Schedule_SILO

checklist_mapp.sql

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 ;

checklist_param.json

{
    "p_dept":"IDCGBFM",
    "sec_id":[
        "PAT-SEC25",
        "PAT-SEC26",
        "PAT-SEC27",
        "PAT-SEC28",
        "PAT-SEC29"
    ]
}

route.php

<?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('checklist-mapp',[WbiBaruController::class,'checklist_mapp']);

schedul_1.sql

DELIMITER //
DROP PROCEDURE IF EXISTS schedul_1;
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 ;

silo.sql

DELIMITER //
DROP PROCEDURE IF EXISTS silo_1;
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 ;

WbiBaruController.php

<?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);
    }
    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);
    }
}