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);
                        }
                    }
                }
            }
        }
    }
Beispiel #2
0
    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);
    }