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; }
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; }
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())); } }
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; }
public static void loadReport(ListBox listBox, QueryReport newReport) { String formattedText = FormUtils.getFormattedTextFromReport(newReport.Id, newReport, listBox.Width, listBox.Font); listBox.Items.Add(formattedText); }
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); }
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); }
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); }
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]; } }