Diachok's IT blog: September 2011

Thursday, September 15, 2011

Последняя реализация индексации

SET NOCOUNT ON;

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
declare @datetime1 datetime;
declare @datetime2 datetime;
declare @time time;

declare @Rdatetime1 datetime;
declare @Rdatetime2 datetime;
declare @Rtime time;

DECLARE @p_body as nvarchar(max), @p_subject as nvarchar(max)
DECLARE @p_recipients as nvarchar(max), @p_profile_name as nvarchar(max)

SET @p_profile_name = N'sql'
SET @p_recipients = N'dyachok.nickolay@agromars.com; Oleynik@agromars.com'
SET @p_subject = N'Отчет по работе с индексами'
SET @Rdatetime1 = getdate()
SET @p_body = N'Начало отчета:' + CONVERT(nvarchar(30),@Rdatetime1,126)+N'<BR>'
BEGIN TRY
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
IF OBJECT_ID('#work_to_do') IS NOT NULL
    DROP TABLE #work_to_do;

SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 5.0 AND index_id > 0
order by avg_fragmentation_in_percent desc;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;
FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
-- Loop through the partitions.
WHILE (@@FETCH_STATUS = 0)
    BEGIN;
        
        --IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;
        --PRINT N'Execute: ' + @command
            -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'BEGIN TRY ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE END TRY BEGIN CATCH END CATCH;';
        IF @frag >= 30.0
            SET @command = N'BEGIN TRY ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD  WITH (ONLINE=ON) END TRY BEGIN CATCH ALTER INDEX '+@indexname+N' ON '+ @schemaname+N'.'+ @objectname+ N' REBUILD WITH (ONLINE=OFF) END CATCH;';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        set @datetime1 = getdate();
        EXEC (@command);
        set @datetime2 = getdate();
        set @time  = @datetime2-@datetime1;
        
        SET @p_body = @p_body + N'Executed: ' + @command+N' Затрачено время:'+CAST(@time as NvarChar(13))+N'<BR>';
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
END TRY
BEGIN CATCH
SET @p_body = @p_body + N'<BR>!!!!Ошибка выполнения:' + ERROR_NUMBER() + N' '+ERROR_SEVERITY() + N' '+ ERROR_STATE()+N' '+ERROR_LINE() + N' ' +ERROR_PROCEDURE() + N' '+ERROR_MESSAGE()
END CATCH

SET @Rdatetime2 = getdate() 
set @Rtime  = @Rdatetime2-@Rdatetime1;
SET @p_body = @p_body + N'Завершение отчета:' + CONVERT(nvarchar(30), @Rdatetime2, 126) + N' Время выполенения:' + CAST(@Rtime as NvarChar(13))+N'<BR>'

EXEC msdb.dbo.sp_send_dbmail
   @profile_name = @p_profile_name,
   @recipients = @p_recipients,
   @body = @p_body,
   @body_format = 'HTML',
   @subject = @p_subject

GO

Сайт для форматироваия кода http://www.manoli.net/csharpformat/. Умеет работать с C# и t-sql

Новая система по перестройке индексов, совместил скрипт микрософта и гилева, так как скрипт гилева перестал нормально отрабатывать. Опытным путем установил, что скорость работы базы зависит от частоты индексирования. Сейчас размер базы около 400 ГБ. Индексацию делаю каждый час после backup

Declare @ToFile Nvarchar(256);
Declare @Name Nvarchar(256);
use agroupp82;
set @ToFile=N'd:\Backup\'+db_name()+N'_'+CONVERT(nvarchar(10),getdate(),112)+REPLACE(CONVERT(nvarchar(8),GETDATE(),108),':','')+N'.trn'
set @Name=N'log_'+db_name()+N'_'+CONVERT(nvarchar(10),getdate(),112)+REPLACE(CONVERT(nvarchar(8),GETDATE(),108),':','')
BACKUP log AGROUPP82 TO  disk=@ToFile WITH COMPRESSION, NOFORMAT, NOINIT,  NAME=@Name, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Связано с тем что очень быстро набивается журнал логов. Ну и как итог сам скрипт:

