pears.NetCandidateDiaryActionSave

CREATE PROCEDURE "pears"."NetCandidateDiaryActionSave"( IN "pWebUserID" CHAR(20),IN "pActions" long VARCHAR ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "i" SMALLINT;
  DECLARE "s" CHAR(100);
  DECLARE "sAction" CHAR(20);
  SET "pActions" = "trim"("isnull"("pActions",''));
  "looplabel": loop
    IF "pActions" = '' THEN
      leave "looplabel"
    END IF;
    SET "i" = "charindex"(',',"pActions");
    IF "i" = 0 THEN
      SET "s" = "pActions";
      SET "pActions" = ''
    ELSE
      SET "s" = "trim"("left"("pActions","i"-1));
      SET "pActions" = "trim"("right"("pActions","length"("pActions")-"i"))
    END IF;
    IF "s" LIKE 'Shift%' THEN
      SET "s" = "right"("s","length"("s")-6);
      SET "i" = "charindex"('=',"s");
      SET "sAction" = "right"("s","length"("s")-"i");
      SET "s" = "left"("s","i"-1);
      IF "saction" = 'Delete' THEN
        DELETE FROM "tempshift" WHERE "tempshiftid" = "s" AND "personid" = any(SELECT "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID") AND "state" IN( 'A','U','H' ) 
      ELSE IF "saction" = 'Confirm' THEN
          UPDATE "tempshift" SET "tempconfirmed" = 1 WHERE "tempshiftid" = "s" AND "personid" = any(SELECT "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID") AND "state" IN( 'P','C' ) ;
          CALL "IQXNetRequestAction"("pWebUserID",'CandidateConfirm','SHIFT',"s")
        ELSE IF "saction" = 'Reject' THEN
            UPDATE "tempshift" SET "state" = 'C',"whencancelled" = CURRENT TIMESTAMP,"whocancelled" = "userstaffid","crefill" = 1,"clientconfirmed" = 0,"tempconfirmed" = 1,"cancelreason" = 'T'
              WHERE "tempshiftid" = "s" AND "personid" = any(SELECT "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID") AND "state" = 'P';
            CALL "IQXNetRequestAction"("pWebUserID",'CandidateReject','SHIFT',"s")
          ELSE IF "saction" = 'Acknowledge' THEN
              UPDATE "tempshift" SET "tempconfirmed" = 1
                WHERE "tempshiftid" = "s" AND "personid" = any(SELECT "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID") AND "state" = 'C'
            END IF
          END IF
        END IF
      END IF
    END IF
  END loop "looplabel";
  SELECT '0:~Success'
END /* DOC
2018-10-10 MHS IW-456 multi-cand fix. Appears not currently in use so won't do tests
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateDiaryActionSave" IS 
{CREATE PROCEDURE pears."NetCandidateDiaryActionSave"( IN "pWebUserID" CHAR(20),IN "pActions" long VARCHAR ) 
RESULT( "pResult" CHAR(250) ) 
BEGIN
  DECLARE "i" SMALLINT;
  DECLARE "s" CHAR(100);
  DECLARE "sAction" CHAR(20);
  SET "pActions" = "trim"("isnull"("pActions",''));
  "looplabel": loop
    IF "pActions" = '' THEN
      leave "looplabel"
    END IF;
    SET "i" = "charindex"(',',"pActions");
    IF "i" = 0 THEN
      SET "s" = "pActions";
      SET "pActions" = ''
    ELSE
      SET "s" = "trim"("left"("pActions","i"-1));
      SET "pActions" = "trim"("right"("pActions","length"("pActions")-"i"))
    END IF;
    IF "s" LIKE 'Shift%' THEN
      SET "s" = "right"("s","length"("s")-6);
      SET "i" = "charindex"('=',"s");
      SET "sAction" = "right"("s","length"("s")-"i");
      SET "s" = "left"("s","i"-1);
      IF "saction" = 'Delete' THEN
        DELETE FROM "tempshift" WHERE "tempshiftid" = "s" AND "personid" IN (SELECT personid FROM iqxnetuserlink WHERE iqxnetuserid=pWebUserID) AND "state" IN( 'A','U','H' ) 
      ELSE IF "saction" = 'Confirm' THEN
          UPDATE "tempshift" SET "tempconfirmed" = 1 WHERE "tempshiftid" = "s" AND "personid" IN (SELECT personid FROM iqxnetuserlink WHERE iqxnetuserid=pWebUserID) AND "state" IN( 'P','C' ) ;
          CALL "IQXNetRequestAction"("pWebUserID",'CandidateConfirm','SHIFT',"s")
        ELSE IF "saction" = 'Reject' THEN
            UPDATE "tempshift" SET "state" = 'C',"whencancelled" = CURRENT TIMESTAMP,"whocancelled" = "userstaffid","crefill" = 1,"clientconfirmed" = 0,"tempconfirmed" = 1,"cancelreason" = 'T'
              WHERE "tempshiftid" = "s" AND "personid" IN (SELECT personid FROM iqxnetuserlink WHERE iqxnetuserid=pWebUserID) AND "state" = 'P';
            CALL "IQXNetRequestAction"("pWebUserID",'CandidateReject','SHIFT',"s")
          ELSE IF "saction" = 'Acknowledge' THEN
              UPDATE "tempshift" SET "tempconfirmed" = 1
                WHERE "tempshiftid" = "s" AND "personid" IN (SELECT personid FROM iqxnetuserlink WHERE iqxnetuserid=pWebUserID) AND "state" = 'C'
            END IF
          END IF
        END IF
      END IF
    END IF
  END loop "looplabel";
  SELECT '0:~Success'
END
 
 /* DOC
2018-10-10 MHS IW-456 multi-cand fix. Appears not currently in use so won't do tests
*/
}