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

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

namespace XYY.Data.Standard.Finance
{
    public interface IFiannce_ServiceDeliveyBillRepository : IBaseRepository<Finance_ServiceDeliveryBill>
    {
        Task<AddDeliveryBillResult> AddDeliveryBill(Finance_ServiceDeliveryBill bill, List<Finance_ServiceDeliveryBillDetailInput> details);
        Task InputSupplementOrDeduction(int billId, List<Finance_ServiceDeliveryBillDetailInput> details, int type);
        Task UpdateConfirmed(int billId, List<Finance_ServiceDeliveryBillDetailInput> details);
        Task<List<Finance_ServiceDeliveryBillDetail>> GetDetail(int billId, int type, bool? confirmed);
        Task<List<Finance_ServiceDeliveryBillDto>> GetDataAsStatus(int status, string billName);
        Task<Dictionary<int, int>> GetGroupCounts(string billName);

        Task Pass(int billId);

        Task Rereject(int billId);

        Task<List<Finance_Logistics_LadingBillLogDto>> GetPaymentLog(int billId);

        Task DeleteBill(int billId);
        Task FreightRecalculation(int channelId, DateTime start, DateTime end);
    }
    public class Fiannce_ServiceDeliveyBillRepository : BaseRepository<Finance_ServiceDeliveryBill>, IFiannce_ServiceDeliveyBillRepository
    {
        public Fiannce_ServiceDeliveyBillRepository(IUnitOfWork unitOfWork) : base(unitOfWork)
        { 
        }


        public async Task DeleteBill(int billId)
        {
            await DeleteByIdAsync(billId);
            await _unitOfWork.Connection.ExecuteAsync("delete Finance_ServiceDeliveryBillDetail where BillId=" + billId, null, _unitOfWork.Transaction);
        }

        public async Task<List<Finance_Logistics_LadingBillLogDto>> GetPaymentLog(int billId)
        {
            var task = await _unitOfWork.QueryAsync<Finance_ServiceDeliveryBillLog>(x => x.Id == billId);
            var list = task.ToList();

            List<Finance_Logistics_LadingBillLogDto> dtos = new List<Finance_Logistics_LadingBillLogDto>();
            foreach (var item in list)
            {
                dtos.Add(new Finance_Logistics_LadingBillLogDto
                {
                    OldPayment = item.OldValue,
                    NewPayment = item.NewValue,
                    Payment = (decimal.Parse(item.NewValue) - decimal.Parse(item.OldValue)).ToString(),
                    CreateTime = item.CreateTime.Value
                });
            }
            return dtos;
        }

        public async Task UpdateConfirmed(int billId, List<Finance_ServiceDeliveryBillDetailInput> details)
        {

            await _unitOfWork.ExecuteScalarAsync<Finance_ServiceDeliveryBillDetailInput>("delete Finance_ServiceDeliveryBillDetailInput where BillId=" + billId);
            details.ForEach(x => { x.BillId = billId; x.CreateTime = DateTime.Now; });
            await _unitOfWork.BulkToDBAsync(details);

            await _unitOfWork.ExecuteStoredProcedureAsync("UpdateConfirmed", new
            {
                billId = billId
            });

            await _unitOfWork.ExecuteStoredProcedureAsync("updateBill", new { billId = billId });

        }

        public async Task<List<Finance_ServiceDeliveryBillDetail>> GetDetail(int billId, int type, bool? confirmed)
        {
            string sql = @" select * from Finance_ServiceDeliveryBillDetail where [FeeType]=" + type + " and  billId= " + billId;
            if (confirmed.HasValue)
                sql += " and confirmed = " + (confirmed.Value ? 1 : 0);
            var t = await _unitOfWork.QueryBySqlAsync<Finance_ServiceDeliveryBillDetail>(sql);
            return t.ToList();
        }


        public async Task<List<Finance_ServiceDeliveryBillDto>> GetDataAsStatus(int status, string billName)
        {
            string sql = $@"select * from Finance_ServiceDeliveryBill where Status=" + status;
            if (!string.IsNullOrEmpty(billName))
            {
                sql += " and Name like '%" + billName + "%' ";
            }
            var t = await _unitOfWork.QueryBySqlAsync<Finance_ServiceDeliveryBillDto>(sql);
            return t.ToList();
        }

        public async Task<Dictionary<int, int>> GetGroupCounts(string billName)
        {
            string where = "";
            if (!string.IsNullOrEmpty(billName))
            {
                where += "  where Name like '%" + billName + "%' ";
            }
            string sql = $@"select Status as [Key],count(0) as [Value] from Finance_ServiceDeliveryBill {where} group by Status";
            var t = await _unitOfWork.QueryBySqlAsync<dynamic>(sql);
            return t.ToDictionary(x => (int)x.Key, x => (int)x.Value);
        }



        public async Task InputSupplementOrDeduction(int billId, List<Finance_ServiceDeliveryBillDetailInput> details, int type)
        {

            await _unitOfWork.ExecuteScalarAsync<Finance_ServiceDeliveryBillDetailInput>("delete Finance_ServiceDeliveryBillDetailInput where BillId=" + billId);
            details.ForEach(x => { x.BillId = billId; x.CreateTime = DateTime.Now; });
            await _unitOfWork.BulkToDBAsync(details);

            await _unitOfWork.ExecuteStoredProcedureAsync("InputSupplementOrDeduction", new
            {
                billId = billId,
                type = type
            });

            await _unitOfWork.ExecuteStoredProcedureAsync("updateBill", new { billId = billId });
        }



