pears.NetCalendarShifts

CREATE PROCEDURE "pears"."NetCalendarShifts"( IN "pWebUserID" CHAR(20),IN "pstart" DOUBLE,IN "pend" DOUBLE ) 
RESULT( "shiftdate" CHAR(20),"role" CHAR(1),"shiftcount" INTEGER,"title" CHAR(50),"tipArray" long VARCHAR ) 
BEGIN
  DECLARE "dStart" DATE;
  DECLARE "dEnd" DATE;
  SET "dstart" = "dateadd"("second","pstart",'1970-01-01');
  SET "dend" = "dateadd"("second","pend",'1970-01-01');
  SELECT "dateformat"("subby"."shiftdate",'yyyy-mm-dd') AS "caldate",'F' AS "role",
    "subby"."cnt" AS "shiftcount",
    "string"('Filled: ',"subby"."cnt") AS "title",
    "string"('[',
    (SELECT "list"("string"('{"name":',"netjsstring"("person"."name"),',"id":',"netjsstring"("person"."personid"),',"descrip":',
      "netjsstring"("string"("v"."position",' ',
      "isnull"("st"."description","string"("dateformat"("s"."timefrom",'hh:nn'),'-',"dateformat"("s"."timeto",'hh:nn'))))),'}'),',')
      FROM "tempshift" AS "s" KEY JOIN("tempshiftplan" AS "p","person")
        ,"tempshiftplan" AS "p" KEY JOIN "vacancy" AS "v" KEY JOIN "employment"
        ,"tempshiftplan" AS "p" KEY LEFT OUTER JOIN "tempshifttype" AS "st"
      WHERE "s"."state" IN( 'P','B','W' ) 
      AND "s"."shiftdate" = "subby"."shiftdate"
      AND "p"."description" <> 'Added Time'
      AND "employment"."companyid" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")),']') AS "tipArray"
    FROM(SELECT "s"."shiftdate","count"() AS "cnt"
        FROM "tempshift" AS "s" KEY JOIN "tempshiftplan" AS "p" KEY JOIN "vacancy" AS "v" KEY JOIN "employment"
        WHERE "s"."state" IN( 'P','B','W' ) 
        AND "s"."shiftdate" BETWEEN "dstart" AND "dend"
        AND "p"."description" <> 'Added Time'
        AND "employment"."companyid" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
        GROUP BY "s"."shiftdate") AS "subby" UNION ALL
  SELECT "dateformat"("subby"."shiftdate",'yyyy-mm-dd'),'U',
    "subby"."cnt",
    "string"('Unfilled: ',"subby"."cnt"),
    "string"('[',
    (SELECT "list"("string"('{"descrip":',
      "netjsstring"("string"("v"."position",' ',"isnull"("st"."description","string"("dateformat"("p"."timefrom",'hh:nn'),'-',"dateformat"("p"."timeto",'hh:nn'))))),'}'),',')
      FROM "tempshiftplan" AS "p" KEY JOIN "vacancy" AS "v" KEY JOIN "employment"
        ,"tempshiftplan" AS "p" KEY LEFT OUTER JOIN "tempshifttype" AS "st"
      WHERE NOT EXISTS(SELECT * FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND NOT("state" = 'C' AND "crefill" = 1))
      AND "p"."shiftdate" = "subby"."shiftdate"
      AND "employment"."companyid" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")),']')
    FROM(SELECT "p"."shiftdate","count"() AS "cnt"
        FROM "tempshiftplan" AS "p" KEY JOIN "vacancy" KEY JOIN "employment"
        WHERE NOT EXISTS(SELECT * FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND NOT("state" = 'C' AND "crefill" = 1))
        AND "p"."shiftdate" BETWEEN "dstart" AND "dend"
        AND "employment"."companyid" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
        GROUP BY "p"."shiftdate") AS "subby"
    ORDER BY 1 ASC,2 DESC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCalendarShifts" IS 
'create procedure pears."NetCalendarShifts"( in "pWebUserID" char(20),in "pstart" double,in "pend" double ) 
result( "shiftdate" char(20),"role" char(1),"shiftcount" integer,"title" char(50),"tipArray" long varchar ) 
begin
  declare "dStart" date;
  declare "dEnd" date;
  set "dstart" = "dateadd"("second","pstart",''1970-01-01'');
  set "dend" = "dateadd"("second","pend",''1970-01-01'');
  select "dateformat"("subby"."shiftdate",''yyyy-mm-dd'') as "caldate",''F'' as "role",
    "subby"."cnt" as "shiftcount",
    "string"(''Filled: '',"subby"."cnt") as "title",
    "string"(''['',
    (select "list"("string"(''{"name":'',"netjsstring"("person"."name"),'',"id":'',"netjsstring"("person"."personid"),'',"descrip":'',
      "netjsstring"("string"("v"."position",'' '',
      "isnull"("st"."description","string"("dateformat"("s"."timefrom",''hh:nn''),''-'',"dateformat"("s"."timeto",''hh:nn''))))),''}''),'','')
      from "tempshift" as "s" key join("tempshiftplan" as "p","person")
        ,"tempshiftplan" as "p" key join "vacancy" as "v" key join "employment"
        ,"tempshiftplan" as "p" key left outer join "tempshifttype" as "st"
      where "s"."state" in( ''P'',''B'',''W'' ) 
      and "s"."shiftdate" = "subby"."shiftdate"
      and "p"."description" <> ''Added Time''
      and "employment"."companyid" = any(select "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID")),'']'') as "tipArray"
    from(select "s"."shiftdate","count"() as "cnt"
        from "tempshift" as "s" key join "tempshiftplan" as "p" key join "vacancy" as "v" key join "employment"
        where "s"."state" in( ''P'',''B'',''W'' ) 
        and "s"."shiftdate" between "dstart" and "dend"
        and "p"."description" <> ''Added Time''
        and "employment"."companyid" = any(select "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID")
        group by "s"."shiftdate") as "subby" union all
  select "dateformat"("subby"."shiftdate",''yyyy-mm-dd''),''U'',
    "subby"."cnt",
    "string"(''Unfilled: '',"subby"."cnt"),
    "string"(''['',
    (select "list"("string"(''{"descrip":'',
      "netjsstring"("string"("v"."position",'' '',"isnull"("st"."description","string"("dateformat"("p"."timefrom",''hh:nn''),''-'',"dateformat"("p"."timeto",''hh:nn''))))),''}''),'','')
      from "tempshiftplan" as "p" key join "vacancy" as "v" key join "employment"
        ,"tempshiftplan" as "p" key left outer join "tempshifttype" as "st"
      where not exists(select * from "tempshift" where "tempshiftplanid" = "p"."tempshiftplanid" and not("state" = ''C'' and "crefill" = 1))
      and "p"."shiftdate" = "subby"."shiftdate"
      and "employment"."companyid" = any(select "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID")),'']'')
    from(select "p"."shiftdate","count"() as "cnt"
        from "tempshiftplan" as "p" key join "vacancy" key join "employment"
        where not exists(select * from "tempshift" where "tempshiftplanid" = "p"."tempshiftplanid" and not("state" = ''C'' and "crefill" = 1))
        and "p"."shiftdate" between "dstart" and "dend"
        and "employment"."companyid" = any(select "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID")
        group by "p"."shiftdate") as "subby"
    order by 1 asc,2 desc
end'