统计bipp资源的sql*xl vba脚本

Public rowid  As Integer
Public loca  As String

'Created by She Xiaoyu 2006-8-4 Lahore

Sub BIPP()
ScreenUpdate = False
If SQLXL.Database.Connected <> True Then
        MsgBox "Connection has not been estiblished yet!!!", 32, "[e-mail]      Aug.2006"
        Exit Sub
    End If

'create new sheet

   Sheets.Add
   
'freeze window
    Range("E2").Select
    ActiveWindow.FreezePanes = True
    
'query bipp

    SQLXL.Sql.setText Text:="select distinct r_btrunk.bscid,r_munit.r_module,r_btrunk.munit,r_board.slotno" & _
                        " from r_btrunk,r_munit,r_board " & _
                        " where r_board.boardtype=23 and r_btrunk.bscid=r_board.bscid " & _
                                " and r_btrunk.munit=r_board.munit and r_munit.bscid=r_btrunk.bscid " & _
                                " and r_btrunk.munit=r_munit.munit"

     Set Targets(litExcel).StartFromCell = Range("A1")
        Targets(litExcel).headings = True
        SQLXL.Sql.Statements(1).Target = litExcel
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute

'main program

    SQLXL.Targets(litExcel).headings = False
    SQLXL.Targets(litExcel).SQLInNote = False

'format header
    Range("A1").Select
    Selection.Copy
    Range("E1:S1").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    'column width

Columns("A:S").Select
    Selection.ColumnWidth = 4


 
Range("E1").Value = "Abis E1 Used"
Range("F1").Value = "Trx Used"
Range("G1").Value = "Lapd Used"
Range("H1").Value = "Radio Channel Num"
Range("I1").Value = "TCH/F"
Range("J1").Value = "TCH/H0"
Range("K1").Value = "TCH/H1"
Range("L1").Value = "SDCCH/8+SACCH/C8"
Range("M1").Value = "FCCH+SCH+BCCH+CCCH"
Range("N1").Value = "BCCH+SDCCH/4"
Range("O1").Value = "BCCH+CCCH"
Range("P1").Value = "BCCH+SDCCH/4+CBCH"
Range("Q1").Value = "SDCCH + CBCH"
Range("R1").Value = "Other Channel (GPRS)"
Range("S1").Value = "Dynamic HR Adjective TS"


    rowid = 2
      

While Range("A" & CStr(rowid)).Text <> ""

'E1 used
    sqlstrhead = "select count(*) from " & _
            " ( select distinct a.bscid,c.r_module,a.munit,a.unit,a.pcm from r_btrunk a,r_board b, r_munit c " & _
            "         where a.kind = 1 and b.bscId = a.bscId And b.munit = a.munit and b.unit=a.unit" & _
            "           and b.munit = c.munit and b.bscId = c.bscId and a.bscid=" & _
            CStr(Range("A" & CStr(rowid)).Value) & _
                                " and a.munit=" & _
                                CStr(Range("C" & CStr(rowid)).Value) & _
                                " )"

                            

        SQLXL.Sql.setText Text:=sqlstrhead
        SQLXL.Sql.Statements(1).Target = litExcel
        Set SQLXL.Targets(litExcel).StartFromCell = Range("E" & CStr(rowid))
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute

'trx used
    sqlstrhead = " SELECT count(*) " & _
                         " FROM (SELECT DISTINCT BSCID,SITEID,BTSID,TRXID,munit,unit FROM R_BTRUNK " & _
                                " where siteid<>0 and btsid <>0 and trxid<>0 and bscid<>0) a, " & _
                                " r_board b, r_munit c " & _
                                " where a.bscId = c.bscId And a.munit = c.munit " & _
                                " and a.bscid=b.bscid  and a.munit=b.munit and a.unit=b.unit and a.bscid=" & _
                                CStr(Range("A" & CStr(rowid)).Value) & _
                                " and a.munit=" & _
                                CStr(Range("C" & CStr(rowid)).Value)
                            

        SQLXL.Sql.setText Text:=sqlstrhead
        SQLXL.Sql.Statements(1).Target = litExcel
        Set SQLXL.Targets(litExcel).StartFromCell = Range("F" & CStr(rowid))
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute
   
 
'lapd used
        If (Range("D" & CStr(rowid)).Value > 14) Then
            loca = " cslotno > 20 and cslotno < 25 "
        Else
            loca = " cslotno < 21 and cslotno > 14 "
        End If
        
    sqlstrhead = "select count(*)  from r_lapd where bscid= " & _
           CStr(Range("A" & CStr(rowid)).Value) & _
               " and module= " & _
            CStr(Range("B" & CStr(rowid)).Value) & _
           " and " & loca & ";"
    
    
     SQLXL.Sql.setText Text:=sqlstrhead
        SQLXL.Sql.Statements(1).Target = litExcel
        Set SQLXL.Targets(litExcel).StartFromCell = Range("G" & CStr(rowid))
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute
    

