From 4221bf69f713a2fb0bb4c4cae6f8f4f819c3ec31 Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Mon, 30 Nov 2009 09:02:32 +0000 Subject: [PATCH] Moved all SQL statements from PHP files into relevant *_db.inc files. --- admin/attachments.php | 44 +--- admin/create_coy.php | 4 +- admin/db/attachments_db.inc | 71 ++++++ admin/db/company_db.inc | 120 +++++----- admin/db/fiscalyears_db.inc | 338 ++++++++++++++++++++++++++++ admin/db/maintenance_db.inc | 6 + admin/db/printers_db.inc | 13 ++ admin/db/shipping_db.inc | 93 ++++++++ admin/db/transactions_db.inc | 43 ++++ admin/fiscalyears.php | 264 +--------------------- admin/gl_setup.php | 5 - admin/payment_terms.php | 57 +---- admin/printers.php | 10 +- admin/shipping_companies.php | 44 +--- admin/view_print_transaction.php | 27 +-- includes/date_functions.inc | 6 +- lang/en_US/LC_MESSAGES/en_US.mo | Bin 901 -> 901 bytes reporting/includes/excel_report.inc | 1 + reporting/includes/pdf_report.inc | 1 + 19 files changed, 668 insertions(+), 479 deletions(-) create mode 100644 admin/db/attachments_db.inc create mode 100644 admin/db/fiscalyears_db.inc create mode 100644 admin/db/shipping_db.inc create mode 100644 admin/db/transactions_db.inc diff --git a/admin/attachments.php b/admin/attachments.php index dafa3e02..3d762348 100644 --- a/admin/attachments.php +++ b/admin/attachments.php @@ -17,6 +17,7 @@ include_once($path_to_root . "/includes/session.inc"); include_once($path_to_root . "/includes/date_functions.inc"); include_once($path_to_root . "/includes/ui.inc"); include_once($path_to_root . "/includes/data_checks.inc"); +include_once($path_to_root . "/admin/db/attachments_db.inc"); if (isset($_GET['vw'])) $view_id = $_GET['vw']; @@ -108,32 +109,16 @@ if ($Mode == 'ADD_ITEM' || $Mode == 'UPDATE_ITEM') $unique_name = $filename = $filetype = ""; $filesize = 0; } - $date = date2sql(Today()); if ($Mode == 'ADD_ITEM') { - $sql = "INSERT INTO ".TB_PREF."attachments (type_no, trans_no, description, filename, unique_name, - filesize, filetype, tran_date) VALUES (".db_escape($_POST['filterType'])."," - .db_escape($_POST['trans_no']).",".db_escape($_POST['description']).", " - .db_escape($filename).", ".db_escape($unique_name).", ".db_escape($filesize) - .", ".db_escape($filetype).", '$date')"; - db_query($sql, "Attachment could not be inserted"); + add_attachment($_POST['filterType'], $_POST['trans_no'], $_POST['description'], + $filename, $unique_name, $filesize, $filetype); display_notification(_("Attachment has been inserted.")); } else { - $sql = "UPDATE ".TB_PREF."attachments SET - type_no=".db_escape($_POST['filterType']).", - trans_no=".db_escape($_POST['trans_no']).", - description=".db_escape($_POST['description']).", "; - if ($filename != "") - { - $sql .= "filename=".db_escape($filename).", - unique_name=".db_escape($unique_name).", - filesize=".db_escape($filesize).", - filetype=".db_escape($filetype); - } - $sql .= "tran_date='$date' WHERE id=".db_escape($selected_id); - db_query($sql, "Attachment could not be updated"); + update_attachment($selected_id, $_POST['filterType'], $_POST['trans_no'], $_POST['description'], + $filename, $unique_name, $filesize, $filetype); display_notification(_("Attachment has been updated.")); } $Mode = 'RESET'; @@ -145,8 +130,7 @@ if ($Mode == 'Delete') $dir = $comp_path."/".user_company(). "/attachments"; if (file_exists($dir."/".$row['unique_name'])) unlink($dir."/".$row['unique_name']); - $sql = "DELETE FROM ".TB_PREF."attachments WHERE id = ".db_escape($selected_id); - db_query($sql, "Could not delete attachment"); + delete_attachment($selected_id); display_notification(_("Attachment has been deleted.")); $Mode = 'RESET'; } @@ -171,22 +155,6 @@ function viewing_controls() end_form(); } -//---------------------------------------------------------------------------------------- - -function get_attached_documents($type) -{ - $sql = "SELECT * FROM ".TB_PREF."attachments WHERE type_no=".db_escape($type) - ." ORDER BY trans_no"; - return db_query($sql, "Could not retrieve attachments"); -} - -function get_attachment($id) -{ - $sql = "SELECT * FROM ".TB_PREF."attachments WHERE id=".db_escape($id); - $result = db_query($sql, "Could not retrieve attachments"); - return db_fetch($result); -} - function display_rows($type) { global $table_style; diff --git a/admin/create_coy.php b/admin/create_coy.php index e1b2882f..1a03e39d 100644 --- a/admin/create_coy.php +++ b/admin/create_coy.php @@ -124,8 +124,8 @@ function handle_submit() if (is_uploaded_file ($filename)) { db_import($filename, $conn, $id); - if (isset($_POST['admpassword']) && $_POST['admpassword'] != "") - db_query("UPDATE ".$conn['tbpref']."users set password = '".md5($_POST['admpassword']). "' WHERE user_id = 'admin'"); + if (isset($_POST['admpassword']) && $_POST['admpassword'] != "") + update_admin_password($conn, md5($_POST['admpassword'])); } else { diff --git a/admin/db/attachments_db.inc b/admin/db/attachments_db.inc new file mode 100644 index 00000000..6934071f --- /dev/null +++ b/admin/db/attachments_db.inc @@ -0,0 +1,71 @@ +. +***********************************************************************/ + +//---------------------------------------------------------------------------------------- + +function add_attachment($filterType, $trans_no, $description, + $filename, $unique_name, $filesize, $filetype) +{ + $date = date2sql(Today()); + $sql = "INSERT INTO ".TB_PREF."attachments (type_no, trans_no, description, filename, unique_name, + filesize, filetype, tran_date) VALUES (".db_escape($filterType)."," + .db_escape($trans_no).",".db_escape($description).", " + .db_escape($filename).", ".db_escape($unique_name).", ".db_escape($filesize) + .", ".db_escape($filetype).", '$date')"; + db_query($sql, "Attachment could not be inserted"); +} +//---------------------------------------------------------------------------------------- + +function update_attachment($selected_id, $filterType, $trans_no, $description, + $filename, $unique_name, $filesize, $filetype) +{ + $date = date2sql(Today()); + $sql = "UPDATE ".TB_PREF."attachments SET + type_no=".db_escape($filterType).", + trans_no=".db_escape($trans_no).", + description=".db_escape($description).", "; + if ($filename != "") + { + $sql .= "filename=".db_escape($filename).", + unique_name=".db_escape($unique_name).", + filesize=".db_escape($filesize).", + filetype=".db_escape($filetype); + } + $sql .= "tran_date='$date' WHERE id=".db_escape($selected_id); + db_query($sql, "Attachment could not be updated"); +} + +//---------------------------------------------------------------------------------------- + +function delete_attachment($id) +{ + $sql = "DELETE FROM ".TB_PREF."attachments WHERE id = ".db_escape($id); + db_query($sql, "Could not delete attachment"); +} +//---------------------------------------------------------------------------------------- + +function get_attached_documents($type) +{ + $sql = "SELECT * FROM ".TB_PREF."attachments WHERE type_no=".db_escape($type) + ." ORDER BY trans_no"; + return db_query($sql, "Could not retrieve attachments"); +} + +function get_attachment($id) +{ + $sql = "SELECT * FROM ".TB_PREF."attachments WHERE id=".db_escape($id); + $result = db_query($sql, "Could not retrieve attachments"); + return db_fetch($result); +} + + +?> \ No newline at end of file diff --git a/admin/db/company_db.inc b/admin/db/company_db.inc index 621e9865..c95ef8de 100644 --- a/admin/db/company_db.inc +++ b/admin/db/company_db.inc @@ -114,83 +114,99 @@ function get_company_pref($pref_name, $tbpref = TB_PREF) return $prefs[$pref_name]; } -// fiscal year routines -function add_fiscalyear($from_date, $to_date, $closed) +function get_base_sales_type() { - $from = date2sql($from_date); - $to = date2sql($to_date); - - $sql = "INSERT INTO ".TB_PREF."fiscal_year (begin, end, closed) - VALUES (".db_escape($from).",".db_escape($to).", ".db_escape($closed).")"; + $sql = "SELECT base_sales FROM ".TB_PREF."company WHERE coy_code=1"; - db_query($sql, "could not add fiscal year"); + $result = db_query($sql, "could not get base sales type"); + $myrow = db_fetch($result); + return $myrow[0]; } -function update_fiscalyear($id, $closed) -{ - $sql = "UPDATE ".TB_PREF."fiscal_year SET closed=".db_escape($closed)." - WHERE id=".db_escape($id); +function get_company_extensions($id = -1) { + global $path_to_root; - db_query($sql, "could not update fiscal year"); + $file = $path_to_root.($id == -1 ? '' : '/company/'.$id).'/installed_extensions.php'; + $installed_extensions = array(); + if (is_file($file)) { + include($file); + } + return $installed_extensions; } -function get_all_fiscalyears() +function add_payment_terms($daysOrFoll, $terms, $dayNumber) { - $sql = "SELECT * FROM ".TB_PREF."fiscal_year ORDER BY begin"; - - return db_query($sql, "could not get all fiscal years"); + if ($daysOrFoll) + { + $sql = "INSERT INTO ".TB_PREF."payment_terms (terms, + days_before_due, day_in_following_month) + VALUES (" . + db_escape($terms) . ", " . db_escape($dayNumber) . ", 0)"; + } + else + { + $sql = "INSERT INTO ".TB_PREF."payment_terms (terms, + days_before_due, day_in_following_month) + VALUES (" . db_escape($terms) . ", + 0, " . db_escape($dayNumber) . ")"; + } + db_query($sql,"The payment term could not be added"); } -function get_fiscalyear($id) +function update_payment_terms($selected_id, $daysOrFoll, $terms, $dayNumber) { - $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE id=".db_escape($id); - - $result = db_query($sql, "could not get fiscal year"); - - return db_fetch($result); + if ($daysOrFoll) + { + $sql = "UPDATE ".TB_PREF."payment_terms SET terms=" . db_escape($terms) . ", + day_in_following_month=0, + days_before_due=" . db_escape($dayNumber) . " + WHERE terms_indicator = " .db_escape($selected_id); + } + else + { + $sql = "UPDATE ".TB_PREF."payment_terms SET terms=" . db_escape($terms) . ", + day_in_following_month=" . db_escape($dayNumber) . ", + days_before_due=0 + WHERE terms_indicator = " .db_escape($selected_id); + } + db_query($sql,"The payment term could not be updated"); } -function get_current_fiscalyear() +function delete_payment_terms($selected_id) { - $year = get_company_pref('f_year'); - - $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE id=".db_escape($year); + $sql="DELETE FROM ".TB_PREF."payment_terms WHERE terms_indicator=".db_escape($selected_id); + db_query($sql,"could not delete a payment terms"); +} - $result = db_query($sql, "could not get current fiscal year"); +function get_payment_terms($selected_id) +{ + $sql = "SELECT * FROM ".TB_PREF."payment_terms WHERE terms_indicator=".db_escape($selected_id); + $result = db_query($sql,"could not get payment term"); return db_fetch($result); } -function delete_fiscalyear($id) +function get_payment_terms_all($show_inactive) { - begin_transaction(); - - $sql="DELETE FROM ".TB_PREF."fiscal_year WHERE id=".db_escape($id); - - db_query($sql, "could not delete fiscal year"); - - commit_transaction(); + $sql = "SELECT * FROM ".TB_PREF."payment_terms"; + if (!$show_inactive) $sql .= " WHERE !inactive"; + return db_query($sql,"could not get payment terms"); } -function get_base_sales_type() +function customer_has_terms($selected_id) { - $sql = "SELECT base_sales FROM ".TB_PREF."company WHERE coy_code=1"; - - $result = db_query($sql, "could not get base sales type"); - $myrow = db_fetch($result); - return $myrow[0]; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtors_master WHERE payment_terms = ".db_escape($selected_id); + $result = db_query($sql,"check failed"); + $myrow = db_fetch_row($result); + return ($myrow[0] > 0); } -function get_company_extensions($id = -1) { - global $path_to_root; - - $file = $path_to_root.($id == -1 ? '' : '/company/'.$id).'/installed_extensions.php'; - $installed_extensions = array(); - if (is_file($file)) { - include($file); - } - return $installed_extensions; +function supplier_has_terms($selected_id) +{ + $sql= "SELECT COUNT(*) FROM ".TB_PREF."suppliers WHERE payment_terms = ".db_escape($selected_id); + $result = db_query($sql,"check failed"); + $myrow = db_fetch_row($result); + return ($myrow[0] > 0); } - ?> \ No newline at end of file diff --git a/admin/db/fiscalyears_db.inc b/admin/db/fiscalyears_db.inc new file mode 100644 index 00000000..fb2084c3 --- /dev/null +++ b/admin/db/fiscalyears_db.inc @@ -0,0 +1,338 @@ +. +***********************************************************************/ + +// fiscal year routines +function add_fiscalyear($from_date, $to_date, $closed) +{ + $from = date2sql($from_date); + $to = date2sql($to_date); + + $sql = "INSERT INTO ".TB_PREF."fiscal_year (begin, end, closed) + VALUES (".db_escape($from).",".db_escape($to).", ".db_escape($closed).")"; + + db_query($sql, "could not add fiscal year"); +} + +function update_fiscalyear($id, $closed) +{ + $sql = "UPDATE ".TB_PREF."fiscal_year SET closed=".db_escape($closed)." + WHERE id=".db_escape($id); + + db_query($sql, "could not update fiscal year"); +} + +function get_all_fiscalyears() +{ + $sql = "SELECT * FROM ".TB_PREF."fiscal_year ORDER BY begin"; + + return db_query($sql, "could not get all fiscal years"); +} + +function get_fiscalyear($id) +{ + $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE id=".db_escape($id); + + $result = db_query($sql, "could not get fiscal year"); + + return db_fetch($result); +} + +function get_current_fiscalyear() +{ + global $path_to_root; + include_once($path_to_root . "/admin/db/company_db.inc"); + $year = get_company_pref('f_year'); + + $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE id=".db_escape($year); + + $result = db_query($sql, "could not get current fiscal year"); + + return db_fetch($result); +} + + +function delete_fiscalyear($id) +{ + begin_transaction(); + + $sql="DELETE FROM ".TB_PREF."fiscal_year WHERE id=".db_escape($id); + + db_query($sql, "could not delete fiscal year"); + + commit_transaction(); +} + +function is_date_in_fiscalyears($date) +{ + $date = date2sql($date); + $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE '$date' >= begin AND '$date' <= end"; + + $result = db_query($sql, "could not get all fiscal years"); + return db_fetch($result) !== false; +} + +function is_bad_begin_date($date) +{ + $bdate = date2sql($date); + $sql = "SELECT MAX(end) FROM ".TB_PREF."fiscal_year WHERE begin < '$bdate'"; + + $result = db_query($sql, "could not retrieve last fiscal years"); + $row = db_fetch_row($result); + if ($row[0] === null) + return false; + $max = add_days(sql2date($row[0]), 1); + return ($max !== $date); +} + +function check_years_before($date, $closed=false) +{ + $date = date2sql($date); + $sql = "SELECT COUNT(*) FROM ".TB_PREF."fiscal_year WHERE begin < '$date'"; + if (!$closed) + $sql .= " AND closed=0"; + + $result = db_query($sql, "could not check fiscal years before"); + $row = db_fetch_row($result); + return ($row[0] > 0); +} + +//--------------------------------------------------------------------------------------------- +function close_year($year) +{ + $co = get_company_prefs(); + if (get_gl_account($co['retained_earnings_act']) == false || get_gl_account($co['profit_loss_year_act']) == false) + { + display_error(_("The Retained Earnings Account or the Profit and Loss Year Account has not been set in System and General GL Setup")); + return false; + } + begin_transaction(); + + $myrow = get_fiscalyear($year); + $to = $myrow['end']; + // retrieve total balances from balance sheet accounts + $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans INNER JOIN ".TB_PREF."chart_master ON account=account_code + INNER JOIN ".TB_PREF."chart_types ON account_type=id INNER JOIN ".TB_PREF."chart_class ON class_id=cid + WHERE ctype>=".CL_ASSETS." AND ctype <=".CL_EQUITY." AND tran_date <= '$to'"; + $result = db_query($sql, "The total balance could not be calculated"); + + $row = db_fetch_row($result); + $balance = round2($row[0], user_price_dec()); + + $to = sql2date($to); + + if ($balance != 0.0) + { + $trans_type = ST_JOURNAL; + $trans_id = get_next_trans_no($trans_type); + + add_gl_trans($trans_type, $trans_id, $to, $co['retained_earnings_act'], + 0, 0, _("Closing Year"), -$balance); + add_gl_trans($trans_type, $trans_id, $to, $co['profit_loss_year_act'], + 0, 0, _("Closing Year"), $balance); + + } + close_transactions($to); + + commit_transaction(); + return true; +} + +function open_year($year) +{ + $myrow = get_fiscalyear($year); + $from = sql2date($myrow['begin']); + + begin_transaction(); + open_transactions($from); + commit_transaction(); +} + +//--------------------------------------------------------------------------------------------- +function delete_attachments_and_comments($type_no, $trans_no) +{ + global $comp_path; + + $sql = "SELECT * FROM ".TB_PREF."attachments WHERE type_no = $type_no AND trans_no = $trans_no"; + $result = db_query($sql, "Could not retrieve attachments"); + while ($row = db_fetch($result)) + { + $dir = $comp_path."/".user_company(). "/attachments"; + if (file_exists($dir."/".$row['unique_name'])) + unlink($dir."/".$row['unique_name']); + $sql = "DELETE FROM ".TB_PREF."attachments WHERE type_no = $type_no AND trans_no = $trans_no"; + db_query($sql, "Could not delete attachment"); + } + $sql = "DELETE FROM ".TB_PREF."comments WHERE type = $type_no AND id = $trans_no"; + db_query($sql, "Could not delete comments"); +} + +function delete_this_fiscalyear($selected_id) +{ + global $db_connections; + + db_backup($db_connections[$_SESSION["wa_current_user"]->company], 'Security backup before Fiscal Year Removal'); + begin_transaction(); + $ref = _("Open Balance"); + $myrow = get_fiscalyear($selected_id); + $to = $myrow['end']; + $sql = "SELECT order_no, trans_type FROM ".TB_PREF."sales_orders WHERE ord_date <= '$to' AND type <> 1"; // don't take the templates + $result = db_query($sql, "Could not retrieve sales orders"); + while ($row = db_fetch($result)) + { + $sql = "SELECT SUM(qty_sent), SUM(quantity) FROM ".TB_PREF."sales_order_details WHERE order_no = {$row['order_no']} AND trans_type = {$row['trans_type']}"; + $res = db_query($sql, "Could not retrieve sales order details"); + $row2 = db_fetch_row($res); + if ($row2[0] == $row2[1]) + { + $sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no = {$row['order_no']} AND trans_type = {$row['trans_type']}"; + db_query($sql, "Could not delete sales order details"); + $sql = "DELETE FROM ".TB_PREF."sales_orders WHERE order_no = {$row['order_no']} AND trans_type = {$row['trans_type']}"; + db_query($sql, "Could not delete sales order"); + delete_attachments_and_comments($row['trans_type'], $row['order_no']); + } + } + $sql = "SELECT order_no FROM ".TB_PREF."purch_orders WHERE ord_date <= '$to'"; + $result = db_query($sql, "Could not retrieve purchase orders"); + while ($row = db_fetch($result)) + { + $sql = "SELECT SUM(quantity_ordered), SUM(quantity_received) FROM ".TB_PREF."purch_order_details WHERE order_no = {$row['order_no']}"; + $res = db_query($sql, "Could not retrieve purchase order details"); + $row2 = db_fetch_row($res); + if ($row2[0] == $row2[1]) + { + $sql = "DELETE FROM ".TB_PREF."purch_order_details WHERE order_no = {$row['order_no']}"; + db_query($sql, "Could not delete purchase order details"); + $sql = "DELETE FROM ".TB_PREF."purch_orders WHERE order_no = {$row['order_no']}"; + db_query($sql, "Could not delete purchase order"); + delete_attachments_and_comments(ST_PURCHORDER, $row['order_no']); + } + } + $sql = "SELECT id FROM ".TB_PREF."grn_batch WHERE delivery_date <= '$to'"; + $result = db_query($sql, "Could not retrieve grn batch"); + while ($row = db_fetch($result)) + { + $sql = "DELETE FROM ".TB_PREF."grn_items WHERE grn_batch_id = {$row['id']}"; + db_query($sql, "Could not delete grn items"); + $sql = "DELETE FROM ".TB_PREF."grn_batch WHERE id = {$row['id']}"; + db_query($sql, "Could not delete grn batch"); + delete_attachments_and_comments(25, $row['id']); + } + $sql = "SELECT trans_no, type FROM ".TB_PREF."debtor_trans WHERE tran_date <= '$to' AND + (ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) = alloc"; + $result = db_query($sql, "Could not retrieve debtor trans"); + while ($row = db_fetch($result)) + { + if ($row['type'] == ST_SALESINVOICE) + { + $deliveries = get_parent_trans(ST_SALESINVOICE,$row['trans_no']); + foreach ($deliveries as $delivery) + { + $sql = "DELETE FROM ".TB_PREF."debtor_trans_details WHERE debtor_trans_no = $delivery AND debtor_trans_type = ".ST_CUSTDELIVERY; + db_query($sql, "Could not delete debtor trans details"); + $sql = "DELETE FROM ".TB_PREF."debtor_trans WHERE trans_no = $delivery AND type = ".ST_CUSTDELIVERY; + db_query($sql, "Could not delete debtor trans"); + } + } + $sql = "DELETE FROM ".TB_PREF."cust_allocations WHERE trans_no_from = {$row['trans_no']} AND type_no_from = {$row['type']}"; + db_query($sql, "Could not delete cust allocations"); + $sql = "DELETE FROM ".TB_PREF."debtor_trans_details WHERE debtor_trans_no = {$row['trans_no']} AND debtor_trans_type = {$row['type']}"; + db_query($sql, "Could not delete debtor trans details"); + $sql = "DELETE FROM ".TB_PREF."debtor_trans WHERE trans_no = {$row['trans_no']} AND type = {$row['type']}"; + db_query($sql, "Could not delete debtor trans"); + delete_attachments_and_comments($row['type'], $row['trans_no']); + } + $sql = "SELECT trans_no, type FROM ".TB_PREF."supp_trans WHERE tran_date <= '$to' AND + ABS(ov_amount + ov_gst + ov_discount) = alloc"; + $result = db_query($sql, "Could not retrieve supp trans"); + while ($row = db_fetch($result)) + { + $sql = "DELETE FROM ".TB_PREF."supp_allocations WHERE trans_no_from = {$row['trans_no']} AND type_no_from = {$row['type']}"; + db_query($sql, "Could not delete supp allocations"); + $sql = "DELETE FROM ".TB_PREF."supp_invoice_items WHERE supp_trans_no = {$row['trans_no']} AND supp_trans_type = {$row['type']}"; + db_query($sql, "Could not delete supp invoice items"); + $sql = "DELETE FROM ".TB_PREF."supp_trans WHERE trans_no = {$row['trans_no']} AND type = {$row['type']}"; + db_query($sql, "Could not delete supp trans"); + delete_attachments_and_comments($row['type'], $row['trans_no']); + } + $sql = "SELECT id FROM ".TB_PREF."workorders WHERE released_date <= '$to' AND closed=1"; + $result = db_query($sql, "Could not retrieve supp trans"); + while ($row = db_fetch($result)) + { + $sql = "SELECT issue_no FROM ".TB_PREF."wo_issues WHERE workorder_id = {$row['id']}"; + $res = db_query($sql, "Could not retrieve wo issues"); + while ($row2 = db_fetch_row($res)) + { + $sql = "DELETE FROM ".TB_PREF."wo_issue_items WHERE issue_id = {$row2[0]}"; + db_query($sql, "Could not delete wo issue items"); + } + delete_attachments_and_comments(28, $row['id']); + $sql = "DELETE FROM ".TB_PREF."wo_issues WHERE workorder_id = {$row['id']}"; + db_query($sql, "Could not delete wo issues"); + $sql = "DELETE FROM ".TB_PREF."wo_manufacture WHERE workorder_id = {$row['id']}"; + db_query($sql, "Could not delete wo manufacture"); + $sql = "DELETE FROM ".TB_PREF."wo_requirements WHERE workorder_id = {$row['id']}"; + db_query($sql, "Could not delete wo requirements"); + $sql = "DELETE FROM ".TB_PREF."workorders WHERE id = {$row['id']}"; + db_query($sql, "Could not delete workorders"); + delete_attachments_and_comments(26, $row['id']); + } + $sql = "SELECT loc_code, stock_id, SUM(qty) AS qty, SUM(qty*standard_cost) AS std_cost FROM ".TB_PREF."stock_moves WHERE tran_date <= '$to' GROUP by + loc_code, stock_id"; + $result = db_query($sql, "Could not retrieve supp trans"); + while ($row = db_fetch($result)) + { + $sql = "DELETE FROM ".TB_PREF."stock_moves WHERE tran_date <= '$to' AND loc_code = '{$row['loc_code']}' AND stock_id = '{$row['stock_id']}'"; + db_query($sql, "Could not delete stock moves"); + $qty = $row['qty']; + $std_cost = ($qty == 0 ? 0 : round2($row['std_cost'] / $qty, user_price_dec())); + $sql = "INSERT INTO ".TB_PREF."stock_moves (stock_id, loc_code, tran_date, reference, qty, standard_cost) VALUES + ('{$row['stock_id']}', '{$row['loc_code']}', '$to', '$ref', $qty, $std_cost)"; + db_query($sql, "Could not insert stock move"); + } + $sql = "DELETE FROM ".TB_PREF."voided WHERE date_ <= '$to'"; + db_query($sql, "Could not delete voided items"); + $sql = "DELETE FROM ".TB_PREF."trans_tax_details WHERE tran_date <= '$to'"; + db_query($sql, "Could not delete trans tax details"); + $sql = "DELETE FROM ".TB_PREF."exchange_rates WHERE date_ <= '$to'"; + db_query($sql, "Could not delete exchange rates"); + $sql = "DELETE FROM ".TB_PREF."budget_trans WHERE tran_date <= '$to'"; + db_query($sql, "Could not delete exchange rates"); + $sql = "SELECT account, SUM(amount) AS amount FROM ".TB_PREF."gl_trans WHERE tran_date <= '$to' GROUP by account"; + $result = db_query($sql, "Could not retrieve gl trans"); + while ($row = db_fetch($result)) + { + $sql = "DELETE FROM ".TB_PREF."gl_trans WHERE tran_date <= '$to' AND account = '{$row['account']}'"; + db_query($sql, "Could not delete gl trans"); + if (is_account_balancesheet($row['account'])) + { + $trans_no = get_next_trans_no(0); + if (is_bank_account($row['account'])) + { + $sql = "SELECT SUM(amount) FROM ".TB_PREF."bank_trans WHERE trans_date <= '$to' AND bank_act = '{$row['account']}'"; + $res = db_query($sql, "Could not retrieve bank trans"); + $row2 = db_fetch_row($res); + $sql = "DELETE FROM ".TB_PREF."bank_trans WHERE trans_date <= '$to' AND bank_act = '{$row['account']}'"; + db_query($sql, "Could not delete bank trans"); + $sql = "INSERT INTO ".TB_PREF."bank_trans (type, trans_no, trans_date, bank_act, ref, amount) VALUES + (0, $trans_no, '$to', '{$row['account']}', '$ref', {$row2[0]})"; + db_query($sql, "Could not insert bank trans"); + } + $sql = "INSERT INTO ".TB_PREF."gl_trans (type, type_no, tran_date, account, memo_, amount) VALUES + (0, $trans_no, '$to', '{$row['account']}', '$ref', {$row['amount']})"; + db_query($sql, "Could not insert gl trans"); + } + } + delete_fiscalyear($selected_id); + commit_transaction(); +} + +?> \ No newline at end of file diff --git a/admin/db/maintenance_db.inc b/admin/db/maintenance_db.inc index ba695a30..8ddbaebc 100644 --- a/admin/db/maintenance_db.inc +++ b/admin/db/maintenance_db.inc @@ -56,6 +56,12 @@ function array_natsort($aryData, $strIndex, $strSortBy, $strSortType=false) return $aryResult; } +function update_admin_password($conn, $password) +{ + $sql = "UPDATE ".$conn['tbpref']."users SET password=".db_escape($password) . " + WHERE user_id='admin'"; + db_query($sql, "could not update user password for 'admin'"); +} function write_config_db($new = false) { diff --git a/admin/db/printers_db.inc b/admin/db/printers_db.inc index c1d80368..b873ee2e 100644 --- a/admin/db/printers_db.inc +++ b/admin/db/printers_db.inc @@ -41,6 +41,11 @@ function get_printer($id) return db_fetch($result); } +function delete_printer($id) +{ + $sql="DELETE FROM ".TB_PREF."printers WHERE id=".db_escape($id); + db_query($sql,"could not delete printer definition"); +} //============================================================================ // printer profiles functions // @@ -101,4 +106,12 @@ function get_print_profile($name) return db_query($sql,"could not get printing profile"); } +function printer_has_profile($id) +{ + $sql= "SELECT COUNT(*) FROM ".TB_PREF."print_profiles WHERE printer = ".db_escape($id); + $result = db_query($sql,"check printers relations failed"); + $myrow = db_fetch_row($result); + return ($myrow[0] > 0); +} + ?> \ No newline at end of file diff --git a/admin/db/shipping_db.inc b/admin/db/shipping_db.inc new file mode 100644 index 00000000..b447426e --- /dev/null +++ b/admin/db/shipping_db.inc @@ -0,0 +1,93 @@ +. +***********************************************************************/ + +//-------------------------------------------------------------------------------------- + +function add_shipper($shipper_name, $contact, $phone, $phone2, $address) +{ + $sql = "INSERT INTO ".TB_PREF."shippers (shipper_name, contact, phone, phone2, address) + VALUES (" . db_escape($shipper_name) . ", " . + db_escape($contact). ", " . + db_escape($phone). ", " . + db_escape($phone2). ", " . + db_escape($address) . ")"; + + db_query($sql,"The Shipping Company could not be added"); +} + +//-------------------------------------------------------------------------------------- + +function update_shipper($selected_id, $shipper_name, $contact, $phone, $phone2, $address) +{ + $sql = "UPDATE ".TB_PREF."shippers SET shipper_name=" . db_escape($shipper_name). " , + contact =" . db_escape($contact). " , + phone =" . db_escape($phone). " , + phone2 =" . db_escape($phone2). " , + address =" . db_escape($address). " + WHERE shipper_id = ".db_escape($selected_id); + + db_query($sql,"The shipping company could not be updated"); +} + +//-------------------------------------------------------------------------------------- + +function delete_shipper($selected_id) +{ + $sql="DELETE FROM ".TB_PREF."shippers WHERE shipper_id=".db_escape($selected_id); + + db_query($sql,"could not delete shipper"); +} + +//-------------------------------------------------------------------------------------- + +function get_shippers($show_inactive) +{ + $sql = "SELECT * FROM ".TB_PREF."shippers"; + if (!$show_inactive) $sql .= " WHERE !inactive"; + $sql .= " ORDER BY shipper_id"; + + return db_query($sql,"could not get shippers"); +} + +//-------------------------------------------------------------------------------------- + +function get_shipper($selected_id) +{ + $sql = "SELECT * FROM ".TB_PREF."shippers WHERE shipper_id=".db_escape($selected_id); + + $result = db_query($sql, "could not get shipper"); + return db_fetch($result); +} + +//-------------------------------------------------------------------------------------- + +function sales_orders_has_shipper($selected_id) +{ + $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_orders WHERE ship_via=".db_escape($selected_id); + + $result = db_query($sql,"check failed"); + $myrow = db_fetch_row($result); + return ($myrow[0] > 0); +} + +//-------------------------------------------------------------------------------------- + +function debtor_trans_has_shipper($selected_id) +{ + $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtor_trans WHERE ship_via=".db_escape($selected_id); + + $result = db_query($sql,"check failed"); + $myrow = db_fetch_row($result); + return ($myrow[0] > 0); +} + +?> \ No newline at end of file diff --git a/admin/db/transactions_db.inc b/admin/db/transactions_db.inc new file mode 100644 index 00000000..09814938 --- /dev/null +++ b/admin/db/transactions_db.inc @@ -0,0 +1,43 @@ +. +***********************************************************************/ + +//-------------------------------------------------------------------------------------- + +function get_sql_for_view_transactions($filtertype, $from, $to, &$trans_ref) +{ + $db_info = get_systype_db_info($filtertype); + + if ($db_info == null) + return ""; + + $table_name = $db_info[0]; + $type_name = $db_info[1]; + $trans_no_name = $db_info[2]; + $trans_ref = $db_info[3]; + + $sql = "SELECT DISTINCT $trans_no_name as trans_no"; + + if ($trans_ref) + $sql .= " ,$trans_ref "; + + $sql .= ", ".$filtertype." as type FROM $table_name + WHERE $trans_no_name >= ".db_escape($from). " + AND $trans_no_name <= ".db_escape($to); + + if ($type_name != null) + $sql .= " AND `$type_name` = ".db_escape($filtertype); + + $sql .= " ORDER BY $trans_no_name"; + return $sql; +} + +?> \ No newline at end of file diff --git a/admin/fiscalyears.php b/admin/fiscalyears.php index a974e4a2..1c674ff4 100644 --- a/admin/fiscalyears.php +++ b/admin/fiscalyears.php @@ -15,6 +15,7 @@ include_once($path_to_root . "/includes/session.inc"); include_once($path_to_root . "/includes/date_functions.inc"); include_once($path_to_root . "/admin/db/company_db.inc"); +include_once($path_to_root . "/admin/db/fiscalyears_db.inc"); include_once($path_to_root . "/includes/ui.inc"); include_once($path_to_root . "/sales/includes/db/cust_trans_db.inc"); include_once($path_to_root . "/admin/db/maintenance_db.inc"); @@ -26,40 +27,6 @@ page(_($help_context = "Fiscal Years"), false, false, "", $js); simple_page_mode(true); //--------------------------------------------------------------------------------------------- -function is_date_in_fiscalyears($date) -{ - $date = date2sql($date); - $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE '$date' >= begin AND '$date' <= end"; - - $result = db_query($sql, "could not get all fiscal years"); - return db_fetch($result) !== false; -} - -function is_bad_begin_date($date) -{ - $bdate = date2sql($date); - $sql = "SELECT MAX(end) FROM ".TB_PREF."fiscal_year WHERE begin < '$bdate'"; - - $result = db_query($sql, "could not retrieve last fiscal years"); - $row = db_fetch_row($result); - if ($row[0] === null) - return false; - $max = add_days(sql2date($row[0]), 1); - return ($max !== $date); -} - -function check_years_before($date, $closed=false) -{ - $date = date2sql($date); - $sql = "SELECT COUNT(*) FROM ".TB_PREF."fiscal_year WHERE begin < '$date'"; - if (!$closed) - $sql .= " AND closed=0"; - - $result = db_query($sql, "could not check fiscal years before"); - $row = db_fetch_row($result); - return ($row[0] > 0); -} - function check_data() { if (!is_date($_POST['from_date']) || is_date_in_fiscalyears($_POST['from_date']) || is_bad_begin_date($_POST['from_date'])) @@ -82,56 +49,6 @@ function check_data() } return true; } -//--------------------------------------------------------------------------------------------- -function close_year($year) -{ - $co = get_company_prefs(); - if (get_gl_account($co['retained_earnings_act']) == false || get_gl_account($co['profit_loss_year_act']) == false) - { - display_error(_("The Retained Earnings Account or the Profit and Loss Year Account has not been set in System and General GL Setup")); - return false; - } - begin_transaction(); - - $myrow = get_fiscalyear($year); - $to = $myrow['end']; - // retrieve total balances from balance sheet accounts - $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans INNER JOIN ".TB_PREF."chart_master ON account=account_code - INNER JOIN ".TB_PREF."chart_types ON account_type=id INNER JOIN ".TB_PREF."chart_class ON class_id=cid - WHERE ctype>=".CL_ASSETS." AND ctype <=".CL_EQUITY." AND tran_date <= '$to'"; - $result = db_query($sql, "The total balance could not be calculated"); - - $row = db_fetch_row($result); - $balance = round2($row[0], user_price_dec()); - - $to = sql2date($to); - - if ($balance != 0.0) - { - $trans_type = ST_JOURNAL; - $trans_id = get_next_trans_no($trans_type); - - add_gl_trans($trans_type, $trans_id, $to, $co['retained_earnings_act'], - 0, 0, _("Closing Year"), -$balance); - add_gl_trans($trans_type, $trans_id, $to, $co['profit_loss_year_act'], - 0, 0, _("Closing Year"), $balance); - - } - close_transactions($to); - - commit_transaction(); - return true; -} - -function open_year($year) -{ - $myrow = get_fiscalyear($year); - $from = sql2date($myrow['begin']); - - begin_transaction(); - open_transactions($from); - commit_transaction(); -} function handle_submit() { @@ -187,185 +104,6 @@ function check_can_delete($selected_id) return true; } -//--------------------------------------------------------------------------------------------- -function delete_attachments_and_comments($type_no, $trans_no) -{ - global $comp_path; - - $sql = "SELECT * FROM ".TB_PREF."attachments WHERE type_no = $type_no AND trans_no = $trans_no"; - $result = db_query($sql, "Could not retrieve attachments"); - while ($row = db_fetch($result)) - { - $dir = $comp_path."/".user_company(). "/attachments"; - if (file_exists($dir."/".$row['unique_name'])) - unlink($dir."/".$row['unique_name']); - $sql = "DELETE FROM ".TB_PREF."attachments WHERE type_no = $type_no AND trans_no = $trans_no"; - db_query($sql, "Could not delete attachment"); - } - $sql = "DELETE FROM ".TB_PREF."comments WHERE type = $type_no AND id = $trans_no"; - db_query($sql, "Could not delete comments"); -} - -function delete_this_fiscalyear($selected_id) -{ - global $db_connections; - - db_backup($db_connections[$_SESSION["wa_current_user"]->company], 'Security backup before Fiscal Year Removal'); - begin_transaction(); - $ref = _("Open Balance"); - $myrow = get_fiscalyear($selected_id); - $to = $myrow['end']; - $sql = "SELECT order_no, trans_type FROM ".TB_PREF."sales_orders WHERE ord_date <= '$to' AND type <> 1"; // don't take the templates - $result = db_query($sql, "Could not retrieve sales orders"); - while ($row = db_fetch($result)) - { - $sql = "SELECT SUM(qty_sent), SUM(quantity) FROM ".TB_PREF."sales_order_details WHERE order_no = {$row['order_no']} AND trans_type = {$row['trans_type']}"; - $res = db_query($sql, "Could not retrieve sales order details"); - $row2 = db_fetch_row($res); - if ($row2[0] == $row2[1]) - { - $sql = "DELETE FROM ".TB_PREF."sales_order_details WHERE order_no = {$row['order_no']} AND trans_type = {$row['trans_type']}"; - db_query($sql, "Could not delete sales order details"); - $sql = "DELETE FROM ".TB_PREF."sales_orders WHERE order_no = {$row['order_no']} AND trans_type = {$row['trans_type']}"; - db_query($sql, "Could not delete sales order"); - delete_attachments_and_comments($row['trans_type'], $row['order_no']); - } - } - $sql = "SELECT order_no FROM ".TB_PREF."purch_orders WHERE ord_date <= '$to'"; - $result = db_query($sql, "Could not retrieve purchase orders"); - while ($row = db_fetch($result)) - { - $sql = "SELECT SUM(quantity_ordered), SUM(quantity_received) FROM ".TB_PREF."purch_order_details WHERE order_no = {$row['order_no']}"; - $res = db_query($sql, "Could not retrieve purchase order details"); - $row2 = db_fetch_row($res); - if ($row2[0] == $row2[1]) - { - $sql = "DELETE FROM ".TB_PREF."purch_order_details WHERE order_no = {$row['order_no']}"; - db_query($sql, "Could not delete purchase order details"); - $sql = "DELETE FROM ".TB_PREF."purch_orders WHERE order_no = {$row['order_no']}"; - db_query($sql, "Could not delete purchase order"); - delete_attachments_and_comments(ST_PURCHORDER, $row['order_no']); - } - } - $sql = "SELECT id FROM ".TB_PREF."grn_batch WHERE delivery_date <= '$to'"; - $result = db_query($sql, "Could not retrieve grn batch"); - while ($row = db_fetch($result)) - { - $sql = "DELETE FROM ".TB_PREF."grn_items WHERE grn_batch_id = {$row['id']}"; - db_query($sql, "Could not delete grn items"); - $sql = "DELETE FROM ".TB_PREF."grn_batch WHERE id = {$row['id']}"; - db_query($sql, "Could not delete grn batch"); - delete_attachments_and_comments(25, $row['id']); - } - $sql = "SELECT trans_no, type FROM ".TB_PREF."debtor_trans WHERE tran_date <= '$to' AND - (ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) = alloc"; - $result = db_query($sql, "Could not retrieve debtor trans"); - while ($row = db_fetch($result)) - { - if ($row['type'] == ST_SALESINVOICE) - { - $deliveries = get_parent_trans(ST_SALESINVOICE,$row['trans_no']); - foreach ($deliveries as $delivery) - { - $sql = "DELETE FROM ".TB_PREF."debtor_trans_details WHERE debtor_trans_no = $delivery AND debtor_trans_type = ".ST_CUSTDELIVERY; - db_query($sql, "Could not delete debtor trans details"); - $sql = "DELETE FROM ".TB_PREF."debtor_trans WHERE trans_no = $delivery AND type = ".ST_CUSTDELIVERY; - db_query($sql, "Could not delete debtor trans"); - } - } - $sql = "DELETE FROM ".TB_PREF."cust_allocations WHERE trans_no_from = {$row['trans_no']} AND type_no_from = {$row['type']}"; - db_query($sql, "Could not delete cust allocations"); - $sql = "DELETE FROM ".TB_PREF."debtor_trans_details WHERE debtor_trans_no = {$row['trans_no']} AND debtor_trans_type = {$row['type']}"; - db_query($sql, "Could not delete debtor trans details"); - $sql = "DELETE FROM ".TB_PREF."debtor_trans WHERE trans_no = {$row['trans_no']} AND type = {$row['type']}"; - db_query($sql, "Could not delete debtor trans"); - delete_attachments_and_comments($row['type'], $row['trans_no']); - } - $sql = "SELECT trans_no, type FROM ".TB_PREF."supp_trans WHERE tran_date <= '$to' AND - ABS(ov_amount + ov_gst + ov_discount) = alloc"; - $result = db_query($sql, "Could not retrieve supp trans"); - while ($row = db_fetch($result)) - { - $sql = "DELETE FROM ".TB_PREF."supp_allocations WHERE trans_no_from = {$row['trans_no']} AND type_no_from = {$row['type']}"; - db_query($sql, "Could not delete supp allocations"); - $sql = "DELETE FROM ".TB_PREF."supp_invoice_items WHERE supp_trans_no = {$row['trans_no']} AND supp_trans_type = {$row['type']}"; - db_query($sql, "Could not delete supp invoice items"); - $sql = "DELETE FROM ".TB_PREF."supp_trans WHERE trans_no = {$row['trans_no']} AND type = {$row['type']}"; - db_query($sql, "Could not delete supp trans"); - delete_attachments_and_comments($row['type'], $row['trans_no']); - } - $sql = "SELECT id FROM ".TB_PREF."workorders WHERE released_date <= '$to' AND closed=1"; - $result = db_query($sql, "Could not retrieve supp trans"); - while ($row = db_fetch($result)) - { - $sql = "SELECT issue_no FROM ".TB_PREF."wo_issues WHERE workorder_id = {$row['id']}"; - $res = db_query($sql, "Could not retrieve wo issues"); - while ($row2 = db_fetch_row($res)) - { - $sql = "DELETE FROM ".TB_PREF."wo_issue_items WHERE issue_id = {$row2[0]}"; - db_query($sql, "Could not delete wo issue items"); - } - delete_attachments_and_comments(28, $row['id']); - $sql = "DELETE FROM ".TB_PREF."wo_issues WHERE workorder_id = {$row['id']}"; - db_query($sql, "Could not delete wo issues"); - $sql = "DELETE FROM ".TB_PREF."wo_manufacture WHERE workorder_id = {$row['id']}"; - db_query($sql, "Could not delete wo manufacture"); - $sql = "DELETE FROM ".TB_PREF."wo_requirements WHERE workorder_id = {$row['id']}"; - db_query($sql, "Could not delete wo requirements"); - $sql = "DELETE FROM ".TB_PREF."workorders WHERE id = {$row['id']}"; - db_query($sql, "Could not delete workorders"); - delete_attachments_and_comments(26, $row['id']); - } - $sql = "SELECT loc_code, stock_id, SUM(qty) AS qty, SUM(qty*standard_cost) AS std_cost FROM ".TB_PREF."stock_moves WHERE tran_date <= '$to' GROUP by - loc_code, stock_id"; - $result = db_query($sql, "Could not retrieve supp trans"); - while ($row = db_fetch($result)) - { - $sql = "DELETE FROM ".TB_PREF."stock_moves WHERE tran_date <= '$to' AND loc_code = '{$row['loc_code']}' AND stock_id = '{$row['stock_id']}'"; - db_query($sql, "Could not delete stock moves"); - $qty = $row['qty']; - $std_cost = ($qty == 0 ? 0 : round2($row['std_cost'] / $qty, user_price_dec())); - $sql = "INSERT INTO ".TB_PREF."stock_moves (stock_id, loc_code, tran_date, reference, qty, standard_cost) VALUES - ('{$row['stock_id']}', '{$row['loc_code']}', '$to', '$ref', $qty, $std_cost)"; - db_query($sql, "Could not insert stock move"); - } - $sql = "DELETE FROM ".TB_PREF."voided WHERE date_ <= '$to'"; - db_query($sql, "Could not delete voided items"); - $sql = "DELETE FROM ".TB_PREF."trans_tax_details WHERE tran_date <= '$to'"; - db_query($sql, "Could not delete trans tax details"); - $sql = "DELETE FROM ".TB_PREF."exchange_rates WHERE date_ <= '$to'"; - db_query($sql, "Could not delete exchange rates"); - $sql = "DELETE FROM ".TB_PREF."budget_trans WHERE tran_date <= '$to'"; - db_query($sql, "Could not delete exchange rates"); - $sql = "SELECT account, SUM(amount) AS amount FROM ".TB_PREF."gl_trans WHERE tran_date <= '$to' GROUP by account"; - $result = db_query($sql, "Could not retrieve gl trans"); - while ($row = db_fetch($result)) - { - $sql = "DELETE FROM ".TB_PREF."gl_trans WHERE tran_date <= '$to' AND account = '{$row['account']}'"; - db_query($sql, "Could not delete gl trans"); - if (is_account_balancesheet($row['account'])) - { - $trans_no = get_next_trans_no(0); - if (is_bank_account($row['account'])) - { - $sql = "SELECT SUM(amount) FROM ".TB_PREF."bank_trans WHERE trans_date <= '$to' AND bank_act = '{$row['account']}'"; - $res = db_query($sql, "Could not retrieve bank trans"); - $row2 = db_fetch_row($res); - $sql = "DELETE FROM ".TB_PREF."bank_trans WHERE trans_date <= '$to' AND bank_act = '{$row['account']}'"; - db_query($sql, "Could not delete bank trans"); - $sql = "INSERT INTO ".TB_PREF."bank_trans (type, trans_no, trans_date, bank_act, ref, amount) VALUES - (0, $trans_no, '$to', '{$row['account']}', '$ref', {$row2[0]})"; - db_query($sql, "Could not insert bank trans"); - } - $sql = "INSERT INTO ".TB_PREF."gl_trans (type, type_no, tran_date, account, memo_, amount) VALUES - (0, $trans_no, '$to', '{$row['account']}', '$ref', {$row['amount']})"; - db_query($sql, "Could not insert gl trans"); - } - } - delete_fiscalyear($selected_id); - commit_transaction(); -} - function handle_delete() { global $selected_id, $Mode; diff --git a/admin/gl_setup.php b/admin/gl_setup.php index 85f5b843..48e67c4b 100644 --- a/admin/gl_setup.php +++ b/admin/gl_setup.php @@ -128,11 +128,6 @@ $_POST['default_delivery_required'] = $myrow['default_delivery_required']; table_section_title(_("General GL")); -// Not used in FA2.0. -//gl_all_accounts_list_row(_("Retained Earning Clearing Account:"), 'retained_earnings_act', $_POST['retained_earnings_act']); -// Not used in FA2.0. -//gl_all_accounts_list_row(_("Payroll Account:"), 'payroll_act', $_POST['payroll_act']); - text_row(_("Past Due Days Interval:"), 'past_due_days', $_POST['past_due_days'], 6, 6, '', "", _("days")); gl_all_accounts_list_row(_("Retained Earnings:"), 'retained_earnings_act', $_POST['retained_earnings_act']); diff --git a/admin/payment_terms.php b/admin/payment_terms.php index 1e0f8c65..c6a98794 100644 --- a/admin/payment_terms.php +++ b/admin/payment_terms.php @@ -57,43 +57,15 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') { if ($selected_id != -1) { - if (check_value('DaysOrFoll')) - { - $sql = "UPDATE ".TB_PREF."payment_terms SET terms=" . db_escape($_POST['terms']) . ", - day_in_following_month=0, - days_before_due=" . db_escape($_POST['DayNumber']) . " - WHERE terms_indicator = " .db_escape($selected_id); - } - else - { - $sql = "UPDATE ".TB_PREF."payment_terms SET terms=" . db_escape($_POST['terms']) . ", - day_in_following_month=" . db_escape($_POST['DayNumber']) . ", - days_before_due=0 - WHERE terms_indicator = " .db_escape( $selected_id ); - } + update_payment_terms($selected_id, check_value('DaysOrFoll'), $_POST['terms'], $_POST['DayNumber']); $note = _('Selected payment terms have been updated'); } else { - - if (check_value('DaysOrFoll')) - { - $sql = "INSERT INTO ".TB_PREF."payment_terms (terms, - days_before_due, day_in_following_month) - VALUES (" . - db_escape($_POST['terms']) . ", " . db_escape($_POST['DayNumber']) . ", 0)"; - } - else - { - $sql = "INSERT INTO ".TB_PREF."payment_terms (terms, - days_before_due, day_in_following_month) - VALUES (" . db_escape($_POST['terms']) . ", - 0, " . db_escape($_POST['DayNumber']) . ")"; - } + add_payment_terms(check_value('DaysOrFoll'), $_POST['terms'], $_POST['DayNumber']); $note = _('New payment terms have been added'); } //run the sql from either of the above possibilites - db_query($sql,"The payment term could not be added or updated"); display_notification($note); $Mode = 'RESET'; } @@ -102,29 +74,20 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') if ($Mode == 'Delete') { // PREVENT DELETES IF DEPENDENT RECORDS IN debtors_master - - $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtors_master WHERE payment_terms = ".db_escape($selected_id); - $result = db_query($sql,"check failed"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (customer_has_terms($selected_id)) { display_error(_("Cannot delete this payment term, because customer accounts have been created referring to this term.")); } else { - $sql= "SELECT COUNT(*) FROM ".TB_PREF."suppliers WHERE payment_terms = ".db_escape($selected_id); - $result = db_query($sql,"check failed"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (supplier_has_terms($selected_id)) { display_error(_("Cannot delete this payment term, because supplier accounts have been created referring to this term")); } else { //only delete if used in neither customer or supplier accounts - - $sql="DELETE FROM ".TB_PREF."payment_terms WHERE terms_indicator=".db_escape($selected_id); - db_query($sql,"could not delete a payment terms"); + delete_payment_terms($selected_id); display_notification(_('Selected payment terms have been deleted')); } } @@ -141,9 +104,7 @@ if ($Mode == 'RESET') } //------------------------------------------------------------------------------------------------- -$sql = "SELECT * FROM ".TB_PREF."payment_terms"; -if (!check_value('show_inactive')) $sql .= " WHERE !inactive"; -$result = db_query($sql,"could not get payment terms"); +$result = get_payment_terms_all(check_value('show_inactive')); start_form(); start_table($table_style); @@ -197,11 +158,7 @@ if ($selected_id != -1) { if ($Mode == 'Edit') { //editing an existing payment terms - $sql = "SELECT * FROM ".TB_PREF."payment_terms - WHERE terms_indicator=".db_escape($selected_id); - - $result = db_query($sql,"could not get payment term"); - $myrow = db_fetch($result); + $myrow = get_payment_terms($selected_id); $_POST['terms'] = $myrow["terms"]; $days_before_due = $myrow["days_before_due"]; diff --git a/admin/printers.php b/admin/printers.php index d46afeeb..cd44a8e1 100644 --- a/admin/printers.php +++ b/admin/printers.php @@ -59,18 +59,14 @@ if ($Mode == 'Delete') { // PREVENT DELETES IF DEPENDENT RECORDS IN print_profiles - $sql= "SELECT COUNT(*) FROM ".TB_PREF."print_profiles WHERE printer = ".db_escape($selected_id); - $result = db_query($sql,"check printers relations failed"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (printer_has_profile($selected_id)) { display_error(_("Cannot delete this printer definition, because print profile have been created using it.")); } else { - $sql="DELETE FROM ".TB_PREF."printers WHERE id=".db_escape($selected_id); - db_query($sql,"could not delete printer definition"); - display_notification(_('Selected printer definition has been deleted')); + delete_printer($selected_id); + display_notification(_('Selected printer definition has been deleted')); } $Mode = 'RESET'; } diff --git a/admin/shipping_companies.php b/admin/shipping_companies.php index 19f034b7..9422d846 100644 --- a/admin/shipping_companies.php +++ b/admin/shipping_companies.php @@ -14,6 +14,7 @@ $path_to_root=".."; include($path_to_root . "/includes/session.inc"); page(_($help_context = "Shipping Company")); include($path_to_root . "/includes/ui.inc"); +include($path_to_root . "/admin/db/shipping_db.inc"); simple_page_mode(true); //---------------------------------------------------------------------------------------------- @@ -32,15 +33,7 @@ function can_process() //---------------------------------------------------------------------------------------------- if ($Mode=='ADD_ITEM' && can_process()) { - - $sql = "INSERT INTO ".TB_PREF."shippers (shipper_name, contact, phone, phone2, address) - VALUES (" . db_escape($_POST['shipper_name']) . ", " . - db_escape($_POST['contact']). ", " . - db_escape($_POST['phone']). ", " . - db_escape($_POST['phone2']). ", " . - db_escape($_POST['address']) . ")"; - - db_query($sql,"The Shipping Company could not be added"); + add_shipper($_POST['shipper_name'], $_POST['contact'], $_POST['phone'], $_POST['phone2'], $_POST['address']); display_notification(_('New shipping company has been added')); $Mode = 'RESET'; } @@ -49,15 +42,7 @@ if ($Mode=='ADD_ITEM' && can_process()) if ($Mode=='UPDATE_ITEM' && can_process()) { - - $sql = "UPDATE ".TB_PREF."shippers SET shipper_name=" . db_escape($_POST['shipper_name']). " , - contact =" . db_escape($_POST['contact']). " , - phone =" . db_escape($_POST['phone']). " , - phone2 =" . db_escape($_POST['phone2']). " , - address =" . db_escape($_POST['address']). " - WHERE shipper_id = ".db_escape($selected_id); - - db_query($sql,"The shipping company could not be updated"); + update_shipper($selected_id, $_POST['shipper_name'], $_POST['contact'], $_POST['phone'], $_POST['phone2'], $_POST['address']); display_notification(_('Selected shipping company has been updated')); $Mode = 'RESET'; } @@ -68,10 +53,7 @@ if ($Mode == 'Delete') { // PREVENT DELETES IF DEPENDENT RECORDS IN 'sales_orders' - $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_orders WHERE ship_via=".db_escape($selected_id); - $result = db_query($sql,"check failed"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (sales_orders_has_shipper($selected_id)) { $cancel_delete = 1; display_error(_("Cannot delete this shipping company because sales orders have been created using this shipper.")); @@ -80,18 +62,14 @@ if ($Mode == 'Delete') { // PREVENT DELETES IF DEPENDENT RECORDS IN 'debtor_trans' - $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtor_trans WHERE ship_via=".db_escape($selected_id); - $result = db_query($sql,"check failed"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (debtor_trans_has_shipper($selected_id)) { $cancel_delete = 1; display_error(_("Cannot delete this shipping company because invoices have been created using this shipping company.")); } else { - $sql="DELETE FROM ".TB_PREF."shippers WHERE shipper_id=".db_escape($selected_id); - db_query($sql,"could not delete shipper"); + delete_shipper($selected_id); display_notification(_('Selected shipping company has been deleted')); } } @@ -107,10 +85,7 @@ if ($Mode == 'RESET') } //---------------------------------------------------------------------------------------------- -$sql = "SELECT * FROM ".TB_PREF."shippers"; -if (!check_value('show_inactive')) $sql .= " WHERE !inactive"; -$sql .= " ORDER BY shipper_id"; -$result = db_query($sql,"could not get shippers"); +$result = get_shippers(check_value('show_inactive')); start_form(); start_table($table_style); @@ -146,10 +121,7 @@ if ($selected_id != -1) if ($Mode == 'Edit') { //editing an existing Shipper - $sql = "SELECT * FROM ".TB_PREF."shippers WHERE shipper_id=".db_escape($selected_id); - - $result = db_query($sql, "could not get shipper"); - $myrow = db_fetch($result); + $myrow = get_shipper($selected_id); $_POST['shipper_name'] = $myrow["shipper_name"]; $_POST['contact'] = $myrow["contact"]; diff --git a/admin/view_print_transaction.php b/admin/view_print_transaction.php index b3e35f62..65bc42d4 100644 --- a/admin/view_print_transaction.php +++ b/admin/view_print_transaction.php @@ -18,6 +18,7 @@ include_once($path_to_root . "/includes/session.inc"); include_once($path_to_root . "/includes/date_functions.inc"); include_once($path_to_root . "/includes/ui.inc"); include_once($path_to_root . "/includes/data_checks.inc"); +include_once($path_to_root . "/admin/db/transactions_db.inc"); include_once($path_to_root . "/reporting/includes/reporting.inc"); $js = ""; @@ -93,31 +94,11 @@ function handle_search() global $table_style; if (check_valid_entries()==true) { - $db_info = get_systype_db_info($_POST['filterType']); - - if ($db_info == null) + $trans_ref = false; + $sql = get_sql_for_view_transactions($_POST['filterType'], $_POST['FromTransNo'], $_POST['ToTransNo'], $trans_ref); + if ($sql == "") return; - $table_name = $db_info[0]; - $type_name = $db_info[1]; - $trans_no_name = $db_info[2]; - $trans_ref = $db_info[3]; - - $sql = "SELECT DISTINCT $trans_no_name as trans_no"; - - if ($trans_ref) - $sql .= " ,$trans_ref "; - - $sql .= ", ".$_POST['filterType']." as type FROM $table_name - WHERE $trans_no_name >= ".db_escape($_POST['FromTransNo']). " - AND $trans_no_name <= ".db_escape($_POST['ToTransNo']); - - if ($type_name != null) - $sql .= " AND `$type_name` = ".db_escape($_POST['filterType']); - - $sql .= " ORDER BY $trans_no_name"; - - $print_type = $_POST['filterType']; $print_out = ($print_type == ST_SALESINVOICE || $print_type == ST_CUSTCREDIT || $print_type == ST_CUSTDELIVERY || $print_type == ST_PURCHORDER || $print_type == ST_SALESORDER || $print_type == ST_SALESQUOTE); diff --git a/includes/date_functions.inc b/includes/date_functions.inc index 13294eb0..182e09c8 100644 --- a/includes/date_functions.inc +++ b/includes/date_functions.inc @@ -160,7 +160,7 @@ function new_doc_date($date=null) function is_date_in_fiscalyear($date, $convert=false) { global $path_to_root; - include_once($path_to_root . "/admin/db/company_db.inc"); + include_once($path_to_root . "/admin/db/fiscalyears_db.inc"); $myrow = get_current_fiscalyear(); if ($myrow['closed'] == 1) @@ -181,7 +181,7 @@ function is_date_in_fiscalyear($date, $convert=false) function begin_fiscalyear() { global $path_to_root; - include_once($path_to_root . "/admin/db/company_db.inc"); + include_once($path_to_root . "/admin/db/fiscalyears_db.inc"); $myrow = get_current_fiscalyear(); return sql2date($myrow['begin']); @@ -190,7 +190,7 @@ function begin_fiscalyear() function end_fiscalyear() { global $path_to_root; - include_once($path_to_root . "/admin/db/company_db.inc"); + include_once($path_to_root . "/admin/db/fiscalyears_db.inc"); $myrow = get_current_fiscalyear(); return sql2date($myrow['end']); diff --git a/lang/en_US/LC_MESSAGES/en_US.mo b/lang/en_US/LC_MESSAGES/en_US.mo index f499b8b4bc844c5823caa5220f5399547f0c96e4..37d63d9a2dca71a5871dfb95bc7716742024f051 100644 GIT binary patch delta 38 kcmZo=Z)M*Q#K>i+YiOZhXlP|%Iyr_>4vD+Dg^`g70IIAANdN!< delta 38 kcmZo=Z)M*Q#K>i!Yht<8 diff --git a/reporting/includes/excel_report.inc b/reporting/includes/excel_report.inc index fb7ed1be..f2a36b2f 100644 --- a/reporting/includes/excel_report.inc +++ b/reporting/includes/excel_report.inc @@ -11,6 +11,7 @@ ***********************************************************************/ include_once($path_to_root . "/reporting/includes/Workbook.php"); include_once($path_to_root . "/admin/db/company_db.inc"); +include_once($path_to_root . "/admin/db/fiscalyears_db.inc"); include_once($path_to_root . "/config.php"); // xls version class FrontReport extends Spreadsheet_Excel_Writer_Workbook diff --git a/reporting/includes/pdf_report.inc b/reporting/includes/pdf_report.inc index 944b4c28..03eb81f4 100644 --- a/reporting/includes/pdf_report.inc +++ b/reporting/includes/pdf_report.inc @@ -13,6 +13,7 @@ include_once(dirname(__FILE__)."/class.pdf.inc"); include_once(dirname(__FILE__)."/printer_class.inc"); include_once($path_to_root . "/admin/db/company_db.inc"); +include_once($path_to_root . "/admin/db/fiscalyears_db.inc"); include_once($path_to_root . "/admin/db/printers_db.inc"); include_once($path_to_root . "/config.php"); class FrontReport extends Cpdf -- 2.30.2