pears.NetSendMessageToCandidate

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
 
*/
}