pears.NetClientEmployeeList

CREATE PROCEDURE "pears"."NetClientEmployeeList"( IN "pwebuserID" CHAR(20),IN "pKeyName" CHAR(50) DEFAULT NULL,IN "pFromDate" CHAR(30) DEFAULT NULL,IN "pToDate" CHAR(30) DEFAULT NULL,IN "pSlice" INTEGER DEFAULT NULL,IN "pSliceSize" INTEGER DEFAULT NULL ) 
RESULT( "ClientContact" CHAR(50),"ClientName" CHAR(60),"CandidateName" CHAR(50),"LastTimesheetID" CHAR(20),"LastPosition" CHAR(60),"LastTimesheetDate" DATE,"CandidateActive" SMALLINT,"CandidatePersonID" CHAR(20) ) 
BEGIN
  //IQXWeb	
  DECLARE "x" INTEGER;
  DECLARE "y" INTEGER;
  DECLARE "SearchFromDate" DATE;
  DECLARE "SearchToDate" DATE;
  DECLARE "CurrentStates" CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE "IDs"(
    "ID" CHAR(20) NULL,
    ) NOT transactional;
  INSERT INTO "IDs"( "ID" ) 
    SELECT DISTINCT "e"."companyid" FROM "iqxnetuserlink" AS "l" KEY JOIN "employment" AS "e" WHERE "l"."iqxnetuserid" = "pWebUserID";
  SET "x" = "isnull"("pSliceSize",20);
  SET "y" = "isnull"("pSlice",0)*"x"+1;
  SET "pKeyName" = "isnull"("nullif"("trim"("pKeyName"),''),'')+'%';
  SET "SearchFromDate" = "isnull"("IQXNetStringToDate"("pFromDate"),CURRENT date-1000);
  SET "SearchToDate" = "isnull"("IQXNetStringToDate"("pToDate"),CURRENT DATE+10);
  SET "CurrentStates" = (SELECT FIRST "personcurrentstates" FROM "params" ORDER BY "dbid" ASC);
  SET "CurrentStates" = '['+"isnull"("nullif"("trim"("CurrentStates"),''),'C')+']';
  SELECT top "x" START at "y"
    "cp"."name" AS "ClientContact",
    "c"."name" AS "ClientName",
    "p"."name" AS "CandidateName",
    (SELECT FIRST "ts"."temptimesheetid" FROM "temptimesheet" AS "ts" KEY JOIN "placement" KEY JOIN "employment" WHERE "ts"."personid" = "p"."personid" AND "employment"."companyid" = any(SELECT "id" FROM "ids") AND "ts"."enddate" BETWEEN "SearchFromDate" AND "SearchToDate" ORDER BY "ts"."enddate" DESC) AS "tsid",
    "e"."position" AS "LastPosition",
    "ts"."EndDate" AS "LastTimesheetDate",
    IF "p"."status" LIKE "CurrentStates" THEN 1 ELSE 0 endif AS "CandidateActive",
    "p"."PersonID" AS "CandidatePersonID"
    FROM "person" AS "p" JOIN "temptimesheet" AS "ts" ON "ts"."temptimesheetid" = "tsid"
      ,"temptimesheet" AS "ts" KEY JOIN "placement" KEY JOIN "employment" AS "e"
      ,"placement" KEY JOIN "vacancy" KEY JOIN "employment" KEY JOIN("company" AS "c","person" AS "cp")
    WHERE "p"."keyname" LIKE "pKeyName"
    AND "p"."personid" = any(SELECT "ts"."personid" FROM "temptimesheet" AS "ts" KEY JOIN "placement" KEY JOIN "employment" WHERE "employment"."companyid" = any(SELECT "id" FROM "ids") AND "ts"."enddate" BETWEEN "SearchFromDate" AND "SearchToDate")
    ORDER BY "LastTimesheetDate" DESC
