<?php

namespace RxConnexionBundle\Repository;

use RxConnexionBundle\Entity\ItemEntity;
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 ' . $patientBillingBreakdown_form['item_description'];
                        } /*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'])) {
                    	$item = $this->getEntityManager()->getRepository(ItemEntity::class)->find($patientBillingBreakdown_form['item']);
                    	if($item && !$item->getIsZeroPrice()) {
		                    $errors[] = 'Please enter price in ' . $patientBillingBreakdown_form['item_description'];
	                    }
                    } /*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`'),
            array('pbb_itemised.`item_count`', 'pbb_itemised.`item_count`', 'itemisedCount'),
            array('pbb_itemised_sn.with_serial_no_count', 'pbb_itemised_sn.with_serial_no_count', 'withSerialNoCount'),
            array('pbi_itemised.itemised_count', 'pbi_itemised.itemised_count', 'pbi_itemisedCount'),
            array('pbi_itemised.itemised_with_serial_count', 'pbi_itemised.itemised_with_serial_count', 'pbi_itemisedWithSerialCount'),

            array('pbi_non_inventory.non_inventory_count', 'pbi_non_inventory.non_inventory_count', 'pbi_nonInventoryCount'),
            array('pbi_non_inventory.pbi_ctr', 'pbi_non_inventory.pbi_ctr', 'pbi_nonInventoryCount_pbiCtr'),
        );
        $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`
            LEFT JOIN (
			    SELECT
			        COUNT(pbb_TMP.id) AS item_count,
			        pbb_TMP.patient_billing_id
			    FROM patient_billing_breakdown pbb_TMP
			    INNER JOIN item i_TMP on pbb_TMP.item_id = i_TMP.id
			    WHERE i_TMP.is_itemised = 1
			    GROUP BY pbb_TMP.patient_billing_id
			) pbb_itemised ON pb.id = pbb_itemised.patient_billing_id
			LEFT JOIN (
			    SELECT
			        i_TMP.description,
			        i_TMP.is_itemised,
			        pbi_TMP.serial_no,
			        pbb_TMP.patient_billing_id,
			        COUNT(pbb_TMP.id) AS with_serial_no_count
			    FROM patient_billing_breakdown pbb_TMP
			    INNER JOIN item i_TMP on pbb_TMP.item_id = i_TMP.id
			    INNER JOIN (
			        SELECT
			            pbi_TMP.patient_billing_id,
			            isn_TMP.serial_no,
			            isn_TMP.status
			        FROM patient_billing_inventory pbi_TMP
			        LEFT JOIN inventory_serial_no isn_TMP on pbi_TMP.id = isn_TMP.patient_billing_inventory_id AND isn_TMP.status = 'Sold'
			        WHERE pbi_TMP.patient_billing_inventory_id IS NULL
			        AND (isn_TMP.serial_no IS NOT NULL OR pbi_TMP.is_trial = 1 AND pbi_TMP.is_trial_returned = 1)
			        GROUP BY patient_billing_id
			    ) pbi_TMP ON pbb_TMP.patient_billing_id = pbi_TMP.patient_billing_id
			    GROUP BY pbb_TMP.patient_billing_id
			) pbb_itemised_sn ON pb.id = pbb_itemised_sn.patient_billing_id
			LEFT JOIN (
				SELECT
				  pbi_TMP.patient_billing_id,
				  COUNT(pbi_TMP.id) AS itemised_count,
				  -- COUNT(isn_.serial_no) AS itemised_with_serial_count
				  COUNT(IF(pbi_TMP.is_trial = 1 AND pbi_TMP.is_trial_returned = 1, 1, isn_.serial_no)) AS itemised_with_serial_count
				FROM patient_billing_inventory pbi_TMP
				INNER JOIN item i_ on pbi_TMP.item_id = i_.id
				LEFT JOIN inventory_serial_no isn_ ON pbi_TMP.id = isn_.patient_billing_inventory_id
				WHERE i_.is_itemised = 1
				-- AND (pbi_TMP.is_trial = 0 OR pbi_TMP.is_trial = 1 AND pbi_TMP.is_trial_returned = 1)
				GROUP BY pbi_TMP.patient_billing_id
			) pbi_itemised ON pb.id = pbi_itemised.patient_billing_id
			LEFT JOIN (
			  SELECT
			    pbi_.patient_billing_id,
			    COUNT(pbi_.id) AS non_inventory_count,
			    pbi_pbi.pbi_ctr
			  FROM patient_billing_inventory pbi_
			  INNER JOIN item i_ on pbi_.item_id = i_.id
			  LEFT JOIN (
			    SELECT
			      pbi__.patient_billing_inventory_id,
			      COUNT(pbi__.id) AS pbi_ctr
			    FROM patient_billing_inventory pbi__
			    WHERE pbi__.patient_billing_inventory_id IS NOT NULL
			    GROUP BY pbi__.patient_billing_inventory_id
			  ) pbi_pbi ON pbi_.id = pbi_pbi.patient_billing_inventory_id
			  INNER JOIN (
			    SELECT
			        pbb__.patient_billing_id,
			        pbb__.item_id
			    FROM patient_billing_breakdown pbb__
			    WHERE pbb__.item_type != 'Part of CPAP Package'
			    GROUP BY pbb__.patient_billing_id, pbb__.item_id
			  ) pbb_pocp_ ON pbi_.patient_billing_id = pbb_pocp_.patient_billing_id AND pbi_.item_id = pbb_pocp_.item_id
			  WHERE i_.is_non_inventory = 1
			  GROUP BY pbi_.patient_billing_id
			) pbi_non_inventory ON pb.id = pbi_non_inventory.patient_billing_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,
                    COUNT(pbb.id) AS item_count
                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`, 
                  `inventory_serial_no_id`, 
                  `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, 
                  :inventory_serial_no_$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['inventory_serial_no_' . $key] = isset($breakdown['inventory_serial_no']) && !empty($breakdown['inventory_serial_no']) ? $breakdown['inventory_serial_no'] : 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'";
                    break;
	            default:
		            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb.`item_type` = :itemType ";
		            $stmtParams['itemType'] = $params['type'];
            }

        }

        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,
                pat.`address` AS address,
          		CONCAT_WS(',', pat.`phone_no`,  pat.`phone_no_two`, pat.`phone_no_three`) AS phoneNo,
				CONCAT_WS(',', pat.`email`,  pat.`email_two`, pat.`email_three`) AS email,
                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,
                pbbForAccessory.cost AS pbbForAccessory_cost,
                
                pbbForCpapPackage.cpapPackageCount,
                pbbForCpapPackage.price AS pbbForCpapPackage_price,
                pbbForCpapPackage.cpapPackageCount_void,
                pbbForCpapPackage.price_void AS pbbForCpapPackage_price_void,
                pbbForCpapPackage.cost AS pbbForCpapPackage_cost,
                
                pbbForMedicalSupplies.medicalSuppliesCount,
                pbbForMedicalSupplies.price AS pbbForMedicalSupplies_price,
                pbbForMedicalSupplies.medicalSuppliesCount_void,
                pbbForMedicalSupplies.price_void AS pbbForMedicalSupplies_price_void,
                pbbForMedicalSupplies.cost AS pbbForMedicalSupplies_cost,
                
                pbbForLongTerm.longTermCount,
                pbbForLongTerm.price AS pbbForLongTerm_price,
                pbbForLongTerm.longTermCount_void,
                pbbForLongTerm.price_void AS pbbForLongTerm_price_void,
                pbbForLongTerm.cost AS pbbForLongTerm_cost,
                
                pbbForRentToOwn.rentToCount,
                pbbForRentToOwn.price AS pbbForRentToOwn_price,
                pbbForRentToOwn.rentToCount_void,
                pbbForRentToOwn.price_void AS pbbForRentToOwn_price_void,
                pbbForRentToOwn.cost AS pbbForRentToOwn_cost,
                
                pbbForPartOfCpapPackage.partOfCpapPackageCount,
                pbbForPartOfCpapPackage.price AS pbbForPartOfCpapPackage_price,
                pbbForPartOfCpapPackage.partOfCpapPackageCount_void,
                pbbForPartOfCpapPackage.price_void AS pbbForPartOfCpapPackage_price_void,
                pbbForPartOfCpapPackage.cost AS pbbForPartOfCpapPackage_cost,
                
                pbbForCpapTrial.cpapTrialCount,
                pbbForCpapTrial.price AS pbbForCpapTrial_price,
                pbbForCpapTrial.cpapTrialCount_void,
                pbbForCpapTrial.price_void AS pbbForCpapTrial_price_void,
                pbbForCpapTrial.cost AS pbbForCpapTrial_cost,
                
                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 pbb4.quantity 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 pbb4.quantity ELSE 0 END) AS accessoryCount_void,
                SUM(CASE WHEN pb4.billing_status IN ('Void', 'Returned') THEN 0 ELSE IF(i4.is_itemised = 0, IF(pbi4_2.inv_sub_item_count > 0, pbi4_2.total_cost,iac4.ave_cost * pbb4.quantity), isn4.total_cost) END) AS cost,
                pbb4.patient_billing_id
              FROM patient_billing_breakdown pbb4
              INNER JOIN patient_billing pb4 ON pbb4.patient_billing_id = pb4.id
              INNER JOIN item i4 ON pbb4.item_id = i4.id
              LEFT JOIN patient_billing_inventory pbi4 ON pb4.id = pbi4.patient_billing_id AND i4.id = pbi4.item_id
              LEFT JOIN item_ave_cost iac4 ON pbi4.item_id = iac4.item_id AND pb4.health_facility_id = iac4.health_facility_id
              LEFT JOIN (
                SELECT
                    pbi4_.patient_billing_inventory_id,
                    SUM(IF(i4_.is_itemised = 0, iac4_.ave_cost ,isn4_.total_cost)) AS total_cost,
                    COUNT(pbi4_.id) AS inv_sub_item_count
                FROM patient_billing_inventory pbi4_
                INNER JOIN patient_billing pb4_ ON pbi4_.patient_billing_id = pb4_.id
                INNER JOIN item i4_ ON pbi4_.item_id = i4_.id
                LEFT JOIN item_ave_cost iac4_ ON pbi4_.item_id = iac4_.item_id AND pb4_.health_facility_id = iac4_.health_facility_id
                LEFT JOIN (
				    SELECT
				        SUM(isn4__.cost) AS total_cost,
				        inv4__.item_id,
				        inv4__.health_facility_id,
				        pbi4__.patient_billing_id
				    FROM inventory_serial_no isn4__
				    INNER JOIN inventory inv4__ on isn4__.inventory_id = inv4__.id
				    INNER JOIN patient_billing_inventory pbi4__ ON isn4__.patient_billing_inventory_id = pbi4__.id
				    GROUP BY pbi4__.patient_billing_id, inv4__.item_id, inv4__.health_facility_id
				) isn4_ ON isn4_.patient_billing_id = pb4_.id AND pbi4_.item_id = isn4_.item_id AND pb4_.health_facility_id = isn4_.health_facility_id
                GROUP BY pbi4_.patient_billing_inventory_id
              ) pbi4_2 ON pbi4.id = pbi4_2.patient_billing_inventory_id
              LEFT JOIN (
			    SELECT
			        SUM(isn4_.cost) AS total_cost,
			        inv4_.item_id,
			        inv4_.health_facility_id,
			        pbi4_.patient_billing_id
			    FROM inventory_serial_no isn4_
			    INNER JOIN inventory inv4_ on isn4_.inventory_id = inv4_.id
			    INNER JOIN patient_billing_inventory pbi4_ ON isn4_.patient_billing_inventory_id = pbi4_.id
			    GROUP BY pbi4_.patient_billing_id, inv4_.item_id, inv4_.health_facility_id
			) isn4 ON isn4.patient_billing_id = pb4.id AND pbi4.item_id = isn4.item_id AND pb4.health_facility_id = isn4.health_facility_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 pbb5.quantity 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 pbb5.quantity ELSE 0 END) AS cpapPackageCount_void,
                SUM(CASE WHEN pb5.billing_status IN ('Void', 'Returned') THEN 0 ELSE IF(i5.is_itemised = 0, IF(pbi5_2.inv_sub_item_count > 0, pbi5_2.total_cost ,iac5.ave_cost * pbb5.quantity), isn5.total_cost) END) AS cost,
			    pbb5.patient_billing_id
              FROM patient_billing_breakdown pbb5
              INNER JOIN patient_billing pb5 ON pbb5.patient_billing_id = pb5.id
              INNER JOIN item i5 ON pbb5.item_id = i5.id
              LEFT JOIN patient_billing_inventory pbi5 ON pb5.id = pbi5.patient_billing_id AND i5.id = pbi5.item_id
              LEFT JOIN item_ave_cost iac5 ON pbi5.item_id = iac5.item_id AND pb5.health_facility_id = iac5.health_facility_id
              LEFT JOIN (
                SELECT
                    pbi5_.patient_billing_inventory_id,
                    SUM(IF(i5_.is_itemised = 0, iac5_.ave_cost ,isn5_.total_cost)) AS total_cost,
                    COUNT(pbi5_.id) AS inv_sub_item_count
                FROM patient_billing_inventory pbi5_
                INNER JOIN patient_billing pb5_ ON pbi5_.patient_billing_id = pb5_.id
                INNER JOIN item i5_ ON pbi5_.item_id = i5_.id
                LEFT JOIN item_ave_cost iac5_ ON pbi5_.item_id = iac5_.item_id AND pb5_.health_facility_id = iac5_.health_facility_id
                LEFT JOIN (
				    SELECT
				        SUM(isn5__.cost) AS total_cost,
				        inv5__.item_id,
				        inv5__.health_facility_id,
				        pbi5__.patient_billing_id
				    FROM inventory_serial_no isn5__
				    INNER JOIN inventory inv5__ on isn5__.inventory_id = inv5__.id
				    INNER JOIN patient_billing_inventory pbi5__ ON isn5__.patient_billing_inventory_id = pbi5__.id
				    GROUP BY pbi5__.patient_billing_id, inv5__.item_id, inv5__.health_facility_id
				) isn5_ ON isn5_.patient_billing_id = pb5_.id AND pbi5_.item_id = isn5_.item_id AND pb5_.health_facility_id = isn5_.health_facility_id
                GROUP BY pbi5_.patient_billing_inventory_id
              ) pbi5_2 ON pbi5.id = pbi5_2.patient_billing_inventory_id
              LEFT JOIN (
			    SELECT
			        SUM(isn5_.cost) AS total_cost,
			        inv5_.item_id,
			        inv5_.health_facility_id,
			        pbi5_.patient_billing_id
			    FROM inventory_serial_no isn5_
			    INNER JOIN inventory inv5_ on isn5_.inventory_id = inv5_.id
			    INNER JOIN patient_billing_inventory pbi5_ ON isn5_.patient_billing_inventory_id = pbi5_.id
			    GROUP BY pbi5_.patient_billing_id, inv5_.item_id, inv5_.health_facility_id
			) isn5 ON isn5.patient_billing_id = pb5.id AND pbi5.item_id = isn5.item_id AND pb5.health_facility_id = isn5.health_facility_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 pbb6.quantity 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 pbb6.quantity ELSE 0 END) AS medicalSuppliesCount_void,
                SUM(CASE WHEN pb6.billing_status IN ('Void', 'Returned') THEN 0 ELSE IF(i6.is_itemised = 0, IF(pbi6_2.inv_sub_item_count > 0, pbi6_2.total_cost,iac6.ave_cost * pbb6.quantity), isn6.total_cost) END) AS cost,
                pbb6.patient_billing_id
              FROM patient_billing_breakdown pbb6
              INNER JOIN patient_billing pb6 ON pbb6.patient_billing_id = pb6.id
              INNER JOIN item i6 ON pbb6.item_id = i6.id
              LEFT JOIN patient_billing_inventory pbi6 ON pb6.id = pbi6.patient_billing_id AND i6.id = pbi6.item_id
              LEFT JOIN item_ave_cost iac6 ON pbi6.item_id = iac6.item_id AND pb6.health_facility_id = iac6.health_facility_id
              LEFT JOIN (
                SELECT
                    pbi6_.patient_billing_inventory_id,
                    SUM(IF(i6_.is_itemised = 0, iac6_.ave_cost ,isn6_.total_cost)) AS total_cost,
                    COUNT(pbi6_.id) AS inv_sub_item_count
                FROM patient_billing_inventory pbi6_
                INNER JOIN patient_billing pb6_ ON pbi6_.patient_billing_id = pb6_.id
                INNER JOIN item i6_ ON pbi6_.item_id = i6_.id
                LEFT JOIN item_ave_cost iac6_ ON pbi6_.item_id = iac6_.item_id AND pb6_.health_facility_id = iac6_.health_facility_id
                LEFT JOIN (
				    SELECT
				        SUM(isn6__.cost) AS total_cost,
				        inv6__.item_id,
				        inv6__.health_facility_id,
				        pbi6__.patient_billing_id
				    FROM inventory_serial_no isn6__
				    INNER JOIN inventory inv6__ on isn6__.inventory_id = inv6__.id
				    INNER JOIN patient_billing_inventory pbi6__ ON isn6__.patient_billing_inventory_id = pbi6__.id
				    GROUP BY pbi6__.patient_billing_id, inv6__.item_id, inv6__.health_facility_id
				) isn6_ ON isn6_.patient_billing_id = pb6_.id AND pbi6_.item_id = isn6_.item_id AND pb6_.health_facility_id = isn6_.health_facility_id
                GROUP BY pbi6_.patient_billing_inventory_id
              ) pbi6_2 ON pbi6.id = pbi6_2.patient_billing_inventory_id
              LEFT JOIN (
			    SELECT
			        SUM(isn6_.cost) AS total_cost,
			        inv6_.item_id,
			        inv6_.health_facility_id,
			        pbi6_.patient_billing_id
			    FROM inventory_serial_no isn6_
			    INNER JOIN inventory inv6_ on isn6_.inventory_id = inv6_.id
			    INNER JOIN patient_billing_inventory pbi6_ ON isn6_.patient_billing_inventory_id = pbi6_.id
			    GROUP BY pbi6_.patient_billing_id, inv6_.item_id, inv6_.health_facility_id
			) isn6 ON isn6.patient_billing_id = pb6.id AND pbi6.item_id = isn6.item_id AND pb6.health_facility_id = isn6.health_facility_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 pbb2.quantity 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 pbb2.quantity ELSE 0 END) AS longTermCount_void,
                SUM(CASE WHEN pb2.billing_status IN ('Void', 'Returned') THEN 0 ELSE IF(i2.is_itemised = 0, IF(pbi2_2.inv_sub_item_count > 0, pbi2_2.total_cost,iac2.ave_cost * pbb2.quantity), isn2.total_cost) END) AS cost,
                pbb2.patient_billing_id
              FROM patient_billing_breakdown pbb2
              INNER JOIN patient_billing pb2 ON pbb2.patient_billing_id = pb2.id
              INNER JOIN item i2 ON pbb2.item_id = i2.id
              LEFT JOIN patient_billing_inventory pbi2 ON pb2.id = pbi2.patient_billing_id AND i2.id = pbi2.item_id
              LEFT JOIN item_ave_cost iac2 ON pbi2.item_id = iac2.item_id AND pb2.health_facility_id = iac2.health_facility_id
              LEFT JOIN (
                SELECT
                    pbi2_.patient_billing_inventory_id,
                    SUM(IF(i2_.is_itemised = 0, iac2_.ave_cost ,isn2_.total_cost)) AS total_cost,
                    COUNT(pbi2_.id) AS inv_sub_item_count
                FROM patient_billing_inventory pbi2_
                INNER JOIN patient_billing pb2_ ON pbi2_.patient_billing_id = pb2_.id
                INNER JOIN item i2_ ON pbi2_.item_id = i2_.id
                LEFT JOIN item_ave_cost iac2_ ON pbi2_.item_id = iac2_.item_id AND pb2_.health_facility_id = iac2_.health_facility_id
                LEFT JOIN (
				    SELECT
				        SUM(isn2__.cost) AS total_cost,
				        inv2__.item_id,
				        inv2__.health_facility_id,
				        pbi2__.patient_billing_id
				    FROM inventory_serial_no isn2__
				    INNER JOIN inventory inv2__ on isn2__.inventory_id = inv2__.id
				    INNER JOIN patient_billing_inventory pbi2__ ON isn2__.patient_billing_inventory_id = pbi2__.id
				    GROUP BY pbi2__.patient_billing_id, inv2__.item_id, inv2__.health_facility_id
				) isn2_ ON isn2_.patient_billing_id = pb2_.id AND pbi2_.item_id = isn2_.item_id AND pb2_.health_facility_id = isn2_.health_facility_id
                GROUP BY pbi2_.patient_billing_inventory_id
              ) pbi2_2 ON pbi2.id = pbi2_2.patient_billing_inventory_id
              LEFT JOIN (
			    SELECT
			        SUM(isn2_.cost) AS total_cost,
			        inv2_.item_id,
			        inv2_.health_facility_id,
			        pbi2_.patient_billing_id
			    FROM inventory_serial_no isn2_
			    INNER JOIN inventory inv2_ on isn2_.inventory_id = inv2_.id
			    INNER JOIN patient_billing_inventory pbi2_ ON isn2_.patient_billing_inventory_id = pbi2_.id
			    GROUP BY pbi2_.patient_billing_id, inv2_.item_id, inv2_.health_facility_id
			) isn2 ON isn2.patient_billing_id = pb2.id AND pbi2.item_id = isn2.item_id AND pb2.health_facility_id = isn2.health_facility_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 pbb3.quantity 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 pbb3.quantity ELSE 0 END) AS rentToCount_void,
                SUM(CASE WHEN pb3.billing_status IN ('Void', 'Returned') THEN 0 ELSE IF(i3.is_itemised = 0, IF(pbi3_2.inv_sub_item_count > 0, pbi3_2.total_cost,iac3.ave_cost * pbb3.quantity), isn3.total_cost) END) AS cost,
                pbb3.patient_billing_id
              FROM patient_billing_breakdown pbb3
              INNER JOIN patient_billing pb3 ON pbb3.patient_billing_id = pb3.id
              INNER JOIN item i3 ON pbb3.item_id = i3.id
              LEFT JOIN patient_billing_inventory pbi3 ON pb3.id = pbi3.patient_billing_id AND i3.id = pbi3.item_id
              LEFT JOIN item_ave_cost iac3 ON pbi3.item_id = iac3.item_id AND pb3.health_facility_id = iac3.health_facility_id
              LEFT JOIN (
                SELECT
                    pbi3_.patient_billing_inventory_id,
                    SUM(IF(i3_.is_itemised = 0, iac3_.ave_cost ,isn3_.total_cost)) AS total_cost,
                    COUNT(pbi3_.id) AS inv_sub_item_count
                FROM patient_billing_inventory pbi3_
                INNER JOIN patient_billing pb3_ ON pbi3_.patient_billing_id = pb3_.id
                INNER JOIN item i3_ ON pbi3_.item_id = i3_.id
                LEFT JOIN item_ave_cost iac3_ ON pbi3_.item_id = iac3_.item_id AND pb3_.health_facility_id = iac3_.health_facility_id
                LEFT JOIN (
				    SELECT
				        SUM(isn3__.cost) AS total_cost,
				        inv3__.item_id,
				        inv3__.health_facility_id,
				        pbi3__.patient_billing_id
				    FROM inventory_serial_no isn3__
				    INNER JOIN inventory inv3__ on isn3__.inventory_id = inv3__.id
				    INNER JOIN patient_billing_inventory pbi3__ ON isn3__.patient_billing_inventory_id = pbi3__.id
				    GROUP BY pbi3__.patient_billing_id, inv3__.item_id, inv3__.health_facility_id
				) isn3_ ON isn3_.patient_billing_id = pb3_.id AND pbi3_.item_id = isn3_.item_id AND pb3_.health_facility_id = isn3_.health_facility_id
                GROUP BY pbi3_.patient_billing_inventory_id
              ) pbi3_2 ON pbi3.id = pbi3_2.patient_billing_inventory_id
              LEFT JOIN (
			    SELECT
			        SUM(isn3_.cost) AS total_cost,
			        inv3_.item_id,
			        inv3_.health_facility_id,
			        pbi3_.patient_billing_id
			    FROM inventory_serial_no isn3_
			    INNER JOIN inventory inv3_ on isn3_.inventory_id = inv3_.id
			    INNER JOIN patient_billing_inventory pbi3_ ON isn3_.patient_billing_inventory_id = pbi3_.id
			    GROUP BY pbi3_.patient_billing_id, inv3_.item_id, inv3_.health_facility_id
			) isn3 ON isn3.patient_billing_id = pb3.id AND pbi3.item_id = isn3.item_id AND pb3.health_facility_id = isn3.health_facility_id
			  WHERE pbb3.`term` = 'Rent to Own'
              GROUP BY pbb3.patient_billing_id
            ) pbbForRentToOwn ON pb.id = pbbForRentToOwn.patient_billing_id
            LEFT JOIN (
              SELECT
                -- COUNT(pbb7.`id`) AS rentToCount,
                -- SUM(pbb7.amount) AS price,
                SUM(CASE WHEN pb7.billing_status IN ('Void', 'Returned') THEN 0 ELSE pbb7.quantity END) AS partOfCpapPackageCount,
                SUM(CASE WHEN pb7.billing_status IN ('Void', 'Returned') THEN 0 ELSE pbb7.amount END) AS price,
                SUM(CASE WHEN pb7.billing_status IN ('Void', 'Returned') THEN pbb7.amount ELSE 0 END) AS price_void,
                SUM(CASE WHEN pb7.billing_status IN ('Void', 'Returned') THEN pbb7.quantity ELSE 0 END) AS partOfCpapPackageCount_void,
                SUM(CASE WHEN pb7.billing_status IN ('Void', 'Returned') THEN 0 ELSE IF(i7.is_itemised = 0, IF(pbi7_2.inv_sub_item_count > 0, pbi7_2.total_cost,iac7.ave_cost * pbb7.quantity), isn7.total_cost) END) AS cost,
                pbb7.patient_billing_id
              FROM patient_billing_breakdown pbb7
              INNER JOIN patient_billing pb7 ON pbb7.patient_billing_id = pb7.id
              INNER JOIN item i7 ON pbb7.item_id = i7.id
              LEFT JOIN patient_billing_inventory pbi7 ON pb7.id = pbi7.patient_billing_id AND i7.id = pbi7.item_id
              LEFT JOIN item_ave_cost iac7 ON pbi7.item_id = iac7.item_id AND pb7.health_facility_id = iac7.health_facility_id
              LEFT JOIN (
                SELECT
                    pbi7_.patient_billing_inventory_id,
                    SUM(IF(i7_.is_itemised = 0, iac7_.ave_cost ,isn7_.total_cost)) AS total_cost,
                    COUNT(pbi7_.id) AS inv_sub_item_count
                FROM patient_billing_inventory pbi7_
                INNER JOIN patient_billing pb7_ ON pbi7_.patient_billing_id = pb7_.id
                INNER JOIN item i7_ ON pbi7_.item_id = i7_.id
                LEFT JOIN item_ave_cost iac7_ ON pbi7_.item_id = iac7_.item_id AND pb7_.health_facility_id = iac7_.health_facility_id
                LEFT JOIN (
				    SELECT
				        SUM(isn7__.cost) AS total_cost,
				        inv7__.item_id,
				        inv7__.health_facility_id,
				        pbi7__.patient_billing_id
				    FROM inventory_serial_no isn7__
				    INNER JOIN inventory inv7__ on isn7__.inventory_id = inv7__.id
				    INNER JOIN patient_billing_inventory pbi7__ ON isn7__.patient_billing_inventory_id = pbi7__.id
				    GROUP BY pbi7__.patient_billing_id, inv7__.item_id, inv7__.health_facility_id
				) isn7_ ON isn7_.patient_billing_id = pb7_.id AND pbi7_.item_id = isn7_.item_id AND pb7_.health_facility_id = isn7_.health_facility_id
                GROUP BY pbi7_.patient_billing_inventory_id
              ) pbi7_2 ON pbi7.id = pbi7_2.patient_billing_inventory_id
              LEFT JOIN (
			    SELECT
			        SUM(isn7_.cost) AS total_cost,
			        inv7_.item_id,
			        inv7_.health_facility_id,
			        pbi7_.patient_billing_id
			    FROM inventory_serial_no isn7_
			    INNER JOIN inventory inv7_ on isn7_.inventory_id = inv7_.id
			    INNER JOIN patient_billing_inventory pbi7_ ON isn7_.patient_billing_inventory_id = pbi7_.id
			    GROUP BY pbi7_.patient_billing_id, inv7_.item_id, inv7_.health_facility_id
			) isn7 ON isn7.patient_billing_id = pb7.id AND pbi7.item_id = isn7.item_id AND pb7.health_facility_id = isn7.health_facility_id
              WHERE pbb7.`item_type` = 'Part of CPAP Package'
              GROUP BY pbb7.patient_billing_id
            ) pbbForPartOfCpapPackage ON pb.id = pbbForPartOfCpapPackage.patient_billing_id
            LEFT JOIN (
              SELECT
                -- COUNT(pbb8.`id`) AS rentToCount,
                -- SUM(pbb8.amount) AS price,
                SUM(CASE WHEN pb8.billing_status IN ('Void', 'Returned') THEN 0 ELSE pbb8.quantity END) AS cpapTrialCount,
                SUM(CASE WHEN pb8.billing_status IN ('Void', 'Returned') THEN 0 ELSE pbb8.amount END) AS price,
                SUM(CASE WHEN pb8.billing_status IN ('Void', 'Returned') THEN pbb8.amount ELSE 0 END) AS price_void,
                SUM(CASE WHEN pb8.billing_status IN ('Void', 'Returned') THEN pbb8.quantity ELSE 0 END) AS cpapTrialCount_void,
                SUM(CASE WHEN pb8.billing_status IN ('Void', 'Returned') THEN 0 ELSE IF(i8.is_itemised = 0, IF(pbi8_2.inv_sub_item_count > 0, pbi8_2.total_cost,iac8.ave_cost * pbb8.quantity), isn8.total_cost) END) AS cost,
                pbb8.patient_billing_id
              FROM patient_billing_breakdown pbb8
              INNER JOIN patient_billing pb8 ON pbb8.patient_billing_id = pb8.id
              INNER JOIN item i8 ON pbb8.item_id = i8.id
              LEFT JOIN patient_billing_inventory pbi8 ON pb8.id = pbi8.patient_billing_id AND i8.id = pbi8.item_id
              LEFT JOIN item_ave_cost iac8 ON pbi8.item_id = iac8.item_id AND pb8.health_facility_id = iac8.health_facility_id
              LEFT JOIN (
                SELECT
                    pbi8_.patient_billing_inventory_id,
                    SUM(IF(i8_.is_itemised = 0, iac8_.ave_cost ,isn8_.total_cost)) AS total_cost,
                    COUNT(pbi8_.id) AS inv_sub_item_count
                FROM patient_billing_inventory pbi8_
                INNER JOIN patient_billing pb8_ ON pbi8_.patient_billing_id = pb8_.id
                INNER JOIN item i8_ ON pbi8_.item_id = i8_.id
                LEFT JOIN item_ave_cost iac8_ ON pbi8_.item_id = iac8_.item_id AND pb8_.health_facility_id = iac8_.health_facility_id
                LEFT JOIN (
				    SELECT
				        SUM(isn8__.cost) AS total_cost,
				        inv8__.item_id,
				        inv8__.health_facility_id,
				        pbi8__.patient_billing_id
				    FROM inventory_serial_no isn8__
				    INNER JOIN inventory inv8__ on isn8__.inventory_id = inv8__.id
				    INNER JOIN patient_billing_inventory pbi8__ ON isn8__.patient_billing_inventory_id = pbi8__.id
				    GROUP BY pbi8__.patient_billing_id, inv8__.item_id, inv8__.health_facility_id
				) isn8_ ON isn8_.patient_billing_id = pb8_.id AND pbi8_.item_id = isn8_.item_id AND pb8_.health_facility_id = isn8_.health_facility_id
                GROUP BY pbi8_.patient_billing_inventory_id
              ) pbi8_2 ON pbi8.id = pbi8_2.patient_billing_inventory_id
              LEFT JOIN (
			    SELECT
			        SUM(isn8_.cost) AS total_cost,
			        inv8_.item_id,
			        inv8_.health_facility_id,
			        pbi8_.patient_billing_id
			    FROM inventory_serial_no isn8_
			    INNER JOIN inventory inv8_ on isn8_.inventory_id = inv8_.id
			    INNER JOIN patient_billing_inventory pbi8_ ON isn8_.patient_billing_inventory_id = pbi8_.id
			    GROUP BY pbi8_.patient_billing_id, inv8_.item_id, inv8_.health_facility_id
			) isn8 ON isn8.patient_billing_id = pb8.id AND pbi8.item_id = isn8.item_id AND pb8.health_facility_id = isn8.health_facility_id
              WHERE pbb8.`item_type` = 'CPAP Trial'
              GROUP BY pbb8.patient_billing_id
            ) pbbForCpapTrial ON pb.id = pbbForCpapTrial.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'";
	                break;
	            default:
		            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pbb.`item_type` = :item_type";
		            $stmtParams['item_type'] = $params['item_type'];
            }

        }

        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,
				 p.`address` AS address,
				 CONCAT_WS(',', p.`phone_no`,  p.`phone_no_two`, p.`phone_no_three`) AS phoneNo,
				 CONCAT_WS(',', p.`email`,  p.`email_two`, p.`email_three`) AS email,
				 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.`id` AS itemId,
                 i.`description` AS itemDesc,
                 pbb.`term`,
                 pbb.`item_type` AS itemType,
                 pbb.`quantity` AS qty,
                 pbb.`price`,
	             IF(i.is_itemised = 0, IF(pbi_2.inv_sub_item_count > 0, pbi_2.total_cost ,iac.ave_cost * pbb.quantity), isn.total_cost) AS cost,
                 pbi_2.inv_sub_item_count,
                 pbi_2.sub_items
                 -- IF(i.is_itemised = 0, iac.ave_cost, isn.total_cost) AS cost
            FROM `patient_billing_breakdown` pbb
            INNER JOIN `patient_billing` pb ON pb.`id` = pbb.`patient_billing_id`
            INNER 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`
            -- LEFT JOIN patient_billing_inventory pbi ON pbi.patient_billing_id = pb.id AND pbi.item_id = i.id
            LEFT JOIN patient_billing_inventory pbi ON pbi.id = (
                SELECT pbi_tmp.id
                FROM patient_billing_inventory pbi_tmp
                WHERE pbi_tmp.patient_billing_id = pb.id
                AND pbi_tmp.item_id = i.id
                LIMIT 1
            )
            LEFT JOIN item_ave_cost iac ON pbi.item_id = iac.item_id AND pb.health_facility_id = iac.health_facility_id
            LEFT JOIN (
                SELECT
                    pbi_.patient_billing_inventory_id,
                    SUM(IF(i_.is_itemised = 0, iac_.ave_cost ,isn_.total_cost)) AS total_cost,
                    COUNT(pbi_.id) AS inv_sub_item_count,
                   	GROUP_CONCAT(CONCAT_WS('~', i_.id, i_.description, IF(i_.is_itemised = 0, iac_.ave_cost ,isn_.total_cost)) SEPARATOR '@') AS sub_items
                FROM patient_billing_inventory pbi_
                INNER JOIN patient_billing pb_ ON pbi_.patient_billing_id = pb_.id
                INNER JOIN item i_ ON pbi_.item_id = i_.id
                LEFT JOIN item_ave_cost iac_ ON pbi_.item_id = iac_.item_id AND pb_.health_facility_id = iac_.health_facility_id
                LEFT JOIN (
				    SELECT
				        SUM(isn__.cost) AS total_cost,
				        inv__.item_id,
				        inv__.health_facility_id,
				        pbi__.patient_billing_id
				    FROM inventory_serial_no isn__
				    INNER JOIN inventory inv__ on isn__.inventory_id = inv__.id
				    INNER JOIN patient_billing_inventory pbi__ ON isn__.patient_billing_inventory_id = pbi__.id
				    GROUP BY pbi__.patient_billing_id, inv__.item_id, inv__.health_facility_id
				) isn_ ON isn_.patient_billing_id = pb_.id AND pbi_.item_id = isn_.item_id AND pb_.health_facility_id = isn_.health_facility_id
                GROUP BY pbi_.patient_billing_inventory_id
              ) pbi_2 ON pbi.id = pbi_2.patient_billing_inventory_id
          	LEFT JOIN (
			    SELECT
			        SUM(isn_.cost) AS total_cost,
			        inv_.item_id,
			        inv_.health_facility_id,
			        pbi_.patient_billing_id
			    FROM inventory_serial_no isn_
			    INNER JOIN inventory inv_ on isn_.inventory_id = inv_.id
			    INNER JOIN patient_billing_inventory pbi_ ON isn_.patient_billing_inventory_id = pbi_.id
			    GROUP BY pbi_.patient_billing_id, inv_.item_id, inv_.health_facility_id
			) isn ON isn.patient_billing_id = pb.id AND pbi.item_id = isn.item_id AND pb.health_facility_id = isn.health_facility_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
            AND pb.billing_status NOT IN ('Void', 'Returned')
        ";
        $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;
    }
}
