?? drp.sql
字號:
/*==============================================================*/
/* Database name: drp */
/* DBMS name: MySQL */
/* Created on: 2007-6-18 */
/*==============================================================*/
drop database if exists drp;
create database drp;
use drp;
drop table if exists t_user;
drop table if exists t_client;
drop table if exists t_temi_client;
drop table if exists t_items;
drop table if exists t_data_dict;
drop table if exists t_fiscal_year_period;
drop table if exists t_flow_card_master;
drop table if exists t_flow_card_detail;
drop view if exists v_aim_client;
/*==============================================================*/
/* Table: t_user */
/*==============================================================*/
create table if not exists t_user
(
user_id varchar(10) primary key not null,
user_name varchar(20) not null,
password varchar(20),
contact_tel varchar(30),
email varchar(30),
create_date datetime
);
/*==============================================================*/
/* Table: t_client */
/*==============================================================*/
create table if not exists t_client
(
id int primary key auto_increment not null,
pid int not null,
name varchar(40) not null,
client_id varchar(10),
client_level char(3),
bank_acct_no varchar(30),
contact_tel varchar(20),
address varchar(50),
zip_code varchar(20),
is_leaf char(1) default 'N',
is_client char(1) default 'N'
);
/*==============================================================*/
/* Table: t_temi_client */
/*==============================================================*/
create table if not exists t_temi_client
(
id int primary key auto_increment not null,
pid int not null,
name varchar(40) not null,
temi_id varchar(20),
temi_lelve char(3),
contact_tel varchar(18),
contactor varchar(30),
address varchar(50),
zip_code varchar(20),
is_leaf char(1) default 'N',
is_temi_client char(1) default 'N'
);
/*==============================================================*/
/* Table: t_items */
/*==============================================================*/
create table if not exists t_items
(
item_no varchar(10) primary key not null,
item_name varchar(30) not null,
spec varchar(30),
pattern varchar(30),
category char(3) not null,
unit char(3) not null
);
/*==============================================================*/
/* Table: t_fiscal_year_period */
/*==============================================================*/
create table if not exists t_fiscal_year_period
(
id int primary key auto_increment not null,
fiscal_year int not null,
fiscal_period tinyint not null,
begin_date datetime not null,
end_date datetime not null,
period_sts char(1) default 'N'
);
/*==============================================================*/
/* Table: t_flow_card_master */
/*==============================================================*/
create table if not exists t_flow_card
(
vou_no varchar(16) primary key not null,
fiscal_year int not null,
fiscal_period tinyint not null,
client_id varchar(10) not null,
opr_type char(1) not null,
record_date datetime not null,
recorder_id varchar(10) not null,
vou_sts char(1) default 'N',
confirmer_id varchar(10),
conf_date datetime,
spotter_id varchar(10),
spot_date datetime,
spot_remark varchar(60),
spot_flag char(1) default 'N',
adjust_time datetime,
adjuster_id varchar(10)
);
/*==============================================================*/
/* Table: t_flow_card_detail */
/*==============================================================*/
create table if not exists t_flow_card_detail
(
id int primary key auto_increment not null,
vou_no varchar(16) not null,
aim_id varchar(10) not null,
item_no varchar(10) not null,
qty decimal(10,2) default 0,
amt decimal(10,2) default 0,
adjust_qty decimal(10,2) default 0,
adjust_reason varchar(50),
adjust_flag char(1) default 'N'
);
/*==============================================================*/
/* Table: flow_card_detail */
/*==============================================================*/
create table if not exists t_data_dict
(
id char(3) primary key not null,
name varchar(20) not null,
category varchar(30) not null
);
/*==============================================================*/
/* View: flow_card_detail */
/*==============================================================*/
create view v_aim_client(id, name, level_id, level_name) as
select a.client_id as id, a.name, a.client_level as type_id, b.name as type_name from t_client a, t_data_dict b where a.client_level=b.id
union
select a.temi_id as id, a.name, a.temi_lelve as type_id, b.name as type_name from t_temi_client a, t_data_dict b where a.temi_lelve=b.id
;
insert into t_client(pid, name, is_leaf, is_client) values (0, '所有分銷商', 'N', 'N');
insert into t_client(pid, name, is_leaf, is_client) values (1, '華北區', 'N', 'N');
insert into t_client(pid, name, is_leaf, is_client) values (2, '北京', 'N', 'N');
insert into t_client(pid, name, client_id, client_level, bank_acct_no, contact_tel, address, zip_code, is_leaf, is_client) values (3, '北京市醫藥股份有限公司', '2001', 'A01', 'bank00001', '1351111111', '北京市', '100000', 'Y', 'Y');
insert into t_client(pid, name, client_id, client_level, bank_acct_no, contact_tel, address, zip_code, is_leaf, is_client) values (3, '北京尚學堂醫藥銷售公司', '3001', 'A03', 'bank00001', '1351111111', '北京市', '100000', 'Y', 'Y');
insert into t_client(pid, name, is_leaf, is_client) values (1, '東北區', 'N', 'N');
insert into t_client(pid, name, is_leaf, is_client) values (5, '吉林省', 'Y', 'N');
insert into t_client(pid, name, is_leaf, is_client) values (5, '遼寧省', 'Y', 'N');
insert into t_client(pid, name, is_leaf, is_client) values (5, '黑龍江省', 'Y', 'N');
insert into t_temi_client(pid, name, is_leaf, is_temi_client) values (0, '所有終端客戶', 'N', 'N');
insert into t_temi_client(pid, name, is_leaf, is_temi_client) values (1, '華北區', 'N', 'N');
insert into t_temi_client(pid, name, is_leaf, is_temi_client) values (2, '北京', 'N', 'N');
insert into t_temi_client(pid, name, temi_id, temi_lelve, contact_tel, contactor, address, zip_code, is_leaf, is_temi_client) values (3, '北京中醫醫院', '5001', 'D01', '1351111111', '張三', '北京市', '100000', 'Y', 'Y');
insert into t_temi_client(pid, name, temi_id, temi_lelve, contact_tel, contactor, address, zip_code, is_leaf, is_temi_client) values (4, '中日醫院', '5002', 'D01', '1351111111', '李四', '北京市', '100000', 'Y', 'Y');
insert into t_data_dict(id, name, category) values('A01', '一級分銷商', 'client_level');
insert into t_data_dict(id, name, category) values('A02', '二級分銷商', 'client_level');
insert into t_data_dict(id, name, category) values('A03', '三級分銷商', 'client_level');
insert into t_data_dict(id, name, category) values('A04', '總部', 'client_level');
insert into t_data_dict(id, name, category) values('B01', '醫療器械', 'item_category');
insert into t_data_dict(id, name, category) values('B02', '中成藥', 'item_category');
insert into t_data_dict(id, name, category) values('B03', '西藥', 'item_category');
insert into t_data_dict(id, name, category) values('C01', '盒', 'item_unit');
insert into t_data_dict(id, name, category) values('C02', '片', 'item_unit');
insert into t_data_dict(id, name, category) values('C03', '箱', 'item_unit');
insert into t_data_dict(id, name, category) values('D01', '甲級醫院', 'temi_client_level');
insert into t_data_dict(id, name, category) values('D02', '乙級醫院', 'temi_client_level');
insert into t_data_dict(id, name, category) values('D03', '丙級醫院', 'temi_client_level');
insert into t_data_dict(id, name, category) values('D04', '藥店', 'temi_client_level');
insert into t_data_dict(id, name, category) values('D05', '其他', 'temi_client_level');
insert into t_user(user_id, user_name, password) values('root', '管理員', 'root123');?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -