using Aspose.Cells;
using KGIS.Framework.DBOperator;
using KGIS.Framework.Maps;
using KGIS.Framework.Utils;
using KGIS.Framework.Utils.Helper;
using Kingo.Plugin.YJJK.ModelEntity;
using Kingo.PluginServiceInterface;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;

namespace Kingo.Plugin.YJJK.ZLBBReport
{
    /// <summary>
    /// 废弃与垃圾填埋细化标注变化统计表
    /// </summary>
    public class ExportXHBZBHTJB
    {
        /// <summary>
        /// 执行导出
        /// </summary>
        /// <param name="savePath">保存位置</param>
        /// <param name="baseReportPath">获取年初表位置</param>
        /// <returns>是否成功</returns>
        public bool Export(string savePath, string baseReportPath, IRDBHelper dbHelper)
        {
            try
            {
                DataTable dt = null;
                if (string.IsNullOrWhiteSpace(savePath))
                {
                    MessageHelper.ShowError("存储位置错误请重试!");
                    return false;
                }
                string sourceFile = string.Empty;
                DirectoryInfo dir = new DirectoryInfo(baseReportPath);
                //FileInfo[] fileSet = dir.GetFiles(System.IO.Path.GetFileName("*废弃与垃圾填埋细化标注汇总统计表.xlsx"), SearchOption.AllDirectories);
                FileInfo[] fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")废弃与垃圾填埋细化标注汇总统计表.xlsx"), SearchOption.AllDirectories);
                if (fileSet != null && fileSet.Length > 0)
                {
                    sourceFile = fileSet[0].FullName;
                }
                else
                {
                    fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")废弃与垃圾填埋细化标注汇总统计表.xls"), SearchOption.AllDirectories);
                    if (fileSet != null && fileSet.Length > 0)
                    {
                        sourceFile = fileSet[0].FullName;
                    }
                }
                if (!System.IO.File.Exists(sourceFile))
                {
                    //Common.Helper.MessageHelper.ShowError("年初基础表不存在,请重试!");
                    //Common.Utility.LogAPI.Debug("废弃与垃圾填埋细化标注汇总统计表 不能被 成功 找到\r\n");
                    return false;
                }
                //Common.Utility.LogAPI.Debug("废弃与垃圾填埋细化标注汇总统计表 能被 成功 找到\r\n");

                // 地类字典
                Dictionary<string, string> dic2to1 = LandTypeDicHelper.GetLevel2ToLevel1LandTypeDic();
                Dictionary<string, string> dic1 = LandTypeDicHelper.GetLevel1LandTypeDic();
                Dictionary<string, double[]> dicArea = new Dictionary<string, double[]>();

                // 读取模板
                string tempPath = SysAppPath.GetCurrentAppPath() + @"\TempalateReports\BGTJ\";
                string tempFile = tempPath + "废弃与垃圾填埋细化标注变化统计表.xlsx";
                string targetFile = savePath;

                Workbook workbook = new Workbook(tempFile);
                Worksheet sheet = workbook.Worksheets[0];

                Workbook sourceWorkbook = new Workbook(sourceFile);
                Worksheet sourceSheet = sourceWorkbook.Worksheets[0];
                Cells cellsNC = sourceWorkbook.Worksheets[0].Cells;
                int valueRow = 0;
                for (int i = 0; i < cellsNC.MaxRow; i++)
                {
                    if (cellsNC[i, 1].IsMerged == false && cellsNC[i, 1].Value != null && cellsNC[i, 1].Value.ToString() == "名称")
                    {
                        valueRow = i + 2;
                        break;
                    }
                    if (cellsNC[i, 1].IsMerged == true && cellsNC[i, 1].Value != null && cellsNC[i, 1].Value.ToString() == "名称")
                    {
                        valueRow = i + cellsNC[i, 1].GetMergedRange().RowCount + 1;
                        break;
                    }
                }
                Range sourceRange = sourceSheet.Cells.CreateRange("D" + valueRow + ":M" + valueRow);
                //Range sourceRange = sourceSheet.Cells.CreateRange("C5:L5");
                Range dataRange = null;

                #region 年初面积操作
                // 年初面积操作
                dataRange = sheet.Cells.CreateRange("B5:K5");
                for (int i = 0; i < sourceRange.ColumnCount; i++)
                {
                    if (sourceRange[0, i].Value == null || string.IsNullOrWhiteSpace(sourceRange[0, i].StringValue))
                    {
                        sourceRange[0, i].Value = 0;
                    }
                    dataRange[0, i].Value = sourceRange[0, i].Value;
                }
                #endregion

                #region 增减面积操作
                // 增减面积操作
                string sql = string.Empty;
                dataRange = sheet.Cells.CreateRange("B3:K4");

                Cell level1Cell = null;
                Cell level2Cell = null;

                Regex rex = new Regex(@"(?<=\()[^()]+(?=\))");
                Match DLBMStr = null;

                List<CellInfo> cellList = new List<CellInfo>();
                List<CellInfo> tempList = new List<CellInfo>();

                string Level1Name = string.Empty;
                string Level2Name = string.Empty;

                string Level1 = string.Empty;
                string Level2 = string.Empty;

                for (int i = 0; i < dataRange.ColumnCount; i++)
                {
                    level1Cell = dataRange[0, i];
                    level2Cell = dataRange[1, i];

                    if (!string.IsNullOrWhiteSpace(level1Cell.StringValue))
                    {
                        Level1Name = level1Cell.StringValue.Replace("（", "(").Replace("）", ")");
                    }
                    if (!string.IsNullOrWhiteSpace(level2Cell.StringValue))
                    {
                        Level2Name = level2Cell.StringValue.Replace("（", "(").Replace("）", ")");
                    }
                    Level1 = rex.Match(Level1Name).Value;
                    Level2 = Level2Name.Contains("小计") ? "小计" : rex.Match(Level2Name).Value;

                    CellInfo cellInfo = new CellInfo()
                    {
                        Level1Name = Level1,
                        Level2Name = Level2,
                        CellColumn = level2Cell.Column
                    };

                    cellList.Add(cellInfo);
                }

                // 添加基础数据
                double valueAdd = 0;
                double valueMin = 0;
                double valueHR = 0;
                double valueHC = 0;
                foreach (CellInfo cell in cellList)
                {
                    if (cell.Level2Name == "小计") continue;


                    // 增加
                    sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ  " +
                        "WHERE  (BGHDLBM LIKE '{0}%') AND (BGHTBXHDM = '{1}') " +
                        "AND ((BGQDLBM NOT LIKE '{0}%') OR (BGQTBXHDM <> '{1}'))", cell.Level2Name, cell.Level1Name);
                    dt = dbHelper.ExecuteDatatable("tb", sql, true);
                    valueAdd = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                    sheet.Cells[8, cell.CellColumn].Value = valueAdd;

                    // 减少
                    sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ  " +
                        "WHERE (BGQDLBM LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
                        "AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{1}'))", cell.Level2Name, cell.Level1Name);
                    dt = dbHelper.ExecuteDatatable("tb", sql, true);
                    valueMin = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                    sheet.Cells[9, cell.CellColumn].Value = valueMin;

                    // 划入
                    sql = string.Format(@"select SUM(BGMJ) from 	
    (
    select 
	case when XZQTZLX = '1' THEN bgq else bgh END DLBM,
  case when XZQTZLX = '1' THEN BGQTBXHDM else BGHTBXHDM END TBXHDM, BGMJ
    from
    (
    select 
	case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,
	case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQTBXHDM, BGHTBXHDM, XZQTZLX from
    (
    select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQTBXHDM, BGHTBXHDM, XZQTZLX
    from JCTJB_GQ where XZQTZLX in ('1', '3')
    )A
    )A
    WHERE(DLBM LIKE '{0}%') AND(TBXHDM = '{1}')
    )A ", cell.Level2Name, cell.Level1Name);
                    dt = dbHelper.ExecuteDatatable("tb", sql, true);
                    valueHR = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                    sheet.Cells[5, cell.CellColumn].Value = valueHR;

                    // 划出
                    sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
                        "WHERE (BGQDLBM LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
                        "AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{1}')) and (XZQTZLX='2' or XZQTZLX='4')", cell.Level2Name, cell.Level1Name);
                    dt = dbHelper.ExecuteDatatable("tb", sql, true);
                    valueHC = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                    sheet.Cells[6, cell.CellColumn].Value = valueHC;

                    sheet.Cells[7, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[4, cell.CellColumn].Value) + valueHR - valueHC;

                }

                // 小计统计
                foreach (CellInfo cell in cellList)
                {
                    if (cell.Level2Name != "小计") continue;

                    valueAdd = 0;
                    valueMin = 0;
                    valueHR = 0;
                    valueHC = 0;
                    tempList = cellList.FindAll(x => x.Level2Name != "小计" && x.Level1Name == cell.Level1Name);
                    foreach (var item in tempList)
                    {
                        valueAdd += sheet.Cells[8, item.CellColumn].DoubleValue;
                        valueMin += sheet.Cells[9, item.CellColumn].DoubleValue;
                        valueHR += sheet.Cells[5, item.CellColumn].DoubleValue;
                        valueHC += sheet.Cells[6, item.CellColumn].DoubleValue;
                    }
                    sheet.Cells[8, cell.CellColumn].Value = valueAdd;
                    sheet.Cells[9, cell.CellColumn].Value = valueMin;
                    sheet.Cells[5, cell.CellColumn].Value = valueHR;
                    sheet.Cells[6, cell.CellColumn].Value = valueHC;

                    sheet.Cells[7, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[4, cell.CellColumn].Value) + valueHR - valueHC;
                }

                #endregion

                #region 年末面积操作
                // 年末面积操作
                dataRange = sheet.Cells.CreateRange("B5:K11");
                for (int i = 0; i < sourceRange.ColumnCount; i++)
                {
                    for (int j = 0; j < 6; j++)
                    {
                        string s = dataRange[j, i].Value == null ? "0" : dataRange[j, i].Value.ToString();
                        dataRange[j, i].PutValue(s, true);
                        Style style = dataRange[j, i].GetStyle();
                        style.Number = 2;
                        dataRange[j, i].SetStyle(style);
                    }
                    dataRange[6, i].Value = dataRange[3, i].DoubleValue + dataRange[4, i].DoubleValue - dataRange[5, i].DoubleValue;
                }

                #endregion


                // 保存EXCEL
                workbook.Save(targetFile);


                return true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {

            }
        }

        public BBPCModel Export(string savePath, string baseReportPath, IRDBHelper dbHelper, ref List<BBPCModel> pcModelList)
        {
            BBPCModel result = null;
            try
            {
                DataTable dt = null;
                if (string.IsNullOrWhiteSpace(savePath))
                {
                    MessageHelper.ShowError("存储位置错误请重试!");
                    return result;
                }
                string sourceFile = string.Empty;
                DirectoryInfo dir = new DirectoryInfo(baseReportPath);
                FileInfo[] fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")废弃与垃圾填埋细化标注汇总统计表.xlsx"), SearchOption.AllDirectories);
                if (fileSet != null && fileSet.Length > 0)
                {
                    sourceFile = fileSet[0].FullName;
                }
                else
                {
                    fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")废弃与垃圾填埋细化标注汇总统计表.xls"), SearchOption.AllDirectories);
                    if (fileSet != null && fileSet.Length > 0)
                    {
                        sourceFile = fileSet[0].FullName;
                    }
                }
                if (!System.IO.File.Exists(sourceFile))
                {
                    return result;
                }
                // 地类字典
                Dictionary<string, string> dic2to1 = LandTypeDicHelper.GetLevel2ToLevel1LandTypeDic();
                Dictionary<string, string> dic1 = LandTypeDicHelper.GetLevel1LandTypeDic();
                Dictionary<string, double[]> dicArea = new Dictionary<string, double[]>();

                // 读取模板
                string tempPath = SysAppPath.GetCurrentAppPath() + @"\TempalateReports\BGTJ\";
                string tempFile = tempPath + "废弃与垃圾填埋细化标注变化统计表.xlsx";
                string targetFile = savePath;

                Workbook workbook = new Workbook(tempFile);
                Worksheet sheet = workbook.Worksheets[0];

                Workbook sourceWorkbook = new Workbook(sourceFile);
                Worksheet sourceSheet = sourceWorkbook.Worksheets[0];
                Cells cellsNC = sourceWorkbook.Worksheets[0].Cells;
                int valueRow = 0;
                for (int i = 0; i < cellsNC.MaxRow; i++)
                {
                    if (cellsNC[i, 1].IsMerged == false && cellsNC[i, 1].Value != null && cellsNC[i, 1].Value.ToString() == "名称")
                    {
                        valueRow = i + 2;
                        break;
                    }
                    if (cellsNC[i, 1].IsMerged == true && cellsNC[i, 1].Value != null && cellsNC[i, 1].Value.ToString() == "名称")
                    {
                        valueRow = i + cellsNC[i, 1].GetMergedRange().RowCount + 1;
                        break;
                    }
                }
                Range sourceRange = sourceSheet.Cells.CreateRange("D" + valueRow + ":M" + valueRow);
                Range dataRange = null;

                #region 年初面积操作
                // 年初面积操作
                dataRange = sheet.Cells.CreateRange("B5:K5");
                for (int i = 0; i < sourceRange.ColumnCount; i++)
                {
                    if (sourceRange[0, i].Value == null || string.IsNullOrWhiteSpace(sourceRange[0, i].StringValue))
                    {
                        sourceRange[0, i].Value = 0;
                    }
                    dataRange[0, i].Value = sourceRange[0, i].Value;
                }
                #endregion

                #region 增减面积操作
                // 增减面积操作
                string sql = string.Empty;
                dataRange = sheet.Cells.CreateRange("B3:K4");

                Cell level1Cell = null;
                Cell level2Cell = null;

                Regex rex = new Regex(@"(?<=\()[^()]+(?=\))");
                Match DLBMStr = null;

                List<CellInfo> cellList = new List<CellInfo>();
                List<CellInfo> tempList = new List<CellInfo>();

                string Level1Name = string.Empty;
                string Level2Name = string.Empty;

                string Level1 = string.Empty;
                string Level2 = string.Empty;

                for (int i = 0; i < dataRange.ColumnCount; i++)
                {
                    level1Cell = dataRange[0, i];
                    level2Cell = dataRange[1, i];

                    if (!string.IsNullOrWhiteSpace(level1Cell.StringValue))
                    {
                        Level1Name = level1Cell.StringValue.Replace("（", "(").Replace("）", ")");
                    }
                    if (!string.IsNullOrWhiteSpace(level2Cell.StringValue))
                    {
                        Level2Name = level2Cell.StringValue.Replace("（", "(").Replace("）", ")");
                    }
                    Level1 = rex.Match(Level1Name).Value;
                    Level2 = Level2Name.Contains("小计") ? "小计" : rex.Match(Level2Name).Value;

                    CellInfo cellInfo = new CellInfo()
                    {
                        Level1Name = Level1,
                        Level2Name = Level2,
                        CellColumn = level2Cell.Column
                    };

                    cellList.Add(cellInfo);
                }

                // 添加基础数据
                double valueAdd = 0;
                double valueMin = 0;
                double valueHR = 0;
                double valueHC = 0;
                foreach (CellInfo cell in cellList)
                {
                    if (cell.Level2Name == "小计") continue;


                    // 增加
                    sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
                        "WHERE  XZQTZLX not in ('1','2','3','4') and (BGHDLBM LIKE '{0}%') AND (BGHTBXHDM = '{1}') " +
                        "AND ((BGQDLBM NOT LIKE '{0}%') OR (BGQTBXHDM <> '{1}'))", cell.Level2Name, cell.Level1Name);
                    dt = dbHelper.ExecuteDatatable("tb", sql, true);
                    valueAdd = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                    sheet.Cells[8, cell.CellColumn].Value = valueAdd;

                    // 减少
                    sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
                        "WHERE XZQTZLX not in ('1','2','3','4') and (BGQDLBM LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
                        "AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{1}'))", cell.Level2Name, cell.Level1Name);
                    dt = dbHelper.ExecuteDatatable("tb", sql, true);
                    valueMin = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                    sheet.Cells[9, cell.CellColumn].Value = valueMin;

                    // 划入
                    //sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
                    //    "WHERE (BGHDLBM LIKE '{0}%') AND (BGHTBXHDM = '{1}') " +
                    //    "AND ((BGQDLBM NOT LIKE '{0}%') OR (BGQTBXHDM <> '{1}')) and (XZQTZLX='1' or XZQTZLX='3')", cell.Level2Name, cell.Level1Name);
                    sql = string.Format(@"select SUM(BGMJ) from 	
    (
    select 
	case when XZQTZLX = '1' THEN bgq else bgh END DLBM,
  case when XZQTZLX = '1' THEN BGQTBXHDM else BGHTBXHDM END TBXHDM, BGMJ
    from
    (
    select 
	case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,
	case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQTBXHDM, BGHTBXHDM, XZQTZLX from
    (
    select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQTBXHDM, BGHTBXHDM, XZQTZLX
    from JCTJB_GQ where XZQTZLX in ('1', '3')
    )A
    )A
    WHERE(DLBM LIKE '{0}%') AND(TBXHDM = '{1}')
    )A ", cell.Level2Name, cell.Level1Name);
                    dt = dbHelper.ExecuteDatatable("tb", sql, true);
                    valueHR = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                    sheet.Cells[5, cell.CellColumn].Value = valueHR;

                    // 划出
                    sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
                        "WHERE (BGQDLBM LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
                        "AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{1}')) and (XZQTZLX='2' or XZQTZLX='4')", cell.Level2Name, cell.Level1Name);
                    dt = dbHelper.ExecuteDatatable("tb", sql, true);
                    valueHC = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                    sheet.Cells[6, cell.CellColumn].Value = valueHC;

                    sheet.Cells[7, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[4, cell.CellColumn].Value) + valueHR - valueHC;

                }

                // 小计统计
                foreach (CellInfo cell in cellList)
                {
                    if (cell.Level2Name != "小计") continue;

                    valueAdd = 0;
                    valueMin = 0;
                    valueHR = 0;
                    valueHC = 0;
                    tempList = cellList.FindAll(x => x.Level2Name != "小计" && x.Level1Name == cell.Level1Name);
                    foreach (var item in tempList)
                    {
                        valueAdd += sheet.Cells[8, item.CellColumn].DoubleValue;
                        valueMin += sheet.Cells[9, item.CellColumn].DoubleValue;
                        valueHR += sheet.Cells[5, item.CellColumn].DoubleValue;
                        valueHC += sheet.Cells[6, item.CellColumn].DoubleValue;
                    }
                    sheet.Cells[8, cell.CellColumn].Value = valueAdd;
                    sheet.Cells[9, cell.CellColumn].Value = valueMin;
                    sheet.Cells[5, cell.CellColumn].Value = valueHR;
                    sheet.Cells[6, cell.CellColumn].Value = valueHC;

                    sheet.Cells[7, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[4, cell.CellColumn].Value) + valueHR - valueHC;
                }
                #endregion

                #region 年末面积操作
                // 年末面积操作
                dataRange = sheet.Cells.CreateRange("B5:K11");
                for (int i = 0; i < sourceRange.ColumnCount; i++)
                {
                    for (int j = 0; j < 6; j++)
                    {
                        string s = dataRange[j, i].Value == null ? "0" : dataRange[j, i].Value.ToString();
                        dataRange[j, i].PutValue(s, true);
                        Style style = dataRange[j, i].GetStyle();
                        style.Number = 2;
                        dataRange[j, i].SetStyle(style);
                    }
                    dataRange[6, i].Value = dataRange[3, i].DoubleValue + dataRange[4, i].DoubleValue - dataRange[5, i].DoubleValue;
                    if (dataRange[6, i].DoubleValue < 0)
                    {
                        if (result == null)
                        {
                            result = new BBPCModel();
                            result.ReportName = 15;
                            result.Columns = new List<PCColumnModel>();
                        }
                        PCColumnModel coluModel = new PCColumnModel();
                        coluModel.Ids = new List<int>();
                        coluModel.IsActive = true;
                        coluModel.ColumnIndex = i;
                        coluModel.DiffValue = Math.Abs(dataRange[6, i].DoubleValue);

                        CellInfo cell = cellList[i];
                        if (cell.Level2Name == "小计") continue;

                        // 减少
                        sql = string.Format("SELECT ID FROM JCTJB_GQ " +
                            "WHERE (BGQDLBM LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
                            "AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{1}')) AND XZQTZLX='0'", cell.Level2Name, cell.Level1Name);

                        DataTable IDsTable = dbHelper.ExecuteDatatable("ids", sql, true);
                        string ids = string.Empty;
                        foreach (DataRow row in IDsTable.Rows)
                        {
                            coluModel.Ids.Add(Convert.ToInt32(row[0]));
                        }
                        result.Columns.Add(coluModel);
                    }
                }

                #endregion
                return result;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {

            }
        }

        public BBPCModel CheckNegative(string baseReportPath, IRDBHelper dbHelper, bool isTZMJPC, ref List<BBPCModel> TZNCbBPCModels)
        {
            try
            {
                BBPCModel result = null;
                BBPCModel TZresult = null;
                DataTable dt = null;
                string sourceFile = string.Empty;
                DirectoryInfo dir = new DirectoryInfo(baseReportPath);
                //FileInfo[] fileSet = dir.GetFiles(System.IO.Path.GetFileName("*废弃与垃圾填埋细化标注汇总统计表.xlsx"), SearchOption.AllDirectories);
                FileInfo[] fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")废弃与垃圾填埋细化标注汇总统计表.xlsx"), SearchOption.AllDirectories);
                if (fileSet != null && fileSet.Length > 0)
                {
                    sourceFile = fileSet[0].FullName;
                }
                else
                {
                    fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")废弃与垃圾填埋细化标注汇总统计表.xls"), SearchOption.AllDirectories);
                    if (fileSet != null && fileSet.Length > 0)
                    {
                        sourceFile = fileSet[0].FullName;
                    }
                }
                if (!System.IO.File.Exists(sourceFile))
                {
                    return result;
                }
                // 地类字典
                Dictionary<string, string> dic2to1 = LandTypeDicHelper.GetLevel2ToLevel1LandTypeDic();
                Dictionary<string, string> dic1 = LandTypeDicHelper.GetLevel1LandTypeDic();
                Dictionary<string, double[]> dicArea = new Dictionary<string, double[]>();

                // 读取模板
                string tempPath = SysAppPath.GetCurrentAppPath() + @"\TempalateReports\统一时点更新统计表\原样表\";
                string tempFile = tempPath + "表 13-废弃与垃圾填埋细化标注变化统计表.xlsx";

                Workbook workbook = new Workbook(tempFile);
                Worksheet sheet = workbook.Worksheets[0];

                Workbook sourceWorkbook = new Workbook(sourceFile);
                Worksheet sourceSheet = sourceWorkbook.Worksheets[0];
                Cells cellsNC = sourceWorkbook.Worksheets[0].Cells;
                int valueRow = 0;
                for (int i = 0; i < cellsNC.MaxRow; i++)
                {
                    if (cellsNC[i, 0].IsMerged == false && cellsNC[i, 0].Value != null && cellsNC[i, 0].Value.ToString() == "名称")
                    {
                        valueRow = i + 2;
                        break;
                    }
                    if (cellsNC[i, 0].IsMerged == true && cellsNC[i, 0].Value != null && cellsNC[i, 0].Value.ToString() == "名称")
                    {
                        valueRow = i + cellsNC[i, 0].GetMergedRange().RowCount + 1;
                        break;
                    }
                }
                Range sourceRange = sourceSheet.Cells.CreateRange("C" + valueRow + ":L" + valueRow);
                Range dataRange = null;

                #region 年初面积操作
                // 年初面积操作
                dataRange = sheet.Cells.CreateRange("B5:K5");
                for (int i = 0; i < sourceRange.ColumnCount; i++)
                {
                    if (sourceRange[0, i].Value == null || string.IsNullOrWhiteSpace(sourceRange[0, i].StringValue))
                    {
                        sourceRange[0, i].Value = 0;
                    }
                    dataRange[0, i].Value = sourceRange[0, i].Value;
                }
                #endregion

                #region 增减面积操作
                // 增减面积操作
                string sql = string.Empty;
                dataRange = sheet.Cells.CreateRange("B3:K4");

                Cell level1Cell = null;
                Cell level2Cell = null;

                Regex rex = new Regex(@"(?<=\()[^()]+(?=\))");
                Match DLBMStr = null;

                List<CellInfo> cellList = new List<CellInfo>();
                List<CellInfo> tempList = new List<CellInfo>();

                string Level1Name = string.Empty;
                string Level2Name = string.Empty;

                string Level1 = string.Empty;
                string Level2 = string.Empty;

                for (int i = 0; i < dataRange.ColumnCount; i++)
                {
                    level1Cell = dataRange[0, i];
                    level2Cell = dataRange[1, i];

                    if (!string.IsNullOrWhiteSpace(level1Cell.StringValue))
                    {
                        Level1Name = level1Cell.StringValue.Replace("（", "(").Replace("）", ")");
                    }
                    if (!string.IsNullOrWhiteSpace(level2Cell.StringValue))
                    {
                        Level2Name = level2Cell.StringValue.Replace("（", "(").Replace("）", ")");
                    }
                    Level1 = rex.Match(Level1Name).Value;
                    Level2 = Level2Name.Contains("小计") ? "小计" : rex.Match(Level2Name).Value;

                    CellInfo cellInfo = new CellInfo()
                    {
                        Level1Name = Level1,
                        Level2Name = Level2,
                        CellColumn = level2Cell.Column
                    };

                    cellList.Add(cellInfo);
                }

                // 添加基础数据
                double valueAdd = 0;
                double valueMin = 0;
                double valueHR = 0;
                double valueHC = 0;
                foreach (CellInfo cell in cellList)
                {
                    if (cell.Level2Name == "小计") continue;


                    // 增加
                    sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
                        "WHERE (BGHDLBM LIKE '{0}%') AND (BGHTBXHDM = '{1}') " +
                        "AND ((BGQDLBM NOT LIKE '{0}%') OR (BGQTBXHDM <> '{1}'))", cell.Level2Name, cell.Level1Name);
                    dt = dbHelper.ExecuteDatatable("tb", sql, true);
                    valueAdd = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                    sheet.Cells[8, cell.CellColumn].Value = valueAdd;

                    // 减少
                    sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
                        "WHERE (BGQDLBM LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
                        "AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{1}'))", cell.Level2Name, cell.Level1Name);
                    dt = dbHelper.ExecuteDatatable("tb", sql, true);
                    valueMin = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                    sheet.Cells[9, cell.CellColumn].Value = valueMin;

                    // 划入
                    sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
                        "WHERE (BGHDLBM LIKE '{0}%') AND (BGHTBXHDM = '{1}') " +
                        "AND ((BGQDLBM NOT LIKE '{0}%') OR (BGQTBXHDM <> '{1}')) and (XZQTZLX='1' or XZQTZLX='3')", cell.Level2Name, cell.Level1Name);
                    dt = dbHelper.ExecuteDatatable("tb", sql, true);
                    valueHR = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                    sheet.Cells[5, cell.CellColumn].Value = valueHR;

                    // 划出
                    sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
                        "WHERE (BGQDLBM LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
                        "AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{1}')) and (XZQTZLX='2' or XZQTZLX='4')", cell.Level2Name, cell.Level1Name);
                    dt = dbHelper.ExecuteDatatable("tb", sql, true);
                    valueHC = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                    sheet.Cells[6, cell.CellColumn].Value = valueHC;

                    sheet.Cells[7, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[4, cell.CellColumn].Value) + valueHR - valueHC;
                    #region 调整面积负值
                    if (isTZMJPC && sheet.Cells[7, cell.CellColumn].DoubleValue < 0)
                    {
                        if (TZresult == null)
                        {
                            TZresult = new BBPCModel();
                            TZresult.ReportName = 12;
                            TZresult.Columns = new List<PCColumnModel>();
                        }
                        PCColumnModel coluModel = new PCColumnModel();
                        coluModel.Ids = new List<int>();
                        coluModel.IsActive = true;
                        coluModel.ColumnIndex = cell.CellColumn;
                        coluModel.DiffValue = Math.Abs(sheet.Cells[7, cell.CellColumn].DoubleValue);

                        sql = string.Format("SELECT ID FROM JCTJB_GQ " +
                            "WHERE (BGQDLBM LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
                            "AND XZQTZLX in ('2','4')", cell.Level2Name, cell.Level1Name);

                        DataTable IDsTable = dbHelper.ExecuteDatatable("ids", sql, true);
                        string ids = string.Empty;
                        foreach (DataRow row in IDsTable.Rows)
                        {
                            coluModel.Ids.Add(Convert.ToInt32(row[0]));
                        }
                        TZresult.Columns.Add(coluModel);
                        TZNCbBPCModels.Add(TZresult);
                    }
                    #endregion
                }

                // 小计统计
                foreach (CellInfo cell in cellList)
                {
                    if (cell.Level2Name != "小计") continue;

                    valueAdd = 0;
                    valueMin = 0;
                    valueHR = 0;
                    valueHC = 0;
                    tempList = cellList.FindAll(x => x.Level2Name != "小计" && x.Level1Name == cell.Level1Name);
                    foreach (var item in tempList)
                    {
                        valueAdd += sheet.Cells[8, item.CellColumn].DoubleValue;
                        valueMin += sheet.Cells[9, item.CellColumn].DoubleValue;
                        valueHR += sheet.Cells[5, item.CellColumn].DoubleValue;
                        valueHC += sheet.Cells[6, item.CellColumn].DoubleValue;
                    }
                    sheet.Cells[8, cell.CellColumn].Value = valueAdd;
                    sheet.Cells[9, cell.CellColumn].Value = valueMin;
                    sheet.Cells[5, cell.CellColumn].Value = valueHR;
                    sheet.Cells[6, cell.CellColumn].Value = valueHC;

                    sheet.Cells[7, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[4, cell.CellColumn].Value) + valueHR - valueHC;
                }

                #endregion

                #region 年末面积操作
                // 年末面积操作
                dataRange = sheet.Cells.CreateRange("B5:K11");
                for (int i = 0; i < sourceRange.ColumnCount; i++)
                {
                    for (int j = 0; j < 6; j++)
                    {
                        string s = dataRange[j, i].Value == null ? "0" : dataRange[j, i].Value.ToString();
                        dataRange[j, i].PutValue(s, true);
                        Style style = dataRange[j, i].GetStyle();
                        style.Number = 2;
                        dataRange[j, i].SetStyle(style);
                    }
                    dataRange[6, i].Value = dataRange[3, i].DoubleValue + dataRange[4, i].DoubleValue - dataRange[5, i].DoubleValue;
                    if (dataRange[6, i].DoubleValue < 0)
                    {
                        if (result == null)
                        {
                            result = new BBPCModel();
                            result.ReportName = 12;
                            result.Columns = new List<PCColumnModel>();
                        }
                        PCColumnModel coluModel = new PCColumnModel();
                        coluModel.Ids = new List<int>();
                        coluModel.IsActive = true;
                        coluModel.ColumnIndex = i;
                        coluModel.DiffValue = Math.Abs(dataRange[6, i].DoubleValue);

                        CellInfo cell = cellList[i];
                        if (cell.Level2Name == "小计") continue;

                        // 减少
                        sql = string.Format("SELECT ID FROM JCTJB_GQ " +
                            "WHERE (BGQDLBM LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
                            "AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{1}')) AND XZQTZLX='0'", cell.Level2Name, cell.Level1Name);

                        DataTable IDsTable = dbHelper.ExecuteDatatable("ids", sql, true);
                        string ids = string.Empty;
                        foreach (DataRow row in IDsTable.Rows)
                        {
                            coluModel.Ids.Add(Convert.ToInt32(row[0]));
                        }
                        result.Columns.Add(coluModel);
                    }
                }

                #endregion

                return result;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {

            }
        }
    }
}