====== pears.NetDoSkillsMatch ======
<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"."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
}
</code>