v002_to_v003.sql

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

CREATE TABLE dbo.QW_TICKET (
    [qw_ticketid] [int] NOT NULL ,
    [qw_status] [int] NOT NULL ,
    [qw_priority] [int] NOT NULL ,
    [qw_type] [int] NOT NULL ,
    [qw_source] [int] NOT NULL ,
    [qw_response] [int] NULL ,
    [qw_impact] [int] NOT NULL ,
    [qw_problem] [varchar] (255) NOT NULL ,
    [qw_action] [varchar] (255) NULL ,
    [qw_description] [text] NOT NULL ,
    [qw_solution] [int] NULL ,
    CONSTRAINT [pk_qw_ticket] PRIMARY KEY CLUSTERED ([qw_ticketid]) WITH FILLFACTOR = 80 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE dbo.QW_TICKETEVENT (
    [qw_eventid] [int] NOT NULL ,
    [qw_ticketid] [int] NOT NULL ,
    [qw_iscomment] [bit] NULL ,
    [qw_isstatechange] [bit] NULL ,
    [qw_eventdate] [datetime] NULL ,
    [qw_creator] [int] NULL ,
    [qw_callage] [int] NULL ,
    [qw_supporttime] [int] NULL ,
    [qw_researchtime] [int] NULL ,
    [qw_phonetime] [int] NULL ,
    [qw_status] [int] NULL ,
    [qw_priority] [int] NULL ,
    [qw_owner] [int] NULL ,
    [qw_workgroup] [int] NULL ,
    [qw_customer] [int] NULL ,
    [qw_textlen] [int] NULL ,
    CONSTRAINT [pk_qw_ticketevent] PRIMARY KEY CLUSTERED ([qw_eventid]) WITH FILLFACTOR = 80 ON [PRIMARY],
    CONSTRAINT [fk_qw_ticketevent_ticket] FOREIGN KEY ([qw_ticketid]) REFERENCES QW_TICKET ([qw_ticketid])
) ON [PRIMARY] 
GO

CREATE TABLE dbo.QW_FIELDINFO (
	[qw_tablename] [varchar] (30) NOT NULL ,
	[qw_columnname] [varchar] (30) NOT NULL ,
	[qw_columntype] [int] NOT NULL ,
	[qw_enumvalue] [int] NULL ,
	[qw_enumlabel] [varchar] (128) NULL
) ON [PRIMARY]
GO

CREATE VIEW dbo.QW_DD_TICKETSTATUS_VW AS
SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
FROM QW_FIELDINFO
WHERE (qw_tablename = 'qw_ticket')
  AND (qw_columnname = 'qw_status')
GO

CREATE VIEW dbo.QW_DD_TICKETPRIORITY_VW AS
SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
FROM QW_FIELDINFO
WHERE (qw_tablename = 'qw_ticket')
  AND (qw_columnname = 'qw_priority')
GO

CREATE VIEW dbo.QW_DD_TICKETTYPE_VW AS
SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
FROM QW_FIELDINFO
WHERE (qw_tablename = 'qw_ticket')
  AND (qw_columnname = 'qw_type')
GO

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

CREATE VIEW dbo.QW_DD_TICKETRESPONSE_VW AS
SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
FROM QW_FIELDINFO
WHERE (qw_tablename = 'qw_ticket')
  AND (qw_columnname = 'qw_response')
GO

CREATE VIEW dbo.QW_DD_TICKETIMPACT_VW AS
SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
FROM QW_FIELDINFO
WHERE (qw_tablename = 'qw_ticket')
  AND (qw_columnname = 'qw_impact')
GO

INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_status',14,0,'New');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_status',14,1,'Owned');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_status',14,2,'Release Candidate');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_priority',14,0,'1-Critical');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_priority',14,1,'2-Serious');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_priority',14,2,'3-Moderate');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_priority',14,3,'4-Low');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_type',14,0,'BUG');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_type',14,1,'RFE');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_type',14,2,'INFO');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_source',14,0,'Email');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_source',14,1,'Phone');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_source',14,2,'Fax');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_source',14,3,'Voice Mail');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_source',14,4,'Walkup');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_source',14,5,'Self');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_source',14,6,'Customer Portal');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_response',14,0,'Email');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_response',14,1,'Phone');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_response',14,2,'Fax');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_response',14,3,'Voice Mail');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_response',14,4,'Walkup');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_response',14,5,'Self');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_impact',14,0,'All Sites');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_impact',14,1,'System Down');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_impact',14,2,'Site');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_impact',14,3,'Group');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket','qw_impact',14,4,'Individual');
GO

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