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');
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 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;
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) {
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");
}
// make a new partition if it's a new month
if (date('j') == 1 && !$added_partition) {
$p = "p" . date("Ym");
$boundary = date("Y-m-d", mktime(0, 0, 0, date("n") + 1, 1, date("Y")));
// new partition named pYYYYMM
// ends on first day of next month
$newQ = sprintf("ALTER TABLE bigArchive ADD PARTITION \n (PARTITION %s \n VALUES LESS THAN (TO_DAYS('%s'))\n )", $p, $boundary);
$newR = $sql->query($newQ);
if ($newR === false) {
echo cron_msg("Error creating new partition {$p}");
} else {
$added_partition = true;
}
}
// now just copy rows into the partitioned table
$loadQ = "INSERT INTO bigArchive SELECT * FROM {$FANNIE_TRANS_DB}.dtransactions\n WHERE " . $sql->datediff('datetime', "'{$date}'") . "= 0";
$loadR = $sql->query($loadQ);
} else {
if (!$sql->table_exists($table)) {
// 20Nov12 EL Add "TABLE".
$query = "CREATE TABLE {$table} LIKE {$FANNIE_TRANS_DB}.dtransactions";
if ($FANNIE_SERVER_DBMS == 'MSSQL') {
$query = "SELECT * INTO {$table} FROM {$FANNIE_TRANS_DB}.dbo.dtransactions\n WHERE " . $sql->datediff('datetime', "'{$date}'") . "= 0";
}
$chk1 = $sql->query($query, $FANNIE_ARCHIVE_DB);
$chk2 = true;
if (strstr($FANNIE_SERVER_DBMS, "MYSQL")) {
// mysql doesn't create & populate in one step
$chk2 = $sql->query("INSERT INTO {$table} SELECT * FROM {$FANNIE_TRANS_DB}.dtransactions\n WHERE " . $sql->datediff('datetime', "'{$date}'") . "= 0");
}
if ($chk1 === false || $chk2 === false) {
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}";
