pears.GetShiftStateDescription

CREATE FUNCTION "pears"."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