frmdeviceoperation.frm

网路设备资产管理系统

    Set mMSFlexGrid1 = New cFlexGridBinder
    With mMSFlexGrid1
        Set .Grid = MSFlexGrid1
        
        .Editable = True
        
       If mode = "add" Then
        .EditOnMouse = emeClick
        
        .BindColumn 2, txtEdit

        .BindColumn 10, txtEdit
       ElseIf mode = "view" Then
        .EditOnMouse = emeNone
        .FullRowSelect = True
       End If

        
        
    End With

End Sub



Private Sub mMSFlexGrid1_ValidateEdit(ByVal Row As Long, ByVal col As Long, NewData As Variant, Cancel As Boolean)
    '#############################################################################################################################
    'This Event is fired to validate an Edit before it is accepted. Set Cancel to True
    'to reject the Edit
    '#############################################################################################################################
  
    
        If col = COL_NETCENTER_ID Then
        
         ''''''''''''''''''''''''''''''''''
          If Len(NewData) <> 9 Then
             MsgBox "中心编码必须为9位!", vbOKOnly + vbExclamation, "警告"
             Cancel = True
            Exit Sub
          Else
          
         
          
            Set mrc = ExecuteSQL("select * from tbl_device where netcenter_id = '" & Trim(NewData) & "'", MsgText, HasError)
    
            If mrc.EOF = True Then
                MsgBox "设备库中没有该中心编码的设备!", vbOKOnly + vbExclamation, "警告"
                Cancel = True
                Exit Sub
            End If
          
          End If
            
            ''''''''''''''''''''''''''''''

        
        ElseIf col = 10 Then
        
          If Trim(NewData) = "" Then
             MsgBox "SN号不能为空!", vbOKOnly + vbExclamation, "警告"
             Cancel = True
            Exit Sub
          Else
          
            Set mrc = ExecuteSQL("select * from tbl_device where sn = '" & Trim(NewData) & "'", MsgText, HasError)
    
            If mrc.EOF = True Then
                MsgBox "设备库中没有该SN号的设备!", vbOKOnly + vbExclamation, "警告"
                Cancel = True
                Exit Sub
            End If
            
          End If
          
        End If
        
        
 
    
    Cancel = False
End Sub

Private Sub mMSFlexGrid1_AfterEdit(ByVal Row As Long, ByVal col As Long)
    '#############################################################################################################################
    'This Event is fired after a ValidateEdit Event is accepted
    '#############################################################################################################################

    With MSFlexGrid1
        If col = COL_NETCENTER_ID Then
        

          
          ''''''''''''''''''''''''''''''''''

    
            '到此为止中心编号经认证，下面首先自动调出其厂商等属性

            Set mrc = ExecuteSQL("select * from tbl_device where netcenter_id = '" & Trim(.TextMatrix(Row, 2)) & "'", MsgText, HasError)
    
            If mrc.EOF = False Then
            .TextMatrix(Row, 0) = mrc.Fields("device_id")
            '.TextMatrix(Row, 1) = mrc.Fields("item_id")
            '.TextMatrix(Row, 2) = mrc.Fields("netcenter_id")
            .TextMatrix(Row, 3) = mrc.Fields("property_id")
            .TextMatrix(Row, 4) = mrc.Fields("model_id")
            .TextMatrix(Row, 5) = mrc.Fields("model_name")
            .TextMatrix(Row, 6) = mrc.Fields("manufacturer_id")
            .TextMatrix(Row, 7) = mrc.Fields("manufacturer_name")
            .TextMatrix(Row, 8) = mrc.Fields("type_id")
            .TextMatrix(Row, 9) = mrc.Fields("type_name")
            .TextMatrix(Row, 10) = mrc.Fields("sn")
            .TextMatrix(Row, 11) = mrc.Fields("state")
            End If

            
 
   
            ''''''''''''''''''''''''''''''

          
        ElseIf col = 10 Then
        

        
        End If
        
    End With
