v021_to_v022.sql

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

CREATE TABLE [dbo].[QW_INTERACTION](
	[qw_interactionid] [int] NOT NULL,
	[qw_isticket] [numeric](1,0) NOT NULL,
	[qw_source] [int] NOT NULL,
	[qw_problem] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
	[qw_description] [varchar](2000) COLLATE Latin1_General_CI_AS NOT NULL,
	[qw_intcomments] [text] COLLATE Latin1_General_CI_AS NULL,
	[qw_notes] [text] COLLATE Latin1_General_CI_AS NULL,
	[qw_history] [int] NULL,
	[qw_agentid] [int] NOT NULL,
	[qw_datereceived] [datetime] NULL,
	[qw_datereported] [datetime] NULL,
	[qw_laststatuschanged] [datetime] NULL,
	[qw_quicktemplateid] [int] NULL,
	[qw_phonetime] [int] NULL,
	[qw_researchtime] [int] NULL,
	[qw_customerid] [int] NULL,
	[qw_employeeid] [int] NULL,
	[qw_datemodified] [datetime] NULL,
	[qw_modifiedbyid] [int] NULL,
 CONSTRAINT [pk_qw_interaction] PRIMARY KEY CLUSTERED
(
	[qw_interactionid] ASC
) WITH FILLFACTOR = 80 ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[QW_INTERACTION]  WITH CHECK ADD  CONSTRAINT [fk_qw_inter_agent] FOREIGN KEY([qw_agentid])
REFERENCES [dbo].[QW_EMPLOYEE] ([qw_employeeid])
GO
ALTER TABLE [dbo].[QW_INTERACTION] ADD CONSTRAINT [fk_qw_inter_modifiedby] FOREIGN KEY([qw_modifiedbyid])
REFERENCES [dbo].[QX_USERS] ([pkey])
GO
ALTER TABLE [dbo].[QW_INTERACTION] ADD CONSTRAINT [fk_qw_inter_customer] FOREIGN KEY([qw_customerid])
REFERENCES [dbo].[QW_CUSTOMER] ([qw_customerid])
GO
ALTER TABLE [dbo].[QW_INTERACTION] ADD CONSTRAINT [fk_qw_inter_employee] FOREIGN KEY([qw_employeeid])
REFERENCES [dbo].[QW_EMPLOYEE] ([qw_employeeid])
GO


CREATE TABLE [dbo].[QW_INTER_TICKET](
	[qw_linkid] [int] NOT NULL,
	[qw_interactionid] [int] NOT NULL,
	[qw_ticketid] [int] NOT NULL,
	[qw_datecreated] [datetime] NOT NULL,
	[qw_createdbyid] [int] NOT NULL,
CONSTRAINT [pk_qw_linkid] PRIMARY KEY CLUSTERED
(
	[qw_linkid] ASC
) WITH FILLFACTOR = 80 ON [PRIMARY],
CONSTRAINT [un_qw_interticket] UNIQUE 
(
	[qw_interactionid] ASC,
	[qw_ticketid] ASC
) 
) ON [PRIMARY]
GO

/*interaction source*/
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_interaction','qw_source',14,0,'Incoming Call');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_interaction','qw_source',14,1,'Outgoing Call');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_interaction','qw_source',14,2,'Fax');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_interaction','qw_source',14,2,'Incoming Email');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_interaction','qw_source',14,2,'Outgoing Email');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_interaction','qw_source',14,2,'Walk-up');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_interaction','qw_source',14,2,'Other');
GO

CREATE VIEW dbo.QW_DD_INTERSOURCE_VW AS
SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
FROM QW_FIELDINFO
WHERE (qw_tablename = 'qw_interaction')
  AND (qw_columnname = 'qw_source')
GO

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