dbllxt.sql

小型企业物品管理系统,DELPHI源码+ACCESS数据库

use master
go
--如果存在数据库 dbllxt，则删除之
if exists(select name from sysdatabases where name = 'dbllxt')
drop database dbllxt
go
--新建数据库 dbllxt
--create database dbllxt
--go
CREATE DATABASE [dbllxt]  ON (NAME = 'dbllxt', FILENAME ='d:\领料数据库\dbllxt.mdf' , SIZE = 10, FILEGROWTH = 10%) LOG ON (NAME = 'dbllxt_Log', FILENAME = 'd:\领料数据库\dbllxt_log.ldf' , SIZE = 1, FILEGROWTH = 10%)
GO
--转到当前数据库操作
use dbllxt
go
--系统信息
create table tbSysInfo(
	siCorp		varchar(64) default('成都彭山雅俊人工作室'),--司名称
	siZip		varchar(16) default('610063'),--邮编
	siAddr		varchar(64) default('成都市一环路北三段雅图丽景13#-03'),--地址
	siTel		varchar(64) default('028-86276960'),--联系电话
	siFax		varchar(64) default('028-86276951'),--传真
	siMan		varchar(64) default('彭攀'),--法人代表
	siMail		varchar(64) default('[e-mail]'),--邮件
	siURL		varchar(64) default('http://www.***.com'),--网址
	siSN		varchar(1024) default('pplovejx'),--序列号
)
go
--测试数据
insert into tbSysInfo(siSN) values('')
----部门表
create table tbdep(
	deID		int identity(1,1) primary key,--自动编号
	dename		varchar(32) unique not null,--部门名称
	dememo		varchar(255),--部门备注
)
go
insert into tbdep(dename,dememo)values('业务部门','主管业务')
insert into tbdep(dename,dememo)values('会员部门','客户服务')
insert into tbdep(dename,dememo)values('交易部','公司交易方面的')
insert into tbdep(dename,dememo)values('财务部门','公司财务方面的')
insert into tbdep(dename,dememo)values('法律部','公司法律支持')
go
create table tbybqh(
	yqid		int identity(1,1) primary key,
	yqsf		varchar(20) not null,--省份
	yqdq		varchar(20) not null,--地区
	yqyb		varchar(20),-- 邮编
	yqqh		varchar(20),--区号
)
go

create proc spinyq(
	@yqsf		varchar(20),
	@yqdq		varchar(20),
	@yqyb		varchar(20),
	@yqqh		varchar(20),
	@msg		int output
)
as
if exists(select * from tbybqh where yqdq=@yqdq)
select @msg=1
else
begin
insert into tbybqh(yqsf,yqdq,yqyb,yqqh)values(@yqsf,@yqdq,@yqyb,@yqqh)
select @msg=2
end
go
	
----员工表
create table tbemp(
	emid		int identity(1,1) primary key,---自动编号
	emname		varchar(50) not null,--员工名称
	empass		varchar(50) default('abc123'),--员工密码
	emlogin		bit default(0),--1表是已经登陆0表示未登陆是否登录
	emsex		bit default(1),--员工性别
	deid		int references tbdep(deid),--所属部门
	emmemo		varchar(255) default(''),--备注信息
)
go	
insert into tbemp(emname,empass,emsex,deid,emmemo)values('蒋萱','jx821020abc',0,1,'系统管理员')
go
--员工试图

create view view_empdep
as
select em.*,dep.dename from tbemp em
	join tbdep dep on em.deid=dep.deid
go

----类型表
create table tbclass(
	clid		int identity(1,1) primary key,
	clname		varchar(50) unique not null,--类型名称
	clmemo		varchar(255) default('')--备注信息
)
go
insert into tbclass(clname)values('打印纸')
insert into tbclass(clname)values('文具')
go
------商品表
create table tbwpb(
	bwid		int identity(1,1) primary key,
	bwname		varchar(50) unique not null,--商品名称
	clid		int references tbclass(clid),--所属类型
	bwunit		varchar(20),--单位
	bwprice		money,--单价
	bwmemo		varchar(255) default('')--备注信息
)
go
insert into tbwpb(bwname,clid,bwunit,bwprice)values('文件夹',2,'个',10)
insert into tbwpb(bwname,clid,bwunit,bwprice)values('A4纸',2,'箱',30)
---入库明晰表
create table tbGoodIn(
	byid		int identity(1,1) primary key,
	bwid		int references tbwpb(bwid),---对应的商品编号
	byquanty	int,--入库数量
	bytotal		money,--总价
	byrkdate	datetime default(getdate()),--入库时间
	rkid		int references tbemp(emid),--入库员
	bymemo		varchar(255) default(''),--备注信息
)
go
----入库数量统计(物品信息统计)
create view veiw_rktj
as
select bwid,sum(byquanty) as rkzs,count(*) as numcount from tbgoodin
group by bwid
go

