}
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)
$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'), "')'", '');
$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) {
echo cron_msg("Failed: {$query}");
}
$rslt = $sql->query($AR_EOM_Summary_Q);
if ($rslt === False) {
echo cron_msg("Failed: {$AR_EOM_Summary_Q}");
}
}
}
echo cron_msg("Done.");
CORE-POS is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
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
*********************************************************************************/
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';
}
$dbc = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$dbc->query("TRUNCATE TABLE YTD_Patronage_MiddleStep");
$ts = mktime(0, 0, 0, date("n"), 1, date("Y") - 1);
for ($i = 0; $i < 12; $i++) {
$start = date("Y-m-d", $ts);
$end = date("Y-m-t", $ts);
$dlog = DTransactionsModel::selectDtrans($start, $end);
$ts = mktime(0, 0, 0, date("n", $ts) + 1, 1, date("Y", $ts));
$query = "INSERT INTO YTD_Patronage_MiddleStep \n select d.card_no,MONTH(d.datetime) as month_no,\n total,\n YEAR(d.datetime) AS year_no,\n DAY(d.datetime) AS day_no,\n " . $dbc->concat($dbc->convert('emp_no', 'char'), "'-'", $dbc->convert('register_no', 'char'), "'-'", $dbc->convert('trans_no', 'char'), '') . " as trans_num\n from " . $dlog . " as d\n WHERE datetime BETWEEN '{$start} 00:00:00' AND '{$end} 23:59:59'\n AND d.trans_type = 'T' AND total <> 0\n AND emp_no <> 9999 and register_no <> 99 AND trans_status NOT IN ('Z','X')";
$dbc->query($query);
}
$dbc->query("TRUNCATE TABLE YTD_Patronage_Speedup");
$query = "INSERT INTO YTD_Patronage_Speedup\n SELECT card_no,month_no,SUM(total) as total,year_no\n FROM YTD_Patronage_MiddleStep AS d\n LEFT JOIN custdata as c on c.CardNo=d.card_no and c.personNum=1 \n LEFT JOIN suspensions as s on s.cardno = d.card_no \n WHERE c.memType=1 or s.memtype1=1 \n GROUP BY d.card_no,\n year_no, month_no, day_no, trans_num";
$dbc->query($query);
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");
}
// 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 />");
//$sql->query("DROP TABLE TempVirtCoupon");
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 = $sql->query("TRUNCATE TABLE batchMergeTable");
if ($chk === False) {
echo cron_msg("Could not truncate batchMergeTable");
}
$chk = $sql->query("INSERT INTO batchMergeTable\n SELECT b.startDate,b.endDate,p.upc,p.description,b.batchID\n FROM batches AS b LEFT JOIN batchList AS l\n ON b.batchID=l.batchID \n " . DTrans::joinProducts('l', 'p', 'INNER'));
if ($chk === False) {
echo cron_msg("Could not load batch reporting data for UPCs");
}
$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");
}
}
