<?php

namespace RxConnexionBundle\Repository;

/**
 * ItemAveCostRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class ItemAveCostRepository extends \Doctrine\ORM\EntityRepository
{

	public function validate($form) {

		$errors = array();

		$action = $form['action'];

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

			$healthFacilityExist = $this->createQueryBuilder('ip')
				->where('ip.id != :id')
				->andWhere('ip.item = :item_id')
				->andWhere('ip.healthFacility = :health_facility')
				->setParameters(array(
					'id' => $form['id'],
					'item_id' => base64_decode($form['item']),
					'health_facility' => $form['health_facility'],
				))
				->getQuery()->getResult();

			if($healthFacilityExist)
				$errors[] = 'Branch already exist.';

			if(empty($form['ave_cost']))
				$errors[] = 'Ave Cost should not be blank.';

			if(empty($form['health_facility']))
				$errors[] = 'Branch should not be blank.';
		}

		return $errors;
	}

	public function ajax_list(array $get){
		$columns = array(
			array('b.`description`', "b.`description`", 'branch_description'),
			array('itc.`ave_cost`', "itc.ave_cost"),
			array('itc.`id`', 'itc.`id`'),
		);
		$asColumns = array();

		$select = "SELECT";
		$from = "FROM `item_ave_cost` itc";
		$joins = " INNER JOIN health_facility b ON itc.health_facility_id = b.id";
		$sqlWhere = "WHERE itc.`item_id` = :item_id";
		$sqlWhere .= "";
		$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 = $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)
		);
	}
}
