$query = "INSERT INTO ar_history_sum\n SELECT card_no,SUM(charges),SUM(payments),SUM(charges)-SUM(payments)\n FROM ar_history GROUP BY card_no";
$rslt = $sql->query($query);
if ($rslt === False) {
echo cron_msg("Failed: {$query}");
}
/* turnover view/cache base tables for WFC end-of-month reports */
if (date("j") == 1 && $sql->table_exists("ar_history_backup")) {
$query = "TRUNCATE TABLE ar_history_backup";
$rslt = $sql->query($query);
if ($rslt === False) {
echo cron_msg("Failed: {$query}");
}
$query = "INSERT INTO ar_history_backup SELECT * FROM ar_history";
$rslt = $sql->query($query);
if ($rslt === False) {
echo cron_msg("Failed: {$query}");
}
$AR_EOM_Summary_Q = "\n INSERT INTO AR_EOM_Summary\n SELECT c.CardNo," . $sql->concat("c.FirstName", "' '", "c.LastName", '') . " AS memName,\n\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -4\n THEN charges ELSE 0 END)\n - SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -4\n THEN payments ELSE 0 END) AS priorBalance,\n\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " = -3\n THEN a.charges ELSE 0 END) AS threeMonthCharges,\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " = -3\n THEN a.payments ELSE 0 END) AS threeMonthPayments,\n\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -3\n THEN charges ELSE 0 END)\n - SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -3\n THEN payments ELSE 0 END) AS threeMonthBalance,\n\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " = -2\n THEN a.charges ELSE 0 END) AS twoMonthCharges,\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " = -2\n THEN a.payments ELSE 0 END) AS twoMonthPayments,\n\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -2\n THEN charges ELSE 0 END)\n - SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -2\n THEN payments ELSE 0 END) AS twoMonthBalance,\n\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " = -1\n THEN a.charges ELSE 0 END) AS lastMonthCharges,\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " = -1\n THEN a.payments ELSE 0 END) AS lastMonthPayments,\n\n SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -1\n THEN charges ELSE 0 END)\n - SUM(CASE WHEN " . $sql->monthdiff('a.tdate', $sql->now()) . " <= -1\n THEN payments ELSE 0 END) AS lastMonthBalance\n\n FROM ar_history_backup AS a LEFT JOIN " . $FANNIE_OP_DB . $sql->sep() . "custdata AS c \n ON a.card_no=c.CardNo AND c.personNum=1\n GROUP BY c.CardNo,c.LastName,c.FirstName";
if ($sql->table_exists("AR_EOM_Summary")) {
$query = "TRUNCATE TABLE AR_EOM_Summary";
$rslt = $sql->query($query);
if ($rslt === False) {
echo cron_msg("Failed: {$query}");
}
$rslt = $sql->query($AR_EOM_Summary_Q);
if ($rslt === False) {
echo cron_msg("Failed: {$AR_EOM_Summary_Q}");
}
}
}
echo cron_msg("Done.");
}
if (!function_exists('cron_msg')) {
include $FANNIE_ROOT . 'src/cron_msg.php';
}
/* PURPOSE:
Crunch the previous month's total sales &
deliveries into a single archive record
*/
set_time_limit(0);
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$deliveryQ = "INSERT INTO InvDeliveryArchive\n SELECT max(inv_date),upc,vendor_id,sum(quantity),sum(price)\n FROM InvDeliveryLM \n GROUP BY upc,vendor_id";
$chk = $sql->query($deliveryQ);
if ($chk === false) {
echo cron_msg("Error archiving last month's inventory data");
}
$chk1 = $sql->query("TRUNCATE TABLE InvDeliveryLM");
$lmQ = "INSERT INTO InvDeliveryLM SELECT * FROM InvDelivery WHERE " . $sql->monthdiff($sql->now(), 'inv_date') . " = 1";
$chk2 = $sql->query($lmQ);
if ($chk1 === false || $chk2 === false) {
echo cron_msg("Error setting up last month's inventory data");
}
$clearQ = "DELETE FROM InvDelivery WHERE " . $sql->monthdiff($sql->now(), 'inv_date') . " = 1";
$chk = $sql->query($clearQ);
if ($chk === false) {
echo cron_msg("Error clearing inventory data");
}
$salesQ = "INSERT INTO InvSalesArchive\n select max(datetime),upc,sum(quantity),sum(total)\n FROM transarchive WHERE " . $sql->monthdiff($sql->now(), 'datetime') . " = 1\n AND scale=0 AND trans_status NOT IN ('X','R') \n AND trans_type = 'I' AND trans_subtype <> '0'\n AND register_no <> 99 AND emp_no <> 9999\n GROUP BY upc";
$chk = $sql->query($salesQ);
if ($chk === false) {
echo cron_msg("Error archiving sales data for inventory");
}
