public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = binding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "e.*";
string from = "from [" + db.dbclass.tableName + "] e ";//LEFT JOIN " + EEmpPositionInfo.db.dbclass.tableName + " p ON c.EmpID=p.EmpID AND p.EmpPosEffTo IS NULL";
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
filter.add(new IN("EmpID", "Select distinct EmpID from " + EAttendanceRecord.db.dbclass.tableName, new DBFilter()));
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = sbinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "e.*, ep.EmpPayrollID";
string from = "from [" + db.dbclass.tableName + "] e, EmpPayroll ep, PayrollPeriod pp";
filter.add(new Match("PayGroupID", PayGroupID.SelectedValue));
filter.add(new MatchField("e.EmpID", "ep.EmpID"));
filter.add(new MatchField("ep.PayperiodID", "pp.PayperiodID"));
filter.add(new Match("ep.EmpPayStatus", "T"));
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
filter.add(new IN("e.EmpID", "Select EmpID From " + EEmpPersonalInfo.db.dbclass.tableName + " ee", EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime())));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = binding.createFilter();
string select = "e.*";
string from = "from [" + db.dbclass.tableName + "] e ";
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = sbinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "e.* ";
string from = "from [" + EEmpPersonalInfo.db.dbclass.tableName + "] e ";
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(Payroll_PeriodSelectionList1.GetEmpPayrollDBTerm());
//DBFilter payPeriodFilter = new DBFilter();
//if (DecryptedRequest["PayGroupID"] != null)
// payPeriodFilter.add(new Match("PayGroupID", DecryptedRequest["PayGroupID"]));
//else
// payPeriodFilter.add(new Match("PayGroupID", 0));
filter.add(new IN("e.EmpID", "SELECT DISTINCT ep.EmpID FROM " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter));
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime().Date, AppUtils.ServerDateTime().Date);
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
if (table.Rows.Count != 0)
{
btnGenerate.Visible = true;
}
else
{
btnGenerate.Visible = false;
}
view = new DataView(table);
//if (info != null)
//{
// info.loadPageList(null, PrevPage, NextPage, FirstPage, LastPage);
// CurPage.Value = info.page.ToString();
// NumPage.Value = info.numPage.ToString();
//}
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadEmpData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = binding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = " e.* ";
string from = "from [" + db.dbclass.tableName + "] e ";
IN inTerm = new IN("e.EmpID", "Select ep.EmpID from EmpPayroll ep, PayrollPeriod pp ", filter);
filter.add(new MatchField("e.EmpID", "ep.EmpID"));
filter.add(new MatchField("ep.PayperiodID", "pp.PayperiodID"));
filter.add(new Match("ep.EmpPayStatus", "C"));
DateTime dtPayPeriodFr;
DateTime dtPayPeriodTo;
if (DateTime.TryParse(((WebDatePicker)EmployeeSearchControl1.AdditionElementControl.FindControl("PayPeriodFr")).Value, out dtPayPeriodFr))
{
dtPayPeriodTo = dtPayPeriodFr.AddMonths(12).AddDays(-1);
filter.add(new Match("pp.PayPeriodFr", "<=", dtPayPeriodTo));
filter.add(new Match("pp.PayPeriodFr", ">=", dtPayPeriodFr));
}
filter.add("EmpNo", true);
DBFilter resultFilter = new DBFilter();
resultFilter.add(inTerm);
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
resultFilter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = resultFilter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
//if (info != null)
//{
// info.loadPageList(null, PrevPage, NextPage, FirstPage, LastPage);
// CurPage.Value = info.page.ToString();
// NumPage.Value = info.numPage.ToString();
//}
if (repeater != null)
{
repeater.DataSource = table;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = sbinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = " e.*, ca.CostAllocationID ";
string from = "from " + EEmpPersonalInfo.db.dbclass.tableName + " e, " + ECostAllocation.db.dbclass.tableName + " ca";
filter.add(new MatchField("e.EmpID", "ca.EmpID"));
filter.add(new Match("ca.CostAllocationStatus", "T"));
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(new Match("ep.EmpPayStatus", "C"));
empPayrollFilter.add(new Match("ep.PayPeriodID", CurPayPeriodID));
// Start 0000185, KuangWei, 2015-04-21
//filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
// End 0000185, KuangWei, 2015-04-21
DBFilter inCostAllocationFilter = new DBFilter();
filter.add(new IN("ca.EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter));
// Start 0000185, KuangWei, 2015-04-21
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, info);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
// End 0000185, KuangWei, 2015-04-21
view = new DataView(table);
//if (info != null)
//{
// info.loadPageList(null, PrevPage, NextPage, FirstPage, LastPage);
// CurPage.Value = info.page.ToString();
// NumPage.Value = info.numPage.ToString();
//}
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = binding.createFilter();
if (info != null && info.orderby != null && !info.orderby.Equals(""))
{
filter.add(info.orderby, info.order);
}
string select = "e.*";
string from = "from [" + db.dbclass.tableName + "] e ";
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(Payroll_PeriodSelectionList1.GetEmpPayrollDBTerm("ep"));
filter.add(new IN("e.EmpID", "SELECT DISTINCT ep.EmpID FROM " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter));
DBFilter payPeriodFilter = new SearchBinding(dbConn, EPayrollPeriod.db).createFilter();;
payPeriodFilter.add(new IN("pp.PayPeriodID", "SELECT DISTINCT ep.PayPeriodID FROM " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter));
string sqlStr = " min(pp.PayPeriodFr) as PayPeriodFr, max(pp.PayPeriodTo) as PayPeriodTo ";
string fromStr = " from [" + EPayrollPeriod.db.dbclass.tableName + "] pp ";
DataTable payPeriodTable = payPeriodFilter.loadData(dbConn, null, sqlStr, fromStr);
int rowCount = payPeriodTable.Rows.Count;
if (rowCount > 0 && !string.IsNullOrEmpty(payPeriodTable.Rows[0]["PayPeriodFr"].ToString()))
{
filter.add(new Match("e.EmpDateOfJoin", ">=", ((DateTime)payPeriodTable.Rows[0]["PayPeriodFr"]).ToString("yyyy/MM/dd")));
filter.add(new Match("e.EmpDateOfJoin", "<=", ((DateTime)payPeriodTable.Rows[0]["PayPeriodTo"]).ToString("yyyy/MM/dd")));
}
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadEmpData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = binding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = " e.* ";
string from = "from [" + db.dbclass.tableName + "] e ";
IN inTerm = new IN("e.EmpID", "Select ete.EmpID from EmpTrainingEnroll ete, TrainingSeminar ts ", filter);
filter.add(new MatchField("ete.TrainingSeminarID", "ts.TrainingSeminarID"));
//DateTime dtTrainingSeminarFrom;
//DateTime dtTrainingSeminarTo;
//if (DateTime.TryParse(((WebDatePicker)EmployeeSearchControl1.AdditionElementControl.FindControl("TrainingSeminarDateFrom")).Value, out dtTrainingSeminarFrom))
// filter.add(new Match("ts.TrainingSeminarDateTo", ">=", dtTrainingSeminarFrom));
//if (DateTime.TryParse(((WebDatePicker)EmployeeSearchControl1.AdditionElementControl.FindControl("TrainingSeminarDateTo")).Value, out dtTrainingSeminarTo))
// filter.add(new Match("ts.TrainingSeminarDateFrom", "<=", dtTrainingSeminarTo));
DBFilter resultFilter = new DBFilter();
resultFilter.add(inTerm);
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
resultFilter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = resultFilter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
//if (info != null)
//{
// info.loadPageList(null, PrevPage, NextPage, FirstPage, LastPage);
// CurPage.Value = info.page.ToString();
// NumPage.Value = info.numPage.ToString();
//}
if (repeater != null)
{
repeater.DataSource = table;
repeater.DataBind();
}
return(view);
}
public DataView loadEmpData(ListInfo info, DBManager db, Repeater repeater)
{
WebDatePicker PayPeriodAsOFDate = (WebDatePicker)EmployeeSearchControl1.AdditionElementControl.FindControl("PayPeriodAsOFDate");
DBFilter filter = binding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "e.*, ep.*, pp.PayPeriodFr, pp.PayperiodTo, pp.PayPeriodConfirmDate";
string from = "from [" + db.dbclass.tableName + "] e, EmpPayroll ep, PayrollPeriod pp ";
filter.add(new MatchField("e.EmpID", "ep.EmpID"));
filter.add(new MatchField("ep.PayperiodID", "pp.PayperiodID"));
filter.add(new Match("ep.EmpPayStatus", "C"));
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
DateTime dtPayPeriodAsOFDate;
if (DateTime.TryParse(PayPeriodAsOFDate.Value, out dtPayPeriodAsOFDate))
{
filter.add(new Match("pp.PayPeriodFr", "<=", dtPayPeriodAsOFDate));
filter.add(new Match("pp.PayPeriodTo", ">=", dtPayPeriodAsOFDate));
}
if (dtPayPeriodAsOFDate.Ticks.Equals(0))
{
dtPayPeriodAsOFDate = AppUtils.ServerDateTime().Date;
}
filter.add(new IN("e.EmpID", "Select EmpID from " + EEmpPersonalInfo.db.dbclass.tableName + " ee", EmployeeSearchControl1.GetEmpInfoFilter(dtPayPeriodAsOFDate, dtPayPeriodAsOFDate)));
//if (filter.getOrderClause().IndexOf("PayPeriodFr")<0)
// filter.add("EmpNo", true);
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = table;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = binding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "e.*";
string from = "from [" + db.dbclass.tableName + "] e ";//LEFT JOIN " + EEmpPositionInfo.db.dbclass.tableName + " p ON c.EmpID=p.EmpID AND p.EmpPosEffTo IS NULL";
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
//foreach (RepeaterItem item in HierarchyLevel.Items)
//{
// DBFilter sub = null;
// DropDownList c = (DropDownList)item.FindControl("HElementID");
// string v = c.SelectedValue;
// if (!v.Equals(""))
// {
// if (sub == null)
// {
// sub = new DBFilter();
// sub.add(new MatchField("p.EmpPosID", "EmpPosID"));
// }
// sub.add(new Match("HLevelID", c.Attributes["HLevelID"]));
// sub.add(new Match("HElementID", v));
// }
// if (sub != null)
// filter.add(new Exists(EEmpHierarchy.db.dbclass.tableName, sub));
//}
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = binding.createFilter();
if (info != null && info.orderby != null && !info.orderby.Equals(""))
{
filter.add(info.orderby, info.order);
}
string select = "et.EmpTermID, e.*";
string from = "from [" + db.dbclass.tableName + "] e ";//LEFT JOIN " + EEmpPositionInfo.db.dbclass.tableName + " p ON c.EmpID=p.EmpID AND p.EmpPosEffTo IS NULL";
from += " inner join " + EEmpTermination.db.dbclass.tableName + " et on et.empid = e.empid ";
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
//DateTime dtLastEmploymentDateFrom, dtLastEmploymentDateTo;
//if (DateTime.TryParse(((WebDatePicker)EmployeeSearchControl1.AdditionElementControl.FindControl("LastEmploymentDateFrom")).Value, out dtLastEmploymentDateFrom))
// filter.add(new Match("et.EmpTermLastDate", ">=", dtLastEmploymentDateFrom));
//if (DateTime.TryParse(((WebDatePicker)EmployeeSearchControl1.AdditionElementControl.FindControl("LastEmploymentDateTo")).Value, out dtLastEmploymentDateTo))
// filter.add(new Match("et.EmpTermLastDate", "<=", dtLastEmploymentDateTo));
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(Payroll_PeriodSelectionList1.GetEmpPayrollDBTerm());
DBFilter lastEmploymentDateFilter = new DBFilter();
lastEmploymentDateFilter.add(new MatchField("et.EmpTermLastDate", "<=", "tmpPP.PayPeriodTo"));
empPayrollFilter.add(new IN("ep.PayPeriodID", "SELECT tmpPP.PayPeriodID FROM " + EPayrollPeriod.db.dbclass.tableName + " tmpPP", lastEmploymentDateFilter));
filter.add(new IN("e.EmpID", "SELECT DISTINCT ep.EmpID FROM " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter));
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = binding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
// only staffs with commission calculation is configured through latest Recurring Payment
DBFilter m_rpFilter = new DBFilter();
OR m_or = new OR();
m_or.add(new NullTerm("EmpRPEffTo"));
m_or.add(new Match("EmpRPEffTo", ">=", Utility.LastDateOfMonth(AppUtils.ServerDateTime())));
m_rpFilter.add(new Match("EmpRPEffFr", "<=", Utility.LastDateOfMonth(AppUtils.ServerDateTime())));
m_rpFilter.add(m_or);
DBFilter m_isHitRateBasedFilter = new DBFilter();
m_isHitRateBasedFilter.add(new Match("PaymentCodeIsHitRateBased", true));
m_rpFilter.add(new IN("PayCodeID", "SELECT PaymentCodeID FROM PaymentCode", m_isHitRateBasedFilter));
filter.add(new IN("EmpID", "SELECT tmpRP.EmpID FROM EmpRecurringPayment tmpRP", m_rpFilter));
string select = "e.* ";
string from = "from [" + db.dbclass.tableName + "] e ";
//DataTable table = filter.loadData(info, select, from);
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = sbinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "e.*, ep.*";
string from = "from [" + db.dbclass.tableName + "] ep, EmpPersonalInfo e ";
filter.add(new MatchField("e.EmpID", "ep.EmpID"));
filter.add(new Match("ep.EmpPayStatus", "T"));
filter.add(new Match("ep.PayPeriodID", CurPayPeriodID));
// Start 0000185, KuangWei, 2015-04-21
//filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, info);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
// End 0000185, KuangWei, 2015-04-21
if (table.Rows.Count > 0)
{
btnUndo.Visible = IsAllowEdit;
}
else
{
btnUndo.Visible = false;
}
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
string m_year = this.Year.Text; // ((TextBox)EmployeeSearchControl1.AdditionElementControl.FindControl("Year")).Text;
string m_month = this.Month.SelectedValue; //((DropDownList)EmployeeSearchControl1.AdditionElementControl.FindControl("Month")).SelectedValue;
if (!string.IsNullOrEmpty(m_year) && !string.IsNullOrEmpty(m_month))
{
DBFilter filter = new DBFilter();
filter.add(getEffRangeDBTerm(m_year, m_month, null));
DBFilter empfilter = sbinding.createFilter();
empfilter.add(new IN("e.EmpID", "Select distinct EmpID from " + EEmpCostCenter.db.dbclass.tableName, filter));
// Start 0000110, Ricky So, 2014/12/16
DBFilter m_securityFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
m_securityFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
empfilter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", m_securityFilter));
//// Start 0000129, Ricky So, 2014/11/11
//empfilter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
//// End 0000129, Ricky So, 2014/11/11
// End 0000110, Ricky So, 2014/12/16
string select = " e.* ";
string from = " from " + db.dbclass.tableName + " e ";
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, empfilter, info);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
if (table.Rows.Count > 0)
{
panelPayPeriod.Visible = true;
}
view = new DataView(table);
ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
}
return(view);
}
//public void loadState()
//{
// NotConfirmEmpInfo = NotConfirm_ListFooter.ListInfo; //new ListInfo();
// //int page = 0;
// //if (!NotConfirm_CurPage.Value.Equals(""))
// // page = Int32.Parse(NotConfirm_CurPage.Value);
// //NotConfirmEmpInfo.loadState(Request, page);
// NotTrialRunEmpInfo = NotTrialRun_ListFooter.ListInfo; //new ListInfo();
// //page = 0;
// //if (!NotTrialRun_CurPage.Value.Equals(""))
// // page = Int32.Parse(NotTrialRun_CurPage.Value);
// //NotTrialRunEmpInfo.loadState(Request, page);
//}
public DataView loadNotConfirmData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = sNotConfirmEmpBinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "e.*, ep.EmpPayrollID";
string from = "from [" + db.dbclass.tableName + "] ep, EmpPersonalInfo e ";
filter.add(new MatchField("e.EmpID", "ep.EmpID"));
filter.add(new Match("ep.EmpPayStatus", "T"));
filter.add(new Match("ep.PayPeriodID", CurPayPeriodID));
// Start 0000185, KuangWei, 2015-04-21
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, info);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
// End 0000185, KuangWei, 2015-04-21
if (table.Rows.Count > 0)
{
panelNotConfirmEmployeeList.Visible = true;
panelProcessEndOption.Visible = false;
}
else
{
panelNotConfirmEmployeeList.Visible = false;
}
NotConfirmEmpView = new DataView(table);
NotConfirm_ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = NotConfirmEmpView;
repeater.DataBind();
}
return(NotConfirmEmpView);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = new DBFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "TE.TaxEmpID, e.* ";
string from = "FROM " + db.dbclass.tableName + " TE LEFT JOIN EmpPersonalInfo E ON TE.EmpID=E.EmpID ";
//DBFilter taxFormFilter = new DBFilter();
//taxFormFilter.add(new Match("Tf.TaxFormYear", int.Parse(YearSelect.SelectedValue)));
//taxFormFilter.add(new Match("Tf.TaxFormType","B"));
//taxFormFilter.add(new Match("Tf.TaxCompID", int.Parse(TaxCompID.SelectedValue)));
DBFilter taxEmpFilter = new DBFilter();
//filter.add (new MatchField("TE.EmpID","E.EmpID"));
filter.add(new Match("TaxFormID", CurrentTaxFormID));
OR orEmpIDTerm = new OR();
orEmpIDTerm.add(new IN("TE.EmpID", "Select EE.EmpID from " + EEmpPersonalInfo.db.dbclass.tableName + " ee", EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime().Date, AppUtils.ServerDateTime().Date)));
orEmpIDTerm.add(new IN("NOT TE.EmpID", "SELECT EE2.EmpID from " + EEmpPersonalInfo.db.dbclass.tableName + " ee2", null));
filter.add(orEmpIDTerm);
//filter.add(new IN("Te.TaxFormID","Select Tf.TaxformID from TaxForm tf",taxFormFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = binding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = " e.*";
string from = "from [" + db.dbclass.tableName + "] e ";//LEFT JOIN " + EEmpPositionInfo.db.dbclass.tableName + " p ON c.EmpID=p.EmpID AND p.EmpPosEffTo IS NULL";
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
DateTime dtEmpDateOfJoinFrom = new DateTime(), dtEmpDateOfJoinTo = new DateTime();
if (DateTime.TryParse(EmpDateOfJoinFrom.Value, out dtEmpDateOfJoinFrom))
{
filter.add(new Match("e.EmpDateOfJoin", ">=", dtEmpDateOfJoinFrom));
}
if (DateTime.TryParse(EmpDateOfJoinTo.Value, out dtEmpDateOfJoinTo))
{
filter.add(new Match("e.EmpDateOfJoin", "<=", dtEmpDateOfJoinTo));
}
filter.add(new NullTerm("NOT e.EmpDateOfJoin"));
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = sbinding.createFilter();
string select = "e.* ";
string from = "from [" + EEmpPersonalInfo.db.dbclass.tableName + "] e ";
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(Payroll_PeriodSelectionList1.GetEmpPayrollDBTerm());
filter.add(new IN("e.EmpID", "SELECT DISTINCT ep.EmpID FROM " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter));
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime().Date, AppUtils.ServerDateTime().Date);
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
if (table.Rows.Count != 0)
{
btnGenerate.Visible = true;
}
else
{
btnGenerate.Visible = false;
}
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData2(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = new DBFilter();
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
int m_batchID = -1;
if (!int.TryParse(BatchID.SelectedValue, out m_batchID))
{
m_batchID = -1;
}
DBFilter m_batchFilter = new DBFilter();
m_batchFilter.add(new Match("CAImportBatchID", m_batchID));
filter.add(new IN("EmpID", "SELECT EmpID FROM CommissionAchievement ", m_batchFilter));
string select = "e.* ";
string from = "from [" + db.dbclass.tableName + "] e ";
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView emploadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = new DBFilter();// empSBinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "* ";
string from = "from [" + EEmpPersonalInfo.db.dbclass.tableName + "] e ";
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
ListFooter.Refresh();
if (table.Rows.Count != 0)
{
btnGenerate.Visible = true;
}
else
{
btnGenerate.Visible = false;
}
empView = new DataView(table);
if (repeater != null)
{
repeater.DataSource = empView;
repeater.DataBind();
}
return(empView);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = binding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
// only staffs with commission calculation is configured through latest Recurring Payment
DBTerm m_inCondition = CreateFilterByProcess(gProcessName, gPID);
if (m_inCondition != null)
{
filter.add(m_inCondition);
}
string select = "e.* ";
string from = "from [" + db.dbclass.tableName + "] e ";
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = binding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = " e.*";
string from = "from [" + db.dbclass.tableName + "] e ";//LEFT JOIN " + EEmpPositionInfo.db.dbclass.tableName + " p ON c.EmpID=p.EmpID AND p.EmpPosEffTo IS NULL";
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
DateTime dtLastProbationDateFrom = new DateTime(), dtLastProbationDateTo = new DateTime();
OR orEmpProbaLastDate = new OR();
AND andEmpProbaLastDateRange = new AND();
andEmpProbaLastDateRange.add(new MatchField("e.EmpProbaLastDate", ">=", "e.EmpDateOfJoin"));
if (DateTime.TryParse(LastProbationDateFrom.Value, out dtLastProbationDateFrom))
{
//andEmpProbaLastDateRange = new AND();
andEmpProbaLastDateRange.add(new Match("e.EmpProbaLastDate", ">=", dtLastProbationDateFrom));
}
if (DateTime.TryParse(LastProbationDateTo.Value, out dtLastProbationDateTo))
{
//if (andEmpProbaLastDateRange == null)
// andEmpProbaLastDateRange = new AND();
andEmpProbaLastDateRange.add(new Match("e.EmpProbaLastDate", "<=", dtLastProbationDateTo));
}
if (andEmpProbaLastDateRange != null)
{
orEmpProbaLastDate.add(andEmpProbaLastDateRange);
}
orEmpProbaLastDate.add(new NullTerm("e.EmpProbaLastDate"));
filter.add(orEmpProbaLastDate);
int probationPeriod = -1;
if (int.TryParse(EmpProbaPeriod.Text, out probationPeriod))
{
if (!string.IsNullOrEmpty(EmpProbaUnit.SelectedValue))
{
filter.add(new Match("e.EmpProbaPeriod", probationPeriod));
filter.add(new Match("e.EmpProbaUnit", EmpProbaUnit.SelectedValue));
}
}
else if (!string.IsNullOrEmpty(EmpProbaPeriod.Text))
{
EmpProbaPeriod.Text = string.Empty;
}
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = FilterNullProbationLastDate(table, dtLastProbationDateFrom, dtLastProbationDateTo);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView emploadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime().Date, AppUtils.ServerDateTime().Date);//new DBFilter();// empSBinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "* ";
string from = "from [" + EEmpPersonalInfo.db.dbclass.tableName + "] ee ";
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(new MatchField("ee.EmpID", "ep.EmpID"));
empPayrollFilter.add(new Match("ep.EmpPayStatus", "C"));
// empPayrollFilter.add(new Match("ep.PayPeriodID", CurPayPeriodID));
DateTime dtPayPeriodFr, dtPayPeriodTo;
if (DateTime.TryParse(PayPeriodFr.Value, out dtPayPeriodFr) && DateTime.TryParse(PayPeriodTo.Value, out dtPayPeriodTo))
{
DBFilter payPeriodFilter = new DBFilter();
// refer to Date To of payperiod
payPeriodFilter.add(new Match("pp.PayPeriodTo", "<=", dtPayPeriodTo));
payPeriodFilter.add(new Match("pp.PayPeriodTo", ">=", dtPayPeriodFr));
empPayrollFilter.add(new IN("PayperiodID ", "Select payperiodID from PayrollPeriod pp", payPeriodFilter));
}
DBFilter mpfRecordFilter = new DBFilter();
mpfRecordFilter.add(new Match("MPFPlanID", MPFPlanID.SelectedValue));
mpfRecordFilter.add(new Match("MPFRecType", "N"));
empPayrollFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from MPFRecord", mpfRecordFilter));
Exists exists = new Exists(EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter);
filter.add(exists);
filter.add(WebUtils.AddRankFilter(Session, "ee.EmpID", true));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
if (table.Rows.Count != 0)
{
ReportExportControl1.Visible = true;
ReportExportControl2.Visible = true;
}
else
{
ReportExportControl1.Visible = false;
ReportExportControl2.Visible = false;
}
empView = new DataView(table);
//if (info != null)
//{
// info.loadPageList(null, empPrevPage, empNextPage, empFirstPage, empLastPage);
// CurPage.Value = info.page.ToString();
// NumPage.Value = info.numPage.ToString();
//}
if (repeater != null)
{
repeater.DataSource = empView;
repeater.DataBind();
}
return(empView);
}
public DataView loadNotTrialRunData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = sNotTrialRunEmpBinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "e.* ";
string from = "from [" + db.dbclass.tableName + "] e, [EmpPositionInfo] ep, [PayrollPeriod] pp ";
filter.add(new MatchField("e.EmpID", "ep.EmpID"));
filter.add(new MatchField("ep.PayGroupID", "pp.PayGroupID"));
filter.add(new MatchField("ep.EmpPosEffFr", "<=", "pp.PayPeriodTo"));
filter.add(new MatchField("e.EmpDateOfJoin", "<=", "pp.PayPeriodTo"));
filter.add(new Match("pp.PayPeriodID", CurPayPeriodID));
OR orFilter = new OR();
orFilter.add(new MatchField("ep.EmpPosEffTo", ">=", "pp.PayPeriodFr"));
orFilter.add(new NullTerm("ep.EmpPosEffTo"));
filter.add(orFilter);
filter.add(new IN("Not e.empid", "Select et.empid from [EmpTermination] et where et.EmpTermLastDate<pp.PayPeriodFr", new DBFilter()));
filter.add(new IN("Not e.empid", "Select ep.empid from [EmpPayroll] ep where pp.PayPeriodID=ep.PayPeriodID and ep.EmpPayIsRP='Y'", new DBFilter()));
// Start 0000185, KuangWei, 2015-04-21
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, info);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
// End 0000185, KuangWei, 2015-04-21
if (table.Rows.Count > 0)
// Start 0000096, KuangWei, 2014-09-18
{
panelNotTrialRunEmployeeList.Visible = true; //& panelProcessEndOption.Visible;
notTrialRunCount = table.Rows.Count;
//string msg = notTrialRunCount + " of employees have not been run!!! Are you sure to end this payroll process?";
string msg = "Certain number of employees have not been run!!! Are you sure to end this payroll process?";
btnProcessEnd.OnClientClick = HROne.Translation.PromptMessage.CreateConfirmDialogJavascript(HROne.Common.WebUtility.GetLocalizedString(msg));
}
// End 0000096, KuangWei, 2014-09-18
else
{
panelNotTrialRunEmployeeList.Visible = false;
}
NotTrialRunEmpView = new DataView(table);
NotTrialRun_ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = NotTrialRunEmpView;
repeater.DataBind();
}
return(NotTrialRunEmpView);
}
public DataView LoadFinalPaymentData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = finalPaymentSearchBinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "e.* ";
string from = "from EmpPersonalInfo e ";
IN inTerm = new IN("e.EmpID", "Select DISTINCT epi.EmpID from [EmpPositionInfo] epi, [PayrollPeriod] pp ", filter);
filter.add(new MatchField("e.EmpID", "epi.EmpID"));
filter.add(new MatchField("epi.PayGroupID", "pp.PayGroupID"));
filter.add(new MatchField("epi.EmpPosEffFr", "<=", "pp.PayPeriodTo"));
filter.add(new Match("pp.PayPeriodID", CurID));
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
OR orFilter = new OR();
orFilter.add(new MatchField("epi.EmpPosEffTo", ">=", "pp.PayPeriodFr"));
orFilter.add(new NullTerm("epi.EmpPosEffTo"));
filter.add(orFilter);
filter.add(new IN("Not e.empid", "Select DISTINCT empid from EmpPayroll where EmpPayStatus='T'", new DBFilter()));
DBFilter empTerminationFilter = new DBFilter();
if (DecryptedRequest["RecurringPayment"] == "1" || DecryptedRequest["YearEndBonus"] == "1")
{
empTerminationFilter.add(new MatchField("et.EmpTermLastDate", ">=", "pp.PayPeriodFr"));
}
empTerminationFilter.add(new MatchField("et.EmpTermLastDate", "<=", "pp.PayPeriodTo"));
filter.add(new IN(" e.empid", "Select DISTINCT et.empid from EmpTermination et ", empTerminationFilter));
if (DecryptedRequest["RecurringPayment"] == "1")
{
// PayRecType should be 'F'
filter.add(new IN("Not e.empid", "Select DISTINCT epp.empid from EmpPayroll epp where pp.PayPeriodID=epp.PayPeriodID and epp.EmpPayIsRP='Y'", new DBFilter()));
}
else if (DecryptedRequest["ClaimsAndDeduction"] == "1")
{
// Claims and Deductions only
DBFilter CNDFilter = new DBFilter();
CNDFilter.add(new MatchField("CNDEffDate", "<=", "pp.PayperiodTo"));
CNDFilter.add(new MatchField("CNDEffDate", ">=", "epi.EmpPosEffFr"));
OR orCNDPos = new OR();
orCNDPos.add(new MatchField("CNDEffDate", "<=", "epi.EmpPosEffTo"));
orCNDPos.add(new NullTerm("epi.EmpPosEffTo"));
CNDFilter.add(orCNDPos);
OR orCNDPayRecID = new OR();
orCNDPayRecID.add(new Match("PayRecID", 0));
orCNDPayRecID.add(new NullTerm("PayRecID"));
CNDFilter.add(orCNDPayRecID);
filter.add(new IN("e.empid", "Select DISTINCT empid from ClaimsAndDeductions ", CNDFilter));
}
else if (DecryptedRequest["AdditionalRemuneration"] == "1" && (DecryptedRequest["ClaimsAndDeduction"] != "1" && DecryptedRequest["RecurringPayment"] != "1"))
{
filter.add(new IN("e.empid", "Select DISTINCT epp.empid from EmpPayroll epp where pp.PayPeriodID=epp.PayPeriodID", new DBFilter()));
}
else if (DecryptedRequest["YearEndBonus"] == "1")
{
// Year End Bonus Only
filter.add(new IN("Not e.empid", "Select DISTINCT epp.empid from EmpPayroll epp where pp.PayPeriodID=epp.PayPeriodID and epp.EmpPayIsYEB='Y'", new DBFilter()));
}
DBFilter resultFilter = new DBFilter();
resultFilter.add(inTerm);
resultFilter.add(new IN("e.EmpID", "Select DISTINCT EmpID From " + EEmpPersonalInfo.db.dbclass.tableName + " ee", EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime())));
//DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(select, from, resultFilter, info);
DataTable table = resultFilter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
newJoinView = new DataView(table);
if (info != null)
{
//info.loadPageList(null, PrevPage, NextPage, FirstPage, LastPage);
//CurPage.Value = info.page.ToString();
//NumPage.Value = info.numPage.ToString();
}
if (repeater != null)
{
repeater.DataSource = newJoinView;
repeater.DataBind();
}
return(newJoinView);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = sbinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "e.*, TE.TaxEmpID, TE.TaxEmpIsReleasePrintoutToESS, et.EmpTermLastDate ";
string from = "from [" + db.dbclass.tableName + "] TE, EmpPersonalInfo e Left Join EmpTermination et on e.EmpID=et.EmpID";
//DBFilter taxFormFilter = new DBFilter();
//taxFormFilter.add(new Match("Tf.TaxFormYear", int.Parse(YearSelect.SelectedValue)));
//taxFormFilter.add(new Match("Tf.TaxFormType","B"));
//taxFormFilter.add(new Match("Tf.TaxCompID", int.Parse(TaxCompID.SelectedValue)));
DBFilter taxEmpFilter = new DBFilter();
filter.add(new MatchField("TE.EmpID", "E.EmpID"));
filter.add(new Match("TaxFormID", CurrentTaxFormID));
//filter.add(new IN("Te.TaxFormID","Select Tf.TaxformID from TaxForm tf",taxFormFilter));
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime().Date, AppUtils.ServerDateTime().Date);
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DateTime dtLastEmploymentDateFrom, dtLastEmploymentDateTo;
if (DateTime.TryParse(((WebDatePicker)EmployeeSearchControl1.AdditionElementControl.FindControl("LastEmploymentDateFrom")).Value, out dtLastEmploymentDateFrom))
{
filter.add(new Match("et.EmpTermLastDate", ">=", dtLastEmploymentDateFrom));
}
if (DateTime.TryParse(((WebDatePicker)EmployeeSearchControl1.AdditionElementControl.FindControl("LastEmploymentDateTo")).Value, out dtLastEmploymentDateTo))
{
filter.add(new Match("et.EmpTermLastDate", "<=", dtLastEmploymentDateTo));
}
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
if (table.Rows.Count != 0)
{
btnPostToESS.Visible = true & IsAllowEdit;
}
else
{
btnPostToESS.Visible = false & IsAllowEdit;
}
view = new DataView(table);
//if (info != null)
//{
// info.loadPageList(null, PrevPage, NextPage, FirstPage, LastPage);
// CurPage.Value = info.page.ToString();
// NumPage.Value = info.numPage.ToString();
//}
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
//public void loadState()
//{
// info = new ListInfo();
// int page = 0;
// if (!CurPage.Value.Equals(""))
// page = Int32.Parse(CurPage.Value);
// info.loadState(Request, page);
// info.order = Order.Value.Equals("True");
// info.orderby = OrderBy.Value;
// if (info.orderby == "")
// info.orderby = null;
// info.recordPerPage = -1;
//}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
//ESSAuthorizationProcess authorization = new ESSAuthorizationProcess(dbConn);
//List<EAuthorizationGroup> authGroupList = authorization.GetAuthorizerAuthorizationGroupList(CurID);
//if (authGroupList.Count > 0)
//{
// Start 0000064, Miranda, 2014-09-19
// Start 0000124, Miranda, 2014-11-10
DBFilter filter = binding.createFilter();
// End 0000124, Miranda, 2014-11-10
// End 0000064, Miranda, 2014-09-19
DateTime dtPeriodFr, dtPeriodTo;
if (DateTime.TryParse(EmpRequestFromDate.Value, out dtPeriodFr))
{
filter.add(new Match("EmpRequestApprovalHistoryCreateDateTime", ">=", dtPeriodFr));
}
if (DateTime.TryParse(EmpRequestToDate.Value, out dtPeriodTo))
{
filter.add(new Match("EmpRequestApprovalHistoryCreateDateTime", "<", dtPeriodTo.AddDays(1)));
}
// Start 0000064, Miranda, 2014-09-19
// Start 0000124, Miranda, 2014-11-10
filter.add(new MatchField("R.EmpID", "E.EmpID"));
// End 0000124, Miranda, 2014-11-10
// End 0000064, Miranda, 2014-09-19
filter.add(new MatchField("rah.EmpRequestID", "R.EmpRequestID"));
// Start 0000208, Miranda, 2015-06-12
//filter.add(new Match("rah.EmpRequestApprovalHistoryActionByEmpID", CurID));
DBFilter m_filter = new DBFilter();
m_filter.add(new Match("a.EmpID", CurID));
DataTable m_table = AppUtils.runSelectSQL("a.AuthorizationGroupID", "From " + EAuthorizer.db.dbclass.tableName + " a", m_filter, dbConn);
DBFilter a_filter = new DBFilter();
if (m_table.Rows != null)
{
ArrayList list = new ArrayList();
foreach (DataRow row in m_table.Rows)
{
list.Add(row[0].ToString());
}
String[] values = (String[])list.ToArray(typeof(String));
a_filter.add(new IN("a.AuthorizationGroupID", values));
}
DataTable a_table = AppUtils.runSelectSQL("distinct a.EmpID", "From " + EAuthorizer.db.dbclass.tableName + " a", a_filter, dbConn);
if (a_table.Rows != null)
{
ArrayList list = new ArrayList();
foreach (DataRow row in a_table.Rows)
{
list.Add(row[0].ToString());
}
String[] values = (String[])list.ToArray(typeof(String));
filter.add(new IN("rah.EmpRequestApprovalHistoryActionByEmpID", values));
}
// End 0000208, Miranda, 2015-06-12
// Start 0000180, KuangWei, 2015-03-25
// Start 0000064, Miranda, 2014-09-19
// Start 0000124, Miranda, 2014-11-10
string select = "R.*, rah.*, E.EmpNo, E.EmpEngSurname, E.EmpEngOtherName, E.EmpChiFullName, E.EmpAlias, E.EmpGender, E.EmpHKID, E.EmpPassportNo, L.LeaveCode, L.LeaveCodeDesc ";
string from = "from " + db.dbclass.tableName + " R , " + EEmpRequestApprovalHistory.db.dbclass.tableName + " rah "
+ ", " + EEmpPersonalInfo.db.dbclass.tableName + " E " + ", " + ERequestLeaveApplication.db.dbclass.tableName + " C, " + ELeaveCode.db.dbclass.tableName + " L";
// End 0000124, Miranda, 2014-11-10
// End 0000064, Miranda, 2014-09-19
filter.add(new MatchField("C.RequestLeaveAppID", "R.EmpRequestRecordID"));
filter.add(new MatchField("L.LeaveCodeID", "C.RequestLeaveCodeID"));
// End 0000180, KuangWei, 2015-03-25
// Start 0000124, Miranda, 2014-11-10
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("E.EmpID", "ee.EmpID"));
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
// End 0000124, Miranda, 2014-11-10
//DBFilter authorizerFilter = new DBFilter();
//authorizerFilter.add(new Match("EmpID", CurID));
//OR orFirstGrpStatusTerms = new OR();
//orFirstGrpStatusTerms.add(new Match("R.EmpRequestStatus", EEmpRequest.STATUS_SUBMITTED));
//DBFilter firstGrpFilter = new DBFilter();
//firstGrpFilter.add(orFirstGrpStatusTerms);
//firstGrpFilter.add(orAuthorizationGrpApplicationTerms);
//firstGrpFilter.add(new IN("AuthorizationGroupID", "Select AuthorizationGroupID from " + EAuthorizer.db.dbclass.tableName, authorizerFilter));
//OR orSecondGrpStatusTerms = new OR();
//orSecondGrpStatusTerms.add(new Match("R.EmpRequestStatus", EEmpRequest.STATUS_SUBMITTED));
//orSecondGrpStatusTerms.add(new Match("R.EmpRequestStatus", EEmpRequest.STATUS_ACCEPTED));
//DBFilter secondGrpFilter = new DBFilter();
//secondGrpFilter.add(orSecondGrpStatusTerms);
//secondGrpFilter.add(orAuthorizationGrpApplicationTerms);
//secondGrpFilter.add(new IN("AuthorizationGroupID", "Select AuthorizationGroupID from " + EAuthorizer.db.dbclass.tableName, authorizerFilter));
//OR orAuthorizationGrpFilter = new OR();
//orAuthorizationGrpFilter.add(new IN("EP.EmpFirstAuthorizationGp", "Select AuthorizationGroupID from " + EAuthorizationGroup.db.dbclass.tableName, firstGrpFilter));
//orAuthorizationGrpFilter.add(new IN("EP.EmpSecondAuthorizationGp", "Select AuthorizationGroupID from " + EAuthorizationGroup.db.dbclass.tableName, secondGrpFilter));
//filter.add(orAuthorizationGrpFilter);
// Start 0000124, Miranda, 2014-11-10
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
// Start 0000064, Miranda, 2014-09-19
//if (table.Rows.Count > 0)
// table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, new ListInfo());
// End 0000064, Miranda, 2014-09-19
// End 0000124, Miranda, 2014-11-10
view = new DataView(table);
ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
//}
//else
// return null;
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
int m_year;
int m_month;
int m_year2;
int m_month2;
if (int.TryParse(Year.Text, out m_year) && m_year > 1980)
{
if (int.TryParse(Month.SelectedValue, out m_month) && m_month > 0)
{
if (int.TryParse(Year2.Text, out m_year2) && m_year2 > 1980)
{
if (int.TryParse(Month2.SelectedValue, out m_month2) && m_month2 > 0)
{
DateTime m_fromDate = new DateTime(m_year, m_month, 1);
DateTime m_toDate = new DateTime(m_year2, m_month2, DateTime.DaysInMonth(m_year2, m_month2));
if (m_fromDate > m_toDate)
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.addError("Invalid Date Range Parameter: From-Date must be prior to To-Date");
}
else
{
DBFilter filter = new DBFilter();
OR orEffTo = new OR();
filter.add(new Match("EmpCostCenterEffFr", "<=", m_toDate));
orEffTo.add(new Match("EmpCostCenterEffTo", ">=", m_fromDate));
orEffTo.add(new NullTerm("EmpCostCenterEffTo"));
filter.add(orEffTo);
DBFilter empfilter = sbinding.createFilter();
empfilter.add(new IN("e.EmpID", "Select distinct EmpID from " + EEmpCostCenter.db.dbclass.tableName, filter));
//empfilter.add(WebUtils.AddRankFilter(Session, "EmpID", true));
string select = " e.* ";
string from = " from " + db.dbclass.tableName + " e ";
// Start 0000185, KuangWei, 2015-04-21
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
empfilter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, empfilter, info);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
// End 0000185, KuangWei, 2015-04-21
view = new DataView(table);
ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
}
}
}
}
}
return(view);
}
public DataView emploadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = new DBFilter();// empSBinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "* ";
string from = "from [" + EEmpPersonalInfo.db.dbclass.tableName + "] e ";
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
DateTime dtPeriodFr, dtPeriodTo;
if (DateTime.TryParse(PeriodFr.Value, out dtPeriodFr) && DateTime.TryParse(PeriodTo.Value, out dtPeriodTo))
{
empInfoFilter.add(new Match("EmpDateOfJoin", "<=", dtPeriodTo));
DBFilter empTermFilter = new DBFilter();
empTermFilter.add(new MatchField("ee.EmpID", "et.EmpID"));
empTermFilter.add(new Match("et.EmpTermLastDate", "<", dtPeriodFr));
empInfoFilter.add(new Exists(EEmpTermination.db.dbclass.tableName + " et ", empTermFilter, true));
DBFilter attendanceRecordFilter = new DBFilter();
attendanceRecordFilter.add(new MatchField("e.EmpID", "ar.EmpID"));
attendanceRecordFilter.add(new Match("ar.AttendanceRecordDate", "<=", dtPeriodTo));
attendanceRecordFilter.add(new Match("ar.AttendanceRecordDate", ">=", dtPeriodFr));
//attendanceRecordFilter.add(new Match("ar.AttendanceRecordActualOvertimeMins", ">", 0));
empInfoFilter.add(new Exists(EAttendanceRecord.db.dbclass.tableName + " ar", attendanceRecordFilter));
DBFilter empPosFilter = new DBFilter();
empPosFilter.add(new MatchField("e.EmpID", "ep.EmpID"));
empPosFilter.add(new Match("ep.EmpPosEffFr", "<=", dtPeriodTo));
OR orEmpPosEffToTerm = new OR();
orEmpPosEffToTerm.add(new Match("ep.EmpPosEffTo", ">=", dtPeriodFr));
orEmpPosEffToTerm.add(new NullTerm("ep.EmpPosEffTo"));
empPosFilter.add(orEmpPosEffToTerm);
DBFilter attendancePlanFilter = new DBFilter();
attendancePlanFilter.add(new MatchField("ep.AttendancePlanID", "ap.AttendancePlanID"));
attendancePlanFilter.add(new Match("ap.AttendancePlanOTGainAsCompensationLeaveEntitle", true));
empPosFilter.add(new Exists(EAttendancePlan.db.dbclass.tableName + " ap", attendancePlanFilter));
empInfoFilter.add(new Exists(EEmpPositionInfo.db.dbclass.tableName + " ep", empPosFilter));
}
else
{
btnGenerate.Visible = false;
empView = null;
repeater.DataSource = null;
repeater.DataBind();
return(null);
}
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
if (table.Rows.Count != 0)
{
btnGenerate.Visible = true;
}
else
{
btnGenerate.Visible = false;
}
empView = new DataView(table);
ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = empView;
repeater.DataBind();
}
return(empView);
}
public DataView emploadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = new DBFilter();// empSBinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "* ";
string from = "from [" + EEmpPersonalInfo.db.dbclass.tableName + "] e ";
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
DateTime dtPeriodFr;
DateTime dtPeriodTo;
//try
//{
// dtPeriodFrom = DateTime.Parse(PeriodFr.Value);
//}
//catch
//{
// dtPeriodFrom = AppUtils.ServerDateTime();
//}
//try
//{
// dtPeriodTo = DateTime.Parse(PeriodTo.Value);
//}
//catch
//{
// dtPeriodTo = AppUtils.ServerDateTime();
//}
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime(), AppUtils.ServerDateTime());
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
if (DateTime.TryParse(PeriodFr.Value, out dtPeriodFr) && DateTime.TryParse(PeriodTo.Value, out dtPeriodTo))
{
empInfoFilter.add(new Match("EmpDateOfJoin", "<=", dtPeriodTo));
DBFilter empTermFilter = new DBFilter();
empTermFilter.add(new MatchField("ee.EmpID", "et.EmpID"));
empTermFilter.add(new Match("et.EmpTermLastDate", "<", dtPeriodFr));
empInfoFilter.add(new Exists(EEmpTermination.db.dbclass.tableName + " et ", empTermFilter, true));
}
else
{
btnGenerate.Visible = false;
btnGenerate1.Visible = false;
empView = null;
repeater.DataSource = null;
repeater.DataBind();
return(null);
}
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
DataTable table = filter.loadData(dbConn, null, select, from);
table = EmployeeSearchControl1.FilterEncryptedEmpInfoField(table, info);
if (table.Rows.Count != 0)
{
btnGenerate.Visible = true;
btnGenerate1.Visible = true;
}
else
{
btnGenerate.Visible = false;
btnGenerate1.Visible = false;
}
empView = new DataView(table);
//if (info != null)
//{
// info.loadPageList(null, empPrevPage, empNextPage, empFirstPage, empLastPage);
// CurPage.Value = info.page.ToString();
// NumPage.Value = info.numPage.ToString();
//}
if (repeater != null)
{
repeater.DataSource = empView;
repeater.DataBind();
}
return(empView);
}