AR_EOM_Summary.twoMonthBalance <= ar_live_balance.balance When/how-often can/should it be run? Daily? */ /* --COMMENTS - - - - - - - - - - - - - - - - - - - - - - - - - - * * 18Oct12 EL Keep this comment block from appearing in the Help popup. * Reformat SQL statements. * 17Jun12 EL Fix Help to make it appropriate to this program. * Was a copy of reactivate.equity.php. */ set_time_limit(0); ini_set('memory_limit', '256M'); $sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW); $TRANS = $FANNIE_TRANS_DB . ($FANNIE_SERVER_DBMS == "MSSQL" ? 'dbo.' : '.'); $custdata = $sql->table_definition('custdata'); $susQ = "INSERT INTO suspensions\n select m.card_no,'I',c.memType,c.Type,'',\n " . $sql->now() . ",m.ads_OK,c.Discount,\n c.ChargeLimit,1\n from meminfo as m left join\n custdata as c on c.CardNo=m.card_no and c.personNum=1\n left join {$TRANS}ar_live_balance as n on m.card_no=n.card_no\n left join {$TRANS}AR_EOM_Summary AS a ON a.cardno=m.card_no\n where a.twoMonthBalance <= n.balance\n AND a.lastMonthPayments < a.twoMonthBalance\n and c.type='PC' and n.balance > 0\n and c.memtype in (1,3)\n and NOT EXISTS(SELECT NULL FROM suspensions as s\n WHERE s.cardno=m.card_no)"; if (!isset($custdata['ChargeLimit'])) { $susQ = str_replace('c.ChargeLimit', 'c.MemDiscountLimit', $susQ); } $sql->query($susQ); $histQ = "INSERT INTO suspension_history\n select 'automatic'," . $sql->now() . ",'',\n m.card_no,1\n from meminfo as m left join\n custdata as c on c.CardNo=m.card_no and c.personNum=1\n left join {$TRANS}ar_live_balance as n on m.card_no=n.card_no\n left join {$TRANS}AR_EOM_Summary AS a ON a.cardno=m.card_no\n where a.twoMonthBalance <= n.balance\n AND a.lastMonthPayments < a.twoMonthBalance\n and c.type='PC' and n.balance > 0\n and c.memtype in (1,3)\n and NOT EXISTS(SELECT NULL FROM suspensions as s\n WHERE s.cardno=m.card_no)"; $sql->query($histQ); $custQ = "UPDATE custdata AS c\n LEFT JOIN suspensions AS s ON c.CardNo=s.cardno\n SET c.type='INACT',memType=0,c.Discount=0,c.ChargeLimit=0,MemDiscountLimit=0\n WHERE c.type='PC' AND s.cardno is not null"; if (!isset($custdata['ChargeLimit'])) { $custQ = str_replace('c.ChargeLimit=0,', '', $custQ); } $sql->query($custQ); $memQ = "UPDATE meminfo AS m\n LEFT JOIN suspensions AS s ON m.card_no=s.cardno\n SET ads_OK=0\n WHERE s.cardno is not null"; $sql->query($memQ);
if (!function_exists('cron_msg')) { include $FANNIE_ROOT . 'src/cron_msg.php'; } if (!isset($FANNIE_AR_DEPARTMENTS) || empty($FANNIE_AR_DEPARTMENTS)) { return; } set_time_limit(0); $ret = preg_match_all("/[0-9]+/", $FANNIE_AR_DEPARTMENTS, $depts); $depts = array_pop($depts); $dlist = "("; foreach ($depts as $d) { $dlist .= $d . ","; } $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) {
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) $upQ = "UPDATE custdata AS c LEFT JOIN\n houseVirtualCoupons AS h ON c.CardNo=h.card_no\n SET c.memCoupons=(CASE WHEN c.personNum=1 THEN 1 ELSE 0 END)\n WHERE " . $sql->now() . " >= h.start_date \n AND " . $sql->now() . " <= h.end_date"; if ($FANNIE_SERVER_DBMS == "MSSQL") { $upQ = "UPDATE custdata SET \n c.memCoupons=SUM(CASE WHEN c.personNum=1 THEN 1 ELSE 0)\n FROM custdata AS c LEFT JOIN\n houseVirtualCoupons AS h ON c.CardNo=h.card_no\n WHERE " . $sql->now() . " >= h.start_date \n AND " . $sql->now() . "<= h.end_date\n GROUP BY c.CardNo"; } $sql->query($upQ); // update blueline to match memcoupons $blueLineQ = "UPDATE custdata SET memCoupons=" . $sql->concat($sql->convert('CardNo', 'CHAR'), "' '", 'LastName', "' Coup('", $sql->convert('memCoupons', 'CHAR'), "')'", ''); $sql->query($blueLineQ); $sql->query("DROP TABLE TempVirtCoupon");
*/ 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); $chk_vital = array(); $chk_opt = array(); /* change prices */ if (strstr($FANNIE_SERVER_DBMS, "MYSQL")) { $chk_vital[] = $sql->query("UPDATE products AS p LEFT JOIN\n batchList AS l ON l.upc=p.upc LEFT JOIN\n batches AS b ON b.batchID=l.batchID\n SET p.normal_price = l.salePrice\n WHERE l.batchID=b.batchID AND l.upc=p.upc\n AND l.upc NOT LIKE 'LC%'\n AND b.discounttype = 0\n AND " . $sql->datediff($sql->now(), 'b.startDate') . " = 0"); } else { $chk_vital[] = $sql->query("UPDATE products SET\n normal_price = l.salePrice\n FROM products AS p, batches AS b, batchList AS l\n WHERE l.batchID=b.batchID AND l.upc=p.upc\n AND l.upc NOT LIKE 'LC%'\n AND b.discounttype = 0\n AND " . $sql->datediff($sql->now(), 'b.startDate') . " = 0"); } /* likecoded items differentiated for char concatenation */ if (strstr($FANNIE_SERVER_DBMS, "MYSQL")) { $chk_vital[] = $sql->query("UPDATE products AS p LEFT JOIN\n upcLike AS v ON v.upc=p.upc LEFT JOIN\n batchList AS l ON l.upc=concat('LC',convert(v.likeCode,char))\n LEFT JOIN batches AS b ON b.batchID = l.batchID\n SET p.normal_price = l.salePrice\n WHERE l.upc LIKE 'LC%'\n AND b.discounttype = 0\n AND " . $sql->datediff($sql->now(), 'b.startDate') . " = 0"); } else { $chk_vital[] = $sql->query("UPDATE products SET normal_price = l.salePrice\n FROM products AS p LEFT JOIN\n upcLike AS v ON v.upc=p.upc LEFT JOIN\n batchList AS l ON l.upc='LC'+convert(varchar,v.likecode)\n LEFT JOIN batches AS b ON b.batchID = l.batchID\n WHERE l.upc LIKE 'LC%'\n AND b.discounttype = 0\n AND " . $sql->datediff($sql->now(), 'b.startDate') . " = 0"); } $success = true; foreach ($chk_vital as $chk) { if ($chk === false) { $success = false;
Should be run after dtransaction rotation and after midnight. */ 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'; } if (!isset($FANNIE_EQUITY_DEPARTMENTS) || empty($FANNIE_EQUITY_DEPARTMENTS)) { return; } set_time_limit(0); $ret = preg_match_all("/[0-9]+/", $FANNIE_EQUITY_DEPARTMENTS, $depts); $depts = array_pop($depts); $dlist = "("; foreach ($depts as $d) { $dlist .= $d . ","; } $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 stockpurchases\n SELECT card_no,\n CASE WHEN department IN {$dlist} THEN total ELSE 0 END as stockPayments,\n tdate,trans_num,department\n FROM dlog_15 WHERE " . $sql->datediff($sql->now(), 'tdate') . " = 1\n AND department IN {$dlist}"; $sql->query($query); // rebuild summary table $sql->query('TRUNCATE TABLE equity_history_sum'); $sql->query('INSERT INTO equity_history_sum SELECT card_no, SUM(stockPurchase), MIN(tdate) FROM stockpurchases GROUP BY card_no');
* * 18Oct12 EL Keep this comment block from appearing in the Help popup. * Reformat first SQL statement. * 17Jun12 EL Fix Help to make it appropriate to this program. * Was a copy of reactivate.equity.php. */ set_time_limit(0); ini_set('memory_limit', '256M'); $sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW); $TRANS = $FANNIE_TRANS_DB . ($FANNIE_SERVER_DBMS == "MSSQL" ? 'dbo.' : '.'); $custdata = $sql->table_definition('custdata'); $meminfoQ = "UPDATE meminfo AS m LEFT JOIN\n custdata AS c ON m.card_no=c.CardNo\n LEFT JOIN {$TRANS}ar_live_balance AS s\n ON c.cardno=s.card_no LEFT JOIN suspensions AS p\n ON c.cardno=p.cardno LEFT JOIN {$TRANS}AR_EOM_Summary AS a\n ON m.card_no=a.cardno\n SET m.ads_OK=p.mailflag\n WHERE c.Type = 'INACT' and p.reasoncode IN (1)\n AND s.balance < a.twoMonthBalance"; $sql->query($meminfoQ); $custQ = "UPDATE custdata AS c LEFT JOIN {$TRANS}ar_live_balance AS s\n ON c.CardNo=s.card_no LEFT JOIN suspensions AS p\n ON c.CardNo=p.cardno LEFT JOIN {$TRANS}AR_EOM_Summary AS a\n ON c.CardNo=a.cardno\n SET c.Discount=p.discount,c.MemDiscountLimit=p.chargelimit,\n c.ChargeLimit=p.chargelimit,\n c.memType=p.memtype1,c.Type=p.memtype2,chargeOk=1\n WHERE c.Type = 'INACT' and p.reasoncode IN (1)\n AND s.balance < a.twoMonthBalance"; if (!isset($custdata['ChargeLimit'])) { $custQ = str_replace('c.ChargeLimit=p.chargelimit,', '', $custQ); } $sql->query($custQ); $histQ = "insert into suspension_history\n select 'AR paid'," . $sql->now() . ",\n 'Account reactivated',c.CardNo,0 from\n suspensions as s left join\n custdata as c on s.cardno=c.CardNo\n and c.personNum=1\n where c.Type not in ('INACT','INACT2') and s.type='I'"; $sql->query($histQ); $clearQ = "select c.CardNo from\n suspensions as s left join\n custdata as c on s.cardno=c.CardNo\n where c.Type not in ('INACT','INACT2') and s.type='I'\n AND c.personNum=1"; $clearR = $sql->query($clearQ); $cns = "("; while ($clearW = $sql->fetch_row($clearR)) { $cns .= $clearW[0] . ","; } $cns = rtrim($cns, ",") . ")"; if (strlen($cns) > 2) { $delQ = "DELETE FROM suspensions WHERE cardno IN {$cns}"; $delR = $sql->query($delQ); }
Update memcoupon table for WFC virtual coupon 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 . "."; $sql->query("TRUNCATE TABLE memcoupon"); $insQ = "INSERT INTO memcoupon\n select card_no, tdate, total,trans_num \n from {$TRANS}dlog_90_view\n where (trans_subtype = 'MA' or upc like '%MAD Coupon%') \n and ceiling(month(tdate)/3.0) = ceiling(month(" . $sql->now() . ")/3.0)\n and year(tdate) = year(" . $sql->now() . ")\n and total > -2.52 and total < 2.52\n order by card_no"; $insR = $sql->query($insQ); $resetQ = "update custdata set memCoupons=1 where Type='PC'"; $resetR = $sql->query($resetQ); $bl = "CONCAT( CONVERT(CardNo,char), ' ', LastName )"; if ($FANNIE_SERVER_DBMS == "MSSQL") { $bl = "RTRIM(CardNo) + ' ' + RTRIM(LastName)"; } $resetQ = "update custdata set memCoupons=0,blueLine={$bl} where Type<>'PC'"; $resetR = $sql->query($resetQ); $usedQ = "SELECT cardno FROM memcoupon GROUP BY cardno HAVING SUM(total) <> 0"; $usedR = $sql->query($usedQ); while ($usedW = $sql->fetch_row($usedR)) { $upR = $sql->query("UPDATE custdata SET memCoupons=0 WHERE CardNo=" . $usedW['cardno']); } $bl = "CONCAT( CONVERT(CardNo,char), ' ', LastName, ' Coup(', CONVERT(memCoupons,char), ')' )";
} 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"); }
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']); if ($cleanR === False) {
echo "Error: Can't find directory (suspensions)"; return; } include '../../config.php'; if (!class_exists('FannieAPI')) { include $FANNIE_ROOT . 'classlib2.0/FannieAPI.php'; } /* HELP This script activates members with equity paid in full */ set_time_limit(0); ini_set('memory_limit', '256M'); $sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW); $TRANS = $FANNIE_TRANS_DB . ($FANNIE_SERVER_DBMS == "MSSQL" ? 'dbo.' : '.'); $custdata = $sql->table_definition('custdata'); $dStr = date("Y-m-01 00:00:00"); $susQ = "INSERT INTO suspensions\n select m.card_no,'I',c.memType,c.Type,'',\n " . $sql->now() . ",m.ads_OK,c.Discount,\n c.ChargeLimit,4\n from meminfo as m left join\n custdata as c on c.CardNo=m.card_no and c.personNum=1\n left join {$TRANS}equity_live_balance as n on m.card_no=n.memnum\n left join memDates AS d ON m.card_no=d.card_no\n WHERE \n ( \n (DATE_ADD(d.start_date, INTERVAL 2 YEAR) < '{$dStr}'\n AND YEAR(d.start_date) < 2013) \n OR\n (DATE_ADD(d.start_date, INTERVAL 1 YEAR) < '{$dStr}'\n AND YEAR(d.start_date) >= 2013) \n )\n and c.Type='PC' and n.payments < 100\n and c.memType in (1,3)\n and NOT EXISTS(SELECT NULL FROM suspensions as s\n WHERE s.cardno=m.card_no)"; if (!isset($custdata['ChargeLimit'])) { $susQ = str_replace('c.ChargeLimit', 'c.MemDiscountLimit', $susQ); } $sql->query($susQ); $histQ = "INSERT INTO suspension_history\n select 'automatic'," . $sql->now() . ",'',\n m.card_no,4\n from meminfo as m left join\n custdata as c on c.CardNo=m.card_no and c.personNum=1\n left join {$TRANS}equity_live_balance as n on m.card_no=n.memnum\n left join memDates AS d ON m.card_no=d.card_no\n WHERE\n ( \n (DATE_ADD(d.start_date, INTERVAL 2 YEAR) < '{$dStr}'\n AND YEAR(d.start_date) < 2013) \n OR\n (DATE_ADD(d.start_date, INTERVAL 1 YEAR) < '{$dStr}'\n AND YEAR(d.start_date) >= 2013) \n )\n and c.Type='PC' and n.payments < 100\n and c.memType in (1,3)\n and NOT EXISTS(SELECT NULL FROM suspensions as s\n WHERE s.cardno=m.card_no)"; $sql->query($histQ); $custQ = "UPDATE custdata as c LEFT JOIN\n suspensions as s on c.CardNo=s.cardno\n SET c.type='INACT',memType=0,c.Discount=0,\n c.ChargeLimit=0,MemDiscountLimit=0\n where c.type='PC' and s.cardno is not null"; if (!isset($custdata['ChargeLimit'])) { $custQ = str_replace('c.ChargeLimit=0,', '', $custQ); } $sql->query($custQ); $memQ = "UPDATE meminfo as m LEFT JOIN\n suspensions as s ON m.card_no=s.cardno\n SET ads_OK=0\n where s.cardno is not null"; $sql->query($memQ);
in the file license.txt along with IT CORE; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA *********************************************************************************/ /* HELP nightly.voidhistory.php Updates voidTransHistory to include voided transactions from the previous day. Should be run after midnight & after dtransactions is rotated */ 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_TRANS_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW); $query = "INSERT INTO voidTransHistory\n SELECT datetime,description,\n " . $sql->concat($sql->convert('emp_no', 'char'), "'-'", $sql->convert('register_no', 'char'), "'-'", $sql->convert('trans_no', 'char'), '') . ",\n 0\n FROM transarchive WHERE trans_subtype='CM'\n AND " . $sql->datediff('datetime', $sql->now()) . " = -1\n AND description LIKE 'VOIDING TRANSACTION %-%-%'\n AND register_no <> 99 AND emp_no <> 9999 AND trans_status <> 'X'"; $success = $sql->query($query); if ($success) { echo cron_msg("Voids logged"); } else { echo cron_msg("Error logging voids"); }
// FROM houseVirtualCoupons AS h // LEFT JOIN TempVirtCoupon AS t ON // h.card_no=t.card_no AND h.coupID=t.coupID // WHERE ".$sql->now()." > h.end_date // OR (t.card_no IS NOT NULL AND t.coupID IS NOT NULL)"; // } $sql->query($sqlQ); // if ($delR == false) // echo cron_msg("Delete query failed<br />" . $sqlQ); // else // echo cron_msg("Successfully removed redeemed houseVirtualCoupons"; // set custdata.memcoupons equal to the number // of available coupons (in theory) $sql->query("UPDATE custdata SET memCoupons = 0"); // set memCoupons = 0 first $upQ = "UPDATE custdata AS c, houseVirtualCoupons AS h\n SET c.memCoupons=1\n WHERE c.CardNo=h.card_no \n AND " . $sql->now() . " >= h.start_date \n AND " . $sql->now() . "<= h.end_date\n AND c.memType <> 0"; if ($FANNIE_SERVER_DBMS == "MSSQL") { $upQ = "UPDATE custdata SET \n c.memCoupons=SUM(CASE WHEN c.personNum=1 THEN 1 ELSE 0)\n FROM custdata AS c LEFT JOIN\n houseVirtualCoupons AS h ON c.CardNo=h.card_no\n WHERE " . $sql->now() . " >= h.start_date \n AND " . $sql->now() . "<= h.end_date\n GROUP BY c.CardNo"; } $sql->query($upQ); // if ($upR == false) // echo cron_msg("Failed to update custdata field: memCoupons<br />"); // else // echo cron_msg("Successfully updated custdata field: memCoupons<br />"); // update blueline to match memcoupons $blueLineQ = "UPDATE custdata SET blueLine=" . $sql->concat($sql->convert('CardNo', 'CHAR'), "' '", 'LastName', "' Coup('", $sql->convert('memCoupons', 'CHAR'), "')'", '') . "WHERE memType <> 0"; $sql->query($blueLineQ); // if ($blR == false) // echo cron_msg("Failed to update custdata field: blueLine<br />"); // else // echo cron_msg("Successfully updated custdata field: blueLine<br />");
NOTE: Pretty much MUST be run on the first day of the month (and BEFORE nightly.virtualCoupons) to work as intended. And don't forget to push to lanes overnight! */ 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 . "."; // clear out houseVirtualCoupons $sql->query("TRUNCATE TABLE houseVirtualCoupons"); echo cron_msg("Truncate table houseVirtualCoupons<br />"); // re-populate houseVirtualCoupons $insQ = "INSERT INTO houseVirtualCoupons (card_no,coupID,description,start_date,end_date) \n SELECT c.CardNo, '00001', 'Owner Appreciation 10% Discount', DATE_FORMAT(NOW() ,'%Y-%m-01 00:00:00'), \n CONCAT(LAST_DAY(NOW()),' 23:59:59') FROM custdata AS c WHERE c.memType <> 0"; $insR = $sql->query($insQ); echo cron_msg("Re-populate houseVirtualCoupons"); // set custdata.memcoupons equal to the number // of available coupons (in theory) $sql->query("UPDATE custdata SET memCoupons = 0"); // set memCoupons = 0 first $upQ = "UPDATE custdata AS c, houseVirtualCoupons AS h\n SET c.memCoupons=1\n WHERE c.CardNo=h.card_no \n AND " . $sql->now() . " >= h.start_date \n AND " . $sql->now() . "<= h.end_date\n AND c.memType <> 0"; $sql->query($upQ); // update blueline to match memcoupons $blueLineQ = "UPDATE custdata SET blueLine=" . $sql->concat($sql->convert('CardNo', 'CHAR'), "' '", 'LastName', "' Coup('", $sql->convert('memCoupons', 'CHAR'), "')'", '') . "WHERE memType <> 0"; $sql->query($blueLineQ); echo cron_msg("Updated values in core_op.custdata");
while ($datesW = $sql->fetch_row($datesR)) { $dates[] = sprintf('%d-%02d-%02d', $datesW['year'], $datesW['month'], $datesW['day']); } $UPDATED_DLOG_SCHEMA = false; $table_def = $sql->table_definition('dlog'); if (isset($table_def['description'])) { // most likely $UPDATED_DLOG_SCHEMA = true; } /* Load dtransactions into the archive, trim to 90 days */ $chkP = $sql->prepare("INSERT INTO transarchive SELECT * FROM dtransactions WHERE " . $sql->datediff('datetime', '?') . '= 0'); $chk1 = false; foreach ($dates as $date) { $chk1 = $sql->execute($chkP, array($date)); } $chk2 = $sql->query("DELETE FROM transarchive WHERE " . $sql->datediff($sql->now(), 'datetime') . " > 92"); if ($chk1 === false) { echo cron_msg("Error loading data into transarchive"); } elseif ($chk2 === false) { echo cron_msg("Error trimming transarchive"); } else { echo cron_msg("Data rotated into transarchive"); } /* reload all the small snapshot */ $chk1 = $sql->query("TRUNCATE TABLE dlog_15"); $chk2 = $sql->query("INSERT INTO dlog_15 SELECT * FROM dlog_90_view WHERE " . $sql->datediff($sql->now(), 'tdate') . " <= 15"); if ($chk1 === false || $chk2 === false) { echo cron_msg("Error reloading dlog_15"); } else { echo cron_msg("Success reloading dlog_15"); }
$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");
if (file_exists($cachepath)) { $dh = opendir($cachepath); while (($file = readdir($dh)) !== false) { if ($file == "." || $file == "..") { continue; } if (!is_file($cachepath . $file)) { continue; } unlink($cachepath . $file); } closedir($dh); } $sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_TRANS_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW); // auto-close called/waiting after 30 days $subquery = "select p.order_id from PendingSpecialOrder as p\n left join SpecialOrders as s\n on p.order_id=s.specialOrderID\n where p.trans_id=0 and s.statusFlag=1\n and " . $sql->datediff($sql->now(), 'datetime') . " > 30"; $cwIDs = "("; $r = $sql->query($subquery); while ($w = $sql->fetch_row($r)) { $cwIDs .= $w['order_id'] . ","; } $cwIDs = rtrim($cwIDs, ",") . ")"; if (strlen($cwIDs) > 2) { // transfer to completed orders $copyQ = "INSERT INTO CompleteSpecialOrder\n SELECT p.* FROM PendingSpecialOrder AS p\n WHERE p.order_id IN {$cwIDs}"; // make note in history table $historyQ = "INSERT INTO SpecialOrderHistory\n (order_id, entry_date, entry_type, entry_value)\n SELECT p.order_id,\n " . $sql->now() . ",\n 'AUTOCLOSE',\n 'Call/Waiting 30'\n FROM PendingSpecialOrder AS p\n WHERE p.order_id IN {$cwIDs}\n GROUP BY p.order_id"; $sql->query($historyQ); // clear from pending $sql->query($copyQ); $delQ = "DELETE FROM PendingSpecialOrder\n WHERE order_id IN {$cwIDs}";