includes/picking.inc

   1 <?php
   2
   3 require_once($path_to_root.'/'.'modules/order_line_extra/includes/sql_set.inc');
   4 require_once($path_to_root.'/'.'sales/includes/sales_db.inc');
   5 function process_picking_flag() {
   6         $id = find_submit('_chgpck', false);
   7         if ($id != null && $id != -1 && isset($_POST['pick'])) {
   8                 $id = str_replace("_update", "", $id);
   9                 change_pck_flag($id, isset($_POST['pick'][$id]));
  10         }
  11 }
  12
  13 function change_pck_flag($id, $value=null)
  14 {
  15         if($value === null) $value = true;
  16         global  $Ajax;
  17
  18         $picker = new Picker();
  19         list($debtor_no, $branch_code) = explode('_', $id);
  20
  21         // insert or remove pick order form pick table initial_quantity
  22         if($picker->picked($debtor_no, $branch_code)) {
  23                 $picker->deleteAllForBranch($debtor_no, $branch_code);
  24         }
  25         else {
  26                 $picker->insertAllForBranch($debtor_no, $branch_code);
  27         }
  28         $Ajax->activate('order_line_view');
  29 }
  30
  31
  32 class Picker extends OrderAction {
  33         function __construct($data=null) {
  34                 if($data === null) $data = array();
  35                 parent::__construct($data);
  36         }
  37         function picked($debtor_no, $branch_code) {
  38                 $sql = "SELECT *
  39                         FROM ".TB_PREF."sales_orders so
  40                         NATURAL JOIN ".TB_PREF."sales_order_details sod
  41                         JOIN ".TB_PREF."topick p on (sod.id = p.detail_id)
  42                         WHERE debtor_no = $debtor_no AND branch_code = $branch_code
  43                         ";
  44
  45                 return db_num_rows(db_query($sql)) > 0;
  46
  47         }
  48
  49         function getBranch($detail_id) {
  50         $sql = "SELECT stk_code AS stock_id
  51                 , order_no AS order_id
  52                 , debtor_no
  53                 , branch_code
  54                 , quantity-qty_sent AS quantity
  55                 FROM ".TB_PREF."sales_orders so
  56                 NATURAL JOIN ".TB_PREF."sales_order_details sod
  57                 WHERE sod.id = $detail_id
  58                 ";
  59         $result = db_query($sql);
  60         $row = db_fetch($result);
  61
  62         return $row;
  63
  64         }
  65         function insertForDetail($detail_id) {
  66         display_warning("picked $detail_id");
  67                 $branch = $this->getBranch($detail_id);
  68                 $debtor_no = $branch->debtor_no;
  69                 $branch_code = $branch->branch_code;
  70                 $this->deleteForDetail($detail_id);
  71                 $pick_location = OrderXtraConfig::$default_location;
  72                 $sql = "SELECT  d.stock_id
  73                         ,sod.id AS detail_id
  74                         , d.quantity AS quantity
  75                         ,IF(".OrderXtraConfig::sql_held_condition().", d.quantity, 0) held
  76                         ,qoh.quantity AS qoh
  77                         ,tp.quantity AS total_picked
  78                         ,quantity_before
  79                         ,order_id
  80                         FROM ".TB_PREF."sales_order_details sod
  81                         NATURAL JOIN ".TB_PREF."sales_orders so
  82                         JOIN ".TB_PREF."denorm_order_details_queue d ON (sod.id = d.id)
  83                         JOIN ".TB_PREF."denorm_qoh qoh ON (qoh.stock_id = sod.stk_code AND loc_code = '$pick_location')
  84                         LEFT JOIN (".totalpick_query().") tp ON (tp.stock_id = sod.stk_code)
  85                         WHERE  sod.id = $detail_id
  86                         ";
  87                 $result = db_query($sql, $sql);
  88                 while($row=db_fetch($result)) {
  89                         foreach($row as $key => $value) { $$key = $value; }
  90                                 $available = max(min($quantity-$held, $qoh-$quantity_before-$total_picked-$held) ,0 );
  91
  92                         if($available || OrderXtraConfig::$autopick_null)
  93                                 insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $available, $quantity);
  94
  95                 }
  96         }
  97         function insertAllForBranch($debtor_no, $branch_code) {
  98                 $this->deleteAllForBranch($debtor_no, $branch_code);
  99                 $pick_location = OrderXtraConfig::$default_location;
 100                 $sql = "SELECT  d.stock_id
 101                         ,sod.id AS detail_id
 102                         , d.quantity AS quantity
 103                         ,IF(".OrderXtraConfig::sql_held_condition().", d.quantity, 0) held
 104                         ,qoh.quantity AS qoh
 105                         ,tp.quantity AS total_picked
 106                         ,quantity_before
 107                         ,order_id
 108                         FROM ".TB_PREF."sales_order_details sod
 109                         NATURAL JOIN ".TB_PREF."sales_orders so
 110                         JOIN ".TB_PREF."denorm_order_details_queue d ON (sod.id = d.id)
 111                         JOIN ".TB_PREF."denorm_qoh qoh ON (qoh.stock_id = sod.stk_code AND loc_code = '$pick_location')
 112                         LEFT JOIN (".totalpick_query().") tp ON (tp.stock_id = sod.stk_code)
 113                         WHERE debtor_no = $debtor_no AND branch_code = $branch_code
 114                         ";
 115                 $result = db_query($sql, $sql);
 116                 while($row=db_fetch($result)) {
 117                         foreach($row as $key => $value) { $$key = $value; }
 118                                 $available = max(min($quantity-$held, $qoh-$quantity_before-$total_picked-$held) ,0 );
 119
 120                         if($available || OrderXtraConfig::$autopick_null)
 121                                 insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $available, $quantity);
 122
 123                 }
 124         }
 125         function deleteForDetail($detail_id) {
 126                 $sql = "DELETE ".TB_PREF."topick p
 127                         FROM ".TB_PREF."topick p
 128                         JOIN ".TB_PREF."sales_order_details sod on (sod.id = p.detail_id)
 129                         NATURAL JOIN ".TB_PREF."sales_orders so
 130                         WHERE  sod.id = $detail_id
 131                         ";
 132                 db_query($sql);
 133         }
 134
 135         function deleteAllForBranch($debtor_no, $branch_code) {
 136                 $sql = "DELETE ".TB_PREF."topick p
 137                         FROM ".TB_PREF."topick p
 138                         JOIN ".TB_PREF."sales_order_details sod on (sod.id = p.detail_id)
 139                         NATURAL JOIN ".TB_PREF."sales_orders so
 140                         WHERE debtor_no = $debtor_no AND branch_code = $branch_code
 141                         ";
 142                 db_query($sql);
 143         }
 144
 145         function pickAllDetails() {
 146                 foreach($this->detail_ids as $detail_id) {
 147                         $this->insertForDetail($detail_id);
 148                 }
 149         }
 150         function unpickAllDetails() {
 151                 foreach($this->detail_ids as $detail_id) {
 152                         $this->deleteForDetail($detail_id);
 153                 }
 154         }
 155
 156 }
 157
 158 function pick_query($detail_id=null) {
 159         $sql =  "SELECT  detail_id, -sum(quantity) as quantity
 160                 FROM ".TB_PREF."topick
 161                 WHERE type IN ('order', 'booked')";
 162         if($detail_id) $sql .= " AND detail_id = $detail_id";
 163         else $sql .= " GROUP BY detail_id ";
 164
 165         return $sql;
 166
 167 }
 168
 169 /* query to get the total picked quantity */
 170 function totalpick_query($stock_id=null) {
 171         $sql =  "SELECT  sku AS stock_id, -sum(quantity) as quantity
 172                 FROM ".TB_PREF."topick
 173                 WHERE type IN ('order', 'booked')";
 174         if($stock_id) $sql .= " AND sku = $stock_id";
 175         else $sql .= " GROUP BY stock_id ";
 176
 177         return $sql;
 178 }
 179
 180 function insert_pick($sku, $order_id, $detail_id, $debtor_no, $branch_code, $to_pick, $quantity, $qoh_location=null) {
 181         if($qoh_location == null) $qoh_location = OrderXtraConfig::$default_location;
 182         $item_link = "/modules/order_line_extra/item_schedule.php?stock_id=$sku";
 183         $order_link = "/sales/sales_order_entry.php?OrderNumber=$order_id";
 184         $base = substr($sku, 0, 8);
 185         $variation = substr($sku, 9);
 186         $branch = get_cust_branch($debtor_no, $branch_code);
 187         $location = $branch['branch_ref'];
 188         $booked = $quantity - $to_pick;
 189
 190         $sql = "INSERT INTO ".TB_PREF."topick  SET
 191                 order_id = $order_id,
 192                 order_link = '$order_link',
 193                 location = '$location',
 194                 sku     = '$sku',
 195                 base = '$base',
 196                 variation = '$variation',
 197                 item_link = '$item_link',
 198                 quantity = -{$quantity},
 199                 detail_id = $detail_id,
 200                 type = 'order'";
 201         db_query($sql, 'There was a problem inserting the picking information.');
 202
 203         # add booked quantity. Insert them as picked so they won't have to be picked
 204         $sql = "INSERT INTO ".TB_PREF."topick  SET
 205                 order_id = $order_id,
 206                 order_link = '$order_link',
 207                 location = '$location',
 208                 sku     = '$sku',
 209                 item_link = '$item_link',
 210                 base = '$base',
 211                 variation = '$variation',
 212                 quantity = $booked,
 213                 detail_id = $detail_id,
 214                 type = 'booked'";
 215         db_query($sql, 'There was a problem inserting the picking information.');
 216
 217         # add QOH
 218         $qoh = get_qoh_on_date($sku, $qoh_location);
 219         $sql = "INSERT INTO ".TB_PREF."topick  SET
 220                 order_id = $order_id,
 221                 order_link = '$order_link',
 222                 location = '$location',
 223                 sku     = '$sku',
 224                 item_link = '$item_link',
 225                 base = '$base',
 226                 variation = '$variation',
 227                 quantity = $qoh,
 228                 detail_id = $detail_id,
 229                 type = 'stock'";
 230         db_query($sql, 'There was a problem inserting the picking information.');
 231
 232 }
 233
 234 function delete_pick($detail_id) {
 235         $sql = "DELETE FROM ".TB_PREF."topick
 236                 WHERE  detail_id = $detail_id
 237                 ";
 238         return db_query($sql);
 239 }
 240
 241 function update_pick($detail_id, $to_pick) {
 242         // delete existing
 243         delete_pick($detail_id);
 244
 245         if($to_pick ==0) return;
 246
 247         $sql = "SELECT stk_code AS stock_id
 248                 , order_no AS order_id
 249                 , debtor_no
 250                 , branch_code
 251                 , quantity-qty_sent AS quantity
 252                 FROM ".TB_PREF."sales_orders so
 253                 NATURAL JOIN ".TB_PREF."sales_order_details sod
 254                 WHERE sod.id = $detail_id
 255                 ";
 256         $result = db_query($sql);
 257         $row = db_fetch($result);
 258
 259         foreach($row as $key => $value) { $$key = $value; }
 260
 261         insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $to_pick, $quantity);
 262 }
 263
 264 function clean_pickup_for_dispatch($cart) {
 265         foreach($cart->line_items as $line) {
 266                 delete_pick($line->src_id);
 267         }
 268 }
 269
 270