pears.NetCandidateCreatePopup

CREATE PROCEDURE "pears"."NetCandidateCreatePopup"( IN "pWebUserID" CHAR(20),IN "pConsultantID" CHAR(20),IN "pPayrollNumber" CHAR(10),IN "pComment" CHAR(255),IN "pPersonID" CHAR(20) DEFAULT NULL ) 
RESULT( "pResult" CHAR(255) ) 
BEGIN
  // IQXNet
  DECLARE "ptype" CHAR(10);
  DECLARE "ptitle" CHAR(30);
  DECLARE "pStaffID" CHAR(20);
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  IF "pPersonID" IS NULL THEN
    SELECT '95:~Unique candidate link required';
    RETURN
  END IF;
  SET "ptype" = 'WebsiteQuery';
  SET "ptitle" = 'Query from website'+IF "isnull"("pPayrollNumber",'') <> '' THEN ' - '+"pPayrollNumber" endif;
  SET "pStaffID" = "isnull"("pConsultantID",(SELECT top 1 "staffid" FROM "IQXNetMessageRecipient" WHERE "MessageType" = "ptype" ORDER BY "staffid" ASC),(SELECT "staffid" FROM "person" WHERE "personid" = "pPersonID"));
  IF "pStaffID" IS NULL THEN
    SELECT '98:~Unable to create a reminder';
    RETURN
  END IF;
  IF "pPayrollNumber" IS NULL THEN
    SELECT '99:~Unable to create a reminder';
    RETURN
  END IF;
  IF "pComment" IS NULL THEN
    SELECT '97:~Please enter a comment, detailing your reasons';
    RETURN
  END IF;
  INSERT INTO "diary"( "diaryid","staffid","personid","employmentid","vacancyid","placementid","progressid","diaryfrom","description","notes","durationtype","popupminutes" ) VALUES
    ( "uniquekey"("pStaffID"),"pStaffID","pPersonID",NULL,NULL,NULL,NULL,CURRENT TIMESTAMP,"ptitle","pComment",'R',0 ) ;
  SELECT '0:~success'
END /* DOC
2016-10-13 PC allow candidate to create a notification popup
2018-07-24 PC include improved error handler
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
 
Tests:
invalid pWebUserID
valid pWebUserID but no values
valid pWebUserId, valid pConsultantID
valid pWebUserId, valid pConsultantID, valid payrollnumber
valid pWebUserId, valid pConsultantID, valid payrollnumber, valid comment
 
*/
/* TEST
call NetTestSetup('');
select * from NetCandidateCreatePopup(null,null,null,null);
expect 95*;
select * from NetCandidateCreatePopup('test.candidate',null,null,null);
expect 98*;
select * from NetCandidateCreatePopup('test.candidate','TEST',null,null);
expect 99*;
select * from NetCandidateCreatePopup('test.candidate','TEST','ABC123',null);
expect 97*;
select * from NetCandidateCreatePopup('test.candidate','TEST','ABC123','New Comment');
expect 0*;
select top 1 staffid,personid,replace(description,' ','!') as A,replace(notes,' ','!') as B from diary order by whenentered desc;
expect staffid=TEST,personid=TEST,A=Query!from!website!-!ABC123,B=New!Comment;
delete from diary 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateCreatePopup" IS 
{CREATE PROCEDURE pears."NetCandidateCreatePopup"(IN pWebUserID CHAR(20),IN pConsultantID CHAR(20), IN pPayrollNumber CHAR(10), IN pComment CHAR(255), IN pPersonID CHAR(20) DEFAULT NULL )
RESULT( pResult CHAR(255) )
BEGIN
// IQXNet
  DECLARE "ptype" CHAR(10);
  DECLARE "ptitle" CHAR(30);
  DECLARE "pStaffID" CHAR(20);
  SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
  IF pPersonID IS NULL THEN
    SELECT '95:~Unique candidate link required';
    RETURN;
  END IF;  
  SET "ptype" = 'WebsiteQuery';
  SET "ptitle" = 'Query from website'+ IF isnull(pPayrollNumber,'')!='' THEN ' - '+pPayrollNumber endif;
  SET pStaffID=isnull(pConsultantID,(SELECT top 1 staffid FROM IQXNetMessageRecipient WHERE MessageType = ptype ORDER BY staffid),(SELECT staffid FROM person WHERE personid=pPersonID));
 
  IF pStaffID IS NULL THEN
    SELECT '98:~Unable to create a reminder';
    RETURN
  END IF;
  IF pPayrollNumber IS NULL THEN
    SELECT '99:~Unable to create a reminder';
    RETURN;
  END IF;
  IF pComment IS NULL THEN
    SELECT '97:~Please enter a comment, detailing your reasons';
    RETURN
  END IF;
  INSERT INTO diary( diaryid,staffid,personid,employmentid,vacancyid,placementid,progressid,diaryfrom,description,notes,durationtype,popupminutes) VALUES( 
    uniquekey(pStaffID),pStaffID,pPersonID,NULL,NULL,NULL,NULL,CURRENT TIMESTAMP,ptitle,pComment,'R',0);
  SELECT '0:~success'
END
 
/* DOC
2016-10-13 PC allow candidate to create a notification popup
2018-07-24 PC include improved error handler
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
 
Tests:
    invalid pWebUserID
    valid pWebUserID but no values
    valid pWebUserId, valid pConsultantID
    valid pWebUserId, valid pConsultantID, valid payrollnumber
    valid pWebUserId, valid pConsultantID, valid payrollnumber, valid comment
 
*/
 
/* TEST
call NetTestSetup('');
select * from NetCandidateCreatePopup(null,null,null,null);
expect 95*;
select * from NetCandidateCreatePopup('test.candidate',null,null,null);
expect 98*;
select * from NetCandidateCreatePopup('test.candidate','TEST',null,null);
expect 99*;
select * from NetCandidateCreatePopup('test.candidate','TEST','ABC123',null);
expect 97*;
select * from NetCandidateCreatePopup('test.candidate','TEST','ABC123','New Comment');
expect 0*;
select top 1 staffid,personid,replace(description,' ','!') as A,replace(notes,' ','!') as B from diary order by whenentered desc;
expect staffid=TEST,personid=TEST,A=Query!from!website!-!ABC123,B=New!Comment;
delete from diary 
*/
}