pears.NetReportTimesheets

CREATE PROCEDURE "pears"."NetReportTimesheets"( IN "pWebUserID" CHAR(20),IN "From__D" CHAR(20),IN "To__D" CHAR(20) ) 
RESULT( "Title__H" CHAR(100),"Number__1" CHAR(20),"Temp" CHAR(60),"Position" CHAR(60),"Client__B" CHAR(100),"End_Date__DXA" DATE,"Total__RMTG" DOUBLE,"Details__Eat" CHAR(100) ) 
BEGIN
  DECLARE "pstart" DATE;
  DECLARE "pend" DATE;
  DECLARE "pStartPeriod" INTEGER;
  DECLARE "pEndPeriod" INTEGER;
  DECLARE "rTitle" CHAR(100);
  DECLARE "userClass" CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    ) NOT transactional;SET "pstart" = "iqxnetstringtodate"("From__D");
  SET "pend" = "iqxnetstringtodate"("To__D");
  SET "rTitle" = "dateformat"("pstart",'dd/mm/yyyy')+' - '+"dateformat"("pend",'dd/mm/yyyy');
  SET "pStartPeriod" = "weekcontaining"("pstart");
  SET "pEndPeriod" = "weekcontaining"("pend");
  SET "userClass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  IF "userClass" = 'CANDIDATE' THEN
    INSERT INTO "IDs"( "ID" ) 
      SELECT DISTINCT "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID";
    SELECT "rTitle","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
      "Sum"((SELECT "sum"("unitspaid"*"payrate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid")) AS "timesheettotal",'NetReportTimesheetLines&pTimesheetID='
      +"t"."temptimesheetid" AS "Details"
      FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company"
        ,"temptimesheet" AS "t" KEY JOIN "tempdesk"
        ,"temptimesheet" AS "t" KEY JOIN "person"
      WHERE "t"."personid" = any(SELECT "id" FROM "ids")
      AND "t"."Period" BETWEEN "pStartPeriod" AND "pEndPeriod"
      AND "t"."PeriodLength" = 'W'
      GROUP BY GROUPING sets((),("weekenddate"),(("weekenddate"),"rTitle","t"."serialnumber","tempname","vacancy"."position","companyname","Details"))
  ELSE IF "userClass" = 'AGENCY' THEN
      INSERT INTO "IDs"( "ID" ) 
        SELECT DISTINCT "e"."companyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" WHERE "l"."iqxnetuserid" = "pWebUserID";
      SELECT "rTitle","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
        "sum"((SELECT "sum"("unitspaid"*"payrate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid")) AS "timesheettotal",'NetReportTimesheetLines&pTimesheetID='
        +"t"."temptimesheetid" AS "Details"
        FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company"
          ,"temptimesheet" AS "t" KEY JOIN "tempdesk"
          ,"temptimesheet" AS "t" KEY JOIN "person" KEY JOIN "pay_employee" KEY JOIN "company" AS "agcomp"
        WHERE "agcomp"."companyid" = any(SELECT "id" FROM "ids")
        AND "weekenddate" >= "IQXNetCompanyStartDate"("agcomp"."companyid")
        AND "t"."Period" BETWEEN "pStartPeriod" AND "pEndPeriod"
        AND "t"."PeriodLength" = 'W'
        GROUP BY GROUPING sets((),("weekenddate"),(("weekenddate"),"rTitle","t"."serialnumber","tempname","vacancy"."position","companyname","Details"))
    ELSE -- CLIENT
      INSERT INTO "IDs"( "ID" ) 
        SELECT DISTINCT "e"."companyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" WHERE "l"."iqxnetuserid" = "pWebUserID";
      SELECT "rTitle","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname","weekmonthenddate"("t"."period","t"."periodlength") AS "weekenddate",
        "sum"((SELECT "sum"("unitscharged"*"chargerate") FROM "temptimesheetline" WHERE "temptimesheetid" = "t"."temptimesheetid")) AS "timesheettotal",'NetReportTimesheetLines&pTimesheetID='
        +"t"."temptimesheetid" AS "Details"
        FROM "temptimesheet" AS "t" KEY JOIN "placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company"
          ,"temptimesheet" AS "t" KEY JOIN "tempdesk"
          ,"temptimesheet" AS "t" KEY JOIN "person"
        WHERE "company"."companyid" = any(SELECT "id" FROM "ids")
        AND "weekenddate" >= "IQXNetCompanyStartDate"("company"."companyid")
        AND "t"."Period" BETWEEN "pStartPeriod" AND "pEndPeriod"
        AND "t"."PeriodLength" = 'W'
        GROUP BY GROUPING sets((),("weekenddate"),(("weekenddate"),"t"."serialnumber","tempname","vacancy"."position","companyname","t"."temptimesheetid"))
    END IF
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetReportTimesheets" IS 
{CREATE PROCEDURE pears."NetReportTimesheets"(IN pWebUserID CHAR(20),IN From__D CHAR(20),IN To__D CHAR(20))
RESULT(Title__H CHAR(100),Number__1 CHAR(20),Temp CHAR(60),POSITION CHAR(60),Client__B CHAR(100),End_Date__DXA DATE,Total__RMTG DOUBLE,Details__Eat CHAR(100))
BEGIN
  DECLARE pstart DATE;
  DECLARE pend DATE;
  DECLARE pStartPeriod INTEGER;
  DECLARE pEndPeriod INTEGER;
  DECLARE rTitle CHAR(100);
  DECLARE userClass CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE IDs(
    ID CHAR(20) NULL,
    ) NOT transactional;SET pstart=iqxnetstringtodate(From__D);
  SET pend=iqxnetstringtodate(To__D);
  SET rTitle=dateformat(pstart,'dd/mm/yyyy')+' - '+dateformat(pend,'dd/mm/yyyy');
  SET pStartPeriod=weekcontaining(pstart);
  SET pEndPeriod=weekcontaining(pend);
  SET userClass=(SELECT FIRST iqxnetuserclassid FROM iqxnetuser WHERE iqxnetuserid = pWebUserID);
  IF userClass = 'CANDIDATE' THEN
    INSERT INTO IDs( ID) 
      SELECT DISTINCT personid FROM iqxnetuserlink WHERE iqxnetuserid = pWebUserID;
    SELECT rTitle,t.serialnumber,string(person.surname,', ',person.forenames) AS tempname,vacancy.position,company.name AS companyname,weekmonthenddate(t.period,t.periodlength) AS weekenddate,
      SUM((SELECT SUM(unitspaid*payrate) FROM temptimesheetline WHERE temptimesheetid = t.temptimesheetid)) AS timesheettotal,'NetReportTimesheetLines&pTimesheetID='
      +t.temptimesheetid AS Details
      FROM temptimesheet AS t KEY JOIN placement KEY JOIN vacancy KEY JOIN employment KEY JOIN company
      ,temptimesheet AS t KEY JOIN tempdesk
      ,temptimesheet AS t KEY JOIN person
      WHERE t.personid = any(SELECT id FROM ids)
      AND t.Period BETWEEN pStartPeriod AND pEndPeriod
      AND t.PeriodLength = 'W'
      GROUP BY GROUPING sets((),(weekenddate),((weekenddate),rTitle,t.serialnumber,tempname,vacancy.position,companyname,Details))
  ELSE IF userClass = 'AGENCY' THEN
      INSERT INTO IDs( ID) 
        SELECT DISTINCT e.companyid FROM iqxnetuserlink AS l KEY JOIN employment AS e WHERE l.iqxnetuserid = pWebUserID;
      SELECT rTitle,t.serialnumber,string(person.surname,', ',person.forenames) AS tempname,vacancy.position,company.name AS companyname,weekmonthenddate(t.period,t.periodlength) AS weekenddate,
        SUM((SELECT SUM(unitspaid*payrate) FROM temptimesheetline WHERE temptimesheetid = t.temptimesheetid)) AS timesheettotal,'NetReportTimesheetLines&pTimesheetID='
        +t.temptimesheetid AS Details
        FROM temptimesheet AS t KEY JOIN placement KEY JOIN vacancy KEY JOIN employment KEY JOIN company
        ,temptimesheet AS t KEY JOIN tempdesk
        ,temptimesheet AS t KEY JOIN person KEY JOIN pay_employee KEY JOIN company AS agcomp
        WHERE agcomp.companyid = any(SELECT id FROM ids)
        AND weekenddate >= IQXNetCompanyStartDate(agcomp.companyid)
        AND t.Period BETWEEN pStartPeriod AND pEndPeriod
        AND t.PeriodLength = 'W'
        GROUP BY GROUPING sets((),(weekenddate),((weekenddate),rTitle,t.serialnumber,tempname,vacancy.position,companyname,Details))
    ELSE -- CLIENT
      INSERT INTO IDs( ID) 
        SELECT DISTINCT e.companyid FROM iqxnetuserlink AS l KEY JOIN employment AS e WHERE l.iqxnetuserid = pWebUserID;
      SELECT rTitle,t.serialnumber,string(person.surname,', ',person.forenames) AS tempname,vacancy.position,company.name AS companyname,weekmonthenddate(t.period,t.periodlength) AS weekenddate,
        SUM((SELECT SUM(unitscharged*chargerate) FROM temptimesheetline WHERE temptimesheetid = t.temptimesheetid)) AS timesheettotal,'NetReportTimesheetLines&pTimesheetID='
        +t.temptimesheetid AS Details
        FROM temptimesheet AS t KEY JOIN placement KEY JOIN vacancy KEY JOIN employment KEY JOIN company
        ,temptimesheet AS t KEY JOIN tempdesk
        ,temptimesheet AS t KEY JOIN person
        WHERE company.companyid = any(SELECT id FROM ids)
        AND weekenddate >= IQXNetCompanyStartDate(company.companyid)
        AND t.Period BETWEEN pStartPeriod AND pEndPeriod
        AND t.PeriodLength = 'W'
        GROUP BY GROUPING sets((),(weekenddate),(weekenddate,t.serialnumber,tempname,vacancy.position,companyname,t.temptimesheetid))
    END IF
  END IF
END
}