End Sub

Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)
    On Error Resume Next
    Select Case Button.key
    
        Case "保存"
    '''''''''''''''''''''''''''''''''''''''''
If Trim(txtOperDesc.Text) = "" Then
         MsgBox "必须输入操作描述!", vbOKOnly + vbExclamation, "警告"
        txtDesc.SetFocus
        Exit Sub
End If

If Trim(cboOperType.Text) = "" Then
         MsgBox "必须选择操作类型", vbOKOnly + vbExclamation, "警告"
        cboOperType.SetFocus
        Exit Sub
End If

If Trim(txtApplicant.Text) = "" Then
         MsgBox "必须输入申请人", vbOKOnly + vbExclamation, "警告"
        txtApplicant.SetFocus
        Exit Sub
End If

If Trim(txtTransactor.Text) = "" Then
         MsgBox "必须输入经办人", vbOKOnly + vbExclamation, "警告"
        txtTransactor.SetFocus
        Exit Sub
End If


If MSFlexGrid1.Rows = 1 Then
         MsgBox "必须添加待操作的设备", vbOKOnly + vbExclamation, "警告"
        Exit Sub
End If

'对所有的items进行有校检查

For i = 1 To MSFlexGrid1.Rows - 1

    If Trim(MSFlexGrid1.TextMatrix(i, 0)) = "" Then
         MsgBox "第" & MSFlexGrid1.TextMatrix(i, 1) & "行的设备编号不在库中", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    Else

            Set mrc = ExecuteSQL("select * from tbl_device where device_id = '" & Trim(MSFlexGrid1.TextMatrix(i, 0)) & "'", MsgText, HasError)
    
            If mrc.EOF = False Then
                
                       Select Case cboOperType.Text
        
                       Case "设备预领"
                         If Trim(mrc.Fields("state")) <> "在库" Then
                              MsgBox "第" & MSFlexGrid1.TextMatrix(i, 1) & "行的设备状态必须是:在库", vbOKOnly + vbExclamation, "警告"
                              Exit Sub
                         End If
                       Case "设备退库"
                         If Trim(mrc.Fields("state")) <> "上线" And Trim(mrc.Fields("state")) <> "预领" Then
                              MsgBox "第" & MSFlexGrid1.TextMatrix(i, 1) & "行的设备状态必须是:上线 或 预领", vbOKOnly + vbExclamation, "警告"
                              Exit Sub
                         End If
                       Case "故障退库"
                         If Trim(mrc.Fields("state")) <> "上线" And Trim(mrc.Fields("state")) <> "预领" Then
                              MsgBox "第" & MSFlexGrid1.TextMatrix(i, 1) & "行的设备状态必须是:上线 或 预领", vbOKOnly + vbExclamation, "警告"
                              Exit Sub
                         End If
                       Case "设备送修"
                         If Trim(mrc.Fields("state")) <> "故障" Then
                              MsgBox "第" & MSFlexGrid1.TextMatrix(i, 1) & "行的设备状态必须是:故障", vbOKOnly + vbExclamation, "警告"
                              Exit Sub
                         End If
                       Case "设备修返"
                         If Trim(mrc.Fields("state")) <> "送修" Then
                              MsgBox "第" & MSFlexGrid1.TextMatrix(i, 1) & "行的设备状态必须是:送修", vbOKOnly + vbExclamation, "警告"
                              Exit Sub
                         End If
                       Case "设备借出"
                         If Trim(mrc.Fields("state")) <> "在库" Then
                              MsgBox "第" & MSFlexGrid1.TextMatrix(i, 1) & "行的设备状态必须是:在库", vbOKOnly + vbExclamation, "警告"
                              Exit Sub
                         End If
                       Case "设备归还"
                         If Trim(mrc.Fields("state")) <> "借出" Then
                              MsgBox "第" & MSFlexGrid1.TextMatrix(i, 1) & "行的设备状态必须是:在库", vbOKOnly + vbExclamation, "警告"
                              Exit Sub
                         End If
                       Case "设备报废"
                         If Trim(mrc.Fields("state")) <> "在库" And Trim(mrc.Fields("state")) <> "故障" Then
                              MsgBox "第" & MSFlexGrid1.TextMatrix(i, 1) & "行的设备状态必须是:在库 或 故障", vbOKOnly + vbExclamation, "警告"
                              Exit Sub
                         End If
                    End Select
            End If
    
    End If

   
Next


'''''''''''''''''''''''''''''
  '下面开始保存记录
  On Error GoTo ErrorHandler
  BeginTrans
   
   '1 保存入库单
    Set mrc = ExecuteSQL("select top 1 oper_id from tbl_device_oper order by oper_id desc", MsgText, HasError)
    
    If mrc.EOF = False Then
         oldid = Val(mrc.Fields("oper_id"))
         oldid = oldid + 1
         snewid = Trim(str(oldid))
         If Len(snewid) = 1 Then snewid = "0000000" & snewid
         If Len(snewid) = 2 Then snewid = "000000" & snewid
         If Len(snewid) = 3 Then snewid = "00000" & snewid
         If Len(snewid) = 4 Then snewid = "0000" & snewid
         If Len(snewid) = 5 Then snewid = "000" & snewid
         If Len(snewid) = 6 Then snewid = "00" & snewid
         If Len(snewid) = 7 Then snewid = "0" & snewid
        newoper_id = snewid
    Else
        newoper_id = "00000001"
    End If
    
    txtSQL = "insert into tbl_device_oper values('"
   txtSQL = txtSQL & newoper_id & "','"
   txtSQL = txtSQL & Trim(txtOperDesc) & "','"
   txtSQL = txtSQL & Trim(ymdOperrq.FormatDate) & "','"
   txtSQL = txtSQL & Trim(cboOperType.Text) & "','"
   txtSQL = txtSQL & Trim(txtApplicant) & "','"
   txtSQL = txtSQL & Trim(txtTransactor) & "','"
   txtSQL = txtSQL & Trim(txtMemo) & "'"
   txtSQL = txtSQL & ")"
    
    Set mrc = ExecuteSQL(txtSQL, MsgText, HasError)
    
    
    
