pears.NetTempShiftStatusString

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