专业课程设计实验报告产品订单标准管理系统.doc

专业课程设计实验报告产品订单标准管理系统 数据库课程设计产品订单管理系统二零一二年五月目录TOC o “1-3” h z u 一、引言 1二、开发环境 1三、需求分析p 13.1按客户分类订单管理子系统 23.2其他信息子系统 23.3预览报表子系统 2四、概念结构设计定单 24.2子系统模型图 34.21订单的职员实体关系 34.2产品订单系统产品实体关系 34.23产品订单系统订单实体关系 44.2产品订单系统订单明细实体关系 54.25产品订单系统付费实体关系 54.26产品订单系统付费方式实体关系 64.27产品订单系统客户实体关系 64.2产品订单系统我的公司信息实体关系 7五、数据字典（比较多，不一一列出） 7六、系统详细设计 86.1数据库表的设计 86.2由表生成各种窗口 136.3生成报表 17七、详细设计代表性的SQL语句及结果 187.按职员分类的销售额子查询 187.按客户分类的销售额子查询 18八、设计细节展示 198.发票窗体设计 198.结果展示 198.按产品分类销售额窗体设计 208.结果展示 20九、关键技术及体会 20十、课程设计总结 21附录：主窗体代码： 22一、引言订单管理是每所企业或工厂必需面正确工作，尤其这几年，中国加入WTO，伴随国家政策调整，给企业订单管理，商品进存销全部带来了不少冲击。但一直以来用传统人手方法管理文件档案，这种管理方法存在很多缺点：效率低，保密性差，另外伴随进货数量增加，这肯定增加了订单管理者工作量和劳动强度。同时，将产生大量文件和数据，这给订单信息查找，更新和维护全部带来了不少困难。据调查，现在中国还有相当一部分企业订单还停留在纸介质基础上，尤其小型工厂或企业对订单管理更是落后。这么管理机制已经不能适应时代发展需求，其管理方法将浪费人力物力。伴随科技不停提升，计算机科学和技术日渐成熟，这种传统手工管理模式肯定被以计算机为物质基础信息管理方法所替换。二、开发环境：Intel Pentium双核 2.70GHz内存：2G开发软件： office access 20_操作系统： dow _P professional 版本20_ Service Pack 3三、需求分析p 我企业为加强订单管理，准备开发一个订单管理系统，该系统包含按用户分类订单管理子系统，其它信息子系统，预览报表子系统。下面是经需求调查并初步归纳出相关存放要求：以下所表示3.1按用户分类订单管理子系统a.订购情况：订单标识，职员标识，产品，单价，数量，折扣，总计等信息。b.付费情况：包含订单标识，付费方法，付费日期，付费金额，持卡人姓名，信用卡号等信息c.预览发票：包含订单标识，发票日期，发票金额，付款条款等信息。3.2其它信息子系统a.职员情况：包含职员姓，名，职务，电话号，邮件地址等信息b.企业信息：包含企业全称，地址，邮编，营业税率，电话号，传真号等c.产品情况：包含产品标识，产品名称，产品单价等。d.付费方法：包含付费方法标识，付费方法，是否使用信用卡等。e.装运方法：包含装运方法标识，装运方法。3.3预览报表子系统a.按用户情况：包含用户姓名，电话号，传真号，企业名称等b.按应收账款：包含用户姓名，目前应收账款，30-60天后应收账款，61-90天后应收账款，大于90天后应收账款，余额等信息。c.按用户分类销售额：包含企业名称，单位总计，总销售额，营业税，运费总计，发票上金额等信息。e.按职员分类销售额报表：包含职员姓名，单位总数，总销售额等信息。f.按产品分类销售额：包含产品名称，单位总计，总销售额等信四、概念结构设计定单 4.1整体ER模型图4.2子系统模型图4.21订单职员实体关系4.2产品订单系统产品实体关系4.23产品订单系统订单实体关系4.2产品订单系统订单明细实体关系4.25产品订单系统付费实体关系4.26产品订单系统付费方法实体关系4.27产品订单系统用户实体关系4.2产品订单系统我企业信息实体关系五、数据字典（比较多，不一一列出）六、系统具体设计6.1数据库表设计本系统表一共有十一张，分别为“Switchboad Items”、“产品”、“订单”、“订单明细”、“付费”、“付费方法”、“用户”、“我企业信息”、“职员”、“装运方法”。表6-1 表6-2 Switchboad Items”注：表6-2“Switchboard Items”关键用于对主界面设置，包含“Switchboard ID”、“项目编号”、“项目文字”、“命令”，“参数”。表6-3 产品注：表6-3 “产品“关键用于储存产品信息包含产品标识、产品名称、单件等三项信息表6-4订单表6-5订单明细表注：表6-4 “订单”及表6-5“订单明细表”关键用于查看订单基础内容。“定单”关键包含“订单标识”、“用户标识”、“职员标识”、“订单编号”、“收货方名称”、“收货方地址”、“收货方城市”、“收货方省/自治区”、“收货方邮政编码”、“收货方国家”、“收货方电话”、“发货日期”、“装运方法标识”、“运费”、“营业税额”；“订单明细”关键包含“订单明细标识”、“点单标识”、“产品标识”、“数量”“单价”、“折扣”等。表6-6付费注：表6-6“付费”只要用于查看付费方法、金额和日期。只要包含“付费标识”、“订单标识”、“付费金额”、“付费日期”、“信用卡号”、“持卡人”、“信用卡到期日”、“信用卡权限”、“付费方法标识”。表6-7付费方法注：表6-7“付费方法”关键包含“付费方法标识”、“付费方法”、“是否使用信用卡”表6-8用户注：表6-8“用户”关键用于查看用户基础信息。表6-9我企业信息注：表6-9“我企业信息”关键用于查看企业基础信息。表6-10职员表6-11装运方法6.2由表生成多种窗口图61 主切换面板图6-2 按用户分类订单图63 产品窗口图6-4 按用户分类订单图6-5 打印发票窗口图6-6 订单分类窗口图6-7 订单明细窗口图6-8 付费窗口图6-9 付费方法窗口图 6-10 我企业信息窗口图6-11 职员窗口图612 装运方法图613 装运信息6.3生成报表按产品分类销售额生成报表注:输入开始和结束日期点预览后七、具体设计代表性SQL语句及结果7.按职员分类销售额子查询SELECT DISTINCTROW 订单.EmployeeID, 订单.OrderDate, 订单.FreightCharge, 订单.SalesTa_Rate, Sum(CLng(Quantity_UnitPrice_(1-Discount)_100)/100) AS 总销售额, Sum(订单明细表.Quantity) AS 单位总数FROM 订单 LEFT JOIN 订单明细表 ON 订单.OrderID = 订单明细表.OrderIDGROUP BY 订单.EmployeeID, 订单.OrderDate, 订单.FreightCharge, 订单.SalesTa_Rate;7.按用户分类销售额子查询SELECT DISTINCTROW 订单.CustomerID, 订单.OrderDate, 订单.FreightCharge, 订单.SalesTa_Rate, Sum(CLng(Quantity_UnitPrice_(1-Discount)_100)/100) AS 总销售额, Sum(订单明细表.Quantity) AS 单位总计FROM 订单 LEFT JOIN 订单明细表 ON 订单.OrderID = 订单明细表.OrderIDGROUP BY 订单.CustomerID, 订单.OrderDate, 订单.FreightCharge, 订单.SalesTa_Rate;八、设计细节展示8.发票窗体设计8.结果展示8.按产品分类销售额窗体设计8.结果展示九、关键技术及体会本课程设计中关键用了表操作，查询操作，窗体设计，菜单和工具栏使用等技术，开始只是模拟书上例题进行练习，做了几次后，发觉利用模板，或着使用数据导入导出，和，等结合使用会愈加方便，快捷。十、课程设计总结在这次数据库实习过程中，我们利用课下时间进行学习，对数据库基础操作和感念有了大致了解。能够用用access设计一个简单订单管理系统，其关键功效是查询和管理。同时，这次实习培养了我们对数据库爱好，有利于我们以后对数据库有深入了解和学习，也给了我们一个动手实践机会，使我们在理论付诸于实践过程中有了新能力上提升参考文件access20_自学教程 中国经济出版社 20_.8access20_应用设计教程 高等教育出版社2附录：主窗体代码：Option pare DatabaseOption E_plicitConst conNumButtons = 8Const conFontWeightBold = 700Const conFontWeightNormal = 400Private Sub Bo_23_ClickEnd SubPrivate Sub cmdE_it_ClickCloseCurrentDatabaseEnd SubSub cmdE_it_GotFocusDim intion As Integer#;If the E_it Button has received the focus, turn off the focus on all the menu optionsFor intion = 1 To conNumButtonsMe(“Option” intion).Visible = FalseMe(“OptionLabel” intion).FontWeight = conFontWeightNormalNe_t intionE_itLabel.FontUnderline = TrueEnd SubPrivate Sub cmdE_it_LostFocusE_itLabel.FontUnderline = FalseEnd SubPrivate Sub cmdE_it_MouseDown(Button As Integer, Shift As Integer, _ As Single, Y As Single)E_itLabel.FontWeight = conFontWeightBoldEnd SubPrivate Sub cmdE_it_MouseUp(Button As Integer, Shift As Integer, _ As Single, Y As Single)E_itLabel.FontWeight = conFontWeightNormalEnd SubPrivate Sub Form_Open(Cancel As Integer)#; Minimize the database dow and initialize the form.On Error GoTo Form_Open_Err#; Minimize the database dow. DoCmd.SelectObject acForm, “Switchboard”, TrueDoCmd.Minimize#; Move to the switchboard page that is marked as the default. Me.Filter = “ItemNumber = 0 AND Argument = #;默认#; ”Me.FilterOn = TrueForm_Open_E_it:E_it SubForm_Open_Err:MsgBo_ Err.DescriptionResume Form_Open_E_itEnd SubPrivate Sub Form_Current#; Update the caption and fill in the list of options. Me.Caption = Nz(Me!ItemTe_t, “”)FillOptionsEnd SubPrivate Sub FillOptions#; Fill in the options for this switchboard page. #; The number of buttons on the form. Dim dbs As DatabaseDim rst As RecordsetDim strSQL As StringDim intion As Integer#; Set the focus to the first button on the form,#; and then hide all of the buttons on the form#; but the first. You can#;t hide the field with the focus. Me!Option1.Visible = TrueMe!mand1.Enabled = TrueMe!mand1.SetFocusWith Me!OptionLabel1.Visible = True.FontWeight = conFontWeightBoldEnd WithFor intion = 2 To conNumButtonsMe(“Option” intion).Visible = FalseMe(“OptionLabel” intion).Visible = FalseMe(“OptionLabel” intion).FontWeight = conFontWeightNormalMe(“mand” intion).Enabled = FalseNe_t intion#; Open the table of Switchboard Items, and find#; the first item for this Switchboard Page. Set dbs = CurrentDbstrSQL = “SELECT _FROM Switchboard Items”strSQL = strSQL “ WHERE ItemNumber > 0 AND SwitchboardID=” Me!SwitchboardIDstrSQL = strSQL “ ORDER BY ItemNumber;”Set rst = dbs.OpenRecordset(strSQL)#; If there are no options for this Switchboard Page,#; display a message. Otherwise, fill the page with the items. If (rst.EOF) ThenMe!OptionLabel1.Caption = “There are no items for this switchboard page”ElseWhile (Not (rst.EOF)Me(“OptionLabel” rst!ItemNumber).Visible = TrueMe(“OptionLabel” rst!ItemNumber).Caption = rst!ItemTe_tMe(“mand” rst!ItemNumber).Enabled = Truerst.MoveNe_tWendEnd If#; Close the recordset and the database. rst.Closedbs.CloseEnd SubPrivate Function HandleFocus(intBtn As Integer)#; This function is called when a menu option receives the focus.#; intBtn indicates which button was clicked.Dim intion As IntegerOn Error GoTo HandleMouseOver_ErrFor intion = 1 To conNumButtons#;Show that this menu option has the focusIf intion = intBtn ThenMe(“Option” intion).Visible = TrueMe(“OptionLabel” intion).FontWeight = conFontWeightBoldMe(“mand” intBtn).SetFocus#;and turn off the focus on the other optionsElseMe(“Option” intion).Visible = FalseMe(“OptionLabel” intion).FontWeight = conFontWeightNormalEnd IfNe_t intionHandleMouseOver_E_it:E_it FunctionHandleMouseOver_Err:MsgBo_ “实施该命令时犯错。”, vbCriticalResume HandleMouseOver_E_itEnd FunctionPrivate Function HandleButtonClick(intBtn As Integer)#; This function is called when a button is clicked.#; intBtn indicates which button was clicked. #; Constants for the mands that can be e_ecuted. Const conCmdGotoSwitchboard = 1Const conCmdOpenFormAdd = 2Const conCmdOpenFormBrowse = 3Const conCmdOpenReport = 4Const conCmdCustomizeSwitchboard = 5Const conCmdE_itlication = 6Const conCmdRunMacro = 7Const conCmdRunCode = 8#; An error that is special cased. Const conErrDoCmdCancelled = 2501Dim dbs As DatabaseDim rst As RecordsetOn Error GoTo HandleButtonClick_Err#; Find the item in the Switchboard Items table#; that corresponds to the button that was clicked. Set dbs = CurrentDbSet rst = dbs.OpenRecordset(“Switchboard Items”, dbOpenDynaset)rst.FindFirst “SwitchboardID=” Me!SwitchboardID “ AND ItemNumber=” intBtn#; If no item matches, report the error and e_it the function. If (rst.NoMatch) ThenMsgBo_ “读取 Switchboard Items 表时犯错。”rst.Closedbs.CloseE_it FunctionEnd IfSelect Case rst!mand#; Go to another switchboard.Case conCmdGotoSwitchboardMe.Filter = “ItemNumber = 0 AND SwitchboardID=” rst!Argument#; Open a form in Add mode.Case conCmdOpenFormAddDoCmd.OpenForm rst!Argument, , , , acAdd#; Open a form.Case conCmdOpenFormBrowseDoCmd.OpenForm rst!Argument#; Open a report.Case conCmdOpenReportDoCmd.OpenReport rst!Argument, acPreview#; Customize the Switchboard.Case conCmdCustomizeSwitchboard#; Handle the case where the Switchboard Manager#; is not installed (e.g.Minimal Install).On Error Resume Ne_tlication.Run “ACWZMAIN.sbm_Entry”If (Err 0) Then MsgBo_ “命令不可用”On Error GoTo 0#; Update the form.Me.Filter = “ItemNumber = 0 AND Argument = #;默认#; ”Me.Caption = Nz(Me!ItemTe_t, “”)FillOptions#; E_it the lication.Case conCmdE_itlicationCloseCurrentDatabase#; Run a macro.Case conCmdRunMacroDoCmd.RunMacro rst!Argument#; Run code.Case conCmdRunCodelication.Run rst!Argument#; Any other mand is unrecognized.Case ElseMsgBo_ “未知选项”End Select#; Close the recordset and the database. rst.Closedbs.CloseHandleButtonClick_E_it:E_it FunctionHandleButtonClick_Err:#; If the action was cancelled by the user for#; some reason, don#;t display an error message. #; Instead, resume on the ne_t line. If (Err = conErrDoCmdCancelled) ThenResume Ne_tElseMsgBo_ “实施该命令时犯错。”, vbCriticalResume HandleButtonClick_E_itEnd IfEnd Function第 14 页 共 14 页