X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=taxes%2Fdb%2Ftax_groups_db.inc;h=826ad8a05865b3b6dc482b86d017b764c3fbb678;hb=4c09b19388f22b15edee7024a392055418275188;hp=10f0256a2e4654826d50815ff76aa8ee8eec4c6e;hpb=c09be0dad6b05131e240349a375af7a4b7bf3444;p=fa-stable.git diff --git a/taxes/db/tax_groups_db.inc b/taxes/db/tax_groups_db.inc index 10f0256a..826ad8a0 100644 --- a/taxes/db/tax_groups_db.inc +++ b/taxes/db/tax_groups_db.inc @@ -1,5 +1,14 @@ . +***********************************************************************/ function clear_shipping_tax_group() { $sql = "UPDATE ".TB_PREF."tax_groups SET tax_shipping=0 WHERE 1"; db_query($sql, "could not update tax_shipping fields"); @@ -12,7 +21,7 @@ function add_tax_group($name, $tax_shipping, $taxes, $rates) if($tax_shipping) // only one tax group for shipping clear_shipping_tax_group(); - $sql = "INSERT INTO ".TB_PREF."tax_groups (name, tax_shipping) VALUES (".db_escape($name).", $tax_shipping)"; + $sql = "INSERT INTO ".TB_PREF."tax_groups (name, tax_shipping) VALUES (".db_escape($name).", ".db_escape($tax_shipping).")"; db_query($sql, "could not add tax group"); $id = db_insert_id(); @@ -29,7 +38,7 @@ function update_tax_group($id, $name, $tax_shipping, $taxes, $rates) if($tax_shipping) // only one tax group for shipping clear_shipping_tax_group(); - $sql = "UPDATE ".TB_PREF."tax_groups SET name=".db_escape($name).",tax_shipping=$tax_shipping WHERE id=$id"; + $sql = "UPDATE ".TB_PREF."tax_groups SET name=".db_escape($name).",tax_shipping=".db_escape($tax_shipping)." WHERE id=".db_escape($id); db_query($sql, "could not update tax group"); delete_tax_group_items($id); @@ -38,16 +47,17 @@ function update_tax_group($id, $name, $tax_shipping, $taxes, $rates) commit_transaction(); } -function get_all_tax_groups() +function get_all_tax_groups($all=false) { $sql = "SELECT * FROM ".TB_PREF."tax_groups"; + if (!$all) $sql .= " WHERE !inactive"; return db_query($sql, "could not get all tax group"); } function get_tax_group($type_id) { - $sql = "SELECT * FROM ".TB_PREF."tax_groups WHERE id=$type_id"; + $sql = "SELECT * FROM ".TB_PREF."tax_groups WHERE id=".db_escape($type_id); $result = db_query($sql, "could not get tax group"); @@ -58,7 +68,7 @@ function delete_tax_group($id) { begin_transaction(); - $sql = "DELETE FROM ".TB_PREF."tax_groups WHERE id=$id"; + $sql = "DELETE FROM ".TB_PREF."tax_groups WHERE id=".db_escape($id); db_query($sql, "could not delete tax group"); @@ -72,79 +82,70 @@ function add_tax_group_items($id, $items, $rates) for ($i=0; $i < count($items); $i++) { $sql = "INSERT INTO ".TB_PREF."tax_group_items (tax_group_id, tax_type_id, rate) - VALUES ($id, " . $items[$i] . ", " . $rates[$i] .")"; + VALUES (".db_escape($id).", ".db_escape($items[$i]).", " . $rates[$i] .")"; db_query($sql, "could not add item tax group item"); } } function delete_tax_group_items($id) { - $sql = "DELETE FROM ".TB_PREF."tax_group_items WHERE tax_group_id=$id"; + $sql = "DELETE FROM ".TB_PREF."tax_group_items WHERE tax_group_id=".db_escape($id); db_query($sql, "could not delete item tax group items"); } -function get_tax_group_items($id) +// +// Return all tax types with rate value updated according to tax group selected +// Call the function without arg to find shipment group taxes. +// +function get_tax_group_rates($group_id=null) { - $sql = "SELECT ".TB_PREF."tax_group_items.*, ".TB_PREF."tax_types.name AS tax_type_name, - ".TB_PREF."tax_types.sales_gl_code, ".TB_PREF."tax_types.purchasing_gl_code - FROM ".TB_PREF."tax_group_items, ".TB_PREF."tax_types - WHERE tax_group_id=$id - AND ".TB_PREF."tax_types.id=tax_type_id"; - - return db_query($sql, "could not get item tax type group items"); + global $suppress_tax_rates; + + $sql = + "SELECT t.id as tax_type_id," + .(isset($suppress_tax_rates) && $suppress_tax_rates == 1 + ? "t.name as tax_type_name," + : "CONCAT(t.name, ' (', t.rate, '%)') as tax_type_name,") + ."t.sales_gl_code, + t.purchasing_gl_code, + IF(g.tax_type_id, t.rate, NULL) as rate + FROM ".TB_PREF."tax_types t + LEFT JOIN ".TB_PREF."tax_group_items g ON t.id=g.tax_type_id + AND g.tax_group_id=". ($group_id ? db_escape($group_id) : "(SELECT id FROM ".TB_PREF."tax_groups WHERE tax_shipping=1)") + ." WHERE !t.inactive"; + + return db_query($sql, "cannot get tax types as array"); } function get_tax_group_items_as_array($id) { - $ret_tax_array = array(); - - $tax_group_items = get_tax_group_items($id); - - while ($tax_group_item = db_fetch($tax_group_items)) + $active_taxes = $ret_tax_array = array(); + + $tax_group_items = get_tax_group_rates($id); + + while ($tax_group_item = db_fetch_assoc($tax_group_items)) { - $index = $tax_group_item['tax_type_id']; - $ret_tax_array[$index]['tax_type_id'] = $tax_group_item['tax_type_id']; - $ret_tax_array[$index]['tax_type_name'] = $tax_group_item['tax_type_name']; - $ret_tax_array[$index]['sales_gl_code'] = $tax_group_item['sales_gl_code']; - $ret_tax_array[$index]['purchasing_gl_code'] = $tax_group_item['purchasing_gl_code']; - $ret_tax_array[$index]['rate'] = $tax_group_item['rate']; - $ret_tax_array[$index]['Value'] = 0; + $tax_group_item['Value'] = 0; + $ret_tax_array[$tax_group_item['tax_type_id']] = $tax_group_item; } - + return $ret_tax_array; } -function get_shipping_tax_group_items() -{ - - $sql = "SELECT ".TB_PREF."tax_group_items.*, ".TB_PREF."tax_types.name AS tax_type_name, - ".TB_PREF."tax_types.sales_gl_code, ".TB_PREF."tax_types.purchasing_gl_code - FROM " .TB_PREF."tax_group_items, ".TB_PREF."tax_types, ".TB_PREF."tax_groups - WHERE " .TB_PREF."tax_groups.tax_shipping=1 - AND " .TB_PREF."tax_groups.id=tax_group_id - AND ".TB_PREF."tax_types.id=tax_type_id"; - return db_query($sql, "could not get shipping tax group items"); -} function get_shipping_tax_as_array() { - $ret_tax_array = array(); + $active_taxes = $ret_tax_array = array(); - - $tax_group_items = get_shipping_tax_group_items(); + $tax_group_items = get_tax_group_rates(); - while ($tax_group_item = db_fetch($tax_group_items)) + while ($tax_group_item = db_fetch_assoc($tax_group_items)) { - $index = $tax_group_item['tax_type_id']; - $ret_tax_array[$index]['tax_type_id'] = $tax_group_item['tax_type_id']; - $ret_tax_array[$index]['tax_type_name'] = $tax_group_item['tax_type_name']; - $ret_tax_array[$index]['sales_gl_code'] = $tax_group_item['sales_gl_code']; - $ret_tax_array[$index]['purchasing_gl_code'] = $tax_group_item['purchasing_gl_code']; - $ret_tax_array[$index]['rate'] = $tax_group_item['rate']; - $ret_tax_array[$index]['Value'] = 0; + $tax_group_item['Value'] = 0; + $ret_tax_array[$tax_group_item['tax_type_id']] = $tax_group_item; } - + return $ret_tax_array; } -?> \ No newline at end of file +