====== pears.NetReportConsultantStats ======
<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"."NetReportConsultantStats"( in "pWebUserID" char(20),in "From__D0b" char(20),in "To__D0" char(20) ) 
result( "Title__H" char(100),"Consultant__1" char(60),"Contacts_Made__RT" integer,"Placements_Made__RT" integer,"Shifts_Entered__RT" integer,"Shifts_Booked__RT" integer,"Timesheets_Completed__RT" integer ) 
begin
  declare "rTitle" char(100);
  declare "deptid" char(2);
  declare "deptname" char(30);
  declare "dFrom" date;
  declare "dTo" date;
  set "dFrom" = "iqxnetstringtodate"("From__D0b");
  set "dTo" = "iqxnetstringtodate"("To__D0");
  select "d"."departmentid","d"."name" into "deptid","deptname" from "department" as "d" key join "staff" key join "iqxnetuser" as "u" where "u"."iqxnetuserid" = "pWebUserID";
  set "rTitle" = "dateformat"("dFrom",'dd/mm/yyyy')+'-'+"dateformat"("dTo",'dd/mm/yyyy')+' '+"deptname";
  select "rTitle",
    "s"."name",
    (select "count"() from "contactevent" where "staffid" = "s"."staffid" and "contactdate" between "dFrom" and "dTo"),
    (select "count"() from "placement" where "staffid" = "s"."staffid" and "placedate" between "dFrom" and "dTo"),
    (select "count"() from "tempshiftplan" where "staffid" = "s"."staffid" and "whenentered" between "dFrom" and "dTo"+1),
    (select "count"() from "tempshift" where "state" in( 'P','B','W' ) and "staffid" = "s"."staffid" and "whenentered" between "dFrom" and "dTo"+1),
    (select "count"() from "temptimesheet" where "staffid" = "s"."staffid" and "whenentered" between "dFrom" and "dTo"+1)
    from "staff" as "s"
    where "s"."defaultdepartid" = "deptid"
    order by "s"."keyname" asc
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportConsultantStats" IS 
{create PROCEDURE pears."NetReportConsultantStats"(in pWebUserID char(20), in From__D0b char(20), in To__D0 char(20))
result(Title__H char(100),Consultant__1 char(60),Contacts_Made__RT integer,Placements_Made__RT integer,Shifts_Entered__RT integer,Shifts_Booked__RT integer,Timesheets_Completed__RT integer)
begin
  declare rTitle char(100);
  declare deptid char(2);
  declare deptname char(30);
  declare dFrom date;
  declare dTo date;
  set dFrom=iqxnetstringtodate(From__D0b);
  set dTo=iqxnetstringtodate(To__D0);
  select d.departmentid,d.name into deptid,deptname from department d key join staff key join iqxnetuser u where u.iqxnetuserid=pWebUserID;
  set rTitle=dateformat(dFrom,'dd/mm/yyyy')+'-'+dateformat(dTo,'dd/mm/yyyy')+' '+deptname;
  select rTitle,
  s.name,
  (select count(*) from contactevent where staffid=s.staffid and contactdate between dFrom and dTo),
  (select count(*) from placement where staffid=s.staffid and placedate between dFrom and dTo),
  (select count(*) from tempshiftplan where staffid=s.staffid and whenentered between dFrom and dTo+1),
  (select count(*) from tempshift where state in ('P','B','W') and staffid=s.staffid and whenentered between dFrom and dTo+1),
  (select count(*) from temptimesheet where staffid=s.staffid and whenentered between dFrom and dTo+1)
  from staff s
  where s.defaultdepartid=deptid
  order by s.keyname
end
}
</code>