includes/picking.inc

   1 <?php
   2
   3 require_once($path_to_root.'/'.'sales/includes/sales_db.inc');
   4 function process_picking_flag() {
   5         $id = find_submit('_chgpck', false);
   6         if ($id != null && $id != -1 && isset($_POST['pick'])) {
   7                 $id = str_replace("_update", "", $id);
   8                 change_pck_flag($id, isset($_POST['pick'][$id]));
   9         }
  10 }
  11
  12 function change_pck_flag($id, $value=null)
  13 {
  14         if($value === null) $value = true;
  15         global  $Ajax;
  16
  17         $picker = new Picker();
  18         list($debtor_no, $branch_code) = explode('_', $id);
  19
  20         // insert or remove pick order form pick table initial_quantity
  21         if($picker->picked($debtor_no, $branch_code)) {
  22                 $picker->deleteAllForBranch($debtor_no, $branch_code);
  23         }
  24         else {
  25                 $picker->insertAllForBranch($debtor_no, $branch_code);
  26         }
  27         $Ajax->activate('order_line_view');
  28 }
  29
  30
  31 class Picker {
  32         function picked($debtor_no, $branch_code) {
  33                 $sql = "SELECT *
  34                                                 FROM ".TB_PREF."sales_orders so
  35                                                 NATURAL JOIN ".TB_PREF."sales_order_details sod
  36                                                 JOIN ".TB_PREF."topick p on (sod.id = p.detail_id)
  37                                                 WHERE debtor_no = $debtor_no AND branch_code = $branch_code
  38                                                 ";
  39
  40                 return db_num_rows(db_query($sql)) > 0;
  41
  42         }
  43         function insertAllForBranch($debtor_no, $branch_code) {
  44                 $this->deleteAllForBranch($debtor_no, $branch_code);
  45                 $pick_location = OrderXtraConfig::$default_location;
  46                 $sql = "SELECT  d.stock_id
  47                                                                                 ,sod.id AS detail_id
  48                                                                                 , d.quantity AS quantity
  49                                                                                 ,IF(".OrderXtraConfig::sql_held_condition().", d.quantity, 0) held
  50                                                                                 ,qoh.quantity AS qoh
  51                                                                                 ,quantity_before
  52                                                                                 ,order_id
  53                                                 FROM ".TB_PREF."sales_order_details sod
  54                                                 NATURAL JOIN ".TB_PREF."sales_orders so
  55                                                 JOIN ".TB_PREF."denorm_order_details_queue d ON (sod.id = d.id)
  56                                                 JOIN ".TB_PREF."denorm_qoh qoh ON (qoh.stock_id = sod.stk_code AND loc_code = '$pick_location')
  57                                                 WHERE debtor_no = $debtor_no AND branch_code = $branch_code
  58                                                 ";
  59                 $result = db_query($sql, $sql);
  60                 while($row=db_fetch($result)) {
  61                         foreach($row as $key => $value) { $$key = $value; }
  62                         $available = max(min($quantity-$held, $qoh-$quantity_before-$held) ,0 );
  63
  64                 insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $available, $quantity);
  65
  66                 }
  67         }
  68
  69         function deleteAllForBranch($debtor_no, $branch_code) {
  70                 $sql = "DELETE ".TB_PREF."topick p
  71                                                 FROM ".TB_PREF."topick p
  72                                                 JOIN ".TB_PREF."sales_order_details sod on (sod.id = p.detail_id)
  73                                                 NATURAL JOIN ".TB_PREF."sales_orders so
  74                                                 WHERE debtor_no = $debtor_no AND branch_code = $branch_code
  75                                                 ";
  76                 db_query($sql);
  77         }
  78
  79 }
  80
  81 function pick_query() {
  82         return "SELECT  detail_id, -sum(quantity) as quantity
  83                                         FROM ".TB_PREF."topick
  84                                         WHERE type IN ('order', 'booked')
  85                                         GROUP BY detail_id ";
  86
  87 }
  88
  89 function insert_pick($sku, $order_id, $detail_id, $debtor_no, $branch_code, $to_pick, $quantity, $qoh_location=null) {
  90                 if($qoh_location == null) $qoh_location = OrderXtraConfig::$default_location;
  91                 $item_link = "/modules/order_line_extra/item_schedule.php?stock_id=$sku";
  92                 $order_link = "/sales/sales_order_entry.php?OrderNumber=$order_id";
  93                 $base = substr($sku, 0, 8);
  94                 $variation = substr($sku, 9);
  95                 $branch = get_cust_branch($debtor_no, $branch_code);
  96                 $location = $branch['branch_ref'];
  97                         $booked = $quantity - $to_pick;
  98
  99                 $sql = "INSERT INTO ".TB_PREF."topick  SET
 100                         order_id = $order_id,
 101                         order_link = '$order_link',
 102                         location = '$location',
 103                         sku     = '$sku',
 104                         base = '$base',
 105                         variation = '$variation',
 106                         item_link = '$item_link',
 107                         quantity = -{$quantity},
 108                         detail_id = $detail_id,
 109                         type = 'order'";
 110                 db_query($sql, 'There was a problem inserting the picking information.');
 111
 112                 # add booked quantity. Insert them as picked so they won't have to be picked
 113                 $sql = "INSERT INTO ".TB_PREF."topick  SET
 114                         order_id = $order_id,
 115                         order_link = '$order_link',
 116                         location = '$location',
 117                         sku     = '$sku',
 118                         item_link = '$item_link',
 119                         base = '$base',
 120                         variation = '$variation',
 121                         quantity = $booked,
 122                         detail_id = $detail_id,
 123                         type = 'booked'";
 124                 db_query($sql, 'There was a problem inserting the picking information.');
 125
 126                 # add QOH
 127                 $qoh = get_qoh_on_date($sku, $qoh_location);
 128                 $sql = "INSERT INTO ".TB_PREF."topick  SET
 129                         order_id = $order_id,
 130                         order_link = '$order_link',
 131                         location = '$location',
 132                         sku     = '$sku',
 133                         item_link = '$item_link',
 134                         base = '$base',
 135                         variation = '$variation',
 136                         quantity = $qoh,
 137                         detail_id = $detail_id,
 138                         type = 'stock'";
 139                 db_query($sql, 'There was a problem inserting the picking information.');
 140
 141 }
 142
 143 function update_pick($detail_id, $to_pick) {
 144         // delete existing
 145                 $sql = "DELETE FROM ".TB_PREF."topick
 146                                                 WHERE  detail_id = $detail_id
 147                                                 ";
 148                 db_query($sql);
 149
 150                 if($to_pick ==0) return;
 151
 152                 $sql = "SELECT stk_code AS stock_id
 153                                                 , order_no AS order_id
 154                                                 , debtor_no
 155                                                 , branch_code
 156                                                 , quantity-qty_sent AS quantity
 157                                                 FROM ".TB_PREF."sales_orders so
 158                                                 NATURAL JOIN ".TB_PREF."sales_order_details sod
 159                                                 WHERE sod.id = $detail_id
 160                                                 ";
 161                 $result = db_query($sql);
 162                 $row = db_fetch($result);
 163
 164                 foreach($row as $key => $value) { $$key = $value; }
 165
 166         insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $to_pick, $quantity);
 167 }
 168
 169