For i = 1 To MSFlexGrid1.Rows - 1

   '1 修改设备状态
     State = ""
     ext_query = ""
       Select Case cboOperType.Text
        
          Case "设备预领"
            State = "预领"
          Case "设备退库"
            ext_query = ext_query & " area_id=0, "
            ext_query = ext_query & " area_name='', "
            ext_query = ext_query & " building_id=0, "
            ext_query = ext_query & " building_name='', "
            ext_query = ext_query & " room_id=0, "
            ext_query = ext_query & " room_name='', "
            ext_query = ext_query & " ip='', "
            State = "在库"
          Case "故障退库"
            ext_query = ext_query & " area_id=0, "
            ext_query = ext_query & " area_name='', "
            ext_query = ext_query & " building_id=0, "
            ext_query = ext_query & " building_name='', "
            ext_query = ext_query & " room_id=0, "
            ext_query = ext_query & " room_name='', "
            ext_query = ext_query & " ip='', "
            State = "故障"
          Case "设备送修"
            State = "送修"
          Case "设备修返"
            State = "在库"
          Case "设备借出"
            State = "借出"
          Case "设备归还"
            State = "在库"
          Case "设备报废"
            State = "报废"
       End Select
   
   
    txtSQL = "update tbl_device set "
    txtSQL = txtSQL & ext_query
    txtSQL = txtSQL & " state=" & Quote(State)
    txtSQL = txtSQL & " where device_id =" & Quote(MSFlexGrid1.TextMatrix(i, 0))
    
    Set mrc = ExecuteSQL(txtSQL, MsgText, HasError)

   '2 保存设备日志
    Set mrc = ExecuteSQL("select top 1 log_id from tbl_device_log order by log_id desc", MsgText, HasError)
    
    If mrc.EOF = False Then
        newlog_id = mrc.Fields("log_id") + 1
    Else
        newlog_id = 1
    End If
    
    txtSQL = "insert into tbl_device_log values('"
   txtSQL = txtSQL & newlog_id & "','"
   txtSQL = txtSQL & MSFlexGrid1.TextMatrix(i, 0) & "','"
   txtSQL = txtSQL & Trim(cboOperType.Text) & " 操作单号:" & newoper_id & "','"
   txtSQL = txtSQL & Trim(txtApplicant) & "','"
   txtSQL = txtSQL & Trim(txtTransactor) & "','"
   txtSQL = txtSQL & Trim(ymdOperrq.FormatDate) & "'"
   txtSQL = txtSQL & ")"
    
    Set mrc = ExecuteSQL(txtSQL, MsgText, HasError)

   '3 保存items
     txtSQL = "insert into tbl_device_oper_items values('"
   txtSQL = txtSQL & newoper_id & "','"
   txtSQL = txtSQL & i & "','"
   txtSQL = txtSQL & MSFlexGrid1.TextMatrix(i, 0) & "','"
   txtSQL = txtSQL & Trim(MSFlexGrid1.TextMatrix(i, 2)) & "','"
   txtSQL = txtSQL & Trim(MSFlexGrid1.TextMatrix(i, 3)) & "','"
   txtSQL = txtSQL & Trim(MSFlexGrid1.TextMatrix(i, 4)) & "','"
   txtSQL = txtSQL & Trim(MSFlexGrid1.TextMatrix(i, 5)) & "','"
   txtSQL = txtSQL & Trim(MSFlexGrid1.TextMatrix(i, 6)) & "','"
   txtSQL = txtSQL & Trim(MSFlexGrid1.TextMatrix(i, 7)) & "','"
   txtSQL = txtSQL & Trim(MSFlexGrid1.TextMatrix(i, 8)) & "','"
   txtSQL = txtSQL & Trim(MSFlexGrid1.TextMatrix(i, 9)) & "','"
   txtSQL = txtSQL & Trim(MSFlexGrid1.TextMatrix(i, 10)) & "'"
   txtSQL = txtSQL & ")"
    
    Set mrc = ExecuteSQL(txtSQL, MsgText, HasError)
  
 Next
 
  
  CommitTrans

