<?php

namespace RxConnexionBundle\Repository;

/**
 * PurchaseOrderRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class PurchaseOrderRepository extends \Doctrine\ORM\EntityRepository
{
    public function ajax_list(array $get, $userData){

        $columns = array(
//            array('po.`batch_no`', "po.`batch_no`", 'batchNo'),
            array('s.`description`', "s.`description`", 'supplierDesc'),
            array('mr.`material_requisition_no`', "mr.`material_requisition_no`", 'mrNo'),
            array('pob.`poNos`', "pob.`poNos`"),
            array('mr.`request_date`', "DATE_FORMAT(mr.`request_date`, '%m/%d/%Y')", 'requestDate'),
            array('b.`description`', "b.`description`", 'branchDesc'),
//            array('d.`description`', "d.`description`", 'departmentDesc'),
            array('p.`description`', "p.`description`", 'purposeDesc'),
//            array('mr.`specific_purpose`', "mr.`specific_purpose`", 'specificPurpose'),
            array('u.`first_name`', "CONCAT_WS(' ', u.`first_name`, u.`last_name`)", 'userFullname'),
//            array('po.`status`', "po.`status`"),
            array('po.`id`', "po.`id`")
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `purchase_order` po";
        $joins = "
            LEFT JOIN `supplier` s ON po.`supplier_id` = s.`id`
            LEFT JOIN `material_requisition` mr ON po.`material_requisition_id` = mr.`id`
            LEFT JOIN `health_facility` b ON mr.`health_facility_id` = b.`id`
            -- LEFT JOIN `department` d ON mr.`department_id` = d.`id`
            LEFT JOIN `account_user` u ON mr.`account_user_id` = u.`id`
            LEFT JOIN `purpose` p on mr.`purpose_id` = p.`id`
            LEFT JOIN (
                SELECT
                    `purchase_order_id`,
                    GROUP_CONCAT(po_no SEPARATOR '<br>') AS poNos
                FROM `purchase_order_breakdown`
                GROUP BY `purchase_order_id`
            ) pob ON po.`id` = pob.`purchase_order_id`
        ";
        $sqlWhere = "";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array();

        if(isset($get['branch']) && !empty($get['branch']) && 'all' != $get['branch']) {
	        $sqlWhere .= (empty($sqlWhere) ? 'WHERE ' : ' AND ') . "mr.`health_facility_id` = :branch_id";
	        $stmtParams['branch_id'] = base64_decode($get['branch']);
        } elseif('Super Admin' !== $userData['type']) {

            if(count($userData['branches'])) {
                $ors = '';
                foreach($userData['branches'] as $key => $branchId) {
                    $ors .= (!empty($ors) ? ' OR ' : '') . 'mr.`health_facility_id` = :branch_id_' . $key;
                    $stmtParams['branch_id_' . $key] = $branchId;
                }
                $sqlWhere .= (empty($sqlWhere) ? 'WHERE ' : ' AND ') . "($ors)";
            } else { // Don't show anything
                $sqlWhere .= (empty($sqlWhere) ? 'WHERE ' : ' AND ') . "mr.`health_facility_id` < 0";
            }

//            if(count($userData['departmentIds'])) {
//                $ors = '';
//                foreach($userData['departmentIds'] as $key => $departmentId) {
//                    $ors .= (!empty($ors) ? ' OR ' : '') . 'mr.`department_id` = :department_id_' . $key;
//                    $stmtParams['department_id_' . $key] = $departmentId;
//                }
//                $sqlWhere .= (empty($sqlWhere) ? 'WHERE ' : ' AND ') . "($ors)";
//            } else { // Don't show anything
//                $sqlWhere .= (empty($sqlWhere) ? 'WHERE ' : ' AND ') . "mr.`department_id` < 0";
//            }
        }

        if(isset($get['status']) && !empty($get['status']) && 'ALL' !== strtoupper($get['status'])) {
            if($get['status'] == 'Active') {
                $sqlWhere .= (empty($sqlWhere) ? 'WHERE ' : ' AND ') . 'po.`status` IN (:status1, :status2)';
                $stmtParams['status1'] = 'New';
                $stmtParams['status2'] = 'Sent';
            } elseif($get['status'] == 'Closed') {
                $sqlWhere .= (empty($sqlWhere) ? 'WHERE ' : ' AND ') . 'po.`status` IN (:status1, :status2)';
                $stmtParams['status1'] = 'Voided';
                $stmtParams['status2'] = 'Received';
            }

        }

        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') {
                    if(3 === $key) {
                        $aLikes[] = "DATE_FORMAT(mr.`request_date`, '%m/%d/%Y') LIKE :searchValue";
                    } else {
                        $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();

        $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()
        );

        $url = $get['url'];
        $viewUrl = $url . '/admin/material_requisition/purchase_order/view';

        $hasPurchaseOrderViewAccess = $userData['type'] === 'Super Admin' || in_array('Admin Purchase Order', $userData['accesses']);

        foreach($result as $row) {

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

            $action = $hasPurchaseOrderViewAccess ? "<a class='action-button-style' href='$viewUrl/$id'>View</a>" : '';

            $values = array(
//                $row['batchNo'],
                $row['supplierDesc'],
                $row['mrNo'],
                $row['poNos'],
                $row['requestDate'],
                $row['branchDesc'],
//                $row['departmentDesc'],
                $row['purposeDesc'],
//                $row['specificPurpose'],
                $row['userFullname'],
//                $row['status'],
                $action
            );

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

        unset($result);

        return $output;
    }

//    public function getNextBatchNo() {
//
//        $sql = "
//            SELECT
//                ppo.`batch_no`
//            FROM `purchase_order` ppo
//            ORDER BY ppo.`batch_no` DESC
//            LIMIT 1
//        ";
//
//        $query = $this->getEntityManager()->getConnection()->prepare($sql);
//        $query->execute();
//
//        $result = $query->fetch();
//
//        return $result ? $result['batch_no'] + 1 : 1;
//    }

//    public function dashboardData($userData) {
//
//        $andWhere = '';
//        $stmtParams = array();
//
//        if($userData['type'] !== 'Super Admin') {
//            if(count($userData['branchIds'])) {
//                $ors = '';
//                foreach($userData['branchIds'] as $key => $branchId) {
//                    $ors .= (empty($ors) ? '' : ' OR ') . 'mr.`branch_id` = :branchId' . $key;
//                    $stmtParams['branchId' . $key] = $branchId;
//                }
//                if(!empty($ors)) {
//                    $andWhere .= ' AND (' . $ors . ')';
//                }
//            } else {
//                $andWhere .= ' AND mr.`branch_id` < 0';
//            }
//            if(count($userData['departmentIds'])) {
//                $ors = '';
//                foreach($userData['departmentIds'] as $key => $departmentId) {
//                    $ors .= (empty($ors) ? '' : ' OR ') . 'mr.`department_id` = :departmentId' . $key;
//                    $stmtParams['departmentId' . $key] = $departmentId;
//                }
//                if(!empty($ors)) {
//                    $andWhere .= ' AND (' . $ors . ')';
//                }
//            } else {
//                $andWhere .= ' AND mr.`department_id` < 0';
//            }
//        }
//
//        $query = $this->getEntityManager()->getConnection()->prepare("
//            SELECT
//                COUNT(DISTINCT po.`id`) AS ctr,
//                po.`status`
//            FROM `purchase_order` po
//            LEFT JOIN `material_requisition` mr ON po.`material_requisition_id` = mr.`id`
//            WHERE po.`status` = 'New'
//            $andWhere
//            GROUP BY po.`status`
//        ");
//        $query->execute($stmtParams);
//
//        return $query->fetchAll();
//    }

//    public function countForApprovalByUser($userData, $status, $purchaseOrder_id=null) {
//
//        $andWhere = '';
//        $stmtParams = array(
//            'user_id' => $userData['id'],
//            'status' => $status
//        );
//
//        if(!is_null($purchaseOrder_id)) {
//            $andWhere = 'AND c.`id` = :purchaseOrder_id';
//            $stmtParams['purchaseOrder_id'] = $purchaseOrder_id;
//        }
//
//        if($userData['type'] !== 'Super Admin') {
//            if(count($userData['branchIds'])) {
//                $ors = '';
//                foreach($userData['branchIds'] as $key => $branchId) {
//                    $ors .= (empty($ors) ? '' : ' OR ') . 'mr.`branch_id` = :branchId' . $key;
//                    $stmtParams['branchId' . $key] = $branchId;
//                }
//                if(!empty($ors)) {
//                    $andWhere .= ' AND (' . $ors . ')';
//                }
//            } else {
//                $andWhere .= ' AND mr.`branch_id` < 0';
//            }
//            if(count($userData['departmentIds'])) {
//                $ors = '';
//                foreach($userData['departmentIds'] as $key => $departmentId) {
//                    $ors .= (empty($ors) ? '' : ' OR ') . 'mr.`department_id` = :departmentId' . $key;
//                    $stmtParams['departmentId' . $key] = $departmentId;
//                }
//                if(!empty($ors)) {
//                    $andWhere .= ' AND (' . $ors . ')';
//                }
//            } else {
//                $andWhere .= ' AND mr.`department_id` < 0';
//            }
//            $andWhere .= "
//                AND mr.`branch_id` IN (
//                    SELECT sau.`branch_id`
//                    FROM `status_approval_user` sau
//                    LEFT JOIN `status_approval` sa ON sau.`status_approval_id` = sa.`id`
//                    WHERE sau.`user_id` = :user_id
//                    AND sa.`description` = :status
//                    AND sau.`start_date` <= :startDate
//                    AND (sau.`end_date` >= :endDate OR sau.`end_date` IS NULL)
//                )
//            ";
//            $andWhere .= "
//                AND mr.`department_id` IN (
//                    SELECT sau.`department_id`
//                    FROM `status_approval_user` sau
//                    LEFT JOIN `status_approval` sa ON sau.`status_approval_id` = sa.`id`
//                    WHERE sau.`user_id` = :user_id
//                    AND sa.`description` = :status
//                    AND sau.`start_date` <= :startDate
//                    AND (sau.`end_date` >= :endDate OR sau.`end_date` IS NULL)
//                )
//            ";
//            $stmtParams['startDate'] = date('Y-m-d 00:00:00');
//            $stmtParams['endDate'] = date('Y-m-d 23:59:59');
//        }
//
//        $query = $this->getEntityManager()->getConnection()->prepare("
//                SELECT COUNT(DISTINCT c.`id`) as ctr
//                FROM `purchase_order` c
//                LEFT JOIN `material_requisition`mr ON c.`material_requisition_id` = mr.`id`
//                LEFT JOIN `purchase_order_status` cs ON c.`id` = cs.`purchase_order_id` AND cs.`status` = :status AND cs.`batch_no` = c.`approval_batch_no` AND cs.`user_id` = :user_id
//                WHERE c.`status` = :status
//                AND cs.`id` IS NULL
//                $andWhere
//            ");
//        $query->execute($stmtParams);
//        $query = $query->fetchAll();
//
//        return isset($query[0]) ? $query[0]['ctr'] : 0;
//    }
}
