6.dataschema_v055.sql

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

    CONSTRAINT pk_qw_eventid PRIMARY KEY (qw_eventid)
);

CREATE TABLE QW_TICKETEVENT (
    qw_eventid int NOT NULL ,
    qw_ticketid int NOT NULL ,
    qw_eventtype int NOT NULL,
    qw_eventdate date NULL ,
    qw_madeby int NOT NULL ,
    qw_customer int NULL ,
    qw_oldvalue varchar2(100) NULL,
    qw_newvalue varchar2(100) NULL,
    CONSTRAINT pk_qw_ticketevent PRIMARY KEY (qw_eventid),
--  CONSTRAINT fk_qw_ticketevent_ticket FOREIGN KEY (qw_ticketid) REFERENCES QW_TICKET (qw_ticketid),
    CONSTRAINT fk_qw_eventtype FOREIGN KEY (qw_eventtype) REFERENCES QW_EVENTTYPE (qw_eventid)
);

CREATE TABLE QW_INTERACTION(
    qw_interactionid   int  NOT NULL,
    qw_source          int  NOT NULL,
    qw_action          int  NULL,
    qw_problem         varchar2(255) NOT NULL,
    qw_description     varchar2(2000) NOT NULL,
    qw_intcomments     clob NULL,
    qw_notes           clob NULL,
    qw_history         int  NULL,
    qw_agentid         int  NOT NULL,
    qw_datereceived    date  NULL,
    qw_datereported    date  NULL,
    qw_phonetime       int  NULL,
    qw_researchtime    int  NULL,
    qw_customerid      int  NULL,
    qw_employeeid      int  NULL,
    qw_datemodified    date  NULL,
    qw_modifiedbyid    int  NULL,
    CONSTRAINT pk_qw_interaction PRIMARY KEY (qw_interactionid),
    CONSTRAINT fk_qw_inter_agent FOREIGN KEY (qw_agentid) REFERENCES QX_USERS (pkey),
    CONSTRAINT fk_qw_inter_modifiedby FOREIGN KEY (qw_modifiedbyid) REFERENCES QX_USERS (pkey),
    CONSTRAINT fk_qw_inter_customer FOREIGN KEY (qw_customerid) REFERENCES QW_CUSTOMER (qw_customerid),
    CONSTRAINT fk_qw_inter_employee FOREIGN KEY (qw_employeeid) REFERENCES QW_EMPLOYEE (qw_employeeid)
);

CREATE TABLE QW_INTER_TICKET(
    qw_interactionid int NOT NULL,
    qw_ticketid      int NOT NULL,
    CONSTRAINT pk_inter_ticket PRIMARY KEY (qw_interactionid, qw_ticketid)
);

CREATE TABLE QW_CATEGORY (
    qw_categoryid  int  NOT NULL,
    qw_name        varchar2(255) NOT NULL ,
    qw_description varchar2(2000) NULL ,
    CONSTRAINT pk_qw_category PRIMARY KEY (qw_categoryid)
);

CREATE TABLE QW_SUBCATEGORY (
    qw_subcategoryid int  NOT NULL,
    qw_categoryid    int  NOT NULL,
    qw_name          varchar2(255) NOT NULL ,
    qw_description   varchar2(2000) NULL ,
    CONSTRAINT pk_qw_subcategory PRIMARY KEY (qw_subcategoryid),
    CONSTRAINT fk_qw_categoryid FOREIGN KEY (qw_categoryid) REFERENCES QW_CATEGORY (qw_categoryid)
);

-- QW_CALLSNOTIFICATION

CREATE TABLE QW_TICKETNOTIFICATION (
    QW_NOTIFICATIONID                INT NOT NULL,
    QW_PRODUCTID                     INT,
    QW_ORGANIZATIONID                INT,
    QW_EMAIL                         varchar2(255),
    QW_INCLUDERECORD                 number(2,0),
    QW_SENDMODE                      number(2,0),
    QW_SUBJECT                       varchar2(255) NOT NULL,
    QW_MAILBODY                      clob NULL,
    QW_DISABLE                       number(2,0),
    QW_ONANYUPDATE                   number,
    QW_DESCRIPTION                   varchar2(255),
  CONSTRAINT PK_QW_TICKETNOTIFICATION PRIMARY KEY (QW_NOTIFICATIONID)
);

-- QW_TICKETNOTIFICATION_PRIORITY
CREATE TABLE QW_TICKETNOTIFICATION_PRIORITY (
    QW_PRIORITY                      INT,
    QW_NOTIFICATIONID                INT,
    CONSTRAINT FK_QW_PRIORITY_NOTIFICATIONID FOREIGN KEY (QW_NOTIFICATIONID) REFERENCES QW_TICKETNOTIFICATION (QW_NOTIFICATIONID)
);

-- QW_TICKETNOTIFICATION_RECIPIENT
CREATE TABLE QW_TICKETNOTIFICATION_RECIP (
    QW_RECIPIENT                     INT,
    QW_NOTIFICATIONID                INT,
    CONSTRAINT FK_QW_RECIP_NOTIFICATIONID FOREIGN KEY (QW_NOTIFICATIONID) REFERENCES QW_TICKETNOTIFICATION (QW_NOTIFICATIONID)
);

