<?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(__DIR__ . '/../../../classlib2.0/FannieAPI.php');
}

class UIGLib 
{
    /**
      Create purchase orders from zipfile
      @param $zipfile filename
      @param $vendorID integer vendor ID
      @param $repeat this date has been previously imported
    */
    static public function import($zipfile, $vendorID, $repeat=false)
    {
        global $FANNIE_OP_DB;
        $za = new ZipArchive();
        $try = $za->open($zipfile);
        if ($try !== true) {
            // invalid file
            return $try;
        }

        $dbc = FannieDB::get($FANNIE_OP_DB);
        $create = $dbc->prepare('INSERT INTO PurchaseOrder (vendorID, creationDate, placed,
                            placedDate, userID, vendorOrderID, vendorInvoiceID, storeID) VALUES
                            (?, ?, 1, ?, 0, ?, ?, ?)');
        $find = $dbc->prepare('SELECT orderID FROM PurchaseOrder WHERE vendorID=? AND storeID=? AND vendorInvoiceID=?');
        $plu = $dbc->prepare('SELECT upc FROM VendorAliases WHERE isPrimary=1 AND vendorID=? AND sku LIKE ?');
        $clear = $dbc->prepare('DELETE FROM PurchaseOrderItems WHERE orderID=?');
        $codeP = $dbc->prepare('SELECT sku, salesCode FROM PurchaseOrderItems WHERE orderID=?');
        $storeID = FannieConfig::config('STORE_ID');

        for ($i=0; $i<$za->numFiles; $i++) {
            $info = $za->statIndex($i);
            if (substr(strtolower($info['name']), -4) != '.csv') {
                // skip non-csv file
                continue;
            }

            $fp = $za->getStream($info['name']);
            if (!$fp) { // false or null in failure cases
                continue;
            }
            $header_info = array();
            $item_info = array();
            $csvfile = tempnam(__DIR__ . '/noauto/originals/', 'inv');
            $csvFP = fopen($csvfile, 'w');
            while(!feof($fp)) {
                $line = fgetcsv($fp);
                if (strtolower($line[0]) == 'header') {
                    $header_info = self::parseHeader($line);
                } else if (strtolower($line[0]) == 'detail') {
                    $item = self::parseItem($line, $vendorID);
                    $item_info[] = $item;
                }
                fputcsv($csvFP, $line);
            }

            if (count($item_info) > 0) {
                $id = false;
                // check whether order already exists
                $idR = $dbc->execute($find, array($vendorID, $storeID, $header_info['vendorInvoiceID']));
                $new = false;
                $codeMap = array();
                if ($dbc->num_rows($idR) > 0) {
                    $idW = $dbc->fetch_row($idR);
                    $id = $idW['orderID'];
                    $codeR = $dbc->execute($codeP, array($id));
                    while ($codeW = $dbc->fetchRow($codeR)) {
                        $codeMap[$codeW['sku']] = $codeW['salesCode'];
                    }
                    $dbc->execute($clear, array($id));
                }
                if (!$id) {
                    // date has not been downloaded before OR
                    // date previously did not include this invoice
                    $dbc->execute($create, array($vendorID, $header_info['placedDate'], $header_info['placedDate'],
                                    $header_info['vendorOrderID'], $header_info['vendorInvoiceID'], $storeID));
                    $id = $dbc->insertID();
                }

                $fakeSku = 1;
                foreach($item_info as $item) {
                    if ($item['sku'] == 0) {
                        $item['sku'] = $fakeSku;
                        $fakeSku++;
                    }
                    $model = new PurchaseOrderItemsModel($dbc);
                    $model->orderID($id);
                    $model->sku($item['sku']);
                    if ($model->load()) {
                        // sometimes an invoice contains multiple
                        // lines with the same product SKU
                        // sum those so the single record in
                        // PurchaseOrderItems is correct
                        $item['quantity'] += $model->quantity();
                        $item['receivedQty'] += $model->receivedQty();
                        $item['receivedTotalCost'] += $model->receivedTotalCost();
                    }
                    $model->quantity($item['quantity']);
                    $model->receivedQty($item['receivedQty']);
                    $model->receivedTotalCost($item['receivedTotalCost']);

                    $model->unitCost($item['unitCost']);
                    $model->caseSize($item['caseSize']);
                    $model->receivedDate($header_info['receivedDate']);
                    $model->unitSize($item['unitSize']);
                    $model->brand($item['brand']);
                    $model->description($item['description']);
                    $model->internalUPC($item['upc']);

                    $pluCheck = $dbc->execute($plu, array($vendorID, $item['sku']));
                    if ($dbc->num_rows($pluCheck) > 0) {
                        $pluInfo = $dbc->fetch_row($pluCheck);
                        $model->internalUPC($pluInfo['upc']);
                    }
                    if (!$new && isset($codeMap[$item['sku']])) {
                        $model->salesCode($codeMap[$item['sku']]);
                    } elseif ($model->salesCode() == '') {
                        $code = $model->guessCode();
                        $model->salesCode($code);
                    }

                    switch ($item['sku']) { // anomoly handler
                        case '0473850';
                            $model->unitSize('#');
                            break;
                    }

                    $model->save();
                }

                rename($csvfile, __DIR__ . '/noauto/originals/' . $id . '.csv');
            } else {
                unlink($csvfile);
            }
        }

        return true;
    }

