数据库课程设计网吧管理系统设计报告书.doc

设计题目网吧管理系统设计技术参数1 题目要达到的目标：完成系统的登陆、数据的插入删除修改统计等基本操作。2 数据库：采用关系数据库ACCESS2003/SQLSERVER2000。3 所使用的语言、工具:VB6.0+ACCESS2003+WINDOWSXP。4 所采用的技术、系统结构:ADO数据库联接技术,系统结构为C/S结构。设计要求1 上机前预先把所有的源程序编写好。2 上机时不得迟到，不得缺席，爱护设备，认真调试程序。3 仔细查阅相关资料，认真完成思考题。4 按要求书写设计任务书，并按要求按版打印，不得雷同。工作量一周工作计划周一听取教师布置设计的任务及要求。周二学生查阅相关资料，进行数据库设计。周三至周五：详细设计与界面设计。周一至周四程序设计与上机，调试源程序。周五书写课程设计任务书。参考资料数据库系统概论数据库系统概论实验指导书自编数据库系统概论课程设计大纲等自编指导教师评语该生在数据库系统概论课程设计期间：上机时不迟到，不缺席，爱护设备，认真调试程序。仔细查阅相关资料，认真完成作业。听从指导教师安排。在设计与实现过程中，能使设计的系统达到预期设计要求，功能合理，流程清楚，语句条理通顺。按要求书写设计任务书，其论文格式规范，图表清晰合理，资料详实。系统界面友好，程序运行基本正常，功能完备。说明：此表一式四份，学生、指导教师、教研室、系部各一份。可加附页。2008年 7 月 4 目录第一章 概述2第二章 问题定义2第三章 需求分析4第四章 数据库设计7第五章 界面设计9第六章 代码11第一章 概述随着上个世纪九十年代计算机网络的高速发展，人类进入到了网络化、信息化的时代。而网络的发展带动了一种新兴产业网吧行业。从上世纪九十年代至今，网吧经历了前所未有的发展。从小规模到大规模，从单一经营到连锁经营，从简单提供上网服务演变到休息、娱乐场所。这就带来了新的问题网吧管理。目前，网吧的人工管理已经不能适应网吧的高速发展，成为阻碍网吧发展的桎梏。因此开发一套完善的网吧管理系统成为网吧发展的首要任务。本系统主要负责服务器子系统的设计。采用功能强大的Visual Basic6.0作为开发工具，Microsoft Access作为数据库平台。本系统采用C/S结构。它将一个应用系统分成两大部分，由多台计算机分别执行，协同完成整个系统的应用，从而达到系统中软、硬件资源最大限度的利用。因此，对于一个相对专用、独立的系统，它是个较好的系统建立方法。因此，本设计采用这一模式。本系统从操作简便、界面友好、灵活、实用、安全的要求出发，实现较为简单。本系统设计功能较为齐全，适合于中小型网吧的管理。第二章 问题定义网吧管理人员：系统的录入人员，主要指管理各种档案的工作人员，需要通过密码才能进入。他们拥有对系统数据的录入、修改、查询、删除等操作权限。但不可以更改系统密码。上机人员：权限最低的登入人员，当上网时间到了之后就会被提示。第三章 需求分析一、主要功能： 完成网吧管理人员对上机人员的调整，对机器的分配，以及纪录用户的上机时间，并对整个网吧的系统进行管理。因此，本系统应该主要完成和实现如下功能：初始化系统数据；录入登陆人员的基本信息数据；上机时间，下机时间的基本设定；查询机器的剩余时间；计算上机的费用；人员信息： 在该项内容中将包括的数据库项有用户名，用户密码，密码确认，开户时间，开户金额，开户总机时，剩余时间，上机次数，备注。会员信息：用户名，用户密码，密码确认，开户时间，开户金额，折扣，总时间，上机次数，备注。机器编号：ID,IP。二、数据流图（DFD）：三、数据字典（DD）: ClientIP=ID+IPHistory=ID+用户名+机器号+开始时间+结束时间+应付费Member=用户名+用户密码+密码确认+开户时间+开户金额+折扣+总时间+上机次数+备注四、程序流程图：不限时限时系统主界面时间类型时间设置系统设置调用系统菜单退出系统五、功能结构图：网吧管理系统登陆人员信息上机时间设定费用汇总下机时间查询退出 网吧管理系统模块时间的基本设定 下机时间上机时间总时间时间设定模块第四章 数据库设计二、逻辑设计：下面列出几个主要的数据库表设计：4.1 ClientIP表：序号中文名字段名类型备注0ID3字符型 主键1IP10文本archar4.2history表：序号中文名字段名类型备注0ID2字符型主键1userID（10，2）文本型 2pcid 2数字3starttime日期时间4endtime日期时间5pay 2货币4.3member表：序号中文名字段名类型备注0userID10文本符型主键1Pwd10文本符型2ChkPwd10文本类型3DataTime日期时间4Balance2数字5Discount2数字6Totaltime2数字7CountNum2数字9Memo备注4.3member1表：序号中文名字段名类型备注0userID10文本符型主键1Pwd10文本符型2ChkPwd10文本类型3DataTime日期时间4Balance2数字5Discount2数字6Totaltime2数字7CountNum2数字9Memo备注4.4online表：序号中文名字段名类型备注0ID10字符型 主键1ClientID（10，2）字符型archar2LoginTime时间日期3TimeOut 10字符型4.4online表：序号中文名字段名类型备注0ID10字符型 主键1Mstate（是，否）判断2Jstate文本3stime日期4Endtime日期5Time 10数字6Money货币7Ltime10数字8Userid文本9uprice货币三、物理设计写索引是如何设计的：写索引是如何设计的：镞索引是行的物理顺序和索引的顺序是一致的。页级，低层等索引的各个级别上都包含实际的数据页。一个表只能是有一个镞索引。由于update,delete语句要求相对多一些的读操作，因此镞索引常常能加速这样的操作。在至少有一个索引的表中，你应该有一个镞索引第五章 界面设计一、主界面单击主界面上的各个按钮，可以进入相应的界面中。二、系统设置对参数进行设置功能介绍，相关控件；三、查询上机时间等信息功能介绍，相关控件；第六章 代码与注释一、窗体1代码Option ExplicitPrivate Sub cmdexit_Click()Unload MeEnd SubPrivate Sub cmdcounter_Click() Dim cn As ADODB.Connection Dim i As Integer Dim rst As ADODB.Recordset Dim strsql As String Set cn = New Connection Set rst = New ADODB.Recordset cn.Open concn 'Íù history ±íÖÐÌí¼Ó¼Ç¼ frmManager.Adodc1.Recordset!endtime = Now() txtmoney.Text = frmManager.Adodc1.Recordset!Time * frmManager.Adodc1.Recordset!uprice '×¢Òâʱ¼ä×ֶεÄÌí¼Ó·½·¨£¬# strsql = "insert into history (pcid,starttime,endtime,pay) values (" & frmManager.Adodc1.Recordset!Mid & ",#" & frmManager.Adodc1.Recordset!stime & "#,#" & frmManager.Adodc1.Recordset!endtime & "#," & txtmoney.Text & ")" Debug.Print strsql cn.Execute strsql ' Êշѳɹ¦£¬Êý¾Ý³õʼ»¯ With frmManager.Adodc1 .Recordset!mstate = 0 .Recordset!stime = 0 .Recordset!jstate = "δ¼Æ·Ñ" .Recordset!Money = 0 .Recordset!endtime = 0 .Recordset!Time = 0 .Recordset!lTime = 0 .Recordset!userID = "" On Error GoTo error_proc .Recordset.Save End With frmManager.Refresh 'ʹÊý¾Ýͬ²½ÏÔʾ MsgBox "Êշѳɹ¦" Unload Me Exit Sub error_proc: MsgBox Err.Description, vbCritical, "ÖØÊÔ" Unload MeEnd SubPrivate Sub cmdvipexit_Click()Unload MeEnd SubPrivate Sub cmdvipcounter_Click() Dim Gdiscount As Double Dim cn As ADODB.Connection Dim i As Integer Dim rst As ADODB.Recordset Dim strsql As String Dim uprice As Double Set cn = New Connection Set rst = New ADODB.Recordset cn.Open concn frmManager.Adodc1.Recordset!endtime = Now() Gdiscount = frmMember.datPrimaryRS.Recordset!DISCOUNT '»áÔ±µÄÕË»§ÉϵĽð¶î txtvipmoney.Text = frmManager.Adodc1.Recordset!Time * frmManager.Adodc1.Recordset!uprice * Gdiscount strsql = "insert into history (pcid,starttime,endtime,pay,userid) values (" & frmManager.Adodc1.Recordset!Mid & ",#" & frmManager.Adodc1.Recordset!stime & "#,#" & frmManager.Adodc1.Recordset!endtime & "#," & txtvipmoney.Text & ",""" & frmManager.Adodc1.Recordset!userID & """)" cn.Execute strsql '¸üÐÂMEMBER±í strsql = "update member set countNum=countNum+1,TOTALTIME=TOTALTIME+" & frmManager.Adodc1.Recordset!Time & " , balance=balance-" & CSng(txtvipmoney.Text) & " WHERE userid=""" & frmManager.Adodc1.Recordset!userID & """" cn.Execute strsql '²é³öÓû§Óà¶î strsql = "select balance from member where userid=""" & frmManager.Adodc1.Recordset!userID & """" rst.Open strsql, cn, adOpenDynamic, adLockOptimistic If rst.BOF And rst.EOF Then MsgBox "System Error5" Exit Sub End If uprice = rst("balance") If uprice < 0 Then MsgBox "Óû§" & frmManager.Adodc1.Recordset!userID & "ÒѾ­Ç··Ñ" & (-uprice) & "Ôª" End If strsql = "update pc set mstate=0,stime=0,jstate=""δ¼Æ·Ñ"",money=0,endtime=0,ltime=0,time=0,userid="""" where mid=" & frmManager.Adodc1.Recordset!Mid On Error GoTo error_proc cn.Execute strsql rst.Close Set rst = Nothing cn.Close Set cn = Nothing frmManager.Refresh 'ʹÊý¾Ýͬ²½ÏÔʾ MsgBox "»áÔ±Êշѳɹ¦" Unload Me Exit Suberror_proc: MsgBox Err.Description, vbCritical, "ÖØÊÔ" Unload MeEnd SubPrivate Sub Form_Load() '´ÓÊý¾Ý¿âÖлñÈ¡ÉÏÍøʱ¼äIf IsNull(frmManager.Adodc1.Recordset!Time) = False Then '×¢Òâ²»ÄÜÓÃÕâÖÖÐÎʽ¸³Öµ txttime.Text=txtviptime.Text = frmManager.Adodc1.Recordset!Time & "" 'ÏÔʾÉÏÍøÓ¦¸¶½ð¶î If (frmManager.Adodc1.Recordset!userID = "") Or (IsNull(frmManager.Adodc1.Recordset!userID) Then frmCounter.SSTab1.Tab = 0 txttime.Text = frmManager.Adodc1.Recordset!Time & "" txtmoney.Text = frmManager.Adodc1.Recordset!Time * frmManager.Adodc1.Recordset!uprice & "" frmCounter.cmdvipcounter.Visible = False Else frmCounter.SSTab1.Tab = 1 txtviptime.Text = frmManager.Adodc1.Recordset!Time & "" txtvipmoney.Text = frmManager.Adodc1.Recordset!Time * frmManager.Adodc1.Recordset!uprice & "" frmCounter.cmdcounter.Visible = False End IfElse MsgBox "ÉÏÍøʱ¼äΪÁã"End If End Sub二、窗体2代码Option ExplicitPrivate Sub Form_Load()datPrimaryRS.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "mechanicID.mdb;Persist Security Info=False"datPrimaryRS.CommandType = adCmdTabledatPrimaryRS.RecordSource = "history"datPrimaryRS.RefreshEnd SubPrivate Sub Form_Resize() On Error Resume Next 'µ±´°Ìåµ÷Õûʱ»áµ÷ÕûÍø¸ñ grdDataGrid.Height = Me.ScaleHeight - datPrimaryRS.Height - 30 - picButtons.HeightEnd SubPrivate Sub Form_Unload(Cancel As Integer) Screen.MousePointer = vbDefaultEnd SubPrivate Sub datPrimaryRS_Error(ByVal ErrorNumber As Long, Description As String, ByVal Scode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, fCancelDisplay As Boolean) '´íÎó´¦Àí³ÌÐò´úÂëÖÃÓÚ´Ë´¦ 'ÏëÒªºöÂÔ´íÎó£¬×¢Ê͵ôÏÂÒ»ÐÐ 'ÏëÒª²¶»ñËüÃÇ£¬ÔÚ´ËÌí¼Ó´úÂëÒÔ´¦ÀíËüÃÇ MsgBox "Data error event hit err:" & DescriptionEnd SubPrivate Sub cmdDelete_Click() On Error GoTo DeleteErr With datPrimaryRS.Recordset .Delete .MoveNext If .EOF Then .MoveLast End With Exit SubDeleteErr: MsgBox Err.DescriptionEnd SubPrivate Sub cmdClose_Click() Unload MeEnd Sub三、窗体3代码Option ExplicitPrivate Sub cmdok_Click() If txtHour.Text = "" Then MsgBox "ÄãµÄʱ¼äÊäÈë²»ÄÜΪ¿Õ,ÇëÖØÊÔ!", vbCritical txtHour.Text = "" txtHour.SetFocus Else frmManager.Adodc1.Recordset!mstate = 1 frmManager.Adodc1.Recordset!lTime = txtHour.Text frmManager.Adodc1.Recordset!stime = Now() frmManager.Adodc1.Recordset!jstate = "¼Æ·ÑÖÐ" frmManager.Adodc1.Recordset.Save Unload Me End IfEnd SubPrivate Sub cmdcancel_Click() Unload MeEnd SubPrivate Sub Form_Load()' Dim cn As ADODB.Connection' Dim rst As ADODB.Recordset' Set cn = New Connection' Set rst = New ADODB.Recordset' cn.Open concn End Sub四、窗体4代码Option ExplicitPublic blnReceived As BooleanPublic blnOnlineOK As Boolean 'ÅжÏÊÇ·ñÔÚÏßPublic strData As StringPrivate Sub DataGrid1_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single) '°´ÏÂÓÒ¼ü£¬µ¯³ö²Ëµ¥ If Button = 2 Then PopupMenu mnupopup End SubPrivate Sub Form_Activate() 'Ò»¼¤»î¾ÍÕ¼ÆÁÄ»µÄÖмä datagrid1.Left = (Me.ScaleWidth - datagrid1.Width) / 2 End SubPrivate Sub Form_Load() 'Êý¾ÝÁ¬½Ó³õʼ»¯ Adodc1.ConnectionString = concn Adodc1.CommandType = adCmdTable Adodc1.RecordSource = "pc" Adodc1.Refresh Call DisableX(Me) '½ûÓùرմ°Ìå Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim sql As String Set cn = New ADODB.Connection Set rs = New ADODB.Recordset cn.Open concn sql = "select id,ip from clientip" rs.Open sql, cn, adOpenKeyset rs.MoveNext rs.MoveFirst glngClientNum = rs.RecordCount ReDim gstrClientIP(glngClientNum - 1) While Not rs.EOF gstrClientIP(rs("ID") - 1) = rs("IP") rs.MoveNext Wend rs.Close Set rs = Nothing cn.Close Set cn = Nothing With Winsock1 .Protocol = sckUDPProtocol .RemoteHost = gstrClientIP(glngClientNum - 1) .RemotePort = 4001 .LocalPort = 4002 End With blnReceived = False blnOnlineOK = False Timer3.Enabled = False Exit Sub Label1.Caption = "Ç뵽ϵͳÉèÖÃ-¡·Ìí¼ÓÐ޸Ŀͻ§¶Ëip,ÖØÐÂÐ޸ģ¬·Ç·¨µÄ¼ÆËã»ú±àºÅ" Label1.Visible = TrueEnd SubPrivate Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)If Button = 2 Then PopupMenu mnupopupEnd SubPrivate Sub Form_Resize() datagrid1.Left = (Me.ScaleWidth - datagrid1.Width) / 2End SubPrivate Sub Form_Unload(Cancel As Integer)Dim Frm As Form For Each Frm In Forms Unload Frm Set Frm = Nothing Next FrmEnd SubPrivate Sub mnuExit_Click()EndEnd SubPrivate Sub mnuLimittime_Click() frmLimittime.Show 1 'µ÷ÓÃÏÞʱ´°ÌåEnd SubPrivate Sub mnuLock_Click() If MsgBox("ÊÇ·ñÒªÍ˳ö²¢Í£Ö¹¼Æ·Ñ£¿", vbOKCancel) = vbOK Then frmCounter.Show 1 'µ÷ÓÃfrmCounter ´°Ìå End IfEnd SubPrivate Sub mnusystem_Click() frmSystem.Show vbModalEnd SubPrivate Sub mnuTiming_Click() frmTiming.Show 1 'µ÷ÓüÆʱ´°ÌåEnd SubPrivate Sub Timer1_Timer() Dim cn As ADODB.Connection Dim rst As ADODB.Recordset Dim strsql As String Dim i As Integer Dim A As String A = Adodc1.Recordset!jstate = "¼Æ·ÑÖÐ" Set cn = New Connection Set rst = New ADODB.Recordset cn.Open concn strsql = "update pc set time=format(DateDiff(""d"",stime,now()*24+(hour(now()-hour(stime) +(Minute(now()-Minute(stime)/60), ""#,#0.000""),money=time*uprice where jstate=""¼Æ·ÑÖÐ""" cn.Execute strsql i = Adodc1.Recordset.AbsolutePosition Adodc1.Refresh rst.CursorLocation = adUseClient strsql = "select * from pc where Time>=lTime and ltime<>0 and jstate=""¼Æ·ÑÖÐ""" rst.Open strsql, cn, adOpenDynamic, adLockOptimistic If rst.RecordCount > 0 Then rst.MoveFirst While Not rst.EOF rst!jstate = "δ¼Æ·Ñ" rst.Update MsgBox ("µÚ" & rst!Mid & "ºÅ»úÆ÷ÏÞʱÒѵ½") rst.MoveNext Wend End If Adodc1.Recordset.AbsolutePosition = iEnd SubPrivate Sub Timer3_Timer() '½«ËùÓеÄÔÚÏß»úÆ÷µÄ³¬Ê±¼ÆÊý¼ÓÒ» Dim cn As ADODB.Connection Dim rst As ADODB.Recordset Dim strsql As String Set cn = New ADODB.Connection Set rst = New ADODB.Recordset cn.Open concn strsql = "select ClientID from online" rst.Open strsql, cn If rst.BOF And rst.EOF Then Exit Sub End If While Not rst.EOF AddTimeOut rst("ClientID") rst.MoveNext WendEnd SubPrivate Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button) Dim PC_NumberA As Integer Dim cn As ADODB.Connection Dim i As Integer Dim rst As ADODB.Recordset Set cn = New Connection Set rst = New ADODB.Recordset cn.Open concn frmManager.datagrid1.Row = frmManager.datagrid1.Row frmManager.datagrid1.Col = 0 PC_Number1 = frmManager.datagrid1.Text PC_NumberA = CInt(PC_Number1) Select Case Button.Key Case "cmdNumberID" Case "cmdTiming" frmTiming.Show Case "cmdLimittime" frmLimittime.Show Case "cmdLock" ForceScreenLock PC_NumberA PC_NumberA = 0 Case "cmdPoweroff" ForceShutDown PC_NumberA Case "cmdReboot" ForceReboot PC_NumberA Case "cmdReleaseLock" ReleaseLock PC_NumberA