====== pears.NetErrorHandler ======
<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 function "pears"."NetErrorHandler"( in "pErrorCode" integer,in "UID" char(20) default null,in "pRecordID" char(20) default null,in "pActionDetails" char(255) default null ) 
returns char(255)
deterministic // IQXWeb
begin
  declare "retval" char(255);
  declare "ErrorMessage" char(250);
  declare "ErrorAnalysisText" char(20);
  declare "HelpMessage" char(1024);
  declare "StaffID" char(20);
  declare "LogAll" char(1);
  declare "LogID" char(20);
  declare "DescriptionString" char(300);
  if "isnull"("pErrorCode",'0') = 0 then
    return 'A System-level error occured'
  end if;
  set "StaffID" = "isnull"((select "staffid" from "iqxnetuser" where "iqxnetuserid" = 'user'),'IQXWEB');
  select "l"."ErrorMessage","l"."HelpMessage" into "ErrorMessage","HelpMessage" from "ErrorList" as "l" where "l"."ErrorCode" = "pErrorCode";
  set "LogAll" = (select "isnull"("value",0) from "IQXNetSwitch" where "iqxnetswitchid" = 'LOGALLIQXWEBMESSAGES');
  if "ErrorMessage" is null then
    set "ErrorMessage" = "isnull"("pActionDetails",'A System-level error occured')
  end if;
  if "pErrorCode" <= 99 then -- System error
    set "ErrorAnalysisText" = 'WEBSYSERR'
  end if;
  if "pErrorCode" between 100 and 199 then -- warning
    set "ErrorAnalysisText" = 'WEBUSERERR'
  end if;
  if "pErrorCode" >= 200 then -- information
    set "ErrorAnalysisText" = 'WEBSTATS'
  end if;
  if "ErrorAnalysisText" = 'WEBSYSERR' or "LogAll" = '1' then
    set "LogID" = "uniquekey"('x');
    set "DescriptionString" = "string"("pErrorCode",':',"ErrorMessage");
    insert into "audittrail"( "audittrailid","eventdatetime","userid","AuditType","RecordID","Description","OldValues","NewValues","ConnectionNumber" ) values( "LogID","now"(),"StaffID","ErrorAnalysisText","pRecordID","DescriptionString","UID","pActionDetails","connection_property"('Number') ) 
  end if;
  update "ErrorList" set "TotalCount" = (select "isnull"("TotalCount",0)+1 from "ErrorList" where "ErrorCode" = "pErrorCode"),"LastOccurrence" = "now"() where "ErrorCode" = "pErrorCode";
  set "retval" = "string"("pErrorCode",':~',"ErrorMessage");
  if "HelpMessage" is not null then
    set "retval" = "string"("retval",'\x0A\x0A',"HelpMessage")
  end if;
  if "LogID" is not null then
    set "retval" = "string"("retval",'\x0A\x0A','Reference: ',"LogID")
  end if;
  return "retval"
end /* DOC
2018-05-11 PC IW-513 Improve error handling and display
2018-05-21 PC IW-513 alter error codes to prioritise error state by code
2018-06-14 PC add reference code to reference line in audittrail
2018-06-27 PC add help message for highlight box or link to wiki
2018-07-24 PC modified return string for appearance
*/
/* TEST
delete from audittrail where audittype='websyserr';
select NetErrorHandler(1);
expect NetErrorHandler(1)=1:~A System-level error occured*; 
select top 1 description,OldValues from audittrail where audittype='websyserr' order by eventdatetime desc;
expect description=1:A*; 
select NetErrorHandler(99);
expect NetErrorHandler(99)=99:~Permission denied*; 
select top 1 description,OldValues from audittrail where audittype='websyserr' order by eventdatetime desc;
expect description=99:Permission*;
select NetErrorHandler(100);
expect NetErrorHandler(100)=100:~invalid date or time*; 
select top 1 description,OldValues from audittrail where audittype='websyserr' order by eventdatetime desc;
expect description=99:Permission*;
*/
</code>