frmdevicesetup.frm

网路设备资产管理系统

        .BindColumn 12, cboBuilding
        .BindColumn 14, cboRoom
        
        .BindColumn 15, 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 "必须选择校区!", vbOKOnly + vbExclamation, "警告"
             Cancel = True
            Exit Sub
          End If
        
        ElseIf col = 12 Then
          If Trim(NewData) = "" Then
             MsgBox "必须选择楼栋!", vbOKOnly + vbExclamation, "警告"
             Cancel = True
            Exit Sub
          End If
        
        ElseIf col = 14 Then
          If Trim(NewData) = "" Then
             MsgBox "必须选择房间!", vbOKOnly + vbExclamation, "警告"
             Cancel = True
            Exit Sub
          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("model_id")
            .TextMatrix(Row, 4) = mrc.Fields("model_name")
            .TextMatrix(Row, 5) = mrc.Fields("manufacturer_id")
            .TextMatrix(Row, 6) = mrc.Fields("manufacturer_name")
            .TextMatrix(Row, 7) = mrc.Fields("type_id")
            .TextMatrix(Row, 8) = mrc.Fields("type_name")



            .TextMatrix(Row, 16) = mrc.Fields("state")
            End If

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

          
        ElseIf col = 10 Then
        
          Set mrc = ExecuteSQL("select * from tbl_area where area_name = '" & .TextMatrix(Row, 10) & "'", MsgText, HasError)
    
          If mrc.EOF = False Then
             .TextMatrix(Row, 9) = mrc.Fields("area_id")
          End If
          
          cboBuilding.Clear
          Set rst = ExecuteSQL("select * from tbl_building where area_id = " & MSFlexGrid1.TextMatrix(Row, 9), MsgText, HasError)
    
          Do While Not rst.EOF
             cboBuilding.AddItem rst.Fields("building_name")
             rst.MoveNext
          Loop
          
        ElseIf col = 12 Then
        
          Set mrc = ExecuteSQL("select * from tbl_building where building_name = '" & .TextMatrix(Row, 12) & "'", MsgText, HasError)
    
          If mrc.EOF = False Then
             .TextMatrix(Row, 11) = mrc.Fields("building_id")
          End If
          
          cboRoom.Clear
          Set rst = ExecuteSQL("select * from tbl_room where building_id = " & MSFlexGrid1.TextMatrix(Row, 11), MsgText, HasError)
    
          Do While Not rst.EOF
             cboRoom.AddItem rst.Fields("room_name")
             rst.MoveNext
          Loop
          
        ElseIf col = 14 Then
        
          Set mrc = ExecuteSQL("select * from tbl_room where room_name = '" & .TextMatrix(Row, 14) & "'", MsgText, HasError)
    
          If mrc.EOF = False Then
             .TextMatrix(Row, 13) = mrc.Fields("room_id")
          End If
        
        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(txtSetupDesc.Text) = "" Then
         MsgBox "必须输入操作描述!", vbOKOnly + vbExclamation, "警告"
        txtDesc.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
                

                         If Trim(mrc.Fields("state")) <> "预领" Then
                              MsgBox "第" & MSFlexGrid1.TextMatrix(i, 1) & "行的设备状态必须是:预领", vbOKOnly + vbExclamation, "警告"
                              Exit Sub
                         End If

 
            End If
    
    End If
    
    
    If Trim(MSFlexGrid1.TextMatrix(i, 9)) = "" Then
         MsgBox "第" & MSFlexGrid1.TextMatrix(i, 1) & "行的设备必须上线的校区", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    
    If Trim(MSFlexGrid1.TextMatrix(i, 11)) = "" Then
         MsgBox "第" & MSFlexGrid1.TextMatrix(i, 1) & "行的设备必须上线的楼栋", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If
    
    If Trim(MSFlexGrid1.TextMatrix(i, 13)) = "" Then
         MsgBox "第" & MSFlexGrid1.TextMatrix(i, 1) & "行的设备必须上线的房间", vbOKOnly + vbExclamation, "警告"
        Exit Sub
    End If

   
Next




'''''''''''''''''''''''''''''
  '下面开始保存记录
  On Error GoTo ErrorHandler
  BeginTrans
   
   '1 保存入库单
    Set mrc = ExecuteSQL("select top 1 setup_id from tbl_device_setup order by setup_id desc", MsgText, HasError)
    
    If mrc.EOF = False Then
         oldid = Val(mrc.Fields("setup_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
        newsetup_id = snewid
    Else
        newsetup_id = "00000001"
    End If
    
    txtSQL = "insert into tbl_device_setup values('"
   txtSQL = txtSQL & newsetup_id & "','"
   txtSQL = txtSQL & Trim(txtSetupDesc) & "','"
   txtSQL = txtSQL & Trim(ymdSetuprq.FormatDate) & "','"
   txtSQL = txtSQL & Trim(txtApplicant) & "','"
   txtSQL = txtSQL & Trim(txtTransactor) & "'"
   txtSQL = txtSQL & ")"
    
    Set mrc = ExecuteSQL(txtSQL, MsgText, HasError)
    
    
    
For i = 1 To MSFlexGrid1.Rows - 1

   '1 修改设备状态
     State = "上线"
   
   
    txtSQL = "update tbl_device set "
    txtSQL = txtSQL & " area_id=" & Quote(MSFlexGrid1.TextMatrix(i, 9)) & ", "
    txtSQL = txtSQL & " area_name=" & Quote(MSFlexGrid1.TextMatrix(i, 10)) & ", "
    txtSQL = txtSQL & " building_id=" & Quote(MSFlexGrid1.TextMatrix(i, 11)) & ", "
    txtSQL = txtSQL & " building_name=" & Quote(MSFlexGrid1.TextMatrix(i, 12)) & ", "
    txtSQL = txtSQL & " room_id=" & Quote(MSFlexGrid1.TextMatrix(i, 13)) & ", "
    txtSQL = txtSQL & " room_name=" & Quote(MSFlexGrid1.TextMatrix(i, 14)) & ", "
    'txtSQL = txtSQL & "state=" & Quote(MSFlexGrid1.TextMatrix(i, 15)) & ", "
    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("设备上线:") & MSFlexGrid1.TextMatrix(i, 10) & " " & MSFlexGrid1.TextMatrix(i, 12) & " " & MSFlexGrid1.TextMatrix(i, 14) & " 上线单号:" & newsetup_id & "','"
   txtSQL = txtSQL & Trim(txtApplicant) & "','"
   txtSQL = txtSQL & Trim(txtTransactor) & "','"
   txtSQL = txtSQL & Trim(ymdSetuprq.FormatDate) & "'"
   txtSQL = txtSQL & ")"
    
    Set mrc = ExecuteSQL(txtSQL, MsgText, HasError)

   '3 保存items
     txtSQL = "insert into tbl_device_setup_items values('"
   txtSQL = txtSQL & newsetup_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 & Trim(MSFlexGrid1.TextMatrix(i, 11)) & "','"
   txtSQL = txtSQL & Trim(MSFlexGrid1.TextMatrix(i, 12)) & "','"
   txtSQL = txtSQL & Trim(MSFlexGrid1.TextMatrix(i, 13)) & "','"
   txtSQL = txtSQL & Trim(MSFlexGrid1.TextMatrix(i, 14)) & "','"
   txtSQL = txtSQL & Trim(MSFlexGrid1.TextMatrix(i, 15)) & "'"
   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
  
  FrmDeviceSetupList.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