<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Models\TblUserAuthenticate;
use App\Mail\AcceptanceQuotation;
use App\Mail\ProjectStarted;
use App\Mail\AssigningProjectTask;
use App\Mail\ApproveDeclineInvoice;
use App\Mail\PaymentReceivable;
use App\Mail\ProjectHandover;
use App\Mail\DrawingForPreliminariesWorks;
use App\Mail\UploadedInvoice;
use App\Mail\VariationOrder;
use App\Jobs\SendAcceptanceQuotationEmail;
use App\Jobs\SendProjectStartedEmail;
use App\Jobs\SendApproveDeclineInvoiceEmail;
use App\Jobs\SendPaymentReceivableEmail;
use App\Jobs\SendProjectHandoverEmail;
use App\Jobs\SendVariationOrderApprovedEmail;
use App\Jobs\SendDrawingEmail;
use App\Jobs\SendUploadInvoiceEmail;
use Auth;
use Mail;
use DB;

class TransactionController extends Controller
{
    public $toRecipientsOfAssigningProjectTask;
   //api-get-view-customer-list
    //view customer list
    function GetViewCustomerList(Request $request)
   {
        //CALL USP_GET_CUSTOMER_LIST_ACTIVITY('VIEW_CUSTOMER_LIST','VIEW_ALL',@ERRNO,@ERRMSG); 
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_CUSTOMER_LIST',
                   'P_ITEM' => $request -> ITEM,
             
        ];
        $response = DB::select("call USP_GET_CUSTOMER_LIST_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }
     //api-get-view-lead-status-history
    //view lead status history
    function GetViewLeadStatusHistory(Request $request)
   {
        //CALL USP_GET_LEAD_STATUS_HISTORY('VIEW_LEAD_STATUS_HISTORY','VIEW_ALL',1,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_LEAD_STATUS_HISTORY',
                   'P_ITEM' => $request -> ITEM,
                   'P_LEAD_SYS_ID' => $request -> LEAD_SYS_ID,
             
        ];
        $response = DB::select("call USP_GET_LEAD_STATUS_HISTORY(:P_OPERATION_NAME, :P_ITEM,:P_LEAD_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

    //api-get-view-acknowledgement-of-handover
    //view acknowledgement of handover
    function GetViewAcknowledgementOfHandover(Request $request)
   {
        //CALL USP_GET_VIEW_ACKNOWLEDGEMENT_OF_HANDOVER_ACTIVITY('VIEW_ACKNOWLEDGEMENT_OF_HANDOVER','VIEW_ALL',1,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_ACKNOWLEDGEMENT_OF_HANDOVER',
                   'P_ITEM' => $request -> ITEM,
                   'P_QUOTATION_SYS_ID' => $request -> QUOTATION_SYS_ID,
             
        ];
        $response = DB::select("call USP_GET_VIEW_ACKNOWLEDGEMENT_OF_HANDOVER_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_QUOTATION_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }
  //api-post-add-update-ackment-handover
  //post add update ackment handover
  function PostAddUpdateAckmentHandover(Request $request) 
  {
      $requestPostJson= json_decode(json_encode($request ->getContent()));
      //CALL USP_POST_ACKMENT_OF_HANDOVER_ACTIVITY('ADD_UPDATE_ACKMENT_OF_HANDOVER_ACTIVITY', '{"ITEM":"ADD","QUATATION_SYS_ID":"0","OWNER_NAME_1":"test1","NRIC_NUMBER_1":"1001","OWNER_NAME_2":"TEST1","NRIC_NUMBER_2":"10001","ON_DATE":"13","DAY_OF":"JAN","YEAR":"2023","SIGNED_NAME":"TEST1","SIGNED_NRIC_NUMBER":"1001" ,"ON_BEHALF_OF_COMPANY_NAME":"TEST1" ,"ON_BEHALF_OF_COMPANY_SIGNATURE":"TEST1" ,"CUSTOMER_SIGNATURE":"TEST1","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
      $payload =[
          'P_OPERATION_NAME' => 'ADD_UPDATE_ACKMENT_OF_HANDOVER_ACTIVITY',
          'P_JSON'=>$requestPostJson,
      ];
      $response = DB::select("call USP_POST_ACKMENT_OF_HANDOVER_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
      if(!empty($response))
      {
          $response_decoded = json_decode($response[0]->JSON_VALUE, true);
          return $response_decoded;
      }
      else
      {
          $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
          return $response_data;
      }
  }
  //api-get-view-renovation-acknowledgement
    //view renovation acknowledgement
    function GetViewRenovationAcknowledgement(Request $request)
   {
        //CALL USP_GET_VIEW_RENOVATION_ACKNOWLEDGEMENT_ACTIVITY('VIEW_RENOVATION_ACKNOWLEDGEMENT','VIEW_ALL',1,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_RENOVATION_ACKNOWLEDGEMENT',
                   'P_ITEM' => $request -> ITEM,
                   'P_QUOTATION_SYS_ID' => $request -> QUOTATION_SYS_ID,
             
        ];
        $response = DB::select("call USP_GET_VIEW_RENOVATION_ACKNOWLEDGEMENT_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_QUOTATION_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-post-add-update-renovation-ackment
  //add update renovation ackment
  function PostAddUpdateRenovationAckment(Request $request) 
  {
      $requestPostJson= json_decode(json_encode($request ->getContent()));
      //CALL USP_POST_RENOVATION_ACKMENT_ACTIVITY('ADD_UPDATE_RENOVATION_ACKMENT_ACTIVITY', '{"ITEM":"ADD","QUATATION_SYS_ID":"0","NAME_1":"test1","NRIC_NUMBER_1":"1001","NRIC_NUMBER_2":"1001","NRIC_NUMBER_3":"1001","NRIC_NUMBER_4":"1001","OWNER_OF":"TEST1","DATE_1":"2000-09-05","NAME_AND_SIGNATURE_OF_FLAT_OWNER_1":"TEST1","NAME_AND_SIGNATURE_OF_FLAT_OWNER_2":"TEST1" ,"NAME_AND_SIGNATURE_OF_FLAT_OWNER_3":"TEST1" ,"NAME_AND_SIGNATURE_OF_FLAT_OWNER_4":"TEST1" ,"WITNESSED_BY":"TEST1","NAME_2":"TEST1","NRIC_NUMBER":"987456","ADDRESS":"TEST1","DATE_2":"2000-05-20","CUSTOMER_SIGNATURE":"TEST1","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
      $payload =[
          'P_OPERATION_NAME' => 'ADD_UPDATE_RENOVATION_ACKMENT_ACTIVITY',
          'P_JSON'=>$requestPostJson,
      ];
      $response = DB::select("call USP_POST_RENOVATION_ACKMENT_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
      if(!empty($response))
      {
          $response_decoded = json_decode($response[0]->JSON_VALUE, true);
          return $response_decoded;
      }
      else
      {
          $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
          return $response_data;
      }
  }

  //api-post-add-update-hdb-license-application
  //add update hdb license application
  function PostAddUpdateHdbLicenseApplication(Request $request) 
  {
      $requestPostJson= json_decode(json_encode($request ->getContent()));
      //CALL USP_POST_HDB_LICENSE_APPLICATION_ACTIVITY('ADD_UPDATE_HDB_LICENSE_APPLICATION_ACTIVITY', '{"ITEM":"ADD","QUATATION_SYS_ID":"0","NAME_1":"test1","NRIC_NUMBER_1":"1001","NRIC_NUMBER_2":"1001","NRIC_NUMBER_3":"1001","NRIC_NUMBER_4":"1001","OWNER_OF":"TEST1","DATE_1":"2000-09-05","NAME_AND_SIGNATURE_OF_FLAT_OWNER_1":"TEST1","NAME_AND_SIGNATURE_OF_FLAT_OWNER_2":"TEST1" ,"NAME_AND_SIGNATURE_OF_FLAT_OWNER_3":"TEST1" ,"NAME_AND_SIGNATURE_OF_FLAT_OWNER_4":"TEST1" ,"WITNESSED_BY":"TEST1","NAME_2":"TEST1","NRIC_NUMBER":"987456","ADDRESS":"TEST1","DATE_2":"2000-05-20","CUSTOMER_SIGNATURE":"TEST1","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
      
      $payload =[
          'P_OPERATION_NAME' => 'ADD_UPDATE_HDB_LICENSE_APPLICATION_ACTIVITY',
          'P_JSON'=>$requestPostJson,
      ];
      $response = DB::select("call USP_POST_HDB_LICENSE_APPLICATION_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
      if(!empty($response))
      {
          $response_decoded = json_decode($response[0]->JSON_VALUE, true);
          return $response_decoded;
      }
      else
      {
          $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
          return $response_data;
      }
  }

  //api-get-view-hdb-license-application
  //view hdb license application
  function GetViewHdbLicenseApplication(Request $request)
   {
        //CALL USP_GET_VIEW_HDB_LICENSE_APPLICATION_ACTIVITY('VIEW_HDB_LICENSE_APPLICATION','VIEW_ALL',1,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_HDB_LICENSE_APPLICATION',
                   'P_ITEM' => $request -> ITEM,
                   'P_PROJECT_MANAGEMENT_SYS_ID' => $request -> PROJECT_MANAGEMENT_SYS_ID,
             
        ];
        $response = DB::select("call USP_GET_VIEW_HDB_LICENSE_APPLICATION_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_PROJECT_MANAGEMENT_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-update-sequence-generator
   //update sequence generator
   function PostAddUpdateSequenceGenerator(Request $request) 
  {
      $requestPostJson= json_decode(json_encode($request ->getContent()));
      //CALL USP_POST_SEQUENCE_GENERATOR('UPDATE_SEQUENCE_GENERATOR','{"ITEM":"UPDATE","name":"Lead_sequence"}',@ERRNO,@ERRMSG);
      
      $payload =[
          'P_OPERATION_NAME' => 'UPDATE_SEQUENCE_GENERATOR',
          'P_JSON'=>$requestPostJson,
      ];
      $response = DB::select("call USP_POST_SEQUENCE_GENERATOR(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
      if(!empty($response))
      {
          $response_decoded = json_decode($response[0]->JSON_VALUE, true);
          return $response_decoded;
      }
      else
      {
          $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
          return $response_data;
      }
  }

  //api-get-view-sequence-generator
  //view sequence generator
  function GetViewSequenceGenerator(Request $request)
   {
        //CALL USP_GET_SEQUENCE_GENERATOR('VIEW_SEQUENCE_GENERATOR','VIEW_ALL',@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_SEQUENCE_GENERATOR',
                   'P_ITEM' => $request -> ITEM,
             
        ];
        $response = DB::select("call USP_GET_SEQUENCE_GENERATOR(:P_OPERATION_NAME, :P_ITEM, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

    //api-get-view-list-quotation-info
    //view list quotation info
    function GetViewListQuotationInfo(Request $request)
    {
        //CALL USP_GET_LIST_QUOTATION_ACTIVITY('VIEW_QUOTATION_INFO','VIEW_ALL',@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_QUOTATION_INFO',
                   'P_ITEM' => $request -> ITEM,
                   'P_USER_SYS_ID' => $request -> USER_SYS_ID,
                   'P_ORGANISATION_SYS_ID' => $request -> ORGANISATION_SYS_ID,                    
        ];
        $response = DB::select("call USP_GET_LIST_QUOTATION_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_USER_SYS_ID,:P_ORGANISATION_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

   //api-get-view-renovation-indemnity
   //view renovation indemnity
   function GetViewRenovationIndemnity(Request $request)
   {
        //CALL USP_GET_VIEW_RENOVATION_INDEMNITY_ACTIVITY('VIEW_RENOVATION_INDEMNITY','VIEW_ALL',1,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_RENOVATION_INDEMNITY',
                   'P_ITEM' => $request -> ITEM,
                   'P_QUOTATION_SYS_ID' => $request -> QUOTATION_SYS_ID,
             
        ];
        $response = DB::select("call USP_GET_VIEW_RENOVATION_INDEMNITY_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_QUOTATION_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-post-add-update-renovation-indemnity
   //add update renovation indemnity
   function PostAddUpdateRenovationIndemnity(Request $request) 
  {
      $requestPostJson= json_decode(json_encode($request ->getContent()));
      //CALL USP_POST_RENOVATION_INDEMNITY_ACTIVITY('ADD_UPDATE_RENOVATION_INDEMNITY', '{"ITEM":"ADD","QUOTATION_SYS_ID":"0","RENOVATION_ADDRESS":"test1","NAME":"test1","NRIC_NUMBER_1":"1001","NRIC_NUMBER_2":"1001","RENOVATION_WORKS_1":"test1","RENOVATION_WORKS_2":"test1","RENOVATION_WORKS_3":"test1","RENOVATION_WORKS_4":"test1","DAY":"21","MONTH":"Jan","YEAR":"2023","NAME_OR_SIGNATURE_OF_PROPOSED_BUYER":"TEST1","NAME_OR_SIGNATURE_OF_REGISTERED_HOLDER_OF_HDB_RRC":"test1" ,"COMPANY_NAME_OF_HDB_RRC":"TEST1" ,"NAME_OR_SIGNATURE_OF_EXISTING_OWNER":"TEST1" ,"CREATED_BY":"1"}',@ERRNO,@ERRMSG);
      
      $payload =[
          'P_OPERATION_NAME' => 'ADD_UPDATE_RENOVATION_INDEMNITY',
          'P_JSON'=>$requestPostJson,
      ];
      $response = DB::select("call USP_POST_RENOVATION_INDEMNITY_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
      if(!empty($response))
      {
          $response_decoded = json_decode($response[0]->JSON_VALUE, true);
          return $response_decoded;
      }
      else
      {
          $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
          return $response_data;
      }
  }

  //api-post-add-update-consent-form
   //add update consent form
   function PostAddUpdateConsentForm(Request $request) 
  {
      $requestPostJson= json_decode(json_encode($request ->getContent()));
      //CALL USP_POST_CONSENT_FORM_ACTIVITY('ADD_UPDATE_CONSENT_FORM', '{"ITEM":"ADD","QUOTATION_SYS_ID":"0","OWNER_NAME":"test1","OWNER_NRIC_NUMBER":"1001","OWNER_ADDRESS":"SINGAPORE","OWNER_SIGNATURE":"NEW","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
      
      $payload =[
          'P_OPERATION_NAME' => 'ADD_UPDATE_CONSENT_FORM',
          'P_JSON'=>$requestPostJson,
      ];
      $response = DB::select("call USP_POST_CONSENT_FORM_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
      if(!empty($response))
      {
          $response_decoded = json_decode($response[0]->JSON_VALUE, true);
          return $response_decoded;
      }
      else
      {
          $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
          return $response_data;
      }
  }

   //api-get-view-consent-form
   //view consent form
   function GetViewConsentForm(Request $request)
   {
        //CALL USP_GET_VIEW_CONSENT_FORM_ACTIVITY('VIEW_CONSENT_FORM','VIEW_ALL',1,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_CONSENT_FORM',
                   'P_ITEM' => $request -> ITEM,
                   'P_QUOTATION_SYS_ID' => $request -> QUOTATION_SYS_ID,
             
        ];
        $response = DB::select("call USP_GET_VIEW_CONSENT_FORM_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_QUOTATION_SYS_ID, @ERRNO,@ERRMSG)", $payload);

        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-post-create-lead-to-quotation
   //post create lead to quotation
   function PostCreateLeadToQuotation(Request $request) 
  {
      $requestPostJson= json_decode(json_encode($request ->getContent()));
      //USP_POST_LEAD_TO_QUOTATION_ACTIVITY('CREATE_LEAD_TO_QUOTATION', '{"ITEM":"ADD","LEAD_SYS_ID":"1","CREATED_BY":"1"}',@ERRNO,@ERRMSG);SELECT @ERRMSG;
      
      $payload =[
          'P_OPERATION_NAME' => 'CREATE_LEAD_TO_QUOTATION',
          'P_JSON'=>$requestPostJson,
      ];
      $response = DB::select("call USP_POST_LEAD_TO_QUOTATION_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
      if(!empty($response))
      {
          $response_decoded = json_decode($response[0]->JSON_VALUE, true);
          return $response_decoded;
      }
      else
      {
          $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
          return $response_data;
      }
  }

  //api-post-update-delivery-order-status
   //post update delivery order status
   function PostUpdateDeliveryOrderStatus(Request $request) 
  {
      $requestPostJson= json_decode(json_encode($request ->getContent()));
      //CALL USP_POST_DELIVERY_ORDER_STATUS_ACTIVITY('DELIVERY_ORDER_STATUS_UPDATE', '[{"ITEM":"STATUS","DELIVERY_ORDER_ID":1,"STATUS":"Approved","APPROVAL_REMARKS":"Test","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
      
      $payload =[
          'P_OPERATION_NAME' => 'DELIVERY_ORDER_STATUS_UPDATE',
          'P_JSON'=>$requestPostJson,
      ];
      $response = DB::select("call USP_POST_DELIVERY_ORDER_STATUS_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
      if(!empty($response))
      {
          $response_decoded = json_decode($response[0]->JSON_VALUE, true);
          return $response_decoded;
      }
      else
      {
          $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
          return $response_data;
      }
  }
//api-post-add-update-delivery-info
   //post add update delivery info
   function PostAddUpdateDeliveryInfo(Request $request) 
  {
      $requestPostJson= json_decode(json_encode($request ->getContent()));
      //CALL USP_POST_DELIVERY_ORDER_ACTIVITY{"INFO":{"ITEM":"ADD","RECORD_SYS_ID":0,"DELIVERY_ORDER_NO":"DO12345","SALES_ORDER_SYS_ID":123,"CUSTOMER_SYS_ID":456,"CUSTOMER_NAME":"ABC Electronics","ORDER_TYPE":"Online","ORDER_QTY":5,"ORDER_NO":"SO98765","INVOICE_DATE":"2023-10-13","DELIVERY_STATUS":"Shipped","PAYMENT_STATUS":"Paid","DELIVERY_DATE":"2023-10-14","DELIVERY_TIME":"10:00 AM","DELIVERY_TYPE":"Express","DELIVERY_CHARGE":10.00,"DELIVERY_INSTRUCTION":"Leave at front door","DELIVERY_ADDRESS":"test2000","CREATED_BY":1,"DETAILS":[{"SALES_ORDER_PRODUCT_SYS_ID":256,"PACKING_STATUS":"Packed","DELIVERY_STATUS":"Out for Delivery","PRODUCT_NAME":"Product A","QTY":10,"DESCRIPTION":"Description of Product A"},{"SALES_ORDER_PRODUCT_SYS_ID":257,"PACKING_STATUS":"Packed","DELIVERY_STATUS":"Out for Delivery","PRODUCT_NAME":"Product A","QTY":10,"DESCRIPTION":"Description of Product A"}]}}
      
      $payload =[
          'P_OPERATION_NAME' => 'ADD_UPDATE_DELIVERY',
          'P_JSON'=>$requestPostJson,
      ];
      $response = DB::select("call USP_POST_DELIVERY_ORDER_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
      if(!empty($response))
      {
          $response_decoded = json_decode($response[0]->JSON_VALUE, true);
          return $response_decoded;
      }
      else
      {
          $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
          return $response_data;
      }
  }
  //api-get-view-delivery-order-info
   //view delivery order info
   function GetViewDeliveryOrderInfo(Request $request)
   {
        //CALL USP_GET_DELIVERY_ORDER_INFO('DELIVERY_ORDER_INFO','SPECIFIC',2,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'DELIVERY_ORDER_INFO',
                   'P_ITEM' => $request -> ITEM,
                   'P_DELIVERY_ORDER_ID' => $request -> DELIVERY_ORDER_ID,
             
        ];
        $response = DB::select("call USP_GET_DELIVERY_ORDER_INFO(:P_OPERATION_NAME, :P_ITEM,:P_DELIVERY_ORDER_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }
   //api-get-view-delivery-order-history
   //View delivery order history
   function GetViewDeliveryOrderHistory(Request $request)
   {
        //CALL USP_GET_DELIVERY_ORDER_HISTORY('DELIVERY_ORDER_HISTORY','SPECIFIC',2,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'DELIVERY_ORDER_HISTORY',
                   'P_ITEM' => $request -> ITEM,
                   'P_SALES_ORDER_ID' => $request -> SALES_ORDER_ID,
             
        ];
        $response = DB::select("call USP_GET_DELIVERY_ORDER_HISTORY(:P_OPERATION_NAME, :P_ITEM,:P_SALES_ORDER_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-get-view-quotation-item-details
   //view quotation item details
   function GetViewQuotationItemDetails(Request $request)
   {
        //CALL USP_GET_QUOTATION_ITEM_DETAILS_ACTIVITY('VIEW_QUOTATION_DETAILS','SPECIFIC','1',@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_QUOTATION_DETAILS',
                   'P_ITEM' => $request -> ITEM,
                   'P_QUOTATION_SYS_ID' => $request -> QUOTATION_SYS_ID,
             
        ];
        $response = DB::select("call USP_GET_QUOTATION_ITEM_DETAILS_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_QUOTATION_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }
 
   //api-get-view-standard-renovation-contract
   //view standard renovation contract
   function GetViewStandardRenovationContract(Request $request)
   {
        //CALL USP_GET_VIEW_STANDARD_RENOVATION_CONTRACT_ACTIVITY('VIEW_STANDARD_RENOVATION_CONTRACT','SPECIFIC',2,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_STANDARD_RENOVATION_CONTRACT',
                   'P_ITEM' => $request -> ITEM,
                   'P_QUOTATION_SYS_ID' => $request -> QUOTATION_SYS_ID,
             
        ];
        $response = DB::select("call USP_GET_VIEW_STANDARD_RENOVATION_CONTRACT_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_QUOTATION_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-post-add-update-standard-renovation-contract
   //add update standard renovation contract
    // function PostAddUpdateStandardRenovationContract(Request $request) 
    // {
    //   $requestPostJson= json_decode(json_encode($request->getContent()));
    //   //CALL USP_POST_STANDARD_RENOVATION_CONTRACT_ACTIVITY('ADD_UPDATE_STANDARD_RENOVATION_CONTRACT', '{"ITEM":"ADD_UPDATE","QUOTATION_SYS_ID":"2","OWNER_NAME":"TEST","OWNER_NRIC_NUMBER":"10","OWNER_PREMISES_ADDRESS":"SPAIN","CONTRACT_SUM":"852.33","OWNER_SIGNATURE":"TEST","INTERIOR_DESIGNER_SIGNATURE":"TEST","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
      
    //     $payload =[
    //         'P_OPERATION_NAME' => 'ADD_UPDATE_STANDARD_RENOVATION_CONTRACT',
    //         'P_JSON'=>$requestPostJson,
    //     ];
    //     $response = DB::select("call USP_POST_STANDARD_RENOVATION_CONTRACT_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
    //     if(!empty($response))
    //     {
    //         $response_decoded = json_decode($response[0]->JSON_VALUE, true);
    //         if(isset($request['ITEM'])){
    //             $itemStatus = $request['ITEM'];
    //             if($itemStatus == 'ADD_UPDATE'){
    //                 $mailPayload =[
    //                     'P_OPERATION_NAME' => 'VIEW_EMAIL_CONFIG_DETAILS',
    //                     'P_ITEM' => 'MAIL_CONFIG',
    //                     'P_TEMPLATE' => 'QUOTATION_ACCEPTED',
    //                     'P_JSON'=>'{"QUOTATION_SYS_ID":'.$request->QUOTATION_SYS_ID.'}'
    //                 ];
    //                 $mailResponse = DB::select("call USP_GET_MAIL_TEMPLATE_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_TEMPLATE, :P_JSON, @ERRNO,@ERRMSG)", $mailPayload);
    //                 $mailResponseDecoded = json_decode($mailResponse[0]->JSON_VALUE, true);
    //                 $resStatus = $mailResponseDecoded['status'];
    //                 if($resStatus == 'true'){
    //                     $emailData = $mailResponseDecoded['response'][0];
    //                     if ($emailData) {
    //                         $email = explode(', ', $emailData["TO_EMAIL"]);
    //                         $toReceipientsEmail = [];
    //                         foreach($email as $index => $ce) { if($ce){ $toReceipientsEmail[$index] = trim($ce); }}
    //                         Mail::to($toReceipientsEmail)->queue(new AcceptanceQuotation($emailData));
    //                     }
    //                 }
    //             }
    //         }
    //         return $response_decoded;
    //     }
    //     else
    //     {
    //         $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
    //         return $response_data;
    //     }
    // }

    function PostAddUpdateStandardRenovationContract(Request $request) {
        //CALL USP_POST_STANDARD_RENOVATION_CONTRACT_ACTIVITY('ADD_UPDATE_STANDARD_RENOVATION_CONTRACT', '{"ITEM":"ADD_UPDATE","QUOTATION_SYS_ID":"2","OWNER_NAME":"TEST","OWNER_NRIC_NUMBER":"10","OWNER_PREMISES_ADDRESS":"SPAIN","CONTRACT_SUM":"852.33","OWNER_SIGNATURE":"TEST","INTERIOR_DESIGNER_SIGNATURE":"TEST","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
        $requestPostJson= json_decode(json_encode($request->getContent()));
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_STANDARD_RENOVATION_CONTRACT',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_STANDARD_RENOVATION_CONTRACT_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response)) {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            if(isset($request['ITEM'])){
                $itemStatus = $request['ITEM'];
                if($itemStatus == 'ADD_UPDATE'){
                    $emailSent = $this->sendQuotationAcceptedEmail($request->QUOTATION_SYS_ID);
                    if (!$emailSent) {
                        $response_decoded['response'] = 'Event Successfully, But Mail is not Triggered';
                    }
                }
            }
            return $response_decoded;
        }
        else {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    private function sendQuotationAcceptedEmail($quotationSysId)
    {
        try {
            $mailPayload = [
                'P_OPERATION_NAME' => 'VIEW_EMAIL_CONFIG_DETAILS',
                'P_ITEM' => 'MAIL_CONFIG',
                'P_TEMPLATE' => 'QUOTATION_ACCEPTED',
                'P_JSON' => '{"QUOTATION_SYS_ID":' . $quotationSysId . '}'
            ];
            $mailResponse = DB::select("call USP_GET_MAIL_TEMPLATE_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_TEMPLATE, :P_JSON, @ERRNO,@ERRMSG)", $mailPayload);
            $mailResponseDecoded = json_decode($mailResponse[0]->JSON_VALUE, true);
            $resStatus = $mailResponseDecoded['status'];
            if ($resStatus == 'true') {
                $emailData = $mailResponseDecoded['response'][0];
                if ($emailData) {
                    $email = explode(', ', $emailData["TO_EMAIL"]);
                    $toReceipientsEmail = [];
                    foreach ($email as $index => $ce) {
                        if ($ce) {
                            $toReceipientsEmail[$index] = trim($ce);
                        }
                    }
                    // Dispatch the job for sending email
                    SendAcceptanceQuotationEmail::dispatch($emailData, $toReceipientsEmail);
                    return true;
                }
            }
            return false;
        } catch (\Exception $e) {
            // Log the exception or handle it as needed
            return false;
        }
    }

  //api-get-view-file-upload
     //view file upload
     function GetViewFileUpload(Request $request)
     {
          //CALL USP_GET_FILE_UPLOAD_ACTIVITY('VIEW_FILE_UPLOAD','VIEW_ALL',4,'IMAGE',@ERRNO,@ERRMSG);
          $payload = [
                     'P_OPERATION_NAME' => 'VIEW_FILE_UPLOAD',
                     'P_ITEM' => $request -> ITEM,
                     'P_REFERENCE_SYS_ID' => $request -> REFERENCE_SYS_ID,
                     'P_MODULE' => $request -> MODULE,
                     'P_UPLOAD_FOR' => $request -> UPLOAD_FOR,
                     
          ];
          $response = DB::select("call USP_GET_FILE_UPLOAD_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_REFERENCE_SYS_ID,:P_MODULE,:P_UPLOAD_FOR, @ERRNO,@ERRMSG)", $payload);
          if(!empty($response))
          {
              $response_decoded = json_decode($response[0]->JSON_VALUE, true);
              return $response_decoded;
          }
          else
          {
              $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
              return $response_data;
          }
     }

     //api-post-add-update-file-upload
    // post add update file upload
    function PostAddUpdateFileUpload(Request $request) {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        // CALL USP_POST_FILE_UPLOAD_ACTIVITY('FILE_UPLOAD','{"ITEM":"ADD_UPDATE","FILE_SYS_ID":"0","REFERENCE_SYS_ID":"4","UPLOAD_FOR":"IMAGE_UPLOAD","FILE_NAME":"ABC.JPG","MODULE":"IMAGE","CREATED_BY":1}',@ERRNO,@ERRMSG);
        $payload =[ 'P_OPERATION_NAME' => 'FILE_UPLOAD', 'P_JSON'=>$requestPostJson, ];
        $response = DB::select("call USP_POST_FILE_UPLOAD_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response)) {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            if($request['ITEM'] == 'ADD_UPDATE_DRAWING'){
                if($request['DETAILS'][0]['REFERENCE_SYS_ID']){
                    $projectSysId = $request['DETAILS'][0]['REFERENCE_SYS_ID'];
                    $fileSysId = $response_decoded['FILE_SYS_ID'];
                    $emailSent = $this->sendDrawingEmail($projectSysId,$fileSysId);
                    if (!$emailSent) {
                        $response_decoded['response'] = 'Event Successfully, But Mail is not Triggered';
                    }
                }
            }
            return $response_decoded;
        }
        else {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    private function sendDrawingEmail($projectSysId,$fileSysId){
        try {
            $mailPayload =[
                'P_OPERATION_NAME' => 'VIEW_EMAIL_CONFIG_DETAILS',
                'P_ITEM' => 'MAIL_CONFIG',
                'P_TEMPLATE' => '3D_DRAWING_UPLOAD',
                'P_JSON'=>'{"PROJECT_MANAGEMENT_SYS_ID":'.$projectSysId.',"FILE_SYS_ID":'.$fileSysId.'}'
            ];
            $mailResponse = DB::select("call USP_GET_MAIL_TEMPLATE_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_TEMPLATE, :P_JSON, @ERRNO,@ERRMSG)", $mailPayload);
            $mailResponseDecoded = json_decode($mailResponse[0]->JSON_VALUE, true);
            $resStatus = $mailResponseDecoded['status'];
            if($resStatus == 'true'){
                $emailData = $mailResponseDecoded['response'][0];
                if ($emailData) {
                    $email = explode(', ', $emailData["TO_EMAIL"]);
                    $toReceipientsEmail = [];
                    foreach($email as $index => $ce) { if($ce){ $toReceipientsEmail[$index] = trim($ce); }}
                    // Dispatch the job for sending email
                    SendDrawingEmail::dispatch($emailData, $toReceipientsEmail);
                    return true;
                }
            }
            return false;
        } catch (\Exception $e) {
            // Log the exception or handle it as needed
            return false;
        }
    }

    //api-post-create-quotation-to-project
    // post create quotation to project
    function PostCreateQuotationToProject(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_QUOTATION_TO_PROJECT_ACTIVITY('CREATE_QUOTATION_TO_PROJECT', '{"ITEM":"ADD","QUOTATION_SYS_ID":80,"QUOTATION_DETAILS_ID":80,"CREATED_BY":"1"}',@ERRNO,@ERRMSG);SELECT @ERRMSG;
        $payload =[
            'P_OPERATION_NAME' => 'CREATE_QUOTATION_TO_PROJECT',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_QUOTATION_TO_PROJECT_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    //api-get-view-list-project-management-info
     //view list project management info
     function GetViewListProjectManagementInfo(Request $request)
     {
          //CALL USP_GET_LIST_PROJECT_MANAGEMENT_ACTIVITY('VIEW_PROJECT_MANAGEMENT_INFO','VIEW_ALL',@ERRNO,@ERRMSG);
          $payload = [
                     'P_OPERATION_NAME' => 'VIEW_PROJECT_MANAGEMENT_INFO',
                     'P_ITEM' => $request -> ITEM,
                     'P_USER_SYS_ID' => $request -> USER_SYS_ID,
                 
                    
                     
          ];
          $response = DB::select("call USP_GET_LIST_PROJECT_MANAGEMENT_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_USER_SYS_ID, @ERRNO,@ERRMSG)", $payload);
          if(!empty($response))
          {
              $response_decoded = json_decode($response[0]->JSON_VALUE, true);
              return $response_decoded;
          }
          else
          {
              $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
              return $response_data;
          }
     }

     //api-get-view-quotation-sign-form
  //view quotation sign form
  function GetViewQuotationSignForm(Request $request)
  {
       //CALL USP_GET_VIEW_QUOTATION_SIGN_FORM_ACTIVITY('VIEW_QUOTATION_SIGN_FORM','SPECIFIC',2,@ERRNO,@ERRMSG);
       $payload = [
                  'P_OPERATION_NAME' => 'VIEW_QUOTATION_SIGN_FORM',
                  'P_ITEM' => $request -> ITEM,
                  'P_QUOTATION_SYS_ID' => $request -> QUOTATION_SYS_ID,
            
       ];
       $response = DB::select("call USP_GET_VIEW_QUOTATION_SIGN_FORM_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_QUOTATION_SYS_ID, @ERRNO,@ERRMSG)", $payload);
       if(!empty($response))
       {
           $response_decoded = json_decode($response[0]->JSON_VALUE, true);
           return $response_decoded;
       }
       else
       {
           $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
           return $response_data;
       }
  }
 //api-post-add-update-quotation-sign-form
    // post add update quotation sign form
    function PostAddUpdateQuotationSignForm(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_QUOTATION_SIGN_FORM_ACTIVITY('ADD_UPDATE_QUOTATION_SIGN_FORM', '{"ITEM":"ADD_UPDATE","QUOTATION_SYS_ID":"1","OWNER_SIGNATURE":"TEST","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_QUOTATION_SIGN_FORM',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_QUOTATION_SIGN_FORM_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    //api-get-view-specific-project-management
  //view specific project management
  function GetViewSpecificProjectManagement(Request $request)
  {
       //CALL USP_GET_SPECIFIC_PROJECT_MANAGEMENT('VIEW_SPECIFIC_PROJECT_MANAGEMENT','SPECIFIC','297',@ERRNO,@ERRMSG);
       $payload = [
                  'P_OPERATION_NAME' => 'VIEW_SPECIFIC_PROJECT_MANAGEMENT',
                  'P_ITEM' => $request -> ITEM,
                  'P_PROJECT_MANAGEMENT_SYS_ID' => $request -> PROJECT_MANAGEMENT_SYS_ID,
            
       ];
       $response = DB::select("call USP_GET_SPECIFIC_PROJECT_MANAGEMENT(:P_OPERATION_NAME, :P_ITEM,:P_PROJECT_MANAGEMENT_SYS_ID, @ERRNO,@ERRMSG)", $payload);
       if(!empty($response))
       {
           $response_decoded = json_decode($response[0]->JSON_VALUE, true);
           return $response_decoded;
       }
       else
       {
           $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
           return $response_data;
       }
  }

   //api-get-view-project-handover
  //view  project handover
  function GetViewProjectHandover(Request $request)
  {
       //CALL USP_GET_VIEW_PROJECT_HANDOVER_ACTIVITY('VIEW_PROJECT_HANDOVER','SPECIFIC',297,@ERRNO,@ERRMSG);
       $payload = [
                  'P_OPERATION_NAME' => 'VIEW_PROJECT_HANDOVER',
                  'P_ITEM' => $request -> ITEM,
                  'P_PROJECT_MANAGEMENT_SYS_ID' => $request -> PROJECT_MANAGEMENT_SYS_ID,
            
       ];
       $response = DB::select("call USP_GET_VIEW_PROJECT_HANDOVER_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_PROJECT_MANAGEMENT_SYS_ID, @ERRNO,@ERRMSG)", $payload);
       if(!empty($response))
       {
           $response_decoded = json_decode($response[0]->JSON_VALUE, true);
           return $response_decoded;
       }
       else
       {
           $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
           return $response_data;
       }
  }
  //api-post-add-update-project-handover
    // post add update project handover
    function PostAddUpdateProjectHandover(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_PROJECT_HANDOVER_ACTIVITY('ADD_UPDATE_PROJECT_HANDOVER', '{"ITEM":"ADD_UPDATE","PROJECT_MANAGEMENT_SYS_ID":"297","PROJECT_REFERENCE_NO":"ABC1234","OWNER_NAME_1":"Tony Lee","OWNER_NRIC_NUMBER_1":"1231234","OWNER_NAME_2":"Su Chan","OWNER_NRIC_NUMBER_2":"1231234","PROJECT_SITE_ADDRESS":"9 Bishan Pl, Singapore 579837","DAY":"30th","MONTH":"October","YEAR":"2023","ASSIGNED_TO":"Stephen Lee","CUSTOMER_SIGNATURE":"Tony","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_PROJECT_HANDOVER',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_PROJECT_HANDOVER_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response)) {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            if(isset($request['ITEM'])){
                $itemStatus = $request['ITEM'];
                if($itemStatus == 'ADD_UPDATE'){
                    $emailSent = $this->sendProjectHandoverEmail($request['PROJECT_MANAGEMENT_SYS_ID']);
                    if (!$emailSent) {
                        $response_decoded['response'] = 'Event Successfully, But Mail is not Triggered';
                    }
                }
            }
            return $response_decoded;
        }
        else {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    private function sendProjectHandoverEmail($projectSysId){
        try {
            $mailPayload =[
                'P_OPERATION_NAME' => 'VIEW_EMAIL_CONFIG_DETAILS',
                'P_ITEM' => 'MAIL_CONFIG',
                'P_TEMPLATE' => 'HANDOVER_SIGNED',
                'P_JSON'=>'{"PROJECT_MANAGEMENT_SYS_ID":'.$projectSysId.'}'
            ];
            $mailResponse = DB::select("call USP_GET_MAIL_TEMPLATE_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_TEMPLATE, :P_JSON, @ERRNO,@ERRMSG)", $mailPayload);
            $mailResponseDecoded = json_decode($mailResponse[0]->JSON_VALUE, true);
            $resStatus = $mailResponseDecoded['status'];
            if($resStatus == 'true'){
                $emailData = $mailResponseDecoded['response'][0];
                if ($emailData) {
                    $email = explode(', ', $emailData["TO_EMAIL"]);
                    $toReceipientsEmail = [];
                    foreach($email as $index => $ce) { if($ce){ $toReceipientsEmail[$index] = trim($ce); }}
                    // Dispatch the job for sending email
                    SendProjectHandoverEmail::dispatch($emailData, $toReceipientsEmail);
                    return true;
                }
            }
            return false;
        } catch (\Exception $e) {
            // Log the exception or handle it as needed
            return false;
        }
    }

    //api-post-add-update-order-payment-details
    // add update order payment details
    function PostAddUpdateOrderPaymentDetails(Request $request) {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_ORDER_PAYMENT_ACTIVITY('ADD_UPDATE_ORDER_PAYMENT_DETAILS', '[{"ITEM":"ADD_UPDATE","ORDER_SYS_ID":"201","INVOICE_NO":"2","MODULE":"PAYABLE","PAYMENT_DESC":"ORDER_NEW","REF_NO":"100","AMOUNT":"45626","BANK_NAME":"UNION","CHEQUE_NO":"10","CHEQUE_DATE":"08-09-2023","BANK_SYS_ID":"6","PAYMENT_MODE":"ONLINE","TO_FROM_WHOM":"RAHUL ROY","PAYMENT_DATE":"08-01-2023","BALANCE":"8598","REMARKS":"TEST100","CREATED_BY":1},{"ITEM":"ADD","RECORD_SYS_ID":"6","ORDER_SYS_ID":"202","INVOICE_NO":"2","MODULE":"PAYABLE","PAYMENT_DESC":"ORDER_NEW","REF_NO":"100","AMOUNT":"45626","BANK_NAME":"BOI","CHEQUE_NO":"10","CHEQUE_DATE":"08-09-2023","BANK_SYS_ID":"6","PAYMENT_MODE":"ONLINE","TO_FROM_WHOM":"RAHUL ROY","PAYMENT_DATE":"08-01-2023","BALANCE":"8598","REMARKS":"TEST200","CREATED_BY":1}]',@ERRNO,@ERRMSG);SELECT @ERRMSG;
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_ORDER_PAYMENT_DETAILS',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_ORDER_PAYMENT_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response)) {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            if(isset($request[0]['MODULE'])){
                $moduleItem = $request[0]['MODULE'];
                if($moduleItem == 'PAYABLE'){
                    $emailSent = $this->sendPaymentReceivableEmail($response_decoded['RECORD_SYS_ID']);
                    if (!$emailSent) {
                        $response_decoded['response'] = 'Event Successfully, But Mail is not Triggered';
                    }
                }
            }
            return $response_decoded;
        }
        else {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    private function sendPaymentReceivableEmail($recordSysId){
        try {
            $mailPayload =[
                'P_OPERATION_NAME' => 'VIEW_EMAIL_CONFIG_DETAILS',
                'P_ITEM' => 'MAIL_CONFIG',
                'P_TEMPLATE' => 'PAYMENT_RECEIVABLE',
                'P_JSON'=>'{"RECORD_SYS_ID":'.$recordSysId.'}'
            ]; 
            $mailResponse = DB::select("call USP_GET_MAIL_TEMPLATE_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_TEMPLATE, :P_JSON, @ERRNO,@ERRMSG)", $mailPayload);
            $mailResponseDecoded = json_decode($mailResponse[0]->JSON_VALUE, true);
            $resStatus = $mailResponseDecoded['status'];
            if($resStatus == 'true'){
                $emailData = $mailResponseDecoded['response'][0];
                if ($emailData) {
                    $email = explode(', ', $emailData["TO_EMAIL"]);
                    $toReceipientsEmail = [];
                    foreach($email as $index => $ce) { if($ce){ $toReceipientsEmail[$index] = trim($ce); }}
                    // Dispatch the job for sending email
                    SendPaymentReceivableEmail::dispatch($emailData, $toReceipientsEmail);
                    return true;
                }
            }
            return false;
        } catch (\Exception $e) {
            // Log the exception or handle it as needed
            return false;
        }

    }

     //api-get-view-order-payment-info
  //view order payment info
  function GetViewOrderPaymentInfo(Request $request)
  {
       //CALL USP_GET_ORDER_INFO_PAYMENT_ACTIVITY('VIEW_PAYMENT_INFO','VIEW_ALL','Receivable',@ERRNO,@ERRMSG);
       $payload = [
                  'P_OPERATION_NAME' => 'VIEW_PAYMENT_INFO',
                  'P_ITEM' => $request -> ITEM,
                  'P_MODULE' => $request -> MODULE,
            
       ];
       $response = DB::select("call USP_GET_ORDER_INFO_PAYMENT_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_MODULE, @ERRNO,@ERRMSG)", $payload);
       if(!empty($response))
       {
           $response_decoded = json_decode($response[0]->JSON_VALUE, true);
           return $response_decoded;
       }
       else
       {
           $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
           return $response_data;
       }
  }

   //api-get-view-order-payment-specific-info
  //view order payment specific info
  function GetViewOrderPaymentSpecificInfo(Request $request)
  {
       //CALL USP_GET_ORDER_PAYMENT_ACTIVITY('VIEW_ORDER_PAYMENT_DETAILS','SPECIFIC',6,'PAYABLE',@ERRNO,@ERRMSG);
       $payload = [
                  'P_OPERATION_NAME' => 'VIEW_ORDER_PAYMENT_DETAILS',
                  'P_ITEM' => $request -> ITEM,
                  'P_PROJECT_MANAGEMENT_SYS_ID' => $request -> PROJECT_MANAGEMENT_SYS_ID,
                  'P_MODULE' => $request -> MODULE,
            
       ];
       $response = DB::select("call USP_GET_ORDER_PAYMENT_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_PROJECT_MANAGEMENT_SYS_ID,:P_MODULE, @ERRNO,@ERRMSG)", $payload);
       if(!empty($response))
       {
           $response_decoded = json_decode($response[0]->JSON_VALUE, true);
           return $response_decoded;
       }
       else
       {
           $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
           return $response_data;
       }
  }
  //api-post-update-grn-status
    // update grn status
    function PostUpdateGrnStatus(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_GRN_STATUS_ACTIVITY('GRN_STATUS_UPDATE', '{"INFO":{"ITEM":"RECEIVING_STATUS","RECEIVING_STATUS":"Received","RECEIVED_DATE":"2023-09-28","REFERENCE_NO":"","GRN_ROUND_OFF_TOTAL":"","GRN_UNIT_TOTAL":"","GRN_TAX_TOTAL":"","GRN_GRAND_TOTAL":"","CREATED_BY":5,"PURCHASE_ORDER_ID":"102","DETAILS":[{"RECORD_SYS_ID":0,"PRODUCT_SYS_ID":"7","PURCHASE_ORDER_ID":"102","PURCHASE_DETAILS_SYS_ID":239,"PRODUCT_NAME":"BMC-693","PRODUCT_SKU_CODE":null,"QTY":10,"RECEIVED_QTY":10,"LESS_EXCESS":0,"BATCH_NO":"456123","LOT_NO":"456123","MF_DATE":"null","REJECTED_QTY":0,"ACCEPTED_QTY":10,"ADD_REDUCE_TYPE":"ADD","SUPPLIER_SYS_ID":3,"REF_LOCATION_SYS_ID":44,"ORGANISATION_SYS_ID":"1","LOCATION_TYPE":"Warehouse","ADJUSTMENT_TYPE":"PURCHASE ORDER ENTRY","CREATED_BY":5,"LESS_EXCESS_REMARKS":"","MODEL_NUMBER":"BMC123","ITEM_DIMENSION":"4x-5x-6x","ITEM_DESCRIPTION":"What is Lorem Ipsum?","ITEM_CATEGORY":"Coffee Table","ITEM_COLOR":"","ITEM_REMARKS":"dummy remarks"}]}}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'GRN_STATUS_UPDATE',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_GRN_STATUS_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    //api-get-view-list-purchase-order-info
  //view list purchase order info
  function GetViewListPurchaseOrderInfo(Request $request)
  {
       //CALL USP_GET_LIST_PURCHASE_ORDER_ACTIVITY('VIEW_PURCHASE_ORDER_INFO','VIEW_ALL','Open',@ERRNO,@ERRMSG);
       $payload = [
                  'P_OPERATION_NAME' => 'VIEW_PURCHASE_ORDER_INFO',
                  'P_ITEM' => $request -> ITEM,
                  'P_PROJECT_SYS_ID' => $request -> PROJECT_SYS_ID,
                  
            
       ];
       $response = DB::select("call USP_GET_LIST_PURCHASE_ORDER_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_PROJECT_SYS_ID, @ERRNO,@ERRMSG)", $payload);
       if(!empty($response))
       {
           $response_decoded = json_decode($response[0]->JSON_VALUE, true);
           return $response_decoded;
       }
       else
       {
           $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
           return $response_data;
       }
  }
    //api-post-add-update-project-documents
    // add update project documents
    function PostAddUpdateProjectDocuments(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_PROJECT_DOCUMENTS_ACTIVITY('ADD_UPDATE_PROJECT_DOCUMENTS', '{"ITEM":"ADD","RECORD_SYS_ID":"1","PROJECT_MANAGEMENT_SYS_ID":"2","DOCUMENT_TITLE":"TEST100","UPLOADED_FILE":"RRRTEST", "CREATED_BY":"1"}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_PROJECT_DOCUMENTS',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_PROJECT_DOCUMENTS_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }
  //api-get-view-specific-project-documents-info
    //view specific project documents info
    function GetViewSpecificProjectDocumentsInfo(Request $request)
   {
        //CALL USP_GET_SPECIFIC_PROJECT_DOCUMENTS_ACTIVITY('VIEW_PROJECT_DOCUMENTS_INFO','SPECIFIC',3,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_PROJECT_DOCUMENTS_INFO',
                   'P_ITEM' => $request -> ITEM,
                   'P_RECORD_SYS_ID' => $request -> RECORD_SYS_ID,
                  
        ];
        $response = DB::select("call USP_GET_SPECIFIC_PROJECT_DOCUMENTS_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_RECORD_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-get-view-list-project-documents-info
    //view list project documents info
    function GetViewListProjectDocumentsInfo(Request $request)
   {
        //CALL USP_GET_LIST_PROJECT_DOCUMENTS_ACTIVITY('VIEW_PROJECT_DOCUMENTS_INFO','VIEW_ALL',@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_PROJECT_DOCUMENTS_INFO',
                   'P_ITEM' => $request -> ITEM,
                   
                  
        ];
        $response = DB::select("call USP_GET_LIST_PROJECT_DOCUMENTS_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-get-view-list-payment-description
    //view list payment description
    function GetViewListPaymentDescription(Request $request)
   {
        //CALL USP_GET_VIEW_LIST_PAYMENT_DESCRIPTION_ACTIVITY('VIEW_PAYMENT_DESCRIPTION','VIEW_ALL',@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_PAYMENT_DESCRIPTION',
                   'P_ITEM' => $request -> ITEM,
                   'P_PROJECT_MANAGEMENT_SYS_ID' => $request -> PROJECT_MANAGEMENT_SYS_ID,
                   
                   
                  
        ];
        $response = DB::select("call USP_GET_VIEW_LIST_PAYMENT_DESCRIPTION_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_PROJECT_MANAGEMENT_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-post-add-update-project-defect-list
    // add update project defect list
    function PostAddUpdateProjectDefectList(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_PROJECT_DEFECT_LIST_ACTIVITY('ADD_UPDATE_PROJECT_DEFECT_LIST', '{"ITEM":"ADD_UPDATE","PROJECT_MANAGEMENT_SYS_ID":"0","AREA_TYPE":2,"AREA_DESCRIPTION":"NEW","DATE":"20","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_PROJECT_DEFECT_LIST',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_PROJECT_DEFECT_LIST_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    //api-get-view-list-defect-list
    //view list defect list
    function GetViewDefectList(Request $request)
   {
        //CALL USP_GET_DEFECT_LIST_ACTIVITY('VIEW_DEFECT_LIST','VIEW_ALL',1,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_DEFECT_LIST',
                   'P_ITEM' => $request -> ITEM,
                   'P_RECORD_SYS_ID' => $request -> RECORD_SYS_ID,
                  
        ];
        $response = DB::select("call USP_GET_DEFECT_LIST_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_RECORD_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }
       //api-post-add-update-project-material-list
    // add update project material list
    function PostAddUpdateProjectMaterialList(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_PROJECT_MATERIAL_LIST_ACTIVITY('ADD_UPDATE_PROJECT_MATERIAL_LIST', '[{"ITEM":"ADD_UPDATE","PROJECT_MANAGEMENT_SYS_ID":2,"SUPPLIER_ID":2,"TYPE":"NEW","SIZE":"20","AREA":"test","CODE":"12355","DESCRIPTION":"NEW TILE","CREATED_BY":"1"},{"ITEM":"ADD_UPDATE","PROJECT_MANAGEMENT_SYS_ID":"4","SUPPLIER_ID":3,"TYPE":"OLD","SIZE":"20","AREA":"test200","CODE":"12355","DESCRIPTION":"NEW TILE","CREATED_BY":"1"}]',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_PROJECT_MATERIAL_LIST',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_PROJECT_MATERIAL_LIST_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    //api-get-view-project-material-list
    //view project material list
    function GetViewProjectMaterialList(Request $request)
   {
        //CALL USP_GET_VIEW_PROJECT_MATERIAL_LIST_ACTIVITY('VIEW_PROJECT_MATERIAL_LIST','VIEW_ALL',1,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_PROJECT_MATERIAL_LIST',
                   'P_ITEM' => $request -> ITEM,
                   'P_RECORD_SYS_ID' => $request -> RECORD_SYS_ID,
                   
                  
        ];
        $response = DB::select("call USP_GET_VIEW_PROJECT_MATERIAL_LIST_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_RECORD_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

    //api-post-add-update-variation-order-info
    //add update variation order info
    function PostAddUpdateVariationOrderInfo(Request $request) {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_VARIATION_ORDER_ACTIVITY('ADD_UPDATE_VARIATION_ORDER_INFO', '{"INFO":{"ITEM":"ADD_UPDATE","QUOTATION_SYS_ID":"1","VO_SYS_ID":"0","CUSTOMER_SIGNATURE":"TEST","CREATED_BY":"1"}}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_VARIATION_ORDER_INFO',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_VARIATION_ORDER_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response)) {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            if(isset($request['INFO'])){
                if($request['INFO']['ITEM'] == 'E_SIGN'){
                    $projectSysId = $request['INFO']['PROJECT_SYS_ID'];
                    $voSysId = $request['INFO']['VO_SYS_ID'];
                    $emailSent = $this->sendVariationOrderApprovedEmail($projectSysId,$voSysId);
                    if (!$emailSent) {
                        $response_decoded['response'] = 'Event Successfully, But Mail is not Triggered';
                    }
                }
            }
            return $response_decoded;
        }
        else {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    private function sendVariationOrderApprovedEmail($projectSysId,$voSysId){
        try {
            $mailPayload =[
                'P_OPERATION_NAME' => 'VIEW_EMAIL_CONFIG_DETAILS',
                'P_ITEM' => 'MAIL_CONFIG',
                'P_TEMPLATE' => 'VO_APPROVED',
                'P_JSON'=>'{"PROJECT_MANAGEMENT_SYS_ID":'.$projectSysId.',"VO_SYS_ID":'.$voSysId.'}',
            ];
            $mailResponse = DB::select("call USP_GET_MAIL_TEMPLATE_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_TEMPLATE, :P_JSON, @ERRNO,@ERRMSG)", $mailPayload);
            if($mailResponse){
                $mailResponseDecoded = json_decode($mailResponse[0]->JSON_VALUE, true);
                $resStatus = $mailResponseDecoded['status'];
                if($resStatus == 'true'){
                    $emailData = $mailResponseDecoded['response'][0];
                    if ($emailData) {
                        $email = explode(', ', $emailData["TO_EMAIL"]);
                        $toReceipientsEmail = [];
                        foreach($email as $index => $ce) { if($ce){ $toReceipientsEmail[$index] = trim($ce); }}
                        // Dispatch the job for sending email
                        SendVariationOrderApprovedEmail::dispatch($emailData, $toReceipientsEmail);
                        return true;
                    }
                }
            }
            return false;
        } catch (\Exception $e) {
            // Log the exception or handle it as needed
            return false;
        }

    }

    //api-get-view-list-variation-order-info
    //view list variation order info
    function GetViewListVariationOrderInfo(Request $request)
   {
        //CALL USP_GET_LIST_VARIATION_ORDER_ACTIVITY('VIEW_VARIATION_ORDER_INFO','VIEW_ALL',@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_VARIATION_ORDER_INFO',
                   'P_ITEM' => $request -> ITEM,
                   'P_PROJECT_SYS_ID' => $request -> PROJECT_SYS_ID,
                  
        ];
        $response = DB::select("call USP_GET_LIST_VARIATION_ORDER_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_PROJECT_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-get-view-specific-variation-order-info
    //view specific variation order info
    function GetViewSpecificVariationOrderInfo(Request $request)
   {
        //CALL USP_GET_SPECIFIC_VARIATION_ORDER_ACTIVITY('VIEW_SPECIFIC_VARIATION_ORDER_INFO','SPECIFIC',1,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_SPECIFIC_VARIATION_ORDER_INFO',
                   'P_ITEM' => $request -> ITEM,
                   'P_VO_SYS_ID' => $request -> VO_SYS_ID,
                   
                  
        ];
        $response = DB::select("call USP_GET_SPECIFIC_VARIATION_ORDER_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_VO_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-get-view-variation-item-details
    //view variation item details
    function GetViewVariationItemDetails(Request $request)
   {
        //CALL USP_GET_VARIATION_ITEM_DETAILS_ACTIVITY('VIEW_VARIATION_ITEM_DETAILS','SPECIFIC','358',@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_VARIATION_ITEM_DETAILS',
                   'P_ITEM' => $request -> ITEM,
                   'P_VO_SYS_ID' => $request -> VO_SYS_ID,
                   
                  
        ];
        $response = DB::select("call USP_GET_VARIATION_ITEM_DETAILS_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_VO_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-get-view-project-management-item-details
    //view project management item details
    function GetViewProjectManagementItemDetails(Request $request)
   {
        //CALL USP_GET_PROJECT_MANAGEMENT_ITEM_DETAILS_ACTIVITY('VIEW_PROJECT_ITEM_DETAILS','SPECIFIC','3',@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_PROJECT_ITEM_DETAILS',
                   'P_ITEM' => $request -> ITEM,
                   'P_PROJECT_MANAGEMENT_SYS_ID' => $request -> PROJECT_MANAGEMENT_SYS_ID,
                  
        ];
        $response = DB::select("call USP_GET_PROJECT_MANAGEMENT_ITEM_DETAILS_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_PROJECT_MANAGEMENT_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }
   //api-post-add-update-project-task
    //add update project task
    public function PostAddUpdateProjectTaskOld(Request $request) 
    {
        // Decode the incoming JSON request into an array of objects
        $requestPostJsonArray = json_decode($request->getContent(), true);
        if (json_last_error() !== JSON_ERROR_NONE) {
            return response()->json(['status' => 'false', 'response' => 'Invalid JSON input'], 400);
        }
        $payload = [
            'P_OPERATION_NAME' => 'ADD_UPDATE_PROJECT_TASK',
            'P_JSON' => json_encode($requestPostJsonArray),
        ];
        $response = DB::select("call USP_POST_PROJECT_TASK_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if (!empty($response)) {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
            // // Use a collection to leverage unique and pluck methods
            // $uniqueDetails = collect($requestPostJsonArray)
            //     ->unique('PROJECT_MANAGEMENT_DETAILS_SYS_ID')
            //     ->map(function ($item) {
            //         return [
            //             'PROJECT_MANAGEMENT_SYS_ID' => $item['PROJECT_MANAGEMENT_SYS_ID'],
            //             'PROJECT_MANAGEMENT_DETAILS_SYS_ID' => $item['PROJECT_MANAGEMENT_DETAILS_SYS_ID'],
            //         ];
            //     });

            // // Process email notifications for unique project management details
            // foreach ($uniqueDetails as $uniquePostJson) {
            //     $mailPayload = [
            //         'P_OPERATION_NAME' => 'VIEW_EMAIL_CONFIG_DETAILS',
            //         'P_ITEM' => 'MAIL_CONFIG',
            //         'P_TEMPLATE' => 'NEW_PROJECT_TASK',
            //         'P_JSON' => json_encode([
            //             'PROJECT_MANAGEMENT_SYS_ID' => $uniquePostJson['PROJECT_MANAGEMENT_SYS_ID'],
            //             'PROJECT_MANAGEMENT_DETAILS_SYS_ID' => $uniquePostJson['PROJECT_MANAGEMENT_DETAILS_SYS_ID'],
            //         ]),
            //     ];

            //     $mailResponse = DB::select("call USP_GET_MAIL_TEMPLATE_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_TEMPLATE, :P_JSON, @ERRNO,@ERRMSG)", $mailPayload);
            //     $mailResponseDecoded = json_decode($mailResponse[0]->JSON_VALUE, true);
            //     $resStatus = $mailResponseDecoded['status'];
            //     if($resStatus == 'true'){
            //         $emailData = $mailResponseDecoded['response'][0];
            //         if ($emailData) {
            //             $email = explode(', ', $emailData["TO_EMAIL"]);
            //             $toReceipientsEmail = [];
            //             foreach($email as $index => $ce) { if($ce){ $toReceipientsEmail[$index] = trim($ce); }}
            //             Mail::to($toReceipientsEmail)->queue(new AssigningProjectTask($emailData));
            //         }
            //     }
            // }

            
        } else {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    public function PostAddUpdateProjectTask(Request $request) 
    {
        // Decode the incoming JSON request into an array of objects
        $requestPostJsonArray = json_decode($request->getContent(), true);
        if (json_last_error() !== JSON_ERROR_NONE) {
            return response()->json(['status' => 'false', 'response' => 'Invalid JSON input'], 400);
        }
        $payload = [
            'P_OPERATION_NAME' => 'ADD_UPDATE_PROJECT_TASK',
            'P_JSON' => json_encode($requestPostJsonArray),
        ];
        $response = DB::select("call USP_POST_PROJECT_TASK_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if (!empty($response)) {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            if($response_decoded['status'] == 'true'){
                return $response_decoded;
            }
            return $response_decoded;
        } else {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    private function sendNewTaskAssignedEmail($requestPostJsonArray){
        try {
            $projectManagementSubDetails = [];
            $projectManagementSysId = null;
            foreach ($requestPostJsonArray as $item) {
                $projectManagementSubDetails[] = $item['PROJECT_MANAGEMENT_SUB_DETAILS_SYS_ID'];
                $projectManagementSysId = $item['PROJECT_MANAGEMENT_SYS_ID'];
            }
            $newObject = [
                'PROJECT_MANAGEMENT_SUB_DETAILS_SYS_ID' => implode(',', $projectManagementSubDetails),
                'PROJECT_MANAGEMENT_SYS_ID' => $projectManagementSysId,
            ];
            $mailPayload = [
                'P_OPERATION_NAME' => 'VIEW_EMAIL_CONFIG_DETAILS',
                'P_ITEM' => 'MAIL_CONFIG',
                'P_TEMPLATE' => 'NEW_TASK_ASSIGNED',
                'P_JSON' => json_encode($newObject),
            ];
            $mailResponse = DB::select("call USP_GET_MAIL_TEMPLATE_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_TEMPLATE, :P_JSON, @ERRNO,@ERRMSG)", $mailPayload);
            //return $mailResponse;
            $mailResponseDecoded = json_decode($mailResponse[0]->JSON_VALUE, true);
            
            $resStatus = $mailResponseDecoded['status'];
            if($resStatus == 'true'){
                $emailData = $mailResponseDecoded['response'][0];
                if ($emailData) {
                    $email = explode(', ', $emailData["TO_EMAIL"]);
                    $toReceipientsEmail = [];
                    foreach($email as $index => $ce) { if($ce){ $toReceipientsEmail[$index] = trim($ce); }}
                    Mail::to($toReceipientsEmail)->queue(new AssigningProjectTask($emailData));
                    return true;
                }
            }
            return false;
        } catch (\Exception $e) {
            // Log the exception or handle it as needed
            return false;
        }
    }

    public function NewPostAddUpdateProjectTaskForEmailTest(Request $request) 
    {
        // Decode the incoming JSON request into an array of objects
        $requestPostJsonArray = json_decode($request->getContent(), true);
        $projectManagementSubDetails = [];
        $projectManagementSysId = null;
        foreach ($requestPostJsonArray as $item) {
            $projectManagementSubDetails[] = $item['PROJECT_MANAGEMENT_SUB_DETAILS_SYS_ID'];
            $projectManagementSysId = $item['PROJECT_MANAGEMENT_SYS_ID'];
        }
        $newObject = [
            'PROJECT_MANAGEMENT_SUB_DETAILS_SYS_ID' => implode(',', $projectManagementSubDetails),
            'PROJECT_MANAGEMENT_SYS_ID' => $projectManagementSysId,
        ];
        $newJson = json_encode($newObject);
        return $newJson;
        $mailResponseDecoded = json_decode('{
        "status": "true",
        "response": [
            {
            "FOOTER": "<p><span style=\"color:#c0392b\">****** This is auto generated&nbsp;email. Please do not reply. *******</span></p>",
            "SUBJECT": "New Task Assigned 9 Creation Pte. Ltd.",
            "CC_EMAIL": "",
            "TO_EMAIL": "prasanta.saren@yopmail.com, xiaoqi@yopmail.com, Dennise@yopmail.com, Shine@yopmail.com, shuxuan@yopmail.com, Jagadeesh@yopmail.com, sujay@yopmail.com, surya.biswas@yopmail.com, howardlam@yopmail.com, ",
            "SIGNATURE": "Sabya",
            "MAIL_CONTENT": [
                {
                "DATE": "12-Jun-2024",
                "STATUS": "Completed",
                "PROJECT_NO": "Pro-ST-24-06-283",
                "LIST_OF_WORK": "Preliminaries Works",
                "TASK": "Interior design & space planning          \n- Furniture layout plan          \n- Colour & material proposal          \n- Consultation & project management       \n- To apply for renovation permit HDB ,    \n- Condo application deposit (borne by owner)          \n- To apply for public liability and workman compensation   \n   insurance, if any borne by owner  \n- 3D perspective drawing",
                "SUB_CONTRACTOR_NAME": "CSS Design"
                },
                {
                "DATE": "12-Jun-2024",
                "STATUS": "Completed",
                "PROJECT_NO": "Pro-ST-24-06-283",
                "LIST_OF_WORK": "Demolition Works",
                "TASK": "Lay corrugated paper for floor protection & clear debris",
                "SUB_CONTRACTOR_NAME": "CSS Design"
                },
                {
                "DATE": "12-Jun-2024",
                "STATUS": "Pending",
                "PROJECT_NO": "Pro-ST-24-06-283",
                "LIST_OF_WORK": "Demolition Works",
                "TASK": "Hacking & Dismantle Works: - Erection or demolition works are subjected to HDB / Condo management approval. - Renovation time may be affected if there is delay in approval. - Price do not include d ",
                "SUB_CONTRACTOR_NAME": "CSS Design"
                },
                {
                "DATE": "12-Jun-2024",
                "STATUS": "Pending",
                "PROJECT_NO": "Pro-ST-24-06-283",
                "LIST_OF_WORK": "Carpentery Works",
                "TASK": "Interior design & space planning - Furniture layout plan - Colour & material proposal - Consultation & project management - To apply for renovation permit HDB",
                "SUB_CONTRACTOR_NAME": "CSS Design"
                }
            ]
            }
        ]
        }',true);
        $resStatus = $mailResponseDecoded['status'];
        if($resStatus == 'true'){
            $emailData = $mailResponseDecoded['response'][0];
            if ($emailData) {
                $email = explode(', ', $emailData["TO_EMAIL"]);
                $toReceipientsEmail = [];
                foreach($email as $index => $ce) { if($ce){ $toReceipientsEmail[$index] = trim($ce); }}
                Mail::to($toReceipientsEmail)->queue(new AssigningProjectTask($emailData));
                return $response_data = ['status' => 'true', 'response' => 'Mail Sent Succesfully'];
            }
        }
    }

    //api-post-add-update-project-check-list
    //add update project check list
    function PostAddUpdateProjectCheckList(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_PROJECT_CHECK_LIST_ACTIVITY('ADD_UPDATE_PROJECT_CHECK_LIST', '{"ITEM":"ADD","RECORD_SYS_ID":"0","PROJECT_SYS_ID":"1","WORK_SCHEDULE":"1","HANDOVER_FORM":"1","DEFECT_FORM":"1","MATERIAL_LIST":"1","WATER_PROOFING":"1","LEADS":"1","FLOOR_PLAN":"1","CONSENT_LETTER":"1","BOND_CERTIFICATE":"1","RENO_DEPOSIT":"1","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_PROJECT_CHECK_LIST',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_PROJECT_CHECK_LIST_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    //api-get-view-specific-project-material-list
    //view specific project material list
    function GetViewSpecificProjectMaterialList(Request $request)
   {
        //CALL USP_GET_VIEW_SPECIFIC_PROJECT_MATERIAL_LIST_ACTIVITY('VIEW_PROJECT_MATERIAL_LIST','SPECIFIC',2,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_PROJECT_MATERIAL_LIST',
                   'P_ITEM' => $request -> ITEM,
                   'P_PROJECT_SYS_ID' => $request -> PROJECT_SYS_ID,
                  
        ];
        $response = DB::select("call USP_GET_VIEW_SPECIFIC_PROJECT_MATERIAL_LIST_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_PROJECT_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-get-view-defect-list-item-details
    //view-defect-list-item-details
    function GetViewDefectListItemDetails(Request $request)
   {
        //CALL USP_GET_DEFECT_LIST_ITEM_DETAILS_ACTIVITY('VIEW_DEFECT_LIST_ITEM_DETAILS','SPECIFIC','11',@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_DEFECT_LIST_ITEM_DETAILS',
                   'P_ITEM' => $request -> ITEM,
                   'P_RECORD_SYS_ID' => $request -> RECORD_SYS_ID,
                  
        ];
        $response = DB::select("call USP_GET_DEFECT_LIST_ITEM_DETAILS_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_RECORD_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-get-view-sub-contractor-task-list
    //view sub contractor task list
    function GetViewSubContractorTaskList(Request $request)
   {
        //CALL USP_GET_LIST_SUB_CONTRACTOR_TASK_ACTIVITY('VIEW_SUB_CONTRACTOR_DETAILS','SPECIFIC','7',@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_SUB_CONTRACTOR_DETAILS',
                   'P_ITEM' => $request -> ITEM,
                   'P_SUB_CONTRACTOR_SYS_ID' => $request -> SUB_CONTRACTOR_SYS_ID,
                  
        ];
        $response = DB::select("call USP_GET_LIST_SUB_CONTRACTOR_TASK_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_SUB_CONTRACTOR_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }
//api-get-view-material-list-item-details
    //view material list item details
    function GetViewMaterialListItemDetails(Request $request)
   {
        //CALL USP_GET_MATERIAL_LIST_ITEM_DETAILS_ACTIVITY('VIEW_MATERIAL_LIST_ITEM_DETAILS','SPECIFIC','18',@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_MATERIAL_LIST_ITEM_DETAILS',
                   'P_ITEM' => $request -> ITEM,
                   'P_RECORD_SYS_ID' => $request -> RECORD_SYS_ID,
                  
        ];
        $response = DB::select("call USP_GET_MATERIAL_LIST_ITEM_DETAILS_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_RECORD_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   

     //api-post-add-update-salesman-commission
    //add update salesman commission
    function PostAddUpdateSalesmanCommission(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_SALESMAN_COMMISSION_ACTIVITY('ADD_UPDATE_SALESMAN_COMMISSION', '{"ITEM":"ADD","RECORD_SYS_ID":"0","PROJECT_MANAGEMENT_SYS_ID":"1","ASSIGNED_SYS_ID":"1","AMOUNT":"500.56","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_SALESMAN_COMMISSION',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_SALESMAN_COMMISSION_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

 //api-get-view-specific-salesman-commission-info
    //view specific salesman commission info
    function GetViewSpecificSalesmanCommissionInfo(Request $request)
   {
        //CALL USP_GET_SPECIFIC_SALESMAN_COMMISSION_ACTIVITY('VIEW_SALESMAN_COMMISSION_INFO','SPECIFIC',1,@ERRNO,@ERRMSG);

        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_SALESMAN_COMMISSION_INFO',
                   'P_ITEM' => $request -> ITEM,
                   'P_PROJECT_SYS_ID' => $request -> PROJECT_SYS_ID,
           
                  
        ];
        $response = DB::select("call USP_GET_SPECIFIC_SALESMAN_COMMISSION_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_PROJECT_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-post-add-update-purchase-order-info
    //add update purchase order info
    function PostAddUpdatePurchaseOrderInfoWeb(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_SALESMAN_COMMISSION_ACTIVITY('ADD_UPDATE_SALESMAN_COMMISSION', '{"ITEM":"ADD","RECORD_SYS_ID":"0","PROJECT_MANAGEMENT_SYS_ID":"1","ASSIGNED_SYS_ID":"1","AMOUNT":"500.56","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_PURCHASE_INFO',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_PURCHASE_ORDER_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }
    //api-post-add-update-project-management
    //add update project management
    function PostAddUpdateProjectManagement(Request $request) {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_PROJECT_MANAGEMENT_ACTIVITY('UPDATE_PROJECT_MANAGEMENT', '{"ITEM":"UPDATE","RECORD_SYS_ID":"1","PROJECT_MANAGEMENT_SYS_ID":"1","PROJECT_START_DATE":"2023-11-16","PROJECT_END_DATE":"2024-12-23","PROJECT_STATUS":"PENDING","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'UPDATE_PROJECT_MANAGEMENT',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_PROJECT_MANAGEMENT_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response)){
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            if(isset($request['ITEM'])){
                $itemStatus = $request['ITEM'];
                if($itemStatus == "UPDATE"){
                    $emailSent = $this->sendProjectStartedEmail($request->PROJECT_MANAGEMENT_SYS_ID);
                    if (!$emailSent) {
                        $response_decoded['response'] = 'Event Successfully, But Mail is not Triggered';
                    }
                }
            }
            return $response_decoded;
        }
        else {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    private function sendProjectStartedEmail($projectSysId){
        try {
            $mailPayload =[
                'P_OPERATION_NAME' => 'VIEW_EMAIL_CONFIG_DETAILS',
                'P_ITEM' => 'MAIL_CONFIG',
                'P_TEMPLATE' => 'NEW_PROJECT_START',
                'P_JSON'=>'{"PROJECT_MANAGEMENT_SYS_ID":'.$projectSysId.'}'
            ];
            $mailResponse = DB::select("call USP_GET_MAIL_TEMPLATE_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_TEMPLATE, :P_JSON, @ERRNO,@ERRMSG)", $mailPayload);
            $mailResponseDecoded = json_decode($mailResponse[0]->JSON_VALUE, true);
            $resStatus = $mailResponseDecoded['status'];
            if($resStatus == 'true'){
                $emailData = $mailResponseDecoded['response'][0];
                if ($emailData) {
                    $email = explode(', ', $emailData["TO_EMAIL"]);
                    $toReceipientsEmail = [];
                    foreach($email as $index => $ce) { if($ce){ $toReceipientsEmail[$index] = trim($ce); }}
                    // Dispatch the job for sending email
                    SendProjectStartedEmail::dispatch($emailData, $toReceipientsEmail);
                    return true;
                }
            }
            return false;
        } catch (\Exception $e) {
            // Log the exception or handle it as needed
            return false;
        }

    }

    //api-post-add-update-sub-contractor-mapping
    //add update sub contractor mapping
    function PostAddUpdateSubContractorMapping(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_SUB_CONTRACTOR_MAPPING_ACTIVITY('ADD_UPDATE_SUB_CONTRACTOR_MAPPING','[{"RECORD_SYS_ID":"0","ITEM":"ADD","USER_SYS_ID":"1","LOCATION_TYPE":"Test","REF_LOCATION_SYS_ID":"10","CREATED_BY":1},{"RECORD_SYS_ID":"0","ITEM":"ADD","USER_SYS_ID":"1","LOCATION_TYPE":"Test10","REF_LOCATION_SYS_ID":"10","CREATED_BY":1}]',@ERRNO,@ERRMSG); 
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_SUB_CONTRACTOR_MAPPING',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_SUB_CONTRACTOR_MAPPING_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    //api-get-view-specific-sub-contractor-mapping-info
    //view specific sub contractor mapping info
    function GetViewSpecificSubContractorMappingInfo(Request $request)
   {
        //CALL USP_GET_VIEW_SUB_CONTRACTOR_MAPPING_ACTIVITY('VIEW_SUB_CONTRACTOR_MAPPING_INFO','SPECIFIC',@ERRNO,@ERRMSG);

        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_SUB_CONTRACTOR_MAPPING_INFO',
                   'P_ITEM' => $request -> ITEM,
                   
           
                  
        ];
        $response = DB::select("call USP_GET_VIEW_SUB_CONTRACTOR_MAPPING_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-get-view-sales-report-info
    //view sales report info
    function GetViewSalesReportInfo(Request $request)
   {
        //CALL USP_SALES_REPORT_ACTIVITY('VIEW_SALES_REPORT','SPECIFIC',@ERRNO,@ERRMSG);

        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_SALES_REPORT',
                   'P_ITEM' => $request -> ITEM,
                   'P_USER_SYS_ID' => $request -> USER_SYS_ID,
                   
             
                   
           
                  
        ];
        $response = DB::select("call USP_SALES_REPORT_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_USER_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-get-view-leads-report-info
    //view leads report info
    function GetViewLeadsReportInfo(Request $request)
   {
        //CALL USP_LEADS_SUMMARY_REPORT_ACTIVITY ('VIEW_LEADS_REPORT','SPECIFIC',37,@ERRNO,@ERRMSG);

        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_LEADS_REPORT',
                   'P_ITEM' => $request -> ITEM,
                   'P_RECORD_SYS_ID' => $request -> RECORD_SYS_ID,
             
                   
           
                  
        ];
        $response = DB::select("call USP_LEADS_SUMMARY_REPORT_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_RECORD_SYS_ID,@ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-get-view-list-sub-contractor-by-work
    //view list sub contractor by work
    function GetViewListSubContractorByWork(Request $request)
   {
        //CALL USP_GET_VIEW_LIST_SUB_CONTRACTOR_BY_WORK_ACTIVITY('VIEW_SUB_CONTRACTOR_LIST_BY_WORK','VIEW_ALL',@ERRNO,@ERRMSG);

        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_SUB_CONTRACTOR_LIST_BY_WORK',
                   'P_ITEM' => $request -> ITEM,
                   
             
                   
           
                  
        ];
        $response = DB::select("call USP_GET_VIEW_LIST_SUB_CONTRACTOR_BY_WORK_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }
//api-post-add-update-project-others
    //add update project others
    function PostAddUpdateProjectOthers(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_PROJECT_OTHERS_ACTIVITY('ADD_UPDATE_PROJECT_OTHERS', '{"ITEM":"ADD","RECORD_SYS_ID":"0","PROJECT_SYS_ID":"13","OTHERS":"TEST1","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_PROJECT_OTHERS',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_PROJECT_OTHERS_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    //api-post-update-purchase-order-status
    function PostAddUpdatePurchaseOrderStatus(Request $request) {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_PURCHASE_ORDER_STATUS_ACTIVITY('PURCHASE_ORDER_STATUS_UPDATE', '{"ITEM":"ORDER_STATUS","PURCHASE_ORDER_ID":1,"ORDER_STATUS":"Approved","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'PURCHASE_ORDER_STATUS_UPDATE',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_PURCHASE_ORDER_STATUS_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response)) {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            if(isset($request[0]['ORDER_STATUS'])){
                $orderStatus = $request[0]['ORDER_STATUS'];
                if($orderStatus == 'Approved' || $orderStatus == 'Rejected'){
                    $emailSent = $this->sendApproveDeclineInvoiceEmail($request[0]['PURCHASE_ORDER_ID']);
                    if (!$emailSent) {
                        $response_decoded['response'] = 'Event Successfully, But Mail is not Triggered';
                    }
                }
            }
            return $response_decoded;
        }
        else {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    private function sendApproveDeclineInvoiceEmail($purchaseOrderId){
        try {
            $mailPayload =[
                'P_OPERATION_NAME' => 'VIEW_EMAIL_CONFIG_DETAILS',
                'P_ITEM' => 'MAIL_CONFIG',
                'P_TEMPLATE' => 'INVOICE_STATUS',
                'P_JSON'=>'{"PURCHASE_ORDER_SYS_ID":'.$purchaseOrderId.'}'
            ];
            $mailResponse = DB::select("call USP_GET_MAIL_TEMPLATE_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_TEMPLATE, :P_JSON, @ERRNO,@ERRMSG)", $mailPayload);
            $mailResponseDecoded = json_decode($mailResponse[0]->JSON_VALUE, true);
            $resStatus = $mailResponseDecoded['status'];
            if($resStatus == 'true'){
                $emailData = $mailResponseDecoded['response'][0];
                if ($emailData) {
                    $email = explode(', ', $emailData["TO_EMAIL"]);
                    $toReceipientsEmail = [];
                    foreach($email as $index => $ce) { if($ce){ $toReceipientsEmail[$index] = trim($ce); }}
                    // Dispatch the job for sending email
                    SendApproveDeclineInvoiceEmail::dispatch($emailData, $toReceipientsEmail);
                    return true;
                }
            }
            return false;
        } catch (\Exception $e) {
            // Log the exception or handle it as needed
            return false;
        }
    }

    //api-post-add-update-package-order-info
    function PostAddUpdatePackageOrderInfo(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_PACKAGE_ACTIVITY('ADD_UPDATE_PACKAGE_INFO', '{"PACKAGE_NAME":"Chips","PACKAGE_AMOUNT":2500,"DETAILS":[{"MODULE":"UPDATE_ITEM_DETAILS","PACKAGE_DETAILS_SYS_ID":0,"PACKAGE_SYS_ID":2,"WORK_CATEGORY_ID":120,"WORK_CATEGORY_VALUE":"Category A","INDEX_NO":124,"SUB_TOTAL":1500,"CREATED_BY":"User1","SUB_DETAILS":[{"PACKAGE_SUB_DETAILS_ID":0,"PACKAGE_SYS_ID":2,"WORK_SUB_CATEGORY_ID":201,"WORK_SUB_CATEGORY_VALUE":"Subcategory Y","INDEX_NO":124,"QTY":20,"UOM":"Unit","ESTIMATED_COST":2000,"GROSS_AMOUNT":2400,"ACTUAL_AMOUNT":2200},{"PACKAGE_SUB_DETAILS_ID":0,"PACKAGE_SYS_ID":1,"WORK_SUB_CATEGORY_ID":201,"WORK_SUB_CATEGORY_VALUE":"Subcategory Y","INDEX_NO":124,"QTY":20,"UOM":"Unit","ESTIMATED_COST":2000,"GROSS_AMOUNT":2400,"ACTUAL_AMOUNT":2200}]},{"MODULE":"UPDATE_ITEM_DETAILS","PACKAGE_DETAILS_SYS_ID":0,"PACKAGE_SYS_ID":1,"WORK_CATEGORY_ID":100,"WORK_CATEGORY_VALUE":"Category B","INDEX_NO":123,"SUB_TOTAL":1000,"CREATED_BY":"User1","SUB_DETAILS":[{"PACKAGE_SUB_DETAILS_ID":0,"PACKAGE_SYS_ID":1,"WORK_SUB_CATEGORY_ID":201,"WORK_SUB_CATEGORY_VALUE":"Subcategory Z","INDEX_NO":124,"QTY":20,"UOM":"Unit","ESTIMATED_COST":2000,"GROSS_AMOUNT":2400,"ACTUAL_AMOUNT":2200},{"PACKAGE_SUB_DETAILS_ID":0,"PACKAGE_SYS_ID":1,"WORK_SUB_CATEGORY_ID":201,"WORK_SUB_CATEGORY_VALUE":"Subcategory Y","INDEX_NO":124,"QTY":20,"UOM":"Unit","ESTIMATED_COST":2000,"GROSS_AMOUNT":2400,"ACTUAL_AMOUNT":2200}]}]}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_PACKAGE_INFO',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_PACKAGE_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    //api-get-list-package-info
    function GetListPackageInfo(Request $request)
    {
        //CALL USP_GET_LIST_PACKAGE_ACTIVITY('VIEW_PACKAGE_INFO','VIEW_ALL',@ERRNO,@ERRMSG);
        $payload = [
                'P_OPERATION_NAME' => 'VIEW_PACKAGE_INFO',
                'P_ITEM' => $request -> ITEM,
                'P_USER_SYS_ID' => $request -> USER_SYS_ID,      
        ];
        $response = DB::select("call USP_GET_LIST_PACKAGE_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_USER_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    //api-get-view-package-item-details
    function GetViewPackageItemDetails(Request $request)
    {
         //CALL USP_GET_PACKAGE_ITEM_DETAILS_ACTIVITY('VIEW_PACKAGE_DETAILS','SPECIFIC','1',@ERRNO,@ERRMSG);
 
         $payload = [
                    'P_OPERATION_NAME' => 'VIEW_PACKAGE_DETAILS',
                    'P_ITEM' => $request -> ITEM,
                    'P_PACKAGE_SYS_ID' => $request -> PACKAGE_SYS_ID,      
         ];
         $response = DB::select("call USP_GET_PACKAGE_ITEM_DETAILS_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_PACKAGE_SYS_ID, @ERRNO,@ERRMSG)", $payload);
         if(!empty($response))
         {
             $response_decoded = json_decode($response[0]->JSON_VALUE, true);
             return $response_decoded;
         }
         else
         {
             $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
             return $response_data;
         }
    }


    //api-post-add-update-role-privileges
    //post add update role privileges
    function PostAddUpdateRolePrivileges(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_ROLE_PRIVILEGES_ACTIVITY('ADD_UPDATE_ROLE_PRIVILEGES', '{"ITEM":"ADD","RECORD_SYS_ID":"0","ROLE_SYS_ID":"4","SUB_MODULE_SYS_ID":"3","CREATE_ACCESS":"Y","VIEW_ACCESS":"Y","UPDATE_ACCESS":"N","DELETE_ACCESS":"N","FULL_ACCESS":"N","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_ROLE_PRIVILEGES',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_ROLE_PRIVILEGES_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }
//api-get-view-role-privilege-info
//view role privilege info
function GetViewRolePrivilegeInfo(Request $request)
{
     //CALL USP_GET_ROLE_PRIVILEGES_ACTIVITY('VIEW_ROLE_PRIVILEGES_INFO','SPECIFIC','1',@ERRNO,@ERRMSG);

     $payload = [
                'P_OPERATION_NAME' => 'VIEW_ROLE_PRIVILEGES_INFO',
                'P_ITEM' => $request -> ITEM,
                'P_RECORD_SYS_ID' => $request -> RECORD_SYS_ID,      
     ];
     $response = DB::select("call USP_GET_ROLE_PRIVILEGES_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_RECORD_SYS_ID, @ERRNO,@ERRMSG)", $payload);
     if(!empty($response))
     {
         $response_decoded = json_decode($response[0]->JSON_VALUE, true);
         return $response_decoded;
     }
     else
     {
         $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
         return $response_data;
     }
}


//api-get-view-sub-contractor-agreement
//view sub contractor agreement
function GetViewSubContractorAgreement(Request $request)
{
     //CALL USP_GET_SUB_CONTRACTOR_AGREEMENT_ACTIVITY('VIEW_SUB_CONTRACTOR_AGREEMENT','SPECIFIC',1,@ERRNO,@ERRMSG);

     $payload = [
                'P_OPERATION_NAME' => 'VIEW_SUB_CONTRACTOR_AGREEMENT',
                'P_ITEM' => $request -> ITEM,
                'P_SUB_CONTRACTOR_SYS_ID' => $request -> SUB_CONTRACTOR_SYS_ID,      
     ];
     $response = DB::select("call USP_GET_SUB_CONTRACTOR_AGREEMENT_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_SUB_CONTRACTOR_SYS_ID, @ERRNO,@ERRMSG)", $payload);
     if(!empty($response))
     {
         $response_decoded = json_decode($response[0]->JSON_VALUE, true);
         return $response_decoded;
     }
     else
     {
         $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
         return $response_data;
     }
}

//api-get-view-customer-details
//view customer details
function GetViewCustomerDetails(Request $request)
{
     //CALL USP_GET_CUSTOMER_DETAILS_ACTIVITY('VIEW_CUSTOMER_DETAILS','VIEW_PROJECT',33,@ERRNO,@ERRMSG);

     $payload = [
                'P_OPERATION_NAME' => 'VIEW_CUSTOMER_DETAILS',
                'P_ITEM' => $request -> ITEM,
                'P_LEAD_SYS_ID' => $request -> LEAD_SYS_ID,      
     ];
     $response = DB::select("call USP_GET_CUSTOMER_DETAILS_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_LEAD_SYS_ID, @ERRNO,@ERRMSG)", $payload);
     if(!empty($response))
     {
         $response_decoded = json_decode($response[0]->JSON_VALUE, true);
         return $response_decoded;
     }
     else
     {
         $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
         return $response_data;
     }
}
//api-get-view-in-tray
//view in tray
function GetViewInTray(Request $request)
{
     //CALL USP_GET_IN_TRAY_ACTIVITY('VIEW_IN_TRAY_DETAILS','SPECIFIC',98,@ERRNO,@ERRMSG);
     //CALL USP_GET_IN_TRAY_ACTIVITY('VIEW_IN_TRAY_DETAILS','VIEW_ALL',0,@ERRNO,@ERRMSG);

     $payload = [
                'P_OPERATION_NAME' => 'VIEW_IN_TRAY_DETAILS',
                'P_ITEM' => $request -> ITEM,
                'P_ASSIGNED_SYS_ID' => $request -> ASSIGNED_SYS_ID,      
     ];
     $response = DB::select("call USP_GET_IN_TRAY_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_ASSIGNED_SYS_ID, @ERRNO,@ERRMSG)", $payload);
     if(!empty($response))
     {
         $response_decoded = json_decode($response[0]->JSON_VALUE, true);
         return $response_decoded;
     }
     else
     {
         $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
         return $response_data;
     }
}

//api-get-view-calendar-task-list-info
//view calendar task list info
function GetViewCalendarTaskListInfo(Request $request)
{
     //CALL USP_GET_CALENDAR_TASK_LIST_ACTIVITY('VIEW_CALENDAR_TASK_LIST_INFO','VIEW_ALL',1,@ERRNO,@ERRMSG);

     $payload = [
                'P_OPERATION_NAME' => 'VIEW_CALENDAR_TASK_LIST_INFO',
                'P_ITEM' => $request -> ITEM,
                'P_USER_SYS_ID' => $request -> USER_SYS_ID,      
     ];
     $response = DB::select("call USP_GET_CALENDAR_TASK_LIST_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_USER_SYS_ID, @ERRNO,@ERRMSG)", $payload);
     if(!empty($response))
     {
         $response_decoded = json_decode($response[0]->JSON_VALUE, true);
         return $response_decoded;
     }
     else
     {
         $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
         return $response_data;
     }
}

//api-get-view-payment-tray
//view payment tray
function GetViewPaymentTray(Request $request)
{
     //CALL USP_GET_MASTER_PAYMENT_TRAY_ACTIVITY('VIEW_PAYMENT_TRAY_DETAILS','VIEW_ALL',103,@ERRNO,@ERRMSG);

     $payload = [
                'P_OPERATION_NAME' => 'VIEW_PAYMENT_TRAY_DETAILS',
                'P_ITEM' => $request -> ITEM,
                'P_ASSIGNED_SYS_ID' => $request -> ASSIGNED_SYS_ID,      
     ];
     $response = DB::select("call USP_GET_MASTER_PAYMENT_TRAY_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_ASSIGNED_SYS_ID, @ERRNO,@ERRMSG)", $payload);
     if(!empty($response))
     {
         $response_decoded = json_decode($response[0]->JSON_VALUE, true);
         return $response_decoded;
     }
     else
     {
         $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
         return $response_data;
     }
}

//api-get-view-application-log-info
//view application log info
function GetViewApplicationLogInfo(Request $request)
{
     //CALL USP_GET_LOG_ACTIVITY('VIEW_APPLICATION_LOG_INFO','VIEW_ALL','Leads',1,@ERRNO,@ERRMSG);

     $payload = [
                'P_OPERATION_NAME' => 'VIEW_APPLICATION_LOG_INFO',
                'P_ITEM' => $request -> ITEM,
                'P_EVENT' => $request -> EVENT,    
                'P_RECORD_SYS_ID' => $request -> RECORD_SYS_ID,  
                'P_PAGE_SIZE' => $request -> PAGE_SIZE,    
                'P_PAGE_NO' => $request -> PAGE_NO,
     ];
     $response = DB::select("call USP_GET_LOG_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_EVENT,:P_RECORD_SYS_ID,:P_PAGE_SIZE,:P_PAGE_NO, @ERRNO,@ERRMSG)", $payload);
     if(!empty($response))
     {
         $response_decoded = json_decode($response[0]->JSON_VALUE, true);
         return $response_decoded;
     }
     else
     {
         $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
         return $response_data;
     }
}

//api-get-view-dashboard-count
//view dashboard count
function GetViewDashboardCount(Request $request)
{
     //CALL USP_DASHBOARD_COUNT_ACTIVITY('VIEW_DASHBOARD_COUNT','DASHBOARD_COUNT',105,@ERRNO,@ERRMSG);

     $payload = [
                'P_OPERATION_NAME' => 'VIEW_DASHBOARD_COUNT',
                'P_ITEM' => $request -> ITEM,
                  
                'P_RECORD_SYS_ID' => $request -> RECORD_SYS_ID,  
     ];
     $response = DB::select("call USP_DASHBOARD_COUNT_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_RECORD_SYS_ID, @ERRNO,@ERRMSG)", $payload);
     if(!empty($response))
     {
         $response_decoded = json_decode($response[0]->JSON_VALUE, true);
         return $response_decoded;
     }
     else
     {
         $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
         return $response_data;
     }
}
//View User Role Privilege Info
function GetViewUserRolePrivilegeInfo(Request $request)
{
    //CALL USP_GET_ROLE_PRIVILEGES_ACTIVITY('VIEW_USER_ROLE_PRIVILEGES_INFO','SPECIFIC','4',@ERRNO,@ERRMSG);
    $payload = [
                'P_OPERATION_NAME' => 'VIEW_USER_ROLE_PRIVILEGES_INFO',
                'P_ITEM' => $request -> ITEM,
                'P_RECORD_SYS_ID'=> $request -> RECORD_SYS_ID,
     ];
     $response = DB::select("call USP_GET_ROLE_PRIVILEGES_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_RECORD_SYS_ID, @ERRNO,@ERRMSG)", $payload);
     if(!empty($response))
     {
         $response_decoded = json_decode($response[0]->json_value, true);
         return $response_decoded;
     }
     else
     {
         $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
         return $response_data;
     }
}
//api-get-view-sub-contractor-job-allocation
//view sub contractor job allocation
function GetViewSubContractorJobAllocation(Request $request)
{
     //CALL USP_GET_SUB_CONTRACTOR_JOB_ALLOCATION_ACTIVITY('VIEW_SUB_CONTRACTOR_JOB_ALLOCATION','SPECIFIC',45,@ERRNO,@ERRMSG);

     $payload = [
                'P_OPERATION_NAME' => 'VIEW_SUB_CONTRACTOR_JOB_ALLOCATION',
                'P_ITEM' => $request -> ITEM,
                'P_SUB_CONTRACTOR_SYS_ID' => $request -> SUB_CONTRACTOR_SYS_ID,      
     ];
     $response = DB::select("call USP_GET_SUB_CONTRACTOR_JOB_ALLOCATION_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_SUB_CONTRACTOR_SYS_ID, @ERRNO,@ERRMSG)", $payload);
     if(!empty($response))
     {
         $response_decoded = json_decode($response[0]->JSON_VALUE, true);
         return $response_decoded;
     }
     else
     {
         $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
         return $response_data;
     }
}

//api-post-add-update-organization
    //add update organization
    function PostAddUpdateOrganization(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_ORGANIZATION_ACTIVITY('ADD_UPDATE_ORGANIZATION', '{"ITEM":"ADD_UPDATE","RECORD_SYS_ID":"0","ORGANIZATION_SYS_ID":"1","ORGANIZATION_LOGO":"logo.jpg","TAX_NUMBER":"100","COMPANY_TYPE":"SERVICE","LEGAL_NAME":"CSS OFFICE","REGISTRATION_NO":"999","CONTACT_NO":"89653300","EMAIL":"css@yopmail.com","WEBSITE":"css.com","ADDRESS_LINE_1":"City","ADDRESS_LINE_2":"956230","CITY":"Singapore","PIN_CODE":"235600","COUNTRY":"SINGAPORE","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_ORGANIZATION',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_ORGANIZATION_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    //api-get-view-organization
//view organization
function GetViewOrganization(Request $request)
{
     //CALL USP_GET_VIEW_ORGANIZATION_ACTIVITY('VIEW_ORGANIZATION','VIEW_ALL','0',@ERRNO,@ERRMSG);

//CALL USP_GET_VIEW_ORGANIZATION_ACTIVITY('VIEW_ORGANIZATION','SPECIFIC','2',@ERRNO,@ERRMSG);

     $payload = [
                'P_OPERATION_NAME' => 'VIEW_ORGANIZATION',
                'P_ITEM' => $request -> ITEM,
                'P_RECORD_SYS_ID' => $request -> RECORD_SYS_ID,      
     ];
     $response = DB::select("call USP_GET_VIEW_ORGANIZATION_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_RECORD_SYS_ID, @ERRNO,@ERRMSG)", $payload);
     if(!empty($response))
     {
         $response_decoded = json_decode($response[0]->JSON_VALUE, true);
         return $response_decoded;
     }
     else
     {
         $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
         return $response_data;
     }
}
//api-get-view-emp-dashboard-count
//view emp dashboard count
function GetViewEmpDashboardCount(Request $request)
{
     //CALL USP_EMP_DASHBOARD_COUNT_ACTIVITY('VIEW_EMP_DASHBOARD_COUNT','DASHBOARD_COUNT',104,@ERRNO,@ERRMSG);



     $payload = [
                'P_OPERATION_NAME' => 'VIEW_EMP_DASHBOARD_COUNT',
                'P_ITEM' => $request -> ITEM,
                'P_RECORD_SYS_ID' => $request -> RECORD_SYS_ID,      
     ];
     $response = DB::select("call USP_EMP_DASHBOARD_COUNT_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_RECORD_SYS_ID, @ERRNO,@ERRMSG)", $payload);
     if(!empty($response))
     {
         $response_decoded = json_decode($response[0]->JSON_VALUE, true);
         return $response_decoded;
     }
     else
     {
         $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
         return $response_data;
     }
}

//api-get-view-leads-sample-report-info
//view leads sample report info
function GetViewLeadsSampleReportInfo(Request $request)
{
     //CALL USP_LEADS_REPORT_ACTIVITY('VIEW_LEADS_SAMPLE_REPORT','SPECIFIC',NULL,@ERRNO,@ERRMSG);



     $payload = [
                'P_OPERATION_NAME' => 'VIEW_LEADS_SAMPLE_REPORT',
                'P_ITEM' => $request -> ITEM,
                'P_ASSIGNED_TO' => $request -> ASSIGNED_TO,      
     ];
     $response = DB::select("call USP_LEADS_REPORT_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_ASSIGNED_TO, @ERRNO,@ERRMSG)", $payload);
     if(!empty($response))
     {
         $response_decoded = json_decode($response[0]->JSON_VALUE, true);
         return $response_decoded;
     }
     else
     {
         $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
         return $response_data;
     }
}


//api-get-view-sub-contractor-report-info
    //view sub contractor report info
    function GetViewSubContractorReportInfo(Request $request)
   {
        //CALL USP_SUB_CONTRACTOR_REPORT_ACTIVITY('VIEW_SUB_CONTRACTOR_REPORT','SPECIFIC','Open','103','1',NULL,NULL,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_SUB_CONTRACTOR_REPORT',
                   'P_ITEM' => $request -> ITEM,
                   'P_PROJECT_STATUS' => $request -> PROJECT_STATUS,
                   'P_USER_ID' => $request -> USER_ID,
                  
                   'P_SUB_CONTRACTOR_ID' => $request -> SUB_CONTRACTOR_ID,
                   'P_START_DATE' => $request -> START_DATE,
                   'P_END_DATE' => $request -> END_DATE,

             
                   
           
                  
        ];
        $response = DB::select("call USP_SUB_CONTRACTOR_REPORT_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_PROJECT_STATUS,:P_USER_ID,:P_SUB_CONTRACTOR_ID,:P_START_DATE,:P_END_DATE,@ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-get-view-project-report-info
    //view project report info
    function GetViewProjectReportInfo(Request $request)
   {
        //CALL USP_PROJECT_REPORT_ACTIVITY('VIEW_PROJECT_REPORT','SPECIFIC','Open','103',NULL,NULL,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_PROJECT_REPORT',
                   'P_ITEM' => $request -> ITEM,
                   'P_PROJECT_STATUS' => $request -> PROJECT_STATUS,
                   'P_USER_ID' => $request -> USER_ID,
            
              
                   'P_START_DATE' => $request -> START_DATE,
                   'P_END_DATE' => $request -> END_DATE,

             
                   
           
                  
        ];
        $response = DB::select("call USP_PROJECT_REPORT_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_PROJECT_STATUS,:P_USER_ID,:P_START_DATE,:P_END_DATE,@ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

    //api-get-view-payment-receivable-report-info
    // view payment receivable report info
    function GetViewPaymentReceivableReportInfo(Request $request)
   {
        //CALL USP_PAYMENT_RECEIVABLE_REPORT_ACTIVITY('VIEW_PAYMENT_RECEIVABLE_REPORT','SPECIFIC',NULL,1,NULL,NULL,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_PAYMENT_RECEIVABLE_REPORT',
                   'P_ITEM' => $request -> ITEM,
                   'P_PROJECT_STATUS' => $request -> PROJECT_STATUS,
                   'P_USER_ID' => $request -> USER_ID,
            
              
                   'P_START_DATE' => $request -> START_DATE,
                   'P_END_DATE' => $request -> END_DATE,

             
                   
           
                  
        ];
        $response = DB::select("call USP_PAYMENT_RECEIVABLE_REPORT_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_PROJECT_STATUS,:P_USER_ID,:P_START_DATE,:P_END_DATE,@ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-get-view-work-report-info
    //view work report info
    function GetViewWorkReportInfo(Request $request)
   {
        //CALL USP_WORK_REPORT_ACTIVITY('VIEW_WORK_REPORT','SPECIFIC',NULL,1,NULL,NULL,@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_WORK_REPORT',
                   'P_ITEM' => $request -> ITEM,
                   'P_PROJECT_STATUS' => $request -> PROJECT_STATUS,
                   'P_USER_ID' => $request -> USER_ID,
                   'P_START_DATE' => $request -> START_DATE,
                   'P_END_DATE' => $request -> END_DATE,
        ];
        $response = DB::select("call USP_WORK_REPORT_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_PROJECT_STATUS,:P_USER_ID,:P_START_DATE,:P_END_DATE,@ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-get-view-library-item-details
    //view library item details
    function GetViewLibraryItemDetails(Request $request)
    {
         //CALL USP_GET_LIBRARY_ITEM_DETAILS_ACTIVITY('VIEW_LIBRARY_ITEM_DETAILS','VIEW_ALL',@ERRNO,@ERRMSG);
         $payload = [
                    'P_OPERATION_NAME' => 'VIEW_LIBRARY_ITEM_DETAILS',
                    'P_ITEM' => $request -> ITEM,
                    'P_USER_SYS_ID' => $request -> USER_SYS_ID,
             
                  
                  
         ];
         $response = DB::select("call USP_GET_LIBRARY_ITEM_DETAILS_ACTIVITY(:P_OPERATION_NAME,:P_ITEM,:P_USER_SYS_ID,@ERRNO,@ERRMSG)", $payload);
         if(!empty($response))
         {
             $response_decoded = json_decode($response[0]->JSON_VALUE, true);
             return $response_decoded;
         }
         else
         {
             $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
             return $response_data;
         }
    }

     //api-post-add-update-sub-contractor
   //add update sub contractor
   function PostAddUpdateSubContractor(Request $request) 
   {
       $requestPostJson= json_decode(json_encode($request ->getContent()));
       //CALL USP_POST_SUB_CONTRACTOR_MGMT_ACTIVITY('ADD_UPDATE_SUB_CONTRACTOR', '{"ITEM":"ADD","SUB_CONTRACTOR_SYS_ID":"0","USER_SYS_ID":"1","COMPANY_NAME":"ABC","EMAIL":"abc@gmail.com" , "PHONE_NO":"123456","ADDRESS":"fcb", "POST_CODE":"7001" , "COUNTRY":"IN","BANK_NAME":"CBA","BANK_ACCOUNT_NO":"0707", "UEN":"524", "TYPE_OF_SUB_CONTRACTOR":"BAC", "REBATE":"10.2", "REMARK":"GOOD" ,"CREATED_BY":"1"}',@ERRNO,@ERRMSG);
       $payload =[
           'P_OPERATION_NAME' => 'ADD_UPDATE_SUB_CONTRACTOR',
           'P_JSON'=>$requestPostJson,
       ];
       $response = DB::select("call USP_POST_SUB_CONTRACTOR_MGMT_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
       if(!empty($response))
       {
           $response_decoded = json_decode($response[0]->JSON_VALUE, true);
           return $response_decoded;
       }
       else
       {
           $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
           return $response_data;
       }
   }

   //api-get-view-sub-contractor-task-list-Web
    //view sub contractor task list
    function GetViewSubContractorTaskListWeb(Request $request)
   {
        //CALL USP_GET_LIST_SUB_CONTRACTOR_TASK_ACTIVITY('VIEW_SUB_CONTRACTOR_DETAILS','SPECIFIC','7',@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_SUB_CONTRACTOR_DETAILS',
                   'P_ITEM' => $request -> ITEM,
                   'P_SUB_CONTRACTOR_SYS_ID' => $request -> SUB_CONTRACTOR_SYS_ID,
                  
        ];
        $response = DB::select("call USP_GET_LIST_SUB_CONTRACTOR_TASK_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_SUB_CONTRACTOR_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
   }

   //api-post-add-update-sub-contractor-Web
   //add update sub contractor
   function PostAddUpdateSubContractorWeb(Request $request) 
   {
       $requestPostJson= json_decode(json_encode($request ->getContent()));
       //CALL USP_POST_SUB_CONTRACTOR_MGMT_ACTIVITY('ADD_UPDATE_SUB_CONTRACTOR', '{"ITEM":"ADD","SUB_CONTRACTOR_SYS_ID":"0","USER_SYS_ID":"1","COMPANY_NAME":"ABC","EMAIL":"abc@gmail.com" , "PHONE_NO":"123456","ADDRESS":"fcb", "POST_CODE":"7001" , "COUNTRY":"IN","BANK_NAME":"CBA","BANK_ACCOUNT_NO":"0707", "UEN":"524", "TYPE_OF_SUB_CONTRACTOR":"BAC", "REBATE":"10.2", "REMARK":"GOOD" ,"CREATED_BY":"1"}',@ERRNO,@ERRMSG);
       $payload =[
           'P_OPERATION_NAME' => 'ADD_UPDATE_SUB_CONTRACTOR',
           'P_JSON'=>$requestPostJson,
       ];
       $response = DB::select("call USP_POST_SUB_CONTRACTOR_MGMT_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
       if(!empty($response))
       {
           $response_decoded = json_decode($response[0]->JSON_VALUE, true);
           return $response_decoded;
       }
       else
       {
           $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
           return $response_data;
       }
   }

    //api-get-view-file-upload-web
    //view file upload
    function GetViewFileUploadWeb(Request $request)
    {
        //CALL USP_GET_FILE_UPLOAD_ACTIVITY('VIEW_FILE_UPLOAD','VIEW_ALL',4,'IMAGE',@ERRNO,@ERRMSG);
        $payload = [
                    'P_OPERATION_NAME' => 'VIEW_FILE_UPLOAD',
                    'P_ITEM' => $request -> ITEM,
                    'P_REFERENCE_SYS_ID' => $request -> REFERENCE_SYS_ID,
                    'P_MODULE' => $request -> MODULE,
                    'P_UPLOAD_FOR' => $request -> UPLOAD_FOR,
                    
        ];
        $response = DB::select("call USP_GET_FILE_UPLOAD_ACTIVITY(:P_OPERATION_NAME, :P_ITEM,:P_REFERENCE_SYS_ID,:P_MODULE,:P_UPLOAD_FOR, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    //api-post-add-update-purchase-order-info
    //add update purchase order info
    function PostAddUpdatePurchaseOrderInfo(Request $request) {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_SALESMAN_COMMISSION_ACTIVITY('ADD_UPDATE_SALESMAN_COMMISSION', '{"ITEM":"ADD","RECORD_SYS_ID":"0","PROJECT_MANAGEMENT_SYS_ID":"1","ASSIGNED_SYS_ID":"1","AMOUNT":"500.56","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
        $payload =[ 'P_OPERATION_NAME' => 'ADD_UPDATE_PURCHASE_INFO', 'P_JSON'=>$requestPostJson, ];
        $response = DB::select("call USP_POST_PURCHASE_ORDER_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response)) {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            if(isset($request['ITEM'])){
                $itemStatus = $request['ITEM'];
                if($itemStatus == 'ADD_UPDATE'){
                    if(isset($response_decoded['PURCHASE_ORDER_SYS_ID'])){
                        $purchaseOrderSysId = $response_decoded['PURCHASE_ORDER_SYS_ID'];
                        $emailSent = $this->sendUploadInvoiceEmail($purchaseOrderSysId);
                        if (!$emailSent) {
                            $response_decoded['response'] = 'Event Successfully, But Mail is not Triggered';
                        }
                    }
                }
            }
            return $response_decoded;
        }
        else {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    private function sendUploadInvoiceEmail($purchaseOrderSysId){
        try {
            $mailPayload =[
                'P_OPERATION_NAME' => 'VIEW_EMAIL_CONFIG_DETAILS',
                'P_ITEM' => 'MAIL_CONFIG',
                'P_TEMPLATE' => 'INVOICE_UPLOAD',
                'P_JSON'=>'{"PURCHASE_ORDER_SYS_ID":'.$purchaseOrderSysId.'}'
            ];
            $mailResponse = DB::select("call USP_GET_MAIL_TEMPLATE_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_TEMPLATE, :P_JSON, @ERRNO,@ERRMSG)", $mailPayload);
            $mailResponseDecoded = json_decode($mailResponse[0]->JSON_VALUE, true);
            $resStatus = $mailResponseDecoded['status'];
            if($resStatus == 'true'){
                $emailData = $mailResponseDecoded['response'][0];
                if ($emailData) {
                    $email = explode(', ', $emailData["TO_EMAIL"]);
                    $toReceipientsEmail = [];
                    foreach($email as $index => $ce) { if($ce){ $toReceipientsEmail[$index] = trim($ce); }}
                    // Dispatch the job for sending email
                    SendUploadInvoiceEmail::dispatch($emailData, $toReceipientsEmail);
                    return true;
                }
            }
            return false;
        } catch (\Exception $e) {
            // Log the exception or handle it as needed
            return false;
        }
    }
    //api-post-add-update-file-upload-web
    // post add update file upload
    function PostAddUpdateFileUploadWeb(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        // CALL USP_POST_FILE_UPLOAD_ACTIVITY('FILE_UPLOAD','{"ITEM":"ADD_UPDATE","FILE_SYS_ID":"0","REFERENCE_SYS_ID":"4","UPLOAD_FOR":"IMAGE_UPLOAD","FILE_NAME":"ABC.JPG","MODULE":"IMAGE","CREATED_BY":1}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'FILE_UPLOAD',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_FILE_UPLOAD_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }

    //api-get-email-config-details-info
    //email config details info
    function GetEmailConfigDetailsInfo(Request $request)
    {
        //CALL USP_GET_MAIL_TEMPLATE_ACTIVITY('VIEW_EMAIL_CONFIG_DETAILS','MAIL_CONFIG','NEW_PROJECT_START',2,@ERRNO,@ERRMSG);
        $payload = [
                'P_OPERATION_NAME' => 'VIEW_EMAIL_CONFIG_DETAILS',
                'P_ITEM' => $request -> ITEM,
                'P_TEMPLATE' => $request -> TEMPLATE,
                'P_PARAMETER' => $request -> PARAMETER,        
        ];
        $response = DB::select("call USP_GET_MAIL_TEMPLATE_ACTIVITY(:P_OPERATION_NAME, :P_ITEM, :P_TEMPLATE,:P_PARAMETER, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }
    //api-get-view-lead-source
    //get-view-lead-source
    function GetViewLeadSource(Request $request)
    {
        //CALL USP_GET_ALL_MASTER_DATA('VIEW_LEAD_SOURCE','VIEW_ALL','0',@ERRNO,@ERRMSG);
        $payload = [
                'P_OPERATION_NAME' => 'VIEW_LEAD_SOURCE',
                'P_ITEM' => $request -> ITEM,
                'P_RECORD_SYS_ID' => $request -> RECORD_SYS_ID,        
        ];
        $response = DB::select("call USP_GET_ALL_MASTER_DATA(:P_OPERATION_NAME, :P_ITEM, :P_RECORD_SYS_ID, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }
    //api-post-add-update-lead-source
    //post-add-update-lead-source
    function PostAddUpdateLeadSource(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        // CALL USP_POST_ALL_MASTER_DATA('ADD_UPDATE_LEAD_SOURCE','{"ITEM":"ADD","RECORD_SYS_ID":"0","LEAD_SOURCE_NAME":"Small 2","CREATED_BY":"1"}',@ERRNO,@ERRMSG);
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_LEAD_SOURCE',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_ALL_MASTER_DATA(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }
    //api-get-view-out-tray
    //view-out-tray
    function GetViewOutTray(Request $request)
    {
        //CALL USP_GET_OUT_TRAY_ACTIVITY('VIEW_OUT_TRAY_DETAILS','VIEW_ALL',@ERRNO,@ERRMSG);
        $payload = [
                   'P_OPERATION_NAME' => 'VIEW_OUT_TRAY_DETAILS',
                   'P_ITEM' => $request -> ITEM,
        ];
        $response = DB::select("call USP_GET_OUT_TRAY_ACTIVITY(:P_OPERATION_NAME,:P_ITEM, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }
    
     //api-post-add-update-project-documents-box-integration
    //add-update-project-documents-box-integration
    function PostaddupdateprojectdocumentsboxIntegration(Request $request) 
    {
        $requestPostJson= json_decode(json_encode($request ->getContent()));
        //CALL USP_POST_PROJECT_DOCUMENTS_BOX_INTEGRATION_ACTIVITY('ADD_UPDATE_PROJECT_DOCUMENTS_BOX_INTEGRATION', '{"UPLOADED_FILE":"Prizes Template (1) (1).xlsx","RECORD_SYS_ID":"0","DOCUMENT_TITLE":"Test Doc","ITEM":"ADD","PROJECT_NUMBER":"Pro-SA-25-09-059","MESSAGE":"Test Doc uploaded","CREAT
        $payload =[
            'P_OPERATION_NAME' => 'ADD_UPDATE_PROJECT_DOCUMENTS_BOX_INTEGRATION',
            'P_JSON'=>$requestPostJson,
        ];
        $response = DB::select("call USP_POST_PROJECT_DOCUMENTS_BOX_INTEGRATION_ACTIVITY(:P_OPERATION_NAME, :P_JSON, @ERRNO,@ERRMSG)", $payload);
        if(!empty($response))
        {
            $response_decoded = json_decode($response[0]->JSON_VALUE, true);
            return $response_decoded;
        }
        else
        {
            $response_data = [ 'status' => 'false', 'response' => 'opps!! something went wrong',];
            return $response_data;
        }
    }
}