I am reading CSV file using LINQ and applying a filter then I am getting data but the problem is my count data is not always matching when comparing data after applying the same filter. I'm opening that CSV file with Excel but I could not determine why my count and excel data is not matching. so here I am pasting my full code with CSV file link. if possible please some one run my code after downloading the CSV file.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
DataTable oData = null;
public Form1()
{
InitializeComponent();
oData = new DataTable();
oData.Columns.Add("Date", typeof(string));
oData.Columns.Add("Time", typeof(string));
oData.Columns.Add("Incomming", typeof(string));
oData.Columns.Add("Outgoing", typeof(string));
oData.Columns.Add("Miss Call", typeof(string));
}
private void button1_Click(object sender, EventArgs e)
{
TimeSpan StartTime;
TimeSpan EndTime;
bool flag=true;
TimeSpan tsStart = new TimeSpan(09, 00, 00);
TimeSpan tsEnd = new TimeSpan(17, 30, 0);
List<PhoneData> oPhoneData = GetPhoneData(@"d:\report.csv");
bool isFirstTime = false;
oData.Rows.Clear();
while (tsStart < tsEnd)
{
if (!flag)
{
tsStart = new TimeSpan(tsStart.Hours, tsStart.Minutes, int.Parse("01"));
}
flag = false;
StartTime = tsStart;
tsStart = tsStart.Add(new TimeSpan(00, 30, 00));
EndTime = TimeSpan.Parse((tsStart.Hours >= 10 ? tsStart.Hours.ToString() : ("0" + tsStart.Hours.ToString()))
+ ":" + (tsStart.Minutes >= 10 ? tsStart.Minutes.ToString() : ("0" + tsStart.Minutes.ToString())) + ":00");
int incomingCount = (from row in oPhoneData
where row.direction == "I"
&& row.Call_Start.TimeOfDay >= StartTime
&& row.Call_Start.TimeOfDay <= EndTime
&& row.Is_Internal == 0
&& row.continuation == 0
&& row.call_duration.TotalSeconds > 0
&& !row.party1name.Contains("Voice Mail")
&& !row.party1name.Contains("VM")
select 1).Count();
int outgoingCount = (from row in oPhoneData
where row.direction == "O"
&& row.Call_Start.TimeOfDay >= StartTime
&& row.Call_Start.TimeOfDay <= EndTime
&& row.Is_Internal == 0
&& row.continuation == 0
&& row.party1name != "Voice Mail"
&& !row.party1name.StartsWith("VM")
select 1).Count();
int misscallCount = (from row in oPhoneData
where row.direction == "I"
&& row.continuation == 0
&& row.Caller.Trim() != string.Empty
&& row.Call_Start.TimeOfDay >= StartTime
&& row.Call_Start.TimeOfDay <= EndTime
&& row.party1name != "Voice Mail"
&& !row.party1name.StartsWith("VM")
&& !row.party1name.StartsWith("Line")
&& row.Park_Time == 0
&& row.Called_number == "687220"
select 1).Count();
DataRow dr = oData.NewRow();
dr["Date"] = "12-04-2016";
dr["Time"] = StartTime + "-" + EndTime;
dr["Incomming"] = incomingCount;
dr["Outgoing"] = outgoingCount;
dr["Miss Call"] = misscallCount;
oData.Rows.Add(dr);
}
dgList.DataSource = oData;
MessageBox.Show("Job Done");
}
public List<PhoneData> GetPhoneData(string strFileName)
{
return File.ReadLines(strFileName)
.Skip(1)
.Where(s => s != "")
.Select(s => s.Split(new[] { ',' }))
.Select(a => new PhoneData
{
Call_Start = DateTime.Parse( a[0]),
call_duration = TimeSpan.Parse(a[1]),
Ring_duration = int.Parse(a[2]),
direction = a[4],
Is_Internal =Convert.ToInt32( a[8]),
continuation = int.Parse( a[10]),
party1name = a[13],
Caller = a[3],
Park_Time = Convert.ToInt32(a[16]),
Called_number = a[5]
})
.ToList();
}
}
public class PhoneData
{
public DateTime Call_Start { get; set; }
public TimeSpan call_duration { get; set; }
public Int32 Ring_duration { get; set; }
public string direction { get; set; }
public Int32 Is_Internal { get; set; }
public Int32 continuation { get; set; }
public string party1name { get; set; }
public string Caller { get; set; }
public Int32 Park_Time { get; set; }
public string Called_number { get; set; }
}
}