public async Task <IActionResult> Generate([FromQuery] string dataSourceType, [FromQuery] string dataSourceConnStr, [FromQuery] string parametersFile, [FromQuery] string templateFile, [FromQuery] int worksheet, [FromQuery] int row, [FromQuery] int column, [FromBody] ParameterInfoModel[] parameters) { ReportsAutoDiscoveryConfigModel pathSearchConfig = GetAutoDiscoveryConfig(); KeyValuePair <DbEngine, string> dataSourceDbEngine = _availableDataSources.First(item => string.Equals(item.Value.Trim().ToLower(), dataSourceType.Trim().ToLower())); IReportGeneratorManager manager = new ExcelReportGeneratorManager(_loggerFactory, dataSourceDbEngine.Key, dataSourceConnStr); string reportFile = GetExcelFilePath("Report", Guid.NewGuid()); ExecutionConfig config = ExecutionConfigManager.Read(Path.Combine(pathSearchConfig.ParametersFilesDirectory, parametersFile)); if (parameters != null && parameters.Length > 0) { if (config.DataSource == ReportDataSource.StoredProcedure) { foreach (ParameterInfoModel parameter in parameters) { StoredProcedureParameter existingStoreProcParam = config.StoredProcedureParameters.FirstOrDefault(p => string.Equals(p.ParameterName.ToLower(), parameter.Key.ToLower())); if (existingStoreProcParam != null) { existingStoreProcParam.ParameterValue = parameter.Value; } } } else { foreach (ParameterInfoModel parameter in parameters) { DbQueryParameter sqlStatementPrameter = null; if (parameter.Type == ParameterType.Where) { sqlStatementPrameter = config.ViewParameters.WhereParameters.FirstOrDefault(p => string.Equals(p.ParameterName.ToLower(), parameter.Key.ToLower())); } if (parameter.Type == ParameterType.Order) { sqlStatementPrameter = config.ViewParameters.OrderByParameters.FirstOrDefault(p => string.Equals(p.ParameterName.ToLower(), parameter.Key.ToLower())); } if (parameter.Type == ParameterType.Group) { sqlStatementPrameter = config.ViewParameters.GroupByParameters.FirstOrDefault(p => string.Equals(p.ParameterName.ToLower(), parameter.Key.ToLower())); } if (sqlStatementPrameter != null) { sqlStatementPrameter.ParameterValue = parameter.Value.ToString(); } } } } bool result = await manager.GenerateAsync(Path.Combine(pathSearchConfig.TemplatesFilesDirectory, templateFile), config, reportFile, ExcelReportGeneratorHelper.CreateParameters(worksheet, row, column)); if (result) { byte[] bytes = System.IO.File.ReadAllBytes(reportFile); return(File(bytes, _expectedMimeTypes[MsExcelExtension], "Report.xlsx")); } return(null); }
public static DbQueryParameter[] GetParameters(DbColumn[] columns) { var parameters = new DbQueryParameter[columns.Length]; for (var i = 0; i < columns.Length; i++) { parameters[i] = new DbQueryParameter(@"@" + NameProvider.ToParameterName(columns[i].Name)); } return(parameters); }
private static void AppendParameter(StringBuilder builder, DbQueryParameter parameter) { if (parameter.Conditions != null && parameter.Conditions.Count > 0) { for (int i = 0; i < parameter.Conditions.Count; i++) { builder.Append(_conditionsStatements[parameter.Conditions[i]]); if (i == parameter.Conditions.Count - 1) { builder.Append(string.Format("{0} {1} {2}", parameter.ParameterName, parameter.ComparisonOperator, parameter.ParameterValue)); } } } else { builder.Append(string.Format("{0} {1} {2}", parameter.ParameterName, parameter.ComparisonOperator, parameter.ParameterValue)); } }