pears.NetReportConsultantStats

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
}