protected void HierarchyLevel_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
EHierarchyLevel level = (EHierarchyLevel)e.Item.DataItem;
DBFilter filter = new DBFilter();
filter.add(new Match("HLevelID", level.HLevelID));
filter.add(new Match("CompanyID", CompanyID.SelectedValue.Equals(string.Empty) ? "0" : CompanyID.SelectedValue));
DropDownList c = (DropDownList)e.Item.FindControl("HElementID");
EEmpHierarchy h = (EEmpHierarchy)CurElements[level.HLevelID];
string selected = null;
if (h != null)
{
selected = h.HElementID.ToString();
EHierarchyElement element = new EHierarchyElement();
element.HElementID = h.HElementID;
EHierarchyElement.db.select(dbConn, element);
Label HierarchyElementLabel = (Label)e.Item.FindControl("OldHElementID");
HierarchyElementLabel.Text = element.HElementCode + " - " + element.HElementDesc;
}
else
{
c.Text = "";
}
WebFormUtils.loadValues(dbConn, c, EHierarchyElement.VLHierarchyElement, filter, null, selected, "combobox.notselected");
c.Attributes["HLevelID"] = level.HLevelID.ToString();
}
// 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 void Delete_Click(object sender, EventArgs e)
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.clear();
EHierarchyElement obj = new EHierarchyElement();
obj.HElementID = CurID;
db.select(dbConn, obj);
DBFilter hierarchyElementFilter = new DBFilter();
hierarchyElementFilter.add(new Match("HElementID", obj.HElementID));
IN inTerms = new IN("EmpPosID", "Select EmpPosID From " + EEmpHierarchy.db.dbclass.tableName, hierarchyElementFilter);
DBFilter empPosFilter = new DBFilter();
empPosFilter.add(inTerms);
empPosFilter.add("empid", true);
ArrayList empPosList = EEmpPositionInfo.db.select(dbConn, empPosFilter);
if (empPosList.Count > 0)
{
errors.addError(string.Format(HROne.Translation.PageErrorMessage.ERROR_CODE_USED_BY_EMPLOYEE, new string[] { HROne.Common.WebUtility.GetLocalizedString("Code"), obj.HElementCode }));
foreach (EEmpPositionInfo empPos in empPosList)
{
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = empPos.EmpID;
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
errors.addError("- " + empInfo.EmpNo + ", " + empInfo.EmpEngFullName);
}
else
{
EEmpPositionInfo.db.delete(dbConn, empPos);
}
}
errors.addError(HROne.Translation.PageErrorMessage.ERROR_ACTION_ABORT);
}
else
{
WebUtils.StartFunction(Session, FUNCTION_CODE);
db.delete(dbConn, obj);
WebUtils.EndFunction(dbConn);
HROne.Common.WebUtility.RedirectURLwithEncryptedQueryString(Response, Session, "HierarchyElement_List.aspx");
}
}
protected void Save_Click(object sender, EventArgs e)
{
EHierarchyElement c = new EHierarchyElement();
Hashtable values = new Hashtable();
binding.toValues(values);
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.clear();
db.validate(errors, values);
if (!errors.isEmpty())
{
return;
}
db.parse(values, c);
AND andFilterTerm = new AND();
andFilterTerm.add(new Match("CompanyID", c.CompanyID));
andFilterTerm.add(new Match("HLevelID", c.HLevelID));
if (!AppUtils.checkDuplicate(dbConn, db, c, errors, "HElementCode", andFilterTerm))
{
return;
}
WebUtils.StartFunction(Session, FUNCTION_CODE);
if (CurID < 0)
{
// Utils.MarkCreate(Session, c);
db.insert(dbConn, c);
CurID = c.HElementID;
// url = Utils.BuildURL(-1, CurID);
}
else
{
// Utils.Mark(Session, c);
db.update(dbConn, c);
}
WebUtils.EndFunction(dbConn);
HROne.Common.WebUtility.RedirectURLwithEncryptedQueryString(Response, Session, "HierarchyElement_View.aspx?HElementID=" + CurID);
}
protected bool loadObject()
{
obj = new EHierarchyElement();
bool isNew = WebFormWorkers.loadKeys(db, obj, DecryptedRequest);
if (!db.select(dbConn, obj))
{
return(false);
}
Hashtable values = new Hashtable();
db.populate(obj, values);
binding.toControl(values);
return(true);
}
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);
}
// 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 void HierarchyLevel_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
EHierarchyLevel level = (EHierarchyLevel)e.Item.DataItem;
Label c = (Label)e.Item.FindControl("HElementID");
// Start 0000125, Miranda, 2014-11-19
HyperLink hl = (HyperLink)e.Item.FindControl("hlHElementID");
// End 0000125, Miranda, 2014-11-19
EEmpHierarchy h = (EEmpHierarchy)CurElements[level.HLevelID];
//string selected = null;
if (h != null)
{
EHierarchyElement element = new EHierarchyElement();
element.HElementID = h.HElementID;
EHierarchyElement.db.select(dbConn, element);
c.Text = element.HElementCode + " - " + element.HElementDesc;
// Start 0000125, Miranda, 2014-11-19
if (h.HElementID > 0)
{
hl.NavigateUrl += h.HElementID;
hl.ForeColor = System.Drawing.Color.RoyalBlue;
}
else
{
hl.NavigateUrl = "";
}
// End 0000125, Miranda, 2014-11-19
}
else
{
c.Text = "";
// Start 0000125, Miranda, 2014-11-19
hl.NavigateUrl = "";
// End 0000125, Miranda, 2014-11-19
}
}
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);
}
}
public override ReportDocument GenerateReport()
{
if (EmpList.Count > 0)
{
string HierarchyLevel1 = " ";
string HierarchyLevel2 = " ";
string HierarchyLevel3 = " ";
ArrayList HierarchyLevelList = EHierarchyLevel.db.select(dbConn, new DBFilter());
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;
}
}
DataSet.EmployeeDetail ds = new DataSet.EmployeeDetail();
ArrayList empExtraFieldList = EEmpExtraField.db.select(dbConn, new DBFilter());
DataSet.EmployeeDetail.EmpExtraFieldDataTable empExtraFieldTable = ds.EmpExtraField;
foreach (EEmpExtraField empExtraField in empExtraFieldList)
{
DataSet.EmployeeDetail.EmpExtraFieldRow empExtraFieldRow = empExtraFieldTable.NewEmpExtraFieldRow();
empExtraFieldRow.EmpExtraFieldGroupName = empExtraField.EmpExtraFieldGroupName;
empExtraFieldRow.EmpExtraFieldID = empExtraField.EmpExtraFieldID;
empExtraFieldRow.EmpExtraFieldName = empExtraField.EmpExtraFieldName;
if (empExtraFieldRow.IsEmpExtraFieldGroupNameNull())
{
empExtraFieldRow.EmpExtraFieldGroupName = "Extra Information";
}
empExtraFieldTable.Rows.Add(empExtraFieldRow);
}
foreach (int EmpID in EmpList)
{
DataSet.EmployeeDetail.employeedetailRow empRow = ImportEmployeeDetailRow(dbConn, ds.employeedetail, EmpID, AppUtils.ServerDateTime().Date);
EEmpDocument empDocument = EEmpDocument.GetProfilePhotoEmpDocument(dbConn, EmpID);
if (empDocument != null)
{
string documentFilePath = empDocument.GetDocumentPhysicalPath(dbConn);
string transferFilePath = documentFilePath;
string strTmpFolder = string.Empty;
if (empDocument.EmpDocumentIsCompressed)
{
transferFilePath = empDocument.GetExtractedFilePath(dbConn);
}
empRow.PhotoByteArray = ThumbnailToByteArray(transferFilePath, 200, 200);
empDocument.RemoveExtractedFile();
}
DBFilter empFilter = new DBFilter();
empFilter.add(new Match("EmpID", EmpID));
DBFilter empPosFilter = new DBFilter();
empPosFilter.add(new Match("EmpID", EmpID));
empPosFilter.add("EmpPosEffFr", false);
ArrayList empPosList = EEmpPositionInfo.db.select(dbConn, empPosFilter);
DataSet.EmployeeDetail.EmpPositionInfoDataTable empPosTable = ds.EmpPositionInfo;
foreach (EEmpPositionInfo empPos in empPosList)
{
DataSet.EmployeeDetail.EmpPositionInfoRow row = empPosTable.NewEmpPositionInfoRow();
row.CompanyID = empPos.CompanyID;
row.EmpID = empPos.EmpID;
row.EmpPosEffFr = empPos.EmpPosEffFr;
if (!empPos.EmpPosEffTo.Ticks.Equals(0))
{
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;
foreach (EHierarchyLevel hLevel in HierarchyLevelList)
{
if (hLevel.HLevelSeqNo < 4 && hLevel.HLevelSeqNo > 0)
{
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);
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;
}
}
}
empPosTable.AddEmpPositionInfoRow(row);
}
DBFilter empRPFilter = new DBFilter();
empRPFilter.add(new Match("EmpID", EmpID));
empRPFilter.add("empRPEffFr", false);
ArrayList empRPList = EEmpRecurringPayment.db.select(dbConn, empRPFilter);
DataSet.EmployeeDetail.EmpRecurringPaymentDataTable empRPTable = ds.EmpRecurringPayment;
foreach (EEmpRecurringPayment empRP in empRPList)
{
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;
if (!empRP.EmpRPEffTo.Ticks.Equals(0))
{
row.EmpRPEffTo = empRP.EmpRPEffTo;
}
row.EmpRPID = empRP.EmpRPID;
if (empRP.EmpRPMethod.Equals("A"))
{
row.EmpRPMethod = HROne.Common.WebUtility.GetLocalizedString("Autopay");
}
else if (empRP.EmpRPMethod.Equals("Q"))
{
row.EmpRPMethod = HROne.Common.WebUtility.GetLocalizedString("Cheque");
}
else if (empRP.EmpRPMethod.Equals("C"))
{
row.EmpRPMethod = HROne.Common.WebUtility.GetLocalizedString("Cash");
}
else if (empRP.EmpRPMethod.Equals("O"))
{
row.EmpRPMethod = HROne.Common.WebUtility.GetLocalizedString("Others");
}
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);
}
EmployeeBankAccountProcess.ImportEmpBankAccountRow(dbConn, ds.EmpBankAccount, EmpID);
ArrayList empSpouseList = EEmpSpouse.db.select(dbConn, empFilter);
DataSet.EmployeeDetail.EmpSpouseDataTable empSpouseTable = ds.EmpSpouse;
foreach (EEmpSpouse empSpouse in empSpouseList)
{
DataSet.EmployeeDetail.EmpSpouseRow row = empSpouseTable.NewEmpSpouseRow();
row.EmpID = empSpouse.EmpID;
row.EmpSpouseChineseName = empSpouse.EmpSpouseChineseName;
//empSpouse.EmpSpouseDateOfBirth;
row.EmpSpouseHKID = empSpouse.EmpSpouseHKID;
row.EmpSpouseID = empSpouse.EmpSpouseID;
row.EmpSpouseOtherName = empSpouse.EmpSpouseOtherName;
row.EmpSpousePassportIssuedCountry = empSpouse.EmpSpousePassportIssuedCountry;
row.EmpSpousePassportNo = empSpouse.EmpSpousePassportNo;
row.EmpSpouseSurname = empSpouse.EmpSpouseSurname;
// Start 0000142, KuangWei, 2014-12-21
row.EmpGender = empSpouse.EmpGender;
if (empSpouse.EmpIsMedicalSchemaInsured)
{
row.EmpIsMedicalSchemaInsured = "Yes";
}
else
{
row.EmpIsMedicalSchemaInsured = "No";
}
row.EmpMedicalEffectiveDate = empSpouse.EmpMedicalEffectiveDate;
row.EmpMedicalExpiryDate = empSpouse.EmpMedicalExpiryDate;
// End 0000142, KuangWei, 2014-12-21
empSpouseTable.AddEmpSpouseRow(row);
}
ArrayList empDependantList = EEmpDependant.db.select(dbConn, empFilter);
DataSet.EmployeeDetail.EmpDependantDataTable empDependantTable = ds.EmpDependant;
foreach (EEmpDependant empDependant in empDependantList)
{
DataSet.EmployeeDetail.EmpDependantRow row = empDependantTable.NewEmpDependantRow();
row.EmpID = empDependant.EmpID;
row.EmpDependantChineseName = empDependant.EmpDependantChineseName;
//empDependant.EmpDependantDateOfBirth;
row.EmpDependantGender = empDependant.EmpDependantGender;
row.EmpDependantHKID = empDependant.EmpDependantHKID;
row.EmpDependantID = empDependant.EmpDependantID;
row.EmpDependantOtherName = empDependant.EmpDependantOtherName;
row.EmpDependantPassportIssuedCountry = empDependant.EmpDependantPassportIssuedCountry;
row.EmpDependantPassportNo = empDependant.EmpDependantPassportNo;
row.EmpDependantRelationship = empDependant.EmpDependantRelationship;
row.EmpDependantSurname = empDependant.EmpDependantSurname;
empDependantTable.AddEmpDependantRow(row);
}
DBFilter empQualificationFilter = new DBFilter();
empQualificationFilter.add(new Match("EmpID", EmpID));
empQualificationFilter.add("EmpQualificationFrom", false);
ArrayList empQualificationList = EEmpQualification.db.select(dbConn, empQualificationFilter);
DataSet.EmployeeDetail.EmpQualificationDataTable empQualificationTable = ds.EmpQualification;
foreach (EEmpQualification empQualification in empQualificationList)
{
DataSet.EmployeeDetail.EmpQualificationRow row = empQualificationTable.NewEmpQualificationRow();
row.EmpID = empQualification.EmpID;
if (!empQualification.EmpQualificationFrom.Ticks.Equals(0))
{
row.EmpQualificationFrom = empQualification.EmpQualificationFrom;
}
if (!empQualification.EmpQualificationTo.Ticks.Equals(0))
{
row.EmpQualificationTo = empQualification.EmpQualificationTo;
}
row.EmpQualificationID = empQualification.EmpQualificationID;
row.EmpQualificationInstitution = empQualification.EmpQualificationInstitution;
row.EmpQualificationRemark = empQualification.EmpQualificationRemark;
EQualification qualification = new EQualification();
qualification.QualificationID = empQualification.QualificationID;
if (EQualification.db.select(dbConn, qualification))
{
row.QualificationID = qualification.QualificationID;
row.QualificationCode = qualification.QualificationCode;
row.QualificationDesc = qualification.QualificationDesc;
}
empQualificationTable.AddEmpQualificationRow(row);
}
ArrayList empSkillList = EEmpSkill.db.select(dbConn, empFilter);
DataSet.EmployeeDetail.EmpSkillDataTable empSkillTable = ds.EmpSkill;
foreach (EEmpSkill empSkill in empSkillList)
{
DataSet.EmployeeDetail.EmpSkillRow row = empSkillTable.NewEmpSkillRow();
row.EmpID = empSkill.EmpID;
row.EmpSkillID = empSkill.EmpSkillID;
ESkill skill = new ESkill();
skill.SkillID = empSkill.SkillID;
if (ESkill.db.select(dbConn, skill))
{
row.SkillID = skill.SkillID;
row.SkillCode = skill.SkillCode;
row.SkillDesc = skill.SkillDesc;
}
ESkillLevel skillLevel = new ESkillLevel();
skillLevel.SkillLevelID = empSkill.SkillLevelID;
if (ESkillLevel.db.select(dbConn, skillLevel))
{
row.SkillLevelID = skillLevel.SkillLevelID;
row.SkillLevelCode = skillLevel.SkillLevelCode;
row.SkillLevelDesc = skillLevel.SkillLevelDesc;
}
empSkillTable.AddEmpSkillRow(row);
}
ArrayList empWorkExpList = EEmpWorkExp.db.select(dbConn, empFilter);
DataSet.EmployeeDetail.EmpWorkExpDataTable empWorkExpTable = ds.EmpWorkExp;
foreach (EEmpWorkExp empWorkExp in empWorkExpList)
{
DataSet.EmployeeDetail.EmpWorkExpRow row = empWorkExpTable.NewEmpWorkExpRow();
row.EmpID = empWorkExp.EmpID;
row.EmpWorkExpID = empWorkExp.EmpWorkExpID;
row.EmpWorkExpFromMonth = empWorkExp.EmpWorkExpFromMonth;
row.EmpWorkExpFromYear = empWorkExp.EmpWorkExpFromYear;
row.EmpWorkExpToYear = empWorkExp.EmpWorkExpToYear;
row.EmpWorkExpToMonth = empWorkExp.EmpWorkExpToMonth;
row.EmpWorkExpPosition = empWorkExp.EmpWorkExpPosition;
row.EmpWorkExpCompanyName = empWorkExp.EmpWorkExpCompanyName;
row.EmpWorkExpRemark = empWorkExp.EmpWorkExpRemark;
empWorkExpTable.AddEmpWorkExpRow(row);
}
ArrayList empExtraFieldValueList = EEmpExtraFieldValue.db.select(dbConn, empFilter);
DataSet.EmployeeDetail.EmpExtraFieldValueDataTable empExtraFieldValueTable = ds.EmpExtraFieldValue;
foreach (EEmpExtraFieldValue empExtraFieldValue in empExtraFieldValueList)
{
DataSet.EmployeeDetail.EmpExtraFieldValueRow row = empExtraFieldValueTable.NewEmpExtraFieldValueRow();
row.EmpID = empExtraFieldValue.EmpID;
row.EmpExtraFieldValueID = empExtraFieldValue.EmpExtraFieldValueID;
row.EmpExtraFieldID = empExtraFieldValue.EmpExtraFieldID;
row.EmpExtraFieldValue = empExtraFieldValue.EmpExtraFieldValue;
empExtraFieldValueTable.AddEmpExtraFieldValueRow(row);
}
//DBFilter filter = new DBFilter();
//filter.add(new Match("P.EmpID", EmpID));
//select = "P.*,EmpPos.*,Pos.*";
//from = "from EmpPersonalInfo P LEFT JOIN EmpPositionInfo EmpPos ON P.EmpID=EmpPos.EmpID AND EmpPos.EmpPosEffTo IS NULL LEFT JOIN Position Pos ON EmpPos.PositionID=Pos.PositionID";
//filter.loadData(ds, "employeedetail", null, select, from, null);
//select = "P.*,Pos.PositionCode,Pos.PositionDesc,R.RankCode, R.RankDesc";
//from = "from EmpPositionInfo P LEFT JOIN Position Pos ON P.PositionID=Pos.PositionID LEFT JOIN Rank R ON P.RankID=R.RankID";
//filter.loadData(ds, "EmpPositionInfo", null, select, from, null);
//select = "P.*,C.*";
//from = "from EmpRecurringPayment P LEFT JOIN PaymentCode C ON P.PayCodeID=C.PaymentCodeID ";
//filter.loadData(ds, "EmpRecurringPayment", null, select, from, null);
//select = "P.*";
//from = "from EmpBankAccount P ";
//filter.loadData(ds, "EmpBankAccount", null, select, from, null);
//select = "P.*";
//from = "from EmpSpouse P ";
//filter.loadData(ds, "EmpSpouse", null, select, from, null);
//select = "P.*";
//from = "from EmpDependant P ";
//filter.loadData(ds, "EmpDependant", null, select, from, null);
//select = "P.*, Q.*";
//from = "from EmpQualification P LEFT JOIN Qualification Q on P.QualificationID=Q.QualificationID";
//filter.loadData(ds, "EmpQualification", null, select, from, null);
//select = "P.*, S.*,L.*";
//from = "from EmpSkill P LEFT JOIN Skill S ON P.SkillID=S.SkillID LEFT JOIN SkillLevel L on P.SkillLevelID=L.SkillLevelID";
//filter.loadData(ds, "EmpSkill", null, select, from, null);
}
//DBAESEncryptStringFieldAttribute.decode(ds.Tables["employeedetail"], "EmpHKID", true);
//DBAESEncryptStringFieldAttribute.decode(ds.Tables["employeedetail"], "EmpPassportNo", false);
//DBAESEncryptStringFieldAttribute.decode(ds.Tables["employeedetail"], "EmpResAddr", true);
//DBAESEncryptStringFieldAttribute.decode(ds.Tables["employeedetail"], "EmpCorAddr", true);
//DBAESEncryptStringFieldAttribute.decode(ds.Tables["EmpSpouse"], "EmpSpouseHKID", false);
//DBAESEncryptStringFieldAttribute.decode(ds.Tables["EmpSpouse"], "EmpSpousePassportNo", false);
//DBAESEncryptStringFieldAttribute.decode(ds.Tables["EmpDependant"], "EmpDependantHKID", false);
//DBAESEncryptStringFieldAttribute.decode(ds.Tables["EmpDependant"], "EmpDependantPassportNo", false);
if (reportDocument == null)
{
reportDocument = new ReportTemplate.Report_Employee_Detail();
}
else
{
}
reportDocument.SetDataSource(ds);
reportDocument.SetParameterValue("HierarchyLevel1", HierarchyLevel1);
reportDocument.SetParameterValue("HierarchyLevel2", HierarchyLevel2);
reportDocument.SetParameterValue("HierarchyLevel3", HierarchyLevel3);
reportDocument.SetParameterValue("HierarchyLevel1", HierarchyLevel1, "PositionInfo");
reportDocument.SetParameterValue("HierarchyLevel2", HierarchyLevel2, "PositionInfo");
reportDocument.SetParameterValue("HierarchyLevel3", HierarchyLevel3, "PositionInfo");
return(reportDocument);
}
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 btnExport_Click(object sender, EventArgs e)
{
ArrayList list = WebUtils.SelectedRepeaterItemToBaseObjectList(ECostAllocation.db, Repeater, "ItemSelect");
if (list.Count > 0)
{
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("CostAllocation$");
dataSet.Tables.Add(dataTable);
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("EmployeeID", typeof(string));
dataTable.Columns.Add("EnglishName", typeof(string));
dataTable.Columns.Add("ChineseName", typeof(string));
dataTable.Columns.Add("PaymentCodeDesc", typeof(string));
dataTable.Columns.Add("CostCenterCode", typeof(string));
dataTable.Columns.Add("Amount", typeof(double));
foreach (ECostAllocation obj in list)
{
if (ECostAllocation.db.select(dbConn, obj))
{
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = obj.EmpID;
EEmpPersonalInfo.db.select(dbConn, empInfo);
DBFilter costAllocationDetailFilter = new DBFilter();
costAllocationDetailFilter.add(new Match("CostAllocationID", obj.CostAllocationID));
ArrayList costAllocationDetailList = ECostAllocationDetail.db.select(dbConn, costAllocationDetailFilter);
foreach (ECostAllocationDetail detail in costAllocationDetailList)
{
ECompany company = new ECompany();
company.CompanyID = detail.CompanyID;
ECompany.db.select(dbConn, company);
DataRow row = dataTable.NewRow();
row["EmployeeID"] = empInfo.EmpNo;
row["EnglishName"] = empInfo.EmpEngFullName;;
row["ChineseName"] = empInfo.EmpChiFullName;
row["Company"] = company.CompanyCode;
DBFilter costAllocationDetailHierarchyFilter = new DBFilter();
costAllocationDetailHierarchyFilter.add(new Match("CostAllocationDetailID", detail.CostAllocationDetailID));
ArrayList empHierarchyList = ECostAllocationDetailHElement.db.select(dbConn, costAllocationDetailHierarchyFilter);
foreach (ECostAllocationDetailHElement 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;
}
}
}
EPaymentCode paymentCode = new EPaymentCode();
paymentCode.PaymentCodeID = detail.PaymentCodeID;
EPaymentCode.db.select(dbConn, paymentCode);
if (detail.CostAllocationDetailIsContribution)
{
row["PaymentCodeDesc"] = paymentCode.PaymentCodeDesc + "(Contribution)";
}
else
{
row["PaymentCodeDesc"] = paymentCode.PaymentCodeDesc;
}
ECostCenter costCenter = new ECostCenter();
costCenter.CostCenterID = detail.CostCenterID;
ECostCenter.db.select(dbConn, costCenter);
row["CostCenterCode"] = costCenter.CostCenterCode;
row["Amount"] = detail.CostAllocationDetailAmount;
dataTable.Rows.Add(row);
}
}
}
export.Update(dataSet);
WebUtils.TransmitFile(Response, exportFileName, "CostALlocation" + (CostAllocationStatus.SelectedValue.Equals("T")?"Trial":"Confirm") + "_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
return;
}
else
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.addError("Employee not selected");
}
view = loadData(info, EEmpPayroll.db, Repeater);
}
protected System.IO.FileInfo GenerateExcelFile()
{
DateTime periodFromDate;
DateTime periodToDate;
if (DateTime.TryParse(PeriodFrDate.Value, out periodFromDate) && DateTime.TryParse(PeriodToDate.Value, out periodToDate))
{
int countDay = periodToDate.AddDays(1).Subtract(periodFromDate).Days;// get days from periodFromDate to periodToDate
List <EHierarchyLevel> hLevelList = GetHierarchyLevelList(dbConn);
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet();
SetWorkSheetColumnWidth(workSheet, countDay);
createWorkSheetPeriodRow(workbook, workSheet, periodFromDate, periodToDate);
createWorkSheetHeaderRows(workbook, workSheet, hLevelList, countDay, periodFromDate, periodToDate);
// load data detail start
ArrayList list = LoadRosterTableGroup(periodFromDate, periodToDate, CurID);
if (list.Count >= 1)
{
selectedRosterTableGroupID = ((ERosterTableGroup)list[0]).RosterTableGroupID;
BuildCommonRosterCodeAndTermsFilter();
Dictionary <int, List <DateTime> > staffRosterDateList = LoadEmployeeList(periodFromDate, periodToDate);
int RowPos = 0;
foreach (int empID in staffRosterDateList.Keys)
{
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = empID;
if (!EEmpPersonalInfo.db.select(dbConn, empInfo))
{
continue;
}
string positionTitle = string.Empty;
List <string> hElementDescList = new List <string>();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, periodFromDate, empID);
if (empPos != null)
{
EPosition position = new EPosition();
position.PositionID = empPos.PositionID;
if (EPosition.db.select(dbConn, position))
{
positionTitle = position.PositionDesc;
}
foreach (EHierarchyLevel hLevel in hLevelList)
{
string hElementDesc = string.Empty;
DBFilter empHierarchy1Filter = new DBFilter();
empHierarchy1Filter.add(new Match("EmpPosID", empPos.EmpPosID));
empHierarchy1Filter.add(new Match("HLevelID", hLevel.HLevelID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchy1Filter);
if (empHierarchyList.Count > 0)
{
EHierarchyElement hElement = new EHierarchyElement();
hElement.HElementID = ((EEmpHierarchy)empHierarchyList[0]).HElementID;
if (EHierarchyElement.db.select(dbConn, hElement))
{
hElementDesc = hElement.HElementDesc;
}
}
hElementDescList.Add(hElementDesc);
}
}
createWorkSheetDetailRows(workbook, workSheet, RowPos, hElementDescList, empInfo, positionTitle, countDay, periodFromDate, empID);
RowPos += 2;
}
}
// load data detail end
string strTmpFolder = HROne.Common.Folder.GetOrCreateSessionTempFolder(Session.SessionID).FullName;
string strTmpFile = System.IO.Path.Combine(strTmpFolder, AppUtils.ServerDateTime().ToString("~yyyyMMddHHmmss_AttendanceTimeCardRecordReport.xls"));
System.IO.Stream fileoutputstream = new System.IO.FileStream(strTmpFile, System.IO.FileMode.OpenOrCreate);
workbook.Write(fileoutputstream);
fileoutputstream.Close();
return(new System.IO.FileInfo(strTmpFile));
}
return(null);
}
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 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);
}
}
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);
}
// End 0000185, KuangWei, 2015-05-05
// Start 0000185, KuangWei, 2015-05-05
public override ReportDocument GenerateReport()
{
if (CurrentDate.Ticks != 0 && ReferenceDate.Ticks != 0)
{
dataSet = new DataSet.HeadCountSummarySet();
dataSet.Gender.Rows.Add(new string[] { "M", "Male" });
dataSet.Gender.Rows.Add(new string[] { "F", "Female" });
// Get Total PreviousCount
DBFilter EmpReferencePosFilter = new DBFilter();
EmpReferencePosFilter.add(new Match("EmpPosEffFr", "<=", ReferenceDate));
OR orReferencePosEffToDate = new OR();
orReferencePosEffToDate.add(new Match("EmpPosEffTo", ">=", ReferenceDate));
orReferencePosEffToDate.add(new NullTerm("EmpPosEffTo"));
EmpReferencePosFilter.add(orReferencePosEffToDate);
DBFilter empReferenceTerminationFilter = new DBFilter();
empReferenceTerminationFilter.add(new Match("EmpTermLastDate", "<", ReferenceDate));
EmpReferencePosFilter.add(new IN("not empid", "Select empid from EmpTermination", empReferenceTerminationFilter));
ArrayList empReferenceDatePosList = EEmpPositionInfo.db.select(dbConn, EmpReferencePosFilter);
foreach (EEmpPositionInfo empPos in empReferenceDatePosList)
{
foreach (EEmpPersonalInfo obj in empList)
{
if (obj.EmpID == empPos.EmpID)
{
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = empPos.EmpID;
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
DataSet.HeadCountSummarySet.HeadCountSummaryRow row = CreateHeadCountRow(empPos.EmpPosID);
row.PreviousCount = 1;
dataSet.HeadCountSummary.Rows.Add(row);
}
}
}
}
// Get Total CurrentCount
DBFilter EmpCurrentPosFilter = new DBFilter();
EmpCurrentPosFilter.add(new Match("EmpPosEffFr", "<=", CurrentDate));
OR orCurrentPosEffToDate = new OR();
orCurrentPosEffToDate.add(new Match("EmpPosEffTo", ">=", CurrentDate));
orCurrentPosEffToDate.add(new NullTerm("EmpPosEffTo"));
EmpCurrentPosFilter.add(orCurrentPosEffToDate);
DBFilter empCurrentTerminationFilter = new DBFilter();
empCurrentTerminationFilter.add(new Match("EmpTermLastDate", "<", CurrentDate));
EmpCurrentPosFilter.add(new IN("not empid", "Select empid from EmpTermination", empCurrentTerminationFilter));
ArrayList empCurrentDatePosList = EEmpPositionInfo.db.select(dbConn, EmpCurrentPosFilter);
foreach (EEmpPositionInfo empPos in empCurrentDatePosList)
{
foreach (EEmpPersonalInfo obj in empList)
{
if (obj.EmpID == empPos.EmpID)
{
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = empPos.EmpID;
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
DataSet.HeadCountSummarySet.HeadCountSummaryRow row = CreateHeadCountRow(empPos.EmpPosID);
row.CurrentCount = 1;
dataSet.HeadCountSummary.Rows.Add(row);
}
}
}
}
// Get New Join Count and transfer count
DBFilter EmpNewPosFilter = new DBFilter();
EmpNewPosFilter.add(new Match("EmpPosEffFr", ">", ReferenceDate));
EmpNewPosFilter.add(new Match("EmpPosEffFr", "<=", CurrentDate));
ArrayList empNewPosList = EEmpPositionInfo.db.select(dbConn, EmpNewPosFilter);
foreach (EEmpPositionInfo empPos in empNewPosList)
{
foreach (EEmpPersonalInfo obj in empList)
{
if (obj.EmpID == empPos.EmpID)
{
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = empPos.EmpID;
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
DataSet.HeadCountSummarySet.HeadCountSummaryRow row = CreateHeadCountRow(empPos.EmpPosID);
// check2 if have previous position change
{
DBFilter previousEmpPosFilter = new DBFilter();
previousEmpPosFilter.add(new Match("EmpID", empPos.EmpID));
previousEmpPosFilter.add(new Match("EmpPosEffTo", "<", empPos.EmpPosEffFr));
previousEmpPosFilter.add("EmpPosEffFr", false);
ArrayList previousEmpPosList = EEmpPositionInfo.db.select(dbConn, previousEmpPosFilter);
if (previousEmpPosList.Count > 0)
{
EEmpPositionInfo empPreviousPos = (EEmpPositionInfo)previousEmpPosList[0];
EHierarchyElement hPreviousElement = getHierarchyElement(empPreviousPos.EmpPosID);
if (empPos.CompanyID != empPreviousPos.CompanyID || row.HElementID != hPreviousElement.HElementID)
{
row.TransferInCount = 1;
if (empPreviousPos.EmpPosEffFr <= (CurrentDate < ReferenceDate ? CurrentDate : ReferenceDate))
{
DataSet.HeadCountSummarySet.HeadCountSummaryRow previousrow = CreateHeadCountRow(empPreviousPos.EmpPosID);
previousrow.TransferOutCount = 1;
dataSet.HeadCountSummary.Rows.Add(previousrow);
}
}
}
else
{
row.NewJoinCount = 1;
}
}
// check if have next position change
if (empPos.EmpPosEffFr < empPos.EmpPosEffTo)
{
DBFilter nextEmpPosFilter = new DBFilter();
nextEmpPosFilter.add(new Match("EmpID", empPos.EmpID));
nextEmpPosFilter.add(new Match("EmpPosEffFr", ">", empPos.EmpPosEffTo));
nextEmpPosFilter.add(new Match("EmpPosEffFr", "<=", CurrentDate));
nextEmpPosFilter.add("EmpPosEffFr", true);
ArrayList nextEmpPosList = EEmpPositionInfo.db.select(dbConn, nextEmpPosFilter);
if (nextEmpPosList.Count > 0)
{
EEmpPositionInfo empNextPos = (EEmpPositionInfo)nextEmpPosList[0];
EHierarchyElement hNextElement = getHierarchyElement(empNextPos.EmpPosID);
if (empPos.CompanyID != empNextPos.CompanyID || row.HElementID != hNextElement.HElementID)
{
row.TransferOutCount = 1;
if (empNextPos.EmpPosEffTo.Ticks.Equals(0) || empNextPos.EmpPosEffTo > (CurrentDate > ReferenceDate ? CurrentDate : ReferenceDate))
{
DataSet.HeadCountSummarySet.HeadCountSummaryRow nextRow = CreateHeadCountRow(empNextPos.EmpPosID);
nextRow.TransferInCount = 1;
dataSet.HeadCountSummary.Rows.Add(nextRow);
}
}
}
}
dataSet.HeadCountSummary.Rows.Add(row);
}
}
}
}
// Get Terminated Count
DBFilter empTerminationFilter = new DBFilter();
empTerminationFilter.add(new Match("EmpTermLastDate", ">=", ReferenceDate));
empTerminationFilter.add(new Match("EmpTermLastDate", "<", CurrentDate));
ArrayList empTerminationList = EEmpTermination.db.select(dbConn, empTerminationFilter);
if (empTerminationList.Count > 0)
{
foreach (EEmpTermination empTermination in empTerminationList)
{
foreach (EEmpPersonalInfo obj in empList)
{
if (obj.EmpID == empTermination.EmpID)
{
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = empTermination.EmpID;
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
DBFilter empTerminationPosFilter = new DBFilter();
empTerminationPosFilter.add(new Match("EmpID", empTermination.EmpID));
empTerminationPosFilter.add(new Match("EmpPosEffFr", "<=", empTermination.EmpTermLastDate));
OR orEmpTerminationPosEffToDate = new OR();
orEmpTerminationPosEffToDate.add(new Match("EmpPosEffTo", ">=", empTermination.EmpTermLastDate));
orEmpTerminationPosEffToDate.add(new NullTerm("EmpPosEffTo"));
empTerminationPosFilter.add(orEmpTerminationPosEffToDate);
empTerminationPosFilter.add("EmpPosEffFr", false);
ArrayList empTerminationPosList = EEmpPositionInfo.db.select(dbConn, empTerminationPosFilter);
if (empTerminationPosList.Count > 0)
{
EEmpPositionInfo empPos = (EEmpPositionInfo)empTerminationPosList[0];
DataSet.HeadCountSummarySet.HeadCountSummaryRow row = CreateHeadCountRow(empPos.EmpPosID);
row.TerminateCount = 1;
dataSet.HeadCountSummary.Rows.Add(row);
}
}
}
}
}
}
if (reportDocument == null)
{
if (GroupBy.Equals(ReportGroupBy.Company))
{
reportDocument = new ReportTemplate.Report_Employee_Gender_Company();
}
else if (GroupBy.Equals(ReportGroupBy.Hierarchy))
{
reportDocument = new ReportTemplate.Report_Employee_Gender_Hierarchy();
}
else if (GroupBy.Equals(ReportGroupBy.PayrollGroup))
{
reportDocument = new ReportTemplate.Report_Employee_Gender_PayrollGroup();
}
}
else
{
}
reportDocument.SetDataSource(dataSet);
if (GroupBy.Equals(ReportGroupBy.Hierarchy))
{
reportDocument.SetParameterValue("HierarchyLevelName", HierarchyLevelName);
}
reportDocument.SetParameterValue("AsOfDateString", CurrentDate.ToString("yyyy-MM-dd"));
//reportDocument.SetParameterValue("CurrentDate", CurrentDate);
//reportDocument.SetParameterValue("ReferenceDate", ReferenceDate);
////if (Gender.Equals("M"))
//// reportDocument.SetParameterValue("Gender", "Male");
////else if (Gender.Equals("F"))
//// reportDocument.SetParameterValue("Gender", "Female");
////else
// reportDocument.SetParameterValue("Gender", "All");
// rpt.SetDataSource((DataTable)mpfTable);
return(reportDocument);
}
else
{
return(null);
}
}
protected override System.Data.DataSet CreateDataSource()
{
const string PAYMENTCODE_PREFIX = "[Payment] ";
System.Data.DataSet dataSet = new System.Data.DataSet(); //export.GetDataSet();
DataTable dataTable = new DataTable("Payroll$");
dataSet.Tables.Add(dataTable);
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("Payroll Group", typeof(string));
dataTable.Columns.Add("Position", typeof(string));
dataTable.Columns.Add("EmpNo", typeof(string));
dataTable.Columns.Add("English Name", typeof(string));
dataTable.Columns.Add("Chinese Name", typeof(string));
dataTable.Columns.Add("HKID", typeof(string));
dataTable.Columns.Add("From", typeof(DateTime));
dataTable.Columns.Add("To", typeof(DateTime));
int firstSummaryColumnPos = dataTable.Columns.Count;
dataTable.Columns.Add("Net Payment", typeof(double));
dataTable.Columns.Add("Relevant Income", typeof(double));
dataTable.Columns.Add("Non-Relevant Income", typeof(double));
dataTable.Columns.Add("Wages Payable for Min Wages", typeof(double));
dataTable.Columns.Add("Total Hours Worked", typeof(double));
dataTable.Columns.Add("Min Wages Required", typeof(double));
dataTable.Columns.Add("Employer Mandatory Contribution", typeof(double));
// dataTable.Columns.Add("Employee Mandatory Contribution", typeof(double));
dataTable.Columns.Add("Employer Voluntary Contribution", typeof(double));
// dataTable.Columns.Add("Employee Voluntary Contribution", typeof(double));
dataTable.Columns.Add("Employer P-Fund Contribution", typeof(double));
// dataTable.Columns.Add("Employee P-Fund Contribution", typeof(double));
dataTable.Columns.Add("Total Employer Contribution", typeof(double));
dataTable.Columns.Add("Total Employee Contribution", typeof(double));
dataTable.Columns.Add("Total Taxable Payment", typeof(double));
dataTable.Columns.Add("Total Non-Taxable Payment", typeof(double));
int firstDetailColumnPos = dataTable.Columns.Count;
foreach (EEmpPersonalInfo empInfo in EmpList)
{
EEmpPersonalInfo.db.select(dbConn, empInfo);
//DBFilter empPayrollFilterForPayrollPeriod = new DBFilter();
//empPayrollFilterForPayrollPeriod.add(new Match("ep.EmpID", empInfo.EmpID));
//empPayrollFilterForPayrollPeriod.add(Payroll_PeriodSelectionList1.GetEmpPayrollDBTerm());
//DBFilter payPeriodFilter = new DBFilter();
//payPeriodFilter.add(new IN("PayPeriodID", "SELECT PayPeriodID from " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilterForPayrollPeriod));
//ArrayList payPeriodList = EPayrollPeriod.db.select(dbConn, payPeriodFilter);
if (PayPeriodList == null)
{
OR orPayBatchIDTerms = new OR();
foreach (EPayrollBatch payBatch in PayBatchList)
{
orPayBatchIDTerms.add(new Match("ep.PayBatchID", payBatch.PayBatchID));
}
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(orPayBatchIDTerms);
DBFilter payPeriodFilter = new DBFilter();
payPeriodFilter.add(new IN(EPayrollPeriod.db.dbclass.tableName + ".PayPeriodID ", "SELECT DISTINCT PayPeriodID FROM " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter));
PayPeriodList = EPayrollPeriod.db.select(dbConn, payPeriodFilter);
}
foreach (EPayrollPeriod payPeriod in PayPeriodList)
{
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(new Match("EmpID", empInfo.EmpID));
if (reportType.Equals(ReportType.TrialRun))
{
empPayrollFilter.add(new Match("EmpPayStatus", "=", "T"));
}
else
{
empPayrollFilter.add(new Match("EmpPayStatus", "<>", "T"));
}
if (PayBatchList != null)
{
OR orPayrollBatch = new OR();
foreach (EPayrollBatch payrollBatch in PayBatchList)
{
orPayrollBatch.add(new Match("PayBatchID", payrollBatch.PayBatchID));
}
empPayrollFilter.add(orPayrollBatch);
}
empPayrollFilter.add(new Match("PayPeriodID", payPeriod.PayPeriodID));
// Check if the EmpPayroll record for that payroll period exists
if (EEmpPayroll.db.count(dbConn, empPayrollFilter) > 0 && EPayrollPeriod.db.select(dbConn, payPeriod))
{
EPayrollGroup payrollGroup = new EPayrollGroup();
payrollGroup.PayGroupID = payPeriod.PayGroupID;
EPayrollGroup.db.select(dbConn, payrollGroup);
DataRow row = dataTable.NewRow();
row["EmpNo"] = empInfo.EmpNo;
row["English Name"] = empInfo.EmpEngFullName;
row["Chinese Name"] = empInfo.EmpChiFullName;
row["HKID"] = empInfo.EmpHKID;
row["From"] = payPeriod.PayPeriodFr;
row["To"] = payPeriod.PayPeriodTo;
row["Payroll Group"] = payrollGroup.PayGroupDesc;
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["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.HElementDesc;
}
}
}
EPosition position = new EPosition();
position.PositionID = empPos.PositionID;
if (EPosition.db.select(dbConn, position))
{
row["Position"] = position.PositionDesc;
}
}
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;
//DBFilter empPayrollFilterForPaymentRecord = new DBFilter(empPayrollFilterForPayrollPeriod);
//empPayrollFilterForPaymentRecord.add(new Match("PayPeriodID", payPeriod.PayPeriodID));
//DBFilter paymentRecordFilter = new DBFilter();
//paymentRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilterForPaymentRecord));
//paymentRecordFilter.add(new Match("PayRecStatus", "A"));
IN inEmpPayroll = new IN("EmpPayrollID", "Select ep.EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilter);
DBFilter empPayrollFilterForPaymentRecord = new DBFilter();
empPayrollFilterForPaymentRecord.add(inEmpPayroll);
ArrayList paymentRecords = EPaymentRecord.db.select(dbConn, empPayrollFilterForPaymentRecord);
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;
}
}
row["Net Payment"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(netAmount, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
row["Relevant Income"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(releventIncome, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
row["Non-Relevant Income"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(nonRelevantIncome, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
row["Wages Payable for Min Wages"] = HROne.Payroll.PayrollProcess.GetTotalWagesWithoutRestDayPayment(dbConn, empInfo.EmpID, payPeriod.PayPeriodFr, payPeriod.PayPeriodTo, null);
row["Total Hours Worked"] = HROne.Payroll.PayrollProcess.GetTotalEmpPayrollWorkingHours(dbConn, empInfo.EmpID, payPeriod.PayPeriodID);
row["Min Wages Required"] = (double)row["Total Hours Worked"] * HROne.Payroll.PayrollProcess.GetMinimumWages(dbConn, empInfo.EmpID, payPeriod.PayPeriodTo);
row["Total Taxable Payment"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(taxableAmount, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
row["Total Non-Taxable Payment"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(nonTaxableAmount, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
ArrayList mpfRecords = EMPFRecord.db.select(dbConn, empPayrollFilterForPaymentRecord);
foreach (EMPFRecord mpfRecord in mpfRecords)
{
vcER += mpfRecord.MPFRecActVCER;
mcER += +mpfRecord.MPFRecActMCER;
vcEE += mpfRecord.MPFRecActVCEE;
mcEE += mpfRecord.MPFRecActMCEE;
}
ArrayList orsoRecords = EORSORecord.db.select(dbConn, empPayrollFilterForPaymentRecord);
foreach (EORSORecord orsoRecord in orsoRecords)
{
pFundER += orsoRecord.ORSORecActER;
pFundEE += orsoRecord.ORSORecActEE;
}
row["Employer Mandatory Contribution"] = mcER;
// row["Employee Mandatory Contribution"] = mcEE;
row["Employer Voluntary Contribution"] = vcER;
// row["Employee Voluntary Contribution"] = vcEE;
row["Employer P-Fund Contribution"] = pFundER;
// row["Employee P-Fund Contribution"] = pFundEE;
row["Total Employer Contribution"] = mcER + vcER + pFundER;
row["Total Employee Contribution"] = mcEE + vcEE + pFundEE;
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
{
System.Diagnostics.Debug.Write("System reserved payment column is used");
}
}
}
for (int i = 0; i < firstDetailColumnPos; i++)
{
dataTable.Columns[i].ColumnName = HROne.Common.WebUtility.GetLocalizedString(dataTable.Columns[i].ColumnName);
}
for (int i = firstSummaryColumnPos; i < firstDetailColumnPos; i++)
{
dataTable.Columns[firstSummaryColumnPos].SetOrdinal(dataTable.Columns.Count - 1);
}
return(dataSet);
}
protected void btnExport_Click(object sender, EventArgs e)
{
ArrayList list = WebUtils.SelectedRepeaterItemToBaseObjectList(ECostAllocation.db, Repeater, "ItemSelect");
int GroupingHierarchyLevelID = 1;
string GroupingHierarchyLevelDesc = string.Empty;
if (list.Count > 0)
{
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();
CreateSummaryDataTable(dataSet);
DBFilter hierarchyLevelFilter = new DBFilter();
Hashtable hierarchyLevelHashTable = new Hashtable();
hierarchyLevelFilter.add("HLevelSeqNo", true);
ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);
foreach (EHierarchyLevel hlevel in hierarchyLevelList)
{
if (string.IsNullOrEmpty(GroupingHierarchyLevelDesc))
{
GroupingHierarchyLevelID = hlevel.HLevelID;
GroupingHierarchyLevelDesc = hlevel.HLevelDesc.Trim();
}
hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
}
foreach (ECostAllocation obj in list)
{
if (ECostAllocation.db.select(dbConn, obj))
{
EEmpPayroll empPayroll = new EEmpPayroll();
empPayroll.EmpPayrollID = obj.EmpPayrollID;
EEmpPayroll.db.select(dbConn, empPayroll);
EPayrollPeriod payPeriod = new EPayrollPeriod();
payPeriod.PayPeriodID = empPayroll.PayPeriodID;
EPayrollPeriod.db.select(dbConn, payPeriod);
EPayrollGroup payGroup = new EPayrollGroup();
payGroup.PayGroupID = payPeriod.PayGroupID;
EPayrollGroup.db.select(dbConn, payGroup);
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = obj.EmpID;
EEmpPersonalInfo.db.select(dbConn, empInfo);
DBFilter costAllocationDetailFilter = new DBFilter();
costAllocationDetailFilter.add(new Match("CostAllocationID", obj.CostAllocationID));
ArrayList costAllocationDetailList = ECostAllocationDetail.db.select(dbConn, costAllocationDetailFilter);
foreach (ECostAllocationDetail detail in costAllocationDetailList)
{
ECompany company = new ECompany();
company.CompanyID = detail.CompanyID;
ECompany.db.select(dbConn, company);
ECostCenter costCenter = new ECostCenter();
costCenter.CostCenterID = detail.CostCenterID;
ECostCenter.db.select(dbConn, costCenter);
int HElementID = 0;
string hElementDesc = string.Empty;
DBFilter costAllocationDetailHierarchyFilter = new DBFilter();
costAllocationDetailHierarchyFilter.add(new Match("CostAllocationDetailID", detail.CostAllocationDetailID));
ArrayList empHierarchyList = ECostAllocationDetailHElement.db.select(dbConn, costAllocationDetailHierarchyFilter);
foreach (ECostAllocationDetailHElement 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))
{
// Select first hierarchy for testing
if (hierarchyLevel.HLevelDesc.Equals(GroupingHierarchyLevelDesc.Trim()) && HElementID == 0)
{
HElementID = hierarchyElement.HElementID;
hElementDesc = hierarchyElement.HElementDesc;
}
}
}
}
DataTable hierarchyTable = dataSet.Tables["hierarchy"];
DataTable paymentTable = dataSet.Tables["payment"];
DataTable contributionTable = dataSet.Tables["contribution"];
DataRow[] rows = hierarchyTable.Select("CompanyID=" + company.CompanyID + " and PayGroupID=" + payGroup.PayGroupID + " and HElementID=" + HElementID + " and CostCenterID=" + detail.CostCenterID);
int hierarchyRowID = 0;
if (rows.Length == 0)
{
hierarchyRowID = hierarchyTable.Rows.Count + 1;
DataRow hierarchyRow = hierarchyTable.NewRow();
hierarchyRow["ID"] = hierarchyRowID;
hierarchyRow["Company"] = company.CompanyName;
hierarchyRow["PayrollGroupDesc"] = payGroup.PayGroupDesc;
hierarchyRow["HierarchyDesc"] = hElementDesc;
hierarchyRow["CostCenterDesc"] = costCenter.CostCenterDesc;
hierarchyRow["CompanyID"] = company.CompanyID;
hierarchyRow["hElementID"] = HElementID;
hierarchyRow["CostCenterID"] = costCenter.CostCenterID;
hierarchyRow["PayGroupID"] = payGroup.PayGroupID;
hierarchyTable.Rows.Add(hierarchyRow);
}
else
{
hierarchyRowID = (int)rows[0]["ID"];
}
EPaymentCode paymentCode = new EPaymentCode();
paymentCode.PaymentCodeID = detail.PaymentCodeID;
EPaymentCode.db.select(dbConn, paymentCode);
if (!detail.CostAllocationDetailIsContribution)
{
if (!paymentTable.Columns.Contains(paymentCode.PaymentCodeDesc.Trim()))
{
paymentTable.Columns.Add(paymentCode.PaymentCodeDesc.Trim(), typeof(double));
}
rows = paymentTable.Select("ID=" + hierarchyRowID);
DataRow paymentRow;
if (rows.Length == 0)
{
paymentRow = paymentTable.NewRow();
paymentRow["ID"] = hierarchyRowID;
paymentTable.Rows.Add(paymentRow);
}
else
{
paymentRow = rows[0];
}
if (paymentRow.IsNull(paymentCode.PaymentCodeDesc.Trim()))
{
paymentRow[paymentCode.PaymentCodeDesc.Trim()] = detail.CostAllocationDetailAmount;
}
else
{
paymentRow[paymentCode.PaymentCodeDesc.Trim()] = (double)paymentRow[paymentCode.PaymentCodeDesc.Trim()] + detail.CostAllocationDetailAmount;
}
}
else
{
rows = contributionTable.Select("ID=" + hierarchyRowID);
DataRow contributionRow;
if (rows.Length == 0)
{
contributionRow = contributionTable.NewRow();
contributionRow["ID"] = hierarchyRowID;
contributionRow["MCEE"] = 0;
contributionRow["MCER"] = 0;
contributionRow["VCEE"] = 0;
contributionRow["VCER"] = 0;
contributionRow["PFUNDEE"] = 0;
contributionRow["PFUNDER"] = 0;
contributionTable.Rows.Add(contributionRow);
}
else
{
contributionRow = rows[0];
}
if (paymentCode.PaymentTypeID.Equals(EPaymentType.SystemPaymentType.MPFEmployeeMandatoryContributionPaymentType(dbConn).PaymentTypeID))
{
contributionRow["MCEE"] = (double)contributionRow["MCEE"] + detail.CostAllocationDetailAmount;
}
else if (paymentCode.PaymentTypeID.Equals(EPaymentType.SystemPaymentType.MPFEmployeeVoluntaryContributionPaymentType(dbConn).PaymentTypeID))
{
contributionRow["VCEE"] = (double)contributionRow["VCEE"] + detail.CostAllocationDetailAmount;
}
else if (paymentCode.PaymentTypeID.Equals(EPaymentType.SystemPaymentType.MPFEmployerMandatoryContributionPaymentType(dbConn).PaymentTypeID))
{
contributionRow["MCER"] = (double)contributionRow["MCER"] + detail.CostAllocationDetailAmount;
}
else if (paymentCode.PaymentTypeID.Equals(EPaymentType.SystemPaymentType.MPFEmployerVoluntaryContributionPaymentType(dbConn).PaymentTypeID))
{
contributionRow["VCER"] = (double)contributionRow["VCER"] + detail.CostAllocationDetailAmount;
}
else if (paymentCode.PaymentTypeID.Equals(EPaymentType.SystemPaymentType.PFundEmployeeContributionPaymentType(dbConn).PaymentTypeID))
{
contributionRow["PFUNDEE"] = (double)contributionRow["PFUNDEE"] + detail.CostAllocationDetailAmount;
}
else if (paymentCode.PaymentTypeID.Equals(EPaymentType.SystemPaymentType.PFundEmployerContributionPaymentType(dbConn).PaymentTypeID))
{
contributionRow["PFUNDER"] = (double)contributionRow["PFUNDER"] + detail.CostAllocationDetailAmount;
}
}
}
}
}
GenerateExcelReport(dataSet, exportFileName);
//export.Update(dataSet);
WebUtils.TransmitFile(Response, exportFileName, "CostALlocation" + (CostAllocationStatus.SelectedValue.Equals("T") ? "Trial" : "Confirm") + "_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
Response.End();
}
else
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.addError("Employee not selected");
}
view = loadData(info, EEmpPayroll.db, Repeater);
}
private void GenerateRosterTableData(ArrayList EmpInfoList, int year, int month)
{
DateTime dateStart = new DateTime(year, month, 1);
DateTime dateEnd = new DateTime(year, month, DateTime.DaysInMonth(year, month));
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
const string FIELD_EMP_NO = "Emp. No";
const int COLUMN_HEADER_ROW = 2;
NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.CreateSheet("RosterTable");
NPOI.HSSF.UserModel.HSSFCellStyle upperLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
upperLineStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.HSSF.UserModel.HSSFCellStyle bottomLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
bottomLineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.HSSF.UserModel.HSSFCellStyle leftLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
leftLineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.HSSF.UserModel.HSSFCellStyle rightLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
rightLineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.HSSF.UserModel.HSSFCellStyle upperLeftLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
upperLeftLineStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
upperLeftLineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.HSSF.UserModel.HSSFCellStyle bottomLeftLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
bottomLeftLineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
bottomLeftLineStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.HSSF.UserModel.HSSFCellStyle upperRightLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
upperRightLineStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
upperRightLineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
NPOI.HSSF.UserModel.HSSFCellStyle bottomRightLineStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
bottomRightLineStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
bottomRightLineStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
workSheet.CreateRow(0).CreateCell(0).SetCellValue("Year");
workSheet.GetRow(0).CreateCell(1).SetCellValue(year);
workSheet.CreateRow(1).CreateCell(0).SetCellValue("Month");
workSheet.GetRow(1).CreateCell(1).SetCellValue(month);
NPOI.HSSF.UserModel.HSSFRow headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW);
DBFilter hLevelFilter = new DBFilter();
hLevelFilter.add("HLevelSeqNo", true);
int HIERARCHYLEVEL_COLUMN = 0;
ArrayList hLevelList = EHierarchyLevel.db.select(dbConn, hLevelFilter);
for (int levelIndex = 0; levelIndex < hLevelList.Count; levelIndex++)
{
EHierarchyLevel hLevel = (EHierarchyLevel)hLevelList[levelIndex];
headerRow.CreateCell(HIERARCHYLEVEL_COLUMN + levelIndex).SetCellValue(hLevel.HLevelDesc);
}
int POSITION_COLUMN = headerRow.LastCellNum;
headerRow.CreateCell(POSITION_COLUMN).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Position"));
int EMPNO_COLUMN = headerRow.LastCellNum;
headerRow.CreateCell(EMPNO_COLUMN).SetCellValue(FIELD_EMP_NO);
headerRow.CreateCell(EMPNO_COLUMN + 1).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Name"));
headerRow.CreateCell(EMPNO_COLUMN + 2).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Alias"));
headerRow.CreateCell(EMPNO_COLUMN + 3).SetCellValue(HROne.Common.WebUtility.GetLocalizedString("Chinese Name"));
NPOI.HSSF.UserModel.HSSFCellStyle sundayStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
NPOI.HSSF.UserModel.HSSFFont sundayFont = (NPOI.HSSF.UserModel.HSSFFont)workBook.CreateFont();
sundayFont.Color = NPOI.HSSF.Util.HSSFColor.RED.index;
sundayStyle.SetFont(sundayFont);
Hashtable styleList = new Hashtable();
ArrayList availableRosterClientList = new ArrayList();
ArrayList availableRosterClientSiteList = new ArrayList();
#region Create Column Header
int ROSTER_DETAIL_COLUMN = headerRow.LastCellNum;
for (int i = 1; i <= DateTime.DaysInMonth(year, month); i++)
{
//workSheet.Cells.Add(HEADER_ROW, ROSTAER_DETAIL_COLUMN + i - 1,i);
NPOI.HSSF.UserModel.HSSFCell headerCell = (NPOI.HSSF.UserModel.HSSFCell)headerRow.CreateCell(ROSTER_DETAIL_COLUMN + i - 1);
headerCell.SetCellValue(i);
if (new DateTime(year, month, i).DayOfWeek == DayOfWeek.Sunday)
{
headerCell.CellStyle = sundayStyle;
}
}
#endregion
#region Create Employee Roster Detail
int recordCount = 0;
foreach (EEmpPersonalInfo empInfo in EmpInfoList)
{
if (EEmpPersonalInfo.db.select(dbConn, empInfo))
{
recordCount++;
//workSheet.Cells.Add(HEADER_ROW + recordCount, 1,empInfo.EmpNo);
//workSheet.Cells.Add(HEADER_ROW + recordCount, 2,empInfo.EmpEngFullName);
NPOI.HSSF.UserModel.HSSFRow detailRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(COLUMN_HEADER_ROW + recordCount);
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, dateEnd, empInfo.EmpID);
if (empPos != null)
{
for (int levelIndex = 0; levelIndex < hLevelList.Count; levelIndex++)
{
EHierarchyLevel hLevel = (EHierarchyLevel)hLevelList[levelIndex];
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);
if (empHierarchyList.Count > 0)
{
EEmpHierarchy empHierarchy = (EEmpHierarchy)empHierarchyList[0];
EHierarchyElement hElement = new EHierarchyElement();
hElement.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, hElement))
{
detailRow.CreateCell(HIERARCHYLEVEL_COLUMN + levelIndex).SetCellValue(hElement.HElementDesc);
}
}
}
EPosition position = new EPosition();
position.PositionID = empPos.PositionID;
if (EPosition.db.select(dbConn, position))
{
detailRow.CreateCell(POSITION_COLUMN).SetCellValue(position.PositionDesc);
}
}
detailRow.CreateCell(EMPNO_COLUMN).SetCellValue(empInfo.EmpNo);
detailRow.CreateCell(EMPNO_COLUMN + 1).SetCellValue(empInfo.EmpEngFullName);
detailRow.CreateCell(EMPNO_COLUMN + 2).SetCellValue(empInfo.EmpAlias);
detailRow.CreateCell(EMPNO_COLUMN + 3).SetCellValue(empInfo.EmpChiFullName);
DBFilter rosterTableFilter = new DBFilter();
rosterTableFilter.add(new Match("EmpID", empInfo.EmpID));
rosterTableFilter.add(new Match("RosterTableDate", ">=", dateStart));
rosterTableFilter.add(new Match("RosterTableDate", "<=", dateEnd));
ArrayList rosterTableList = ERosterTable.db.select(dbConn, rosterTableFilter);
foreach (ERosterTable rosterTable in rosterTableList)
{
ERosterCode rosterCode = new ERosterCode();
rosterCode.RosterCodeID = rosterTable.RosterCodeID;
if (ERosterCode.db.select(dbConn, rosterCode))
{
string value = string.Empty;
//if (workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellExists ((ushort)(ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1)) )
// value = workSheet.Rows[(ushort)(HEADER_ROW+ recordCount)].CellAtCol( (ushort)(ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1)).Value.ToString();
//if (string.IsNullOrEmpty(value))
// workSheet.Cells.Add(HEADER_ROW + recordCount, ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1,rosterCode.RosterCode);
//else
// workSheet.Cells.Add(HEADER_ROW + recordCount, ROSTAER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1,value + "|" + rosterCode.RosterCode);
int cellColIndex = ROSTER_DETAIL_COLUMN + rosterTable.RosterTableDate.Day - 1;
NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.GetCell(cellColIndex);
if (rosterCell == null)
{
rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(cellColIndex);
}
else
{
value = rosterCell.StringCellValue;
}
string rosterCodeValue = rosterCode.RosterCode;
if (!rosterTable.RosterTableOverrideInTime.Ticks.Equals(0) || !rosterTable.RosterTableOverrideOutTime.Ticks.Equals(0))
{
DateTime inTime = rosterCode.RosterCodeInTime;
DateTime outTime = rosterCode.RosterCodeOutTime;
if (!rosterTable.RosterTableOverrideInTime.Ticks.Equals(0))
{
inTime = rosterTable.RosterTableOverrideInTime;
}
if (!rosterTable.RosterTableOverrideOutTime.Ticks.Equals(0))
{
outTime = rosterTable.RosterTableOverrideOutTime;
}
rosterCodeValue += "(" + inTime.ToString("HHmm") + "~" + outTime.ToString("HHmm") + ")";
}
if (string.IsNullOrEmpty(value))
{
rosterCell.SetCellValue(rosterCodeValue);
//if (!string.IsNullOrEmpty(rosterCode.RosterCodeColorCode))
//{
// //System.Drawing.Color color = System.Drawing.ColorTranslator.FromHtml(rosterCode.RosterCodeColorCode);
// //System.Drawing.Color fontcolor = WebUtils.ComputeTextColor(color);
// //rosterCell.CellStyle.FillForegroundColor = workBook.GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).GetIndex();
// //rosterCell.CellStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
// //rosterCell.CellStyle.FillBackgroundColor = workBook.GetCustomPalette().FindSimilarColor(fontcolor.R, fontcolor.G, fontcolor.B).GetIndex();
// string styleCode = "RosterCode" + "_" + rosterCode.RosterCode;
// if (styleList.Contains(styleCode))
// rosterCell.CellStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)styleList[styleCode];
// else
// {
// NPOI.HSSF.UserModel.HSSFCellStyle rosterCodeStyle = (NPOI.HSSF.UserModel.HSSFCellStyle)workBook.CreateCellStyle();
// System.Drawing.Color color = System.Drawing.ColorTranslator.FromHtml(rosterCode.RosterCodeColorCode);
// System.Drawing.Color fontcolor = WebUtils.ComputeTextColor(color);
// rosterCodeStyle.FillForegroundColor = workBook.GetCustomPalette().FindSimilarColor(color.R, color.G, color.B).GetIndex();
// rosterCodeStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
// rosterCodeStyle.FillBackgroundColor = workBook.GetCustomPalette().FindSimilarColor(fontcolor.R, fontcolor.G, fontcolor.B).GetIndex();
// styleList.Add(styleCode, rosterCodeStyle);
// rosterCell.CellStyle = rosterCodeStyle;
// }
//}
}
else
{
rosterCell.SetCellValue(value + "|" + rosterCodeValue);
//rosterCell.CellStyle=workBook.GetCellStyleAt(0);
}
}
}
for (DateTime dateIndex = dateStart; dateIndex <= dateEnd; dateIndex = dateIndex.AddDays(1))
{
string value = string.Empty;
//if (workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellExists((ushort)(ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1)) )
// value = workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellAtCol((ushort)(ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1)).Value.ToString();
int cellColIndex = ROSTER_DETAIL_COLUMN + dateIndex.Day - 1;
NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.GetCell(cellColIndex);
if (rosterCell == null)
{
rosterCell = (NPOI.HSSF.UserModel.HSSFCell)detailRow.CreateCell(cellColIndex);
}
else
{
value = rosterCell.StringCellValue;
}
if (string.IsNullOrEmpty(value))
{
EEmpPositionInfo empPosInfo = AppUtils.GetLastPositionInfo(dbConn, dateIndex, empInfo.EmpID);
if (empPosInfo != null)
{
if ((empPosInfo.EmpPosEffTo.Ticks.Equals(0) || dateIndex <= empPosInfo.EmpPosEffTo))
{
EWorkHourPattern workPattern = new EWorkHourPattern();
workPattern.WorkHourPatternID = empPosInfo.WorkHourPatternID;
if (EWorkHourPattern.db.select(dbConn, workPattern))
{
ERosterCode rosterCode = new ERosterCode();
rosterCode.RosterCodeID = workPattern.GetDefaultRosterCodeID(dbConn, dateIndex);
if (ERosterCode.db.select(dbConn, rosterCode))
{
//workSheet.Cells.Add(HEADER_ROW + recordCount, ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1, rosterCode.RosterCode);
rosterCell.SetCellValue(rosterCode.RosterCode);
}
}
DBFilter empRosterTableGroupListFilter = new DBFilter();
empRosterTableGroupListFilter.add(new Match("EmpID", empInfo.EmpID));
empRosterTableGroupListFilter.add(new Match("empRosterTableGroupEffFr", "<=", dateIndex));
OR orEmpPosEffToTerms = new OR();
orEmpPosEffToTerms.add(new Match("empRosterTableGroupEffTo", ">=", dateIndex));
orEmpPosEffToTerms.add(new NullTerm("empRosterTableGroupEffTo"));
empRosterTableGroupListFilter.add(orEmpPosEffToTerms);
ArrayList empRosterTableGroupList = EEmpRosterTableGroup.db.select(dbConn, empRosterTableGroupListFilter);
foreach (EEmpRosterTableGroup empRosterTableGroup in empRosterTableGroupList)
{
ERosterTableGroup rosterTableGroup = new ERosterTableGroup();
rosterTableGroup.RosterTableGroupID = empRosterTableGroup.RosterTableGroupID;
if (ERosterTableGroup.db.select(dbConn, rosterTableGroup))
{
if (rosterTableGroup.RosterClientSiteID > 0)
{
if (!availableRosterClientSiteList.Contains(rosterTableGroup.RosterClientSiteID))
{
availableRosterClientSiteList.Add(rosterTableGroup.RosterClientSiteID);
}
}
else if (rosterTableGroup.RosterClientID > 0)
{
if (!availableRosterClientList.Contains(rosterTableGroup.RosterClientID))
{
availableRosterClientList.Add(rosterTableGroup.RosterClientID);
}
}
}
}
}
}
}
}
DBFilter leaveAppFilter = new DBFilter();
leaveAppFilter.add(new Match("EmpID", empInfo.EmpID));
leaveAppFilter.add(new Match("LeaveAppDateTo", ">=", dateStart));
leaveAppFilter.add(new Match("LeaveAppDateFrom", "<=", dateEnd));
ArrayList leaveAppList = ELeaveApplication.db.select(dbConn, leaveAppFilter);
foreach (ELeaveApplication leaveApp in leaveAppList)
{
ELeaveCode leaveCode = new ELeaveCode();
leaveCode.LeaveCodeID = leaveApp.LeaveCodeID;
if (ELeaveCode.db.select(dbConn, leaveCode))
{
//if (leaveCode.LeaveCodeColorCode.Length == 6)
//{
// try
// {
// int red = System.Int32.Parse(leaveCode.LeaveCodeColorCode.Substring(0, 2), System.Globalization.NumberStyles.AllowHexSpecifier);
// int green = System.Int32.Parse(leaveCode.LeaveCodeColorCode.Substring(2, 2), System.Globalization.NumberStyles.AllowHexSpecifier);
// int blue = System.Int32.Parse(leaveCode.LeaveCodeColorCode.Substring(4, 2), System.Globalization.NumberStyles.AllowHexSpecifier);
// string Color = System.Drawing.Color.FromArgb(red, green, blue).ToKnownColor().ToString();
// for (DateTime dateIndex = leaveApp.LeaveAppDateFrom; dateIndex <= leaveApp.LeaveAppDateTo; dateIndex = dateIndex.AddDays(1))
// {
// org.in2bits.MyXls.Cell cell = workSheet.Rows[(ushort)(HEADER_ROW + recordCount)].CellAtCol((ushort)(ROSTAER_DETAIL_COLUMN + dateIndex.Day - 1));
// //cell.Pattern = 1;
// //cell.PatternColor = org.in2bits.MyXls.Colors.Yellow;
// }
// }
// catch
// {
// }
//}
}
}
}
}
#endregion
#region Create Roster Code Liet
OR orRosterCodeTerm = new OR();
foreach (int rosterClientID in availableRosterClientList)
{
AND andRosterCodeTerms = new AND();
orRosterCodeTerm.add(new Match("RosterClientID", rosterClientID));
}
foreach (int rosterClientSiteID in availableRosterClientSiteList)
{
AND andRosterCodeTerms = new AND();
orRosterCodeTerm.add(new Match("RosterClientSiteID", rosterClientSiteID));
}
orRosterCodeTerm.add(new Match("RosterClientID", 0));
DBFilter rosterCodeListFilter = new DBFilter();
rosterCodeListFilter.add(orRosterCodeTerm);
rosterCodeListFilter.add("RosterCode", true);
ArrayList rosterCodeList = ERosterCode.db.select(dbConn, rosterCodeListFilter);
int ROSTER_CODE_START_ROW = COLUMN_HEADER_ROW + recordCount + 5;
int rosterCodeCount = 0;
int maxColumnCount = 3;
int columnCellWidth = 9;
int maxRowCount = (int)(rosterCodeList.Count / maxColumnCount) + (rosterCodeList.Count % maxColumnCount == 0 ? 0 : 1);
foreach (ERosterCode rosterCode in rosterCodeList)
{
int currentRowNum = rosterCodeCount % maxRowCount;
int currentColumnNum = (rosterCodeCount / maxRowCount) * columnCellWidth;
rosterCodeCount++;
NPOI.HSSF.UserModel.HSSFRow rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(ROSTER_CODE_START_ROW + currentRowNum);
if (rosterCodeRow == null)
{
rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(ROSTER_CODE_START_ROW + currentRowNum);
}
NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(ROSTER_DETAIL_COLUMN + currentColumnNum);
rosterCell.SetCellValue(rosterCode.RosterCode);
rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(ROSTER_DETAIL_COLUMN + currentColumnNum + 1);
rosterCell.SetCellValue(rosterCode.RosterCodeDesc);
if (rosterCodeCount.Equals(1))
{
rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(ROSTER_DETAIL_COLUMN - 1);
rosterCell.SetCellValue("Code:");
}
}
for (int rowIdx = ROSTER_CODE_START_ROW - 1; rowIdx < ROSTER_CODE_START_ROW + maxRowCount + 1; rowIdx++)
{
NPOI.HSSF.UserModel.HSSFRow rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(rowIdx);
if (rosterCodeRow == null)
{
rosterCodeRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.CreateRow(rowIdx);
}
if (rowIdx == ROSTER_CODE_START_ROW - 1)
{
for (int colIdx = ROSTER_DETAIL_COLUMN - 1; colIdx < ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth; colIdx++)
{
NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.GetCell(colIdx);
if (rosterCell == null)
{
rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(colIdx);
}
if (colIdx == ROSTER_DETAIL_COLUMN - 1)
{
rosterCell.CellStyle = upperLeftLineStyle;
}
else if (colIdx == ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth - 1)
{
rosterCell.CellStyle = upperRightLineStyle;
}
else
{
rosterCell.CellStyle = upperLineStyle;
}
}
}
else if (rowIdx == ROSTER_CODE_START_ROW + maxRowCount)
{
for (int colIdx = ROSTER_DETAIL_COLUMN - 1; colIdx < ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth; colIdx++)
{
NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.GetCell(colIdx);
if (rosterCell == null)
{
rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(colIdx);
}
if (colIdx == ROSTER_DETAIL_COLUMN - 1)
{
rosterCell.CellStyle = bottomLeftLineStyle;
}
else if (colIdx == ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth - 1)
{
rosterCell.CellStyle = bottomRightLineStyle;
}
else
{
rosterCell.CellStyle = bottomLineStyle;
}
}
}
else
{
for (int colIdx = ROSTER_DETAIL_COLUMN - 1; colIdx < ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth; colIdx++)
{
NPOI.HSSF.UserModel.HSSFCell rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.GetCell(colIdx);
if (rosterCell == null)
{
rosterCell = (NPOI.HSSF.UserModel.HSSFCell)rosterCodeRow.CreateCell(colIdx);
}
if (colIdx == ROSTER_DETAIL_COLUMN - 1)
{
rosterCell.CellStyle = leftLineStyle;
}
else if (colIdx == ROSTER_DETAIL_COLUMN + maxColumnCount * columnCellWidth - 1)
{
rosterCell.CellStyle = rightLineStyle;
}
//else
// rosterCell.CellStyle = bottomLineStyle;
}
}
}
#endregion
//doc.FileName = exportFileName;
//doc.Save();
System.IO.FileStream file = new System.IO.FileStream(exportFileName, System.IO.FileMode.Create);
workBook.Write(file);
file.Close();
WebUtils.TransmitFile(Response, exportFileName, "RosterTable_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
Response.End();
}
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;
}
public override ReportDocument GenerateReport()
{
DataSet.EmpTerminationListSet dataSet = new DataSet.EmpTerminationListSet();
string strPrintPeriod = string.Empty;
DataSet.EmpTerminationListSet.TerminationListDataTable dataTable = dataSet.TerminationList;
DBFilter hierarchyLevelFilter = new DBFilter();
hierarchyLevelFilter.add("HLevelSeqNo", true);
ArrayList hierarchyLevelList = EHierarchyLevel.db.select(dbConn, hierarchyLevelFilter);
foreach (EEmpTermination empTermination in EmpTermList)
{
EEmpTermination.db.select(dbConn, empTermination);
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = empTermination.EmpID;
EEmpPersonalInfo.db.select(dbConn, empInfo);
DBFilter positionFilter = new DBFilter();
positionFilter.add(new Match("EmpID", empInfo.EmpID));
//if (lngPayPeriodFr != 0 && lngPayPeriodTo != 0)
//{
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);
//}
positionFilter.add("EmpPosEffFr", false);
ArrayList empPositionInfos = EEmpPositionInfo.db.select(dbConn, positionFilter);
EEmpPositionInfo empPositionInfo;
if (empPositionInfos.Count > 0)
{
empPositionInfo = (EEmpPositionInfo)empPositionInfos[0];
}
else
{
empPositionInfo = new EEmpPositionInfo();
}
ECompany company = new ECompany();
company.CompanyID = empPositionInfo.CompanyID;
ECompany.db.select(dbConn, company);
EPosition position = new EPosition();
position.PositionID = empPositionInfo.PositionID;
EPosition.db.select(dbConn, position);
ECessationReason cessationReason = new ECessationReason();
cessationReason.CessationReasonID = empTermination.CessationReasonID;
ECessationReason.db.select(dbConn, cessationReason);
string businessHierarchy = string.Empty;
foreach (EHierarchyLevel hLevel in hierarchyLevelList)
{
DBFilter empHierarchyFilter = new DBFilter();
empHierarchyFilter.add(new Match("EmpPosID", empPositionInfo.EmpPosID));
empHierarchyFilter.add(new Match("HLevelID", hLevel.HLevelID));
ArrayList empHierarchyList = EEmpHierarchy.db.select(dbConn, empHierarchyFilter);
foreach (EEmpHierarchy empHierarchy in empHierarchyList)
{
EHierarchyElement obj = new EHierarchyElement();
obj.HElementID = empHierarchy.HElementID;
if (EHierarchyElement.db.select(dbConn, obj))
{
if (string.IsNullOrEmpty(businessHierarchy))
{
businessHierarchy = obj.HElementDesc;
}
else
{
businessHierarchy += "\t" + obj.HElementDesc;
}
}
}
}
//DBFilter HierarchyElementFilter = new DBFilter();
//System.Data.DataTable hierarchyElementTable = HierarchyElementFilter.loadData("Select HE.*, HL.HLevelSeqNo from HierarchyElement he left join HierarchyLevel hl on he.HLevelID=hl.HLevelID where HElementID in (Select HElementID from EmpHierarchy where EmpPosID=" + empPositionInfo.EmpPosID + ") order by HLevelSeqNo");
//foreach (System.Data.DataRow hierarchyElementRow in hierarchyElementTable.Rows)
//{
// EHierarchyElement obj = new EHierarchyElement();
// EHierarchyElement.db.toObject(hierarchyElementRow, obj);
// if (string.IsNullOrEmpty(businessHierarchy))
// businessHierarchy = obj.HElementDesc;
// else
// businessHierarchy += "\t" + obj.HElementDesc;
//}
DataSet.EmpTerminationListSet.TerminationListRow row = dataTable.NewTerminationListRow();
row.EmpID = empInfo.EmpID;
row.EmpNo = empInfo.EmpNo;
row.CompanyID = company.CompanyID;
row.CompanyName = company.CompanyName;
row.EmpName = empInfo.EmpEngFullName;
row.BusinessHierarchy = businessHierarchy;
row.Position = position.PositionDesc;
row.TerminationCode = cessationReason.CessationReasonCode;
row.TerminationDesc = cessationReason.CessationReasonDesc;
row.LastEmploymentDate = empTermination.EmpTermLastDate;
dataTable.Rows.Add(row);
}
if (reportDocument == null)
{
reportDocument = new ReportTemplate.Report_Employee_TerminationList();
//reportDocument.Load(@"ReportTemplate\Report_Payroll_DiscrepancyList.rpt");
}
else
{
}
reportDocument.SetDataSource(dataSet);
return(reportDocument);
}
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 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 void GenerateDetailByEmpID()
{
DataTable empInfoTable = dataSet.Tables["EmpInfo"];
DataTable dataTable = dataSet.Tables["PaySlip"];
foreach (EEmpPersonalInfo empInfo in EmpList)
{
string empDiv = string.Empty;
string empDep = string.Empty;
EEmpPersonalInfo.db.select(dbConn, empInfo);
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, empInfo.EmpDateOfJoin, empInfo.EmpID);
if (empPos == null)
{
empPos = new EEmpPositionInfo();
}
EPosition position = new EPosition();
ECompany company = new ECompany();
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;
}
}
}
}
DBFilter basicSalaryRPFilter = new DBFilter();
basicSalaryRPFilter.add(new Match("EmpID", empInfo.EmpID));
basicSalaryRPFilter.add(new Match("EmpRPEffFr", "<=", empInfo.EmpDateOfJoin));
OR orbasicSalaryRPToTerm = new OR();
orbasicSalaryRPToTerm.add(new Match("EmpRPEffTo", ">=", empInfo.EmpDateOfJoin));
orbasicSalaryRPToTerm.add(new NullTerm("EmpRPEffTo"));
basicSalaryRPFilter.add(orbasicSalaryRPToTerm);
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("PayPeriodFr", "<=", empInfo.EmpDateOfJoin));
payPeriodFilter.add(new Match("PayPeriodTo", ">=", empInfo.EmpDateOfJoin));
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);
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];
}
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["CompanyName"] = company.CompanyName;
empInfoRow["PositionDesc"] = position.PositionDesc;
empInfoRow["BasicSalary"] = basicSalaryRP.EmpRPAmount;
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;
}
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);
}
}
protected override System.Data.DataSet CreateDataSource()
{
System.Data.DataSet dataSet = new System.Data.DataSet();
DataTable empInfoTable = new DataTable("EmpInfo");
DataTable payrollTable = new DataTable("Payment");
DataTable hierarchyTable = new DataTable("hierarchy");
DataTable payPeriodTable = new DataTable("payPeriod");
dataSet.Tables.Add(empInfoTable);
dataSet.Tables.Add(payrollTable);
dataSet.Tables.Add(hierarchyTable);
dataSet.Tables.Add(payPeriodTable);
payPeriodTable.Columns.Add("payPeriodFr", typeof(DateTime));
hierarchyTable.Columns.Add("LevelDesc", typeof(string));
payrollTable.Columns.Add("EmpPayrollID", typeof(int));
payrollTable.Columns.Add("empID", typeof(int));
payrollTable.Columns.Add("payPeriodFr", typeof(DateTime));
payrollTable.Columns.Add("netAmount", typeof(double));
payrollTable.Columns.Add("EE", typeof(double));
payrollTable.Columns.Add("ER", typeof(double));
int firstDetailColumnPos = payrollTable.Columns.Count;
empInfoTable.Columns.Add("Employee No.", typeof(string));
empInfoTable.Columns.Add("EmpPayrollID", typeof(int));
empInfoTable.Columns.Add("Employee Name", typeof(string));
empInfoTable.Columns.Add("Alias", typeof(string));
empInfoTable.Columns.Add("Chinese Name", typeof(string));
empInfoTable.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)
{
if (hlevel.HLevelID.Equals(intHierarchyLevelID))
{
hierarchyLevelGroupingFieldName = hlevel.HLevelDesc;
}
empInfoTable.Columns.Add(hlevel.HLevelDesc, typeof(string));
hierarchyLevelHashTable.Add(hlevel.HLevelID, hlevel);
DataRow m_hierarchyRow = hierarchyTable.NewRow();
m_hierarchyRow["LevelDesc"] = hlevel.HLevelDesc;
hierarchyTable.Rows.Add(m_hierarchyRow);
}
empInfoTable.Columns.Add("EmpID", typeof(int));
empInfoTable.Columns.Add("Payroll Group", typeof(string));
empInfoTable.Columns.Add("Date Join", typeof(DateTime));
empInfoTable.Columns.Add("Date Left", typeof(DateTime));
empInfoTable.Columns.Add("Net Payable", typeof(double));
empInfoTable.Columns.Add("MCEE", typeof(double));
empInfoTable.Columns.Add("MCER", typeof(double));
empInfoTable.Columns.Add("VCEE", typeof(double));
empInfoTable.Columns.Add("VCER", typeof(double));
empInfoTable.Columns.Add("PFundEE", typeof(double));
empInfoTable.Columns.Add("PFundER", typeof(double));
DBFilter payPeriodFilter = new DBFilter();
if (!PeriodFrom.Ticks.Equals(0))
{
payPeriodFilter.add(new Match("PayPeriodTo", ">=", PeriodFrom));
}
if (!PeriodTo.Ticks.Equals(0))
{
payPeriodFilter.add(new Match("PayPeriodTo", "<=", PeriodTo));
}
ArrayList payPeriodList = EPayrollPeriod.db.select(dbConn, payPeriodFilter);
if (payPeriodList.Count > 0)
{
// reset period
PeriodFrom = new DateTime();
PeriodTo = new DateTime();
}
DBFilter m_userCompanyFilter = new DBFilter();
m_userCompanyFilter.add(new Match("UserID", currentUser.UserID));
DBFilter m_userRankFilter = new DBFilter();
m_userRankFilter.add(new Match("UserID", currentUser.UserID));
Hashtable m_userCompanyList = new Hashtable();
foreach (EUserCompany m_userCompany in EUserCompany.db.select(dbConn, m_userCompanyFilter))
{
m_userCompanyList.Add(m_userCompany.CompanyID, m_userCompany.CompanyID);
}
Hashtable m_userRankList = new Hashtable();
foreach (EUserRank m_userRank in EUserRank.db.select(dbConn, m_userRankFilter))
{
m_userRankList.Add(m_userRank.RankID, m_userRank.RankID);
}
int[] m_EmpIDList = new int[empList.Count];
int i = 0;
foreach (EEmpPersonalInfo m_info in empList)
{
m_EmpIDList[i] = m_info.EmpID;
i++;
}
foreach (EPayrollPeriod payPeriod in payPeriodList)
{
DataRow[] m_payPeriodRows = payPeriodTable.Select("payPeriodFr = '" + ((DateTime)payPeriod.PayPeriodFr).ToString("yyyy-MM-dd") + "' ");
if (m_payPeriodRows.Length <= 0)
{
DataRow m_payPeriodRow = payPeriodTable.NewRow();
m_payPeriodRow["payPeriodFr"] = payPeriod.PayPeriodFr;
payPeriodTable.Rows.Add(m_payPeriodRow);
}
if (PeriodFrom > payPeriod.PayPeriodFr || PeriodFrom.Ticks.Equals(0))
{
PeriodFrom = payPeriod.PayPeriodFr;
}
if (PeriodTo < payPeriod.PayPeriodTo || PeriodTo.Ticks.Equals(0))
{
PeriodTo = payPeriod.PayPeriodTo;
}
DBFilter empPayrollFilter = new DBFilter();
empPayrollFilter.add(new Match("PayPeriodID", payPeriod.PayPeriodID));
empPayrollFilter.add(new IN("EmpID", m_EmpIDList));
ArrayList empPayrollList = EEmpPayroll.db.select(dbConn, empPayrollFilter);
foreach (EEmpPayroll empPayroll in empPayrollList)
{
DataRow paymentRow = payrollTable.NewRow();
DataRow[] m_existingEmpID = payrollTable.Select("EmpID = " + empPayroll.EmpID.ToString());
if (m_existingEmpID.Length == 0)
{
EEmpPersonalInfo empInfo = new EEmpPersonalInfo();
empInfo.EmpID = empPayroll.EmpID;
if (EEmpPersonalInfo.db.select(dbConn, empInfo) == false)
{
continue;
}
DataRow row = empInfoTable.NewRow();
row["EmpID"] = empInfo.EmpID;
row["Employee No."] = empInfo.EmpNo;
row["EmpPayrollID"] = empPayroll.EmpPayrollID;
row["Employee Name"] = empInfo.EmpEngFullName;
row["Alias"] = empInfo.EmpAlias;
row["Chinese Name"] = empInfo.EmpChiFullName;
row["Date Join"] = empInfo.EmpDateOfJoin;
DBFilter empTerminationFilter = new DBFilter();
empTerminationFilter.add(new Match("EmpID", empInfo.EmpID));
ArrayList empTerminationList = EEmpTermination.db.select(dbConn, empTerminationFilter);
if (empTerminationList.Count > 0)
{
row["Date Left"] = ((EEmpTermination)empTerminationList[0]).EmpTermLastDate;
}
DBFilter empPosFilter = new DBFilter();
EEmpPositionInfo empPos = AppUtils.GetLastPositionInfo(dbConn, payPeriod.PayPeriodTo, empInfo.EmpID);
if (empPos != null)
{
if (!m_userCompanyList.Contains(empPos.CompanyID) || !m_userRankList.Contains(empPos.RankID))
{
continue;
}
if (selectedCompanyID > 0 && empPos.CompanyID != selectedCompanyID)
{
continue;
}
ECompany company = new ECompany();
company.CompanyID = empPos.CompanyID;
if (ECompany.db.select(dbConn, company))
{
row["Company"] = company.CompanyName;
}
EPayrollGroup payrollGroup = new EPayrollGroup();
payrollGroup.PayGroupID = empPos.PayGroupID;
if (EPayrollGroup.db.select(dbConn, payrollGroup))
{
row["Payroll Group"] = payrollGroup.PayGroupDesc;
}
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;
}
}
}
}
empInfoTable.Rows.Add(row);
}
double netAmount = 0;
DBFilter paymentRecordFilter = new DBFilter();
paymentRecordFilter.add(new Match("EmpPayrollID", empPayroll.EmpPayrollID));
paymentRecordFilter.add(new Match("PayRecStatus", "A"));
foreach (EPaymentRecord paymentRecord in EPaymentRecord.db.select(dbConn, paymentRecordFilter))
{
netAmount += paymentRecord.PayRecActAmount;
}
paymentRow["EmpID"] = empPayroll.EmpID;
paymentRow["EmpPayrollID"] = empPayroll.EmpPayrollID;
paymentRow["payPeriodFr"] = payPeriod.PayPeriodFr;
paymentRow["netAmount"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(netAmount, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
DBFilter mpfRecordFilter = new DBFilter();
mpfRecordFilter.add(new Match("EmpPayrollID", empPayroll.EmpPayrollID));
double m_ee = 0;
double m_er = 0;
foreach (EMPFRecord mpfRecord in EMPFRecord.db.select(dbConn, mpfRecordFilter))
{
m_ee += (mpfRecord.MPFRecActVCEE + mpfRecord.MPFRecActMCEE);
m_er += (mpfRecord.MPFRecActVCER + mpfRecord.MPFRecActMCER);
}
ArrayList orsoRecords = EORSORecord.db.select(dbConn, mpfRecordFilter);
foreach (EORSORecord orsoRecord in orsoRecords)
{
m_ee += orsoRecord.ORSORecActEE;
m_er += orsoRecord.ORSORecActER;
}
paymentRow["EE"] = m_ee;
paymentRow["ER"] = m_er;
payrollTable.Rows.Add(paymentRow);
}
}
//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 (payrollTable.Columns.Contains(paymentCode.PaymentCodeDesc))
// payrollTable.Columns[paymentCode.PaymentCodeDesc].SetOrdinal(firstDetailColumnPos);
//}
return(dataSet);
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
PageErrors errors = PageErrors.getErrors(db, Page.Master);
errors.clear();
ArrayList list = WebUtils.SelectedRepeaterItemToBaseObjectList(EEmpPersonalInfo.db, Repeater, "ItemSelect");
if (list.Count > 0)
{
const string PAYMENTCODE_PREFIX = "[Payment] ";
string exportFileName = System.IO.Path.GetTempFileName();
System.IO.File.Delete(exportFileName);
exportFileName += ".xls";
HROne.Export.ExcelExport export = new HROne.Export.ExcelExport(exportFileName);
DataSet dataSet = new DataSet();
DataTable dataTable = new DataTable("Payroll$");
dataSet.Tables.Add(dataTable);
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("Payroll Group", typeof(string));
dataTable.Columns.Add("Position", typeof(string));
dataTable.Columns.Add("EmpNo", typeof(string));
dataTable.Columns.Add("English Name", typeof(string));
dataTable.Columns.Add("Chinese Name", typeof(string));
dataTable.Columns.Add("HKID", typeof(string));
dataTable.Columns.Add("From", typeof(DateTime));
dataTable.Columns.Add("To", typeof(DateTime));
int firstSummaryColumnPos = dataTable.Columns.Count;
dataTable.Columns.Add("Net Payment", typeof(double));
int firstDetailColumnPos = dataTable.Columns.Count;
foreach (EEmpPersonalInfo empInfo in list)
{
EEmpPersonalInfo.db.select(dbConn, empInfo);
DBFilter empPayrollFilterForPayrollPeriod = new DBFilter();
empPayrollFilterForPayrollPeriod.add(new Match("ep.EmpID", empInfo.EmpID));
empPayrollFilterForPayrollPeriod.add(Payroll_PeriodSelectionList1.GetEmpPayrollDBTerm());
DBFilter payPeriodFilter = new DBFilter();
payPeriodFilter.add(new IN("PayPeriodID", "SELECT PayPeriodID from " + EEmpPayroll.db.dbclass.tableName + " ep", empPayrollFilterForPayrollPeriod));
ArrayList payPeriodList = EPayrollPeriod.db.select(dbConn, payPeriodFilter);
foreach (EPayrollPeriod payPeriod in payPeriodList)
{
if (EPayrollPeriod.db.select(dbConn, payPeriod))
{
EPayrollGroup payrollGroup = new EPayrollGroup();
payrollGroup.PayGroupID = payPeriod.PayGroupID;
EPayrollGroup.db.select(dbConn, payrollGroup);
DataRow row = dataTable.NewRow();
row["EmpNo"] = empInfo.EmpNo;
row["English Name"] = empInfo.EmpEngFullName;
row["Chinese Name"] = empInfo.EmpChiFullName;
row["HKID"] = empInfo.EmpHKID;
row["From"] = payPeriod.PayPeriodFr;
row["To"] = payPeriod.PayPeriodTo;
row["Payroll Group"] = payrollGroup.PayGroupDesc;
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["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.HElementDesc;
}
}
}
EPosition position = new EPosition();
position.PositionID = empPos.PositionID;
if (EPosition.db.select(dbConn, position))
{
row["Position"] = position.PositionDesc;
}
}
double netAmount = 0;
DBFilter empPayrollFilterForPaymentRecord = new DBFilter(empPayrollFilterForPayrollPeriod);
empPayrollFilterForPaymentRecord.add(new Match("PayPeriodID", payPeriod.PayPeriodID));
DBFilter paymentRecordFilter = new DBFilter();
paymentRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from " + EEmpPayroll.db.dbclass.tableName + " ep ", empPayrollFilterForPaymentRecord));
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;
}
row["Net Payment"] = HROne.CommonLib.GenericRoundingFunctions.RoundingTo(netAmount, ExchangeCurrency.DefaultCurrencyDecimalPlaces(), ExchangeCurrency.DefaultCurrencyDecimalPlaces());
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
{
System.Diagnostics.Debug.Write("System reserved payment column is used");
}
}
}
for (int i = 0; i < firstDetailColumnPos; i++)
{
dataTable.Columns[i].ColumnName = HROne.Common.WebUtility.GetLocalizedString(dataTable.Columns[i].ColumnName);
}
for (int i = firstSummaryColumnPos; i < firstDetailColumnPos; i++)
{
dataTable.Columns[firstSummaryColumnPos].SetOrdinal(dataTable.Columns.Count - 1);
}
export.Update(dataSet);
WebUtils.TransmitFile(Response, exportFileName, "PaymentListWaiJi_" + AppUtils.ServerDateTime().ToString("yyyyMMddHHmmss") + ".xls", true);
return;
}
else
{
errors.addError("Employee not selected");
}
}
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);
}
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");
}
}
