model = new Monitoringpica(); $this->info = $this->model->makeInfo( $this->module); $this->access = array(); $this->data = array_merge(array( 'pageTitle' => $this->info['title'], 'pageNote' => $this->info['note'], 'pageModule'=> 'monitoringpica', 'return' => self::returnUrl() ),$this->data); } public function index( Request $request ) { // Make Sure users Logged if(!\Auth::check()) return redirect('user/login')->with('status', 'error')->with('message','You are not login'); // Render into template $this->data['total_petak'] = \DB::table('master_petak')->count(); $this->data['total_pica'] = \DB::table('tx_pica_pi as a') ->join('tx_pica_pi_problem as b', 'b.id_pica_pi', '=', 'a.id') ->select('a.lahan_id') ->distinct() ->groupBy('a.lahan_id') ->get() ->count(); $this->data['total_pica_corrective'] = \DB::table('tx_pica_pi as a') ->join('tx_pica_pi_problem as b', 'b.id_pica_pi', '=', 'a.id') ->join('tx_pica_pi_corrective as c', 'c.id_pica_pi_problem', '=', 'b.id_problem') ->select('a.lahan_id') ->distinct() ->groupBy('a.lahan_id') ->get() ->count(); return view( $this->module.'.index',$this->data); } public function getDataByParams(Request $request) { $kode_afd = $request->input('kode_afd', ''); $problem_group = $request->input('problem_group', ''); $area_problem = $request->input('area', ''); $results = \DB::table('tx_pica_pi as a') ->join('tx_pica_pi_problem as b', 'b.id_pica_pi', '=', 'a.id') ->join('master_problem_pica as c', 'c.id', '=', 'b.id_problem') ->join('master_petak as d', 'd.lahan_id', '=', 'a.lahan_id') ->join('master_kebun as e', function($join) { $join->on('e.kode_kebun', '=', \DB::raw('d.plant COLLATE utf8mb4_unicode_ci')); }) ->join('master_rayon as f', function($join) { $join->on('f.kode_rayon', '=', \DB::raw('d.kode_rayon COLLATE utf8mb4_unicode_ci')); }) ->join('master_afd as g', 'g.kode_afd', '=', 'd.afd_new') ->select( 'e.kode_kebun', 'f.kode_rayon', 'g.kode_afd', 'a.lahan_id', 'c.problem', 'a.problem_group', 'a.area_problem', 'b.id_problem' ) ->where('g.kode_afd', $kode_afd) ->where('a.problem_group', $problem_group) ->where('a.area_problem', $area_problem) ->groupBy('c.problem') ->distinct() ->get(); if ($results->isEmpty()) { $html = '

'; $html .= '

Tidak ada data problem yang ditemukan berdasarkan filter yang dipilih.

'; $html .= '

'; } else { $html = '

'; $html .= '

Berikut adalah problem yang terjadi berdasarkan filter yang dipilih:

'; $html .= '

' . htmlspecialchars($row->problem) . '

'; $html .= '

'; } return response($html, 200)->header('Content-Type', 'text/html'); } public function getColumnChartData(Request $request) { $kode_kebun = $request->input('kode_kebun', ''); $problem_group = $request->input('problem_group', ''); $area_problem = $request->input('area', ''); $results = \DB::table('tx_pica_pi as a') ->join('tx_pica_pi_problem as b', 'b.id_pica_pi', '=', 'a.id') ->join('master_problem_pica as c', 'c.id', '=', 'b.id_problem') ->join('master_petak as d', 'd.lahan_id', '=', 'a.lahan_id') ->join('master_kebun as e', function($join) { $join->on('e.kode_kebun', '=', \DB::raw('d.plant COLLATE utf8mb4_unicode_ci')); }) ->join('master_rayon as f', function($join) { $join->on('f.kode_rayon', '=', \DB::raw('d.kode_rayon COLLATE utf8mb4_unicode_ci')); }) ->join('master_afd as g', 'g.kode_afd', '=', 'd.afd_new') ->select('c.problem', \DB::raw('COUNT(DISTINCT a.lahan_id) as jumlah_petak')) ->where('e.kode_kebun', $kode_kebun) ->where('a.problem_group', $problem_group) ->where('a.area_problem', $area_problem) ->groupBy('c.problem') ->get(); $categories = []; $data = []; foreach ($results as $row) { $categories[] = $row->problem; $data[] = (int) $row->jumlah_petak; } return response()->json([ 'categories' => $categories, 'data' => $data ]); } public function getStackedColumnChartData(Request $request) { $kode_kebun = $request->input('kode_kebun', ''); $problem_group = $request->input('problem_group', ''); $area_problem = $request->input('area', ''); $results = \DB::table('tx_pica_pi as a') ->join('tx_pica_pi_problem as b', 'b.id_pica_pi', '=', 'a.id') ->join('master_problem_pica as c', 'c.id', '=', 'b.id_problem') ->join('master_petak as d', 'd.lahan_id', '=', 'a.lahan_id') ->join('master_kebun as e', function($join) { $join->on('e.kode_kebun', '=', \DB::raw('d.plant COLLATE utf8mb4_unicode_ci')); }) ->join('master_rayon as f', function($join) { $join->on('f.kode_rayon', '=', \DB::raw('d.kode_rayon COLLATE utf8mb4_unicode_ci')); }) ->join('master_afd as g', 'g.kode_afd', '=', 'd.afd_new') ->select( 'f.nama_rayon', 'c.problem', \DB::raw('COUNT(DISTINCT a.lahan_id) as jumlah_petak') ) ->where('e.kode_kebun', $kode_kebun) ->where('a.problem_group', $problem_group) ->where('a.area_problem', $area_problem) ->groupBy('f.nama_rayon', 'c.problem') ->get(); $rayons = []; $problems = []; $dataMap = []; foreach ($results as $row) { $rayons[$row->nama_rayon] = true; $problems[$row->problem] = true; $dataMap[$row->problem][$row->nama_rayon] = (int) $row->jumlah_petak; } $categories = array_keys($rayons); $series = []; foreach (array_keys($problems) as $problem) { $data = []; foreach ($categories as $rayon) { $data[] = isset($dataMap[$problem][$rayon]) ? $dataMap[$problem][$rayon] : 0; } $series[] = [ 'name' => $problem, 'data' => $data ]; } return response()->json([ 'categories' => $categories, 'series' => $series ]); } public function getTableDataByParams(Request $request) { $kode_afd = $request->input('kode_afd', ''); $problem_group = $request->input('problem_group', ''); $area_problem = $request->input('area', ''); $id_problem = $request->input('id_problem', ''); $results = \DB::table('tx_pica_pi as a') ->join('tx_pica_pi_problem as b', 'b.id_pica_pi', '=', 'a.id') ->join('master_problem_pica as c', 'c.id', '=', 'b.id_problem') ->join('master_petak as d', 'd.lahan_id', '=', 'a.lahan_id') ->join('master_kebun as e', function($join) { $join->on('e.kode_kebun', '=', \DB::raw('d.plant COLLATE utf8mb4_unicode_ci')); }) ->join('master_rayon as f', function($join) { $join->on('f.kode_rayon', '=', \DB::raw('d.kode_rayon COLLATE utf8mb4_unicode_ci')); }) ->join('master_afd as g', 'g.kode_afd', '=', 'd.afd_new') ->select( 'a.lahan_id', 'd.nama_kebun', 'd.luas_polig as luas_ha', 'c.problem' ) ->where('g.kode_afd', $kode_afd) ->where('a.problem_group', $problem_group) ->where('a.area_problem', $area_problem) ->where('b.id_problem', $id_problem) ->distinct() ->get(); $html = ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; if ($results->isEmpty()) { $html .= ''; } else { foreach ($results as $row) { $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; } } $html .= '

Lahan ID	Nama Kebun	Luas (Ha)	Problem	Action
'; $html .= ' Tidak ada data ditemukan.'; $html .= '
' . htmlspecialchars($row->lahan_id) . '	' . htmlspecialchars($row->nama_kebun) . '	' . htmlspecialchars($row->luas_ha) . '	' . htmlspecialchars($row->problem) . '

'; return response($html, 200)->header('Content-Type', 'text/html'); } public function getCorrectiveTableData(Request $request) { config(['app.debug' => true]); $lahan_id = $request->input('lahan_id', ''); $id_problem = $request->input('id_problem', ''); $results = \DB::table('tx_pica_pi_corrective as a') ->join('tx_pica_pi_problem as b', 'b.id', '=', 'a.id_pica_pi_problem') ->join('tx_pica_pi as c', 'c.id', '=', 'b.id_pica_pi') ->join('master_problem_pica as e', 'e.id', '=', 'b.id_problem') ->join('master_corective_pica as f', 'f.id', '=', 'a.id_corrective') ->leftJoin('tx_pica_pi_corrective_action as d', 'a.id', '=', 'd.id_pica_pi_correction') ->select( 'c.lahan_id', 'e.problem', 'f.corrective', 'a.tanggal_target', 'a.persen_target', 'd.tgl_penyelesaian', 'd.persen_peyelesaian', 'd.keterangan', 'd.foto' ) ->where('c.lahan_id', $lahan_id) ->where('b.id_problem', $id_problem) ->groupBy('a.id') ->get(); $html = ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; if ($results->isEmpty()) { $html .= ''; } else { foreach ($results as $row) { $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; // Persen Target Bar $persen_target = is_numeric($row->persen_target) ? (int)$row->persen_target : 0; $html .= ''; $html .= ''; // Persen Penyelesaian Bar $persen_penyelesaian = is_numeric($row->persen_peyelesaian) ? (int)$row->persen_peyelesaian : 0; $html .= ''; $html .= ''; if ($row->foto) { $html .= ''; } else { $html .= ''; } $html .= ''; } } $html .= '

Lahan ID	Problem	Corrective	Tanggal Target	Persen Target	Tgl Penyelesaian	Persen Penyelesaian	Keterangan	Foto
'; $html .= ' Tidak ada data ditemukan.'; $html .= '
' . htmlspecialchars($row->lahan_id) . '	' . htmlspecialchars($row->problem) . '	' . htmlspecialchars($row->corrective) . '	' . ($row->tanggal_target ? date('d M Y', strtotime($row->tanggal_target)) : '-') . '	' . $persen_target . '%	'; if ($row->tgl_penyelesaian) { $html .= '' . date('d M Y', strtotime($row->tgl_penyelesaian)) . ''; } else { $html .= '-'; } $html .= '	' . $persen_penyelesaian . '%	' . htmlspecialchars($row->keterangan) . '		-

'; return response($html, 200)->header('Content-Type', 'text/html'); } public function formSasaranPicaKebun() { $this->data['pageTitle'] = 'Sasaran Pica Kebun'; $this->data['pageNote'] = 'Form untuk melihat sasaran Pica di tingkat kebun berdasarkan kategori, area problem, dan problem group.'; return view($this->module.'.sasaran_kebun', $this->data); } public function getSasaranPicaKebun(Request $request) { $kode_kebun = $request->input('kebun', ''); $kategori = $request->input('kategori', ''); $area_problem = $request->input('area', ''); $results = \DB::select('CALL sp_sasaran_pica_kebun(?, ?, ?)', [ $kode_kebun, $kategori, $area_problem ]); $html = '

'; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; if (empty($results)) { $html .= ''; } else { foreach ($results as $row) { $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $months = ['januari', 'februari', 'maret', 'april', 'mei', 'juni', 'juli', 'agustus', 'september', 'oktober', 'november', 'desember']; foreach ($months as $month) { $realMonthProperty = 'real_' . $month; $keteranganProperty = 'ket_' . $month; $monthValue = (float)($row->$month ?? 0); $realValue = (float)($row->$realMonthProperty ?? 0); $percentage = $monthValue > 0 ? round(($realValue / $monthValue) * 100) : 0; $html .= ''; } $html .= ''; $html .= ''; } } $html .= '

Kode Kebun	Kategori	Area Problem	Problem	Corrective Action	Kuanta	Satuan		Januari	Februari	Maret	April	Mei	Juni	Juli	Agustus	September	Oktober	November	Desember	Jumlah
'; $html .= ' Tidak ada data ditemukan.'; $html .= '
' . htmlspecialchars($row->kode_kebun ?? '') . '	' . htmlspecialchars($row->kategori ?? '') . '	' . htmlspecialchars($row->area_problem ?? '') . '	' . htmlspecialchars($row->problem ?? '') . '	' . htmlspecialchars($row->corrective_action ?? '') . '	' . htmlspecialchars($row->kuanta ?? '') . '	' . htmlspecialchars($row->satuan ?? '') . '	Sasaran '; $html .= 'Realisasi '; $html .= 'Persen Capaian'; $html .= '	'; $html .= '' . number_format($monthValue, 1, ',', '.') . ' '; $html .= '' . number_format($realValue, 1, ',', '.') . ' '; $html .= '' . $percentage . '% '; if (!empty($row->$keteranganProperty)) { $html .= ''; } $html .= ' '; if ($monthValue > 0) { $html .= ''; } $html .= '	'; $html .= '' . number_format((float)($row->jumlah ?? 0), 1, ',', '.') . ' '; $html .= '' . number_format((float)($row->jumlah_real ?? 0), 1, ',', '.') . ' '; $jumlahTotal = (float)($row->jumlah ?? 0); $jumlahReal = (float)($row->jumlah_real ?? 0); $persenTotal = $jumlahTotal > 0 ? round(($jumlahReal / $jumlahTotal) * 100) : 0; $html .= '' . $persenTotal . '%'; $html .= '

'; $html .= '

'; $html .= ''; $html .= ''; $html .= ''; return response($html, 200)->header('Content-Type', 'text/html'); } function updateMonthValue(Request $request) { $kode_kebun = $request->input('kode_kebun', ''); $kategori = $request->input('kategori', ''); $area_problem = $request->input('area_problem', ''); $problem = $request->input('problem', ''); $corrective_action = $request->input('corrective_action', ''); $month = $request->input('month', ''); $value = $request->input('value', 0); $keterangan = $request->input('keterangan', ''); try { \DB::table('realisasi_pica_kebun') ->updateOrInsert( [ 'kode_kebun' => $kode_kebun, 'kategori' => $kategori, 'area_problem' => $area_problem, 'problem' => $problem, 'corrective_action' => $corrective_action, ], [ $month => $value, 'ket_' . $month => $keterangan, 'updated_at' => now() ] ); return response()->json(['message' => 'Nilai berhasil diperbarui!']); } catch (\Exception $e) { return response()->json(['message' => 'Terjadi kesalahan saat menyimpan nilai.'], 500); } } public function formSasaranPicaKebunTriwulan() { $this->data['pageTitle'] = 'Sasaran Pica Kebun Triwulan'; $this->data['pageNote'] = 'Form untuk melihat sasaran Pica di tingkat kebun berdasarkan triwulan, kategori, dan area problem.'; return view($this->module.'.sasaran_kebun_triwulan', $this->data); } public function getSasaranPicaKebunTriwulan(Request $request) { $kode_kebun = $request->input('kebun', ''); $kategori = $request->input('kategori', ''); $area_problem = $request->input('area', ''); $triwulan = $request->input('triwulan', ''); $results = \DB::select('CALL sp_pica_kebun_triwulan(?, ?, ?, ?)', [ $kode_kebun, $kategori, $area_problem, $triwulan ]); $html = '

'; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $triwulans = ['tw1', 'tw2', 'tw3', 'tw4']; $triwulanColors = ['#4CAF50', '#45a049', '#3d8b40', '#357a35']; $triwulanLabels = ['TW1', 'TW2', 'TW3', 'TW4']; $displayTriwulans = []; if (!empty($triwulan)) { $displayTriwulans = [$triwulan]; } else { $displayTriwulans = $triwulans; } foreach ($displayTriwulans as $tw) { $index = array_search($tw, $triwulans); $html .= ''; } if (empty($triwulan)) { $html .= ''; } $html .= ''; if (empty($results)) { $colspanCount = 8 + count($displayTriwulans) + (empty($triwulan) ? 1 : 0); $html .= ''; } else { foreach ($results as $row) { $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; $html .= ''; foreach ($displayTriwulans as $tw) { $realTwProperty = 'real_' . $tw; $twValue = (int)($row->$tw ?? 0); $realValue = (int)($row->$realTwProperty ?? 0); $percentage = $twValue > 0 ? round(($realValue / $twValue) * 100) : 0; $html .= ''; } if (empty($triwulan)) { $html .= ''; } $html .= ''; } } $html .= '

Kode Kebun	Kategori	Area Problem	Problem	Corrective Action	Kuanta	Satuan		' . $triwulanLabels[$index] . '	Jumlah
'; $html .= ' Tidak ada data ditemukan.'; $html .= '
' . htmlspecialchars($row->kode_kebun ?? '') . '	' . htmlspecialchars($row->kategori ?? '') . '	' . htmlspecialchars($row->area_problem ?? '') . '	' . htmlspecialchars($row->problem ?? '') . '	' . htmlspecialchars($row->corrective_action ?? '') . '	' . htmlspecialchars($row->kuanta ?? '') . '	' . htmlspecialchars($row->satuan ?? '') . '	Sasaran '; $html .= 'Realisasi '; $html .= 'Persen Capaian'; $html .= '	'; $html .= '' . number_format($twValue, 1, ',', '.') . ' '; $html .= '' . number_format($realValue, 1, ',', '.') . ' '; $html .= '' . $percentage . '%'; $html .= '	'; $jumlahTotal = (float)($row->jumlah ?? 0); $jumlahReal = (float)($row->jumlah_real ?? 0); $html .= '' . number_format($jumlahTotal, 1, ',', '.') . ' '; $html .= '' . number_format($jumlahReal, 1, ',', '.') . ' '; $persenTotal = $jumlahTotal > 0 ? round(($jumlahReal / $jumlahTotal) * 100) : 0; $html .= '' . $persenTotal . '%'; $html .= '

'; $html .= '