using Microsoft.AspNetCore.SignalR;
using System;
using System.Collections.Generic;
using System.IO.Compression;
using System.Linq;
using System.Security.Claims;
using System.Text;
using System.Threading.Tasks;
using XYY.Model.Standard.Finance;
using System.IO;
using XYY.Data.Standard.Finance;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using Microsoft.Extensions.Caching.Distributed;
using XYY.Model.Standard;

namespace XYY.Service.Standard.Finance
{
    /// <summary>
    /// 批量导入Hub
    /// </summary>
    public class BatchInputHub : Hub
    {
        IFinance_CustomerDeliveryBillRepository repository;
        IDistributedCache distributedCache;
        public BatchInputHub(IFinance_CustomerDeliveryBillRepository repository, IDistributedCache distributedCache)
        {
            this.repository = repository;
            this.distributedCache = distributedCache;
        }
        private string UserName
        {
            get
            {
                return Context.User.Claims.FirstOrDefault(x => x.Type == ClaimTypes.Name)?.Value;
            }
        }
        public override async Task OnConnectedAsync()
        {
            //加入用户组
            await Groups.AddToGroupAsync(Context.ConnectionId, UserName);
            //读取当前任务列表
            UserTasks = await GetTaskItems(UserName);
            //重新追到用户UI 
            await Clients.Group(this.UserName).SendAsync("AddTaskStatus", UserTasks);
        }

        public async Task SendAsnyc(string action, string message)
        {
            await Clients.Group(this.UserName).SendAsync(action, message);
        }

        private string GetTempPath(Guid guid)
        {
            return System.IO.Path.Combine(System.AppContext.BaseDirectory, "temp", guid.ToString());
        }

        private string GetTempFle(Guid guid)
        {
            return System.IO.Path.Combine(System.AppContext.BaseDirectory, "temp", guid.ToString() + ".zip");
        }

        public async Task<List<string>> DownAndUnZip(string url)
        {
            //文件下载解压
            byte[] data;
            try
            {
                data = await new System.Net.WebClient().DownloadDataTaskAsync(url);
            }
            catch (Exception ex)
            {
                await SendAsnyc("TaskError", "下载文件失败" + ex.Message);
                return null;
            }
            Guid guid = Guid.NewGuid();

            string tempDirectory = GetTempPath(guid);
            if (!System.IO.Directory.Exists(tempDirectory))
            {
                System.IO.Directory.CreateDirectory(tempDirectory);
            }

            string tempFile = GetTempFle(guid);
            File.WriteAllBytes(tempFile, data);
            try
            {
                System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
                ZipFile.ExtractToDirectory(tempFile, tempDirectory, Encoding.GetEncoding("GB2312"));
            }
            catch (Exception ex)
            {
                await SendAsnyc("TaskError", "解压文件失败" + ex.Message);
                return null;
            }

            System.IO.File.Delete(tempFile);

            //列出文件列表
            var fileList = System.IO.Directory.GetFiles(tempDirectory, "*.xlsx", System.IO.SearchOption.AllDirectories);
            return fileList.ToList();
        }
        private async Task SendError(Finance_Task item, string errorMessage)
        {
            item.Status = errorMessage;
            await UpdateTaskStatus(item);
        }

