pears.NetCandidateDocumentsAboutToExpire [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetCandidateDocumentsAboutToExpire ======
<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"."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;

*/
}
</code>