Beispiel #1
0
        public DataSet ExecuteQueryReport(QueryReport queryReport)
        {
            DataSet ds = null;

            try
            {
                ArrayList parameters = new ArrayList();

                if (queryReport.HasUserFilter)
                {
                    OracleParameter filterParam = new OracleParameter("filterUser", OracleDbType.Int32);
                    filterParam.Direction = ParameterDirection.Input;
                    filterParam.Value = DBNull.Value;
                    if (queryReport.QueryFilterByUser != null)
                    {
                        filterParam.Value = queryReport.QueryFilterByUser.Id_user;
                    }
                    parameters.Add(filterParam);
                }

                if (queryReport.HasCommandFilter)
                {
                    OracleParameter filterParam = new OracleParameter("filterCommand", OracleDbType.Int32);
                    filterParam.Direction = ParameterDirection.Input;
                    filterParam.Value = DBNull.Value;
                    if (queryReport.QueryFilterByCommand != null)
                    {
                        filterParam.Value = queryReport.QueryFilterByCommand.Id_command;
                    }
                    parameters.Add(filterParam);
                }

                if (queryReport.HasCategoryFilter)
                {
                    OracleParameter filterParam = new OracleParameter("filterCategory", OracleDbType.Int32);
                    filterParam.Direction = ParameterDirection.Input;
                    filterParam.Value = DBNull.Value;
                    if (queryReport.QueryFilterByCategory != null)
                    {
                        filterParam.Value = queryReport.QueryFilterByCategory.Id_category;
                    }
                    parameters.Add(filterParam);
                }

                OracleParameter cursorParam = new OracleParameter("pRefCursor", OracleDbType.RefCursor);
                cursorParam.Direction = ParameterDirection.Output;
                parameters.Add(cursorParam);

                ds = base.ModuloDatosInformes.ExecuteDataSet(queryReport.Store_procedure, parameters);
            }
            catch (Exception ex)
            {
                ds = null;
                base.ModuloLog.Error(ex);
            }

            return ds;
        }
Beispiel #2
0
 public QueryReport(QueryReport qr)
 {
     this.Id = qr.Id;
     this.Name = qr.Name;
     this.Store_procedure = qr.Store_procedure;
     this.Description = qr.Description;
     this.Char_Type = qr.Char_Type;
     this.Range_X = qr.Range_X;
     this.Range_Y = qr.Range_Y;
     this.Sheet_Name = qr.Sheet_Name;
     this.HasAxis = qr.HasAxis;
     this.HasUserFilter = qr.HasUserFilter;
     this.HasCommandFilter = qr.HasCommandFilter;
     this.HasCategoryFilter = qr.HasCategoryFilter;
     this.QueryFilterByUser = qr.QueryFilterByUser;
     this.QueryFilterByCommand = qr.QueryFilterByCommand;
     this.QueryFilterByCategory = qr.QueryFilterByCategory;
     this.Selected = true;
 }
Beispiel #3
0
        private void FillQueryReportData(DataRow dRow, QueryReport qR)
        {
            object obj;

            obj = dRow["ID"];
            if (obj != DBNull.Value)
            {
                qR.Id = Int32.Parse(obj.ToString());
            }

            obj = dRow["NAME"];
            if (obj != DBNull.Value)
            {
                qR.Name = obj.ToString();
            }

            obj = dRow["STORE_PROCEDURE"];
            if (obj != DBNull.Value)
            {
                qR.Store_procedure = obj.ToString();
            }

            obj = dRow["DESCRIPTION"];
            if (obj != DBNull.Value)
            {
                qR.Description = obj.ToString();
            }

            obj = dRow["CHART_TYPE"];
            if (obj != DBNull.Value)
            {
                qR.Char_Type = obj.ToString();
            }

            obj = dRow["RANGE_X"];
            if (obj != DBNull.Value)
            {
                qR.Range_X = obj.ToString();
            }

            obj = dRow["RANGE_Y"];
            if (obj != DBNull.Value)
            {
                qR.Range_Y = obj.ToString();
            }

            obj = dRow["P_NAME"];
            if (obj != DBNull.Value)
            {
                qR.Sheet_Name = obj.ToString();
            }

            obj = dRow["HAS_AXIS"];
            if (obj != DBNull.Value)
            {
                qR.HasAxis = obj.ToString().Equals("1");
            }

            obj = dRow["FILTERS"];
            if (obj != DBNull.Value)
            {
                qR.fillFilterFlags(Int32.Parse(obj.ToString()));
            }
        }
