<?php

class App_Process_Media_StampObj extends Qs_Db_Obj
{
    protected $_hasFilter = true;
    protected $_tableAlias = 'Stamp';
    protected $_listSequenceNumbering;

    protected function _filterWhere(Zend_Db_Select $select)
    {
        $select = parent::_filterWhere($select);
        $revision = $this->_partObject->getData('revision');
        $select->where('revisionStart <= ? AND (revisionEnd > ? OR revisionEnd IS NULL)', $revision['id']);

        return $select;
    }

    public function getPartNumbers($filter)
    {
        return $this->_db->select()
            ->from(array('pv' => $this->_getTableName('PartVersion')), array('title' => 'number', 'value' => 'number'))
            ->join(array('p' => $this->_getTableName('Part')), 'pv.id = p.currentVersion', array())
            ->where('number LIKE ?', '%' . $filter .'%')
            ->limit(100)
            ->query()->fetchAll();
    }

    public function getMeltLotNumbers($filter)
    {
        return $this->_db->select()
            ->distinct()
            ->from(array('s' => $this->_getTableName('Stamp')), array('title' => 'meltLotNumber', 'value' => 'meltLotNumber'))
            ->where('meltLotNumber LIKE ?', '%' . $filter .'%')
            ->limit(100)
            ->query()->fetchAll();
    }

    public function getSerialNumbers4Autocomplete($term)
    {
        return $this->_db->select()
            ->distinct()
            ->from(array('s' => $this->_getTableName('Stamp')), array('title' => 'serialNumber', 'value' => 'serialNumber'))
            ->where('serialNumber LIKE ?', '%' . $term .'%')
            ->limit(100)
            ->query()->fetchAll();
    }

    public function getUsers4Autocomplete($term)
    {
        $nameExpr = new Zend_Db_Expr('("firstName" + \' \' + "lastName")');
        $select = $this->_db->select()
            ->distinct()
            ->from(array('s' => $this->_getTableName('User')), array('title' => $nameExpr, 'value' => 'id'))
            ->limit(20);
        Qs_Db_Filter::where($select, array('firstName', 'lastName'), $term);
        return $this->_db->fetchAll($select);
    }

    public function getUserName($userId)
    {
        $nameExpr = new Zend_Db_Expr('("firstName" + \' \' + "lastName")');
        $select = $this->_db->select()
            ->distinct()
            ->from(array('s' => $this->_getTableName('User')), array('title' => $nameExpr))
            ->where('"id" = ?', $userId);
        return $this->_db->fetchOne($select);
    }

    protected function _joinFromDb(Zend_Db_Select $select)
    {
        $select->joinLeft(array('m' => $this->_getTableName('Media')), 'm.id = Stamp.mediaId', array());
        $select->joinLeft(array('p' => $this->_getTableName('Part')), 'p.id = m.partId', array('partId' => 'id'));
        return $this;
    }

