<?php

namespace RxConnexionBundle\Repository;

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

        $errors = array();

        $action = $inventory_form['action'];

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

            $inventoryExist = $this->getEntityManager()->getRepository('RxConnexionBundle:InventoryEntity')
                ->createQueryBuilder('ii')
                ->where('ii.id != :id')
                ->andWhere('ii.item = :item_id')
                ->andWhere('ii.healthFacility = :branch')
                ->andWhere('ii.location = :location')
                ->andWhere('ii.status = :status')
                ->setParameters(array(
                    'id' => $inventory_form['id'],
                    'item_id' => $inventory_form['item'],
                    'branch' => $inventory_form['healthFacility'],
                    'location' => $inventory_form['location'],
                    'status' => $inventory_form['status']
                ))
                ->getQuery()->getResult();

            if($inventoryExist)
                $errors[] = 'Item already exist in the selected branch and location.';

            if(empty($inventory_form['healthFacility']))
                $errors[] = 'Please enter branch.';

//            if(empty($inventory_form['location']))
//                $errors[] = 'Please enter location.';

            if(empty($inventory_form['item']))
                $errors[] = 'Please enter item.';

            if(empty($inventory_form['status']))
                $errors[] = 'Please select status.';
        }

        return $errors;
    }

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

        $columns = array(
            'eb.description',
            'l.description',
            'ei.description',
            'ii.status',
            'ii.quantity',
            'ii.id'
        );
        $dtColumns = array(
            'eb.description as branch_description',
            'l.description as location_description',
            'ei.description as item_description',
            'ii.status',
            'ii.quantity',
            'ii.id'
        );
        $dbColumns = array();
        $asColumns = array();

        $query = $this->getEntityManager()
            ->getRepository('RxConnexionBundle:InventoryEntity')
            ->createQueryBuilder('ii')
            ->select(array_merge($dtColumns, $dbColumns))
            ->leftJoin('ii.branch', 'eb')
            ->leftJoin('ii.location', 'l')
            ->leftJoin('ii.item', 'ei')
            ->where('ii.branch IN (:branchIds)')
            ->setParameter('branchIds', $userData['branchIds']);

        if(isSet($get['branchId']) && $get['branchId'] !== 'all') {

            $query->andWhere('ii.branch = :branchId')
                ->setParameter('branchId', base64_decode($get['branchId']));
        }

        if(isSet($get['locationId']) && $get['locationId'] !== 'all') {

            $query->andWhere('ii.location = :locationId')
                ->setParameter('locationId', base64_decode($get['locationId']));
        }

        if(isSet($get['status']) && $get['status'] !== 'all') {

            $query->andWhere('ii.status = :status')
                ->setParameter('status', $get['status']);
        }

        /*
         * 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_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/inventory/form';

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

        foreach($result as $row) {

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

            $values = array(
                $row['branch_description'],
                $row['location_description'],
                $row['item_description'],
                $row['status'],
                number_format($row['quantity'], 2),
                $hasUpdate ? "<a class='action-button-style' href='$formUrl/u/$id'>Update</a>" : ""
            );

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

        unset($result);

        return $output;
    }

    public function ajax_list(array $get, $userData){
        $columns = array(
            array("b.`description`", "b.`description`", 'branch_description'),
            array("l.`description`", "l.`description`", 'location_description'),
            array("i.`description`", "i.`description`", 'item_description'),
            array("inv.`status`", "inv.`status`"),
            //array("inv.`quantity`", "IF(inv.`quantity` > 0, CONCAT_WS(' ', inv.`quantity`, ium.`description`), 'Out of stock')", 'quantity'),
            array("inv.`quantity`", "ROUND(inv.`quantity`, 0)", 'quantity'),
            array("inv.`id`", "inv.`id`"),
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `inventory` inv";
        $joins = "LEFT JOIN `health_facility` b ON inv.`health_facility_id` = b.`id`";
        $joins .= " LEFT JOIN `location` l ON inv.`location_id` = l.`id`";
        $joins .= " LEFT JOIN `item` i ON inv.`item_id` = i.`id`";
//        $joins .= " LEFT JOIN `item_unit_measurement` ium ON ium.`id` = (
//                                                                        SELECT iumt.`id`
//                                                                        FROM `item_unit_measurement` iumt
//                                                                        WHERE iumt.`id` = i.`id`
//                                                                        LIMIT 1
//                                                                        )";
        $sqlWhere = "";


        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array();
        foreach($columns as $key => $column) {
            $select .= ($key > 0 ? ', ' : ' ') . $column[1] . (isset($column[2]) ? ' AS ' . $column[2] : '');
        }
        if(isset($get['status']) && !empty($get['status']) && $get['status'] !== 'all'){
            $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' inv.`status` = :status ';

            //IF STOCK ON HAND DBTABLES MUST NOT SHOW RECORDS THAT HAS NEGATIVE QTY RECORDS
            if ($get['status'] == 'Stock On Hand') {
                $sqlWhere .= ' AND inv.`quantity` > 0';
            }

            $stmtParams['status'] = $get['status'];
        }

        if(isset($get['branchId']) && !empty($get['branchId']) && $get['branchId'] !== 'all'){
            $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' inv.`health_facility_id` = :branchId ';
            $stmtParams['branchId'] = base64_decode($get['branchId']);
        }

        if(isset($get['locationId']) && !empty($get['locationId']) && $get['locationId'] !== 'all'){
            $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' inv.`location_id` = :locationId ';
            $stmtParams['locationId'] = base64_decode($get['locationId']);
        }

        /*
         * 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'];
        }

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

        $sql = "$select $from $joins $sqlWhere $groupBy $orderBy $limit";
        $query = $this->getEntityManager()->getConnection()->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'];
        $formUrl = $url . '/admin/inventory/form';

        foreach($result as $row) {
            $id = base64_encode($row['id']);
            $hasUpdate = $userData['type'] === 'Super Admin' ||  in_array('Admin Inventory Update', $userData['accesses']);

            $values = array(
                $row['branch_description'],
                $row['location_description'],
                $row['item_description'],
                $row['status'],
                $row['quantity'],
                //number_format($row['quantity'], 2),
                $hasUpdate ? "<a class='action-button-style' href='$formUrl/u/$id'>Details</a>" : ""
            );

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

        unset($result);

        return $output;
    }

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

        $conn = $this->getEntityManager()->getConnection();
        $query = $conn->prepare("
            CREATE TEMPORARY TABLE IF NOT EXISTS `item_price_temp` AS (
                SELECT *
                FROM `item_price`
                WHERE `start_date` <= :today
                AND (`end_date` IS NULL OR `end_date` >= :today)
                ORDER BY `start_date` ASC
            );
        ");
        $query->execute(array(
            'today' => date('Y-m-d 23:59:59')
        ));

        $columns = array(
            array('l.`description`', 'l.`description`', 'location_desc'),
            array('it.`description`', 'it.`description`', 'item_desc'),
            array('i.`quantity`', 'i.`quantity`'),
            array('ip.`price`', 'ip.`price`'),
            array('i.`id`', "i.`id`")
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `inventory` i";
        $joins = "
            LEFT JOIN `location` l ON i.`location_id` = l.`id`
            LEFT JOIN `item` it ON i.`item_id` = it.`id`
            LEFT JOIN (
                SELECT
                    ip.`item_id`,
                    ip.`price`
                FROM `item_price_temp` ip
                GROUP BY ip.`item_id`
            ) ip ON it.`id` = ip.`item_id`
        ";
        $sqlWhere = "WHERE i.`health_facility_id` = :health_facility_id AND i.`status` = 'Stock On Hand' AND it.`for_sale` = 1";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array(
            'health_facility_id' => base64_decode($get['branchId'])
        );

        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'];
        }

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

        $query = $conn->prepare("DROP TABLE IF EXISTS `item_price_temp`;");
        $query->execute();

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

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

        foreach($result as $row) {

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

            $values = array(
                $row['location_desc'],
                $row['item_desc'],
                number_format($row['quantity'], 2),
                number_format($price, 2),
                "<a class='action-button-style href-inventory-add' data-id='$id' data-price='$price'>Add Item</a>"
            );

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

        unset($result);

        return $output;
    }

    public function autoCompleteSuggestions($q, $userData) {

        $params = array(
            'description' => "%{$q['query']}%"
        );
        $andWhere = '';

        if(isSet($q['health_facility_id']) && $q['health_facility_id'] !== 'all') {
            $andWhere .= ' AND ii.`health_facility_id` = :health_facility_id';
            $params['health_facility_id'] = base64_decode($q['health_facility_id']);
        }

//        } else if(isset($userData['branchIds']) && count($userData['branchIds'])) {
//
//            $ors = '';
//            foreach($userData['branchIds'] as $key => $branchId) {
//                $ors .= (empty($ors) ? '' : ' OR ') . 'eb.`id` = :branchId' . $key;
//                $params['branchId'.$key] = $branchId;
//            }
//            if(!empty($ors)) {
//                $andWhere .= ' AND (' . $ors . ')';
//            }
//        }

        if(isSet($q['status']) && $q['status'] !== 'all') {
            $andWhere .= ' AND ii.`status` = :status';
            $params['status'] = $q['status'];
        }

        $sql = "
            SELECT
                ii.`id`,
                CONCAT_WS(' - ', ei.`description`, b.`description`, IF(l.`description` != '' AND l.`description` IS NOT NULL, l.`description`, ''), iu.description, iss.description) AS data,
                CONCAT_WS(' - ', ei.`description`, b.`description`, IF(l.`description` != '' AND l.`description` IS NOT NULL, l.`description`, ''), iu.description, iss.description) AS value,
                inv_so.serialNos,
                ei.is_itemised
            FROM `inventory` ii
            LEFT JOIN `item` ei ON ii.`item_id` = ei.`id`
            LEFT JOIN `health_facility` b ON ii.`health_facility_id` = b.`id`
            LEFT JOIN `location` l ON ii.`location_id` = l.`id`
              LEFT JOIN item_unit iu ON ei.item_unit_id = iu.id
              LEFT JOIN item_size iss ON ei.item_size_id = iss.id
            LEFT JOIN (
              SELECT 
                GROUP_CONCAT(CONCAT(inv_so.id, '~', inv_so.serial_no) ORDER BY CAST(inv_so.serial_no AS INTEGER) ASC) AS serialNos,
                inv_so.inventory_id
              FROM inventory_serial_no inv_so 
              WHERE inv_so.status = 'Available'
              GROUP BY inv_so.inventory_id
            ) inv_so ON ii.id = inv_so.inventory_id
            WHERE (CONCAT_WS(' - ', ei.`description`, b.`description`, IF(l.`description` != '' AND l.`description` IS NOT NULL, l.`description`, ''), iu.description, iss.description) LIKE :description
              		OR ei.code LIKE :description OR ei.code2 LIKE :description)
            $andWhere
            ORDER BY ei.`description`
            LIMIT 0,20
        ";

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

        $query->execute($params);

        $result = $query->fetchAll();

        return $result;
    }

    public function getItemQuantityByStatusAndBranch($item_id, $status, $health_facility_id) {

        $query = $this->getEntityManager()->getConnection()->prepare("
            SELECT 
              SUM(i.`quantity`) AS totalQuantity
            FROM `inventory` i
            WHERE i.`status` = :status
            AND i.`item_id` = :item_id
            AND i.`health_facility_id` = :health_facility_id
            GROUP BY i.`item_id`
        ");
        $query->execute(array(
            'status' => $status,
            'item_id' => $item_id,
            'health_facility_id' => $health_facility_id
        ));
        $query = $query->fetchAll();

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

    public function findWithAvailableQuantityByItemAndBranch($item_id, $health_facility_id) {

        $query = $this->getEntityManager()->getRepository('RxConnexionBundle:InventoryEntity')
            ->createQueryBuilder('i')
            ->where('i.item = :item_id')
            ->andWhere('i.branch = :health_facility_id')
            ->andWhere('i.quantity > 0')
            ->setParameters(array(
                'item_id' => $item_id,
                'health_facility_id' => $health_facility_id
            ));

        return $query->getQuery()->getResult();
    }

    // Report Queries Here!!!

    public function reportInventory($userData, $branch, $location, $status, $item){

        $stmtParams = array();

        $sqlWhere = 'WHERE i.`id` IS NOT NULL';

        if('Super Admin' !== $userData['type']) {
            //$sqlWhere = 'WHERE (i.`health_facility_id` IN (:branchIds))';
            //$stmtParams['branchIds'] = implode(',' , $userData['branchIds']);
        }

        if(isSet($branch) && $branch !== 'all' && $branch != 'null') {
            $sqlWhere .= ' AND (i.`health_facility_id` = :branch)';
            $stmtParams['branch'] = !is_numeric($branch) ? base64_decode($branch) : $branch;
        }

        if(isSet($location) && $location !== 'all' && $location != 'null') {
            $sqlWhere .= ' AND (i.`location_id` = :location)';
            $stmtParams['location'] = !is_numeric($location) ? base64_decode($location) : $location;
        }

        if(isSet($item) && $item !== 'all' && $item != 'null') {
            $sqlWhere .= ' AND (i.`item_id` = :item)';
            $stmtParams['item'] = !is_numeric($item) ? base64_decode($item) : $item;
        }

        if(isSet($status) && $status !== 'all' && $status != 'null') {
            $sqlWhere .= ' AND (i.`status` = :status)';
            $stmtParams['status'] = $status;
            if($status == 'Stock On Hand') {
                $sqlWhere .= ' AND i.`quantity` > 0';
            }
        }

        $sql = "SELECT
                  i.`quantity` as quantity,
                  i.`status` as status,
                  it.`description` as item,
                  b.`description` as branch,
                  l.`description` as location
				FROM `inventory` i
				LEFT JOIN `item` it ON it.`id` = i.`item_id`
				LEFT JOIN `health_facility` b ON b.`id` = i.`health_facility_id`
				LEFT JOIN `location` l ON l.`id` = i.`location_id`
				$sqlWhere
        ";

        $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
        $stmt->execute($stmtParams);
        $result = $stmt->fetchAll();
        return $result;
    }
}
