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