====== pears.NetAgencyCurrentRequirementAdditionalComments ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."NetAgencyCurrentRequirementAdditionalComments"( in "pWebUserID" char(20),in "pLineID" char(22) ) 
result( "ActionTime" char(20),"SentTo" char(50),"Note" long varchar,"shiftref" char(255),"EventType" char(30),"ContactDate" char(10),"ContactTime" char(10) ) 
// IQXWeb
begin
  declare "VacID" char(20);
  declare "TempShiftID" char(20);
  declare "CompID" char(20);
  declare "TempShiftRef" char(20);
  declare "VacRef" char(20);
  declare "ConfirmContactClass" char(20);
  declare "DeclineContactClass" char(20);
  declare "ClientCommentClass" char(20);
  declare "SecAgID" char(20);
  set "SecAgID" = (select top 1 "employment"."companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" order by "isnull"("employment"."leavedate",current date) asc);
  if "left"("pLineId",1) = 'V' then
    set "VacID" = "right"("pLineID",20)
  end if;
  if "left"("pLineId",1) = 'P' then
    set "TempShiftID" = "right"("pLineID",20);
    set "VacID" = (select top 1 "vacancyid" from "tempshiftplan" where "tempshiftplanid" = "right"("pLineID",20))
  end if;
  set "CompID" = (select first "employment"."companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid");
  set "TempShiftRef" = (select "ShiftSerialNumber" from "TempShiftPlan" where "tempshiftplanid" = "TempShiftID");
  set "ConfirmContactClass" = (select top 1 "classcode" from "contactclass" where "classdescrip" = 'Confirm Interest' order by "sortorder" asc);
  set "DeclineContactClass" = (select top 1 "classcode" from "contactclass" where "classdescrip" = 'Decline Interest' order by "sortorder" asc);
  set "ClientCommentClass" = (select top 1 "classcode" from "contactclass" where "classdescrip" = 'Client Comment' order by "sortorder" asc);
  select distinct
    cast("dateformat"("ce"."contactdate",'dd/mm/yyyy') as char)+' '+cast("dateformat"("ce"."contacttime",'hh:nn') as char) as "ActionTime",
    "s"."name" as "SentTo",
    "ce"."notes" as "Note",
    "substring"("ce"."description","charindex"(':',"ce"."description")+1) as "ShiftRef",
    "cc"."classdescrip",
    "ce"."contactdate" as "contactdate",
    "ce"."contacttime" as "contacttime"
    from "vacancy" as "va"
      ,"tempshiftplan" as "tsp"
      right outer join "vacancy" as "va"
      key join "contactevent" as "ce"
      key join "staff" as "s"
      ,"contactevent" as "ce"
      key join "employment" as "e"
      ,"contactevent" as "ce" key join "contactclass" as "cc"
      ,"contactevent" as "ce" left outer join "person" as "p" left outer join "pay_employee" as "pe"
    where(("ce"."classcode" in( "ConfirmContactClass","DeclineContactClass","ClientCommentClass" ) and "va"."vacancyid" = "VacID")
    and "e"."companyid" = "SecAgID")
    --and pe.secondaryagencyid=SecAgID
    order by "contactdate" desc,"contacttime" desc
