v010_to_v011.sql

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

-------- DEF VALUES ----------
insert into QX_USERS (pkey,loginname,fullname,user_type) values (-1000,'defemp','DEFAULT_EMPLOYEE',1)
insert into QW_EMPLOYEE (qw_employeeid,qw_userid,qw_firstname,qw_lastname,qw_fullname) values (-1000,-1000,'DEFAULT','EMPLOYEE','DEFAULT_EMPLOYEE')
insert into QW_PRODUCT (qw_productid,qw_name) values (-1000,'DEFAULT_PRODUCT')
insert into QW_BUILDVERSION (qw_buildid,qw_name,qw_version,qw_productid) values (-1000,'DEFAULT_VERSION','0.0.0.0',-1000)
GO

--------- Change type ----------
ALTER TABLE [dbo].[QW_TICKET] DROP COLUMN [qw_response]
GO
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_response] varchar(2000) null
GO

ALTER TABLE [dbo].[QW_TICKET] DROP COLUMN [qw_description]
GO
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_description] varchar(2000) null
GO

--------- Add notepad fields ----------
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_intcomments] text NULL   
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_notes] text NULL   
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_history] text NULL   
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_descriptiontext] text NULL   
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_responsetext] text NULL   
GO

-------- Actor section -----------
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_agentcallid] int NULL 
GO
ALTER TABLE [dbo].[QW_TICKET] ADD CONSTRAINT [fk_qw_ticket_agent] FOREIGN KEY([qw_agentcallid])
REFERENCES [dbo].[QW_EMPLOYEE] ([qw_employeeid])
UPDATE QW_TICKET SET qw_agentcallid = ISNULL((SELECT qw_employeeid FROM QW_EMPLOYEE WHERE qw_fullname='DEFAULT_EMPLOYEE'),-1000)
GO
ALTER TABLE [dbo].[QW_TICKET] ALTER COLUMN [qw_agentcallid] INT NOT NULL 
GO

ALTER TABLE [dbo].[QW_TICKET] ADD [qw_ownercallid] int NULL   
GO
ALTER TABLE [dbo].[QW_TICKET] ADD CONSTRAINT [fk_qw_ticket_owner] FOREIGN KEY([qw_ownercallid])
REFERENCES [dbo].[QW_EMPLOYEE] ([qw_employeeid])
GO

ALTER TABLE [dbo].[QW_TICKET] ADD [qw_wgroupcallid] int NULL   
GO
ALTER TABLE QW_TICKET ADD CONSTRAINT [fk_qw_ticket_wgroup] FOREIGN KEY([qw_wgroupcallid])
REFERENCES [dbo].[QX_WORKGROUP] ([pkey])
GO

ALTER TABLE [dbo].[QW_TICKET] ADD [qw_escwgroupcallid] int NULL   
GO
ALTER TABLE QW_TICKET ADD CONSTRAINT [fk_qw_ticket_escwgroup] FOREIGN KEY([qw_escwgroupcallid])
REFERENCES [dbo].[QX_WORKGROUP] ([pkey])
GO

----------- Metrics section ------------
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_datereported] datetime NULL   
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_dateassigned] datetime NULL   
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_dateowned] datetime NULL   
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_dateresolved] datetime NULL   
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_daterequested] datetime NULL   
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_eta] datetime NULL   
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_datepending] datetime NULL   
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_dateclosed] datetime NULL   
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_lastescalated] datetime NULL   
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_lastreopened] datetime NULL
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_laststatuschanged] datetime NULL
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_datemodified] datetime NULL
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_holdtilldate] datetime NULL
GO
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_phonetime] int NULL
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_researchtime] int NULL
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_reopencnt] int NULL
GO

---------- Product section -------------
ALTER TABLE [dbo].[QW_TICKET] ADD [qw_productcallid] int NULL 
GO
ALTER TABLE [dbo].[QW_TICKET] ADD CONSTRAINT [fk_qw_ticket_product] FOREIGN KEY([qw_productcallid])
REFERENCES [dbo].[QW_PRODUCT] ([qw_productid])
UPDATE QW_TICKET SET qw_productcallid = ISNULL((SELECT qw_productid FROM QW_PRODUCT WHERE qw_name='DEFAULT_PRODUCT'),-1000)
GO
ALTER TABLE [dbo].[QW_TICKET] ALTER COLUMN [qw_productcallid] INT NOT NULL 
GO

ALTER TABLE [dbo].[QW_TICKET] ADD [qw_componentcallid] int NULL   
GO
ALTER TABLE [dbo].[QW_TICKET] ADD CONSTRAINT [fk_qw_ticket_component] FOREIGN KEY([qw_componentcallid])
REFERENCES [dbo].[QW_COMPONENT] ([qw_componentid])
GO

ALTER TABLE [dbo].[QW_TICKET] ADD [qw_buildverscallid] int NULL 
GO
ALTER TABLE [dbo].[QW_TICKET] ADD CONSTRAINT [fk_qw_ticket_buildversion] FOREIGN KEY([qw_buildverscallid])
REFERENCES [dbo].[QW_BUILDVERSION] ([qw_buildid])
UPDATE QW_TICKET SET qw_buildverscallid = ISNULL((SELECT qw_buildid FROM QW_BUILDVERSION WHERE qw_name='DEFAULT_VERSION'),-1000)
GO
ALTER TABLE [dbo].[QW_TICKET] ALTER COLUMN [qw_buildverscallid] INT NOT NULL 
GO

ALTER TABLE [dbo].[QW_TICKET] ADD [qw_resverscallid] int NULL   
GO
ALTER TABLE [dbo].[QW_TICKET] ADD CONSTRAINT [fk_qw_ticket_resversion] FOREIGN KEY([qw_resverscallid])
REFERENCES [dbo].[QW_BUILDVERSION] ([qw_buildid])
GO


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