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); }