public function get_rekapsetoran()
{
$shifts = Shift::all();
$pool_id = Auth::user()->pool_id;
$shiftoption = Koki::to_dropdown($shifts, 'id', 'shift');
$this->data['shifts'] = $shiftoption + array('all' => 'Gabungan');
$this->data['fleets'] = Kso::join('fleets', 'fleets.id', '=', 'ksos.fleet_id')->where('ksos.pool_id', '=', $pool_id)->where('ksos.actived', '=', 1)->get(array('ksos.id', 'ksos.fleet_id', 'fleets.taxi_number'));
return View::make('themes.modul.' . $this->views . '.rekapsetoranarmada', $this->data);
}
public function post_searchFleetkso()
{
$jsondata = Input::json();
$pool_id = Auth::user()->pool_id;
$fleets = Kso::join('fleets', 'fleets.id', '=', 'ksos.fleet_id')->where('fleets.taxi_number', 'LIKE', '%' . $jsondata->taxi_number . '%')->where('fleets.pool_id', '=', $pool_id)->order_by('fleets.taxi_number', 'asc')->get(array('fleets.taxi_number', 'ksos.id', 'ksos.kso_number'));
$fleetdata = array_map(function ($object) {
return $object->to_array();
}, $fleets);
$data['fleets'] = $fleetdata;
return json_encode($data);
}
public function post_report()
{
$tanggal = Input::get('date', date('Y-m-d'));
foreach (User::where('pool_id', '=', Auth::user()->pool_id)->get() as $u) {
//foreach (Fleetmodel::where('actived','=',1)->get() as $model) {
$ksos = Kso::join('anak_asuh', 'anak_asuh.fleet_id', '=', 'ksos.fleet_id')->join('fleets', 'fleets.id', '=', 'ksos.fleet_id')->where('ksos.pool_id', '=', Auth::user()->pool_id)->where('ksos.actived', '=', 1)->where('anak_asuh.status', '=', 1)->where('anak_asuh.user_id', '=', $u->id)->get(array('ksos.id', 'ksos.fleet_id', 'ksos.bravo_driver_id', 'ksos.charlie_driver_id', 'anak_asuh.user_id', 'fleets.fleet_model_id'));
if (!empty($ksos)) {
echo 'Bapak Asuh : ' . $u->fullname;
// echo '<br>Model : ' . $model->fleet_model;
echo '<table border=1>';
echo '<tr>';
echo '<td>No</td>';
echo '<td>Body</td>';
echo '<td>Pengemudi</td>';
echo '<td>NIP</td>';
echo '<td>Jadwal</td>';
for ($p = 2; $p >= 0; $p--) {
echo '<td>KS bulan ' . date("M, Y", strtotime($tanggal . " -" . $p . " month")) . '</td>';
}
echo '</tr>';
$no = 1;
foreach ($ksos as $x) {
echo '<tr>';
echo '<td>' . $no++ . '</td>';
echo '<td>' . Fleet::find($x->fleet_id)->taxi_number . '</td>';
echo '<td>' . Driver::find($x->bravo_driver_id)->name . '</td>';
echo '<td>' . Driver::find($x->bravo_driver_id)->nip . '</td>';
echo '<td></td>';
//query 3 ks bulan terakhir
for ($k = 2; $k >= 0; $k--) {
$manualquery = DB::query('select
cin.*,
cf.checkin_id,
MONTHNAME(cin.operasi_time) as monthname,
month(cin.operasi_time) as month,
year(cin.operasi_time) as year,
sum(if( cf.financial_type_id = 1, cf.amount, 0)) as setoran_wajib,
sum(if( cf.financial_type_id = 2, cf.amount, 0)) as tabungan_sparepart,
sum(if( cf.financial_type_id = 3, cf.amount, 0)) as denda,
sum(if( cf.financial_type_id = 4, cf.amount, 0)) as potongan,
sum(if( cf.financial_type_id = 5, cf.amount, 0)) as cicilan_sparepart,
sum(if( cf.financial_type_id = 6, cf.amount, 0)) as cicilan_ks,
sum(if( cf.financial_type_id = 7, cf.amount, 0)) as biaya_cuci,
sum(if( cf.financial_type_id = 8, cf.amount, 0)) as iuran_laka,
sum(if( cf.financial_type_id = 9, cf.amount, 0)) as cicilan_dp_kso,
sum(if( cf.financial_type_id = 10, cf.amount, 0)) as cicilan_hutang_lama,
sum(if( cf.financial_type_id = 11, cf.amount, 0)) as ks,
sum(if( cf.financial_type_id = 12, cf.amount, 0)) as cicilan_lain,
sum(if( cf.financial_type_id = 13, cf.amount, 0)) as hutang_dp_sparepart,
sum(if( cf.financial_type_id = 20, cf.amount, 0)) as setoran_cash,
sum(if( cf.financial_type_id = 21, cf.amount, 0)) as tabungan,
(sum(if( cf.financial_type_id = 6, cf.amount, 0))- sum(if( cf.financial_type_id = 11, cf.amount, 0))) as selisi_ks
from checkins cin left join checkin_financials cf on ( cin.id = cf.checkin_id )
where month(cin.operasi_time) = ? and
year(cin.operasi_time) = ? and
cin.driver_id = ?
group by YEAR(cin.operasi_time), MONTH(cin.operasi_time), cin.operasi_status_id, cin.driver_id', array(date("n", strtotime($tanggal . " -" . $k . " month")), date("Y", strtotime($tanggal . " -" . $k . " month")), $x->bravo_driver_id));
//var_dump($manualquery);
/*
$selisih_ks = DB::table('financial_report_monthly_driver_status')
->where('driver_id','=',$x->bravo_driver_id)
->where('month','=', date("n", strtotime($tanggal." -".$k." month")) )
->where('year', '=', date("Y", strtotime($tanggal." -".$k." month")) )
->sum(array('selisi_ks'));
echo '<td>'.$selisih_ks.'</td>';
*/
}
echo '</tr>';
echo '<tr>';
echo '<td></td>';
echo '<td></td>';
echo '<td>';
$xc = Driver::find($x->charlie_driver_id);
if ($xc) {
echo $xc->name;
} else {
echo '-';
}
echo '</td>';
echo '<td>';
if ($xc) {
echo $xc->nip;
} else {
echo '-';
}
echo '</td>';
echo '<td></td>';
//query 3 ks bulan terakhir
for ($z = 2; $z >= 0; $z--) {
$selisih_ks = DB::table('financial_report_monthly_driver_status_temp')->where('driver_id', '=', $x->charlie_driver_id)->where('month', '=', date("n", strtotime($tanggal . " -" . $z . " month")))->where('year', '=', date("Y", strtotime($tanggal . " -" . $z . " month")))->sum(array('selisi_ks'));
echo '<td>' . $selisih_ks . '</td>';
}
echo '</tr>';
}
echo '</table>';
// }
}
}
}
/**
* @doc
*
*/
public function get_downloadksoactive()
{
$pool_id = Auth::user()->pool_id;
$this->data['ksos'] = Kso::join('fleets as f', 'f.id', '=', 'ksos.fleet_id')->join('drivers as d', 'd.id', '=', 'ksos.bravo_driver_id')->where('ksos.pool_id', '=', Auth::user()->pool_id)->where('ksos.actived', '=', 1)->order_by('f.taxi_number', 'asc')->get();
return View::make('themes.modul.' . $this->report . '.listksoactive', $this->data);
}