------入库数量统计详细信息
create view view_rktjxx
as
select wpb.bwid,wpb.bwname,wpb.clid,wpb.bwunit,wpb.bwprice,cl.clname,vi.rkzs,vi.rkzs*wpb.bwprice as total,vi.numcount from tbwpb wpb
	join veiw_rktj vi on wpb.bwid=vi.bwid
	join tbclass cl on wpb.clid=cl.clid
go

---入库明细存储过程

create proc spingoodin(
	@bwid		int,
	@byquanty	int,--购买数量
	@rkid		int,---入库人员ID
	@bymemo		varchar(255),--入库备注信息
	@msg		varchar(255) output
)
as
declare
	@bwprice	money
select @bwprice=bwprice from tbwpb where bwid=@bwid
insert into tbGoodIn (bwid,byquanty,rkid,bymemo,bytotal)values
(@bwid,@byquanty,@rkid,@bymemo,@bwprice*@byquanty)
select @msg='物品入库成功！'
go
-------------入库明细修改存储过程
create proc spupgoodin(
	@byid		int,
	@bwid		int,
	@byquanty	int,--购买数
	@bymemo		varchar(255),--入库备注信息
	@msg		varchar(255) output
)
as
declare
	@bwprice	money,
	@sequanty	int---出库数量
select @sequanty=sequanty from tbsell where bwid=@bwid
if @sequanty>@byquanty 
select @msg='修改失败！该物品的出库数量大于入库数量！'
else begin
select @bwprice=bwprice from tbwpb where bwid=@bwid
update tbgoodin set bwid=@bwid,byquanty=@byquanty,bymemo=@bymemo,bytotal=@byquanty*@bwprice where byid=@byid 
select @msg='修改成功！'
end
go
---入库视图bwunitbwprice
create view view_gooin
as
select wpb.bwname,wpb.clid,wpb.bwunit,wpb.bwprice,goin.*,emp.emname rkname,cl.clname from tbgoodin goin
	join tbwpb wpb on goin.bwid=wpb.bwid
	join tbclass cl on wpb.clid=cl.clid
	join tbemp emp on goin.rkid=emp.emid
go

--商品表视图
create view view_wpb
as
select wpb.*,cl.clname from tbwpb wpb
	join tbclass cl on wpb.clid=cl.clid
go
----分页存储过程
create proc returpage(
	@sql		varchar(50),
	@findzd		varchar(20),--需要查询的字段
	@findstr	varchar(20),
	@pagesize	int,
	@sort		varchar(20),
	@page		int,--当前的总页数
	@zid		varchar(20),--主键
	@sqlstr		nvarchar(1000) output--返回的SQL的语句
)
as
if @pagesize=0 
set @pagesize=35
if @findzd<>''
	set @sqlstr='SELECT   TOP  '+CAST(@pagesize AS VARCHAR(20))+' *  FROM '+@SQL+'  T WHERE T.'+@zID+' not in 
	(SELECT TOP '+CAST((@pagesize*(@Page-1)) AS VARCHAR(20))+' '+@zID+' FROM '+@SQL+' 
	T where t.'+@findzd+' like '''+@findstr+''' ORDER BY '+@Sort+') 
	 and t.'+@findzd+' like '''+@findstr+''' ORDER BY '+@Sort
else if @findzd=''
	set @sqlstr='SELECT   TOP  '+CAST(@pagesize AS VARCHAR(20))+' *  FROM '+@SQL+'  T WHERE T.'+@zID+' not in 
	(SELECT TOP '+CAST((@pagesize*(@Page-1)) AS VARCHAR(20))+' '+@zID+' FROM '+@SQL+' T) ORDER BY '+@Sort
