pears.NetErrorHandler

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*;
*/