====== pears.NetCalendarShifts ======
<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>
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'
</code>