<?php

namespace RxConnexionBundle\Repository;

use RxConnexionBundle\Entity\InventoryEntity;

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

        $errors = array();

        $action = $item_form['action'];

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

            $itemExist = $this->getEntityManager()->getRepository('RxConnexionBundle:ItemEntity')
                ->createQueryBuilder('ep')
                ->where('ep.id != :id')
                ->andWhere('ep.description = :description')
                ->andWhere('ep.isDeleted = 0')
                ->andWhere('ep.endDate IS NULL')
                ->setParameters(array(
                    'id' => $item_form['id'],
                    'description' => $item_form['description'],
                ))
                ->getQuery()->getResult();

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

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

            if(empty($item_form['supplier']))
                $errors[] = 'Supplier should not be blank.';

        }

        return $errors;
    }

    public function ajax_list(array $get){

        $columns = array(
            array('s.`description`', 's.`description`', 'supplierDesc'),
            array('ic.`description`', 'ic.`description`', 'itemCat'),
            array('isc.`description`', 'isc.`description`', 'itemSubCat'),
            array('co.`description`', 'co.`description`', 'itemCompany'),
            array("CONCAT_WS(', ', c.`code`, c.`code2`)", "CONCAT_WS(', ', c.`code`, c.`code2`)", 'code'),
            array('c.`type`', 'c.`type`'),
            array('c.`description`', 'c.`description`'),
            array('c.`start_date`', "DATE_FORMAT(c.`start_date`, '%m/%d/%Y')", 'startDate'),
            array('c.`end_date`', "DATE_FORMAT(c.`end_date`, '%m/%d/%Y')", 'endDate'),
            array('c.`id`', "c.`id`")
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `item` c";
        $joins = "LEFT JOIN `item_subcategory` isc ON c.`item_subcategory_id` = isc.`id`";
        $joins .= " LEFT JOIN `item_category` ic ON isc.`item_category_id` = ic.`id`";
        $joins .= " LEFT JOIN `supplier` s ON s.`id` = c.`supplier_id`";
        $joins .= " LEFT JOIN `company` co ON c.`item_company_id` = co.`id`";
        $sqlWhere = "WHERE c.`is_deleted` = 0";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array();

        if (isset($get['status']) && !empty($get['status'])) {
            switch ($get['status']) {
                case 'Active':
                    $sqlWhere .= (empty($sqlWhere) ? ' WHERE ' : ' AND ') . "c.end_date IS NULL";
                    break;
                case 'Inactive':
                    $sqlWhere .= (empty($sqlWhere) ? ' WHERE ' : ' AND ') . "c.end_date IS NOT NULL";
                    break;
            }
        }

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

        return array(
            'results' => $result,
            'total' => count($result_count)
        );
    }

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

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

	    $itemDesc = "CONCAT_WS(' / ', it.`code` , it.`code2` , it.`description`)";
        $columns = array(
            array('it.`description`', $itemDesc, 'item_desc'),
            //array('iv.qty', 'iv.qty'),
            array('it.`id`', "it.`id`"),
//            array('it.`is_consumable`', 'it.`is_consumable`'),
            array('it.`description`', "it.`description`"),
            array('it.`description`', "it.`description`"),
            array('it.`is_itemised`', "it.`is_itemised`"),
            array('inv_so.`serialNos`', "inv_so.`serialNos`"),
        );
        $asColumns = array(
            $itemDesc
        );
        $select = "SELECT";
        $from = "FROM `item` it";
        $joins = "
            -- LEFT JOIN `item_size` isi ON it.`item_size_id` = isi.`id`
            -- LEFT JOIN `item_unit` iu ON it.`item_unit_id` = iu.`id`
            -- INNER JOIN `inv_tmp` iv ON it.`id` = iv.`item_id`
            INNER JOIN (
                SELECT 
                    inv.`item_id`,
                    SUM(inv.`quantity`) AS qty
                FROM `inventory` inv
                WHERE inv.`health_facility_id` = :branch_id
                AND inv.`status` = :status
                AND inv.quantity > 0 
                GROUP BY inv.`item_id`
            ) iv ON it.id = iv.item_id
            LEFT JOIN (
              SELECT 
                GROUP_CONCAT(CONCAT(inv_so.id, '~', inv_so.serial_no) ORDER BY inv_so.serial_no ASC) AS serialNos,
                inv.item_id
              FROM inventory_serial_no inv_so 
              INNER JOIN inventory inv ON inv.id = inv_so.inventory_id
              WHERE inv_so.status = 'Available'
              AND inv.`health_facility_id` = :branch_id
              AND inv.`status` = :status
              AND inv.quantity > 0 
              GROUP BY inv.item_id
            ) inv_so ON it.id = inv_so.item_id
        ";
        $sqlWhere = ' WHERE it.end_date IS NULL AND (it.is_non_inventory IS NULL OR it.is_non_inventory = 0 OR it.is_non_inventory = "") ';
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array(
	        'branch_id' => base64_decode($get['branchId']),
	        'status' => InventoryEntity::STATUS_STOCK_ON_HAND
        );

        if($get['itemCategory_id'] > 0){
            $sqlWhere = (empty($sqlWhere) ? ' WHERE ' : ' AND ' ) . ' it.`item_category_id` = :itemCategory_id';
            $stmtParams['itemCategory_id'] = $get['itemCategory_id'];
        }

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

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

        /*
         * Output
         */
        $output = array(
            "draw" => intval($get['draw']),
            "recordsTotal" => $recordsTotal,
            "recordsFiltered" => $recordsTotal,
            "data" => array()
        );
        foreach($result as $key => $row) {
            $id = base64_encode($row['id']);
            //$consumable = $row['is_consumable'] == 1 ? 'checked' : '';
            $qty = '';
//            $consumable = '';
            $note = '';
//            $itemIsConsumable = 'false';
            $serialNos = '';
            if(isset($get['itemIdQtys'])){
                if(array_key_exists($row['id'], $get['itemIdQtys'])){
                    $qty = $get['itemIdQtys'][$row['id']]['itemQty'];
//                    $itemIsConsumable = $get['itemIdQtys'][$row['id']]['itemIsConsumable'];
                    $note = $get['itemIdQtys'][$row['id']]['itemNote'];
                    if(isset($get['itemIdQtys'][$row['id']]['serialNos'])) {
	                    foreach ($get['itemIdQtys'][$row['id']]['serialNos'] as $serialNo) {
		                    $opts = '<option></option>';
		                    foreach (explode(',', $row['serialNos']) as $sn) {
			                    $isn = explode('~', $sn);
			                    $selected = $isn[0] == $serialNo['id'] ? 'selected' : '';
			                    $opts .= "<option value='{$isn[0]}' $selected>{$isn[1]}</option>";
		                    }
		                    $select = "<select class='input-small select2 inventory_serial_no_select addItemForm-serialNos-$key'>$opts</select>";
		                    $serialNos .= "<div style='display: flex;'>$select<button type='button' data-key='$key' class='action-button-style remove_serial_input_btn'><i class='fa fa-trash'></i></button></div>";
	                    }
                    }
                }
//                $consumable = $itemIsConsumable === 'true' ? 'checked' : '';
            }
            $values = array(
                $row['item_desc'],
                //number_format($row['qty'], 0),
                "<div style='display: flex;'><input type='text' class='numeric' id='addItemForm-qty-$key' value='$qty'/>" . ($row['is_itemised'] == 1 ? '<button data-serial_nos="'.$row['serialNos'].'" data-is_itemised="1" type="button" class="action-button-style add_serial_input_btn" data-key="'.$key.'"  id="add_serial_input_btn-'.$key.'" ><i class="fa fa-plus"></i></button>' : '') . '</div>',
                $serialNos,
                "<input type='text' id='addItemForm-note-$key' value='$note' />",
//                "<input type='checkbox' id='addItemForm-consumable-$key' $consumable/>",
                "<a class='action-button-style href-item-add' data-key='$key' data-id='$id'>Add Item</a>"
            );

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

        unset($result);

        return $output;
    }

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

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

        $itemDesc = "CONCAT_WS(' / ', it.`code` , it.`code2` , it.`description`)";
        $columns = array(
            //array('it.`description`', "CONCAT(it.`description`, IF(ic.`description` IS NOT NULL, CONCAT(' / ', ic.`description`), ''), IF(ib.`description` IS NOT NULL, CONCAT(' / ', ib.`description`), ''), IF(im.`description` IS NOT NULL, CONCAT(' / ', im.`description`), ''), IF(ico.`description` IS NOT NULL, CONCAT(' / ', ico.`description`), ''), IF(isi.`description` IS NOT NULL, CONCAT(' / ', isi.`description`), ''), IF(iu.`description` IS NOT NULL, CONCAT(' / ', iu.`description`), ''))", 'item_desc'),
            array('s.`description`', 's.`description`', 'supplier_desc'),
            array('it.`description`', $itemDesc, 'item_desc'),
            array('iv.qty', 'iv.qty'),
            array('it.`id`', "it.`id`"),
            array('itc.`percent`', "itc.`percent`"),
        );
        $asColumns = array(
            $itemDesc
        );
        $select = "SELECT";
        $from = "FROM `item` it";
        $joins = "
            -- LEFT JOIN `item_size` isi ON it.`item_size_id` = isi.`id`
            -- LEFT JOIN `item_unit` iu ON it.`item_unit_id` = iu.`id`
            LEFT JOIN `supplier` s ON s.`id` = it.`supplier_id`
            LEFT JOIN `item_subcategory` isub ON isub.`id` = it.`item_subcategory_id`
            LEFT JOIN `item_category` ic ON ic.`id` = isub.`item_category_id`
            LEFT JOIN `inv_tmp` iv ON it.`id` = iv.`item_id`
            LEFT JOIN item_tax_code itc ON itc.item_id = it.id AND itc.health_facility_id = :branchId
        ";
        //$sqlWhere = "WHERE it.`for_sale` = 1";
        $sqlWhere = ' WHERE it.end_date IS NULL AND (it.is_non_inventory IS NULL OR it.is_non_inventory = 0 OR it.is_non_inventory = "")';
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array(
            'branchId' => base64_decode($get['branchId']),
        );

        if($get['itemCategory_id'] > 0){
            $sqlWhere = (empty($sqlWhere) ? ' WHERE ' : ' AND ' ) .  ' isub.`item_category_id` = :itemCategory_id';
            $stmtParams['itemCategory_id'] = $get['itemCategory_id'];
        }

        if($get['supplier_id'] > 0){
            $sqlWhere = 'WHERE it.`supplier_id` = :supplier_id';
            $stmtParams['supplier_id'] = $get['supplier_id'];
        }

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

        $q = $conn->prepare("
            CREATE TEMPORARY TABLE IF NOT EXISTS `inv_tmp` (
                INDEX `IDX_TMP_000AAA` (`item_id`)
            ) AS (
                SELECT 
                    i.`item_id`,
                    SUM(i.`quantity`) AS qty
                FROM `inventory` i
                WHERE i.`health_facility_id` = :branch_id
                AND i.`status` = :status
                GROUP BY i.`item_id`
            )
        ");
        $q->execute(array(
            'branch_id' => base64_decode($get['branchId']),
            'status' => InventoryEntity::STATUS_STOCK_ON_HAND
        ));

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

        foreach($result as $key => $row) {

            $id = base64_encode($row['id']);
            $supplier = $row['supplier_desc'];
            $noSupplier = empty($row['supplier_desc']) || is_null($row['supplier_desc']);
            $disabled = $noSupplier ? 'disabled' : '';

            if($noSupplier && ($userData['type'] === 'Super Admin' || in_array('Admin Supplier New', $userData['accesses']))) {
                $itemUrl =  "/main/item/form/u/$id";
                $supplier .= "<a class='' target='_blank' href='$itemUrl'>Update Supplier</a>";
            }

            /*$measurement_input = "<select id='item-unit-of-measurement-$key' style='width: 120px!important;padding:0px!important;margin-bottom:0px!important'>";
            if(!empty($row['itemUnitMeasurement'])){
                foreach (explode('|',$row['itemUnitMeasurement']) as $iums){
                    $ium = explode('!' , $iums);
                    $measurement_input .=  "<option value='".$ium[0]."' ".($ium[4] ? 'selected' : '')." >" .$ium[1].' - ' .$ium[3]."</option>";
                }
            }*/

            //$measurement_input .=  "</select>";

            $values = array(
                $supplier,
                $row['item_desc'],
                number_format($row['qty'], 0),
                //"<input type='text' id='item-location-description-$key' data-key='$key' class='href-get-item-location item-location-description'/><input type='hidden' id='item-location-id-$key' class='item-location-id' /> ",
                //"<input type='text' id='item-quantity-$key'/>",
                /*   $measurement_input,*/
                //$row['previousPrice'],
                //"<input type='text' id='item-control-no-$key' />",
                "<input $disabled type='text' class='numeric itemForm-computeTotalPrice' id='addItemForm-qty-$key' />",
                "<input $disabled type='text' class='amount itemForm-computeTotalPrice' id='addItemForm-initial-price-$key' />",
                "<input $disabled value='".$row['percent']."' type='text' class='amount itemForm-computeTotalPrice' id='addItemForm-tax-$key' />",
	            "<input $disabled type='hidden' readonly class='amount' id='addItemForm-price-$key' /> <input $disabled type='text' readonly class='amount' id='addItemForm-total-price-$key' />",
                "<input $disabled type='text' id='addItemForm-note-$key' />",
                "<button type='button' class='action-button-style href-item-add'  data-key='$key' data-id='$id'>Add Item</button>"
            );

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

        unset($result);

        return $output;
    }

    public function supplierHistoryForCanvassing($item_id) {

        $q = $this->getEntityManager()->getConnection()->prepare("
            SELECT
                s.`description` AS supplierDesc,
                s.`mobile_no` AS supplierMobileNo,
                s.`phone_no` AS supplierPhoneNo,
                s.`email` AS supplierEmail,
                DATE_FORMAT(cis.`canvass_date`, '%m/%d/%Y') as canvassDate,
                cis.`price`
            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`
            LEFT JOIN `supplier` s ON cis.`supplier_id` = s.`id`
            WHERE mri.`item_id` = ?
            AND cis.`is_selected` = 1
            ORDER BY cis.`canvass_date` DESC
            LIMIT 10
        ");
        $q->execute(array($item_id));

        return $q->fetchAll();
    }

    public function autocomplete_suggestions($request)
    {
        $q = $request->get('query');
        $item_category_id = $request->get('item_category_id');
        $healthFacility_id = $request->get('healthFacility_id');
        $andWhere = '';
        $params = array(
            'description' => "%$q%",
            'now' => DATE('Y-m-d 00:00:00')
        );
        if(isset($item_category_id) && $item_category_id != ''){
            $andWhere .= ' AND ic.`item_category_id` = :itemCat';
            $params['itemCat'] = $item_category_id;
        }
        $isnAndWhere = '';
        if(isset($healthFacility_id) && $healthFacility_id != ''){
	        $isnAndWhere .= ' AND inv.`health_facility_id` = :healthFacility_id';
            $params['healthFacility_id'] = $healthFacility_id;
        }

        $sql = "
            SELECT
                i.`id`,
                i.`code`,
                ip.`price`,
                ic.`description` AS subCat,
                i.`description` AS data,
                i.`description` AS value,
                inv_so.serialNos,
                i.is_itemised
            FROM `item` i
            LEFT JOIN `item_subcategory` ic ON i.`item_subcategory_id` = ic.`id`
            LEFT JOIN `item_price` ip ON i.id = ip.item_id AND ip.`start_date` <= :now AND (ip.`end_date` >= :now OR ip.`end_date` is null) AND ip.`is_deleted` = 0
            LEFT JOIN (
              SELECT 
                GROUP_CONCAT(CONCAT(inv_so.id, '~', inv_so.serial_no) ORDER BY inv_so.serial_no ASC) AS serialNos,
                inv.item_id
              FROM inventory_serial_no inv_so 
              INNER JOIN inventory inv ON inv.id = inv_so.inventory_id $isnAndWhere
              WHERE inv_so.status = 'Available'
              GROUP BY inv.item_id
            ) inv_so ON i.id = inv_so.item_id
            WHERE (i.`description` LIKE :description OR i.code LIKE :description OR i.code2 LIKE :description)
            AND i.`is_deleted` = 0
            AND i.end_date IS NULL
            $andWhere
            ORDER BY i.`description`
            LIMIT 0,20
        ";

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

        $query->execute($params);

        $result = $query->fetchAll();

        return $result;
    }

    public function itemExportToExcel() {

        $sql = "
            SELECT
                i.`id`,
                ic.`description` AS subCat,
                c.`description` AS company,
                i.`code`,
                i.`type`,
                i.`description`,
                i.`start_date`,
                i.`end_date`
            FROM `item` i
            LEFT JOIN `item_subcategory` ic ON i.`item_subcategory_id` = ic.`id`
            LEFT JOIN `company` c ON i.`item_company_id` = c.`id`
            WHERE i.end_date IS NULL 
        ";

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

        $query->execute();

        $result = $query->fetchAll();

        return $result;
    }

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

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

        $columns = array(
            array('i.`description`', 'i.`description`'),
            array('i.`package_item_quantity`', "i.`package_item_quantity`"),
            array('i.`id`', "i.`id`"),
        );
        $asColumns = array();
        $select = "SELECT";
        $from = "FROM `item` i";
        $joins = "";
        $sqlWhere = "WHERE i.`item_id` = :item_id AND i.end_date IS NULL ";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array(
            'item_id' => base64_decode($get['item_id'])
        );

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

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

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

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

        foreach($result as $key => $row) {

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

            if($hasUpdate) {
                $action .= "<a class='action-button-style href-new-item' data-action='u' data-id='$id'>Update</a>";
            }

            $values = array(
                $row['description'],
                $row['package_item_quantity'],
                $action
            );

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

        unset($result);

        return $output;
    }
}
