From f821f29324e963437132a3f6fa7c57fcef5c5f0e Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Mon, 6 Apr 2015 11:48:15 +0200 Subject: [PATCH] Removed obsolete stock_move.person_id field and related handling. --- includes/db/inventory_db.inc | 16 ++-- .../includes/db/work_orders_quick_db.inc | 2 +- purchasing/includes/db/grn_db.inc | 5 +- purchasing/includes/db/invoice_db.inc | 2 +- sales/includes/db/sales_credit_db.inc | 2 +- sales/includes/db/sales_delivery_db.inc | 2 +- sql/alter2.4.sql | 2 + sql/en_US-demo.sql | 73 +++++++++---------- sql/en_US-new.sql | 1 - 9 files changed, 54 insertions(+), 51 deletions(-) diff --git a/includes/db/inventory_db.inc b/includes/db/inventory_db.inc index 48aa125d..9c9d2b2e 100644 --- a/includes/db/inventory_db.inc +++ b/includes/db/inventory_db.inc @@ -354,18 +354,18 @@ function handle_negative_inventory($stock_id, $quantity, $standard_cost, $date_) // $date_ - display / non-sql date // $std_cost - in HOME currency -// $price - in $person_id's currency +// $price - in transaction currency function add_stock_move($type, $stock_id, $trans_no, $location, - $date_, $reference, $quantity, $std_cost, $person_id=0, $price=0) + $date_, $reference, $quantity, $std_cost, $price=0) { $date = date2sql($date_); $sql = "INSERT INTO ".TB_PREF."stock_moves (stock_id, trans_no, type, loc_code, - tran_date, person_id, reference, qty, standard_cost, price) VALUES (" + tran_date, reference, qty, standard_cost, price) VALUES (" .db_escape($stock_id).", ".db_escape($trans_no).", " .db_escape($type).", ".db_escape($location).", '$date', " - .db_escape($person_id).", ".db_escape($reference).", " + .db_escape($reference).", " .db_escape($quantity).", ".db_escape($std_cost)."," .db_escape($price).")"; db_query($sql, "The stock movement record cannot be inserted"); @@ -403,7 +403,11 @@ function get_stock_moves($type, $type_no) function void_stock_move($type, $type_no) { - $sql = "SELECT * from ".TB_PREF."stock_moves WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no); + $sql = "SELECT move.*, supplier.supplier_id from ".TB_PREF."stock_moves move + LEFT JOIN ".TB_PREF."supp_trans credit ON credit.trans_no=move.trans_no AND credit.type=move.type + LEFT JOIN ".TB_PREF."grn_batch grn ON grn.id=move.trans_no AND 25=move.type + LEFT JOIN ".TB_PREF."suppliers supplier ON IFNULL(grn.supplier_id, credit.supplier_id)=supplier.supplier_id + WHERE move.type=".db_escape($type)." AND move.trans_no=".db_escape($type_no); $result = db_query($sql, "Could not void stock moves"); while ($row = db_fetch($result)) @@ -419,7 +423,7 @@ function void_stock_move($type, $type_no) else $unit_cost = $row["standard_cost"]; - update_average_material_cost($row["person_id"], $row["stock_id"], + update_average_material_cost($row["supplier_id"], $row["stock_id"], $unit_cost, -$row["qty"], sql2date($row["tran_date"])); } } diff --git a/manufacturing/includes/db/work_orders_quick_db.inc b/manufacturing/includes/db/work_orders_quick_db.inc index 74748024..89d8fa47 100644 --- a/manufacturing/includes/db/work_orders_quick_db.inc +++ b/manufacturing/includes/db/work_orders_quick_db.inc @@ -68,7 +68,7 @@ function add_work_order_quick($wo_ref, $loc_code, $units_reqd, $stock_id, $type, // insert a -ve stock move for each item $UnitCost = get_standard_cost($bom_item["component"]); add_stock_move(ST_WORKORDER, $bom_item["component"], $woid, - $bom_item["loc_code"], $date_, $wo_ref, -$item_quantity, $UnitCost, 0, $UnitCost); + $bom_item["loc_code"], $date_, $wo_ref, -$item_quantity, $UnitCost, $UnitCost); } // ------------------------------------------------------------------------- diff --git a/purchasing/includes/db/grn_db.inc b/purchasing/includes/db/grn_db.inc index 58320e7c..7604568f 100644 --- a/purchasing/includes/db/grn_db.inc +++ b/purchasing/includes/db/grn_db.inc @@ -152,8 +152,7 @@ function add_grn(&$po) $po->line_items[$line_no]->grn_item_id = $grn_item; /* Update location stock records - NB a po cannot be entered for a service/kit parts done automatically */ add_stock_move(ST_SUPPRECEIVE, $order_line->stock_id, $grn, $po->Location, $date_, "", - $order_line->receive_qty, $order_line->standard_cost, - $po->supplier_id, $order_line->taxfree_charge_price($po)); + $order_line->receive_qty, $order_line->standard_cost, $order_line->taxfree_charge_price($po)); } /*quantity received is != 0 */ } /*end of order_line loop */ @@ -259,7 +258,7 @@ function set_grn_item_credited(&$entered_grn, $supplier, $transno, $date) db_query($sql); add_stock_move(ST_SUPPCREDIT, $entered_grn->item_code, $transno, $myrow['loc_code'], $date, "", - $entered_grn->this_quantity_inv, $mcost, $supplier, $entered_grn->chg_price); + $entered_grn->this_quantity_inv, $mcost, $entered_grn->chg_price); } function get_grn_items($grn_batch_id=0, $supplier_id="", $outstanding_only=false, diff --git a/purchasing/includes/db/invoice_db.inc b/purchasing/includes/db/invoice_db.inc index ab5cd439..543df442 100644 --- a/purchasing/includes/db/invoice_db.inc +++ b/purchasing/includes/db/invoice_db.inc @@ -675,7 +675,7 @@ function remove_not_invoice_item($id) $price = get_tax_free_price_for_item($myrow['item_code'], $myrow['unit_price'], $supp['tax_group_id'], $supp['tax_included']); add_stock_move(ST_SUPPRECEIVE, $myrow["item_code"], $myrow['grn_batch_id'], $grn['loc_code'], sql2date($grn["delivery_date"]), "", - -$myrow["QtyOstdg"], $myrow['std_cost_unit'], $grn["supplier_id"], $price); + -$myrow["QtyOstdg"], $myrow['std_cost_unit'], $price); $clearing_act = get_company_pref('grn_clearing_act'); if ($clearing_act) { // otherwise GRN clearing account is not used diff --git a/sales/includes/db/sales_credit_db.inc b/sales/includes/db/sales_credit_db.inc index 25fe3fc2..b23b9600 100644 --- a/sales/includes/db/sales_credit_db.inc +++ b/sales/includes/db/sales_credit_db.inc @@ -193,7 +193,7 @@ function add_credit_movements_item(&$credit_note, &$credit_line, add_stock_move(ST_CUSTCREDIT, $credit_line->stock_id, key($credit_note->trans_no), $credit_note->Location, $credit_note->document_date, $reference, $credit_line->qty_dispatched, - $curr_std_cost, 0, $price); + $curr_std_cost, $price); } } diff --git a/sales/includes/db/sales_delivery_db.inc b/sales/includes/db/sales_delivery_db.inc index 43440132..51b709c1 100644 --- a/sales/includes/db/sales_delivery_db.inc +++ b/sales/includes/db/sales_delivery_db.inc @@ -96,7 +96,7 @@ function write_sales_delivery(&$delivery,$bo_policy) if ($delivery_line->qty_dispatched != 0) { add_stock_move(ST_CUSTDELIVERY, $delivery_line->stock_id, $delivery_no, $delivery->Location, $delivery->document_date, $delivery->reference, - -$delivery_line->qty_dispatched, $delivery_line->standard_cost, 0, + -$delivery_line->qty_dispatched, $delivery_line->standard_cost, $line_price*(1-$delivery_line->discount_percent)); $stock_gl_code = get_stock_gl_code($delivery_line->stock_id); diff --git a/sql/alter2.4.sql b/sql/alter2.4.sql index 55fc2ab6..727e91c3 100644 --- a/sql/alter2.4.sql +++ b/sql/alter2.4.sql @@ -236,4 +236,6 @@ UPDATE `0_stock_moves` SET price = price*(1-discount_percent); ALTER TABLE `0_stock_moves` DROP COLUMN `discount_percent`; +ALTER TABLE `0_stock_moves` DROP COLUMN `person_id`; + DROP TABLE IF EXISTS `0_movement_types`; diff --git a/sql/en_US-demo.sql b/sql/en_US-demo.sql index 75c9fe04..62a93c3f 100644 --- a/sql/en_US-demo.sql +++ b/sql/en_US-demo.sql @@ -1812,7 +1812,6 @@ CREATE TABLE `0_stock_moves` ( `type` smallint(6) NOT NULL DEFAULT '0', `loc_code` char(5) NOT NULL DEFAULT '', `tran_date` date NOT NULL DEFAULT '0000-00-00', - `person_id` int(11) DEFAULT NULL, `price` double NOT NULL DEFAULT '0', `reference` char(40) NOT NULL DEFAULT '', `qty` double NOT NULL DEFAULT '1', @@ -1827,42 +1826,42 @@ CREATE TABLE `0_stock_moves` ( -- INSERT INTO `0_stock_moves` VALUES -('1', '1', '102', '25', 'DEF', '2009-06-21', '2', '10', '', '100', '10'), -('2', '1', '103', '25', 'DEF', '2009-06-21', '2', '11', '', '100', '11'), -('3', '1', '104', '25', 'DEF', '2009-06-21', '2', '12', '', '100', '12'), -('4', '1', '102', '26', 'DEF', '2009-06-21', '0', '0', '1', '-10', '0'), -('5', '1', '103', '26', 'DEF', '2009-06-21', '0', '0', '1', '-10', '0'), -('6', '1', '104', '26', 'DEF', '2009-06-21', '0', '0', '1', '-10', '0'), -('7', '1', '3400', '26', 'DEF', '2009-06-21', '0', '0', '1', '10', '0'), -('8', '2', '102', '13', 'DEF', '2009-06-21', '0', '30.4', '1', '-2', '10'), -('9', '3', '102', '13', 'DEF', '2009-06-21', '0', '50', 'auto', '-1', '10'), -('10', '4', '102', '13', 'DEF', '2009-06-21', '0', '35.89', 'auto', '-1', '10'), -('11', '3', '102', '26', 'DEF', '2009-06-21', '0', '0', '3', '-2', '0'), -('12', '3', '103', '26', 'DEF', '2009-06-21', '0', '0', '3', '-2', '0'), -('13', '3', '104', '26', 'DEF', '2009-06-21', '0', '0', '3', '-2', '0'), -('14', '3', '3400', '26', 'DEF', '2009-06-21', '0', '0', '3', '2', '0'), -('15', '4', '102', '26', 'DEF', '2009-06-21', '0', '0', '4', '-4', '0'), -('16', '4', '103', '26', 'DEF', '2009-06-21', '0', '0', '4', '-4', '0'), -('17', '4', '104', '26', 'DEF', '2009-06-21', '0', '0', '4', '-4', '0'), -('18', '4', '3400', '26', 'DEF', '2009-06-21', '0', '0', '4', '4', '0'), -('19', '2', '102', '26', 'DEF', '2009-06-21', '0', '0', '', '-2', '0'), -('20', '2', '103', '26', 'DEF', '2009-06-21', '0', '0', '', '-2', '0'), -('21', '2', '104', '26', 'DEF', '2009-06-21', '0', '0', '', '-2', '0'), -('22', '1', '3400', '29', 'DEF', '2009-06-21', '0', '0', '', '2', '0'), -('23', '5', '102', '26', 'DEF', '2009-06-21', '0', '0', '5', '-5', '0'), -('24', '5', '103', '26', 'DEF', '2009-06-21', '0', '0', '5', '-5', '0'), -('25', '5', '104', '26', 'DEF', '2009-06-21', '0', '0', '5', '-5', '0'), -('26', '5', '3400', '26', 'DEF', '2009-06-21', '0', '0', '5', '5', '0'), -('27', '6', '102', '26', 'DEF', '2009-06-21', '0', '0', '6', '5', '0'), -('28', '6', '103', '26', 'DEF', '2009-06-21', '0', '0', '6', '5', '0'), -('29', '6', '104', '26', 'DEF', '2009-06-21', '0', '0', '6', '5', '0'), -('30', '6', '3400', '26', 'DEF', '2009-06-21', '0', '0', '6', '-5', '0'), -('31', '7', '102', '26', 'DEF', '2009-06-21', '0', '0', '7', '2', '0'), -('32', '7', '103', '26', 'DEF', '2009-06-21', '0', '0', '7', '2', '0'), -('33', '7', '104', '26', 'DEF', '2009-06-21', '0', '0', '7', '2', '0'), -('34', '7', '3400', '26', 'DEF', '2009-06-21', '0', '0', '7', '-2', '0'), -('35', '5', '102', '13', 'DEF', '2009-06-21', '0', '50', 'auto', '-1', '10'), -('36', '3', '102', '11', 'DEF', '2009-06-21', '0', '37.68', 'Return Ex Inv: 18', '1', '10'); +('1', '1', '102', '25', 'DEF', '2009-06-21', '10', '', '100', '10'), +('2', '1', '103', '25', 'DEF', '2009-06-21', '11', '', '100', '11'), +('3', '1', '104', '25', 'DEF', '2009-06-21', '12', '', '100', '12'), +('4', '1', '102', '26', 'DEF', '2009-06-21', '0', '1', '-10', '0'), +('5', '1', '103', '26', 'DEF', '2009-06-21', '0', '1', '-10', '0'), +('6', '1', '104', '26', 'DEF', '2009-06-21', '0', '1', '-10', '0'), +('7', '1', '3400', '26', 'DEF', '2009-06-21', '0', '1', '10', '0'), +('8', '2', '102', '13', 'DEF', '2009-06-21', '30.4', '1', '-2', '10'), +('9', '3', '102', '13', 'DEF', '2009-06-21', '50', 'auto', '-1', '10'), +('10', '4', '102', '13', 'DEF', '2009-06-21', '35.89', 'auto', '-1', '10'), +('11', '3', '102', '26', 'DEF', '2009-06-21', '0', '3', '-2', '0'), +('12', '3', '103', '26', 'DEF', '2009-06-21', '0', '3', '-2', '0'), +('13', '3', '104', '26', 'DEF', '2009-06-21', '0', '3', '-2', '0'), +('14', '3', '3400', '26', 'DEF', '2009-06-21', '0', '3', '2', '0'), +('15', '4', '102', '26', 'DEF', '2009-06-21', '0', '4', '-4', '0'), +('16', '4', '103', '26', 'DEF', '2009-06-21', '0', '4', '-4', '0'), +('17', '4', '104', '26', 'DEF', '2009-06-21', '0', '4', '-4', '0'), +('18', '4', '3400', '26', 'DEF', '2009-06-21', '0', '4', '4', '0'), +('19', '2', '102', '26', 'DEF', '2009-06-21', '0', '', '-2', '0'), +('20', '2', '103', '26', 'DEF', '2009-06-21', '0', '', '-2', '0'), +('21', '2', '104', '26', 'DEF', '2009-06-21', '0', '', '-2', '0'), +('22', '1', '3400', '29', 'DEF', '2009-06-21', '0', '', '2', '0'), +('23', '5', '102', '26', 'DEF', '2009-06-21', '0', '5', '-5', '0'), +('24', '5', '103', '26', 'DEF', '2009-06-21', '0', '5', '-5', '0'), +('25', '5', '104', '26', 'DEF', '2009-06-21', '0', '5', '-5', '0'), +('26', '5', '3400', '26', 'DEF', '2009-06-21', '0', '5', '5', '0'), +('27', '6', '102', '26', 'DEF', '2009-06-21', '0', '6', '5', '0'), +('28', '6', '103', '26', 'DEF', '2009-06-21', '0', '6', '5', '0'), +('29', '6', '104', '26', 'DEF', '2009-06-21', '0', '6', '5', '0'), +('30', '6', '3400', '26', 'DEF', '2009-06-21', '0', '6', '-5', '0'), +('31', '7', '102', '26', 'DEF', '2009-06-21', '0', '7', '2', '0'), +('32', '7', '103', '26', 'DEF', '2009-06-21', '0', '7', '2', '0'), +('33', '7', '104', '26', 'DEF', '2009-06-21', '0', '7', '2', '0'), +('34', '7', '3400', '26', 'DEF', '2009-06-21', '0', '7', '-2', '0'), +('35', '5', '102', '13', 'DEF', '2009-06-21', '50', 'auto', '-1', '10'), +('36', '3', '102', '11', 'DEF', '2009-06-21', '37.68', 'Return Ex Inv: 18', '1', '10'); ### Structure of table `0_supp_allocations` ### diff --git a/sql/en_US-new.sql b/sql/en_US-new.sql index 1cd4bd33..31947a52 100644 --- a/sql/en_US-new.sql +++ b/sql/en_US-new.sql @@ -1618,7 +1618,6 @@ CREATE TABLE `0_stock_moves` ( `type` smallint(6) NOT NULL DEFAULT '0', `loc_code` char(5) NOT NULL DEFAULT '', `tran_date` date NOT NULL DEFAULT '0000-00-00', - `person_id` int(11) DEFAULT NULL, `price` double NOT NULL DEFAULT '0', `reference` char(40) NOT NULL DEFAULT '', `qty` double NOT NULL DEFAULT '1', -- 2.30.2