OLSBackend_Common/JiaZhiQuan.Common/Repository/RepositoryExtension.Supplier.cs

using System.Net.Http;
using System;
using System.Threading.Tasks;
using Wicture.DbRESTFul.Infrastructure.Repository;
using static JiaZhiQuan.Common.YCTech.YCTechModel;
using JiaZhiQuan.Common.Config;
using System.Linq;
using JiaZhiQuan.Common.Utils;
using Wicture.DbRESTFul;
using System.Collections.Generic;
using System.Data;
using Wicture.DbRESTFul.Cache;

namespace JiaZhiQuan.Common
{
    /// <summary>
    /// 供应商
    /// </summary>
    public static partial class RepositoryExtension
    {
        /// <summary>
        /// 同步供应商商品
        /// </summary>
        /// <param name="repository"></param>
        /// <param name="conn"></param>
        /// <param name="httpClientFactory"></param>
        /// <param name="config"></param>
        /// <param name="cacheProvider"></param>
        /// <param name="isDev"></param>
        /// <param name="supplierName"></param>
        /// <returns></returns>
        /// <exception cref="LogicalException"></exception>
        public static async Task<bool> SupplierGoodsSync(this DbRESTFulRepository repository, IDbConnection conn, IHttpClientFactory httpClientFactory, ConfigFromDb config, bool isDev, string supplierName)
        {
            bool hasChange = false;
            var supplier = await repository.QuerySingleOrDefaultAsync<dynamic>("select supplierId,supplierName from n_supplier where supplierName=@supplierName and state=0", new { supplierName });
            if (supplier == null) throw new LogicalException("未找到该供应商");
            int supplierId = (int)supplier.supplierId;
            var now = DateTime.Now;
            string syncRst = "同步于初科技商品失败，调用商品接口失败";

            //调用商品接口
            var httpClient = httpClientFactory.CreateClient();
            var request = new YCTechSyncProductRequest
            {
                Timestamp = DateTime.Now.ToString("yyyyMMddHHmmssfff")
            };
            await request.ExeRequest(httpClient, config);

            if (request.Response != null && request.Response.Code != "-99" && request.Response.Data != null && request.Response.Data.Any())
            {
                var supplierGoods = request.Response.Data.Select(g => new SupplierGoods()
                {
                    productId = (int)g.Id,
                    productName = g.ProductName,
                    supplierId = supplierId,
                    supplierName = supplierName,
                    originalPrice = AmountUtils.ConvertYuanToCent((decimal)g.MianZhi),
                    purchasePrice = AmountUtils.ConvertYuanToCent((decimal)g.ShouJia),
                    pricingType = g.JiJiaLX,
                    pricing = (int)(g.JiJia * 10000)
                }).ToList();

                var goodsList = await repository.QueryAsync<SupplierGoods>(@"select 
                                                                        productId,
                                                                        productName,
                                                                        supplierId,
                                                                        originalPrice,
                                                                        purchasePrice ,
                                                                        pricingType,
                                                                        pricing
                                                                        from n_supplier_goods where supplierId=@supplierId"
                                                                        , new { supplierId });

                //日志数据
                List<SupplierGoodsChangeLog> logList = new List<SupplierGoodsChangeLog>();
                //需要删除的数据
                List<int> delIds = new List<int>();
                List<SupplierGoods> addedItems = new List<SupplierGoods>();
                List<SupplierGoods> updatedItems = new List<SupplierGoods>();

                if (goodsList != null && goodsList.Any())
                {
                    //转化成字典进行比较
                    var oldDict = goodsList.ToDictionary(item => item.productId);
                    var newDict = supplierGoods.ToDictionary(item => item.productId);

                    //新增的数据
                    addedItems = supplierGoods.Where(nItem => !oldDict.ContainsKey(nItem.productId)).ToList();

                    if (addedItems.Any())
                    {
                        addedItems.ForEach(a =>
                        {
                            logList.Add(new SupplierGoodsChangeLog
                            {
                                type = 0,
                                syncId = 0,
                                productId = a.productId,
                                productName = a.productName,
                                content = $"{supplierName}，新增“{a.productId}”，{a.productName},原价{a.originalPrice}元，" +
                                $"优惠{a.pricingType}{a.pricing}，采购价{a.purchasePrice}元",
                                createAt = now
                            });
                        });
                    }

                    //删除的数据
                    var deletedItems = goodsList.Where(oItem => !newDict.ContainsKey(oItem.productId)).ToList();
                    if (deletedItems.Any())
                    {
                        deletedItems.ForEach(a =>
                        {
                            delIds.Add(a.productId);
                            logList.Add(new SupplierGoodsChangeLog
                            {
                                type = 1,
                                syncId = 0,
                                productId = a.productId,
                                productName = a.productName,
                                content = $"{supplierName}，减少“{a.productId}”，{a.productName},原价{a.originalPrice}元，" +
                                $"优惠{a.pricingType}{a.pricing}，采购价{a.purchasePrice}元",
                                createAt = now
                            });
                        });

                    }

                    //更新的商品
                    updatedItems = supplierGoods.Where(nItem =>
                    {
                        if (oldDict.TryGetValue(nItem.productId, out var oItem))
                        {
                            return !AreEqual(oItem, nItem);
                        }
                        return false;
                    }).ToList();

                    if (updatedItems.Any())
                    {
                        updatedItems.ForEach(a =>
                        {
                            var oldGoods = goodsList.FirstOrDefault(g => g.productId == a.productId);
                            if (oldGoods != null)
                            {
                                logList.Add(new SupplierGoodsChangeLog
                                {
                                    type = 2,
                                    syncId = 0,
                                    productId = a.productId,
                                    productName = a.productName,
                                    content = $"{supplierName}，“{a.productId}，{oldGoods.productName}“从“原价{oldGoods.originalPrice}元，" +
                                    $"优惠{oldGoods.pricingType}{oldGoods.pricing}，采购价{oldGoods.purchasePrice}元“变更为" +
                                    $"“原价{a.originalPrice}元，优惠{a.pricingType}{a.pricing}，采购价{a.purchasePrice}元”",
                                    createAt = now
                                });
                            }
                        });
                    }
                }
                else addedItems = supplierGoods;

                var trans = conn.BeginTransaction();
                try
                {
                    //新增的数据
                    if (addedItems.Any())
                    {
                        await repository.QuerySingleOrDefaultAsync<int>($@"INSERT INTO 
                                                                                    n_supplier_goods(
                                                                                    productId,
                                                                                    productName,
                                                                                    supplierId,
                                                                                    originalPrice,
                                                                                    purchasePrice,
                                                                                    pricingType,
                                                                                    pricing,
                                                                                    onsaleState,
                                                                                    createAt)VALUES(@productId,
                                                                                    @productName,
                                                                                    @supplierId,
                                                                                    @originalPrice,
                                                                                    @purchasePrice,
                                                                                    @pricingType,
                                                                                    @pricing,
                                                                                    {(int)MallGoodsOnsaleState.上架},
                                                                                    @createAt)", addedItems, conn, trans);
                    }
                    //更新的数据
                    if (updatedItems.Any())
                    {
                        await repository.QuerySingleOrDefaultAsync<int>($@"update n_supplier_goods 
                                                                            set onsaleState={(int)MallGoodsOnsaleState.上架},
                                                                                productName=@productName,
                                                                                supplierId=@supplierId,
                                                                                originalPrice=@originalPrice,
                                                                                purchasePrice=@purchasePrice,
                                                                                pricingType=@pricingType,
                                                                                pricing=@pricing,
                                                                                updateAt=@updateAt
                                                                            where productId=@productId", updatedItems, conn, trans);
                    }
                    //删除的数据
                    if (delIds.Any())
                    {
                        await repository.QuerySingleOrDefaultAsync<int>($"update n_supplier_goods set onsaleState={(int)MallGoodsOnsaleState.下架} where productId in @productId", new { productId = delIds }, conn, trans);
                    }
                    //改动日志数据
                    if (logList.Any())
                    {
                        int syncId = await repository.QuerySingleOrDefaultAsync<int>(@"insert into n_supplier_goods_sync(supplierId,state,syncRst,createAt) 
                    values (@supplierId,@state,@syncRst,@createAt);select LAST_INSERT_ID();", new { supplierId, state = 1, syncRst = "同步成功", createAt = now }, conn, trans);
                        logList.ForEach(l => { l.syncId = syncId; });
                        await repository.QuerySingleOrDefaultAsync<int>($@"INSERT INTO 
                                                                                    n_supplier_goods_change_records(
                                                                                    syncId,
                                                                                    type,
                                                                                    productId,
                                                                                    productName,
                                                                                    content,
                                                                                    createAt)VALUES(
                                                                                    @syncId,
                                                                                    @type,
                                                                                    @productId,
                                                                                    @productName,
                                                                                    @content,
                                                                                    @createAt)", logList, conn, trans);
                        hasChange = true;
                    }

                    trans.Commit();

                    // 如果有变化发送邮件给运营
                    if (hasChange)
                    {
                        SendEmail(config, isDev);
                    }

                    return true;
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    syncRst = "同步于初科技商品失败" + "\r\n" + ex.Message;
                    LoggerManager.Logger.Error(ex, "同步于初科技商品失败" + "\r\n" + ex.Message);
                }
            }

            await repository.QuerySingleOrDefaultAsync<int>(@"insert into n_supplier_goods_sync(supplierId,state,syncRst,createAt) 
                    values (@supplierId,@state,@syncRst,@createAt);", new { supplierId, state = 0, syncRst, createAt = now });
            LoggerManager.Logger.Error("", syncRst);

            return false;
        }

        /// <summary>
        /// 同步供应商余额
        /// </summary>
        /// <param name="repository"></param>
        /// <param name="httpClientFactory"></param>
        /// <param name="config"></param>
        /// <param name="supplierName"></param>
        /// <returns></returns>
        public static async Task<bool> SyncYCTechSupplierBalance(this DbRESTFulRepository repository, IHttpClientFactory httpClientFactory, ConfigFromDb config, string supplierName = "于初科技", bool isSyncAt = true)
        {
            var supplier = await repository.QuerySingleOrDefaultAsync<dynamic>("select supplierId,supplierName from n_supplier where supplierName=@supplierName and state=0", new { supplierName });
            if (supplier != null)
            {
                int supplierId = (int)supplier.supplierId;
                //查询余额
                var httpClient = httpClientFactory.CreateClient();
                var request = new YCTechYuERequest
                {
                    Timestamp = DateTime.Now.ToString("yyyyMMddHHmmssfff")
                };
                await request.ExeRequest(httpClient, config);
                if (request.Response != null && request.Response.Code != "-99")
                {
                    int currentBalance = (int)(request.Response.Data.SurplusYuE * 100);
                    string atSql = "";
                    if (isSyncAt) atSql = ",syncAt=now()";
                    await repository.QuerySingleOrDefaultAsync<int>($"update n_supplier set balance=@balance {atSql} where supplierId=@supplierId", new { supplierId, balance = currentBalance });
                    return true;
                }
            }
            LoggerManager.Logger.Error("", "获取供应商余额失败");
            return false;
        }


        /// <summary>
        /// 比较商品内容是否变化
        /// </summary>
        /// <param name="item1"></param>
        /// <param name="item2"></param>
        /// <returns></returns>
        private static bool AreEqual(SupplierGoods item1, SupplierGoods item2)
        {
            return item1.productName == item2.productName &&
                   item1.originalPrice == item2.originalPrice &&
                   item1.purchasePrice == item2.purchasePrice &&
                   item1.pricingType == item2.pricingType &&
                   item1.pricing == item2.pricing;
        }

        private static void SendEmail(ConfigFromDb config, bool isDev)
        {
            if (!string.IsNullOrEmpty(config.MPPostStatWarningMailTo))
            {
                // 通过邮件提醒
                var cont = "供应商商品有变化，请登录管理后台查看";
                var mailToDic = new Dictionary<string, string>();
                config.MPPostStatWarningMailTo.Split(',', StringSplitOptions.RemoveEmptyEntries).ForEach(e => mailToDic[e] = e);
                var env = isDev ? "【测试环境】" : "【正式环境】";
                _ = CommonUtils.SendEmail(new MailInfo
                {
                    Account = config.MailAccount,
                    MailFromAddr = config.MailAccount,
                    AuthCode = config.MailAuthCode,
                    MailFromName = "JiaZhiQuan.Processor",
                    MailToDic = mailToDic,
                    Subject = env + cont,
                    Content = env + cont
                });
            }
        }

        private class SupplierGoods
        {
            public int productId { get; set; }
            public string productName { get; set; }
            public int supplierId { get; set; }
            public string supplierName { get; set; }
            public int originalPrice { get; set; }
            public int purchasePrice { get; set; }
            public string pricingType { get; set; }
            public int pricing { get; set; }
            public DateTime createAt { get; set; } = DateTime.Now;
            public DateTime updateAt { get; set; } = DateTime.Now;
        }

        private class SupplierGoodsChangeLog
        {
            public int type { get; set; }
            public int syncId { get; set; }
            public int productId { get; set; }
            public string productName { get; set; }
            public string content { get; set; }
            public DateTime createAt { get; set; } = DateTime.Now;
        }
    }
}