6.dataschema_v055.sql

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

CREATE TABLE QW_FIELDINFO (
    qw_tablename  varchar2(128) NOT NULL ,
    qw_columnname varchar2(255) NOT NULL ,
    qw_columntype int NOT NULL ,
    qw_enumvalue  int NULL ,
    qw_enumlabel  varchar2(128) NULL
);

CREATE TABLE QW_EMPLOYEE (
    qw_employeeid   int  NOT NULL ,
    qw_userid       int  NULL ,
    qw_employeenum  varchar2  (100) NULL ,
    qw_orgid        int  NULL ,
    qw_managerid    int  NULL ,
    qw_firstname    varchar2  (100) NOT NULL ,
    qw_lastname     varchar2  (100) NOT NULL ,
    qw_fullname     varchar2  (255) NULL ,
    qw_soundex      varchar2  (4) NULL ,
    qw_title        varchar2  (40) NULL ,
    qw_department   varchar2  (100) NULL ,
    qw_building     varchar2  (100) NULL ,
    qw_site         varchar2  (100) NULL ,
    qw_location     varchar2  (100) NULL ,
    qw_mailstop     varchar2  (100) NULL ,
    qw_phone        varchar2  (30) NULL ,
    qw_altphone     varchar2  (30) NULL ,
    qw_email        varchar2  (255) NULL ,
    qw_fax          varchar2  (30) NULL ,
    qw_pager        varchar2  (30) NULL ,
    qw_webpage      varchar2  (255) NULL ,
    qw_reachby      int  NULL ,
    qw_notes        clob  NULL ,
    qw_history      int  NULL ,
    CONSTRAINT pk_qw_employee PRIMARY KEY (qw_employeeid),
    CONSTRAINT fk_qw_employee_users FOREIGN KEY (qw_userid) REFERENCES QX_USERS ( pkey )
);

CREATE TABLE QW_ADDRESS (
    qw_addressid    int  NOT NULL ,
    qw_addresstype  int  NOT NULL ,
    qw_objectid     int  NOT NULL ,
    qw_objecttype   int  NOT NULL ,
    qw_address1     varchar2(255) NULL ,
    qw_address2     varchar2(255) NULL ,
    qw_city         varchar2(100) NULL ,
    qw_state        int  NULL ,
    qw_country      varchar2(255) NULL ,
    qw_zip          varchar2(40) NULL ,
    qw_phone        varchar2(30) NULL ,
    qw_fax          varchar2(30) NULL ,
    qw_other        varchar2(255) NULL ,
    qw_notes        clob  NULL ,
    CONSTRAINT pk_qw_address PRIMARY KEY (qw_addressid)
);

CREATE TABLE QW_ORGANIZATION (
    qw_orgid int  NOT NULL ,
    qw_parentid int  NULL ,
    qw_regionid varchar2(255)  NULL ,
    qw_name varchar2(255) NOT NULL ,
    qw_cocode varchar2(255) NULL ,
    qw_description varchar2(255) NULL ,
    qw_fax varchar2(30) NULL ,
    qw_phone varchar2(30) NULL ,
    qw_altphone varchar2(30) NULL ,
    qw_notes clob  NULL ,
    CONSTRAINT pk_qw_organization PRIMARY KEY (qw_orgid)
);

CREATE TABLE QW_PRODUCT (
    qw_productid int  NOT NULL,
    qw_name varchar2(100) NOT NULL,
    qw_modelnumber varchar2(100) NULL,
    qw_notes clob  NULL,
    CONSTRAINT pk_qw_product PRIMARY KEY (qw_productid)
);

CREATE TABLE QW_COMPONENT (
    qw_componentid int  NOT NULL,
    qw_name varchar2(100) NOT NULL,
    qw_description varchar2(255) NULL,
    qw_workgroupid int  NULL,
    qw_productid int  NULL,
    CONSTRAINT pk_qw_component PRIMARY KEY (qw_componentid),
    CONSTRAINT fk_qw_component_workgroup FOREIGN KEY (qw_workgroupid) REFERENCES QX_WORKGROUP (pkey),
    CONSTRAINT fk_qw_component_product FOREIGN KEY (qw_productid) REFERENCES QW_PRODUCT (qw_productid)
);

CREATE TABLE QW_BUILDVERSION (
    qw_buildid int  NOT NULL,
    qw_name varchar2(100) NOT NULL,
    qw_version varchar2(100) NOT NULL,
    qw_productid int  NOT NULL,
    qw_notes clob  NULL,
    CONSTRAINT pk_qw_buildversion PRIMARY KEY (qw_buildid),
    CONSTRAINT fk_qw_buildversion_product FOREIGN KEY (qw_productid) REFERENCES QW_PRODUCT (qw_productid)
);

CREATE TABLE QW_CUSTOMER (
    qw_customerid int  NOT NULL,
    qw_orgid int  NULL,
    qw_firstname varchar2(100) NULL ,
    qw_lastname varchar2(100) NULL ,
    qw_fullname varchar2(255) NULL ,
    qw_soundex varchar2(4) NULL ,
    qw_title varchar2(40) NULL ,
    qw_department varchar2(100) NULL ,
    qw_mailstop varchar2(100) NULL ,
    qw_phone varchar2(30) NULL ,
    qw_altphone varchar2(30) NULL ,
    qw_email varchar2(255) NULL ,
    qw_fax varchar2(30) NULL ,
    qw_notes clob  NULL ,
    CONSTRAINT pk_qw_customer PRIMARY KEY (qw_customerid)
);

