pears.NetCandidateCreatePopup [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetCandidateCreatePopup ======
<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"."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 
*/
}
</code>