pears.NetErrorHandler

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetErrorHandler" IS 
{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)
// IQXWeb
DETERMINISTIC
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 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,'\n\n',HelpMessage) 
    END IF;
 
    IF LogID IS NOT NULL THEN 
        SET retval=string(retval,'\n\n','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*;
*/
}