//public System.Data.DataSet GenerateDataSet()
//{
//DataSet.Payroll_MPFRemittanceStatement dataSet = new DataSet.Payroll_MPFRemittanceStatement();
//DataSet.Payroll_MPFRemittanceStatement.ExistingMemberDataTable existingMPF = dataSet.ExistingMember;
//DataSet.Payroll_MPFRemittanceStatement.NewJoinMemberDataTable newJoinMPF = dataSet.NewJoinMember;
//additionalMPF = dataSet.AdditionalMember;
//DataSet.Payroll_MPFRemittanceStatement.MPFPlanDataTable mpfPlan = dataSet.MPFPlan;
//DataSet.Payroll_MPFRemittanceStatement.MPFSchemeDataTable mpfScheme = dataSet.MPFScheme;
//string strPrintPeriod = PayPeriodFr.ToString("yyyy-MM-dd") + " - " + PayPeriodTo.ToString("yyyy-MM-dd");
//DBFilter payPeriodFilter = new DBFilter();
//payPeriodFilter.add(new Match("pp.PayPeriodFr", "<=", PayPeriodTo));
//payPeriodFilter.add(new Match("pp.PayPeriodTo", ">=", PayPeriodFr));
//foreach (EEmpPersonalInfo empInfo in EmpList)
//{
//EEmpPersonalInfo.db.select(dbConn, empInfo);
//DBFilter empPayrollFilter = new DBFilter();
//empPayrollFilter.add(new Match("EmpID", empInfo.EmpID));
//empPayrollFilter.add(new IN("PayPeriodID", "Select PayPeriodID from PayrollPeriod pp", payPeriodFilter));
//DBFilter mpfRecordFilter = new DBFilter();
//mpfRecordFilter.add(new IN("EmpPayrollID", "Select EmpPayrollID from EmpPayroll", empPayrollFilter));
//mpfRecordFilter.add(new Match("MPFPlanID", MPFPlanID));
//mpfRecordFilter.add("MPfRecPeriodFr", true);
//mpfRecordFilter.add("MPfRecPeriodTo", true);
//ArrayList mpfRecords = EMPFRecord.db.select(dbConn, mpfRecordFilter);
//DataSet.Payroll_MPFRemittanceStatement.ExistingMemberRow existingMPFRow = null;
//DataSet.Payroll_MPFRemittanceStatement.NewJoinMemberRow newJoinMPFRow = null;
//foreach (EMPFRecord mpfRecord in mpfRecords)
//{
// EMPFPlan mpfPlanObject = new EMPFPlan();
// mpfPlanObject.MPFPlanID = mpfRecord.MPFPlanID;
// if (EMPFPlan.db.select(dbConn, mpfPlanObject))
// {
// if (mpfPlan.Select("MPFPlanID=" + mpfPlanObject.MPFPlanID).Length == 0)
// {
// DataSet.Payroll_MPFRemittanceStatement.MPFPlanRow mpfPlanRow = mpfPlan.NewMPFPlanRow();
// mpfPlanRow.MPFPlanID = mpfPlanObject.MPFPlanID;
// mpfPlanRow.MPFPlanCode = mpfPlanObject.MPFPlanCode;
// mpfPlanRow.MPFPlanCompanyAddress = mpfPlanObject.MPFPlanCompanyAddress;
// mpfPlanRow.MPFPlanCompanyName = mpfPlanObject.MPFPlanCompanyName;
// mpfPlanRow.MPFPlanContactName = mpfPlanObject.MPFPlanContactName;
// mpfPlanRow.MPFPlanContactNo = mpfPlanObject.MPFPlanContactNo;
// mpfPlanRow.MPFPlanDesc = mpfPlanObject.MPFPlanDesc;
// mpfPlanRow.MPFPlanParticipationNo = mpfPlanObject.MPFPlanParticipationNo;
// mpfPlanRow.MPFSchemeID = mpfPlanObject.MPFSchemeID;
// if (!string.IsNullOrEmpty(mpfPlanObject.MPFPlanExtendData))
// {
// System.Xml.XmlNodeList payCenterList = Utility.GetXmlDocumentByDataString(mpfPlanObject.MPFPlanExtendData).DocumentElement.GetElementsByTagName("MPFPlanPayCenter");
// if (payCenterList.Count > 0)
// {
// mpfPlanRow.MPFHSBCPayCenter = payCenterList[0].InnerText;
// }
// }
// EMPFScheme mpfSchemeObject = new EMPFScheme();
// mpfSchemeObject.MPFSchemeID = mpfPlanObject.MPFSchemeID;
// if (EMPFScheme.db.select(dbConn, mpfSchemeObject))
// {
// if (mpfScheme.Select("MPFSchemeID=" + mpfSchemeObject.MPFSchemeID).Length == 0)
// {
// DataSet.Payroll_MPFRemittanceStatement.MPFSchemeRow schemeRow = mpfScheme.NewMPFSchemeRow();
// schemeRow.MPFSchemeCode = mpfSchemeObject.MPFSchemeCode;
// schemeRow.MPFSchemeDesc = mpfSchemeObject.MPFSchemeDesc;
// schemeRow.MPFSchemeID = mpfSchemeObject.MPFSchemeID;
// mpfScheme.Rows.Add(schemeRow);
// }
// }
// mpfPlan.Rows.Add(mpfPlanRow);
// }
// }
// EEmpPayroll empPayroll = new EEmpPayroll();
// empPayroll.EmpPayrollID = mpfRecord.EmpPayrollID;
// EEmpPayroll.db.select(dbConn, empPayroll);
// EPayrollPeriod payrollPeriod = new EPayrollPeriod();
// payrollPeriod.PayPeriodID = empPayroll.PayPeriodID;
// EPayrollPeriod.db.select(dbConn, payrollPeriod);
// if (mpfRecord.MPFRecType.Equals("N"))
// {
// if (newJoinMPFRow == null)
// {
// newJoinMPFRow = newJoinMPF.NewNewJoinMemberRow();
// LoadNewJoinMemberRowInfo(empInfo, mpfRecord, newJoinMPFRow);
// }
// else
// {
// if (!(newJoinMPFRow.EmpID == empInfo.EmpID && newJoinMPFRow.PeriodFrom.Equals(mpfRecord.MPFRecPeriodFr) && newJoinMPFRow.PeriodTo.Equals(mpfRecord.MPFRecPeriodTo)))
// {
// newJoinMPF.Rows.Add(newJoinMPFRow);
// newJoinMPFRow = newJoinMPF.NewNewJoinMemberRow();
// LoadNewJoinMemberRowInfo(empInfo, mpfRecord, newJoinMPFRow);
// }
// }
// newJoinMPFRow.RelevantIncome += mpfRecord.MPFRecActMCRI;
// newJoinMPFRow.MCEE += mpfRecord.MPFRecActMCEE;
// newJoinMPFRow.VCEE += mpfRecord.MPFRecActVCEE;
// newJoinMPFRow.MCER += mpfRecord.MPFRecActMCER;
// newJoinMPFRow.VCER += mpfRecord.MPFRecActVCER;
// }
// else if (mpfRecord.MPFRecType.Equals("A"))
// {
// if (newJoinMPFRow != null)
// if (newJoinMPFRow.EmpID == empInfo.EmpID)
// {
// if (!(newJoinMPFRow.PeriodFrom.Equals(mpfRecord.MPFRecPeriodFr) && newJoinMPFRow.PeriodTo.Equals(mpfRecord.MPFRecPeriodTo)))
// {
// newJoinMPF.Rows.Add(newJoinMPFRow);
// newJoinMPFRow = newJoinMPF.NewNewJoinMemberRow();
// LoadNewJoinMemberRowInfo(empInfo, mpfRecord, newJoinMPFRow);
// }
// newJoinMPFRow.RelevantIncome += mpfRecord.MPFRecActMCRI;
// newJoinMPFRow.MCEE += mpfRecord.MPFRecActMCEE;
// newJoinMPFRow.VCEE += mpfRecord.MPFRecActVCEE;
// newJoinMPFRow.MCER += mpfRecord.MPFRecActMCER;
// newJoinMPFRow.VCER += mpfRecord.MPFRecActVCER;
// }
// if (existingMPFRow != null)
// if (existingMPFRow.EmpID == empInfo.EmpID)
// {
// if (!(existingMPFRow.PeriodFrom.Equals(mpfRecord.MPFRecPeriodFr) && existingMPFRow.PeriodTo.Equals(mpfRecord.MPFRecPeriodTo)))
// {
// existingMPF.Rows.Add(existingMPFRow);
// existingMPFRow = existingMPF.NewExistingMemberRow();
// LoadExistingMemberRowInfo(empInfo, mpfRecord, existingMPFRow);
// }
// existingMPFRow.RelevantIncome += mpfRecord.MPFRecActMCRI;
// existingMPFRow.MCEE += mpfRecord.MPFRecActMCEE;
// existingMPFRow.VCEE += mpfRecord.MPFRecActVCEE;
// existingMPFRow.MCER += mpfRecord.MPFRecActMCER;
// existingMPFRow.VCER += mpfRecord.MPFRecActVCER;
// }
// }
// else
// {
// if (existingMPFRow == null)
// {
// existingMPFRow = existingMPF.NewExistingMemberRow();
// LoadExistingMemberRowInfo(empInfo, mpfRecord, existingMPFRow);
// }
// else
// {
// if (!(existingMPFRow.EmpID == empInfo.EmpID && existingMPFRow.PeriodFrom.Equals(mpfRecord.MPFRecPeriodFr) && existingMPFRow.PeriodTo.Equals(mpfRecord.MPFRecPeriodTo)))
// {
// existingMPF.Rows.Add(existingMPFRow);
// existingMPFRow = existingMPF.NewExistingMemberRow();
// LoadExistingMemberRowInfo(empInfo, mpfRecord, existingMPFRow);
// }
// }
// existingMPFRow.RelevantIncome += mpfRecord.MPFRecActMCRI;
// existingMPFRow.MCEE += mpfRecord.MPFRecActMCEE;
// existingMPFRow.VCEE += mpfRecord.MPFRecActVCEE;
// existingMPFRow.MCER += mpfRecord.MPFRecActMCER;
// existingMPFRow.VCER += mpfRecord.MPFRecActVCER;
// }
//}
//if (existingMPFRow != null)
//{
// existingMPF.Rows.Add(existingMPFRow);
//}
//if (newJoinMPFRow != null)
//{
// newJoinMPF.Rows.Add(newJoinMPFRow);
//}
//}
//return dataSet;
//}
public override ReportDocument GenerateReport()
{
if (PayPeriodFr.Ticks != 0 && PayPeriodTo.Ticks != 0 && EmpList != null)
{
MPFFile.GenericMPFFile mpfFile = new MPFFile.GenericMPFFile(dbConn);
mpfFile.LoadMPFFileDetail(EmpList, MPFPlanID, PayPeriodFr, PayPeriodTo);
System.Data.DataSet dataSet = mpfFile.CreateRemittanceStatementDataSet();
if (reportDocument == null)
{
if (mpfFile.MPFSchemeTrusteeCode.Equals("HSBC") || mpfFile.MPFSchemeTrusteeCode.Equals("HangSeng"))
{
reportDocument = new ReportTemplate.Report_Payroll_MPFRemittanceStatement_HSBC();
}
else if (mpfFile.MPFSchemeTrusteeCode.Equals("AIA"))
{
reportDocument = new ReportTemplate.Report_Payroll_MPFRemittanceStatement_AIA();
}
else if (mpfFile.MPFSchemeTrusteeCode.Equals("AXA"))
{
reportDocument = new ReportTemplate.Report_Payroll_MPFRemittanceStatement_AXA();
}
else if (mpfFile.MPFSchemeTrusteeCode.Equals("BOCI"))
{
reportDocument = new ReportTemplate.Report_Payroll_MPFRemittanceStatement_BOCI();
}
else
{
reportDocument = new ReportTemplate.Report_Payroll_MPFRemittanceStatement();
}
}
else
{
}
reportDocument.SetDataSource(dataSet);
//foreach (ReportDocument o in reportDocument.Subreports)
//{
// o.SetDataSource(dataSet);
//}
reportDocument.SetParameterValue("ContributionPeriod", PayPeriodFr.ToString("yyyy-MM-dd") + " - " + PayPeriodTo.ToString("yyyy-MM-dd"));
reportDocument.SetParameterValue("ContributionPeriodFrom", PayPeriodFr);
reportDocument.SetParameterValue("ContributionPeriodTo", PayPeriodTo);
reportDocument.SetParameterValue("TotalAdditionalEmployeeMC", mpfFile.TotalAdditionalEmployeeMC);
reportDocument.SetParameterValue("TotalAdditionalEmployeeVC", mpfFile.TotalAdditionalEmployeeVC);
reportDocument.SetParameterValue("TotalBackpayEmployeeMC", mpfFile.TotalBackPaymentEmployeeMC);
reportDocument.SetParameterValue("TotalBackpayEmployeeVC", mpfFile.TotalBackPaymentEmployeeVC);
reportDocument.SetParameterValue("TotalExistingEmployeeMC", mpfFile.TotalExistingEmployeeMC);
reportDocument.SetParameterValue("TotalExistingEmployeeVC", mpfFile.TotalExistingEmployeeVC);
reportDocument.SetParameterValue("TotalNewJoinEmployeeMC", mpfFile.TotalNewJoinEmployeeMC);
reportDocument.SetParameterValue("TotalNewJoinEmployeeVC", mpfFile.TotalNewJoinEmployeeVC);
if (mpfFile.MPFSchemeTrusteeCode.Equals("AIA"))
{
reportDocument.SetParameterValue("ChequeNo", this.ChequeNo);
}
return(reportDocument);
}
else
{
return(null);
}
}
public void GenerateExcelReport(string _filename)
{
// F&V MPF Remittance Statement is composed of MPF and PFund data. So the current MPF Remittance Statement can only achieve 1/2 of their needs.
if (PayPeriodFr.Ticks != 0 && PayPeriodTo.Ticks != 0 && EmpList != null)
{
// get MPF part of the remmittance statement
MPFFile.GenericMPFFile mpfFile = new MPFFile.GenericMPFFile(dbConn);
mpfFile.LoadMPFFileDetail(EmpList, MPFPlanID, PayPeriodFr, PayPeriodTo);
System.Data.DataSet dataSet = mpfFile.CreateRemittanceStatementDataSet();
string _schemeNo = "";
string _companyName = "";
DataTable m_table = null;
NPOI.HSSF.UserModel.HSSFSheet m_worksheet = null;
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
if (this.MPFPlanID > 0)
{
EMPFPlan _mpfPlan = EMPFPlan.GetObject(dbConn, this.MPFPlanID);
if (_mpfPlan != null)
{
EMPFScheme _mpfScheme = EMPFScheme.GetObject(dbConn, _mpfPlan.MPFSchemeID);
if (_mpfScheme != null)
{
_schemeNo = _mpfScheme.MPFSchemeCode;
}
_companyName = _mpfPlan.MPFPlanCompanyName;
}
m_table = ConvertData(dataSet.Tables["NewJoinMember"], "", 1);
m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("1-NewJoinMember");
WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 1);
m_table = ConvertData(dataSet.Tables["ExistingMember"], "IsBackPayRecord = false", 1);
m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("1-ExistingEmployee");
WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 1);
m_table = ConvertData(dataSet.Tables["ExistingMember"], "IsBackPayRecord = true", 1);
m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("1-TerminatedEmployee");
WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 1);
}
if (this.ORSOPlanID > 0)
{
EORSOPlan _orsoPlan = EORSOPlan.GetObject(dbConn, ORSOPlanID);
if (_orsoPlan != null)
{
_schemeNo = _orsoPlan.ORSOPlanSchemeNo;
_companyName = _orsoPlan.ORSOPlanCompanyName;
}
DataTable _orsoStaffTable = LoadORSODetail();
m_table = ConvertData(_orsoStaffTable, "IsBackPayRecord = false", 2);
m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("2-ExistingEmployee");
WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 2);
m_table = ConvertData(_orsoStaffTable, "IsBackPayRecord = true", 2);
m_worksheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet("2-TerminatedEmployee");
WriteToSpreadsheet(m_table, m_worksheet, _schemeNo, _companyName, 2);
}
// get ORSO part of remittance statement
System.IO.FileStream file = new System.IO.FileStream(_filename, System.IO.FileMode.Create);
workbook.Write(file);
file.Close();
}
}