fannie/cron/nightly.batch.php
<?php
/*******************************************************************************
Copyright 2009 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
*********************************************************************************/
/* HELP
nightly.batch.php
This script sets up sales. First it takes all items
off sale, then it applies sales batches with a
start and end date including the current day.
This script should run daily. Because batch start
and end dates are inclusive, scheduling the script
after midnight will give the most sensible results.
This script does not update the lanes, therefore
it should be run before lane syncing.
*/
/* 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(__DIR__ . '/../classlib2.0/FannieAPI.php');
}
if (!function_exists('cron_msg')) {
include(__DIR__ . '/../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);
$b_def = $sql->tableDefinition('batches');
$p_def = $sql->tableDefinition('products');
$has_limit = (isset($b_def['transLimit']) && isset($p_def['special_limit'])) ? true : false;
// unsale everything
$sql->query("UPDATE products SET
special_price=0,
specialpricemethod=0,
specialquantity=0,
specialgroupprice=0,
" . ($has_limit ? 'special_limit=0,' : '') . "
discounttype=0,
start_date='1900-01-01',
end_date='1900-01-01'");
// resale things that should be on sale
if (strstr($FANNIE_SERVER_DBMS,"MYSQL")){
$sql->query("UPDATE products AS p
INNER JOIN upcLike AS u ON p.upc=u.upc
SET p.mixmatchcode=convert(u.likeCode+500,char)");
$sql->query("UPDATE products AS p
LEFT JOIN batchList AS l ON p.upc=l.upc
LEFT JOIN batches AS b ON l.batchID=b.batchID
SET
p.special_price = l.salePrice,
p.specialpricemethod = l.pricemethod,
p.specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,
p.specialquantity = l.quantity,
" . ($has_limit ? 'p.special_limit=b.transLimit,' : '') . "
p.start_date = b.startDate,
p.end_date = b.endDate,
p.discounttype = b.discounttype,
p.mixmatchcode = CASE
WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(l.batchID,char)
WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(-1*l.batchID,char)
ELSE p.mixmatchcode
END
WHERE l.upc NOT LIKE 'LC%'
AND CURDATE() >= b.startDate AND CURDATE() <= b.endDate
AND b.discounttype > 0");
$sql->query("UPDATE products AS p LEFT JOIN
upcLike AS v ON v.upc=p.upc LEFT JOIN
batchList AS l ON l.upc=concat('LC',convert(v.likeCode,char))
LEFT JOIN batches AS b ON l.batchID=b.batchID
SET p.special_price = l.salePrice,
p.end_date = b.endDate,p.start_date=b.startDate,
p.specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,
p.specialquantity=l.quantity,
p.specialpricemethod=l.pricemethod,
" . ($has_limit ? 'p.special_limit=b.transLimit,' : '') . "
p.discounttype = b.discounttype,
p.mixmatchcode = CASE
WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(l.batchID,char)
WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(-1*l.batchID,char)
ELSE p.mixmatchcode
END
WHERE l.upc LIKE 'LC%'
AND CURDATE() >= b.startDate AND CURDATE() <= b.endDate
AND b.discounttype > 0");
}
else {
$sql->query("UPDATE products
SET mixmatchcode=convert(varchar,u.likecode+500)
FROM
products AS p
INNER JOIN upcLike AS u
ON p.upc=u.upc");
$sql->query("UPDATE products
SET
special_price = l.salePrice,
specialpricemethod = l.pricemethod,
specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,
specialquantity = l.quantity,
" . ($has_limit ? 'special_limit=b.transLimit,' : '') . "
start_date = b.startDate,
end_date = b.endDate,
discounttype = b.discountType,
mixmatchcode = CASE
WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(varchar,b.batchID)
WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(varchar,-1*b.batchID)
ELSE p.mixmatchcode
END
FROM products AS p
LEFT JOIN batchList AS l ON p.upc=l.upc
LEFT JOIN batches AS b ON l.batchID=b.batchID
WHERE l.upc NOT LIKE 'LC%'
AND b.discountType > 0");
$sql->query("UPDATE products SET special_price = l.salePrice,
end_date = b.enddate,start_date=b.startdate,
specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,
specialquantity=l.quantity,
specialpricemethod=l.pricemethod,
" . ($has_limit ? 'special_limit=b.transLimit,' : '') . "
discounttype = b.discounttype,
mixmatchcode = CASE
WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(varchar,b.batchID)
WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(varchar,-1*b.batchID)
ELSE p.mixmatchcode
END
FROM products AS p LEFT JOIN
upcLike AS v ON v.upc=p.upc LEFT JOIN
batchList AS l ON l.upc='LC'+convert(varchar,v.likecode)
LEFT JOIN batches AS b ON l.batchID=b.batchID
WHERE l.upc LIKE 'LC%'
AND b.discountType > 0");
}