<?php

namespace RxConnexionBundle\Repository;

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

        $errors = array();

        $action = $ticket_form['action'];

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

            /*    $ticketExist = $this->getEntityManager()->getRepository('RxConnexionBundle:TicketEntity')
                    ->createQueryBuilder('ep')
                    ->where('ep.id != :id')
                    ->andWhere('ep.description = :description')
                    ->setParameters(array(
                        'id' => $ticket_form['id'],
                        'description' => $ticket_form['description']
                    ))
                    ->getQuery()->getResult();

                if($ticketExist)
                    $errors[] = 'Description already exist.';*/

            if(empty($ticket_form['accountUser']))
                $errors[] = 'User should not be blank.';

            /*if(empty($ticket_form['patientTickets']))
                $errors[] = 'Patient should not be blank. (Put at least 1 patient).';

            if(empty($ticket_form['patientChartTickets']))
                $errors[] = 'Encounter should not be blank. (Put at least 1 encounter)';*/
        }

        return $errors;
    }

    public function validateFromDashboard($ticket_form) {

        $errors = array();


        if(empty($ticket_form['accountUser']))
                $errors[] = 'User should not be blank.';

        if(empty($ticket_form['note']))
            $errors[] = 'Note should not be blank.';


        return $errors;
    }

    public function ajax_list(array $get, $userData){
        $columns = array(
            array('t.`id`', "t.`id`"),
            array('DATE_FORMAT(t.`created_at` ,"%m/%d/%Y")', 'DATE_FORMAT(t.`created_at` ,"%m/%d/%Y")', 'createdAt'),
            array('au.`full_name`', 'au.`full_name`', 'user'),
            array('ts.`description`', 'ts.`description`', 'ticketStatus'),
            array('tsv.`description`', 'tsv.`description`', 'ticketSeverity'),
            array('pt.`patients`', 'pt.`patients`', 'patients'),
            array('pct.`patientCharts`', 'pct.`patientCharts`', 'patientCharts'),
            array('tn.`note`', 'tn.`note`', 'latestNote')
        );
        $asColumns = array();
        $select = "SELECT";
        $from = "FROM `ticket` t";
        $joins = "LEFT JOIN `account_user` au ON t.`account_user_id` = au.`id`";
        $joins .= " LEFT JOIN `ticket_status` ts ON t.`ticket_status_id` = ts.`id`";
        $joins .= " LEFT JOIN `ticket_severity` tsv ON t.`ticket_severity_id` = tsv.`id`";
        $joins .= " LEFT JOIN `ticket_cc` tcc ON tcc.`ticket_id` = t.`id`";
        $joins .= " LEFT JOIN (
                    SELECT
                        pt.`ticket_id`,
                        GROUP_CONCAT(CONCAT_WS(';', p.`id`, p.`full_name`) SEPARATOR ',' ) AS patients
                    FROM `patient_ticket` pt
                    LEFT JOIN `patient` p  ON pt.`patient_id` = p.`id`
                    WHERE pt.`is_deleted` = 0
                    GROUP BY pt.`ticket_id`
        ) pt ON pt.`ticket_id` = t.`id`";
        $joins .= " LEFT JOIN (
                    SELECT
                        pct.`ticket_id`,
                        GROUP_CONCAT(CONCAT_WS('-', pc.`id`, DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y'), p.`id`, ct.`description`) SEPARATOR ',' ) AS patientCharts
                    FROM `patient_chart_ticket` pct
                    LEFT JOIN `patient_chart` pc  ON pct.`patient_chart_id` = pc.`id`
                    LEFT JOIN `chart_type` ct  ON pc.`chart_type_id` = ct.`id`
                    LEFT JOIN `patient` p  ON pc.`patient_id` = p.`id`
                    WHERE pct.`is_deleted` = 0
                    GROUP BY pct.`ticket_id`
        ) pct ON pct.`ticket_id` = t.`id`";
        $joins .= " LEFT JOIN `ticket_note` tn ON tn.`id` = (
            SELECT tnt.`id`
            FROM `ticket_note` tnt
            WHERE tnt.`ticket_id` = t.`id`
            ORDER BY tnt.`created_at` DESC
            LIMIT 1
        )";
        $stmtParams = array();
        $sqlWhere = "WHERE t.`is_deleted` = 0";
        if($userData['userType'] != 'System'){
            $sqlWhere .= " AND (t.`account_user_id` = :accountUser";
            $sqlWhere .= " OR tcc.`account_user_id` = :cc_accountUser)";
            $stmtParams['accountUser'] = $userData['id'];
            $stmtParams['cc_accountUser'] = $userData['id'];
        }
        $groupBy = " GROUP BY t.`id`";
        $orderBy = "";
        $limit = "";


        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['month'] != "all"){
            $yearMonth = explode("|",$get['month']);
            $year = $yearMonth[0];
            $month = $yearMonth[1];
            $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' YEAR(t.`created_at`) = :year AND MONTH(t.`created_at`) = :month ';
            $stmtParams['year'] = $year;
            $stmtParams['month'] = $month;
        }

        if($get['ticketStatus'] != 'All'){
            $sqlWhere .= " AND t.`ticket_status_id` = :ticketStatus";
            $stmtParams['ticketStatus'] = $get['ticketStatus'];
        }

        if($get['assignedTo'] != ''){
            $sqlWhere .= " AND t.`account_user_id` = :assignedTo";
            $stmtParams['assignedTo'] = $get['assignedTo'];
        }

        /* 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 patient_ajax_list(array $get, $userData){
        $columns = array(
            array('t.`id`', "t.`id`"),
            array('t.`updated_at`', "t.`updated_at`"),
            array('au.`full_name`', 'au.`full_name`', 'user'),
            array('ts.`description`', 'ts.`description`', 'ticketStatus'),
            array('tsv.`description`', 'tsv.`description`', 'ticketSeverity'),
            array('pt.`patients`', 'pt.`patients`', 'patients'),
            array('pct.`patientCharts`', 'pct.`patientCharts`', 'patientCharts'),
            array('tn.`note`', 'tn.`note`', 'latestNote')
        );
        $asColumns = array();
        $select = "SELECT";
        $from = "FROM `ticket` t";
        $joins = "LEFT JOIN `account_user` au ON t.`account_user_id` = au.`id`";
        $joins .= " LEFT JOIN `ticket_status` ts ON t.`ticket_status_id` = ts.`id`";
        $joins .= " LEFT JOIN `ticket_severity` tsv ON t.`ticket_severity_id` = tsv.`id`";
        $joins .= " LEFT JOIN (
                    SELECT
                        p.`id` AS patient_id,
                        pt.`ticket_id`,
                        GROUP_CONCAT(CONCAT_WS(';', p.`id`, p.`full_name`) SEPARATOR ',' ) AS patients
                    FROM `patient_ticket` pt
                    LEFT JOIN `patient` p  ON pt.`patient_id` = p.`id`
                    WHERE pt.`is_deleted` = 0
                    GROUP BY pt.`ticket_id`
        ) pt ON pt.`ticket_id` = t.`id`";
        $joins .= " LEFT JOIN (
                    SELECT
                        pct.`ticket_id`,
                        GROUP_CONCAT(CONCAT_WS('-', pc.`id`, DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y'), p.`id`, ct.`description`) SEPARATOR ',' ) AS patientCharts
                    FROM `patient_chart_ticket` pct
                    LEFT JOIN `patient_chart` pc  ON pct.`patient_chart_id` = pc.`id`
                    LEFT JOIN `chart_type` ct  ON pc.`chart_type_id` = ct.`id`
                    LEFT JOIN `patient` p  ON pc.`patient_id` = p.`id`
                    WHERE pct.`is_deleted` = 0
                    GROUP BY pct.`ticket_id`
        ) pct ON pct.`ticket_id` = t.`id`";
        $joins .= " LEFT JOIN `ticket_note` tn ON tn.`id` = (
            SELECT tnt.`id`
            FROM `ticket_note` tnt
            WHERE tnt.`ticket_id` = t.`id`
            ORDER BY tnt.`created_at` DESC
            LIMIT 1
        )";
        $stmtParams = array(
            'patient_id' => base64_decode($get['patient_id'])
        );
        $sqlWhere = "WHERE pt.`patient_id` = :patient_id";
        $sqlWhere .= " AND t.`is_deleted` = 0";
        if($userData['userType'] != 'System'){
            //$sqlWhere .= " AND  t.`account_user_id` = :accountUser";
            //$stmtParams['accountUser'] = $userData['id'];
        }
        $groupBy = "";
        $orderBy = "ORDER BY t.`updated_at` DESC";
        $limit = "";


        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 autocomplete_suggestions($q) {

        $sql = "
            SELECT
                CONCAT_WS(' - ', p.`id`, au.`full_name`, ts.`description`) AS data,
                CONCAT_WS(' - ', p.`id`, au.`full_name`, ts.`description`) AS value
            FROM `ticket` p
            LEFT JOIN `account_user` au ON p.`account_user_id` = au.`id`
            LEFT JOIN `ticket_status` ts ON p.`ticket_status_id` = ts.`id`
            WHERE CONCAT_WS(' - ', p.`id`, au.`full_name`, ts.`description`) LIKE :description
            AND p.`is_deleted`= 0
            AND ts.`description` != 'Closed'
            ORDER BY p.`id`
            LIMIT 0,20
        ";

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

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

        $result = $query->fetchAll();

        return $result;
    }

    public function dashboardMyTasks($userData) {
        $conn = $this->getEntityManager()->getConnection();
        $andWhere = '';
        $stmtParams = array();
        if(isset($userData['id']) && $userData['type'] != 'Super Admin'){
            $andWhere .= " AND (t.`account_user_id` = :userId";
            $andWhere .= ' OR tcc.`account_user_id` = :userId)';
            $stmtParams['userId'] = $userData['id'];
        }
        $sql = $conn->prepare(
            "
            SELECT
                t.`id` AS ticketId,
                au.`full_name` AS ticketUser,
                ts.`description` AS ticketStatus,
                tsv.`description` AS ticketSeverity,
                DATE_FORMAT(t.`created_at`, '%m/%d/%Y') AS ticketDate,
                tn.`note` AS latestNote,
                pt.`patients` AS patients,
                pct.`patientCharts` AS patientCharts
            FROM `ticket` t
            LEFT JOIN `account_user` au ON t.`account_user_id` = au.`id`
            LEFT JOIN `ticket_status` ts ON t.`ticket_status_id` = ts.`id`
            LEFT JOIN `ticket_severity` tsv ON t.`ticket_severity_id` = tsv.`id`
            LEFT JOIN `ticket_cc` tcc ON tcc.`ticket_id` = t.`id`
            LEFT JOIN (
                    SELECT
                        pt.`ticket_id`,
                        GROUP_CONCAT(CONCAT_WS('|',p.id, p.`full_name`, hf.`description`) SEPARATOR '~' ) AS patients
                    FROM `patient_ticket` pt
                    LEFT JOIN `patient` p  ON pt.`patient_id` = p.`id`
                    LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                          SELECT
                            phf1.`id`
                          FROM `patient_health_facility` phf1
                          WHERE phf1.`end_date` IS NULL
                          AND phf1.`patient_id` = p.`id`
                          ORDER BY phf1.`start_date` DESC
                          LIMIT 1
                    )
                    LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                    WHERE pt.`is_deleted` = 0
                    GROUP BY pt.`ticket_id`
            ) pt ON pt.`ticket_id` = t.`id`
            LEFT JOIN (
                    SELECT
                        pct.`ticket_id`,
                        GROUP_CONCAT(CONCAT_WS('|', pc.`id`, DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y'), p.`id`) SEPARATOR '~' ) AS patientCharts
                    FROM `patient_chart_ticket` pct
                    LEFT JOIN `patient_chart` pc  ON pct.`patient_chart_id` = pc.`id`
                    LEFT JOIN `patient` p  ON pc.`patient_id` = p.`id`
                    WHERE pct.`is_deleted` = 0
                    GROUP BY pct.`ticket_id`
            ) pct ON pct.`ticket_id` = t.`id`
            LEFT JOIN `ticket_note` tn ON tn.`id` = (
                SELECT tnt.`id`
                FROM `ticket_note` tnt
                WHERE tnt.`ticket_id` = t.`id`
                ORDER BY tnt.`created_at` DESC
                LIMIT 1
            )
            WHERE t.`is_deleted` = 0
            AND ts.`description` != 'Closed'
            $andWhere
            GROUP BY t.`id`
        "
        );

        $sql->execute($stmtParams);

        return $sql->fetchAll();

    }

    public function dashboardEndorsed($params) {
        $conn = $this->getEntityManager()->getConnection();
        $stmtParams = array();
        $andWhere = ' AND t.`is_deleted` = 0';

        if(isset($params['status']) && $params['status'] != 'All'){
            $endorsed_status = $params['status'];
            $andWhere .= ' AND t.`ticket_status_id` = :status';
            $stmtParams['status'] = $endorsed_status;
        }

        if(isset($params['endorsed']) && $params['endorsed'] != '' && $params['endorsed'] != 'All'){
            $endorsed_user = $params['endorsed'];
            $andWhere .= ' AND (au.`id` = :endorsed';
            $andWhere .= ' OR tcc.`account_user_id` = :endorsed)';
            $stmtParams['endorsed'] = $endorsed_user;
        }

        if(isset($params['patient']) && $params['patient'] != '' && $params['patient'] != 'All'){
            $andWhere .= ' AND pt.`patient_id` = :patientId';
            $stmtParams['patientId'] = $params['patient'];
        }

        if(isset($params['severity']) && $params['severity'] != 'All'){
            $severity = $params['severity'];
            $andWhere .= ' AND t.`ticket_severity_id` = :severity';
            $stmtParams['severity'] = $severity;
        }

        $sql = $conn->prepare(
            "
            SELECT 
                pt.`patient_id` AS patientId,
                t.`id` AS ticketId,
                au.`full_name` AS ticketUser,
                au_cb.`full_name` AS createdBy,
                DATE_FORMAT(t.`created_at`, '%m/%d/%Y') AS ticketDate,
                ts.`description` AS ticketStatus,
                tsv.`description` AS ticketSeverity,
                temp_pt.`patients` AS patients,
                temp_pct.`patientCharts` AS patientCharts,
                CONCAT('[ ' , DATE_FORMAT(tn.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', tn.`created_by`, ' ] : ', tn.`note`) AS latestNote
            FROM `ticket` t
            LEFT JOIN `patient_ticket` pt ON t.`id` = pt.`ticket_id`
            LEFT JOIN `ticket_status` ts ON t.`ticket_status_id` = ts.`id`
            LEFT JOIN `ticket_severity` tsv ON t.`ticket_severity_id` = tsv.`id`
            LEFT JOIN `account_user` au ON t.`account_user_id` = au.`id`
            LEFT JOIN `account_user` au_cb ON t.`created_by` = au_cb.`username`
            LEFT JOIN `ticket_cc` tcc ON tcc.`ticket_id` = t.`id`
            LEFT JOIN (
                SELECT
                    pt.`ticket_id`,
                    GROUP_CONCAT(CONCAT_WS('|', p.`full_name`, p.`id`, hf.`description`) SEPARATOR '~' ) AS patients,
                    p.`id` AS patientId
                FROM `patient_ticket` pt
                LEFT JOIN `patient` p ON pt.`patient_id` = p.`id`
                LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                      SELECT
                        phf1.`id`
                      FROM `patient_health_facility` phf1
                      WHERE phf1.`end_date` IS NULL
                      AND phf1.`patient_id` = pt.`patient_id`
                      ORDER BY phf1.`start_date` DESC
                      LIMIT 1
                )
                   LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                WHERE pt.`is_deleted` = 0
                GROUP BY pt.`ticket_id`
            ) temp_pt ON temp_pt.`ticket_id` = t.`id`
            LEFT JOIN (
                SELECT
                    pct.`ticket_id`,
                    GROUP_CONCAT(CONCAT_WS('|', pc.`id`, DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y'), ct.`description`, p.`id`, p.`full_name` ) SEPARATOR '~' ) AS patientCharts
                FROM `patient_chart_ticket` pct
                LEFT JOIN `patient_chart` pc  ON pct.`patient_chart_id` = pc.`id`
                LEFT JOIN `chart_type` ct  ON pc.`chart_type_id` = ct.`id`
                LEFT JOIN `patient` p  ON pc.`patient_id` = p.`id`
                WHERE pct.`is_deleted` = 0
                GROUP BY pct.`ticket_id`
            ) temp_pct ON temp_pct.`ticket_id` = t.`id`
            LEFT JOIN `ticket_note` tn ON tn.`id` = (
                SELECT tnt.`id`
                FROM `ticket_note` tnt
                WHERE tnt.`ticket_id` = t.`id`
                ORDER BY tnt.`created_at` DESC
                LIMIT 1
            )
            WHERE 1
            -- ts.`description` != 'Closed'
            $andWhere
            GROUP BY t.`id`
            ORDER BY tn.`created_at` DESC
        "
        );

        $sql->execute($stmtParams);
        return $sql->fetchAll();

    }

    public function reportEndorsedPatientEncounterPerUser($params) {

        $sqlWhere = "WHERE  t.`is_deleted` = 0";
        $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 ") . "t.`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['accountUser']) && !empty($params['accountUser']) && 'NULL' !== strtoupper($params['accountUser'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "t.`account_user_id` = :accountUser";
            $stmtParams['accountUser'] = $params['accountUser'];
        }

        $sql = "
                SELECT
                    au.`full_name` accountUser,
                    COUNT(pt.`patients`) countOfEndorsedPatients
                FROM `ticket` t
                LEFT JOIN `account_user` au ON t.`account_user_id` = au.`id`
                LEFT JOIN `ticket_status` ts ON t.`ticket_status_id` = ts.`id`
                LEFT JOIN `ticket_severity` tsv ON t.`ticket_severity_id` = tsv.`id`
                LEFT JOIN (
                        SELECT
                            pt.`ticket_id`,
                            GROUP_CONCAT(CONCAT_WS('|', p.`full_name`, p.`id`) SEPARATOR '~' ) AS patients
                        FROM `patient_ticket` pt
                        LEFT JOIN `patient` p  ON pt.`patient_id` = p.`id`
                        WHERE pt.`is_deleted` = 0
                        GROUP BY pt.`ticket_id`
                ) pt ON pt.`ticket_id` = t.`id`
                LEFT JOIN (
                        SELECT
                            pct.`ticket_id`,
                            GROUP_CONCAT(CONCAT_WS('|', pc.`id`, DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y'), ct.`description`, p.`id`, p.`full_name` ) SEPARATOR '~' ) AS patientCharts
                        FROM `patient_chart_ticket` pct
                        LEFT JOIN `patient_chart` pc  ON pct.`patient_chart_id` = pc.`id`
                        LEFT JOIN `chart_type` ct  ON pc.`chart_type_id` = ct.`id`
                        LEFT JOIN `patient` p  ON pc.`patient_id` = p.`id`
                        WHERE pct.`is_deleted` = 0
                        GROUP BY pct.`ticket_id`
                ) pct ON pct.`ticket_id` = t.`id`
                LEFT JOIN `ticket_note` tn ON tn.`id` = (
                    SELECT tnt.`id`
                    FROM `ticket_note` tnt
                    WHERE tnt.`ticket_id` = t.`id`
                    ORDER BY tnt.`created_at` DESC
                    LIMIT 1
                )
                $sqlWhere
                AND ts.`description` != 'Closed'
                GROUP BY au.`id`
                ORDER BY tn.`created_at` DESC 
        ";

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

        $result = $query->fetchAll();

        return $result;
    }

    public function reportTicket($params) {

        $sqlWhere = "WHERE (pt.`is_deleted` = 0 OR pt.`is_deleted` IS NOT NULL)";
        $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 ") . "pt.`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['accountUser']) && !empty($params['accountUser']) && 'NULL' !== strtoupper($params['accountUser'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "t.`account_user_id` = :accountUser";
            $stmtParams['accountUser'] = $params['accountUser'];
        }

        if(isset($params['status']) && !empty($params['status']) && 'NULL' !== strtoupper($params['status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "t.`ticket_status_id` = :status";
            $stmtParams['status'] = $params['status'];
        }

        $sql = "
                SELECT
                    p.`id` AS patientId,
                    p.`full_name` AS patientName,
                    t.`id` AS ticketId,
                    ts.`description` AS ticketSeverity,
                    tst.`description` AS ticketStatus,
                    au.`full_name` AS assignedTo,
                    CONCAT('[ ' , DATE_FORMAT(tn.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', tn.`created_by`, ' ] : ', tn.`note`) AS latestNote
                FROM `patient_ticket` pt
                LEFT JOIN `patient` p ON pt.`patient_id` = p.`id`
                LEFT JOIN `ticket` t ON pt.`ticket_id` = t.`id`
                LEFT JOIN `ticket_severity` ts ON t.`ticket_severity_id` = ts.`id`
                LEFT JOIN `ticket_status` tst ON t.`ticket_severity_id` = tst.`id`
                LEFT JOIN `account_user` au ON t.`account_user_id` = au.`id`
                LEFT JOIN `ticket_note` tn ON tn.`id` = (
					 	SELECT tn1.`id`
					 	FROM `ticket_note` tn1
					 	WHERE tn1.`ticket_id` = t.`id`
					 	ORDER BY tn1.`updated_at` DESC
					 	LIMIT 1
				)
                $sqlWhere
                AND ts.`description` != 'Closed'
        ";

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

        $result = $query->fetchAll();

        return $result;
    }
}