        public async Task StartItem(Finance_Task item)
        {
            string billName2 = item.JobName.Replace("账单", "");
            var bill = (await this.repository.QueryAsync(x => (x.Name == item.JobName || x.Name == billName2) && x.StartTime >= DateTime.Parse("2021-10-01")))
                .FirstOrDefault();
            if (bill == null)
            {
                await SendError(item, "执行错误:系统无此帐单");
                return;
            }


            item.JobProgress = 10;
            item.Step = "读取文件内容";
            item.Status = "执行中";
            await UpdateTaskStatus(item);
            Workbooks wbs;
            Application app = null;
            _Workbook wb;
            try
            {
                app = new Application();
                app.Visible = false; //不显示EXCEL
                app.DisplayAlerts = false; //不提示信息
                app.ScreenUpdating = false; //停止更新屏幕，加快速度
                wbs = app.Workbooks; //获取工作薄
                wb = wbs.Add(item.FileName);

                item.JobProgress = 20;
                item.Step = "分析汇总数据";
                //System.Threading.Thread.Sleep(1000);
                await UpdateTaskStatus(item);


                Microsoft.Office.Interop.Excel.Range range;
                //读取第1个对象
                Microsoft.Office.Interop.Excel.Worksheet sh = (Worksheet)wb.Sheets[1];
                int lastUsedRow = GetRowQty(sh);

                range = sh.get_Range("A1", Missing.Value);
                var rowCount = lastUsedRow;
                var colCount = 5;


                decimal sumFee = 0;
                //decimal cd = 0;
                try
                {

                    if (rowCount > 0)
                    {
                        range = range.get_Resize(rowCount, colCount);
                        object[,] value22 = (object[,])range.Value2;
                        //读取总计列
                        for (var i = 1; i < rowCount + 1; i++)
                        {
                            //取总计列
                            if (value22[i, 1] != null && value22[i, 1].ToString() == "合计")
                            {
                                sumFee = decimal.Parse(value22[i, 5].ToString());
                            }
                            //if (value22[i, 1] != null && value22[i, 1].ToString() == "索赔")
                            //{
                            //    cd = decimal.Parse(value22[i, 5].ToString());
                            //}
                        }
                    }
                }
                catch (Exception ex)
                {
                    await SendError(item, "读取合计值失败" + ex.Message);
                    return;
                }

                item.JobProgress = 30;
                item.Step = "分析运费数据";
                //System.Threading.Thread.Sleep(1000);
                await UpdateTaskStatus(item);



                //取补款、扣款帐单工作表号，读取相关的运费表
                List<int> deductionIndex = new List<int>();
                List<int> supplementIndex = new List<int>();
                //索赔
                List<int> claimIndex = new List<int>();
                //最后一个运费表
                int lastShippingSh = wb.Sheets.Count + 1;
                for (int i = 1; i < wb.Sheets.Count + 1; i++)
                {
                    var wsb = (Worksheet)wb.Sheets[i];
                    if (wsb.Name.Contains("扣款") && !wsb.Name.Contains("索赔"))
                    {
                        if (lastShippingSh > i)
                        {
                            lastShippingSh = i;
                        }
                        deductionIndex.Add(i);
                    }
                    else if (wsb.Name.Contains("补款"))
                    {
                        if (lastShippingSh > i)
                        {
                            lastShippingSh = i;
                        }
                        supplementIndex.Add(i);
                    }
                    else if (wsb.Name.Contains("索赔"))
                    {
                        if (lastShippingSh > i)
                        {
                            lastShippingSh = i;
                        }
                        claimIndex.Add(i);
                    }
                }

                List<Finance_CustomerDeliveryBillDetailInput> input = new List<Finance_CustomerDeliveryBillDetailInput>();
                ///从第二个表读到最后一个运费表
                for (int i = 2; i < lastShippingSh; i++)
                {
                    var wsb = (Worksheet)wb.Sheets[i];
                    //

                    range = wsb.get_Range("A1", Missing.Value);
                    colCount = GetColumnQty(wsb);
                    rowCount = GetRowQty(wsb);
                    if (rowCount == 0)
                    {
                        item.Status = wsb.Name + "读取运费失败，工作表中没有数据";
                        await UpdateTaskStatus(item);
                        continue;
                    }
                    //读取行开始
                    range = range.get_Resize(rowCount, colCount);
                    object[,] value22 = (object[,])range.Value2;
                    int trackingIndex = -1;
                    int weightIndex = -1;
                    int feeIndex = -1;
                    int surchargeIndex = -1;
                    int taxIndex = -1;
                    int numIndex = -1;
                    int startRow = -1;
                    for (int c = 1; c <= rowCount; c++)
                    {
                        for (int j = 1; j <= colCount; j++)
                        {
                            if (value22[c, j] == null)
                                continue;
                            string name = value22[c, j].ToString();
                            if (name == "序号")
                            {
                                numIndex = j;
                                startRow = c;
                            }
                            if (name == "跟踪号")
                            {
                                trackingIndex = j;
                            }
                            else if (name == "实际称重")
                            {
                                weightIndex = j;
                            }
                            else if (name == "称重计费")
                            {
                                feeIndex = j;
                            }
                            else if (name == "附加费")
                            {
                                surchargeIndex = j;
                            }
                            else if (name == "税费")
                            {
                                taxIndex = j;
                            }
                        }
                        if (numIndex > -1)
                            break;
                    }
                    string errorMessage = "";
                    if (trackingIndex == -1)
                    {
                        errorMessage += wsb.Name + "不包含列跟踪号";
                    }
                    else if (weightIndex == -1)
                    {
                        errorMessage += wsb.Name + "不包含列实际称重";
                    }
                    else if (feeIndex == -1)
                    {
                        errorMessage += wsb.Name + "不包含列称重计费";
                    }
                    if (!string.IsNullOrEmpty(errorMessage))
                    {

                        item.Status = errorMessage;
                        await UpdateTaskStatus(item);
                        return;
                    }



                    for (int j = startRow + 1; j <= rowCount; j++)
                    {
                        //如果是空行的话，我们去掉
                        if (value22[j, 1] == Missing.Value || value22[j, 1] == null)
                        {
                            break;
                        }
                        try
                        {


                            input.Add(new Finance_CustomerDeliveryBillDetailInput
                            {
                                Amount = GetDecimalValue(value22, feeIndex, j),
                                ConfirmedAmount = GetDecimalValue(value22, feeIndex, j),
                                Remark = string.Empty,
                                Weight = GetDecimalValue(value22, weightIndex, j),
                                TrackingNumber = value22[j, trackingIndex].ToString(),
                                AttachFee = surchargeIndex > 0 ? GetDecimalValue(value22, surchargeIndex, j) : 0,
                                TaxFee = taxIndex > 0 ? GetDecimalValue(value22, taxIndex, j) : 0
                            });
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("读取" + j + "行数据失败" + ex.Message);
                        }
                    }
                }

                item.JobProgress = 40;
                item.Step = "导入运费数据";
                //System.Threading.Thread.Sleep(1000);
                await UpdateTaskStatus(item);
                try
                {
                    await repository.UpdateConfirmed(bill.Id, input);
                }
                catch (Exception ex)
                {
                    await SendError(item, "导入运费失败" + ex.Message);
                    return;
                }


                item.JobProgress = 50;
                item.Step = "分析补款数据";
                //System.Threading.Thread.Sleep(1000);
                await UpdateTaskStatus(item);
                List<Finance_CustomerDeliveryBillDetailInput> supplementInputs = new List<Finance_CustomerDeliveryBillDetailInput>();
                if (supplementIndex.Count > 0)
                {
                    //补款数据
                    foreach (var index in supplementIndex)
                    {
                        var wsb = (Worksheet)wb.Sheets[index];
                        //

                        range = wsb.get_Range("A1", Missing.Value);
                        colCount = GetColumnQty(wsb);
                        rowCount = GetRowQty(wsb);
                        if (rowCount == 0)
                        {
                            item.Status = wsb.Name + "读取补款失败，工作表中没有数据";
                            await UpdateTaskStatus(item);
                            return;
                        }
                        //读取列表头
                        range = range.get_Resize(1, colCount);
                        object[,] value22 = (object[,])range.Value2;
                        int trackingIndex = -1;
                        int feeIndex = -1;
                        int remarkIndex = -1;
                        string[] nameSplit = wsb.Name.Split('-');
                        string SupplementName = "";
                        if (nameSplit.Length > 1)
                        {
                            SupplementName = nameSplit[1];
                        }
                        else
                        {
                            SupplementName = nameSplit[0];
                        }


                        Finance_CustomerDeliveryBillDetailInput inputSupplement = new Finance_CustomerDeliveryBillDetailInput();
                        for (int j = 1; j <= colCount; j++)
                        {
                            string cname = value22[1, j].ToString();
                            if (cname == "运单号" || cname == "跟踪号")
                            {
                                trackingIndex = j;
                            }
                            else if (cname == "金额")
                            {
                                feeIndex = j;
                            }
                            else if (cname == "科目" || cname == "明细")
                            {
                                remarkIndex = j;
                            }
                        }


                        range = range.get_Resize(rowCount, colCount);
                        value22 = (object[,])range.Value2;
                        //逐行分析
                        if (value22[rowCount, 1] == Missing.Value || value22[rowCount, 1] == null)
                        {
                            //最后一行如果是合计列的话，我们去掉
                            rowCount -= 1;
                        }

                        string errorMessage = "";

                        if (trackingIndex == -1)
                        {
                            errorMessage += wsb.Name + "不包含列跟踪号|运单号";
                        }
                        else if (feeIndex == -1)
                        {
                            errorMessage += wsb.Name + "不包含列金额";
                        }

                        if (!string.IsNullOrEmpty(errorMessage))
                        {

                            item.Status = errorMessage;
                            await UpdateTaskStatus(item);
                            return;
                        }

                        for (int j = 2; j <= rowCount; j++)
                        {
                            //备注先取明细，明细没有，取工作表名
                            string remark = SupplementName;
                            if (remarkIndex > -1)
                            {
                                remark = value22[j, remarkIndex].ToString();
                            }
                            supplementInputs.Add(new Finance_CustomerDeliveryBillDetailInput
                            {
                                Amount = GetDecimalValue(value22, feeIndex, j),
                                ConfirmedAmount = GetDecimalValue(value22, feeIndex, j),
                                Remark = remark,
                                TrackingNumber = value22[j, trackingIndex].ToString(),
                            });
                        }
                    }
                }


                item.JobProgress = 60;
                item.Step = "导入补款数据";
                //System.Threading.Thread.Sleep(1000);
                await UpdateTaskStatus(item);

                try
                {
                    //if (supplementInputs.Count > 0)
                    //{
                    foreach (var supplement in supplementInputs)
                    {
                        supplement.TrackingNumber = supplement.TrackingNumber.Replace("\t", "");
                    }
                    await repository.InputSupplementOrDeduction(bill.Id, supplementInputs, 2);
                    //}
                }
                catch (Exception ex)
                {
                    await SendError(item, "导入补款失败" + ex.Message);
                    return;
                }


                item.JobProgress = 70;
                item.Step = "分析扣款数据";
                //System.Threading.Thread.Sleep(1000);
                await UpdateTaskStatus(item);


                List<Finance_CustomerDeliveryBillDetailInput> deductionInputs = new List<Finance_CustomerDeliveryBillDetailInput>();
                if (deductionIndex.Count > 0)
                {
                    //扣款数据
                    foreach (var index in deductionIndex)
                    {
                        var wsb = (Worksheet)wb.Sheets[index];
                        //

                        range = wsb.get_Range("A1", Missing.Value);
                        colCount = GetColumnQty(wsb);
                        rowCount = GetRowQty(wsb);
                        if (rowCount == 0)
                        {
                            item.Status = wsb.Name + "读取运费失败，工作表中没有数据";
                            await UpdateTaskStatus(item);
                            return;
                        }
                        //读取列表头
                        range = range.get_Resize(1, colCount);
                        object[,] value22 = (object[,])range.Value2;
                        int trackingIndex = -1;
                        int feeIndex = -1;
                        int remarkIndex = -1;
                        string[] nameSplit = wsb.Name.Split('-');
                        string SupplementName = "";
                        if (nameSplit.Length > 1)
                        {
                            SupplementName = nameSplit[1];
                        }
                        else
                        {
                            SupplementName = nameSplit[0];
                        }


                        Finance_CustomerDeliveryBillDetailInput deduction = new Finance_CustomerDeliveryBillDetailInput();
                        for (int j = 1; j <= colCount; j++)
                        {
                            if (value22[1, j] != null)
                            { 
                                string cname = value22[1, j].ToString();
                                if (cname == "运单号" || cname == "跟踪号")
                                {
                                    trackingIndex = j;
                                }
                                if (cname == "金额")
                                {
                                    feeIndex = j;
                                }
                                if (cname == "科目" || cname == "明细")
                                {
                                    remarkIndex = j;
                                }
                            }
                        }


                        range = range.get_Resize(rowCount, colCount);
                        value22 = (object[,])range.Value2;
                        //逐行分析
                        if (value22[rowCount, 1] == Missing.Value || value22[rowCount, 1] == null)
                        {
                            //最后一行如果是合计列的话，我们去掉
                            rowCount -= 1;
                        }

                        string errorMessage = "";

                        if (trackingIndex == -1)
                        {
                            errorMessage += wsb.Name + "不包含列跟踪号|运单号";
                        }
                        else if (feeIndex == -1)
                        {
                            errorMessage += wsb.Name + "不包含列金额";
                        }

                        if (!string.IsNullOrEmpty(errorMessage))
                        {
                            item.Status = errorMessage;
                            await UpdateTaskStatus(item);
                            return;
                        }

                        for (int j = 2; j <= rowCount; j++)
                        {
                            //备注先取明细，明细没有，取工作表名
                            string remark = SupplementName;
                            if (remarkIndex > -1)
                            {
                                remark = value22[j, remarkIndex].ToString();
                            }
                            deductionInputs.Add(new Finance_CustomerDeliveryBillDetailInput
                            {
                                Amount = GetDecimalValue(value22, feeIndex, j),
                                ConfirmedAmount = GetDecimalValue(value22, feeIndex, j),
                                Remark = remark,
                                TrackingNumber = value22[j, trackingIndex].ToString(),
                                FinanceCustomerSupplementType = wsb.Name == "扣款-仓库退件" 
                                ? FinanceCustomerSupplementOrDeductionType.扣_退件退回 : FinanceCustomerSupplementOrDeductionType.None
                            });
                        }
                    }
                }

                item.JobProgress = 75;
                item.Step = "导入扣款数据";
                //System.Threading.Thread.Sleep(1000);
                await UpdateTaskStatus(item);

                try
                {
                    //if (deductionInputs.Count > 0)
                    await repository.InputSupplementOrDeduction(bill.Id, deductionInputs, 1);
                }
                catch (Exception ex)
                {
                    await SendError(item, "导入扣款失败" + ex.Message);
                    return;
                }

                //索赔
                item.JobProgress = 80;
                item.Step = "分析索赔数据";
                await UpdateTaskStatus(item);

                List<Finance_CustomerDeliveryBillClaimInput> claimInputs = new List<Finance_CustomerDeliveryBillClaimInput>();
                if (claimIndex.Count > 0)
                {
                    //索赔数据
                    foreach (var index in claimIndex)
                    {
                        var wsb = (Worksheet)wb.Sheets[index];
                        //

                        range = wsb.get_Range("A1", Missing.Value);
                        colCount = GetColumnQty(wsb);
                        rowCount = GetRowQty(wsb);
                        if (rowCount == 0)
                        {
                            item.Status = wsb.Name + "读取索赔失败，工作表中没有数据";
                            await UpdateTaskStatus(item);
                            return;
                        }
                        //读取列表头
                        range = range.get_Resize(1, colCount);
                        object[,] value22 = (object[,])range.Value2;
                        int trackingIndex = -1;
                        int totalAmountIndex = -1;
                        int solutionIndex = -1;
                        int costIndex = -1;
                        int freightIndex = -1;
                        int totalValueIndex = -1;
                        string[] nameSplit = wsb.Name.Split('-');
                        string ClaimName = "";
                        if (nameSplit.Length > 1)
                        {
                            ClaimName = nameSplit[1];
                        }
                        else
                        {
                            ClaimName = nameSplit[0];
                        }


                        for (int j = 1; j <= colCount; j++)
                        {
                            string cname = value22[1, j].ToString();
                            if (cname == "运单号" || cname == "跟踪号")
                            {
                                trackingIndex = j;
                            }
                            if (cname == "成本")
                            {
                                costIndex = j;
                            }
                            if (cname == "运费")
                            {
                                freightIndex = j;
                            }
                            if (cname == "申报价值（人民币）")
                            {
                                totalValueIndex = j;
                            }
                            if (cname == "总金额")
                            {
                                totalAmountIndex = j;
                            }
                            if (cname == "索赔原因")
                            {
                                solutionIndex = j;
                            }
                        }


                        range = range.get_Resize(rowCount, colCount);
                        value22 = (object[,])range.Value2;
                        //逐行分析
                        if (value22[rowCount, 1] == Missing.Value || value22[rowCount, 1] == null)
                        {
                            //最后一行如果是合计列的话，我们去掉
                            rowCount -= 1;
                        }

                        string errorMessage = "";

                        if (trackingIndex == -1)
                        {
                            errorMessage += wsb.Name + "不包含列跟踪号【运单号】";
                        }
                        else if (totalAmountIndex == -1)
                        {
                            errorMessage += wsb.Name + "不包含列【总金额】";
                        }
                        else if (costIndex == -1)
                        {
                            errorMessage += wsb.Name + "不包含列【成本】";
                        }
                        else if (freightIndex == -1)
                        {
                            errorMessage += wsb.Name + "不包含列【运费】";
                        }
                        else if (totalValueIndex == -1)
                        {
                            errorMessage += wsb.Name + "不包含列【申报价值（人民币）】";
                        }

                        if (!string.IsNullOrEmpty(errorMessage))
                        {
                            item.Status = errorMessage;
                            await UpdateTaskStatus(item);
                            return;
                        }

                        for (int j = 2; j <= rowCount; j++)
                        {
                            string solution = string.Empty;
                            string remark = ClaimName;
                            if (solutionIndex > -1)
                            {
                                solution = value22[j, solutionIndex].ToString();
                            }
                            claimInputs.Add(new Finance_CustomerDeliveryBillClaimInput
                            {
                                TotalAmount = GetDecimalValue(value22, totalAmountIndex, j),
                                Cost = GetDecimalValue(value22, costIndex, j),
                                Freight = GetDecimalValue(value22, freightIndex, j),
                                TotalValue = GetDecimalValue(value22, totalValueIndex, j),
                                Solution = solution,
                                TrackingNumber = value22[j, trackingIndex].ToString(),
                                Remark = remark
                            });
                        }
                    }
                }

                item.JobProgress = 85;
                item.Step = $"导入索赔数据({claimInputs.Count}条)";
                await UpdateTaskStatus(item);

                try
                {
                    if (claimInputs.Count > 0)
                        await repository.InputClaim(bill.Id, claimInputs);
                }
                catch (Exception ex)
                {
                    await SendError(item, "导入索赔失败" + ex.Message);
                    return;
                }


                item.JobProgress = 90;
                item.Step = "分析汇总数据";
                //System.Threading.Thread.Sleep(1000);
                await UpdateTaskStatus(item);
                try
                {


                    var nowBill = await repository.GetAsync(bill.Id);
                    if (nowBill != null)
                    {
                        decimal claimAmount = nowBill.ClaimAmount.HasValue ? nowBill.ClaimAmount.Value : 0m;
                        decimal nowBillTotal = Math.Round(nowBill.ConfirmedFreigh + nowBill.Supplement + nowBill.Deduction + claimAmount, 2);
                        decimal destSumFee = Math.Round(sumFee, 2);
                        if (nowBillTotal != destSumFee)
                        {
                            //金额对不上
                            await SendError(item, $"导入失败，金额错误，运费[{nowBill.ConfirmedFreigh}]，补款[{nowBill.Supplement}]，扣款[{nowBill.Deduction}]，索赔[{claimAmount}]，明细总额[{nowBillTotal}]:表1总额[{destSumFee}]");
                            return;
                        }
                        else
                        {
                            if (nowBill.Status == FiannceServiceBillStatus.待对帐)
                                await repository.Pass(nowBill.Id, false, true);

                        }
                    }
                }
                catch (Exception ex)
                {
                    await SendError(item, ex.Message);
                }


                item.JobProgress = 100;
                item.Step = "通过帐单，处理完毕";
                //System.Threading.Thread.Sleep(1000);
                await UpdateTaskStatus(item);
            }
            catch (Exception ex)
            {
                await SendError(item, "执行失败" + ex.Message);
                return;
            }
            finally
            {
                if (app != null)
                {
                    app.Quit();
                }

            }
        }



