From 4568f3745a4a9e6efd6d796baccc0e9b23b81764 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Fri, 20 Feb 2015 10:34:29 +0100 Subject: [PATCH] Added dimension edition on purchase invoice entry. --- purchasing/includes/db/invoice_db.inc | 17 ++++----- purchasing/includes/db/invoice_items_db.inc | 38 ++++++++++----------- purchasing/includes/po_class.inc | 2 ++ purchasing/includes/purchasing_db.inc | 2 ++ purchasing/includes/supp_trans_class.inc | 3 ++ purchasing/includes/ui/invoice_ui.inc | 10 ++++++ purchasing/includes/ui/po_ui.inc | 10 +++++- sql/alter2.4.sql | 9 +++++ sql/en_US-demo.sql | 12 ++++--- sql/en_US-new.sql | 2 ++ 10 files changed, 72 insertions(+), 33 deletions(-) diff --git a/purchasing/includes/db/invoice_db.inc b/purchasing/includes/db/invoice_db.inc index dd172395..707704cf 100644 --- a/purchasing/includes/db/invoice_db.inc +++ b/purchasing/includes/db/invoice_db.inc @@ -126,7 +126,8 @@ function add_supp_invoice(&$supp_trans) // do not receive as ref because we chan //$company_currency = get_company_currency(); $trans_no = $supp_trans->trans_no; $trans_type = $supp_trans->trans_type; - /*Start an sql transaction */ + $supplier = get_supplier($supp_trans->supplier_id); + begin_transaction(); hook_db_prewrite($supp_trans, $trans_type); $tax_total = 0; @@ -138,8 +139,6 @@ function add_supp_invoice(&$supp_trans) // do not receive as ref because we chan } else $allocs = get_po_prepayments($supp_trans); - $supplier = get_supplier($supp_trans->supplier_id); - add_new_exchange_rate($supplier['curr_code'], $supp_trans->tran_date, $supp_trans->ex_rate); foreach ($taxes as $n => $taxitem) @@ -234,8 +233,8 @@ function add_supp_invoice(&$supp_trans) // do not receive as ref because we chan $total += add_gl_trans_supplier($trans_type, $invoice_id, $date_, $entered_gl_code->gl_code, $entered_gl_code->gl_dim, $entered_gl_code->gl_dim2, $entered_gl_code->amount, $supp_trans->supplier_id, "", 0, $memo_); - add_supp_invoice_gl_item($trans_type, $invoice_id, $entered_gl_code->gl_code, - $entered_gl_code->amount, $memo_); + add_supp_invoice_gl_item($trans_type, $invoice_id, $entered_gl_code->gl_code, $entered_gl_code->amount, $memo_, + $entered_gl_code->gl_dim, $entered_gl_code->gl_dim2); // store tax details if the gl account is a tax account if ($trans_type == ST_SUPPCREDIT) @@ -270,8 +269,10 @@ function add_supp_invoice(&$supp_trans) // do not receive as ref because we chan $stock_gl_code = get_stock_gl_code($entered_grn->item_code); - $dim = $supplier['dimension_id'] ? $supplier['dimension_id'] : $stock_gl_code['dimension_id']; - $dim2 = $supplier['dimension2_id'] ? $supplier['dimension2_id'] : $stock_gl_code['dimension2_id']; + $dim = !empty($supp_trans->dimension) ? $supp_trans->dimension : + ($supplier['dimension_id'] ? $supplier['dimension_id'] : $stock_gl_code['dimension_id']); + $dim2 = !empty($supp_trans->dimension2) ? $supp_trans->dimension2 : + ($supplier['dimension2_id'] ? $supplier['dimension2_id'] : $stock_gl_code['dimension2_id']); if ($trans_type == ST_SUPPCREDIT) { $iv_act = (is_inventory_item($entered_grn->item_code) ? $stock_gl_code["inventory_account"] : @@ -490,7 +491,7 @@ function read_supp_invoice($trans_no, $trans_type, &$supp_trans) else { $supp_trans->add_gl_codes_to_trans($details_row["gl_code"], get_gl_account_name($details_row["gl_code"]), 0, 0, - $details_row["FullUnitPrice"], $details_row["memo_"]); + $details_row["FullUnitPrice"], $details_row["memo_"], $details_row["dimension_id"], $details_row["dimension2_id"]); } } $supp_trans->tax_overrides = get_tax_overrides($trans_type, $trans_no); diff --git a/purchasing/includes/db/invoice_items_db.inc b/purchasing/includes/db/invoice_items_db.inc index 00fdbae8..770f5b43 100644 --- a/purchasing/includes/db/invoice_items_db.inc +++ b/purchasing/includes/db/invoice_items_db.inc @@ -12,48 +12,48 @@ //------------------------------------------------------------------------------------------------------------- function add_supp_invoice_item($supp_trans_type, $supp_trans_no, $stock_id, $description, - $gl_code, $unit_price, $unit_tax, $quantity, $grn_item_id, $po_detail_item_id, $memo_, - $err_msg="") + $gl_code, $unit_price, $unit_tax, $quantity, $grn_item_id, $po_detail_item_id, $memo_, $dim_id=0, $dim2_id=0) { $sql = "INSERT INTO ".TB_PREF."supp_invoice_items (supp_trans_type, supp_trans_no, stock_id, description, gl_code, unit_price, unit_tax, quantity, - grn_item_id, po_detail_item_id, memo_) "; + grn_item_id, po_detail_item_id, memo_, dimension_id, dimension2_id) "; $sql .= "VALUES (".db_escape($supp_trans_type).", ".db_escape($supp_trans_no).", " .db_escape($stock_id). ", ".db_escape($description).", ".db_escape($gl_code).", ".db_escape($unit_price) - .", ".db_escape($unit_tax).", ".db_escape($quantity).", - ".db_escape($grn_item_id).", ".db_escape($po_detail_item_id).", ".db_escape($memo_).")"; + .", ".db_escape($unit_tax).", ".db_escape($quantity).", ".db_escape($grn_item_id) + .", ".db_escape($po_detail_item_id).", ".db_escape($memo_) + .", ".db_escape($dim_id).", ".db_escape($dim2_id).")"; - if ($err_msg == "") - $err_msg = "Cannot insert a supplier transaction detail record"; - - db_query($sql, $err_msg); + db_query($sql, "Cannot insert a supplier transaction detail record"); return db_insert_id(); } //------------------------------------------------------------------------------------------------------------- -function add_supp_invoice_gl_item($supp_trans_type, $supp_trans_no, $gl_code, $amount, $memo_, $err_msg="") +function add_supp_invoice_gl_item($supp_trans_type, $supp_trans_no, $gl_code, $amount, $memo_, $dim_id=0, $dim2_id=0) { return add_supp_invoice_item($supp_trans_type, $supp_trans_no, "", "", $gl_code, $amount, - 0, 0, /*$grn_item_id*/0, /*$po_detail_item_id*/0, $memo_, $err_msg); + 0, 0, /*$grn_item_id*/-1, /*$po_detail_item_id*/0, $memo_, 0, $dim_id, $dim2_id); } -//---------------------------------------------------------------------------------------- - function get_supp_invoice_items($supp_trans_type, $supp_trans_no) { - $sql = "SELECT *, unit_price AS FullUnitPrice FROM " - .TB_PREF."supp_invoice_items inv LEFT JOIN ".TB_PREF."grn_items grn ON grn.id =inv.grn_item_id + $sql = "SELECT inv.*, grn.*, unit_price AS FullUnitPrice, + stock.parallel_qe, + stock.units, + tax_type.exempt, + tax_type.name as tax_type_name + FROM " + .TB_PREF."supp_invoice_items inv LEFT JOIN ".TB_PREF."grn_items grn ON grn.id =inv.grn_item_id + LEFT JOIN ".TB_PREF."stock_master stock ON stock.stock_id=inv.stock_id + LEFT JOIN ".TB_PREF."item_tax_types tax_type ON stock.tax_type_id=tax_type.id WHERE supp_trans_type = ".db_escape($supp_trans_type)." - AND supp_trans_no = ".db_escape($supp_trans_no) - ." ORDER BY inv.id"; + AND supp_trans_no = ".db_escape($supp_trans_no) + ." ORDER BY inv.id"; return db_query($sql, "Cannot retreive supplier transaction detail records"); } -//---------------------------------------------------------------------------------------- - function void_supp_invoice_items($type, $type_no) { $sql = "UPDATE ".TB_PREF."supp_invoice_items SET quantity=0, unit_price=0 diff --git a/purchasing/includes/po_class.inc b/purchasing/includes/po_class.inc index 0464c42b..47129816 100644 --- a/purchasing/includes/po_class.inc +++ b/purchasing/includes/po_class.inc @@ -37,6 +37,8 @@ class purch_order var $terms; var $ex_rate; var $cash_account; + var $dimension, + $dimension2; var $reference; var $tax_overrides = array(); // array of taxes manually inserted during sales invoice entry (direct invoice) diff --git a/purchasing/includes/purchasing_db.inc b/purchasing/includes/purchasing_db.inc index 2a2c2399..149b42d9 100644 --- a/purchasing/includes/purchasing_db.inc +++ b/purchasing/includes/purchasing_db.inc @@ -184,6 +184,8 @@ function add_direct_supp_trans($cart) $inv->supplier_id = $cart->supplier_id; $inv->tran_date = $cart->orig_order_date; $inv->due_date = $cart->due_date; + $inv->dimension = $cart->dimension; + $inv->dimension2 = $cart->dimension2; $inv->reference = $ref; $inv->supp_reference = $cart->supp_ref; $inv->tax_included = $cart->tax_included; diff --git a/purchasing/includes/supp_trans_class.inc b/purchasing/includes/supp_trans_class.inc index cfa66169..99fe1934 100644 --- a/purchasing/includes/supp_trans_class.inc +++ b/purchasing/includes/supp_trans_class.inc @@ -48,6 +48,9 @@ class supp_trans var $currency; var $tax_overrides = array(); // array of taxes manually inserted during sales invoice entry + var $dimension, + $dimension2; + function supp_trans($trans_type, $trans_no=0) { $this->trans_type = $trans_type; diff --git a/purchasing/includes/ui/invoice_ui.inc b/purchasing/includes/ui/invoice_ui.inc index d16e8d13..5846af6e 100644 --- a/purchasing/includes/ui/invoice_ui.inc +++ b/purchasing/includes/ui/invoice_ui.inc @@ -20,6 +20,8 @@ function copy_from_trans(&$supp_trans) $_POST['reference'] = $supp_trans->reference; $_POST['supplier_id'] = $supp_trans->supplier_id; $_POST['tax_algorithm'] = $supp_trans->tax_algorithm; + $_POST['dimension'] = $supp_trans->dimension; + $_POST['dimension2'] = $supp_trans->dimension2; $_POST['_ex_rate'] = $supp_trans->ex_rate; if (isset($supp_trans->tax_overrides)) @@ -38,6 +40,8 @@ function copy_to_trans(&$supp_trans) $supp_trans->reference = $_POST['reference']; if (isset($_POST['tax_algorithm'])) $supp_trans->tax_algorithm = $_POST['tax_algorithm']; + $supp_trans->dimension = @$_POST['dimension']; + $supp_trans->dimension2 = @$_POST['dimension2']; $supp_trans->ex_rate = input_num('_ex_rate', null); @@ -124,6 +128,12 @@ function invoice_header(&$supp_trans) } text_row(_("Supplier's Ref.:"), 'supp_reference', $_POST['supp_reference'], 20, 60); + if (get_company_pref('use_dimension')) + dimensions_list_row(_('Dimension').':', 'dimension', null, true, _('Default'), false, 1); + + if (get_company_pref('use_dimension') == 2) + dimensions_list_row(_('Dimension 2').':', 'dimension2', null, true, _('Default'), false, 2); + table_section(2, "33%"); date_row(_("Date") . ":", 'tran_date', '', true, 0, 0, 0, "", true); diff --git a/purchasing/includes/ui/po_ui.inc b/purchasing/includes/ui/po_ui.inc index ed696468..811bcb88 100644 --- a/purchasing/includes/ui/po_ui.inc +++ b/purchasing/includes/ui/po_ui.inc @@ -30,6 +30,8 @@ function copy_from_cart() $_POST['prep_amount'] = price_format($cart->prep_amount); $_POST['_ex_rate'] = $cart->ex_rate; $_POST['cash_account'] = $cart->cash_account; + $_POST['dimension'] = $cart->dimension; + $_POST['dimension2'] = $cart->dimension2; foreach($cart->tax_overrides as $id => $value) $_POST['mantax'][$id] = price_format($value); } @@ -50,6 +52,8 @@ function copy_to_cart() $cart->Comments = $_POST['Comments']; $cart->Location = $_POST['StkLocation']; $cart->delivery_address = $_POST['delivery_address']; + $cart->dimension = @$_POST['dimension']; + $cart->dimension2 = @$_POST['dimension2']; if (isset($_POST['tax_algorithm'])) $cart->tax_algorithm = $_POST['tax_algorithm']; $cart->prep_amount = input_num('prep_amount', 0); @@ -151,7 +155,6 @@ function display_po_header(&$order) $_POST['OrderDate']); } - if ($editable) { ref_row(_("Reference:"), 'ref'); @@ -168,6 +171,11 @@ function display_po_header(&$order) date_row(_("Due Date:"), 'due_date', '', false, 0, 0, 0, null, true); text_row(_("Supplier's Reference:"), 'supp_ref', null, 16, 15); + + if (get_company_pref('use_dimension')) + dimensions_list_row(_('Dimension').':', 'dimension', null, true, _('Default'), false, 1); + if (get_company_pref('use_dimension') == 2) + dimensions_list_row(_('Dimension 2').':', 'dimension2', null, true, _('Default'), false, 2); locations_list_row(_("Receive Into:"), 'StkLocation', null, false, true); table_section(3); diff --git a/sql/alter2.4.sql b/sql/alter2.4.sql index b250e04a..92d77970 100644 --- a/sql/alter2.4.sql +++ b/sql/alter2.4.sql @@ -142,3 +142,12 @@ ALTER TABLE `0_workcentres` ENGINE=InnoDB; ALTER TABLE `0_gl_trans` CHANGE `type_no` `type_no` int(11) NOT NULL default '0'; ALTER TABLE `0_loc_stock` CHANGE `reorder_level` `reorder_level` double NOT NULL default '0'; + +# added dimensions in supplier documents +ALTER TABLE `0_supp_invoice_items` ADD COLUMN `dimension_id` int(11) NOT NULL DEFAULT '0' AFTER `memo_`; +ALTER TABLE `0_supp_invoice_items` ADD COLUMN `dimension2_id` int(11) NOT NULL DEFAULT '0' AFTER `dimension_id`; + +UPDATE `0_supp_invoice_items` si + LEFT JOIN `0_gl_trans` gl ON si.supp_trans_type=gl.`type` AND si.supp_trans_no=gl.type_no AND si.gl_code=gl.account + SET si.dimension_id=gl.dimension_id, si.dimension2_id=gl.dimension2_id +WHERE si.grn_item_id=-1 AND (gl.dimension_id OR gl.dimension2_id) diff --git a/sql/en_US-demo.sql b/sql/en_US-demo.sql index 7e23a8ed..ae3aefde 100644 --- a/sql/en_US-demo.sql +++ b/sql/en_US-demo.sql @@ -1952,6 +1952,8 @@ CREATE TABLE IF NOT EXISTS `0_supp_invoice_items` ( `unit_price` double NOT NULL default '0', `unit_tax` double NOT NULL default '0', `memo_` tinytext, + `dimension_id` int(11) NOT NULL DEFAULT '0', + `dimension2_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `Transaction` (`supp_trans_type`,`supp_trans_no`,`stock_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ; @@ -1960,11 +1962,11 @@ CREATE TABLE IF NOT EXISTS `0_supp_invoice_items` ( -- Dumping data for table `0_supp_invoice_items` -- -INSERT INTO `0_supp_invoice_items` VALUES(1, 7, 20, '0', 1, 1, '102', '17inch VGA Monitor', 100, 10, 0.5, ''); -INSERT INTO `0_supp_invoice_items` VALUES(2, 7, 20, '0', 2, 2, '103', '32MB VGA Card', 100, 11, 0.55, ''); -INSERT INTO `0_supp_invoice_items` VALUES(3, 7, 20, '0', 3, 3, '104', '52x CD Drive', 100, 12, 0.6, ''); -INSERT INTO `0_supp_invoice_items` VALUES(4, 8, 20, '2150', 0, 0, '', '', 0, 0.95, 0, 'Phone'); -INSERT INTO `0_supp_invoice_items` VALUES(5, 8, 20, '5780', 0, 0, '', '', 0, 19.05, 0, 'Phone'); +INSERT INTO `0_supp_invoice_items` VALUES(1, 7, 20, '0', 1, 1, '102', '17inch VGA Monitor', 100, 10, 0.5, '', 0, 0); +INSERT INTO `0_supp_invoice_items` VALUES(2, 7, 20, '0', 2, 2, '103', '32MB VGA Card', 100, 11, 0.55, '', 0, 0); +INSERT INTO `0_supp_invoice_items` VALUES(3, 7, 20, '0', 3, 3, '104', '52x CD Drive', 100, 12, 0.6, '', 0, 0); +INSERT INTO `0_supp_invoice_items` VALUES(4, 8, 20, '2150', 0, 0, '', '', 0, 0.95, 0, 'Phone', 0, 0); +INSERT INTO `0_supp_invoice_items` VALUES(5, 8, 20, '5780', 0, 0, '', '', 0, 19.05, 0, 'Phone', 0, 0); -- -------------------------------------------------------- diff --git a/sql/en_US-new.sql b/sql/en_US-new.sql index 3012d757..73d29c9e 100644 --- a/sql/en_US-new.sql +++ b/sql/en_US-new.sql @@ -1721,6 +1721,8 @@ CREATE TABLE IF NOT EXISTS `0_supp_invoice_items` ( `unit_price` double NOT NULL default '0', `unit_tax` double NOT NULL default '0', `memo_` tinytext, + `dimension_id` int(11) NOT NULL DEFAULT '0', + `dimension2_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `Transaction` (`supp_trans_type`,`supp_trans_no`,`stock_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; -- 2.30.2