數(shù)據(jù)庫課程設計報告(倉庫管理系統(tǒng))

數(shù)據(jù)庫課程設計報告（倉庫管理系統(tǒng)）三概要設計3.1E—R圖和相關說明類別（類別編號，類別名稱，上級類別）倉庫（倉庫編號，隸屬單位，備注）用戶（用戶名，密碼，權限類型）；客戶（客戶編號，聯(lián)系人，單位，聯(lián)系電話，傳真，通信地址，郵政編碼）產品（編號,名稱,規(guī)格,計算單位，參考價格,數(shù)量下限,數(shù)量下限,有效期,警告出庫（出庫類型,產品類型,單價,數(shù)量,總價格,客戶單位,出庫倉庫,經辦用戶,出庫日期）入庫（入庫類型,產品名稱,生產日期,單價,數(shù)量,總價格,客戶單位,入庫倉庫,經辦用戶,入庫日期）庫存（產品入庫單價,庫存數(shù)量,生產日期）四邏輯設計（一）、表的定義如下：Client表（客戶信息表）Product（產品信息表）ProinStore（庫存信息表）Takeout（出庫表）StoreIn（入庫表）Users（用戶表）ProType(產品類型表)Storehouse（倉庫信息表）（二）、視圖定義如下：一、report2(報表視圖)CREATEVIEWReport2ASSELECTSiType,Pid,Pprice,Pnum,(Pprice*Pnum)AsAmount,Cid,Sid,EmpName,OptDateFROMStoreInUNIONSELECTTtype,Pid,Pprice,Pnum,(Pprice*Pnum)AsAmount,Cid,Sid,EmpName,OptDateFROMTakeOut二、庫存統(tǒng)計視圖CREATEVIEWTotal_NumASSELECTdbo.ProInStore.Pid,SUM(dbo.ProInStore.Pnum)ASTotalFROMdbo.ProInStoreINNERJOINdbo.ProductONdbo.ProInStore.Pid=dbo.Product.PidGROUPBYdbo.ProInStore.Pid三、庫存視圖CREATEVIEWdbo.v_ProInStoreASSELECTp.Pid,SUM(s.Pnum)ASSumNumFROMdbo.ProductpINNERJOINdbo.ProInStoresONp.Pid=s.PidGROUPBYp.Pid四、入庫視圖CREATEVIEWdbo.v_storeinASSELECTp.Pid,SUM(i.Pnum)ASSumNum,SUM(i.Pnum*i.Pprice)ASSumPriceFROMdbo.ProductpINNERJOINdbo.StoreIniONp.Pid=i.PidGROUPBYp.Pid五、出庫視圖CREATEVIEWdbo.v_takeoutASSELECTp.Pid,SUM(t.Pnum)ASSumNum,SUM(t.Pnum*t.Pprice)ASSumPriceFROMdbo.ProductpINNERJOINdbo.TakeOuttONp.Pid=t.PidGROUPBYp.Pid六、失效期統(tǒng)計視圖CREATEVIEWdbo.ValidASSELECTdbo.ProInStore.SpIdAS庫存記錄編號,dbo.Product.PnameAS產品名稱,dbo.ProInStore.PpriceAS產品價格,dbo.ProInStore.PnumAS產品數(shù)量,dbo.ProInStore.MakeDateAS生產日期,dbo.Storehouse.SnameAS倉庫名稱,ROUND(DATEDIFF(day,DATEADD(day,dbo.Product.Valid,dbo.ProInStore.MakeDate),GETDATE()),0)AS距離失效期的天數(shù)FROMdbo.ProInStoreINNERJOINdbo.ProductONdbo.ProInStore.Pid=dbo.Product.PidANDDATEDIFF(day,GETDATE(),dbo.ProInStore.MakeDate)>=dbo.Product.Valid-dbo.Product.AlarmDaysINNERJOINdbo.StorehouseONdbo.ProInStore.Sid=dbo.Storehouse.Sid五詳細設計總體模塊設計如下：模塊一（基本信息管理）（由本人完成）準備工作：一完成對數(shù)據(jù)庫連接和操作的類

