<?php
/*******************************************************************************

    Copyright 2014 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

*********************************************************************************/

class SalesBatchTask extends FannieTask
{

    public $name = 'Sales Batch Task';

    public $description = 'Apply sales batches. Puts items on sale
    if they are in a current batch and also takes items off sale
    if they are not in a current batch.
    Replaces the old nightly.batch.php script.';

    public $default_schedule = array(
        'min' => 10,
        'hour' => 2,
        'day' => '*',
        'month' => '*',
        'weekday' => '*',
    );

    private function getSaleItems($dbc)
    {
        $b_def = $dbc->tableDefinition('batches');
        $t_def = $dbc->tableDefinition('batchList');
        $y_def = $dbc->tableDefinition('batchType');

        $query = 'SELECT l.upc, 
                    l.batchID, 
                    l.pricemethod, 
                    l.salePrice, 
                    l.groupSalePrice,
                    l.quantity,
                    b.startDate, 
                    b.endDate, 
                    b.discounttype
                    ' . (isset($b_def['transLimit']) ? ',b.transLimit' : ',0 AS transLimit') . '
                    ' . (isset($y_def['exitInventory']) ? ',t.exitInventory' : ',0 AS exitInventory') . '
                  FROM batches AS b
                    INNER JOIN batchList AS l ON b.batchID = l.batchID
                    LEFT JOIN batchType AS t ON b.batchType = t.batchTypeID
                  WHERE b.discounttype > 0
                    AND b.startDate <= ?
                    AND b.endDate >= ?
                  ORDER BY l.upc,
                    l.salePrice DESC';
        if (!isset($t_def['groupSalePrice'])) {
            $query = str_replace('l.groupSalePrice', 'NULL AS groupSalePrice', $query);
        }
        /**
          In HQ mode, join on junction table to get UPC+storeID rows
          when applying sale pricing
        */
        if ($this->config->get('STORE_MODE') === 'HQ') {
            $query = str_replace('WHERE', ' LEFT JOIN StoreBatchMap AS s ON b.batchID=s.batchID WHERE ', $query);
            $query = str_replace('SELECT', 'SELECT s.storeID,', $query);
        }

        return $query;
    }

