public void PivotTable_ES()
{
var pvtData = new PivotData(new[] { "District", "SubCounty", "wrd_name", "Sex", "Quarter", "HES" }, new CountAggregatorFactory());
pvtData.ProcessData(new DataTableReader(ReturnEconomic_strengthening()));
var pvtTbl = new PivotTable(
new[] { "Quarter", "District", "SubCounty" }, //rows
new[] { "HES" }, //columns
pvtData);
var outputWr = new StringWriter();
var pvtHtmlWr = new PivotTableHtmlWriter(outputWr);
pvtHtmlWr.SubtotalRows = true;
pvtHtmlWr.AllowHtml = true;
pvtHtmlWr.TotalsRowHeaderText = "Grand Total";
pvtHtmlWr.TotalsColumnHeaderText = "Sub County Totals";
pvtHtmlWr.TableClass = "table border = '1' table - bordered table-hover";
pvtHtmlWr.Write(pvtTbl);
var pvtTblHtml = outputWr.ToString();
LitPivot.Text = pvtTblHtml;
}
public ActionResult PivotTable(int year)
{
var context = new PivotContext();
var pvtData = GetDataCube();
// in this example filter is applied to in-memory data cube
// for large datasets it may be applied on database level (with SQL WHERE expression)
var filteredPvtData = new SliceQuery(pvtData).Where("Order Year", year).Execute();
// render pivot table HTML
var pvtTbl = new PivotTable(
new[] { "Country" }, // rows
new[] { "Order Year", "Order Month" }, // cols
filteredPvtData
);
// sort by row total
pvtTbl.SortRowKeys(null,
1, // lets order by measure #1 (sum of unit price)
System.ComponentModel.ListSortDirection.Descending);
var strHtmlWr = new StringWriter();
var pvtHtmlWr = new PivotTableHtmlWriter(strHtmlWr);
pvtHtmlWr.TableClass = "table table-bordered table-condensed pvtTable";
pvtHtmlWr.Write(pvtTbl);
context.PivotTableHtml = strHtmlWr.ToString();
// prepare data for pie chart (total sum by country)
var pvtDataForChart = new SliceQuery(filteredPvtData).Dimension("Country").Measure(1).Execute();
var chartPvtTbl = new PivotTable(new[] { "Country" }, null, pvtDataForChart);
// sort by row total
chartPvtTbl.SortRowKeys(null, System.ComponentModel.ListSortDirection.Descending);
var strJsonWr = new StringWriter();
var jsonWr = new PivotTableJsonWriter(strJsonWr);
jsonWr.Write(chartPvtTbl);
context.PivotTableJson = strJsonWr.ToString();
return(PartialView(context));
}
public void Export_OVCSERV()
{
var pvtData = new PivotData(new[] { "District", "SubCounty", "wrd_name", "Sex", "Age", "Quarter" }, new CountAggregatorFactory());
pvtData.ProcessData(new DataTableReader(ReturnMembersServed()));
var pvtTbl = new PivotTable(
new[] { "Quarter", "District", "SubCounty" }, //rows
new[] { "Age", "Sex" }, //columns
pvtData);
var outputWr = new StringWriter();
var pvtHtmlWr = new PivotTableHtmlWriter(outputWr);
pvtHtmlWr.SubtotalRows = true;
pvtHtmlWr.AllowHtml = true;
pvtHtmlWr.TotalsRowHeaderText = "Grand Total";
pvtHtmlWr.TableClass = "table border = '1' table - bordered table-hover";
var pvtCsvWr = new PivotTableCsvWriter(outputWr);
pvtCsvWr.Write(pvtTbl);
}
protected void ShowCode6Wide()
{
int labID = Convert.ToInt32(ddlLab.SelectedValue.ToString());
if (labID > 0)
{
//var x = lstStudy.SelectedValue;
var x2 = gridStudy.GetSelectedFieldValues("studyID");
if (x2.Count == 0)
//if (lstStudy.SelectedValue == "")
{
lblSelectOne.Text = "Select some studies.";
lblSelectOne.Visible = true;
}
else if (chkDisplayOnPage.Checked == false && chkXLSX.Checked == false)
{
lblSelectOne.Text = "Select the output method.";
lblSelectOne.Visible = true;
}
else
{
SQL_utils sql = new SQL_utils("backend");
string study_csv = String.Join(",", x2);
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("labID", labID.ToString(), "int"));
//ps.Add(sql.CreateParam("study_csv", lstStudy.SelectedValue, "text"));
ps.Add(sql.CreateParam("study_csv", study_csv, "text"));
DataTable dt = sql.DataTable_from_ProcName("spCode6_person_by_Lab", ps);
PivotTable pivotTable = utilPivotTable.DataTable_to_PivotTable(dt, new List <string> {
"code6"
}, new List <string> {
"source_of_ID"
}, "ID",
new List <utilPivotTable.AggrFx> {
utilPivotTable.AggrFx.Concat2
});
lblSelectOne.Visible = false;
if (chkDisplayOnPage.Checked == true)
{
//Write back to page
var htmlResult = new StringWriter();
var pvtHtmlWr = new PivotTableHtmlWriter(htmlResult);
pvtHtmlWr.TotalsColumn = false;
pvtHtmlWr.TotalsRow = false;
pvtHtmlWr.GrandTotal = false;
pvtHtmlWr.Write(pivotTable);
Literal xtab = new Literal();
xtab.Text = htmlResult.ToString();
panel_wide.Controls.Add(xtab);
}
if (chkXLSX.Checked == true)
{
var dataTblWr = new PivotTableDataTableWriter("Test"); //.Write(pivotTable);
var tbl = dataTblWr.Write(pivotTable);
string filename = "code6_IDs_wide_" + SpreadsheetGearUtils.DateTime_for_filename();
SpreadsheetGearUtils.SaveDataTableToExcel(tbl, filename, true, "xlsx");
}
sql.Close();
}
}
}
protected void Load_Kappa(int measureID)
{
SQL_utils sql = new SQL_utils("data");
DataTable dt_score_vars = sql.DataTable_from_SQLstring(" select fldname, minval, maxval from def.vwFld where measureID=" + measureID.ToString() + " and relitem in (1,3) ");
List <string> vars = dt_score_vars.AsEnumerable().Select(f => f.Field <string>("fldname")).ToList();
//foreach (string v in vars)
foreach (DataRow var_row in dt_score_vars.Rows)
{
string v = var_row["fldname"].ToString();
string x = "exec spRELv2_Get_Scores_for_var '" + v + "', " + Master.Master_studyID.ToString() + ", " + measureID.ToString() + ", 'exclude missing', 3";
DataTable dt_kappa = sql.DataTable_from_SQLstring(x);
var pivotData = new PivotData(
new string[] { "orig_val", "rel_val", "fldname" },
new CountAggregatorFactory(),
new DataTableReader(dt_kappa));
var pivotTable = new PivotTable(
new[] { "orig_val" }, // row dimension(s)
new[] { "rel_val" }, // column dimension(s)
pivotData);
//var q = new SliceQuery(pivotData)
// .Dimension("orig_val")
// .Dimension("rel_val")
// .Measure(0);
//var q2 = q.Execute(true);
utilStats.Kappa_stats k = utilStats.CalculateKappa(pivotTable);
var htmlResult = new StringWriter();
var pvtHtmlWr = new PivotTableHtmlWriter(htmlResult);
pvtHtmlWr.Write(pivotTable);
Literal xtab = new Literal();
xtab.Text = htmlResult.ToString();
Literal kappa = new Literal();
kappa.Text += utilStats.PrintKappa_stats(k);
Table tbl = new Table();
TableRow row = new TableRow();
TableCell cell1 = new TableCell();
TableCell cell2 = new TableCell();
cell1.Controls.Add(xtab);
cell2.Controls.Add(kappa);
row.Cells.Add(cell1);
row.Cells.Add(cell2);
tbl.Rows.Add(row);
Label v_title = new Label();
v_title.Text = v;
v_title.Font.Size = 10;
v_title.Font.Bold = true;
UpdatePanel_Kappa.ContentTemplateContainer.Controls.Add(v_title);
UpdatePanel_Kappa.ContentTemplateContainer.Controls.Add(tbl);
UpdatePanel_Kappa.Update();
UpdatePanel_Kappa.Visible = true;
}
sql.Close();
}
protected void CreatePivot(DataTable dt, List <string> pivot_rows, List <string> pivot_cols, List <string> stats, string cellfield, int decimal_places)
{
List <utilPivotTable.AggrFx> fx = new List <utilPivotTable.AggrFx>();
if (stats.Contains("N"))
{
fx.Add(utilPivotTable.AggrFx.N);
}
if (stats.Contains("Mean"))
{
fx.Add(utilPivotTable.AggrFx.M);
}
if (stats.Contains("SD"))
{
fx.Add(utilPivotTable.AggrFx.SD);
}
_pivot = utilPivotTable.DataTable_to_PivotTable(dt, pivot_rows, pivot_cols, cellfield, fx);
var strHtmlWr = new StringWriter();
var pvtHtmlWr = new PivotTableHtmlWriter(strHtmlWr);
pvtHtmlWr.TableClass = "table table-bordered table-condensed pvtTable";
pvtHtmlWr.FormatValue = (aggr, measureIdx) =>
{
if (aggr.Count == 0)
{
return("0");
}
// apply default number format
if (decimal_places == 0)
{
return(String.Format("{0:#}", aggr.Value));
}
else if (decimal_places == 1)
{
return(String.Format("{0:0.#}", aggr.Value));
}
else if (decimal_places == 2)
{
return(String.Format("{0:0.##}", aggr.Value));
}
else if (decimal_places == 3)
{
return(String.Format("{0:0.###}", aggr.Value));
}
else if (decimal_places == 4)
{
return(String.Format("{0:0.####}", aggr.Value));
}
else if (decimal_places == 5)
{
return(String.Format("{0:0.#####}", aggr.Value));
}
else
{
return(String.Format("{0:0.##}", aggr.Value));
}
};
pvtHtmlWr.Write(_pivot);
_htmlContent = strHtmlWr.ToString();
_htmlContent = _htmlContent.Replace("Average of", "Mean");
_htmlContent = _htmlContent.Replace("Variance of", "SD");
_htmlContent = _htmlContent.Replace("Count", "N");
string headerRow = String.Join(" > ", pivot_rows);
string headerCol = String.Join(" > ", pivot_cols);
_autotitle = String.Format("<b>{0}</b> by <b>{1}</b>", headerRow, headerCol);
output = String.Format("{0}{1}", _autotitle, _htmlContent);
}
// string GetData(IEnumerable<Dictionary<string,object>> inputData) {
// var pvtData = new PivotData(
// new [] {"Tournament","Category", "Gender","Player"}, // list of all dimensions used in pivot table
// new SumAggregatorFactory("Count"),
// true);
// pvtData.ProcessData( inputData ); // use appropriate overload for different data sources
// var pvtTbl = new PivotTable(
// new[] {"Tournament","Category"}, // rows
// new[] {"Gender", "Player"},
// pvtData
// );
//
// var strWr = new StringWriter();
// var htmlPvtTblWr = new PivotTableHtmlWriter(strWr);
// htmlPvtTblWr.Write(pvtTbl);
//
// return strWr.ToString();
// }
public override IDisplayResult Display(CrosstabVisualProfile profile, IUpdateModel updater)
{
var context = new PivotContext();
// var json = new System.Web.Script.Serialization.JavaScriptSerializer();
// var inputData = json.Deserialize<IEnumerable<Dictionary<string,object>>>(inputDataJson);
// var inputData = JsonConvert.DeserializeObject<IEnumerable<Dictionary<string,object>>>(profile.QueryResult.ToString());//json.Deserialize<IEnumerable<Dictionary<string,object>>>(inputDataJson);
//var inputData = JsonConvert.DeserializeObject<IEnumerable<Dictionary<string,object>>>(profile.QueryResult.ToString());//json.Deserialize<IEnumerable<Dictionary<string,object>>>(inputDataJson);
// var pvtData = GetData(inputData);
var converted = JsonConvert.DeserializeObject(profile.QueryResult.ToString());
var inputData = JsonConvert.DeserializeObject <IEnumerable <Dictionary <string, object> > >(converted);
// in this example filter is applied to in-memory data cube
// for large datasets it may be applied on database level (with SQL WHERE expression)
// var filteredPvtData = new SliceQuery(pvtData).Where("Order Year", year).Execute();
var pvtData = new PivotData(
new [] { "Tournament", "Category", "Gender", "Player" }, // list of all dimensions used in pivot table
new SumAggregatorFactory("Count"),
true);
pvtData.ProcessData(inputData); // use appropriate overload for different data sources
var pvtTbl = new PivotTable(
new[] { "Tournament", "Category" }, // rows
new[] { "Gender", "Player" },
pvtData
);
// render pivot table HTML
// var pvtTbl = new PivotTable(
// new[] {"Country"}, // rows
// new[] {"Order Year", "Order Month"} , // cols
// pvtData// filteredPvtData
// );
// sort by row total
// pvtTbl.SortRowKeys(null,
// 1, // lets order by measure #1 (sum of unit price)
// System.ComponentModel.ListSortDirection.Descending);
var strHtmlWr = new StringWriter();
var pvtHtmlWr = new PivotTableHtmlWriter(strHtmlWr);
pvtHtmlWr.TableClass = "table table-bordered table-condensed pvtTable";
pvtHtmlWr.Write(pvtTbl);
context.PivotTableHtml = strHtmlWr.ToString();
// prepare data for pie chart (total sum by country)
// var pvtDataForChart = new SliceQuery(filteredPvtData).Dimension("Country").Measure(1).Execute();
// var chartPvtTbl = new PivotTable(new[]{"Country"},null,pvtDataForChart);
// // sort by row total
// chartPvtTbl.SortRowKeys(null, System.ComponentModel.ListSortDirection.Descending);
// var strJsonWr = new StringWriter();
// var jsonWr = new PivotTableJsonWriter(strJsonWr);
// jsonWr.Write(chartPvtTbl);
// context.PivotTableJson = strJsonWr.ToString();
//var pager = await GetPagerAsync(context.Updater, listPart);
////giannis
//var settings = GetSettings(listPart);
//model.EnablePositioning = settings.EnablePositioning;
//model.AdminListViewName = settings.AdminListViewName;
//model.ListViewProviders = _listViewService.Providers.ToList();
//model.ListViewProvider = GetListViewProvider(model);// _listViewService.GetDefaultProvider();
//model.ItemsPerPage = listPart.ItemsPerPage;
//model.Skip = listPart.Skip;
//model.PagerSuffix = listPart.PagerSuffix;
//model.MaxItems = listPart.MaxItems;
//model.DisplayPager = listPart.DisplayPager;
//========
// model.ProjectionPart = listPart;
// model.ContentItems = (await QueryListItemsAsync(listPart, pager, true)).ToArray();
//model.PageOfContentItems = (await QueryListItemsAsync(listPart, pager, true)).ToArray();
//model.ContainedContentTypeDefinitions = GetContainedContentTypes(listPart);
//model.Context = context;
//model.Pager = await context.New.PagerSlim(pager);
//model.ListViewShape = model.ListViewProvider.BuildDisplay(context, model);// context.AdminListView.BuildDisplay(context);
return(Combine(
Initialize <CrosstabVisualProfileViewModel>("CrosstabVisualProfile", model =>
{
model.PivotContext = context;
model.QueryResult = profile.QueryResult;
model.AutoStart = profile.AutoStart;
model.Controls = profile.Controls;
model.Indicators = profile.Indicators;
model.Interval = profile.Interval;
model.Keyboard = profile.Keyboard;
model.Pause = profile.Pause;
model.Wrap = profile.Wrap;
})
.Location("Detail", "Content:1")//,
//Shape<TableLayoutProfileViewModel>("TableLayoutProfile_Summary", model =>
// {
// model.AutoStart = profile.AutoStart;
// model.Controls = profile.Controls;
// model.Indicators = profile.Indicators;
// model.Interval = profile.Interval;
// model.Keyboard = profile.Keyboard;
// model.Pause = profile.Pause;
// model.Wrap = profile.Wrap;
// })
// .Location("Summary", "Meta:5")
));
//return Combine(
// Shape("TableLayoutProfile_SummaryAdmin", model =>
// {
// model.Profile = profile;
// }).Location("Content:5")
// //Shape("BootStrapProfile_Buttons_SummaryAdmin", model =>
// //{
// // model.Profile = profile;
// //}).Location("Actions:2")
//);
}
