<?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 UnfiUploadPage extends \COREPOS\Fannie\API\FannieUploadPage 
{
    public $title = "Fannie - UNFI Prices";
    public $header = "Upload UNFI price file";

    public $description = '[UNFI Catalog Import] specialized vendor import tool. Column choices
    default to UNFI price file layout.';

    protected $preview_opts = array(
        'upc' => array(
            'display_name' => 'UPC *',
            'default' => 14,
            'required' => True
        ),
        'srp' => array(
            'display_name' => 'SRP *',
            'default' => 16,
            'required' => True
        ),
        'brand' => array(
            'display_name' => 'Brand *',
            'default' => 2,
            'required' => True
        ),
        'desc' => array(
            'display_name' => 'Description *',
            'default' => 6,
            'required' => True
        ),
        'sku' => array(
            'display_name' => 'SKU *',
            'default' => 1,
            'required' => true
        ),
        'qty' => array(
            'display_name' => 'Case Qty *',
            'default' => 3,
            'required' => True
        ),
        'size' => array(
            'display_name' => 'Unit Size',
            'default' => 4,
        ),
        'cost' => array(
            'display_name' => 'Case Cost (Reg) *',
            'default' => 8,
            'required' => True
        ),
        'saleCost' => array(
            'display_name' => 'Case Cost (Sale)',
            'default' => 12,
        ),
        'cat' => array(
            'display_name' => 'UNFI Category # *',
            'default' => 5,
            'required' => True
        ),
        'flags' => array(
            'display_name' => 'Flags',
            'default' => 20,
        ),
    );

    protected $use_splits = true;
    protected $use_js = false;
    protected $vendor_name = 'UNFI';

    protected function getVendorID()
    {
        $idP = $this->connection->prepare("SELECT vendorID FROM vendors WHERE vendorName=? ORDER BY vendorID");
        $vid = $this->connection->getValue($idP, array($this->vendor_name));

        return $vid;
    }

    function process_file($linedata, $indexes)
    {
        global $FANNIE_OP_DB;
        $dbc = FannieDB::get($FANNIE_OP_DB);
        $VENDOR_ID = $this->getVendorID();
        if ($VENDOR_ID === false) {
            $this->error_details = 'Cannot find vendor';
            return false;
        }

        // PLU items have different internal UPCs
        // map vendor SKUs to the internal PLUs
        $SKU_TO_PLU_MAP = array();
        $skusP = $dbc->prepare('SELECT sku, upc, isPrimary, multiplier FROM VendorAliases WHERE vendorID=?');
        $skusR = $dbc->execute($skusP, array($VENDOR_ID));
        while($skusW = $dbc->fetch_row($skusR)) {
            if (!isset($SKU_TO_PLU_MAP[$skusW['sku']])) {
                $SKU_TO_PLU_MAP[$skusW['sku']] = array();
            }
            $SKU_TO_PLU_MAP[$skusW['sku']][] = $skusW;
        }

        $extraP = $dbc->prepare("update prodExtra set cost=? where upc=?");
        $prodP = $dbc->prepare('
            UPDATE products
            SET cost=?,
                numflag= numflag | ? | ?,
                modified=' . $dbc->now() . '
            WHERE upc=?
                AND default_vendor_id=?');
        $itemP = $dbc->prepare("
            INSERT INTO vendorItems (
                brand, 
                sku,
                size,
                upc,
                units,
                cost,
                description,
                vendorDept,
                vendorID,
                saleCost,
                modified,
                srp
            ) VALUES (
                ?,
                ?,
                ?,
                ?,
                ?,
                ?,
                ?,
                ?,
                ?,
                ?,
                ?,
                ?
            ) ON DUPLICATE KEY UPDATE brand=?, sku=?, size=?, units=?, cost=?, description=?, vendorDept=?, vendorID=?, saleCost=?, modified=?
            ");
        $srpP = false;
        if (false && $dbc->tableExists('vendorSRPs')) {
            $srpP = $dbc->prepare("INSERT INTO vendorSRPs (vendorID, upc, srp) VALUES (?,?,?)");
        }
        $updated_upcs = array();
        $rounder = new \COREPOS\Fannie\API\item\PriceRounder();

        $dbc->startTransaction();

        foreach($linedata as $data) {
            if (!is_array($data)) continue;

            if (!isset($data[$indexes['upc']])) continue;

            // grab data from appropriate columns
            $sku = ($indexes['sku'] !== false) ? $data[$indexes['sku']] : '';
            $sku = str_pad($sku, 7, '0', STR_PAD_LEFT);
            $brand = $data[$indexes['brand']];
            $description = $data[$indexes['desc']];
            $qty = $data[$indexes['qty']];
            $size = ($indexes['size'] !== false) ? $data[$indexes['size']] : '';
            $prodInfo = ($indexes['flags'] !== false) ? $data[$indexes['flags']] : '';
            $flag = 0;
            $upc = substr($data[$indexes['upc']],0,13);
            // zeroes isn't a real item, skip it
            if ($upc == "0000000000000")
                continue;
            $aliases = array(array('upc'=>$upc, 'multiplier'=>1, 'isPrimary'=>1));
            if (isset($SKU_TO_PLU_MAP[$sku])) {
                $aliases = $SKU_TO_PLU_MAP[$sku];
            }
            $category = $data[$indexes['cat']];
            $reg = trim($data[$indexes['cost']]);
            $net = ($indexes['saleCost'] !== false) ? trim($data[$indexes['saleCost']]) : 0.00;
            // blank spreadsheet cell
            if (empty($net)) {
                $net = 0;
            }
            $srp = trim($data[$indexes['srp']]);
            // can't process items w/o price (usually promos/samples anyway)
            if (empty($reg) or empty($srp))
                continue;

            // syntax fixes. 
            // trim $ off amounts as well as commas for the
            // occasional > $1,000 item
            $reg = $this->sanitizePrice($reg);
            $net = $this->sanitizePrice($net);
            $srp = $this->sanitizePrice($srp);

            // sale price isn't really a discount
            if ($reg == $net) {
                $net = 0;
            }

            // skip the item if prices aren't numeric
            // this will catch the 'label' line in the first CSV split
            // since the splits get returned in file system order,
            // we can't be certain *when* that chunk will come up
            if (!is_numeric($reg) or !is_numeric($srp)) {
                continue;
            }

            list($organic_flag, $gf_flag) = $this->getFlags($prodInfo);

            // need unit cost, not case cost
            $reg_unit = $reg / $qty;
            $net_unit = $net / $qty;

            foreach ($aliases as $alias) {

                if (substr($size, -1) == '#' && substr($alias['upc'], 0, 3) == '002') {
                    $qty = trim($size, '# ');
                    if ($qty == 0) {
                        $qty = 1;
                    }
                    $reg_unit = $reg / $qty;
                    $net_unit = $net / $qty;
                    $size = '#';
                } elseif (substr($size, -2) == 'LB' && substr($alias['upc'], 0, 3) == '002') {
                    $qty = trim($size, 'LB ');
                    if ($qty == 0) {
                        $qty = 1;
                    }
                    $reg_unit = $reg / $qty;
                    $net_unit = $net / $qty;
                    $size = 'LB';
                }

                if ($alias['multiplier'] == 0 || $alias['multiplier']*$reg_unit == 0) {
                    $alias['multiplier'] = 1;
                }
                $dbc->execute($extraP, array($reg_unit*$alias['multiplier'],$alias['upc']));
                $dbc->execute($prodP, array($reg_unit*$alias['multiplier'],$organic_flag,$gf_flag,$alias['upc'],$VENDOR_ID));
                $updated_upcs[] = $alias['upc'];

                $srp = $rounder->round($srp * $alias['multiplier']);

                $args = array(
                    $brand, 
                    $alias['isPrimary'] ? $sku : $alias['upc'],
                    $size === false ? '' : $size,
                    $alias['upc'],
                    $qty,
                    $reg_unit*$alias['multiplier'],
                    $description,
                    $category,
                    $VENDOR_ID,
                    $net_unit*$alias['multiplier'],
                    date('Y-m-d H:i:s'),
                    $srp,
                    // on duplicate key
                    $brand,
                    $alias['isPrimary'] ? $sku : $alias['upc'],
                    $size === false ? '' : $size,
                    $qty,
                    $reg_unit*$alias['multiplier'],
                    $description,
                    $category,
                    $VENDOR_ID,
                    $net_unit*$alias['multiplier'],
                    date('Y-m-d H:i:s')
                );
                $dbc->execute($itemP,$args);

                if ($srpP) {
                    $dbc->execute($srpP,array($VENDOR_ID,$alias['upc'],$srp));
                }
            }
        }

        $updateModel = new ProdUpdateModel($dbc);
        $updateModel->logManyUpdates($updated_upcs, ProdUpdateModel::UPDATE_EDIT);

        $dbc->commitTransaction();

        return true;
    }

    protected function sanitizePrice($reg)
    {
        $reg = str_replace('$',"",$reg);
        return str_replace(",","",$reg);
    }

    protected function getFlags($prodInfo)
    {
        // set organic flag on OG1 (100%) or OG2 (95%)
        $organic_flag = 0;
        if (strstr($prodInfo, 'O2') || strstr($prodInfo, 'O1')) {
            $organic_flag = (1 << (17 - 1));
        }
        // set gluten-free flag on g
        $gf_flag = 0;
        if (strstr($prodInfo, 'g')) {
            $organic_flag = (1 << (18 - 1));
        }

        return array($organic_flag, $gf_flag);
    }

    /* clear tables before processing */
    function split_start(){
        global $FANNIE_OP_DB;
        $dbc = FannieDB::get($FANNIE_OP_DB);

        $VENDOR_ID = $this->getVendorID();
        if ($VENDOR_ID === false) {
            $this->error_details = 'Cannot find vendor';
            return false;
        }

    }

    function preview_content(){
        return '';  
        return '<input type="checkbox" name="rm_cds" checked /> Remove check digits';
    }

    function results_content()
    {
        $ret = "<p>Price data import complete</p>";
        $ret .= '<p><a href="'.filter_input(INPUT_SERVER, 'PHP_SELF').'">Upload Another</a></p>';

        return $ret;
    }
}

FannieDispatch::conditionalExec();