Beispiel #4
0
        public List<QueryReport> GetAllQueryReports()
        {
            List<QueryReport> listaQueryReports = new List<QueryReport>();
            QueryReport qR = new QueryReport();
            DataSet ds = null;

            try
            {
                base.ModuloLog.Debug("Se consultan todas las QueryReport que hay en la base de datos.");

                base.Query = SP_SELECT_ALL_QUERY_REPORTS;
                ds = base.ModuloDatos.ExecuteDataSet(base.Query);

                // ExecuteDataSet -> Selects
                // ExecuteScalar  -> Devuelve un campo (ej: select count(*) from commands)
                // ExecuteNonReader -> Insert/Update/Delete: el valor que devuelve es el numero de filas que se han insertado/actualizado o borrado....

                foreach (DataTable dt in ds.Tables)
                {
                    foreach (DataRow dRow in dt.Rows)
                    {
                        qR = new QueryReport();
                        this.FillQueryReportData(dRow, qR);
                        listaQueryReports.Add(qR);
                    }
                }
            }
            catch (Exception ex)
            {
                listaQueryReports.Clear();
                base.ModuloLog.Error(ex);
            }
            return listaQueryReports;
        }
Beispiel #5
0
 public static void loadReport(ListBox listBox, QueryReport newReport)
 {
     String formattedText = FormUtils.getFormattedTextFromReport(newReport.Id, newReport, listBox.Width, listBox.Font);
     listBox.Items.Add(formattedText);
 }
Beispiel #6
0
        private static String getFormattedTextFromReport(int elementId, QueryReport newReport, int elementWidth, Font elementFont)
        {
            List<String> text = new List<String>();
            StringBuilder build = new StringBuilder();
            text.Add(elementId + " -> " + Constantes.getMessage(newReport.Name));

            if (newReport.QueryFilterByUser != null)
            {
                build.Append(" @" + newReport.QueryFilterByUser.Name);
            }
            if (newReport.QueryFilterByCommand != null)
            {
                build.Append(" @" + newReport.QueryFilterByCommand.Name);
            }
            if (newReport.QueryFilterByCategory != null)
            {
                build.Append(" @" + newReport.QueryFilterByCategory.Name);
            }
            text.Add(build.ToString());

            return FormUtils.textToPaint(text.ToArray(), elementWidth, elementFont);
        }
Beispiel #7
0
        private static Range getRangeFrom(QueryReport queryReport, Worksheet sheetReport, DataSet procResultSet)
        {
            // A3
            string rX = queryReport.Range_X;
            // B
            string rY = queryReport.Range_Y;
            // A2 (para coger la fila de titulos y que rellene la leyenda correctamente).
            string rXmodif = rX.Substring(0,1) + (Int32.Parse(rX.Substring(1,1)) - 1);
            // BN (en función del número de datos insertados)
            string rYmodif = rY + (procResultSet.Tables[0].Rows.Count + 2);

            // Devuelve el rango de datos para pasárselo al gráfico.
            return sheetReport.get_Range(rXmodif, rYmodif);
        }
