fix deploy code and readme

fix:chatbot ui

.env.example

@@ -68,3 +68,4 @@ VITE_APP_NAME="${APP_NAME}"
 
 API_KEY_GOOGLE="xxxxx"
 SPREAD_SHEET_ID="xxxxx"
+OPENAI_API_KEY="xxxxx"

README.md

@@ -1,66 +1,112 @@
-<p align="center"><a href="https://laravel.com" target="_blank"><img src="https://raw.githubusercontent.com/laravel/art/master/logo-lockup/5%20SVG/2%20CMYK/1%20Full%20Color/laravel-logolockup-cmyk-red.svg" width="400" alt="Laravel Logo"></a></p>
+# Usage icon
 
-<p align="center">
-<a href="https://github.com/laravel/framework/actions"><img src="https://github.com/laravel/framework/workflows/tests/badge.svg" alt="Build Status"></a>
-<a href="https://packagist.org/packages/laravel/framework"><img src="https://img.shields.io/packagist/dt/laravel/framework" alt="Total Downloads"></a>
-<a href="https://packagist.org/packages/laravel/framework"><img src="https://img.shields.io/packagist/v/laravel/framework" alt="Latest Stable Version"></a>
-<a href="https://packagist.org/packages/laravel/framework"><img src="https://img.shields.io/packagist/l/laravel/framework" alt="License"></a>
-</p>
+search or pick icon in <a href="https://icon-sets.iconify.design/mingcute/?keyword=mingcute">here</a>
 
-## About Laravel
+# Set up queue for running automatically
 
-Laravel is a web application framework with expressive, elegant syntax. We believe development must be an enjoyable and creative experience to be truly fulfilling. Laravel takes the pain out of development by easing common tasks used in many web projects, such as:
+-   Install Supervisor
 
