Основная схема состоит в формирование данных для скула, с последующей ставкой (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
No comments:
Post a Comment