Biskilog_Accounting/Server/Services/SalesService.cs

using Biskilog_Accounting.Server.POSModels;
using Biskilog_Accounting.Shared.CustomModels;
using Biskilog_Accounting.Shared.Enums;
using Biskilog_Accounting.Shared.Interfaces;
using Biskilog_Accounting.Shared.POSModels;
using Microsoft.AspNetCore.SignalR;
using Microsoft.EntityFrameworkCore;
using Microsoft.Net.Http.Headers;
using MySqlConnector;
using System.Text.Json;

namespace Biskilog_Accounting.Server.Services
{
    public class SalesService : ISalesInterface
    {
        private readonly BiskAcdbContext m_context;
        private readonly ITokenService m_tokenService;
        private readonly HttpContext m_httpContext;

        public event EventHandler TransactionsChanged;
        public event EventHandler FetchComplete;
        public event EventHandler FetchStart;

        public SalesService(BiskAcdbContext a_context, ITokenService a_tokenService,
            IHttpContextAccessor a_httpContextAccessor)
        {
            m_context = a_context;
            m_tokenService = a_tokenService;
            m_httpContext = a_httpContextAccessor?.HttpContext;
        }

        public Task FetchRecentTransaction(int a_limit)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<SaleItem> GetRecentTransaction()
        {
            throw new NotImplementedException();
        }

        public IEnumerable<SaleItem> GetTransactions(DateTime a_start, DateTime a_end)
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;

