sales/includes/db/branches_db.inc

   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 ***********************************************************************/
  12 function 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, $group_no,
  15         $default_ship_via, $notes, $bank_account)
  16 {
  17         begin_transaction(__FUNCTION__, func_get_args());
  18         $sql = "INSERT INTO ".TB_PREF."cust_branch (debtor_no, br_name, branch_ref, br_address,
  19                 salesman, area, tax_group_id, sales_account, receivables_account, payment_discount_account,
  20                 sales_discount_account, default_location,
  21                 br_post_address, group_no, default_ship_via, notes, bank_account)
  22                 VALUES (".db_escape($customer_id). ",".db_escape($br_name) . ", "
  23                         .db_escape($br_ref) . ", "
  24                         .db_escape($br_address) . ", ".db_escape($salesman) . ", "
  25                         .db_escape($area) . ","
  26                         .db_escape($tax_group_id) . ", "
  27                         .db_escape($sales_account) . ", "
  28                         .db_escape($receivables_account) . ", "
  29                         .db_escape($payment_discount_account) . ", "
  30                         .db_escape($sales_discount_account) . ", "
  31                         .db_escape($default_location) . ", "
  32                         .db_escape($br_post_address) . ","
  33                         .db_escape($group_no) . ", "
  34                         .db_escape($default_ship_via). ", "
  35                         .db_escape($notes). ", "
  36                         .db_escape($bank_account, true).")";
  37         db_query($sql,"The branch record could not be added");
  38         commit_transaction();
  39 }
  40
  41 function update_branch($customer_id, $branch_code, $br_name, $br_ref, $br_address,
  42         $salesman, $area, $tax_group_id, $sales_account, $sales_discount_account, $receivables_account,
  43         $payment_discount_account, $default_location, $br_post_address, $group_no,
  44         $default_ship_via, $notes, $bank_account)
  45 {
  46         begin_transaction(__FUNCTION__, func_get_args());
  47         $sql = "UPDATE ".TB_PREF."cust_branch SET br_name = " . db_escape($br_name) . ",
  48                 branch_ref = " . db_escape($br_ref) . ",
  49                 br_address = ".db_escape($br_address). ",
  50                 salesman= ".db_escape($salesman) . ",
  51                 area=".db_escape($area) . ",
  52                 tax_group_id=".db_escape($tax_group_id). ",
  53                 sales_account=".db_escape($sales_account) . ",
  54                 sales_discount_account=".db_escape($sales_discount_account) . ",
  55                 receivables_account=".db_escape($receivables_account) . ",
  56                 payment_discount_account=".db_escape($payment_discount_account) . ",
  57                 default_location=".db_escape($default_location) . ",
  58                 br_post_address =".db_escape($br_post_address) . ",
  59                 group_no=".db_escape($group_no) . ",
  60                 default_ship_via=".db_escape($default_ship_via) . ",
  61                 notes=".db_escape($notes) . ",
  62                 bank_account=".db_escape($bank_account, true)."
  63                 WHERE branch_code =".db_escape($branch_code) . "
  64                 AND debtor_no=".db_escape($customer_id);
  65         db_query($sql,"The branch record could not be updated");
  66         commit_transaction();
  67 }
  68
  69 function delete_branch($customer_id, $branch_code)
  70 {
  71         begin_transaction(__FUNCTION__, func_get_args());
  72         delete_entity_contacts('cust_branch', $branch_code);
  73
  74         $sql="DELETE FROM ".TB_PREF."cust_branch WHERE branch_code=".db_escape($branch_code)." AND debtor_no=".db_escape($customer_id);
  75         db_query($sql,"could not delete branch");
  76         commit_transaction();
  77 }
  78
  79 function branch_in_foreign_table($customer_id, $branch_code, $table)
  80 {
  81         $sql= "SELECT COUNT(*) FROM ".TB_PREF."$table WHERE branch_code=".db_escape($branch_code)
  82                 ." AND debtor_no = ".db_escape($customer_id);
  83         $result = db_query($sql,"could not query $table");
  84         $myrow = db_fetch_row($result);
  85         return ($myrow[0] > 0);
  86 }
  87
  88 function get_branch($branch_id)
  89 {
  90         $sql = "SELECT branch.*, salesman.salesman_name
  91                 FROM "
  92                         .TB_PREF."cust_branch branch,"
  93                         .TB_PREF."salesman salesman
  94                 WHERE branch.salesman=salesman.salesman_code
  95                         AND branch_code=".db_escape($branch_id);
  96
  97         $result = db_query($sql, "Cannot retreive a customer branch");
  98
  99         return db_fetch($result);
 100 }
 101
 102 function get_cust_branch($customer_id, $branch_code)
 103 {
 104         $sql = "SELECT * FROM ".TB_PREF."cust_branch
 105                 WHERE branch_code=".db_escape($branch_code)."
 106                 AND debtor_no=".db_escape($customer_id);
 107         $result = db_query($sql,"check failed");
 108         return db_fetch($result);
 109 }
 110
 111 function get_branch_accounts($branch_id)
 112 {
 113         $sql = "SELECT receivables_account,sales_account, sales_discount_account, payment_discount_account
 114                 FROM ".TB_PREF."cust_branch WHERE branch_code=".db_escape($branch_id);
 115
 116         $result = db_query($sql, "Cannot retreive a customer branch");
 117
 118         return db_fetch($result);
 119 }
 120
 121 function get_branch_name($branch_id)
 122 {
 123         $sql = "SELECT br_name FROM ".TB_PREF."cust_branch
 124                 WHERE branch_code = ".db_escape($branch_id);
 125
 126         $result = db_query($sql,"could not retreive name for branch" . $branch_id);
 127
 128         $myrow = db_fetch_row($result);
 129         return $myrow[0];
 130 }
 131
 132 function get_cust_branches_from_group($group_no)
 133 {
 134         $sql = "SELECT branch_code, debtor_no FROM ".TB_PREF."cust_branch
 135                 WHERE group_no = ".db_escape($group_no);
 136
 137         return db_query($sql,"could not retreive branches for group " . $group_no);
 138 }
 139
 140 function get_default_info_for_branch($customer_id)
 141 {
 142         $sql = "SELECT name, address, debtor_ref
 143                 FROM ".TB_PREF."debtors_master WHERE debtor_no = ".db_escape($customer_id);
 144         $result = db_query($sql,"check failed");
 145         return db_fetch($result);
 146 }
 147
 148 function get_sql_for_customer_branches($customer_id)
 149 {
 150         $sql = "SELECT "
 151                 ."b.branch_code, "
 152                 ."b.branch_ref, "
 153                 ."b.br_name, "
 154                 ."p.name as contact_name, "
 155                 ."s.salesman_name, "
 156                 ."a.description, "
 157                 ."p.phone, "
 158                 ."p.fax, "
 159                 ."p.email, "
 160                 ."t.name AS tax_group_name, "
 161                 ."b.inactive
 162                 FROM ".TB_PREF."cust_branch b "
 163                 . "LEFT JOIN ".TB_PREF."crm_contacts c
 164                         ON c.entity_id=b.branch_code AND c.type='cust_branch' AND c.action='general'
 165                         LEFT JOIN ".TB_PREF."crm_persons p on c.person_id=p.id,"
 166                         .TB_PREF."areas a, "
 167                         .TB_PREF."salesman s, "
 168                         .TB_PREF."tax_groups t
 169                 WHERE b.tax_group_id=t.id
 170                 AND b.area=a.area_code
 171                 AND b.salesman=s.salesman_code
 172                 AND b.debtor_no = ".db_escape($customer_id);
 173
 174         if (!get_post('show_inactive')) $sql .= " AND !b.inactive";
 175         $sql .= " GROUP BY b.branch_code ORDER BY branch_ref";
 176
 177         return $sql;
 178 }
 179 /*
 180         Get contacts of given type for customer branch.
 181         $branch_code - branch id
 182         $action - type of contact
 183         $customer_id - if passed: get also customer contacts for given action
 184         $default - return only single record selected with defaults order defined in $defs array,
 185          otherways get all $action contacts
 186 */
 187 function get_branch_contacts($branch_code, $action=null, $customer_id=null, $default = true)
 188 {
 189         $defs = array('cust_branch.'.$action,
 190                                 'customer.'.$action,
 191                                 'cust_branch.general',
 192                                 'customer.general');
 193
 194         $sql = "SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type
 195                 FROM "
 196                         .TB_PREF."crm_persons p,"
 197                         .TB_PREF."crm_contacts r
 198                 WHERE r.person_id=p.id AND ((r.type='cust_branch'
 199                         AND r.entity_id=".db_escape($branch_code).')';
 200         if($customer_id) {
 201                 $sql .= " OR (r.type='customer' AND r.entity_id=".db_escape($customer_id).")";
 202         }
 203         $sql .= ')';
 204
 205         if ($action)
 206                 $sql .= ' AND (r.action='.db_escape($action).($default ? " OR r.action='general'" : '').')';
 207
 208         $res = db_query($sql, "can't retrieve branch contacts");
 209
 210         $results = array();
 211         while($contact = db_fetch($res))
 212                 $results[] = $contact;
 213
 214         if ($results && $default) {
 215                 // select first available contact in $defs order
 216                 foreach($defs as $type) {
 217                         if ($n = array_search_value($type, $results, 'ext_type'))
 218                                 return $n;
 219                 }
 220                 return null;
 221         }
 222         return $results;
 223 }
 224
 225 function _get_branch_contacts($branch_code, $action=null, $customer_id=null, $default = false)
 226 {
 227         $sql = "SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type
 228                 FROM ".TB_PREF."crm_persons p,"
 229                         .TB_PREF."crm_contacts r
 230                 WHERE r.person_id=p.id AND r.type='cust_branch'
 231                         AND r.entity_id=".db_escape($branch_code);
 232
 233         if ($action)
 234                 $sql .= ' AND (r.action='.db_escape($action).($default ? " OR r.action='general'":'').')';
 235
 236         if($customer_id) {
 237                 $sql = "($sql) UNION (SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type
 238                 FROM ".TB_PREF."crm_persons p,"
 239                         .TB_PREF."crm_contacts r
 240                 WHERE r.person_id=p.id AND r.type='customer'
 241                         AND r.entity_id=".db_escape($customer_id);
 242                 if ($action)
 243                         $sql .= ' AND (r.action='.db_escape($action).($default ? " OR r.action='general'":'').')';
 244                 $sql .= ')';
 245         }
 246
 247         $res = db_query($sql, "can't retrieve branch contacts");
 248         $results = array();
 249         $type = '';
 250         while($contact = db_fetch($res)) {
 251                 if ($type && $type != $contact['type']) break; // skip cust when branch contacts found
 252                 $results[] = $contact;
 253                 $type = $contact['type'];
 254         }
 255         if ($results && $default) {
 256                 // here we have general and action contacts selected
 257                 if ($n = array_search_value($action, $results, 'action')) {
 258                         return $n;
 259                 }
 260                 // only general contact found
 261                 return $results[0];
 262         }
 263         return $results;
 264 }
 265
 266 function get_default_branch($customer_id, $ar_account=null)
 267 {
 268         $sql = "SELECT *
 269                 FROM ".TB_PREF."cust_branch WHERE debtor_no = ".db_escape($customer_id);
 270         if($ar_account)
 271                 $sql .= " AND receivables_account=".db_escape($ar_account);
 272         $result = db_query($sql,"cannot retrieve default branch");
 273         return db_fetch($result);
 274 }
 275
 276 function  get_branches_search($customer, $branch)
 277 {
 278         global $SysPrefs;
 279
 280         if (isset($SysPrefs->max_rows_in_search))
 281                 $limit = $SysPrefs->max_rows_in_search;
 282         else
 283                 $limit = 10;
 284
 285         $sql = "SELECT
 286                     b.branch_code,
 287                 b.branch_ref,
 288                 b.br_name,
 289                 p.name as contact_name,
 290                     p.phone
 291                 FROM ".TB_PREF."cust_branch b
 292                 LEFT JOIN ".TB_PREF."crm_contacts c
 293                         ON c.entity_id=b.branch_code AND c.type='cust_branch' AND c.action='general'
 294                 LEFT JOIN ".TB_PREF."crm_persons p ON c.person_id=p.id
 295                 WHERE b.debtor_no = ".db_escape($customer)."
 296                         AND b.br_name LIKE " . db_escape("%" . $branch. "%") . "
 297                 ORDER BY b.br_name LIMIT 0,".(int)($limit);
 298
 299         return db_query($sql, "Failed in retreiving branches list.");
 300 }
 301