<? include('includes/main.php'); ?>
<? include('includes/apfunctions.php'); ?>
<? //apbilllistaging.php
//if this report is slow due to a long list of vendors, it can be rewritten to use a temporary table, and non-vendor specific queries to improve speed
unset($str);
if ($detail) {
echo texttitle(STR_AP_DETAIL_AGING_REPORT);
} else {
echo texttitle(STR_AP_AGING_REPORT);
};
echo texttitle(createtime('Y-m-d'));
$timestamp = time();
$date_time_array = getdate($timestamp);
$hours = $date_time_array["hours"];
$minutes = $date_time_array["minutes"];
$seconds = $date_time_array["seconds"];
$month = $date_time_array["mon"];
$day = $date_time_array["mday"];
$year = $date_time_array["year"];
$timestamp = mktime($hour, $minute, $second, $month, $day, $year);
$today=date("Y-m-d", $timestamp);
$timestamp = mktime($hour, $minute, $second, $month, $day-31, $year);
$monthago=date("Y-m-d", $timestamp);
$timestamp = mktime($hour, $minute, $second, $month, $day-61, $year);
$twomonthago=date("Y-m-d", $timestamp);
$timestamp = mktime($hour, $minute, $second, $month, $day-91, $year);
$threemonthago=date("Y-m-d", $timestamp);
if ($detail) {
$sumstr1='';
$sumstr2='';
$groupstr=' group by company.companyname,apbill.total,vendor.id,apbill.invoicenumber,apbill.dateofinvoice,apbill.id,apbill.description';
$wherestr=',apbill.invoicenumber,apbill.dateofinvoice,apbill.id,apbill.description';
} else { //just show summary
$sumstr1='sum(';
$sumstr2=')';
$groupstr=' group by company.companyname,vendor.id,apbill.vendorid';
$wherestr='';
};
$recordSet = &$conn->Execute('select company.companyname,'.$sumstr1.'apbill.total'.$sumstr2.'-sum(apbillpayment.amount*(apbillpayment.checkvoid!=0)),vendor.id'.$wherestr.' from apbill cross join vendor cross join company left join apbillpayment on apbillpayment.apbillid=apbill.id where apbill.vendorid=vendor.id and vendor.paytocompanyid=company.id and apbill.cancel=0 and apbill.complete=0 and apbill.gencompanyid='.sqlprep($active_company).$groupstr.' order by company.companyname');
if (!$recordSet||$recordSet->EOF) die(texterror(STR_NO_AGING_BILLS_FOUND));
echo '<table border="1"><tr><th rowspan="2">'.STR_VENDOR.'</th>';
if ($detail) echo '<th rowspan="2">'.STR_DESCRIPTION.'</th><th rowspan="2">'.STR_INVOICE_NUMBER.'</th><th rowspan="2">'.STR_DATE.'</th>';
echo '<th rowspan="2">Amount</th><th rowspan="2">'.STR_DISCOUNT.'</th><th colspan="4">'.STR_AGING_BY_INVOICE_DATE.'</th></tr><tr><th>'.STR_CURRENT.'</th><th>31-60</th><th>61-90</th><th>91+</th></tr>';
while ($recordSet&&!$recordSet->EOF) {
$vendid=$recordSet->fields[2];
$discount=0;
$monthagototal=0;
$twomonthagototal=0;
$threemonthagototal=0;
$overthreemonthagototal='0';
if ($detail) $andstr=' and apbill.id='.sqlprep($recordSet->fields[5]);
$recordSet2 = &$conn->Execute('select sum(apbill.discountamount) from apbill where apbill.vendorid='.sqlprep($recordSet->fields[2]).' and apbill.discountdate>'.sqlprep($today).$andstr.' and apbill.cancel=0 and apbill.complete=0 and apbill.gencompanyid='.sqlprep($active_company));
if ($recordSet2&&!$recordSet2->EOF) if ($recordSet2->fields[0]) $discount=$recordSet2->fields[0];
$recordSet2 = &$conn->Execute('select '.$sumstr1.'apbill.total'.$sumstr2.'-sum(apbillpayment.amount*(apbillpayment.checkvoid!=0)) from apbill left join apbillpayment on apbillpayment.apbillid=apbill.id where apbill.vendorid='.sqlprep($recordSet->fields[2]).$andstr.' and apbill.dateofinvoice>'.sqlprep($monthago).' and apbill.cancel=0 and apbill.complete=0 and apbill.gencompanyid='.sqlprep($active_company));
if ($recordSet2&&!$recordSet2->EOF) if ($recordSet2->fields[0]) $monthagototal=$recordSet2->fields[0];
$recordSet2 = &$conn->Execute('select '.$sumstr1.'apbill.total'.$sumstr2.'-sum(apbillpayment.amount*(apbillpayment.checkvoid!=0)) from apbill left join apbillpayment on apbillpayment.apbillid=apbill.id where apbill.vendorid='.sqlprep($recordSet->fields[2]).$andstr.' and apbill.dateofinvoice>='.sqlprep($twomonthago).' and apbill.dateofinvoice<'.sqlprep($monthago).' and apbill.cancel=0 and apbill.complete=0 and apbill.gencompanyid='.sqlprep($active_company));
if ($recordSet2&&!$recordSet2->EOF) if ($recordSet2->fields[0]) $twomonthagototal=$recordSet2->fields[0];
$recordSet2 = &$conn->Execute('select '.$sumstr1.'apbill.total'.$sumstr2.'-sum(apbillpayment.amount*(apbillpayment.checkvoid!=0)) from apbill left join apbillpayment on apbillpayment.apbillid=apbill.id where apbill.vendorid='.sqlprep($recordSet->fields[2]).$andstr.' and apbill.dateofinvoice>='.sqlprep($threemonthago).' and apbill.dateofinvoice<'.sqlprep($twomonthago).' and apbill.cancel=0 and apbill.complete=0 and apbill.gencompanyid='.sqlprep($active_company));
if ($recordSet2&&!$recordSet2->EOF) if ($recordSet2->fields[0]) $threemonthagototal=$recordSet2->fields[0];
$recordSet2 = &$conn->Execute('select '.$sumstr1.'apbill.total'.$sumstr2.'-sum(apbillpayment.amount*(apbillpayment.checkvoid!=0)) from apbill left join apbillpayment on apbillpayment.apbillid=apbill.id where apbill.vendorid='.sqlprep($recordSet->fields[2]).$andstr.' and apbill.dateofinvoice<'.sqlprep($threemonthago).' and apbill.cancel=0 and apbill.complete=0 and apbill.gencompanyid='.sqlprep($active_company));
if ($recordSet2&&!$recordSet2->EOF) if ($recordSet2->fields[0]) $overthreemonthagototal=$recordSet2->fields[0];
echo '<tr><td>'.$recordSet->fields[0].'</td>';
if ($detail) echo '<td>'.$recordSet->fields[6].'</td><td>'.$recordSet->fields[3].'</td><td>'.$recordSet->fields[4].'</td>';
echo '<td align="right">'.CURRENCY_SYMBOL.checkdec($recordSet->fields[1],PREFERRED_DECIMAL_PLACES).'</td><td align="right">'.CURRENCY_SYMBOL.checkdec($discount,PREFERRED_DECIMAL_PLACES).'</td><td align="right">'.CURRENCY_SYMBOL.checkdec($monthagototal,PREFERRED_DECIMAL_PLACES).'</td><td align="right">'.CURRENCY_SYMBOL.checkdec($twomonthagototal,PREFERRED_DECIMAL_PLACES).'</td><td align="right">'.CURRENCY_SYMBOL.checkdec($threemonthagototal,PREFERRED_DECIMAL_PLACES).'</td><td align="right">'.CURRENCY_SYMBOL.checkdec($overthreemonthagototal,PREFERRED_DECIMAL_PLACES).'</td></tr>';
$vtotalamount+=$recordSet->fields[1];
$vtotaldiscount+=$discount;
$vtotalmonthagototal+=$monthagototal;
$vtotaltwomonthagototal+=$twomonthagototal;
$vtotalthreemonthagototal+=$threemonthagototal;
$vtotaloverthreemonthagototal+=$overthreemonthagototal;
$totalamount+=$recordSet->fields[1];
$totaldiscount+=$discount;
$totalmonthagototal+=$monthagototal;
$totaltwomonthagototal+=$twomonthagototal;
$totalthreemonthagototal+=$threemonthagototal;
$totaloverthreemonthagototal+=$overthreemonthagototal;
$vendorname=$recordSet->fields[0];
$recordSet->MoveNext();
if ($detail) if ($recordSet->fields[2]<>$vendid||$recordSet->EOF) {
echo '<tr><th colspan="4" align="right">'.$vendorname.' '.STR_TOTAL.':</th><th align="right">'.CURRENCY_SYMBOL.checkdec($vtotalamount,PREFERRED_DECIMAL_PLACES).'</th><th align="right">'.CURRENCY_SYMBOL.checkdec($vtotaldiscount,PREFERRED_DECIMAL_PLACES).'</th><th align="right">'.CURRENCY_SYMBOL.checkdec($vtotalmonthagototal,PREFERRED_DECIMAL_PLACES).'</th><th align="right">'.CURRENCY_SYMBOL.checkdec($vtotaltwomonthagototal,PREFERRED_DECIMAL_PLACES).'</th><th align="right">'.CURRENCY_SYMBOL.checkdec($vtotalthreemonthagototal,PREFERRED_DECIMAL_PLACES).'</th><th align="right">'.CURRENCY_SYMBOL.checkdec($vtotaloverthreemonthagototal,PREFERRED_DECIMAL_PLACES).'</th></tr>';
$vtotalamount=0;
$vtotaldiscount=0;
$vtotalmonthagototal=0;
$vtotaltwomonthagototal=0;
$vtotalthreemonthagototal=0;
$vtotaloverthreemonthagototal=0;
};
};
if ($detail) {
echo '<tr><td colspan="10"> </td></tr><tr><td colspan="4">';
} else {
echo '<tr><td colspan="7"> </td></tr><tr><td>';
};
echo STR_TOTAL.':</td><td align="right">'.CURRENCY_SYMBOL.checkdec($totalamount,PREFERRED_DECIMAL_PLACES).'</td><td align="right">'.CURRENCY_SYMBOL.checkdec($totaldiscount,PREFERRED_DECIMAL_PLACES).'</td><td align="right">'.CURRENCY_SYMBOL.checkdec($totalmonthagototal,PREFERRED_DECIMAL_PLACES).'</td><td align="right">'.CURRENCY_SYMBOL.checkdec($totaltwomonthagototal,PREFERRED_DECIMAL_PLACES).'</td><td align="right">'.CURRENCY_SYMBOL.checkdec($totalthreemonthagototal,PREFERRED_DECIMAL_PLACES).'</td><td align="right">'.CURRENCY_SYMBOL.checkdec($totaloverthreemonthagototal,PREFERRED_DECIMAL_PLACES).'</td></tr>';
echo '</table>';
?>
<? include('includes/footer.php'); ?>