pears.NetOwnerProvTimesheets

CREATE PROCEDURE "pears"."NetOwnerProvTimesheets"( IN "pWebUserID" CHAR(20) ) 
RESULT( "tempprovtimesheetid" CHAR(20),"serialnumber" CHAR(20),"tempname" CHAR(60),"position" CHAR(60),"companyname" CHAR(60),"weekenddate" DATE,"timesheettype" CHAR(1),"completed" SMALLINT ) 
BEGIN
  DECLARE "divid" CHAR(20);
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  SELECT "t"."tempprovtimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname",
    "NetTimesheetEndDate"(1,1,"t"."tempprovtimesheetid") AS "weekenddate",
    IF "NetVacancyHasRateScript"("t"."tempjobtypeid","t"."vacancyid") = 0 THEN 'C'
    ELSE IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W'
        AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL
          AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1
          OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T'
        ELSE 'C'
        endif endif
    endif AS "timesheettype",
    IF "t"."extnumber" = 2 THEN 1 ELSE 0 endif AS "completed"
    FROM "tempprovtimesheet" AS "t" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company"
      ,"tempprovtimesheet" AS "t" KEY JOIN "tempdesk"
      ,"tempprovtimesheet" AS "t" KEY JOIN "person"
    WHERE "person"."divisionid" = "divid" AND "company"."divisionid" = "divid" AND "t"."extnumber" > 0
    AND "weekenddate" > CURRENT date-60
    ORDER BY "weekenddate" ASC,"t"."serialnumber" ASC,"companyname" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerProvTimesheets" IS 
{CREATE PROCEDURE pears."NetOwnerProvTimesheets"( IN "pWebUserID" CHAR(20) ) 
RESULT( "tempprovtimesheetid" CHAR(20),"serialnumber" CHAR(20),"tempname" CHAR(60),"position" CHAR(60),"companyname" CHAR(60),"weekenddate" DATE,"timesheettype" CHAR(1),"completed" SMALLINT ) 
BEGIN
  DECLARE "divid" CHAR(20);
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  SELECT "t"."tempprovtimesheetid","t"."serialnumber","string"("person"."surname",', ',"person"."forenames") AS "tempname","vacancy"."position","company"."name" AS "companyname",
    "NetTimesheetEndDate"(1,1,"t"."tempprovtimesheetid") AS "weekenddate",
    IF "NetVacancyHasRateScript"("t"."tempjobtypeid","t"."vacancyid") = 0 THEN 'C'
    ELSE IF "tempdesk"."desktype" = 'S' THEN 'S' ELSE IF "tempdesk"."desktype" = 'W'
        AND EXISTS(SELECT * FROM "placement" WHERE "placementid" = "t"."placementid" AND "worknormalhours" IS NOT NULL AND "workstarttime" IS NOT NULL
          AND("isnull"("workmonday",0) = 1 OR "isnull"("worktuesday",0) = 1 OR "isnull"("workwednesday",0) = 1 OR "isnull"("workthursday",0) = 1 OR "isnull"("workfriday",0) = 1
          OR "isnull"("worksaturday",0) = 1 OR "isnull"("worksunday",0) = 1)) THEN 'T'
        ELSE 'C'
        endif endif
    endif AS "timesheettype",
    IF "t"."extnumber" = 2 THEN 1 ELSE 0 endif AS "completed"
    FROM "tempprovtimesheet" AS "t" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "company"
      ,"tempprovtimesheet" AS "t" KEY JOIN "tempdesk"
      ,"tempprovtimesheet" AS "t" KEY JOIN "person"
    WHERE "person"."divisionid" = "divid" AND "company"."divisionid" = "divid" AND "t"."extnumber" > 0
    AND "weekenddate" > CURRENT date-60
    ORDER BY "weekenddate" ASC,"t"."serialnumber" ASC,"companyname" ASC
END
}