<?php

namespace RxConnexionBundle\Repository;

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

		$columns = array(
			array('i.`description`',  "CONCAT_WS(' / ', i.code, i.code2, i.description)", 'item'),
			array('isn.`serial_no`',  "IFNULL(isn.`serial_no`, pbi.serial_no)", 'serial_no'),
			array('pb.`invoice_no`', 'pb.`invoice_no`'),
			array('pb.`billing_date`', "DATE_FORMAT(pb.billing_date, '%m/%d/%Y')", 'billingDate'),
			array('pbi.`is_trial`', "IF(pbi.is_trial = 1, 'Trial', 'Sold')", 'status'),
			array('pb.`id`', 'pb.`id`', 'billing_id'),
			array('pbi.`id`', 'pbi.`id`'),
			array('pbi.`is_trial`', "pbi.is_trial"),
			array('pbi.`is_trial_returned`', "pbi.is_trial_returned"),
		);
		$asColumns = array(
			"CONCAT_WS(' / ', i.code, i.code2, i.description)",
			"IF(pbi.is_trial = 1, 'Trial', 'Sold')"
		);

		$select = "SELECT";
		$from = "FROM `patient_billing_inventory` pbi";
		$joins = " INNER JOIN patient_billing pb ON pbi.patient_billing_id = pb.id ";
		$joins .= " INNER JOIN inventory inv ON pbi.inventory_id = inv.id ";
		$joins .= " INNER JOIN item i ON inv.item_id = i.id ";
		$joins .= " LEFT JOIN inventory_serial_no isn ON pbi.id = isn.patient_billing_inventory_id";
		$sqlWhere = "WHERE pb.`patient_id` = :patient_id";
		$sqlWhere .= " AND pb.`is_deleted` = 0";
		$groupBy = "";
		$orderBy = "";
		$limit = "";
		$stmtParams = array(
			'patient_id' => base64_decode($get['patient_id'])
		);

		if (isset($get['type']) && !empty($get['type'])) {
			if ($get['type'] == 'Serialized') {
				$sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' (isn.id IS NOT NULL OR pbi.serial_no IS NOT NULL)';
			}
		}
		if (isset($get['status']) && !empty($get['status']) && in_array($get['status'], ['Trial', 'Sold'])) {
			$sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' pbi.is_trial = :status ';
			$stmtParams['status'] = $get['status'] == 'Trial' ? 1 : 0;
		} elseif (isset($get['status']) && $get['status'] === 'Trial - Returned') {
			$sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' pbi.is_trial = 1 AND pbi.is_trial_returned = 1 ';
		}

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