连锁药房药品进销存管理系统

连锁药房药品进销存管理系统内容摘要：

销售日期 medicalid bigint 药品 id tcount int 销售数量 opuserid int 操作员 id zongjia money 总价 recipeid Varchar(50) 处方编号 个人设置 个人设置包括个人资料修改和密码修改。 个人资料包括管理员的姓名、姓 名、密码保护（密码问题和问题答案）、联系电话和 Email，个人资料修改就可以对这些资料进行修改。 密码修改是对管理员登入密码进行修改。 表 7 用户信息表 (users) 字段名 字段类型 说明 userid int 用户唯一 id，自动增量 username Varchar(50) 仓库名称 password Varchar(50) 仓库所在地址 truename Varchar(50) 仓库描述 sex Bit 性别 1表示男， 0表示女 question Varchar(50) 密码问题 answer Varchar(50) 问题答案 Varchar(50) phone Varchar(50) 电话 usertype int 用户类型，暂时未使用该字段 存储过程说明 (1)用户信息修改 CREATE PROCEDURE ( 连锁药房药品进销存管理系统 第 18 页 (共 56 页 ) @userid [int], @truename [varchar](50), @sex [bit], @question [varchar](50), @answer [varchar](50), @ [varchar](50), @phone [varchar](50) ) AS update [dbo].[users] set [truename]=@truename,[sex]=@sex, [question]=@question, [answer]=@answer, []=@, [phone]=@phone where ([userid]=@userid) (2)仓库选择 CREATE PROCEDURE (@storeid [int]) AS select * from [dbo].[stores] where (storeid=@storeid) (3)仓库修改 CREATE PROCEDURE ( @storeid [int], @taddr [varchar](200), @tdesc [varchar](200) ) 系统设计过程论述 第 19 页 (共 56 页 ) AS update [dbo].[stores] set [address]=@taddr,[description]=@tdesc where (storeid=@storeid) (4)仓库删除 CREATE PROCEDURE (@storeid [int]) AS delete from [dbo].[stores] where ([storeid]=@storeid) (5)添加新仓库 CREATE PROCEDURE ( @tname [varchar](200), @taddr [varchar](200), @tdesc [varchar](200) ) AS Insert into [dbo].[stores] ([name],[address],[description]) Values (@tname,@taddr,@tdesc) (6)删除销售记录 CREATE PROCEDURE ( @uid [int] ) 连锁药房药品进销存管理系统 第 20 页 (共 56 页 ) AS delete from [dbo].[sales] where ([uid]=@uid) (7)添加销售记录 CREATE PROCEDURE ( @medid [bigint], @tcount [int], @zongjia [money], @opuserid [int], @recipeid [varchar](50) ) AS insert into [dbo].[sales] ([medicalid],[tcount],[zongjia],[opuserid],[recipeid]) values ( @medid,@tcount,@zongjia,@opuserid,@recipeid) (8)获取存储规则 CREATE PROCEDURE ( @uid [bigint] ) AS select [dbo].[stores].[name] as storename, [dbo].[medical].[ame] as medname, [dbo].[storerule].[topcount], [dbo].[storerule].[basecount] 系统设计过程论述 第 21 页 (共 56 页 ) from [dbo].[stores],[dbo].[medical],[dbo].[storerule] where ( [dbo].[storerule].[uid]=@uid and [dbo].[storerule].[storeid]=[dbo].[stores].[storeid] and [dbo].[storerule].[medicalid]=[dbo].[medical].[medicalid] ) (9)修改存储规则 CREATE PROCEDURE ( @uid [bigint], @topcount [int], @basecount [int] ) AS update [dbo].[storerule] set [topcount]=@topcount,[basecount]=@basecount where ([uid]=@uid) (10)删除存储规则 CREATE PROCEDURE (@uid [bigint]) AS delete from [dbo].[storerule] where ([uid]=@uid) (11)添加存储规则 CREATE PROCEDURE ( @medicalid [bigint], 连锁药房药品进销存管理系统 第 22 页 (共 56 页 ) @storeid [int], @topcount [int], @basecount [int] ) AS insert into [dbo].[storerule] ( [medicalid],[storeid],[topcount],[basecount] ) values (@medicalid,@storeid,@topcount,@basecount) (12)修改药品价格 CREATE PROCEDURE ( @medid [bigint], @jinjia [money], @shoujia [money], @dazhelv [float] ) AS update [dbo].[medical] set jinjia=@jinjia, shoujia=@shoujia, dazhelv=@dazhelv where ([medicalid]=@medid) (13)修改采购计划 CREATE PROCEDURE ( 系统设计过程论述 第 23 页 (共 56 页 ) @uid [int], @tcount [int], @zongjia [money], @provider [varchar](200), @execdate [smalldatetime] ) AS update [dbo].[stockplan] set [tcount]=@tcount,[zongjia]=@zongjia,[provider]=@provider, [execdate]=@execdate where ([uid]=@uid) (14)删除采购计划 CREATE PROCEDURE ( @uid [int] ) AS delete from [dbo].[stockplan] where ([uid]=@uid) (15)制订采购计划 CREATE PROCEDURE ( @medicalid [bigint], @tcount [int], @zongjia [money], @provider [varchar](200), @makeuserid [int], @execdate [smalldatetime] 连锁药房药品进销存管理系统 第 24 页 (共 56 页 ) ) AS insert into [dbo].[stockplan] ([medicalid],[tcount],[zongjia],[provider],[makeuserid],[execdate]) values (@medicalid,@tcount,@zongjia,@provider,@makeuserid,@execdate) (16)删除出入库记录 CREATE PROCEDURE (@uid [bigint]) AS delete from [dbo].[outinrecords] where ([uid]=@uid) (17)添加出入库记录 CREATE PROCEDURE ( @stype [bit], @medicalid [bigint], @storeid [int], @tcount [int], @managerid [int], @shandler [varchar](200) ) AS insert into [dbo].[outinrecords] ([medicalid],[storeid],[stype],[tcount],[managerid],[shandler]) values (@medicalid,@storeid,@stype,@tcount,@managerid,@shandler) 系统设计过程论述 第 25 页 (共 56 页 ) if @stype=0 /* 如果是入库 */ update [dbo].[storerule] set [curcount]=[curcount]+@tcount/*当前库存 =当前库存 +此次入库数量 */ where ([medicalid]=@medicalid and [storeid]=@storeid) else /* 否则为出库 */ update [dbo].[storerule] set [curcount]=[curcount]@tcount /* 当前库存 =当前库存 此次出库数量 */ where ([medicalid]=@medicalid and [storeid]=@storeid) (18)获取药品信息 CREATE PROCEDURE (@medicalid [bigint] ) AS select * from medical where (medicalid=@medicalid) (19)修改药品信息 CREATE PROCEDURE ( @medicalid [bigint], @ame [varchar](200), @tiaoma [varchar](200), @bianma [varchar](200), @jiliang [varchar](200), @pici [varchar](200), @jinjia [money], @shoujia [money], @dazhelv [float], @outdate [smalldatetime], @pertime [smalldatetime], @prodc。