private function process($year, $month, $departemen_id)
 {
     $reportName = "laporan_lplpo";
     $objPHPExcel = $this->loadPHPExcelLib($reportName);
     $objPHPExcel->setActiveSheetIndex(0);
     $activeSheet = $objPHPExcel->getActiveSheet();
     $namaBulan = array("1" => "Januari", "2" => "Februari", "3" => "Maret", "4" => "April", "5" => "Mei", "6" => "Juni", "7" => "Juli", "8" => "Agustus", "9" => "September", "10" => "Oktober", "11" => "November", "12" => "Desember");
     $puskesmas_id = NULL;
     $jenisLaporan = "";
     $filter = "";
     if (substr("" . $departemen_id, 0, 4) == "wil_") {
         $jenisLaporan = "summary";
         $puskesmas = Puskesmas::model()->findByPk(substr("" . $departemen_id, 4));
         $puskesmas_id = $puskesmas->id;
         $departemen = NULL;
         $departemen_id = NULL;
         $activeSheet->getCell("A8")->setValue("Wilayah Kerja Puskesmas " . ucwords(strtolower($puskesmas->nama)));
     } else {
         $jenisLaporan = "per_departemen";
         $departemen = Departemen::model()->findByPk($departemen_id);
         $puskesmas = Puskesmas::model()->findByPk($departemen->puskesmas_id);
         $puskesmas_id = $puskesmas->id;
         $activeSheet->getCell("A8")->setValue($departemen->nama);
         $filter = " AND departemen_id = '" . $departemen->id . "'";
     }
     //echo "JL : ".$jenisLaporan.", Pusk : ".$puskesmas_id.", DPID : ".$departemen_id;
     //judul
     $activeSheet->getCell("A5")->setValue("PEMAKAIAN BULAN " . strtoupper($namaBulan[$month]));
     $no = 1;
     $baris = 12;
     $output = KartuStok::getLPLPOData($month, $year, $jenisLaporan, $puskesmas_id, $departemen_id);
     foreach ($output as $element) {
         $activeSheet->setCellValue("A" . $baris, $no);
         $activeSheet->setCellValue("B" . $baris, $element["nama_obat"]);
         $activeSheet->setCellValue("C" . $baris, $element["kemasan"]);
         $activeSheet->setCellValue("D" . $baris, $element["stok_awal"]);
         $activeSheet->setCellValue("E" . $baris, $element["penerimaan"]);
         $activeSheet->setCellValue("F" . $baris, $element["pemakaian"]);
         $activeSheet->setCellValue("G" . $baris, $element["rusak"]);
         $activeSheet->setCellValue("H" . $baris, $element["saldo"]);
         $no++;
         $baris++;
     }
     $activeSheet->getStyle("A12:H" . ($baris - 1))->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
     $i = 4;
     $reseps = $this->getDataResep($year, $month, $departemen_id, $puskesmas_id);
     foreach ($reseps as $key => $element) {
         $activeSheet->setCellValueByColumnAndRow($i, 5, $key);
         $activeSheet->setCellValueByColumnAndRow($i, 6, $element);
         $activeSheet->getStyleByColumnAndRow($i, 5)->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
         $activeSheet->getStyleByColumnAndRow($i, 6)->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
         $i++;
     }
     return array("phpexcel" => $objPHPExcel, "name" => $reportName . "_" . $namaBulan[$month] . "_" . $year);
 }
 private function process($_tanggal, $_tanggal2, $tujuan_id)
 {
     $reportName = "laporan_obat";
     $objPHPExcel = $this->loadPHPExcelLib($reportName);
     $objPHPExcel->setActiveSheetIndex(0);
     $activeSheet = $objPHPExcel->getActiveSheet();
     $tanggal = $this->reverseDate($_tanggal);
     $tanggal2 = $this->reverseDate($_tanggal2);
     $puskesmas_id = Yii::app()->user->puskesmas_id;
     $puskesmas = Puskesmas::model()->findByPk($puskesmas_id);
     $departemen_id = Departemen::model()->findByAttributes(array("puskesmas_id" => $puskesmas_id, "departemen_jenis_id" => "1"))->id;
     $tujuan = DistribusiObatTujuan::model()->findByPk($tujuan_id);
     $activeSheet->getCell("A8")->setValue(strtoupper($tujuan->nama . " - PUSKESMAS " . $puskesmas->nama));
     $activeSheet->getCell("A5")->setValue("PEMAKAIAN TANGGAL " . $_tanggal . " S/D " . $_tanggal2);
     //echo "$tujuan_id, $departemen_id, $puskesmas_id";
     $no = 1;
     $baris = 12;
     $obatArr = Obat::model()->findAll();
     foreach ($obatArr as $obat) {
         $data = KartuStok::kalkulasiStokObat($obat->id, $tujuan_id, $departemen_id, $puskesmas_id, FALSE);
         $masuk = 0;
         $keluar = 0;
         foreach ($data as $d) {
             //echo "Perbandingan : ".strtotime($tanggal)." > ".strtotime($d["date"])." > ".strtotime($tanggal2);
             //echo "<br>";
             if (strtotime($tanggal) <= strtotime($d["date"]) && strtotime($d["date"]) < strtotime($tanggal2) + 86400) {
                 $masuk += $d["masuk"];
                 $keluar += $d["keluar"];
             } else {
                 continue;
             }
         }
         $activeSheet->setCellValue("A" . $baris, $no);
         $activeSheet->setCellValue("B" . $baris, $obat->nama);
         $activeSheet->setCellValue("C" . $baris, ObatSatuan::model()->findByPk($obat->kemasan_id)->nama);
         $activeSheet->setCellValue("D" . $baris, $masuk);
         $activeSheet->setCellValue("E" . $baris, $keluar);
         $no++;
         $baris++;
     }
     $activeSheet->getStyle("A12:E" . ($baris - 1))->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
     return array("phpexcel" => $objPHPExcel, "name" => $reportName . "_" . $_tanggal . "_" . $_tanggal2);
 }
 private function processKTP($tanggal, $tanggal2, $departemen_id)
 {
     $reportName = "laporan_pembayaran_ktp";
     $objPHPExcel = $this->loadPHPExcelLib($reportName);
     $objPHPExcel->setActiveSheetIndex(0);
     $activeSheet = $objPHPExcel->getActiveSheet();
     $filter = "";
     if (substr("" . $departemen_id, 0, 4) == "wil_") {
         $puskesmas = Puskesmas::model()->findByPk(substr("" . $departemen_id, 4));
         $departemen = NULL;
         $activeSheet->setCellValue("A2", "Wilayah Puskesmas " . ucwords(strtolower($puskesmas->nama)));
     } else {
         $departemen = Departemen::model()->findByPk($departemen_id);
         $puskesmas = Puskesmas::model()->findByPk($departemen->puskesmas_id);
         $activeSheet->setCellValue("A2", $departemen->nama);
         $filter = " AND departemen_id = '" . $departemen->id . "'";
     }
     //judul
     $activeSheet->setCellValue("A4", "Tanggal " . $tanggal . " s/d " . $tanggal2);
     $tanggal = $this->reverseDate($tanggal);
     $tanggal2 = $this->reverseDate($tanggal2);
     $baris = 10;
     $trs = TransaksiKunjungan::model()->findAll("waktu between '{$tanggal} 00:00:00' AND '{$tanggal2} 23:59:00' AND puskesmas_id = '" . $puskesmas->id . "'" . $filter);
     foreach ($trs as $t) {
         if ($t->jenis_pembayaran_id == 1) {
             //UMUM
             $pasien = Pasien::model()->findByPk($t->pasien_id);
             if ($pasien->dalam_wilayah == 1) {
                 $wilayah[0] += 1;
             } else {
                 if ($pasien->dalam_wilayah == 2) {
                     $wilayah[1] += 1;
                 } else {
                     $wilayah[2] += 1;
                 }
             }
             $umur = date_diff(date_create($pasien->tanggal_lahir), date_create(date("Y-m-d", strtotime($t->waktu))))->y;
             if ($umur > 200) {
                 $umur = 0;
             }
             $activeSheet->setCellValue("A" . $baris, $baris - 9);
             $activeSheet->setCellValueExplicit("B" . $baris, $pasien->kode, PHPExcel_Cell_DataType::TYPE_STRING);
             $activeSheet->setCellValue("C" . $baris, $pasien->nama);
             $activeSheet->setCellValue("D" . $baris, $umur);
             $activeSheet->setCellValue("E" . $baris, $t->jenis_kunjungan == "B" ? "1" : "0");
             $activeSheet->setCellValue("F" . $baris, $t->jenis_kunjungan == "L" ? "1" : "0");
             $activeSheet->setCellValue("G" . $baris, $pasien->jenis_kelamin == "L" ? "1" : "0");
             $activeSheet->setCellValue("H" . $baris, $pasien->jenis_kelamin == "P" ? "1" : "0");
             $activeSheet->setCellValue("I" . $baris, $pasien->dalam_wilayah == "1" ? "1" : "0");
             $activeSheet->setCellValue("J" . $baris, $pasien->dalam_wilayah == "2" ? "1" : "0");
             $activeSheet->setCellValue("K" . $baris, $pasien->dalam_wilayah == "3" ? "1" : "0");
             $activeSheet->setCellValue("L" . $baris, date("d-m-Y", strtotime($t->waktu)));
             $activeSheet->setCellValue("M" . $baris, $pasien->alamat);
             $activeSheet->setCellValueExplicit("N" . $baris, $pasien->no_ktp . "", PHPExcel_Cell_DataType::TYPE_STRING);
             $activeSheet->setCellValue("O" . $baris, "10000");
             $antrian = TransaksiAntrian::model()->findByAttributes(array("kunjungan_id" => $t->id));
             //BP
             if ($antrian->poli_tujuan == 1) {
                 $activeSheet->setCellValue("P" . $baris, $t->jenis_kunjungan == "B" ? "1" : "0");
                 $activeSheet->setCellValue("Q" . $baris, $t->jenis_kunjungan == "L" ? "1" : "0");
             }
             if ($antrian->poli_tujuan == 3) {
                 $activeSheet->setCellValue("R" . $baris, $t->jenis_kunjungan == "B" ? "1" : "0");
                 $activeSheet->setCellValue("S" . $baris, $t->jenis_kunjungan == "L" ? "1" : "0");
             }
             if ($antrian->poli_tujuan == 4) {
                 $activeSheet->setCellValue("T" . $baris, $t->jenis_kunjungan == "B" ? "1" : "0");
                 $activeSheet->setCellValue("U" . $baris, $t->jenis_kunjungan == "L" ? "1" : "0");
             }
             $baris++;
         }
     }
     $activeSheet->getStyle("A10:S" . $baris)->applyFromArray($this->getNormalStyle());
     $activeSheet->setCellValue("E" . $baris, "=SUM(E10:E" . ($baris - 1) . ")");
     /*$activeSheet->setCellValue("F".$baris, "=SUM(F10:F".($baris-1).")");
       $activeSheet->setCellValue("G".$baris, "=SUM(G10:G".($baris-1).")");
       $activeSheet->setCellValue("H".$baris, "=SUM(H10:H".($baris-1).")");
       $activeSheet->setCellValue("I".$baris, "=SUM(I10:I".($baris-1).")");
       
       $activeSheet->setCellValue("N".$baris, "=SUM(N10:N".($baris-1).")");
       $activeSheet->setCellValue("O".$baris, "=SUM(O10:O".($baris-1).")");
       $activeSheet->setCellValue("P".$baris, "=SUM(P10:P".($baris-1).")");
       $activeSheet->setCellValue("Q".$baris, "=SUM(Q10:Q".($baris-1).")");
       $activeSheet->setCellValue("R".$baris, "=SUM(R10:R".($baris-1).")");
       $activeSheet->setCellValue("S".$baris, "=SUM(S10:S".($baris-1).")");*/
     return array("phpexcel" => $objPHPExcel, "name" => $reportName . "_" . $tanggal . "_" . $tanggal2);
 }
 private function process($tanggal, $tanggal2, $departemen_id, $jenis_kunjungan, $jenis_pembayaran_id, $jenis_kelamin, $jenis_laporan, $klinik, $wilayah)
 {
     $reportName = "laporan_kunjungan_loket_harian";
     $tanggal = $this->reverseDate($tanggal);
     $tanggal2 = $this->reverseDate($tanggal2);
     $objPHPExcel = $this->loadPHPExcelLib($reportName);
     $rekapArray = array();
     foreach (JenisPembayaran::model()->findAll("status = '1'") as $jp) {
         $rekapArray[$jp->nama] = array();
         foreach (Poli::model()->findAllByAttributes(array("jenis" => 1, "status" => 1)) as $poli) {
             $rekapArray[$jp->nama][$poli->nama] = array("L" => 0, "P" => 0);
         }
     }
     $objPHPExcel->setActiveSheetIndex(0);
     $activeSheet = $objPHPExcel->getActiveSheet();
     //judul
     $activeSheet->getCell("B4")->setValue("Tanggal : " . $tanggal);
     $filter = "";
     if (substr("" . $departemen_id, 0, 4) == "wil_") {
         $puskesmas = Puskesmas::model()->findByPk(substr("" . $departemen_id, 4));
         $departemen = NULL;
         $activeSheet->getCell("B3")->setValue("Wilayah Puskesmas " . ucwords(strtolower($puskesmas->nama)));
     } else {
         $departemen = Departemen::model()->findByPk($departemen_id);
         $puskesmas = Puskesmas::model()->findByPk($departemen->puskesmas_id);
         $activeSheet->getCell("B3")->setValue($departemen->nama);
         $filter = " AND departemen_id = '" . $departemen->id . "'";
     }
     if ($jenis_kunjungan != "") {
         $filter .= " AND jenis_kunjungan = '" . $jenis_kunjungan . "' ";
     }
     if ($jenis_pembayaran_id != "") {
         $filter .= " AND jenis_pembayaran_id = '" . $jenis_pembayaran_id . "' ";
     }
     if ($jenis_laporan != "") {
         $arrayDiagnosaKhusus = array();
         foreach (Lb1JenisLaporanDetail::model()->findAllByAttributes(array("jenis_id" => $jenis_laporan)) as $data) {
             $arrayDiagnosaKhusus[] = $data->penyakit_id;
         }
     }
     //header
     $baris = 8;
     $no = 1;
     $transaksiArray = TransaksiKunjungan::model()->findAll("waktu between '{$tanggal} 00:00:00' AND '{$tanggal2} 23:59:00' AND puskesmas_id = '" . $puskesmas->id . "'" . $filter);
     foreach ($transaksiArray as $kunjungan) {
         $pasien = Pasien::model()->findByPk($kunjungan->pasien_id);
         if ($jenis_kelamin != "" && $pasien->jenis_kelamin != $jenis_kelamin) {
             continue;
         }
         if ($wilayah != "" && $pasien->dalam_wilayah != $wilayah) {
             continue;
         }
         $ada = FALSE;
         $kosong = TRUE;
         $jenis_pembayaran = JenisPembayaran::model()->findByPk($kunjungan->jenis_pembayaran_id);
         $poli = Poli::model()->findByPk($kunjungan->poli_tujuan);
         $diagnosaArray = array();
         $idDiagnosaArray = array();
         $meds = TransaksiMedicalRecord::model()->findAllByAttributes(array("kunjungan_id" => $kunjungan->id));
         foreach ($meds as $med) {
             $diags = TransaksiDiagnosa::model()->findAllByAttributes(array("medical_record_id" => $med->id));
             foreach ($diags as $diag) {
                 $kosong = FALSE;
                 $ada = TRUE;
                 /*
                                     if($jenis_laporan != "" && !in_array($diag->penyakit_id, $arrayDiagnosaKhusus)){
                                         
                                     }else{
                                         $ada = TRUE;
                                     }*/
                 $penyakit = Penyakit::model()->findByAttributes(array("id" => $diag->penyakit_id));
                 $diagnosaArray[] = "[" . $penyakit->kode . "/" . $diag->jenis_kasus . "] " . ($penyakit->nama_indonesia == "" ? $penyakit->nama : $penyakit->nama_indonesia);
             }
         }
         $ada2 = FALSE;
         $kosong2 = TRUE;
         $bpArray = array();
         $ants = TransaksiAntrian::model()->findAllByAttributes(array("kunjungan_id" => $kunjungan->id));
         foreach ($ants as $ant) {
             $kosong2 = FALSE;
             if ($klinik != "" && $ant->poli_tujuan != $klinik) {
             } else {
                 $ada2 = TRUE;
             }
             $polie = Poli::model()->findByPk($ant->poli_tujuan);
             $rekapArray[$jenis_pembayaran->nama][$polie->nama][$pasien->jenis_kelamin] += 1;
             $bpArray[] = $polie->nama;
         }
         if ($kosong || $kosong2 || $ada && $ada2) {
         } else {
             continue;
         }
         $activeSheet->getCellByColumnAndRow(1, $baris)->setValue($no . ".");
         $activeSheet->getCellByColumnAndRow(2, $baris)->setValue(date("d-m-Y", strtotime($kunjungan->waktu)));
         $activeSheet->getCellByColumnAndRow(3, $baris)->setValueExplicit($pasien->kode, PHPExcel_Cell_DataType::TYPE_STRING);
         $activeSheet->getCellByColumnAndRow(4, $baris)->setValue($pasien->nama);
         $age = date_diff(date_create($pasien->tanggal_lahir), date_create($kunjungan->waktu))->y;
         if ($age > 200) {
             $age = 0;
         }
         $activeSheet->getCellByColumnAndRow(5, $baris)->setValue($age);
         $activeSheet->getCellByColumnAndRow(6, $baris)->setValue($pasien->jenis_kelamin);
         $activeSheet->getCellByColumnAndRow(7, $baris)->setValue($pasien->alamat);
         $activeSheet->getCellByColumnAndRow(8, $baris)->setValue($pasien->nama_kepala_keluarga);
         $activeSheet->getCellByColumnAndRow(9, $baris)->setValueExplicit($pasien->no_ktp, PHPExcel_Cell_DataType::TYPE_STRING);
         $activeSheet->getCellByColumnAndRow(10, $baris)->setValue($jenis_pembayaran->nama);
         $activeSheet->getCellByColumnAndRow(11, $baris)->setValueExplicit($kunjungan->kode_asuransi, PHPExcel_Cell_DataType::TYPE_STRING);
         $activeSheet->getCellByColumnAndRow(12, $baris)->setValue($kunjungan->jenis_kunjungan == "B" ? "Baru" : "Lama");
         $activeSheet->getCellByColumnAndRow(13, $baris)->setValue(implode(", ", $diagnosaArray));
         //diagnosa
         $activeSheet->getCellByColumnAndRow(14, $baris)->setValue(implode(", ", $bpArray));
         //bp
         $tujuan = $poli->nama . " : " . ucwords(strtolower(Departemen::model()->findByPk($kunjungan->departemen_id)->nama));
         $activeSheet->getCellByColumnAndRow(15, $baris)->setValue($tujuan);
         $baris++;
         $no++;
     }
     $activeSheet->getStyle("B8:P" . ($baris - 1))->applyFromArray(array('borders' => array('allborders' => array('style' => PHPExcel_Style_Border::BORDER_THIN))));
     //Pindah ke sheet 2
     $objPHPExcel->setActiveSheetIndex(1);
     $activeSheet = $objPHPExcel->getActiveSheet();
     $smbaris = 4;
     foreach ($rekapArray as $key => $rekap) {
         $num = 2;
         $activeSheet->getCellByColumnAndRow(1, $smbaris)->setValue($key);
         foreach (Poli::model()->findAllByAttributes(array("jenis" => 1, "status" => 1)) as $poli) {
             if ($smbaris == 4) {
                 //set judul
                 $activeSheet->getCellByColumnAndRow($num, 2)->setValue($poli->nama);
                 $activeSheet->getCellByColumnAndRow($num, 3)->setValue("Lk");
                 $activeSheet->getCellByColumnAndRow($num + 1, 3)->setValue("Pr");
                 $activeSheet->getCellByColumnAndRow($num + 2, 3)->setValue("Total");
                 $activeSheet->mergeCellsByColumnAndRow($num, 2, $num + 2, 2);
             }
             $activeSheet->getCellByColumnAndRow($num, $smbaris)->setValue($rekap[$poli->nama]["L"]);
             $activeSheet->getCellByColumnAndRow($num + 1, $smbaris)->setValue($rekap[$poli->nama]["P"]);
             $activeSheet->getCellByColumnAndRow($num + 2, $smbaris)->setValue($rekap[$poli->nama]["L"] + $rekap[$poli->nama]["P"]);
             $num += 3;
         }
         if ($smbaris == 4) {
             $activeSheet->getStyle("B2:" . $this->get_col_letter($num - 1) . "3")->applyFromArray(array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '8B0000')), 'font' => array('color' => array('rgb' => 'FFFFFF'))));
         }
         $smbaris++;
     }
     $this->fillBorder($activeSheet, "B2:" . $this->get_col_letter($num - 1) . ($smbaris - 1));
     $activeSheet->getStyle("B4:" . $this->get_col_letter($num - 1) . ($smbaris - 1))->applyFromArray(array('font' => array('color' => array('rgb' => '000000'), 'size' => 13, 'name' => 'Calibri')));
     $activeSheet->getStyle("C4:" . $this->get_col_letter($num - 1) . ($smbaris - 1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
     return array("phpexcel" => $objPHPExcel, "name" => $reportName . "_" . $tanggal);
 }
 private function process($tanggal, $tanggal2, $departemen_id)
 {
     $reportName = "laporan_tindakan";
     $tahun = date("Y", strtotime($this->reverseDate($tanggal)));
     $objPHPExcel = $this->loadPHPExcelLib($reportName);
     $objPHPExcel->setActiveSheetIndex(0);
     $activeSheet = $objPHPExcel->getActiveSheet();
     $filter = "";
     if (substr("" . $departemen_id, 0, 4) == "wil_") {
         $puskesmas = Puskesmas::model()->findByPk(substr("" . $departemen_id, 4));
         $departemen = NULL;
         $activeSheet->setCellValue("A4", "WILAYAH " . $puskesmas->nama);
     } else {
         $departemen = Departemen::model()->findByPk($departemen_id);
         $puskesmas = Puskesmas::model()->findByPk($departemen->puskesmas_id);
         $activeSheet->setCellValue("A4", $departemen->nama);
         $filter = " AND departemen_id = '" . $departemen->id . "'";
     }
     //judul
     $activeSheet->setCellValue("A1", "REKAPITULASI RETRIBUSI PUSKESMAS TAHUN " . $tahun);
     $activeSheet->setCellValue("A3", "PUSKESMAS " . $puskesmas->nama);
     $activeSheet->setCellValue("A4", "Tanggal " . $tanggal . " s/d " . $tanggal2);
     $tanggal = $this->reverseDate($tanggal);
     $tanggal2 = $this->reverseDate($tanggal2);
     $query = "puskesmas_id = '" . $puskesmas->id . "' AND waktu between '{$tanggal} 00:00:00' AND '{$tanggal2} 23:59:00' ";
     for ($baris = 7; $baris <= 42; $baris++) {
         $i = $activeSheet->getCell("I" . $baris)->getValue();
         $j = $activeSheet->getCell("J" . $baris)->getValue();
         $k = $activeSheet->getCell("K" . $baris)->getValue();
         $l = $activeSheet->getCell("L" . $baris)->getValue();
         $tes = "0";
         $data = "";
         if ($i != "") {
             $jml = TransaksiTindakan::model()->count("tindakan_id = '" . $i . "' AND " . $query . $filter);
             $tes = "1";
         } else {
             if ($j != "") {
                 //echo "select transaksi_tindakan.* from transaksi_tindakan, tindakan WHERE transaksi_tindakan.tindakan_id = tindakan.id AND tindakan.kategori_id = '".$j."' AND transaksi_tindakan.".$query.$filter."<br>";
                 $jml = TransaksiTindakan::model()->countBySql("select count(transaksi_tindakan.id) from transaksi_tindakan, tindakan WHERE transaksi_tindakan.tindakan_id = tindakan.id AND tindakan.kategori_id = '" . $j . "' AND transaksi_tindakan." . $query . $filter);
                 $tes = "2";
             } else {
                 if ($k != "") {
                     $jml = TransaksiLaborat::model()->count("laborat_id = '" . $k . "' AND " . $query . $filter);
                     $tes = "3";
                 } else {
                     if ($l != "") {
                         $jml = TransaksiLaborat::model()->countBySql("select count(transaksi_laborat.id) from transaksi_laborat, laborat WHERE transaksi_laborat.laborat_id = laborat.id AND laborat.kategori_id = '" . $l . "' AND transaksi_laborat." . $query . $filter);
                         $tes = "4";
                     }
                 }
             }
         }
         //$activeSheet->setCellValue("H".$baris, $tes);
         $activeSheet->setCellValue("E" . $baris, $jml * 1);
     }
     $activeSheet->removeColumn("L");
     $activeSheet->removeColumn("K");
     $activeSheet->removeColumn("J");
     $activeSheet->removeColumn("I");
     $askes = TransaksiKunjungan::model()->count("jenis_pembayaran_id = '5' AND " . $query . $filter);
     $gratis = TransaksiKunjungan::model()->count("jenis_pembayaran_id = '11' AND " . $query . $filter);
     $activeSheet->setCellValue("E43", $askes);
     $activeSheet->setCellValue("E44", $gratis);
     $activeSheet->setCellValue("F43", $askes);
     $activeSheet->setCellValue("F44", $gratis);
     return array("phpexcel" => $objPHPExcel, "name" => $reportName . "_" . $tanggal . "_" . $tanggal2);
 }
 private function process($month, $year, $departemen_id, $kategori_penyakit)
 {
     $reportName = "laporan_lb1";
     $objPHPExcel = $this->loadPHPExcelLib($reportName);
     $objPHPExcel->setActiveSheetIndex(0);
     $activeSheet = $objPHPExcel->getActiveSheet();
     $namaBulan = array("1" => "Januari", "2" => "Februari", "3" => "Maret", "4" => "April", "5" => "Mei", "6" => "Juni", "7" => "Juli", "8" => "Agustus", "9" => "September", "10" => "Oktober", "11" => "November", "12" => "Desember");
     //judul
     $activeSheet->getCell("B3")->setValue(": " . Setting::getNamaPuskesmas());
     $activeSheet->getCell("B4")->setValue(": " . Setting::getKodePuskesmas());
     $activeSheet->getCell("B5")->setValue(": " . CHtml::value(WilayahKabupaten::model()->findByPk(Setting::getKabupaten()), "nama"));
     $activeSheet->getCell("AW4")->setValue($namaBulan[$month]);
     $activeSheet->getCell("AW5")->setValue($year);
     $filter = "";
     if (substr("" . $departemen_id, 0, 4) == "wil_") {
         $puskesmas = Puskesmas::model()->findByPk(substr("" . $departemen_id, 4));
         $departemen = NULL;
     } else {
         $departemen = Departemen::model()->findByPk($departemen_id);
         $puskesmas = Puskesmas::model()->findByPk($departemen->puskesmas_id);
         $filter = " AND departemen_id = '" . $departemen->id . "'";
     }
     $kodePusk = Setting::getKodePuskesmas();
     //konten
     $globaldata = array();
     $baris = 11;
     $trs = array();
     if ($kategori_penyakit == "") {
         //$trs = Penyakit::model()->findAll("lb1_status = '1'");
         foreach (Penyakit::model()->findAll("lb1_status = '1'") as $p) {
             $trs[] = $p->id;
         }
     } else {
         foreach (Lb1JenisLaporanDetail::model()->findAllByAttributes(array("jenis_id" => $kategori_penyakit)) as $detail) {
             //$penyakit = Penyakit::model()->findByAttributes(array("id" => $detail->penyakit_id));
             $trs[] = $detail->penyakit_id;
         }
     }
     $jumlahData = 0;
     foreach ($trs as $p) {
         $activeSheet->getCellByColumnAndRow(0, $baris)->setValue($kodePusk);
         $activeSheet->getCellByColumnAndRow(1, $baris)->setValue($namaBulan[$month]);
         $activeSheet->getCellByColumnAndRow(2, $baris)->setValue($year);
         $activeSheet->getCellByColumnAndRow(3, $baris)->setValue($p->kode);
         $activeSheet->getCellByColumnAndRow(4, $baris)->setValue($p->nama_indonesia);
         //$activeSheet->getCell("CE".$baris)->setValue($p->id);
         $output = array();
         for ($i = 0; $i < 18; $i++) {
             $output[$i . "_L_B"] = 0;
             $output[$i . "_L_L"] = 0;
             $output[$i . "_P_B"] = 0;
             $output[$i . "_P_L"] = 0;
         }
         $globaldata[$p] = $output;
         $baris++;
         $jumlahData++;
     }
     $diags = TransaksiDiagnosa::model()->findAll("puskesmas_id = '" . $puskesmas->id . "' AND year(waktu) = '{$year}' AND month(waktu) = '{$month}'" . $filter);
     foreach ($diags as $diag) {
         $med = TransaksiMedicalRecord::model()->findByPk($diag->medical_record_id);
         $pasien = Pasien::model()->findByPk($med->pasien_id);
         $jenis_kelamin = $pasien->jenis_kelamin;
         $kategoriUmur = $this->getKategoriUmur($pasien);
         if (in_array($diag->penyakit_id, $trs)) {
             $globaldata[$diag->penyakit_id][$kategoriUmur . "_" . $jenis_kelamin . "_" . $diag->jenis_kasus] += 1;
         }
     }
     $jumlahIsi = 0;
     $baris = 11;
     foreach ($globaldata as $penyakit_id => $output) {
         /*
                       $kunjungans = TransaksiKunjungan::model()->findAll("puskesmas_id = '{$puskemas_id}' AND year(waktu) = '{$year}' AND month(waktu) = '{$month}'");
                       foreach($kunjungans as $kunjungan){
                       $pasien = Pasien::model()->findByPk($kunjungan->pasien_id);
                       $jenis_kelamin = $pasien->jenis_kelamin;
         
                       //get kategori umur
                       $kategoriUmur = $this->getKategoriUmur($pasien);
         
                       $medRecArray = array();
                       $antrians = TransaksiAntrian::model()->findAll("kunjungan_id = '{$kunjungan->id}'");
                       foreach($antrians as $antrian){
                       $medRecArray[$antrian->medical_record_id] = 1;
                       }
         
                       foreach($medRecArray as $key => $val){
                       $diags = TransaksiDiagnosa::model()->findAll("puskesmas_id = '{$puskemas_id}' AND medical_record_id = '{$key}' AND penyakit_id = '{$p->id}'");
                       foreach($diags as $diag){
                       $output[$kategoriUmur."_".$jenis_kelamin."_".$diag->jenis_kasus] += 1;
                       }
                       }
                       } */
         $jml = array();
         $jml["L_B"] = 0;
         $jml["L_L"] = 0;
         $jml["P_B"] = 0;
         $jml["P_L"] = 0;
         for ($kat = 0; $kat <= 17; $kat++) {
             $kolom = 10 + $kat * 4;
             $activeSheet->getCellByColumnAndRow($kolom, $baris)->setValue($output[$kat . "_L_B"]);
             $activeSheet->getCellByColumnAndRow($kolom + 1, $baris)->setValue($output[$kat . "_L_L"]);
             $activeSheet->getCellByColumnAndRow($kolom + 2, $baris)->setValue($output[$kat . "_P_B"]);
             $activeSheet->getCellByColumnAndRow($kolom + 3, $baris)->setValue($output[$kat . "_P_L"]);
             $jml["L_B"] += $output[$kat . "_L_B"];
             $jml["L_L"] += $output[$kat . "_L_L"];
             $jml["P_B"] += $output[$kat . "_P_B"];
             $jml["P_L"] += $output[$kat . "_P_L"];
         }
         $p = Penyakit::model()->findByAttributes(array("id" => $penyakit_id));
         $activeSheet->getCellByColumnAndRow(0, $baris)->setValue($kodePusk);
         $activeSheet->getCellByColumnAndRow(1, $baris)->setValue($namaBulan[$month]);
         $activeSheet->getCellByColumnAndRow(2, $baris)->setValue($year);
         $activeSheet->getCellByColumnAndRow(3, $baris)->setValue($p->kode);
         $nama = $p->nama_indonesia;
         if ($nama == "") {
             $nama = $p->nama;
         }
         $activeSheet->getCellByColumnAndRow(4, $baris)->setValue($nama);
         $activeSheet->getCellByColumnAndRow(5, $baris)->setValue("=SUM(G" . $baris . ":J" . $baris . ")");
         $activeSheet->getCellByColumnAndRow(6, $baris)->setValue($jml["L_B"]);
         $activeSheet->getCellByColumnAndRow(7, $baris)->setValue($jml["L_L"]);
         $activeSheet->getCellByColumnAndRow(8, $baris)->setValue($jml["P_B"]);
         $activeSheet->getCellByColumnAndRow(9, $baris)->setValue($jml["P_L"]);
         /*
                     if($jumlahIsi>$jumlahData){
                         $p = Penyakit::model()->findByAttributes(array("id"=>$penyakit_id));
                         $activeSheet->getCellByColumnAndRow(0, $baris)->setValue($kodePusk);
                         $activeSheet->getCellByColumnAndRow(1, $baris)->setValue($namaBulan[$month]);
                         $activeSheet->getCellByColumnAndRow(2, $baris)->setValue($year);
                         $activeSheet->getCellByColumnAndRow(3, $baris)->setValue($p->kode);
                         $nama = $p->nama_indonesia;
                         if($nama == ""){
                             $nama = $p->nama;
                         }
                         $activeSheet->getCellByColumnAndRow(4, $baris)->setValue($nama);
                         $this->fillBorder($activeSheet, "A".$baris.":CD".$baris);
                     }*/
         //$activeSheet->getCell("CF".$baris)->setValue($penyakit_id);
         $baris++;
         $jumlahIsi++;
     }
     $this->fillBorder($activeSheet, "A11:CD" . ($baris - 1));
     return array("phpexcel" => $objPHPExcel, "name" => $reportName . "_" . $namaBulan[$month] . "_" . $year);
 }
 private function process2($year, $departemen_id)
 {
     $reportName = "laporan_kunjungan_loket_lengkap";
     $objPHPExcel = $this->loadPHPExcelLib($reportName);
     $objPHPExcel->setActiveSheetIndex(0);
     $activeSheet = $objPHPExcel->getActiveSheet();
     $activeSheet->setCellValue("A1", "Tahun " . $year);
     $filter = "";
     if (substr("" . $departemen_id, 0, 4) == "wil_") {
         $puskesmas = Puskesmas::model()->findByPk(substr("" . $departemen_id, 4));
         $departemen = NULL;
         $activeSheet->getCell("C3")->setValue($puskesmas->nama);
     } else {
         $departemen = Departemen::model()->findByPk($departemen_id);
         $puskesmas = Puskesmas::model()->findByPk($departemen->puskesmas_id);
         $activeSheet->getCell("C3")->setValue($departemen->nama);
         $filter = "AND departemen_id = '" . $departemen_id . "' ";
     }
     $baris = 11;
     for ($bulan = 1; $bulan <= 12; $bulan++) {
         $baseQuery = "year(waktu) = '{$year}' AND month(waktu) = '{$bulan}' AND puskesmas_id = '" . $puskesmas->id . "'" . $filter;
         //kunjungan baru
         $kunjunganBaru = TransaksiKunjungan::model()->count($baseQuery . " AND jenis_kunjungan = 'B'");
         $activeSheet->setCellValue("C" . $baris, $kunjunganBaru);
         //kunjungan lama
         $kunjunganLama = TransaksiKunjungan::model()->count($baseQuery . " AND jenis_kunjungan = 'L'");
         $activeSheet->setCellValue("D" . $baris, $kunjunganLama);
         //BP
         $criteria = new CDbCriteria();
         $criteria->condition = $baseQuery . " AND poli_tujuan IN(1, 24)";
         $criteria->order = "id";
         $criteria->group = "kunjungan_id";
         $bp = TransaksiAntrian::model()->count($criteria);
         $activeSheet->setCellValue("F" . $baris, $bp);
         $criteria = new CDbCriteria();
         $criteria->condition = $baseQuery . " AND poli_tujuan = '3'";
         $criteria->order = "id";
         $criteria->group = "kunjungan_id";
         $bpg = TransaksiAntrian::model()->count($criteria);
         $activeSheet->setCellValue("G" . $baris, $bpg);
         $criteria = new CDbCriteria();
         $criteria->condition = $baseQuery . " AND poli_tujuan IN(4,5,6)";
         $criteria->order = "id";
         $criteria->group = "kunjungan_id";
         $kia = TransaksiAntrian::model()->count($criteria);
         $activeSheet->setCellValue("H" . $baris, $kia);
         $criteria = new CDbCriteria();
         $criteria->condition = $baseQuery . " AND poli_tujuan = '17'";
         $criteria->order = "id";
         $criteria->group = "kunjungan_id";
         $p2 = TransaksiAntrian::model()->count($criteria);
         $activeSheet->setCellValue("I" . $baris, $p2);
         $criteria = new CDbCriteria();
         $criteria->condition = $baseQuery . " AND poli_tujuan IN(2,12,13)";
         $criteria->order = "id";
         $criteria->group = "kunjungan_id";
         $ugd = TransaksiAntrian::model()->count($criteria);
         $activeSheet->setCellValue("J" . $baris, $ugd);
         //dalam luar wilayah
         $wilayah = array(0, 0, 0);
         //jenis pembayaran
         $jp = array(0, 0, 0, 0, 0, 0);
         $trs = TransaksiKunjungan::model()->findAll($baseQuery);
         foreach ($trs as $t) {
             if ($t->jenis_pembayaran_id == 1) {
                 //UMUM
                 $pasien = Pasien::model()->findByPk($t->pasien_id);
                 if ($pasien->dalam_wilayah == 1) {
                     $wilayah[0] += 1;
                 } else {
                     if ($pasien->dalam_wilayah == 2) {
                         $wilayah[1] += 1;
                     } else {
                         $wilayah[2] += 1;
                     }
                 }
             } else {
                 if ($t->jenis_pembayaran_id == 3) {
                     //PBI
                     $jp[0] += 1;
                 } else {
                     if ($t->jenis_pembayaran_id == 5) {
                         //NON PBI ASKES / TNI POLRI
                         $jp[1] += 1;
                     } else {
                         if ($t->jenis_pembayaran_id == 4) {
                             //NON MANDIRI
                             $jp[2] += 1;
                         } else {
                             if ($t->jenis_pembayaran_id == 6) {
                                 //JAMKESDA
                                 $jp[3] += 1;
                             } else {
                                 if ($t->jenis_pembayaran_id == 2) {
                                     //JAMKESDA
                                     $jp[4] += 1;
                                 } else {
                                     if (in_array($t->jenis_pembayaran_id, array(7, 8, 9, 10, 11))) {
                                         //GRATIS
                                         $jp[5] += 1;
                                     }
                                 }
                             }
                         }
                     }
                 }
             }
         }
         $activeSheet->setCellValue("L" . $baris, $wilayah[0]);
         $activeSheet->setCellValue("M" . $baris, $wilayah[1]);
         $activeSheet->setCellValue("N" . $baris, $wilayah[2]);
         $activeSheet->setCellValue("O" . $baris, $jp[0]);
         $activeSheet->setCellValue("P" . $baris, $jp[1]);
         $activeSheet->setCellValue("Q" . $baris, $jp[2]);
         $activeSheet->setCellValue("R" . $baris, $jp[3]);
         $activeSheet->setCellValue("S" . $baris, $jp[4]);
         $activeSheet->setCellValue("T" . $baris, $jp[5]);
         //rujuk
         //dalam luar wilayah
         $wilayah = array(0, 0, 0);
         //jenis pembayaran
         $jp = array(0, 0, 0, 0, 0, 0);
         $baseQuery = "year(waktu_periksa) = '{$year}' AND month(waktu_periksa) = '{$bulan}' AND puskesmas_id = '" . $puskesmas->id . "'" . $filter;
         $trans = TransaksiMedicalRecord::model()->findAll($baseQuery . " AND keadaan_akhir_id = '9'");
         foreach ($trans as $meds) {
             $t = TransaksiKunjungan::model()->findByPk($meds->kunjungan_id);
             if ($t->jenis_pembayaran_id == 1) {
                 //UMUM
                 $pasien = Pasien::model()->findByPk($t->pasien_id);
                 if ($pasien->dalam_wilayah == 1) {
                     $wilayah[0] += 1;
                 } else {
                     if ($pasien->dalam_wilayah == 2) {
                         $wilayah[1] += 1;
                     } else {
                         $wilayah[2] += 1;
                     }
                 }
             } else {
                 if ($t->jenis_pembayaran_id == 3) {
                     //PBI
                     $jp[0] += 1;
                 } else {
                     if ($t->jenis_pembayaran_id == 5) {
                         //NON PBI ASKES / TNI POLRI
                         $jp[1] += 1;
                     } else {
                         if ($t->jenis_pembayaran_id == 4) {
                             //NON MANDIRI
                             $jp[2] += 1;
                         } else {
                             if ($t->jenis_pembayaran_id == 6) {
                                 //JAMKESDA
                                 $jp[3] += 1;
                             } else {
                                 if ($t->jenis_pembayaran_id == 2) {
                                     //JAMKESDA
                                     $jp[4] += 1;
                                 } else {
                                     if (in_array($t->jenis_pembayaran_id, array(7, 8, 9, 10, 11))) {
                                         //GRATIS
                                         $jp[5] += 1;
                                     }
                                 }
                             }
                         }
                     }
                 }
             }
         }
         $activeSheet->setCellValue("V" . $baris, $wilayah[0]);
         $activeSheet->setCellValue("W" . $baris, $wilayah[1]);
         $activeSheet->setCellValue("X" . $baris, $wilayah[2]);
         $activeSheet->setCellValue("Y" . $baris, $jp[0]);
         $activeSheet->setCellValue("Z" . $baris, $jp[1]);
         $activeSheet->setCellValue("AA" . $baris, $jp[2]);
         $activeSheet->setCellValue("AB" . $baris, $jp[3]);
         $activeSheet->setCellValue("AC" . $baris, $jp[4]);
         $activeSheet->setCellValue("AD" . $baris, $jp[5]);
         $baris++;
     }
     return array("phpexcel" => $objPHPExcel, "name" => $reportName . "_" . $year);
 }
 private function processLP($tanggal, $tanggal2, $departemen_id)
 {
     $reportName = "laporan_penyakit_terbanyak_lp";
     $objPHPExcel = $this->loadPHPExcelLib($reportName);
     $objPHPExcel->setActiveSheetIndex(0);
     $activeSheet = $objPHPExcel->getActiveSheet();
     $filter = "";
     if (substr("" . $departemen_id, 0, 4) == "wil_") {
         $puskesmas = Puskesmas::model()->findByPk(substr("" . $departemen_id, 4));
         $departemen = NULL;
         $activeSheet->getCell("A2")->setValue("10 PENYAKIT TERBANYAK DI PUSKESMAS " . ucwords(strtolower($puskesmas->nama)));
         $activeSheet->getCell("A3")->setValue("Tanggal " . $tanggal . " s/d " . $tanggal2);
         $activeSheet->getCell("A31")->setValue("Sumber : Laporan Wilayah Puskesmas " . ucwords(strtolower($puskesmas->nama)));
         $activeSheet->getCell("A64")->setValue("Sumber : Laporan Wilayah Puskesmas " . ucwords(strtolower($puskesmas->nama)));
     } else {
         $departemen = Departemen::model()->findByPk($departemen_id);
         $puskesmas = Puskesmas::model()->findByPk($departemen->puskesmas_id);
         $activeSheet->getCell("A2")->setValue("10 PENYAKIT TERBANYAK DI " . ucwords(strtolower($departemen->nama)));
         $activeSheet->getCell("A3")->setValue("Tanggal " . $tanggal . " s/d " . $tanggal2);
         $activeSheet->getCell("A31")->setValue("Sumber : Laporan Wilayah " . ucwords(strtolower($departemen->nama)));
         $activeSheet->getCell("A64")->setValue("Sumber : Laporan Wilayah " . ucwords(strtolower($departemen->nama)));
         $filter = " AND transaksi_diagnosa.departemen_id = '" . $departemen->id . "'";
     }
     $tanggal = $this->reverseDate($tanggal);
     $tanggal2 = $this->reverseDate($tanggal2);
     //mencari penyakit terbanyak laki-laki
     $baris = 8;
     $sql = "SELECT penyakit_id, COUNT(*) as is_dirujuk\n                FROM `transaksi_diagnosa`, `transaksi_medical_record`, `pasien`\n                where\n                    transaksi_diagnosa.medical_record_id = transaksi_medical_record.id AND\n                    transaksi_medical_record.pasien_id = pasien.id AND\n                    pasien.jenis_kelamin = 'L' AND\n                    jenis_kasus = 'B' and waktu between '{$tanggal} 00:00:00' AND '{$tanggal2} 23:59:00' AND transaksi_diagnosa.puskesmas_id = '" . $puskesmas->id . "'" . $filter . "\n                group by penyakit_id\n                order by is_dirujuk DESC\n                limit 0,20";
     $transaksiDiagnosa = TransaksiDiagnosa::model()->findAllBySql($sql);
     foreach ($transaksiDiagnosa as $transaksi) {
         $penyakit = Penyakit::model()->findByAttributes(array("id" => $transaksi->penyakit_id));
         $nama = $penyakit->nama_indonesia;
         if ($nama == NULL) {
             $nama = $penyakit->nama;
         }
         $activeSheet->getCell("B" . $baris)->setValue($penyakit->kode);
         $activeSheet->getCell("C" . $baris)->setValue($nama);
         $activeSheet->getCell("D" . $baris)->setValue($transaksi->is_dirujuk);
         $baris++;
     }
     $jml = TransaksiDiagnosa::model()->countBySql("SELECT COUNT(*)\n                FROM `transaksi_diagnosa`, `transaksi_medical_record`, `pasien`\n                where\n                    transaksi_diagnosa.medical_record_id = transaksi_medical_record.id AND\n                    transaksi_medical_record.pasien_id = pasien.id AND\n                    pasien.jenis_kelamin = 'L' AND\n                    jenis_kasus = 'B' and waktu between '{$tanggal} 00:00:00' AND '{$tanggal2} 23:59:00' AND transaksi_diagnosa.puskesmas_id = '" . $puskesmas->id . "'" . $filter . "\n\n                order by is_dirujuk DESC\n                limit 0,20");
     $activeSheet->getCell("D30")->setValue($jml);
     //mencari penyakit terbanyak perempuan
     $baris = 41;
     $sql = "SELECT penyakit_id, COUNT(*) as is_dirujuk\n                FROM `transaksi_diagnosa`, `transaksi_medical_record`, `pasien`\n                where\n                    transaksi_diagnosa.medical_record_id = transaksi_medical_record.id AND\n                    transaksi_medical_record.pasien_id = pasien.id AND\n                    pasien.jenis_kelamin = 'P' AND\n                    jenis_kasus = 'B' and waktu between '{$tanggal} 00:00:00' AND '{$tanggal2} 23:59:00' AND transaksi_diagnosa.puskesmas_id = '" . $puskesmas->id . "'" . $filter . "\n                group by penyakit_id\n                order by is_dirujuk DESC\n                limit 0,20";
     $transaksiDiagnosa = TransaksiDiagnosa::model()->findAllBySql($sql);
     foreach ($transaksiDiagnosa as $transaksi) {
         $penyakit = Penyakit::model()->findByAttributes(array("id" => $transaksi->penyakit_id));
         $nama = $penyakit->nama_indonesia;
         if ($nama == NULL) {
             $nama = $penyakit->nama;
         }
         $activeSheet->getCell("B" . $baris)->setValue($penyakit->kode);
         $activeSheet->getCell("C" . $baris)->setValue($nama);
         $activeSheet->getCell("D" . $baris)->setValue($transaksi->is_dirujuk);
         $baris++;
     }
     $jml = TransaksiDiagnosa::model()->countBySql("SELECT COUNT(*)\n                FROM `transaksi_diagnosa`, `transaksi_medical_record`, `pasien`\n                where\n                    transaksi_diagnosa.medical_record_id = transaksi_medical_record.id AND\n                    transaksi_medical_record.pasien_id = pasien.id AND\n                    pasien.jenis_kelamin = 'P' AND\n                    jenis_kasus = 'B' and waktu between '{$tanggal} 00:00:00' AND '{$tanggal2} 23:59:00' AND transaksi_diagnosa.puskesmas_id = '" . $puskesmas->id . "'" . $filter . "\n\n                order by is_dirujuk DESC\n                limit 0,20");
     $activeSheet->getCell("D63")->setValue($jml);
     return array("phpexcel" => $objPHPExcel, "name" => $reportName . "_" . $tanggal . "_" . $tanggal2);
 }