/*==============================================================*/
/* 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, '華北區(qū)', '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, '北京市醫(yī)藥股份有限公司', '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, '北京尚學(xué)堂醫(yī)藥銷售公司', '3001', 'A03', 'bank00001', '1351111111', '北京市', '100000', 'Y', 'Y');
insert into t_client(pid, name, is_leaf, is_client) values (1, '東北區(qū)', '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, '華北區(qū)', '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, '北京中醫(yī)醫(yī)院', '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, '中日醫(yī)院', '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', '醫(yī)療器械', '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', '甲級醫(yī)院', 'temi_client_level');
insert into t_data_dict(id, name, category) values('D02', '乙級醫(yī)院', 'temi_client_level');
insert into t_data_dict(id, name, category) values('D03', '丙級醫(yī)院', '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');