<?php

namespace RxConnexionBundle\Repository;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr;
//use RxConnexionBundle\Entity\CanvassItemSupplierEntity;
use RxConnexionBundle\Entity\CanvassItemSupplierEntity;
use RxConnexionBundle\Entity\SupplierEntity;

/**
 * SupplierRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class SupplierRepository extends \Doctrine\ORM\EntityRepository
{
    public function validate($supplier_form) {

        $errors = array();

        $action = $supplier_form['action'];

        // d = delete
        if($action !== 'd') {

            $supplierExist = $this->getEntityManager()->getRepository('RxConnexionBundle:SupplierEntity')
                ->createQueryBuilder('es')
                ->where('es.id != :id')
                ->andWhere('es.description = :description')
                ->setParameters(array(
                    'id' => $supplier_form['id'],
                    'description' => $supplier_form['description']
                ))
                ->getQuery()->getResult();

            if($supplierExist)
                $errors[] = 'Description already exist.';

            $codeExist = $this->getEntityManager()->getRepository('RxConnexionBundle:SupplierEntity')
                ->createQueryBuilder('es')
                ->where('es.id != :id')
                ->andWhere('es.code = :code')
                ->setParameters(array(
                    'id' => $supplier_form['id'],
                    'code' => $supplier_form['code']
                ))
                ->getQuery()->getResult();

            if($codeExist)
                $errors[] = 'Code already exist.';

            if(empty($supplier_form['code']))
                $errors[] = 'code should not be blank.';

            if(empty($supplier_form['description']))
                $errors[] = 'Description should not be blank.';

            if(!empty($supplier_form['email']) && !(filter_var($supplier_form['email'], FILTER_VALIDATE_EMAIL) && preg_match('/@.+\./', $supplier_form['email'])))
                $errors[] = 'Email address is invalid.';
        }

        return $errors;
    }

    public function ajax_list(array $get, $userData){

        $columns = array(
            'es.code',
            'es.description',
            'es.mobileNo',
            'es.phoneNo',
            'es.email',
            'es.fax',
            'es.address',
            'es.id',
            'es.tinNo'
        );
        $dtColumns = array(
            'es.code',
            'es.description',
            'es.mobileNo',
            'es.phoneNo',
            'es.email',
            'es.fax',
            'es.address',
            'es.id',
            'es.tinNo'
        );
        $dbColumns = array();
        $asColumns = array();

        $query = $this->getEntityManager()
            ->getRepository('RxConnexionBundle:SupplierEntity')
            ->createQueryBuilder('es')
            ->select(array_merge($dtColumns, $dbColumns));

        /*
         * Ordering
         */
        foreach($get['columns'] as $key => $column) {
            if($column['orderable']=='true') {
                if(isSet($get['order'])) {
                    foreach($get['order'] as $order) {
                        if($order['column']==$key) {
                            $query->addOrderBy($columns[$key], $order['dir']);
                        }
                    }
                }
            }
        }

        /*
           * Filtering
           * NOTE this does not match the built-in DataTables filtering which does it
           * word by word on any field. It's possible to do here, but concerned about efficiency
           * on very large tables, and MySQL's regex functionality is very limited
           */
        if ( isset($get['search']) && $get['search']['value'] != '' ){
            $aLike = array();
            $search_value = $get['search']['value'];
            foreach($get['columns'] as $key => $column) {
                if($column['searchable']=='true') {
                    if(!empty($search_value))
                        $aLike[] = $query->expr()->like($columns[$key], '\'%'. $search_value .'%\'');
                }
            }
            foreach($asColumns as $asColumn) {
                $aLike[] = $query->expr()->like($asColumn, '\'%'. $search_value .'%\'');
            }
            if(count($aLike) > 0) $query->andWhere(new Expr\Orx($aLike));
            else unset($aLike);
        }

        $query->andWhere('es.isDeleted = 0');

        $query_count = $query;
        $result_count = $query_count->getQuery()->getArrayResult();

        /* Set Limit and Length */
        if ( isset( $get['start'] ) && $get['length'] != '-1' ){
            $query->setFirstResult( (int)$get['start'] )
                ->setMaxResults( (int)$get['length'] );
        }

        $result = $query->getQuery()->getResult();

        /* Data Count */
        $recordsTotal = count($result_count);
        /* Data set length after filtering */
        $recordsFiltered = count($result);

        unset($result_count);

        /*
         * Output
         */
        $output = array(
            "draw" => intval($get['draw']),
            "recordsTotal" => $recordsTotal,
            "recordsFiltered" => $recordsTotal,
            "data" => array()
        );

        $url = $get['url'];
        $formUrl = $url . '/admin/supplier/form';

        $hasUpdate = $userData['type'] === 'Super Admin' ||  in_array('Admin Supplier Update', $userData['accesses']);

        foreach($result as $row) {

            $id = base64_encode($row['id']);

            $values = array(
                $row['code'],
                $row['description'],
                $row['mobileNo'],
                $row['phoneNo'],
                $row['email'],
                $row['fax'],
                $row['tinNo'],
                $row['address'],
                $hasUpdate ? "<a class='action-button-style' href='$formUrl/u/$id'>Update</a>" : ""
            );

            $output['data'][] = $values;
        }

        unset($result);

        return $output;
    }

    public function ajax_list_for_canvassing(array $get, $userData){

        $columns = array(
            array('s.`description`', "s.`description`"),
            array('s.`mobile_no`', "s.`mobile_no`"),
            array('s.`phone_no`', "s.`phone_no`"),
            array('s.`email`', "s.`email`"),
            array('cis.canvassDate', "CONCAT_WS(' / ', cis.canvassDate, cis.`price`)", 'lastCanvassDatePrice'),
            array('s.`id`', "s.`id`"),
//            array('cis2.`currency_id`', "cis2.`currency_id`", 'cvsCurrency_id'),
//            array('cis2.`withholdingTaxRate_id`', "cis2.`withholdingTaxRate_id`", 'cvsWTaxRate_id'),
            array('cis2.`paymentMode`', "cis2.`paymentMode`", 'cvsPaymentMode'),
            array('cis2.`term`', "cis2.`term`", 'cvsTerm'),
            array('cis2.`price`', "cis2.`price`", 'cvsPrice'),
//            array('cis.`currency_id`', "cis.`currency_id`", 'currency_id'),
//            array('cis.`withholdingTaxRate_id`', "cis.`withholdingTaxRate_id`", 'wTaxRate_id'),
            array('cis.`paymentMode`', "cis.`paymentMode`", 'paymentMode'),
            array('cis.`term`', "cis.`term`", 'term'),
            array('cis.`price`', "cis.`price`", 'price')
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `supplier` s";
        $joins = "
            LEFT JOIN `canvass_item_supplier_tmp` cis ON s.`id` = cis.`supplier_id`
            LEFT JOIN `canvass_item_supplier_tmp2` cis2 ON s.`id` = cis2.`supplier_id`
        ";
        $sqlWhere = "";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array();

        foreach($columns as $key => $column) {
            $select .= ($key > 0 ? ', ' : ' ') . $column[1] . (isset($column[2]) ? ' AS ' . $column[2] : '');
        }

        /*
         * Ordering
         */
        foreach($get['columns'] as $key => $column) {
            if($column['orderable']=='true') {
                if(isSet($get['order'])) {
                    foreach($get['order'] as $order) {
                        if($order['column']==$key) {
                            $orderBy .= (!empty($orderBy) ? ', ' : 'ORDER BY ') . $columns[$key][0] . (!empty($order['dir']) ? ' ' . $order['dir'] : '');
                        }
                    }
                }
            }
        }

        /*
         * Filtering
         */
        if(isset($get['search']) && $get['search']['value'] != ''){
            $aLikes = array();
            foreach($get['columns'] as $key => $column) {
                if($column['searchable']=='true') {
                    $aLikes[] = $columns[$key][0] . ' LIKE :searchValue';
                }
            }
            foreach($asColumns as $asColumn) {
                $aLikes[] = $asColumn . ' LIKE :searchValue';
            }
            if(count($aLikes)) {
                $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . '(' . implode(' OR ', $aLikes) . ')';
                $stmtParams['searchValue'] = "%" . $get['search']['value'] . "%";
            }
        }

        /* Set Limit and Length */
        if(isset( $get['start'] ) && $get['length'] != '-1'){
            $limit = 'LIMIT ' . (int)$get['start'] . ',' . (int)$get['length'];
        }

        $conn = $this->getEntityManager()->getConnection();

        $q = $conn->prepare("
            CREATE TEMPORARY TABLE IF NOT EXISTS `canvass_item_supplier_tmp` (
                INDEX `IDX_TMP_000BBB` (`supplier_id`)
            ) AS (
                SELECT
                    cis.`supplier_id`,
                    DATE_FORMAT(MAX(cis.`canvass_date`), '%m/%d/%Y') AS canvassDate,
                    cis.`price`,
                    -- cis.`currency_id`,
                    -- cis.`withholding_tax_rate_id` AS withholdingTaxRate_id,
                    cis.`payment_mode` AS paymentMode,
                    cis.`term`
                FROM `canvass_item_supplier` cis
                LEFT JOIN `canvass_item` ci ON cis.`canvass_item_id` = ci.`id`
                LEFT JOIN `material_requisition_item` mri ON ci.`material_requisition_item_id` = mri.`id`
                WHERE mri.`item_id` = :item_id
                AND cis.`is_selected` = 1
            )
        ");
        $q->execute(array(
            'item_id' => $get['item_id'],
        ));

        $q = $conn->prepare("
            CREATE TEMPORARY TABLE IF NOT EXISTS `canvass_item_supplier_tmp2` (
                INDEX `IDX_TMP_000CCC` (`supplier_id`)
            ) AS (
                SELECT
                    cis.`supplier_id`,
                    -- cis.`currency_id`,
                    -- cis.`withholding_tax_rate_id` AS withholdingTaxRate_id,
                    cis.`payment_mode` AS paymentMode,
                    cis.`term`,
                    cis.`price`
                FROM `canvass_item_supplier` cis
                LEFT JOIN `canvass_item` ci ON cis.`canvass_item_id` = ci.`id`
                WHERE ci.`canvass_id` = :canvass_id
                GROUP BY cis.`supplier_id`
            )
        ");
        $q->execute(array(
            'canvass_id' => base64_decode($get['canvass_id'])
        ));

        $sql = "$select $from $joins $sqlWhere $groupBy $orderBy";
        $query = $conn->prepare($sql);
        $query->execute($stmtParams);
        $result_count = $query->fetchAll();

        $sql = "$select $from $joins $sqlWhere $groupBy $orderBy $limit";
        $query = $conn->prepare($sql);
        $query->execute($stmtParams);
        $result = $query->fetchAll();

        /* Data Count */
        $recordsTotal = count($result_count);

        /*
         * Output
         */
        $output = array(
            "draw" => intval($get['draw']),
            "recordsTotal" => $recordsTotal,
            "recordsFiltered" => $recordsTotal,
            "data" => array()
        );

//        $q = $conn->prepare("
//            SELECT `id`, `code` FROM `currency`
//        ");
//        $q->execute();
//        $currencies = $q->fetchAll();
//
//        $q = $conn->prepare("
//            SELECT `id`, `description`, `rate` FROM `withholding_tax_rate`
//        ");
//        $q->execute();
//        $wTaxRates = $q->fetchAll();

        foreach($result as $row) {

//            $currencyOpts = '';
//            foreach($currencies as $currency) {
//                $currencyOpts .= '<option ' . ($currency['id'] == $row['cvsCurrency_id'] || $currency['id'] == $row['currency_id'] ? 'selected' : '') . ' value="' . $currency['id'] . '">' . $currency['code'] . '</option>';
//            }
//
//            $wtrOpts = '<option></option>';
//            foreach($wTaxRates as $wtr) {
//                $wtrOpts .= '<option ' . ($wtr['id'] == $row['cvsWTaxRate_id'] || $wtr['id'] == $row['wTaxRate_id'] ? 'selected' : '') . ' value="' . $wtr['id'] . '">' . $wtr['description'] . ' (' . number_format($wtr['rate'], 0) . '%)' . '</option>';
//            }

            $payModeOpts = '<option ' . ($row['cvsPaymentMode'] === CanvassItemSupplierEntity::PAYMENT_MODE_COD || $row['paymentMode'] === CanvassItemSupplierEntity::PAYMENT_MODE_COD ? 'selected' : '') . ' value="' . CanvassItemSupplierEntity::PAYMENT_MODE_COD . '">COD</option>';
            $payModeOpts .= '<option  ' . ($row['cvsPaymentMode'] === CanvassItemSupplierEntity::PAYMENT_MODE_TERM || $row['paymentMode'] === CanvassItemSupplierEntity::PAYMENT_MODE_TERM ? 'selected' : '') . ' value="' . CanvassItemSupplierEntity::PAYMENT_MODE_TERM . '">Term</option>';

            /*$action .= "<a href='javascript:void(0)' class='action-button-style href-canvassItemSupplier-form' data-action='n' data-ref-id='$id'>Add Supplier</a>";*/

            $values = array(
                $row['description'],
                $row['mobile_no'],
                $row['phone_no'],
                $row['email'],
                $row['lastCanvassDatePrice'],
//                '<select id="addSupplierForm-currency-' . $row['id'] . '" style="width: 50px;">' . $currencyOpts . '</select>',
                '<select id="addSupplierForm-paymentMode-' . $row['id'] . '" style="width: 50px;">' . $payModeOpts . '</select>',
                '<input value="' . (!empty($row['cvsTerm']) ? $row['cvsTerm'] : (!empty($row['term']) ? $row['term'] : '')) . '" type="text" id="addSupplierForm-term-' . $row['id'] . '" style="width: 75px;" />',
//                '<select id="addSupplierForm-wTaxRate-' . $row['id'] . '" style="width: 100px;">' . $wtrOpts . '</select>',
                //'<input value="' . (!empty($row['cvsPrice']) ? number_format($row['cvsPrice'], 2) : (!empty($row['price']) ? number_format($row['price'], 2) : '')) . '" type="text" id="addSupplierForm-price-' . $row['id'] . '" />',
                '<input type="text" id="addSupplierForm-price-' . $row['id'] . '" />',
                // '<input type="text" id="addSupplierForm-qty-' . $row['id'] . '" />',
                //'<input type="text" id="addSupplierForm-note-' . $row['id'] . '" />',
                '<a href="javascript:void(0)" class="href-addSupplierForm-doAdd" data-id="' . $row['id'] . '">Add</a>'
            );

            $output['data'][] = $values;
        }

        unset($result);

        return $output;
    }

    public function autoCompleteSuggestions($q) {

        $sql = "
            SELECT
                es.`id`,
                es.`description` AS data,
                es.`description` AS value
            FROM `supplier` es
            WHERE es.`description` LIKE :description
            AND (es.`end_date` IS NULL OR es.`end_date` >= :dateNow)
            ORDER BY es.`description`
            LIMIT 0,20
        ";

        $query = $this->getEntityManager()->getConnection()->prepare($sql);

        $query->execute(array(
            'dateNow' => date('Y-m-d'),
            'description' => "%$q%"
        ));

        $result = $query->fetchAll();

        return $result;
    }

    public function autoCompleteSuggestions2($q) {

        $sql = "
            SELECT
                es.`id`,
                CONCAT(IF(es.`code` IS NOT NULL, CONCAT(es.`code`, ' - '), ''), es.`description`) AS data,
                CONCAT(IF(es.`code` IS NOT NULL, CONCAT(es.`code`, ' - '), ''), es.`description`) AS value
            FROM `supplier` es
            WHERE CONCAT(IF(es.`code` IS NOT NULL, CONCAT(es.`code`, ' - '), ''), es.`description`) LIKE :description
            AND es.`status` = 'Approved'
            AND (es.`end_date` IS NULL OR es.`end_date` >= :dateNow)
            ORDER BY es.`code`
            LIMIT 0,20
        ";

        $query = $this->getEntityManager()->getConnection()->prepare($sql);

        $query->execute(array(
            'dateNow' => date('Y-m-d'),
            'description' => "%$q%"
        ));

        $result = $query->fetchAll();

        return $result;
    }

    public function reportSupplier() {

        $sql = "
            SELECT
                s.`id`,
                s.`code` as code,
                s.`description` as description
            FROM `supplier` s
        ";

        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute();

        $result = $query->fetchAll();

        return $result;
    }

    public function countForApprovalByUser($userData, $status) {
        $andWhere = '';
//        $stmtParams = array(
//            'user_id' => $userData['id'],
//            'status' => $status
//        );

        $query = $this->getEntityManager()->getConnection()->prepare("
                SELECT COUNT(DISTINCT sp.`id`) as ctr
                FROM `supplier` sp
                WHERE sp.`status` = 'Pending Approval'
            ");
        $query->execute();
        $query = $query->fetchAll();

        return isset($query[0]) ? $query[0]['ctr'] : 0;
    }
}