ErrorHandler:

'Handle Error
If Err.Number <> 0 Or HasError Then
  RollbackTrans
  If vbOK = MsgBox("保存过程中发生错误，您是否要查看错误的详细信息？", vbOKCancel + vbExclamation + vbDefaultButton2, "警告") Then
     FrmError.error = Err.Description
     FrmError.sql = txtSQL
     FrmError.msg = MsgText
     FrmError.Show
  End If
Else
  
  FrmDeviceOperationList.Grid_Fill
  
  Unload Me
  
End If
'''''''''''''''''''''''''''''''''''''''
    
        Case "添加设备"
        
        
        
        MSFlexGrid1.AddItem vbTab _
        & MSFlexGrid1.Rows & vbTab _
        & "" & vbTab _
        & "" & vbTab _
        & "" & vbTab _
        & "" & vbTab _
        & "" & vbTab _
        & "" & vbTab _
        & "" & vbTab _
        & "" & vbTab _
        & ""
            
        Case "删除设备"

        If MSFlexGrid1.Rows > 2 Then
          
          MSFlexGrid1.RemoveItem (MSFlexGrid1.Row)


          For i = 1 To MSFlexGrid1.Rows - 1

            MSFlexGrid1.TextMatrix(i, 1) = i

          Next
        
        ElseIf MSFlexGrid1.Rows = 2 Then
           MSFlexGrid1.Rows = 1
        End If
            
        Case "关闭"
          Unload Me
          
    End Select
    
End Sub