GetMedicalRecordsProductByQueueNo

class GetMedicalRecordsProductByQueueNo implements BusinessFunction {

    public function getDescription(){
        return "Get Medical Records List";
    }

    public function execute($dto){
        //\Log::debug($dto);
        $recordOwnerId  = $dto["record_owner_id"];
        $ouCode         = $dto["ou_code"];
        $queueNo        = $dto["queue_no"];
        $dateNow        = $dto["date_now"];

        $queue = ConsultationQueue::where('ou_code_alias', $ouCode)
                ->where('doc_date', $dateNow)
                ->where('queue_no', $queueNo)
                ->first();

        if(is_null($queue)){
            throw new CoreException(ERROR_BUSINESS_VALIDATION,[],
                ["queue_no" => ["No antrian tidak ditemukan"]]);
        }

        $recordOwner    = RecordOwner::find($recordOwnerId);
        $patient        = Patient::find($queue->patient_id);

        $customer       = Customer::where('customer_code', $patient->patient_code)->first();
        $medicalRecord  = MedicalRecord::where('consultation_queue_id', $queue->consultation_queue_id)->first();

        $raw = "SELECT A.product_id, A.product_code, A.product_name, X.qty, I.how_to_use,
                I.prescription_balance_item_id, X.medical_record_product_id,
                H.uom_name, Z.salesman_id, Z.full_name as salesman_name,
                COALESCE(E.qty, 0) as current_qty, E.product_balance_id, E.product_balance_stock_id,    Y.record_date, Y.record_time,
                COALESCE(F.gross_sell_price, G.gross_sell_price, 0) AS gross_sell_price,
                COALESCE(F.curr_code, G.curr_code, '') AS curr_code,
                COALESCE(F.flg_tax_amount, G.flg_tax_amount) as flg_tax_amount,
                COALESCE(F.tax_id, G.tax_id) as tax_id,
                COALESCE(F.sell_price, G.sell_price, 0) as sell_price,
                X.create_username as username, Y.remark_cashier
            FROM m_medical_record_product X
            JOIN m_prescription_balance_item I ON I.medical_record_product_id = X.medical_record_product_id
            JOIN m_medical_record Y ON Y.medical_record_id = X.medical_record_id
            JOIN m_salesman Z ON Z.short_name = X.create_username
            JOIN m_product A ON A.product_id = X.product_id
            JOIN m_ctgr_product B ON B.ctgr_product_id = A.ctgr_product_id
            JOIN m_group_product C ON C.group_product_id = B.group_product_id
                AND C.group_product_code = :groupProductCode
            JOIN m_uom H ON H.uom_id = A.base_uom_id
            LEFT JOIN in_product_balance D ON D.product_id = A.product_id
            LEFT JOIN in_product_balance_stock E ON E.product_balance_id = D.product_balance_id
                AND E.product_id = D.product_id
                AND E.warehouse_id = :warehouseId
                AND E.product_status = 'GOOD'
                AND E.record_owner_id = :recordOwnerId
            LEFT JOIN m_sell_price_product_for_so F ON F.product_id = A.product_id AND :date BETWEEN F.date_from AND F.date_to AND F.ou_id = :ouId
                AND F.price_level = :priceLevel
            LEFT JOIN m_sell_price_product_for_so G ON G.product_id = A.product_id AND :date BETWEEN G.date_from AND G.date_to
                AND G.ou_id = (f_get_ou_bu_structure_custom(:ouId)).ou_bu_id AND G.price_level = :priceLevel
            WHERE Y.consultation_queue_id = :consultationQueueId
                AND Y.record_date = :date AND Y.patient_id = :patientId
                AND I.qty_redeem = 0 AND I.active = 'Y'
            ORDER BY Y.record_time ASC";

            $medicalRecordProduct = DB::select(DB::raw($raw), [
                "warehouseId"           => $recordOwner->warehouse_id,
                "ouId"                  => $recordOwnerId,
                "priceLevel"            => $customer->price_level,
                "date"                  => $dateNow,
                "patientId"             => $queue->patient_id,
                "recordOwnerId"         => $recordOwnerId,
                "groupProductCode"      => 'FG',
                "consultationQueueId"   => $queue->consultation_queue_id
            ]);

        // Ambil data pasien
        $builder_patient = new QueryBuilder();
        $builder_patient->add(" SELECT A.patient_id, A.salutation, A.short_name, A.fullname, A.place_of_birth,
                        A.date_of_birth, A.gender, f_get_full_current_address_patient(A.patient_id) AS full_address ")
            ->add(" FROM ")->add(Patient::getTableName())->add(" A ")
            ->add(" WHERE A.patient_id = :patientId ");

        $query_builder_patient = new CreateNativeQuery($builder_patient->toString());
        $query_builder_patient->setParameter("patientId", $patient->patient_id);

        $result_patient = $query_builder_patient->getSingleResult();

        return [
            'medical_record_product_list'   => $medicalRecordProduct,
            'customer'                      => $customer,
            'patient'                       => !is_null($result_patient) ? $result_patient : [],
            'name_with_salutation'          => !is_null($patient) ? $patient->salutation.' - '.$patient->short_name.', '.$patient->fullname  : '',
            'current_address'               => !is_null($result_patient) ? $result_patient->full_address : '',
            'remark_cashier'                => !is_null($medicalRecord) ? $medicalRecord->remark_cashier : '',
            'queue'                         => $queue
        ];
    }
}