public void SetFilterValue()
{
try
{
string filterString = string.Empty;
foreach (string TableName in BodyTablesName)
{
filterString = string.Empty;
var searchList = FilterSetList.FindAll(x => !x.IsParameter && x.TableName == TableName && string.IsNullOrEmpty(x.ColumnValue) && !x.ExtraFilterColumn);
foreach (FilterSets filter in searchList)
{
if (filter.Operators == "In" ||
filter.Operators == "Not In")
{
filterString += filter.ColumnName + " " + filter.Operators + " (" + GetInOperatorString(filter.ColumnValue) + ") And ";
}
else
{
if (filter.ColumnName == "Expression")
{
filterString += filter.ColumnValue + " And ";
}
else
{
if (filter.DataType == "DateTime" || filter.DataType == "System.DateTime")
{
CultureInfo provider = CultureInfo.InvariantCulture;
DateTime datevalue = DateTime.ParseExact(filter.ColumnValue, ExtensionMethods.GridDateFormatArray, provider, DateTimeStyles.None);
filterString += filter.ColumnName + " " + filter.Operators + " '" + datevalue.ToString(ExtensionMethods.DateFormat) + "' And ";
}
else if (filter.DataType == "Boolean" || filter.DataType == "System.Boolean")
{
string filVal = filter.ColumnValue == "false" ? "0" : "1";
filterString += filter.ColumnName + " " + filter.Operators + " " + filVal + " And ";
}
else
{
filterString += filter.ColumnName + " " + filter.Operators + " '" + filter.ColumnValue + "' And ";
}
}
}
}
if (filterString.Length > 0)
{
filterString = filterString.Substring(0, filterString.Length - 4);
DsSource.Tables[TableName].DefaultView.RowFilter = filterString;
}
}
}
catch (Exception ex)
{
throw (ex);
}
}
private void SetSQLParametersValue()
{
try
{
var searchList = FilterSetList.FindAll(x => x.IsParameter && string.IsNullOrEmpty(x.ColumnValue) && string.IsNullOrEmpty(x.DefaultValue));
if (searchList.Any())
{
throw new Exception("Parameter can not be blank. ");
}
foreach (RDLTable rt in DsReport.Tables)
{
foreach (RDLParameter param in rt.Parameters)
{
searchList = FilterSetList.FindAll(x => x.IsParameter && x.ColumnName == param.Prompt);
if (searchList.Count == 1)
{
SetValue(param, searchList[0]);
}
else
{
param.Value = string.Empty;
}
}
}
//Report Parameters
foreach (RDLParameter param in Parameters)
{
searchList = FilterSetList.FindAll(x => x.IsParameter && x.ColumnName == param.Name);
if (searchList.Count == 1)
{
SetValue(param, searchList[0]);
}
else
{
param.Value = string.Empty;
}
}
}
catch (Exception ex)
{
throw (ex);
}
}
public void SetParameterValue(List <CustomeParameter> ParamList)
{
try
{
foreach (CustomeParameter cParam in ParamList)
{
var searchList = FilterSetList.FindAll(x => x.ColumnName.ToUpper().Equals(cParam.ParameterName.ToUpper()));
if (searchList.Count != 0)
{
searchList[0].ColumnValue = cParam.ParameterValue;
}
}
}
catch (Exception ex)
{
throw (ex);
}
}
protected void btnEmail_Click(object sender, EventArgs e)
{
try
{
string checkedRequiredField = "";
DataTable dt = new DataTable();
string subject = "";
string fileName = "";
string body = "";
Boolean isSubjectYM = false;
Boolean isFileNameYM = false;
errorList = new CustomList <ErrorList>();
MailSetupManager manager = new MailSetupManager();
CustomList <MailSetup> mailSetup = manager.GetAllMailSetup(Convert.ToInt32(Session["ReportID"]));
MailSetup mS = mailSetup.Find(f => f.Subject.IsNotNullOrEmpty());
if (mS.IsNull())
{
return;
}
else
{
subject = mS.Subject;
fileName = mS.FileName;
body = mS.Body;
isSubjectYM = mS.IsSubjectYM;
isFileNameYM = mS.IsFileNameYM;
}
#region
if (isSubjectYM)
{
FilterSets month = FilterSetList.Find(f => f.ColumnName == "MonthNo");
FilterSets year = FilterSetList.Find(f => f.ColumnName == "YearNo");
string monthYearName = "";
if (month.IsNotNull())
{
monthYearName = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(month.ColumnActualValue.ToInt()).Substring(0, 3) + "' " + year.ColumnActualValue.Substring(2, 2);
}
if (monthYearName == "")
{
FilterSets fromDate = FilterSetList.Find(f => f.ColumnName == "FromDate");
if (fromDate.IsNotNull())
{
monthYearName = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(fromDate.ColumnValue.Substring(0, 2).ToInt()).Substring(0, 3) + "' " + fromDate.ColumnValue.Substring(8, 2);
}
}
if (monthYearName == "")
{
FilterSets workDate = FilterSetList.Find(f => f.ColumnName == "Workdate");
if (workDate.IsNotNull())
{
monthYearName = Convert.ToDateTime(workDate.ColumnValue).ToString("dd MMM yy");
}
}
subject = subject + "_" + monthYearName;
}
if (isFileNameYM)
{
FilterSets month = FilterSetList.Find(f => f.ColumnName == "MonthNo");
FilterSets year = FilterSetList.Find(f => f.ColumnName == "YearNo");
string monthYearName = "";
if (month.IsNotNull())
{
monthYearName = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(month.ColumnActualValue.ToInt()).Substring(0, 3) + "' " + year.ColumnActualValue.Substring(2, 2);
}
if (monthYearName == "")
{
FilterSets fromDate = FilterSetList.Find(f => f.ColumnName == "FromDate");
if (fromDate.IsNotNull())
{
monthYearName = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(fromDate.ColumnValue.Substring(0, 2).ToInt()).Substring(0, 3) + "' " + fromDate.ColumnValue.Substring(8, 2);
}
}
if (monthYearName == "")
{
FilterSets workDate = FilterSetList.Find(f => f.ColumnName == "Workdate");
if (workDate.IsNotNull())
{
monthYearName = Convert.ToDateTime(workDate.ColumnValue).ToString("dd MMM yy");
}
}
fileName = fileName + "_" + monthYearName;
}
string reportPath = Session["reportPath"].ToString();
DataTable dt1 = new DataTable();
if (!mS.IsIndividual)
{
Report.LoadSourceDataSet(ref checkedRequiredField, ref dt1);
}
FilterSets empCode = FilterSetList.Find(f => f.ColumnName == "EmpCode");
if (empCode.ColumnActualValue.IsNotNullOrEmpty())
{
mailSetup = mailSetup.FindAll(f => f.EmpKey.ToString() == empCode.ColumnActualValue);
}
#endregion
foreach (MailSetup m in mailSetup)
{
//if (mS.IsIndividual)
// Report.LoadSourceDataSet(m.EmpKey.ToString(), ref dt1);
//SentEmail sm = new SentEmail();
//string message = sm.EmailUtil(m.EmailAddress, reportPath, dt1, "", subject, fileName, body);
//if (message.IsNotNullOrEmpty())
//{
// ErrorList eL = new ErrorList();
// eL.EmpName = m.EmpName.Trim();
// eL.Error = message;
// errorList.Add(eL);
//}
}
((PageBase)this.Page).SuccessMessage = "Mail sent successfully!";
}
catch (Exception ex)
{
((PageBase)this.Page).ErrorMessage = (ExceptionHelper.getExceptionMessage(ex));
}
}
public void AddOtherColumnToFilterTable(DataColumnCollection dcCollection, List <ReportSuiteColumnValueViewModel> reportColumnList, string ColumnName, bool HasParent)
{
FilterSets newFilter;
RDLColumn rc;
try
{
#region Add Other Column
string[] strColumnArr = ColumnName.Split(',');
foreach (string strColumn in strColumnArr)
{
if (strColumn.Equals("UserCode", StringComparison.OrdinalIgnoreCase))
{
if (!FilterSetList.Any(x => x.ColumnName == strColumn))
{
newFilter = new FilterSets
{
ColumnName = strColumn,
DataType = "System.Int32",
TableName = string.Empty,
OrAnd = "And",
Operators = "=",
ColumnValue = UserCode,
DefaultValue = UserCode,
IsHidden = true
};
FilterSetList.Add(newFilter);
}
}
else
{
rc = Fields.Find(x => x.Name == strColumn);
var reportColumn = reportColumnList.Find(x => x.ColumnId == strColumn);
if (rc != null)
{
if (!FilterSetList.Any(item => item.ColumnName == strColumn && (item.TableName == "Parameter" || item.TableName == rc.TableName)))
{
if (reportColumn != null)
{
if (!string.IsNullOrEmpty(reportColumn.ParentColumns))
{
AddOtherColumnToFilterTable(dcCollection, reportColumnList, reportColumn.ParentColumns, false);
#region Add Filter Set
newFilter = new FilterSets
{
ColumnName = rc.Name,
DataType = rc.DataType,
TableName = rc.TableName,
OrAnd = "And",
Operators = "=",
IsParameter = false,
HasParent = true
};
if (reportColumn != null)
{
newFilter.DefaultValue = reportColumn.DefaultValue ?? string.Empty;
newFilter.ParentColumn = reportColumn.ParentColumns ?? string.Empty;
newFilter.DAOClass = reportColumn.DaoClass ?? string.Empty;
newFilter.MethodName = reportColumn.MethodName ?? string.Empty;
newFilter.ValueColumnId = reportColumn.ValueColumnId ?? string.Empty;
newFilter.DisplayColumnId = reportColumn.DisplayColumnId ?? string.Empty;
newFilter.IsMultipleSelection = reportColumn.IsMultipleSelection;
newFilter.ShowAdditionalColumn = reportColumn.ShowAdditionalColumn;
newFilter.AdditionalColumnId = reportColumn.AdditionalColumnId ?? string.Empty;
newFilter.AdditionalColumnHeader = reportColumn.AdditionalColumnHeader ?? string.Empty;
newFilter.ColumnWidth = reportColumn.ColumnWidth ?? string.Empty;
}
if (rc.DataType == "Boolean" || rc.DataType == "System.Boolean")
{
newFilter.ColumnValue = "false";
newFilter.DefaultValue = "false";
}
FilterSetList.Add(newFilter);
#endregion
}
else
{
#region Add Filter Set
newFilter = new FilterSets
{
ColumnName = rc.Name,
DataType = rc.DataType,
TableName = rc.TableName,
OrAnd = "And",
Operators = "=",
IsParameter = false,
HasParent = HasParent
};
if (reportColumn != null)
{
newFilter.DefaultValue = reportColumn.DefaultValue ?? string.Empty;
newFilter.ParentColumn = reportColumn.ParentColumns ?? string.Empty;
newFilter.DAOClass = reportColumn.DaoClass ?? string.Empty;
newFilter.MethodName = reportColumn.MethodName ?? string.Empty;
newFilter.ValueColumnId = reportColumn.ValueColumnId ?? string.Empty;
newFilter.DisplayColumnId = reportColumn.DisplayColumnId ?? string.Empty;
newFilter.IsMultipleSelection = reportColumn.IsMultipleSelection;
newFilter.ShowAdditionalColumn = reportColumn.ShowAdditionalColumn;
newFilter.AdditionalColumnId = reportColumn.AdditionalColumnId ?? string.Empty;
newFilter.AdditionalColumnHeader = reportColumn.AdditionalColumnHeader ?? string.Empty;
newFilter.ColumnWidth = reportColumn.ColumnWidth ?? string.Empty;
}
if (rc.DataType == "Boolean" || rc.DataType == "System.Boolean")
{
newFilter.ColumnValue = "false";
newFilter.DefaultValue = "false";
}
FilterSetList.Add(newFilter);
#endregion
}
}
else
{
#region Add Filter Set
newFilter = new FilterSets
{
ColumnName = rc.Name,
DataType = rc.DataType,
TableName = rc.TableName,
OrAnd = "And",
Operators = "=",
IsParameter = false,
HasParent = HasParent
};
if (reportColumn != null)
{
newFilter.DefaultValue = reportColumn.DefaultValue ?? string.Empty;
newFilter.ParentColumn = reportColumn.ParentColumns ?? string.Empty;
newFilter.DAOClass = reportColumn.DaoClass ?? string.Empty;
newFilter.MethodName = reportColumn.MethodName ?? string.Empty;
newFilter.ValueColumnId = reportColumn.ValueColumnId ?? string.Empty;
newFilter.DisplayColumnId = reportColumn.DisplayColumnId ?? string.Empty;
newFilter.IsMultipleSelection = reportColumn.IsMultipleSelection;
newFilter.ShowAdditionalColumn = reportColumn.ShowAdditionalColumn;
newFilter.AdditionalColumnId = reportColumn.AdditionalColumnId ?? string.Empty;
newFilter.AdditionalColumnHeader = reportColumn.AdditionalColumnHeader ?? string.Empty;
newFilter.ColumnWidth = reportColumn.ColumnWidth ?? string.Empty;
}
if (rc.DataType == "Boolean" || rc.DataType == "System.Boolean")
{
newFilter.ColumnValue = "false";
newFilter.DefaultValue = "false";
}
FilterSetList.Add(newFilter);
#endregion
}
}
}
else
{
#region Add Filter Column Not in report
if (!FilterSetList.Any(x => x.ColumnName == strColumn))
{
if (reportColumn != null)
{
if (!string.IsNullOrEmpty(reportColumn.ParentColumns))
{
AddOtherColumnToFilterTable(dcCollection, reportColumnList, reportColumn.ParentColumns, false);
#region Add Filter Set
newFilter = new FilterSets
{
ColumnName = strColumn,
DataType = "System.String",
TableName = string.Empty,
OrAnd = "And",
Operators = "=",
IsParameter = false,
HasParent = true,
ExtraFilterColumn = true
};
if (reportColumn != null)
{
newFilter.DefaultValue = reportColumn.DefaultValue ?? string.Empty;
newFilter.ParentColumn = reportColumn.ParentColumns ?? string.Empty;
newFilter.DAOClass = reportColumn.DaoClass ?? string.Empty;
newFilter.MethodName = reportColumn.MethodName ?? string.Empty;
newFilter.ValueColumnId = reportColumn.ValueColumnId ?? string.Empty;
newFilter.DisplayColumnId = reportColumn.DisplayColumnId ?? string.Empty;
newFilter.IsMultipleSelection = reportColumn.IsMultipleSelection;
newFilter.ShowAdditionalColumn = reportColumn.ShowAdditionalColumn;
newFilter.AdditionalColumnId = reportColumn.AdditionalColumnId ?? string.Empty;
newFilter.AdditionalColumnHeader = reportColumn.AdditionalColumnHeader ?? string.Empty;
newFilter.ColumnWidth = reportColumn.ColumnWidth ?? string.Empty;
}
FilterSetList.Add(newFilter);
#endregion
}
else
{
#region Add Filter Set
newFilter = new FilterSets
{
ColumnName = strColumn,
DataType = "System.String",
TableName = string.Empty,
OrAnd = "And",
Operators = "=",
IsParameter = false,
HasParent = HasParent,
ExtraFilterColumn = true
};
if (reportColumn != null)
{
newFilter.DefaultValue = reportColumn.DefaultValue ?? string.Empty;
newFilter.ParentColumn = reportColumn.ParentColumns ?? string.Empty;
newFilter.DAOClass = reportColumn.DaoClass ?? string.Empty;
newFilter.MethodName = reportColumn.MethodName ?? string.Empty;
newFilter.ValueColumnId = reportColumn.ValueColumnId ?? string.Empty;
newFilter.DisplayColumnId = reportColumn.DisplayColumnId ?? string.Empty;
newFilter.IsMultipleSelection = reportColumn.IsMultipleSelection;
newFilter.ShowAdditionalColumn = reportColumn.ShowAdditionalColumn;
newFilter.AdditionalColumnId = reportColumn.AdditionalColumnId ?? string.Empty;
newFilter.AdditionalColumnHeader = reportColumn.AdditionalColumnHeader ?? string.Empty;
newFilter.ColumnWidth = reportColumn.ColumnWidth ?? string.Empty;
}
FilterSetList.Add(newFilter);
#endregion
}
}
}
#endregion
}
}
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
}
public void AddParameterColumnToFilterTable(DataColumnCollection dcCollection, List <ReportSuiteColumnValueViewModel> reportColumnList, RDLParameter rp, ReportSuiteColumnValueViewModel reportColumn, Boolean HasParent)
{
FilterSets newFilter;
RDLColumn rc;
try
{
#region Add Parameter Column
rc = Fields.Find(item => item.Name == rp.Name);
if (!FilterSetList.Any(item => item.ColumnName == rp.Name && (item.TableName == "Parameter" || item.TableName == rc.TableName)))
{
newFilter = new FilterSets
{
ColumnName = rp.Name,
DataType = rp.DbType.ToString(),
TableName = "Parameter",
OrAnd = "And",
Operators = "=",
Caption = "***",
IsParameter = true,
HasParent = HasParent
};
if (rp.Name.Equals("UserCode", StringComparison.OrdinalIgnoreCase))
{
newFilter.ColumnValue = UserCode;
newFilter.DefaultValue = UserCode;
}
else
{
newFilter.DefaultValue = rp.DefaultValue == null ? string.Empty : rp.DefaultValue.ToString();
}
if (reportColumn != null)
{
newFilter.DefaultValue = reportColumn.DefaultValue ?? string.Empty;
newFilter.ParentColumn = reportColumn.ParentColumns ?? string.Empty;
newFilter.DAOClass = reportColumn.DaoClass ?? string.Empty;
newFilter.MethodName = reportColumn.MethodName ?? string.Empty;
newFilter.ValueColumnId = reportColumn.ValueColumnId ?? string.Empty;
newFilter.DisplayColumnId = reportColumn.DisplayColumnId ?? string.Empty;
newFilter.ShowAdditionalColumn = reportColumn.ShowAdditionalColumn;
newFilter.AdditionalColumnId = reportColumn.AdditionalColumnId ?? string.Empty;
newFilter.AdditionalColumnHeader = reportColumn.AdditionalColumnHeader ?? string.Empty;
newFilter.ColumnWidth = reportColumn.ColumnWidth ?? string.Empty;
newFilter.IsMultipleSelection = reportColumn.IsMultipleSelection;
}
if (newFilter.DataType == "Boolean" || newFilter.DataType == "System.Boolean")
{
newFilter.ColumnValue = "false";
}
FilterSetList.Add(newFilter);
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
}