pears.NetTempShiftStatusString

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetTempShiftStatusString" IS 
{CREATE FUNCTION pears."NetTempShiftStatusString"(IN pShiftStatus CHAR(30),IN pTempConfirmed INTEGER DEFAULT NULL, IN pClientConfirmed INTEGER DEFAULT NULL, IN pCancelReason CHAR(255) DEFAULT NULL)
RETURNS CHAR(200)
DETERMINISTIC
BEGIN
 
    IF pShiftStatus='B' THEN
        RETURN 'Confirmed';
    END IF;
 
    IF pShiftStatus='W' THEN
        RETURN 'Worked';
    END IF;
 
    IF isnull(pShiftStatus,'')='' THEN
        RETURN 'Unfilled';
    END IF;
 
    IF pShiftStatus='P' THEN
        RETURN string('Pending',
          CASE 
            WHEN pClientConfirmed=0 AND pTempConfirmed=0 
               THEN ' Client and Temp Confirmation'
            WHEN pClientConfirmed=0  
               THEN ' Client Confirmation'
            WHEN pTempConfirmed=0 
               THEN ' Temp Confirmation'
          END)
    END IF;
 
    IF pShiftStatus='C' THEN
        RETURN string('Cancelled',
          (IF pCancelReason IS NOT NULL THEN ': '+pCancelReason endif),
          CASE 
            WHEN pClientConfirmed=0 AND pTempConfirmed=0 
               THEN ', Awaiting Client and Temp Notification'
            WHEN pClientConfirmed=0  
               THEN ', Awaiting Client Notification'
            WHEN pTempConfirmed=0 
               THEN ', Awaiting Temp Notification'
          END)
    END IF;
 
END
 
/* DOC
30-05-2018 PC IW-558 move state detail to second routine
6/6/2018 MHS re-worked and sorted tests
 
*/
 
/* TEST
select NetTempshiftStatusString('W') as A;
expect A=Worked;
select replace(NetTempshiftStatusString('C',0,0,'Test'),',','#') as A;
expect A=Cancelled: Test# Awaiting Client and Temp Notification;
select NetTempshiftStatusString('P',0,1) as A;
expect A=Pending Temp Confirmation;
*/
}