v015_to_v016.sql

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

BEGIN TRANSACTION
    CREATE TABLE dbo.TMP_QW_FIELDINFO(
        qw_tablename varchar(128) NOT NULL,
        qw_columnname varchar(255) NOT NULL,
        qw_columntype int NOT NULL,
        qw_enumvalue int NULL,
        qw_enumlabel varchar(128) NULL
    ) ON [PRIMARY]
    GO

    INSERT INTO dbo.TMP_QW_FIELDINFO
    SELECT fi.qw_tablename, ft.qw_name, fi.qw_columntype, fi.qw_enumvalue, fi.qw_enumlabel
    FROM dbo.QW_FIELDINFO fi, dbo.QW_FIELDINFO_TYPE ft
    WHERE fi.qw_fieldinfo_type_id = ft.qw_fieldinfo_type_id
    GO

    DROP TABLE dbo.QW_FIELDINFO
    GO
    EXECUTE sp_rename N'dbo.TMP_QW_FIELDINFO', N'QW_FIELDINFO'
    GO
    DROP TABLE dbo.QW_FIELDINFO_TYPE
    GO
COMMIT

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
WHERE (qw_tablename = 'qw_ticket')
  AND (qw_columnname = '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
WHERE (qw_tablename = 'qw_ticket')
  AND (qw_columnname = '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
WHERE (qw_tablename = 'qw_ticket')
  AND (qw_columnname = '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
WHERE (qw_tablename = 'qw_ticket')
  AND (qw_columnname = 'qw_source')
GO

DROP VIEW dbo.QW_DD_TICKETRESPONSE_VW
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
WHERE (qw_tablename = 'qw_ticket')
  AND (qw_columnname = 'qw_impact')
GO

DROP VIEW dbo.QW_DD_ADDRESSTYPE_VW
GO
CREATE VIEW dbo.QW_DD_ADDRESSTYPE_VW AS
SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
FROM QW_FIELDINFO
WHERE (qw_tablename = 'qw_address')
  AND (qw_columnname = 'qw_addresstype')
GO

DROP VIEW dbo.QW_DD_EMPLOYEEREACHBY_VW
GO
CREATE VIEW dbo.QW_DD_EMPLOYEEREACHBY_VW AS
SELECT qw_enumvalue AS pkey, qw_enumlabel AS value
FROM QW_FIELDINFO
WHERE (qw_tablename = 'qw_employee')
  AND (qw_columnname = 'qw_reachby')
GO

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

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