<?php

namespace RxConnexionBundle\Repository;

/**
 * PatientTicketRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class PatientTicketRepository extends \Doctrine\ORM\EntityRepository
{
    public function patientTicketNote($patient_id) {
        /*
        $sql = "
            SELECT 
              tn.`created_by`,
              tn.`created_at`,
              tn.`note`
            FROM `ticket_note` tn
            LEFT JOIN `ticket` t ON t.`id` = tn.`ticket_id`
            LEFT JOIN `patient_ticket` pt ON t.`id` = pt.`ticket_id`
            WHERE tn.`is_deleted` = 0
            AND pt.`patient_id` = :patient_id
            ORDER BY tn.`created_at` DESC
        ";
        */

        $sql = "
            SELECT 
                  t.`created_by`,
                  t.`created_at`,
                  tn.`note`
            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
                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` 
            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` 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 pt.`patient_id` = :patient_id 
            AND t.`is_deleted` = 0 
            ORDER BY t.`updated_at` DESC
        ";

        $stmtParams['patient_id'] = $patient_id;
        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;

    }
}
