function get_daily_program_report($date)
{
$sql = "select * from tb_kategori_daily_program order by id";
$result = $this->db->query($sql)->result();
foreach ($result as $key => $value) {
$sql_child = "select d.*, ff.id as id_ff, ff.id_daily_program, ff.callsign, CONCAT_WS(' ',k.kode, jl.kode) as excercise, ff.freq, k.warna, TIME_TO_SEC(TIMEDIFF(d.time_end, d.time_start))/60 as selisih, \r\n (TIME_TO_SEC(TIMEDIFF(d.time_start, '00:00:00'))/60)-(TIME_TO_SEC(TIMEDIFF((select awal from tb_daily_time where tanggal = '" . $date . "'), '00:00:00'))/60) as start \r\n from tb_daily_program d\r\n left join tb_flight_formasi ff on (ff.id_daily_program = d.id)\r\n left join tb_kode_latihan k on (ff.id_kode_latihan = k.id)\r\n left join tb_jenis_latihan jl on (ff.id_jenis_latihan = jl.id)\r\n where d.id_kategori = '" . $value->id . "' \r\n and d.tanggal = '" . $date . "' ORDER BY d.time_start";
//echo $sql_child."<br/>";
$child = $this->db->query($sql_child)->result();
$result[$key]->program = $child;
foreach ($child as $key2 => $value2) {
$sql_child2 = "select c.id, pf.kode_dragon, pf.nama as nama_fs, p.kode_tail, c.jam_ops_total,\r\n TIME_TO_SEC(c.jam_ops_total) as total_sec, pf.id as id_penerbang_fs, pb.id as id_penerbang_bs,\r\n pb.kode_dragon as kd_dragon_bs, pb.nama as nama_bs, pf.kode_nrp as nrp_front, pb.kode_nrp as nrp_back\r\n from tb_configuration c\r\n join tb_pesawat p on (c.id_pesawat = p.id)\r\n join tb_penerbang pf on (c.id_penerbang_fs = pf.id)\r\n left join tb_penerbang pb on (c.id_penerbang_bs = pb.id)\r\n where c.id_flight_formasi = '" . $value2->id_ff . "'\r\n ";
$child2 = $this->db->query($sql_child2)->result();
$result[$key]->program[$key2]->program = $child2;
foreach ($child2 as $key3 => $value3) {
$sql_child3a = "select warna, total_resiko from tb_hurt \r\n where id_configuration = '" . $value3->id . "' \r\n and id_penerbang = '" . $value3->id_penerbang_fs . "'";
$child3a = $this->db->query($sql_child3a)->result();
$result[$key]->program[$key2]->program[$key3]->hurt_fs = $child3a;
$sql_child3b = "select warna, total_resiko from tb_hurt \r\n where id_configuration = '" . $value3->id . "' \r\n and id_penerbang = '" . $value3->id_penerbang_bs . "'";
$child3b = $this->db->query($sql_child3b)->result();
$result[$key]->program[$key2]->program[$key3]->hurt_bs = $child3b;
// CEK BENTROK PENERBANG
$sql_child3c = "select count(*) as jumlah_bentrok \r\n from tb_configuration c\r\n join tb_flight_formasi ff on (c.id_flight_formasi = ff.id)\r\n join tb_daily_program dp on (ff.id_daily_program = dp.id)\r\n where c.id_penerbang_fs = '" . $value3->id_penerbang_fs . "'\r\n and ff.tanggal = '" . $date . "'\r\n and dp.id != '" . $value2->id . "'\r\n and \r\n ('" . $value2->time_start . "' between dp.time_start and dp.time_end\r\n or '" . $value2->time_end . "' between dp.time_start and dp.time_end)\r\n ";
//echo $sql_child3c; die;
$child3c = $this->db->query($sql_child3c)->row()->jumlah_bentrok;
$result[$key]->program[$key2]->program[$key3]->bentrok_fs = $child3c;
$sql_child3d = "select count(*) as jumlah_bentrok \r\n from tb_configuration c\r\n join tb_flight_formasi ff on (c.id_flight_formasi = ff.id)\r\n join tb_daily_program dp on (ff.id_daily_program = dp.id)\r\n where c.id_penerbang_bs = '" . $value3->id_penerbang_bs . "'\r\n and ff.tanggal = '" . $date . "'\r\n and dp.id != '" . $value2->id . "'\r\n and \r\n ('" . $value2->time_start . "' between dp.time_start and dp.time_end\r\n or '" . $value2->time_end . "' between dp.time_start and dp.time_end)\r\n ";
//echo $sql_child3d; die;
$child3d = $this->db->query($sql_child3d)->row()->jumlah_bentrok;
$result[$key]->program[$key2]->program[$key3]->bentrok_bs = $child3d;
}
$sql_child3 = "select SUM(TIME_TO_SEC(c.jam_ops_total)) as total\r\n from tb_configuration c\r\n join tb_pesawat p on (c.id_pesawat = p.id)\r\n join tb_penerbang pf on (c.id_penerbang_fs = pf.id)\r\n left join tb_penerbang pb on (c.id_penerbang_bs = pb.id)\r\n where c.id_flight_formasi = '" . $value2->id_ff . "'\r\n ";
$result[$key]->program[$key2]->total_ops = $this->db->query($sql_child3)->row()->total;
$start = time_to_second($value2->time_start) + 60;
$sql_child4 = "select count(*) as jumlah\r\n from tb_daily_program \r\n where '" . time_formation(sec_to_time($start)) . ':00' . "' between time_start and time_end\r\n and tanggal = '" . $value2->tanggal . "' \r\n and id_kategori = '" . $value->id . "'\r\n and id != '" . $value2->id . "' \r\n and (time_start < '" . time_formation(sec_to_time($start)) . ':00' . "' or id < '" . $value2->id . "')\r\n ";
$result[$key]->program[$key2]->bentrok = $this->db->query($sql_child4)->row()->jumlah;
}
}
//die(json_encode($result));
return $result;
}
echo time_formation(sec_to_time($nig_dual_total));
?>
</td>
<td align="center"><?php
echo time_formation($flight_total);
?>
</td>
<td align="center"><?php
echo time_formation(sec_to_time($first_total));
?>
</td>
<td align="center"><?php
echo time_formation(sec_to_time($inst_sim_total));
?>
</td>
<td align="center"><?php
echo time_formation(sec_to_time($inst_act_total));
?>
</td>
<td align="center"><?php
echo time_formation(sec_to_time($inst_fly_day));
?>
</td>
<td align="center"><?php
echo time_formation(sec_to_time($inst_fly_night));
?>
</td>
</tr>
</tbody>
<tfoot></tfoot>
</table>
?>
</td>
<td align="center"><?php
echo time_formation(sec_to_time($value->total_yll));
?>
</td>
<td align="center"><?php
echo time_formation(sec_to_time($value->total_jet));
?>
</td>
<td align="center"><?php
echo time_formation(sec_to_time($value->total_propeler));
?>
</td>
<td align="center"><?php
echo time_formation(sec_to_time($value->total_jet + $value->total_propeler));
?>
</td>
<td><?php
echo $value->jabatan;
?>
</td>
</tr>
<?php
}
?>
</tbody>
</table>
<table>
<tr>
<td colspan="14"></td>
<tr>
<td colspan="2" align="center"><b>TOTAL</b></td>
<td align="center"><?php
echo time_formation($total_rencana);
?>
</td>
<td align="center"><?php
echo time_formation(sec_to_time($total_bln_ini));
?>
</td>
<td align="center"><?php
echo time_formation(sec_to_time($total_bln_bf));
?>
</td>
<td align="center"><?php
echo time_formation(sec_to_time($total_bln));
?>
</td>
<td align="center"><?php
echo round2Two($persen_tercapai);
?>
%</td>
<td></td>
</tr>
</tfoot>
</table>
<table>
<tr>
<td colspan="5"></td>
<td colspan="3" align="center">Komandan Skadron Udara 3</td>
</tr>
?>
</td>
<td align="center"><?php
echo time_formation(sec_to_time(abs($ttl_bulan)));
?>
</td>
<td align="center"><?php
echo $start_pm_thn < 0 ? '-' : '';
?>
</td>
<td align="center"><?php
echo time_formation(sec_to_time(abs($start_pm_thn)));
?>
</td>
<td align="center"><?php
echo time_formation(sec_to_time(abs($ttl_thn_yll)));
?>
</td>
<td align="center"><?php
echo $value['sorties'];
?>
</td>
<td align="center"><?php
echo $sorties;
?>
</td>
<td align="center"><?php
echo $ttl_ytll;
?>
</td>
</tr>