Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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();
                    }
                }
            }
        }
Ejemplo n.º 3
0
 public async Task <List <C21Contractor> > GetC21ContractorsAsync()
 {
     using (var db = new SageDb("Db"))
     {
         return(await db.C21Contractors.ToListAsync());
     }
 }
Ejemplo n.º 4
0
        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;
                    }
                }
            }
        }
Ejemplo n.º 5
0
        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);
        }
Ejemplo n.º 6
0
 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));
 }
Ejemplo n.º 7
0
 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);
 }
Ejemplo n.º 8
0
 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));
 }
Ejemplo n.º 9
0
        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);
        }
Ejemplo n.º 10
0
        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));
        }
Ejemplo n.º 11
0
 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());
         }
     }
 }
Ejemplo n.º 12
0
        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);
        }
Ejemplo n.º 13
0
        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}");
        }
Ejemplo n.º 14
0
 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);
             }
         }
     }
 }