<?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`',  "isn.`serial_no`"),
			array('pb.`invoice_no`', 'pb.`invoice_no`'),
			array('pb.`billing_date`', "DATE_FORMAT(pb.billing_date, '%m/%d/%Y')", 'billingDate'),
			array('pb.`id`', 'pb.`id`', 'billing_id'),
			array('pbi.`id`', 'pbi.`id`'),
		);
		$asColumns = array(
			"CONCAT_WS(' / ', i.code, i.code2, i.description)"
		);

		$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.inventory_serial_no_id = isn.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'])
		);

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