pears.IQXNetSaveQuestionnaire [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.IQXNetSaveQuestionnaire ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code 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'
</code>