====== pears.NetSendMessageToCandidate ======
<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"."NetSendMessageToCandidate"( in "pWebUserID" char(20),in "pRecipientID" char(20),in "pMessageText" long varchar ) 
result( "pResult" char(255) ) 
// IQXWeb
begin
  declare "PersID" char(20);
  declare "ContactStaffID" char(20);
  declare "ContactEventPK" char(20);
  declare "ContactEventCode" char(20);
  declare "contactEventTitle" char(100);
  declare "ContactEventText" long varchar;
  declare "ContactEventWhoEntered" char(20);
  declare "PersPhoneNumber" char(20);
  declare "CascadedDelivery" char(50);
  -- validate input and collect settings
  if "isnull"("pRecipientID",'') = '' then
    select '101:~ invalid recipient'
  end if;
  if "length"("pMessageText") = 0 then
    select '102:~no message'
  end if;
  -- populate constants
  set "PersID" = (select top 1 "p"."personid" from "person" as "p" where "p"."personid" = "pRecipientID");
  set "ContactStaffID" = (select top 1 "u"."staffid" from "iqxnetuser" as "u" where "u"."iqxnetuserid" = "pWebUserID");
  set "ContactEventWhoEntered" = (select top 1 "staffid" from "staff" where "staffid" = 'WEBUSER');
  set "PersPhoneNumber" = "replace"("getphone"('P','mobile',"PersID"),' ','');
  set "ContactEventText" = "trim"("pMessageText");
  set "ContactEventCode" = (select top 1 "sendsmscontactclass" from "params");
  set "ContactEventTitle" = 'SMS scheduled';
  if "isnull"("ContactStaffID",'') = '' then
    select '103:~ invalid staff'
  end if;
  if "isnull"("PersID",'') = '' then
    select '104:~ invalid recipient'
  end if;
  if "length"("PersPhoneNumber") = 0 then
    set "PersPhoneNumber" = "getphone"('P','e-mail',"PersID");
    set "ContactEventTitle" = "string"("ContactEventTitle",' as E-Mail');
    set "CascadedDelivery" = 'text unavailable so message sent by email';
    set "ContactEventCode" = (select top 1 "sendemailcontactclass" from "params")
  end if;
  if "length"("PersPhoneNumber") = 0 then
    select '103:~no SMS-enabled phone or email address'
  end if;
  -- create contact event
  set "ContactEventPK" = "uniquekey"('');
  insert into "contactevent"( "contacteventid","staffid","personid","description","notes","who","Contactdate","Contacttime","classcode","WhoEntered" ) values( "ContactEventPK","ContactStaffID","PersID","ContactEventTitle","ContactEventText",'A',current date,current time,"ContactEventCode","ContactEventWhoEntered" ) ;
  -- create action entry
  insert into "IQXNetAction"( "IQXNetActionID","IQXNetUserID","ActionType","TargetType","ActionState","WhenRequested","TargetID","ActionAddress" ) values( "uniquekey"(''),"pWebUserID",'SendSMS','CONTACTEVENT',0,"now"(),"ContactEventPK","PersPhoneNumber" ) ;
  select "string"('0:~',"isnull"("CascadedDelivery",'Success'))
end /* DOC
2018-02-01 PC send text message (or other format, eg email) to a candidate. Use in conjunction with SendMessageToCandidate.xml 
2018-07-31 PC IW-513 Improve error handler

*/
/* TEST

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetSendMessageToCandidate" IS 
{create PROCEDURE pears."NetSendMessageToCandidate"(in pWebUserID char(20), in pRecipientID char(20), in pMessageText long varchar)
result (pResult char(255))
// IQXWeb
begin
    declare PersID char(20);
    declare ContactStaffID char(20);
    declare ContactEventPK char(20);
    declare ContactEventCode char(20);
    declare contactEventTitle char(100);
    declare ContactEventText long varchar;
    declare ContactEventWhoEntered char(20);
    declare PersPhoneNumber char(20);
    declare CascadedDelivery char(50);

    -- validate input and collect settings
    if isnull(pRecipientID,'') = '' then
        select '101:~ invalid recipient';
    end if;
    if length(pMessageText) = 0  then
        select '102:~no message';
    end if;

    -- populate constants
    set PersID = (select top 1 p.personid from person p where p.personid=pRecipientID);
    set ContactStaffID = (select top 1 u.staffid from iqxnetuser u where u.iqxnetuserid=pWebUserID);
    set ContactEventWhoEntered = (select top 1 staffid from staff where staffid='WEBUSER');
    set PersPhoneNumber=replace(getphone('P','mobile',PersID),' ','');
    set ContactEventText=trim(pMessageText);
    set ContactEventCode=(select top 1 sendsmscontactclass from params);
    set ContactEventTitle = 'SMS scheduled';

    if isnull(ContactStaffID,'') = '' then
        select '103:~ invalid staff';
    end if;
    if isnull(PersID,'') = '' then
        select '104:~ invalid recipient';
    end if;

    if length(PersPhoneNumber) = 0  then
        set PersPhoneNumber=getphone('P','e-mail',PersID);
        set ContactEventTitle=string(ContactEventTitle,' as E-Mail');
        set CascadedDelivery='text unavailable so message sent by email';
        set ContactEventCode=(select top 1 sendemailcontactclass from params);
    end if;
    if length(PersPhoneNumber) = 0  then
        select '103:~no SMS-enabled phone or email address';
    end if;

    -- create contact event
    set ContactEventPK = uniquekey('');
    insert into contactevent (contacteventid,staffid, personid,description, notes, who, Contactdate, Contacttime, classcode,WhoEntered) values (ContactEventPK, ContactStaffID, PersID, ContactEventTitle, ContactEventText, 'A', current date, current time, ContactEventCode,ContactEventWhoEntered);

    -- create action entry
    insert into IQXNetAction (IQXNetActionID,IQXNetUserID,ActionType,TargetType,ActionState,WhenRequested,TargetID,ActionAddress) values (uniquekey(''),pWebUserID,'SendSMS','CONTACTEVENT',0,now(*),ContactEventPK,PersPhoneNumber);   
    select string('0:~',isnull(CascadedDelivery,'Success'));
end

/* DOC
2018-02-01 PC send text message (or other format, eg email) to a candidate. Use in conjunction with SendMessageToCandidate.xml 
2018-07-31 PC IW-513 Improve error handler

*/

/* TEST

*/
}
</code>