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 $page_security = 'SA_CUSTBULKREP';
13 // ----------------------------------------------------------------
17 // Title: Customer Details Listing
18 // ----------------------------------------------------------------
21 include_once($path_to_root . "/includes/session.inc");
22 include_once($path_to_root . "/includes/date_functions.inc");
23 include_once($path_to_root . "/includes/data_checks.inc");
24 include_once($path_to_root . "/gl/includes/gl_db.inc");
26 //----------------------------------------------------------------------------------------------------
28 print_customer_details_listing();
30 function get_customer_details_for_report($area=0, $salesid=0)
32 $sql = "SELECT debtor.debtor_no,
43 branch.br_post_address,
47 salesman.salesman_name
48 FROM ".TB_PREF."debtors_master debtor
49 INNER JOIN ".TB_PREF."cust_branch branch ON debtor.debtor_no=branch.debtor_no
50 INNER JOIN ".TB_PREF."sales_types pricelist ON debtor.sales_type=pricelist.id
51 INNER JOIN ".TB_PREF."areas area ON branch.area = area.area_code
52 INNER JOIN ".TB_PREF."salesman salesman ON branch.salesman=salesman.salesman_code
53 WHERE debtor.inactive = 0";
57 $sql .= " AND salesman.salesman_code=".db_escape($salesid)."
58 AND area.area_code=".db_escape($area);
60 $sql .= " AND area.area_code=".db_escape($area);
62 elseif ($salesid != 0)
63 $sql .= " AND salesman.salesman_code=".db_escape($salesid);
64 $sql .= " ORDER BY description,
65 salesman.salesman_name,
69 return db_query($sql,"No transactions were returned");
72 function get_contacts_for_branch($branch)
74 $sql = "SELECT p.*, r.action, r.type, CONCAT(r.type,'.',r.action) as ext_type
75 FROM ".TB_PREF."crm_persons p,"
76 .TB_PREF."crm_contacts r
77 WHERE r.person_id=p.id AND r.type='cust_branch'
78 AND r.entity_id=".db_escape($branch);
79 $res = db_query($sql, "can't retrieve branch contacts");
81 while($contact = db_fetch($res))
82 $results[] = $contact;
86 function getTransactions($debtorno, $branchcode, $date)
88 $date = date2sql($date);
90 $sql = "SELECT SUM((ov_amount+ov_freight+ov_discount)*rate) AS Turnover
91 FROM ".TB_PREF."debtor_trans
92 WHERE debtor_no=".db_escape($debtorno)."
93 AND branch_code=".db_escape($branchcode)."
94 AND (type=".ST_SALESINVOICE." OR type=".ST_CUSTCREDIT.")
95 AND tran_date >='$date'";
97 $result = db_query($sql,"No transactions were returned");
99 $row = db_fetch_row($result);
103 //----------------------------------------------------------------------------------------------------
105 function print_customer_details_listing()
107 global $path_to_root;
109 $from = $_POST['PARAM_0'];
110 $area = $_POST['PARAM_1'];
111 $folk = $_POST['PARAM_2'];
112 $more = $_POST['PARAM_3'];
113 $less = $_POST['PARAM_4'];
114 $comments = $_POST['PARAM_5'];
115 $orientation = $_POST['PARAM_6'];
116 $destination = $_POST['PARAM_7'];
118 include_once($path_to_root . "/reporting/includes/excel_report.inc");
120 include_once($path_to_root . "/reporting/includes/pdf_report.inc");
122 $orientation = ($orientation ? 'L' : 'P');
125 if ($area == ALL_NUMERIC)
127 if ($folk == ALL_NUMERIC)
131 $sarea = _('All Areas');
133 $sarea = get_area_name($area);
135 $salesfolk = _('All Sales Folk');
137 $salesfolk = get_salesman_name($folk);
139 $morestr = _('Greater than ') . number_format2($more, $dec);
143 $lessstr = _('Less than ') . number_format2($less, $dec);
147 $more = (double)$more;
148 $less = (double)$less;
150 $cols = array(0, 150, 300, 425, 550);
152 $headers = array(_('Customer Postal Address'), _('Price/Turnover'), _('Branch Contact Information'),
153 _('Branch Delivery Address'));
155 $aligns = array('left', 'left', 'left', 'left');
157 $params = array( 0 => $comments,
158 1 => array('text' => _('Activity Since'), 'from' => $from, 'to' => ''),
159 2 => array('text' => _('Sales Areas'), 'from' => $sarea, 'to' => ''),
160 3 => array('text' => _('Sales Folk'), 'from' => $salesfolk, 'to' => ''),
161 4 => array('text' => _('Activity'), 'from' => $morestr, 'to' => $lessstr . " " . get_company_pref("curr_default")));
163 $rep = new FrontReport(_('Customer Details Listing'), "CustomerDetailsListing", user_pagesize(), 9, $orientation);
164 if ($orientation == 'L')
165 recalculate_cols($cols);
168 $rep->Info($params, $cols, $headers, $aligns);
171 $result = get_customer_details_for_report($area, $folk);
175 while ($myrow=db_fetch($result))
177 $printcustomer = true;
178 if ($more != '' || $less != '')
180 $turnover = getTransactions($myrow['debtor_no'], $myrow['branch_code'], $from);
181 if ($more != 0.0 && $turnover <= (double)$more)
182 $printcustomer = false;
183 if ($less != 0.0 && $turnover >= (double)$less)
184 $printcustomer = false;
189 if ($carea != $myrow['description'])
194 $rep->TextCol(0, 3, _('Customers in') . " " . $myrow['description']);
195 $carea = $myrow['description'];
200 if ($sman != $myrow['salesman_name'])
205 $rep->TextCol(0, 3, $myrow['salesman_name']);
206 $sman = $myrow['salesman_name'];
212 // Here starts the new report lines
213 $contacts = get_contacts_for_branch($myrow['branch_code']);
214 $rep->TextCol(0, 1, $myrow['name']);
215 $rep->TextCol(1, 2, _('Price List') . ": " . $myrow['sales_type']);
216 $rep->TextCol(2, 3, $myrow['br_name']);
218 $adr = Explode("\n", $myrow['address']);
219 if ($myrow['br_post_address'] == '')
220 $adr2 = Explode("\n", $myrow['br_address']);
222 $adr2 = Explode("\n", $myrow['br_post_address']);
223 $count1 = count($adr);
224 $count2 = count($adr2);
225 $count1 = max($count1, $count2);
226 $count1 = max($count1, 4);
228 $rep->TextCol(0, 1, $adr[0]);
229 $rep->TextCol(1, 2, _('Currency') . ": " . $myrow['curr_code']);
230 if (isset($contacts[0]))
231 $rep->TextCol(2, 3, $contacts[0]['name']. " " .$contacts[0]['name2']);
233 $rep->TextCol(3, 4, $adr2[0]);
236 $rep->TextCol(0, 1, $adr[1]);
237 if ($myrow['dimension_id'] != 0)
239 $dim = get_dimension($myrow['dimension_id']);
240 $rep->TextCol(1, 2, _('Dimension') . ": " . $dim['name']);
242 if (isset($contacts[0]))
243 $rep->TextCol(2, 3, _('Ph') . ": " . $contacts[0]['phone']);
245 $rep->TextCol(3, 4, $adr2[1]);
248 $rep->TextCol(0, 1, $adr[2]);
249 if ($myrow['dimension2_id'] != 0)
251 $dim = get_dimension($myrow['dimension2_id']);
252 $rep->TextCol(1, 2, _('Dimension') . " 2: " . $dim['name']);
254 if ($myrow['notes'] != '')
258 $rep->TextColLines(1, 2, _("General Notes:")." ".$myrow['notes'], -2);
262 if (isset($contacts[0]))
263 $rep->TextCol(2, 3, _('Fax') . ": " . $contacts[0]['fax']);
265 $rep->TextCol(3, 4, $adr2[2]);
266 if ($more != 0.0 || $less != 0.0)
267 $rep->TextCol(1, 2, _('Turnover') . ": " . number_format2($turnover, $dec));
268 for ($i = 3; $i < $count1; $i++)
272 $rep->TextCol(0, 1, $adr[$i]);
273 if ($i == 3 && isset($contacts[0]) && isset($contacts[0]['email']))
274 $rep->TextCol(2, 3, _('Email') . ": " . $contacts[0]['email']);
275 if (isset($adr2[$i]))
276 $rep->TextCol(3, 4, $adr2[$i]);
278 if ($newrow != 0 && $newrow < $rep->row)
281 $rep->Line($rep->row + 8);