drp.rar_drp-ce5

/*==============================================================*/ /* 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_