билиг атс в текстовый лог (ставится службой)

[Alf]

http://lordofsilence.narod.ru/Programs/programs.html

пример лога
ATS_20080704.log
04.07.0812:31:02 8 170 00:00:0645633 2 9 1
04.07.0812:35:22 7 170 00:00:0745633 2 9 1

[Alf]

скрипт подготовки лога для импорта в sql

Set oShell = CreateObject("Shell.Application")
Set WshShell = WScript.CreateObject("WScript.Shell")
Set WSHNetwork = WScript.CreateObject("WScript.Network")
Set colDrives = WSHNetwork.EnumNetworkDrives
Set fso=WScript.CreateObject("Scripting.FileSystemObject")
set wshProcEnv = wshShell.environment("process")

Const PathBillingLog="U:\LogFiles\"
Const NameBillingFile="ATS_YYYYMMDD.log"
Const PathBillingLogForSQLImport="U:\"
Const NameBillingFileForSQLImport="ATS_Billing.log"
Const Delimer="|"

Dim DateCurrentLog
Dim DateCurrentLogFile
Dim StrCallNumberReturn

'Процедура должна стартануть в 23:59
Call GetDateCurrentLogFile
'Ожидание 2 минуты отключения службы от текущего лога
Call TimeOut
'Чтение завершонного лога в 00:01 прошлого дня
Call ReadLogFile

'===========================================================================

Sub GetDateCurrentLogFile()
DateCurrentLog= mid(date,7,4) & Mid(date,4,2) & mid(date,1,2)
DateCurrentLogFile= PathBillingLog & Replace(NameBillingFile, "YYYYMMDD", DateCurrentLog)
End Sub

Sub ReadLogFile
Set LogFile = FSO.OpenTextFile(DateCurrentLogFile, 1, False)
Set BillingForSQLFile=FSO.OpenTextFile(PathBillingLogForSQLImport & NameBillingFileForSQLImport, 2, True)

StrWrite=""
Do While Not LogFile.AtEndOfStream
Str_Billing = LogFile.ReadLine
StrWrite = Mid(Str_Billing,1,6) & mid(date,7,2) & Mid(Str_Billing,7,2)
StrWrite = StrWrite & Delimer & Mid(Str_Billing,9,8)
StrWrite = StrWrite & Delimer & Mid(Str_Billing,18,2)
StrWrite = StrWrite & Delimer & Mid(Str_Billing,23,3)
StrWrite = StrWrite & Delimer & Mid(Str_Billing,31,8)

StrCallNumber=Mid(Str_Billing,39,20)

StrCallNumberReturn=""
Call CheskCallNumber(Str_Billing,StrCallNumber)
StrCallNumber=StrCallNumberReturn

StrWrite = StrWrite & Delimer & StrCallNumberReturn

StrWrite = StrWrite & Delimer & Mid(Str_Billing,75,2)
StrWrite = StrWrite & Delimer & Mid(Str_Billing,103,1)
StrWrite = StrWrite & Delimer & Mid(Str_Billing,105,1)

'попытка отрезать недонабранные номера менее 5 цифр
If Len(Trim(StrCallNumber))=>5 then
BillingForSQLFile.WriteLine(StrWrite)
End if

Loop
LogFile.Close
BillingForSQLFile.Close
End Sub


'================================================================
Sub CheskCallNumber(StrBilling,CallNumber)
CallNumber=Trim(CallNumber)
' korp
If Mid(StrBilling,103,1)="0" Then
If Mid(CallNumber,1,1)="0" And Mid(CallNumber,2,1)<>"0" Then CallNumber="0" & CallNumber
If Mid(CallNumber,1,2)="00" Then CallNumber=CallNumber
If Mid(CallNumber,1,1)<>"0" Then CallNumber= "00" & CallNumber
End If
' gorod
If Mid(StrBilling,103,1)="9" Then
CallNumber=CallNumber
End If
StrCallNumberReturn=CallNumber & String(20-Len(Trim(CallNumber))," ")
End Sub

'================================================================
Sub TimeOut
WScript.Sleep 120000
End Sub

[Alf]

Лог после обработки скриптом
08.07.2008|11:19:23| 8|112|00:01:18|77432 | 2|9|1
08.07.2008|11:20:57| 5|111|00:01:08|41613 | 2|9|1
08.07.2008|11:22:59|13|111|00:00:45|0044951 | 2|0|1
08.07.2008|11:24:57| 6|111|00:01:24|88612532922 | 2|9|1
08.07.2008|11:30:39| 7|121|00:00:08|77432 | 2|9|1

[Alf]

Скрипт создания таблицы MS SQL для импорта из текстового лога

CREATE TABLE [dbo].[ATS_Billing] (
[ID_Call] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
[CallDate] [datetime] NULL ,
[CallTime] [datetime] NULL ,
[Duration] [datetime] NULL ,
[Abonent] [int] NULL ,
[Trunk] [int] NULL ,
[Number] [char] (25) COLLATE Cyrillic_General_CI_AS NULL ,
[Colum_1] [int] NULL ,
[Colum_2] [int] NULL ,
[Colum_3] [int] NULL
) ON [PRIMARY]
GO

[Alf]

1й шаг настройки импорта

[Alf]

2 Настройка источника

[Alf]

2.1 Второе окно настройки источника

[Alf]

3 Выбор БД приемника

[Alf]

4. Настройка импорта

[Alf]

5 Тест импорта

[Alf]

6 Настройка шедулера из DTS

[Alf]

? проверка настройки в Jobs

[Alf]

Как реализовать отчет из dhtml?