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"."NetAgencyCurrentRequirementSearch"( IN "pWebUserID" CHAR(20),IN "pSlice" INTEGER DEFAULT 0,IN "pSliceSize" INTEGER DEFAULT 20,IN "pLineID" CHAR(20) DEFAULT NULL,IN "pStatus" CHAR(1),IN "pCompanyName" CHAR(50) DEFAULT NULL,IN "pPermOrShift" CHAR(1) DEFAULT NULL,IN "pPosition" CHAR(50) DEFAULT NULL,IN "pRef" CHAR(10) DEFAULT NULL,IN "pSearchStartDate" CHAR(26) DEFAULT NULL,IN "pSearchEndDate" CHAR(26) DEFAULT NULL ) 
RESULT( "LineID" CHAR(30),"StartDate" DATE,"StartTime" TIME,"FinishDate" DATE,"FinishTime" TIME,"Position" CHAR(50),"ShiftDescription" CHAR(50),"CompanyName" CHAR(60),"ContactName" CHAR(60),"Status" CHAR(40),"ConsultantName" CHAR(60),"OurRef" CHAR(20),"TheirRef" CHAR(50),"ContractRef" CHAR(20),"CascadeDateTime" TIMESTAMP,"CascadeLevel" SMALLINT,"WhenEntered" TIMESTAMP,"SubmittedCandidates" long VARCHAR,"LongDescription" long VARCHAR,"Interest" long VARCHAR,"Declined" CHAR(50),"Notes" long VARCHAR ) 
BEGIN
  // IQXWeb
  DECLARE @SecAgID CHAR(20);
  DECLARE @PlacedCode CHAR(1);
  DECLARE "SearchStr" CHAR(100);
  DECLARE "x" INTEGER;
  DECLARE "y" INTEGER;
  DECLARE "CompanySearch" CHAR(60);
  DECLARE "PositionSearch" CHAR(60);
  DECLARE LOCAL TEMPORARY TABLE "DSet"(
    "LineID" CHAR(30) NULL,
    "StartDate" DATE NULL,
    "StartTime" TIME NULL,
    "FinishDate" DATE NULL,
    "FinishTime" TIME NULL,
    "Position" CHAR(50) NULL,
    "ShiftDescription" CHAR(50) NULL,
    "CompanyName" CHAR(60) NULL,
    "ContactName" CHAR(30) NULL,
    "Status" CHAR(40) NULL,
    "ConsultantName" CHAR(60) NULL,
    "OurRef" CHAR(20) NULL,
    "TheirRef" CHAR(50) NULL,
    "ContractRef" CHAR(20) NULL,
    "CascadeDateTime" TIMESTAMP NULL,
    "CascadeLevel" SMALLINT NULL,
    "WhenEntered" TIMESTAMP NULL,
    "SubmittedCandidates" long VARCHAR NULL,
    "LongDescription" long VARCHAR NULL,
    "Interest" long VARCHAR NULL,
    "Declined" CHAR(50) NULL,
    "Notes" long VARCHAR NULL,
    "LineStatus" CHAR(1) NULL,
    ) NOT transactional;
  SET @SecAgID = (SELECT top 1 "employment"."companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" ORDER BY "isnull"("employment"."leavedate",CURRENT DATE) DESC);
  SET @PlacedCode = (SELECT top 1 "s"."status" FROM "status" AS "s" WHERE "type" = 'R' AND "name" = 'Placed' ORDER BY "sortorder" ASC);
  SET "x" = "isnull"("pSliceSize",20);
  SET "y" = "x"*"isnull"("pSlice",0)+1;
  SET "CompanySearch" = "isnull"("pCompanyName",'');
  SET "PositionSearch" = "isnull"("pPosition",'');
  IF "isnull"("pRef",'') <> '' THEN
    SET "CompanySearch" = NULL;
    SET "PositionSearch" = NULL;
    SET "pStatus" = NULL;
    SET "pPermOrShift" = NULL
  END IF;
  INSERT INTO "DSet"
    SELECT 'V'+"v"."VacancyID" AS "LineID",
      "v"."StartDate",
      NULL,
      "v"."FinishDate",
      NULL AS "FinishTime",
      "v"."Position" AS "Position",
      '' AS "ShiftDescription",
      "c"."Name" AS "CompanyName",
      "Person"."Name" AS "ContactName",
      (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status",
      "s"."Name" AS "Consultant",
      "v"."RefCode" AS "OurRef",
      "v"."TheirRef" AS "TheirRef",
      "v"."ContractRef",
      "v"."CascadeDateTime",
      "v"."CascadeLevel",
      "v"."WhenEntered",
      (SELECT "list"(DISTINCT "person"."name",', ') FROM "vacancy" KEY JOIN "progress" KEY JOIN "person" KEY JOIN "pay_employee" WHERE "vacancy"."vacancyid" = "v"."vacancyid" AND "pay_employee"."secondaryagencyid" = @secagid) AS "SubmittedCandidates",
      "v"."OtherNotes" AS "LongDescription",
      "isnull"(
      (SELECT top 1 'Declined Interest at '+CAST("dateformat"("cv"."whendeclined",'dd/mm/yyyy hh:nn') AS CHAR) FROM "cascadedvacancy" AS "cv" RIGHT OUTER JOIN "vacancy" AS "va" WHERE "cv"."whendeclined" IS NOT NULL AND("cv"."secondaryagencyid" IS NULL OR "cv"."secondaryagencyid" = @secagid) AND "cv"."vacancyid" = "v"."vacancyid" ORDER BY "whendeclined" DESC),
      (SELECT top 1 "ce"."Description"+' at '+CAST("dateformat"("ce"."contactdate",'dd/mm/yyyy') AS CHAR)+' '+CAST("dateformat"("ce"."contacttime",'hh:nn') AS CHAR)+' sent to '+"s"."name"+'. Note: '+"ce"."notes" FROM "cascadedvacancy" AS "cv" RIGHT OUTER JOIN "vacancy" AS "va" KEY JOIN "contactevent" AS "ce" KEY JOIN "staff" AS "s","contactevent" AS "ce" KEY JOIN "employment" AS "e" WHERE(("ce"."classcode" = 'CI') OR("ce"."classcode" = 'DI')) AND("cv"."secondaryagencyid" IS NULL OR "cv"."secondaryagencyid" = @secagid) AND "ce"."vacancyid" = "v"."vacancyid" AND "e"."companyid" = "cv"."secondaryagencyid" ORDER BY "ce"."description" DESC,"contactdate" ASC,"contacttime" DESC)) AS "Interest",
      (SELECT top 1 "cv"."whendeclined" FROM "cascadedvacancy" AS "cv" WHERE "cv"."vacancyid" = "v"."vacancyid" AND "cv"."secondaryagencyid" = @secagid ORDER BY "whendeclined" DESC) AS "Declined",
      "v"."othernotes",
      "v"."status" AS "LineStatus"
      FROM "Vacancy" AS "v" KEY JOIN "Employment" AS "e" KEY JOIN("Company" AS "c","Person")
        ,"Vacancy" AS "v" LEFT OUTER JOIN "CascadedVacancy" AS "cv"
        ,"Company" AS "c" KEY JOIN "CompanyStatus" AS "compstat"
        ,"Company" AS "c" LEFT OUTER JOIN "division" AS "d"
        ,"Vacancy" AS "v" KEY JOIN "staff" AS "s"
      WHERE "Declined" IS NULL
      AND "cv"."whendeclined" IS NULL
      AND(("Interest" LIKE 'Confirm%') OR("Interest" IS NULL))
      AND "isnull"("cv"."secondaryagencyid","c"."companyid") = @secagid
      AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S' UNION ALL
    SELECT 'P'+"p"."TempShiftPlanID" AS "LineID",
      "isnull"("ts"."shiftdate","p"."ShiftDate"),
      "isnull"("ts"."timefrom","p"."TimeFrom"),
      (IF "isnull"("ts"."timefrom","p"."TimeFrom") < "isnull"("ts"."timefrom","p"."TimeTo") THEN "isnull"("ts"."shiftdate","p"."ShiftDate") ELSE "isnull"("ts"."shiftdate","p"."ShiftDate")+1 endif) AS "FinishDate",
      "isnull"("ts"."timeto","p"."TimeTo") AS "FinishTime",
      "v"."Position",
      "p"."Description" AS "ShiftDescription",
      "c"."Name" AS "CompanyName",
      "Person"."Name" AS "ContactName",
      (CASE "isnull"("ts"."state",'U')
      WHEN 'B' THEN 'Booked'
      WHEN 'P' THEN 'Provisional'
      WHEN 'W' THEN 'Worked'
      WHEN 'C' THEN 'Cancelled'
      WHEN 'U' THEN 'Unfilled' END) AS "Status",
      (SELECT "Name" FROM "Staff" WHERE "Staff"."StaffID" = "v"."StaffID") AS "ConsultantName",
      CAST("p"."ShiftSerialNumber" AS CHAR(20)) AS "OurRef",
      "p"."ReferenceCode" AS "TheirRef",
      "v"."ContractRef",
      "p"."CascadeDateTime",
      "p"."CascadeLevel",
      "p"."WhenEntered",
      (SELECT "list"(DISTINCT "person"."name",', ') FROM "tempshiftprogress" KEY JOIN "person" KEY JOIN "pay_employee" WHERE "tempshiftprogress"."tempshiftplanid" = "p"."tempshiftplanid" AND "pay_employee"."secondaryagencyid" = @secagid) AS "SubmittedCandidates",
      '' AS "LongDescription",
      (SELECT top 1 "ce"."Description"+' at '+CAST("dateformat"("ce"."contactdate",'dd/mm/yyyy') AS CHAR)+' '+CAST("dateformat"("ce"."contacttime",'hh:nn') AS CHAR)+' sent to '+"s"."name"+'. Note: '+"ce"."notes" FROM "cascadedvacancy" AS "cv" RIGHT OUTER JOIN "vacancy" AS "va" KEY JOIN "contactevent" AS "ce" KEY JOIN "staff" AS "s" WHERE(("ce"."classcode" = 'CI') OR("ce"."classcode" = 'DI')) AND("cv"."secondaryagencyid" IS NULL OR "cv"."secondaryagencyid" = @secagid) AND "ce"."vacancyid" = "v"."vacancyid" AND "substr"("ce"."description","charindex"(':',"ce"."description")+1) = "OurRef" ORDER BY "ce"."description" DESC,"contactdate" ASC,"contacttime" DESC) AS "Interest",
      (SELECT top 1 "cs"."WhenDeclined" FROM "cascadedshift" AS "cs" WHERE "cs"."tempshiftplanid" = "p"."tempshiftplanid" AND "cs"."secondaryagencyid" = @secagid ORDER BY "cascadelevel" DESC) AS "Declined",
      "p"."ClientNote",
      "v"."Status" AS "LineStatus"
      FROM "TempShiftPlan" AS "p" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company" AS "c","Person")
        ,"TempShiftPlan" AS "p" LEFT OUTER JOIN "CascadedShift" AS "cs"
        ,"TempShiftPlan" AS "p" LEFT OUTER JOIN "TempShift" AS "ts"
        ,"Company" AS "c" KEY JOIN "CompanyStatus" AS "compstat"
        ,"Company" AS "c" LEFT OUTER JOIN "division" AS "d"
      WHERE "Declined" IS NULL
      AND "cs"."whendeclined" IS NULL
      AND(("Interest" LIKE 'Confirm%') OR("Interest" IS NULL))
      AND "cs"."secondaryagencyid" = @secagid;
  SELECT top "x" START at "y"
    "LineID","StartDate","StartTime","FinishDate","FinishTime","Position","ShiftDescription","CompanyName","ContactName","Status","ConsultantName","OurRef","TheirRef","ContractRef","CascadeDateTime","CascadeLevel","WhenEntered","SubmittedCandidates","LongDescription","Interest","Declined","Notes"
    FROM "DSet"
    WHERE("LineID" = "pLineID"
    OR("LineStatus" = "isnull"("pStatus","LineStatus")
    AND "left"("CompanyName","length"("CompanySearch")) = "isnull"("CompanySearch","left"("CompanyName"+"ContactName","length"("CompanySearch")))
    AND "left"("LineId",1) = "isnull"("pPermOrShift","left"("LineId",1))
    AND "left"("Position","length"("PositionSearch")) = "isnull"("PositionSearch","left"("Position","length"("PositionSearch")))
    AND "isnull"("pLineID",'') = ''
    AND("isnull"("dateformat"("pSearchStartDate",'yyyy-mm-dd'),"today"()) <= "isnull"("FinishDate","dateformat"("now"(),'yyyy-mm-dd')) AND "isnull"("dateformat"("pSearchEndDate",'yyyy-mm-dd'),"today"()) >= "isnull"("StartDate","dateformat"("now"(),'yyyy-mm-dd'))))
    OR("OurRef" = "pRef"
    AND "isnull"("pLineID",'') = '')
    OR "isnull"("pLineID","pStatus","pCompanyName","pPermOrShift","pPosition","pRef",'') = '')
    ORDER BY "startdate" DESC,"starttime" DESC
END /* DOC
2016-09-29 PC v0.1 create searchable list, combined with greater detail, searchable on Line ID
2016-11-29 PC Test and doc
2017-01-17 PC search dates added
2018-06-15 PC revise tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries
 
tests:
valid pWebUser, pslice, pSliceSize, pLineID for V
valid pWebUser, pslice, pSliceSize, pStatus
valid pWebUser, pslice, pSliceSize, pLineID for P
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
delete from cascadedvacancy where cascadedvacancyid='TEST';
delete from cascadedshift where cascadedshiftid='TEST';
insert into cascadedvacancy (CascadedVacancyID,VacancyID,SecondaryAgencyID,cascadetime,cascadelevel,method) values ('TEST','TEST','TEST',now(*),1,9);
update vacancy set staffid='TEST',refcode='TestRef',status='[' where vacancyid='TEST';
update tempdesk set desktype='W' where tempdeskid='TEST';
select * from NetAgencyCurrentRequirementSearch('test.agency',null,null,null,null,null,null,null,null,null,null) where lineid='VTEST';
expect LineId=VTEST,Position=test,CompanyName=test,ContactName=test,ConsultantName=TestStaff;
insert into tempshiftplan (tempshiftplanid,vacancyid,description) on existing update defaults off values ('TEST','TEST','test2');
insert into cascadedshift (cascadedshiftid,tempshiftplanid,secondaryagencyid,cascadetime,cascadelevel,method) values ('TEST','TEST','TEST',now(*),1,9); 
select * from NetAgencyCurrentRequirementSearch('test.agency',0,10,null,null,null,null,null,null,null,null) where lineid='PTEST';
expect LineId=PTEST,Position=test,CompanyName=test,ContactName=test,ConsultantName=TestStaff;
select * from NetAgencyCurrentRequirementSearch('test.agency',0,10,'PTEST',null,null,null,null,null,null,null);
expect LineId=PTEST,Position=test,CompanyName=test,ContactName=test,ConsultantName=TestStaff;
delete from cascadedvacancy where cascadedvacancyid='TEST';
delete from cascadedshift where cascadedshiftid='TEST';
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyCurrentRequirementSearch" IS 
{CREATE PROCEDURE pears."NetAgencyCurrentRequirementSearch"(IN pWebUserID CHAR(20),IN pSlice INTEGER DEFAULT 0,IN pSliceSize INTEGER DEFAULT 20,IN pLineID CHAR(20) DEFAULT NULL,IN pStatus CHAR(1),IN pCompanyName CHAR(50) DEFAULT NULL,IN pPermOrShift CHAR(1) DEFAULT NULL,IN pPosition CHAR(50) DEFAULT NULL, IN pRef CHAR(10) DEFAULT NULL, IN pSearchStartDate CHAR(26) DEFAULT NULL, IN pSearchEndDate CHAR(26) DEFAULT NULL)
RESULT(LineID CHAR(30),StartDate DATE,StartTime TIME,FinishDate DATE,FinishTime TIME,POSITION CHAR(50),ShiftDescription CHAR(50),CompanyName CHAR(60),ContactName CHAR(60),STATUS CHAR(40),ConsultantName CHAR(60),OurRef CHAR(20),TheirRef CHAR(50),ContractRef CHAR(20),CascadeDateTime TIMESTAMP,CascadeLevel SMALLINT,WhenEntered TIMESTAMP,SubmittedCandidates long VARCHAR,LongDescription long VARCHAR,Interest long VARCHAR,Declined CHAR(50),Notes long VARCHAR )
BEGIN
	// IQXWeb
    DECLARE @SecAgID CHAR(20);
    DECLARE @PlacedCode CHAR(1);
    DECLARE "SearchStr" CHAR(100);
    DECLARE x INTEGER;
    DECLARE y INTEGER;
    DECLARE CompanySearch CHAR(60);
    DECLARE PositionSearch CHAR(60);
    DECLARE LOCAL TEMPORARY TABLE "DSet"(
        "LineID" CHAR(30) NULL,
        "StartDate" DATE NULL,
        "StartTime" TIME NULL,
        "FinishDate" DATE NULL,
        "FinishTime" TIME NULL,
        "Position" CHAR(50) NULL,
        "ShiftDescription" CHAR(50) NULL,
        "CompanyName" CHAR(60) NULL,
        "ContactName" CHAR(30) NULL,
        "Status" CHAR(40) NULL,
        "ConsultantName" CHAR(60) NULL,
        "OurRef" CHAR(20) NULL,
        "TheirRef" CHAR(50) NULL,
        "ContractRef" CHAR(20) NULL,
        "CascadeDateTime" TIMESTAMP NULL,
        "CascadeLevel" SMALLINT NULL,
        "WhenEntered" TIMESTAMP NULL,
        "SubmittedCandidates" long VARCHAR NULL,
        "LongDescription" long VARCHAR NULL,
        "Interest" long VARCHAR NULL,
        "Declined" CHAR(50) NULL,
        Notes long VARCHAR NULL,
        LineStatus CHAR(1) NULL
    ) NOT transactional;
    SET @SecAgID = (SELECT top 1 "employment"."companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" ORDER BY isnull(employment.leavedate,CURRENT DATE) DESC);
    SET @PlacedCode = (SELECT top 1 "s"."status" FROM "status" AS "s" WHERE "type" = 'R' AND "name" = 'Placed' ORDER BY sortorder);
    SET x=isnull(pSliceSize,20);
    SET y=x*isnull(pSlice,0)+1;
    SET CompanySearch=isnull(pCompanyName,'');
    SET PositionSearch=isnull(pPosition,'');
    IF isnull(pRef,'')<> '' THEN
        SET CompanySearch=NULL;
        SET PositionSearch=NULL;
        SET pStatus=NULL;
        SET pPermOrShift=NULL;
    END IF;
    INSERT INTO "DSet"    
      SELECT 'V'+"v"."VacancyID" AS "LineID",
      "v"."StartDate",
      NULL,
      "v"."FinishDate",
      NULL AS FinishTime,
      "v"."Position" AS "Position",
      '' AS "ShiftDescription",
      "c"."Name" AS "CompanyName",
      "Person"."Name" AS "ContactName",
      (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status",
      s."Name" AS "Consultant",
      "v"."RefCode" AS "OurRef",
      "v"."TheirRef" AS TheirRef,
      "v"."ContractRef",
      "v"."CascadeDateTime",
      "v"."CascadeLevel",
      "v"."WhenEntered",
      (SELECT "list"(DISTINCT "person"."name",', ') FROM "vacancy" KEY JOIN "progress" KEY JOIN "person" KEY JOIN "pay_employee" WHERE "vacancy"."vacancyid" = "v"."vacancyid" AND "pay_employee"."secondaryagencyid" = @secagid) AS "SubmittedCandidates",
      "v"."OtherNotes" AS "LongDescription",
      "isnull"(
        (SELECT top 1 'Declined Interest at '+CAST("dateformat"("cv"."whendeclined",'dd/mm/yyyy hh:nn') AS CHAR) FROM "cascadedvacancy" AS "cv" RIGHT OUTER JOIN "vacancy" AS "va" WHERE "cv"."whendeclined" IS NOT NULL AND("cv"."secondaryagencyid" IS NULL OR "cv"."secondaryagencyid" = @secagid) AND "cv"."vacancyid" = "v"."vacancyid" ORDER BY whendeclined DESC),
        (SELECT top 1 "ce"."Description"+' at '+CAST("dateformat"("ce"."contactdate",'dd/mm/yyyy') AS CHAR)+' '+CAST("dateformat"("ce"."contacttime",'hh:nn') AS CHAR)+' sent to '+"s"."name"+'. Note: '+"ce"."notes" FROM "cascadedvacancy" AS "cv" RIGHT OUTER JOIN "vacancy" AS "va" KEY JOIN "contactevent" AS "ce" KEY JOIN "staff" AS "s","contactevent" AS "ce" KEY JOIN "employment" AS "e" WHERE(("ce"."classcode" = 'CI') OR("ce"."classcode" = 'DI')) AND("cv"."secondaryagencyid" IS NULL OR "cv"."secondaryagencyid" = @secagid) AND "ce"."vacancyid" = "v"."vacancyid" AND "e"."companyid" = "cv"."secondaryagencyid" ORDER BY "ce"."description" DESC,"contactdate" ASC,"contacttime" DESC)) AS "Interest",
      (SELECT top 1 "cv"."whendeclined" FROM "cascadedvacancy" AS "cv" WHERE "cv"."vacancyid" = "v"."vacancyid" AND "cv"."secondaryagencyid" = @secagid ORDER BY whendeclined DESC) AS "Declined",
      v.othernotes,
      v.status AS LineStatus
      FROM "Vacancy" AS "v" KEY JOIN "Employment" AS "e" KEY JOIN("Company" AS "c","Person")
        ,"Vacancy" AS "v" LEFT JOIN "CascadedVacancy" AS "cv"
        ,"Company" AS "c" KEY JOIN "CompanyStatus" AS "compstat"
        ,"Company" AS "c" LEFT JOIN "division" AS "d",
        Vacancy AS v KEY JOIN staff s
      WHERE "Declined" IS NULL
      AND "cv"."whendeclined" IS NULL
      AND(("Interest" LIKE 'Confirm%') OR("Interest" IS NULL))
      AND isnull("cv"."secondaryagencyid",c.companyid) = @secagid 
      AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
 
    UNION ALL
    SELECT 'P'+"p"."TempShiftPlanID" AS "LineID",
      "isnull"("ts"."shiftdate","p"."ShiftDate"),
      "isnull"("ts"."timefrom","p"."TimeFrom"),
      (IF "isnull"("ts"."timefrom","p"."TimeFrom") < "isnull"("ts"."timefrom","p"."TimeTo") THEN "isnull"("ts"."shiftdate","p"."ShiftDate") ELSE "isnull"("ts"."shiftdate","p"."ShiftDate")+1 endif) AS "FinishDate",
      "isnull"("ts"."timeto","p"."TimeTo") AS "FinishTime",
      "v"."Position",
      "p"."Description" AS "ShiftDescription",
      "c"."Name" AS "CompanyName",
      "Person"."Name" AS "ContactName",
      (CASE isnull(ts.state,'U')
        WHEN 'B' THEN 'Booked'
        WHEN 'P' THEN 'Provisional'
        WHEN 'W' THEN 'Worked'
        WHEN 'C' THEN 'Cancelled'
        WHEN 'U' THEN 'Unfilled'
      END CASE) AS "Status",
      (SELECT "Name" FROM "Staff" WHERE "Staff"."StaffID" = "v"."StaffID") AS "ConsultantName",
      CAST("p"."ShiftSerialNumber" AS CHAR(20)) AS "OurRef",
      "p"."ReferenceCode" AS "TheirRef",
      "v"."ContractRef",
      "p"."CascadeDateTime",
      "p"."CascadeLevel",
      "p"."WhenEntered",
      (SELECT "list"(DISTINCT "person"."name",', ') FROM "tempshiftprogress" KEY JOIN "person" KEY JOIN "pay_employee" WHERE "tempshiftprogress"."tempshiftplanid" = "p"."tempshiftplanid" AND "pay_employee"."secondaryagencyid" = @secagid) AS "SubmittedCandidates",
      '' AS "LongDescription",
      (SELECT top 1 "ce"."Description"+' at '+CAST("dateformat"("ce"."contactdate",'dd/mm/yyyy') AS CHAR)+' '+CAST("dateformat"("ce"."contacttime",'hh:nn') AS CHAR)+' sent to '+"s"."name"+'. Note: '+"ce"."notes" FROM "cascadedvacancy" AS "cv" RIGHT OUTER JOIN "vacancy" AS "va" KEY JOIN "contactevent" AS "ce" KEY JOIN "staff" AS "s" WHERE(("ce"."classcode" = 'CI') OR("ce"."classcode" = 'DI')) AND("cv"."secondaryagencyid" IS NULL OR "cv"."secondaryagencyid" = @secagid) AND "ce"."vacancyid" = "v"."vacancyid" AND "substr"("ce"."description","charindex"(':',"ce"."description")+1) = "OurRef" ORDER BY "ce"."description" DESC,"contactdate" ASC,"contacttime" DESC) AS "Interest",
      (SELECT top 1 "cs"."WhenDeclined" FROM "cascadedshift" AS "cs" WHERE "cs"."tempshiftplanid" = "p"."tempshiftplanid" AND "cs"."secondaryagencyid" = @secagid ORDER BY cascadelevel DESC) AS "Declined",
      p.ClientNote,
      v.Status AS LineStatus
      FROM "TempShiftPlan" AS "p" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company" AS "c","Person")
        ,"TempShiftPlan" AS "p" LEFT JOIN "CascadedShift" AS "cs"
        ,"TempShiftPlan" AS "p" LEFT OUTER JOIN "TempShift" AS "ts"
        ,"Company" AS "c" KEY JOIN "CompanyStatus" AS "compstat"
        ,"Company" AS "c" LEFT JOIN "division" AS "d"
      WHERE "Declined" IS NULL
      AND "cs"."whendeclined" IS NULL
      AND(("Interest" LIKE 'Confirm%') OR("Interest" IS NULL))
      AND "cs"."secondaryagencyid" = @secagid;
 
    SELECT top x START at y
    "LineID","StartDate","StartTime","FinishDate","FinishTime","Position","ShiftDescription","CompanyName","ContactName","Status","ConsultantName","OurRef","TheirRef","ContractRef","CascadeDateTime","CascadeLevel","WhenEntered","SubmittedCandidates","LongDescription","Interest",Declined,Notes  
    FROM DSet
    WHERE LineID = pLineID
    OR(
    LineStatus = isnull(pStatus,LineStatus)
    AND LEFT(CompanyName,LENGTH(CompanySearch)) = isnull(CompanySearch,LEFT(CompanyName+ContactName,LENGTH(CompanySearch)))
    AND LEFT(LineId,1)=isnull(pPermOrShift,LEFT(LineId,1))
    AND LEFT(POSITION,LENGTH(PositionSearch)) = isnull(PositionSearch,LEFT(POSITION,LENGTH(PositionSearch)))
    AND isnull(pLineID,'')=''
    AND (isnull(dateformat(pSearchStartDate,'yyyy-mm-dd'),today(*)) <= isnull(FinishDate,dateformat(now(*),'yyyy-mm-dd')) AND isnull(dateformat(pSearchEndDate,'yyyy-mm-dd'),today(*)) >= isnull(StartDate,dateformat(now(*),'yyyy-mm-dd')))
    )
    OR (OurRef = pRef
    AND isnull(pLineID,'')='')
    OR isnull(pLineID,pStatus,pCompanyName,pPermOrShift,pPosition,pRef,'')=''
    ORDER BY startdate DESC,starttime DESC
 
 
END
 
/* DOC
2016-09-29 PC v0.1 create searchable list, combined with greater detail, searchable on Line ID
2016-11-29 PC Test and doc
2017-01-17 PC search dates added
2018-06-15 PC revise tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries
 
tests:
    valid pWebUser, pslice, pSliceSize, pLineID for V
    valid pWebUser, pslice, pSliceSize, pStatus
    valid pWebUser, pslice, pSliceSize, pLineID for P
*/
 
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
delete from cascadedvacancy where cascadedvacancyid='TEST';
delete from cascadedshift where cascadedshiftid='TEST';
insert into cascadedvacancy (CascadedVacancyID,VacancyID,SecondaryAgencyID,cascadetime,cascadelevel,method) values ('TEST','TEST','TEST',now(*),1,9);
update vacancy set staffid='TEST',refcode='TestRef',status='[' where vacancyid='TEST';
update tempdesk set desktype='W' where tempdeskid='TEST';
select * from NetAgencyCurrentRequirementSearch('test.agency',null,null,null,null,null,null,null,null,null,null) where lineid='VTEST';
expect LineId=VTEST,Position=test,CompanyName=test,ContactName=test,ConsultantName=TestStaff;
insert into tempshiftplan (tempshiftplanid,vacancyid,description) on existing update defaults off values ('TEST','TEST','test2');
insert into cascadedshift (cascadedshiftid,tempshiftplanid,secondaryagencyid,cascadetime,cascadelevel,method) values ('TEST','TEST','TEST',now(*),1,9); 
select * from NetAgencyCurrentRequirementSearch('test.agency',0,10,null,null,null,null,null,null,null,null) where lineid='PTEST';
expect LineId=PTEST,Position=test,CompanyName=test,ContactName=test,ConsultantName=TestStaff;
select * from NetAgencyCurrentRequirementSearch('test.agency',0,10,'PTEST',null,null,null,null,null,null,null);
expect LineId=PTEST,Position=test,CompanyName=test,ContactName=test,ConsultantName=TestStaff;
delete from cascadedvacancy where cascadedvacancyid='TEST';
delete from cascadedshift where cascadedshiftid='TEST';
 
*/
}
pears.NetAgencyCurrentRequirementSearch

Original SQL

iqx
