frmdevice.frm

网路设备资产管理系统

     searchQuery = searchQuery & " and property_id = '" & Trim(txtPropertyId.Text) & "' "
  End If
  
  
Grid_Fill

End Sub

Private Sub Form_Load()


  Toolbar1.Buttons(1).Enabled = cur_priv_sbzlxg


Set TreeView1.ImageList = ImageList2

Set rootNode = TreeView1.Nodes.Add(, , "Root", "上线设备", 1)
rootNode.Expanded = True


Fill_Root_tree (rootNode.key)


Set rootNode = TreeView1.Nodes.Add(, , "Storehouse", "在库设备", 1)
Set rootNode = TreeView1.Nodes.Add(, , "Broken", "故障设备", 1)
Set rootNode = TreeView1.Nodes.Add(, , "Prefetch", "预领设备", 1)
Set rootNode = TreeView1.Nodes.Add(, , "Lend", "借出设备", 1)
Set rootNode = TreeView1.Nodes.Add(, , "Repair", "送修设备", 1)
Set rootNode = TreeView1.Nodes.Add(, , "Discard", "报废设备", 1)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Grid_Init
'Grid_Fill



          cboDevicetype.Clear
          Set rst = ExecuteSQL("select * from tbl_devicetype ", MsgText, HasError)
          cboDevicetype.AddItem ""
          Do While Not rst.EOF
             cboDevicetype.AddItem rst.Fields("type_name")
             rst.MoveNext
          Loop
          
          cboManufacturer.Clear
          Set rst = ExecuteSQL("select * from tbl_manufacturer ", MsgText, HasError)
          cboManufacturer.AddItem ""
          Do While Not rst.EOF
             cboManufacturer.AddItem rst.Fields("manufacturer_name")
             rst.MoveNext
          Loop
          
          cboModel.Clear
          Set rst = ExecuteSQL("select * from tbl_model", MsgText, HasError)
          cboModel.AddItem ""
          Do While Not rst.EOF
             cboModel.AddItem rst.Fields("model_name")
             rst.MoveNext
          Loop

End Sub

Private Sub Fill_Root_tree(NodeKey As String)

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
txtSQL = "select * from tbl_area"
Set rst = ExecuteSQL(txtSQL, MsgText, HasError)


    Do While Not rst.EOF
                      
         Set nd = TreeView1.Nodes.Add(NodeKey, tvwChild, "area_" & rst.Fields("area_id"), rst.Fields("area_name"), 2)
         'nd.Expanded = True
         Call Fill_Area_tree(nd.key, rst.Fields("area_id"))
         
    rst.MoveNext
    Loop



End Sub


Sub Fill_Area_tree(NodeKey As String, area_id As String)


Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
txtSQL = "select * from tbl_building where area_id = " & area_id
Set rst = ExecuteSQL(txtSQL, MsgText, HasError)


    Do While Not rst.EOF
                      
         Set nd = TreeView1.Nodes.Add(NodeKey, tvwChild, "building_" & rst.Fields("building_id"), rst.Fields("building_name"), 3)
         'nd.Expanded = True
         Call Fill_Building_tree(nd.key, rst.Fields("building_id"))
         
    rst.MoveNext
    Loop
    
    

End Sub


Private Sub Fill_Building_tree(NodeKey As String, building_id As String)

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
txtSQL = "select * from tbl_room where building_id = " & building_id
Set rst = ExecuteSQL(txtSQL, MsgText, HasError)


    Do While Not rst.EOF
                      
         Set nd = TreeView1.Nodes.Add(NodeKey, tvwChild, "room_" & rst.Fields("room_id"), rst.Fields("room_name"), 4)

         
    rst.MoveNext
    Loop

End Sub