    public function getListSelect($options = array())
    {
        $userId = App_User_Auth::getInstance()->getData('id');

        $select = $this->_db->select();
        $select->from(
            array('s' => $this->_getTableName('Stamp')),
            array(
                'id',
                'mediaId',
                'stampId',
                'added',
                'serialNumber',
                'meltLotNumber',
                'htCondition',
                'welderId',
                'personWelding',
                'weldRodLotNo',
                'timeCardLine',
                'quantity',
            )
        );
        $select->joinLeft(
            array('u' => $this->_getTableName('User')),
            'u.id = s.personWelding',
            array('personWeldingName' => new Zend_Db_Expr('("firstName" + \' \' + "lastName")'))
        );
        $select->join(
            array('sm' => $this->_getTableName('StampMark')),
            'sm.stampId = s.id',
            array(
                'markId'           => 'id',
                'markNum'          => 'num',
                'markType'         => 'type',
                'markSize'         => 'size',
                'markColor'        => 'color',
                'markPositionTop'  => 'positionTop',
                'markPositionLeft' => 'positionLeft',
                'markScrapCode'    => 'scrapCode',
                'markWidth'        => 'width',
                'markLength'       => 'length',
                'markDepth'        => 'depth',
                'markDisposition'  => 'disposition',
                'markBadWeld'      => 'badWeld',
                'markComments'     => 'comments',
                'markXRayAccept'        => 'xRayAccept',
                'markPtAccept'          => 'ptAccept',
                'markDimensionalAccept' => 'dimensionalAccept',
            )
        );
        $select->join(array('m' => $this->_getTableName('Media')), 'm.id = s.mediaId', array('image'));
        $select->join(array('p' => $this->_getTableName('Part')), 'p.id = m.partId', array('partId' => 'id'));
        $select->joinLeft(array('bv' => $this->_getTableName('PartVersion')), 'bv.id = p.blockedVersion', array());
        $select->order(array('s.added DESC', 's.stampId', 'sm.num'));

        if ($userId) {
            $_userId = (int) $userId;
            $revisionIdExpr = new Zend_Db_Expr("(CASE  WHEN bv.userId = {$_userId} THEN p.blockedVersion ELSE p.currentVersion END)");
            $select->where('m.revisionStart <= ' . $revisionIdExpr . ' AND (m.revisionEnd > ' . $revisionIdExpr . ' OR m.revisionEnd IS NULL)');
        } else {
            $select->where('m.revisionStart <= p.currentVersion AND (m.revisionEnd > p.currentVersion OR m.revisionEnd IS NULL)');
        }

        $filteredByMeltLotNumber = $filteredBySerialNumber = $filteredByPartNumber = false;
        if ($this->getFilter('meltLotNumber')) {
            $filteredByMeltLotNumber = true;
            $select->where('s.meltLotNumber LIKE ?', $this->getFilter('meltLotNumber'));
        }
        $select->join(array('pv' => $this->_getTableName('PartVersion')), 'pv.id = p.currentVersion', array('partVersionNumber' => 'number'));
        if ($this->getFilter('partNumber')) {
            $filteredByPartNumber = true;
            $select->where('pv.number LIKE ?', $this->getFilter('partNumber'));
        }
        if (($serialNumber = $this->getFilter('serialNumber'))) {
            $filteredBySerialNumber = true;
            $select->where('s.serialNumber LIKE ?', $serialNumber);
        }
        if (($fromDate = $this->getFilter('fromDate'))) {
            $select->where('CAST(s.added AS DATE) >= CAST(? AS DATE)', $fromDate);
        }
        if (($toDate = $this->getFilter('toDate'))) {
            $select->where('CAST(s.added AS DATE) <= CAST(? AS DATE)', $toDate);
        }

        $this->_listSequenceNumbering = ($filteredByMeltLotNumber && $filteredBySerialNumber && $filteredByPartNumber);
        return $select;
    }

