====== pears.NetTempShiftStatusString ======
<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"."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;
*/
}
</code>