Sub Grid_Init()

    With MSFlexGrid1
        .FormatString = "|<|<中心编号|<固定资产号|<型号|<厂商|<设备类型|<状态|<校区|<楼栋|<房间|<SN号"
        .ColWidth(0) = 0
        .ColWidth(1) = 0
        .ColWidth(2) = 1200
        .ColWidth(3) = 1200
        .ColWidth(4) = 1800
        .ColWidth(5) = 1000
        .ColWidth(6) = 1000
        .ColWidth(7) = 800
        .ColWidth(8) = 1000
        .ColWidth(9) = 1800
        .ColWidth(10) = 2000
        .ColWidth(11) = 2000
        .Rows = 1

    End With
    
    Set mMSFlexGrid1 = New cFlexGridBinder
    With mMSFlexGrid1
        Set .Grid = MSFlexGrid1
        
        .Editable = True
        .EditOnMouse = emeNone

        .FullRowSelect = True
        
    End With

End Sub

Sub Grid_Fill()

count_all = 0
count_1 = 0
count_2 = 0
count_3 = 0
count_4 = 0

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
txtSQL = "select * from tbl_device " & txtQuery & searchQuery & " order by area_id,building_id,room_id"
Set rst = ExecuteSQL(txtSQL, MsgText, HasError)


   MSFlexGrid1.Rows = 1
    
    Do While Not rst.EOF
        MSFlexGrid1.AddItem vbTab _
        & rst.Fields("device_id") & vbTab _
        & rst.Fields("netcenter_id") & vbTab _
        & rst.Fields("property_id") & vbTab _
        & rst.Fields("model_name") & vbTab _
        & rst.Fields("manufacturer_name") & vbTab _
        & rst.Fields("type_name") & vbTab _
        & rst.Fields("state") & vbTab _
        & rst.Fields("area_name") & vbTab _
        & rst.Fields("building_name") & vbTab _
        & rst.Fields("room_name") & vbTab _
        & rst.Fields("sn")
        
        count_all = count_all + 1
        
        If rst.Fields("type_id") = 1 Then
        count_1 = count_1 + 1
        ElseIf rst.Fields("type_id") = 2 Then
        count_2 = count_2 + 1
        ElseIf rst.Fields("type_id") = 3 Then
        count_3 = count_3 + 1
        ElseIf rst.Fields("type_id") = 4 Then
        count_4 = count_4 + 1
        End If
        
    rst.MoveNext
    Loop
    
    
    lblCountAll = count_all
    lblCount1 = count_1
    lblCount2 = count_2
    lblCount3 = count_3
    lblCount4 = count_4

End Sub






Private Sub MSFlexGrid1_DblClick()

If mMSFlexGrid1.HighlightedRow > 0 Then
  FrmDeviceDetail.device_id = MSFlexGrid1.TextMatrix(mMSFlexGrid1.HighlightedRow, 1)
  FrmDeviceDetail.Show
Else
  MsgBox "必须选择一台设备!", vbOKOnly + vbExclamation, "警告"
End If

End Sub

Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)
    On Error Resume Next
    Select Case Button.key
        
        Case "添加"
           FrmDeviceDetail.device_id = 0
           FrmDeviceDetail.Show
        
        
        Case "导出"
          Call export
            
        Case "关闭"
          Unload Me
          
    End Select
End Sub

Sub export()
    On Error GoTo ErrHandler
Dim xlApp As Excel.Application
Dim xlBook As Object
Dim xlSheet As Object

Screen.MousePointer = vbHourglass
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add

Set xlSheet = xlBook.Worksheets(1)
xlSheet.Range("A1:J1").Select

With xlApp.Selection
   .MergeCells = True
   .Font.Name = "宋体"
   .Font.Size = 18
   .Font.Bold = True
   .HorizontalAlignment = -4108 '水平居
   .VerticalAlignment = -4108 '垂直居
   .Columns.AutoFit
End With
xlSheet.Range("A1").Value = "设备数据导出"

'开始排列评委
Dim startLine As Integer
startLine = 3

 xlSheet.Cells(2, 1) = "中心编号"
 xlSheet.Cells(2, 2) = "固定资产号"
 
 xlSheet.Cells(2, 3) = "型号"
 xlSheet.Cells(2, 4) = "厂商"
 xlSheet.Cells(2, 5) = "设备类型"
 
 xlSheet.Cells(2, 6) = "状态"
 xlSheet.Cells(2, 7) = "校区"
 xlSheet.Cells(2, 8) = "楼栋"
 xlSheet.Cells(2, 9) = "房间"
 xlSheet.Cells(2, 10) = "SN号"




