pears.NetDoSkillsMatch

CREATE PROCEDURE "pears"."NetDoSkillsMatch"( IN "pWebUserID" CHAR(20),IN "pVacancyID" CHAR(20),IN "pPersonID" CHAR(20) ) 
RESULT( "IsCand" CHAR(30) ) 
// IQXWeb
BEGIN
  DECLARE "VacancyDept" CHAR(20);
  DECLARE "MatchCount" INTEGER;
  IF(SELECT "count"() FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID") = 0 THEN
    SELECT '99:~please check data';
    RETURN
  END IF;
  IF "isnull"("pVacancyID",'') = '' THEN
    SELECT '98:~please check data';
    RETURN
  END IF;
  IF "isnull"("pPersonID",'') = '' THEN
    SELECT '97:~please check data';
    RETURN
  END IF;
  SET "VacancyDept" = (SELECT "departmentid" FROM "vacancy" WHERE "vacancyid" = "pVacancyID");
  SELECT top 1
    (SELECT "count"() FROM "tagvalue" AS "v" WHERE "v"."tagid" = "c"."tagid" AND "v"."taglocation" = "c"."sourcelocation" AND "v"."tagchoiceid" = "c"."tagchoiceid" AND "v"."id" = "pPersonID") AS "IsCand"
    FROM "criterion" AS "c"
    WHERE "c"."sourcelocation" = 'a'+"VacancyDept"
    AND "c"."id" = "pVacancyID"
    ORDER BY "IsCand" DESC
/* DOC
2017-01-03 PC compare a candidate to vacancy requirements, to decide if a candidate has skills to perform a vacancy
2018-08-27 PC imnprove error handler
 
Tests:
invalid pWebUserID
valid pWebUserID, invalid pVacancyID, pPersonID
valid pWebUserID, pVacancyID invalid pPersonID
valid pWebUserID, pVacancyID, pPersonID but personid does not have skills
valid pWebUserID, pVacancyID, pPersonID personid has skill
 
*/
/* 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.agency';
 
select * from NetDoSkillsMatch(null,null,null);
expect IsCand=99*;
select * from NetDoSkillsMatch('test.agency',null,null);
expect IsCand=98*;
select * from NetDoSkillsMatch('test.agency','test',null);
expect IsCand=97*;
select * from NetDoSkillsMatch('test.agency','test','test');
expect IsCand=0;
insert into tag (taglocation,tagid,tagtype,description) on existing skip values ('A~~','~~','T','TestTag');
insert into criterion (searchlocation,id,critid,sourcelocation,tagid) on existing skip values ('V~~','TEST','x','A~~','~~');
select * from NetDoSkillsMatch('test.agency','test','test');
expect 0;
 
*/
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetDoSkillsMatch" IS 
{CREATE PROCEDURE pears."NetDoSkillsMatch"( IN pWebUserID CHAR(20), IN pVacancyID CHAR(20), IN pPersonID CHAR(20) )
RESULT(IsCand CHAR(30))
// IQXWeb
BEGIN
    DECLARE VacancyDept CHAR(20);
    DECLARE MatchCount INTEGER;
 
    IF (SELECT COUNT(*) FROM iqxnetuser WHERE iqxnetuserid=pWebUserID) = 0 THEN
        SELECT '99:~please check data';
        RETURN;
    END IF;
    IF isnull(pVacancyID,'')='' THEN
        SELECT '98:~please check data';
        RETURN;
    END IF;
    IF isnull(pPersonID,'')='' THEN
        SELECT '97:~please check data';
        RETURN;
    END IF;
 
    SET VacancyDept=(SELECT departmentid FROM vacancy WHERE vacancyid=pVacancyID);
 
    SELECT top 1
        (SELECT COUNT(*) FROM tagvalue v WHERE v.tagid=c.tagid AND v.taglocation=c.sourcelocation AND v.tagchoiceid=c.tagchoiceid AND v.id=pPersonID) AS IsCand
    FROM 
        criterion c 
    WHERE 
        c.sourcelocation='a'+VacancyDept 
        AND c.id=pVacancyID
    ORDER BY
        IsCand DESC
 
/* DOC
2017-01-03 PC compare a candidate to vacancy requirements, to decide if a candidate has skills to perform a vacancy
2018-08-27 PC imnprove error handler
 
Tests:
    invalid pWebUserID
    valid pWebUserID, invalid pVacancyID, pPersonID
    valid pWebUserID, pVacancyID invalid pPersonID
    valid pWebUserID, pVacancyID, pPersonID but personid does not have skills
    valid pWebUserID, pVacancyID, pPersonID personid has skill
 
*/
 
/* 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.agency';
 
select * from NetDoSkillsMatch(null,null,null);
expect IsCand=99*;
select * from NetDoSkillsMatch('test.agency',null,null);
expect IsCand=98*;
select * from NetDoSkillsMatch('test.agency','test',null);
expect IsCand=97*;
select * from NetDoSkillsMatch('test.agency','test','test');
expect IsCand=0;
insert into tag (taglocation,tagid,tagtype,description) on existing skip values ('A~~','~~','T','TestTag');
insert into criterion (searchlocation,id,critid,sourcelocation,tagid) on existing skip values ('V~~','TEST','x','A~~','~~');
select * from NetDoSkillsMatch('test.agency','test','test');
expect 0;
 
*/
 
END
}