pears.EmailToSend [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.EmailToSend ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **EmailToSendID** | char(20) | NOT NULL |  |  |
| EmailType | char(10) | NULL |  | invoice etc for special behaviour |
| Sender | char(250) | NULL |  | Will override that specified by Email Account Settings if allowed by Email system |
| Recipients | long varchar | NOT NULL |  |  |
| Subject | char(250) | NOT NULL |  |  |
| Body | long varchar | NULL |  |  |
| PlainBody | long varchar | NULL |  |  |
| HTML | char(1) | NOT NULL | 'Y' | Y if Body is HTML |
| EmailAccountSettingsID | char(20) | NULL |  | Currently equates to IQXNetEmailDetailsID |
| 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 |  | For use by sending mechanism |
| LockID | char(20) | NULL |  | For use by sending mechanism |
| Source | char(100) | NULL |  | Where this record came from |
| WhenSent | timestamp | NULL |  |  |
| WhenEntered | timestamp | NOT NULL | 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 | 0 |  |
| ContactEventEmailLogID | char(20) | NULL |  |  |

===== Primary Key =====
  * EmailToSendID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| staff | WhoEntered | [[database:tables:pears_staff|pears.staff (staffid)]] | ON DELETE SET NULL |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_emailtosendattachment|pears.EmailToSendAttachment]] | EmailToSend | EmailToSendID | EmailToSendID |
| [[database:tables:pears_emailtosendcontactevent|pears.EmailToSendContactEvent]] | EmailToSend | EmailToSendID | EmailToSendID |

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| ReadyToSend | Index | ReadyToSend, WhenEntered |  |
| EmailToSendLocked | Index | Locked |  |

===== Triggers =====
^ Name ^ Timing ^ Event ^
| EmailToSendWhenSent | after | update of "WhenSent" order 1 |

===== Original SQL =====
<code sql>
-- 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
</code>