<?php

namespace RxConnexionBundle\Repository;

use RxConnexionBundle\Entity\InventoryEntity;
use RxConnexionBundle\Entity\InventorySerialNoEntity;

/**
 * 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("CONCAT_WS(' / ', i.code, i.code2)", "CONCAT_WS(' / ', i.code, i.code2)", 'codes'),
            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`"),
            array("i.`id`", "TO_BASE64(i.`id`)", 'item_id'),
        );
        $asColumns = array(
        	"CONCAT_WS(' / ', i.code, i.code2)"
        );

        $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 = " WHERE i.is_non_inventory = 0 AND i.is_service = 0";


        $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']);
        } elseif($userData['type'] !== 'Super Admin') {
	        $ors = '';
	        if(count($userData['branches'])) {
		        foreach ($userData['branches'] as $key => $branchId) {
			        $ors .= (empty($ors) ? '' : ' OR ') . 'inv.`health_facility_id` = :branchId' . $key;
			        $stmtParams['branchId' . $key] = $branchId;
		        }
		        if (!empty($ors)) {
			        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') .' (' . $ors . ')';
		        }
	        } else {
		        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' inv.health_facility_id < 0';
	        }
        }

        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['codes'],
                "<a href='$url/main/item/detail/{$row['item_id']}' target='_blank'>{$row['item_description']}</a>",
                $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;
        } elseif($userData['type'] !== 'Super Admin') {
	        $ors = '';
	        if(count($userData['branches'])) {
		        foreach ($userData['branches'] as $key => $branchId) {
			        $ors .= (empty($ors) ? '' : ' OR ') . 'i.`health_facility_id` = :branchId' . $key;
			        $stmtParams['branchId' . $key] = $branchId;
		        }
		        if (!empty($ors)) {
			        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') .' (' . $ors . ')';
		        }
	        } else {
		        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' i.health_facility_id < 0';
	        }
        }

        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,
                  it.`id` as item_id,
                  b.`description` as branch,
                  b.id AS branch_id,
       			  iac.ave_cost,
       			  CONCAT_WS(' / ', it.code, it.code2) AS codes
				FROM `inventory` i
				INNER JOIN `item` it ON it.`id` = i.`item_id`
				INNER JOIN `health_facility` b ON b.`id` = i.`health_facility_id`
				LEFT JOIN item_ave_cost iac ON it.id = iac.item_id AND i.health_facility_id = iac.health_facility_id
				$sqlWhere
        ";

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

    public function reportInventory_2($userData, $params){

        $stmtParams = array();

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

	    if(isset($params['branches']) && !is_null($params['branches']) && count($params['branches'])) {
		    $ors = '';
		    foreach ($params['branches'] as $i => $branch) {
			    $ors .= (empty($ors) ? ' ' : ' OR ') . "b.`id` = :branch_$i";
			    $stmtParams["branch_$i"] = base64_decode($branch);
		    }
		    $sqlWhere .= " AND ($ors)";
	    } elseif($userData['type'] !== 'Super Admin') {
		    $ors = '';
		    if(count($userData['branches'])) {
			    foreach ($userData['branches'] as $key => $branchId) {
				    $ors .= (empty($ors) ? '' : ' OR ') . 'b.`id` = :branchId' . $key;
				    $stmtParams['branchId' . $key] = $branchId;
			    }
			    if (!empty($ors)) {
				    $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') .' (' . $ors . ')';
			    }
		    } else {
			    $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' b.id < 0';
		    }
	    }

	    if(isset($params['categories']) && !is_null($params['categories']) && count($params['categories'])) {
		    $ors = '';
		    foreach ($params['categories'] as $i => $categories) {
			    $ors .= (empty($ors) ? ' ' : ' OR ') . "its.`id` = :categories_$i";
			    $stmtParams["categories_$i"] = base64_decode($categories);
		    }
		    $sqlWhere .= " AND ($ors)";
	    }

	    if(isset($params['statuses']) && !is_null($params['statuses']) && count($params['statuses'])) {
	    	$ors = '';
		    foreach ($params['statuses'] as $k => $status) {
			    $ors .= (empty($ors) ? ' ' : ' OR ') . "i.status = :status_$k" /*. ($status == InventoryEntity::STATUS_STOCK_ON_HAND ? ' AND i.quantity > 0' : '')*/;
			    $stmtParams["status_$k"] = $status;
	    	}
		    $sqlWhere .= (empty($sqlWhere) ? ' WHERE ' : ' AND ') . "($ors)";
	    }

	    if(isset($params['item']) && !empty($params['item'])) {
		    $sqlWhere .= ' AND (i.`item_id` = :item)';
		    $stmtParams['item'] = $params['item'];
	    }

        $sql = "SELECT
                  i.`quantity` as quantity,
                  i.`status` as status,
                  it.`description` as item,
                  it.`id` as item_id,
                  b.`description` as branch,
                  b.id AS branch_id,
       			  iac.ave_cost,
       			  CONCAT_WS(' / ', it.code, it.code2) AS codes
				FROM `inventory` i
				INNER JOIN `item` it ON it.`id` = i.`item_id`
			  	LEFT JOIN item_subcategory its ON it.item_subcategory_id = its.id
				INNER JOIN `health_facility` b ON b.`id` = i.`health_facility_id`
				LEFT JOIN item_ave_cost iac ON it.id = iac.item_id AND i.health_facility_id = iac.health_facility_id
				$sqlWhere
        ";

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

    public function reportInventorySerialCode($userData, $params){

        $stmtParams = array();

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

        if(isset($params['branches']) && !is_null($params['branches']) && count($params['branches'])) {
	        $ors = '';
	        foreach ($params['branches'] as $i => $branch) {
		        $ors .= (empty($ors) ? ' ' : ' OR ') . "hf.`id` = :branch_$i";
		        $stmtParams["branch_$i"] = base64_decode($branch);
        	}
	        $sqlWhere .= " AND ($ors)";
        } elseif($userData['type'] !== 'Super Admin') {
	        $ors = '';
	        if(count($userData['branches'])) {
		        foreach ($userData['branches'] as $key => $branchId) {
			        $ors .= (empty($ors) ? '' : ' OR ') . 'hf.`id` = :branchId' . $key;
			        $stmtParams['branchId' . $key] = $branchId;
		        }
		        if (!empty($ors)) {
			        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') .' (' . $ors . ')';
		        }
	        } else {
		        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' hf.id < 0';
	        }
        }

	    if(isset($params['categories']) && !is_null($params['categories']) && count($params['categories'])) {
		    $ors = '';
		    foreach ($params['categories'] as $i => $categories) {
			    $ors .= (empty($ors) ? ' ' : ' OR ') . "i.`item_subcategory_id` = :categories_$i";
			    $stmtParams["categories_$i"] = base64_decode($categories);
		    }
		    $sqlWhere .= " AND ($ors)";
	    }

	    if(isset($params['status']) && !is_null($params['status']) && !empty($params['status'])) {
	        $sqlWhere .= (empty($sqlWhere) ? ' WHERE ' : ' AND ') . "isn.`status` = :status";
		    $stmtParams["status"] = $params['status'];
	    }

	    if(isset($params['item']) && !empty($params['item'])) {
		    $sqlWhere .= ' AND (i.`item_id` = :item)';
		    $stmtParams['item'] = $params['item'];
	    }

	    $sql = "SELECT
       				   inv.id AS inventory_id,
				       i.description AS item,
				       i.id AS item_id,
				       hf.description AS branch,
				       hf.id AS branch_id,
				       isn.status,
				       GROUP_CONCAT(isn.serial_no) AS serialNos,
       				  	CONCAT_WS(' / ', i.code, i.code2) AS item_codes
				FROM inventory_serial_no isn
				INNER JOIN inventory inv on isn.inventory_id = inv.id
				INNER JOIN health_facility hf on inv.health_facility_id = hf.id
				INNER JOIN item i ON inv.item_id = i.id
				$sqlWhere
                GROUP BY i.id, hf.id,isn.status
        ";
        $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
        $stmt->execute($stmtParams);
        $result = $stmt->fetchAll();
        return $result;
    }

    public function reportInventoryTransfer($userData, $params){

        $stmtParams = array();
        $itemStmtParams = array();

        $sqlWhere = 'WHERE hf_from.id != hf_to.id ';
        $itemWhere = 'WHERE tiid.quantity > 0 ';

	    $sqlWhere .= (empty($sqlWhere) ? ' WHERE ' : ' AND ') . "ti.`transfer_date` BETWEEN :date_from AND :date_to";
	    $stmtParams["date_from"] = (new \DateTime($params['date_from']))->format('Y-m-d 00:00:00');
	    $stmtParams["date_to"] = (new \DateTime($params['date_to']))->format('Y-m-d 23:59:59');

        if(isset($params['branch_from']) && !is_null($params['branch_from']) && !empty($params['branch_from'])) {
	        $sqlWhere .= (empty($sqlWhere) ? ' WHERE ' : ' AND ') . "hf_from.`id` = :branch_from";
	        $stmtParams["branch_from"] = base64_decode($params['branch_from']);
        } elseif($userData['type'] !== 'Super Admin') {
	        $ors = '';
	        if(count($userData['branches'])) {
		        foreach ($userData['branches'] as $key => $branchId) {
			        $ors .= (empty($ors) ? '' : ' OR ') . 'hf_from.`id` = :branchId' . $key;
			        $stmtParams['branchId' . $key] = $branchId;
		        }
		        if (!empty($ors)) {
			        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') .' (' . $ors . ')';
		        }
	        } else {
		        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' hf_from.id < 0';
	        }
        }

        if(isset($params['branch_to']) && !is_null($params['branch_to']) && !empty($params['branch_to'])) {
	        $sqlWhere .= (empty($sqlWhere) ? ' WHERE ' : ' AND ') . "hf_to.`id` = :branch_to";
	        $stmtParams["branch_to"] = base64_decode($params['branch_to']);
        } elseif($userData['type'] !== 'Super Admin') {
	        $ors = '';
	        if(count($userData['branches'])) {
		        foreach ($userData['branches'] as $key => $branchId) {
			        $ors .= (empty($ors) ? '' : ' OR ') . 'hf_to.`id` = :branchId' . $key;
			        $stmtParams['branchId' . $key] = $branchId;
		        }
		        if (!empty($ors)) {
			        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') .' (' . $ors . ')';
		        }
	        } else {
		        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' hf_to.id < 0';
	        }
        }

	    if(isset($params['categories']) && !is_null($params['categories']) && count($params['categories'])) {
		    $ors = '';
		    foreach ($params['categories'] as $i => $categories) {
			    $ors .= (empty($ors) ? ' ' : ' OR ') . "i.`item_subcategory_id` = :categories_$i";
			    $itemStmtParams["categories_$i"] = base64_decode($categories);
		    }
		    $itemWhere .= (empty($itemWhere) ? ' WHERE ' : ' AND ') . " ($ors)";
	    }

	    if(isset($params['item']) && !empty($params['item'])) {
		    $itemWhere .= (empty($itemWhere) ? ' WHERE ' : ' AND ') . " i.id = :item";
		    $itemStmtParams['item'] = $params['item'];
	    }

	    $conn = $this->getEntityManager()->getConnection();
	    $conn->prepare("
            SET SESSION group_concat_max_len = 1000000;
			CREATE TEMPORARY TABLE IF NOT EXISTS `transfer_inventory_item_delivery_tmp` (
                INDEX `IDX_000AAA` (`transfer_inventory_id`)
            ) AS (
                SELECT
				       tii.transfer_inventory_id,
				       GROUP_CONCAT(CONCAT_WS('~~',
				         i.id,  
				         i.description, 
				         COALESCE(tiid.quantity, 0.00), 
				         COALESCE(iac.ave_cost, 0.00), 
				         CONCAT_WS(' / ', i.code, i.code2)
			          	) SEPARATOR '@@') AS items
				  FROM transfer_inventory_item_delivery tiid
				  INNER JOIN transfer_inventory_item tii ON tiid.transfer_inventory_item_id = tii.id
				  INNER JOIN transfer_inventory ti on tii.transfer_inventory_id = ti.id
				  INNER JOIN item i on tii.item_id = i.id
				  LEFT JOIN item_ave_cost iac on i.id = iac.item_id AND ti.branch_id_to = iac.health_facility_id
				  $itemWhere
				  GROUP BY tii.transfer_inventory_id
            )
        ")->execute($itemStmtParams);

	    $sql = "
				SELECT
                   ti.id,
                   ti.transfer_no,
                   CONCAT_WS('-', hf_from.id, hf_to.id) AS from_to,
			       hf_from.id AS branch_from_id,
			       hf_from.description AS branch_from,
			       hf_to.id AS branch_to_id,
			       hf_to.description AS branch_to,
			       tiids.items
				FROM transfer_inventory ti
				INNER JOIN health_facility hf_from ON ti.branch_id_from = hf_from.id
				INNER JOIN health_facility hf_to ON ti.branch_id_to = hf_to.id
				INNER JOIN transfer_inventory_item_delivery_tmp tiids ON ti.id = tiids.transfer_inventory_id
				$sqlWhere
				
        ";
        $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
        $stmt->execute($stmtParams);
        $result = $stmt->fetchAll();
        return $result;
    }

    public function reportInventorySold($userData, $params){

        $stmtParams = array();

        $sqlWhere = 'WHERE pb.is_deleted = 0 ';
        $sqlWhere .= (empty($sqlWhere) ? ' WHERE ' : ' AND ') . ' pbi.quantity > 0 ';
        if($params['include_0_item_cost'] === null) {
	        $sqlWhere .= (empty($sqlWhere) ? ' WHERE ' : ' AND ') . ' pbi.sold_value > 0 ';
        }

	    $sqlWhere .= (empty($sqlWhere) ? ' WHERE ' : ' AND ') . "pb.`billing_date` BETWEEN :date_from AND :date_to";
	    $stmtParams["date_from"] = (new \DateTime($params['date_from']))->format('Y-m-d 00:00:00');
	    $stmtParams["date_to"] = (new \DateTime($params['date_to']))->format('Y-m-d 23:59:59');

        if(isset($params['branch']) && !is_null($params['branch']) && !empty($params['branch'])) {
	        $sqlWhere .= (empty($sqlWhere) ? ' WHERE ' : ' AND ') . "hf.`id` = :branch";
	        $stmtParams["branch"] = base64_decode($params['branch']);
        } elseif($userData['type'] !== 'Super Admin') {
	        $ors = '';
	        if(count($userData['branches'])) {
		        foreach ($userData['branches'] as $key => $branchId) {
			        $ors .= (empty($ors) ? '' : ' OR ') . 'hf.`id` = :branchId' . $key;
			        $stmtParams['branchId' . $key] = $branchId;
		        }
		        if (!empty($ors)) {
			        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') .' (' . $ors . ')';
		        }
	        } else {
		        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' hf.id < 0';
	        }
        }

	    if(isset($params['categories']) && !is_null($params['categories']) && count($params['categories'])) {
		    $ors = '';
		    foreach ($params['categories'] as $i => $categories) {
			    $ors .= (empty($ors) ? ' ' : ' OR ') . "i.`item_subcategory_id` = :categories_$i";
			    $stmtParams["categories_$i"] = base64_decode($categories);
		    }
		    $sqlWhere .= (empty($sqlWhere) ? ' WHERE ' : ' AND ') . " ($ors)";
	    }

	    if(isset($params['item']) && !empty($params['item'])) {
		    $sqlWhere .= (empty($sqlWhere) ? ' WHERE ' : ' AND ') . " i.id = :item";
		    $stmtParams['item'] = $params['item'];
	    }

	    $sql = "
				SELECT
				    hf.description AS branch,
				    hf.id AS branch_id,
				    i.description AS item,
				    i.id AS item_id,
                    CONCAT_WS(' / ', i.code, i.code2) AS item_codes,
				    SUM(pbi.quantity) AS totalQty,
				    SUM(pbi.sold_value) AS totalSoldValue,
				    GROUP_CONCAT(CONCAT_WS('~~', pb.id, pb.invoice_no) SEPARATOR '@@') AS invoices
				FROM patient_billing_inventory pbi
				INNER JOIN item i on pbi.item_id = i.id
				INNER JOIN patient_billing pb on pbi.patient_billing_id = pb.id
				INNER JOIN health_facility hf on pb.health_facility_for_address_id = hf.id
				$sqlWhere
				GROUP BY hf.id, i.id
        ";
        $stmt = $this->getEntityManager()->getConnection()->prepare($sql);
        $stmt->execute($stmtParams);
        $result = $stmt->fetchAll();
        return $result;
    }
}
