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;
}
function grafik_home_get()
{
$data = $this->m_laporan->get_all_graph();
$tanggal = array();
$sas_label = array();
$laks_label = array();
$sas = array();
$laks = array();
foreach ($data['jam_terbang'] as $hasil) {
$tanggal[] = indo_tgl_graph($hasil->tanggal);
$sas_label[] = (int) time_to_second($hasil->sas);
$laks_label[] = (int) time_to_second($hasil->laks);
$sas[] = (int) time_to_second($hasil->sas);
$laks[] = (int) time_to_second($hasil->laks);
}
$result['jam_terbang'] = array('tanggal' => $tanggal, 'sas' => $sas_label, 'laks' => $laks_label, 'sas_chart' => $sas, 'laks_chart' => $laks);
die(json_encode($result));
}
