pears.NetClientShiftDetailsTemplates

CREATE PROCEDURE "pears"."NetClientShiftDetailsTemplates"( IN "pWebUserID" CHAR(20),IN "pVacancyID" CHAR(20) DEFAULT NULL ) 
RESULT( "LineID" CHAR(20),"TimeFrom" CHAR(10),"TimeTo" CHAR(10),"BreakMinutes" INTEGER,"Moveable" INTEGER,"Position" CHAR(50) ) 
BEGIN
  // IQXWeb
  SELECT "p"."TempShiftTemplateID" AS "LineID",
    CAST("dateformat"("p"."timefrom",'hh:mm') AS CHAR) AS "TimeFrom",
    CAST("dateformat"("p"."timeto",'hh:mm') AS CHAR) AS "TimeTo",
    "p"."breakMinutes" AS "BreakMinutes",
    "p"."moveable" AS "Moveable",
    "p"."Description" AS "Position"
    FROM "TempShiftTemplate" AS "p"
      KEY JOIN "TempShiftTemplateAllowed"
      KEY JOIN "Vacancy" AS "v"
      KEY JOIN "Employment"
      KEY JOIN "Company" AS "c"
    WHERE "v"."vacancyid" = "pvacancyid"
    AND "c"."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    AND "v"."status" = 'C'
    ORDER BY 6 ASC,2 ASC
END /* DOC
2016-10-18 PC shift template for dropdown on Current Requirements page
IW-69
 
 
Tests:
valid pwebuserid and pVacancyID but invalid state
valid pwebuserid and pVacancyID and state
 
*/
/* TEST
call NetTestSetup('');
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null 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 vacancy set status='P' where vacancyid='test';
select * from NetClientShiftDetailsTemplates('test.client','TEST');
expect EOF;
update vacancy set status='C' where vacancyid='test';
insert into TempShiftTemplateAllowed (TempShiftTemplateID,VacancyID) on existing update values ('test','test');
select * from NetClientShiftDetailsTemplates('test.client','TEST');
expect LineID=TEST,TimeFrom=08:00,TimeTo=16:00,BreakMinutes=30,Moveable=1,Position=test;
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null where vacancyid='test';
call NetTestSetup('');
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientShiftDetailsTemplates" IS 
{CREATE PROCEDURE pears."NetClientShiftDetailsTemplates"(IN pWebUserID CHAR(20),IN pVacancyID CHAR(20) DEFAULT NULL)
RESULT(LineID CHAR(20),TimeFrom CHAR(10),TimeTo CHAR(10),BreakMinutes INTEGER,Moveable INTEGER,POSITION CHAR(50))
BEGIN
// IQXWeb
  SELECT
    p."TempShiftTemplateID" AS LineID,
    CAST(dateformat(p.timefrom,'hh:mm') AS CHAR) AS TimeFrom,
    CAST(dateformat(p.timeto,'hh:mm') AS CHAR) AS TimeTo,
    p.breakMinutes AS BreakMinutes,
    p.moveable AS Moveable,
    "p"."Description" AS "Position"    
FROM 
    "TempShiftTemplate" AS "p" 
    KEY JOIN "TempShiftTemplateAllowed" 
    KEY JOIN "Vacancy" AS "v" 
    KEY JOIN "Employment" 
    KEY JOIN "Company" AS c
  WHERE 
    "v"."vacancyid" = "pvacancyid"
    AND c."CompanyID" = any(SELECT "companyid" FROM "employment" KEY JOIN "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserID")
    AND "v"."status" = 'C'  
  ORDER BY 6 ASC,2 ASC
END
 
/* DOC
2016-10-18 PC shift template for dropdown on Current Requirements page
IW-69
 
 
Tests:
    valid pwebuserid and pVacancyID but invalid state
    valid pwebuserid and pVacancyID and state
 
*/
 
/* TEST
call NetTestSetup('');
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null 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 vacancy set status='P' where vacancyid='test';
select * from NetClientShiftDetailsTemplates('test.client','TEST');
expect EOF;
update vacancy set status='C' where vacancyid='test';
insert into TempShiftTemplateAllowed (TempShiftTemplateID,VacancyID) on existing update values ('test','test');
select * from NetClientShiftDetailsTemplates('test.client','TEST');
expect LineID=TEST,TimeFrom=08:00,TimeTo=16:00,BreakMinutes=30,Moveable=1,Position=test;
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null where vacancyid='test';
call NetTestSetup('');
*/
}