$TotOD2 += $AgedAnalysis['overdue2'];
$LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 220 - $Left_Margin, $FontSize, $AgedAnalysis['supplierid'] . ' - ' . $AgedAnalysis['suppname'], 'left');
$LeftOvers = $pdf->addTextWrap(220, $YPos, 60, $FontSize, $DisplayBalance, 'right');
$LeftOvers = $pdf->addTextWrap(280, $YPos, 60, $FontSize, $DisplayCurrent, 'right');
$LeftOvers = $pdf->addTextWrap(340, $YPos, 60, $FontSize, $DisplayDue, 'right');
$LeftOvers = $pdf->addTextWrap(400, $YPos, 60, $FontSize, $DisplayOverdue1, 'right');
$LeftOvers = $pdf->addTextWrap(460, $YPos, 60, $FontSize, $DisplayOverdue2, 'right');
$YPos -= $line_height;
if ($YPos < $Bottom_Margin + $line_height) {
include 'includes/PDFAgedSuppliersPageHeader.inc';
}
if ($_POST['DetailedReport'] == 'Yes') {
$FontSize = 6;
/*draw a line under the Supplier aged analysis*/
$pdf->line($Page_Width - $Right_Margin, $YPos + 10, $Left_Margin, $YPos + 10);
$sql = "SELECT systypes.typename,\n\t\t\t\t\t\t\tsupptrans.suppreference,\n\t\t\t\t\t\t\tsupptrans.trandate,\n\t\t\t\t\t\t\t(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) as balance,\n\t\t\t\t\t\t\tCASE WHEN paymentterms.daysbeforedue > 0 THEN\n\t\t\t\t\t\t\t\tCASE WHEN (TO_DAYS(Now()) - TO_DAYS(supptrans.trandate)) >= paymentterms.daysbeforedue THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\t\tCASE WHEN TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= 0 THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\t\t\t\t\t\tEND AS due,\n\t\t\t\t\t\t\tCASE WHEN paymentterms.daysbeforedue > 0 THEN\n\t\t\t\t\t\t\t\tCASE 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\n\t\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\t\tCASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays1'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\t\t\t\t\t\tEND AS overdue1,\n\t\t\t\t\t\t\tCASE WHEN paymentterms.daysbeforedue > 0 THEN\n\t\t\t\t\t\t\t\tCASE 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\n\t\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\t\tCASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays2'] . ") THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\t\t\t\t\t\tEND AS overdue2\n\t\t\t\t\t\tFROM suppliers\n\t\t\t\t\t\tLEFT JOIN paymentterms\n\t\t\t\t\t\t\tON suppliers.paymentterms = paymentterms.termsindicator\n\t\t\t\t\t\tLEFT JOIN supptrans\n\t\t\t\t\t\t\tON suppliers.supplierid = supptrans.supplierno\n\t\t\t\t\t\tLEFT JOIN systypes\n\t\t\t\t\t\t\tON systypes.typeid = supptrans.type\n\t\t\t\t\t\tWHERE ABS(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) >0.009\n\t\t\t\t\t\t\tAND supptrans.settled = 0\n\t\t\t\t\t\t\tAND supptrans.supplierno = '" . $AgedAnalysis["supplierid"] . "'";
$DetailResult = DB_query($sql, $db, '', '', False, False);
/*dont trap errors - trapped below*/
if (DB_error_no($db) != 0) {
$Title = _('Aged Supplier Account Analysis - Problem Report');
include 'includes/header.inc';
prnMsg(_('The details of outstanding transactions for Supplier') . ' - ' . $AgedAnalysis['supplierid'] . ' ' . _('could not be retrieved because') . ' - ' . DB_error_msg($db), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . _('The SQL that failed was') . '<br />' . $sql;
}
include 'includes/footer.inc';
exit;
}
while ($DetailTrans = DB_fetch_array($DetailResult)) {
$LeftOvers = $pdf->addTextWrap($Left_Margin + 5, $YPos, 60, $FontSize, $DetailTrans['typename'], 'left');
custbranch.taxgroupid,
locations.contact,
locations.email
FROM recurringsalesorders,
debtorsmaster,
custbranch,
salestypes,
locations
WHERE recurringsalesorders.ordertype=salestypes.typeabbrev
AND recurringsalesorders.debtorno = debtorsmaster.debtorno
AND recurringsalesorders.debtorno = custbranch.debtorno
AND recurringsalesorders.branchcode = custbranch.branchcode
AND recurringsalesorders.fromstkloc=locations.loccode
AND recurringsalesorders.ordertype=salestypes.typeabbrev
AND (TO_DAYS(NOW()) - TO_DAYS(recurringsalesorders.lastrecurrence)) > (365/recurringsalesorders.frequency)
AND DATE_ADD(recurringsalesorders.lastrecurrence, ' . INTERVAL('365/recurringsalesorders.frequency', 'DAY') . ') <= recurringsalesorders.stopdate';
$RecurrOrdersDueResult = DB_query($sql, $db, _('There was a problem retrieving the recurring sales order templates. The database reported:'));
if (DB_num_rows($RecurrOrdersDueResult) == 0) {
prnMsg(_('There are no recurring order templates that are due to have another recurring order created'), 'warn');
include 'includes/footer.inc';
exit;
}
echo '<BR>The number of recurring orders to process is : ' . DB_num_rows($RecurrOrdersDueResult);
while ($RecurrOrderRow = DB_fetch_array($RecurrOrdersDueResult)) {
$EmailText = '';
echo '<BR>' . _('Recurring order') . ' ' . $RecurrOrderRow['recurrorderno'] . ' ' . _('for') . ' ' . $RecurrOrderRow['debtorno'] . ' - ' . $RecurrOrderRow['branchcode'] . ' ' . _('is being processed');
$result = DB_Txn_Begin($db);
/*the last recurrence was the date of the last time the order recurred
the frequency is the number of times per annum that the order should recurr
so 365 / frequency gives the number of days between recurrences */
$DelDate = FormatDateforSQL(DateAdd(ConvertSQLDate($RecurrOrderRow['lastrecurrence']), 'd', 365 / $RecurrOrderRow['frequency']));
holdreasons.reasondescription,
SUM(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc) AS balance,
SUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN
CASE WHEN (TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate)) >= paymentterms.daysbeforedue THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END
ELSE
CASE WHEN TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(debtortrans.trandate, ' . INTERVAL('1', 'MONTH') . '), ' . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(debtortrans.trandate))', 'DAY') . ')) >= 0 THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END
END) AS due,
SUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN
CASE WHEN TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) >= (paymentterms.daysbeforedue + ' . $_SESSION['PastDueDays1'] . ') THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight - debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END
ELSE
CASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(debtortrans.trandate, ' . INTERVAL('1', 'MONTH') . '), ' . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(debtortrans.trandate))', 'DAY') . ')) >= ' . $_SESSION['PastDueDays1'] . ') THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END
END) AS overdue1,
SUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN
CASE WHEN TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) >= (paymentterms.daysbeforedue + ' . $_SESSION['PastDueDays2'] . ') THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END
ELSE
CASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(debtortrans.trandate, ' . INTERVAL('1', 'MONTH') . '), ' . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(debtortrans.trandate))', 'DAY') . ')) >= ' . $_SESSION['PastDueDays2'] . ") THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END\n\t\t\tEND) AS overdue2\n\t\t\tFROM debtorsmaster,\n\t\t\t\tpaymentterms,\n\t\t\t\tholdreasons,\n\t\t\t\tcurrencies,\n\t\t\t\tdebtortrans\n\t\t\tWHERE debtorsmaster.paymentterms = paymentterms.termsindicator\n\t\t\tAND debtorsmaster.currcode = currencies.currabrev\n\t\t\tAND debtorsmaster.holdreason = holdreasons.reasoncode\n\t\t\tAND debtorsmaster.debtorno = '" . $_POST['CustomerID'] . "'\n\t\t\tAND debtorsmaster.debtorno = debtortrans.debtorno\n\t\t\tGROUP BY debtorsmaster.name,\n\t\t\t\tdebtorsmaster.pymtdiscount,\n\t\t\t\tdebtorsmaster.currcode,\n\t\t\t\tcurrencies.currency,\n\t\t\t\tcurrencies.rate,\n\t\t\t\tpaymentterms.terms,\n\t\t\t\tdebtorsmaster.creditlimit,\n\t\t\t\tpaymentterms.daysbeforedue,\n\t\t\t\tpaymentterms.dayinfollowingmonth,\n\t\t\t\tdebtorsmaster.creditlimit,\n\t\t\t\tholdreasons.dissallowinvoices,\n\t\t\t\tholdreasons.reasondescription";
$ErrMsg = _('The customer details could not be retrieved because');
$DbgMsg = _('The SQL that failed was');
$CustomerResult = DB_query($SQL, $db, $ErrMsg, $DbgMsg);
if (DB_num_rows($CustomerResult) == 0) {
/*Because there is no balance - so just retrieve the header information about the customer - the choice is do one query to get the balance and transactions for those customers who have a balance and two queries for those who don't have a balance OR always do two queries - I opted for the former */
$NIL_BALANCE = True;
$SQL = "SELECT debtorsmaster.name,\n\t\t\t\tdebtorsmaster.pymtdiscount,\n\t\t\t\tcurrencies.currency,\n\t\t\t\tcurrencies.rate,\n\t\t\t\tpaymentterms.terms,\n\t\t\t\tdebtorsmaster.creditlimit,\n\t\t\t\tdebtorsmaster.currcode,\n\t\t\t\tholdreasons.dissallowinvoices,\n\t\t\t\tholdreasons.reasondescription\n\t\t\tFROM debtorsmaster,\n\t\t\t\tpaymentterms,\n\t\t\t\tholdreasons,\n\t\t\t\tcurrencies\n\t\t\tWHERE debtorsmaster.paymentterms = paymentterms.termsindicator\n\t\t\tAND debtorsmaster.currcode = currencies.currabrev\n\t\t\tAND debtorsmaster.holdreason = holdreasons.reasoncode\n\t\t\tAND debtorsmaster.debtorno = '" . $_POST['CustomerID'] . "'";
$ErrMsg = _('The customer details could not be retrieved because');
$DbgMsg = _('The SQL that failed was');
$CustomerResult = DB_query($SQL, $db, $ErrMsg, $DbgMsg);
} else {
$NIL_BALANCE = False;
}
$_SESSION['CustomerRecord'] = DB_fetch_array($CustomerResult);
if ($NIL_BALANCE == True) {
$_POST['Status'] = $_GET['Status'];
}
} elseif (isset($_POST['Status'])) {
if ($_POST['Status'] == '' or $_POST['Status'] == 1 or $_POST['Status'] == 0) {
$Status = $_POST['Status'];
} else {
prnMsg(_('The balance status should be all or zero balance or not zero balance'), 'error');
exit;
}
} else {
$_POST['Status'] = '';
}
if (!isset($_POST['TransAfterDate'])) {
$_POST['TransAfterDate'] = Date($_SESSION['DefaultDateFormat'], Mktime(0, 0, 0, Date('m') - $_SESSION['NumberOfMonthMustBeShown'], Date('d'), Date('Y')));
}
$SQL = "SELECT debtorsmaster.name,\n\t\tcurrencies.currency,\n\t\tcurrencies.decimalplaces,\n\t\tpaymentterms.terms,\n\t\tdebtorsmaster.creditlimit,\n\t\tholdreasons.dissallowinvoices,\n\t\tholdreasons.reasondescription,\n\t\tSUM(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc) AS balance,\n\t\tSUM(CASE WHEN (paymentterms.daysbeforedue > 0) THEN\n\t\t\tCASE WHEN (TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate)) >= paymentterms.daysbeforedue\n\t\t\tTHEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END\n\t\tELSE\n\t\t\tCASE WHEN TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(debtortrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(debtortrans.trandate))', 'DAY') . ")) >= 0 THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END\n\t\tEND) AS due,\n\t\tSUM(CASE WHEN (paymentterms.daysbeforedue > 0) THEN\n\t\t\tCASE WHEN TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) > paymentterms.daysbeforedue\n\t\t\tAND TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays1'] . ")\n\t\t\tTHEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END\n\t\tELSE\n\t\t\tCASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(debtortrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(debtortrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays1'] . ")\n\t\t\tTHEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount\n\t\t\t- debtortrans.alloc ELSE 0 END\n\t\tEND) AS overdue1,\n\t\tSUM(CASE WHEN (paymentterms.daysbeforedue > 0) THEN\n\t\t\tCASE WHEN TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) > paymentterms.daysbeforedue\n\t\t\tAND TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays2'] . ") THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END\n\t\tELSE\n\t\t\tCASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(debtortrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(debtortrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays2'] . ") THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END\n\t\tEND) AS overdue2\n\t\tFROM debtorsmaster,\n\t \t\t\tpaymentterms,\n\t \t\t\tholdreasons,\n\t \t\t\tcurrencies,\n\t \t\t\tdebtortrans\n\t\tWHERE debtorsmaster.paymentterms = paymentterms.termsindicator\n\t \t\tAND debtorsmaster.currcode = currencies.currabrev\n\t \t\tAND debtorsmaster.holdreason = holdreasons.reasoncode\n\t \t\tAND debtorsmaster.debtorno = '" . $CustomerID . "'\n\t \t\tAND debtorsmaster.debtorno = debtortrans.debtorno\n\t\t\tGROUP BY debtorsmaster.name,\n\t\t\tcurrencies.currency,\n\t\t\tpaymentterms.terms,\n\t\t\tpaymentterms.daysbeforedue,\n\t\t\tpaymentterms.dayinfollowingmonth,\n\t\t\tdebtorsmaster.creditlimit,\n\t\t\tholdreasons.dissallowinvoices,\n\t\t\tholdreasons.reasondescription";
$ErrMsg = _('The customer details could not be retrieved by the SQL because');
$CustomerResult = DB_query($SQL, $ErrMsg);
if (DB_num_rows($CustomerResult) == 0) {
/*Because there is no balance - so just retrieve the header information about the customer - the choice is do one query to get the balance and transactions for those customers who have a balance and two queries for those who don't have a balance OR always do two queries - I opted for the former */
$NIL_BALANCE = True;
$SQL = "SELECT debtorsmaster.name,\n\t\t\t\t\tdebtorsmaster.currcode,\n\t\t\t\t\tcurrencies.currency,\n\t\t\t\t\tcurrencies.decimalplaces,\n\t\t\t\t\tpaymentterms.terms,\n\t\t\t\t\tdebtorsmaster.creditlimit,\n\t\t\t\t\tholdreasons.dissallowinvoices,\n\t\t\t\t\tholdreasons.reasondescription\n\t\t\tFROM debtorsmaster INNER JOIN paymentterms\n\t\t\tON debtorsmaster.paymentterms = paymentterms.termsindicator\n\t\t\tINNER JOIN currencies\n\t\t\tON debtorsmaster.currcode = currencies.currabrev\n\t\t\tINNER JOIN holdreasons\n\t\t\tON debtorsmaster.holdreason = holdreasons.reasoncode\n\t\t\tWHERE debtorsmaster.debtorno = '" . $CustomerID . "'";
$ErrMsg = _('The customer details could not be retrieved by the SQL because');
$CustomerResult = DB_query($SQL, $ErrMsg);
} else {
$NIL_BALANCE = False;
}
$CustomerRecord = DB_fetch_array($CustomerResult);
if ($NIL_BALANCE == True) {
$CustomerRecord['balance'] = 0;
$CustomerRecord['due'] = 0;
$_POST['CompanyNameField'] = $_GET['Database'];
}
if (isset($argc)) {
if (isset($argv[1])) {
$_SESSION['DatabaseName'] = $argv[1];
$DatabaseName = $argv[1];
$_POST['CompanyNameField'] = $argv[1];
}
}
include 'includes/session.inc';
$title = _('Recurring Orders Process');
include 'includes/header.inc';
include 'includes/SQL_CommonFunctions.inc';
include 'includes/GetSalesTransGLCodes.inc';
include 'includes/htmlMimeMail.php';
$sql = "SELECT recurringsalesorders.recurrorderno,\n\t\trecurringsalesorders.debtorno,\n \t\trecurringsalesorders.branchcode,\n \t\trecurringsalesorders.customerref,\n \t\trecurringsalesorders.buyername,\n \t\trecurringsalesorders.comments,\n \t\trecurringsalesorders.orddate,\n \t\trecurringsalesorders.ordertype,\n \t\trecurringsalesorders.shipvia,\n \t\trecurringsalesorders.deladd1,\n \t\trecurringsalesorders.deladd2,\n \t\trecurringsalesorders.deladd3,\n \t\trecurringsalesorders.deladd4,\n \t\trecurringsalesorders.deladd5,\n \t\trecurringsalesorders.deladd6,\n \t\trecurringsalesorders.contactphone,\n \t\trecurringsalesorders.contactemail,\n \t\trecurringsalesorders.deliverto,\n \t\trecurringsalesorders.freightcost,\n \t\trecurringsalesorders.fromstkloc,\n \t\trecurringsalesorders.lastrecurrence,\n \t\trecurringsalesorders.stopdate,\n \t\trecurringsalesorders.frequency,\n \t\trecurringsalesorders.autoinvoice,\n\t\tdebtorsmaster.name,\n\t\tdebtorsmaster.currcode,\n\t\tsalestypes.sales_type,\n\t\tcustbranch.area,\n\t\tcustbranch.taxgroupid,\n\t\tlocations.contact,\n\t\tlocations.email\n\tFROM recurringsalesorders,\n\t\tdebtorsmaster,\n\t\tcustbranch,\n\t\tsalestypes,\n\t\tlocations\n\tWHERE recurringsalesorders.ordertype=salestypes.typeabbrev\n\tAND recurringsalesorders.debtorno = debtorsmaster.debtorno\n\tAND recurringsalesorders.debtorno = custbranch.debtorno\n\tAND recurringsalesorders.branchcode = custbranch.branchcode\n\tAND recurringsalesorders.fromstkloc=locations.loccode\n\tAND recurringsalesorders.ordertype=salestypes.typeabbrev\n\tAND (TO_DAYS(NOW()) - TO_DAYS(recurringsalesorders.lastrecurrence)) > (365/recurringsalesorders.frequency)\n\tAND DATE_ADD(recurringsalesorders.lastrecurrence, " . INTERVAL('365/recurringsalesorders.frequency', 'DAY') . ") <= recurringsalesorders.stopdate";
$RecurrOrdersDueResult = DB_query($sql, $db, _('There was a problem retrieving the recurring sales order templates. The database reported:'));
if (DB_num_rows($RecurrOrdersDueResult) == 0) {
prnMsg(_('There are no recurring order templates that are due to have another recurring order created'), 'warn');
include 'includes/footer.inc';
exit;
}
prnMsg(_('The number of recurring orders to process is') . ' : ' . DB_num_rows($RecurrOrdersDueResult), 'info');
while ($RecurrOrderRow = DB_fetch_array($RecurrOrdersDueResult)) {
$EmailText = '';
echo '<br />' . _('Recurring order') . ' ' . $RecurrOrderRow['recurrorderno'] . ' ' . _('for') . ' ' . $RecurrOrderRow['debtorno'] . ' - ' . $RecurrOrderRow['branchcode'] . ' ' . _('is being processed');
$result = DB_Txn_Begin($db);
/*the last recurrence was the date of the last time the order recurred
the frequency is the number of times per annum that the order should recurr
so 365 / frequency gives the number of days between recurrences */
$DelDate = FormatDateforSQL(DateAdd(ConvertSQLDate($RecurrOrderRow['lastrecurrence']), 'd', 365 / $RecurrOrderRow['frequency']));
$TotOD1 += $AgedAnalysis['overdue1'] - $AgedAnalysis['overdue2'];
$TotOD2 += $AgedAnalysis['overdue2'];
$LeftOvers = $pdf->addTextWrap($Left_Margin, $YPos, 220 - $Left_Margin, $FontSize, $AgedAnalysis['debtorno'] . ' - ' . $AgedAnalysis['name'], 'left');
$LeftOvers = $pdf->addTextWrap(220, $YPos, 60, $FontSize, $DisplayBalance, 'right');
$LeftOvers = $pdf->addTextWrap(280, $YPos, 60, $FontSize, $DisplayCurrent, 'right');
$LeftOvers = $pdf->addTextWrap(340, $YPos, 60, $FontSize, $DisplayDue, 'right');
$LeftOvers = $pdf->addTextWrap(400, $YPos, 60, $FontSize, $DisplayOverdue1, 'right');
$LeftOvers = $pdf->addTextWrap(460, $YPos, 60, $FontSize, $DisplayOverdue2, 'right');
$YPos -= $line_height;
if ($YPos < $Bottom_Margin + $line_height) {
include 'includes/PDFAgedDebtorsPageHeader.inc';
}
if ($_POST['DetailedReport'] == 'Yes') {
/*draw a line under the customer aged analysis*/
$pdf->line($Page_Width - $Right_Margin, $YPos + 10, $Left_Margin, $YPos + 10);
$sql = "SELECT systypes.typename,\n\t\t\t\t\t\tdebtortrans.transno,\n\t\t\t\t\t\tdebtortrans.trandate,\n\t\t\t\t\t\t(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc) as balance,\n\t\t\t\t\t\t(CASE WHEN (paymentterms.daysbeforedue > 0)\n\t\t\t\t\t\t\tTHEN\n\t\t\t\t\t\t\t\t(CASE WHEN (TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate)) >= paymentterms.daysbeforedue\n\t\t\t\t\t\t\t\tTHEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc\n\t\t\t\t\t\t\t\tELSE 0 END)\n\t\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\t\t(CASE WHEN TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(debtortrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(debtortrans.trandate))', 'DAY') . ")) >= 0\n\t\t\t\t\t\t\t\tTHEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc\n\t\t\t\t\t\t\t\tELSE 0 END)\n\t\t\t\t\t\tEND) AS due,\n\t\t\t\t\t\t(CASE WHEN (paymentterms.daysbeforedue > 0)\n\t\t\t\t\t\t\tTHEN\n\t\t\t\t\t\t\t\t(CASE WHEN TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays1'] . ") THEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc ELSE 0 END)\n\t\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\t\t(CASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(debtortrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(debtortrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays1'] . ")\n\t\t\t\t\t\t\t\tTHEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc\n\t\t\t\t\t\t\t\tELSE 0 END)\n\t\t\t\t\t\tEND) AS overdue1,\n\t\t\t\t\t\t(CASE WHEN (paymentterms.daysbeforedue > 0)\n\t\t\t\t\t\t\tTHEN\n\t\t\t\t\t\t\t\t(CASE WHEN TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(debtortrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays2'] . ")\n\t\t\t\t\t\t\t\tTHEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc\n\t\t\t\t\t\t\t\tELSE 0 END)\n\t\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\t\t(CASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(debtortrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(debtortrans.trandate))', 'DAY') . ")) >= " . $_SESSION['PastDueDays2'] . ")\n\t\t\t\t\t\t\t\tTHEN debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc\n\t\t\t\t\t\t\t\tELSE 0 END)\n\t\t\t\t\t\tEND) AS overdue2\n\t\t\t\t FROM debtorsmaster,\n\t\t\t\t\t\tpaymentterms,\n\t\t\t\t\t\tdebtortrans,\n\t\t\t\t\t\tsystypes\n\t\t\t\t WHERE systypes.typeid = debtortrans.type\n\t\t\t\t\t\tAND debtorsmaster.paymentterms = paymentterms.termsindicator\n\t\t\t\t\t\tAND debtorsmaster.debtorno = debtortrans.debtorno\n\t\t\t\t\t\tAND debtortrans.debtorno = '" . $AgedAnalysis['debtorno'] . "'\n\t\t\t\t\t\tAND ABS(debtortrans.ovamount + debtortrans.ovgst + debtortrans.ovfreight + debtortrans.ovdiscount - debtortrans.alloc)>0.004";
$DetailResult = DB_query($sql, $db, '', '', False, False);
/*Dont trap errors */
if (DB_error_no($db) != 0) {
$Title = _('Aged Customer Account Analysis') . ' - ' . _('Problem Report') . '....';
include 'includes/header.inc';
prnMsg(_('The details of outstanding transactions for customer') . ' - ' . $AgedAnalysis['debtorno'] . ' ' . _('could not be retrieved because') . ' - ' . DB_error_msg($db), 'error');
echo '<br /><a href="' . $RootPath . '/index.php">' . _('Back to the menu') . '</a>';
if ($debug == 1) {
echo '<br />' . _('The SQL that failed was') . '<br />' . $sql;
}
include 'includes/footer.inc';
exit;
}
while ($DetailTrans = DB_fetch_array($DetailResult)) {
$LeftOvers = $pdf->addTextWrap($Left_Margin + 5, $YPos, 60, $FontSize, $DetailTrans['typename'], 'left');
ELSE
CASE WHEN TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, ' . INTERVAL('1', 'MONTH') . '), ' . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ')) >= 0 THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
END) AS due,
SUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN
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
ELSE
CASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, ' . INTERVAL('1', 'MONTH') . '), ' . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ')) >= ' . $_SESSION['PastDueDays1'] . ')
THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END
END) AS overdue1,
Sum(CASE WHEN paymentterms.daysbeforedue > 0 THEN
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
ELSE
CASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, ' . INTERVAL('1', 'MONTH') . '), ' . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ')) >= ' . $_SESSION['PastDueDays2'] . ")\n\t\t\tTHEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\tEND ) AS overdue2\n\t\tFROM suppliers,\n \t\t\tpaymentterms,\n \t\t\tcurrencies,\n \t\t\tsupptrans\n\t\tWHERE suppliers.paymentterms = paymentterms.termsindicator\n \t\tAND suppliers.currcode = currencies.currabrev\n \t\tAND suppliers.supplierid = '" . $SupplierID . "'\n \t\tAND suppliers.supplierid = supptrans.supplierno\n\t\tGROUP BY suppliers.suppname,\n \t\t\tcurrencies.currency,\n \t\t\tpaymentterms.terms,\n \t\t\tpaymentterms.daysbeforedue,\n \t\t\tpaymentterms.dayinfollowingmonth";
$ErrMsg = _('The supplier details could not be retrieved by the SQL because');
$DbgMsg = _('The SQL that failed was');
$SupplierResult = DB_query($SQL, $db, $ErrMsg, $DbgMsg);
if (DB_num_rows($SupplierResult) == 0) {
/*Because there is no balance - so just retrieve the header information about the Supplier - the choice is do one query to get the balance and transactions for those Suppliers who have a balance and two queries for those who don't have a balance OR always do two queries - I opted for the former */
$NIL_BALANCE = True;
$SQL = "SELECT suppliers.suppname, \n\t\t\tcurrencies.currency, \n\t\t\tpaymentterms.terms\n\t\tFROM suppliers,\n\t \t\tpaymentterms,\n\t \t\tcurrencies\n\t\tWHERE suppliers.paymentterms = paymentterms.termsindicator\n\t\tAND suppliers.currcode = currencies.currabrev\n\t\tAND suppliers.supplierid = '" . $SupplierID . "'";
$ErrMsg = _('The supplier details could not be retrieved by the SQL because');
$DbgMsg = _('The SQL that failed was');
$SupplierResult = DB_query($SQL, $db, $ErrMsg, $DbgMsg);
} else {
$NIL_BALANCE = False;
}
$SupplierRecord = DB_fetch_array($SupplierResult);
if ($NIL_BALANCE == True) {
</div>';
include 'includes/footer.inc';
exit;
} else {
if (isset($_GET['SupplierID'])) {
$_SESSION['SupplierID'] = $_GET['SupplierID'];
}
$SupplierID = $_SESSION['SupplierID'];
}
if (isset($_GET['FromDate'])) {
$_POST['TransAfterDate'] = $_GET['FromDate'];
}
if (!isset($_POST['TransAfterDate']) or !Is_Date($_POST['TransAfterDate'])) {
$_POST['TransAfterDate'] = Date($_SESSION['DefaultDateFormat'], Mktime(0, 0, 0, Date('m') - 12, Date('d'), Date('Y')));
}
$SQL = "SELECT suppliers.suppname,\n\t\tsuppliers.currcode,\n\t\tcurrencies.currency,\n\t\tcurrencies.decimalplaces AS currdecimalplaces,\n\t\tpaymentterms.terms,\n\t\tSUM(supptrans.ovamount + supptrans.ovgst - supptrans.alloc) AS balance,\n\t\tSUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN\n\t\t\tCASE WHEN (TO_DAYS(Now()) - TO_DAYS(supptrans.trandate)) >= paymentterms.daysbeforedue\n\t\t\tTHEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\tELSE\n\t\t\tCASE WHEN TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= 0 THEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\tEND) AS due,\n\t\tSUM(CASE WHEN paymentterms.daysbeforedue > 0 THEN\n\t\t\tCASE WHEN (TO_DAYS(Now()) - TO_DAYS(supptrans.trandate)) > paymentterms.daysbeforedue\n\t\t\t\t\tAND (TO_DAYS(Now()) - TO_DAYS(supptrans.trandate)) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays1'] . ")\n\t\t\tTHEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\tELSE\n\t\t\tCASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= '" . $_SESSION['PastDueDays1'] . "')\n\t\t\tTHEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\tEND) AS overdue1,\n\t\tSum(CASE WHEN paymentterms.daysbeforedue > 0 THEN\n\t\t\tCASE WHEN TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) > paymentterms.daysbeforedue AND TO_DAYS(Now()) - TO_DAYS(supptrans.trandate) >= (paymentterms.daysbeforedue + " . $_SESSION['PastDueDays2'] . ")\n\t\t\tTHEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\tELSE\n\t\t\tCASE WHEN (TO_DAYS(Now()) - TO_DAYS(DATE_ADD(DATE_ADD(supptrans.trandate, " . INTERVAL('1', 'MONTH') . "), " . INTERVAL('(paymentterms.dayinfollowingmonth - DAYOFMONTH(supptrans.trandate))', 'DAY') . ")) >= '" . $_SESSION['PastDueDays2'] . "')\n\t\t\tTHEN supptrans.ovamount + supptrans.ovgst - supptrans.alloc ELSE 0 END\n\t\tEND ) AS overdue2\n\t\tFROM suppliers INNER JOIN paymentterms\n\t\tON suppliers.paymentterms = paymentterms.termsindicator\n\t \tINNER JOIN currencies\n\t \tON suppliers.currcode = currencies.currabrev\n\t \tINNER JOIN supptrans\n\t \tON suppliers.supplierid = supptrans.supplierno\n\t\tWHERE suppliers.supplierid = '" . $SupplierID . "'\n\t\tGROUP BY suppliers.suppname,\n\t \t\t\tcurrencies.currency,\n\t \t\t\tcurrencies.decimalplaces,\n\t \t\t\tpaymentterms.terms,\n\t \t\t\tpaymentterms.daysbeforedue,\n\t \t\t\tpaymentterms.dayinfollowingmonth";
$ErrMsg = _('The supplier details could not be retrieved by the SQL because');
$DbgMsg = _('The SQL that failed was');
$SupplierResult = DB_query($SQL, $db, $ErrMsg, $DbgMsg);
if (DB_num_rows($SupplierResult) == 0) {
/*Because there is no balance - so just retrieve the header information about the Supplier - the choice is do one query to get the balance and transactions for those Suppliers who have a balance and two queries for those who don't have a balance OR always do two queries - I opted for the former */
$NIL_BALANCE = True;
$SQL = "SELECT suppliers.suppname,\n\t\t\t\t\tsuppliers.currcode,\n\t\t\t\t\tcurrencies.currency,\n\t\t\t\t\tcurrencies.decimalplaces AS currdecimalplaces,\n\t\t\t\t\tpaymentterms.terms\n\t\t\tFROM suppliers INNER JOIN paymentterms\n\t\t\tON suppliers.paymentterms = paymentterms.termsindicator\n\t\t\tINNER JOIN currencies\n\t\t\tON suppliers.currcode = currencies.currabrev\n\t\t\tWHERE suppliers.supplierid = '" . $SupplierID . "'";
$ErrMsg = _('The supplier details could not be retrieved by the SQL because');
$DbgMsg = _('The SQL that failed was');
$SupplierResult = DB_query($SQL, $db, $ErrMsg, $DbgMsg);
} else {
$NIL_BALANCE = False;
}
$SupplierRecord = DB_fetch_array($SupplierResult);
if ($NIL_BALANCE == True) {
$_POST['CompanyNameField'] = $argv[1];
}
}
include 'includes/session.inc';
$Title = _('Recurring Orders Process');
/* KwaMoja manual links before header.inc */
$ViewTopic = 'SalesOrders';
$BookMark = 'RecurringSalesOrders';
include 'includes/header.inc';
include 'includes/SQL_CommonFunctions.inc';
include 'includes/GetSalesTransGLCodes.inc';
include 'includes/htmlMimeMail.php';
if ($_SESSION['RestrictLocations'] == 0) {
$sql = "SELECT recurringsalesorders.recurrorderno,\n\t\t\t\t\trecurringsalesorders.debtorno,\n\t\t\t\t\trecurringsalesorders.branchcode,\n\t\t\t\t\trecurringsalesorders.customerref,\n\t\t\t\t\trecurringsalesorders.buyername,\n\t\t\t\t\trecurringsalesorders.comments,\n\t\t\t\t\trecurringsalesorders.orddate,\n\t\t\t\t\trecurringsalesorders.ordertype,\n\t\t\t\t\trecurringsalesorders.shipvia,\n\t\t\t\t\trecurringsalesorders.deladd1,\n\t\t\t\t\trecurringsalesorders.deladd2,\n\t\t\t\t\trecurringsalesorders.deladd3,\n\t\t\t\t\trecurringsalesorders.deladd4,\n\t\t\t\t\trecurringsalesorders.deladd5,\n\t\t\t\t\trecurringsalesorders.deladd6,\n\t\t\t\t\trecurringsalesorders.contactphone,\n\t\t\t\t\trecurringsalesorders.contactemail,\n\t\t\t\t\trecurringsalesorders.deliverto,\n\t\t\t\t\trecurringsalesorders.freightcost,\n\t\t\t\t\trecurringsalesorders.fromstkloc,\n\t\t\t\t\trecurringsalesorders.lastrecurrence,\n\t\t\t\t\trecurringsalesorders.stopdate,\n\t\t\t\t\trecurringsalesorders.frequency,\n\t\t\t\t\trecurringsalesorders.autoinvoice,\n\t\t\t\t\tdebtorsmaster.name,\n\t\t\t\t\tdebtorsmaster.currcode,\n\t\t\t\t\tsalestypes.sales_type,\n\t\t\t\t\tcustbranch.area,\n\t\t\t\t\tcustbranch.taxgroupid,\n\t\t\t\t\tlocations.contact,\n\t\t\t\t\tlocations.email\n\t\t\t\tFROM recurringsalesorders\n\t\t\t\tINNER JOIN debtorsmaster\n\t\t\t\t\tON recurringsalesorders.debtorno = debtorsmaster.debtorno\n\t\t\t\tINNER JOIN custbranch\n\t\t\t\t\tON recurringsalesorders.debtorno = custbranch.debtorno\n\t\t\t\t\tAND recurringsalesorders.branchcode = custbranch.branchcode\n\t\t\t\tINNER JOIN salestypes\n\t\t\t\t\tON recurringsalesorders.ordertype=salestypes.typeabbrev\n\t\t\t\tINNER JOIN locations\n\t\t\t\t\tON recurringsalesorders.fromstkloc=locations.loccode\n\t\t\t\tWHERE (TO_DAYS(CURRENT_DATE) - TO_DAYS(recurringsalesorders.lastrecurrence)) > (365/recurringsalesorders.frequency)\n\t\t\t\t\tAND DATE_ADD(recurringsalesorders.lastrecurrence, " . INTERVAL('365/recurringsalesorders.frequency', 'DAY') . ") <= recurringsalesorders.stopdate";
} else {
$sql = "SELECT recurringsalesorders.recurrorderno,\n\t\t\t\t\trecurringsalesorders.debtorno,\n\t\t\t\t\trecurringsalesorders.branchcode,\n\t\t\t\t\trecurringsalesorders.customerref,\n\t\t\t\t\trecurringsalesorders.buyername,\n\t\t\t\t\trecurringsalesorders.comments,\n\t\t\t\t\trecurringsalesorders.orddate,\n\t\t\t\t\trecurringsalesorders.ordertype,\n\t\t\t\t\trecurringsalesorders.shipvia,\n\t\t\t\t\trecurringsalesorders.deladd1,\n\t\t\t\t\trecurringsalesorders.deladd2,\n\t\t\t\t\trecurringsalesorders.deladd3,\n\t\t\t\t\trecurringsalesorders.deladd4,\n\t\t\t\t\trecurringsalesorders.deladd5,\n\t\t\t\t\trecurringsalesorders.deladd6,\n\t\t\t\t\trecurringsalesorders.contactphone,\n\t\t\t\t\trecurringsalesorders.contactemail,\n\t\t\t\t\trecurringsalesorders.deliverto,\n\t\t\t\t\trecurringsalesorders.freightcost,\n\t\t\t\t\trecurringsalesorders.fromstkloc,\n\t\t\t\t\trecurringsalesorders.lastrecurrence,\n\t\t\t\t\trecurringsalesorders.stopdate,\n\t\t\t\t\trecurringsalesorders.frequency,\n\t\t\t\t\trecurringsalesorders.autoinvoice,\n\t\t\t\t\tdebtorsmaster.name,\n\t\t\t\t\tdebtorsmaster.currcode,\n\t\t\t\t\tsalestypes.sales_type,\n\t\t\t\t\tcustbranch.area,\n\t\t\t\t\tcustbranch.taxgroupid,\n\t\t\t\t\tlocations.contact,\n\t\t\t\t\tlocations.email\n\t\t\t\tFROM recurringsalesorders\n\t\t\t\tINNER JOIN debtorsmaster\n\t\t\t\t\tON recurringsalesorders.debtorno = debtorsmaster.debtorno\n\t\t\t\tINNER JOIN custbranch\n\t\t\t\t\tON recurringsalesorders.debtorno = custbranch.debtorno\n\t\t\t\t\tAND recurringsalesorders.branchcode = custbranch.branchcode\n\t\t\t\tINNER JOIN salestypes\n\t\t\t\t\tON recurringsalesorders.ordertype=salestypes.typeabbrev\n\t\t\t\tINNER JOIN locations\n\t\t\t\t\tON recurringsalesorders.fromstkloc=locations.loccode\n\t\t\t\tINNER JOIN www_users\n\t\t\t\t\tON locations.loccode=www_users.defaultlocation\n\t\t\t\tWHERE (TO_DAYS(CURRENT_DATE) - TO_DAYS(recurringsalesorders.lastrecurrence)) > (365/recurringsalesorders.frequency)\n\t\t\t\t\tAND DATE_ADD(recurringsalesorders.lastrecurrence, " . INTERVAL('365/recurringsalesorders.frequency', 'DAY') . ") <= recurringsalesorders.stopdate\n\t\t\t\t\tAND www_users.userid='" . $_SESSION['UserID'] . "'";
}
$RecurrOrdersDueResult = DB_query($sql, $db, _('There was a problem retrieving the recurring sales order templates. The database reported:'));
if (DB_num_rows($RecurrOrdersDueResult) == 0) {
prnMsg(_('There are no recurring order templates that are due to have another recurring order created'), 'warn');
include 'includes/footer.inc';
exit;
}
prnMsg(_('The number of recurring orders to process is') . ' : ' . DB_num_rows($RecurrOrdersDueResult), 'info');
while ($RecurrOrderRow = DB_fetch_array($RecurrOrdersDueResult)) {
$EmailText = '';
echo '<br />' . _('Recurring order') . ' ' . $RecurrOrderRow['recurrorderno'] . ' ' . _('for') . ' ' . $RecurrOrderRow['debtorno'] . ' - ' . $RecurrOrderRow['branchcode'] . ' ' . _('is being processed');
$result = DB_Txn_Begin($db);
/*the last recurrence was the date of the last time the order recurred
the frequency is the number of times per annum that the order should recurr
so 365 / frequency gives the number of days between recurrences */
