====== pears.IQXNetSaveQuestionnaire ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original SQL =====
create procedure "pears"."IQXNetSaveQuestionnaire"(
/* Application Maintained Function / Procedure - DO NOT EDIT*/
in "pid" char(20),in "pdata" long varchar,in "plocation" char(2) default null )
begin
declare "thistagtype" char(1);
declare "thistaglocation" char(3);
declare "thistagid" char(3);
declare "thistagchoiceid" char(4);
declare "tagscleared" long varchar;
declare "thismoniker" char(10);
declare "thisid" char(20);
declare "x" char(250);
declare "y" char(250);
declare "n" double;
declare "i" integer;
declare "q" integer;
declare "l" integer;
declare "dt" date;
declare "auditTagID" char(5);
declare "auditTagLocation" char(5);
declare "auditID" char(20);
declare "auditOldValue" long varchar;
set "tagscleared" = '';
set "l" = "length"("pdata");
if "isnull"("l",0) = 0 then
return
end if;
set "i" = 1;
"mainloop":
while "i" <= "l" loop
set "q" = "locate"("pdata",'=',"i");
if "q" = 0 then
leave "mainloop"
end if;
set "x" = "substr"("pdata","i","q"-"i");
set "i" = "q"+1;
set "q" = "locate"("pdata",'&',"i");
if "q" = 0 then
set "y" = "substr"("pdata","i",("l"+1)-"i");
set "i" = "l"+1
else
set "y" = "substr"("pdata","i","q"-"i");
set "i" = "q"+1
end if;
set "x" = "IQXNetDecodePostValue"("x");
set "y" = "trim"("IQXNetDecodePostValue"("y"));
set "thistagtype" = "substr"("x",1,1);
set "thistaglocation" = "substr"("x",2,3);
set "thistagid" = "substr"("x",5,3);
if "plocation" is null then
set "thisid" = "pid"
else
set "thisid" = "NetQuestionOwnerID"("plocation","pid","thistaglocation")
end if;
if "thisid" is null then
continue "mainloop"
end if;
set "thismoniker" = '{'+"thistaglocation"+"thistagid"+'}';
set "thistaglocation" = "replace"("rtrim"("replace"("thistaglocation",'_',' ')),' ','_'); -- get rid of trailing underlines
set "thistagid" = "replace"("rtrim"("replace"("thistagid",'_',' ')),' ','_');
set "thistagchoiceid" = "substr"("x",8,4);
if "locate"("tagscleared","thismoniker") = 0 then
call "NetQuestionAudit"("auditTagLocation","auditTagID","auditID","auditOldValue");
set "auditTagLocation" = "thistaglocation";
set "auditTagID" = "thistagid";
set "auditID" = "thisid";
set "auditOldValue" = "getquestanswer"("thistaglocation","thistagid","thisid");
delete from "tagvalue" where "taglocation" = "thistaglocation" and "tagid" = "thistagid" and "id" = "thisid"
and not exists(select * from "tagchoice" where "taglocation" = "thistaglocation" and "tagid" = "thistagid" and "tagchoiceid" = "tagvalue"."tagchoiceid" and "donotpublishtoweb" = 1);
set "tagscleared" = "tagscleared"+"thismoniker"
end if;
if "thistagtype" in( 'T','U' ) and "y" <> '' then
if "thistagtype" = 'U' then
set "y" = "ucase"("y")
end if;
insert into "tagvalue"( "taglocation","tagid","id","textvalue" ) values( "thistaglocation","thistagid","thisid","y" )
else if "thistagtype" = 'M' and "y" <> '' and "y" <> '_' then
insert into "tagvalue"( "taglocation","tagid","tagchoiceid","id","value" ) values( "thistaglocation","thistagid","y","thisid",1 )
else if "thistagtype" = 'D' and "y" <> '' then
set "dt" = "IQXNetStringToDate"("y");
if "dt" is not null then
insert into "tagvalue"( "taglocation","tagid","id","value" ) values( "thistaglocation","thistagid","thisid","datediff"("day",'1899-12-30',"dt") )
end if
else begin
set "n" = cast("y" as double)
exception
when others then set "n" = 0
end;
if "n" <> 0 then
if "thistagtype" = 'N' then
insert into "tagvalue"( "taglocation","tagid","id","value" ) values( "thistaglocation","thistagid","thisid","n" )
else
insert into "tagvalue"( "taglocation","tagid","tagchoiceid","id","value" ) values( "thistaglocation","thistagid","thistagchoiceid","thisid","n" )
end if
end if
end if
end if
end if
end loop "mainloop";
call "NetQuestionAudit"("auditTagLocation","auditTagID","auditID","auditOldValue")
end
go
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."IQXNetSaveQuestionnaire" IS
'create procedure IQXNetSaveQuestionnaire
/* Application Maintained Function / Procedure - DO NOT EDIT*/
( in pid char(20),in pdata long varchar,in plocation char(2) default null )
begin
declare thistagtype char(1);
declare thistaglocation char(3);
declare thistagid char(3);
declare thistagchoiceid char(4);
declare tagscleared long varchar;
declare thismoniker char(10);
declare thisid char(20);
declare x char(250);
declare y char(250);
declare n double;
declare i integer;
declare q integer;
declare l integer;
declare dt date;
declare auditTagID char(5);
declare auditTagLocation char(5);
declare auditID char(20);
declare auditOldValue long varchar;
set tagscleared = '''';
set l = length(pdata);
if isnull(l,0) = 0 then
return
end if;
set i = 1;
mainloop:
while i <= l loop
set q = locate(pdata,''='',i);
if q = 0 then
leave mainloop
end if;
set x = substr(pdata,i,q-i);
set i = q+1;
set q = locate(pdata,''&'',i);
if q = 0 then
set y = substr(pdata,i,(l+1)-i);
set i = l+1
else
set y = substr(pdata,i,q-i);
set i = q+1
end if;
set x = IQXNetDecodePostValue(x);
set y = trim(IQXNetDecodePostValue(y));
set thistagtype = substr(x,1,1);
set thistaglocation = substr(x,2,3);
set thistagid = substr(x,5,3);
if plocation is null then
set thisid = pid
else
set thisid = NetQuestionOwnerID(plocation,pid,thistaglocation)
end if;
if thisid is null then
continue mainloop
end if;
set thismoniker = ''{''+thistaglocation+thistagid+''}'';
set thistaglocation = replace(rtrim(replace(thistaglocation,''_'','' '')),'' '',''_''); -- get rid of trailing underlines
set thistagid = replace(rtrim(replace(thistagid,''_'','' '')),'' '',''_'');
set thistagchoiceid = substr(x,8,4);
if locate(tagscleared,thismoniker) = 0 then
call NetQuestionAudit(auditTagLocation,auditTagID,auditID,auditOldValue);
set auditTagLocation = thistaglocation;
set auditTagID = thistagid;
set auditID = thisid;
set auditOldValue = getquestanswer(thistaglocation,thistagid,thisid);
delete from tagvalue where taglocation = thistaglocation and tagid = thistagid and id = thisid
and not exists(select * from tagchoice where taglocation = thistaglocation and tagid = thistagid and tagchoiceid = tagvalue.tagchoiceid and donotpublishtoweb = 1);
set tagscleared = tagscleared+thismoniker
end if;
if thistagtype in( ''T'',''U'' ) and y <> '''' then
if thistagtype = ''U'' then
set y = ucase(y)
end if;
insert into tagvalue( taglocation,tagid,id,textvalue ) values( thistaglocation,thistagid,thisid,y )
else if thistagtype = ''M'' and y <> '''' and y <> ''_'' then
insert into tagvalue( taglocation,tagid,tagchoiceid,id,value ) values( thistaglocation,thistagid,y,thisid,1 )
else if thistagtype = ''D'' and y <> '''' then
set dt = IQXNetStringToDate(y);
if dt is not null then
insert into tagvalue( taglocation,tagid,id,value ) values( thistaglocation,thistagid,thisid,datediff(day,''1899-12-30'',dt) )
end if
else begin
set n = cast(y as double)
exception
when others then set n = 0
end;
if n <> 0 then
if thistagtype = ''N'' then
insert into tagvalue( taglocation,tagid,id,value ) values( thistaglocation,thistagid,thisid,n )
else
insert into tagvalue( taglocation,tagid,tagchoiceid,id,value ) values( thistaglocation,thistagid,thistagchoiceid,thisid,n )
end if
end if
end if
end if
end if
end loop mainloop;
call NetQuestionAudit(auditTagLocation,auditTagID,auditID,auditOldValue)
end'