pears.NetQuestionnaire

CREATE PROCEDURE "pears"."NetQuestionnaire"( IN "pWebUserID" CHAR(20),IN "ptaglocation" CHAR(3),IN "pid" CHAR(20) DEFAULT NULL,IN "pgroup" SMALLINT DEFAULT NULL ) 
RESULT( "tagsortorder" SMALLINT,"tagid" CHAR(3),"sortX" SMALLINT,"choicesortorder" SMALLINT,"tagchoiceid" CHAR(4),"rectype" SMALLINT,"description" CHAR(100),"tagtype" CHAR(1),"minstep" DOUBLE,"units" CHAR(10),"required" SMALLINT,"displaygroup" SMALLINT,"value" DOUBLE,"textvalue" long VARCHAR,"taglocation" CHAR(3),"tagchoiceparentid" CHAR(4) ) 
// IQXWeb
BEGIN
  DECLARE "ispublic" SMALLINT;
  DECLARE "divid" CHAR(20);
  DECLARE "userclass" CHAR(20);
  IF "pid" IS NULL THEN
    SET "ispublic" = 0;
    IF "ptaglocation" LIKE '[PA]%' THEN
      SET "pid" = (SELECT FIRST "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid")
    ELSE IF "ptaglocation" = 'E' THEN
        SET "pid" = (SELECT FIRST "employmentid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid")
      ELSE IF "ptaglocation" = 'C' THEN
          SET "pid" = (SELECT FIRST "employment"."companyid" FROM "iqxnetuserlink" KEY JOIN "employment" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid")
        END IF
      END IF
    END IF
  ELSE SET "userclass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pwebuserid");
    IF "userclass" = 'OWNER' THEN
      SET "ispublic" = 0; -- Owner rights to all record types but they only see the data if they own the record
      SET "DivID" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pWebUserID");
      IF "ptaglocation" LIKE '[PA]%' THEN
        SET "pid" = (SELECT FIRST "personid" FROM "person" WHERE "personid" = "pid" AND "divisionid" = "divid")
      ELSE IF "ptaglocation" = 'E' THEN
          SET "pid" = (SELECT FIRST "e"."employmentid" FROM "employment" AS "e" KEY JOIN "company" AS "c" WHERE "e"."employmentid" = "pid" AND "c"."divisionid" = "divid")
        ELSE IF "ptaglocation" = 'C' THEN
            SET "pid" = (SELECT FIRST "companyid" FROM "company" WHERE "companyid" = "pid" AND "divisionid" = "divid")
          END IF
        END IF -- Read only access to web view questions only
      END IF
    ELSE SET "ispublic" = 1
    END IF END IF;
  SELECT "tag"."sortorder","tag"."tagid",0 AS "sortX",CAST(NULL AS SMALLINT),CAST(NULL AS CHAR(4)),0 AS "rectype","isnull"("tag"."longdescription","tag"."description"),"tag"."tagtype","tag"."minstep","tag"."units",
    "tag"."required","isnull"("tag"."displaygroup",0) AS "displaygroup",CAST(NULL AS DOUBLE),CAST(NULL AS long VARCHAR),"tag"."taglocation",CAST(NULL AS CHAR(4))
    FROM "tag" WHERE "tag"."taglocation" = "ptaglocation" AND(IF "ispublic" = 1 THEN "tag"."publiconweb" ELSE "tag"."publishtoweb" endif)
     = 1 AND("isnull"("pgroup",0) = 0 OR "displaygroup" = "pgroup") UNION ALL
  SELECT "tag"."sortorder","tag"."tagid",3-("isnull"("tagchoice"."subchoice",0)+1) AS "sortX","tagchoice"."sortorder","tagchoice"."tagchoiceid","isnull"("tagchoice"."subchoice",0)+1 AS "rectype",
    "isnull"("tagchoice"."longdescription","tagchoice"."description"),NULL,NULL,NULL,NULL,"isnull"("tag"."displaygroup",0) AS "displaygroup","tagchoice"."value",CAST(NULL AS long VARCHAR),NULL,"tagchoice"."tagchoiceparentid"
    FROM "tagchoice" KEY JOIN "tag" WHERE "tag"."taglocation" = "ptaglocation" AND(IF "ispublic" = 1 THEN "tag"."publiconweb" ELSE "tag"."publishtoweb" endif)
     = 1 AND "isnull"("tagchoice"."donotpublishtoweb",0) = 0
    AND("isnull"("pgroup",0) = 0 OR "displaygroup" = "pgroup") UNION ALL
  SELECT "tag"."sortorder","tag"."tagid",2 AS "sortX","tagchoice"."sortorder","tagchoice"."tagchoiceid",3 AS "rectype",NULL,NULL,NULL,NULL,NULL,
    "isnull"("tag"."displaygroup",0) AS "displaygroup","tagvalue"."value","tagvalue"."textvalue",NULL,NULL
    FROM "tagvalue" KEY JOIN "tag","tagvalue" KEY LEFT OUTER JOIN "tagchoice"
    WHERE "tagvalue"."id" = "pid" AND "tag"."taglocation" = "ptaglocation" AND(IF "ispublic" = 1 THEN "tag"."publiconweb" ELSE "tag"."publishtoweb" endif)
     = 1 AND "isnull"("tagchoice"."donotpublishtoweb",0) = 0
    AND("isnull"("pgroup",0) = 0 OR "displaygroup" = "pgroup")
    ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC,6 ASC