    static private function parseHeader($line)
    {
        $INVOICE = 1;
        $INVOICE_DATE = 4;
        $PLACED_DATE = 21;
        $PO_NUMBER = 24;

        return array(
                'placedDate' => date('Y-m-d', strtotime($line[$PLACED_DATE])),
                'receivedDate' => date('Y-m-d', strtotime($line[$INVOICE_DATE])),
                'vendorOrderID' => $line[$PO_NUMBER],
                'vendorInvoiceID' => $line[$INVOICE],
        );
    }

    static private $lookups = null;
    static private function getCaseSize($dbc, $upc, $sku, $vendorID)
    {
        if (self::$lookups === null) { 
            $vend = $dbc->prepare('
                SELECT units 
                FROM vendorItems 
                WHERE vendorID=? 
                    AND (upc=? OR sku=?)
                    AND units IS NOT NULL
                    AND units > 0');
            $order = $dbc->prepare('
                SELECT caseSize
                FROM PurchaseOrderItems AS i
                    INNER JOIN PurchaseOrder AS o ON i.orderID=o.orderID
                WHERE o.vendorID=?
                    AND (i.internalUPC=? OR i.sku=?)
                    AND i.caseSize <> 1
                ORDER BY i.receivedDate DESC');
            self::$lookups = array($vend, $order);
        }

        foreach (self::$lookups as $lookup) {
            $size = $dbc->getValue($lookup, array($vendorID, $upc, $sku));
            if ($size !== false) {
                return $size;
            }
        }

        return 1;
    }

    static private function parseItem($line, $vendorID)
    {
        global $FANNIE_OP_DB;

        $UPC = 3;
        $SKU = 4;
        $RECVQTY = 5;
        $CASESIZE = 6;
        $UNITSIZE = 7;
        $BRAND = 8;
        $DESCRIPTION = 9;
        $TOTALCOST = 13;
        $UNITCOST = 15;
        $ORDERQTY = 18;

        // remove non-digits and check digits
        // then pad to length
        $upc = str_replace('-', '', $line[$UPC]);
        $upc = str_replace(' ', '', $upc);
        $upc = substr($upc, 0, strlen($upc)-1);
        $upc = BarcodeLib::padUPC($upc);
        $line[$SKU] = str_pad($line[$SKU], 7, '0', STR_PAD_LEFT);

        $caseSize = $line[$CASESIZE];
        // invoice does not include proper case size
        // try to find actual size in vendorItems table
        // via SKU or UPC
        if (strtoupper($caseSize) == 'CS' || !is_numeric($caseSize)) {
            $dbc = FannieDB::get($FANNIE_OP_DB);
            $caseSize = self::getCaseSize($dbc, $upc, $line[$SKU], $vendorID);
        } 

        return array(
            'sku' => $line[$SKU],
            'quantity' => $line[$ORDERQTY],
            'unitCost' => $line[$UNITCOST],
            'caseSize' => $caseSize,
            'receivedQty' => $line[$RECVQTY] * $caseSize,
            'receivedTotalCost' => $line[$TOTALCOST],
            'unitSize' => $line[$UNITSIZE],
            'brand' => $line[$BRAND],
            'description' => $line[$DESCRIPTION],
            'upc' => $upc,
        );
    }
}

