<?php
defined('BASEPATH') or exit('No direct script access allowed');
class M_rekamMedis extends CI_Model
{
    public function pasien()
    {
        date_default_timezone_set("Asia/jakarta");
        $tgl = date('Y-m-d');

        $doctorId = $this->session->userdata('doctorId');
        if(!empty($doctorId)){
            return $this->db->query("SELECT *, d.FirstName as namaDokter , d.LastName as lasNameDokter FROM Cases c join company cp on cp.CompanyCode = c.CompanyCode 
            join CaseDetail cd on cd.[Case ID] = c.[Case ID] 
            join CaseAdmin ca on ca.[Case ID] = c.[Case ID] 
			join Doctor d on d.DoctorID = ca.DoctorID 
            WHERE  [Intake Date] = '$tgl'  AND CaseDetail = '1' and ca.DoctorID = '$doctorId'  AND Pelayanan IS NULL");
        }
        return $this->db->query("SELECT *, d.FirstName as namaDokter , d.LastName as lasNameDokter   FROM Cases c join company cp on cp.CompanyCode = c.CompanyCode 
            join CaseDetail cd on cd.[Case ID] = c.[Case ID] 
            join CaseAdmin ca on ca.[Case ID] = c.[Case ID] 
			join Doctor d on d.DoctorID = ca.DoctorID 
            WHERE  [Intake Date] = '$tgl'  AND CaseDetail = '1'  AND Pelayanan IS NULL");
    }

    public function transaksipasien($statusCaseId = "")
    {
      date_default_timezone_set("Asia/jakarta");
       $tgl = date('Y-m-d');
       $where = ""; 
       if(!empty($statusCaseId)){
            $where = "and c.[Case Status] = '$statusCaseId'";
       }
       
       return $this->db->query("SELECT *, d.FirstName as namaDokter , d.LastName as lasNameDokter,c.[Case Status] FROM Cases c join company cp on cp.CompanyCode = c.CompanyCode 
       join CaseDetail cd on cd.[Case ID] = c.[Case ID] 
       join CaseAdmin ca on ca.[Case ID] = c.[Case ID] 
       join Doctor d on d.DoctorID = ca.DoctorID 
       WHERE  [Intake Date] = '$tgl'  AND CaseDetail = '1'" . $where);
       
    }

    public function pemeriksaan($id)
    {
        return $this->db->query("SELECT *, n.CompanyName as jaminan , t.FirstName + ' ' + t.LastName as doctorName FROM Cases c, Company n, CaseDetail d, Doctor t, Patient p WHERE c.[CompanyCode]=n.[CompanyCode] AND c.[Case ID]=d.[Case ID] AND d.[DoctorID]=t.[DoctorID] AND c.PatientCode=p.PatientCode AND c.[Case ID] = '$id'");
    }

    public function hasilpemeriksaan($id)
    {
        //return $this->db->query("SELECT * FROM tabelPemeriksaan WHERE [Case ID] = '$id'");
        return $this->db->query("SELECT e.case_id,e.tingkat_kesadaran,e.vital_sign,e.poli,e.keluhan_utama,e.penyakit_terdahulu,
        e.pemeriksaan_fisik,e.diagnosa_primer,e.diagnosa_sekunder,e.rencana_tindakan,e.tindak_lanjut,e.catatan,e.status,
        e.tanggal_kontrol,e.tanggal_meninggal,e.poli_tujuan,e.instansi_tujuan,e.obat,e.alergi,e.kdhamil,e.kdimunisasi,
        e.kdkb,e.paneldiag,i.DESCRPTION,d.DESCRPTION as diagsekunder
        FROM erm_pemeriksaan_fisik e 
        join ICD9 i on e.diagnosa_primer = i.ICD_CODE
        Left join  ICD9 d on e.diagnosa_sekunder = d.ICD_CODE
        WHERE case_id = '$id'");
    }  
    
    public function hasilpemeriksaangigi($id)
    {
        return $this->db->query("SELECT * FROM ermPemeriksaanGigi WHERE caseid = '$id'");
    }    
    
    public function getPoli($id)
    {
        
        return $this->db->query("SELECT distinct Unit as ActivityClass FROM CaseAdmin where [Case ID] = '$id'  ");
    }

    public function getCasesByPatientCode($patientCode)
    {
        
        return $this->db->query("select top 5 * from cases where PatientCode  = '$patientCode' order by [Case ID] desc");
    }

    public function diagnosa()
    {
        $dataDiagnosa = $this->db->query("Select * From ICD9 where Active ='1' ")->result();
        $bb = json_encode($dataDiagnosa);
        return json_decode($bb, TRUE);
    }

    public function hasilDiagnosa($id)
    {
        return $this->db->query("SELECT * FROM tabelDiagnosa t, ICD9 i WHERE t.ICD_Code=i.ICD_CODE AND [Case ID] = '$id'");
    }

    public function codediagnosa($id)
    {
        return $this->db->query("SELECT * FROM tabelDiagnosa WHERE [Case ID] = '$id'");
    }

    public function countCaseDetail($id)
    {
        return $this->db->query("SELECT * FROM tabelCaseDetail WHERE [Case ID] = '$id'");
    }

    public function tab($id)
    {
        return $this->db->query("SELECT * FROM tabelTaskID WHERE [Case ID] = '$id'");
    }

    public function hasilRujukan($id)
    {
        return $this->db->query("SELECT * FROM tabelRujukan WHERE [Case ID] = '$id'");
    }

    public function hasilTindakan($id)
    {
        return $this->db->query("SELECT * FROM tabelTindakan WHERE [Case ID] = '$id'");
    }

    public function hasilObat($id)
    {
        return $this->db->query("SELECT * FROM tabelObat WHERE [Case ID] = '$id'");
    }
    public function obat($id2)
    {
        return $this->db->query("SELECT a.[Case ID],a.DrugsID,p.Description,a.Unit,a.Qty,a.Charge,a.Dosis,a.Cara,a.Ket  from [Activity Drugs] a
        Inner JOIN Pharmacy p on a.DrugsID = p.PharmacyCode
        where [Case ID] = '$id2'
        ");
    }

    public function hasilLaboratory($id)
    {
        return $this->db->query("SELECT * FROM tabelLaboratory WHERE [Case ID] = '$id'");
    }
    
    public function histori($mr)
    {
        return $this->db->query("SELECT TOP 1 * FROM tabelCaseDetail WHERE [PatientCode] = '$mr' order by [Case ID] DESC");
    }

    public function cariMr($mr)
    {
        return $this->db->query("SELECT TOP (10) c.[Case ID], s.Description, m.CompanyName, s.RegStatus, cd.diagnosa_primer, s.PatientCode
        FROM   tabelCaseDetail AS c INNER JOIN
                     Cases AS s ON c.[Case ID] = s.[Case ID] INNER JOIN
                     erm_pemeriksaan_fisik AS cd ON c.[Case ID] = cd.case_id INNER JOIN
                     Company AS m ON s.CompanyCode = m.CompanyCode
        WHERE (c.PatientCode = '$mr')");
    }

    public function record1($tanggal)
    {
        $userdata = $data['user'] = $this->M_user->getUserSession()->row_array();
        $idalias = $userdata["IdAlias"];
        return $this->db->query("SELECT s.[Case ID],s.Description,s.RegStatus,m.CompanyName,s.[Case Status],ca.Unit,d.FirstName as dokter,d.LastName,c.PatientCode,c.[Intake Date], er.StatusResume,er.diagnosa_primer  
        FROM tabelCaseDetail c, Cases s, Company m, CaseAdmin ca, Doctor d, erm_pemeriksaan_fisik er WHERE c.[Case ID]=s.[Case ID] AND s.CompanyCode=m.CompanyCode AND
        ca.[Case ID] = s.[Case ID] and d.DoctorID = ca.DoctorID AND
        er.case_id = s.[Case ID]
        AND c.[Intake Date] = '$tanggal' And d.DoctorID = '$idalias' ORDER BY s.[Case ID] DESC");
    }

    public function filter()
    {
        extract($_GET);
        return $this->db->query("SELECT * FROM tabelCaseDetail c, Cases s, Company m WHERE c.[Case ID]=s.[Case ID] AND s.CompanyCode=m.CompanyCode AND c.[Intake Date] = '$tanggal' ORDER BY s.[Case ID] DESC");
    }

    public function record2($tanggal)
    {
        $userdata = $data['user'] = $this->M_user->getUserSession()->row_array();
        $idalias = $userdata["IdAlias"];
        return $this->db->query("SELECT s.[Case ID],s.Description,s.RegStatus,m.CompanyName,s.[Case Status],ca.Unit,d.FirstName as dokter,d.LastName,c.PatientCode,c.[Intake Date],er.StatusResume,er.diagnosa_primer 
        FROM tabelCaseDetail c, Cases s, Company m, CaseAdmin ca, Doctor, erm_pemeriksaan_fisik er d WHERE c.[Case ID]=s.[Case ID] AND s.CompanyCode=m.CompanyCode AND
        ca.[Case ID] = s.[Case ID] and d.DoctorID = ca.DoctorID AND
        er.case_id = s.[Case ID]
        AND c.[Intake Date] = '$tanggal'  And d.DoctorID = '$idalias' AND Obat IS NULL ORDER BY s.[Case ID] DESC");
    }

    public function dosis()
    {
        return $this->db->query("SELECT * FROM Resep_Dosis");
    }

    public function CaraPakai()
    {
        return $this->db->query("SELECT * FROM Resep_CaraPakai");
    }

    public function usiakehamilan()
    {
        return $this->db->query("SELECT * FROM Usia_hamil");
    }

    public function jenisimunisasi()
    {
        return $this->db->query("SELECT * FROM Jenis_Imunisasi");
    }

    public function jeniskb()
    {
        return $this->db->query("SELECT * FROM Jenis_KB");
    }

    public function pharmacy()
    {
        return $this->db->query("SELECT * FROM Pharmacy WHERE Active = '1'");
    }

    public function monitoring($type)
    {

        $tgl = $this->input->post('tanggal');

        date_default_timezone_set("Asia/jakarta");

        $type = strtoupper($type);

        if(empty($tgl)){

            $tgl = date('Y/m/d');
            return $this->db->query("SELECT  fi.case_id,fi.erm_pemeriksaan_fisik_id,c.[Case ID], c2.CompanyName , c.Description as FirstName, 
                                d.FirstName as dokter,d.LastName,cd.Unit , c.[Patient Status] ,
                                pr.header, pr.status , pr.createDate,fi.obat,fi.rencana_tindakan,cl.NoAntrian
                                FROM pemeriksaanRecord pr 
                                JOIN erm_pemeriksaan_fisik fi on fi.case_id = pr.caseId
                                right JOIN Cases c on c.[Case ID] = pr.caseId 
                                JOIN CaseDetail cl on cl.[Case ID] = c.[Case ID] 
                                JOIN CaseAdmin cd on c.[Case ID] = cd.[Case ID] 
                                Join Doctor d on d.DoctorID = cd.DoctorID 
                                JOIN Company c2 on c2.CompanyCode = c.CompanyCode 
                                WHERE pr.status = 1 
                                and CONVERT(varchar,createDate ,111) = '$tgl' and pr.header = '$type' order by createDate ASC  ");
        }

        $tgl = date("Y/m/d", strtotime($tgl));
        return $this->db->query("SELECT  fi.case_id,fi.erm_pemeriksaan_fisik_id,c.[Case ID], c2.CompanyName , c.Description as FirstName, 
                                d.FirstName as dokter,d.LastName,cd.Unit , c.[Patient Status] ,
                                pr.header, pr.status , pr.createDate,fi.obat,fi.rencana_tindakan,cl.NoAntrian
                                FROM pemeriksaanRecord pr 
                                JOIN erm_pemeriksaan_fisik fi on fi.case_id = pr.caseId
                                right JOIN Cases c on c.[Case ID] = pr.caseId 
                                JOIN CaseDetail cl on cl.[Case ID] = c.[Case ID] 
                                JOIN CaseAdmin cd on c.[Case ID] = cd.[Case ID] 
                                Join Doctor d on d.DoctorID = cd.DoctorID 
                                JOIN Company c2 on c2.CompanyCode = c.CompanyCode 
                                WHERE pr.status = 1 
                                and CONVERT(varchar,createDate ,111) = '$tgl' and pr.header = '$type' order by createDate ASC  ");
    }

    public function monitoringapotik($type)
    {

        //$tgl = $this->input->post('tanggal');
        $tgl = date('Y/m/d');

        date_default_timezone_set("Asia/jakarta");

        $type = strtoupper($type);

        if(empty($tgl)){

            $tgl = date('Y/m/d');
            return $this->db->query("SELECT epf.case_id , epf.erm_pemeriksaan_fisik_id, c2.CompanyName , p.FirstName , c.[Patient Status] , pr.header, pr.status , pr.createDate, epf.obat,
            d.FirstName as dokter,d.LastName,cd.Unit  FROM pemeriksaanRecord pr LEFT JOIN erm_pemeriksaan_fisik epf on epf.case_id = pr.caseId
            JOIN Cases c on c.[Case ID] = epf.case_id JOIN Patient p on p.PatientCode = c.PatientCode 
            JOIN Company c2 on c2.CompanyCode = c.CompanyCode
            JOIN CaseAdmin cd  on c.[Case ID] = cd.[Case ID]
            Join Doctor  d on d.DoctorID = cd.DoctorID
            WHERE pr.status = 2 and CONVERT(varchar,createDate ,111) = '$tgl' and pr.header = '$type' ");
        }

        $tgl = date("Y/m/d", strtotime($tgl));
        return $this->db->query("SELECT epf.case_id , epf.erm_pemeriksaan_fisik_id, c2.CompanyName , p.FirstName , c.[Patient Status] , pr.header, pr.status,  pr.createDate, epf.obat,  d.FirstName as dokter,d.LastName,cd.Unit    FROM pemeriksaanRecord pr LEFT JOIN erm_pemeriksaan_fisik epf on epf.case_id = pr.caseId
        JOIN Cases c on c.[Case ID] = epf.case_id JOIN Patient p on p.PatientCode = c.PatientCode
        JOIN Company c2 on c2.CompanyCode = c.CompanyCode 
        JOIN CaseAdmin cd  on c.[Case ID] = cd.[Case ID]
        Join Doctor  d on d.DoctorID = cd.DoctorID
        WHERE pr.status = 2 and CONVERT(varchar,createDate ,111) = '$tgl' and pr.header = '$type' ");
    }

    public function getErmSetting($name)
    {
        return $this->db->query("SELECT * FROM ermSetting WHERE name = '$name'");
    }

    public function save_batch($data2){
		return $this->db->insert_batch('LabRequestDetail', $data2);
	  }

    public function save_batch1($data2){
		return $this->db->insert_batch('tblLabResultDetail', $data2);
	  }

    public function HasilLab($id)
   {
    return $this->db->query("select lh.IdRequestHeader, ld.PackedID,pd.ResultID,lf.Bahasa, pc.Description as [Group], lf.NormalValue,lf.ResultValue,pc.Description
        from LabRequestHeader lh join LabRequestDetail ld on lh.IdRequestHeader = ld.IdRequestHeader
        join tblLabPackedDetail pd on pd.PackedID = ld.PackedID
		join tblLabPacked pc on pc.PackedID = pd.PackedID
        join tblLabResultDetail lf on lf.ResultID = pd.ResultID where lh.CaseID  = '$id' ");
   } 

   public function HeaderLab($id)
   {
    return $this->db->query("select lh.IdRequestHeader, ld.PackedID, pd.Description, lh.[status]
    from LabRequestHeader lh join LabRequestDetail ld on lh.IdRequestHeader = ld.IdRequestHeader
    join tblLabPacked pd on pd.PackedID = ld.PackedID where lh.CaseID = '$id' ");
   } 

   public function ElementGigi()
   {
    return $this->db->query("SELECT * FROM erm_ElemenGigi where Dactive = 1");
   }

   public function obatparmasi($idracik)
   {
    return $this->db->query("select rh.CaseID,rh.PatientCode,rh.Status,rd.ResepId,rd.PharmacyCode,p.Description,rd.iQty,rd.qtyUnit,rd.frekuensi,rd.CaraPakai,rd.Ket 
    from Resep_Header rh 
    join Resep_Detail rd on rh.ResepId = rd.ResepId 
    join Pharmacy p on p.PharmacyCode = rd.PharmacyCode where rh.ResepId = '$idracik' ");
   }
   
   public function obatmodal()
   {
    return $this->db->query("SELECT PharmacyCode, [Description], [PharmacyUnit] FROM Pharmacy WHERE Active = '1' ORDER BY [Description] ASC");
   }

   public function tampilracik($idracik)
   {
    return $this->db->query("Select d.PharmacyCode,h.ResepId,h.[Case ID],h.QtyRacikan,h.Frekuensi,p.Description,p.PharmacyUnit as Satuan,D.iQty as Jumlah  FROM Resep_Racikan_H H 
    join Resep_Racikan_D d on d.ResepID = H.ResepId
    join Pharmacy p on p.PharmacyCode = d.PharmacyCode
    where h.ResepId = '$idracik' ");
   }

   public function tampilracik2($id)
   {
    extract($_GET);
    return $this->db->query("Select d.PharmacyCode,p.PharmacyUnit,h.ResepId,h.[Case ID],h.QtyRacikan,h.Frekuensi,p.Description,p.PharmacyUnit as Satuan,D.iQty as Jumlah  FROM Resep_Racikan_H H 
    join Resep_Racikan_D d on d.ResepID = H.ResepId
    join Pharmacy p on p.PharmacyCode = d.PharmacyCode
    where h.ResepId = '$id' ");
   }

   public function headerRacik($idracik)
   {
    return $this->db->query("Select h.ResepId,h.NamaRacikan,h.[Case ID],h.QtyRacikan,h.Frekuensi,h.Jenis,h.Cara,h.IdRacikan FROM Resep_Racikan_H H where h.ResepId= '$idracik' ");
   }

   public function headernonracik($id)
   {
    return $this->db->query("SELECT rh.[ResepId],rh.[CaseID],c.Description,rh.Jenis,c.[Intake Date],c.Category,d.FirstName
    FROM Resep_Header rh
    join Cases c on  c.[Case ID] = rh.CaseID 
    join Doctor d on d.DoctorID = rh.DoctorId
    where CaseID = '$id' And rh.Jenis = 'NON-RACIKAN' ");
   }

   public function trxheaderRacik($id)
   {
    return $this->db->query("SELECT rh.[ResepId],rh.[CaseID],c.Description,rh.Jenis,c.[Intake Date],c.Category,d.FirstName
    FROM Resep_Header rh
    join Cases c on  c.[Case ID] = rh.CaseID 
    join Doctor d on d.DoctorID = rh.DoctorId
    where CaseID = '$id' AND rh.Jenis = 'RACIKAN' ");
   }

   public function historicppt($id)
   {
    return $this->db->query("select case_id,ResumMedis,StatusResume,WaktuResume,TglResume from erm_pemeriksaan_fisik where case_id = '$id'");
   }

   public function Obatnonracik($id)
   {
    return $this->db->query("select rh.ResepId,rh.CaseID,rh.Category,rh.PatientCode,rh.InTakeDate,rd.PharmacyCode,ph.Description,RH.Jenis from Resep_Header RH
    JOIN Resep_Detail rd on rh.ResepId = rd.ResepId
    join Pharmacy ph on ph.PharmacyCode = rd.PharmacyCode
    where RH.CaseID = '$id'
    order by rd.ResepId DESC");
   }

   public function Obatracikan($id)
   {
    return $this->db->query("Select d.PharmacyCode,h.ResepId,h.[Case ID],h.QtyRacikan,h.Frekuensi,p.Description,p.PharmacyUnit as Satuan,D.iQty as Jumlah  FROM Resep_Racikan_H H 
    join Resep_Racikan_D d on d.ResepID = H.ResepId
    join Pharmacy p on p.PharmacyCode = d.PharmacyCode
    where h.[Case ID] = '$id'");
   }

   public function pasienanamnesa()
   {
    date_default_timezone_set("Asia/jakarta");
        $tgl = date('Y-m-d');

        $doctorId = $this->session->userdata('doctorId');
        if(!empty($doctorId)){
            return $this->db->query("SELECT *, d.FirstName as namaDokter , d.LastName as lasNameDokter FROM Cases c join company cp on cp.CompanyCode = c.CompanyCode 
            join CaseDetail cd on cd.[Case ID] = c.[Case ID] 
            join CaseAdmin ca on ca.[Case ID] = c.[Case ID] 
			join Doctor d on d.DoctorID = ca.DoctorID 
            WHERE  [Intake Date] = '$tgl'  AND CaseDetail = '1'");
        }
        return $this->db->query("SELECT *, d.FirstName as namaDokter , d.LastName as lasNameDokter   FROM Cases c join company cp on cp.CompanyCode = c.CompanyCode 
            join CaseDetail cd on cd.[Case ID] = c.[Case ID] 
            join CaseAdmin ca on ca.[Case ID] = c.[Case ID] 
			join Doctor d on d.DoctorID = ca.DoctorID 
            WHERE  [Intake Date] = '$tgl'  AND CaseDetail = '1'");
   }

}