using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XYY.Core.Standard.Data.Infrastructure;
using XYY.Model.Standard.Finance;
using XYY.Common.Standard;
using Microsoft.Extensions.Caching.Distributed;
using XYY.Core.Standard.ExcelHelper.MSExcelHelper;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using NPOI.SS.Util;

namespace XYY.Service.Standard.Finance
{
    #region IService
    public interface ISMPReportsService
    {
        Task<List<EFFinanceBill>> GetEFFinanceBillGroup(DateTime start, DateTime end, int channelId, int customerId, int SalesmanUserId, int BranchCompanyId);
        Task<List<EFFinanceGroupBill>> CacheGetEFFinanceBillGroupAsCustomer(DateTime start, DateTime end);
        Task<List<EFFinanceGroupBill>> CacheGetEFFinanceBillGroupAsChannel(DateTime start, DateTime end);
        Task<byte[]> ExportEFFinanceBillGroup(DateTime start, DateTime end, int channelId, int customerId, int SalesmanUserId, int BranchCompanyId);
    }
    #endregion
    #region Service
    public class SMPReportsService : ISMPReportsService
    {
        IDistributedCache _cache;
        IUnitOfWork _unitOfWork;
        IExcelHelper _excel;
        public SMPReportsService(IDistributedCache cache
            , IUnitOfWork unitOfWork
            , IExcelHelper excel)
        {
            _cache = cache;
            _unitOfWork = unitOfWork;
            _excel = excel;
        }
        #region GetEFFinanceBillGroup
        public async Task<List<EFFinanceBill>> CacheGetEFFinanceBillGroup(DateTime start, DateTime end, int channelId, int customerId, int SalesmanUserId, int BranchCompanyId)
        {
            return await _cache.GetAsync("GetEFFinanceBillGroup"
              + start.ToString_yyyyMMdd() + "-" + end.ToString_yyyyMMdd() + "-" + channelId + "-" + customerId + "-" + SalesmanUserId + "-" + BranchCompanyId,
            async () =>
            {
                var group = await GetEFFinanceBillGroup(start, end, channelId, customerId, SalesmanUserId, BranchCompanyId);
                return group;
            });
        }
        public async Task<List<EFFinanceBill>> GetEFFinanceBillGroup(DateTime start, DateTime end, int channelId, int customerId, int SalesmanUserId, int BranchCompanyId)
        {
            string where = GetBillSerachWhere(channelId, customerId, SalesmanUserId, BranchCompanyId);
            string sql = $@"
                          select CONVERT(nvarchar(10),称重时间, 120) as 称重时间,  sum(isnull(bc.Profit,0)) as 毛利,
sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))收入,
sum(isnull(bc.ShippingFee,0)+isnull(bc.EUTariff,0)+isnull(bc.AttachFee,0))成本,
case  when sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))=0 
then 0 else sum(isnull(bc.Profit,0))/ sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))end 毛利占比    
from BaseOrderInfo fb   
left join Finance_Order_Charges(nolock) bc on fb.BranchCompanyId=bc.CustomerId and fb.系统单号=bc.SystemNo and bc.IncomeOrESxpense='支出' and bc.ChargeTarget=2 and bc.Offset=0  
left join Finance_Order_Charges(nolock) mc on fb.CustomerId=mc.CustomerId and fb.系统单号=mc.SystemNo and mc.IncomeOrESxpense='支出' and mc.ChargeTarget=1 and mc.Offset=0
where 称重时间 between '{start}' and '{end.AddDays(1)}' and PublicChannelId not in(8,9,13,14,15,16,21,32,35,38,39,40,42,43,54,55,56,63,64,65) and fb.CustomerId not in(227,294) {where}
 
