market_createnew.sql

提供商业企业进销存、成本核算、销售人员工资考核结算、地区管理报表（特别是二维报表）

use  master
go
 if  exists(select  name  from  master.dbo.sysdatabases  where  name=N'market')
   drop   database[market]
go
create  database[market]
go
use  market
create table supply_dict
(
    supply_code       varchar(10)              not null,
    input_code        varchar(20)              not null,
    supply_nm         varchar(40),
    supply_name       varchar(40), 
    address           varchar(80),             
    post              varchar(6),
    section_code      varchar(6), 
    section           varchar(12),  
    type              varchar(10), 
    telephone         varchar(20),  
    fax               varchar(20),
    telegraph         varchar(20), 
    bankname          varchar(40),  
    bankpost          varchar(6), 
    bankno            varchar(20), 
    taxno             varchar(20),
    storeaddress      varchar(40),
    storetele         varchar(20), 
    employee          varchar(10),
    department        varchar(20),
    constraint  pk_supply  primary key (supply_code));

create table customer_dict
(
    customer_code     varchar(10)              not null,
    input_code        varchar(20)              not null,
    customer_nm       varchar(40),
    customer_name     varchar(40), 
    relation          varchar(30),
    address           varchar(80),             
    post              varchar(6),
    section_code      varchar(6), 
    section           varchar(12),   
    telephone         varchar(20),  
    fax               varchar(20),
    telegraph         varchar(20), 
    bankname          varchar(40),  
    bankpost          varchar(6), 
    bankno            varchar(20), 
    taxno             varchar(20),
    type              varchar(10),
    employee          varchar(10),
    department        varchar(20),
    authorize         numeric(15,2),
    constraint  pk_customer  primary key (customer_code));

create table goods_dict
(
    goods_code           varchar(9)               not null,
    goods_name           varchar(2)               not null,
    goods_id             varchar(14),
    input_code           varchar(8),
    trademark            varchar(3),
    class                varchar(2),
    type                 varchar(2),
    unit                 varchar(6),
    pricelast            numeric(15,2),
    proportion           numeric(15,2),
    slproportion         numeric(15,2),
    state                varchar(3),
    remarks              varchar(30),
    constraint pk_good  primary key (goods_code));


create table name_type
(
    name_code               varchar(2)             not null,
    name_type               varchar(20)            not null,
    constraint  pk_name   primary key (name_code));

create table trademark_type
(
    trademark_code               varchar(3)             not null,
    trademark_type               varchar(20)            not null,
    constraint  pk_trademark   primary key (trademark_code));

create table class_type
(
    class_code               varchar(2)             not null,
    class_type               varchar(20)            not null,
    constraint  pk_class   primary key (class_code));

create table brand_type
(
    brand_code               varchar(2)             not null,
    brand_type               varchar(20)            not null,
    constraint  pk_brand   primary key (brand_code));

create table cash_dict
(
    cash_code               varchar(2)             not null,
    cash_name               varchar(8)             not null,
    constraint  pk_cash   primary key (cash_code));

create table operator_dict
(
    operator_code  varchar(6)              not null,
    operator_name  varchar(60)             not null,
    depart         varchar(8) ,
    operator_right  varchar(1)           ,
    operator_pwd    varchar(8)           ,
    constraint pk_operator_dict primary key (operator_code));

create table resource_dict
(
    resource_code               varchar(2)             not null,
    resource_name               varchar(20)             not null,
    remarks                     varchar(30)  ,
    constraint  pk_resource     primary key (resource_code));

create table purpose_dict
(
    purpose_code               varchar(2)             not null,
    purpose_name               varchar(20)             not null,
    remarks                     varchar(30)  ,
    constraint  pk_purpose     primary key (purpose_code));

create table  employee_dict
(
    employee_code      varchar(3)                     not null,
    employee_name      varchar(10),
    sex                varchar(2),
    telephone          varchar(12),
    mobile             varchar(14),
    address            varchar(40),
    idcard             varchar(17),
    bankid             varchar(19),
    basesalary         numeric(15,2),
    birthday           datetime,
    constraint  pk_employee   primary key (employee_code));

create  table  stock_dict
(
    stock_code         varchar(2)       not null ,
    stock_name         varchar(20)      not null ,
    type               varchar(10) ,
    remark             varchar(30),
    constraint pk_stock primary key (stock_code));


create table  acct_dict
(
    accper             tinyint                    not null,
    begindt            datetime,
    enddt              datetime,
    proflg             varchar(3),
    endflg             varchar(3),
    uyear              varchar(4),
    constraint  pk_accper   primary key (accper));

create  table  inventory
(
    goods_code       varchar(9)       not null,
    stock_code       varchar(2)       not null ,
    acct             tinyint       not null,
    bgnqty           numeric(15,2) ,
    bgncost          numeric(15,2),
    inqty            numeric(15,2),
    incost           numeric(15,2),
    outqty           numeric(15,2),
    outcost          numeric(15,2),
    edqty            numeric(15,2),
    edcost           numeric(15,2),
    saleqty          numeric(15,2),
    sale             numeric(15,2),
    saleprice        numeric(15,2),
    avgprice         numeric(15,2),
    constraint pk_inventory primary key (goods_code,stock_code,acct));

create  table  bill_mst
(
    bill_date        datetime         not null,
    bill_no          varchar(11)      not null ,
    bill_rp          varchar(2)       not null,
    type             varchar(2)       not null,
    stock_code       varchar(2)       not null,
    stock_out        varchar(2),
    unit_code        varchar(10) ,
    unit_name        varchar(40),
    operator         varchar(6),
    employee_code    varchar(3),
    employee         varchar(10),
    remark           varchar(30),
    charge_indicator tinyint,
    constraint pk_bill_mst primary key (bill_no));



create  table  bill_det
(
    id               tinyint       not null,
    bill_no          varchar(11)      not null,
    goods_code       varchar(9)       not null,
    goods_name       varchar(20) ,
    trademark        varchar(20) ,
    class            varchar(20),
    type             varchar(20),
    unit             varchar(6),
    qty              numeric(15,2),
    price            numeric(15,2),
    tax              numeric(15,2),
    totaltax         numeric(15,2),
    total            numeric(15,2),
    cost             numeric(15,2),