use agroupp82;

SET NOCOUNT ON;

DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 
declare @datetime1 datetime;
declare @datetime2 datetime;
declare @time time;

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
IF OBJECT_ID('#work_to_do') IS NOT NULL
    DROP TABLE #work_to_do;

SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 5.0 AND index_id > 0
order by avg_fragmentation_in_percent desc;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;
FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
-- Loop through the partitions.
WHILE (@@FETCH_STATUS = 0)
    BEGIN;
        
        --IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

            -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'BEGIN TRY ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE END TRY BEGIN CATCH END CATCH;';
        IF @frag >= 30.0
            SET @command = N'BEGIN TRY ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD  WITH (ONLINE=ON) END TRY BEGIN CATCH ALTER INDEX '+@indexname+N' ON '+ @schemaname+N'.'+ @objectname+ N' REBUILD WITH (ONLINE=OFF) END CATCH;';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        set @datetime1 = getdate();
        --EXEC (@command);
        set @datetime2 = getdate();
        set @time  = @datetime2-@datetime1;
        PRINT N'Executed: ' + @command+N' Затрачено время:'+CAST(@time as NvarChar(13));
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

Friday, September 9, 2011

Расшифровка МТС звонков спомощью C# и regex.

В связи с анализом разговоров МТС необходимо было написать систему расшифровки звонком МТС, так как 1С очень медлено работает с текстовыми файлами пришлось решать эту проблему с помощью C# и regex(спасибо Linux).
Основная схема состоит в формирование данных для скула, с последующей ставкой (bulk load). Весь анализ выполнял уже в 1С 8.2.14.532

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO.Compression;
using System.IO;
using Ionic.Zip;
using System.Text.RegularExpressions;
using System.Data.SqlClient;



namespace WorkWithMTS
{

class Program
    {

        private static void DeleteData(string connectionString, string Table,string SelectDate)
        {
            string queryString =
                "Delete from " + Table + "  where [ReportDate]=Cast('" + SelectDate + "T00:00:00' as datetime)";
            using (SqlConnection connection = new SqlConnection(
                       connectionString))
            {
                SqlCommand command = new SqlCommand(
                    queryString, connection);
                connection.Open();
                command.CommandTimeout = 1000;
                command.ExecuteNonQuery();
                
            }
        }

        private static void UpdateDateCall(string connectionString, string Table)
        {
            string queryString =
                "UPDATE " + Table + " SET [DateCall]=[CallDate]+[CallTime]";
            using (SqlConnection connection = new SqlConnection(
                       connectionString))
            {
                SqlCommand command = new SqlCommand(
                    queryString, connection);
                connection.Open();
                command.CommandTimeout = 1000;
                command.ExecuteNonQuery();

            }
        }


