<?php

/**
  Class that connects special orders
  to purchase orders
*/
class SoPoBridge
{
    /**
      Requires SQLManager, FannieConfig
    */
    public function __construct($dbc, $config)
    {
        $this->dbc = $dbc;
        $this->config = $config;
    }

    /**
      Get ID of currently open purchase order for
      the given vendor and store
    */
    private function getPurchaseOrderID($vendorID, $storeID)
    {
        $cutoff = date('Y-m-d 00:00:00', strtotime('28 days ago'));
        $prep = $this->dbc->prepare('
            SELECT orderID
            FROM ' . FannieDB::fqn('PurchaseOrder', 'op') . '
            WHERE vendorID=?
                AND storeID=?
                AND placed=0
                AND creationDate >= ?
            ORDER BY creationDate DESC');
        return $this->dbc->getValue($prep, array($vendorID, $storeID, $cutoff));
    }

    /**
      Check whether this special order line item has a valid
      SKU and thus can be added to a purchase order
    */
    public function canPurchaseOrder($soID, $transID)
    {
        $pending = $this->config->get('TRANS_DB') . $this->dbc->sep() . 'PendingSpecialOrder';
        $prep = $this->dbc->prepare('
            SELECT v.sku, n.vendorID, d.salesCode, o.trans_status AS altSKU
            FROM ' . $pending . ' AS o
                INNER JOIN ' . FannieDB::fqn('vendors', 'op') . ' AS n ON LEFT(n.vendorName, LENGTH(o.mixMatch)) = o.mixMatch
                LEFT JOIN ' . FannieDB::fqn('vendorItems', 'op') . ' AS v on n.vendorID=v.vendorID AND o.upc=v.upc
                LEFT JOIN ' . FannieDB::fqn('departments', 'op') . ' AS d ON o.department=d.dept_no
            WHERE o.order_id=?
                AND o.trans_id=?
        ');

        return $this->dbc->getRow($prep, array($soID, $transID));
    }

    private function numCases($soID, $transID)
    {
        $pending = $this->config->get('TRANS_DB') . $this->dbc->sep() . 'PendingSpecialOrder';
        $prep = $this->dbc->prepare('
            SELECT ItemQtty
            FROM ' . $pending . '
            WHERE order_id=?
                AND trans_id=?');
        return $this->dbc->getValue($prep, array($soID, $transID));
    }

    /**
      Add special order line item to existing purchase order
    */
    public function addItemToPurchaseOrder($soID, $transID, $storeID)
    {
        $vendorInfo = $this->canPurchaseOrder($soID, $transID);
        if ($vendorInfo === false) {
            return false;
        }
        if (empty($vendorInfo['sku']) && !empty($vendorInfo['altSKU'])) {
            $vendorInfo['sku'] = $vendorInfo['altSKU'];
        }
        $poID = $this->getPurchaseOrderID($vendorInfo['vendorID'], $storeID);
        $porder = new PurchaseOrderModel($this->dbc);
        // init purchase order if necessary
        if ($poID === false) {
            $porder->vendorID($vendorInfo['vendorID']);
            $porder->storeID($storeID);
            $porder->creationDate(date('Y-m-d H:i:s'));
            $porder->vendorOrderID('SO-' . date('Ymd'));
            $poID = $porder->save();
        }

        // get number of cases
        $cases = $this->numCases($soID, $transID);

        $prep = $this->dbc->prepare('SELECT * FROM vendorItems WHERE sku=? AND vendorID=?');
        $item = $this->dbc->getRow($prep, array($vendorInfo['sku'], $vendorInfo['vendorID']));
        $pending = $this->config->get('TRANS_DB') . $this->dbc->sep() . 'PendingSpecialOrder';
        $prep = $this->dbc->prepare("SELECT description, quantity AS units, 0 AS cost, '' AS brand, upc
            FROM {$pending} WHERE order_id=? AND trans_id=?");
        $spoRow = $this->dbc->getRow($prep, array($soID, $transID));
        if ($item === false) {
            $item = $spoRow;
            if ($vendorInfo['altSKU']) {
                $item['sku'] = $vendorInfo['altSKU'];
            } elseif (is_numeric($item['upc'])) {
                $item['sku'] = $item['upc'];
            } else {
                $item['sku'] = uniqid();
            }
        }
        $item['units'] = $spoRow['units'];

        $poSKU = substr($item['sku'], -12) . '.';
        $poitem = new PurchaseOrderItemsModel($this->dbc);
        $poitem->orderID($poID);
        $poitem->sku($poSKU);
        $poitem->salesCode($vendorInfo['salesCode']);
        $poitem->isSpecialOrder(1);
        $poitem->unitCost($item['cost']);
        $poitem->quantity($cases);
        $poitem->caseSize($item['units']);
        $poitem->brand($item['brand']);
        $poitem->description($item['description']);
        // put specialOrderID & transID into UPC to cross reference 
        // between PO and SO lines
        $poitem->internalUPC(str_pad($soID, 9, '0', STR_PAD_LEFT) . str_pad($transID, 4, '0', STR_PAD_LEFT));
        $saved = $poitem->save();

        return $saved ? $poID : false;
    }

    /**
      Find the purchase order containing this item
    */
    public function findPurchaseOrder($soID, $transID, $storeID)
    {
        $vendorInfo = $this->canPurchaseOrder($soID, $transID);
        if ($vendorInfo === false) {
            return false;
        }

        $prep = $this->dbc->prepare('
            SELECT o.orderID
            FROM ' . FannieDB::fqn('PurchaseOrder', 'op') . ' AS o
                INNER JOIN ' . FannieDB::fqn('PurchaseOrderItems', 'op') . ' AS i ON o.orderID=i.orderID
            WHERE o.vendorID=?
                AND o.storeID=?
                AND i.isSpecialOrder=1
                AND i.internalUPC=?
        ');
        return $this->dbc->getValue($prep, array(
            $vendorInfo['vendorID'],
            $storeID,
            str_pad($soID, 9, '0', STR_PAD_LEFT) . str_pad($transID, 4, '0', STR_PAD_LEFT),
        ));
    }

    /**
      Delete a SPO item from a PO
      @param $soID [int] special order ID
      @param $transID [int] special order line item ID
      @return [bool] success

      An item is only deleted from a PO if it has not yet been received
    */
    public function removeItemFromPurchaseOrder($orderID, $transID)
    {
        $upc = str_pad($orderID, 9, '0', STR_PAD_LEFT) . str_pad($transID, 4, '0', STR_PAD_LEFT);

        $itemP = $this->dbc->prepare('SELECT * FROM ' . FannieDB::fqn('PurchaseOrderItems', 'op')
                                . ' WHERE internalUPC=? AND orderID=?');
        $item = $this->dbc->getRow($itemP, array($upc, $orderID));
        if ($item === false || $item['receivedDate'] || $item['receivedBy']) {
            return false;
        }

        $cleanP = $this->dbc->prepare("DELETE FROM " . FannieDB::fqn('PurchaseOrderItems', 'op')
                                . " WHERE sku=? AND orderID=?");
        $cleanR = $this->dbc->execute($cleanP, array($item['sku'], $orderID));

        return $cleanR ? true : false;
    }

    /**
      Update line item's status as placed, update order status too if needed
    */
    public function markAsPlaced($soID, $transID)
    {
        $table = $this->config->get('TRANS_DB') . $this->dbc->sep() . 'PendingSpecialOrder';
        $itemP = $this->dbc->prepare('
            UPDATE ' . $table . ' 
            SET memType=1
            WHERE order_id=?
                AND trans_id=?');
        $this->dbc->execute($itemP, array($soID, $transID));

        $all = $this->dbc->prepare('SELECT MIN(memType) FROM ' . $table . ' WHERE order_id=? AND trans_id > 0');
        $min = $this->dbc->getValue($all, array($soID));
        if ($min == 1) {
            $table = $this->config->get('TRANS_DB') . $this->dbc->sep() . 'SpecialOrders';
            $upP = $this->dbc->prepare('UPDATE ' . $table . ' SET statusFlag=?, subStatus=? WHERE specialOrderID=?');
            $this->dbc->execute($upP, array(4, time(), $soID));
        }
    }
}

