IQX Quick Menu

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
CREATE PROCEDURE "pears"."NetWizardGetPage"( IN "pWebUserID" CHAR(20) DEFAULT NULL,IN "pWizardID" CHAR(20) DEFAULT NULL,IN "pPath" long VARCHAR DEFAULT NULL,IN "pAction" CHAR(20) ) 
RESULT( "pageID" CHAR(50),"pageCaption" CHAR(250),"pageFooter" CHAR(250),"hasCheckBox" SMALLINT,"isLastPage" SMALLINT,"nextButtonCaption" CHAR(100),"backButtonCaption" CHAR(100),"pageNumber" SMALLINT,"pageCount" SMALLINT,"path" long VARCHAR,"divisionEtc" CHAR(100) ) 
BEGIN
  DECLARE "sID" CHAR(50);
  DECLARE "sPath" long VARCHAR;
  DECLARE "i" SMALLINT;
  DECLARE "iFirst" SMALLINT;
  DECLARE "iLast" SMALLINT;
  DECLARE "sFooter" CHAR(250);
  DECLARE "iCheckbox" SMALLINT;
  // pAction values- START, resume, back, NEXT
  IF "isnull"("pWebUserID",'') = '' THEN
    SET "pWebUserID" = NULL
  END IF;
  IF "isnull"("pWizardID",'') = '' THEN
    SET "pWizardID" = 'DEFAULT'
  END IF;
  IF "pAction" = 'start' THEN
    SET "sID" = "SoftWizardController"("pWizardID",NULL,'nextPageID');
    IF "sID" IS NOT NULL THEN
      SET "sID" = "string"(':',"sID");
      SET "sPath" = "string"('/',"sID")
    END IF
  ELSE IF "pAction" = 'resume' THEN
      SET "sPath" = (SELECT "path" FROM "IQXNetWizardState" WHERE "IQXNetUserID" = "pWebUserID" AND "WizardID" = "pWizardID");
      SET "i" = "locate"("sPath",'/',-1);
      IF "isnull"("i",0) > 0 THEN
        SET "sID" = "substring"("sPath","i"+1)
      ELSE // No saved state - behave AS START
        SET "sID" = "SoftWizardController"("pWizardID",NULL,'nextPageID');
        IF "sID" IS NOT NULL THEN
          SET "sID" = "string"(':',"sID");
          SET "sPath" = "string"('/',"sID")
        END IF
      END IF
    ELSE IF "pAction" = 'next' THEN
        SET "i" = "locate"("pPath",'/',-1);
        IF "isnull"("i",0) > 0 THEN
          SET "sID" = "substring"("pPath","i"+1);
          SET "sID" = "SoftWizardController"("pWizardID","replace"("sID",':',''),'nextPageID');
          IF "sID" IS NOT NULL THEN
            SET "sPath" = "string"("pPath",'/',"sID")
          END IF
        END IF
      ELSE IF "pAction" = 'back' THEN
          SET "i" = "locate"("pPath",'/',-1);
          IF "isnull"("i",0) > 0 THEN
            SET "sPath" = "substring"("pPath",1,"i"-1);
            SET "i" = "locate"("sPath",'/',-1);
            IF "isnull"("i",0) > 0 THEN
              SET "sID" = "substring"("sPath","i"+1)
            END IF
          END IF
        END IF
      END IF END IF END IF;
  SET "sID" = "isnull"("sID",'');
  SET "iFirst" = IF "left"("sID",1) = ':' THEN 1 ELSE 0 endif;
  SET "iLast" = IF "right"("sID",1) = ':' THEN 1 ELSE 0 endif;
  SET "sID" = "replace"("sID",':','');
  IF "sID" = '' THEN
    SELECT '',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
  ELSE
    IF "pWebUserID" IS NOT NULL THEN
      INSERT INTO "IQXNetWizardState"( "WizardID","IQXNetUserID","Path" ) ON existing UPDATE defaults off VALUES( "pWizardID","pWebUserID","sPath" ) 
    END IF;
    SET "sFooter" = "SoftWizardController"("pWizardID","sID",'pageFooter');
    IF "left"("sFooter",1) = '!' THEN
      SET "iCheckbox" = 1;
      SET "sFooter" = "stuff"("sFooter",1,1,'')
    ELSE
      SET "iCheckbox" = 0
    END IF;
    SELECT "sID",
      "SoftWizardController"("pWizardID","sID",'pageCaption'),
      "sFooter",
      "iCheckbox",
      "iLast",
      IF "iLast" = 1 THEN "isnull"("SoftWizardController"("pWizardID","sID",'finishButtonCaption'),'Finish') ELSE "isnull"("SoftWizardController"("pWizardID","sID",'nextButtonCaption'),'Next') endif,
      IF "iFirst" = 1 THEN NULL ELSE "isnull"("SoftWizardController"("pWizardID","sID",'backButtonCaption'),'Back') endif,
      CAST("isnull"("SoftWizardController"("pWizardID","sID",'pageNumber'),'0') AS SMALLINT),
      CAST("isnull"("SoftWizardController"("pWizardID","sID",'totalPageCount'),'0') AS SMALLINT),
      "isnull"("sPath",''),"isnull"("SoftWizardController"("pWizardID","sID",'division'),'')
  END IF
