Generate and Verify OTP

 Function GenerateAndSaveOTP(&type As string, &mobOrEmail As string, &nationalId As string) Returns integer

   Local number &randomNum;

   Local datetime &currentTime;

   Local SQL &sql;

   Local number &secs;

   Local string &oprid;

   Local number &expireLimitSec = 120;

   /* Get the current user ID and timestamp */

   &oprid = &nationalId;

   &currentTime = %Datetime;

   

   /* Check if an OTP was generated within the last 60 seconds for this user */

   SQLExec("SELECT NVL((SYSDATE - CAST(DATETIME1 AS DATE)) * 86400, 0) AS SECONDS_DIFF FROM PS_T_OTP_SESSIONS WHERE NATIONAL_ID = :1 AND T_TYPE=:2 AND T_MOB_EMAIL=:3", &oprid, &type, &mobOrEmail, &secs);

   

   rem  MessageBox(0, "", 0, 0, "" | &secs);

   If &secs > 0 And

         &secs <= &expireLimitSec Then

      /* OTP is still valid within the last 60 seconds */

      rem MessageBox(0, "", 0, 0, "Please try again after one minute. Your current OTP is still valid.");

      Return 0;

   End-If;

   

   /* Generate a new random OTP between 1000 and 9999 */

   &randomNum = Round((Rand() * 8999) + 1000, 0);

   

   /* Delete any previous OTP for the user */

   rem SQLExec("DELETE FROM PS_T_OTP_SESSIONS WHERE NATIONAL_ID = :1 AND T_TYPE=:2", &oprid, &type);

   SQLExec("DELETE FROM PS_T_OTP_SESSIONS WHERE ((NATIONAL_ID = :1 AND T_TYPE=:2) OR NVL((SYSDATE - CAST(DATETIME1 AS DATE)) * 86400, 0)>:3)", &oprid, &type, &expireLimitSec);

   

   /* Insert the new OTP and current timestamp into the table */

   SQLExec("INSERT INTO PS_T_OTP_SESSIONS (NATIONAL_ID, T_OTP, DATETIME1,T_TYPE,T_MOB_EMAIL) VALUES (:1, :2, :3,:4,:5)", &oprid, &randomNum, &currentTime, &type, &mobOrEmail);

   CommitWork();

   /* Return the newly generated OTP */

   Return &randomNum;

End-Function;

Function VerifyOTP(&type As string, &nationalId As string, &mobOrEmail As string, &inputOTP As number) Returns boolean

   Local string &oprid;

   Local number &secs;

   Local SQL &sql;

   

   /* Get the current user ID */

   &oprid = &nationalId;

   

   /* Retrieve the time difference in seconds if the OTP matches */

   SQLExec("SELECT NVL((SYSDATE - CAST(DATETIME1 AS DATE)) * 86400, 0) AS SECONDS_DIFF FROM PS_T_OTP_SESSIONS WHERE NATIONAL_ID = :1 AND T_OTP = :2 AND T_TYPE=:3 AND T_MOB_EMAIL=:4", &oprid, &inputOTP, &type, &mobOrEmail, &secs);

   

   /* Check if OTP exists and is within the valid time period */

   If &secs > 0 And

         &secs < 120 Then

      /* OTP is valid, so delete it after verification */

      rem SQLExec("DELETE FROM PS_T_OTP_SESSIONS WHERE NATIONAL_ID = :1", &oprid);

      rem CommitWork();

      Return True; /* OTP verified successfully */

   Else

      /* OTP is invalid or expired */

      Return False;

   End-If;

End-Function;

Function deleteOtpsAfterVerification(&nationalId As string)

   Local string &oprid = &nationalId;

   

   SQLExec("DELETE FROM PS_T_OTP_SESSIONS WHERE NATIONAL_ID = :1", &oprid);

   CommitWork();

End-Function;