-- [Simple, fast routing engine](https://laravel.com/docs/routing).
-- [Powerful dependency injection container](https://laravel.com/docs/container).
-- Multiple back-ends for [session](https://laravel.com/docs/session) and [cache](https://laravel.com/docs/cache) storage.
-- Expressive, intuitive [database ORM](https://laravel.com/docs/eloquent).
-- Database agnostic [schema migrations](https://laravel.com/docs/migrations).
-- [Robust background job processing](https://laravel.com/docs/queues).
-- [Real-time event broadcasting](https://laravel.com/docs/broadcasting).
+```
+sudo apt update && sudo apt install supervisor -y
+```
 
-Laravel is accessible, powerful, and provides tools required for large, robust applications.
+-   Create Supervisor Config
 
-## Learning Laravel
+```
+sudo nano /etc/supervisor/conf.d/laravel-worker.conf
 
-Laravel has the most extensive and thorough [documentation](https://laravel.com/docs) and video tutorial library of all modern web application frameworks, making it a breeze to get started with the framework.
+[program:laravel-worker]
+process_name=%(program_name)s_%(process_num)02d
+command=php /home/arifal/development/sibedas-pbg-web/artisan queue:work --queue=default --timeout=40000 --tries=1 --sleep=3
+autostart=true
+autorestart=true
+numprocs=4
+redirect_stderr=true
+stdout_logfile=/home/arifal/development/sibedas-pbg-web/storage/logs/worker.log
+stopasgroup=true
+killasgroup=true
+```
 
-You may also try the [Laravel Bootcamp](https://bootcamp.laravel.com), where you will be guided through building a modern Laravel application from scratch.
+-   Reload Supervisor
 
-If you don't feel like reading, [Laracasts](https://laracasts.com) can help. Laracasts contains thousands of video tutorials on a range of topics including Laravel, modern PHP, unit testing, and JavaScript. Boost your skills by digging into our comprehensive video library.
+```
+sudo supervisorctl reread
+sudo supervisorctl update
+sudo supervisorctl start laravel-worker
+sudo supervisorctl restart laravel-worker
+sudo supervisorctl status
+```
 
-## Laravel Sponsors
+# How to running
 
-We would like to extend our thanks to the following sponsors for funding Laravel development. If you are interested in becoming a sponsor, please visit the [Laravel Partners program](https://partners.laravel.com).
+-   Install composer package
 
-### Premium Partners
+```
+composer install
+```
 
-- **[Vehikl](https://vehikl.com/)**
-- **[Tighten Co.](https://tighten.co)**
-- **[WebReinvent](https://webreinvent.com/)**
-- **[Kirschbaum Development Group](https://kirschbaumdevelopment.com)**
-- **[64 Robots](https://64robots.com)**
-- **[Curotec](https://www.curotec.com/services/technologies/laravel/)**
-- **[Cyber-Duck](https://cyber-duck.co.uk)**
-- **[DevSquad](https://devsquad.com/hire-laravel-developers)**
-- **[Jump24](https://jump24.co.uk)**
-- **[Redberry](https://redberry.international/laravel/)**
-- **[Active Logic](https://activelogic.com)**
-- **[byte5](https://byte5.de)**
-- **[OP.GG](https://op.gg)**
+-   Install npm package
 
-## Contributing
+```
+npm install && npm run build
+```
 
-Thank you for considering contributing to the Laravel framework! The contribution guide can be found in the [Laravel documentation](https://laravel.com/docs/contributions).
+-   Create symlinks storage
 
-## Code of Conduct
+```
+php artisan storage:link
+```
 
-In order to ensure that the Laravel community is welcoming to all, please review and abide by the [Code of Conduct](https://laravel.com/docs/contributions#code-of-conduct).
+-   Running migration
 
-## Security Vulnerabilities
+```
+php artisan migrate
+```
 
-If you discover a security vulnerability within Laravel, please send an e-mail to Taylor Otwell via [taylor@laravel.com](mailto:taylor@laravel.com). All security vulnerabilities will be promptly addressed.
+-   Running seeder
 
-## License
+```
+php artisan db:seed
+```
 
-The Laravel framework is open-sourced software licensed under the [MIT license](https://opensource.org/licenses/MIT).
+-   Create view table
+-   excute all sql queries on folder database/view_query
+
+# Add ENV variable
+
+-   API_KEY_GOOGLE
+
+```
+Get api key from google developer console for and turn on spreadsheet api or feaature for google sheet
+```
+
+-   SPREAD_SHEET_ID
+
+```
+Get spreadsheet id from google sheet link
+```
+
+-   OPENAI_API_KEY
+
+```
+Get OpenAI API key from chatgpt subscription
+```
+
+-   ENV
+
+```
+
+API_KEY_GOOGLE="xxxxx"
+SPREAD_SHEET_ID="xxxxx"
+OPENAI_API_KEY="xxxxx"
+
+```
+
+# Technology version
+
+-   php 8.3
+-   Laravel 11
+-   node v22.13.0
+-   npm 10.9.2
+-   mariadb Ver 15.1 Distrib 10.6.18-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper
+-   Ubuntu 24.04

app/Console/Commands/ExecuteScraping.php

@@ -2,6 +2,7 @@
 
 namespace App\Console\Commands;
 
+use App\Jobs\SyncronizeSIMBG;
 use App\Services\ServiceSIMBG;
 use Illuminate\Console\Command;
 use \Illuminate\Support\Facades\Log;
@@ -28,13 +29,12 @@ class ExecuteScraping extends Command
 
     private $service_simbg;
 
-    public function __construct(ServiceSIMBG $service_simbg){
-        $this->service_simbg = $service_simbg;
+    public function __construct(){
         parent::__construct();
     }
     public function handle()
     {
+        SyncronizeSIMBG::dispatch()->onQueue('default');
         Log::info("running scheduler daily scraping");
-        $this->service_simbg->syncTaskList();
     }
 }

app/Http/Controllers/Api/AdvertisementController.php

@@ -199,7 +199,7 @@ class AdvertisementController extends Controller
 
     public function downloadExcelAdvertisement()
     {
-        $filePath = storage_path('app/public/templates/template_reklame.xlsx');
+        $filePath = public_path('templates/template_reklame.xlsx');
 
         // Cek apakah file ada
         if (!file_exists($filePath)) {

app/Http/Controllers/Api/BigDataResumeController.php

@@ -0,0 +1,281 @@
+<?php
+
+namespace App\Http\Controllers\Api;
+
+use App\Http\Controllers\Controller;
+use App\Http\Resources\BigdataResumeResource;
+use App\Models\BigdataResume;
+use App\Models\DataSetting;
+use Illuminate\Http\Request;
+use Illuminate\Support\Facades\Log;
+
+class BigDataResumeController extends Controller
+{
+    /**
+     * Display a listing of the resource.
+     */
+    public function index(Request $request)
+    {
+        try{
+            $filterDate = $request->get("filterByDate");
+
+            if (!$filterDate || $filterDate === "latest") {
+                $big_data_resume = BigdataResume::where('year', now()->year)->latest()->first();
+                if (!$big_data_resume) {
+                    return $this->response_empty_resume();
+                }
+            } else {
+                $big_data_resume = BigdataResume::whereDate('created_at', $filterDate)
+                    ->orderBy('id', 'desc')
+                    ->first();
+
+                if (!$big_data_resume) {
+                    return $this->response_empty_resume();
+                }
+            }
+
+            $data_settings = DataSetting::all();
+            if($data_settings->isEmpty()){
+                return response()->json(['message' => 'No data setting found']);
+            }
+
+            function cleanNumber($value) {
+            return floatval(str_replace('.', '', $value));
+            }
+
+            $target_pad = floatval(optional($data_settings->where('key', 'TARGET_PAD')->first())->value);
+            $realisasi_terbit_pbg_sum = cleanNumber(optional($data_settings->where('key', 'REALISASI_TERBIT_PBG_SUM')->first())->value);
+            $realisasi_terbit_pbg_count = cleanNumber(optional($data_settings->where('key', 'REALISASI_TERBIT_PBG_COUNT')->first())->value);
+            $menunggu_klik_dpmptsp_sum = cleanNumber(optional($data_settings->where('key', 'MENUNGGU_KLIK_DPMPTSP_SUM')->first())->value);
+            $menunggu_klik_dpmptsp_count = cleanNumber(optional($data_settings->where('key', 'MENUNGGU_KLIK_DPMPTSP_COUNT')->first())->value);
+            $proses_dinas_teknis_sum = cleanNumber(optional($data_settings->where('key', 'PROSES_DINAS_TEKNIS_SUM')->first())->value);
+            $proses_dinas_teknis_count = cleanNumber(optional($data_settings->where('key', 'PROSES_DINAS_TEKNIS_COUNT')->first())->value);
+            
+            $tata_ruang = $big_data_resume->spatial_sum;
+            $kekurangan_potensi = $target_pad - $big_data_resume->potention_sum;
+
+            // percentage kekurangan potensi
+            $kekurangan_potensi_percentage = $target_pad > 0 && $target_pad > 0 
+            ? round(($kekurangan_potensi / $target_pad) * 100, 2) : 0;
+
+            // percentage total potensi
+            $total_potensi_percentage = $big_data_resume->potention_sum > 0 && $target_pad > 0 
+            ? round(($big_data_resume->potention_sum / $target_pad) * 100, 2) : 0;
+            
+            // percentage verified document
+            $verified_percentage = $big_data_resume->verified_sum > 0 && $big_data_resume->potention_sum > 0 
+            ? round(($big_data_resume->verified_sum / $big_data_resume->potention_sum) * 100, 2) : 0;
+
+            // percentage non-verified document
+            $non_verified_percentage = $big_data_resume->non_verified_sum > 0 && $big_data_resume->potention_sum > 0 
+            ? round(($big_data_resume->non_verified_sum / $big_data_resume->potention_sum) * 100, 2) : 0;
+
+            // percentage business document
+            $business_percentage = $big_data_resume->business_sum > 0 && $big_data_resume->non_verified_sum > 0 
+            ? round(($big_data_resume->business_sum / $big_data_resume->non_verified_sum) * 100, 2) : 0;
+
+            // percentage non-business document
+            $non_business_percentage = $big_data_resume->non_business_sum > 0 && $big_data_resume->potention_sum > 0 
+            ? round(($big_data_resume->non_business_sum / $big_data_resume->potention_sum) * 100, 2) : 0;
+
+            // percentage tata ruang
+            $tata_ruang_percentage = $tata_ruang > 0 && $big_data_resume->potention_sum > 0 
+            ? round(($tata_ruang / $big_data_resume->potention_sum) * 100, 2) : 0;
+
+            // percentage realisasi terbit pbg
+            $realisasi_terbit_percentage = $big_data_resume->verified_sum > 0 && $realisasi_terbit_pbg_sum > 0
+            ? round(($realisasi_terbit_pbg_sum / $big_data_resume->verified_sum) * 100, 2) : 0;
+
+            // percentage menunggu klik dpmptsp
+            $menunggu_klik_dpmptsp_percentage = $big_data_resume->verified_sum > 0 && $menunggu_klik_dpmptsp_sum > 0
+            ? round(($menunggu_klik_dpmptsp_sum / $big_data_resume->verified_sum) * 100, 2) : 0;
+
+            // percentage proses_dinas_teknis
+            $proses_dinas_teknis_percentage = $big_data_resume->verified_sum > 0 && $proses_dinas_teknis_sum > 0
+            ? round(($proses_dinas_teknis_sum / $big_data_resume->verified_sum) * 100, 2) : 0;
+
+            $result = [
+                'target_pad' => [
+                    'sum' => $target_pad,
+                    'percentage' => 100,
+                ],
+                'tata_ruang' => [
+                    'sum' => $big_data_resume->spatial_sum,
+                    'count' => $big_data_resume->spatial_count,
+                    'percentage' => $tata_ruang_percentage,
+                ],
+                'kekurangan_potensi' => [
+                    'sum' => $kekurangan_potensi,
+                    'percentage' => $kekurangan_potensi_percentage
+                ],
+                'total_potensi' => [
+                    'sum' => (float) $big_data_resume->potention_sum,
+                    'count' => $big_data_resume->potention_count,
+                    'percentage' => $total_potensi_percentage
+                ],
+                'verified_document' => [
+                    'sum' => (float) $big_data_resume->verified_sum,
+                    'count' => $big_data_resume->verified_count,
+                    'percentage' => $verified_percentage
+                ],
+                'non_verified_document' => [
+                    'sum' => (float) $big_data_resume->non_verified_sum,
+                    'count' => $big_data_resume->non_verified_count,
+                    'percentage' => $non_verified_percentage
+                ],
+                'business_document' => [
+                    'sum' => (float) $big_data_resume->business_sum,
+                    'count' => $big_data_resume->business_count,
+                    'percentage' => $business_percentage
+                ],
+                'non_business_document' => [
+                    'sum' => (float) $big_data_resume->non_business_sum,
+                    'count' => $big_data_resume->non_business_count,
+                    'percentage' => $non_business_percentage
+                ],
+                'realisasi_terbit' => [
+                    'sum' => $realisasi_terbit_pbg_sum,
+                    'count' => $realisasi_terbit_pbg_count,
+                    'percentage' => $realisasi_terbit_percentage
+                ],
+                'menunggu_klik_dpmptsp' => [
+                    'sum' => $menunggu_klik_dpmptsp_sum,
+                    'count' => $menunggu_klik_dpmptsp_count,
+                    'percentage' => $menunggu_klik_dpmptsp_percentage
+                ],
+                'proses_dinas_teknis' => [
+                    'sum' => $proses_dinas_teknis_sum,
+                    'count' => $proses_dinas_teknis_count,
+                    'percentage' => $proses_dinas_teknis_percentage
+                ]
+            ];
+            return response()->json($result);
+        }catch(\Exception $e){
+            return response()->json(['message' => 'Error when fetching data'], 500);
+        }
+    }
+
+
+
+    public function bigdata_report(Request $request){
+        try{
+            $query = BigdataResume::query()->orderBy('id', 'desc');
+        
+            if($request->filled('search')){
+                $query->where('year', 'LIKE', '%'.$request->input('search').'%');
+            }
+
+            $query = $query->paginate(config('app.paginate_per_page', 50));
+            return  BigdataResumeResource::collection($query)->response()->getData(true);
+        }catch(\Exception $e){
+            Log::error($e->getMessage());
+            return response()->json(['message' => 'Error when fetching data'], 500);
+        }
+    }
+
+    public function payment_recaps(Request $request)
+    {
+        try {
+            $query = BigdataResume::query()->orderBy('id', 'desc');
+
+            if ($request->filled('date')) {
+                $query->where('year', 'LIKE', '%' . $request->input('search') . '%');
+            }
+
+            $data = $query->paginate(10);
+
+            // Restructure response
+            $transformedData = [];
+
+            foreach ($data as $item) {
+                $createdAt = $item->created_at;
+                $id = $item->id;
+
+                foreach ($item->toArray() as $key => $value) {
+                    // Only include columns with "sum" in their names
+                    if (strpos($key, 'sum') !== false) {
+                        $transformedData[] = [
+                            'id' => $id,
+                            'category' => $key,
+                            'nominal' => $value,
+                            'created_at' => $createdAt,
+                        ];
+                    }
+                }
+            }
+
+            return response()->json([
+                'data' => $transformedData, // Flat array
+                'pagination' => [
+                    'total' => $data->total(),
+                    'per_page' => $data->perPage(),
+                    'current_page' => $data->currentPage(),
+                    'last_page' => $data->lastPage(),
+                ]
+            ]);
+        } catch (\Exception $e) {
+            Log::error($e->getMessage());
+            return response()->json(['message' => 'Error when fetching data'], 500);
+        }
+    }
+
+
+    private function response_empty_resume(){
+        $result = [
+            'target_pad' => [
+                'sum' => 0,
+                'percentage' => 100,
+            ],
+            'tata_ruang' => [
+                'sum' => 0,
+                'percentage' => 0,
+            ],
+            'kekurangan_potensi' => [
+                'sum' => 0,
+                'percentage' => 0
+            ],
+            'total_potensi' => [
+                'sum' => 0,
+                'count' => 0,
+                'percentage' => 0
+            ],
+            'verified_document' => [
+                'sum' => 0,
+                'count' => 0,
+                'percentage' => 0
+            ],
+            'non_verified_document' => [
+                'sum' => 0,
+                'count' => 0,
+                'percentage' => 0
+            ],
+            'business_document' => [
+                'sum' => 0,
+                'count' => 0,
+                'percentage' => 0
+            ],
+            'non_business_document' => [
+                'sum' => 0,
+                'count' => 0,
+                'percentage' => 0
+            ],
+            'realisasi_terbit' => [
+                'sum' => 0,
+                'count' => 0,
+                'percentage' => 0
+            ],
+            'menunggu_klik_dpmptsp' => [
+                'sum' => 0,
+                'count' => 0,
+                'percentage' => 0
+            ],
+            'proses_dinas_teknis' => [
+                'sum' => 0,
+                'count' => 0,
+                'percentage' => 0
+            ]
+        ];
+
+        return response()->json($result);
+    }
+}

app/Http/Controllers/Api/BusinessOrIndustriesController.php

@@ -9,6 +9,7 @@ use App\Models\BusinessOrIndustry;
 use Illuminate\Http\Request;
 use Maatwebsite\Excel\Facades\Excel;
 use \Illuminate\Support\Facades\Validator;
+use App\Http\Requests\ExcelUploadRequest;
 class BusinessOrIndustriesController extends Controller
 {
     /**
@@ -30,7 +31,7 @@ class BusinessOrIndustriesController extends Controller
             });
         }
 
-        return response()->json($query->paginate());
+        return response()->json($query->paginate(config('app.paginate_per_page', 50)));
     }
 
     /**
@@ -79,29 +80,15 @@ class BusinessOrIndustriesController extends Controller
         }
     }
 
-    public function upload(Request $request){
-
-        if ($request->hasFile('file')) {
-            $file = $request->file('file');
-        }
-
-        // Validasi file
-        $validator = Validator::make($request->all(), [
-            'file' => 'required|mimes:xlsx,xls|max:102400', // Max 100MB
-        ]);
-
-        if ($validator->fails()) {
+    public function upload(ExcelUploadRequest $request){
+        try {
+            if(!$request->hasFile('file')){
                 return response()->json([
-                'message' => 'File validation failed.',
-                'errors' => $validator->errors()
+                   'error' => 'No file provided'
                 ], 400);
             }
 
-        try {
-            // Ambil file dari request
             $file = $request->file('file');
-
-            // Menggunakan Laravel Excel untuk mengimpor file
             Excel::import(new BusinessIndustriesImport, $file);
 
             // Jika sukses, kembalikan respons sukses

app/Http/Controllers/Api/ChatbotController.php

@@ -0,0 +1,119 @@
+<?php
+
+namespace App\Http\Controllers\Api;
+
+use Illuminate\Http\Request;
+use App\Services\OpenAIService;
+use App\Http\Controllers\Controller;
+use Illuminate\Http\Response;
+use Illuminate\Support\Facades\DB;
+use Illuminate\Support\Facades\Log;
+
+class ChatbotController extends Controller
+{
+    protected $openAIService;
+
+    public function __construct(OpenAIService $openAIService)
+    {
+        $this->openAIService = $openAIService;
+    }
+
+    public function generateText(Request $request)
+    {
+        $request->validate([
+            'tab_active' => 'required|string',
+            'prompt' => 'required|string',
+        ]);
+
+        $tab_active = $request->input('tab_active');
+        $main_content = match ($tab_active) {
+            "count-retribusi" => "RETRIBUTION",
+            "document-validation" => "DOCUMENT VALIDATION",
+            "data-information" => "DATA SUMMARY",
+            default => "UNKNOWN",
+        };
+
+        $chatHistory = $request->input('chatHistory');
+        // Log::info('Chat history sebelum disimpan:', ['history' => $chatHistory]);
+
+        if ($main_content === "UNKNOWN") {
+            return response()->json(['response' => 'Invalid tab_active value.'], 400);
+        }
+
+        // info($main_content);
+
+        $queryResponse = $this->openAIService->generateQueryBasedMainContent($request->input('prompt'), $main_content, $chatHistory);
+        
+        $firstValidation = $this->openAIService->validateSyntaxQuery($queryResponse);
+        $secondValidation = $this->openAIService->validateSyntaxQuery($queryResponse);
+
+        $formattedResultQuery = "[]";
+        $queryResponse = str_replace(['```sql', '```'], '', $queryResponse);
+        $resultQuery = DB::select($queryResponse);
+        $formattedResultQuery = json_encode($resultQuery, JSON_PRETTY_PRINT);
+        // info($formattedResultQuery);
+        
+        $nlpResult = $this->openAIService->generateNLPFromQuery($request->input('prompt'), $formattedResultQuery);
+        $finalGeneratedText =$this->openAIService->generateFinalText($nlpResult);
+        return response()->json(['response' => $finalGeneratedText, 'nlpResponse' => $queryResponse]);
+    }
+
+    public function mainGenerateText(Request $request)
+    {
+        // Log hanya data yang relevan
+        info("Received prompt: " . $request->input('prompt'));
+
+        // Validasi input
+        $request->validate([
+            'prompt' => 'required|string',
+        ]);
+
+        try {
+            // Panggil service untuk generate text
+            $classifyResponse = $this->openAIService->classifyMainGenerateText($request->input('prompt'));
+            info($classifyResponse);
+
+            // Pastikan hasil klasifikasi valid sebelum melanjutkan
+            $validCategories = [
+                'reklame', 'business_or_industries', 'customers', 
+                'pbg', 'retribusi', 'spatial_plannings', 
+                'tourisms', 'umkms'
+            ];
+
+            if (!in_array($classifyResponse, $validCategories)) {
+                return response()->json([
+                    'error' => ''
+                ], 400);
+            }
+
+            $chatHistory = $request->input('chatHistory');
+            Log::info('Chat history sebelum disimpan:', ['history' => $chatHistory]);
+
+            $queryResponse = $this->openAIService->createMainQuery($classifyResponse, $request->input('prompt'), $chatHistory);
+            info($queryResponse);
+
+            $firstValidation = $this->openAIService->validateSyntaxQuery($queryResponse);
+            $secondValidation = $this->openAIService->validateSyntaxQuery($queryResponse);
+
+            $formattedResultQuery = "[]";
+            
+            $queryResponse = str_replace(['```sql', '```'], '', $queryResponse);
+            $queryResult = DB::select($queryResponse);
+            
+            $formattedResultQuery = json_encode($queryResult, JSON_PRETTY_PRINT);
+            
+            $nlpResult = $this->openAIService->generateNLPFromQuery($request->input('prompt'), $formattedResultQuery);
+            $finalGeneratedText =$this->openAIService->generateFinalText($nlpResult);
+            
+            return response()->json(['response' => $finalGeneratedText, 'nlpResponse' => $queryResponse]);
+        } catch (\Exception $e) {
+            // Tangani error dan log exception
+            \Log::error("Error generating text: " . $e->getMessage());
+
+            return response()->json([
+                'error' => ''
+            ], 500);
+        }
+    }
+
+}

app/Http/Controllers/Api/CustomersController.php

@@ -22,9 +22,9 @@ class CustomersController extends Controller
         if ($request->has('search') &&!empty($request->get('search'))) {
             $query = $query->where('nomor_pelanggan', 'LIKE', '%'.$request->get('search').'%')
                 ->orWhere('nama', 'LIKE', '%'.$request->get('search').'%')
-                ->orWhere('kota_palayanan', 'LIKE', '%'.$request->get('search').'%');
+                ->orWhere('kota_pelayanan', 'LIKE', '%'.$request->get('search').'%');
         }
-        return CustomersResource::collection($query->paginate());
+        return CustomersResource::collection($query->paginate(config('app.paginate_per_page', 50)));
     }
 
     /**

app/Http/Controllers/Api/GlobalSettingsController.php

@@ -34,7 +34,7 @@ class GlobalSettingsController extends Controller
         try {
             $data = GlobalSetting::create($request->validated());
             return new GlobalSettingResource($data);
-        } catch (\Exception $e) {
+        } catch (Exception $e) {
             return $this->resError($e->getMessage(), null, $e->getCode());
         }
     }

app/Http/Controllers/Api/GoogleSheetController.php

@@ -3,33 +3,15 @@
 namespace App\Http\Controllers\Api;
 
 use App\Http\Controllers\Controller;
-use App\Services\GoogleSheetService;
 use Illuminate\Http\Request;
 
 class GoogleSheetController extends Controller
 {
-    protected $googleSheetService;
-    public function __construct(GoogleSheetService $googleSheetService){
-        $this->googleSheetService = $googleSheetService;
-    }
-    /**
-     * Display a listing of the resource.
-     */
-    public function index()
+    public function index(Request $request)
     {
-        $dataCollection = $this->googleSheetService->getSheetDataCollection();
-        $result = [
-            "last_row" => $this->googleSheetService->getLastRowByColumn("C"),
-            "last_column" => $this->googleSheetService->getLastColumn(),
-            "header" => $this->googleSheetService->getHeader(),
-            "data_collection" => $dataCollection
-        ];
-        return response()->json($result);
+        
     }
 
-    /**
-     * Store a newly created resource in storage.
-     */
     public function store(Request $request)
     {
         //

app/Http/Controllers/Api/ImportDatasourceController.php

@@ -24,7 +24,7 @@ class ImportDatasourceController extends Controller
             $search = $request->get("search");
             $query->where('status', 'like', "%".$search."%");
         }
-        return ImportDatasourceResource::collection($query->paginate());
+        return ImportDatasourceResource::collection($query->paginate(config('app.paginate_per_page', 50)));
     }
 
     public function checkImportDatasource(){

app/Http/Controllers/Api/LackOfPotentialController.php

@@ -0,0 +1,34 @@
+<?php
+
+namespace App\Http\Controllers\Api;
+
+use App\Http\Controllers\Controller;
+use App\Models\Advertisement;
+use App\Models\Customer;
+use App\Models\SpatialPlanning;
+use Illuminate\Http\Request;
+use App\Models\TourismBasedKBLI;
+
+class LackOfPotentialController extends Controller
+{
+
+    public function count_lack_of_potential(){
+        try{
+            $total_reklame = Advertisement::count();
+            $total_pdam = Customer::count();
+            $total_tata_ruang = SpatialPlanning::count();
+            $data_report_tourism = TourismBasedKBLI::all();
+
+            return response()->json([
+                'total_reklame' => $total_reklame,
+                'total_pdam' => $total_pdam,
+                'total_tata_ruang' => $total_tata_ruang,
+                'data_report' => $data_report_tourism,
+            ], 200);
+        }catch(\Exception $e){
+            return response()->json([
+                'message' => 'Error: '.$e->getMessage()
+            ], 500);
+        }
+    }
+}

app/Http/Controllers/Api/MenusController.php

@@ -3,8 +3,11 @@
 namespace App\Http\Controllers\Api;
 
 use App\Http\Controllers\Controller;
+use App\Http\Requests\MenuRequest;
+use App\Http\Resources\MenuResource;
 use App\Models\Menu;
 use Illuminate\Http\Request;
+use Illuminate\Support\Facades\Log;
 
 class MenusController extends Controller
 {
@@ -13,21 +16,27 @@ class MenusController extends Controller
      */
     public function index(Request $request)
     {
-        $query = Menu::query();
+        $query = Menu::query()->orderBy('id', 'desc');
 
         if($request->has("search") && !empty($request->get("search"))){
             $query = $query->where("name", "like", "%".$request->get("search")."%");
         }
 
-        return response()->json($query->paginate());
+        return response()->json($query->paginate(config('app.paginate_per_page', 50)));
     }
 
     /**
      * Store a newly created resource in storage.
      */
-    public function store(Request $request)
+    public function store(MenuRequest $request)
     {
-        //
+        try{
+            $menu = Menu::create($request->validated());
+            return response()->json(['message' => 'Menu created successfully', 'data' => new MenuResource($menu)]);
+        }catch(\Exception $e){
+            Log::error($e);
+            return response()->json(['message' => 'Error when creating menu'], 500);
+        }
     }
 
     /**
@@ -35,15 +44,37 @@ class MenusController extends Controller
      */
     public function show(string $id)
     {
-        //
+        try{
+            $menu = Menu::find($id);
+            if($menu){
+                return response()->json(['message' => 'Menu found', 'data' => new MenuResource($menu)]);
+            } else {
+                return response()->json(['message' => 'Menu not found'], 404);
+            }
+        }catch(\Exception $e){
+            Log::error($e);
+            Log::error($e->getMessage());
+            return response()->json(['message' => 'Error when finding menu'], 500);
+        }
     }
 
     /**
      * Update the specified resource in storage.
      */
-    public function update(Request $request, string $id)
+    public function update(MenuRequest $request, string $id)
     {
-        //
+        try{
+            $menu = Menu::findOrFail($id);
+            if($menu){
+                $menu->update($request->validated());
+                return response()->json(['message' => 'Menu updated successfully', 'data' => new MenuResource($menu)]);
+            } else {
+                return response()->json(['message' => 'Menu not found'], 404);
+            }
+        }catch(\Exception $e){
+            Log::error($e);
+            return response()->json(['message' => 'Error when updating menu'], 500);
+        }
     }
 
     /**
@@ -51,6 +82,28 @@ class MenusController extends Controller
      */
     public function destroy(string $id)
     {
-        //
+        try{
+            $menu = Menu::findOrFail($id);
+            if($menu){
+                $this->deleteChildren($menu);
+                $menu->roles()->detach();
+                $menu->delete();
+                return response()->json(['message' => 'Menu deleted successfully']);
+            } else {
+                return response()->json(['message' => 'Menu not found'], 404);
+            }
+        }catch(\Exception $e){
+            Log::error($e);
+            return response()->json(['message' => 'Error when deleting menu'], 500);
+        }
+    }
+
+    private function deleteChildren($menu)
+    {
+        foreach ($menu->children as $child) {
+            $this->deleteChildren($child); // Recursively delete its children
+            $child->roles()->detach(); // Detach roles before deleting
+            $child->delete();
+        }
     }
 }

app/Http/Controllers/Api/PbgTaskController.php

@@ -21,9 +21,35 @@ class PbgTaskController extends Controller
     public function __construct(GoogleSheetService $googleSheetService){
         $this->googleSheetService = $googleSheetService;
     }
-    public function index()
+    public function index(Request $request)
     {
-        //
+        info($request);
+        
+        $isLastUpdated = filter_var($request->query('isLastUpdated', false), FILTER_VALIDATE_BOOLEAN);
+
+        $query = PbgTask::query();
+
+        if ($isLastUpdated) {
+            $query->orderBy('updated_at', 'desc');
+        } else {
+            $query->where('status', 20);
+        }
+
+        // Ambil maksimal 10 data
+        $pbg_task = $query->limit(10)->get();
+        $totalData = $pbg_task->count();
+
+        // Tambahkan nomor urut
+        $data = $pbg_task->map(function ($item, $index) {
+            return array_merge($item->toArray(), ['no' => $index + 1]);
+        });
+
+        return response()->json([
+            'data' => $data,
+            'meta' => [
+                'total' => $totalData
+            ]
+        ]);
     }
 
     /**

app/Http/Controllers/Api/PbgTaskGoogleSheetsController.php

@@ -0,0 +1,61 @@
+<?php
+
+namespace App\Http\Controllers\Api;
+
+use App\Http\Controllers\Controller;
+use App\Http\Resources\PbgTaskGoogleSheetResource;
+use App\Models\PbgTaskGoogleSheet;
+use Illuminate\Http\Request;
+
+class PbgTaskGoogleSheetsController extends Controller
+{
+    /**
+     * Display a listing of the resource.
+     */
+    public function index(Request $request)
+    {
+        $query = PbgTaskGoogleSheet::query()->orderBy('id', 'desc');
+        if ($request->filled('search')) {
+            $query->where('no_registrasi', 'like', "%{$request->get('search')}%");
+        }
+        return PbgTaskGoogleSheetResource::collection($query->paginate(config('app.paginate_per_page', 50)));
+    }
+
+    /**
+     * Store a newly created resource in storage.
+     */
+    public function store(Request $request)
+    {
+        //
+    }
+
+    /**
+     * Display the specified resource.
+     */
+    public function show(string $id)
+    {
+        //
+    }
+
+    /**
+     * Update the specified resource in storage.
+     */
+    public function update(Request $request, string $id)
+    {
+        //
+    }
+
+    /**
+     * Remove the specified resource from storage.
+     */
+    public function destroy(string $id)
+    {
+        try{
+            $data = PbgTaskGoogleSheet::find($id);
+            $data->delete();
+            return response()->json(['message' => 'Data deleted successfully'], 200);
+        }catch(\Exception $e){
+            return response()->json(['message' => 'Failed to delete data'], 500);
+        }
+    }
+}

app/Http/Controllers/Api/RequestAssignmentController.php

@@ -5,7 +5,11 @@ namespace App\Http\Controllers\Api;
 use App\Http\Controllers\Controller;
 use App\Http\Resources\RequestAssignmentResouce;
 use App\Models\PbgTask;
+use App\Models\PbgTaskGoogleSheet;
+use DB;
+use Exception;
 use Illuminate\Http\Request;
+use Log;
 
 class RequestAssignmentController extends Controller
 {
@@ -17,11 +21,57 @@ class RequestAssignmentController extends Controller
         $query = PbgTask::query()->orderBy('id', 'desc');
         if($request->has('search') && !empty($request->get("search"))){
             $query->where('name', 'LIKE', '%'.$request->get('search').'%')
-            ->orWhere('registration_number', 'LIKE', '%'.$request->get('search').'%');
+            ->orWhere('registration_number', 'LIKE', '%'.$request->get('search').'%')
+            ->orWhere('document_number', 'LIKE', '%'.$request->get('search').'%');
         }
         return RequestAssignmentResouce::collection($query->paginate());
     }
 
+    public function report_payment_recaps(Request $request)
+    {
+        try {
+            // Query dengan group by kecamatan dan sum nilai_retribusi_keseluruhan_simbg
+            $query = PbgTaskGoogleSheet::select(
+                    'kecamatan',
+                    DB::raw('SUM(nilai_retribusi_keseluruhan_simbg) as total')
+                )
+                ->groupBy('kecamatan')
+                ->paginate(10);
+
+            // Return hasil dalam JSON format
+            return response()->json([
+                'success' => true,
+                'data' => $query
+            ]);
+
+        } catch (Exception $e) {
+            Log::error($e->getMessage());
+            return response()->json(['message' => 'Terjadi kesalahan: ' . $e->getMessage()], 500);
+        }
+    }
+    public function report_pbg_ptsp()
+    {
+        try {
+            // Query dengan group by status dan count total per status
+            $query = PbgTask::select(
+                    'status',
+                    'status_name',
+                    DB::raw('COUNT(*) as total')
+                )
+                ->groupBy('status', 'status_name')
+                ->paginate(10);
+
+            // Return hasil dalam JSON format
+            return response()->json([
+                'success' => true,
+                'data' => $query
+            ]);
+
+        } catch (Exception $e) {
+            Log::error($e->getMessage());
+            return response()->json(['message' => 'Terjadi kesalahan: ' . $e->getMessage()], 500);
+        }
+    }
     /**
      * Store a newly created resource in storage.
      */

app/Http/Controllers/Api/RolesController.php

@@ -3,6 +3,7 @@
 namespace App\Http\Controllers\Api;
 
 use App\Http\Controllers\Controller;
+use App\Http\Requests\RoleRequest;
 use App\Models\Role;
 use Illuminate\Http\Request;
 
@@ -13,21 +14,26 @@ class RolesController extends Controller
      */
     public function index(Request $request)
     {
-        $query = Role::query();
+        $query = Role::query()->orderBy('id', 'desc');
 
         if($request->has('search') && !empty($request->get('search'))){
             $query = $query->where('name', 'like', '%'. $request->get('search') . '%');
         }
 
-        return response()->json($query->paginate());
+        return response()->json($query->paginate(config('app.paginate_per_page', 50)));
     }
 
     /**
      * Store a newly created resource in storage.
      */
-    public function store(Request $request)
+    public function store(RoleRequest $request)
     {
-        //
+        try{
+            $role = Role::create($request->validated());
+            return response()->json(['message' => 'Successfully created', 'data' => $role]);
+        }catch(\Exception $e){
+            return response()->json(['message' => 'Error when creating role', 'error' => $e->getMessage()], 500);
+        }
     }
 
     /**
@@ -35,15 +41,34 @@ class RolesController extends Controller
      */
     public function show(string $id)
     {
-        //
+        try{
+            $role = Role::find($id);
+            if($role){
+                return response()->json(['data' => $role]);
+            } else {
+                return response()->json(['message' => 'Role not found'], 404);
+            }
+        }catch(\Exception $e){
+            return response()->json(['message' => 'Error when getting role', 'error' => $e->getMessage()], 500);
+        }
     }
 
     /**
      * Update the specified resource in storage.
      */
-    public function update(Request $request, string $id)
+    public function update(RoleRequest $request, string $id)
     {
-        //
+        try{
+            $role = Role::find($id);
+            if($role){
+                $role->update($request->validated());
+                return response()->json(['message' => 'Successfully updated', 'data' => $role]);
+            } else {
+                return response()->json(['message' => 'Role not found'], 404);
+            }
+        }catch(\Exception $e){
+            return response()->json(['message' => 'Error when updating role', 'error' => $e->getMessage()], 500);
+        }
     }
 
     /**
@@ -51,6 +76,16 @@ class RolesController extends Controller
      */
     public function destroy(string $id)
     {
-        //
+        try{
+            $role = Role::find($id);
+            if($role){
+                $role->delete();
+                return response()->json(['message' => 'Successfully deleted']);
+            } else {
+                return response()->json(['message' => 'Role not found'], 404);
+            }
+        }catch(\Exception $e){
+            return response()->json(['message' => 'Error when deleting role', 'error' => $e->getMessage()], 500);
+        }
     }
 }

app/Http/Controllers/Api/ScrapingController.php

@@ -4,6 +4,7 @@ namespace App\Http\Controllers\Api;
 
 use App\Enums\ImportDatasourceStatus;
 use App\Http\Controllers\Controller;
+use App\Jobs\SyncronizeSIMBG;
 use App\Models\ImportDatasource;
 use App\Traits\GlobalApiResponse;
 use Illuminate\Support\Facades\Artisan;
@@ -23,8 +24,8 @@ class ScrapingController extends Controller
         }
 
         // run service artisan command 
-        Artisan::call("app:execute-scraping");
-        return $this->resSuccess("Success execute scraping service please wait");
+        SyncronizeSIMBG::dispatch();
+        return $this->resSuccess(["message" => "Success execute scraping service on background, check status for more"]);
     }
 
     /**

app/Http/Controllers/Api/SpatialPlanningController.php

@@ -0,0 +1,146 @@
+<?php
+
+namespace App\Http\Controllers\Api;
+
+use App\Models\SpatialPlanning;
+use Illuminate\Http\Request;
+use App\Http\Requests\SpatialPlanningRequest;
+use Illuminate\Http\Response;
+use App\Http\Controllers\Controller;
+use App\Http\Resources\SpatialPlanningResource;
+use Illuminate\Support\Facades\DB;
+use Illuminate\Support\Facades\Validator;
+use Maatwebsite\Excel\Facades\Excel;
+use App\Imports\SpatialPlanningImport;
+use Illuminate\Support\Facades\Storage;
+
+class SpatialPlanningController extends Controller
+{
+    /**
+     * Display a listing of the resource.
+     */
+    public function index(Request $request)
+    {
+        info($request);
+        $perPage = $request->input('per_page', 15);
+        $search = $request->input('search', '');
+
+        $query = SpatialPlanning::query();
+        if ($search) {
+            $query->where(function ($q) use ($search) {
+                $q->where('name', 'like', "%$search%")
+                    ->orWhere('kbli', 'like', "%$search%")
+                    ->orWhere('activities', 'like', "%$search%")
+                    ->orWhere('area', 'like', "%$search%")
+                    ->orWhere('location', 'like', "%$search%")
+                    ->orWhere('number', 'like', "%$search%");
+            });
+        }
+                
+        $spatialPlannings = $query->paginate($perPage);
+
+        // Menambhakan nomor urut (No)
+        $start = ($spatialPlannings->currentPage()-1) * $perPage + 1;
+
+        // Tambahkan nomor urut ke dalam data
+        $data = $spatialPlannings->map(function ($item, $index) use ($start) {
+            return array_merge($item->toArray(), ['no' => $start + $index]);
+        });
+
+        info($data);
+
+        return response()->json([
+            'data' => $data,
+            'meta' => [
+                'total' => $spatialPlannings->total(),
+                'per_page' => $spatialPlannings->perPage(),
+                'current_page' => $spatialPlannings->currentPage(),
+                'last_page'=>$spatialPlannings->lastPage(),
+            ]
+        ]);
+    }
+
+    /**
+     * Store a newly created resource in storage.
+     */
+    public function store(SpatialPlanningRequest $request): SpatialPlanning
+    {
+        $data = $request->validated();
+        return SpatialPlanning::create($data);
+    }
+    
+    /**
+     * import spatial planning from excel
+     */
+    public function importFromFile(Request $request)
+    {
+        info($request);
+        //validasi file
+        $validator = Validator::make($request->all(), [
+            'file' => 'required|mimes:xlsx, xls|max:10240'
+        ]);
+
+        if ($validator->fails()) {
+            return response()->json([
+                'message'=>'File vaildation failed.',
+                "errors"=>$validator->errors()
+            ], 400);
+        }
+
+        try {
+            $file = $request->file('file');
+            Excel::import(new SpatialPlanningImport, $file);
+            return response()->json([
+                'message'=>'File uploaded and imported successfully!'
+            ], 200);
+        } catch (\Exception $e) {
+            return response()->json([
+                'message'=>'Error during file import.',
+                'error'=>$e->getMessage()
+            ], 500);
+        }
+    }
+
+    /**
+     * Display the specified resource.
+     */
+    public function show(SpatialPlanning $spatialPlanning): SpatialPlanning
+    {
+        return $spatialPlanning;
+    }
+
+    /**
+     * Update the specified resource in storage.
+     */
+    public function update(SpatialPlanningRequest $request, SpatialPlanning $spatialPlanning): SpatialPlanning
+    {
+        info($request);
+        $data = $request->validated();
+        info($data);
+
+        $spatialPlanning->update($data);
+
+        return $spatialPlanning;
+    }
+
+    public function destroy(SpatialPlanning $spatialPlanning): Response
+    {
+        $spatialPlanning->delete();
+
+        return response()->noContent();
+    }
+
+    public function downloadExcelSpatialPlanning()
+    {
+        $filePath = public_path('templates/template_spatial_planning.xlsx');
+        info(sprintf("File Path: %s | Exists: %s", $filePath, file_exists($filePath) ? 'Yes' : 'No'));
+
+        // Cek apakah file ada
+        if (!file_exists($filePath)) {
+            return response()-> json(['message' => 'File tidak ditemukan!'], Response::HTTP_NOT_FOUND);
+        }
+
+        // Return file to download
+        return response()->download($filePath);
+    }
+}

app/Http/Controllers/Api/SpatialPlanningsController.php

@@ -1,103 +0,0 @@
-<?php
-
-namespace App\Http\Controllers\Api;
-
-use App\Http\Controllers\Controller;
-use App\Http\Requests\ExcelUploadRequest;
-use App\Http\Requests\SpatialPlanningsRequest;
-use App\Http\Resources\SpatialPlanningsResource;
-use App\Imports\SpatialPlanningImport;
-use App\Models\SpatialPlanning;
-use Illuminate\Http\Request;
-use Maatwebsite\Excel\Facades\Excel;
-
-class SpatialPlanningsController extends Controller
-{
-    /**
-     * Display a listing of the resource.
-     */
-    public function index(Request $request)
-    {
-        $query = SpatialPlanning::query()->orderBy('id', 'desc');
-        if ($request->has("search") &&!empty($request->get("search"))) {
-            $query = $query->where("name", "LIKE", "%{$request->get("search")}%")
-                ->orWhere("nomor", "LIKE", "%{$request->get("search")}%");
-        }
-        return SpatialPlanningsResource::collection($query->paginate());
-    }
-    
-    public function store(SpatialPlanningsRequest $request)
-    {
-        try{
-            $validated = $request->validated();
-            $data = SpatialPlanning::create($validated);
-            return response()->json(['message' => 'Successfully created', new SpatialPlanningsResource($data)]);
-        }catch(\Exception $e){
-            return response()->json([
-                'message' => $e->getMessage()
-            ], 500);
-        }
-    }
-
-    /**
-     * Display the specified resource.
-     */
-    public function show(string $id)
-    {
-        //
-    }
-
-    /**
-     * Update the specified resource in storage.
-     */
-    public function update(SpatialPlanningsRequest $request, string $id)
-    {
-        try{
-            $validated = $request->validated();
-            $data = SpatialPlanning::find($id);
-            $data->update($validated);
-            return response()->json(['message' => 'Successfully updated', new SpatialPlanningsResource($data)]);
-        }catch(\Exception $e){
-            return response()->json([
-               'message' => $e->getMessage()
-            ], 500);
-        }
-    }
-
-    /**
-     * Remove the specified resource from storage.
-     */
-    public function destroy(string $id)
-    {
-        try{
-            SpatialPlanning::destroy($id);
-            return response()->json([
-               'message' => 'Successfully deleted'
-            ], 200);
-        }catch(\Exception $e){
-            return response()->json([
-                'message' => $e->getMessage()
-            ], 500);
-        }
-    }
-
-    public function upload(ExcelUploadRequest $request){
-        try{
-            if(!$request->hasFile('file')){
-                return response()->json([
-                   'error' => 'No file provided'
-                ], 400);
-            }
-
-            $file = $request->file('file');
-            Excel::import(new SpatialPlanningImport, $file); 
-            return response()->json([
-               'message' => 'Successfully imported'
-            ], 200);
-        }catch(\Exception $e){
-            return response()->json([
-                'error' => $e->getMessage()
-            ], 500);
-        }
-    }
-}

app/Http/Controllers/Api/TaskAssignmentsController.php

@@ -0,0 +1,64 @@
+<?php
+
+namespace App\Http\Controllers\Api;
+
+use App\Http\Controllers\Controller;
+use App\Http\Resources\TaskAssignmentsResource;
+use App\Models\TaskAssignment;
+use Illuminate\Http\Request;
+
+class TaskAssignmentsController extends Controller
+{
+    /**
+     * Display a listing of the resource.
+     */
+    public function index(Request $request, $uuid)
+    {
+        try{
+            $query = TaskAssignment::query()
+            ->where('pbg_task_uid', $uuid)
+                ->orderBy('id', 'desc');
+
+            if ($request->filled('search')) {
+                $query->where('name', 'like', "%{$request->get('search')}%")
+                ->orWhere('email', 'like', "%{$request->get('search')}%");
+            }
+
+            return TaskAssignmentsResource::collection($query->paginate(config('app.paginate_per_page', 50)));
+        }catch(\Exception $exception){
+            return response()->json(['message' => $exception->getMessage()], 500);
+        }
+    }
+
+    /**
+     * Store a newly created resource in storage.
+     */
+    public function store(Request $request)
+    {
+        //
+    }
+
+    /**
+     * Display the specified resource.
+     */
+    public function show(string $id)
+    {
+        //
+    }
+
+    /**
+     * Update the specified resource in storage.
+     */
+    public function update(Request $request, string $id)
+    {
+        //
+    }
+
+    /**
+     * Remove the specified resource from storage.
+     */
+    public function destroy(string $id)
+    {
+        //
+    }
+}

app/Http/Controllers/Api/TourismController.php

@@ -25,6 +25,13 @@ class TourismController extends Controller
         $search = $request->input('search', '');
 
         $query = Tourism::query();
+        if ($search) {
+            $query->where(function ($q) use ($search) {
+                $q->where('business_name', 'like', "%$search%")
+                    ->orWhere('project_name', 'like', "%$search%")
+                    ->orWhere('business_address', 'like', "%$search%");
+            });
+        }
         $tourisms = $query->paginate($perPage);
 
         $tourisms->getCollection()->transform(function ($tourisms) {
@@ -32,12 +39,18 @@ class TourismController extends Controller
             $tourisms->village_name = $village ? $village->village_name : null;
 
             $district = DB::table('districts')->where('district_code', $tourisms->district_code)->first();
-            $tourisms->district_name = $village ? $village->village_name : null;
+            $tourisms->district_name = $district ? $district->district_name : null;
             return $tourisms;
         });
 
+        $start = ($tourisms->currentPage()-1) * $perPage + 1;
+
+        $data = $tourisms->map(function ($item, $index) use ($start) {
+            return array_merge($item->toArray(), ['no' => $start + $index]);
+        });
+
         return response()->json([
-            'data' => TourismResource::collection($tourisms),
+            'data' => $data,
             'meta' => [
                 'total' => $tourisms->total(),
                 'per_page' => $tourisms->perPage(),
@@ -93,6 +106,18 @@ class TourismController extends Controller
         }
     }
 
+    public function getAllLocation()
+    {
+        $locations = Tourism::whereNotNull('longitude')
+                    ->whereNotNull('latitude')
+                    ->select('project_name', 'longitude', 'latitude')
+                    ->get();
+
+        return response()->json([
+            'data' => $locations
+        ]);
+    }
+
     /**
      * Display the specified resource.
      */
@@ -127,4 +152,18 @@ class TourismController extends Controller
 
         return response()->noContent();
     }
+
+    public function downloadExcelTourism()
+    {
+        $filePath = public_path('templates/template_pariwisata.xlsx');
+        info(sprintf("File Path: %s | Exists: %s", $filePath, file_exists($filePath) ? 'Yes' : 'No'));
+
+        // Cek apakah file ada
+        if (!file_exists($filePath)) {
+            return response()-> json(['message' => 'File tidak ditemukan!'], Response::HTTP_NOT_FOUND);
+        }
+
+        // Return file to download
+        return response()->download($filePath);
+    }
 }

app/Http/Controllers/Api/UmkmController.php

@@ -27,6 +27,17 @@ class UmkmController extends Controller
 
         $query = Umkm::query();
 
+        if ($search) {
+            $query->where(function ($q) use ($search) {
+                $q->where('business_name', 'like', "%$search%")
+                    ->orWhere('business_address', 'like', "%$search%")
+                    ->orWhere('business_desc', 'like', "%$search%")
+                    ->orWhere('business_id_number', 'like', "%$search%")
+                    ->orWhere('owner_id', 'like', "%$search%")
+                    ->orWhere('owner_name', 'like', "%$search%");
+            });
+        }
+
         $umkm = $query->paginate($perPage);
 
         $umkm->getCollection()->transform(function ($umkm) {
@@ -47,8 +58,14 @@ class UmkmController extends Controller
             return $umkm;
         });
 
+        $start = ($umkm->currentPage()-1) * $perPage + 1;
+
+        $data = $umkm->map(function ($item, $index) use ($start) {
+            return array_merge($item->toArray(), ['no' => $start + $index]);
+        });
+
         return response()->json([
-            'data' => UmkmResource::collection($umkm),
+            'data' => $data,
             'meta' => [
                 'total' => $umkm->total(),
                 'per_page' => $umkm->perPage(),
@@ -176,7 +193,7 @@ class UmkmController extends Controller
 
     public function downloadExcelUmkm()
     {
-        $filePath = storage_path('app/public/templates/template_umkm.xlsx');
+        $filePath = public_path('templates/template_umkm.xlsx');
 
         // Cek apakah file ada
         if (!file_exists($filePath)) {

app/Http/Controllers/Api/UsersController.php

@@ -11,6 +11,7 @@ use App\Traits\GlobalApiResponse;
 use Illuminate\Support\Facades\DB;
 use Illuminate\Support\Facades\Hash;
 use Illuminate\Http\Request;
+use Illuminate\Support\Facades\Log;
 
 class UsersController extends Controller
 {
@@ -29,9 +30,10 @@ class UsersController extends Controller
     public function index(Request $request){
         $query = User::query();
         if($request->has('search') && !empty($request->get("search"))){
-            $query->where('name', 'LIKE', '%'.$request->get('search').'%');
+            $query->where('name', 'LIKE', '%'.$request->get('search').'%')
+            ->orWhere('email', 'LIKE', '%'.$request->get('search').'%');
         }
-        return UserResource::collection($query->paginate());
+        return UserResource::collection($query->paginate(config('app.paginate_per_page', 50)));
     }
     public function logout(Request $request){
         $request->user()->tokens()->delete();

app/Http/Controllers/Approval/ApprovalController.php

@@ -0,0 +1,65 @@
+<?php
+
+namespace App\Http\Controllers\Approval;
+
+use App\Http\Controllers\Controller;
+use Illuminate\Http\Request;
+
+class ApprovalController extends Controller
+{
+    /**
+     * Display a listing of the resource.
+     */
+    public function index()
+    {
+        return view('approval.index');
+    }
+
+    /**
+     * Show the form for creating a new resource.
+     */
+    public function create()
+    {
+        //
+    }
+
+    /**
+     * Store a newly created resource in storage.
+     */
+    public function store(Request $request)
+    {
+        //
+    }
+
+    /**
+     * Display the specified resource.
+     */
+    public function show(string $id)
+    {
+        //
+    }
+
+    /**
+     * Show the form for editing the specified resource.
+     */
+    public function edit(string $id)
+    {
+        //
+    }
+
+    /**
+     * Update the specified resource in storage.
+     */
+    public function update(Request $request, string $id)
+    {
+        //
+    }
+
+    /**
+     * Remove the specified resource from storage.
+     */
+    public function destroy(string $id)
+    {
+        //
+    }
+}

app/Http/Controllers/Auth/AuthenticatedSessionController.php

@@ -38,8 +38,6 @@ class AuthenticatedSessionController extends Controller
 
         // Buat token untuk API
         $token = $user->createToken(env('APP_KEY'))->plainTextToken;
-
-        // Simpan token di session (bisa digunakan di JavaScript)
         session(['api_token' => $token]);
 
         return redirect()->intended(RouteServiceProvider::HOME);

app/Http/Controllers/BigdataResumesController.php

@@ -0,0 +1,48 @@
+<?php
+
+namespace App\Http\Controllers;
+
+use Illuminate\Http\Request;
+
+class BigdataResumesController extends Controller
+{
+    /**
+     * Display a listing of the resource.
+     */
+    public function index()
+    {
+        return view('bigdata-resumes.index');
+    }
+
+    /**
+     * Show the form for creating a new resource.
+     */
+    public function create()
+    {
+        //
+    }
+
+    /**
+     * Display the specified resource.
+     */
+    public function show(string $id)
+    {
+        //
+    }
+
+    /**
+     * Show the form for editing the specified resource.
+     */
+    public function edit(string $id)
+    {
+        //
+    }
+
+    /**
+     * Remove the specified resource from storage.
+     */
+    public function destroy(string $id)
+    {
+        //
+    }
+}

app/Http/Controllers/BusinessOrIndustriesController.php

@@ -4,63 +4,40 @@ namespace App\Http\Controllers;
 
 use App\Models\BusinessOrIndustry;
 use Illuminate\Http\Request;
+use Illuminate\Support\Facades\DB;
+use Illuminate\Support\Facades\Auth;
 
 class BusinessOrIndustriesController extends Controller
 {
     /**
      * Display a listing of the resource.
      */
-    public function index()
+    public function index(Request $request)
     {
-        return view('business-industries.index');
+        $menuId = $request->query('menu_id') ?? $request->input('menu_id');
+        $permissions = $this->permissions[$menuId]?? []; // Avoid undefined index error
+        $creator = $permissions['allow_create'] ?? 0;
+        $updater = $permissions['allow_update'] ?? 0;
+        $destroyer = $permissions['allow_destroy'] ?? 0;
+        return view('business-industries.index', compact('creator', 'updater', 'destroyer','menuId'));
     }
     
     /**
      * Show the form for creating a new resource.
      */
-    public function create()
+    public function create(Request $request)
     {
-        return view("business-industries.create");
-    }
-
-    /**
-     * Store a newly created resource in storage.
-     */
-    public function store(Request $request)
-    {
-        //
-    }
-
-    /**
-     * Display the specified resource.
-     */
-    public function show(string $id)
-    {
-        //
+        $menuId = $request->query('menu_id') ?? $request->input('menu_id');
+        return view("business-industries.create", compact('menuId'));
     }
 
     /**
      * Show the form for editing the specified resource.
      */
-    public function edit(string $id)
+    public function edit(string $id, Request $request)
     {
+        $menuId = $request->query('menu_id') ?? $request->input('menu_id');
         $data = BusinessOrIndustry::findOrFail($id);
-        return view('business-industries.edit', compact('data'));
-    }
-
-    /**
-     * Update the specified resource in storage.
-     */
-    public function update(Request $request, string $id)
-    {
-        //
-    }
-
-    /**
-     * Remove the specified resource from storage.
-     */
-    public function destroy(string $id)
-    {
-        //
+        return view('business-industries.edit', compact('data', 'menuId'));
     }
 }

app/Http/Controllers/Chatbot/ChatbotController.php

@@ -0,0 +1,17 @@
+<?php
+
+namespace App\Http\Controllers\Chatbot;
+
+use App\Http\Controllers\Controller;
+use Illuminate\Http\Request;
+
+class ChatbotController extends Controller
+{
+    /**
+     * Displya a listing of the resource
+     */
+    public function index()
+    {
+        return view('chatbot.index');
+    }
+}

app/Http/Controllers/ChatbotPimpinan/ChatbotPimpinanController.php

@@ -0,0 +1,17 @@
+<?php
+
+namespace App\Http\Controllers\ChatbotPimpinan;
+
+use App\Http\Controllers\Controller;
+use Illuminate\Http\Request;
+
+class ChatbotPimpinanController extends Controller
+{
+    /**
+     * Display a listing of the resource
+     */
+    public function index()
+    {
+        return view('chatbot-pimpinan.index');
+    }
+}

app/Http/Controllers/Controller.php

@@ -2,7 +2,54 @@
 
 namespace App\Http\Controllers;
 
+use Illuminate\Support\Facades\Auth;
+
 abstract class Controller
 {
-    //
+    protected array $permissions = [];
+
+    public function __construct()
+    {
+        if (!Auth::check()) {
+            return;
+        }
+        $this->setUserPermissions();
+    }
+
+    protected function setUserPermissions()
+    {
+        $user = Auth::user();
+
+        if (!$user) {
+            return;
+        }
+
+        $menus = $user->roles()
+            ->with(['menus' => function ($query) {
+                $query->select('menus.id', 'menus.name')
+                      ->withPivot(['allow_show' ,'allow_create', 'allow_update', 'allow_destroy']);
+            }])
+            ->get()
+            ->pluck('menus')
+            ->flatten()
+            ->unique('id');
+
+        // Store permissions in an associative array
+        foreach ($menus as $menu) {
+            $this->permissions[$menu->id] = [
+                'allow_show' => $menu->pivot->allow_show ?? 0,
+                'allow_create' => $menu->pivot->allow_create ?? 0,
+                'allow_update' => $menu->pivot->allow_update ?? 0,
+                'allow_destroy' => $menu->pivot->allow_destroy ?? 0,
+            ];
+        }
+
+        // Share permissions globally in views
+        view()->share('permissions', $this->permissions);
+    }
+
+    public function getPermissions()
+    {
+        return $this->permissions;
+    }
 }

app/Http/Controllers/CustomersController.php

@@ -4,23 +4,34 @@ namespace App\Http\Controllers;
 
 use App\Models\Customer;
 use Illuminate\Http\Request;
+use Illuminate\Support\Facades\Auth;
+use Illuminate\Support\Facades\DB;
 
 class CustomersController extends Controller
 {
-    public function index()
+    public function index(Request $request)
     {
-        return view('customers.index');
+        $menuId = $request->query('menu_id') ?? $request->input('menu_id');
+        $permissions = $this->permissions[$menuId]?? []; // Avoid undefined index error
+        $creator = $permissions['allow_create'] ?? 0;
+        $updater = $permissions['allow_update'] ?? 0;
+        $destroyer = $permissions['allow_destroy'] ?? 0;
+
+        return view('customers.index', compact('creator', 'updater', 'destroyer', 'menuId'));
     }
-    public function create()
+    public function create(Request $request)
     {
-        return view('customers.create');
+        $menuId = $request->query('menu_id');
+        return view('customers.create', compact('menuId'));
     }
-    public function edit(string $id)
+    public function edit(Request $request, string $id)
     {
         $data = Customer::findOrFail($id);
-        return view('customers.edit', compact('data'));
+        $menuId = $request->query('menu_id');
+        return view('customers.edit', compact('data', 'menuId'));
     }
-    public function upload(){
-        return view('customers.upload');
+    public function upload(Request $request){
+        $menuId = $request->query('menu_id');
+        return view('customers.upload', compact('menuId'));
     }
 }

app/Http/Controllers/Dashboards/BigDataController.php

@@ -15,7 +15,8 @@ class BigDataController extends Controller
         return view('dashboards.bigdata', compact('latest_created'));
     }
 
-    public function pbg(){
-        return view('index');
+    public function pbg()
+    {
+        return view('dashboards.pbg');
     }
 }

app/Http/Controllers/Dashboards/PotentialsController.php

@@ -0,0 +1,16 @@
+<?php
+
+namespace App\Http\Controllers\Dashboards;
+
+use App\Http\Controllers\Controller;
+use Illuminate\Http\Request;
+
+class PotentialsController extends Controller
+{
+    public function inside_system(){
+        return view('dashboards.potentials.inside_system');
+    }
+    public function outside_system(){
+        return view('dashboards.potentials.outside_system');
+    }
+}

app/Http/Controllers/Data/AdvertisementController.php

@@ -6,15 +6,23 @@ use App\Http\Controllers\Controller;
 use App\Models\Advertisement;
 use Illuminate\Http\Request;
 use Illuminate\Support\Facades\DB;
+use Illuminate\Support\Facades\Auth;
 
 class AdvertisementController extends Controller
 {
     /**
      * Display a listing of the resource.
      */
-    public function index()
+    public function index(Request $request)
     {
-        return view('data.advertisements.index');
+        $menuId = $request->query('menu_id', 0);
+        $permissions = $this->permissions[$menuId] ?? []; // Avoid undefined index error
+
+        $creator = $permissions['allow_create'] ?? 0;
+        $updater = $permissions['allow_update'] ?? 0;
+        $destroyer = $permissions['allow_destroy'] ?? 0;
+
+        return view('data.advertisements.index', compact('creator', 'updater', 'destroyer','menuId'));
     }
 
     /**
@@ -29,8 +37,9 @@ class AdvertisementController extends Controller
     /**
      * Show the form for creating a new resource.
      */
-    public function create()
+    public function create(Request $request)
     {
+        $menuId = $request->query('menu_id', 0);
         $title = 'Advertisement';
         $subtitle = 'Create Data';
 
@@ -47,14 +56,15 @@ class AdvertisementController extends Controller
         
         // $route = 'advertisements.create';
         // info("AdvertisementController@edit diakses dengan ID: $title");
-        return view('data.advertisements.form', compact('title', 'subtitle', 'fields', 'fieldTypes', 'apiUrl', 'dropdownOptions'));
+        return view('data.advertisements.form', compact('title', 'subtitle', 'fields', 'fieldTypes', 'apiUrl', 'dropdownOptions','menuId'));
     }
 
     /**
      * Show the form for editing the specified resource.
      */
-    public function edit($id)
+    public function edit(Request $request, $id)
     {
+        $menuId = $request->query('menu_id', 0);
         info("AdvertisementController@edit diakses dengan ID: $id");
         $title = 'Advertisement';
         $subtitle = 'Update Data';
@@ -62,7 +72,7 @@ class AdvertisementController extends Controller
         // Pastikan model ditemukan
         if (!$modelInstance) {
             info("AdvertisementController@edit: Model tidak ditemukan.");
-            return redirect()->route('advertisements.index')->with('error', 'Advertisement not found');
+            return redirect()->route('web.advertisements.index')->with('error', 'Advertisement not found');
         }
 
         // Mengambil dan memetakan village_name dan district_name
@@ -86,7 +96,7 @@ class AdvertisementController extends Controller
 
         // $route = 'advertisements.update'; // Menggunakan route update untuk form edit
         // info("AdvertisementController@edit diakses dengan route: $route");
-        return view('data.advertisements.form', compact('title', 'subtitle', 'modelInstance', 'fields', 'fieldTypes', 'apiUrl', 'dropdownOptions'));
+        return view('data.advertisements.form', compact('title', 'subtitle', 'modelInstance', 'fields', 'fieldTypes', 'apiUrl', 'dropdownOptions', 'menuId'));
     }
 
     private function getFields()

app/Http/Controllers/Data/GoogleSheetsController.php

@@ -0,0 +1,36 @@
+<?php
+
+namespace App\Http\Controllers\Data;
+
+use App\Http\Controllers\Controller;
+use App\Models\PbgTaskGoogleSheet;
+use Illuminate\Http\Request;
+
+class GoogleSheetsController extends Controller
+{
+    /**
+     * Display a listing of the resource.
+     */
+    public function index(Request $request)
+    {
+        $menu_id = $request->query('menu_id');
+        $user_menu_permission = $this->permissions[$menu_id];
+        return view('data.google-sheet.index', compact('user_menu_permission'));
+    }
+
+    public function create()
+    {
+        return view('data.google-sheet.create');
+    }
+
+    public function show(string $id)
+    {
+        $data = PbgTaskGoogleSheet::find($id);
+        return view('data.google-sheet.show', compact('data'));
+    }
+
+    public function edit(string $id)
+    {
+        return view('data.google-sheet.edit');
+    }
+}

app/Http/Controllers/Data/SpatialPlanningController.php

@@ -0,0 +1,103 @@
+<?php
+
+namespace App\Http\Controllers\Data;
+
+use App\Http\Controllers\Controller;
+use App\Models\SpatialPlanning;
+use Illuminate\Http\Request;
+
+class SpatialPlanningController extends Controller
+{
+    /**
+     * Display a listing of the resource.
+     */
+    public function index(Request $request)
+    {
+        $menuId = $request->query('menu_id', 0);
+        $permissions = $this->permissions[$menuId] ?? []; // Avoid undefined index error
+
+        $creator = $permissions['allow_create'] ?? 0;
+        $updater = $permissions['allow_update'] ?? 0;
+        $destroyer = $permissions['allow_destroy'] ?? 0;
+
+        return view('data.spatialPlannings.index', compact('creator', 'updater', 'destroyer','menuId'));
+    }
+
+    /**
+     * show the form for creating a new resource.
+     */
+    public function bulkCreate(Request $request)
+    {
+        $menuId = $request->query('menu_id', 0);
+        return view('data.spatialPlannings.form-upload', compact('menuId'));
+    }
+
+    /**
+     * Show the form for creating a new resource.
+     */
+    public function create(Request $request)
+    {
+        $menuId = $request->query('menu_id', 0);
+        $title = 'Rencana Tata Ruang';
+        $subtitle = "Create Data";
+
+        // Mengambil data untuk dropdown
+        $dropdownOptions = [];
+
+        $fields = $this->getFields();
+        $fieldTypes = $this->getFieldTypes();
+
+        $apiUrl = url('/api/spatial-plannings');
+        return view('data.spatialPlannings.form', compact('title', 'subtitle', 'fields', 'fieldTypes', 'apiUrl', 'dropdownOptions','menuId'));
+    }
+
+    /**
+     * Show the form for editing the specified resource.
+     */
+    public function edit(Request $request,string $id)
+    {
+        $menuId = $request->query('menu_id', 0);
+        $title = 'Rencana Tata Ruang';
+        $subtitle = 'Update Data';
+
+        $modelInstance = SpatialPlanning::find($id);
+        // Pastikan model ditemukan
+        if (!$modelInstance) {
+            return redirect()->route('spatialPlanning.index') ->with('error', 'Rencana tata ruang tidak ditemukan');
+        }
+
+        $dropdownOptions = [];
+
+        $fields = $this->getFields();
+        $fieldTypes = $this->getFieldTypes();
+
+        $apiUrl = url('/api/spatial-plannings');
+        return view('data.spatialPlannings.form', compact('title', 'subtitle', 'modelInstance', 'fields', 'fieldTypes', 'apiUrl', 'dropdownOptions','menuId'));
+    }
+
+    private function getFields()
+    {
+        return [
+            "name"=> "Nama",
+            "kbli"=> "KBLI",
+            "activities"=> "Kegiatan",
+            "area"=> "Luas (m2)",
+            "location"=> "Lokasi",
+            "number"=> "Nomor",
+            "date"=> "Tanggal",
+        ];
+    }
+
+    private function getFieldTypes() 
+    {
+        return [
+            "name"=> "text",
+            "kbli"=> "text",
+            "activities"=> "text",
+            "area"=> "text",
+            "location"=> "text",
+            "number"=> "text",
+            "date"=> "date",
+        ];
+    }
+}

app/Http/Controllers/Data/TourismController.php

@@ -6,30 +6,39 @@ use App\Http\Controllers\Controller;
 use App\Models\Tourism;
 use Illuminate\Http\Request;
 use Illuminate\Support\Facades\DB;
+use Illuminate\Support\Facades\Auth;
 
 class TourismController extends Controller
 {
     /**
      * Display a listing of the resource
      */
-    public function index()
+    public function index(Request $request)
     {
-        return view('data.tourisms.index');
+        $menuId = $request->query('menu_id', 0);
+        $permissions = $this->permissions[$menuId] ?? []; // Avoid undefined index error
+
+        $creator = $permissions['allow_create'] ?? 0;
+        $updater = $permissions['allow_update'] ?? 0;
+        $destroyer = $permissions['allow_destroy'] ?? 0;
+        return view('data.tourisms.index', compact('creator', 'updater', 'destroyer', 'menuId'));
     }
 
     /**
      * show the form for creating a new rsource.
      */
-    public function bulkCreate()
+    public function bulkCreate(Request $request)
     {
-        return view('data.tourisms.form-upload');
+        $menuId = $request->query('menu_id', 0);
+        return view('data.tourisms.form-upload', compact('menuId'));
     }
 
     /**
      * Show th form for creating a new resource
      */
-    public function create()
+    public function create(Request $request)
     {
+        $menuId = $request->query('menu_id', 0);
         $title = 'Pariwisata';
         $subtitle = 'Create Data';
 
@@ -44,21 +53,22 @@ class TourismController extends Controller
 
         $apiUrl = url('/api/tourisms');
 
-        return view('data.tourisms.form', compact('title', 'subtitle', 'fields', 'fieldTypes', 'apiUrl', 'dropdownOptions'));
+        return view('data.tourisms.form', compact('title', 'subtitle', 'fields', 'fieldTypes', 'apiUrl', 'dropdownOptions', 'menuId'));
     }
 
     /**
      * show the form for editing the specified resource.
      */
-    public function edit($id)
+    public function edit(Request $request, $id)
     {
+        $menuId = $request->query('menu_id', 0);
         $title = 'Pariwisata';
-        $subtitle = 'Create Data';
+        $subtitle = 'Update Data';
 
         $modelInstance = Tourism::find($id);
         // Pastikan model ditemukan
         if (!$modelInstance) {
-            return redirect()->route('tourisms.index') ->with('error', 'Pariwisata tidak ditemukan');
+            return redirect()->route('web-tourisms.index') ->with('error', 'Pariwisata tidak ditemukan');
         }
 
         // Mengambil dan memetakan village_name dan district_name
@@ -78,7 +88,7 @@ class TourismController extends Controller
 
         $apiUrl = url('/api/tourisms');
 
-        return view('data.tourisms.form', compact('title', 'subtitle', 'modelInstance', 'fields', 'fieldTypes', 'apiUrl', 'dropdownOptions'));
+        return view('data.tourisms.form', compact('title', 'subtitle', 'modelInstance', 'fields', 'fieldTypes', 'apiUrl', 'dropdownOptions', 'menuId'));
     }
 
     private function getFields()

app/Http/Controllers/Data/UmkmController.php

@@ -6,30 +6,39 @@ use App\Http\Controllers\Controller;
 use App\Models\Umkm;
 use Illuminate\Http\Request;
 use Illuminate\Support\Facades\DB;
+use Illuminate\Support\Facades\Auth;
 
 class UmkmController extends Controller
 {
     /**
      * Display a listing of the resource.
      */
-    public function index()
+    public function index(Request $request)
     {
-        return view('data.umkm.index');
+        $menuId = $request->query('menu_id', 0);
+        $permissions = $this->permissions[$menuId] ?? []; // Avoid undefined index error
+
+        $creator = $permissions['allow_create'] ?? 0;
+        $updater = $permissions['allow_update'] ?? 0;
+        $destroyer = $permissions['allow_destroy'] ?? 0;
+        return view('data.umkm.index', compact('creator', 'updater', 'destroyer', 'menuId'));
     }
 
     /**
      * Show the form for creating a new resource.
      */
-    public function bulkCreate()
+    public function bulkCreate(Request $request)
     {
-        return view('data.umkm.form-upload');
+        $menuId = $request->query('menu_id', 0);
+        return view('data.umkm.form-upload', compact('menuId'));
     }
 
     /**
      * Show the form for creating a new resource.
      */
-    public function create()
+    public function create(Request $request)
     {
+        $menuId = $request->query('menu_id', 0);
         $title = 'UMKM';
         $subtitle = 'Create Data';
 
@@ -47,20 +56,21 @@ class UmkmController extends Controller
 
         $apiUrl = url('/api/umkm');
 
-        return view('data.umkm.form', compact('title', 'subtitle', 'fields', 'fieldTypes', 'apiUrl', 'dropdownOptions'));
+        return view('data.umkm.form', compact('title', 'subtitle', 'fields', 'fieldTypes', 'apiUrl', 'dropdownOptions','menuId'));
     }
 
     /**
      * Show the form for editing the specified resource.
      */
-    public function edit($id)
+    public function edit(Request $request,$id)
     {
+        $menuId = $request->query('menu_id', 0);
         $title = 'UMKM';
         $subtitle = 'Update Data';
         $modelInstance = Umkm::find($id);
         // Pastikan model ditemukan
         if (!$modelInstance) {
-            return redirect()->route('umkm.index')->with('error', 'Umkm not found');
+            return redirect()->route('web-umkm.index')->with('error', 'Umkm not found');
         }
 
         // Mengambil dan memetakan village_name dan district_name
@@ -96,7 +106,7 @@ class UmkmController extends Controller
         $apiUrl = url('/api/umkm');
 
         // dd($modelInstance->business_form_id, $dropdownOptions['business_form']);
-        return view('data.umkm.form', compact('title', 'subtitle', 'modelInstance', 'fields', 'fieldTypes', 'apiUrl', 'dropdownOptions'));
+        return view('data.umkm.form', compact('title', 'subtitle', 'modelInstance', 'fields', 'fieldTypes', 'apiUrl', 'dropdownOptions','menuId'));
     }
 
     private function getFields()

app/Http/Controllers/DataSettingController.php

@@ -8,23 +8,31 @@ use Exception;
 use Illuminate\Support\Facades\DB;
 use Illuminate\Support\Facades\Log;
 use Illuminate\Support\Facades\Request;
+use Illuminate\Support\Facades\Auth;
+use Illuminate\Http\Request as IndexRequest;
 
 class DataSettingController extends Controller
 {
     /**
      * Display a listing of the resource.
      */
-    public function index()
+    public function index(IndexRequest $request)
     {
-        return view("data-settings.index");
+        $menuId = $request->query('menu_id') ?? $request->input('menu_id');
+        $permissions = $this->permissions[$menuId]?? []; // Avoid undefined index error
+        $creator = $permissions['allow_create'] ?? 0;
+        $updater = $permissions['allow_update'] ?? 0;
+        $destroyer = $permissions['allow_destroy'] ?? 0;
+        return view("data-settings.index", compact('creator', 'updater', 'destroyer','menuId'));
     }
 
     /**
      * Show the form for creating a new resource.
      */
-    public function create()
+    public function create(IndexRequest $request)
     {
-        return view("data-settings.create");
+        $menuId = $request->query('menu_id') ?? $request->input('menu_id');
+        return view("data-settings.create", compact('menuId'));
     }
 
     /**
@@ -57,14 +65,15 @@ class DataSettingController extends Controller
     /**
      * Show the form for editing the specified resource.
      */
-    public function edit(string $id)
+    public function edit(IndexRequest $request,string $id)
     {
         try{
             $data = DataSetting::findOrFail($id);
+            $menuId = $request->query('menu_id') ?? $request->input('menu_id');
             if(empty($data)){
                 return redirect()->route('data-settings.index')->with('error', 'Invalid id');
             }
-            return view("data-settings.edit", compact("data"));
+            return view("data-settings.edit", compact("data", 'menuId'));
         }catch(Exception $ex){
             return redirect()->route("data-settings.index")->with("error", "Invalid id");
         }

app/Http/Controllers/GoogleApisController.php

@@ -0,0 +1,12 @@
+<?php
+
+namespace App\Http\Controllers;
+
+use Illuminate\Http\Request;
+
+class GoogleApisController extends Controller
+{
+    public function index(){
+        return view('maps.google-api');
+    }
+}

app/Http/Controllers/InvitationsController.php

@@ -0,0 +1,12 @@
+<?php
+
+namespace App\Http\Controllers;
+
+use Illuminate\Http\Request;
+
+class InvitationsController extends Controller
+{
+    public function index(Request $request){
+        return view('invitations.index');
+    }
+}

app/Http/Controllers/Master/UsersController.php

@@ -13,6 +13,7 @@ use Illuminate\Support\Facades\Hash;
 use App\Models\User;
 use App\Traits\GlobalApiResponse;
 use Illuminate\Auth\Events\Registered;
+use Illuminate\Support\Facades\Auth;
 
 class UsersController extends Controller
 {
@@ -21,13 +22,20 @@ class UsersController extends Controller
         $users = User::all();
         return $this->resSuccess($users);
     }
-    public function index(){
+    public function index(Request $request){
+        $menuId = $request->query('menu_id') ?? $request->input('menu_id');
+        $permissions = $this->permissions[$menuId]?? []; // Avoid undefined index error
+        $creator = $permissions['allow_create'] ?? 0;
+        $updater = $permissions['allow_update'] ?? 0;
+        $destroyer = $permissions['allow_destroy'] ?? 0;
+
         $users = User::paginate();
-        return view('master.users.index', compact('users'));
+        return view('master.users.index', compact('users', 'creator', 'updater', 'destroyer','menuId'));
     }
-    public function create(){
+    public function create(Request $request){
+        $menuId = $request->query('menu_id') ?? $request->input('menu_id');
         $roles = Role::all();
-        return view('master.users.create', compact('roles'));
+        return view('master.users.create', compact('roles', 'menuId'));
     }
     public function store(UsersRequest $request){
         $request->validate([
@@ -65,10 +73,11 @@ class UsersController extends Controller
         $user = User::find($id);
         return view('master.users.show', compact('user'));
     }
-    public function edit($id){
+    public function edit(Request $request, $id){
+        $menuId = $request->query('menu_id') ?? $request->input('menu_id');
         $user = User::find($id);
         $roles = Role::all();
-        return view('master.users.edit', compact('user', 'roles'));
+        return view('master.users.edit', compact('user', 'roles', 'menuId'));
     }
     public function update(Request $request, $id){
         $user = User::find($id);

app/Http/Controllers/MenusController.php

@@ -12,18 +12,35 @@ class MenusController extends Controller
     /**
      * Display a listing of the resource.
      */
-    public function index()
+    public function index(Request $request)
     {
-        return view('menus.index');
+        $menuId = (int) $request->query('menu_id', 0);
+        $permissions = $this->permissions[$menuId] ?? []; // Avoid undefined index error
+
+        $creator = $permissions['allow_create'] ?? 0;
+        $updater = $permissions['allow_update'] ?? 0;
+        $destroyer = $permissions['allow_destroy'] ?? 0;
+
+        return view('menus.index', compact('creator', 'updater', 'destroyer', 'menuId'));
     }
 
     /**
      * Show the form for creating a new resource.
      */
-    public function create()
+    public function create(Request $request)
     {
-        $parent_menus = Menu::whereNull('parent_id')->get();
-        return view("menus.create", compact('parent_menus'));
+        $menuId = $request->query('menu_id'); // Get menu_id from request
+        $menu = Menu::with('children')->find($menuId); // Find the menu
+
+        // Get IDs of all child menus to exclude
+        $excludedIds = $menu ? $this->getChildMenuIds($menu) : [$menuId];
+
+        // Fetch only menus that have children and are not in the excluded list
+        $parent_menus = Menu::whereHas('children')
+            ->whereNotIn('id', $excludedIds)
+            ->get();
+
+        return view("menus.create", compact('parent_menus', 'menuId'));
     }
 
     /**
@@ -56,11 +73,16 @@ class MenusController extends Controller
     /**
      * Show the form for editing the specified resource.
      */
-    public function edit(string $id)
+    public function edit(string $id, Request $request)
     {
-        $menu = Menu::findOrFail($id);
-        $parent_menus = Menu::whereNull('parent_id')->where('id','!=',$id)->get();
-        return view("menus.edit", compact('menu','parent_menus'));
+        $menuId = $request->query('menu_id');
+        $menu = Menu::with('children')->find($id);
+        $excludedIds = $menu ? $this->getChildMenuIds($menu) : [$id]; 
+
+        $parent_menus = Menu::whereHas('children')
+            ->whereNotIn('id', $excludedIds)
+            ->get();
+        return view("menus.edit", compact('menu','parent_menus', 'menuId'));
     }
 
     /**
@@ -110,4 +132,15 @@ class MenusController extends Controller
             $child->delete();
         }
     }
+
+    private function getChildMenuIds($menu)
+    {
+        $ids = [$menu->id]; // Start with current menu ID
+
+        foreach ($menu->children as $child) {
+            $ids = array_merge($ids, $this->getChildMenuIds($child)); // Recursively fetch children
+        }
+
+        return $ids;
+    }
 }

app/Http/Controllers/PaymentRecapsController.php

@@ -0,0 +1,64 @@
+<?php
+
+namespace App\Http\Controllers;
+
+use Illuminate\Http\Request;
+
+class PaymentRecapsController extends Controller
+{
+    /**
+     * Display a listing of the resource.
+     */
+    public function index()
+    {
+        return view('payment-recaps.index');
+    }
+
+    /**
+     * Show the form for creating a new resource.
+     */
+    public function create()
+    {
+        //
+    }
+
+    /**
+     * Store a newly created resource in storage.
+     */
+    public function store(Request $request)
+    {
+        //
+    }
+
+    /**
+     * Display the specified resource.
+     */
+    public function show(string $id)
+    {
+        //
+    }
+
+    /**
+     * Show the form for editing the specified resource.
+     */
+    public function edit(string $id)
+    {
+        //
+    }
+
+    /**
+     * Update the specified resource in storage.
+     */
+    public function update(Request $request, string $id)
+    {
+        //
+    }
+
+    /**
+     * Remove the specified resource from storage.
+     */
+    public function destroy(string $id)
+    {
+        //
+    }
+}

app/Http/Controllers/ReportPaymentRecapsController.php

@@ -0,0 +1,12 @@
+<?php
+
+namespace App\Http\Controllers;
+
+use Illuminate\Http\Request;
+
+class ReportPaymentRecapsController extends Controller
+{
+    public function index(Request $request){
+        return view('report-payment-recaps.index');
+    }
+}

app/Http/Controllers/ReportPbgPTSPController.php

@@ -0,0 +1,12 @@
+<?php
+
+namespace App\Http\Controllers;
+
+use Illuminate\Http\Request;
+
+class ReportPbgPTSPController extends Controller
+{
+    public function index(Request $request){
+        return view('report-pbg-ptsp.index');
+    }
+}

app/Http/Controllers/RequestAssignment/PbgTaskController.php

@@ -5,15 +5,37 @@ namespace App\Http\Controllers\RequestAssignment;
 use App\Http\Controllers\Controller;
 use App\Models\PbgTask;
 use Illuminate\Http\Request;
+use Illuminate\Support\Facades\DB;
+use Illuminate\Support\Facades\Auth;
 
 class PbgTaskController extends Controller
 {
     /**
      * Display a listing of the resource.
      */
-    public function index()
+    public function index(Request $request)
     {
-        return view('pbg_task.index');
+        $menuId = $request->query('menu_id');
+        $user = Auth::user();
+        $userId = $user->id;
+
+        // Ambil role_id yang dimiliki user
+        $roleIds = DB::table('user_role')
+            ->where('user_id', $userId)
+            ->pluck('role_id');
+
+        // Ambil data akses berdasarkan role_id dan menu_id
+        $roleAccess = DB::table('role_menu')
+            ->whereIn('role_id', $roleIds)
+            ->where('menu_id', $menuId)
+            ->first();
+
+        // Pastikan roleAccess tidak null sebelum mengakses properti
+        $creator = $roleAccess->allow_create ?? 0;
+        $updater = $roleAccess->allow_update ?? 0;
+        $destroyer = $roleAccess->allow_destroy ?? 0;
+
+        return view('pbg_task.index', compact('creator', 'updater', 'destroyer'));
     }
 
     /**

app/Http/Controllers/RolesController.php

@@ -10,23 +10,31 @@ use Illuminate\Http\Request;
 use Illuminate\Support\Facades\DB;
 use Illuminate\Support\Facades\Log;
 use Illuminate\Support\Facades\Schema;
+use Illuminate\Support\Facades\Auth;
 
 class RolesController extends Controller
 {
     /**
      * Display a listing of the resource.
      */
-    public function index()
+    public function index(Request $request)
     {
-        return view("roles.index");
+        $menuId = $request->query('menu_id') ?? $request->input('menu_id');
+        $permissions = $this->permissions[$menuId]?? []; // Avoid undefined index error
+        $creator = $permissions['allow_create'] ?? 0;
+        $updater = $permissions['allow_update'] ?? 0;
+        $destroyer = $permissions['allow_destroy'] ?? 0;
+
+        return view("roles.index", compact('creator', 'updater', 'destroyer', 'menuId'));
     }
 
     /**
      * Show the form for creating a new resource.
      */
-    public function create()
+    public function create(Request $request)
     {
-        return view("roles.create");
+        $menuId = $request->query('menu_id');
+        return view("roles.create", compact('menuId'));
     }
 
     /**
@@ -59,10 +67,11 @@ class RolesController extends Controller
     /**
      * Show the form for editing the specified resource.
      */
-    public function edit(string $id)
+    public function edit(string $id, Request $request)
     {
+        $menuId = $request->query('menu_id');
         $role = Role::findOrFail($id);
-        return view("roles.edit", compact('role'));
+        return view("roles.edit", compact('role', 'menuId'));
     }
 
     /**
@@ -100,12 +109,13 @@ class RolesController extends Controller
         }
     }
 
-    public function menu_permission(string $role_id){
+    public function menu_permission(string $role_id, Request $request){
         try{
+            $menuId = $request->query('menu_id');
             $role = Role::findOrFail($role_id);
             $menus = Menu::all();
             $role_menus = RoleMenu::where('role_id', $role_id)->get() ?? collect();
-            return view('roles.role_menu', compact('role', 'menus', 'role_menus'));
+            return view('roles.role_menu', compact('role', 'menus', 'role_menus', 'menuId'));
         }catch(\Exception $e){
             return redirect()->back()->with("error", $e->getMessage());
         }
@@ -113,8 +123,9 @@ class RolesController extends Controller
 
     public function update_menu_permission(Request $request, string $role_id){
         try{
+            $menuId = $request->query('menu_id');
             $validateData = $request->validate([
-                "permissions" => "array",
+                "permissions" => "nullable|array",
                 "permissions.*.allow_show" => "nullable|boolean",
                 "permissions.*.allow_create" => "nullable|boolean",
                 "permissions.*.allow_update" => "nullable|boolean",
@@ -123,6 +134,13 @@ class RolesController extends Controller
 
             $role = Role::find($role_id);
 
+            // Jika `permissions` tidak ada atau kosong, hapus semua permissions terkait
+            if (!isset($validateData['permissions']) || empty($validateData['permissions'])) {
+                $role->menus()->detach();
+                return redirect()->route("roles.index", ['menu_id' => $menuId])
+                    ->with('success', 'All menu permissions have been removed.');
+            }
+
             $permissionsArray = [];
             foreach ($validateData['permissions'] as $menu_id => $permission) {
                 $permissionsArray[$menu_id] = [
@@ -137,7 +155,7 @@ class RolesController extends Controller
             // Sync will update existing records and insert new ones
             $role->menus()->sync($permissionsArray);
 
-            return redirect()->route("role-menu.permission", $role_id)->with('success','Menu Permission updated successfully');
+            return redirect()->route("roles.index", ['menu_id' => $menuId])->with('success','Menu Permission updated successfully');
         }catch(\Exception $e){
             Log::error("Error updating role_menu:", ["error" => $e->getMessage()]);
             return redirect()->route("role-menu.permission", $role_id)->with("error", $e->getMessage());

app/Http/Controllers/Settings/SyncronizeController.php

@@ -6,6 +6,8 @@ use App\Http\Controllers\Controller;
 use App\Services\ServiceSIMBG;
 use Illuminate\Http\Request;
 use Exception;
+use Illuminate\Support\Facades\Auth;
+use Illuminate\Support\Facades\DB;
 class SyncronizeController extends Controller
 {
     protected $service_simbg;
@@ -13,16 +15,36 @@ class SyncronizeController extends Controller
         $this->service_simbg = $service_simbg;
     }
     public function index(Request $request){
-        return view('settings.syncronize.index');
+        $menuId = $request->query('menu_id');
+        $user = Auth::user();
+        $userId = $user->id;
+
+        // Ambil role_id yang dimiliki user
+        $roleIds = DB::table('user_role')
+            ->where('user_id', $userId)
+            ->pluck('role_id');
+
+        // Ambil data akses berdasarkan role_id dan menu_id
+        $roleAccess = DB::table('role_menu')
+            ->whereIn('role_id', $roleIds)
+            ->where('menu_id', $menuId)
+            ->first();
+
+        // Pastikan roleAccess tidak null sebelum mengakses properti
+        $creator = $roleAccess->allow_create ?? 0;
+        $updater = $roleAccess->allow_update ?? 0;
+        $destroyer = $roleAccess->allow_destroy ?? 0;
+
+        return view('settings.syncronize.index', compact('creator', 'updater', 'destroyer'));
     }
 
     public function syncPbgTask(){
-        $res = $this->service_simbg->syncTaskList();
+        $res = $this->service_simbg->syncTaskPBG();
         return $res;
     }
 
     public function syncronizeTask(Request $request){
-        $res = $this->service_simbg->syncTaskList();
+        $res = $this->service_simbg->syncTaskPBG();
         return redirect()->back()->with('success', 'Processing completed successfully');
     }
 
@@ -33,7 +55,7 @@ class SyncronizeController extends Controller
 
     public function syncIndexIntegration(Request $request, $uuid){
         $token = $request->get('token');
-        $res = $this->service_simbg->syncIndexIntegration($uuid, $token);
+        $res = $this->service_simbg->syncIndexIntegration($uuid);
         return $res;
     }
 
@@ -42,4 +64,9 @@ class SyncronizeController extends Controller
         $res = $this->service_simbg->syncTaskDetailSubmit($uuid, $token);
         return $res;
     }
+
+    public function syncTaskAssignments($uuid){
+        $res = $this->service_simbg->syncTaskAssignments($uuid);
+        return $res;
+    }
 }

app/Http/Controllers/SpatialPlanningsController.php

@@ -1,28 +0,0 @@
-<?php
-
-namespace App\Http\Controllers;
-
-use App\Models\SpatialPlanning;
-use Illuminate\Http\Request;
-
-class SpatialPlanningsController extends Controller
-{
-    public function index()
-    {
-        return view('spatial-plannings.index');
-    }
-    public function create()
-    {
-        return view('spatial-plannings.create');
-    }
-
-    public function edit(string $id)
-    {
-        $data = SpatialPlanning::findOrFail($id);
-        return view('spatial-plannings.update', compact('data'));
-    }
-
-    public function upload (){
-        return view('spatial-plannings.upload');
-    }
-}

app/Http/Controllers/TpatptsController.php

@@ -0,0 +1,28 @@
+<?php
+
+namespace App\Http\Controllers;
+
+use Illuminate\Http\Request;
+
+class TpatptsController extends Controller
+{
+    public function index()
+    {
+        return view('tpa-tpt.index');
+    }
+
+    public function create()
+    {
+        //
+    }
+
+    public function show(string $id)
+    {
+        //
+    }
+
+    public function edit(string $id)
+    {
+        //
+    }
+}

app/Http/Requests/AdvertisementRequest.php

@@ -40,4 +40,40 @@ class AdvertisementRequest extends FormRequest
 			'contact' => 'required|string',
         ];
     }
+
+    /**
+     * pesan error validasi
+     */
+    public function messages(): array
+    {
+        return [
+            'no.required' => 'Nomor harus diisi.',
+            'business_name.required' => 'Nama usaha harus diisi.',
+            'business_name.string' => 'Nama usaha harus berupa teks.',
+            'npwpd.required' => 'NPWPD harus diisi.',
+            'npwpd.string' => 'NPWPD harus berupa teks.',
+            'advertisement_type.required' => 'Jenis reklame harus diisi.',
+            'advertisement_type.string' => 'Jenis reklame harus berupa teks.',
+            'advertisement_content.required' => 'Isi reklame harus diisi.',
+            'advertisement_content.string' => 'Isi reklame harus berupa teks.',
+            'business_address.required' => 'Alamat usaha harus diisi.',
+            'business_address.string' => 'Alamat usaha harus berupa teks.',
+            'advertisement_location.required' => 'Lokasi reklame harus diisi.',
+            'advertisement_location.string' => 'Lokasi reklame harus berupa teks.',
+            'village_name.required' => 'Nama desa harus diisi.',
+            'district_name.required' => 'Nama kecamatan harus diisi.',
+            'length.required' => 'Panjang harus diisi.',
+            'width.required' => 'Lebar harus diisi.',
+            'viewing_angle.required' => 'Sudut pandang harus diisi.',
+            'viewing_angle.string' => 'Sudut pandang harus berupa teks.',
+            'face.required' => 'Jumlah sisi harus diisi.',
+            'face.string' => 'Jumlah sisi harus berupa teks.',
+            'area.required' => 'Luas harus diisi.',
+            'area.string' => 'Luas harus berupa teks.',
+            'angle.required' => 'Sudut harus diisi.',
+            'angle.string' => 'Sudut harus berupa teks.',
+            'contact.required' => 'Kontak harus diisi.',
+            'contact.string' => 'Kontak harus berupa teks.',
+        ];
+    }
 }

app/Http/Requests/DataSettingRequest.php

@@ -21,7 +21,7 @@ class DataSettingRequest extends FormRequest
      */
     public function rules(): array
     {
-        $id = $this->route('data_setting');
+        $id = $this->route('data_setting_id');
         return [
             "key" => "required|unique:data_settings,key," . $id,
             "value" => "required",

app/Http/Requests/MenuRequest.php

@@ -3,6 +3,7 @@
 namespace App\Http\Requests;
 
 use Illuminate\Foundation\Http\FormRequest;
+use Illuminate\Validation\Rule;
 
 class MenuRequest extends FormRequest
 {
@@ -21,12 +22,14 @@ class MenuRequest extends FormRequest
      */
     public function rules(): array
     {
+        $menuId = $this->route('menu_id'); // Get the menu ID if updating
+
         return [
-            'name' => ['required','string','max:255'],
-            'url' => ['nullable','string','max:255'],
-            'icon' => ['nullable','string','max:255'],
-            'parent_id' => ['nullable','exists:menus,id'],
-            'sort_order' => ['required','integer'],
+            'name' => ['required', 'string', 'max:255', Rule::unique('menus', 'name')->ignore($menuId)],
+            'url' => ['nullable', 'string', 'max:255'],
+            'icon' => ['nullable', 'string', 'max:255'],
+            'parent_id' => ['nullable', 'exists:menus,id'],
+            'sort_order' => ['required', 'integer'],
         ];
     }
 }

app/Http/Requests/RoleRequest.php

@@ -21,7 +21,7 @@ class RoleRequest extends FormRequest
      */
     public function rules(): array
     {
-        $roleId = $this->route('role');
+        $roleId = $this->route('role_id');
         return [
             'name' => 'required|string|max:255|unique:roles,name,' . ($roleId ?? 'NULL') . ',id',
             'description' => 'nullable|string',

app/Http/Requests/SpatialPlanningRequest.php

@@ -0,0 +1,52 @@
+<?php
+
+namespace App\Http\Requests;
+
+use Illuminate\Foundation\Http\FormRequest;
+
+class SpatialPlanningRequest extends FormRequest
+{
+    /**
+     * Determine if the user is authorized to make this request.
+     */
+    public function authorize(): bool
+    {
+        return true;
+    }
+
+    /**
+     * Get the validation rules that apply to the request.
+     *
+     * @return array<string, \Illuminate\Contracts\Validation\ValidationRule|array<mixed>|string>
+     */
+    public function rules(): array
+    {
+        return [
+			'name' => 'string',
+			'kbli' => 'string',
+			'activities' => 'string',
+			'area' => 'string',
+			'location' => 'string',
+			'number' => 'string',
+            'date' => 'date_format:Y-m-d',
+        ];
+    }
+
+    /**
+     * Get the validation messages for the defined validation rules.
+     *
+     * @return array<string, string>
+     */
+    public function messages(): array
+    {
+        return [
+            'name.string' => 'Kolom Nama harus berupa teks.',
+            'kbli.string' => 'Kolom KBLI harus berupa teks.',
+            'activities.string' => 'Kolom Kegiatan harus berupa teks.',
+            'area.string' => 'Kolom Area harus berupa teks.',
+            'location.string' => 'Kolom Lokasi harus berupa teks.',
+            'number.string' => 'Kolom Nomor harus berupa teks.',
+            'date.date_format' => 'Format tanggal tidak valid, gunakan format Y-m-d H:i:s.',
+        ];
+    }
+}

app/Http/Requests/TourismRequest.php

@@ -49,4 +49,85 @@ class TourismRequest extends FormRequest
 			'tki' => 'required|string',
         ];
     }
+
+	/**
+	 * Get the validation messages for the defined validation rules.
+	 *
+	 * @return array<string, string>
+	 */
+	public function messages(): array
+	{
+		return [
+			'project_id.required' => 'ID proyek harus diisi.',
+			'project_id.string' => 'ID proyek harus berupa teks.',
+
+			'project_type_id.required' => 'ID tipe proyek harus diisi.',
+			'project_type_id.string' => 'ID tipe proyek harus berupa teks.',
+
+			'nib.required' => 'NIB harus diisi.',
+			'nib.string' => 'NIB harus berupa teks.',
+
+			'business_name.required' => 'Nama usaha harus diisi.',
+			'business_name.string' => 'Nama usaha harus berupa teks.',
+
+			'oss_publication_date.required' => 'Tanggal publikasi OSS harus diisi.',
+
+			'investment_status_description.required' => 'Deskripsi status investasi harus diisi.',
+			'investment_status_description.string' => 'Deskripsi status investasi harus berupa teks.',
+
+			'business_form.required' => 'Bentuk usaha harus diisi.',
+			'business_form.string' => 'Bentuk usaha harus berupa teks.',
+
+			'project_risk.required' => 'Risiko proyek harus diisi.',
+			'project_risk.string' => 'Risiko proyek harus berupa teks.',
+
+			'project_name.required' => 'Nama proyek harus diisi.',
+			'project_name.string' => 'Nama proyek harus berupa teks.',
+
+			'business_scale.required' => 'Skala usaha harus diisi.',
+			'business_scale.string' => 'Skala usaha harus berupa teks.',
+
+			'business_address.required' => 'Alamat usaha harus diisi.',
+			'business_address.string' => 'Alamat usaha harus berupa teks.',
+
+			'district_name.required' => 'Nama kecamatan harus diisi.',
+
+			'village_name.required' => 'Nama desa harus diisi.',
+
+			'longitude.required' => 'Garis bujur harus diisi.',
+			'longitude.string' => 'Garis bujur harus berupa teks.',
+
+			'latitude.required' => 'Garis lintang harus diisi.',
+			'latitude.string' => 'Garis lintang harus berupa teks.',
+
+			'project_submission_date.required' => 'Tanggal pengajuan proyek harus diisi.',
+
+			'kbli.required' => 'Kode KBLI harus diisi.',
+			'kbli.string' => 'Kode KBLI harus berupa teks.',
+
+			'kbli_title.required' => 'Judul KBLI harus diisi.',
+			'kbli_title.string' => 'Judul KBLI harus berupa teks.',
+
+			'supervisory_sector.required' => 'Sektor pengawasan harus diisi.',
+			'supervisory_sector.string' => 'Sektor pengawasan harus berupa teks.',
+
+			'user_name.required' => 'Nama pengguna harus diisi.',
+			'user_name.string' => 'Nama pengguna harus berupa teks.',
+
+			'email.required' => 'Email harus diisi.',
+			'email.string' => 'Email harus berupa teks.',
+
+			'contact.required' => 'Kontak harus diisi.',
+			'contact.string' => 'Kontak harus berupa teks.',
+
+			'land_area_in_m2.required' => 'Luas lahan dalam m² harus diisi.',
+			'land_area_in_m2.string' => 'Luas lahan dalam m² harus berupa teks.',
+
+			'investment_amount.required' => 'Jumlah investasi harus diisi.',
+			'investment_amount.string' => 'Jumlah investasi harus berupa teks.',
+
+			'tki.required' => 'Jumlah tenaga kerja Indonesia harus diisi.',
+			'tki.string' => 'Jumlah tenaga kerja Indonesia harus berupa teks.',
+		];
+	}
 }

app/Http/Resources/BigdataResumeResource.php

@@ -0,0 +1,51 @@
+<?php
+
+namespace App\Http\Resources;
+
+use Illuminate\Http\Request;
+use Illuminate\Http\Resources\Json\JsonResource;
+
+class BigdataResumeResource extends JsonResource
+{
+    /**
+     * Transform the resource into an array.
+     *
+     * @return array<string, mixed>
+     */
+    public function toArray(Request $request): array
+    {
+        return [
+            'id' => $this->id,
+            'import_datasource_id' => $this->import_datasource_id,
+            'potention_count'      => (int) $this->potention_count,
+            'potention_sum'        => number_format((float) $this->potention_sum, 2, ',', '.'),
+
+            'non_verified_count'   => (int) $this->non_verified_count,
+            'non_verified_sum'     => number_format((float) $this->non_verified_sum, 2, ',', '.'),
+
+            'verified_count'       => (int) $this->verified_count,
+            'verified_sum'         => number_format((float) $this->verified_sum, 2, ',', '.'),
+
+            'business_count'       => (int) $this->business_count,
+            'business_sum'         => number_format((float) $this->business_sum, 2, ',', '.'),
+
+            'non_business_count'   => (int) $this->non_business_count,
+            'non_business_sum'     => number_format((float) $this->non_business_sum, 2, ',', '.'),
+
+            'spatial_count'        => (int) $this->spatial_count,
+            'spatial_sum'          => number_format((float) $this->spatial_sum, 2, ',', '.'),
+            
+            'issuance_realization_pbg_count'        => (int) $this->issuance_realization_pbg_count,
+            'issuance_realization_pbg_sum'          => number_format((float) $this->issuance_realization_pbg_sum, 2, ',', '.'),
+            
+            'waiting_click_dpmptsp_count'        => (int) $this->waiting_click_dpmptsp_count,
+            'waiting_click_dpmptsp_sum'          => number_format((float) $this->waiting_click_dpmptsp_sum, 2, ',', '.'),
+            
+            'process_in_technical_office_count'        => (int) $this->process_in_technical_office_count,
+            'process_in_technical_office_sum'          => number_format((float) $this->process_in_technical_office_sum, 2, ',', '.'),
+            
+            'year'                 => $this->year,
+            'created_at' => $this->created_at->toDateTimeString(),
+        ];
+    }
+}

app/Http/Resources/MenuResource.php

@@ -0,0 +1,19 @@
+<?php
+
+namespace App\Http\Resources;
+
+use Illuminate\Http\Request;
+use Illuminate\Http\Resources\Json\JsonResource;
+
+class MenuResource extends JsonResource
+{
+    /**
+     * Transform the resource into an array.
+     *
+     * @return array<string, mixed>
+     */
+    public function toArray(Request $request): array
+    {
+        return parent::toArray($request);
+    }
+}

app/Http/Resources/PbgTaskGoogleSheetResource.php

@@ -0,0 +1,19 @@
+<?php
+
+namespace App\Http\Resources;
+
+use Illuminate\Http\Request;
+use Illuminate\Http\Resources\Json\JsonResource;
+
+class PbgTaskGoogleSheetResource extends JsonResource
+{
+    /**
+     * Transform the resource into an array.
+     *
+     * @return array<string, mixed>
+     */
+    public function toArray(Request $request): array
+    {
+        return parent::toArray($request);
+    }
+}

app/Http/Resources/SpatialPlanningResource.php

@@ -0,0 +1,19 @@
+<?php
+
+namespace App\Http\Resources;
+
+use Illuminate\Http\Request;
+use Illuminate\Http\Resources\Json\JsonResource;
+
+class SpatialPlanningResource extends JsonResource
+{
+    /**
+     * Transform the resource into an array.
+     *
+     * @return array<string, mixed>
+     */
+    public function toArray(Request $request): array
+    {
+        return parent::toArray($request);
+    }
+}

app/Http/Resources/TaskAssignmentsResource.php

@@ -0,0 +1,19 @@
+<?php
+
+namespace App\Http\Resources;
+
+use Illuminate\Http\Request;
+use Illuminate\Http\Resources\Json\JsonResource;
+
+class TaskAssignmentsResource extends JsonResource
+{
+    /**
+     * Transform the resource into an array.
+     *
+     * @return array<string, mixed>
+     */
+    public function toArray(Request $request): array
+    {
+        return parent::toArray($request);
+    }
+}

app/Imports/BusinessIndustriesImport.php

@@ -3,37 +3,105 @@
 namespace App\Imports;
 
 use App\Models\BusinessOrIndustry;
-use Maatwebsite\Excel\Concerns\ToModel;
-use Maatwebsite\Excel\Concerns\ToCollection;
 use Illuminate\Support\Collection;
+use Maatwebsite\Excel\Concerns\ToCollection;
+use Maatwebsite\Excel\Concerns\WithChunkReading;
+use Maatwebsite\Excel\Concerns\WithHeadingRow;
+use Maatwebsite\Excel\Concerns\WithMultipleSheets;
+use Illuminate\Contracts\Queue\ShouldQueue;
+use Maatwebsite\Excel\Concerns\WithBatchInserts;
+use Illuminate\Support\Facades\Log;
 
-class BusinessIndustriesImport implements ToCollection
+class BusinessIndustriesImport implements ToCollection, WithMultipleSheets, WithChunkReading, WithBatchInserts, ShouldQueue, WithHeadingRow
 {
     /**
-    * @param array $row
-    *
-    * @return \Illuminate\Database\Eloquent\Model|null
+    * @param Collection $collection
     */    
-    public function collection(Collection $rows)
+    public function collection(Collection $collection)
     {
-        foreach ($rows->skip(1) as $row){
-            $clean_nop = preg_replace('/[^A-Za-z0-9]/', '', $row[2]);
-            if (!BusinessOrIndustry::where('nop', $clean_nop)->exists()) {
-                BusinessOrIndustry::create([
-                    'nama_kecamatan'     => $row[0],
-                    'nama_kelurahan'     => $row[1],
-                    'nop'                => $clean_nop, // Store cleaned 'nop'
-                    'nama_wajib_pajak'   => $row[3],
-                    'alamat_wajib_pajak' => $row[4], 
-                    'alamat_objek_pajak' => $row[5], 
-                    'luas_bumi'          => $row[6], 
-                    'luas_bangunan'      => $row[7], 
-                    'njop_bumi'          => $row[8], 
-                    'njop_bangunan'      => $row[9], 
-                    'ketetapan'          => $row[10], 
-                    'tahun_pajak'        => $row[11], 
+        try{
+            $batchData = [];
+            $batchSize = 1000;
+    
+            foreach ($collection as $row){
+                if(!isset($row['nop']) || empty($row['nop'])){
+                    continue;
+                }
+    
+    
+                $clean_nop = preg_replace('/[^A-Za-z0-9]/', '', $row['nop']);
+    
+                $batchData[] = [
+                        'nama_kecamatan'     => $row['nama_kecamatan'],
+                        'nama_kelurahan'     => $row['nama_kelurahan'],
+                        'nop'                => $clean_nop,
+                        'nama_wajib_pajak'   => $row['nama_wajib_pajak'],
+                        'alamat_wajib_pajak' => $row['alamat_wajib_pajak'], 
+                        'alamat_objek_pajak' => $row['alamat_objek_pajak'], 
+                        'luas_bumi'          => $row['luas_bumi'], 
+                        'luas_bangunan'      => $row['luas_bangunan'], 
+                        'njop_bumi'          => $row['njop_bumi'], 
+                        'njop_bangunan'      => $row['njop_bangunan'], 
+                        'ketetapan'          => $row['ketetapan'], 
+                        'tahun_pajak'        => $row['tahun_pajak'], 
+                ];
+    
+                if(count($batchData) >= $batchSize){
+                    BusinessOrIndustry::upsert($batchData, ['nop'], [
+                        'nama_kecamatan',
+                        'nama_kelurahan',
+                        'nama_wajib_pajak',
+                        'alamat_wajib_pajak',
+                        'alamat_objek_pajak',
+                        'luas_bumi',
+                        'luas_bangunan',
+                        'njop_bumi',
+                        'njop_bangunan',
+                        'ketetapan',
+                        'tahun_pajak',
+                    ]);
+                    $batchData = [];
+                }
+            }
+            if(!empty($batchData)){
+                BusinessOrIndustry::upsert($batchData, ['nop'], [
+                    'nama_kecamatan',
+                    'nama_kelurahan',
+                    'nama_wajib_pajak',
+                    'alamat_wajib_pajak',
+                    'alamat_objek_pajak',
+                    'luas_bumi',
+                    'luas_bangunan',
+                    'njop_bumi',
+                    'njop_bangunan',
+                    'ketetapan',
+                    'tahun_pajak',
                 ]);
             }
+        }catch(\Exception $exception){
+            Log::error('Error while importing Business Industries data:', ['error' => $exception->getMessage()]);
+            return;
         }
     }
+
+    public function sheets(): array {
+        return [
+            0 => $this
+        ];
+    }
+
+    public function headingRow(): int
+    {
+        return 1;
+    }
+
+    public function chunkSize(): int
+    {
+        return 1000;
+    }
+
+    public function batchSize(): int
+    {
+        return 1000;
+    }
 }

app/Imports/CustomersImport.php

@@ -11,8 +11,9 @@ use Maatwebsite\Excel\Concerns\WithHeadingRow;
 use Maatwebsite\Excel\Concerns\WithMultipleSheets;
 use Illuminate\Contracts\Queue\ShouldQueue;
 use Maatwebsite\Excel\Concerns\WithBatchInserts;
+use Illuminate\Support\Facades\Log;
 
-class CustomersImport implements ToCollection, WithMultipleSheets
+class CustomersImport implements ToCollection, WithMultipleSheets, WithChunkReading, WithBatchInserts, ShouldQueue, WithHeadingRow
 {
     /**
     * @param Collection $collection
@@ -20,23 +21,51 @@ class CustomersImport implements ToCollection, WithMultipleSheets
     public function collection(Collection $collection)
     {
         $batchData = [];
+        $batchSize = 1000;
 
-        foreach ($collection->skip(1) as $row) {
-            if (!isset($row[0]) || empty($row[0])) {
+        foreach ($collection as $row) {
+            if (!isset($row['nomor_pelanggan']) || empty($row['nomor_pelanggan'])) {
                 continue;
             }
 
-            $latitude = filter_var($row[4], FILTER_VALIDATE_FLOAT) ? bcadd($row[4], '0', 18) : null;
-            $longitude = filter_var($row[5], FILTER_VALIDATE_FLOAT) ? bcadd($row[5], '0', 18) : null;
+            $latitude = '0';
+            $longitude = '0';
+
+            if (isset($row['latkor']) && !empty(trim($row['latkor']))) {
+                $latitude = str_replace(',', '.', trim($row['latkor']));
+                if (is_numeric($latitude)) {
+                    $latitude = bcadd($latitude, '0', 18); 
+                } else {
+                    $latitude = '0'; 
+                }
+            } else {
+                $latitude = '0';
+            }
+
+            if (isset($row['lonkor']) && !empty(trim($row['lonkor']))) {
+                $longitude = str_replace(',', '.', trim($row['lonkor'])); 
+                if (is_numeric($longitude)) {
+                    $longitude = bcadd($longitude, '0', 18); 
+                } else {
+                    $longitude = '0';
+                }
+            } else {
+                $longitude = '0';
+            }
 
             $batchData[] = [
-                'nomor_pelanggan' => $row[0],
-                'kota_pelayanan' => $row[1],
-                'nama' => $row[2],
-                'alamat' => $row[3],
+                'nomor_pelanggan' => $row['nomor_pelanggan'] ?? '',
+                'kota_pelayanan' => $row['kota_pelayanan'] ?? '',
+                'nama' => $row['nama'] ?? '',
+                'alamat' => $row['alamat'] ?? '',
                 'latitude' => $latitude,
                 'longitude' => $longitude,
             ];
+
+            if (count($batchData) >= $batchSize) {
+                Customer::upsert($batchData, ['nomor_pelanggan'], ['kota_pelayanan', 'nama', 'alamat', 'latitude', 'longitude']);
+                $batchData = [];
+            }
         }
 
         if (!empty($batchData)) {
@@ -44,9 +73,20 @@ class CustomersImport implements ToCollection, WithMultipleSheets
         }
     }
 
+
     public function sheets(): array {
         return [
             0 => $this
         ];
     }
+
+    public function chunkSize(): int
+    {
+        return 1000;
+    }
+
+    public function batchSize(): int
+    {
+        return 1000;
+    }
 }

app/Imports/SpatialPlanningImport.php

@@ -3,75 +3,61 @@
 namespace App\Imports;
 
 use App\Models\SpatialPlanning;
-use Carbon\Carbon;
-use DateTime;
 use Illuminate\Support\Collection;
-use Illuminate\Support\Facades\Log;
 use Maatwebsite\Excel\Concerns\ToCollection;
-use Maatwebsite\Excel\Concerns\WithMultipleSheets;
+use Illuminate\Support\Facades\DB;
+use DateTime;
+use Carbon\Carbon;
 
-class SpatialPlanningImport implements ToCollection, WithMultipleSheets
+class SpatialPlanningImport implements ToCollection
 {
+    protected static $processed = false;
+
     /**
-    * @param Collection $collection
+     * Process each row in the file
      */
-    public function collection(Collection $collection)
+    public function collection(Collection $rows)
     {
-        $months = [
-            "Januari" => "January", "Februari" => "February", "Maret" => "March",
-            "April" => "April", "Mei" => "May", "Juni" => "June",
-            "Juli" => "July", "Agustus" => "August", "September" => "September",
-            "Oktober" => "October", "November" => "November", "Desember" => "December"
-        ];
+        if (self::$processed) {
+            return;
+        }
+        self::$processed = true;
 
-        $collection->skip(2)->each(function ($row) use ($months) {
-            if (empty(array_filter($row->toArray()))) {
+        if ($rows->isEmpty())
+        {
             return;
         }
 
-            if (!isset($row[6]) || empty($row[6])) {
+        //cari header secara otomatis
+        $header = $rows->first();
+        $headerIndex = collect($header)->search(fn($value) => !empty($value));
+
+        // Pastikan header ditemukan
+        if ($headerIndex === false) {
             return;
         }
 
-            if(!SpatialPlanning::where("nomor", $row[6])->exists()){
-                $clean_nomor = str_replace('\\','',$row[6]);
-                $date_string = isset($row[7]) ? trim($row[7]) : null;
-                $clean_sp_date = null;
-                if ($date_string) {
-                    if(is_numeric($date_string)) {
-                        $clean_sp_date = Carbon::createFromFormat('Y-m-d', '1900-01-01')->addDays($date_string - 2)->format('Y-m-d');
-                    }else{
-                        foreach ($months as $id => $en) {
-                            $date_string = str_replace($id, $en, $date_string);
-                        }
+        foreach ($rows->skip(1) as $row) {
+            $dateValue = trim($row[7]);
+            info($dateValue);
+            $parsedDate = Carbon::createFromFormat('Y-m-d', $dateValue)->format('Y-m-d');
+            info($parsedDate); 
 
-                        $formats = ['j F Y', 'd F Y', 'j-M-Y', 'd-M-Y'];
-
-                        foreach ($formats as $format) {
-                            $date = DateTime::createFromFormat($format, $date_string);
-                            if ($date) {
-                                $clean_sp_date = $date->format('Y-m-d');
-                                break;
-                            }
-                        }
-                    }
-                }
-                SpatialPlanning::create([
-                    'name'          => $row[1],
-                    'kbli'         => $row[2],
-                    'kegiatan'       => $row[3],
-                    'luas'     => $row[4],
-                    'lokasi' => $row[5],
-                    'nomor'        => $clean_nomor,
-                    'sp_date' => $clean_sp_date,
-                ]);
-            }
-        });
-    }
-
-    public function sheets(): array {
-        return [
-            0 => $this
+            $dataToInsert[] = [
+                'name'=>$row[1],
+                'kbli'=>$row[2],
+                'activities'=>$row[3],
+                'area'=>$row[4],
+                'location'=>$row[5],
+                'number'=>$row[6],
+                'date'=>$parsedDate,
             ];
         }
+
+        if(!empty($dataToInsert)) {
+            SpatialPlanning::insert($dataToInsert);
+        } else {
+            return;
+        }
+    }
 }

app/Imports/TourismImport.php

@@ -65,13 +65,6 @@ class TourismImport implements ToCollection
             // ambill village code yang village_name sama dengan $villageName
             $villageCode = $listTrueVillage[$villageName]['village_code'] ?? '000000';
 
-            
-            // if (empty($row[16])) {
-            //     info("Data kosong");
-            // } else {
-            //     info("Baris ke- | Nilai: " . $row[16]);
-            // }
-
             $excelSerialDate = $row[16];
             if (is_numeric($excelSerialDate)) {
                 $projectSubmissionDate = Carbon::createFromFormat('Y-m-d', '1899-12-30')
@@ -82,8 +75,6 @@ class TourismImport implements ToCollection
                     ->format('Y-m-d H:i:s');
             }
 
-            info("Tanggal dikonversi: " . $projectSubmissionDate);
-
             $dataToInsert[] = [
                 'project_id' => $row[1],
                 'project_type_id' => $row[2],

app/Jobs/SyncronizeSIMBG.php

@@ -0,0 +1,35 @@
+<?php
+
+namespace App\Jobs;
+
+use App\Services\GoogleSheetService;
+use App\Services\ServiceSIMBG;
+use Illuminate\Bus\Queueable;
+use Illuminate\Contracts\Queue\ShouldQueue;
+use Illuminate\Foundation\Bus\Dispatchable;
+use Illuminate\Queue\InteractsWithQueue;
+use Illuminate\Queue\SerializesModels;
+
+class SyncronizeSIMBG implements ShouldQueue
+{
+    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
+
+    public $tries = 1;
+
+    public function __construct()
+    {
+    }
+
+    public function handle(): void
+    {
+        try {
+            $serviceSIMBG = app(ServiceSIMBG::class);
+            $serviceSIMBG->syncTaskPBG();
+        } catch (\Exception $e) {
+            \Log::error("SyncronizeSIMBG Job Failed: " . $e->getMessage(), [
+                'exception' => $e,
+            ]);
+            $this->fail($e); // Mark the job as failed
+        }
+    }
+}

app/Models/BigdataResume.php

@@ -21,6 +21,15 @@ class BigdataResume extends Model
         'business_sum',
         'non_business_count',
         'non_business_sum',
+        'spatial_count',
+        'spatial_sum',
+        'year',
+        'waiting_click_dpmptsp_count',
+        'waiting_click_dpmptsp_sum',
+        'issuance_realization_pbg_count',
+        'issuance_realization_pbg_sum',
+        'process_in_technical_office_count',
+        'process_in_technical_office_sum',
     ];
 
     public function importDatasource()
@@ -28,88 +37,84 @@ class BigdataResume extends Model
         return $this->belongsTo(ImportDatasource::class, 'import_datasource_id');
     }
 
-    public static function generateResumeData($import_datasource_id){
-        $query_verified = once( function () {
-            return DB::table('pbg_task AS pt')
-                ->leftJoin('pbg_task_google_sheet AS ptgs', 'pt.registration_number', '=', 'ptgs.no_registrasi')
-                ->leftJoin('pbg_task_retributions AS ptr', 'pt.uuid', '=', 'ptr.pbg_task_uid')
-                ->whereRaw('LOWER(TRIM(ptgs.status_verifikasi)) = ?', [strtolower(trim('Selesai Verifikasi'))])
-                ->selectRaw('COUNT(pt.id) AS total_data, 
-                            SUM(ptr.nilai_retribusi_bangunan) AS total_retribution') 
+    public static function generateResumeData($import_datasource_id, $year, $data_setting){
+        $stats = PbgTask::with(['googleSheet', 'pbg_task_retributions'])
+            ->leftJoin('pbg_task_retributions as ptr', 'pbg_task.uuid', '=', 'ptr.pbg_task_uid')
+            ->leftJoin('pbg_task_google_sheet as ptgs', 'pbg_task.registration_number', '=', 'ptgs.no_registrasi')
+            ->when($year !== 'all', function ($query) use ($year) {
+                $query->whereYear('pbg_task.task_created_at', (int) $year);
+            })
+            ->selectRaw("
+                COUNT(CASE WHEN LOWER(TRIM(ptgs.status_verifikasi)) = 'selesai verifikasi' THEN 1 END) AS verified_count,
+                SUM(CASE WHEN LOWER(TRIM(ptgs.status_verifikasi)) = 'selesai verifikasi' THEN ptr.nilai_retribusi_bangunan ELSE 0 END) AS verified_total,
+                
+                COUNT(CASE WHEN LOWER(TRIM(ptgs.status_verifikasi)) != 'selesai verifikasi' OR ptgs.status_verifikasi IS NULL THEN 1 END) AS non_verified_count,
+                SUM(CASE WHEN LOWER(TRIM(ptgs.status_verifikasi)) != 'selesai verifikasi' OR ptgs.status_verifikasi IS NULL THEN ptr.nilai_retribusi_bangunan ELSE 0 END) AS non_verified_total,
+                
+                COUNT(CASE WHEN (LOWER(TRIM(ptgs.status_verifikasi)) != 'selesai verifikasi' OR ptgs.status_verifikasi IS NULL) 
+                        AND LOWER(TRIM(pbg_task.function_type)) = 'sebagai tempat usaha' THEN 1 END) AS business_count,
+                SUM(CASE WHEN (LOWER(TRIM(ptgs.status_verifikasi)) != 'selesai verifikasi' OR ptgs.status_verifikasi IS NULL) 
+                        AND LOWER(TRIM(pbg_task.function_type)) = 'sebagai tempat usaha' THEN ptr.nilai_retribusi_bangunan ELSE 0 END) AS business_total,
+
+                COUNT(CASE WHEN (LOWER(TRIM(ptgs.status_verifikasi)) != 'selesai verifikasi' OR ptgs.status_verifikasi IS NULL) 
+                        AND (LOWER(TRIM(pbg_task.function_type)) != 'sebagai tempat usaha' OR pbg_task.function_type IS NULL) THEN 1 END) AS non_business_count,
+                SUM(CASE WHEN (LOWER(TRIM(ptgs.status_verifikasi)) != 'selesai verifikasi' OR ptgs.status_verifikasi IS NULL) 
+                        AND (LOWER(TRIM(pbg_task.function_type)) != 'sebagai tempat usaha' OR pbg_task.function_type IS NULL) THEN ptr.nilai_retribusi_bangunan ELSE 0 END) AS non_business_total
+            ")
             ->first();
+
+        // Assign Results
+        $verified_count = $stats->verified_count ?? 0;
+        $verified_total = $stats->verified_total ?? 0;
+        $non_verified_count = $stats->non_verified_count ?? 0;
+        $non_verified_total = $stats->non_verified_total ?? 0;
+        $business_count = $stats->business_count ?? 0;
+        $business_total = $stats->business_total ?? 0;
+        $non_business_count = $stats->non_business_count ?? 0;
+        $non_business_total = $stats->non_business_total ?? 0;
+
+        $query_potention = once(function () use ($year) {
+            $query = PbgTask::leftJoin('pbg_task_retributions as ptr', 'pbg_task.uuid', '=', 'ptr.pbg_task_uid')
+                ->selectRaw('COUNT(DISTINCT pbg_task.id) as task_count, SUM(ptr.nilai_retribusi_bangunan) as total_retribution');
+
+            if ($year !== 'all') {
+                $query->whereYear('pbg_task.task_created_at', (int) $year);
+            }
+
+            return $query->first();
         });
 
-        $verified_count = $query_verified->total_data ?? 0;
-        $verified_total = $query_verified->total_retribution ?? 0;
-
-        $query_business = once(function () {
-            return DB::table('pbg_task AS pt')
-                ->leftJoin('pbg_task_google_sheet AS ptgs', 'pt.registration_number', '=', 'ptgs.no_registrasi')
-                ->leftJoin('pbg_task_retributions AS ptr', 'pt.uuid', '=', 'ptr.pbg_task_uid')
-                ->where(function ($query) {
-                    $query->whereRaw('LOWER(TRIM(ptgs.status_verifikasi)) != ?', [strtolower(trim('Selesai Verifikasi'))])
-                        ->orWhereNull('ptgs.status_verifikasi');
-                })
-                ->where(function ($query) {
-                    $query->whereRaw('LOWER(TRIM(pt.function_type)) = ?', [strtolower(trim('Sebagai Tempat Usaha'))]);
-                })
-                ->selectRaw('COUNT(pt.id) AS total_data, 
-                            SUM(ptr.nilai_retribusi_bangunan) AS total_retribution')
-                ->first();
-        });
-
-        $business_count = $query_business->total_data ?? 0;
-        $business_total = $query_business->total_retribution ?? 0;
-
-        $query_non_business = once( function () {
-            return DB::table('pbg_task AS pt')
-            ->leftJoin('pbg_task_google_sheet AS ptgs', 'pt.registration_number', '=', 'ptgs.no_registrasi')
-            ->leftJoin('pbg_task_retributions AS ptr', 'pt.uuid', '=', 'ptr.pbg_task_uid') // Join ke pbg_task_retributions
-            ->where(function ($query) {
-                $query->whereRaw('LOWER(TRIM(ptgs.status_verifikasi)) != ?', [strtolower(trim('Selesai Verifikasi'))])
-                    ->orWhereNull('ptgs.status_verifikasi'); // Include NULL values
-            })
-            ->where(function ($query) {
-                $query->whereRaw('LOWER(TRIM(pt.function_type)) != ?', [strtolower(trim('Sebagai Tempat Usaha'))])
-                    ->orWhereNull('pt.function_type'); // Include NULL values
-            })
-            ->selectRaw('COUNT(pt.id) AS total_data, 
-                        SUM(ptr.nilai_retribusi_bangunan) AS total_retribution') // Menambahkan SUM dari pbg_task_retributions
-            ->first();
-        });
-        $non_business_count = $query_non_business->total_data ?? 0;
-        $non_business_total = $query_non_business->total_retribution ?? 0;
-
-        $query_non_verified = once(function () {
-            return DB::table('pbg_task AS pt')
-            ->leftJoin('pbg_task_google_sheet AS ptgs', 'pt.registration_number', '=', 'ptgs.no_registrasi')
-            ->leftJoin('pbg_task_retributions AS ptr', 'pt.uuid', '=', 'ptr.pbg_task_uid') // Join tabel pbg_task_retributions
-            ->where(function ($query) {
-                $query->whereRaw('LOWER(TRIM(ptgs.status_verifikasi)) != ?', [strtolower(trim('Selesai Verifikasi'))])
-                    ->orWhereNull('ptgs.status_verifikasi'); // Include NULL values
-            })
-            ->selectRaw('COUNT(pt.id) AS total_data, 
-                        SUM(ptr.nilai_retribusi_bangunan) AS total_retribution') // Menambahkan SUM dari pbg_task_retributions
-            ->first();
-        });
-
-        $non_verified_count = $query_non_verified->total_data ?? 0;
-        $non_verified_total = $query_non_verified->total_retribution ?? 0;
-
-        $query_potention = once( function () {
-            return DB::table('pbg_task as pt')
-            ->leftJoin('pbg_task_retributions as ptr', 'pt.uuid', '=', 'ptr.pbg_task_uid')
-            ->select(
-                DB::raw('COUNT(DISTINCT pt.id) as task_count'),
-                DB::raw('SUM(ptr.nilai_retribusi_bangunan) as total_retribution')
-            )
-            ->first();
-        });
         $potention_count = $query_potention->task_count ?? 0;
         $potention_total = $query_potention->total_retribution ?? 0;
 
+        $query_spatial_plannings = once(function () use ($year) {
+            $query = PbgTask::leftJoin('spatial_plannings as sp', 'pbg_task.document_number', '=', 'sp.number')
+                ->leftJoin('pbg_task_retributions as ptr', 'ptr.pbg_task_uid', '=', 'pbg_task.uuid')
+                ->selectRaw('
+                    CASE 
+                        WHEN COUNT(DISTINCT sp.id) > 0 THEN COUNT(DISTINCT sp.id)
+                        ELSE (SELECT COUNT(*) FROM spatial_plannings) 
+                    END as task_count,
+                    SUM(CASE WHEN sp.id IS NOT NULL AND ptr.id IS NOT NULL THEN ptr.nilai_retribusi_bangunan ELSE 0 END) as total_retribution
+                ');
+
+            if ($year !== 'all') {
+                $query->whereYear('pbg_task.task_created_at', (int) $year);
+            }
+
+            return $query->first();
+        });
+
+        $spatial_planning_count = $query_spatial_plannings->task_count ?? 0;
+        $spatial_planning_total = $query_spatial_plannings->total_retribution;
+
+        $potention_count -= $spatial_planning_count;
+        $potention_total -= $spatial_planning_total;
+
         return self::create([
             'import_datasource_id' => $import_datasource_id,
+            'spatial_count' => $spatial_planning_count,
+            'spatial_sum' => $spatial_planning_total ?? 0.00,
             'potention_count' => $potention_count ?? 0,
             'potention_sum' => $potention_total ?? 0.00,
             'non_verified_count' => $non_verified_count ?? 0,
@@ -120,6 +125,13 @@ class BigdataResume extends Model
             'business_sum' => $business_total ?? 0.00,
             'non_business_count' => $non_business_count ?? 0,
             'non_business_sum' => $non_business_total ?? 0.00,
+            'year' => $year,
+            'waiting_click_dpmptsp_count' => $data_setting['MENUNGGU_KLIK_DPMPTSP_COUNT'] ?? 0,
+            'waiting_click_dpmptsp_sum' => $data_setting['MENUNGGU_KLIK_DPMPTSP_SUM'] ?? 0.00,
+            'issuance_realization_pbg_count' => $data_setting['REALISASI_TERBIT_PBG_COUNT'] ?? 0,
+            'issuance_realization_pbg_sum' => $data_setting['REALISASI_TERBIT_PBG_SUM'] ?? 0.00,
+            'process_in_technical_office_count' => $data_setting['PROSES_DINAS_TEKNIS_COUNT'] ?? 0,
+            'process_in_technical_office_sum' => $data_setting['PROSES_DINAS_TEKNIS_SUM'] ??0.00,
         ]);
     }
 }

app/Models/PbgTask.php

@@ -37,4 +37,13 @@ class PbgTask extends Model
     public function pbg_task_index_integrations(){
         return $this->hasOne(PbgTaskIndexIntegrations::class, 'pbg_task_uid', 'uuid');
     }
+
+    public function googleSheet(){
+        return $this->hasOne(PbgTaskGoogleSheet::class, 'no_registrasi', 'registration_number');
+    }
+
+    public function taskAssignments()
+    {
+        return $this->hasMany(TaskAssignment::class, 'pbg_task_uid', 'uuid');
+    }
 }

app/Models/RoleMenu.php

@@ -18,6 +18,13 @@ class RoleMenu extends Model
         'allow_destroy',
     ];
 
+    protected $casts = [
+        'allow_show' => 'boolean',
+        'allow_create' => 'boolean',
+        'allow_update' => 'boolean',
+        'allow_destroy' => 'boolean',
+    ];
+
     public $timestamps = true;
 
     public function role(){

app/Models/SpatialPlanning.php

@@ -4,16 +4,34 @@ namespace App\Models;
 
 use Illuminate\Database\Eloquent\Model;
 
+/**
+ * Class SpatialPlanning
+ *
+ * @property $id
+ * @property $created_at
+ * @property $updated_at
+ * @property $name
+ * @property $kbli
+ * @property $activities
+ * @property $area
+ * @property $location
+ * @property $number
+ * @property $date
+ *
+ * @package App
+ * @mixin \Illuminate\Database\Eloquent\Builder
+ */
 class SpatialPlanning extends Model
 {
-    protected $table = "spatial_plannings";
-    protected $fillable = [
-        'name',
-        'kbli',
-        'kegiatan',
-        'luas',
-        'lokasi',
-        'nomor',
-        'sp_date'
-    ];
+    
+    protected $perPage = 20;
+
+    /**
+     * The attributes that are mass assignable.
+     *
+     * @var array<int, string>
+     */
+    protected $fillable = ['name', 'kbli', 'activities', 'area', 'location', 'number', 'date'];
+
+
 }

app/Models/TaskAssignment.php

@@ -0,0 +1,28 @@
+<?php
+
+namespace App\Models;
+
+use Illuminate\Database\Eloquent\Model;
+use Illuminate\Database\Eloquent\Factories\HasFactory;
+
+class TaskAssignment extends Model
+{
+    use HasFactory;
+
+    protected $fillable = [
+        'user_id', 'name', 'username', 'email', 'phone_number', 'role',
+        'role_name', 'is_active', 'file', 'expertise', 'experience',
+        'is_verif', 'uid', 'status', 'status_name', 'note', 'pbg_task_uid', 'tas_id'
+    ];
+
+    protected $casts = [
+        'is_active' => 'boolean',
+        'is_verif' => 'boolean',
+        'file' => 'array', // JSON field casting
+    ];
+
+    public function pbgTask()
+    {
+        return $this->belongsTo(PbgTask::class, 'pbg_task_uid', 'uuid');
+    }
+}

app/Models/User.php

@@ -53,4 +53,11 @@ class User extends Authenticatable
     public function roles(){
         return $this->belongsToMany(Role::class, 'user_role')->withTimestamps();
     }
+
+    public function menus(){
+        return Menu::whereHas('roles', function ($query){
+            $query->whereIn('roles.id', $this->roles->pluck('id'))
+            ->where('role_menu.allow_show', true);
+        });
+    }
 }

app/Providers/AppServiceProvider.php

@@ -9,6 +9,8 @@ use Illuminate\Support\Facades\Blade;
 use Illuminate\Support\Facades\View;
 use Illuminate\Support\ServiceProvider;
 use Carbon\Carbon;
+use App\Services\ServiceSIMBG;
+use App\Services\GoogleSheetService;
 
 class AppServiceProvider extends ServiceProvider
 {
@@ -17,7 +19,12 @@ class AppServiceProvider extends ServiceProvider
      */
     public function register(): void
     {
-        //
+        $this->app->singleton(GoogleSheetService::class, function () {
+            return new GoogleSheetService();
+        });
+        $this->app->singleton(ServiceSIMBG::class, function ($app) {
+            return new ServiceSIMBG($app->make(GoogleSheetService::class));
+        });
     }
 
     /**
@@ -27,21 +34,24 @@ class AppServiceProvider extends ServiceProvider
     {
         Blade::component('circle', Circle::class);
 
-        View::composer('layouts.partials.sidebar', function ($view){
+        View::composer('layouts.partials.sidebar', function ($view) {
             $user = Auth::user();
 
-            if($user){
-                $menus = Menu::whereHas('roles', function ($query) use ($user){
-                    $query->where('roles.id', $user->roles->pluck('id'));
+            if ($user) {
+                $menus = Menu::whereHas('roles', function ($query) use ($user) {
+                        $query->whereIn('roles.id', $user->roles->pluck('id'))
+                            ->where('role_menu.allow_show', 1);
                     })
-                ->with(['children' => function ($query) {
-                    $query->whereHas('roles', function ($subQuery) {
-                        $subQuery->where('role_menu.allow_show', 1);
+                    ->with(['children' => function ($query) use ($user) {
+                        $query->whereHas('roles', function ($subQuery) use ($user) {
+                            $subQuery->whereIn('roles.id', $user->roles->pluck('id'))
+                                    ->where('role_menu.allow_show', 1);
                         });
                     }])
+                    ->whereNull('parent_id') // Ambil hanya menu utama
                     ->orderBy('sort_order', 'asc')
                     ->get();
-            }else{
+            } else {
                 $menus = collect();
             }
 

app/Providers/RouteServiceProvider.php

@@ -17,7 +17,7 @@ class RouteServiceProvider extends ServiceProvider
      *
      * @var string
      */
-    public const HOME = '/dashboards/bigdata';
+    public const HOME = '/home';
 
     /**
      * Define your route model bindings, pattern filters, and other route configuration.

app/Services/GoogleSheetService.php

@@ -13,6 +13,7 @@ class GoogleSheetService
     protected $client;
     protected $service;
     protected $spreadsheetID;
+    protected $service_sheets;
     public function __construct()
     {
         $this->client = new Google_Client();

app/Services/OpenAIService.php

@@ -0,0 +1,276 @@
+<?php
+
+namespace App\Services;
+
+use OpenAI;
+use Illuminate\Support\Facades\Storage;
+use Illuminate\Support\Facades\Log;
+
+class OpenAIService
+{
+    protected $client;
+
+    public function __construct()
+    {
+        // $this->client = OpenAI::client(env('OPENAI_API_KEY'));
+        $this->client = OpenAI::client(env('OPENAI_API_KEY'));
+    }
+
+    public function generateQueryBasedMainContent($prompt, $mainContent, $chatHistory)
+    {
+        // Load file JSON
+        $jsonPath = public_path('templates/contentTemplatePrompt.json'); // Sesuaikan path
+        $jsonData = json_decode(file_get_contents($jsonPath), true);
+
+        // Periksa apakah kategori ada dalam JSON
+        if (!isset($jsonData[$mainContent])) {
+            return "Template prompt tidak ditemukan.";
+        }
+
+        // Ambil template berdasarkan kategori
+        $promptTemplate = $jsonData[$mainContent]['prompt'];
+
+        // Menyusun pesan untuk OpenAI
+        $messages = [
+            ['role' => 'system', 'content' => $promptTemplate],
+        ];
+
+        // Menambahkan chat history sebagai konteks
+        foreach ($chatHistory as $chat) {
+            if (isset($chat['user'])) {
+                $messages[] = ['role' => 'user', 'content' => $chat['user']];
+            }
+            if (isset($chat['rawBotResponse'])) {
+                $messages[] = ['role' => 'assistant', 'content' => $chat['rawBotResponse']];
+            }
+        }
+
+        // Tambahkan prompt terbaru user
+        $messages[] = ['role' => 'user', 'content' => $prompt];
+
+        // Kirim request ke OpenAI API
+        $response = $this->client->chat()->create([
+            'model' => 'gpt-4o-mini',
+            'messages' => $messages,
+        ]);
+
+        return trim($response['choices'][0]['message']['content'] ?? 'No response');
+    }
+
+    
+    // public function generateQueryBasedMainContent($prompt, $mainContent, $chatHistory)
+    // {
+    //     // Load file JSON
+    //     $jsonPath = public_path('templates/contentTemplatePrompt.json'); // Sesuaikan path
+    //     $jsonData = json_decode(file_get_contents($jsonPath), true);
+
+    //     // Periksa apakah kategori ada dalam JSON
+    //     if (!isset($jsonData[$mainContent])) {
+    //         return "Template prompt tidak ditemukan.";
+    //     }
+
+    //     // Ambil template berdasarkan kategori
+    //     $promptTemplate = $jsonData[$mainContent]['prompt'];
+
+    //     $response = $this->client->chat()->create([
+    //         'model' => 'gpt-4o-mini',
+    //         'messages' => [
+    //             ['role' => 'system', 'content' => $promptTemplate],
+    //             ['role' => 'user', 'content' => $prompt],
+    //         ],
+    //     ]);
+
+    //     return trim($response['choices'][0]['message']['content'] ?? 'No response');
+    // }
+
+
+    public function validateSyntaxQuery($queryResponse)
+    {
+        $response = $this->client->chat()->create([
+            'model' => 'gpt-4o-mini',
+            'messages' => [
+                [
+                    'role' => 'system', 
+                    'content' => "You are a MariaDB SQL expert. Your task is to validate the syntax of an SQL query to ensure it follows proper MariaDB syntax rules. 
+
+                    Guidelines:
+                    - Check for any syntax errors, missing keywords, or incorrect clause usage.
+                    - Ensure the query is well-structured and adheres to best practices.
+                    - Verify that all SQL keywords are used correctly and in the right order.
+                    - If the query is valid, respond with: \"VALID\".
+                    - If the query has issues, respond with: \"INVALID\".
+                    
+                    Always respond with either \"VALID\" or \"INVALID\"."
+                ],
+                ['role' => 'user', 'content' => $queryResponse],
+            ],
+        ]);
+
+        return trim($response['choices'][0]['message']['content'] ?? 'No response');
+    }
+
+    public function generateNLPFromQuery($inputUser, $resultQuery) {
+        $response = $this->client->chat()->create([
+            'model' => 'gpt-4o-mini',
+            'messages' => [
+                [
+                    'role' => 'system', 
+                    'content' => "You are an expert assistant. Your task is to analyze the database query results and transform them into a human-readable answer based on the user's question.
+    
+                    Guidelines:
+                    - Understand the user's question and extract the key intent.
+                    - Summarize or format the query results to directly answer the user's question.
+                    - Ensure the response is clear, concise, and relevant.
+                    - If the query result is empty or does not match the question, provide a polite response indicating that no data is available.
+    
+                    Always provide a well-structured response that makes sense based on the input question."
+                ],
+                ['role' => 'user', 'content' => "User's question: $inputUser \nDatabase result: $resultQuery"],
+            ],
+        ]);
+    
+        return trim($response['choices'][0]['message']['content'] ?? 'No response');
+    }
+
+    public function generateFinalText($nlpResult) {
+        $response = $this->client->chat()->create([
+            'model' => 'gpt-4o-mini',
+            'messages' => [
+                [
+                    'role' => 'system',
+                    'content' => "You are an expert text formatter. Your task is to take the given NLP result and format it into a structured, human-readable text suitable for rendering inside an HTML <div>.
+    
+                    Guidelines:
+                    - Preserve the meaning and clarity of the content.
+                    - Use proper line breaks for readability.
+                    - If the text contains lists, convert them into bullet points.
+                    - Emphasize important keywords using <strong> tags if necessary.
+                    - Ensure the response remains clean and concise without extra explanations."
+                ],
+                ['role' => 'user', 'content' => "Here is the NLP result that needs formatting:\n\n$nlpResult"],
+            ],
+        ]);
+    
+        return trim($response['choices'][0]['message']['content'] ?? 'No response');
+    }
+
+    public function classifyMainGenerateText($prompt) {
+        $response = $this->client->chat()->create([
+            'model' => 'gpt-4o-mini',
+            'messages' => [
+                [
+                    'role' => 'system',
+                    'content' => "You are an assistant that classifies text into one of the following categories:
+                    - reklame (ads or product/service promotions)
+                    - business_or_industries (business or industries in general)
+                    - customers (customers, consumers, or service users)
+                    - pbg (tasks related to Building Approval)
+                    - retribusi (retributions related to PBG)
+                    - spatial_plannings (spatial planning)
+                    - tourisms (tourism and tourist destinations)
+                    - umkms (Micro, Small, and Medium Enterprises)
+    
+                    Respond with only one of the categories above without any additional explanation."
+                ],
+                [
+                    'role' => 'user',
+                    'content' => "Classify the following text:\n\n" . $prompt
+                ],
+            ],
+        ]);
+    
+        return trim($response['choices'][0]['message']['content'] ?? 'No response');
+    }
+
+    public function createMainQuery($classify, $prompt, $chatHistory)
+    {
+        // Load file JSON
+        $jsonPath = public_path('templates/table_config.json');
+        $jsonConfig = json_decode(file_get_contents($jsonPath), true);
+
+        // Pastikan kategori tersedia dalam konfigurasi
+        if (!isset($jsonConfig[$classify])) {
+            return "Error: Kategori tidak ditemukan dalam konfigurasi.";
+        }
+
+        // Ambil nama tabel dan kolom
+        $tableName = $jsonConfig[$classify]['table_name'];
+        $columns = implode(', ', $jsonConfig[$classify]['list_column']);
+
+        // Konversi chatHistory ke dalam format messages
+        $messages = [
+            [
+                'role' => 'system',
+                'content' => "You are an AI assistant that generates only valid MariaDB queries based on user requests. 
+                Use the following table information to construct the SQL query:
+
+                - Table Name: $tableName
+                - Available Columns: $columns
+
+                Generate only the SQL query without any explanation or additional text.
+                The query should include `LIMIT 10` to restrict the results."
+            ]
+        ];
+
+        // Menambahkan chat history sebagai konteks
+        foreach ($chatHistory as $chat) {
+            if (isset($chat['user'])) {
+                $messages[] = ['role' => 'user', 'content' => $chat['user']];
+            }
+            if (isset($chat['rawBotResponse'])) {
+                $messages[] = ['role' => 'assistant', 'content' => $chat['rawBotResponse']];
+            }
+        }
+
+        // Tambahkan prompt utama pengguna
+        $messages[] = ['role' => 'user', 'content' => $prompt];
+
+        // Kirim permintaan ke model AI
+        $response = $this->client->chat()->create([
+            'model' => 'gpt-4o-mini',
+            'messages' => $messages
+        ]);
+
+        return trim($response['choices'][0]['message']['content'] ?? 'No response');
+    }
+
+    
+    // public function createMainQuery($classify, $prompt)
+    // {
+    //     // Load file JSON
+    //     $jsonPath = public_path('templates/table_config.json');
+    //     $jsonConfig = json_decode(file_get_contents($jsonPath), true);
+
+    //     // Pastikan kategori tersedia dalam konfigurasi
+    //     if (!isset($jsonConfig[$classify])) {
+    //         return "Error: Kategori tidak ditemukan dalam konfigurasi.";
+    //     }
+
+    //     // Ambil nama tabel dan kolom
+    //     $tableName = $jsonConfig[$classify]['table_name'];
+    //     $columns = implode(', ', $jsonConfig[$classify]['list_column']);
+
+    //     $response = $this->client->chat()->create([
+    //         'model' => 'gpt-4o-mini',
+    //         'messages' => [
+    //             [
+    //                 'role' => 'system',
+    //                 'content' => "You are an AI assistant that generates only valid MariaDB queries based on user requests. 
+    //                 Use the following table information to construct the SQL query:
+
+    //                 - Table Name: $tableName
+    //                 - Available Columns: $columns
+
+    //                 Generate only the SQL query without any explanation or additional text
+    //                 The query should include `LIMIT 10` to restrict the results."
+    //             ],
+    //             [
+    //                 'role' => 'user',
+    //                 'content' => $prompt
+    //             ],
+    //         ],
+    //     ]);
+    
+    //     return trim($response['choices'][0]['message']['content'] ?? 'No response');
+    // }
+}

app/Services/ServiceClient.php

@@ -51,9 +51,25 @@ class ServiceClient
 
             $resultResponse = json_decode($responseBody, true, 512, JSON_THROW_ON_ERROR);
             return $this->resSuccess($resultResponse);
+            } catch (\GuzzleHttp\Exception\ClientException $e) {
+                // Handle 4xx errors (e.g., 401 Unauthorized)
+                $responseBody = (string) $e->getResponse()->getBody();
+                $errorResponse = json_decode($responseBody, true);
+
+                if (isset($errorResponse['code']) && $errorResponse['code'] === 'token_not_valid') {
+                    return $this->resError('Invalid token, please refresh your token.', $errorResponse, 401);
+                }
+
+                return $this->resError('Client error from API', $errorResponse, $e->getResponse()->getStatusCode());
+            } catch (\GuzzleHttp\Exception\ServerException $e) {
+                // Handle 5xx errors (e.g., Internal Server Error)
+                return $this->resError('Server error from API', (string) $e->getResponse()->getBody(), 500);
+            } catch (\GuzzleHttp\Exception\RequestException $e) {
+                // Handle network errors (e.g., timeout, connection issues)
+                return $this->resError('Network error: ' . $e->getMessage(), null, 503);
             } catch (Exception $e) {
-            \Log::error('error from client service'. $e->getMessage());
-            return $this->resError($e->getMessage());
+                // Handle unexpected errors
+                return $this->resError('Unexpected error: ' . $e->getMessage(), null, 500);
             }
     }
 

app/Services/ServiceSIMBG.php

@@ -9,12 +9,16 @@ use App\Models\ImportDatasource;
 use App\Models\PbgTaskIndexIntegrations;
 use App\Models\PbgTaskPrasarana;
 use App\Models\PbgTaskRetributions;
+use App\Models\TaskAssignment;
 use Exception;
 use App\Models\PbgTask;
 use App\Traits\GlobalApiResponse;
 use Illuminate\Support\Facades\Log;
 use Carbon\Carbon;
 use App\Services\ServiceClient;
+use App\Services\GoogleSheetService;
+use App\Models\DataSetting;
+use App\Models\PbgTaskGoogleSheet;
 
 class ServiceSIMBG
 {
@@ -24,10 +28,11 @@ class ServiceSIMBG
     private $simbg_host;
     private $fetch_per_page;
     private $service_client;
+    private $googleSheetService;
     /**
      * Create a new class instance.
      */
-    public function __construct()
+    public function __construct(GoogleSheetService $googleSheetService)
     {
         $settings = GlobalSetting::whereIn('key', [
             'SIMBG_EMAIL', 'SIMBG_PASSWORD', 'SIMBG_HOST', 'FETCH_PER_PAGE'
@@ -39,6 +44,7 @@ class ServiceSIMBG
         $this->fetch_per_page = trim((string) ($settings['FETCH_PER_PAGE'] ?? ""));
     
         $this->service_client = new ServiceClient($this->simbg_host);
+        $this->googleSheetService = $googleSheetService;
     }
 
     public function getToken(){
@@ -61,9 +67,22 @@ class ServiceSIMBG
         }
     }
 
-    public function syncIndexIntegration($uuid, $token)
+    public function syncIndexIntegration($uuids)
     {
         try{
+            if(empty($uuids)){
+                return false;
+            }
+
+            $initResToken = $this->getToken();
+            if (empty($initResToken->original['data']['token']['access'])) {
+                Log::error("API response indicates failure", ['token' => 'Failed to retrieve token']);
+                return false;
+            }
+            $token = $initResToken->original['data']['token']['access'];
+            
+            $integrations = [];
+            foreach($uuids as $uuid){
                 $url = "/api/pbg/v1/detail/" . $uuid . "/retribution/indeks-terintegrasi/";
         
                 $headers = [
@@ -75,18 +94,17 @@ class ServiceSIMBG
                 if (empty($res->original['success']) || !$res->original['success']) {
                     // Log error
                     Log::error("API response indicates failure", ['url' => $url, 'uuid' => $uuid]);
-                return false;
+                    continue;
                 }
             
                 $data = $res->original['data']['data'] ?? null;
                 if (!$data) {
                     Log::error("No valid data returned from API", ['url' => $url, 'uuid' => $uuid]);
-                return false;
+                    continue;
                 }
 
-            $resultData = PbgTaskIndexIntegrations::updateOrCreate(
-                ['pbg_task_uid' => $uuid],
-                [
+                $integrations[] = [
+                    'pbg_task_uid' => $uuid,
                     'indeks_fungsi_bangunan' => $data['indeks_fungsi_bangunan'] ?? null,
                     'indeks_parameter_kompleksitas' => $data['indeks_parameter_kompleksitas'] ?? null,
                     'indeks_parameter_permanensi' => $data['indeks_parameter_permanensi'] ?? null,
@@ -94,8 +112,11 @@ class ServiceSIMBG
                     'faktor_kepemilikan' => $data['faktor_kepemilikan'] ?? null,
                     'indeks_terintegrasi' => $data['indeks_terintegrasi'] ?? null,
                     'total' => $data['total'] ?? null,
-                ]
-            );
+                ];
+            }
+
+            PbgTaskIndexIntegrations::upsert($integrations, ['pbg_task_uid'], ['indeks_fungsi_bangunan', 
+            'indeks_parameter_kompleksitas', 'indeks_parameter_permanensi', 'indeks_parameter_ketinggian', 'faktor_kepemilikan', 'indeks_terintegrasi', 'total']);
             
             return true;
         }catch (Exception $e){
@@ -104,22 +125,184 @@ class ServiceSIMBG
         }
     }
 
-    public function syncTaskList()
+    public function syncTaskPBG()
     {
-        $initResToken = $this->getToken();
-
+        try {
+            Log::info("Processing google sheet sync");
             $importDatasource = ImportDatasource::create([
                 'status' => ImportDatasourceStatus::Processing->value,
             ]);
 
+            // sync google sheet first
+            $totalRowCount = $this->googleSheetService->getLastRowByColumn("C");
+            $sheetData = $this->googleSheetService->getSheetDataCollection($totalRowCount);
+            $sheet_big_data = $this->googleSheetService->get_data_by_sheet();
+            $data_setting_result = []; // Initialize result storage
+        
+            $found_section = null; // Track which section is found
+        
+            foreach ($sheet_big_data as $row) {
+                // Check for section headers
+                if (in_array("•PROSES PENERBITAN:", $row)) {
+                    $found_section = "MENUNGGU_KLIK_DPMPTSP";
+                } elseif (in_array("•BERKAS AKTUAL TERVERIFIKASI DINAS TEKNIS 2024:", $row)) {
+                    $found_section = "REALISASI_TERBIT_PBG";
+                } elseif (in_array("•TERPROSES DI DPUTR: belum selesai rekomtek'", $row)) {
+                    $found_section = "PROSES_DINAS_TEKNIS";
+                }
+        
+                // If a section is found and we reach "Grand Total", save the corresponding values
+                if ($found_section && isset($row[0]) && trim($row[0]) === "Grand Total") {
+                    if ($found_section === "MENUNGGU_KLIK_DPMPTSP") {
+                        $data_setting_result["MENUNGGU_KLIK_DPMPTSP_COUNT"] = $this->convertToInteger($row[2]) ?? null;
+                        $data_setting_result["MENUNGGU_KLIK_DPMPTSP_SUM"] = $this->convertToDecimal($row[3])  ?? null;
+                    } elseif ($found_section === "REALISASI_TERBIT_PBG") {
+                        $data_setting_result["REALISASI_TERBIT_PBG_COUNT"] = $this->convertToInteger($row[2]) ?? null;
+                        $data_setting_result["REALISASI_TERBIT_PBG_SUM"] = $this->convertToDecimal($row[4]) ?? null;
+                    } elseif ($found_section === "PROSES_DINAS_TEKNIS") {
+                        $data_setting_result["PROSES_DINAS_TEKNIS_COUNT"] = $this->convertToInteger($row[2]) ?? null;
+                        $data_setting_result["PROSES_DINAS_TEKNIS_SUM"] = $this->convertToDecimal($row[3]) ?? null;
+                    }
+        
+                    // Reset section tracking after capturing "Grand Total"
+                    $found_section = null;
+                }
+            }
+
+            Log::info("data setting result", ['result' => $data_setting_result]);
+
+            foreach ($data_setting_result as $key => $value) {
+                DataSetting::updateOrInsert(
+                    ["key" => $key], // Find by key
+                    ["value" => $value] // Update or insert value
+                );
+            }
+            $mapToUpsert = [];
+
+            foreach($sheetData as $data){
+                $mapToUpsert[] = 
+                    [
+                        'no_registrasi' => $data['no__registrasi'] ?? null,
+                        'jenis_konsultasi' => $data['jenis_konsultasi'] ?? null,
+                        'fungsi_bg' => $data['fungsi_bg'] ?? null,
+                        'tgl_permohonan' => $this->convertToDate($data['tgl_permohonan']),
+                        'status_verifikasi' => $data['status_verifikasi'] ?? null,
+                        'status_permohonan' => $this->convertToDate($data['status_permohonan']),
+                        'alamat_pemilik' => $data['alamat_pemilik'] ?? null,
+                        'no_hp' => $data['no__hp'] ?? null,
+                        'email' => $data['e_mail'] ?? null,
+                        'tanggal_catatan' => $this->convertToDate($data['tanggal_catatan']),
+                        'catatan_kekurangan_dokumen' => $data['catatan_kekurangan_dokumen'] ?? null,
+                        'gambar' => $data['gambar'] ?? null,
+                        'krk_kkpr' => $data['krk_kkpr'] ?? null,
+                        'no_krk' => $data['no__krk'] ?? null,
+                        'lh' => $data['lh'] ?? null,
+                        'ska' => $data['ska'] ?? null,
+                        'keterangan' => $data['keterangan'] ?? null,
+                        'helpdesk' => $data['helpdesk'] ?? null,
+                        'pj' => $data['pj'] ?? null,
+                        'kepemilikan' => $data['kepemilikan'] ?? null,
+                        'potensi_taru' => $data['potensi_taru'] ?? null,
+                        'validasi_dinas' => $data['validasi_dinas'] ?? null,
+                        'kategori_retribusi' => $data['kategori_retribusi'] ?? null,
+                        'no_urut_ba_tpt' => $data['no__urut_ba_tpt__2024_0001_'] ?? null,
+                        'tanggal_ba_tpt' => $this->convertToDate($data['tanggal_ba_tpt']),
+                        'no_urut_ba_tpa' => $data['no__urut_ba_tpa'] ?? null,
+                        'tanggal_ba_tpa' => $this->convertToDate($data['tanggal_ba_tpa']),
+                        'no_urut_skrd' => $data['no__urut_skrd__2024_0001_'] ?? null,
+                        'tanggal_skrd' => $this->convertToDate($data['tanggal_skrd']),
+                        'ptsp' => $data['ptsp'] ?? null,
+                        'selesai_terbit' => $data['selesai_terbit'] ?? null,
+                        'tanggal_pembayaran' => $this->convertToDate($data['tanggal_pembayaran__yyyy_mm_dd_']),
+                        'format_sts' => $data['format_sts'] ?? null,
+                        'tahun_terbit' => (int) $data['tahun_terbit'] ?? null,
+                        'tahun_berjalan' => (int) $data['tahun_berjalan'] ?? null,
+                        'kelurahan' => $data['kelurahan'] ?? null,
+                        'kecamatan' => $data['kecamatan'] ?? null,
+                        'lb' => $this->convertToDecimal($data['lb'])  ?? null,
+                        'tb' => $this->convertToDecimal($data['tb']) ?? null,
+                        'jlb' => (int) $data['jlb'] ?? null,
+                        'unit' => (int) $data['unit'] ?? null,
+                        'usulan_retribusi' => (int) $data['usulan_retribusi'] ?? null,
+                        'nilai_retribusi_keseluruhan_simbg' => $this->convertToDecimal($data['nilai_retribusi_keseluruhan__simbg_']) ?? null,
+                        'nilai_retribusi_keseluruhan_pad' => $this->convertToDecimal($data['nilai_retribusi_keseluruhan__pad_']) ?? null,
+                        'denda' => $this->convertToDecimal($data['denda']) ?? null,
+                        'latitude' => $data['latitude'] ?? null,
+                        'longitude' => $data['longitude'] ?? null,
+                        'nik_nib' => $data['nik_nib'] ?? null,
+                        'dok_tanah' => $data['dok__tanah'] ?? null,
+                        'temuan' => $data['temuan'] ?? null,
+                    ];
+            }
+
+            $batchSize = 1000;
+            $chunks = array_chunk($mapToUpsert, $batchSize);
+
+            foreach($chunks as $chunk){
+                PbgTaskGoogleSheet::upsert($chunk, ["no_registrasi"],[
+                    'jenis_konsultasi',
+                    'nama_pemilik',
+                    'lokasi_bg',
+                    'fungsi_bg',
+                    'nama_bangunan',
+                    'tgl_permohonan',
+                    'status_verifikasi',
+                    'status_permohonan',
+                    'alamat_pemilik',
+                    'no_hp',
+                    'email',
+                    'tanggal_catatan',
+                    'catatan_kekurangan_dokumen',
+                    'gambar',
+                    'krk_kkpr',
+                    'no_krk',
+                    'lh',
+                    'ska',
+                    'keterangan',
+                    'helpdesk',
+                    'pj',
+                    'kepemilikan',
+                    'potensi_taru',
+                    'validasi_dinas',
+                    'kategori_retribusi',
+                    'no_urut_ba_tpt',
+                    'tanggal_ba_tpt',
+                    'no_urut_ba_tpa',
+                    'tanggal_ba_tpa',
+                    'no_urut_skrd',
+                    'tanggal_skrd',
+                    'ptsp',
+                    'selesai_terbit',
+                    'tanggal_pembayaran',
+                    'format_sts',
+                    'tahun_terbit',
+                    'tahun_berjalan',
+                    'kelurahan',
+                    'kecamatan',
+                    'lb',
+                    'tb',
+                    'jlb',
+                    'unit',
+                    'usulan_retribusi',
+                    'nilai_retribusi_keseluruhan_simbg',
+                    'nilai_retribusi_keseluruhan_pad',
+                    'denda',
+                    'latitude',
+                    'longitude',
+                    'nik_nib',
+                    'dok_tanah',
+                    'temuan',
+                ]);
+            }
+            
+            $initResToken = $this->getToken();
             if (empty($initResToken->original['data']['token']['access'])) {
                 $importDatasource->update([
                     'status' => ImportDatasourceStatus::Failed->value,
-                'message' => 'Failed to retrieve token'
+                    'response_body' => 'Failed to retrieve token'
                 ]);
                 return $this->resError("Failed to retrieve token");
             }
-
             $apiToken = $initResToken->original['data']['token']['access'];
             $headers = ['Authorization' => "Bearer " . $apiToken];
 
@@ -130,40 +313,56 @@ class ServiceSIMBG
             if ($totalPage == 0) {
                 $importDatasource->update([
                     'status' => ImportDatasourceStatus::Failed->value,
-                'message' => 'Invalid response: no total_page'
+                    'response_body' => 'Invalid response: no total_page'
                 ]);
                 return $this->resError("Invalid response from API");
             }
 
             $savedCount = $failedCount = 0;
 
+            Log::info("Fetching tasks", ['total page' => $totalPage]);
+
             for ($currentPage = 1; $currentPage <= $totalPage; $currentPage++) {
+                try {
                     $pageUrl = "/api/pbg/v1/list/?page={$currentPage}&size={$this->fetch_per_page}&sort=ASC";
-            $getToken = $this->getToken();
-            Log::info("response index integration", ['currentPage' => $currentPage]);
-            if (empty($getToken->original['data']['token']['access'])) {
-                $importDatasource->update([
-                    'status' => ImportDatasourceStatus::Failed->value,
-                    'message' => 'Failed to retrieve token'
-                ]);
+
+                    Log::info("Fetching tasks", ['currentPage' => $currentPage]);
+                    $headers = [
+                        'Authorization' => "Bearer " . $apiToken, // Update headers
+                    ];
+
+                    for ($attempt = 0; $attempt < 2; $attempt++) { // Try twice (original + retry)
+
+                        $response = $this->service_client->get($pageUrl, $headers);
+
+                        if ($response instanceof \Illuminate\Http\JsonResponse) {
+                            $decodedResponse = json_decode($response->getContent(), true);
+
+                            if (isset($decodedResponse['errors']['code']) && $decodedResponse['errors']['code'] === 'token_not_valid') {
+                                $initResToken = $this->getToken();
+
+                                if (!empty($initResToken->original['data']['token']['access'])) {
+                                    $new_token = $initResToken->original['data']['token']['access'];
+                                    $headers['Authorization'] = "Bearer " . $new_token;
+                                    continue;
+                                } else {
+                                    Log::error("Failed to refresh token");
+                                    return $this->resError("Failed to refresh token");
+                                }
+                            }
+                        }
+
+                        // Success case, break loop
                         break;
                     }
-            $token = $getToken->original['data']['token']['access'];
-            $headers = ['Authorization' => "Bearer " . $token];
-            $response = $this->service_client->get($pageUrl, $headers);
+
                     $tasks = $response->original['data']['data'] ?? [];
 
                     if (empty($tasks)) {
-                $importDatasource->update([
-                    'status' => ImportDatasourceStatus::Failed->value,
-                    'message' => 'No data found on page'
-                ]);
                         Log::warning("No data found on page", ['page' => $currentPage]);
-                break;
+                        continue;
                     }
 
-            Log::info("executed page", ['page' => $currentPage, 'total' => $totalPage]);
-
                     $tasksCollective = [];
                     foreach ($tasks as $item) {
                         try {
@@ -190,65 +389,102 @@ class ServiceSIMBG
                                 'created_at' => now(),
                             ];
 
-                    $this->syncIndexIntegration($item['uid'], $token);
-
-                    $this->syncTaskDetailSubmit($item['uid'], $token);
-                    
+                            $this->syncTaskDetailSubmit($item['uid'], $apiToken);
+                            $this->syncTaskAssignments($item['uid']);
                             $savedCount++;
                         } catch (Exception $e) {
                             $failedCount++;
-                    $importDatasource->update([
-                        'status' => ImportDatasourceStatus::Failed->value,
-                        'message' => "Successfully processed: $savedCount, Failed: $failedCount"
-                    ]);
                             Log::error("Failed to process task", [
                                 'error' => $e->getMessage(),
                                 'task' => $item,
                             ]);
-                    break;
+                            continue; // Skip failed task, continue processing the rest
                         }
                     }
 
+                    if (!empty($tasksCollective)) {
                         PbgTask::upsert($tasksCollective, ['uuid'], [
                             'name', 'owner_name', 'application_type', 'application_type_name', 'condition',
                             'registration_number', 'document_number', 'address', 'status', 'status_name',
                             'slf_status', 'slf_status_name', 'function_type', 'consultation_type', 'due_date',
                             'land_certificate_phase', 'task_created_at', 'updated_at'
                         ]);
+
+                        $uuids = array_column($tasksCollective, 'uuid');
+                        $this->syncIndexIntegration($uuids);
+                    }
+                } catch (Exception $e) {
+                    Log::error("Failed to process page", [
+                        'error' => $e->getMessage(),
+                        'page' => $currentPage,
+                    ]);
+                    continue; // Skip the failed page and move to the next
+                }
             }
 
+            BigdataResume::generateResumeData($importDatasource->id, "all", $data_setting_result);
+            BigdataResume::generateResumeData($importDatasource->id, now()->year, $data_setting_result);
+
+            // Final update after processing all pages
             $importDatasource->update([
                 'status' => ImportDatasourceStatus::Success->value,
                 'message' => "Successfully processed: $savedCount, Failed: $failedCount"
             ]);
             
-        BigdataResume::generateResumeData($importDatasource->id);
-
             Log::info("syncTaskList completed", ['savedCount' => $savedCount, 'failedCount' => $failedCount]);
 
             return $this->resSuccess(['savedCount' => $savedCount, 'failedCount' => $failedCount]);
-    }
 
+        } catch (Exception $e) {
+            Log::error("syncTaskList failed", ['error' => $e->getMessage()]);
+            if (isset($importDatasource)) {
+                $importDatasource->update([
+                    'status' => ImportDatasourceStatus::Failed->value,
+                    'response_body' => 'Critical failure: ' . $e->getMessage()
+                ]);
+            }
+            return $this->resError("Critical failure occurred: " . $e->getMessage());
+        }
+    }
 
     public function syncTaskDetailSubmit($uuid, $token)
     {
         try{
             $url = "/api/pbg/v1/detail/" . $uuid . "/retribution/submit/";
+
             $headers = [
                 'Authorization' => "Bearer " . $token,
             ];
 
+            for ($attempt = 0; $attempt < 2; $attempt++) { 
                 $res = $this->service_client->get($url, $headers);
 
-            if (empty($res->original['success']) || !$res->original['success']) {
-                // Log error
-                Log::error("API response indicates failure", ['url' => $url, 'uuid' => $uuid]);
-                return false;
+                // Check if response is JsonResponse and decode it
+                if ($res instanceof \Illuminate\Http\JsonResponse) {
+                    $decodedResponse = json_decode($res->getContent(), true);
+
+                    if (isset($decodedResponse['errors']['code']) && $decodedResponse['errors']['code'] === 'token_not_valid') {
+                        $initResToken = $this->getToken();
+
+                        if (!empty($initResToken->original['data']['token']['access'])) {
+                            $new_token = $initResToken->original['data']['token']['access'];
+
+                            $headers['Authorization'] = "Bearer " . $new_token;
+                            continue; 
+                        } else {
+                            Log::error("Failed to refresh token");
+                            return $this->resError("Failed to refresh token");
+                        }
+                    }
                 }
 
-            $data = $res->original['data']['data'] ?? [];
+                break;
+            }
+
+            // Ensure response is valid before accessing properties
+            $responseData = $res->original ?? [];
+            $data = $responseData['data']['data'] ?? [];
             if (empty($data)) {
-                Log::error("No data returned from API", ['url' => $url, 'uuid' => $uuid]);
                 return false;
             }
     
@@ -311,5 +547,113 @@ class ServiceSIMBG
         }
     }
 
-}
+    public function syncTaskAssignments($uuid){
+        try{
+            $init_token = $this->getToken();
+            $token = $init_token->original['data']['token']['access'];
+            $url = "/api/pbg/v1/list-tim-penilai/". $uuid . "/?page=1&size=10";
+            $headers = [
+                'Authorization' => "Bearer " . $token,
+            ];
 
+            $response = $this->service_client->get($url, $headers);
+            $datas = $response->original['data']['data'] ?? [];
+            if(empty($datas)){
+                return false;
+            }
+            $task_assignments = [];
+
+            foreach ($datas as $data) {
+                $task_assignments[] = [
+                    'pbg_task_uid' => $uuid,
+                    'user_id' => $data['user_id'],
+                    'name' => $data['name'],
+                    'username' => $data['username'],
+                    'email' => $data['email'],
+                    'phone_number' => $data['phone_number'],
+                    'role' => $data['role'],
+                    'role_name' => $data['role_name'],
+                    'is_active' => $data['is_active'],
+                    'file' => !empty($data['file']) ? json_encode($data['file']) : null, 
+                    'expertise' => !empty($data['expertise']) ? json_encode($data['expertise']) : null,
+                    'experience' => !empty($data['experience']) ? json_encode($data['experience']) : null,
+                    'is_verif' => $data['is_verif'],
+                    'uid' => $data['uid'],
+                    'status' => $data['status'],
+                    'status_name' => $data['status_name'],
+                    'note' => $data['note'],
+                    'ta_id' => $data['id'],
+                    'created_at' => now(),
+                    'updated_at' => now(),
+                ];
+            }
+            TaskAssignment::upsert(
+                $task_assignments,
+                ['uid'], 
+                ['ta_id','name', 'username', 'email', 'phone_number', 'role', 'role_name', 'is_active', 'file', 'expertise', 'experience', 'is_verif', 'status', 'status_name', 'note', 'updated_at']
+            );
+            return true;
+        }catch(Exception $e){
+            Log::error("Failed to sync task assignments", ['error' => $e->getMessage()]);
+            throw $e;
+        }
+    }
+
+    protected function convertToDecimal(?string $value): ?float
+    {
+        if (empty($value)) {
+            return null; // Return null if the input is empty
+        }
+
+        // Remove all non-numeric characters except comma and dot
+        $value = preg_replace('/[^0-9,\.]/', '', $value);
+
+        // If the number contains both dot (.) and comma (,)
+        if (strpos($value, '.') !== false && strpos($value, ',') !== false) {
+            $value = str_replace('.', '', $value); // Remove thousands separator
+            $value = str_replace(',', '.', $value); // Convert decimal separator to dot
+        }
+        // If only a dot is present (assumed as thousands separator)
+        elseif (strpos($value, '.') !== false) {
+            $value = str_replace('.', '', $value); // Remove all dots (treat as thousands separators)
+        }
+        // If only a comma is present (assumed as decimal separator)
+        elseif (strpos($value, ',') !== false) {
+            $value = str_replace(',', '.', $value); // Convert comma to dot (decimal separator)
+        }
+
+        // Ensure the value is numeric before returning
+        return is_numeric($value) ? (float) number_format((float) $value, 2, '.', '') : null;
+    }
+
+    protected function convertToInteger($value) {
+        // Check if the value is an empty string, and return null if true
+        if (trim($value) === "") {
+            return null;
+        }
+
+        $cleaned = str_replace('.','', $value);
+
+        // Otherwise, cast to integer
+        return (int) $cleaned;
+    }
+
+    protected function convertToDate($dateString)
+    {
+        try {
+            // Check if the string is empty
+            if (empty($dateString)) {
+                return null;
+            }
+
+            // Try to parse the date string
+            $date = Carbon::parse($dateString);
+
+            // Return the Carbon instance
+            return $date->format('Y-m-d');
+        } catch (Exception $e) {
+            // Return null if an error occurs during parsing
+            return null;
+        }
+    }
+}

composer.json

@@ -15,11 +15,11 @@
         "laravel/framework": "^11.31",
         "laravel/sanctum": "^4.0",
         "laravel/tinker": "^2.9",
-        "maatwebsite/excel": "^3.1"
+        "maatwebsite/excel": "^3.1",
+        "openai-php/client": "^0.10.3"
     },
     "require-dev": {
         "fakerphp/faker": "^1.23",
-        "ibex/crud-generator": "^2.1",
         "laravel/pail": "^1.1",
         "laravel/pint": "^1.13",
         "laravel/sail": "^1.26",
@@ -57,7 +57,7 @@
         ],
         "dev": [
             "Composer\\Config::disableProcessTimeout",
-            "npx concurrently -c \"#93c5fd,#c4b5fd,#fb7185,#fdba74\" \"php artisan serve\" \"php artisan queue:listen --tries=1\" \"php artisan pail --timeout=0\" \"npm run dev\" --names=server,queue,logs,vite"
+            "npx concurrently -c \"#93c5fd,#c4b5fd,#fb7185,#fdba74\" \"php artisan serve\" \"php artisan pail --timeout=0\" \"npm run dev\" --names=server,queue,logs,vite"
         ]
     },
     "extra": {

composer.lock

@@ -4,7 +4,7 @@
         "Read more about it at https://getcomposer.org/doc/01-basic-usage.md#installing-dependencies",
         "This file is @generated automatically"
     ],
-    "content-hash": "52617d098d62b15c6ce8538cc8aea775",
+    "content-hash": "41bb51871a746904ab745e4095db8b46",
     "packages": [
         {
             "name": "brick/math",
@@ -3296,6 +3296,97 @@
             ],
             "time": "2024-11-21T10:39:51+00:00"
         },
+        {
+            "name": "openai-php/client",
+            "version": "v0.10.3",
+            "source": {
+                "type": "git",
+                "url": "https://github.com/openai-php/client.git",
+                "reference": "4a565d145e0fb3ea1baba8fffe39d86c56b6dc2c"
+            },
+            "dist": {
+                "type": "zip",
+                "url": "https://api.github.com/repos/openai-php/client/zipball/4a565d145e0fb3ea1baba8fffe39d86c56b6dc2c",
+                "reference": "4a565d145e0fb3ea1baba8fffe39d86c56b6dc2c",
+                "shasum": ""
+            },
+            "require": {
+                "php": "^8.1.0",
+                "php-http/discovery": "^1.20.0",
+                "php-http/multipart-stream-builder": "^1.4.2",
+                "psr/http-client": "^1.0.3",
+                "psr/http-client-implementation": "^1.0.1",
+                "psr/http-factory-implementation": "*",
+                "psr/http-message": "^1.1.0|^2.0.0"
+            },
+            "require-dev": {
+                "guzzlehttp/guzzle": "^7.9.2",
+                "guzzlehttp/psr7": "^2.7.0",
+                "laravel/pint": "^1.18.1",
+                "mockery/mockery": "^1.6.12",
+                "nunomaduro/collision": "^7.11.0|^8.5.0",
+                "pestphp/pest": "^2.36.0|^3.5.0",
+                "pestphp/pest-plugin-arch": "^2.7|^3.0",
+                "pestphp/pest-plugin-type-coverage": "^2.8.7|^3.1.0",
+                "phpstan/phpstan": "^1.12.7",
+                "symfony/var-dumper": "^6.4.11|^7.1.5"
+            },
+            "type": "library",
+            "autoload": {
+                "files": [
+                    "src/OpenAI.php"
+                ],
+                "psr-4": {
+                    "OpenAI\\": "src/"
+                }
+            },
+            "notification-url": "https://packagist.org/downloads/",
+            "license": [
+                "MIT"
+            ],
+            "authors": [
+                {
+                    "name": "Nuno Maduro",
+                    "email": "enunomaduro@gmail.com"
+                },
+                {
+                    "name": "Sandro Gehri"
+                }
+            ],
+            "description": "OpenAI PHP is a supercharged PHP API client that allows you to interact with the Open AI API",
+            "keywords": [
+                "GPT-3",
+                "api",
+                "client",
+                "codex",
+                "dall-e",
+                "language",
+                "natural",
+                "openai",
+                "php",
+                "processing",
+                "sdk"
+            ],
+            "support": {
+                "issues": "https://github.com/openai-php/client/issues",
+                "source": "https://github.com/openai-php/client/tree/v0.10.3"
+            },
+            "funding": [
+                {
+                    "url": "https://www.paypal.com/paypalme/enunomaduro",
+                    "type": "custom"
+                },
+                {
+                    "url": "https://github.com/gehrisandro",
+                    "type": "github"
+                },
+                {
+                    "url": "https://github.com/nunomaduro",
+                    "type": "github"
+                }
+            ],
+            "time": "2024-11-12T20:51:16+00:00"
+        },
         {
             "name": "paragonie/constant_time_encoding",
             "version": "v3.0.0",
@@ -3413,6 +3504,141 @@
             },
             "time": "2020-10-15T08:29:30+00:00"
         },
+        {
+            "name": "php-http/discovery",
+            "version": "1.20.0",
+            "source": {
+                "type": "git",
+                "url": "https://github.com/php-http/discovery.git",
+                "reference": "82fe4c73ef3363caed49ff8dd1539ba06044910d"
+            },
+            "dist": {
+                "type": "zip",
+                "url": "https://api.github.com/repos/php-http/discovery/zipball/82fe4c73ef3363caed49ff8dd1539ba06044910d",
+                "reference": "82fe4c73ef3363caed49ff8dd1539ba06044910d",
+                "shasum": ""
+            },
+            "require": {
+                "composer-plugin-api": "^1.0|^2.0",
+                "php": "^7.1 || ^8.0"
+            },
+            "conflict": {
+                "nyholm/psr7": "<1.0",
+                "zendframework/zend-diactoros": "*"
+            },
+            "provide": {
+                "php-http/async-client-implementation": "*",
+                "php-http/client-implementation": "*",
+                "psr/http-client-implementation": "*",
+                "psr/http-factory-implementation": "*",
+                "psr/http-message-implementation": "*"
+            },
+            "require-dev": {
+                "composer/composer": "^1.0.2|^2.0",
+                "graham-campbell/phpspec-skip-example-extension": "^5.0",
+                "php-http/httplug": "^1.0 || ^2.0",
+                "php-http/message-factory": "^1.0",
+                "phpspec/phpspec": "^5.1 || ^6.1 || ^7.3",
+                "sebastian/comparator": "^3.0.5 || ^4.0.8",
+                "symfony/phpunit-bridge": "^6.4.4 || ^7.0.1"
+            },
+            "type": "composer-plugin",
+            "extra": {
+                "class": "Http\\Discovery\\Composer\\Plugin",
+                "plugin-optional": true
+            },
+            "autoload": {
+                "psr-4": {
+                    "Http\\Discovery\\": "src/"
+                },
+                "exclude-from-classmap": [
+                    "src/Composer/Plugin.php"
+                ]
+            },
+            "notification-url": "https://packagist.org/downloads/",
+            "license": [
+                "MIT"
+            ],
+            "authors": [
+                {
+                    "name": "Márk Sági-Kazár",
+                    "email": "mark.sagikazar@gmail.com"
+                }
+            ],
+            "description": "Finds and installs PSR-7, PSR-17, PSR-18 and HTTPlug implementations",
+            "homepage": "http://php-http.org",
+            "keywords": [
+                "adapter",
+                "client",
+                "discovery",
+                "factory",
+                "http",
+                "message",
+                "psr17",
+                "psr7"
+            ],
+            "support": {
+                "issues": "https://github.com/php-http/discovery/issues",
+                "source": "https://github.com/php-http/discovery/tree/1.20.0"
+            },
+            "time": "2024-10-02T11:20:13+00:00"
+        },
+        {
+            "name": "php-http/multipart-stream-builder",
+            "version": "1.4.2",
+            "source": {
+                "type": "git",
+                "url": "https://github.com/php-http/multipart-stream-builder.git",
+                "reference": "10086e6de6f53489cca5ecc45b6f468604d3460e"
+            },
+            "dist": {
+                "type": "zip",
+                "url": "https://api.github.com/repos/php-http/multipart-stream-builder/zipball/10086e6de6f53489cca5ecc45b6f468604d3460e",
+                "reference": "10086e6de6f53489cca5ecc45b6f468604d3460e",
+                "shasum": ""
+            },
+            "require": {
+                "php": "^7.1 || ^8.0",
+                "php-http/discovery": "^1.15",
+                "psr/http-factory-implementation": "^1.0"
+            },
+            "require-dev": {
+                "nyholm/psr7": "^1.0",
+                "php-http/message": "^1.5",
+                "php-http/message-factory": "^1.0.2",
+                "phpunit/phpunit": "^7.5.15 || ^8.5 || ^9.3"
+            },
+            "type": "library",
+            "autoload": {
+                "psr-4": {
+                    "Http\\Message\\MultipartStream\\": "src/"
+                }
+            },
+            "notification-url": "https://packagist.org/downloads/",
+            "license": [
+                "MIT"
+            ],
+            "authors": [
+                {
+                    "name": "Tobias Nyholm",
+                    "email": "tobias.nyholm@gmail.com"
+                }
+            ],
+            "description": "A builder class that help you create a multipart stream",
+            "homepage": "http://php-http.org",
+            "keywords": [
+                "factory",
+                "http",
+                "message",
+                "multipart stream",
+                "stream"
+            ],
+            "support": {
+                "issues": "https://github.com/php-http/multipart-stream-builder/issues",
+                "source": "https://github.com/php-http/multipart-stream-builder/tree/1.4.2"
+            },
+            "time": "2024-09-04T13:22:54+00:00"
+        },
         {
             "name": "phpoffice/phpspreadsheet",
             "version": "1.29.10",
@@ -7155,74 +7381,6 @@
             },
             "time": "2020-07-09T08:09:16+00:00"
         },
-        {
-            "name": "ibex/crud-generator",
-            "version": "v2.1.2",
-            "source": {
-                "type": "git",
-                "url": "https://github.com/awais-vteams/laravel-crud-generator.git",
-                "reference": "3906f4a702c91bbe3a84d940c3021d1511834320"
-            },
-            "dist": {
-                "type": "zip",
-                "url": "https://api.github.com/repos/awais-vteams/laravel-crud-generator/zipball/3906f4a702c91bbe3a84d940c3021d1511834320",
-                "reference": "3906f4a702c91bbe3a84d940c3021d1511834320",
-                "shasum": ""
-            },
-            "require": {
-                "laravel/framework": "^10.30|^11.0",
-                "php": "^8.2"
-            },
-            "type": "library",
-            "extra": {
-                "laravel": {
-                    "providers": [
-                        "Ibex\\CrudGenerator\\CrudServiceProvider"
-                    ]
-                }
-            },
-            "autoload": {
-                "psr-4": {
-                    "Ibex\\CrudGenerator\\": "src/"
-                }
-            },
-            "notification-url": "https://packagist.org/downloads/",
-            "license": [
-                "MIT"
-            ],
-            "authors": [
-                {
-                    "name": "M Awais",
-                    "email": "asargodha@gmail.com"
-                }
-            ],
-            "description": "Laravel CRUD Generator",
-            "keywords": [
-                "alpine js",
-                "bootstrap css",
-                "crud",
-                "crud generator",
-                "laravel",
-                "laravel crud generator",
-                "laravel package",
-                "tailwind css"
-            ],
-            "support": {
-                "issues": "https://github.com/awais-vteams/laravel-crud-generator/issues",
-                "source": "https://github.com/awais-vteams/laravel-crud-generator/tree/v2.1.2"
-            },
-            "funding": [
-                {
-                    "url": "https://github.com/awais-vteams",
-                    "type": "github"
-                },
-                {
-                    "url": "https://ko-fi.com/mawais",
-                    "type": "ko_fi"
-                }
-            ],
-            "time": "2024-12-09T06:01:54+00:00"
-        },
         {
             "name": "laravel/pail",
             "version": "v1.2.2",
@@ -9316,12 +9474,12 @@
     ],
     "aliases": [],
     "minimum-stability": "stable",
-    "stability-flags": [],
+    "stability-flags": {},
     "prefer-stable": true,
     "prefer-lowest": false,
     "platform": {
         "php": "^8.2"
     },
-    "platform-dev": [],
+    "platform-dev": {},
     "plugin-api-version": "2.6.0"
 }

config/app.php

@@ -123,5 +123,6 @@ return [
         'store' => env('APP_MAINTENANCE_STORE', 'database'),
     ],
 
-    'api_url' => env('API_URL', 'http://localhost:8000')
+    'api_url' => env('API_URL', 'http://localhost:8000'),
+    'paginate_per_page' => 50
 ];

config/database.php

@@ -79,6 +79,8 @@ return [
             'engine' => null,
             'options' => extension_loaded('pdo_mysql') ? array_filter([
                 PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
+                PDO::ATTR_TIMEOUT => 40000,
+                PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION wait_timeout=40000; SET SESSION interactive_timeout=40000;"
             ]) : [],
         ],
 

config/queue.php

@@ -109,4 +109,10 @@ return [
         'table' => 'failed_jobs',
     ],
 
+    // set timeout queue
+
+    'worker' => [
+        'timeout' => 40000
+    ]
+
 ];

database/migrations/2025_02_19_113154_create_spatial_plannings_table.php

@@ -1,34 +0,0 @@
-<?php
-
-use Illuminate\Database\Migrations\Migration;
-use Illuminate\Database\Schema\Blueprint;
-use Illuminate\Support\Facades\Schema;
-
-return new class extends Migration
-{
-    /**
-     * Run the migrations.
-     */
-    public function up(): void
-    {
-        Schema::create('spatial_plannings', function (Blueprint $table) {
-            $table->id();
-            $table->string('name');
-            $table->string('kbli');
-            $table->text('kegiatan');
-            $table->decimal('luas',18,2);
-            $table->text('lokasi');
-            $table->string('nomor')->unique();
-            $table->date('sp_date');
-            $table->timestamps();
-        });
-    }
-
-    /**
-     * Reverse the migrations.
-     */
-    public function down(): void
-    {
-        Schema::dropIfExists('spatial_plannings');
-    }
-};

database/migrations/2025_02_20_050004_create_spatial_planning_table.php

@@ -0,0 +1,35 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\Schema;
+
+return new class extends Migration
+{
+    /**
+     * Run the migrations.
+     */
+    public function up(): void
+    {
+        Schema::create('spatial_planning', function (Blueprint $table) {
+            $table->id();
+            $table->timestamp('created_at')->default(DB::raw('CURRENT_TIMESTAMP'));
+            $table->timestamp('updated_at')->default(DB::raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'));
+            $table->string('name')->nullable();
+            $table->string('kbli')->nullable();
+            $table->string('activities')->nullable();
+            $table->string('area')->nullable();
+            $table->string('location')->nullable();
+            $table->string('number')->nullable();
+            $table->dateTime('date')->nullable();
+        });
+    }
+
+    /**
+     * Reverse the migrations.
+     */
+    public function down(): void
+    {
+        Schema::dropIfExists('spatial_planning');
+    }
+};

database/migrations/2025_02_20_073855_rename_spatial_planning_table.php

@@ -0,0 +1,24 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\Schema;
+
+return new class extends Migration
+{
+    /**
+     * Run the migrations.
+     */
+    public function up(): void
+    {
+        Schema::rename('spatial_planning', 'spatial_plannings');
+    }
+
+    /**
+     * Reverse the migrations.
+     */
+    public function down(): void
+    {
+        Schema::rename('spatial_plannings', 'spatial_planning');
+    }
+};

database/migrations/2025_02_26_140440_add_spatial_in_bigdata_resumes.php

@@ -0,0 +1,32 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\Schema;
+
+return new class extends Migration
+{
+    /**
+     * Run the migrations.
+     */
+    public function up(): void
+    {
+        Schema::table('bigdata_resumes', function (Blueprint $table) {
+            $table->integer('spatial_count')->default(0);
+            $table->decimal('spatial_sum', 20,2)->default(0);
+            $table->string('year');
+        });
+    }
+
+    /**
+     * Reverse the migrations.
+     */
+    public function down(): void
+    {
+        Schema::table('bigdata_resumes', function (Blueprint $table) {
+            $table->dropColumn('spatial_count');
+            $table->dropColumn('spatial_sum');
+            $table->dropColumn('year');
+        });
+    }
+};

database/migrations/2025_02_27_163347_add_formatted_registration_number_to_pbg_task_google_sheet.php

@@ -0,0 +1,28 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\Schema;
+
+return new class extends Migration
+{
+    /**
+     * Run the migrations.
+     */
+    public function up(): void
+    {
+        Schema::table('pbg_task_google_sheet', function (Blueprint $table) {
+            $table->string('formatted_registration_number')->nullable()->after('no_registrasi');
+        });
+    }
+
+    /**
+     * Reverse the migrations.
+     */
+    public function down(): void
+    {
+        Schema::table('pbg_task_google_sheet', function (Blueprint $table) {
+            $table->dropColumn('formatted_registration_number');
+        });
+    }
+};

database/migrations/2025_03_03_174851_add_some_column_in_bigdata_resumes.php

@@ -0,0 +1,38 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\Schema;
+
+return new class extends Migration
+{
+    /**
+     * Run the migrations.
+     */
+    public function up(): void
+    {
+        Schema::table('bigdata_resumes', function (Blueprint $table) {
+            $table->integer('waiting_click_dpmptsp_count')->default(0);
+            $table->decimal('waiting_click_dpmptsp_sum', 20,2)->default(0);
+            $table->integer('issuance_realization_pbg_count')->default(0);
+            $table->decimal('issuance_realization_pbg_sum', 20,2)->default(0);
+            $table->integer('process_in_technical_office_count')->default(0);
+            $table->decimal('process_in_technical_office_sum', 20,2)->default(0);
+        });
+    }
+
+    /**
+     * Reverse the migrations.
+     */
+    public function down(): void
+    {
+        Schema::table('bigdata_resumes', function (Blueprint $table) {
+            $table->dropColumn('waiting_click_dpmptsp_count');
+            $table->dropColumn('waiting_click_dpmptsp_sum');
+            $table->dropColumn('issuance_realization_pbg_count');
+            $table->dropColumn('issuance_realization_pbg_sum');
+            $table->dropColumn('process_in_technical_office_count');
+            $table->dropColumn('process_in_technical_office_sum');
+        });
+    }
+};

database/migrations/2025_03_05_211133_create_task_assignments_table.php

@@ -0,0 +1,48 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\Schema;
+
+return new class extends Migration
+{
+    /**
+     * Run the migrations.
+     */
+    public function up(): void
+    {
+        Schema::create('task_assignments', function (Blueprint $table) {
+            $table->id(); // Auto-increment primary key
+            
+            // Foreign key reference to pbg_tasks (uid column)
+            $table->string('pbg_task_uid');
+            $table->foreign('pbg_task_uid')->references('uuid')->on('pbg_task')->onDelete('cascade');
+
+            $table->unsignedBigInteger('user_id'); // Reference to users table
+            $table->string('name');
+            $table->string('username')->unique();
+            $table->string('email')->unique();
+            $table->string('phone_number')->nullable();
+            $table->unsignedInteger('role'); // Assuming role is numeric
+            $table->string('role_name');
+            $table->boolean('is_active')->default(true);
+            $table->json('file')->nullable(); // Store as JSON if 'file' is an array
+            $table->string('expertise')->nullable();
+            $table->string('experience')->nullable();
+            $table->boolean('is_verif')->default(false);
+            $table->string('uid')->unique();
+            $table->unsignedTinyInteger('status')->default(0); // Assuming status is a small integer
+            $table->string('status_name')->nullable();
+            $table->text('note')->nullable();
+            $table->timestamps();
+        });
+    }
+
+    /**
+     * Reverse the migrations.
+     */
+    public function down(): void
+    {
+        Schema::dropIfExists('task_assignments');
+    }
+};

database/migrations/2025_03_07_105725_modify_expertise_experience_in_task_assignments.php

@@ -0,0 +1,32 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\Schema;
+
+return new class extends Migration
+{
+    /**
+     * Run the migrations.
+     */
+    public function up(): void
+    {
+        Schema::table('task_assignments', function (Blueprint $table) {
+            $table->json('expertise')->nullable()->change();
+            $table->json('experience')->nullable()->change();
+            $table->bigInteger('ta_id')->nullable()->after('id');
+        });
+    }
+
+    /**
+     * Reverse the migrations.
+     */
+    public function down(): void
+    {
+        Schema::table('task_assignments', function (Blueprint $table) {
+            $table->text('expertise')->nullable()->change();
+            $table->text('experience')->nullable()->change();
+            $table->dropColumn('ta_id');
+        });
+    }
+};

database/seeders/DatabaseSeeder.php

@@ -15,17 +15,23 @@ class DatabaseSeeder extends Seeder
      */
     public function run(): void
     {
-        // User::factory(10)->create();
-
-        User::factory()->create([
+        User::updateOrCreate(
+            ['email' => 'user@demo.com'], // Kondisi pencarian
+            [
                 'name' => 'Darkone',
-            'email' => 'user@demo.com',
                 'email_verified_at' => now(),
                 'password' => Hash::make('password'),
                 'firstname' => 'John',
                 'lastname' => 'Doe',
                 'position' => 'crusial',
                 'remember_token' => Str::random(10),
+            ]
+        );
+
+        $this->call([
+            RoleSeeder::class,
+            MenuSeeder::class,
+            UsersRoleMenuSeeder::class
         ]);
     }
 }

database/seeders/MenuSeeder.php

@@ -0,0 +1,287 @@
+<?php
+
+namespace Database\Seeders;
+
+use Illuminate\Database\Console\Seeds\WithoutModelEvents;
+use Illuminate\Database\Seeder;
+use App\Models\Menu;
+use Illuminate\Support\Arr;
+
+class MenuSeeder extends Seeder
+{
+    /**
+     * Run the database seeds.
+     */
+    public function run(): void
+    {
+                $menus = [
+            [
+                "name" => "Dashboard",
+                "url" => "/dashboard",
+                "icon" => "mingcute:home-3-line",
+                "parent_id" => null,
+                "sort_order" => 1,
+                "children" => [
+                    [
+                        "name" => "Dashboard Pimpinan",
+                        "url" => "dashboard.home",
+                        "icon" => null,
+                        "sort_order" => 1,
+                    ],
+                    [
+                        "name" => "Dashboard PBG",
+                        "url" => "dashboard.pbg",
+                        "icon" => null,
+                        "sort_order" => 2,
+                    ],
+                    [
+                        "name" => "Dashboard Potensi",
+                        "url" => '/potentials',
+                        "icon" => null,
+                        "sort_order" => 3,
+                        "children" => [
+                            [
+                                "name" => "Luar Sistem",
+                                "url" => "dashboard.potentials.inside_system",
+                                "icon" => null,
+                                "sort_order" => 1,
+                            ],
+                            [
+                                "name" => "Dalam Sistem",
+                                "url" => "dashboard.potentials.outside_system",
+                                "icon" => null,
+                                "sort_order" => 2,
+                            ],
+                        ]
+                    ],
+                    [
+                        "name" => "PETA",
+                        "url" => "dashboard.maps",
+                        "icon" => null,
+                        "sort_order" => 4,
+                    ],
+                ],
+            ],
+            [
+                "name" => "Master",
+                "url" => "/master",
+                "icon" => "mingcute:cylinder-line",
+                "parent_id" => null,
+                "sort_order" => 2,
+                "children" => [
+                    [
+                        "name" => "Users",
+                        "url" => "users.index",
+                        "icon" => null,
+                        "sort_order" => 1,
+                    ],
+                ]
+            ],
+            [
+                "name" => "Settings",
+                "url" => "/settings",
+                "icon" => "mingcute:settings-6-line",
+                "parent_id" => null,
+                "sort_order" => 3,
+                "children" => [
+                    [
+                        "name" => "Syncronize",
+                        "url" => "settings.syncronize",
+                        "icon" => null,
+                        "sort_order" => 1,
+                    ],
+                    [
+                        "name" => "Menu",
+                        "url" => "menus.index",
+                        "icon" => null,
+                        "sort_order" => 2,
+                    ],
+                    [
+                        "name" => "Role",
+                        "url" => "roles.index",
+                        "icon" => null,
+                        "sort_order" => 3,
+                    ],
+                ]
+            ],
+            [
+                "name" => "Data Settings",
+                "url" => "/data-settings",
+                "icon" => "mingcute:settings-1-line",
+                "parent_id" => null,
+                "sort_order" => 4,
+                "children" => [
+                    [
+                        "name" => "Setting Dashboard",
+                        "url" => "data-settings.index",
+                        "icon" => null,
+                        "sort_order" => 1,
+                    ],
+                ]
+            ],
+            [
+                "name" => "Data",
+                "url" => "/data",
+                "icon" => "mingcute:task-line",
+                "parent_id" => null,
+                "sort_order" => 5,
+                "children" => [
+                    [
+                        "name" => "PBG",
+                        "url" => "pbg-task.index",
+                        "icon" => null,
+                        "sort_order" => 1,
+                    ],
+                    [
+                        "name" => "Reklame",
+                        "url" => "web.advertisements.index",
+                        "icon" => null,
+                        "sort_order" => 2,
+                    ],
+                    [
+                        "name" => "Usaha atau Industri",
+                        "url" => "business-industries.index",
+                        "icon" => null,
+                        "sort_order" => 3,
+                    ],
+                    [
+                        "name" => "UMKM",
+                        "url" => "web-umkm.index",
+                        "icon" => null,
+                        "sort_order" => 4,
+                    ],
+                    [
+                        "name" => "Pariwisata",
+                        "url" => "web-tourisms.index",
+                        "icon" => null,
+                        "sort_order" => 5,
+                    ],
+                    [
+                        "name" => "Tata Ruang",
+                        "url" => "web-spatial-plannings.index",
+                        "icon" => null,
+                        "sort_order" => 6,
+                    ],
+                    [
+                        "name" => "PDAM",
+                        "url" => "customers",
+                        "icon" => null,
+                        "sort_order" => 7,
+                    ],
+                    [
+                        "name" => "Google Sheets",
+                        "url" => "google-sheets",
+                        "icon" => null,
+                        "sort_order" => 8,
+                    ],
+                    [
+                        "name" => "TPA TPT",
+                        "url" => "tpa-tpt.index",
+                        "icon" => null,
+                        "sort_order" => 9,
+                    ],
+                ]
+            ],
+            [
+                "name" => "Laporan",
+                "url" => "/laporan",
+                "icon" => "mingcute:report-line",
+                "parent_id" => null,
+                "sort_order" => 6,
+                "children" => [
+                    [
+                        "name" => "Lap Pariwisata",
+                        "url" => "tourisms-report.index",
+                        "icon" => null,
+                        "sort_order" => 1,
+                    ],
+                    [
+                        "name" => "Lap Pimpinan",
+                        "url" => "bigdata-resumes",
+                        "icon" => null,
+                        "sort_order" => 2,
+                    ],
+                    [
+                        "name" => "Rekap Pembayaran",
+                        "url" => "payment-recaps",
+                        "icon" => null,
+                        "sort_order" => 3,
+                    ],
+                    [
+                        "name" => "Lap Rekap Data Pembayaran",
+                        "url" => "report-payment-recaps",
+                        "icon" => null,
+                        "sort_order" => 4,
+                    ],
+                    [
+                        "name" => "Lap PBG (PTSP)",
+                        "url" => "report-pbg-ptsp",
+                        "icon" => null,
+                        "sort_order" => 5,
+                    ],
+                ]
+            ],
+            [
+                "name" => "Neng Bedas",
+                "url" => "/chat",
+                "icon" => "mingcute:wechat-line",
+                "parent_id" => null,
+                "sort_order" => 7,
+                "children" => [
+                    [
+                        "name" => "Chat",
+                        "url" => "main-chatbot.index",
+                        "icon" => null,
+                        "sort_order" => 1,
+                    ],
+                ]
+            ],
+            [
+                "name" => "Approval",
+                "url" => "/approval",
+                "icon" => "mingcute:user-follow-2-line",
+                "parent_id" => null,
+                "sort_order" => 8,
+                "children" => [
+                    [
+                        "name" => "Approval Pejabat",
+                        "url" => "approval-list",
+                        "icon" => null,
+                        "sort_order" => 1,
+                    ],
+                ]
+            ],
+            [
+                "name" => "Tools",
+                "url" => "/tools",
+                "icon" => "mingcute:tool-line",
+                "parent_id" => null,
+                "sort_order" => 9,
+                "children" => [
+                    [
+                        "name" => "Undangan",
+                        "url" => "invitations",
+                        "icon" => null,
+                        "sort_order" => 1,
+                    ],
+                ]
+            ],
+        ];
+
+        foreach ($menus as $menuData) {
+            $this->createOrUpdateMenu($menuData);
+        }
+    }
+
+        private function createOrUpdateMenu($menuData, $parentId = null){
+        $menuData['parent_id'] = $parentId;
+
+        $menu = Menu::updateOrCreate(['name' => $menuData['name']], Arr::except($menuData, ['children']));
+
+        if(!empty($menuData['children'])){
+            foreach($menuData['children'] as $child){
+                $this->createOrUpdateMenu($child, $menu->id);
+            }
+        }
+    }
+}

database/seeders/RoleSeeder.php

@@ -0,0 +1,37 @@
+<?php
+
+namespace Database\Seeders;
+
+use App\Models\Role;
+use Illuminate\Database\Console\Seeds\WithoutModelEvents;
+use Illuminate\Database\Seeder;
+
+class RoleSeeder extends Seeder
+{
+    /**
+     * Run the database seeds.
+     */
+    public function run(): void
+    {
+        $roles = [
+            [
+                "name" => "superadmin",
+                "description" => "show all menus for super admins",
+            ],
+            [
+                "name" => "admin",
+                "description" => "show only necessary menus for admins",
+            ],
+            [
+                "name" => "operator",
+                "description" => "show only necessary menus for operators",
+            ],
+            [
+                "name" => "user",
+                "description" => "show only necessary menus for users",
+            ]
+        ];
+
+        Role::upsert($roles, ['name']);
+    }
+}

database/seeders/UsersRoleMenuSeeder.php

@@ -15,262 +15,52 @@ class UsersRoleMenuSeeder extends Seeder
      */
    public function run(): void
     {
-        $roles = [
-            [
-                "name" => "superadmin",
-                "description" => "show all menus for super admins",
+        // Fetch roles in a single query
+        $roles = Role::whereIn('name', ['superadmin', 'admin', 'operator'])->get()->keyBy('name');
+
+        // Fetch all menus in a single query and index by name
+        $menus = Menu::whereIn('name', [
+            'Dashboard', 'Master', 'Settings', 'Data Settings', 'Data', 'Laporan', 'Neng Bedas', 
+            'Approval', 'Tools', 'Dashboard Pimpinan', 'Dashboard PBG', 'Users', 'Syncronize', 
+            'Menu', 'Role', 'Setting Dashboard', 'PBG', 'Reklame', 'Usaha atau Industri', 'Pariwisata', 
+            'Lap Pariwisata', 'UMKM', 'Dashboard Potensi', 'Tata Ruang', 'PDAM', 'PETA', 
+            'Lap Pimpinan', 'Chat', 'Dalam Sistem', 'Luar Sistem', 'Google Sheets', 'TPA TPT', 
+            'Approval Pejabat', 'Undangan', 'Rekap Pembayaran', 'Lap Rekap Data Pembayaran', 'Lap PBG (PTSP)'
+        ])->get()->keyBy('name');
+
+        // Define access levels for each role
+        $permissions = [
+            'superadmin' => [
+                'Dashboard', 'Master', 'Settings', 'Data Settings', 'Data', 'Laporan', 'Neng Bedas', 
+                'Approval', 'Tools', 'Dashboard Pimpinan', 'Dashboard PBG', 'Users', 'Syncronize', 
+                'Menu', 'Role', 'Setting Dashboard', 'PBG', 'Reklame', 'Usaha atau Industri', 'Pariwisata', 
+                'Lap Pariwisata', 'UMKM', 'Dashboard Potensi', 'Tata Ruang', 'PDAM', 'Dalam Sistem', 
+                'Luar Sistem', 'Lap Pimpinan', 'Chat', 'Google Sheets', 'TPA TPT', 'Approval Pejabat', 
+                'Undangan', 'Rekap Pembayaran', 'Lap Rekap Data Pembayaran', 'Lap PBG (PTSP)'
             ],
-            [
-                "name" => "admin",
-                "description" => "show only necessary menus for admins",
-            ],
-            [
-                "name" => "operator",
-                "description" => "show only necessary menus for operators",
-            ]
+            'admin' => ['Dashboard', 'Master', 'Settings'],
+            'operator' => ['Dashboard', 'Data', 'Laporan']
         ];
 
-        Role::upsert($roles, ['name']);
+        // Define permission levels
+        $superadminPermissions = ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true];
+        $adminPermissions = ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true];
+        $operatorPermissions = ["allow_show" => true, "allow_create" => false, "allow_update" => false, "allow_destroy" => false];
 
-        $parent_menus = [
-            [
-                "name" => "Dashboard",
-                "url" => "/dashboard",
-                "icon" => "mingcute:home-3-line",
-                "parent_id" => null,
-                "sort_order" => 1,
-            ],
-            [
-                "name" => "Master",
-                "url" => "/master",
-                "icon" => "mingcute:cylinder-line",
-                "parent_id" => null,
-                "sort_order" => 2,
-            ],
-            [
-                "name" => "Settings",
-                "url" => "/settings",
-                "icon" => "mingcute:settings-6-line",
-                "parent_id" => null,
-                "sort_order" => 3,
-            ],
-            [
-                "name" => "Data Settings",
-                "url" => "/data-settings",
-                "icon" => "mingcute:settings-1-line",
-                "parent_id" => null,
-                "sort_order" => 4,
-            ],
-            [
-                "name" => "Data",
-                "url" => "/data",
-                "icon" => "mingcute:task-line",
-                "parent_id" => null,
-                "sort_order" => 5,
-            ],
-            [
-                "name" => "Laporan",
-                "url" => "/laporan",
-                "icon" => "mingcute:task-line",
-                "parent_id" => null,
-                "sort_order" => 6,
-            ]
-        ];
-
-        foreach ($parent_menus as $parent_menu) {
-            Menu::firstOrCreate(['name' => $parent_menu['name']], $parent_menu);
+        // Assign menus to roles
+        foreach ($permissions as $roleName => $menuNames) {
+            $role = $roles[$roleName] ?? null;
+            if ($role) {
+                $role->menus()->sync(
+                    collect($menuNames)->mapWithKeys(fn($menuName) => [
+                        $menus[$menuName]->id => ($roleName === 'superadmin' ? $superadminPermissions : 
+                                                ($roleName === 'admin' ? $adminPermissions : $operatorPermissions))
+                    ])->toArray()
+                );
+            }
         }
         
-        // Attach Menus to Roles
-        $superadmin = Role::where('name', 'superadmin')->first();
-        $admin = Role::where('name', 'admin')->first();
-        $operator = Role::where('name', 'operator')->first();
-
-        $dashboard = Menu::where('name', 'Dashboard')->first();
-        $master = Menu::where('name', 'Master')->first();
-        $settings = Menu::where('name', 'Settings')->first();
-        $dataSettings = Menu::where('name', 'Data Settings')->first();
-        $data = Menu::where('name', 'Data')->first();
-        $laporan = Menu::where('name', 'Laporan')->first();
-
-        // create children menu
-        $children_menus = [
-            [
-                "name" => "Dashboard Pimpinan",
-                "url" => "dashboard.home",
-                "icon" => null,
-                "parent_id" => $dashboard->id,
-                "sort_order" => 1,
-            ],
-            [
-                "name" => "Dashboard PBG",
-                "url" => "dashboard.pbg",
-                "icon" => null,
-                "parent_id" => $dashboard->id,
-                "sort_order" => 2,
-            ],
-            [
-                "name" => "Dashboard Potensi",
-                "url" => "dashboard.lack_of_potential",
-                "icon" => null,
-                "parent_id" => $dashboard->id,
-                "sort_order" => 3,
-            ],
-            [
-                "name" => "Users",
-                "url" => "users.index",
-                "icon" => null,
-                "parent_id" => $master->id,
-                "sort_order" => 1,
-            ],
-            [
-                "name" => "Syncronize",
-                "url" => "settings.syncronize",
-                "icon" => null,
-                "parent_id" => $settings->id,
-                "sort_order" => 1,
-            ],
-            [
-                "name" => "Menu",
-                "url" => "menus.index",
-                "icon" => null,
-                "parent_id" => $settings->id,
-                "sort_order" => 2,
-            ],
-            [
-                "name" => "Role",
-                "url" => "roles.index",
-                "icon" => null,
-                "parent_id" => $settings->id,
-                "sort_order" => 3,
-            ],
-            [
-                "name" => "Setting Dashboard",
-                "url" => "data-settings.index",
-                "icon" => null,
-                "parent_id" => $dataSettings->id,
-                "sort_order" => 1,
-            ],
-            [
-                "name" => "PBG",
-                "url" => "pbg-task.index",
-                "icon" => null,
-                "parent_id" => $data->id,
-                "sort_order" => 1,
-            ],
-            [
-                "name" => "Reklame",
-                "url" => "advertisements.index",
-                "icon" => null,
-                "parent_id" => $data->id,
-                "sort_order" => 2,
-            ],
-            [
-                "name" => "Usaha atau Industri",
-                "url" => "business-industries.index",
-                "icon" => null,
-                "parent_id" => $data->id,
-                "sort_order" => 3,
-            ],
-            [
-                "name" => "UMKM",
-                "url" => "umkm.index",
-                "icon" => null,
-                "parent_id" => $data->id,
-                "sort_order" => 4,
-            ],
-            [
-                "name" => "Pariwisata",
-                "url" => "tourisms.index",
-                "icon" => null,
-                "parent_id" => $data->id,
-                "sort_order" => 5,
-            ],
-            [
-                "name" => "Tata Ruang",
-                "url" => "spatial-plannings",
-                "icon" => null,
-                "parent_id" => $data->id,
-                "sort_order" => 6,
-            ],
-            [
-                "name" => "PDAM",
-                "url" => "customers",
-                "icon" => null,
-                "parent_id" => $data->id,
-                "sort_order" => 7,
-            ],
-            [
-                "name" => "Lap Pariwisata",
-                "url" => "tourisms.index",
-                "icon" => null,
-                "parent_id" => $laporan->id,
-                "sort_order" => 1,
-            ],
-        ];
-
-        foreach ($children_menus as $child_menu) {
-            Menu::firstOrCreate(['name' => $child_menu['name']], $child_menu);
-        }
-
-        $dashboard_pimpinan = Menu::where('name', 'Dashboard Pimpinan')->first();
-        $dashboard_pbg = Menu::where('name', 'Dashboard PBG')->first();
-        $users = Menu::where('name', 'Users')->first();
-        $syncronize = Menu::where('name', 'Syncronize')->first();
-        $setting_menu = Menu::where('name', 'Menu')->first();
-        $setting_role = Menu::where('name', 'Role')->first();
-        $setting_dashboard = Menu::where('name', 'Setting Dashboard')->first();
-        $setting_pbg = Menu::where('name', 'PBG')->first();
-        $reklame = Menu::where('name', 'Reklame')->first();
-        $businessIndustries = Menu::where('name', 'Usaha atau Industri')->first();
-        $pariwisata = Menu::where('name', 'Pariwisata')->first();
-        $laporan_pariwisata = Menu::where('name', 'Lap Pariwisata')->first();
-        $umkm = Menu::where('name', 'UMKM')->first();
-        $lack_of_potentials = Menu::where('name', 'Dashboard Potensi')->first();
-        $spatial_plannings = Menu::where('name', 'Tata Ruang')->first();
-        $pdam = Menu::where('name', 'PDAM')->first();
-
-        // Superadmin gets all menus
-        $superadmin->menus()->sync([
-            // parent
-            $dashboard->id => ["allow_show" => true, "allow_create" => false, "allow_update" => false, "allow_destroy" => false],
-            $master->id => ["allow_show" => true, "allow_create" => false, "allow_update" => false, "allow_destroy" => false],
-            $settings->id => ["allow_show" => true, "allow_create" => false, "allow_update" => false, "allow_destroy" => false],
-            $dataSettings->id => ["allow_show" => true, "allow_create" => false, "allow_update" => false, "allow_destroy" => false],
-            $data->id => ["allow_show" => true, "allow_create" => false, "allow_update" => false, "allow_destroy" => false],
-            $laporan->id => ["allow_show" => true, "allow_create" => false, "allow_update" => false, "allow_destroy" => false],
-            // children
-            $dashboard_pimpinan->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $dashboard_pbg->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $users->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $syncronize->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $setting_menu->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $setting_role->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $setting_dashboard->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $setting_pbg->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $reklame->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $businessIndustries->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $pariwisata->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $laporan_pariwisata->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $umkm->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $lack_of_potentials->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $spatial_plannings->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $pdam->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-        ]);
-
-        // Admin gets limited menus
-        $admin->menus()->sync([
-            $dashboard->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $master->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $settings->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-        ]);
-
-        // Operator gets only basic menus with full permissions
-        $operator->menus()->sync([
-            $dashboard->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-            $data->id => ["allow_show" => true, "allow_create" => true, "allow_update" => true, "allow_destroy" => true],
-        ]);
         // Attach User to role super admin
-        User::findOrFail(1)->roles()->sync([$superadmin->id]);
+        User::findOrFail(1)->roles()->sync([$roles['superadmin']->id]);
     }
 }

database/view_query/v_advertisements.sql

@@ -0,0 +1,21 @@
+CREATE VIEW v_advertisements AS 
+SELECT 
+    a.no,
+    a.business_name,
+    a.npwpd,
+    a.advertisement_type,
+    a.advertisement_content,
+    a.business_address,
+    a.advertisement_location,
+    v.village_name AS village_name,
+    d.district_name AS district_name,
+    a.length,
+    a.width,
+    a.viewing_angle,
+    a.face,
+    a.area,
+    a.angle,
+    a.contact
+FROM advertisements a 
+JOIN villages v ON a.village_code = v.village_code
+JOIN districts d ON a.district_code = d.district_code;

database/view_query/v_tourism_base_kbli.sql

@@ -0,0 +1,8 @@
+CREATE VIEW v_tourisms_based_kbli AS
+SELECT kbli_title, total_records
+FROM (
+    SELECT kbli, kbli_title, COUNT(*) AS total_records
+    FROM tourisms
+    GROUP BY kbli, kbli_title
+) AS subquery
+ORDER BY total_records DESC;

database/view_query/v_tourisms.sql

@@ -0,0 +1,29 @@
+CREATE VIEW v_tourisms AS 
+SELECT 
+	t.project_id,
+	t.project_type_id,
+	t.nib,
+	t.business_name,
+	t.oss_publication_date,
+	t.investment_status_description,
+	t.business_form,
+	t.project_risk,
+	t.project_name,
+	t.business_scale,
+	t.business_address,
+	v.village_name as village_name,
+	d.district_name as district_name,
+	t.longitude,
+	t.latitude,
+	t.project_submission_date,
+	t.kbli_title,
+	t.supervisory_sector,
+	t.user_name,
+	t.email,
+	t.contact,
+	t.land_area_in_m2,
+	t.investment_amount,
+	t.tki
+FROM tourisms t
+JOIN villages v on t.village_code = v.village_code
+JOIN districts d on t.district_code = d.district_code;