        private static void BulkInsert(string connectionString, string Table, string filename)
        {
            string queryString =
                @"BULK INSERT " + Table + @"
                    FROM '"+filename+@"'
                    WITH 
                    (
                        CODEPAGE='1251',
                        FIELDTERMINATOR =',',
                        ROWTERMINATOR ='\n'
                    )";
            using (SqlConnection connection = new SqlConnection(
                       connectionString))
            {
                
                SqlCommand command = new SqlCommand(
                    queryString, connection);
                connection.Open();
                command.CommandTimeout = 1000;
                command.ExecuteNonQuery();
                 
            }
        }

static void Main(string[] args)
        {
            string dir = null;
            string cs = null;
            //string dir = @"g:\agromars\MTS\01\";
            if (args.Count() != 0)
            {
                dir = args[0];
            }

            if ((dir==null) || (dir.Length<2))
            {
                dir = Directory.GetCurrentDirectory()+@"\";
            }

if (!System.IO.Directory.Exists(dir))
            {
                Console.WriteLine("Папка не обнаружена!\n");
                System.Console.ReadKey();
                return;
            }


            string[] filePaths = Directory.GetFiles(dir, "*.csv");

            var sortfilePaths = from s in filePaths
                                orderby s
                                select s;

            if (filePaths.Count() == 0)
            {
                Console.WriteLine("Файлы для обработки не обнаружены!!!");
                System.Console.ReadKey();
                return;
            }
            //cs = "Data Source=.;Initial Catalog=Phone_MTS;User Id=userMTS;Password=userMTS;";
            //DeleteData(cs, "[Phone_MTS].[dbo].[Services]", "2011-01-31");

            //delete tmp file
            string[] filestemp = Directory.GetFiles(dir, "*.tmp");
            foreach (string filetemp in filestemp)
            {
                FileInfo filestodel = new FileInfo(filetemp);
                filestodel.Delete();
            }

FileStream fin;
            FileStream foutH;
            FileStream foutHPacket;
            FileStream foutS;
            FileStream foutSMsg;
            FileStream foutSInet;
            FileStream foutSOth;
            FileStream foutTrash = new FileStream(dir +  "Trash.tmp", FileMode.Append);;

            StreamWriter fstr_outH = null;
            StreamWriter fstr_outHPacket = null;
            StreamWriter fstr_outS = null;
            StreamWriter fstr_outSMsg = null;
            StreamWriter fstr_outSInet = null;
            StreamWriter fstr_outSOth = null;
            StreamWriter fstr_outTrash = new StreamWriter(foutTrash);

            string str;
            string Contract = null, PhoneNumber = "", NameDate = "", NameDateS=null, ORah=null;


            Regex rstr = new Regex(@"^.*,.*,.*,.*,(?<valuephone>.*),\d{4}\-\d{2}\-\d{2},\s*\d{2}:\d{2}:\d{2},\d{2}:\d{2}:\d{2},.*,[0-9-\.]*$"); //Ищем строки документа звонки
            Regex rstrOth = new Regex(@"^.*,.*,.*,.*,.*,\d{4}\-\d{2}\-\d{2},\s*\d{2}:\d{2}:\d{2},.*,.*,[0-9-\.]*$"); //Ищем строки документа остальные
            Regex rstrMsg = new Regex(@"^.*,.*,.*,.*,.*,\d{4}\-\d{2}\-\d{2},\s*\d{2}:\d{2}:\d{2},.*Msg,.*,[0-9-\.]*$"); //Ищем строки документа Msg
            Regex rstrInet = new Regex(@"^.*,.*,.*,.*,.*,\d{4}\-\d{2}\-\d{2},\s*\d{2}:\d{2}:\d{2},\s*[0-9\.]*\s*(b|K|M|Kb|Mb),.*,[0-9-\.]*$"); //Ищем строки документа Inet

            Regex rH = new Regex(@"^(?<value>.*),.*,.*,[0-9-\.]*$"); //Ищем шапки документа

foreach (string filepath in sortfilePaths)
            {
                
                try
                {
                    fin = new FileStream(filepath, FileMode.Open); // чтение файла
                }
                catch (FileNotFoundException ex)
                {

                    Console.WriteLine("Ошибка:" + ex.Message);
                    System.Console.ReadKey();
                    return;
                }

                StreamReader fstr_in = new StreamReader(fin, Encoding.Default);

while ((str = fstr_in.ReadLine()) != null)
                {

                    //ищем дату
                    Regex r = new Regex(@"Номер рахунку\s*\d*\s*від\s*(?<valuedate>[0-9]{1,2}[.][0-9]{1,2}[.][0-9]{4})");
                    // Find a single match in the string.
                    Match m = r.Match(str);
                    if ((m.Success) && (NameDate != m.Groups["valuedate"].Value))
                    {
                        NameDate = m.Groups["valuedate"].Value;
                        NameDateS = Convert.ToDateTime(NameDate).ToString("yyyy-MM-dd");
                        Contract = null;
                        if (fstr_outS == null)
                        {
                            //if (fstr_outS != null)
                            //{
                            //    fstr_outS.Close();
                            //    fstr_outSMsg.Close();
                            //    fstr_outSInet.Close();
                            //    fstr_outSOth.Close();                                
                            //}
                            try
                            {
                                foutS = new FileStream(dir + NameDate + "_Str.tmp", FileMode.Append);                                
                                foutSMsg = new FileStream(dir + NameDate + "_StrMsg.tmp", FileMode.Append);
                                foutSInet = new FileStream(dir + NameDate + "_StrInet.tmp", FileMode.Append);
                                foutSOth = new FileStream(dir + NameDate + "_StrOth.tmp", FileMode.Append);
                            }
                            catch (IOException ex)
                            {
                                Console.WriteLine("Ошибка:" + ex.Message);
                                System.Console.ReadKey();
                                return;
                            }
                            fstr_outS = new StreamWriter(foutS, Encoding.GetEncoding(1251));
                            fstr_outSMsg = new StreamWriter(foutSMsg, Encoding.GetEncoding(1251));
                            fstr_outSInet = new StreamWriter(foutSInet, Encoding.GetEncoding(1251));
                            fstr_outSOth = new StreamWriter(foutSOth, Encoding.GetEncoding(1251)); 
                        }

                        if (fstr_outH == null)
                        {
                            try
                            {
                                foutH = new FileStream(dir + NameDate + "_Head.tmp", FileMode.Append);
                                foutHPacket = new FileStream(dir + NameDate + "_HeadPacket.tmp", FileMode.Append);
                                
                            }
                            catch (IOException ex)
                            {
                                Console.WriteLine("Ошибка:" + ex.Message);
                                System.Console.ReadKey();
                                return;
                            }
                            fstr_outH = new StreamWriter(foutH,Encoding.GetEncoding(1251));
                            fstr_outHPacket = new StreamWriter(foutHPacket, Encoding.GetEncoding(1251));
                        }

                    }

//Если контракты
                    Regex rORah = new Regex(@"Особовий рахунок:\s*(?<value>\w.*)");
                    // Find a single match in the string.
                    Match mORah = rORah.Match(str);
                    if (mORah.Success)
                    {
                        ORah = mORah.Groups["value"].Value;                      
                        //Console.Write(str);
                    }


                    //Если контракты
                    Regex r1 = new Regex(@"Контракт\s*№\s*(?<value>\d*)");
                    // Find a single match in the string.
                    Match m1 = r1.Match(str);
                    if (m1.Success)
                    {
                        Contract = m1.Groups["value"].Value;
                       Regex rch = new Regex(@"Контракт\s*№\s*(?<value>\d*)\s*Номер телефону:\s*(?<phonevalue>\d*)");
                       Match mch = rch.Match(str);
                       if (mch.Success)
                       {
                           PhoneNumber = mch.Groups["phonevalue"].Value;
                       }
                       else
                       {
                           PhoneNumber = "";
                       }
                        
                        Console.WriteLine(str);                                                
                    }
                    int strCount = str.ToCharArray().Where((x) => x == ',').Count();
                    //Вычисляем пакеты данных //Ціновий пакет:
                    if ((Contract != null) && (fstr_outH != null))
                    {
                        Regex rPacket = new Regex(@"Ціновий пакет:\s*(?<value>\w.*)");
                        Match mPacket = rPacket.Match(str);
                        if (mPacket.Success)
                        {
                            fstr_outHPacket.WriteLine(ORah + "," + Contract + "," + PhoneNumber + "," + NameDateS + "," + mPacket.Groups[1].Value.Trim());
                        }
                        
                    }

                    if ((Contract != null) && (fstr_outH != null) && ((strCount >= 3) && (strCount<9)))
                    {
                        try
                        {
                            
                            Match mH = rH.Match(str);
                            if (mH.Success)
                            {
                                string strService = mH.Groups["value"].Value.Replace(",", ".");
                                //str = Regex.Replace(str, mH.Groups["value"].Value, strService);
                                str = str.Replace(mH.Groups["value"].Value, strService);

                                fstr_outH.WriteLine(ORah + "," + Contract + "," + PhoneNumber + "," + NameDateS + "," + str);
                                continue;
                            }
                            Regex rHOther = new Regex(@"^"".*"",.*,.*,[0-9-\.]*$"); //Ищем шапки документа, с неправильной структурой
                            Match mHOther = rHOther.Match(str);
                            if (mHOther.Success)
                            {
                                fstr_outH.WriteLine(ORah + "," + Contract + "," + PhoneNumber + "," + NameDateS + "," + str);
                                continue;
                            }

                            Console.WriteLine("Внимание:" + ORah + "," + Contract + "," + PhoneNumber + "," + NameDateS + "," + str);

                        }
                        catch (IOException ex)
                        {
                            Console.WriteLine("Ошибка:" + ex.Message);
                            System.Console.ReadKey();
                            return;
                        }
                    }


                    if ((Contract != null) && (fstr_outS != null) && (strCount == 9))
                    {
                        // конвертим дату для bulk
                        Regex rTDate = new Regex(@"([\d]{2})\.([\d]{2})\.([\d]{4})");
                        str = rTDate.Replace(str, @"$3-$2-$1");
 

                        try
                        {
                             Match mstrMsg = rstrMsg.Match(str);
                            if (mstrMsg.Success) 
                            {
                                // Убираем Msg
                                Regex rTMsg = new Regex(@"([\d]*)\s*Msg");
                                str = rTMsg.Replace(str, @"$1");
                                fstr_outSMsg.WriteLine(ORah + "," + Contract + "," + PhoneNumber + "," + NameDateS + "," + str + ",");
                                continue;
                            }
                            Match mstrInet = rstrInet.Match(str); 
                            if (mstrInet.Success)
                            {
                                // Пересчитываем в байты
                                //\s*(?<valuestring>\w*)
                                Regex rT1 = new Regex(@"(?<valuenumber>[0-9\.]+)\s+(?<valuestring>b|Kb|Mb|M|K)");
                                Match mrT1 = rT1.Match(str);
                                string strbyte = null;
                                if (mrT1.Success)
                                {
                                    decimal tbyte = Convert.ToDecimal(mrT1.Groups["valuenumber"].Value.Replace(".",","));
                                    if (mrT1.Groups[2].Value == "b")
                                        strbyte = tbyte.ToString();
                                    else if ((mrT1.Groups[2].Value == "Kb") || (mrT1.Groups[2].Value == "K"))
                                        strbyte = Convert.ToInt64(tbyte * 1024).ToString();
                                    else if ((mrT1.Groups[2].Value == "Mb") || (mrT1.Groups[2].Value == "M"))
                                        strbyte = Convert.ToInt64(tbyte * 1024 * 1024).ToString();
                                    else if ((mrT1.Groups[2].Value == "Gb") || (mrT1.Groups[2].Value == "G"))
                                        strbyte = Convert.ToInt64(tbyte * 1024 * 1024 * 1024).ToString();
                                    else if (mrT1.Groups[2].Value != "")
                                        Console.WriteLine("Ошибка:" + Contract + "," + PhoneNumber + "," + NameDateS + "," + str);

                                    Regex rT2 = new Regex(@"(?<valuenumber>[0-9\.]+)\s+(?<valuestring>b|Kb|Mb|M|K)");
                                    str = rT2.Replace(str, strbyte);
                                }
                                fstr_outSInet.WriteLine(ORah + "," + Contract + "," + PhoneNumber + "," + NameDateS + "," + str + ",");
                                continue;
                            }
                            Match mstr = rstr.Match(str);
                            if (mstr.Success)
                            {
                                string direction = "";//mstr.Groups[1].Value.ToString();
                                Regex rD = new Regex(@"\.*(?<valuenumber>[0-9A-Za-z]*)(?=[0-9A-Za-z]{7,})"); // \.*([0-9A-Za-z]*)(?=[0-9A-Za-z]{7,}) // (?<valuenumber>.*)(?=\d{7,})
                                Match mrD = rD.Match(str);
                                if (mrD.Success)
                                {
                                    direction = mrD.Groups[1].Value.Trim();
                                    if ((direction.Length > 3) && ((direction.IndexOf("38", 0) != -1) || (direction.IndexOf("15", 0) != -1) || (direction.StartsWith("0"))))
                                    {
                                        int MaxLength = Math.Min(direction.Length, 6);
                                        direction = direction.Substring(direction.Length - MaxLength, MaxLength).Trim();
                                        direction = direction.Substring(direction.Length - 3, 3);
                                    }
                                    else if (direction.Length == 2)
                                        direction = "0" + direction;
                                    else if ((direction.Length == 4) && direction.StartsWith("8"))
                                    {
                                        
                                        direction = direction.Substring(1, 3);
                                    }
                                    else if (direction.Length > 3)
                                    {
                                        direction = "";
                                    }
                                }
                                //direction=direction.Substring(direction.Length-10, 10).Substring(0,3);


                                // Пересчитываем в секунды
                                //\s*(?<valuestring>\w*)

                                int sec = 0;
                                Regex rTS = new Regex(@"\d{4}\-\d{2}\-\d{2},.*\d{2}:\d{2}:\d{2},.*(\d{2}):(\d{2}):(\d{2}),");
                                Match mrTS = rTS.Match(str);
                                
                                if (mrTS.Success)
                                {
                                    int tsec = Convert.ToInt16(mrTS.Groups[3].Value);
                                    int tmin = Convert.ToInt16(mrTS.Groups[2].Value);
                                    int thour = Convert.ToInt16(mrTS.Groups[1].Value);

                                    sec = thour * 60 * 60 + tmin * 60 + tsec;

                                    //Regex rT2S = new Regex(@"\d{4}\.\d{2}\.\d{2},.*\d{2}:\d{2}:\d{2},.*(\d{2}):(\d{2}):(\d{2}),");
                                    //str = rT2S.Replace(str, strbyte);
                                }

                                string strToPhone = "";
                                Regex rPhone = new Regex(@"\d*(?=(\d{9}))");
                                Match mrPhone = rPhone.Match(str);

                                if (mrPhone.Success)
                                {

                                    strToPhone = mrPhone.Groups[1].Value;
                                }


                                fstr_outS.WriteLine(ORah + "," + Contract + "," + PhoneNumber + "," + NameDateS + "," + direction + "," + strToPhone + "," + sec + "," + str + ",");
                                continue;
                            }
                            Match mstrOth = rstrOth.Match(str);
                            if (mstrOth.Success)
                            {
                                fstr_outSOth.WriteLine(ORah + "," + Contract + "," + PhoneNumber + "," + NameDateS + "," + str + ",");
                                continue;
                            }
                            Console.WriteLine("Внимание:" + ORah + "," + Contract + "," + PhoneNumber + "," + NameDateS + "," + str);
                            
                        }
                        catch (IOException ex)
                        {
                            Console.WriteLine("Ошибка:"+ex.Message);
                            System.Console.ReadKey();
                            return;
                        }
                    }
                    fstr_outTrash.WriteLine(str);

                }
                fstr_in.Close();
                
            }

fstr_outS.Close();
            fstr_outSMsg.Close();           
            fstr_outSInet.Close();
            fstr_outSOth.Close();
            fstr_outH.Close();
            fstr_outHPacket.Close();
            fstr_outTrash.Close();



            Console.WriteLine("Загрузка в SQL");
            
            if (args.Count() > 0)
            {
                cs = args[1];
            }
            if ((cs == null) || (cs.Length == 0))
            {
                cs = "Data Source=.;Initial Catalog=Phone_MTS;User Id=userMTS;Password=userMTS;";
            }
            DeleteData(cs, "[Phone_MTS].[dbo].[Services]", NameDateS);
            DeleteData(cs, "[Phone_MTS].[dbo].[Packages]", NameDateS);
            DeleteData(cs, "[Phone_MTS].[dbo].[ExplanationCalls]", NameDateS);
            DeleteData(cs, "[Phone_MTS].[dbo].[ExplanationInet]", NameDateS);
            DeleteData(cs, "[Phone_MTS].[dbo].[ExplanationMsg]", NameDateS);
            DeleteData(cs, "[Phone_MTS].[dbo].[ExplanationOther]", NameDateS);


            BulkInsert(cs, "[Phone_MTS].[dbo].[Services]", dir + NameDate + "_Head.tmp");
            BulkInsert(cs, "[Phone_MTS].[dbo].[Packages]", dir + NameDate + "_HeadPacket.tmp");
            BulkInsert(cs, "[Phone_MTS].[dbo].[ExplanationCalls]", dir + NameDate + "_Str.tmp");
            BulkInsert(cs, "[Phone_MTS].[dbo].[ExplanationInet]", dir + NameDate + "_StrInet.tmp");
            BulkInsert(cs, "[Phone_MTS].[dbo].[ExplanationMsg]", dir + NameDate + "_StrMsg.tmp");
            BulkInsert(cs, "[Phone_MTS].[dbo].[ExplanationOther]", dir + NameDate + "_StrOth.tmp");

            UpdateDateCall(cs, "[Phone_MTS].[dbo].[ExplanationCalls]");
            UpdateDateCall(cs, "[Phone_MTS].[dbo].[ExplanationInet]");
            UpdateDateCall(cs, "[Phone_MTS].[dbo].[ExplanationMsg]");
            UpdateDateCall(cs, "[Phone_MTS].[dbo].[ExplanationOther]");

            Console.WriteLine("Выполнено! Press any key!");
            System.Console.ReadKey();
        }
    }

}

