예제 #1
0
 public bool Save_ReportingDefinition(ref ReportDefinition RD, string user)
 {
     try
     {
         if (RD.id == 0) // New Report
         {
             SQL.Query = string.Format(SQL.SQLQuery.Reporting_SaveReportingDefinition, SQL.ParseInput(RD.title), SQL.ParseInput(user), JsonConvert.SerializeObject(RD.Description), SQL.ParseInput(RD.query), SQL.ParseInput(RD.language), SQL.ParseInput(RD.columns));
             SQL.cmd = SQL.Command(SQL.Data);
             RD.id = Convert.ToInt32(SQL.cmd.ExecuteScalar());
         }
         else
         { // Update Report
             SQL.Query = string.Format(SQL.SQLQuery.Reporting_UpdateReportingDefinition, RD.id, SQL.ParseInput(RD.title), JsonConvert.SerializeObject(RD.Description), SQL.ParseInput(RD.query), SQL.ParseInput(RD.language), SQL.ParseInput(RD.columns), SQL.ParseInput(user));
             SQL.cmd = SQL.Command(SQL.Data);
             SQL.cmd.ExecuteNonQuery();
         }
     }
     catch (Exception ex)
     {
         SQL.WriteLog_Error(ex, "Save/Update failed on id:"+RD.id+" :(", "UIFS.Reporting.Save_ReportingDefinition()");
         return false;
     }
     return true;
 }
예제 #2
0
        // -------------------------------------------------------------------
        // --[ Begin Report Data Outputs
        /* /-------[   function: Output_DataTables       ]-------\
           | Creates HTML and Javascript for the "DataTables" jquery plugin
           | * we can specify column settings: width,
           |
           \-------------------------------------------------------------------/
        */
        public string Output_DataTables(ReportDefinition RD)
        {
            string html ="",js="",data="";

            //. build table
            html = "<table cellpadding='0' cellspacing='0' border='0' class='display' id='Reporting_DataTables'>" +
                "<thead><tr>";
            string[] columns = RD.columns.Split(new char[] { ',' });
            for (int t = 0; t < columns.Length; t++)
            {
                html = html + "<th>" + columns[t] + "</th>";
            }
            html = html + "</tr></thead>"+
                "<tbody></tbody></table>";

            //. get data!
            SQL.Query=RD.query;
            SQL.cmd=SQL.Command(SQL.Data);
            SQL.sdr = SQL.cmd.ExecuteReader();
            if (SQL.sdr.HasRows)
            {
                while (SQL.sdr.Read())
                {
                    data = data + "[";
                    for (int t = 0; t < columns.Length; t++)
                    {
                        if (!SQL.sdr.IsDBNull(t))
                        {
                            data = data + JsonConvert.SerializeObject(SQL.sdr[t].ToString()) + ",";
                        }
                        else { data += "\"\","; }
                    }
                    data = data.Remove(data.Length - 1, 1) + "],";
                }
                data = data.Remove(data.Length - 1, 1);
            }
            SQL.sdr.Close();

            js = "<script type='text/javascript'>" +
                "$('#Reporting_DataTables').dataTable( {" +
                "'aoColumnDefs': [ { 'bSearchable': false, 'bVisible': false, 'aTargets': [0,1] }]," +		// 0 would be the id field...
                "'bJQueryUI':true," + // use jquery ui theme
                "'sPaginationType': 'full_numbers'," + // for the paging navigation (either full or 2 arrows)
                "'aLengthMenu': [[25, 50, 100, 200, -1], [25, 50, 100, 200, 'All']]," +
                "'sScrollY':'250px'," + // MUST set our height to keep this thing under control!
                "'sScrollX':'100%'," + // Set width to container size...add scrollbar in table
                "'sHeightMatch': 'none'," + // do not let calculate row height...for faster display
                //"'sAjaxSource': 'ajax.aspx?cmd=300.1'," +
                        // The next line adds a row click function (allows selection multiple)
                "'fnInitComplete': function () {$('tr').click(function () {if ($(this).hasClass('row_selected')) $(this).removeClass('row_selected'); else $(this).addClass('row_selected'); }); }," +
                "'aaData':["+data+"]"+
                "});" +
                "SubjectTable = $('#DataTables_FormSelect').dataTable();" +
                "</script>";
            return html+js;
        }
