<?php

namespace RxConnexionBundle\Repository;

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

        $errors = array();

        $action = $accountUser_form['action'];


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

            $accountUserExist = $this->getEntityManager()->getRepository('RxConnexionBundle:AccountUserEntity')
                ->createQueryBuilder('ep')
                ->where('ep.id != :id')
                ->andWhere('ep.isDeleted != :isDeleted')
                ->andWhere('ep.username = :username')
                ->setParameters(array(
                    'id' => $accountUser_form['id'],
                    'isDeleted' => 1,
                    'username' => $accountUser_form['username']
                ))
                ->getQuery()->getResult();

            if($accountUserExist)
                $errors[] = 'Username already exist.';

            if(empty($accountUser_form['username'])) {
                $errors[] = 'Username should not be blank.';
            } else {

                if(!preg_match("/^[a-zA-Z0-9]+$/", $accountUser_form['username'])) {
                    $errors[] = 'Username must not contain any special character/spaces.';
                }

                if(strlen($accountUser_form['username']) < 3) {
                    $errors[] = 'Username must be at least 3 characters.';
                }
            }

            if($action === 'n' && empty($accountUser_form['password']['first']) && empty($accountUser_form['password']['second'])) {
                $errors[] = 'Please enter your password.';
            } else if($accountUser_form['password']['first'] !== $accountUser_form['password']['second']) {
                $errors[] = 'Password does not match.';
            }

            if(empty($accountUser_form['first_name'])) {
                $errors[] = 'First name should not be blank.';
            }

            if(empty($accountUser_form['last_name'])) {
                $errors[] = 'Last name should not be blank.';
            }
        }

        return $errors;
    }

    public function ajax_list(array $get){

        $columns = array(
            array('a.`description`', 'a.`description`', 'account_desc'),
            array('cr.`description`', 'cr.`description`', 'account_user_type_desc'),
            array('c.`username`', 'c.`username`'),
            array('c.`full_name`', 'c.`full_name`'),
            array('c.`is_active`', 'c.`is_active`'),
            array('c.`id`', "c.`id`")
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `account_user` c";
        $joins = "LEFT JOIN `account_user_type` cr ON c.`account_user_type_id` = cr.`id`";
        $joins .= " LEFT JOIN `account` a ON cr.`account_id` = a.`id`";
        $sqlWhere = "WHERE c.`is_deleted` = 0";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array();

        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'] . "%";
            }
        }
        if($get['status']){
            if($get['status'] == 1) {
                $status = 1;
            } else {
                $status = 0;
            }

            $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . 'c.`is_active` = :status';
            $stmtParams['status'] = $status;
        }

        /* 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 moa_autocomplete_suggestions($q) {

        $sql = "
            SELECT
                p.`id`,
                p.`full_name` AS data,
                p.`full_name` AS value
            FROM `account_user` p
            LEFT JOIN `account_user_type` aut ON p.`account_user_type_id` = aut.`id`
            WHERE p.`full_name` LIKE :description
            AND (aut.`description` = 'MOA' OR aut.`description` = 'MOA Manager')
            AND p.`is_deleted` = 0 
            ORDER BY p.`full_name`
            LIMIT 0,20
        ";

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

        $query->execute(array(
            'description' => "%$q%"
        ));

        $result = $query->fetchAll();

        return $result;
    }

    public function autocomplete_suggestions($q, $company_id) {

        $stmtParams = array();
        $sqlWhere = '';
        if (!empty($company_id)) {
            $sqlWhere = "
               AND p.`id` IN (SELECT uc.`account_user_id`
                            FROM `account_user_company` uc
                            WHERE uc.`company_id` = :company
                            AND uc.`start_date` <= :now
                            AND (
                                uc.`end_date` IS NULL OR uc.`end_date` >= :now
                            )
                        ) 
            ";

            $stmtParams['company'] = $company_id;
            $stmtParams['now'] = date('Y-m-d');
        }

        $sql = "
            SELECT
                p.`id`,
                p.`full_name` AS data,
                p.`full_name` AS value
            FROM `account_user` p
            LEFT JOIN `account_user_type` aut ON p.`account_user_type_id` = aut.`id`
            WHERE (p.`full_name` LIKE :description OR 
                    p.`username` LIKE :description OR 
                    p.`first_name` LIKE :description OR 
                    p.`last_name` LIKE :description OR 
                    CONCAT_WS(', ',p.`last_name`, p.`first_name`) LIKE :description OR 
                    CONCAT_WS(' ',p.`first_name`, p.`last_name`) LIKE :description
                    )
            AND p.`is_active` = 1
            AND p.`is_deleted` = 0
            $sqlWhere
            ORDER BY p.`full_name`
            LIMIT 0,20
        ";

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

        $stmtParams['description'] = "%$q%";
        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    public function getMOAUserType() {

        $conn = $this->getEntityManager()->getConnection();
        $sql = $conn->prepare(
            "
            SELECT
                au.`full_name`,
                au.`id`
            FROM `account_user` au
            LEFT JOIN `account_user_type` aut ON au.`account_user_type_id` = aut.`id`
            WHERE (aut.`description` = 'MOA' OR aut.`description` = 'MOA Manager')
            AND au.`is_deleted` = 0
        "
        );
        $sql->execute();
        return $sql->fetchAll();
    }

    public function reportLoginTracker($params) {
        $sqlWhere = "";
        $stmtParams = array();

        if (!is_null($params['date_from']) && $params['date_from'] !== 'null' && $params['date_from'] !== 'all' && !empty($params['date_from']) && !is_null($params['date_to']) && $params['date_to'] !== 'null' && $params['date_to'] !== 'all' && !empty($params['date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "lr.`start_date` BETWEEN :date_from AND :date_to";
            $stmtParams['date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
            $stmtParams['date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['date_to'])));
        }

        if(isset($params['users']) && !empty($params['users']) && 'NULL' !== strtoupper($params['users'])){
            $branches = explode(',' , $params['users']);
            if(count($branches) > 0){
                $hf_ors = '';
                foreach($branches as $key => $branchId){
                    $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "lr.`account_user_id` = :users_id_$key";
                    $stmtParams["users_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

        $sql = "
            SELECT
                au.`username`,
                au.`full_name`,
                DATE_FORMAT(lr.`start_date`, '%Y/%m/%d %r') AS dateLog,
                lr.`note` AS statusLog
            FROM `login_tracker` lr
            LEFT JOIN `account_user` au ON au.`username` = lr.`username` 
            $sqlWhere
            ORDER BY lr.`username` DESC, 
        ";

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

        $result = $query->fetchAll();

        return $result;
    }

	public function productivityReport($params) {

		$stmtParams = array();
		$andWhere_encounter = "";
		$andWhere_endorsement = "";
		$andWhere_letter = "";
		$andWhere_invoice = "";
		$andWhere_payments = "";


		if (!is_null($params['date_from']) && $params['date_from'] !== 'null' && $params['date_from'] !== 'all' && !empty($params['date_from']) && !is_null($params['date_to']) && $params['date_to'] !== 'null' && $params['date_to'] !== 'all' && !empty($params['date_to'])) {
			$andWhere_encounter .= " AND pc.`chart_date` BETWEEN :date_from AND :date_to";
			$andWhere_endorsement .= " AND tn.`created_at` BETWEEN :date_from AND :date_to";
			$andWhere_letter .= " AND pl.`created_at` BETWEEN :date_from AND :date_to";
			$andWhere_invoice .= " AND pb.`created_at` BETWEEN :date_from AND :date_to";
			$andWhere_payments .= " AND pp.`created_at` BETWEEN :date_from AND :date_to";

			$stmtParams['date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
			$stmtParams['date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['date_to'])));
		}

		if(isset($params['encounter_type']) && !empty($params['encounter_type']) && 'NULL' !== strtoupper($params['encounter_type'])){
			$encounterTypes = explode(',' , $params['encounter_type']);
			if(count($encounterTypes) > 0){
				$hf_ors = '';
				foreach($encounterTypes as $key => $encounterTypeId){
					$hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "pc.`chart_type_id` = :chartType_id_$key";
					$stmtParams["chartType_id_$key"] = $encounterTypeId;
				}
				$andWhere_encounter .= " AND ($hf_ors) ";
			}
		}

		if(isset($params['branch']) && !empty($params['branch']) && 'NULL' !== strtoupper($params['branch'])){
			$branches = explode(',' , $params['branch']);
			if(count($branches) > 0){
				$hf_ors = '';
				foreach($branches as $key => $branchId){
					$hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "hf.`id` = :healthFacility_id_$key";
					$stmtParams["healthFacility_id_$key"] = $branchId;
				}
				$andWhere_encounter .= " AND ($hf_ors) ";
				$andWhere_endorsement .= " AND ($hf_ors) ";
				$andWhere_letter .= " AND ($hf_ors) ";
				$andWhere_invoice .= " AND ($hf_ors) ";
				$andWhere_payments .= " AND ($hf_ors) ";
			}
		}

		if(isset($params['clinician']) && !empty($params['clinician']) && 'NULL' !== strtoupper($params['clinician'])){
			$clinicians = explode(',' , $params['clinician']);
			if(count($clinicians) > 0){
				$clinician_ors = '';
				foreach($clinicians as $key => $clinicianId){
					$clinician_ors .= (empty($clinician_ors) ? '' : ' OR ') . "au.`provider_id` = :clinician_id_$key";
					$stmtParams["clinician_id_$key"] = $clinicianId;
				}
				$andWhere_encounter .= " AND ($clinician_ors) ";
				$andWhere_endorsement .= " AND ($clinician_ors) ";
				$andWhere_letter .= " AND ($clinician_ors) ";
				$andWhere_invoice .= " AND ($clinician_ors) ";
				$andWhere_payments .= " AND ($clinician_ors) ";
			}
		}

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

		$conn->prepare("CREATE TEMPORARY TABLE IF NOT EXISTS `user_productivity_tmp`
					(
					  `action`        VARCHAR(255),
					  `branch_name`   VARCHAR(255),
					  `branch_id`     INT,
					  `user_name`  	  VARCHAR(255),
					  `user_id`       VARCHAR(255),
					  `details`       VARCHAR(255),
					  `total_count`   BIGINT
					);
					
					INSERT INTO user_productivity_tmp
					SELECT ct.`description`        AS action,
					       hf.`description`        AS branchName,
					       hf.`id`                 AS branchId,
					       p.full_name             AS provider,
					       p.id                    AS providerId,
					       ct.id                   AS details,
					       COUNT(DISTINCT pc.`id`) AS total_count
					FROM `patient_chart` pc
					INNER JOIN `chart_type` ct ON ct.`id` = pc.`chart_type_id`
					INNER JOIN `chart_status` cs ON cs.`id` = pc.`chart_status_id`
					INNER JOIN `health_facility` hf ON hf.`id` = pc.`health_facility_id`
					INNER JOIN `account_user` au ON au.`provider_id` = pc.`provider_id`
					INNER JOIN `provider` p ON p.`id` = pc.`provider_id`
					WHERE pc.`is_deleted` = 0
					  AND cs.`description` = 'Completed'
					  AND au.`is_active` = 1
					  $andWhere_encounter
					GROUP BY hf.id, p.`id`, ct.`id`;
					INSERT INTO user_productivity_tmp
					SELECT
					  'Endorsements'        AS action,
					  hf.description        AS branchName,
					  hf.id                 AS branchId,
					  au.full_name          AS user,
					  au.id                 AS userId,
					  GROUP_CONCAT(tn.note) AS details,
					  COUNT(tn.id)          AS totalCount
					FROM ticket_note tn
					INNER JOIN ticket t ON tn.ticket_id = t.id
					INNER JOIN patient_ticket pt ON t.id = pt.ticket_id
					INNER JOIN patient p ON pt.patient_id = p.id
					INNER JOIN patient_health_facility phf ON phf.`id` = (
					  SELECT phf_tmp.`id`
					  FROM `patient_health_facility` phf_tmp
					  WHERE phf_tmp.`patient_id` = p.`id`
					    AND phf_tmp.`end_date` IS NULL
					    AND phf_tmp.`is_deleted` = 0
					  ORDER BY phf_tmp.`start_date` DESC
					  LIMIT 1
					)
					INNER JOIN health_facility hf on phf.health_facility_id = hf.id
					INNER JOIN account_user au on tn.created_by = au.username
					WHERE t.is_deleted = 0
					  AND au.provider_id IS NOT NULL
					  $andWhere_endorsement
					GROUP BY hf.id, au.id;
					INSERT INTO user_productivity_tmp
					SELECT 'Letters'                    AS action,
					       hf.description               AS branchName,
					       hf.id                        AS branchId,
					       au.full_name                 AS user,
					       au.id                        AS userId,
					       GROUP_CONCAT(pl.letter_type) AS details,
					       COUNT(pl.id)                 AS totalCount
					FROM patient_letter pl
					INNER JOIN patient p ON pl.patient_id = p.id
					INNER JOIN patient_health_facility phf ON phf.`id` = (
					  SELECT phf_tmp.`id`
					  FROM `patient_health_facility` phf_tmp
					  WHERE phf_tmp.`patient_id` = p.`id`
					    AND phf_tmp.`end_date` IS NULL
					    AND phf_tmp.`is_deleted` = 0
					  ORDER BY phf_tmp.`start_date` DESC
					  LIMIT 1
					)
					INNER JOIN health_facility hf on phf.health_facility_id = hf.id
					INNER JOIN account_user au on pl.created_by = au.username
					WHERE pl.is_deleted = 0
					  AND au.provider_id IS NOT NULL
					  $andWhere_letter
					GROUP BY hf.id, au.id;
					INSERT INTO user_productivity_tmp
					SELECT 'Invoices'                  AS action,
					       hf.description              AS branchName,
					       hf.id                       AS branchId,
					       au.full_name                AS user,
					       au.id                       AS userId,
					       GROUP_CONCAT(pb.invoice_no) AS details,
					       COUNT(pb.id)                AS totalCount
					FROM patient_billing pb
					INNER JOIN health_facility hf on pb.health_facility_id = hf.id
					INNER JOIN account_user au ON pb.created_by = au.username
					WHERE pb.is_deleted = 0
					  AND au.provider_id IS NOT NULL
					  $andWhere_invoice
					GROUP BY hf.id, au.id;
					INSERT INTO user_productivity_tmp
					SELECT 'Payments'          AS action,
					       hf.description      AS branchName,
					       hf.id               AS branchId,
					       au.full_name        AS user,
					       au.id               AS userId,
					       GROUP_CONCAT(pp.id) AS details,
					       COUNT(pb.id)        AS totalCount
					FROM patient_payment pp
					INNER JOIN patient_billing pb on pp.patient_billing_id = pb.id
					INNER JOIN health_facility hf on pb.health_facility_id = hf.id
					INNER JOIN account_user au ON pp.created_by = au.username
					WHERE pp.is_deleted = 0
					  AND au.provider_id IS NOT NULL
					  $andWhere_payments
					GROUP BY hf.id, au.id;
		")->execute($stmtParams);

		$query = $conn->prepare("
			SELECT *
			FROM user_productivity_tmp"
		);
		$query->execute();

		$result = $query->fetchAll();

		$conn->prepare("DROP TABLE IF EXISTS user_productivity_tmp;")->execute();

		// GROUPING OF BRANCHES AND ITS ACTIONS & USERS
		$branches = [];
		foreach ($result as $item) {
			$e = $this->in_array_assoc($item['branch_id'], 'id', $branches);

			if ($e['exist']) {
				$branches[$e['key']]['data'][] = $item;
				$branches[$e['key']]['total'] += $item['total_count'];

				$ee = $this->in_array_assoc($item['action'], 'description', $branches[$e['key']]['actions']);
				if (!$ee['exist']) {
					$branches[$e['key']]['actions'][] = ['description' => $item['action'], 'total' => $item['total_count']];
				} else {
					$branches[$e['key']]['actions'][$ee['key']]['total'] += $item['total_count'];
				}

				$eee = $this->in_array_assoc($item['user_id'], 'id', $branches[$e['key']]['users']);
				if (!$eee['exist']) {
					$branches[$e['key']]['users'][] = ['id' => $item['user_id'], 'name' => $item['user_name']];
				}

			} else {
				$branches[] = [
					'id'      => $item['branch_id'],
					'name'    => $item['branch_name'],
					'total'   => $item['total_count'],
					'actions' => [['description' => $item['action'], 'total' => $item['total_count']]],
					'users'   => [['id' => $item['user_id'], 'name' => $item['user_name']]],
					'data'    => [$item],
				];
			}
		}

		return $branches;
	}

	private function in_array_assoc($val, $key, $arr) {

		$in_array = array('exist' => false, 'key' => 0);

		foreach($arr as $rowKey => $row) {
			if($row[$key]==$val) {
				$in_array['exist'] = true;
				$in_array['key'] = $rowKey;
				break;
			}
		}

		return $in_array;
	}

}
