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)
{
// Start 0000183, KuangWei, 2015-04-10
if (isSupervisor(CurID))
{
TeamRecordExport.Visible = true;
}
// End 0000183, KuangWei, 2015-04-10
DBFilter filter = sbinding.createFilter();
OR orTimeCardRecordHolder = new OR();
orTimeCardRecordHolder.add(new Match("EmpID", CurID));
//if (!string.IsNullOrEmpty(timeCardNo))
orTimeCardRecordHolder.add(new Match("TimeCardRecordCardNo", GetTimeCardNo(CurID)));
filter.add(orTimeCardRecordHolder);
DateTime dtTimeCardRecordDate;
if (!DateTime.TryParse(TimeCardRecordDate.Value, out dtTimeCardRecordDate))
{
dtTimeCardRecordDate = AppUtils.ServerDateTime().Date;
}
filter.add(new Match("TimeCardRecordDateTime", ">=", dtTimeCardRecordDate));
filter.add(new Match("TimeCardRecordDateTime", "<", dtTimeCardRecordDate.AddDays(1)));
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = "*";
string from = "from " + ETimeCardRecord.db.dbclass.tableName + "";
DataTable table = filter.loadData(dbConn, info, select, from);
view = new DataView(table);
ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, DataList repeater)
{
DBFilter filter = binding.createFilter();
DateTime dtPeriodFr, dtPeriodTo;
if (DateTime.TryParse(RequestFromDate.Value, out dtPeriodFr))
{
filter.add(new Match("RequestOTClaimCancelCreateDateTime", ">=", dtPeriodFr));
}
if (DateTime.TryParse(RequestToDate.Value, out dtPeriodTo))
{
filter.add(new Match("RequestOTClaimCancelCreateDateTime", "<", dtPeriodTo.AddDays(1)));
}
filter.add(new Match("c.EmpID", this.CurID));
if (!EmpRequestStatus.SelectedValue.Equals(string.Empty))
{
if (EmpRequestStatus.SelectedValue.Equals("REQUEESTSTATUS_PROCESSING"))
{
filter.add(EEmpRequest.EndStatusDBTerms("EmpRequestStatus", true));
}
else if (EmpRequestStatus.SelectedValue.Equals("REQUEESTSTATUS_END_PROCESS"))
{
filter.add(EEmpRequest.EndStatusDBTerms("EmpRequestStatus", false));
}
}
string select = "c.RequestOTClaimCancelID, c.RequestOTClaimCancelCreateDateTime, r.EmpRequestStatus, r.EmpRequestID, r.EmpRequestLastAuthorizationWorkFlowIndex, la.* ";
string from = "from " + db.dbclass.tableName + " c " +
" LEFT JOIN " + EOTClaim.db.dbclass.tableName + " la ON c.OTClaimID = la.OTClaimID " +
" LEFT JOIN " + EEmpRequest.db.dbclass.tableName + " r On r.EmpRequestRecordID = c.RequestOTClaimCancelID and r.EmpRequestType = '" + EEmpRequest.TYPE_EEOTCLAIM + "'";
DataTable table = filter.loadData(dbConn, info, select, from);
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)
{
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 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)
{
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();
m_rpFilter.add(new NullTerm("EmpRPEffTo"));
m_rpFilter.add(new Match("EmpRPFPS", ">", 0));
m_rpFilter.add(new Match("EmpRPFPS", "<", 100));
m_rpFilter.add(new Match("EmpRPBasicSalary", ">", 0));
m_rpFilter.add(AppUtils.GetPayemntCodeDBTermByPaymentType(dbConn, "tmpRP.PayCodeID", "BASICSAL"));
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 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 = sbinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
string select = " cr.* ";
string from = "from CessationReason cr ";
DBFilter notMPFSchemeCessationReasonFilter = new DBFilter();
notMPFSchemeCessationReasonFilter.add(new Match("MPFSchemeID", "=", MPFSchemeID.Value));
DBFilter notMPFSchemeCessationReasonMapFilter = new DBFilter();
notMPFSchemeCessationReasonMapFilter.add(new Match("MPFSchemeCessationReasonID", "<>", CurID));
notMPFSchemeCessationReasonMapFilter.add(new IN("MPFSchemeCessationReasonID", "Select MPFSchemeCessationReasonID from MPFSchemeCessationReason", notMPFSchemeCessationReasonFilter));
IN notinTerms = new IN("NOT CessationReasonID", "select CessationReasonID from MPFSchemeCessationReasonMapping", notMPFSchemeCessationReasonMapFilter);
DBFilter mpfSchemeCessationReasontMapFilter = new DBFilter();
mpfSchemeCessationReasontMapFilter.add(new Match("MPFSchemeCessationReasonID", CurID));
IN inTerms = new IN("CessationReasonID", "select CessationReasonID from MPFSchemeCessationReasonMapping", mpfSchemeCessationReasontMapFilter);
OR orTerms = new OR();
orTerms.add(notinTerms);
orTerms.add(inTerms);
filter.add(orTerms);
filter.add("CessationReasonCode", true);
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, info);
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadData(ListInfo info, DBManager db, DataList repeater)
{
DBFilter filter = sbinding.createFilter();
if (info != null && info.orderby != null && !info.orderby.Equals(""))
{
filter.add(info.orderby, info.order);
}
else
{
info.orderby = "TaxEmpPlacePeriodFr";
info.order = true;
}
string select = "c.*";
string from = "from " + db.dbclass.tableName + " c ";
//filter.add(new Match("EmpPayrollID",EmpPayrollID.Value));
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, info);
foreach (DataRow row in table.Rows)
{
foreach (DBField field in db.fields)
{
if (table.Columns.Contains(field.name))
{
if (row[field.name] != null)
{
if (field.transcoder != null)
{
row[field.name] = field.transcoder.fromDB(row[field.name]);
}
}
}
}
}
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
//protected bool loadObject()
//{
// obj = new EPayrollGroup();
// bool isNew = WebFormWorkers.loadKeys(EPayrollGroup.db, obj, DecryptedRequest);
// if (!EPayrollGroup.db.select(dbConn, obj))
// return false;
// Hashtable values = new Hashtable();
// EPayrollGroup.db.populate(obj, values);
// binding.toControl(values);
// if (CurPayPeriodID <= 0)
// {
// CurPayPeriodID = obj.CurrentPayPeriodID;
// }
// try
// {
// PayPeriodID.SelectedValue = CurPayPeriodID.ToString();
// ucPayroll_PeriodInfo.CurrentPayPeriodID = CurPayPeriodID;
// }
// catch (Exception ex)
// {
// CurPayPeriodID = 0;
// }
// if (PayPeriodID.SelectedIndex == 0)
// {
// CurPayPeriodID = 0;
// ucPayroll_PeriodInfo.CurrentPayPeriodID = 0;
// }
// if (CurPayPeriodID > 0)
// panelCostAllocationAdjustmentDetail.Visible = true;
// else
// panelCostAllocationAdjustmentDetail.Visible = false;
// return true;
//}
//protected void CostAllocationStatus_SelectedIndexChanged(object sender, EventArgs e)
//{
// Response.Redirect(Request.Url.LocalPath + "?PayGroupID=" + PayGroupID.SelectedValue + "&CostAllocationStatus=" + CostAllocationStatus.SelectedValue);
//}
//protected void PayGroupID_SelectedIndexChanged(object sender, EventArgs e)
//{
// Response.Redirect(Request.Url.LocalPath + "?PayGroupID=" + PayGroupID.SelectedValue + "&CostAllocationStatus=" + CostAllocationStatus.SelectedValue);
//}
//protected void PayPeriodID_SelectedIndexChanged(object sender, EventArgs e)
//{
// Response.Redirect(Request.Url.LocalPath + "?PayGroupID=" + PayGroupID.SelectedValue + "&PayPeriodID=" + PayPeriodID.SelectedValue + "&CostAllocationStatus=" + CostAllocationStatus.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.*, ca.CostAllocationID ";
string from = "from " + EEmpPersonalInfo.db.dbclass.tableName + " e, " + ECostAllocation.db.dbclass.tableName + " ca, " + EEmpPayroll.db.dbclass.tableName + " ep, " + EPayrollPeriod.db.dbclass.tableName + " pp";
filter.add(new MatchField("e.EmpID", "ca.EmpID"));
filter.add(new MatchField("ca.EmpPayrollID", "ep.EmpPayrollID"));
filter.add(new MatchField("ep.PayPeriodID", "pp.PayPeriodID"));
filter.add(new Match("ca.CostAllocationStatus", CostAllocationStatus.SelectedValue));
//DBFilter empPayrollFilter = new DBFilter();
//empPayrollFilter.add(new Match("ep.EmpPayStatus", "C"));
//empPayrollFilter.add(new Match("ep.PayPeriodID", CurPayPeriodID));
// Start 0000129, Ricky So, 2014/11/11
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
// End 0000129, Ricky So, 2014/11/11
//DBFilter inCostAllocationFilter = new DBFilter();
//filter.add(new IN("ca.EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter));
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, 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 = view;
repeater.DataBind();
}
return(view);
}
protected DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = sbinding.createFilter();
//Start 0000210, Miranda, 2015-06-14
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
//End 0000210, Miranda, 2015-06-14
DateTime dtPeriodFr, dtPeriodTo;
if (DateTime.TryParse(RequestLateWaivePeriodFrom.Value, out dtPeriodFr))
{
filter.add(new Match("AttendanceRecordDate", ">=", dtPeriodFr));
}
if (DateTime.TryParse(RequestLateWaivePeriodTo.Value, out dtPeriodTo))
{
filter.add(new Match("AttendanceRecordDate", "<", dtPeriodTo.AddDays(1)));
}
filter.add(new Match("AttendanceRecordActualLateMins", ">", 0));
OR m_or = new OR();
m_or.add(new Match("AttendanceRecordWaivedLateMins", 0));
m_or.add(new NullTerm("AttendanceRecordWaivedLateMins"));
filter.add(m_or);
// Start 0000112, Miranda, 2015-01-17
filter.add(new Match("EmpID", CurEmpID));
// End 0000112, Miranda, 2015-01-17
string select = "A.*, r.RosterCodeDesc";
string from = "from " + sdb.dbclass.tableName + " A LEFT JOIN " +
ERosterCode.db.dbclass.tableName + " r ON A.RosterCodeID=r.RosterCodeID";
DataTable table = filter.loadData(dbConn, info, select, from);
//DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, 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();
filter.add(new Match("c.EmpID", this.CurID));
filter.add(new IN("e.EmpRequestType", lateWaiveReqType));
//filter.add(new Match("Year(RequestLateWaiveCreateDate)", "=", Year.Text));
//Start 0000210, Miranda, 2015-06-14
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
//End 0000210, Miranda, 2015-06-14
string select = "c.*, e.EmpRequestID, a.AttendanceRecordDate, a.AttendanceRecordWorkStart, a.AttendanceRecordWorkEnd, a.AttendanceRecordActualLateMins, r.RosterCodeDesc ";
string from = "from [" + sdb.dbclass.tableName + "] c" +
" right join " + EEmpRequest.db.dbclass.tableName + " e on e.EmpRequestRecordID = c.RequestLateWaiveID and e.EmpID = c.EmpID" +
" left join " + EAttendanceRecord.db.dbclass.tableName + " a on a.AttendanceRecordID = c.AttendanceRecordID" +
" left join " + ERosterCode.db.dbclass.tableName + " r on r.RosterCodeID = a.RosterCodeID";
DataTable table = filter.loadData(dbConn, info, select, from);
view = new DataView(table);
ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
if (CurEntitleID != -1)
{
EEmpRequest detail = new EEmpRequest();
detail.EmpRequestID = this.CurEntitleID;
if (EEmpRequest.db.select(dbConn, detail))
{
// Start 0000112, Miranda, 2015-01-11
lblActionDate.Text = detail.EmpRequestCreateDate.ToString("yyyy-MM-dd HH:mm:ss");
// End 0000112, Miranda, 2015-01-11
lblActionBy.Text = detail.EmpRequestLastActionBy;
lblStatus.Text = detail.EmpRequestStatus;
lblReason.Text = detail.EmpRequestRejectReason;
}
}
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 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 = " 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, DataList repeater)
{
DBFilter filter = sbinding.createFilter();
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
//{
// info.orderby = "is_processed, " + info.orderby;
//}
//else
//{
// info.orderby = "is_processed, CNDEffDate";
// info.order = false;
//}
//string select = "c.*, (CASE WHEN payrecid>0 THEN 1 Else 0 END) is_processed ";
//string from = "from " + db.dbclass.tableName + " c ";
if (info != null && info.orderby != null && !info.orderby.Equals(""))
{
}
else
{
info.orderby = "CNDEffDate";
info.order = false;
}
string select = "c.* ";
string from = "from " + db.dbclass.tableName + " c ";
filter.add(new NullTerm("PayRecID"));
filter.add(WebUtils.AddRankFilter(Session, "c.EmpID", true));
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, info);
DataView 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();
string select = "c.*, CONVERT(nvarchar(7), c.AttendancePreparationProcessMonth, 21) AttendancePreparationProcessMonth2 ";
string from = "from [" + db.dbclass.tableName + "] c ";
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, 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();
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 loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = sbinding.createFilter();
string select = " c.* ";
string from = "from " + db.dbclass.tableName + " c ";
filter.add(new Match("AttendancePreparationProcessStatus", EAttendancePreparationProcess.STATUS_NORMAL));
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, info);
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = table;
repeater.DataBind();
}
return(view);
}
// Start 0000069, Ricky So, 2014-08-08
public DataView LoadUserList(Repeater repeater)
{
DBFilter m_filter = sbinding.createFilter();;
m_filter.add(new Match("pgu.PayGroupID", CurID));
m_filter.add(new MatchField("pgu.UserID", "u.UserID"));
m_filter.add("u.UserName", true);
string m_select = "u.userName ";
string m_from = "FROM PayrollGroupUsers pgu,Users u ";
DataTable m_table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, m_select, m_from, m_filter, null);
DataView m_view = new DataView(m_table);
if (repeater != null)
{
repeater.DataSource = m_view;
repeater.DataBind();
}
return(m_view);
}
public DataView loadData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = trainingEnrollBinding.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 ";//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));
DBFilter empTrainingEnrollFilter = new DBFilter();
empTrainingEnrollFilter.add(new MatchField("e.empid", "ete.empid"));
empTrainingEnrollFilter.add(new Match("ete.TrainingSeminarID", CurID));
filter.add(new Exists(EEmpTrainingEnroll.db.dbclass.tableName + " ete", empTrainingEnrollFilter, 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 = 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 = "c.*";
string from = "from " + db.dbclass.tableName + " c ";
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, info);
ListFooter.Refresh();
view = new DataView(table);
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
public DataView loadReminderData(ListInfo info, DBManager db, Repeater repeater)
{
DBFilter filter = reminderBinding.createFilter();
if (info != null && info.orderby != null && !info.orderby.Equals(""))
{
filter.add(info.orderby, info.order);
}
string select = " * ";
string from = "from " + EReminderType.db.dbclass.tableName;
if (productLicense.ProductType != HROne.ProductLicense.ProductLicenseType.HROne)
{
filter.add(new Match("ReminderTypeCode", "<>", EInbox.INBOX_TYPE_WORKPERMITEXPIRY));
}
filter.add("ReminderTypeCode", true);
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, info);
reminderView = 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();
}
//lblEmpRecordCount.Text = view.Count.ToString();
return(reminderView);
}
public DataView loadData(ListInfo info, DBManager AttendancePlanDetaildb, DataList repeater)
{
DBFilter filter = sbinding.createFilter();
if (info != null && info.orderby != null && !info.orderby.Equals(""))
{
filter.add(info.orderby, info.order);
}
string select = "c.*";
string from = "from " + AttendancePlanDetaildb.dbclass.tableName + " c ";
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, 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.*, ep.*";
string from = "from [" + db.dbclass.tableName + "] ep, EmpPersonalInfo e ";
filter.add(new MatchField("e.EmpID", "ep.EmpID"));
filter.add(Payroll_PeriodSelectionList1.GetEmpPayrollDBTerm("ep"));
filter.add(WebUtils.AddRankFilter(Session, "e.EmpID", true));
DataTable table = WebUtils.GetDataTableFromSelectQueryWithFilter(dbConn, select, from, filter, info);
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)
{
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 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)
{
DBFilter filter = binding.createFilter();
DateTime dtPeriodFr, dtPeriodTo;
if (DateTime.TryParse(EmpRequestFromDate.Value, out dtPeriodFr))
{
filter.add(new Match("EmpRequestCreateDate", ">=", dtPeriodFr));
}
if (DateTime.TryParse(EmpRequestToDate.Value, out dtPeriodTo))
{
filter.add(new Match("EmpRequestCreateDate", "<", dtPeriodTo.AddDays(1)));
}
//if (info != null && info.orderby != null && !info.orderby.Equals(""))
// filter.add(info.orderby, info.order);
filter.add(new Match("R.EmpRequestStatus", "<>", EEmpRequest.STATUS_CANCELLED));
filter.add(new Match("R.EmpRequestStatus", "<>", EEmpRequest.STATUS_REJECTED));
filter.add(new Match("R.EmpRequestStatus", "<>", EEmpRequest.STATUS_APPROVED));
DBFilter workFlowDetailFilter = new DBFilter();
OR orWorkFlowIndexTerms = new OR();
if (EmpRequestStatus2.SelectedValue == "Y")
{
orWorkFlowIndexTerms.add(new MatchField("awfd.AuthorizationWorkFlowIndex", "=", "R.EmpRequestLastAuthorizationWorkFlowIndex + 1"));
}
else if (EmpRequestStatus2.SelectedValue == "O")
{
orWorkFlowIndexTerms.add(new MatchField("awfd.AuthorizationWorkFlowIndex", ">", "R.EmpRequestLastAuthorizationWorkFlowIndex + 1"));
}
else
{
orWorkFlowIndexTerms.add(new MatchField("awfd.AuthorizationWorkFlowIndex", ">", "R.EmpRequestLastAuthorizationWorkFlowIndex"));
}
AND andMaxWorkFlowIndexTerms = new AND();
andMaxWorkFlowIndexTerms.add(new MatchField("awfd.AuthorizationWorkFlowIndex", "=", "(SELECT MAX(maxAWFD.AuthorizationWorkFlowIndex) FROM " + EAuthorizationWorkFlowDetail.db.dbclass.tableName + " maxAWFD WHERE maxAWFD.AuthorizationWorkFlowID=awfd.AuthorizationWorkFlowID)"));
andMaxWorkFlowIndexTerms.add(new MatchField("R.EmpRequestLastAuthorizationWorkFlowIndex", ">=", "awfd.AuthorizationWorkFlowIndex"));
orWorkFlowIndexTerms.add(andMaxWorkFlowIndexTerms);
workFlowDetailFilter.add(orWorkFlowIndexTerms);
DBFilter authorizerFilter = new DBFilter();
string strAuthGroupInList = string.Empty;
foreach (EAuthorizationGroup authGroup in authGroupList)
{
if (string.IsNullOrEmpty(strAuthGroupInList))
{
strAuthGroupInList = authGroup.AuthorizationGroupID.ToString();
}
else
{
strAuthGroupInList += "," + authGroup.AuthorizationGroupID.ToString();
}
}
workFlowDetailFilter.add(new IN("awfd.AuthorizationGroupID", strAuthGroupInList, null));
AND andLeaveApplicationTerms = new AND();
andLeaveApplicationTerms.add(new IN("R.EmpRequestType", new string[] { EEmpRequest.TYPE_EELEAVEAPP, EEmpRequest.TYPE_EELEAVECANCEL }));
andLeaveApplicationTerms.add(new IN("EP.AuthorizationWorkFlowIDLeaveApp", "SELECT awfd.AuthorizationWorkFlowID FROM " + EAuthorizationWorkFlowDetail.db.dbclass.tableName + " awfd", workFlowDetailFilter));
//andLeaveApplicationTerms.add(new Match("AuthorizationGroupIsApproveLeave", "<>", false));
AND andEEProfileTerms = new AND();
andEEProfileTerms.add(new Match("R.EmpRequestType", EEmpRequest.TYPE_EEPROFILE));
andEEProfileTerms.add(new IN("EP.AuthorizationWorkFlowIDEmpInfoModified", "SELECT awfd.AuthorizationWorkFlowID FROM " + EAuthorizationWorkFlowDetail.db.dbclass.tableName + " awfd", workFlowDetailFilter));
// Start 0000060, Miranda, 2014-07-13
AND andOTClaimTerms = new AND();
andOTClaimTerms.add(new IN("R.EmpRequestType", new string[] { EEmpRequest.TYPE_EEOTCLAIM, EEmpRequest.TYPE_EEOTCLAIMCANCEL }));
andOTClaimTerms.add(new IN("EP.AuthorizationWorkFlowIDOTClaims", "SELECT awfd.AuthorizationWorkFlowID FROM " + EAuthorizationWorkFlowDetail.db.dbclass.tableName + " awfd", workFlowDetailFilter));
// End 0000060, Miranda, 2014-07-13
// Start 0000112, Miranda, 2014-12-10
AND andLateWaiveTerms = new AND();
andLateWaiveTerms.add(new IN("R.EmpRequestType", new string[] { EEmpRequest.TYPE_EELATEWAIVE, EEmpRequest.TYPE_EELATEWAIVECANCEL }));
andLateWaiveTerms.add(new IN("EP.AuthorizationWorkFlowIDLateWaive", "SELECT awfd.AuthorizationWorkFlowID FROM " + EAuthorizationWorkFlowDetail.db.dbclass.tableName + " awfd", workFlowDetailFilter));
// End 0000112, Miranda, 2014-12-10
OR orAuthorizationGrpApplicationTerms = new OR();
orAuthorizationGrpApplicationTerms.add(andLeaveApplicationTerms);
orAuthorizationGrpApplicationTerms.add(andEEProfileTerms);
// Start 0000060, Miranda, 2014-07-13
orAuthorizationGrpApplicationTerms.add(andOTClaimTerms);
// End 0000060, Miranda, 2014-07-13
// Start 0000112, Miranda, 2014-12-10
orAuthorizationGrpApplicationTerms.add(andLateWaiveTerms);
// End 0000112, Miranda, 2014-12-10
filter.add(orAuthorizationGrpApplicationTerms);
//string select = "R.*, E.EmpNo, E.EmpEngSurname, E.EmpEngOtherName, E.EmpAlias ";
//string from = "from (select * from [" + db.dbclass.tableName + "] where EmpRequestStatus = '"
// + EEmpRequest.STATUS_USRSUBMIT.ToString() + "' or EmpRequestStatus = '"
// + EEmpRequest.STATUS_FSTAPP.ToString() + "' ) R LEFT JOIN " + EEmpPersonalInfo.db.dbclass.tableName + " E on R.EmpID = E.EmpID AND R.EmpID in (Select P.EmpID From "
// + EEmpPositionInfo.db.dbclass.tableName + " P where P.EmpPosEffFr < '" + DateTime.Today.ToString("yyyy-MM-dd") + "' and ( P.EmpPosEffFr > '" + DateTime.Today.ToString("yyyy-MM-dd") + "' or P.EmpPosEffTo is Null ) and ( P.EmpFirstAuthorizationGp in ( Select A.AuthorizationGroupID From "
// + EAuthorizer.db.dbclass.tableName + " A where A.EmpID = " + CurID + " and R.EmpRequestStatus = '"
// + EEmpRequest.STATUS_USRSUBMIT.ToString() + "')) or ( P.EmpSecondAuthorizationGp in ( Select A.AuthorizationGroupID From "
// + EAuthorizer.db.dbclass.tableName + " A where A.EmpID = " + CurID + " and (R.EmpRequestStatus = '"
// + EEmpRequest.STATUS_FSTAPP.ToString() + "' or R.EmpRequestStatus = '"
// + EEmpRequest.STATUS_USRSUBMIT.ToString() + "'))))";
// Start 0000105, KuangWei, 2014-10-21
// Start 0000232, Miranda, 2015-07-02
string select = "R.*, E.EmpNo, E.EmpEngSurname, E.EmpEngOtherName, E.EmpAlias, ISNULL ( L.LeaveCode , L2.LeaveCode ) as LeaveCode, ISNULL ( L.LeaveCodeDesc , L2.LeaveCodeDesc) as LeaveCodeDesc";
// End 0000232, Miranda, 2015-07-02
string from = "from " + db.dbclass.tableName + " R "
+ " LEFT JOIN " + EEmpPersonalInfo.db.dbclass.tableName + " E on R.EmpID = E.EmpID "
+ " Left Join " + EEmpPositionInfo.db.dbclass.tableName + " EP on EP.EmpID = E.EmpID "
+ " and EP.EmpPosEffFr <= '" + DateTime.Today.ToString("yyyy-MM-dd") + "' AND (EP.EmpPosEffTO >= '" + DateTime.Today.ToString("yyyy-MM-dd") + "' or EP.EmpPosEffTo is Null )"
// Start 0000112, Miranda, 2015-01-11
// Start 0000232, Miranda, 2015-07-02
+ " LEFT JOIN " + ERequestLeaveApplication.db.dbclass.tableName + " C on R.EmpRequestRecordID = C.RequestLeaveAppID and R.EmpRequestType = '" + EEmpRequest.TYPE_EELEAVEAPP + "'"
+ " LEFT JOIN " + ERequestLeaveApplicationCancel.db.dbclass.tableName + " CC on R.EmpRequestRecordID = CC.RequestLeaveAppCancelID and R.EmpRequestType = '" + EEmpRequest.TYPE_EELEAVECANCEL + "'"
+ " LEFT JOIN " + ELeaveApplication.db.dbclass.tableName + " LA on LA.LeaveAppID = CC.LeaveAppID"
// End 0000112, Miranda, 2015-01-11
+ " LEFT JOIN " + ELeaveCode.db.dbclass.tableName + " L on C .RequestLeaveCodeID = L.LeaveCodeID "
+ " LEFT JOIN " + ELeaveCode.db.dbclass.tableName + " L2 on LA.LeaveCodeID = L2.LeaveCodeID";
// End 0000232, Miranda, 2015-07-02
// End 0000105, KuangWei, 2014-10-21
//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);
DataTable table = filter.loadData(dbConn, info, select, from);
view = new DataView(table);
ListFooter.Refresh();
if (repeater != null)
{
repeater.DataSource = view;
repeater.DataBind();
}
return(view);
}
else
{
return(null);
}
}
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)
{
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 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 DBFilter GetEmpInfoFilter(DateTime FromDate, DateTime ToDate)
{
FromDate = FromDate.Date;
ToDate = ToDate.Date;
DBFilter filter = binding.createFilter();
DateTime dtPeriodFr, dtPeriodTo;
if (DateTime.TryParse(JoinDateFrom.Value, out dtPeriodFr))
{
filter.add(new Match("ee.EmpDateOfJoin", ">=", dtPeriodFr));
}
if (DateTime.TryParse(JoinDateTo.Value, out dtPeriodTo))
{
filter.add(new Match("ee.EmpDateOfJoin", "<=", dtPeriodTo));
}
//if (EmpStatus.SelectedValue.Equals("A") || EmpStatus.SelectedValue.Equals("T") || EmpStatus.SelectedValue.Equals("TERMINATED"))
// filter.add(new Match("EmpDateOfJoin", "<=", ToDate.Date));
filter.add(WebUtils.AddRankFilter(Session, "ee.EmpID", true));
DateTime dtLastDateFr, dtLastDateTo;
DBFilter empTerminationFilter = new DBFilter();
if (DateTime.TryParse(LastDateFrom.Value, out dtLastDateFr))
{
empTerminationFilter.add(new Match("searchet.EmpTermLastDate", ">=", dtLastDateFr));
}
if (DateTime.TryParse(LastDateTo.Value, out dtLastDateTo))
{
empTerminationFilter.add(new Match("searchet.EmpTermLastDate", "<=", dtLastDateTo));
}
if (EmpStatus.SelectedValue.Equals("TERMINATED"))
{
empTerminationFilter.add(new Match("searchet.EmpTermLastDate", "<", FromDate.Date));
}
if (empTerminationFilter.terms().Count > 0 || EmpStatus.SelectedValue.Equals("T"))
{
empTerminationFilter.add(new MatchField("ee.EmpID", "searchet.EmpID"));
filter.add(new Exists(EEmpTermination.db.dbclass.tableName + " searchet ", empTerminationFilter));
}
if (EmpStatus.SelectedValue.Equals("AT"))
{
DBFilter notExistsEmpTerminationFilter = new DBFilter();
notExistsEmpTerminationFilter.add(new Match("searchnoet.EmpTermLastDate", "<", FromDate.Date));
notExistsEmpTerminationFilter.add(new MatchField("ee.EmpID", "searchnoet.EmpID"));
filter.add(new Exists(EEmpTermination.db.dbclass.tableName + " searchnoet ", notExistsEmpTerminationFilter, true));
}
if (EmpStatus.SelectedValue.Equals("A"))
{
DBFilter notExistsEmpTerminationFilter = new DBFilter();
notExistsEmpTerminationFilter.add(new MatchField("ee.EmpID", "searchnoet.EmpID"));
filter.add(new Exists(EEmpTermination.db.dbclass.tableName + " searchnoet ", notExistsEmpTerminationFilter, true));
}
DBFilter empPosFilter = new DBFilter();
//{
// OR orPositionTerm = null;
// foreach (ListItem item in PositionList.Items)
// if (item.Selected)
// {
// if (orPositionTerm == null)
// orPositionTerm = new OR();
// orPositionTerm.add(new Match("searchepi.PositionID", item.Value));
// }
// if (orPositionTerm != null)
// empPosFilter.add(orPositionTerm);
//}
DBTerm positionDBTerm = CreateDBTermsFromListControl("searchepi.PositionID", EPosition.db.dbclass.tableName + " pos", "pos.PositionID", PositionList.ListControl);
if (positionDBTerm != null)
{
empPosFilter.add(positionDBTerm);
}
DBTerm rankDBTerms = CreateDBTermsFromListControl("searchepi.RankID", ERank.db.dbclass.tableName + " rank", "rank.RankID", RankList.ListControl);
if (rankDBTerms != null)
{
empPosFilter.add(rankDBTerms);
}
DBTerm employmentTypeDBTerms = CreateDBTermsFromListControl("searchepi.EmploymentTypeID", EEmploymentType.db.dbclass.tableName + " empType", "empType.EmploymentTypeID", EmploymentTypeList.ListControl);
if (employmentTypeDBTerms != null)
{
empPosFilter.add(employmentTypeDBTerms);
}
DBTerm staffTypeDBTerms = CreateDBTermsFromListControl("searchepi.StaffTypeID", EStaffType.db.dbclass.tableName + " staffType", "staffType.StaffTypeID", StaffTypeList.ListControl);
if (staffTypeDBTerms != null)
{
empPosFilter.add(staffTypeDBTerms);
}
DBTerm payrollGroupDBTerms = CreateDBTermsFromListControl("searchepi.PayGroupID", EPayrollGroup.db.dbclass.tableName + " payGroup", "payGroup.PayGroupID", PayrollGroupList.ListControl);
if (payrollGroupDBTerms != null)
{
empPosFilter.add(payrollGroupDBTerms);
}
DBTerm companyAndHierarchyTerm = HierarchyCheckBoxList1.GetFilters("searchepi", "EmpPosID");
if (companyAndHierarchyTerm != null)
{
empPosFilter.add(companyAndHierarchyTerm);
}
if (empPosFilter.terms().Count > 0)
{
empPosFilter.add(new MatchField("ee.EmpID", "searchepi.EmpID"));
EEmpPositionInfo.DBFilterAddDateRange(empPosFilter, "searchepi", FromDate, ToDate);
OR orEmpPosTerm = new OR();
orEmpPosTerm.add(new Exists(EEmpPositionInfo.db.dbclass.tableName + " searchepi ", empPosFilter));
filter.add(orEmpPosTerm);
}
//DBFilter empPosNotExistsFilter = new DBFilter();
//empPosNotExistsFilter.add(new MatchField("ee.EmpID", "np.EmpID"));
//orEmpPosTerm.add(new Exists(EEmpPositionInfo.db.dbclass.tableName + " np", empPosNotExistsFilter, true));
return(filter);
}
