private BezekInfoEntity createSummaryBezekRow(IGrouping <int, BezekFileInfo> row) { SharedService validationsService = new SharedService(); var defaultValues = row.First(); var contractSummary = new BezekFileInfo(); long callTimeSum = 0, freeTimeSum = 0, freeSupplierTimeSum = 0; decimal callRateSum = 0, monthlyRateSum = 0; double discountPresent = 0; int amountOfContracts = row.Count(); for (int i = 0; i < amountOfContracts; i++) { var currentContract = row.ElementAt(i); contractSummary.BillingAmount += currentContract.BillingAmount; contractSummary.BillingAmountAfterTax += currentContract.BillingAmountAfterTax; callRateSum += currentContract.CallRate; contractSummary.CallsAmount += currentContract.CallsAmount; callTimeSum += validationsService.GetCurrectTimeSpanTicksByString(currentContract.CallTime); freeTimeSum += validationsService.GetCurrectTimeSpanTicksByString(currentContract.FreeTimeUsage); freeSupplierTimeSum += validationsService.GetCurrectTimeSpanTicksByString(currentContract.FreeTimeUsageSupplier); contractSummary.ConsumptionAmount += currentContract.ConsumptionAmount; discountPresent += currentContract.DiscountPrecent; monthlyRateSum += currentContract.MonthlyRate != null?currentContract.MonthlyRate.Value:0; contractSummary.PriceBeforeDiscount += currentContract.PriceBeforeDiscount; } return(new BezekInfoEntity { Amount = contractSummary.BillingAmount, AmountAfterTax = contractSummary.BillingAmountAfterTax, Description = "", Type = "", CallRate = callRateSum / amountOfContracts, CallsAmount = contractSummary.CallsAmount, CallTime = new TimeSpan(callTimeSum).ToString(), DepartmentNumber = defaultValues.DepartmentNumber, ClientNumber = defaultValues.ClientNumber, ConsumptionAmount = contractSummary.ConsumptionAmount, DiscountPrecent = discountPresent / amountOfContracts, EndDate = defaultValues.EndDateBilling, FreeTimeUsage = new TimeSpan(freeTimeSum).ToString(), FreeTimeUsageSupplier = new TimeSpan(freeSupplierTimeSum).ToString(), GeneralSummaryRowId = defaultValues.GeneralRowId, HebServiceType = "", IsMatched = row.Where(x => !x.IsMatched).Count() == 0, MonthlyRate = monthlyRateSum / amountOfContracts, OriginalClient = defaultValues.OriginalClient, OriginalPayer = defaultValues.OriginalPayer, PayerNumberBezek = defaultValues.PayerNumberBezek, Contract = row.Key, PriceBeforeDiscount = contractSummary.PriceBeforeDiscount, RowId = 0, SecondaryServiceType = "", ServiceType = "", StartDate = defaultValues.StartDateBilling, TimePeriodText = "", CustomerId = defaultValues.CustomerId }); }
private BezekFileInfo CreateExcelRowToBezekTablePreviousPayments(ExcelWorksheet sheet, int rowNum, GeneralBillingSummary generalSummary, string invoiceNumber, int rowId) { try { BezekFileInfo fileInfo = new BezekFileInfo(); fileInfo.BillingAmountAfterTax = string.IsNullOrEmpty(sheet.Cells[rowNum, 7].Text.Trim()) ? 0 : decimal.Parse(sheet.Cells[rowNum, 7].Text.Trim()); fileInfo.BillingAmount = string.IsNullOrEmpty(sheet.Cells[rowNum, 5].Text.Trim()) ? 0 : decimal.Parse(sheet.Cells[rowNum, 5].Text.Trim()); fileInfo.BillingDescription = sheet.Cells[rowNum, 10].Text.Trim(); fileInfo.BillingType = sheet.Cells[rowNum, 4].Text.Trim(); fileInfo.CallRate = 0; fileInfo.CallsAmount = 0; fileInfo.GeneralRowId = generalSummary.RowId; fileInfo.CallTime = ""; fileInfo.DepartmentNumber = ""; fileInfo.ClientNumber = sheet.Cells[rowNum, 1].Text.Trim(); fileInfo.ConsumptionAmount = 0; fileInfo.CustomerId = generalSummary.CustomerId; fileInfo.DiscountPrecent = 0; fileInfo.FreeTimeUsage = ""; fileInfo.FreeTimeUsageSupplier = ""; fileInfo.HebServiceType = ""; fileInfo.OriginalClient = ""; fileInfo.OriginalPayer = 0; int paymentsLeft = string.IsNullOrEmpty(sheet.Cells[rowNum, 15].Text.Trim()) ? 0 : int.Parse(sheet.Cells[rowNum, 15].Text.Trim()); decimal paymentsSoFar = string.IsNullOrEmpty(sheet.Cells[rowNum, 13].Text.Trim()) ? 0 : decimal.Parse(sheet.Cells[rowNum, 13].Text.Trim()); int totalPayments = Convert.ToInt32(paymentsSoFar / fileInfo.BillingAmountAfterTax) + paymentsLeft; fileInfo.MonthlyRate = fileInfo.BillingAmountAfterTax; fileInfo.PayerNumberBezek = Convert.ToInt32(sheet.Cells[rowNum, 2].Text.Trim()); fileInfo.PriceBeforeDiscount = 0; fileInfo.SecondaryServiceType = ""; fileInfo.ServiceType = ""; fileInfo.StartDateBilling = string.IsNullOrEmpty(sheet.Cells[rowNum, 9].Text.Trim()) ? generalSummary.BillFromDate : DateTime.ParseExact(sheet.Cells[rowNum, 9].Text.Trim(), "dd/MM/yyyy", null); fileInfo.EndDateBilling = totalPayments != 0 ? fileInfo.StartDateBilling.Value.AddMonths(totalPayments) : fileInfo.StartDateBilling.Value; fileInfo.SubscriptionNumber = Convert.ToInt32(sheet.Cells[rowNum, 3].Text.Trim().Replace("-", "")); fileInfo.TimePeriodText = ""; fileInfo.IsMatched = false; fileInfo.RowId = rowId; fileInfo.InvoiceNumber = invoiceNumber; decimal taxAmount = string.IsNullOrEmpty(sheet.Cells[rowNum, 6].Text.Trim()) ? 0 : decimal.Parse(sheet.Cells[rowNum, 6].Text.Trim()); fileInfo.TaxRate = taxAmount != 0 && fileInfo.BillingAmount != 0 ? Convert.ToInt32((((taxAmount + fileInfo.BillingAmount) / fileInfo.BillingAmount) - 1) * 100) : 0; return(fileInfo); } catch (Exception e) { return(null); } }
private BezekFileInfo CreateExcelRowToBezekTable(ExcelWorksheet sheet, int rowNum, GeneralBillingSummary generalSummary, string invoiceNumber) { try { BezekFileInfo fileInfo = new BezekFileInfo(); fileInfo.BillingAmount = string.IsNullOrEmpty(sheet.Cells[rowNum, 9].Text.Trim()) ? 0 : decimal.Parse(sheet.Cells[rowNum, 9].Text.Trim()); fileInfo.BillingDescription = sheet.Cells[rowNum, 8].Text.Trim(); fileInfo.BillingType = sheet.Cells[rowNum, 7].Text.Trim(); fileInfo.CallRate = string.IsNullOrEmpty(sheet.Cells[rowNum, 19].Text.Trim()) ? 0 : decimal.Parse(sheet.Cells[rowNum, 19].Text.Trim()); fileInfo.CallsAmount = string.IsNullOrEmpty(sheet.Cells[rowNum, 18].Text.Trim()) ? 0 : int.Parse(sheet.Cells[rowNum, 18].Text.Trim()); fileInfo.GeneralRowId = generalSummary.RowId; fileInfo.CallTime = sheet.Cells[rowNum, 17].Text.Trim(); fileInfo.DepartmentNumber = sheet.Cells[rowNum, 3].Text.Trim(); fileInfo.ClientNumber = sheet.Cells[rowNum, 1].Text.Trim(); fileInfo.ConsumptionAmount = string.IsNullOrEmpty(sheet.Cells[rowNum, 11].Text.Trim()) ? 0 : int.Parse(sheet.Cells[rowNum, 11].Text.Trim()); fileInfo.TaxRate = string.IsNullOrEmpty(sheet.Cells[rowNum, 10].Text.Trim()) ? 0 : int.Parse(sheet.Cells[rowNum, 10].Text.Trim()); fileInfo.CustomerId = generalSummary.CustomerId; fileInfo.DiscountPrecent = string.IsNullOrEmpty(sheet.Cells[rowNum, 16].Text.Trim()) ? 0.0 : double.Parse(sheet.Cells[rowNum, 16].Text.Trim()); fileInfo.EndDateBilling = string.IsNullOrEmpty(sheet.Cells[rowNum, 6].Text.Trim()) ? generalSummary.BillToDate : DateTime.ParseExact(sheet.Cells[rowNum, 6].Text.Trim(), "dd/MM/yyyy", null); fileInfo.FreeTimeUsage = sheet.Cells[rowNum, 20].Text.Trim(); fileInfo.FreeTimeUsageSupplier = sheet.Cells[rowNum, 20].Text.Trim(); fileInfo.HebServiceType = sheet.Cells[rowNum, 25].Text.Trim(); fileInfo.MonthlyRate = string.IsNullOrEmpty(sheet.Cells[rowNum, 12].Text.Trim()) ? 0 : decimal.Parse(sheet.Cells[rowNum, 12].Text.Trim()); fileInfo.OriginalClient = sheet.Cells[rowNum, 14].Text.Trim(); fileInfo.OriginalPayer = string.IsNullOrEmpty(sheet.Cells[rowNum, 15].Text.Trim()) ? 0 : int.Parse(sheet.Cells[rowNum, 15].Text.Trim()); fileInfo.PayerNumberBezek = Convert.ToInt32(sheet.Cells[rowNum, 2].Text.Trim()); fileInfo.PriceBeforeDiscount = string.IsNullOrEmpty(sheet.Cells[rowNum, 13].Text.Trim()) ? 0 : decimal.Parse(sheet.Cells[rowNum, 13].Text.Trim()); fileInfo.SecondaryServiceType = sheet.Cells[rowNum, 24].Text.Trim(); fileInfo.ServiceType = sheet.Cells[rowNum, 23].Text.Trim(); fileInfo.StartDateBilling = string.IsNullOrEmpty(sheet.Cells[rowNum, 5].Text.Trim()) ? generalSummary.BillFromDate : DateTime.ParseExact(sheet.Cells[rowNum, 5].Text.Trim(), "dd/MM/yyyy", null); fileInfo.SubscriptionNumber = Convert.ToInt32(sheet.Cells[rowNum, 4].Text.Trim().Replace("-", "")); fileInfo.TimePeriodText = sheet.Cells[rowNum, 21].Text.Trim(); fileInfo.IsMatched = false; fileInfo.RowId = rowNum; fileInfo.InvoiceNumber = invoiceNumber; fileInfo.BillingAmountAfterTax = Math.Round(fileInfo.BillingAmount * ((fileInfo.TaxRate != 0 ? Convert.ToDecimal(fileInfo.TaxRate) / 100 : 0) + 1), 2); return(fileInfo); } catch (Exception e) { return(null); } }
private async Task <BezekInfoEntity> createBezekCodeEntity(BezekFileInfo bezek) { return(new BezekInfoEntity { Amount = bezek.BillingAmount, AmountAfterTax = bezek.BillingAmountAfterTax, CallRate = bezek.CallRate, CallsAmount = bezek.CallsAmount, CallTime = bezek.CallTime, DepartmentNumber = bezek.DepartmentNumber, ClientNumber = bezek.ClientNumber, ConsumptionAmount = bezek.ConsumptionAmount, Contract = bezek.SubscriptionNumber, Description = bezek.BillingDescription, DiscountPrecent = bezek.DiscountPrecent, EndDate = bezek.EndDateBilling, FreeTimeUsage = bezek.FreeTimeUsage, FreeTimeUsageSupplier = bezek.FreeTimeUsageSupplier, GeneralSummaryRowId = bezek.GeneralRowId, HebServiceType = bezek.HebServiceType, IsMatched = bezek.IsMatched, MonthlyRate = bezek.MonthlyRate, OriginalClient = bezek.OriginalClient, OriginalPayer = bezek.OriginalPayer, PayerNumberBezek = bezek.PayerNumberBezek, PriceBeforeDiscount = bezek.PriceBeforeDiscount, SecondaryServiceType = bezek.SecondaryServiceType, ServiceType = bezek.ServiceType, StartDate = bezek.StartDateBilling, TimePeriodText = bezek.TimePeriodText, Type = bezek.BillingType, RowId = bezek.RowId, TaxRate = bezek.TaxRate, CustomerId = bezek.CustomerId }); }