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}";
