public AccountancyExportManager(IAccountancyExportDataProvider accountancyExportDataProvider,
IOptions <AccountancyConfiguration> accountancyConfiguration,
ILogger <AccountancyExportManager> logger)
{
_accountancyExportDataProvider = accountancyExportDataProvider;
_accountancyConfiguration = accountancyConfiguration.Value;
_logger = logger;
_accountancyConfiguration.WinbooksExportLocation = FileUtils.EnsureStorageDirectory(_accountancyConfiguration.WinbooksExportLocation);
}
public AccountancyExportDataProvider(CrmContext context, IMapper mapper,
IOptions <AccountancyConfiguration> accountancyConfiguration,
IFileStorageService fileStorageService,
ILogger <AccountancyExportDataProvider> logger)
{
_context = context;
_mapper = mapper;
_accountancyConfig = accountancyConfiguration.Value;
_fileStorageService = fileStorageService;
_logger = logger;
_accountancyConfig.WinbooksExportLocation = _fileStorageService.EnsureDirectory(_accountancyConfig.WinbooksExportLocation);
}
private async Task GenerateExportFilesAsync(int fromNumber, int untilNumber, bool isDryRun, AccountancyConfiguration configuration)
{
var invoiceQuery = _context.Invoices
.Where(i => i.Number >= fromNumber && i.Number <= untilNumber && i.BookingDate == null)
.Include(i => i.VatRate);
var invoiceRecords = await invoiceQuery.ToListAsync();
var customerRecords = await invoiceQuery.Select(i => i.Customer)
.Where(c => c != null && c.BookingDate == null)
.Include(c => c.Country)
.Distinct().ToListAsync();
// TODO validate if there are gaps that have already been booked
// Invoices export
var invoiceLines = new List <object>();
var now = DateTime.Now;
if (isDryRun)
{
_logger.LogInformation($"Starting dry run of accountancy export. Simulating booking of {invoiceRecords.Count} invoices and {customerRecords.Count} customers..");
}
else
{
_logger.LogInformation($"Starting accountancy export. {invoiceRecords.Count} invoices and {customerRecords.Count} will be booked.");
}
foreach (var invoiceRecord in invoiceRecords)
{
EnsureValidityForExport(invoiceRecord);
invoiceLines.AddRange(GenerateInvoiceExportLines(invoiceRecord, configuration));
_logger.LogDebug($"Exported invoice {invoiceRecord.Number} for accountancy export.");
if (!isDryRun)
{
invoiceRecord.BookingDate = now;
}
}
using (var writer = new StreamWriter($"{configuration.WinbooksExportLocation}{configuration.WinbooksInvoicesFile}"))
using (var csv = new CsvWriter(writer))
{
csv.Configuration.HasHeaderRecord = false;
csv.WriteRecords(invoiceLines);
}
// Customers export
var customerLines = new List <object>();
foreach (var customerRecord in customerRecords)
{
customerLines.Add(GenerateCustomerExportLine(customerRecord, configuration));
_logger.LogDebug($"Exported customer {customerRecord.Number} for accountancy export.");
if (!isDryRun)
{
customerRecord.BookingDate = now;
}
}
using (var writer = new StreamWriter($"{configuration.WinbooksExportLocation}{configuration.WinbooksCustomersFile}"))
using (var csv = new CsvWriter(writer))
{
csv.Configuration.HasHeaderRecord = false;
csv.WriteRecords(customerLines);
}
}
public async Task <AccountancyExport> CreateAsync(AccountancyExport accountancyExport, AccountancyConfiguration configuration)
{
var accountancyExportRecord = _mapper.Map <DataProvider.Models.AccountancyExport>(accountancyExport);
_context.AccountancyExports.Add(accountancyExportRecord);
using (var transaction = _context.Database.BeginTransaction())
{
try
{
await GenerateExportFilesAsync((int)accountancyExport.FromNumber, (int)accountancyExport.UntilNumber, accountancyExport.IsDryRun, configuration);
await _context.SaveChangesAsync();
transaction.Commit();
return(_mapper.Map <AccountancyExport>(accountancyExportRecord));
}
catch (InvalidDataException e)
{
transaction.Rollback();
throw e;
}
}
}
private object GenerateCustomerExportLine(DataProvider.Models.Customer customer, AccountancyConfiguration configuration)
{
return(new {
Number = customer.Number,
Type = "1",
Name1 = FormatAsName(customer.Name),
Name2 = "",
CivName1 = "",
CivName2 = "",
Address1 = FormatAsName(customer.Address1),
Address2 = FormatAsName(customer.Address2),
VATCat = customer.IsCompany ? "1" : "3",
Country = customer.Country != null ? customer.Country.Code : "??",
VatNumber = FormatVatNumber(customer.VatNumber),
PayCode = "",
TelNumber = "",
FaxNumber = "",
BnkAccount = "",
ZipCode = FormatAsName(customer.EmbeddedPostalCode),
City = FormatAsName(customer.EmbeddedCity),
DefitPost = "",
Lang = "",
Category = "",
Central = "",
VatCode = "",
Currency = "EUR",
LastRemDev = "",
LastRemDat = "",
TotDeb1 = "0.000",
TotCre1 = "0.000",
TotDebTmp1 = "0.000",
TotCreTmp1 = "0.000",
TotDeb2 = "0.000",
TotCre2 = "0.000",
TotDebTmp2 = "0.000",
TotCreTmp2 = "0.000",
IsLocked = "F",
MemoType = "",
IsDoc = "T",
F28150 = ""
});
}
private IEnumerable <object> GenerateInvoiceExportLines(DataProvider.Models.Invoice invoice, AccountancyConfiguration configuration)
{
var invoiceDate = (DateTime)invoice.InvoiceDate;
var invoiceDateStr = invoiceDate.ToString("yyyyMMdd", CultureInfo.InvariantCulture);
var dueDate = (DateTime)invoice.DueDate;
var dueDateStr = dueDate.ToString("yyyyMMdd", CultureInfo.InvariantCulture);
var startYear = Int32.Parse(configuration.WinbooksStart);
var bookYear = GetBookYear(invoiceDate, startYear);
var startBookYear = Int32.Parse(configuration.WinbooksBookYear);
var startMonth = startBookYear >= 100 ? startBookYear / 100 : 0;
var period = GetPeriod(invoiceDate, startMonth);
var amount = (double)invoice.Amount;
var totalAmount = (double)invoice.TotalAmount;
var vatAmount = (double)invoice.Vat;
if (invoice.IsCreditNote)
{
amount *= -1;
totalAmount *= -1;
vatAmount *= -1;
}
var grossAmountSalesLine = new {
DocType = "1",
DBKCode = configuration.WinbooksDiary,
DBKType = "2",
DocNumber = ((int)invoice.Number).ToString("D5"), // format with 5 digits
DocOrder = "001",
OPCode = "",
AccountGL = "400000",
AccountRP = invoice.CustomerId.ToString(),
BookYear = bookYear,
Period = period,
Date = invoiceDateStr,
DateDoc = invoiceDateStr,
DueDate = dueDateStr,
Comment = "",
CommentText = "",
Amount = "0.000",
AmountEUR = totalAmount.ToString("0.000"),
VATBase = amount.ToString("0.000"),
VATCode = "",
CurrAmount = "0.000",
CurrCode = "",
CurEURBase = "0.000",
VATTax = vatAmount.ToString("0.000"),
VATInput = "",
CurrRate = "0.00000",
RemindLev = "0",
MatchNo = "",
OldDate = " ",
IsMatched = "T",
IsLocked = "F",
IsImported = "F",
IsPositve = "T",
IsTemp = "F",
MemoType = " ",
IsDoc = "F",
DocStatus = " ",
DICFrom = "",
CODAKey = ""
};
var account = GetAccount(invoice.VatRate);
var vatCode = GetVatCode(invoice.VatRate);
var netAmountSalesLine = new {
DocType = "3",
DBKCode = configuration.WinbooksDiary,
DBKType = "2",
DocNumber = ((int)invoice.Number).ToString("D5"), // format with 5 digits
DocOrder = "002",
OPCode = "",
AccountGL = account,
AccountRP = invoice.CustomerId.ToString(),
BookYear = bookYear,
Period = period,
Date = invoiceDateStr,
DateDoc = invoiceDateStr,
DueDate = dueDateStr,
Comment = "",
CommentText = "",
Amount = "0.000",
AmountEUR = (amount * -1).ToString("0.000"),
VATBase = "0.000",
VATCode = "",
CurrAmount = "0.000",
CurrCode = "",
CurEURBase = "0.000",
VATTax = "0.000",
VATInput = vatCode,
CurrRate = "0.00000",
RemindLev = "0",
MatchNo = "",
OldDate = " ",
IsMatched = "T",
IsLocked = "F",
IsImported = "F",
IsPositve = "T",
IsTemp = "F",
MemoType = " ",
IsDoc = "F",
DocStatus = " ",
DICFrom = "",
CODAKey = ""
};
var docType = GetDocType(invoice.VatRate);
var vatLine = new {
DocType = docType,
DBKCode = configuration.WinbooksDiary,
DBKType = "2",
DocNumber = ((int)invoice.Number).ToString("D5"), // format with 5 digits
DocOrder = "VAT",
OPCode = "FIXED",
AccountGL = docType == "4" ? "451000" : "",
AccountRP = invoice.CustomerId.ToString(),
BookYear = bookYear,
Period = period,
Date = invoiceDateStr,
DateDoc = invoiceDateStr,
DueDate = dueDateStr,
Comment = "",
CommentText = "",
Amount = "0.000",
AmountEUR = (vatAmount * -1).ToString("0.000"),
VATBase = (invoice.IsCreditNote ? amount : Math.Abs(amount)).ToString("0.000"),
VATCode = vatCode,
CurrAmount = "0.000",
CurrCode = "",
CurEURBase = "0.000",
VATTax = "0.000",
VATInput = "",
CurrRate = "0.00000",
RemindLev = "0",
MatchNo = "",
OldDate = " ",
IsMatched = "F",
IsLocked = "F",
IsImported = "F",
IsPositve = "T",
IsTemp = "F",
MemoType = " ",
IsDoc = "F",
DocStatus = " ",
DICFrom = "",
CODAKey = ""
};
return(new object[] { grossAmountSalesLine, netAmountSalesLine, vatLine });
}