            if (AuthEnums.Valid == m_tokenService.ValidateToken(token))
            {
                IEnumerable<string> accessiblebranches = m_tokenService.BranchIds(token);

                using (var command = m_context.Database.GetDbConnection().CreateCommand())
                {
                    command.CommandText = "CALL GetTransactionsByDate(@p0,@p1,@p2)";
                    command.Parameters.Add(new MySqlParameter("@p0", a_start.ToString("yyyy-MM-dd")));
                    command.Parameters.Add(new MySqlParameter("@p1", a_end.ToString("yyyy-MM-dd")));
                    command.Parameters.Add(new MySqlParameter("@p2", string.Join(", ", accessiblebranches.ToArray())));

                    m_context.Database.OpenConnection();

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            yield return new SaleItem
                            {
                                Transno = reader.GetString(0),
                                Total = (decimal)reader.GetDouble(1),
                                Date = reader.GetDateTime(2),
                                Cashier = reader.GetString(3),
                                BranchId = reader.GetString(4),
                                Customer = reader.GetString(5),
                                Status = reader.GetString(6),
                            };
                        }
                    }
                }
            }
        }

        public Task FetchTransaction(DateTime a_start, DateTime a_end)
        {
            throw new NotImplementedException();
        }

        public Task FetchReceipt(string a_receiptId)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<SaleItem> GetReceipt(string a_receiptId)
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;

            if (AuthEnums.Valid == m_tokenService.ValidateToken(token))
            {
                IEnumerable<string> accessiblebranches = m_tokenService.BranchIds(token);

                using (var command = m_context.Database.GetDbConnection().CreateCommand())
                {
                    command.CommandText = "CALL GetTransactionsById(@p0,@p1)";
                    command.Parameters.Add(new MySqlParameter("@p0", a_receiptId));
                    command.Parameters.Add(new MySqlParameter("@p1", string.Join(", ", accessiblebranches.ToArray())));

                    m_context.Database.OpenConnection();

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            yield return new SaleItem
                            {
                                Transno = reader.GetString(0),
                                Total = (decimal)reader.GetDouble(1),
                                Date = reader.GetDateTime(2),
                                Cashier = reader.GetString(3),
                                BranchId = reader.GetString(4),
                                Customer = reader.GetString(5),
                                Status = reader.GetString(6),
                            };
                        }
                    }
                    // Close the connection explicitly
                    m_context.Database.CloseConnection();
                }
            }
        }

        public Task<IEnumerable<Tblcart>> GetReceiptDetail(string a_receiptId)
        {
            List<Tblcart> details = new List<Tblcart>();
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;

            if (AuthEnums.Valid == m_tokenService.ValidateToken(token))
            {
                IEnumerable<string> accessiblebranches = m_tokenService.BranchIds(token);

                using (var command = m_context.Database.GetDbConnection().CreateCommand())
                {
                    command.CommandText = "CALL GetReceiptDetails(@p0,@p1)";
                    command.Parameters.Add(new MySqlParameter("@p0", a_receiptId));
                    command.Parameters.Add(new MySqlParameter("@p1", string.Join(", ", accessiblebranches.ToArray())));

                    m_context.Database.OpenConnection();

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            details.Add(new Tblcart
                            {
                                Transno = a_receiptId,
                                Id = reader.GetString(0),
                                Quantity = reader.GetInt32(1),
                                Date = reader.GetDateTime(2),
                                Price = reader.IsDBNull(3) ? 0 : reader.GetDecimal(3),
                                Cashier = reader.GetString(4),
                                Status = reader.GetString(5),
                                Total = reader.IsDBNull(6) ? 0 : reader.GetDecimal(6),
                                Unit = reader.GetString(7),
                                Costprice = reader.IsDBNull(8) ? 0 : reader.GetDecimal(8),
                                BranchId = reader.GetString(9),
                                CountId = reader.GetString(10),
                                Tendered = reader.IsDBNull(11) ? 0 : reader.GetDecimal(11),
                                Balance = reader.IsDBNull(12) ? 0 : reader.GetDecimal(12),
                                ValueAddTax = reader.IsDBNull(13) ? 0 : reader.GetDecimal(13)
                            });
                        }
                    }
                }
            }
            return Task.FromResult(details.AsEnumerable());
        }

        public async Task SyncCart(List<Tblcart> a_item)
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;
            if (AuthEnums.Valid == m_tokenService.ValidateToken(token))
            {
                string jsonString = JsonSerializer.Serialize(a_item);
                using (var command = m_context.Database.GetDbConnection().CreateCommand())
                {
                    m_context.Database.OpenConnection();

                    command.CommandText = "CALL SaleSyncUpstream(@p0)";
                    command.Parameters.Add(new MySqlParameter("@p0", jsonString));
                    command.ExecuteNonQuery();
                }
            }
        }

        public DateTime GetLastSyncDate(string a_tablename)
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;
            if (AuthEnums.Valid == m_tokenService.ValidateToken(token))
            {
                string activeBranch = m_tokenService.GetBaseBranch(token)!;
                DateTime? lastSync = m_context.Tblsyncinfos.FirstOrDefault(p => p.TableName == a_tablename && p.BranchId == activeBranch!)?.LastSyncDate;

                if (lastSync != null)
                {
                    return (DateTime)lastSync!;
                }
            }
            return new DateTime(2000,01,01);
        }

        public void SetLastSyncDate(string a_tableName, DateTime a_timestamp)
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;
            if (AuthEnums.Valid == m_tokenService.ValidateToken(token))
            {
                string activeBranch = m_tokenService.GetBaseBranch(token)!;
                using (var command = m_context.Database.GetDbConnection().CreateCommand())
                {
                    m_context.Database.OpenConnection();

                    command.CommandText = "CALL SetTableLastSync(@p0,@p1,@p2)";
                    command.Parameters.Add(new MySqlParameter("@p0", a_tableName));
                    command.Parameters.Add(new MySqlParameter("@p1", activeBranch));
                    command.Parameters.Add(new MySqlParameter("@p2", a_timestamp));
                    command.ExecuteNonQuery();
                }
            }

        }

        public async Task SyncCancelledTransaction(List<Tblcancelledtransaction> a_item)
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;
            if (AuthEnums.Valid == m_tokenService.ValidateToken(token))
            {
                string jsonString = JsonSerializer.Serialize(a_item);
                using (var command = m_context.Database.GetDbConnection().CreateCommand())
                {
                    m_context.Database.OpenConnection();

                    command.CommandText = "CALL CancelledSaleSync(@p0)";
                    command.Parameters.Add(new MySqlParameter("@p0", jsonString));
                    command.ExecuteNonQuery();
                }
            }
        }

        public async Task SyncCreditPurchase(List<Creditpurchase> a_item)
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;
            if (AuthEnums.Valid == m_tokenService.ValidateToken(token))
            {
                string jsonString = JsonSerializer.Serialize(a_item);
                using (var command = m_context.Database.GetDbConnection().CreateCommand())
                {
                    m_context.Database.OpenConnection();

                    command.CommandText = "CALL CreditPurchaseSync(@p0)";
                    command.Parameters.Add(new MySqlParameter("@p0", jsonString));
                    command.ExecuteNonQuery();
                }
            }
        }

        public async Task SyncCustomerAccount(List<Customeraccount> a_customerAccounts)
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;
            if (AuthEnums.Valid == m_tokenService.ValidateToken(token))
            {
                string jsonString = JsonSerializer.Serialize(a_customerAccounts);
                using (var command = m_context.Database.GetDbConnection().CreateCommand())
                {
                    m_context.Database.OpenConnection();

                    command.CommandText = "CALL CustomerAccountSync(@p0)";
                    command.Parameters.Add(new MySqlParameter("@p0", jsonString));
                    command.ExecuteNonQuery();
                }
            }
        }

        public async Task SyncCustomerPurchase(List<Tblcustomerpurchase> a_customerPurchase)
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;
            if (AuthEnums.Valid == m_tokenService.ValidateToken(token))
            {
                string jsonString = JsonSerializer.Serialize(a_customerPurchase);
                using (var command = m_context.Database.GetDbConnection().CreateCommand())
                {
                    m_context.Database.OpenConnection();

                    command.CommandText = "CALL CustomerPurchasesSync(@p0)";
                    command.Parameters.Add(new MySqlParameter("@p0", jsonString));
                    command.ExecuteNonQuery();
                }
            }
        }

        public async Task SyncDiscountLogs(List<Tbldiscountlog> a_discountLog)
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;
            if (AuthEnums.Valid == m_tokenService.ValidateToken(token))
            {
                string jsonString = JsonSerializer.Serialize(a_discountLog);
                using (var command = m_context.Database.GetDbConnection().CreateCommand())
                {
                    m_context.Database.OpenConnection();

                    command.CommandText = "CALL DiscountLogsSync(@p0)";
                    command.Parameters.Add(new MySqlParameter("@p0", jsonString));
                    command.ExecuteNonQuery();
                }
            }
        }

        public async Task SyncDeliveryDetails(List<Tbldeliverydetail> a_details)
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;
            if (AuthEnums.Valid == m_tokenService.ValidateToken(token))
            {
                string jsonString = JsonSerializer.Serialize(a_details);
                using (var command = m_context.Database.GetDbConnection().CreateCommand())
                {
                    m_context.Database.OpenConnection();

                    command.CommandText = "CALL DeliveryDetailsSync(@p0)";
                    command.Parameters.Add(new MySqlParameter("@p0", jsonString));
                    command.ExecuteNonQuery();
                }
            }
        }

        public async Task SyncDeliveryHead(List<Tbldeliveryhead> a_heads)
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;
            if (AuthEnums.Valid == m_tokenService.ValidateToken(token))
            {
                string jsonString = JsonSerializer.Serialize(a_heads);
                using (var command = m_context.Database.GetDbConnection().CreateCommand())
                {
                    m_context.Database.OpenConnection();

                    command.CommandText = "CALL DeliveryheadSync(@p0)";
                    command.Parameters.Add(new MySqlParameter("@p0", jsonString));
                    command.ExecuteNonQuery();
                }
            }
        }

        public async Task SyncDeliveryRecipients(List<Tbldeliveryrecipient> a_recipients)
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;
            if (AuthEnums.Valid == m_tokenService.ValidateToken(token))
            {
                string jsonString = JsonSerializer.Serialize(a_recipients);
                using (var command = m_context.Database.GetDbConnection().CreateCommand())
                {
                    m_context.Database.OpenConnection();

                    command.CommandText = "CALL DeliveryRecipientSync(@p0)";
                    command.Parameters.Add(new MySqlParameter("@p0", jsonString));
                    command.ExecuteNonQuery();
                }
            }
        }

        public async Task SyncInvoice(List<Tblinvoice> a_invoice)
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;
            if (AuthEnums.Valid == m_tokenService.ValidateToken(token))
            {
                string jsonString = JsonSerializer.Serialize(a_invoice);
                using (var command = m_context.Database.GetDbConnection().CreateCommand())
                {
                    m_context.Database.OpenConnection();

                    command.CommandText = "CALL InvoiceSync(@p0)";
                    command.Parameters.Add(new MySqlParameter("@p0", jsonString));
                    command.ExecuteNonQuery();
                }
            }
        }
    }
}