From 963bc21ea092322d5750d4c0914b5e532e7d2467 Mon Sep 17 00:00:00 2001 From: Joe Hunt Date: Sat, 5 Dec 2009 09:27:17 +0000 Subject: [PATCH] Moved all SQL statements from PHP files into relevant *_db.inc files. All folders done, except reporting/repXXX.php and reporting/includes/reports_classes.inc Awaiting change in report mechanism --- CHANGELOG.txt | 37 ++++ includes/ui/allocation_cart.inc | 3 +- reporting/includes/header2.inc | 9 +- reporting/includes/reports_classes.inc | 2 - sales/create_recurrent_invoices.php | 28 +-- sales/customer_payments.php | 12 +- sales/includes/db/branches_db.inc | 118 +++++++++++++ sales/includes/db/cust_trans_db.inc | 141 ++++++++++++++++ sales/includes/db/custalloc_db.inc | 68 ++++++++ sales/includes/db/customers_db.inc | 72 ++++++-- sales/includes/db/recurrent_invoices_db.inc | 66 ++++++++ sales/includes/db/sales_groups_db.inc | 159 ++++++++++++++++++ sales/includes/db/sales_order_db.inc | 74 ++++++++ sales/includes/sales_db.inc | 2 + sales/inquiry/customer_allocation_inquiry.php | 66 +------- sales/inquiry/customer_inquiry.php | 70 +------- sales/inquiry/sales_deliveries_view.php | 57 +------ sales/inquiry/sales_orders_view.php | 68 +------- sales/manage/credit_status.php | 6 +- sales/manage/customer_branches.php | 112 +++--------- sales/manage/customers.php | 65 ++----- sales/manage/recurrent_invoices.php | 39 +---- sales/manage/sales_areas.php | 22 +-- sales/manage/sales_groups.php | 28 +-- sales/manage/sales_people.php | 40 +---- sales/manage/sales_points.php | 5 +- sales/manage/sales_types.php | 17 +- sales/view/view_sales_order.php | 9 +- taxes/db/tax_types_db.inc | 14 ++ taxes/item_tax_types.php | 5 +- taxes/tax_calc.inc | 12 -- taxes/tax_groups.php | 14 +- taxes/tax_types.php | 5 +- 33 files changed, 831 insertions(+), 614 deletions(-) create mode 100644 sales/includes/db/recurrent_invoices_db.inc create mode 100644 sales/includes/db/sales_groups_db.inc diff --git a/CHANGELOG.txt b/CHANGELOG.txt index 8fe90ab0..d719aef3 100644 --- a/CHANGELOG.txt +++ b/CHANGELOG.txt @@ -19,6 +19,43 @@ Legend: ! -> Note $ -> Affected files +05-Dec-2009 Joe Hunt +! Moved all SQL statements from PHP files into relevant *_db.inc files. + All folders done, except reporting/repXXX.php and reporting/includes/reports_classes.inc + Awaiting change in report mechanism +$ /includes/ui/allocation_cart.inc + /reporting/includes/header2.inc + /reporting/includes/reports_classes.inc (partly) + /sales/create_recurrent_invoices.php + /sales/customer_payments.php + /sales/allocations/customer_allocate.php + /sales/includes/sales_db.inc + /sales/includes/db/branches_db.inc + /sales/includes/db/custalloc_db.inc + /sales/includes/db/customers_db.inc + /sales/includes/db/cust_trans_db.inc + /sales/includes/db/recurrent_invoices_db.inc (new file) + /sales/includes/db/sales_groups_db.inc (new file) + /sales/includes/db/sales_order_db.inc + /sales/inquiry/customer_allocation_inquiry.php + /sales/inquiry/customer_inquiry.php + /sales/inquiry/sales_deliveries_view.php + /sales/inquiry/sales_orders_view.php + /sales/manage/credit_status.php + /sales/manage/customers.php + /sales/manage/customer_branches.php + /sales/manage/recurrent_invoices.php + /sales/manage/sales_areas.php + /sales/manage/sales_groups.php + /sales/manage/sales_people.php + /sales/manage/sales_points.php + /sales/manage/sales_types.php + /sales/view/view_sales_order.php + /taxes/item_tax_types.php + /taxes/tax_groups.php + /taxes/tax_types.php + /taxes/db/tax_types_db.inc + 04-Dec-2009 Janusz Dobrowolski + Next release upgrade skeleton files. $ /sql/alter2.3.php (new) diff --git a/includes/ui/allocation_cart.inc b/includes/ui/allocation_cart.inc index e6163392..6d081aef 100644 --- a/includes/ui/allocation_cart.inc +++ b/includes/ui/allocation_cart.inc @@ -192,8 +192,7 @@ class allocation exchange_variation($this->type, $this->trans_no, $alloc_item->type, $alloc_item->type_no, $this->date_, $alloc_item->current_allocated, - $sup ? PT_SUPPLIER - : PT_CUSTOMER); + $this->person_type ? PT_SUPPLIER : PT_CUSTOMER); ////////////////////////////////////////////////////////////// diff --git a/reporting/includes/header2.inc b/reporting/includes/header2.inc index c3fd180b..1a03c8b8 100644 --- a/reporting/includes/header2.inc +++ b/reporting/includes/header2.inc @@ -221,10 +221,11 @@ if ($branch != null) { $id = $branch['salesman']; - $sql = "SELECT salesman_name FROM ".TB_PREF."salesman WHERE salesman_code='$id'"; - $result = db_query($sql,"could not get sales person"); - $row = db_fetch($result); - $this->TextWrap($col, $this->row, $width, $row['salesman_name'], 'C'); + //$sql = "SELECT salesman_name FROM ".TB_PREF."salesman WHERE salesman_code='$id'"; + //$result = db_query($sql,"could not get sales person"); + //$row = db_fetch($result); + //$this->TextWrap($col, $this->row, $width, $row['salesman_name'], 'C'); + $this->TextWrap($col, $this->row, $width, get_salesman_name($id), 'C'); } elseif ($doctype == 26) $this->TextWrap($col, $this->row, $width, $wo_types_array[$myrow["type"]], 'C'); diff --git a/reporting/includes/reports_classes.inc b/reporting/includes/reports_classes.inc index e7df6a73..ec1d5911 100644 --- a/reporting/includes/reports_classes.inc +++ b/reporting/includes/reports_classes.inc @@ -230,8 +230,6 @@ class BoxReports return ""; case 'ACCOUNTS': // not used -// $sql = "SELECT id, name FROM ".TB_PREF."chart_types"; -// return combo_input($name, '', $sql, 'id', 'name',array('spec_option'=>_("No Account Group Filter"),'spec_id'=>ALL_NUMERIC)); return gl_account_types_list($name, null, _("No Account Group Filter"), true); case 'ACCOUNTS_NO_FILTER': // not used diff --git a/sales/create_recurrent_invoices.php b/sales/create_recurrent_invoices.php index dafc08ac..309f50f6 100644 --- a/sales/create_recurrent_invoices.php +++ b/sales/create_recurrent_invoices.php @@ -23,13 +23,6 @@ if ($use_popup_windows) page(_($help_context = "Create and Print Recurrent Invoices"), false, false, "", $js); -function set_last_sent($id, $date) -{ - $date = date2sql($date); - $sql = "UPDATE ".TB_PREF."recurrent_invoices SET last_sent='$date' WHERE id=".db_escape($id); - db_query($sql,"The recurrent invoice could not be updated or added"); -} - function create_recurrent_invoices($customer_id, $branch_id, $order_no, $tmpl_no) { global $Refs; @@ -55,17 +48,14 @@ function create_recurrent_invoices($customer_id, $branch_id, $order_no, $tmpl_no $cart->trans_type = ST_SALESINVOICE; $cart->reference = $Refs->get_next($cart->trans_type); $invno = $cart->write(1); - set_last_sent($tmpl_no, $cart->document_date); + update_last_sent_recurrent_invoice($tmpl_no, $cart->document_date); return $invno; } if (isset($_GET['recurrent'])) { $invs = array(); - $sql = "SELECT * FROM ".TB_PREF."recurrent_invoices WHERE id=".db_escape($_GET['recurrent']); - - $result = db_query($sql,"could not get recurrent invoice"); - $myrow = db_fetch($result); + $myrow = get_recurrent_invoice($_GET['recurrent']); if ($myrow['debtor_no'] == 0) { $cust = get_cust_branches_from_group($myrow['group_no']); @@ -96,17 +86,7 @@ if (isset($_GET['recurrent'])) } } -//------------------------------------------------------------------------------------------------- -function get_sales_group_name($group_no) -{ - $sql = "SELECT description FROM ".TB_PREF."groups WHERE id = ".db_escape($group_no); - $result = db_query($sql, "could not get group"); - $row = db_fetch($result); - return $row[0]; -} - -$sql = "SELECT * FROM ".TB_PREF."recurrent_invoices ORDER BY description, group_no, debtor_no"; -$result = db_query($sql,"could not get recurrent invoices"); +$result = get_recurrent_invoices(); start_table("$table_style width=70%"); $th = array(_("Description"), _("Template No"),_("Customer"),_("Branch")."/"._("Group"),_("Days"),_("Monthly"),_("Begin"),_("End"),_("Last Created"),""); @@ -164,7 +144,7 @@ if ($due) else display_note(_("No recurrent invoices are due."), 1, 0); -echo '
'; +br(); end_page(); ?> diff --git a/sales/customer_payments.php b/sales/customer_payments.php index 7170d4fd..9f4630a4 100644 --- a/sales/customer_payments.php +++ b/sales/customer_payments.php @@ -200,19 +200,11 @@ function read_customer_data() { global $Refs; - $sql = "SELECT ".TB_PREF."debtors_master.pymt_discount, - ".TB_PREF."credit_status.dissallow_invoices - FROM ".TB_PREF."debtors_master, ".TB_PREF."credit_status - WHERE ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id - AND ".TB_PREF."debtors_master.debtor_no = ".db_escape($_POST['customer_id']); - - $result = db_query($sql, "could not query customers"); - - $myrow = db_fetch($result); + $myrow = get_customer_habit($_POST['customer_id']); $_POST['HoldAccount'] = $myrow["dissallow_invoices"]; $_POST['pymt_discount'] = $myrow["pymt_discount"]; - $_POST['ref'] = $Refs->get_next(12); + $_POST['ref'] = $Refs->get_next(ST_CUSTPAYMENT); } //---------------------------------------------------------------------------------------------- diff --git a/sales/includes/db/branches_db.inc b/sales/includes/db/branches_db.inc index 97d6fa20..dc0cdbd2 100644 --- a/sales/includes/db/branches_db.inc +++ b/sales/includes/db/branches_db.inc @@ -9,6 +9,79 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the License here . ***********************************************************************/ +function add_branch($customer_id, $br_name, $br_ref, $br_address, $phone, $phone2, $fax, $contact_name, + $salesman, $area, $email, $tax_group_id, $sales_account, $sales_discount_account, $receivables_account, + $payment_discount_account, $default_location, $br_post_address, $disable_trans, $group_no, $default_ship_via, $notes) +{ + $sql = "INSERT INTO ".TB_PREF."cust_branch (debtor_no, br_name, branch_ref, br_address, + salesman, phone, phone2, fax, + contact_name, area, email, tax_group_id, sales_account, receivables_account, payment_discount_account, sales_discount_account, default_location, + br_post_address, disable_trans, group_no, default_ship_via, notes) + VALUES (".db_escape($customer_id). ",".db_escape($br_name) . ", " + .db_escape($br_ref) . ", " + .db_escape($br_address) . ", ".db_escape($salesman) . ", " + .db_escape($phone) . ", ".db_escape($phone2) . ", " + .db_escape($fax) . "," + .db_escape($contact_name) . ", ".db_escape($area) . "," + .db_escape($email) . ", ".db_escape($tax_group_id) . ", " + .db_escape($sales_account) . ", " + .db_escape($receivables_account) . ", " + .db_escape($payment_discount_account) . ", " + .db_escape($sales_discount_account) . ", " + .db_escape($default_location) . ", " + .db_escape($br_post_address) . "," + .db_escape($disable_trans) . ", " + .db_escape($group_no) . ", " + .db_escape($default_ship_via). ", " + .db_escape($notes) . ")"; + db_query($sql,"The branch record could not be added"); +} + +function update_branch($customer_id, $branch_code, $br_name, $br_ref, $br_address, $phone, $phone2, $fax, $contact_name, + $salesman, $area, $email, $tax_group_id, $sales_account, $sales_discount_account, $receivables_account, + $payment_discount_account, $default_location, $br_post_address, $disable_trans, $group_no, $default_ship_via, $notes) +{ + $sql = "UPDATE ".TB_PREF."cust_branch SET br_name = " . db_escape($br_name) . ", + branch_ref = " . db_escape($br_ref) . ", + br_address = ".db_escape($br_address). ", + phone=".db_escape($phone). ", + phone2=".db_escape($phone2). ", + fax=".db_escape($fax).", + contact_name=".db_escape($contact_name) . ", + salesman= ".db_escape($salesman) . ", + area=".db_escape($area) . ", + email=".db_escape($email) . ", + tax_group_id=".db_escape($tax_group_id). ", + sales_account=".db_escape($sales_account) . ", + sales_discount_account=".db_escape($sales_discount_account) . ", + receivables_account=".db_escape($receivables_account) . ", + payment_discount_account=".db_escape($payment_discount_account) . ", + default_location=".db_escape($default_location) . ", + br_post_address =".db_escape($br_post_address) . ", + disable_trans=".db_escape($disable_trans) . ", + group_no=".db_escape($group_no) . ", + default_ship_via=".db_escape($default_ship_via) . ", + notes=".db_escape($notes) . " + WHERE branch_code =".db_escape($branch_code) . " + AND debtor_no=".db_escape($customer_id); + db_query($sql,"The branch record could not be updated"); +} + +function delete_branch($customer_id, $branch_code) +{ + $sql="DELETE FROM ".TB_PREF."cust_branch WHERE branch_code=".db_escape($branch_code)." AND debtor_no=".db_escape($customer_id); + db_query($sql,"could not delete branch"); +} + +function branch_in_foreign_table($customer_id, $branch_code, $table) +{ + $sql= "SELECT COUNT(*) FROM ".TB_PREF."$table WHERE branch_code=".db_escape($branch_code) + ." AND debtor_no = ".db_escape($customer_id); + $result = db_query($sql,"could not query $table"); + $myrow = db_fetch_row($result); + return ($myrow[0] > 0); +} + function get_branch($branch_id) { $sql = "SELECT ".TB_PREF."cust_branch.*,".TB_PREF."salesman.salesman_name @@ -21,6 +94,15 @@ function get_branch($branch_id) return db_fetch($result); } +function get_cust_branch($customer_id, $branch_code) +{ + $sql = "SELECT * FROM ".TB_PREF."cust_branch + WHERE branch_code=".db_escape($branch_code)." + AND debtor_no=".db_escape($customer_id); + $result = db_query($sql,"check failed"); + return db_fetch($result); +} + function get_branch_accounts($branch_id) { $sql = "SELECT receivables_account,sales_account, sales_discount_account, payment_discount_account @@ -49,4 +131,40 @@ function get_cust_branches_from_group($group_no) return db_query($sql,"could not retreive branches for group " . $group_no); } + +function get_default_info_for_branch($customer_id) +{ + $sql = "SELECT name, address, email, debtor_ref + FROM ".TB_PREF."debtors_master WHERE debtor_no = ".db_escape($customer_id); + $result = db_query($sql,"check failed"); + return db_fetch($result); +} +function get_sql_for_customer_branches() +{ + $sql = "SELECT " + ."b.branch_code, " + ."b.branch_ref, " + ."b.br_name, " + ."b.contact_name, " + ."s.salesman_name, " + ."a.description, " + ."b.phone, " + ."b.fax, " + ."b.email, " + ."t.name AS tax_group_name, " + ."b.inactive + FROM ".TB_PREF."cust_branch b, " + .TB_PREF."debtors_master c, " + .TB_PREF."areas a, " + .TB_PREF."salesman s, " + .TB_PREF."tax_groups t + WHERE b.debtor_no=c.debtor_no + AND b.tax_group_id=t.id + AND b.area=a.area_code + AND b.salesman=s.salesman_code + AND b.debtor_no = ".db_escape($_POST['customer_id']); + + if (!get_post('show_inactive')) $sql .= " AND !b.inactive"; + return $sql; +} ?> \ No newline at end of file diff --git a/sales/includes/db/cust_trans_db.inc b/sales/includes/db/cust_trans_db.inc index 2ab21e17..363a8f4c 100644 --- a/sales/includes/db/cust_trans_db.inc +++ b/sales/includes/db/cust_trans_db.inc @@ -247,6 +247,13 @@ function get_customer_trans_order($type, $type_no) //---------------------------------------------------------------------------------------- +function get_related_documents($type, $trans_no) +{ + $sql = "SELECT * FROM ".TB_PREF."debtor_trans WHERE type=$type AND order_=".db_escape($trans_no); + return db_query($sql,"The related documents could not be retreived"); +} +//---------------------------------------------------------------------------------------- + function get_customer_details_from_trans($type, $type_no) { $sql = "SELECT ".TB_PREF."debtors_master.name, ".TB_PREF."debtors_master.curr_code, ".TB_PREF."cust_branch.br_name @@ -299,4 +306,138 @@ function get_customer_trans_link($type, $type_no) } //---------------------------------------------------------------------------------------- +function get_sql_for_customer_inquiry() +{ + $date_after = date2sql($_POST['TransAfterDate']); + $date_to = date2sql($_POST['TransToDate']); + + $sql = "SELECT + trans.type, + trans.trans_no, + trans.order_, + trans.reference, + trans.tran_date, + trans.due_date, + debtor.name, + branch.br_name, + debtor.curr_code, + (trans.ov_amount + trans.ov_gst + trans.ov_freight + + trans.ov_freight_tax + trans.ov_discount) AS TotalAmount, "; + if ($_POST['filterType'] != ALL_TEXT) + $sql .= "@bal := @bal+(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount), "; + +// else +// $sql .= "IF(trans.type=".ST_CUSTDELIVERY.",'', IF(trans.type=".ST_SALESINVOICE." OR trans.type=".ST_BANKPAYMENT.",@bal := @bal+ +// (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount), @bal := @bal- +// (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount))) , "; + $sql .= "trans.alloc AS Allocated, + ((trans.type = ".ST_SALESINVOICE.") + AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue + FROM " + .TB_PREF."debtor_trans as trans, " + .TB_PREF."debtors_master as debtor, " + .TB_PREF."cust_branch as branch + WHERE debtor.debtor_no = trans.debtor_no + AND trans.tran_date >= '$date_after' + AND trans.tran_date <= '$date_to' + AND trans.branch_code = branch.branch_code"; + + if ($_POST['customer_id'] != ALL_TEXT) + $sql .= " AND trans.debtor_no = ".db_escape($_POST['customer_id']); + + if ($_POST['filterType'] != ALL_TEXT) + { + if ($_POST['filterType'] == '1') + { + $sql .= " AND (trans.type = ".ST_SALESINVOICE." OR trans.type = ".ST_BANKPAYMENT.") "; + } + elseif ($_POST['filterType'] == '2') + { + $sql .= " AND (trans.type = ".ST_SALESINVOICE.") "; + } + elseif ($_POST['filterType'] == '3') + { + $sql .= " AND (trans.type = " . ST_CUSTPAYMENT + ." OR trans.type = ".ST_BANKDEPOSIT.") "; + } + elseif ($_POST['filterType'] == '4') + { + $sql .= " AND trans.type = ".ST_CUSTCREDIT." "; + } + elseif ($_POST['filterType'] == '5') + { + $sql .= " AND trans.type = ".ST_CUSTDELIVERY." "; + } + + if ($_POST['filterType'] == '2') + { + $today = date2sql(Today()); + $sql .= " AND trans.due_date < '$today' + AND (trans.ov_amount + trans.ov_gst + trans.ov_freight_tax + + trans.ov_freight + trans.ov_discount - trans.alloc > 0) "; + } + } + return $sql; +} + +function get_sql_for_sales_deliveries_view($selected_customer, $selected_stock_id) +{ + $sql = "SELECT trans.trans_no, + debtor.name, + branch.branch_code, + branch.br_name, + sorder.deliver_to, + trans.reference, + sorder.customer_ref, + trans.tran_date, + trans.due_date, + (ov_amount+ov_gst+ov_freight+ov_freight_tax) AS DeliveryValue, + debtor.curr_code, + Sum(line.quantity-line.qty_done) AS Outstanding, + Sum(line.qty_done) AS Done + FROM " + .TB_PREF."sales_orders as sorder, " + .TB_PREF."debtor_trans as trans, " + .TB_PREF."debtor_trans_details as line, " + .TB_PREF."debtors_master as debtor, " + .TB_PREF."cust_branch as branch + WHERE + sorder.order_no = trans.order_ AND + trans.debtor_no = debtor.debtor_no + AND trans.type = ".ST_CUSTDELIVERY." + AND line.debtor_trans_no = trans.trans_no + AND line.debtor_trans_type = trans.type + AND trans.branch_code = branch.branch_code + AND trans.debtor_no = branch.debtor_no "; + + if ($_POST['OutstandingOnly'] == true) { + $sql .= " AND line.qty_done < line.quantity "; + } + + //figure out the sql required from the inputs available + if (isset($_POST['DeliveryNumber']) && $_POST['DeliveryNumber'] != "") + { + $delivery = "%".$_POST['DeliveryNumber']; + $sql .= " AND trans.trans_no LIKE ".db_escape($delivery); + $sql .= " GROUP BY trans.trans_no"; + } + else + { + $sql .= " AND trans.tran_date >= '".date2sql($_POST['DeliveryAfterDate'])."'"; + $sql .= " AND trans.tran_date <= '".date2sql($_POST['DeliveryToDate'])."'"; + + if ($selected_customer != -1) + $sql .= " AND trans.debtor_no=".db_escape($selected_customer)." "; + + if (isset($selected_stock_item)) + $sql .= " AND line.stock_id=".db_escape($selected_stock_item)." "; + + if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != ALL_TEXT) + $sql .= " AND sorder.from_stk_loc = ".db_escape($_POST['StockLocation'])." "; + + $sql .= " GROUP BY trans.trans_no "; + + } //end no delivery number selected + return $sql; +} ?> \ No newline at end of file diff --git a/sales/includes/db/custalloc_db.inc b/sales/includes/db/custalloc_db.inc index 618c9397..5b7782ae 100644 --- a/sales/includes/db/custalloc_db.inc +++ b/sales/includes/db/custalloc_db.inc @@ -171,5 +171,73 @@ function get_allocatable_to_cust_transactions($customer_id, $trans_no=null, $typ return db_query($sql." ORDER BY trans_no", "Cannot retreive alloc to transactions"); } +function get_sql_for_customer_allocation_inquiry() +{ + $data_after = date2sql($_POST['TransAfterDate']); + $date_to = date2sql($_POST['TransToDate']); + $sql = "SELECT + trans.type, + trans.trans_no, + trans.reference, + trans.order_, + trans.tran_date, + trans.due_date, + debtor.name, + debtor.curr_code, + (trans.ov_amount + trans.ov_gst + trans.ov_freight + + trans.ov_freight_tax + trans.ov_discount) AS TotalAmount, + trans.alloc AS Allocated, + ((trans.type = ".ST_SALESINVOICE.") + AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue + FROM " + .TB_PREF."debtor_trans as trans, " + .TB_PREF."debtors_master as debtor + WHERE debtor.debtor_no = trans.debtor_no + AND (trans.ov_amount + trans.ov_gst + trans.ov_freight + + trans.ov_freight_tax + trans.ov_discount != 0) + AND trans.tran_date >= '$data_after' + AND trans.tran_date <= '$date_to'"; + + if ($_POST['customer_id'] != ALL_TEXT) + $sql .= " AND trans.debtor_no = ".db_escape($_POST['customer_id']); + + if (isset($_POST['filterType']) && $_POST['filterType'] != ALL_TEXT) + { + if ($_POST['filterType'] == '1' || $_POST['filterType'] == '2') + { + $sql .= " AND trans.type = ".ST_SALESINVOICE." "; + } + elseif ($_POST['filterType'] == '3') + { + $sql .= " AND trans.type = " . ST_CUSTPAYMENT; + } + elseif ($_POST['filterType'] == '4') + { + $sql .= " AND trans.type = ".ST_CUSTCREDIT." "; + } + + if ($_POST['filterType'] == '2') + { + $today = date2sql(Today()); + $sql .= " AND trans.due_date < '$today' + AND (round(abs(trans.ov_amount + " + ."trans.ov_gst + trans.ov_freight + " + ."trans.ov_freight_tax + trans.ov_discount) - trans.alloc,6) > 0) "; + } + } + else + { + $sql .= " AND trans.type <> ".ST_CUSTDELIVERY." "; + } + + + if (!check_value('showSettled')) + { + $sql .= " AND (round(abs(trans.ov_amount + trans.ov_gst + " + ."trans.ov_freight + trans.ov_freight_tax + " + ."trans.ov_discount) - trans.alloc,6) != 0) "; + } + return $sql; +} ?> \ No newline at end of file diff --git a/sales/includes/db/customers_db.inc b/sales/includes/db/customers_db.inc index 1670c426..7ec7b297 100644 --- a/sales/includes/db/customers_db.inc +++ b/sales/includes/db/customers_db.inc @@ -9,6 +9,54 @@ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the License here . ***********************************************************************/ + +function add_customer($CustName, $cust_ref, $address, $tax_id, $curr_code, $email, $dimension_id, $dimension2_id, + $credit_status, $payment_terms, $discount, $pymt_discount, $credit_limit, $sales_type, $notes) +{ + $sql = "INSERT INTO ".TB_PREF."debtors_master (name, debtor_ref, address, tax_id, email, dimension_id, dimension2_id, + curr_code, credit_status, payment_terms, discount, pymt_discount,credit_limit, + sales_type, notes) VALUES (".db_escape($CustName) .", " .db_escape($cust_ref) .", " + .db_escape($address) . ", " . db_escape($tax_id) . "," + .db_escape($email) . ", ".db_escape($dimension_id) . ", " + .db_escape($dimension2_id) . ", ".db_escape($curr_code) . ", + " . db_escape($credit_status) . ", ".db_escape($payment_terms) . ", " . $discount . ", + " . $pymt_discount . ", " . $credit_limit + .", ".db_escape($sales_type).", ".db_escape($notes) . ")"; + + db_query($sql,"The customer could not be added"); +} + +function update_customer($customer_id, $CustName, $cust_ref, $address, $tax_id, $curr_code, $email, $dimension_id, $dimension2_id, + $credit_status, $payment_terms, $discount, $pymt_discount, $credit_limit, $sales_type, $notes) +{ + $sql = "UPDATE ".TB_PREF."debtors_master SET name=" . db_escape($CustName) . ", + debtor_ref=" . db_escape($cust_ref) . ", + address=".db_escape($address) . ", + tax_id=".db_escape($tax_id) . ", + curr_code=".db_escape($curr_code) . ", + email=".db_escape($email) . ", + dimension_id=".db_escape($dimension_id) . ", + dimension2_id=".db_escape($dimension2_id) . ", + credit_status=".db_escape($credit_status) . ", + payment_terms=".db_escape($payment_terms) . ", + discount=" . $discount . ", + pymt_discount=" . $pymt_discount . ", + credit_limit=" . $credit_limit . ", + sales_type = ".db_escape($sales_type) . ", + notes=".db_escape($notes) . " + WHERE debtor_no = ".db_escape($customer_id); + + db_query($sql,"The customer could not be updated"); +} + +function delete_customer($customer_id, $escaped=false) +{ + if (!$escaped) + $customer_id = db_escape($customer_id); + $sql = "DELETE FROM ".TB_PREF."debtors_master WHERE debtor_no=$customer_id"; + db_query($sql,"cannot delete customer"); +} + function get_customer_details($customer_id, $to=null) { @@ -118,27 +166,19 @@ function get_customer_name($customer_id) return $row[0]; } -function get_area_name($id) -{ - $sql = "SELECT description FROM ".TB_PREF."areas WHERE area_code=".db_escape($id); - - $result = db_query($sql, "could not get sales type"); - - $row = db_fetch_row($result); - return $row[0]; -} - -function get_salesman_name($id) +function get_customer_habit($customer_id) { - $sql = "SELECT salesman_name FROM ".TB_PREF."salesman WHERE salesman_code=".db_escape($id); + $sql = "SELECT ".TB_PREF."debtors_master.pymt_discount, + ".TB_PREF."credit_status.dissallow_invoices + FROM ".TB_PREF."debtors_master, ".TB_PREF."credit_status + WHERE ".TB_PREF."debtors_master.credit_status = ".TB_PREF."credit_status.id + AND ".TB_PREF."debtors_master.debtor_no = ".db_escape($customer_id); - $result = db_query($sql, "could not get sales type"); + $result = db_query($sql, "could not query customers"); - $row = db_fetch_row($result); - return $row[0]; + return db_fetch($result); } - ?> \ No newline at end of file diff --git a/sales/includes/db/recurrent_invoices_db.inc b/sales/includes/db/recurrent_invoices_db.inc new file mode 100644 index 00000000..4ff68549 --- /dev/null +++ b/sales/includes/db/recurrent_invoices_db.inc @@ -0,0 +1,66 @@ +. +***********************************************************************/ + +function add_recurrent_invoice($description, $order_no, $debtor_no, $group_no, $days, $monthly, + $begin, $end) +{ + $sql = "INSERT INTO ".TB_PREF."recurrent_invoices (description, order_no, debtor_no, + group_no, days, monthly, begin, end, last_sent) VALUES (".db_escape($description) . ", " + .db_escape($order_no).", ".db_escape($debtor_no).", " + .db_escape($group_no).", ".$days.", ".$monthly.", '" + .date2sql($begin)."', '".date2sql($end)."', '".date2sql(Add_Years($begin, -5))."')"; + db_query($sql,"The recurrent invoice could not be added"); +} + +function update_recurrent_invoice($selected_id, $description, $order_no, $debtor_no, $group_no, $days, $monthly, + $begin, $end) +{ + $sql = "UPDATE ".TB_PREF."recurrent_invoices SET + description=".db_escape($description).", + order_no=".db_escape($order_no).", + debtor_no=".db_escape($debtor_no).", + group_no=".db_escape($group_no).", + days=".$days.", + monthly=".$monthly.", + begin='".date2sql($begin)."', + end='".date2sql($end)."' + WHERE id = ".db_escape($selected_id); + db_query($sql,"The recurrent invoice could not be updated"); +} + +function update_last_sent_recurrent_invoice($id, $date) +{ + $date = date2sql($date); + $sql = "UPDATE ".TB_PREF."recurrent_invoices SET last_sent='$date' WHERE id=".db_escape($id); + db_query($sql,"The recurrent invoice could not be updated"); +} + +function delete_recurrent_invoice($selected_id) +{ + $sql="DELETE FROM ".TB_PREF."recurrent_invoices WHERE id=".db_escape($selected_id); + db_query($sql,"could not delete recurrent invoice"); +} + +function get_recurrent_invoices() +{ + $sql = "SELECT * FROM ".TB_PREF."recurrent_invoices ORDER BY description, group_no, debtor_no"; + return db_query($sql,"could not get recurrent invoices"); +} + +function get_recurrent_invoice($selected_id) +{ + $sql = "SELECT * FROM ".TB_PREF."recurrent_invoices WHERE id=".db_escape($selected_id); + + $result = db_query($sql,"could not get recurrent invoice"); + return db_fetch($result); +} +?> \ No newline at end of file diff --git a/sales/includes/db/sales_groups_db.inc b/sales/includes/db/sales_groups_db.inc new file mode 100644 index 00000000..57d15fc2 --- /dev/null +++ b/sales/includes/db/sales_groups_db.inc @@ -0,0 +1,159 @@ +. +***********************************************************************/ + +function add_sales_group($description) +{ + $sql = "INSERT INTO ".TB_PREF."groups (description) VALUES (".db_escape($description) . ")"; + db_query($sql,"The sales group could not be added"); +} + +function update_sales_group($selected_id, $description) +{ + $sql = "UPDATE ".TB_PREF."groups SET description=".db_escape($description)." WHERE id = ".db_escape($selected_id); + db_query($sql,"The sales group could not be updated"); +} + +function delete_sales_group($selected_id) +{ + $sql="DELETE FROM ".TB_PREF."groups WHERE id=".db_escape($selected_id); + db_query($sql,"could not delete sales group"); +} + +function get_sales_groups($show_inactive) +{ + $sql = "SELECT * FROM ".TB_PREF."groups"; + if (!$show_inactive) $sql .= " WHERE !inactive"; + $sql .= " ORDER BY description"; + return db_query($sql,"could not get groups"); +} + +function get_sales_group($selected_id) +{ + $sql = "SELECT * FROM ".TB_PREF."groups WHERE id=".db_escape($selected_id); + + $result = db_query($sql,"could not get group"); + return db_fetch($result); +} + +function get_sales_group_name($group_no) +{ + $sql = "SELECT description FROM ".TB_PREF."groups WHERE id = ".db_escape($group_no); + $result = db_query($sql, "could not get group"); + $row = db_fetch($result); + return $row[0]; +} + +function add_sales_area($description) +{ + $sql = "INSERT INTO ".TB_PREF."areas (description) VALUES (".db_escape($description) . ")"; + db_query($sql,"The sales area could not be added"); +} + +function update_sales_area($selected_id, $description) +{ + $sql = "UPDATE ".TB_PREF."areas SET description=".db_escape($description)." WHERE area_code = ".db_escape($selected_id); + db_query($sql,"The sales area could not be updated"); +} + +function delete_sales_area($selected_id) +{ + $sql="DELETE FROM ".TB_PREF."areas WHERE area_code=".db_escape($selected_id); + db_query($sql,"could not delete sales area"); +} + +function get_sales_areas($show_inactive) +{ + $sql = "SELECT * FROM ".TB_PREF."areas"; + if (!$show_inactive) $sql .= " WHERE !inactive"; + return db_query($sql,"could not get areas"); +} + +function get_sales_area($selected_id) +{ + $sql = "SELECT * FROM ".TB_PREF."areas WHERE area_code=".db_escape($selected_id); + + $result = db_query($sql,"could not get area"); + return db_fetch($result); +} + +function get_area_name($id) +{ + $sql = "SELECT description FROM ".TB_PREF."areas WHERE area_code=".db_escape($id); + + $result = db_query($sql, "could not get sales type"); + + $row = db_fetch_row($result); + return $row[0]; +} + +function add_salesman($salesman_name, $salesman_phone, $salesman_fax, + $salesman_email, $provision, $break_pt, $provision2) +{ + $sql = "INSERT INTO ".TB_PREF."salesman (salesman_name, salesman_phone, salesman_fax, salesman_email, + provision, break_pt, provision2) + VALUES (".db_escape($salesman_name) . ", " + .db_escape($salesman_phone) . ", " + .db_escape($salesman_fax) . ", " + .db_escape($salesman_email) . ", ". + $provision.", ".$break_pt.", " + .$provision2.")"; + db_query($sql,"The insert of the sales person failed"); +} + +function update_salesman($selected_id, $salesman_name, $salesman_phone, $salesman_fax, + $salesman_email, $provision, $break_pt, $provision2) +{ + $sql = "UPDATE ".TB_PREF."salesman SET salesman_name=".db_escape($salesman_name) . ", + salesman_phone=".db_escape($salesman_phone) . ", + salesman_fax=".db_escape($salesman_fax) . ", + salesman_email=".db_escape($salesman_email) . ", + provision=".$provision.", + break_pt=".$break_pt.", + provision2=".$provision2." + WHERE salesman_code = ".db_escape($selected_id); + db_query($sql,"The update of the sales person failed"); +} + +function delete_salesman($selected_id) +{ + $sql="DELETE FROM ".TB_PREF."salesman WHERE salesman_code=".db_escape($selected_id); + db_query($sql,"The sales-person could not be deleted"); +} + +function get_salesmen($show_inactive) +{ + $sql = "SELECT * FROM ".TB_PREF."salesman"; + if (!$show_inactive) $sql .= " WHERE !inactive"; + return db_query($sql,"could not get sales persons"); +} + +function get_salesman($selected_id) +{ + $sql = "SELECT * FROM ".TB_PREF."salesman WHERE salesman_code=".db_escape($selected_id); + + $result = db_query($sql,"could not get sales person"); + return db_fetch($result); +} + +function get_salesman_name($id) +{ + $sql = "SELECT salesman_name FROM ".TB_PREF."salesman WHERE salesman_code=".db_escape($id); + + $result = db_query($sql, "could not get sales type"); + + $row = db_fetch_row($result); + return $row[0]; +} + + + +?> \ No newline at end of file diff --git a/sales/includes/db/sales_order_db.inc b/sales/includes/db/sales_order_db.inc index 0d8c618a..299240c9 100644 --- a/sales/includes/db/sales_order_db.inc +++ b/sales/includes/db/sales_order_db.inc @@ -474,4 +474,78 @@ function get_branch_to_order($customer_id, $branch_id) { return db_query($sql,"Customer Branch Record Retreive"); } + +function get_sql_for_sales_orders_view($selected_customer, $trans_type) +{ + global $selected_stock_item; + + $sql = "SELECT + sorder.order_no, + sorder.reference, + debtor.name, + branch.br_name," + .($_POST['order_view_mode']=='InvoiceTemplates' + || $_POST['order_view_mode']=='DeliveryTemplates' ? + "sorder.comments, " : "sorder.customer_ref, ") + ."sorder.ord_date, + sorder.delivery_date, + sorder.deliver_to, + Sum(line.unit_price*line.quantity*(1-line.discount_percent))+freight_cost AS OrderValue, + sorder.type, + debtor.curr_code, + Sum(line.qty_sent) AS TotDelivered, + Sum(line.quantity) AS TotQuantity + FROM ".TB_PREF."sales_orders as sorder, " + .TB_PREF."sales_order_details as line, " + .TB_PREF."debtors_master as debtor, " + .TB_PREF."cust_branch as branch + WHERE sorder.order_no = line.order_no + AND sorder.trans_type = line.trans_type + AND sorder.trans_type = $trans_type + AND sorder.debtor_no = debtor.debtor_no + AND sorder.branch_code = branch.branch_code + AND debtor.debtor_no = branch.debtor_no"; + + if (isset($_POST['OrderNumber']) && $_POST['OrderNumber'] != "") + { + // search orders with number like + $number_like = "%".$_POST['OrderNumber']; + $sql .= " AND sorder.order_no LIKE ".db_escape($number_like) + ." GROUP BY sorder.order_no"; + } + else // ... or select inquiry constraints + { + if ($_POST['order_view_mode']!='DeliveryTemplates' && $_POST['order_view_mode']!='InvoiceTemplates') + { + $date_after = date2sql($_POST['OrdersAfterDate']); + $date_before = date2sql($_POST['OrdersToDate']); + + $sql .= " AND sorder.ord_date >= '$date_after'" + ." AND sorder.ord_date <= '$date_before'"; + } + if ($trans_type == ST_SALESQUOTE && !check_value('show_all')) + $sql .= " AND sorder.delivery_date >= '".date2sql(Today())."'"; + if ($selected_customer != -1) + $sql .= " AND sorder.debtor_no=".db_escape($selected_customer); + + if (isset($selected_stock_item)) + $sql .= " AND line.stk_code=".db_escape($selected_stock_item); + + if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != ALL_TEXT) + $sql .= " AND sorder.from_stk_loc = ".db_escape($_POST['StockLocation']); + + if ($_POST['order_view_mode']=='OutstandingOnly') + $sql .= " AND line.qty_sent < line.quantity"; + elseif ($_POST['order_view_mode']=='InvoiceTemplates' || $_POST['order_view_mode']=='DeliveryTemplates') + $sql .= " AND sorder.type=1"; + + $sql .= " GROUP BY sorder.order_no, + sorder.debtor_no, + sorder.branch_code, + sorder.customer_ref, + sorder.ord_date, + sorder.deliver_to"; + } + return $sql; +} ?> \ No newline at end of file diff --git a/sales/includes/sales_db.inc b/sales/includes/sales_db.inc index 53645836..febf9332 100644 --- a/sales/includes/sales_db.inc +++ b/sales/includes/sales_db.inc @@ -17,6 +17,8 @@ include_once($path_to_root . "/sales/includes/db/sales_invoice_db.inc"); include_once($path_to_root . "/sales/includes/db/sales_delivery_db.inc"); include_once($path_to_root . "/sales/includes/db/sales_types_db.inc"); include_once($path_to_root . "/sales/includes/db/sales_points_db.inc"); +include_once($path_to_root . "/sales/includes/db/sales_groups_db.inc"); +include_once($path_to_root . "/sales/includes/db/recurrent_invoices_db.inc"); include_once($path_to_root . "/sales/includes/db/custalloc_db.inc"); include_once($path_to_root . "/sales/includes/db/cust_trans_db.inc"); include_once($path_to_root . "/sales/includes/db/cust_trans_details_db.inc"); diff --git a/sales/inquiry/customer_allocation_inquiry.php b/sales/inquiry/customer_allocation_inquiry.php index 84e5416f..343e367e 100644 --- a/sales/inquiry/customer_allocation_inquiry.php +++ b/sales/inquiry/customer_allocation_inquiry.php @@ -133,70 +133,8 @@ function fmt_credit($row) } //------------------------------------------------------------------------------------------------ - $data_after = date2sql($_POST['TransAfterDate']); - $date_to = date2sql($_POST['TransToDate']); - - $sql = "SELECT - trans.type, - trans.trans_no, - trans.reference, - trans.order_, - trans.tran_date, - trans.due_date, - debtor.name, - debtor.curr_code, - (trans.ov_amount + trans.ov_gst + trans.ov_freight - + trans.ov_freight_tax + trans.ov_discount) AS TotalAmount, - trans.alloc AS Allocated, - ((trans.type = ".ST_SALESINVOICE.") - AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue - FROM " - .TB_PREF."debtor_trans as trans, " - .TB_PREF."debtors_master as debtor - WHERE debtor.debtor_no = trans.debtor_no - AND (trans.ov_amount + trans.ov_gst + trans.ov_freight - + trans.ov_freight_tax + trans.ov_discount != 0) - AND trans.tran_date >= '$data_after' - AND trans.tran_date <= '$date_to'"; - - if ($_POST['customer_id'] != ALL_TEXT) - $sql .= " AND trans.debtor_no = ".db_escape($_POST['customer_id']); - - if (isset($_POST['filterType']) && $_POST['filterType'] != ALL_TEXT) - { - if ($_POST['filterType'] == '1' || $_POST['filterType'] == '2') - { - $sql .= " AND trans.type = ".ST_SALESINVOICE." "; - } - elseif ($_POST['filterType'] == '3') - { - $sql .= " AND trans.type = " . ST_CUSTPAYMENT; - } - elseif ($_POST['filterType'] == '4') - { - $sql .= " AND trans.type = ".ST_CUSTCREDIT." "; - } - - if ($_POST['filterType'] == '2') - { - $today = date2sql(Today()); - $sql .= " AND trans.due_date < '$today' - AND (round(abs(trans.ov_amount + " - ."trans.ov_gst + trans.ov_freight + " - ."trans.ov_freight_tax + trans.ov_discount) - trans.alloc,6) > 0) "; - } - }else - { - $sql .= " AND trans.type <> ".ST_CUSTDELIVERY." "; - } - - - if (!check_value('showSettled')) - { - $sql .= " AND (round(abs(trans.ov_amount + trans.ov_gst + " - ."trans.ov_freight + trans.ov_freight_tax + " - ."trans.ov_discount) - trans.alloc,6) != 0) "; - } +$sql = get_sql_for_customer_allocation_inquiry(); + //------------------------------------------------------------------------------------------------ $cols = array( diff --git a/sales/inquiry/customer_inquiry.php b/sales/inquiry/customer_inquiry.php index de745b5a..8029dc9c 100644 --- a/sales/inquiry/customer_inquiry.php +++ b/sales/inquiry/customer_inquiry.php @@ -203,75 +203,7 @@ function check_overdue($row) && (abs($row["TotalAmount"]) - $row["Allocated"] != 0); } //------------------------------------------------------------------------------------------------ - $date_after = date2sql($_POST['TransAfterDate']); - $date_to = date2sql($_POST['TransToDate']); - - $sql = "SELECT - trans.type, - trans.trans_no, - trans.order_, - trans.reference, - trans.tran_date, - trans.due_date, - debtor.name, - branch.br_name, - debtor.curr_code, - (trans.ov_amount + trans.ov_gst + trans.ov_freight - + trans.ov_freight_tax + trans.ov_discount) AS TotalAmount, "; - if ($_POST['filterType'] != ALL_TEXT) - $sql .= "@bal := @bal+(trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount), "; - -// else -// $sql .= "IF(trans.type=".ST_CUSTDELIVERY.",'', IF(trans.type=".ST_SALESINVOICE." OR trans.type=".ST_BANKPAYMENT.",@bal := @bal+ -// (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount), @bal := @bal- -// (trans.ov_amount + trans.ov_gst + trans.ov_freight + trans.ov_freight_tax + trans.ov_discount))) , "; - $sql .= "trans.alloc AS Allocated, - ((trans.type = ".ST_SALESINVOICE.") - AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue - FROM " - .TB_PREF."debtor_trans as trans, " - .TB_PREF."debtors_master as debtor, " - .TB_PREF."cust_branch as branch - WHERE debtor.debtor_no = trans.debtor_no - AND trans.tran_date >= '$date_after' - AND trans.tran_date <= '$date_to' - AND trans.branch_code = branch.branch_code"; - - if ($_POST['customer_id'] != ALL_TEXT) - $sql .= " AND trans.debtor_no = ".db_escape($_POST['customer_id']); - - if ($_POST['filterType'] != ALL_TEXT) - { - if ($_POST['filterType'] == '1') - { - $sql .= " AND (trans.type = ".ST_SALESINVOICE." OR trans.type = ".ST_BANKPAYMENT.") "; - } - elseif ($_POST['filterType'] == '2') - { - $sql .= " AND (trans.type = ".ST_SALESINVOICE.") "; - } - elseif ($_POST['filterType'] == '3') - { - $sql .= " AND (trans.type = " . ST_CUSTPAYMENT - ." OR trans.type = ".ST_BANKDEPOSIT.") "; - } - elseif ($_POST['filterType'] == '4') - { - $sql .= " AND trans.type = ".ST_CUSTCREDIT." "; - } - elseif ($_POST['filterType'] == '5') - { - $sql .= " AND trans.type = ".ST_CUSTDELIVERY." "; - } - - if ($_POST['filterType'] == '2') - { - $today = date2sql(Today()); - $sql .= " AND trans.due_date < '$today' - AND (trans.ov_amount + trans.ov_gst + trans.ov_freight_tax + - trans.ov_freight + trans.ov_discount - trans.alloc > 0) "; - } - } +$sql = get_sql_for_customer_inquiry(); //------------------------------------------------------------------------------------------------ db_query("set @bal:=0"); diff --git a/sales/inquiry/sales_deliveries_view.php b/sales/inquiry/sales_deliveries_view.php index e65602bf..2069703b 100644 --- a/sales/inquiry/sales_deliveries_view.php +++ b/sales/inquiry/sales_deliveries_view.php @@ -168,62 +168,7 @@ function check_overdue($row) $row["Outstanding"]!=0; } //------------------------------------------------------------------------------------------------ -$sql = "SELECT trans.trans_no, - debtor.name, - branch.branch_code, - branch.br_name, - sorder.deliver_to, - trans.reference, - sorder.customer_ref, - trans.tran_date, - trans.due_date, - (ov_amount+ov_gst+ov_freight+ov_freight_tax) AS DeliveryValue, - debtor.curr_code, - Sum(line.quantity-line.qty_done) AS Outstanding, - Sum(line.qty_done) AS Done - FROM " - .TB_PREF."sales_orders as sorder, " - .TB_PREF."debtor_trans as trans, " - .TB_PREF."debtor_trans_details as line, " - .TB_PREF."debtors_master as debtor, " - .TB_PREF."cust_branch as branch - WHERE - sorder.order_no = trans.order_ AND - trans.debtor_no = debtor.debtor_no - AND trans.type = ".ST_CUSTDELIVERY." - AND line.debtor_trans_no = trans.trans_no - AND line.debtor_trans_type = trans.type - AND trans.branch_code = branch.branch_code - AND trans.debtor_no = branch.debtor_no "; - -if ($_POST['OutstandingOnly'] == true) { - $sql .= " AND line.qty_done < line.quantity "; -} - -//figure out the sql required from the inputs available -if (isset($_POST['DeliveryNumber']) && $_POST['DeliveryNumber'] != "") -{ - $delivery = "%".$_POST['DeliveryNumber']; - $sql .= " AND trans.trans_no LIKE ".db_escape($delivery); - $sql .= " GROUP BY trans.trans_no"; -} -else -{ - $sql .= " AND trans.tran_date >= '".date2sql($_POST['DeliveryAfterDate'])."'"; - $sql .= " AND trans.tran_date <= '".date2sql($_POST['DeliveryToDate'])."'"; - - if ($selected_customer != -1) - $sql .= " AND trans.debtor_no=".db_escape($selected_customer)." "; - - if (isset($selected_stock_item)) - $sql .= " AND line.stock_id=".db_escape($selected_stock_item)." "; - - if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != ALL_TEXT) - $sql .= " AND sorder.from_stk_loc = ".db_escape($_POST['StockLocation'])." "; - - $sql .= " GROUP BY trans.trans_no "; - -} //end no delivery number selected +$sql = get_sql_for_sales_deliveries_view($selected_customer, $selected_stock_id); $cols = array( _("Delivery #") => array('fun'=>'trans_view'), diff --git a/sales/inquiry/sales_orders_view.php b/sales/inquiry/sales_orders_view.php index ec009123..c60df3a4 100644 --- a/sales/inquiry/sales_orders_view.php +++ b/sales/inquiry/sales_orders_view.php @@ -245,73 +245,7 @@ end_table(1); //--------------------------------------------------------------------------------------------- // Orders inquiry table // -$sql = "SELECT - sorder.order_no, - sorder.reference, - debtor.name, - branch.br_name," - .($_POST['order_view_mode']=='InvoiceTemplates' - || $_POST['order_view_mode']=='DeliveryTemplates' ? - "sorder.comments, " : "sorder.customer_ref, ") - ."sorder.ord_date, - sorder.delivery_date, - sorder.deliver_to, - Sum(line.unit_price*line.quantity*(1-line.discount_percent))+freight_cost AS OrderValue, - sorder.type, - debtor.curr_code, - Sum(line.qty_sent) AS TotDelivered, - Sum(line.quantity) AS TotQuantity - FROM ".TB_PREF."sales_orders as sorder, " - .TB_PREF."sales_order_details as line, " - .TB_PREF."debtors_master as debtor, " - .TB_PREF."cust_branch as branch - WHERE sorder.order_no = line.order_no - AND sorder.trans_type = line.trans_type - AND sorder.trans_type = $trans_type - AND sorder.debtor_no = debtor.debtor_no - AND sorder.branch_code = branch.branch_code - AND debtor.debtor_no = branch.debtor_no"; - -if (isset($_POST['OrderNumber']) && $_POST['OrderNumber'] != "") -{ - // search orders with number like - $number_like = "%".$_POST['OrderNumber']; - $sql .= " AND sorder.order_no LIKE ".db_escape($number_like) - ." GROUP BY sorder.order_no"; -} -else // ... or select inquiry constraints -{ - if ($_POST['order_view_mode']!='DeliveryTemplates' && $_POST['order_view_mode']!='InvoiceTemplates') - { - $date_after = date2sql($_POST['OrdersAfterDate']); - $date_before = date2sql($_POST['OrdersToDate']); - - $sql .= " AND sorder.ord_date >= '$date_after'" - ." AND sorder.ord_date <= '$date_before'"; - } - if ($trans_type == 32 && !check_value('show_all')) - $sql .= " AND sorder.delivery_date >= '".date2sql(Today())."'"; - if ($selected_customer != -1) - $sql .= " AND sorder.debtor_no=".db_escape($selected_customer); - - if (isset($selected_stock_item)) - $sql .= " AND line.stk_code=".db_escape($selected_stock_item); - - if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != ALL_TEXT) - $sql .= " AND sorder.from_stk_loc = ".db_escape($_POST['StockLocation']); - - if ($_POST['order_view_mode']=='OutstandingOnly') - $sql .= " AND line.qty_sent < line.quantity"; - elseif ($_POST['order_view_mode']=='InvoiceTemplates' || $_POST['order_view_mode']=='DeliveryTemplates') - $sql .= " AND sorder.type=1"; - - $sql .= " GROUP BY sorder.order_no, - sorder.debtor_no, - sorder.branch_code, - sorder.customer_ref, - sorder.ord_date, - sorder.deliver_to"; -} +$sql = get_sql_for_sales_orders_view($selected_customer, $trans_type); if ($trans_type == ST_SALESORDER) $cols = array( diff --git a/sales/manage/credit_status.php b/sales/manage/credit_status.php index aed0b1ff..90fc179e 100644 --- a/sales/manage/credit_status.php +++ b/sales/manage/credit_status.php @@ -58,11 +58,7 @@ if ($Mode=='UPDATE_ITEM' && can_process()) function can_delete($selected_id) { - $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtors_master - WHERE credit_status=".db_escape($selected_id); - $result = db_query($sql, "could not query customers"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (key_in_foreign_table($selected_id, 'debtors_master', 'credit_status')) { display_error(_("Cannot delete this credit status because customer accounts have been created referring to it.")); return false; diff --git a/sales/manage/customer_branches.php b/sales/manage/customer_branches.php index 3850e8ba..72bb9f29 100644 --- a/sales/manage/customer_branches.php +++ b/sales/manage/customer_branches.php @@ -77,62 +77,27 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') if ($selected_id != -1) { /*SelectedBranch could also exist if submit had not been clicked this code would not run in this case cos submit is false of course see the delete code below*/ - - $sql = "UPDATE ".TB_PREF."cust_branch SET br_name = " . db_escape($_POST['br_name']) . ", - branch_ref = " . db_escape($_POST['br_ref']) . ", - br_address = ".db_escape($_POST['br_address']). ", - phone=".db_escape($_POST['phone']). ", - phone2=".db_escape($_POST['phone2']). ", - fax=".db_escape($_POST['fax']).", - contact_name=".db_escape($_POST['contact_name']) . ", - salesman= ".db_escape($_POST['salesman']) . ", - area=".db_escape($_POST['area']) . ", - email=".db_escape($_POST['email']) . ", - tax_group_id=".db_escape($_POST['tax_group_id']). ", - sales_account=".db_escape($_POST['sales_account']) . ", - sales_discount_account=".db_escape($_POST['sales_discount_account']) . ", - receivables_account=".db_escape($_POST['receivables_account']) . ", - payment_discount_account=".db_escape($_POST['payment_discount_account']) . ", - default_location=".db_escape($_POST['default_location']) . ", - br_post_address =".db_escape($_POST['br_post_address']) . ", - disable_trans=".db_escape($_POST['disable_trans']) . ", - group_no=".db_escape($_POST['group_no']) . ", - default_ship_via=".db_escape($_POST['default_ship_via']) . ", - notes=".db_escape($_POST['notes']) . " - WHERE branch_code =".db_escape($_POST['branch_code']) . " - AND debtor_no=".db_escape($_POST['customer_id']); + update_branch($_POST['customer_id'], $_POST['branch_code'], $_POST['br_name'], $_POST['br_ref'], + $_POST['br_address'], $_POST['phone'], $_POST['phone2'], $_POST['fax'], $_POST['contact_name'], + $_POST['salesman'], $_POST['area'], $_POST['email'], $_POST['tax_group_id'], $_POST['sales_account'], + $_POST['sales_discount_account'], $_POST['receivables_account'], $_POST['payment_discount_account'], + $_POST['default_location'], $_POST['br_post_address'], $_POST['disable_trans'], $_POST['group_no'], + $_POST['default_ship_via'], $_POST['notes']); $note =_('Selected customer branch has been updated'); } else { /*Selected branch is null cos no item selected on first time round so must be adding a record must be submitting new entries in the new Customer Branches form */ - $sql = "INSERT INTO ".TB_PREF."cust_branch (debtor_no, br_name, branch_ref, br_address, - salesman, phone, phone2, fax, - contact_name, area, email, tax_group_id, sales_account, receivables_account, payment_discount_account, sales_discount_account, default_location, - br_post_address, disable_trans, group_no, default_ship_via, notes) - VALUES (".db_escape($_POST['customer_id']). ",".db_escape($_POST['br_name']) . ", " - .db_escape($_POST['br_ref']) . ", " - .db_escape($_POST['br_address']) . ", ".db_escape($_POST['salesman']) . ", " - .db_escape($_POST['phone']) . ", ".db_escape($_POST['phone2']) . ", " - .db_escape($_POST['fax']) . "," - .db_escape($_POST['contact_name']) . ", ".db_escape($_POST['area']) . "," - .db_escape($_POST['email']) . ", ".db_escape($_POST['tax_group_id']) . ", " - .db_escape($_POST['sales_account']) . ", " - .db_escape($_POST['receivables_account']) . ", " - .db_escape($_POST['payment_discount_account']) . ", " - .db_escape($_POST['sales_discount_account']) . ", " - .db_escape($_POST['default_location']) . ", " - .db_escape($_POST['br_post_address']) . "," - .db_escape($_POST['disable_trans']) . ", " - .db_escape($_POST['group_no']) . ", " - .db_escape($_POST['default_ship_via']). ", " - .db_escape($_POST['notes']) . ")"; - + add_branch($_POST['customer_id'], $_POST['br_name'], $_POST['br_ref'], + $_POST['br_address'], $_POST['phone'], $_POST['phone2'], $_POST['fax'], $_POST['contact_name'], + $_POST['salesman'], $_POST['area'], $_POST['email'], $_POST['tax_group_id'], $_POST['sales_account'], + $_POST['sales_discount_account'], $_POST['receivables_account'], $_POST['payment_discount_account'], + $_POST['default_location'], $_POST['br_post_address'], $_POST['disable_trans'], $_POST['group_no'], + $_POST['default_ship_via'], $_POST['notes']); + $note = _('New customer branch has been added'); } - //run the sql from either of the above possibilites - db_query($sql,"The branch record could not be inserted or updated"); display_notification($note); $Mode = 'RESET'; if (@$_REQUEST['popup']) { @@ -148,28 +113,20 @@ elseif ($Mode == 'Delete') // PREVENT DELETES IF DEPENDENT RECORDS IN 'debtor_trans' - $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtor_trans WHERE branch_code=".db_escape($_POST['branch_code'])." AND debtor_no = ".db_escape($_POST['customer_id']); - $result = db_query($sql,"could not query debtortrans"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (branch_in_foreign_table($_POST['customer_id'], $_POST['branch_code'], 'debtor_trans')) { display_error(_("Cannot delete this branch because customer transactions have been created to this branch.")); } else { - $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_orders WHERE branch_code=".db_escape($_POST['branch_code'])." AND debtor_no = ".db_escape($_POST['customer_id']); - $result = db_query($sql,"could not query sales orders"); - - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (branch_in_foreign_table($_POST['customer_id'], $_POST['branch_code'], 'sales_orders')) { display_error(_("Cannot delete this branch because sales orders exist for it. Purge old sales orders first.")); } else { - $sql="DELETE FROM ".TB_PREF."cust_branch WHERE branch_code=".db_escape($_POST['branch_code'])." AND debtor_no=".db_escape($_POST['customer_id']); - db_query($sql,"could not delete branch"); + delete_branch($_POST['customer_id'], $_POST['branch_code']); display_notification(_('Selected customer branch has been deleted')); } } //end ifs to test if the branch can be deleted @@ -211,30 +168,8 @@ echo "
"; $num_branches = db_customer_has_branches($_POST['customer_id']); - $sql = "SELECT " - ."b.branch_code, " - ."b.branch_ref, " - ."b.br_name, " - ."b.contact_name, " - ."s.salesman_name, " - ."a.description, " - ."b.phone, " - ."b.fax, " - ."b.email, " - ."t.name AS tax_group_name, " - ."b.inactive - FROM ".TB_PREF."cust_branch b, " - .TB_PREF."debtors_master c, " - .TB_PREF."areas a, " - .TB_PREF."salesman s, " - .TB_PREF."tax_groups t - WHERE b.debtor_no=c.debtor_no - AND b.tax_group_id=t.id - AND b.area=a.area_code - AND b.salesman=s.salesman_code - AND b.debtor_no = ".db_escape($_POST['customer_id']); - - if (!get_post('show_inactive')) $sql .= " AND !b.inactive"; +$sql = get_sql_for_customer_branches(); + //------------------------------------------------------------------------------------------------ if ($num_branches) { @@ -279,11 +214,7 @@ if ($selected_id != -1) if ($Mode == 'Edit') { //editing an existing branch - $sql = "SELECT * FROM ".TB_PREF."cust_branch - WHERE branch_code=".db_escape($_POST['branch_code'])." - AND debtor_no=".db_escape($_POST['customer_id']); - $result = db_query($sql,"check failed"); - $myrow = db_fetch($result); + $myrow = get_cust_branch($_POST['customer_id'], $_POST['branch_code']); set_focus('br_name'); $_POST['branch_code'] = $myrow["branch_code"]; $_POST['br_name'] = $myrow["br_name"]; @@ -313,10 +244,7 @@ if ($selected_id != -1) elseif ($Mode != 'ADD_ITEM') { //end of if $SelectedBranch only do the else when a new record is being entered if(!$num_branches) { - $sql = "SELECT name, address, email, debtor_ref - FROM ".TB_PREF."debtors_master WHERE debtor_no = ".db_escape($_POST['customer_id']); - $result = db_query($sql,"check failed"); - $myrow = db_fetch($result); + $myrow = get_default_info_for_branch($_POST['customer_id']); $_POST['br_name'] = $myrow["name"]; $_POST['br_ref'] = $myrow["debtor_ref"]; $_POST['contact_name'] = _('Main Branch'); diff --git a/sales/manage/customers.php b/sales/manage/customers.php index dd264683..e95af958 100644 --- a/sales/manage/customers.php +++ b/sales/manage/customers.php @@ -77,25 +77,10 @@ function handle_submit() if ($new_customer == false) { - - $sql = "UPDATE ".TB_PREF."debtors_master SET name=" . db_escape($_POST['CustName']) . ", - debtor_ref=" . db_escape($_POST['cust_ref']) . ", - address=".db_escape($_POST['address']) . ", - tax_id=".db_escape($_POST['tax_id']) . ", - curr_code=".db_escape($_POST['curr_code']) . ", - email=".db_escape($_POST['email']) . ", - dimension_id=".db_escape($_POST['dimension_id']) . ", - dimension2_id=".db_escape($_POST['dimension2_id']) . ", - credit_status=".db_escape($_POST['credit_status']) . ", - payment_terms=".db_escape($_POST['payment_terms']) . ", - discount=" . input_num('discount') / 100 . ", - pymt_discount=" . input_num('pymt_discount') / 100 . ", - credit_limit=" . input_num('credit_limit') . ", - sales_type = ".db_escape($_POST['sales_type']) . ", - notes=".db_escape($_POST['notes']) . " - WHERE debtor_no = ".db_escape($_POST['customer_id']); - - db_query($sql,"The customer could not be updated"); + update_customer($_POST['customer_id'], $_POST['CustName'], $_POST['cust_ref'], $_POST['address'], + $_POST['tax_id'], $_POST['curr_code'], $_POST['email'], $_POST['dimension_id'], $_POST['dimension2_id'], + $_POST['credit_status'], $_POST['payment_terms'], input_num('discount') / 100, input_num('pymt_discount') / 100, + input_num('credit_limit'), $_POST['sales_type'], $_POST['notes']); update_record_status($_POST['customer_id'], $_POST['inactive'], 'debtors_master', 'debtor_no'); @@ -107,18 +92,10 @@ function handle_submit() { //it is a new customer begin_transaction(); - - $sql = "INSERT INTO ".TB_PREF."debtors_master (name, debtor_ref, address, tax_id, email, dimension_id, dimension2_id, - curr_code, credit_status, payment_terms, discount, pymt_discount,credit_limit, - sales_type, notes) VALUES (".db_escape($_POST['CustName']) .", " .db_escape($_POST['cust_ref']) .", " - .db_escape($_POST['address']) . ", " . db_escape($_POST['tax_id']) . "," - .db_escape($_POST['email']) . ", ".db_escape($_POST['dimension_id']) . ", " - .db_escape($_POST['dimension2_id']) . ", ".db_escape($_POST['curr_code']) . ", - " . db_escape($_POST['credit_status']) . ", ".db_escape($_POST['payment_terms']) . ", " . input_num('discount')/100 . ", - " . input_num('pymt_discount')/100 . ", " . input_num('credit_limit') - .", ".db_escape($_POST['sales_type']).", ".db_escape($_POST['notes']) . ")"; - - db_query($sql,"The customer could not be added"); + add_customer($_POST['CustName'], $_POST['cust_ref'], $_POST['address'], + $_POST['tax_id'], $_POST['curr_code'], $_POST['email'], $_POST['dimension_id'], $_POST['dimension2_id'], + $_POST['credit_status'], $_POST['payment_terms'], input_num('discount') / 100, input_num('pymt_discount') / 100, + input_num('credit_limit'), $_POST['sales_type'], $_POST['notes']); $_POST['customer_id'] = db_insert_id(); $new_customer = false; @@ -146,30 +123,22 @@ if (isset($_POST['delete'])) // PREVENT DELETES IF DEPENDENT RECORDS IN 'debtor_trans' $sel_id = db_escape($_POST['customer_id']); - $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtor_trans WHERE debtor_no=$sel_id"; - $result = db_query($sql,"check failed"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + + if (key_in_foreign_table($sel_id, 'debtor_trans', 'debtor_no', true)) { $cancel_delete = 1; display_error(_("This customer cannot be deleted because there are transactions that refer to it.")); } else { - $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_orders WHERE debtor_no=$sel_id"; - $result = db_query($sql,"check failed"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (key_in_foreign_table($sel_id, 'sales_orders', 'debtor_no', true)) { $cancel_delete = 1; display_error(_("Cannot delete the customer record because orders have been created against it.")); } else { - $sql = "SELECT COUNT(*) FROM ".TB_PREF."cust_branch WHERE debtor_no=$sel_id"; - $result = db_query($sql,"check failed"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (key_in_foreign_table($sel_id, 'cust_branch', 'debtor_no', true)) { $cancel_delete = 1; display_error(_("Cannot delete this customer because there are branch records set up against it.")); @@ -180,8 +149,8 @@ if (isset($_POST['delete'])) if ($cancel_delete == 0) { //ie not cancelled the delete as a result of above tests - $sql = "DELETE FROM ".TB_PREF."debtors_master WHERE debtor_no=$sel_id"; - db_query($sql,"cannot delete customer"); + + delete_customer($sel_id, true); display_notification(_("Selected customer has been deleted.")); unset($_POST['customer_id']); @@ -230,11 +199,7 @@ if ($new_customer) } else { - - $sql = "SELECT * FROM ".TB_PREF."debtors_master WHERE debtor_no = ".db_escape($_POST['customer_id']); - $result = db_query($sql,"check failed"); - - $myrow = db_fetch($result); + $myrow = get_customer($_POST['customer_id']); $_POST['CustName'] = $myrow["name"]; $_POST['cust_ref'] = $myrow["debtor_ref"]; diff --git a/sales/manage/recurrent_invoices.php b/sales/manage/recurrent_invoices.php index f646ff2a..dea4ef3f 100644 --- a/sales/manage/recurrent_invoices.php +++ b/sales/manage/recurrent_invoices.php @@ -13,6 +13,7 @@ $page_security = 'SA_SRECURRENT'; $path_to_root = "../.."; include($path_to_root . "/includes/session.inc"); include($path_to_root . "/includes/ui.inc"); +include_once($path_to_root . "/sales/includes/sales_db.inc"); $js = ""; if ($use_popup_windows) @@ -40,29 +41,17 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') { if ($selected_id != -1) { - $sql = "UPDATE ".TB_PREF."recurrent_invoices SET - description=".db_escape($_POST['description']).", - order_no=".db_escape($_POST['order_no']).", - debtor_no=".db_escape($_POST['debtor_no']).", - group_no=".db_escape($_POST['group_no']).", - days=".input_num('days', 0).", - monthly=".input_num('monthly', 0).", - begin='".date2sql($_POST['begin'])."', - end='".date2sql($_POST['end'])."' - WHERE id = ".db_escape($selected_id); + update_recurrent_invoice($selected_id, $_POST['description'], $_POST['order_no'], $_POST['debtor_no'], + $_POST['group_no'], input_num('days', 0), input_num('monthly', 0), $_POST['begin'], $_POST['end']); $note = _('Selected recurrent invoice has been updated'); } else { - $sql = "INSERT INTO ".TB_PREF."recurrent_invoices (description, order_no, debtor_no, - group_no, days, monthly, begin, end, last_sent) VALUES (".db_escape($_POST['description']) . ", " - .db_escape($_POST['order_no']).", ".db_escape($_POST['debtor_no']).", " - .db_escape($_POST['group_no']).", ".input_num('days', 0).", ".input_num('monthly', 0).", '" - .date2sql($_POST['begin'])."', '".date2sql($_POST['end'])."', '".date2sql(Add_Years($_POST['begin'], -5))."')"; + add_recurrent_invoice($_POST['description'], $_POST['order_no'], $_POST['debtor_no'], $_POST['group_no'], + input_num('days', 0), input_num('monthly', 0), $_POST['begin'], $_POST['end']); $note = _('New recurrent invoice has been added'); } - db_query($sql,"The recurrent invoice could not be updated or added"); display_notification($note); $Mode = 'RESET'; } @@ -75,8 +64,7 @@ if ($Mode == 'Delete') if ($cancel_delete == 0) { - $sql="DELETE FROM ".TB_PREF."recurrent_invoices WHERE id=".db_escape($selected_id); - db_query($sql,"could not delete recurrent invoice"); + delete_recurrent_invoice($selected_id); display_notification(_('Selected recurrent invoice has been deleted')); } //end if Delete area @@ -89,16 +77,8 @@ if ($Mode == 'RESET') unset($_POST); } //------------------------------------------------------------------------------------------------- -function get_sales_group_name($group_no) -{ - $sql = "SELECT description FROM ".TB_PREF."groups WHERE id = ".db_escape($group_no); - $result = db_query($sql, "could not get group"); - $row = db_fetch($result); - return $row[0]; -} -$sql = "SELECT * FROM ".TB_PREF."recurrent_invoices ORDER BY description, group_no, debtor_no"; -$result = db_query($sql,"could not get recurrent invoices"); +$result = get_recurrent_invoices(); start_form(); start_table("$table_style width=70%"); @@ -149,10 +129,7 @@ if ($selected_id != -1) { if ($Mode == 'Edit') { //editing an existing area - $sql = "SELECT * FROM ".TB_PREF."recurrent_invoices WHERE id=".db_escape($selected_id); - - $result = db_query($sql,"could not get recurrent invoice"); - $myrow = db_fetch($result); + $myrow = get_recurrent_invoice($selected_id); $_POST['description'] = $myrow["description"]; $_POST['order_no'] = $myrow["order_no"]; diff --git a/sales/manage/sales_areas.php b/sales/manage/sales_areas.php index 8bb0ffd1..1b528388 100644 --- a/sales/manage/sales_areas.php +++ b/sales/manage/sales_areas.php @@ -35,16 +35,15 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') { if ($selected_id != -1) { - $sql = "UPDATE ".TB_PREF."areas SET description=".db_escape($_POST['description'])." WHERE area_code = ".db_escape($selected_id); + update_sales_area($selected_id, $_POST['description']); $note = _('Selected sales area has been updated'); } else { - $sql = "INSERT INTO ".TB_PREF."areas (description) VALUES (".db_escape($_POST['description']) . ")"; + add_sales_area($_POST['description']); $note = _('New sales area has been added'); } - db_query($sql,"The sales area could not be updated or added"); display_notification($note); $Mode = 'RESET'; } @@ -57,18 +56,14 @@ if ($Mode == 'Delete') // PREVENT DELETES IF DEPENDENT RECORDS IN 'debtors_master' - $sql= "SELECT COUNT(*) FROM ".TB_PREF."cust_branch WHERE area=".db_escape($selected_id); - $result = db_query($sql,"check failed"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (key_in_foreign_table($selected_id, 'cust_branch', 'area')) { $cancel_delete = 1; display_error(_("Cannot delete this area because customer branches have been created using this area.")); } if ($cancel_delete == 0) { - $sql="DELETE FROM ".TB_PREF."areas WHERE area_code=".db_escape($selected_id); - db_query($sql,"could not delete sales area"); + delete_sales_area($selected_id); display_notification(_('Selected sales area has been deleted')); } //end if Delete area @@ -85,9 +80,7 @@ if ($Mode == 'RESET') //------------------------------------------------------------------------------------------------- -$sql = "SELECT * FROM ".TB_PREF."areas"; -if (!check_value('show_inactive')) $sql .= " WHERE !inactive"; -$result = db_query($sql,"could not get areas"); +$result = get_sales_areas(check_value('show_inactive')); start_form(); start_table("$table_style width=30%"); @@ -124,10 +117,7 @@ if ($selected_id != -1) { if ($Mode == 'Edit') { //editing an existing area - $sql = "SELECT * FROM ".TB_PREF."areas WHERE area_code=".db_escape($selected_id); - - $result = db_query($sql,"could not get area"); - $myrow = db_fetch($result); + $myrow = get_sales_area($selected_id); $_POST['description'] = $myrow["description"]; } diff --git a/sales/manage/sales_groups.php b/sales/manage/sales_groups.php index 2ee95bad..7521182a 100644 --- a/sales/manage/sales_groups.php +++ b/sales/manage/sales_groups.php @@ -35,16 +35,15 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') { if ($selected_id != -1) { - $sql = "UPDATE ".TB_PREF."groups SET description=".db_escape($_POST['description'])." WHERE id = ".db_escape($selected_id); + update_sales_group($selected_id, $_POST['description']); $note = _('Selected sales group has been updated'); } else { - $sql = "INSERT INTO ".TB_PREF."groups (description) VALUES (".db_escape($_POST['description']) . ")"; + add_sales_group($_POST['description']); $note = _('New sales group has been added'); } - db_query($sql,"The sales group could not be updated or added"); display_notification($note); $Mode = 'RESET'; } @@ -57,19 +56,14 @@ if ($Mode == 'Delete') // PREVENT DELETES IF DEPENDENT RECORDS IN 'debtors_master' - $sql= "SELECT COUNT(*) FROM ".TB_PREF."cust_branch WHERE group_no=".db_escape($selected_id); - $result = db_query($sql,"check failed"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (key_in_foreign_table($selected_id, 'cust_branch', 'group_no')) { $cancel_delete = 1; display_error(_("Cannot delete this group because customers have been created using this group.")); } if ($cancel_delete == 0) { - $sql="DELETE FROM ".TB_PREF."groups WHERE id=".db_escape($selected_id); - db_query($sql,"could not delete sales group"); - + delete_sales_group($selected_id); display_notification(_('Selected sales group has been deleted')); } //end if Delete area $Mode = 'RESET'; @@ -84,10 +78,7 @@ if ($Mode == 'RESET') } //------------------------------------------------------------------------------------------------- -$sql = "SELECT * FROM ".TB_PREF."groups"; -if (!check_value('show_inactive')) $sql .= " WHERE !inactive"; -$sql .= " ORDER BY description"; -$result = db_query($sql,"could not get groups"); +$result = get_sales_groups(check_value('show_inactive')); start_form(); start_table("$table_style width=30%"); @@ -110,9 +101,7 @@ while ($myrow = db_fetch($result)) } inactive_control_row($th); -end_table(); - -echo '
'; +end_table(1); //------------------------------------------------------------------------------------------------- @@ -122,10 +111,7 @@ if ($selected_id != -1) { if ($Mode == 'Edit') { //editing an existing area - $sql = "SELECT * FROM ".TB_PREF."groups WHERE id=".db_escape($selected_id); - - $result = db_query($sql,"could not get group"); - $myrow = db_fetch($result); + $myrow = get_sales_group($selected_id); $_POST['description'] = $myrow["description"]; } diff --git a/sales/manage/sales_people.php b/sales/manage/sales_people.php index 1c398b61..55a5f12f 100644 --- a/sales/manage/sales_people.php +++ b/sales/manage/sales_people.php @@ -48,31 +48,16 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') if ($selected_id != -1) { /*selected_id could also exist if submit had not been clicked this code would not run in this case cos submit is false of course see the delete code below*/ - - $sql = "UPDATE ".TB_PREF."salesman SET salesman_name=".db_escape($_POST['salesman_name']) . ", - salesman_phone=".db_escape($_POST['salesman_phone']) . ", - salesman_fax=".db_escape($_POST['salesman_fax']) . ", - salesman_email=".db_escape($_POST['salesman_email']) . ", - provision=".input_num('provision').", - break_pt=".input_num('break_pt').", - provision2=".input_num('provision2')." - WHERE salesman_code = ".db_escape($selected_id); + update_salesman($selected_id, $_POST['salesman_name'], $_POST['salesman_phone'], $_POST['salesman_fax'], + $_POST['salesman_email'], input_num('provision'), input_num('break_pt'), input_num('provision2')); } else { /*Selected group is null cos no item selected on first time round so must be adding a record must be submitting new entries in the new Sales-person form */ - $sql = "INSERT INTO ".TB_PREF."salesman (salesman_name, salesman_phone, salesman_fax, salesman_email, - provision, break_pt, provision2) - VALUES (".db_escape($_POST['salesman_name']) . ", " - .db_escape($_POST['salesman_phone']) . ", " - .db_escape($_POST['salesman_fax']) . ", " - .db_escape($_POST['salesman_email']) . ", ". - input_num('provision').", ".input_num('break_pt').", " - .input_num('provision2').")"; + add_salesman($_POST['salesman_name'], $_POST['salesman_phone'], $_POST['salesman_fax'], + $_POST['salesman_email'], input_num('provision'), input_num('break_pt'), input_num('provision2')); } - //run the sql from either of the above possibilites - db_query($sql,"The insert or update of the sales person failed"); if ($selected_id != -1) display_notification(_('Selected sales person data have been updated')); else @@ -86,17 +71,13 @@ if ($Mode == 'Delete') // PREVENT DELETES IF DEPENDENT RECORDS IN 'debtors_master' - $sql= "SELECT COUNT(*) FROM ".TB_PREF."cust_branch WHERE salesman=".db_escape($selected_id); - $result = db_query($sql,"check failed"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (key_in_foreign_table($selected_id, 'cust_branch', 'salesman')) { display_error("Cannot delete this sales-person because branches are set up referring to this sales-person - first alter the branches concerned."); } else { - $sql="DELETE FROM ".TB_PREF."salesman WHERE salesman_code=".db_escape($selected_id); - db_query($sql,"The sales-person could not be deleted"); + delete_salesman($selected_id); display_notification(_('Selected sales person data have been deleted')); } $Mode = 'RESET'; @@ -111,9 +92,7 @@ if ($Mode == 'RESET') } //------------------------------------------------------------------------------------------------ -$sql = "SELECT * FROM ".TB_PREF."salesman"; -if (!check_value('show_inactive')) $sql .= " WHERE !inactive"; -$result = db_query($sql,"could not get sales persons"); +$result = get_salesmen(check_value('show_inactive')); start_form(); start_table("$table_style width=60%"); @@ -154,10 +133,7 @@ if ($selected_id != -1) { if ($Mode == 'Edit') { //editing an existing Sales-person - $sql = "SELECT * FROM ".TB_PREF."salesman WHERE salesman_code=".db_escape($selected_id); - - $result = db_query($sql,"could not get sales person"); - $myrow = db_fetch($result); + $myrow = get_salesman($selected_id); $_POST['salesman_name'] = $myrow["salesman_name"]; $_POST['salesman_phone'] = $myrow["salesman_phone"]; diff --git a/sales/manage/sales_points.php b/sales/manage/sales_points.php index 915911c3..b3947548 100644 --- a/sales/manage/sales_points.php +++ b/sales/manage/sales_points.php @@ -64,9 +64,8 @@ if ($Mode=='UPDATE_ITEM' && can_process()) if ($Mode == 'Delete') { - $sql = "SELECT * FROM ".TB_PREF."users WHERE print_profile=".db_escape($selected_id); - $res = db_query($sql, "canot check pos usage"); - if (db_num_rows($res)) { + if (key_in_foreign_table($selected_id, 'users', 'print_profile')) + { display_error(_("Cannot delete this POS because it is used in users setup.")); } else { delete_sales_point($selected_id); diff --git a/sales/manage/sales_types.php b/sales/manage/sales_types.php index 7ebd7685..bc54f635 100644 --- a/sales/manage/sales_types.php +++ b/sales/manage/sales_types.php @@ -65,26 +65,15 @@ if ($Mode=='UPDATE_ITEM' && can_process()) if ($Mode == 'Delete') { // PREVENT DELETES IF DEPENDENT RECORDS IN 'debtor_trans' - - $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtor_trans WHERE tpe=".db_escape($selected_id); - $result = db_query($sql,"check failed"); - check_db_error("The number of transactions using this Sales type record could not be retrieved", $sql); - - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + + if (key_in_foreign_table($selected_id, 'debtor_trans', 'tpe')) { display_error(_("Cannot delete this sale type because customer transactions have been created using this sales type.")); } else { - - $sql = "SELECT COUNT(*) FROM ".TB_PREF."debtors_master WHERE sales_type=".db_escape($selected_id); - $result = db_query($sql,"check failed"); - check_db_error("The number of customers using this Sales type record could not be retrieved", $sql); - - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (key_in_foreign_table($selected_id, 'debtors_master', 'sales_type')) { display_error(_("Cannot delete this sale type because customers are currently set up to use this sales type.")); } diff --git a/sales/view/view_sales_order.php b/sales/view/view_sales_order.php index ebf60096..69273e03 100644 --- a/sales/view/view_sales_order.php +++ b/sales/view/view_sales_order.php @@ -94,8 +94,7 @@ if ($_GET['trans_type'] != ST_SALESQUOTE) $th = array(_("#"), _("Ref"), _("Date"), _("Total")); table_header($th); - $sql = "SELECT * FROM ".TB_PREF."debtor_trans WHERE type=".ST_CUSTDELIVERY." AND order_=".db_escape($_GET['trans_no']); - $result = db_query($sql,"The related delivery notes could not be retreived"); + $result = get_related_documents(ST_CUSTDELIVERY, $_GET['trans_no']); $delivery_total = 0; $k = 0; @@ -127,8 +126,7 @@ if ($_GET['trans_type'] != ST_SALESQUOTE) $th = array(_("#"), _("Ref"), _("Date"), _("Total")); table_header($th); - $sql = "SELECT * FROM ".TB_PREF."debtor_trans WHERE type=".ST_SALESINVOICE." AND order_=".db_escape($_GET['trans_no']); - $result = db_query($sql,"The related invoices could not be retreived"); + $result = get_related_documents(ST_SALESINVOICE, $_GET['trans_no']); $invoices_total = 0; $k = 0; @@ -159,8 +157,7 @@ if ($_GET['trans_type'] != ST_SALESQUOTE) $th = array(_("#"), _("Ref"), _("Date"), _("Total")); table_header($th); - $sql = "SELECT * FROM ".TB_PREF."debtor_trans WHERE type=".ST_CUSTCREDIT." AND order_=".db_escape($_GET['trans_no']); - $result = db_query($sql,"The related credit notes could not be retreived"); + $result = get_related_documents(ST_CUSTCREDIT, $_GET['trans_no']); $credits_total = 0; $k = 0; diff --git a/taxes/db/tax_types_db.inc b/taxes/db/tax_types_db.inc index e57ffb35..f42470ef 100644 --- a/taxes/db/tax_types_db.inc +++ b/taxes/db/tax_types_db.inc @@ -113,4 +113,18 @@ function is_tax_gl_unique($gl_code, $gl_code2=-1, $selected_id=-1) { return $gl_code2 == -1 ? ($row[0] <= 1) : ($row[0] == 0); } + +function is_tax_account($account_code) +{ + $sql= "SELECT id FROM ".TB_PREF."tax_types WHERE + sales_gl_code=".db_escape($account_code)." OR purchasing_gl_code=".db_escape($account_code); + $result = db_query($sql, "checking account is tax account"); + if (db_num_rows($result) > 0) { + $acct = db_fetch($result); + return $acct['id']; + } else + return false; +} + + ?> \ No newline at end of file diff --git a/taxes/item_tax_types.php b/taxes/item_tax_types.php index f1414efc..7e3dfce6 100644 --- a/taxes/item_tax_types.php +++ b/taxes/item_tax_types.php @@ -72,10 +72,7 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') function can_delete($selected_id) { - $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_master WHERE tax_type_id=".db_escape($selected_id); - $result = db_query($sql, "could not query stock master"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (key_in_foreign_table($selected_id, 'stock_master', 'tax_type_id')) { display_error(_("Cannot delete this item tax type because items have been created referring to it.")); return false; diff --git a/taxes/tax_calc.inc b/taxes/tax_calc.inc index 7107cdca..0da0a74e 100644 --- a/taxes/tax_calc.inc +++ b/taxes/tax_calc.inc @@ -207,16 +207,4 @@ function get_tax_for_items($items, $prices, $shipping_cost, $tax_group, $tax_inc return $ret_tax_array; } -function is_tax_account($account_code) -{ - $sql= "SELECT id FROM ".TB_PREF."tax_types WHERE - sales_gl_code=".db_escape($account_code)." OR purchasing_gl_code=".db_escape($account_code); - $result = db_query($sql, "checking account is tax account"); - if (db_num_rows($result) > 0) { - $acct = db_fetch($result); - return $acct['id']; - } else - return false; -} - ?> \ No newline at end of file diff --git a/taxes/tax_groups.php b/taxes/tax_groups.php index be683482..13990c38 100644 --- a/taxes/tax_groups.php +++ b/taxes/tax_groups.php @@ -99,21 +99,15 @@ function can_delete($selected_id) { if ($selected_id == -1) return false; - $sql = "SELECT COUNT(*) FROM ".TB_PREF."cust_branch WHERE tax_group_id=".db_escape($selected_id); - $result = db_query($sql, "could not query customers"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (key_in_foreign_table($selected_id, 'cust_branch', 'tax_group_id')) { - display_note(_("Cannot delete this tax group because customer branches been created referring to it.")); + display_error(_("Cannot delete this tax group because customer branches been created referring to it.")); return false; } - $sql = "SELECT COUNT(*) FROM ".TB_PREF."suppliers WHERE tax_group_id=".db_escape($selected_id); - $result = db_query($sql, "could not query suppliers"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (key_in_foreign_table($selected_id, 'suppliers', 'tax_group_id')) { - display_note(_("Cannot delete this tax group because suppliers been created referring to it.")); + display_error(_("Cannot delete this tax group because suppliers been created referring to it.")); return false; } diff --git a/taxes/tax_types.php b/taxes/tax_types.php index c7669183..87b3f255 100644 --- a/taxes/tax_types.php +++ b/taxes/tax_types.php @@ -72,10 +72,7 @@ if ($Mode=='UPDATE_ITEM' && can_process()) function can_delete($selected_id) { - $sql= "SELECT COUNT(*) FROM ".TB_PREF."tax_group_items WHERE tax_type_id=".db_escape($selected_id); - $result = db_query($sql, "could not query tax groups"); - $myrow = db_fetch_row($result); - if ($myrow[0] > 0) + if (key_in_foreign_table($selected_id, 'tax_group_items', 'tax_type_id')) { display_error(_("Cannot delete this tax type because tax groups been created referring to it.")); -- 2.30.2