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                                                                                 ,tp.quantity AS total_picked
  52                                                                                 ,quantity_before
  53                                                                                 ,order_id
  54                                                 FROM ".TB_PREF."sales_order_details sod
  55                                                 NATURAL JOIN ".TB_PREF."sales_orders so
  56                                                 JOIN ".TB_PREF."denorm_order_details_queue d ON (sod.id = d.id)
  57                                                 JOIN ".TB_PREF."denorm_qoh qoh ON (qoh.stock_id = sod.stk_code AND loc_code = '$pick_location')
  58                                                 LEFT JOIN (".totalpick_query().") tp ON (tp.stock_id = sod.stk_code)
  59                                                 WHERE debtor_no = $debtor_no AND branch_code = $branch_code
  60                                                 ";
  61                 $result = db_query($sql, $sql);
  62                 while($row=db_fetch($result)) {
  63                         foreach($row as $key => $value) { $$key = $value; }
  64                         $available = max(min($quantity-$held, $qoh-$quantity_before-$total_picked-$held) ,0 );
  65
  66                 if($available || OrderXtraConfig::$autopick_null)
  67                         insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $available, $quantity);
  68
  69                 }
  70         }
  71
  72         function deleteAllForBranch($debtor_no, $branch_code) {
  73                 $sql = "DELETE ".TB_PREF."topick p
  74                                                 FROM ".TB_PREF."topick p
  75                                                 JOIN ".TB_PREF."sales_order_details sod on (sod.id = p.detail_id)
  76                                                 NATURAL JOIN ".TB_PREF."sales_orders so
  77                                                 WHERE debtor_no = $debtor_no AND branch_code = $branch_code
  78                                                 ";
  79                 db_query($sql);
  80         }
  81
  82 }
  83
  84 function pick_query($detail_id=null) {
  85         $sql =  "SELECT  detail_id, -sum(quantity) as quantity
  86                                         FROM ".TB_PREF."topick
  87                                         WHERE type IN ('order', 'booked')";
  88         if($detail_id) $sql .= " AND detail_id = $detail_id";
  89         else $sql .= " GROUP BY detail_id ";
  90
  91         return $sql;
  92
  93 }
  94
  95 /* query to get the total picked quantity */
  96 function totalpick_query($stock_id=null) {
  97         $sql =  "SELECT  sku AS stock_id, -sum(quantity) as quantity
  98                                         FROM ".TB_PREF."topick
  99                                         WHERE type IN ('order', 'booked')";
 100         if($stock_id) $sql .= " AND sku = $stock_id";
 101         else $sql .= " GROUP BY stock_id ";
 102
 103         return $sql;
 104 }
 105
 106 function insert_pick($sku, $order_id, $detail_id, $debtor_no, $branch_code, $to_pick, $quantity, $qoh_location=null) {
 107                 if($qoh_location == null) $qoh_location = OrderXtraConfig::$default_location;
 108                 $item_link = "/modules/order_line_extra/item_schedule.php?stock_id=$sku";
 109                 $order_link = "/sales/sales_order_entry.php?OrderNumber=$order_id";
 110                 $base = substr($sku, 0, 8);
 111                 $variation = substr($sku, 9);
 112                 $branch = get_cust_branch($debtor_no, $branch_code);
 113                 $location = $branch['branch_ref'];
 114                         $booked = $quantity - $to_pick;
 115
 116                 $sql = "INSERT INTO ".TB_PREF."topick  SET
 117                         order_id = $order_id,
 118                         order_link = '$order_link',
 119                         location = '$location',
 120                         sku     = '$sku',
 121                         base = '$base',
 122                         variation = '$variation',
 123                         item_link = '$item_link',
 124                         quantity = -{$quantity},
 125                         detail_id = $detail_id,
 126                         type = 'order'";
 127                 db_query($sql, 'There was a problem inserting the picking information.');
 128
 129                 # add booked quantity. Insert them as picked so they won't have to be picked
 130                 $sql = "INSERT INTO ".TB_PREF."topick  SET
 131                         order_id = $order_id,
 132                         order_link = '$order_link',
 133                         location = '$location',
 134                         sku     = '$sku',
 135                         item_link = '$item_link',
 136                         base = '$base',
 137                         variation = '$variation',
 138                         quantity = $booked,
 139                         detail_id = $detail_id,
 140                         type = 'booked'";
 141                 db_query($sql, 'There was a problem inserting the picking information.');
 142
 143                 # add QOH
 144                 $qoh = get_qoh_on_date($sku, $qoh_location);
 145                 $sql = "INSERT INTO ".TB_PREF."topick  SET
 146                         order_id = $order_id,
 147                         order_link = '$order_link',
 148                         location = '$location',
 149                         sku     = '$sku',
 150                         item_link = '$item_link',
 151                         base = '$base',
 152                         variation = '$variation',
 153                         quantity = $qoh,
 154                         detail_id = $detail_id,
 155                         type = 'stock'";
 156                 db_query($sql, 'There was a problem inserting the picking information.');
 157
 158 }
 159
 160 function delete_pick($detail_id) {
 161                 $sql = "DELETE FROM ".TB_PREF."topick
 162                                                 WHERE  detail_id = $detail_id
 163                                                 ";
 164                 return db_query($sql);
 165 }
 166
 167 function update_pick($detail_id, $to_pick) {
 168         // delete existing
 169                 delete_pick($detail_id);
 170
 171                 if($to_pick ==0) return;
 172
 173                 $sql = "SELECT stk_code AS stock_id
 174                                                 , order_no AS order_id
 175                                                 , debtor_no
 176                                                 , branch_code
 177                                                 , quantity-qty_sent AS quantity
 178                                                 FROM ".TB_PREF."sales_orders so
 179                                                 NATURAL JOIN ".TB_PREF."sales_order_details sod
 180                                                 WHERE sod.id = $detail_id
 181                                                 ";
 182                 $result = db_query($sql);
 183                 $row = db_fetch($result);
 184
 185                 foreach($row as $key => $value) { $$key = $value; }
 186
 187         insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $to_pick, $quantity);
 188 }
 189
 190 function clean_pickup_for_dispatch($cart) {
 191         foreach($cart->line_items as $line) {
 192                 delete_pick($line->src_id);
 193         }
 194 }
 195
 196