v014_to_v015.sql

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

BEGIN TRANSACTION
GO
EXECUTE sp_rename N'dbo.QW_TICKET.qw_datepending', N'Tmp_qw_datedelivered_1', 'COLUMN' 
GO
EXECUTE sp_rename N'dbo.QW_TICKET.Tmp_qw_datedelivered_1', N'qw_datedelivered', 'COLUMN' 
GO
COMMIT
GO

insert into QW_FIELDINFO_TYPE values (9,'qw_reasoncallid','Optional field')
GO
delete from qw_fieldinfo where
qw_tablename = 'qw_ticket' and qw_fieldinfo_type_id = 9
GO
insert into QW_FIELDINFO values ('qw_ticket',9,14,0,'Training Issue')
insert into QW_FIELDINFO values ('qw_ticket',9,14,1,'Patch/Upgrade')
insert into QW_FIELDINFO values ('qw_ticket',9,14,2,'Configuration Issue')
insert into QW_FIELDINFO values ('qw_ticket',9,14,3,'Correct Behaviour')
insert into QW_FIELDINFO values ('qw_ticket',9,14,4,'Documentation Error')
insert into QW_FIELDINFO values ('qw_ticket',9,14,5,'Insufficient Evidence')
insert into QW_FIELDINFO values ('qw_ticket',9,14,6,'Product Limitation')
insert into QW_FIELDINFO values ('qw_ticket',9,14,7,'Support Expired')
insert into QW_FIELDINFO values ('qw_ticket',9,14,8,'Withdrawn')
insert into QW_FIELDINFO values ('qw_ticket',9,14,9,'Other')
GO

CREATE VIEW [dbo].[QW_DD_TICKETWHYCLOSED_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_reasoncallid')
GO


--------- Add extra fields to QW_TICKET ----------


ALTER TABLE [dbo].[QW_TICKET] DROP COLUMN [qw_history]
GO
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_history] int NULL 
GO
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_customercallid] int NULL 
GO
ALTER TABLE [dbo].[QW_TICKET] ADD CONSTRAINT [fk_qw_ticket_customer] FOREIGN KEY([qw_customercallid])
REFERENCES [dbo].[QW_CUSTOMER] ([qw_customerid])
GO

UPDATE QW_FIELDINFO SET QW_ENUMLABEL = 'Delivered'
WHERE QW_TABLENAME = 'qw_ticket' AND QW_FIELDINFO_TYPE_ID = 1 AND QW_ENUMLABEL = 'Pending'
GO

delete from qw_fieldinfo where
qw_tablename = 'qw_ticket' and qw_fieldinfo_type_id = 1
insert into QW_FIELDINFO values ('qw_ticket',1,14,0,'New')
insert into QW_FIELDINFO values ('qw_ticket',1,14,1,'Assigned')
insert into QW_FIELDINFO values ('qw_ticket',1,14,2,'Owned')
insert into QW_FIELDINFO values ('qw_ticket',1,14,3,'Release Candidate')
insert into QW_FIELDINFO values ('qw_ticket',1,14,4,'Delivered')
insert into QW_FIELDINFO values ('qw_ticket',1,14,5,'Un-reproducible')
insert into QW_FIELDINFO values ('qw_ticket',1,14,6,'Client Issue')
insert into QW_FIELDINFO values ('qw_ticket',1,14,7,'Duplicate')
insert into QW_FIELDINFO values ('qw_ticket',1,14,8,'Parked')
insert into QW_FIELDINFO values ('qw_ticket',1,14,9,'Test Fail')
insert into QW_FIELDINFO values ('qw_ticket',1,14,10,'Closed')
GO

----- SOME PROPERTIES WITH DEFAULT VALUES -----
insert into QW_CUSTOMER (qw_customerid,qw_lastname,qw_firstname,qw_fullname) values (-1000,'CUSTOMER','UNKNOWN','Unknown Customer')

DECLARE @id int
EXEC dbo.QX_NEXT_KEY 'qx_sys_prop', 1, @id OUTPUT
insert into QX_SYS_PROP values (@id,'UNKNOWN_CUSTOMER','-1000',NULL,NULL,0,'Default ticket customer