end /* DOC
2013-08-28 PC Provide additional Comments for the detail line of Current Requirements
2016-10-06 PC set retreive confirm / decline / Note entry from contact event table
2017-01-19 PC filter by events belonging to agency candidates only
2018-05-03 PC revise test routines
2019-03-05 PC IW-873 add top 1 and order by on subqueries

This routine requires three contact event types which must contain the following text:
Confirm Interest
Decline Interest
Client Comment
*/
/* 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 contactevent where contacteventid like 'TEST%';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';
insert into vacancy (vacancyid,departmentid,employmentid,status,position,staffid) values ('TESTTESTTESTTESTTEST','~~','TEST','[','test2','test');
insert into tempshiftplan (tempshiftplanid,vacancyid,description) on existing update defaults off values ('TESTTESTTESTTESTTEST','TESTTESTTESTTESTTEST','test2');
insert into "contactevent"( "contacteventid","vacancyid","staffid","employmentid","contactdate","contacttime","description","classcode","notes",whoentered,personid ) values
('TEST1','TESTTESTTESTTESTTEST','TEST','TEST',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'hh:mm:ss'),'Add comment:',(select top 1 classcode from contactclass where classdescrip='Confirm Interest' order by sortorder),'TestNote','TEST','TEST');
insert into pay_employee (personid,secondaryagencyid) on existing update defaults off values ('TEST','TEST');
select * from NetAgencyCurrentRequirementAdditionalComments('test.agency','VTESTTESTTESTTESTTEST');
expect SentTo=TestStaff,Note=TestNote;
expect EOF;
select * from NetAgencyCurrentRequirementAdditionalComments('test.agency','PTESTTESTTESTTESTTEST');
expect SentTo=TestStaff,Note=TestNote;
update pay_employee set secondaryagencyid='TEST' where personid='TEST';
select * from NetAgencyCurrentRequirementAdditionalComments('test.agency','PTESTTESTTESTTESTTEST');
expect SentTo=TestStaff,Note=TestNote;
expect EOF;
delete from contactevent where vacancyid='TESTTESTTESTTESTTEST';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyCurrentRequirementAdditionalComments" IS 
{create PROCEDURE pears."NetAgencyCurrentRequirementAdditionalComments"( in "pWebUserID" char(20),in "pLineID" char(22)) 
result( "ActionTime" char(20),"SentTo" char(50),"Note" long varchar,shiftref char(255),EventType char(30),ContactDate char(10),ContactTime char(10) ) 
// IQXWeb
begin
  declare "VacID" char(20);
  declare "TempShiftID" char(20);
  declare "CompID" char(20);
  declare "TempShiftRef" char(20);
  declare "VacRef" char(20);
  declare ConfirmContactClass char(20);
  declare DeclineContactClass char(20);
  declare ClientCommentClass char(20);
  declare SecAgID char(20);
  set SecAgID = (select top 1 "employment"."companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid" order by isnull(employment.leavedate,current date));
  if left(pLineId,1)='V' then
    set VacID=right(pLineID,20);
  end if;
  if left(pLineId,1)='P' then
    set TempShiftID=right(pLineID,20);
    set VacID=(select top 1 vacancyid from tempshiftplan where tempshiftplanid=right(pLineID,20));
  end if;
  set "CompID" = (select first "employment"."companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid");
  set "TempShiftRef" = (select "ShiftSerialNumber" from "TempShiftPlan" where "tempshiftplanid" = "TempShiftID");
  set ConfirmContactClass = (select top 1 classcode from contactclass where classdescrip='Confirm Interest' order by sortorder);
  set DeclineContactClass = (select top 1 classcode from contactclass where classdescrip='Decline Interest' order by sortorder);
  set ClientCommentClass = (select top 1 classcode from contactclass where classdescrip='Client Comment' order by sortorder);
  select distinct
    cast("dateformat"("ce"."contactdate",'dd/mm/yyyy') as char)+' '+cast("dateformat"("ce"."contacttime",'hh:nn') as char) as "ActionTime",
    "s"."name" as "SentTo",
    "ce"."notes" as "Note",
    "substring"("ce"."description","charindex"(':',"ce"."description")+1) as "ShiftRef",
     cc.classdescrip,
    "ce"."contactdate" as "contactdate",
    "ce"."contacttime" as "contacttime"
    from "vacancy" as "va"
      ,"tempshiftplan" as "tsp"
      right outer join "vacancy" as "va"
      key join "contactevent" as "ce"
      key join "staff" as "s"
      ,"contactevent" as "ce"
      key join "employment" as "e",
    contactevent ce key join contactclass cc,
    contactevent ce left outer join person p left outer join pay_employee pe
    where(("ce"."classcode" in (ConfirmContactClass,DeclineContactClass,ClientCommentClass) and "va"."vacancyid" = "VacID")
    and "e"."companyid" = "SecAgID")
    --and pe.secondaryagencyid=SecAgID
    order by "contactdate" desc,"contacttime" desc
end

/* DOC
2013-08-28 PC Provide additional Comments for the detail line of Current Requirements
2016-10-06 PC set retreive confirm / decline / Note entry from contact event table
2017-01-19 PC filter by events belonging to agency candidates only
2018-05-03 PC revise test routines
2019-03-05 PC IW-873 add top 1 and order by on subqueries

This routine requires three contact event types which must contain the following text:
    Confirm Interest
    Decline Interest
    Client Comment
*/

/* 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 contactevent where contacteventid like 'TEST%';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';
insert into vacancy (vacancyid,departmentid,employmentid,status,position,staffid) values ('TESTTESTTESTTESTTEST','~~','TEST','[','test2','test');
insert into tempshiftplan (tempshiftplanid,vacancyid,description) on existing update defaults off values ('TESTTESTTESTTESTTEST','TESTTESTTESTTESTTEST','test2');
insert into "contactevent"( "contacteventid","vacancyid","staffid","employmentid","contactdate","contacttime","description","classcode","notes",whoentered,personid ) values
        ('TEST1','TESTTESTTESTTESTTEST','TEST','TEST',dateformat(now(*),'yyyy-mm-dd'),dateformat(now(*),'hh:mm:ss'),'Add comment:',(select top 1 classcode from contactclass where classdescrip='Confirm Interest' order by sortorder),'TestNote','TEST','TEST');
insert into pay_employee (personid,secondaryagencyid) on existing update defaults off values ('TEST','TEST');
select * from NetAgencyCurrentRequirementAdditionalComments('test.agency','VTESTTESTTESTTESTTEST');
expect SentTo=TestStaff,Note=TestNote;
expect EOF;
select * from NetAgencyCurrentRequirementAdditionalComments('test.agency','PTESTTESTTESTTESTTEST');
expect SentTo=TestStaff,Note=TestNote;
update pay_employee set secondaryagencyid='TEST' where personid='TEST';
select * from NetAgencyCurrentRequirementAdditionalComments('test.agency','PTESTTESTTESTTESTTEST');
expect SentTo=TestStaff,Note=TestNote;
expect EOF;
delete from contactevent where vacancyid='TESTTESTTESTTESTTEST';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';
*/
}
</code>