'radio channel number

    sqlstrhead = _
                "select count(*) from " & _
                                " (select distinct r_ztechannel.bscid,r_ztechannel.siteid," & _
                                " r_ztechannel.btsid,r_ztechannel.trxid,r_ztechannel.tsid,r_ztechannel.tschannelcomb," & _
                                " r_btrunk.munit from r_ztechannel,r_btrunk where r_ztechannel.bscid=r_btrunk.bscid " & _
                                " and r_ztechannel.siteid=r_btrunk.siteid and r_btrunk.bscid=" & _
                CStr(Range("A" & CStr(rowid)).Value) & _
                "and r_btrunk.munit=" & _
                CStr(Range("C" & CStr(rowid)).Value) & _
                ")"

        SQLXL.Sql.setText Text:=sqlstrhead
        SQLXL.Sql.Statements(1).Target = litExcel
        Set SQLXL.Targets(litExcel).StartFromCell = Range("H" & CStr(rowid))
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute
'tch/f
sqlstrhead = _
                "select count(*) from (select distinct r_ztechannel.bscid,r_ztechannel.siteid," & _
                                " r_ztechannel.btsid,r_ztechannel.trxid,r_ztechannel.tsid," & _
                                " r_ztechannel.tschannelcomb,r_btrunk.munit from r_ztechannel," & _
                                " r_btrunk where r_ztechannel.bscid=r_btrunk.bscid " & _
                                " and r_ztechannel.siteid=r_btrunk.siteid " & _
                                " and r_ztechannel.tschannelcomb=0 and r_btrunk.bscid=" & _
                CStr(Range("A" & CStr(rowid)).Value) & _
                "and r_btrunk.munit=" & _
                CStr(Range("C" & CStr(rowid)).Value) & _
                ")"

        SQLXL.Sql.setText Text:=sqlstrhead
        SQLXL.Sql.Statements(1).Target = litExcel
        Set SQLXL.Targets(litExcel).StartFromCell = Range("I" & CStr(rowid))
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute


'tch/h0

   sqlstrhead = _
                "select count(*) from (select distinct r_ztechannel.bscid,r_ztechannel.siteid," & _
                                " r_ztechannel.btsid,r_ztechannel.trxid,r_ztechannel.tsid," & _
                                " r_ztechannel.tschannelcomb,r_btrunk.munit from r_ztechannel," & _
                                " r_btrunk where r_ztechannel.bscid=r_btrunk.bscid " & _
                                " and r_ztechannel.siteid=r_btrunk.siteid " & _
                                " and r_ztechannel.tschannelcomb=1 and r_btrunk.bscid=" & _
                CStr(Range("A" & CStr(rowid)).Value) & _
                "and r_btrunk.munit=" & _
                CStr(Range("C" & CStr(rowid)).Value) & _
                ")"

        SQLXL.Sql.setText Text:=sqlstrhead
        SQLXL.Sql.Statements(1).Target = litExcel
        Set SQLXL.Targets(litExcel).StartFromCell = Range("J" & CStr(rowid))
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute
'tch/h1
sqlstrhead = _
                "select count(*) from (select distinct r_ztechannel.bscid,r_ztechannel.siteid," & _
                                " r_ztechannel.btsid,r_ztechannel.trxid,r_ztechannel.tsid," & _
                                " r_ztechannel.tschannelcomb,r_btrunk.munit from r_ztechannel," & _
                                " r_btrunk where r_ztechannel.bscid=r_btrunk.bscid " & _
                                " and r_ztechannel.siteid=r_btrunk.siteid " & _
                                " and r_ztechannel.tschannelcomb=2 and r_btrunk.bscid=" & _
                CStr(Range("A" & CStr(rowid)).Value) & _
                "and r_btrunk.munit=" & _
                CStr(Range("C" & CStr(rowid)).Value) & _
                ")"

        SQLXL.Sql.setText Text:=sqlstrhead
        SQLXL.Sql.Statements(1).Target = litExcel
        Set SQLXL.Targets(litExcel).StartFromCell = Range("K" & CStr(rowid))
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute

