includes/order_lines.inc

   1 <?php
   2 include_once('order_xtra_config.inc');
   3 include_once($path_to_root."/includes/ui/ui_input.inc");
   4 /* This utility function remove beginning <td> and </td> from a string
   5  * generated by the above *_cell function.
   6  * This is usefull to use the result of the function within a pager
   7  * which already include the <td>...</td>
   8  * However, for function which doesn't return a string
   9  * but echo, we need to capture the output of the echo first.
  10  */
  11 function start_extract_cell() {
  12         // We need
  13         ob_start();
  14 }
  15
  16 function end_extract_cell() {
  17         $content = ob_get_contents();
  18         ob_end_clean();
  19         return extract_cell($content);
  20 }
  21
  22 function extract_cell($td) {
  23         if(preg_match('/\s*<td>(.*)<\/td>\s*/s', $td, $matches)) {
  24                 return $matches[1];
  25         }
  26         else {
  27                 return $td;
  28         }
  29 }
  30
  31 /* remove unchecked data so each
  32  * funcion doesn't to check what's been checked or not.
  33  */
  34 function filter_data(&$data) {
  35         if(!isset($data['detail'])) return;
  36         $new_details = array();
  37         foreach(@$data['detail'] as $detail_id => $values) {
  38                 if(@$values['check'] === 'on') $new_details[$detail_id]=$values;
  39         }
  40         $data['detail'] = $new_details;
  41 }
  42
  43 function post_to_detail_ids() {
  44         if(!isset($_POST['detail'])) return;
  45         $detail_ids = array();
  46         foreach($_POST['detail'] as $detail_id => $values) {
  47                 array_push($detail_ids, $detail_id);
  48         }
  49
  50         return $detail_ids;
  51 }
  52
  53 function update_extra_order_details() {
  54         if(!isset($_POST['Update']) || $_POST['Update'] != 'Update')  return;
  55
  56         $cart = post_to_detail_ids();
  57         if($cart === null) return;
  58 begin_transaction();
  59         hook_db_prewrite($cart, 'order_xtra');
  60         // update in database all field
  61         $date_fields = array('hold_until_date', 'required_date', 'expiry_date', 'priority');
  62         foreach($_POST['detail'] as $detail_id => $values) {
  63                 $set = array();
  64                 $comment=$values['comment'];
  65           array_push($set, " comment='$comment' "); // erase comment if needed.
  66                 foreach($date_fields as $date_field) {
  67                         if(isset($values[$date_field])) {
  68                                 $input_date = trim($values[$date_field]);
  69                                 if($input_date == "")  {
  70                                         // Only the priority field can be set to null.
  71                                         if($date_field == 'priority') array_push($set," $date_field = NULL");
  72                                 } else {
  73                                         $date = date2sql($input_date);
  74                                         array_push($set," $date_field='$date' ");
  75                                 }
  76                         }
  77                 }
  78
  79                 if(!empty($set))  {
  80                         $sql = "UPDATE ".TB_PREF."sales_order_details  SET ".implode($set,', ')."  WHERE id = $detail_id ";
  81
  82                         if(!db_query($sql)) {
  83                                 display_error('Problem while updating order details. Try again');
  84                                 cancel_transaction();
  85                                 return;
  86                         }
  87                 }
  88         }
  89         hook_db_postwrite($cart, 'order_xtra');
  90         commit_transaction();
  91
  92
  93         display_notification('Order details updated');
  94
  95 }
  96
  97 function split_order_details() {
  98         if(!isset($_POST['Split']) || $_POST['Split'] != 'Split')  return;
  99         $splitter = new Splitter($_POST);
 100         begin_transaction();
 101         $cart = post_to_detail_ids();
 102         if($cart === null) return;
 103         hook_db_prewrite($cart, 'order_xtra');
 104                 if($splitter->splitAll()) display_warning("Items have been split.");
 105         hook_db_postwrite($cart, 'order_xtra');
 106         commit_transaction();
 107 }
 108
 109 function bulk_update_order_details() {
 110         if(!isset($_POST['Bulk']) || $_POST['Bulk'] != 'Bulk')  return;
 111         $bulk_updater = new BulkUpdater($_POST);
 112         begin_transaction();
 113         $cart = post_to_detail_ids();
 114         if($cart === null) return;
 115         hook_db_prewrite($cart, 'order_xtra');
 116         $bulk_updater->update();
 117         hook_db_postwrite($cart, 'order_xtra');
 118         commit_transaction();
 119 }
 120
 121
 122
 123 function compute_input_name($row, $field) {
 124         $row_id = $row['id'];
 125         return "detail[$row_id][$field]";
 126 }
 127
 128 function view_link($dummy, $order_no)
 129 {
 130         return  get_customer_trans_view_str(ST_SALESORDER, $order_no);
 131 }
 132
 133 function item_link($dummy, $stock_id)
 134 {
 135         return pager_link( _($stock_id),
 136         "/modules/order_line_extra/item_schedule.php?stock_id=" .$stock_id);
 137 }
 138
 139 function order_link($row)
 140 {
 141         return pager_link( _("Sales Order"),
 142         "/sales/sales_order_entry.php?NewQuoteToSalesOrder=" .$row['order_no'], ICON_DOC);
 143 }
 144
 145 function customer_link($row) {
 146         return customer_link2($row['debtor_ref'], $row['debtor_no']);
 147 }
 148
 149 function customer_link2($name, $id) {
 150         return pager_link(_($name), "/modules/order_line_extra/order_lines_view.php?customer_id=${id}");
 151
 152 }
 153
 154 function aggregate_comment($row) {
 155         $comment =  $row['order_comment'].";".$row['detail_comment'];
 156         if($comment) {
 157                 $comments = array_map('trim', explode(';', $comment));
 158                 $comments = array_filter($comments);
 159                 $first = array_shift($comments);
 160                 if(count($comments) == 0)
 161                         return $first;
 162                 else {
 163                         $tooltip = implode('<br> - ', $comments);
 164                         return "$first <span class='before-tooltip'>more<span class='tooltip'>&nbsp$tooltip</span><span>";
 165                 }
 166         }
 167         return '';
 168 }
 169
 170 function available_quantity($row, $available) {
 171         $quantity = $row['quantity'];
 172         $class = '';
 173         if($available== 0) $class = 'limited';
 174         else if($quantity > $available)  $class = 'partial';
 175         else if(OrderXtraConfig::early(sql2date($row['hold_until_date']))) $class = 'early';
 176
 177         return "<span class='$class'>$available<span>";
 178 }
 179
 180 function input_date_details($row, $field_name, $date) {
 181 $row_id = $row['id'];
 182         $name = compute_input_name($row, $field_name);
 183
 184 $_POST[$name] = sql2date($date);
 185
 186         start_extract_cell();
 187                 date_cells (null, $name, 'title', null, 0,0,1001);
 188         return end_extract_cell();
 189
 190
 191         return "
 192 <input type='text' value='$date' name='$name'>
 193 ";
 194 }
 195
 196 function input_priority_date_details($row, $date) {
 197         return input_date_details($row, 'priority', $date);
 198 }
 199
 200 function input_hold_until_date_details($row, $date) {
 201         return input_date_details($row, 'hold_until_date', $date);
 202 }
 203
 204 function input_required_date_details($row, $date) {
 205         return input_date_details($row, 'required_date', $date);
 206 }
 207
 208 function input_expiry_date_details($row, $date) {
 209         return input_date_details($row, 'expiry_date', $date);
 210 }
 211
 212 function input_comment_details($row, $comment) {
 213 $row_id = $row['id'];
 214         return "
 215 <input type='text' value='$comment' name='detail[$row_id][comment]'>
 216 ";
 217 }
 218
 219 function input_check_details($row, $dummy) {
 220         $row_id = $row['id'];
 221                 return "
 222                 <input type='checkbox' class='check_detail' name='detail[$row_id][check]'>";
 223 }
 224
 225 function pick_checkbox($row)
 226 {
 227         $id = $row['debtor_no']."_".$row['branch_code'];
 228         $name = "chgpck" .$id;
 229         $value = $row['quantity_to_pick'] > 0 ? 1:0;
 230
 231 // save also in hidden field for testing during 'Update'
 232  return checkbox(null, $name, $value, true,
 233         _('Pick all possible item for this branch.'))
 234         . hidden('pick['.$id.']', $value, false);
 235 }
 236
 237 function get_order_details_extra($customer_id, $location, $item_like) {
 238         $sql = "SELECT sod.id
 239         , so.order_no
 240         , stk_code
 241         , sod.quantity - qty_sent quantity
 242         , GREATEST(0, LEAST(qoh.quantity  - quantity_before, sod.quantity - qty_sent))
 243         , quantity_before
 244         , sod.`priority`
 245         , hold_until_date
 246         , required_date
 247         , expiry_date
 248         ,comment
 249         FROM ".TB_PREF."sales_order_details sod
 250         JOIN ".TB_PREF."sales_orders so ON (so.order_no = sod.order_no
 251                 AND so.trans_type = sod.trans_type
 252                 AND so.trans_type = ".ST_SALESORDER."
 253                 AND so.debtor_no = $customer_id
 254         )
 255         JOIN ".TB_PREF."denorm_qoh qoh ON (stock_id = stk_code AND loc_code = '$location')
 256         LEFT JOIN ".TB_PREF."denorm_order_details_queue  d ON (d.id = sod.id)
 257         WHERE sod.quantity > qty_sent
 258         ";
 259
 260         if($item_like) {
 261                         if($item_like[0]=='/')  {
 262                                 $regexp = substr($item_like, 1);
 263                                 $sql .= " AND stk_code RLIKE '$regexp'";
 264                         }
 265                         else
 266                                 $sql .= " AND stk_code LIKE '$item_like'";
 267         }
 268
 269         return $sql;
 270 }
 271
 272 function get_order_summary($location) {
 273         $held_condition = OrderXtraConfig::sql_held_condition();
 274
 275         $pick = "SELECT  detail_id, -sum(quantity) as quantity
 276                                         FROM ".TB_PREF."topick
 277                                         WHERE type IN ('order', 'booked')
 278                                         GROUP BY detail_id ";
 279
 280         $sub = "SELECT debtor_no, debtor_ref, branch_code, branch_ref, stk_code
 281         , min(delivery_date)
 282         , sum(d.quantity) as quantity
 283         , sum((d.quantity)*unit_price*(1-discount_percent/100)) as amount
 284         , min(required_date)
 285         , group_concat(distinct comments separator ';') as order_comment
 286         , group_concat(distinct comment separator ';') as detail_comment
 287         , max(quantity_before) as quantity_before
 288         , sum(if($held_condition, greatest(least(d.quantity, qoh.quantity - quantity_before), 0), 0)) as quantity_held
 289         , sum(if($held_condition, 0,  greatest(least(d.quantity, qoh.quantity - quantity_before), 0))) as available_quantity
 290         , sum(if($held_condition, 0,  greatest(least(d.quantity, qoh.quantity - quantity_before), 0)*unit_price*(1-discount_percent/100))) as available_amount
 291         , sum(p.quantity) as quantity_to_pick
 292         , sum(p.quantity*unit_price) as amount_to_pick
 293         FROM ".TB_PREF."sales_order_details sod
 294         JOIN ".TB_PREF."sales_orders so ON (so.order_no = sod.order_no
 295                 AND so.trans_type = sod.trans_type
 296                 AND so.trans_type = ".ST_SALESORDER."
 297         )
 298         NATURAL JOIN ".TB_PREF."debtors_master
 299         NATURAL JOIN ".TB_PREF."cust_branch
 300         LEFT JOIN ($pick) p ON (detail_id = sod.id)
 301         JOIN ".TB_PREF."denorm_order_details_queue  d ON (d.id = sod.id)
 302         LEFT JOIN ".TB_PREF."denorm_qoh qoh ON (qoh.stock_id = stk_code AND loc_code = '$location')
 303         WHERE sod.quantity > qty_sent AND ".ST_SALESORDER."
 304         GROUP BY debtor_no, branch_code, stk_code
 305         ";
 306
 307         //$sub = TB_PREF."order_summary_view";
 308         //
 309
 310         $sql = array( "select" => "debtor_no, branch_code, debtor_ref, branch_ref
 311         , `min(delivery_date)` as delivery_date
 312         , sum(sub.quantity) as quantity
 313         , sum(sub.amount) as amount
 314         , sum(sub.available_quantity)
 315         , sum(sub.available_amount)
 316         , sum(quantity_held) as quantity_help
 317         ,  min(`min(required_date)`) as required_date
 318         , group_concat(distinct order_comment separator ';') as order_comment
 319         , group_concat(distinct detail_comment separator ';') as detail_comment
 320         , sum(quantity_to_pick) as quantity_to_pick
 321         , sum(amount_to_pick) as amount_to_pick
 322         ", "from" => " ($sub) sub
 323                         LEFT JOIN ".TB_PREF."denorm_qoh qoh ON (stock_id = stk_code AND loc_code = '$location')
 324
 325         ", "group" => " debtor_no, branch_code
 326         ");
 327
 328         return $sql;
 329 }
 330 ?>