From 6e0c436eff1b7664473ea0dd1264b78f096a7808 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Wed, 26 Jan 2011 12:59:02 +0000 Subject: [PATCH] Reorganized work order costing to make corect registration of costs in journal. --- includes/sysnames.inc | 1 + includes/types.inc | 4 +- includes/ui/ui_view.inc | 6 +- .../includes/db/work_order_costing_db.inc | 188 ++++++++++++++++++ manufacturing/includes/db/work_orders_db.inc | 108 +--------- .../includes/db/work_orders_quick_db.inc | 51 ++--- manufacturing/includes/manufacturing_db.inc | 1 + manufacturing/includes/manufacturing_ui.inc | 4 +- manufacturing/view/wo_costs_view.php | 73 +++++++ manufacturing/work_order_costs.php | 36 ++-- sql/alter2.4.php | 41 +++- sql/alter2.4.sql | 20 ++ 12 files changed, 356 insertions(+), 177 deletions(-) create mode 100644 manufacturing/includes/db/work_order_costing_db.inc create mode 100644 manufacturing/view/wo_costs_view.php diff --git a/includes/sysnames.inc b/includes/sysnames.inc index 67007577..10f05518 100644 --- a/includes/sysnames.inc +++ b/includes/sysnames.inc @@ -110,6 +110,7 @@ $wo_types_array = array ( $wo_cost_types = array( WO_LABOUR => _("Labour Cost"), WO_OVERHEAD => _("Overhead Cost"), + WO_MATERIALS => _("Materials") ); //---------------------------------------------------------------------------------- diff --git a/includes/types.inc b/includes/types.inc index 9a437f1b..a40f4210 100644 --- a/includes/types.inc +++ b/includes/types.inc @@ -123,9 +123,6 @@ function payment_person_name($type, $person_id, $full=true) { case PT_QUICKENTRY : $qe = get_quick_entry($person_id); return ($full ? $payment_person_types[$type] . " ":"") . $qe["description"]; - case PT_WORKORDER : - global $wo_cost_types; - return $wo_cost_types[$person_id]; case PT_CUSTOMER : return ($full ?$payment_person_types[$type] . " ":"") . get_customer_name($person_id); case PT_SUPPLIER : @@ -171,6 +168,7 @@ define('WO_ADVANCED', 2); define('WO_LABOUR', 0); define('WO_OVERHEAD', 1); +define('WO_MATERIALS', 2); //---------------------------------------------------------------------------------- // GL account classes diff --git a/includes/ui/ui_view.inc b/includes/ui/ui_view.inc index c0e9e8c9..f9ae9fc3 100644 --- a/includes/ui/ui_view.inc +++ b/includes/ui/ui_view.inc @@ -51,10 +51,10 @@ function get_gl_view_str($type, $trans_no, $label="", $force=false, $class='', $ $label = _("GL"); $icon = ICON_GL; } + $url = ($type == ST_WORKORDER && !work_order_is_closed($trans_no)) ? "manufacturing/view/wo_costs_view.php?trans_no=$trans_no" + : "gl/view/gl_trans_view.php?type_id=$type&trans_no=$trans_no"; - return viewer_link($label, - "gl/view/gl_trans_view.php?type_id=$type&trans_no=$trans_no", - $class, $id, $icon); + return viewer_link($label, $url, $class, $id, $icon); } //-------------------------------------------------------------------------------------- diff --git a/manufacturing/includes/db/work_order_costing_db.inc b/manufacturing/includes/db/work_order_costing_db.inc new file mode 100644 index 00000000..bf4493c3 --- /dev/null +++ b/manufacturing/includes/db/work_order_costing_db.inc @@ -0,0 +1,188 @@ +. +***********************************************************************/ + +function add_wo_costing($workorder_id, $cost_type, $trans_type, $trans_no, $factor=1) +{ + $sql = "INSERT INTO ".TB_PREF."wo_costing (workorder_id, cost_type, trans_type, trans_no, factor) + VALUES (".db_escape($workorder_id)."," + .db_escape($cost_type).",".db_escape($trans_type)."," + .db_escape($trans_no).",".db_escape($factor).")"; + + db_query($sql, "could not add work order costing"); + +} + +function get_wo_costing($workorder_id) +{ + $sql="SELECT * FROM ".TB_PREF."wo_costing WHERE workorder_id=".db_escape($workorder_id); + + return db_query($sql, "could not get work order costing"); +} + +function delete_wo_costing($trans_type, $trans_no) +{ + $sql="DELETE FROM ".TB_PREF."wo_costing WHERE trans_type=".db_escape($trans_type) + ." AND trans_no=".db_escape($trans_no); + + db_query($sql, "could not delete work order costing"); +} + +//-------------------------------------------------------------------------------------- + +function add_material_cost($stock_id, $qty, $date_) +{ + $m_cost = 0; + $result = get_bom($stock_id); + while ($bom_item = db_fetch($result)) + { + $standard_cost = get_standard_cost($bom_item['component']); + $m_cost += ($bom_item['quantity'] * $standard_cost); + } + //$dec = user_price_dec(); + //price_decimal_format($m_cost, $dec); + $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id = " + .db_escape($stock_id); + $result = db_query($sql); + $myrow = db_fetch($result); + $material_cost = $myrow['material_cost']; + //$qoh = get_qoh_on_date($stock_id, null, $date_); + $qoh = get_qoh_on_date($stock_id); + if ($qoh < 0) + $qoh = 0; + if ($qoh + $qty != 0) + $material_cost = ($qoh * $material_cost + $qty * $m_cost) / ($qoh + $qty); + //$material_cost = round2($material_cost, $dec); + $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=$material_cost + WHERE stock_id=".db_escape($stock_id); + db_query($sql,"The cost details for the inventory item could not be updated"); +} + +function add_overhead_cost($stock_id, $qty, $date_, $costs) +{ + //$dec = user_price_dec(); + //price_decimal_format($costs, $dec); + if ($qty != 0) + $costs /= $qty; + $sql = "SELECT overhead_cost FROM ".TB_PREF."stock_master WHERE stock_id = " + .db_escape($stock_id); + $result = db_query($sql); + $myrow = db_fetch($result); + $overhead_cost = $myrow['overhead_cost']; + //$qoh = get_qoh_on_date($stock_id, null, $date_); + $qoh = get_qoh_on_date($stock_id); + if ($qoh < 0) + $qoh = 0; + if ($qoh + $qty != 0) + $overhead_cost = ($qoh * $overhead_cost + $qty * $costs) / ($qoh + $qty); + //$overhead_cost = round2($overhead_cost, $dec); + $sql = "UPDATE ".TB_PREF."stock_master SET overhead_cost=".db_escape($overhead_cost)." + WHERE stock_id=".db_escape($stock_id); + db_query($sql,"The cost details for the inventory item could not be updated"); +} + +function add_labour_cost($stock_id, $qty, $date_, $costs) +{ + //$dec = user_price_dec(); + //price_decimal_format($costs, $dec); + if ($qty != 0) + $costs /= $qty; + $sql = "SELECT labour_cost FROM ".TB_PREF."stock_master WHERE stock_id = " + .db_escape($stock_id); + $result = db_query($sql); + $myrow = db_fetch($result); + $labour_cost = $myrow['labour_cost']; + //$qoh = get_qoh_on_date($stock_id, null, $date_); + $qoh = get_qoh_on_date($stock_id); + if ($qoh < 0) + $qoh = 0; + if ($qoh + $qty != 0) + $labour_cost = ($qoh * $labour_cost + $qty * $costs) / ($qoh + $qty); + //$labour_cost = round2($labour_cost, $dec); + $sql = "UPDATE ".TB_PREF."stock_master SET labour_cost=".db_escape($labour_cost)." + WHERE stock_id=".db_escape($stock_id); + db_query($sql,"The cost details for the inventory item could not be updated"); +} + +function add_issue_cost($stock_id, $qty, $date_, $costs) +{ + if ($qty != 0) + $costs /= $qty; + $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id = " + .db_escape($stock_id); + $result = db_query($sql); + $myrow = db_fetch($result); + $material_cost = $myrow['material_cost']; + //$dec = user_price_dec(); + //price_decimal_format($material_cost, $dec); + //$qoh = get_qoh_on_date($stock_id, null, $date_); + $qoh = get_qoh_on_date($stock_id); + if ($qoh < 0) + $qoh = 0; + if ($qoh + $qty != 0) + $material_cost = ($qty * $costs) / ($qoh + $qty); + //$material_cost = round2($material_cost, $dec); + $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=material_cost+" + .db_escape($material_cost) + ." WHERE stock_id=".db_escape($stock_id); + db_query($sql,"The cost details for the inventory item could not be updated"); +} + +/* + Create journal entry for WO related costs. +*/ +function add_wo_costs_journal($wo_id, $amount, $cost_type, $cr_acc, $db_acc, $date, $dim1=0, $dim2=0, $memo = null, $ref= null) +{ +//-------- this should be done by single call to write_journal_entries() using items_cart() +// + global $Refs, $wo_cost_types; + + begin_transaction(); + + $journal_id = get_next_trans_no(ST_JOURNAL); + if (!$ref) $ref = $Refs->get_next(ST_JOURNAL); + + add_gl_trans_std_cost(ST_JOURNAL, $journal_id, $date, $cr_acc, + 0, 0, $wo_cost_types[$cost_type], -$amount); + $is_bank_to = is_bank_account($cr_acc); + if ($is_bank_to) + { + add_bank_trans(ST_JOURNAL, $journal_id, $is_bank_to, "", + $date, -$amount, PT_WORKORDER, $wo_id, get_company_currency(), + "Cannot insert a destination bank transaction"); + } + + add_gl_trans_std_cost(ST_JOURNAL, $journal_id, $date, $db_acc, + $dim1, $dim2, $wo_cost_types[$cost_type], $amount); + + $Refs->save(ST_JOURNAL, $journal_id, $ref); + + add_wo_costing($wo_id, $cost_type, ST_JOURNAL, $journal_id); + + add_comments(ST_JOURNAL, $journal_id, $date, $memo); + add_audit_trail(ST_JOURNAL, $journal_id, $date); + + commit_transaction(); +} + +function void_wo_costing($wo_id) +{ + $res = get_wo_costing($wo_id); + + while($row = db_fetch($res)) + { + // void any related gl trans + void_gl_trans($row['trans_type'], $row['trans_no'], true); + } + + $sql = "DELETE FROM ".TB_PREF."wo_costing WHERE workorder_id=".db_escape($wo_id); + db_query($sql, "could not delete work order costing"); +} diff --git a/manufacturing/includes/db/work_orders_db.inc b/manufacturing/includes/db/work_orders_db.inc index e39d334c..e63aae81 100644 --- a/manufacturing/includes/db/work_orders_db.inc +++ b/manufacturing/includes/db/work_orders_db.inc @@ -9,106 +9,6 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the License here . ***********************************************************************/ -//-------------------------------------------------------------------------------------- - -function add_material_cost($stock_id, $qty, $date_) -{ - $m_cost = 0; - $result = get_bom($stock_id); - while ($bom_item = db_fetch($result)) - { - $standard_cost = get_standard_cost($bom_item['component']); - $m_cost += ($bom_item['quantity'] * $standard_cost); - } - //$dec = user_price_dec(); - //price_decimal_format($m_cost, $dec); - $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id = " - .db_escape($stock_id); - $result = db_query($sql); - $myrow = db_fetch($result); - $material_cost = $myrow['material_cost']; - //$qoh = get_qoh_on_date($stock_id, null, $date_); - $qoh = get_qoh_on_date($stock_id); - if ($qoh < 0) - $qoh = 0; - if ($qoh + $qty != 0) - $material_cost = ($qoh * $material_cost + $qty * $m_cost) / ($qoh + $qty); - //$material_cost = round2($material_cost, $dec); - $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=$material_cost - WHERE stock_id=".db_escape($stock_id); - db_query($sql,"The cost details for the inventory item could not be updated"); -} - -function add_overhead_cost($stock_id, $qty, $date_, $costs) -{ - //$dec = user_price_dec(); - //price_decimal_format($costs, $dec); - if ($qty != 0) - $costs /= $qty; - $sql = "SELECT overhead_cost FROM ".TB_PREF."stock_master WHERE stock_id = " - .db_escape($stock_id); - $result = db_query($sql); - $myrow = db_fetch($result); - $overhead_cost = $myrow['overhead_cost']; - //$qoh = get_qoh_on_date($stock_id, null, $date_); - $qoh = get_qoh_on_date($stock_id); - if ($qoh < 0) - $qoh = 0; - if ($qoh + $qty != 0) - $overhead_cost = ($qoh * $overhead_cost + $qty * $costs) / ($qoh + $qty); - //$overhead_cost = round2($overhead_cost, $dec); - $sql = "UPDATE ".TB_PREF."stock_master SET overhead_cost=".db_escape($overhead_cost)." - WHERE stock_id=".db_escape($stock_id); - db_query($sql,"The cost details for the inventory item could not be updated"); -} - -function add_labour_cost($stock_id, $qty, $date_, $costs) -{ - //$dec = user_price_dec(); - //price_decimal_format($costs, $dec); - if ($qty != 0) - $costs /= $qty; - $sql = "SELECT labour_cost FROM ".TB_PREF."stock_master WHERE stock_id = " - .db_escape($stock_id); - $result = db_query($sql); - $myrow = db_fetch($result); - $labour_cost = $myrow['labour_cost']; - //$qoh = get_qoh_on_date($stock_id, null, $date_); - $qoh = get_qoh_on_date($stock_id); - if ($qoh < 0) - $qoh = 0; - if ($qoh + $qty != 0) - $labour_cost = ($qoh * $labour_cost + $qty * $costs) / ($qoh + $qty); - //$labour_cost = round2($labour_cost, $dec); - $sql = "UPDATE ".TB_PREF."stock_master SET labour_cost=".db_escape($labour_cost)." - WHERE stock_id=".db_escape($stock_id); - db_query($sql,"The cost details for the inventory item could not be updated"); -} - -function add_issue_cost($stock_id, $qty, $date_, $costs) -{ - if ($qty != 0) - $costs /= $qty; - $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id = " - .db_escape($stock_id); - $result = db_query($sql); - $myrow = db_fetch($result); - $material_cost = $myrow['material_cost']; - //$dec = user_price_dec(); - //price_decimal_format($material_cost, $dec); - //$qoh = get_qoh_on_date($stock_id, null, $date_); - $qoh = get_qoh_on_date($stock_id); - if ($qoh < 0) - $qoh = 0; - if ($qoh + $qty != 0) - $material_cost = ($qty * $costs) / ($qoh + $qty); - //$material_cost = round2($material_cost, $dec); - $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=material_cost+" - .db_escape($material_cost) - ." WHERE stock_id=".db_escape($stock_id); - db_query($sql,"The cost details for the inventory item could not be updated"); -} - function add_work_order($wo_ref, $loc_code, $units_reqd, $stock_id, $type, $date_, $required_by, $memo_, $costs, $cr_acc, $labour, $cr_lab_acc) { @@ -273,7 +173,7 @@ function release_work_order($woid, $releaseDate, $memo_) create_wo_requirements($woid, $stock_id); add_comments(ST_WORKORDER, $woid, $releaseDate, $memo_); - add_audit_trail(ST_WORKORDER, $woid, $releaseDate,_("Released.")); + add_audit_trail(ST_WORKORDER, $woid, $myrow['date_'], _("Released.")); commit_transaction(); } @@ -334,8 +234,7 @@ function void_work_order($woid) // void all related stock moves void_stock_move(ST_WORKORDER, $woid); - // void any related gl trans - void_gl_trans(ST_WORKORDER, $woid, true); + void_wo_costing($woid); // clear the requirements units received void_wo_requirements($woid); @@ -390,8 +289,7 @@ function void_work_order($woid) // void all related stock moves void_stock_move(ST_WORKORDER, $woid); - // void any related gl trans - void_gl_trans(ST_WORKORDER, $woid, true); + void_wo_costing($wo); // clear the requirements units received void_wo_requirements($woid); diff --git a/manufacturing/includes/db/work_orders_quick_db.inc b/manufacturing/includes/db/work_orders_quick_db.inc index 1534e277..5654caeb 100644 --- a/manufacturing/includes/db/work_orders_quick_db.inc +++ b/manufacturing/includes/db/work_orders_quick_db.inc @@ -148,40 +148,21 @@ function work_order_quick_costs($woid, $stock_id, $units_reqd, $date_, $advanced add_labour_cost($stock_id, $units_reqd, $date_, $lcost * $units_reqd / $wo['units_reqd']); $ocost = get_gl_wo_cost($woid, WO_OVERHEAD); add_overhead_cost($stock_id, $units_reqd, $date_, $ocost * $units_reqd / $wo['units_reqd']); - } - // credit additional costs - $item_accounts = get_stock_gl_code($stock_id); - if ($costs != 0.0) - { - add_gl_trans_std_cost(ST_WORKORDER, $woid, $date_, $cr_acc, - 0, 0, $wo_cost_types[WO_OVERHEAD], -$costs, PT_WORKORDER, WO_OVERHEAD); - $is_bank_to = is_bank_account($cr_acc); - if ($is_bank_to) - { - add_bank_trans(ST_WORKORDER, $woid, $is_bank_to, "", - $date_, -$costs, PT_WORKORDER, WO_OVERHEAD, get_company_currency(), - "Cannot insert a destination bank transaction"); - } - - add_gl_trans_std_cost(ST_WORKORDER, $woid, $date_, $item_accounts["assembly_account"], - $item_accounts["dimension_id"], $item_accounts["dimension2_id"], $wo_cost_types[WO_OVERHEAD], $costs, - PT_WORKORDER, WO_OVERHEAD); - } - if ($labour != 0.0) - { - add_gl_trans_std_cost(ST_WORKORDER, $woid, $date_, $cr_lab_acc, - 0, 0, $wo_cost_types[WO_LABOUR], -$labour, PT_WORKORDER, WO_LABOUR); - $is_bank_to = is_bank_account($cr_lab_acc); - if ($is_bank_to) - { - add_bank_trans(ST_WORKORDER, $woid, $is_bank_to, "", - $date_, -$labour, PT_WORKORDER, WO_LABOUR, get_company_currency(), - "Cannot insert a destination bank transaction"); - } - - add_gl_trans_std_cost(ST_WORKORDER, $woid, $date_, $item_accounts["assembly_account"], - $item_accounts["dimension_id"], $item_accounts["dimension2_id"], $wo_cost_types[WO_LABOUR], $labour, - PT_WORKORDER, WO_LABOUR); + + } else { // only for quick + // credit additional costs + $item_accounts = get_stock_gl_code($stock_id); + + if ($costs != 0.0) + { + add_wo_costs_journal($woid, $costs, WO_OVERHEAD, $cr_acc, $item_accounts["assembly_account"], + $date_, $item_accounts["dimension_id"], $item_accounts["dimension2_id"]); + } + if ($labour != 0.0) // only for quick + { + add_wo_costs_journal($woid, $labour, WO_LABOUR, $cr_lab_acc, $item_accounts["assembly_account"], + $date_, $item_accounts["dimension_id"], $item_accounts["dimension2_id"]); + } } // debit total components $total_cost $stockitem = get_item($stock_id); @@ -189,7 +170,7 @@ function work_order_quick_costs($woid, $stock_id, $units_reqd, $date_, $advanced if ($advanced) $memo = $date_.": ".$memo; add_gl_trans_std_cost(ST_WORKORDER, $woid, $date_, $item_accounts["inventory_account"], - 0, 0, $memo, -$total_cost); + 0, 0, $memo, -$total_cost); } //-------------------------------------------------------------------------------------- diff --git a/manufacturing/includes/manufacturing_db.inc b/manufacturing/includes/manufacturing_db.inc index 3a599161..2c880aef 100644 --- a/manufacturing/includes/manufacturing_db.inc +++ b/manufacturing/includes/manufacturing_db.inc @@ -11,6 +11,7 @@ ***********************************************************************/ include_once($path_to_root . "/manufacturing/includes/db/work_centres_db.inc"); include_once($path_to_root . "/manufacturing/includes/db/work_orders_db.inc"); +include_once($path_to_root . "/manufacturing/includes/db/work_order_costing_db.inc"); include_once($path_to_root . "/manufacturing/includes/db/work_orders_quick_db.inc"); include_once($path_to_root . "/manufacturing/includes/db/work_order_issues_db.inc"); include_once($path_to_root . "/manufacturing/includes/db/work_order_produce_items_db.inc"); diff --git a/manufacturing/includes/manufacturing_ui.inc b/manufacturing/includes/manufacturing_ui.inc index f2cde17f..7e63f77a 100644 --- a/manufacturing/includes/manufacturing_ui.inc +++ b/manufacturing/includes/manufacturing_ui.inc @@ -244,8 +244,8 @@ function display_wo_payments($woid) alt_table_row_color($k); - label_cell(get_gl_view_str(ST_WORKORDER, $myrow["type_no"], $myrow["type_no"])); - label_cell($wo_cost_types[$myrow['person_id']]); + label_cell(get_gl_view_str( $myrow["type"], $myrow["type_no"], $myrow["type_no"])); + label_cell($wo_cost_types[$myrow['cost_type']]); $date = sql2date($myrow["tran_date"]); label_cell($date); amount_cell(-($myrow['amount'])); diff --git a/manufacturing/view/wo_costs_view.php b/manufacturing/view/wo_costs_view.php new file mode 100644 index 00000000..12ed5fe3 --- /dev/null +++ b/manufacturing/view/wo_costs_view.php @@ -0,0 +1,73 @@ +. +***********************************************************************/ +$page_security = 'SA_MANUFTRANSVIEW'; +$path_to_root = "../.."; + +include_once($path_to_root . "/includes/session.inc"); + +$js = ""; +if ($use_popup_windows) + $js .= get_js_open_window(900, 500); +page(_($help_context = "View Work Order Costs"), true, false, "", $js); + +include_once($path_to_root . "/includes/date_functions.inc"); +include_once($path_to_root . "/includes/manufacturing.inc"); +include_once($path_to_root . "/includes/data_checks.inc"); + +include_once($path_to_root . "/manufacturing/includes/manufacturing_db.inc"); +include_once($path_to_root . "/manufacturing/includes/manufacturing_ui.inc"); + +//------------------------------------------------------------------------------------------------- + +if ($_GET['trans_no'] != "") +{ + $wo_id = $_GET['trans_no']; +} + +//------------------------------------------------------------------------------------------------- + +function display_wo_costs($prod_id) +{ + global $wo_cost_types; + + $costs = get_gl_wo_cost_trans($prod_id); + + br(1); + start_table(TABLESTYLE); + $th = array(_("Date"), _("Type"), _("Amount"), _("Memo"), ''); + table_header($th); + while($myrow = db_fetch($costs)) { + start_row(); + label_cell(sql2date($myrow["tran_date"])); + label_cell($wo_cost_types[$myrow["cost_type"]]); + amount_cell(-$myrow['amount']); + label_cell($myrow['memo_']); + label_cell(get_trans_view_str($myrow['trans_type'],$myrow["trans_no"])); + end_row(); + } + end_table(1); +} + +//------------------------------------------------------------------------------------------------- + +display_heading(sprintf(_("Production Costs for Work Order # %d"), $wo_id)); + +display_wo_costs($wo_id); + +//------------------------------------------------------------------------------------------------- + +br(2); + +end_page(true, false, false, ST_WORKORDER, $wo_id); + +?> + diff --git a/manufacturing/work_order_costs.php b/manufacturing/work_order_costs.php index 50e7f7fe..4054fcbd 100644 --- a/manufacturing/work_order_costs.php +++ b/manufacturing/work_order_costs.php @@ -44,8 +44,6 @@ if (isset($_GET['AddedID'])) display_note(get_trans_view_str($stype, $id, _("View this Work Order"))); - display_note(get_gl_view_str($stype, $id, _("View the GL Journal Entries for this Work Order")), 1); - hyperlink_params("work_order_costs.php", _("Enter another additional cost."), "trans_no=$id"); hyperlink_no_params("search_work_orders.php", _("Select another &Work Order to Process")); @@ -67,9 +65,8 @@ if (strlen($wo_details[0]) == 0) //-------------------------------------------------------------------------------------------------- -function can_process() +function can_process($wo_details) { - global $wo_details; if (!check_num('costs', 0)) { @@ -86,7 +83,7 @@ function can_process() } elseif (!is_date_in_fiscalyear($_POST['date_'])) { - display_error(_("The entered date is not in fiscal year.")); + display_error(_("The entered date is out of fiscal year or is closed for further data entry.")); set_focus('date_'); return false; } @@ -102,24 +99,14 @@ function can_process() //-------------------------------------------------------------------------------------------------- -if (isset($_POST['process']) && can_process() == true) +if (isset($_POST['process']) && can_process($wo_details) == true) { $date = $_POST['date_']; - begin_transaction(); - add_gl_trans_std_cost(ST_WORKORDER, $_POST['selected_id'], $_POST['date_'], $_POST['cr_acc'], - 0, 0, $date.": ".$wo_cost_types[$_POST['PaymentType']], -input_num('costs'), PT_WORKORDER, $_POST['PaymentType']); - $is_bank_to = is_bank_account($_POST['cr_acc']); - if ($is_bank_to) - { - add_bank_trans(ST_WORKORDER, $_POST['selected_id'], $is_bank_to, "", - $_POST['date_'], -input_num('costs'), PT_WORKORDER, $_POST['PaymentType'], get_company_currency(), - "Cannot insert a destination bank transaction"); - } + $memo = $_POST['memo']; + $ref = $_POST['ref']; - add_gl_trans_std_cost(ST_WORKORDER, $_POST['selected_id'], $_POST['date_'], $_POST['db_acc'], - $_POST['dim1'], $_POST['dim2'], $date.": ".$wo_cost_types[$_POST['PaymentType']], input_num('costs'), PT_WORKORDER, - $_POST['PaymentType']); - commit_transaction(); + add_wo_costs_journal($_POST['selected_id'], input_num('costs'), $_POST['PaymentType'], + $_POST['cr_acc'], $_POST['db_acc'], $date, $_POST['dim1'], $_POST['dim2'], $memo, $ref); meta_forward($_SERVER['PHP_SELF'], "AddedID=".$_POST['selected_id']); } @@ -130,6 +117,9 @@ display_wo_details($_POST['selected_id']); //------------------------------------------------------------------------------------- +if (!isset($_POST['ref'])) + $_POST['ref'] = $Refs->get_next(ST_JOURNAL); + start_form(); hidden('selected_id', $_POST['selected_id']); @@ -139,9 +129,11 @@ start_table(TABLESTYLE2); br(); -yesno_list_row(_("Type:"), 'PaymentType', null, $wo_cost_types[WO_OVERHEAD], $wo_cost_types[WO_LABOUR]); date_row(_("Date:"), 'date_'); +ref_row(_("Reference:"), 'ref', ''); + +yesno_list_row(_("Type:"), 'PaymentType', null, $wo_cost_types[WO_OVERHEAD], $wo_cost_types[WO_LABOUR]); $item_accounts = get_stock_gl_code($wo_details['stock_id']); $_POST['db_acc'] = $item_accounts['assembly_account']; @@ -151,7 +143,7 @@ $_POST['cr_acc'] = $r[0]; amount_row(_("Additional Costs:"), 'costs'); gl_all_accounts_list_row(_("Debit Account"), 'db_acc', null); gl_all_accounts_list_row(_("Credit Account"), 'cr_acc', null); - +textarea_row(_("Memo:"), 'memo', null, 40, 5); end_table(1); hidden('dim1', $item_accounts["dimension_id"]); hidden('dim2', $item_accounts["dimension2_id"]); diff --git a/sql/alter2.4.php b/sql/alter2.4.php index 8a3f0861..2ef35175 100644 --- a/sql/alter2.4.php +++ b/sql/alter2.4.php @@ -29,9 +29,8 @@ class fa2_4 { if (get_company_pref('grn_clearing_act') === null) { // available form 2.3.1, can be not defined on pre-2.4 installations set_company_pref('grn_clearing_act', 'glsetup.purchase', 'varchar', 15, 0); - refresh_sys_prefs(); } - + if ($this->update_workorders()) // return update_company_prefs(array('version_id'=>$db_version), $pref); return true; } @@ -45,17 +44,45 @@ class fa2_4 { // // Test if patch was applied before. // - function installed($pref) { - - $n = 1; // number of patches to be installed + function installed($pref) + { + $n = 2; // number of patches to be installed $patchcnt = 0; if (!check_table($pref, 'suppliers', 'tax_algorithm')) $patchcnt++; + if (!check_table($pref, 'wo_costing')) $patchcnt++; return $n == $patchcnt ? true : ($patchcnt ? ($patchcnt.'/'. $n) : 0); } + function update_workorders() + { + global $db; + + $sql = "SELECT DISTINCT type, type_no, tran_date, person_id FROM ".TB_PREF."gl_trans WHERE `type`=".ST_WORKORDER + ." AND person_type_id=1"; + $res = db_query($sql); + if (!$res) + { + display_error("Cannot update work orders costs" + .':
'. db_error_msg($db)); + return false; + } + while ($row = db_fetch($res)) + { + $journal_id = get_next_trans_no(ST_JOURNAL); + + $sql1 = "UPDATE ".TB_PREF."gl_trans SET `type`=".ST_JOURNAL.", type_no={$journal_id}, + person_type_id=NULL, person_id=0 + WHERE `type`=".ST_WORKORDER." AND type_no={$row['type_no']} AND tran_date='{$row['tran_date']}' + AND person_id='{$row['person_id']}'"; + if (!db_query($sql1)) return false; + + $sql2 = "INSERT INTO ".TB_PREF."wo_costing (workorder_id, cost_type, trans_no) + VALUES ({$row['type_no']}, {$row['person_id']}, {$journal_id})"; + if (!db_query($sql2)) return false; + } + return true; + } } $install = new fa2_4; - -?> \ No newline at end of file diff --git a/sql/alter2.4.sql b/sql/alter2.4.sql index e35ed96c..c9900f7c 100644 --- a/sql/alter2.4.sql +++ b/sql/alter2.4.sql @@ -1,4 +1,24 @@ ALTER TABLE `0_suppliers` ADD COLUMN `tax_algorithm` tinyint(1) NOT NULL default '1' AFTER `tax_included`; ALTER TABLE `0_supp_trans` ADD COLUMN `tax_algorithm` tinyint(1) NULL default '1' AFTER `tax_included`; INSERT INTO `0_sys_prefs` VALUES('tax_algorithm','glsetup.customer', 'tinyint', 1, '1'); +INSERT INTO `0_sys_prefs` VALUES('gl_closing_date','setup.closing_date', 'date', 8, ''); +# Fix eventual invalid date/year in audit records +UPDATE `0_audit_trail` audit + LEFT JOIN `0_gl_trans` gl ON gl.`type`=audit.`type` AND gl.type_no=audit.trans_no + LEFT JOIN `0_fiscal_year` year ON year.begin<=gl.tran_date AND year.end>=gl.tran_date + SET audit.gl_date=gl.tran_date, audit.fiscal_year=year.id + WHERE NOT ISNULL(gl.`type`); + +DROP TABLE IF EXISTS `0_wo_costing`; + +CREATE TABLE `0_wo_costing` ( + `id` int(11) NOT NULL auto_increment, + `workorder_id` int(11) NOT NULL default '0', + `cost_type` tinyint(1) NOT NULL default '0', + `trans_type` int(11) NOT NULL default '0', + `trans_no` int(11) NOT NULL default '0', + `factor` double NOT NULL default '1', + PRIMARY KEY (`id`) +) TYPE=InnoDB; + -- 2.30.2