        private List<Finance_Task> UserTasks;

        public async Task Start(string url)
        {
            if (string.IsNullOrEmpty(url))
            {
                await SendAsnyc("TaskError", "必须上传一个任务文件");
                return;
            }

            var fileList = await DownAndUnZip(url);
            if (fileList == null)
                return;
            if (fileList.Count == 0)
            {
                await SendAsnyc("TaskError", "压缩包内xlsx文件数量为0，请检查");
                return;
            }

            List<Finance_Task> tasks = new List<Finance_Task>();
            for (int i = 0; i < fileList.Count; i++)
            {
                System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"^.*?\-(.*)\.xlsx$");
                string fileName = new System.IO.FileInfo(fileList[i]).Name;
                if (regex.IsMatch(fileName))
                {
                    var math = regex.Match(fileName);
                    fileName = math.Groups[1].Value;
                }
                //string fileName = new System.IO.FileInfo(fileList[i]).Name.Replace(".xlsx", "");
                //拆分子任务
                tasks.Add(new Finance_Task
                {
                    FileName = fileList[i],
                    CreateTime = DateTime.Now,
                    Guid = Guid.NewGuid(),
                    JobName = fileName,
                    JobProgress = 0,
                    Status = "等待执行",
                    Step = "检查文件名"
                });
            }
            UserTasks = await GetTaskItems(UserName);
            UserTasks.AddRange(tasks);
            distributedCache.SetString(UserName, Newtonsoft.Json.JsonConvert.SerializeObject(UserTasks));
            //通知前端页面增加监控
            await Clients.Group(this.UserName).SendAsync("AddTaskStatus", tasks);

