v025_to_v026.sql

CRM源码This file describes some issues that should be implemented in future and how it should be imple

-- QW_CALLSNOTIFICATION

CREATE TABLE dbo.QW_TICKETNOTIFICATION (
    QW_NOTIFICATIONID                INT NOT NULL,
    QW_PRODUCTID                     INT,
    QW_ORGANIZATIONID                INT,
    QW_EMAIL                          VARCHAR(255),
    QW_INCLUDERECORD                  NUMERIC(2,0),
    QW_SENDMODE                       NUMERIC(2,0),
    QW_SUBJECT                        VARCHAR(255) NOT NULL,
    QW_MAILBODY						  TEXT COLLATE Latin1_General_CI_AS NULL,
    QW_DISABLE                        NUMERIC(2,0),
    QW_ONANYUPDATE                    NUMERIC,
    QW_DESCRIPTION                    VARCHAR(255),
  CONSTRAINT PK_QW_TICKETNOTIFICATION PRIMARY KEY (QW_NOTIFICATIONID)
)
go

-- QW_TICKETNOTIFICATION_PRIORITY

CREATE TABLE dbo.QW_TICKETNOTIFICATION_PRIORITY (
    QW_PRIORITY                       INT,
    QW_NOTIFICATIONID                INT
)
go
ALTER TABLE dbo.QW_TICKETNOTIFICATION_PRIORITY
	ADD FOREIGN KEY (QW_NOTIFICATIONID)
	REFERENCES QW_TICKETNOTIFICATION (QW_NOTIFICATIONID)
go

-- QW_TICKETNOTIFICATION_RECIPIENT
CREATE TABLE dbo.QW_TICKETNOTIFICATION_RECIPIENT (
    QW_RECIPIENT                      INT,
    QW_NOTIFICATIONID                INT
)
go
ALTER TABLE QW_TICKETNOTIFICATION_RECIPIENT
	ADD FOREIGN KEY (QW_NOTIFICATIONID)
	REFERENCES QW_TICKETNOTIFICATION (QW_NOTIFICATIONID)
go

-- QW_TICKETNOTIFICATION_STATUS
CREATE TABLE dbo.QW_TICKETNOTIFICATION_STATUS (
    QW_STATUS                         INT,
    QW_NOTIFICATIONID                INT
)
go
ALTER TABLE QW_TICKETNOTIFICATION_STATUS
	ADD FOREIGN KEY (QW_NOTIFICATIONID)
	REFERENCES QW_TICKETNOTIFICATION (QW_NOTIFICATIONID)
go


CREATE VIEW dbo.QW_DD_TNSENDMODE_VW AS
	SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
	FROM QW_FIELDINFO
	WHERE (qw_tablename = 'qw_ticketnotification')
		  AND (qw_columnname = 'qw_sendmode')
go

/*ticket notification send modes*/
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification','qw_sendmode',14,0,'Alert');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification','qw_sendmode',14,1,'Email');
go


CREATE VIEW dbo.QW_DD_NOTIFICATIONRECIPIENT_VW AS
	SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
	FROM QW_FIELDINFO
	WHERE (qw_tablename = 'qw_ticketnotification_recipient')
		AND (qw_columnname = 'qw_recipient')
go

/*ticket notification recipients*/
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_recipient','qw_recipient',14,0,'Workgroup');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_recipient','qw_recipient',14,1,'Use Email To Address');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_recipient','qw_recipient',14,2,'SAM');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_recipient','qw_recipient',14,3,'Owner');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_recipient','qw_recipient',14,4,'Customer');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_recipient','qw_recipient',14,5,'Account Manager');
go

-- all new update scripts must include a line like this:
UPDATE QX_SYS_PROP SET PROP_VAL = '026' WHERE (PROP_ID = 'DB_APP_VERSION');