После формируем таблицы в скуле
Таблица сервисов

CREATE TABLE [dbo].[Services](
    [Rahunok] [nchar](20) NOT NULL,
    [Contract] [nchar](10) NOT NULL,
    [Phone] [nchar](9) NULL,
    [ReportDate] [datetime]  NOT NULL,
    [NameServices] [nvarchar](250) NULL,    
    [Sum1] [decimal](12, 2) NULL,
    [Sum2] [decimal](12, 2) NULL,
    [Sum3] [decimal](12, 2) NULL        
) ON [PRIMARY]

GO

Таблица пакетов

CREATE TABLE [dbo].[Packages](
    [Rahunok] [nchar](20) NOT NULL,
    [Contract] [nchar](10) NOT NULL,
    [Phone] [nchar](9) NULL,
    [ReportDate] [datetime] NOT NULL,
    [NamePackege] [nvarchar](250) NULL
    
) ON [PRIMARY]

GO

Таблица операторов

CREATE TABLE [dbo].[Operators](
    [OperatorName] [nvarchar](50) NOT NULL,
    [Direction] [nchar](3) NOT NULL
) ON [PRIMARY]

GO

В которую вносим следующие данные

OperatorName Direction
Golden Telecom 039
life:) 063
life:) 093
PEOPLEnet 092
Utel 091
Интертелеком 094
Киевстар 067
Киевстар 068
Киевстар 096
Киевстар 097
Киевстар 098
МТС 050
МТС 066
МТС 095
МТС 099

