pears.NetClientCombinedRequirementTemplates

CREATE PROCEDURE "pears"."NetClientCombinedRequirementTemplates"( IN "pWebUserID" CHAR(20) ) 
RESULT( "VacancyID" CHAR(20),"Position" long VARCHAR,"DateFrom" CHAR(10),"DateTo" CHAR(10),"Description" CHAR(2048),"VacType" CHAR(5),"SiteDetails" CHAR(300) ) 
BEGIN
  // IQXWeb
  DECLARE "CompanyCount" INTEGER;
  SET "CompanyCount" = (SELECT "count"() FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID");
  SELECT DISTINCT
    "v"."VacancyID" AS "LineID",
    (IF "isnull"("SiteDetails",'') <> '' THEN
      "string"("v"."Position",' ',"c"."Name",', ',"SiteDetails")
    ELSE
      "string"("v"."Position",' ',"c"."Name")
    endif) AS "Position",
    "dateformat"("v"."startdate",'dd/mm/yyyy') AS "DateFrom",
    "dateformat"("v"."expiry",'dd/mm/yyyy') AS "DateTo",
    "v"."othernotes" AS "Description",
    'CV' AS "VacType",
    "isnull"("string"("v"."sitename",(IF "isnull"("v"."addr1",'') <> '' THEN(IF "v"."Sitename" <> '' THEN ', ' endif)+"v"."addr1" endif),(IF "isnull"("v"."addr2",'') <> '' THEN ', '+"v"."addr2" endif),(IF "isnull"("v"."addr3",'') <> '' THEN ', '+"v"."addr3" endif),(IF "isnull"("v"."Town",'') <> '' THEN ', '+"v"."town" endif),(IF "isnull"("v"."postcode",'') <> '' THEN ', '+"v"."postcode" endif)),"GetCompanyAddressOnLine"("e"."companyid")) AS "SiteDetails"
    FROM "Vacancy" AS "v"
      KEY JOIN "Employment" AS "e"
      KEY JOIN "Company" AS "c"
    WHERE "c"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    AND "v"."temp" = 1
    AND "v"."Status" = '['
    AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') <> 'S'
    AND "isnull"("v"."expiry",'2099-12-31') >= CURRENT DATE UNION
  SELECT DISTINCT
    "v"."VacancyID" AS "LineID",
    (IF "isnull"("SiteDetails",'') <> '' THEN
      "string"("v"."Position",' ',"c"."Name",', ',"SiteDetails")
    ELSE
      "string"("v"."Position",' ',"c"."Name")
    endif) AS "Position",
    "dateformat"("v"."startdate",'dd/mm/yyyy') AS "DateFrom",
    "dateformat"("v"."expiry",'dd/mm/yyyy') AS "DateTo",
    "v"."othernotes" AS "Description",
    'SV' AS "VacType",
    "isnull"("string"("v"."sitename",(IF "isnull"("v"."addr1",'') <> '' THEN(IF "v"."Sitename" <> '' THEN ', ' endif)+"v"."addr1" endif),(IF "isnull"("v"."addr2",'') <> '' THEN ', '+"v"."addr2" endif),(IF "isnull"("v"."addr3",'') <> '' THEN ', '+"v"."addr3" endif),(IF "isnull"("v"."Town",'') <> '' THEN ', '+"v"."town" endif),(IF "isnull"("v"."postcode",'') <> '' THEN ', '+"v"."postcode" endif)),"GetCompanyAddressOnLine"("e"."companyid")) AS "SiteDetails"
    FROM "TempShiftTemplateAllowed"
      KEY JOIN "Vacancy" AS "v"
      KEY JOIN "Employment" AS "e"
      KEY JOIN "Company" AS "c"
    WHERE "c"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    AND "v"."temp" = 1 AND "isnull"((SELECT "desktype" FROM "tempdesk" WHERE "tempdeskid" = "v"."tempdeskid"),'') = 'S'
    AND "v"."status" = 'C'
    AND "isnull"("v"."expiry",'2099-12-31') >= CURRENT DATE UNION
  SELECT DISTINCT
    "v"."VacancyID" AS "LineID",
    "string"("v"."Position",' ',"c"."Name",', ',"SiteDetails") AS "Position",
    "dateformat"("v"."startdate",'dd/mm/yyyy') AS "DateFrom",
    NULL AS "DateTo",
    "v"."othernotes" AS "Description",
    'PV' AS "VacType",
    "isnull"("string"("v"."sitename",(IF "isnull"("v"."addr1",'') <> '' THEN(IF "v"."Sitename" <> '' THEN ', ' endif)+"v"."addr1" endif),(IF "isnull"("v"."addr2",'') <> '' THEN ', '+"v"."addr2" endif),(IF "isnull"("v"."addr3",'') <> '' THEN ', '+"v"."addr3" endif),(IF "isnull"("v"."Town",'') <> '' THEN ', '+"v"."town" endif),(IF "isnull"("v"."postcode",'') <> '' THEN ', '+"v"."postcode" endif)),"GetCompanyAddressOnLine"("e"."companyid")) AS "SiteDetails"
    FROM "Vacancy" AS "v"
      KEY JOIN "Employment" AS "e"
      KEY JOIN "Company" AS "c"
    WHERE "c"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    AND "v"."temp" = 0
    AND "v"."Status" = '['
    AND "isnull"("v"."expiry",'2099-12-31') >= CURRENT DATE
    ORDER BY 3 ASC,2 ASC
END /* DOC
2016-10-18 PC Create combined template list for Client Requirements
2017-01-20 PC IW-69 Add PV category
2019-02-18 PC IW-826 add details to template dropdown
2019-02-20 PC slightly modify output and test routines to remove comma when no company details
2019-02-21 PC IW-825 modify to include site details
*/
/* TEST
call NetTestSetup('');
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null,othernotes='test' where vacancyid='test';
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
update tempdesk set desktype='C' where tempdeskid='test';
select * from NetClientCombinedRequirementTemplates(null);
expect EOF;
select * from NetClientCombinedRequirementTemplates('test.client');
expect EOF;
update vacancy set temp=1,status='[',tempdeskid='TEST',expiry=dateadd(dd,-1,now(*)) where vacancyid='test';
select * from NetClientCombinedRequirementTemplates('test.client');
expect EOF;
update vacancy set expiry=dateadd(dd,1,now(*)),status='[',temp=1 where vacancyid='test';
select vacancyID,Position,replace(replace(Description,',','!'),' ','~') as A,VacType from NetClientCombinedRequirementTemplates('test.client');
expect Vacancyid=TEST,Position=test test,A=test,VacType=CV;
insert into tempdesk (tempdeskid,departmentid,name,desktype) on existing update defaults off values ('TEST2','~~','test2','S');
update vacancy set temp=1,tempdeskid='TEST2',expiry=dateadd(dd,1,now(*)) where vacancyid='test';
select vacancyID,Position,replace(replace(Description,',','!'),' ','~') as B,VacType from NetClientCombinedRequirementTemplates('test.client');
expect EOF;
update vacancy set expiry=dateadd(dd,1,now(*)),status='C',temp=1 where vacancyid='test';
update tempdesk set desktype='S' where tempdeskid='test';
select vacancyID,Position,replace(replace(Description,',','!'),' ','~') as C,VacType from NetClientCombinedRequirementTemplates('test.client');
expect Vacancyid=TEST,Position=test test,C=test,VacType=SV;
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null where vacancyid='test';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientCombinedRequirementTemplates" IS 
{CREATE PROCEDURE pears."NetClientCombinedRequirementTemplates"(IN pWebUserID CHAR(20))
RESULT(VacancyID CHAR(20),POSITION long VARCHAR,DateFrom CHAR(10),DateTo CHAR(10),Description CHAR(2048),VacType CHAR(5),SiteDetails CHAR(300) )
BEGIN
// IQXWeb
  DECLARE CompanyCount INTEGER;
  SET CompanyCount = (SELECT COUNT() FROM iqxnetuserlink WHERE iqxnetuserid = pWebUserID);
  SELECT DISTINCT
    v.VacancyID AS LineID,
    (IF isnull(SiteDetails,'') != '' THEN
        string(v.Position,' ',c."Name",', ',SiteDetails)
    ELSE 
        string(v.Position,' ',c."Name")
    endif) AS POSITION,
    dateformat(v.startdate,'dd/mm/yyyy') AS DateFrom,
    dateformat(v.expiry,'dd/mm/yyyy') AS DateTo,
    v.othernotes AS Description,
    'CV' AS VacType,
    isnull(string(v.sitename,(IF isnull(v.addr1,'') != '' THEN (IF v.Sitename != '' THEN ', ' endif)+v.addr1 endif ),(IF isnull(v.addr2,'') != '' THEN ', ' + v.addr2 endif ),(IF isnull(v.addr3,'') != '' THEN ', ' +v.addr3 endif ),(IF isnull(v.Town,'') != '' THEN ', ' + v.town endif ),(IF isnull(v.postcode,'') != '' THEN ', ' + v.postcode endif )),GetCompanyAddressOnLine(e.companyid)) AS SiteDetails
 
  FROM
    Vacancy AS v
    KEY JOIN Employment e
    KEY JOIN Company AS c
  WHERE 
    c.CompanyID = any(SELECT companyid FROM employment KEY JOIN iqxnetuserlink WHERE iqxnetuserid = pWebUserID)
    AND v.temp = 1 
    AND v.Status = '[' 
    AND isnull((SELECT desktype FROM tempdesk WHERE tempdeskid = v.tempdeskid),'') <> 'S'
    AND isnull(v.expiry,'2099-12-31') >= CURRENT DATE 
  UNION
  SELECT DISTINCT
    v.VacancyID AS LineID,
        (IF isnull(SiteDetails,'') != '' THEN
        string(v.Position,' ',c."Name",', ',SiteDetails)
    ELSE 
        string(v.Position,' ',c."Name")
    endif) AS POSITION,
    dateformat(v.startdate,'dd/mm/yyyy') AS DateFrom,
    dateformat(v.expiry,'dd/mm/yyyy') AS DateTo,
    v.othernotes AS Description,
    'SV' AS VacType,
    isnull(string(v.sitename,(IF isnull(v.addr1,'') != '' THEN (IF v.Sitename != '' THEN ', ' endif)+v.addr1 endif ),(IF isnull(v.addr2,'') != '' THEN ', ' + v.addr2 endif ),(IF isnull(v.addr3,'') != '' THEN ', ' +v.addr3 endif ),(IF isnull(v.Town,'') != '' THEN ', ' + v.town endif ),(IF isnull(v.postcode,'') != '' THEN ', ' + v.postcode endif )),GetCompanyAddressOnLine(e.companyid)) AS SiteDetails 
  FROM 
    TempShiftTemplateAllowed
    KEY JOIN Vacancy AS v
    KEY JOIN Employment e
    KEY JOIN Company AS c
  WHERE 
    c.CompanyID = any(SELECT companyid FROM employment KEY JOIN iqxnetuserlink WHERE iqxnetuserid = pWebUserID)
    AND v.temp = 1 AND isnull((SELECT desktype FROM tempdesk WHERE tempdeskid = v.tempdeskid),'') = 'S'
    AND v.status = 'C' 
    AND isnull(v.expiry,'2099-12-31') >= CURRENT DATE 
UNION
    SELECT DISTINCT
    v.VacancyID AS LineID,
    string(v.Position,' ',c."Name",', ',SiteDetails) AS POSITION,
    dateformat(v.startdate,'dd/mm/yyyy') AS DateFrom,
    NULL AS DateTo,
    v.othernotes AS Description,
    'PV' AS VacType,
    isnull(string(v.sitename,(IF isnull(v.addr1,'') != '' THEN (IF v.Sitename != '' THEN ', ' endif)+v.addr1 endif ),(IF isnull(v.addr2,'') != '' THEN ', ' + v.addr2 endif ),(IF isnull(v.addr3,'') != '' THEN ', ' +v.addr3 endif ),(IF isnull(v.Town,'') != '' THEN ', ' + v.town endif ),(IF isnull(v.postcode,'') != '' THEN ', ' + v.postcode endif )),GetCompanyAddressOnLine(e.companyid)) AS SiteDetails
  FROM
    Vacancy AS v
    KEY JOIN Employment e
    KEY JOIN Company AS c
  WHERE 
    c.CompanyID = any(SELECT companyid FROM employment KEY JOIN iqxnetuserlink WHERE iqxnetuserid = pWebUserID)
    AND v.temp = 0 
    AND v.Status = '[' 
    AND isnull(v.expiry,'2099-12-31') >= CURRENT DATE 
  ORDER BY 3 ASC,2 ASC
END
 
/* DOC
2016-10-18 PC Create combined template list for Client Requirements
2017-01-20 PC IW-69 Add PV category
2019-02-18 PC IW-826 add details to template dropdown
2019-02-20 PC slightly modify output and test routines to remove comma when no company details
2019-02-21 PC IW-825 modify to include site details
*/
 
/* TEST
call NetTestSetup('');
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null,othernotes='test' where vacancyid='test';
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
update tempdesk set desktype='C' where tempdeskid='test';
select * from NetClientCombinedRequirementTemplates(null);
expect EOF;
select * from NetClientCombinedRequirementTemplates('test.client');
expect EOF;
update vacancy set temp=1,status='[',tempdeskid='TEST',expiry=dateadd(dd,-1,now(*)) where vacancyid='test';
select * from NetClientCombinedRequirementTemplates('test.client');
expect EOF;
update vacancy set expiry=dateadd(dd,1,now(*)),status='[',temp=1 where vacancyid='test';
select vacancyID,Position,replace(replace(Description,',','!'),' ','~') as A,VacType from NetClientCombinedRequirementTemplates('test.client');
expect Vacancyid=TEST,Position=test test,A=test,VacType=CV;
insert into tempdesk (tempdeskid,departmentid,name,desktype) on existing update defaults off values ('TEST2','~~','test2','S');
update vacancy set temp=1,tempdeskid='TEST2',expiry=dateadd(dd,1,now(*)) where vacancyid='test';
select vacancyID,Position,replace(replace(Description,',','!'),' ','~') as B,VacType from NetClientCombinedRequirementTemplates('test.client');
expect EOF;
update vacancy set expiry=dateadd(dd,1,now(*)),status='C',temp=1 where vacancyid='test';
update tempdesk set desktype='S' where tempdeskid='test';
select vacancyID,Position,replace(replace(Description,',','!'),' ','~') as C,VacType from NetClientCombinedRequirementTemplates('test.client');
expect Vacancyid=TEST,Position=test test,C=test,VacType=SV;
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null where vacancyid='test';
*/
}