END /* DOC
Summer 15 Added support for submenu questions - tagchoiceparentid
Summer 15 Added long text for sub choices
20120907 modified to insert long text for question title. retrieve from LongDescription
sortX=0 for tags, 1 for subchoices, 2 for choices and values
rectype=0 for tags, 1 for choices, 2 for subchoices, 3 for values
2017-06-21 PC Test and doc
2018-06-06 MHS removed tests since failing and this proc deprecated in favour of NetQuestionnaire2
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetQuestionnaire" IS 
{CREATE PROCEDURE pears."NetQuestionnaire"(IN pWebUserID CHAR(20),IN ptaglocation CHAR(3),IN pid CHAR(20) DEFAULT NULL,IN pgroup SMALLINT DEFAULT NULL)
RESULT(tagsortorder SMALLINT,tagid CHAR(3),sortX SMALLINT,choicesortorder SMALLINT,tagchoiceid CHAR(4),rectype SMALLINT,description CHAR(100),tagtype CHAR(1),minstep DOUBLE,units CHAR(10),required SMALLINT,displaygroup SMALLINT,VALUE DOUBLE,textvalue long VARCHAR,taglocation CHAR(3),tagchoiceparentid CHAR(4))
// IQXWeb
BEGIN
  DECLARE ispublic SMALLINT;
  DECLARE divid CHAR(20);
  DECLARE userclass CHAR(20);
  IF pid IS NULL THEN
    SET ispublic=0;
    IF ptaglocation LIKE '[PA]%' THEN
      SET pid=(SELECT FIRST personid FROM iqxnetuserlink WHERE iqxnetuserid = pwebuserid)
    ELSE IF ptaglocation = 'E' THEN
        SET pid=(SELECT FIRST employmentid FROM iqxnetuserlink WHERE iqxnetuserid = pwebuserid)
      ELSE IF ptaglocation = 'C' THEN
          SET pid=(SELECT FIRST employment.companyid FROM iqxnetuserlink KEY JOIN employment WHERE iqxnetuserlink.iqxnetuserid = pwebuserid)
        END IF
      END IF
    END IF
  ELSE
    SET userclass=(SELECT FIRST iqxnetuserclassid FROM iqxnetuser WHERE iqxnetuserid = pwebuserid);
    IF userclass = 'OWNER' THEN
      SET ispublic=0; -- Owner rights to all record types but they only see the data if they own the record
      SET DivID=(SELECT FIRST staff.divisionid FROM iqxnetuser KEY JOIN staff WHERE iqxnetuser.iqxnetuserid = pWebUserID);
      IF ptaglocation LIKE '[PA]%' THEN
        SET pid=(SELECT FIRST personid FROM person WHERE personid = pid AND divisionid = divid)
      ELSE IF ptaglocation = 'E' THEN
          SET pid=(SELECT FIRST e.employmentid FROM employment AS e KEY JOIN company AS c WHERE e.employmentid = pid AND c.divisionid = divid)
        ELSE IF ptaglocation = 'C' THEN
            SET pid=(SELECT FIRST companyid FROM company WHERE companyid = pid AND divisionid = divid)
          END IF
        END IF
      END IF
    ELSE
      SET ispublic=1 -- Read only access to web view questions only
    END IF
  END IF;
  SELECT tag.sortorder,tag.tagid,0 AS sortX,CAST(NULL AS SMALLINT),CAST(NULL AS CHAR(4)),0 AS rectype,isnull(tag.longdescription,tag.description),tag.tagtype,tag.minstep,tag.units,
    tag.required,isnull(tag.displaygroup,0) AS displaygroup,CAST(NULL AS DOUBLE),CAST(NULL AS long VARCHAR),tag.taglocation,CAST(NULL AS CHAR(4))
    FROM tag WHERE tag.taglocation = ptaglocation AND(IF ispublic = 1 THEN tag.publiconweb ELSE tag.publishtoweb
    endif) = 1 AND(isnull(pgroup,0) = 0 OR displaygroup = pgroup) UNION ALL
  SELECT tag.sortorder,tag.tagid,3-(isnull(tagchoice.subchoice,0)+1) AS sortX,tagchoice.sortorder,tagchoice.tagchoiceid,isnull(tagchoice.subchoice,0)+1 AS rectype,
    isnull(tagchoice.longdescription,tagchoice.description),NULL,NULL,NULL,NULL,isnull(tag.displaygroup,0) AS displaygroup,tagchoice.value,CAST(NULL AS long VARCHAR),NULL,tagchoice.tagchoiceparentid
    FROM tagchoice KEY JOIN tag WHERE tag.taglocation = ptaglocation AND(IF ispublic = 1 THEN tag.publiconweb ELSE tag.publishtoweb
    endif) = 1 AND isnull(tagchoice.donotpublishtoweb,0) = 0
    AND(isnull(pgroup,0) = 0 OR displaygroup = pgroup) UNION ALL
  SELECT tag.sortorder,tag.tagid,2 AS sortX,tagchoice.sortorder,tagchoice.tagchoiceid,3 AS rectype,NULL,NULL,NULL,NULL,NULL,
    isnull(tag.displaygroup,0) AS displaygroup,tagvalue.value,tagvalue.textvalue,NULL,NULL
    FROM tagvalue KEY JOIN tag,tagvalue KEY LEFT OUTER JOIN tagchoice
    WHERE tagvalue.id = pid AND tag.taglocation = ptaglocation AND(IF ispublic = 1 THEN tag.publiconweb ELSE tag.publishtoweb
    endif) = 1 AND isnull(tagchoice.donotpublishtoweb,0) = 0
    AND(isnull(pgroup,0) = 0 OR displaygroup = pgroup) ORDER BY
    1 ASC,2 ASC,3 ASC,4 ASC,5 ASC,6 ASC
END
 
/* DOC
    Summer 15 Added support for submenu questions - tagchoiceparentid
    Summer 15 Added long text for sub choices
    20120907 modified to insert long text for question title. retrieve from LongDescription
    sortX=0 for tags, 1 for subchoices, 2 for choices and values
    rectype=0 for tags, 1 for choices, 2 for subchoices, 3 for values
    2017-06-21 PC Test and doc
    2018-06-06 MHS removed tests since failing and this proc deprecated in favour of NetQuestionnaire2
*/
}