pears.IQXNetSaveQuestionnaire

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'