crm.sql

来自「CRM 不仅仅是一个管理名词和管理概念」· SQL 代码 · 共 577 行 · 第 1/2 页

CREATE TABLE crm_contact_folk (
  FolkID int(10) NOT NULL auto_increment,
  ContactID int(10) NOT NULL default '0',
  Name varchar(40) NOT NULL default '',
  Sex tinyint(1) NOT NULL default '0',
  Relation varchar(40) NOT NULL default '',
  Tel varchar(40) NOT NULL default '',
  Mobile varchar(40) NOT NULL default '',
  Hobby varchar(100) NOT NULL default '',
  PRIMARY KEY  (FolkID),
  KEY ContactID (ContactID)
) TYPE=MyISAM COMMENT='联系人亲属信息表';

# --------------------------------------------------------

#
# 数据表的结构 `crm_corp_kind`
#

CREATE TABLE crm_corp_kind (
  CorpKindID mediumint(10) NOT NULL auto_increment,
  CorpKindName varchar(40) NOT NULL default '',
  PRIMARY KEY  (CorpKindID)
) TYPE=MyISAM COMMENT='企业性质表';

#
# 导出下面的数据库内容 `crm_corp_kind`
#

INSERT INTO crm_corp_kind VALUES (1, '中外合资');
INSERT INTO crm_corp_kind VALUES (2, '外商独资');
INSERT INTO crm_corp_kind VALUES (3, '国营企业');
INSERT INTO crm_corp_kind VALUES (4, '民营企业');
# --------------------------------------------------------

#
# 数据表的结构 `crm_expense`
#

CREATE TABLE crm_expense (
  ExpenseID int(10) NOT NULL auto_increment,
  ExpenseName varchar(100) NOT NULL default '',
  UserID int(10) NOT NULL default '0',
  DeptID int(10) NOT NULL default '0',
  AccountID int(10) NOT NULL default '0',
  ProjectID int(10) NOT NULL default '0',
  ShiYou varchar(100) NOT NULL default '',
  RealExpense decimal(10,2) NOT NULL default '0.00',
  ProjectExpenseType varchar(10) NOT NULL default '',
  ProjectExpenseName varchar(40) NOT NULL default '',
  UseTime date NOT NULL default '0000-00-00',
  PRIMARY KEY  (ExpenseID),
  KEY AccountID (AccountID),
  KEY ProjectID (ProjectID),
  KEY UserID (UserID),
  KEY DeptID (DeptID)
) TYPE=MyISAM COMMENT='费用表';

# --------------------------------------------------------

#
# 数据表的结构 `crm_expense_name`
#

CREATE TABLE crm_expense_name (
  ExpenseTypeNameID tinyint(2) NOT NULL auto_increment,
  ExpenseTypeName varchar(40) NOT NULL default '',
  ExpenseType tinyint(2) NOT NULL default '0',
  PRIMARY KEY  (ExpenseTypeNameID)
) TYPE=MyISAM COMMENT='费用名称表';

#
# 导出下面的数据库内容 `crm_expense_name`
#

INSERT INTO crm_expense_name VALUES (1, '出差长途交通', 2);
INSERT INTO crm_expense_name VALUES (2, '出差市内交通', 2);
INSERT INTO crm_expense_name VALUES (3, '住宿费', 2);
INSERT INTO crm_expense_name VALUES (4, '招待费', 2);
INSERT INTO crm_expense_name VALUES (5, '礼品费', 2);
INSERT INTO crm_expense_name VALUES (6, '办公费', 2);
INSERT INTO crm_expense_name VALUES (7, '其他项目费用', 2);
INSERT INTO crm_expense_name VALUES (8, '市内交通', 1);
INSERT INTO crm_expense_name VALUES (9, '通讯费', 1);
INSERT INTO crm_expense_name VALUES (10, '其他日常费用', 1);
# --------------------------------------------------------

#
# 数据表的结构 `crm_expense_type`
#

CREATE TABLE crm_expense_type (
  ExpenseType tinyint(2) NOT NULL auto_increment,
  TypeName varchar(40) NOT NULL default '',
  PRIMARY KEY  (ExpenseType)
) TYPE=MyISAM;

#
# 导出下面的数据库内容 `crm_expense_type`
#

INSERT INTO crm_expense_type VALUES (1, '日常费用');
INSERT INTO crm_expense_type VALUES (2, '项目费用');
# --------------------------------------------------------

#
# 数据表的结构 `crm_jieduan`
#

CREATE TABLE crm_jieduan (
  JieDuanID int(10) NOT NULL auto_increment,
  JieDuanName varchar(40) NOT NULL default '',
  PRIMARY KEY  (JieDuanID)
) TYPE=MyISAM COMMENT='项目阶段表';

#
# 导出下面的数据库内容 `crm_jieduan`
#

INSERT INTO crm_jieduan VALUES (1, '售前项目');
INSERT INTO crm_jieduan VALUES (2, '售后项目');
INSERT INTO crm_jieduan VALUES (3, '结案成功');
INSERT INTO crm_jieduan VALUES (4, '结案失败');
# --------------------------------------------------------

#
# 数据表的结构 `crm_project`
#

