public function run()
{
$sql = FannieDB::get($this->config->get('OP_DB'));
$chk_vital = array();
$chk_opt = array();
/* change prices
*/
if (strstr($this->config->get('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($this->config->get('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;
break;
}
}
if ($success) {
$this->cronMsg("Price change batches run successfully");
} else {
$this->cronMsg("Error running price change batches");
}
// log updates to prodUpdate table
$success = true;
$likeP = $sql->prepare('SELECT upc FROM upcLike WHERE likeCode=?');
$batchQ = 'SELECT upc FROM batchList as l LEFT JOIN batches AS b
ON l.batchID=b.batchID WHERE b.discounttype=0
AND ' . $sql->datediff($sql->now(), 'b.startDate') . ' = 0';
$batchR = $sql->query($batchQ);
$prodUpdate = new ProdUpdateModel($sql);
while ($batchW = $sql->fetch_row($batchR)) {
$upcs = array();
$upc = $batchW['upc'];
// unpack likecodes to UPCs
if (substr($upc, 0, 2) == 'LC') {
$likeR = $sql->execute($likeP, array(substr($upc, 2)));
while ($likeW = $sql->fetch_row($likeR)) {
$upcs[] = $likeW['upc'];
}
} else {
$upcs[] = $upc;
}
foreach ($upcs as $item) {
$prodUpdate->reset();
$prodUpdate->upc($item);
$logged = $prodUpdate->logUpdate(ProdUpdateModel::UPDATE_PC_BATCH, 1001);
if (!$logged) {
$success = false;
}
}
}
if ($success) {
$this->cronMsg("Changes logged in prodUpdate");
} else {
$this->cronMsg("Error logging changes");
}
}
/**
Scan prodUpdate from dept changes and log them
in prodDepartmentHistory
@param $dbc [SQLManager] db connection
@param $offset [optional int] start scanning from this prodUpdateID
*/
private function scanDeptChanges($dbc, $offset = 0)
{
$prodUpdateQ = 'SELECT prodUpdateID FROM prodUpdate ';
$args = array();
if ($offset > 0) {
$prodUpdateQ .= ' WHERE prodUpdateID > ? ';
$args[] = $offset;
}
$prodUpdateQ .= ' ORDER BY upc, modified';
$prodUpdateP = $dbc->prepare($prodUpdateQ);
$prodUpdateR = $dbc->execute($prodUpdateP, $args);
$chkP = $dbc->prepare("SELECT modified,dept_ID FROM\n prodDepartmentHistory WHERE upc=?\n ORDER BY modified DESC");
$upc = null;
$prevDept = null;
$update = new ProdUpdateModel($dbc);
$history = new ProdDepartmentHistoryModel($dbc);
/**
See scanPriceChanges()
*/
while ($prodUpdateW = $dbc->fetch_row($prodUpdateR)) {
$update->reset();
$update->prodUpdateID($prodUpdateW['prodUpdateID']);
$update->load();
if ($upc === null || $upc != $update->upc()) {
$upc = $update->upc();
$prevDept = null;
$chkR = $dbc->execute($chkP, array($upc));
if ($dbc->num_rows($chkR) > 0) {
$chkW = $dbc->fetch_row($chkR);
$prevDept = $chkW['dept_ID'];
}
}
if ($prevDept != $update->dept()) {
$history->reset();
$history->upc($upc);
$history->storeID($update->storeID());
$history->modified($update->modified());
$history->dept_ID($update->dept());
$history->uid($update->user());
$history->prodUpdateID($update->prodUpdateID());
$this->cronMsg('Add dept change #' . $update->prodUpdateID(), FannieLogger::INFO);
$history->save();
}
$prevDept = $update->dept();
}
}
$batchQ = 'SELECT upc FROM batchList as l LEFT JOIN batches AS b
ON l.batchID=b.batchID WHERE b.discounttype=0
AND ' . $sql->datediff($sql->now(), 'b.startDate') . ' = 0';
$batchR = $sql->query($batchQ);
$prodUpdate = new ProdUpdateModel($sql);
while ($batchW = $sql->fetch_row($batchR)) {
$upcs = array();
$upc = $batchW['upc'];
// unpack likecodes to UPCs
if (substr($upc, 0, 2) == 'LC') {
$likeR = $sql->execute($likeP, array(substr($upc, 2)));
while ($likeW = $sql->fetch_row($likeR)) {
$upcs[] = $likeW['upc'];
}
} else {
$upcs[] = $upc;
}
foreach ($upcs as $item) {
$prodUpdate->reset();
$prodUpdate->upc($item);
$logged = $prodUpdate->logUpdate(ProdUpdateModel::UPDATE_PC_BATCH, 1001);
if (!$logged) {
$success = false;
}
}
}
if ($success) {
echo cron_msg("Changes logged in prodUpdate");
} else {
echo cron_msg("Error logging changes");
}
