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