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