-- QW_TICKETNOTIFICATION_STATUS
CREATE TABLE QW_TICKETNOTIFICATION_STATUS (
    QW_STATUS                        INT,
    QW_NOTIFICATIONID                INT,
    CONSTRAINT FK_QW_STATUS_NOTIFICATIONID FOREIGN KEY (QW_NOTIFICATIONID) REFERENCES QW_TICKETNOTIFICATION (QW_NOTIFICATIONID)
);

-- QW_TICKETESCALATION
CREATE TABLE QW_TICKETESCALATION (
    QW_ESCALATIONID         INT NOT NULL,
    QW_PRODUCTID            INT,
    QW_PRODUCTFILTER        varchar2(255),
    QW_ORGANIZATIONID       INT,
    QW_SENDMODE             number(2,0),
    QW_EMPLOYEEID           INT,
    QW_WORKGROUPID          INT,
    QW_EMAIL                varchar2(255),
    QW_WAITTIME             INT NOT NULL,
    QW_TIMEUNIT		        number(2,0) NOT NULL,
    QW_FIREIF               INT NOT NULL,
    QW_REPEAT               number(2,0),
    QW_SUBJECT              varchar2(255),
    QW_MAILBODY             clob NULL,
    QW_DISABLE              number(2,0),
    QW_DESCRIPTION          varchar2(255),
  CONSTRAINT PK_QW_TICKETESCALATION PRIMARY KEY (QW_ESCALATIONID)
);

-- QW_TICKETESCALATION_PRIORITY
CREATE TABLE QW_TICKETESCALATION_PRIORITY (
    QW_PRIORITY                    INT,
    QW_ESCALATIONID                INT,
    CONSTRAINT FK_QW_PRIORITY_ESCALATIONID FOREIGN KEY (QW_ESCALATIONID) REFERENCES QW_TICKETESCALATION (QW_ESCALATIONID)
);

-- QW_TICKETESCALATION_STATUS
CREATE TABLE QW_TICKETESCALATION_STATUS (
    QW_STATUS                      INT,
    QW_ESCALATIONID                INT,
    CONSTRAINT FK_QW_STATUS_ESCALATIONID FOREIGN KEY (QW_ESCALATIONID) REFERENCES QW_TICKETESCALATION (QW_ESCALATIONID)
);

-- QW_TICKETESCALATION_RECIPIENT
CREATE TABLE QW_TICKETESCALATION_RECIPIENT (
    QW_RECIPIENT                   INT,
    QW_ESCALATIONID                INT,
    CONSTRAINT FK_QW_RECIPIENT_ESCALATIONID FOREIGN KEY (QW_ESCALATIONID) REFERENCES QW_TICKETESCALATION (QW_ESCALATIONID)
);

-- QW_ESCALATION_EVENT
CREATE TABLE QW_ESCALATION_EVENT (
    QW_ESCEVENTID					INT NOT NULL,
    QW_ESCALATIONID					INT NOT NULL,
    QW_TICKETID						INT NOT NULL,
    QW_NEXT_CHECK			        date  NOT NULL,
    QW_DATEMODIFIED			        date  NULL,
  CONSTRAINT PK_QW_ESCALATION_EVENT PRIMARY KEY (QW_ESCEVENTID)
);

-- QW_SOLUTION
CREATE TABLE QW_SOLUTION_CATEGORY (
    qw_categoryid  int  NOT NULL,
    qw_name        varchar2(255) NOT NULL ,
    qw_description varchar2(2000) NULL ,
    CONSTRAINT pk_qw_sol_category PRIMARY KEY (qw_categoryid)
);

CREATE TABLE QW_SOLUTION_SUBCATEGORY (
    qw_subcategoryid int  NOT NULL,
    qw_categoryid    int  NOT NULL,
    qw_name          varchar2(255) NOT NULL ,
    qw_description   varchar2(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)
);

CREATE TABLE QW_SOLUTION (
    qw_solutionid                  INT NOT NULL,
    qw_topic                       number(2,0),
    qw_solutionstatus              number(2,0),
    qw_categoryid                  int NULL,
    qw_subcategoryid               int NULL,
    qw_productid                   int NULL,
    qw_componentid                 int NULL,
    qw_summary                     varchar2(255) NOT NULL,
    qw_problem                     varchar2(2000) NOT NULL,
    qw_problemtext                 clob NULL,
    qw_solution                    varchar2(2000) NOT NULL,
    qw_solutiontext                clob NULL,
    qw_audit                       int NULL,
    qw_intcomments                 clob NULL,
    qw_creator                     INT NOT NULL,
    qw_reviewer                    INT NULL,
    qw_publisher                   INT NULL,
    qw_modifiedby                  INT NOT NULL,
    qw_lastmodified                date NOT NULL,
    qw_datecreated                 date NOT NULL,
    qw_datereviewed                date NULL,
    qw_datepublished               date NULL,
    qw_dateexpiration              date NOT NULL,
    qw_documentid                  varchar2(100),
  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 QX_USERS (PKEY),
  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)
);

CREATE TABLE QW_WORKFLOW (
    qw_workflowid  int  NOT NULL,
    qw_objecttype  int  NOT NULL,
    qw_statusfrom  int  NULL,
    qw_statusto    int  NULL,
    qw_roleid      int  NULL,
    CONSTRAINT pk_qw_workflow PRIMARY KEY (qw_workflowid)
);