END /* DOC
2019-03-20 MHS. Controlling proc for delivery of soft wizard mechanism.
*/
/* TEST
select *,ifnull(backButtonCaption,'null','notnull') as bb from NetWizardGetPage('TEST.CANDIDATE', 'TESTWIZARD', '/page1/page2/page3:/page4', 'back');
expect pageID=page3, path=/page1/page2/page3:, isLastPage=1, bb=notnull;
select *,ifnull(backButtonCaption,'null','notnull') as bb from NetWizardGetPage('TEST.CANDIDATE', 'TESTWIZARD', '/page1/page2/:page3/page4', 'back');
expect pageID=page3, path=/page1/page2/:page3, isLastPage=0, bb=null;
select * from NetWizardGetPage('TEST.CANDIDATE', 'TESTWIZARD','', 'resume');
expect pageID=page3, path=/page1/page2/:page3;
select *,string(pageID,'X') as p1, string(path,'Y') as p2 from NetWizardGetPage('TEST.CANDIDATE', 'TESTWIZARD', '/page1', 'back');
expect p1=X, p2=Y;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetWizardGetPage" IS 
{CREATE PROCEDURE pears."NetWizardGetPage"(IN pWebUserID CHAR(20) DEFAULT NULL, IN pWizardID CHAR(20) DEFAULT NULL, IN pPath long VARCHAR DEFAULT NULL, IN pAction CHAR(20) )
RESULT(pageID CHAR(50), pageCaption CHAR(250), pageFooter CHAR(250), hasCheckBox SMALLINT, isLastPage SMALLINT, nextButtonCaption CHAR(100), backButtonCaption CHAR(100), pageNumber SMALLINT, pageCount SMALLINT, path long VARCHAR, divisionEtc CHAR(100) ) 
BEGIN
	  DECLARE sID CHAR(50);
    DECLARE sPath long VARCHAR;
    DECLARE i SMALLINT;
    DECLARE iFirst SMALLINT;
    DECLARE iLast SMALLINT;
    DECLARE sFooter CHAR(250);
    DECLARE iCheckbox SMALLINT;
    // pAction values- START, resume, back, NEXT
    IF isnull(pWebUserID,'')='' THEN
       SET pWebUserID=NULL
    END IF;
    IF isnull(pWizardID,'')='' THEN
       SET pWizardID='DEFAULT'
    END IF;
    IF pAction='start' THEN 
       SET sID = SoftWizardController(pWizardID, NULL, 'nextPageID');
       IF sID IS NOT NULL THEN
          SET sID=string(':',sID);
          SET sPath = string('/',sID);
       END IF;
    ELSE IF pAction='resume' THEN 
       SET sPath=(SELECT path FROM IQXNetWizardState WHERE IQXNetUserID=pWebUserID AND WizardID=pWizardID);
       SET i=locate(sPath,'/',-1);
       IF isnull(i,0)>0 THEN
          SET sID=SUBSTRING(sPath,i+1);
       ELSE // No saved state - behave AS START
          SET sID = SoftWizardController(pWizardID, NULL, 'nextPageID');
          IF sID IS NOT NULL THEN
            SET sID=string(':',sID);
            SET sPath = string('/',sID);
          END IF;
       END IF;
    ELSE IF pAction='next' THEN
        SET i=locate(pPath,'/',-1);
        IF isnull(i,0)>0 THEN
            SET sID=SUBSTRING(pPath,i+1);
            SET sID=SoftWizardController(pWizardID, REPLACE(sID,':',''), 'nextPageID');
            IF sID IS NOT NULL THEN
               SET sPath=string(pPath,'/',sID);
            END IF;
        END IF;
    ELSE IF pAction='back' THEN
        SET i=locate(pPath,'/',-1);
        IF isnull(i,0)>0 THEN
            SET sPath=SUBSTRING(pPath,1,i-1);
            SET i=locate(sPath,'/',-1);
            IF isnull(i,0)>0 THEN
                SET sID=SUBSTRING(sPath,i+1);
            END IF;
        END IF;
    END IF; 
    END IF; 
    END IF;
    END IF;
    SET sID=isnull(sID,'');
    SET iFirst=IF LEFT(sID,1)=':' THEN 1 ELSE 0 endif;
    SET iLast=IF RIGHT(sID,1)=':' THEN 1 ELSE 0 endif;
    SET sID=REPLACE(sID,':','');
    IF sID='' THEN
       SELECT '', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL 
    ELSE
       IF pWebUserID IS NOT NULL THEN
          INSERT INTO IQXNetWizardState (WizardID,IQXNetUserID,Path) ON existing UPDATE defaults off VALUES (pWizardID,pWebUserID,sPath);
       END IF;
       SET sFooter=SoftWizardController(pWizardID,sID,'pageFooter');
       IF LEFT(sFooter,1)='!' THEN
          SET iCheckbox=1;
          SET sFooter=stuff(sFooter,1,1,'');
       ELSE
          SET iCheckbox=0;
       END IF;
       SELECT sID,
              SoftWizardController(pWizardID,sID,'pageCaption'),
              sFooter,
              iCheckbox,
              iLast,
              IF iLast=1 THEN isnull(SoftWizardController(pWizardID,sID,'finishButtonCaption'),'Finish') ELSE isnull(SoftWizardController(pWizardID,sID,'nextButtonCaption'),'Next') endif,
              IF iFirst=1 THEN NULL ELSE isnull(SoftWizardController(pWizardID,sID,'backButtonCaption'),'Back') endif,
              CAST(isnull(SoftWizardController(pWizardID,sID,'pageNumber'),'0') AS SMALLINT),
              CAST(isnull(SoftWizardController(pWizardID,sID,'totalPageCount'),'0') AS SMALLINT),
              isnull(sPath,''), isnull(SoftWizardController(pWizardID,sID,'division'),'');
    END IF;
END
 
/* DOC
2019-03-20 MHS. Controlling proc for delivery of soft wizard mechanism.
*/
 
/* TEST
select *,ifnull(backButtonCaption,'null','notnull') as bb from NetWizardGetPage('TEST.CANDIDATE', 'TESTWIZARD', '/page1/page2/page3:/page4', 'back');
expect pageID=page3, path=/page1/page2/page3:, isLastPage=1, bb=notnull;
select *,ifnull(backButtonCaption,'null','notnull') as bb from NetWizardGetPage('TEST.CANDIDATE', 'TESTWIZARD', '/page1/page2/:page3/page4', 'back');
expect pageID=page3, path=/page1/page2/:page3, isLastPage=0, bb=null;
select * from NetWizardGetPage('TEST.CANDIDATE', 'TESTWIZARD','', 'resume');
expect pageID=page3, path=/page1/page2/:page3;
select *,string(pageID,'X') as p1, string(path,'Y') as p2 from NetWizardGetPage('TEST.CANDIDATE', 'TESTWIZARD', '/page1', 'back');
expect p1=X, p2=Y;
*/
}
pears.NetWizardGetPage

Original SQL

iqx
