v003_to_v004.sql

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

CREATE TABLE dbo.QW_FIELDINFO_TYPE (
	[qw_fieldinfo_type_id] [int] NOT NUll,
	[qw_name] [varchar] (50) NOT NULL,
	[qw_description] [varchar] (255) NULL,
	CONSTRAINT [pk_qw_fieldinfo_type] PRIMARY KEY CLUSTERED ([qw_fieldinfo_type_id]) WITH FILLFACTOR = 80 on [PRIMARY],
	CONSTRAINT [uq_qw_fieldinfo_type_name] UNIQUE ([qw_name])
) ON [PRIMARY]
GO

INSERT INTO QW_FIELDINFO_TYPE(qw_fieldinfo_type_id, qw_name, qw_description) VALUES (1, 'qw_status', 'Required field');
INSERT INTO QW_FIELDINFO_TYPE(qw_fieldinfo_type_id, qw_name, qw_description) VALUES (2, 'qw_priority', 'Required field');
INSERT INTO QW_FIELDINFO_TYPE(qw_fieldinfo_type_id, qw_name, qw_description) VALUES (3, 'qw_impact', 'Required field');
INSERT INTO QW_FIELDINFO_TYPE(qw_fieldinfo_type_id, qw_name, qw_description) VALUES (4, 'qw_type', 'Required field');
INSERT INTO QW_FIELDINFO_TYPE(qw_fieldinfo_type_id, qw_name, qw_description) VALUES (5, 'qw_source', 'Required field');
INSERT INTO QW_FIELDINFO_TYPE(qw_fieldinfo_type_id, qw_name, qw_description) VALUES (6, 'qw_response', 'Required for moving Ticket to Pending status');
GO

ALTER TABLE dbo.QW_FIELDINFO 
	ADD [qw_fieldinfo_type_id] [int] CONSTRAINT [fk_fieldinfo_type] FOREIGN KEY ([qw_fieldinfo_type_id]) REFERENCES QW_FIELDINFO_TYPE ([qw_fieldinfo_type_id])
GO

UPDATE QW_FIELDINFO 
set qw_fieldinfo_type_id=
	(select QW_FIELDINFO_TYPE.qw_fieldinfo_type_id from QW_FIELDINFO_TYPE where QW_FIELDINFO_TYPE.qw_name=QW_FIELDINFO.qw_columnname)
GO

ALTER TABLE QW_FIELDINFO
DROP COLUMN qw_columnname
GO

INSERT INTO QW_FIELDINFO (qw_tablename,qw_fieldinfo_type_id,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket',1,14,3,'Pending');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_fieldinfo_type_id,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket',1,14,4,'Un-reproducible');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_fieldinfo_type_id,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket',1,14,5,'Client Issue');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_fieldinfo_type_id,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket',1,14,6,'Duplicate');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_fieldinfo_type_id,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket',1,14,7,'Parked');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_fieldinfo_type_id,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket',1,14,8,'Test Fail');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_fieldinfo_type_id,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticket',1,14,9,'Closed');
GO

ALTER TABLE QW_FIELDINFO
ALTER COLUMN qw_fieldinfo_type_id int NOT NULL
GO

DROP VIEW dbo.QW_DD_TICKETSTATUS_VW
GO
CREATE VIEW dbo.QW_DD_TICKETSTATUS_VW AS
SELECT 
  qw_enumvalue AS pkey, qw_enumlabel AS value
FROM 
  QW_FIELDINFO
JOIN QW_FIELDINFO_TYPE ON QW_FIELDINFO_TYPE.qw_fieldinfo_type_id=QW_FIELDINFO.qw_fieldinfo_type_id
WHERE (qw_tablename = 'qw_ticket')
  AND (QW_FIELDINFO_TYPE.qw_name = 'qw_status')
GO

DROP VIEW dbo.QW_DD_TICKETPRIORITY_VW
GO
CREATE VIEW dbo.QW_DD_TICKETPRIORITY_VW AS
SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
FROM QW_FIELDINFO
JOIN QW_FIELDINFO_TYPE ON QW_FIELDINFO_TYPE.qw_fieldinfo_type_id=QW_FIELDINFO.qw_fieldinfo_type_id
WHERE (qw_tablename = 'qw_ticket')
  AND (QW_FIELDINFO_TYPE.qw_name = 'qw_priority')
GO

DROP VIEW dbo.QW_DD_TICKETTYPE_VW
GO
CREATE VIEW dbo.QW_DD_TICKETTYPE_VW AS
SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
FROM QW_FIELDINFO
JOIN QW_FIELDINFO_TYPE ON QW_FIELDINFO_TYPE.qw_fieldinfo_type_id=QW_FIELDINFO.qw_fieldinfo_type_id
WHERE (qw_tablename = 'qw_ticket')
  AND (QW_FIELDINFO_TYPE.qw_name = 'qw_type')
GO

DROP VIEW dbo.QW_DD_TICKETSOURCE_VW
GO
CREATE VIEW dbo.QW_DD_TICKETSOURCE_VW AS
SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
FROM QW_FIELDINFO
JOIN QW_FIELDINFO_TYPE ON QW_FIELDINFO_TYPE.qw_fieldinfo_type_id=QW_FIELDINFO.qw_fieldinfo_type_id
WHERE (qw_tablename = 'qw_ticket')
  AND (QW_FIELDINFO_TYPE.qw_name = 'qw_source')
GO

DROP VIEW dbo.QW_DD_TICKETRESPONSE_VW
GO
CREATE VIEW dbo.QW_DD_TICKETRESPONSE_VW AS
SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
FROM QW_FIELDINFO
JOIN QW_FIELDINFO_TYPE ON QW_FIELDINFO_TYPE.qw_fieldinfo_type_id=QW_FIELDINFO.qw_fieldinfo_type_id
WHERE (qw_tablename = 'qw_ticket')
  AND (QW_FIELDINFO_TYPE.qw_name = 'qw_response')
GO

DROP VIEW dbo.QW_DD_TICKETIMPACT_VW
GO
CREATE VIEW dbo.QW_DD_TICKETIMPACT_VW AS
SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
FROM QW_FIELDINFO
JOIN QW_FIELDINFO_TYPE ON QW_FIELDINFO_TYPE.qw_fieldinfo_type_id=QW_FIELDINFO.qw_fieldinfo_type_id
WHERE (qw_tablename = 'qw_ticket')
  AND (QW_FIELDINFO_TYPE.qw_name = 'qw_impact')
GO

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