v029_to_v030.sql

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

-- QW_TICKETESCALATION
CREATE TABLE dbo.QW_TICKETESCALATION (
    QW_ESCALATIONID					INT NOT NULL,
    QW_PRODUCTID					INT,
	QW_PRODUCTFILTER				VARCHAR(255),
    QW_ORGANIZATIONID				INT,
	QW_EMPLOYEEID					INT,
	QW_WORKGROUPID					INT,
    QW_EMAIL						VARCHAR(255),
	QW_WAITTIME		                INT NOT NULL,
    QW_TIMEUNIT		                NUMERIC(2,0) NOT NULL,
	QW_FIREIF						INT NOT NULL,
    QW_REPEAT						NUMERIC(2,0),
    QW_SUBJECT                      VARCHAR(255),
    QW_MAILBODY						TEXT NULL,
    QW_DISABLE                      NUMERIC(2,0),
    QW_DESCRIPTION                  VARCHAR(255),
  CONSTRAINT PK_QW_TICKETESCALATION PRIMARY KEY (QW_ESCALATIONID)
)
go

-- QW_TICKETESCALATION_PRIORITY
CREATE TABLE dbo.QW_TICKETESCALATION_PRIORITY (
    QW_PRIORITY                       INT,
    QW_ESCALATIONID                INT
    CONSTRAINT FK_QW_PRIORITY_ESCALATIONID FOREIGN KEY (QW_ESCALATIONID) REFERENCES QW_TICKETESCALATION (QW_ESCALATIONID)
)
go

-- QW_TICKETESCALATION_STATUS
CREATE TABLE dbo.QW_TICKETESCALATION_STATUS (
    QW_STATUS                         INT,
    QW_ESCALATIONID                INT
    CONSTRAINT FK_QW_STATUS_ESCALATIONID FOREIGN KEY (QW_ESCALATIONID) REFERENCES QW_TICKETESCALATION (QW_ESCALATIONID)
)
go

-- QW_TICKETESCALATION_RECIPIENT
CREATE TABLE dbo.QW_TICKETESCALATION_RECIPIENT (
    QW_RECIPIENT                      INT,
    QW_ESCALATIONID                INT
    CONSTRAINT FK_QW_RECIPIENT_ESCALATIONID FOREIGN KEY (QW_ESCALATIONID) REFERENCES QW_TICKETESCALATION (QW_ESCALATIONID)
)
go


CREATE VIEW dbo.QW_DD_ESCALATIONSTATUS_VW AS
	SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
	FROM QW_FIELDINFO
	WHERE (qw_tablename = 'qw_ticketescalation_status')
		AND (qw_columnname = 'qw_status')
go

CREATE VIEW dbo.QW_DD_ESCALATIONPRIORITY_VW AS
	SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
	FROM QW_FIELDINFO
	WHERE (qw_tablename = 'qw_ticketescalation_priority')
		AND (qw_columnname = 'qw_priority')
go

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

CREATE VIEW dbo.QW_DD_ESCALATIONFIREIF_VW AS
	SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
	FROM QW_FIELDINFO
	WHERE (qw_tablename = 'qw_ticketescalation')
		  AND (qw_columnname = 'qw_fireif')
go

/*ticket escalation fire if options*/
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation','qw_fireif',14,0,'Not Closed');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation','qw_fireif',14,1,'Not Updated');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation','qw_fireif',14,2,'Not Assigned');
go


CREATE VIEW dbo.QW_DD_ESCALATIONTIMEUNIT_VW AS
	SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
	FROM QW_FIELDINFO
	WHERE (qw_tablename = 'qw_ticketescalation')
		  AND (qw_columnname = 'qw_timeunit')
go

/*ticket escalation time unit options*/
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation','qw_timeunit',14,0,'Minutes');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation','qw_timeunit',14,1,'Hours');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation','qw_timeunit',14,2,'Days');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation','qw_timeunit',14,3,'Work Days');
go


/*ticket escalation recipients*/
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_recipient','qw_recipient',14,0,'Assigned Workgroup');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_recipient','qw_recipient',14,1,'Assigned Employee');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_recipient','qw_recipient',14,2,'Assigned Email');
go
/*ticket escalation priority*/
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_priority','qw_priority',14,0,'Critical');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_priority','qw_priority',14,1,'Serious');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_priority','qw_priority',14,2,'Moderate');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_priority','qw_priority',14,3,'Low');
go
/*ticket escalation statuss*/
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_status','qw_status',14,0,'New');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_status','qw_status',14,1,'Assigned');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_status','qw_status',14,2,'Owned');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_status','qw_status',14,3,'Release Candidate');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_status','qw_status',14,4,'Delivered');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_status','qw_status',14,5,'Un-reproducible');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_status','qw_status',14,6,'Client Issue');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_status','qw_status',14,7,'Duplicate');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_status','qw_status',14,8,'Parked');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_status','qw_status',14,9,'Test Fail');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketescalation_status','qw_status',14,10,'Closed');
go

--- for notification 

CREATE VIEW dbo.QW_DD_NOTIFICATIONSTATUS_VW AS
	SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
	FROM QW_FIELDINFO
	WHERE (qw_tablename = 'qw_ticketnotification_status')
		AND (qw_columnname = 'qw_status')
go

CREATE VIEW dbo.QW_DD_NOTIFICATIONPRIORITY_VW AS
	SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
	FROM QW_FIELDINFO
	WHERE (qw_tablename = 'qw_ticketnotification_priority')
		AND (qw_columnname = 'qw_priority')
go

INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_priority','qw_priority',14,0,'Critical');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_priority','qw_priority',14,1,'Serious');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_priority','qw_priority',14,2,'Moderate');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_priority','qw_priority',14,3,'Low');
go

INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_status','qw_status',14,0,'New');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_status','qw_status',14,1,'Assigned');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_status','qw_status',14,2,'Owned');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_status','qw_status',14,3,'Release Candidate');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_status','qw_status',14,4,'Delivered');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_status','qw_status',14,5,'Un-reproducible');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_status','qw_status',14,6,'Client Issue');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_status','qw_status',14,7,'Duplicate');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_status','qw_status',14,8,'Parked');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_status','qw_status',14,9,'Test Fail');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_ticketnotification_status','qw_status',14,10,'Closed');
go

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