pears.NetReportConsultantDailies

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
}