From 6d27f64388100dbbfad58ccb52c1ed334dd74e16 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Sun, 17 May 2009 21:22:45 +0000 Subject: [PATCH] Added optional excluding items from sales. --- CHANGELOG.txt | 12 ++++++- includes/ui/ui_lists.inc | 11 +++--- inventory/includes/db/items_category_db.inc | 12 ++++--- inventory/includes/db/items_db.inc | 12 ++++--- inventory/manage/item_categories.php | 9 +++-- inventory/manage/items.php | 12 +++++-- sql/alter2.2.php | 40 +++++++++++++++------ sql/alter2.2.sql | 2 ++ 8 files changed, 80 insertions(+), 30 deletions(-) diff --git a/CHANGELOG.txt b/CHANGELOG.txt index 8be0a287..794464cc 100644 --- a/CHANGELOG.txt +++ b/CHANGELOG.txt @@ -19,11 +19,21 @@ Legend: ! -> Note $ -> Affected files +17-May-2009 Janusz Dobrowolski ++ Added excluding item/category from sales. +$ /includes/ui/ui_lists.inc + /inventory/includes/db/items_category_db.inc + /inventory/includes/db/items_db.inc + /inventory/manage/item_categories.php + /inventory/manage/items.php + /sql/alter2.2.sql + /sql/alter2.2.php + 15-May-2009 Joe Hunt # Bad link to view dimension $ /includes/ui/ui_view.inc ! Improved layout. -$ /purchasing/includes/ui/invoice_ud.inc +$ /purchasing/includes/ui/invoice_ui.inc 14-May-2009 Joe Hunt + Added user_id to Journal Inquiry diff --git a/includes/ui/ui_lists.inc b/includes/ui/ui_lists.inc index acdd3d1b..bde06e22 100644 --- a/includes/ui/ui_lists.inc +++ b/includes/ui/ui_lists.inc @@ -710,17 +710,20 @@ function sales_items_list($name, $selected_id=null, $all_option=false, $sql = "SELECT i.item_code, i.description, c.description, count(*)>1 as kit, i.inactive FROM + ".TB_PREF."stock_master s, ".TB_PREF."item_codes i LEFT JOIN ".TB_PREF."stock_category c - ON i.category_id=c.category_id"; + ON i.category_id=c.category_id + WHERE i.stock_id=s.stock_id"; + if ($type == 'local') { // exclude foreign codes - $sql .= " WHERE !i.is_foreign"; + $sql .= " AND !i.is_foreign"; } elseif ($type == 'kits') { // sales kits - $sql .= " WHERE !i.is_foreign AND i.item_code!=i.stock_id"; + $sql .= " AND !i.is_foreign AND i.item_code!=i.stock_id"; } - $sql .= " AND !i.inactive"; + $sql .= " AND !i.inactive AND !s.inactive AND !s.no_sale"; $sql .= " GROUP BY i.item_code"; return combo_input($name, $selected_id, $sql, 'i.item_code', 'c.description', diff --git a/inventory/includes/db/items_category_db.inc b/inventory/includes/db/items_category_db.inc index 2d3cefd1..f7d4ed6b 100644 --- a/inventory/includes/db/items_category_db.inc +++ b/inventory/includes/db/items_category_db.inc @@ -11,12 +11,12 @@ ***********************************************************************/ function add_item_category($description, $tax_type_id, $sales_account, $cogs_account, $inventory_account, $adjustment_account, $assembly_account, - $units, $mb_flag, $dim1, $dim2) + $units, $mb_flag, $dim1, $dim2, $no_sale) { $sql = "INSERT INTO ".TB_PREF."stock_category (description, dflt_tax_type, dflt_units, dflt_mb_flag, dflt_sales_act, dflt_cogs_act, dflt_inventory_act, dflt_adjustment_act, dflt_assembly_act, - dflt_dim1, dflt_dim2) + dflt_dim1, dflt_dim2, dflt_no_sale) VALUES (" .db_escape($description)."," .db_escape($tax_type_id)."," @@ -28,14 +28,15 @@ function add_item_category($description, $tax_type_id, $sales_account, .db_escape($adjustment_account)."," .db_escape($assembly_account)."," .db_escape($dim1)."," - .db_escape($dim2).")"; + .db_escape($dim2)."," + .db_escape($no_sale).")"; db_query($sql,"an item category could not be added"); } function update_item_category($id, $description, $tax_type_id, $sales_account, $cogs_account, $inventory_account, $adjustment_account, - $assembly_account, $units, $mb_flag, $dim1, $dim2) + $assembly_account, $units, $mb_flag, $dim1, $dim2, $no_sale) { $sql = "UPDATE ".TB_PREF."stock_category SET " @@ -49,7 +50,8 @@ function update_item_category($id, $description, $tax_type_id, ."dflt_adjustment_act = ".db_escape($adjustment_account)."," ."dflt_assembly_act = ".db_escape($assembly_account)."," ."dflt_dim1 = ".db_escape($dim1)."," - ."dflt_dim2 = ".db_escape($dim2) + ."dflt_dim2 = ".db_escape($dim2)."," + ."dflt_no_sale = '$no_sale'" ." WHERE category_id = '$id'"; db_query($sql,"an item category could not be updated"); diff --git a/inventory/includes/db/items_db.inc b/inventory/includes/db/items_db.inc index 410ed990..9af892d9 100644 --- a/inventory/includes/db/items_db.inc +++ b/inventory/includes/db/items_db.inc @@ -12,7 +12,7 @@ function update_item($stock_id, $description, $long_description, $category_id, $tax_type_id, $units='', $mb_flag='', $sales_account, $inventory_account, $cogs_account, $adjustment_account, $assembly_account, $dimension_id, - $dimension2_id) + $dimension2_id, $no_sale) { $sql = "UPDATE ".TB_PREF."stock_master SET long_description=".db_escape($long_description).", description=".db_escape($description).", @@ -24,7 +24,8 @@ function update_item($stock_id, $description, $long_description, $category_id, assembly_account='$assembly_account', dimension_id=$dimension_id, dimension2_id=$dimension2_id, - tax_type_id=$tax_type_id"; + tax_type_id=$tax_type_id, + no_sale='$no_sale'"; if ($units != '') $sql .= ", units='$units'"; @@ -42,15 +43,16 @@ function update_item($stock_id, $description, $long_description, $category_id, function add_item($stock_id, $description, $long_description, $category_id, $tax_type_id, $units, $mb_flag, $sales_account, $inventory_account, $cogs_account, $adjustment_account, $assembly_account, $dimension_id, - $dimension2_id) + $dimension2_id, $no_sale) { $sql = "INSERT INTO ".TB_PREF."stock_master (stock_id, description, long_description, category_id, tax_type_id, units, mb_flag, sales_account, inventory_account, cogs_account, - adjustment_account, assembly_account, dimension_id, dimension2_id) + adjustment_account, assembly_account, dimension_id, dimension2_id, no_sale) VALUES (".db_escape($stock_id).", ".db_escape($description).", ".db_escape($long_description).", '$category_id', $tax_type_id, '$units', '$mb_flag', '$sales_account', '$inventory_account', '$cogs_account', - '$adjustment_account', '$assembly_account', $dimension_id, $dimension2_id)"; + '$adjustment_account', '$assembly_account', $dimension_id, $dimension2_id, + '$no_sale')"; db_query($sql, "The item could not be added"); diff --git a/inventory/manage/item_categories.php b/inventory/manage/item_categories.php index 59873122..ec54c174 100644 --- a/inventory/manage/item_categories.php +++ b/inventory/manage/item_categories.php @@ -43,7 +43,8 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') $_POST['tax_type_id'], $_POST['sales_account'], $_POST['cogs_account'], $_POST['inventory_account'], $_POST['adjustment_account'], $_POST['assembly_account'], - $_POST['units'], $_POST['mb_flag'], $_POST['dim1'], $_POST['dim2']); + $_POST['units'], $_POST['mb_flag'], $_POST['dim1'], $_POST['dim2'], + check_value('no_sale')); display_notification(_('Selected item category has been updated')); } else @@ -53,7 +54,7 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') $_POST['cogs_account'], $_POST['inventory_account'], $_POST['adjustment_account'], $_POST['assembly_account'], $_POST['units'], $_POST['mb_flag'], $_POST['dim1'], - $_POST['dim2']); + $_POST['dim2'], check_value('no_sale')); display_notification(_('New item category has been added')); } $Mode = 'RESET'; @@ -155,12 +156,14 @@ if ($selected_id != -1) $_POST['mb_flag'] = $myrow["dflt_mb_flag"]; $_POST['dim1'] = $myrow["dflt_dim1"]; $_POST['dim2'] = $myrow["dflt_dim2"]; + $_POST['no_sale'] = $myrow["dflt_no_sale"]; } hidden('selected_id', $selected_id); hidden('category_id'); } else { $_POST['long_description'] = ''; $_POST['description'] = ''; + $_POST['no_sale'] = 0; $company_record = get_company_prefs(); @@ -191,6 +194,8 @@ stock_item_types_list_row(_("Item Type:"), 'mb_flag', null, true); stock_units_list_row(_("Units of Measure:"), 'units', null); +check_row(_("Exclude from sales:"), 'no_sale', $_POST['no_sale']); + gl_all_accounts_list_row(_("Sales Account:"), 'sales_account', $_POST['sales_account']); gl_all_accounts_list_row(_("Inventory Account:"), 'inventory_account', $_POST['inventory_account']); diff --git a/inventory/manage/items.php b/inventory/manage/items.php index 3d136119..e4db890c 100644 --- a/inventory/manage/items.php +++ b/inventory/manage/items.php @@ -168,7 +168,8 @@ if (isset($_POST['addupdate'])) get_post('mb_flag'), $_POST['sales_account'], $_POST['inventory_account'], $_POST['cogs_account'], $_POST['adjustment_account'], $_POST['assembly_account'], - $_POST['dimension_id'], $_POST['dimension2_id']); + $_POST['dimension_id'], $_POST['dimension2_id'], + check_value('no_sale')); update_record_status($_POST['NewStockID'], $_POST['inactive'], 'stock_master', 'stock_id'); update_record_status($_POST['NewStockID'], $_POST['inactive'], @@ -185,7 +186,8 @@ if (isset($_POST['addupdate'])) $_POST['units'], $_POST['mb_flag'], $_POST['sales_account'], $_POST['inventory_account'], $_POST['cogs_account'], $_POST['adjustment_account'], $_POST['assembly_account'], - $_POST['dimension_id'], $_POST['dimension2_id']); + $_POST['dimension_id'], $_POST['dimension2_id'], + check_value('no_sale')); display_notification(_("A new item has been added.")); $_POST['stock_id'] = $_POST['NewStockID'] = @@ -334,6 +336,7 @@ else $_POST['assembly_account'] = $myrow['assembly_account']; $_POST['dimension_id'] = $myrow['dimension_id']; $_POST['dimension2_id'] = $myrow['dimension2_id']; + $_POST['no_sale'] = $myrow['no_sale']; $_POST['del_image'] = 0; $_POST['inactive'] = $myrow["inactive"]; label_row(_("Item Code:"),$_POST['NewStockID']); @@ -361,6 +364,7 @@ if ($new_item && (list_updated('category_id') || !isset($_POST['units']))) { $_POST['assembly_account'] = $category_record["dflt_assembly_act"]; $_POST['dimension_id'] = $category_record["dflt_dim1"]; $_POST['dimension2_id'] = $category_record["dflt_dim2"]; + $_POST['no_sale'] = $category_record["dflt_no_sale"]; } $fresh_item = !isset($_POST['NewStockID']) || $new_item || check_usage($_POST['stock_id'],false); @@ -410,7 +414,7 @@ if (is_manufactured($_POST['mb_flag'])) else hidden('assembly_account', $_POST['assembly_account']); -table_section_title(_("Picture")); +table_section_title(_("Other")); // Add image upload for New Item - by Joe label_row(_("Image File (.jpg)") . ":", ""); @@ -435,6 +439,8 @@ label_row(" ", $stock_img_link); if ($check_remove_image) check_row(_("Delete Image:"), 'del_image', $_POST['del_image']); +check_row(_("Exclude from sales:"), 'no_sale', $_POST['no_sale']); + record_status_list_row(_("Item status:"), 'inactive'); end_outer_table(1); div_end(); diff --git a/sql/alter2.2.php b/sql/alter2.2.php index 7a4cf751..63b479f4 100644 --- a/sql/alter2.2.php +++ b/sql/alter2.2.php @@ -42,8 +42,8 @@ class fa2_2 { if ($info[1]) $sql .= " WHERE {$info[1]}=$typeno"; $result = db_query($sql); - if(db_num_rows($result)) { - while($row = db_fetch($result)) { + if (db_num_rows($result)) { + while ($row = db_fetch($result)) { $res2 = db_query("INSERT INTO {$pref}refs VALUES(" . $row['id'].",".$typeno.",'".$row['ref']."')"); if (!$res2) { @@ -54,14 +54,33 @@ class fa2_2 { } } } -/* FIX - // add audit_trail data for all transactions - $datatbl = array ("gl_trans", "purch_orders", "sales_orders", - "workorders"); - $sql = "INSERT INTO ".$pref."audit_trail" - ." (type, trans_no, user, fiscal_year, gl_date) VALUES (" - . "$type,$trans_no,$user,$year,$date)"; -*/ +/* FIX // restore/init audit_trail data + $datatbl = array ( + "gl_trans"=> array("type", "type_no","tran_date"), + "purch_orders" => array("order_no", "'18'", "ord_date"), + "sales_orders" => array("order_no", "'30'", "ord_date"), + "workorders" => array("id", "'26'", "date_") ); + foreach ( $datatbl as $tblname => $tbl) { + $sql = "SELECT {$tbl[0]} as type, {$tbl[1]} as trans, {$tbl[2]} as dat" + . " FROM {$pref}{$tblname}"; + $result = db_query($sql); + if (db_num_rows($result)) { + $user = ; + $year = ; + while ($row = db_fetch($result)) { + $sql2 = "INSERT INTO ".$pref."audit_trail" + ." (type, trans_no, user, fiscal_year, gl_date, gl_seq) VALUES (" + . "{$row['type']},{$row['trans']},$user,$year,{$row['dat']},0)"; + $res2 = db_query($sql2); + if (!$res2) { + display_error(_("Cannot init audit_trail data") + .':
'. db_error_msg($db)); + return false; + } + } + } + } +*/ return true; } // @@ -79,6 +98,7 @@ class fa2_2 { if (check_table($pref, 'stock_category', 'dflt_dim2')) return false; if (check_table($pref, 'users', 'sticky_doc_date')) return false; if (check_table($pref, 'audit_trail')) return false; + if (check_table($pref, 'stock_master','no_sale')) return false; return true; } }; diff --git a/sql/alter2.2.sql b/sql/alter2.2.sql index b70e3a08..719b7d2f 100644 --- a/sql/alter2.2.sql +++ b/sql/alter2.2.sql @@ -28,6 +28,7 @@ ALTER TABLE `0_stock_category` ADD COLUMN `dflt_adjustment_act` varchar(11) NOT ALTER TABLE `0_stock_category` ADD COLUMN `dflt_assembly_act` varchar(11) NOT NULL default ''; ALTER TABLE `0_stock_category` ADD COLUMN `dflt_dim1` int(11) default NULL; ALTER TABLE `0_stock_category` ADD COLUMN `dflt_dim2` int(11) default NULL; +ALTER TABLE `0_stock_category` ADD COLUMN `dflt_no_sale` tinyint(1) NOT NULL default '0'; ALTER TABLE `0_users` ADD COLUMN `sticky_doc_date` TINYINT(1) DEFAULT '0'; @@ -66,3 +67,4 @@ CREATE TABLE `0_audit_trail` ( KEY (`fiscal_year`, `gl_seq`) ) TYPE=InnoDB ; +ALTER TABLE `0_stock_master` ADD COLUMN `no_sale` tinyint(1) NOT NULL default '0'; -- 2.30.2