<?php

namespace RxConnexionBundle\Repository;

use RxConnexionBundle\Entity\PatientBillingBreakdownEntity;
use RxConnexionBundle\Entity\PatientRentalEntity;

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

        $errors = array();

        $action = $patientRental_form['action'];

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

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

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

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

            if(empty($patientRental_form['start_date'])) {
                $errors[] = 'Start date should not be blank.';
            }


            if(empty($patientRental_form['end_date'])) {
                $errors[] = 'End 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($patientRental_form['payer_type'])) {
                $errors[] = 'Please select payer type.';
            } else if($patientRental_form['payer_type'] === PatientRentalEntity::PAYER_TYPE_INSURANCE && empty($patientRental_form['patientInsurance'])) {
                $errors[] = 'You have selected insurance as payer type, please enter the name of the insurance.';
            }

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

                foreach($patientRental_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'])) {
                            $errors[] = 'Please enter item type 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;
                    }*/
                }
            }
        }

        return $errors;
    }

    public function ajax_list(array $get){

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

        $columns = array(
            array('pr.`start_date`',  "DATE_FORMAT(pr.`start_date`, '%m/%d/%Y')", 'startDate'),
            array('pr.`end_date`',  "DATE_FORMAT(pr.`end_date`, '%m/%d/%Y')", 'endDate'),
            array("hf2.`description`", "hf2.`description`", 'healthFacility'),
            array('pr.`payer_type`', "IF(pr.`payer_type` = 'Self Pay', 'Self Pay', i.`description`)", 'payer'),
            array('pr.`payment_day`', 'pr.`payment_day`', 'paymentDay'),
            array('pr.`amortization_amount`', 'pr.`amortization_amount`', 'amortization_amount'),
            array('pr.`amount_due`', 'pr.`amount_due`', 'amountDue'),
            array('pr.`amount_balance`', 'pr.`amount_balance`', 'amountBal'),
            array('pr.`id`', 'pr.`id`')
        );
        $asColumns = array(
            ''
        );

        $select = "SELECT";
        $from = "FROM `patient_rental` pr";
        $joins = "
            LEFT JOIN `health_facility` hf ON pr.`health_facility_id` = hf.`id`
            LEFT JOIN `health_facility` hf2 ON pr.`health_facility_for_address_id` = hf2.`id` 
            LEFT JOIN `patient_billing_breakdown_tmp` pbb ON pr.`id` = pbb.`patient_rental_id`
            LEFT JOIN `patient_insurance` pi ON pr.`patient_insurance_id` = pi.`id`
            LEFT JOIN `insurance` i ON pi.`insurance_id` = i.`id`
        ";
        $sqlWhere = "WHERE pr.`patient_id` = :patient_id";
        $sqlWhere .= " AND pr.`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();

        $query = $conn->prepare("
            CREATE TEMPORARY TABLE IF NOT EXISTS `patient_billing_breakdown_tmp` (
                INDEX `IDX_TMP_000AAA` (`patient_rental_id`)
            ) AS (
                SELECT
                    pbb.`patient_rental_id`,
                    GROUP_CONCAT(
                      CONCAT_WS(
                          ' - ',
                          IF(pc.`id` IS NOT NULL, CONCAT_WS(' - ', pc.`id`, DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y'), ct.`description`), null),
                          CASE pbb.`type`
                              WHEN 'Chart Procedure' THEN cp.`description`
                              WHEN 'Item' THEN it.`description`
                              ELSE pbb.`description`
                          END,
                          CONCAT(pbb.`quantity`, ' x ', pbb.`price`, ' = ', pbb.`amount`)
                      )
                      SEPARATOR '<br/>'
                    ) AS breakdown
                FROM `patient_billing_breakdown` pbb
                LEFT JOIN `patient_rental` pr ON pbb.`patient_rental_id` = pr.`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 pr.`patient_id` = :patient_id
                AND pbb.`is_deleted` = 0
                GROUP BY pbb.`patient_rental_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 nativeInsert($patientRental_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` = :healthFacilityRentalForAddress
                    LIMIT 1
                )
                LIMIT 1
            );
            SET @invNoBilling = (
                SELECT pr.`invoice_no` * 1 as invoice_no
                FROM `patient_rental` pr
                LEFT JOIN `health_facility` hf on pr.`health_facility_for_address_id` = hf.`id`
                WHERE hf.`company_id` = (
                    SELECT hf2.`company_id`
                    FROM `health_facility` hf2
                    WHERE hf2.`id` = :healthFacilityRentalForAddress
                    LIMIT 1
                )
                ORDER BY invoice_no DESC
                LIMIT 1
            );
        ", array('healthFacilityRentalForAddress' => $patientRental_form['healthFacilityRentalForAddress']));

        $rawQuery = "
            INSERT INTO `patient_rental` (
                `invoice_no`, 
                `serial_no`, 
                `patient_id`, 
                `patient_insurance_id`, 
                `start_date`, 
                `end_date`, 
                `amortization_amount`, 
                `payment_day`, 
                `amount_due`, 
                `payer_type`, 
                `note`, 
                `is_deleted`, 
                `created_by`, 
                `created_at`, 
                `updated_by`, 
                `updated_at`,
                `health_facility_id`, 
                `health_facility_for_address_id`
            ) VALUES (
                IF(@invNoBilling > @invNoCompany, @invNoBilling, @invNoCompany) + 1, 
                :serial_no, 
                :patient, 
                :patientInsurance, 
                :start_date, 
                :end_date, 
                :amortization_amount, 
                :payment_day, 
                :amount_due, 
                :payer_type, 
                :note, 
                0, 
                :created_by, 
                :created_at, 
                :updated_by, 
                :updated_at, 
                :healthFacility, 
                :healthFacilityRentalForAddress
            );
            -- SET @lastInsertID := LAST_INSERT_ID();
        ";
        $stmtParams = array(
            //'healthFacilityForAddress_id' => $patientBilling_form['healthFacilityForAddress'],
            //'healthFacilityForAddress_company_id' => $healthFacilityForAddress->getCompany()->getId(),
            'serial_no' => $patientRental_form['serial_no'],
            'patient' => base64_decode($patientRental_form['patient']),
            'patientInsurance' => !empty($patientRental_form['patientInsurance']) ? $patientRental_form['patientInsurance'] : null,
            'start_date' => date_format(date_create_from_format('m/d/Y', $patientRental_form['start_date']), 'Y-m-d H:i:s'),
            'end_date' => date_format(date_create_from_format('m/d/Y', $patientRental_form['end_date']), 'Y-m-d H:i:s'),
            'amortization_amount' => $patientRental_form['amortization_amount'],
            'payment_day' => $patientRental_form['payment_day'],
            'amount_due' => $patientRental_form['amount_due'],
            'payer_type' => $patientRental_form['payer_type'],
            'note' => $patientRental_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'),
            'healthFacility' => $patientRental_form['healthFacility'],
            'healthFacilityRentalForAddress' => $patientRental_form['healthFacilityRentalForAddress'],
        );

        // 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);
        $patientRental_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(
            'patientRental_id' => $patientRental_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($patientRental_form['patientBillingBreakdowns'] AS $key => $breakdown) {
            $rawQuery .= "
                INSERT INTO `patient_billing_breakdown` (
                  `patient_rental_id`, 
                  `patient_chart_id`, 
                  `chart_procedure_id`, 
                  `item_id`, 
                  `type`, 
                  `description`, 
                  `item_type`, 
                  `term`, 
                  `quantity`, 
                  `price`, 
                  `amount`, 
                  `is_deleted`, 
                  `created_by`, 
                  `created_at`, 
                  `updated_by`, 
                  `updated_at`
                ) VALUES (
                  :patientRental_id, 
                  :patientChart_$key, 
                  :chartProcedure_$key, 
                  :item_$key, 
                  :type_$key, 
                  :description_$key, 
                  :item_type_$key,
                  :term_$key,
                  :quantity_$key, 
                  :price_$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['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 $patientRental_id;
    }

    public function getForAutoGenerateRentalInvoice()
    {
        $today = new \DateTime();
        $conn = $this->getEntityManager()->getConnection();
        $stmtParams = array(
            'payment_day' => $today->format('d'),
            'today' => $today->format('Y-m-d 00:00:00'),
            'today_mdY' => $today->format('m/d/Y'),
            //'due_date' => date('m/d/Y', strtotime($today->format('m/d/Y') . ' + 2 days')),
        );
        $sql = "SELECT 
                        pr.id,
                        pr.serial_no,
                        TO_BASE64(pr.patient_id) AS patient,
                        pr.patient_insurance_id AS patientInsurance,
                        :today_mdY AS billing_date,
                        :today_mdY AS due_date,
                        pr.amount_due,
                        pr.payer_type,
                        pr.note,
                        pr.health_facility_id AS healthFacility,
                        pr.health_facility_for_address_id AS healthFacilityForAddress,
                        '' AS comment,
                        '' AS endorsed_to,
                        '' AS demand_letter_date,
                        '' AS insurance_claim_date,
                        pr.amortization_amount,
                       pbb2.breakdown AS patientBillingBreakdowns
                FROM patient_rental pr
                LEFT JOIN (
                  SELECT pbb.patient_rental_id,
                         GROUP_CONCAT(
                             CONCAT_WS('~',
                                       COALESCE(pbb.patient_chart_id, ''),
                                       COALESCE(pbb.chart_procedure_id, ''),
                                       COALESCE(pbb.item_id, ''),
                                       COALESCE(pbb.type, ''),
                                       COALESCE(pbb.description, ''),
                                       COALESCE(pbb.serial_number, ''),
                                       COALESCE(pbb.item_type, ''),
                                       COALESCE(pbb.term, ''),
                                       COALESCE(pbb.quantity, '')) SEPARATOR
                             '@@') AS breakdown
                  FROM patient_billing_breakdown pbb
                  GROUP BY pbb.patient_rental_id
                ) AS pbb2 ON pbb2.patient_rental_id = pr.id
                WHERE pr.payment_day = :payment_day
                AND :today BETWEEN pr.start_date AND pr.end_date;";
        $query = $conn->prepare($sql);
        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }
}
