<?php

namespace RxConnexionBundle\Repository;

use RxConnexionBundle\Entity\PatientBillingEntity;
use RxConnexionBundle\Entity\PatientBillingBreakdownEntity;

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

        $errors = array();

        $action = $patientBilling_form['action'];

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

            if(empty($patientBilling_form['patient'])) {
                $errors[] = 'Something went wrong. Please try again.';
            }

            if(empty($patientBilling_form['healthFacility'])) {
                $errors[] = 'Please select billing branch.';
            }

            if(empty($patientBilling_form['healthFacilityForAddress'])) {
                $errors[] = 'Please select billing address.';
            }

            if(empty($patientBilling_form['billing_date'])) {
                $errors[] = 'Billing date should not be blank.';
            }

            if(empty($patientBilling_form['due_date'])) {
                $errors[] = 'Due date should not be blank.';
            }

            /*if(empty($patientBilling_form['note'])) {
                $errors[] = 'Notes should not be blank.';
            }*/

            /*if(isset($patientBilling_form['invoice_no'])){*/
                /*if(empty($patientBilling_form['invoice_no'])) {
                    $errors[] = 'Invoice no. should not be blank.';
                }*/
            /*}*/

            if(empty($patientBilling_form['payer_type'])) {
                $errors[] = 'Please select payer type.';
            } else if($patientBilling_form['payer_type'] === PatientBillingEntity::PAYER_TYPE_INSURANCE && empty($patientBilling_form['patientInsurance'])) {
                $errors[] = 'You have selected insurance as payer type, please enter the name of the insurance.';
            }

            if(!isset($patientBilling_form['patientBillingBreakdowns'])) {
                $errors[] = 'Please add breakdown.';
            } else {

                foreach($patientBilling_form['patientBillingBreakdowns'] as $key => $patientBillingBreakdown_form) {

                    $bDownNo = $key + 1;

                    if($patientBillingBreakdown_form['type'] === PatientBillingBreakdownEntity::TYPE_CHART_PROCEDURE) {

                        if(empty($patientBillingBreakdown_form['patientChart'])) {
                            $errors[] = 'Please enter encounter in breakdown no. ' . $bDownNo;
                        }

                        if(empty($patientBillingBreakdown_form['chartProcedure'])) {
                            $errors[] = 'Please enter procedure in breakdown no. ' . $bDownNo;
                        }

                    } else if ($patientBillingBreakdown_form['type'] === PatientBillingBreakdownEntity::TYPE_ITEM) {

                        if(empty($patientBillingBreakdown_form['item'])) {
                            $errors[] = 'Please enter item in breakdown no. ' . $bDownNo;
                        }

                        if(empty($patientBillingBreakdown_form['item_type']) && $action == 'n') {
                            $errors[] = 'Please enter item type in breakdown no. ' . $bDownNo;
                        }

                    } else {

                        if(empty($patientBillingBreakdown_form['description'])) {
                            $errors[] = 'Please enter description in breakdown no. ' . $bDownNo;
                        }
                    }

                    if($action=='n'){
                        if(empty($patientBillingBreakdown_form['quantity'])) {
                            $errors[] = 'Please enter quantity in breakdown no. ' . $bDownNo;
                        } /*else if($patientBillingBreakdown_form['quantity']) {
                            $errors[] = 'Please enter a valid numeric value in the quantity field of breakdown no. ' . $bDownNo;
                        }*/
                    }

                    if(empty($patientBillingBreakdown_form['price'])) {
                        $errors[] = 'Please enter price in breakdown no. ' . $bDownNo;
                    } /*else if($patientBillingBreakdown_form['price']) {
                        $errors[] = 'Please enter a valid numeric value in the price field of breakdown no. ' . $bDownNo;
                    }*/
                }
            }
        }

        if(($action === 'v' || $action === 'r') && empty($patientBilling_form['note'])){
            $errors[] = 'Notes should not be blank.';
        }

        return $errors;
    }

    public function validate_return($patientBilling_form) {

        $errors = array();

        $action = $patientBilling_form['action'];

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

            if(empty($patientBilling_form['patient'])) {
                $errors[] = 'Something went wrong. Please try again.';
            }

            if(empty($patientBilling_form['healthFacility'])) {
                $errors[] = 'Please select billing branch.';
            }

            if(empty($patientBilling_form['healthFacilityForAddress'])) {
                $errors[] = 'Please select billing address.';
            }

            if(empty($patientBilling_form['billing_date'])) {
                $errors[] = 'Billing date should not be blank.';
            }

            if(empty($patientBilling_form['due_date'])) {
                $errors[] = 'Due date should not be blank.';
            }

            /*if(empty($patientBilling_form['note'])) {
                $errors[] = 'Notes should not be blank.';
            }*/

            /*if(isset($patientBilling_form['invoice_no'])){*/
            /*if(empty($patientBilling_form['invoice_no'])) {
                $errors[] = 'Invoice no. should not be blank.';
            }*/
            /*}*/

            if(empty($patientBilling_form['payer_type'])) {
                $errors[] = 'Please select payer type.';
            } else if($patientBilling_form['payer_type'] === PatientBillingEntity::PAYER_TYPE_INSURANCE && empty($patientBilling_form['patientInsurance'])) {
                $errors[] = 'You have selected insurance as payer type, please enter the name of the insurance.';
            }

            if(!isset($patientBilling_form['patientBillingBreakdowns'])) {
                $errors[] = 'Please add breakdown.';
            } else {

                foreach($patientBilling_form['patientBillingBreakdowns'] as $key => $patientBillingBreakdown_form) {

                    $bDownNo = $key + 1;

                    if($patientBillingBreakdown_form['type'] === PatientBillingBreakdownEntity::TYPE_CHART_PROCEDURE) {

                        if(empty($patientBillingBreakdown_form['patientChart'])) {
                            $errors[] = 'Please enter encounter in breakdown no. ' . $bDownNo;
                        }

                        if(empty($patientBillingBreakdown_form['chartProcedure'])) {
                            $errors[] = 'Please enter procedure in breakdown no. ' . $bDownNo;
                        }

                    } else if ($patientBillingBreakdown_form['type'] === PatientBillingBreakdownEntity::TYPE_ITEM) {

                        if(empty($patientBillingBreakdown_form['item'])) {
                            $errors[] = 'Please enter item in breakdown no. ' . $bDownNo;
                        }

                    } else {

                        if(empty($patientBillingBreakdown_form['description'])) {
                            $errors[] = 'Please enter description in breakdown no. ' . $bDownNo;
                        }
                    }

                    if(empty($patientBillingBreakdown_form['quantity'])) {
                        $errors[] = 'Please enter quantity in breakdown no. ' . $bDownNo;
                    } /*else if($patientBillingBreakdown_form['quantity']) {
                        $errors[] = 'Please enter a valid numeric value in the quantity field of breakdown no. ' . $bDownNo;
                    }*/

                    if(empty($patientBillingBreakdown_form['price'])) {
                        $errors[] = 'Please enter price in breakdown no. ' . $bDownNo;
                    } /*else if($patientBillingBreakdown_form['price']) {
                        $errors[] = 'Please enter a valid numeric value in the price field of breakdown no. ' . $bDownNo;
                    }*/
                }
            }
        }

        if(($action === 'v' || $action === 'r') && empty($patientBilling_form['note'])){
            $errors[] = 'Notes should not be blank.';
        }

        return $errors;
    }

    public function ajax_list(array $get){

        $patient_id = base64_decode($get['patient_id']);

        $columns = array(
            /*array('pb.`id`', "CONCAT(DATE_FORMAT(pb.`billing_date`, '%y%m'), '-', LPAD(pb.`id`, 6, 0))", 'billingNo'),*/
            array('pb.`billing_date`',  "DATE_FORMAT(pb.`billing_date`, '%m/%d/%Y')", 'billingDate'),
            array('pb.`invoice_no`', "IF((pb.`invoice_no` IS NULL OR pb.`invoice_no` = '') AND pb.`prev_sys_invoice_no` IS NOT NULL, CONCAT(pb.`prev_sys_invoice_no`, ' (Imported)'), pb.`invoice_no`)", 'invoiceNo'),
            array('pb.`billing_status`', 'pb.`billing_status`', 'status'),
            array('hf.`description`', 'hf.`description`', 'billingBranch'),
            /*array("IF(hf.`description` IS NOT NULL, hf.`description`, pb.`prev_sys_invoice_branch`)", "IF(hf.`description` IS NOT NULL, hf.`description`, pb.`prev_sys_invoice_branch`)", 'healthFacility'),*/
            array("IF(hf2.`description` IS NOT NULL, hf2.`description`, pb.`prev_sys_invoice_branch`)", "IF(hf2.`description` IS NOT NULL, hf2.`description`, pb.`prev_sys_invoice_branch`)", 'healthFacility'),
            array('pb.`payer_type`', "IF(pb.`payer_type` = 'Self Pay', 'Self Pay', IF(pb.`payer_type` = 'Financing', IF(fc.`description` IS NULL, 'Financing', fc.`description`), i.`description`))", 'payer'),
            /*array('pbb.`breakdown`', 'pbb.`breakdown`'),*/
            array('pb.`amount_due`', 'pb.`amount_due`', 'amountDue'),
            array('pb.`amount_paid`', 'pb.`amount_paid`', 'amountPaid'),
            array('pb.`amount_refunded`', 'pb.`amount_refunded`', 'amountRefunded'),
            array('pb.`amount_balance`', "IF(pb.`billing_status` != 'Void', pb.`amount_balance`, 0)", 'amountBal'),
            //array('pb.`note`', 'pb.`note`'),
            array('pb.`created_at`', 'pb.`created_at`'),
            array('pb.`id`', 'pb.`id`')
        );
        $asColumns = array(
            'pb.`prev_sys_invoice_no`'
        );

        $select = "SELECT";
        $from = "FROM `patient_billing` pb";
        $joins = "
            LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
            LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id` 
            LEFT JOIN `patient_payment` pp ON pb.`patient_payment_id_financing` = pp.`id`
            LEFT JOIN `financing_company` fc ON pp.`financing_company_id` = fc.`id`
            LEFT JOIN `patient_billing_breakdown_tmp` pbb ON pb.`id` = pbb.`patient_billing_id`
            LEFT JOIN `patient_billing_refund` pbr ON pb.`id` = pbr.`patient_billing_id`
            LEFT JOIN `patient_insurance` pi ON pb.`patient_insurance_id` = pi.`id`
            LEFT JOIN `insurance` i ON pi.`insurance_id` = i.`id`
        ";
        $sqlWhere = "WHERE pb.`patient_id` = :patient_id";
        $sqlWhere .= " AND pb.`is_deleted` = 0";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array(
            'patient_id' => $patient_id
        );

        if($get['status'] != 'All'){
            $sqlWhere .= " AND pb.`billing_status` = :billingStatus";
            $stmtParams['billingStatus'] = $get['status'];
        }

        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'];
        }

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

        $query = $conn->prepare("
            CREATE TEMPORARY TABLE IF NOT EXISTS `patient_billing_breakdown_tmp` (
                INDEX `IDX_TMP_000AAA` (`patient_billing_id`)
            ) AS (
                SELECT
                    pbb.`patient_billing_id`,
                    GROUP_CONCAT(
                      CONCAT_WS(
                          ' - ',
                          IF(pc.`id` IS NOT NULL, CONCAT_WS(' - ', pc.`id`, DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y'), ct.`description`), null),
                          CASE pbb.`type`
                              WHEN 'Chart Procedure' THEN cp.`description`
                              WHEN 'Item' THEN it.`description`
                              ELSE pbb.`description`
                          END,
                          CONCAT(pbb.`quantity`, ' x ', pbb.`price`, ' = ', pbb.`amount`)
                      )
                      SEPARATOR '<br/>'
                    ) AS breakdown
                FROM `patient_billing_breakdown` pbb
                LEFT JOIN `patient_billing` pb ON pbb.`patient_billing_id` = pb.`id`
                LEFT JOIN `patient_chart` pc ON pbb.`patient_chart_id` = pc.`id`
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                LEFT JOIN `chart_procedure` cp ON pbb.`chart_procedure_id` = cp.`id`
                LEFT JOIN `item` it ON pbb.`item_id` = it.`id`
                WHERE pb.`patient_id` = :patient_id
                AND pbb.`is_deleted` = 0
                GROUP BY pbb.`patient_billing_id`
            )
        ");
        $query->execute(array('patient_id' => $patient_id));

        $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_count)
        );
    }

    public function accounts_receiveable_ajax_list(array $get){

        $patient_id = base64_decode($get['patient_id']);

        $columns = array(
            array('pb.`billing_date`',  "DATE_FORMAT(pb.`billing_date`, '%m/%d/%Y')", 'billingDate'),
            array('pb.`invoice_no`', "IF((pb.`invoice_no` IS NULL OR pb.`invoice_no` = '') AND pb.`prev_sys_invoice_no` IS NOT NULL, CONCAT(pb.`prev_sys_invoice_no`, ' (Imported)'), pb.`invoice_no`)", 'invoiceNo'),
            array('pb.`due_date`',  "DATE_FORMAT(pb.`due_date`, '%m/%d/%Y')", 'dueDate'),
            array('pb.`payer_type`', "IF(pb.`payer_type` = 'Self Pay', 'Self Pay', IF(pb.`payer_type` = 'Financing', fc.`description`, i.`description`))", 'payer'),
            array('pb.`amount_due`', 'pb.`amount_due`', 'amountDue'),
            array('pb.`amount_paid`', 'pb.`amount_paid`', 'amountPaid'),
            array('pb.`amount_balance`', 'pb.`amount_balance`', 'amountBal'),
            array('pb.`id`', 'pb.`id`')
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `patient_billing` pb";
        $joins = "
            LEFT JOIN `patient_payment` pp ON pb.`patient_payment_id_financing` = pp.`id`
            LEFT JOIN `financing_company` fc ON pp.`financing_company_id` = fc.`id`
            LEFT JOIN `patient_insurance` pi ON pb.`patient_insurance_id` = pi.`id`
            LEFT JOIN `insurance` i ON pi.`insurance_id` = i.`id`
        ";
        $sqlWhere = "WHERE pb.`patient_id` = :patient_id";
        $sqlWhere .= " AND pb.`is_deleted` = 0";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array(
            'patient_id' => $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'] . "%";
            }
        }

        /* 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_count)
        );
    }

    public function nativeInsert($patientBilling_form) {

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

        $conn->executeQuery("
            SET @invNoCompany = (
                SELECT IF(c.`last_or_number` IS NULL, 0, c.`last_or_number` * 1) as last_or_number 
                FROM `company` c
                WHERE c.`id` = (
                    SELECT hf2.`company_id`
                    FROM `health_facility` hf2
                    WHERE hf2.`id` = :healthFacilityForAddress
                    LIMIT 1
                )
                LIMIT 1
            );
            SET @invNoBilling = (
                SELECT pb.`invoice_no` * 1 as invoice_no
                FROM `patient_billing` pb
                LEFT JOIN `health_facility` hf on pb.`health_facility_for_address_id` = hf.`id`
                WHERE hf.`company_id` = (
                    SELECT hf2.`company_id`
                    FROM `health_facility` hf2
                    WHERE hf2.`id` = :healthFacilityForAddress
                    LIMIT 1
                )
                ORDER BY invoice_no DESC
                LIMIT 1
            );
        ", array('healthFacilityForAddress' => $patientBilling_form['healthFacilityForAddress']));

        $rawQuery = "
            INSERT INTO `patient_billing` (
                `invoice_no`, 
                `serial_no`, 
                `patient_id`, 
                `patient_insurance_id`, 
                `billing_date`, 
                `due_date`, 
                `amount_due`, 
                `payer_type`, 
                `note`, 
                `is_deleted`, 
                `created_by`, 
                `created_at`, 
                `updated_by`, 
                `updated_at`, 
                `billing_status`, 
                `health_facility_id`, 
                `health_facility_for_address_id`, 
                `comment`, 
                `endorsed_to`, 
                `demand_letter_date`, 
                `insurance_claim_date`
            ) VALUES (
                IF(@invNoBilling > @invNoCompany, @invNoBilling, @invNoCompany) + 1, 
                :serial_no, 
                :patient, 
                :patientInsurance, 
                :billing_date, 
                :due_date, 
                :amount_due, 
                :payer_type, 
                :note, 
                0, 
                :created_by, 
                :created_at, 
                :updated_by, 
                :updated_at, 
                :billing_status, 
                :healthFacility, 
                :healthFacilityForAddress, 
                :comment, 
                :endorsed_to, 
                :demand_letter_date, 
                :insurance_claim_date
            );
            -- SET @lastInsertID := LAST_INSERT_ID();
        ";
        $stmtParams = array(
            //'healthFacilityForAddress_id' => $patientBilling_form['healthFacilityForAddress'],
            //'healthFacilityForAddress_company_id' => $healthFacilityForAddress->getCompany()->getId(),
            'serial_no' => $patientBilling_form['serial_no'],
            'patient' => base64_decode($patientBilling_form['patient']),
            'patientInsurance' => !empty($patientBilling_form['patientInsurance']) ? $patientBilling_form['patientInsurance'] : null,
            'billing_date' => date_format(date_create_from_format('m/d/Y', $patientBilling_form['billing_date']), 'Y-m-d 00:00:00'),
            'due_date' => date_format(date_create_from_format('m/d/Y', $patientBilling_form['due_date']), 'Y-m-d 00:00:00'),
            'amount_due' => $patientBilling_form['amount_due'],
            'payer_type' => $patientBilling_form['payer_type'],
            'note' => $patientBilling_form['note'],
            'created_by' => isSet($_COOKIE['username']) ? $_COOKIE['username'] : 'System',
            'created_at' => date('Y-m-d H:i:s'),
            'updated_by' => isSet($_COOKIE['username']) ? $_COOKIE['username'] : 'System',
            'updated_at' => date('Y-m-d H:i:s'),
            'billing_status' => PatientBillingEntity::BILLING_STATUS_PENDING_PAYMENT,
            'healthFacility' => $patientBilling_form['healthFacility'],
            'healthFacilityForAddress' => $patientBilling_form['healthFacilityForAddress'],
            'comment' => $patientBilling_form['comment'],
            'endorsed_to' => $patientBilling_form['endorsed_to'],
            'demand_letter_date' => !empty($patientBilling_form['demand_letter_date']) ? date_format(date_create_from_format('m/d/Y', $patientBilling_form['demand_letter_date']), 'Y-m-d 00:00:00') : null,
            'insurance_claim_date' => !empty($patientBilling_form['insurance_claim_date']) ? date_format(date_create_from_format('m/d/Y', $patientBilling_form['insurance_claim_date']), 'Y-m-d 00:00:00') : null,
        );

        // We need to execute the insert of patient billing table here so that we can get the ID into a return variable
        // This is because we can't perform a select statement after an insert statement, we need to release the buffer in order to do that
        // thus we will lose the value of @lastInsertID variable

        // We need to assign this in a variable so that we will have a in the lastInsertId() method
        $stmt = $conn->executeQuery($rawQuery, $stmtParams);
        $patientBilling_id = $conn->lastInsertId();
        // We also need to call closeCursor in order to release buffered data otherwise it will cause general error: 2014
        $stmt->closeCursor();

        $rawQuery = '';
        $stmtParams = array(
            'patientBilling_id' => $patientBilling_id,
            'created_by' => isSet($_COOKIE['username']) ? $_COOKIE['username'] : 'System',
            'created_at' => date('Y-m-d H:i:s'),
            'updated_by' => isSet($_COOKIE['username']) ? $_COOKIE['username'] : 'System',
            'updated_at' => date('Y-m-d H:i:s')
        );
        foreach($patientBilling_form['patientBillingBreakdowns'] AS $key => $breakdown) {
            $rawQuery .= "
                INSERT INTO `patient_billing_breakdown` (
                  `patient_billing_id`, 
                  `patient_chart_id`, 
                  `chart_procedure_id`, 
                  `item_id`, 
                  `type`, 
                  `description`, 
                  `serial_number`, 
                  `item_type`, 
                  `term`, 
                  `quantity`, 
                  `invoice_quantity`, 
                  `price`, 
                  `invoice_price`, 
                  `amount`, 
                  `invoice_amount`, 
                  `is_deleted`, 
                  `created_by`, 
                  `created_at`, 
                  `updated_by`, 
                  `updated_at`
                ) VALUES (
                  :patientBilling_id, 
                  :patientChart_$key, 
                  :chartProcedure_$key, 
                  :item_$key, 
                  :type_$key, 
                  :description_$key, 
                  :serial_number_$key, 
                  :item_type_$key,
                  :term_$key,
                  :quantity_$key, 
                  :quantity_$key, 
                  :price_$key, 
                  :price_$key, 
                  :amount_$key, 
                  :amount_$key, 
                  0,
                  :created_by, 
                  :created_at, 
                  :updated_by, 
                  :updated_at
                );
            ";
            $stmtParams['patientChart_' . $key] = isset($breakdown['patientChart']) ? $breakdown['patientChart'] : null;
            $stmtParams['chartProcedure_' . $key] = isset($breakdown['chartProcedure']) ? $breakdown['chartProcedure'] : null;
            $stmtParams['item_' . $key] = isset($breakdown['item']) ? $breakdown['item'] : null;
            $stmtParams['type_' . $key] = $breakdown['type'];
            $stmtParams['description_' . $key] = isset($breakdown['description']) ? $breakdown['description'] : null;
            $stmtParams['serial_number_' . $key] = isset($breakdown['serialNumber']) ? $breakdown['serialNumber'] : null;
            $stmtParams['item_type_' . $key] = isset($breakdown['item_type']) ? $breakdown['item_type'] : null;
            $stmtParams['term_' . $key] = isset($breakdown['term']) ? $breakdown['term'] : null;
            $stmtParams['quantity_' . $key] = $breakdown['quantity'];
            $stmtParams['price_' . $key] = preg_replace("/[^0-9.]/", "", $breakdown['price']);
            $stmtParams['amount_' . $key] = preg_replace("/[^0-9.]/", "", $breakdown['amount']);
        }

        /*$rawQuery .= "
            SELECT @lastInsertID AS lastInsertId;
        ";*/

        $stmt = $conn->executeQuery($rawQuery, $stmtParams);
        $conn->commit();

        return $patientBilling_id;
    }

    public function reportInvoice($params) {
        $sqlWhere = "WHERE pb.`is_deleted` = 0";
        $sqlWhere2 = "";
        $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 ") . "pb.`billing_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['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
            $companies = explode(',' , $params['company']);
            if(count($companies) > 0){
                $co_ors = '';
                foreach($companies as $key => $companyId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "hf2.`company_id` = :company_id_$key";
                    $stmtParams["company_id_$key"] = $companyId;
                }
                $sqlWhere .= " AND ($co_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 ') . "pb.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

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

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

        if(isset($params['status']) && !empty($params['status']) && 'NULL' !== strtoupper($params['status'])) {
            $statuses = explode(',', $params['status']);
            if(count($statuses) > 0) {
                $ors = '';
                foreach ($statuses as $key => $status) {
                    $ors .= (empty($ors) ? "" : " OR ") . "pb.`billing_status` = :status_$key";
                    $stmtParams["status_$key"] = $status;
                }
                $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "($ors)";
            }
            //$sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`billing_status` = :status";
            //$stmtParams['status'] = $params['status'];
        }

        if($params['company'] == 'null' && $params['branch'] == 'null') {
            if(!is_null($params['userData']) && !empty($params['userData'])) {
                if ($params['userData']['type'] != 'Super Admin') {
                    if (count($params['userData']['company']['companyBranches']) > 0) {
                        $hf_ors = '';
                        foreach ($params['userData']['company']['companyBranches'] as $key => $branchId) {
                            $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "pb.`health_facility_for_address_id` = $branchId";
                        }
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " ($hf_ors) ";
                    } else {
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " (1 != 1) ";
                    }
                }
            }
        }

        if(isset($params['type']) && !empty($params['type']) && 'NULL' !== strtoupper($params['type'])) {
            switch ($params['type']) {
                case 'All' :
                    $sqlWhere .= "";
                    break;
                case 'cpap_package':
                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb.`item_type` = 'CPAP Package'";
                    break;
                case 'accessory':
                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "(pbb.`item_type` = 'Accessory' OR pbb.`is_accessory` = 1)";
                    break;
                case 'medical_supplies':
                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb.`item_type` = 'Medical Supplies'";
            }

        }

        if(isset($params['term']) && !empty($params['term']) && 'NULL' !== strtoupper($params['term'])) {
            switch ($params['term']) {
                case 'All' :
                    $sqlWhere .= "";
                    $sqlWhere2 .= "";
                    break;
                case 'long_term':
                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb.`term` = 'Long Term Rental'";
                    $sqlWhere2 .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb1.`term` = 'Long Term Rental'";
                    break;
                case 'rent_to_own':
                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb.`term` = 'Rent To Own'";
                    $sqlWhere2 .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb1.`term` = 'Rent To Own'";
            }

        }

        $sql = "
            SELECT 
                pat.`id` patientId,
                IF(pb.`invoice_no` IS NOT NULL, pb.`invoice_no`, pb.`prev_sys_invoice_no`) invoiceNo,
                DATE_FORMAT(pb.`billing_date`, '%m/%d/%Y') billingDate,
                pat.`full_name` AS patient,
                co.`description` AS companyDesc,
                IF(pat.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf.`description`), pb.`prev_sys_invoice_branch`) ) healthFacility,
                IF(pat.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf2.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf2.`description`), pb.`prev_sys_invoice_branch`) ) healthFacilityAddress,
                pb.`amount_refunded` AS amtRefunded,
                pb.`amount_paid` AS amtPaid,
                IF(pb.billing_status IN ('Void', 'Returned'), 0, pb.`amount_balance`) AS amtBalance,
                -- pb.`amount_due` AS amtDue,
                pbb2.`amtDue`,
                
                IF(pb.billing_status IN ('Void', 'Returned'), 0, pb.`amount_refunded`) AS amtRefunded_forTotal,
                IF(pb.billing_status IN ('Void', 'Returned'), 0, pb.`amount_paid`) AS amtPaid_forTotal,
                IF(pb.billing_status IN ('Void', 'Returned'), 0, pb.`amount_balance`) AS amtBalance_forTotal,
                IF(pb.billing_status IN ('Void', 'Returned'), 0, pbb2.`amtDue`) AS amtDue_forTotal,
                
                pb.`billing_status` AS billingStatus,
                pb.`note`,
                pp.totalAmount AS totalPaymentAmount,
                hf2.id AS healthFacilityAddress_id,
                
                pbbForAccessory.accessoryCount,
                pbbForAccessory.price AS pbbForAccessory_price,
                pbbForAccessory.accessoryCount_void,
                pbbForAccessory.price_void AS pbbForAccessory_price_void,
                
                pbbForCpapPackage.cpapPackageCount,
                pbbForCpapPackage.price AS pbbForCpapPackage_price,
                pbbForCpapPackage.cpapPackageCount_void,
                pbbForCpapPackage.price_void AS pbbForCpapPackage_price_void,
                
                pbbForMedicalSupplies.medicalSuppliesCount,
                pbbForMedicalSupplies.price AS pbbForMedicalSupplies_price,
                pbbForMedicalSupplies.medicalSuppliesCount_void,
                pbbForMedicalSupplies.price_void AS pbbForMedicalSupplies_price_void,
                
                pbbForLongTerm.longTermCount,
                pbbForLongTerm.price AS pbbForLongTerm_price,
                pbbForLongTerm.longTermCount_void,
                pbbForLongTerm.price_void AS pbbForLongTerm_price_void,
                
                pbbForRentToOwn.rentToCount,
                pbbForRentToOwn.price AS pbbForRentToOwn_price,
                pbbForRentToOwn.rentToCount_void,
                pbbForRentToOwn.price_void AS pbbForRentToOwn_price_void,
                
                ppForBreakdown.breakdown AS ppForBreakdown_breakdown
            FROM `patient_billing` pb
            LEFT JOIN `patient` pat ON pb.`patient_id` = pat.`id`
            LEFT JOIN `patient_billing_breakdown` pbb ON pbb.`id` = (
              SELECT pbb1.`id`
              FROM `patient_billing_breakdown` pbb1
              WHERE pbb1.`patient_billing_id` = pb.`id`
              $sqlWhere2
              LIMIT 1
            )
            LEFT JOIN (
              SELECT 
                pbb_TMP.patient_billing_id,
                SUM(pbb_TMP.amount) AS amtDue
              FROM patient_billing_breakdown pbb_TMP
              GROUP BY pbb_TMP.patient_billing_id
            ) pbb2 ON pb.id = pbb2.patient_billing_id
            LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
            LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`
            LEFT JOIN `company` co ON hf2.`company_id` = co.`id`
            LEFT JOIN `patient_insurance` pi ON pb.`patient_insurance_id` = pi.`id`
            LEFT JOIN `insurance` i ON pi.`insurance_id` = i.`id`
            LEFT JOIN (
              SELECT
                pptmp.patient_billing_id,
                SUM(pptmp.amount) AS totalAmount
                FROM patient_payment pptmp
                INNER JOIN patient_billing pbtmp ON pptmp.patient_billing_id = pbtmp.id
                WHERE pptmp.payment_status = 'Posted'
                GROUP BY pptmp.patient_billing_id
            ) pp ON pb.id = pp.patient_billing_id
            LEFT JOIN (
              SELECT
                  pp_g.patient_billing_id,
                 GROUP_CONCAT(CONCAT_WS('~', pp_g.pay_type, pp_g.transactionCount, totalAmt) SEPARATOR ',') AS breakdown
                FROM (
                     SELECT
                       pp_pbp.patient_billing_id,
                       pp_pbp.pay_type,
                       COUNT(pp_pbp.id) AS transactionCount,
                       SUM(pp_pbp.amount) AS totalAmt
                     FROM patient_payment pp_pbp
                     WHERE pp_pbp.patient_billing_id IS NOT NULL
                     AND pp_pbp.payment_status = 'Posted'
                     GROUP BY pp_pbp.patient_billing_id, pp_pbp.pay_type
                ) pp_g
                GROUP BY pp_g.patient_billing_id
            ) ppForBreakdown ON pb.id = ppForBreakdown.patient_billing_id
            LEFT JOIN (
              SELECT 
                -- COUNT(pbb4.`id`) AS accessoryCount,
                -- SUM(pbb4.amount) AS price,
                SUM(CASE WHEN pb4.billing_status IN ('Void', 'Returned') THEN 0 ELSE 1 END) AS accessoryCount,
                SUM(CASE WHEN pb4.billing_status IN ('Void', 'Returned') THEN 0 ELSE pbb4.amount END) AS price,
                SUM(CASE WHEN pb4.billing_status IN ('Void', 'Returned') THEN pbb4.amount ELSE 0 END) AS price_void,
                SUM(CASE WHEN pb4.billing_status IN ('Void', 'Returned') THEN 1 ELSE 0 END) AS accessoryCount_void,
                pbb4.patient_billing_id
              FROM patient_billing_breakdown pbb4
              INNER JOIN patient_billing pb4 ON pbb4.patient_billing_id = pb4.id
              WHERE (pbb4.`item_type` = 'Accessory' OR pbb4.`is_accessory` = 1)
              GROUP BY pbb4.patient_billing_id
            ) pbbForAccessory ON pb.id = pbbForAccessory.patient_billing_id
            LEFT JOIN (
              SELECT
                -- COUNT(pbb5.`id`) AS cpapPackageCount,
                -- SUM(pbb5.amount) AS price,
                SUM(CASE WHEN pb5.billing_status IN ('Void', 'Returned') THEN 0 ELSE 1 END) AS cpapPackageCount,
                SUM(CASE WHEN pb5.billing_status IN ('Void', 'Returned') THEN 0 ELSE pbb5.amount END) AS price,
                SUM(CASE WHEN pb5.billing_status IN ('Void', 'Returned') THEN pbb5.amount ELSE 0 END) AS price_void,
                SUM(CASE WHEN pb5.billing_status IN ('Void', 'Returned') THEN 1 ELSE 0 END) AS cpapPackageCount_void,
                pbb5.patient_billing_id
              FROM patient_billing_breakdown pbb5
              INNER JOIN patient_billing pb5 ON pbb5.patient_billing_id = pb5.id
              WHERE pbb5.`item_type` = 'CPAP Package'
              GROUP BY pbb5.patient_billing_id
            ) pbbForCpapPackage ON pb.id = pbbForCpapPackage.patient_billing_id 
            LEFT JOIN (
              SELECT
                -- COUNT(pbb6.`id`) AS medicalSuppliesCount,
                -- SUM(pbb6.amount) AS price,
                SUM(CASE WHEN pb6.billing_status IN ('Void', 'Returned') THEN 0 ELSE 1 END) AS medicalSuppliesCount,
                SUM(CASE WHEN pb6.billing_status IN ('Void', 'Returned') THEN 0 ELSE pbb6.amount END) AS price,
                SUM(CASE WHEN pb6.billing_status IN ('Void', 'Returned') THEN pbb6.amount ELSE 0 END) AS price_void,
                SUM(CASE WHEN pb6.billing_status IN ('Void', 'Returned') THEN 1 ELSE 0 END) AS medicalSuppliesCount_void,
                pbb6.patient_billing_id
              FROM patient_billing_breakdown pbb6
              INNER JOIN patient_billing pb6 ON pbb6.patient_billing_id = pb6.id
              WHERE pbb6.`item_type` = 'Medical Supplies'
              GROUP BY pbb6.patient_billing_id
            ) pbbForMedicalSupplies ON pb.id = pbbForMedicalSupplies.patient_billing_id
            LEFT JOIN (
              SELECT
                -- COUNT(pbb2.`id`) AS longTermCount,
                -- SUM(pbb2.amount) AS price,
                SUM(CASE WHEN pb2.billing_status IN ('Void', 'Returned') THEN 0 ELSE 1 END) AS longTermCount,
                SUM(CASE WHEN pb2.billing_status IN ('Void', 'Returned') THEN 0 ELSE pbb2.amount END) AS price,
                SUM(CASE WHEN pb2.billing_status IN ('Void', 'Returned') THEN pbb2.amount ELSE 0 END) AS price_void,
                SUM(CASE WHEN pb2.billing_status IN ('Void', 'Returned') THEN 1 ELSE 0 END) AS longTermCount_void,
                pbb2.patient_billing_id
              FROM patient_billing_breakdown pbb2
              INNER JOIN patient_billing pb2 ON pbb2.patient_billing_id = pb2.id
              WHERE pbb2.`term` = 'Long Term Rental'
              GROUP BY pbb2.patient_billing_id
            ) pbbForLongTerm ON pb.id = pbbForLongTerm.patient_billing_id
            LEFT JOIN (
              SELECT
                -- COUNT(pbb3.`id`) AS rentToCount,
                -- SUM(pbb3.amount) AS price,
                SUM(CASE WHEN pb3.billing_status IN ('Void', 'Returned') THEN 0 ELSE 1 END) AS rentToCount,
                SUM(CASE WHEN pb3.billing_status IN ('Void', 'Returned') THEN 0 ELSE pbb3.amount END) AS price,
                SUM(CASE WHEN pb3.billing_status IN ('Void', 'Returned') THEN pbb3.amount ELSE 0 END) AS price_void,
                SUM(CASE WHEN pb3.billing_status IN ('Void', 'Returned') THEN 1 ELSE 0 END) AS rentToCount_void,
                pbb3.patient_billing_id
              FROM patient_billing_breakdown pbb3
              INNER JOIN patient_billing pb3 ON pbb3.patient_billing_id = pb3.id
              WHERE pbb3.`term` = 'Rent to Own'
              GROUP BY pbb3.patient_billing_id
            ) pbbForRentToOwn ON pb.id = pbbForRentToOwn.patient_billing_id
            $sqlWhere
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf2.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND pb.`amount_due` != 0
            AND pat.`is_test_patient` != 1
            ORDER BY pb.`billing_date` DESC
        ";

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

        $result = $query->fetchAll();

        return $result;
    }

    public function reportInvoiceSummary($params) {
        $sqlWhere = "WHERE pb.`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 ") . "pb.`billing_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['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
            $companies = explode(',' , $params['company']);
            if(count($companies) > 0){
                $hf_ors = '';
                foreach($companies as $key => $companyId){
                    $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "hf2.`company_id` = :company_id_$key";
                    $stmtParams["company_id_$key"] = $companyId;
                }
                $sqlWhere .= " 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 ') . "pb.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

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

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

        if(isset($params['status']) && !empty($params['status']) && 'NULL' !== strtoupper($params['status'])) {
            $statuses = explode(',', $params['status']);
            if(count($statuses) > 0) {
                $ors = '';
                foreach ($statuses as $key => $status) {
                    $ors .= (empty($ors) ? "" : " OR ") . "pb.`billing_status` = :status_$key";
                    $stmtParams["status_$key"] = $status;
                }
                $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "($ors)";
            }
            //$sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`billing_status` = :status";
            //$stmtParams['status'] = $params['status'];
        }

        if($params['company'] == 'null' && $params['branch'] == 'null') {
            if(!is_null($params['userData']) && !empty($params['userData'])) {
                if ($params['userData']['type'] != 'Super Admin') {
                    if (count($params['userData']['company']['companyBranches']) > 0) {
                        $hf_ors = '';
                        foreach ($params['userData']['company']['companyBranches'] as $key => $branchId) {
                            $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "pb.`health_facility_for_address_id` = $branchId";
                        }
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " ($hf_ors) ";
                    } else {
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " (1 != 1) ";
                    }
                }
            }
        }

        if(isset($params['type']) && !empty($params['type']) && 'NULL' !== strtoupper($params['type'])) {
            switch ($params['type']) {
                case 'All' :
                    $sqlWhere .= "";
                    break;
                case 'cpap_package':
                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb.`item_type` = 'CPAP Package'";
                    break;
                case 'accessory':
                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "(pbb.`item_type` = 'Accessory' OR pbb.`is_accessory` = 1)";
                    break;
                case 'medical_supplies':
                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb.`item_type` = 'Medical Supplies'";
            }

        }

        if(isset($params['term']) && !empty($params['term']) && 'NULL' !== strtoupper($params['term'])) {
            switch ($params['term']) {
                case 'All' :
                    $sqlWhere .= "";
                    break;
                case 'long_term':
                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb.`term` = 'Long Term Rental'";
                    break;
                case 'rent_to_own':
                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb.`term` = 'Rent To Own'";
            }

        }

        $sql = "
            SELECT 
                COUNT(pb.`health_facility_for_address_id`) AS totalCount,
                IF(pat.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf2.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf2.`description`), pb.`prev_sys_invoice_branch`)) AS healthFacility,
                SUM(pb.`amount_due`) AS totalAmtDue
            FROM `patient_billing` pb
            LEFT JOIN `patient` pat ON pb.`patient_id` = pat.`id`
            LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
            LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`
            LEFT JOIN `company` co ON hf2.`company_id` = co.`id`
            LEFT JOIN `patient_billing_breakdown` pbb ON pbb.`id` = (
              SELECT pbb1.`id`
              FROM `patient_billing_breakdown` pbb1
              WHERE pbb1.`patient_billing_id` = pb.`id`
              LIMIT 1
            )
            $sqlWhere
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf2.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND pb.`amount_due` != 0
            AND hf2.`id` NOT IN(
              SELECT hff.`id`
              FROM `health_facility` hff
              WHERE hff.`is_deleted` = 0
              AND hff.`description` IN('Finance Underwriting & Investment Capital', 'Canpay Medical Inc.')
            )
            AND pat.`is_test_patient` != 1
            GROUP BY pb.`health_facility_for_address_id`
        ";

        $sql2 = "SELECT 
                   hf2.`id` AS billingBranchId,
                   IF(p.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf2.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf2.`description`), pb.`prev_sys_invoice_branch`) ) AS billingBranch,
                   COUNT(pbb.`item_id`) AS longTermCount,
                   SUM(pbb.`price`) AS price
                FROM `patient_billing` pb 
                LEFT JOIN `patient_billing_financing_payment_detail` pbfpd ON pb.`id` = pbfpd.`patient_billing_id`
                LEFT JOIN `patient_billing_breakdown` pbb ON pbb.`patient_billing_id` = pb.`id`
                LEFT JOIN `item` i ON pbb.`item_id` = i.`id`
                LEFT JOIN `patient` p ON pb.`patient_id` = p.`id`
                LEFT JOIN `patient_chart` pc ON pc.`id` = (
                          SELECT pc1.`id`
                          FROM `patient_chart` pc1
                          WHERE pc1.`patient_id` = p.`id`
                          ORDER BY pc1.`chart_date` DESC
                          LIMIT 1
                )
                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.`created_at` DESC
                          LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
                LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`
                LEFT JOIN `company` co ON hf2.`company_id` = co.`id`
                $sqlWhere
                AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                AND hf2.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                AND pbb.`term` = 'Long Term Rental'
                AND p.`is_test_patient` != 1
                GROUP BY pb.`health_facility_for_address_id`";

        $sql3 = "SELECT 
                   hf2.`id` AS billingBranchId,
                   IF(p.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf2.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf2.`description`), pb.`prev_sys_invoice_branch`) ) AS billingBranch,
                   COUNT(pbb.`item_id`) AS rentToCount,
                   SUM(pbb.`price`) AS price
                FROM `patient_billing` pb 
                LEFT JOIN `patient_billing_financing_payment_detail` pbfpd ON pb.`id` = pbfpd.`patient_billing_id`
                LEFT JOIN `patient_billing_breakdown` pbb ON pbb.`patient_billing_id` = pb.`id`
                LEFT JOIN `item` i ON pbb.`item_id` = i.`id`
                LEFT JOIN `patient` p ON pb.`patient_id` = p.`id`
                LEFT JOIN `patient_chart` pc ON pc.`id` = (
                          SELECT pc1.`id`
                          FROM `patient_chart` pc1
                          WHERE pc1.`patient_id` = p.`id`
                          ORDER BY pc1.`chart_date` DESC
                          LIMIT 1
                )
                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.`created_at` DESC
                          LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
                LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`
                LEFT JOIN `health_facility` pat_hf ON phf.`health_facility_id` = pat_hf.`id`
                LEFT JOIN `company` co ON hf2.`company_id` = co.`id`
                $sqlWhere
                AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                AND hf2.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                AND pbb.`term` = 'Rent to Own'
                AND p.`is_test_patient` != 1
                GROUP BY pb.`health_facility_for_address_id`";

        $sql4 = "SELECT 
                   hf2.`id` AS billingBranchId,
                   IF(p.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf2.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf2.`description`), pb.`prev_sys_invoice_branch`) ) AS billingBranch,
                   COUNT(pbb.`item_id`) AS accessoryCount,
                   SUM(pbb.`price`) AS price
                FROM `patient_billing` pb 
                LEFT JOIN `patient_billing_financing_payment_detail` pbfpd ON pb.`id` = pbfpd.`patient_billing_id`
                LEFT JOIN `patient_billing_breakdown` pbb ON pbb.`patient_billing_id` = pb.`id`
                LEFT JOIN `item` i ON pbb.`item_id` = i.`id`
                LEFT JOIN `patient` p ON pb.`patient_id` = p.`id`
                LEFT JOIN `patient_chart` pc ON pc.`id` = (
                          SELECT pc1.`id`
                          FROM `patient_chart` pc1
                          WHERE pc1.`patient_id` = p.`id`
                          ORDER BY pc1.`chart_date` DESC
                          LIMIT 1
                )
                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.`created_at` DESC
                          LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
                LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`
                LEFT JOIN `company` co ON hf2.`company_id` = co.`id`
                $sqlWhere
                AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                AND hf2.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                AND (pbb.`item_type` = 'Accessory' OR pbb.`is_accessory` = 1)
                AND p.`is_test_patient` != 1
                GROUP BY pb.`health_facility_for_address_id`";

        $sql5 = "SELECT 
                   hf2.`id` AS billingBranchId,
                   IF(p.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf2.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf2.`description`), pb.`prev_sys_invoice_branch`) ) AS billingBranch,
                   COUNT(pbb.`item_id`) AS cpapPackageCount,
                   SUM(pbb.`price`) AS price
                FROM `patient_billing` pb 
                LEFT JOIN `patient_billing_financing_payment_detail` pbfpd ON pb.`id` = pbfpd.`patient_billing_id`
                LEFT JOIN `patient_billing_breakdown` pbb ON pbb.`patient_billing_id` = pb.`id`
                LEFT JOIN `item` i ON pbb.`item_id` = i.`id`
                LEFT JOIN `patient` p ON pb.`patient_id` = p.`id`
                LEFT JOIN `patient_chart` pc ON pc.`id` = (
                          SELECT pc1.`id`
                          FROM `patient_chart` pc1
                          WHERE pc1.`patient_id` = p.`id`
                          ORDER BY pc1.`chart_date` DESC
                          LIMIT 1
                )
                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.`created_at` DESC
                          LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
                LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`
                LEFT JOIN `company` co ON hf2.`company_id` = co.`id`
                $sqlWhere
                AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                AND hf2.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                AND pbb.`item_type` = 'CPAP Package'
                AND p.`is_test_patient` != 1
                GROUP BY pb.`health_facility_for_address_id`";

        $sql6 = "SELECT 
                  hf2.`id` AS billingBranchId,
                   IF(p.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf2.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf2.`description`), pb.`prev_sys_invoice_branch`) ) AS billingBranch,
                   COUNT(pbb.`item_id`) AS medicalSuppliesCount,
                   SUM(pbb.`price`) AS price
                FROM `patient_billing` pb 
                LEFT JOIN `patient_billing_financing_payment_detail` pbfpd ON pb.`id` = pbfpd.`patient_billing_id`
                LEFT JOIN `patient_billing_breakdown` pbb ON pbb.`patient_billing_id` = pb.`id`
                LEFT JOIN `item` i ON pbb.`item_id` = i.`id`
                LEFT JOIN `patient` p ON pb.`patient_id` = p.`id`
                LEFT JOIN `patient_chart` pc ON pc.`id` = (
                          SELECT pc1.`id`
                          FROM `patient_chart` pc1
                          WHERE pc1.`patient_id` = p.`id`
                          ORDER BY pc1.`chart_date` DESC
                          LIMIT 1
                )
                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.`created_at` DESC
                          LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
                LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`
                LEFT JOIN `company` co ON hf2.`company_id` = co.`id`
                $sqlWhere
                AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                AND hf2.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                AND pbb.`item_type` = 'Medical Supplies'
                AND p.`is_test_patient` != 1
                GROUP BY pb.`health_facility_for_address_id`";

        $sql7 = "
            SELECT 
                COUNT(pb.`health_facility_for_address_id`) AS totalCount,
                IF(pat.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf2.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf2.`description`), pb.`prev_sys_invoice_branch`)) AS healthFacility,
                SUM(pb.`amount_due`) AS totalAmtDue
            FROM `patient_billing` pb
            LEFT JOIN `patient` pat ON pb.`patient_id` = pat.`id`
            LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
            LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`
            LEFT JOIN `company` co ON hf2.`company_id` = co.`id`
            LEFT JOIN `patient_billing_breakdown` pbb ON pbb.`id` = (
              SELECT pbb1.`id`
              FROM `patient_billing_breakdown` pbb1
              WHERE pbb1.`patient_billing_id` = pb.`id`
              LIMIT 1
            )
            $sqlWhere
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf2.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf2.`id` IN(
              SELECT hff.`id`
              FROM `health_facility` hff
              WHERE hff.`is_deleted` = 0
              AND hff.`description` IN('Finance Underwriting & Investment Capital', 'Canpay Medical Inc.')
            )
            AND pb.`amount_due` != 0
            AND pat.`is_test_patient` != 1
            GROUP BY pb.`health_facility_for_address_id`
        ";

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

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

        $query2 = $conn->prepare($sql2);
        $query2->execute($stmtParams);

        $query3 = $conn->prepare($sql3);
        $query3->execute($stmtParams);

        $query4 = $conn->prepare($sql4);
        $query4->execute($stmtParams);

        $query5 = $conn->prepare($sql5);
        $query5->execute($stmtParams);

        $query6 = $conn->prepare($sql6);
        $query6->execute($stmtParams);

        $query7 = $conn->prepare($sql7);
        $query7->execute($stmtParams);


        $result['invoiceSummary'] = $query->fetchAll();
        $result['longTerm'] = $query2->fetchAll();
        $result['rentToOwn'] = $query3->fetchAll();
        $result['accessory'] = $query4->fetchAll();
        $result['cpapPackage'] = $query5->fetchAll();
        $result['medicalSupplies'] = $query6->fetchAll();
        $result['financing'] = $query7->fetchAll();

        return $result;
    }

    public function arReport($params) {
        $now = new \DateTime();
        $sqlWhere = "WHERE pb.`is_deleted` = 0";
        $stmtParams = array();

        if (isset($params['interval']) && $params['interval'] != 'All') {
            if(isset($params['interval']) && !empty($params['interval']) && 'NULL' !== strtoupper($params['interval'])){
                switch ($params['interval']) {
                    case 'Current' :
                        $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -29 DAY) AND :now";
                        break;

                    case '30Days' :
                            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -59 DAY) AND DATE_ADD(:now, INTERVAL -30 DAY)";
                        break;

                    case '60Days' :
                        $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -89 DAY) AND DATE_ADD(:now, INTERVAL -60 DAY)";
                        break;

                    case '90Days' :
                        $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -119 DAY) AND DATE_ADD(:now, INTERVAL -90 DAY)";
                        break;

                    default :
                        $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -100 YEAR) AND DATE_ADD(:now, INTERVAL -120 DAY)";
                        break;
                }

                $params['interval'];
            }
        }

        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 ') . "pb.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

        if(isset($params['branch_address']) && !empty($params['branch_address']) && 'NULL' !== strtoupper($params['branch_address'])){
            $branchAddress = explode(',' , $params['branch_address']);
            if(count($branchAddress) > 0){
                $hf_address_ors = '';
                foreach($branchAddress as $key => $branchAddressId){
                    $hf_address_ors .= (empty($hf_address_ors) ? '' : ' OR ') . "pb.`health_facility_for_address_id` = :healthFacilityAddress_id_$key";
                    $stmtParams["healthFacilityAddress_id_$key"] = $branchAddressId;
                }
                $sqlWhere .= " AND ($hf_address_ors) ";
            }
        }

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

        if(isset($params['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
            $companies = explode(',' , $params['company']);
            if(count($companies) > 0){
                $co_ors = '';
                foreach($companies as $key => $companyId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "hf3_c.`id` = :company_id_$key";
                    $stmtParams["company_id_$key"] = $companyId;
                }
                $sqlWhere .= " AND ($co_ors) ";
            }
        }

        if($params['company'] == 'null' && $params['branch'] == 'null' && $params['branch_address'] == 'null') {
            if (!is_null($params['userData']) && !empty($params['userData'])) {
                if ($params['userData']['type'] != 'Super Admin') {
                    if (count($params['userData']['company']['companyBranches']) > 0) {
                        $hf_ors = '';
                        foreach ($params['userData']['company']['companyBranches'] as $key => $branchId) {
                            $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "pb.`health_facility_id` = $branchId";
                        }
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " ($hf_ors)";
                    } else {
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " (1 != 1) ";
                    }
                }
            }
        }

        $sql = "
            SELECT
              pb.`id`,
              pat.`id` patientId,
              IF(pb.`invoice_no` IS NOT NULL, pb.`invoice_no`, pb.`prev_sys_invoice_no`) invoiceNo,
              DATE_FORMAT(pb.`billing_date`, '%m/%d/%Y') billingDate,
              pat.`full_name` patient,
              DATE_FORMAT(pp.`pay_date` , '%m/%d/%Y') lastPaymentDate,
              pp.`bill_paid_amount` lastPaymentAmount,
              pb.`amount_due` amtDue,
              IF(pb.`billing_status` != 'Returned', pb.`billing_status`, 0.00) AS amount_balance,
              pb.`amount_balance` amtBalance,
              IF(tmp_ins.`patientInsurance` IS NOT NULL, tmp_ins.`patientInsurance`, 'Self Pay') patientInsurance,
              IF(pb.`payer_type` = 'Self Pay', 'Self Pay', IF(pb.`payer_type` = 'Financing', IF(fc.`description` IS NULL, 'Financing', fc.`description`), tmp_ins.`patientInsurance`)) as payer,
              IF(pat.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf.`description`), pb.`prev_sys_invoice_branch`)) healthFacility,
              pb.`billing_status` billingStatus,
              DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') lastCalled,
              pb.`note`,
              pb.`endorsed_to` as billing_endorsedTo,
              hf.`description` as healthFacility_desc,
              hf2.`description` as healthFacilityForAddress_desc,
              hf2_c.`description` as healthFacilityForAddress_companyDesc,
              pp.`amount` as lastPaymentAmt,
              hf3.`description` as notNullHealthFacility_desc,
              hf3_c.`description` as notNullHealthFacility_companyDesc,
              pp.`amount` as lastPaymentAmt,
              IF(pb.`demand_letter_date` IS NULL, '', DATE_FORMAT(pb.`demand_letter_date`, '%m/%d/%Y')) as patientBilling_demandLetterDate,
              IF(pb.`insurance_claim_date` IS NULL, '', DATE_FORMAT(pb.`insurance_claim_date`, '%m/%d/%Y')) as patientBilling_insuranceClaimDate,
              IF(pb.`is_payment_plan` = 1, 
                  IF(DATE_FORMAT(pbfpd.`start_date`, CONCAT('%Y-%m-',LPAD(pbfpd.`payment_day`, 2, '0'))) > :now , 
                      DATE_FORMAT(pbfpd.`start_date`, CONCAT('%m/',LPAD(pbfpd.`payment_day`, 2, '0'), '/%Y')),
                      DATE_FORMAT(pbfpd.`start_date` + INTERVAL 1 MONTH, CONCAT('%m/',LPAD(pbfpd.`payment_day`, 2, '0'), '/%Y'))
                  ), 
                  '') as patientBilling_nextDue,
              IF(DATE_ADD(pb.`billing_date`, INTERVAL 45 DAY) <= DATE_FORMAT(:now, '%Y-%m-%d'), '1', '0') isFortyFiveDays,
              CASE 
                WHEN DATEDIFF(pb.`billing_date`, :now) <= 29 THEN 'Current'
                WHEN DATEDIFF(pb.`billing_date`, :now) <= 59 THEN '30 Days'
                WHEN DATEDIFF(pb.`billing_date`, :now) <= 89 THEN '60 Days'
                WHEN DATEDIFF(pb.`billing_date`, :now) <= 119 THEN '90 Days'
                ELSE '120 Days or More'
              END AS accountPhase
            FROM `patient_billing` pb
            LEFT JOIN `patient` pat ON pb.`patient_id` = pat.`id`
            LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
            LEFT JOIN `company` hf_c ON hf.`company_id` = hf_c.`id`
            LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`
            LEFT JOIN `company` hf2_c ON hf2.`company_id` = hf2_c.`id`
            LEFT JOIN `health_facility` hf3 ON hf3.id = IF(pb.`health_facility_for_address_id` IS NULL, pb.`health_facility_id`, pb.`health_facility_for_address_id`)
            LEFT JOIN company hf3_c on hf3.`company_id` = hf3_c.`id`
            LEFT JOIN `patient_billing_financing_payment_detail` pbfpd ON pbfpd.`patient_billing_id` = pb.`id`
            LEFT JOIN(
              SELECT
                pins.`id` patientInsuranceId,
                i.`description` patientInsurance
              FROM `patient_insurance` pins
              LEFT JOIN `insurance` i ON pins.`insurance_id` = i.`id`
            ) tmp_ins ON tmp_ins.`patientInsuranceId` = pb.`patient_insurance_id`
            LEFT JOIN `patient_payment` pp ON pp.`id` = (
              SELECT
                pp2.`id`
              FROM `patient_payment` pp2
              WHERE pp2.`patient_id` = pat.`id`
              AND pp2.`patient_billing_id` = pb.`id`
              ORDER BY pp2.`pay_date` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pc ON pc.`id` = (
              SELECT pc2.`id`
              FROM `patient_chart` pc2
              LEFT JOIN `chart_type` ct ON pc2.`chart_type_id` = ct.`id`
              WHERE ct.`description` IN('Phone Call - AR (CA)', 'Phone Call - AR (CBT)')
              AND pc2.`patient_id` = pat.`id`
              ORDER BY pc2.`chart_date` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_payment` pp_f ON pb.`patient_payment_id_financing` = pp_f.`id`
            LEFT JOIN `financing_company` fc ON pp_f.`financing_company_id` = fc.`id`
            $sqlWhere
            AND pb.`amount_due` != 0
            AND pat.`is_test_patient` != 1
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf2.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND pb.`billing_status` = 'Pending Payment'
            ORDER BY pat.`full_name` ASC
        ";
        $stmtParams['now'] = $now->format('Y-m-d 23:59:59');
        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    public function arReportSummary($params) {
        $now = new \DateTime();
        $sqlWhere = "WHERE pb.`is_deleted` = 0";
        $stmtParams = array();

        if (isset($params['interval']) && $params['interval'] != 'All') {
            if(isset($params['interval']) && !empty($params['interval']) && 'NULL' !== strtoupper($params['interval'])){
                switch ($params['interval']) {
                    case 'Current' :
                        $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -29 DAY) AND :now";
                        break;

                    case '30Days' :
                        $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -59 DAY) AND DATE_ADD(:now, INTERVAL -30 DAY)";
                        break;

                    case '60Days' :
                        $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -89 DAY) AND DATE_ADD(:now, INTERVAL -60 DAY)";
                        break;

                    case '90Days' :
                        $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -119 DAY) AND DATE_ADD(:now, INTERVAL -90 DAY)";
                        break;

                    default :
                        $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -100 YEAR) AND DATE_ADD(:now, INTERVAL -120 DAY)";
                        break;
                }
                $stmtParams['now'] = $now->format('Y-m-d 23:59:59');
                $params['interval'];
            }
        }

        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 ') . "pb.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

        if(isset($params['branch_address']) && !empty($params['branch_address']) && 'NULL' !== strtoupper($params['branch_address'])){
            $branchAddress = explode(',' , $params['branch_address']);
            if(count($branchAddress) > 0){
                $hf_address_ors = '';
                foreach($branchAddress as $key => $branchAddressId){
                    $hf_address_ors .= (empty($hf_address_ors) ? '' : ' OR ') . "pb.`health_facility_for_address_id` = :healthFacilityAddress_id_$key";
                    $stmtParams["healthFacilityAddress_id_$key"] = $branchAddressId;
                }
                $sqlWhere .= " AND ($hf_address_ors) ";
            }
        }

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

        if(isset($params['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
            $companies = explode(',' , $params['company']);
            if(count($companies) > 0){
                $co_ors = '';
                foreach($companies as $key => $companyId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "hf3_c.`id` = :company_id_$key";
                    $stmtParams["company_id_$key"] = $companyId;
                }
                $sqlWhere .= " AND ($co_ors) ";
            }
        }

        if($params['company'] == 'null' && $params['branch'] == 'null' && $params['branch_address'] == 'null') {
            if (!is_null($params['userData']) && !empty($params['userData'])) {
                if ($params['userData']['type'] != 'Super Admin') {
                    if (count($params['userData']['company']['companyBranches']) > 0) {
                        $hf_ors = '';
                        foreach ($params['userData']['company']['companyBranches'] as $key => $branchId) {
                            $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "pb.`health_facility_id` = $branchId";
                        }
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " ($hf_ors)";
                    } else {
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " (1 != 1) ";
                    }
                }
            }
        }

        $sql = "
            SELECT
              hf.`description` AS healthFacility,
              COUNT(IF(pat.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf.`description`), pb.`prev_sys_invoice_branch`))) AS totalCount,
              SUM(IF(pb.`billing_status` != 'Returned', pb.`amount_balance`, 0.00)) AS totalBalance
            FROM `patient_billing` pb
            LEFT JOIN `patient` pat ON pb.`patient_id` = pat.`id`
            LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
            LEFT JOIN `company` hf_c ON hf.`company_id` = hf_c.`id`
            LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`
            LEFT JOIN `company` hf2_c ON hf2.`company_id` = hf2_c.`id`
            LEFT JOIN `health_facility` hf3 ON hf3.id = IF(pb.`health_facility_for_address_id` IS NULL, pb.`health_facility_id`, pb.`health_facility_for_address_id`)
            LEFT JOIN `company` hf3_c on hf3.`company_id` = hf3_c.`id`
            LEFT JOIN `patient_billing_financing_payment_detail` pbfpd ON pbfpd.`patient_billing_id` = pb.`id`
            LEFT JOIN(
              SELECT
                pins.`id` patientInsuranceId,
                i.`description` patientInsurance
              FROM `patient_insurance` pins
              LEFT JOIN `insurance` i ON pins.`insurance_id` = i.`id`
            ) tmp_ins ON tmp_ins.`patientInsuranceId` = pb.`patient_insurance_id`
            LEFT JOIN `patient_payment` pp ON pp.`id` = (
              SELECT
                pp2.`id`
              FROM `patient_payment` pp2
              WHERE pp2.`patient_id` = pat.`id`
              AND pp2.`patient_billing_id` = pb.`id`
              ORDER BY pp2.`pay_date` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pc ON pc.`id` = (
              SELECT pc2.`id`
              FROM `patient_chart` pc2
              LEFT JOIN `chart_type` ct ON pc2.`chart_type_id` = ct.`id`
              WHERE ct.`description` IN('Phone Call - AR (CA)', 'Phone Call - AR (CBT)')
              AND pc2.`patient_id` = pat.`id`
              ORDER BY pc2.`chart_date` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_payment` pp_f ON pb.`patient_payment_id_financing` = pp_f.`id`
            LEFT JOIN `financing_company` fc ON pp_f.`financing_company_id` = fc.`id`
            $sqlWhere
            AND pb.`amount_due` != 0
            AND pat.`is_test_patient` != 1
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf2.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND pb.`billing_status` = 'Pending Payment'
            GROUP BY pb.`health_facility_id`
        ";

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

        $result = $query->fetchAll();

        return $result;
    }

    public function reportARSummary($params) {
        $sqlWhere = "WHERE pb.`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 ") . "pb.`billing_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['payerType']) && !empty($params['payerType']) && 'NULL' !== strtoupper($params['payerType'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`payer_type` = :payerType";
            $stmtParams['payerType'] = $params['payerType'];
        }

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

        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 ') . "pb.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

        if(isset($params['branch_address']) && !empty($params['branch_address']) && 'NULL' !== strtoupper($params['branch_address'])){
            $branchAddress = explode(',' , $params['branch_address']);
            if(count($branchAddress) > 0){
                $hf_address_ors = '';
                foreach($branchAddress as $key => $branchAddressId){
                    $hf_address_ors .= (empty($hf_address_ors) ? '' : ' OR ') . "pb.`health_facility_for_address_id` = :healthFacilityAddress_id_$key";
                    $stmtParams["healthFacilityAddress_id_$key"] = $branchAddressId;
                }
                $sqlWhere .= " AND ($hf_address_ors) ";
            }
        }

        if(isset($params['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
            $companies = explode(',' , $params['company']);
            if(count($companies) > 0){
                $co_ors = '';
                foreach($companies as $key => $companyId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "hf_c.`id` = :company_id_$key";
                    $stmtParams["company_id_$key"] = $companyId;
                }
                $sqlWhere .= " AND ($co_ors) ";
            }
        }

        if($params['company'] == 'null' && $params['branch'] == 'null' && $params['branch_address'] == 'null') {
            if (!is_null($params['userData']) && !empty($params['userData'])) {
                if ($params['userData']['type'] != 'Super Admin') {
                    if (count($params['userData']['company']['companyBranches']) > 0) {
                        $hf_ors = '';
                        foreach ($params['userData']['company']['companyBranches'] as $key => $branchId) {
                            $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "pb.`health_facility_id` = $branchId";
                        }
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " ($hf_ors) ";
                    } else {
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " (1 != 1) ";
                    }
                }
            }
        }

        $sql = "
            SELECT
              pat.`id` AS patientId,
              pb.`payer_type` AS payerType,
              pat.`full_name` AS full_name,
              IF(pat.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf.`description`), pb.`prev_sys_invoice_branch`)) as healthFacility,
              IF(pat.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf2.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf2.`description`), pb.`prev_sys_invoice_branch`)) as healthFacilityAddress,
              IF(tmp_ins.`patientInsurance` IS NOT NULL, tmp_ins.`patientInsurance`, '-') AS patientInsurance,
              SUM(pb.`amount_due`) AS billTo,
              SUM(pb_cur.`amount_balance`) AS currentBal,
              SUM(pb_th.`amount_balance`) AS thirtyDays,
              SUM(pb_si.`amount_balance`) AS sixtyDays,
              SUM(pb_ni.`amount_balance`) AS ninetyDays,
              SUM(pb_ot.`amount_balance`) AS oneTwentyDays,
              pb.`amount_due` AS credit,
              SUM(pb.`amount_balance`) AS amountBalance
            FROM `patient_billing` pb
            LEFT JOIN `patient` pat ON pb.`patient_id` = pat.`id`
            LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
            LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`
            LEFT JOIN `company` hf_c ON hf.`company_id` = hf_c.`id`
            LEFT JOIN(
              SELECT
                pins.`id` patientInsuranceId,
                i.`description` patientInsurance
              FROM `patient_insurance` pins
              LEFT JOIN `insurance` i ON pins.`insurance_id` = i.`id`
            ) tmp_ins ON tmp_ins.`patientInsuranceId` = pb.`patient_insurance_id`
            LEFT JOIN `patient_payment` pp ON pp.`id` = (
                  SELECT
                    pp2.`id`
                  FROM `patient_payment` pp2
                  WHERE pp2.`patient_id` = pat.`id`
                  ORDER BY pp2.`created_at` DESC
                  LIMIT 1       
            )
            LEFT JOIN `patient_chart` pc ON pc.`id` = (
                  SELECT pc2.`id`
                  FROM `patient_chart` pc2
                         LEFT JOIN `chart_type` ct ON pc2.`chart_type_id` = ct.`id`
                  WHERE ct.`description` IN('Phone Call - AR (CA)', 'Phone Call - AR (CBT)')
                    AND pc2.`patient_id` = pat.`id`
                  ORDER BY pc2.`chart_date` DESC
                  LIMIT 1
            )
			LEFT JOIN `patient_billing` pb_cur ON pb_cur.`id` = (
					SELECT pb0.`id`
					FROM `patient_billing` pb0
					WHERE pb0.`id` = pb.`id`
					AND pb0.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -29 DAY) AND :now
					LIMIT 1
			)
			LEFT JOIN `patient_billing` pb_th ON pb_th.`id` = (
					SELECT pb1.`id`
					FROM `patient_billing` pb1
					WHERE pb1.`id` = pb.`id`
					AND pb1.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -59 DAY) AND DATE_ADD(:now, INTERVAL -30 DAY)
					LIMIT 1
			)
			LEFT JOIN `patient_billing` pb_si ON pb_si.`id` = (
					SELECT pb2.`id`
					FROM `patient_billing` pb2
					WHERE pb2.`id` = pb.`id`
					AND pb2.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -89 DAY) AND DATE_ADD(:now, INTERVAL -60 DAY)
					LIMIT 1
			)
			LEFT JOIN `patient_billing` pb_ni ON pb_ni.`id` = (
					SELECT pb3.`id`
					FROM `patient_billing` pb3
					WHERE pb3.`id` = pb.`id`
					AND pb3.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -119 DAY) AND DATE_ADD(:now, INTERVAL -90 DAY)
					LIMIT 1
			)
			LEFT JOIN `patient_billing` pb_ot ON pb_ot.id = (
					SELECT pb4.id
					FROM `patient_billing` pb4
					WHERE pb4.id = pb.id
					AND pb4.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -10 YEAR) AND DATE_ADD(:now, INTERVAL -120 DAY)
					LIMIT 1
			)	
            $sqlWhere
            AND pb.`amount_due` != 0
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf2.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND pb.`billing_status` = 'Pending Payment'
            AND pat.`is_test_patient` != 1           
            GROUP BY pb.`patient_id`
            ORDER BY pat.`full_name` ASC
        ";

        $dt = new \DateTime();
        $stmtParams['now'] = $dt->format('Y-m-d 23:59:59');
        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    public function reportARHealthFacilitySummary($params) {
        $sqlWhere = "WHERE pb.`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 ") . "pb.`billing_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['payerType']) && !empty($params['payerType']) && 'NULL' !== strtoupper($params['payerType'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`payer_type` = :payerType";
            $stmtParams['payerType'] = $params['payerType'];
        }

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

        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 ') . "pb.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

        if(isset($params['branch_address']) && !empty($params['branch_address']) && 'NULL' !== strtoupper($params['branch_address'])){
            $branchAddress = explode(',' , $params['branch_address']);
            if(count($branchAddress) > 0){
                $hf_address_ors = '';
                foreach($branchAddress as $key => $branchAddressId){
                    $hf_address_ors .= (empty($hf_address_ors) ? '' : ' OR ') . "pb.`health_facility_for_address_id` = :healthFacilityAddress_id_$key";
                    $stmtParams["healthFacilityAddress_id_$key"] = $branchAddressId;
                }
                $sqlWhere .= " AND ($hf_address_ors) ";
            }
        }

        if(isset($params['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
            $companies = explode(',' , $params['company']);
            if(count($companies) > 0){
                $co_ors = '';
                foreach($companies as $key => $companyId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "hf_c.`id` = :company_id_$key";
                    $stmtParams["company_id_$key"] = $companyId;
                }
                $sqlWhere .= " AND ($co_ors) ";
            }
        }

        if($params['company'] == 'null' && $params['branch'] == 'null' && $params['branch_address'] == 'null') {
            if (!is_null($params['userData']) && !empty($params['userData'])) {
                if ($params['userData']['type'] != 'Super Admin') {
                    if (count($params['userData']['company']['companyBranches']) > 0) {
                        $hf_ors = '';
                        foreach ($params['userData']['company']['companyBranches'] as $key => $branchId) {
                            $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "pb.`health_facility_id` = $branchId";
                        }
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " ($hf_ors) ";
                    } else {
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " (1 != 1) ";
                    }
                }
            }
        }

        $sql = "
            SELECT
              pat.`id` AS patientId,
              pb.`payer_type` AS payerType,
              pat.`full_name` AS full_name,
              IF(pat.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf.`description`), pb.`prev_sys_invoice_branch`)) as healthFacility,
              IF(tmp_ins.`patientInsurance` IS NOT NULL, tmp_ins.`patientInsurance`, '-') AS patientInsurance,
              SUM(pb.`amount_due`) AS billTo,
              SUM(pb_cur.`amount_balance`) AS currentBal,
              SUM(pb_th.`amount_balance`) AS thirtyDays,
              SUM(pb_si.`amount_balance`) AS sixtyDays,
              SUM(pb_ni.`amount_balance`) AS ninetyDays,
              SUM(pb_ot.`amount_balance`) AS oneTwentyDays,
              pb.`amount_due` AS credit,
              SUM(pb.`amount_balance`) AS amountBalance
            FROM `patient_billing` pb
            LEFT JOIN `patient` pat ON pb.`patient_id` = pat.`id`
            LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
            LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`
            LEFT JOIN `company` hf_c ON hf.`company_id` = hf_c.`id`
            LEFT JOIN(
              SELECT
                pins.`id` patientInsuranceId,
                i.`description` patientInsurance
              FROM `patient_insurance` pins
              LEFT JOIN `insurance` i ON pins.`insurance_id` = i.`id`
            ) tmp_ins ON tmp_ins.`patientInsuranceId` = pb.`patient_insurance_id`
            LEFT JOIN `patient_payment` pp ON pp.`id` = (
                  SELECT
                    pp2.`id`
                  FROM `patient_payment` pp2
                  WHERE pp2.`patient_id` = pat.`id`
                  ORDER BY pp2.`created_at` DESC
                  LIMIT 1       
            )
            LEFT JOIN `patient_chart` pc ON pc.`id` = (
                  SELECT pc2.`id`
                  FROM `patient_chart` pc2
                         LEFT JOIN `chart_type` ct ON pc2.`chart_type_id` = ct.`id`
                  WHERE ct.`description` IN('Phone Call - AR (CA)', 'Phone Call - AR (CBT)')
                    AND pc2.`patient_id` = pat.`id`
                  ORDER BY pc2.`chart_date` DESC
                  LIMIT 1
            )
			LEFT JOIN `patient_billing` pb_cur ON pb_cur.`id` = (
					SELECT pb0.`id`
					FROM `patient_billing` pb0
					WHERE pb0.`id` = pb.`id`
					AND pb0.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -29 DAY) AND :now
					LIMIT 1
			)
			LEFT JOIN `patient_billing` pb_th ON pb_th.`id` = (
					SELECT pb1.`id`
					FROM `patient_billing` pb1
					WHERE pb1.`id` = pb.`id`
					AND pb1.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -59 DAY) AND DATE_ADD(:now, INTERVAL -30 DAY)
					LIMIT 1
			)
			LEFT JOIN `patient_billing` pb_si ON pb_si.`id` = (
					SELECT pb2.`id`
					FROM `patient_billing` pb2
					WHERE pb2.`id` = pb.`id`
					AND pb2.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -89 DAY) AND DATE_ADD(:now, INTERVAL -60 DAY)
					LIMIT 1
			)
			LEFT JOIN `patient_billing` pb_ni ON pb_ni.`id` = (
					SELECT pb3.`id`
					FROM `patient_billing` pb3
					WHERE pb3.`id` = pb.`id`
					AND pb3.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -119 DAY) AND DATE_ADD(:now, INTERVAL -90 DAY)
					LIMIT 1
			)
			LEFT JOIN `patient_billing` pb_ot ON pb_ot.id = (
					SELECT pb4.id
					FROM `patient_billing` pb4
					WHERE pb4.id = pb.id
					AND pb4.`billing_date` BETWEEN DATE_ADD(:now, INTERVAL -10 YEAR) AND DATE_ADD(:now, INTERVAL -120 DAY)
					LIMIT 1
			)	
            $sqlWhere
            AND pb.`amount_due` != 0
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf2.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND pb.`billing_status` = 'Pending Payment'
            AND pat.`is_test_patient` != 1           
            GROUP BY IF(pat.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf.`description`), pb.`prev_sys_invoice_branch`))
            ORDER BY pat.`full_name` ASC
        ";

        $dt = new \DateTime();
        $stmtParams['now'] = $dt->format('Y-m-d 23:59:59');
        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    public function report_itemised($params) {
        $now = new \DateTime();
        $sqlWhere = "WHERE (pbb.is_deleted = 0 OR pbb.`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 ") . "pb.`billing_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['patient']) && !empty($params['patient']) && 'NULL' !== strtoupper($params['patient'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`patient_id` = :patient";
            $stmtParams['patient'] = $params['patient'];
        }

        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 ') . "pb.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

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

        if(isset($params['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
            $companies = explode(',' , $params['company']);
            if(count($companies) > 0){
                $co_ors = '';
                foreach($companies as $key => $companyId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "hf2.`company_id` = :company_id_$key";
                    $stmtParams["company_id_$key"] = $companyId;
                }
                $sqlWhere .= " AND ($co_ors) ";
            }
        }

        if($params['company'] == 'null' && $params['branch'] == 'null') {
            if (!is_null($params['userData']) && !empty($params['userData'])) {
                if ($params['userData']['type'] != 'Super Admin') {
                    if (count($params['userData']['company']['companyBranches']) > 0) {
                        $hf_ors = '';
                        foreach ($params['userData']['company']['companyBranches'] as $key => $branchId) {
                            $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "phf.`health_facility_id` = $branchId";
                        }
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " ($hf_ors) ";
                    } else {
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " (1 != 1) ";
                    }
                }
            }
        }

        if(isset($params['item_type']) && !empty($params['item_type']) && 'NULL' !== strtoupper($params['item_type'])) {
            switch ($params['item_type']) {
                case 'All' :
                    $sqlWhere .= "";
                    break;
                case 'cpap_package':
                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb.`item_type` = 'CPAP Package'";
                    break;
                case 'accessory':
                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "(pbb.`item_type` = 'Accessory' OR pbb.`is_accessory` = 1)";
                    break;
                case 'medical_supplies':
                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb.`item_type` = 'Medical Supplies'";
            }

        }

        if(isset($params['term']) && !empty($params['term']) && 'NULL' !== strtoupper($params['term'])) {
            switch ($params['term']) {
                case 'All' :
                    $sqlWhere .= "";
                    break;
                case 'long_term':
                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb.`term` = 'Long Term Rental'";
                    break;
                case 'rent_to_own':
                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb.`term` = 'Rent To Own'";
            }

        }

        $sql = "
            SELECT 
				 p.`id` AS patientId,
				 p.`full_name` AS patientName,
				 co.`description` AS companyDesc,
				 hf1.`description` AS branch,
				 hf2.`description` AS branchAddress,
				 IF(pb.`invoice_no` IS NOT NULL, pb.`invoice_no`, pb.`prev_sys_invoice_no`) invoiceNo,
                 ic.`description` AS itemCategory,
                 i.`description` AS itemDesc,
                 pbb.`term`,
                 pbb.`item_type` AS itemType,
                 pbb.`quantity` AS qty,
                 pbb.`price`
            FROM `patient_billing_breakdown` pbb
            LEFT JOIN `patient_billing` pb ON pb.`id` = pbb.`patient_billing_id`
            LEFT JOIN `patient` p ON pb.`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.`created_at` DESC
                       LIMIT 1
            )
            LEFT JOIN `health_facility` hf0 ON phf.`health_facility_id` = hf0.`id`
            LEFT JOIN `health_facility` hf1 ON pb.`health_facility_id` = hf1.`id`
            LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`
            LEFT JOIN `company` co ON hf2.`company_id` = hf2.`id`
            LEFT JOIN `item` i ON pbb.`item_id` = i.`id`
            LEFT JOIN `item_subcategory` its ON i.`item_subcategory_id` = its.`id`
            LEFT JOIN `item_category` ic ON its.`item_category_id` = ic.`id`
            $sqlWhere
            AND hf0.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf1.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf2.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND p.`is_test_patient` != 1
        ";
        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    public function report_rent_to_own_long_term_rental($params) {
        $now = new \DateTime();
        $sqlWhere = "WHERE (pr.is_deleted = 0 OR pr.`is_deleted` IS NOT NULL)";
        $stmtParams = array();

        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 ') . "pr.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

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

        if(isset($params['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
            $companies = explode(',' , $params['company']);
            if(count($companies) > 0){
                $co_ors = '';
                foreach($companies as $key => $companyId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "hf.`company_id` = :company_id_$key";
                    $stmtParams["company_id_$key"] = $companyId;
                }
                $sqlWhere .= " AND ($co_ors) ";
            }
        }

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

        if(isset($params['type']) && $params['type'] != 'All' && !empty($params['type']) && 'NULL' !== strtoupper($params['type'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb.`item_type` = :type";
            $stmtParams['type'] = $params['type'];
        }

        $sql = "
            SELECT 
                p.`id` AS patientId,
                p.`full_name` AS patientName,
                co.`description` AS companyDesc,
	            hf.`description` AS branchDesc,
                i.`description` AS itemDesc,
                pbb.`item_type` AS paymentPlanType,
                pbb.`quantity` AS qty,
                pbb.`price`,
                CONCAT(pr.payment_day, CASE
                    WHEN pr.payment_day BETWEEN 11 AND 13 THEN \"th\"
                    WHEN pr.payment_day = 1 THEN 'st'
                    WHEN pr.payment_day = 2 THEN 'nd'
                    WHEN pr.payment_day = 3 THEN 'rd'
                    ELSE 'th'
                END) AS paymentDay,
                pr.`amortization_amount` AS amortizationAmt,
                DATE_FORMAT(pr.`start_date`, '%m/%d/%Y') AS startDate,
                DATE_FORMAT(pr.`end_date`, '%m/%d/%Y') AS endDate
            FROM `patient_rental` pr 
            LEFT JOIN `patient_billing_breakdown` pbb ON pbb.`patient_billing_id` = pr.`id`
            LEFT JOIN `item` i ON pbb.`item_id` = i.`id`
            LEFT JOIN `patient` p ON pr.`patient_id` = p.`id`
            LEFT JOIN `patient_chart` pc ON pc.`id` = (
              SELECT pc1.`id`
              FROM `patient_chart` pc1
              WHERE pc1.`patient_id` = p.`id`
              ORDER BY pc1.`chart_date` DESC
              LIMIT 1
            )
            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.`created_at` DESC
                       LIMIT 1
            )
            LEFT JOIN `health_facility` hf1 ON pr.`health_facility_id` = hf1.`id`
            LEFT JOIN `health_facility` hf2 ON pr.`health_facility_for_address_id` = hf2.`id`
            LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
            LEFT JOIN `company` co ON hf2.`company_id` = co.`id`
            $sqlWhere
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf1.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf2.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND p.`is_test_patient` != 1
        ";
        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    public function arPatientDashboard($params) {
        $sqlWhere = "WHERE pb.`is_deleted` = 0";
        $stmtParams = array();

        if(isset($params)) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pb.`patient_id` = :patient";
            $stmtParams['patient'] = $params;
        }

        $sql = "
            SELECT 
                pat.`id` patientId,
                IF(pb.`invoice_no` IS NOT NULL, pb.`invoice_no`, pb.`prev_sys_invoice_no`) invoiceNo,
                pb.`amount_due` amtDue
            FROM `patient_billing` pb
            LEFT JOIN `patient` pat ON pb.`patient_id` = pat.`id`
            LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
            $sqlWhere
            AND pb.`id` NOT IN(SELECT pp.`patient_billing_id` FROM `patient_payment` pp)
            AND pb.`amount_due` != 0
            ORDER BY pb.`billing_date` ASC
        ";

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

        $result = $query->fetchAll();

        return $result;
    }

    public function nextInvoiceNo($healthFacility) {

        $companyId = $healthFacility->getCompany()->getId();

        /* NOTE: FIRST, GET THE LAST_OR_NO OF COMPANY */
        $query = $this->getEntityManager()->getConnection()->prepare("
          SELECT c2.`last_or_number` * 1 as last_or_number 
          FROM `company` c2
          WHERE c2.`id` = :companyId
          LIMIT 1
        ");
        $query->execute(array(
            'companyId' => $companyId
        ));
        $result = $query->fetch();
        $healthFacility_lastOrNumber = intval(is_null($result['last_or_number']) ? 0 : $result['last_or_number']);
        $lastInvoiceNo = $healthFacility_lastOrNumber;


        /* NOTE: THEN, GET THE INVOICE_NO OF LAST LAST BILLING  */
        $query = $this->getEntityManager()->getConnection()->prepare("
          SELECT pbb.`invoice_no` * 1 as invoice_no
          FROM `patient_billing` pbb
          LEFT JOIN `health_facility` hf on pbb.`health_facility_for_address_id` = hf.`id`
          WHERE hf.`company_id` = :companyId
          ORDER BY invoice_no DESC
          LIMIT 1
        ");
        $query->execute(array(
            'companyId' => $companyId
        ));
        $result = $query->fetch();
        $patientBilling_lastInvoiceNo = intval(is_null($result['invoice_no']) ? 0 : $result['invoice_no']);


        /* NOTE: IF INVOICE_NO OF LAST LAST BILLING IS GREATER THAN LAST_OR_NO IN HEALTH FACILITY, THEN CONSIDER THE LAST LAST INVOICE_NO OF BILLING AS OUR LAST INVOICE */
        if($patientBilling_lastInvoiceNo > $healthFacility_lastOrNumber){
            $lastInvoiceNo = $patientBilling_lastInvoiceNo;
        }

        // $newInvoiceExists = true;
        $newInvoice = $lastInvoiceNo + 1;

        /* NOTE: ADD 1 TO lastInvoice(newInvoice) AND CHECK IF NEW INVOICE ALREADY EXIST IN BILLING WITH THE SAME HEALTH FACILITY, LOOP UNTIL NEW INVOICE DOES NOT EXISTs */
        /*while($newInvoiceExists){
            // NOTE: ADD 1 FOR NEXT INVOICE
            $newInvoice += 1;

            $query = $this->getEntityManager()->getConnection()->prepare("
              SELECT pbb.`id`
              FROM `patient_billing` pbb
              LEFT JOIN `health_facility` hf on pbb.`health_facility_for_address_id` = hf.`id`
              WHERE hf.`company_id` = :companyId AND pbb.`invoice_no` = :newInvoice
              ORDER BY pbb.`id` DESC
              LIMIT 1
            ");
            $query->execute(array(
                'companyId' => $healthFacility->getCompany()->getId(),
                'newInvoice' => $newInvoice
            ));
            $result = $query->fetch();
            if(is_null($result['id'])){ // IF YUNG NEW INVOICE AY WALA PA SA PATIENT BILLING, THEN SET FALSE PARA MAG BREAK ANG LOOP
                $newInvoiceExists = false;
            } else {
                $newInvoiceExists = true;
            }
        }*/

        return $newInvoice;
    }

    public function nextInvoiceNoFinancing($financingCompany_id) {

        $query = $this->getEntityManager()->getConnection()->prepare("
          SELECT fc.`last_or_number` * 1 as last_or_number 
          FROM `financing_company` fc 
          WHERE fc.`id` = :financingCompany_id
          LIMIT 1
        ");
        $query->execute(array(
            'financingCompany_id' => $financingCompany_id
        ));
        $result = $query->fetch();
        $financingCompany_lastOrNumber = intval(is_null($result['last_or_number']) ? 0 : $result['last_or_number']);
        $lastInvoiceNo = $financingCompany_lastOrNumber;

        $query = $this->getEntityManager()->getConnection()->prepare("
          SELECT pbb.`invoice_no` * 1 as invoice_no
          FROM `patient_billing` pbb
          LEFT JOIN `patient_payment` pp on pbb.`patient_payment_id_financing` = pp.`id`
          WHERE pbb.`payer_type` = :payerType AND
              pp.`financing_company_id` = :financingCompany_id
          ORDER BY invoice_no DESC
          LIMIT 1
        ");
        $query->execute(array(
            'payerType' => PatientBillingEntity::PAYER_TYPE_FINANCING,
            'financingCompany_id' => $financingCompany_id,
        ));
        $result = $query->fetch();
        $patientBilling_lastInvoiceNo = intval(is_null($result['invoice_no']) ? 0 : $result['invoice_no']);

        if($patientBilling_lastInvoiceNo > $financingCompany_lastOrNumber){
            $lastInvoiceNo = $patientBilling_lastInvoiceNo;
        }

        return $lastInvoiceNo + 1;
    }

    public function reportPatientBillingFile($params) {
        $sqlWhere = "WHERE pb.`is_deleted` = 0";
        $sqlWhere2 = "";
        $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 ") . "pb.`billing_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['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
            $companies = explode(',' , $params['company']);
            if(count($companies) > 0){
                $co_ors = '';
                foreach($companies as $key => $companyId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "hf2.`company_id` = :company_id_$key";
                    $stmtParams["company_id_$key"] = $companyId;
                }
                $sqlWhere .= " AND ($co_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 ') . "pb.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

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

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

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

        if($params['company'] == 'null' && $params['branch'] == 'null') {
            if(!is_null($params['userData']) && !empty($params['userData'])) {
                if ($params['userData']['type'] != 'Super Admin') {
                    if (count($params['userData']['company']['companyBranches']) > 0) {
                        $hf_ors = '';
                        foreach ($params['userData']['company']['companyBranches'] as $key => $branchId) {
                            $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "pb.`health_facility_for_address_id` = $branchId";
                        }
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " ($hf_ors) ";
                    } else {
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " (1 != 1) ";
                    }
                }
            }
        }

        if($params['type'] != 'All') {
            if ($params['type'] == 1) {
                $sqlWhere .= " AND pb.`patient_file_id` IS NOT NULL";
            } else {
                $sqlWhere .= " AND pb.`patient_file_id` IS NULL";
            }
        }

        $sysParam = $this->getEntityManager()->getRepository('RxConnexionBundle:SystemParameterEntity')->findOneBy(array(
            'description' => 'Insurance Package'
        ));

        if (isset($sysParam) && $sysParam != 'null') {
            $stmtParams['spInsurancePackage'] = $sysParam->getValue();
        } else {
            $stmtParams['spInsurancePackage'] = date('Y-m-d');
        }


        $sql = "
            SELECT 
                pat.`id` patientId,
                IF(pb.`invoice_no` IS NOT NULL, pb.`invoice_no`, pb.`prev_sys_invoice_no`) invoiceNo,
                DATE_FORMAT(pb.`billing_date`, '%m/%d/%Y') billingDate,
                IF(pat.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf.`description`), pb.`prev_sys_invoice_branch`) ) healthFacility,
                IF(pat.`full_name` = 'Mismatched Invoice', pb.`prev_sys_invoice_branch`, IF(hf2.`description` IS NOT NULL, IF(pb.`prev_sys_invoice_branch` = 'AMS', 'AMS', hf2.`description`), pb.`prev_sys_invoice_branch`) ) healthFacilityAddress,
                pat.`full_name` AS patient,
                co.`description` AS companyDesc,
                pb.`billing_status` AS billingStatus,
                pf.`description` AS patientFileDesc,
                pb.`note`
            FROM `patient_billing` pb
            LEFT JOIN `patient` pat ON pb.`patient_id` = pat.`id`
            LEFT JOIN `patient_file` pf ON pb.`patient_file_id` = pf.`id` 
            LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
            LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`
            LEFT JOIN `company` co ON hf2.`company_id` = co.`id`
            LEFT JOIN `patient_insurance` pi ON pb.`patient_insurance_id` = pi.`id`
            LEFT JOIN `insurance` i ON pi.`insurance_id` = i.`id`
            $sqlWhere
            AND pb.`amount_due` != 0
            AND pb.`payer_type` = 'Insurance'
            AND pat.`is_test_patient` != 1
            AND pb.`created_at` BETWEEN '2020-1-1' AND :spInsurancePackage
            ORDER BY pb.`billing_date` DESC
        ";

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

        $result = $query->fetchAll();

        return $result;
    }

    public function reportPatientWithDifferentBranchesInvoice($params) {
        $sqlWhere = "WHERE p.`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 ") . "p.`referral_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['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
//            $companies = explode(',' , $params['company']);
//            if(count($companies) > 0){
//                $co_ors = '';
//                foreach($companies as $key => $companyId){
//                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "hf.`company_id` = :company_id_$key";
//                    $stmtParams["company_id_$key"] = $companyId;
//                }
//                $sqlWhere .= " AND ($co_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 ') . "ft.`health_facility_id` = :healthFacility_id_$key";
//                    $stmtParams["healthFacility_id_$key"] = $branchId;
//                }
//                $sqlWhere .= " AND ($hf_ors) ";
//            }
//        }

//        if($params['company'] == 'null' && $params['branch'] == 'null') {
//            if (!is_null($params['userData']) && !empty($params['userData'])) {
//                if ($params['userData']['type'] != 'Super Admin') {
//                    if (count($params['userData']['company']['companyBranches']) > 0) {
//                        $hf_ors = '';
//                        foreach ($params['userData']['company']['companyBranches'] as $key => $branchId) {
//                            $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "ft.`health_facility_id` = $branchId";
//                        }
//                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " ($hf_ors) ";
//                    } else {
//                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " (1 != 1) ";
//                    }
//                }
//            }
//        }

        $sql = "SELECT 
                    p.`id`,
                    DATE_FORMAT(p.`referral_date`, '%m/%d/%Y') AS referralDate,
                    DATE_FORMAT(p.`receive_date`, '%m/%d/%Y') AS receiveDate,
                    p.`full_name` AS patientName,
                    prev_hf.`description` AS prevHealthFacility,
                    latest_hf.`description` AS latestHealthFacility
                FROM `patient` p
                LEFT JOIN `patient_billing` prev_pb ON prev_pb.`id` = (
                    SELECT 
                        prev_pb1.`id`
                    FROM `patient_billing` prev_pb1
                    LEFT JOIN `patient_billing` prev ON prev.`id` = (
                        SELECT pre.`id`
                        FROM `patient_billing` pre
                        WHERE pre.`is_deleted` = 0
                        AND pre.`patient_id` = p.`id`
                        ORDER BY pre.`billing_date` ASC
                        LIMIT 1
                    )
                    WHERE prev_pb1.`is_deleted` = 0
                    AND prev_pb1.`patient_id` = p.`id`
                    AND prev.`health_facility_for_address_id` != prev_pb1.`health_facility_for_address_id`
                    ORDER BY prev_pb1.`billing_date` ASC
                    LIMIT 1
                )
                LEFT JOIN `health_facility` prev_hf ON prev_hf.`id` = prev_pb.`health_facility_for_address_id`
                LEFT JOIN `patient_billing` latest_pb ON latest_pb.`id` = (
                    SELECT 
                        latest_pb1.`id`
                    FROM `patient_billing` latest_pb1
                    WHERE latest_pb1.`is_deleted` = 0
                    AND latest_pb1.`patient_id` = p.`id`
                    ORDER BY latest_pb1.`billing_date` DESC
                    LIMIT 1
                )
                LEFT JOIN `health_facility` latest_hf ON latest_hf.`id` = latest_pb.`health_facility_for_address_id`
                $sqlWhere
                AND p.`is_duplicate` = 0
                AND (p.`is_not_count_as_referral` = 0 OR p.`is_not_count_as_referral` IS NULL)
                AND p.`full_name` IS NOT NULL
                AND p.`full_name` IS NOT NULL
                AND p.`referral_date` IS NOT NULL
                AND p.`is_test_patient` = 0
                AND p.`referral_date` != '0000-00-00 00:00:00'    
                ";

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

        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    public function autocomplete_suggestions($q)
    {
        $sql = "
            SELECT
                pb.`id`,
                CONCAT_WS(' - ', pb.`invoice_no`, DATE_FORMAT(pb.`billing_date`, '%Y/%m/%d'), pb.`amount_due`) AS data,
                CONCAT_WS(' - ', pb.`invoice_no`, DATE_FORMAT(pb.`billing_date`, '%Y/%m/%d'), pb.`amount_due`) AS value,
                pb.`billing_status` AS billingStatus,
                pb.`amount_due` AS amountDue,
                pp.`id` AS patientPaymentId,
                pbr.`id` AS patientRefundId
            FROM `patient_billing` pb
            LEFT JOIN `patient_payment` pp ON pp.`id` = (
              SELECT pp1.`id` 
              FROM `patient_payment` pp1
              WHERE pp1.`patient_billing_id` = pb.`id`
              LIMIT 1
            )
            LEFT JOIN `patient_billing_refund` pbr ON pbr.`id` = (
              SELECT pbr1.`id` 
              FROM `patient_billing_refund` pbr1
              WHERE pbr1.`patient_billing_id` = pb.`id`
              LIMIT 1
            )
            WHERE pb.`invoice_no` LIKE :description
            AND pb.`is_deleted` = 0
            AND (pb.`invoice_no` IS NOT NULL OR pb.`invoice_no` != '')
            ORDER BY pb.`invoice_no`
            LIMIT 0,20
        ";

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

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

        $result = $query->fetchAll();

        return $result;
    }
}
