pears.GetShiftStateDescription

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."GetShiftStateDescription" IS 
{CREATE FUNCTION GetShiftStateDescription 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(  IN sid CHAR(20),IN ddate DATE,IN bperson SMALLINT ) 
RETURNS CHAR(70)
BEGIN
  DECLARE rv CHAR(70);
  DECLARE t CHAR(70);
  DECLARE tt CHAR(70);
  DECLARE s CHAR(20);
  DECLARE s1 CHAR(1);
  -- bperson=2 means same as 1 but full description for Available and Unavailable
  IF bperson = -1 THEN
    RETURN getshiftstatedescriptionPlus(sid,ddate)
  ELSE IF bperson >= 1 THEN
      SET rv = getkeyshiftstate(sid,ddate,1)
    ELSE
      SET rv = getkeyshiftplanstate(sid,ddate,bperson)
    END IF END IF;
  SET s1 = LEFT(rv,1);
  IF LENGTH(rv) > 1 AND s1 IN( 'P','B','W','E','X','Y' ) THEN
    SET s = stuff(rv,1,1,NULL);
    SET t = NULL;
    IF bperson >= 1 THEN
      IF s1 = 'E' THEN -- Employment NOT Shift record
        SELECT company.name INTO t FROM employment KEY JOIN company WHERE employment.employmentid = s
      ELSE
        IF s1 IN( 'X','Y' ) THEN -- Plan - Y indicates cascaded
          SELECT isnull(person.name,company.name) INTO t FROM tempshiftplan KEY JOIN vacancy KEY JOIN employment KEY JOIN company,vacancy KEY LEFT OUTER JOIN person WHERE tempshiftplan.tempshiftplanid = s
        ELSE
          SELECT isnull(person.name,company.name) INTO t FROM tempshift KEY JOIN vacancy KEY JOIN employment KEY JOIN company,vacancy KEY LEFT OUTER JOIN person WHERE tempshift.tempshiftid = s
        END IF
      END IF
    ELSE IF s1 NOT IN( 'X','Y' ) THEN
        SELECT gettempname(tempshift.personid) INTO t FROM tempshift WHERE tempshift.tempshiftid = s
      END IF END IF;
    SET rv = string(s1,t)
  END IF;
  IF s1 IN( 'A','U' ) AND bperson >= 1 THEN
    SET s = NULLIF(stuff(rv,1,1,NULL),'');
    IF s1 = 'A' THEN
      SELECT string('Available ',a.availtemplateid),string('A: ',a.name,' ',dateformat(a.timefrom,'hh:nn'),'-',dateformat(a.timeto,'hh:nn'))
        INTO t,tt FROM tempshift AS sh KEY JOIN availabilitytemplate AS a WHERE sh.tempshiftid = s;
      SET t = isnull(t,'Available')
    ELSE
      SET t = 'Unavailable';
      SELECT string('U: ',u.name) INTO tt FROM tempshift AS sh KEY JOIN tempshiftunavailablereason AS u WHERE bperson = 2 AND sh.tempshiftid = s
    END IF;
    IF bperson = 2 AND tt IS NOT NULL THEN
      SET rv = tt
    ELSE
      SET rv = t
    END IF
  END IF;
  RETURN rv
END
}