END /* DOC
2018-10-15 PC IW-695 View previous temps & search for them
2018-10-29 MHS IW-696 re-design using indexable ts.enddate field
2018-11-01 ET IW-696 change datatype for result column LastPosition from char(20) to date.  Updated the Test to accept date in uk format
2018-12-19 ET IW-696 added wildcard to end of pKeyName so that if you only specify part of the key name records which start with that part will be returned.
2019-01-18 PC IW-799 previous temps - sort by using most recent timesheet date instead of Keyname
*/
/* TEST
call nettestsetup('');
insert into temptimesheet (temptimesheetid,enddate,personid,placementid,period,periodlength) 
on existing update defaults off values ('TEST','2005-01-11','TEST','TEST',200101,'W');
select * from NetClientEmployeeList('test.client', 'XXXXXX', null, null, null, null);
expect EOF;
select * from NetClientEmployeeList('test.client', null, '2001-01-01', '2001-02-01', null, null);
expect EOF;
select * from NetClientEmployeeList('test.client', null, '2005-01-01', '2005-02-01', null, null);
expect CandidatePersonID=TEST, LastTimesheetDate=11/01/2005;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientEmployeeList" IS 
{CREATE PROCEDURE pears."NetClientEmployeeList"(IN pwebuserID CHAR(20),IN pKeyName CHAR(50) DEFAULT NULL, IN pFromDate CHAR(30) DEFAULT NULL, IN pToDate CHAR(30) DEFAULT NULL, IN pSlice INTEGER DEFAULT NULL, pSliceSize INTEGER DEFAULT NULL)
RESULT(ClientContact CHAR(50), ClientName CHAR(60), CandidateName CHAR(50), LastTimesheetID CHAR(20), LastPosition CHAR(60), LastTimesheetDate DATE, CandidateActive SMALLINT, CandidatePersonID CHAR(20))
BEGIN
//IQXWeb	
  DECLARE x INTEGER;
  DECLARE y INTEGER;
  DECLARE SearchFromDate DATE;
  DECLARE SearchToDate DATE;
  DECLARE CurrentStates CHAR(20);
  DECLARE LOCAL TEMPORARY TABLE IDs(
    ID CHAR(20) NULL,
    ) NOT transactional;
  INSERT INTO IDs( ID) 
  SELECT DISTINCT e.companyid FROM iqxnetuserlink AS l KEY JOIN employment AS e WHERE l.iqxnetuserid = pWebUserID;
 
  SET x=isnull(pSliceSize,20);
  SET y=isnull(pSlice,0)*x+1;
  SET pKeyName=isnull(NULLIF(TRIM(pKeyName),''),'')+'%';
  SET SearchFromDate=isnull(IQXNetStringToDate(pFromDate),CURRENT DATE - 1000);
  SET SearchToDate=isnull(IQXNetStringToDate(pToDate), CURRENT DATE + 10);
  SET CurrentStates=(SELECT FIRST personcurrentstates FROM params ORDER BY dbid);
  SET CurrentStates='['+isnull(NULLIF(TRIM(CurrentStates),''),'C')+']';
 
  SELECT top x START at y
    cp.name AS ClientContact,
    c.name AS ClientName,
    p.name AS CandidateName,
    (SELECT FIRST ts.temptimesheetid FROM temptimesheet ts KEY JOIN placement KEY JOIN employment WHERE ts.personid=p.personid AND employment.companyid IN (SELECT id FROM ids) AND ts.enddate BETWEEN SearchFromDate AND SearchToDate ORDER BY ts.enddate DESC) AS tsid,
    e.position AS LastPosition,
    ts.EndDate AS LastTimesheetDate,
    IF p.status LIKE CurrentStates THEN 1 ELSE 0 endif AS CandidateActive,
    p.PersonID AS CandidatePersonID
  FROM
    person p JOIN temptimesheet ts ON ts.temptimesheetid=tsid, 
    temptimesheet ts KEY JOIN placement KEY JOIN employment e,
    placement KEY JOIN vacancy KEY JOIN employment KEY JOIN (company c, person cp)
  WHERE
    p.keyname LIKE pKeyName 
    AND p.personid IN (SELECT ts.personid FROM temptimesheet ts KEY JOIN placement KEY JOIN employment WHERE employment.companyid IN (SELECT id FROM ids) AND ts.enddate BETWEEN SearchFromDate AND SearchToDate)
  ORDER BY
    LastTimesheetDate DESC
 
END
 
/* DOC
2018-10-15 PC IW-695 View previous temps & search for them
2018-10-29 MHS IW-696 re-design using indexable ts.enddate field
2018-11-01 ET IW-696 change datatype for result column LastPosition from char(20) to date.  Updated the Test to accept date in uk format
2018-12-19 ET IW-696 added wildcard to end of pKeyName so that if you only specify part of the key name records which start with that part will be returned.
2019-01-18 PC IW-799 previous temps - sort by using most recent timesheet date instead of Keyname
*/
 
/* TEST
call nettestsetup('');
insert into temptimesheet (temptimesheetid,enddate,personid,placementid,period,periodlength) 
on existing update defaults off values ('TEST','2005-01-11','TEST','TEST',200101,'W');
select * from NetClientEmployeeList('test.client', 'XXXXXX', null, null, null, null);
expect EOF;
select * from NetClientEmployeeList('test.client', null, '2001-01-01', '2001-02-01', null, null);
expect EOF;
select * from NetClientEmployeeList('test.client', null, '2005-01-01', '2005-02-01', null, null);
expect CandidatePersonID=TEST, LastTimesheetDate=11/01/2005;
*/
}