public function post_exphutangarmada()
{
$input = Input::all();
$shift = 'all';
$saldohutangbymonth = $this->allQuery($input['date'], Auth::user()->pool_id, $shift, 0, 1000);
if ($saldohutangbymonth) {
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator(Auth::user()->fullname)->setLastModifiedBy(Auth::user()->fullname)->setTitle("Laporan Harian " . Pool::find(Auth::user()->pool_id)->pool_name . '-' . date('Y-m-d'))->setSubject("Laporan Harian " . Pool::find(Auth::user()->pool_id)->pool_name . '-' . date('Y-m-d'))->setDescription("Laporan harian operasi pool")->setKeywords("Laporan Harian")->setCategory("");
$styleArray = array('font' => array('bold' => true, 'color' => array('rgb' => 'FF0000'), 'size' => 16));
$sheet_active = 0;
$objPHPExcel->createSheet(NULL, $sheet_active);
$objPHPExcel->setActiveSheetIndex($sheet_active);
//title
$objPHPExcel->getActiveSheet()->mergeCells('A2:J2');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'LAPORAN HUTANG ARMADA PER TANGGAL ' . Myfungsi::fulldate(strtotime($input['date'])));
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleArray);
// Coloum header
$objPHPExcel->getActiveSheet()->mergeCells('A5:A6');
$objPHPExcel->getActiveSheet()->mergeCells('B5:B6');
$objPHPExcel->getActiveSheet()->mergeCells('C5:D5');
$objPHPExcel->getActiveSheet()->mergeCells('E5:E6');
$objPHPExcel->getActiveSheet()->mergeCells('F5:I5');
$objPHPExcel->getActiveSheet()->mergeCells('J5:L5');
$objPHPExcel->getActiveSheet()->mergeCells('M5:O5');
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'NO');
$objPHPExcel->getActiveSheet()->setCellValue('B5', 'BAPAK ASUH');
$objPHPExcel->getActiveSheet()->setCellValue('C5', 'PENGEMUDI');
$objPHPExcel->getActiveSheet()->setCellValue('C6', 'NIP');
$objPHPExcel->getActiveSheet()->setCellValue('D6', 'NAMA');
$objPHPExcel->getActiveSheet()->setCellValue('E5', 'BODY');
$objPHPExcel->getActiveSheet()->setCellValue('F5', 'PEMAKAIAN SPAREPART ARMADA');
$objPHPExcel->getActiveSheet()->setCellValue('F6', 'PEMAKAIAN');
$objPHPExcel->getActiveSheet()->setCellValue('G6', 'TABUNGAN');
$objPHPExcel->getActiveSheet()->setCellValue('H6', 'BAYAR');
$objPHPExcel->getActiveSheet()->setCellValue('I6', 'SELISIH');
$objPHPExcel->getActiveSheet()->setCellValue('J5', 'SETORAN ARMADA');
$objPHPExcel->getActiveSheet()->setCellValue('J6', 'KS');
$objPHPExcel->getActiveSheet()->setCellValue('K6', 'BAYAR KS');
$objPHPExcel->getActiveSheet()->setCellValue('L6', 'SELISIH');
$objPHPExcel->getActiveSheet()->setCellValue('M5', 'SALDO ARMADA');
$objPHPExcel->getActiveSheet()->setCellValue('M6', 'SALDO SPAREPART');
$objPHPExcel->getActiveSheet()->setCellValue('N6', 'SALDO KS');
$objPHPExcel->getActiveSheet()->setCellValue('O6', 'SALDO AKHIR');
$objPHPExcel->getActiveSheet()->setCellValue('P5', 'SHIFT');
$no = 1;
$starline = 8;
foreach ($saldohutangbymonth as $saldo) {
$bpkasuh = Anakasuh::where('status', '=', 1)->where('fleet_id', '=', $saldo->fleet_id)->first();
$bravo = Driver::find($saldo->bravo_driver_id);
$saldosp = $saldo->tabungan_sparepart + $saldo->hutang_dp_sparepart + $saldo->cicilan_sparepart - $saldo->pemakaian_part;
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $starline, $no);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $starline, $bpkasuh ? User::find($bpkasuh->user_id)->fullname : 'TIDAK ADA BAPAK ASUH');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $starline, $bravo ? $bravo->nip : 'NONE');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $starline, $bravo ? $bravo->name : 'NONE');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $starline, $saldo->taxi_number);
//pemakaikan sp
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $starline, $saldo->pemakaian_part);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $starline, $saldo->tabungan_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $starline, $saldo->hutang_dp_sparepart + $saldo->cicilan_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $starline, $saldosp);
//setoran armada
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $starline, $saldo->ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $starline, $saldo->cicilan_ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $starline, $saldo->selisi_ks);
//saldo armada
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $starline, $saldosp);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $starline, $saldo->selisi_ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, $starline, $saldosp + $saldo->selisi_ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(15, $starline, $saldo->shift_id);
$no++;
$starline++;
}
$objPHPExcel->getActiveSheet()->getStyle('A5:O' . ($starline + 1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_HAIR);
$objPHPExcel->getActiveSheet()->getStyle('A5:O6')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A5:O' . ($starline + 1))->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($starline + 1) . ':O' . ($starline + 1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//end
//SET TANGGAL UNDUH DAN PASSWORD
$objPHPExcel->getActiveSheet()->setCellValue('B' . ($starline + 10), 'Tanggal Unduh');
$objPHPExcel->getActiveSheet()->setCellValue('C' . ($starline + 10), ':');
$objPHPExcel->getActiveSheet()->setCellValue('D' . ($starline + 10), date('Y-m-d H:i:s'));
$objPHPExcel->getSecurity()->setLockWindows(true);
$objPHPExcel->getSecurity()->setLockStructure(true);
$objPHPExcel->getSecurity()->setWorkbookPassword("FreeBlocking");
$objPHPExcel->getActiveSheet()->getProtection()->setPassword('FreeBlocking');
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
// This should be enabled in order to enable any of the following!
//$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
$objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
//END
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(path('public') . 'Laporan-hutang-armada.xls');
return Response::download(path('public') . 'Laporan-hutang-armada.xls');
}
//end if
//var_dump($saldohutangbymonth);
}
public function post_expkasharian()
{
$shift_id = Input::get('shift_id', 'all');
$startdate = Input::get('startdateops', date('Y-m-01'));
$date = Input::get('date', date('Y-m-d'));
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator(Auth::user()->fullname)->setLastModifiedBy(Auth::user()->fullname)->setTitle("Laporan Harian " . Pool::find(Auth::user()->pool_id)->pool_name . '-' . date('Y-m-d'))->setSubject("Laporan Harian " . Pool::find(Auth::user()->pool_id)->pool_name . '-' . date('Y-m-d'))->setDescription("Laporan harian operasi pool")->setKeywords("Laporan Harian")->setCategory("");
$styleArray = array('font' => array('bold' => true, 'color' => array('rgb' => 'FF0000'), 'size' => 16));
$jenis_kendaraan = Fleetmodel::where('actived', '=', 1)->get();
$sheet_active = 0;
if (Input::get('statusopsdef') == 'all') {
//query export
if ($shift_id == 'all') {
$financials = DB::table('financial_report_summary_graf')->where('operasi_time', '>=', $startdate)->where('operasi_time', '<=', $date)->where_pool_id(Auth::user()->pool_id)->order_by('operasi_time', 'asc')->get();
} else {
$financials = DB::table('financial_report_summary')->where('shift_id', '=', $shift_id)->where('operasi_time', '>=', $startdate)->where('operasi_time', '<=', $date)->where_pool_id(Auth::user()->pool_id)->order_by('operasi_time', 'asc')->get();
}
$objPHPExcel->createSheet(NULL, 0);
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->mergeCells('A2:X2');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'LAPORAN PENDAPATAN PERIODE TANGGAL ' . Myfungsi::fulldate(strtotime($startdate)) . ' - ' . Myfungsi::fulldate(strtotime($date)) . ' Gabungan Armada');
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);
$objPHPExcel->getActiveSheet()->mergeCells('A5:A6');
$objPHPExcel->getActiveSheet()->mergeCells('B5:B6');
$objPHPExcel->getActiveSheet()->mergeCells('C5:D5');
$objPHPExcel->getActiveSheet()->mergeCells('E5:E6');
$objPHPExcel->getActiveSheet()->mergeCells('F5:G5');
$objPHPExcel->getActiveSheet()->mergeCells('H5:H6');
$objPHPExcel->getActiveSheet()->mergeCells('I5:I6');
$objPHPExcel->getActiveSheet()->mergeCells('J5:J6');
$objPHPExcel->getActiveSheet()->mergeCells('K5:K6');
$objPHPExcel->getActiveSheet()->mergeCells('L5:O5');
$objPHPExcel->getActiveSheet()->mergeCells('P5:R5');
//$objPHPExcel->getActiveSheet()->mergeCells('R5:R6');
$objPHPExcel->getActiveSheet()->mergeCells('S5:S6');
$objPHPExcel->getActiveSheet()->mergeCells('T5:T6');
$objPHPExcel->getActiveSheet()->mergeCells('U5:U6');
$objPHPExcel->getActiveSheet()->mergeCells('V5:V6');
$objPHPExcel->getActiveSheet()->mergeCells('W5:W6');
$objPHPExcel->getActiveSheet()->mergeCells('X5:X6');
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'NO');
$objPHPExcel->getActiveSheet()->setCellValue('B5', 'BAPAK ASUH');
$objPHPExcel->getActiveSheet()->setCellValue('C5', 'PENGEMUDI');
$objPHPExcel->getActiveSheet()->setCellValue('C6', 'NIP');
$objPHPExcel->getActiveSheet()->setCellValue('D6', 'NAMA');
$objPHPExcel->getActiveSheet()->setCellValue('E5', 'TANGGAL OPERASI');
$objPHPExcel->getActiveSheet()->setCellValue('F5', 'STATUS');
$objPHPExcel->getActiveSheet()->setCellValue('F6', 'OPS');
$objPHPExcel->getActiveSheet()->setCellValue('G6', 'BS');
$objPHPExcel->getActiveSheet()->setCellValue('H5', 'SETORAN MURNI');
$objPHPExcel->getActiveSheet()->setCellValue('I5', 'TAB SPAREPART');
$objPHPExcel->getActiveSheet()->setCellValue('J5', 'DENDA JAM');
$objPHPExcel->getActiveSheet()->setCellValue('K5', 'DP SPAREPART');
$objPHPExcel->getActiveSheet()->setCellValue('L5', 'BAYAR CICILAN');
$objPHPExcel->getActiveSheet()->setCellValue('L6', 'KS');
$objPHPExcel->getActiveSheet()->setCellValue('M6', 'S-PART');
$objPHPExcel->getActiveSheet()->setCellValue('N6', 'DP-KSO');
$objPHPExcel->getActiveSheet()->setCellValue('O6', 'HUT-LAMA');
$objPHPExcel->getActiveSheet()->setCellValue('P5', 'BAYAR');
$objPHPExcel->getActiveSheet()->setCellValue('P6', 'STIKER BANDARA & KEAMANAN');
$objPHPExcel->getActiveSheet()->setCellValue('Q6', 'CUCI');
$objPHPExcel->getActiveSheet()->setCellValue('R6', 'LAKA');
$objPHPExcel->getActiveSheet()->setCellValue('S5', 'HARUS SETOR');
$objPHPExcel->getActiveSheet()->setCellValue('T5', 'POTONGAN');
$objPHPExcel->getActiveSheet()->setCellValue('U5', 'SETOR CASH');
$objPHPExcel->getActiveSheet()->setCellValue('V5', 'KETEKORAN');
$objPHPExcel->getActiveSheet()->setCellValue('W5', 'SETORAN OPS');
$objPHPExcel->getActiveSheet()->setCellValue('X5', 'SHIFT');
$no = 1;
$starline = 8;
foreach ($financials as $finan) {
$ks = abs($finan->setoran_cash - ($finan->setoran_wajib + $finan->tabungan_sparepart + $finan->denda + $finan->cicilan_sparepart + $finan->cicilan_ks + $finan->biaya_cuci + $finan->iuran_laka + $finan->cicilan_dp_kso + $finan->cicilan_hutang_lama + $finan->cicilan_lain + $finan->hutang_dp_sparepart - $finan->potongan));
$bs = 'Tidak';
if ($ks <= $finan->cicilan_ks) {
$bs = 'Ya';
}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $starline, $no);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $starline, '');
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $starline, $finan->nip);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $starline, $finan->name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $starline, $finan->operasi_time);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $starline, $finan->kode);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $starline, $bs);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $starline, $finan->setoran_wajib);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $starline, $finan->tabungan_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $starline, $finan->denda);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $starline, $finan->hutang_dp_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $starline, $finan->cicilan_ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $starline, $finan->cicilan_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $starline, $finan->cicilan_dp_kso);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, $starline, $finan->cicilan_hutang_lama);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(15, $starline, $finan->cicilan_lain);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(16, $starline, $finan->biaya_cuci);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(17, $starline, $finan->iuran_laka);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(18, $starline, '=SUM(H' . $starline . ':R' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(19, $starline, $finan->potongan);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(20, $starline, $finan->setoran_cash);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(20, $starline, $finan->ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(21, $starline, '=(U' . $starline . '-(S' . $starline . '-T' . $starline . '))');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(22, $starline, '=(U' . $starline . '-(Q' . $starline . '+R' . $starline . '))');
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(23, $starline, $finan->shift); //col X
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(24, $starline, $finan->shift_id);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, $starline, $finan->cicilan_lain);
//hidden coloumn status operasi
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(25, $starline, $finan->operasi_status_id); //col Z
$no++;
$starline++;
}
$objPHPExcel->getActiveSheet()->mergeCells('A' . ($starline + 1) . ':G' . ($starline + 1) . '');
$objPHPExcel->getActiveSheet()->setCellValue('A' . ($starline + 1), 'TOTAL SETORAN ');
$objPHPExcel->getActiveSheet()->setCellValue('H' . ($starline + 1), '=SUM(H8:H' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('I' . ($starline + 1), '=SUM(I8:I' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('J' . ($starline + 1), '=SUM(J8:J' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('K' . ($starline + 1), '=SUM(K8:K' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('L' . ($starline + 1), '=SUM(L8:L' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('M' . ($starline + 1), '=SUM(M8:M' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('N' . ($starline + 1), '=SUM(N8:N' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('O' . ($starline + 1), '=SUM(O8:O' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('P' . ($starline + 1), '=SUM(P8:P' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('Q' . ($starline + 1), '=SUM(Q8:Q' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('R' . ($starline + 1), '=SUM(R8:R' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('S' . ($starline + 1), '=SUM(S8:S' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('T' . ($starline + 1), '=SUM(T8:T' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('U' . ($starline + 1), '=SUM(U8:U' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('V' . ($starline + 1), '=SUM(V8:V' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('W' . ($starline + 1), '=SUM(W8:W' . $starline . ')');
$objPHPExcel->getActiveSheet()->getStyle('A5:X' . ($starline + 1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_HAIR);
$objPHPExcel->getActiveSheet()->getStyle('A5:X6')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A5:X' . ($starline + 1))->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($starline + 1) . ':X' . ($starline + 1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->setTitle('Laporan KAS ' . date('d', strtotime($startdate)) . ' - ' . $date);
} else {
foreach ($jenis_kendaraan as $model) {
//query export
if ($shift_id == 'all') {
$financials = DB::table('financial_report_sum_all')->where('operasi_time', '>=', $startdate)->where('operasi_time', '<=', $date)->where('fleet_model_id', '=', $model->id)->where_pool_id(Auth::user()->pool_id)->order_by('operasi_time', 'asc')->get();
} else {
$financials = DB::table('financial_report_sum')->where('shift_id', '=', $shift_id)->where('operasi_time', '>=', $startdate)->where('operasi_time', '<=', $date)->where('fleet_model_id', '=', $model->id)->where_pool_id(Auth::user()->pool_id)->order_by('operasi_time', 'asc')->get();
}
$objPHPExcel->createSheet(NULL, $sheet_active);
$objPHPExcel->setActiveSheetIndex($sheet_active);
$objPHPExcel->getActiveSheet()->mergeCells('A2:X2');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'LAPORAN PENDAPATAN PERIODE TANGGAL ' . Myfungsi::fulldate(strtotime($startdate)) . ' - ' . Myfungsi::fulldate(strtotime($date)) . ' ' . $model->fleet_model);
$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setVisible(false);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setVisible(false);
$objPHPExcel->getActiveSheet()->mergeCells('A5:A6');
$objPHPExcel->getActiveSheet()->mergeCells('B5:B6');
$objPHPExcel->getActiveSheet()->mergeCells('C5:D5');
$objPHPExcel->getActiveSheet()->mergeCells('E5:E6');
$objPHPExcel->getActiveSheet()->mergeCells('F5:G5');
$objPHPExcel->getActiveSheet()->mergeCells('H5:H6');
$objPHPExcel->getActiveSheet()->mergeCells('I5:I6');
$objPHPExcel->getActiveSheet()->mergeCells('J5:J6');
$objPHPExcel->getActiveSheet()->mergeCells('K5:K6');
$objPHPExcel->getActiveSheet()->mergeCells('L5:O5');
$objPHPExcel->getActiveSheet()->mergeCells('P5:R5');
//$objPHPExcel->getActiveSheet()->mergeCells('R5:R6');
$objPHPExcel->getActiveSheet()->mergeCells('S5:S6');
$objPHPExcel->getActiveSheet()->mergeCells('T5:T6');
$objPHPExcel->getActiveSheet()->mergeCells('U5:U6');
$objPHPExcel->getActiveSheet()->mergeCells('V5:V6');
$objPHPExcel->getActiveSheet()->mergeCells('W5:W6');
$objPHPExcel->getActiveSheet()->mergeCells('X5:X6');
$objPHPExcel->getActiveSheet()->setCellValue('A5', 'NO');
$objPHPExcel->getActiveSheet()->setCellValue('B5', 'BAPAK ASUH');
$objPHPExcel->getActiveSheet()->setCellValue('C5', 'PENGEMUDI');
$objPHPExcel->getActiveSheet()->setCellValue('C6', 'NIP');
$objPHPExcel->getActiveSheet()->setCellValue('D6', 'NAMA');
$objPHPExcel->getActiveSheet()->setCellValue('E5', 'TANGGAL OPERASI');
$objPHPExcel->getActiveSheet()->setCellValue('F5', 'STATUS');
$objPHPExcel->getActiveSheet()->setCellValue('F6', 'OPS');
$objPHPExcel->getActiveSheet()->setCellValue('G6', 'BS');
$objPHPExcel->getActiveSheet()->setCellValue('H5', 'SETORAN MURNI');
$objPHPExcel->getActiveSheet()->setCellValue('I5', 'TAB SPAREPART');
$objPHPExcel->getActiveSheet()->setCellValue('J5', 'DENDA JAM');
$objPHPExcel->getActiveSheet()->setCellValue('K5', 'DP SPAREPART');
$objPHPExcel->getActiveSheet()->setCellValue('L5', 'BAYAR CICILAN');
$objPHPExcel->getActiveSheet()->setCellValue('L6', 'KS');
$objPHPExcel->getActiveSheet()->setCellValue('M6', 'S-PART');
$objPHPExcel->getActiveSheet()->setCellValue('N6', 'DP-KSO');
$objPHPExcel->getActiveSheet()->setCellValue('O6', 'HUT-LAMA');
$objPHPExcel->getActiveSheet()->setCellValue('P5', 'BAYAR');
$objPHPExcel->getActiveSheet()->setCellValue('P6', 'STIKER BANDARA & KEAMANAN');
$objPHPExcel->getActiveSheet()->setCellValue('Q6', 'CUCI');
$objPHPExcel->getActiveSheet()->setCellValue('R6', 'LAKA');
$objPHPExcel->getActiveSheet()->setCellValue('S5', 'HARUS SETOR');
$objPHPExcel->getActiveSheet()->setCellValue('T5', 'POTONGAN');
$objPHPExcel->getActiveSheet()->setCellValue('U5', 'SETOR CASH');
$objPHPExcel->getActiveSheet()->setCellValue('V5', 'KETEKORAN');
$objPHPExcel->getActiveSheet()->setCellValue('W5', 'SETORAN OPS');
$objPHPExcel->getActiveSheet()->setCellValue('X5', 'SHIFT');
$no = 1;
$starline = 8;
foreach ($financials as $finan) {
$ks = abs($finan->setoran_cash - ($finan->setoran_wajib + $finan->tabungan_sparepart + $finan->denda + $finan->cicilan_sparepart + $finan->cicilan_ks + $finan->biaya_cuci + $finan->iuran_laka + $finan->cicilan_dp_kso + $finan->cicilan_hutang_lama + $finan->cicilan_lain + $finan->hutang_dp_sparepart - $finan->potongan));
$bs = 'Tidak';
if ($ks <= $finan->cicilan_ks) {
$bs = 'Ya';
}
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $starline, $no);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $starline, '');
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $starline, $finan->nip);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $starline, $finan->name);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $starline, $finan->operasi_time);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $starline, $finan->kode);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $starline, $bs);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $starline, $finan->setoran_wajib);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $starline, $finan->tabungan_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(9, $starline, $finan->denda);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(10, $starline, $finan->hutang_dp_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(11, $starline, $finan->cicilan_ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(12, $starline, $finan->cicilan_sparepart);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(13, $starline, $finan->cicilan_dp_kso);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, $starline, $finan->cicilan_hutang_lama);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(15, $starline, $finan->cicilan_lain);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(16, $starline, $finan->biaya_cuci);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(17, $starline, $finan->iuran_laka);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(18, $starline, '=SUM(H' . $starline . ':R' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(19, $starline, $finan->potongan);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(20, $starline, $finan->setoran_cash);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(20, $starline, $finan->ks);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(21, $starline, '=(U' . $starline . '-(S' . $starline . '-T' . $starline . '))');
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(22, $starline, '=(U' . $starline . '-(Q' . $starline . '+R' . $starline . '))');
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(23, $starline, $finan->shift); //col X
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(24, $starline, $finan->shift_id);
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(14, $starline, $finan->cicilan_lain);
//hidden coloumn status operasi
//$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(25, $starline, $finan->operasi_status_id); //col Z
$no++;
$starline++;
}
$objPHPExcel->getActiveSheet()->mergeCells('A' . ($starline + 1) . ':G' . ($starline + 1) . '');
$objPHPExcel->getActiveSheet()->setCellValue('A' . ($starline + 1), 'TOTAL SETORAN ');
$objPHPExcel->getActiveSheet()->setCellValue('H' . ($starline + 1), '=SUM(H8:H' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('I' . ($starline + 1), '=SUM(I8:I' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('J' . ($starline + 1), '=SUM(J8:J' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('K' . ($starline + 1), '=SUM(K8:K' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('L' . ($starline + 1), '=SUM(L8:L' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('M' . ($starline + 1), '=SUM(M8:M' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('N' . ($starline + 1), '=SUM(N8:N' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('O' . ($starline + 1), '=SUM(O8:O' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('P' . ($starline + 1), '=SUM(P8:P' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('Q' . ($starline + 1), '=SUM(Q8:Q' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('R' . ($starline + 1), '=SUM(R8:R' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('S' . ($starline + 1), '=SUM(S8:S' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('T' . ($starline + 1), '=SUM(T8:T' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('U' . ($starline + 1), '=SUM(U8:U' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('V' . ($starline + 1), '=SUM(V8:V' . $starline . ')');
$objPHPExcel->getActiveSheet()->setCellValue('W' . ($starline + 1), '=SUM(W8:W' . $starline . ')');
$objPHPExcel->getActiveSheet()->getStyle('A5:X' . ($starline + 1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_HAIR);
$objPHPExcel->getActiveSheet()->getStyle('A5:X6')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A5:X' . ($starline + 1))->getBorders()->getOutline()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($starline + 1) . ':X' . ($starline + 1))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->setTitle('Laporan KAS ' . date('d', strtotime($startdate)) . ' - ' . $date);
$sheet_active++;
}
}
//end if gabungan
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(path('public') . 'Laporan-KAS-' . Pool::find(Auth::user()->pool_id)->pool_name . '.xls');
return Response::download(path('public') . 'Laporan-KAS-' . Pool::find(Auth::user()->pool_id)->pool_name . '.xls', 'Laporan-KAS-' . Pool::find(Auth::user()->pool_id)->pool_name . '.xls');
}
public function get_reportdailyjson($date = false)
{
if (!$date) {
$date = date('Y-m-d');
}
$timestamp = strtotime($date);
$report_daily = DB::table('financial_report_daily')->where_pool_id(Auth::user()->pool_id)->get();
$tanggalKSO = Myfungsi::fulldate($timestamp);
$returndata = array('tanggal' => $tanggalKSO, 'date' => $date);
return json_encode($returndata);
}
public function get_ExportJhoHarianPdf($date = false)
{
if (!$date) {
$date = date('Y-m-d');
}
$timestamp = strtotime($date);
$arrayschedule = array();
$schedule = Schedule::where('month', '=', date('n', $timestamp))->where('year', '=', date('Y', $timestamp))->get(array('id', 'fleet_id'));
foreach ($schedule as $sc) {
$arrayschedule[] = $sc->id;
}
$this->data['scheduleday'] = array();
if (is_array($arrayschedule) && !empty($arrayschedule)) {
$this->data['scheduleday'] = Scheduledate::join('schedules', 'schedules.id', '=', 'schedule_dates.schedule_id')->where_in('schedule_dates.schedule_id', $arrayschedule)->where('schedules.pool_id', '=', Auth::user()->pool_id)->where('schedule_dates.date', '=', date('j', $timestamp))->get(array('schedule_dates.id as id', 'schedule_dates.driver_id', 'schedules.fleet_id', 'schedule_dates.fg_check'));
}
$this->data['tanggal'] = Myfungsi::fulldate($timestamp);
/*
ob_start();
echo View::make('themes.modul.'.$this->report.'.scheduleharian',$this->data);
$content = ob_get_clean();
try
{
$html2pdf = new HTML2PDF('P', 'A4', 'en');
$html2pdf->pdf->SetDisplayMode('fullpage');
//$html2pdf->pdf->SetProtection(array('print'), 'spipu');
$html2pdf->writeHTML($content, isset($_GET['vuehtml']));
return Response::make($html2pdf->Output(), 200, array('Content-type' => 'application/pdf'));
}
catch(HTML2PDF_exception $e) {
echo $e;
exit;
}
*/
return View::make('themes.modul.' . $this->report . '.scheduleharian', $this->data);
}
