/**
* Dapatkan laporan kinerja tusbung
*/
public function get_report()
{
$get = $this->prepare_get(array('unit', 'blth'));
extract($get);
$unit = intval($unit);
$blth = intval($blth);
$r = array();
if (empty($unit) or empty($blth)) {
return $r;
}
$t = array();
$this->db->query("START TRANSACTION");
// dicetak
$run = $this->db->query("SELECT `ID_TAGIHAN`, DATE(`TGL_CETAK_TAGIHAN`) AS `CETAK` FROM `tagihan` WHERE `ID_BLTH` = '{$blth}' GROUP BY DATE(`TGL_CETAK_TAGIHAN`)");
// cacah
for ($i = 0; $i < count($run); $i++) {
$tgl = $run[$i]->CETAK;
if (is_null($tgl)) {
continue;
}
$srun = $this->db->query("SELECT SUM(`LEMBAR_TAGIHAN`) AS `LEMBAR`, SUM(`RPTAG_TAGIHAN`) AS `TAGIHAN` FROM `tagihan` WHERE DATE(`TGL_CETAK_TAGIHAN`) = '{$tgl}'", TRUE);
$t[$tgl]['cetak']['lembar'] = $srun->LEMBAR;
$t[$tgl]['cetak']['tagihan'] = $srun->TAGIHAN;
}
// dilunasi
$run = $this->db->query("SELECT `ID_TAGIHAN`, DATE(`TGL_LUNAS_TAGIHAN`) AS `LUNAS` FROM `tagihan` WHERE `ID_BLTH` = '{$blth}' GROUP BY DATE(`TGL_LUNAS_TAGIHAN`)");
// cacah
for ($i = 0; $i < count($run); $i++) {
$tgl = $run[$i]->LUNAS;
if (is_null($tgl)) {
continue;
}
$srun = $this->db->query("SELECT SUM(`LEMBAR_TAGIHAN`) AS `LEMBAR`, SUM(`RPTAG_TAGIHAN`) AS `TAGIHAN` FROM `tagihan` WHERE DATE(`TGL_LUNAS_TAGIHAN`) = '{$tgl}'", TRUE);
$t[$tgl]['lunas']['lembar'] = $srun->LEMBAR;
$t[$tgl]['lunas']['tagihan'] = $srun->TAGIHAN;
}
$this->db->query("COMMIT");
// susun dalam tabel
$total = array();
$r = array();
if (count($t) > 0) {
foreach ($t as $key => $val) {
$tr = array();
$tr['tgl'] = datedb_to_tanggal($key, 'd-M-Y');
// dicetak
if (!isset($total['cetak'])) {
$total['cetak'] = array(0, 0);
}
if (!isset($t[$key]['cetak'])) {
$tr['cetak_lembar'] = $tr['cetak_tagihan'] = 0;
} else {
$tr['cetak_lembar'] = number_format($val['cetak']['lembar'], 0, ',', '.');
$total['cetak'][0] += $val['cetak']['lembar'];
$tr['cetak_tagihan'] = number_format($val['cetak']['tagihan'], 0, ',', '.');
$total['cetak'][1] += $val['cetak']['tagihan'];
}
// dilunasi
if (!isset($total['lunas'])) {
$total['lunas'] = array(0, 0);
}
if (!isset($t[$key]['lunas'])) {
$tr['lunas_lembar'] = $tr['lunas_tagihan'] = 0;
} else {
$tr['lunas_lembar'] = number_format($val['lunas']['lembar'], 0, ',', '.');
$total['lunas'][0] += $val['lunas']['lembar'];
$tr['lunas_tagihan'] = number_format($val['lunas']['tagihan'], 0, ',', '.');
$total['lunas'][1] += $val['lunas']['tagihan'];
}
// tidak lunas
if (!isset($total['tlunas'])) {
$total['tlunas'] = array(0, 0);
}
$tll = $this->tonumber($tr['cetak_lembar']) - $this->tonumber($tr['lunas_lembar']);
$tlt = $this->tonumber($tr['cetak_tagihan']) - $this->tonumber($tr['lunas_tagihan']);
$tr['tlunas_lembar'] = $tll;
$tr['tlunas_tagihan'] = number_format($tlt, 0, ',', '.');
$total['tlunas'][0] += $tll;
$total['tlunas'][1] += $tlt;
// diputus
if (!isset($total['p'])) {
$total['p'] = array(0, 0);
}
$tr['p_lembar'] = $tr['p_tagihan'] = 0;
// diputus lunas
if (!isset($total['pl'])) {
$total['pl'] = array(0, 0);
}
$tr['pl_lembar'] = $tr['pl_tagihan'] = 0;
// sambung
if (!isset($total['sambung'])) {
$total['sambung'] = array(0, 0);
}
$tr['sambung_lembar'] = $tr['sambung_tagihan'] = 0;
// rasio
if (!isset($total['rasio'])) {
$total['rasio'] = array(0, 0);
}
$rl = $tr['cetak_lembar'] != 0 ? $tr['lunas_lembar'] * 100 / $tr['cetak_lembar'] : 0;
$rt = $tr['cetak_tagihan'] != 0 ? $tr['lunas_tagihan'] * 100 / $tr['cetak_tagihan'] : 0;
$tr['rasio_lembar'] = number_format($rl, 2, ',', '.') . '%';
$total['rasio'][0] += $rl;
$tr['rasio_tagihan'] = number_format($rt, 2, ',', '.') . '%';
$total['rasio'][1] += $rt;
$r[] = $tr;
}
// hitung total
$total['rasio'][0] = number_format($total['rasio'][0] / count($r), 2, ',', '.');
$total['rasio'][1] = number_format($total['rasio'][1] / count($r), 2, ',', '.');
// number_format untuk tagihan
$total['cetak'][1] = number_format($total['cetak'][1], 0, ',', '.');
$total['lunas'][1] = number_format($total['lunas'][1], 0, ',', '.');
$total['p'][1] = number_format($total['p'][1], 0, ',', '.');
$total['pl'][1] = number_format($total['pl'][1], 0, ',', '.');
$total['sambung'][1] = number_format($total['sambung'][1], 0, ',', '.');
$total['tlunas'][1] = number_format($total['tlunas'][1], 0, ',', '.');
$total['rasio'][0] = $total['rasio'][0] . '%';
$total['rasio'][1] = $total['rasio'][1] . '%';
}
return array('data' => $r, 'total' => $total);
}
/**
* Mendapatkan data aduan
*/
public function get_aduan()
{
$get = $this->prepare_get(array('cpage'));
extract($get);
$cpage = intval($cpage);
$r = array();
// total halaman
$dtpg = 20;
$run = $this->db->query("SELECT COUNT(`ID_ADUAN`) AS `TOTAL` FROM `aduan` LIMIT 0, 400", TRUE);
$total = $run->TOTAL;
$numpage = ceil($total / $dtpg);
$start = $cpage * $dtpg;
$run = $this->db->query("SELECT a.ID_ADUAN, a.ID_PELANGGAN, b.ALAMAT_PELANGGAN, a.TELEPON_ADUAN, a.ISI_ADUAN, a.TL_ADUAN, a.TANGGAL_ADUAN, a.NAMA_ADUAN, b.ALAMAT_PELANGGAN FROM aduan a, pelanggan b WHERE a.ID_PELANGGAN = b.ID_PELANGGAN ORDER BY a.TANGGAL_ADUAN DESC LIMIT {$start}, {$dtpg}");
for ($i = 0; $i < count($run); $i++) {
$r[] = array('id' => $run[$i]->ID_ADUAN, 'idpel' => $run[$i]->ID_PELANGGAN, 'nama' => trim($run[$i]->NAMA_ADUAN), 'alamat' => trim($run[$i]->ALAMAT_PELANGGAN), 'info' => trim($run[$i]->NAMA_ADUAN) . '<br>' . trim($run[$i]->ALAMAT_PELANGGAN), 'telepon' => $run[$i]->TELEPON_ADUAN, 'aduan' => $run[$i]->ISI_ADUAN, 'tl' => $run[$i]->TL_ADUAN, 'tanggal' => datedb_to_tanggal($run[$i]->TANGGAL_ADUAN, 'd/m/Y H:i'));
}
return array('data' => $r, 'numpage' => $numpage);
}
/**
* Untuk laporan tusbung
*/
public function tusbung($r)
{
$get = $this->prepare_get(array('unit', 'blth'));
extract($get);
$unit = intval($unit);
$blth = intval($blth);
// cari unit
$run = $this->db->query("SELECT `NAMA_UNIT` FROM `unit` WHERE `ID_UNIT` = '{$unit}'", TRUE);
$nmunit = $run->NAMA_UNIT;
// bulan tahun
$run = $this->db->query("SELECT `NAMA_BLTH` FROM `blth` WHERE `ID_BLTH` = '{$blth}'", TRUE);
$bln = substr($run->NAMA_BLTH, 0, 2);
$thn = substr($run->NAMA_BLTH, 2, 4);
$nmblth = datedb_to_tanggal($thn . '-' . $bln . '-' . '01', 'F Y');
include_once 'lib/phpexcel/PHPExcel.php';
$obj = new \PHPExcel();
$obj->getProperties()->setCreator('Sistem Analisa Baca Meter Madura')->setLastModifiedBy('Sistem Analisa Baca Meter Madura')->setTitle("Laporan Kinerja Tusbung")->setSubject('Laporan')->setDescription('Laporan Kinerja Tusbung')->setKeywords('laporan', 'kinerja', 'tusbung')->setCategory('Laporan');
$obj->setActiveSheetIndex(0);
$obj->getDefaultStyle()->getFont()->setName('Calibri');
$obj->getDefaultStyle()->getFont()->setSize(11);
$obj->getActiveSheet()->setCellValue('A3', 'Laporan Kinerja Pemutusan');
$obj->getActiveSheet()->setCellValue('A4', "Zona ... Unit {$nmunit}");
$obj->getActiveSheet()->setCellValue('A5', "Bulan: {$nmblth}");
$obj->getActiveSheet()->getStyle('A1:A5')->getFont()->setBold(true);
$obj->getActiveSheet()->getStyle('A1:A5')->getFont()->setSize(12);
$obj->getActiveSheet()->mergeCells('A3:P3');
$obj->getActiveSheet()->mergeCells('A4:P4');
$obj->getActiveSheet()->mergeCells('A5:P5');
$obj->getActiveSheet()->getStyle('A3:A5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$obj->getActiveSheet()->setCellValue('A7', 'No');
$obj->getActiveSheet()->setCellValue('B7', 'Tgl Cetak');
$obj->getActiveSheet()->setCellValue('C7', 'Tul VI-01 Dicetak');
$obj->getActiveSheet()->setCellValue('E7', 'Tul VI-01 Dilunasi');
$obj->getActiveSheet()->setCellValue('G7', 'Tul VI-01 Diputus');
$obj->getActiveSheet()->setCellValue('I7', 'Tul VI-01 Diputus Lunas');
$obj->getActiveSheet()->setCellValue('K7', 'Sambung');
$obj->getActiveSheet()->setCellValue('M7', 'Tul VI-01 Tidak Lunas');
$obj->getActiveSheet()->setCellValue('O7', 'Rasio Pelunasan %');
$obj->getActiveSheet()->setCellValue('C8', 'Lembar');
$obj->getActiveSheet()->setCellValue('E8', 'Lembar');
$obj->getActiveSheet()->setCellValue('G8', 'Lembar');
$obj->getActiveSheet()->setCellValue('I8', 'Lembar');
$obj->getActiveSheet()->setCellValue('K8', 'Lembar');
$obj->getActiveSheet()->setCellValue('M8', 'Lembar');
$obj->getActiveSheet()->setCellValue('O8', 'Lembar');
$obj->getActiveSheet()->setCellValue('D8', 'Tagihan');
$obj->getActiveSheet()->setCellValue('F8', 'Tagihan');
$obj->getActiveSheet()->setCellValue('H8', 'Tagihan');
$obj->getActiveSheet()->setCellValue('J8', 'Tagihan');
$obj->getActiveSheet()->setCellValue('L8', 'Tagihan');
$obj->getActiveSheet()->setCellValue('N8', 'Tagihan');
$obj->getActiveSheet()->setCellValue('P8', 'Tagihan');
$obj->getActiveSheet()->mergeCells('A7:A8');
$obj->getActiveSheet()->mergeCells('B7:B8');
$obj->getActiveSheet()->mergeCells('C7:D7');
$obj->getActiveSheet()->mergeCells('E7:F7');
$obj->getActiveSheet()->mergeCells('G7:H7');
$obj->getActiveSheet()->mergeCells('I7:J7');
$obj->getActiveSheet()->mergeCells('K7:L7');
$obj->getActiveSheet()->mergeCells('M7:N7');
$obj->getActiveSheet()->mergeCells('O7:P7');
$obj->getActiveSheet()->getStyle('A7:P8')->getFont()->setBold(true);
$obj->getActiveSheet()->getStyle('A7:P8')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$obj->getActiveSheet()->getColumnDimension('A')->setWidth(4);
$obj->getActiveSheet()->getColumnDimension('B')->setWidth(11);
for ($i = 'C'; $i <= 'P'; $i++) {
$obj->getActiveSheet()->getColumnDimension($i)->setWidth(12);
}
$data = $r['data'];
$total = $r['total'];
for ($i = 0, $crow = 9; $i < count($data); $i++, $crow++) {
$d = $data[$i];
$obj->getActiveSheet()->setCellValue('A' . $crow, $i + 1);
$obj->getActiveSheet()->setCellValue('B' . $crow, $d['tgl']);
$obj->getActiveSheet()->setCellValue('B' . $crow, $d['tgl']);
$obj->getActiveSheet()->setCellValue('C' . $crow, $d['cetak_lembar']);
$obj->getActiveSheet()->setCellValueExplicit('D' . $crow, $d['cetak_tagihan'], \PHPExcel_Cell_DataType::TYPE_STRING);
$obj->getActiveSheet()->setCellValue('E' . $crow, $d['lunas_lembar']);
$obj->getActiveSheet()->setCellValueExplicit('F' . $crow, $d['lunas_tagihan'], \PHPExcel_Cell_DataType::TYPE_STRING);
$obj->getActiveSheet()->setCellValue('G' . $crow, $d['p_lembar']);
$obj->getActiveSheet()->setCellValueExplicit('H' . $crow, $d['p_tagihan'], \PHPExcel_Cell_DataType::TYPE_STRING);
$obj->getActiveSheet()->setCellValue('I' . $crow, $d['pl_lembar']);
$obj->getActiveSheet()->setCellValueExplicit('J' . $crow, $d['pl_tagihan'], \PHPExcel_Cell_DataType::TYPE_STRING);
$obj->getActiveSheet()->setCellValue('K' . $crow, $d['sambung_lembar']);
$obj->getActiveSheet()->setCellValueExplicit('L' . $crow, $d['sambung_tagihan'], \PHPExcel_Cell_DataType::TYPE_STRING);
$obj->getActiveSheet()->setCellValue('M' . $crow, $d['tlunas_lembar']);
$obj->getActiveSheet()->setCellValueExplicit('N' . $crow, $d['tlunas_tagihan'], \PHPExcel_Cell_DataType::TYPE_STRING);
$obj->getActiveSheet()->setCellValueExplicit('O' . $crow, $d['rasio_lembar'], \PHPExcel_Cell_DataType::TYPE_STRING);
$obj->getActiveSheet()->setCellValueExplicit('P' . $crow, $d['rasio_tagihan'], \PHPExcel_Cell_DataType::TYPE_STRING);
}
$obj->getActiveSheet()->setCellValue('A' . $crow, 'Jumlah');
$obj->getActiveSheet()->mergeCells('A' . $crow . ':B' . $crow);
$obj->getActiveSheet()->setCellValue('C' . $crow, $total['cetak'][0]);
$obj->getActiveSheet()->setCellValueExplicit('D' . $crow, $total['cetak'][1], \PHPExcel_Cell_DataType::TYPE_STRING);
$obj->getActiveSheet()->setCellValue('E' . $crow, $total['lunas'][0]);
$obj->getActiveSheet()->setCellValueExplicit('F' . $crow, $total['lunas'][1], \PHPExcel_Cell_DataType::TYPE_STRING);
$obj->getActiveSheet()->setCellValue('G' . $crow, $total['p'][0]);
$obj->getActiveSheet()->setCellValueExplicit('H' . $crow, $total['p'][1], \PHPExcel_Cell_DataType::TYPE_STRING);
$obj->getActiveSheet()->setCellValue('I' . $crow, $total['pl'][0]);
$obj->getActiveSheet()->setCellValueExplicit('J' . $crow, $total['pl'][1], \PHPExcel_Cell_DataType::TYPE_STRING);
$obj->getActiveSheet()->setCellValue('K' . $crow, $total['sambung'][0]);
$obj->getActiveSheet()->setCellValueExplicit('L' . $crow, $total['sambung'][1], \PHPExcel_Cell_DataType::TYPE_STRING);
$obj->getActiveSheet()->setCellValue('M' . $crow, $total['tlunas'][0]);
$obj->getActiveSheet()->setCellValueExplicit('N' . $crow, $total['tlunas'][1], \PHPExcel_Cell_DataType::TYPE_STRING);
$obj->getActiveSheet()->setCellValueExplicit('O' . $crow, $total['rasio'][0], \PHPExcel_Cell_DataType::TYPE_STRING);
$obj->getActiveSheet()->setCellValueExplicit('P' . $crow, $total['rasio'][1], \PHPExcel_Cell_DataType::TYPE_STRING);
$obj->getActiveSheet()->getStyle('A' . $crow . ':P' . $crow)->getFont()->setBold(true);
$obj->getActiveSheet()->getStyle('A7:P' . $crow)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
$obj->getActiveSheet()->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$obj->getActiveSheet()->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_FOLIO);
$objWriter = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment;filename=\"laporan-tusbung-{$nmunit}-{$bln}{$thn}.xlsx\"");
header('Cache-Control: max-age=0');
$objWriter->save('php://output');
}
