====== 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>
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*;
*/
}
</code>