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));
DBFilter empWorkingSummaryFilter = new DBFilter();
DateTime dtPeriodFr, dtPeriodTo;
if (DateTime.TryParse(PeriodFr.Value, out dtPeriodFr) && DateTime.TryParse(PeriodTo.Value, out dtPeriodTo))
{
empWorkingSummaryFilter.add(new Match("ews.EmpWorkingSummaryAsOfDate", "<=", dtPeriodTo));
empWorkingSummaryFilter.add(new Match("ews.EmpWorkingSummaryAsOfDate", ">=", dtPeriodFr));
}
else
{
btnGenerate.Visible = false;
btnGenerate1.Visible = false;
empView = null;
repeater.DataSource = null;
repeater.DataBind();
return(null);
}
filter.add(new IN("EmpID", "Select distinct EmpID from " + EEmpWorkingSummary.db.dbclass.tableName + " ews", empWorkingSummaryFilter));
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));
//}
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);
}
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);
}
//protected void PayPeriodID_SelectedIndexChanged(object sender, EventArgs e)
//{
// Response.Redirect(Request.Url.LocalPath + "?PayGroupID=" + PayGroupID.SelectedValue + "&PayPeriodID=" + PayPeriodID.SelectedValue);
//}
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 [" + db.dbclass.tableName + "] e ";
DBFilter empPayrollFilter = new DBFilter();
if (PayrollStatus.SelectedValue.Equals("T"))
{
empPayrollFilter.add(new Match("ep.EmpPayStatus", "=", "T"));
}
else
{
empPayrollFilter.add(new Match("ep.EmpPayStatus", "<>", "T"));
}
//DBFilter payPeriodFilter = new DBFilter();
//if (DecryptedRequest["PayGroupID"] != null)
// payPeriodFilter.add(new Match("PayGroupID", DecryptedRequest["PayGroupID"]));
//else
// payPeriodFilter.add(new Match("PayGroupID", 0));
empPayrollFilter.add(new IN("ep.PayPeriodID", " Select PayPeriodID from PayrollPeriod pp", Payroll_ConfirmedPeriod_List1.getPayrollPeriodFilter()));
filter.add(new IN("e.EmpID", "Select ep.EmpID from EmpPayroll 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 && Payroll_ConfirmedPeriod_List1.RecordCount > 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 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 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));
//}
//DateTime dtPeriodFr= new DateTime();
//DateTime dtPeriodTo=new DateTime();
//int intProcessYear = 0, intReferenceYear = 0, intProcessMonth = 0, intReferenceMonth = 0;
//if (int.TryParse(((TextBox)EmployeeSearchControl1.AdditionElementControl.FindControl("ProcessYear")).Text, out intProcessYear) && int.TryParse(((DropDownList)EmployeeSearchControl1.AdditionElementControl.FindControl("ProcessMonth")).SelectedValue, out intProcessMonth))
//{
// dtPeriodTo = new DateTime(intProcessYear, intProcessMonth, DateTime.DaysInMonth(intProcessYear, intProcessMonth));
//}
//if (int.TryParse(((TextBox)EmployeeSearchControl1.AdditionElementControl.FindControl("ReferenceYear")).Text, out intReferenceYear) && int.TryParse(((DropDownList)EmployeeSearchControl1.AdditionElementControl.FindControl("ReferenceMonth")).SelectedValue, out intReferenceMonth))
//{
// dtPeriodFr = new DateTime(intReferenceYear, intReferenceMonth, DateTime.DaysInMonth(intReferenceYear, intReferenceMonth));
//}
//if (dtPeriodFr>dtPeriodTo)
// if (dtPeriodTo.Ticks != 0)
// {
// DateTime dtTemp = dtPeriodTo;
// dtPeriodTo = dtPeriodFr;
// dtPeriodFr = dtTemp;
// }
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 = "* ";
string from = "from [" + EEmpPersonalInfo.db.dbclass.tableName + "] e ";
IN inTerm = new IN("e.EmpID", "Select ep.EmpID from [EmpPositionInfo] ep, [PayrollPeriod] pp ", filter);
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 Match("pp.PayPeriodID", CurPayPeriodID));
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
OR orPayPeriodFilter = new OR();
orPayPeriodFilter.add(new MatchField("ep.EmpPosEffTo", ">=", "pp.PayPeriodFr"));
orPayPeriodFilter.add(new NullTerm("ep.EmpPosEffTo"));
filter.add(orPayPeriodFilter);
filter.add(new IN("Not e.empid", "Select et.empid from " + EEmpTermination.db.dbclass.tableName + " et where et.EmpTermLastDate<pp.PayPeriodFr", new DBFilter()));
DBFilter resultFilter = new DBFilter();
resultFilter.add(inTerm);
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime().Date, AppUtils.ServerDateTime().Date);
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);
if (table.Rows.Count != 0)
{
btnGenerate.Visible = true;
btnGenerate2.Visible = true;
}
else
{
btnGenerate.Visible = false;
btnGenerate2.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 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 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 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));
}
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 loadData(ListInfo info, DBManager db, Repeater repeater, bool IsGenerated)
{
if (!string.IsNullOrEmpty(TaxCompID.SelectedValue))
{
//if (YearSelect.SelectedIndex == 0) YearSelect.SelectedIndex = YearSelect.Items.Count - 1;
DateTime lastTaxDate = new DateTime(int.Parse(YearSelect.SelectedValue), 3, 31);
DateTime firstTaxDate = new DateTime(int.Parse(YearSelect.SelectedValue) - 1, 4, 1);
DBFilter filter = new DBFilter();//sbinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "e.*, et.EmpTermLastDate ";
string from = "from [" + db.dbclass.tableName + "] e LEFT JOIN [" + EEmpTermination.db.dbclass.tableName + "] et ON et.EmpID=e.EmpID ";
if (IsGenerated)
{
select += ", te.TaxEmpID, te.TaxEmpGeneratedDate, te.TaxEmpGeneratedByUserID ";
from += " INNER JOIN [" + ETaxEmp.db.dbclass.tableName + "] te ON e.EmpID=te.EmpID AND te.TaxFormID=" + CurID.ToString() + " ";
}
else
{
DBFilter taxEmpFilter = new DBFilter();
//Start 0000205, Miranda, 2015-5-30
//taxEmpFilter.add(new Match("TaxFormID", CurID));
int[] arr = new int[2];
arr[0] = CurID;
if (TaxFormType.SelectedValue.Equals("F", StringComparison.CurrentCultureIgnoreCase))
{
arr[1] = getFormIDByType("G");
}
else if (TaxFormType.SelectedValue.Equals("G", StringComparison.CurrentCultureIgnoreCase))
{
arr[1] = getFormIDByType("F");
}
taxEmpFilter.add(new IN("TaxFormID", arr));
//taxEmpFilter.add(new Match("TaxFormID", CurID));
//End 0000205, Miranda, 2015-5-30
taxEmpFilter.add(new MatchField("te.EmpID", "e.EmpID"));
filter.add(new Exists(ETaxEmp.db.dbclass.tableName + " te", taxEmpFilter, true));
}
if (TaxFormType.SelectedValue.Equals("B", StringComparison.CurrentCultureIgnoreCase))
{
OR joinDateInTerms = new OR();
joinDateInTerms.add(new Match("e.EmpDateOfJoin", "<=", lastTaxDate));
joinDateInTerms.add(new NullTerm("e.EmpDateOfJoin"));
filter.add(joinDateInTerms);
OR termDateTerms = new OR();
termDateTerms.add(new IN("not e.empid", "Select et.empid from EmpTermination et ", new DBFilter()));
DBFilter termDateFilter = new DBFilter();
termDateFilter.add(new Match("et.EmpTermLastDate", ">", lastTaxDate));
termDateTerms.add(new IN("e.empid", "Select et.empid from EmpTermination et ", termDateFilter));
filter.add(termDateTerms);
DBFilter positionFilter = new DBFilter();
positionFilter.add(new Match("ep.EmpPosEffFr", "<=", lastTaxDate));
OR positonEffToTerms = new OR();
positonEffToTerms.add(new Match("ep.EmpPosEffTo", ">=", lastTaxDate));
positonEffToTerms.add(new NullTerm("ep.EmpPosEffTo"));
positionFilter.add(positonEffToTerms);
DBFilter taxCompFilter = new DBFilter();
taxCompFilter.add(new Match("tcm.TaxCompID", TaxCompID.SelectedIndex == 0 ? "0" : TaxCompID.SelectedValue));
positionFilter.add(new IN("ep.CompanyID", "Select tcm.CompanyID from TaxCompanyMap tcm", taxCompFilter));
filter.add(new IN("e.EmpID", "Select ep.EmpID from EmpPositionInfo ep", positionFilter));
}
else if (TaxFormType.SelectedValue.Equals("F", StringComparison.CurrentCultureIgnoreCase) || TaxFormType.SelectedValue.Equals("G", StringComparison.CurrentCultureIgnoreCase))
{
OR joinDateInTerms = new OR();
joinDateInTerms.add(new Match("e.EmpDateOfJoin", "<=", lastTaxDate));
joinDateInTerms.add(new NullTerm("e.EmpDateOfJoin"));
filter.add(joinDateInTerms);
DBFilter termDateFilter = new DBFilter();
termDateFilter.add(new Match("et.EmpTermLastDate", "<=", lastTaxDate));
termDateFilter.add(new Match("et.EmpTermLastDate", ">=", firstTaxDate));
filter.add(new IN("e.empid", "Select et.empid from EmpTermination et ", termDateFilter));
DBFilter positionFilter = new DBFilter();
positionFilter.add(new Match("ep.EmpPosEffFr", "<=", lastTaxDate));
OR positonEffToTerms = new OR();
positonEffToTerms.add(new Match("ep.EmpPosEffTo", ">", lastTaxDate));
positonEffToTerms.add(new NullTerm("ep.EmpPosEffTo"));
positionFilter.add(positonEffToTerms);
DBFilter taxCompFilter = new DBFilter();
taxCompFilter.add(new Match("tcm.TaxCompID", TaxCompID.SelectedIndex == 0 ? "0" : TaxCompID.SelectedValue));
positionFilter.add(new IN("ep.CompanyID", "Select tcm.CompanyID from TaxCompanyMap tcm", taxCompFilter));
filter.add(new IN("e.EmpID", "Select ep.EmpID from EmpPositionInfo ep", positionFilter));
}
else if (TaxFormType.SelectedValue.Equals("E", StringComparison.CurrentCultureIgnoreCase))
{
filter.add(new Match("e.EmpDateOfJoin", ">=", firstTaxDate));
filter.add(new Match("e.EmpDateOfJoin", "<=", lastTaxDate));
DBFilter positionFilter = new DBFilter();
positionFilter.add(new Match("ep.EmpPosEffFr", "<=", lastTaxDate));
OR positonEffToTerms = new OR();
positonEffToTerms.add(new Match("ep.EmpPosEffTo", ">", firstTaxDate));
positonEffToTerms.add(new NullTerm("ep.EmpPosEffTo"));
positionFilter.add(positonEffToTerms);
DBFilter taxCompFilter = new DBFilter();
taxCompFilter.add(new Match("tcm.TaxCompID", TaxCompID.SelectedIndex == 0 ? "0" : TaxCompID.SelectedValue));
positionFilter.add(new IN("ep.CompanyID", "Select tcm.CompanyID from TaxCompanyMap tcm", taxCompFilter));
filter.add(new IN("e.EmpID", "Select ep.EmpID from EmpPositionInfo ep", positionFilter));
}
else if (TaxFormType.SelectedValue.Equals("M", StringComparison.CurrentCultureIgnoreCase))
{
filter.add(new Match("et.EmpTermLastDate", ">=", firstTaxDate));
filter.add(new Match("et.EmpTermLastDate", "<=", lastTaxDate));
}
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);
// Remove condition for record count = 0
// Since there are 2 employee list, the system cannot detect whether all list are empty.
//if (table.Rows.Count != 0)
btnGenerate.Visible = true & IsAllowEdit;
//else
// btnGenerate.Visible = false;
DataView 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);
}
else
{
repeater.DataSource = null;
repeater.DataBind();
return(null);
}
}
public DataView emploadData(ListInfo info, DBManager db, Repeater repeater)
{
DateTime dtPeriodFr = new DateTime(), dtPeriodTo = new DateTime();
if (DateTime.TryParse(PayPeriodFr.Value, out dtPeriodFr) && DateTime.TryParse(PayPeriodTo.Value, out dtPeriodTo))
{
DBFilter filter = 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 empPayrollFilter = new DBFilter();
//empPayrollFilter.add(new MatchField("e.EmpID", "ep.EmpID"));
//empPayrollFilter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
empPayrollFilter.add(new Match("ep.EmpPayStatus", "C"));
empPayrollFilter.add(new MatchField("ep.PayPeriodID", "pp.PayPeriodID"));
empPayrollFilter.add(new Match("pp.PayPeriodFr", ">=", dtPeriodFr));
empPayrollFilter.add(new Match("pp.PayPeriodTo", "<=", dtPeriodTo));
DBFilter payRecordFilter = new DBFilter();
//payRecordFilter.add(new MatchField("ep.EmpPayrollID", "pr.EmpPayrollID"));
// IN existsPayRec = new IN("ep.EmpPayrollID", "Select distinct pr.EmpPayrollID from (select EmpPayrollID,PayRecMethod from " + EPaymentRecord.db.dbclass.tableName + " group by EmpPayrollID, PayRecMethod) pr", payRecordFilter);
IN existsPayRec = new IN("ep.EmpPayrollID", "Select distinct pr.EmpPayrollID from (select distinct EmpPayrollID,PayRecMethod from " + EPaymentRecord.db.dbclass.tableName + " ) pr", payRecordFilter);
empPayrollFilter.add(existsPayRec);
IN exists = new IN("e.EmpID", "Select distinct ep.EmpID from " + EEmpPayroll.db.dbclass.tableName + " ep, " + EPayrollPeriod.db.dbclass.tableName + " pp", empPayrollFilter);
filter.add(exists);
// 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)
{
panelPayPeriod.Visible = true;
ReportExportControl1.Visible = true;
ReportExportControl2.Visible = true;
}
else
{
panelPayPeriod.Visible = false;
ReportExportControl1.Visible = false;
ReportExportControl2.Visible = false;
}
empView = new DataView(table);
EmpListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = empView;
repeater.DataBind();
}
return(empView);
}
else
{
panelPayPeriod.Visible = false;
ReportExportControl1.Visible = false;
ReportExportControl2.Visible = false;
return(null);
}
}
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"));
int m_Year, m_Month;
if (int.TryParse(Year.Text, out m_Year) && int.TryParse(Month.SelectedValue, out m_Month))
{
DateTime dtPeriodFr = new DateTime(m_Year, m_Month, 1);
DateTime dtPeriodTo = new DateTime(m_Year, m_Month, DateTime.DaysInMonth(m_Year, m_Month));
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;
empView = null;
repeater.DataSource = null;
repeater.DataBind();
return(null);
}
filter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
if (m_Year < 1800)
{
return(null);
}
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);
//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 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));
DBFilter orsoRecordFilter = new DBFilter();
orsoRecordFilter.add(new Match("ORSOPlanID", ORSOPlanID.SelectedValue));
OR m_orPlan = new OR();
m_orPlan.add(new IN("EmpPayrollID", "SELECT EmpPayrollID FROM MPFRecord", mpfRecordFilter));
m_orPlan.add(new IN("EmpPayrollID", "SELECT EmpPayrollID FROM ORSORecord", orsoRecordFilter));
empPayrollFilter.add(m_orPlan);
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)
{
btnGenerate1.Visible = true;
btnGenerate2.Visible = true;
}
else
{
btnGenerate1.Visible = false;
btnGenerate2.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 emploadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = new DBFilter();// empSBinding.createFilter();
string select = "* ";
// Start 0000193, Kuang Wei, 2015-05-14
//string from = "from [" + EEmpPersonalInfo.db.dbclass.tableName + "] e ";
string from = "from [" + EEmpPersonalInfo.db.dbclass.tableName + "] e, EmpPayroll ep";//EEmpPayroll.db.dbclass.tableName + " ep"
// End 0000193, Kuang Wei, 2015-05-14
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(new MatchField("e.EmpID", "ep.EmpID"));
empPayrollFilter.add(new Match("ep.EmpPayStatus", "C"));
// Start 0000193, Kuang Wei, 2015-05-14
DBFilter empInfoFilter = EmployeeSearchControl1.GetEmpInfoFilter(AppUtils.ServerDateTime().Date, AppUtils.ServerDateTime().Date);
empInfoFilter.add(new MatchField("e.EmpID", "ee.EmpID"));
empPayrollFilter.add(new Exists(EEmpPersonalInfo.db.dbclass.tableName + " ee", empInfoFilter));
// End 0000193, Kuang Wei, 2015-05-14
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));
}
else
{
empPayrollFilter.add(new Match("1", "2"));
}
DBFilter orsoRecordFilter = new DBFilter();
orsoRecordFilter.add(new Match("ORSOPlanID", ORSOPlanID.SelectedValue));
empPayrollFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from ORSORecord", orsoRecordFilter));
// Start 0000193, Kuang Wei, 2015-05-14
//Exists exists = new Exists(EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter);
//filter.add(exists);
//DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, info);
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, empPayrollFilter, info);
// End 0000193, Kuang Wei, 2015-05-14
if (table.Rows.Count != 0)
{
ReportExportControl2.Visible = true;
ReportExportControl1.Visible = true;
}
else
{
ReportExportControl2.Visible = false;
ReportExportControl1.Visible = false;
}
empView = new DataView(table);
if (repeater != null)
{
repeater.DataSource = empView;
repeater.DataBind();
}
return(empView);
}