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
});
}
