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