c#开发的进销存管理系统

CREATE TABLE [dbo].[商品清单] ( [货号] [char] (14) NOT NULL Primary Key, [条码] [char] (14) NULL , [拼音编码] [char] (40) NULL , [品名] [varchar] (80) NULL , [规格] [varchar] (40) NULL , [单位] [char] (6) NOT NULL , [产地] [varchar] (50) NULL , [类别] [char] (20) NULL , [进货价] [decimal] (28,6) NULL default(0), [销售价1] [decimal] (28,6) NULL default(0), [销售价2] [decimal] (28,6) NULL default(0), [最低售价] [decimal] (28,6) NULL default(0) ) GO CREATE TABLE [dbo].[供货商清单] ( [供货商号] [char] (10) NOT NULL Primary Key, [拼音编码] [char] (40) NOT NULL , [简称] [varchar] (80) NULL , [名称] [varchar] (80) NULL , [地址] [varchar] (80) NULL , [邮编] [char] (6) NULL , [区号] [char] (6) NULL , [地区] [varchar] (12) NULL , [类型] [char] (10) NULL , [电话] [varchar] (20) NULL , [传真] [varchar] (20) NULL , [电报] [varchar] (20) NULL , [开户行] [varchar] (40) NULL , [开户行邮编] [char] (6) NULL , [银行帐号] [varchar] (20) NULL , [税号] [varchar] (20) NULL , [库房地址] [varchar] (40) NULL , [库房电话] [varchar] (20) NULL , [业务员] [char] (10) NULL , [业务部门] [varchar] (20) NULL , ) GO CREATE TABLE [dbo].[客户清单] ( [客户编号] [char] (10) NOT NULL Primary Key, [拼音编码] [char] (20) NOT NULL , [简称] [varchar] (80) NULL , [名称] [varchar] (80) NULL , [联系人] [varchar] (30) NULL , [地址] [varchar] (80) NULL , [邮编] [char] (6) NULL , [区号] [char] (6) NULL , [地区] [varchar] (12) NULL , [电话] [varchar] (20) NULL , [传真] [varchar] (20) NULL , [电报] [varchar] (20) NULL , [开户行] [varchar] (40) NULL , [开户行邮编] [char] (6) NULL , [银行帐号] [varchar] (20) NULL , [税号] [varchar] (20) NULL , [性质] [varchar] (10) NULL , [业务员] [char] (10) NULL , [业务部门] [varchar] (20) NULL , [授信额度] [decimal] (28,6) NULL ) GO CREATE TABLE [dbo].[进货单] ( [编号] [char] (14) Not NULL Primary key, [供货商号] [char] (10) NOT NULL , [进货日期] [datetime] NULL, [业务员] [char] (10) NULL , [制单人] [char] (10) NULL , [验收员] [char] (10) NULL , [保管员] [char] (10) NULL , [税价合计] [decimal] (28,6) NULL , [不含税价] [decimal] (28,6) NULL , [税额] [decimal] (28,6) NULL, [订单号] [char] (14) NULL ) GO CREATE TABLE [dbo].[进货单明细] ( [编号] [char] (14) Not NULL Primary key, [进货单号] [char] (14) Not NULL , [货号] [char] (14) NOT NULL , [进货数量] [decimal] (28,6) NOT NULL , [进价] [decimal] (28,6) NULL , [税价合计] [decimal] (28,6) NULL , [扣率] [decimal] (28,6) NULL , [税率] [decimal] (28,6) NULL , [不含税价] [decimal] (28,6) NULL , [税额] [decimal] (28,6) NULL , [仓库] [char] (20) NULL , [货物质量] [varchar] (50) NULL ) GO CREATE TABLE [dbo].[销售单] ( [编号] [char] (14) Not NULL Primary key, [客户编号] [char] (10) NOT NULL , [销售日期] [datetime] NULL, [业务员] [char] (10) NULL , [制单人] [char] (10) NULL , [保管员] [char] (10) NULL , [税价合计] [decimal] (28,6) NULL , [不含税价] [decimal] (28,6) NULL , [税额] [decimal] (28,6) NULL, [订单号] [char] (14) Not NULL ) GO CREATE TABLE [dbo].[销售单明细] ( [编号] [char] (14) Not NULL Primary key, [销售单号] [char] (14) Not NULL , [货号] [char] (14) NOT NULL , [销售数量] [decimal] (28,6) NOT NULL , [销售价] [decimal] (28,6) NULL , [税价合计] [decimal] (28,6) NULL , [扣率] [decimal] (28,6) NULL , [税率] [decimal] (28,6) NULL , [不含税价] [decimal] (28,6) NULL , [税额] [decimal] (28,6) NULL , [仓库] [char] (20) NULL ) GO CREATE TABLE [dbo].[库存库] ( [货号] [char] (14) NOT NULL , [仓库] [varchar] (20) NOT NULL , [库存数量] [decimal] (28,6) NOT NULL , [库存金额] [decimal] (28,6) NOT NULL , [库存单价] [decimal] (28,6) NOT NULL , [最新进价] [decimal] (28,6) NULL ) GO CREATE TABLE [dbo].[权限清单] ( [权限序号] [int] IDENTITY (1, 1) NOT NULL , [用户编号] [char] (6) NULL , [部门] [char] (20) NULL , [权限名称] [char] (30) NOT NULL ) GO ALTER TABLE [dbo].[进货单] ADD CONSTRAINT [进货单_供货商_fk] FOREIGN KEY ( [供货商号] ) REFERENCES [dbo].[供货商清单] ( [供货商号] ) GO ALTER TABLE [dbo].[进货单明细] ADD CONSTRAINT [FK_进货单明细_进货单] FOREIGN KEY ( [进货单号] ) REFERENCES [dbo].[进货单] ( [编号] ), CONSTRAINT [进货单明细_货号_fk] FOREIGN KEY ( [货号] ) REFERENCES [dbo].[商品清单] ( [货号] ) GO ALTER TABLE [dbo].[销售单] ADD CONSTRAINT [销售单_客户编号_fk] FOREIGN KEY ( [客户编号] ) REFERENCES [dbo].[客户清单] ( [客户编号] ) GO ALTER TABLE [dbo].[销售单明细] ADD CONSTRAINT [FK_销售单明细_销售单] FOREIGN KEY ( [销售单号] ) REFERENCES [dbo].[销售单] ( [编号] ), CONSTRAINT [销售单明细_货号_fk] FOREIGN KEY ( [货号] ) REFERENCES [dbo].[商品清单] ( [货号] ) GO CREATE PROCEDURE sf_进货单 AS begin transaction -- 库存库中没有,增加记录 INSERT INTO 库存库(货号,仓库,库存数量,库存金额,库存单价) SELECT DISTINCT j.货号, j.仓库, 0,0,0 FROM 进货单明细 AS J left join 库存库 as k on ( j.仓库=k.仓库 and j.货号=k.货号 ) where k.货号 is null -- 修改库存信息 UPDATE 库存库 SET 库存单价=case when 库存数量<=0 or (库存数量+数量ALL)<=0 then 进价 else (库存金额+税价合计ALL)/(库存数量+数量ALL) end , 库存数量=库存数量+数量ALL, 库存金额=case when 库存数量<=0 or (库存数量+数量ALL)<=0 then 进价*(库存数量+数量ALL) else (库存金额+税价合计ALL) end , 最新进价=进价 FROM (SELECT 仓库,货号,'数量ALL'=sum(进货数量), '进价' = sum(税价合计)/sum(进货数量), '税价合计ALL'=sum(税价合计) FROM 进货单明细 GROUP BY 仓库,货号) AS LSJ WHERE 库存库.仓库=LSj.仓库 AND 库存库.货号=LSj.货号 -- 加入应付款 INSERT INTO 应付款(编号, 进货单号, 货号, 供货商号, 数量, 进货单价, 金额, 进货日期, 状态) SELECT '付'+a.编号,b.编号,a.货号,b.供货商号,进货数量,进价,a.税价合计,进货日期,'应付' FROM 进货单明细 as a, 进货单 as b where a.进货单号=b.编号 -- 加入历史 insert into 进货单历史 select * from 进货单 insert into 进货单明细历史 select * from 进货单明细 -- 清除进货单 delete from 进货单明细 delete from 进货单 commit go CREATE PROCEDURE sf_销售单 AS begin transaction -- 修改库存信息 UPDATE 库存库 SET 库存数量=库存数量-数量ALL, 库存金额=库存单价*(库存数量-数量ALL) FROM (SELECT 仓库,货号,'数量ALL'=sum(销售数量) FROM 销售单明细 GROUP BY 仓库,货号) AS LSJ WHERE 库存库.仓库=LSj.仓库 AND 库存库.货号=LSj.货号 -- 加入应收款 INSERT INTO 应收款(编号, 销售单号, 货号, 客户编号, 数量, 销售价, 金额, 销售日期, 状态) SELECT '收'+a.编号,b.编号,a.货号,b.客户编号,销售数量,销售价,a.税价合计,销售日期,'应收' FROM 销售单明细 as a, 销售单 as b where a.销售单号=b.编号 -- 加入历史 insert into 销售单历史 select * from 销售单 insert into 销售单明细历史 select * from 销售单明细 -- 清除销售单 delete from 销售单明细 delete from 销售单 commit go