Untitled

CREATE DEFINER=`rroott`@`%` PROCEDURE `dragonfiatlas`.`spUpdateVerifiedClientInfo`(
  IN varClientId INT, 
  IN varIsPrimary TINYINT, 
  IN varIdTypeId INT, 
  IN varIdIssueDate DATETIME, 
  IN varIdExpiryDate DATETIME,
  IN varIdFrontPath VARCHAR(255),
  IN varIdBackPath VARCHAR(255),
  IN varSelfiePath VARCHAR(255),
  IN varDateUpdated DATETIME,
  IN varDataSourceId INT,
  IN varModifiedBy VARCHAR(50)
)
BEGIN
	
  DECLARE sql_error_message TEXT;

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
	  
    ROLLBACK;

    SET sql_error_message = 'An error occurred during spUpdateVerifiedClientInfo execution.';

    INSERT INTO dragonfiexceptionlogs.`DBLogs` (
      `Event`, `Failed`, `Date`, `ErrorMsg`, `varId`
    ) VALUES (
      'spUpdateVerifiedClientInfo', 1, NOW(), sql_error_message, 
      CONCAT(varClientId, ',', varIsPrimary, '-', varIdTypeId, '-', varIdIssueDate, '-', varIdExpiryDate, '-', varDataSourceId)
    );

    SELECT 0 AS 'Return', sql_error_message AS 'ReturnMessage';
  END;

  START TRANSACTION;

  -- Conditional updates based on varDataSourceId
  IF varDataSourceId = 3 THEN
    UPDATE apollodashboard.ClientPersonalInfo 
    SET 
        IdTypeId = varIdTypeId,
        IdIssueDate = varIdIssueDate,
        IdExpiryDate = varIdExpiryDate,
        IdFrontPath = varIdFrontPath,
        IdBackPath = varIdBackPath,
        SelfiePath = varSelfiePath,
        DateUpdated = varDateUpdated,
        ModifiedBy = varModifiedBy
    WHERE 
        ClientId = varClientId 
        AND IsPrimary = varIsPrimary;
  ELSEIF varDataSourceId = 2 THEN
    UPDATE hermesdashboard.ClientPersonalInfo 
    SET 
        IdTypeId = varIdTypeId,
        IdIssueDate = varIdIssueDate,
        IdExpiryDate = varIdExpiryDate,
        IdFrontPath = varIdFrontPath,
        IdBackPath = varIdBackPath,
        SelfiePath = varSelfiePath,
        DateUpdated = varDateUpdated,
        ModifiedBy = varModifiedBy
    WHERE 
        ClientId = varClientId 
        AND IsPrimary = varIsPrimary;
  ELSEIF varDataSourceId = 1 THEN
    UPDATE dragonfidashboard.ClientPersonalInfo 
    SET 
        IdTypeId = varIdTypeId,
        IdIssueDate = varIdIssueDate,
        IdExpiryDate = varIdExpiryDate,
        IdFrontPath = varIdFrontPath,
        IdBackPath = varIdBackPath,
        SelfiePath = varSelfiePath,
        DateUpdated = varDateUpdated,
        ModifiedBy = varModifiedBy
    WHERE 
        ClientId = varClientId 
        AND IsPrimary = varIsPrimary;
  END IF;

  COMMIT;

END