pears.NetAgencyProvTimesheets

CREATE PROCEDURE "pears"."NetAgencyProvTimesheets"( 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
  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" KEY JOIN "pay_employee" KEY JOIN "company" AS "agcomp" KEY JOIN "employment" AS "agemp" KEY JOIN "iqxnetuserlink"
    WHERE "iqxnetuserlink"."iqxnetuserid" = "pWebUserID"
    AND "t"."extnumber" > 0
    AND "weekenddate" > CURRENT date-60
    ORDER BY "weekenddate" ASC,"t"."serialnumber" ASC,"tempname" ASC
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyProvTimesheets" IS 
{CREATE PROCEDURE pears."NetAgencyProvTimesheets"( 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
  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" KEY JOIN "pay_employee" KEY JOIN "company" AS "agcomp" KEY JOIN "employment" AS "agemp" KEY JOIN "iqxnetuserlink"
    WHERE "iqxnetuserlink"."iqxnetuserid" = "pWebUserID"
    AND "t"."extnumber" > 0
    AND "weekenddate" > CURRENT date-60
    ORDER BY "weekenddate" ASC,"t"."serialnumber" ASC,"tempname" ASC
END
}