go
----返回符合条件的数量的综合
create proc returncount(
	@findzd		varchar(20),--查询的字段
	@findstr	varchar(100),--查询的值
	@sql		varchar(100)--表的名称
)
as
declare  @strSQL   varchar(1000)
if @findzd='' and @findstr=''
set @strsql='select count(*) from '+@sql
else
set @strsql='select count(*) from '+@sql+' where '+@findzd+' like '''+@findstr+'%'''
exec(@strsql)
go
---返回页码
create proc retuyema(
	@count		int,
	@pagesize	int,--每页的条数
	@page		int output--总的页码
)
as
if @pagesize=0 
set @pagesize=35
if @count%@pagesize=0
set @page=@count/@pagesize
else
set @page=@count/@pagesize+1
go

---领料表
create table tbsell(
	seid		int identity(1,1) primary key,
	bwid		int references tbwpb(bwid),--名称所对应的ID
	sedate		datetime default(getdate()),--领料时间
	czid		int references tbemp(emid),--操作员
	llid		int references tbemp(emid),--领料员
	sequanty	int,--领料数量
	setotal		money,--领料总额
	sememo		varchar(255) default(''),---备注信息
)
go

---出库总数
create view veiw_cktj
as
select bwid,sum(sequanty) as ckzs,count(*) as numcount from tbsell
group by bwid
go

------出库数量统计详细信息
create view view_cktjxx
as
select wpb.bwid,wpb.bwname,wpb.clid,wpb.bwunit,wpb.bwprice,cl.clname,vi.ckzs,vi.ckzs*wpb.bwprice as total,vi.numcount from tbwpb wpb
	join veiw_cktj vi on wpb.bwid=vi.bwid
	join tbclass cl on wpb.clid=cl.clid
go

----物品库存数量(即当前出库的数量一定要小于等于当前的最大库存)
create proc spwpkcsl(
	@bwid		int,
	@kcsl		int output
)
as
declare @pdrksl 	int, 
	@pdcksl		int,
	@rksl           int,
	@cksl	 	int
set @pdrksl=(select count(*) from veiw_rktj where bwid=@bwid)
set @pdcksl=(select count(*) from veiw_cktj where bwid=@bwid)
if @pdrksl=0
select @kcsl=0
else if @pdrksl>0 and @pdcksl=0
select @kcsl=rkzs from veiw_rktj where bwid=@bwid
else if @pdrksl>0 and @pdcksl>0 begin
select @rksl=rkzs from veiw_rktj where bwid=@bwid
select @cksl=ckzs from veiw_cktj where bwid=@bwid
select @kcsl=@rksl-@cksl
end
go

--领料视图
create view view_sell
as
select wpb.bwname,wpb.bwunit,wpb.bwprice,wpb.clid,sell.*,
emc.emname czname,eml.emname llname,eml.deid,cl.clname,dep.dename from tbsell sell
	join tbwpb wpb on sell.bwid=wpb.bwid
	join tbemp emc on sell.czid=emc.emid
	join tbemp eml on sell.llid=eml.emid
	join tbclass cl on wpb.clid=cl.clid
	join tbdep dep on eml.deid=dep.deid
go



--修改领料存储过程
create proc spupsell(
	@seid		int,
	@bwid		int,
	@llid		int,
	@sequanty	int,
	@sememo		varchar(255),
	@msg		varchar(255) output
)
as
declare
	@bwprice	money
select @bwprice=bwprice from tbwpb where bwid=@bwid
update tbsell set bwid=@bwid,llid=@llid,sequanty=@sequanty,sememo=@sememo,setotal=@bwprice*@sequanty where seid=@seid
select @msg='修改成功!'
go
---新增领料存储过程
create proc spinsell(
	@bwid		int,
	@czid		int,
	@llid		int,
	@sequanty	int,
	@sememo		varchar(255),
	@msg		varchar(255) output
)
as
declare
	@bwprice	money
select @bwprice=bwprice from tbwpb where bwid=@bwid
insert into tbsell (bwid,czid,llid,sequanty,sememo,setotal)values(@bwid,@czid,@llid,@sequanty,@sememo,@bwprice*@sequanty)
select @msg='新增成功！'
go
----库存统计
create view view_kc
as
select wpb.bwid,rktj.rkzs-isnull(cktj.ckzs,0) kczs from tbwpb wpb
	join veiw_rktj rktj on wpb.bwid=rktj.bwid
	left outer join veiw_cktj cktj on rktj.bwid=cktj.bwid