            //执行逻辑
            //步骤通知
            foreach (var item in tasks)
            {
                await StartItem(item);
            }
        }



        public async Task Clear()
        {
            await distributedCache.RemoveAsync(this.UserName);
        }

        public async Task<List<Finance_Task>> GetTaskItems(string userName)
        {
            string val = await distributedCache.GetStringAsync(userName);
            if (!string.IsNullOrEmpty(val))
            {
                return Newtonsoft.Json.JsonConvert.DeserializeObject<List<Finance_Task>>(val);
            }
            else
            {
                return new List<Finance_Task>();
            }
        }

        private static decimal GetDecimalValue(object[,] value22, int feeIndex, int j)
        {
            if (feeIndex == -1)
                return 0;
            return (decimal)((double)value22[j, feeIndex]);
        }

        private static int GetRowQty(Worksheet sh)
        {
            return sh.Cells.Find("*", System.Reflection.Missing.Value,
                             System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                             Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious,
                             false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
        }
        private static int GetColumnQty(Worksheet sh)
        {
            return sh.Cells.Find("*", System.Reflection.Missing.Value,
                                System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                Microsoft.Office.Interop.Excel.XlSearchOrder.xlByColumns, Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious,
                                false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Column;
        }
        /// <summary>
        /// 更新任务状态
        /// </summary>
        /// <param name="finance_Task"></param>
        /// <param name="account"></param>
        /// <returns></returns>
        public async Task UpdateTaskStatus(Finance_Task finance_Task)
        {
            await Clients.Group(UserName).SendAsync("UpdateTaskStatus", finance_Task);
            //同时更新缓存服务任务状态            
            distributedCache.SetString(UserName, Newtonsoft.Json.JsonConvert.SerializeObject(UserTasks));
        }
    }
}
