From c33fed6d8d791f7901f418345daafa9cda03c6a9 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Wed, 18 Feb 2015 10:06:05 +0100 Subject: [PATCH] Added memo, usage fields to Quick Entries. --- gl/includes/db/gl_db_bank_accounts.inc | 20 ++--- gl/manage/gl_quick_entries.php | 32 ++++--- includes/ui/ui_view.inc | 120 ++++++++++++++++--------- sql/alter2.4.sql | 3 + sql/en_US-demo.sql | 20 +++-- sql/en_US-new.sql | 20 +++-- 6 files changed, 134 insertions(+), 81 deletions(-) diff --git a/gl/includes/db/gl_db_bank_accounts.inc b/gl/includes/db/gl_db_bank_accounts.inc index 6eebecc0..ef6793e2 100644 --- a/gl/includes/db/gl_db_bank_accounts.inc +++ b/gl/includes/db/gl_db_bank_accounts.inc @@ -115,22 +115,22 @@ function get_bank_charge_account($id) //--------------------------------------------------------------------------------------------- -function add_quick_entry($description, $type, $base_amount, $base_desc, $bal_type) +function add_quick_entry($description, $type, $base_amount, $base_desc, $bal_type, $usage) { - $sql = "INSERT INTO ".TB_PREF."quick_entries (description, type, base_amount, base_desc, bal_type) + $sql = "INSERT INTO ".TB_PREF."quick_entries (description, type, base_amount, base_desc, bal_type, `usage`) VALUES (".db_escape($description).", ".db_escape($type).", " - .db_escape($base_amount).", ".db_escape($base_desc).", ".db_escape($bal_type).")"; + .db_escape($base_amount).", ".db_escape($base_desc).", ".db_escape($bal_type).", ".db_escape($usage).")"; db_query($sql, "could not insert quick entry for $description"); } //--------------------------------------------------------------------------------------------- -function update_quick_entry($selected_id, $description, $type, $base_amount, $base_desc, $bal_type) +function update_quick_entry($selected_id, $description, $type, $base_amount, $base_desc, $bal_type, $usage) { $sql = "UPDATE ".TB_PREF."quick_entries SET description = ".db_escape($description).", type=".db_escape($type).", base_amount=".db_escape($base_amount) - .", base_desc=".db_escape($base_desc).", bal_type=".db_escape($bal_type)." + .", base_desc=".db_escape($base_desc).", bal_type=".db_escape($bal_type).", `usage`=".db_escape($usage)." WHERE id = ".db_escape($selected_id); db_query($sql, "could not update quick entry for $selected_id"); @@ -147,25 +147,25 @@ function delete_quick_entry($selected_id) //--------------------------------------------------------------------------------------------- -function add_quick_entry_line($qid, $action, $dest_id, $amount, $dim, $dim2) +function add_quick_entry_line($qid, $action, $dest_id, $amount, $dim, $dim2, $memo) { $sql = "INSERT INTO ".TB_PREF."quick_entry_lines - (qid, action, dest_id, amount, dimension_id, dimension2_id) + (qid, action, dest_id, amount, dimension_id, dimension2_id, memo) VALUES (".db_escape($qid).", ".db_escape($action).",".db_escape($dest_id).", - ".db_escape($amount).", ".db_escape($dim).", ".db_escape($dim2).")"; + ".db_escape($amount).", ".db_escape($dim).", ".db_escape($dim2).", ".db_escape($memo).")"; db_query($sql, "could not insert quick entry line for $qid"); } //--------------------------------------------------------------------------------------------- -function update_quick_entry_line($selected_id, $qid, $action, $dest_id, $amount, $dim, $dim2) +function update_quick_entry_line($selected_id, $qid, $action, $dest_id, $amount, $dim, $dim2, $memo) { $sql = "UPDATE ".TB_PREF."quick_entry_lines SET qid = ".db_escape($qid) .", action=".db_escape($action).", dest_id=".db_escape($dest_id).", amount=".db_escape($amount) - .", dimension_id=".db_escape($dim).", dimension2_id=".db_escape($dim2)." + .", dimension_id=".db_escape($dim).", dimension2_id=".db_escape($dim2).", memo=".db_escape($memo)." WHERE id = ".db_escape($selected_id); db_query($sql, "could not update quick entry line for $selected_id"); diff --git a/gl/manage/gl_quick_entries.php b/gl/manage/gl_quick_entries.php index d314b690..e92ca903 100644 --- a/gl/manage/gl_quick_entries.php +++ b/gl/manage/gl_quick_entries.php @@ -102,13 +102,13 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') if ($selected_id != -1) { update_quick_entry($selected_id, $_POST['description'], $_POST['type'], - input_num('base_amount'), $_POST['base_desc'], get_post('bal_type', 0)); + input_num('base_amount'), $_POST['base_desc'], get_post('bal_type', 0), $_POST['usage']); display_notification(_('Selected quick entry has been updated')); } else { add_quick_entry($_POST['description'], $_POST['type'], - input_num('base_amount'), $_POST['base_desc'], get_post('bal_type', 0)); + input_num('base_amount'), $_POST['base_desc'], get_post('bal_type', 0), $_POST['usage']); display_notification(_('New quick entry has been added')); } $Mode = 'RESET'; @@ -117,16 +117,20 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') if ($Mode2=='ADD_ITEM2' || $Mode2=='UPDATE_ITEM2') { - if ($selected_id2 != -1) + if (!get_post('dest_id')) { + display_error(_("You must select GL account.")); + set_focus('dest_id'); + } + elseif ($selected_id2 != -1) { update_quick_entry_line($selected_id2, $selected_id, $_POST['actn'], $_POST['dest_id'], input_num('amount', 0), - $_POST['dimension_id'], $_POST['dimension2_id']); + $_POST['dimension_id'], $_POST['dimension2_id'], $_POST['memo']); display_notification(_('Selected quick entry line has been updated')); } else { add_quick_entry_line($selected_id, $_POST['actn'], $_POST['dest_id'], input_num('amount', 0), - $_POST['dimension_id'], $_POST['dimension2_id']); + $_POST['dimension_id'], $_POST['dimension2_id'], $_POST['memo']); display_notification(_('New quick entry line has been added')); } $Mode2 = 'RESET2'; @@ -167,7 +171,7 @@ if ($Mode2 == 'BDel') if ($Mode == 'RESET') { $selected_id = -1; - $_POST['description'] = $_POST['type'] = ''; + $_POST['description'] = $_POST['type'] = $_POST['usage'] = ''; $_POST['base_desc']= _('Base Amount'); $_POST['base_amount'] = price_format(0); $_POST['bal_type'] = 0; @@ -183,7 +187,7 @@ if ($Mode2 == 'RESET2') $result = get_quick_entries(); start_form(); start_table(TABLESTYLE); -$th = array(_("Description"), _("Type"), "", ""); +$th = array(_("Description"), _("Type"), _("Usage"), "", ""); table_header($th); $k = 0; @@ -193,6 +197,7 @@ while ($myrow = db_fetch($result)) $type_text = $quick_entry_types[$myrow["type"]]; label_cell($myrow['description']); label_cell($type_text); + label_cell($myrow['usage']); edit_button_cell("Edit".$myrow["id"], _("Edit")); delete_button_cell("Delete".$myrow["id"], _("Delete")); end_row(); @@ -214,6 +219,7 @@ if ($selected_id != -1) $_POST['description'] = $myrow["description"]; $_POST['type'] = $myrow["type"]; $_POST['base_desc'] = $myrow["base_desc"]; + $_POST['usage'] = $myrow["usage"]; $_POST['bal_type'] = $myrow["bal_type"]; $_POST['base_amount'] = $myrow["bal_type"] ? $myrow["base_amount"] : price_format($myrow["base_amount"]); @@ -222,6 +228,7 @@ if ($selected_id != -1) } text_row_ex(_("Description").':', 'description', 50, 60); +text_row_ex(_("Usage").':', 'usage', 80, 120); quick_entry_types_list_row(_("Entry Type").':', 'type', null, true); @@ -258,11 +265,11 @@ if ($selected_id != -1) start_table(TABLESTYLE2); $dim = get_company_pref('use_dimension'); if ($dim == 2) - $th = array(_("Post"), _("Account/Tax Type"), _("Amount"), _("Dimension"), _("Dimension")." 2", "", ""); + $th = array(_("Post"), _("Account/Tax Type"), _("Amount"), _("Memo"), _("Dimension"), _("Dimension")." 2", "", ""); elseif ($dim == 1) - $th = array(_("Post"), _("Account/Tax Type"), _("Amount"), _("Dimension"), "", ""); + $th = array(_("Post"), _("Account/Tax Type"), _("Amount"), _("Memo"), _("Dimension"), "", ""); else - $th = array(_("Post"), _("Account/Tax Type"), _("Amount"), "", ""); + $th = array(_("Post"), _("Account/Tax Type"), _("Amount"), _("Memo"), "", ""); table_header($th); $k = 0; @@ -287,7 +294,8 @@ if ($selected_id != -1) label_cell(number_format2($myrow['amount'], user_exrate_dec()), "nowrap align=right "); else amount_cell($myrow['amount']); - } + label_cell($myrow['memo']); + } if ($dim >= 1) label_cell(get_dimension_string($myrow['dimension_id'], true)); if ($dim > 1) @@ -312,6 +320,7 @@ if ($selected_id != -1) $_POST['dest_id'] = $myrow["dest_id"]; $_POST['actn'] = $myrow["action"]; $_POST['amount'] = $myrow["amount"]; + $_POST['memo'] = $myrow["memo"]; $_POST['dimension_id'] = $myrow["dimension_id"]; $_POST['dimension2_id'] = $myrow["dimension2_id"]; } @@ -338,6 +347,7 @@ if ($selected_id != -1) else amount_row(_("Amount").":", 'amount', price_format(0)); } + text_row_ex(_("Line memo").':', 'memo', 50, 256, ''); } if ($dim >= 1) dimensions_list_row(_("Dimension").":", 'dimension_id', null, true, " ", false, 1); diff --git a/includes/ui/ui_view.inc b/includes/ui/ui_view.inc index 16da77e4..12883aa0 100644 --- a/includes/ui/ui_view.inc +++ b/includes/ui/ui_view.inc @@ -625,7 +625,7 @@ function display_allocations_to($person_type, $person_id, $type, $type_no, $tota function display_quick_entries(&$cart, $id, $base, $type, $descr='') { $bank_amount = 0; - + if (!isset($id) || $id == null || $id == "") { display_error( _("No Quick Entries are defined.")); @@ -635,31 +635,79 @@ function display_quick_entries(&$cart, $id, $base, $type, $descr='') { if ($type == QE_DEPOSIT) $base = -$base; - if ($type != QE_SUPPINV) // only one quick entry on journal/bank transaction + if ($type != QE_SUPPINV && $type != QE_JOURNAL) // only one quick entry on journal/bank transaction $cart->clear_items(); + + // as for now tax_group is passed only in QE_SUPPINV + $qe_lines = quickentry_calculate($base, $id, @$cart->tax_group_id, $cart->tran_date); + + if ($qe_lines === 0) + { + display_error( _("No Quick Entry lines are defined.")); + set_focus('totamount'); + return 0; + } elseif ($qe_lines === -1) + { + display_error(_("Cannot post to GL account used by more than one tax type.")); + return 0; + } + foreach($qe_lines as $qe_line) + { + if ($descr != '') $qe_line['descr'] .= ': '.$descr; + if ($type != QE_SUPPINV) + $cart->add_gl_item($qe_line['code'], $qe_line['dim1'], + $qe_line['dim2'], $qe_line['amount'], $qe_line['descr']); + else + { + $acc_name = get_gl_account_name($qe_line['code']); + $cart->add_gl_codes_to_trans($qe_line['code'], + $acc_name, $qe_line['dim1'], + $qe_line['dim2'], $qe_line['amount'], $qe_line['descr']); + } + } + } + return $bank_amount; +} + +//-------------------------------------------------------------------------------------- + +function quickentry_calculate($base, $id, $taxgroup=null, $date=null) +{ + $gl_entries = array(); $qe = get_quick_entry($id); - if ($qe['bal_type'] == 1) + $type = $qe['type']; + $total = $base; + // quick entry made on account balance is special case. + if ($qe['bal_type'] == 1) // if this is quick entry based on balance - calculate it { - if ($qe['base_amount'] == 1.0) // monthly - $begin = begin_month($cart->tran_date); + // Note, that this is ugly hack overriding standard field usage + // just to make the feature available between major FA releases! + $gl_code = $qe['base_desc']; + $monthly = $qe['base_amount'] == 1.0; + + if (!isset($date)) + $date = Today(); + + if ($monthly) // marked as monthly + $begin = begin_month($date); else { - if (is_account_balancesheet($qe['base_desc'])) // total + if (is_account_balancesheet($gl_code)) // total $begin = ""; else $begin = begin_fiscalyear(); // from fiscalyear begin } - $base = get_gl_trans_from_to($begin, $cart->tran_date, $qe['base_desc']); + $base = get_gl_trans_from_to($begin, $date, $gl_code); } - if ($descr != '') $qe['description'] .= ': '.$descr; + $result = get_quick_entry_lines($id); if (db_num_rows($result) == 0) { - display_error( _("No Quick Entry lines are defined.")); - set_focus('totamount'); return 0; } + + // first calculate total rate in case more than one tax is used $totrate = 0; while ($row = db_fetch($result)) { @@ -669,14 +717,15 @@ function display_quick_entries(&$cart, $id, $base, $type, $descr='') case "t": // post taxes calculated on base amount case "t+": // ditto & increase base amount case "t-": // ditto & reduce base amount - if (substr($row['action'],0,1) != 'T') - $totrate += get_tax_type_rate($row['dest_id']); + if (substr($row['action'], 0, 1) != 'T') + $totrate += get_tax_type_default_rate($row['dest_id']); } } $first = true; $taxbase = 0; foreach($qe_lines as $qe_line) { + $gl_code = $qe_line['dest_id']; switch (strtolower($qe_line['action'])) { case "=": // post current base amount to GL account $part = $base; @@ -714,58 +763,45 @@ function display_quick_entries(&$cart, $id, $base, $type, $descr='') $part = $taxbase; else $part = $base/100; + $item_tax = get_tax_type($qe_line['dest_id']); //if ($type == QE_SUPPINV && substr($qe_line['action'],0,1) != 'T') if ($type == QE_SUPPINV) { - $taxgroup = $cart->tax_group_id; $rates = 0; - $res = get_tax_group_rates($cart->tax_group_id); + $res = get_tax_group_rates($taxgroup); while ($row = db_fetch($res)) $rates += $row['rate']; if ($rates == 0) continue 2; } $tax = round2($part * $item_tax['rate'], user_price_dec()); - if ($tax==0) continue 2; + if ($tax == 0) continue 2; $gl_code = ($type == QE_DEPOSIT || ($type == QE_JOURNAL && $base < 0)) ? $item_tax['sales_gl_code'] : $item_tax['purchasing_gl_code']; if (!is_tax_gl_unique($gl_code)) { - display_error(_("Cannot post to GL account used by more than one tax type.")); - break 2; - } - if ($type != QE_SUPPINV) - $cart->add_gl_item($gl_code, - $qe_line['dimension_id'], $qe_line['dimension2_id'], - $tax, $qe['description']); - else - { - $acc_name = get_gl_account_name($gl_code); - $cart->add_gl_codes_to_trans($gl_code, - $acc_name, $qe_line['dimension_id'], - $qe_line['dimension2_id'], $tax, $qe['description']); + return -1; // more than one taxtype used with this GL account } + + $part = $tax; if (strpos($qe_line['action'], '+')) $base += $tax; elseif (strpos($qe_line['action'], '-')) $base -= $tax; - continue 2; - } - if ($type != QE_SUPPINV) - $cart->add_gl_item($qe_line['dest_id'], $qe_line['dimension_id'], - $qe_line['dimension2_id'], $part, $qe['description']); - else - { - $acc_name = get_gl_account_name($qe_line['dest_id']); - $cart->add_gl_codes_to_trans($qe_line['dest_id'], - $acc_name, $qe_line['dimension_id'], - $qe_line['dimension2_id'], $part, $qe['description']); + } + $gl_entries[] = array( + 'code' => $gl_code, + 'name' => get_gl_account_name($gl_code), + 'dim1' => $qe_line['dimension_id'], + 'dim2' => $qe_line['dimension2_id'], + 'amount' => $part, + 'descr' => $qe_line['memo'] + ); } - } - } - return $bank_amount; + return $gl_entries; } + //-------------------------------------------------------------------------------------- // // Simple English version of number to words conversion. diff --git a/sql/alter2.4.sql b/sql/alter2.4.sql index 92d77970..8b74324f 100644 --- a/sql/alter2.4.sql +++ b/sql/alter2.4.sql @@ -151,3 +151,6 @@ 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) + +ALTER TABLE `0_quick_entries` ADD COLUMN `usage` varchar(120) NULL AFTER `description`; +ALTER TABLE `0_quick_entry_lines` ADD COLUMN `memo` tinytext NOT NULL AFTER `amount`; diff --git a/sql/en_US-demo.sql b/sql/en_US-demo.sql index 65d40502..aced2220 100644 --- a/sql/en_US-demo.sql +++ b/sql/en_US-demo.sql @@ -1390,6 +1390,7 @@ CREATE TABLE IF NOT EXISTS `0_quick_entries` ( `id` smallint(6) unsigned NOT NULL auto_increment, `type` tinyint(1) NOT NULL default '0', `description` varchar(60) NOT NULL, + `usage` varchar(120) NULL, `base_amount` double NOT NULL default '0', `base_desc` varchar(60) default NULL, `bal_type` tinyint(1) NOT NULL default '0', @@ -1401,9 +1402,9 @@ CREATE TABLE IF NOT EXISTS `0_quick_entries` ( -- Dumping data for table `0_quick_entries` -- -INSERT INTO `0_quick_entries` VALUES(1, 1, 'Maintenance', 0, 'Amount', 0); -INSERT INTO `0_quick_entries` VALUES(2, 4, 'Phone', 0, 'Amount', 0); -INSERT INTO `0_quick_entries` VALUES(3, 2, 'Cash Sales', 0, 'Amount', 0); +INSERT INTO `0_quick_entries` VALUES(1, 1, 'Maintenance', NULL, 0, 'Amount', 0); +INSERT INTO `0_quick_entries` VALUES(2, 4, 'Phone', NULL, 0, 'Amount', 0); +INSERT INTO `0_quick_entries` VALUES(3, 2, 'Cash Sales', 'Retail sales without invoice', 0, 'Amount', 0); -- -------------------------------------------------------- @@ -1416,6 +1417,7 @@ CREATE TABLE IF NOT EXISTS `0_quick_entry_lines` ( `id` smallint(6) unsigned NOT NULL auto_increment, `qid` smallint(6) unsigned NOT NULL, `amount` double default '0', + `memo` tinytext NOT NULL, `action` varchar(2) NOT NULL, `dest_id` varchar(15) NOT NULL default '', `dimension_id` smallint(6) unsigned default NULL, @@ -1428,12 +1430,12 @@ CREATE TABLE IF NOT EXISTS `0_quick_entry_lines` ( -- Dumping data for table `0_quick_entry_lines` -- -INSERT INTO `0_quick_entry_lines` VALUES(1, 1, 0, 't-', '1', 0, 0); -INSERT INTO `0_quick_entry_lines` VALUES(2, 2, 0, 't-', '1', 0, 0); -INSERT INTO `0_quick_entry_lines` VALUES(3, 3, 0, 't-', '1', 0, 0); -INSERT INTO `0_quick_entry_lines` VALUES(4, 3, 0, '=', '4010', 0, 0); -INSERT INTO `0_quick_entry_lines` VALUES(5, 1, 0, '=', '5765', 0, 0); -INSERT INTO `0_quick_entry_lines` VALUES(6, 2, 0, '=', '5780', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(1, 1, 0, '', 't-', '1', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(2, 2, 0, '', 't-', '1', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(3, 3, 0, '', 't-', '1', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(4, 3, 0, '', '=', '4010', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(5, 1, 0, '', '=', '5765', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(6, 2, 0, '', '=', '5780', 0, 0); -- -------------------------------------------------------- diff --git a/sql/en_US-new.sql b/sql/en_US-new.sql index 73d29c9e..b5ccd678 100644 --- a/sql/en_US-new.sql +++ b/sql/en_US-new.sql @@ -1217,6 +1217,7 @@ CREATE TABLE IF NOT EXISTS `0_quick_entries` ( `id` smallint(6) unsigned NOT NULL auto_increment, `type` tinyint(1) NOT NULL default '0', `description` varchar(60) NOT NULL, + `usage` varchar(120) NULL, `base_amount` double NOT NULL default '0', `base_desc` varchar(60) default NULL, `bal_type` tinyint(1) NOT NULL default '0', @@ -1228,9 +1229,9 @@ CREATE TABLE IF NOT EXISTS `0_quick_entries` ( -- Dumping data for table `0_quick_entries` -- -INSERT INTO `0_quick_entries` VALUES(1, 1, 'Maintenance', 0, 'Amount', 0); -INSERT INTO `0_quick_entries` VALUES(2, 4, 'Phone', 0, 'Amount', 0); -INSERT INTO `0_quick_entries` VALUES(3, 2, 'Cash Sales', 0, 'Amount', 0); +INSERT INTO `0_quick_entries` VALUES(1, 1, 'Maintenance', NULL, 0, 'Amount', 0); +INSERT INTO `0_quick_entries` VALUES(2, 4, 'Phone', NULL, 0, 'Amount', 0); +INSERT INTO `0_quick_entries` VALUES(3, 2, 'Cash Sales', 'Retail sales without invoice', 0, 'Amount', 0); -- -------------------------------------------------------- @@ -1243,6 +1244,7 @@ CREATE TABLE IF NOT EXISTS `0_quick_entry_lines` ( `id` smallint(6) unsigned NOT NULL auto_increment, `qid` smallint(6) unsigned NOT NULL, `amount` double default '0', + `memo` tinytext NOT NULL, `action` varchar(2) NOT NULL, `dest_id` varchar(15) NOT NULL default '', `dimension_id` smallint(6) unsigned default NULL, @@ -1255,12 +1257,12 @@ CREATE TABLE IF NOT EXISTS `0_quick_entry_lines` ( -- Dumping data for table `0_quick_entry_lines` -- -INSERT INTO `0_quick_entry_lines` VALUES(1, 1, 0, 't-', '1', 0, 0); -INSERT INTO `0_quick_entry_lines` VALUES(2, 2, 0, 't-', '1', 0, 0); -INSERT INTO `0_quick_entry_lines` VALUES(3, 3, 0, 't-', '1', 0, 0); -INSERT INTO `0_quick_entry_lines` VALUES(4, 3, 0, '=', '4010', 0, 0); -INSERT INTO `0_quick_entry_lines` VALUES(5, 1, 0, '=', '5765', 0, 0); -INSERT INTO `0_quick_entry_lines` VALUES(6, 2, 0, '=', '5780', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(1, 1, 0, '', 't-', '1', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(2, 2, 0, '', 't-', '1', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(3, 3, 0, '', 't-', '1', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(4, 3, 0, '', '=', '4010', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(5, 1, 0, '', '=', '5765', 0, 0); +INSERT INTO `0_quick_entry_lines` VALUES(6, 2, 0, '', '=', '5780', 0, 0); -- -------------------------------------------------------- -- 2.30.2