protected bool loadObject()
{
EEmpPersonalInfo obj = new EEmpPersonalInfo();
bool isNew = WebFormWorkers.loadKeys(empDB, obj, DecryptedRequest);
DBFilter filter = new DBFilter();
filter.add(new Match("EmpID", obj.EmpID));
filter.add(WebUtils.AddRankFilter(Session, "EmpID", true));
ArrayList empInfoList = EEmpPersonalInfo.db.select(dbConn, filter);
if (empInfoList.Count == 0)
{
return(false);
}
obj = (EEmpPersonalInfo)empInfoList[0];
Hashtable values = new Hashtable();
empDB.populate(obj, values);
empBinding.toControl(values);
OldEmpNo.Text = EmpNo.Text;
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, CurEmpID);
values = new Hashtable();
empPosDB.populate(empPos, values);
empPosBinding.toControl(values);
return(true);
}
private GenericBankFileDetail CreateBankFileDetail(int EmpID)
{
GenericBankFileDetail bankFileDetail = new GenericBankFileDetail();
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = EmpID;
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
bankFileDetail.EmpID = empInfo.EmpID;
bankFileDetail.EmpNo = empInfo.EmpNo;
bankFileDetail.EmpName = empInfo.EmpEngFullName;
}
DBFilter empPosFilter = new DBFilter();
empPosFilter.add(new Match("EmpID", EmpID));
empPosFilter.add("EmpPosEffFr", false);
ArrayList empPosList = EEmpPositionInfo.db.select(dbConn, empPosFilter);
if (empPosList.Count > 0)
{
EEmpPositionInfo empPos = (EEmpPositionInfo)empPosList[0];
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
ECompany.db.select(dbConn, company);
bankFileDetail.CompanyID = company.CompanyID;
bankFileDetail.CompanyName = company.CompanyName;
}
return(bankFileDetail);
}
protected void Delete_Click(object sender, EventArgs e)
{
ArrayList list = new ArrayList();
foreach (RepeaterItem i in Repeater.Items)
{
CheckBox cb = (CheckBox)i.FindControl("ItemSelect");
if (cb.Checked)
{
EEmpPositionInfo o = new EEmpPositionInfo();
WebFormUtils.GetKeys(sdb, o, cb);
list.Add(o);
}
}
if (list.Count > 0)
{
WebUtils.StartFunction(Session, FUNCTION_CODE, CurID);
foreach (EEmpPositionInfo o in list)
{
if (sdb.select(dbConn, o))
{
sdb.delete(dbConn, o);
DBFilter filter = new DBFilter();
filter.add(new Match("EmpPosID", o.EmpPosID));
ArrayList existingHierarchyList = EEmpHierarchy.db.select(dbConn, filter);
foreach (EEmpHierarchy h in existingHierarchyList)
{
EEmpHierarchy.db.delete(dbConn, h);
}
}
}
WebUtils.EndFunction(dbConn);
}
loadData(info, sdb, Repeater);
}
protected void Repeater_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
DataRowView row = (DataRowView)e.Item.DataItem;
CheckBox cb = (CheckBox)e.Item.FindControl("ItemSelect");
WebFormUtils.LoadKeys(sdb, row, cb);
e.Item.FindControl("ItemSelect").Visible = toolBar.DeleteButton_Visible;
EEmpPositionInfo obj = new EEmpPositionInfo();
EEmpPositionInfo.db.toObject(((DataRowView)e.Item.DataItem).Row, obj);
Hashtable values = new Hashtable();
EEmpPositionInfo.db.populate(obj, values);
Binding eBinding;
eBinding = new Binding(dbConn, EEmpPositionInfo.db);
eBinding.add(new BlankZeroLabelVLBinder(EEmpPositionInfo.db, (Label)e.Item.FindControl("CompanyID"), ECompany.VLCompany));
eBinding.add(new BlankZeroLabelVLBinder(EEmpPositionInfo.db, (Label)e.Item.FindControl("PositionID"), EPosition.VLPosition));
eBinding.add(new BlankZeroLabelVLBinder(EEmpPositionInfo.db, (Label)e.Item.FindControl("RankID"), ERank.VLRank));
eBinding.add(new BlankZeroLabelVLBinder(EEmpPositionInfo.db, (Label)e.Item.FindControl("StaffTypeID"), EStaffType.VLStaffType));
eBinding.add(new BlankZeroLabelVLBinder(EEmpPositionInfo.db, (Label)e.Item.FindControl("LeavePlanID"), ELeavePlan.VLLeavePlan));
eBinding.add(new BlankZeroLabelVLBinder(EEmpPositionInfo.db, (Label)e.Item.FindControl("PayGroupID"), EPayrollGroup.VLPayrollGroup));
eBinding.init(Request, Session);
eBinding.toControl(values);
}
protected void LeaveAppDays_TextChanged(object sender, EventArgs e)
{
DateTime tmpLeaveAppDateFrom;
double tmpLeaveAppDay = 0;
double workhour = 0;
if (!DateTime.TryParse(RequestLeaveAppDateFrom.Value, out tmpLeaveAppDateFrom))
{
tmpLeaveAppDateFrom = AppUtils.ServerDateTime().Date;
}
if (double.TryParse(RequestLeaveDays.Text, out tmpLeaveAppDay) && tmpLeaveAppDay > 0)
{
EEmpPositionInfo currentEmpPos = AppUtils.GetLastPositionInfo(dbConn, tmpLeaveAppDateFrom, CurEmpID);
if (currentEmpPos != null)
{
EWorkHourPattern workPattern = new EWorkHourPattern();
workPattern.WorkHourPatternID = currentEmpPos.WorkHourPatternID;
if (EWorkHourPattern.db.select(dbConn, workPattern))
{
workhour = workPattern.GetDefaultWorkHour(dbConn, tmpLeaveAppDateFrom);
}
}
if (workhour > 0)
{
RequestLeaveAppHours.Text = ((double)(workhour * tmpLeaveAppDay)).ToString("0.####");
}
}
}
protected virtual int MaximumBroughtForwardForSLCat2(DateTime AsOfDate)
{
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, AsOfDate, EmpID);
if (empPos != null)
{
if (leaveTypeDisabledSLCat2 == false) // if CAT2 is enabled
{
ELeavePlan leavePlan = new ELeavePlan();
leavePlan.LeavePlanID = empPos.LeavePlanID;
if (ELeavePlan.db.select(dbConn, leavePlan))
{
DBFilter dbFilter = new DBFilter();
dbFilter.add(new Match("LeavePlanID", leavePlan.LeavePlanID));
dbFilter.add(new Match("LeaveTypeID", leaveTypeIDSLCat2));
ArrayList list = ELeavePlanBroughtForward.db.select(dbConn, dbFilter);
if (list.Count > 0)
{
ELeavePlanBroughtForward leavePlanBroughtForward = (ELeavePlanBroughtForward)list[0];
return(leavePlanBroughtForward.LeavePlanBroughtForwardMax);
}
}
}
}
return(0);
//return 9999;
}
protected bool loadObject()
{
obj = new EEmpPositionInfo();
bool isNew = WebFormWorkers.loadKeys(db, obj, DecryptedRequest);
DBFilter filter = new DBFilter();
filter.add(new Match("EmpID", CurEmpID));
filter.add(WebUtils.AddRankFilter(Session, "EmpID", true));
if (EEmpPersonalInfo.db.count(dbConn, filter) == 0)
{
if (CurEmpID <= 0)
{
return(false);
}
else
{
HROne.Common.WebUtility.RedirectURLwithEncryptedQueryString(Response, Session, "~/AccessDeny.aspx");
}
}
if (!db.select(dbConn, obj))
{
if (CurID <= 0)
{
return(false);
}
else
{
HROne.Common.WebUtility.RedirectURLwithEncryptedQueryString(Response, Session, "~/AccessDeny.aspx");
}
}
if (obj.EmpID != CurEmpID)
{
if (CurID <= 0)
{
return(false);
}
else
{
HROne.Common.WebUtility.RedirectURLwithEncryptedQueryString(Response, Session, "~/AccessDeny.aspx");
}
}
//if (obj.EmpPosIsRosterTableGroupSupervisor)
// EmpPosIsRosterTableGroupSupervisor.Visible = true;
//else
// EmpPosIsRosterTableGroupSupervisor.Visible = false;
Hashtable values = new Hashtable();
db.populate(obj, values);
binding.toControl(values);
return(true);
}
// End 0000185, KuangWei, 2015-05-05
private DataSet.HeadCountSummarySet.HeadCountSummaryRow CreateHeadCountRow(int EmpPosID)
{
DataSet.HeadCountSummarySet.HeadCountSummaryRow row = dataSet.HeadCountSummary.NewHeadCountSummaryRow();
EEmpPositionInfo empPos = new EEmpPositionInfo();
empPos.EmpPosID = EmpPosID;
if (EEmpPositionInfo.db.select(dbConn, empPos))
{
row.EmpID = empPos.EmpID;
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = empPos.EmpID;
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
row.EmpGender = empInfo.EmpGender;
row.EmpAreaCode = empInfo.EmpResAddrAreaCode;
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row.CompanyID = company.CompanyID;
row.CompanyCode = company.CompanyCode;
row.CompanyName = company.CompanyName;
}
EPayrollGroup payGroup = new EPayrollGroup();
payGroup.PayGroupID = empPos.PayGroupID;
if (EPayrollGroup.db.select(dbConn, payGroup))
{
row.PayGroupID = payGroup.PayGroupID;
row.PayGroupCode = payGroup.PayGroupCode;
row.PayGroupDesc = payGroup.PayGroupDesc;
}
EHierarchyElement hElement = getHierarchyElement(EmpPosID);
if (hElement != null)
{
row.HElementID = hElement.HElementID;
row.HElementCode = hElement.HElementCode;
row.HElementDesc = hElement.HElementDesc;
}
else
{
row.HElementID = 0;
row.HElementCode = " ";
row.HElementDesc = " ";
}
}
}
row.PreviousCount = 0;
row.CurrentCount = 0;
row.NewJoinCount = 0;
row.TerminateCount = 0;
row.TransferInCount = 0;
row.TransferOutCount = 0;
return(row);
}
protected DataTable loadExtraData(DataTable sourceTable)
{
DataTable destTable = sourceTable.Copy();
destTable.Columns.Add("CompanyCode", typeof(string));
destTable.Columns.Add("BusinessHierarchy", typeof(string));
destTable.Columns.Add("PositionCode", typeof(string));
destTable.Columns.Add("RankCode", typeof(string));
destTable.Columns.Add("StaffTypeCode", typeof(string));
destTable.Columns.Add("LeavePlanCode", typeof(string));
destTable.Columns.Add("PayGroupCode", typeof(string));
foreach (DataRow row in destTable.Rows)
{
EEmpPositionInfo empPos = EEmpPositionInfo.GetObject(dbConn, row["EmpPosID"]);
if (empPos != null)
{
ECompany company = ECompany.GetObject(dbConn, row["CompanyID"]);
if (company != null)
{
row["CompanyCode"] = company.CompanyCode;
}
row["BusinessHierarchy"] = empPos.GetBusinessHierarchyString(dbConn);
EPosition position = EPosition.GetObject(dbConn, row["PositionID"]);
if (position != null)
{
row["PositionCode"] = position.PositionCode;
}
ERank rank = ERank.GetObject(dbConn, row["RankID"]);
if (rank != null)
{
row["RankCode"] = rank.RankCode;
}
EStaffType staffType = EStaffType.GetObject(dbConn, row["StaffTypeID"]);
if (staffType != null)
{
row["StaffTypeCode"] = staffType.StaffTypeCode;
}
ELeavePlan leavePlan = ELeavePlan.GetObject(dbConn, row["LeavePlanID"]);
if (leavePlan != null)
{
row["LeavePlanCode"] = leavePlan.LeavePlanCode;
}
EPayrollGroup payGroup = EPayrollGroup.GetObject(dbConn, (int)row["PayGroupID"]);
if (payGroup != null)
{
row["PayGroupCode"] = payGroup.PayGroupCode;
}
}
}
return(destTable);
}
public void LoadLastEmpPositionInfo(int EmpID)
{
EEmpPositionInfo obj = AppUtils.GetLastPositionInfo(dbConn, EmpID);
if (obj != null)
{
int lastEmpPosID = obj.EmpPosID;
obj.EmpPosID = -1;
Hashtable values = new Hashtable();
db.populate(obj, values);
binding.toControl(values);
loadHierarchy(lastEmpPosID);
}
}
protected void LeaveAppTime_TextChanged(object sender, EventArgs e)
{
if (RequestLeaveAppUnit.SelectedValue.Equals("H"))
{
DateTime dtTimeFrom = new DateTime();
if (!DateTime.TryParseExact(RequestLeaveAppTimeFrom.Text, "HH:mm", null, System.Globalization.DateTimeStyles.None, out dtTimeFrom))
{
RequestLeaveAppTimeFrom.Text = string.Empty;
}
DateTime dtTimeTo = new DateTime();
if (!DateTime.TryParseExact(RequestLeaveAppTimeTo.Text, "HH:mm", null, System.Globalization.DateTimeStyles.None, out dtTimeTo))
{
RequestLeaveAppTimeTo.Text = string.Empty;
}
if (dtTimeFrom.Ticks.Equals(0) || dtTimeTo.Ticks.Equals(0))
{
return;
}
DateTime tmpLeaveAppDateFrom;
if (DateTime.TryParse(RequestLeaveAppDateFrom.Value, out tmpLeaveAppDateFrom))
{
double workhour = 0;
EEmpPositionInfo currentEmpPos = AppUtils.GetLastPositionInfo(dbConn, tmpLeaveAppDateFrom, CurEmpID);
if (currentEmpPos != null)
{
EWorkHourPattern workPattern = new EWorkHourPattern();
workPattern.WorkHourPatternID = currentEmpPos.WorkHourPatternID;
if (EWorkHourPattern.db.select(dbConn, workPattern))
{
workhour = workPattern.GetDefaultWorkHour(dbConn, tmpLeaveAppDateFrom);
}
}
if (workhour > 0)
{
double timeDiff = ((TimeSpan)dtTimeTo.Subtract(dtTimeFrom)).TotalHours;
if (timeDiff < 0)
{
timeDiff += 1;
}
RequestLeaveDays.Text = ((double)(timeDiff / workhour)).ToString("0.####");
RequestLeaveAppHours.Text = timeDiff.ToString("0.####");
}
}
}
}
private static DataRow CreateWorkingSummaryRow(DatabaseConnection dbConn, DataTable dataTable, Hashtable hierarchyLevelHashTable, EEmpPersonalInfo empInfo, DateTime AsOfDate, double RestDayEntitled, double RestDayTaken, double TotalWorkingDays, double TotalWorkingHours)
{
DataRow row = dataTable.NewRow();
row[FIELD_EMP_NO] = empInfo.EmpNo;
row["EnglishName"] = empInfo.EmpEngFullName;
row["ChineseName"] = empInfo.EmpChiFullName;
DBFilter empPosFilter = new DBFilter();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, AsOfDate, empInfo.EmpID);
if (empPos != null)
{
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row["Company"] = company.CompanyCode;
}
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
if (hierarchyLevel != null)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementCode;
}
}
}
}
row[FIELD_AS_OF_DATE] = AsOfDate;
row[FIELD_REST_DAY_ENTITLED] = RestDayEntitled;
row[FIELD_REST_DAY_TAKEN] = RestDayTaken;
row[FIELD_TOTAL_WORKING_DAYS] = TotalWorkingDays;
row[FIELD_TOTAL_WORKING_HOURS] = TotalWorkingHours;
dataTable.Rows.Add(row);
return(row);
}
protected void Delete_Click(object sender, EventArgs e)
{
EEmpPositionInfo c = new EEmpPositionInfo();
c.EmpPosID = CurID;
if (EEmpPositionInfo.db.select(dbConn, c))
{
WebUtils.StartFunction(Session, FUNCTION_CODE, c.EmpID);
db.delete(dbConn, c);
DBFilter filter = new DBFilter();
filter.add(new Match("EmpPosID", c.EmpPosID));
ArrayList existingHierarchyList = EEmpHierarchy.db.select(dbConn, filter);
foreach (EEmpHierarchy h in existingHierarchyList)
{
EEmpHierarchy.db.delete(dbConn, h);
}
WebUtils.EndFunction(dbConn);
}
HROne.Common.WebUtility.RedirectURLwithEncryptedQueryString(Response, Session, "EmpTab_Position_View.aspx?EmpID=" + EmpID.Value);
}
public bool Delete()
{
EEmpPositionInfo c = new EEmpPositionInfo();
c.EmpPosID = EmpPosID;
if (EEmpPositionInfo.db.select(dbConn, c))
{
WebUtils.StartFunction(Session, FUNCTION_CODE, c.EmpID);
db.delete(dbConn, c);
DBFilter filter = new DBFilter();
filter.add(new Match("EmpPosID", c.EmpPosID));
ArrayList existingHierarchyList = EEmpHierarchy.db.select(dbConn, filter);
foreach (EEmpHierarchy h in existingHierarchyList)
{
EEmpHierarchy.db.delete(dbConn, h);
}
WebUtils.EndFunction(dbConn);
}
return(true);
}
// End 0000185, KuangWei, 2015-05-05
private DataSet.HeadCountSummarySet.HeadCountSummaryRow CreateHeadCountRow(int CompanyID, int EmpPosID)
{
DataSet.HeadCountSummarySet.HeadCountSummaryRow row = dataSet.HeadCountSummary.NewHeadCountSummaryRow();
ECompany company = new ECompany();
company.CompanyID = CompanyID;
ECompany.db.select(dbConn, company);
row.CompanyID = company.CompanyID;
row.CompanyCode = company.CompanyCode;
row.CompanyName = company.CompanyName;
EHierarchyElement hElement = getFirstLevelHierarchyElement(EmpPosID);
if (hElement != null)
{
row.HElementID = hElement.HElementID;
row.HElementCode = hElement.HElementCode;
row.HElementDesc = hElement.HElementDesc;
}
else
{
row.HElementID = 0;
row.HElementCode = " ";
row.HElementDesc = " ";
}
EEmpPositionInfo empPos = new EEmpPositionInfo();
empPos.EmpPosID = EmpPosID;
EEmpPositionInfo.db.select(dbConn, empPos);
row.EmpID = empPos.EmpID;
row.PreviousCount = 0;
row.CurrentCount = 0;
row.NewJoinCount = 0;
row.TerminateCount = 0;
row.TransferInCount = 0;
row.TransferOutCount = 0;
return(row);
}
protected double ALRounding(int EmpID, DateTime AsOfDate, ELeaveBalance balanceItem)
{
double originalProrata = balanceItem.LeaveBalanceEntitled;
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, balanceItem.LeaveBalanceEffectiveEndDate, EmpID);
if (empPos != null)
{
ELeavePlan leavePlan = new ELeavePlan();
leavePlan.LeavePlanID = empPos.LeavePlanID;
if (ELeavePlan.db.select(dbConn, leavePlan))
{
if (!leavePlan.LeavePlanALRoundingRuleIsApplyFirstYearBroughtForwardOnly || leavePlan.LeavePlanALRoundingRuleIsApplyFirstYearBroughtForwardOnly && AsOfDate > balanceItem.LeaveBalanceEffectiveEndDate && balanceItem.LeaveBalanceEffectiveDate <= DefaultServiceStartDate)
{
EALProrataRoundingRule roundingRule = new EALProrataRoundingRule();
roundingRule.ALProrataRoundingRuleID = leavePlan.ALProrataRoundingRuleID;
if (EALProrataRoundingRule.db.select(dbConn, roundingRule))
{
return(roundingRule.Rounding(dbConn, originalProrata));
}
}
}
}
return(originalProrata);
}
public static DataTable Export(DatabaseConnection dbConn, ArrayList EmpInfoList, DateTime PeriodFrom, DateTime PeriodTo)
{
DataTable tmpDataTable = new DataTable("WorkingSummary$");
tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string));
tmpDataTable.Columns.Add("EnglishName", typeof(string));
tmpDataTable.Columns.Add("ChineseName", typeof(string));
tmpDataTable.Columns.Add("Company", typeof(string));
DBFilter hierarchyLevelFilter = new DBFilter();
Hashtable hierarchyLevelHashTable = new Hashtable();
hierarchyLevelFilter.add("HLevelSeqNo", true);
ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
tmpDataTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
}
tmpDataTable.Columns.Add(FIELD_AS_OF_DATE, typeof(DateTime));
tmpDataTable.Columns.Add(FIELD_REST_DAY_ENTITLED, typeof(double));
tmpDataTable.Columns.Add(FIELD_REST_DAY_TAKEN, typeof(double));
tmpDataTable.Columns.Add(FIELD_TOTAL_WORKING_DAYS, typeof(double));
tmpDataTable.Columns.Add(FIELD_TOTAL_WORKING_HOURS, typeof(double));
tmpDataTable.Columns.Add(FIELD_TOTAL_LUNCH_HOURS, typeof(double));
foreach (EEmpPersonalInfo empInfo in EmpInfoList)
{
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
DBFilter empWorkingSummaryFilter = new DBFilter();
empWorkingSummaryFilter.add(new Match("EmpID", empInfo.EmpID));
empWorkingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", ">=", PeriodFrom));
empWorkingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", PeriodTo));
empWorkingSummaryFilter.add("EmpWorkingSummaryAsOfDate", true);
ArrayList empWorkingSummaryList = EEmpWorkingSummary.db.select(dbConn, empWorkingSummaryFilter);
foreach (EEmpWorkingSummary empWorkingSummary in empWorkingSummaryList)
{
DataRow row = tmpDataTable.NewRow();
row[FIELD_EMP_NO] = empInfo.EmpNo;
row["EnglishName"] = empInfo.EmpEngFullName;
row["ChineseName"] = empInfo.EmpChiFullName;
DBFilter empPosFilter = new DBFilter();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, empWorkingSummary.EmpWorkingSummaryAsOfDate, empInfo.EmpID);
if (empPos != null)
{
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row["Company"] = company.CompanyCode;
}
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
if (hierarchyLevel != null)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementCode;
}
}
}
}
row[FIELD_AS_OF_DATE] = empWorkingSummary.EmpWorkingSummaryAsOfDate;
row[FIELD_REST_DAY_ENTITLED] = empWorkingSummary.EmpWorkingSummaryRestDayEntitled;
row[FIELD_REST_DAY_TAKEN] = empWorkingSummary.EmpWorkingSummaryRestDayTaken;
row[FIELD_TOTAL_WORKING_DAYS] = empWorkingSummary.EmpWorkingSummaryTotalWorkingDays;
row[FIELD_TOTAL_WORKING_HOURS] = empWorkingSummary.EmpWorkingSummaryTotalWorkingHours;
row[FIELD_TOTAL_LUNCH_HOURS] = empWorkingSummary.EmpWorkingSummaryTotalLunchTimeHours;
tmpDataTable.Rows.Add(row);
}
}
}
return(tmpDataTable);
}
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);
}
public override ReportDocument GenerateReport()
{
string HierarchyLevel1 = string.Empty;
string HierarchyLevel2 = string.Empty;
string HierarchyLevel3 = string.Empty;
string HierarchyLevel4 = string.Empty;
if (values.Count > 0)
{
DataSet.EmployeeDetail ds = new DataSet.EmployeeDetail();
DataSet.EmployeeDetail.HierarchyLevelDataTable HierarchyLevelTable = ds.HierarchyLevel;
ArrayList HierarchyLevelList = EHierarchyLevel.db.select(dbConn, new DBFilter());
foreach (EHierarchyLevel HierarchyLevel in HierarchyLevelList)
{
DataSet.EmployeeDetail.HierarchyLevelRow row = HierarchyLevelTable.NewHierarchyLevelRow();
row.HLevelCode = HierarchyLevel.HLevelCode;
row.HLevelDesc = HierarchyLevel.HLevelDesc;
row.HLevelID = HierarchyLevel.HLevelID;
row.HLevelSeqNo = HierarchyLevel.HLevelSeqNo;
HierarchyLevelTable.Rows.Add(row);
}
int currentDummyPosID = 0;
int currentDummyHierarchyID = 0;
foreach (int EmpID in values)
{
EmployeeDetailProcess.ImportEmployeeDetailRow(dbConn, ds.employeedetail, EmpID, AsOfDate);
DBFilter empBasicSalaryfilter = new DBFilter();
DBFilter paymentCodeFilter = new DBFilter();
paymentCodeFilter.add(new Match("PaymentTypeID", EPaymentType.SystemPaymentType.BasicSalaryPaymentType(dbConn).PaymentTypeID));
//empBasicSalaryfilter.add(new MatchField("erp.PayCodeID", "pc.PaymentCodeID"));
empBasicSalaryfilter.add(new IN("PayCodeID", "Select PaymentCodeID from PaymentCode", paymentCodeFilter));
empBasicSalaryfilter.add(new Match("EmpRPEffFr", "<=", AsOfDate));
OR orEffToFilter = new OR();
orEffToFilter.add(new Match("EmpRPEffTo", ">=", AsOfDate));
orEffToFilter.add(new NullTerm("EmpRPEffTo"));
empBasicSalaryfilter.add(orEffToFilter);
empBasicSalaryfilter.add(new Match("EmpID", EmpID));
DataSet.EmployeeDetail.EmpRecurringPaymentDataTable empRPTable = ds.EmpRecurringPayment;
ArrayList empRecurringPaymentList = EEmpRecurringPayment.db.select(dbConn, empBasicSalaryfilter);
foreach (EEmpRecurringPayment empRP in empRecurringPaymentList)
{
DataSet.EmployeeDetail.EmpRecurringPaymentRow row = empRPTable.NewEmpRecurringPaymentRow();
row.CurrencyID = empRP.CurrencyID;
row.EmpAccID = empRP.EmpAccID;
row.EmpID = empRP.EmpID;
row.EmpRPAmount = Convert.ToDecimal(empRP.EmpRPAmount);
row.EmpRPEffFr = empRP.EmpRPEffFr;
row.EmpRPEffTo = empRP.EmpRPEffTo;
row.EmpRPID = empRP.EmpRPID;
row.EmpRPMethod = empRP.EmpRPMethod;
row.EmpRPRemark = empRP.EmpRPRemark;
row.EmpRPUnit = empRP.EmpRPUnit;
EPaymentCode paymentCode = new EPaymentCode();
paymentCode.PaymentCodeID = empRP.PayCodeID;
if (EPaymentCode.db.select(dbConn, paymentCode))
{
row.PayCodeID = paymentCode.PaymentCodeID;
row.PaymentCode = paymentCode.PaymentCode;
row.PaymentCodeDesc = paymentCode.PaymentCodeDesc;
row.PaymentCodeID = paymentCode.PaymentCodeID;
// row.p
}
empRPTable.AddEmpRecurringPaymentRow(row);
}
{
DataSet.EmployeeDetail.EmpPositionInfoDataTable empPosTable = ds.EmpPositionInfo;
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, AsOfDate, EmpID);
if (empPos == null)
{
currentDummyPosID--;
DataSet.EmployeeDetail.EmpPositionInfoRow row = empPosTable.NewEmpPositionInfoRow();
row.EmpPosID = currentDummyPosID;
row.EmpID = EmpID;
empPosTable.AddEmpPositionInfoRow(row);
DataSet.EmployeeDetail.EmpHierarchyDataTable empHierarchyTable = ds.EmpHierarchy;
foreach (EHierarchyLevel hLevel in HierarchyLevelList)
{
currentDummyHierarchyID--;
DataSet.EmployeeDetail.EmpHierarchyRow empHierarchyRow = empHierarchyTable.NewEmpHierarchyRow();
empHierarchyRow.EmpHierarchyID = currentDummyHierarchyID;
empHierarchyRow.EmpID = EmpID;
empHierarchyRow.EmpPosID = currentDummyPosID;
empHierarchyRow.HLevelID = hLevel.HLevelID;
empHierarchyRow.HLevelSeqNo = hLevel.HLevelSeqNo;
empHierarchyTable.AddEmpHierarchyRow(empHierarchyRow);
}
}
else
{
DataSet.EmployeeDetail.EmpPositionInfoRow row = empPosTable.NewEmpPositionInfoRow();
row.CompanyID = empPos.CompanyID;
row.EmpID = empPos.EmpID;
row.EmpPosEffFr = empPos.EmpPosEffFr;
row.EmpPosEffTo = empPos.EmpPosEffTo;
row.EmpPosID = empPos.EmpPosID;
row.LeavePlanID = empPos.LeavePlanID;
row.PayGroupID = empPos.PayGroupID;
EPosition position = new EPosition();
position.PositionID = empPos.PositionID;
if (EPosition.db.select(dbConn, position))
{
row.PositionCode = position.PositionCode;
row.PositionDesc = position.PositionDesc;
row.PositionID = position.PositionID;
}
ERank rank = new ERank();
rank.RankID = empPos.RankID;
if (ERank.db.select(dbConn, rank))
{
row.RankCode = rank.RankCode;
row.RankDesc = rank.RankDesc;
row.RankID = rank.RankID;
}
row.StaffTypeID = empPos.StaffTypeID;
empPosTable.AddEmpPositionInfoRow(row);
DataSet.EmployeeDetail.EmpHierarchyDataTable empHierarchyTable = ds.EmpHierarchy;
foreach (EHierarchyLevel hLevel in HierarchyLevelList)
{
if (hLevel.HLevelSeqNo.Equals(1))
{
HierarchyLevel1 = hLevel.HLevelDesc;
}
else if (hLevel.HLevelSeqNo.Equals(2))
{
HierarchyLevel2 = hLevel.HLevelDesc;
}
else if (hLevel.HLevelSeqNo.Equals(3))
{
HierarchyLevel3 = hLevel.HLevelDesc;
}
else if (hLevel.HLevelSeqNo.Equals(4))
{
HierarchyLevel4 = hLevel.HLevelDesc;
}
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
empHierarchyFilter.add(new Match("HLevelID", hLevel.HLevelID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
DataSet.EmployeeDetail.EmpHierarchyRow empHierarchyRow = empHierarchyTable.NewEmpHierarchyRow();
empHierarchyRow.EmpID = EmpID;
empHierarchyRow.EmpPosID = empPos.EmpPosID;
empHierarchyRow.HLevelID = hLevel.HLevelID;
empHierarchyRow.HLevelSeqNo = hLevel.HLevelSeqNo;
if (empHierarchyList.Count > 0)
{
EEmpHierarchy empHierarchy = (EEmpHierarchy)empHierarchyList[0];
empHierarchyRow.EmpHierarchyID = empHierarchy.EmpHierarchyID;
EHierarchyElement hElement = new EHierarchyElement();
hElement.HElementID = empHierarchy.HElementID;
EHierarchyElement.db.select(dbConn, hElement);
empHierarchyRow.HElementCode = hElement.HElementCode;
empHierarchyRow.HElementDesc = hElement.HElementDesc;
empHierarchyRow.HElementID = hElement.HElementID;
}
else
{
currentDummyHierarchyID--;
empHierarchyRow.EmpHierarchyID = currentDummyHierarchyID;
}
empHierarchyTable.AddEmpHierarchyRow(empHierarchyRow);
}
}
}
}
DataSet.EmployeeDetail.PayrollGroupDataTable payrollGroupTable = ds.PayrollGroup;
ArrayList payrollGroupList = EPayrollGroup.db.select(dbConn, new DBFilter());
foreach (EPayrollGroup payrollGroup in payrollGroupList)
{
DataSet.EmployeeDetail.PayrollGroupRow row = payrollGroupTable.NewPayrollGroupRow();
row.PayGroupCode = payrollGroup.PayGroupCode;
row.PayGroupDesc = payrollGroup.PayGroupDesc;
row.PayGroupID = payrollGroup.PayGroupID;
payrollGroupTable.Rows.Add(row);
}
DataSet.EmployeeDetail.PositionDataTable PositionTable = ds.Position;
ArrayList PositionList = EPosition.db.select(dbConn, new DBFilter());
foreach (EPosition Position in PositionList)
{
DataSet.EmployeeDetail.PositionRow row = PositionTable.NewPositionRow();
row.PositionCode = Position.PositionCode;
row.PositionDesc = Position.PositionDesc;
row.PositionID = Position.PositionID;
PositionTable.Rows.Add(row);
}
DataSet.EmployeeDetail.BankListDataTable BankListTable = ds.BankList;
ArrayList BankListList = EBankList.db.select(dbConn, new DBFilter());
foreach (EBankList BankList in BankListList)
{
DataSet.EmployeeDetail.BankListRow row = BankListTable.NewBankListRow();
row.BankCode = BankList.BankCode;
row.BankName = BankList.BankName;
BankListTable.Rows.Add(row);
}
//System.Data.DataSet ds = new System.Data.DataSet();
//string select;
//string from;
//DBFilter filter;
//OR or;
//foreach (int EmpID in values)
//{
//from = "from EmpPersonalInfo e LEFT JOIN EmpTermination et ON et.EmpID=e.EmpID ";
//select = " e.*, et.EmpTermLastDate";
//filter = new DBFilter();
//filter.add(new Match("e.EmpID", EmpID));
//filter.loadData(ds, "employeedetail", null, select, from, null);
//from = "from EmpPositionInfo ";//EmpPos ON P.EmpID=EmpPos.EmpID AND EmpPos.EmpPosEffTo IS NULL LEFT JOIN Position Pos ON EmpPos.PositionID=Pos.PositionID";
//select = "* ";
//filter = new DBFilter();
//filter.add(new Match("EmpPosEffFr", "<=", AsOfDate));
//OR orEffToFilter = new OR();
//orEffToFilter.add(new Match("EmpPosEffTo", ">=", AsOfDate));
//orEffToFilter.add(new NullTerm("EmpPosEffTo"));
//filter.add(orEffToFilter);
//filter.add(new Match("EmpID", EmpID));
//filter.loadData(ds, "EmpPositionInfo", null, select, from, null);
//from = "from EmpRecurringPayment erp, PaymentCode pc";//EmpPos ON P.EmpID=EmpPos.EmpID AND EmpPos.EmpPosEffTo IS NULL LEFT JOIN Position Pos ON EmpPos.PositionID=Pos.PositionID";
//select = "* ";
//filter = new DBFilter();
//filter.add(new MatchField("erp.PayCodeID", "pc.PaymentCodeID"));
//filter.add(new Match("pc.PaymentTypeID", EPaymentType.SystemPaymentType.BasicSalaryPaymentType(dbConn).PaymentTypeID));
//filter.add(new Match("erp.EmpRPEffFr", "<=", AsOfDate));
//orEffToFilter = new OR();
//orEffToFilter.add(new Match("erp.EmpRPEffTo", ">=", AsOfDate));
//orEffToFilter.add(new NullTerm("erp.EmpRPEffTo"));
//filter.add(orEffToFilter);
//filter.add(new Match("EmpID", EmpID));
//filter.loadData(ds, "EmpRecurringPayment", null, select, from, null);
//}
//from = " from PayrollGroup ";//EmpPos ON P.EmpID=EmpPos.EmpID AND EmpPos.EmpPosEffTo IS NULL LEFT JOIN Position Pos ON EmpPos.PositionID=Pos.PositionID";
//select = "* ";
//filter = new DBFilter();
//filter.loadData(ds, "PayrollGroup", null, select, from, null);
//from = " from Position ";//EmpPos ON P.EmpID=EmpPos.EmpID AND EmpPos.EmpPosEffTo IS NULL LEFT JOIN Position Pos ON EmpPos.PositionID=Pos.PositionID";
//select = "* ";
//filter = new DBFilter();
//filter.loadData(ds, "Position", null, select, from, null);
//from = "from EmpHierarchy eh Left Join HierarchyElement he ON eh.HElementID=he.HElementID LEFT JOIN HierarchyLevel hl on hl.HLevelID=eh.HLevelID";//EmpPos ON P.EmpID=EmpPos.EmpID AND EmpPos.EmpPosEffTo IS NULL LEFT JOIN Position Pos ON EmpPos.PositionID=Pos.PositionID";
//select = " eh.*, he.HElementCode, he.HElementDesc, hl.HLevelSeqNo";
//filter = new DBFilter();
//filter.add("HLevelSeqNo", true);
//filter.loadData(ds, "EmpHierarchy", null, select, from, null);
foreach (System.Data.DataRow row in ds.Tables["EmpHierarchy"].Rows)
{
if (row.IsNull("HElementDesc"))
{
row["HElementDesc"] = "-";
}
if (string.IsNullOrEmpty(row["HElementDesc"].ToString()))
{
row["HElementDesc"] = "-";
}
}
//from = "from HierarchyLevel hl ";//EmpPos ON P.EmpID=EmpPos.EmpID AND EmpPos.EmpPosEffTo IS NULL LEFT JOIN Position Pos ON EmpPos.PositionID=Pos.PositionID";
//select = " hl.* ";
//filter = new DBFilter();
//filter.add("HLevelSeqNo", true);
//filter.loadData(ds, "HierarchyLevel", null, select, from, null);
if (reportDocument == null)
{
reportDocument = new ReportTemplate.Report_Employee_SalaryList();
}
else
{
}
reportDocument.SetDataSource(ds);
reportDocument.SetParameterValue("HierarchyLevel1", HierarchyLevel1);
reportDocument.SetParameterValue("HierarchyLevel2", HierarchyLevel2);
reportDocument.SetParameterValue("HierarchyLevel3", HierarchyLevel3);
if (reportDocument.ParameterFields["HierarchyLevel4"] != null)
{
reportDocument.SetParameterValue("HierarchyLevel4", HierarchyLevel4);
}
return(reportDocument);
}
else
{
return(null);
}
}
public override CrystalDecisions.CrystalReports.Engine.ReportDocument GenerateReport()
{
if (EmpPayrollList != null)
{
DataSet.PayrollTrialRunSummaryDataSet dataSet = new DataSet.PayrollTrialRunSummaryDataSet();
DataSet.PayrollTrialRunSummaryDataSet.TrialRunSummaryDataTable dataTable = dataSet.TrialRunSummary;
foreach (EEmpPayroll empPayroll in EmpPayrollList)
{
EEmpPayroll.db.select(dbConn, empPayroll);
EPayrollPeriod payPeriod = new EPayrollPeriod();
payPeriod.PayPeriodID = empPayroll.PayPeriodID;
EPayrollPeriod.db.select(dbConn, payPeriod);
string strPayPeriod = payPeriod.PayPeriodFr.ToString("yyyy-MM-dd") + " - " + payPeriod.PayPeriodTo.ToString("yyyy-MM-dd");
EPayrollGroup payGroup = new EPayrollGroup();
payGroup.PayGroupID = payPeriod.PayGroupID;
EPayrollGroup.db.select(dbConn, payGroup);
EPayrollBatch payBatch = new EPayrollBatch();
payBatch.PayBatchID = empPayroll.PayBatchID;
EPayrollBatch.db.select(dbConn, payBatch);
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = empPayroll.EmpID;
EEmpPersonalInfo.db.select(dbConn, empInfo);
DataSet.PayrollTrialRunSummaryDataSet.TrialRunSummaryRow row = dataTable.NewTrialRunSummaryRow();
row.EmpPayrollID = empPayroll.EmpPayrollID;
row.EmpID = empPayroll.EmpID;
row.EmpNo = empInfo.EmpNo;
row.EmpName = empInfo.EmpEngFullName;
row.EmpAlias = empInfo.EmpAlias;
row.PayGroupDesc = payGroup.PayGroupCode + " - " + payGroup.PayGroupDesc;
row.PayGroupID = empPayroll.PayPeriodID;
row.PayrollPeriod = strPayPeriod;
if (!payBatch.PayBatchValueDate.Ticks.Equals(0))
{
row.PayBatchValueDate = payBatch.PayBatchValueDate;
}
row.PayBatchRemark = payBatch.PayBatchRemark;
row.BasicSalary = 0;
row.LeaveAllowance = 0;
row.PFundEE = 0;
row.PFundER = 0;
row.Overtime = 0;
row.Bonus = 0;
row.Commission = 0;
row.Others = 0;
row.NetIncome = 0;
row.TotalIncome = 0;
row.PayMethodAutoPay = 0;
row.PayMethodCash = 0;
row.PayMethodCheque = 0;
row.PayMethodOthers = 0;
AND recurringBasicSalaryAndTerms = new AND();
recurringBasicSalaryAndTerms.add(new Match("EmpRPEffFr", "<=", payPeriod.PayPeriodTo));
DBFilter basicSalaryPaymentCodeFilter = new DBFilter();
basicSalaryPaymentCodeFilter.add(new Match("PaymentTypeID", EPaymentType.SystemPaymentType.BasicSalaryPaymentType(dbConn).PaymentTypeID));
recurringBasicSalaryAndTerms.add(new IN("PayCodeID", "Select PaymentCodeID FROM " + EPaymentCode.db.dbclass.tableName, basicSalaryPaymentCodeFilter));
EEmpRecurringPayment empRP = (EEmpRecurringPayment)AppUtils.GetLastObj(dbConn, EEmpRecurringPayment.db, "EmpRPEffFr", empPayroll.EmpID, recurringBasicSalaryAndTerms);
if (empRP != null)
{
row.RecurringBasicSalary = empRP.EmpRPAmount;
}
DBFilter paymentRecordFilter = new DBFilter();
paymentRecordFilter.add(new Match("EmpPayrollID", empPayroll.EmpPayrollID));
paymentRecordFilter.add(new Match("PayRecStatus", "A"));
ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter);
foreach (EPaymentRecord paymentRecord in paymentRecords)
{
EPaymentCode payCode = new EPaymentCode();
payCode.PaymentCodeID = paymentRecord.PaymentCodeID;
EPaymentCode.db.select(dbConn, payCode);
EPaymentType payType = new EPaymentType();
payType.PaymentTypeID = payCode.PaymentTypeID;
EPaymentType.db.select(dbConn, payType);
if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.BasicSalaryPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase))
{
row.BasicSalary += paymentRecord.PayRecActAmount;
row.TotalIncome += paymentRecord.PayRecActAmount;
}
else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.LeaveDeductionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase))
{
row.LeaveAllowance += paymentRecord.PayRecActAmount;
row.TotalIncome += paymentRecord.PayRecActAmount;
}
else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.LeaveAllowancePaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase))
{
row.LeaveAllowance += paymentRecord.PayRecActAmount;
row.TotalIncome += paymentRecord.PayRecActAmount;
}
else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.MPFEmployeeMandatoryContributionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase))
{
row.PFundEE += paymentRecord.PayRecActAmount;
}
else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.MPFEmployeeVoluntaryContributionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase))
{
row.PFundEE += paymentRecord.PayRecActAmount;
}
else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.PFundEmployeeContributionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase))
{
row.PFundEE += paymentRecord.PayRecActAmount;
}
else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.MPFEmployerMandatoryContributionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase))
{
row.PFundER += paymentRecord.PayRecActAmount;
}
else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.MPFEmployerVoluntaryContributionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase))
{
row.PFundER += paymentRecord.PayRecActAmount;
}
else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.PFundEmployerContributionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase))
{
row.PFundER += paymentRecord.PayRecActAmount;
}
else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.OverTimePaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase))
{
row.Overtime += paymentRecord.PayRecActAmount;
row.TotalIncome += paymentRecord.PayRecActAmount;
}
else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.BonusPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase))
{
row.Bonus += paymentRecord.PayRecActAmount;
row.TotalIncome += paymentRecord.PayRecActAmount;
}
else if (payType.PaymentTypeCode.Equals(EPaymentType.SystemPaymentType.CommissionPaymentType(dbConn).PaymentTypeCode, StringComparison.CurrentCultureIgnoreCase))
{
row.Commission += paymentRecord.PayRecActAmount;
row.TotalIncome += paymentRecord.PayRecActAmount;
}
else
{
row.Others += paymentRecord.PayRecActAmount;
row.TotalIncome += paymentRecord.PayRecActAmount;
}
if (paymentRecord.PayRecMethod.Equals("A"))
{
row.PayMethodAutoPay += paymentRecord.PayRecActAmount;
}
else if (paymentRecord.PayRecMethod.Equals("C"))
{
row.PayMethodCash += paymentRecord.PayRecActAmount;
}
else if (paymentRecord.PayRecMethod.Equals("Q"))
{
row.PayMethodCheque += paymentRecord.PayRecActAmount;
}
else
{
row.PayMethodOthers += paymentRecord.PayRecActAmount;
}
row.NetIncome += paymentRecord.PayRecActAmount;
}
row.TotalWagesForMinWages = HROne.Payroll.PayrollProcess.GetTotalWagesWithoutRestDayPayment(dbConn, empInfo.EmpID, payPeriod.PayPeriodFr, payPeriod.PayPeriodTo, null);
row.TotalWorkingHours = HROne.Payroll.PayrollProcess.GetTotalEmpPayrollWorkingHours(dbConn, empInfo.EmpID, payPeriod.PayPeriodID);
row.MinWagesRequired = row.TotalWorkingHours * HROne.Payroll.PayrollProcess.GetMinimumWages(dbConn, empInfo.EmpID, payPeriod.PayPeriodTo);
DBFilter mpfRecordFilter = new DBFilter();
mpfRecordFilter.add(new Match("EmpPayrollID", empPayroll.EmpPayrollID));
ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter);
foreach (EMPFRecord mpfRecord in mpfRecords)
{
row.PFundER += mpfRecord.MPFRecActVCER + mpfRecord.MPFRecActMCER;
}
ArrayList orsoRecords = EORSORecord.db.select(dbConn, mpfRecordFilter);
foreach (EORSORecord orsoRecord in orsoRecords)
{
row.PFundER += orsoRecord.ORSORecActER;
}
DBFilter positionFilter = new DBFilter();
positionFilter.add(new Match("EmpID", empInfo.EmpID));
positionFilter.add(new Match("PayGroupID", payGroup.PayGroupID));
positionFilter.add(new Match("EmpPosEffFr", "<=", payPeriod.PayPeriodTo));
OR orPosEffToTerms = new OR();
orPosEffToTerms.add(new Match("EmpPosEffTo", ">=", payPeriod.PayPeriodFr));
orPosEffToTerms.add(new NullTerm("EmpPosEffTo"));
positionFilter.add(orPosEffToTerms);
ArrayList positionInfos = EEmpPositionInfo.db.select(dbConn, positionFilter);
EEmpPositionInfo empPos = null;
if (positionInfos.Count > 0)
{
empPos = (EEmpPositionInfo)positionInfos[0];
}
else
{
empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID);
}
if (HLevelIDDisplayList != null && empPos != null)
{
for (int count = 0; count < HLevelIDDisplayList.Count && count < 2; count++)
{
int hLevelID = Convert.ToInt32(HLevelIDDisplayList[count]);
EHierarchyLevel hLevel = new EHierarchyLevel();
hLevel.HLevelID = hLevelID;
if (EHierarchyLevel.db.select(dbConn, hLevel))
{
row["HLevelDesc" + (count + 1)] = hLevel.HLevelDesc;
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
empHierarchyFilter.add(new Match("HLevelID", hLevelID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
if (empHierarchyList.Count > 0)
{
EEmpHierarchy empHierarchy = (EEmpHierarchy)empHierarchyList[0];
EHierarchyElement hElement = new EHierarchyElement();
hElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hElement))
{
row["HElementCode" + (count + 1)] = hElement.HElementCode;
row["HElementDesc" + (count + 1)] = hElement.HElementDesc;
}
}
}
}
}
dataTable.Rows.Add(row);
}
if (reportDocument == null)
{
if (reportType.Equals(ReportType.TrialRun))
{
reportDocument = new ReportTemplate.Report_Payroll_TrialRunSummary();
}
else if (reportType.Equals(ReportType.History))
{
reportDocument = new ReportTemplate.Report_Payroll_HistorySummary();
}
}
else
{
}
reportDocument.SetDataSource(dataSet);
return(reportDocument);
}
else
{
return(null);
}
}
public static string GetValueFromID(DatabaseConnection dbConn, string fieldName, string fieldValue)
{
try
{
string tmpfieldName = fieldName;
if (tmpfieldName.Equals("PreviousEmpID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("NewEmpID", StringComparison.CurrentCultureIgnoreCase))
{
tmpfieldName = "EmpID";
}
if (tmpfieldName.EndsWith("PaymentCodeID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.EndsWith("PayCodeID", StringComparison.CurrentCultureIgnoreCase))
{
tmpfieldName = "PaymentCodeID";
}
if (tmpfieldName.Equals("DefaultMPFPlanID", StringComparison.CurrentCultureIgnoreCase))
{
tmpfieldName = "MPFPlanID";
}
if (tmpfieldName.Equals("AttendanceFormulaPayFormID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("ReferencePayFormID", StringComparison.CurrentCultureIgnoreCase))
{
tmpfieldName = "PayFormID";
}
if (tmpfieldName.StartsWith("LeaveCode", StringComparison.CurrentCultureIgnoreCase) && tmpfieldName.EndsWith("Formula", StringComparison.CurrentCultureIgnoreCase))
{
tmpfieldName = "PayFormID";
}
if (tmpfieldName.StartsWith("PayGroup", StringComparison.CurrentCultureIgnoreCase) && tmpfieldName.EndsWith("Formula", StringComparison.CurrentCultureIgnoreCase))
{
tmpfieldName = "PayFormID";
}
if (tmpfieldName.Equals("CurrentPayPeriodID", StringComparison.CurrentCultureIgnoreCase))
{
tmpfieldName = "PayPeriodID";
}
if (tmpfieldName.Equals("EmpPosDefaultRosterCodeID", StringComparison.CurrentCultureIgnoreCase))
{
tmpfieldName = "RosterCodeID";
}
if (tmpfieldName.Equals("RosterClientMappingSiteCodeToHLevelID", StringComparison.CurrentCultureIgnoreCase))
{
tmpfieldName = "HLevelID";
}
if (tmpfieldName.Equals("EmpFirstAuthorizationGp", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("EmpSecondAuthorizationGp", StringComparison.CurrentCultureIgnoreCase))
{
tmpfieldName = "AuthorizationGroupID";
}
if (tmpfieldName.StartsWith("A", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("ALProrataRoundingRuleID", StringComparison.CurrentCultureIgnoreCase))
{
EALProrataRoundingRule obj = new EALProrataRoundingRule();
obj.ALProrataRoundingRuleID = int.Parse(fieldValue);
if (EALProrataRoundingRule.db.select(dbConn, obj))
{
return(obj.ALProrataRoundingRuleCode + " - " + obj.ALProrataRoundingRuleDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("AttendancePlanID", StringComparison.CurrentCultureIgnoreCase))
{
EAttendancePlan obj = new EAttendancePlan();
obj.AttendancePlanID = int.Parse(fieldValue);
if (EAttendancePlan.db.select(dbConn, obj))
{
return(obj.AttendancePlanCode + " - " + obj.AttendancePlanDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("AuthorizationGroupID", StringComparison.CurrentCultureIgnoreCase))
{
EAuthorizationGroup obj = new EAuthorizationGroup();
obj.AuthorizationGroupID = int.Parse(fieldValue);
if (EAuthorizationGroup.db.select(dbConn, obj))
{
return(obj.AuthorizationCode + " - " + obj.AuthorizationDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("AVCPlanID", StringComparison.CurrentCultureIgnoreCase))
{
EAVCPlan obj = new EAVCPlan();
obj.AVCPlanID = int.Parse(fieldValue);
if (EAVCPlan.db.select(dbConn, obj))
{
return(obj.AVCPlanCode + " - " + obj.AVCPlanDesc);
}
else
{
return(string.Empty);
}
}
}
else if (tmpfieldName.StartsWith("C", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("CessationReasonID", StringComparison.CurrentCultureIgnoreCase))
{
ECessationReason obj = new ECessationReason();
obj.CessationReasonID = int.Parse(fieldValue);
if (ECessationReason.db.select(dbConn, obj))
{
return(obj.CessationReasonCode + " - " + obj.CessationReasonDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("CompanyID", StringComparison.CurrentCultureIgnoreCase))
{
ECompany obj = new ECompany();
obj.CompanyID = int.Parse(fieldValue);
if (ECompany.db.select(dbConn, obj))
{
return(obj.CompanyCode + " - " + obj.CompanyName);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("CostAllocationDetailID", StringComparison.CurrentCultureIgnoreCase))
{
ECostAllocationDetail obj = new ECostAllocationDetail();
obj.CostAllocationDetailID = int.Parse(fieldValue);
if (ECostAllocationDetail.db.select(dbConn, obj))
{
return(GetValueFromID(dbConn, "PayCodeID", obj.PaymentCodeID.ToString()) + ", " + GetValueFromID(dbConn, "CostCenterID", obj.CostCenterID.ToString()));
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("CostAllocationID", StringComparison.CurrentCultureIgnoreCase))
{
ECostAllocation obj = new ECostAllocation();
obj.CostAllocationID = int.Parse(fieldValue);
if (ECostAllocation.db.select(dbConn, obj))
{
return(GetValueFromID(dbConn, "EmpPayrollID", obj.EmpPayrollID.ToString()));
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("CostCenterID", StringComparison.CurrentCultureIgnoreCase))
{
ECostCenter obj = new ECostCenter();
obj.CostCenterID = int.Parse(fieldValue);
if (ECostCenter.db.select(dbConn, obj))
{
return(obj.CostCenterCode + " - " + obj.CostCenterDesc);
}
else
{
return(string.Empty);
}
}
}
else if (tmpfieldName.StartsWith("D", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("DocumentTypeID", StringComparison.CurrentCultureIgnoreCase))
{
EDocumentType obj = new EDocumentType();
obj.DocumentTypeID = int.Parse(fieldValue);
if (EDocumentType.db.select(dbConn, obj))
{
return(obj.DocumentTypeCode + " - " + obj.DocumentTypeDesc);
}
else
{
return(string.Empty);
}
}
}
else if (tmpfieldName.StartsWith("E", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("EmpAccID", StringComparison.CurrentCultureIgnoreCase))
{
EEmpBankAccount obj = new EEmpBankAccount();
obj.EmpBankAccountID = int.Parse(fieldValue);
if (EEmpBankAccount.db.select(dbConn, obj))
{
return(obj.EmpBankCode + "-" + obj.EmpBranchCode + "-" + obj.EmpAccountNo);
}
//return obj.EmpBankCode + "-" + obj.EmpBranchCode + "-" + string.Empty.PadRight(obj.EmpAccountNo.Length, 'X');
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("EmpCostCenterID", StringComparison.CurrentCultureIgnoreCase))
{
EEmpCostCenter obj = new EEmpCostCenter();
obj.EmpCostCenterID = int.Parse(fieldValue);
if (EEmpCostCenter.db.select(dbConn, obj))
{
return(GetValueFromID(dbConn, "EmpID", obj.EmpID.ToString()));
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("EmpExtraFieldID", StringComparison.CurrentCultureIgnoreCase))
{
EEmpExtraField obj = new EEmpExtraField();
obj.EmpExtraFieldID = int.Parse(fieldValue);
if (EEmpExtraField.db.select(dbConn, obj))
{
return(obj.EmpExtraFieldName);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("EmpID", StringComparison.CurrentCultureIgnoreCase))
{
EEmpPersonalInfo obj = new EEmpPersonalInfo();
obj.EmpID = int.Parse(fieldValue);
if (EEmpPersonalInfo.db.select(dbConn, obj))
{
return(obj.EmpNo + " - " + obj.EmpEngFullName);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("EmpPayrollID", StringComparison.CurrentCultureIgnoreCase))
{
EEmpPayroll obj = new EEmpPayroll();
obj.EmpPayrollID = int.Parse(fieldValue);
if (EEmpPayroll.db.select(dbConn, obj))
{
return(GetValueFromID(dbConn, "EmpID", obj.EmpID.ToString()) + " : " + GetValueFromID(dbConn, "PayPeriodID", obj.PayPeriodID.ToString()));
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("EmpPosID", StringComparison.CurrentCultureIgnoreCase))
{
EEmpPositionInfo obj = new EEmpPositionInfo();
obj.EmpPosID = int.Parse(fieldValue);
if (EEmpPositionInfo.db.select(dbConn, obj))
{
return(obj.EmpPosEffFr.ToString("yyyy-MM-dd") + " - " + (obj.EmpPosEffTo.Ticks.Equals(0) ? "Present" : obj.EmpPosEffTo.ToString("yyyy-MM-dd")));
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("EmploymentTypeID", StringComparison.CurrentCultureIgnoreCase))
{
EEmploymentType obj = new EEmploymentType();
obj.EmploymentTypeID = int.Parse(fieldValue);
if (EEmploymentType.db.select(dbConn, obj))
{
return(obj.EmploymentTypeCode + " - " + obj.EmploymentTypeDesc);
}
else
{
return(string.Empty);
}
}
}
else if (tmpfieldName.StartsWith("F", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("FunctionID", StringComparison.CurrentCultureIgnoreCase))
{
ESystemFunction obj = new ESystemFunction();
obj.FunctionID = int.Parse(fieldValue);
if (ESystemFunction.db.select(dbConn, obj))
{
return(obj.FunctionCode + " - " + obj.Description);
}
else
{
return(string.Empty);
}
}
}
else if (tmpfieldName.StartsWith("H", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("HElementID", StringComparison.CurrentCultureIgnoreCase))
{
EHierarchyElement obj = new EHierarchyElement();
obj.HElementID = int.Parse(fieldValue);
if (EHierarchyElement.db.select(dbConn, obj))
{
return(obj.HElementCode + " - " + obj.HElementDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("HLevelID", StringComparison.CurrentCultureIgnoreCase))
{
EHierarchyLevel obj = new EHierarchyLevel();
obj.HLevelID = int.Parse(fieldValue);
if (EHierarchyLevel.db.select(dbConn, obj))
{
return(obj.HLevelCode + " - " + obj.HLevelDesc);
}
else
{
return(string.Empty);
}
}
}
else if (tmpfieldName.StartsWith("L", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("LeaveTypeID", StringComparison.CurrentCultureIgnoreCase))
{
ELeaveType obj = new ELeaveType();
obj.LeaveTypeID = int.Parse(fieldValue);
if (ELeaveType.db.select(dbConn, obj))
{
return(obj.LeaveType + " - " + obj.LeaveTypeDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("LeaveCodeID", StringComparison.CurrentCultureIgnoreCase))
{
ELeaveCode obj = new ELeaveCode();
obj.LeaveCodeID = int.Parse(fieldValue);
if (ELeaveCode.db.select(dbConn, obj))
{
return(obj.LeaveCode + " - " + obj.LeaveCodeDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("LeavePlanID", StringComparison.CurrentCultureIgnoreCase))
{
ELeavePlan obj = new ELeavePlan();
obj.LeavePlanID = int.Parse(fieldValue);
if (ELeavePlan.db.select(dbConn, obj))
{
return(obj.LeavePlanCode + " - " + obj.LeavePlanDesc);
}
else
{
return(string.Empty);
}
}
}
else if (tmpfieldName.StartsWith("M", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("MPFPlanID", StringComparison.CurrentCultureIgnoreCase))
{
EMPFPlan obj = new EMPFPlan();
obj.MPFPlanID = int.Parse(fieldValue);
if (EMPFPlan.db.select(dbConn, obj))
{
return(obj.MPFPlanCode + " - " + obj.MPFPlanDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("MPFSchemeID", StringComparison.CurrentCultureIgnoreCase))
{
EMPFScheme obj = new EMPFScheme();
obj.MPFSchemeID = int.Parse(fieldValue);
if (EMPFScheme.db.select(dbConn, obj))
{
return(obj.MPFSchemeCode + " - " + obj.MPFSchemeDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("MPFSchemeCessationReasonID", StringComparison.CurrentCultureIgnoreCase))
{
EMPFSchemeCessationReason obj = new EMPFSchemeCessationReason();
obj.MPFSchemeCessationReasonID = int.Parse(fieldValue);
if (EMPFSchemeCessationReason.db.select(dbConn, obj))
{
return(obj.MPFSchemeCessationReasonCode + " - " + obj.MPFSchemeCessationReasonDesc);
}
else
{
return(string.Empty);
}
}
}
else if (tmpfieldName.StartsWith("O", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("ORSOPlanID", StringComparison.CurrentCultureIgnoreCase))
{
EORSOPlan obj = new EORSOPlan();
obj.ORSOPlanID = int.Parse(fieldValue);
if (EORSOPlan.db.select(dbConn, obj))
{
return(obj.ORSOPlanCode + " - " + obj.ORSOPlanDesc);
}
else
{
return(string.Empty);
}
}
}
else if (tmpfieldName.StartsWith("P", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("PayGroupID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("PayrollGroupID", StringComparison.CurrentCultureIgnoreCase))
{
EPayrollGroup obj = new EPayrollGroup();
obj.PayGroupID = int.Parse(fieldValue);
if (EPayrollGroup.db.select(dbConn, obj))
{
return(obj.PayGroupCode + " - " + obj.PayGroupDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("PayFormID", StringComparison.CurrentCultureIgnoreCase))
{
EPayrollProrataFormula obj = new EPayrollProrataFormula();
obj.PayFormID = int.Parse(fieldValue);
if (EPayrollProrataFormula.db.select(dbConn, obj))
{
return(obj.PayFormCode + " - " + obj.PayFormDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("PaymentCodeID", StringComparison.CurrentCultureIgnoreCase) || tmpfieldName.Equals("PayCodeID", StringComparison.CurrentCultureIgnoreCase))
{
EPaymentCode obj = new EPaymentCode();
obj.PaymentCodeID = int.Parse(fieldValue);
if (EPaymentCode.db.select(dbConn, obj))
{
return(obj.PaymentCode + " - " + obj.PaymentCodeDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("PaymentTypeID", StringComparison.CurrentCultureIgnoreCase))
{
EPaymentType obj = new EPaymentType();
obj.PaymentTypeID = int.Parse(fieldValue);
if (EPaymentType.db.select(dbConn, obj))
{
return(obj.PaymentTypeCode + " - " + obj.PaymentTypeDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("PayPeriodID", StringComparison.CurrentCultureIgnoreCase))
{
EPayrollPeriod obj = new EPayrollPeriod();
obj.PayPeriodID = int.Parse(fieldValue);
if (EPayrollPeriod.db.select(dbConn, obj))
{
return(GetValueFromID(dbConn, "PayrollGroupID", obj.PayGroupID.ToString()) + ": " + obj.PayPeriodFr.ToString("yyyy-MM-dd") + " to " + obj.PayPeriodTo.ToString("yyyy-MM-dd"));
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("PermitTypeID", StringComparison.CurrentCultureIgnoreCase))
{
EPermitType obj = new EPermitType();
obj.PermitTypeID = int.Parse(fieldValue);
if (EPermitType.db.select(dbConn, obj))
{
return(obj.PermitTypeCode + " - " + obj.PermitTypeDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("PositionID", StringComparison.CurrentCultureIgnoreCase))
{
EPosition obj = new EPosition();
obj.PositionID = int.Parse(fieldValue);
if (EPosition.db.select(dbConn, obj))
{
return(obj.PositionCode + " - " + obj.PositionDesc);
}
else
{
return(string.Empty);
}
}
}
else if (tmpfieldName.StartsWith("Q", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("QualificationID", StringComparison.CurrentCultureIgnoreCase))
{
EQualification obj = new EQualification();
obj.QualificationID = int.Parse(fieldValue);
if (EQualification.db.select(dbConn, obj))
{
return(obj.QualificationCode + " - " + obj.QualificationDesc);
}
else
{
return(string.Empty);
}
}
}
else if (tmpfieldName.StartsWith("R", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("RankID", StringComparison.CurrentCultureIgnoreCase))
{
ERank obj = new ERank();
obj.RankID = int.Parse(fieldValue);
if (ERank.db.select(dbConn, obj))
{
return(obj.RankCode + " - " + obj.RankDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("ReminderTypeID", StringComparison.CurrentCultureIgnoreCase))
{
EReminderType obj = new EReminderType();
obj.ReminderTypeID = int.Parse(fieldValue);
if (EReminderType.db.select(dbConn, obj))
{
return(obj.ReminderTypeCode + " - " + obj.ReminderTypeDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("RosterClientID", StringComparison.CurrentCultureIgnoreCase))
{
ERosterClient obj = new ERosterClient();
obj.RosterClientID = int.Parse(fieldValue);
if (ERosterClient.db.select(dbConn, obj))
{
return(obj.RosterClientCode + " - " + obj.RosterClientName);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("RosterClientSiteID", StringComparison.CurrentCultureIgnoreCase))
{
ERosterClientSite obj = new ERosterClientSite();
obj.RosterClientSiteID = int.Parse(fieldValue);
if (ERosterClientSite.db.select(dbConn, obj))
{
return(obj.RosterClientSiteCode);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("RosterCodeID", StringComparison.CurrentCultureIgnoreCase))
{
ERosterCode obj = new ERosterCode();
obj.RosterCodeID = int.Parse(fieldValue);
if (ERosterCode.db.select(dbConn, obj))
{
return(obj.RosterCode + " - " + obj.RosterCodeDesc);
}
else
{
return(string.Empty);
}
}
}
else if (tmpfieldName.StartsWith("S", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("SkillID", StringComparison.CurrentCultureIgnoreCase))
{
ESkill obj = new ESkill();
obj.SkillID = int.Parse(fieldValue);
if (ESkill.db.select(dbConn, obj))
{
return(obj.SkillCode + " - " + obj.SkillDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("SkillLevelID", StringComparison.CurrentCultureIgnoreCase))
{
ESkillLevel obj = new ESkillLevel();
obj.SkillLevelID = int.Parse(fieldValue);
if (ESkillLevel.db.select(dbConn, obj))
{
return(obj.SkillLevelCode + " - " + obj.SkillLevelDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("StaffTypeID", StringComparison.CurrentCultureIgnoreCase))
{
EStaffType obj = new EStaffType();
obj.StaffTypeID = int.Parse(fieldValue);
if (EStaffType.db.select(dbConn, obj))
{
return(obj.StaffTypeCode + " - " + obj.StaffTypeDesc);
}
else
{
return(string.Empty);
}
}
}
else if (tmpfieldName.StartsWith("T", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("TaxCompID", StringComparison.CurrentCultureIgnoreCase))
{
ETaxCompany obj = new ETaxCompany();
obj.TaxCompID = int.Parse(fieldValue);
if (ETaxCompany.db.select(dbConn, obj))
{
return(obj.TaxCompEmployerName);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("TaxEmpID", StringComparison.CurrentCultureIgnoreCase))
{
ETaxEmp obj = new ETaxEmp();
obj.TaxEmpID = int.Parse(fieldValue);
if (ETaxEmp.db.select(dbConn, obj))
{
return(obj.TaxEmpSurname + ", " + obj.TaxEmpOtherName + ", " + GetValueFromID(dbConn, "TaxFormID", obj.TaxFormID.ToString()));
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("TaxFormID", StringComparison.CurrentCultureIgnoreCase))
{
ETaxForm obj = new ETaxForm();
obj.TaxFormID = int.Parse(fieldValue);
if (ETaxForm.db.select(dbConn, obj))
{
return("Tax Year :" + obj.TaxFormYear + ", Form: IR56" + obj.TaxFormType);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("TaxPayID", StringComparison.CurrentCultureIgnoreCase))
{
ETaxPayment obj = new ETaxPayment();
obj.TaxPayID = int.Parse(fieldValue);
if (ETaxPayment.db.select(dbConn, obj))
{
return(obj.TaxPayCode + " - " + obj.TaxPayDesc);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("TrainingCourseID", StringComparison.CurrentCultureIgnoreCase))
{
ETrainingCourse obj = new ETrainingCourse();
obj.TrainingCourseID = int.Parse(fieldValue);
if (ETrainingCourse.db.select(dbConn, obj))
{
return(obj.TrainingCourseCode + " - " + obj.TrainingCourseName);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("TrainingSeminarID", StringComparison.CurrentCultureIgnoreCase))
{
ETrainingSeminar obj = new ETrainingSeminar();
obj.TrainingSeminarID = int.Parse(fieldValue);
if (ETrainingSeminar.db.select(dbConn, obj))
{
return(GetValueFromID(dbConn, "TrainingCourseID", obj.TrainingCourseID.ToString()) + ": " + obj.TrainingSeminarDateFrom.ToString("yyyy-MM-dd") + " to " + obj.TrainingSeminarDateTo.ToString("yyyy-MM-dd"));
}
else
{
return(string.Empty);
}
}
}
else if (tmpfieldName.StartsWith("U", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("UserID", StringComparison.CurrentCultureIgnoreCase))
{
EUser obj = new EUser();
obj.UserID = int.Parse(fieldValue);
if (EUser.db.select(dbConn, obj))
{
return(obj.LoginID + " - " + obj.UserName);
}
else
{
return(string.Empty);
}
}
if (tmpfieldName.Equals("UserGroupID", StringComparison.CurrentCultureIgnoreCase))
{
EUserGroup obj = new EUserGroup();
obj.UserGroupID = int.Parse(fieldValue);
if (EUserGroup.db.select(dbConn, obj))
{
return(obj.UserGroupName + " - " + obj.UserGroupDesc);
}
else
{
return(string.Empty);
}
}
}
else if (tmpfieldName.StartsWith("Y", StringComparison.CurrentCultureIgnoreCase))
{
if (tmpfieldName.Equals("YebPlanID", StringComparison.CurrentCultureIgnoreCase))
{
EYEBPlan obj = new EYEBPlan();
obj.YEBPlanID = int.Parse(fieldValue);
if (EYEBPlan.db.select(dbConn, obj))
{
return(obj.YEBPlanCode + " - " + obj.YEBPlanDesc);
}
else
{
return(string.Empty);
}
}
}
if (tmpfieldName.EndsWith("ID") && !tmpfieldName.EndsWith("HKID") && !tmpfieldName.EndsWith("LoginID") && !tmpfieldName.EndsWith("CurrencyID") && !tmpfieldName.EndsWith("LeaveAppID") && !tmpfieldName.EndsWith("EmpPaymentID") && !tmpfieldName.EndsWith("PayBatchID") && !tmpfieldName.EndsWith("PayRecID") && !tmpfieldName.EndsWith("RosterTableID") && !tmpfieldName.EndsWith("SynID") && !tmpfieldName.EndsWith("CNDImportBatchID"))
{
if (ESystemParameter.getParameter(dbConn, "DebugMode").Equals("Y"))
{
throw new Exception("ID field not define:" + fieldName);
}
}
return(string.Empty);
}
catch (Exception ex)
{
if (ESystemParameter.getParameter(dbConn, "DebugMode").Equals("Y"))
{
throw ex;
}
else
{
return(string.Empty);
}
}
}
public void GenerateDetailByEmpID()
{
//dataSet = new DataSet.Payroll_PaySlip();
//EPayrollPeriod payPeriod = new EPayrollPeriod();
//payPeriod.PayPeriodID = PayPeriodID;
//EPayrollPeriod.db.select(dbConn, payPeriod);
//EPayrollGroup payGroup = new EPayrollGroup();
//payGroup.PayGroupID = payPeriod.PayGroupID;
//EPayrollGroup.db.select(dbConn, payGroup);
//string strPayPeriod = payPeriod.PayPeriodFr.ToString("yyyy-MM-dd") + " - " + payPeriod.PayPeriodTo.ToString("yyyy-MM-dd");
DataTable empInfoTable = dataSet.Tables["EmpInfo"];
DataTable dataTable = dataSet.Tables["PaySlip"];
DataTable mpfTable = dataSet.Tables["PaySlip_MPF"];
DataTable orsoTable = dataSet.Tables["PaySlip_ORSO"];
foreach (EEmpTermination empTermination in EmpList)
{
string empDiv = string.Empty;
string empDep = string.Empty;
EEmpTermination.db.select(dbConn, empTermination);
ECessationReason cessationReason = new ECessationReason();
cessationReason.CessationReasonID = empTermination.CessationReasonID;
ECessationReason.db.select(dbConn, cessationReason);
//DBFilter bankAccountFilter = new DBFilter();
//bankAccountFilter.add(new Match("EmpID", empInfo.EmpID));
//bankAccountFilter.add(new Match("EmpAccDefault", 1));
//ArrayList bankAccounts = EEmpBankAccount.db.select(dbConn, bankAccountFilter);
//EEmpBankAccount bankAccount;
//if (bankAccounts.Count > 0)
// bankAccount = (EEmpBankAccount)bankAccounts[0];
//else
// bankAccount = new EEmpBankAccount();
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = empTermination.EmpID;
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
//DBFilter positionFilter = new DBFilter();
//positionFilter.add(new Match("EmpID", empInfo.EmpID));
////positionFilter.add(new Match("PayGroupID", payGroup.PayGroupID));
//positionFilter.add(new Match("EmpPosEffFr", "<=", empTermination.EmpTermLastDate));
//OR orPosEffToTerms = new OR();
//orPosEffToTerms.add(new Match("EmpPosEffTo", ">=", empTermination.EmpTermLastDate));
//orPosEffToTerms.add(new NullTerm("EmpPosEffTo"));
//positionFilter.add(orPosEffToTerms);
//ArrayList positionInfos = EEmpPositionInfo.db.select(dbConn, positionFilter);
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, empTermination.EmpTermLastDate, empInfo.EmpID);// new EPosition();
if (empPos == null)
{
empPos = new EEmpPositionInfo();
}
EPosition position = new EPosition();
ECompany company = new ECompany();
////if (positionInfos.Count > 0)
////{
// EEmpPositionInfo empPos = (EEmpPositionInfo)positionInfos[0];
position.PositionID = empPos.PositionID;
EPosition.db.select(dbConn, position);
company.CompanyID = empPos.CompanyID;
ECompany.db.select(dbConn, company);
DBFilter emphierarchyFilter = new DBFilter();
emphierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, emphierarchyFilter);
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyElement hElement = new EHierarchyElement();
hElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hElement))
{
EHierarchyLevel hLevel = new EHierarchyLevel();
hLevel.HLevelID = hElement.HLevelID;
if (EHierarchyLevel.db.select(dbConn, hLevel))
{
if (hLevel.HLevelDesc.Equals("Division", StringComparison.CurrentCultureIgnoreCase) || hLevel.HLevelCode.Equals("DIV", StringComparison.CurrentCultureIgnoreCase))
{
empDiv = hElement.HElementDesc;
}
//if (hLevel.HLevelDesc.Equals("Department", StringComparison.CurrentCultureIgnoreCase) || hLevel.HLevelCode.Equals("DEP", StringComparison.CurrentCultureIgnoreCase))
// empDep = hElement.HElementDesc;
}
}
}
//}
DBFilter basicSalaryRPFilter = new DBFilter();
basicSalaryRPFilter.add(new Match("EmpID", empInfo.EmpID));
basicSalaryRPFilter.add(new Match("EmpRPEffFr", "<=", empTermination.EmpTermLastDate));
OR orbasicSalaryRPToTerm = new OR();
orbasicSalaryRPToTerm.add(new Match("EmpRPEffTo", ">=", empTermination.EmpTermLastDate));
orbasicSalaryRPToTerm.add(new NullTerm("EmpRPEffTo"));
basicSalaryRPFilter.add(orbasicSalaryRPToTerm);
//filter.add(EmpRPEffFr,true);
DBFilter basicSalaryPaymentCodeFilter = new DBFilter();
basicSalaryPaymentCodeFilter.add(new Match("PaymentTypeID", EPaymentType.SystemPaymentType.BasicSalaryPaymentType(dbConn).PaymentTypeID));
basicSalaryRPFilter.add(new IN("PayCodeID", "Select PaymentCodeID from PaymentCode", basicSalaryPaymentCodeFilter));
ArrayList recurringPayments = EEmpRecurringPayment.db.select(dbConn, basicSalaryRPFilter);
EEmpRecurringPayment basicSalaryRP = new EEmpRecurringPayment();
if (recurringPayments.Count > 0)
{
basicSalaryRP = (EEmpRecurringPayment)recurringPayments[0];
}
DBFilter payPeriodFilter = new DBFilter();
payPeriodFilter.add(new Match("PayPeriodTo", ">=", empTermination.EmpTermLastDate));
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(new Match("EmpID", empInfo.EmpID));
empPayrollFilter.add(new IN("PayPeriodID", "Select PayPeriodID from PayrollPeriod", payPeriodFilter));
string PayBatchInTerm = string.Empty;
if (payBatchList != null)
{
foreach (EPayrollBatch payrollBatch in payBatchList)
{
if (string.IsNullOrEmpty(PayBatchInTerm))
{
PayBatchInTerm = payrollBatch.PayBatchID.ToString();
}
else
{
PayBatchInTerm += "," + payrollBatch.PayBatchID.ToString();
}
}
}
if (!string.IsNullOrEmpty(PayBatchInTerm))
{
empPayrollFilter.add(new IN("PayBatchID", PayBatchInTerm, null));
}
if (PayPeriodList != null)
{
string payPeriodIDList = string.Empty;
foreach (EPayrollPeriod payPeriod in PayPeriodList)
{
if (string.IsNullOrEmpty(payPeriodIDList))
{
payPeriodIDList = payPeriod.PayPeriodID.ToString();
}
else
{
payPeriodIDList += "," + payPeriod.PayPeriodID.ToString();
}
}
if (!string.IsNullOrEmpty(payPeriodIDList))
{
empPayrollFilter.add(new IN("PayPeriodID", payPeriodIDList, null));
}
}
// if ReportType.Unspecify, no filter is add (for backward compatibility with previous UI)
if (reportType.Equals(ReportType.TrialRun))
{
empPayrollFilter.add(new Match("EmpPayStatus", "=", "T"));
}
else if (reportType.Equals(ReportType.History))
{
empPayrollFilter.add(new Match("EmpPayStatus", "<>", "T"));
}
IN inEmpPayroll = new IN("EmpPayrollID", "Select EmpPayrollID from EmpPayroll", empPayrollFilter);
DBFilter paymentRecordFilter = new DBFilter();
paymentRecordFilter.add(inEmpPayroll);
paymentRecordFilter.add(new Match("PayRecStatus", "A"));
paymentRecordFilter.add("PaymentCodeID", true);
paymentRecordFilter.add("PayRecMethod", true);
ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter);
DBFilter mpfRecordFilter = new DBFilter();
mpfRecordFilter.add(inEmpPayroll);
mpfRecordFilter.add("MPFRecPeriodFr", true);
ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter);
DBFilter orsoRecordFilter = new DBFilter();
orsoRecordFilter.add(inEmpPayroll);
orsoRecordFilter.add("ORSORecPeriodFr", true);
ArrayList orsoRecords = EORSORecord.db.select(dbConn, orsoRecordFilter);
EPayrollBatch payBatch = null;
DBFilter payBatchFilter = new DBFilter();
payBatchFilter.add(new IN("PayBatchID", "Select PayBatchID from EmpPayroll", empPayrollFilter));
if (!string.IsNullOrEmpty(PayBatchInTerm))
{
payBatchFilter.add(new IN("PayBatchID", PayBatchInTerm, null));
}
payBatchFilter.add("PayBatchValueDate", false);
ArrayList payBatchs = EPayrollBatch.db.select(dbConn, payBatchFilter);
if (payBatchs.Count > 0)
{
payBatch = (EPayrollBatch)payBatchs[0];
}
bool hasMPF = false, hasORSO = false, hasLeaveBalance = false;
if (IsShowLeaveBalance)
{
hasLeaveBalance = GenerateLeaveBalanceData(empInfo.EmpID, empTermination.EmpTermLastDate);
}
//double intALBalance = 0;
//HROne.LeaveCalc.LeaveBalanceCalc calc = new HROne.LeaveCalc.LeaveBalanceCalc(empInfo.EmpID, payPeriod.PayPeriodFr.AddDays(-1));
//ArrayList leaveBalanceList = calc.getCurrentBalanceList();
//foreach (ELeaveBalance b in leaveBalanceList)
//{
// if (b.LeaveTypeID.Equals(ELeaveType.ANNUAL_LEAVE_TYPE(dbConn).LeaveTypeID))
// intALBalance = b.Balance;
//}
hasMPF = GenerateMPFData(empInfo.EmpID, mpfRecords);
hasORSO = GenerateORSOData(empInfo.EmpID, orsoRecords);
DataRow empInfoRow = empInfoTable.NewRow();
empInfoRow["EmpID"] = empInfo.EmpID;
empInfoRow["EmpNo"] = empInfo.EmpNo;
empInfoRow["EmpName"] = empInfo.EmpEngFullName;
empInfoRow["EmpChineseName"] = empInfo.EmpChiFullName;
empInfoRow["EmpHKID"] = empInfo.EmpHKID;
empInfoRow["EmpDateOfJoin"] = empInfo.EmpDateOfJoin;
//empInfoRow["PayGroupDesc"] = payGroup.PayGroupCode + " - " + payGroup.PayGroupDesc;
//empInfoRow["PayrollPeriod"] = strPayPeriod;
//empInfoRow["PeriodFr"] = payPeriod.PayPeriodFr;
//empInfoRow["PeriodTo"] = payPeriod.PayPeriodTo;
//empInfoRow["MPFDate"] = new DateTime(payPeriod.PayPeriodTo.Year, payPeriod.PayPeriodTo.Month, 10).AddMonths(1);
//empInfoRow["BankAccount"] = bankAccount.EmpBankCode + " - " + bankAccount.EmpBranchCode + "-" + bankAccount.EmpAccountNo;
empInfoRow["CompanyName"] = company.CompanyName;
empInfoRow["PositionDesc"] = position.PositionDesc;
empInfoRow["BasicSalary"] = basicSalaryRP.EmpRPAmount;
empInfoRow["EmpTermResignDate"] = empTermination.EmpTermResignDate;
empInfoRow["EmpTermLastDate"] = empTermination.EmpTermLastDate;
empInfoRow["CessationReasonDesc"] = cessationReason.CessationReasonDesc;
empInfoRow["HasMPF"] = hasMPF;
empInfoRow["HasORSO"] = hasORSO;
empInfoRow["HasLeaveBalance"] = hasLeaveBalance;
int YearOfServiceMonth = Convert.ToInt32(Math.Truncate(HROne.CommonLib.Utility.MonthDifference(empInfo.EmpServiceDate, empTermination.EmpTermLastDate)));
empInfoRow["YearOfServiceYear"] = YearOfServiceMonth / 12;
empInfoRow["YearOfServiceMonth"] = YearOfServiceMonth % 12;
if (payBatch != null)
{
if (payBatch.PayBatchValueDate.Ticks != 0)
{
empInfoRow["ValueDate"] = payBatch.PayBatchValueDate;
}
}
if (empDep.Equals(string.Empty) && empDiv.Equals(string.Empty))
{
empInfoRow["Department"] = empDiv;
}
else if (empDiv.Equals(string.Empty))
{
empInfoRow["Department"] = empDep;
}
else if (empDep.Equals(string.Empty))
{
empInfoRow["Department"] = empDiv;
}
else
{
empInfoRow["Department"] = empDiv + " / " + empDep;
}
//empInfoRow["AnnualLeaveBalance"] = intALBalance;
//empInfoRow["LeaveBalanceAsOfDate"] = payPeriod.PayPeriodFr.AddDays(-1);
empInfoTable.Rows.Add(empInfoRow);
GeneratePayrollData(empInfo.EmpID, paymentRecords);
}
}
DataRow[] allowanceRows = dataSet.Tables["PaySlip"].Select("PayAmount > 0", "PaymentCodeDisplaySeqNo");
foreach (DataRow importRow in allowanceRows)
{
dataSet.Tables["PaySlip_Allowance"].ImportRow(importRow);
}
DataRow[] deductionRows = dataSet.Tables["PaySlip"].Select("PayAmount < 0", "PaymentCodeDisplaySeqNo");
foreach (DataRow importRow in deductionRows)
{
dataSet.Tables["PaySlip_Deduction"].ImportRow(importRow);
}
}
public override CrystalDecisions.CrystalReports.Engine.ReportDocument GenerateReport()
{
DataSet.Payroll_NetPaymentList dataSet = new DataSet.Payroll_NetPaymentList();
if (ByAutoPay)
{
DataSet.Payroll_NetPaymentList.PaymentMethodRow payMethodRow = dataSet.PaymentMethod.NewPaymentMethodRow();
payMethodRow.PayMethodCode = "A";
payMethodRow.PayMethodDesc = "Autopay";
dataSet.PaymentMethod.AddPaymentMethodRow(payMethodRow);
}
if (ByCheque)
{
DataSet.Payroll_NetPaymentList.PaymentMethodRow payMethodRow = dataSet.PaymentMethod.NewPaymentMethodRow();
payMethodRow.PayMethodCode = "Q";
payMethodRow.PayMethodDesc = "Cheque";
dataSet.PaymentMethod.AddPaymentMethodRow(payMethodRow);
}
if (ByCash)
{
DataSet.Payroll_NetPaymentList.PaymentMethodRow payMethodRow = dataSet.PaymentMethod.NewPaymentMethodRow();
payMethodRow.PayMethodCode = "C";
payMethodRow.PayMethodDesc = "Cash";
dataSet.PaymentMethod.AddPaymentMethodRow(payMethodRow);
}
if (ByOthers)
{
DataSet.Payroll_NetPaymentList.PaymentMethodRow payMethodRow = dataSet.PaymentMethod.NewPaymentMethodRow();
payMethodRow.PayMethodCode = "O";
payMethodRow.PayMethodDesc = "Others";
dataSet.PaymentMethod.AddPaymentMethodRow(payMethodRow);
}
foreach (EEmpPersonalInfo empInfo in EmpList)
{
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
DataSet.Payroll_NetPaymentList.employeedetailRow row = dataSet.employeedetail.NewemployeedetailRow();
{
EEmpPositionInfo posInfo = AppUtils.GetLastPositionInfo(dbConn, empInfo.EmpID);
if (posInfo != null)
{
ECompany company = new ECompany();
company.CompanyID = posInfo.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row.CompanyCode = company.CompanyCode;
row.CompanyID = company.CompanyID;
row.CompanyName = company.CompanyName;
}
else
{
row.CompanyCode = string.Empty;
row.CompanyID = 0;
row.CompanyName = "-";
}
row.EmpPosEffFr = posInfo.EmpPosEffFr;
row.EmpPosEffTo = posInfo.EmpPosEffTo;
//posInfo.EmploymentTypeID
row.EmpPosID = posInfo.EmpPosID;
row.LeavePlanID = posInfo.LeavePlanID;
row.PayGroupID = posInfo.PayGroupID;
EPosition position = new EPosition();
position.PositionID = posInfo.PositionID;
if (EPosition.db.select(dbConn, position))
{
row.PositionCode = position.PositionCode;
row.PositionDesc = position.PositionDesc;
row.PositionID = position.PositionID;
}
else
{
row.PositionCode = string.Empty;
row.PositionDesc = "-";
row.PositionID = 0;
}
row.RankID = posInfo.RankID;
row.Remark = empInfo.Remark;
row.StaffTypeID = posInfo.StaffTypeID;
//posInfo.YebPlanID;
ArrayList HierarchyLevelList = EHierarchyLevel.db.select(dbConn, new DBFilter());
foreach (EHierarchyLevel hLevel in HierarchyLevelList)
{
if (hLevel.HLevelSeqNo < 4 && hLevel.HLevelSeqNo > 0)
{
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", posInfo.EmpPosID));
empHierarchyFilter.add(new Match("HLevelID", hLevel.HLevelID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
if (empHierarchyList.Count > 0)
{
EEmpHierarchy empHierarchy = (EEmpHierarchy)empHierarchyList[0];
EHierarchyElement hElement = new EHierarchyElement();
hElement.HElementID = empHierarchy.HElementID;
EHierarchyElement.db.select(dbConn, hElement);
row["HierarchyCode" + hLevel.HLevelSeqNo] = hElement.HElementCode;
row["HierarchyDesc" + hLevel.HLevelSeqNo] = hElement.HElementDesc;
}
}
}
}
else
{
row.CompanyCode = string.Empty;
row.CompanyID = 0;
row.CompanyName = "-";
row.PositionCode = string.Empty;
row.PositionDesc = "-";
row.PositionID = 0;
}
}
row.EmpAlias = empInfo.EmpAlias;
row.EmpChiFullName = empInfo.EmpChiFullName;
row.EmpCorAddr = empInfo.EmpCorAddr;
row.EmpDateOfBirth = empInfo.EmpDateOfBirth;
row.EmpDateOfJoin = empInfo.EmpDateOfJoin;
row.EmpEmail = empInfo.EmpEmail;
row.EmpEngOtherName = empInfo.EmpEngOtherName;
row.EmpEngSurname = empInfo.EmpEngSurname;
row.EmpEngFullName = empInfo.EmpEngFullName;
row.EmpGender = empInfo.EmpGender;
row.EmpHKID = empInfo.EmpHKID;
row.EmpHomePhoneNo = empInfo.EmpHomePhoneNo;
row.EmpID = empInfo.EmpID;
row.EmpMaritalStatus = empInfo.EmpMaritalStatus;
row.EmpMobileNo = empInfo.EmpMobileNo;
row.EmpNationality = empInfo.EmpNationality;
row.EmpNo = empInfo.EmpNo;
row.EmpNoticePeriod = empInfo.EmpNoticePeriod;
row.EmpNoticeUnit = empInfo.EmpNoticeUnit;
row.EmpOfficePhoneNo = empInfo.EmpOfficePhoneNo;
row.EmpPassportExpiryDate = empInfo.EmpPassportExpiryDate;
row.EmpPassportIssuedCountry = empInfo.EmpPassportIssuedCountry;
row.EmpPassportNo = empInfo.EmpPassportNo;
row.EmpPlaceOfBirth = empInfo.EmpPlaceOfBirth;
row.EmpProbaLastDate = empInfo.EmpProbaLastDate;
row.EmpProbaPeriod = empInfo.EmpProbaPeriod;
row.EmpProbaUnit = empInfo.EmpProbaUnit;
row.EmpResAddr = empInfo.EmpResAddr;
row.EmpResAddrAreaCode = empInfo.EmpResAddrAreaCode;
row.EmpServiceDate = empInfo.EmpServiceDate;
row.EmpStatus = empInfo.EmpStatus;
DBFilter empTermFilter = new DBFilter();
empTermFilter.add(new Match("EmpID", empInfo.EmpID));
ArrayList empTermList = EEmpTermination.db.select(dbConn, empTermFilter);
if (empTermList.Count > 0)
{
row.EmpTermLastDate = ((EEmpTermination)empTermList[0]).EmpTermLastDate;
}
dataSet.employeedetail.AddemployeedetailRow(row);
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(new Match("EmpID", empInfo.EmpID));
OR orPayrollBatch = new OR();
foreach (EPayrollBatch payBatch in PayrollBatchList)
{
orPayrollBatch.add(new Match("PayBatchID", payBatch.PayBatchID));
}
empPayrollFilter.add(orPayrollBatch);
IN inEmpPayroll = new IN("EmpPayrollID", "Select EmpPayrollID from EmpPayroll", empPayrollFilter);
DBFilter autoPayPaymentRecordFilter = new DBFilter();
autoPayPaymentRecordFilter.add(inEmpPayroll);
autoPayPaymentRecordFilter.add("EmpAccID", true);
ArrayList autoPayPaymentRecords = EPaymentRecord.db.select(dbConn, autoPayPaymentRecordFilter);
foreach (EPaymentRecord paymentRecord in autoPayPaymentRecords)
{
DataSet.Payroll_NetPaymentList.PaymentRecordRow paymentRow = dataSet.PaymentRecord.NewPaymentRecordRow();
paymentRow.PayRecID = paymentRecord.PayRecID;
paymentRow.EmpID = empInfo.EmpID;
paymentRow.PayRecActAmount = paymentRecord.PayRecActAmount;
paymentRow.PayRecMethod = paymentRecord.PayRecMethod;
paymentRow.EmpAccountNo = string.Empty;
EEmpPayroll empPayroll = new EEmpPayroll();
empPayroll.EmpPayrollID = paymentRecord.EmpPayrollID;
if (EEmpPayroll.db.select(dbConn, empPayroll))
{
if (!empPayroll.EmpPayValueDate.Ticks.Equals(0))
{
paymentRow.EmpPayValueDate = empPayroll.EmpPayValueDate;
}
}
if (paymentRecord.PayRecMethod.Equals("A", StringComparison.CurrentCultureIgnoreCase))
{
// Only Autopay payment show account no
EEmpBankAccount empBankAccount = new EEmpBankAccount();
empBankAccount.EmpBankAccountID = paymentRecord.EmpAccID;
if (EEmpBankAccount.db.select(dbConn, empBankAccount))
{
if (empBankAccount.EmpID.Equals(empInfo.EmpID))
{
paymentRow.EmpAccountNo = empBankAccount.EmpBankCode + "-" + empBankAccount.EmpBranchCode + "-" + empBankAccount.EmpAccountNo;
paymentRow.EmpAccID = paymentRecord.EmpAccID;
}
}
}
dataSet.PaymentRecord.AddPaymentRecordRow(paymentRow);
}
}
}
if (reportDocument == null)
{
reportDocument = new ReportTemplate.Report_Payroll_NetPaymentListByPaymentMethod();
}
else
{
}
reportDocument.SetDataSource(dataSet);
return(reportDocument);
}
public static DataSet.EmployeeDetail.employeedetailRow ImportEmployeeDetailRow(DatabaseConnection dbConn, DataSet.EmployeeDetail.employeedetailDataTable empInfoTable, int EmpID, DateTime AsOfDate)
{
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = EmpID;
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
DataSet.EmployeeDetail.employeedetailRow row = empInfoTable.NewemployeedetailRow();
{
EEmpPositionInfo posInfo = AppUtils.GetLastPositionInfo(dbConn, AsOfDate, EmpID);
if (posInfo != null)
{
ECompany company = new ECompany();
company.CompanyID = posInfo.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row.CompanyCode = company.CompanyCode;
row.CompanyID = company.CompanyID;
row.CompanyName = company.CompanyName;
}
else
{
row.CompanyCode = string.Empty;
row.CompanyID = 0;
row.CompanyName = "-";
}
row.EmpPosEffFr = posInfo.EmpPosEffFr;
row.EmpPosEffTo = posInfo.EmpPosEffTo;
//posInfo.EmploymentTypeID
row.EmpPosID = posInfo.EmpPosID;
row.LeavePlanID = posInfo.LeavePlanID;
row.PayGroupID = posInfo.PayGroupID;
EPosition position = new EPosition();
position.PositionID = posInfo.PositionID;
if (EPosition.db.select(dbConn, position))
{
row.PositionCode = position.PositionCode;
row.PositionDesc = position.PositionDesc;
row.PositionID = position.PositionID;
}
else
{
row.PositionCode = string.Empty;
row.PositionDesc = "-";
row.PositionID = 0;
}
row.RankID = posInfo.RankID;
row.Remark = empInfo.Remark;
row.StaffTypeID = posInfo.StaffTypeID;
//posInfo.YebPlanID;
DBFilter hLevelFilter = new DBFilter();
hLevelFilter.add("HLevelSeqNo", true);
string BusinessHierarchy = string.Empty;
ArrayList HierarchyLevelList = EHierarchyLevel.db.select(dbConn, hLevelFilter);
foreach (EHierarchyLevel hLevel in HierarchyLevelList)
{
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", posInfo.EmpPosID));
empHierarchyFilter.add(new Match("HLevelID", hLevel.HLevelID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
string currentElement = string.Empty;
if (empHierarchyList.Count > 0)
{
EEmpHierarchy empHierarchy = (EEmpHierarchy)empHierarchyList[0];
EHierarchyElement hElement = new EHierarchyElement();
hElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hElement))
{
if (empInfoTable.Columns.Contains("HierarchyCode" + hLevel.HLevelSeqNo))
{
row["HierarchyCode" + hLevel.HLevelSeqNo] = hElement.HElementCode;
}
if (empInfoTable.Columns.Contains("HierarchyDesc" + hLevel.HLevelSeqNo))
{
row["HierarchyDesc" + hLevel.HLevelSeqNo] = hElement.HElementDesc;
}
currentElement = hElement.HElementCode;
}
}
if (string.IsNullOrEmpty(BusinessHierarchy))
{
BusinessHierarchy = currentElement;
}
else
{
BusinessHierarchy += " / " + currentElement;
}
}
row.BusinessHierarchy = BusinessHierarchy;
}
else
{
row.CompanyCode = string.Empty;
row.CompanyID = 0;
row.CompanyName = "-";
row.PositionCode = string.Empty;
row.PositionDesc = "-";
row.PositionID = 0;
}
}
row.EmpAlias = empInfo.EmpAlias;
row.EmpChiFullName = empInfo.EmpChiFullName;
row.EmpCorAddr = empInfo.EmpCorAddr;
row.EmpDateOfBirth = empInfo.EmpDateOfBirth;
row.EmpDateOfJoin = empInfo.EmpDateOfJoin;
row.EmpEmail = empInfo.EmpEmail;
row.EmpEngOtherName = empInfo.EmpEngOtherName;
row.EmpEngSurname = empInfo.EmpEngSurname;
row.EmpEngFullName = empInfo.EmpEngFullName;
row.EmpGender = empInfo.EmpGender;
row.EmpHKID = empInfo.EmpHKID;
row.EmpHomePhoneNo = empInfo.EmpHomePhoneNo;
row.EmpID = empInfo.EmpID;
row.EmpMaritalStatus = empInfo.EmpMaritalStatus;
row.EmpMobileNo = empInfo.EmpMobileNo;
row.EmpNationality = empInfo.EmpNationality;
row.EmpNo = empInfo.EmpNo;
row.EmpNoticePeriod = empInfo.EmpNoticePeriod;
row.EmpNoticeUnit = empInfo.EmpNoticeUnit;
row.EmpOfficePhoneNo = empInfo.EmpOfficePhoneNo;
row.EmpPassportExpiryDate = empInfo.EmpPassportExpiryDate;
row.EmpPassportIssuedCountry = empInfo.EmpPassportIssuedCountry;
row.EmpPassportNo = empInfo.EmpPassportNo;
row.EmpPlaceOfBirth = empInfo.EmpPlaceOfBirth;
row.EmpProbaLastDate = empInfo.EmpProbaLastDate;
row.EmpProbaPeriod = empInfo.EmpProbaPeriod;
row.EmpProbaUnit = empInfo.EmpProbaUnit;
row.EmpResAddr = empInfo.EmpResAddr;
row.EmpResAddrAreaCode = empInfo.EmpResAddrAreaCode;
if (row.EmpResAddrAreaCode.Equals("H"))
{
row.EmpResAddr += ", " + HROne.Common.WebUtility.GetLocalizedString("Hong Kong");
}
else if (row.EmpResAddrAreaCode.Equals("K"))
{
row.EmpResAddr += ", " + HROne.Common.WebUtility.GetLocalizedString("Kowloon");
}
else if (row.EmpResAddrAreaCode.Equals("N"))
{
row.EmpResAddr += ", " + HROne.Common.WebUtility.GetLocalizedString("New Territories");
}
//else
// row.EmpResAddr = ", " + HROne.Common.WebUtility.GetLocalizedString("Overseas");
row.EmpServiceDate = empInfo.EmpServiceDate;
row.EmpStatus = empInfo.EmpStatus;
DBFilter empTermFilter = new DBFilter();
empTermFilter.add(new Match("EmpID", EmpID));
ArrayList empTermList = EEmpTermination.db.select(dbConn, empTermFilter);
if (empTermList.Count > 0)
{
row.EmpTermLastDate = ((EEmpTermination)empTermList[0]).EmpTermLastDate;
}
if (!row.IsEmpTermLastDateNull())
{
row.YearOfService = Utility.YearDifference(empInfo.EmpServiceDate, row.EmpTermLastDate < AsOfDate ? row.EmpTermLastDate : AsOfDate);
}
else
{
row.YearOfService = Utility.YearDifference(empInfo.EmpServiceDate, AsOfDate);
}
if (row.YearOfService < 0)
{
row.YearOfService = 0;
}
empInfoTable.AddemployeedetailRow(row);
return(row);
}
else
{
return(null);
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
startMonth = DateTime.Parse(Year.Text + "-" + Month.SelectedValue + "-1");
tempMonth = DateTime.Parse(Year2.Text + "-" + Month2.SelectedValue + "-1");
endMonth = tempMonth.AddMonths(1).AddDays(-1);
int report_range_days = ((TimeSpan)(endMonth - startMonth)).Days + 1;
if (report_range_days <= 0)
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.addError("Invalid Date Range Parameter: From-Date must be prior to To-Date");
return;
}
peroid = "Period: " + startMonth.ToString("yyyy-MM-dd") + " to " + endMonth.ToString("yyyy-MM-dd");
ArrayList list = WebUtils.SelectedRepeaterItemToBaseObjectList(db, Repeater, "ItemSelect");
if (list.Count > 0)
{
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
HROne.Export.ExcelExport export = new HROne.Export.ExcelExport(exportFileName);
DataTable tmpDataTable = new DataTable(TABLE_NAME);
tmpDataTable.Columns.Add(FIELD_COMPANY, typeof(string));
tmpDataTable.Columns.Add(FIELD_DIVISION, typeof(string));
tmpDataTable.Columns.Add(FIELD_DEPARTMENT, typeof(string));
tmpDataTable.Columns.Add(FIELD_SECTION, typeof(string));
tmpDataTable.Columns.Add(FIELD_EMP_NO, typeof(string));
tmpDataTable.Columns.Add(FIELD_EMP_NAME, typeof(string));
tmpDataTable.Columns.Add(FIELD_ALIAS, typeof(string));
tmpDataTable.Columns.Add(FIELD_POSITION, typeof(string));
tmpDataTable.Columns.Add(FIELD_FROM, typeof(DateTime));
tmpDataTable.Columns.Add(FIELD_TO, typeof(DateTime));
tmpDataTable.Columns.Add(FIELD_COST_CENTER, typeof(string));
tmpDataTable.Columns.Add(FIELD_PERCENTAGE, typeof(double));
foreach (EEmpPersonalInfo obj in list)
{
if (EEmpPersonalInfo.db.select(dbConn, obj))
{
DBFilter filter = new DBFilter();
filter.add(new Match("EmpID", obj.EmpID));
ArrayList empCsts = EEmpCostCenter.db.select(dbConn, filter);
foreach (EEmpCostCenter empCostCenter in empCsts)
{
DBFilter empCstDetailFilter = new DBFilter();
empCstDetailFilter.add(new Match("EmpCostCenterID", empCostCenter.EmpCostCenterID));
ArrayList empCstDetailList = EEmpCostCenterDetail.db.select(dbConn, empCstDetailFilter);
foreach (EEmpCostCenterDetail detail in empCstDetailList)
{
DataRow row = tmpDataTable.NewRow();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, AppUtils.ServerDateTime(), obj.EmpID);
if (empPos != null)
{
DBFilter positionFilter = new DBFilter();
positionFilter.add(new Match("PositionID", empPos.PositionID));
ArrayList positionList = EPosition.db.select(dbConn, positionFilter);
EPosition postion = (EPosition)positionList[0];
row[FIELD_POSITION] = postion.PositionDesc;
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row[FIELD_COMPANY] = company.CompanyName;
}
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
if (hierarchyElement.HLevelID == 1)
{
row[FIELD_DEPARTMENT] = hierarchyElement.HElementDesc;
}
else if (hierarchyElement.HLevelID == 2)
{
row[FIELD_DIVISION] = hierarchyElement.HElementDesc;
}
else if (hierarchyElement.HLevelID == 3)
{
row[FIELD_SECTION] = hierarchyElement.HElementDesc;
}
}
}
}
row[FIELD_EMP_NO] = obj.EmpNo;
row[FIELD_EMP_NAME] = obj.EmpEngFullName;
row[FIELD_ALIAS] = obj.EmpAlias;
//Fields From and To
row[FIELD_FROM] = empCostCenter.EmpCostCenterEffFr;
row[FIELD_TO] = empCostCenter.EmpCostCenterEffTo;
//Field Cost Center
ECostCenter costCenter = new ECostCenter();
costCenter.CostCenterID = detail.CostCenterID;
if (ECostCenter.db.select(dbConn, costCenter))
{
row[FIELD_COST_CENTER] = costCenter.CostCenterDesc;//costCenter.CostCenterCode
}
//Field average percentage
TimeSpan ts;
if ((empCostCenter.EmpCostCenterEffTo <= DateTime.Parse("0001-01-01")) && empCostCenter.EmpCostCenterEffFr < startMonth) // check EmpCostCenterEffTo is null and EmpCostCenterEffFr less than Date-Range-From
{
row[FIELD_FROM] = startMonth;
row[FIELD_TO] = endMonth;
ts = endMonth - startMonth;
}
else if ((empCostCenter.EmpCostCenterEffTo <= DateTime.Parse("0001-01-01"))) // check EmpCostCenterEffTo is null, use Date-Range-To value
{
row[FIELD_TO] = endMonth;
ts = endMonth - empCostCenter.EmpCostCenterEffFr;
}
else if (empCostCenter.EmpCostCenterEffFr < startMonth) // check EmpCostCenterEffFr less than Date-Range-From, use Date-Range-From value
{
row[FIELD_FROM] = startMonth;
ts = empCostCenter.EmpCostCenterEffTo - startMonth;
}
else
{
ts = empCostCenter.EmpCostCenterEffTo - empCostCenter.EmpCostCenterEffFr;
}
double day = ts.Days + 1;
row[FIELD_PERCENTAGE] = detail.EmpCostCenterPercentage * (day / report_range_days);
//add row data to data table
tmpDataTable.Rows.Add(row);
}
}
}
}
GenerateExcelReport(tmpDataTable, exportFileName);
WebUtils.TransmitFile(Response, exportFileName, "AverageCostCenterExportReport_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
Response.End();
}
else
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.addError("Employee not selected");
}
//view = loadData(info, db, Repeater);
}
protected void GenerateCompensationLeaveEntitlement(ArrayList empInfoList, DateTime PeriodFrom, DateTime PeriodTo)
{
foreach (EEmpPersonalInfo empInfo in empInfoList)
{
ArrayList compensationLeaveEntitleList = new ArrayList();
DBFilter attendanceRecordFilter = new DBFilter();
attendanceRecordFilter.add(new Match("EmpID", empInfo.EmpID));
attendanceRecordFilter.add(new Match("AttendanceRecordDate", "<=", PeriodTo));
attendanceRecordFilter.add(new Match("AttendanceRecordDate", ">=", PeriodFrom));
//attendanceRecordFilter.add(new Match("AttendanceRecordActualOvertimeMins", ">", 0));
attendanceRecordFilter.add("AttendanceRecordDate", true);
ArrayList attendanceRecordList = EAttendanceRecord.db.select(dbConn, attendanceRecordFilter);
EEmpPositionInfo currentEmpPosInfo = null;
EAttendancePlan currentAttendancePlan = null;
foreach (EAttendanceRecord attendanceRecord in attendanceRecordList)
{
if (currentEmpPosInfo != null)
{
if (currentEmpPosInfo.EmpPosEffFr <= attendanceRecord.AttendanceRecordDate && (currentEmpPosInfo.EmpPosEffTo >= attendanceRecord.AttendanceRecordDate || currentEmpPosInfo.EmpPosEffTo.Ticks.Equals(0)))
{
currentEmpPosInfo = null;
currentAttendancePlan = null;
}
}
if (currentEmpPosInfo == null)
{
currentEmpPosInfo = AppUtils.GetLastPositionInfo(dbConn, attendanceRecord.AttendanceRecordDate, empInfo.EmpID);
EAttendancePlan attendancePlan = new EAttendancePlan();
attendancePlan.AttendancePlanID = currentEmpPosInfo.AttendancePlanID;
if (EAttendancePlan.db.select(dbConn, attendancePlan))
{
currentAttendancePlan = attendancePlan;
}
}
if (currentAttendancePlan != null)
{
if (currentAttendancePlan.AttendancePlanOTGainAsCompensationLeaveEntitle && currentAttendancePlan.AttendancePlanOTMinsUnit > 0)
{
int actualMins = AppUtils.ApplyRoundingRule(attendanceRecord.AttendanceRecordActualOvertimeMins, currentAttendancePlan.AttendancePlanOTMinsRoundingRule, currentAttendancePlan.AttendancePlanOTMinsUnit);
ECompensationLeaveEntitle compLeaveEntitle = new ECompensationLeaveEntitle();
compLeaveEntitle.EmpID = empInfo.EmpID;
compLeaveEntitle.CompensationLeaveEntitleEffectiveDate = attendanceRecord.AttendanceRecordDate;
compLeaveEntitle.CompensationLeaveEntitleClaimPeriodFrom = attendanceRecord.AttendanceRecordDate;
compLeaveEntitle.CompensationLeaveEntitleClaimPeriodTo = attendanceRecord.AttendanceRecordDate;
compLeaveEntitle.CompensationLeaveEntitleHoursClaim = actualMins / 60.0;
compLeaveEntitle.CompensationLeaveEntitleIsAutoGenerated = true;
compensationLeaveEntitleList.Add(compLeaveEntitle);
}
}
}
SubmitCompensationLeaveEntitlementList(compensationLeaveEntitleList);
//HROne.LeaveCalc.LeaveBalanceCalc leaaveBalCal = new HROne.LeaveCalc.LeaveBalanceCalc(dbConn, empInfo.EmpID);
//leaaveBalCal.RecalculateAfter(PeriodFrom , ELeaveType.COMPENSATION_LEAVE_TYPE(dbConn).LeaveTypeID);
}
}
private static DataRow CreateWorkingSummaryTemplateRow(DatabaseConnection dbConn, DataTable dataTable, Hashtable hierarchyLevelHashTable, EEmpPersonalInfo empInfo, DateTime PeriodFrom, DateTime PeriodTo, EWorkHourPattern currentWorkHourPattern)
{
double restDayCount = 0;
double statutoryHolidayCount = 0;
double restDayTaken = 0;
double statutoryHolidayTaken = 0;
double workHourPatternWorkingDaysCount = 0;
double workHourPatternLunchTimeCount = 0;
double workHourPatternWorkingHourCount = 0;
double totalWorkingDays = 0;
double totalWorkingHours = 0;
double totalLunchMins = 0;
double totalWorkingHoursExpected = 0;
double totalLateMins = 0;
double totalEarlyLeaveMins = 0;
double totalOvertimeMins = 0;
double totalLeaveApplicationDayTaken = 0;
EEmpTermination empTermination = EEmpTermination.GetObjectByEmpID(dbConn, empInfo.EmpID);
bool hasStatutoryHolidayRosterCode = false;
// Check if Roster Code List contains Statutory Holiday Roster Code
ArrayList rosterCodeList = ERosterCode.db.select(dbConn, new DBFilter());
foreach (ERosterCode rosterCode in rosterCodeList)
{
if (rosterCode.RosterCodeType.Equals(ERosterCode.ROSTERTYPE_CODE_STATUTORYHOLIDAY))
{
hasStatutoryHolidayRosterCode = true;
break;
}
}
if (currentWorkHourPattern != null)
{
for (DateTime asOfDate = PeriodFrom; asOfDate <= PeriodTo; asOfDate = asOfDate.AddDays(1))
{
if (EStatutoryHoliday.IsHoliday(dbConn, asOfDate))// && currentWorkHourPattern.WorkHourPatternUseStatutoryHolidayTable)
{
statutoryHolidayCount++;
}
//rest day must be integer and rest within 24 hour
restDayCount += (1 - currentWorkHourPattern.GetDefaultDayUnit(dbConn, asOfDate, false, false));
double workDayUnit = 0;
double workHourUnit = 0;
double LunchTimeUnit = 0;
if (empInfo.EmpDateOfJoin <= asOfDate)
{
workDayUnit = currentWorkHourPattern.GetDefaultDayUnit(dbConn, asOfDate, false, false);
LunchTimeUnit = currentWorkHourPattern.GetDefaultLunch(dbConn, asOfDate);
workHourUnit = currentWorkHourPattern.GetDefaultWorkHour(dbConn, asOfDate);
DBFilter leaveAppFilter = new DBFilter();
leaveAppFilter.add(new Match("EmpID", empInfo.EmpID));
leaveAppFilter.add(new Match("LeaveAppDateFrom", "<=", asOfDate));
leaveAppFilter.add(new Match("LeaveAppDateTo", ">=", asOfDate));
leaveAppFilter.add(new Match("LeaveAppNoPayProcess", false));
ArrayList leaveAppList = ELeaveApplication.db.select(dbConn, leaveAppFilter);
foreach (ELeaveApplication leaveApp in leaveAppList)
{
if (leaveApp.LeaveAppDateFrom.Equals(leaveApp.LeaveAppDateTo))
{
workDayUnit -= leaveApp.LeaveAppDays;
double currentDayDefaultDayUnit = currentWorkHourPattern.GetDefaultDayUnit(dbConn, asOfDate, false, false);
if (currentDayDefaultDayUnit * leaveApp.LeaveAppDays > 0)
{
workHourUnit -= currentWorkHourPattern.GetDefaultWorkHour(dbConn, asOfDate) / currentDayDefaultDayUnit * leaveApp.LeaveAppDays;
}
}
else
{
workDayUnit = 0;
workHourUnit = 0;
LunchTimeUnit = 0;
}
}
if (workDayUnit < 0)
{
workDayUnit = 0;
}
if (workHourUnit < 0)
{
workHourUnit = 0;
}
if (workDayUnit < 1)
{
LunchTimeUnit = 0;
}
if (empTermination != null)
{
if (empTermination.EmpTermLastDate < asOfDate)
{
workDayUnit = 0;
workHourUnit = 0;
LunchTimeUnit = 0;
}
}
}
workHourPatternWorkingDaysCount += workDayUnit;
workHourPatternWorkingHourCount += workHourUnit;
workHourPatternLunchTimeCount += LunchTimeUnit;
}
DBFilter leaveAppTakenFilter = new DBFilter();
leaveAppTakenFilter.add(new Match("EmpID", empInfo.EmpID));
leaveAppTakenFilter.add(new Match("LeaveAppDateFrom", "<=", PeriodTo));
leaveAppTakenFilter.add(new Match("LeaveAppDateTo", ">=", PeriodFrom));
leaveAppTakenFilter.add(new Match("LeaveAppNoPayProcess", false));
ArrayList leaveAppTakenList = ELeaveApplication.db.select(dbConn, leaveAppTakenFilter);
foreach (ELeaveApplication leaveApp in leaveAppTakenList)
{
totalLeaveApplicationDayTaken += leaveApp.LeaveAppDays;
}
}
DBFilter attendanceRecordFilter = new DBFilter();
attendanceRecordFilter.add(new Match("EmpID", empInfo.EmpID));
attendanceRecordFilter.add(new Match("AttendanceRecordDate", ">=", PeriodFrom));
attendanceRecordFilter.add(new Match("AttendanceRecordDate", "<=", PeriodTo));
attendanceRecordFilter.add("AttendanceRecordDate", true);
ArrayList attendanceRecordList = EAttendanceRecord.db.select(dbConn, attendanceRecordFilter);
foreach (EAttendanceRecord attendanceRecord in attendanceRecordList)
{
ERosterCode rosterCode = new ERosterCode();
rosterCode.RosterCodeID = attendanceRecord.RosterCodeID;
if (ERosterCode.db.select(dbConn, rosterCode))
{
if (rosterCode.RosterCodeType.Equals(ERosterCode.ROSTERTYPE_CODE_RESTDAY))
{
restDayTaken++;
}
if (rosterCode.RosterCodeType.Equals(ERosterCode.ROSTERTYPE_CODE_STATUTORYHOLIDAY))
{
statutoryHolidayTaken++;
}
}
double workingHours = attendanceRecord.TotalWorkingHourTimeSpan(dbConn).TotalHours;
if (workingHours <= 0)
{
workingHours = attendanceRecord.AttendanceRecordActualWorkingHour + Convert.ToDouble(attendanceRecord.AttendanceRecordActualEarlyLeaveMins - attendanceRecord.AttendanceRecordActualLateMins + attendanceRecord.AttendanceRecordActualOvertimeMins) / 60.0;
}
totalWorkingDays += attendanceRecord.AttendanceRecordActualWorkingDay;
totalWorkingHours += workingHours;
totalWorkingHoursExpected += attendanceRecord.AttendanceRecordActualWorkingHour;
totalLateMins += attendanceRecord.AttendanceRecordActualLateMins;
totalEarlyLeaveMins += attendanceRecord.AttendanceRecordActualEarlyLeaveMins;
totalOvertimeMins += attendanceRecord.AttendanceRecordActualOvertimeMins;
totalLunchMins += attendanceRecord.AttendanceRecordActualLunchTimeMins;
}
if (totalWorkingDays <= 0 && totalWorkingHours <= 0 && totalLunchMins <= 0 && empInfo.EmpDateOfJoin <= PeriodFrom)
{
totalWorkingDays = workHourPatternWorkingDaysCount;
totalWorkingHours = workHourPatternWorkingHourCount;
totalLunchMins = workHourPatternLunchTimeCount * 60;
}
DataRow row = dataTable.NewRow();
row[FIELD_EMP_NO] = empInfo.EmpNo;
row["EnglishName"] = empInfo.EmpEngFullName;
row["ChineseName"] = empInfo.EmpChiFullName;
DBFilter empPosFilter = new DBFilter();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, PeriodTo, empInfo.EmpID);
if (empPos != null)
{
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row["Company"] = company.CompanyCode;
}
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
if (hierarchyLevel != null)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementCode;
}
}
}
}
row[FIELD_AS_OF_DATE] = PeriodTo;
row[FIELD_REST_DAY_ENTITLED] = restDayCount - statutoryHolidayCount;
if (hasStatutoryHolidayRosterCode)
{
row[FIELD_REST_DAY_TAKEN] = restDayTaken;
}
else
{
row[FIELD_REST_DAY_TAKEN] = restDayTaken > statutoryHolidayCount ? restDayTaken - statutoryHolidayCount : 0;
}
row[FIELD_TOTAL_WORKING_DAYS] = totalWorkingDays;
row[FIELD_TOTAL_WORKING_HOURS] = totalWorkingHours;
row[FIELD_TOTAL_LUNCH_HOURS] = totalLunchMins / 60.0;
row[FIELD_EXTRA_TOTAL_STATUTORY_HOLIDAY] = statutoryHolidayCount;
row[FIELD_EXTRA_TOTAL_LEAVE_APPLICATION_TAKEN] = totalLeaveApplicationDayTaken;
row[FIELD_EXTRA_TOTAL_WORKING_HOURS_EXPECTED] = totalWorkingHoursExpected;
row[FIELD_EXTRA_TOTAL_LATE_MINS] = totalLateMins;
row[FIELD_EXTRA_TOTAL_EARLYLEAVE_MINS] = totalEarlyLeaveMins;
row[FIELD_EXTRA_TOTAL_OVERTIME_MINS] = totalOvertimeMins;
dataTable.Rows.Add(row);
return(row);
}
private void GenerateReport(ArrayList empList, DateTime PeriodFrom, DateTime PeriodTo, string SortBy)
{
DataTable dataTable = new DataTable();
dataTable.Columns.Add("EmpID", typeof(int));
dataTable.Columns.Add("EmpNo", typeof(string));
dataTable.Columns.Add("EmpName", typeof(string));
dataTable.Columns.Add("EmpAlias", typeof(string));
dataTable.Columns.Add("Company", typeof(string));
DBFilter hierarchyLevelFilter = new DBFilter();
Hashtable hierarchyLevelHashTable = new Hashtable();
hierarchyLevelFilter.add("HLevelSeqNo", true);
ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
dataTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
}
dataTable.Columns.Add("Position", typeof(string));
dataTable.Columns.Add("TrainingSeminarID", typeof(int));
dataTable.Columns.Add("TrainingCourseName", typeof(string));
dataTable.Columns.Add("TrainingSeminarDesc", typeof(string));
dataTable.Columns.Add("TrainingSeminarDateFrom", typeof(DateTime));
dataTable.Columns.Add("TrainingSeminarDateTo", typeof(DateTime));
dataTable.Columns.Add("TrainingSeminarDuration", typeof(double));
dataTable.Columns.Add("TrainingSeminarDurationUnit", typeof(string));
dataTable.Columns.Add("TrainingSeminarTrainer", typeof(string));
foreach (EEmpPersonalInfo empInfo in empList)
{
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, AppUtils.ServerDateTime().Date, empInfo.EmpID);
ECompany company = new ECompany();
EPosition position = new EPosition();
ArrayList empHierarchyList = new ArrayList();
if (empPos != null)
{
company.CompanyID = empPos.CompanyID;
ECompany.db.select(dbConn, company);
//row["Company"] = company.CompanyCode;
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
//foreach (EEmpHierarchy empHierarchy in empHierarchyList)
//{
// EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
// if (hierarchyLevel != null)
// {
// EHierarchyElement hierarchyElement = new EHierarchyElement();
// hierarchyElement.HElementID = empHierarchy.HElementID;
// if (EHierarchyElement.db.select(dbConn, hierarchyElement))
// row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
// }
//}
position.PositionID = empPos.PositionID;
EPosition.db.select(dbConn, position);
}
DBFilter empTrainingSeminar = new DBFilter();
empTrainingSeminar.add(new Match("EmpID", empInfo.EmpID));
OR orTrainingCourse = null;
foreach (ListItem item in TrainingCourseList.Items)
{
if (item.Selected)
{
if (orTrainingCourse == null)
{
orTrainingCourse = new OR();
}
orTrainingCourse.add(new Match("te.TrainingCourseID", item.Value));
}
}
DBFilter trainingSeminarFilter = new DBFilter();
if (!PeriodFrom.Ticks.Equals(0))
{
trainingSeminarFilter.add(new Match("TrainingSeminarDateFrom", ">=", PeriodFrom));
}
if (!PeriodTo.Ticks.Equals(0))
{
trainingSeminarFilter.add(new Match("TrainingSeminarDateTo", "<=", PeriodTo));
}
if (orTrainingCourse != null)
{
trainingSeminarFilter.add(orTrainingCourse);
}
empTrainingSeminar.add(new IN("TrainingSeminarID", "Select TrainingSeminarID from " + ETrainingSeminar.db.dbclass.tableName + " te", trainingSeminarFilter));
ArrayList empTrainingSeminarList = EEmpTrainingEnroll.db.select(dbConn, empTrainingSeminar);
foreach (EEmpTrainingEnroll empTrainingEnroll in empTrainingSeminarList)
{
ETrainingSeminar trainingSeminar = new ETrainingSeminar();
trainingSeminar.TrainingSeminarID = empTrainingEnroll.TrainingSeminarID;
if (ETrainingSeminar.db.select(dbConn, trainingSeminar))
{
DataRow row = dataTable.NewRow();
row["EmpID"] = empInfo.EmpID;
row["EmpNo"] = empInfo.EmpNo;
row["EmpName"] = empInfo.EmpEngFullName;
row["EmpAlias"] = empInfo.EmpAlias;
row["Company"] = company.CompanyCode;
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
if (hierarchyLevel != null)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
}
}
}
row["Position"] = position.PositionDesc;
row["TrainingSeminarID"] = trainingSeminar.TrainingSeminarID;
ETrainingCourse trainingCourse = new ETrainingCourse();
trainingCourse.TrainingCourseID = trainingSeminar.TrainingCourseID;
if (ETrainingCourse.db.select(dbConn, trainingCourse))
{
row["TrainingCourseName"] = trainingCourse.TrainingCourseName;
}
else
{
row["TrainingCourseName"] = string.Empty;
}
row["TrainingSeminarDesc"] = trainingSeminar.TrainingSeminarDesc == null ? string.Empty : trainingSeminar.TrainingSeminarDesc;
row["TrainingSeminarDateFrom"] = trainingSeminar.TrainingSeminarDateFrom;
row["TrainingSeminarDateTo"] = trainingSeminar.TrainingSeminarDateTo;
row["TrainingSeminarDuration"] = trainingSeminar.TrainingSeminarDuration;
if (trainingSeminar.TrainingSeminarDurationUnit.Equals("H"))
{
row["TrainingSeminarDurationUnit"] = "Hour(s)";
}
else
{
row["TrainingSeminarDurationUnit"] = trainingSeminar.TrainingSeminarDurationUnit;
}
row["TrainingSeminarTrainer"] = trainingSeminar.TrainingSeminarTrainer;
dataTable.Rows.Add(row);
}
}
}
}
//org.in2bits.MyXls.XlsDocument document = new org.in2bits.MyXls.XlsDocument();
//org.in2bits.MyXls.Worksheet worksheet = document.Workbook.Worksheets.Add("training report");
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFSheet worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("training report");
NPOI.HSSF.UserModel.HSSFFont boldFont = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
NPOI.HSSF.UserModel.HSSFCellStyle reportHeaderStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
reportHeaderStyle.SetFont(boldFont);
NPOI.HSSF.UserModel.HSSFCellStyle columnHeaderStyleCenter = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
columnHeaderStyleCenter.SetFont(boldFont);
columnHeaderStyleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
//NPOI.HSSF.UserModel.HSSFCellStyle numericStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
//numericStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.00");
int rowCount = 0;
//worksheet.Cells.Add(rowCount, (ushort)1, "Training Report").Font.Bold = true;
NPOI.HSSF.UserModel.HSSFCell reportHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)worksheet.CreateRow(rowCount).CreateCell(0);
reportHeaderCell.SetCellValue("Training Report");
reportHeaderCell.CellStyle = reportHeaderStyle;
rowCount++;
if (!PeriodFrom.Ticks.Equals(0) && !PeriodTo.Ticks.Equals(0))
{
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy"));
worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Period: " + PeriodFrom.ToString("dd/MM/yyyy") + " - " + PeriodTo.ToString("dd/MM/yyyy"));
rowCount++;
}
else if (!PeriodTo.Ticks.Equals(0))
{
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Up to: " + PeriodTo.ToString("dd/MM/yyyy"));
worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("Up to: " + PeriodTo.ToString("dd/MM/yyyy"));
rowCount++;
}
else if (!PeriodFrom.Ticks.Equals(0))
{
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "From: " + PeriodFrom.ToString("dd/MM/yyyy"));
worksheet.CreateRow(rowCount).CreateCell(0).SetCellValue("From: " + PeriodFrom.ToString("dd/MM/yyyy"));
rowCount++;
}
if (SortBy.Equals("Date", StringComparison.CurrentCultureIgnoreCase))
{
DataView dataView = new DataView(dataTable);
dataView.Sort = "TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName, EmpNo";
DataTable sortedTable = dataView.ToTable();
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Training Date").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)2, "Course Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)3, "Description").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true;
//worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
//worksheet.Cells.Add(rowCount, (ushort)5, string.Empty);
//worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true;
//worksheet.Cells.Merge(rowCount, rowCount, 4, 5);
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
columnHeaderCell.SetCellValue("Training Date");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
columnHeaderCell.SetCellValue("Course Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
columnHeaderCell.SetCellValue("Description");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
columnHeaderCell.SetCellValue("Duration");
columnHeaderCell.CellStyle = columnHeaderStyleCenter;
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4));
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5);
columnHeaderCell.SetCellValue("Trainer");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6);
columnHeaderCell.SetCellValue("Employee No.");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7);
columnHeaderCell.SetCellValue("Employee Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8);
columnHeaderCell.SetCellValue("Alias");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9);
columnHeaderCell.SetCellValue("Position");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10);
columnHeaderCell.SetCellValue("Company");
columnHeaderCell.CellStyle = reportHeaderStyle;
int colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
//worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
columnHeaderCell.CellStyle = reportHeaderStyle;
}
int currentTrainingSeminarID = 0;
foreach (DataRow row in sortedTable.Rows)
{
rowCount++;
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
if (!currentTrainingSeminarID.Equals((int)row["TrainingSeminarID"]))
{
if (row["TrainingSeminarDateFrom"] != DBNull.Value)
{
DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]);
if (trainingFrom.Equals(trainingTo))
{
//worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy"));
detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
}
else
{
//worksheet.Cells.Add(rowCount, (ushort)1, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
detailRow.CreateCell(0).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
}
}
}
currentTrainingSeminarID = ((int)row["TrainingSeminarID"]);
//worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingCourseName"]);
//worksheet.Cells.Add(rowCount, (ushort)3, row["TrainingSeminarDesc"]);
//worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]);
//worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]);
//worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]);
//worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]);
//worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]);
//worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]);
//worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]);
//worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]);
detailRow.CreateCell(1).SetCellValue(row["TrainingCourseName"].ToString());
detailRow.CreateCell(2).SetCellValue(row["TrainingSeminarDesc"].ToString());
detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]);
//detailRow.GetCell(3).CellStyle = numericStyle;
detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString());
detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString());
detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString());
detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString());
detailRow.CreateCell(9).SetCellValue(row["Position"].ToString());
detailRow.CreateCell(10).SetCellValue(row["Company"].ToString());
colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
if (row[hlevel.HLevelDesc] != DBNull.Value)
{
//worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
}
}
}
}
else if (SortBy.Equals("Position", StringComparison.CurrentCultureIgnoreCase))
{
DataView dataView = new DataView(dataTable);
dataView.Sort = "Position, EmpNo, TrainingSeminarDateFrom, TrainingSeminarDateTo, TrainingCourseName ";
DataTable sortedTable = dataView.ToTable();
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Position").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)2, "Employee No.").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)3, "Employee Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)4, "Alias").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)5, "Company").Font.Bold = true;
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
columnHeaderCell.SetCellValue("Position");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
columnHeaderCell.SetCellValue("Employee No.");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
columnHeaderCell.SetCellValue("Employee Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
columnHeaderCell.SetCellValue("Alias");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(4);
columnHeaderCell.SetCellValue("Company");
columnHeaderCell.CellStyle = reportHeaderStyle;
int colCount = 4;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
//worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
columnHeaderCell.CellStyle = reportHeaderStyle;
}
//worksheet.Cells.Add(rowCount, (ushort)colCount + 1, "Course Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 2, "Description").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 3, "Training Date").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 4, "Duration").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 5, string.Empty);
//worksheet.Cells.Merge(rowCount, rowCount, colCount + 4, colCount + 5);
//worksheet.Rows[rowCount].GetCell((ushort)(colCount + 4)).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
//worksheet.Cells.Add(rowCount, (ushort)colCount + 6, "Trainer").Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 1);
columnHeaderCell.SetCellValue("Course Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 2);
columnHeaderCell.SetCellValue("Description");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 3);
columnHeaderCell.SetCellValue("Training Date");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 4);
columnHeaderCell.SetCellValue("Duration");
columnHeaderCell.CellStyle = columnHeaderStyleCenter;
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, colCount + 4, colCount + 5));
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount + 6);
columnHeaderCell.SetCellValue("Trainer");
columnHeaderCell.CellStyle = reportHeaderStyle;
int currentEmpID = 0;
foreach (DataRow row in sortedTable.Rows)
{
rowCount++;
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
if (!currentEmpID.Equals((int)row["EmpID"]))
{
//worksheet.Cells.Add(rowCount, (ushort)1, row["Position"]);
//worksheet.Cells.Add(rowCount, (ushort)2, row["EmpNo"]);
//worksheet.Cells.Add(rowCount, (ushort)3, row["EmpName"]);
//worksheet.Cells.Add(rowCount, (ushort)4, row["EmpAlias"]);
//worksheet.Cells.Add(rowCount, (ushort)5, row["Company"]);
detailRow.CreateCell(0).SetCellValue(row["Position"].ToString());
detailRow.CreateCell(1).SetCellValue(row["EmpNo"].ToString());
detailRow.CreateCell(2).SetCellValue(row["EmpName"].ToString());
detailRow.CreateCell(3).SetCellValue(row["EmpAlias"].ToString());
detailRow.CreateCell(4).SetCellValue(row["Company"].ToString());
colCount = 4;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
if (row[hlevel.HLevelDesc] != DBNull.Value)
{
//worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
}
}
}
currentEmpID = ((int)row["EmpID"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 1, row["TrainingCourseName"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 2, row["TrainingSeminarDesc"]);
detailRow.CreateCell(colCount + 1).SetCellValue(row["TrainingCourseName"].ToString());
detailRow.CreateCell(colCount + 2).SetCellValue(row["TrainingSeminarDesc"].ToString());
if (row["TrainingSeminarDateFrom"] != DBNull.Value)
{
DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]);
if (trainingFrom.Equals(trainingTo))
{
//worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy"));
detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
}
else
{
//worksheet.Cells.Add(rowCount, (ushort)colCount + 3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
detailRow.CreateCell(colCount + 3).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
}
}
//worksheet.Cells.Add(rowCount, (ushort)colCount + 4, row["TrainingSeminarDuration"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 5, row["TrainingSeminarDurationUnit"]);
//worksheet.Cells.Add(rowCount, (ushort)colCount + 6, row["TrainingSeminarTrainer"]);
detailRow.CreateCell(colCount + 4).SetCellValue((double)row["TrainingSeminarDuration"]);
//detailRow.GetCell(colCount + 4).CellStyle = numericStyle;
detailRow.CreateCell(colCount + 5).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
detailRow.CreateCell(colCount + 6).SetCellValue(row["TrainingSeminarTrainer"].ToString());
}
}
else if (SortBy.Equals("Course", StringComparison.CurrentCultureIgnoreCase))
{
DataView dataView = new DataView(dataTable);
dataView.Sort = "TrainingCourseName, TrainingSeminarDateFrom, TrainingSeminarDateTo, EmpNo";
DataTable sortedTable = dataView.ToTable();
rowCount++;
//worksheet.Cells.Add(rowCount, (ushort)1, "Course Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)2, "Description").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)3, "Training Date").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)4, "Duration").Font.Bold = true;
//worksheet.Rows[rowCount].GetCell(4).HorizontalAlignment = org.in2bits.MyXls.HorizontalAlignments.Centered;
//worksheet.Cells.Add(rowCount, (ushort)5, string.Empty);
//worksheet.Cells.Add(rowCount, (ushort)6, "Trainer").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)7, "Employee No.").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)8, "Employee Name").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)9, "Alias").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)10, "Position").Font.Bold = true;
//worksheet.Cells.Add(rowCount, (ushort)11, "Company").Font.Bold = true;
//worksheet.Cells.Merge(rowCount, rowCount, 4, 5);
NPOI.HSSF.UserModel.HSSFRow columnHeaderRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
NPOI.HSSF.UserModel.HSSFCell columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(0);
columnHeaderCell.SetCellValue("Course Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(1);
columnHeaderCell.SetCellValue("Description");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(2);
columnHeaderCell.SetCellValue("Training Date");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(3);
columnHeaderCell.SetCellValue("Duration");
columnHeaderCell.CellStyle = columnHeaderStyleCenter;
worksheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowCount, rowCount, 3, 4));
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(5);
columnHeaderCell.SetCellValue("Trainer");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(6);
columnHeaderCell.SetCellValue("Employee No.");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(7);
columnHeaderCell.SetCellValue("Employee Name");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(8);
columnHeaderCell.SetCellValue("Alias");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(9);
columnHeaderCell.SetCellValue("Position");
columnHeaderCell.CellStyle = reportHeaderStyle;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(10);
columnHeaderCell.SetCellValue("Company");
columnHeaderCell.CellStyle = reportHeaderStyle;
int colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
//worksheet.Cells.Add(rowCount, colCount, hlevel.HLevelDesc).Font.Bold = true;
columnHeaderCell = (NPOI.HSSF.UserModel.HSSFCell)columnHeaderRow.CreateCell(colCount);
columnHeaderCell.SetCellValue(hlevel.HLevelDesc);
columnHeaderCell.CellStyle = reportHeaderStyle;
}
foreach (DataRow row in sortedTable.Rows)
{
rowCount++;
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)worksheet.CreateRow(rowCount);
//worksheet.Cells.Add(rowCount, (ushort)1, row["TrainingCourseName"]);
//worksheet.Cells.Add(rowCount, (ushort)2, row["TrainingSeminarDesc"]);
detailRow.CreateCell(0).SetCellValue(row["TrainingCourseName"].ToString());
detailRow.CreateCell(1).SetCellValue(row["TrainingSeminarDesc"].ToString());
if (row["TrainingSeminarDateFrom"] != DBNull.Value)
{
DateTime trainingFrom = ((DateTime)row["TrainingSeminarDateFrom"]);
DateTime trainingTo = ((DateTime)row["TrainingSeminarDateTo"]);
if (trainingFrom.Equals(trainingTo))
{
//worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy"));
detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy"));
}
else
{
//worksheet.Cells.Add(rowCount, (ushort)3, trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
detailRow.CreateCell(2).SetCellValue(trainingFrom.ToString("dd/MM/yyyy") + " - " + trainingTo.ToString("dd/MM/yyyy"));
}
}
//worksheet.Cells.Add(rowCount, (ushort)4, row["TrainingSeminarDuration"]);
//worksheet.Cells.Add(rowCount, (ushort)5, row["TrainingSeminarDurationUnit"]);
//worksheet.Cells.Add(rowCount, (ushort)6, row["TrainingSeminarTrainer"]);
//worksheet.Cells.Add(rowCount, (ushort)7, row["EmpNo"]);
//worksheet.Cells.Add(rowCount, (ushort)8, row["EmpName"]);
//worksheet.Cells.Add(rowCount, (ushort)9, row["EmpAlias"]);
//worksheet.Cells.Add(rowCount, (ushort)10, row["Position"]);
//worksheet.Cells.Add(rowCount, (ushort)11, row["Company"]);
detailRow.CreateCell(3).SetCellValue((double)row["TrainingSeminarDuration"]);
//detailRow.GetCell(3).CellStyle = numericStyle;
detailRow.CreateCell(4).SetCellValue(row["TrainingSeminarDurationUnit"].ToString());
detailRow.CreateCell(5).SetCellValue(row["TrainingSeminarTrainer"].ToString());
detailRow.CreateCell(6).SetCellValue(row["EmpNo"].ToString());
detailRow.CreateCell(7).SetCellValue(row["EmpName"].ToString());
detailRow.CreateCell(8).SetCellValue(row["EmpAlias"].ToString());
detailRow.CreateCell(9).SetCellValue(row["Position"].ToString());
detailRow.CreateCell(10).SetCellValue(row["Company"].ToString());
colCount = 10;
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
colCount++;
if (row[hlevel.HLevelDesc] != DBNull.Value)
{
//worksheet.Cells.Add(rowCount, colCount, row[hlevel.HLevelDesc]);
detailRow.CreateCell(colCount).SetCellValue(row[hlevel.HLevelDesc].ToString());
}
}
}
}
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
//document.FileName = exportFileName;
//document.Save();
System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
workbook.Write(file);
file.Close();
string filename = "TrainingReport_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls";
WebUtils.TransmitFile(Response, exportFileName, filename, true);
return;
}
protected void Save_Click(object sender, EventArgs e)
{
EEmpPositionInfo newEmpPos = new EEmpPositionInfo();
Hashtable values = new Hashtable();
empPosBinding.toValues(values);
PageErrors errors = PageErrors.getErrors(empPosDB, Page);
errors.clear();
empPosDB.validate(errors, values);
if (!errors.isEmpty())
{
return;
}
empPosDB.parse(values, newEmpPos);
int OldEmpPosID = newEmpPos.EmpPosID;
EEmpPersonalInfo newEmpInfo = new EEmpPersonalInfo();
values = new Hashtable();
empBinding.toValues(values);
errors = PageErrors.getErrors(empDB, Page);
errors.clear();
empDB.validate(errors, values);
if (!errors.isEmpty())
{
return;
}
empDB.parse(values, newEmpInfo);
int OldEmpID = newEmpInfo.EmpID;
newEmpInfo.EmpID = 0;
newEmpInfo.EmpNo = newEmpInfo.EmpNo.ToUpper();
if (!AppUtils.checkDuplicate(dbConn, empDB, newEmpInfo, errors, "EmpNo"))
{
return;
}
DBFilter empTermFilter = new DBFilter();
empTermFilter.add(new Match("EmpID", OldEmpID));
empTermFilter.add(WebUtils.AddRankFilter(Session, "EmpID", true));
ArrayList empTermList = EEmpTermination.db.select(dbConn, empTermFilter);
if (empTermList.Count == 0)
{
errors.addError(HROne.Translation.PageErrorMessage.ERROR_TERMINATION_RECORD_NOT_FOUND);
return;
}
EEmpTermination empTerm = (EEmpTermination)empTermList[0];
if (!empTerm.EmpTermIsTransferCompany)
{
errors.addError(HROne.Translation.PageErrorMessage.ERROR_EMP_NOT_TRANSFERABLE);
return;
}
if (empTerm.NewEmpID >= 0)
{
EEmpPersonalInfo NextEmpInfo = new EEmpPersonalInfo();
NextEmpInfo.EmpID = empTerm.NewEmpID;
if (EEmpPersonalInfo.db.select(dbConn, NextEmpInfo))
{
errors.addError(string.Format(HROne.Translation.PageErrorMessage.ERROR_EMP_TRANSFERRED_BEFORE, new string[] { NextEmpInfo.EmpNo }));
return;
}
}
empTerm.NewEmpID = CopyEmployeeDetail(OldEmpID);
EEmpTermination.db.update(dbConn, empTerm);
newEmpInfo.EmpID = empTerm.NewEmpID;
newEmpInfo.EmpStatus = "A";
newEmpInfo.EmpDateOfJoin = empTerm.EmpTermLastDate.AddDays(1);
newEmpInfo.PreviousEmpID = OldEmpID;
EEmpPersonalInfo.db.update(dbConn, newEmpInfo);
EEmpPositionInfo oldEmpPos = new EEmpPositionInfo();
oldEmpPos.EmpPosID = OldEmpPosID;
if (EEmpPositionInfo.db.select(dbConn, oldEmpPos))
{
//oldEmpPos.EmpPosEffTo = empTerm.EmpTermLastDate;
EEmpPositionInfo.db.update(dbConn, oldEmpPos);
}
newEmpPos.EmpPosEffFr = empTerm.EmpTermLastDate.AddDays(1);
newEmpPos.EmpID = newEmpInfo.EmpID;
newEmpPos.EmpPosID = 0;
EEmpPositionInfo.db.insert(dbConn, newEmpPos);
ArrayList list = new ArrayList();
foreach (RepeaterItem item in HierarchyLevel.Items)
{
DropDownList d = (DropDownList)item.FindControl("HElementID");
int HLevelID = Int32.Parse(d.Attributes["HLevelID"]);
if (d.SelectedIndex > 0)
{
EEmpHierarchy h = new EEmpHierarchy();
h.EmpID = newEmpInfo.EmpID;
h.EmpPosID = newEmpPos.EmpPosID;
h.HLevelID = HLevelID;
h.HElementID = Int32.Parse(d.SelectedValue);
list.Add(h);
}
}
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpID", newEmpInfo.EmpID));
empHierarchyFilter.add(new Match("EmpPosID", newEmpPos.EmpPosID));
EEmpHierarchy.db.delete(dbConn, empHierarchyFilter);
foreach (EEmpHierarchy h in list)
{
EEmpHierarchy.db.insert(dbConn, h);
}
HROne.LeaveCalc.LeaveBalanceCalc bal = new HROne.LeaveCalc.LeaveBalanceCalc(dbConn, OldEmpID, empTerm.EmpTermLastDate);
ArrayList balList = bal.getCurrentBalanceList();
foreach (ELeaveBalance leaveBalance in balList)
{
ELeaveBalanceAdjustment leaveAdj = new ELeaveBalanceAdjustment();
leaveAdj.EmpID = newEmpInfo.EmpID;
leaveAdj.LeaveBalAdjDate = empTerm.EmpTermLastDate;
leaveAdj.LeaveBalAdjType = ELeaveBalanceAdjustment.ADJUST_TYPE_RESET_BALANCE;
leaveAdj.LeaveTypeID = leaveBalance.LeaveTypeID;
leaveAdj.LeaveBalAdjValue = leaveBalance.getBalance();
leaveAdj.LeaveBalAdjRemark = "System adjustment by transfer company";
ELeaveBalanceAdjustment.db.insert(dbConn, leaveAdj);
}
HROne.Common.WebUtility.RedirectURLwithEncryptedQueryString(Response, Session, "Emp_PositionInfo_View.aspx?EmpPosID=" + newEmpPos.EmpPosID + "&EmpID=" + newEmpInfo.EmpID);
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
const string FIELD_COMPANY = "Company";
const string FIELD_POSITION = "Position";
const string FIELD_PAYROLLGROUP = "Payroll Group";
const string FIELD_EMPNO = "EmployeeID";
const string FIELD_EMPENGFULLNAME = "English Name";
const string FIELD_CHINESENAME = "¤¤¤å©m¦W";
const string FIELD_HKID = @"HKID/Passport";
const string FIELD_PERIODFROM = "From";
const string FIELD_PERIODTO = "To";
const string FIELD_WAGESWORK = "Wages Paid";
const string FIELD_WORKHOURTOTAL = "Total Working Hours";
const string FIELD_RESTDAYTOTAL = "No. of Rest Day";
const string FIELD_STATUTORYHOLIDAYTOTAL = "No. of SH";
const string FIELD_FULLPAIDLEAVETOTAL = "No. of Full Paid Leave";
const string FIELD_NONFULLPAIDLEAVETOTAL = "Non-Full Paid Leave";
ArrayList list = new ArrayList();
foreach (RepeaterItem i in Repeater.Items)
{
CheckBox cb = (CheckBox)i.FindControl("ItemSelect");
if (cb.Checked)
{
EEmpPersonalInfo o = (EEmpPersonalInfo)EEmpPersonalInfo.db.createObject();
WebFormUtils.GetKeys(EEmpPersonalInfo.db, o, cb);
list.Add(o);
}
}
ArrayList payPeriodList = Payroll_ConfirmedPeriod_List1.GetSelectedBaseObjectList();
if (list.Count > 0 && payPeriodList.Count > 0)
{
//const string PAYMENTCODE_PREFIX = "[StatutoryMinimumWageSummary] ";
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
//System.IO.File.Copy(Server.MapPath("~/template/HistoryList_Template.xls"), exportFileName, true);
HROne.Export.ExcelExport export = new HROne.Export.ExcelExport(exportFileName);
DataSet dataSet = new DataSet(); //export.GetDataSet();
DataTable dataTable = new DataTable("Payroll$");
dataSet.Tables.Add(dataTable);
dataTable.Columns.Add(FIELD_COMPANY, typeof(string));
DBFilter hierarchyLevelFilter = new DBFilter();
Hashtable hierarchyLevelHashTable = new Hashtable();
hierarchyLevelFilter.add("HLevelSeqNo", true);
ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
dataTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
}
dataTable.Columns.Add(FIELD_POSITION, typeof(string));
dataTable.Columns.Add(FIELD_PAYROLLGROUP, typeof(string));
dataTable.Columns.Add(FIELD_EMPNO, typeof(string));
dataTable.Columns.Add(FIELD_EMPENGFULLNAME, typeof(string));
dataTable.Columns.Add(FIELD_CHINESENAME, typeof(string));
dataTable.Columns.Add(FIELD_HKID, typeof(string));
dataTable.Columns.Add(FIELD_PERIODFROM, typeof(DateTime));
dataTable.Columns.Add(FIELD_PERIODTO, typeof(DateTime));
dataTable.Columns.Add(FIELD_WAGESWORK, typeof(double));
dataTable.Columns.Add(FIELD_WORKHOURTOTAL, typeof(double));
dataTable.Columns.Add(FIELD_RESTDAYTOTAL, typeof(double));
dataTable.Columns.Add(FIELD_STATUTORYHOLIDAYTOTAL, typeof(double));
dataTable.Columns.Add(FIELD_FULLPAIDLEAVETOTAL, typeof(double));
dataTable.Columns.Add(FIELD_NONFULLPAIDLEAVETOTAL, typeof(double));
int firstSummaryColumnPos = dataTable.Columns.Count;
int firstDetailColumnPos = dataTable.Columns.Count;
foreach (EPayrollPeriod payPeriod in payPeriodList)
{
if (EPayrollPeriod.db.select(dbConn, payPeriod))
{
EPayrollGroup payrollGroup = new EPayrollGroup();
payrollGroup.PayGroupID = payPeriod.PayGroupID;
EPayrollGroup.db.select(dbConn, payrollGroup);
foreach (EEmpPersonalInfo empInfo in list)
{
EEmpPersonalInfo.db.select(dbConn, empInfo);
EEmpTermination empTerm = EEmpTermination.GetObjectByEmpID(dbConn, empInfo.EmpID);
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(new Match("ep.EmpID", empInfo.EmpID));
empPayrollFilter.add(new Match("ep.PayPeriodID", payPeriod.PayPeriodID));
if (PayrollStatus.SelectedValue.Equals("T"))
{
empPayrollFilter.add(new Match("ep.EmpPayStatus", "=", "T"));
}
else
{
empPayrollFilter.add(new Match("ep.EmpPayStatus", "<>", "T"));
}
DataRow row = dataTable.NewRow();
row[FIELD_EMPNO] = empInfo.EmpNo;
row[FIELD_EMPENGFULLNAME] = empInfo.EmpEngFullName;
row[FIELD_CHINESENAME] = empInfo.EmpChiFullName;
row[FIELD_HKID] = empInfo.EmpHKID;
row[FIELD_PERIODFROM] = payPeriod.PayPeriodFr;
row[FIELD_PERIODTO] = payPeriod.PayPeriodTo;
DBFilter empPosFilter = new DBFilter();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID);
if (empPos != null)
{
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row[FIELD_COMPANY] = company.CompanyCode;
}
EPosition position = new EPosition();
position.PositionID = empPos.PositionID;
if (EPosition.db.select(dbConn, position))
{
row[FIELD_POSITION] = position.PositionDesc;
}
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPos.EmpPosID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyLevel hierarchyLevel = (EHierarchyLevel)hierarchyLevelHashTable[empHierarchy.HLevelID];
if (hierarchyLevel != null)
{
EHierarchyElement hierarchyElement = new EHierarchyElement();
hierarchyElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hierarchyElement))
{
row[hierarchyLevel.HLevelDesc] = hierarchyElement.HElementDesc;
}
}
}
EPayrollGroup curentPayGroup = new EPayrollGroup();
curentPayGroup.PayGroupID = empPos.PayGroupID;
if (EPayrollGroup.db.select(dbConn, curentPayGroup))
{
row[FIELD_PAYROLLGROUP] = curentPayGroup.PayGroupDesc;
}
}
double netAmount = 0, releventIncome = 0, nonRelevantIncome = 0, taxableAmount = 0, nonTaxableAmount = 0;
double mcER = 0, mcEE = 0;
double vcER = 0, vcEE = 0;
double pFundER = 0, pFundEE = 0;
double wagesByWork = 0;
double wagesByRest = 0;
double fullPaidLeaveDays = 0;
double nonFullPaidLeaveDays = 0;
DBFilter paymentRecordFilter = new DBFilter();
paymentRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter));
paymentRecordFilter.add(new Match("PayRecStatus", "A"));
ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, paymentRecordFilter);
foreach (EPaymentRecord paymentRecord in paymentRecords)
{
EPaymentCode payCode = new EPaymentCode();
payCode.PaymentCodeID = paymentRecord.PaymentCodeID;
EPaymentCode.db.select(dbConn, payCode);
// Always Use Payment Code Description for grouping payment code with same description
//string fieldName = PAYMENTCODE_PREFIX + payCode.PaymentCodeDesc;
//if (dataTable.Columns[fieldName] == null)
// dataTable.Columns.Add(new DataColumn(fieldName, typeof(double)));
//if (row[fieldName] == null || row[fieldName] == DBNull.Value)
// row[fieldName] = 0;
//row[fieldName] = (double)row[fieldName] + paymentRecord.PayRecActAmount;
netAmount += paymentRecord.PayRecActAmount;
if (payCode.PaymentCodeIsMPF)
{
releventIncome += paymentRecord.PayRecActAmount;
}
else
{
nonRelevantIncome += paymentRecord.PayRecActAmount;
}
DBFilter taxPaymentMapFilter = new DBFilter();
taxPaymentMapFilter.add(new Match("PaymentCodeID", paymentRecord.PaymentCodeID));
if (ETaxPaymentMap.db.count(dbConn, taxPaymentMapFilter) > 0)
{
taxableAmount += paymentRecord.PayRecActAmount;
}
else
{
nonTaxableAmount += paymentRecord.PayRecActAmount;
}
if (payCode.PaymentCodeIsWages)
{
if (paymentRecord.PayRecIsRestDayPayment)
{
wagesByRest += paymentRecord.PayRecActAmount;
}
else
{
wagesByWork += paymentRecord.PayRecActAmount;
}
}
}
DBFilter mpfRecordFilter = new DBFilter();
mpfRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter));
ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter);
foreach (EMPFRecord mpfRecord in mpfRecords)
{
vcER += mpfRecord.MPFRecActVCER;
mcER += +mpfRecord.MPFRecActMCER;
vcEE += mpfRecord.MPFRecActVCEE;
mcEE += mpfRecord.MPFRecActMCEE;
}
ArrayList orsoRecords = EORSORecord.db.select(dbConn, mpfRecordFilter);
foreach (EORSORecord orsoRecord in orsoRecords)
{
pFundER += orsoRecord.ORSORecActER;
pFundEE += orsoRecord.ORSORecActEE;
}
row[FIELD_WAGESWORK] = wagesByWork;
DBFilter workingSummaryFilter = new DBFilter();
workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr));
if (empTerm != null)
{
workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo));
}
else
{
workingSummaryFilter.add(new Match("EmpWorkingSummaryAsOfDate", "<=", payPeriod.PayPeriodTo));
}
workingSummaryFilter.add(new Match("EmpID", empInfo.EmpID));
ArrayList empWorkingSummaryList = EEmpWorkingSummary.db.select(dbConn, workingSummaryFilter);
double workHourTotal = 0, restDayTotal = 0;
foreach (EEmpWorkingSummary empWorkSummary in empWorkingSummaryList)
{
workHourTotal += empWorkSummary.EmpWorkingSummaryTotalWorkingHours;
restDayTotal += empWorkSummary.EmpWorkingSummaryRestDayEntitled;
}
row[FIELD_WORKHOURTOTAL] = workHourTotal;
row[FIELD_RESTDAYTOTAL] = restDayTotal;
DBFilter statutoryHolidayFilter = new DBFilter();
statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", ">=", payPeriod.PayPeriodFr < empInfo.EmpDateOfJoin ? empInfo.EmpDateOfJoin : payPeriod.PayPeriodFr));
if (empTerm != null)
{
statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo > empTerm.EmpTermLastDate ? empTerm.EmpTermLastDate : payPeriod.PayPeriodTo));
}
else
{
statutoryHolidayFilter.add(new Match("StatutoryHolidayDate", "<=", payPeriod.PayPeriodTo));
}
ArrayList statutoryHolidayList = EStatutoryHoliday.db.select(dbConn, statutoryHolidayFilter);
double restDayCount = 0;
foreach (EStatutoryHoliday statutoryHoliday in statutoryHolidayList)
{
restDayCount++;
}
row[FIELD_STATUTORYHOLIDAYTOTAL] = restDayCount;
DBFilter LeaveAppEmpPayrollFilter = new DBFilter();
LeaveAppEmpPayrollFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilter));
ArrayList LeaveAppEmpPayrollLists = ELeaveApplication.db.select(dbConn, LeaveAppEmpPayrollFilter);
foreach (ELeaveApplication leaveApp in LeaveAppEmpPayrollLists)
{
ELeaveCode leaveCode = new ELeaveCode();
leaveCode.LeaveCodeID = leaveApp.LeaveCodeID;
if (ELeaveCode.db.select(dbConn, leaveCode))
{
if (leaveCode.LeaveCodePayRatio >= 1)
{
fullPaidLeaveDays += leaveApp.LeaveAppDays;
}
else
{
nonFullPaidLeaveDays += leaveApp.LeaveAppDays;
}
}
}
row[FIELD_FULLPAIDLEAVETOTAL] = fullPaidLeaveDays;
row[FIELD_NONFULLPAIDLEAVETOTAL] = nonFullPaidLeaveDays;
dataTable.Rows.Add(row);
}
}
}
//DBFilter paymentCodeFilter = new DBFilter();
//paymentCodeFilter.add("PaymentCodeDisplaySeqNo", false);
//paymentCodeFilter.add("PaymentCode", false);
//ArrayList paymentCodeList = EPaymentCode.db.select(dbConn, paymentCodeFilter);
//foreach (EPaymentCode paymentCode in paymentCodeList)
//{
// if (dataTable.Columns.Contains(PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc))
// {
// DataColumn paymentColumn = dataTable.Columns[PAYMENTCODE_PREFIX + paymentCode.PaymentCodeDesc];
// paymentColumn.SetOrdinal(firstDetailColumnPos);
// if (!dataTable.Columns.Contains(paymentCode.PaymentCodeDesc))
// paymentColumn.ColumnName = paymentCode.PaymentCodeDesc;
// else
// {
// Console.Write("System reserved payment column is used");
// }
// }
//}
//for (int i = firstSummaryColumnPos; i < firstDetailColumnPos; i++)
// dataTable.Columns[firstSummaryColumnPos].SetOrdinal(dataTable.Columns.Count - 1);
export.Update(dataSet);
System.IO.FileStream excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open);
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(excelfileStream);
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.GetSheetAt(0);
workSheet.ShiftRows(workSheet.FirstRowNum, workSheet.LastRowNum, 1);
NPOI.HSSF.UserModel.HSSFRow excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(0);
if (excelRow == null)
{
excelRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(0);
}
NPOI.HSSF.UserModel.HSSFCell excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.GetCell(0);
if (excelCell == null)
{
excelCell = (NPOI.HSSF.UserModel.HSSFCell)excelRow.CreateCell(0);
}
excelCell.SetCellValue("Statutory Minimum Wage Summary Report");
excelfileStream = new System.IO.FileStream(exportFileName, System.IO.FileMode.Open);
workbook.Write(excelfileStream);
excelfileStream.Close();
WebUtils.TransmitFile(Response, exportFileName, "StatutoryMinimumWageSummary_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
return;
}
else
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.addError("Employee not selected");
}
}