You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1060 lines
48 KiB
1060 lines
48 KiB
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 ExportBFXHDLMJBHTJB |
|
{ |
|
/// <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 + ":K" + valueRow); |
|
Range dataRange = null; |
|
|
|
#region 年初面积操作 |
|
// 年初面积操作 |
|
dataRange = sheet.Cells.CreateRange("B5:I5"); |
|
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:I4"); |
|
|
|
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; |
|
if (Level1Name.Contains("合计") && string.IsNullOrWhiteSpace(Level2)) |
|
{ |
|
Level2 = "合计"; |
|
} |
|
CellInfo cellInfo = new CellInfo() |
|
{ |
|
Level1Name = Level1, |
|
Level2Name = Level2, |
|
CellColumn = level2Cell.Column |
|
}; |
|
|
|
cellList.Add(cellInfo); |
|
} |
|
|
|
List<string> xhdm = new List<string>() { "HDGD", "HQGD", "LQGD", "MQGD", "SHGD", "ZZLSZW", "ZZFLZW", "LYFLLZ", "LLJZ", "XG", "WG", "LQYD", "GCCD", "XSCD", "HDGY", "GTGY", "MTGY", "SNGY", "BLGY", "DLGY" }; |
|
// 添加基础数据 |
|
double valueAdd = 0; |
|
double valueMin = 0; |
|
double valueHR = 0; |
|
double valueHC = 0; |
|
foreach (CellInfo cell in cellList) |
|
{ |
|
if (cell.Level2Name == "合计") continue; |
|
// 增加 |
|
var BGH = ""; |
|
var BGQ = ""; |
|
if (cell.Level1Name.ToString().Contains("20")) |
|
{ |
|
BGH = "BGHCZCSXM"; |
|
BGQ = "BGQCZCSXM"; |
|
} |
|
else |
|
{ |
|
BGH = "BGHDLBM"; |
|
BGQ = "BGQDLBM"; |
|
} |
|
|
|
sql = $"SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE ({BGH} LIKE '{cell.Level2Name}%' ) AND ({BGQ} NOT LIKE '{cell.Level2Name}%')"; |
|
|
|
if (cell.Level2Name == "1104A") |
|
{ |
|
sql = @"select SUM(BGMJ) from ( |
|
SELECT |
|
case when substr(BGQDLBM, 1, 4)='1104' and BGQCZCSXM!='空' THEN BGQCZCSXM ELSE BGQDLBM END BGQDLBM, |
|
case when substr(BGHDLBM, 1, 4)='1104' and BGHCZCSXM!='空' THEN BGHCZCSXM ELSE BGHDLBM END BGHDLBM, |
|
BGHDLBM,BGHCZCSXM,BGQCZCSXM,BGMJ FROM JCTJB_GQ |
|
)A WHERE |
|
(BGHDLBM = '1104K' AND BGQDLBM != '1104A' AND BGQDLBM != '1104K' ) |
|
OR |
|
(BGHDLBM = '1104A' AND BGQDLBM != '1104A' AND BGQDLBM != '1104K')"; |
|
} |
|
if (cell.Level2Name == "1107A") |
|
{ |
|
sql = @"select SUM(BGMJ) from ( |
|
SELECT |
|
case when substr(BGQDLBM, 1, 4)='1107' and BGQCZCSXM!='空' THEN BGQCZCSXM ELSE BGQDLBM END BGQDLBM, |
|
case when substr(BGHDLBM, 1, 4)='1107' and BGHCZCSXM!='空' THEN BGHCZCSXM ELSE BGHDLBM END BGHDLBM, |
|
BGHDLBM,BGHCZCSXM,BGQCZCSXM,BGMJ FROM JCTJB_GQ |
|
)A WHERE |
|
(BGHDLBM = '1107K' AND BGQDLBM != '1107A' AND BGQDLBM != '1107K' ) |
|
OR |
|
(BGHDLBM = '1107A' AND BGQDLBM != '1107A' AND BGQDLBM != '1107K')"; |
|
} |
|
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 = $"SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE ({BGQ} LIKE '{cell.Level2Name}%' ) AND ({BGH} NOT LIKE '{cell.Level2Name}%') and XZQTZLX<> '2'"; |
|
|
|
if (cell.Level2Name == "1104A") |
|
{ |
|
sql = @"select SUM(BGMJ) from ( |
|
SELECT |
|
case when substr(BGQDLBM, 1, 4)='1104' and BGQCZCSXM!='空' THEN BGQCZCSXM ELSE BGQDLBM END BGQDLBM, |
|
case when substr(BGHDLBM, 1, 4)='1104' and BGHCZCSXM!='空' THEN BGHCZCSXM ELSE BGHDLBM END BGHDLBM, |
|
BGHDLBM,BGHCZCSXM,BGQCZCSXM,BGMJ FROM JCTJB_GQ where XZQTZLX<> '2' |
|
)A WHERE |
|
(BGQDLBM = '1104K' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K' ) |
|
OR |
|
(BGQDLBM = '1104A' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K') "; |
|
} |
|
if (cell.Level2Name == "1107A") |
|
{ |
|
sql = @"select SUM(BGMJ) from ( |
|
SELECT |
|
case when substr(BGQDLBM, 1, 4)='1107' and BGQCZCSXM!='空' THEN BGQCZCSXM ELSE BGQDLBM END BGQDLBM, |
|
case when substr(BGHDLBM, 1, 4)='1107' and BGHCZCSXM!='空' THEN BGHCZCSXM ELSE BGHDLBM END BGHDLBM, |
|
BGHDLBM,BGHCZCSXM,BGQCZCSXM,BGMJ FROM JCTJB_GQ where XZQTZLX<> '2' |
|
)A WHERE |
|
(BGQDLBM = '1107K' AND BGHDLBM != '1107A' AND BGHDLBM != '1107K' ) |
|
OR |
|
(BGQDLBM = '1107A' AND BGHDLBM != '1107A' AND BGHDLBM != '1107K') "; |
|
} |
|
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 = $"SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE ({BGH} LIKE '{cell.Level2Name}%' ) and (XZQTZLX='1' or XZQTZLX='3')"; |
|
|
|
if (cell.Level2Name == "1104A") |
|
{ |
|
sql = @" |
|
select sum(bgmj) from ( |
|
SELECT case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,bgmj FROM JCTJB_GQ WHERE |
|
(XZQTZLX='1' and (bgq='1104A' or bgq='1104K')) |
|
or |
|
(XZQTZLX='3' and ( bgh='1104A' or bgh='1104K')) |
|
) "; |
|
} |
|
if (cell.Level2Name == "1107A") |
|
{ |
|
sql = @" |
|
select sum(bgmj) from ( |
|
SELECT case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,bgmj FROM JCTJB_GQ WHERE |
|
(XZQTZLX='1' and (bgq='1107A' or bgq='1107K')) |
|
or |
|
(XZQTZLX='3' and ( bgh='1107A' or bgh='1107K')) |
|
) "; |
|
} |
|
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 = $"SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE ({BGQ} LIKE '{cell.Level2Name}%' ) AND ({BGH} NOT LIKE '{cell.Level2Name}%') and (XZQTZLX='2' or XZQTZLX='4')"; |
|
|
|
if (cell.Level2Name == "1104A") |
|
{ |
|
sql = @" |
|
SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE (XZQTZLX='2' or XZQTZLX='4') and ( |
|
(BGQDLBM = '1104K' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K' ) |
|
OR |
|
(BGQDLBM = '1104A' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K')) and BGQCZCSXM = '空' "; |
|
} |
|
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.Level1Name == cell.Level1Name && x.Level2Name != "合计"); |
|
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:I11"); |
|
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 + ":K" + valueRow); |
|
Range dataRange = null; |
|
|
|
#region 年初面积操作 |
|
// 年初面积操作 |
|
dataRange = sheet.Cells.CreateRange("B5:I5"); |
|
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:I4"); |
|
|
|
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; |
|
if (Level1Name.Contains("合计") && string.IsNullOrWhiteSpace(Level2)) |
|
{ |
|
Level2 = "合计"; |
|
} |
|
CellInfo cellInfo = new CellInfo() |
|
{ |
|
Level1Name = Level1, |
|
Level2Name = Level2, |
|
CellColumn = level2Cell.Column |
|
}; |
|
|
|
cellList.Add(cellInfo); |
|
} |
|
|
|
List<string> xhdm = new List<string>() { "HDGD", "HQGD", "LQGD", "MQGD", "SHGD", "ZZLSZW", "ZZFLZW", "LYFLLZ", "LLJZ", "XG", "WG", "LQYD", "GCCD", "XSCD", "HDGY", "GTGY", "MTGY", "SNGY", "BLGY", "DLGY" }; |
|
// 添加基础数据 |
|
double valueAdd = 0; |
|
double valueMin = 0; |
|
double valueHR = 0; |
|
double valueHC = 0; |
|
foreach (CellInfo cell in cellList) |
|
{ |
|
if (cell.Level2Name == "合计") continue; |
|
// 增加 |
|
var BGH = ""; |
|
var BGQ = ""; |
|
if (cell.Level1Name.ToString().Contains("20")) |
|
{ |
|
BGH = "BGHCZCSXM"; |
|
BGQ = "BGQCZCSXM"; |
|
} |
|
else |
|
{ |
|
BGH = "BGHDLBM"; |
|
BGQ = "BGQDLBM"; |
|
} |
|
|
|
sql = $"SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE ({BGH} LIKE '{cell.Level2Name}%' ) AND ({BGQ} NOT LIKE '{cell.Level2Name}%')"; |
|
|
|
if (cell.Level2Name == "1104A") |
|
{ |
|
sql = @"select SUM(BGMJ) from ( |
|
SELECT |
|
case when substr(BGQDLBM, 1, 4)='1104' and BGQCZCSXM!='空' THEN BGQCZCSXM ELSE BGQDLBM END BGQDLBM, |
|
case when substr(BGHDLBM, 1, 4)='1104' and BGHCZCSXM!='空' THEN BGHCZCSXM ELSE BGHDLBM END BGHDLBM, |
|
BGHDLBM,BGHCZCSXM,BGQCZCSXM,BGMJ FROM JCTJB_GQ WHERE XZQTZLX not in('1','2','3','4') |
|
)A WHERE |
|
(BGHDLBM = '1104K' AND BGQDLBM != '1104A' AND BGQDLBM != '1104K' ) |
|
OR |
|
(BGHDLBM = '1104A' AND BGQDLBM != '1104A' AND BGQDLBM != '1104K')"; |
|
} |
|
if (cell.Level2Name == "1107A") |
|
{ |
|
sql = @"select SUM(BGMJ) from ( |
|
SELECT |
|
case when substr(BGQDLBM, 1, 4)='1107' and BGQCZCSXM!='空' THEN BGQCZCSXM ELSE BGQDLBM END BGQDLBM, |
|
case when substr(BGHDLBM, 1, 4)='1107' and BGHCZCSXM!='空' THEN BGHCZCSXM ELSE BGHDLBM END BGHDLBM, |
|
BGHDLBM,BGHCZCSXM,BGQCZCSXM,BGMJ FROM JCTJB_GQ WHERE XZQTZLX not in('1','2','3','4') |
|
)A WHERE |
|
(BGHDLBM = '1107K' AND BGQDLBM != '1107A' AND BGQDLBM != '1107K' ) |
|
OR |
|
(BGHDLBM = '1107A' AND BGQDLBM != '1107A' AND BGQDLBM != '1107K')"; |
|
} |
|
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 = $"SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE ({BGQ} LIKE '{cell.Level2Name}%' ) AND ({BGH} NOT LIKE '{cell.Level2Name}%')"; |
|
|
|
if (cell.Level2Name == "1104A") |
|
{ |
|
sql = @"select SUM(BGMJ) from ( |
|
SELECT |
|
case when substr(BGQDLBM, 1, 4)='1104' and BGQCZCSXM!='空' THEN BGQCZCSXM ELSE BGQDLBM END BGQDLBM, |
|
case when substr(BGHDLBM, 1, 4)='1104' and BGHCZCSXM!='空' THEN BGHCZCSXM ELSE BGHDLBM END BGHDLBM, |
|
BGHDLBM,BGHCZCSXM,BGQCZCSXM,BGMJ FROM JCTJB_GQ WHERE XZQTZLX not in('1','2','3','4') |
|
)A WHERE |
|
(BGQDLBM = '1104K' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K' ) |
|
OR |
|
(BGQDLBM = '1104A' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K') "; |
|
} |
|
if (cell.Level2Name == "1107A") |
|
{ |
|
sql = @"select SUM(BGMJ) from ( |
|
SELECT |
|
case when substr(BGQDLBM, 1, 4)='1107' and BGQCZCSXM!='空' THEN BGQCZCSXM ELSE BGQDLBM END BGQDLBM, |
|
case when substr(BGHDLBM, 1, 4)='1107' and BGHCZCSXM!='空' THEN BGHCZCSXM ELSE BGHDLBM END BGHDLBM, |
|
BGHDLBM,BGHCZCSXM,BGQCZCSXM,BGMJ FROM JCTJB_GQ WHERE XZQTZLX not in('1','2','3','4') |
|
)A WHERE |
|
(BGQDLBM = '1107K' AND BGHDLBM != '1107A' AND BGHDLBM != '1107K' ) |
|
OR |
|
(BGQDLBM = '1107A' AND BGHDLBM != '1107A' AND BGHDLBM != '1107K') "; |
|
} |
|
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 = $"SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE ({BGH} LIKE '{cell.Level2Name}%' ) AND ({BGQ} NOT LIKE '{cell.Level2Name}%') and (XZQTZLX='1' or XZQTZLX='3')"; |
|
|
|
if (cell.Level2Name == "1104A") |
|
{ |
|
sql = @" |
|
SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE (XZQTZLX='1' or XZQTZLX='3') and ( |
|
(BGHDLBM = '1104K' AND BGQDLBM != '1104A' AND BGQDLBM != '1104K' ) |
|
OR |
|
(BGHDLBM = '1104A' AND BGQDLBM != '1104A' AND BGQDLBM != '1104K')) "; |
|
} |
|
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 = $"SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE ({BGQ} LIKE '{cell.Level2Name}%' ) AND ({BGH} NOT LIKE '{cell.Level2Name}%') and (XZQTZLX='2' or XZQTZLX='4')"; |
|
|
|
if (cell.Level2Name == "1104A") |
|
{ |
|
sql = @" |
|
SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE (XZQTZLX='2' or XZQTZLX='4') and ( |
|
(BGQDLBM = '1104K' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K' ) |
|
OR |
|
(BGQDLBM = '1104A' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K')) "; |
|
} |
|
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.Level1Name == cell.Level1Name && x.Level2Name != "合计"); |
|
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:I11"); |
|
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 = 8; |
|
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; |
|
var BGH = ""; |
|
var BGQ = ""; |
|
if (cell.Level1Name.ToString().Contains("20")) |
|
{ |
|
BGH = "BGHCZCSXM"; |
|
BGQ = "BGQCZCSXM"; |
|
} |
|
else |
|
{ |
|
BGH = "BGHDLBM"; |
|
BGQ = "BGQDLBM"; |
|
} |
|
// 减少 |
|
sql = $"SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE ({BGQ} LIKE '{cell.Level2Name}%' ) AND ({BGH} NOT LIKE '{cell.Level2Name}%')"; |
|
|
|
if (cell.Level2Name == "1104A") |
|
{ |
|
sql = @"select SUM(BGMJ) from ( |
|
SELECT |
|
case when substr(BGQDLBM, 1, 4)='1104' and BGQCZCSXM!='空' THEN BGQCZCSXM ELSE BGQDLBM END BGQDLBM, |
|
case when substr(BGHDLBM, 1, 4)='1104' and BGHCZCSXM!='空' THEN BGHCZCSXM ELSE BGHDLBM END BGHDLBM, |
|
BGHDLBM,BGHCZCSXM,BGQCZCSXM,BGMJ FROM JCTJB_GQ WHERE XZQTZLX not in('1','2','3','4') |
|
)A WHERE |
|
(BGQDLBM = '1104K' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K' ) |
|
OR |
|
(BGQDLBM = '1104A' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K') "; |
|
} |
|
if (cell.Level2Name == "1107A") |
|
{ |
|
sql = @"select SUM(BGMJ) from ( |
|
SELECT |
|
case when substr(BGQDLBM, 1, 4)='1107' and BGQCZCSXM!='空' THEN BGQCZCSXM ELSE BGQDLBM END BGQDLBM, |
|
case when substr(BGHDLBM, 1, 4)='1107' and BGHCZCSXM!='空' THEN BGHCZCSXM ELSE BGHDLBM END BGHDLBM, |
|
BGHDLBM,BGHCZCSXM,BGQCZCSXM,BGMJ FROM JCTJB_GQ WHERE XZQTZLX not in('1','2','3','4') |
|
)A WHERE |
|
(BGQDLBM = '1107K' AND BGHDLBM != '1107A' AND BGHDLBM != '1107K' ) |
|
OR |
|
(BGQDLBM = '1107A' AND BGHDLBM != '1107A' AND BGHDLBM != '1107K') "; |
|
} |
|
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)) |
|
{ |
|
//Common.Helper.MessageHelper.ShowError("年初基础表不存在,请重试!"); |
|
//Common.Utility.LogAPI.Debug("部分细化地类面积汇总表 不能被 成功 找到\r\n"); |
|
return result; |
|
} |
|
//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\统一时点更新统计表\原样表\"; |
|
string tempFile = tempPath + "表 12-部分细化地类面积变化统计表.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 + ":J"+ valueRow); |
|
Range dataRange = null; |
|
|
|
#region 年初面积操作 |
|
// 年初面积操作 |
|
dataRange = sheet.Cells.CreateRange("B5:I5"); |
|
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:I4"); |
|
|
|
Cell level1Cell = null; |
|
Cell level2Cell = null; |
|
|
|
Regex rex = new Regex(@"(?<=\()[^()]+(?=\))"); |
|
|
|
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); |
|
} |
|
|
|
List<string> xhdm = new List<string>() { "HDGD", "HQGD", "LQGD", "MQGD", "SHGD", "ZZLSZW", "ZZFLZW", "LYFLLZ", "LLJZ", "XG", "WG", "LQYD", "GCCD", "XSCD", "HDGY", "GTGY", "MTGY", "SNGY", "BLGY", "DLGY" }; |
|
// 添加基础数据 |
|
double valueAdd = 0; |
|
double valueMin = 0; |
|
double valueHR = 0; |
|
double valueHC = 0; |
|
foreach (CellInfo cell in cellList) |
|
{ |
|
if (cell.Level2Name == "合计") continue; |
|
// 增加 |
|
var BGH = ""; |
|
var BGQ = ""; |
|
if (cell.Level1Name.ToString().Contains("20")) |
|
{ |
|
BGH = "BGHCZCSXM"; |
|
BGQ = "BGQCZCSXM"; |
|
} |
|
else |
|
{ |
|
BGH = "BGHDLBM"; |
|
BGQ = "BGQDLBM"; |
|
} |
|
|
|
sql = $"SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE ({BGH} LIKE '{cell.Level2Name}%' ) AND ({BGQ} NOT LIKE '{cell.Level2Name}%')"; |
|
|
|
if (cell.Level2Name == "1104A") |
|
{ |
|
sql = @" |
|
SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE |
|
(BGHDLBM = '1104K' AND BGQDLBM != '1104A' AND BGQDLBM != '1104K' ) |
|
OR |
|
(BGHDLBM = '1104A' AND BGQDLBM != '1104A' AND BGQDLBM != '1104K') "; |
|
} |
|
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 = $"SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE ({BGQ} LIKE '{cell.Level2Name}%' ) AND ({BGH} NOT LIKE '{cell.Level2Name}%')"; |
|
|
|
if (cell.Level2Name == "1104A") |
|
{ |
|
sql = @" |
|
SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE |
|
(BGQDLBM = '1104K' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K' ) |
|
OR |
|
(BGQDLBM = '1104A' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K') "; |
|
} |
|
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 = $"SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE ({BGH} LIKE '{cell.Level2Name}%' ) AND ({BGQ} NOT LIKE '{cell.Level2Name}%') and (XZQTZLX='1' or XZQTZLX='3')"; |
|
|
|
if (cell.Level2Name == "1104A") |
|
{ |
|
sql = @" |
|
SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE (XZQTZLX='1' or XZQTZLX='3') and ( |
|
(BGHDLBM = '1104K' AND BGQDLBM != '1104A' AND BGQDLBM != '1104K' ) |
|
OR |
|
(BGHDLBM = '1104A' AND BGQDLBM != '1104A' AND BGQDLBM != '1104K')) "; |
|
} |
|
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 = $"SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE ({BGQ} LIKE '{cell.Level2Name}%' ) AND ({BGH} NOT LIKE '{cell.Level2Name}%') and (XZQTZLX='2' or XZQTZLX='4')"; |
|
|
|
if (cell.Level2Name == "1104A") |
|
{ |
|
sql = @" |
|
SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE (XZQTZLX='2' or XZQTZLX='4') and ( |
|
(BGQDLBM = '1104K' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K' ) |
|
OR |
|
(BGQDLBM = '1104A' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K')) "; |
|
} |
|
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 = 4; |
|
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); |
|
|
|
if (cell.Level1Name.ToString().Contains("20")) |
|
{ |
|
BGH = "BGHCZCSXM"; |
|
BGQ = "BGQCZCSXM"; |
|
} |
|
else |
|
{ |
|
BGH = "BGHDLBM"; |
|
BGQ = "BGQDLBM"; |
|
} |
|
sql = $"SELECT ID FROM JCTJB_GQ WHERE ({BGQ} LIKE '{cell.Level2Name}%' ) AND XZQTZLX in ('2','4')"; |
|
|
|
if (cell.Level2Name == "1104A") |
|
{ |
|
sql = @"SELECT ID FROM JCTJB_GQ WHERE (BGQDLBM = '1104K') OR (BGQDLBM = '1104A') AND XZQTZLX in ('2','4')"; |
|
} |
|
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.Level1Name == cell.Level1Name && x.Level2Name != "合计"); |
|
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:I11"); |
|
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 = 4; |
|
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; |
|
var BGH = ""; |
|
var BGQ = ""; |
|
if (cell.Level1Name.ToString().Contains("20")) |
|
{ |
|
BGH = "BGHCZCSXM"; |
|
BGQ = "BGQCZCSXM"; |
|
} |
|
else |
|
{ |
|
BGH = "BGHDLBM"; |
|
BGQ = "BGQDLBM"; |
|
} |
|
sql = $"SELECT ID FROM JCTJB_GQ WHERE ({BGQ} LIKE '{cell.Level2Name}%' ) AND ({BGH} NOT LIKE '{cell.Level2Name}%') AND XZQTZLX='0'"; |
|
|
|
if (cell.Level2Name == "1104A") |
|
{ |
|
sql = @"SELECT ID FROM JCTJB_GQ WHERE (BGQDLBM = '1104K' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K' ) OR (BGQDLBM = '1104A' AND BGHDLBM != '1104A' AND BGHDLBM != '1104K') AND XZQTZLX='0'"; |
|
} |
|
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 |
|
{ |
|
|
|
} |
|
} |
|
} |
|
}
|
|
|