sales.sql

通过对现在流行的各种销售管理软件的分析

if exists(select * from sysdatabases where name='SellManage')
drop database SellManage
go
create database SellManage
go
use SellManage
go
create table Admin 					-----用户信息表
(
   
    [Id] int identity(1,1) primary key not null,        ----管理员ID号
    userName varchar(20)not null,			----管理员帐号
    [passWord] varchar(32)not null,			----管理员密码
    type varchar(20) check(type='管理员' or type='普通用户') ,--用户类型
    regDate datetime default getdate()			----注册日期
)
go
--select * from Admin
--drop table Admin
create table Customer                                   --------客户信息表
(
    [Id] int identity(1,1) not null,        		------客户ID号
    customerId varchar(30)  primary key not null,       ------客户编号   
    customerName varchar(50) not null,                  ------客户姓名
    visitedTime datetime default getdate()not null,     ------录入时间
    city varchar(20),                                   ------客户所在城市
    telephone varchar(20),                     		------客户电话号码
    address varchar(255),                               ------客户详细地址
    department varchar(255),                            ------客户所在单位
    remark varchar(255),                                ------客户信息备注
)
go
--select * from Customer
--drop table Customer
create table  Goods                                     -------产品信息表
(
    [Id] int identity(1,1) not null,        		-------产品ID号  
    goodsId varchar(20) primary key not null,           -------产品编号
    goodsName varchar(50) not null,                     -------产品名称
    goodsUnit varchar(25),                              -------单位
    price float not null,                               -------单价
    remain float not null,                              -------库存 
    images varchar(255),                                -------产品缩略图
    [Time] datetime default getdate(),                  -------录入时间
    remark varchar(250),                                -------产品信息备注
)
go
--select * from Goods
--drop table Goods
create table Sale                                     			---------销售信息表
(
    [ID] int identity(1,1) primary key not null,			---------销售ID号
    sale_goodsId varchar(20) references Goods(goodsId) not null,        ---------产品编号
    customerId varchar(30) references Customer(customerId) not null,    ---------客户编号
    title varchar(255),                                 		---------销售简介
    price float not null,                               		---------销售单价
    costPrice  float not null,                          		---------成本单价
    amount int not null,                                		---------销售数量
    salesDate datetime default getdate(),               		---------录入时间 
    remark varchar(250)                                 		---------销售信息备注 
)
go
--销售信息视图
create view saleView
as
select *,(price*amount) as saletotal,(price*amount-costPrice*amount) as profit from Sale 
--select (price*amount) as saletotal,(costPrice*amount) as profit from sale where id=1
go
--select * from Sale
--drop table Sale
--添加客户信息视图
go
create proc addCustomerInfo
@customerID varchar(30),
@customerName varchar(50),
@visistedTime datetime,
@city varchar(20),
@telephone varchar(20),
@address varchar(255),
@department varchar(255),
@remark varchar(255)
as 
insert into Customer values(@customerID,@customerName,@visistedTime,@city,@telephone,@address,@department,@remark)
go
--exec addCustomerInfo '011','guest','2006-09-30','长沙','465463468764','湖南长沙','中国建筑','他该啊啊啊啊阿里山的'
--注册用户视图
create proc adduserInfo
@userName varchar(20),
@password varchar(32),
@type varchar(20),
@regDate datetime
as
insert into Admin values(@userName,@password,@type,@regDate)
go
--exec adduserInfo 'yangzhi','5251314','管理员','2006-09-30'
----------建立名为addGoodsInfo的存储过程，存储产品信息(视图)
create proc addGoodsInfo 
@goodsId varchar(20),
@goodsName varchar(50),
@goodsUnit varchar(25),
@price   float,
@remain  float,
@images  varchar(255),
@time    datetime,
@remark  varchar(250)
as
insert into Goods(goodsId,goodsName,goodsUnit,price,remain,images,Time,remark)  values(@goodsId,@goodsName,@goodsUnit,@price,@remain,@images,@time,@remark)
go
--修改产品信息视图
create proc updateGoodsInfo
@goodsId varchar(20),
@goodsName varchar(50),
@goodsUnit varchar(25),
@price   float,
@remain  float,
@images  varchar(255),
@time    datetime,
@remark  varchar(250)
as
update Goods set goodsName=@goodsName,goodsUnit=@goodsUnit,price=@price,
remain=@remain,images=@images,[time]=@time,remark=@remark where goodsID=@goodsID
go
----exec updateGoodsInfo 'A001','aaa','bbb',10,30,'adasdfasd','2006-09-23','adfasdfasdfasd'
--修改销售信息视图
create proc updateSaleInfo
@ID int,
@sale_goodsId varchar(20),
@customerId varchar(30),
@title varchar(255),
@price float,
@costPrice float,
@amount int,
@salesDate datetime,
@remark varchar(250)
as
update sale set sale_goodsId=@sale_goodsId,customerId=@customerId,title=@title,price=@price,
costPrice=@costPrice,amount=@amount,salesDate=@salesDate,remark=@remark where [ID]=@ID
go
--exec updateSaleInfo 1,'A001','001','金山词霸df',60,50,100,'2006-09-23','adfasdfasdfasd'