From 8b00266bdb37fcd64e57c14874947f8c0c59e821 Mon Sep 17 00:00:00 2001 From: Maxime Bourget Date: Tue, 18 Jun 2013 10:32:48 +0100 Subject: [PATCH] Quick Hack to add Pick/Unpick per item. Needs Refactoring --- includes/order_lines.inc | 22 +-- includes/picking.inc | 280 +++++++++++++++++++++++++-------------- order_lines_view.php | 12 +- 3 files changed, 201 insertions(+), 113 deletions(-) diff --git a/includes/order_lines.inc b/includes/order_lines.inc index 1759c88..52e3c15 100644 --- a/includes/order_lines.inc +++ b/includes/order_lines.inc @@ -108,15 +108,19 @@ function split_order_details() { } function bulk_update_order_details() { - if(!isset($_POST['Bulk']) || $_POST['Bulk'] != 'Bulk') return; - $bulk_updater = new BulkUpdater($_POST); - begin_transaction(); - $cart = post_to_detail_ids(); - if($cart === null) return; - hook_db_prewrite($cart, 'order_xtra'); - $bulk_updater->update(); - hook_db_postwrite($cart, 'order_xtra'); - commit_transaction(); + if(!isset($_POST['Pick']) || $_POST['Pick'] != 'Pick') return; + $picker = new Picker(); +} + +function pick_order_details() { + if(!isset($_POST['Pick']) || $_POST['Pick'] != 'Pick') return; + $picker = new Picker($_POST); + $picker->pickAllDetails(); +} +function unpick_order_details() { + if(!isset($_POST['Unpick']) || $_POST['Unpick'] != 'Unpick') return; + $picker = new Picker($_POST); + $picker->unpickAllDetails(); } diff --git a/includes/picking.inc b/includes/picking.inc index 2d3b5b8..7995901 100644 --- a/includes/picking.inc +++ b/includes/picking.inc @@ -1,5 +1,6 @@ 0; } + + function getBranch($detail_id) { + $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); + + return $row; + + } + function insertForDetail($detail_id) { + display_warning("picked $detail_id"); + $branch = $this->getBranch($detail_id); + $debtor_no = $branch->debtor_no; + $branch_code = $branch->branch_code; + $this->deleteForDetail($detail_id); + $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 + ,tp.quantity AS total_picked + ,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') + LEFT JOIN (".totalpick_query().") tp ON (tp.stock_id = sod.stk_code) + WHERE sod.id = $detail_id + "; + $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-$total_picked-$held) ,0 ); + + if($available || OrderXtraConfig::$autopick_null) + insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $available, $quantity); + + } + } 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 - ,tp.quantity AS total_picked - ,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') - LEFT JOIN (".totalpick_query().") tp ON (tp.stock_id = sod.stk_code) - WHERE debtor_no = $debtor_no AND branch_code = $branch_code - "; + ,sod.id AS detail_id + , d.quantity AS quantity + ,IF(".OrderXtraConfig::sql_held_condition().", d.quantity, 0) held + ,qoh.quantity AS qoh + ,tp.quantity AS total_picked + ,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') + LEFT JOIN (".totalpick_query().") tp ON (tp.stock_id = sod.stk_code) + 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-$total_picked-$held) ,0 ); + $available = max(min($quantity-$held, $qoh-$quantity_before-$total_picked-$held) ,0 ); - if($available || OrderXtraConfig::$autopick_null) - insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $available, $quantity); + if($available || OrderXtraConfig::$autopick_null) + insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $available, $quantity); } } + function deleteForDetail($detail_id) { + $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 sod.id = $detail_id + "; + db_query($sql); + } 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 - "; + 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 pickAllDetails() { + foreach($this->detail_ids as $detail_id) { + $this->insertForDetail($detail_id); + } + } + function unpickAllDetails() { + foreach($this->detail_ids as $detail_id) { + $this->deleteForDetail($detail_id); + } + } + } function pick_query($detail_id=null) { $sql = "SELECT detail_id, -sum(quantity) as quantity - FROM ".TB_PREF."topick - WHERE type IN ('order', 'booked')"; + FROM ".TB_PREF."topick + WHERE type IN ('order', 'booked')"; if($detail_id) $sql .= " AND detail_id = $detail_id"; else $sql .= " GROUP BY detail_id "; @@ -95,8 +169,8 @@ function pick_query($detail_id=null) { /* query to get the total picked quantity */ function totalpick_query($stock_id=null) { $sql = "SELECT sku AS stock_id, -sum(quantity) as quantity - FROM ".TB_PREF."topick - WHERE type IN ('order', 'booked')"; + FROM ".TB_PREF."topick + WHERE type IN ('order', 'booked')"; if($stock_id) $sql .= " AND sku = $stock_id"; else $sql .= " GROUP BY stock_id "; @@ -104,85 +178,85 @@ function totalpick_query($stock_id=null) { } function insert_pick($sku, $order_id, $detail_id, $debtor_no, $branch_code, $to_pick, $quantity, $qoh_location=null) { - if($qoh_location == null) $qoh_location = OrderXtraConfig::$default_location; - $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.'); - - # add QOH - $qoh = get_qoh_on_date($sku, $qoh_location); - $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 = $qoh, - detail_id = $detail_id, - type = 'stock'"; - db_query($sql, 'There was a problem inserting the picking information.'); + if($qoh_location == null) $qoh_location = OrderXtraConfig::$default_location; + $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.'); + + # add QOH + $qoh = get_qoh_on_date($sku, $qoh_location); + $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 = $qoh, + detail_id = $detail_id, + type = 'stock'"; + db_query($sql, 'There was a problem inserting the picking information.'); } function delete_pick($detail_id) { - $sql = "DELETE FROM ".TB_PREF."topick - WHERE detail_id = $detail_id - "; - return db_query($sql); + $sql = "DELETE FROM ".TB_PREF."topick + WHERE detail_id = $detail_id + "; + return db_query($sql); } function update_pick($detail_id, $to_pick) { // delete existing - delete_pick($detail_id); - - if($to_pick ==0) return; - - $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; } + delete_pick($detail_id); + + if($to_pick ==0) return; + + $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); } diff --git a/order_lines_view.php b/order_lines_view.php index 868bbc2..b68fb7f 100644 --- a/order_lines_view.php +++ b/order_lines_view.php @@ -43,7 +43,9 @@ function get_parameter($param) { filter_data($_POST); update_extra_order_details(); split_order_details(); -bulk_update_order_details(); +//bulk_update_order_details(); +pick_order_details(); +unpick_order_details(); process_picking_flag(); @@ -186,6 +188,11 @@ text_cells('Comment', 'bulk[comment]'); submit_cells('Bulk', 'Bulk'); } +function display_pick_area() { +submit_cells('Pick', 'Pick'); +submit_cells('Unpick', 'Unpick'); +} + if($customer_id !== null) { start_table(TABLESTYLE_NOBORDER); @@ -194,6 +201,9 @@ end_table(); start_table(TABLESTYLE_NOBORDER); display_bulk_area(); end_table(); +start_table(TABLESTYLE_NOBORDER); +display_pick_area(); +end_table(); } -- 2.30.2