create file automation deployment using sh

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){
+    public function __construct(){
-        $this->service_simbg = $service_simbg;
         parent::__construct();
     }
     public function handle()
     {
+        SyncronizeSIMBG::dispatch();
         Log::info("running scheduler daily scraping");
-        $this->service_simbg->syncTaskList();
     }
 }

app/Http/Controllers/Api/BigDataResumeController.php

@@ -19,11 +19,12 @@ class BigDataResumeController extends Controller
         try{
             $filterDate = $request->get("filterByDate");
 
-            // If filterByDate is "latest" or empty, get the most recent record
             if (!$filterDate || $filterDate === "latest") {
-                $big_data_resume = BigdataResume::latest()->first();
+                $big_data_resume = BigdataResume::where('year', now()->year)->latest()->first();
+                if (!$big_data_resume) {
+                    return $this->response_empty_resume();
+                }
             } else {
-                // Filter by specific date
                 $big_data_resume = BigdataResume::whereDate('created_at', $filterDate)
                     ->orderBy('id', 'desc')
                     ->first();
@@ -38,13 +39,17 @@ class BigDataResumeController extends Controller
                 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 = floatval(optional($data_settings->where('key', 'REALISASI_TERBIT_PBG_SUM')->first())->value);
+            $realisasi_terbit_pbg_sum = cleanNumber(optional($data_settings->where('key', 'REALISASI_TERBIT_PBG_SUM')->first())->value);
-            $realisasi_terbit_pbg_count = floatval(optional($data_settings->where('key', 'REALISASI_TERBIT_PBG_COUNT')->first())->value);
+            $realisasi_terbit_pbg_count = cleanNumber(optional($data_settings->where('key', 'REALISASI_TERBIT_PBG_COUNT')->first())->value);
-            $menuggu_klik_dpmptsp_sum = floatval(optional($data_settings->where('key', 'MENUNGGU_KLIK_DPMPTSP_SUM')->first())->value);
+            $menunggu_klik_dpmptsp_sum = cleanNumber(optional($data_settings->where('key', 'MENUNGGU_KLIK_DPMPTSP_SUM')->first())->value);
-            $menuggu_klik_dpmptsp_count = floatval(optional($data_settings->where('key', 'MENUNGGU_KLIK_DPMPTSP_COUNT')->first())->value);
+            $menunggu_klik_dpmptsp_count = cleanNumber(optional($data_settings->where('key', 'MENUNGGU_KLIK_DPMPTSP_COUNT')->first())->value);
-            $proses_dinas_teknis_sum = floatval(optional($data_settings->where('key', 'PROSES_DINAS_TEKNIS_SUM')->first())->value);
+            $proses_dinas_teknis_sum = cleanNumber(optional($data_settings->where('key', 'PROSES_DINAS_TEKNIS_SUM')->first())->value);
-            $proses_dinas_teknis_count = floatval(optional($data_settings->where('key', 'PROSES_DINAS_TEKNIS_COUNT')->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;
@@ -82,8 +87,8 @@ class BigDataResumeController extends Controller
             ? 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 && $menuggu_klik_dpmptsp_sum > 0
+            $menunggu_klik_dpmptsp_percentage = $big_data_resume->verified_sum > 0 && $menunggu_klik_dpmptsp_sum > 0
-            ? round(($menuggu_klik_dpmptsp_sum / $big_data_resume->verified_sum) * 100, 2) : 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
@@ -134,8 +139,8 @@ class BigDataResumeController extends Controller
                     'percentage' => $realisasi_terbit_percentage
                 ],
                 'menunggu_klik_dpmptsp' => [
-                    'sum' => $menuggu_klik_dpmptsp_sum,
+                    'sum' => $menunggu_klik_dpmptsp_sum,
-                    'count' => $menuggu_klik_dpmptsp_count,
+                    'count' => $menunggu_klik_dpmptsp_count,
                     'percentage' => $menunggu_klik_dpmptsp_percentage
                 ],
                 'proses_dinas_teknis' => [
@@ -150,6 +155,8 @@ class BigDataResumeController extends Controller
         }
     }
 
+
+
     public function bigdata_report(Request $request){
         try{
             $query = BigdataResume::query()->orderBy('id', 'desc');
@@ -158,7 +165,7 @@ class BigDataResumeController extends Controller
                 $query->where('name', 'LIKE', '%'.$request->input('search').'%');
             }
 
-            $query = $query->paginate(15);
+            $query = $query->paginate(config('app.paginate_per_page', 50));
             return  BigdataResumeResource::collection($query)->response()->getData(true);
         }catch(\Exception $e){
             Log::error($e->getMessage());
@@ -166,38 +173,6 @@ class BigDataResumeController extends Controller
         }
     }
 
-    /**
-     * 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)
-    {
-        //
-    }
-
     private function response_empty_resume(){
         $result = [
             'target_pad' => [

app/Http/Controllers/Api/BusinessOrIndustriesController.php

@@ -31,7 +31,7 @@ class BusinessOrIndustriesController extends Controller
             });
         }
 
-        return response()->json($query->paginate());
+        return response()->json($query->paginate(config('app.paginate_per_page', 50)));
     }
 
     /**

app/Http/Controllers/Api/ChatbotController.php

@@ -0,0 +1,84 @@
+<?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;
+
+class ChatbotController extends Controller
+{
+    protected $openAIService;
+
+    public function __construct(OpenAIService $openAIService)
+    {
+        $this->openAIService = $openAIService;
+    }
+
+    public function generateText(Request $request)
+    {
+        info($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",
+        };
+
+        if ($main_content === "UNKNOWN") {
+            return response()->json(['response' => 'Invalid tab_active value.'], 400);
+        }
+
+        info($main_content);
+
+        // Klasifikasi apakah pertanyaan butuh database atau bisa dijawab langsung
+        $classifyResponse = $this->openAIService->generateClassifyMainContent($request->input('prompt'), $main_content);
+
+        if ($classifyResponse === "DATABASE") {
+            $queryResponse = $this->openAIService->generateQueryBasedMainContent($request->input('prompt'), $main_content);
+            if (is_array($queryResponse)) {
+                info('Query Response is an array: ', $queryResponse);
+            } else {
+                info('Query Response is a string: ' . $queryResponse);
+            }
+            
+            // Validasi query dua kali sebelum eksekusi
+            if (
+                $this->openAIService->validateSyntaxQuery($queryResponse) === "VALID" &&
+                $this->openAIService->validateSyntaxQuery($queryResponse) === "VALID"
+            ) {
+                info($queryResponse);
+                $queryResponse = str_replace(['```sql', '```'], '', $queryResponse);
+                $resultQuery = DB::select($queryResponse);
+                $formattedResultQuery = json_encode($resultQuery, JSON_PRETTY_PRINT);
+                $nlpResult = $this->openAIService->generateNLPFromQuery($request->input('prompt'), $formattedResultQuery);
+                $finalGeneratedText =$this->openAIService->generateFinalText($nlpResult);
+                return response()->json(['response' => $finalGeneratedText]);
+            }
+
+            return response()->json(['response' => ''], 400);
+        }
+
+        if ($classifyResponse === "GENERAL") {
+            $nlpResult = $this->openAIService->generateGeneralText($request->input('prompt'), $main_content);
+            $finalGeneratedText =$this->openAIService->generateFinalText($nlpResult);
+            return response()->json(['response' => $finalGeneratedText]);
+        }
+
+        return response()->json(['response' => ''], 500);
+    }
+
+
+    private function classifyContent(string $prompt) {
+        $classifyResponse = $this->openAIService->generateClassifyContent($prompt);
+        return $classifyResponse;
+    }
+}

app/Http/Controllers/Api/CustomersController.php

@@ -24,7 +24,7 @@ class CustomersController extends Controller
                 ->orWhere('nama', '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/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

@@ -7,6 +7,7 @@ use App\Models\Advertisement;
 use App\Models\Customer;
 use App\Models\SpatialPlanning;
 use Illuminate\Http\Request;
+use App\Models\TourismBasedKBLI;
 
 class LackOfPotentialController extends Controller
 {
@@ -16,11 +17,13 @@ class LackOfPotentialController extends Controller
             $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
+                'total_tata_ruang' => $total_tata_ruang,
+                'data_report' => $data_report_tourism,
             ], 200);
         }catch(\Exception $e){
             return response()->json([

app/Http/Controllers/Api/MenusController.php

@@ -22,7 +22,7 @@ class MenusController extends Controller
             $query = $query->where("name", "like", "%".$request->get("search")."%");
         }
 
-        return response()->json($query->paginate());
+        return response()->json($query->paginate(config('app.paginate_per_page', 50)));
     }
 
     /**

app/Http/Controllers/Api/RequestAssignmentController.php

@@ -17,7 +17,8 @@ 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());
     }

app/Http/Controllers/Api/RolesController.php

@@ -20,7 +20,7 @@ class RolesController extends Controller
             $query = $query->where('name', 'like', '%'. $request->get('search') . '%');
         }
 
-        return response()->json($query->paginate());
+        return response()->json($query->paginate(config('app.paginate_per_page', 50)));
     }
 
     /**

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");
+        SyncronizeSIMBG::dispatch();
-        return $this->resSuccess("Success execute scraping service please wait");
+        return $this->resSuccess(["message" => "Success execute scraping service on background, check status for more"]);
     }
 
     /**

app/Http/Controllers/Api/UsersController.php

@@ -31,7 +31,7 @@ class UsersController extends Controller
         if($request->has('search') && !empty($request->get("search"))){
             $query->where('name', '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/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/Settings/SyncronizeController.php

@@ -17,12 +17,12 @@ class SyncronizeController extends Controller
     }
 
     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');
     }
 

app/Http/Resources/BigdataResumeResource.php

@@ -34,7 +34,16 @@ class BigdataResumeResource extends JsonResource
 
             '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/Jobs/SyncronizeSIMBG.php

@@ -0,0 +1,27 @@
+<?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 function __construct()
+    {
+    }
+
+    public function handle(): void
+    {
+        $serviceSIMBG = app(ServiceSIMBG::class);
+        $serviceSIMBG->syncTaskPBG();
+    }
+}

app/Models/BigdataResume.php

@@ -23,7 +23,13 @@ class BigdataResume extends Model
         'non_business_sum',
         'spatial_count',
         'spatial_sum',
-        'year'
+        '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()
@@ -31,7 +37,7 @@ class BigdataResume extends Model
         return $this->belongsTo(ImportDatasource::class, 'import_datasource_id');
     }
 
-    public static function generateResumeData($import_datasource_id, $year){
+    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')
@@ -82,9 +88,15 @@ class BigdataResume extends Model
         $potention_total = $query_potention->total_retribution ?? 0;
 
         $query_spatial_plannings = once(function () use ($year) {
-            $query = PbgTask::join('spatial_plannings as sp', 'pbg_task.document_number', '=', 'sp.number')
+            $query = PbgTask::leftJoin('spatial_plannings as sp', 'pbg_task.document_number', '=', 'sp.number')
-                ->join('pbg_task_retributions as ptr', 'ptr.pbg_task_uid', '=', 'pbg_task.uuid')
+                ->leftJoin('pbg_task_retributions as ptr', 'ptr.pbg_task_uid', '=', 'pbg_task.uuid')
-                ->selectRaw('COUNT(DISTINCT pbg_task.id) as task_count, SUM(ptr.nilai_retribusi_bangunan) as total_retribution');
+                ->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);
@@ -94,7 +106,7 @@ class BigdataResume extends Model
         });
 
         $spatial_planning_count = $query_spatial_plannings->task_count ?? 0;
-        $spatial_planning_total = $query_spatial_plannings->total_retribution ?? 0;
+        $spatial_planning_total = $query_spatial_plannings->total_retribution;
 
         $potention_count -= $spatial_planning_count;
         $potention_total -= $spatial_planning_total;
@@ -113,7 +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
+            '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/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,9 @@ class AppServiceProvider extends ServiceProvider
      */
     public function register(): void
     {
-        //
+        $this->app->singleton(ServiceSIMBG::class, function ($app) {
+            return new ServiceSIMBG($app->make(GoogleSheetService::class));
+        });
     }
 
     /**

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,157 @@
+<?php
+
+namespace App\Services;
+
+use OpenAI;
+use Illuminate\Support\Facades\Storage;
+
+class OpenAIService
+{
+    protected $client;
+
+    public function __construct()
+    {
+        $this->client = OpenAI::client(env('OPENAI_API_KEY'));
+    }
+
+    public function generateGeneralText($prompt, $mainContent)
+    {
+        $response = $this->client->chat()->create([
+            'model' => 'gpt-4o-mini',
+            'messages' => [
+                [
+                    'role' => 'system', 
+                    'content' => "You are an expert assistant. Your task is to generate a concise response based on the provided prompt and main content. 
+
+                    Guidelines:
+                    - Summarize the key points in exactly 5 bullet points.
+                    - Ensure the response is clear and relevant to the prompt.
+                    - Use simple and professional language."
+                ],
+                ['role' => 'user', 'content' => "Prompt: $prompt \nMain Content: $mainContent"],
+            ],
+        ]);
+
+        return trim($response['choices'][0]['message']['content'] ?? 'No response');
+    }
+
+    public function generateClassifyMainContent($prompt, $mainContent)
+    {
+        $response = $this->client->chat()->create([
+            'model' => 'gpt-4o-mini',
+            'messages' => [
+                [
+                    'role' => 'system', 
+                    'content' => "You are an expert assistant in classifying questions based on whether their answers must be retrieved from a database or can be explained generally.
+                    Your task is to return one of the following two labels:
+                    - \"DATABASE\" → If the question requires specific data that can only be obtained from a database.
+                    - \"GENERAL\" → If the question can be answered without accessing a database.
+                    
+                    Consider the following context: \"$mainContent\"
+                    
+                    Respond with only one of the labels: \"DATABASE\" or \"GENERAL\"."
+                ],
+                ['role' => 'user', 'content' => $prompt],
+            ],
+        ]);
+
+        return trim($response['choices'][0]['message']['content'] ?? 'No response');
+    }
+
+    public function generateQueryBasedMainContent($prompt, $mainContent)
+    {
+        // 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');
+    }    
+
+}

app/Services/ServiceSIMBG.php

@@ -15,6 +15,9 @@ 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 +27,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 +43,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(){
@@ -112,13 +117,175 @@ class ServiceSIMBG
         }
     }
 
-    public function syncTaskList()
+    public function syncTaskPBG()
     {
         try {
             $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"] = $row[2] ?? null;
+                        $data_setting_result["MENUNGGU_KLIK_DPMPTSP_SUM"] = $row[3] ?? null;
+                    } elseif ($found_section === "REALISASI_TERBIT_PBG") {
+                        $data_setting_result["REALISASI_TERBIT_PBG_COUNT"] = $row[2] ?? null;
+                        $data_setting_result["REALISASI_TERBIT_PBG_SUM"] = $row[4] ?? null;
+                    } elseif ($found_section === "PROSES_DINAS_TEKNIS") {
+                        $data_setting_result["PROSES_DINAS_TEKNIS_COUNT"] = $row[2] ?? null;
+                        $data_setting_result["PROSES_DINAS_TEKNIS_SUM"] = $row[3] ?? null;
+                    }
+        
+                    // Reset section tracking after capturing "Grand Total"
+                    $found_section = null;
+                }
+            }
+
+            foreach ($data_setting_result as $key => $value) {
+                DataSetting::updateOrInsert(
+                    ["key" => $key], // Find by key
+                    ["value" => $value] // Update or insert value
+                );
+            }
+            $mapToUpsert = [];
+            $count = 0;
+
+            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,
@@ -126,8 +293,6 @@ class ServiceSIMBG
                 ]);
                 return $this->resError("Failed to retrieve token");
             }
-
-            $initResToken = $this->getToken();
             $apiToken = $initResToken->original['data']['token']['access'];
             $headers = ['Authorization' => "Bearer " . $apiToken];
 
@@ -217,8 +382,8 @@ class ServiceSIMBG
                 }
             }
 
-            BigdataResume::generateResumeData($importDatasource->id, "all");
+            BigdataResume::generateResumeData($importDatasource->id, "all", $data_setting_result);
-            BigdataResume::generateResumeData($importDatasource->id, now()->year);
+            BigdataResume::generateResumeData($importDatasource->id, now()->year, $data_setting_result);
 
             // Final update after processing all pages
             $importDatasource->update([
@@ -324,5 +489,59 @@ class ServiceSIMBG
             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;
+        }
+
+        // Otherwise, cast to integer
+        return (int) $value;
+    }
+
+    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,7 +15,8 @@
         "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",

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
 ];

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');
+        });
+    }
+};

deploy.sh

@@ -0,0 +1,38 @@
+GIT_BRANCH="dev"
+PHP_VERSION="php8.3"
+
+echo "🚀 Starting deployment..."
+php artisan down
+
+echo "📥 Pulling latest changes from Git..."
+git fetch origin $GIT_BRANCH
+git reset --hard origin/$GIT_BRANCH
+git pull origin $GIT_BRANCH
+
+echo "⚡ Installing NPM dependencies and building assets..."
+npm ci --no-audit --no-fund
+npm run build
+
+echo "📦 Installing composer dependencies..."
+composer install --no-interaction --optimize-autoloader
+
+echo "🗄️ Running migrations..."
+php artisan migrate --force
+
+echo "⚡ Optimizing application..."
+php artisan cache:clear
+php artisan config:clear
+php artisan config:cache
+php artisan route:cache
+php artisan view:cache
+
+echo "🔄 Restarting PHP service..."
+systemctl restart $PHP_VERSION-fpm
+
+echo "🔁 Restarting Supervisor queue workers..."
+supervisorctl stop all
+supervisorctl reload
+supervisorctl start all
+
+php artisan up
+echo "🚀 Deployment completed successfully!"

public/index.php

@@ -5,6 +5,7 @@ use Illuminate\Http\Request;
 define('LARAVEL_START', microtime(true));
 
 ini_set('max_execution_time',14400);
+ini_set('memory_limit', '2G');
 
 // Determine if the application is in maintenance mode...
 if (file_exists($maintenance = __DIR__.'/../storage/framework/maintenance.php')) {

public/templates/contentTemplatePrompt.json

@@ -0,0 +1,11 @@
+{
+    "RETRIBUTION": {
+        "prompt": "You are a MariaDB SQL expert. Your task is to generate an efficient and optimized SQL query based on user input.\n\n        The query should retrieve data from the view table `v_pbg_task_with_retributions`, specifically selecting the following columns:\n\n        - nilai_retribusi_bangunan\n        - land_certificate_phase\n        - due_date\n        - consultation_type\n        - function_type\n        - slf_status_name\n        - slf_status\n        - status_name\n        - status\n        - address\n        - document_number\n        - registration_number\n        - application_type_name\n        - application_type\n        - owner_name\n        - name\n\n        Ensure the query is well-structured, uses best indexing practices, and avoids performance bottlenecks.\n\n        Consider the following context: \"$mainContent\".\n\n        Always return only the SQL query without any additional explanation."
+    },
+    "DOCUMENT VALIDATION": {
+        "prompt": "You are a MariaDB SQL expert. Your task is to generate an efficient and optimized SQL query based on user input.\n\n        The query should retrieve data from the view table `pbg_task`, specifically selecting the following columns:\n\n        - name\n        - owner_name\n        - application_type\n        - application_type_name\n        - registration_number\n        - document_number\n        - address\n        - status_name\n        - slf_status\n        - slf_status_name\n        - function_type\n        - consultation_type\n        - function_type\n        - consultation_type\n        - due_date\n        - land_certificate_phase\n\n        Ensure the query is well-structured, uses best indexing practices, and avoids performance bottlenecks.\n\n        Consider the following context: \"$mainContent\".\n\n        Always return only the SQL query without any additional explanation."
+    },
+    "DATA SUMMARY": {
+        "prompt": "You are a MariaDB SQL expert. Your task is to generate an efficient and optimized SQL query based on user input.\n\n        The query should retrieve data from the view table `bigdata_resumes`, specifically selecting the following columns:\n\n        - potention_count\n        - potention_sum\n        - non_verified_count\n        - non_verified_sum\n        - verified_sum\n        - verified_count\n        - business_count\n        - business_sum\n        - non_business_count\n        - non_business_sum\n        - spatial_count\n        - spatial_sum\n        - updated_at\n\n        Ensure the query is well-structured, uses best indexing practices, and avoids performance bottlenecks.\n\n        Consider the following context: \"$mainContent\".\n\n        Always return only the SQL query without any additional explanation."
+    }
+}

resources/js/bigdata-resumes/index.js

@@ -30,29 +30,34 @@ class BigdataResume {
 
     initTableDataSettings() {
         let tableContainer = document.getElementById("table-bigdata-resumes");
-        // Create a new Grid.js instance only if it doesn't exist
         this.table = new Grid({
             columns: [
                 { name: "ID" },
-                { name: "Potention Count" },
+                { name: "Jumlah Potensi" },
-                { name: "Potention Sum" },
+                { name: "Total Potensi" },
-                { name: "Non Verified Count" },
+                { name: "Jumlah Berkas Belum Terverifikasi" },
-                { name: "Non Verified Sum" },
+                { name: "Total Berkas Belum Terverifikasi" },
-                { name: "Verified Count" },
+                { name: "Jumlah Berkas Terverifikasi" },
-                { name: "Verified Sum" },
+                { name: "Total Berkas Terverifikasi" },
-                { name: "Business Count" },
+                { name: "Jumlah Usaha" },
-                { name: "Business Sum" },
+                { name: "Total Usaha" },
-                { name: "Non Business Count" },
+                { name: "Jumlah Non Usaha" },
-                { name: "Non Business Sum" },
+                { name: "Total Non Usaha" },
-                { name: "Spatial Sum" },
+                { name: "Jumlah Tata Ruang" },
-                { name: "Spatial Count" },
+                { name: "Total Tata Ruang" },
+                { name: "Jumlah Menunggu Klik DPMPTSP" },
+                { name: "Total Menunggu Klik DPMPTSP" },
+                { name: "Jumlah Realisasi Terbit PBG" },
+                { name: "Total Realisasi Terbit PBG" },
+                { name: "Jumlah Proses Dinas Teknis" },
+                { name: "Total Proses Dinas Teknis" },
                 {
                     name: "Created",
                     attributes: { style: "width: 200px; white-space: nowrap;" }, // Set width dynamically
                 },
             ],
             pagination: {
-                limit: 15,
+                limit: 50,
                 server: {
                     url: (prev, page) =>
                         `${prev}${prev.includes("?") ? "&" : "?"}page=${
@@ -74,8 +79,8 @@ class BigdataResume {
                         .getAttribute("content")}`,
                     "Content-Type": "application/json",
                 },
-                then: (data) =>
+                then: (data) => {
-                    data.data.map((item) => [
+                    return data.data.map((item) => [
                         item.id,
                         item.potention_count,
                         addThousandSeparators(item.potention_sum),
@@ -89,8 +94,19 @@ class BigdataResume {
                         addThousandSeparators(item.non_business_sum),
                         item.spatial_count,
                         addThousandSeparators(item.spatial_sum),
+                        item.waiting_click_dpmptsp_count,
+                        addThousandSeparators(item.waiting_click_dpmptsp_sum),
+                        item.issuance_realization_pbg_count,
+                        addThousandSeparators(
+                            item.issuance_realization_pbg_sum
+                        ),
+                        item.process_in_technical_office_count,
+                        addThousandSeparators(
+                            item.process_in_technical_office_sum
+                        ),
                         moment(item.created_at).format("YYYY-MM-DD H:mm:ss"),
-                    ]),
+                    ]);
+                },
                 total: (data) => data.total,
             },
         }).render(tableContainer);

resources/js/business-industries/index.js

@@ -64,7 +64,7 @@ class BusinessIndustries {
                 },
             ],
             pagination: {
-                limit: 15,
+                limit: 50,
                 server: {
                     url: (prev, page) =>
                         `${prev}${prev.includes("?") ? "&" : "?"}page=${

resources/js/chatbot/index.js

@@ -0,0 +1,176 @@
+import GlobalConfig from "../global-config.js";
+
+document.addEventListener("DOMContentLoaded", function () {
+    document.querySelectorAll(".nav-link").forEach(tab => {
+        tab.addEventListener("click", function () {
+            setTimeout(() => {
+                const tab_active = getActiveTabId();
+                console.log("Active Tab ID:", tab_active);
+            }, 100); // Timeout untuk memastikan class `active` sudah diperbarui
+        });
+    });
+
+    const textarea = document.getElementById("user-message");
+    const sendButton = document.getElementById("send");
+    const conversationArea = document.querySelector(".row.flex-grow");
+
+    // Fungsi untuk mengirim pesan
+    async function sendMessage() {
+        const userText = textarea.value.trim();
+        if (userText !== "") {
+            // Kosongkan textarea setelah mengirim
+            textarea.value = "";
+
+            // Ambil tab aktif saat ini
+            const currentTab = getActiveTabId();
+
+            // Tambahkan pesan user ke UI
+            addMessage(userText, "user");
+
+            // Tambahkan pesan bot sementara dengan "Loading..."
+            const botMessageElement = addMessage('<div class="loader w-auto"></div>', "bot");
+
+            // Panggil API untuk mendapatkan response dari bot
+            const botResponse = await getBotResponse(currentTab, userText);
+
+            // Perbarui pesan bot dengan respons yang sebenarnya
+            botMessageElement.innerHTML = botResponse;;
+        }
+    }
+
+    // Event listener untuk klik tombol
+    sendButton.addEventListener("click", sendMessage);
+
+    // Event listener untuk menekan Enter di textarea
+    textarea.addEventListener("keydown", function (event) {
+        if (event.key === "Enter" && !event.shiftKey) {
+            event.preventDefault(); // Mencegah newline di textarea
+            sendMessage(); // Panggil fungsi kirim pesan
+        }
+    });
+
+    function addMessage(text, sender) {
+        const messageRow = document.createElement("div");
+        messageRow.classList.add("row", "flex-grow", "overflow-auto");
+    
+        const messageCol = document.createElement("div");
+        messageCol.classList.add("w-auto", "d-inline-block"); // Menyesuaikan lebar konten
+    
+        if (sender === "user") {
+            messageCol.classList.add("ms-auto", "max-w-50"); // Rata kanan, max 50% (setara col-6)
+        } else {
+            messageCol.classList.add("max-w-75"); // Max 75% (setara col-9)
+    
+            // Tambahkan avatar hanya untuk bot
+            const avatarSpan = document.createElement("span");
+            avatarSpan.classList.add("d-flex", "align-items-center", "mb-1");
+    
+            const avatarImg = document.createElement("img");
+            avatarImg.classList.add("rounded-circle");
+            avatarImg.width = 32;
+            avatarImg.src = "/images/iconchatbot.jpeg";
+            avatarImg.alt = "bot-avatar";
+    
+            avatarSpan.appendChild(avatarImg);
+            messageCol.appendChild(avatarSpan);
+        }
+    
+        const messageDiv = document.createElement("div");
+        messageDiv.classList.add("p-2", "rounded", "mb-2");
+    
+        if (sender === "user") {
+            messageDiv.classList.add("user-response", "bg-primary", "text-white");
+        } else {
+            messageDiv.classList.add("bot-response", "bg-light");
+        }
+    
+        // Menyisipkan konten HTML langsung (bisa berupa teks atau loader)
+        messageDiv.innerHTML = text;
+        messageCol.appendChild(messageDiv);
+        messageRow.appendChild(messageCol);
+    
+        // Tambahkan ke area percakapan
+        conversationArea.appendChild(messageRow);
+    
+        // Scroll otomatis ke bawah
+        conversationArea.scrollTop = conversationArea.scrollHeight;
+    
+        return messageDiv; // Mengembalikan elemen agar bisa diperbarui nanti
+    }
+    
+
+    // function addMessage(text, sender) {
+    //     const messageRow = document.createElement("div");
+    //     messageRow.classList.add("row", "flex-grow", "overflow-auto");
+    
+    //     const messageCol = document.createElement("div");
+    //     messageCol.classList.add("col-9", "w-auto");
+    
+    //     if (sender === "user") {
+    //         messageCol.classList.add("ms-auto"); // Geser ke kanan untuk user
+    //     }
+    
+    //     const messageDiv = document.createElement("div");
+    //     messageDiv.classList.add("p-2", "rounded", "mb-2");
+    
+    //     if (sender === "user") {
+    //         messageDiv.classList.add("user-response", "bg-primary", "text-white");
+    //     } else {
+    //         messageDiv.classList.add("bot-response", "bg-light");
+    
+    //         // Tambahkan avatar hanya untuk bot
+    //         const avatarSpan = document.createElement("span");
+    //         avatarSpan.classList.add("d-flex", "align-items-center", "mb-1");
+    
+    //         const avatarImg = document.createElement("img");
+    //         avatarImg.classList.add("rounded-circle");
+    //         avatarImg.width = 32;
+    //         avatarImg.src = "/images/iconchatbot.jpeg";
+    //         avatarImg.alt = "bot-avatar";
+    
+    //         avatarSpan.appendChild(avatarImg);
+    //         messageCol.appendChild(avatarSpan);
+    //     }
+    
+    //     // Menyisipkan konten HTML langsung (bisa berupa teks atau loader)
+    //     messageDiv.innerHTML = text;
+    //     messageCol.appendChild(messageDiv);
+    //     messageRow.appendChild(messageCol);
+    
+    //     // Tambahkan ke area percakapan
+    //     conversationArea.appendChild(messageRow);
+    
+    //     // Scroll otomatis ke bawah
+    //     conversationArea.scrollTop = conversationArea.scrollHeight;
+    
+    //     return messageDiv; // Mengembalikan elemen agar bisa diperbarui nanti
+    // }
+
+    // Fungsi untuk memanggil API
+    async function getBotResponse(tab_active, userText) {
+        try {
+            const url = `${GlobalConfig.apiHost}/api/generate-text`;
+            const response = await fetch(url, {
+                method: "POST",
+                body: JSON.stringify({tab_active:tab_active, prompt: userText }),
+                headers: {
+                    Authorization: `Bearer ${document
+                        .querySelector('meta[name="api-token"]')
+                        .getAttribute("content")}`,
+                    "Content-Type": "application/json",
+                },
+            });
+
+            const data = await response.json();
+            return data.response || "Maaf, saya tidak mengerti.";
+        } catch (error) {
+            console.error("Error fetching bot response:", error);
+            return "Terjadi kesalahan, coba lagi nanti.";
+        }
+    }
+});
+
+function getActiveTabId() {
+    const activeTab = document.querySelector(".nav-link.active");
+    return activeTab ? activeTab.id : null;
+}

resources/js/customers/index.js

@@ -53,7 +53,7 @@ class Customers {
                 },
             ],
             pagination: {
-                limit: 15,
+                limit: 50,
                 server: {
                     url: (prev, page) =>
                         `${prev}${prev.includes("?") ? "&" : "?"}page=${

resources/js/dashboards/lack-of-potential.js

@@ -16,6 +16,19 @@ class LackOfPotential {
         this.pdamCount = this.allCountData.total_pdam ?? 0;
         this.tataRuangCount = this.allCountData.total_tata_ruang ?? 0;
 
+        let dataReportTourism = this.allCountData.data_report;
+
+        this.totalVilla = dataReportTourism
+            .filter((item) => item.kbli_title.toLowerCase() === "vila")
+            .reduce((sum, item) => sum + item.total_records, 0);
+        this.totalRestoran = dataReportTourism
+            .filter((item) => item.kbli_title.toLowerCase() === "restoran")
+            .reduce((sum, item) => sum + item.total_records, 0);
+        this.totalPariwisata = dataReportTourism.reduce(
+            (sum, item) => sum + item.total_records,
+            0
+        );
+
         this.bigTargetPAD = new Big(this.totalTargetPAD ?? 0);
         this.bigTotalPotensi = new Big(this.totalPotensi.total ?? 0);
         this.bigTotalLackPotential = this.bigTargetPAD.minus(
@@ -140,6 +153,15 @@ class LackOfPotential {
         document.getElementById("pdam-count").innerText = this.pdamCount;
         document.getElementById("pbb-bangunan-count").innerText =
             this.tataRuangCount;
+        document.getElementById("tata-ruang-count").innerText =
+            this.tataRuangCount;
+        document.getElementById("tata-ruang-usaha-count").innerText =
+            this.tataRuangCount;
+        document.getElementById("restoran-count").innerText =
+            this.totalRestoran;
+        document.getElementById("villa-count").innerText = this.totalVilla;
+        document.getElementById("pariwisata-count").innerText =
+            this.totalPariwisata;
     }
 }
 document.addEventListener("DOMContentLoaded", async function (e) {

resources/js/master/users/users.js

@@ -31,7 +31,7 @@ class UsersTable {
                 },
             ],
             pagination: {
-                limit: 15,
+                limit: 50,
                 server: {
                     url: (prev, page) =>
                         `${prev}${prev.includes("?") ? "&" : "?"}page=${

resources/js/menus/index.js

@@ -83,7 +83,7 @@ class Menus {
                 },
             ],
             pagination: {
-                limit: 15,
+                limit: 50,
                 server: {
                     url: (prev, page) =>
                         `${prev}${prev.includes("?") ? "&" : "?"}page=${

resources/js/roles/index.js

@@ -52,7 +52,7 @@ class Roles {
                 },
             ],
             pagination: {
-                limit: 15,
+                limit: 50,
                 server: {
                     url: (prev, page) =>
                         `${prev}${prev.includes("?") ? "&" : "?"}page=${

resources/js/settings/syncronize/syncronize.js

@@ -4,13 +4,21 @@ import "gridjs/dist/gridjs.umd.js";
 import GlobalConfig from "../../global-config.js";
 
 class SyncronizeTask {
+    constructor() {
+        this.toastElement = document.getElementById("toastNotification");
+        this.toastMessage = document.getElementById("toast-message");
+        this.toast = new bootstrap.Toast(this.toastElement);
+        this.table = null;
+    }
     init() {
         this.initTableImportDatasources();
         this.handleSubmitSync();
-        this.handleSubmitSnycGoogleSheet();
     }
     initTableImportDatasources() {
-        new Grid({
+        let tableContainer = document.getElementById(
+            "table-import-datasources"
+        );
+        this.table = new gridjs.Grid({
             columns: ["ID", "Message", "Response", "Status", "Created"],
             search: {
                 server: {
@@ -18,7 +26,7 @@ class SyncronizeTask {
                 },
             },
             pagination: {
-                limit: 15,
+                limit: 50,
                 server: {
                     url: (prev, page) =>
                         `${prev}${prev.includes("?") ? "&" : "?"}page=${
@@ -45,20 +53,24 @@ class SyncronizeTask {
                     ]),
                 total: (data) => data.meta.total,
             },
-        }).render(document.getElementById("table-import-datasources"));
+        }).render(tableContainer);
     }
     handleSubmitSync() {
         const button = document.getElementById("btn-sync-submit");
+        const spinner = document.getElementById("spinner");
+        const apiToken = document
+            .querySelector('meta[name="api-token"]')
+            .getAttribute("content");
+
+        // Show the spinner while checking
+        spinner.classList.remove("d-none");
 
-        // Check if the button should be enabled or disabled based on the status
         fetch(
             `${GlobalConfig.apiHost}/api/import-datasource/check-datasource`,
             {
                 method: "GET",
                 headers: {
-                    Authorization: `Bearer ${document
+                    Authorization: `Bearer ${apiToken}`,
-                        .querySelector('meta[name="api-token"]')
-                        .getAttribute("content")}`,
                     "Content-Type": "application/json",
                 },
             }
@@ -70,49 +82,21 @@ class SyncronizeTask {
                 return response.json();
             })
             .then((data) => {
-                console.log("data check button sync", data.can_execute);
                 button.disabled = !data.can_execute;
 
-                // If the button is enabled, add click event to trigger sync
+                if (!data.can_execute) {
-                if (!button.disabled) {
+                    // Keep spinner visible if cannot execute
-                    button.addEventListener("click", function (e) {
+                    spinner.classList.remove("d-none");
-                        button.disabled = true; // Disable button to prevent multiple clicks
+                } else {
-                        button.textContent = "Syncing..."; // Change button text to show syncing
+                    // Hide spinner when execution is allowed
+                    spinner.classList.add("d-none");
 
-                        // Trigger the scraping API call
+                    // Remove previous event listener before adding a new one
-                        fetch(`${GlobalConfig.apiHost}/api/scraping`, {
+                    button.removeEventListener("click", this.handleSyncClick);
-                            method: "GET",
+                    button.addEventListener(
-                            headers: {
+                        "click",
-                                Authorization: `Bearer ${document
+                        this.handleSyncClick.bind(this)
-                                    .querySelector('meta[name="api-token"]')
+                    );
-                                    .getAttribute("content")}`,
-                                "Content-Type": "application/json",
-                            },
-                        })
-                            .then((response) => {
-                                if (!response.ok) {
-                                    throw new Error(
-                                        "Network response was not ok"
-                                    );
-                                }
-                                return response.json();
-                            })
-                            .then((data) => {
-                                console.log("data sync button", data);
-                                alert("Synchronization executed successfully");
-                                window.location.reload();
-                            })
-                            .catch((err) => {
-                                console.error("Fetch error:", err);
-                                alert(
-                                    "An error occurred during synchronization"
-                                );
-                            })
-                            .finally(() => {
-                                button.disabled = false; // Re-enable the button after the request is complete
-                                button.textContent = "Sync Data"; // Reset button text
-                            });
-                    });
                 }
             })
             .catch((err) => {
@@ -120,42 +104,53 @@ class SyncronizeTask {
                 alert("An error occurred while checking the datasource");
             });
     }
-    handleSubmitSnycGoogleSheet() {
-        const button = document.getElementById("btn-sync-submit-google-sheet");
-        button.addEventListener("click", function (e) {
-            button.disabled = true; // Disable button to prevent multiple clicks
-            button.textContent = "Syncing..."; // Change button text to show syncing
 
-            // Trigger the scraping API call
+    handleSyncClick() {
-            fetch(`${GlobalConfig.apiHost}/api/sync-pbg-task-google-sheet`, {
+        const button = document.getElementById("btn-sync-submit");
-                method: "GET",
+        const spinner = document.getElementById("spinner");
-                headers: {
+        const apiToken = document
-                    Authorization: `Bearer ${document
+            .querySelector('meta[name="api-token"]')
-                        .querySelector('meta[name="api-token"]')
+            .getAttribute("content");
-                        .getAttribute("content")}`,
+
-                    "Content-Type": "application/json",
+        button.disabled = true; // Prevent multiple clicks
-                },
+        spinner.classList.remove("d-none"); // Show spinner during sync
+
+        fetch(`${GlobalConfig.apiHost}/api/scraping`, {
+            method: "GET",
+            headers: {
+                Authorization: `Bearer ${apiToken}`,
+                "Content-Type": "application/json",
+            },
+        })
+            .then(async (response) => {
+                if (!response.ok) {
+                    throw new Error(`HTTP error! Status: ${response.status}`);
+                }
+
+                let data;
+                try {
+                    data = await response.json();
+                } catch (jsonError) {
+                    throw new Error("Failed to parse JSON response");
+                }
+
+                return data;
             })
-                .then((response) => {
+            .then((data) => {
-                    if (!response.ok) {
+                this.toastMessage.innerText =
-                        throw new Error("Network response was not ok");
+                    data.data.message || "Synchronize successfully!";
-                    }
+                this.toast.show();
-                    return response.json();
+
-                })
+                // Update the table if it exists
-                .then((data) => {
+                if (this.table) {
-                    console.log("data sync button", data);
+                    this.table.updateConfig({}).forceRender();
-                    alert("Synchronization executed successfully");
+                }
-                    window.location.reload();
+            })
-                })
+            .catch((err) => {
-                .catch((err) => {
+                console.error("Fetch error:", err);
-                    console.error("Fetch error:", err);
+                alert("An error occurred during synchronization" + err.message);
-                    alert("An error occurred during synchronization");
+                button.disabled = false;
-                })
+            });
-                .finally(() => {
-                    button.disabled = false; // Re-enable the button after the request is complete
-                    button.textContent = "Sync Google Sheet"; // Reset button text
-                });
-        });
     }
 }
 document.addEventListener("DOMContentLoaded", function (e) {

resources/views/chatbot/index.blade.php

@@ -0,0 +1,92 @@
+@extends('layouts.vertical', ['subtitle' => 'Chatbot'])
+
+@section('css')
+@vite(['node_modules/gridjs/dist/theme/mermaid.min.css'])
+<style>
+#user-message {
+    height: 60px; /* Menambah tinggi textarea */
+    font-size: 1.1rem; /* Memperbesar font */
+    padding: 10px; /* Menambah ruang di dalam textarea */
+    resize: none; /* Mencegah resize manual */
+}
+
+.loader {
+  width: 10px;
+  aspect-ratio: 1;
+  border-radius: 50%;
+  animation: l5 1s infinite linear alternate;
+}
+
+@keyframes l5 {
+    0%  {box-shadow: 10px 0 #000, -10px 0 #0002; background: #000 }
+    33% {box-shadow: 10px 0 #000, -10px 0 #0002; background: #0002}
+    66% {box-shadow: 10px 0 #0002, -10px 0 #000; background: #0002}
+    100%{box-shadow: 10px 0 #0002, -10px 0 #000; background: #000 }
+}
+</style>
+@endsection
+
+@section('content')
+@include('layouts.partials/page-title', ['title' => 'Chatbot', 'subtitle' => 'Chatbot'])
+
+<div class="card">
+    <div class="card-header">
+        <ul class="nav nav-tabs nav-justified">
+            <li class="nav-item">
+                <button id="count-retribusi" data-bs-toggle="tab" aria-expanded="false" class="nav-link">
+                    <span class="d-block d-sm-none"><i class="bx bx-home"></i></span>
+                    <span class="d-none d-sm-block">Perhitungan Retribusi</span>
+                </button>
+            </li>
+            <li class="nav-item">
+                <button id="document-validation" data-bs-toggle="tab" aria-expanded="true"
+                    class="nav-link active">
+                    <span class="d-block d-sm-none"><i class="bx bx-user"></i></span>
+                    <span class="d-none d-sm-block">Validasi Dokumen PBG</span>
+                </button>
+            </li>
+            <li class="nav-item">
+                <button id="data-information" data-bs-toggle="tab" aria-expanded="false" class="nav-link">
+                    <span class="d-block d-sm-none"><i class="bx bx-envelope"></i></span>
+                    <span class="d-none d-sm-block">Pengumpulan Data PBG</span>
+                </button>
+            </li>
+        </ul>
+    </div>
+    <div class="card-body d-flex flex-column" style="height: 700px;">
+        <!-- Conversation Area -->
+        
+        <!-- Bot Response -->
+        <div class="row flex-grow overflow-auto">
+            <div class="col-9 w-auto">
+                <span class="d-flex align-items-center mb-1">
+                    <img class="rounded-circle" width="32" src="/images/iconchatbot.jpeg" alt="avatar-3">
+               </span>
+                <div class="bot-response p-2 bg-light rounded mb-2">
+                    <p>Halo! Ada yang bisa saya bantu?</p>
+                </div>
+            </div>
+        </div>
+    
+        <!-- Input & Button (Selalu di Bawah) -->
+        <div class="row mt-auto">
+            <div class="col-xl-12 d-flex align-items-end gap-1">
+                <textarea class="form-control" id="user-message"></textarea>
+                <button id="send" class="btn btn-primary btn-lg rounded-pill">
+                    <i class='bx bx-send'></i>
+                </button>
+            </div>
+            {{-- <div class="col-xl-2 d-flex justify-content-end">
+                
+            </div> --}}
+        </div>
+        </div>
+        
+    </div>    
+</div>
+
+@endsection
+
+@section('scripts')
+@vite(['resources/js/chatbot/index.js'])
+@endsection

resources/views/customers/upload.blade.php

@@ -1,8 +1,8 @@
-@extends('layouts.vertical', ['subtitle' => 'Data'])
+@extends('layouts.vertical', ['subtitle' => 'PDAM'])
 
 @section('content')
 
-@include('layouts.partials/page-title', ['title' => 'Data', 'subtitle' => 'Tata Ruang'])
+@include('layouts.partials/page-title', ['title' => 'Data', 'subtitle' => 'PDAM'])
 
 <x-toast-notification />
 <div class="row">

resources/views/dashboards/lack_of_potential.blade.php

@@ -21,7 +21,7 @@
     <div class="wrapper">
         <div id="lack-of-potential-fixed-container" class="" style="width:1400px;height:770px;position:relative;margin:auto;z-index:1;">
             <div style="position: absolute; top: 200px; left: 50px;">
-                <x-custom-circle title="Restoran" size="small" style="background-color: #0e4753;" />
+                <x-custom-circle title="Restoran" size="small" style="background-color: #0e4753;" visible_data="true" data_id="restoran-count" data_count="0" />
                 <div class="square dia-top-left-bottom-right" style="top:30px;left:50px;width:150px;height:120px;"></div>
                 <x-custom-circle title="PBB Bangunan" visible_data="true" data_id="pbb-bangunan-count" data_count="0" size="small" style="background-color: #0e4753;" />
                 <div class="square" style="width:150px;height:2px;background-color:black;left:50px;top:150px;"></div>
@@ -52,11 +52,11 @@
                 <div class="square dia-top-right-bottom-left" style="top:-110px;left:90px;width:150px;height:170px;"></div>
                 <div class="square dia-top-left-bottom-right" style="top:-110px;left:230px;width:150px;height:170px;"></div>
                 <div class="square dia-top-left-bottom-right" style="top:-110px;left:260px;width:200px;height:180px;"></div>
-                <x-custom-circle title="Villa" size="small" style="float:left;background-color: #234f6c;" />
+                <x-custom-circle title="Villa" size="small" style="float:left;background-color: #234f6c;" visible_data="true" data_id="villa-count" data_count="0" />
                 <x-custom-circle title="Pabrik" size="small" style="float:left;background-color: #234f6c;" />
                 <x-custom-circle title="Jalan Protocol" size="small" style="float:left;background-color: #234f6c;" />
                 <x-custom-circle title="Ruko" size="small" style="float:left;background-color: #234f6c;" />
-                <x-custom-circle title="Pariwisata" size="small" style="float:left;background-color: #234f6c; margin-right: 20px;" />
+                <x-custom-circle title="Pariwisata" size="small" style="float:left;background-color: #234f6c; margin-right: 20px;" visible_data="true" data_id="pariwisata-count" data_count="0" />
                 <div class="square" style="width:150px;height:2px;background-color:black;left:350px;top:50px;"></div>
                 <x-custom-circle title="DISBUDPAR" size="small" style="background-color: #3a968b;" />
             </div>
@@ -74,23 +74,20 @@
                     'visible_small_circle' => false,
                     'style' => 'margin-left:180px;top:-20px;'
                     ])
-                    @endcomponent
+                @endcomponent
-                    
+                <x-custom-circle title="Tata Ruang" size="large" style="background-color: #da6635;float:left;margin-left:250px;" visible_data="true" data_id="tata-ruang-count" data_count="0" />
-                    <x-custom-circle title="Tata Ruang" size="large" style="background-color: #da6635;float:left;margin-left:250px;" />
+            </div>
-                    
-                </div>
                 
-                <div style="position: absolute; top: 310px; left: 1150px;">
+            <div style="position: absolute; top: 310px; left: 1150px;">
-                    <div class="square dia-top-left-bottom-right" style="top:90px;left:-100px;width:100px;height:100px;"></div>
+                <div class="square dia-top-left-bottom-right" style="top:90px;left:-100px;width:100px;height:100px;"></div>
-                    <div class="square dia-top-right-bottom-left" style="top:-110px;left:-100px;width:100px;height:100px;"></div>
+                <div class="square dia-top-right-bottom-left" style="top:-110px;left:-100px;width:100px;height:100px;"></div>
-                    <x-custom-circle title="Peta" visible_data_type="true" data_type="1:5000" size="small" style="background-color: #224f6d;float:left;" />
+                <x-custom-circle title="Peta" visible_data_type="true" data_type="1:5000" size="small" style="background-color: #224f6d;float:left;" />
-                    <x-custom-circle title="Tapak Bangunan" size="small" style="background-color: #2390af;float:left;margin-left:20px;" />
+                <x-custom-circle title="Tapak Bangunan" size="small" style="background-color: #2390af;float:left;margin-left:20px;" />
-                    
+            </div>
-                </div>
                 
-                <x-custom-circle title="BPN" size="small" style="background-color: #2390af;position:absolute;left:1270px;top:440px;" />
+            <x-custom-circle title="BPN" size="small" style="background-color: #2390af;position:absolute;left:1270px;top:440px;" />
                 
-                <div style="position: absolute; top: 470px; left: 430px;">
+            <div style="position: absolute; top: 470px; left: 430px;">
                 <div class="square dia-top-right-bottom-left" style="top:-80px;left:20px;width:150px;height:120px;"></div>
                 <div class="square dia-top-right-bottom-left" style="top:-50px;left:100px;width:100px;height:100px;"></div>
                 <div class="square dia-top-left-bottom-right" style="top:-50px;left:180px;width:100px;height:100px;"></div>
@@ -105,7 +102,7 @@
 
             <div  style="position: absolute; top: 50px; left: 1100px;">
                 <x-custom-circle title="Non Usaha" size="large" style="background-color: #3a968b;margin-top:20px;" />
-                <x-custom-circle title="USAHA" size="large" style="background-color: #627c8b;margin-top:260px;" />
+                <x-custom-circle title="USAHA" size="large" style="background-color: #627c8b;margin-top:260px;" visible_data="true" data_id="tata-ruang-usaha-count" data_count="0" />
             </div>
         </div>
     </div>

resources/views/layouts/partials/topbar.blade.php

@@ -22,14 +22,20 @@
 
                <div class="d-flex align-items-center gap-2">
                     <!-- Theme Color (Light/Dark) -->
-                    <!-- <div class="topbar-item">
+                    {{-- <div class="topbar-item">
                          <button type="button" class="topbar-button" id="light-dark-mode">
                               <iconify-icon icon="solar:moon-outline"
                                    class="fs-22 align-middle light-mode"></iconify-icon>
                               <iconify-icon icon="solar:sun-2-outline"
                                    class="fs-22 align-middle dark-mode"></iconify-icon>
                          </button>
-                    </div> -->
+                    </div> --}}
+
+                    <div class="topbar-item">
+                         <a href="{{ route('chatbot.index') }}" class="topbar-button">
+                             <iconify-icon icon="solar:chat-square-outline" class="fs-22 align-middle"></iconify-icon>
+                         </a>
+                     </div>
 
                     <!-- Notification -->
                     <div class="dropdown topbar-item">

resources/views/layouts/vertical.blade.php

@@ -1,6 +1,61 @@
 <!DOCTYPE html>
 <html lang="en" @yield('html-attribute')>
 
+<style>
+    /* .floating-icon {
+        position: fixed;
+        right: 40px;
+        bottom: 100px;
+        width: 70px;  
+        height: 70px; 
+        background: white;
+        border-radius: 50%;
+        box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
+        cursor: pointer;
+        z-index: 1000;
+        background-image: url('/images/iconchatbot.jpeg');
+        background-size: cover;  
+        background-position: center;
+    } */
+
+
+    .floating-icon {
+        position: fixed;
+        right: 20px;
+        bottom: 20px; 
+        width: 60px;  
+        height: 60px;
+        background: white;
+        border-radius: 50%;
+        box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2); 
+        cursor: pointer;
+        z-index: 1000;
+        background-image: url('/images/iconchatbot.jpeg'); 
+        background-size: contain; 
+        background-repeat: no-repeat;
+        background-position: center;
+        transition: transform 0.3s ease-in-out, box-shadow 0.3s ease-in-out;
+    }
+
+    .floating-icon:hover {
+        transform: scale(1.1);
+        box-shadow: 0 6px 12px rgba(0, 0, 0, 0.3);
+    }
+
+    @keyframes bounce {
+        0%, 100% {
+            transform: translateY(0);
+        }
+        50% {
+            transform: translateY(-10px);
+        }
+    }
+
+    .floating-icon.animate {
+        animation: bounce 1s infinite;
+    }
+</style>
+
 <head>
     @include('layouts.partials/title-meta')
 
@@ -17,10 +72,25 @@
 
         <div class="page-content">
 
-            <div class="container-fluid">
+            {{-- <div class="container-fluid">
 
                 @yield('content')
+                <div>
+                    <iconify-icon icon="solar:chat-square-outline" class="fs-35 align-middle"></iconify-icon>
+                </div>
 
+            </div> --}}
+            <div class="container-fluid">
+                @yield('content')
+            
+                {{-- <div class="floating-icon">
+                    
+                </div>             --}}
+                @if (!Request::is('chatbot'))
+                    <a href="{{ route('chatbot.index') }}" class="floating-icon">
+                        
+                    </a>
+                @endif
             </div>
 
             @include('layouts.partials/footer')

resources/views/settings/syncronize/index.blade.php

@@ -7,14 +7,16 @@
 @section('content')
 
 @include('layouts.partials/page-title', ['title' => 'Settings', 'subtitle' => 'Syncronize'])
-
+<x-toast-notification />
 <div class="row">
     <div class="col-12">
         <div class="card w-100">
             <div class="card-body">
                 <div class="d-flex flex-wrap justify-content-end gap-2">
-                    <button type="button" class="btn btn-success btn-sm d-block d-sm-inline w-auto" id="btn-sync-submit-google-sheet">Sync Google Sheet</button>
+                    <button type="button" class="btn btn-success btn-sm d-block d-sm-inline w-auto" id="btn-sync-submit">
-                    <button type="button" class="btn btn-success btn-sm d-block d-sm-inline w-auto" id="btn-sync-submit">Sync SIMBG</button>
+                        <span id="spinner" class="spinner-border spinner-border-sm me-1 d-none" role="status" aria-hidden="true"></span>    
+                        Sync SIMBG
+                    </button>
                 </div>
                 <div>
                     <div id="table-import-datasources"></div>

routes/api.php

@@ -21,9 +21,11 @@ use App\Http\Controllers\Api\AdvertisementController;
 use App\Http\Controllers\Api\UmkmController;
 use App\Http\Controllers\Api\TourismController;
 use App\Http\Controllers\Api\SpatialPlanningController;
+use App\Http\Controllers\Api\ChatbotController;
 use Illuminate\Support\Facades\Route;
 
 Route::post('/login', [UsersController::class, 'login'])->name('api.user.login');
+Route::post('/generate-text', [ChatbotController::class, 'generateText']);
 Route::group(['middleware' => 'auth:sanctum'], function (){
     // users
     Route::controller(UsersController::class)->group(function(){

routes/web.php

@@ -20,6 +20,7 @@ use App\Http\Controllers\Data\TourismController;
 use App\Http\Controllers\Data\SpatialPlanningController;
 use App\Http\Controllers\Report\ReportTourismController;
 use App\Http\Controllers\SpatialPlanningsController;
+use App\Http\Controllers\Chatbot\ChatbotController;
 use Illuminate\Support\Facades\Route;
 
 require __DIR__ . '/auth.php';
@@ -59,6 +60,9 @@ Route::group(['middleware' => 'auth'], function(){
     // menus
     Route::resource('/menus', MenusController::class);
 
+    // chatbot
+    Route::resource('/chatbot', ChatbotController::class);
+
     // roles
     Route::resource('/roles', RolesController::class);
     Route::group(['prefix' => '/roles'], function (){

vite.config.js

@@ -97,6 +97,9 @@ export default defineConfig({
                 "resources/js/dashboards/pbg.js",
                 // maps
                 "resources/js/maps/maps-kml.js",
+                // laporan pimpinan
+                "resources/js/bigdata-resumes/index.js",
+                "resources/js/chatbot/index.js",
             ],
             refresh: true,
         }),