<?php

namespace RxConnexionBundle\Repository;

use http\Env\Request;

class AuditTrailRepository extends \Doctrine\ORM\EntityRepository
{

    public function ajax_list(array $get){

        $fieldUserFullName = "IF(att.`user_type` = 'Account', au.`full_name`, u.`full_name`)";
        $fieldTableNameId = "CONCAT(att.`table_name`, ' / ', att.`table_id`)";
        $columns = array(
            array('att.`created_at`', "DATE_FORMAT(att.`created_at`, '%m/%d/%Y %h:%i:%s %p')", 'createdAt'),
            array('au.`full_name`', $fieldUserFullName, 'userFullName'),
            array('att.`tran_label`', 'att.`tran_label`', 'tranLabel'),
            array('att.`axn`', 'att.`axn`', 'axn'),
            array('att.`table_name`', $fieldTableNameId, 'tableNameId'),
            array('att.`id`', "att.`id`")
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `audit_trail` att";
        $joins = "
            -- LEFT JOIN IF(att.`user_type` = 'Account', `account_user`, `user`) u ON att.`user_id` = u.`id`
            LEFT JOIN `account_user` au ON att.`user_id` = au.`id`
            LEFT JOIN `user` u ON att.`user_id` = u.`id`
        ";
        $sqlWhere = "";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array();

        foreach($columns as $key => $column) {
            $select .= ($key > 0 ? ', ' : ' ') . $column[1] . (isset($column[2]) ? ' AS ' . $column[2] : '');
        }

        /*
         * Ordering
         */
	    $orderBy .= (!empty($orderBy) ? ', ' : 'ORDER BY att.`created_at` DESC');
	    /*foreach($get['columns'] as $key => $column) {
			if($column['orderable']=='true') {
				if(isSet($get['order'])) {
					foreach($get['order'] as $order) {
						if($order['column']==$key) {
							if(1 === $key) {
								$orderBy .= (!empty($orderBy) ? ', ' : 'ORDER BY ') . $fieldUserFullName . (!empty($order['dir']) ? ' ' . $order['dir'] : '');
							} else if(4 === $key) {
								$orderBy .= (!empty($orderBy) ? ', ' : 'ORDER BY ') . $fieldTableNameId . (!empty($order['dir']) ? ' ' . $order['dir'] : '');
							} else {
								$orderBy .= (!empty($orderBy) ? ', ' : 'ORDER BY ') . $columns[$key][0] . (!empty($order['dir']) ? ' ' . $order['dir'] : '');
							}
						}
					}
				}
			}
		}*/

        /*
         * Filtering
         */
        if(isset($get['transaction']) && !empty($get['transaction'])) {
	        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . " att.`tran_label` = :transaction";
	        $stmtParams['transaction'] = $get['transaction'];
        }
        if(isset($get['action']) && !empty($get['action'])) {
	        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . " att.`axn` = :action";
	        $stmtParams['action'] = $get['action'];
        }
        if(isset($get['table']) && !empty($get['table'])) {
	        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . " att.`table_name` = :table";
	        $stmtParams['table'] = $get['table'];
        }
        if(isset($get['id']) && !empty($get['id'])) {
	        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . " att.`table_id` = :id";
	        $stmtParams['id'] = $get['id'];
        }
        /*if(isset($get['search']) && $get['search']['value'] != ''){
            $aLikes = array();
            foreach($get['columns'] as $key => $column) {
                if($column['searchable']=='true') {
                    if(1 === $key) {
                        $aLikes[] = $fieldUserFullName . " LIKE :searchValue";
                    } else if(4 === $key) {
                        $aLikes[] = $fieldTableNameId . ' LIKE :searchValue';
                    } else {
                        $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'];
        }

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

        /*$sql = "$select $from $joins $sqlWhere $groupBy $orderBy";
        $query = $conn->prepare($sql);
        $query->execute($stmtParams);
        $result_count = $query->fetchAll();*/

        $sql = "$select $from $joins $sqlWhere $groupBy $orderBy $limit";
        $query = $conn->prepare($sql);
        $query->execute($stmtParams);
        $result = $query->fetchAll();

        return array(
            'results' => $result,
            'total' => count($result)
        );
    }

    public function patient_history_ajax_list(array $get){

        $columns = array(
            array('at.`axn`', 'at.`axn`'),
            array('at.`created_at`', 'at.`created_at`', 'date'),
            array('au.`full_name`', 'au.`full_name`', 'user'),
            array('at.`data`', 'at.`data`', 'patient_history'),
            array('at.`tran_label`', 'at.`tran_label`', 'label'),
            array('at.`id`', "at.`id`"),
            array('at.`table_name`', "at.`table_name`")
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `audit_trail` at";
        $joins = "LEFT JOIN `account_user` au ON au.`username` = at.`created_by`";
        $sqlWhere = " 
                WHERE (
                    (at.`table_id` = :patient_id AND at.table_name = 'patient') OR 
                    (at.table_name = 'patient_chart' AND at.`table_id` IN(
                        SELECT pc.`id` FROM `patient_chart` pc WHERE pc.`patient_id` = :patient_id                    
                    ))
                    OR
                    (at.`table_name` = 'patient_chart_file' AND at.`table_id` IN(
                        SELECT pcf.`id` FROM `patient_chart_file` pcf
                        LEFT JOIN `patient_chart` pc ON pcf.`patient_chart_id` = pc.`id`  
                        WHERE pc.`patient_id` = :patient_id                    
                    ))
                    OR
                    (at.`table_name` = 'patient_diagnosis' AND at.`table_id` IN(
                        SELECT pd.`id` FROM `patient_diagnosis` pd
                        LEFT JOIN `patient` pat ON pd.`patient_id` = pat.`id`  
                        WHERE pd.`patient_id` = :patient_id               
                    ))
                    OR
                    (at.`table_name` = 'schedule' AND at.`table_id` IN(
                        SELECT s.`id` FROM `schedule` s
                        WHERE s.`patient_id` = :patient_id               
                    ))
                    OR
                    (at.`table_name` = 'patient_insurance' AND at.`table_id` IN(
                        SELECT pins.`id` FROM `patient_insurance` pins
                        WHERE pins.`patient_id` = :patient_id               
                    ))
                    OR
                    (at.`table_name` = 'patient_referring_health_facility' AND at.`table_id` IN(
                        SELECT prhf.`id` FROM `patient_referring_health_facility` prhf
                        WHERE prhf.`patient_id` = :patient_id               
                    ))
                    OR
                    (at.`table_name` = 'patient_diagnosis_medication' AND at.`table_id` IN(
                        SELECT pdm.`id` FROM `patient_diagnosis_medication` pdm
                        LEFT JOIN `medication` med ON med.`id` = pdm.`medication_id`
                        LEFT JOIN (
                                      SELECT pd.`patient_id` patientId, pd.`diagnosis_id` pdiId FROM `patient_diagnosis` pd
                                      LEFT JOIN `patient` pat ON pd.`patient_id` = pat.`id` 
                                   ) tmp_patdia ON tmp_patdia.pdiId = pdm.patient_diagnosis_id
                        WHERE tmp_patdia.`patientId` = :patient_id               
                    ))
                    OR
                    (at.`table_name` = 'patient_referring_provider' AND at.`table_id` IN(
                        SELECT prf.`id` FROM `patient_referring_provider` prf
                        WHERE prf.`patient_id` = :patient_id               
                    ))
                    OR
                    (at.`table_name` = 'patient_file' AND at.`table_id` IN(
                        SELECT pf.`id` FROM `patient_file` pf
                        WHERE pf.`patient_id` = :patient_id               
                    ))
                    OR
                    (at.`table_name` = 'patient_note' AND at.`table_id` IN(
                        SELECT pn.`id` FROM `patient_note` pn
                        WHERE pn.`patient_id` = :patient_id               
                    ))
                    OR
                    (at.`table_name` = 'patient_letter' AND at.`table_id` IN(
                        SELECT pl.`id` FROM `patient_letter` pl
                        WHERE pl.`patient_id` = :patient_id               
                    ))
                    OR
                    (at.`table_name` = 'patient_billing' AND at.`table_id` IN(
                        SELECT pb.`id` FROM `patient_billing` pb
                        WHERE pb.`patient_id` = :patient_id               
                    ))
                    OR
                    (at.`table_name` = 'patient_payment' AND at.`table_id` IN(
                        SELECT pp.`id` FROM `patient_payment` pp
                        WHERE pp.`patient_id` = :patient_id               
                    ))
                    OR
                    (at.`table_name` = 'patient_red_flag' AND at.`table_id` IN(
                        SELECT prf.`id` FROM `patient_red_flag` prf
                        WHERE prf.`patient_id` = :patient_id               
                    ))
                    OR
                    (at.`table_name` = 'patient_service' AND at.`table_id` IN(
                        SELECT ps.`id` FROM `patient_service` ps
                        WHERE ps.`patient_id` = :patient_id               
                    ))
                )";
        $groupBy = "";
        $orderBy = " ORDER BY at.`created_at` DESC";
        $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'] . "%";
            }
        }
//        if($get['status']){
//            if($get['status'] == '0') {
//                $status = 0;
//            } else {
//                $status = 1;
//            }
//            $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . 'astf.`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 detailsById($id) {

        $query = $this->getEntityManager()->getConnection()->prepare("
            SELECT
                att.`id`,
                att.`tran_label` AS tranLabel,
                att.`axn`,
                att.`data`,
                DATE_FORMAT(att.`created_at`, '%m/%d/%Y %h:%i:%s %p') AS createdAt,
                IF(att.`user_type` = 'Account', au.`full_name`, u.`full_name`) AS userFullName
            FROM `audit_trail` att
            LEFT JOIN `account_user` au ON att.`user_id` = au.`id`
            LEFT JOIN `user` u ON att.`user_id` = u.`id`
            WHERE att.`id` = ?
        ");

        $query->execute(array($id));

        $query = $query->fetch(0);

        $query['data'] = json_decode($query['data'], true);

        return $query;
    }

    public function provider_work_schedule_history_ajax_list(array $get){

        $columns = array(
            array('at.`axn`', 'at.`axn`'),
            array('at.`created_at`', 'at.`created_at`', 'date'),
            array('IF(at.`created_by` != "superadmin" , au.`full_name` ,"Super Admin")', 'IF(at.`created_by` != "superadmin",au.`full_name`,"Super Admin")', 'user'),
            array('at.`tran_label`', 'at.`tran_label`', 'label'),
            array('at.`data`', 'at.`data`', 'historyData'),
            array('at.`id`', "at.`id`"),
            array('at.`table_name`', "at.`table_name`")
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `audit_trail` at";
        $joins = "LEFT JOIN `account_user` au ON au.`username` = at.`created_by`";
        $sqlWhere = "WHERE at.`table_name` = :table_name AND at.`table_id` IN(
                        SELECT pws.`id` FROM `provider_work_schedule` pws
                        WHERE pws.`provider_id` = :provider_id
                    )";
        $groupBy = "";
        $orderBy = " ORDER BY at.`created_at` DESC";
        $limit = "";
        $stmtParams = array(
            'table_name' => 'provider_work_schedule',
            'provider_id' => base64_decode($get['provider_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'] . "%";
            }
        }
//        if($get['status']){
//            if($get['status'] == '0') {
//                $status = 0;
//            } else {
//                $status = 1;
//            }
//            $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . 'astf.`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 markeringStaff_work_schedule_history_ajax_list(array $get){

        $columns = array(
            array('at.`axn`', 'at.`axn`'),
            array('at.`created_at`', 'at.`created_at`', 'date'),
            array('IF(at.`created_by` != "superadmin" , au.`full_name` ,"Super Admin")', 'IF(at.`created_by` != "superadmin",au.`full_name`,"Super Admin")', 'user'),
            array('at.`tran_label`', 'at.`tran_label`', 'label'),
            array('at.`data`', 'at.`data`', 'historyData'),
            array('at.`id`', "at.`id`"),
            array('at.`table_name`', "at.`table_name`")
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `audit_trail` at";
        $joins = "LEFT JOIN `account_user` au ON au.`username` = at.`created_by`";
        $sqlWhere = "WHERE at.`table_name` = :table_name AND at.`table_id` IN(
                        SELECT pws.`id` FROM `marketing_staff_work_schedule` pws
                        WHERE pws.`marketing_staff_id` = :marketing_staff_id
                    )";
        $groupBy = "";
        $orderBy = " ORDER BY at.`created_at` DESC";
        $limit = "";
        $stmtParams = array(
            'table_name' => 'marketing_staff_work_schedule',
            'marketing_staff_id' => base64_decode($get['marketingStaff_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'] . "%";
            }
        }
//        if($get['status']){
//            if($get['status'] == '0') {
//                $status = 0;
//            } else {
//                $status = 1;
//            }
//            $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . 'astf.`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 module_list() {

        $sql = "
            SELECT
                c.`table_name`,
                c.`tran_label`
            FROM `audit_trail` c
            GROUP BY c.`tran_label`
            ORDER BY c.`tran_label`
        ";

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

        $result = $query->fetchAll();

        return $result;
    }

    public function report_cbtTracker($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 ") . "aut.`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 ") . "aut.`user_id` = :accountUser";
            $stmtParams['accountUser'] = $params['accountUser'];
        }

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

        $sql = "
            SELECT 
                DATE_FORMAT(aut.`created_at`, '%m/%d/%Y %h:%i:%p') AS dateCreated,
                aut.`table_name` AS tableName, 
                aut.`axn`,
                aut.`user_id`,
                aut.`tran_label` AS label,
                au.`full_name` AS user,
                aut.`data`
            FROM `audit_trail` aut
            LEFT JOIN `account_user` au ON aut.`created_by` = au.`username`
            $sqlWhere
            ORDER BY aut.`created_at` DESC
        ";

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

        $result = $query->fetchAll();

        return $result;
    }
}
