数据库课程设计--产品订单管理系统.docx

数据库课程设计 产品订单管理系统 二零一二年五月目录一、引言1二、开发环境1三、需求分析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，随着国家政策的调整，给企业的订单管理，商品的进存销都带来了不少冲击。但一直以来用传统人手方式管理文件档案，这种管理方式存在许多缺点：效率低，保密性差，另外随着进货数量的增加，这必然增加了订单管理者的工作量和劳动强度。同时，将产生大量的文件和数据，这给订单信息的查找，更新和维护都带来了不少困难。据调查，目前我国还有相当一部分企业的订单还停留在纸介质的基础上，尤其小型工厂或企业对订单的管理更是落后。这样的管理机制已经不能适应时代发展的需求，其管理方法将浪费人力物力。随着科技的不断提高，计算机科学与技术日渐成熟，这种传统的手工管理模式必然被以计算机为物质基础的信息管理方式所取代。二、开发环境 CPU： Intel Pentium双核 2.70GHz内存： 2G开发软件： Microsoft office access 2003操作系统： Microsoft window XP professional 版本2002 Service Pack 3三、需求分析我公司为加强订单管理，准备开发一个订单管理系统，该系统包括按客户分类订单管理子系统，其他信息子系统，预览报表子系统。下面是经需求调查并初步归纳出的相关存储要求：如下所示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, 订单.SalesTaxRate, Sum(CLng(Quantity*UnitPrice*(1-Discount)*100)/100) AS 总销售额, Sum(订单明细表.Quantity) AS 单位总数FROM 订单 LEFT JOIN 订单明细表 ON 订单.OrderID = 订单明细表.OrderIDGROUP BY 订单.EmployeeID, 订单.OrderDate, 订单.FreightCharge, 订单.SalesTaxRate;7.按客户分类的销售额子查询SELECT DISTINCTROW 订单.CustomerID, 订单.OrderDate, 订单.FreightCharge, 订单.SalesTaxRate, Sum(CLng(Quantity*UnitPrice*(1-Discount)*100)/100) AS 总销售额, Sum(订单明细表.Quantity) AS 单位总计FROM 订单 LEFT JOIN 订单明细表 ON 订单.OrderID = 订单明细表.OrderIDGROUP BY 订单.CustomerID, 订单.OrderDate, 订单.FreightCharge, 订单.SalesTaxRate;八、设计细节展示8.发票窗体设计8.结果展示8.按产品分类销售额窗体设计8.结果展示九、关键技术及体会本课程设计中主要用了表操作，查询操作，窗体设计，菜单与工具栏的使用等技术，开始只是模仿书上的例题进行练习，做了几次后，发现利用模板，或着使用数据导入导出，和，等结合使用会更加方便，快捷。十、课程设计总结在这次数据库实习的过程中，我们利用课下时间进行学习，对数据库的基本操作和感念有了大体的了解。能够用用access设计一个简单的订单管理系统，其主要功能是查询与管理。同时，这次实习培养了我们对数据库的兴趣，有助于我们以后对数据库有进一步的了解和学习，也给了我们一个动手实践的机会，使我们在理论付诸于实践的过程中有了新的能力上的提升参考文献1. access2003自学教程 中国经济出版社 2001.82. access2003应用设计教程 高等教育出版社2附录：主窗体代码：Option Compare DatabaseOption ExplicitConst conNumButtons = 8Const conFontWeightBold = 700Const conFontWeightNormal = 400Private Sub Box23_Click()End SubPrivate Sub cmdExit_Click() CloseCurrentDatabaseEnd SubSub cmdExit_GotFocus() Dim intOption As Integer 'If the Exit Button has received the focus, turn off the focus on all the menu options For intOption = 1 To conNumButtons Me("Option" & intOption).Visible = False Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal Next intOption ExitLabel.FontUnderline = TrueEnd SubPrivate Sub cmdExit_LostFocus() ExitLabel.FontUnderline = FalseEnd SubPrivate Sub cmdExit_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) ExitLabel.FontWeight = conFontWeightBoldEnd SubPrivate Sub cmdExit_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single) ExitLabel.FontWeight = conFontWeightNormalEnd SubPrivate Sub Form_Open(Cancel As Integer)' Minimize the database window and initialize the form.On Error GoTo Form_Open_Err ' Minimize the database window. DoCmd.SelectObject acForm, "Switchboard", True DoCmd.Minimize ' Move to the switchboard page that is marked as the default. Me.Filter = "ItemNumber = 0 AND Argument = '默认' " Me.FilterOn = TrueForm_Open_Exit: Exit SubForm_Open_Err: MsgBox Err.Description Resume Form_Open_ExitEnd SubPrivate Sub Form_Current()' Update the caption and fill in the list of options. Me.Caption = Nz(Me!ItemText, "") FillOptionsEnd SubPrivate Sub FillOptions()' Fill in the options for this switchboard page. ' The number of buttons on the form. Dim dbs As Database Dim rst As Recordset Dim strSQL As String Dim intOption 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 = True Me!Command1.Enabled = True Me!Command1.SetFocus With Me!OptionLabel1 .Visible = True .FontWeight = conFontWeightBold End With For intOption = 2 To conNumButtons Me("Option" & intOption).Visible = False Me("OptionLabel" & intOption).Visible = False Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal Me("Command" & intOption).Enabled = False Next intOption ' Open the table of Switchboard Items, and find ' the first item for this Switchboard Page. Set dbs = CurrentDb() strSQL = "SELECT * FROM Switchboard Items" strSQL = strSQL & " WHERE ItemNumber > 0 AND SwitchboardID=" & Me!SwitchboardID strSQL = 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) Then Me!OptionLabel1.Caption = "There are no items for this switchboard page" Else While (Not (rst.EOF) Me("OptionLabel" & rst!ItemNumber).Visible = True Me("OptionLabel" & rst!ItemNumber).Caption = rst!ItemText Me("Command" & rst!ItemNumber).Enabled = True rst.MoveNext Wend End If ' Close the recordset and the database. rst.Close dbs.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 intOption As IntegerOn Error GoTo HandleMouseOver_Err For intOption = 1 To conNumButtons 'Show that this menu option has the focus. If intOption = intBtn Then Me("Option" & intOption).Visible = True Me("OptionLabel" & intOption).FontWeight = conFontWeightBold Me("command" & intBtn).SetFocus '. and turn off the focus on the other options Else Me("Option" & intOption).Visible = False Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal End If Next intOptionHandleMouseOver_Exit: Exit FunctionHandleMouseOver_Err: MsgBox "执行该命令时出错。", vbCritical Resume HandleMouseOver_ExitEnd FunctionPrivate Function HandleButtonClick(intBtn As Integer)' This function is called when a button is clicked.' intBtn indicates which button was clicked. ' Constants for the commands that can be executed. Const conCmdGotoSwitchboard = 1 Const conCmdOpenFormAdd = 2 Const conCmdOpenFormBrowse = 3 Const conCmdOpenReport = 4 Const conCmdCustomizeSwitchboard = 5 Const conCmdExitApplication = 6 Const conCmdRunMacro = 7 Const conCmdRunCode = 8 ' An error that is special cased. Const conErrDoCmdCancelled = 2501 Dim dbs As Database Dim 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 = CurrentDb() Set rst = dbs.OpenRecordset("Switchboard Items", dbOpenDynaset) rst.FindFirst "SwitchboardID=" & Me!SwitchboardID & " AND ItemNumber=" & intBtn ' If no item matches, report the error and exit the function. If (rst.NoMatch) Then MsgBox "读取 Switchboard Items 表时出错。" rst.Close dbs.Close Exit Function End If Select Case rst!Command ' Go to another switchboard. Case conCmdGotoSwitchboard Me.Filter = "ItemNumber = 0 AND SwitchboardID=" & rst!Argument ' Open a form in Add mode. Case conCmdOpenFormAdd DoCmd.OpenForm rst!Argument, , , , acAdd ' Open a form. Case conCmdOpenFormBrowse DoCmd.OpenForm rst!Argument ' Open a report. Case conCmdOpenReport DoCmd.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 Next Application.Run "ACWZMAIN.sbm_Entry" If (Err <> 0) Then MsgBox "命令不可用" On Error GoTo 0 ' Update the form. Me.Filter = "ItemNumber = 0 AND Argument = '默认' " Me.Caption = Nz(Me!ItemText, "") FillOptions ' Exit the application. Case conCmdExitApplication CloseCurrentDatabase ' Run a macro. Case conCmdRunMacro DoCmd.RunMacro rst!Argument ' Run code. Case conCmdRunCode Application.Run rst!Argument ' Any other command is unrecognized. Case Else MsgBox "未知选项" End Select ' Close the recordset and the database. rst.Close dbs.CloseHandleButtonClick_Exit: Exit FunctionHandleButtonClick_Err: ' If the action was cancelled by the user for ' some reason, don't display an error message. ' Instead, resume on the next line. If (Err = conErrDoCmdCancelled) Then Resume Next Else MsgBox "执行该命令时出错。", vbCritical Resume HandleButtonClick_Exit End IfEnd Function 26 / 28