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