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); }