X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;ds=sidebyside;f=sales%2Fincludes%2Fdb%2Fbranches_db.inc;h=09b1fbe4f28af171c013cd574c5bbc028b5bdf9c;hb=91bede4226ba8b45151f0501ac88eb946ed5f2a1;hp=dc0cdbd2e405ba2a83a2b643a4070eab9013d63d;hpb=963bc21ea092322d5750d4c0914b5e532e7d2467;p=fa-stable.git diff --git a/sales/includes/db/branches_db.inc b/sales/includes/db/branches_db.inc index dc0cdbd2..09b1fbe4 100644 --- a/sales/includes/db/branches_db.inc +++ b/sales/includes/db/branches_db.inc @@ -9,21 +9,20 @@ 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) +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, $bank_account) { $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) + 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, bank_account) 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($area) . "," + .db_escape($tax_group_id) . ", " .db_escape($sales_account) . ", " .db_escape($receivables_account) . ", " .db_escape($payment_discount_account) . ", " @@ -33,24 +32,21 @@ 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($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, $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) +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, $bank_account) { $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) . ", @@ -61,7 +57,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) . ", + 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"); @@ -69,6 +66,8 @@ function update_branch($customer_id, $branch_code, $br_name, $br_ref, $br_addres function delete_branch($customer_id, $branch_code) { + delete_entity_contacts('cust_branch', $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"); } @@ -91,7 +90,7 @@ function get_branch($branch_id) $result = db_query($sql, "Cannot retreive a customer branch"); - return db_fetch($result); + return db_fetch($result); } function get_cust_branch($customer_id, $branch_code) @@ -110,7 +109,7 @@ function get_branch_accounts($branch_id) $result = db_query($sql, "Cannot retreive a customer branch"); - return db_fetch($result); + return db_fetch($result); } function get_branch_name($branch_id) @@ -120,7 +119,7 @@ function get_branch_name($branch_id) $result = db_query($sql,"could not retreive name for branch" . $branch_id); - $myrow = db_fetch_row($result); + $myrow = db_fetch_row($result); return $myrow[0]; } @@ -134,37 +133,124 @@ 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, 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, " + ."p.name as contact_name, " ."s.salesman_name, " ."a.description, " - ."b.phone, " - ."b.fax, " - ."b.email, " + ."p.phone, " + ."p.fax, " + ."p.email, " ."t.name AS tax_group_name, " ."b.inactive - FROM ".TB_PREF."cust_branch b, " - .TB_PREF."debtors_master c, " + 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," .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 + 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']); if (!get_post('show_inactive')) $sql .= " AND !b.inactive"; + $sql .= " GROUP BY b.branch_code ORDER BY branch_ref"; + return $sql; } +/* + 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 +*/ +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; +} + +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); + + 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, 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).($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; // 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