Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
CREATE PROCEDURE "pears"."NetQuestionnaire2"( IN "pWebUserID" CHAR(20),IN "ptaglocation" CHAR(3),IN "pid" CHAR(20) DEFAULT NULL,IN "pgroup" CHAR(25) DEFAULT NULL,IN "pdoctype" CHAR(25) DEFAULT NULL ) RESULT( "tagsortorder" SMALLINT,"taglocation" CHAR(3),"tagid" CHAR(3),"sortX" SMALLINT,"choicesortorder" SMALLINT,"tagchoiceid" CHAR(4),"rectype" SMALLINT,"description" long VARCHAR,"tagtype" CHAR(1),"minstep" DOUBLE,"units" CHAR(10),"required" SMALLINT,"displaygroup" SMALLINT,"value" DOUBLE,"textvalue" long VARCHAR,"tagchoiceparentid" CHAR(4),"readonly" SMALLINT ) // IQXWeb BEGIN DECLARE "ispublic" SMALLINT; DECLARE "userclass" CHAR(20); IF "pid" IS NULL THEN SET "ispublic" = 0; IF "ptaglocation" LIKE '[PA]%' THEN SET "pid" = "NetCandidateValidPersonID"("pWebUserID","pid") ELSE IF "ptaglocation" = 'E' THEN SET "pid" = (SELECT FIRST "employmentid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid" AND(SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid") = 1 ORDER BY 1 ASC) ELSE IF "ptaglocation" = 'C' THEN SET "pid" = (SELECT FIRST "employment"."companyid" FROM "iqxnetuserlink" KEY JOIN "employment" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" AND(SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pwebuserid") = 1 ORDER BY 1 ASC) 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 have division rights to the record IF "ptaglocation" LIKE '[PA]%' THEN SET "pid" = (SELECT FIRST "personid" FROM "person" WHERE "personid" = "pid" AND "isnull"("divisionid",'') = any(SELECT "divisionid" FROM "dashboardstaffdivisions"())) 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 "isnull"("c"."divisionid",'') = any(SELECT "divisionid" FROM "dashboardstaffdivisions"())) ELSE IF "ptaglocation" = 'C' THEN SET "pid" = (SELECT FIRST "companyid" FROM "company" WHERE "companyid" = "pid" AND "isnull"("divisionid",'') = any(SELECT "divisionid" FROM "dashboardstaffdivisions"())) END IF END IF END IF ELSE IF "userclass" = 'AGENCY' THEN SET "ispublic" = 0; IF "ptaglocation" LIKE '[PA]%' THEN SET "pid" = (SELECT FIRST "pe"."personid" FROM "pay_employee" AS "pe" KEY JOIN "company" KEY JOIN "employment" KEY JOIN "iqxnetuserlink" AS "x" WHERE "pe"."personid" = "pid" AND "x"."iqxnetuserid" = "pwebuserid") ELSE SET "ispublic" = 1 -- Read only access to web view questions only END IF ELSE IF "userclass" = 'CANDIDATE' THEN IF "ptaglocation" = 'P' THEN SET "pid" = "NetCandidateValidPersonID"("pWebUserID","pid") END IF; SET "ispublic" = 0 ELSE SET "ispublic" = 1 -- Read only access to web view questions only END IF END IF END IF END IF; IF "left"("pgroup",1) = '?' THEN -- If no members in the specified group, fall back on the underlying taglocation SET "pgroup" = "stuff"("pgroup",1,1,NULL); IF NOT EXISTS(SELECT * FROM "iqxNetTagGroupMember" WHERE "iqxNetTagGroupID" = "pgroup") THEN SET "pgroup" = NULL END IF END IF; IF "pgroup" IS NULL AND "pdoctype" IS NULL THEN SELECT "tag"."sortorder","tag"."taglocation","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), CAST(NULL AS CHAR(4)),(IF "ispublic" = 1 THEN 1 ELSE "tag"."readonly" endif) AS "readonly" FROM "tag" WHERE "tag"."taglocation" = "ptaglocation" AND(IF "ispublic" = 1 THEN "tag"."publiconweb" ELSE "tag"."publishtoweb" endif) = 1 UNION ALL SELECT "tag"."sortorder","tag"."taglocation","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),"tagchoice"."tagchoiceparentid",NULL 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 UNION ALL SELECT "tag"."sortorder","tag"."taglocation","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 ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC,6 ASC,7 ASC ELSE IF "pdoctype" IS NULL THEN SELECT "x"."sortorder","tag"."taglocation","tag"."tagid",0 AS "sortX",CAST(NULL AS SMALLINT),CAST(NULL AS CHAR(4)),0 AS "rectype","isnull"("x"."description","tag"."longdescription","tag"."description"),"tag"."tagtype","tag"."minstep","tag"."units", "isnull"("x"."required","tag"."required") AS "required","isnull"("tag"."displaygroup",0) AS "displaygroup",CAST(NULL AS DOUBLE),CAST(NULL AS long VARCHAR),CAST(NULL AS CHAR(4)),"isnull"("x"."readonly","tag"."readonly") AS "readonly" FROM "tag" JOIN "iqxNetTagGroupMember" AS "x" ON "x"."iqxNetTagGroupID" = "pgroup" AND "x"."tagid" = "tag"."tagid" AND "x"."taglocation" = "tag"."taglocation" WHERE "tag"."taglocation" = "NetQuestionOwnerID"("ptaglocation",NULL,"tag"."taglocation") UNION ALL SELECT "x"."sortorder","tag"."taglocation","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),"tagchoice"."tagchoiceparentid",NULL FROM "tagchoice" KEY JOIN "tag" JOIN "iqxNetTagGroupMember" AS "x" ON "x"."iqxNetTagGroupID" = "pgroup" AND "x"."tagid" = "tag"."tagid" AND "x"."taglocation" = "tag"."taglocation" WHERE "tag"."taglocation" = "NetQuestionOwnerID"("ptaglocation",NULL,"tag"."taglocation") AND "isnull"("tagchoice"."donotpublishtoweb",0) = 0 UNION ALL SELECT "x"."sortorder","tag"."taglocation","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" JOIN "iqxNetTagGroupMember" AS "x" ON "x"."iqxNetTagGroupID" = "pgroup" AND "x"."tagid" = "tag"."tagid" AND "x"."taglocation" = "tag"."taglocation" ,"tagvalue" KEY LEFT OUTER JOIN "tagchoice" WHERE "tagvalue"."id" = "NetQuestionOwnerID"("ptaglocation","pid","tag"."taglocation") AND "tag"."taglocation" = "NetQuestionOwnerID"("ptaglocation",NULL,"tag"."taglocation") AND "isnull"("tagchoice"."donotpublishtoweb",0) = 0 ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC,6 ASC,7 ASC ELSE SELECT "tag"."sortorder","tag"."taglocation","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", "isnull"("x"."required","tag"."required") AS "required","isnull"("tag"."displaygroup",0) AS "displaygroup",CAST(NULL AS DOUBLE),CAST(NULL AS long VARCHAR),CAST(NULL AS CHAR(4)),0 AS "readonly" FROM "tag" JOIN "DocumentTag" AS "x" ON "x"."DocumentTypeID" = "pdoctype" AND "x"."tagid" = "tag"."tagid" AND "x"."taglocation" = "tag"."taglocation" WHERE "tag"."publishtoweb" = 1 UNION ALL SELECT "tag"."sortorder","tag"."taglocation","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),"tagchoice"."tagchoiceparentid",NULL FROM "tagchoice" KEY JOIN "tag" JOIN "DocumentTag" AS "x" ON "x"."DocumentTypeID" = "pdoctype" AND "x"."tagid" = "tag"."tagid" AND "x"."taglocation" = "tag"."taglocation" WHERE "tag"."publishtoweb" = 1 AND "isnull"("tagchoice"."donotpublishtoweb",0) = 0 UNION ALL SELECT "tag"."sortorder","tag"."taglocation","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" JOIN "DocumentTag" AS "x" ON "x"."DocumentTypeID" = "pdoctype" AND "x"."tagid" = "tag"."tagid" AND "x"."taglocation" = "tag"."taglocation" ,"tagvalue" KEY LEFT OUTER JOIN "tagchoice" WHERE "tagvalue"."id" = "pid" AND "tag"."publishtoweb" = 1 AND "isnull"("tagchoice"."donotpublishtoweb",0) = 0 ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC,6 ASC,7 ASC END IF END IF END /* DOC Derived from NetQuestionnaire but with different use of pgroup to allow sub-selection using the new iqxNetTagGroup system. Where used this disregards publiconweb and publishtoweb Also added pdoctype to allow compliance questions with document upload 2017-06-09 PC change Description to long varchar to match data type 2017-06-22 PC Test and Doc 2018-06-06 Fix division access for owner logins. Fix tests. 2018-10-22 MHS IW-456 multi-cand fix and tests */ /* TEST call NetTestSetup(''); delete from tag where taglocation='P' and tagid='~~~'; select * from NetQuestionnaire2(null,null,null,null); expect EOF; select * from NetQuestionnaire2('test.candidate',null,null,null); expect EOF; select * from NetQuestionnaire2('test.candidate','P',null,null) where tagid='~~~'; expect EOF; insert into tag (tagid,taglocation,tagtype,description,publiconweb,publishtoweb) values ('~~~','P','M','TagDescrip',1,1); insert into tagchoice (taglocation,tagid,tagchoiceid,description) values ('P','~~~','~','ChoiceDescrip'); insert into tagvalue (taglocation,tagid,tagchoiceid,id) values ('P','~~~','~','TEST'); select * from NetQuestionnaire2('test.candidate','P',null,null) where tagid='~~~'; expect description=TagDescrip, rectype=0; expect description=ChoiceDescrip, rectype=1; expect rectype=3; expect EOF; select * from NetQuestionnaire2('test.candidate','P',null,'TEST') where tagid='~~~'; expect EOF; insert into iqxnettaggroup (iqxNetTagGroupID,description) on existing update values ('TEST','Test'); insert into iqxnettaggroupmember (iqxNetTagGroupID,taglocation,tagid) values ('TEST','P','~~~'); select * from NetQuestionnaire2('test.candidate','P',null,'TEST') where tagid='~~~'; expect description=TagDescrip, rectype=0; expect description=ChoiceDescrip, rectype=1; expect rectype=3; expect EOF; select * from NetQuestionnaire2('test.client','E',null,'TEST') where tagid='~~~'; expect EOF; select * from NetQuestionnaire2('test.client','C',null,'TEST') where tagid='~~~'; expect EOF; */ GO COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetQuestionnaire2" IS {CREATE PROCEDURE pears."NetQuestionnaire2"(IN pWebUserID CHAR(20),IN ptaglocation CHAR(3),IN pid CHAR(20) DEFAULT NULL,IN pgroup CHAR(25) DEFAULT NULL, IN pdoctype CHAR(25) DEFAULT NULL) RESULT(tagsortorder SMALLINT,taglocation CHAR(3),tagid CHAR(3),sortX SMALLINT,choicesortorder SMALLINT,tagchoiceid CHAR(4),rectype SMALLINT,description long VARCHAR,tagtype CHAR(1),minstep DOUBLE,units CHAR(10),required SMALLINT,displaygroup SMALLINT,VALUE DOUBLE,textvalue long VARCHAR,tagchoiceparentid CHAR(4),readonly SMALLINT) // IQXWeb BEGIN DECLARE ispublic SMALLINT; DECLARE userclass CHAR(20); IF pid IS NULL THEN SET ispublic=0; IF ptaglocation LIKE '[PA]%' THEN SET pid=NetCandidateValidPersonID(pWebUserID,pid) ELSE IF ptaglocation = 'E' THEN SET pid=(SELECT FIRST employmentid FROM iqxnetuserlink WHERE iqxnetuserid = pwebuserid AND (SELECT COUNT(*) FROM iqxnetuserlink WHERE iqxnetuserid = pwebuserid)=1 ORDER BY 1) ELSE IF ptaglocation = 'C' THEN SET pid=(SELECT FIRST employment.companyid FROM iqxnetuserlink KEY JOIN employment WHERE iqxnetuserlink.iqxnetuserid = pwebuserid AND (SELECT COUNT(*) FROM iqxnetuserlink WHERE iqxnetuserid = pwebuserid)=1 ORDER BY 1) 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 have division rights to the record IF ptaglocation LIKE '[PA]%' THEN SET pid=(SELECT FIRST personid FROM person WHERE personid = pid AND isnull(divisionid,'') IN (SELECT divisionid FROM dashboardstaffdivisions())) 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 isnull(c.divisionid,'') IN (SELECT divisionid FROM dashboardstaffdivisions())) ELSE IF ptaglocation = 'C' THEN SET pid=(SELECT FIRST companyid FROM company WHERE companyid = pid AND isnull(divisionid,'') IN (SELECT divisionid FROM dashboardstaffdivisions())) END IF END IF END IF ELSE IF userclass = 'AGENCY' THEN SET ispublic=0; IF ptaglocation LIKE '[PA]%' THEN SET pid=(SELECT FIRST pe.personid FROM pay_employee AS pe KEY JOIN company KEY JOIN employment KEY JOIN iqxnetuserlink AS x WHERE pe.personid=pid AND x.iqxnetuserid=pwebuserid) ELSE SET ispublic=1 -- Read only access to web view questions only END IF ELSE IF userclass = 'CANDIDATE' THEN IF ptaglocation = 'P' THEN SET pid=NetCandidateValidPersonID(pWebUserID,pid); END IF; SET ispublic=0 ELSE SET ispublic=1 -- Read only access to web view questions only END IF END IF END IF END IF; IF LEFT(pgroup,1)='?' THEN -- If no members in the specified group, fall back on the underlying taglocation SET pgroup=stuff(pgroup,1,1,NULL); IF NOT EXISTS(SELECT * FROM iqxNetTagGroupMember WHERE iqxNetTagGroupID=pgroup) THEN SET pgroup=NULL; END IF; END IF; IF pgroup IS NULL AND pdoctype IS NULL THEN SELECT tag.sortorder,tag.taglocation,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), CAST(NULL AS CHAR(4)),(IF ispublic = 1 THEN 1 ELSE tag.readonly endif) AS readonly FROM tag WHERE tag.taglocation = ptaglocation AND(IF ispublic = 1 THEN tag.publiconweb ELSE tag.publishtoweb endif) = 1 UNION ALL SELECT tag.sortorder,tag.taglocation,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),tagchoice.tagchoiceparentid,NULL 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 UNION ALL SELECT tag.sortorder,tag.taglocation,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 ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC,6 ASC,7 ASC ELSE IF pdoctype IS NULL THEN SELECT x.sortorder,tag.taglocation,tag.tagid,0 AS sortX,CAST(NULL AS SMALLINT),CAST(NULL AS CHAR(4)),0 AS rectype,isnull(x.description,tag.longdescription,tag.description),tag.tagtype,tag.minstep,tag.units, isnull(x.required,tag.required) AS required,isnull(tag.displaygroup,0) AS displaygroup,CAST(NULL AS DOUBLE),CAST(NULL AS long VARCHAR),CAST(NULL AS CHAR(4)),isnull(x.readonly,tag.readonly) AS readonly FROM tag JOIN iqxNetTagGroupMember x ON x.iqxNetTagGroupID=pgroup AND x.tagid=tag.tagid AND x.taglocation=tag.taglocation WHERE tag.taglocation = NetQuestionOwnerID(ptaglocation,NULL,tag.taglocation) UNION ALL SELECT x.sortorder,tag.taglocation,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),tagchoice.tagchoiceparentid,NULL FROM tagchoice KEY JOIN tag JOIN iqxNetTagGroupMember x ON x.iqxNetTagGroupID=pgroup AND x.tagid=tag.tagid AND x.taglocation=tag.taglocation WHERE tag.taglocation = NetQuestionOwnerID(ptaglocation,NULL,tag.taglocation) AND isnull(tagchoice.donotpublishtoweb,0) = 0 UNION ALL SELECT x.sortorder,tag.taglocation,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 JOIN iqxNetTagGroupMember x ON x.iqxNetTagGroupID=pgroup AND x.tagid=tag.tagid AND x.taglocation=tag.taglocation, tagvalue KEY LEFT OUTER JOIN tagchoice WHERE tagvalue.id = NetQuestionOwnerID(ptaglocation,pid,tag.taglocation) AND tag.taglocation = NetQuestionOwnerID(ptaglocation,NULL,tag.taglocation) AND isnull(tagchoice.donotpublishtoweb,0) = 0 ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC,6 ASC,7 ASC ELSE SELECT tag.sortorder,tag.taglocation,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, isnull(x.required,tag.required) AS required,isnull(tag.displaygroup,0) AS displaygroup,CAST(NULL AS DOUBLE),CAST(NULL AS long VARCHAR),CAST(NULL AS CHAR(4)),0 AS readonly FROM tag JOIN DocumentTag x ON x.DocumentTypeID=pdoctype AND x.tagid=tag.tagid AND x.taglocation=tag.taglocation WHERE tag.publishtoweb = 1 UNION ALL SELECT tag.sortorder,tag.taglocation,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),tagchoice.tagchoiceparentid,NULL FROM tagchoice KEY JOIN tag JOIN DocumentTag x ON x.DocumentTypeID=pdoctype AND x.tagid=tag.tagid AND x.taglocation=tag.taglocation WHERE tag.publishtoweb = 1 AND isnull(tagchoice.donotpublishtoweb,0) = 0 UNION ALL SELECT tag.sortorder,tag.taglocation,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 JOIN DocumentTag x ON x.DocumentTypeID=pdoctype AND x.tagid=tag.tagid AND x.taglocation=tag.taglocation, tagvalue KEY LEFT OUTER JOIN tagchoice WHERE tagvalue.id = pid AND tag.publishtoweb = 1 AND isnull(tagchoice.donotpublishtoweb,0) = 0 ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC,6 ASC,7 ASC END IF; END IF; END /* DOC Derived from NetQuestionnaire but with different use of pgroup to allow sub-selection using the new iqxNetTagGroup system. Where used this disregards publiconweb and publishtoweb Also added pdoctype to allow compliance questions with document upload 2017-06-09 PC change Description to long varchar to match data type 2017-06-22 PC Test and Doc 2018-06-06 Fix division access for owner logins. Fix tests. 2018-10-22 MHS IW-456 multi-cand fix and tests */ /* TEST call NetTestSetup(''); delete from tag where taglocation='P' and tagid='~~~'; select * from NetQuestionnaire2(null,null,null,null); expect EOF; select * from NetQuestionnaire2('test.candidate',null,null,null); expect EOF; select * from NetQuestionnaire2('test.candidate','P',null,null) where tagid='~~~'; expect EOF; insert into tag (tagid,taglocation,tagtype,description,publiconweb,publishtoweb) values ('~~~','P','M','TagDescrip',1,1); insert into tagchoice (taglocation,tagid,tagchoiceid,description) values ('P','~~~','~','ChoiceDescrip'); insert into tagvalue (taglocation,tagid,tagchoiceid,id) values ('P','~~~','~','TEST'); select * from NetQuestionnaire2('test.candidate','P',null,null) where tagid='~~~'; expect description=TagDescrip, rectype=0; expect description=ChoiceDescrip, rectype=1; expect rectype=3; expect EOF; select * from NetQuestionnaire2('test.candidate','P',null,'TEST') where tagid='~~~'; expect EOF; insert into iqxnettaggroup (iqxNetTagGroupID,description) on existing update values ('TEST','Test'); insert into iqxnettaggroupmember (iqxNetTagGroupID,taglocation,tagid) values ('TEST','P','~~~'); select * from NetQuestionnaire2('test.candidate','P',null,'TEST') where tagid='~~~'; expect description=TagDescrip, rectype=0; expect description=ChoiceDescrip, rectype=1; expect rectype=3; expect EOF; select * from NetQuestionnaire2('test.client','E',null,'TEST') where tagid='~~~'; expect EOF; select * from NetQuestionnaire2('test.client','C',null,'TEST') where tagid='~~~'; expect EOF; */ }