v054_to_v055.sql

来自「CRM源码This file describes some issues tha」· SQL 代码 · 共 44 行

CREATE TABLE #TMP_TNR_TABLE(
    QW_RECIPIENT                     INT,
    QW_NOTIFICATIONID                INT
)
GO
INSERT INTO #TMP_TNR_TABLE SELECT * FROM QW_TICKETNOTIFICATION_RECIPIENT
GO
ALTER TABLE QW_TICKETNOTIFICATION_RECIPIENT drop FK_QW_RECIPIENT_NOTIFICATIONID 
GO
DROP TABLE QW_TICKETNOTIFICATION_RECIPIENT
GO
-- QW_TICKETNOTIFICATION_RECIP
CREATE TABLE dbo.QW_TICKETNOTIFICATION_RECIP (
    QW_RECIPIENT                     INT,
    QW_NOTIFICATIONID                INT
    CONSTRAINT FK_QW_RECIP_NOTIFICATIONID FOREIGN KEY (QW_NOTIFICATIONID) REFERENCES QW_TICKETNOTIFICATION (QW_NOTIFICATIONID)
)
go
INSERT INTO QW_TICKETNOTIFICATION_RECIP SELECT * FROM #TMP_TNR_TABLE
GO
DROP TABLE #TMP_TNR_TABLE
go

drop view QW_DD_NOTIFICATIONRECIPIENT_VW
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_recip')
		AND (qw_columnname = 'qw_recipient')
go
delete from qw_fieldinfo where qw_tablename = 'qw_ticketnotification_recipient' 
GO
/*ticket notification recipients*/
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_recip','qw_recipient',14,0,'Workgroup');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_recip','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_recip','qw_recipient',14,2,'Agent');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_recip','qw_recipient',14,3,'Owner');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_recip','qw_recipient',14,4,'Ticket Customer');
go


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