XYY.Core/2.Data/XYY.Data.Strandard/Finance/IFinance_Customer_SupplementRepository.cs

using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using XYY.Common.Standard;
using XYY.Core.Standard;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Model.Standard.Finance;
using XYY.Model.Standard.Order;

namespace XYY.Data.Standard.Finance
{
    public interface IFinance_Customer_SupplementRepository : IBaseRepository<Finance_Customer_Supplement>
    {
        Task<List<Finance_Customer_Supplement>> GetInputFinanceCustomerSupplements(List<Finance_Customer_Supplement> list, List<SMPDetailType> detailTypes);
        Task<List<Finance_Customer_Supplement>> InputFinanceCustomerSupplements(List<Finance_Customer_Supplement> list);

        Task<PageResult<ViewFinance_Customer_Supplement>> GetPageResult(QueryModel queryModel);
    }

    public class Finance_Customer_SupplementRepository : BaseRepository<Finance_Customer_Supplement>, IFinance_Customer_SupplementRepository
    {
        public Finance_Customer_SupplementRepository(IUnitOfWork unitOfWork) : base(unitOfWork)
        {

        }

        /// <summary>
        /// 快递资费信息表
        /// </summary>
        [Table("Logistics_Fee_Other")]
        public class Logistics_Fee_Other : BaseEntity
        {
            /// <summary>
            /// 快递编号
            /// </summary> 
            public int ExpressId { get; set; }

            /// <summary>
            /// 快递资费类型
            /// </summary>        
            public int FeeType { get; set; }

            /// <summary>
            /// 费用
            /// </summary> 
            public decimal Price { get; set; }

            /// <summary>
            /// 生效时间
            /// </summary> 
            public DateTime EffectiveDate { get; set; }

            /// <summary>
            /// 是否生效
            /// </summary>
            public bool IsEffective { get; set; }

            /// <summary>
            /// COD代收手继费是否为百分比
            /// </summary>
            public bool IsPercentByCodHandCost { get; set; }

            public int CustomerId { get; set; }
        }

