database:procedures:pears_netcandidatedocumentsabouttoexpire



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','[email protected]');
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','[email protected]');
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;
 
*/
}
  • database/procedures/pears_netcandidatedocumentsabouttoexpire.txt
  • Last modified: 2026/08/07 19:24
  • by 127.0.0.1