private void btnCompare_Click(object sender, EventArgs e)
{
bool ifOnlyCompareDistNum = false;
if (checkBox1.Checked == true)
{
ifOnlyCompareDistNum = true;
}
if (string.IsNullOrEmpty(comboBox1.Text) || string.IsNullOrEmpty(comboBox2.Text))
{
MessageBox.Show("Pls select format", "CDRcompare");
return;
}
int timeRange = 0;
if (CanCovert(textBox3.Text, typeof(System.Int32)))
{
timeRange = Convert.ToInt32(textBox3.Text);
}
else
{
MessageBox.Show("Pls input correct time range", "CDRcompare");
return;
}
//存右边哈希值的Dictionary
Dictionary <int, List <int> > dForSearchListRight = new Dictionary <int, List <int> >();
List <CDRItem> listLeft = new List <CDRItem>();
btnCompare.Enabled = false;
label6.Text = "Loading...";
ultraActivityIndicator1.Start(true);
try
{
if (comboBox1.Text == "WHC format")
{
listLeft = FormatLoad.importExcelUsingNetvantageWHC(textBox1.Text, Convert.ToInt32(textBox4.Text), Convert.ToInt32(textBox5.Text), Convert.ToInt32(textBox6.Text), Convert.ToInt32(textBox7.Text));
}
if (comboBox1.Text == "ashan format")
{
listLeft = FormatLoad.importExcelUsingNetvantageAshan(textBox1.Text, Convert.ToInt32(textBox4.Text), Convert.ToInt32(textBox5.Text), Convert.ToInt32(textBox6.Text), Convert.ToInt32(textBox7.Text));
}
if (comboBox1.Text == "Umobile format")
{
listLeft = FormatLoad.importExcelUsingNetvantageUmobile(textBox1.Text, Convert.ToInt32(textBox4.Text), Convert.ToInt32(textBox5.Text), Convert.ToInt32(textBox6.Text), Convert.ToInt32(textBox7.Text));
}
}
catch (Exception loade)
{
MessageBox.Show(loade.Message);
}
if (listLeft == null)
{
MessageBox.Show("error format");
btnCompare.Enabled = true;
return;
}
List <CDRItem> listRight = new List <CDRItem>();
try
{
if (comboBox2.Text == "WHC format")
{
listRight = FormatLoad.importExcelUsingNetvantageWHC(textBox2.Text, Convert.ToInt32(textBox8.Text), Convert.ToInt32(textBox9.Text), Convert.ToInt32(textBox10.Text), Convert.ToInt32(textBox11.Text));
}
if (comboBox2.Text == "ashan format")
{
listRight = FormatLoad.importExcelUsingNetvantageAshan(textBox2.Text, Convert.ToInt32(textBox8.Text), Convert.ToInt32(textBox9.Text), Convert.ToInt32(textBox10.Text), Convert.ToInt32(textBox11.Text));
}
if (comboBox2.Text == "Umobile format")
{
listRight = FormatLoad.importExcelUsingNetvantageUmobile(textBox2.Text, Convert.ToInt32(textBox8.Text), Convert.ToInt32(textBox9.Text), Convert.ToInt32(textBox10.Text), Convert.ToInt32(textBox11.Text));
}
}
catch (Exception loade)
{
MessageBox.Show(loade.Message);
}
if (listRight == null)
{
btnCompare.Enabled = true;
MessageBox.Show("error format");
return;
}
//写 dForSearchListRight
for (int ii = 0; ii < listRight.Count; ii++)
{
CDRItem ci = listRight[ii];
string sRowText = "";
int iHash = 0;
if (ifOnlyCompareDistNum == false)
{
sRowText = sRowText + ci.ani;
sRowText = sRowText + ci.dest;
}
else
{
sRowText = ci.dest;
}
//MessageBox.Show(sRowText);
iHash = sRowText.GetHashCode();
if (dForSearchListRight.ContainsKey(iHash))
{
dForSearchListRight[iHash].Add(ii);
}
else
{
dForSearchListRight.Add(iHash, new List <int>());
dForSearchListRight[iHash].Add(ii);
}
}
//--------------------------
Application.DoEvents();
label6.Text = "Comparing...";
//ultraActivityIndicator1.Start(true);
//hash Dictionary already ok ,now start search matching record,
//first search record in left but not in right
int iDeleteCount = 0;
List <int> listToBeDeleteLeft = new List <int>();
string filePath = "compare_result.csv";
for (int ii = 0; ii < listLeft.Count; ii++)
{
string strExportSame = "";
string sRowText = "";
int iHash = 0;
CDRItem ci = listLeft[ii];
strExportSame = ci.ani.ToString() + "," + ci.dest.ToString() + "," + ci.start.ToString() + "," + ci.duration.ToString() + ",";
if (ifOnlyCompareDistNum == false)
{
sRowText = sRowText + ci.ani;
sRowText = sRowText + ci.dest;
}
else
{
sRowText = ci.dest;
}
//MessageBox.Show(sRowText);
iHash = sRowText.GetHashCode();
if (dForSearchListRight.ContainsKey(iHash))
{
double minDurationDiff = 0.0;
double minDiffDuration = 0.0;
string minDiffAnum = null;
string minDiffBnum = null;
DateTime minDiffConnecttime = DateTime.MinValue;
bool exactMatch = false;
bool mohuMatch = false;
int recordElement = 0;
foreach (int element in dForSearchListRight[iHash]) //loop A
{
//是否通过时间和duration检查
//if ((compareDateTime(ci.start, listRight[element].start, timeRange) == true))
if ((compareDateTime(ci.start.AddHours(Convert.ToDouble(textBox12.Text)), listRight[element].start.AddHours(Convert.ToDouble(textBox13.Text)), timeRange) == true))
{
if (System.Math.Abs(ci.duration - listRight[element].duration) < 10.0)
{
strExportSame = strExportSame + listRight[element].ani.ToString() + "," + listRight[element].dest.ToString() + "," + listRight[element].start.ToString() + "," + listRight[element].duration.ToString();
//File.AppendAllText(filePath, strExportSame);
//File.AppendAllText(filePath, strExportSame);
listToBeDeleteLeft.Add(element);
//在dictionary的list删除此条找到的纪录
dForSearchListRight[iHash].Remove(element);
iDeleteCount++;
exactMatch = true;
break; //break 是跳出loop A
}
//if ((System.Math.Abs(ci.duration - listRight[element].duration) > 10.0) && (System.Math.Abs(ci.duration - listRight[element].duration) < 20)) //时间差距大于10
//{
// strExportSame = strExportSame + listRight[element].ani.ToString() + "," + listRight[element].dest.ToString() + "," + listRight[element].start.ToString() + "," + listRight[element].duration.ToString() + "," + "same a b num but duration diff GT 10";
// //File.AppendAllText(filePath, strExportSame);
// //File.AppendAllText(filePath, strExportSame);
// listToBeDeleteLeft.Add(element);
// //在dictionary的list删除此条找到的纪录
// dForSearchListRight[iHash].Remove(element);
// //iDeleteCount++; 这里不能加 ，因为不是完全相等的记录
// break;
//}
if ((System.Math.Abs(ci.duration - listRight[element].duration) > 10.0) && (System.Math.Abs(ci.duration - listRight[element].duration) < 30.0))
{
if (minDurationDiff == 0.0)
{
minDurationDiff = System.Math.Abs(ci.duration - listRight[element].duration);
minDiffAnum = listRight[element].ani;
minDiffBnum = listRight[element].dest;
minDiffConnecttime = listRight[element].start;
minDiffDuration = listRight[element].duration;
recordElement = element;
mohuMatch = true;
continue;
}
if (System.Math.Abs(ci.duration - listRight[element].duration) < minDurationDiff)
{
minDurationDiff = System.Math.Abs(ci.duration - listRight[element].duration);
minDiffAnum = listRight[element].ani;
minDiffBnum = listRight[element].dest;
minDiffConnecttime = listRight[element].start;
minDiffDuration = listRight[element].duration;
recordElement = element;
mohuMatch = true;
continue;
}
}
}
} //loop A
if ((!exactMatch) && (mohuMatch))
{
strExportSame = strExportSame + minDiffAnum + "," + minDiffBnum + "," + minDiffConnecttime.ToString() + "," + minDiffDuration.ToString() + "," + "same a b num but duration diff GT 10";
listToBeDeleteLeft.Add(recordElement);
dForSearchListRight[iHash].Remove(recordElement);
}
strExportSame = strExportSame + "\r\n";
File.AppendAllText(filePath, strExportSame);
}
else
{
strExportSame = strExportSame + "\r\n";
File.AppendAllText(filePath, strExportSame);
}
} //left--->right search ok
if (MessageBox.Show("Search finished \r\nfrom left to right find " + iDeleteCount.ToString() + " same records\r\nDo you want to export right_only record? ", "CDRcompare", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
//delete right record
listToBeDeleteLeft.Sort();
listToBeDeleteLeft.Reverse();
for (int ii = 0; ii < listToBeDeleteLeft.Count; ii++)
{
listRight.RemoveAt(listToBeDeleteLeft[ii]);
}
for (int ii = 0; ii < listRight.Count; ii++)
{
string strExportRemain = "";
strExportRemain = ",,,," + listRight[ii].ani.ToString() + "," + listRight[ii].dest.ToString() + "," + listRight[ii].start.ToString() + "," + listRight[ii].duration.ToString() + "\r\n";
File.AppendAllText(filePath, strExportRemain);
}
MessageBox.Show("Export finished");
Application.DoEvents();
btnCompare.Enabled = true;
label6.Text = "Finished";
ultraActivityIndicator1.Stop(true);
}
btnCompare.Enabled = true;
label6.Text = "Finished";
ultraActivityIndicator1.Stop(true);
}
//
public static List <CDRItem> importExcelUsingNetvantageAshan(string strFilePath, int iSrc, int iDist, int iConTime, int iDuration)
{
//要进行的处理
//1：去掉distNum前缀的70743 ok
//2：srcNum如果等于asterisk，改为空 ok
//3：srcNum如果等于数字，去掉前面多余的0，例如0080 改为80 ok
//4：支持多个worksheet ok
List <CDRItem> list = new List <CDRItem>();
//----------to use this function ,remember to add reference :using Infragistics.Documents.Excel;
if (File.Exists(strFilePath))
{
//Load the Excel File into the Workbook Object
//Application.DoEvents();
Workbook theWorkbook = Workbook.Load(strFilePath, false);
for (int jj = 0; jj < theWorkbook.Worksheets.Count; jj++)
{
Worksheet theWorksheet = theWorkbook.Worksheets[jj];
int theRowCounter = 0;
//Iterate through all Worksheet rows Iterate through
foreach (WorksheetRow theWorksheetRow in theWorksheet.Rows)
{
if (theRowCounter == 0)
{
//check file format
//string strHeadArray = "";
//foreach (WorksheetCell theWorksheetCell in theWorksheetRow.Cells)
//{
// strHeadArray = strHeadArray + theWorksheetCell.Value.ToString().Trim() + ",";
//}
//strHeadArray = strHeadArray.Substring(0, strHeadArray.Length - 1);
////if (strHeadArray != "start time,duration,CalledIP,Callout callerID,Callout calledNum")
//if (!strHeadArray.Equals("start time,duration,CalledIP,Callout callerID,Callout calledNum", StringComparison.OrdinalIgnoreCase))
//{
// return null;
//}
theRowCounter++;
continue;
}
else
//This is the actual data that will populate the data model
{
CDRItem ci = new CDRItem();
try
{
ci.ani = (theWorksheetRow.Cells[iSrc].Value != null) ? theWorksheetRow.Cells[iSrc].Value.ToString() : "";
if (ci.ani == "asterisk")
{
ci.ani = "";
}
Int64 l1;
Int64.TryParse(ci.ani, out l1);
if (l1 != 0)
{
ci.ani = l1.ToString();
}
ci.dest = (theWorksheetRow.Cells[iDist].Value != null) ? theWorksheetRow.Cells[iDist].Value.ToString() : "";
if (ci.dest.Length > 5)
{
ci.dest = ci.dest.Substring(5);
}
if (theWorksheetRow.Cells[iConTime].Value.GetType() == typeof(double))
{
ci.start = DateTime.FromOADate((double)theWorksheetRow.Cells[iConTime].Value);
}
else
{
ci.start = DateTime.Parse(theWorksheetRow.Cells[iConTime].Value.ToString());
}
ci.duration = Convert.ToDouble(theWorksheetRow.Cells[iDuration].Value);
}
catch (System.OverflowException tep)
{
MessageBox.Show("stack over flow" + tep.Message);
}
list.Add(ci);
}
theRowCounter++;
} //loop worksheet's row
} //loop worksheet
}
else
{
MessageBox.Show("No such file was found!");
}
return(list);
}
//
public static List <CDRItem> importExcelUsingNetvantageUmobile(string strFilePath, int iSrc, int iDist, int iConTime, int iDuration)
{
List <CDRItem> list = new List <CDRItem>();
//----------to use this function ,remember to add reference :using Infragistics.Documents.Excel;
if (File.Exists(strFilePath))
{
//Load the Excel File into the Workbook Object
//Application.DoEvents();
Workbook theWorkbook = Workbook.Load(strFilePath, false);
//We will only work with the first Worksheet in the Workbook -- 只支持第一个WorkSheet
Worksheet theWorksheet = theWorkbook.Worksheets[0];
int theRowCounter = 0;
//Iterate through all Worksheet rows Iterate through
foreach (WorksheetRow theWorksheetRow in theWorksheet.Rows)
{
if (theRowCounter == 0)
{
//check file format
//string strHeadArray = "";
//foreach (WorksheetCell theWorksheetCell in theWorksheetRow.Cells)
//{
// strHeadArray = strHeadArray + theWorksheetCell.Value.ToString().Trim() + ",";
//}
//strHeadArray = strHeadArray.Substring(0, strHeadArray.Length - 1);
////event_start_date event_start_time anum bnum event_duration billing_operator
//if (strHeadArray != "event_start_date,event_start_time,anum,bnum,event_duration,billing_operator")
//{
// return null;
//}
theRowCounter++;
continue;
}
else
//This is the actual data that will populate the data model
{
CDRItem ci = new CDRItem();
try
{
ci.ani = (theWorksheetRow.Cells[iSrc].Value != null) ? theWorksheetRow.Cells[iSrc].Value.ToString() : "";
ci.dest = (theWorksheetRow.Cells[iDist].Value != null) ? theWorksheetRow.Cells[iDist].Value.ToString() : "";
//ci.dest = "60" + ci.dest;
if (ci.dest.Length > 3)
{
ci.dest = ci.dest.Substring(3);
}
//取日期
if (theWorksheetRow.Cells[iConTime].Value.GetType() == typeof(double))
{
ci.start = DateTime.FromOADate((double)theWorksheetRow.Cells[iConTime].Value);
}
else
{
//ci.start = DateTime.ParseExact(theWorksheetRow.Cells[iConTime].Value.ToString(), "dd/MM/yy", new CultureInfo("en-US"));
//ci.start = DateTime.ParseExact(theWorksheetRow.Cells[iConTime].Value.ToString(), "dd/MM/yy", new CultureInfo("en-US"));
ci.start = DateTime.Parse(theWorksheetRow.Cells[iConTime].Value.ToString());
//年和日互换
int iyear = ci.start.Year;
int imonth = ci.start.Month;
int iday = ci.start.Day;
ci.start = new DateTime(iday + 2000, imonth, iyear - 2000);
}
//ci.start = DateTime.Parse(theWorksheetRow.Cells[iConTime].Value.ToString());
//取时间
DateTime d2 = Convert.ToDateTime(theWorksheetRow.Cells[iConTime + 1].Value);
TimeSpan timeSpan = d2.TimeOfDay;
ci.start = ci.start.Date.Add(timeSpan);
//if (theWorksheetRow.Cells[iConTime].Value.GetType() == typeof(double))
// ci.start = ci.start.Date.Add(DateTime.FromOADate((double)theWorksheetRow.Cells[iConTime].Value).TimeOfDay);
//else
// ci.start = ci.start.Date.Add(DateTime.Parse(theWorksheetRow.Cells[iConTime].Value.ToString()).TimeOfDay);
ci.duration = Convert.ToDouble(theWorksheetRow.Cells[iDuration].Value);
}
catch (Exception tep)
{
MessageBox.Show(tep.Message + theRowCounter.ToString() + theWorksheetRow.Cells[0].Value.ToString() + theWorksheetRow.Cells[1].Value.ToString() + theWorksheetRow.Cells[2].Value.ToString() + theWorksheetRow.Cells[3].Value.ToString());
}
list.Add(ci);
}
theRowCounter++;
}
}
else
{
MessageBox.Show("No such file was found!");
}
return(list);
}
public static List <CDRItem> importExcelUsingNetvantageWHC(string strFilePath, int iSrc, int iDist, int iConTime, int iDuration)
{
List <CDRItem> list = new List <CDRItem>();
bool bOADateFormat = false;
bool bDatetimeFormat = false;
bool bXueliangFormat = false;
//----------to use this function ,remember to add reference :using Infragistics.Documents.Excel;
if (File.Exists(strFilePath))
{
//Load the Excel File into the Workbook Object
//Application.DoEvents();
Workbook theWorkbook = Workbook.Load(strFilePath, false);
//We will only work with the first Worksheet in the Workbook -- 只支持第一个WorkSheet
Worksheet theWorksheet = theWorkbook.Worksheets[0];
int theRowCounter = 0;
//Iterate through all Worksheet rows Iterate through
foreach (WorksheetRow theWorksheetRow in theWorksheet.Rows)
{
if (theRowCounter == 0)
{
//check file format
/*
* string strHeadArray="";
* foreach (WorksheetCell theWorksheetCell in theWorksheetRow.Cells)
* {
* strHeadArray = strHeadArray + theWorksheetCell.Value.ToString().Trim() + ",";
* }
* strHeadArray = strHeadArray.Substring(0, strHeadArray.Length - 1);
* //ID CDRID SRCNum DSTNum TGIN IPIn TGOUT IPOut CDRDate SetupTime ConnectTime DisconnectTime Duration CauseValue Country City Remarks
* if (strHeadArray != "ID,CDRID,SRCNum,DSTNum,TGIN,IPIn,TGOUT,IPOut,CDRDate,SetupTime,ConnectTime,DisconnectTime,Duration,CauseValue,Country,City,Remarks")
* {
* return null;
* }
* */
theRowCounter++;
continue;
}
else
//This is the actual data that will populate the data model
{
CDRItem ci = new CDRItem();
try
{
ci.ani = (theWorksheetRow.Cells[iSrc].Value != null) ? theWorksheetRow.Cells[iSrc].Value.ToString() : "";
ci.dest = (theWorksheetRow.Cells[iDist].Value != null) ? theWorksheetRow.Cells[iDist].Value.ToString() : "";
if ((bOADateFormat == false) && (bDatetimeFormat == false) && (bXueliangFormat == false))
{
try
{
ci.start = DateTime.FromOADate(Convert.ToDouble(theWorksheetRow.Cells[iConTime].Value));
//ci.start = DateTime.Parse(theWorksheetRow.Cells[iConTime].Value.ToString());
bOADateFormat = true;
}
catch (Exception dep)
{
ci.start = DateTime.Parse(theWorksheetRow.Cells[iConTime].Value.ToString());
//ci.start = DateTime.FromOADate(Convert.ToDouble(theWorksheetRow.Cells[iConTime].Value));
bDatetimeFormat = true;
}
}
else
{
if ((bOADateFormat == true) && (theWorksheetRow.Cells[iConTime].Value != null))
{
ci.start = DateTime.FromOADate(Convert.ToDouble(theWorksheetRow.Cells[iConTime].Value));
}
if ((bDatetimeFormat == true) && (theWorksheetRow.Cells[iConTime].Value != null))
{
ci.start = DateTime.Parse(theWorksheetRow.Cells[iConTime].Value.ToString());
}
if ((bXueliangFormat == true) && (theWorksheetRow.Cells[iConTime].Value != null))
{
ci.start = DateTime.ParseExact(theWorksheetRow.Cells[iConTime].Value.ToString(), "yyyyMMddHHmmss", null);
}
}
ci.duration = (theWorksheetRow.Cells[iDuration].Value != null) ? Convert.ToDouble(theWorksheetRow.Cells[iDuration].Value) : 0.0;
//ci.duration = Convert.ToDouble(theWorksheetRow.Cells[iDuration].Value);
}
catch (Exception tep)
{
string sXueliangDatetime = "";
sXueliangDatetime = theWorksheetRow.Cells[iConTime].Value.ToString();
string formatString = "yyyyMMddHHmmss";
try
{
DateTime dt = DateTime.ParseExact(sXueliangDatetime, formatString, null);
ci.start = dt;
bXueliangFormat = true;
}
catch (Exception tmep)
{ }
//MessageBox.Show(tep.Message + theRowCounter.ToString() + theWorksheetRow.Cells[0].Value.ToString() + theWorksheetRow.Cells[1].Value.ToString() + theWorksheetRow.Cells[2].Value.ToString() + theWorksheetRow.Cells[3].Value.ToString());
}
if (ci.dest != "")
{
list.Add(ci);
}
}
theRowCounter++;
}
}
else
{
MessageBox.Show("No such file was found!");
}
return(list);
}