예제 #3
0
        /* /-------[   function: Output_Aggregation       ]-------\
           | Can output HTML with javascript at the end.
           | We use the plugin: jqplot
           |
           \-------------------------------------------------------------------/
        */
        public string Output_Aggregation(ReportDefinition RD)
        {
            string html = "",js="";
            AggregationDisplay ADisplay = new AggregationDisplay();
            if (RD.Description.Aggregates != null)
            {
                html = "<table cellpadding='0' cellspacing='0' border='0' class='display'>" +
                    "<thead></thead>";
                for (int t = 0; t < RD.Description.Aggregates.Length; t++)
                {
                    ReportDefinition.Aggregate RDA = RD.Description.Aggregates[t];
                    DateTime DT;
                    SQL.Query = RDA.query;
                    SQL.cmd = SQL.Command(SQL.Data);
                    //. query may return different data depending on the datatype
                    try
                    {
                        switch (RDA.manipulation)
                        {
                            case "CNT":
                                switch (RDA.datatype)
                                {
                                    case "bit":
                                        // we expect false (0) first
                                        SQL.sdr = SQL.cmd.ExecuteReader(); SQL.sdr.Read();
                                        if (SQL.sdr.HasRows)
                                        {
                                            ADisplay.CNT_Bool_False = SQL.sdr.GetInt32(0);
                                            ADisplay.CNT_Bool_True = SQL.sdr.GetInt32(1);
                                        }
                                        // TODO: depending on Yes/No, On/Off...
                                        //html = html + "<tr><td>HOW MANY: <span class='title'>" + RDA.title + "</span></td><td>YES=" + ADisplay.CNT_Bool_True + "; NO=" + ADisplay.CNT_Bool_False + "</td></tr>";
                                        html = html + "<tr><td colspan='2'>"+
                                            "<div id='piechart_" + t.ToString() + "' style='width:300px; height:180px; margin-left:auto;margin-right:auto; '></div>" +
                                            "</td></tr>";
                                        //. build jqplot data and script
                                        js = js + "jqdata = [['YES ("+ADisplay.CNT_Bool_True+")',"+ADisplay.CNT_Bool_True+"],['NO ("+ADisplay.CNT_Bool_False+")',"+ADisplay.CNT_Bool_False+"]];";
                                        js = js + "$.jqplot('piechart_"+t.ToString()+"', [jqdata], {seriesDefaults: {renderer:$.jqplot.PieRenderer,"+
                                                "rendererOptions:{padding:10,sliceMargin:4, startAngle:-90, showDataLabels:true}},"+
                                                "legend: {show:true},"+
                                                "title: {text:'HOW MANY: " + RDA.title + "',textColor:'#FFDEAD'}" +
                                                " });";
                                        SQL.sdr.Close();
                                        break;
                                    default:
                                        ADisplay.AggrValue = Convert.ToInt32(SQL.cmd.ExecuteScalar());
                                        html = html + "<tr><td>A COUNT of: " + RDA.title + "</td><td>" + ADisplay.AggrValue + "</td></tr>";
                                        break;
                                }
                                break;
                            case "SUM":
                                ADisplay.AggrValue = Convert.ToInt32(SQL.cmd.ExecuteScalar());
                                html = html + "<tr><td>A SUM of: " + RDA.title + "</td><td>" + ADisplay.AggrValue + "</td></tr>";
                                break;
                            case "AVG":
                                ADisplay.AggrValue = Convert.ToInt32(SQL.cmd.ExecuteScalar());
                                html = html + "<tr><td>AVERAGE of: " + RDA.title + "</td><td>" + ADisplay.AggrValue + "</td></tr>";
                                break;

                            // -- DATETIME RANGES
                            // : Time Ranges should only display Hours, Minutes (Days are invalid here)
                            // : Date & Time Ranges can display Days, Hours, Minutes...
                            case "SUM_DTRANGE":
                                DT = Convert.ToDateTime(SQL.cmd.ExecuteScalar());
                                html = html + "<tr><td>SUM of: " + RDA.title + "</td><td>Days: " + DT.Day +", Hours: "+DT.Hour+", Minutes: "+DT.Minute+ "</td></tr>";
                                break;
                            case "SUM_DRANGE":
                                DT = Convert.ToDateTime(SQL.cmd.ExecuteScalar());
                                html = html + "<tr><td>SUM of: " + RDA.title + "</td><td>Days: " + DT.Day + "</td></tr>";
                                break;
                            case "SUM_TRANGE":
                                DT = Convert.ToDateTime(SQL.cmd.ExecuteScalar());
                                html = html + "<tr><td>SUM of: " + RDA.title + "</td><td>Hours: " + DT.Hour + ", Minutes: " + DT.Minute + "</td></tr>";
                                break;
                            case "AVG_DTRANGE":
                                DT = Convert.ToDateTime(SQL.cmd.ExecuteScalar());
                                html = html + "<tr><td>AVERAGE of: " + RDA.title + "</td><td>Days: " + DT.Day + ", Hours: " + DT.Hour + ", Minutes: " + DT.Minute + "</td></tr>";
                                break;
                            case "AVG_DRANGE":
                                DT = Convert.ToDateTime(SQL.cmd.ExecuteScalar());
                                html = html + "<tr><td>AVERAGE of: " + RDA.title + "</td><td>Days: " + DT.Day + "</td></tr>";
                                break;
                            case "AVG_TRANGE":
                                DT = Convert.ToDateTime(SQL.cmd.ExecuteScalar());
                                html = html + "<tr><td>AVERAGE of: " + RDA.title + "</td><td>Hours: " + DT.Hour + ", Minutes: " + DT.Minute + "</td></tr>";
                                break;

                        }
                    }
                    catch (Exception ex)
                    {
                        SQL.WriteLog_Error(ex, "Could not read data with: " + SQL.Query, "UIFS.Reporting.Output_Aggregation()");
                        if (!SQL.sdr.IsClosed) { SQL.sdr.Close(); }
                        html = html + "<tr><td>ERROR:" + ex.Message + "<br/>" + ex.StackTrace + "</td></tr>";
                    }

                }
                html = html + "</table>";
            }

            return html +"<script type='text/javascript'>" + js + "</script>";
        }
