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);
}
public static function kalkulasiStokObat($id, $tujuan_id = 0, $departemen_id, $puskesmas_id, $include_obat_rusak = TRUE)
{
$dataTransaksi = array();
//untuk satu wilayah atau satu departemen
if ($tujuan_id == 0) {
//untuk satu wilayah
if ($departemen_id == 0) {
//ambil data stok awal
$list = Yii::app()->db->createCommand("select stok_awal.tanggal, stok_awal_detail.obat_id, stok_awal_detail.jumlah\n from stok_awal_detail, stok_awal\n where \n stok_awal_detail.stok_awal_id = stok_awal.id \n and stok_awal.puskesmas_id = '{$puskesmas_id}'\n and stok_awal_detail.obat_id = '{$id}'")->queryAll();
foreach ($list as $item) {
$obj = array();
$obj["date"] = $item['tanggal'];
$obj["keterangan"] = "Stok Awal";
$obj["masuk"] = $item['jumlah'];
$obj["keluar"] = 0;
$obj["data"] = "STOK_AWAL";
$dataTransaksi[] = $obj;
}
//ambil data penerimaan
$list = Yii::app()->db->createCommand("select penerimaan_obat.tanggal, penerimaan_obat_detail.obat_id, penerimaan_obat_detail.jumlah\n from penerimaan_obat_detail, penerimaan_obat\n where \n penerimaan_obat_detail.penerimaan_obat_id = penerimaan_obat.id \n and penerimaan_obat.puskesmas_id = '{$puskesmas_id}'\n and penerimaan_obat_detail.obat_id = '{$id}'")->queryAll();
foreach ($list as $item) {
$obj = array();
$obj["date"] = $item['tanggal'];
$obj["keterangan"] = "Penerimaan dari GFLK";
$obj["masuk"] = $item['jumlah'];
$obj["keluar"] = 0;
$obj["data"] = "PENERIMAAN";
$dataTransaksi[] = $obj;
}
//ambil data pemakaian (obat keluar)
$filter = array("puskesmas_id" => $puskesmas_id, "obat_id" => $id);
$pemakaianArr = PemakaianObat::model()->findAllByAttributes($filter);
foreach ($pemakaianArr as $pakai) {
$tujuan = DistribusiObatTujuan::model()->findByPk($pakai->distribusi_obat_tujuan_id);
$departemen = Departemen::model()->findByPk($pakai->departemen_id);
$obj = array();
$obj["date"] = $pakai->tanggal;
$obj["keterangan"] = "Pemakaian " . ucwords(strtolower($tujuan->nama)) . " " . ucwords(strtolower($departemen->nama));
if ($pakai->keterangan != "") {
$obj["keterangan"] = $pakai->keterangan;
}
$obj["masuk"] = 0;
$obj["keluar"] = $pakai->jumlah;
$obj["data"] = "PEMAKAIAN";
$dataTransaksi[] = $obj;
}
} else {
//ambil data transfer dari gudang obat
$list = Yii::app()->db->createCommand("select distribusi_obat.tanggal, distribusi_obat.asal_id, distribusi_obat.asal_departemen_id, distribusi_obat.tujuan_id, distribusi_obat.departemen_id, distribusi_obat_detail.obat_id, distribusi_obat_detail.jumlah\n from distribusi_obat_detail, distribusi_obat\n where \n distribusi_obat_detail.distribusi_obat_id = distribusi_obat.id \n and distribusi_obat.asal_id = '1'\n and distribusi_obat.departemen_id = '" . $departemen_id . "'\n and distribusi_obat.puskesmas_id = '" . $puskesmas_id . "' \n and distribusi_obat_detail.obat_id = '{$id}'")->queryAll();
foreach ($list as $item) {
$obj = array();
$obj["date"] = $item['tanggal'];
$tujuan = DistribusiObatTujuan::model()->findByPk($item["asal_id"]);
$departemen = Departemen::model()->findByPk($item["asal_departemen_id"]);
$obj["keterangan"] = "Transfer dari " . $tujuan->nama . " " . $departemen->nama;
$obj["masuk"] = $item['jumlah'];
$obj["keluar"] = 0;
$obj["data"] = "DISTRIBUSI";
$dataTransaksi[] = $obj;
}
//ambil data pemakaian (obat keluar)
$filter = array("puskesmas_id" => $puskesmas_id, "obat_id" => $id, "departemen_id" => $departemen_id);
$pemakaianArr = PemakaianObat::model()->findAllByAttributes($filter);
foreach ($pemakaianArr as $pakai) {
$tujuan = DistribusiObatTujuan::model()->findByPk($pakai->distribusi_obat_tujuan_id);
$departemen = Departemen::model()->findByPk($pakai->departemen_id);
$obj = array();
$obj["date"] = $pakai->tanggal;
$obj["keterangan"] = "Pemakaian " . ucwords(strtolower($tujuan->nama)) . " " . ucwords(strtolower($departemen->nama));
if ($pakai->keterangan != "") {
$obj["keterangan"] = $pakai->keterangan;
}
$obj["masuk"] = 0;
$obj["keluar"] = $pakai->jumlah;
$obj["data"] = "PEMAKAIAN";
$dataTransaksi[] = $obj;
}
}
} else {
if ($tujuan_id == 1) {
//PENERIMAAN OBAT DARI GFLK
//ambil data stok awal
$list = Yii::app()->db->createCommand("select stok_awal.tanggal, stok_awal_detail.obat_id, stok_awal_detail.jumlah\n from stok_awal_detail, stok_awal\n where \n stok_awal_detail.stok_awal_id = stok_awal.id \n and stok_awal.puskesmas_id = '{$puskesmas_id}'\n and stok_awal_detail.obat_id = '{$id}'")->queryAll();
foreach ($list as $item) {
$obj = array();
$obj["date"] = $item['tanggal'];
$obj["keterangan"] = "Stok Awal";
$obj["masuk"] = $item['jumlah'];
$obj["keluar"] = 0;
$obj["data"] = "STOK_AWAL";
$dataTransaksi[] = $obj;
}
//ambil data penerimaan
$list = Yii::app()->db->createCommand("select penerimaan_obat.tanggal, penerimaan_obat_detail.obat_id, penerimaan_obat_detail.jumlah\n from penerimaan_obat_detail, penerimaan_obat\n where \n penerimaan_obat_detail.penerimaan_obat_id = penerimaan_obat.id \n and penerimaan_obat.puskesmas_id = '{$puskesmas_id}'\n and penerimaan_obat_detail.obat_id = '{$id}'")->queryAll();
foreach ($list as $item) {
$obj = array();
$obj["date"] = $item['tanggal'];
$obj["keterangan"] = "Penerimaan dari GFLK";
$obj["masuk"] = $item['jumlah'];
$obj["keluar"] = 0;
$obj["data"] = "PENERIMAAN";
$dataTransaksi[] = $obj;
}
//ambil data distribusi
$list = Yii::app()->db->createCommand("select distribusi_obat.tanggal, distribusi_obat.asal_id, distribusi_obat.asal_departemen_id, distribusi_obat.tujuan_id, distribusi_obat.departemen_id, distribusi_obat_detail.obat_id, distribusi_obat_detail.jumlah\n from distribusi_obat_detail, distribusi_obat\n where \n distribusi_obat_detail.distribusi_obat_id = distribusi_obat.id \n and distribusi_obat.puskesmas_id = '" . $puskesmas_id . "' \n and distribusi_obat_detail.obat_id = '{$id}'")->queryAll();
foreach ($list as $item) {
$obj = array();
$obj["date"] = $item['tanggal'];
if ($item["asal_id"] == 1) {
$tujuan = DistribusiObatTujuan::model()->findByPk($item["tujuan_id"]);
$departemen = Departemen::model()->findByPk($item["departemen_id"]);
$obj["keterangan"] = "Distribusi ke " . $tujuan->nama . " " . $departemen->nama;
$obj["masuk"] = 0;
$obj["keluar"] = $item['jumlah'];
} else {
if ($item["tujuan_id"] == 1) {
$tujuan = DistribusiObatTujuan::model()->findByPk($item["asal_id"]);
$departemen = Departemen::model()->findByPk($item["asal_departemen_id"]);
$obj["keterangan"] = "Pengembalian dari " . $tujuan->nama . " " . $departemen->nama;
$obj["masuk"] = $item['jumlah'];
$obj["keluar"] = 0;
}
}
$obj["data"] = "DISTRIBUSI";
$dataTransaksi[] = $obj;
}
} else {
//STOK OBAT DI APOTEK / PUSTU / DLL
//ambil data penerimaan dari distribusi (obat masuk)
//ambil data distribusi
$list = Yii::app()->db->createCommand("select distribusi_obat.tanggal, distribusi_obat.asal_id, distribusi_obat.asal_departemen_id, distribusi_obat.tujuan_id, distribusi_obat.departemen_id, distribusi_obat_detail.obat_id, distribusi_obat_detail.jumlah\n from distribusi_obat_detail, distribusi_obat\n where \n distribusi_obat_detail.distribusi_obat_id = distribusi_obat.id \n and \n ((\n distribusi_obat.tujuan_id = '" . $tujuan_id . "' \n and \n distribusi_obat.departemen_id = '" . $departemen_id . "' \n ) \n or \n (\n distribusi_obat.asal_id = '" . $tujuan_id . "' \n and \n distribusi_obat.asal_departemen_id = '" . $departemen_id . "' \n ))\n and distribusi_obat.puskesmas_id = '" . $puskesmas_id . "' \n and distribusi_obat_detail.obat_id = '{$id}'")->queryAll();
foreach ($list as $item) {
$obj = array();
$obj["date"] = $item['tanggal'];
if ($item["asal_id"] == 1) {
$tujuan = DistribusiObatTujuan::model()->findByPk($item["asal_id"]);
$departemen = Departemen::model()->findByPk($item["asal_departemen_id"]);
$obj["keterangan"] = "Transfer dari " . $tujuan->nama . " " . $departemen->nama;
$obj["masuk"] = $item['jumlah'];
$obj["keluar"] = 0;
} else {
if ($item["tujuan_id"] == 1) {
$tujuan = DistribusiObatTujuan::model()->findByPk($item["tujuan_id"]);
$departemen = Departemen::model()->findByPk($item["departemen_id"]);
$obj["keterangan"] = "Pengembalian ke " . $tujuan->nama . " " . $departemen->nama;
$obj["masuk"] = 0;
$obj["keluar"] = $item['jumlah'];
}
}
$obj["data"] = "DISTRIBUSI";
$dataTransaksi[] = $obj;
}
//ambil data pemakaian (obat keluar)
$pemakaianArr = PemakaianObat::model()->findAllByAttributes(array("puskesmas_id" => $puskesmas_id, "obat_id" => $id, "distribusi_obat_tujuan_id" => $tujuan_id, "departemen_id" => $departemen_id));
foreach ($pemakaianArr as $pakai) {
$tujuan = DistribusiObatTujuan::model()->findByPk($pakai->distribusi_obat_tujuan_id);
$departemen = Departemen::model()->findByPk($pakai->departemen_id);
$obj = array();
$obj["date"] = $pakai->tanggal;
$obj["keterangan"] = "Pemakaian " . ucwords(strtolower($tujuan->nama)) . " " . ucwords(strtolower($departemen->nama));
if ($pakai->keterangan != "") {
$obj["keterangan"] = $pakai->keterangan;
}
$obj["masuk"] = 0;
$obj["keluar"] = $pakai->jumlah;
$obj["data"] = "PEMAKAIAN";
$dataTransaksi[] = $obj;
}
}
}
//ambil data stock opname
$stockOpnameArr = StockOpnameObat::model()->findAllByAttributes(array("puskesmas_id" => $puskesmas_id, "obat_id" => $id, "distribusi_obat_tujuan_id" => $tujuan_id, "departemen_id" => $departemen_id));
foreach ($stockOpnameArr as $pakai) {
$tujuan = DistribusiObatTujuan::model()->findByPk($pakai->distribusi_obat_tujuan_id);
$departemen = Departemen::model()->findByPk($pakai->departemen_id);
$obj = array();
$obj["date"] = date("Y-m-d", strtotime($pakai->waktu));
$obj["keterangan"] = "Perubahan Stok Oleh " . User::model()->findByPk($pakai->user_id)->nama_lengkap;
$obj["masuk"] = $pakai->jumlah_masuk;
$obj["keluar"] = $pakai->jumlah_keluar;
$obj["data"] = "STOCK_OPNAME";
$dataTransaksi[] = $obj;
}
if ($include_obat_rusak == TRUE) {
//ambil data obat rusak
$list = Yii::app()->db->createCommand("select \n obat_rusak.waktu, obat_rusak.distribusi_obat_tujuan_id, obat_rusak.departemen_id, obat_rusak.keterangan,\n obat_rusak_detail.obat_id, obat_rusak_detail.jumlah\n from obat_rusak_detail, obat_rusak\n where \n obat_rusak.distribusi_obat_tujuan_id = '" . $tujuan_id . "'\n AND obat_rusak.departemen_id = '" . $departemen_id . "'\n AND obat_rusak_detail.obat_rusak_id = obat_rusak.id \n AND obat_rusak.puskesmas_id = '" . $puskesmas_id . "' \n AND obat_rusak_detail.obat_id = '{$id}'")->queryAll();
foreach ($list as $item) {
$obj = array();
$obj["date"] = date("Y-m-d", strtotime($item['waktu']));
$obj["keterangan"] = "Obat Rusak, Ket : " . $item["keterangan"];
$obj["masuk"] = 0;
$obj["keluar"] = $item['jumlah'];
$obj["data"] = "OBAT_RUSAK";
$dataTransaksi[] = $obj;
}
}
//echo count($dataTransaksi)."<br>";
usort($dataTransaksi, array("KartuStok", "compareData"));
return $dataTransaksi;
}
