Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
CREATE PROCEDURE "pears"."NetAgencyVacancies"( IN "pWebUserID" CHAR(20) ) RESULT( "RowID" CHAR(30),"StartDate" DATE,"StartTime" TIME,"FinishDate" DATE,"FinishTime" TIME,"IsShift" SMALLINT,"VacancyID" CHAR(20),"TempShiftPlanID" CHAR(20),"Position" CHAR(50),"ShiftDescription" CHAR(50),"CompanyName" CHAR(60),"ContactName" CHAR(30),"Status" CHAR(40),"Department" CHAR(30),"Consultant" CHAR(60),"OurRef" CHAR(20),"TheirRef" CHAR(50),"ContractRef" CHAR(20),"CascadeDateTime" TIMESTAMP,"CascadeLevel" SMALLINT,"WhenEntered" TIMESTAMP,"SubmittedCandidates" long VARCHAR,"IsCurrent" SMALLINT,"LongDescription" long VARCHAR ) //IQXWeb BEGIN DECLARE @DateRangeStart DATE; DECLARE @DateRangeEnd DATE; DECLARE @SecAgID CHAR(20); SET @DateRangeStart = "dateadd"("week",-4,CURRENT DATE); SET @DateRangeEnd = "dateadd"("week",4,CURRENT DATE); SET @SecAgID = (SELECT top 1 "employment"."companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" ORDER BY "isnull"("employment"."leavedate",CURRENT DATE) DESC); SELECT 'V'+"v"."VacancyID" AS "RowID", "v"."StartDate", CAST(NULL AS TIME) AS "StartTime", "v"."FinishDate", CAST(NULL AS TIME) AS "FinishTime", 0 AS "IsShift", "v"."VacancyID", CAST(NULL AS CHAR(20)) AS "TempShiftPlanID", "v"."Position" AS "Position", CAST(NULL AS CHAR(50)) AS "ShiftDescription", "Company"."Name" AS "CompanyName", "Person"."Name" AS "ContactName", (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status", (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department", (SELECT "Name" FROM "Staff" WHERE "Staff"."StaffID" = "v"."StaffID") AS "Consultant", "v"."RefCode" AS "OurRef", "v"."TheirRef", "v"."ContractRef", "v"."CascadeDateTime", "v"."CascadeLevel", "v"."WhenEntered", (SELECT "list"("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", (IF "v"."status" = 'C' THEN 1 ELSE 0 endif) AS "IsCurrent", "v"."OtherNotes" AS "LongDescription" FROM "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person") ,"Vacancy" AS "v" KEY JOIN "CascadedVacancy" AS "cv" WHERE "cv"."secondaryagencyid" = @secagid AND "v"."StartDate" <= @DateRangeEnd AND "isnull"("v"."FinishDate",@DateRangeEnd) >= @DateRangeStart AND "v"."temp" = 1 AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S' UNION ALL SELECT 'P'+"p"."TempShiftPlanID" AS "RowID", "p"."ShiftDate" AS "StartDate", "p"."TimeFrom" AS "StartTime", (IF "p"."TimeFrom" < "p"."TimeTo" THEN "p"."ShiftDate" ELSE "p"."ShiftDate"+1 endif) AS "FinishDate", "p"."TimeTo" AS "FinishTime", 1 AS "IsShift", "v"."VacancyID", "p"."TempShiftPlanID", "v"."Position", "p"."Description" AS "ShiftDescription", "Company"."Name" AS "CompanyName", "Person"."Name" AS "ContactName", (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "Status", (SELECT "Name" FROM "Department" WHERE "Department"."DepartmentID" = "v"."DepartmentID") AS "Department", (SELECT "Name" FROM "Staff" WHERE "Staff"."StaffID" = "v"."StaffID") AS "Consultant", CAST("p"."ShiftSerialNumber" AS CHAR(20)) AS "OurRef", "p"."ReferenceCode" AS "TheirRef", "v"."ContractRef", "p"."CascadeDateTime", "p"."CascadeLevel", "p"."WhenEntered", (SELECT "list"("person"."name",', ') FROM "tempshiftprogress" KEY JOIN "person" KEY JOIN "pay_employee" WHERE "tempshiftprogress"."tempshiftplanid" = "p"."tempshiftplanid" AND "pay_employee"."secondaryagencyid" = @secagid) AS "SubmittedCandidates", (IF EXISTS(SELECT * FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND NOT("state" = 'C' AND "isnull"("crefill",0) = 1)) THEN 0 ELSE 1 endif) AS "IsCurrent", CAST(NULL AS long VARCHAR) AS "LongDescription" FROM "TempShiftPlan" AS "p" KEY JOIN "Vacancy" AS "v" KEY JOIN "Employment" KEY JOIN("Company","Person") ,"TempShiftPlan" AS "p" KEY JOIN "CascadedShift" AS "cs" WHERE "cs"."secondaryagencyid" = @secagid AND "p"."ShiftDate" BETWEEN @DateRangeStart AND @DateRangeEnd ORDER BY 1 ASC,2 ASC END /* DOC 2017-05-18 PC Doc and Test 2019-03-05 PC IW-873 add top 1 and order by on subqueries */ /* TEST call NetTestSetup(''); delete from cascadedvacancy where vacancyid='TEST'; delete from cascadedshift where cascadedshiftid='TEST'; update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner'); update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency'; update vacancy set startdate=dateformat(now(*),'yyyy-mm-dd'),temp=1 where vacancyid='TEST'; update tempshiftplan set shiftdate=dateformat(now(*),'yyyy-mm-dd') where tempshiftplanid='TEST'; insert into cascadedvacancy (cascadedvacancyid,vacancyid,secondaryagencyid,cascadetime,cascadelevel,method) values ('TEST','TEST','TEST',now(*),1,1); select * from NetAgencyVacancies(null); select * from NetAgencyVacancies('test.agency'); expect RowID=VTEST,Position=test; insert into cascadedshift (cascadedshiftid,tempshiftplanid,secondaryagencyid,cascadetime,cascadelevel,method) values ('TEST','TEST','TEST',now(*),1,1); select * from NetAgencyVacancies('test.agency') order by RowID; expect RowID=PTEST,Position=test; expect RowID=VTEST,Position=test; */ GO COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyVacancies" IS {CREATE PROCEDURE pears."NetAgencyVacancies"(IN pWebUserID CHAR(20)) RESULT(RowID CHAR(30),StartDate DATE,StartTime TIME,FinishDate DATE,FinishTime TIME,IsShift SMALLINT,VacancyID CHAR(20),TempShiftPlanID CHAR(20),POSITION CHAR(50),ShiftDescription CHAR(50),CompanyName CHAR(60),ContactName CHAR(30),STATUS CHAR(40),Department CHAR(30),Consultant CHAR(60),OurRef CHAR(20),TheirRef CHAR(50),ContractRef CHAR(20),CascadeDateTime TIMESTAMP,CascadeLevel SMALLINT,WhenEntered TIMESTAMP,SubmittedCandidates long VARCHAR,IsCurrent SMALLINT,LongDescription long VARCHAR) //IQXWeb BEGIN DECLARE @DateRangeStart DATE; DECLARE @DateRangeEnd DATE; DECLARE @SecAgID CHAR(20); SET @DateRangeStart=dateadd(week,-4,CURRENT DATE); SET @DateRangeEnd=dateadd(week,4,CURRENT DATE); SET @SecAgID=(SELECT top 1 employment.companyid FROM employment KEY JOIN iqxnetuserlink WHERE iqxnetuserlink.iqxnetuserid = pwebuserid ORDER BY isnull(employment.leavedate,CURRENT DATE) DESC); SELECT 'V'+v.VacancyID AS RowID, v.StartDate, CAST(NULL AS TIME) AS StartTime, v.FinishDate, CAST(NULL AS TIME) AS FinishTime, 0 AS IsShift, v.VacancyID, CAST(NULL AS CHAR(20)) AS TempShiftPlanID, v.Position AS POSITION, CAST(NULL AS CHAR(50)) AS ShiftDescription, Company.Name AS CompanyName, Person.Name AS ContactName, (SELECT Name FROM STATUS WHERE STATUS.Status = v.Status AND STATUS.Type = 'V') AS STATUS, (SELECT Name FROM Department WHERE Department.DepartmentID = v.DepartmentID) AS Department, (SELECT Name FROM Staff WHERE Staff.StaffID = v.StaffID) AS Consultant, v.RefCode AS OurRef, v.TheirRef, v.ContractRef, v.CascadeDateTime, v.CascadeLevel, v.WhenEntered, (SELECT list(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, (IF v.status = 'C' THEN 1 ELSE 0 endif) AS IsCurrent,v.OtherNotes AS LongDescription FROM Vacancy AS v KEY JOIN Employment KEY JOIN(Company,Person) ,Vacancy AS v KEY JOIN CascadedVacancy AS cv WHERE cv.secondaryagencyid = @secagid AND v.StartDate <= @DateRangeEnd AND isnull(v.FinishDate,@DateRangeEnd) >= @DateRangeStart AND v.temp = 1 AND isnull((SELECT desktype FROM tempdesk WHERE tempdeskid = v.tempdeskid),'') <> 'S' UNION ALL SELECT 'P'+p.TempShiftPlanID AS RowID, p.ShiftDate AS StartDate, p.TimeFrom AS StartTime, (IF p.TimeFrom < p.TimeTo THEN p.ShiftDate ELSE p.ShiftDate+1 endif) AS FinishDate,p.TimeTo AS FinishTime, 1 AS IsShift, v.VacancyID, p.TempShiftPlanID, v.Position, p.Description AS ShiftDescription, Company.Name AS CompanyName, Person.Name AS ContactName, (SELECT Name FROM STATUS WHERE STATUS.Status = v.Status AND STATUS.Type = 'V') AS STATUS, (SELECT Name FROM Department WHERE Department.DepartmentID = v.DepartmentID) AS Department, (SELECT Name FROM Staff WHERE Staff.StaffID = v.StaffID) AS Consultant, CAST(p.ShiftSerialNumber AS CHAR(20)) AS OurRef, p.ReferenceCode AS TheirRef, v.ContractRef, p.CascadeDateTime, p.CascadeLevel, p.WhenEntered, (SELECT list(person.name,', ') FROM tempshiftprogress KEY JOIN person KEY JOIN pay_employee WHERE tempshiftprogress.tempshiftplanid = p.tempshiftplanid AND pay_employee.secondaryagencyid = @secagid) AS SubmittedCandidates, (IF EXISTS(SELECT * FROM tempshift WHERE tempshiftplanid = p.tempshiftplanid AND NOT(state = 'C' AND isnull(crefill,0) = 1)) THEN 0 ELSE 1 endif) AS IsCurrent,CAST(NULL AS long VARCHAR) AS LongDescription FROM TempShiftPlan AS p KEY JOIN Vacancy AS v KEY JOIN Employment KEY JOIN(Company,Person) ,TempShiftPlan AS p KEY JOIN CascadedShift AS cs WHERE cs.secondaryagencyid = @secagid AND p.ShiftDate BETWEEN @DateRangeStart AND @DateRangeEnd ORDER BY 1 ASC,2 ASC END /* DOC 2017-05-18 PC Doc and Test 2019-03-05 PC IW-873 add top 1 and order by on subqueries */ /* TEST call NetTestSetup(''); delete from cascadedvacancy where vacancyid='TEST'; delete from cascadedshift where cascadedshiftid='TEST'; update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner'); update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency'; update vacancy set startdate=dateformat(now(*),'yyyy-mm-dd'),temp=1 where vacancyid='TEST'; update tempshiftplan set shiftdate=dateformat(now(*),'yyyy-mm-dd') where tempshiftplanid='TEST'; insert into cascadedvacancy (cascadedvacancyid,vacancyid,secondaryagencyid,cascadetime,cascadelevel,method) values ('TEST','TEST','TEST',now(*),1,1); select * from NetAgencyVacancies(null); select * from NetAgencyVacancies('test.agency'); expect RowID=VTEST,Position=test; insert into cascadedshift (cascadedshiftid,tempshiftplanid,secondaryagencyid,cascadetime,cascadelevel,method) values ('TEST','TEST','TEST',now(*),1,1); select * from NetAgencyVacancies('test.agency') order by RowID; expect RowID=PTEST,Position=test; expect RowID=VTEST,Position=test; */ }