wisnubaldas · May 29, 2022 11:18 · May 29, 2022 · May 29, 2022
diff --git a/WbiBaruController.php b/WbiBaruController.php
@@ -44,4 +44,16 @@ public function silo(Request $request)
             "data" => $data
         ], 200);
     }
+    public function checklist_mapp(Request $request)
+    {
+        $sec_id = implode(',',$request->sec_id);
+        $data = DB::select('CALL checklist_mapp(?,?)', array($request->p_dept,$sec_id));
+        return response()->json([
+            "response" => [
+                'success'       => true,
+                'message'       => 'cibay',
+            ],
+            "data" => $data
+        ], 200);
+    }
 }
diff --git a/checklist_mapp.sql b/checklist_mapp.sql
@@ -0,0 +1,19 @@
+DELIMITER //
+DROP PROCEDURE IF EXISTS checklist_mapp;
+CREATE PROCEDURE checklist_mapp (
+	IN `p_dept` VARCHAR(50),
+	IN `sec_id` VARCHAR(50)
+)
+ BEGIN
+	SELECT DISTINCT A.`plant_dept`,D.`plant_name`,A.section_id, C.`section_name`,A.eq_erp_id, B.eq_desc FROM checklist_mapping A
+	LEFT JOIN master_plant D ON A.`plant_dept`=D.`dept`
+	LEFT JOIN master_section C ON A.section_id=C.section_id
+	LEFT JOIN master_equipment B ON A.eq_erp_id=B.`eq_erp_id`
+	WHERE A.plant_dept = p_dept
+	AND FIND_IN_SET(A.section_id, sec_id)
+-- 	// AND A.section_id IN (declared_in_param)
+	ORDER BY eq_erp_id;
+ END;
+//
+
+DELIMITER ;
diff --git a/checklist_param.json b/checklist_param.json
@@ -0,0 +1,10 @@
+{
+    "p_dept":"IDCGBFM",
+    "sec_id":[
+        "PAT-SEC25",
+        "PAT-SEC26",
+        "PAT-SEC27",
+        "PAT-SEC28",
+        "PAT-SEC29"
+    ]
+}
diff --git a/route.php b/route.php
@@ -1,4 +1,5 @@
 <?php
 Route::get('schedules1/{plant_dept}/{date_dept}',[WbiBaruController::class,'schedule1']);
 Route::get('silo',[WbiBaruController::class,'silo']);
-Route::get('update-schedule',[WbiBaruController::class,'update_schedule']);
+Route::get('update-schedule',[WbiBaruController::class,'update_schedule']);
+Route::get('checklist-mapp',[WbiBaruController::class,'checklist_mapp']);
diff --git a/schedul_1.sql b/schedul_1.sql
@@ -1,5 +1,5 @@
 DELIMITER //
-
+DROP PROCEDURE IF EXISTS schedul_1;
 CREATE PROCEDURE schedul_1 (
 	IN `planDept` VARCHAR(50),
 	IN `datePlan` VARCHAR(50)

diff --git a/silo.sql b/silo.sql
@@ -1,5 +1,5 @@
 DELIMITER //
-
+DROP PROCEDURE IF EXISTS silo_1;
 CREATE PROCEDURE silo_1 (
 	IN `sec_name` VARCHAR(50),
 	IN `sch_date` VARCHAR(50)

diff --git a/WbiBaruController.php b/WbiBaruController.php
@@ -0,0 +1,47 @@
+<?php
+
+namespace App\Http\Controllers\Api;
+
+use App\Http\Controllers\Controller;
+use Illuminate\Http\Request;
+use Illuminate\Support\Facades\DB;
+
+class WbiBaruController extends Controller
+{
+    public function schedule1($plant_dept,$date_dept)
+    {
+        $data = DB::select('CALL schedul_1(?,?)',array($plant_dept,$date_dept));
+        return response()->json([
+            "response" => [
+                'success'       => true,
+                'message'       => 'cibay',
+            ],
+            "data" => $data
+        ], 200);
+    }
+    public function update_schedule(Request $request)
+    {
+        $affected = DB::table('schedules')
+              ->where('plant_dept', 'IDCGCB')
+              ->where('schedules_date', '2022-02-25')
+              ->update(['patrol_status' => 1]);
+        return response()->json([
+                "response" => [
+                    'success'       => true,
+                    'message'       => 'cibay',
+                ],
+                "data" => $affected
+            ], 200);
+    }
+    public function silo(Request $request)
+    {
+        $data = DB::select('CALL silo_1(?,?)',array($request->sec_name,$request->sch_date));
+        return response()->json([
+            "response" => [
+                'success'       => true,
+                'message'       => 'cibay',
+            ],
+            "data" => $data
+        ], 200);
+    }
+}
diff --git a/route.php b/route.php
@@ -0,0 +1,4 @@
+<?php
+Route::get('schedules1/{plant_dept}/{date_dept}',[WbiBaruController::class,'schedule1']);
+Route::get('silo',[WbiBaruController::class,'silo']);
+Route::get('update-schedule',[WbiBaruController::class,'update_schedule']);
diff --git a/schedul_1.sql b/schedul_1.sql
@@ -0,0 +1,18 @@
+DELIMITER //
+
+CREATE PROCEDURE schedul_1 (
+	IN `planDept` VARCHAR(50),
+	IN `datePlan` VARCHAR(50)
+)
+ BEGIN
+  SELECT DISTINCT a.schedule_date, b.section_id, c.section_name, a.shift_id, a.patrol_status, a.`end_shift_status`
+	FROM schedules a
+	LEFT JOIN (SELECT DISTINCT section_id, eq_erp_id, plant_dept FROM checklist_mapping) b ON b.eq_erp_id = a.eq_erp_id AND b.plant_dept = a.plant_dept
+	LEFT JOIN master_section c ON c.section_id = b.section_id
+	WHERE a.plant_dept = 'IDCGMJ' 
+	AND a.schedule_date = '2022-04-26'
+	ORDER BY schedule_date, section_id, shift_id;
+ END;
+//
+
+DELIMITER ;
diff --git a/silo.sql b/silo.sql
@@ -0,0 +1,18 @@
+DELIMITER //
+
+CREATE PROCEDURE silo_1 (
+	IN `sec_name` VARCHAR(50),
+	IN `sch_date` VARCHAR(50)
+)
+ BEGIN
+  SELECT DISTINCT a.`schedule_id`, a.schedule_date, b.section_id, c.section_name, a.shift_id, a.patrol_status, a.`end_shift_status`
+	FROM schedules a
+	LEFT JOIN (SELECT DISTINCT section_id, eq_erp_id, plant_dept FROM checklist_mapping) b ON b.eq_erp_id = a.eq_erp_id AND b.plant_dept = a.plant_dept
+	LEFT JOIN master_section c ON c.section_id = b.section_id
+	WHERE c.section_name = sec_name
+	AND a.schedule_date = sch_date
+	ORDER BY schedule_date, section_id, shift_id;
+ END;
+//
+
+DELIMITER ;
No results found