“虛擬商店”之倉庫管理系統(tǒng)(存儲版)
【正文】 lect CustomerId,CustomerName,Address,Contact,ContactPhone,MemoFROM Customer Where CustomerType=1 or CustomerType=3k) 收貨客戶視圖建立CREATE VIEW View_BuyCustomer ASSelect CustomerId,CustomerName,Address,Contact,ContactPhone,MemoFROM Customer Where CustomerType=2 or CustomerType=3l) 庫存盤點存儲過程建立CREATE PROCEDURE Procedure_storecheck storemanid intASDeclare lastcheckdate datetime Select lastcheckdate=max(Checkdate) from CheckInfoIf ROWCOUNT =0 Insert into CheckInfo(GoodsId,CheckAmount,CheckDate,StoremanId) Select Goodsid,CheckAmount=(select isnull(sum(EntryAmount),0) from EntryInfo where GoodsId=)(select isnull(sum(ExportAmount),0) from ExportInfo where GoodsId=),getdate(),storemanid from Goods aElse Insert into CheckInfo(GoodsId,CheckAmount,CheckDate,StoremanId) Select Goodsid,CheckAmount=(select isnull(CheckAmount,0) from Checkinfo where GoodsId= and Checkdate=lastcheckdate)+(select isnull(sum(EntryAmount),0) from EntryInfo where GoodsId= and EntryDate=lastcheckdate)(select isnull(sum(ExportAmount),0) from ExportInfowhere GoodsId= and ExportDate=lastcheckdate),getdate(),storemanid from Goods am) 實時庫存情況存儲過程建立CREATE PROCEDURE Procedure_storereal ASDeclare lastcheckdate datetime Select lastcheckdate=max(Checkdate) from CheckInfoIf ROWCOUNT =0 Select ,CheckAmount=(select isnull(sum(EntryAmount),0) from EntryInfo where GoodsId=)(select isnull(sum(ExportAmount),0) from ExportInfo where GoodsId=) from Goods a,Measure b where =ElseSelect ,CheckAmount=(select isnull(CheckAmount,0) from Checkinfo where GoodsId= and Checkdate=lastcheckdate)+(select isnull(sum(EntryAmount),0) from EntryInfo where GoodsId= and EntryDate=lastcheckdate)(select isnull(sum(ExportAmount),0) from ExportInfo where GoodsId= and ExportDate=lastcheckdate) from Goods a,Measure b where =n) 查詢庫存異常情況存儲過程建立CREATE PROCEDURE Procedure_storealert ASCreate TABLE 1(Goodsid int,CheckAmount int,UpAlert int,DownAlert int)Declare lastcheckdate datetime Select lastcheckdate=max(Checkdate) from CheckInfoIf ROWCOUNT =0 Insert into 1(Goodsid,CheckAmount,UpAlert,DownAlert) Select ,CheckAmount=(select isnull(sum(EntryAmount),0) from EntryInfo where GoodsId=)(select isnull(sum(ExportAmount),0) from ExportInfo where GoodsIs=), from Goods a,StorehouseAlert b where = Else Insert into 1(Goodsid,CheckAmount,UpAlert,DownAlert) Select ,CheckAmount=(select isnull(CheckAmount,0) from Checkinfo where GoodsId= and Checkdate=lastcheckdate)+(select isnull(sum(EntryAmount),0) from EntryInfo where GoodsId= and EntryDate=lastcheckdate)(select isnull(sum(ExportAmount),0) from ExportInfo where GoodsId= and ExportDate=lastcheckdate) , from Goods a,StorehouseAlert b where =Select , from 1 a,Goods b, Measure c where = and = and ( or )第四章 系統(tǒng)初始化a) 將客戶信息加入到Customer表Insert into Customer(customertype,customername,address,contact,contactphone) Values(1,39。 代碼實現(xiàn)a) 創(chuàng)建數(shù)據(jù)庫USE masterGOCREATE DATABASE storehouseON ( NAME =39。庫存異常情況查詢，查詢低于庫存下限或高于上限的貨品數(shù)據(jù)。在實現(xiàn)時，將每一類用戶作為一個角色實現(xiàn)，這樣在授權(quán)時只需對角色授權(quán)，而無需對每個具體的用戶授權(quán)。庫管人員{庫管人員編號，登錄帳號，登錄密碼，姓名，用戶類型，備注說明}，主鍵是庫管人員編號。2． 單據(jù)查詢：可以隨時查詢?nèi)霂?、出庫、盤點等各種貨物單據(jù)。第二章 數(shù)據(jù)庫解構(gòu)該倉庫管理系統(tǒng)部分需要完成的主要功能包含：a) 基本資料管理：1． 供貨單位管理：設(shè)置并管理供貨商檔案，檔案信息包括客戶編號、客戶名稱、地址、聯(lián)系人、聯(lián)系電話和備注信息。整個工程采用結(jié)構(gòu)化的方式進行推進，逐步完善系統(tǒng)功能。目 錄設(shè)計思想 …………………………………………………………………………IV關(guān)鍵字 ………………………………………………………………………………IV第一章 選題說明 ……………………………………………………………1第二章 數(shù)據(jù)庫解構(gòu) 概念解構(gòu)設(shè)計 ……………………………………………………3 邏輯結(jié)構(gòu)設(shè)計 ……………………………………………………7 物理結(jié)構(gòu)設(shè)計 ………………………………………………………7第三章 數(shù)據(jù)庫設(shè)計 安全控制 ……………………………………………………………9 數(shù)據(jù)操作功能 ……………………………………………………10 視圖
公司管理相關(guān)推薦
鄂ICP備17016276號-1