pears.NetOwnerRequirementList
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL
CREATE PROCEDURE "pears"."NetOwnerRequirementList"( IN "pWebUserID" CHAR(20),IN "pSlice" INTEGER DEFAULT 1,IN "pSliceSize" INTEGER DEFAULT 10,IN "pStartDate" CHAR(26) DEFAULT NULL,IN "pEndDate" CHAR(26) DEFAULT NULL,IN "SearchString" CHAR(100) DEFAULT NULL,IN "pStatus" CHAR(1) DEFAULT NULL,IN "pConsultantFilter" CHAR(2048) DEFAULT NULL ) RESULT( "RowID" CHAR(20),"StartDate" CHAR(20),"CombinedTime" CHAR(20),"IsShift" SMALLINT,"Position" CHAR(50),"ShiftDescription" CHAR(50),"CompanyID" CHAR(20),"CompanyName" CHAR(60),"ContactID" CHAR(20),"ContactName" CHAR(60),"DetailStatus" CHAR(30),"ListStatus" CHAR(30),"Department" CHAR(50),"Consultant" CHAR(60),"OurRef" CHAR(20),"ContractRef" CHAR(20),"ShiftRef" CHAR(20),"WhenEntered" TIMESTAMP,"FilledBy" CHAR(250),"WhoCancelled" CHAR(60),"StartTime" CHAR(15),"FinishTime" CHAR(15),"FinishDate" CHAR(30),"Filled" SMALLINT,"Unconfirmed" SMALLINT,"LongDescription" long VARCHAR,"CompanyAddress" CHAR(250),"PersonID" CHAR(20),"TextStatus" CHAR(100),"CancelButton" SMALLINT,"ConfirmButton" SMALLINT,"RejectButton" SMALLINT,"FillButton" SMALLINT,"PONumber" CHAR(50),"CreatedBy" CHAR(255),"CancelState" CHAR(50),"ConfirmedBy" CHAR(60) ) BEGIN // IQXNet DECLARE "DateRangeStart" DATE; DECLARE "DateRangeEnd" DATE; DECLARE "companycount" INTEGER; DECLARE "SecAgencyCode" CHAR(1); DECLARE "x" INTEGER; DECLARE "y" INTEGER; DECLARE "ConsultantFilter" CHAR(2048); --declare local temporary table "IDs"( -- "ID" char(20) not null, -- "CompanyStartDate" date null, -- ) not transactional; DECLARE LOCAL TEMPORARY TABLE "DSet"( "RowID" CHAR(20) NULL, "StartDate" CHAR(20) NULL, "CombinedTime" CHAR(20) NULL, "IsShift" SMALLINT NULL DEFAULT 0, "Position" CHAR(50) NULL, "ShiftDescription" CHAR(50) NULL, "CompanyID" CHAR(20) NULL, "CompanyName" CHAR(60) NULL, "ContactID" CHAR(20) NULL, "ContactName" CHAR(60) NULL, "DetailStatus" CHAR(30) NULL, "ListStatus" CHAR(30) NULL, "Department" CHAR(50) NULL, "Consultant" CHAR(60) NULL, "OurRef" CHAR(20) NULL, "ContractRef" CHAR(20) NULL, "ShiftRef" CHAR(20) NULL, "WhenEntered" TIMESTAMP NULL, "FilledBy" CHAR(250) NULL, "WhoCancelled" CHAR(60) NULL, "StartTime" CHAR(15) NULL, "FinishTime" CHAR(15) NULL, "FinishDate" CHAR(30) NULL, "Filled" SMALLINT NULL DEFAULT 0, "Unconfirmed" SMALLINT NULL DEFAULT 0, "LongDescription" long VARCHAR NULL, "CompanyAddress" CHAR(250) NULL, "PersonID" CHAR(20) NULL, "TextStatus" CHAR(100) NULL, "CancelButton" SMALLINT NULL DEFAULT 0, "ConfirmButton" SMALLINT NULL DEFAULT 0, "RejectButton" SMALLINT NULL DEFAULT 0, "FillButton" SMALLINT NULL DEFAULT 0, "PONumber" CHAR(50) NULL, "CreatedBy" CHAR(255) NULL, "CancelState" CHAR(50) NULL, "ConfirmedBy" CHAR(60) NULL, "ConfirmState" CHAR(30) NULL, ) NOT transactional; IF "IQXNetHasPermission"("pWebUserID",'ORDERJOBS') = 0 THEN RETURN END IF; IF "pConsultantFilter" IS NULL THEN SET "ConsultantFilter" = (SELECT "list"("staff"."staffid") FROM "staff" KEY JOIN "division" AS "d" WHERE "defunct" = 0 AND "staff"."divisionid" = any(SELECT "divisionid" FROM "dashboardstaffdivisions"())) ELSE SET "ConsultantFilter" = "isnull"("pConsultantFilter",(SELECT "staffid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID")) END IF; SET "DateRangeStart" = "IQXNetStringToDate"("pStartDate"); SET "DateRangeEnd" = "IQXNetStringToDate"("pEndDate"); SET "DateRangeStart" = "isnull"("DateRangeStart","dateadd"("week",-1,CURRENT DATE)); SET "DateRangeEnd" = "isnull"("DateRangeEnd","dateadd"("year",1,CURRENT DATE)); SET "companycount" = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID"); SET "SecAgencyCode" = 'A'; SET "x" = "pSliceSize"; SET "y" = "x"*"pSlice"+1; INSERT INTO "Dset" SELECT "v"."VacancyID" AS "RowID", "v"."startdate" AS "StartDate", NULL AS "CombinedTime", 0 AS "IsShift", "v"."Position" AS "Position", NULL AS "ShiftDescription", "Company"."CompanyID", "Company"."Name" AS "CompanyName", "Person"."PersonID", "Person"."Name" AS "ContactName", (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "DetailStatus", "DetailStatus" AS "ListStatus", (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"."ContractRef", "v"."refcode" AS "ShiftRef", "v"."WhenEntered", NULL AS "FilledBy", NULL AS "WhoCancelled", NULL AS "StartTime", NULL AS "FinishTime", "v"."finishdate" AS "FinishDate", NULL AS "Filled", (IF "v"."status" = ']' THEN 1 ELSE 0 endif) AS "UnConfirmed", "v"."OtherNotes" AS "LongDescription", "GetCompanyAddressOnLine"("e"."companyid") AS "CompanyAddress", NULL AS "PersonID", (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "statusText", 0 AS "CancelButton", 0 AS "ConfirmButton", 0 AS "RejectButton", 0 AS "FillButton", "v"."theirref" AS "PONumber", ('Consultant: '+(SELECT "name" FROM "staff" WHERE "staffid" = "v"."staffid")+' at: '+"dateformat"("v"."whenentered",'dd/mm/yyyy hh:mm:ss')) AS "CreatedBy", NULL AS "CancelState", NULL AS "ConfirmedBy", NULL AS "ConfirmState" FROM "vacancy" AS "v" KEY JOIN "Employment" AS "e" KEY JOIN("Company","Person") WHERE "StartDate" BETWEEN "DateRangeStart" AND "DateRangeEnd" AND "v"."status" NOT IN( 'D','[' ) AND "v"."status" = "isnull"("pStatus","v"."status") AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S' AND "charindex"("v"."staffid","ConsultantFilter") > 0 UNION ALL SELECT "p"."TempShiftPlanID" AS "RowID", "isnull"("tes"."shiftdate","p"."ShiftDate") AS "StartDate", CAST("dateformat"("StartTime",'hh:nn') AS CHAR)+' - '+CAST("dateformat"("FinishTime",'hh:nn') AS CHAR) AS "CombinedTime", 1 AS "IsShift", "v"."Position" AS "Position", "p"."Description" AS "ShiftDescription", "Company"."CompanyID", "Company"."Name" AS "CompanyName", "Pe"."PersonID" AS "ContactID", "Pe"."Name" AS "ContactName", (SELECT(IF "Status"."final" = 1 OR "ConfirmStatus" IS NULL THEN "Status"."Name" ELSE "ConfirmStatus" endif) FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "DetailStatus", (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "ListStatus", (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"."ContractRef", CAST("p"."ShiftSerialNumber" AS CHAR(20)) AS "ShiftRef", "p"."WhenEntered", (SELECT "name" FROM "person" WHERE "personid" = "tes"."personid")+(IF(SELECT "status" FROM "person" WHERE "personid" = "tes"."personid") = 'A' THEN ' (S)' endif) AS "FilledBy", "isnull"((IF "tes"."cancelreason" IN( 'C','E','G','L','R' ) THEN '(C)' ELSE IF "tes"."cancelreason" IN( 'P','S','T','W','X' ) THEN '(T)' ELSE '(G)' endif endif),'') AS "WhoCancelled", "dateformat"("isnull"("tes"."timefrom","p"."TimeFrom"),'hh:mm') AS "StartTime", "dateformat"("isnull"("tes"."timeto","p"."TimeTo"),'hh:mm') AS "FinishTime", (IF "FinishTime" < "StartTime" THEN "dateadd"("dd",1,"StartDate") ELSE "StartDate" endif) AS "FinishDate", (IF "WhoCancelled" = '(G)' THEN 3 ELSE IF "WhoCancelled" = '(T)' THEN 2 ELSE IF "isnull"((SELECT "surname" FROM "person" WHERE "personid" = "tes"."personid"),'') <> '' THEN 0 ELSE 1 endif endif endif) AS "Filled", "isnull"((SELECT FIRST 1 FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND "state" = 'C' AND "cancelreason" = '~'),0) AS "UnConfirmed", CAST(NULL AS long VARCHAR) AS "LongDescription", (IF 1 > 1 THEN "GetCompanyAddressOnLine"("company"."companyid") ELSE '' endif) AS "CompanyAddress", "tes"."personid" AS "PersonID", (IF(SELECT "count"() FROM "contactevent" WHERE "classcode" = 'SS' AND "description" = "ShiftRef") = 1 THEN 'In Progress' ELSE(CASE "tes"."state" WHEN 'B' THEN 'Confirmed' WHEN 'W' THEN 'Worked' WHEN 'P' THEN 'Pending' WHEN 'C' THEN(SELECT "cr"."name" FROM "TempShiftCancelReason" AS "cr" WHERE "cr"."TempShiftCancelReasonid" = "tes"."cancelreason")+' '+"tes"."note"+' at '+"dateformat"("tes"."whencancelled",'dd/mm/yyyy hh:nn') ELSE 'Pending' END) endif) AS "statusText", "isnull"((IF "tes"."state" = 'W' OR "WhoCancelled" = '(C)' OR "WhoCancelled" = '(T)' THEN 0 ELSE 1 endif),1) AS "CancelButton","isnull"((IF "tes"."clientconfirmed" = 0 THEN 1 ELSE 0 endif),0) AS "ConfirmButton","isnull"((IF("ConfirmButton" = 1 AND "tes"."state" <> 'C') THEN 1 ELSE 0 endif),0) AS "RejectButton", "isnull"((IF("tes"."state" = 'P') THEN 1 ELSE 0 endif),0) AS "FillButton", "p"."ReferenceCode" AS "PONumber", ('Ordered by: '+"p"."orderedby"+' at: '+"dateformat"("p"."whenentered",'dd/mm/yyyy hh:mm:ss')) AS "CreatedBy", "string"("dateformat"("tes"."WhenCancelled",'dd/mm/yyyy hh:nn:ss'),' by ',"isnull"((SELECT "name" FROM "staff" WHERE "staff"."staffid" = "tes"."WhoCancelled"),'(not recorded)')) AS "CancelState", "tes"."confirmedwith" AS "ConfirmedBy", IF "isnull"("tes"."clientconfirmed",0) = 1 THEN 'Client confirmed' ELSE IF "isnull"("tes"."tempconfirmed",0) = 1 THEN 'Candidate confirmed' ELSE NULL endif endif AS "ConfirmStatus" FROM "tempshift" AS "tes" RIGHT OUTER JOIN "TempShiftPlan" AS "p" LEFT OUTER JOIN "Vacancy" AS "v" KEY JOIN "Employment" AS "e" KEY JOIN("Company","Person" AS "Pe") WHERE "StartDate" BETWEEN "DateRangeStart" AND "DateRangeEnd" AND "v"."status" = "isnull"("pStatus","v"."status") AND "charindex"("v"."staffid","ConsultantFilter") > 0; SELECT top "x" START at "y" "RowID","StartDate","CombinedTime","IsShift","Position","ShiftDescription","CompanyID","CompanyName","ContactID","ContactName","DetailStatus","ListStatus","Department","Consultant","OurRef","ContractRef","ShiftRef","WhenEntered","FilledBy","WhoCAncelled","StartTime","FinishTime","FinishDate","Filled","Unconfirmed","LongDescription","CompanyAddress","PersonID","TextStatus","CancelButton","ConfirmButton","RejectButton","FillButton","PONumber","CreatedBy","CancelState","ConfirmedBy" FROM "DSet" ORDER BY "StartDate" DESC END /* DOC 2017-03-28 PC Create list of Current Requirements for Consultant. SearchString not implemented. IW-71 2017-07-28 PC modify initial dataset to use vacancy.staffid as well as retaining original company.staffid 2017-12-21 PC modify to delegate division selction to DivisionAccess table and provide post filtering on consultant 2018-05-03 PC IW-482 view requirements from all consultants 2018-05-07 PC modify method of selecting consultant division list 2018-05-25 PC IW-558 add candidate and client confirmed to shift status 2018-05-25 PC IW-563 change 'Created by' to 'Consultant' 2018-05-29 MHS confirmedwith is a free text field not a personid. Changed case on 'Candidate' for consistency Tests: valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate where companystartdate greater than today change desk to shift valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate with ORDERJOBS and companystartdate a long time ago valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate, invalid pStatus valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate, pStatus */ /* TEST call NetTestSetup(''); delete from tagvalue where taglocation='C' and tagid='9X0' and id='TEST'; update vacancy set status='A' where vacancyid='TEST'; update tempdesk set desktype='W' where tempdeskid='test'; update vacancy set startdate=dateformat(now(*),'yyyy-mm-dd'),status='D',tempdeskid='TEST2',staffid='TEST' where vacancyid='TEST'; update iqxnetuser set staffid='TEST' where iqxnetuserid='TEST.CLIENT'; update company set staffid='TEST' where companyid='TEST'; select * from NetOwnerRequirementList(null,null,null,null,null,null); expect EOF; delete from iqxnetrightallowed where iqxnetrightid='ORDERJOBS' and IQXNetUserClassID='CLIENT'; select * from NetOwnerRequirementList('test.client',0,10,null,null,null); expect EOF; insert into iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,maintainonweb) values ('ORDERJOBS','CLIENT','*',1); select * from NetOwnerRequirementList('test.client',0,11,null,null,null) order by whenentered desc; expect CompanyName=test; select * from NetOwnerRequirementList('test.client',0,12,dateadd(dd,1,now()),null,null) order by whenentered desc; expect CompanyName=test; select * from NetOwnerRequirementList('test.client',0,13,null,null,null) order by whenentered desc; expect CompanyName=test; update tempdesk set desktype='S' where tempdeskid='test'; select * from NetOwnerRequirementList('test.client',0,14,null,null,null) order by whenentered desc; expect CompanyName=test; insert into tagvalue (taglocation,tagid,id,value) values ('C','9X0','TEST',dateadd(yy,1,today())); select * from NetOwnerRequirementList('test.client',0,15,null,null,null) order by whenentered desc; expect CompanyName=test; delete from tagvalue where taglocation='C' and tagid='9X0' and id='TEST'; select * from NetOwnerRequirementList('test.client',0,16,null,null,null) order by whenentered desc; expect CompanyName=test; delete from tagvalue where taglocation='C' and tagid='9X0' and id='TEST'; select * from NetOwnerRequirementList('test.client',0,16,null,null,null,'P') order by whenentered desc; expect EOF; update vacancy set status='P' where vacancyid='TEST'; select * from NetOwnerRequirementList('test.client',0,16,null,null,null,'P') order by whenentered desc; expect CompanyName=test; update vacancy set status=null where vacancyid='TEST'; */ GO COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerRequirementList" IS {CREATE PROCEDURE pears."NetOwnerRequirementList"( IN "pWebUserID" CHAR(20),IN "pSlice" INTEGER DEFAULT 1,IN pSliceSize INTEGER DEFAULT 10,IN "pStartDate" CHAR(26) DEFAULT NULL,IN "pEndDate" CHAR(26) DEFAULT NULL,IN "SearchString" CHAR(100) DEFAULT NULL, IN "pStatus" CHAR(1) DEFAULT NULL, IN pConsultantFilter CHAR(2048) DEFAULT NULL) RESULT( "RowID" CHAR(20),"StartDate" CHAR(20),"CombinedTime" CHAR(20),"IsShift" SMALLINT,"Position" CHAR(50),"ShiftDescription" CHAR(50),CompanyID CHAR(20),CompanyName CHAR(60),ContactID CHAR(20),ContactName CHAR(60),DetailStatus CHAR(30),ListStatus CHAR(30),Department CHAR(50),"Consultant" CHAR(60),"OurRef" CHAR(20),"ContractRef" CHAR(20),"ShiftRef" CHAR(20),"WhenEntered" TIMESTAMP,"FilledBy" CHAR(250),WhoCancelled CHAR(60),StartTime CHAR(15),FinishTime CHAR(15),FinishDate CHAR(30),"Filled" SMALLINT,"Unconfirmed" SMALLINT,"LongDescription" long VARCHAR,"CompanyAddress" CHAR(250),"PersonID" CHAR(20),"TextStatus" CHAR(100),"CancelButton" SMALLINT,"ConfirmButton" SMALLINT,"RejectButton" SMALLINT,"FillButton" SMALLINT,"PONumber" CHAR(50),CreatedBy CHAR(255),CancelState CHAR(50), ConfirmedBy CHAR(60)) BEGIN // IQXNet DECLARE DateRangeStart DATE; DECLARE DateRangeEnd DATE; DECLARE companycount INTEGER; DECLARE "SecAgencyCode" CHAR(1); DECLARE x INTEGER; DECLARE y INTEGER; DECLARE ConsultantFilter CHAR(2048); --declare local temporary table "IDs"( -- "ID" char(20) not null, -- "CompanyStartDate" date null, -- ) not transactional; DECLARE LOCAL TEMPORARY TABLE "DSet"( "RowID" CHAR(20) NULL, "StartDate" CHAR(20) NULL, "CombinedTime" CHAR(20) NULL, "IsShift" SMALLINT NULL DEFAULT 0, "Position" CHAR(50) NULL, "ShiftDescription" CHAR(50) NULL, "CompanyID" CHAR(20) NULL, CompanyName CHAR(60) NULL, ContactID CHAR(20) NULL, ContactName CHAR(60) NULL, DetailStatus CHAR(30) NULL, ListStatus CHAR(30) NULL, Department CHAR(50) NULL, "Consultant" CHAR(60) NULL, "OurRef" CHAR(20) NULL, "ContractRef" CHAR(20) NULL, "ShiftRef" CHAR(20) NULL, "WhenEntered" TIMESTAMP NULL, "FilledBy" CHAR(250) NULL, WhoCancelled CHAR(60) NULL, StartTime CHAR(15) NULL, FinishTime CHAR(15) NULL, FinishDate CHAR(30) NULL, "Filled" SMALLINT NULL DEFAULT 0, "Unconfirmed" SMALLINT NULL DEFAULT 0, "LongDescription" long VARCHAR NULL, "CompanyAddress" CHAR(250) NULL, "PersonID" CHAR(20) NULL, "TextStatus" CHAR(100) NULL, "CancelButton" SMALLINT NULL DEFAULT 0, "ConfirmButton" SMALLINT NULL DEFAULT 0, "RejectButton" SMALLINT NULL DEFAULT 0, "FillButton" SMALLINT NULL DEFAULT 0, "PONumber" CHAR(50) NULL, CreatedBy CHAR(255) NULL, CancelState CHAR(50) NULL, ConfirmedBy CHAR(60) NULL, ConfirmState CHAR(30) NULL ) NOT transactional; IF "IQXNetHasPermission"("pWebUserID",'ORDERJOBS') = 0 THEN RETURN END IF; IF pConsultantFilter IS NULL THEN SET ConsultantFilter=(SELECT list(staff.staffid) FROM staff KEY JOIN division d WHERE defunct=0 AND staff.divisionid IN (SELECT divisionid FROM dashboardstaffdivisions())) ELSE SET ConsultantFilter=isnull(pConsultantFilter,(SELECT staffid FROM iqxnetuser WHERE iqxnetuserid=pWebUserID)); END IF; SET DateRangeStart=IQXNetStringToDate(pStartDate); SET DateRangeEnd=IQXNetStringToDate(pEndDate); SET DateRangeStart = "isnull"(DateRangeStart,"dateadd"("week",-1,CURRENT DATE)); SET DateRangeEnd = "isnull"(DateRangeEnd,"dateadd"("year",1,CURRENT DATE)); SET companycount = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID"); SET "SecAgencyCode" = 'A'; SET x=pSliceSize; SET y=x*pSlice+1; INSERT INTO Dset SELECT "v"."VacancyID" AS "RowID", "v"."startdate" AS "StartDate", NULL AS "CombinedTime", 0 AS "IsShift", "v"."Position" AS "Position", NULL AS "ShiftDescription", "Company"."CompanyID", "Company"."Name" AS "CompanyName", "Person".PersonID, "Person"."Name" AS "ContactName", (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "DetailStatus", DetailStatus AS ListStatus, (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"."ContractRef", "v"."refcode" AS "ShiftRef", "v"."WhenEntered", NULL AS "FilledBy", NULL AS "WhoCancelled", NULL AS "StartTime", NULL AS "FinishTime", v.finishdate AS "FinishDate", NULL AS "Filled", (IF "v"."status" = ']' THEN 1 ELSE 0 endif) AS "UnConfirmed", "v"."OtherNotes" AS "LongDescription", "GetCompanyAddressOnLine"("e"."companyid") AS "CompanyAddress", NULL AS "PersonID", (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "statusText", 0 AS "CancelButton", 0 AS "ConfirmButton", 0 AS "RejectButton", 0 AS "FillButton", "v"."theirref" AS "PONumber", ('Consultant: '+(SELECT "name" FROM "staff" WHERE "staffid" = "v"."staffid")+' at: '+"dateformat"("v"."whenentered",'dd/mm/yyyy hh:mm:ss')) AS "CreatedBy", NULL AS "CancelState", NULL AS ConfirmedBy, NULL AS ConfirmState FROM "vacancy" AS "v" KEY JOIN "Employment" AS "e" KEY JOIN("Company","Person") WHERE "StartDate" BETWEEN DateRangeStart AND DateRangeEnd AND v.status NOT IN('D','[') AND v.status = isnull(pStatus,v.status) AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S' AND charindex(v.staffid,ConsultantFilter) > 0 UNION ALL SELECT "p"."TempShiftPlanID" AS "RowID", "isnull"("tes"."shiftdate","p"."ShiftDate") AS "StartDate", CAST("dateformat"("StartTime",'hh:nn') AS CHAR)+' - '+CAST("dateformat"("FinishTime",'hh:nn') AS CHAR) AS "CombinedTime", 1 AS "IsShift", "v"."Position" AS "Position", "p"."Description" AS "ShiftDescription", "Company"."CompanyID", "Company"."Name" AS "CompanyName", "Pe".PersonID AS ContactID, "Pe"."Name" AS "ContactName", (SELECT (IF STATUS.final=1 OR ConfirmStatus IS NULL THEN STATUS."Name" ELSE ConfirmStatus endif) FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS DetailStatus, (SELECT "Name" FROM "Status" WHERE "Status"."Status" = "v"."Status" AND "Status"."Type" = 'V') AS "ListStatus", (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"."ContractRef", CAST("p"."ShiftSerialNumber" AS CHAR(20)) AS "ShiftRef", "p"."WhenEntered", (SELECT "name" FROM "person" WHERE "personid" = "tes"."personid")+(IF(SELECT "status" FROM "person" WHERE "personid" = "tes"."personid") = 'A' THEN ' (S)' endif) AS "FilledBy", "isnull"((IF "tes"."cancelreason" IN( 'C','E','G','L','R' ) THEN '(C)' ELSE IF "tes"."cancelreason" IN( 'P','S','T','W','X' ) THEN '(T)' ELSE '(G)' endif endif),'') AS "WhoCancelled", dateformat("isnull"("tes"."timefrom","p"."TimeFrom"),'hh:mm') AS "StartTime", dateformat("isnull"("tes"."timeto","p"."TimeTo"),'hh:mm') AS "FinishTime", (IF "FinishTime" < "StartTime" THEN "dateadd"("dd",1,"StartDate") ELSE "StartDate" endif) AS "FinishDate", (IF "WhoCancelled" = '(G)' THEN 3 ELSE IF "WhoCancelled" = '(T)' THEN 2 ELSE IF "isnull"((SELECT "surname" FROM "person" WHERE "personid" = "tes"."personid"),'') <> '' THEN 0 ELSE 1 endif endif endif) AS "Filled", "isnull"((SELECT FIRST 1 FROM "tempshift" WHERE "tempshiftplanid" = "p"."tempshiftplanid" AND "state" = 'C' AND "cancelreason" = '~'),0) AS "UnConfirmed", CAST(NULL AS long VARCHAR) AS "LongDescription", (IF 1 > 1 THEN "GetCompanyAddressOnLine"("company"."companyid") ELSE '' endif) AS "CompanyAddress", "tes"."personid" AS "PersonID", (IF(SELECT "count"() FROM "contactevent" WHERE "classcode" = 'SS' AND "description" = "ShiftRef") = 1 THEN 'In Progress' ELSE(CASE "tes"."state" WHEN 'B' THEN 'Confirmed' WHEN 'W' THEN 'Worked' WHEN 'P' THEN 'Pending' WHEN 'C' THEN(SELECT "cr"."name" FROM "TempShiftCancelReason" AS "cr" WHERE "cr"."TempShiftCancelReasonid" = "tes"."cancelreason")+' '+"tes"."note"+' at '+"dateformat"("tes"."whencancelled",'dd/mm/yyyy hh:nn') ELSE 'Pending' END) endif) AS "statusText", "isnull"((IF "tes"."state" = 'W' OR "WhoCancelled" = '(C)' OR "WhoCancelled" = '(T)' THEN 0 ELSE 1 endif),1) AS "CancelButton","isnull"((IF "tes"."clientconfirmed" = 0 THEN 1 ELSE 0 endif),0) AS "ConfirmButton","isnull"((IF("ConfirmButton" = 1 AND "tes"."state" <> 'C') THEN 1 ELSE 0 endif),0) AS "RejectButton", "isnull"((IF(tes.state = 'P') THEN 1 ELSE 0 endif),0) AS "FillButton", "p"."ReferenceCode" AS "PONumber", ('Ordered by: '+"p"."orderedby"+' at: '+"dateformat"("p"."whenentered",'dd/mm/yyyy hh:mm:ss')) AS "CreatedBy", "string"("dateformat"("tes"."WhenCancelled",'dd/mm/yyyy hh:nn:ss'),' by ',"isnull"((SELECT "name" FROM "staff" WHERE "staff"."staffid" = "tes"."WhoCancelled"),'(not recorded)')) AS "CancelState", tes.confirmedwith AS ConfirmedBy, IF isnull(tes.clientconfirmed,0)=1 THEN 'Client confirmed' ELSE IF isnull(tes.tempconfirmed,0)=1 THEN 'Candidate confirmed' ELSE NULL endif endif AS "ConfirmStatus" FROM "tempshift" AS "tes" RIGHT OUTER JOIN "TempShiftPlan" AS "p" LEFT OUTER JOIN "Vacancy" AS "v" KEY JOIN "Employment" AS "e" KEY JOIN("Company","Person" AS "Pe") WHERE "StartDate" BETWEEN DateRangeStart AND DateRangeEnd AND v.status = isnull(pStatus,v.status) AND charindex(v.staffid,ConsultantFilter) > 0; SELECT top x START at y "RowID","StartDate","CombinedTime","IsShift","Position","ShiftDescription",CompanyID,CompanyName,ContactID,ContactName,DetailStatus,ListStatus,Department,"Consultant","OurRef","ContractRef","ShiftRef","WhenEntered","FilledBy",WhoCAncelled,StartTime,FinishTime,FinishDate,"Filled","Unconfirmed","LongDescription","CompanyAddress","PersonID","TextStatus","CancelButton","ConfirmButton","RejectButton","FillButton","PONumber",CreatedBy,CancelState,ConfirmedBy FROM "DSet" ORDER BY StartDate DESC END /* DOC 2017-03-28 PC Create list of Current Requirements for Consultant. SearchString not implemented. IW-71 2017-07-28 PC modify initial dataset to use vacancy.staffid as well as retaining original company.staffid 2017-12-21 PC modify to delegate division selction to DivisionAccess table and provide post filtering on consultant 2018-05-03 PC IW-482 view requirements from all consultants 2018-05-07 PC modify method of selecting consultant division list 2018-05-25 PC IW-558 add candidate and client confirmed to shift status 2018-05-25 PC IW-563 change 'Created by' to 'Consultant' 2018-05-29 MHS confirmedwith is a free text field not a personid. Changed case on 'Candidate' for consistency Tests: valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate where companystartdate greater than today change desk to shift valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate with ORDERJOBS and companystartdate a long time ago valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate, invalid pStatus valid pWebUserID,pSlice,pSliceSize,PstartDate,pEndDate, pStatus */ /* TEST call NetTestSetup(''); delete from tagvalue where taglocation='C' and tagid='9X0' and id='TEST'; update vacancy set status='A' where vacancyid='TEST'; update tempdesk set desktype='W' where tempdeskid='test'; update vacancy set startdate=dateformat(now(*),'yyyy-mm-dd'),status='D',tempdeskid='TEST2',staffid='TEST' where vacancyid='TEST'; update iqxnetuser set staffid='TEST' where iqxnetuserid='TEST.CLIENT'; update company set staffid='TEST' where companyid='TEST'; select * from NetOwnerRequirementList(null,null,null,null,null,null); expect EOF; delete from iqxnetrightallowed where iqxnetrightid='ORDERJOBS' and IQXNetUserClassID='CLIENT'; select * from NetOwnerRequirementList('test.client',0,10,null,null,null); expect EOF; insert into iqxnetrightallowed (iqxnetrightid,iqxnetuserclassid,id,maintainonweb) values ('ORDERJOBS','CLIENT','*',1); select * from NetOwnerRequirementList('test.client',0,11,null,null,null) order by whenentered desc; expect CompanyName=test; select * from NetOwnerRequirementList('test.client',0,12,dateadd(dd,1,now()),null,null) order by whenentered desc; expect CompanyName=test; select * from NetOwnerRequirementList('test.client',0,13,null,null,null) order by whenentered desc; expect CompanyName=test; update tempdesk set desktype='S' where tempdeskid='test'; select * from NetOwnerRequirementList('test.client',0,14,null,null,null) order by whenentered desc; expect CompanyName=test; insert into tagvalue (taglocation,tagid,id,value) values ('C','9X0','TEST',dateadd(yy,1,today())); select * from NetOwnerRequirementList('test.client',0,15,null,null,null) order by whenentered desc; expect CompanyName=test; delete from tagvalue where taglocation='C' and tagid='9X0' and id='TEST'; select * from NetOwnerRequirementList('test.client',0,16,null,null,null) order by whenentered desc; expect CompanyName=test; delete from tagvalue where taglocation='C' and tagid='9X0' and id='TEST'; select * from NetOwnerRequirementList('test.client',0,16,null,null,null,'P') order by whenentered desc; expect EOF; update vacancy set status='P' where vacancyid='TEST'; select * from NetOwnerRequirementList('test.client',0,16,null,null,null,'P') order by whenentered desc; expect CompanyName=test; update vacancy set status=null where vacancyid='TEST'; */ }
127.0.0.1