includes/order_lines.inc

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