v033_to_v034.sql

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

delete from dbo.QW_FIELDINFO
where qw_tablename='qw_solution' and qw_columnname='qw_topic'
go

INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_topic',14,0,'Hardware');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_topic',14,1,'Software');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_topic',14,2,'Product');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_topic',14,3,'Service');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_topic',14,4,'Documentation');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_topic',14,5,'Operation');
INSERT INTO QW_FIELDINFO (qw_tablename,qw_columnname,qw_columntype,qw_enumvalue,qw_enumlabel) VALUES ('qw_solution','qw_topic',14,6,'Network');
go

CREATE TABLE dbo.QW_SOLUTION_CATEGORY (
    qw_categoryid  int  NOT NULL,
    qw_name        varchar(255) NOT NULL ,
    qw_description varchar(2000) NULL ,
    CONSTRAINT pk_qw_sol_category PRIMARY KEY (qw_categoryid)
)
GO

CREATE TABLE dbo.QW_SOLUTION_SUBCATEGORY (
    qw_subcategoryid int  NOT NULL,
    qw_categoryid    int  NOT NULL,
    qw_name          varchar(255) NOT NULL ,
    qw_description   varchar(2000) NULL ,
    CONSTRAINT pk_qw_sol_subcategory PRIMARY KEY (qw_subcategoryid),
    CONSTRAINT fk_qw_sol_categoryid FOREIGN KEY (qw_categoryid) REFERENCES QW_SOLUTION_CATEGORY (qw_categoryid)
)
GO

DROP TABLE dbo.QW_SOLUTION

CREATE TABLE dbo.QW_SOLUTION (
    QW_SOLUTIONID                  INT NOT NULL,
    QW_TOPIC                       NUMERIC(2,0),
    QW_SOLUTIONSTATUS              NUMERIC(2,0),
    QW_PROBLEM                     VARCHAR(255) NOT NULL,
    QW_SUMMARY                     VARCHAR(255) NOT NULL,
    qw_categoryid                  int  NULL,
    qw_subcategoryid               int  NULL,
    qw_audit                       int  NULL,
    QW_CREATOR                     INT NOT NULL,
    QW_Reviewer                    INT NULL,
    QW_Publisher                   INT NULL,
    QW_MODIFIEDBY                  INT NOT NULL,
--    lastused                       DATE,
    QW_LASTMODIFIED                DATETIME NOT NULL,
    QW_DATECREATED                 DATETIME NOT NULL,
    QW_DATEREVIEWED                DATETIME NULL,
    QW_DATEPUBLISHED               DATETIME NULL,
    QW_DATEEXPIRATION              DATETIME NOT NULL,
    QW_DOCUMENTID                  VARCHAR(100),
    QW_PROBLEMTEXT                 text NULL,
  CONSTRAINT PK_SOLUTION PRIMARY KEY (QW_SOLUTIONID),
  CONSTRAINT FK_QW_SOLUTION_CREATOR FOREIGN KEY (QW_CREATOR) REFERENCES QX_USERS (PKEY),
  CONSTRAINT FK_QW_SOLUTION_PUBLISHER FOREIGN KEY (QW_PUBLISHER) REFERENCES QX_USERS (PKEY),
  CONSTRAINT FK_QW_SOLUTION_REVIEWER FOREIGN KEY (QW_REVIEWER) REFERENCES QX_USERS (PKEY),
  CONSTRAINT FK_QW_SOLUTION_MODIFIEDBY FOREIGN KEY (QW_MODIFIEDBY) REFERENCES QW_EMPLOYEE (QW_EMPLOYEEID),
  CONSTRAINT FK_QW_SOLUTION_CATEGORY FOREIGN KEY (qw_categoryid) REFERENCES QW_SOLUTION_CATEGORY (qw_categoryid),
  CONSTRAINT FK_QW_SOLUTION_SUBCATEGORY FOREIGN KEY (qw_subcategoryid) REFERENCES QW_SOLUTION_SUBCATEGORY (qw_subcategoryid)
)
go

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