Calculate Traffic Channels (1)

Product : Size of traffic channels needed into company.
Part involved : Implementation of practical use to calculate the quantity of traffic channels needed.
Summary : How much need to grow our infrastructure in land lines, mobile phone calls and another types of callings ?
Scenario : Developing IT plans will be based on the growth of the future company needs and that includes how to calculate the quantity of traffic channels .
Company : Minister of Health of Peru (MINSA).
Reference date : October 2006.

The most type of PBX display reports with some profiles about detailed calling party processed into PBX, this information is the input that we need to generate into our own model of trunking size. Next lines is an example of this type of information :

2832 Ana M.León M OGP1 0609010125000:00:05 0.74 S 0' 4481949 N 0001001001001 40 0BA

When information is not formatted like than the upper lines then is necessary to start with preliminar delimited field information of every record in order to establish a standard input information with the purpose of create a main input call party file log, such as next lines :

"01/11/2006","00:01:00","2782","5519617",0 “ is the same to say
Date, Time, from Extension, Number called, Call party time (secs)

When the information is appended according to the last format is possible to filter the information with every field or the combination of these ones. Next script works under excel enviroment and can be executed to achieved this goal. Check that the differents levels of code nesting are separated with differents colors.

Ruta10:
FilaIn = 1: FilaOut = 1: condicion = True

While condicion = True
If ActiveSheet.Cells(FilaIn, 1).Value = "" Then

Taz = Taz + 1
End If
If Taz >= 1 Then condicion = False

Level1 = 0: Level2 = 0: Level3 = 0: Level4 = 0
ActiveSheet.Cells(FilaIn, 1).Select
CCadena = ActiveSheet.Cells(FilaIn, 1).Value
Ccadena1 = Left(CCadena, 2)
Ccadena2 = Left(ActiveSheet.Cells(FilaIn + 1, 1).Value, 2)

If Ccadena1 = " " Then
ActiveSheet.Cells(FilaIn, 9).Value = ""
Else
ActiveSheet.Cells(FilaIn, 9).Value = "NO"
End If

If Ccadena1 <> " " And Ccadena2 <> " " Then
Selection.EntireRow.Delete
Else
FilaIn = FilaIn + 1
End If

'
Wend

FilaIn = 1: condicion = True: Par = 0: Taz = 0
While condicion = True
If ActiveSheet.Cells(FilaIn, 1).Value = "" Then
Taz = Taz + 1
End If
If Taz >= 1 Then condicion = False

ActiveSheet.Cells(FilaIn, 1).Select
CCadena = ActiveSheet.Cells(FilaIn, 9).Value
If CCadena = "NO" And Par = 0 Then ActiveSheet.Cells(FilaIn, 10).Value = ""
If CCadena <> "NO" And Par = 0 Then ActiveSheet.Cells(FilaIn, 10).Value = "Error"
If CCadena = "" And Par = 1 Then ActiveSheet.Cells(FilaIn, 10).Value = ""
If CCadena <> "" And Par = 1 Then ActiveSheet.Cells(FilaIn, 10).Value = "Error"
If ActiveSheet.Cells(FilaIn, 10).Value = "Error" Then
Selection.EntireRow.Delete
Else
Par = Par + 1: FilaIn = FilaIn + 1
If Par = 2 Then Par = 0
End If
Wend

' Crear archivo de texto con las columnas Fecha, Hora, Anexo, NumeroLlamado, Segs
' en modo append
Ruta1:

FilaIn = 1: Techito = True: Taz = 0
Dim Fecha As Date: Dim Hora As Date
Dim cadena1 As String: Dim cadena2 As String
Dim numero As String: Dim Cadena As String: Dim anexo As String: Dim tmp As String
Dim saro As String: Dim smes As String: Dim sdia As String: Dim shh As String: Dim smm As String: Dim sss As String
Dim ss1 As String: Dim ss2 As String: Dim ss3 As String
Dim iaro As Integer: Dim imes As Integer: Dim idia As Integer: Dim ihh As Integer: Dim imm As Integer: Dim iss As Integer
Dim iss1 As Integer: Dim iss2 As Integer: Dim iss3 As Integer

