yngc_frmdailyselect.frm

主要实现数据库的日志管理功能

Private strSelectCondition As String

'查询的值
Private strSelectValue As String

'结束时间
Private strEndTime As String

'计数器
Private i As Integer

'日志删除条件
Private strDelDailyCondition As String
Private strSql As String

'数据集
Private objOraDyn As OraDynaset
Private objResponse

'定义Excel的应用程序变量
Private appExcel As New Excel.Application
Private xlExcelBook As New Excel.Workbook

'输出数据时的SQL语句
Private strSqlPrint As String

Private m_clsServer As yngc_Server.yngc_clsServer

''定义ORADC的连接字符串
'Private m_strConnect As String
'
'
'Public Property Let ConnectStr(strConn As String)
'    m_strConnect = strConn
'End Property


Private Sub Form_Unload(Cancel As Integer)
    '释放引用的变量
   Set appExcel = Nothing
   Set xlExcelBook = Nothing
   
End Sub

Private Sub yngc_btnCancal_Click()
   Unload Me
End Sub


Private Sub yngc_btnPrintDaily_Click()
   On Error Resume Next
'建立OraDynaset对象
   Set objOraDyn = g_clsConnect.ObjectOraDb.CreateDynaset(strSqlPrint, 0&)
  '开始一个事务
   g_clsConnect.ObjectOraDb.BeginTrans
   Set appExcel = CreateObject("excel.application")
   appExcel.Visible = True
   Set xlExcelBook = appExcel.Workbooks.Open(App.Path & "\template\日志查询.xls")
   With xlExcelBook.Worksheets("sheet1")
      .Activate
       '初始化Excel表的标题，列
      .Range("C" + Trim(Str(1))).Value = Format(Now, "YYYY年")
      .Range("D" + Trim(Str(1))).Value = (Format(Now, "MM月DD日") & "日志查询")
      .Range("A" + Trim(Str(2))).Value = "用户姓名"
      .Range("B" + Trim(Str(2))).Value = "用户IP"
      .Range("C" + Trim(Str(2))).Value = "登陆时间"
      .Range("d" + Trim(Str(2))).Value = "用户操作"
      .Range("e" + Trim(Str(2))).Value = "退出时间"
      
      For i = 1 To objOraDyn.RecordCount
         .Range("A" + Trim(Str(i + 2))).Value = objOraDyn.Fields("LOG_USERID").Value
         .Range("B" + Trim(Str(i + 2))).Value = objOraDyn.Fields("LOG_IP").Value
         .Range("C" + Trim(Str(i + 2))).Value = objOraDyn.Fields("LOG_ENTERTIME").Value
         .Range("d" + Trim(Str(i + 2))).Value = objOraDyn.Fields("LOG_FUNCNAME").Value
         .Range("e" + Trim(Str(i + 2))).Value = objOraDyn.Fields("LOG_DEPARTTIME").Value
         objOraDyn.DbMoveNext
      Next i
   '结束事务
   g_clsConnect.ObjectOraDb.CommitTrans
   End With
   xlExcelBook.SaveAs (App.Path & "\报表\" & Format(Now, "YYYY年MM月DD日") & "日志查询.xls")
   Exit Sub
err:
   MsgBox "无法找到指定文件"
End Sub

Private Sub yngc_btnSelectDaily_Click()
    '根据查询条件生成查询语句
   If Trim(yngc_cboSeleceCondition.Text) = "登陆时间" Then
      strSql = "select log_userid as 用户姓名,log_ip as 登陆IP," _
                       & "log_entertime as 登陆时间 ,log_funcname as 用户操作 ,log_departtime as 退出时间 from yngc_sys_loglist where " & strSelectCondition & " >'" & strSelectValue & "' " & _
      "and " & strSelectCondition & " < '" & strEndTime & "'"
      strSqlPrint = "select log_userid ,log_ip ,log_entertime ,log_funcname,log_departtime from yngc_sys_loglist where " & strSelectCondition & " >'" & strSelectValue & "' " & _
      "and " & strSelectCondition & " < '" & strEndTime & "'"
   Else
      strSelectValue = yngc_txtSelectValue.Text
      strSql = "select log_userid as 用户姓名,log_ip as 登陆IP," _
                       & "log_entertime as 登陆时间 ,log_funcname as 用户操作 ,log_departtime as 退出时间 from yngc_sys_loglist where " & strSelectCondition & " ='" & strSelectValue & "'"
      strSqlPrint = "select log_userid ,log_ip ,log_entertime ,log_funcname,log_departtime from yngc_sys_loglist where " & strSelectCondition & " ='" & strSelectValue & "'"
   End If
    '设置ORADC控件的数据源
   ORADC1.RecordSource = strSql
   '控件刷新
   ORADC1.Refresh
   MSFlexGrid1.Refresh
   yngc_txtSelectValue.Text = ""
End Sub

'按照查询条件，有选择的删除日志
Private Sub yngc_btnSelectedDelDaily_Click()
   If Trim(yngc_cboSeleceCondition.Text) = "" And Trim(yngc_txtSelectValue.Text) = "" Then
      MsgBox "请输入要删除的日志的条件值！", vbInformation, "提示"
      Exit Sub
   End If
   '***********************************************************
   '根据查询条件，生成不同的SQL语句
   If Trim(yngc_cboSeleceCondition.Text) = "登陆时间" Then
      strSql = "delete from yngc_sys_loglist where " & strSelectCondition & ">'" & strSelectValue & "'" & _
                 "and " & strSelectCondition & " < '" & strEndTime & "'"
      strDelDailyCondition = "删除" & strSelectValue & "至" & strEndTime & "日志"
   Else
      strSelectValue = yngc_txtSelectValue.Text
      strSql = "delete from yngc_sys_loglist where " & strSelectCondition & " ='" & strSelectValue & "'"
      strDelDailyCondition = "删除" & strSelectCondition & "=" & strSelectValue & "日志"
   End If
   '***********************************************************
   
   objResponse = MsgBox("确定删除日志？", vbYesNo + vbInformation, "提示")
   If objResponse = vbYes Then
      g_clsConnect.ObjectOraDb.ExecuteSQL (strSql)
      g_clsConnect.ObjectOraDb.CommitTrans
      Call g_clsConnect.yngc_subUpdateUserOperation(g_clsConnect.ObjectOraDb, strDelDailyCondition)
   End If
    '控件刷新,显示删除后的数据
   ORADC1.Refresh
   yngc_txtSelectValue.Text = ""
End Sub

Private Sub yngc_cboSeleceCondition_Click()
   Call yngc_sbSetCondition
End Sub

Private Sub Form_Load()

   Set m_clsServer = New yngc_clsServer
   
   '连接字符串
   ORADC1.Connect = m_clsServer.SDEUser & "/" & m_clsServer.SDEPass
   
   '数据库名称
   ORADC1.DatabaseName = m_clsServer.OraSID
   
   Dim strTile As String
   
   '得到用户登录ID
   'Call clsConnect.yngc_subReadFile
   '修改列的宽度
   MSFlexGrid1.ColWidth(0) = 0
   MSFlexGrid1.ColWidth(1) = 1000
   MSFlexGrid1.ColWidth(2) = 1100
   MSFlexGrid1.ColWidth(3) = 1600
   MSFlexGrid1.ColWidth(4) = 1600
   MSFlexGrid1.ColWidth(5) = 1600
   strSql = "select log_userid as 用户姓名,log_ip as 登陆IP," _
                       & "log_entertime as 登陆时间 ,log_funcname as 用户操作 ,log_departtime as 退出时间 from yngc_sys_loglist"
    ORADC1.RecordSource = strSql
    
   strSqlPrint = "select log_userid ,log_ip ,log_entertime,log_funcname ,log_departtime from yngc_sys_loglist"
   
End Sub

Private Sub yngc_dtpStartDate_Change()
'登陆时间改变，查询条件的时间同时改变
    'strSelectValue = yngc_dtpStartDate.Value
    strSelectValue = Format(yngc_dtpStartDate.Value, "yyyy-mm-dd hh:mm:ss")
    
    
End Sub
'**************************************************************
'名称：     yngc_sbSetCondition()
'功能描述： 设置查询、删除日志的条件
'传入参数:  无
'返回参数： 无
'作者：     李建东
'创建日期   2005年11月16日
'**************************************************************
Private Sub yngc_sbSetCondition()
   '根据用户选择的查询条件设置查询语句的条件
   If Trim$(yngc_cboSeleceCondition.Text) = "用户姓名" Then
      strSelectCondition = "LOG_USERID"
      yngc_dtpStartDate.Visible = False
      yngc_dtpEndDate.Visible = False
      Label3.Visible = False
      Label4.Visible = False
      Label2.Visible = True
      Label1.Left = 480
      yngc_txtSelectValue.Visible = True
      yngc_cboSeleceCondition.Left = 1440
   End If
   If Trim(yngc_cboSeleceCondition.Text) = "登陆时间" Then
      strSelectCondition = "LOG_ENTERTIME"
      '显示日期列表
      yngc_dtpStartDate.Visible = True
      yngc_dtpEndDate.Visible = True
      Label3.Visible = True
      Label4.Visible = True
      Label2.Visible = False
      yngc_txtSelectValue.Visible = False
      Label1.Left = 80
      yngc_cboSeleceCondition.Left = 900
       '设置当前时间值
      yngc_dtpStartDate.Value = Format(Now, "yyyy-mm-dd")
      yngc_dtpEndDate.Value = Format(Now, "yyyy-mm-dd")
      '设置当前的查询时间值
      strSelectValue = Format(yngc_dtpStartDate.Value, "yyyy-mm-dd hh:mm:ss")
      strEndTime = Format(yngc_dtpEndDate.Value, "yyyy-mm-dd 23:59:59")
   End If
   If Trim(yngc_cboSeleceCondition.Text) = "登陆IP" Then
      strSelectCondition = "LOG_IP"
      yngc_dtpStartDate.Visible = False
      yngc_dtpEndDate.Visible = False
      Label3.Visible = False
      Label4.Visible = False
      Label2.Visible = True
      yngc_txtSelectValue.Visible = True
      Label1.Left = 480
      yngc_cboSeleceCondition.Left = 1440
   End If
End Sub


Private Sub yngc_dtpEndDate_Change()
   strEndTime = Format(yngc_dtpEndDate.Value, "yyyy-mm-dd 23:59:59")
End Sub