public static WordTable GetSummaryWordTable(DocumentDataSet ws) { string date_format = @"dd-MMM-yyyy"; string number_format = @"#,##0.00;(#,##0.00)"; int firstColW = (int)(115.7 * 56.7), secondColW = (int)(37.6 * 56.7), thirdColW = (int)(36.5 * 56.7); var wordTable = new WordTable(3); foreach (var item in DocumentGeneratorHelper.RmaCompanyInformation) { wordTable.AddInvoiceRow(item.Item1, item.Item2); } wordTable.AddInvoiceRow(string.Format("Date:{0}", DateTime.Now.ToString(date_format))); wordTable.AddInvoiceRow(" "); wordTable.AddInvoiceRow(string.Format("To:{0}", ws.AssumingCompanyName)); wordTable.AddInvoiceRow("Attn: Reinsurance Department"); wordTable.AddInvoiceRow(string.Format("Re: {0} Business as of {1}", ws.AssumingCompanyName, ws.QuarterEndDate.ToString(date_format))); wordTable.AddInvoiceRow("Management Fees Summary", true, 1); wordTable.MergeCurrentRow(1, 1); wordTable.CurrentRow[1].H_Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.CENTER; wordTable.CurrentRow[1].FontSize = 12; wordTable.CurrentRow[1].Width = (secondColW + thirdColW); wordTable.AddInvoiceRow("Current Period", true, 1); wordTable.MergeCurrentRow(1, 1); wordTable.CurrentRow[1].H_Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.CENTER; wordTable.CurrentRow[1].Width = (secondColW + thirdColW); wordTable.AddInvoiceRow(" "); wordTable.AddInvoiceRow("ALL UW YEARS COMBINED", "Business In CAD", "Business In USD", true); var claimSummaries = ws.ReportDtos ?? new List <DocumentDataDto>(); var group1 = claimSummaries.GroupBy(a => a.FeeType).OrderBy(a => a.Key); decimal total1 = 0.0m, total2 = 0.0m; foreach (var g1 in group1) { wordTable.AddInvoiceRow(" "); var feeTypeDesc = g1.Key == FeeTypeEnum.TotalManagementFees ? "Total Management Fees" : "Total Profit Commission & Management Fee Adjustments"; wordTable.AddInvoiceRow(feeTypeDesc, true); var group2 = g1.GroupBy(a => a.UnderwritingYear).OrderBy(a => a.Key); decimal subTotal1 = 0.0m, subTotal2 = 0.0m; foreach (var g2 in group2) { var val1 = string.Format("UW Year {0}", g2.Key.ToString()); var val2 = g2.Sum(a => a.BusinessInCAD).ToString(number_format); var val3 = g2.Sum(a => a.BusinessInUSD).ToString(number_format); wordTable.AddInvoiceRow(val1, val2, val3); wordTable.CurrentRow[1].H_Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.RIGHT; wordTable.CurrentRow[2].H_Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.RIGHT; subTotal1 += g2.Sum(a => a.BusinessInCAD); subTotal2 += g2.Sum(a => a.BusinessInUSD); } wordTable.AddInvoiceRow(string.Empty, "-------------------------", "-------------------------"); wordTable.AddInvoiceRow("Sub Total", subTotal1.ToString(number_format), subTotal2.ToString(number_format), true); total1 += subTotal1; total2 += subTotal2; } wordTable.AddInvoiceRow(" "); var totalV1 = "Total Amount Due to RMA this Quarter"; wordTable.AddInvoiceRow(totalV1, total1.ToString(number_format), total2.ToString(number_format), true); //bottom wordTable.AddInvoiceRow(" "); wordTable.AddInvoiceRow("Please wire these funds to our account within 10 working days."); wordTable.AddInvoiceRow(" "); wordTable.AddInvoiceRow("Best regards,"); wordTable.AddInvoiceRow(" "); wordTable.AddInvoiceRow(" "); wordTable.AddInvoiceRow("Anna Lee"); wordTable.AddInvoiceRow("Associate Director,"); wordTable.AddInvoiceRow("Life Reinsurance Administration and Financial Reporting"); // wordTable.ColumnWidth = new List <int>() { firstColW, secondColW, thirdColW }; wordTable.AdjustColumnWidth(); return(wordTable); }
public static WordTable GetUWYearWordTable(DocumentDataSet ws) { string date_format = @"dd-MMM-yyyy"; string number_format = @"#,##0.00;(#,##0.00)"; int firstColW = (int)(115.7 * 56.7), secondColW = (int)(37.6 * 56.7), thirdColW = (int)(36.5 * 56.7); var wordTable = new WordTable(3); foreach (var item in DocumentGeneratorHelper.RmaCompanyInformation) { wordTable.AddInvoiceRow(item.Item1, item.Item2); } wordTable.AddInvoiceRow("Management Fees Summary", true, 1); wordTable.MergeCurrentRow(1, 1); wordTable.CurrentRow[1].H_Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.CENTER; wordTable.CurrentRow[1].FontSize = 12; wordTable.CurrentRow[1].Width = (secondColW + thirdColW); wordTable.AddInvoiceRow("Current Period", true, 1); wordTable.MergeCurrentRow(1, 1); wordTable.CurrentRow[1].H_Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.CENTER; wordTable.CurrentRow[1].Width = (secondColW + thirdColW); wordTable.AddInvoiceRow(" "); wordTable.AddInvoiceRow(string.Format("Underwriting Year - {0}", ws.UnderwritingYear.ToString()), "Business In CAD", "Business In USD", true); var claimSummaries = ws.ReportDtos ?? new List <DocumentDataDto>(); var group1 = claimSummaries.GroupBy(a => a.FeeType).OrderBy(a => a.Key); decimal total1 = 0.0m, total2 = 0.0m; foreach (var g1 in group1) { wordTable.AddInvoiceRow(" "); var feeTypeDesc = g1.Key == FeeTypeEnum.TotalManagementFees ? "Total Management Fees" : "Total Profit Commission & Management Fee Adjustments"; wordTable.AddInvoiceRow(feeTypeDesc, true); var group2 = g1.GroupBy(a => new { a.TreatyId, a.TreatyNumber, a.TreatyName }).OrderBy(a => a.Key.TreatyName); decimal subTotal1 = 0.0m, subTotal2 = 0.0m; foreach (var g2 in group2) { var val1 = string.Format("{0} {1}", g2.Key.TreatyNumber, g2.Key.TreatyName); var val2 = g2.Sum(a => a.BusinessInCAD).ToString(number_format); var val3 = g2.Sum(a => a.BusinessInUSD).ToString(number_format); wordTable.AddInvoiceRow(val1, val2, val3); wordTable.CurrentRow[1].H_Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.RIGHT; wordTable.CurrentRow[2].H_Alignment = NPOI.XWPF.UserModel.ParagraphAlignment.RIGHT; subTotal1 += g2.Sum(a => a.BusinessInCAD); subTotal2 += g2.Sum(a => a.BusinessInUSD); } wordTable.AddInvoiceRow(string.Empty, "-------------------------", "-------------------------"); wordTable.AddInvoiceRow("Sub Total", subTotal1.ToString(number_format), subTotal2.ToString(number_format), true); total1 += subTotal1; total2 += subTotal2; } wordTable.AddInvoiceRow(" "); var totalV1 = "Net Amount Due to RMA this Quarter"; wordTable.AddInvoiceRow(totalV1, total1.ToString(number_format), total2.ToString(number_format), true); // wordTable.ColumnWidth = new List <int>() { firstColW, secondColW, thirdColW }; wordTable.AdjustColumnWidth(); return(wordTable); }
public static List <DocumentDataSet> CreateDocumentDataSets() { var dataSets = new List <DocumentDataSet>(); var dataSet1 = new DocumentDataSet() { WorkSheetType = WorkSheetTypeEnum.Summary, WorkSheetName = "SUMMARY", UnderwritingYear = 2020, AssumingCompanyName = "KOR", QuarterEndDate = DateTime.Parse("2020-03-31"), ReportDtos = new List <DocumentDataDto>() { new DocumentDataDto() { UnderwritingYear = 2020, BusinessInCAD = 29999, BusinessInUSD = 399999, FeeType = FeeTypeEnum.TotalManagementFees, TreatyId = 1, TreatyName = "TREATY-00001", TreatyNumber = "TN-A00001" }, new DocumentDataDto() { UnderwritingYear = 2020, BusinessInCAD = 500, BusinessInUSD = 100, FeeType = FeeTypeEnum.TotalProfitCommisionManagementFeeAdjustments, TreatyId = 1, TreatyName = "TREATY-00001", TreatyNumber = "TN-A00001" }, new DocumentDataDto() { UnderwritingYear = 2020, BusinessInCAD = 8888, BusinessInUSD = 9999, FeeType = FeeTypeEnum.TotalManagementFees, TreatyId = 2, TreatyName = "TREATY-00002", TreatyNumber = "TN-A00002" }, new DocumentDataDto() { UnderwritingYear = 2020, BusinessInCAD = 7777, BusinessInUSD = 6666, FeeType = FeeTypeEnum.TotalProfitCommisionManagementFeeAdjustments, TreatyId = 2, TreatyName = "TREATY-00002", TreatyNumber = "TN-A00002" }, new DocumentDataDto() { UnderwritingYear = 2021, BusinessInCAD = 678, BusinessInUSD = 789, FeeType = FeeTypeEnum.TotalManagementFees, TreatyId = 1, TreatyName = "TREATY-00001", TreatyNumber = "TN-A00001" } } }; dataSets.Add(dataSet1); return(dataSets); }