/* 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);
$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\n special_price=0,\n specialpricemethod=0,\n specialquantity=0,\n specialgroupprice=0,\n " . ($has_limit ? 'special_limit=0,' : '') . "\n discounttype=0,\n start_date='1900-01-01',\n end_date='1900-01-01'");
// resale things that should be on sale
if (strstr($FANNIE_SERVER_DBMS, "MYSQL")) {
$sql->query("UPDATE products AS p\n INNER JOIN upcLike AS u ON p.upc=u.upc\n SET p.mixmatchcode=convert(u.likeCode+500,char)");
$sql->query("UPDATE products AS p\n LEFT JOIN batchList AS l ON p.upc=l.upc\n LEFT JOIN batches AS b ON l.batchID=b.batchID\n SET\n p.special_price = l.salePrice,\n p.specialpricemethod = l.pricemethod,\n p.specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,\n p.specialquantity = l.quantity,\n " . ($has_limit ? 'p.special_limit=b.transLimit,' : '') . "\n p.start_date = b.startDate,\n p.end_date = b.endDate,\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 ELSE p.mixmatchcode \n END \n WHERE l.upc NOT LIKE 'LC%'\n AND CURDATE() >= b.startDate AND CURDATE() <= b.endDate\n AND b.discounttype <> 0");
$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 l.batchID=b.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.specialquantity=l.quantity,\n p.specialpricemethod=l.pricemethod,\n " . ($has_limit ? 'p.special_limit=b.transLimit,' : '') . "\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 ELSE p.mixmatchcode \n END \n WHERE l.upc LIKE 'LC%'\n AND CURDATE() >= b.startDate AND CURDATE() <= b.endDate\n AND b.discounttype <> 0");
} else {
$sql->query("UPDATE products\n SET mixmatchcode=convert(varchar,u.likecode+500)\n FROM \n products AS p\n INNER JOIN upcLike AS u\n ON p.upc=u.upc");
$sql->query("UPDATE products \n SET\n special_price = l.salePrice,\n specialpricemethod = l.pricemethod,\n specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,\n specialquantity = l.quantity,\n " . ($has_limit ? 'special_limit=b.transLimit,' : '') . "\n start_date = b.startDate,\n end_date = b.endDate,\n discounttype = b.discountType,\n mixmatchcode = CASE \n WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(varchar,b.batchID)\n WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(varchar,-1*b.batchID)\n ELSE p.mixmatchcode \n END\n FROM products AS p\n LEFT JOIN batchList AS l ON p.upc=l.upc\n LEFT JOIN batches AS b ON l.batchID=b.batchID\n WHERE l.upc NOT LIKE 'LC%'\n AND b.discountType <> 0");
$sql->query("UPDATE products SET special_price = l.salePrice,\n end_date = b.enddate,start_date=b.startdate,\n specialgroupprice=CASE WHEN l.salePrice < 0 THEN -1*l.salePrice ELSE l.salePrice END,\n specialquantity=l.quantity,\n specialpricemethod=l.pricemethod,\n " . ($has_limit ? 'special_limit=b.transLimit,' : '') . "\n discounttype = b.discounttype,\n mixmatchcode = CASE \n WHEN l.pricemethod IN (3,4) AND l.salePrice >= 0 THEN convert(varchar,b.batchID)\n WHEN l.pricemethod IN (3,4) AND l.salePrice < 0 THEN convert(varchar,-1*b.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 l.batchID=b.batchID\n WHERE l.upc LIKE 'LC%'\n AND b.discountType <> 0");
}
