/** * 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'); }