예제 #4
0
 public ReportDefinition Load_ReportingDefinition(long id)
 {
     ReportDefinition RD = new ReportDefinition();
     try
     {
         SQL.Query = string.Format(SQL.SQLQuery.Reporting_LoadReportingDefinition,id);
         SQL.cmd = SQL.Command(SQL.Data);
         SQL.sdr = SQL.cmd.ExecuteReader();SQL.sdr.Read();
         if (SQL.sdr.HasRows) {
             RD.id = id;
             RD.title = SQL.sdr.GetString(0);
             RD.Description = (ReportDefinition.Subject)JsonConvert.DeserializeObject(SQL.sdr.GetString(1),typeof(ReportDefinition.Subject));
         }
         else { RD=null; // no data
         }
         SQL.sdr.Close();
     }
     catch (Exception ex)
     {
         SQL.WriteLog_Error(ex, "load failed", "UIFS.Reporting.Load_ReportingDefinition()");
         return null;
     }
     return RD;
 }
예제 #5
0
        /* /-------[   class: FormControl_formatqueryandlanguage       ]-------\
           | based on UIFS.ControlType, we will have different ways to build
           | a query.  This being the standardized routine
           \-------------------------------------------------------------------/
         */
        public void FormControl_formatqueryandlanguage(UIFS.ControlType ctrltype, UIFS.FormControl Control, ReportDefinition.Detail detail, ref string Query, ref string Language)
        {
            string[] selections;
            ReportingSubject.Detail RSDetail = new ReportingSubject.Detail(); // used to pass data for building queries

            switch (ctrltype)
            {
                // All of the following return a single string values
                case ControlType.Textbox:
                case ControlType.DateTime:
                case ControlType.List:
                    RSDetail.db="[" + Control.id.ToString() + "]";
                    RSDetail.lang=detail.lang;
                    RSDetail.name = Control.name;
                    RSDetail.type="text";
                    Query = Query_FormatDetail(RSDetail, detail.selection, ref Language);
                    break;
                // The following return a single numeric values
                case ControlType.Percentage:
                case ControlType.Number:
                    RSDetail.db="[" + Control.id.ToString() + "]";
                    RSDetail.lang=detail.lang;
                    RSDetail.name = Control.name;
                    RSDetail.type="number";
                    Query = Query_FormatDetail(RSDetail, detail.selection, ref Language);
                    break;
                // Checkbox controls are always true/false with an optional input field
                case ControlType.Checkbox:
                    RSDetail.db="[" + Control.id.ToString() + "]";
                    RSDetail.lang=detail.lang;
                    RSDetail.name = Control.name;
                    RSDetail.type="bit";
                    Query = Query_FormatDetail(RSDetail, detail.selection, ref Language);
                    break;
                // Ranges have start/end values

                //TODO: this type
                case ControlType.Range:
                    switch (Language)
                    {
                        case "IS BETWEEN":
                            selections = detail.selection.Split(new char[] { ',' });
                            //Query = "[" + Control.id + "_Start]>=" + selections[0] + " AND [" + Control.id + "_End]<='" + selections[1] + "'";
                            break;
                    }
                    break;

            }
        }
예제 #6
0
            public ReportShow[] ReportShowing; // a list of all possible data field manipulations for report output

            #endregion Fields

            #region Constructors

            //. Need to "build the GUI" by creating all the possible ReportConditions
            //  then, we can save to Mem and access the dynamic controls, individually, without reloading unnecessary data
            //  this happens in Subject_Set();
            public GraphicalUserInterface(ReportDefinition RD)
            {
                this.RD = RD; // save our ReportDefinition
            }