Biskilog_Accounting/Server/Services/ProductRepo.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.EntityFrameworkCore;
using Microsoft.Net.Http.Headers;
using MySqlConnector;
using System.Data;
using System.Data.Common;
using System.Text.Json;

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

        public event EventHandler ProductsChanged;
        public event EventHandler UnitsChanged;
        public event EventHandler BrandsChanged;
        public event EventHandler CategoriesChanged;

        public ProductRepo(BiskAcdbContext a_context, ITokenService a_tokenService, IHttpContextAccessor a_httpContextAccessor)
        {
            m_context = a_context;
            m_tokenService = a_tokenService;
            m_httpContext = a_httpContextAccessor?.HttpContext;
        }
        /// <summary>
        /// Gets all products from the server
        /// </summary>
        /// <returns></returns>
        public IEnumerable<ProductItem> GetProducts(string a_productKey = "")
        {
            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 GetProducts(@p0)";
                    command.Parameters.Add(new MySqlParameter("@p0", string.Join(", ", accessiblebranches.ToArray())));

                    m_context.Database.OpenConnection();

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            List<ProductUnits> pUnits = new List<ProductUnits>();

                            yield return new ProductItem
                            {
                                Product = new Tblproduct
                                {
                                    Pcode = reader.GetString(0),
                                    ProductName = reader.GetString(1),
                                    Pdesc = reader.GetString(2),
                                    BaseUnit = reader.GetString(3),
                                    Costprice = reader.GetDecimal(4),
                                    Status = reader.GetString(5),
                                    Price = reader.GetDecimal(6),
                                    BranchId = reader.GetString(7),
                                },
                                BaseUnit = reader.GetString(3),
                                Stock = new Tblinventory
                                {
                                    Quantity = reader.GetInt32(8)
                                },
                                Restocklevel = new Restocklevel
                                {
                                    WarnLevel = reader.GetInt32(9),
                                    Unit = reader.GetString(10),
                                },
                                Units = GetAltUnits(reader)
                            };
                        }
                    }
                }
            }
        }
        private List<ProductUnits> GetAltUnits(DbDataReader a_reader)
        {
            List<ProductUnits> pUnits = new List<ProductUnits>();
            for (int i = 1; i < 5; i++)
            {
                if (!a_reader.IsDBNull(a_reader.GetOrdinal($"AltUnit{i}")))
                {
                    pUnits.Add(new ProductUnits
                    {
                        UnitCode = a_reader.GetFieldValue<string>($"AltUnit{i}"),
                        QuantityUnit = a_reader.GetFieldValue<int>($"AltUnit{i}QTY"),
                        PriceUnit = a_reader.GetFieldValue<decimal>($"AltUnit{i}Price"),
                        DistinctiveCode = a_reader.GetFieldValue<string>($"AltUnit{i}distinctiveCode")
                    });
                }
                else
                {
                    return pUnits;
                }
            }
            return pUnits;
        }

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

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

                return m_context.Unitofmeasures.Where(b => accessiblebranches.Contains(b.BranchId));
            }
            return new List<Unitofmeasure>();
        }



        public IEnumerable<Tblbrand> GetBrands(string a_brandKey = "")
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;

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

                return m_context.Tblbrands.Where(b => accessiblebranches.Contains(b.BranchId));
            }
            return new List<Tblbrand>();
        }

        public IEnumerable<Tblcategory> GetCategories(string a_categoryKey = "")
        {
            string token = m_httpContext.Request.Headers[HeaderNames.Authorization]!;

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

                return m_context.Tblcategories.Where(b => accessiblebranches.Contains(b.BranchId));
            }
            return new List<Tblcategory>();
        }
        public async Task SyncProducts(List<Tblproduct> a_item)
        {
            try
            {
                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 ProductSync(@p0)";
                        command.Parameters.Add(new MySqlParameter("@p0", jsonString));
                        command.ExecuteNonQuery();
                    }
                }
            }catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

        public async Task SyncInventory(List<Tblinventory> 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 InventorySync(@p0)";
                    command.Parameters.Add(new MySqlParameter("@p0", jsonString));
                    command.ExecuteNonQuery();
                }
            }
        }

        public async Task SyncInventoryEntries(List<Tblinventoryentry> 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 InventoryEntriesSync(@p0)";
                    command.Parameters.Add(new MySqlParameter("@p0", jsonString));
                    command.ExecuteNonQuery();
                }
            }
        }

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

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

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

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

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

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

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

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

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

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

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

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

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

                    command.CommandText = "CALL CategorySync(@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();
                }
            }

        }
        #region Only Need to implement in the client Side
        public Task FetchUnits()
        {
            throw new NotImplementedException();
        }
        public Task FetchProducts()
        {
            throw new NotImplementedException();
        }

        public ProductItem GetProductById(string a_id)
        {
            throw new NotImplementedException();
        }

        public ProductItem GetProductByName(string name)
        {
            throw new NotImplementedException();
        }

        public void RefreshList()
        {
            throw new NotImplementedException();
        }

        public string GetUnitName(string a_unitCode)
        {
            throw new NotImplementedException();
        }
        public Task FetchBrands()
        {
            throw new NotImplementedException();
        }

        public Task FetchCategories()
        {
            throw new NotImplementedException();
        }

        public IEnumerable<ProductItem> GetLowstockItems()
        {
            throw new NotImplementedException();
        }

        public Task FetchLowStockProducts()
        {
            throw new NotImplementedException();
        }
    }
    #endregion
}