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'])) {
