pears.NetProvTimesheetRatesExtended

CREATE PROCEDURE "pears"."NetProvTimesheetRatesExtended"( IN "pWebUserID" CHAR(20),IN "pTempProvTimesheetID" CHAR(20) ) 
RESULT( "TempProvTimesheetLineID" CHAR(20),"IsExpenses" SMALLINT,"BandDescription" CHAR(50),"UnitDescription" CHAR(100),"Units" DECIMAL(12,2),"Rate" DECIMAL(12,2),"Total" DECIMAL(12,2),"CanEditUnits" SMALLINT,"CanEdit" SMALLINT,"Rate2" DECIMAL(12,2),"Total2" DECIMAL(12,2),"ShiftDay" CHAR(10) ) 
-- 20130107 change sort order to sort by day and shift type, rather than by line number.
-- 20130222 derived from GenevaNetProvTimesheetRates
BEGIN
  DECLARE "userclass" CHAR(20);
  SET "userclass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  IF "userclass" = 'CLIENT' THEN
    SELECT "TempProvTimesheetLine"."TempProvTimesheetLineID",
      (IF "isnull"("temppayband"."payrollflag",'') = 'EXPENSES' OR "isnull"("temppayband"."isexpenses",0) = 1 THEN 1 ELSE 0 endif) AS "IsExpenses",
      "temppayband"."description" AS "PayType",
      (IF "IsExpenses" = 1 THEN "tempprovtimesheetline"."description" ELSE "temppayband"."unit" endif),
      "nullif"("tempprovtimesheetline"."unitscharged",0.0) AS "units","nullif"("tempprovtimesheetline"."chargerate",0.0) AS "rate","isnull"("units"*"rate",0.0) AS "total",
      (IF "units" IS NULL OR "isnull"("tempprovtimesheetline"."description",'') = 'Units Edited' OR "NetVacancyHasRateScript"("t"."tempjobtypeid","t"."vacancyid") = 0 THEN 1 ELSE 0 endif) AS "CanEditUnits",
      (IF "IsExpenses" = 1 OR "CanEditUnits" = 1 THEN 1 ELSE 0 endif) AS "CanEdit",
      NULL,NULL,"dateformat"("shifttime",'Ddd dd/mm') AS "ShiftDay"
      FROM "tempprovtimesheetline" KEY JOIN("temppayband","tempprovtimesheet" AS "t")
      WHERE "tempprovtimesheetline"."tempprovtimesheetid" = "pTempProvTimesheetID"
      ORDER BY "shifttime" ASC,"PayType" ASC
  ELSE
    IF "userclass" = 'OWNER' THEN
      SELECT "TempProvTimesheetLine"."TempProvTimesheetLineID",
        (IF "isnull"("temppayband"."payrollflag",'') = 'EXPENSES' OR "isnull"("temppayband"."isexpenses",0) = 1 THEN 1 ELSE 0 endif) AS "IsExpenses",
        "temppayband"."description" AS "PayType",
        (IF "IsExpenses" = 1 THEN "tempprovtimesheetline"."description" ELSE "temppayband"."unit" endif),
        "nullif"("tempprovtimesheetline"."unitspaid",0.0) AS "units","nullif"("tempprovtimesheetline"."payrate",0.0) AS "rate","isnull"("units"*"rate",0.0) AS "total",
        (IF "units" IS NULL OR "isnull"("tempprovtimesheetline"."description",'') = 'Units Edited' OR "NetVacancyHasRateScript"("t"."tempjobtypeid","t"."vacancyid") = 0 THEN 1 ELSE 0 endif) AS "CanEditUnits",
        (IF "IsExpenses" = 1 OR "CanEditUnits" = 1 THEN 1 ELSE 0 endif) AS "CanEdit",
        "nullif"("tempprovtimesheetline"."chargerate",0.0) AS "rate2","isnull"("units"*"rate2",0.0) AS "total2","dateformat"("shifttime",'Ddd dd/mm') AS "ShiftDay"
        FROM "tempprovtimesheetline" KEY JOIN("temppayband","tempprovtimesheet" AS "t")
        WHERE "tempprovtimesheetline"."tempprovtimesheetid" = "pTempProvTimesheetID"
        ORDER BY "shifttime" ASC,"PayType" ASC
    ELSE
      SELECT "TempProvTimesheetLine"."TempProvTimesheetLineID",
        (IF "isnull"("temppayband"."payrollflag",'') = 'EXPENSES' OR "isnull"("temppayband"."isexpenses",0) = 1 THEN 1 ELSE 0 endif) AS "IsExpenses",
        "temppayband"."description" AS "PayType",
        (IF "IsExpenses" = 1 THEN "tempprovtimesheetline"."description" ELSE "temppayband"."unit" endif),
        "nullif"("tempprovtimesheetline"."unitspaid",0.0) AS "units","nullif"("tempprovtimesheetline"."payrate",0.0) AS "rate","isnull"("units"*"rate",0.0) AS "total",
        (IF "units" IS NULL OR "isnull"("tempprovtimesheetline"."description",'') = 'Units Edited' OR "NetVacancyHasRateScript"("t"."tempjobtypeid","t"."vacancyid") = 0 THEN 1 ELSE 0 endif) AS "CanEditUnits",
        (IF "IsExpenses" = 1 OR "CanEditUnits" = 1 THEN 1 ELSE 0 endif) AS "CanEdit",
        NULL,NULL,"dateformat"("shifttime",'Ddd dd/mm') AS "ShiftDay"
        FROM "tempprovtimesheetline" KEY JOIN("temppayband","tempprovtimesheet" AS "t")
        WHERE "tempprovtimesheetline"."tempprovtimesheetid" = "pTempProvTimesheetID"
        ORDER BY "shifttime" ASC,"PayType" ASC
    END IF
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetRatesExtended" IS 
{CREATE PROCEDURE pears."NetProvTimesheetRatesExtended"( IN "pWebUserID" CHAR(20),IN "pTempProvTimesheetID" CHAR(20) ) 
RESULT( "TempProvTimesheetLineID" CHAR(20),"IsExpenses" SMALLINT,"BandDescription" CHAR(50),"UnitDescription" CHAR(100),"Units" DECIMAL(12,2),"Rate" DECIMAL(12,2),"Total" DECIMAL(12,2),"CanEditUnits" SMALLINT,"CanEdit" SMALLINT,"Rate2" DECIMAL(12,2),"Total2" DECIMAL(12,2),"ShiftDay" CHAR(10) ) 
-- 20130107 change sort order to sort by day and shift type, rather than by line number.
-- 20130222 derived from GenevaNetProvTimesheetRates
BEGIN
  DECLARE "userclass" CHAR(20);
  SET "userclass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  IF "userclass" = 'CLIENT' THEN
    SELECT "TempProvTimesheetLine"."TempProvTimesheetLineID",
      (IF "isnull"("temppayband"."payrollflag",'') = 'EXPENSES' OR "isnull"("temppayband"."isexpenses",0) = 1 THEN 1 ELSE 0 endif) AS "IsExpenses",
      "temppayband"."description" AS "PayType",
      (IF "IsExpenses" = 1 THEN "tempprovtimesheetline"."description" ELSE "temppayband"."unit" endif),
      "nullif"("tempprovtimesheetline"."unitscharged",0.0) AS "units","nullif"("tempprovtimesheetline"."chargerate",0.0) AS "rate","isnull"("units"*"rate",0.0) AS "total",
      (IF "units" IS NULL OR "isnull"("tempprovtimesheetline"."description",'') = 'Units Edited' OR "NetVacancyHasRateScript"("t"."tempjobtypeid","t"."vacancyid") = 0 THEN 1 ELSE 0 endif) AS "CanEditUnits",
      (IF "IsExpenses" = 1 OR "CanEditUnits" = 1 THEN 1 ELSE 0 endif) AS "CanEdit",
      NULL,NULL,"dateformat"("shifttime",'Ddd dd/mm') AS "ShiftDay"
      FROM "tempprovtimesheetline" KEY JOIN("temppayband","tempprovtimesheet" AS "t")
      WHERE "tempprovtimesheetline"."tempprovtimesheetid" = "pTempProvTimesheetID"
      ORDER BY "shifttime" ASC,"PayType" ASC
  ELSE
    IF "userclass" = 'OWNER' THEN
      SELECT "TempProvTimesheetLine"."TempProvTimesheetLineID",
        (IF "isnull"("temppayband"."payrollflag",'') = 'EXPENSES' OR "isnull"("temppayband"."isexpenses",0) = 1 THEN 1 ELSE 0 endif) AS "IsExpenses",
        "temppayband"."description" AS "PayType",
        (IF "IsExpenses" = 1 THEN "tempprovtimesheetline"."description" ELSE "temppayband"."unit" endif),
        "nullif"("tempprovtimesheetline"."unitspaid",0.0) AS "units","nullif"("tempprovtimesheetline"."payrate",0.0) AS "rate","isnull"("units"*"rate",0.0) AS "total",
        (IF "units" IS NULL OR "isnull"("tempprovtimesheetline"."description",'') = 'Units Edited' OR "NetVacancyHasRateScript"("t"."tempjobtypeid","t"."vacancyid") = 0 THEN 1 ELSE 0 endif) AS "CanEditUnits",
        (IF "IsExpenses" = 1 OR "CanEditUnits" = 1 THEN 1 ELSE 0 endif) AS "CanEdit",
        "nullif"("tempprovtimesheetline"."chargerate",0.0) AS "rate2","isnull"("units"*"rate2",0.0) AS "total2","dateformat"("shifttime",'Ddd dd/mm') AS "ShiftDay"
        FROM "tempprovtimesheetline" KEY JOIN("temppayband","tempprovtimesheet" AS "t")
        WHERE "tempprovtimesheetline"."tempprovtimesheetid" = "pTempProvTimesheetID"
        ORDER BY "shifttime" ASC,"PayType" ASC
    ELSE
      SELECT "TempProvTimesheetLine"."TempProvTimesheetLineID",
        (IF "isnull"("temppayband"."payrollflag",'') = 'EXPENSES' OR "isnull"("temppayband"."isexpenses",0) = 1 THEN 1 ELSE 0 endif) AS "IsExpenses",
        "temppayband"."description" AS "PayType",
        (IF "IsExpenses" = 1 THEN "tempprovtimesheetline"."description" ELSE "temppayband"."unit" endif),
        "nullif"("tempprovtimesheetline"."unitspaid",0.0) AS "units","nullif"("tempprovtimesheetline"."payrate",0.0) AS "rate","isnull"("units"*"rate",0.0) AS "total",
        (IF "units" IS NULL OR "isnull"("tempprovtimesheetline"."description",'') = 'Units Edited' OR "NetVacancyHasRateScript"("t"."tempjobtypeid","t"."vacancyid") = 0 THEN 1 ELSE 0 endif) AS "CanEditUnits",
        (IF "IsExpenses" = 1 OR "CanEditUnits" = 1 THEN 1 ELSE 0 endif) AS "CanEdit",
        NULL,NULL,"dateformat"("shifttime",'Ddd dd/mm') AS "ShiftDay"
        FROM "tempprovtimesheetline" KEY JOIN("temppayband","tempprovtimesheet" AS "t")
        WHERE "tempprovtimesheetline"."tempprovtimesheetid" = "pTempProvTimesheetID"
        ORDER BY "shifttime" ASC,"PayType" ASC
    END IF
  END IF
END
}