Далее таблица по расшифровке звонков

CREATE TABLE [dbo].[ExplanationCalls](
    [Rahunok] [nchar](20) NOT NULL,
    [Contract] [nchar](10) NOT NULL,
    [Phone] [nchar](9) NULL,
    [ReportDate] [datetime] NULL,
    [Direction] [nchar](3) NULL,
    [ToPhone] [nchar](9) NULL,
    [Duration] [bigint] NULL,
    [Unknown1] [nvarchar](50) NULL,
    [Unknown2] [nvarchar](50) NULL,
    [Type] [nvarchar](18) NULL,
    [Info] [nvarchar](20) NULL,
    [Call] [nvarchar](15) NULL,
    [CallDate] [datetime] NULL,
    [CallTime] [time](7) NULL,
    [CallDuration] [time](7) NULL,
    [Unknown3] [nvarchar](50) NULL,
    [Cost] [decimal](12, 2) NULL,    
    [DateCall] [datetime] NULL -- Для формирования точной даты звонка необходим для 1с, что бы не заниматься лишней обработкой
    
) ON [PRIMARY]

GO

Таблица расшифровки интернет трафика

CREATE TABLE [dbo].[ExplanationInet](
    [Rahunok] [nchar](20) NOT NULL,
    [Contract] [nchar](10) NOT NULL,
    [Phone] [nchar](9) NULL,
    [ReportDate] [datetime] NULL,
    [Unknown1] [nvarchar](50) NULL,
    [Unknown2] [nvarchar](50) NULL,
    [Type] [nvarchar](18) NULL,
    [Info] [nvarchar](20) NULL,
    [Call] [nvarchar](15) NULL,
    [CallDate] [datetime] NULL,
    [CallTime] [time](7) NULL,
    [CallDuration] [bigint] NULL,
    [Unknown3] [nvarchar](50) NULL,
    [Cost] [decimal](12, 2) NULL,
    [DateCall] [datetime] NULL -- Для формирования точной даты звонка необходим для 1с, что бы не заниматься лишней обработкой
) ON [PRIMARY]