group by CONVERT(nvarchar(10),称重时间, 120)
order by CONVERT(nvarchar(10),称重时间, 120)
							";
            return (await _unitOfWork.QueryBySqlAsync<EFFinanceBill>(sql, 360)).ToList();
        }
        #endregion
        #region GetEFFinanceBillGroupAsCustomer
        public async Task<List<EFFinanceGroupBill>> CacheGetEFFinanceBillGroupAsCustomer(DateTime start, DateTime end)
        {
            return await _cache.GetAsync("GetEFFinanceBillGroupAsCustomer"
              + start.ToString_yyyyMMdd() + "-" + end.ToString_yyyyMMdd() + "-",
            async () =>
            {
                return await GetEFFinanceBillGroupAsCustomer(start, end);
            });
        }
        private async Task<List<EFFinanceGroupBill>> GetEFFinanceBillGroupAsCustomer(DateTime start, DateTime end)
        {
            string sql = $@"
                               select * from (
	select [所属客户],fb.CustomerId, sum(isnull(bc.Profit,0)) as 毛利,
	count(fb.OrderId) 收货单量,
	sum(称重重量/1000.0) 收货重量,
	sum(isnull(bc.Profit,0))/count(fb.OrderId) 单件毛利,
	sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))预计收入,
	sum(isnull(bc.ShippingFee,0)+isnull(bc.EUTariff,0)+isnull(bc.AttachFee,0))成本,
	case  when sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))=0 
	then 0 else sum(isnull(bc.Profit,0))/ sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))end 毛利占比    
	from BaseOrderInfo fb   
	left join Finance_Order_Charges(nolock) bc on fb.BranchCompanyId=bc.CustomerId and fb.系统单号=bc.SystemNo and bc.IncomeOrESxpense='支出' and bc.ChargeTarget=2 and bc.Offset=0  
	left join Finance_Order_Charges(nolock) mc on fb.CustomerId=mc.CustomerId and fb.系统单号=mc.SystemNo and mc.IncomeOrESxpense='支出' and mc.ChargeTarget=1 and mc.Offset=0
	where PublicChannelId not in(8,9,13,14,15,16,21,32,35,38,39,40,42,43,54,55,56,63,64,65) and fb.CustomerId not in(227,294)
	and 称重时间 between '{start}' and '{end.AddDays(1)}'
 
	group by [所属客户],fb.CustomerId
) a
where a.预计收入>0
order by 预计收入 desc
";
            var allCustomerData = (await _unitOfWork.QueryBySqlAsync<EFFinanceGroupBill>(sql, 360)).ToList();
            var everyDay = await GetEFFinanceBillCustomerEveryDay(start, end);
            foreach (var custometData in allCustomerData)
            {
                custometData.EveryDayBill = everyDay.Where(x => x.所属客户 == custometData.所属客户).ToList();
            }
            return allCustomerData;
        }
        private async Task<List<EFFinanceBill>> GetEFFinanceBillCustomerEveryDay(
           DateTime start,
           DateTime end)
        {
            string sql = $@"select * from (
	select [所属客户],CONVERT(nvarchar(10),称重时间, 120) as 称重时间,  sum(isnull(bc.Profit,0)) as 毛利,
	sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))收入,
	sum(isnull(bc.ShippingFee,0)+isnull(bc.EUTariff,0)+isnull(bc.AttachFee,0))成本,
	case  when sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))=0 
	then 0 else sum(isnull(bc.Profit,0))/ sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))end 毛利占比    
	from BaseOrderInfo fb   
	left join Finance_Order_Charges(nolock) bc on fb.BranchCompanyId=bc.CustomerId and fb.系统单号=bc.SystemNo and bc.IncomeOrESxpense='支出' and bc.ChargeTarget=2 and bc.Offset=0  
	left join Finance_Order_Charges(nolock) mc on fb.CustomerId=mc.CustomerId and fb.系统单号=mc.SystemNo and mc.IncomeOrESxpense='支出' and mc.ChargeTarget=1 and mc.Offset=0
	where PublicChannelId not in(8,9,13,14,15,16,21,32,35,38,39,40,42,43,54,55,56,63,64,65) and fb.CustomerId not in(227,294)
	and 称重时间 between '{start}' and '{end.AddDays(1)}'
	group by CONVERT(nvarchar(10),称重时间, 120),[所属客户]
) a
where a.收入>0
order by CONVERT(nvarchar(10),称重时间, 120)
							";
            return (await _unitOfWork.QueryBySqlAsync<EFFinanceBill>(sql, 360)).ToList();
        }
        #endregion
        #region GetEFFinanceBillGroupAsChannel
        public async Task<List<EFFinanceGroupBill>> CacheGetEFFinanceBillGroupAsChannel(DateTime start, DateTime end)
        {
            return await _cache.GetAsync("GetEFFinanceBillGroupAsChannel"
                + start.ToString_yyyyMMdd() + "-" + end.ToString_yyyyMMdd() + "-",
              async () =>
              {
                  return await GetEFFinanceBillGroupAsChannel(start, end);
              });
        }
        private async Task<List<EFFinanceGroupBill>> GetEFFinanceBillGroupAsChannel(DateTime start, DateTime end)
        {
            string sql = $@"
                                select * from (
	select [渠道名称],fb.PublicChannelId, sum(isnull(bc.Profit,0)) as 毛利,
	count(fb.OrderId) 收货单量,
	sum(称重重量/1000.0) 收货重量,
	sum(isnull(bc.Profit,0))/count(fb.OrderId) 单件毛利,
	sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))预计收入,
	sum(isnull(bc.ShippingFee,0)+isnull(bc.EUTariff,0)+isnull(bc.AttachFee,0))成本,
	case  when sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))=0 
	then 0 else sum(isnull(bc.Profit,0))/ sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))end 毛利占比    
	from BaseOrderInfo fb   
	left join Finance_Order_Charges(nolock) bc on fb.BranchCompanyId=bc.CustomerId and fb.系统单号=bc.SystemNo and bc.IncomeOrESxpense='支出' and bc.ChargeTarget=2 and bc.Offset=0  
	left join Finance_Order_Charges(nolock) mc on fb.CustomerId=mc.CustomerId and fb.系统单号=mc.SystemNo and mc.IncomeOrESxpense='支出' and mc.ChargeTarget=1 and mc.Offset=0
	where PublicChannelId not in(8,9,13,14,15,16,21,32,35,38,39,40,42,43,54,55,56,63,64,65) and fb.CustomerId not in(227,294)
	and 称重时间 between '{start}' and '{end.AddDays(1)}'
 
	group by [渠道名称],fb.PublicChannelId
) a
where a.预计收入>0
order by 预计收入 desc ";
            var allChannelData = (await _unitOfWork.QueryBySqlAsync<EFFinanceGroupBill>(sql, 360)).ToList();
            var everyDay = await GetEFFinanceBillChannelEveryDay(start, end);
            foreach (var custometData in allChannelData)
            {
                custometData.EveryDayBill = everyDay.Where(x => x.渠道名称 == custometData.渠道名称).ToList();
            }
            return allChannelData;
        }
        private async Task<List<EFFinanceBill>> GetEFFinanceBillChannelEveryDay(
           DateTime start,
           DateTime end)
        {
            string sql = $@"
                            select * from (
	select [渠道名称],CONVERT(nvarchar(10),称重时间, 120) as 称重时间,  sum(isnull(bc.Profit,0)) as 毛利,
	sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))收入,
	sum(isnull(bc.ShippingFee,0)+isnull(bc.EUTariff,0)+isnull(bc.AttachFee,0))成本,
	case  when sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))=0 
	then 0 else sum(isnull(bc.Profit,0))/ sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))end 毛利占比    
	from BaseOrderInfo fb   
	left join Finance_Order_Charges(nolock) bc on fb.BranchCompanyId=bc.CustomerId and fb.系统单号=bc.SystemNo and bc.IncomeOrESxpense='支出' and bc.ChargeTarget=2 and bc.Offset=0  
	left join Finance_Order_Charges(nolock) mc on fb.CustomerId=mc.CustomerId and fb.系统单号=mc.SystemNo and mc.IncomeOrESxpense='支出' and mc.ChargeTarget=1 and mc.Offset=0
	where PublicChannelId not in(8,9,13,14,15,16,21,32,35,38,39,40,42,43,54,55,56,63,64,65) and fb.CustomerId not in(227,294)
	and 称重时间 between '{start}' and '{end.AddDays(1)}'
 
	group by CONVERT(nvarchar(10),称重时间, 120),[渠道名称]
) a
where a.收入>0
order by CONVERT(nvarchar(10),称重时间, 120)
							";
            return (await _unitOfWork.QueryBySqlAsync<EFFinanceBill>(sql, 360)).ToList();
        }
        #endregion
        #region Export
        private string getTempFilePath()
        {
            string basePath = System.IO.Path.Combine(System.AppContext.BaseDirectory, "temp");
            if (!System.IO.Directory.Exists(basePath))
                System.IO.Directory.CreateDirectory(basePath);
            return System.IO.Path.Combine(basePath, Guid.NewGuid().ToString() + ".xlsx");
        }
        public async Task<byte[]> ExportEFFinanceBillGroup(DateTime start, DateTime end, int channelId, int customerId, int SalesmanUserId, int BranchCompanyId)
        {
            var detailData = await GetEFFinanceBillGroupExportDetailData(start, end, channelId, customerId, SalesmanUserId, BranchCompanyId);
            var data = await GetEFFinanceBillGroupExportData(start, end, channelId, customerId, SalesmanUserId, BranchCompanyId);
            string filename = getTempFilePath();
            #region excel操作
            XSSFWorkbook excelBook = new XSSFWorkbook();
            #region 单元格样式
            ICellStyle headStyle = excelBook.CreateCellStyle();
            headStyle.WrapText = true;//设置换行这个要先设置
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.BorderBottom = BorderStyle.Thin;
            headStyle.BorderTop = BorderStyle.Thin;
            headStyle.BorderLeft = BorderStyle.Thin;
            headStyle.BorderRight = BorderStyle.Thin;
            NPOI.SS.UserModel.IFont headFont = excelBook.CreateFont();
            headFont.FontHeightInPoints = 14;
            headFont.IsBold = true;
            headStyle.SetFont(headFont);
            //设置颜色
            headStyle.FillForegroundColor = 0;
            headStyle.FillPattern = FillPattern.SolidForeground;
            ((XSSFColor)headStyle.FillForegroundColorColor).SetRgb(new byte[] { 0, 176, 240 });

            ICellStyle styleCommonCenter = excelBook.CreateCellStyle();
            styleCommonCenter.Alignment = HorizontalAlignment.Center;
            styleCommonCenter.WrapText = true;
            styleCommonCenter.VerticalAlignment = VerticalAlignment.Center;
            styleCommonCenter.BorderBottom = BorderStyle.Thin;
            styleCommonCenter.BorderTop = BorderStyle.Thin;
            styleCommonCenter.BorderLeft = BorderStyle.Thin;
            styleCommonCenter.BorderRight = BorderStyle.Thin;

            ICellStyle styleCombinedWithColor = excelBook.CreateCellStyle();
            styleCombinedWithColor.Alignment = HorizontalAlignment.Center;
            styleCombinedWithColor.WrapText = true;
            styleCombinedWithColor.BorderBottom = BorderStyle.Thin;
            styleCombinedWithColor.BorderTop = BorderStyle.Thin;
            styleCombinedWithColor.BorderLeft = BorderStyle.Thin;
            styleCombinedWithColor.BorderRight = BorderStyle.Thin;
            NPOI.SS.UserModel.IFont CombinedFont = excelBook.CreateFont();
            CombinedFont.IsBold = true;
            styleCombinedWithColor.SetFont(CombinedFont);
            //设置颜色
            styleCombinedWithColor.FillForegroundColor = 0;
            styleCombinedWithColor.FillPattern = FillPattern.SolidForeground;
            ((XSSFColor)styleCombinedWithColor.FillForegroundColorColor).SetRgb(new byte[] { 192,192,192 });

            ICellStyle styleCombined = excelBook.CreateCellStyle();
            styleCombined.Alignment = HorizontalAlignment.Center;
            styleCombined.WrapText = true;
            styleCombined.BorderBottom = BorderStyle.Thin;
            styleCombined.BorderTop = BorderStyle.Thin;
            styleCombined.BorderLeft = BorderStyle.Thin;
            styleCombined.BorderRight = BorderStyle.Thin;
            styleCombined.SetFont(CombinedFont);

            #endregion
            #region 详细数据
            ISheet sheet = excelBook.CreateSheet("Sheet1");
            IRow row = sheet.CreateRow(0);
            row.Height = 200 * 3;
            List<string> detailHeaderName = new List<string>() { "客户单号", "客户", "销售", "渠道", "目的国家", "称重时间", "内部单号", "转单号", "跟踪号", "实际称重", "称重计费", "附加费", "税费", "金额", "结算价", "结算价运费", "毛利", "毛利率" };
            for (int i = 0; i < detailHeaderName.Count; i++)
            {
                sheet.SetColumnWidth(i, 20 * 256);
                ICell cell = row.CreateCell(i);
                cell.CellStyle = headStyle;
                cell.SetCellValue(detailHeaderName[i]);
            }
            //数据  
            for (int i = 0; i < detailData.Count; i++)
            {
                IRow dataRow = sheet.CreateRow(i + 1);
                for (int j = 0; j < detailHeaderName.Count; j++)
                {
                    ICell cell = dataRow.CreateCell(j);
                    cell.CellStyle = styleCommonCenter;
                    if (j == 0) cell.SetCellValue(detailData[i].客户单号.ToString());
                    if (j == 1) cell.SetCellValue(detailData[i].所属客户.ToString());
                    if (j == 2) cell.SetCellValue(detailData[i].业务员.ToString());
                    if (j == 3) cell.SetCellValue(detailData[i].渠道名称.ToString());
                    if (j == 4) cell.SetCellValue(detailData[i].目的国家.ToString());
                    if (j == 5) cell.SetCellValue(detailData[i].称重时间.ToString());
                    if (j == 6) cell.SetCellValue(detailData[i].系统单号.ToString());
                    if (j == 7) cell.SetCellValue(detailData[i].转单号.ToString());
                    if (j == 8) cell.SetCellValue(detailData[i].跟踪号.ToString());
                    if (j == 9) cell.SetCellValue(detailData[i].实际称重.ToString());
                    if (j == 10) cell.SetCellValue(detailData[i].称重计费.ToString());
                    if (j == 11) cell.SetCellValue(detailData[i].附加费.ToString());
                    if (j == 12) cell.SetCellValue(detailData[i].税费.ToString());
                    if (j == 13) cell.SetCellValue(detailData[i].金额.ToString());
                    if (j == 14) cell.SetCellValue(detailData[i].结算价.ToString());
                    if (j == 15) cell.SetCellValue(detailData[i].结算价运费.ToString());
                    if (j == 16) cell.SetCellValue(detailData[i].毛利.ToString());
                    if (j == 17) cell.SetCellValue((detailData[i].毛利占比 * 100).ToString() + "%");
                }
            }
            #endregion
            #region 汇总数据
            ISheet sheet2 = excelBook.CreateSheet("Sheet2");
            IRow row2 = sheet2.CreateRow(0);
            row2.Height = 200 * 3;
            List<string> HeaderName = new List<string>() { "销售", "客户", "渠道", "求和项:客户运费", "求和项:结算价运费", "求和项:毛利","毛利率" };
            for (int i = 0; i < HeaderName.Count; i++)
            {
                sheet2.SetColumnWidth(i, 20 * 256);
                ICell cell = row2.CreateCell(i);
                cell.CellStyle = headStyle;
                cell.SetCellValue(HeaderName[i]);
            }
            string saleManName = string.Empty;
            string customerName = string.Empty;
            int specialRow = 1;//特殊合计行，占位(默认表头占一位)
            //数据
            for (int i = 0; i < data.Count; i++)
            {
                var currSaleManName = data[i].业务员;
                var currCustomerName = data[i].所属客户;
                if (string.IsNullOrEmpty(saleManName) || saleManName != currSaleManName)//业务员合计
                {
                    IRow dataRow = sheet2.CreateRow(i + specialRow);
                    saleManName = currSaleManName;
                    var salemanData = data.Where(x => x.业务员 == saleManName).ToList();
                    //用户合计行
                    for (int j = 0; j < HeaderName.Count; j++)
                    {
                        ICell cell = dataRow.CreateCell(j);
                        cell.CellStyle = styleCombinedWithColor;
                        switch (j)
                        {
                            case 0:
                                cell.SetCellValue(currSaleManName);
                                break;
                            case 3:
                                var total1 = salemanData.Sum(x => x.收入);
                                cell.SetCellValue((double)total1);
                                break;
                            case 4:
                                var total2 = salemanData.Sum(x => x.成本);
                                cell.SetCellValue((double)total2);
                                break;
                            case 5:
                                var total3 = salemanData.Sum(x => x.毛利);
                                cell.SetCellValue((double)total3);
                                break;
                            case 6:
                                cell.SetCellValue(Convert.ToDouble((salemanData.Sum(x => x.毛利) / salemanData.Sum(x => x.收入) * 100)).ToString() + "%");
                                break;
                            default:
                                cell.SetCellValue("");
                                break;
                        }
                    }
                    specialRow++;
                }
                if (string.IsNullOrEmpty(customerName) || customerName != currCustomerName)//客户合计行
                {
                    IRow dataRow = sheet2.CreateRow(i + specialRow);
                    customerName = currCustomerName;
                    var customerData = data.Where(x => x.所属客户 == customerName).ToList();
                    //渠道合计行
                    for (int j = 0; j < HeaderName.Count; j++)
                    {
                        ICell cell = dataRow.CreateCell(j);
                        cell.CellStyle = styleCombined;
                        switch (j)
                        {
                            case 1:
                                CellRangeAddress second_line_region = new CellRangeAddress(i + specialRow, i + specialRow, j, j + 1);
                                sheet2.AddMergedRegion(second_line_region);
                                cell.SetCellValue(customerName);
                                break;
                            case 2:
                                break;
                            case 3:
                                var total1 = customerData.Sum(x => x.收入);
                                cell.SetCellValue((double)total1);
                                break;
                            case 4:
                                var total2 = customerData.Sum(x => x.成本);
                                cell.SetCellValue((double)total2);
                                break;
                            case 5:
                                var total3 = customerData.Sum(x => x.毛利);
                                cell.SetCellValue((double)total3);
                                break;
                            case 6:
                                cell.SetCellValue(Convert.ToDouble((customerData.Sum(x => x.毛利) / customerData.Sum(x => x.收入) * 100)).ToString() + "%");
                                break;
                            default:
                                cell.SetCellValue("");
                                break;
                        }
                    }
                    specialRow++;
                }
                IRow defaultDataRow = sheet2.CreateRow(i + specialRow);
                for (int j = 0; j < HeaderName.Count; j++)
                {
                    ICell cell = defaultDataRow.CreateCell(j);
                    cell.CellStyle = styleCommonCenter;
                    switch (j)
                    {
                        case 2:
                            cell.SetCellValue(data[i].渠道名称);
                            break;
                        case 3:
                            cell.SetCellValue((double)data[i].收入);
                            break;
                        case 4:
                            cell.SetCellValue((double)data[i].成本);
                            break;
                        case 5:
                            defaultDataRow.CreateCell(j ).SetCellValue((double)data[i].毛利);
                            break;
                        case 6:
                            cell.SetCellValue(Convert.ToDouble((data[i].毛利占比 * 100)).ToString() + "%");
                            break;
                        default:
                            cell.SetCellValue("");
                            break;
                    }
                }
            }
            #endregion
            #region 文件保存
            MemoryStream stream = new MemoryStream();
            excelBook.Write(stream);
            var buf = stream.ToArray();
            //保存为Excel文件  
            using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
            #endregion
            #endregion excel操作end
            byte[] bytes = System.IO.File.ReadAllBytes(filename);
            System.IO.File.Delete(filename);
            return bytes;
        }
        public class OutDataInfo : MSExcelClassMapping<EFFinanceBill>
        {
            public OutDataInfo()
            {
                Map(x => x.业务员).Name("销售人员");
                Map(x => x.所属客户).Name("客户名称");
                Map(x => x.渠道名称).Name("渠道名称");
                Map(x => x.收入).Name("客户运费");
                Map(x => x.成本).Name("分公司结算运费");
                Map(x => x.毛利).Name("分公司毛利");
            }
        }
        private async Task<List<EFFinanceBill>> GetEFFinanceBillGroupExportData(DateTime start, DateTime end, int channelId, int customerId, int SalesmanUserId, int BranchCompanyId)
        {
            string where = GetBillSerachWhere(channelId, customerId, SalesmanUserId, BranchCompanyId);
            string sql = @$" select 业务员,所属客户,渠道名称,  sum(isnull(bc.Profit,0)) as 毛利,
sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))收入,
sum(isnull(bc.ShippingFee,0)+isnull(bc.EUTariff,0)+isnull(bc.AttachFee,0))成本,
case  when sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))=0 
then 0 else sum(isnull(bc.Profit,0))/ sum(isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))end 毛利占比    
from BaseOrderInfo fb   
left join Finance_Order_Charges(nolock) bc on fb.BranchCompanyId=bc.CustomerId and fb.系统单号=bc.SystemNo and bc.IncomeOrESxpense='支出' and bc.ChargeTarget=2 and bc.Offset=0  
left join Finance_Order_Charges(nolock) mc on fb.CustomerId=mc.CustomerId and fb.系统单号=mc.SystemNo and mc.IncomeOrESxpense='支出' and mc.ChargeTarget=1 and mc.Offset=0
where   PublicChannelId not in(8,9,13,14,15,16,21,32,35,38,39,40,42,43,54,55,56,63,64,65) and fb.CustomerId not in(227,294)
and 称重时间 between '{start}' and '{end.AddDays(1)}'  {where}
group by 业务员,所属客户,渠道名称
order by 业务员,所属客户,渠道名称 ";
            return (await _unitOfWork.QueryBySqlAsync<EFFinanceBill>(sql, 360)).ToList();
        }
        private async Task<List<ExportDetailDataModel>> GetEFFinanceBillGroupExportDetailData(DateTime start, DateTime end, int channelId, int customerId, int SalesmanUserId, int BranchCompanyId)
        {
            string where = GetBillSerachWhere(channelId, customerId, SalesmanUserId, BranchCompanyId);
            string sql = $@" select 客户单号,所属客户,业务员,渠道名称,目的国家,称重时间,系统单号,转单号,跟踪号,称重重量,isnull(mc.ShippingFee,0) 称重计费,isnull(mc.AttachFee,0) 附加费,isnull(mc.EUTariff,0) 税费,实际派送渠道,分公司名称,
isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0) 金额,
isnull(bc.ShippingFee,0)结算价,
isnull(bc.ShippingFee,0)+isnull(bc.EUTariff,0)+isnull(bc.AttachFee,0) 结算价运费,
isnull(bc.Profit,0) 毛利,
case  when isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0)=0 
then 0 else isnull(bc.Profit,0)/ (isnull(mc.ShippingFee,0)+isnull(mc.EUTariff,0)+isnull(mc.AttachFee,0))end 毛利占比   

