IQX Quick Menu

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
CREATE PROCEDURE "pears"."NetProvTimesheetRates"( 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),"ShiftDate" DATE ) 
// IQXWeb
BEGIN
  DECLARE "userclass" CHAR(20);
  DECLARE "canEditPayLines" SMALLINT;
  SET "userclass" = (SELECT FIRST "iqxnetuserclassid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  SET "canEditPayLines" = "IQXNetHasPermission"("pWebUserID",'TSCANEDITCALCULATED');
  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",
      (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 "canEditPayLines" = 1 OR "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,"date"("tempprovtimesheetline"."ShiftTime")
      FROM "tempprovtimesheetline" KEY JOIN("temppayband","tempprovtimesheet" AS "t")
      WHERE "tempprovtimesheetline"."tempprovtimesheetid" = "pTempProvTimesheetID"
      ORDER BY "tempprovtimesheetline"."linenumber" 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",
        (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 "canEditPayLines" = 1 OR "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","date"("tempprovtimesheetline"."ShiftTime")
        FROM "tempprovtimesheetline" KEY JOIN("temppayband","tempprovtimesheet" AS "t")
        WHERE "tempprovtimesheetline"."tempprovtimesheetid" = "pTempProvTimesheetID"
        ORDER BY "tempprovtimesheetline"."linenumber" 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",
        (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 "canEditPayLines" = 1 OR "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,"date"("tempprovtimesheetline"."ShiftTime")
        FROM "tempprovtimesheetline" KEY JOIN("temppayband","tempprovtimesheet" AS "t")
        WHERE "tempprovtimesheetline"."tempprovtimesheetid" = "pTempProvTimesheetID"
        ORDER BY "tempprovtimesheetline"."linenumber" ASC
    END IF
  END IF
END /* DOC
2016-09-01 PC Test and doc
2018-07-26 PC modify test routines 
 
tests:
client without edit permission
client with edit permission - two lines, one expenses
owner
candidate
agency
 
*/
/* TEST
call NetTestSetup('');
select * from NetProvTimesheetRates('test.client',null);
expect EOF;
select Tempprovtimesheetlineid,IsExpenses,BandDescription,isnull(UnitDescription,'!') as UD,units,Rate,CanEditUnits,CanEdit,isnull(Rate2,'0') as R2,isnull(Total2,'0') as T2 from NetProvTimesheetRates('test.client','test') order by Tempprovtimesheetlineid;
expect Tempprovtimesheetlineid=TEST1,IsExpenses=0,BandDescription=TestPayBandOne,UD=Day,units=1,Rate=11,CanEditUnits=1,CanEdit=1,R2=0,T2=0;
expect Tempprovtimesheetlineid=TEST2,IsExpenses=1,BandDescription=TestPayBandTwo,UD=!,units=1,Rate=21,CanEditUnits=1,CanEdit=1,R2=0,T2=0;
expect EOF;
select Tempprovtimesheetlineid,IsExpenses,BandDescription,isnull(UnitDescription,'!') as UD,units,Rate,CanEditUnits,CanEdit,isnull(Rate2,'0') as R2,isnull(Total2,'0') as T2 from NetProvTimesheetRates('test.owner','test') order by Tempprovtimesheetlineid;
expect Tempprovtimesheetlineid=TEST1,IsExpenses=0,BandDescription=TestPayBandOne,UD=Day,units=1,Rate=10,CanEditUnits=0,CanEdit=0,R2=11,T2=11;
expect Tempprovtimesheetlineid=TEST2,IsExpenses=1,BandDescription=TestPayBandTwo,UD=!,units=1,Rate=20,CanEditUnits=0,CanEdit=1,R2=21,T2=21;
expect EOF;
select Tempprovtimesheetlineid,IsExpenses,BandDescription,isnull(UnitDescription,'!') as UD,units,Rate,CanEditUnits,CanEdit,isnull(Rate2,'0') as R2,isnull(Total2,'0') as T2 from NetProvTimesheetRates('test.candidate','test') order by Tempprovtimesheetlineid;
expect Tempprovtimesheetlineid=TEST1,IsExpenses=0,BandDescription=TestPayBandOne,UD=Day,units=1,Rate=10,CanEditUnits=0,CanEdit=0,R2=0,T2=0;
expect Tempprovtimesheetlineid=TEST2,IsExpenses=1,BandDescription=TestPayBandTwo,UD=!,units=1,Rate=20,CanEditUnits=0,CanEdit=1,R2=0,T2=0;
expect EOF;
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetRates" IS 
{CREATE PROCEDURE pears."NetProvTimesheetRates"(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), ShiftDate DATE)
// IQXWeb
BEGIN
  DECLARE userclass CHAR(20);
  DECLARE canEditPayLines SMALLINT;
  SET userclass=(SELECT FIRST iqxnetuserclassid FROM iqxnetuser WHERE iqxnetuserid = pWebUserID);
  SET canEditPayLines=IQXNetHasPermission(pWebUserID,'TSCANEDITCALCULATED');
  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,
      (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 canEditPayLines=1 OR 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,DATE(tempprovtimesheetline.ShiftTime)
      FROM tempprovtimesheetline KEY JOIN(temppayband,tempprovtimesheet AS t)
      WHERE tempprovtimesheetline.tempprovtimesheetid = pTempProvTimesheetID ORDER BY
      tempprovtimesheetline.linenumber 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,
        (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 canEditPayLines=1 OR 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,DATE(tempprovtimesheetline.ShiftTime)
        FROM tempprovtimesheetline KEY JOIN(temppayband,tempprovtimesheet AS t)
        WHERE tempprovtimesheetline.tempprovtimesheetid = pTempProvTimesheetID ORDER BY
        tempprovtimesheetline.linenumber 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,
        (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 canEditPayLines=1 OR 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,DATE(tempprovtimesheetline.ShiftTime)
        FROM tempprovtimesheetline KEY JOIN(temppayband,tempprovtimesheet AS t)
        WHERE tempprovtimesheetline.tempprovtimesheetid = pTempProvTimesheetID ORDER BY
        tempprovtimesheetline.linenumber ASC
    END IF
  END IF
END
 
/* DOC
2016-09-01 PC Test and doc
2018-07-26 PC modify test routines 
 
tests:
    client without edit permission
    client with edit permission - two lines, one expenses
    owner
    candidate
    agency
 
*/
 
/* TEST
call NetTestSetup('');
select * from NetProvTimesheetRates('test.client',null);
expect EOF;
select Tempprovtimesheetlineid,IsExpenses,BandDescription,isnull(UnitDescription,'!') as UD,units,Rate,CanEditUnits,CanEdit,isnull(Rate2,'0') as R2,isnull(Total2,'0') as T2 from NetProvTimesheetRates('test.client','test') order by Tempprovtimesheetlineid;
expect Tempprovtimesheetlineid=TEST1,IsExpenses=0,BandDescription=TestPayBandOne,UD=Day,units=1,Rate=11,CanEditUnits=1,CanEdit=1,R2=0,T2=0;
expect Tempprovtimesheetlineid=TEST2,IsExpenses=1,BandDescription=TestPayBandTwo,UD=!,units=1,Rate=21,CanEditUnits=1,CanEdit=1,R2=0,T2=0;
expect EOF;
select Tempprovtimesheetlineid,IsExpenses,BandDescription,isnull(UnitDescription,'!') as UD,units,Rate,CanEditUnits,CanEdit,isnull(Rate2,'0') as R2,isnull(Total2,'0') as T2 from NetProvTimesheetRates('test.owner','test') order by Tempprovtimesheetlineid;
expect Tempprovtimesheetlineid=TEST1,IsExpenses=0,BandDescription=TestPayBandOne,UD=Day,units=1,Rate=10,CanEditUnits=0,CanEdit=0,R2=11,T2=11;
expect Tempprovtimesheetlineid=TEST2,IsExpenses=1,BandDescription=TestPayBandTwo,UD=!,units=1,Rate=20,CanEditUnits=0,CanEdit=1,R2=21,T2=21;
expect EOF;
select Tempprovtimesheetlineid,IsExpenses,BandDescription,isnull(UnitDescription,'!') as UD,units,Rate,CanEditUnits,CanEdit,isnull(Rate2,'0') as R2,isnull(Total2,'0') as T2 from NetProvTimesheetRates('test.candidate','test') order by Tempprovtimesheetlineid;
expect Tempprovtimesheetlineid=TEST1,IsExpenses=0,BandDescription=TestPayBandOne,UD=Day,units=1,Rate=10,CanEditUnits=0,CanEdit=0,R2=0,T2=0;
expect Tempprovtimesheetlineid=TEST2,IsExpenses=1,BandDescription=TestPayBandTwo,UD=!,units=1,Rate=20,CanEditUnits=0,CanEdit=1,R2=0,T2=0;
expect EOF;
 
*/
}
pears.NetProvTimesheetRates

Original SQL

iqx
