pears.NetAlreadyApplied

CREATE PROCEDURE "pears"."NetAlreadyApplied"( IN "pWebUserId" CHAR(20),IN "JobID" CHAR(20) DEFAULT NULL ) 
RESULT( "Applied" CHAR(250) )  -- 20120424 has a person already applied for this job? Use in conjunction with Job Search
BEGIN
  DECLARE "AppliedFor" CHAR(20);
  IF "pWebUserId" IS NULL THEN
    SELECT '99:~You are not logged in';
    RETURN
  END IF;
  SET "AppliedFor" = (SELECT top 1 "status" FROM "progress" WHERE "vacancyid" = "JobId" AND "personid" = any(SELECT "personid" FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "pWebUserId") ORDER BY "actiondate" DESC);
  IF "AppliedFor" IS NOT NULL THEN
    SELECT '1:~Already Applied'
  ELSE
    SELECT '1:~Apply'
  END IF
END /* DOC
2018-10-10 MHS IW-456 multi-cand fix and tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries
*/
/* TEST
call NetTestSetup('');
delete from progress where personid='TEST' and vacancyid='TEST';
select * from NetAlreadyApplied('test.candidate','TEST');
expect Applied=1:~Apply;
insert into progress (progressid,personid,vacancyid,staffid,status) values (uniquekey(''),'TEST','TEST',userstaffid,'A');
select * from NetAlreadyApplied('test.candidate','TEST');
expect Applied=1:~Already Applied;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAlreadyApplied" IS 
{CREATE PROCEDURE pears."NetAlreadyApplied"( IN "pWebUserId" CHAR(20),IN "JobID" CHAR(20) DEFAULT NULL ) 
RESULT( "Applied" CHAR(250) )  -- 20120424 has a person already applied for this job? Use in conjunction with Job Search
BEGIN
  DECLARE "AppliedFor" CHAR(20);
  IF "pWebUserId" IS NULL THEN
    SELECT '99:~You are not logged in';
    RETURN
  END IF;
  SET "AppliedFor" = (SELECT top 1 "status" FROM "progress" WHERE "vacancyid" = "JobId" AND "personid" IN (SELECT personid FROM iqxnetuserlink WHERE iqxnetuserid=pWebUserId) ORDER BY actiondate DESC);
  IF "AppliedFor" IS NOT NULL THEN
    SELECT '1:~Already Applied'
  ELSE
    SELECT '1:~Apply'
  END IF
END
 
/* DOC
2018-10-10 MHS IW-456 multi-cand fix and tests
2019-03-05 PC IW-873 add top 1 and order by on subqueries
*/
 
/* TEST
call NetTestSetup('');
delete from progress where personid='TEST' and vacancyid='TEST';
select * from NetAlreadyApplied('test.candidate','TEST');
expect Applied=1:~Apply;
insert into progress (progressid,personid,vacancyid,staffid,status) values (uniquekey(''),'TEST','TEST',userstaffid,'A');
select * from NetAlreadyApplied('test.candidate','TEST');
expect Applied=1:~Already Applied;
*/
}