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

    Copyright 2015 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 InventoryTask extends FannieTask
{

    public $name = 'Inventory (Approximate)';

    public $description = 'Tries to calculate how much inventory is present
    based on recent sales & orders.';

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

    private $store_id = 0;
    private $vendor_id = 0;

    public function setStoreID($s)
    {
        $this->store_id = $s;
    }

    public function setVendorID($v)
    {
        $this->vendor_id = $v;
    }

    /**
      Normal nightly behavior is to clear the whole cache and
      recalculate everything. But if store and vendor IDs have 
      been specified then only those applicable entries are
      cleared
    */
    private function clearEntries($dbc, $store_id, $vendor_id)
    {
        if ($store_id && $vendor_id) {
            $prep = $dbc->prepare('
                DELETE FROM InventoryCache
                WHERE storeID=?
                    AND upc IN (
                    SELECT upc FROM products WHERE store_id=? AND default_vendor_id=?
                    )');
            $dbc->execute($prep, array($store_id, $store_id, $vendor_id));
        } else {
            $dbc->query('TRUNCATE TABLE InventoryCache');
        }
    }

    /**
     * Find items with multiple most-recent counts
     * Mark all but the one with the newest timestamp
     * as not-most-recent
     */
    private function deepClean($dbc, $store_id, $vendor_id)
    {
        if ($store_id || $vendor_id) {
            return true;
        }

        $keepP = $dbc->prepare('SELECT inventoryCountID
            FROM InventoryCounts
            WHERE mostRecent=1
                AND upc=?
                AND storeID=?
            ORDER BY countDate DESC');
        $cleanP = $dbc->prepare('UPDATE InventoryCounts
            SET mostRecent=0
            WHERE upc=?
                AND storeID=?
                AND inventoryCountID <> ?');
        $res = $dbc->query('SELECT upc, storeID 
            FROM InventoryCounts
            WHERE mostRecent=1
            GROUP BY upc, storeID
            HAVING COUNT(*) > 1');
        while ($row = $dbc->fetchRow($res)) {
            $keepID = $dbc->getValue($keepP, array($row['upc'], $row['storeID']));
            if ($keepID) {
                $dbc->execute($cleanP, array($row['upc'], $row['storeID'], $keepID));
            }
        }

        return true;
    }

    /**
      Normal nightly behavior is to get all base counts and
      rebuild cache but if store and vendor IDs have been
      specified only those entires are recalculated
    */
    private function getCounts($dbc, $store_id, $vendor_id)
    {
        $countQ = '
            SELECT i.upc,
                storeID,
                count,
                countDate,
                par
            FROM InventoryCounts AS i ';
        $countArgs = array();
        if ($store_id && $vendor_id) {
            $countQ .= '
                INNER JOIN products AS p ON p.upc=i.upc AND p.store_id=i.storeID
                WHERE mostRecent=1 
                    AND i.storeID=?
                    AND p.default_vendor_id=?
                ORDER BY countDate DESC';
            $countArgs[] = $store_id;
            $countArgs[] = $vendor_id;
        } else {
            $countQ .= ' WHERE mostRecent=1
                ORDER BY countDate DESC';
        }
        $countP = $dbc->prepare($countQ);

        return $dbc->execute($countP, $countArgs);
    }

    public function run()
    {
        $dbc = FannieDB::get($this->config->get('OP_DB'));
        $this->clearEntries($dbc, $this->store_id, $this->vendor_id);
        $this->deepClean($dbc, $this->store_id, $this->vendor_id);

        $dbc->startTransaction();
        $insP = $dbc->prepare('
            INSERT INTO InventoryCache
                (upc, storeID, cacheStart, cacheEnd, baseCount, ordered, sold, shrunk)
            VALUES
                (?, ?, ?, ?, ?, ?, ?, ?)');

        $countR = $this->getCounts($dbc, $this->store_id, $this->vendor_id);
        $last = array(false, false);
        while ($row = $dbc->fetchRow($countR)) {
            if ($last[0] == $row['upc'] && $last[1] == $row['storeID']) {
                continue;
            }
            $last = array($row['upc'], $row['storeID'], $row['countDate']);
            $sales = 0;
            $sales += $this->getSales($dbc, $last);
            $aliases = COREPOS\Fannie\API\item\InventoryLib::getAliases($dbc, $row['upc']);
            foreach ($aliases as $alias) {
                $aliasSales = $this->getSales($dbc, array($alias['upc'], $row['storeID'], $row['countDate']));
                $sales += ($alias['multiplier'] * $aliasSales);
            }

            $orders = InventoryCacheModel::calculateOrdered($dbc, $row['upc'], $row['storeID'], $row['countDate']);

            $dtrans = DTransactionsModel::selectDTrans($row['countDate'], date('Y-m-d', strtotime('yesterday')));
            $shrinkP = $dbc->prepare('
                SELECT d.upc,
                    d.store_id,
                    ' . DTrans::sumQuantity('d') . ' AS qty
                FROM ' . $dtrans . ' AS d
                WHERE d.trans_status = \'Z\'
                    AND ' . DTrans::isNotTesting('d') . '
                    AND d.upc=?
                    AND d.store_id=?
                    AND d.datetime >= ?
                GROUP BY d.upc,
                    d.store_id');
            $shrink = $dbc->getRow($shrinkP, $last);
            $shrink = $shrink ? $shrink['qty'] : 0;

            $args = array(
                $row['upc'],
                $row['storeID'],
                $row['countDate'],
                date('Y-m-d 23:59:59', strtotime('yesterday')),
                $row['count'],
                $orders,
                $sales,
                $shrink,
            );
            $insR = $dbc->execute($insP, $args);
        }
        $dbc->commitTransaction();

        $dbc->query('
            UPDATE InventoryCache
            SET onHand = baseCount + ordered - sold - shrunk
        ');

        $dbc->query('
            UPDATE InventoryCache
            SET onHand = 0, negative=1
            WHERE onHand < 0
        ');

        $this->trimCounts($dbc, $this->store_id, $this->vendor_id);
    }

    // trim the backlog of count data
    // don't keep more than a 3 count history per item
    private function trimCounts($dbc, $store_id, $vendor_id)
    {
        if ($store_id && $vendor_id) {
            return true;
        }

        $dbc->startTransaction();
        $clearR = $dbc->query("SELECT upc, storeID FROM InventoryCounts GROUP BY upc, storeID HAVING COUNT(*) > 3");
        $getP = $dbc->prepare("SELECT inventoryCountID, mostRecent FROM InventoryCounts WHERE upc=? AND storeID=? ORDER BY countDate DESC");
        $delP = $dbc->prepare("DELETE FROM InventoryCounts WHERE inventoryCountID=?");
        while ($clearW = $dbc->fetchRow($clearR)) {
            $args = array($clearW['upc'], $clearW['storeID']);
            $counter = 1;
            $res = $dbc->execute($getP, $args);
            while ($row = $dbc->fetchRow($res)) {
                if ($counter > 3 && $row['mostRecent'] != 1) {
                    $dbc->execute($delP, array($row['inventoryCountID']));
                }
                $counter++;
            }
        }
        $dbc->commitTransaction();
    }

    private function getSales($dbc, $args)
    {
        $dlog = DTransactionsModel::selectDLog($args[2], date('Y-m-d', strtotime('yesterday')));
        $salesP = $dbc->prepare('
            SELECT d.upc,
                d.store_id,
                ' . DTrans::sumQuantity('d') . ' AS qty,
                p.scale AS byWeight
            FROM ' . $dlog . ' AS d
                ' . DTrans::joinProducts('d', 'p', 'INNER') . '
            WHERE p.default_vendor_id > 0
                AND d.trans_status <> \'R\'
                AND d.upc=?
                AND d.store_id=?
                AND d.tdate >= ?
                AND d.charflag <> \'SO\'
            GROUP BY d.upc,
                d.store_id
            HAVING qty > 0');
        $sales = $dbc->getRow($salesP, $args);
        if ($sales === false) {
            return 0;
        }
        return $sales['byWeight'] ? $sales['qty'] * 1.001 : $sales['qty'];
    }
}

