';
return response($html);
}
public function getrankinglkp2kebun(Request $request){
$data = DB::table('q_ranking_lkp2_kebun')
->select('kebun', 'ha_serah_tanah', 'ha_tanam_kepras',
DB::raw('CASE WHEN ha_serah_tanah > 0 THEN ROUND(ha_tanam_kepras / ha_serah_tanah * 100, 2) ELSE 0 END as prosentase'))
->orderByDesc('prosentase')
->where('jenis_kate', 'PC')
->get();
$data2 = DB::table('q_ranking_lkp2_kebun')
->select('kebun', 'ha_serah_tanah', 'ha_tanam_kepras',
DB::raw('CASE WHEN ha_serah_tanah > 0 THEN ROUND(ha_tanam_kepras / ha_serah_tanah * 100, 2) ELSE 0 END as prosentase'))
->orderByDesc('prosentase')
->where('jenis_kate', 'RC')
->get();
$data3 = DB::table('q_ranking_lkp2_kebun')
->select(
'kebun',
DB::raw('SUM(ha_serah_tanah) as total_ha_serah_tanah'),
DB::raw('SUM(ha_tanam_kepras) as total_ha_tanam_kepras'),
DB::raw('CASE WHEN SUM(ha_serah_tanah) > 0 THEN ROUND(SUM(ha_tanam_kepras) / SUM(ha_serah_tanah) * 100, 2) ELSE 0 END as total_prosentase')
)
->groupBy('kebun')
->orderByDesc('total_prosentase')
->get();
// Tab navigation for PC, RC, and Total
$html = '
';
return response($html);
}
public function getrankinglkp2rayon(Request $request){
$data = DB::table('q_ranking_lkp2_rayon')
->select('rayon', 'ha_serah_tanah', 'ha_tanam_kepras',
DB::raw('CASE WHEN ha_serah_tanah > 0 THEN ROUND(ha_tanam_kepras / ha_serah_tanah * 100, 2) ELSE 0 END as prosentase'))
->orderByDesc('prosentase')
->where('jenis_kate', 'PC')
->get();
$data2 = DB::table('q_ranking_lkp2_rayon')
->select('rayon', 'ha_serah_tanah', 'ha_tanam_kepras',
DB::raw('CASE WHEN ha_serah_tanah > 0 THEN ROUND(ha_tanam_kepras / ha_serah_tanah * 100, 2) ELSE 0 END as prosentase'))
->orderByDesc('prosentase')
->where('jenis_kate', 'RC')
->get();
$data3 = DB::table('q_ranking_lkp2_rayon')
->select(
'rayon',
DB::raw('SUM(ha_serah_tanah) as total_ha_serah_tanah'),
DB::raw('SUM(ha_tanam_kepras) as total_ha_tanam_kepras'),
DB::raw('CASE WHEN SUM(ha_serah_tanah) > 0 THEN ROUND(SUM(ha_tanam_kepras) / SUM(ha_serah_tanah) * 100, 2) ELSE 0 END as total_prosentase')
)
->groupBy('rayon')
->orderByDesc('total_prosentase')
->get();
// Tab navigation for Total, PC, and RC
$html = '
';
return response($html);
}
public function getrankinglkp2afdeling(){
$getlevel = DB::table('tb_groups')
->select('level')
->where('group_id',\Session::get('gid'))
->first();
$data = DB::table('q_ranking_lkp2_afd')
->select('kode_afd', 'nama_asman', 'ha_serah_tanah', 'ha_tanam_kepras',
DB::raw('CASE WHEN ha_serah_tanah > 0 THEN ROUND(ha_tanam_kepras / ha_serah_tanah * 100, 2) ELSE 0 END as prosentase'))
->orderByDesc('prosentase')
->where('jenis_kate', 'PC')
->when($getlevel->level == 3, function ($query) {
return $query->where('kode_kebun', substr(\Session::get('fid'), 0, 4));
})
->when($getlevel->level == 4, function ($query) {
return $query->where('kode_kebun', substr(\Session::get('fid'), 0, 6));
})
->get();
$data2 = DB::table('q_ranking_lkp2_afd')
->select('kode_afd', 'nama_asman', 'ha_serah_tanah', 'ha_tanam_kepras',
DB::raw('CASE WHEN ha_serah_tanah > 0 THEN ROUND(ha_tanam_kepras / ha_serah_tanah * 100, 2) ELSE 0 END as prosentase'))
->orderByDesc('prosentase')
->where('jenis_kate', 'RC')
->when($getlevel->level == 3, function ($query) {
return $query->where('kode_kebun', substr(\Session::get('fid'), 0, 4));
})
->when($getlevel->level == 4, function ($query) {
return $query->where('kode_kebun', substr(\Session::get('fid'), 0, 6));
})
->get();
$data4 = DB::table('q_ranking_lkp2_afd')
->select(
'kode_afd',
'nama_asman',
DB::raw('SUM(ha_serah_tanah) as total_ha_serah_tanah'),
DB::raw('SUM(ha_tanam_kepras) as total_ha_tanam_kepras'),
DB::raw('CASE WHEN SUM(ha_serah_tanah) > 0 THEN ROUND(SUM(ha_tanam_kepras) / SUM(ha_serah_tanah) * 100, 2) ELSE 0 END as total_prosentase')
)
->when($getlevel->level == 3, function ($query) {
return $query->where('kode_kebun', substr(\Session::get('fid'), 0, 4));
})
->when($getlevel->level == 4, function ($query) {
return $query->where('kode_kebun', substr(\Session::get('fid'), 0, 6));
})
->groupBy('kode_afd')
->orderByDesc('total_prosentase')
->get();
// Tab navigation for Total, PC, and RC
$html = '
Total LKP Afdeling
Ranking
Nama Asman
Ha Sasaran
Ha Tanam/Kepras
% Tanam/Kepras
';
$no = 1;
foreach ($data4 as $row) {
if ($row->total_prosentase >= 100) {
$bgcolor = '#28a745'; $color = '#fff';
} elseif ($row->total_prosentase >= 90) {
$bgcolor = '#ffc107'; $color = '#000';
} else {
$bgcolor = '#dc3545'; $color = '#fff';
}
$html .= '
' . $no++ . '
' . htmlspecialchars($row->nama_asman) . '
' . number_format($row->total_ha_serah_tanah, 2) . '
' . number_format($row->total_ha_tanam_kepras, 2) . '
' . number_format($row->total_prosentase, 2) . '%
';
}
$html .= '
Ranking LKP Afdeling (PC)
Ranking
Kode Afdeling
Nama Asman
Ha Sasaran
Ha Tanam/Kepras
% Tanam/Kepras
Keterangan
';
$no = 1;
foreach ($data as $row) {
$keterangan = '';
if ($row->ha_tanam_kepras > $row->ha_serah_tanah) {
$keterangan = 'Cek ulang inputan ';
}
if ($row->prosentase >= 100) {
$bgcolor = '#28a745'; $color = '#fff';
} elseif ($row->prosentase >= 90) {
$bgcolor = '#ffc107'; $color = '#000';
} else {
$bgcolor = '#dc3545'; $color = '#fff';
}
$html .= '
' . $no++ . '
' . htmlspecialchars($row->kode_afd) . '
' . htmlspecialchars($row->nama_asman) . '
' . number_format($row->ha_serah_tanah, 2) . '
' . number_format($row->ha_tanam_kepras, 2) . '
' . number_format($row->prosentase, 2) . '%
' . $keterangan . '
';
}
$html .= '
Ranking LKP Afdeling (RC)
Ranking
Kode Afdeling
Nama Asman
Ha Sasaran
Ha Tanam/Kepras
% Tanam/Kepras
Keterangan
';
$no = 1;
foreach ($data2 as $row) {
$keterangan = '';
if ($row->ha_tanam_kepras > $row->ha_serah_tanah) {
$keterangan = 'Cek ulang inputan ';
}
if ($row->prosentase >= 100) {
$bgcolor = '#28a745'; $color = '#fff';
} elseif ($row->prosentase >= 90) {
$bgcolor = '#ffc107'; $color = '#000';
} else {
$bgcolor = '#dc3545'; $color = '#fff';
}
$html .= '
' . $no++ . '
' . htmlspecialchars($row->kode_afd) . '
' . htmlspecialchars($row->nama_asman) . '
' . number_format($row->ha_serah_tanah, 2) . '
' . number_format($row->ha_tanam_kepras, 2) . '
' . number_format($row->prosentase, 2) . '%
' . $keterangan . '
';
}
$html .= '
';
return response($html);
}
public function getSasaran2526KebunPopmie()
{
$data = DB::table('TX_TOTAL_KSO')
->join('master_petak', 'TX_TOTAL_KSO.id_lahan', '=', 'master_petak.lahan_id')
->join('master_afd', 'master_petak.afd_new', '=', 'master_afd.kode_afd')
->join('master_rayon', 'master_afd.kode_rayon', '=', 'master_rayon.kode_rayon')
->join('master_kebun', 'master_rayon.kode_kebun', '=', 'master_kebun.kode_kebun')
->join('master_kategori', 'master_petak.kategori_n', '=', 'master_kategori.kat_lahan')
->join('master_sasaran2526_kebun_popmie as a', 'a.kode_kebun', '=', 'master_kebun.kode_kebun')
->select(
'master_kebun.nama_kebun',
'a.ha_luas_sasaran',
DB::raw('SUM(TX_TOTAL_KSO.ha) as luas_real'),
DB::raw('ROUND(SUM(TX_TOTAL_KSO.ha)/a.ha_luas_sasaran*100,2) as persen_luas'),
'a.ton_tebu_sasaran',
DB::raw("'' as real_ton_tebu"),
DB::raw("0. as persen_ton_tebu"),
'a.protas_sasaran',
DB::raw("'' as real_protas"),
DB::raw("0. as persen_protas")
)
->where('TX_TOTAL_KSO.pekerjaan', 'serah_tanah')
->where('TX_TOTAL_KSO.tgl', '!=', '0000-00-00')
->whereBetween('TX_TOTAL_KSO.tgl', ['2024-12-01', date('Y-m-d')])
->groupBy('master_kebun.kode_kebun')
->get();
$html = '';
$html .= '
Nama Kebun
Luas Sasaran (Ha)
Luas Realisasi (Ha)
% Luas
Tebu Sasaran (Ton)
Tebu Realisasi (Ton)
% Tebu
Protas Sasaran (Ton/Ha)
Protas Realisasi (Ton/Ha)
% Protas
';
$total_luas = 0;
$total_luas_real = 0;
$total_tebu = 0;
$total_protas = 0;
$count = 0;
foreach ($data as $row) {
$html .= '';
$html .= '' . htmlspecialchars($row->nama_kebun) . ' ';
// Luas columns
$html .= '' . number_format($row->ha_luas_sasaran, 2) . ' ';
$html .= '' . number_format($row->luas_real, 2) . ' ';
// Tambahan progress bar persen_tebu_real
$persen_tebu_real = isset($row->persen_luas) ? (float)$row->persen_luas : 0;
$bar_color = $persen_tebu_real < 50 ? '#dc3545' : '#28a745';
$html .= '
' . number_format($persen_tebu_real, 2) . '%
';
// Tebu columns
$html .= '' . number_format($row->ton_tebu_sasaran, 2) . ' ';
$html .= '' . $row->real_ton_tebu . ' ';
// Tambahan progress bar persen_ton_tebu
$persen_ton_tebu = isset($row->persen_ton_tebu) ? (float)$row->persen_ton_tebu : 0;
$bar_color_tebu = $persen_ton_tebu < 50 ? '#dc3545' : '#28a745';
$html .= '
' . number_format($persen_ton_tebu, 2) . '%
';
// Protas columns
$html .= '' . number_format($row->protas_sasaran, 2) . ' ';
$html .= '' . $row->real_protas . ' ';
// Tambahan progress bar persen_protas
$persen_protas = isset($row->persen_protas) ? (float)$row->persen_protas : 0;
$bar_color_protas = $persen_protas < 50 ? '#dc3545' : '#28a745';
$html .= '
' . number_format($persen_protas, 2) . '%
';
$html .= ' ';
$total_luas += $row->ha_luas_sasaran;
$total_luas_real += $row->luas_real;
$total_tebu += $row->ton_tebu_sasaran;
$total_protas += $row->protas_sasaran;
$count++;
}
if ($count > 0) {
$avg_protas = $total_luas > 0 ? $total_tebu / $total_luas : 0;
$avg_persen_luas = $total_luas > 0 ? $total_luas_real / $total_luas * 100 : 0;
$html .= '';
$html .= 'Total ';
$html .= '' . number_format($total_luas, 2) . ' ';
$html .= '' . number_format($total_luas_real, 2) . ' ';
$html .= '
' . number_format($avg_persen_luas, 2) . '%
';
$html .= '' . number_format($total_tebu, 2) . ' ';
$html .= '- ';
$html .= '- ';
$html .= '' . number_format($avg_protas, 2) . ' ';
$html .= '- ';
$html .= '- ';
$html .= ' ';
}
if (count($data) == 0) {
$html .= 'Data tidak ditemukan.
';
return response($html);
}
public function getGrafikTanamBulananKebun(Request $request)
{
$kode_kebun = $request->input('kode_kebun', 'SK09');
$kode_kat = $request->input('kode_kat', 'PC');
$tgl_awal = $request->input('tgl_awal', '2024-09-01');
$tgl_akhir = $request->input('tgl_akhir', '2025-09-10');
if (empty($kode_kebun)) {
$total = 1;
}else{
$total = 1;
}
// Ambil data dari stored procedure
$data = DB::select('call sp_rekap_tanam_bulantanam_grafik_kebun(?,?,?,?,?)', [
$kode_kebun, $kode_kat, $tgl_awal, $tgl_akhir, $total
]);
// Proses data untuk grafik Highcharts
$categories = [];
$ha_serah_tanah = [];
$ha_tanam_kepras = [];
foreach ($data as $row) {
$categories[] = $row->bulan_tanam;
$ha_serah_tanah[] = round((float) $row->ha_serah_tanah, 0);
$ha_tanam_kepras[] = round((float) $row->ha_tanam_kepras, 0);
}
// Format data untuk Highcharts
// Pisahkan TOTAL KSO dari data, tampilkan di text sendiri
$filtered = [];
$totalKsoIndex = null;
foreach ($categories as $idx => $cat) {
if (strtoupper(trim($cat)) === 'TOTAL') {
$totalKsoIndex = $idx;
} else {
$filtered['categories'][] = $cat;
$filtered['ha_serah_tanah'][] = $ha_serah_tanah[$idx];
$filtered['ha_tanam_kepras'][] = $ha_tanam_kepras[$idx];
}
}
$chartOptions = [
'chart' => [
'type' => 'column',
'backgroundColor' => [
'linearGradient' => [0, 0, 0, 400],
'stops' => [
[0, '#f4f8ff'],
[1, '#eaf1fb']
]
],
'style' => [
'fontFamily' => 'Segoe UI, Arial, sans-serif'
]
],
'title' => [
'text' => 'Realisasi Berdasarkan Bulan Tanam (Kebun)',
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold',
'fontSize' => '20px',
'textShadow' => '0 2px 8px #d1ecf1'
]
],
'xAxis' => [
'categories' => $filtered['categories'] ?? [],
'title' => ['text' => 'Bulan Tanam'],
'labels' => [
'style' => [
'color' => '#1e3c72',
'fontWeight' => 'bold',
'fontSize' => '13px'
]
],
'lineColor' => '#1e90ff',
'tickColor' => '#1e90ff'
],
'yAxis' => [
'min' => 0,
'title' => [
'text' => 'Hektar',
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
],
'gridLineColor' => '#e0eafc',
'labels' => [
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
]
],
'legend' => [
'backgroundColor' => '#fff',
'borderColor' => '#d1ecf1',
'borderWidth' => 1,
'itemStyle' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
],
'tooltip' => [
'backgroundColor' => 'rgba(50,70,150,0.95)',
'style' => [
'color' => '#fff',
'fontSize' => '13px'
],
'borderRadius' => 10,
'borderWidth' => 2,
'shadow' => true,
'headerFormat' => '
{point.key} ',
'pointFormat' => ' {series.name}:
{point.y} '
],
'plotOptions' => [
'column' => [
'borderRadius' => 6,
'pointPadding' => 0.15,
'groupPadding' => 0.08,
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#222',
'textOutline' => 'none'
],
'backgroundColor' => 'rgba(255,255,255,0.7)',
'borderRadius' => 4,
'padding' => 2
]
]
],
'colors' => [
['linearGradient' => [0, 0, 0, 300], 'stops' => [[0, '#43e97b'], [1, '#38f9d7']]],
['linearGradient' => [0, 0, 0, 300], 'stops' => [[0, '#f7971e'], [1, '#ffd200']]]
],
'series' => [
[
'name' => 'Ha Serah Tanah',
'data' => $filtered['ha_serah_tanah'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#43e97b'],
[1, '#38f9d7']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#28a745'
]
]
],
[
'name' => 'Ha Tanam/Kepras',
'data' => $filtered['ha_tanam_kepras'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#f7971e'],
[1, '#ffd200']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#ffc107'
]
]
]
]
];
// Tambahkan info TOTAL KSO sebagai text HTML yang menarik
$totalKsoHtml = '';
if ($totalKsoIndex !== null) {
$totalKsoHtml = '
TOTAL —
Ha Serah Tanah: ' . number_format($ha_serah_tanah[$totalKsoIndex]) . ' |
Ha Tanam/Kepras: ' . number_format($ha_tanam_kepras[$totalKsoIndex]) . '
';
}
// Kirim juga totalKsoHtml jika ingin dipakai di frontend
$this->data['total_kso_html'] = $totalKsoHtml;
return response()->json([
'status' => 'success',
'totalKsoHtml' => $this->data['total_kso_html'],
'chartOptions' => $chartOptions
]);
}
public function getGrafikTanamBulananRayon(Request $request)
{
$kode_rayon = $request->input('kode_rayon', 'SK09K1');
$kode_kat = $request->input('kode_kat', 'PC');
$tgl_awal = $request->input('tgl_awal', '2024-09-01');
$tgl_akhir = $request->input('tgl_akhir', '2025-09-10');
// Ambil data dari stored procedure
$data = DB::select('call sp_rekap_tanam_bulantanam_grafik_rayon(?,?,?,?)', [
$kode_rayon, $kode_kat, $tgl_awal, $tgl_akhir
]);
// Proses data untuk grafik Highcharts
$categories = [];
$ha_serah_tanah = [];
$ha_tanam_kepras = [];
foreach ($data as $row) {
$categories[] = $row->bulan_tanam;
$ha_serah_tanah[] = round((float) $row->ha_serah_tanah, 0);
$ha_tanam_kepras[] = round((float) $row->ha_tanam_kepras, 0);
}
// Format data untuk Highcharts
// Pisahkan TOTAL KSO dari data, tampilkan di text sendiri
$filtered = [];
$totalKsoIndex = null;
foreach ($categories as $idx => $cat) {
if (strtoupper(trim($cat)) === 'TOTAL') {
$totalKsoIndex = $idx;
} else {
$filtered['categories'][] = $cat;
$filtered['ha_serah_tanah'][] = $ha_serah_tanah[$idx];
$filtered['ha_tanam_kepras'][] = $ha_tanam_kepras[$idx];
}
}
$chartOptions = [
'chart' => [
'type' => 'column',
'backgroundColor' => [
'linearGradient' => [0, 0, 0, 400],
'stops' => [
[0, '#f4f8ff'],
[1, '#eaf1fb']
]
],
'style' => [
'fontFamily' => 'Segoe UI, Arial, sans-serif'
]
],
'title' => [
'text' => 'Realisasi Berdasarkan Bulan Tanam (Rayon)',
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold',
'fontSize' => '20px',
'textShadow' => '0 2px 8px #d1ecf1'
]
],
'xAxis' => [
'categories' => $filtered['categories'] ?? [],
'title' => ['text' => 'Bulan Tanam'],
'labels' => [
'style' => [
'color' => '#1e3c72',
'fontWeight' => 'bold',
'fontSize' => '13px'
]
],
'lineColor' => '#1e90ff',
'tickColor' => '#1e90ff'
],
'yAxis' => [
'min' => 0,
'title' => [
'text' => 'Hektar',
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
],
'gridLineColor' => '#e0eafc',
'labels' => [
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
]
],
'legend' => [
'backgroundColor' => '#fff',
'borderColor' => '#d1ecf1',
'borderWidth' => 1,
'itemStyle' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
],
'tooltip' => [
'backgroundColor' => 'rgba(50,70,150,0.95)',
'style' => [
'color' => '#fff',
'fontSize' => '13px'
],
'borderRadius' => 10,
'borderWidth' => 2,
'shadow' => true,
'headerFormat' => '
{point.key} ',
'pointFormat' => ' {series.name}:
{point.y} '
],
'plotOptions' => [
'column' => [
'borderRadius' => 6,
'pointPadding' => 0.15,
'groupPadding' => 0.08,
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#222',
'textOutline' => 'none'
],
'backgroundColor' => 'rgba(255,255,255,0.7)',
'borderRadius' => 4,
'padding' => 2
]
]
],
'colors' => [
['linearGradient' => [0, 0, 0, 300], 'stops' => [[0, '#43e97b'], [1, '#38f9d7']]],
['linearGradient' => [0, 0, 0, 300], 'stops' => [[0, '#f7971e'], [1, '#ffd200']]]
],
'series' => [
[
'name' => 'Ha Serah Tanah',
'data' => $filtered['ha_serah_tanah'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#43e97b'],
[1, '#38f9d7']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#28a745'
]
]
],
[
'name' => 'Ha Tanam/Kepras',
'data' => $filtered['ha_tanam_kepras'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#f7971e'],
[1, '#ffd200']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#ffc107'
]
]
]
]
];
// Tambahkan info TOTAL KSO sebagai text HTML yang menarik
$totalKsoHtml = '';
if ($totalKsoIndex !== null) {
$totalKsoHtml = '
TOTAL —
Ha Serah Tanah: ' . number_format($ha_serah_tanah[$totalKsoIndex]) . ' |
Ha Tanam/Kepras: ' . number_format($ha_tanam_kepras[$totalKsoIndex]) . '
';
}
// Kirim juga totalKsoHtml jika ingin dipakai di frontend
$this->data['total_kso_html'] = $totalKsoHtml;
return response()->json([
'status' => 'success',
'totalKsoHtml' => $this->data['total_kso_html'],
'chartOptions' => $chartOptions
]);
}
public function getGrafikDroneKebunPeriode(Request $request)
{
$tahun = $request->input('tahun', 2025);
$periode = $request->input('periode', '10A');
$data = DB::select('call sp_rekap_drone_kebun_periode_dinamis(?, ?)', [$tahun, $periode]);
$categories = [];
$jumlah_petak = [];
$jumlah_petak_sd = [];
$drone = [];
foreach ($data as $row) {
$categories[] = $row->nama_kebun;
$jumlah_petak[] = (int) $row->jumlah_petak_tanamkepras;
$jumlah_petak_sd[] = (int) $row->{'jumlah_petak_tanamkepras_sd_' . $periode};
$drone[] = (int) $row->{'drone_' . $periode};
}
// Filter out "TOTAL KSO" from categories and corresponding data, but keep its values for display
$filtered = [];
$totalKsoIndex = null;
foreach ($categories as $idx => $cat) {
if (strtoupper(trim($cat)) === 'TOTAL KSO') {
$totalKsoIndex = $idx;
} else {
$filtered['categories'][] = $cat;
$filtered['jumlah_petak'][] = $jumlah_petak[$idx];
$filtered['jumlah_petak_sd'][] = $jumlah_petak_sd[$idx];
$filtered['drone'][] = $drone[$idx];
}
}
$chartOptions = [
'chart' => [
'type' => 'column',
'backgroundColor' => [
'linearGradient' => [0, 0, 0, 400],
'stops' => [
[0, '#f4f8ff'],
[1, '#eaf1fb']
]
],
'style' => [
'fontFamily' => 'Segoe UI, Arial, sans-serif'
]
],
'title' => [
'text' => 'Grafik Monitoring Foto Drone Periodik - Kebun',
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold',
'fontSize' => '20px',
'textShadow' => '0 2px 8px #d1ecf1'
]
],
'xAxis' => [
'categories' => $filtered['categories'] ?? [],
'title' => ['text' => 'Nama Kebun'],
'labels' => [
'style' => [
'color' => '#1e3c72',
'fontWeight' => 'bold',
'fontSize' => '13px'
]
],
'lineColor' => '#1e90ff',
'tickColor' => '#1e90ff'
],
'yAxis' => [
'min' => 0,
'title' => [
'text' => 'Jumlah',
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
],
'gridLineColor' => '#e0eafc',
'labels' => [
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
]
],
'legend' => [
'backgroundColor' => '#fff',
'borderColor' => '#d1ecf1',
'borderWidth' => 1,
'itemStyle' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
],
'tooltip' => [
'backgroundColor' => 'rgba(50,70,150,0.95)',
'style' => [
'color' => '#fff',
'fontSize' => '13px'
],
'borderRadius' => 10,
'borderWidth' => 2,
'shadow' => true,
'headerFormat' => '
{point.key} ',
'pointFormat' => ' {series.name}:
{point.y} '
],
'plotOptions' => [
'column' => [
'borderRadius' => 6,
'pointPadding' => 0.15,
'groupPadding' => 0.08,
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#222',
'textOutline' => 'none'
],
'backgroundColor' => 'rgba(255,255,255,0.7)',
'borderRadius' => 4,
'padding' => 2
]
]
],
'colors' => [
['linearGradient' => [0, 0, 0, 300], 'stops' => [[0, '#43e97b'], [1, '#38f9d7']]],
['linearGradient' => [0, 0, 0, 300], 'stops' => [[0, '#f7971e'], [1, '#ffd200']]],
['linearGradient' => [0, 0, 0, 300], 'stops' => [[0, '#f857a6'], [1, '#ff5858']]]
],
'series' => [
[
'name' => 'Jml Petak Tanam/Kepras',
'data' => $filtered['jumlah_petak'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#43e97b'],
[1, '#38f9d7']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#28a745'
]
]
],
[
'name' => 'Jml Petak Tanam/Kepras SD ' . $periode,
'data' => $filtered['jumlah_petak_sd'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#f7971e'],
[1, '#ffd200']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#ffc107'
]
]
],
[
'name' => 'Drone ' . $periode,
'data' => $filtered['drone'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#f857a6'],
[1, '#ff5858']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#dc3545'
]
]
]
]
];
// Tambahkan info TOTAL KSO sebagai text HTML yang menarik
$totalKsoHtml = '';
if ($totalKsoIndex !== null) {
$totalKsoHtml = '
TOTAL —
Jml Petak: ' . number_format($jumlah_petak[$totalKsoIndex]) . ' |
Jml Petak SD ' . htmlspecialchars($periode) . ': ' . number_format($jumlah_petak_sd[$totalKsoIndex]) . ' |
Drone ' . htmlspecialchars($periode) . ': ' . number_format($drone[$totalKsoIndex]) . '
';
}
// Kirim juga totalKsoHtml jika ingin dipakai di frontend
$this->data['total_kso_html'] = $totalKsoHtml;
return response()->json([
'status' => 'success',
'total_kso_html' => $totalKsoHtml,
'chartOptions' => $chartOptions
]);
}
public function getGrafikDroneRayonPeriode(Request $request)
{
$tahun = $request->input('tahun', 2025);
$periode = $request->input('periode', '10A');
$kebun = $request->input('kebun', 'NULL');
$data = DB::select('call sp_rekap_drone_rayon_periode_dinamis(?, ?, ?)', [$tahun, $periode, $kebun]);
$categories = [];
$jumlah_petak = [];
$jumlah_petak_sd = [];
$drone = [];
foreach ($data as $row) {
$categories[] = $row->nama_rayon;
$jumlah_petak[] = (int) $row->jumlah_petak_tanamkepras;
$jumlah_petak_sd[] = (int) $row->{'jumlah_petak_tanamkepras_sd_' . $periode};
$drone[] = (int) $row->{'drone_' . $periode};
}
// Filter out "TOTAL KSO" from categories and corresponding data, but keep its values for display
$filtered = [];
$totalKsoIndex = null;
foreach ($categories as $idx => $cat) {
if (strtoupper(trim($cat)) === 'TOTAL') {
$totalKsoIndex = $idx;
} else {
$filtered['categories'][] = $cat;
$filtered['jumlah_petak'][] = $jumlah_petak[$idx];
$filtered['jumlah_petak_sd'][] = $jumlah_petak_sd[$idx];
$filtered['drone'][] = $drone[$idx];
}
}
$chartOptions = [
'chart' => [
'type' => 'column',
'backgroundColor' => [
'linearGradient' => [0, 0, 0, 400],
'stops' => [
[0, '#f4f8ff'],
[1, '#eaf1fb']
]
],
'style' => [
'fontFamily' => 'Segoe UI, Arial, sans-serif'
]
],
'title' => [
'text' => 'Grafik Monitoring Foto Drone Periodik - Rayon',
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold',
'fontSize' => '20px',
'textShadow' => '0 2px 8px #d1ecf1'
]
],
'xAxis' => [
'categories' => $filtered['categories'] ?? [],
'title' => ['text' => 'Nama Rayon'],
'labels' => [
'style' => [
'color' => '#1e3c72',
'fontWeight' => 'bold',
'fontSize' => '13px'
]
],
'lineColor' => '#1e90ff',
'tickColor' => '#1e90ff'
],
'yAxis' => [
'min' => 0,
'title' => [
'text' => 'Jumlah',
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
],
'gridLineColor' => '#e0eafc',
'labels' => [
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
]
],
'legend' => [
'backgroundColor' => '#fff',
'borderColor' => '#d1ecf1',
'borderWidth' => 1,
'itemStyle' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
],
'tooltip' => [
'backgroundColor' => 'rgba(50,70,150,0.95)',
'style' => [
'color' => '#fff',
'fontSize' => '13px'
],
'borderRadius' => 10,
'borderWidth' => 2,
'shadow' => true,
'headerFormat' => '
{point.key} ',
'pointFormat' => ' {series.name}:
{point.y} '
],
'plotOptions' => [
'column' => [
'borderRadius' => 6,
'pointPadding' => 0.15,
'groupPadding' => 0.08,
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#222',
'textOutline' => 'none'
],
'backgroundColor' => 'rgba(255,255,255,0.7)',
'borderRadius' => 4,
'padding' => 2
]
]
],
'colors' => ['#28a745', '#ffc107', '#1e90ff', '#dc3545', '#17a2b8'],
'series' => [
[
'name' => 'Jml Petak Tanam/Kepras',
'data' => $filtered['jumlah_petak'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#43e97b'],
[1, '#38f9d7']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#28a745'
]
]
],
[
'name' => 'Jml Petak Tanam/Kepras SD ' . $periode,
'data' => $filtered['jumlah_petak_sd'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#f7971e'],
[1, '#ffd200']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#ffc107'
]
]
],
[
'name' => 'Drone ' . $periode,
'data' => $filtered['drone'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#f857a6'],
[1, '#ff5858']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#dc3545'
]
]
]
]
];
// Tambahkan info TOTAL KSO/TOTAL sebagai text HTML yang menarik
$totalKsoHtml = '';
if ($totalKsoIndex !== null) {
$totalKsoHtml = '
TOTAL —
Jml Petak: ' . number_format($jumlah_petak[$totalKsoIndex]) . ' |
Jml Petak SD ' . htmlspecialchars($periode) . ': ' . number_format($jumlah_petak_sd[$totalKsoIndex]) . ' |
Drone ' . htmlspecialchars($periode) . ': ' . number_format($drone[$totalKsoIndex]) . '
';
}
$this->data['total_kso_html'] = $totalKsoHtml;
return response()->json([
'status' => 'success',
'total_kso_html' => $totalKsoHtml,
'chartOptions' => $chartOptions
]);
}
public function getGrafikMorfKebunPeriode(Request $request)
{
config(['app.debug' => true]);
$tahun = $request->input('tahun', 2025);
$periode = $request->input('periode', '10A');
$data = DB::select('call sp_rekap_morfologi_kebun_periode_dinamis(?, ?)', [$tahun, $periode]);
$categories = [];
$jumlah_petak = [];
$jumlah_petak_sd = [];
$drone = [];
foreach ($data as $row) {
$categories[] = $row->nama_kebun;
$jumlah_petak[] = (int) $row->jumlah_petak_tanamkepras;
$jumlah_petak_sd[] = (int) $row->{'jumlah_petak_tanamkepras_sd_' . $periode};
$drone[] = (int) $row->{'morfologi_' . $periode};
}
// Pisahkan TOTAL KSO dari data, tampilkan di text sendiri
$filtered = [];
$totalKsoIndex = null;
foreach ($categories as $idx => $cat) {
if (strtoupper(trim($cat)) === 'TOTAL KSO') {
$totalKsoIndex = $idx;
} else {
$filtered['categories'][] = $cat;
$filtered['jumlah_petak'][] = $jumlah_petak[$idx];
$filtered['jumlah_petak_sd'][] = $jumlah_petak_sd[$idx];
$filtered['drone'][] = $drone[$idx];
}
}
$chartOptions = [
'chart' => [
'type' => 'column',
'backgroundColor' => [
'linearGradient' => [0, 0, 0, 400],
'stops' => [
[0, '#f4f8ff'],
[1, '#eaf1fb']
]
],
'style' => [
'fontFamily' => 'Segoe UI, Arial, sans-serif'
]
],
'title' => [
'text' => 'Grafik Monitoring Morfologi Periodik - Kebun',
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold',
'fontSize' => '20px',
'textShadow' => '0 2px 8px #d1ecf1'
]
],
'xAxis' => [
'categories' => $filtered['categories'] ?? [],
'title' => ['text' => 'Nama Kebun'],
'labels' => [
'style' => [
'color' => '#1e3c72',
'fontWeight' => 'bold',
'fontSize' => '13px'
]
],
'lineColor' => '#1e90ff',
'tickColor' => '#1e90ff'
],
'yAxis' => [
'min' => 0,
'title' => [
'text' => 'Jumlah',
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
],
'gridLineColor' => '#e0eafc',
'labels' => [
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
]
],
'legend' => [
'backgroundColor' => '#fff',
'borderColor' => '#d1ecf1',
'borderWidth' => 1,
'itemStyle' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
],
'tooltip' => [
'backgroundColor' => 'rgba(50,70,150,0.95)',
'style' => [
'color' => '#fff',
'fontSize' => '13px'
],
'borderRadius' => 10,
'borderWidth' => 2,
'shadow' => true,
'headerFormat' => '
{point.key} ',
'pointFormat' => ' {series.name}:
{point.y} '
],
'plotOptions' => [
'column' => [
'borderRadius' => 6,
'pointPadding' => 0.15,
'groupPadding' => 0.08,
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#222',
'textOutline' => 'none'
],
'backgroundColor' => 'rgba(255,255,255,0.7)',
'borderRadius' => 4,
'padding' => 2
]
]
],
'colors' => [
['linearGradient' => [0, 0, 0, 300], 'stops' => [[0, '#43e97b'], [1, '#38f9d7']]],
['linearGradient' => [0, 0, 0, 300], 'stops' => [[0, '#f7971e'], [1, '#ffd200']]],
['linearGradient' => [0, 0, 0, 300], 'stops' => [[0, '#f857a6'], [1, '#ff5858']]]
],
'series' => [
[
'name' => 'Jml Petak Tanam/Kepras',
'data' => $filtered['jumlah_petak'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#43e97b'],
[1, '#38f9d7']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#28a745'
]
]
],
[
'name' => 'Jml Petak Tanam/Kepras SD ' . $periode,
'data' => $filtered['jumlah_petak_sd'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#f7971e'],
[1, '#ffd200']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#ffc107'
]
]
],
[
'name' => 'Morfologi ' . $periode,
'data' => $filtered['drone'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#f857a6'],
[1, '#ff5858']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#dc3545'
]
]
]
]
];
// Tambahkan info TOTAL KSO sebagai text HTML yang menarik
$totalKsoHtml = '';
if ($totalKsoIndex !== null) {
$totalKsoHtml = '
TOTAL —
Jml Petak: ' . number_format($jumlah_petak[$totalKsoIndex]) . ' |
Jml Petak SD ' . htmlspecialchars($periode) . ': ' . number_format($jumlah_petak_sd[$totalKsoIndex]) . ' |
Morfologi ' . htmlspecialchars($periode) . ': ' . number_format($drone[$totalKsoIndex]) . '
';
}
$this->data['total_kso_html'] = $totalKsoHtml;
return response()->json([
'status' => 'success',
'total_kso_html' => $totalKsoHtml,
'chartOptions' => $chartOptions
]);
}
public function getGrafikMorfRayonPeriode(Request $request)
{
config(['app.debug' => true]);
$tahun = $request->input('tahun', 2025);
$periode = $request->input('periode', '10A');
$kebun = $request->input('kebun', 'NULL');
$data = DB::select('call sp_rekap_morfologi_rayon_periode_dinamis(?, ?, ?, ?)', [$tahun, $periode, $kebun,0]);
$categories = [];
$jumlah_petak = [];
$jumlah_petak_sd = [];
$drone = [];
foreach ($data as $row) {
$categories[] = $row->nama_rayon;
$jumlah_petak[] = (int) $row->jumlah_petak_tanamkepras;
$jumlah_petak_sd[] = (int) $row->{'jumlah_petak_tanamkepras_sd_' . $periode};
$drone[] = (int) $row->{'morfologi_' . $periode};
}
// Pisahkan TOTAL KSO dari data, tampilkan di text sendiri
$filtered = [];
$totalKsoIndex = null;
foreach ($categories as $idx => $cat) {
if (strtoupper(trim($cat)) === 'TOTAL') {
$totalKsoIndex = $idx;
} else {
$filtered['categories'][] = $cat;
$filtered['jumlah_petak'][] = $jumlah_petak[$idx];
$filtered['jumlah_petak_sd'][] = $jumlah_petak_sd[$idx];
$filtered['drone'][] = $drone[$idx];
}
}
$chartOptions = [
'chart' => [
'type' => 'column',
'backgroundColor' => [
'linearGradient' => [0, 0, 0, 400],
'stops' => [
[0, '#f4f8ff'],
[1, '#eaf1fb']
]
],
'style' => [
'fontFamily' => 'Segoe UI, Arial, sans-serif'
]
],
'title' => [
'text' => 'Grafik Monitoring Morfologi Periodik - Rayon',
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold',
'fontSize' => '20px',
'textShadow' => '0 2px 8px #d1ecf1'
]
],
'xAxis' => [
'categories' => $filtered['categories'] ?? [],
'title' => ['text' => 'Nama Rayon'],
'labels' => [
'style' => [
'color' => '#1e3c72',
'fontWeight' => 'bold',
'fontSize' => '13px'
]
],
'lineColor' => '#1e90ff',
'tickColor' => '#1e90ff'
],
'yAxis' => [
'min' => 0,
'title' => [
'text' => 'Jumlah',
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
],
'gridLineColor' => '#e0eafc',
'labels' => [
'style' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
]
],
'legend' => [
'backgroundColor' => '#fff',
'borderColor' => '#d1ecf1',
'borderWidth' => 1,
'itemStyle' => [
'color' => '#3345a1',
'fontWeight' => 'bold'
]
],
'tooltip' => [
'backgroundColor' => 'rgba(50,70,150,0.95)',
'style' => [
'color' => '#fff',
'fontSize' => '13px'
],
'borderRadius' => 10,
'borderWidth' => 2,
'shadow' => true,
'headerFormat' => '
{point.key} ',
'pointFormat' => ' {series.name}:
{point.y} '
],
'plotOptions' => [
'column' => [
'borderRadius' => 6,
'pointPadding' => 0.15,
'groupPadding' => 0.08,
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#222',
'textOutline' => 'none'
],
'backgroundColor' => 'rgba(255,255,255,0.7)',
'borderRadius' => 4,
'padding' => 2
]
]
],
'colors' => [
['linearGradient' => [0, 0, 0, 300], 'stops' => [[0, '#43e97b'], [1, '#38f9d7']]],
['linearGradient' => [0, 0, 0, 300], 'stops' => [[0, '#f7971e'], [1, '#ffd200']]],
['linearGradient' => [0, 0, 0, 300], 'stops' => [[0, '#f857a6'], [1, '#ff5858']]]
],
'series' => [
[
'name' => 'Jml Petak Tanam/Kepras',
'data' => $filtered['jumlah_petak'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#43e97b'],
[1, '#38f9d7']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#28a745'
]
]
],
[
'name' => 'Jml Petak Tanam/Kepras SD ' . $periode,
'data' => $filtered['jumlah_petak_sd'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#f7971e'],
[1, '#ffd200']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#ffc107'
]
]
],
[
'name' => 'Morfologi ' . $periode,
'data' => $filtered['drone'] ?? [],
'color' => [
'linearGradient' => [0, 0, 0, 300],
'stops' => [
[0, '#f857a6'],
[1, '#ff5858']
]
],
'dataLabels' => [
'enabled' => true,
'style' => [
'fontWeight' => 'bold',
'color' => '#dc3545'
]
]
]
]
];
// Tambahkan info TOTAL KSO sebagai text HTML yang menarik
$totalKsoHtml = '';
if ($totalKsoIndex !== null) {
$totalKsoHtml = '
TOTAL —
Jml Petak: ' . number_format($jumlah_petak[$totalKsoIndex]) . ' |
Jml Petak SD ' . htmlspecialchars($periode) . ': ' . number_format($jumlah_petak_sd[$totalKsoIndex]) . ' |
Morfologi ' . htmlspecialchars($periode) . ': ' . number_format($drone[$totalKsoIndex]) . '
';
}
// Kirim juga totalKsoHtml jika ingin dipakai di frontend
$this->data['total_kso_html'] = $totalKsoHtml;
return response()->json([
'status' => 'success',
'total_kso_html' => $totalKsoHtml,
'chartOptions' => $chartOptions
]);
}
public function getRankingDroneRayon(Request $request)
{
config(['app.debug' => true]);
$tahun = $request->input('tahun', 2025);
$periode = $request->input('periode', '10A');
$kebun = $request->input('kebun', 'NULL');
$data = DB::select('call sp_rekap_drone_rayon_periode_dinamis(?, ?, ?)', [$tahun, $periode, $kebun]);
// Sort descending by drone_10A
$sorted = collect($data)->sortByDesc(function($item) use ($periode) {
$jumlah_petak = (float)($item->{'jumlah_petak_tanamkepras_sd_' . $periode} ?? 0);
$drone = (float)($item->{'drone_' . $periode} ?? 0);
return $jumlah_petak > 0 ? $drone / $jumlah_petak : 0;
})->values();
$html = '';
$html .= '
';
$html .= '
Ranking
Rayon
Luas (Ha)
Petak Total
Petak SD ' . $periode . '
Petak Foto
% Drone/' . $periode . '
';
$no = 1;
foreach ($sorted as $row) {
if ($row->nama_rayon === 'TOTAL') continue;
$jumlah_petak = (float)($row->{'jumlah_petak_tanamkepras_sd_' . $periode} ?? 0);
$drone = (float)($row->{'drone_' . $periode} ?? 0);
$percent_drone = $jumlah_petak > 0 ? round($drone / $jumlah_petak * 100, 2) : 0;
// Tentukan warna bar berdasarkan prosentase
if ($percent_drone >= 100) {
$bar_color = '#28a745'; // hijau
} elseif ($percent_drone >= 75) {
$bar_color = '#ffc107'; // kuning
} else {
$bar_color = '#dc3545'; // merah
}
$html .= '
' . $no++ . '
' . htmlspecialchars($row->nama_rayon) . '
' . number_format($row->luas_polig, 2) . '
' . number_format($row->jumlah_petak_tanamkepras) . '
' . number_format($jumlah_petak) . '
' . number_format($drone) . '
' . number_format($percent_drone, 2) . '%
';
}
$html .= '
';
return response($html);
}
public function getRankingMorfRayon(Request $request)
{
config(['app.debug' => true]);
$tahun = $request->input('tahun', 2025);
$periode = $request->input('periode', '10A');
$kebun = $request->input('kebun', 'NULL');
$data = DB::select('call sp_rekap_morfologi_rayon_periode_dinamis(?, ?, ?,?)', [$tahun, $periode, $kebun,0]);
// Sort descending by drone_10A
$sorted = collect($data)->sortByDesc(function($item) use ($periode) {
$jumlah_petak = (float)($item->{'jumlah_petak_tanamkepras_sd_' . $periode} ?? 0);
$drone = (float)($item->{'morfologi_' . $periode} ?? 0);
return $jumlah_petak > 0 ? $drone / $jumlah_petak : 0;
})->values();
$html = '';
$html .= '
';
$html .= '
Ranking
Rayon
Luas (Ha)
Petak Total
Petak SD ' . $periode . '
Petak Foto
% Morfologi/' . $periode . '
';
$no = 1;
foreach ($sorted as $row) {
if ($row->nama_rayon === 'TOTAL') continue;
$jumlah_petak = (float)($row->{'jumlah_petak_tanamkepras_sd_' . $periode} ?? 0);
$drone = (float)($row->{'morfologi_' . $periode} ?? 0);
$percent_drone = $jumlah_petak > 0 ? round($drone / $jumlah_petak * 100, 2) : 0;
// Warna bar prosentase
if ($percent_drone >= 100) {
$bar_color = '#28a745'; // hijau
} elseif ($percent_drone >= 75) {
$bar_color = '#f1ff65'; // kuning
} else {
$bar_color = '#dc3545'; // merah
}
$tr_bg = 'background:color:#222;';
$html .= '
' . $no . '
' . htmlspecialchars($row->nama_rayon) . '
' . number_format($row->luas_polig, 2) . '
' . number_format($row->jumlah_petak_tanamkepras) . '
' . number_format($jumlah_petak) . '
' . number_format($drone) . '
' . number_format($percent_drone, 2) . '%
';
$no++;
}
$html .= '
';
return response($html);
}
public function getRekapCurahHujanPerRayon(Request $request)
{
// config(['app.debug' => true]);
$tahun = $request->input('tahun', 2025);
$data = DB::select('call get_rekap_curah_hujan_per_rayon(?)', [$tahun]);
$bulan = [
'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni',
'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'
];
$html = '';
$html .= '
';
$html .= '
Nama Kebun
Nama Rayon ';
foreach ($bulan as $b) {
$html .= '' . $b . ' ';
}
$html .= 'Rata-rata ';
$html .= ' ';
foreach ($bulan as $b) {
$html .= 'Curah Hujan (mm) Hari Hujan ';
}
$html .= 'Curah Hujan (mm) Hari Hujan ';
$html .= ' ';
// Kelompokkan data per nama kebun
$grouped = [];
foreach ($data as $row) {
$grouped[$row->nama_kebun][] = $row;
}
foreach ($grouped as $nama_kebun => $rows) {
// Hitung total rata-rata per kebun
$total_ch_per_bulan = array_fill(0, count($bulan), 0);
$total_hh_per_bulan = array_fill(0, count($bulan), 0);
$count_ch_per_bulan = array_fill(0, count($bulan), 0);
$count_hh_per_bulan = array_fill(0, count($bulan), 0);
foreach ($rows as $row) {
foreach ($bulan as $idx => $b) {
$ch = $row->{$b . '_ch'};
$hh = $row->{$b . '_hh'};
if ($ch !== null && $ch !== '') {
$total_ch_per_bulan[$idx] += $ch;
$count_ch_per_bulan[$idx]++;
}
if ($hh !== null && $hh !== '') {
$total_hh_per_bulan[$idx] += $hh;
$count_hh_per_bulan[$idx]++;
}
}
}
// Baris per rayon
foreach ($rows as $row) {
$html .= '';
$html .= '' . htmlspecialchars($row->nama_kebun) . ' ';
$html .= '' . htmlspecialchars($row->nama_rayon) . ' ';
$total_ch = 0;
$total_hh = 0;
$count_ch = 0;
$count_hh = 0;
foreach ($bulan as $idx => $b) {
$ch = $row->{$b . '_ch'};
$hh = $row->{$b . '_hh'};
// Penentuan warna
if ($ch === null || $ch === '') {
$warna = '';
} elseif ($ch <= 100) {
$warna = 'ch-rendah';
} elseif ($ch <= 300) {
$warna = 'ch-menengah';
} elseif ($ch <= 500) {
$warna = 'ch-tinggi';
} else {
$warna = 'ch-sangattinggi';
}
$html .= '' . ($ch !== null && $ch !== '' ? number_format($ch, 0) : '-') . ' ';
$html .= '' . ($hh !== null && $hh !== '' ? $hh : '-') . ' ';
if ($ch !== null && $ch !== '') {
$total_ch += $ch;
$count_ch++;
}
if ($hh !== null && $hh !== '') {
$total_hh += $hh;
$count_hh++;
}
}
// Tambahkan kolom rata-rata di akhir baris
$avg_ch = $count_ch > 0 ? round($total_ch / $count_ch, 1) : 0;
$avg_hh = $count_hh > 0 ? round($total_hh / $count_hh, 0) : 0;
$html .= '' . ($count_ch > 0 ? number_format($avg_ch, 1) : '-') . ' ';
$html .= '' . ($count_hh > 0 ? number_format($avg_hh, 0) : '-') . ' ';
$html .= ' ';
}
// Baris total rata-rata per kebun
$html .= '';
$html .= 'Total Rata-rata ' . htmlspecialchars($nama_kebun) . ' ';
$sum_avg_ch = 0;
$sum_avg_hh = 0;
$bulan_valid = 0;
foreach ($bulan as $idx => $b) {
$avg_ch_bulan = $count_ch_per_bulan[$idx] > 0 ? round($total_ch_per_bulan[$idx] / $count_ch_per_bulan[$idx], 1) : 0;
$avg_hh_bulan = $count_hh_per_bulan[$idx] > 0 ? round($total_hh_per_bulan[$idx] / $count_hh_per_bulan[$idx], 0) : 0;
$html .= '' . ($count_ch_per_bulan[$idx] > 0 ? number_format($avg_ch_bulan, 1) : '-') . ' ';
$html .= '' . ($count_hh_per_bulan[$idx] > 0 ? number_format($avg_hh_bulan, 0) : '-') . ' ';
if ($count_ch_per_bulan[$idx] > 0) {
$sum_avg_ch += $avg_ch_bulan;
$bulan_valid++;
}
if ($count_hh_per_bulan[$idx] > 0) {
$sum_avg_hh += $avg_hh_bulan;
}
}
// Rata-rata dari rata-rata bulanan
$final_avg_ch = $bulan_valid > 0 ? round($sum_avg_ch / $bulan_valid, 1) : 0;
$final_avg_hh = $bulan_valid > 0 ? round($sum_avg_hh / $bulan_valid, 0) : 0;
$html .= '' . ($bulan_valid > 0 ? number_format($final_avg_ch, 1) : '-') . ' ';
$html .= '' . ($bulan_valid > 0 ? number_format($final_avg_hh, 0) : '-') . ' ';
$html .= ' ';
}
$html .= '
Rendah: 0-100 mm
Menengah: 101-300 mm
Tinggi: 301-500 mm
Sangat Tinggi: >500 mm
';
return response($html);
}
protected function getWarnaCurahHujan($ch)
{
if ($ch === null || $ch === '') {
return '';
} elseif ($ch <= 100) {
return 'ch-rendah';
} elseif ($ch <= 300) {
return 'ch-menengah';
} elseif ($ch <= 500) {
return 'ch-tinggi';
} else {
return 'ch-sangattinggi';
}
}
public function getRekapCurahHujanTahunanKebun(Request $request)
{
$kebun = $request->input('kebun', 'Kwala Madu');
$rayon = $request->input('rayon', 'Kwala Madu A');
$mode = $request->input('mode', 'column'); // 'kebun' atau 'rayon'
// Jika jenis = rayon, dan nama_kebun mengandung spasi, ambil hanya bagian sebelum spasi terakhir (misal: "Kwala Madu A" -> "Kwala Madu A")
// Jika parameter rayon kosong, gunakan data kebun, jika ada gunakan data rayon
if (empty($rayon)) {
$jenis = 'kebun';
$nama_kebun = $kebun;
} else {
$jenis = 'rayon';
$nama_kebun = $rayon;
}
$data = DB::select('call get_rekap_curah_hujan_tahunan(?, ?)', [$jenis, $nama_kebun]);
// Daftar bulan
$bulan = [
'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni',
'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'
];
// Siapkan data untuk grafik Highcharts
$categories = [];
$series_ch = [];
$series_hh = [];
// Ambil tahun-tahun yang ada
foreach ($data as $row) {
$categories[] = $row->tahun;
}
// Untuk setiap bulan, buat data series curah hujan (ch) dan hari hujan (hh)
// Siapkan data curah hujan per bulan per tahun (kategori: bulan, series: tahun)
$tahun_list = array_unique(array_map(function($row) { return $row->tahun; }, $data));
sort($tahun_list);
// Kategori: bulan
$categories = $bulan;
// Series: tiap tahun, data: curah hujan per bulan
$series = [];
foreach ($tahun_list as $tahun) {
$data_ch = [];
foreach ($bulan as $b) {
// Cari row dengan tahun dan ambil curah hujan bulan tsb
$row = collect($data)->first(function($item) use ($tahun) { return $item->tahun == $tahun; });
$data_ch[] = $row ? (float)($row->{$b . '_ch'} ?? 0) : 0;
}
$series[] = [
'name' => (string)$tahun,
'data' => $data_ch,
'type' => $mode
];
}
// Format untuk Highcharts
$chartOptions = [
'chart' => [
'type' => $mode,
'backgroundColor' => '#fff'
],
'title' => [
'text' => 'Grafik Curah Hujan Bulanan'
],
'xAxis' => [
'categories' => $categories,
'title' => ['text' => 'Bulan']
],
'yAxis' => [
[
'title' => ['text' => 'Curah Hujan (mm)'],
'min' => 0
]
],
'tooltip' => [
'shared' => true
],
'legend' => [
'layout' => 'vertical',
'align' => 'right',
'verticalAlign' => 'middle'
],
'series' => array_map(function($s) {
$s['dataLabels'] = [
'enabled' => true,
'style' => [
'fontSize' => '11px'
]
];
return $s;
}, $series)
];
// Tambahkan legend keterangan warna curah hujan
$chartOptions['customLegendHtml'] = '
Rendah: 0-100 mm
Menengah: 101-300 mm
Tinggi: 301-500 mm
Sangat Tinggi: >500 mm
';
return response()->json([
'status' => 'success',
'chartOptions' => $chartOptions
]);
}
public function getChartMutuTakdesPerCluster(Request $request)
{
config(['app.debug' => true]);
// Ambil parameter filter dari request
$kode_kebun = $request->input('kode_kebun', '');
$kode_rayon = $request->input('kode_rayon', '');
$afdeling = $request->input('afdeling', '');
// Query untuk mengambil data berdasarkan filter
$data = DB::table('q_takdes_petak')
->select(
'clusterisasi_protas',
DB::raw('COUNT(*) as jumlah_baris'),
DB::raw('ROUND(SUM(takdes_ton)/SUM(luas_poligon), 2) as rata_protas'),
DB::raw('ROUND(SUM(luas_poligon), 3) as total_luas_poligon')
)
->whereNotNull('lahan_id')
->when(!empty($kode_kebun), function ($query) use ($kode_kebun) {
return $query->where('kode_kebun', $kode_kebun);
})
->when(!empty($kode_rayon), function ($query) use ($kode_rayon) {
return $query->where('kode_rayon', $kode_rayon);
})
->when(!empty($afdeling), function ($query) use ($afdeling) {
return $query->where('afdeling', $afdeling);
})
->groupBy('clusterisasi_protas')
->orderBy(DB::raw("CASE
WHEN clusterisasi_protas COLLATE utf8mb4_unicode_ci = 'EMAS' THEN 1
WHEN clusterisasi_protas COLLATE utf8mb4_unicode_ci = 'HIJAU' THEN 2
WHEN clusterisasi_protas COLLATE utf8mb4_unicode_ci = 'KUNING' THEN 3
WHEN clusterisasi_protas COLLATE utf8mb4_unicode_ci = 'MERAH' THEN 4
WHEN clusterisasi_protas COLLATE utf8mb4_unicode_ci = 'HITAM' THEN 5
ELSE 6 END"))
->get();
// Ambil data kebun untuk mengetahui total petak dan luas per kebun
$dataKebun = DB::table('q_takdes_rekap_kebun')
->select(
'*'
)
->groupBy('kode_kebun')
->get();
// Proses data untuk Highcharts
$categories = $data->pluck('clusterisasi_protas')->toArray();
$jumlah_baris = $data->pluck('jumlah_baris')->map(function($v) { return (int)$v; })->toArray();
$total_luas = $data->pluck('total_luas_poligon')->map(function($v) { return round((float)$v, 2); })->toArray();
$rata_protas = $data->pluck('rata_protas')->map(function($v) { return round((float)$v, 2); })->toArray();
// Warna untuk tiap cluster berdasarkan tingkat produktivitas
$colors = [
'EMAS' => '#ffc107', // emas/gold
'HIJAU' => '#28a745', // hijau
'KUNING' => '#FFD700', // kuning
'MERAH' => '#dc3545', // merah
'HITAM' => '#343a40', // hitam
];
// Format data untuk Highcharts
$chartDataLuas = [];
$chartDataJumlah = [];
foreach ($data as $item) {
$chartDataLuas[] = [
'name' => $item->clusterisasi_protas,
'y' => (float) $item->total_luas_poligon,
'color' => $colors[$item->clusterisasi_protas] ?? '#6c757d'
];
$chartDataJumlah[] = [
'name' => $item->clusterisasi_protas,
'y' => (int) $item->jumlah_baris,
'color' => $colors[$item->clusterisasi_protas] ?? '#6c757d'
];
$chartDataRataProtas[] = [
'name' => $item->clusterisasi_protas,
'y' => (float) $item->rata_protas,
'color' => $colors[$item->clusterisasi_protas] ?? '#6c757d'
];
}
// Return the data as JSON
return response()->json([
'status' => 'success',
'data' => $chartDataLuas,
'categories' => $categories,
'jumlah_baris' => $chartDataJumlah,
'total_luas' => $total_luas,
'rata_protas' => $chartDataRataProtas,
'dataKebun' => $dataKebun
]);
}
public function getTakdesDataByCluster(Request $request)
{
$kode_kebun = $request->input('kode_kebun', '');
$kode_rayon = $request->input('kode_rayon', '');
$afdeling = $request->input('afdeling', '');
$clusterisasi_protas = $request->input('clusterisasi_protas', '');
// Query to get data from q_takdes_petak with foto drone count
$data = DB::table('q_takdes_petak')
->leftJoin('tx_foto_drone', function($join) {
$join->on('q_takdes_petak.lahan_id', '=', 'tx_foto_drone.lahan_id')
->whereMonth('tx_foto_drone.tanggal', 12)
->whereYear('tx_foto_drone.tanggal', 2025);
})
->select('q_takdes_petak.*', DB::raw('COUNT(tx_foto_drone.lahan_id) as jumlah_foto_drone'))
->where(function ($query) use ($kode_kebun, $kode_rayon, $afdeling) {
if (!empty($kode_kebun)) {
$query->where('q_takdes_petak.kode_kebun', $kode_kebun);
}
if (!empty($kode_rayon)) {
$query->where('q_takdes_petak.kode_rayon', $kode_rayon);
}
if (!empty($afdeling)) {
$query->where('q_takdes_petak.afdeling', $afdeling);
}
})
->when(!empty($clusterisasi_protas), function ($query) use ($clusterisasi_protas) {
return $query->whereRaw('q_takdes_petak.clusterisasi_protas COLLATE utf8mb4_unicode_ci = ?', [$clusterisasi_protas]);
})
->groupBy('q_takdes_petak.lahan_id')
->get();
$html = '';
$html .= '
';
$html .= '
No
Foto Drone
Jawa/Luar Jawa
Cluster
Kode Kebun
Nama Kebun
GM
Kode Rayon
Nama Rayon
Manajer Tanaman
Afdeling
Asman
Asmud
Kode Petak SAP
Project Def SAP
No Kontrak eFarming
Lahan ID
Group Lahan ID
Deskripsi Petak
Desa
Kecamatan
Kabupaten
Luas Poligon (Ha)
Tahun Tanam
Masa Tanam
Bulan Tanam
Jenis Kategori 1
Jenis Kategori 2
Kategori Lahan 1
Kategori Lahan 2
Kepemilikan
Jenis Tanah
Tipologi Lahan
Varietas
Kemasakan
Tinggi Batang Sekarang
Tinggi Batang Akhir
Berat Batang Sekarang
Berat Batang Akhir
Jumlah Juring Per Ha
Jumlah Juring Total
Jumlah Batang Per Juring Sekarang
Jumlah Batang Per Juring Akhir
Panjang Juring
Panjang Juring Per Ha
PKP Meter
Diameter Batang Sekarang
Diameter Batang Akhir
Jumlah Batang Per Meter Sekarang
Jumlah Batang Per Meter Akhir
Taksasi Pandang (Ton/Ha)
Taksasi Hitung (Ton/Ha)
Takdes (Ton/Ha)
Takdes (Ton)
Rendemen Takdes
Rendemen Bagi Hasil
Hablur
Hablur Per Ha
Hablur Milik
Gula Milik
Tetes Milik
Clusterisasi Protas
Kode PG Penggiling
Nama PG Penggiling
Jarak Petak ke PG
Angkutan
Jenis Muat
Periode Tebang Rencana
Kelompok HGU
Ex Regional
';
$no = 1;
$total_luas = 0;
foreach ($data as $row) {
// Tentukan class berdasarkan clusterisasi
$clusterClass = '';
switch (strtoupper($row->clusterisasi_protas ?? '')) {
case 'EMAS':
$clusterClass = 'cluster-emas';
break;
case 'HIJAU':
$clusterClass = 'cluster-hijau';
break;
case 'KUNING':
$clusterClass = 'cluster-kuning';
break;
case 'MERAH':
$clusterClass = 'cluster-merah';
break;
case 'HITAM':
$clusterClass = 'cluster-hitam';
break;
}
$html .= '';
$html .= '' . $no++ . ' ';
$html .= '';
if ($row->jumlah_foto_drone > 0) {
$html .= '' . number_format($row->jumlah_foto_drone, 0) . ' Foto ';
} else {
$html .= '0 ';
}
$html .= ' ';
$html .= '' . htmlspecialchars($row->jawa_luarjawa ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->cluster ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->kode_kebun ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->nama_kebun ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->nama_general_manager ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->kode_rayon ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->nama_rayon ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->nama_manajer_tanaman ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->afdeling ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->asman ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->asmud ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->kode_petak_sap ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->project_def_sap ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->no_kontrak_efarming ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->lahan_id ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->group_lahan_id ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->deskripsi_petak ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->desa ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->kecamatan ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->kabupaten ?? '-') . ' ';
$html .= '' . number_format($row->luas_poligon ?? 0, 3) . ' ';
$html .= '' . htmlspecialchars($row->tahun_tanam ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->masa_tanam ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->bulan_tanam ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->jenis_kategori_1 ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->jenis_kategori_2 ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->kategori_lahan_1 ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->kategori_lahan_2 ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->kepemilikan ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->jenis_tanah ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->tipologi_lahan ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->varietas ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->kemasakan ?? '-') . ' ';
$html .= '' . number_format($row->tinggi_batang_sekarang ?? 0, 2) . ' ';
$html .= '' . number_format($row->tinggi_batang_akhir ?? 0, 2) . ' ';
$html .= '' . number_format($row->berat_batang_sekarang ?? 0, 2) . ' ';
$html .= '' . number_format($row->berat_batang_akhir ?? 0, 2) . ' ';
$html .= '' . number_format($row->jumlah_juring_perha ?? 0, 0) . ' ';
$html .= '' . number_format($row->jumlah_juring_total ?? 0, 0) . ' ';
$html .= '' . number_format($row->jumlah_batang_perjuring_sekarang ?? 0, 0) . ' ';
$html .= '' . number_format($row->jumlah_batang_perjuring_akhir ?? 0, 0) . ' ';
$html .= '' . number_format($row->panjang_juring ?? 0, 2) . ' ';
$html .= '' . number_format($row->panjang_juring_perha ?? 0, 2) . ' ';
$html .= '' . number_format($row->pkp_meter ?? 0, 2) . ' ';
$html .= '' . number_format($row->diameter_batang_sekarang ?? 0, 2) . ' ';
$html .= '' . number_format($row->diameter_batang_akhir ?? 0, 2) . ' ';
$html .= '' . number_format($row->jumlah_batang_permeter_sekarang ?? 0, 0) . ' ';
$html .= '' . number_format($row->jumlah_batang_permeter_akhir ?? 0, 0) . ' ';
$html .= '' . number_format($row->taksasi_pandang_tonperha ?? 0, 2) . ' ';
$html .= '' . number_format($row->taksasi_hitung_tonperha ?? 0, 2) . ' ';
$html .= '' . number_format($row->takdes_tonperha ?? 0, 2) . ' ';
$html .= '' . number_format($row->takdes_ton ?? 0, 2) . ' ';
$html .= '' . number_format($row->rendemen_takdes ?? 0, 2) . ' ';
$html .= '' . number_format($row->rendemen_bagihasil ?? 0, 2) . ' ';
$html .= '' . number_format($row->hablur ?? 0, 2) . ' ';
$html .= '' . number_format($row->hablur_perha ?? 0, 2) . ' ';
$html .= '' . number_format($row->hablur_milik ?? 0, 2) . ' ';
$html .= '' . number_format($row->gula_milik ?? 0, 2) . ' ';
$html .= '' . number_format($row->tetes_milik ?? 0, 2) . ' ';
$html .= '' . htmlspecialchars($row->clusterisasi_protas ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->kode_pg_penggiling ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->nama_pg_penggiling ?? '-') . ' ';
$html .= '' . number_format($row->jarak_petak_kepg ?? 0, 2) . ' ';
$html .= '' . htmlspecialchars($row->angkutan ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->jenis_muat ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->periode_tebng_rencana ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->kelompok_hgu ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->ex_regional ?? '-') . ' ';
$html .= ' ';
$total_luas += $row->luas_poligon ?? 0;
}
// Baris total
if (count($data) > 0) {
$html .= '';
$html .= 'Total ';
$html .= '' . number_format($total_luas, 3) . ' ';
$html .= '' . count($data) . ' Petak ';
$html .= ' ';
}
if (count($data) == 0) {
$html .= 'Data tidak ditemukan.
';
$html .= '
EMAS
HIJAU
KUNING
MERAH
HITAM
';
return response($html);
}
public function getTakmarRekapKebun(Request $request)
{
$kodeKebun = $request->input('kode_kebun', '');
$cluster = $request->input('cluster', '');
$jawa = $request->input('jawa_luarjawa', '');
$rows = DB::table('q_takmar_rekap_kebun')
->when($kodeKebun !== '', function ($q) use ($kodeKebun) {
return $q->where('kode_kebun', $kodeKebun);
})
->when($cluster !== '', function ($q) use ($cluster) {
return $q->where(function ($qq) use ($cluster) {
$qq->where('cluster', $cluster)->orWhere('nama_cluster', $cluster);
});
})
->when($jawa !== '', function ($q) use ($jawa) {
return $q->where(function ($qq) use ($jawa) {
$qq->where('jawa_luarjawa', $jawa)->orWhere('jawa_nonjawa', $jawa);
});
})
->orderBy('row_type', 'desc')
->get();
$pick = function ($row, array $keys, $default = null) {
foreach ($keys as $k) {
if (isset($row->$k)) {
return $row->$k;
}
}
return $default;
};
$html = '';
$html .= '
';
$html .= '
';
$html .= '
Jawa/Luar Jawa
Cluster
Kode Kebun
Nama Kebun
Jml Petak
Luas Polig (Ha)
Takmar (Ton)
Protas (Ton/Ha)
Rendemen
Rendemen BH
Hablur (Ton)
Hablur/Ha
Hablur Milik
Gula Milik
Tetes Milik
';
$tot = [
'jml_petak' => 0, 'luas' => 0, 'takdes' => 0, 'protas' => 0,
'rend' => 0, 'rend_bh' => 0, 'hablur' => 0, 'hablur_ha' => 0,
'hablur_milik' => 0, 'gula_milik' => 0, 'tetes_milik' => 0, 'count' => 0
];
$no = 1;
foreach ($rows as $r) {
$jmlPetak = (float) $pick($r, ['jml_petak'], 0);
$luas = (float) $pick($r, ['luas_polig', 'luas_poligon'], 0);
$takdes = (float) $pick($r, ['takmar_ton', 'takmar_tor'], 0);
$protas = (float) $pick($r, ['protas', 'takmar_tonperha', 'takmar_tonha'], 0);
$rend = (float) $pick($r, ['rendemen_takmar'], 0);
$rendBh = (float) $pick($r, ['rendemen_bagihasil', 'rendemen_bagi_hasil'], 0);
$hablur = (float) $pick($r, ['hablur', 'hablur_ton'], 0);
$hablurHa = (float) $pick($r, ['hablur_per_ha', 'hablur_per'], 0);
$hablurMilik = (float) $pick($r, ['hablur_milik', 'hablur_mili'], 0);
$gulaMilik = (float) $pick($r, ['gula_milik'], 0);
$tetesMilik = (float) $pick($r, ['tetes_milik'], 0);
$html .= '
'.htmlspecialchars((string) $pick($r, ['jawa_luarjawa', 'jawa_luarja', 'jawa_nonjawa'], '-')).'
'.htmlspecialchars((string) $pick($r, ['cluster', 'nama_cluster'], '-')).'
'.htmlspecialchars((string) $pick($r, ['kode_kebun', 'kode_kebu'], '-')).'
'.htmlspecialchars((string) $pick($r, ['nama_kebun', 'nama_keb'], '-')).'
'.number_format($jmlPetak, 0).'
'.number_format($luas, 3).'
'.number_format($takdes, 2).'
'.number_format($protas, 2).'
'.number_format($rend, 2).'
'.number_format($rendBh, 2).'
'.number_format($hablur, 2).'
'.number_format($hablurHa, 2).'
'.number_format($hablurMilik, 2).'
'.number_format($gulaMilik, 2).'
'.number_format($tetesMilik, 2).'
';
$tot['jml_petak'] += $jmlPetak;
$tot['luas'] += $luas;
$tot['takdes'] += $takdes;
$tot['protas'] += $protas;
$tot['rend'] += $rend;
$tot['rend_bh'] += $rendBh;
$tot['hablur'] += $hablur;
$tot['hablur_ha'] += $hablurHa;
$tot['hablur_milik'] += $hablurMilik;
$tot['gula_milik'] += $gulaMilik;
$tot['tetes_milik'] += $tetesMilik;
$tot['count']++;
}
$html .= '
';
return response($html);
}
public function getFotoDrone(Request $request)
{
$lahan_id = $request->input('lahan_id');
if (empty($lahan_id)) {
return response()->json([
'status' => 'error',
'message' => 'Parameter lahan_id is required'
]);
}
// Query untuk mengambil data foto drone berdasarkan lahan_id
$data = DB::select('SELECT lahan_id, tanggal, foto FROM tx_foto_drone WHERE lahan_id = ? AND MONTH(tanggal) = 12 AND YEAR(tanggal) = 2025 ORDER BY tanggal DESC', [$lahan_id]);
$html = '';
$html .= '
No
Lahan ID
Tanggal
Foto
Aksi
';
$no = 1;
foreach ($data as $row) {
$html .= '';
$html .= '' . $no++ . ' ';
$html .= '' . htmlspecialchars($row->lahan_id) . ' ';
$html .= '' . date('d-m-Y', strtotime($row->tanggal)) . ' ';
// Kolom foto
if (!empty($row->foto)) {
$html .= '
';
} else {
$html .= 'Tidak ada foto ';
if (!empty($row->foto)) {
$html .= '
';
} else {
$html .= '- ';
}
$html .= ' ';
$html .= ' ';
}
if (count($data) == 0) {
$html .= 'Data foto drone tidak ditemukan untuk lahan ID: ' . htmlspecialchars($lahan_id) . '
';
// Modal untuk menampilkan gambar
return response($html);
}
public function getGrafikKlentekTotal(Request $request)
{
$tanggal = $request->input('tanggal', date('Y-m-d'));
$kode_kebun = NULL;
// Ambil data dari stored procedure
$data = DB::select('call sp_monitoring_klentek_kebun(?, ?)', [$kode_kebun,$tanggal]);
$pekerjaan = ['klentek_1', 'klentek_2', 'klentek_3'];
$hektar = [];
$sasaran = [];
foreach ($pekerjaan as $idx => $pekerjaan_name) {
$hektar[] = isset($data[0]->{"klentek_" . ($idx + 1)}) ? (float)$data[0]->{"klentek_" . ($idx + 1)} : 0;
$sasaran[] = isset($data[0]->{"sasaran_klentek_" . ($idx + 1)}) ? (float)$data[0]->{"sasaran_klentek_" . ($idx + 1)} : 0;
}
return response()->json([
'status' => 'success',
'data' => [
'pekerjaan' => $pekerjaan,
'hektar' => $hektar,
'sasaran' => $sasaran
]
]);
}
public function getGrafikKlentekKebun(Request $request)
{
$tanggal = $request->input('tanggal', date('Y-m-d'));
$kode_kebun = $request->input('kode_kebun');
if (empty($kode_kebun)) {
$kode_kebun = NULL;
}
// Ambil data dari stored procedure
$data = DB::select('call sp_monitoring_klentek_kebun(?, ?)', [$kode_kebun,$tanggal]);
$pekerjaan = ['klentek_1', 'klentek_2', 'klentek_3'];
$hektar = [];
$sasaran = [];
foreach ($pekerjaan as $idx => $pekerjaan_name) {
$hektar[] = isset($data[0]->{"klentek_" . ($idx + 1)}) ? (float)$data[0]->{"klentek_" . ($idx + 1)} : 0;
$sasaran[] = isset($data[0]->{"sasaran_klentek_" . ($idx + 1)}) ? (float)$data[0]->{"sasaran_klentek_" . ($idx + 1)} : 0;
}
return response()->json([
'status' => 'success',
'data' => [
'pekerjaan' => $pekerjaan,
'hektar' => $hektar,
'sasaran' => $sasaran
]
]);
}
public function getGrafikKlentekRayon(Request $request)
{
$tanggal = $request->input('tanggal', date('Y-m-d'));
$kode_rayon = $request->input('kode_rayon');
// Ambil data dari stored procedure
$data = DB::select('call sp_monitoring_klentek_rayon(?, ?)', [$kode_rayon,$tanggal]);
$pekerjaan = ['klentek_1', 'klentek_2', 'klentek_3'];
$hektar = [];
$sasaran = [];
foreach ($pekerjaan as $idx => $pekerjaan_name) {
$hektar[] = isset($data[0]->{"klentek_" . ($idx + 1)}) ? (float)$data[0]->{"klentek_" . ($idx + 1)} : 0;
$sasaran[] = isset($data[0]->{"sasaran_klentek_" . ($idx + 1)}) ? (float)$data[0]->{"sasaran_klentek_" . ($idx + 1)} : 0;
}
return response()->json([
'status' => 'success',
'data' => [
'pekerjaan' => $pekerjaan,
'hektar' => $hektar,
'sasaran' => $sasaran
]
]);
}
public function getRekapDronePetakPeriodeDinamis(Request $request)
{
$tahun = $request->input('tahun');
$periode = $request->input('periode');
$kode_rayon = $request->input('kode_rayon');
// Ambil data dari stored procedure
$data = DB::select('call sp_rekap_drone_petak_periode_dinamis(?, ?, ?)', [
$tahun, $periode, $kode_rayon
]);
// Kelompokkan data berdasarkan nama_rayon
$grouped = collect($data)->groupBy('nama_rayon');
// Buat tab navigation
$html = '';
// Tombol download Excel (menggunakan SheetJS/xlsx)
$html .= '
';
$html .= '
';
$tabIdx = 0;
foreach ($grouped as $nama_rayon => $rows) {
$active = $tabIdx === 0 ? 'active' : '';
$html .= '
'.htmlspecialchars($nama_rayon).'
';
$tabIdx++;
}
$html .= ' ';
$html .= '
';
$tabIdx = 0;
foreach ($grouped as $nama_rayon => $rows) {
$active = $tabIdx === 0 ? 'show active' : '';
$html .= '
';
$html .= '
';
$html .= '
No
Lahan ID
Kode Kebun
Kode Rayon
Nama Rayon
Afdeling
Luas Poligon (Ha)
Petak Tanam & Kepras
Petak SD '.$periode.'
Foto Drone '.$periode.'
';
$no = 1;
foreach ($rows as $row) {
$drone_val = (int)($row->{'drone_' . $periode} ?? 0);
$drone_status = $drone_val > 0
? 'Sudah Input '
: 'Belum Input ';
$html .= '';
$html .= '' . $no++ . ' ';
$html .= '' . htmlspecialchars($row->lahan_id ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->kode_kebun ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->kode_rayon ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->nama_rayon ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->afd_new ?? '-') . ' ';
$html .= '' . number_format($row->luas_polig ?? 0, 3) . ' ';
$html .= '' . number_format($row->petak_tanamkepras ?? 0) . ' ';
$html .= '' . number_format($row->{'jumlah_petak_tanamkepras_sd_' . $periode} ?? 0) . ' ';
$html .= '' . $drone_status . ' ';
$html .= ' ';
}
if (count($rows) == 0) {
$html .= 'Data tidak ditemukan.
';
$html .= '
';
$tabIdx++;
}
$html .= '
';
$html .= '';
$html .= '';
return response($html);
}
public function getRekapMorfPetakPeriodeDinamis(Request $request)
{
$tahun = $request->input('tahun');
$periode = $request->input('periode');
$kode_rayon = $request->input('kode_rayon');
// Ambil data dari stored procedure
$data = DB::select('call sp_rekap_morfologi_petak_periode_dinamis(?, ?, ?, ?)', [
$tahun, $periode, $kode_rayon, 0
]);
// Kelompokkan data berdasarkan nama_rayon
$grouped = collect($data)->groupBy('nama_rayon');
// Buat tab navigation
$html = '';
// Tombol download Excel (menggunakan SheetJS/xlsx)
$html .= '
';
$html .= '
';
$tabIdx = 0;
foreach ($grouped as $nama_rayon => $rows) {
$active = $tabIdx === 0 ? 'active' : '';
$html .= '
'.htmlspecialchars($nama_rayon).'
';
$tabIdx++;
}
$html .= ' ';
$html .= '
';
$tabIdx = 0;
foreach ($grouped as $nama_rayon => $rows) {
$active = $tabIdx === 0 ? 'show active' : '';
$html .= '
';
$html .= '
';
$html .= '
No
Kode Kebun
Nama Kebun
Kode Rayon
Nama Rayon
Afdeling
Lahan ID
Luas Poligon (Ha)
Petak Tanam & Kepras
Petak SD '.$periode.'
Morfologi '.$periode.'
';
$no = 1;
foreach ($rows as $row) {
$morfologi_val = (int)($row->{'morfologi_' . $periode} ?? 0);
$morfologi_status = $morfologi_val > 0
? 'Sudah Input '
: 'Belum Input ';
$html .= '';
$html .= '' . $no++ . ' ';
$html .= '' . htmlspecialchars($row->kode_kebun ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->nama_kebun ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->kode_rayon ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->nama_rayon ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->afd_new ?? '-') . ' ';
$html .= '' . htmlspecialchars($row->lahan_id ?? '-') . ' ';
$html .= '' . number_format($row->luas_polig ?? 0, 3) . ' ';
$html .= '' . number_format($row->jumlah_petak_tanamkepras ?? 0) . ' ';
$html .= '' . number_format($row->{'jumlah_petak_tanamkepras_sd_' . $periode} ?? 0) . ' ';
$html .= '' . $morfologi_status . ' ';
$html .= ' ';
}
if (count($rows) == 0) {
$html .= 'Data tidak ditemukan.
';
$html .= '
';
$tabIdx++;
}
$html .= '
';
$html .= '';
$html .= '';
return response($html);
}
public function getChartDataKesegaranAfd(Request $request)
{
$kode_kebun = $request->input('kebun');
$kode_rayon = $request->input('rayon');
$cluster = $request->input('cluster');
$tanggal = $request->input('tanggal', date('Y-m-d', strtotime('-1 day')));
if($cluster !='' && $cluster != null && $kode_kebun == ''){
$dataHI = DB::select('CALL sp_kesegaran_kebun_hi(?, ?)', [
$cluster,
$tanggal
]);
$dataSD = DB::select('CALL sp_kesegaran_kebun_sd(?, ?)', [
$cluster,
$tanggal
]);
}else{
// Fetch data HI (Hari Ini)
$dataHI = DB::select('CALL sp_kesegaran_afd_grafik_hi(?, ?, ?)', [
$kode_kebun,
$kode_rayon,
$tanggal
]);
// Fetch data SD (Sampai Dengan)
$dataSD = DB::select('CALL sp_kesegaran_afd_grafik_sd(?, ?, ?)', [
$kode_kebun,
$kode_rayon,
$tanggal
]);
}
// Process data HI
$processedHI = collect($dataHI)->map(function ($row) {
return [
'grouping_label' => $row->grouping_label,
'label_24jam' => (int) $row->{'<24jam'},
'label_48jam' => (int) $row->{'24sd48jam'},
'label_lebih_48jam' => (int) $row->{'>48jam'}
];
});
// Process data SD
$processedSD = collect($dataSD)->map(function ($row) {
return [
'grouping_label' => $row->grouping_label,
'label_24jam' => (int) $row->{'<24jam'},
'label_48jam' => (int) $row->{'24sd48jam'},
'label_lebih_48jam' => (int) $row->{'>48jam'}
];
});
return response()->json([
'status' => 'success',
'tanggal' => date('d-m-Y', strtotime($tanggal)),
'data_hi' => $processedHI,
'data_sd' => $processedSD
]);
}
public function grafikbiayasdm(){
$this->data['unit'] = DB::table('master_unit_sdm')
->select('kode_unit', 'unit')
->get();
return view('dashboard.biayasdm', $this->data);
}
public function gettotkaryawan(Request $request){
$p_unit = $request->input('unit', '');
$this->data['jml_kary'] = DB::table('data_jk_sdm')
->selectRaw('SUM(L) + SUM(P) as jml')
->when(!empty($p_unit), function ($query) use ($p_unit) {
return $query->where('unit', $p_unit);
})
->groupBy('bulan')
->orderByDesc('bulan')
->limit(1)
->value('jml');
return response()->json([
'status' => 'success',
'data' => [
'jml_kary' => $this->data['jml_kary'],
],
]);
}
public function getrataumurkaryawan(Request $request){
$p_unit = $request->input('unit', '');
$this->data['rata_umur'] = DB::table('data_umur_sdm')
->selectRaw('ROUND((
(`17_25` * 21) +
(`26_35` * 30.5) +
(`36_45` * 40.5) +
(`46_50` * 48) +
(`50_>` * 55)
) / NULLIF((`17_25` + `26_35` + `36_45` + `46_50` + `50_>`), 0), 2) AS rata_rata_usia')
->when(!empty($p_unit), function ($query) use ($p_unit) {
return $query->where('unit', $p_unit);
})
->groupBy('bulan')
->orderByDesc('bulan')
->limit(1)
->value('rata_rata_usia');
return response()->json([
'status' => 'success',
'data' => [
'rata_umur' => round($this->data['rata_umur']),
],
]);
}
public function getratabiayakaryawan(Request $request)
{
$p_tahun = date('Y');
$p_unit = $request->input('unit', '');
// Call the stored procedure to fetch data
$totalkary = DB::table('data_jk_sdm')
->selectRaw('SUM(L) + SUM(P) as jml')
->when(!empty($p_unit), function ($query) use ($p_unit) {
return $query->where('unit', $p_unit);
})
->groupBy('bulan')
->orderByDesc('bulan')
->limit(1)
->value('jml');
// Call the stored procedure to fetch data
$data = DB::select('call sp_rekap_biaya_sdm(?,?)', [$p_tahun, $p_unit]);
// Process the data if needed
$processedData = collect($data)->map(function ($item, $key) use ($totalkary) {
return [
'rata_biaya_sdm' => round(((float) $item->total_kary_tetap_real + (float) $item->total_kary_non_tetap_real + (float) $item->total_kary_os_real) / max($totalkary, 1), 0),
];
});
// Return the data as JSON
return response()->json([
'status' => 'success',
'data' => $processedData,
]);
}
public function getbiayasdm(Request $request)
{
$p_tahun = date('Y');
$p_unit = $request->input('unit', '');
// Call the stored procedure to fetch data
$data = DB::select('call sp_rekap_biaya_sdm(?,?)', [$p_tahun, $p_unit]);
// Process the data if needed
$processedData = collect($data)->map(function ($item) {
return [
'bulan' => date('F', mktime(0, 0, 0, $item->bulan, 1)),
'tahun' => $item->tahun,
'total_kary_tetap_real' => number_format((float) $item->total_kary_tetap_real/1000, 0, ',', '.'),
'total_kary_tetap_rkap' => number_format((float) $item->total_kary_tetap_rkap/1000, 0, ',', '.'),
'persen_kary_tetap' => round((float) $item->total_kary_tetap_real/1000 / max((float) $item->total_kary_tetap_rkap/1000, 1) * 100, 2),
'total_kary_non_tetap_real' => number_format((float) $item->total_kary_non_tetap_real/1000, 0, ',', '.'),
'total_kary_non_tetap_rkap' => number_format((float) $item->total_kary_non_tetap_rkap/1000, 0, ',', '.'),
'persen_kary_non_tetap' => round((float) $item->total_kary_non_tetap_real/1000 / max((float) $item->total_kary_non_tetap_rkap/1000, 1) * 100, 2),
'total_kary_os_real' => number_format((float) $item->total_kary_os_real/1000, 0, ',', '.'),
'total_kary_os_rkap' => number_format((float) $item->total_kary_os_rkap/1000, 0, ',', '.'),
'persen_kary_os' => round((float) $item->total_kary_os_real/1000 / max((float) $item->total_kary_os_rkap/1000, 1) * 100, 2),
];
});
// Return the data as JSON
return response()->json([
'status' => 'success',
'data' => $processedData,
]);
}
public function getbiayasdmunit(Request $request)
{
$p_tahun = $request->input('tahun', date('Y'));
$p_unit = $request->input('unit', '');
// Call the stored procedure to fetch data
if(empty($p_unit)){
$data = DB::select('call sp_rekap_biaya_sdm_total(?)', [$p_tahun]);
}else{
$data = DB::select('call sp_rekap_biaya_sdm_per_unit(?, ?)', [$p_tahun, $p_unit]);
}
// Process the data if needed
$chartData = collect($data)->map(function ($item) {
return [
'categori' => date('F', mktime(0, 0, 0, $item->bulan, 1)),
'real_tetap' => round(((float) $item->total_kary_tetap_real)/1000,0),
'real_non_tetap' => round(((float) $item->total_kary_non_tetap_real)/1000,0),
'real_os' => round(((float) $item->total_kary_os_real)/1000,0),
'rkap_tetap' => round(((float) $item->total_kary_tetap_rkap)/1000,0),
];
});
$categories = $chartData->pluck('categori')->toArray();
$real_tetap = $chartData->pluck('real_tetap')->toArray();
$real_non_tetap = $chartData->pluck('real_non_tetap')->toArray();
$real_os = $chartData->pluck('real_os')->toArray();
$rkap_tetap = $chartData->pluck('rkap_tetap')->toArray();
// Return the data formatted for Highcharts
return response()->json([
'status' => 'success',
'categori' => $categories,
'real_tetap' => $real_tetap,
'real_non_tetap' => $real_non_tetap,
'real_os' => $real_os,
'rkap_tetap' => $rkap_tetap,
]);
}
public function getRendemenHarianPgGrafik(Request $request)
{
$tanggal = $request->input('tanggal', date('Y-m-d'));
$data = DB::select('CALL sp_real_2026_rendemen_harian_pg_grafik(?)', [$tanggal]);
$categories = collect($data)->map(function ($item) {
return $item->nama_pg ?? $item->kode_pg ?? '';
})->toArray();
$kodePg = collect($data)->pluck('kode_pg')->toArray();
$rendemenHi = collect($data)->map(function ($item) {
return is_null($item->rendemen_hi) || $item->rendemen_hi === '' ? null : (float) $item->rendemen_hi;
})->toArray();
$rendemenSd = collect($data)->map(function ($item) {
return is_null($item->rendemen_sd) || $item->rendemen_sd === '' ? null : (float) $item->rendemen_sd;
})->toArray();
$tanggalLabel = !empty($data) ? ($data[0]->tanggal ?? date('d/m/Y', strtotime($tanggal))) : date('d/m/Y', strtotime($tanggal));
return response()->json([
'status' => 'success',
'tanggal' => $tanggalLabel,
'categories' => $categories,
'kode_pg' => $kodePg,
'series' => [
[
'name' => 'Rendemen HI',
'data' => $rendemenHi,
],
[
'name' => 'Rendemen SD',
'data' => $rendemenSd,
],
],
]);
}
public function getgendersdmunit(Request $request)
{
// $p_tahun = $request->input('tahun', date('Y'));
$p_unit = $request->input('unit', '');
// Call the stored procedure to fetch data
$data = DB::table('data_jk_sdm')
->select(
'bulan',
'tahun',
DB::raw('SUM(L) as L'),
DB::raw('SUM(P) as P')
)
->when(!empty($p_unit), function ($query) use ($p_unit) {
return $query->where('unit', $p_unit);
})
->groupBy('bulan', 'tahun')
->orderByDesc('bulan')
->limit(1)
->get();
// Format data untuk Highcharts Pie
$chartData = [
[
'name' => 'Laki-laki',
'y' => (int) ($data[0]->L ?? 0),
'color' => '#007bff'
],
[
'name' => 'Perempuan',
'y' => (int) ($data[0]->P ?? 0),
'color' => '#e83e8c'
]
];
return response()->json([
'status' => 'success',
'data' => $chartData,
'bulan' => $data[0]->bulan ?? '',
'tahun' => $data[0]->tahun ?? ''
]);
}
public function geteducationsdmunit(Request $request)
{
$p_unit = $request->input('unit', '');
$data = DB::table('data_pendidikan_sdm')
->select(
'bulan',
'tahun',
DB::raw('SUM(sd) as SD'),
DB::raw('SUM(smp) as SMP'),
DB::raw('SUM(sma) as SMA'),
DB::raw('SUM(d1_d3) as D1_D3'),
DB::raw('SUM(s1) as S1'),
DB::raw('SUM(s2) as S2')
)
->when(!empty($p_unit), function ($query) use ($p_unit) {
return $query->where('unit', $p_unit);
})
->groupBy('bulan', 'tahun')
->orderByDesc('bulan')
->limit(1)
->get();
$result = [
[
'name' => 'SD',
'y' => (int) ($data[0]->SD ?? 0),
'color' => '#f39c12'
],
[
'name' => 'SMP',
'y' => (int) ($data[0]->SMP ?? 0),
'color' => '#27ae60'
],
[
'name' => 'SMA',
'y' => (int) ($data[0]->SMA ?? 0),
'color' => '#2980b9'
],
[
'name' => 'D1-D3',
'y' => (int) ($data[0]->D1_D3 ?? 0),
'color' => '#8e44ad'
],
[
'name' => 'S1',
'y' => (int) ($data[0]->S1 ?? 0),
'color' => '#e67e22'
],
[
'name' => 'S2',
'y' => (int) ($data[0]->S2 ?? 0),
'color' => '#c0392b'
]
];
return response()->json([
'status' => 'success',
'data' => $result,
'bulan' => $data[0]->bulan ?? '',
'tahun' => $data[0]->tahun ?? ''
]);
}
public function getjabatansdmunit(Request $request)
{
$p_unit = $request->input('unit', '');
$data = DB::table('data_status_kary_sdm')
->select(
'bulan',
'tahun',
DB::raw('SUM(os) as OS'),
DB::raw('SUM(non_tetap) as NON_TETAP'),
DB::raw('SUM(karpel_tetap) as KARPEL_TETAP'),
DB::raw('SUM(bod_3) as BOD_3'),
DB::raw('SUM(bod_2) as BOD_2'),
DB::raw('SUM(bod_1) as BOD_1'),
DB::raw('SUM(sevp) as SEVP')
)
->when(!empty($p_unit), function ($query) use ($p_unit) {
return $query->where('unit', $p_unit);
})
->groupBy('bulan', 'tahun')
->orderByDesc('bulan')
->limit(1)
->get();
$result = [
[
'name' => 'OS',
'y' => (int) ($data[0]->OS ?? 0),
'color' => '#007bff'
],
[
'name' => 'NON TETAP',
'y' => (int) ($data[0]->NON_TETAP ?? 0),
'color' => '#ffc107'
],
[
'name' => 'KARPEL TETAP',
'y' => (int) ($data[0]->KARPEL_TETAP ?? 0),
'color' => '#28a745'
],
[
'name' => 'BOD 3',
'y' => (int) ($data[0]->BOD_3 ?? 0),
'color' => '#17a2b8'
],
[
'name' => 'BOD 2',
'y' => (int) ($data[0]->BOD_2 ?? 0),
'color' => '#6f42c1'
],
[
'name' => 'BOD 1',
'y' => (int) ($data[0]->BOD_1 ?? 0),
'color' => '#e83e8c'
],
[
'name' => 'SEVP',
'y' => (int) ($data[0]->SEVP ?? 0),
'color' => '#fd7e14'
]
];
return response()->json([
'status' => 'success',
'data' => $result,
'bulan' => $data[0]->bulan ?? '',
'tahun' => $data[0]->tahun ?? ''
]);
}
public function getumursdmunit(Request $request)
{
$p_unit = $request->input('unit', '');
$data = DB::table('data_umur_sdm')
->select(
'bulan',
'tahun',
DB::raw('SUM(`17_25`) as `17_25`'),
DB::raw('SUM(`26_35`) as `26_35`'),
DB::raw('SUM(`36_45`) as `36_45`'),
DB::raw('SUM(`46_50`) as `46_50`'),
DB::raw('SUM(`50_>`) as `50_>`')
)
->when(!empty($p_unit), function ($query) use ($p_unit) {
return $query->where('unit', $p_unit);
})
->groupBy('bulan', 'tahun')
->orderByDesc('bulan')
->limit(1)
->get();
$result = [
[
'name' => '17-25',
'y' => (int) ($data[0]->{'17_25'} ?? 0),
'color' => '#007bff'
],
[
'name' => '26-35',
'y' => (int) ($data[0]->{'26_35'} ?? 0),
'color' => '#28a745'
],
[
'name' => '36-45',
'y' => (int) ($data[0]->{'36_45'} ?? 0),
'color' => '#ffc107'
],
[
'name' => '46-50',
'y' => (int) ($data[0]->{'46_50'} ?? 0),
'color' => '#fd7e14'
],
[
'name' => '50+',
'y' => (int) ($data[0]->{'50_>'} ?? 0),
'color' => '#dc3545'
]
];
return response()->json([
'status' => 'success',
'data' => $result,
'bulan' => $data[0]->bulan ?? '',
'tahun' => $data[0]->tahun ?? ''
]);
}
public function getstatussdmunit(Request $request)
{
$p_unit = $request->input('unit', '');
$data = DB::table('data_golongan_sdm')
->select(
'bulan',
'tahun',
DB::raw('SUM(os) as OS'),
DB::raw('SUM(pkwt) as PKWT'),
DB::raw('SUM(ktng) as KTNG'),
DB::raw('SUM(karpel) as KARPEL'),
DB::raw('SUM(karpim) as KARPIM')
)
->when(!empty($p_unit), function ($query) use ($p_unit) {
return $query->where('unit', $p_unit);
})
->groupBy('bulan', 'tahun')
->orderByDesc('bulan')
->limit(1)
->get();
$result = [
[
'name' => 'OS',
'y' => (int) ($data[0]->OS ?? 0),
'color' => '#007bff'
],
[
'name' => 'PKWT',
'y' => (int) ($data[0]->PKWT ?? 0),
'color' => '#ffc107'
],
[
'name' => 'KTNG',
'y' => (int) ($data[0]->KTNG ?? 0),
'color' => '#28a745'
],
[
'name' => 'KARPEL',
'y' => (int) ($data[0]->KARPEL ?? 0),
'color' => '#17a2b8'
],
[
'name' => 'KARPIM',
'y' => (int) ($data[0]->KARPIM ?? 0),
'color' => '#e83e8c'
]
];
return response()->json([
'status' => 'success',
'data' => $result,
'bulan' => $data[0]->bulan ?? '',
'tahun' => $data[0]->tahun ?? ''
]);
}
public function getgolongansdmunit(Request $request)
{
$p_unit = $request->input('unit', '');
$data = DB::table('data_jabatan_sdm')
->select(
'bulan',
'tahun',
DB::raw('SUM(NG) as NG'),
DB::raw('SUM(`6A_7B`) as `6A_7B`'),
DB::raw('SUM(`8A_10`) as `8A_10`'),
DB::raw('SUM(`11_14`) as `11_14`'),
DB::raw('SUM(`15_16`) as `15_16`'),
DB::raw('SUM(`17_18`) as `17_18`')
)
->when(!empty($p_unit), function ($query) use ($p_unit) {
return $query->where('unit', $p_unit);
})
->groupBy('bulan', 'tahun')
->orderByDesc('bulan')
->limit(1)
->get();
$result = [
[
'name' => 'NG',
'y' => (int) ($data[0]->NG ?? 0),
'color' => '#007bff'
],
[
'name' => '6A-7B',
'y' => (int) ($data[0]->{'6A_7B'} ?? 0),
'color' => '#28a745'
],
[
'name' => '8A-10',
'y' => (int) ($data[0]->{'8A_10'} ?? 0),
'color' => '#ffc107'
],
[
'name' => '11-14',
'y' => (int) ($data[0]->{'11_14'} ?? 0),
'color' => '#fd7e14'
],
[
'name' => '15-16',
'y' => (int) ($data[0]->{'15_16'} ?? 0),
'color' => '#dc3545'
],
[
'name' => '17-18',
'y' => (int) ($data[0]->{'17_18'} ?? 0),
'color' => '#6f42c1'
]
];
return response()->json([
'status' => 'success',
'data' => $result,
'bulan' => $data[0]->bulan ?? '',
'tahun' => $data[0]->tahun ?? ''
]);
}
public function getLoginHistory() {
if(!\Auth::check()) return redirect('user/login');
$history = \DB::table('tb_users as a')
->leftJoin('tb_groups as b', 'a.group_id', '=', 'b.group_id')
->leftJoin('master_kebun as c', 'c.kode_kebun', '=', 'a.first_name')
->leftJoin('master_rayon as d', 'd.kode_rayon', '=', 'a.first_name')
->select('a.email', \DB::raw('IFNULL(c.nama_kebun, IFNULL(d.nama_rayon, a.first_name)) as unit'), 'b.name as group_name', 'a.last_login')
->orderBy('a.last_login', 'desc')
->get();
//rangking history berasal dari tb_login_history
$topten = \DB::table('tb_login_history as a')
->leftJoin('tb_users as b', 'a.user_id', '=', 'b.id')
->leftJoin('tb_groups as c', 'b.group_id', '=', 'c.group_id')
->leftJoin('master_kebun as d', 'd.kode_kebun', '=', 'b.first_name')
->leftJoin('master_rayon as e', 'e.kode_rayon', '=', 'b.first_name')
->select('b.email', \DB::raw('IFNULL(d.nama_kebun, IFNULL(e.nama_rayon, b.first_name)) as unit'), 'c.name as group_name', \DB::raw('COUNT(a.id) as login_count'))
->where('b.group_id', '!=', 1)
->groupBy('a.user_id')
->orderBy('login_count', 'desc')
->limit(10)
->get();
$this->data = array(
'pageTitle' => 'User Login History',
'pageNote' => 'Riwayat login user',
'history' => $history,
'topten' => $topten,
);
return view('user.loginhistory', $this->data);
}
}
 . ')
';
$cell .= '
';
$cellClass = 'no-foto';
}
$cell .= ' . ')
 . ')
 . ')
 . ')
