picked($debtor_no, $branch_code)) { $picker->deleteAllForBranch($debtor_no, $branch_code); } else { $picker->insertAllForBranch($debtor_no, $branch_code); } $Ajax->activate('order_line_view'); } class Picker { function picked($debtor_no, $branch_code) { $sql = "SELECT * FROM ".TB_PREF."sales_orders so NATURAL JOIN ".TB_PREF."sales_order_details sod JOIN ".TB_PREF."topick p on (sod.id = p.detail_id) WHERE debtor_no = $debtor_no AND branch_code = $branch_code "; return db_num_rows(db_query($sql)) > 0; } function insertAllForBranch($debtor_no, $branch_code) { $this->deleteAllForBranch($debtor_no, $branch_code); $pick_location = OrderXtraConfig::$default_location; $sql = "SELECT d.stock_id ,sod.id AS detail_id , d.quantity AS quantity ,IF(".OrderXtraConfig::sql_held_condition().", d.quantity, 0) held ,qoh.quantity AS qoh ,quantity_before ,order_id FROM ".TB_PREF."sales_order_details sod NATURAL JOIN ".TB_PREF."sales_orders so JOIN ".TB_PREF."denorm_order_details_queue d ON (sod.id = d.id) JOIN ".TB_PREF."denorm_qoh qoh ON (qoh.stock_id = sod.stk_code AND loc_code = '$pick_location') WHERE debtor_no = $debtor_no AND branch_code = $branch_code "; $result = db_query($sql, $sql); while($row=db_fetch($result)) { foreach($row as $key => $value) { $$key = $value; } $available = max(min($quantity-$held, $qoh-$quantity_before-$held) ,0 ); insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $available, $quantity); } } function deleteAllForBranch($debtor_no, $branch_code) { $sql = "DELETE ".TB_PREF."topick p FROM ".TB_PREF."topick p JOIN ".TB_PREF."sales_order_details sod on (sod.id = p.detail_id) NATURAL JOIN ".TB_PREF."sales_orders so WHERE debtor_no = $debtor_no AND branch_code = $branch_code "; db_query($sql); } } function pick_query() { return "SELECT detail_id, -sum(quantity) as quantity FROM ".TB_PREF."topick WHERE type IN ('order', 'booked') GROUP BY detail_id "; } function insert_pick($sku, $order_id, $detail_id, $debtor_no, $branch_code, $to_pick, $quantity) { $item_link = "/modules/order_line_extra/item_schedule.php?stock_id=$sku"; $order_link = "/sales/sales_order_entry.php?OrderNumber=$order_id"; $base = substr($sku, 0, 8); $variation = substr($sku, 9); $branch = get_cust_branch($debtor_no, $branch_code); $location = $branch['branch_ref']; $booked = $quantity - $to_pick; $sql = "INSERT INTO ".TB_PREF."topick SET order_id = $order_id, order_link = '$order_link', location = '$location', sku = '$sku', base = '$base', variation = '$variation', item_link = '$item_link', quantity = -{$quantity}, detail_id = $detail_id, type = 'order'"; db_query($sql, 'There was a problem inserting the picking information.'); # add booked quantity. Insert them as picked so they won't have to be picked $sql = "INSERT INTO ".TB_PREF."topick SET order_id = $order_id, order_link = '$order_link', location = '$location', sku = '$sku', item_link = '$item_link', base = '$base', variation = '$variation', quantity = $booked, detail_id = $detail_id, type = 'booked'"; db_query($sql, 'There was a problem inserting the picking information.'); } function update_pick($detail_id, $to_pick) { echo $detail_id, " ", $to_pick, "
"; // delete existing $sql = "DELETE FROM ".TB_PREF."topick WHERE detail_id = $detail_id "; echo db_query($sql); $sql = "SELECT stk_code AS stock_id, order_no AS order_id, debtor_no, branch_code, quantity-qty_sent AS quantity FROM ".TB_PREF."sales_orders so NATURAL JOIN ".TB_PREF."sales_order_details sod WHERE sod.id = $detail_id "; $result = db_query($sql); $row = db_fetch($result); foreach($row as $key => $value) { $$key = $value; } insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $to_pick, $quantity); }