#import"c:\ProgramFiles\CommonFiles\System\ado\msado15.dll"no_namespacerename("EOF","adoEOF")rename("BOF","adoBOF")classADOConn{//定義變量public:_ConnectionPtrm_pConnection;//添加一個指向Connection對象的指針:_RecordsetPtrm_pRecordset;//添加一個指向Recordset對象的指針:public://定義方法ADOConn();virtual~ADOConn();//初始化—連接數(shù)據(jù)庫voidOnInitADOConn();_RecordsetPtr&GetRecordSet(_bstr_tbstrSQL);//執(zhí)行查詢BOOLExecuteSQL(_bstr_tbstrSQL);//執(zhí)行SQL語句，InsertUpdate_variant_tvoidExitConnect();voidBackup();//備份數(shù)據(jù)庫voidRestore();//恢復數(shù)據(jù)庫};一、用戶登陸：實現(xiàn)代碼如下：classCLoginDlg:publicCDialog{//Constructionpublic:CLoginDlg(CWnd*pParent=NULL);//standardconstructor~CLoginDlg();//DialogData//{{AFX_DATA(CLoginDlg)enum{IDD=IDD_LOGIN_DIALOG};CEditm_control_username;CEditm_control_password;intcount;intseconds;CSMButtonm_ok;CSMButtonm_cancel;CStringm_Pwd;CStringm_UserName;COLORREFm_clrText;COLORREFm_clrBkgnd;CBrushm_brBkgnd;CBrushm_brControlBkgnd1;CBrushm_brControlBkgnd2;protected:virtualvoidDoDataExchange(CDataExchange*pDX);//DDX/DDVsupportvirtualvoidOnOK();afx_msgBOOLOnEraseBkgnd(CDC*pDC);afx_msgHBRUSHOnCtlColor(CDC*pDC,CWnd*pWnd,UINTnCtlColor);afx_msgvoidOnTimer(UINTnIDEvent);virtualBOOLOnInitDialog();//}}AFX_MSGDECLARE_MESSAGE_MAP()};voidCLoginDlg::OnOK(){UpdateData(TRUE);if(m_UserName==""){MessageBox("請輸入用戶名");return;}if(m_Pwd==""){MessageBox("請輸入密碼");return;}//定義CUsers對象，用于從表Users中讀取數(shù)據(jù)CUsersuser;user.GetData(m_UserName);//如果讀取的數(shù)據(jù)與用戶輸入數(shù)據(jù)不同，則返回if(user.GetPwd()!=m_Pwd){if(count<3){MessageBox("用戶信息不正確，無法登錄！");count++;m_Pwd.Empty();m_control_password.SetFocus();UpdateData(FALSE)；}else{MessageBox("你可能是非法用戶!","警告",MB_OK|MB_ICONHAND);exit(0);}}else{KillTimer(0);CDialog::OnOK();}}二、客戶的實現(xiàn)代碼voidCClientManDlg::Refresh_Data(){UpdateData(TRUE);//將控件的值讀取到成員變量中CStringcSource;CStringcCtype;//讀取客戶類型值cCtype.Format("%d",m_Ctype.GetCurSel()+1);//設置SELECT語句，按客戶單位排序cSource="SELECTCid,CnameAS客戶單位,ContactAS聯(lián)系人,AddressAS通信地址,";cSource+="PostcodeAS郵政編碼,PhoneAS聯(lián)系電話,FaxAS傳真電話,MemoAS備注";cSource+="FROMClientWHERECtype="+cCtype+"ORDERBYCname";m_adodc.SetRecordSource(cSource);m_adodc.Refresh();//設置表格列寬度_variant_tvIndex;vIndex=long(0);m_datagrid.GetColumns().GetItem(vIndex).SetWidth(0);}BOOLCClientManDlg::OnInitDialog(){CDialog::OnInitDialog();//TODO:Addextrainitializationherem_Ctype.SetCurSel(0);//設置客戶類型Refresh_Data();//刷新表格中的記錄集returnTRUE;//returnTRUEunlessyousetthefocustoacontrol//EXCEPTION:OCXPropertyPagesshouldreturnFALSE}voidCClientManDlg::OnAddButton(){//TODO:AddyourcontrolnotificationhandlercodehereUpdateData(TRUE);//打開編輯對話框CClientEditDlgdlg;dlg.cCid="";dlg.iCtype=m_Ctype.GetCurSel()+1;if(dlg.DoModal()==IDOK)Refresh_Data();}voidCClientManDlg::OnSelchangeCtypeCombo(){//TODO:AddyourcontrolnotificationhandlercodehereRefresh_Data();}voidCClientManDlg::OnModiButton(){//TODO:Addyourcontrolnotificationhandlercodehereif(m_adodc.GetRecordset().GetEof()){MessageBox("請選擇要修改的記錄");return;}UpdateData(TRUE);CClientEditDlgdlg;dlg.cCid=m_datagrid.GetItem(0);//記錄編號dlg.iCtype=m_Ctype.GetCurSel()+1;//客戶類型dlg.m_Cname=m_datagrid.GetItem(1);//單位名稱dlg.cCname=m_datagrid.GetItem(1);dlg.m_Contact=m_datagrid.GetItem(2);//聯(lián)系人dlg.m_Address=m_datagrid.GetItem(3);//通信地址dlg.m_Postcode=m_datagrid.GetItem(4);//郵政編碼dlg.m_Phone=m_datagrid.GetItem(5);//聯(lián)系電話dlg.m_Fax=m_datagrid.GetItem(6);//傳真dlg.m_Memo=m_datagrid.GetItem(7);//備注信息if(dlg.DoModal()==IDOK)Refresh_Data();}voidCClientManDlg::OnDelButton(){//TODO:Addyourcontrolnotificationhandlercodehereif(m_adodc.GetRecordset().GetEof()){MessageBox("請選擇要刪除的記錄");return;}CStringCid;Cid=m_datagrid.GetItem(0);CStoreInobj;if(obj.HaveClient(Cid)==1){MessageBox("客戶出現(xiàn)在入庫單中，不能刪除");return;}CTakeOutobj1;if(obj1.HaveClient(Cid)==1){MessageBox("客戶出現(xiàn)在出庫單中，不能刪除");return;}if(MessageBox("是否刪除當前記錄","請確定",MB_YESNO)==IDYES){CClientclt;clt.sql_delete(m_datagrid.GetItem(0));Refresh_Data();}}三、倉庫信息管理voidCStoreHouseManDlg::Refresh_Data(){UpdateData(TRUE);CStringcSource;cSource="SELECTSid,SnameAS倉庫單位,MemoAS備注";cSource+="FROMStorehouseORDERBYSname";m_adodc.SetRecordSource(cSource);m_adodc.Refresh();//設置表格列寬度_variant_tvIndex;vIndex=long(0);m_datagrid.GetColumns().GetItem(vIndex).SetWidth(0);vIndex=long(1);m_datagrid.GetColumns().GetItem(vIndex).SetWidth(100);vIndex=long(2);m_datagrid.GetColumns().GetItem(vIndex).SetWidth(420);}voidCStoreHouseManDlg::OnAddButton(){//TODO:AddyourcontrolnotificationhandlercodehereUpdateData(TRUE);//打開編輯對話框CStoreHouseEditDlgdlg;dlg.cSid="";if(dlg.DoModal()==IDOK)Refresh_Data();}voidCStoreHouseManDlg::OnModiButton(){//TODO:Addyourcontrolnotificationhandlercodehereif(m_adodc.GetRecordset().GetEof()){MessageBox("請選擇要修改的記錄");return;}UpdateData(TRUE);CStoreHouseEditDlgdlg;dlg.cSid=m_datagrid.GetItem(0);//記錄編號dlg.m_Sname=m_datagrid.GetItem(1);//倉庫名稱dlg.m_Memo=m_datagrid.GetItem(2);//備注信息if(dlg.DoModal()==IDOK)Refresh_Data();}voidCStoreHouseManDlg::OnDelButton(){//TODO:Addyourcontrolnotificationhandlercodehereif(m_adodc.GetRecordset().GetEof()){MessageBox("請選擇要刪除的記錄");return;}CStringSid;Sid=m_datagrid.GetItem(0);CStoreInobj;if(obj.HaveStore(Sid)==1){MessageBox("此倉庫信息出現(xiàn)在入庫單中，不能刪除");return;}CTakeOutobj1;if(obj1.HaveStore(Sid)==1){MessageBox("此倉庫信息出現(xiàn)在出庫單中，不能刪除");return;}CProInStoreobj2;if(obj2.HaveStore(Sid)==1){MessageBox("此倉庫信息出現(xiàn)在庫存產品信息中，不能刪除");return;}if(MessageBox("是否刪除當前記錄","請確定",MB_YESNO)==IDYES){CStorehousesh;sh.sql_delete(Sid);Refresh_Data();}}BOOLCStoreHouseManDlg::OnInitDialog(){CDialog::OnInitDialog();//TODO:AddextrainitializationhereRefresh_Data();returnTRUE;//returnTRUEunlessyousetthefocustoacontrol//EXCEPTION:OCXPropertyPagesshouldreturnFALSE}四、用戶信息管理voidCUserManDlg::OnAddButton(){CUserEditDlgdlg;dlg.iUserType=2;if(dlg.DoModal()==IDOK)m_adodc.Refresh();}voidCUserManDlg::OnModiButton(){if(m_datalist.GetText()==""){MessageBox("請選擇用戶");return;}if(curUser.GetUserName()!="Admin"&&curUser.GetUserName()!=m_datalist.GetText()&&m_datalist.GetBoundText()=="1"){//除Admin外，其他管理員只能修改普通用戶信息MessageBox("只能對普通用戶進行密碼復位");return;}if(MessageBox("是否對當前用戶進行密碼復位","請確認",MB_YESNO)==IDYES){CUsersusr;usr.SetPwd("888888");//設置默認密碼usr.sql_updatePwd(m_datalist.GetText());MessageBox("密碼已經復位");}}voidCUserManDlg::OnDelButton(){if(m_datalist.GetText()==""){MessageBox("請選擇用戶");return;}if(curUser.GetUserName()!="Admin"&&m_datalist.GetBoundText()=="1"){//除Admin外，其他管理員只能刪除普通用戶MessageBox("只能刪除普通用戶");return;}if(m_datalist.GetText()=="Admin"){MessageBox("不能刪除Admin用戶");return;}if(MessageBox("是否刪除當前用戶","請確認",MB_YESNO)==IDYES){CUsersusr;usr.sql_delete(m_datalist.GetText());m_adodc.Refresh();}}五、用戶密碼修改voidCChangePwdDlg::OnOK(){//將對話框中編輯框的數(shù)據(jù)讀取到成員變量中UpdateData(TRUE);//檢查數(shù)據(jù)有效性if(m_OldPwd==""){MessageBox("請輸入舊密碼");return;}if(m_NewPwd1==""){MessageBox("請輸入新密碼");return;}if(m_NewPwd1!=m_NewPwd2){MessageBox("兩次輸入的新密碼不同");return;}//定義CUsers對象，用于從表Users中讀取數(shù)據(jù)CUsersuser;user.GetData(m_UserName);//如果讀取的數(shù)據(jù)與用戶輸入數(shù)據(jù)不同，則返回if(user.GetPwd()!=m_O