Beispiel #8
0
        private static void fillSheet(QueryReport queryReport, Worksheet sheetReport)
        {
            object misval = System.Reflection.Missing.Value;

            string reportName = Constantes.getMessage(queryReport.Name);

            // DataSet que contiene el resultado de la query del report

            QueryReportDatos qd = new QueryReportDatos();

            DataSet ds = qd.ExecuteQueryReport(queryReport);

            // Rellenamos la hoja del Excel con los datos del Report

            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                // Rellenamos el nombre de las columnas

                int columna = 0;
                int[] tamMax = new int[ds.Tables[0].Rows[0].ItemArray.Length];
                int[] tamActual = new int[ds.Tables[0].Rows[0].ItemArray.Length];

                foreach (DataColumn column in ds.Tables[0].Columns)
                {
                    sheetReport.Cells[2, (1 + columna)] = Constantes.getMessage(column.ColumnName);
                    tamActual[columna] = Constantes.getMessage(column.ColumnName).Length;
                    if (tamMax[columna] < tamActual[columna])
                    {
                        tamMax[columna] = tamActual[columna];
                    }
                    columna++;
                }

                // Rellenamos la tabla de datos

                for (int f = 0; f < ds.Tables[0].Rows.Count; f++)
                {
                    for (int c = 0; c < ds.Tables[0].Rows[0].ItemArray.Length; c++)
                    {
                        sheetReport.Cells[(3 + f), (1 + c)] = ds.Tables[0].Rows[f].ItemArray[c];
                        tamActual[c] = ds.Tables[0].Rows[f].ItemArray[c].ToString().Length;
                        if (tamMax[c] < tamActual[c])
                        {
                            tamMax[c] = tamActual[c];
                        }
                    }
                }
                for (int i = 0; i < tamMax.Length; i++)
                {
                    ((Range)sheetReport.Cells[1, 1 + i]).EntireColumn.ColumnWidth = tamMax[i] + 5;
                }

                // Rellenamos el gráfico del informe
                ChartObjects chartObjects = (ChartObjects)(sheetReport.ChartObjects(Type.Missing));
                ChartObject myChart = (ChartObject)chartObjects.Item(1);
                Chart chartPage = myChart.Chart;
                Range chartRange = Report.getRangeFrom(queryReport, sheetReport, ds);
                chartPage.SetSourceData(chartRange, misval);

                object categoryTitle = misval, valueTitle = misval;

                // Comprobamos si hay que rellenar los textos de los ejes y/o series de datos, para el tipo de grafico a generar en el informe
                if (queryReport.HasAxis)
                {
                    int categoryCol = Report.convertLetterToIndex(queryReport.Range_X.Substring(0, 1));
                    categoryTitle = (String)(sheetReport.Cells[2, categoryCol] as Range).Value2;

                    int valueCol = Report.convertLetterToIndex(queryReport.Range_Y);
                    if (valueCol == categoryCol + 1)
                    {
                        // 1 serie de datos, pintamos el otro eje directamente.
                        valueTitle = (String)(sheetReport.Cells[2, valueCol] as Range).Value2;
                    }
                }

                chartPage.ChartWizard(chartRange, (XlChartType)Enum.Parse(typeof(XlChartType), queryReport.Char_Type, true),
                                          misval, misval, misval, misval, misval, reportName, categoryTitle, valueTitle, misval);
            }
            else
            {
                queryReport.Description = "EMPTY";
            }

            // Rellenamos la descripción del informe
            fillDescription(queryReport, sheetReport);
        }
Beispiel #9
0
        private static void fillDescription(QueryReport queryReport, Worksheet sheetReport)
        {
            // Rellenamos la descripción del informe (filas 3 - 4 y 5)
            int colum1 = Report.convertLetterToIndex(queryReport.Range_Y) + 4;
            string reportDescription = Constantes.getMessage(queryReport.Description);
            string[] tokens = reportDescription.Split(new char[] { ';' });

            if(queryReport.Description.Equals("EMPTY"))
            {
                sheetReport.Cells[3, colum1] = tokens[0] + " --> " + Constantes.getMessage(queryReport.Name);
            }
            else
            {
                sheetReport.Cells[3, colum1] = tokens[0];
            }

            if (queryReport.HasUserFilter && queryReport.QueryFilterByUser != null)
            {
                sheetReport.Cells[4, colum1] = Constantes.getMessage("ReportGenerateFilteredByMsg");
                sheetReport.Cells[4, colum1 + 1] = Constantes.getMessage("ReportGenerateUserFilterMsg");
                sheetReport.Cells[4, (colum1 + 2)] = queryReport.QueryFilterByUser.Name;
            }

            if (queryReport.HasCommandFilter && queryReport.QueryFilterByCommand != null)
            {
                sheetReport.Cells[4, colum1] = Constantes.getMessage("ReportGenerateFilteredByMsg");
                sheetReport.Cells[4, colum1 + 4] = Constantes.getMessage("ReportGenerateCommandFilterMsg");
                sheetReport.Cells[4, (colum1 + 5)] = queryReport.QueryFilterByCommand.Name;
            }

            if (queryReport.HasCategoryFilter && queryReport.QueryFilterByCategory != null)
            {
                sheetReport.Cells[4, colum1] = Constantes.getMessage("ReportGenerateFilteredByMsg");
                sheetReport.Cells[4, colum1 + 7] = Constantes.getMessage("ReportGenerateCategoryFilterMsg");
                sheetReport.Cells[4, (colum1 + 8)] = queryReport.QueryFilterByCategory.Name;
            }

            if (tokens.Length > 1){
                sheetReport.Cells[5, colum1] = tokens[1];
            }
        }