public async Task <string> CreateContractorView()
{
var procOutput = "";
using (var db = new SageDb("Db"))
{
db.CommandTimeout = 0;
var response = await db.QueryToListAsync <bool>(
"SELECT CASE WHEN EXISTS(select * FROM sys.views where name = 'C21_FVP_Contractors') THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END"
).ConfigureAwait(false);
if (!response[0])
{
var viewDef =
" CREATE VIEW [FK].[C21_FVP_Contractors]" + Environment.NewLine +
" AS" + Environment.NewLine +
" SELECT" + Environment.NewLine +
" cast(Elements1.[AccountNo] as int) AS FkId, Con.Id, Con.Shortcut, Con.Name, Con.NIP As VatId, Po.[Street]," + Environment.NewLine +
" Po.[HouseNo], Po.[ApartmentNo], Po.[Place]," + Environment.NewLine +
" Po.[PostCode], Po.[Country], Con.[BankingInfoGuid], Elements1.[Active]" + Environment.NewLine +
" FROM SSCommon.[STContractors] as Con INNER JOIN SSCommon.[STElements] as Elements1 ON(Elements1.[Guid] = Con.[MainElement])" + Environment.NewLine +
" LEFT OUTER JOIN SSCommon.[STContacts] as Contacts4 ON(Contacts4.[Guid] = Con.[ContactGuid])" + Environment.NewLine +
" LEFT OUTER JOIN SSCommon.[STPostOfficeAddresses] as Po ON(Po.[Guid] = Contacts4.[MainPostOfficeAddress])";
var createResponse = await db.QueryToListAsync <string>(viewDef).ConfigureAwait(false);
procOutput = "View created";
}
else
{
procOutput = "View exist";
}
}
return(procOutput);
}
public async Task ClearC2FK()
{
using (var db = new SageDb("Db"))
{
var docsToClear = await db.C21Documents.Where(x => x.status == -2).ToListAsync();
if (docsToClear != null && docsToClear.Count > 0)
{
await db.BeginTransactionAsync();
try
{
await db.C21Documents.Where(d => d.status == -2).DeleteAsync();
await db.C21AccountingRecords.Where(r => docsToClear.Select(d => (int?)d.id).ToList().Contains(r.dokId)).DeleteAsync();
await db.C21VatRegisters.Where(v => docsToClear.Select(d => (int?)d.id).ToList().Contains(v.dokId)).DeleteAsync();
await db.CommitTransactionAsync();
}
catch
{
await db.RollbackTransactionAsync();
}
}
}
}
public async Task <List <C21Contractor> > GetC21ContractorsAsync()
{
using (var db = new SageDb("Db"))
{
return(await db.C21Contractors.ToListAsync());
}
}
public async Task AddDocumentAggregate(C21DocumentAggregate documentAggregate)
{
documentAggregate.RenumberDocumentId(await GetNextDocumentId(50), await GetNextAccountRecordtId(50), await GetNextVatRegistertId(50));
if (documentAggregate != null)
{
using (var db = new SageDb("Db"))
{
await db.BeginTransactionAsync();
try
{
await db.InsertAsync(documentAggregate.Document);
await db.BulkCopyAsync(documentAggregate.VatRegisters);
await db.BulkCopyAsync(documentAggregate.AccountingRecords);
await db.CommitTransactionAsync();
}
catch (Exception ex)
{
await db.RollbackTransactionAsync();
throw ex;
}
}
}
}
public async Task <int> GetNextVatRegistertId(int incrementValue)
{
using (var db = new SageDb("Db"))
{
var maxId = await db.C21VatRegisters.MaxAsync(d => (int?)d.id);
incrementValue += maxId != null ? (int)maxId : 0;
}
return(incrementValue);
}
public async Task <C21DocumentDefinition> GetDocumentDefinition(string documentShortcut, short yearId)
{
if (c21DocumentDefinitions == null)
{
using (var db = new SageDb("Db"))
{
c21DocumentDefinitions = await db.C21DocumentDefinitions.ToListAsync();
}
}
return(c21DocumentDefinitions.FirstOrDefault(d => d.rokId == yearId && d.dSkrot == documentShortcut));
}
public async Task <List <C21VatRegisterDef> > GetAllVatRegistersDefs()
{
if (c21VatRegisterDefs == null)
{
using (var db = new SageDb("Db"))
{
c21VatRegisterDefs = await db.C21VatRegisterDefs.OrderBy(r => r.rNazwa).ToListAsync();
}
}
return(c21VatRegisterDefs);
}
public async Task <C21VatRegisterDef> GetVatRegistersDefs(int vatRegisterId)
{
if (c21VatRegisterDefs == null)
{
using (var db = new SageDb("Db"))
{
c21VatRegisterDefs = await db.C21VatRegisterDefs.ToListAsync();
}
}
return(c21VatRegisterDefs.FirstOrDefault(v => v.id == vatRegisterId));
}
public async Task <List <FKFDokument> > GetFKDocuments(int year, int month, string documentShortcut)
{
var c21Year = await GetYearId(new DateTime(year, month, 1));
List <FKFDokument> fkDocuments;
using (var db = new SageDb("Db"))
{
fkDocuments = await db.FKFDokuments.Where(d => d.datadok.Value.Month == month && d.rokId == c21Year.rokId && d.skrot == documentShortcut).ToListAsync();
}
return(fkDocuments);
}
public async Task <C21Year> GetYearId(DateTime documentDate)
{
if (years == null)
{
using (var db = new SageDb("Db"))
{
years = await db.C21Years.ToListAsync();
}
}
var docDate = new DateTime(documentDate.Year, documentDate.Month, documentDate.Day);
return(years.FirstOrDefault(y => docDate >= y.poczatek && docDate <= y.koniec));
}
public async Task <List <C21FvpContractor> > GetC21FvpContractorsAsync(bool active = true)
{
using (var db = new SageDb("Db"))
{
if (active)
{
return(await db.C21FvpContractors.Where(a => a.Active).ToListAsync());
}
else
{
return(await db.C21FvpContractors.ToListAsync());
}
}
}
public async Task <List <string> > ProceedContractorsAsync(int debug = 1)
{
List <string> procOutput = new List <string>();
using (var db = new SageDb("Db"))
{
db.CommandTimeout = 0;
var response = await db.QueryProcAsync <string>(
"[FK].[sp_C21_importKH]",
new DataParameter("Debug", debug, DataType.Int32)
).ConfigureAwait(false);
procOutput = response.ToList();
}
return(procOutput);
}
public async void ProceedDocumentsAsync(int pack, int ticketId, int debug = 0)
{
//List<string> procOutput = new List<string>();
Console.WriteLine($"Task ticket id:{ticketId} Packiet size:{pack} Packiet Log lines: {_procOutput.Count} Start time: {DateTime.Now}");
using (var db = new SageDb("Db"))
{
db.CommandTimeout = 0;
var response = await db.QueryProcAsync <string>(
"[FK].[sp_C21_importDK]",
new DataParameter("Debug", debug, DataType.Int32)
).ConfigureAwait(false);
_procOutput.AddRange(response.ToList());
}
Console.WriteLine($"Task ticket id:{ticketId} Packiet size:{pack} Packiet Log lines: {_procOutput.Count} End time: {DateTime.Now}");
}
public async Task AddContractorsAsync(List <C21Contractor> c21Contractors)
{
using (var db = new SageDb("Db"))
{
foreach (var contractor in c21Contractors)
{
try
{
await db.InsertAsync(contractor);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}