function allLanes($upc)
{
global $FANNIE_LANES, $FANNIE_ROOT;
if (!class_exists("SQLManager")) {
require_once $FANNIE_ROOT . "src/SQLManager.php";
}
$ret = "";
$queryItem = '';
if (is_numeric($upc)) {
$upc = str_pad($upc, 13, 0, STR_PAD_LEFT);
$queryItem = "SELECT * FROM products WHERE upc = '{$upc}'";
} else {
$queryItem = "SELECT * FROM products WHERE description LIKE '%{$upc}%' ORDER BY description";
}
for ($i = 0; $i < count($FANNIE_LANES); $i++) {
$f = $FANNIE_LANES[$i];
$sql = new SQLManager($f['host'], $f['type'], $f['op'], $f['user'], $f['pw']);
if ($sql === False) {
$ret .= "Can't connect to lane: " . ($i + 1) . "<br />";
continue;
}
$resultItem = $sql->query($queryItem);
$num = $sql->num_rows($resultItem);
if ($num == 0) {
$ret .= "Item <span style=\"color:red;\">{$upc}</span> not found on Lane " . ($i + 1) . "<br />";
} else {
if ($num > 1) {
$ret .= "Item <span style=\"color:red;\">{$upc}</span> found multiple times on Lane " . ($i + 1) . "<br />";
while ($rowItem = $sql->fetch_array($resultItem)) {
$ret .= "{$rowItem['upc']} {$rowItem['description']}<br />";
}
} else {
$rowItem = $sql->fetch_array($resultItem);
$ret .= "Item <span style=\"color:red;\">{$upc}</span> on Lane " . ($i + 1) . "<br />";
$ret .= "Price: {$rowItem['normal_price']}";
if ($rowItem['special_price'] != 0) {
$ret .= " <span style=\"color:green;\">ON SALE: {$rowItem['special_price']}</span>";
}
$ret .= "<br />";
}
}
if ($i < count($FANNIE_LANES) - 1) {
$ret .= "<hr />";
}
}
return $ret;
}
*/
/* why is this file such a mess?
SQL for UPDATE against multiple tables is different
for MSSQL and MySQL. There's not a particularly clean
way around it that I can think of, hence alternates
for all queries.
*/
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);
$TRANS = $FANNIE_TRANS_DB . ($FANNIE_SERVER_DBMS == "MSSQL" ? 'dbo.' : '.');
$miQ = "UPDATE meminfo AS m \n INNER JOIN {$TRANS}equity_live_balance s\n ON m.card_no=s.memnum\n INNER JOIN custdata AS c ON c.CardNo=s.memnum\n LEFT JOIN memDates AS d ON d.card_no=s.memnum\n SET m.ads_OK=1\n WHERE (d.start_date IS null OR d.start_date = '0000-00-00 00:00:00')\n AND s.payments > 0\n AND c.Type='PC'";
if ($FANNIE_SERVER_DBMS == 'MSSQL') {
$miQ = "UPDATE meminfo SET ads_OK=1\n FROM {$TRANS}equity_live_balance s\n left join meminfo m ON m.card_no=s.memnum\n left join custdata as c on c.cardno=s.memnum\n left join memDates as d on d.card_no=s.memnum\n where d.start_date is null and s.payments > 0\n and c.type='PC'";
}
$sql->query($miQ);
$mdQ = "UPDATE memDates AS d\n INNER JOIN {$TRANS}equity_live_balance AS s\n ON d.card_no=s.memnum\n INNER JOIN custdata AS c ON c.CardNo=s.memnum\n SET d.start_date=s.startdate,\n d.end_date=CASE WHEN s.payments >= 100 \n THEN '0000-00-00 00:00:00' \n ELSE \n CASE WHEN s.startdate < '2012-12-31 23:59:59'\n THEN DATE_ADD(s.startdate,INTERVAL 2 YEAR) \n ELSE DATE_ADD(s.startdate,INTERVAL 1 YEAR) END\n END\n WHERE (d.start_date IS null OR d.start_date = '0000-00-00 00:00:00'\n OR (s.payments >= 100 AND d.end_date <> '0000-00-00 00:00:00')\n )\n AND s.payments > 0\n AND c.Type='PC'";
if ($FANNIE_SERVER_DBMS == 'MSSQL') {
$mdQ = "UPDATE memDates SET start_date=s.startdate,\n end_date=CASE WHEN s.payments >=100 \n THEN '1900-01-01 00:00:00'\n ELSE dateadd(yy,1,s.startdate) END\n FROM {$TRANS}equity_live_balance s\n left join custdata as c on c.cardno=s.memnum\n left join memDates as d on d.card_no=s.memnum\n where d.start_date is null and s.payments > 0\n and c.type='PC'";
}
$sql->query($mdQ);
$sql->query("DELETE FROM custReceiptMessage WHERE msg_text LIKE 'EQUITY OWED% == %'");
$msgQ = "INSERT custReceiptMessage\n SELECT s.memnum,CONCAT('EQUITY OWED \$',100-s.payments,' == '\n ,'DUE DATE ',MONTH(d.end_date),'/',DAY(d.end_date),'/',YEAR(d.end_date)),\n 'WfcEquityMessage'\n FROM {$TRANS}equity_live_balance AS s\n INNER JOIN memDates as d ON s.memnum=d.card_no\n WHERE s.payments < 100";
$msgR = $sql->query($msgQ);
// ************************************
// Discount value is the percent * 100, e.g. 10 = 10%
$discount_value = 10;
// NOTE: Simply uncomment your desired senior discount day.
// $discount_day = "Sunday";
// $discount_day = "Monday";
// $discount_day = "Tuesday";
$discount_day = "Wednesday";
// $discount_day = "Thursday";
// $discount_day = "Friday";
// $discount_day = "Saturday";
// ************************************
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);
$today = date('l');
$dday = date_create($discount_day);
date_add($dday, date_interval_create_from_date_string('1 days'));
$discount_day_after = date_format($dday, 'l');
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$toggle = $today == $discount_day ? "+" : "-";
if ($today == $discount_day || $today == $discount_day_after) {
$sql->query("UPDATE custdata SET discount = (discount {$toggle} {$discount_value}) WHERE SSI = 1");
} else {
echo cron_msg("nightly.seniordiscount.php: Discount active on " . $discount_day . ".<br /> No discounts to apply");
}
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) {
echo cron_msg("Could not clean {$table} on lane: " . $ln['host']);
return;
}
include '../../config.php';
if (!class_exists('FannieAPI')) {
include $FANNIE_ROOT . 'classlib2.0/FannieAPI.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);
$upc = null;
$prevPrice = null;
$q = "select u.upc,u.modified,price,user from prodUpdate\nas u inner join products as p on p.upc=u.upc\norder by u.upc,u.modified";
if ($FANNIE_SERVER_DBMS == "MSSQL") {
$q = str_replace("user", "[user]", $q);
}
$r = $sql->query($q);
while ($w = $sql->fetch_row($r)) {
if ($upc === null || $upc != $w['upc']) {
// next item, get previous
// date and price from compressed
// history if available
$upc = $w['upc'];
$prevPrice = null;
$prevDate = null;
$chkR = $sql->query("SELECT modified,price FROM\n prodPriceHistory WHERE upc='{$upc}'\n ORDER BY modified DESC");
if ($sql->num_rows($chkR) > 0) {
$chk = $sql->fetch_row($chkR);
$prevDate = $chk['modified'];
$prevPrice = $chk['price'];
}
}
return;
}
include '../../config.php';
if (!class_exists('FannieAPI')) {
include $FANNIE_ROOT . 'classlib2.0/FannieAPI.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);
$upc = null;
$prevDept = null;
$q = "select u.upc,u.modified,dept,user from prodUpdate\nas u inner join products as p on p.upc=u.upc\norder by u.upc,u.modified";
if ($FANNIE_SERVER_DBMS == "MSSQL") {
$q = str_replace("user", "[user]", $q);
}
$r = $sql->query($q);
while ($w = $sql->fetch_row($r)) {
if ($upc === null || $upc != $w['upc']) {
// next item, get previous
// date and price from compressed
// history if available
$upc = $w['upc'];
$prevDept = null;
$prevDate = null;
$chkR = $sql->query("SELECT modified,dept_ID FROM\n prodDepartmentHistory WHERE upc='{$upc}'\n ORDER BY modified DESC");
if ($sql->num_rows($chkR) > 0) {
$chk = $sql->fetch_row($chkR);
$prevDate = $chk['modified'];
$prevDept = $chk['dept_ID'];
}
}
*/
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';
}
/* PURPOSE:
Crunch the previous month's total sales &
deliveries into a single archive record
*/
set_time_limit(0);
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$deliveryQ = "INSERT INTO InvDeliveryArchive\n SELECT max(inv_date),upc,vendor_id,sum(quantity),sum(price)\n FROM InvDeliveryLM \n GROUP BY upc,vendor_id";
$chk = $sql->query($deliveryQ);
if ($chk === false) {
echo cron_msg("Error archiving last month's inventory data");
}
$chk1 = $sql->query("TRUNCATE TABLE InvDeliveryLM");
$lmQ = "INSERT INTO InvDeliveryLM SELECT * FROM InvDelivery WHERE " . $sql->monthdiff($sql->now(), 'inv_date') . " = 1";
$chk2 = $sql->query($lmQ);
if ($chk1 === false || $chk2 === false) {
echo cron_msg("Error setting up last month's inventory data");
}
$clearQ = "DELETE FROM InvDelivery WHERE " . $sql->monthdiff($sql->now(), 'inv_date') . " = 1";
$chk = $sql->query($clearQ);
if ($chk === false) {
echo cron_msg("Error clearing inventory data");
}
$salesQ = "INSERT INTO InvSalesArchive\n select max(datetime),upc,sum(quantity),sum(total)\n FROM transarchive WHERE " . $sql->monthdiff($sql->now(), 'datetime') . " = 1\n AND scale=0 AND trans_status NOT IN ('X','R') \n AND trans_type = 'I' AND trans_subtype <> '0'\n AND register_no <> 99 AND emp_no <> 9999\n GROUP BY upc";
parseitem($action, $s_plu, trim($scale_array["itemdesc"], "'"), $scale_array['tare'], $scale_array['shelflife'], $scale_array['price'], $scale_array['bycount'], $s_type, 0.0, trim($scale_array['text'], "'"), $scale_array['label'], $scale_array['graphics'] == 1 ? 121 : 0);
}
/* 10. Delete and re-add to product-related tables on the lanes. */
/* push updates to the lanes */
include 'laneUpdates_WEFC_Toronto.php';
updateAllLanes($upc, array("products", "productUser"));
// $dbc is looking at lane db now, so change it back.
// What is the DB function to do this? FannieDB::get()?
// We're not in the right environment for that.
$dbc = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
/* 'i9el. Update likecodes */
/* update the item's likecode if specified
also update other items in the likecode
if the appropriate box isn't checked */
if (isset($_REQUEST['likeCode']) && $_REQUEST['likeCode'] != -1) {
$dbc->query("DELETE FROM upcLike WHERE upc='{$upc}'");
$lcQ = "INSERT INTO upcLike (upc,likeCode) VALUES ('{$upc}',{$_REQUEST['likeCode']})";
$dbc->query($lcQ);
if (!isset($_REQUEST['update'])) {
$upcsQ = "SELECT upc FROM upcLike WHERE likeCode={$_REQUEST['likeCode']} AND upc <> '{$upc}'";
$upcsR = $dbc->query($upcsQ);
unset($up_array['description']);
while ($upcsW = $dbc->fetch_row($upcsR)) {
$dbc->smart_update('products', $up_array, "upc='{$upcsW['0']}' AND store_id={$FANNIE_STORE_ID}");
updateProductAllLanes($upcsW[0]);
}
}
} elseif (isset($_REQUEST['likeCode']) && $_REQUEST['likeCode'] == -1) {
$dbc->query("DELETE FROM upcLike WHERE upc='{$upc}'");
}
/* 11. Display the post-update values and an input for the next edit. */
// NOTE: Simply uncomment your desired supplement discount day.
// $discount_day = "Sunday";
$discount_day = "Monday";
// $discount_day = "Tuesday";
// $discount_day = "Wednesday";
// $discount_day = "Thursday";
// $discount_day = "Friday";
// $discount_day = "Saturday";
// ************************************
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);
$today = date('l');
$dday = date_create($discount_day);
date_add($dday, date_interval_create_from_date_string('1 days'));
$discount_day_after = date_format($dday, 'l');
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
if ($today == $discount_day) {
$sql->query("INSERT INTO autoCoupons VALUES(999,'Supplement Discount')");
echo cron_msg("It's {$discount_day}. Supplement discount applied.");
} elseif ($today == $discount_day_after) {
$sql->query("DELETE FROM autoCoupons WHERE coupID = 999");
echo cron_msg("It's {$discount_day_after}. Supplement discount removed.");
} else {
echo cron_msg("No discounts to apply.");
}
function removeAllLanes($upc, $table_name)
{
$FANNIE_OP_DB = FannieConfig::config('OP_DB');
$FANNIE_LANES = FannieConfig::config('LANES');
for ($i = 0; $i < count($FANNIE_LANES); $i++) {
$tmp = new SQLManager($FANNIE_LANES[$i]['host'], $FANNIE_LANES[$i]['type'], $FANNIE_LANES[$i]['op'], $FANNIE_LANES[$i]['user'], $FANNIE_LANES[$i]['pw']);
if ($tmp->table_exists("{$table_name}")) {
$delQ = "DELETE FROM {$table_name} WHERE upc='{$upc}'";
$delR = $tmp->query($delQ, $FANNIE_LANES[$i]['op']);
}
}
}
echo cron_msg("Failed: {$fetchQ}");
return;
}
while ($fetchW = $sql->fetch_row($fetchR)) {
$data[$fetchW['CardNo']] = $fetchW['balance'];
}
$errors = False;
// connect to each lane and update balances
foreach ($FANNIE_LANES as $lane) {
$db = new SQLManager($lane['host'], $lane['type'], $lane['op'], $lane['user'], $lane['pw']);
if ($db === False) {
echo cron_msg("Can't connect to lane: " . $lane['host']);
$errors = True;
continue;
}
foreach ($data as $cn => $bal) {
$upQ = sprintf("UPDATE custdata SET Balance=%.2f WHERE CardNo=%d", $bal, $cn);
$rslt = $db->query($upQ);
if ($rslt === False) {
echo cron_msg("Update of member: {$cn} on lane: {$lane['host']} failed.");
$errors = True;
}
}
}
if ($errors) {
echo cron_msg("There was an error pushing balances to the lanes.");
flush();
} else {
//echo cron_msg("All OK.");
$a = 0;
}
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");
}
public function run()
{
global $FANNIE_LANES;
global $FANNIE_PLUGIN_LIST;
global $FANNIE_PLUGIN_SETTINGS;
if (!FanniePlugin::isEnabled($this->pluginName)) {
echo $this->cronMsg("Plugin '{$this->pluginName}' is not enabled.");
return False;
}
if (!array_key_exists("{$this->pluginName}Database", $FANNIE_PLUGIN_SETTINGS) || empty($FANNIE_PLUGIN_SETTINGS["{$this->pluginName}Database"])) {
echo $this->cronMsg("Setting: '{$this->pluginName}Database' is not set.");
return False;
}
$server_db = $FANNIE_PLUGIN_SETTINGS["{$this->pluginName}Database"];
$dbc = FannieDB::get($server_db);
if ($dbc === False) {
echo $this->cronMsg("Unable to connect to {$server_db}.");
return False;
}
// get balances that changed today
$balanceData = array();
$fetchQ = "SELECT programID, cardNo, balance\n FROM CCredMemCreditBalance\n WHERE mark=1";
//orig: $fetchQ = "SELECT CardNo, balance FROM memChargeBalance WHERE mark=1";
$fetchR = $dbc->query($fetchQ);
if ($fetchR === False) {
echo $this->cronMsg("Fatal: {$fetchQ}");
flush();
return;
}
// Make a list of updates.
while ($fetchW = $dbc->fetch_row($fetchR)) {
$key = $fetchW['programID'] . '|' . $fetchW['cardNo'];
// The order of elements as needed in the UPDATE statement.
$balanceData["{$key}"] = array($fetchW['balance'], $fetchW['programID'], $fetchW['cardNo']);
}
/* Debug
echo $this->cronMsg("Balance updates to do: " . count($balanceData));
*/
/* Get CCredMemberships that have changed today.
* Does not handle Members added today.
*/
$memberData = array();
$memberQ = "SELECT programID, cardNo, creditLimit, maxCreditBalance,\n creditOK, inputOK, transferOK, modified\n FROM CCredMemberships\n WHERE date(modified) = date(" . $dbc->now() . ")";
$memberR = $dbc->query($memberQ);
if ($memberR === False) {
echo $this->cronMsg("Failed: {$memberQ}");
$errors = True;
} else {
while ($memberW = $dbc->fetch_row($memberR)) {
$key = $memberW['programID'] . '|' . $memberW['cardNo'];
// The order of elements as needed in the UPDATE statement.
$memberData["{$key}"] = array($memberW['creditLimit'], $memberW['maxCreditBalance'], $memberW['creditOK'], $memberW['inputOK'], $memberW['transferOK'], $memberW['modified'], $memberW['programID'], $memberW['cardNo']);
}
}
/* Debug
echo $this->cronMsg("Member updates to do: " . count($memberData));
*/
/* Get CCredPrograms that have changed today.
* Does not handle Programs added today.
*/
$programData = array();
$programQ = "SELECT programID, active, startDate, endDate,\n creditOK, inputOK, transferOK, maxCreditBalance,\n modified\n FROM CCredPrograms\n WHERE date(modified) = date(" . $dbc->now() . ")";
$programR = $dbc->query($programQ);
if ($programR === False) {
echo $this->cronMsg("Failed: {$programQ}");
$errors = True;
} else {
while ($programW = $dbc->fetch_row($programR)) {
$key = $programW['programID'];
// The order of elements as needed in the UPDATE statement.
$programData["{$key}"] = array($programW['active'], $programW['startDate'], $programW['endDate'], $programW['creditOK'], $programW['inputOK'], $programW['transferOK'], $programW['maxCreditBalance'], $programW['modified'], $programW['programID']);
}
}
/* Debug
echo $this->cronMsg("Program updates to do: " . count($programData));
*/
$errors = False;
// connect to each lane and update balances
foreach ($FANNIE_LANES as $lane) {
$dbL = new SQLManager($lane['host'], $lane['type'], $lane['op'], $lane['user'], $lane['pw']);
if ($dbL === False) {
echo $this->cronMsg("Can't connect to lane: " . $lane['host'] . " db: {$lane['op']} .");
$errors = True;
continue;
}
/* Find the name of the CoopCred db on the lane.
* Why is PluginList in opdata.lane_config but PluginSettings isn't?
* opdata.parameters has PluginList, CoopCredLaneDatabase
*/
$coopCredEnabled = 0;
$laneDB = "";
$laneQ = "SELECT * FROM parameters\n WHERE param_key IN ('PluginList', 'CoopCredLaneDatabase')\n ORDER BY param_key, store_id, lane_id";
$laneR = $dbL->query($laneQ);
if ($laneR === False) {
echo $this->cronMsg("Failed query on: " . $lane['host'] . " query: {$query}");
$errors = True;
continue;
}
/* Local values will override global. */
while ($laneP = $dbL->fetch_row($laneR)) {
if ($laneP['param_key'] == 'PluginList') {
$paramList = explode(',', $laneP['param_value']);
if (in_array($this->pluginName, $paramList)) {
$coopCredEnabled = 1;
}
}
if ($laneP['param_key'] == 'CoopCredLaneDatabase') {
$laneDB = $laneP['param_value'];
}
}
if (!$coopCredEnabled) {
echo $this->cronMsg("{$this->pluginName} is not enabled on: " . $lane['host']);
continue;
}
if ($laneDB == '') {
echo $this->cronMsg("No CoopCredDatabase named on: " . $lane['host']);
continue;
}
// Change db on connection to the ccred db.
$ccDB = $dbL->addConnection($lane['host'], $lane['type'], $laneDB, $lane['user'], $lane['pw']);
if ($ccDB === False) {
echo $this->cronMsg("Can't add connection to {$laneDB} on: " . $lane['host']);
$errors = True;
continue;
}
$dbL->default_db = $laneDB;
foreach ($balanceData as $dt) {
$upQ = "UPDATE CCredMemberships\n SET creditBalance=?,\n modified=" . $dbc->now() . ",\n modifiedBy=9998\n WHERE programID=? AND cardNo=?";
$upS = $dbL->prepare($upQ);
$upR = $dbL->execute($upS, $dt);
if ($upR === False) {
echo $this->cronMsg("Balance update failed: member: {$dt[2]} " . "in program {$dt[1]} on lane: {$lane['host']}");
$errors = True;
}
/* Debug
else {
echo $this->cronMsg("Balance update OK: member: {$dt[2]} in program {$dt[1]} on lane: {$lane['host']}");
}
*/
}
foreach ($memberData as $dt) {
$upQ = "UPDATE CCredMemberships\n SET creditLimit=?,\n maxCreditBalance=?,\n creditOK=?,\n inputOK=?,\n transferOK=?,\n modified=?,\n modifiedBy=9999\n WHERE programID=? AND cardNo=?";
$upS = $dbL->prepare($upQ);
$upR = $dbL->execute($upS, $dt);
if ($upR === False) {
echo $this->cronMsg("Member update failed: member: {$dt[7]} in program {$dt[6]} on lane: {$lane['host']}");
$errors = True;
}
/* Debug
else {
echo $this->cronMsg("Member update OK: member: {$dt[7]} ".
"in program {$dt[6]} on lane: {$lane['host']}");
}
*/
}
foreach ($programData as $dt) {
$upQ = "UPDATE CCredPrograms\n SET active=?,\n startDate=?,\n endDate=?,\n creditOK=?,\n inputOK=?,\n transferOK=?,\n maxCreditBalance=?,\n modified=?,\n modifiedBy=9999\n WHERE programID=?";
$upS = $dbL->prepare($upQ);
$upR = $dbL->execute($upS, $dt);
if ($upR === False) {
echo $this->cronMsg("Program update failed: program {$dt[8]} " . "on lane: {$lane['host']}");
$errors = True;
}
/* Debug
else {
echo $this->cronMsg("Program update OK: program {$dt[8]} ".
"on lane: {$lane['host']}");
}
*/
}
// each lane
}
if ($errors) {
echo $this->cronMsg("There was an error pushing balances to the lanes.");
flush();
} else {
/* Debug
echo $this->cronMsg("All OK.");
*/
$noop = 0;
}
// /run
}
*/
/* --COMMENTS - - - - - - - - - - - - - - - - - - - - - - - - - -
*
* 18Oct12 EL Keep this comment block from appearing in the Help popup.
* Reformat first SQL statement.
* 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');
$meminfoQ = "UPDATE meminfo AS m LEFT JOIN\n custdata AS c ON m.card_no=c.CardNo\n LEFT JOIN {$TRANS}ar_live_balance AS s\n ON c.cardno=s.card_no LEFT JOIN suspensions AS p\n ON c.cardno=p.cardno LEFT JOIN {$TRANS}AR_EOM_Summary AS a\n ON m.card_no=a.cardno\n SET m.ads_OK=p.mailflag\n WHERE c.Type = 'INACT' and p.reasoncode IN (1)\n AND s.balance < a.twoMonthBalance";
$sql->query($meminfoQ);
$custQ = "UPDATE custdata AS c LEFT JOIN {$TRANS}ar_live_balance AS s\n ON c.CardNo=s.card_no LEFT JOIN suspensions AS p\n ON c.CardNo=p.cardno LEFT JOIN {$TRANS}AR_EOM_Summary AS a\n ON c.CardNo=a.cardno\n SET c.Discount=p.discount,c.MemDiscountLimit=p.chargelimit,\n c.ChargeLimit=p.chargelimit,\n c.memType=p.memtype1,c.Type=p.memtype2,chargeOk=1\n WHERE c.Type = 'INACT' and p.reasoncode IN (1)\n AND s.balance < a.twoMonthBalance";
if (!isset($custdata['ChargeLimit'])) {
$custQ = str_replace('c.ChargeLimit=p.chargelimit,', '', $custQ);
}
$sql->query($custQ);
$histQ = "insert into suspension_history\n select 'AR paid'," . $sql->now() . ",\n 'Account reactivated',c.CardNo,0 from\n suspensions as s left join\n custdata as c on s.cardno=c.CardNo\n and c.personNum=1\n where c.Type not in ('INACT','INACT2') and s.type='I'";
$sql->query($histQ);
$clearQ = "select c.CardNo from\n suspensions as s left join\n custdata as c on s.cardno=c.CardNo\n where c.Type not in ('INACT','INACT2') and s.type='I'\n AND c.personNum=1";
$clearR = $sql->query($clearQ);
$cns = "(";
while ($clearW = $sql->fetch_row($clearR)) {
$cns .= $clearW[0] . ",";
}
$cns = rtrim($cns, ",") . ")";
if (strlen($cns) > 2) {
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);
include 'hobartcsv/parse.php';
parseitem($action, $s_plu, trim($scale_array["itemdesc"], "'"), $scale_array['tare'], $scale_array['shelflife'], $scale_array['price'], $scale_array['bycount'], $s_type, 0.0, trim($scale_array['text'], "'"), $scale_array['label'], $scale_array['graphics'] == 1 ? 121 : 0);
}
/* 8. Delete and re-add to product-related tables on the lanes. */
/* push updates to the lanes */
include 'laneUpdates_WEFC_Toronto.php';
updateAllLanes($upc, array("products", "productUser"));
/* 9. Display the post-update values and an input for the next edit. */
/* Display some of the post-update values and an input for the next edit.
* The page contains form elements but there is no submit for the them.
* The record-select input is also displayed in a proper form with a submit.
*/
// $dbc may be looking at lane db now, so be sure it is looking at Fannie.
$dbc = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$deptQ = "SELECT dept_no, dept_name FROM departments ORDER BY dept_no";
$deptR = $dbc->query($deptQ);
$row = $dbc->fetch_array($deptR);
$firstDeptNo = $row['dept_no'];
$firstDeptName = $row['dept_name'];
$prodQ = "SELECT * FROM products WHERE upc = " . $upc;
$prodR = $dbc->query($prodQ);
$row = $dbc->fetch_array($prodR);
echo "<table border=0>";
echo "<tr><td align=right><b>UPC</b></td><td><font color='red'>" . $upc . "</font><input type=hidden value='" . $upc . "' name=upc></td>";
echo "</tr><tr><td><b>Description</b></td><td>" . $row['description'] . "</td>";
echo "<td><b>Price</b></td><td>\$ " . $row['normal_price'] . "</td></tr></table>";
echo "<table border=0><tr>";
echo "<th>Dept<th>Sub-Dept<th>FS<th>Scale<th>QtyFrc<th>NoDisc<th>inUse<th>deposit</b>";
echo "</tr>";
echo "<tr>";
$dept = $row["department"];
homeless.specialorder.php
Check for SOs w/o a department
and spam out email until someone fixes it
*/
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);
$OP = $FANNIE_OP_DB . ($FANNIE_SERVER_DBMS == "MSSQL" ? 'dbo.' : '.');
$q = "\nselect s.order_id,description,datetime,\ncase when c.lastName ='' then b.LastName else c.lastName END as name\nfrom PendingSpecialOrder\nas s left join SpecialOrders as c on s.order_id=c.specialOrderID\nleft join {$OP}custdata as b on s.card_no=b.CardNo and s.voided=b.personNum\nwhere s.order_id in (\nselect p.order_id from PendingSpecialOrder as p\nleft join SpecialOrders as n\non p.order_id=n.specialOrderID\nwhere notes LIKE ''\ngroup by p.order_id\nhaving max(department)=0 and max(noteSuperID)=0\nand max(trans_id) > 0\n)\nand trans_id > 0\norder by datetime\n";
$r = $sql->query($q);
if ($sql->num_rows($r) > 0) {
$msg_body = "Homeless orders detected!\n\n";
while ($w = $sql->fetch_row($r)) {
$msg_body .= $w['datetime'] . ' - ' . (empty($w['name']) ? '(no name)' : $w['name']) . ' - ' . $w['description'] . "\n";
$msg_body .= "http://key" . $FANNIE_URL . "ordering/view.php?orderID=" . $w['order_id'] . "\n\n";
}
$msg_body .= "These messages will be sent daily until orders get departments\n";
$msg_body .= "or orders are closed\n";
$to = "buyers, michael";
$subject = "Incomplete SO(s)";
mail($to, $subject, $msg_body);
}
Track virtual coupon usage
Adjust custdata settings to match
*/
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);
$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";
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) {
echo cron_msg("Failed: {$query}");
@deprecated. Use Table Snapshot Task
Copies table contents to a backup table
Currently applies to products & custdata.
*/
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);
// drop and recreate because SQL Server
// really hates identity inserts
$sql->query("DROP TABLE productBackup");
if ($FANNIE_SERVER_DBMS == "MSSQL") {
$sql->query("SELECT * INTO productBackup FROM products");
} else {
$sql->query("CREATE TABLE productBackup LIKE products");
$sql->query("INSERT INTO productBackup SELECT * FROM products");
}
$sql->query("DROP TABLE custdataBackup");
if ($FANNIE_SERVER_DBMS == "MSSQL") {
$sql->query("SELECT * INTO custdataBackup FROM custdata");
} else {
$sql->query("CREATE TABLE custdataBackup LIKE custdata");
$sql->query("INSERT INTO custdataBackup SELECT * FROM custdata");
}
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');
*********************************************************************************/
/* HELP
monthly.nabs.php
Make AR payments on nabs accounts
to clear end-of-month balance
probably not relevant for anyone else
*/
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';
}
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_TRANS_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$insQ = "INSERT INTO dtransactions SELECT * FROM nabsAdjustView";
$insR = $sql->query($insQ);
// fix trans_no values
$tn = 1;
$fixQ = "SELECT card_no FROM dtransactions WHERE register_no=20\n AND emp_no=1001";
$fixR = $sql->query($fixQ);
while ($fixW = $sql->fetch_row($fixR)) {
$transQ = "UPDATE dtransactions SET trans_no={$tn}\n WHERE register_no=20 and emp_no=1001\n AND card_no=" . $fixW['card_no'];
$transR = $sql->query($transQ);
$tn++;
}
Update memcoupon table for WFC virtual coupon
Adjust custdata settings to match
*/
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);
$TRANS = $FANNIE_SERVER_DBMS == "MSSQL" ? $FANNIE_TRANS_DB . ".dbo." : $FANNIE_TRANS_DB . ".";
$sql->query("TRUNCATE TABLE memcoupon");
$insQ = "INSERT INTO memcoupon\n select card_no, tdate, total,trans_num \n from {$TRANS}dlog_90_view\n where (trans_subtype = 'MA' or upc like '%MAD Coupon%') \n and ceiling(month(tdate)/3.0) = ceiling(month(" . $sql->now() . ")/3.0)\n and year(tdate) = year(" . $sql->now() . ")\n and total > -2.52 and total < 2.52\n order by card_no";
$insR = $sql->query($insQ);
$resetQ = "update custdata set memCoupons=1 where Type='PC'";
$resetR = $sql->query($resetQ);
$bl = "CONCAT( CONVERT(CardNo,char), ' ', LastName )";
if ($FANNIE_SERVER_DBMS == "MSSQL") {
$bl = "RTRIM(CardNo) + ' ' + RTRIM(LastName)";
}
$resetQ = "update custdata set memCoupons=0,blueLine={$bl} where Type<>'PC'";
$resetR = $sql->query($resetQ);
$usedQ = "SELECT cardno FROM memcoupon GROUP BY cardno HAVING SUM(total) <> 0";
$usedR = $sql->query($usedQ);
while ($usedW = $sql->fetch_row($usedR)) {
$upR = $sql->query("UPDATE custdata SET memCoupons=0 WHERE CardNo=" . $usedW['cardno']);
}
*/
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;
}
$lastDay = date("M d, Y", $tstamp) . " 11:59PM";
$sql = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_TRANS_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$dataQ = "SELECT d.upc as upc, p.description as description,\n sum(CASE WHEN d.quantity <> d.ItemQtty AND d.ItemQtty <> 0 THEN d.quantity*d.ItemQtty ELSE d.quantity END) as quantity,\n sum(d.total) as dollars,\n '{$lastDay}' as lastDay\n FROM dlog_90_view as d inner join \n {$FANNIE_OP_DB}.dbo.products as p\n on d.upc=p.upc\n WHERE p.scale = 0\n AND d.upc > '0000000999999'\n AND datepart(ww,tdate) = {$week}\n group by d.upc, p.description";
// mysql handles week # differently by default
if (strstr($FANNIE_SERVER_DBMS, "MYSQL")) {
$dataQ = "SELECT d.upc as upc, p.description as description,\n sum(CASE WHEN d.quantity <> d.ItemQtty AND d.ItemQtty <> 0 THEN d.quantity*d.ItemQtty ELSE d.quantity END) as quantity,\n sum(d.total) as dollars,\n '{$lastDay}' as lastDay\n FROM dlog_90_view as d inner join \n {$FANNIE_OP_DB}.products as p\n on d.upc=p.upc\n WHERE p.scale = 0\n AND d.upc > '0000000999999'\n AND week(tdate) = " . ($week - 1) . "\n group by d.upc, p.description";
}
/* SPINS numbering is non-standard in 2012
so week is offset by one in the filename
this may change back next year
*/
$filename = date('mdY.csv', $tstamp);
$outfile = sys_get_temp_dir() . "/" . $filename;
$fp = fopen($outfile, "w");
$dataR = $sql->query($dataQ);
while ($row = $sql->fetch_row($dataR)) {
for ($i = 0; $i < 4; $i++) {
fwrite($fp, "\"" . $row[$i] . "\",");
}
fwrite($fp, "\"" . $row[4] . "\"\n");
}
fclose($fp);
$conn_id = ftp_connect($SPINS_SERVER);
$login_id = ftp_login($conn_id, $SPINS_USER, $SPINS_PW);
if (!$conn_id or !$login_id) {
echo "FTP connect failed!";
}
ftp_chdir($conn_id, "data");
ftp_pasv($conn_id, True);
$upload = ftp_put($conn_id, $filename, $outfile, FTP_ASCII);
$allMembers[$ami] = sprintf("%05d|%s|C", $c_row[member_id], $c_row[change_date]);
//$allMembers[$ami] = sprintf("%05d|%s|C", $c_row[member_id], $c_row[modified_date]);
}
}
/* Get all the the members from IS4C, except placeholder "NEW MEMBER"s
* and the Dummy 99900-99998 and Non-Member 99999.
* Set in config_civicrm.php
* $is4cMin = 470;
* $is4cMax = 99900;
* $tempMemberLastName = 'NEW MEMBER';
*/
if (empty($tempMemberLastName)) {
$tempMemberLastName = 'NEW MEMBER';
}
$i_selectMembers = "SELECT CardNo, LastChange\nFROM custdata\nWHERE CardNo between {$is4cMin} AND {$is4cMax}\n AND (LastName IS NULL OR LastName != '{$tempMemberLastName}')\n AND NumberOfChecks != 9";
$i_members = $dbConn2->query("{$i_selectMembers}");
if ($dbConn2->errno) {
$message = sprintf("Select failed: %s\n", $dbConn->error);
dieHere($message, $dieMail);
}
if (!$i_members) {
$msg = sprintf("Failed on: %s", $i_selectMembers);
dieHere("{$msg}", $dieMail);
}
$lastCardNo = "";
while ($i_row = $dbConn2->fetch_row($i_members)) {
if ($i_row[CardNo] == $lastCardNo) {
continue;
}
$allMembers[] = sprintf("%05d|%s|I", $i_row[CardNo], $i_row[LastChange]);
$lastCardNo = $i_row[CardNo];
/**
Define any javascript needed
@return A javascript string
function javascript_content(){
}
*/
function body_content()
{
//Should this really be done with global?
//global $FANNIE_URL, $FANNIE_EQUITY_DEPARTMENTS;
include '../../config.php';
ob_start();
echo showInstallTabs("Sample Data", '../');
?>
<form action=InstallSampleDataPage.php method=post>
<h1 class="install">
<?php
if (!$this->themed) {
echo "<h1 class='install'>{$this->header}</h1>";
}
?>
</h1>
<?php
if (is_writable('../../config.php')) {
echo "<div class=\"alert alert-success\"><i>config.php</i> is writeable</div>";
} else {
echo "<div class=\"alert alert-danger\"><b>Error</b>: config.php is not writeable</div>";
}
?>
<hr />
<div class="well"><em>
<?php
/* First, if this is a request to load a file, do that.
*/
$db = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
if (isset($_REQUEST['employees'])) {
echo "Loading employees";
$db->query("TRUNCATE TABLE employees");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db, 'employees');
} elseif (isset($_REQUEST['custdata'])) {
echo "Loading custdata";
$backup1 = $db->query('TRUNCATE TABLE custdataBackup');
$backup2 = $db->query('INSERT INTO custdataBackup SELECT * FROM custdata');
if ($backup1 === false || $backup2 === false) {
echo _(' - failed to backup current data. Sample data not loaded.');
} else {
$db->query("TRUNCATE TABLE custdata");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db, 'custdata');
}
} elseif (isset($_REQUEST['memtype'])) {
echo "Loading memtype";
$db->query("TRUNCATE TABLE memtype");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db, 'memtype');
} elseif (isset($_REQUEST['products'])) {
echo "Loading products";
$backup1 = $db->query('TRUNCATE TABLE productBackup');
$backup2 = $db->query('INSERT INTO productBackup SELECT * FROM products');
if ($backup1 === false || $backup2 === false) {
echo _(' - failed to backup current data. Sample data not loaded.');
} else {
$db->query("TRUNCATE TABLE products");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db, 'products');
}
} elseif (isset($_REQUEST['prod-flags'])) {
echo "Loading product flags";
$db->query("TRUNCATE TABLE prodFlags");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db, 'prodFlags');
} elseif (isset($_REQUEST['batchType'])) {
echo "Loading batchn types";
$db->query("TRUNCATE TABLE batchType");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db, 'batchType');
} elseif (isset($_REQUEST['depts'])) {
echo "Loading departments";
$db->query("TRUNCATE TABLE departments");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db, 'departments');
/* subdepts sample data is of questionable use
echo "<br />Loading subdepts";
$db->query("TRUNCATE TABLE subdepts");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db,'subdepts');
*/
} elseif (isset($_REQUEST['superdepts'])) {
echo "Loading super departments";
$db->query("TRUNCATE TABLE superdepts");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db, 'superdepts');
$db->query("TRUNCATE TABLE superDeptNames");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db, 'superDeptNames');
} elseif (isset($_REQUEST['tenders'])) {
echo "Loading tenders";
$db->query("TRUNCATE TABLE tenders");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db, 'tenders');
} elseif (isset($_REQUEST['authentication'])) {
echo "Loading authentication info";
$db->query("TRUNCATE TABLE userKnownPrivs");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db, 'userKnownPrivs');
} elseif (isset($_REQUEST['origin'])) {
echo "Loading country info";
$db->query("TRUNCATE TABLE originCountry");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db, 'originCountry');
echo "<br />Loading state/province info";
$db->query("TRUNCATE TABLE originStateProv");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db, 'originStateProv');
} else {
if (isset($_REQUEST['authGroups'])) {
echo "Loading authentication groups";
$db->query("TRUNCATE TABLE userGroups");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db, 'userGroups');
$db->query("TRUNCATE TABLE userGroupPrivs");
\COREPOS\Fannie\API\data\DataLoad::loadSampleData($db, 'userGroupPrivs');
// give "Administrators" group all permissions
$db->query("INSERT userGroupPrivs SELECT \n 1, auth_class, 'all', 'all'\n FROM userKnownPrivs");
}
}
?>
</em></div>
<?php
/* Display a list of data that can be loaded.
*/
?>
<p class="ichunk">
Some sample data is available to get a test lane
up and running quickly and to try Fannie functions.
<h3>Keep in mind this data overwrites whatever is currently in the table.</h3>
<br />These utilities populate the server tables.
Then use the <a href="../../sync/SyncIndexPage.php"
target="_sync"><u>Synchronize</u></a>
utilities to populate the lane tables.
</p>
<hr />
<h4 class="install"><?php
echo _('Cashiers');
?>
</h4>
This table contains login information for cashiers. The two
included logins are '56' and '7000'.<br />
<?php
echo $this->loadButton('employees', 'employees', _('Load sample cashiers'));
?>
<hr />
<h4 class="install">Customer Data</h4>
Customer data is the membership information. Sample data includes
a bunch of members and default non-member 11.<br />
<?php
echo $this->loadButton('custdata', 'custdata', _('Load sample customers'));
?>
<br />
<br />
Customers are classified into different membership types.<br />
<?php
echo $this->loadButton('memtype', 'memtype', _('Load sample member types'));
?>
<hr />
<h4 class="install">Products</h4>
Stuff to sell. There's a lot of sample data. I think this might
be the Wedge's or at least a snapshot of it.<br />
<?php
echo $this->loadButton('products', 'products', _('Load sample products'));
?>
<hr />
<h4 class="install">Product Flags</h4>
Product Flags are a flexible method for identifying custom attributes of items.
CORE includes a default set of some more common flags.<br />
<?php
echo $this->loadButton('prodFlags', 'prod-flags', _('Load sample product flags'));
?>
<hr />
<h4 class="install">Batch Types</h4>
Batches are used for temporary promotional pricing as well as scheduling changes
in regular retail price. Batches may be organized by type. Sample data includes
a couple common options.
<?php
echo $this->loadButton('batchType', 'batchType', _('Load sample batch types'));
?>
<hr />
<h4 class="install">Departments</h4>
Products get categorized into departments .
You can also ring amounts directly to a department. Not needed,
strictly speaking, for a basic lane (Ring up items, total,
accept tender, provide change).<br />
<?php
echo $this->loadButton('departments', 'depts', _('Load sample departments'));
?>
<hr />
<h4 class="install">Super-Department Names <span style="font-weight:400;">and</span> Super-Department Links</h4>
Super Departments are tags for grouping Departments.
A Department can have more than one, that is, belong to more than one Super-Department.
This rudimentary set agrees with the Products sample data.
Super-Departments can also be used to group the domains of Buyers.
Use them with e.g. the <a href="../../fannie/item/productList.php">Product List report/tool</a>
They are also used for grouping shelftags for printing and for grouping data in reports.
<?php
echo $this->loadButton('superdepts', 'superdepts', _('Load sample super departments'));
?>
<hr />
<h4 class="install">Tenders</h4>
Load all the default tenders into the tenders table.<br />
<?php
echo $this->loadButton('tenders', 'tenders', _('Load sample tenders'));
?>
<hr />
<h4 class="install">Authentication</h4>
Load information about currently defined authorization classes<br />
<?php
echo $this->loadButton('userKnownPrivs', 'authentication', _('Load auth classes'));
?>
<br /><br />
Load default groups<br />
<?php
echo $this->loadButton('userGroups', 'authGroups', _('Load auth groups'));
?>
<hr />
<h4 class="install">Countries, States, and Provinces</h4>
Load default place-of-origin information<br />
<?php
echo $this->loadButton('originCountry', 'origin', _('Load origin info'));
?>
<hr />
</form>
<?php
return ob_get_clean();
// body_content
}
/**
Get a column name by index
@param $result_object A result set
@param $index Integer index
@param $which_connection see method close()
@return The column name
function fetch_field($result_object,$index,$which_connection=''){
if ($which_connection == '')
$which_connection = $this->default_db;
return $result_object->FetchField($index);
}
*/
// Little tests of is4c connection.
if (0) {
$selectIs4c = "SELECT CardNo, LastName from custdata LIMIT 5;";
$customers = $dbConn2->query("{$selectIs4c}");
// ->errno probably doesn't exist in SQLManager
if ($dbConn2->errno) {
$message = sprintf("Select failed: %s\n", $dbConn->error);
dieHere($message);
}
// Quick test.
echo "IS4C Numbered\n";
while ($row = $dbConn2->fetch_row($customers)) {
// Why does $row contain each field twice?
// Because $row is a BOTH list and hash; use hash syntax.
// array_keys gets both names and numbers.
//$flds = array_keys($row);
$flds = getNameKeys($row);
$lineOut = implode("\t", $flds) . "\n";
echo $lineOut;
Sync Fannie custdata balance with live table.
Usually once a day is enough.
Do before syncing lane custdata with Fannie's.
See also: LanePush/UpdateCustBalance.php
Run either after nightly.dtrans and nightly.ar, not between them,
and before [nightly.]lanesync.api or nightly.lanesync
Deprecated in favour of cron/tasks/ArHistoryTask.php
*/
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 = "UPDATE {$FANNIE_OP_DB}.custdata AS c\n LEFT JOIN ar_live_balance AS n ON c.CardNo=n.card_no\n SET c.Balance = n.balance";
if ($FANNIE_SERVER_DBMS == "MSSQL") {
$query = "UPDATE {$FANNIE_OP_DB}.dbo.custdata SET Balance = n.balance\n FROM {$FANNIE_OP_DB}.dbo.custdata AS c LEFT JOIN\n ar_live_balance AS n ON c.CardNo=n.card_no";
}
$rslt = $sql->query($query);
if ($rslt === False) {
echo cron_msg("Failed.");
} else {
echo cron_msg("OK.");
}
