fannie/batches/CAP/CoopDealsUploadPage.php
<?php
/*******************************************************************************
Copyright 2013 Whole Foods Co-op
This file is part of CORE-POS.
IT CORE 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.
IT CORE 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
*********************************************************************************/
include(dirname(__FILE__) . '/../../config.php');
if (!class_exists('FannieAPI')) {
include_once(__DIR__ . '/../../classlib2.0/FannieAPI.php');
}
class CoopDealsUploadPage extends \COREPOS\Fannie\API\FannieUploadPage
{
public $title = "Fannie - Co+op Deals sales";
public $header = "Upload Co+op Deals file";
public $description = '[Co+op Deals Import] loads sales information from Co+op Deals pricing spreadsheets.
This data can be used to create sales batches.';
public $themed = true;
protected $preview_opts = array(
'upc' => array(
'display_name' => 'UPC',
'default' => 8,
'required' => true
),
'price' => array(
'display_name' => 'Sale Price',
'default' => 26,
'required' => true
),
'abt' => array(
'display_name' => 'A/B/TPR',
'default' => 6,
'required' => true
),
'sku' => array(
'display_name' => 'SKU',
'default' => 9,
),
'mult' => array(
'display_name' => 'Line Notes',
'default' => 15,
),
'promoDiscount' => array(
'display_name' => 'Promo Discount',
'default' => 18,
),
'cost' => array(
'display_name' => 'Promo Cost',
'default' => 25,
),
'promoPrice' => array(
'display_name' => 'Promo Price',
'default' => 28,
),
);
private function setupTables($dbc)
{
if ($dbc->tableExists('tempCapPrices')){
$drop = $dbc->prepare("DROP TABLE tempCapPrices");
$dbc->execute($drop);
}
if (!$dbc->tableExists('CoopDealsItems')) {
$cdi = new CoopDealsItemsModel($dbc);
$cdi->create();
}
}
private function prepStatements($dbc)
{
$upcP = $dbc->prepare('SELECT upc FROM products WHERE upc=? AND inUse=1');
$priceP = $dbc->prepare("SELECT normal_price FROM products WHERE upc=?");
$skuP = $dbc->prepare('
SELECT s.upc
FROM VendorAliases AS s
INNER JOIN products AS p ON s.vendorID=p.default_vendor_id AND s.upc=p.upc
WHERE s.sku=?'
);
$insP = $dbc->prepare('
INSERT INTO CoopDealsItems
(dealSet, upc, price, abtpr, multiplier, promoDiscount, skuMatch, cost)
VALUES
(?, ?, ?, ?, ?, ?, ?, ?)');
return array($upcP, $skuP, $insP, $priceP);
}
private function checkSku($dbc, $upc, $sku, $skuP)
{
$look2 = $dbc->execute($skuP, array($sku));
if ($dbc->num_rows($look2)) {
$row = $dbc->fetch_row($look2);
return $row['upc'];
}
$sku = str_pad($sku, 7, '0', STR_PAD_LEFT);
$look3 = $dbc->execute($skuP, array($sku));
if ($dbc->num_rows($look3)) {
$row = $dbc->fetch_row($look3);
return $row['upc'];
}
return $upc;
}
private $scaleLinkedItems = null;
private function checkScaleItem($dbc, $upc)
{
if ($this->scaleLinkedItems === null) {
$res = $dbc->query("SELECT plu, linkedPLU from scaleItems WHERE linkedPLU IS NOT NULL AND linkedPLU <> ''");
$items = array();
while ($row = $dbc->fetchRow($res)) {
$items[$row['plu']] = $row['linkedPLU'];
}
$this->scaleLinkedItems = $items;
}
return isset($this->scaleLinkedItems[$upc]) ? $this->scaleLinkedItems[$upc] : false;
}
private function dealTypes($type)
{
$abt = array();
if (strstr($type,"A")) {
$abt[] = "A";
}
if (strstr($type,"B")) {
$abt[] = "B";
}
if (strstr($type,"TPR")) {
$abt[] = "TPR";
}
return $abt;
}
function process_file($linedata, $indexes)
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$this->setupTables($dbc);
$bogoTable = $dbc->tableExists('CoopDealsBogos');
$bogoModel = new CoopDealsBogosModel($dbc);
$month = FormLib::get('deal-month', 'not specified');
$delP = $dbc->prepare('DELETE FROM CoopDealsItems WHERE dealSet=?');
$dbc->execute($delP, array($month));
list($upcP, $skuP, $insP, $priceP) = $this->prepStatements($dbc);
$prefix = 0;
$prefixP = $dbc->prepare("SELECT mixMatch FROM CoopDealsBogos ORDER BY mixMatch DESC");
$last = $dbc->getValue($prefixP, array());
if ($last) {
list($last,) = explode('-', $last);
$prefix = ((int)$last) + 1;
}
$prefix = str_pad($prefix, 5, '0', STR_PAD_LEFT);
$rm_checks = (FormLib::get_form_value('rm_cds') != '') ? True : False;
$col_max = max($indexes);
$dbc->startTransaction();
foreach ($linedata as $data) {
if (!is_array($data)) continue;
if (count($data) < $col_max) continue;
$upc = str_replace("-","",$data[$indexes['upc']]);
$upc = str_replace(" ","",$upc);
if ($rm_checks)
$upc = substr($upc,0,strlen($upc)-1);
$upc = BarcodeLib::padUPC($upc);
$isSKU = 0;
$lookup = $dbc->execute($upcP, array($upc));
if ($dbc->num_rows($lookup) == 0) {
$sku = $this->checkSku($dbc, $upc, $data[$indexes['sku']], $skuP);
if ($sku != $upc) {
$upc = $sku;
$isSKU = 1;
}
}
$mult = 1;
if ($indexes['mult'] !== false) {
$line_notes = $data[$indexes['mult']];
if (preg_match('/(\d+)\/\$(\d+)/', $line_notes, $matches)) {
$mult = $matches[1];
}
}
$price = trim($data[$indexes['price']],"\$");
$cost = 0;
if ($indexes['cost'] && isset($data[$indexes['cost']])) {
$cost = trim($data[$indexes['cost']],"\$");
}
if ($indexes['promoPrice'] && isset($data[$indexes['promoPrice']])) {
$promoPrice = strtoupper($data[$indexes['promoPrice']]);
if (substr($promoPrice, 0, 4) == 'BOGO') {
$mult = -3;
$price = $dbc->getValue($priceP, array($upc));
if ($bogoTable) {
$bogoModel->upc($upc);
$bogoModel->mixMatch($prefix . '-' . $promoPrice);
$bogoModel->save();
}
}
}
$promo = $data[$indexes['promoDiscount']];
foreach ($this->dealTypes($data[$indexes['abt']]) as $type){
$dbc->execute($insP,array($month,$upc,$price,$type,$mult,$promo,$isSKU,$cost));
}
$linked = $this->checkScaleItem($dbc, $upc);
if ($linked) {
foreach ($this->dealTypes($data[$indexes['abt']]) as $type){
$dbc->execute($insP,array($month,$linked,$price,$type,$mult,$promo,$isSKU,$cost));
}
}
}
$dbc->commitTransaction();
return true;
}
function form_content()
{
return '<div class="well">Upload a CSV or Excel (XLS, not XLSX) file containing Co+op Deals
Sale information. The file needs to contain UPCs, sale prices,
and a column indicating A, B, or TPR (or some combination of the
three).</div>';
}
function preview_content()
{
return '
<label>Month</label><input type="text" name="deal-month" required />
<label><input type="checkbox" name="rm_cds" checked /> Remove check digits</label>
';
}
function results_content()
{
$ret = "<p>Sales data import complete</p>";
$ret .= "<p><a href=\"CoopDealsReviewPage.php\">Review data & set up sales</a></p>";
return $ret;
}
public function helpContent()
{
return '<p>Default column selections correspond to the
tab/worksheet that lists all A, B, and TPR items</p>'
. parent::helpContent();
}
public function unitTest($phpunit)
{
$phpunit->assertNotEquals(0, strlen($this->results_content()));
$indexes = array('upc'=>0, 'price'=>1, 'abt'=>2, 'sku'=>3, 'mult'=>4, 'promoDiscount'=>5);
$data = array('4011', 0.99, 'ABTPR', '4011', '2/$2', '20%');
for ($i=0; $i<14; $i++) {
$data[] = 0;
}
$phpunit->assertEquals(true, $this->process_file(array($data), $indexes));
}
}
FannieDispatch::conditionalExec();