        public async Task<List<Finance_Customer_Supplement>> GetInputFinanceCustomerSupplements(List<Finance_Customer_Supplement> list,
            List<SMPDetailType> detailTypes)
        {
            string sql = @"select Id,CustomerId,SystemNo,TrackingNumber,CustomerOrderNo,CreateOrderChannelId from Order_Order(nolock) where SystemNo in @SystemNo
                                    union select Id,CustomerId,SystemNo,TrackingNumber,CustomerOrderNo,CreateOrderChannelId from Order_Order(nolock) where TrackingNumber in @SystemNo  ";
            var systemNos = list.Select(x => x.SystemNo).ToArray();
            var orders = await _unitOfWork.QueryBySqlAsync<Order_Order>(sql, param: new { SystemNo = systemNos });
            Dictionary<string, string> errorList = new Dictionary<string, string>();
            foreach (var item in list)
            {
                if (item.FinanceCustomerSupplementType != FinanceCustomerSupplementOrDeductionType.补_Sku处理费)
                {
                    try
                    {
                        var o = orders.Where(x => x.SystemNo == item.SystemNo.Trim() || x.TrackingNumber == item.SystemNo.Trim()).FirstOrDefault();
                        if (o == null)
                            errorList.Add(item.SystemNo, "找不到相关的订单信息");
                        else
                        {
                            item.CurrenyRate = "RMB";
                            item.OrderId = o.Id;
                            item.SystemNo = o.SystemNo;
                            item.TrackingNumber = o.TrackingNumber;
                            item.CustomerOrderNo = o.CustomerOrderNo;
                            item.CustomerId = o.CustomerId;
                            item.CreateOrderChannelId = o.CreateOrderChannelId;

                            if (string.IsNullOrEmpty(item.SMPDetailType))
                            {
                                errorList.Add(item.SystemNo, "费用类型必须填写");
                            }
                            else
                            {
                                var type = detailTypes.FirstOrDefault(x => x.ItemName == item.SMPDetailType);
                                if (type == null)
                                {
                                    errorList.Add(item.SystemNo, $"未找到费用类型{item.SMPDetailType}，请核对费用类型表后再找联系技术员");
                                }
                                else
                                {
                                    item.SMPDetailTypeId = type.Id;
                                    if (item.FeeString.Contains("+燃油"))
                                    {
                                        //如果包含燃油费用的话，我们对费用进行一次计算 
                                    }
                                    else
                                    {
                                        string regex = @"^(\d+(\.\d+)?)([A-Za-z]{3})$";
                                        if (Regex.IsMatch(item.FeeString, regex))
                                        {
                                            //币种
                                            var maths = Regex.Match(item.FeeString, regex, RegexOptions.IgnoreCase);
                                            if (maths.Groups.Count > 0)
                                            {
                                                item.CurrenyRate = maths.Groups[2].Value;
                                                item.Fee = decimal.Parse(maths.Groups[1].Value);
                                            }
                                        }
                                        else
                                        {
                                            string r = @"^(\d+(\.\d+)?)$";
                                            if (Regex.IsMatch(item.FeeString, r))
                                            {
                                                item.Fee = decimal.Parse(Regex.Match(item.FeeString, r, RegexOptions.IgnoreCase).Value);
                                            }
                                            else
                                            {
                                                errorList.Add(item.SystemNo, "无法识别该费用");
                                            }
                                        }
                                    }
                                }
                            }

                        }
                    }
                    catch (Exception ex)
                    {
                        errorList.Add(item.SystemNo, ex.Message);
                    }
                }
                else
                {
                    var type = detailTypes.FirstOrDefault(x => x.ItemName == item.SMPDetailType);
                    if (type == null)
                    {
                        errorList.Add(item.SystemNo, $"未找到费用类型{item.SMPDetailType}，请核对费用类型表后再找联系技术员");
                    }
                    else
                    {
                        item.SMPDetailTypeId = type.Id;
                    }
                }
            }
            if (errorList.Count > 0)
            {
                StringBuilder stringBuilder = new StringBuilder();
                foreach (var item in errorList)
                {
                    stringBuilder.AppendLine(item.Key + "," + item.Value);
                }
                throw new Exception(stringBuilder.ToString());
            }
            else
            {
                list.ForEach(x => { x.CreateTime = DateTime.Now; x.CreateUserName = _unitOfWork.CurrentName; });

                return list;

            }
        }


        public async Task<List<Logistics_Fee_Other>> GetOthers()
        {
            var data = (await _unitOfWork.QueryBySqlAsync<Logistics_Fee_Other>($"select * from Logistics_Fee_Other(nolock) where FeeType=2 and EffectiveDate<='{DateTime.Now.ToString_yyyyMMddHHmmss()}'")).ToList();
            return data;
        }