        public async Task<AddDeliveryBillResult> AddDeliveryBill(Finance_ServiceDeliveryBill bill, List<Finance_ServiceDeliveryBillDetailInput> details)
        {
            System.Text.StringBuilder stringBuilder = new System.Text.StringBuilder();
            var repeatData = details.Where(x => !string.IsNullOrEmpty(x.TrackingNumber)).GroupBy(x => x.TrackingNumber).Where(x => x.Count() > 1);
            if (repeatData.Count() > 0)
                throw new Exception("导入帐单失败，帐单内有重复数据");

            //写入帐单主体完成
            int billId = (int)(await _unitOfWork.InsertAsync(bill, 1200));
            stringBuilder.AppendLine("写入帐单主体完成");
            //将数据导入到数据库内分析
            await _unitOfWork.ExecuteScalarAsync<Finance_ServiceDeliveryBillDetailInput>("delete Finance_ServiceDeliveryBillDetailInput where BillId=" + billId, 1200);
            stringBuilder.AppendLine("清理旧的导入数据完成");
            details.ForEach(x => { x.BillId = billId; x.CreateTime = DateTime.Now; });
            await _unitOfWork.BulkToDBAsync(details);
            stringBuilder.AppendLine("写入本次导入数据完成");
            //已计费的帐单不处理
            string sql = @"select * from Finance_ServiceDeliveryBillDetailInput(nolock) a where BillId = " + billId + "" +
                " and exists (select 1 from Finance_ServiceDeliveryBillDetail(nolock) b where BillId<>" + billId
                + " and a.TrackingNumber=b.TrackingNumber and FeeType=0 and DeliveryBillType = " + bill.DeliveryBillType.GetValue() + ") ";
            AddDeliveryBillResult result = new AddDeliveryBillResult();

            var t = await _unitOfWork.QueryBySqlAsync<Finance_ServiceDeliveryBillDetailInput>(sql, 1200);
            stringBuilder.AppendLine("查找已计费帐单完成");
            result.RepeatTrackingNumber = t.ToList();
            //没有下单的帐单不处理
            sql = @"select * from Finance_ServiceDeliveryBillDetailInput(nolock) a where BillId = " + billId + "" +
                " and not exists (select 1 from Order_Order(nolock) b where    a.TrackingNumber=b.TrackingNumber) ";
            var t2 = await _unitOfWork.QueryBySqlAsync<Finance_ServiceDeliveryBillDetailInput>(sql, 1200);
            stringBuilder.AppendLine("查找没有下单订单完成");
            result.NotDeliveryTrackingNumber = t2.ToList();

            result.ErrorList = new List<Finance_ServiceDeliveryBillDetailInput>();
            result.RepeatTrackingNumber.ForEach(x => x.Remark = "已导入过数据");
            result.NotDeliveryTrackingNumber.ForEach(x => x.Remark = "未称重");
            result.ErrorList.AddRange(result.RepeatTrackingNumber);
            result.ErrorList.AddRange(result.NotDeliveryTrackingNumber);
            if (result.ErrorList.Count > 0)
            {
                result.ErrorQty = result.ErrorList.Count;
            }

            if (result.ErrorQty < details.Count)
            {
                await _unitOfWork.ExecuteStoredProcedureAsync("inputBill", new { billId = billId }, 1200);
                stringBuilder.AppendLine("导入帐单完成");
                await _unitOfWork.ExecuteStoredProcedureAsync("updateBill", new { billId = billId }, 1200);
                stringBuilder.AppendLine("更新主帐单完成");
            }
            else
            {
                await _unitOfWork.DeleteByIdAsync<Finance_ServiceDeliveryBill>(billId);
            }
            return result;

        }

        public async Task Pass(int billId)
        {
            var model = await GetAsync(billId);
            if (model.Status == FiannceServiceBillStatus.结算中)
                throw new Exception("帐单已完结,无须再审核");

            if (model.Status == FiannceServiceBillStatus.最终确认)
            {
                //最终确认通过时，更新应付款金额
                model.Receivables = model.ConfirmedFreigh + model.Supplement - Math.Abs(model.Deduction);
            }
            if (model.Status == FiannceServiceBillStatus.待对帐)
            {
                model.Status = FiannceServiceBillStatus.最终确认;
            }
            else
            {
                model.Status = (FiannceServiceBillStatus)(model.Status.GetValue() + 1);
            }
            await _unitOfWork.UpdateAsync(model);
        }


        public async Task Rereject(int billId)
        {
            var model = await GetAsync(billId);

            if (model.Status == FiannceServiceBillStatus.最终确认)
                model.Status = FiannceServiceBillStatus.财务确认;

            if (model.Status == FiannceServiceBillStatus.结算中)
                throw new Exception("帐单已完结,无须再审核");
            if (model.Status == FiannceServiceBillStatus.待对帐)
                throw new Exception("帐单刚开始对帐，无法驳回");
            model.Status = (FiannceServiceBillStatus)(model.Status.GetValue() - 1);
            await _unitOfWork.UpdateAsync(model);

        }
        public async Task FreightRecalculation(int channelId, DateTime start, DateTime end)
        {
            string sql = @"update b set b.OPSFreight =0  from order_order(nolock) a join order_fee(nolock)b on a.Id=b.OrderId where a.ChannelId=@channelId and ReceiveTime between @start and @end";
            await _unitOfWork.Connection.ExecuteAsync(sql, new { channelId, start, end }, _unitOfWork.Transaction);
        }
    }

}