fannie/cron/tasks/SpecialOrdersTask.php
<?php
/*******************************************************************************
Copyright 2011 Whole Foods Co-op
This file is part of CORE-POS.
CORE-POS is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
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
*********************************************************************************/
if (!class_exists('SoPoBridge')) {
include(__DIR__ . '/../../ordering/SoPoBridge.php');
}
class SpecialOrdersTask extends FannieTask
{
public $name = 'Special Orders Task';
public $description = 'Moves order items that
have been sold from pending order list to
completed orders. Also auto-closes orders older
than 90 days.
Replaces the old nightly.specialorder.php script as
well as homeless.specialorder.php.';
public $default_schedule = array(
'min' => 0,
'hour' => 3,
'day' => '*',
'month' => '*',
'weekday' => '*',
);
private function cleanFileCache()
{
$cachepath = sys_get_temp_dir()."/ordercache/";
if (!is_dir($cachepath) && !mkdir($cachepath)) {
return false;
}
$dir = opendir($cachepath);
while (($file = readdir($dir)) !== false) {
if ($file == "." || $file == "..") continue;
if (!is_file($cachepath.$file)) continue;
unlink($cachepath.$file);
}
closedir($dir);
}
private function getOldCalledWaiting($sql)
{
$subquery = "select p.order_id from PendingSpecialOrder as p
left join SpecialOrders as s
on p.order_id=s.specialOrderID
where p.trans_id=0 and s.statusFlag=1
and ".$sql->datediff($sql->now(),'datetime')." > 30";
$cwIDs = "(";
$res = $sql->query($subquery);
while ($row = $sql->fetchRow($res)) {
$cwIDs .= $row['order_id'].",";
}
$cwIDs = rtrim($cwIDs,",").")";
return $cwIDs;
}
private function get90DaysOld($sql)
{
$subquery = "select p.order_id from PendingSpecialOrder as p
left join SpecialOrders as s
on p.order_id=s.specialOrderID
where p.trans_id=0
and ".$sql->datediff($sql->now(),'datetime')." > 90";
$allIDs = "(";
$res = $sql->query($subquery);
while ($row = $sql->fetchRow($res)) {
$allIDs .= $row['order_id'].",";
}
$allIDs = rtrim($allIDs,",").")";
return $allIDs;
}
private function closeOrders($sql, $cwIDs, $reason)
{
// transfer to completed orders
$copyQ = "INSERT INTO CompleteSpecialOrder
SELECT p.* FROM PendingSpecialOrder AS p
WHERE p.order_id IN $cwIDs";
$bridge = new SoPoBridge($sql, FannieConfig::factory());
$itemQ = "SELECT s.storeID, p.order_id, p.trans_id
FROM PendingSpecialOrder AS p
LEFT JOIN SpecialOrders AS s ON p.order_id=s.specialOrderID
WHERE p.trans_id > 0
AND p.order_id IN {$cwIDs}";
$itemR = $sql->query($itemQ);
while ($itemW = $sql->fetchRow($itemR)) {
$bridge->removeItemFromPurchaseOrder($itemW['order_id'], $itemW['trans_id'], $itemW['storeID']);
}
// make note in history table
$historyQ = "INSERT INTO SpecialOrderHistory
(order_id, entry_date, entry_type, entry_value)
SELECT p.order_id,
" . $sql->now() . ",
'AUTOCLOSE',
'$reason'
FROM PendingSpecialOrder AS p
WHERE p.order_id IN $cwIDs
GROUP BY p.order_id";
$sql->query($historyQ);
// clear from pending
$sql->query($copyQ);
$delQ = "DELETE FROM PendingSpecialOrder
WHERE order_id IN $cwIDs";
$sql->query($delQ);
}
private function cleanEmptyOrders($sql)
{
$cleanupQ = sprintf("
SELECT p.order_id
FROM PendingSpecialOrder AS p
LEFT JOIN SpecialOrders AS o ON p.order_id=o.specialOrderID
WHERE p.deleted=0 AND (
(
o.specialOrderID IS NULL
OR %s(o.notes)=0
)
OR p.order_id IN (
SELECT order_id FROM CompleteSpecialOrder
WHERE trans_id=0
GROUP BY order_id
)
)
GROUP BY p.order_id
HAVING MAX(trans_id)=0",
($sql->dbmsName()==="mssql" ? 'datalength' : 'length'));
$cleanupR = $sql->query($cleanupQ);
$empty = "(";
$clean=0;
while ($row = $sql->fetchRow($cleanupR)) {
$empty .= $row['order_id'].",";
$clean++;
}
$empty = rtrim($empty,",").")";
$this->cronMsg("Finishing $clean orders");
if (strlen($empty) > 2){
//echo "Empties: ".$empty."\n";
$delQ = "DELETE FROM PendingSpecialOrder WHERE order_id IN $empty AND trans_id=0";
$delR = $sql->query($delQ);
}
}
private function homelessOrderNotices($sql)
{
$OP = $this->config->get('OP_DB') . $sql->sep();
$query = "
select s.order_id,description,datetime,
case when c.lastName ='' then b.LastName else c.lastName END as name
from PendingSpecialOrder
as s left join SpecialOrders as c on s.order_id=c.specialOrderID
left join {$OP}custdata as b on s.card_no=b.CardNo and s.voided=b.personNum
where s.order_id in (
select p.order_id from PendingSpecialOrder as p
left join SpecialOrders as n
on p.order_id=n.specialOrderID
where notes LIKE ''
group by p.order_id
having max(department)=0 and max(noteSuperID)=0
and max(trans_id) > 0
)
and trans_id > 0
order by datetime
";
$res = $sql->query($query);
if ($sql->numRows($res) > 0) {
$msg_body = "Homeless orders detected!\n\n";
while ($row = $sql->fetch_row($res)) {
$msg_body .= $row['datetime'].' - '.(empty($row['name'])?'(no name)':$row['name']).' - '.$row['description']."\n";
$msg_body .= "http://" . $this->config->get('HTTP_HOST') . '/' . $this->config->get('URL')
. "ordering/view.php?orderID=".$row['order_id']."\n\n";
}
$msg_body .= "These messages will be sent daily until orders get departments\n";
$msg_body .= "or orders are closed\n";
if ($this->config->get('COOP_ID') == 'WFC_Duluth') {
$to_addr = "buyers, michael";
$subject = "Incomplete SO(s)";
mail($to_addr,$subject,$msg_body);
} else {
$this->cronMsg($msg_body, FannieLogger::EMERGENCY);
}
}
}
private function deliOrderNotices($sql)
{
$yesterday = date('Y-m-d', strtotime('yesterday'));
$superID = 4;
$prep = $sql->prepare("SELECT order_id FROM PendingSpecialOrder WHERE trans_id=0
AND datetime BETWEEN ? AND ?");
$oIDs = $sql->getAllValues($prep, array($yesterday, $yesterday . ' 23:59:59'));
list($inStr, $args) = $sql->safeInClause($oIDs, array($superID));
$prep = $sql->prepare("SELECT specialOrderID FROM SpecialOrders
WHERE noteSuperID=? AND specialOrderID IN ({$inStr})");
$matches = $sql->getAllValues($prep, $args);
list($inStr, $args) = $sql->safeInClause($oIDs, array($superID));
$prep = $sql->prepare("SELECT order_id FROM PendingSpecialOrder AS p
INNER JOIN " . FannieDB::fqn('MasterSuperDepts', 'op') . " AS m ON p.department=m.dept_ID
WHERE m.superID = ? AND p.order_id IN ({$inStr})");
$matches2 = $sql->getAllValues($prep, $args);
$all = array_merge($matches, $matches2);
list($inStr, $args) = $sql->safeInClause($all);
$prep = $sql->prepare("SELECT specialOrderID, storeID FROM SpecialOrders
WHERE specialOrderID IN ({$inStr})
GROUP BY specialOrderID, storeID");
$orders = $sql->getAllRows($prep, $args);
foreach ($orders as $row) {
$addr = $row['storeID'] == 1 ? 'andy' : 'andy';
$msg_body = 'New Deli Special Order' . "\n\n";
$msg_body .= "http://" . $this->config->get('HTTP_HOST') . '/' . $this->config->get('URL')
. "ordering/view.php?orderID=".$row['specialOrderID']."\n\n";
$subject = "New Special Order";
mail($addr . '@wholefoods.coop',$subject,$msg_body);
}
}
public function run()
{
// clean cache
$this->cleanFileCache();
$sql = FannieDB::get($this->config->get('TRANS_DB'));
// auto-close called/waiting after 30 days
$cwIDs = $this->getOldCalledWaiting($sql);
if (strlen($cwIDs) > 2) {
$this->closeOrders($sql, $cwIDs, 'Call/Waiting 30');
}
// end auto-close
// auto-close all after 90 days
$allIDs = $this->get90DaysOld($sql);
if (strlen($allIDs) > 2){
$this->closeOrders($sql, $allIDs, '90 Days');
}
// end auto-close
$query = "SELECT CASE WHEN matched > 10 THEN matched ELSE mixMatch END as mixMatch,
CASE WHEN matched > 10 THEN mixMatch ELSE matched END AS matched,
MAX(datetime) as tdate,
MAX(emp_no) as emp,
MAX(register_no) AS reg,
MAX(trans_no) AS trans
FROM transarchive
WHERE charflag='SO'
AND emp_no <> 9999
AND register_no <> 99
AND trans_status NOT IN ('X','Z')
GROUP BY mixMatch,matched
HAVING sum(total) <> 0";
$result = $sql->query($query);
$checkP = $sql->prepare("SELECT order_id
FROM SpecialOrderHistory
WHERE order_id=?
AND entry_type='PURCHASED'
AND entry_date=?
AND entry_value=?");
$historyP = $sql->prepare("INSERT INTO SpecialOrderHistory
(order_id, entry_date, entry_type, entry_value)
VALUES
(?, ?, 'PURCHASED', ?)");
$order_ids = array();
$trans_ids = array();
while($row = $sql->fetch_row($result)) {
$order_ids[] = (int)$row['mixMatch'];
$trans_ids[] = (int)$row['matched'];
// log to history if entry doesn't already exist
$args = array(
(int)$row['mixMatch'],
$row['tdate'],
$row['emp'] . '-' . $row['reg'] . '-' . $row['trans'],
);
$checkR = $sql->execute($checkP, $args);
if ($checkR && $sql->num_rows($checkR) == 0) {
$sql->execute($historyP, $args);
}
}
$where = "( ";
for($i=0;$i<count($order_ids);$i++){
$where .= "(order_id=".$order_ids[$i]." AND trans_id=".$trans_ids[$i].") ";
if ($i < count($order_ids)-1)
$where .= " OR ";
}
$where .= ")";
if ($where === '( )') {
$where = '1=1';
}
$this->cronMsg("Found ".count($order_ids)." order items");
// copy item rows to completed and delete from pending
$copyQ = "INSERT INTO CompleteSpecialOrder SELECT * FROM PendingSpecialOrder WHERE $where";
$copyR = $sql->query($copyQ);
$delQ = "DELETE FROM PendingSpecialOrder WHERE $where";
$delR = $sql->query($delQ);
$chkQ = "SELECT * FROM PendingSpecialOrder WHERE $where";
$chkR = $sql->query($chkQ);
$this->cronMsg("Missed on ".$sql->num_rows($chkR)." items");
// the trans_id=0 line contains additional, non-item order info
// this determines where applicable trans_id=0 lines have already
// been copied to CompletedSpecialOrder
// this could occur if the order contained multiple items picked up
// over multiple days
$oids = "(";
foreach($order_ids as $o)
$oids .= $o.",";
$oids = rtrim($oids,",").")";
if ($oids === '()') {
$oids = '(-999)';
}
$checkQ = "SELECT order_id FROM CompleteSpecialOrder WHERE trans_id=0 AND order_id IN $oids";
$checkR = $sql->query($checkQ);
$done_oids = array();
while($row = $sql->fetch_row($checkR))
$done_oids[] = (int)$row['order_id'];
$todo = array_diff($order_ids,$done_oids);
$this->cronMsg("Found ".count($todo)." new order headers");
if (count($todo) > 0){
$copy_oids = "(";
foreach($todo as $o)
$copy_oids .= $o.",";
$copy_oids = rtrim($copy_oids,",").")";
//echo "Headers: ".$copy_oids."\n";
$copyQ = "INSERT INTO CompleteSpecialOrder SELECT * FROM PendingSpecialOrder
WHERE trans_id=0 AND order_id IN $copy_oids";
$copyR = $sql->query($copyQ);
}
// remove "empty" orders from pending
$this->cleanEmptyOrders($sql);
/**
Find active orders that do not belong to a department
and fire off emails until someone fixes it
*/
$this->homelessOrderNotices($sql);
if ($this->config->get('COOP_ID') == 'WFC_Duluth') {
$this->deliOrderNotices($sql);
}
}
}