Track virtual coupon usage
Adjust custdata settings to match
*/
include dirname(__FILE__) . '/../config.php';
if (!class_exists('FannieAPI')) {
include $FANNIE_ROOT . 'classlib2.0/FannieAPI.php';
}
if (!function_exists('cron_msg')) {
include $FANNIE_ROOT . 'src/cron_msg.php';
}
set_time_limit(0);
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$TRANS = $FANNIE_SERVER_DBMS == "MSSQL" ? $FANNIE_TRANS_DB . ".dbo." : $FANNIE_TRANS_DB . ".";
// create a temp table to hold info about recently used coupons
if (!$sql->table_exists("TempVirtCoupon")) {
$sql->query("CREATE TABLE TempVirtCoupon (card_no int, coupID int, quantity double,PRIMARY KEY(card_no,coupID))");
} else {
$sql->query("TRUNCATE TABLE TempVirtCoupon");
}
// select number of coupons used by each member in
// the applicable period
$insQ = "INSERT INTO TempVirtCoupon\n select d.card_no, h.coupID, sum(quantity) as quantity\n from {$TRANS}dlog_90_view as d\n INNER JOIN houseVirtualCoupons as h\n ON d.upc=" . $sql->concat("'00499999'", 'RIGHT(' . $sql->concat("'00000'", $sql->convert('h.coupID', 'CHAR'), '') . ',5)', '') . "\n AND d.card_no=h.card_no\n where d.tdate >= h.start_date and d.tdate <= h.end_date";
$insR = $sql->query($insQ);
// remove expired or already-used coupons
$sqlQ = "DELETE h FROM houseVirtualCoupons AS h\n LEFT JOIN TempVirtCoupon AS t ON\n h.card_no=t.card_no AND h.coupID=t.coupID\n WHERE " . $sql->now() . " > h.end_date\n OR (t.card_no IS NOT NULL AND t.coupID IS NOT NULL)";
if ($FANNIE_SERVER_DBMS == "MSSQL") {
$sqlQ = "DELETE FROM houseVirtualCoupons \n FROM houseVirtualCoupons AS h\n LEFT JOIN TempVirtCoupon AS t ON\n h.card_no=t.card_no AND h.coupID=t.coupID\n WHERE " . $sql->now() . " > h.end_date\n OR (t.card_no IS NOT NULL AND t.coupID IS NOT NULL)";
}
// set custdata.memcoupons equal to the number
// of available coupons (in theory)
}
$dlist = substr($dlist, 0, strlen($dlist) - 1) . ")";
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_TRANS_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$query = "INSERT INTO ar_history\n SELECT card_no,\n CASE WHEN trans_subtype='MI' THEN -total ELSE 0 END AS charges,\n CASE WHEN department IN {$dlist} THEN total ELSE 0 END as payments,\n tdate,trans_num\n FROM dlog_15\n WHERE " . $sql->datediff($sql->now(), 'tdate') . " = 1\n AND (department IN {$dlist} OR trans_subtype='MI')";
$rslt = $sql->query($query);
if ($rslt === False) {
echo cron_msg("Failed: {$query}");
}
$sql->query("TRUNCATE TABLE ar_history_sum");
$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) {
function removeAllLanes($upc, $table_name)
{
$FANNIE_OP_DB = FannieConfig::config('OP_DB');
$FANNIE_LANES = FannieConfig::config('LANES');
for ($i = 0; $i < count($FANNIE_LANES); $i++) {
$tmp = new SQLManager($FANNIE_LANES[$i]['host'], $FANNIE_LANES[$i]['type'], $FANNIE_LANES[$i]['op'], $FANNIE_LANES[$i]['user'], $FANNIE_LANES[$i]['pw']);
if ($tmp->table_exists("{$table_name}")) {
$delQ = "DELETE FROM {$table_name} WHERE upc='{$upc}'";
$delR = $tmp->query($delQ, $FANNIE_LANES[$i]['op']);
}
}
}
}
if (!function_exists('cron_msg')) {
include $FANNIE_ROOT . 'src/cron_msg.php';
}
if (!isset($FANNIE_LANES) || !is_array($FANNIE_LANES)) {
$FANNIE_LANES = array();
}
set_time_limit(0);
foreach ($FANNIE_LANES as $ln) {
$sql = new SQLManager($ln['host'], $ln['type'], $ln['trans'], $ln['user'], $ln['pw']);
if ($sql === False) {
echo cron_msg("Could not connect to lane: " . $ln['host']);
continue;
}
$table = 'localtrans_today';
if ($sql->table_exists($table)) {
$cleanQ = "DELETE FROM {$table} WHERE " . $sql->datediff($sql->now(), 'datetime') . " <> 0";
$cleanR = $sql->query($cleanQ, $ln['trans']);
if ($cleanR === False) {
echo cron_msg("Could not clean {$table} on lane: " . $ln['host']);
}
}
$table = 'localtrans';
$cleanQ = "DELETE FROM {$table} WHERE " . $sql->datediff($sql->now(), 'datetime') . " > 30";
$cleanR = $sql->query($cleanQ, $ln['trans']);
if ($cleanR === False) {
echo cron_msg("Could not clean {$table} on lane: " . $ln['host']);
}
$table = 'efsnetTokens';
$cleanQ = "DELETE FROM {$table} WHERE " . $sql->datediff($sql->now(), 'expireDay') . " <> 0 ";
$cleanR = $sql->query($cleanQ, $ln['trans']);
$chk = $sql->query("INSERT INTO batchMergeTable \n SELECT b.startDate, b.endDate, p.upc, p.description, b.batchID\n FROM batchList AS l LEFT JOIN batches AS b\n ON b.batchID=l.batchID INNER JOIN upcLike AS u\n ON l.upc = " . $sql->concat('LC', $sql->convert('u.likeCode', 'CHAR'), '') . ' ' . DTrans::joinProducts('u', 'p', 'INNER') . "\n WHERE p.upc IS NOT NULL");
if ($chk === False) {
echo cron_msg("Could not load batch reporting data for likecodes");
}
$sql->query("use {$FANNIE_TRANS_DB}");
$cashierPerformanceSQL = "\n SELECT\n min(tdate) as proc_date,\n max(emp_no) as emp_no,\n max(trans_num) as Trans_Num,\n min(tdate) as startTime,\n max(tdate) as endTime,\n CASE WHEN " . $sql->seconddiff('min(tdate)', 'max(tdate)') . " =0 \n then 1 else \n " . $sql->seconddiff('min(tdate)', 'max(tdate)') . "\n END as transInterval,\n sum(CASE WHEN abs(quantity) > 30 THEN 1 else abs(quantity) END) as items,\n Count(upc) as rings,\n SUM(case when trans_status = 'V' then 1 ELSE 0 END) AS Cancels,\n max(card_no) as card_no\n from dlog_90_view \n where trans_type IN ('I','D','0','C')\n group by year(tdate),month(tdate),day(tdate),trans_num";
if (!$sql->isView('CashPerformDay')) {
$chk = $sql->query("TRUNCATE TABLE CashPerformDay");
if ($chk === False) {
echo cron_msg("Could not truncate CashPerformDay");
}
$chk = $sql->query("INSERT INTO CashPerformDay " . $cashierPerformanceSQL);
if ($chk === False) {
echo cron_msg("Could not load data for CashPerformDay");
}
}
if ($sql->tableExists('CashPerformDay_cache')) {
$chk = $sql->query("TRUNCATE TABLE CashPerformDay_cache");
if ($chk === False) {
echo cron_msg("Could not truncate CashPerformDay_cache");
}
$chk = $sql->query("INSERT INTO CashPerformDay_cache " . $cashierPerformanceSQL);
if ($chk === False) {
echo cron_msg("Could not load data for CashPerformDay_cache");
}
}
$sql->query("USE " . $FANNIE_ARCHIVE_DB);
if ($sql->table_exists("reportDataCache")) {
$sql->query("DELETE FROM reportDataCache WHERE expires < " . $sql->now());
}
echo cron_msg("Success");