from BaseOrderInfo fb   
left join Finance_Order_Charges(nolock) bc on fb.BranchCompanyId=bc.CustomerId and fb.系统单号=bc.SystemNo and bc.IncomeOrESxpense='支出' and bc.ChargeTarget=2 and bc.Offset=0  
left join Finance_Order_Charges(nolock) mc on fb.CustomerId=mc.CustomerId and fb.系统单号=mc.SystemNo and mc.IncomeOrESxpense='支出' and mc.ChargeTarget=1 and mc.Offset=0
where   PublicChannelId not in(8,9,13,14,15,16,21,32,35,38,39,40,42,43,54,55,56,63,64,65) and fb.CustomerId not in(227,294)
and 称重时间 between '{start}' and '{end.AddDays(1)}'  {where}  ";
            return (await _unitOfWork.QueryBySqlAsync<ExportDetailDataModel>(sql, 360)).ToList();
        }
        #endregion
        private string GetBillSerachWhere(int channelId, int customerId, int SalesmanUserId, int BranchCompanyId)
        {
            string where = string.Empty;
            if (channelId != 0)
                where += " and fb.PublicChannelId = " + channelId;
            if (customerId != 0)
                where += " and fb.CustomerId = " + customerId;
            if (SalesmanUserId != 0)
                where += " and cu.SalesmanUserId= " + SalesmanUserId;
            if (BranchCompanyId != 0)
                where += " and fb.BranchCompanyId =" + BranchCompanyId;

            return where;
        }
    }
    #endregion
}
