====== pears.NetCandidateDocumentsAboutToExpire ======
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
===== Original SQL =====
create procedure "pears"."NetCandidateDocumentsAboutToExpire"( in "pWebUserID" char(20),in "pPersonID" char(20) default null )
result( "PersonID" char(20),"PersonName" char(60),"Description" char(50),"ExpiryDate" char(10),"DaysToExpiry" double,"DaysExpired" double,"WarningDate" char(10),"HelpText" char(2048) )
begin
// IQXWeb
declare "UserType" char(20);
declare local temporary table "IDs"(
"ID" char(20) not null,
) not transactional;
set "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
set "UserType" = (select "IQXNetUserClassID" from "IQXNetUser" where "IQXNetUserID" = "pWebUserID");
if "UserType" = 'CANDIDATE' and "pPersonID" is not null then
insert into "IDs"( "id" ) values( "pPersonID" )
end if;
if "UserType" = 'AGENCY' then
insert into "IDs"(select distinct "personid" from "pay_employee" where "secondaryagencyid" = any(select "employment"."companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserlink"."iqxnetuserid" = "pWebUserID"))
end if;
select "pe"."personid",
"pe"."name",
"t"."description",
"dateformat"("dateadd"("day",(if "isnull"("v"."value",0) < 6000 or "v"."value" > 50000 then 6000 else "v"."value" endif),'1899-12-30'),'yyyy-mm-dd') as "ExpiryDate",
(if "today"() between "WarningDate" and "ExpiryDate" then "datediff"("dd","today"(),"ExpiryDate") else 0 endif) as "DaysToExpiry",
(if "ExpiryDate" <= "today"() then "datediff"("dd","ExpiryDate","Today"()) else 0 endif) as "DaysExpired",
"dateformat"("dateadd"("day","t"."expiry"*-1,"ExpiryDate"),'yyyy-mm-dd') as "WarningDate",
"isnull"("c"."email",(select "description" from "iqxnetswitch" where "iqxnetswitchid" = 'DEFAULT_HELP_MSG'),'Please contact a consultant for further assistance')
from "tag" as "t" join "tagvalue" as "v" on "v"."tagid" = "t"."tagid" and "v"."taglocation" = "t"."taglocation"
,"tagvalue" as "v" join "person" as "pe" on "pe"."personid" = "v"."id"
,"tagvalue" as "v" left outer join "tagcompliance" as "c" on "c"."taglocation" = "v"."taglocation" and "c"."tagid" = "v"."tagid"
,"tagvalue" as "v" join "IDs" on "IDS"."ID" = "v"."id"
where(("t"."taglocation" = 'P' or "t"."taglocation" like 'A%')
and "t"."expiry" > 0
and "DaysToExpiry"+"DaysExpired" > 0)
and "t"."PublicOnWeb" = 1
order by "DaysExpired" desc,
"DaysToExpiry" desc,
"pe"."name" asc
end /* DOC
2017-04-27 PC IW-208 As an agency and candidate I want to see when documents are about to expire. In the first instance I want to see them on the home page. Once selected I want to be taken to the relevant page to deal with them.
2017-04-27 PC (VHS) IW-205 remove association with document to present any question with an expiry margin
2017-08-31 PC change order of checks and hide valid documents for agency but not candidate
2017-10-30 PC modify candidate to hide valid documents
2017-11-22 PC limit expiry time to 6000 days to avoid overflow from incorrect dates
2018-01-05 PC IW-208 add tooltip popup help paragraph. use DEFAULT_HELP_MSG in iqxnetSettings to provide text for a default message.
2018-10-22 MHS IW-456 multi-cand fix and tests
2018-12-21 PC IW-777 PC fix multi-cand error where more than one cand returning more than one subselect line
2019-03-05 PC IW-873 add top 1 and order by on subqueries
2019-04-06 PC IW-876 trap null pPersonID and expiry date out of range
Tests:
candidate pWebUserID, not due to expire
candidate pWebUserID, due to expire
candidate pWebUserID, expired
agency pWebUserID
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.candidate';
delete from tagvalue where tagid='~~~';
delete from tag where tagid='~~~';
insert into tag (taglocation,tagid,tagtype,description,expiry) values ('P','~~~','D','TestTag',60);
insert into tagvalue (taglocation,tagid,id,value) values ('P','~~~','TEST',datediff(day, '1899-12-30', today(*) ));
select * from NetCandidateDocumentsAboutToExpire('test.candidate','test9');
expect EOF;
select * from NetCandidateDocumentsAboutToExpire('test.candidate','test');
expect EOF;
insert into tagcompliance (tagcomplianceid,taglocation,tagid,SMS,Email) on existing update values ('TEST','P','~~~','0123456789','test@test.com');
update tagvalue set value=datediff(day, '1899-12-30', today(*) )-20 where taglocation='P' and tagid='~~~' and id='TEST';
select * from NetCandidateDocumentsAboutToExpire('test.candidate','test');
expect personid=TEST,PersonName=test,description=TestTag;
update tagvalue set value=datediff(day, '1899-12-30', today(*) )+61 where taglocation='P' and tagid='~~~' and id='TEST';
select * from NetCandidateDocumentsAboutToExpire('test.candidate','test');
expect EOF;
update tagvalue set value=datediff(day, '1899-12-30', today(*) )-61 where taglocation='P' and tagid='~~~' and id='TEST';
select * from NetCandidateDocumentsAboutToExpire('test.candidate','test');
expect personid=TEST,PersonName=test,description=TestTag;
update tagvalue set value=datediff(day, '1899-12-30', today(*) )+50 where taglocation='P' and tagid='~~~' and id='TEST';
select * from NetCandidateDocumentsAboutToExpire('test.candidate','test');
expect personid=TEST,PersonName=test,description=TestTag;
update tagvalue set value=null where taglocation='P' and tagid='~~~' and id='TEST';
select * from NetCandidateDocumentsAboutToExpire('test.candidate','test');
expect personid=TEST,PersonName=test,description=TestTag;
*/
go
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateDocumentsAboutToExpire" IS
{create PROCEDURE pears."NetCandidateDocumentsAboutToExpire"(in pWebUserID char(20),in pPersonID char(20) default null)
RESULT(PersonID char(20),PersonName char(60), Description char(50),ExpiryDate char(10),DaysToExpiry double,DaysExpired double,WarningDate char(10),HelpText char(2048))
BEGIN
// IQXWeb
declare UserType char(20);
declare local temporary table "IDs"(
"ID" char(20) not null
) not transactional;
set pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
set UserType=(select IQXNetUserClassID from IQXNetUser where IQXNetUserID=pWebUserID);
if UserType='CANDIDATE' and pPersonID is not null then
insert into IDs (id) values (pPersonID);
end if;
if UserType='AGENCY' then
insert into IDs (select distinct personid from pay_employee where secondaryagencyid in (select employment.companyid from employment key join iqxnetuserlink where iqxnetuserlink.iqxnetuserid = pWebUserID));
end if;
select
pe.personid,
pe.name,
t.description,
dateformat(dateadd(day, (if isnull(v.value,0) < 6000 or v.value > 50000 then 6000 else v.value endif),'1899-12-30' ),'yyyy-mm-dd') as ExpiryDate,
(if today(*) between WarningDate and ExpiryDate then datediff(dd,today(*),ExpiryDate) else 0 endif) as DaysToExpiry,
(if ExpiryDate <= today(*) then datediff(dd,ExpiryDate,Today(*)) else 0 endif) as DaysExpired,
dateformat(dateadd(day, t.expiry*-1,ExpiryDate),'yyyy-mm-dd') as WarningDate,
isnull(c.email,(select description from iqxnetswitch where iqxnetswitchid='DEFAULT_HELP_MSG'),'Please contact a consultant for further assistance')
from
tag t join tagvalue v on v.tagid=t.tagid and v.taglocation=t.taglocation,
tagvalue v join person pe on pe.personid = v.id,
tagvalue v left outer join tagcompliance c on c.taglocation=v.taglocation and c.tagid=v.tagid,
tagvalue v join IDs on IDS.ID = v.id
where
((t.taglocation='P' or t.taglocation like 'A%')
and t.expiry > 0
and DaysToExpiry+DaysExpired > 0)
and t.PublicOnWeb=1
order by
DaysExpired desc,
DaysToExpiry desc,
pe.name
END
/* DOC
2017-04-27 PC IW-208 As an agency and candidate I want to see when documents are about to expire. In the first instance I want to see them on the home page. Once selected I want to be taken to the relevant page to deal with them.
2017-04-27 PC (VHS) IW-205 remove association with document to present any question with an expiry margin
2017-08-31 PC change order of checks and hide valid documents for agency but not candidate
2017-10-30 PC modify candidate to hide valid documents
2017-11-22 PC limit expiry time to 6000 days to avoid overflow from incorrect dates
2018-01-05 PC IW-208 add tooltip popup help paragraph. use DEFAULT_HELP_MSG in iqxnetSettings to provide text for a default message.
2018-10-22 MHS IW-456 multi-cand fix and tests
2018-12-21 PC IW-777 PC fix multi-cand error where more than one cand returning more than one subselect line
2019-03-05 PC IW-873 add top 1 and order by on subqueries
2019-04-06 PC IW-876 trap null pPersonID and expiry date out of range
Tests:
candidate pWebUserID, not due to expire
candidate pWebUserID, due to expire
candidate pWebUserID, expired
agency pWebUserID
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.candidate';
delete from tagvalue where tagid='~~~';
delete from tag where tagid='~~~';
insert into tag (taglocation,tagid,tagtype,description,expiry) values ('P','~~~','D','TestTag',60);
insert into tagvalue (taglocation,tagid,id,value) values ('P','~~~','TEST',datediff(day, '1899-12-30', today(*) ));
select * from NetCandidateDocumentsAboutToExpire('test.candidate','test9');
expect EOF;
select * from NetCandidateDocumentsAboutToExpire('test.candidate','test');
expect EOF;
insert into tagcompliance (tagcomplianceid,taglocation,tagid,SMS,Email) on existing update values ('TEST','P','~~~','0123456789','test@test.com');
update tagvalue set value=datediff(day, '1899-12-30', today(*) )-20 where taglocation='P' and tagid='~~~' and id='TEST';
select * from NetCandidateDocumentsAboutToExpire('test.candidate','test');
expect personid=TEST,PersonName=test,description=TestTag;
update tagvalue set value=datediff(day, '1899-12-30', today(*) )+61 where taglocation='P' and tagid='~~~' and id='TEST';
select * from NetCandidateDocumentsAboutToExpire('test.candidate','test');
expect EOF;
update tagvalue set value=datediff(day, '1899-12-30', today(*) )-61 where taglocation='P' and tagid='~~~' and id='TEST';
select * from NetCandidateDocumentsAboutToExpire('test.candidate','test');
expect personid=TEST,PersonName=test,description=TestTag;
update tagvalue set value=datediff(day, '1899-12-30', today(*) )+50 where taglocation='P' and tagid='~~~' and id='TEST';
select * from NetCandidateDocumentsAboutToExpire('test.candidate','test');
expect personid=TEST,PersonName=test,description=TestTag;
update tagvalue set value=null where taglocation='P' and tagid='~~~' and id='TEST';
select * from NetCandidateDocumentsAboutToExpire('test.candidate','test');
expect personid=TEST,PersonName=test,description=TestTag;
*/
}