    /**
     * Read data for stamp report
     *
     * @param array $options
     * @return array
     */
    public function getList($options = array())
    {
        $select = $this->getListSelect();
        $rawList = $select->query()->fetchAll();
        $sizeMap = array_flip(App_Process_Media_Obj::getSizeMap());
        $list = array();

        $positionList = array();
        foreach ($rawList as $row) {
            $mediaId = (int) $row['mediaId'];
            $stampId = (int) $row['id'];
            
            $partNumber = $row['partVersionNumber'];
            $serialNumber = $row['serialNumber'];
            $meltLotNumber = $row['meltLotNumber'];

            $row['faSize'] = (isset($sizeMap[$row['markSize']])) ? $sizeMap[$row['markSize']] : $row['markSize'];

            if (!array_key_exists($mediaId, $list)) {
                $list[$mediaId] = array(
                    'mediaId' => $mediaId,
                    'image'   => Qs_ImageFs::getFullAttribs($row['image']),
                    'stamps'  => array(),
                );
            }

            if (!array_key_exists($stampId, $list[$mediaId]['stamps'])) {
                $list[$mediaId]['stamps'][$stampId] = array(
                    'id'                => $stampId,
                    'stampId'           => $row['stampId'],
                    'mediaId'           => $row['mediaId'],
                    'partId'            => $row['partId'],
                    'added'             => $row['added'],
                    'serialNumber'      => $row['serialNumber'],
                    'meltLotNumber'     => $row['meltLotNumber'],
                    'htCondition'       => $row['htCondition'],
                    'welderId'          => $row['welderId'],
                    'personWelding'     => $row['personWelding'],
                    'personWeldingName' => $row['personWeldingName'],
                    'weldRodLotNo'      => $row['weldRodLotNo'],
                    'timeCardLine'      => $row['timeCardLine'],
                    'quantity'          => $row['quantity'],

                    'marks'             => array(),
                );

            }
            unset(
                $row['partId'], $row['mediaId'], $row['stampId'], $row['added'], $row['serialNumber'], $row['meltLotNumber'],
                $row['htCondition'], $row['welderId'], $row['personWelding'], $row['personWeldingName'], $row['weldRodLotNo'],
                $row['timeCardLine'], $row['quantity'], $row['partVersionNumber']
            );

            $positionId = $partNumber . ':' . $serialNumber . ':' . $meltLotNumber;
            $position = str_pad($row['markPositionTop'], 4, '0', STR_PAD_LEFT)
                . ':' . str_pad($row['markPositionLeft'], 4, '0', STR_PAD_LEFT)
                . ':' . str_pad($row['markId'], 6, '0', STR_PAD_LEFT);

            $positionList[$mediaId][$position] = array(
                'positionId' => $positionId,
                'mediaId'    => $mediaId,
                'stampId'    => $stampId,
                'markId'     => $row['markId'],
            );

            $row['mediaMarkIndex'] = $row['markNum'];
            $list[$mediaId]['stamps'][$stampId]['marks'][$row['markId']] = $row;
        }

        return $list;
    }

    public function readPartId($stampId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('Stamp'), []);
        $select->join(array('m' => $this->_getTableName('Media')), 'm.id = Stamp.mediaId', array());
        $select->join(array('p' => $this->_getTableName('Part')), 'p.id = m.partId', array('partId' => 'id'));
        $select->where('Stamp.id = ?', $stampId);
        $select->limit(1);
        return $this->_db->fetchOne($select);
    }

    public function readLastUsedMarkNo($partId, $serialNumber, $meltLotNumber, $filter = array())
    {
        $select = $this->_db->select();
        $select->from(array('p' => $this->_getTableName('Part')), array());
        $select->join(array('m' => $this->_getTableName('Media')), 'm.partId = p.id', array());
        $select->join(array('s' => $this->_getTableName('Stamp')), 's.mediaId = m.id', array());
        $select->join(array('sm' => $this->_getTableName('StampMark')), 'sm.stampId = s.id', array('num'));
        $select->where('p.id = ?', (int) $partId);
        $select->where('s.serialNumber = ?', (string) $serialNumber);
        $select->where('s.meltLotNumber = ?', (string) $meltLotNumber);
        if ($filter) {
            $select->where(Qs_Db::getWhereSql($filter));
        }
        $select->order('sm.num DESC');
        $select->limit(1);
        return $this->_db->fetchOne($select);
    }

    public function getLastMarkNum($partId, $serialNumber, $meltLotNumber)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('MarkNumSequence'), ['lastMarkNum']);
        $select->where('partId = ?', (int) $partId);
        $select->where('serialNumber = ?', (string) $serialNumber);
        $select->where('meltLotNumber = ?', (string) $meltLotNumber);
        $select->limit(1);
        return $this->_db->fetchOne($select);
    }

    public function updateLastMarkNum($lastMarkNum, $partId, $serialNumber, $meltLotNumber)
    {
        if (false === $this->getLastMarkNum($partId, $serialNumber, $meltLotNumber)) {
            $this->_getTable('MarkNumSequence')->insert(array(
                'partId'        => (int) $partId,
                'serialNumber'  => (string) $serialNumber,
                'meltLotNumber' => (string) $meltLotNumber,
                'lastMarkNum'   => (int) $lastMarkNum,
            ));
        } else {
            $this->_getTable('MarkNumSequence')->update(array('lastMarkNum' => $lastMarkNum), array(
                'partId = ?'        => (int) $partId,
                'serialNumber = ?'  => (string) $serialNumber,
                'meltLotNumber = ?' => (string) $meltLotNumber
            ));
        }
        return $this;
    }

}