txtSQL = "select * from tbl_device " & txtQuery & searchQuery & " order by area_id,building_id,room_id"

Set mrc = ExecuteSQL(txtSQL, MsgText, HasError)

Do While Not mrc.EOF

 xlSheet.Cells(startLine, 1) = mrc.Fields("netcenter_id")
 xlSheet.Cells(startLine, 2) = mrc.Fields("property_id")
 
 xlSheet.Cells(startLine, 3) = mrc.Fields("model_name")
 xlSheet.Cells(startLine, 4) = mrc.Fields("manufacturer_name")
 xlSheet.Cells(startLine, 5) = mrc.Fields("type_name")
 
 xlSheet.Cells(startLine, 6) = mrc.Fields("state")
 xlSheet.Cells(startLine, 7) = mrc.Fields("area_name")
 xlSheet.Cells(startLine, 8) = mrc.Fields("building_name")


 xlSheet.Cells(startLine, 9) = mrc.Fields("room_name")
 xlSheet.Cells(startLine, 10) = mrc.Fields("sn")


 
  startLine = startLine + 1

mrc.MoveNext
Loop

'开始画格子
xlSheet.Range("A2" & ":J" & startLine).Select
With xlApp.Selection
  .Borders.LineStyle = xlContinuous
  .Borders.ColorIndex = 1
  .Borders.Weight = xlThin
  .Columns(1).ColumnWidth = 9
  .Columns(2).ColumnWidth = 9
  .Columns(3).ColumnWidth = 9
  .Columns(4).ColumnWidth = 9
  .Columns(5).ColumnWidth = 9
  .Columns(6).ColumnWidth = 9
  .Columns(7).ColumnWidth = 9
  .Columns(8).ColumnWidth = 9
  .Columns(9).ColumnWidth = 9
  .Columns(10).ColumnWidth = 9
  .Columns.AutoFit
End With

xlSheet.Range("A1" & ":J" & startLine).Select
With xlApp.Selection
  .RowHeight = 26
End With

xlSheet.Range("A2" & ":J" & startLine).Select
With xlApp.Selection
   .WrapText = True
   .Font.Name = "宋体"
   .Font.Size = 12
   .HorizontalAlignment = -4108 '水平居
   .VerticalAlignment = -4108 '垂直居
   .Columns.AutoFit
End With


xlApp.Visible = True
Screen.MousePointer = vbDefault



Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
    Exit Sub
ErrHandler:
    MsgBox "发生错误，错误描述如下：" & vbCrLf & vbCrLf & Err.Description, vbInformation, "提示"
        Screen.MousePointer = vbDefault
    Set xlApp = Nothing
    Set xlBook = Nothing
    Set xlSheet = Nothing
End Sub


Private Sub TreeView1_NodeClick(ByVal nd As MSComctlLib.Node)

searchQuery = ""
          
          If nd.Image = 1 Then  '根节点
            If nd.key = "Root" Then
              txtQuery = " where state = '上线'"
            ElseIf nd.key = "Storehouse" Then
              txtQuery = " where state = '在库' and area_id = 0"
            ElseIf nd.key = "Broken" Then
              txtQuery = " where state = '故障' and area_id = 0"
            ElseIf nd.key = "Prefetch" Then
              txtQuery = " where state = '预领' and area_id = 0"
            ElseIf nd.key = "Lend" Then
              txtQuery = " where state = '借出' and area_id = 0"
            ElseIf nd.key = "Repair" Then
              txtQuery = " where state = '送修' and area_id = 0"
            ElseIf nd.key = "Discard" Then
              txtQuery = " where state = '报废' and area_id = 0"
              
            End If
          ElseIf nd.Image = 2 Then
            txtQuery = " where state = '上线' and area_id = " & Replace(nd.key, "area_", "")
          ElseIf nd.Image = 3 Then
            txtQuery = " where state = '上线' and building_id = " & Replace(nd.key, "building_", "")
          ElseIf nd.Image = 4 Then
            txtQuery = " where state = '上线' and room_id = " & Replace(nd.key, "room_", "")
          End If
          
          Grid_Fill
End Sub