function deleteProductAllLanes($upc)
{
$FANNIE_OP_DB = FannieConfig::config('OP_DB');
$FANNIE_LANES = FannieConfig::config('LANES');
$laneupdate_sql = FannieDB::get($FANNIE_OP_DB);
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 (!isset($tmp->connections[$FANNIE_LANES[$i]['op']]) || $tmp->connections[$FANNIE_LANES[$i]['op']] === false) {
// connect failed
continue;
}
$delQ = $tmp->prepare_statement("DELETE FROM products WHERE upc=?");
$delR = $tmp->exec_statement($delQ, array($upc), $FANNIE_LANES[$i]['op']);
}
}
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;
}
function redoCard($cardno)
{
global $FANNIE_LANES, $sql;
$upcQ = $sql->prepare("SELECT upc FROM memberCards WHERE card_no=?");
$upcR = $sql->execute($upcQ, array($cardno));
$upc = "";
if ($sql->num_rows($upcR) > 0) {
$upcW = $sql->fetch_row($upcR);
$upc = $upcW['upc'];
}
foreach ($FANNIE_LANES as $lane) {
$tmp = new SQLManager($lane['host'], $lane['type'], $lane['op'], $lane['user'], $lane['pw']);
$delQ = $tmp->prepare("DELETE FROM memberCards WHERE card_no=?");
$delR = $tmp->execute($delQ, array($cardno));
if (!empty($upc)) {
$ins = $tmp->prepare("INSERT INTO memberCards (card_no, upc)\n VALUES (?, ?)");
$tmp->execute($ins, array($cardno, $upc));
}
}
}
function allLanes($upc)
{
if (!class_exists("SQLManager")) {
require_once "../../src/SQLManager.php";
}
include '../lanedefs.php';
$queryItem = '';
$args = array();
if (is_numeric($upc)) {
$upc = str_pad($upc, 13, 0, STR_PAD_LEFT);
$queryItem = "SELECT * FROM products WHERE upc = ?";
$args = array($upc);
} else {
$queryItem = "SELECT * FROM products WHERE description LIKE ? ORDER BY description";
$args = array('%' . $upc . '%');
}
for ($i = 0; $i < count($lanes); $i++) {
$currentLane = $lanes[$i];
if (substr($currentLane, 0, 3) == "POS") {
$currentLane = "129.103.2.1" . substr($currentLane, -1);
}
$sql = new SQLManager($currentLane, $types[$i], $dbs[$i], $users[$i], $pws[$i]);
//continue;
$prep = $sql->prepare($queryItem);
$resultItem = $sql->execute($prep, $args);
$num = $sql->num_rows($resultItem);
if ($num == 0) {
echo "Item <font color='red'>{$upc}</font> not found on Lane " . ($i + 1) . "<br />";
} else {
if ($num > 1) {
echo "Item <font color='red'>{$upc}</font> found multiple times on Lane " . ($i + 1) . "<br />";
while ($rowItem = $sql->fetch_array($resultItem)) {
echo "{$rowItem['upc']} {$rowItem['description']}<br />";
}
} else {
$rowItem = $sql->fetch_array($resultItem);
echo "Item <font color='red'>{$upc}</font> on Lane " . ($i + 1) . "<br />";
echo "Price: {$rowItem['normal_price']}";
if ($rowItem['special_price'] != 0) {
echo " <font color=green>ON SALE: {$rowItem['special_price']}</font>";
}
echo "<br />";
}
}
if ($i < count($lanes) - 1) {
echo "<hr />";
}
}
}
/**
Fetch data for the specified report
@param [string] $report_class_name name of report
@param [FannieConfig] $config current configuration
@param [SQLManager] $connection database connection
@return [array] report records or [boolean] false
if this source cannot handle the request
*/
public function fetchReportData($report_class_name, \FannieConfig $config, \SQLManager $connection)
{
$date1 = \FormLib::get_form_value('date1', date('Y-m-d'));
$date2 = \FormLib::get_form_value('date2', date('Y-m-d'));
$type = \FormLib::get_form_value('report-basis', 'Purchases');
$exclude = \FormLib::get_form_value('excludes', '');
if ($type == 'Join Date') {
return false;
}
$ex = preg_split('/\\D+/', $exclude, 0, PREG_SPLIT_NO_EMPTY);
$exCondition = '';
$exArgs = array();
foreach ($ex as $num) {
$exCondition .= '?,';
$exArgs[] = $num;
}
$exCondition = substr($exCondition, 0, strlen($exCondition) - 1);
$originalDB = $connection->defaultDatabase();
$plugin_settings = $config->get('PLUGIN_SETTINGS');
$connection->selectDB($plugin_settings['WarehouseDatabase']);
$query = "\n SELECT \n CASE WHEN m.zip='' THEN 'none' ELSE m.zip END as zipcode,\n COUNT(*) as num_trans, \n SUM(total) as spending,\n COUNT(DISTINCT s.card_no) as uniques\n FROM sumMemSalesByDay AS s \n INNER JOIN " . $config->get('OP_DB') . $connection->sep() . "meminfo AS m ON s.card_no=m.card_no \n WHERE ";
if (!empty($exArgs)) {
$query .= "s.card_no NOT IN ({$exCondition}) AND ";
}
$query .= "s.date_id BETWEEN ? AND ?\n GROUP BY zipcode\n ORDER BY SUM(total) DESC";
$exArgs[] = $this->dateToID($date1);
$exArgs[] = $this->dateToID($date2);
$prep = $connection->prepare($query);
$result = $connection->execute($prep, $exArgs);
while ($row = $connection->fetchRow($result)) {
$record = array($row['zipcode'], $row['num_trans'], $row['uniques'], $row['spending']);
$data[] = $record;
}
$connection->setDefaultDB($originalDB);
return $data;
}
/**
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
}
function forceBatch($batchID)
{
global $sql, $FANNIE_SERVER_DBMS, $FANNIE_LANES;
$batchInfoQ = $sql->prepare("SELECT batchType,discountType FROM batches WHERE batchID = ?");
$batchInfoR = $sql->execute($batchInfoQ, array($batchID));
$batchInfoW = $sql->fetch_array($batchInfoR);
$forceQ = "";
$forceLCQ = "";
$forceMMQ = "";
if ($batchInfoW['discountType'] != 0) {
$forceQ = "UPDATE products AS p\n INNER JOIN batchList AS l\n ON p.upc=l.upc\n INNER JOIN batches AS b\n ON l.batchID=b.batchID\n SET p.start_date = b.startDate, \n p.end_date=b.endDate,\n p.special_price=l.salePrice,\n p.specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,\n p.specialpricemethod=l.pricemethod,\n p.specialquantity=l.quantity,\n p.discounttype=b.discounttype,\n p.mixmatchcode = CASE \n WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(l.batchID,char)\n WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(-1*l.batchID,char)\n WHEN l.pricemethod = 0 AND l.quantity > 0 THEN concat('b',convert(l.batchID,char))\n ELSE p.mixmatchcode \n END \n WHERE l.upc not like 'LC%'\n and l.batchID = ?";
$forceLCQ = "UPDATE products AS p\n INNER JOIN upcLike AS v \n ON v.upc=p.upc\n INNER JOIN batchList as l \n ON l.upc=concat('LC',convert(v.likecode,char))\n INNER JOIN batches AS b \n ON b.batchID=l.batchID\n set p.special_price = l.salePrice,\n p.end_date = b.endDate,p.start_date=b.startDate,\n p.specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,\n p.specialpricemethod=l.pricemethod,\n p.specialquantity=l.quantity,\n p.discounttype = b.discounttype,\n p.mixmatchcode = CASE \n WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(l.batchID,char)\n WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(-1*l.batchID,char)\n WHEN l.pricemethod = 0 AND l.quantity > 0 THEN concat('b',convert(l.batchID,char))\n ELSE p.mixmatchcode \n END \n where l.batchID=?";
if ($FANNIE_SERVER_DBMS == 'MSSQL') {
$forceQ = "UPDATE products\n SET start_date = b.startDate, \n end_date=b.endDate,\n special_price=l.salePrice,\n specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,\n specialpricemethod=l.pricemethod,\n specialquantity=l.quantity,\n discounttype=b.discounttype,\n mixmatchcode = CASE \n WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(varchar,l.batchID)\n WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(varchar,-1*l.batchID)\n WHEN l.pricemethod = 0 AND l.quantity > 0 THEN 'b'+convert(varchar,l.batchID)\n ELSE p.mixmatchcode \n END \n FROM products as p, \n batches as b, \n batchList as l \n WHERE l.upc = p.upc\n and l.upc not like 'LC%'\n and b.batchID = l.batchID\n and b.batchID = ?";
$forceLCQ = "update products set special_price = l.salePrice,\n end_date = b.endDate,start_date=b.startDate,\n discounttype = b.discounttype,\n specialpricemethod=l.pricemethod,\n specialquantity=l.quantity,\n specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,\n mixmatchcode = CASE \n WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(varchar,l.batchID)\n WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(varchar,-1*l.batchID)\n WHEN l.pricemethod = 0 AND l.quantity > 0 THEN 'b'+convert(varchar,l.batchID)\n ELSE p.mixmatchcode \n END \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 b.batchID=?";
}
} else {
$forceQ = "UPDATE products AS p\n INNER JOIN batchList AS l\n ON l.upc=p.upc\n SET p.normal_price = l.salePrice,\n p.modified = now()\n WHERE l.upc not like 'LC%'\n AND l.batchID = ?";
$forceLCQ = "UPDATE products AS p\n INNER JOIN upcLike AS v\n ON v.upc=p.upc INNER JOIN\n batchList as b on b.upc=concat('LC',convert(v.likecode,char))\n set p.normal_price = b.salePrice,\n p.modified=now()\n where b.batchID=?";
if ($FANNIE_SERVER_DBMS == 'MSSQL') {
$forceQ = "UPDATE products\n SET normal_price = l.salePrice,\n modified = getdate()\n FROM products as p,\n batches as b,\n batchList as l\n WHERE l.upc = p.upc\n AND l.upc not like 'LC%'\n AND b.batchID = l.batchID\n AND b.batchID = ?";
$forceLCQ = "update products set normal_price = b.salePrice,\n modified=getdate()\n from products as p left join\n upcLike as v on v.upc=p.upc left join\n batchList as b on b.upc='LC'+convert(varchar,v.likecode)\n where b.batchID=?";
}
}
$forceP = $sql->prepare($forceQ);
$forceR = $sql->execute($forceP, array($batchID));
$forceLCP = $sql->prepare($forceLCQ);
$forceLCR = $sql->execute($forceLCP, array($batchID));
$columnsP = $sql->prepare('
SELECT p.upc,
p.normal_price,
p.special_price,
p.modified,
p.specialpricemethod,
p.specialquantity,
p.specialgroupprice,
p.discounttype,
p.mixmatchcode,
p.start_date,
p.end_date
FROM products AS p
INNER JOIN batchList AS b ON p.upc=b.upc
WHERE b.batchID=?');
$lcColumnsP = $sql->prepare('
SELECT p.upc,
p.normal_price,
p.special_price,
p.modified,
p.specialpricemethod,
p.specialquantity,
p.specialgroupprice,
p.discounttype,
p.mixmatchcode,
p.start_date,
p.end_date
FROM products AS p
INNER JOIN upcLike AS u ON p.upc=u.upc
INNER JOIN batchList AS b
ON b.upc = ' . $sql->concat("'LC'", $sql->convert('u.likeCode', 'CHAR'), '') . '
WHERE b.batchID=?');
/**
Get changed columns for each product record
*/
$upcs = array();
$columnsR = $sql->execute($columnsP, array($batchID));
while ($w = $sql->fetch_row($columnsR)) {
$upcs[$w['upc']] = $w;
}
$columnsR = $sql->execute($lcColumnsP, array($batchID));
while ($w = $sql->fetch_row($columnsR)) {
$upcs[$w['upc']] = $w;
}
$updateQ = '
UPDATE products AS p SET
p.normal_price = ?,
p.special_price = ?,
p.modified = ?,
p.specialpricemethod = ?,
p.specialquantity = ?,
p.specialgroupprice = ?,
p.discounttype = ?,
p.mixmatchcode = ?,
p.start_date = ?,
p.end_date = ?
WHERE p.upc = ?';
/**
Update all records on each lane before proceeding
to the next lane. Hopefully faster / more efficient
*/
for ($i = 0; $i < count($FANNIE_LANES); $i++) {
$lane_sql = new SQLManager($FANNIE_LANES[$i]['host'], $FANNIE_LANES[$i]['type'], $FANNIE_LANES[$i]['op'], $FANNIE_LANES[$i]['user'], $FANNIE_LANES[$i]['pw']);
if (!isset($lane_sql->connections[$FANNIE_LANES[$i]['op']]) || $lane_sql->connections[$FANNIE_LANES[$i]['op']] === false) {
// connect failed
continue;
}
$updateP = $lane_sql->prepare($updateQ);
foreach ($upcs as $upc => $data) {
$lane_sql->execute($updateP, array($data['normal_price'], $data['special_price'], $data['modified'], $data['specialpricemethod'], $data['specialquantity'], $data['specialgroupprice'], $data['discounttype'], $data['mixmatchcode'], $data['start_date'], $data['end_date'], $upc));
}
}
$update = new ProdUpdateModel($sql);
$updateType = $batchInfoW['discountType'] == 0 ? ProdUpdateModel::UPDATE_PC_BATCH : ProdUpdateModel::UPDATE_BATCH;
$update->logManyUpdates(array_keys($upcs), $updateType);
}
//$lineOut = implode("\t", $row) . "\n";
// Reduce to one set.
/*
$vals = getNameValues($row);
$lineOut = implode("\t", $vals) . "\n";
echo $lineOut;
*/
}
dieHere("Little test c OK, bailing ...");
// Enable/defeat little tests of civicrm connection
}
// Enable/defeat is4c connection
if (1) {
//echo "Start connection to is4c.\n";
//$dbConn2 = @new mysqli("$IS4C_IP", "$IS4C_USER", "$IS4C_PASSWORD", "$IS4C_DB");
$dbConn2 = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
if ($dbConn2->connect_errno) {
$message = sprintf("Connect2 failed: %s\n", $dbConn2->connect_error);
dieHere("{$message}");
// if ( $dbConn ) {
// $dbConn->close();
// }
// die("dying ...");
//exit();
}
/**
Can we use this? No
$dbConn2->SetFetchMode(ADODB_FETCH_ASSOC);
* PEAR DB Compat - do not use internally.
*
* The fetch modes for NUMERIC and ASSOC for PEAR DB and ADODB are identical
up-to-date, i.e.
ar_live_balance.balance < AR_EOM_Summary.twoMonthBalance
When/how-often can/should it be run? Daily?
*/
/* --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)) {
/**
Get an available dtransactions.trans_no value
@param $connection [SQLManager] database connection
@param $emp_no [int] employee number
@param $register_no [int] register number
@return [int] trans_no
*/
public static function getTransNo(SQLManager $connection, $emp_no = false, $register_no = false)
{
$config = FannieConfig::factory();
if ($emp_no === false) {
$emp_no = $config->get('EMP_NO');
}
if ($register_no === false) {
$register_no = $config->get('REGISTER_NO');
}
$prep = $connection->prepare('
SELECT MAX(trans_no) AS trans
FROM ' . $config->get('TRANS_DB') . $connection->sep() . 'dtransactions
WHERE emp_no=?
AND register_no=?');
$result = $connection->execute($prep, array($emp_no, $register_no));
if (!$result || $connection->num_rows($result) == 0) {
return 1;
} else {
$row = $connection->fetch_row($result);
if ($row['trans'] == '') {
return 1;
} else {
return $row['trans'] + 1;
}
}
}
*/
/* 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");
}
After midnight probably better.
*/
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_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']);
and logs those into prodDepartmentHistory.
This is just faster to deal with as prodUpdate
ends up having a ton of entries.
*/
if (!chdir(dirname(__FILE__))) {
echo "Error: Can't find directory (prod update compress dept)";
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;
public static function aggregateStruct(SQLManager $connection, $dlog, $start_date, $end_date, stdclass $where, $groupby = array())
{
$base_table = self::selectStruct($dlog, $start_date, $end_date);
$dt_col = $dlog ? 'tdate' : 'datetime';
$clone_table = $dlog ? 'dlog_15' : 'transarchive';
/**
Grouping is required
*/
if (!is_array($groupby) || count($groupby) == 0) {
return $base_table;
}
/**
Validate group by columns
*/
$model = new DTransactionsModel(null);
$columns = $model->getColumns();
$insert_cols = array();
$select_cols = array();
for ($i = 0; $i < count($groupby); $i++) {
$group = $groupby[$i];
if (isset($columns[$group])) {
$insert_cols[] = $group;
$select_cols[] = $group;
} elseif (preg_match('/(.+)\\s+AS\\s+(\\w+)$/', $group, $matches)) {
$col_definition = $matches[1];
$col_alias = $matches[2];
if (isset($columns[$col_alias])) {
$insert_cols[] = $col_alias;
$select_cols[] = $group;
$groupby[$i] = $col_definition;
} else {
return $base_table;
}
} else {
return $base_table;
}
}
/**
Always include a datetime column
*/
if (!in_array($dt_col, $insert_cols)) {
$insert_cols[] = $dt_col;
$select_cols[] = 'MAX(' . $dt_col . ') AS ' . $dt_col;
}
/**
Create randomly named temporary table based
on the structure of dlog_15 or transachive
*/
$config = FannieConfig::factory();
$sep = $connection->sep();
$random_name = uniqid('temp' . rand(1000, 9999));
$temp_table = $config->get('ARCHIVE_DB') . $sep . $random_name;
$clone_table = $config->get('TRANS_DB') . $sep . $clone_table;
$temp_name = $connection->temporaryTable($temp_table, $clone_table);
if ($temp_name === false) {
return $base_table;
}
/**
Build a query to insert aggregated rows into
the temporary table
*/
$query = 'INSERT INTO ' . $temp_name . '(';
foreach ($insert_cols as $c) {
$query .= $c . ',';
}
$query .= 'total, quantity) ';
$query .= ' SELECT ';
foreach ($select_cols as $c) {
$query .= $c . ',';
}
/**
Always aggregate by total & quantity
*/
$query .= ' SUM(total) AS total, ' . DTrans::sumQuantity() . ' AS quantity
FROM __TRANSACTION_TABLE__
WHERE ' . $dt_col . ' BETWEEN ? AND ? ';
$params = array($start_date . ' 00:00:00', $end_date . ' 23:59:59');
/**
Add a where clause if one has been specified
*/
if (property_exists($where, 'sql') && is_array($where->sql)) {
foreach ($where->sql as $sql) {
$query .= ' AND ' . $sql;
}
}
if (property_exists($where, 'params') && is_array($where->params)) {
foreach ($where->params as $p) {
$params[] = $p;
}
}
/**
Add the group by clause
*/
$query .= ' GROUP BY ';
foreach ($groupby as $group) {
$query .= $group . ',';
}
$query = substr($query, 0, strlen($query) - 1);
/**
Split monthly archive union if needed
*/
$source_tables = array();
if (strstr($base_table, ' UNION ')) {
preg_match_all('/\\s+FROM\\s+(\\w+)\\s+/', $base_table, $matches);
foreach ($matches[1] as $m) {
$source_tables[] = $m;
}
} else {
$source_tables = array($base_table);
}
/**
Load data into temporary table from source table(s)
using built query
*/
foreach ($source_tables as $source_table) {
$insertQ = str_replace('__TRANSACTION_TABLE__', $source_table, $query);
$prep = $connection->prepare($insertQ);
if (!$connection->execute($prep, $params)) {
return $base_table;
}
}
return $temp_name;
}
<?php
include dirname(__FILE__) . '/../../config.php';
if (!class_exists('SQLManager')) {
include $FANNIE_ROOT . 'src/SQLManager.php';
}
if (basename(__FILE__) != basename($_SERVER['PHP_SELF'])) {
return;
}
$dbc = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
$p1 = $dbc->prepare_statement("SELECT upc FROM productUser where upc=?");
$p2 = $dbc->prepare_statement("SELECT upc FROM products WHERE upc=?");
$upP = $dbc->prepare_statement("UPDATE productUser SET photo=? WHERE upc=?");
$dh = opendir('new');
while (($file = readdir($dh)) !== False) {
$exts = explode(".", $file);
$e = strtolower(array_pop($exts));
if ($e != "png" && $e != "gif" && $e != "jpg" && $e != "jpeg") {
continue;
}
$u = array_pop($exts);
if (!is_numeric($u)) {
continue;
}
$upc = str_pad($u, 13, '0', STR_PAD_LEFT);
$r1 = $dbc->exec_statement($p1, array($upc));
if ($dbc->num_rows($r1) > 0) {
echo "UPC {$upc} found in productUser\n";
$upR = $dbc->exec_statement($upP, array($file, $upc));
rename('new/' . $file, 'done/' . $file);
rename('new/' . $u . '.thumb.' . $e, 'done/' . $u . '.thumb.' . $e);
// The "@" prevents the error from being reported immediately,
// but the test further on will still see it.
if (!$dev) {
$dbConn = new SQLManager($CIVICRM_SERVER, $CIVICRM_SERVER_DBMS, $CIVICRM_DB, $CIVICRM_SERVER_USER, $CIVICRM_SERVER_PW);
} else {
$dbConn = new SQLManager($CIVICRM_SERVER, $CIVICRM_SERVER_DBMS, $CIVICRM_DB_DEV, $CIVICRM_SERVER_USER_DEV, $CIVICRM_SERVER_PW_DEV);
}
$message = $dbConn->error();
if ($message != "") {
dieHere("{$message}", $dieMail);
}
/* Little tests of civiCRM connection.
dieHere("Civi connection did not fail, bailing ...", 0);
civiTestAndDie2($dbConn);
*/
$dbConn2 = new SQLManager($FANNIE_SERVER, $FANNIE_SERVER_DBMS, $FANNIE_OP_DB, $FANNIE_SERVER_USER, $FANNIE_SERVER_PW);
if ($dbConn2->connect_errno) {
$message = sprintf("Connect2 failed: %s\n", $dbConn2->connect_error);
dieHere("{$message}", $dieMail);
}
/*Get the timestamp of the last run of this program
* Not sure that is safe. Always use epoch.
* The pairs with same datestamps are ignored.
* If parts of pairs lost, then trouble. How would that happen?
*/
$epoch = "0000-00-00 00:00:00";
$latestRunDate = $epoch;
//$latestRunDate = getLatestRun($reportFile);
if (!isset($adminId)) {
dieHere("\$adminId is not set. Is usually set in config_civicrm.php\n", $dieMail);
}
/* HELP
nightly.virtualcoupon.php
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)";
in arrears, i.e.
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";
UPDATE products AS p SET
p.normal_price = ?,
p.special_price = ?,
p.modified = ?,
p.specialpricemethod = ?,
p.specialquantity = ?,
p.specialgroupprice = ?,
p.discounttype = ?,
p.mixmatchcode = ?,
p.start_date = ?,
p.end_date = ?
WHERE p.upc = ?';
/**
Update all records on each lane before proceeding
to the next lane. Hopefully faster / more efficient
*/
for ($i = 0; $i < count($FANNIE_LANES); $i++) {
$lane_sql = new SQLManager($FANNIE_LANES[$i]['host'], $FANNIE_LANES[$i]['type'], $FANNIE_LANES[$i]['op'], $FANNIE_LANES[$i]['user'], $FANNIE_LANES[$i]['pw']);
if (!isset($lane_sql->connections[$FANNIE_LANES[$i]['op']]) || $lane_sql->connections[$FANNIE_LANES[$i]['op']] === false) {
// connect failed
continue;
}
$updateP = $lane_sql->prepare($updateQ);
foreach ($upcs as $upc => $data) {
$lane_sql->execute($updateP, array($data['normal_price'], $data['special_price'], $data['modified'], $data['specialpricemethod'], $data['specialquantity'], $data['specialgroupprice'], $data['discounttype'], $data['mixmatchcode'], $data['start_date'], $data['end_date'], $upc));
}
}
$update = new ProdUpdateModel($sql);
$updateType = $batchInfoW['discountType'] == 0 ? ProdUpdateModel::UPDATE_PC_BATCH : ProdUpdateModel::UPDATE_BATCH;
$update->logManyUpdates(array_keys($upcs), $updateType);
echo "Batch {$batchID} has been forced";
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);
}
}
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);
@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++;
}
least once a day.
This script does not update the lanes, therefore
the day's last run should be before lane syncing.
Changes are logged in prodUpdate if possible.
*/
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 {
nightly.memcoupon.php
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)) {
<?php
require_once "SQLManager.php";
$sqlm = new SQLManager();
foreach ($_GET as $key => $val) {
${$key} = $val;
}
foreach ($_POST as $k => $v) {
${$k} = $v;
}
$params_start = strpos($_SERVER['REQUEST_URI'], '?') + 1;
if ($params_start > 1) {
$requestURI = substr($_SERVER['REQUEST_URI'], $params_start);
$params = explode('&', $requestURI);
foreach ($params as $k => $v) {
$params[$k] = explode('=', $v);
${$params}[$k][0] = $params[$k][1];
}
}
$type = substr($queryType, 0, 5);
if ($type == 'Table' && isset($table)) {
$sqlm->setTableParams($table);
}
switch ($queryType) {
case 'TableCountRows':
$sqlm->setJSON($sqlm->getCountRowsAllTables());
break;
case 'TableVisualize':
$sort = isset($sort) ? $sort : false;
$dir = isset($dir) ? $dir : false;
$r = $sqlm->getAllFromTable($table, $sort, $dir);
return;
}
$tstamp = time();
$week = date("W", $tstamp);
$week--;
if ($week == 0) {
$week = 52;
}
if (isset($argv[1]) && is_numeric($argv[1])) {
$week = $argv[1];
}
while (date("W", $tstamp) != $week or date("w", $tstamp) != 6) {
$tstamp = mktime(0, 0, 0, date("n", $tstamp), date("j", $tstamp) - 1, date("Y", $tstamp));
}
$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++) {
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.");
}