CREATE TABLE crm_project (
  ProjectID int(10) NOT NULL auto_increment,
  AccountID int(10) NOT NULL default '0',
  ContactID int(10) NOT NULL default '0',
  ProjectName varchar(40) NOT NULL default '',
  ProjectSN varchar(40) NOT NULL default '',
  Possibility tinyint(2) NOT NULL default '0',
  ProjectContent text NOT NULL,
  ProjectSale decimal(10,2) NOT NULL default '0.00',
  JieDuan varchar(40) NOT NULL default '',
  ProjectState varchar(40) NOT NULL default '',
  ProjectSource varchar(40) NOT NULL default '',
  BeginTime date default NULL,
  EndTime date default NULL,
  CloseTime datetime default NULL,
  Closed tinyint(1) NOT NULL default '0',
  CloseID varchar(40) NOT NULL default '',
  CloseReason varchar(200) NOT NULL default '',
  Successful varchar(10) NOT NULL default '',
  CreateID varchar(40) NOT NULL default '',
  CreateTime datetime default NULL,
  ModifyID varchar(40) NOT NULL default '',
  ModifyTime datetime default NULL,
  PRIMARY KEY  (ProjectID),
  KEY AccountID (AccountID),
  KEY ContactID (ContactID),
  KEY JieDuan (JieDuan)
) TYPE=MyISAM COMMENT='项目表';

# --------------------------------------------------------

#
# 数据表的结构 `crm_project_state`
#

CREATE TABLE crm_project_state (
  StateID int(10) NOT NULL auto_increment,
  StateName varchar(40) NOT NULL default '',
  PRIMARY KEY  (StateID)
) TYPE=MyISAM COMMENT='项目状态表';

#
# 导出下面的数据库内容 `crm_project_state`
#

INSERT INTO crm_project_state VALUES (1, '未进行');
INSERT INTO crm_project_state VALUES (2, '进行中');
INSERT INTO crm_project_state VALUES (3, '延期进行');
INSERT INTO crm_project_state VALUES (4, '成功关闭');
INSERT INTO crm_project_state VALUES (5, '放弃关闭');
INSERT INTO crm_project_state VALUES (6, '丢失');
INSERT INTO crm_project_state VALUES (7, '其他');
# --------------------------------------------------------

#
# 数据表的结构 `crm_slt_column`
#

CREATE TABLE crm_slt_column (
  UID int(10) NOT NULL default '0',
  TableName varchar(40) NOT NULL default '',
  SelectColumn text NOT NULL,
  KEY SaleID (UID)
) TYPE=MyISAM COMMENT='列选择表';

#
# 导出下面的数据库内容 `crm_slt_column`
#

# --------------------------------------------------------

#
# 数据表的结构 `crm_source`
#

CREATE TABLE crm_source (
  SourceID int(10) NOT NULL auto_increment,
  SourceName varchar(40) NOT NULL default '',
  PRIMARY KEY  (SourceID)
) TYPE=MyISAM COMMENT='客户来源表';

#
# 导出下面的数据库内容 `crm_source`
#

INSERT INTO crm_source VALUES (1, '电话询问');
INSERT INTO crm_source VALUES (2, '网上查询');
INSERT INTO crm_source VALUES (3, '展会');
INSERT INTO crm_source VALUES (4, '老客户推荐');
INSERT INTO crm_source VALUES (5, '主动拓展');
INSERT INTO crm_source VALUES (6, '其他');
# --------------------------------------------------------

#
# 数据表的结构 `crm_tbl_column_explain`
#

CREATE TABLE crm_tbl_column_explain (
  TableName varchar(40) NOT NULL default '',
  ColumnName varchar(60) NOT NULL default '',
  ColumnNameCN varchar(60) NOT NULL default ''
) TYPE=MyISAM COMMENT='数据表表栏目含义';

# --------------------------------------------------------

#
# 数据表的结构 `department`
#

CREATE TABLE department (
  departmentid int(3) NOT NULL auto_increment,
  departmentname varchar(20) NOT NULL default '',
  parent_id int(3) NOT NULL default '0',
  PRIMARY KEY  (departmentid)
) TYPE=MyISAM COMMENT='部门分类表';

#
# 导出下面的数据库内容 `department`
#

INSERT INTO department VALUES (1, '总经理', 0);
INSERT INTO department VALUES (4, '技术部', 2);
INSERT INTO department VALUES (2, '网络部', 1);
INSERT INTO department VALUES (3, '软件部', 1);
INSERT INTO department VALUES (5, '制作部', 2);
INSERT INTO department VALUES (6, '设计部', 2);
INSERT INTO department VALUES (7, '策划部', 2);
INSERT INTO department VALUES (8, '市场部', 2);
INSERT INTO department VALUES (9, '财务部', 1);
INSERT INTO department VALUES (10, '人力资源部', 1);
INSERT INTO department VALUES (11, '客服部', 2);
# --------------------------------------------------------

#
# 数据表的结构 `user`
#

CREATE TABLE user (
  uid int(4) unsigned NOT NULL auto_increment,
  username varchar(20) NOT NULL default '',
  password varchar(20) NOT NULL default '',
  name varchar(20) NOT NULL default '',
  departmentid int(3) NOT NULL default '0',
  privilege int(3) NOT NULL default '0',
  fengji varchar(20) NOT NULL default '',
  mobile varchar(20) NOT NULL default '',
  hometel varchar(20) NOT NULL default '',
  email varchar(200) NOT NULL default '',
  PRIMARY KEY  (uid)
) TYPE=MyISAM;

#
# 导出下面的数据库内容 `user`
#

INSERT INTO user VALUES (3, 'protoss', 'tyiZ.EhZVGH/.', 'protoss', 5, 0, '814', '13621837279', '', '[e-mail]');