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