GO

Таблица смс

CREATE TABLE [dbo].[ExplanationMsg](
    [Rahunok] [nchar](20) NOT NULL,
    [Contract] [nchar](10) NOT NULL,
    [Phone] [nchar](9) NULL,
    [ReportDate] [datetime] NULL,
    [Unknown1] [nvarchar](50) NULL,
    [Unknown2] [nvarchar](50) NULL,
    [Type] [nvarchar](18) NULL,
    [Info] [nvarchar](20) NULL,
    [Call] [nvarchar](15) NULL,
    [CallDate] [datetime] NULL,
    [CallTime] [time](7) NULL,
    [CallDuration] [bigint] NULL,
    [Unknown3] [nvarchar](50) NULL,
    [Cost] [decimal](12, 2) NULL,
    [DateCall] [datetime] NULL -- Для формирования точной даты звонка необходим для 1с, что бы не заниматься лишней обработкой
) ON [PRIMARY]

GO

И последняя таблица со всяким мусором который не расшифровался

CREATE TABLE [dbo].[ExplanationOther](
    [Rahunok] [nchar](20) NOT NULL,
    [Contract] [nchar](10) NOT NULL,
    [Phone] [nchar](9) NULL,
    [ReportDate] [datetime] NULL,
    [Unknown1] [nvarchar](50) NULL,
    [Unknown2] [nvarchar](50) NULL,
    [Type] [nvarchar](18) NULL,
    [Info] [nvarchar](20) NULL,
    [Call] [nvarchar](15) NULL,
    [CallDate] [datetime] NULL,
    [CallTime] [time](7) NULL,
    [CallDuration] [nvarchar](8) NULL,
    [Unknown3] [nvarchar](50) NULL,
    [Cost] [decimal](12, 2) NULL,
    [DateCall] [datetime] NULL
) ON [PRIMARY]

GO