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