<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class DeathRegister extends Model
{
    use HasFactory;
    
    protected $fillable = [
        'ipd_id',
        'death_date',
        'death_time',
        'death_reason',
        'guardian_name',
        'death_report',
        'attachment_name',
        'file_url',
        'is_active',
        'gender_id'
    ];



    static public function getStatement($where = [], $filter = []) 
    {

        $data = DeathRegister::select([
               'death_registers.id as death_registers_id',
               'death_registers.ipd_id as ipd_id',
               'ipd_admissions.ipd_admission_no as ipd_admission_case_id',
               'death_registers.death_date as death_date',
               'death_registers.death_time as death_time',
               'death_registers.death_reason as death_reason',
               'death_registers.guardian_name as guardian_name',
               'death_registers.death_report as death_report',
               'death_registers.attachment_name as attachment_name',
               'accounts.name as patient_name',
               'death_registers.file_url as file_url',
               'death_registers.is_active as is_active',
               'ipd_admissions.id',
               'accounts.id',
               'genders.name as gender_name' ,
               'ipd_discharges.id'

        ])
        ->leftJoin('ipd_admissions' , 'ipd_admissions.id' , '=' , 'death_registers.ipd_id')
        ->leftJoin('accounts' , 'accounts.id' , '=' , 'ipd_admissions.patient_id')
        ->leftJoin('account_contacts' , 'account_contacts.account_id' , '=' , 'accounts.id')
        ->leftJoin('account_titles' , 'account_titles.id' , '=' , 'accounts.account_title_id')
        ->leftJoin('genders' , 'genders.id' , '=' , 'account_contacts.account_gender_id')
        ->leftJoin('ipd_discharges', 'ipd_discharges.ipd_admission_id', '=', 'ipd_admissions.id')
        
        ->leftJoin('companies', 'companies.id', '=', 'accounts.company_id')
        ->when(!empty($where), function($query) use ($where) {
            $query->where($where);
        })
       ->when(!empty($filter['from_date']) && !empty($filter['to_date']), function($query) use ($filter) {
            $query->when($filter['from_date'] == $filter['to_date'], function($query) use ($filter) {
            })
            ->when($filter['from_date'] != $filter['to_date'], function($query) use ($filter) {
                $query->whereBetween('ipd_admissions.admission_date_time', [$filter['from_date'], $filter['to_date']]);
            });
        })
        ->when(!empty($filter['search_account']), function($query) use ($filter) {
            $query->where(function($query) use ($filter) {
                $query->where('accounts.name', 'LIKE', '%' . $filter['search_account'] . '%');
            });
        })
        ->when(!empty($filter['company']), function($query) use ($filter) {
            $query->where('accounts.company_id', $filter['company']);
        })
        ->when(!empty($filter['listing_type']), function($query) use ($filter){
            if ($filter['listing_type'] == 'active') {
            $query->whereNull('ipd_discharges.id');
        } elseif ($filter['listing_type'] == 'discharged') {
            $query->whereNotNull('ipd_discharges.id');
        }
        })
        ->groupBy('ipd_admissions.id')
        ->get();

        return $data;
    }
}