'SDCCH/8+SACCH/C8
sqlstrhead = _
                "select count(*) from (select distinct r_ztechannel.bscid,r_ztechannel.siteid," & _
                                " r_ztechannel.btsid,r_ztechannel.trxid,r_ztechannel.tsid," & _
                                " r_ztechannel.tschannelcomb,r_btrunk.munit from r_ztechannel," & _
                                " r_btrunk where r_ztechannel.bscid=r_btrunk.bscid " & _
                                " and r_ztechannel.siteid=r_btrunk.siteid " & _
                                " and r_ztechannel.tschannelcomb=3 and r_btrunk.bscid=" & _
                CStr(Range("A" & CStr(rowid)).Value) & _
                "and r_btrunk.munit=" & _
                CStr(Range("C" & CStr(rowid)).Value) & _
                ")"

        SQLXL.Sql.setText Text:=sqlstrhead
        SQLXL.Sql.Statements(1).Target = litExcel
        Set SQLXL.Targets(litExcel).StartFromCell = Range("L" & CStr(rowid))
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute
'FCCH+SCH+BCCH+CCCH
sqlstrhead = _
                "select count(*) from (select distinct r_ztechannel.bscid,r_ztechannel.siteid," & _
                                " r_ztechannel.btsid,r_ztechannel.trxid,r_ztechannel.tsid," & _
                                " r_ztechannel.tschannelcomb,r_btrunk.munit from r_ztechannel," & _
                                " r_btrunk where r_ztechannel.bscid=r_btrunk.bscid " & _
                                " and r_ztechannel.siteid=r_btrunk.siteid " & _
                                " and r_ztechannel.tschannelcomb=4 and r_btrunk.bscid=" & _
                CStr(Range("A" & CStr(rowid)).Value) & _
                "and r_btrunk.munit=" & _
                CStr(Range("C" & CStr(rowid)).Value) & _
                ")"

        SQLXL.Sql.setText Text:=sqlstrhead
        SQLXL.Sql.Statements(1).Target = litExcel
        Set SQLXL.Targets(litExcel).StartFromCell = Range("M" & CStr(rowid))
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute
'BCCH+SDCCH/4
sqlstrhead = _
                "select count(*) from (select distinct r_ztechannel.bscid,r_ztechannel.siteid," & _
                                " r_ztechannel.btsid,r_ztechannel.trxid,r_ztechannel.tsid," & _
                                " r_ztechannel.tschannelcomb,r_btrunk.munit from r_ztechannel," & _
                                " r_btrunk where r_ztechannel.bscid=r_btrunk.bscid " & _
                                " and r_ztechannel.siteid=r_btrunk.siteid " & _
                                " and r_ztechannel.tschannelcomb=5 and r_btrunk.bscid=" & _
                CStr(Range("A" & CStr(rowid)).Value) & _
                "and r_btrunk.munit=" & _
                CStr(Range("C" & CStr(rowid)).Value) & _
                ")"

        SQLXL.Sql.setText Text:=sqlstrhead
        SQLXL.Sql.Statements(1).Target = litExcel
        Set SQLXL.Targets(litExcel).StartFromCell = Range("N" & CStr(rowid))
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute
'BCCH+CCCH
sqlstrhead = _
                "select count(*) from (select distinct r_ztechannel.bscid,r_ztechannel.siteid," & _
                                " r_ztechannel.btsid,r_ztechannel.trxid,r_ztechannel.tsid," & _
                                " r_ztechannel.tschannelcomb,r_btrunk.munit from r_ztechannel," & _
                                " r_btrunk where r_ztechannel.bscid=r_btrunk.bscid " & _
                                " and r_ztechannel.siteid=r_btrunk.siteid " & _
                                " and r_ztechannel.tschannelcomb=6 and r_btrunk.bscid=" & _
                CStr(Range("A" & CStr(rowid)).Value) & _
                "and r_btrunk.munit=" & _
                CStr(Range("C" & CStr(rowid)).Value) & _
                ")"

        SQLXL.Sql.setText Text:=sqlstrhead
        SQLXL.Sql.Statements(1).Target = litExcel
        Set SQLXL.Targets(litExcel).StartFromCell = Range("O" & CStr(rowid))
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute
'BCCH+SDCCH/4+CBCH
sqlstrhead = _
                "select count(*) from (select distinct r_ztechannel.bscid,r_ztechannel.siteid," & _
                                " r_ztechannel.btsid,r_ztechannel.trxid,r_ztechannel.tsid," & _
                                " r_ztechannel.tschannelcomb,r_btrunk.munit from r_ztechannel," & _
                                " r_btrunk where r_ztechannel.bscid=r_btrunk.bscid " & _
                                " and r_ztechannel.siteid=r_btrunk.siteid " & _
                                " and r_ztechannel.tschannelcomb=7 and r_btrunk.bscid=" & _
                CStr(Range("A" & CStr(rowid)).Value) & _
                "and r_btrunk.munit=" & _
                CStr(Range("C" & CStr(rowid)).Value) & _
                ")"

        SQLXL.Sql.setText Text:=sqlstrhead
        SQLXL.Sql.Statements(1).Target = litExcel
        Set SQLXL.Targets(litExcel).StartFromCell = Range("P" & CStr(rowid))
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute
'SDCCH + CBCH
sqlstrhead = _
                "select count(*) from (select distinct r_ztechannel.bscid,r_ztechannel.siteid," & _
                                " r_ztechannel.btsid,r_ztechannel.trxid,r_ztechannel.tsid," & _
                                " r_ztechannel.tschannelcomb,r_btrunk.munit from r_ztechannel," & _
                                " r_btrunk where r_ztechannel.bscid=r_btrunk.bscid " & _
                                " and r_ztechannel.siteid=r_btrunk.siteid " & _
                                " and r_ztechannel.tschannelcomb=8 and r_btrunk.bscid=" & _
                CStr(Range("A" & CStr(rowid)).Value) & _
                "and r_btrunk.munit=" & _
                CStr(Range("C" & CStr(rowid)).Value) & _
                ")"

        SQLXL.Sql.setText Text:=sqlstrhead
        SQLXL.Sql.Statements(1).Target = litExcel
        Set SQLXL.Targets(litExcel).StartFromCell = Range("Q" & CStr(rowid))
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute
'OTHER channel
sqlstrhead = _
                "select count(*) from (select distinct r_ztechannel.bscid,r_ztechannel.siteid," & _
                                " r_ztechannel.btsid,r_ztechannel.trxid,r_ztechannel.tsid," & _
                                " r_ztechannel.tschannelcomb,r_btrunk.munit from r_ztechannel," & _
                                " r_btrunk where r_ztechannel.bscid=r_btrunk.bscid " & _
                                " and r_ztechannel.siteid=r_btrunk.siteid " & _
                                " and r_ztechannel.tschannelcomb>8 and r_btrunk.bscid=" & _
                CStr(Range("A" & CStr(rowid)).Value) & _
                "and r_btrunk.munit=" & _
                CStr(Range("C" & CStr(rowid)).Value) & _
                ")"

        SQLXL.Sql.setText Text:=sqlstrhead
        SQLXL.Sql.Statements(1).Target = litExcel
        Set SQLXL.Targets(litExcel).StartFromCell = Range("R" & CStr(rowid))
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute


         
        'DYNAMIC HR ADJECTIVE TIMESLOTS
         If (Range("D" & CStr(rowid)).Value < 14) Then
           sqlstrhead = " select ((select count(*) from r_hrdynchannel a " & _
                         " where(bscid, munit) in ( " & _
                                " select distinct b.bscid,b.munit from r_munit b, r_board c " & _
                                " where b.bscid=c.bscid and b.munit=c.munit and " & _
                                " c.slotno < 14 and "
        Else
           sqlstrhead = " select ((select count(*) from r_hrdynchannel a " & _
                         " where(bscid, munit) in ( " & _
                                " select distinct b.bscid,b.munit from r_munit b, r_board c " & _
                                " where b.bscid=c.bscid and b.munit=c.munit and " & _
                                " c.slotno > 14 and "
        End If
       
        sqlstrhead = sqlstrhead & _
        " c.boardtype = 28 " & _
        " and b.bscid = " & _
        CStr(Range("A" & CStr(rowid)).Value) & _
        " and b.r_module = " & _
        CStr(Range("B" & CStr(rowid)).Value) & "))) from dual;"
        
        
        SQLXL.Sql.setText Text:=sqlstrhead
        SQLXL.Sql.Statements(1).Target = litExcel
        Set SQLXL.Targets(litExcel).StartFromCell = Range("S" & CStr(rowid))
        SQLXL.Sql.Statements(1).ShowResultsetDlg = False
        SQLXL.Sql.Statements(1).Execute
      
    

    rowid = rowid + 2

Wend

Range("E2").Select
ScreenUpdate = True
End Sub