X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sales%2Fincludes%2Fdb%2Fbranches_db.inc;h=e51de6f149234bd8004084fa8545d0f0919025ae;hb=efa2530a7c385a329c3fc76f4560b28ba97c3efe;hp=bc9543b41f0a8b77b1064806b636f7081cf7a195;hpb=bd3023c1b5e64429043358a691b3aae20eb54c09;p=fa-stable.git diff --git a/sales/includes/db/branches_db.inc b/sales/includes/db/branches_db.inc index bc9543b4..e51de6f1 100644 --- a/sales/includes/db/branches_db.inc +++ b/sales/includes/db/branches_db.inc @@ -11,13 +11,13 @@ ***********************************************************************/ function add_branch($customer_id, $br_name, $br_ref, $br_address, $salesman, $area, $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, $group_no, + $default_ship_via, $notes, $bank_account) { $sql = "INSERT INTO ".TB_PREF."cust_branch (debtor_no, br_name, branch_ref, br_address, salesman, area, 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, group_no, default_ship_via, notes, bank_account) VALUES (".db_escape($customer_id). ",".db_escape($br_name) . ", " .db_escape($br_ref) . ", " .db_escape($br_address) . ", ".db_escape($salesman) . ", " @@ -29,17 +29,17 @@ function add_branch($customer_id, $br_name, $br_ref, $br_address, $salesman, $ar .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_escape($notes). ", " + .db_escape($bank_account, true).")"; db_query($sql,"The branch record could not be added"); } function update_branch($customer_id, $branch_code, $br_name, $br_ref, $br_address, $salesman, $area, $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, $group_no, + $default_ship_via, $notes, $bank_account) { $sql = "UPDATE ".TB_PREF."cust_branch SET br_name = " . db_escape($br_name) . ", branch_ref = " . db_escape($br_ref) . ", @@ -53,10 +53,10 @@ function update_branch($customer_id, $branch_code, $br_name, $br_ref, $br_addres 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) . " + notes=".db_escape($notes) . ", + bank_account=".db_escape($bank_account, 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"); @@ -81,13 +81,15 @@ function branch_in_foreign_table($customer_id, $branch_code, $table) function get_branch($branch_id) { - $sql = "SELECT ".TB_PREF."cust_branch.*,".TB_PREF."salesman.salesman_name - FROM ".TB_PREF."cust_branch, ".TB_PREF."salesman - WHERE ".TB_PREF."cust_branch.salesman=".TB_PREF."salesman.salesman_code - AND branch_code=".db_escape($branch_id); - + $sql = "SELECT branch.*, salesman.salesman_name + FROM " + .TB_PREF."cust_branch branch," + .TB_PREF."salesman salesman + WHERE branch.salesman=salesman.salesman_code + AND branch_code=".db_escape($branch_id); + $result = db_query($sql, "Cannot retreive a customer branch"); - + return db_fetch($result); } @@ -137,7 +139,7 @@ function get_default_info_for_branch($customer_id) return db_fetch($result); } -function get_sql_for_customer_branches() +function get_sql_for_customer_branches($customer_id) { $sql = "SELECT " ."b.branch_code, " @@ -161,50 +163,133 @@ function get_sql_for_customer_branches() WHERE 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']); + AND b.debtor_no = ".db_escape($customer_id); if (!get_post('show_inactive')) $sql .= " AND !b.inactive"; - $sql .= " ORDER BY branch_ref"; + $sql .= " GROUP BY b.branch_code ORDER BY branch_ref"; return $sql; } - -function get_branch_contacts($branch_code, $action=null, $customer_id=null) -{ /* - $ret = get_crm_persons('cust_branch', $action, $branch_code); - if ($customer_id && !db_num_rows($ret)) { - $ret = get_crm_persons('customer', $action, $customer_id); - } - return $ret; + Get contacts of given type for customer branch. + $branch_code - branch id + $action - type of contact + $customer_id - if passed: get also customer contacts for given action + $default - return only single record selected with defaults order defined in $defs array, + otherways get all $action contacts */ - $sql = "SELECT p.*, r.action, r.type FROM ".TB_PREF."crm_persons p," - .TB_PREF."crm_contacts r WHERE r.person_id=p.id AND r.type='cust_branch' - AND r.entity_id=".db_escape($branch_code); +function get_branch_contacts($branch_code, $action=null, $customer_id=null, $default = true) +{ + $defs = array('cust_branch.'.$action, + 'customer.'.$action, + 'cust_branch.general', + 'customer.general'); + + $sql = "SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type + FROM " + .TB_PREF."crm_persons p," + .TB_PREF."crm_contacts r + WHERE r.person_id=p.id AND ((r.type='cust_branch' + AND r.entity_id=".db_escape($branch_code).')'; + if($customer_id) { + $sql .= " OR (r.type='customer' AND r.entity_id=".db_escape($customer_id).")"; + } + $sql .= ')'; + + if ($action) + $sql .= ' AND (r.action='.db_escape($action).($default ? " OR r.action='general'" : '').')'; + + $res = db_query($sql, "can't retrieve branch contacts"); + + $results = array(); + while($contact = db_fetch($res)) + $results[] = $contact; + + if ($results && $default) { + // select first available contact in $defs order + foreach($defs as $type) { + if ($n = array_search_value($type, $results, 'ext_type')) + return $n; + } + return null; + } + return $results; +} - if ($action) - $sql .= ' AND r.action='.db_escape($action); +function _get_branch_contacts($branch_code, $action=null, $customer_id=null, $default = false) +{ + $sql = "SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type + FROM ".TB_PREF."crm_persons p," + .TB_PREF."crm_contacts r + WHERE r.person_id=p.id AND r.type='cust_branch' + AND r.entity_id=".db_escape($branch_code); - $sql .= " GROUP BY person_id"; + if ($action) + $sql .= ' AND (r.action='.db_escape($action).($default ? " OR r.action='general'":'').')'; if($customer_id) { - $sql = "($sql) UNION (SELECT p.*, r.action, r.type FROM ".TB_PREF."crm_persons p," - .TB_PREF."crm_contacts r WHERE r.person_id=p.id AND r.type='customer' + $sql = "($sql) UNION (SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type + FROM ".TB_PREF."crm_persons p," + .TB_PREF."crm_contacts r + WHERE r.person_id=p.id AND r.type='customer' AND r.entity_id=".db_escape($customer_id); if ($action) - $sql .= ' AND r.action='.db_escape($action); - $sql .= " GROUP BY person_id)"; + $sql .= ' AND (r.action='.db_escape($action).($default ? " OR r.action='general'":'').')'; + $sql .= ')'; } - + $res = db_query($sql, "can't retrieve branch contacts"); $results = array(); $type = ''; while($contact = db_fetch($res)) { - if ($type && $type != $contact['type']) break; + if ($type && $type != $contact['type']) break; // skip cust when branch contacts found $results[] = $contact; $type = $contact['type']; } + if ($results && $default) { + // here we have general and action contacts selected + if ($n = array_search_value($action, $results, 'action')) { + return $n; + } + // only general contact found + return $results[0]; + } return $results; } -?> \ No newline at end of file +function get_default_branch($customer_id, $ar_account=null) +{ + $sql = "SELECT * + FROM ".TB_PREF."cust_branch WHERE debtor_no = ".db_escape($customer_id); + if($ar_account) + $sql .= " AND receivables_account=".db_escape($ar_account); + $result = db_query($sql,"cannot retrieve default branch"); + return db_fetch($result); +} + +function get_branches_search($customer, $branch) +{ + global $SysPrefs; + + if (isset($SysPrefs->max_rows_in_search)) + $limit = $SysPrefs->max_rows_in_search; + else + $limit = 10; + + $sql = "SELECT + b.branch_code, + b.branch_ref, + b.br_name, + p.name as contact_name, + p.phone + FROM ".TB_PREF."cust_branch b + LEFT JOIN ".TB_PREF."crm_contacts c + ON c.entity_id=b.branch_code AND c.type='cust_branch' AND c.action='general' + LEFT JOIN ".TB_PREF."crm_persons p ON c.person_id=p.id + WHERE b.debtor_no = ".db_escape($customer)." + AND b.br_name LIKE " . db_escape("%" . $branch. "%") . " + ORDER BY b.br_name LIMIT 0,".(int)($limit); + + return db_query($sql, "Failed in retreiving branches list."); +} +