CREATE TABLE QW_ENVIRONMENT (
    qw_envid int  NOT NULL,
    qw_envconfname varchar2(100) NULL,
    qw_productid int  NOT NULL,
    qw_orgid int  NOT NULL,
    qw_appos int  NOT NULL,
    qw_apposversion int  NULL,
    qw_apphardware int  NOT NULL,
    qw_dbos int  NOT NULL,
    qw_dbosversion int  NULL,
    qw_dbhardware int  NOT NULL,
    qw_description varchar2(2000) NOT NULL,
    CONSTRAINT pk_qw_environment PRIMARY KEY (qw_envid),
    CONSTRAINT fk_qw_productid_env FOREIGN KEY (qw_productid) REFERENCES QW_PRODUCT (qw_productid),
    CONSTRAINT fk_qw_orgid_env FOREIGN KEY (qw_orgid) REFERENCES QW_ORGANIZATION (qw_orgid)
);

CREATE TABLE QW_HARDWARE (
    qw_hardwareid  int  NOT NULL,
    qw_name        varchar2(255) NOT NULL ,
    qw_description varchar2(2000) NULL ,
    CONSTRAINT pk_qw_hardware PRIMARY KEY (qw_hardwareid)
);

CREATE TABLE QW_OS (
    qw_osid  int  NOT NULL,
    qw_name        varchar2(255) NOT NULL ,
    qw_description varchar2(2000) NULL ,
    CONSTRAINT pk_qw_os PRIMARY KEY (qw_osid)
);

CREATE TABLE QW_TICKET(
    qw_ticketid             int  NOT NULL,
    qw_status               int  NOT NULL,
    qw_priority             int  NOT NULL,
    qw_type                 int  NOT NULL,
    qw_source               int  NOT NULL,
    qw_impact               int  NOT NULL,
    qw_problem              varchar2(255) NOT NULL,
    qw_action               varchar2(255) NULL,
    qw_description          varchar2(2000) NOT NULL,
    qw_solution             int  NULL,
    qw_response             varchar2(2000) NULL,
    qw_intcomments          clob NULL,
    qw_notes                clob NULL,
    qw_history              int  NULL,
    qw_descriptiontext      clob NULL,
    qw_responsetext         clob   NULL,
    qw_agentid              int  NOT NULL,
    qw_ownerid              int  NULL,
    qw_workgroupid          int  NULL,
    qw_escworkgroupid       int  NULL,
    qw_datereported         date  NULL,
    qw_dateassigned         date  NULL,
    qw_dateowned            date  NULL,
    qw_dateresolved         date  NULL,
    qw_daterequested        date  NULL,
    qw_eta                  date  NULL,
    qw_datedelivered        date  NULL,
    qw_dateclosed           date  NULL,
    qw_lastescalated        date  NULL,
    qw_lastreopened         date  NULL,
    qw_laststatuschanged    date  NULL,
    qw_datemodified         date  NULL,
    qw_holdtilldate         date  NULL,
    qw_phonetime            int  NULL,
    qw_researchtime         int  NULL,
    qw_reopencnt            int  NULL,
    qw_productid            int  NOT NULL,
    qw_componentid          int  NULL,
    qw_repbuildversionid    int  NOT NULL,
    qw_resbuildversionid    int  NULL,
    qw_category             int  NULL,
    qw_subcategory          int  NULL,
    qw_otherreason          varchar2(1000) NULL,
    qw_reasoncode           int  NULL,
    qw_repenvironmentid     int  NULL,
    qw_testenvironmentid    int  NULL,
    qw_modifiedbyid         int  NULL,
    qw_customerid           int  NULL,
    qw_employeeid           int  NULL,
    CONSTRAINT pk_qw_ticket PRIMARY KEY (qw_ticketid),
    CONSTRAINT fk_qw_ticket_agent FOREIGN KEY (qw_agentid) REFERENCES QX_USERS (pkey),    
    CONSTRAINT fk_qw_ticket_buildversion FOREIGN KEY (qw_repbuildversionid) REFERENCES QW_BUILDVERSION (qw_buildid),
    CONSTRAINT fk_qw_ticket_component FOREIGN KEY (qw_componentid) REFERENCES QW_COMPONENT (qw_componentid),
    CONSTRAINT fk_qw_ticket_escwgroup FOREIGN KEY (qw_escworkgroupid) REFERENCES QX_WORKGROUP (PKEY),
    CONSTRAINT fk_qw_ticket_owner FOREIGN KEY (qw_ownerid) REFERENCES QX_USERS (pkey),
    CONSTRAINT fk_qw_ticket_product FOREIGN KEY (qw_productid) REFERENCES QW_PRODUCT (qw_productid),
    CONSTRAINT fk_qw_ticket_resversion FOREIGN KEY (qw_resbuildversionid) REFERENCES QW_BUILDVERSION (qw_buildid),
    CONSTRAINT fk_qw_ticket_wgroup FOREIGN KEY (qw_workgroupid) REFERENCES QX_WORKGROUP (PKEY),
    CONSTRAINT fk_qw_ticket_modifiedby FOREIGN KEY (qw_modifiedbyid) REFERENCES QX_USERS (pkey),
    CONSTRAINT fk_qw_ticket_customer FOREIGN KEY (qw_customerid) REFERENCES QW_CUSTOMER (qw_customerid),
    CONSTRAINT fk_qw_ticket_employee FOREIGN KEY (qw_employeeid) REFERENCES QW_EMPLOYEE (qw_employeeid)
);

CREATE TABLE QW_EVENTTYPE (
    qw_eventid   int NOT NULL,
    qw_eventtype varchar2(100),