【文章內(nèi)容簡(jiǎn)介】 銷售日期 medicalid bigint 藥品 id tcount int 銷售數(shù)量 opuserid int 操作員 id zongjia money 總價(jià) recipeid Varchar(50) 處方編號(hào) 個(gè)人設(shè)置 個(gè)人設(shè)置包括個(gè)人資料修改和密碼修改。個(gè)人資料包括管理員的姓名、姓 名、密碼保護(hù)（密碼問(wèn)題和問(wèn)題答案）、聯(lián)系電話和 Email，個(gè)人資料修改就可以對(duì)這些資料進(jìn)行修改。密碼修改是對(duì)管理員登入密碼進(jìn)行修改。 表 7 用戶信息表 (users) 字段名 字段類型 說(shuō)明 userid int 用戶唯一 id，自動(dòng)增量 username Varchar(50) 倉(cāng)庫(kù)名稱 password Varchar(50) 倉(cāng)庫(kù)所在地址 truename Varchar(50) 倉(cāng)庫(kù)描述 sex Bit 性別 1表示男， 0表示女 question Varchar(50) 密碼問(wèn)題 answer Varchar(50) 問(wèn)題答案 Varchar(50) phone Varchar(50) 電話 usertype int 用戶類型，暫時(shí)未使用該字段 存儲(chǔ)過(guò)程說(shuō)明 (1)用戶信息修改 CREATE PROCEDURE ( 連鎖藥房藥品進(jìn)銷存管理系統(tǒng) 第 18 頁(yè) (共 56 頁(yè) ) @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)倉(cāng)庫(kù)選擇 CREATE PROCEDURE (@storeid [int]) AS select * from [dbo].[stores] where (storeid=@storeid) (3)倉(cāng)庫(kù)修改 CREATE PROCEDURE ( @storeid [int], @taddr [varchar](200), @tdesc [varchar](200) ) 系統(tǒng)設(shè)計(jì)過(guò)程論述 第 19 頁(yè) (共 56 頁(yè) ) AS update [dbo].[stores] set [address]=@taddr,[description]=@tdesc where (storeid=@storeid) (4)倉(cāng)庫(kù)刪除 CREATE PROCEDURE (@storeid [int]) AS delete from [dbo].[stores] where ([storeid]=@storeid) (5)添加新倉(cāng)庫(kù) 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] ) 連鎖藥房藥品進(jìn)銷存管理系統(tǒng) 第 20 頁(yè) (共 56 頁(yè) ) 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)獲取存儲(chǔ)規(guī)則 CREATE PROCEDURE ( @uid [bigint] ) AS select [dbo].[stores].[name] as storename, [dbo].[medical].[ame] as medname, [dbo].[storerule].[topcount], [dbo].[storerule].[basecount] 系統(tǒng)設(shè)計(jì)過(guò)程論述 第 21 頁(yè) (共 56 頁(yè) ) 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)修改存儲(chǔ)規(guī)則 CREATE PROCEDURE ( @uid [bigint], @topcount [int], @basecount [int] ) AS update [dbo].[storerule] set [topcount]=@topcount,[basecount]=@basecount where ([uid]=@uid) (10)刪除存儲(chǔ)規(guī)則 CREATE PROCEDURE (@uid [bigint]) AS delete from [dbo].[storerule] where ([uid]=@uid) (11)添加存儲(chǔ)規(guī)則 CREATE PROCEDURE ( @medicalid [bigint], 連鎖藥房藥品進(jìn)銷存管理系統(tǒng) 第 22 頁(yè) (共 56 頁(yè) ) @storeid [int], @topcount [int], @basecount [int] ) AS insert into [dbo].[storerule] ( [medicalid],[storeid],[topcount],[basecount] ) values (@medicalid,@storeid,@topcount,@basecount) (12)修改藥品價(jià)格 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)修改采購(gòu)計(jì)劃 CREATE PROCEDURE ( 系統(tǒng)設(shè)計(jì)過(guò)程論述 第 23 頁(yè) (共 56 頁(yè) ) @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)刪除采購(gòu)計(jì)劃 CREATE PROCEDURE ( @uid [int] ) AS delete from [dbo].[stockplan] where ([uid]=@uid) (15)制訂采購(gòu)計(jì)劃 CREATE PROCEDURE ( @medicalid [bigint], @tcount [int], @zongjia [money], @provider [varchar](200), @makeuserid [int], @execdate [smalldatetime] 連鎖藥房藥品進(jìn)銷存管理系統(tǒng) 第 24 頁(yè) (共 56 頁(yè) ) ) AS insert into [dbo].[stockplan] ([medicalid],[tcount],[zongjia],[provider],[makeuserid],[execdate]) values (@medicalid,@tcount,@zongjia,@provider,@makeuserid,@execdate) (16)刪除出入庫(kù)記錄 CREATE PROCEDURE (@uid [bigint]) AS delete from [dbo].[outinrecords] where ([uid]=@uid) (17)添加出入庫(kù)記錄 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) 系統(tǒng)設(shè)計(jì)過(guò)程論述 第 25 頁(yè) (共 56 頁(yè) ) if @stype=0 /* 如果是入庫(kù) */ update [dbo].[storerule] set [curcount]=[curcount]+@tcount/*當(dāng)前庫(kù)存 =當(dāng)前庫(kù)存 +此次入庫(kù)數(shù)量 */ where ([medicalid]=@medicalid and [storeid]=@storeid) else /* 否則為出庫(kù) */ update [dbo].[storerule] set [curcount]=[curcount]@tcount /* 當(dāng)前庫(kù)存 =當(dāng)前庫(kù)存 此次出庫(kù)數(shù)量 */ 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