1<?php
2
3
4include('includes/session.php');
5
6if (isset($_POST['PrintPDF'])
7	and isset($_POST['FromCriteria'])
8	and mb_strlen($_POST['FromCriteria'])>=1
9	and isset($_POST['ToCriteria'])
10	and mb_strlen($_POST['ToCriteria'])>=1){
11
12	include('includes/PDFStarter.php');
13	$pdf->addInfo('Title',_('Aged Supplier Listing'));
14	$pdf->addInfo('Subject',_('Aged Suppliers'));
15	$FontSize=12;
16	$PageNumber=0;
17	$line_height=12;
18
19	  /*Now figure out the aged analysis for the Supplier range under review */
20
21	if ($_POST['All_Or_Overdues']=='All'){
22		$SQL = "SELECT suppliers.supplierid,
23						suppliers.suppname,
24						currencies.currency,
25						currencies.decimalplaces AS currdecimalplaces,
26						paymentterms.terms,
27						SUM(supptrans.ovamount + supptrans.ovgst  - supptrans.alloc) as balance,
28						SUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN
29						CASE WHEN (TO_DAYS(Now()) - TO_DAYS(supptrans.trandate)) >= paymentterms.daysbeforedue THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
30						ELSE
31						CASE WHEN TO_DAYS(Now()) - TO_DAYS(ADDDATE(last_day(supptrans.trandate),paymentterms.dayinfollowingmonth)) >= 0 THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
32						END) AS due,
33						SUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN
34						CASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays1'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
35						ELSE
36						CASE WHEN TO_DAYS(Now()) - TO_DAYS(ADDDATE(last_day(supptrans.trandate),paymentterms.dayinfollowingmonth)) >= " . $_SESSION['PastDueDays1'] . " THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
37						END) AS overdue1,
38						SUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN
39						CASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue	AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays2'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
40						ELSE
41						CASE WHEN TO_DAYS(Now()) - TO_DAYS(ADDDATE(last_day(supptrans.trandate),paymentterms.dayinfollowingmonth)) >= " . $_SESSION['PastDueDays2'] . " THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
42						END) AS overdue2
43				FROM suppliers INNER JOIN paymentterms
44				ON suppliers.paymentterms = paymentterms.termsindicator
45				INNER JOIN currencies
46				ON suppliers.currcode = currencies.currabrev
47				INNER JOIN supptrans
48				ON suppliers.supplierid = supptrans.supplierno
49				WHERE suppliers.supplierid >= '" . $_POST['FromCriteria'] . "'
50				AND suppliers.supplierid <= '" . $_POST['ToCriteria'] . "'
51				AND  suppliers.currcode ='" . $_POST['Currency'] . "'
52				GROUP BY suppliers.supplierid,
53						suppliers.suppname,
54						currencies.currency,
55						paymentterms.terms,
56						paymentterms.daysbeforedue,
57						paymentterms.dayinfollowingmonth
58				HAVING ROUND(ABS(SUM(supptrans.ovamount + supptrans.ovgst - supptrans.alloc)), currencies.decimalplaces) > 0";
59
60	} else {
61
62		$SQL = "SELECT suppliers.supplierid,
63						suppliers.suppname,
64						currencies.currency,
65						currencies.decimalplaces AS currdecimalplaces,
66						paymentterms.terms,
67						SUM(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) AS balance,
68						SUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN
69							CASE WHEN (TO_DAYS(Now()) - TO_DAYS(supptrans.trandate)) >= paymentterms.daysbeforedue  THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
70						ELSE
71							CASE WHEN TO_DAYS(Now()) - TO_DAYS(ADDDATE(last_day(supptrans.trandate),paymentterms.dayinfollowingmonth)) >= 0 THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
72						END) AS due,
73						Sum(CASE WHEN paymentterms.daysbeforedue > 0 THEN
74							CASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays1'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
75						ELSE
76							CASE WHEN TO_DAYS(Now()) - TO_DAYS(ADDDATE(last_day(supptrans.trandate),paymentterms.dayinfollowingmonth)) >= " . $_SESSION['PastDueDays1'] . " THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
77						END) AS overdue1,
78						SUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN
79							CASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue	AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays2'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
80						ELSE
81							CASE WHEN TO_DAYS(Now()) - TO_DAYS(ADDDATE(last_day(supptrans.trandate),paymentterms.dayinfollowingmonth)) >= " . $_SESSION['PastDueDays2'] . " THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
82						END) AS overdue2
83				FROM suppliers INNER JOIN paymentterms
84				ON suppliers.paymentterms = paymentterms.termsindicator
85				INNER JOIN currencies
86				ON suppliers.currcode = currencies.currabrev
87				INNER JOIN supptrans
88				ON suppliers.supplierid = supptrans.supplierno
89				WHERE suppliers.supplierid >= '" . $_POST['FromCriteria'] . "'
90				AND suppliers.supplierid <= '" . $_POST['ToCriteria'] . "'
91				AND suppliers.currcode ='" . $_POST['Currency'] . "'
92				GROUP BY suppliers.supplierid,
93					suppliers.suppname,
94					currencies.currency,
95					paymentterms.terms,
96					paymentterms.daysbeforedue,
97					paymentterms.dayinfollowingmonth
98				HAVING SUM(IF (paymentterms.daysbeforedue > 0,
99				CASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays1'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END,
100				CASE WHEN TO_DAYS(Now()) - TO_DAYS(ADDDATE(last_day(supptrans.trandate),paymentterms.dayinfollowingmonth)) >= " . $_SESSION['PastDueDays1'] . " THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END)) > 0";
101
102	}
103
104	$SupplierResult = DB_query($SQL,'','',False,False); /*dont trap errors */
105
106	if (DB_error_no() !=0) {
107		$Title = _('Aged Supplier Account Analysis') . ' - ' . _('Problem Report') ;
108		include('includes/header.php');
109		prnMsg(_('The Supplier details could not be retrieved by the SQL because') .  ' ' . DB_error_msg(),'error');
110		echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
111		if ($debug==1){
112			echo '<br />' . $SQL;
113		}
114		include('includes/footer.php');
115		exit;
116	}
117
118	include ('includes/PDFAgedSuppliersPageHeader.inc');
119	$TotBal = 0;
120	$TotDue = 0;
121	$TotCurr = 0;
122	$TotOD1 = 0;
123	$TotOD2 = 0;
124	$CurrDecimalPlaces =0;
125
126	$ListCount = DB_num_rows($SupplierResult); // UldisN
127
128	while ($AgedAnalysis = DB_fetch_array($SupplierResult)){
129
130		$CurrDecimalPlaces = $AgedAnalysis['currdecimalplaces'];
131
132		$DisplayDue = locale_number_format($AgedAnalysis['due']-$AgedAnalysis['overdue1'],$CurrDecimalPlaces);
133		$DisplayCurrent = locale_number_format($AgedAnalysis['balance']-$AgedAnalysis['due'],$CurrDecimalPlaces);
134		$DisplayBalance = locale_number_format($AgedAnalysis['balance'],$CurrDecimalPlaces);
135		$DisplayOverdue1 = locale_number_format($AgedAnalysis['overdue1']-$AgedAnalysis['overdue2'],$CurrDecimalPlaces);
136		$DisplayOverdue2 = locale_number_format($AgedAnalysis['overdue2'],$CurrDecimalPlaces);
137
138		$TotBal += $AgedAnalysis['balance'];
139		$TotDue += ($AgedAnalysis['due']-$AgedAnalysis['overdue1']);
140		$TotCurr += ($AgedAnalysis['balance']-$AgedAnalysis['due']);
141		$TotOD1 += ($AgedAnalysis['overdue1']-$AgedAnalysis['overdue2']);
142		$TotOD2 += $AgedAnalysis['overdue2'];
143
144		$LeftOvers = $pdf->addTextWrap($Left_Margin,$YPos,220-$Left_Margin,$FontSize,$AgedAnalysis['supplierid'] . ' - ' . $AgedAnalysis['suppname'],'left');
145		$LeftOvers = $pdf->addTextWrap(220,$YPos,60,$FontSize,$DisplayBalance,'right');
146		$LeftOvers = $pdf->addTextWrap(280,$YPos,60,$FontSize,$DisplayCurrent,'right');
147		$LeftOvers = $pdf->addTextWrap(340,$YPos,60,$FontSize,$DisplayDue,'right');
148		$LeftOvers = $pdf->addTextWrap(400,$YPos,60,$FontSize,$DisplayOverdue1,'right');
149		$LeftOvers = $pdf->addTextWrap(460,$YPos,60,$FontSize,$DisplayOverdue2,'right');
150
151		$YPos -=$line_height;
152		if ($YPos < $Bottom_Margin + $line_height){
153			  include('includes/PDFAgedSuppliersPageHeader.inc');
154		}
155
156		if ($_POST['DetailedReport']=='Yes'){
157
158		   $FontSize=6;
159		   /*draw a line under the Supplier aged analysis*/
160		   $pdf->line($Page_Width-$Right_Margin, $YPos+10,$Left_Margin, $YPos+10);
161
162		   $sql = "SELECT systypes.typename,
163							supptrans.suppreference,
164							supptrans.trandate,
165							(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) as balance,
166							CASE WHEN paymentterms.daysbeforedue > 0 THEN
167								CASE WHEN (TO_DAYS(Now()) - TO_DAYS(supptrans.trandate)) >= paymentterms.daysbeforedue  THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
168							ELSE
169								CASE WHEN TO_DAYS(Now()) - TO_DAYS(ADDDATE(last_day(supptrans.trandate),paymentterms.dayinfollowingmonth)) >= 0 THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
170							END AS due,
171							CASE WHEN paymentterms.daysbeforedue > 0 THEN
172								CASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue	   AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays1'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
173							ELSE
174								CASE WHEN TO_DAYS(Now()) - TO_DAYS(ADDDATE(last_day(supptrans.trandate), paymentterms.dayinfollowingmonth)) >= " . $_SESSION['PastDueDays1'] . " THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
175							END AS overdue1,
176							CASE WHEN paymentterms.daysbeforedue > 0 THEN
177								CASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays2'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
178							ELSE
179								CASE WHEN TO_DAYS(Now()) - TO_DAYS(ADDDATE(last_day(supptrans.trandate),paymentterms.dayinfollowingmonth)) >= " . $_SESSION['PastDueDays2'] . " THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
180							END AS overdue2
181						FROM suppliers
182						LEFT JOIN paymentterms
183							ON suppliers.paymentterms = paymentterms.termsindicator
184						LEFT JOIN supptrans
185							ON suppliers.supplierid = supptrans.supplierno
186						LEFT JOIN systypes
187							ON systypes.typeid = supptrans.type
188						WHERE ABS(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) >0.009
189							AND supptrans.settled = 0
190							AND supptrans.supplierno = '" . $AgedAnalysis["supplierid"] . "'";
191
192			$DetailResult = DB_query($sql,'','',False,False); /*dont trap errors - trapped below*/
193			if (DB_error_no() !=0) {
194			$Title = _('Aged Supplier Account Analysis - Problem Report');
195			include('includes/header.php');
196			prnMsg(_('The details of outstanding transactions for Supplier') . ' - ' . $AgedAnalysis['supplierid'] . ' ' . _('could not be retrieved because') . ' - ' . DB_error_msg(),'error');
197			echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
198			if ($debug==1){
199			   echo '<br />' . _('The SQL that failed was') . '<br />' . $sql;
200			}
201			include('includes/footer.php');
202			exit;
203			}
204
205			while ($DetailTrans = DB_fetch_array($DetailResult)){
206
207				$LeftOvers = $pdf->addTextWrap($Left_Margin+5,$YPos,60,$FontSize,$DetailTrans['typename'],'left');
208				$LeftOvers = $pdf->addTextWrap($Left_Margin+65,$YPos,50,$FontSize,$DetailTrans['suppreference'],'left');
209				$DisplayTranDate = ConvertSQLDate($DetailTrans['trandate']);
210				$LeftOvers = $pdf->addTextWrap($Left_Margin+105,$YPos,70,$FontSize,$DisplayTranDate,'left');
211
212				$DisplayDue = locale_number_format($DetailTrans['due']-$DetailTrans['overdue1'],$CurrDecimalPlaces);
213				$DisplayCurrent = locale_number_format($DetailTrans['balance']-$DetailTrans['due'],$CurrDecimalPlaces);
214				$DisplayBalance = locale_number_format($DetailTrans['balance'],$CurrDecimalPlaces);
215				$DisplayOverdue1 = locale_number_format($DetailTrans['overdue1']-$DetailTrans['overdue2'],$CurrDecimalPlaces);
216				$DisplayOverdue2 = locale_number_format($DetailTrans['overdue2'],$CurrDecimalPlaces);
217
218				$LeftOvers = $pdf->addTextWrap(220,$YPos,60,$FontSize,$DisplayBalance,'right');
219				$LeftOvers = $pdf->addTextWrap(280,$YPos,60,$FontSize,$DisplayCurrent,'right');
220				$LeftOvers = $pdf->addTextWrap(340,$YPos,60,$FontSize,$DisplayDue,'right');
221				$LeftOvers = $pdf->addTextWrap(400,$YPos,60,$FontSize,$DisplayOverdue1,'right');
222				$LeftOvers = $pdf->addTextWrap(460,$YPos,60,$FontSize,$DisplayOverdue2,'right');
223
224				$YPos -=$line_height;
225				if ($YPos < $Bottom_Margin + $line_height){
226				$PageNumber++;
227				include('includes/PDFAgedSuppliersPageHeader.inc');
228				$FontSize=6;
229				}
230			} /*end while there are detail transactions to show */
231			/*draw a line under the detailed transactions before the next Supplier aged analysis*/
232		   $pdf->line($Page_Width-$Right_Margin, $YPos+10,$Left_Margin, $YPos+10);
233		   $FontSize=8;
234		} /*Its a detailed report */
235	} /*end Supplier aged analysis while loop */
236
237	$YPos -=$line_height;
238	if ($YPos < $Bottom_Margin + (2*$line_height)){
239		$PageNumber++;
240		include('includes/PDFAgedSuppliersPageHeader.inc');
241	} elseif ($_POST['DetailedReport']=='Yes') {
242		//dont do a line if the totals have to go on a new page
243		$pdf->line($Page_Width-$Right_Margin, $YPos+10 ,220, $YPos+10);
244	}
245
246	$DisplayTotBalance = locale_number_format($TotBal,$CurrDecimalPlaces);
247	$DisplayTotDue = locale_number_format($TotDue,$CurrDecimalPlaces);
248	$DisplayTotCurrent = locale_number_format($TotCurr,$CurrDecimalPlaces);
249	$DisplayTotOverdue1 = locale_number_format($TotOD1,$CurrDecimalPlaces);
250	$DisplayTotOverdue2 = locale_number_format($TotOD2,$CurrDecimalPlaces);
251
252	$LeftOvers = $pdf->addTextWrap(220,$YPos,60,$FontSize,$DisplayTotBalance,'right');
253	$LeftOvers = $pdf->addTextWrap(280,$YPos,60,$FontSize,$DisplayTotCurrent,'right');
254	$LeftOvers = $pdf->addTextWrap(340,$YPos,60,$FontSize,$DisplayTotDue,'right');
255	$LeftOvers = $pdf->addTextWrap(400,$YPos,60,$FontSize,$DisplayTotOverdue1,'right');
256	$LeftOvers = $pdf->addTextWrap(460,$YPos,60,$FontSize,$DisplayTotOverdue2,'right');
257
258	$YPos -=$line_height;
259	$pdf->line($Page_Width-$Right_Margin, $YPos ,220, $YPos);
260
261	if ($ListCount == 0) {
262		$Title = _('Aged Supplier Analysis');
263		include('includes/header.php');
264		prnMsg(_('There are no results so the PDF is empty'));
265		include('includes/footer.php');
266	} else {
267		$pdf->OutputD($_SESSION['DatabaseName'] . '_AgedSuppliers_' . date('Y-m-d').'.pdf');
268	}
269	$pdf->__destruct();
270} else { /*The option to print PDF was not hit */
271
272	$Title = _('Aged Supplier Analysis');
273	include('includes/header.php');
274
275	echo '<p class="page_title_text"><img src="'.$RootPath.'/css/'.$Theme.'/images/magnifier.png" title="' . _('Search') . '" alt="" />' . ' ' . $Title . '</p><br />';
276
277	if (!isset($_POST['FromCriteria']) or !isset($_POST['ToCriteria'])) {
278
279	/*if $FromCriteria is not set then show a form to allow input	*/
280
281		echo '<form action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post">
282            <div>
283			<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />
284			<table class="selection">
285			<tr>
286				<td>' . _('From Supplier Code') . ':</td>
287				<td><input tabindex="1" type="text" required="required"  autofocus="autofocus" maxlength="6" size="7" name="FromCriteria" value="1" title+"' . _('Enter the first supplier code alphabetially to include in the report') . '" /></td>
288			</tr>
289			<tr>
290				<td>' . _('To Supplier Code') . ':</td>
291				<td><input tabindex="2" type="text" maxlength="6" size="7" name="ToCriteria" required="required" value="zzzzzz" title="' . _('Enter the last supplier code alphabetically to include in the report') . '" /></td>
292			</tr>
293			<tr>
294				<td>' . _('All balances or overdues only') . ':' . '</td>
295				<td><select tabindex="3" name="All_Or_Overdues">
296					<option selected="selected" value="All">' . _('All suppliers with balances') . '</option>
297					<option value="OverduesOnly">' . _('Overdue accounts only') . '</option>
298					</select></td>
299			</tr>
300			<tr>
301				<td>' . _('For suppliers trading in') . ':' . '</td>
302				<td><select tabindex="4" name="Currency">';
303
304		$sql = "SELECT currency, currabrev FROM currencies";
305		$result=DB_query($sql);
306
307		while ($myrow=DB_fetch_array($result)){
308			if ($myrow['currabrev'] == $_SESSION['CompanyRecord']['currencydefault']){
309				echo '<option selected="selected" value="' . $myrow['currabrev'] . '">' . $myrow['currency'] . '</option>';
310			} else {
311				echo '<option value="' . $myrow['currabrev'] . '">' . $myrow['currency'] . '</option>';
312			}
313		}
314		echo '</select></td>
315			</tr>
316			<tr>
317				<td>' . _('Summary or Detailed Report') . ':' . '</td>
318				<td><select tabindex="5" name="DetailedReport">
319					<option selected="selected" value="No">' . _('Summary Report')  . '</option>
320					<option value="Yes">' . _('Detailed Report')  . '</option>
321					</select></td>
322			</tr>
323			</table>
324			<br />
325			<div class="centre">
326				<input tabindex="6" type="submit" name="PrintPDF" value="' . _('Print PDF') . '" />
327			</div>
328            </div>
329            </form>';
330	}
331	include('includes/footer.php');
332} /*end of else not PrintPDF */
333
334?>
335