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); }