1<?php 2/********************************************************************** 3 Copyright (C) FrontAccounting, LLC. 4 Released under the terms of the GNU General Public License, GPL, 5 as published by the Free Software Foundation, either version 3 6 of the License, or (at your option) any later version. 7 This program is distributed in the hope that it will be useful, 8 but WITHOUT ANY WARRANTY; without even the implied warranty of 9 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 10 See the License here <http://www.gnu.org/licenses/gpl-3.0.html>. 11***********************************************************************/ 12function add_branch($customer_id, $br_name, $br_ref, $br_address, $salesman, $area, 13 $tax_group_id, $sales_account, $sales_discount_account, $receivables_account, 14 $payment_discount_account, $default_location, $br_post_address, $disable_trans, $group_no, 15 $default_ship_via, $notes) 16{ 17 $sql = "INSERT INTO ".TB_PREF."cust_branch (debtor_no, br_name, branch_ref, br_address, 18 salesman, area, tax_group_id, sales_account, receivables_account, payment_discount_account, 19 sales_discount_account, default_location, 20 br_post_address, disable_trans, group_no, default_ship_via, notes) 21 VALUES (".db_escape($customer_id). ",".db_escape($br_name) . ", " 22 .db_escape($br_ref) . ", " 23 .db_escape($br_address) . ", ".db_escape($salesman) . ", " 24 .db_escape($area) . "," 25 .db_escape($tax_group_id) . ", " 26 .db_escape($sales_account) . ", " 27 .db_escape($receivables_account) . ", " 28 .db_escape($payment_discount_account) . ", " 29 .db_escape($sales_discount_account) . ", " 30 .db_escape($default_location) . ", " 31 .db_escape($br_post_address) . "," 32 .db_escape($disable_trans) . ", " 33 .db_escape($group_no) . ", " 34 .db_escape($default_ship_via). ", " 35 .db_escape($notes).")"; 36 db_query($sql,"The branch record could not be added"); 37} 38 39function update_branch($customer_id, $branch_code, $br_name, $br_ref, $br_address, 40 $salesman, $area, $tax_group_id, $sales_account, $sales_discount_account, $receivables_account, 41 $payment_discount_account, $default_location, $br_post_address, $disable_trans, $group_no, 42 $default_ship_via, $notes) 43{ 44 $sql = "UPDATE ".TB_PREF."cust_branch SET br_name = " . db_escape($br_name) . ", 45 branch_ref = " . db_escape($br_ref) . ", 46 br_address = ".db_escape($br_address). ", 47 salesman= ".db_escape($salesman) . ", 48 area=".db_escape($area) . ", 49 tax_group_id=".db_escape($tax_group_id). ", 50 sales_account=".db_escape($sales_account) . ", 51 sales_discount_account=".db_escape($sales_discount_account) . ", 52 receivables_account=".db_escape($receivables_account) . ", 53 payment_discount_account=".db_escape($payment_discount_account) . ", 54 default_location=".db_escape($default_location) . ", 55 br_post_address =".db_escape($br_post_address) . ", 56 disable_trans=".db_escape($disable_trans) . ", 57 group_no=".db_escape($group_no) . ", 58 default_ship_via=".db_escape($default_ship_via) . ", 59 notes=".db_escape($notes) . " 60 WHERE branch_code =".db_escape($branch_code) . " 61 AND debtor_no=".db_escape($customer_id); 62 db_query($sql,"The branch record could not be updated"); 63} 64 65function delete_branch($customer_id, $branch_code) 66{ 67 delete_entity_contacts('cust_branch', $branch_code); 68 69 $sql="DELETE FROM ".TB_PREF."cust_branch WHERE branch_code=".db_escape($branch_code)." AND debtor_no=".db_escape($customer_id); 70 db_query($sql,"could not delete branch"); 71} 72 73function branch_in_foreign_table($customer_id, $branch_code, $table) 74{ 75 $sql= "SELECT COUNT(*) FROM ".TB_PREF."$table WHERE branch_code=".db_escape($branch_code) 76 ." AND debtor_no = ".db_escape($customer_id); 77 $result = db_query($sql,"could not query $table"); 78 $myrow = db_fetch_row($result); 79 return ($myrow[0] > 0); 80} 81 82function get_branch($branch_id) 83{ 84 $sql = "SELECT ".TB_PREF."cust_branch.*,".TB_PREF."salesman.salesman_name 85 FROM ".TB_PREF."cust_branch, ".TB_PREF."salesman 86 WHERE ".TB_PREF."cust_branch.salesman=".TB_PREF."salesman.salesman_code 87 AND branch_code=".db_escape($branch_id); 88 89 $result = db_query($sql, "Cannot retreive a customer branch"); 90 91 return db_fetch($result); 92} 93 94function get_cust_branch($customer_id, $branch_code) 95{ 96 $sql = "SELECT * FROM ".TB_PREF."cust_branch 97 WHERE branch_code=".db_escape($branch_code)." 98 AND debtor_no=".db_escape($customer_id); 99 $result = db_query($sql,"check failed"); 100 return db_fetch($result); 101} 102 103function get_branch_accounts($branch_id) 104{ 105 $sql = "SELECT receivables_account,sales_account, sales_discount_account, payment_discount_account 106 FROM ".TB_PREF."cust_branch WHERE branch_code=".db_escape($branch_id); 107 108 $result = db_query($sql, "Cannot retreive a customer branch"); 109 110 return db_fetch($result); 111} 112 113function get_branch_name($branch_id) 114{ 115 $sql = "SELECT br_name FROM ".TB_PREF."cust_branch 116 WHERE branch_code = ".db_escape($branch_id); 117 118 $result = db_query($sql,"could not retreive name for branch" . $branch_id); 119 120 $myrow = db_fetch_row($result); 121 return $myrow[0]; 122} 123 124function get_cust_branches_from_group($group_no) 125{ 126 $sql = "SELECT branch_code, debtor_no FROM ".TB_PREF."cust_branch 127 WHERE group_no = ".db_escape($group_no); 128 129 return db_query($sql,"could not retreive branches for group " . $group_no); 130} 131 132function get_default_info_for_branch($customer_id) 133{ 134 $sql = "SELECT name, address, debtor_ref 135 FROM ".TB_PREF."debtors_master WHERE debtor_no = ".db_escape($customer_id); 136 $result = db_query($sql,"check failed"); 137 return db_fetch($result); 138} 139 140function get_sql_for_customer_branches() 141{ 142 $sql = "SELECT " 143 ."b.branch_code, " 144 ."b.branch_ref, " 145 ."b.br_name, " 146 ."p.name as contact_name, " 147 ."s.salesman_name, " 148 ."a.description, " 149 ."p.phone, " 150 ."p.fax, " 151 ."p.email, " 152 ."t.name AS tax_group_name, " 153 ."b.inactive 154 FROM ".TB_PREF."cust_branch b " 155 . "LEFT JOIN ".TB_PREF."crm_contacts c 156 ON c.entity_id=b.branch_code AND c.type='cust_branch' AND c.action='general' 157 LEFT JOIN ".TB_PREF."crm_persons p on c.person_id=p.id," 158 .TB_PREF."areas a, " 159 .TB_PREF."salesman s, " 160 .TB_PREF."tax_groups t 161 WHERE b.tax_group_id=t.id 162 AND b.area=a.area_code 163 AND b.salesman=s.salesman_code 164 AND b.debtor_no = ".db_escape($_POST['customer_id']); 165 166 if (!get_post('show_inactive')) $sql .= " AND !b.inactive"; 167 $sql .= " GROUP BY b.branch_code ORDER BY branch_ref"; 168 169 return $sql; 170} 171/* 172 Get contacts of given type for customer branch. 173 $branch_code - branch id 174 $action - type of contact 175 $customer_id - if passed: get also customer contacts for given action 176 $default - return only single record selected with defaults order defined in $defs array, 177 otherways get all $action contacts 178*/ 179function get_branch_contacts($branch_code, $action=null, $customer_id=null, $default = true) 180{ 181 $defs = array('cust_branch.'.$action, 182 'customer.'.$action, 183 'cust_branch.general', 184 'customer.general'); 185 186 $sql = "SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type 187 FROM ".TB_PREF."crm_persons p," 188 .TB_PREF."crm_contacts r WHERE r.person_id=p.id AND ((r.type='cust_branch' 189 AND r.entity_id=".db_escape($branch_code).')'; 190 if($customer_id) { 191 $sql .= " OR (r.type='customer' AND r.entity_id=".db_escape($customer_id).")"; 192 } 193 $sql .= ')'; 194 195 if ($action) 196 $sql .= ' AND (r.action='.db_escape($action).($default ? " OR r.action='general'" : '').')'; 197 198 $res = db_query($sql, "can't retrieve branch contacts"); 199 200 $results = array(); 201 while($contact = db_fetch($res)) 202 $results[] = $contact; 203 204 if ($results && $default) { 205 // select first available contact in $defs order 206 foreach($defs as $type) { 207 if ($n = array_search_value($type, $results, 'ext_type')) 208 return $n; 209 } 210 return null; 211 } 212 return $results; 213} 214 215function _get_branch_contacts($branch_code, $action=null, $customer_id=null, $default = false) 216{ 217 $sql = "SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type 218 FROM ".TB_PREF."crm_persons p," 219 .TB_PREF."crm_contacts r WHERE r.person_id=p.id AND r.type='cust_branch' 220 AND r.entity_id=".db_escape($branch_code); 221 222 if ($action) 223 $sql .= ' AND (r.action='.db_escape($action).($default ? " OR r.action='general'":'').')'; 224 225 if($customer_id) { 226 $sql = "($sql) UNION (SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type 227 FROM ".TB_PREF."crm_persons p," 228 .TB_PREF."crm_contacts r WHERE r.person_id=p.id AND r.type='customer' 229 AND r.entity_id=".db_escape($customer_id); 230 if ($action) 231 $sql .= ' AND (r.action='.db_escape($action).($default ? " OR r.action='general'":'').')'; 232 $sql .= ')'; 233 } 234 235 $res = db_query($sql, "can't retrieve branch contacts"); 236 $results = array(); 237 $type = ''; 238 while($contact = db_fetch($res)) { 239 if ($type && $type != $contact['type']) break; // skip cust when branch contacts found 240 $results[] = $contact; 241 $type = $contact['type']; 242 } 243 if ($results && $default) { 244 // here we have general and action contacts selected 245 if ($n = array_search_value($action, $results, 'action')) { 246 return $n; 247 } 248 // only general contact found 249 return $results[0]; 250 } 251 return $results; 252} 253 254?>