public DBFilter GetEmpInfoFilter(DateTime FromDate, DateTime ToDate)
{
FromDate = FromDate.Date;
ToDate = ToDate.Date;
DBFilter filter = binding.createFilter();
DateTime dtPeriodFr, dtPeriodTo;
if (DateTime.TryParse(JoinDateFrom.Value, out dtPeriodFr))
{
filter.add(new Match("ee.EmpDateOfJoin", ">=", dtPeriodFr));
}
if (DateTime.TryParse(JoinDateTo.Value, out dtPeriodTo))
{
filter.add(new Match("ee.EmpDateOfJoin", "<=", dtPeriodTo));
}
//if (EmpStatus.SelectedValue.Equals("A") || EmpStatus.SelectedValue.Equals("T") || EmpStatus.SelectedValue.Equals("TERMINATED"))
// filter.add(new Match("EmpDateOfJoin", "<=", ToDate.Date));
filter.add(WebUtils.AddRankFilter(Session, "ee.EmpID", true));
DateTime dtLastDateFr, dtLastDateTo;
DBFilter empTerminationFilter = new DBFilter();
if (DateTime.TryParse(LastDateFrom.Value, out dtLastDateFr))
{
empTerminationFilter.add(new Match("searchet.EmpTermLastDate", ">=", dtLastDateFr));
}
if (DateTime.TryParse(LastDateTo.Value, out dtLastDateTo))
{
empTerminationFilter.add(new Match("searchet.EmpTermLastDate", "<=", dtLastDateTo));
}
if (EmpStatus.SelectedValue.Equals("TERMINATED"))
{
empTerminationFilter.add(new Match("searchet.EmpTermLastDate", "<", FromDate.Date));
}
if (empTerminationFilter.terms().Count > 0 || EmpStatus.SelectedValue.Equals("T"))
{
empTerminationFilter.add(new MatchField("ee.EmpID", "searchet.EmpID"));
filter.add(new Exists(EEmpTermination.db.dbclass.tableName + " searchet ", empTerminationFilter));
}
if (EmpStatus.SelectedValue.Equals("AT"))
{
DBFilter notExistsEmpTerminationFilter = new DBFilter();
notExistsEmpTerminationFilter.add(new Match("searchnoet.EmpTermLastDate", "<", FromDate.Date));
notExistsEmpTerminationFilter.add(new MatchField("ee.EmpID", "searchnoet.EmpID"));
filter.add(new Exists(EEmpTermination.db.dbclass.tableName + " searchnoet ", notExistsEmpTerminationFilter, true));
}
if (EmpStatus.SelectedValue.Equals("A"))
{
DBFilter notExistsEmpTerminationFilter = new DBFilter();
notExistsEmpTerminationFilter.add(new MatchField("ee.EmpID", "searchnoet.EmpID"));
filter.add(new Exists(EEmpTermination.db.dbclass.tableName + " searchnoet ", notExistsEmpTerminationFilter, true));
}
DBFilter empPosFilter = new DBFilter();
//{
// OR orPositionTerm = null;
// foreach (ListItem item in PositionList.Items)
// if (item.Selected)
// {
// if (orPositionTerm == null)
// orPositionTerm = new OR();
// orPositionTerm.add(new Match("searchepi.PositionID", item.Value));
// }
// if (orPositionTerm != null)
// empPosFilter.add(orPositionTerm);
//}
DBTerm positionDBTerm = CreateDBTermsFromListControl("searchepi.PositionID", EPosition.db.dbclass.tableName + " pos", "pos.PositionID", PositionList.ListControl);
if (positionDBTerm != null)
{
empPosFilter.add(positionDBTerm);
}
DBTerm rankDBTerms = CreateDBTermsFromListControl("searchepi.RankID", ERank.db.dbclass.tableName + " rank", "rank.RankID", RankList.ListControl);
if (rankDBTerms != null)
{
empPosFilter.add(rankDBTerms);
}
DBTerm employmentTypeDBTerms = CreateDBTermsFromListControl("searchepi.EmploymentTypeID", EEmploymentType.db.dbclass.tableName + " empType", "empType.EmploymentTypeID", EmploymentTypeList.ListControl);
if (employmentTypeDBTerms != null)
{
empPosFilter.add(employmentTypeDBTerms);
}
DBTerm staffTypeDBTerms = CreateDBTermsFromListControl("searchepi.StaffTypeID", EStaffType.db.dbclass.tableName + " staffType", "staffType.StaffTypeID", StaffTypeList.ListControl);
if (staffTypeDBTerms != null)
{
empPosFilter.add(staffTypeDBTerms);
}
DBTerm payrollGroupDBTerms = CreateDBTermsFromListControl("searchepi.PayGroupID", EPayrollGroup.db.dbclass.tableName + " payGroup", "payGroup.PayGroupID", PayrollGroupList.ListControl);
if (payrollGroupDBTerms != null)
{
empPosFilter.add(payrollGroupDBTerms);
}
DBTerm companyAndHierarchyTerm = HierarchyCheckBoxList1.GetFilters("searchepi", "EmpPosID");
if (companyAndHierarchyTerm != null)
{
empPosFilter.add(companyAndHierarchyTerm);
}
if (empPosFilter.terms().Count > 0)
{
empPosFilter.add(new MatchField("ee.EmpID", "searchepi.EmpID"));
EEmpPositionInfo.DBFilterAddDateRange(empPosFilter, "searchepi", FromDate, ToDate);
OR orEmpPosTerm = new OR();
orEmpPosTerm.add(new Exists(EEmpPositionInfo.db.dbclass.tableName + " searchepi ", empPosFilter));
filter.add(orEmpPosTerm);
}
//DBFilter empPosNotExistsFilter = new DBFilter();
//empPosNotExistsFilter.add(new MatchField("ee.EmpID", "np.EmpID"));
//orEmpPosTerm.add(new Exists(EEmpPositionInfo.db.dbclass.tableName + " np", empPosNotExistsFilter, true));
return(filter);
}
public static DataTable GetDataTableFromSelectQueryWithFilter(DatabaseConnection dbConn, string select, string from, DBFilter filter, ListInfo info)
{
DBFilter queryFilter = new DBFilter();
ArrayList afterFilterList = new ArrayList();
foreach (DBTerm term in filter.terms())
{
if (term is Match)
{
Match match = (Match)term;
if (match.value is string && match.op.Equals("Like", StringComparison.CurrentCultureIgnoreCase))
{
afterFilterList.Add(term);
}
else
{
queryFilter.add(term);
}
}
else
{
queryFilter.add(term);
}
}
DataTable table = null;
if (dbConn != null)
{
table = queryFilter.loadData(dbConn, null, select, from, null);
}
//else
//{
// dbConn = DatabaseConnection.GetDatabaseConnection();
// table = queryFilter.loadData(dbConn, null, select, from);
//}
foreach (Match match in afterFilterList)
{
DBAESEncryptStringFieldAttribute.decode(table, match.name);
DataView view = new DataView(table);
view.RowFilter = match.name + " " + (string.IsNullOrEmpty(match.op) ? "=" : match.op) + " '" + ((string)match.value).Replace("'", "''") + "' ";
table = view.ToTable();
}
if (info != null)
{
//if (!string.IsNullOrEmpty(info.orderby))
// if (info.orderby.Equals("EmpEngFullName", StringComparison.CurrentCultureIgnoreCase))
// {
// if (!table.Columns.Contains("EmpEngFullName"))
// {
// table.Columns.Add("EmpEngFullName", typeof(string));
// foreach (System.Data.DataRow row in table.Rows)
// {
// EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
// empInfo.EmpID = (int)row["EmpID"];
// if (EEmpPersonalInfo.db.select(dbConn, empInfo))
// row["EmpEngFullName"] = empInfo.EmpEngFullName;
// }
// }
// }
return(DataTableSortingAndPaging(table, info));
}
else
{
return(table);
}
}