While Techito = True
ActiveSheet.Cells(FilaIn, 1).Select
Estacion = Len(ActiveSheet.Cells(FilaIn, 1).Value)
If Estacion = 0 Then
Taz = Taz + 1
End If
If Taz >= 1 Then Techito = False

Level1 = 0: Level2 = 0: Level3 = 0: Level4 = 0

Cadena = ActiveSheet.Cells(FilaIn, 1).Value
Largo = Len(Cadena)

For a = 1 To Largo
If Mid(Cadena, a, 1) = "" Then Level1 = Level1 + 1: Level2 = 0
If Mid(Cadena, a, 1) = " " Then Level2 = Level2 + 1

If Level1 = 1 And Level2 = 0 Then anexo = anexo + Mid(Cadena, a + 1, 1)
If Level1 = 4 And Level2 = 0 Then
If Level3 >= 1 And Level3 <= 2 Then saro = saro + Mid(Cadena, a, 1)

If Level3 >= 3 And Level3 <= 4 Then smes = smes + Mid(Cadena, a, 1)

If Level3 >= 5 And Level3 <= 6 Then sdia = sdia + Mid(Cadena, a, 1)

If Level3 >= 7 And Level3 <= 8 Then shh = shh + Mid(Cadena, a, 1)

If Level3 >= 9 And Level3 <= 10 Then smm = smm + Mid(Cadena, a, 1) Level3 = Level3 + 1 End If

If Level1 = 5 And Level2 = 0 Then

If Level4 >= 1 And Level4 <= 3 Then ss1 = ss1 + Mid(Cadena, a, 1)

If Level4 >= 5 And Level4 <= 6 Then ss2 = ss2 + Mid(Cadena, a, 1)

If Level4 >= 8 And Level4 <= 9 Then ss3 = ss3 + Mid(Cadena, a, 1) Level4 = Level4 + 1

If Level4 = 10 Then a = Largo

End If

Next a

FilaIn = FilaIn + 1: Level1 = 0: Level2 = 0: Level3 = 0: Level4 = 0 ActiveSheet.Cells(FilaIn, 1).Select Cadena = ActiveSheet.Cells(FilaIn, 1).Value Largo = Len(Cadena)

For a = 1 To Largo

If Mid(Cadena, a, 1) = "" Then Level1 = Level1 + 1: Level2 = 0 If Mid(Cadena, a, 1) = " " Then Level2 = Level2 + 1 If Level1 = 2 And Level2 = 0 Then numero = numero + Mid(Cadena, a + 1, 1) If Level2 >= 1 And Level1 = 2 Then a = Largo
Next a

tmp = "": If Left(numero, 1) = "1" Then numero = Mid(numero, 2, Len(numero))
For b = 1 To Len(numero)
If Mid(numero, b, 1) = " " Then
Else
tmp = tmp + Mid(numero, b, 1)
End If
Next b: numero = tmp: tmp = ""

For b = 1 To Len(anexo)
If Mid(anexo, b, 1) = " " Then
Else
tmp = tmp + Mid(anexo, b, 1)
End If
Next b: anexo = tmp: tmp = ""

segs = 0
iaro = Val(saro): imes = Val(smes): idia = Val(sdia)
ihh = Val(shh): imm = Val(smm): iss = Val(sss)
iss1 = Val(ss1): iss2 = Val(ss2): iss3 = Val(ss3)
Fecha = DateSerial(iaro, imes, idia): cadena1 = Fecha
Hora = TimeSerial(ihh, imm, iss): cadena2 = Hora

If iss1 = 0 Then
Else
segs = segs + (3600 * iss1)
End If
If iss2 = 0 Then
Else
segs = segs + (60 * iss2)
End If
If iss3 = 0 Then
Else
segs = segs + iss3
End If

Open "C:\Temo\1.txt" For Append As #1
Write #1, cadena1, cadena2, anexo, numero, segs: Close 1

FilaIn = FilaIn + 1: Level1 = 0: Level2 = 0: Level3 = 0: Level4 = 0
saro = "": smes = "": sdia = "": ss1 = "": ss2 = "": ss3 = "": shh = "": smm = "": sss = ""
iaro = 0: imes = 0: idia = 0: ihh = 0: imm = 0: iss = 0: anexo = "": numero = "": tmp = ""
Wend
MsgBox ("Fin de Ejecución del Programa")