From acfca8f2a9dbcd41058c165adcfbfee32bbb02bd Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Sun, 4 Apr 2010 12:17:46 +0000 Subject: [PATCH] Added per customer document language. --- sales/includes/db/branches_db.inc | 16 +++++--- sales/includes/db/cust_trans_db.inc | 36 ++++++++++-------- sales/includes/db/customers_db.inc | 19 ++++++---- sales/includes/db/sales_order_db.inc | 55 ++++++++++++++-------------- 4 files changed, 69 insertions(+), 57 deletions(-) diff --git a/sales/includes/db/branches_db.inc b/sales/includes/db/branches_db.inc index 3728dbbc..2af7e368 100644 --- a/sales/includes/db/branches_db.inc +++ b/sales/includes/db/branches_db.inc @@ -11,12 +11,13 @@ ***********************************************************************/ 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) + $payment_discount_account, $default_location, $br_post_address, $disable_trans, $group_no, + $default_ship_via, $notes, $rep_lang) { $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) + br_post_address, disable_trans, group_no, default_ship_via, notes, rep_lang) VALUES (".db_escape($customer_id). ",".db_escape($br_name) . ", " .db_escape($br_ref) . ", " .db_escape($br_address) . ", ".db_escape($salesman) . ", " @@ -33,13 +34,15 @@ function add_branch($customer_id, $br_name, $br_ref, $br_address, $phone, $phone .db_escape($disable_trans) . ", " .db_escape($group_no) . ", " .db_escape($default_ship_via). ", " - .db_escape($notes) . ")"; + .db_escape($notes). ", " + .db_escape($rep_lang ? $rep_lang : null, true) . ")"; 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) + $payment_discount_account, $default_location, $br_post_address, $disable_trans, $group_no, + $default_ship_via, $notes, $rep_lang) { $sql = "UPDATE ".TB_PREF."cust_branch SET br_name = " . db_escape($br_name) . ", branch_ref = " . db_escape($br_ref) . ", @@ -61,7 +64,8 @@ function update_branch($customer_id, $branch_code, $br_name, $br_ref, $br_addres disable_trans=".db_escape($disable_trans) . ", group_no=".db_escape($group_no) . ", default_ship_via=".db_escape($default_ship_via) . ", - notes=".db_escape($notes) . " + notes=".db_escape($notes) . ", + rep_lang=".db_escape($rep_lang ? $rep_lang : null, true) . " WHERE branch_code =".db_escape($branch_code) . " AND debtor_no=".db_escape($customer_id); db_query($sql,"The branch record could not be updated"); @@ -134,7 +138,7 @@ function get_cust_branches_from_group($group_no) function get_default_info_for_branch($customer_id) { - $sql = "SELECT name, address, email, debtor_ref + $sql = "SELECT name, address, email, debtor_ref, rep_lang FROM ".TB_PREF."debtors_master WHERE debtor_no = ".db_escape($customer_id); $result = db_query($sql,"check failed"); return db_fetch($result); diff --git a/sales/includes/db/cust_trans_db.inc b/sales/includes/db/cust_trans_db.inc index 0d5d5ff9..c9fccf11 100644 --- a/sales/includes/db/cust_trans_db.inc +++ b/sales/includes/db/cust_trans_db.inc @@ -147,10 +147,11 @@ function write_customer_trans($trans_type, $trans_no, $debtor_no, $BranchNo, function get_customer_trans($trans_id, $trans_type) { - $sql = "SELECT ".TB_PREF."debtor_trans.*, - ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total, - ".TB_PREF."debtors_master.name AS DebtorName, ".TB_PREF."debtors_master.address, ".TB_PREF."debtors_master.email AS email2, - ".TB_PREF."debtors_master.curr_code, ".TB_PREF."debtors_master.tax_id "; + $sql = "SELECT trans.*," + ."ov_amount+ov_gst+ov_freight+ov_freight_tax+ov_discount AS Total," + ."cust.name AS DebtorName, cust.address, " + ."cust.email AS email2, cust.curr_code, " + ."cust.tax_id "; if ($trans_type == ST_CUSTPAYMENT) { // it's a payment so also get the bank account @@ -163,14 +164,15 @@ function get_customer_trans($trans_id, $trans_type) $sql .= ", ".TB_PREF."shippers.shipper_name, " .TB_PREF."sales_types.sales_type, " .TB_PREF."sales_types.tax_included, " - .TB_PREF."cust_branch.*, " - .TB_PREF."debtors_master.discount, " + ."branch.*, " + ."cust.discount, " .TB_PREF."tax_groups.name AS tax_group_name, " .TB_PREF."tax_groups.id AS tax_group_id "; } + $sql .= ", IF(ISNULL(branch.rep_lang),cust.rep_lang,branch.rep_lang) AS rep_lang"; - $sql .= " FROM ".TB_PREF."debtor_trans, ".TB_PREF."debtors_master "; + $sql .= " FROM ".TB_PREF."debtor_trans trans, ".TB_PREF."debtors_master cust"; if ($trans_type == ST_CUSTPAYMENT) { // it's a payment so also get the bank account @@ -179,12 +181,15 @@ function get_customer_trans($trans_id, $trans_type) if ($trans_type == ST_SALESINVOICE || $trans_type == ST_CUSTCREDIT || $trans_type == ST_CUSTDELIVERY) { // it's an invoice so also get the shipper, salestypes - $sql .= ", ".TB_PREF."shippers, ".TB_PREF."sales_types, ".TB_PREF."cust_branch, ".TB_PREF."tax_groups "; + $sql .= ", ".TB_PREF."shippers, " + .TB_PREF."sales_types, " + .TB_PREF."cust_branch branch, " + .TB_PREF."tax_groups "; } - $sql .= " WHERE ".TB_PREF."debtor_trans.trans_no=".db_escape($trans_id)." - AND ".TB_PREF."debtor_trans.type=".db_escape($trans_type)." - AND ".TB_PREF."debtor_trans.debtor_no=".TB_PREF."debtors_master.debtor_no"; + $sql .= " WHERE trans.trans_no=".db_escape($trans_id)." + AND trans.type=".db_escape($trans_type)." + AND trans.debtor_no=cust.debtor_no"; if ($trans_type == ST_CUSTPAYMENT) { // it's a payment so also get the bank account @@ -194,10 +199,10 @@ function get_customer_trans($trans_id, $trans_type) } if ($trans_type == ST_SALESINVOICE || $trans_type == ST_CUSTCREDIT || $trans_type == ST_CUSTDELIVERY) { // it's an invoice so also get the shipper - $sql .= " AND ".TB_PREF."shippers.shipper_id=".TB_PREF."debtor_trans.ship_via - AND ".TB_PREF."sales_types.id = ".TB_PREF."debtor_trans.tpe - AND ".TB_PREF."cust_branch.branch_code = ".TB_PREF."debtor_trans.branch_code - AND ".TB_PREF."cust_branch.tax_group_id = ".TB_PREF."tax_groups.id "; + $sql .= " AND ".TB_PREF."shippers.shipper_id=trans.ship_via + AND ".TB_PREF."sales_types.id = trans.tpe + AND branch.branch_code = trans.branch_code + AND branch.tax_group_id = ".TB_PREF."tax_groups.id "; } $result = db_query($sql, "Cannot retreive a debtor transaction"); @@ -214,7 +219,6 @@ function get_customer_trans($trans_id, $trans_type) exit; } - //return db_fetch($result); $row = db_fetch($result); $row['email'] = $row['email2']; return $row; diff --git a/sales/includes/db/customers_db.inc b/sales/includes/db/customers_db.inc index 7ec7b297..86a8f2b5 100644 --- a/sales/includes/db/customers_db.inc +++ b/sales/includes/db/customers_db.inc @@ -10,24 +10,26 @@ 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) -{ +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, $rep_lang) +{ $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) .", " + sales_type, notes, rep_lang) 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_escape($sales_type).", ".db_escape($notes).", ".db_escape($rep_lang ? $rep_lang : null, true) . ")"; 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) +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, $rep_lang) { $sql = "UPDATE ".TB_PREF."debtors_master SET name=" . db_escape($CustName) . ", debtor_ref=" . db_escape($cust_ref) . ", @@ -43,7 +45,8 @@ function update_customer($customer_id, $CustName, $cust_ref, $address, $tax_id, pymt_discount=" . $pymt_discount . ", credit_limit=" . $credit_limit . ", sales_type = ".db_escape($sales_type) . ", - notes=".db_escape($notes) . " + notes=".db_escape($notes) . ", + rep_lang=".db_escape($rep_lang ? $rep_lang : null, true) . " WHERE debtor_no = ".db_escape($customer_id); db_query($sql,"The customer could not be updated"); diff --git a/sales/includes/db/sales_order_db.inc b/sales/includes/db/sales_order_db.inc index fd6dd985..8ec647bb 100644 --- a/sales/includes/db/sales_order_db.inc +++ b/sales/includes/db/sales_order_db.inc @@ -269,33 +269,34 @@ function update_sales_order($order) function get_sales_order_header($order_no, $trans_type) { - $sql = "SELECT ".TB_PREF."sales_orders.*, " - .TB_PREF."debtors_master.name, " - .TB_PREF."debtors_master.curr_code, " - .TB_PREF."debtors_master.email AS master_email, " - .TB_PREF."locations.location_name, " - .TB_PREF."debtors_master.discount, " - .TB_PREF."sales_types.sales_type, " - .TB_PREF."sales_types.id AS sales_type_id, " - .TB_PREF."sales_types.tax_included, " - .TB_PREF."shippers.shipper_name, " - .TB_PREF."tax_groups.name AS tax_group_name , " - .TB_PREF."tax_groups.id AS tax_group_id - FROM ".TB_PREF."sales_orders, " - .TB_PREF."debtors_master, " - .TB_PREF."sales_types, " - .TB_PREF."tax_groups, " - .TB_PREF."cust_branch, " - .TB_PREF."locations, " - .TB_PREF."shippers - WHERE ".TB_PREF."sales_orders.order_type=".TB_PREF."sales_types.id - AND ".TB_PREF."cust_branch.branch_code = ".TB_PREF."sales_orders.branch_code - AND ".TB_PREF."cust_branch.tax_group_id = ".TB_PREF."tax_groups.id - AND ".TB_PREF."sales_orders.debtor_no = ".TB_PREF."debtors_master.debtor_no - AND ".TB_PREF."locations.loc_code = ".TB_PREF."sales_orders.from_stk_loc - AND ".TB_PREF."shippers.shipper_id = ".TB_PREF."sales_orders.ship_via - AND ".TB_PREF."sales_orders.trans_type = " . db_escape($trans_type) ." - AND ".TB_PREF."sales_orders.order_no = " . db_escape($order_no ); + $sql = "SELECT sorder.*, " + ."cust.name, " + ."cust.curr_code, " + ."cust.email AS master_email, " + ."loc.location_name, " + ."cust.discount, " + ."stype.sales_type, " + ."stype.id AS sales_type_id, " + ."stype.tax_included, " + ."ship.shipper_name, " + ."tax_group.name AS tax_group_name , " + ."tax_group.id AS tax_group_id," + ."IF(ISNULL(branch.rep_lang),cust.rep_lang,branch.rep_lang) AS rep_lang " + ."FROM ".TB_PREF."sales_orders sorder, " + .TB_PREF."debtors_master cust, " + .TB_PREF."sales_types stype, " + .TB_PREF."tax_groups tax_group, " + .TB_PREF."cust_branch branch, " + .TB_PREF."locations loc, " + .TB_PREF."shippers ship + WHERE sorder.order_type=stype.id + AND branch.branch_code = sorder.branch_code + AND branch.tax_group_id = tax_group.id + AND sorder.debtor_no = cust.debtor_no + AND loc.loc_code = sorder.from_stk_loc + AND ship.shipper_id = sorder.ship_via + AND sorder.trans_type = " . db_escape($trans_type) ." + AND sorder.order_no = " . db_escape($order_no ); $result = db_query($sql, "order Retreival"); -- 2.30.2