go
----库存信息视图
create view view_kctj
as
select sum(godin.byquanty)-sum(sell.sequanty) kczs from tbgoodin godin
	join tbsell sell on godin.bwid=sell.bwid
go
--库存详细信息
create view view_kcxx
as
select wpb.bwid,rktj.rkzs-isnull(cktj.ckzs,0) kczs,wpb.bwname,wpb.bwprice,wpb.bwunit,wpb.clid,cl.clname,(rktj.rkzs-isnull(cktj.ckzs,0))*wpb.bwprice total from tbwpb wpb
	join veiw_rktj rktj on wpb.bwid=rktj.bwid
	join tbclass cl on wpb.clid=cl.clid
	left outer join veiw_cktj cktj on rktj.bwid=cktj.bwid
go

---系统日志表
create table tbsystem(
	syid		int identity(1,1) primary key,
	syname		varchar(100) not null,--名称
	syoption	varchar(255),--操作详细信息
	sydate		datetime default(getdate()),--时间
	symemo		varchar(255),--备注信息
)
go
---系统设置
create table tbeabsh(
	eaid		int identity(1,1) primary key,
	bwid		int references tbwpb(bwid) unique not null,--物品名称
	eaAlert		int,--物品数量库存小于多少发出警报
	eabit		bit default(0),--1为启用0为禁用
	eamemo		varchar(255),--备注信息
)
go
-----系统设置视图
create view view_eabsh
as
select eab.*,bw.bwname,bw.clid,cl.clname from tbeabsh eab
	join tbwpb bw on eab.bwid=bw.bwid
	join tbclass cl on bw.clid=cl.clid
go

--触发器 判断是否是当前库存数量是否小于用户设定的值
create trigger triggdin
on tbgoodin
after insert,update
as
declare @count 		int,--当前库存的数量
	@kccount 	int,--用户设置的最低数量
	@wpname		varchar(100),--物品名称
	@bwid		int--物品编号
select @count=kc.kczs,@kccount=eab.eaalert,@bwid=eab.bwid from view_kc kc,tbeabsh eab where kc.bwid=eab.bwid
if @count<=@kccount
begin
select @wpname=bwname from tbwpb where bwid=@bwid
--raiserror('该物品当前的库存小于或等于您设置的库存最低数量！',16,1,@wpname)
update tbeabsh set eabit=1 where bwid=@bwid
end
else 
return
go

----删除入库明细信息
create proc spdelgoodin(
	@byid		int,
	@msg		varchar(255) output
)
as
declare		@bwid		int--物品类型
select @bwid=bwid  from tbgoodin where byid=@byid
if exists(select * from tbsell where bwid=@bwid)
select @msg='该物品已经有出库记录了！不允许删除！'
else begin
delete tbgoodin where byid=@byid
select @msg='删除成功！'
end
go

create  trigger trigsell
on tbsell
after insert,update
as
declare @count 		int,--当前库存的数量
	@kccount 	int,--用户设置的最低数量
	@wpname		varchar(100),--物品名称
	@bwid		int--物品编号
select @count=kc.kczs,@kccount=eab.eaalert,@bwid=eab.bwid from view_kc kc,tbeabsh eab where kc.bwid=eab.bwid
if @count<=@kccount
begin
select @wpname=bwname from tbwpb where bwid=@bwid
update tbeabsh set eabit=1 where bwid=@bwid
end
else
return
go
----触发器表（tbeabsh）修改新增
create trigger trigeabsh
on tbeabsh
after insert,update
as
declare @count 		int,--当前库存的数量
	@kccount 	int,--用户设置的最低数量
	@wpname		varchar(100),--物品名称
	@bwid		int--物品编号
select @count=kc.kczs,@kccount=eab.eaalert,@bwid=eab.bwid from view_kc kc,tbeabsh eab where kc.bwid=eab.bwid
if @count<=@kccount
begin
select @wpname=bwname from tbwpb where bwid=@bwid
update tbeabsh set eabit=1 where bwid=@bwid
end
else
return
go
-----信息初始化所有
create proc spcsh(
	@msg	varchar(255) output
)
as
delete tbgoodin
delete tbsell
delete tbeabsh
delete tbwpb
delete tbclass
select @msg='系统数据初始化完成！'
go


--基本表定义完