pears.EmailToSend

• EmailToSendID

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."EmailToSend"
-- Table comment: 
-- Statement count: 13
 
CREATE TABLE "pears"."EmailToSend" (
    "EmailToSendID"                  CHAR(20) NOT NULL
   ,"EmailType"                      CHAR(10) NULL
   ,"Sender"                         CHAR(250) NULL
   ,"Recipients"                     long VARCHAR NOT NULL
   ,"Subject"                        CHAR(250) NOT NULL
   ,"Body"                           long VARCHAR NULL
   ,"PlainBody"                      long VARCHAR NULL
   ,"HTML"                           CHAR(1) NOT NULL DEFAULT 'Y'
   ,"EmailAccountSettingsID"         CHAR(20) NULL
   ,"Notificationtemplate"           CHAR(50) NULL
   ,"NotificationRecipientType"      CHAR(15) NULL
   ,"NotificationSubjectType"        CHAR(30) NULL
   ,"NotificationSubject"            long VARCHAR NULL
   ,"NotificationPersonID"           CHAR(20) NULL
   ,"NotificationAgencyid"           CHAR(20) NULL
   ,"NotificationStaffID"            CHAR(20) NULL
   ,"NotificationMakeContactEvent"   CHAR(5) NULL
   ,"HTMLWrappedAndSignedBy"         CHAR(20) NULL
   ,"ESignature"                     CHAR(20) NULL
   ,"Locked"                         TIMESTAMP NULL
   ,"LockID"                         CHAR(20) NULL
   ,"Source"                         CHAR(100) NULL
   ,"WhenSent"                       TIMESTAMP NULL
   ,"WhenEntered"                    TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP
   ,"NotificationMakeProgress"       CHAR(5) NULL
   ,"NotificationOverrideRecipient"  CHAR(3) NULL
   ,"SmtpCredentials"                CHAR(50) NULL
   ,"GraphCredentials"               CHAR(50) NULL
   ,"SmtpSettings"                   CHAR(20) NULL
   ,"WhoEntered"                     CHAR(20) NULL
   ,"ReadyToSend"                    tinyint NULL DEFAULT 0
   ,"ContactEventEmailLogID"         CHAR(20) NULL
   ,PRIMARY KEY ("EmailToSendID" ASC) 
)
GO
 
 
COMMENT ON COLUMN "pears"."EmailToSend"."EmailType" IS 
	'invoice etc for special behaviour'
GO
 
 
COMMENT ON COLUMN "pears"."EmailToSend"."Sender" IS 
	'Will override that specified by Email Account Settings if allowed by Email system'
GO
 
 
COMMENT ON COLUMN "pears"."EmailToSend"."HTML" IS 
	'Y if Body is HTML'
GO
 
 
COMMENT ON COLUMN "pears"."EmailToSend"."EmailAccountSettingsID" IS 
	'Currently equates to IQXNetEmailDetailsID'
GO
 
 
COMMENT ON COLUMN "pears"."EmailToSend"."Locked" IS 
	'For use by sending mechanism'
GO
 
 
COMMENT ON COLUMN "pears"."EmailToSend"."LockID" IS 
	'For use by sending mechanism'
GO
 
 
COMMENT ON COLUMN "pears"."EmailToSend"."Source" IS 
	'Where this record came from'
GO
 
 
ALTER TABLE "pears"."EmailToSend"
    ADD FOREIGN KEY "staff" ("WhoEntered" ASC)
    REFERENCES "pears"."staff" ("staffid")
    ON DELETE SET NULL
GO
 
 
CREATE INDEX "ReadyToSend" ON "pears"."EmailToSend"
    ( "ReadyToSend","WhenEntered" )
GO
 
 
CREATE INDEX "EmailToSendLocked" ON "pears"."EmailToSend"
    ( "Locked" )
GO
 
 
CREATE TRIGGER "EmailToSendWhenSent" after UPDATE OF "WhenSent"
ORDER 1 ON
"pears"."EmailToSend"
REFERENCING OLD AS "old_name" NEW AS "new_name"
FOR each ROW
BEGIN
  IF EXISTS(SELECT * FROM "sys"."syscolumns" WHERE "cname" = 'contacteventemaillogid' AND "tname" = 'emailtosend') THEN
    IF("new_name"."whensent" IS NOT NULL) AND("new_name"."ContactEventEmailLogID" IS NOT NULL) THEN
      UPDATE "ContactEventEmailLog" SET "whensent" = "new_name"."whensent" WHERE "ContactEventEmailLogID" = "new_name"."ContactEventEmailLogID"
    END IF END IF
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."EmailToSend"."EmailToSendWhenSent" IS 
{CREATE TRIGGER EmailToSendWhenSent 
 after UPDATE OF WhenSent ORDER 1 ON
EmailToSend
REFERENCING OLD AS old_name NEW AS new_name
FOR each ROW
BEGIN
   IF EXISTS(SELECT * FROM sys.syscolumns WHERE cname ='contacteventemaillogid' AND tname = 'emailtosend'  ) THEN 
   IF (new_name.whensent IS NOT NULL) AND (new_name.ContactEventEmailLogID IS NOT NULL) THEN   
       UPDATE ContactEventEmailLog SET whensent = new_name.whensent WHERE ContactEventEmailLogID  =   new_name.ContactEventEmailLogID;
   END IF  END IF;
END
}
GO