====== pears.NetReportConsultantDailies ======
<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"."NetReportConsultantDailies"( in "pWebUserID" 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);
  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"(current date,'dd/mm/yyyy')+' '+"deptname";
  select "rTitle",
    "s"."name",
    (select "count"() from "contactevent" where "staffid" = "s"."staffid" and "contactdate" = current date),
    (select "count"() from "placement" where "staffid" = "s"."staffid" and "placedate" = current date),
    (select "count"() from "tempshiftplan" where "staffid" = "s"."staffid" and "whenentered" between current date and current date+1),
    (select "count"() from "tempshift" where "state" in( 'P','B','W' ) and "staffid" = "s"."staffid" and "whenentered" between current date and current date+1),
    (select "count"() from "temptimesheet" where "staffid" = "s"."staffid" and "whenentered" between current date and current date+1)
    from "staff" as "s"
    where "s"."defaultdepartid" = "deptid"
    order by "s"."keyname" asc
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportConsultantDailies" IS 
{create PROCEDURE pears."NetReportConsultantDailies"(in pWebUserID 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);
  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(current date,'dd/mm/yyyy')+' '+deptname;
  select rTitle,
  s.name,
  (select count(*) from contactevent where staffid=s.staffid and contactdate=current date),
  (select count(*) from placement where staffid=s.staffid and placedate=current date),
  (select count(*) from tempshiftplan where staffid=s.staffid and whenentered between current date and current date+1),
  (select count(*) from tempshift where state in ('P','B','W') and staffid=s.staffid and whenentered between current date and current date+1),
  (select count(*) from temptimesheet where staffid=s.staffid and whenentered between current date and current date+1)
  from staff s
  where s.defaultdepartid=deptid
  order by s.keyname
end
}
</code>