====== pears.NetProvTimesheetRates ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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;

*/
}
</code>