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?>