private static DataTable MergeTables(List <DataTable> lstToMerge)
{
DataTable dtResults = lstToMerge.MergeAll("MergeColumn");
for (int i = 0; i < NonDecimalColumns.Count; i++)
{
dtResults.Columns.Add(NonDecimalColumns[i], typeof(string));
dtResults.Columns[NonDecimalColumns[i]].SetOrdinal(i);
}
dtResults.Columns.Add("Total", typeof(decimal));
dtResults.Columns.Add("Average", typeof(decimal));
foreach (DataRow dr in dtResults.Rows)
{
List <decimal> RowValues = new List <decimal>();
string[] splitRecords = dr["MergeColumn"].ToString().Trim().Split('|');
for (int i = 0; i < dtResults.Columns.Count; i++)
{
if (NonDecimalColumns.Contains(dtResults.Columns[i].ColumnName))
{
if (splitRecords.Length == NonDecimalColumns.Count)
{
dr[dtResults.Columns[i].ColumnName] = splitRecords[i];
}
}
else if (dtResults.Columns[i].ColumnName == "Total")
{
dr[dtResults.Columns[i].ColumnName] = RowValues.Sum();
}
else if (dtResults.Columns[i].ColumnName == "Average")
{
dr[dtResults.Columns[i].ColumnName] =
RowValues.Sum() > 0 ? RowValues.Sum() / RowValues.Where(x => x.ToString() != "0" && x.ToString() != "").Count() : 0;
}
else if (dtResults.Columns[i].ColumnName != "MergeColumn")
{
decimal decConversion;
decimal.TryParse(dr[i].ToString(), out decConversion);
RowValues.Add(decConversion);
}
}
}
dtResults.Columns.Remove("MergeColumn");
DataView dv = dtResults.DefaultView;
dv.Sort = "Owner";
dtResults = dv.ToTable();
DataRow drAverage = totalizeColumns(dtResults, "Average");
dtResults.Rows.Add(totalizeColumns(dtResults, "Total"));
dtResults.Rows.Add(drAverage);
return(dtResults);
}
private DataTable SortSalesByTotal(DataTable dt)
{
List <DataTable> lstResults = new List <DataTable>();
DataTable dtToSort = dt;
dtToSort.Columns.Add("MergeColumn");
for (int i = 0; i < dt.Rows.Count; i++)
{
dtToSort.Rows[i]["MergeColumn"] = i;
}
DataView dv1 = dtToSort.AsEnumerable().Where(f => f.Field <string>("Owner") != null).ToList().CopyToDataTable().DefaultView;
DataView dv2 = dtToSort.AsEnumerable().Where(f => f.Field <string>("Owner") == null).ToList().CopyToDataTable().DefaultView;
dv1.Sort = "Total desc";
lstResults.Add(dv1.ToTable());
lstResults.Add(dv2.ToTable());
dtToSort = lstResults.MergeAll("MergeColumn");
dtToSort.Columns.Remove("MergeColumn");
return(dtToSort);
}
private List <DataTable> ReportDatatable(DataTable dt)
{
DataTable dtResults = new DataTable();
DataTable dtPivot = new DataTable();
List <DataTable> lstdtResults = new List <DataTable>();
DateTime StartDate = Convert.ToDateTime(ParamStartDate);
DateTime EndDate = Convert.ToDateTime(ParamEndDate);
List <DataTable> lstDatatable;
List <DataTable> TableList = new List <DataTable>();
dt.Columns.Remove("TrainerName");
////Generate the column with name of day
DataColumn Col = dt.Columns.Add("Day", Type.GetType("System.String"));
//Calculate Check Commission Due
DataColumn Col1 = dt.Columns.Add("CheckCommissionDue", Type.GetType("System.Decimal"));
Col.SetOrdinal(1);
for (int i = 0; i < dt.Rows.Count; i++)
{
//dt.Rows[i][1] = Convert.ToDateTime(dt.Rows[i][0]).DayOfWeek; //Column Day
dt.Rows[i][dt.Columns.Count - 1] = decimal.Parse(dt.Rows[i][6].ToString()) * decimal.Parse(dt.Rows[i][8].ToString()) / 100; //Column Check Commission Due
}
for (int i = 0; i < NumberOfWeeks; i++)
{
lstDatatable = new List <DataTable>();
dtResults = dt.Clone();
var RecordsByWeek = dt.AsEnumerable().Where(row => Convert.ToDateTime(row.Field <string>("Date").ToLower()) >= StartDate.AddDays(i * 7) &&
Convert.ToDateTime(row.Field <string>("Date").ToLower()) <= StartDate.AddDays(((i + 1) * 7) - 1))
.OrderBy(row => Convert.ToDateTime(row.Field <string>("Date").ToLower()));
if (RecordsByWeek.Count() > 0)
{
dtResults = RecordsByWeek.CopyToDataTable();
}
DataTable dt1Col = new DataTable();
DataRow dr;
DataColumn dc = new DataColumn();
dc.ColumnName = "Id";
dt1Col.Columns.Add(dc);
dc = new DataColumn();
dc.ColumnName = "Date";
dt1Col.Columns.Add(dc);
for (int l = 1; l < dtResults.Columns.Count; l++)
{
dr = dt1Col.NewRow();
dr[0] = l;
dr[1] = dtResults.Columns[l].ColumnName;
dt1Col.Rows.Add(dr);
}
lstDatatable.Add(dt1Col);
int dtResultsCounter = 0;
for (int j = 0; j < 7; j++)
{
dt1Col = new DataTable();
dc = new DataColumn();
dc.ColumnName = "Id";
dt1Col.Columns.Add(dc);
dc = new DataColumn();
if (dtResults.Rows.Count > 0 && (StartDate.AddDays((i * 7) + j) - Convert.ToDateTime(dtResults.Rows[dtResultsCounter]["Date"].ToString())).TotalDays == 0)
{
dc.ColumnName = dtResults.Rows[dtResultsCounter]["Date"].ToString();
dt1Col.Columns.Add(dc);
for (int k = 1; k < dtResults.Columns.Count; k++)
{
dr = dt1Col.NewRow();
dr[0] = k;
if (k == 1)
{
dr[1] = StartDate.AddDays((i * 7) + j).DayOfWeek;
}
else
{
dr[1] = dtResults.Rows[dtResultsCounter][k].ToString();
}
dt1Col.Rows.Add(dr);
}
if (dtResultsCounter + 1 < dtResults.Rows.Count)
{
dtResultsCounter++;
}
}
else
{
dc.ColumnName = Common.ApplyDateFormat(StartDate.AddDays((i * 7) + j));
dt1Col.Columns.Add(dc);
for (int k = 1; k < dtResults.Columns.Count; k++)
{
dr = dt1Col.NewRow();
dr[0] = k;
if (k == 1)
{
dr[1] = StartDate.AddDays((i * 7) + j).DayOfWeek;
}
else
{
dr[1] = "";
}
dt1Col.Rows.Add(dr);
}
}
lstDatatable.Add(dt1Col);
}
dtPivot = lstDatatable.MergeAll("Id");
dtPivot.Columns.Remove("Id");
lstdtResults.Add(dtPivot);
}
return(lstdtResults);
}