        public async Task<List<Finance_Customer_Supplement>> InputFinanceCustomerSupplements(List<Finance_Customer_Supplement> list)
        {
            var fjf = await GetOthers();
            string sql = @"select Id,CustomerId,ServicesOrderNo,TransferNumber,SystemNo,TrackingNumber,CustomerOrderNo,CreateOrderChannelId from Order_Order(nolock) where SystemNo in @SystemNo
                                    union select Id,CustomerId,ServicesOrderNo,TransferNumber,SystemNo,TrackingNumber,CustomerOrderNo,CreateOrderChannelId from Order_Order(nolock) where TrackingNumber in @SystemNo
                                    union select Id,CustomerId,ServicesOrderNo,TransferNumber,SystemNo,TrackingNumber,CustomerOrderNo,CreateOrderChannelId from Order_Order(nolock) where TransferNumber in @SystemNo
                                    union select Id,CustomerId,ServicesOrderNo,TransferNumber,SystemNo,TrackingNumber,CustomerOrderNo,CreateOrderChannelId from Order_Order(nolock) where ServicesOrderNo in @SystemNo";
            var systemNos = list.Select(x => x.SystemNo).ToArray();
            var orders = await _unitOfWork.QueryBySqlAsync<Order_Order>(sql, param: new { SystemNo = systemNos });
            Dictionary<string, string> errorList = new Dictionary<string, string>();
            foreach (var item in list)
            {
                if (item.FinanceCustomerSupplementType != FinanceCustomerSupplementOrDeductionType.补_Sku处理费)
                {
                    try
                    {
                        var o = orders.Where(x => x.SystemNo == item.SystemNo.Trim() || x.TrackingNumber == item.SystemNo.Trim()
                        || x.ServicesOrderNo == item.SystemNo.Trim() || x.TransferNumber == item.SystemNo.Trim()).FirstOrDefault();
                        if (o == null)
                            errorList.Add(item.SystemNo, "找不到相关的订单信息");
                        else
                        {
                            item.CurrenyRate = "RMB";
                            item.OrderId = o.Id;
                            item.SystemNo = o.SystemNo;
                            item.TrackingNumber = o.TrackingNumber;
                            item.CustomerOrderNo = o.CustomerOrderNo;
                            item.CustomerId = o.CustomerId;
                            item.CreateOrderChannelId = o.CreateOrderChannelId;
                            if (item.FeeString.Contains("+燃油"))
                            {
                                //如果包含燃油费用的话，我们对费用进行一次计算
                                item.Fee = decimal.Parse(item.FeeString.Replace("+燃油", ""));
                                int year = int.Parse(item.Month.Split('-')[0]);
                                int month = int.Parse(item.Month.Split('-')[1]);
                                int days = DateTime.DaysInMonth(year, month);
                                DateTime monthLastDay = Convert.ToDateTime(year.ToString() + "-" + month.ToString() + "-" + days.ToString());
                                //取客户，渠道，最后一份生效时间的燃油
                                var c = fjf.Where(x => x.CustomerId == item.CustomerId && x.IsEffective && x.ExpressId == o.CreateOrderChannelId && x.EffectiveDate <= monthLastDay).OrderByDescending(x => x.EffectiveDate).FirstOrDefault();
                                if (c != null)
                                {
                                    item.Fee *= (1 + c.Price);
                                }
                            }
                            else
                            {
                                string regex = @"^(\d+(\.\d+)?)([A-Za-z]{3})$";
                                if (Regex.IsMatch(item.FeeString, regex))
                                {
                                    //币种
                                    var maths = Regex.Match(item.FeeString, regex, RegexOptions.IgnoreCase);
                                    if (maths.Groups.Count > 0)
                                    {
                                        item.CurrenyRate = maths.Groups[2].Value;
                                        item.Fee = decimal.Parse(maths.Groups[1].Value);
                                    }
                                }
                                else
                                {
                                    string r = @"^(\d+(\.\d+)?)$";
                                    if (Regex.IsMatch(item.FeeString, r))
                                    {
                                        item.Fee = decimal.Parse(Regex.Match(item.FeeString, r, RegexOptions.IgnoreCase).Value);
                                    }
                                    else
                                    {
                                        errorList.Add(item.SystemNo, "无法识别该费用");
                                    }
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        errorList.Add(item.SystemNo, ex.Message);
                    }
                }
                else
                {
                    if (await IsExistsAsync(x => x.SystemNo == item.SystemNo && x.FinanceCustomerSupplementType
                    == FinanceCustomerSupplementOrDeductionType.补_Sku处理费))
                    {
                        errorList.Add(item.SystemNo, $"已存在SKU处理费{item.SystemNo}");
                    }
                }
            }
            if (errorList.Count > 0)
            {
                StringBuilder stringBuilder = new StringBuilder();
                foreach (var item in errorList)
                {
                    stringBuilder.AppendLine(item.Key + "," + item.Value);
                }
                throw new Exception(stringBuilder.ToString());
            }
            else
            {
                list.ForEach(x => { x.CreateTime = DateTime.Now; x.CreateUserName = _unitOfWork.CurrentName; });

                await _unitOfWork.BulkToDBAsync(list);
                return list;

            }
        }

        public async Task<PageResult<ViewFinance_Customer_Supplement>> GetPageResult(QueryModel queryModel)
        {
            string sql = @"select a.*,b.CompanyName as CustomerName,d.Name as PublicName from Finance_Customer_Supplement(nolock) a
                                        join User_Customer(nolock)b on a.CustomerId = b.Id
                                        join Logistics_Channel(nolock) c on c.Id = a.CreateOrderChannelId
                                        join Logistics_Public(nolock) d on d.Code = c.PublicCode";
            return await _unitOfWork.GetPagingListAsync<ViewFinance_Customer_Supplement>(queryModel, sql);
        }
    }
}