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);
After dtransactions has been copied to these two locations, it is truncated. This script is meant to be run nightly so that dtransactions always holds just the current day's data. */ 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); $cols = $sql->table_definition('dtransactions'); if (isset($cols['date_id'])) { $sql->query("UPDATE dtransactions SET date_id=DATE_FORMAT(datetime,'%Y%m%d')"); } /* Find date(s) in dtransactions */ $datesR = $sql->query('SELECT YEAR(datetime) AS year, MONTH(datetime) as month, DAY(datetime) as day FROM dtransactions GROUP BY YEAR(datetime), MONTH(datetime), DAY(datetime) ORDER BY YEAR(datetime), MONTH(datetime), DAY(datetime)'); $dates = array(); 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'])) {