pears.NetProvTimesheetExpenseRatesSelect

CREATE PROCEDURE "pears"."NetProvTimesheetExpenseRatesSelect"( IN "pWebUserID" CHAR(20),IN "pTempProvTimesheetID" CHAR(20) ) 
RESULT( "TempPaybandID" CHAR(20),"description" CHAR(50),"Units" CHAR(20),"SortOrder" CHAR(10) ) 
// IQXWeb
BEGIN
  IF "pTempProvTimesheetID" IS NULL OR NOT "pTempProvTimesheetID" = any(SELECT "TempProvTimeSheetID" FROM "tempprovtimesheet" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID" UNION SELECT "TempProvTimeSheetID" FROM "tempprovtimesheet" KEY JOIN "person" KEY JOIN "iqxnetuserlink" KEY JOIN "iqxnetuser" AS "u" WHERE "u"."iqxnetuserid" = "pWebUserID") THEN
    SELECT '','','','' WHERE 0 = 1; -- empty result
    RETURN
  END IF;
  SELECT DISTINCT
    "b"."temppaybandid",
    "b"."description",
    "b"."unit",
    "b"."sortorder"
    FROM "temppayband" AS "b"
    --key join tempprovtimesheetline 
    WHERE("isexpenses" = 1 OR "b"."PayrollFlag" LIKE '%expenses%')
    AND("b"."divisionid" IS NULL OR "b"."divisionid" = (SELECT "p"."divisionid" FROM "TempProvTimeSheet" AS "t" KEY JOIN "person" AS "p" WHERE "t"."tempprovtimesheetid" = "pTempProvTimesheetID"))
    ORDER BY "b"."sortorder" ASC
END /* DOC
2016-11-09 PC insert an expense line into tempprovtimesheetline
2016-11-28 PC modify filter for selecting expense lines
2017-06-09 PC modify to select all rates in a division since linking it to a timesheet only works if there is a value there to start with
2018-12-17 MHS fix faulty division logic and tests
 
IW-95
IW-102
 
Inputs:
pWebUserID
pTempProvTimesheetID
 
Outputs:
TempPaybandID
description
Units
 
Tests:
invalid pWebUserID
valid pWebUserID and invalid pTempProvTimesheetID
all valid but no expense line
all valid
 
*/
/* TEST
call NetTestSetup('');
select * from NetProvTimesheetExpenseRatesSelect(null,null);
expect EOF;
select * from NetProvTimesheetExpenseRatesSelect('test.client',null);
expect EOF;
insert into tempprovtimesheet (tempprovtimesheetid,period,periodlength,personid) on existing update values ('TEST',200101,'W','TEST');
insert into temppayband (TempPaybandID,Description,Unit,isExpenses,DivisionID,sortorder) on existing update defaults off values ('EXPENSETEST','Expenses','Amount',1,null,-1);
select * from NetProvTimesheetExpenseRatesSelect('test.client','TEST') order by isnull(sortorder,0);
expect TempPaybandID=EXPENSETEST;
delete from temppayband where temppaybandid='EXPENSETEST';
 
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetExpenseRatesSelect" IS 
{CREATE PROCEDURE pears."NetProvTimesheetExpenseRatesSelect"(IN pWebUserID CHAR(20),IN pTempProvTimesheetID CHAR(20))
RESULT(TempPaybandID CHAR(20),description CHAR(50),Units CHAR(20),SortOrder CHAR(10))
// IQXWeb
BEGIN  
    IF pTempProvTimesheetID IS NULL OR pTempProvTimesheetID NOT IN (SELECT TempProvTimeSheetID FROM tempprovtimesheet KEY JOIN vacancy KEY JOIN employment KEY JOIN iqxnetuserlink WHERE iqxnetuserid=pWebUserID UNION SELECT TempProvTimeSheetID FROM tempprovtimesheet KEY JOIN person KEY JOIN iqxnetuserlink KEY JOIN iqxnetuser u WHERE u.iqxnetuserid=pWebUserID) THEN
        SELECT '','','','' WHERE 0=1; -- empty result
        RETURN;
    END IF;
    SELECT DISTINCT
        b.temppaybandid,
        b.description,
        b.unit,
        b.sortorder
    FROM 
        temppayband b 
        --key join tempprovtimesheetline 
    WHERE 
        (isexpenses=1 OR b.PayrollFlag LIKE '%expenses%' )
        AND
        (b.divisionid IS NULL OR b.divisionid=(SELECT p.divisionid FROM TempProvTimeSheet t KEY JOIN person p WHERE t.tempprovtimesheetid=pTempProvTimesheetID))
    ORDER BY 
        b.sortorder
END
 
/* DOC
2016-11-09 PC insert an expense line into tempprovtimesheetline
2016-11-28 PC modify filter for selecting expense lines
2017-06-09 PC modify to select all rates in a division since linking it to a timesheet only works if there is a value there to start with
2018-12-17 MHS fix faulty division logic and tests
 
IW-95
IW-102
 
Inputs:
    pWebUserID
    pTempProvTimesheetID
 
Outputs:
    TempPaybandID
    description
    Units
 
Tests:
    invalid pWebUserID
    valid pWebUserID and invalid pTempProvTimesheetID
    all valid but no expense line
    all valid
 
*/
 
/* TEST
call NetTestSetup('');
select * from NetProvTimesheetExpenseRatesSelect(null,null);
expect EOF;
select * from NetProvTimesheetExpenseRatesSelect('test.client',null);
expect EOF;
insert into tempprovtimesheet (tempprovtimesheetid,period,periodlength,personid) on existing update values ('TEST',200101,'W','TEST');
insert into temppayband (TempPaybandID,Description,Unit,isExpenses,DivisionID,sortorder) on existing update defaults off values ('EXPENSETEST','Expenses','Amount',1,null,-1);
select * from NetProvTimesheetExpenseRatesSelect('test.client','TEST') order by isnull(sortorder,0);
expect TempPaybandID=EXPENSETEST;
delete from temppayband where temppaybandid='EXPENSETEST';
 
 
*/
}