    public function run()
    {
        global $FANNIE_OP_DB;
        $dbc = FannieDB::get($FANNIE_OP_DB);
        $now = date('Y-m-d 00:00:00');
        $sale_upcs = array();
        $disco_items = array();

        // ensure likecode items are mixmatch-able
        $this->setLikeCodeMixMatch($dbc);
        $this->setLikeCodeMixMatchOverrides($dbc);
        $this->setBogoMixMatch($dbc);

        $likeP = $dbc->prepare('SELECT u.upc 
                                FROM upcLike AS u
                                    INNER JOIN products AS p ON u.upc=p.upc
                                WHERE likeCode=?');
        $product = new ProductsModel($dbc);
        $isPartial = $dbc->prepare('SELECT batchID FROM PartialBatches WHERE batchID=?');
        $applied = array();
        $applyP = $dbc->prepare("UPDATE batches SET applied=1 WHERE batchID=?");

        // lookup current batches
        $prep = $dbc->prepare($this->getSaleItems($dbc));
        $result = $dbc->execute($prep, array($now, $now));
        $changedUPCs = array();
        while ($row = $dbc->fetchRow($result)) {
            // ignore partials. they have a separate task
            if ($dbc->getValue($isPartial, $row['batchID'])) {
                continue;
            }
            if (!isset($applied[$row['batchID']])) {
                $dbc->execute($applyP, array($row['batchID']));
                $applied[$row['batchID']] = true;
            }
            // all items affected by this bathcList record
            // could be more than one in the case of likecodes
            $item_upcs = array();

            // use products column names for readability below
            $special_price = $row['salePrice'];
            $specialpricemethod = $row['pricemethod'];
            if ($row['groupSalePrice'] != null) {
                $specialgroupprice = $row['groupSalePrice'];
            } else {
                $specialgroupprice = abs($row['salePrice']);
            }
            $specialquantity = $row['quantity'];
            $special_limit = $row['transLimit'];
            $start_date = $row['startDate'];
            $end_date = $row['endDate'];
            $discounttype = $row['discounttype'];
            $batchID = $row['batchID'];

            // pricemethod 3 and 4 (AB pricing, typically)
            // has some overly complicated rules
            $mixmatch = false;
            if ($specialpricemethod == 3 || $specialpricemethod==4) {
                if ($special_price >= 0) {
                    $mixmatch = $row['batchID'];
                } else {
                    $mixmatch = -1 * $row['batchID'];
                }
            }

            // unpack likecodes, if needed
            if (substr($row['upc'], 0, 2) == 'LC') {
                $likeCode = substr($row['upc'], 2);
                $likeR = $dbc->execute($likeP, array($likeCode));
                while ($likeW = $dbc->fetch_row($likeR)) {
                    $item_upcs[] = $likeW['upc'];
                    if ($mixmatch !== false) {
                        $mixmatch = $likeCode + 500;
                    }
                }
            } else {
                $item_upcs[] = $row['upc'];
            }

            // check each item to see if it is on
            // sale with the correct parameters
            foreach ($item_upcs as $upc) {
                $product->reset();
                $product->upc($upc);
                $this->cronMsg('Checking item ' . $upc, FannieLogger::INFO);
                /**
                  Transistion mechanism. A batch that is set to apply to
                  zero stores really should apply to zero stores. For now
                  it fails over to using the local store's ID
                */
                if ($this->config->get('STORE_MODE') === 'HQ') {
                    $storeID = $row['storeID'];
                    if ($storeID == null) {
                        $storeID = $this->config->get('STORE_ID');
                    }
                    $product->store_id($storeID);
                }
                if (!$product->load()) {
                    $this->cronMsg("\tError: item does not exist in products", FannieLogger::NOTICE);
                    continue;
                }
                // list of UPCs that should be on sale
                $sale_upcs = $this->addSaleUPC($sale_upcs, $upc, $product->store_id());
                if ($row['exitInventory'] && $row['storeID']) {
                    if (!isset($disco_items[$row['storeID']])) {
                        $disco_items[$row['storeID']] = array();
                    }
                    $disco_items[$row['storeID']][] = $upc;
                }

                // for qtyEnforcedGroupPM the salePrice is the whole group price
                if ($specialpricemethod == 2 && $special_price == $specialgroupprice) {
                    $special_price = $product->normal_price();
                }

                $changed = false;
                if ($product->special_price() != $special_price) {
                    $changed = true;
                    $product->special_price($special_price);
                }
                if ($product->specialpricemethod() != $specialpricemethod) {
                    $changed = true;
                    $product->specialpricemethod($specialpricemethod);
                }
                if ($product->specialgroupprice() != $specialgroupprice) {
                    $changed = true;
                    $product->specialgroupprice($specialgroupprice);
                }
                if ($product->specialquantity() != $specialquantity) {
                    $changed = true;
                    $product->specialquantity($specialquantity);
                }
                if ($product->special_limit() != $special_limit) {
                    $changed = true;
                    $product->special_limit($special_limit);
                }
                if ($product->start_date() != $start_date) {
                    $changed = true;
                    $product->start_date($start_date);
                }
                if ($product->end_date() != $end_date) {
                    $changed = true;
                    $product->end_date($end_date);
                }
                if ($product->discounttype() != $discounttype) {
                    $changed = true;
                    $product->discounttype($discounttype);
                }
                if ($mixmatch !== false && $product->mixmatchcode() != $mixmatch) {
                    $changed = true;
                    $product->mixmatchcode($mixmatch);
                }
                if ($product->batchID() != $batchID) {
                    $changed = true;
                    $product->batchID($batchID);
                }

                if ($changed) {
                    $product->save();
                    $this->cronMsg("\tUpdated item", FannieLogger::INFO);
                    $changedUPCs[] = $upc;
                }

                if ($this->test_mode) {
                    break;
                }
            } // end loop on batchList record items

            if ($this->test_mode) {
                break;
            }
        } // end loop on batchList records

        // No sale items; need a filler value for
        // the query below
        if (count($sale_upcs) == 0) {
            $this->cronMsg('Notice: nothing is currently on sale', FannieLogger::WARNING);
            $sale_upcs = array(1 => array('notValidUPC'));
        }

        // now look for anything on sale that should not be
        // and take those items off sale
        $notOnSale = $this->notOnSaleItems($dbc, $sale_upcs);
        foreach ($notOnSale as $lookupW) {
            $this->cronMsg('Taking ' . $lookupW['upc']  . ':' . $lookupW['store_id'] . ' off sale', FannieLogger::INFO);

            $product->reset();
            if ($this->config->get('STORE_MODE') === 'HQ') {
                $product->store_id($lookupW['store_id']);
            }
            $product->upc($lookupW['upc']);
            $product->discounttype(0);
            $product->special_price(0);
            $product->specialgroupprice(0);
            $product->specialquantity(0);
            $product->special_limit(0);
            $product->start_date('1900-01-01');
            $product->end_date('1900-01-01');
            $product->batchID(0);
            $product->save();
            $changedUPCs[] = $lookupW['upc'];

            if ($this->test_mode) {
                break;
            }
        }

        $queue = new COREPOS\Fannie\API\jobs\QueueManager();
        $queue->add(array(
            'class' => 'COREPOS\\Fannie\\API\\jobs\\SyncItem',
            'data' => array(
                'upc' => $changedUPCs,
            ),
        ));

        $this->discoItems($dbc, $disco_items);
    }

    private function discoItems($dbc, $items)
    {
        foreach ($items as $storeID => $upcs) {
            $this->cronMsg('Discoing ' . count($upcs) . ' for store #' . $storeID, FannieLogger::INFO);
            $args = array( (1 << (20 - 1)) );
            list($inStr, $args) = $dbc->safeInClause($upcs, $args);
            $args[] = $storeID;
            $prep = $dbc->prepare("
                UPDATE products AS p
                SET numflag = numflag | ?
                WHERE p.upc IN ({$inStr})
                    AND p.store_id=?");
            $res = $dbc->execute($prep, $args);

            list($inStr2, $args2) = $dbc->safeInClause($upcs);
            $args2[] = $storeID;
            $prep = $dbc->prepare("
                UPDATE InventoryCounts AS i
                SET i.par=0
                WHERE i.mostRecent=1
                    AND i.upc IN ({$inStr2})
                    AND i.storeID=?");
            $res = $dbc->execute($prep, $args2);
        }
    }

    private function setLikeCodeMixMatch($dbc)
    {
        if ($dbc->dbmsName() == 'mssql') {
            $dbc->query("UPDATE products
                SET mixmatchcode=convert(varchar,u.likecode+500)
                FROM 
                products AS p
                INNER JOIN upcLike AS u
                ON p.upc=u.upc");
        } elseif ($dbc->dbmsName() == 'postgres9') {
            $dbc->query("
                UPDATE products AS p
                SET mixmatchcode = " . $dbc->convert('u.likeCode+500', 'CHAR') . "
                FROM upcLike AS u
                WHERE p.upc=u.upc");
        } else {
            $dbc->query("UPDATE products AS p
                INNER JOIN upcLike AS u ON p.upc=u.upc
                SET p.mixmatchcode=convert(u.likeCode+500,char)");
        }
    }

    private function setLikeCodeMixMatchOverrides($dbc)
    {
        if ($dbc->tableExists('LikeCodeMixMatchOverrides')) {
            if ($dbc->dbmsName() == 'mssql') {
                $dbc->query("UPDATE products
                    SET mixmatchcode=u.mixMatchCode
                    FROM 
                    products AS p
                    INNER JOIN LikeCodeMixMatchOverrides AS u
                    ON p.upc=u.upc");
            } elseif ($dbc->dbmsName() == 'postgres9') {
                $dbc->query("
                    UPDATE products AS p
                    SET mixmatchcode = u.mixMatchCode
                    FROM LikeCodeMixMatchOverrides AS u
                    WHERE p.upc=u.upc");
            } else {
                $dbc->query("UPDATE products AS p
                    INNER JOIN LikeCodeMixMatchOverrides AS u ON p.upc=u.upc
                    SET p.mixmatchcode=u.mixMatchCode");
            }
        }
    }

    private function setBogoMixMatch($dbc)
    {
        if ($dbc->tableExists('CoopDealsBogos')) {
            if ($dbc->dbmsName() == 'mssql') {
                $dbc->query("UPDATE products
                    SET mixmatchcode=u.mixMatch
                    FROM 
                    products AS p
                    INNER JOIN CoopDealsBogos AS u
                    ON p.upc=u.upc");
            } else {
                $res = $dbc->query('SELECT upc, mixMatch FROM CoopDealsBogos ORDER BY upc, mixMatch');
                $upP = $dbc->prepare("UPDATE products SET mixmatchcode=? WHERE upc=?");
                while ($row = $dbc->fetchRow($res)) {
                    $dbc->execute($upP, array($row['mixMatch'], $row['upc']));
                }
                /*
                $dbc->query("UPDATE products AS p
                    INNER JOIN CoopDealsBogos AS u ON p.upc=u.upc
                    SET p.mixmatchcode=u.mixMatch");
                 */
            }
        }
    }

    /**
      Find all items that are on sale but not attached
      to a current batch. $sale_upcs contains lists of
      UPCs that are on sale at each store
    */
    private function notOnSaleItems($dbc, $sale_upcs)
    {
        $lookupBase = 'SELECT p.upc,p.store_id
                    FROM products AS p
                    WHERE (
                            p.discounttype <> 0
                            OR p.special_price <> 0
                            OR p.specialpricemethod <> 0
                            OR p.specialgroupprice <> 0
                            OR p.specialquantity <> 0
                        ) ';
        $ret = array();
        foreach ($sale_upcs as $storeID => $items) {
            $args = array($storeID);    
            list($inStr, $args) = $dbc->safeInClause($items, $args);
            $lookupQ = $lookupBase . ' AND p.store_id=? AND p.upc NOT IN (' . $inStr . ')';
            $lookupP = $dbc->prepare($lookupQ);
            $lookupR = $dbc->execute($lookupP, $args);
            while ($lookupW = $dbc->fetchRow($lookupR)) {
                $ret[] = $lookupW;
            }
        }

        return $ret;
    }

    /**
      Build tiered array of sale UPCs by store
      $sale_upcs
        => storeID 1
            => upc1, upc2, upc3, etc
        => storeID 2
            => upc1, upc2, upc3, etc
        (etc)
    */
    private function addSaleUPC($sale_upcs, $upc, $storeID)
    {
        if (!isset($sale_upcs[$storeID])) {
            $sale_upcs[$storeID] = array();
        }
        $sale_upcs[$storeID][] = $upc;

        return $sale_upcs;
    }
}

