protected void MakeDxChart()
{
SQL_utils sql = new SQL_utils("FinMart");
string importDate = sql.StringScalar_from_SQLstring("select convert(varchar,min(imported_from_EDW),0) from fin.tblEDWData ");
lblImportDate.Text = String.Format("Imported on {0}", importDate);
DataTable dt_bnums = sql.DataTable_from_SQLstring(
"select 'a' orderer, budgetNumber, " +
"[name] budgetName from fin.tblEDWData " +
"where bienniumYear >= 2013 and budgetNumber in " +
"(095001, 095002, 099877, 653920, 655411) " +
"group by budgetNumber, [name] " +
"union select 'b' orderer, budgetNumber, " +
"[name] budgetName from fin.tblEDWData " +
"where bienniumYear >= 2013 and budgetNumber in " +
"(640259, 653977, 656876, 658853, 680475, 680774, 681328)" +
"group by budgetNumber, [name] " +
"union select 'c' orderer, 'Core Clinic' as budgetNumber, " +
"'Sum' as budgetName " +
"union select 'd' orderer, 'Gifts' as budgetNumber, " +
"'Sum' as budgetName order by orderer");
if (dt_bnums.Columns[0].ColumnName == "err")
{
DataRow row = dt_bnums.Rows[0];
lblN.Text = row["err"].ToString();
lblN.Text += sql.Errormsg;
}
else
{
List <DxChartOrder> orders = new List <DxChartOrder>();
int counter = 0;
foreach (DataRow row in dt_bnums.Rows)
{
counter++;
string bname = row["budgetName"].ToString();
string bnum = row["budgetNumber"].ToString();
string mo = DateTime.Now.Month.ToString();
if (mo.Length < 2)
{
mo = "0" + mo;
}
string yrmo_now = DateTime.Now.Year.ToString() + "_" + mo;
string code = String.Format(
"select * from(select '1' id, budgetNumber, [name] budgetName, bienniumYear, " +
"yrmo, RevenueAmount, REV_Endowment, REV_Other, -ExpenseAmount as ExpenseAmount, " +
"-EXP_SALARY as EXP_SALARY, -EXP_Non_SALARY as EXP_Non_SALARY, balance from fin.tblEDWData " +
"where bienniumYear >= 2013 and budgetNumber in " +
"(095001, 095002, 099877, 653920, 655411, 640259, 653977, 656876, 658853, 680774, 681328, 680475) " +
"and yrmo <= (select substring(replace(CONVERT(VARCHAR, GETDATE(), 21), '-', '_'), 1, 7)) " +
"union select '1' id, 'Core Clinic' as budgetNumber, 'Sum' as budgetName, bienniumYear, " +
"yrmo, sum(RevenueAmount) as RevenueAmount, sum(REV_Endowment) as REV_Endowment, " +
"sum(REV_Other) as REV_Other, sum(-ExpenseAmount) as ExpenseAmount, sum(-EXP_SALARY) as EXP_SALARY, " +
"sum(-EXP_Non_SALARY) as EXP_Non_SALARY, sum(balance) from fin.tblEDWData where bienniumYear >= 2013 " +
"and budgetNumber in (095001, 095002, 099877, 653920, 655411) " +
"and yrmo <= (select substring(replace(CONVERT(VARCHAR, GETDATE(), 21), '-', '_'), 1, 7)) " +
"group by bienniumYear , yrmo " +
"union select '1' id ,'Gifts' as budgetNumber ,'Sum' as budgetName , bienniumYear , yrmo ," +
"sum(RevenueAmount) as RevenueAmount ,sum(REV_Endowment) as REV_Endowment ,sum(REV_Other) " +
"as REV_Other ,sum(-ExpenseAmount) as ExpenseAmount ,sum(-EXP_SALARY) as EXP_SALARY ," +
"sum(-EXP_Non_SALARY) as EXP_Non_SALARY ,sum(balance) as balance from fin.tblEDWData " +
"where bienniumYear >= 2013 and budgetNumber in (640259, 653977, 656876, 658853, 680774, 681328, 680475) " +
"and yrmo <= (select substring(replace(CONVERT(VARCHAR, GETDATE(), 21), '-', '_'), 1, 7)) " +
"group by bienniumYear , yrmo ) a where budgetNumber = '{0}'", bnum);
DataTable dt = sql.DataTable_from_SQLstring(code);
decimal maxbal = dt.AsEnumerable().Select(f => f.Field <decimal>("balance")).Max();
List <Color> mycolors = new List <Color> {
Color.DarkBlue //balance
, Color.Red //exp non-sal
, Color.Red //exp sal
, Color.Red //exp tot
, Color.Green //rev endow
, Color.Green //rev other
, Color.Green //rev tot
#region old colors
// Color.Red //balance
//,Color.Orange //exp non-sal
//,Color.Yellow //exp sal
//,Color.Green //exp tot
//,Color.Blue //rev endow
//,Color.Purple //rev other
//,Color.Cyan //rev tot
//,Color.Pink
//,Color.Peru
//,Color.Wheat
//,Color.SkyBlue
//,Color.SpringGreen
//,Color.AliceBlue
//,Color.Orange
//,Color.DarkOrange
//,Color.Olive
#endregion
};
List <DashStyle> mydashstyles = new List <DashStyle> {
DashStyle.Solid
, DashStyle.Dash
, DashStyle.Dot
, DashStyle.Solid
, DashStyle.Dot
, DashStyle.Dash
, DashStyle.Solid
};
if (maxbal != 0)
{
DxLineplotSettings settings = new DxLineplotSettings();
List <string> yvars = new List <string> {
"RevenueAmount"
, "REV_Endowment"
, "REV_Other"
, "ExpenseAmount"
, "EXP_SALARY"
, "EXP_Non_SALARY"
, "balance"
};
settings.maxCol = 2;
settings.W = 1100;
settings.H = 400;
settings.colors = mycolors;
settings.AddVarsFromList(yvars);
//settings.numvars = yvars;
settings.seriesby = "id";
settings.xaxisvar = "yrmo";
settings.colorvar = "variable";
settings.title = String.Format("{0} {1}", bnum, bname);
settings.legend_pos_h = "RightOutside";
settings.legend_pos_v = "Top";
//settings.ChartType = DxChartType.Lineplot;
DxChartOrder order = new DxChartOrder();
order.list_settings.Add(settings);
//orders.Add(order);
DxChartFactory factory = new DxChartFactory(dt, new List <DxChartOrder> {
order
});
foreach (DxChartOrder myorder in factory.orders)
{
foreach (DxChartBatch batch in myorder.batches)
{
WebChartControl c = batch.charts[0].chart;
c.Legend.MarkerMode = LegendMarkerMode.CheckBoxAndMarker;
DevExpress.XtraCharts.Series s0 = c.Series[0];
DevExpress.XtraCharts.Series s1 = c.Series[1];
DevExpress.XtraCharts.Series s2 = c.Series[2];
DevExpress.XtraCharts.Series s3 = c.Series[3];
DevExpress.XtraCharts.Series s4 = c.Series[4];
DevExpress.XtraCharts.Series s5 = c.Series[5];
DevExpress.XtraCharts.Series s6 = c.Series[6];
s0.View.Color = mycolors[0];
s1.View.Color = mycolors[1];
s2.View.Color = mycolors[2];
s3.View.Color = mycolors[3];
s4.View.Color = mycolors[4];
s5.View.Color = mycolors[5];
s6.View.Color = mycolors[6];
((LineSeriesView)s0.View).LineStyle.DashStyle = mydashstyles[0];
((LineSeriesView)s1.View).LineStyle.DashStyle = mydashstyles[1];
((LineSeriesView)s2.View).LineStyle.DashStyle = mydashstyles[2];
((LineSeriesView)s3.View).LineStyle.DashStyle = mydashstyles[3];
((LineSeriesView)s4.View).LineStyle.DashStyle = mydashstyles[4];
((LineSeriesView)s5.View).LineStyle.DashStyle = mydashstyles[5];
((LineSeriesView)s6.View).LineStyle.DashStyle = mydashstyles[6];
System.Web.UI.WebControls.Table t = LayoutOutput.LayoutBatch(batch);
Literal lit = new Literal();
lit.Text = String.Format("<b>{2}. {0} {1}</b>", bnum, bname, counter);
panel.Controls.Add(lit);
panel.Controls.Add(t);
}
}
}
}
}
}
private void AppendCharts(DocumentPosition pos, DxChartBatch batch, Document doc, float scaleX, float scaleY)
{
bool addHeaderRow = true;
if (batch.layout == DxLayout.Upper)
{
int numcols = LayoutOutput.NCols(batch.charts.Count);
int numrows = (addHeaderRow) ? numcols + 1 : numcols;
//int numrows = (addHeaderRow) ? batch.maxRow + 1 : batch.maxRow;
doc.BeginUpdate();
DevExpress.XtraRichEdit.API.Native.Table t = doc.Tables.Create(doc.Range.End, numrows, numcols, AutoFitBehaviorType.AutoFitToContents);
FormatTableBorders(t);
if (addHeaderRow)
{
for (int h = 0; h < numcols; h++)
{
doc.InsertText(t[0, h].Range.Start, batch.vars[h]);
doc.InsertText(t[h, 0].Range.Start, batch.vars[h - 1]);
//if (h > 0 & h < batch.vars.Count) doc.InsertText(t[0, h].Range.Start, batch.vars[h]);
//if (h > 0 & h < batch.vars.Count) doc.InsertText(t[h, 0].Range.Start, batch.vars[h - 1]);
t.Cell(h, 0).VerticalAlignment = TableCellVerticalAlignment.Center;
}
}
//t.Rows.Add(CreateHeaderRow(batch.vars, ncols));
int counter = 0;
for (int r = 0; r < numrows; r++)
{
//for (int c = r; c < batch.maxCol; c++)
for (int c = r; c < numcols; c++)
{
if (c > r)
{
int bumprow = (addHeaderRow) ? 1 : 0;
int idx = LayoutOutput.GetDiagIndex(r, c, batch.maxRow);
string chartfile = String.Format(@"{0}{1}.png", _temppath, batch.charts[counter].guid);
counter++;
DevExpress.XtraRichEdit.API.Native.TableCell cell = t.Cell(r + bumprow, c);
doc.Images.Insert(cell.Range.Start, DocumentImageSource.FromFile(chartfile));
doc.Images[doc.Images.Count - 1].ScaleX = scaleX;
doc.Images[doc.Images.Count - 1].ScaleY = scaleY;
}
}
}
doc.EndUpdate();
}
else
{
foreach (DxChart chart in batch.charts)
{
string chartfile = String.Format(@"{0}{1}.png", _temppath, chart.guid);
log(chartfile);
//MemoryStream s = new MemoryStream();
//chart.chart.ExportToImage(s, System.Drawing.Imaging.ImageFormat.Png);
doc.Images.Insert(pos, DocumentImageSource.FromFile(chartfile));
doc.Images[doc.Images.Count - 1].ScaleX = 0.5f;
doc.Images[doc.Images.Count - 1].ScaleY = 0.5f;
}
}
}
protected void ActigraphPlots()
{
panel.Controls.Clear();
SQL_utils sql = new SQL_utils("data");
int iswake_negvalue = -15;
int cap_activity = 2000;
int cap_whitelight = 300;
int scaleto_activity = 100;
int scaleto_whitelight = 100;
lblSetupInfo.Text = String.Format("Activity capped at {0}, White light capped at {1}<br/>Activity scaled 0:{2}, White light scaled 0:{3}"
, cap_activity, cap_whitelight, scaleto_activity, scaleto_whitelight);
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("id", cboSubject.Value.ToString(), "text"));
ps.Add(sql.CreateParam("smID_epoch", cboStudymeas.Value.ToString(), "int"));
ps.Add(sql.CreateParam("cap_activity", cap_activity.ToString(), "int"));
ps.Add(sql.CreateParam("cap_whitelight", cap_whitelight.ToString(), "int"));
ps.Add(sql.CreateParam("iswake_neg_value", iswake_negvalue.ToString(), "int"));
DataSet ds = sql.DataSet_from_ProcName("spActigraphData", ps);
ds.Tables[0].TableName = "epoch";
ds.Tables[1].TableName = "stats";
DxActogramSettings s = new DxActogramSettings();
s.W = 1200;
s.H = 300;
s.seriesby = "id";
s.xaxisvar = "time_of_day";
s.xaxis_is_date = true;
s.AddVarsFromList(new List <string>()
{
"activity", "is_wake", "white_light"
});
//Changed Feb 2019
//s.numvars = new List<string>() { "activity", "is_wake", "white_light"};
s.colorvar = "variable";
s.panelvar = "report_date";
s.colors = Actigraph.colors;
s.legend_pos_h = "RightOutside";
s.legend_pos_v = "Top";
s.maxy = scaleto_whitelight;
s.miny = iswake_negvalue;
s.maxCol = 1;
s.geom = LineplotGeom.Bar;
s.altgeom = LineplotGeom.Star;
s.vars_for_altgeom = new List <string> {
"white_light"
};
s.markersize = 8;
DxHistogramSettings hist = new DxHistogramSettings((DxChartSettings)s);
hist.AddVarsFromList(new List <string>()
{
"activity", "white_light"
});
//Changed Feb 2019
//hist.numvars = new List<string>() { "activity", "white_light" };
hist.W = 400; hist.H = 200;
hist.colors = Actigraph.colors;
DxChartOrder order = new DxChartOrder();
order.list_settings.Add(s);
order.list_settings.Add(hist);
DataTable stats1 = utilStats.DataTable_DescStats(ds.Tables["epoch"], true);
ds.Tables["epoch"].RescaleColumn("activity", 0, scaleto_activity);
ds.Tables["epoch"].RescaleColumn("white_light", 0, scaleto_whitelight);
DataTable stats2 = utilStats.DataTable_DescStats(ds.Tables["epoch"], true);
DxChartFactory factory = new DxChartFactory(ds.Tables["epoch"], new List <DxChartOrder> {
order
});
int counter = 0;
foreach (DxChartOrder myorder in factory.orders)
{
foreach (DxChartBatch batch in myorder.batches)
{
if (batch.batchsettings.outputtype == DxOutputtype.Actogram)
{
foreach (DxActogram ch in batch.charts)
{
XYDiagram xy = ch.xydiagram;
xy.AxisX.DateTimeScaleOptions.MeasureUnit = DateTimeMeasureUnit.Hour;
xy.AxisX.DateTimeScaleOptions.GridAlignment = DateTimeGridAlignment.Hour;
xy.AxisX.Label.TextPattern = "{A:t}";
DataTable dt = ds.Tables[1];
string date_txt = String.Format("{0}, {1}", ch.date_txt, Convert.ToDateTime(ch.date_txt).DayOfWeek.ToString());
ch.SetMainTitle(date_txt); //Place date in the title
foreach (DataRow row in dt.Rows)
{
DateTime rpt_date = Convert.ToDateTime(row["report_date"].ToString());
if (rpt_date == Convert.ToDateTime(date_txt))
{
Actigraph.ActogramStats stats = new Actigraph.ActogramStats(row);
ch.AnnotateActogram(stats);
counter++;
}
}
}
batch.SetYAxisRange();
batch.SetXAxisRange_1day();
}
else
{
foreach (DxChart ch in batch.charts)
{
XYDiagram xy = ch.xydiagram;
xy.AxisX.Label.TextPattern = "{A:n}";
}
}
System.Web.UI.WebControls.Table t = LayoutOutput.LayoutBatch(batch);
panel.Controls.Add(t);
}
}
gridDaily.DataSource = ds.Tables[1];
gridDaily.DataBind();
gridDaily.Visible = true;
//DxChartOrder order2 = LinePlot_BedTimes();
//DxChartFactory fatory2 = new DxChartFactory(ds.Tables["stats"], order2);
}