public void AddFilterItem(ReportXMLPrintParameterModel filterModel)
{
HtmlGenericControl filter = new HtmlGenericControl("div");
filter.InnerText = filterModel.FilterCaption.IsNullEmptyOrWhiteSpace() ? filterModel.ColumnName : filterModel.FilterCaption;
this.uxFilters.Controls.Add(filter);
}
public void UpdateReportXMLPrintParameter(ReportXMLPrintParameterModel model)
{
ReportXMLPrintParameter existing = this.dataContext
.ReportXMLPrintParameters
.Where(rx => rx.TableName == model.TableName && rx.ColumnName == model.ColumnName && rx.ReportXMLVersion == model.ReportXMLVersion && rx.MasterReport_Id == model.MasterReport_Id)
.FirstOrDefault();
if (existing == null)
{
existing = model.CopyToObject(new ReportXMLPrintParameter()) as ReportXMLPrintParameter;
this.dataContext.ReportXMLPrintParameters.Add(existing);
}
else
{
existing = model.CopyToObject(existing) as ReportXMLPrintParameter;
}
this.dataContext.SaveChanges();
model = existing.CopyToObject(model) as ReportXMLPrintParameterModel;
}
private void LoadFIlters()
{
for (int x = 0; x < this.ReportParameters.Count; ++x)
{
ReportXMLPrintParameterModel item = this.ReportParameters[x];
if (!item.DefaultValue.IsNullEmptyOrWhiteSpace())
{
continue;
}
this.HaveFilters = true;
this.uxParameters.Items.Add(item);
this.uxParameters[x].HideHeader(true);
this.uxParameters[x, 0].Caption = item.FilterCaption;
this.uxParameters[x, 0].IsRequired = item.IsRequired.IsTrue();
}
}
public string BuildSelectQuery(
ReportColumnModel[] columns,
List <WhereParameterModel> whereParameterModel,
List <ReportXMLPrintParameterModel> reportFilters,
Dictionary <string, ReportSQLReplaceHeaderModel> replacementColumns,
string orderByString)
{
if (!columns.HasElements())
{
return(string.Empty);
}
StringBuilder result = new StringBuilder();
result.Append("SELECT ");
for (int x = 0; x < columns.Length; ++x)
{
ReportColumnModel column = columns[x];
if (x == columns.Length - 1)
{
result.AppendLine($" [{columns[0].TableName}].[{column.ColumnName}] ");
}
else if (x == 0)
{
result.AppendLine($"[{columns[0].TableName}].[{column.ColumnName}], ");
}
else
{
result.AppendLine($" [{columns[0].TableName}].[{column.ColumnName}], ");
}
}
// Add replacement SQL here
foreach (KeyValuePair <string, ReportSQLReplaceHeaderModel> replacekey in replacementColumns)
{
result.Replace(replacekey.Key, this.BuildReplacementSQL(replacekey));
}
result.AppendLine($" FROM [{columns[0].TableName}] WITH(NOLOCK) ");
bool haveWhereClause = false;
if (whereParameterModel.Count > 0)
{
result.Append("WHERE ");
haveWhereClause = true;
}
foreach (WhereParameterModel parameter in whereParameterModel.OrderBy(so => so.OperatorIndex))
{
result.AppendLine($"{parameter.ColumnName} = @{parameter.ParameterName} {(parameter.AndOrOperator == SqlWhereOperatorsEnum.None ? string.Empty : parameter.AndOrOperator.ParseToString())} ");
}
List <ReportXMLPrintParameterModel> validFilters = reportFilters
.Where(rf => !rf.FilterValue.IsNullEmptyOrWhiteSpace())
.ToList();
if (reportFilters.Count > 0 &&
!haveWhereClause &&
validFilters.Count > 0)
{
result.Append(" WHERE ");
}
else if (haveWhereClause && validFilters.Count > 0)
{
result.Append(" AND ");
}
for (int x = 0; x < validFilters.Count; ++x)
{
ReportXMLPrintParameterModel filter = validFilters[x];
if (filter.FilterValue.IsNullEmptyOrWhiteSpace())
{
continue;
}
result.AppendLine($"{filter.TableName}.{filter.ColumnName} = '{filter.FilterValue}'");
if (x < validFilters.Count - 1)
{
result.Append(" AND ");
}
}
if (!orderByString.IsNullEmptyOrWhiteSpace())
{
result.Append($" ORDER BY {orderByString} ASC");
}
return(result.ToString());
}
