public void GenerateVendorsFinancialResultReport() { var random = new Random(); var salesReport = this.mySqlData.Value.LoadReports(); var productsTaxes = this.sqliteData.Value.GetAllProducTaxes().ToList(); var vendorsExpenses = salesReport.Select(sr => new VendorExpense() { VendorName = sr.VendorName, Ammount = 10 * random.Next(10, 30) }); var salesJoinedWithTaxesGroupedByVendor = salesReport .Join(productsTaxes, (s => s.ProductName), (pt => pt.ProductName), (s, pt) => new { VendorName = s.VendorName, TotalIncome = s.TotalIncomes, TotalIncomeWithTax = s.TotalIncomes * (pt.Amount / 100) }) .GroupBy(s => s.VendorName) .Select(sg => new { VendorName = sg.Key, TotalIncomes = sg.Sum(s => s.TotalIncome), TotalIncomeWithTax = sg.Sum(s => s.TotalIncomeWithTax) }) .ToList(); var vendorFinancialInfoJoinedWithExpenses = salesJoinedWithTaxesGroupedByVendor .Join(vendorsExpenses, (f => f.VendorName), (e => e.VendorName), (f, e) => new { VendorName = f.VendorName, TotalIncomes = f.TotalIncomes, TotalIncomeWithTax = f.TotalIncomeWithTax, Expenses = e.Ammount }) .GroupBy(fg => fg.VendorName) .Select(fg => new { VendorName = fg.Key, TotalIncomes = fg.Sum(f => f.TotalIncomes), TotalIncomeWithTax = fg.Sum(f => f.TotalIncomeWithTax), Expenses = fg.Sum(f => f.Expenses) }) .ToList(); var reportEntries = new LinkedList <FinancialResultReportEntry>(); foreach (var row in vendorFinancialInfoJoinedWithExpenses.ToList()) { var reportRecord = new FinancialResultReportEntry(); reportRecord.VendorName = row.VendorName; reportRecord.Incomes = row.TotalIncomes; reportRecord.Expenses = (decimal)row.Expenses; reportRecord.Taxes = (decimal)row.TotalIncomeWithTax; reportRecord.FinancialBalance = (decimal)(reportRecord.Incomes - reportRecord.Taxes - reportRecord.Expenses); reportEntries.AddLast(reportRecord); } var reportData = new FinancialResultReport(); reportData.Report = reportEntries; this.xlsxHandler.Value.GenerateVendorsFinancialResultFile(reportData, "FinancialBalanceResults.xlsx"); }
public void GenerateVendorsFinancialResultFile(FinancialResultReport reportData, string fileName) { var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("Financial Balance"); //Columns ws.Cell("B2").Value = "Vendor"; ws.Cell("C2").Value = "Incomes"; ws.Cell("D2").Value = "Expenses"; ws.Cell("E2").Value = "Taxes"; ws.Cell("F2").Value = "Financial Balance"; int rowCount = 3; foreach (var row in reportData.Report) { string vendorCell = "B" + rowCount; string incomesCell = "C" + rowCount; string expensesCell = "D" + rowCount; string taxesCell = "E" + rowCount; string finacialBalanceCell = "F" + rowCount; ws.Cell(vendorCell).Value = row.VendorName; ws.Cell(incomesCell).Value = row.Incomes; ws.Cell(expensesCell).Value = row.Expenses; ws.Cell(taxesCell).Value = row.Taxes; ws.Cell(finacialBalanceCell).Value = row.FinancialBalance; rowCount++; } var tableRange = ws.Range("B2", "F" + (rowCount - 1)); var finBalanceTable = tableRange.CreateTable(); finBalanceTable.Theme = XLTableTheme.TableStyleMedium16; ws.Columns().AdjustToContents(); wb.SaveAs(XlsxSettings.Default.SaveDirectory + fileName); }
public void GenerateVendorsFinancialResultFile(FinancialResultReport reportData, string fileName) { var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("Financial Balance"); //Columns ws.Cell("B2").Value = "Vendor"; ws.Cell("C2").Value = "Incomes"; ws.Cell("D2").Value = "Expenses"; ws.Cell("E2").Value = "Taxes"; ws.Cell("F2").Value = "Financial Balance"; var rowCount = 3; foreach (var row in reportData.Report) { var vendorCell = "B" + rowCount; var incomesCell = "C" + rowCount; var expensesCell = "D" + rowCount; var taxesCell = "E" + rowCount; var finacialBalanceCell = "F" + rowCount; ws.Cell(vendorCell).Value = row.VendorName; ws.Cell(incomesCell).Value = row.Incomes; ws.Cell(expensesCell).Value = row.Expenses; ws.Cell(taxesCell).Value = row.Taxes; ws.Cell(finacialBalanceCell).Value = row.FinancialBalance; rowCount++; } var tableRange = ws.Range("B2", "F" + (rowCount - 1)); var finBalanceTable = tableRange.CreateTable(); finBalanceTable.Theme = XLTableTheme.TableStyleMedium16; ws.Columns().AdjustToContents(); wb.SaveAs(ExcelSettings.Default.ExcelReportsDestinationFolder + fileName); }
public void GenerateVendorsFinancialResultReport() { var random = new Random(); var salesReport = this.mySqlData.Value.LoadReports(); var productsTaxes = this.sqliteData.Value.GetAllProducTaxes().ToList(); var vendorsExpenses = salesReport.Select( sr => new VendorExpense { VendorName = sr.VendorName, Ammount = 10 * random.Next(10, 30) }); var salesJoinedWithTaxesGroupedByVendor = salesReport.Join( productsTaxes, s => s.ProductName, pt => pt.ProductName, (s, pt) => new { s.VendorName, TotalIncome = s.TotalIncomes, TotalIncomeWithTax = s.TotalIncomes * (pt.Amount / 100) }) .GroupBy(s => s.VendorName) .Select( sg => new { VendorName = sg.Key, TotalIncomes = sg.Sum(s => s.TotalIncome), TotalIncomeWithTax = sg.Sum(s => s.TotalIncomeWithTax) }) .ToList(); var vendorFinancialInfoJoinedWithExpenses = salesJoinedWithTaxesGroupedByVendor.Join( vendorsExpenses, f => f.VendorName, e => e.VendorName, (f, e) => new { f.VendorName, f.TotalIncomes, f.TotalIncomeWithTax, Expenses = e.Ammount }) .GroupBy(fg => fg.VendorName) .Select( fg => new { VendorName = fg.Key, TotalIncomes = fg.Sum(f => f.TotalIncomes), TotalIncomeWithTax = fg.Sum(f => f.TotalIncomeWithTax), Expenses = fg.Sum(f => f.Expenses) }) .ToList(); var reportEntries = new LinkedList<FinancialResultReportEntry>(); foreach (var row in vendorFinancialInfoJoinedWithExpenses.ToList()) { var reportRecord = new FinancialResultReportEntry { VendorName = row.VendorName, Incomes = row.TotalIncomes, Expenses = row.Expenses, Taxes = (decimal)row.TotalIncomeWithTax }; reportRecord.FinancialBalance = reportRecord.Incomes - reportRecord.Taxes - reportRecord.Expenses; reportEntries.AddLast(reportRecord); } var reportData = new FinancialResultReport { Report = reportEntries }; this.xlsxHandler.Value.GenerateVendorsFinancialResultFile(reportData, "FinancialBalanceResults.xlsx"); }