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.
2275 lines
146 KiB
2275 lines
146 KiB
using Aspose.Cells; |
|
using KGIS.Framework.DBOperator; |
|
using KGIS.Framework.Maps; |
|
using KGIS.Framework.Utils; |
|
using KGIS.Framework.Utils.ExtensionMethod; |
|
using KGIS.Framework.Utils.Helper; |
|
using Kingo.Plugin.BGResultManager.Model; |
|
using Kingo.PluginServiceInterface; |
|
using System; |
|
using System.Collections.Generic; |
|
using System.Data; |
|
using System.IO; |
|
using System.Linq; |
|
using System.Text; |
|
using System.Xml; |
|
|
|
namespace Kingo.Plugin.BGResultManager.Utility |
|
{ |
|
/// <summary> |
|
/// 土地利用现状一级分类面积按权属性质变化统计表 |
|
/// </summary> |
|
public class ExportQSXZBHTJB |
|
{ |
|
/// <summary> |
|
/// 执行导出 |
|
/// </summary> |
|
/// <param name="savePath">保存位置</param> |
|
/// <param name="baseReportPath">获取年初表位置</param> |
|
/// <param name="dbHelper">数据库链接</param> |
|
/// <returns>是否成功</returns> |
|
public bool Export(string savePath, string baseReportPath, IRDBHelper dbHelper) |
|
{ |
|
try |
|
{ |
|
if (string.IsNullOrWhiteSpace(savePath)) |
|
{ |
|
MessageHelper.ShowError("存储位置错误请重试!"); |
|
return false; |
|
} |
|
string sourceFile = string.Empty; |
|
DirectoryInfo dir = new DirectoryInfo(baseReportPath); |
|
//FileInfo[] fileSet = dir.GetFiles(Path.GetFileName("*土地利用现状一级分类面积按权属性质汇总表.xlsx"), SearchOption.AllDirectories); |
|
FileInfo[] fileSet = dir.GetFiles(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(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("年初基础表不存在,请重试!"); |
|
//LogAPI.Debug("土地利用现状一级分类面积按权属性质汇总表 不能被 成功 找到\r\n"); |
|
return false; |
|
} |
|
//LogAPI.Debug("土地利用现状一级分类面积按权属性质汇总表 能被 成功 找到\r\n"); |
|
|
|
// 获取基础统计表 |
|
DataTable dt = dbHelper.ExecuteDatatable2("TB", "SELECT * FROM JCTJB_GQ", true); |
|
// 地类字典 |
|
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 + "表 3-土地利用现状一级分类面积按权属性质变化统计表.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, 0].IsMerged == false && cellsNC[i, 0].Value != null && cellsNC[i, 0].Value.ToString() == "名称") |
|
{ |
|
valueRow = i + 1; |
|
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 + ":AR" + valueRow); |
|
//Range sourceRange = sourceSheet.Cells.CreateRange("C6:AR6"); |
|
Range dataRange = null; |
|
|
|
#region 年初面积操作 |
|
// 年初面积操作 |
|
dataRange = sheet.Cells.CreateRange("B6:AQ6"); |
|
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 增减面积操作 |
|
// 增减面积操作 |
|
// 这个字典值是容量是8的数组，第一个是国家增加，第二个是集体增加，第三个是国家减少，第四个集体减少,国有划入，集体划入，国有划出，集体划出 |
|
foreach (var pair in dic1) |
|
{ |
|
if (pair.Key == "20") continue; |
|
dicArea.Add(pair.Key, new double[] { 0, 0, 0, 0, 0, 0, 0, 0 }); |
|
} |
|
|
|
string BGQ1Type = string.Empty; |
|
string BGH1Type = string.Empty; |
|
string BGQQSXZ = string.Empty; |
|
string BGHQSXZ = string.Empty; |
|
string XZQTZLX = string.Empty; |
|
|
|
foreach (DataRow row in dt.Rows) |
|
{ |
|
if (!dic2to1.Keys.Contains(row["BGQDLBM"].ToString())) |
|
{ |
|
continue; |
|
} |
|
if (!dic2to1.Keys.Contains(row["BGHDLBM"].ToString())) |
|
{ |
|
continue; |
|
} |
|
BGQ1Type = dic2to1[row["BGQDLBM"].ToString()]; |
|
BGH1Type = dic2to1[row["BGHDLBM"].ToString()]; |
|
BGQQSXZ = row["BGQQSXZ"].ToString(); |
|
BGHQSXZ = row["BGHQSXZ"].ToString(); |
|
XZQTZLX = row["XZQTZLX"].ToString(); |
|
if (BGQQSXZ != BGHQSXZ) |
|
{ |
|
if (BGQQSXZ == "国有") |
|
{ |
|
dicArea[BGQ1Type][2] += double.Parse(row["BGMJ"].ToString());//国有减少 |
|
} |
|
else |
|
{ |
|
dicArea[BGQ1Type][3] += double.Parse(row["BGMJ"].ToString());//集体减少 |
|
} |
|
if (BGHQSXZ == "国有") |
|
{ |
|
dicArea[BGH1Type][0] += double.Parse(row["BGMJ"].ToString());//国有增加 |
|
} |
|
else |
|
{ |
|
dicArea[BGH1Type][1] += double.Parse(row["BGMJ"].ToString());//集体增加 |
|
} |
|
|
|
|
|
if (BGHQSXZ == "国有" && (XZQTZLX == "1" || XZQTZLX == "3"))//国有划入 |
|
{ |
|
dicArea[BGH1Type][4] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else if (BGHQSXZ == "国有" && (XZQTZLX == "2" || XZQTZLX == "4")) |
|
{ |
|
dicArea[BGH1Type][6] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else if (BGHQSXZ == "集体" && (XZQTZLX == "1" || XZQTZLX == "3"))//集体划入 |
|
{ |
|
dicArea[BGH1Type][5] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else if (BGHQSXZ == "集体" && (XZQTZLX == "2" || XZQTZLX == "4"))//集体划出 |
|
{ |
|
dicArea[BGH1Type][7] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
} |
|
else |
|
{ |
|
if (BGQ1Type != BGH1Type) |
|
{ |
|
if (BGQQSXZ == "国有") |
|
{ |
|
dicArea[BGQ1Type][2] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else |
|
{ |
|
dicArea[BGQ1Type][3] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
if (BGHQSXZ == "国有") |
|
{ |
|
dicArea[BGH1Type][0] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else |
|
{ |
|
dicArea[BGH1Type][1] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
|
|
|
|
if (BGHQSXZ == "国有" && (XZQTZLX == "1" || XZQTZLX == "3"))//国有划入 |
|
{ |
|
dicArea[BGH1Type][4] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else if (BGHQSXZ == "国有" && (XZQTZLX == "2" || XZQTZLX == "4")) |
|
{ |
|
dicArea[BGH1Type][6] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else if (BGHQSXZ == "集体" && (XZQTZLX == "1" || XZQTZLX == "3"))//集体划入 |
|
{ |
|
dicArea[BGH1Type][5] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else if (BGHQSXZ == "集体" && (XZQTZLX == "2" || XZQTZLX == "4"))//集体划出 |
|
{ |
|
dicArea[BGH1Type][7] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
} |
|
} |
|
|
|
} |
|
|
|
|
|
double[] dataArr = null; |
|
double[] totalArr = new double[] { 0, 0, 0, 0, 0, 0, 0, 0 }; |
|
|
|
for (int i = 0; i < dicArea.Count; i++) |
|
{ |
|
dataRange = sheet.Cells.CreateRange(5, 5 + i * 3, 6, 2); |
|
dataArr = dicArea.ElementAt(i).Value; |
|
|
|
// 国有增加 |
|
dataRange[4, 0].Value = dataArr[0]; |
|
totalArr[0] += dataArr[0]; |
|
|
|
// 集体增加 |
|
dataRange[4, 1].Value = dataArr[1]; |
|
totalArr[1] += dataArr[1]; |
|
|
|
// 增加小计 |
|
sheet.Cells[9, 4 + i * 3].Value = dataArr[0] + dataArr[1]; |
|
|
|
// 国有减少 |
|
dataRange[5, 0].Value = dataArr[2]; |
|
totalArr[2] += dataArr[2]; |
|
|
|
// 集体减少 |
|
dataRange[5, 1].Value = dataArr[3]; |
|
totalArr[3] += dataArr[3]; |
|
|
|
// 增加小计 |
|
sheet.Cells[10, 4 + i * 3].Value = dataArr[2] + dataArr[3]; |
|
|
|
//国有划入 |
|
dataRange[1, 0].Value = dataArr[4]; |
|
totalArr[4] += dataArr[4]; |
|
//集体划入 |
|
dataRange[1, 1].Value = dataArr[5]; |
|
totalArr[5] += dataArr[5]; |
|
// 划入小计 |
|
sheet.Cells[6, 4 + i * 3].Value = dataArr[4] + dataArr[5]; |
|
|
|
//国有划出 |
|
dataRange[2, 0].Value = dataArr[6]; |
|
totalArr[6] += dataArr[6]; |
|
//集体划出 |
|
dataRange[2, 1].Value = dataArr[7]; |
|
totalArr[7] += dataArr[7]; |
|
// 划出小计 |
|
sheet.Cells[7, 4 + i * 3].Value = dataArr[6] + dataArr[7]; |
|
//国有调整后年初面积 |
|
dataRange[3, 0].Value = Convert.ToDouble(dataRange[0, 0].Value) + dataRange[1, 0].DoubleValue - dataRange[2, 0].DoubleValue; |
|
//集体调整后年初面积 |
|
dataRange[3, 1].Value = Convert.ToDouble(dataRange[0, 1].Value) + dataRange[1, 1].DoubleValue - dataRange[2, 1].DoubleValue; |
|
//调整后年初面积小计 |
|
sheet.Cells[8, 4 + i * 3].Value = Convert.ToDouble(sheet.Cells[5, 4 + i * 3].Value) + sheet.Cells[6, 4 + i * 3].DoubleValue - sheet.Cells[7, 4 + i * 3].DoubleValue;//年初+划入-划出 |
|
} |
|
|
|
// 国有增加统计 |
|
sheet.Cells[9, 2].Value = totalArr[0]; |
|
// 集体增加统计 |
|
sheet.Cells[9, 3].Value = totalArr[1]; |
|
// 增加合计 |
|
sheet.Cells[9, 1].Value = totalArr[0] + totalArr[1]; |
|
// 国有减少统计 |
|
sheet.Cells[10, 2].Value = totalArr[2]; |
|
// 集体减少统计 |
|
sheet.Cells[10, 3].Value = totalArr[3]; |
|
// 减少合计 |
|
sheet.Cells[10, 1].Value = totalArr[2] + totalArr[3]; |
|
|
|
// 国有划入统计 |
|
sheet.Cells[6, 2].Value = totalArr[4]; |
|
// 集体划入统计 |
|
sheet.Cells[6, 3].Value = totalArr[5]; |
|
// 划入合计 |
|
sheet.Cells[6, 1].Value = totalArr[4] + totalArr[5]; |
|
|
|
// 国有划出统计 |
|
sheet.Cells[7, 2].Value = totalArr[6]; |
|
// 集体划出统计 |
|
sheet.Cells[7, 3].Value = totalArr[7]; |
|
// 划出合计 |
|
sheet.Cells[7, 1].Value = totalArr[6] + totalArr[7]; |
|
|
|
//调整后年初面积 |
|
sheet.Cells[8, 1].Value = Convert.ToDouble(sheet.Cells[5, 1].Value) + sheet.Cells[6, 1].DoubleValue - sheet.Cells[7, 1].DoubleValue;//年初+划入-划出 |
|
sheet.Cells[8, 2].Value = Convert.ToDouble(sheet.Cells[5, 2].Value) + sheet.Cells[6, 2].DoubleValue - sheet.Cells[7, 2].DoubleValue;//年初+划入-划出 |
|
sheet.Cells[8, 3].Value = Convert.ToDouble(sheet.Cells[5, 3].Value) + sheet.Cells[6, 3].DoubleValue - sheet.Cells[7, 3].DoubleValue;//年初+划入-划出 |
|
#endregion |
|
|
|
#region 年末面积操作 |
|
// 年末面积操作 |
|
dataRange = sheet.Cells.CreateRange("B6:AQ12"); |
|
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 CheckNegative(string baseReportPath, IRDBHelper dbHelper, bool isTZMJPC, ref List<BBPCModel> TZNCbBPCModels) |
|
{ |
|
try |
|
{ |
|
BBPCModel result = null; |
|
BBPCModel TZresult = null; |
|
string sourceFile = string.Empty; |
|
DirectoryInfo dir = new DirectoryInfo(baseReportPath); |
|
//FileInfo[] fileSet = dir.GetFiles(Path.GetFileName("*土地利用现状一级分类面积按权属性质汇总表.xlsx"), SearchOption.AllDirectories); |
|
FileInfo[] fileSet = dir.GetFiles(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(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("年初基础表不存在,请重试!"); |
|
//LogAPI.Debug("土地利用现状一级分类面积按权属性质汇总表 不能被 成功 找到\r\n"); |
|
return result; |
|
} |
|
//LogAPI.Debug("土地利用现状一级分类面积按权属性质汇总表 能被 成功 找到\r\n"); |
|
|
|
// 获取基础统计表 |
|
DataTable dt = dbHelper.ExecuteDatatable2("TB", "SELECT * FROM JCTJB_GQ", true); |
|
// 地类字典 |
|
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 + "表 3-土地利用现状一级分类面积按权属性质变化统计表.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 + 1; |
|
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 + ":AR" + valueRow); |
|
Range dataRange = null; |
|
|
|
#region 年初面积操作 |
|
// 年初面积操作 |
|
dataRange = sheet.Cells.CreateRange("B6:AQ6"); |
|
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 增减面积操作 |
|
// 增减面积操作 |
|
// 这个字典值是容量是8的数组，第一个是国家增加，第二个是集体增加，第三个是国家减少，第四个集体减少,国有划入，集体划入，国有划出，集体划出 |
|
foreach (var pair in dic1) |
|
{ |
|
if (pair.Key == "20") continue; |
|
dicArea.Add(pair.Key, new double[] { 0, 0, 0, 0, 0, 0, 0, 0 }); |
|
} |
|
|
|
string BGQ1Type = string.Empty; |
|
string BGH1Type = string.Empty; |
|
string BGQQSXZ = string.Empty; |
|
string BGHQSXZ = string.Empty; |
|
string XZQTZLX = string.Empty; |
|
|
|
foreach (DataRow row in dt.Rows) |
|
{ |
|
if (!dic2to1.Keys.Contains(row["BGQDLBM"].ToString())) |
|
{ |
|
continue; |
|
} |
|
if (!dic2to1.Keys.Contains(row["BGHDLBM"].ToString())) |
|
{ |
|
continue; |
|
} |
|
BGQ1Type = dic2to1[row["BGQDLBM"].ToString()]; |
|
BGH1Type = dic2to1[row["BGHDLBM"].ToString()]; |
|
BGQQSXZ = row["BGQQSXZ"].ToString(); |
|
BGHQSXZ = row["BGHQSXZ"].ToString(); |
|
XZQTZLX = row["XZQTZLX"].ToString(); |
|
if (BGQQSXZ != BGHQSXZ) |
|
{ |
|
if (BGQQSXZ == "国有") |
|
{ |
|
dicArea[BGQ1Type][2] += double.Parse(row["BGMJ"].ToString());//国有减少 |
|
} |
|
else |
|
{ |
|
dicArea[BGQ1Type][3] += double.Parse(row["BGMJ"].ToString());//集体减少 |
|
} |
|
if (BGHQSXZ == "国有") |
|
{ |
|
dicArea[BGH1Type][0] += double.Parse(row["BGMJ"].ToString());//国有增加 |
|
} |
|
else |
|
{ |
|
dicArea[BGH1Type][1] += double.Parse(row["BGMJ"].ToString());//集体增加 |
|
} |
|
|
|
|
|
if (BGHQSXZ == "国有" && (XZQTZLX == "1" || XZQTZLX == "3"))//国有划入 |
|
{ |
|
dicArea[BGH1Type][4] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else if (BGHQSXZ == "国有" && (XZQTZLX == "2" || XZQTZLX == "4")) |
|
{ |
|
dicArea[BGH1Type][6] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else if (BGHQSXZ == "集体" && (XZQTZLX == "1" || XZQTZLX == "3"))//集体划入 |
|
{ |
|
dicArea[BGH1Type][5] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else if (BGHQSXZ == "集体" && (XZQTZLX == "2" || XZQTZLX == "4"))//集体划出 |
|
{ |
|
dicArea[BGH1Type][7] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
} |
|
else |
|
{ |
|
if (BGQ1Type != BGH1Type) |
|
{ |
|
if (BGQQSXZ == "国有") |
|
{ |
|
dicArea[BGQ1Type][2] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else |
|
{ |
|
dicArea[BGQ1Type][3] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
if (BGHQSXZ == "国有") |
|
{ |
|
dicArea[BGH1Type][0] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else |
|
{ |
|
dicArea[BGH1Type][1] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
|
|
|
|
if (BGHQSXZ == "国有" && (XZQTZLX == "1" || XZQTZLX == "3"))//国有划入 |
|
{ |
|
dicArea[BGH1Type][4] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else if (BGHQSXZ == "国有" && (XZQTZLX == "2" || XZQTZLX == "4")) |
|
{ |
|
dicArea[BGH1Type][6] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else if (BGHQSXZ == "集体" && (XZQTZLX == "1" || XZQTZLX == "3"))//集体划入 |
|
{ |
|
dicArea[BGH1Type][5] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
else if (BGHQSXZ == "集体" && (XZQTZLX == "2" || XZQTZLX == "4"))//集体划出 |
|
{ |
|
dicArea[BGH1Type][7] += double.Parse(row["BGMJ"].ToString()); |
|
} |
|
} |
|
} |
|
|
|
} |
|
|
|
|
|
double[] dataArr = null; |
|
double[] totalArr = new double[] { 0, 0, 0, 0, 0, 0, 0, 0 }; |
|
|
|
for (int i = 0; i < dicArea.Count; i++) |
|
{ |
|
dataRange = sheet.Cells.CreateRange(5, 5 + i * 3, 6, 2); |
|
dataArr = dicArea.ElementAt(i).Value; |
|
|
|
// 国有增加 |
|
dataRange[4, 0].Value = dataArr[0]; |
|
totalArr[0] += dataArr[0]; |
|
|
|
// 集体增加 |
|
dataRange[4, 1].Value = dataArr[1]; |
|
totalArr[1] += dataArr[1]; |
|
|
|
// 增加小计 |
|
sheet.Cells[9, 4 + i * 3].Value = dataArr[0] + dataArr[1]; |
|
|
|
// 国有减少 |
|
dataRange[5, 0].Value = dataArr[2]; |
|
totalArr[2] += dataArr[2]; |
|
|
|
// 集体减少 |
|
dataRange[5, 1].Value = dataArr[3]; |
|
totalArr[3] += dataArr[3]; |
|
|
|
// 增加小计 |
|
sheet.Cells[10, 4 + i * 3].Value = dataArr[2] + dataArr[3]; |
|
|
|
//国有划入 |
|
dataRange[1, 0].Value = dataArr[4]; |
|
totalArr[4] += dataArr[4]; |
|
//集体划入 |
|
dataRange[1, 1].Value = dataArr[5]; |
|
totalArr[5] += dataArr[5]; |
|
// 划入小计 |
|
sheet.Cells[6, 4 + i * 3].Value = dataArr[4] + dataArr[5]; |
|
|
|
//国有划出 |
|
dataRange[2, 0].Value = dataArr[6]; |
|
totalArr[6] += dataArr[6]; |
|
//集体划出 |
|
dataRange[2, 1].Value = dataArr[7]; |
|
totalArr[7] += dataArr[7]; |
|
// 划出小计 |
|
sheet.Cells[7, 4 + i * 3].Value = dataArr[6] + dataArr[7]; |
|
//国有调整后年初面积 |
|
dataRange[3, 0].Value = Convert.ToDouble(dataRange[0, 0].Value) + dataRange[1, 0].DoubleValue - dataRange[2, 0].DoubleValue; |
|
//集体调整后年初面积 |
|
dataRange[3, 1].Value = Convert.ToDouble(dataRange[0, 1].Value) + dataRange[1, 1].DoubleValue - dataRange[2, 1].DoubleValue; |
|
//调整后年初面积小计 |
|
sheet.Cells[8, 4 + i * 3].Value = Convert.ToDouble(sheet.Cells[5, 4 + i * 3].Value) + sheet.Cells[6, 4 + i * 3].DoubleValue - sheet.Cells[7, 4 + i * 3].DoubleValue;//年初+划入-划出 |
|
#region 调整面积负值 |
|
//国有调整后年初面积负值 |
|
if (isTZMJPC && dataRange[3, 0].DoubleValue < 0) |
|
{ |
|
if (TZresult == null) |
|
{ |
|
TZresult = new BBPCModel(); |
|
TZresult.ReportName = 2; |
|
TZresult.Columns = new List<PCColumnModel>(); |
|
} |
|
PCColumnModel coluModel = new PCColumnModel(); |
|
coluModel.Ids = new List<int>(); |
|
coluModel.IsActive = true; |
|
coluModel.ColumnIndex = 5 + i * 3; |
|
coluModel.DiffValue = Math.Abs(dataRange[3, 0].DoubleValue); |
|
|
|
string dl = dicArea.ElementAt(i).Key; |
|
string qs = "国有"; |
|
string r = dl + "-" + qs; |
|
Dictionary<string, string> dlconvert = LandTypeDicHelper.GetLevel2ToLevel1LandTypeDic(); |
|
string dlWhere = string.Empty; |
|
foreach (var item in dlconvert) |
|
{ |
|
if (item.Value.Equals(dl)) |
|
{ |
|
dlWhere += "'" + item.Key + "',"; |
|
} |
|
} |
|
dlWhere = dlWhere.TrimEnd(','); |
|
string strSql = string.Format("select ID from JCTJB_GQ where BGQDLBM in ({0}) and BGQQSXZ='{1}' and XZQTZLX in ('2','4')", dlWhere, qs); |
|
DataTable IDsTable = dbHelper.ExecuteDatatable("ids", strSql, 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); |
|
} |
|
//集体调整后年初面积负值 |
|
if (isTZMJPC && dataRange[3, 1].DoubleValue < 0) |
|
{ |
|
if (TZresult == null) |
|
{ |
|
TZresult = new BBPCModel(); |
|
TZresult.ReportName = 2; |
|
TZresult.Columns = new List<PCColumnModel>(); |
|
} |
|
PCColumnModel coluModel = new PCColumnModel(); |
|
coluModel.Ids = new List<int>(); |
|
coluModel.IsActive = true; |
|
coluModel.ColumnIndex = 5 + i * 3 + 1; |
|
coluModel.DiffValue = Math.Abs(dataRange[3, 0].DoubleValue); |
|
|
|
string dl = dicArea.ElementAt(i).Key; |
|
string qs = "集体"; |
|
string r = dl + "-" + qs; |
|
Dictionary<string, string> dlconvert = LandTypeDicHelper.GetLevel2ToLevel1LandTypeDic(); |
|
string dlWhere = string.Empty; |
|
foreach (var item in dlconvert) |
|
{ |
|
if (item.Value.Equals(dl)) |
|
{ |
|
dlWhere += "'" + item.Key + "',"; |
|
} |
|
} |
|
dlWhere = dlWhere.TrimEnd(','); |
|
string strSql = string.Format("select ID from JCTJB_GQ where BGQDLBM in ({0}) and BGQQSXZ='{1}' and XZQTZLX in ('2','4')", dlWhere, qs); |
|
DataTable IDsTable = dbHelper.ExecuteDatatable("ids", strSql, 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 |
|
} |
|
|
|
// 国有增加统计 |
|
sheet.Cells[9, 2].Value = totalArr[0]; |
|
// 集体增加统计 |
|
sheet.Cells[9, 3].Value = totalArr[1]; |
|
// 增加合计 |
|
sheet.Cells[9, 1].Value = totalArr[0] + totalArr[1]; |
|
// 国有减少统计 |
|
sheet.Cells[10, 2].Value = totalArr[2]; |
|
// 集体减少统计 |
|
sheet.Cells[10, 3].Value = totalArr[3]; |
|
// 减少合计 |
|
sheet.Cells[10, 1].Value = totalArr[2] + totalArr[3]; |
|
|
|
// 国有划入统计 |
|
sheet.Cells[6, 2].Value = totalArr[4]; |
|
// 集体划入统计 |
|
sheet.Cells[6, 3].Value = totalArr[5]; |
|
// 划入合计 |
|
sheet.Cells[6, 1].Value = totalArr[4] + totalArr[5]; |
|
|
|
// 国有划出统计 |
|
sheet.Cells[7, 2].Value = totalArr[6]; |
|
// 集体划出统计 |
|
sheet.Cells[7, 3].Value = totalArr[7]; |
|
// 划出合计 |
|
sheet.Cells[7, 1].Value = totalArr[6] + totalArr[7]; |
|
|
|
//调整后年初面积 |
|
sheet.Cells[8, 1].Value = Convert.ToDouble(sheet.Cells[5, 1].Value) + sheet.Cells[6, 1].DoubleValue - sheet.Cells[7, 1].DoubleValue;//年初+划入-划出 |
|
sheet.Cells[8, 2].Value = Convert.ToDouble(sheet.Cells[5, 2].Value) + sheet.Cells[6, 2].DoubleValue - sheet.Cells[7, 2].DoubleValue;//年初+划入-划出 |
|
sheet.Cells[8, 3].Value = Convert.ToDouble(sheet.Cells[5, 3].Value) + sheet.Cells[6, 3].DoubleValue - sheet.Cells[7, 3].DoubleValue;//年初+划入-划出 |
|
#endregion |
|
|
|
#region 年末面积操作 |
|
// 年末面积操作 |
|
dataRange = sheet.Cells.CreateRange("B6:AQ12"); |
|
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 = 2; |
|
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); |
|
|
|
decimal n = Math.Floor((i / 3).ToDecimal()); |
|
string dl = (n - 1).ToString("00"); |
|
string qs = ""; |
|
if (i % 3 == 1) |
|
{ |
|
qs = "国有"; |
|
} |
|
else if (i % 3 == 2) |
|
{ |
|
qs = "集体"; |
|
} |
|
string r = dl + "-" + qs; |
|
Dictionary<string, string> dlconvert = LandTypeDicHelper.GetLevel2ToLevel1LandTypeDic(); |
|
string dlWhere = string.Empty; |
|
foreach (var item in dlconvert) |
|
{ |
|
if (item.Value.Equals(dl)) |
|
{ |
|
dlWhere += "'" + item.Key + "',"; |
|
} |
|
} |
|
dlWhere = dlWhere.TrimEnd(','); |
|
string strSql = string.Format("select ID from JCTJB_GQ where BGQDLBM in ({0}) and BGQQSXZ='{1}' and (BGHDLBM not in ({0})or BGHQSXZ<>'{1}') and XZQTZLX='0'", dlWhere, qs); |
|
DataTable IDsTable = dbHelper.ExecuteDatatable("ids", strSql, 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 |
|
{ |
|
|
|
} |
|
} |
|
|
|
/// <summary> |
|
/// 执行导出 |
|
/// </summary> |
|
/// <param name="savePath">保存位置</param> |
|
/// <param name="baseReportPath">获取年初表位置</param> |
|
/// <param name="dbHelper">数据库链接</param> |
|
/// <returns>是否成功</returns> |
|
public bool Export_SQL_1(string savePath, string baseReportPath, IRDBHelper dbHelper) |
|
{ |
|
string ncsjExcelPath = string.Empty; |
|
try |
|
{ |
|
if (string.IsNullOrWhiteSpace(savePath)) |
|
{ |
|
MessageHelper.ShowError("存储位置错误请重试!"); |
|
return false; |
|
} |
|
string sourceFile = string.Empty; |
|
string fileName = string.Empty; |
|
DirectoryInfo dir = new DirectoryInfo(baseReportPath); |
|
#region 数据赋值 |
|
|
|
FileInfo[] fileSet = dir.GetFiles("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")土地利用现状一级分类面积按权属性质*", SearchOption.AllDirectories); |
|
if (fileSet != null && fileSet.Length > 0) |
|
{ |
|
ncsjExcelPath = fileSet[0].FullName; |
|
fileName = fileSet[0].Name.Replace("(", "").Replace(")", "").Replace((MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, "").Replace(fileSet[0].Extension, ""); |
|
} |
|
//else |
|
//{ |
|
// fileSet = dir.GetFiles("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")土地利用现状一级分类面积按权属性质汇总表.xls", SearchOption.AllDirectories); |
|
// if (fileSet != null && fileSet.Length > 0) |
|
// { |
|
// ncsjExcelPath = fileSet[0].FullName; |
|
// } |
|
//} |
|
if (String.IsNullOrWhiteSpace(ncsjExcelPath) == true) |
|
{ |
|
return false; |
|
} |
|
string tempPath = AppDomain.CurrentDomain.BaseDirectory + "TempalateReports\\BGTJ\\土地利用现状一级分类面积按权属性质变化统计表.xlsx"; |
|
File.Copy(tempPath, savePath, true); |
|
DataTable resultdataTable = new DataTable(); |
|
Dictionary<string, string> keyValuePairs = new Dictionary<string, string>(); |
|
keyValuePairs.Add("5,6", "'0303','0304','0306','0402','1105','1106','1108'"); |
|
keyValuePairs.Add("8,9", "'0101','0102','0103'"); |
|
keyValuePairs.Add("11,12", "'0201','0202','0203','0204'"); |
|
keyValuePairs.Add("14,15", "'0301','0302','0305','0307'"); |
|
keyValuePairs.Add("17,18", "'0401','0403','0404'"); |
|
keyValuePairs.Add("20,21", "'201','202','203','204','205'"); |
|
keyValuePairs.Add("23,24", "'1001','1002','1003','1007','1008','1009'"); |
|
keyValuePairs.Add("26,27", "'1109'"); |
|
keyValuePairs.Add("29,30", "'1101','1102','1103','1104','1107','1110'"); |
|
keyValuePairs.Add("32,33", "'1006','1202','1203','1204','1205','1206','1207'"); |
|
StringBuilder stringBuilder = new StringBuilder(); |
|
foreach (var item in keyValuePairs) |
|
{ |
|
var Cells_Y = item.Key.Split(','); |
|
stringBuilder.Append($@" |
|
union all |
|
select '9' Cells_X,case when BGHQSXZ = '国有' then '{Cells_Y[0]}' ELSE '{Cells_Y[1]}' end Cells_Y ,BGMJ from |
|
( |
|
select bgh,SUM(BGMJ) BGMJ,BGHQSXZ from |
|
( |
|
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQQSXZ, BGHQSXZ from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, XZQTZLX, |
|
BGQQSXZ,BGHQSXZ |
|
from JCTJB_GQ |
|
)A WHERE bgh in ({item.Value}) and (BGQQSXZ<>BGHQSXZ or bgq not in ({item.Value})) and XZQTZLX<>2 and XZQTZLX<>3 |
|
)A GROUP BY BGHQSXZ |
|
)A |
|
union ALL |
|
select '10' Cells_X,case when BGQQSXZ = '国有' then '{Cells_Y[0]}' ELSE '{Cells_Y[1]}' end Cells_Y, BGMJ from |
|
( |
|
select SUM(BGMJ) BGMJ,BGQQSXZ from |
|
( |
|
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQQSXZ, BGHQSXZ from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ,XZQTZLX , |
|
BGQQSXZ, BGHQSXZ |
|
from JCTJB_GQ |
|
)A WHERE bgq in ({item.Value}) and (BGQQSXZ<>BGHQSXZ or bgh not in ({item.Value}))and XZQTZLX<>2 and XZQTZLX<>3 |
|
)A GROUP BY BGQQSXZ |
|
)A |
|
"); |
|
} |
|
DataTable dt = dbHelper.ExecuteDatatable("TDLYXZBGB", stringBuilder.ToString().Substring(13), true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
#endregion |
|
|
|
#region 上年末面积 |
|
DataTable table = dbHelper.ExecuteDatatable("tab", string.Format(@"select case WHEN column1='{0}' THEN'column1' ELSE 'column2' END ColumnName from {1} WHERE column1='{0}' or column2='{0}' ", (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, fileName), true); |
|
if (table == null || table.Rows.Count == 0) |
|
{ |
|
return false; |
|
} |
|
string ColumnName = table.Rows[0][0].ToTrim(); |
|
if (ColumnName == "column1") |
|
{ |
|
dt = dbHelper.ExecuteDatatable("SNMMJ", string.Format(@" |
|
select '5' Cells_X ,'1'Cells_Y, round(column2,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'2'Cells_Y, round(column3,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'3'Cells_Y, round(column4,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'4'Cells_Y, round(column5,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'5'Cells_Y, round(column6,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'6'Cells_Y, round(column7,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'7'Cells_Y, round(column8,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'8'Cells_Y, round(column9,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'9'Cells_Y, round(column10,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'10'Cells_Y, round(column11,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'11'Cells_Y, round(column12,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'12'Cells_Y, round(column13,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'13'Cells_Y, round(column14,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'14'Cells_Y, round(column15,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'15'Cells_Y, round(column16,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'16'Cells_Y, round(column17,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'17'Cells_Y, round(column18,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'18'Cells_Y, round(column19,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '5' Cells_X ,'19'Cells_Y, round(column20,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '5' Cells_X ,'20'Cells_Y, round(column21,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '5' Cells_X ,'21'Cells_Y, round(column22,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '5' Cells_X ,'22'Cells_Y, round(column23,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '5' Cells_X ,'23'Cells_Y, round(column24,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'24'Cells_Y, round(column25,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'25'Cells_Y, round(column26,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'26'Cells_Y, round(column27,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'27'Cells_Y, round(column28,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'28'Cells_Y, round(column29,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'29'Cells_Y, round(column30,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'30'Cells_Y, round(column31,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'31'Cells_Y, round(column32,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'32'Cells_Y, round(column33,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'33'Cells_Y, round(column34,4) BGMJ FROM {2} WHERE {1}='{0}' |
|
", (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, ColumnName, fileName), true); |
|
} |
|
else if (ColumnName == "column2") |
|
{ |
|
dt = dbHelper.ExecuteDatatable("SNMMJ", string.Format(@" |
|
select '5' Cells_X ,'1'Cells_Y, round(column3,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'2'Cells_Y, round(column4,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'3'Cells_Y, round(column5,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'4'Cells_Y, round(column6,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'5'Cells_Y, round(column7,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'6'Cells_Y, round(column8,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'7'Cells_Y, round(column9,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'8'Cells_Y, round(column10,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'9'Cells_Y, round(column11,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'10'Cells_Y, round(column12,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'11'Cells_Y, round(column13,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'12'Cells_Y, round(column14,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'13'Cells_Y, round(column15,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'14'Cells_Y, round(column16,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'15'Cells_Y, round(column17,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'16'Cells_Y, round(column18,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'17'Cells_Y, round(column19,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'18'Cells_Y, round(column20,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '5' Cells_X ,'19'Cells_Y, round(column21,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '5' Cells_X ,'20'Cells_Y, round(column22,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '5' Cells_X ,'21'Cells_Y, round(column23,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '5' Cells_X ,'22'Cells_Y, round(column24,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '5' Cells_X ,'23'Cells_Y, round(column25,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'24'Cells_Y, round(column26,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'25'Cells_Y, round(column27,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'26'Cells_Y, round(column28,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'27'Cells_Y, round(column29,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'28'Cells_Y, round(column30,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'29'Cells_Y, round(column31,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'30'Cells_Y, round(column32,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'31'Cells_Y, round(column33,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'32'Cells_Y, round(column34,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '5' Cells_X ,'33'Cells_Y, round(column35,4) BGMJ FROM {2} WHERE {1}='{0}' |
|
", (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, ColumnName, fileName), true); |
|
} |
|
|
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
#endregion |
|
|
|
#region 行政区调入 |
|
stringBuilder.Clear(); |
|
foreach (var item in keyValuePairs) |
|
{ |
|
var Cells_Y = item.Key.Split(','); |
|
stringBuilder.Append($@" union all |
|
select '6' Cells_X,case when QSXZ = '国有' then '{Cells_Y[0]}' ELSE '{Cells_Y[1]}' end Cells_Y ,BGMJ from |
|
( |
|
select dlbm,SUM(BGMJ) BGMJ,QSXZ from |
|
( |
|
select case when XZQTZLX='1' THEN bgq else bgh END dlbm,BGMJ,case when XZQTZLX='1' THEN BGQQSXZ else BGHQSXZ END QSXZ |
|
from ( |
|
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQQSXZ, BGHQSXZ,XZQTZLX from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, XZQTZLX, BGQQSXZ,BGHQSXZ from JCTJB_GQ WHERE XZQTZLX in ('1','3') |
|
)A where XZQTZLX in ('1','3') |
|
)A )A WHERE dlbm in ({item.Value}) GROUP BY QSXZ |
|
)A "); |
|
} |
|
dt = dbHelper.ExecuteDatatable("XZQDR", stringBuilder.ToString().Substring(13), true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
#endregion |
|
|
|
#region 行政区调出 |
|
stringBuilder.Clear(); |
|
foreach (var item in keyValuePairs) |
|
{ |
|
var Cells_Y = item.Key.Split(','); |
|
stringBuilder.Append($@" |
|
union ALL |
|
select '7' Cells_X,case when BGQQSXZ = '国有' then '{Cells_Y[0]}' ELSE '{Cells_Y[1]}' end Cells_Y, BGMJ from |
|
( |
|
select SUM(BGMJ) BGMJ,BGQQSXZ from |
|
( |
|
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQQSXZ, BGHQSXZ,XZQTZLX from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ,XZQTZLX , BGQQSXZ, BGHQSXZ from JCTJB_GQ WHERE XZQTZLX in ('2','4') |
|
)A WHERE bgq in ({item.Value}) |
|
)A GROUP BY BGQQSXZ |
|
)A |
|
"); |
|
} |
|
dt = dbHelper.ExecuteDatatable("XZQDC", stringBuilder.ToString().Substring(13), true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
CellHelper.SetFileToDisk(resultdataTable, savePath, 0); |
|
#endregion |
|
CellHelper.Import(savePath); |
|
return true; |
|
} |
|
catch (Exception e) |
|
{ |
|
throw e; |
|
} |
|
finally |
|
{ |
|
|
|
} |
|
} |
|
|
|
|
|
public List<Dictionary<string, string>> GetResults_Excel(string sTableName) |
|
{ |
|
List<Dictionary<string, string>> result = new List<Dictionary<string, string>>(); |
|
try |
|
{ |
|
XmlDocument doc = new XmlDocument(); |
|
string strPath = SysAppPath.GetCurrentAppPath(); |
|
strPath += @"Configs\Results_Excel.xml"; |
|
doc.Load(strPath); |
|
XmlNode nodeDataCatalog = doc.SelectSingleNode("TableName"); |
|
if (nodeDataCatalog == null) |
|
return result; |
|
XmlNode cnodeDataCatalog = nodeDataCatalog.SelectSingleNode(sTableName); |
|
foreach (XmlNode item in cnodeDataCatalog.ChildNodes) |
|
{ |
|
Dictionary<string, string> dic = new Dictionary<string, string>(); |
|
foreach (XmlNode citem in item.ChildNodes) |
|
{ |
|
dic.Add(citem.Attributes["DLBM"].Value, citem.Attributes["Cells"].Value); |
|
} |
|
result.Add(dic); |
|
} |
|
return result; |
|
} |
|
catch (Exception ex) |
|
{ |
|
LogAPI.Debug(ex); |
|
throw; |
|
} |
|
} |
|
|
|
/// <summary> |
|
/// 执行导出 |
|
/// </summary> |
|
/// <param name="savePath">保存位置</param> |
|
/// <param name="baseReportPath">获取年初表位置</param> |
|
/// <param name="dbHelper">数据库链接</param> |
|
/// <returns>是否成功</returns> |
|
public bool Export_SQL_2(string savePath, string baseReportPath, IRDBHelper dbHelper) |
|
{ |
|
string ncsjExcelPath = string.Empty; |
|
try |
|
{ |
|
if (string.IsNullOrWhiteSpace(savePath)) |
|
{ |
|
MessageHelper.ShowError("存储位置错误请重试!"); |
|
return false; |
|
} |
|
string sourceFile = string.Empty; |
|
DirectoryInfo dir = new DirectoryInfo(baseReportPath); |
|
#region 数据赋值 |
|
string fileName = string.Empty; |
|
FileInfo[] fileSet = dir.GetFiles("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")土地利用现状二级分类面积按权属性质*", SearchOption.AllDirectories); |
|
if (fileSet != null && fileSet.Length > 0) |
|
{ |
|
ncsjExcelPath = fileSet[0].FullName; |
|
fileName = fileSet[0].Name.Replace("(", "").Replace(")", "").Replace((MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, "").Replace(fileSet[0].Extension, ""); |
|
} |
|
//else |
|
//{ |
|
// fileSet = dir.GetFiles("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")土地利用现状二级分类面积按权属性质汇总表.xls", SearchOption.AllDirectories); |
|
// if (fileSet != null && fileSet.Length > 0) |
|
// { |
|
// ncsjExcelPath = fileSet[0].FullName; |
|
// } |
|
//} |
|
if (String.IsNullOrWhiteSpace(ncsjExcelPath) == true) |
|
{ |
|
return false; |
|
} |
|
string tempPath = AppDomain.CurrentDomain.BaseDirectory + "TempalateReports\\BGTJ\\土地利用现状二级分类面积按权属性质变化统计表.xlsx"; |
|
File.Copy(tempPath, savePath, true); |
|
DataTable resultdataTable = new DataTable(); |
|
DataTable dt = dbHelper.ExecuteDatatable("TDLYXZBGB", @" select Cells_X,Cells_Y,SUM(BGMJ) BGMJ from |
|
( |
|
select Cells_X,Cells_Y,BGMJ from |
|
( |
|
select bgh||BGHQSXZ DLBM,sum(BGMJ)BGMJ |
|
from |
|
( |
|
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,BGHQSXZ from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX , |
|
BGQQSXZ, BGHQSXZ |
|
from JCTJB_GQ where XZQTZLX<>'2' and XZQTZLX<>3 |
|
)A WHERE bgq<>bgh or BGQQSXZ<>BGHQSXZ |
|
)A GROUP BY bgh ,BGHQSXZ |
|
)A left join TDLYXZYJLMJAQSBHTJB_2 B ON a.DLBM=b.bgh where Cells_X=9 |
|
)a GROUP BY Cells_X,Cells_Y |
|
union ALL |
|
select Cells_X,Cells_Y,SUM(BGMJ) BGMJ from |
|
( |
|
select Cells_X,Cells_Y,BGMJ from |
|
( |
|
select bgq||BGQQSXZ DLBM,sum(BGMJ)BGMJ |
|
from |
|
( |
|
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,BGQQSXZ from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX , |
|
BGQQSXZ, BGHQSXZ |
|
from JCTJB_GQ where XZQTZLX<>'2' and XZQTZLX<>3 |
|
)A WHERE bgq<>bgh or BGQQSXZ<>BGHQSXZ |
|
)A GROUP BY bgq,BGQQSXZ |
|
)A left join TDLYXZYJLMJAQSBHTJB_2 B ON a.DLBM=b.bgh where Cells_X=10 |
|
)a GROUP BY Cells_X,Cells_Y ", true); |
|
//CellHelper.SetFileToDisk(dt, savePath, 0); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
#endregion |
|
|
|
Dictionary<string, string> keyValuePairs = new Dictionary<string, string>(); |
|
keyValuePairs.Add("5,6", "'0303','0304','0306','0402','1105','1106','1108'"); |
|
keyValuePairs.Add("29,30", "'0101','0102','0103'"); |
|
keyValuePairs.Add("41,42", "'0201','0202','0203','0204'"); |
|
keyValuePairs.Add("56,57", "'0301','0302','0305','0307'"); |
|
keyValuePairs.Add("71,72", "'0401','0403','0404'"); |
|
keyValuePairs.Add("83,84", "'201','202','203','204','205'"); |
|
keyValuePairs.Add("101,102", "'1001','1002','1003','1007','1008','1009'"); |
|
keyValuePairs.Add("125,126", "'1101','1102','1103','1104','1107','1110'"); |
|
keyValuePairs.Add("146,147", "'1006','1202','1203','1204','1205','1206','1207'"); |
|
StringBuilder stringBuilder = new StringBuilder(); |
|
foreach (var item in keyValuePairs) |
|
{ |
|
var Cells_Y = item.Key.Split(','); |
|
stringBuilder.Append($@" |
|
union all |
|
select '9' Cells_X,case when BGHQSXZ = '国有' then '{Cells_Y[0]}' ELSE '{Cells_Y[1]}' end Cells_Y ,BGMJ from |
|
( |
|
select bgh,SUM(BGMJ) BGMJ,BGHQSXZ from |
|
( |
|
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQQSXZ, BGHQSXZ from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, XZQTZLX, |
|
BGQQSXZ, BGHQSXZ |
|
from JCTJB_GQ |
|
)A WHERE bgh in ({item.Value}) and (BGQQSXZ<>BGHQSXZ or bgq not in ({item.Value})) and xzqtzlx<>'2' and XZQTZLX<>3 |
|
)A GROUP BY BGHQSXZ |
|
)A |
|
union ALL |
|
select '10' Cells_X,case when BGQQSXZ = '国有' then '{Cells_Y[0]}' ELSE '{Cells_Y[1]}' end Cells_Y, BGMJ from |
|
( |
|
select SUM(BGMJ) BGMJ,BGQQSXZ from |
|
( |
|
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQQSXZ, BGHQSXZ from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ,XZQTZLX , |
|
BGQQSXZ, BGHQSXZ |
|
from JCTJB_GQ |
|
)A WHERE bgq in ({item.Value}) and (BGQQSXZ<>BGHQSXZ or bgh not in ({item.Value})) and xzqtzlx<>'2' and XZQTZLX<>3 |
|
)A GROUP BY BGQQSXZ |
|
)A |
|
"); |
|
} |
|
dt = dbHelper.ExecuteDatatable("TDLYXZBGB", stringBuilder.ToString().Substring(13), true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
#region 上年末面积 |
|
DataTable table = dbHelper.ExecuteDatatable("tab", string.Format(@"select case WHEN column1='{0}' THEN'column1' ELSE 'column2' END ColumnName from {1} WHERE column1='{0}' or column2='{0}' ", (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, fileName), true); |
|
if (table == null || table.Rows.Count == 0) |
|
{ |
|
return false; |
|
} |
|
string ColumnName = table.Rows[0][0].ToTrim(); |
|
if (ColumnName == "column1") |
|
{ |
|
dt = dbHelper.ExecuteDatatable("SNMMJ", string.Format(@" |
|
select '5' Cells_X, '1' Cells_Y, round(column2,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '2' Cells_Y, round(column3,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '3' Cells_Y, round(column4,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '4' Cells_Y, round(column5,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '5' Cells_Y, round(column6,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '6' Cells_Y, round(column7,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '7' Cells_Y, round(column8,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '8' Cells_Y, round(column9,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '9' Cells_Y, round(column10,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '10' Cells_Y, round(column11,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '11' Cells_Y, round(column12,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '12' Cells_Y, round(column13,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '13' Cells_Y, round(column14,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '14' Cells_Y, round(column15,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '15' Cells_Y, round(column16,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '16' Cells_Y, round(column17,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '17' Cells_Y, round(column18,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '18' Cells_Y, round(column19,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '19' Cells_Y, round(column20,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '20' Cells_Y, round(column21,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '21' Cells_Y, round(column22,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '22' Cells_Y, round(column23,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '23' Cells_Y, round(column24,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '24' Cells_Y, round(column25,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '25' Cells_Y, round(column26,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '26' Cells_Y, round(column27,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '27' Cells_Y, round(column28,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '28' Cells_Y, round(column29,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '29' Cells_Y, round(column30,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '30' Cells_Y, round(column31,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '31' Cells_Y, round(column32,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '32' Cells_Y, round(column33,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '33' Cells_Y, round(column34,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '34' Cells_Y, round(column35,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '35' Cells_Y, round(column36,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '36' Cells_Y, round(column37,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '37' Cells_Y, round(column38,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '38' Cells_Y, round(column39,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '39' Cells_Y, round(column40,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '40' Cells_Y, round(column41,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '41' Cells_Y, round(column42,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '42' Cells_Y, round(column43,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '43' Cells_Y, round(column44,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '44' Cells_Y, round(column45,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '45' Cells_Y, round(column46,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '46' Cells_Y, round(column47,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '47' Cells_Y, round(column48,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '48' Cells_Y, round(column49,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '49' Cells_Y, round(column50,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '50' Cells_Y, round(column51,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '51' Cells_Y, round(column52,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '52' Cells_Y, round(column53,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '53' Cells_Y, round(column54,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '54' Cells_Y, round(column55,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '55' Cells_Y, round(column56,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '56' Cells_Y, round(column57,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '57' Cells_Y, round(column58,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '58' Cells_Y, round(column59,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '59' Cells_Y, round(column60,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '60' Cells_Y, round(column61,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '61' Cells_Y, round(column62,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '62' Cells_Y, round(column63,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '63' Cells_Y, round(column64,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '64' Cells_Y, round(column65,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '65' Cells_Y, round(column66,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '66' Cells_Y, round(column67,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '67' Cells_Y, round(column68,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '68' Cells_Y, round(column69,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '69' Cells_Y, round(column70,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '70' Cells_Y, round(column71,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '71' Cells_Y, round(column72,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '72' Cells_Y, round(column73,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '73' Cells_Y, round(column74,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '74' Cells_Y, round(column75,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '75' Cells_Y, round(column76,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '76' Cells_Y, round(column77,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '77' Cells_Y, round(column78,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '78' Cells_Y, round(column79,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '79' Cells_Y, round(column80,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '80' Cells_Y, round(column81,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '81' Cells_Y, round(column82,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '82' Cells_Y, round(column83,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '83' Cells_Y, round(column84,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '84' Cells_Y, round(column85,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '85' Cells_Y, round(column86,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '86' Cells_Y, round(column87,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '87' Cells_Y, round(column88,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '88' Cells_Y, round(column89,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '89' Cells_Y, round(column90,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '90' Cells_Y, round(column91,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '91' Cells_Y, round(column92,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '92' Cells_Y, round(column93,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '93' Cells_Y, round(column94,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '94' Cells_Y, round(column95,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '95' Cells_Y, round(column96,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '96' Cells_Y, round(column97,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '97' Cells_Y, round(column98,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '98' Cells_Y, round(column99,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '99' Cells_Y, round(column100,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '100' Cells_Y, round(column101,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '101' Cells_Y, round(column102,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '102' Cells_Y, round(column103,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '103' Cells_Y, round(column104,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '104' Cells_Y, round(column105,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '105' Cells_Y, round(column106,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '106' Cells_Y, round(column107,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '107' Cells_Y, round(column108,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '108' Cells_Y, round(column109,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '109' Cells_Y, round(column110,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '110' Cells_Y, round(column111,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '111' Cells_Y, round(column112,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '112' Cells_Y, round(column113,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '113' Cells_Y, round(column114,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '114' Cells_Y, round(column115,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '115' Cells_Y, round(column116,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '116' Cells_Y, round(column117,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '117' Cells_Y, round(column118,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '118' Cells_Y, round(column119,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '119' Cells_Y, round(column120,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '120' Cells_Y, round(column121,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '121' Cells_Y, round(column122,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '122' Cells_Y, round(column123,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '123' Cells_Y, round(column124,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '124' Cells_Y, round(column125,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '125' Cells_Y, round(column126,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '126' Cells_Y, round(column127,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '127' Cells_Y, round(column128,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '128' Cells_Y, round(column129,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '129' Cells_Y, round(column130,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '130' Cells_Y, round(column131,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '131' Cells_Y, round(column132,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '132' Cells_Y, round(column133,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '133' Cells_Y, round(column134,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '134' Cells_Y, round(column135,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '135' Cells_Y, round(column136,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '136' Cells_Y, round(column137,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '137' Cells_Y, round(column138,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '138' Cells_Y, round(column139,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '139' Cells_Y, round(column140,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '140' Cells_Y, round(column141,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '141' Cells_Y, round(column142,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '142' Cells_Y, round(column143,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '143' Cells_Y, round(column144,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '144' Cells_Y, round(column145,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '145' Cells_Y, round(column146,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '146' Cells_Y, round(column147,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '147' Cells_Y, round(column148,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '148' Cells_Y, round(column149,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '149' Cells_Y, round(column150,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '150' Cells_Y, round(column151,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '151' Cells_Y, round(column152,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '152' Cells_Y, round(column153,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '153' Cells_Y, round(column154,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '154' Cells_Y, round(column155,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '155' Cells_Y, round(column156,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '156' Cells_Y, round(column157,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '157' Cells_Y, round(column158,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '158' Cells_Y, round(column159,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '159' Cells_Y, round(column160,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '160' Cells_Y, round(column161,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '161' Cells_Y, round(column162,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '162' Cells_Y, round(column163,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '163' Cells_Y, round(column164,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '164' Cells_Y, round(column165,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '165' Cells_Y, round(column166,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '166' Cells_Y, round(column167,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '167' Cells_Y, round(column168,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '168' Cells_Y, round(column169,4) BGMJ from {2} WHERE {1}='{0}' ", (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, ColumnName, fileName), true); |
|
} |
|
else if (ColumnName == "column2") |
|
{ |
|
dt = dbHelper.ExecuteDatatable("SNMMJ", string.Format(@" |
|
select '5' Cells_X, '1' Cells_Y, round(column3,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '2' Cells_Y, round(column4,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '3' Cells_Y, round(column5,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '4' Cells_Y, round(column6,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '5' Cells_Y, round(column7,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '6' Cells_Y, round(column8,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '7' Cells_Y, round(column9,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '8' Cells_Y, round(column10,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '9' Cells_Y, round(column11,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '10' Cells_Y, round(column12,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '11' Cells_Y, round(column13,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '12' Cells_Y, round(column14,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '13' Cells_Y, round(column15,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '14' Cells_Y, round(column16,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '15' Cells_Y, round(column17,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '16' Cells_Y, round(column18,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '17' Cells_Y, round(column19,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '18' Cells_Y, round(column20,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '19' Cells_Y, round(column21,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '20' Cells_Y, round(column22,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '21' Cells_Y, round(column23,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '22' Cells_Y, round(column24,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '23' Cells_Y, round(column25,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '24' Cells_Y, round(column26,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '25' Cells_Y, round(column27,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '26' Cells_Y, round(column28,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '27' Cells_Y, round(column29,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '28' Cells_Y, round(column30,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '29' Cells_Y, round(column31,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '30' Cells_Y, round(column32,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '31' Cells_Y, round(column33,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '32' Cells_Y, round(column34,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '33' Cells_Y, round(column35,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '34' Cells_Y, round(column36,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '35' Cells_Y, round(column37,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '36' Cells_Y, round(column38,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '37' Cells_Y, round(column39,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '38' Cells_Y, round(column40,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '39' Cells_Y, round(column41,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '40' Cells_Y, round(column42,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '41' Cells_Y, round(column43,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '42' Cells_Y, round(column44,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '43' Cells_Y, round(column45,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '44' Cells_Y, round(column46,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '45' Cells_Y, round(column47,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '46' Cells_Y, round(column48,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '47' Cells_Y, round(column49,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '48' Cells_Y, round(column50,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '49' Cells_Y, round(column51,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '50' Cells_Y, round(column52,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '51' Cells_Y, round(column53,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '52' Cells_Y, round(column54,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '53' Cells_Y, round(column55,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '54' Cells_Y, round(column56,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '55' Cells_Y, round(column57,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '56' Cells_Y, round(column58,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '57' Cells_Y, round(column59,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '58' Cells_Y, round(column60,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '59' Cells_Y, round(column61,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '60' Cells_Y, round(column62,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '61' Cells_Y, round(column63,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '62' Cells_Y, round(column64,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '63' Cells_Y, round(column65,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '64' Cells_Y, round(column66,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '65' Cells_Y, round(column67,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '66' Cells_Y, round(column68,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '67' Cells_Y, round(column69,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '68' Cells_Y, round(column70,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '69' Cells_Y, round(column71,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '70' Cells_Y, round(column72,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '71' Cells_Y, round(column73,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '72' Cells_Y, round(column74,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '73' Cells_Y, round(column75,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '74' Cells_Y, round(column76,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '75' Cells_Y, round(column77,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '76' Cells_Y, round(column78,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '77' Cells_Y, round(column79,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '78' Cells_Y, round(column80,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '79' Cells_Y, round(column81,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '80' Cells_Y, round(column82,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '81' Cells_Y, round(column83,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '82' Cells_Y, round(column84,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '83' Cells_Y, round(column85,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '84' Cells_Y, round(column86,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '85' Cells_Y, round(column87,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '86' Cells_Y, round(column88,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '87' Cells_Y, round(column89,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '88' Cells_Y, round(column90,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '89' Cells_Y, round(column91,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '90' Cells_Y, round(column92,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '91' Cells_Y, round(column93,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '92' Cells_Y, round(column94,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '93' Cells_Y, round(column95,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '94' Cells_Y, round(column96,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '95' Cells_Y, round(column97,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '96' Cells_Y, round(column98,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '97' Cells_Y, round(column99,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '98' Cells_Y, round(column100,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '99' Cells_Y, round(column101,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '100' Cells_Y, round(column102,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '101' Cells_Y, round(column103,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '102' Cells_Y, round(column104,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '103' Cells_Y, round(column105,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '104' Cells_Y, round(column106,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '105' Cells_Y, round(column107,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '106' Cells_Y, round(column108,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '107' Cells_Y, round(column109,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '108' Cells_Y, round(column110,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '109' Cells_Y, round(column111,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '110' Cells_Y, round(column112,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '111' Cells_Y, round(column113,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '112' Cells_Y, round(column114,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '113' Cells_Y, round(column115,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '114' Cells_Y, round(column116,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '115' Cells_Y, round(column117,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '116' Cells_Y, round(column118,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '117' Cells_Y, round(column119,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '118' Cells_Y, round(column120,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '119' Cells_Y, round(column121,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '120' Cells_Y, round(column122,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '121' Cells_Y, round(column123,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '122' Cells_Y, round(column124,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '123' Cells_Y, round(column125,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '124' Cells_Y, round(column126,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '125' Cells_Y, round(column127,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '126' Cells_Y, round(column128,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '127' Cells_Y, round(column129,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '128' Cells_Y, round(column130,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '129' Cells_Y, round(column131,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '130' Cells_Y, round(column132,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '131' Cells_Y, round(column133,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '132' Cells_Y, round(column134,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '133' Cells_Y, round(column135,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '134' Cells_Y, round(column136,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '135' Cells_Y, round(column137,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '136' Cells_Y, round(column138,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '137' Cells_Y, round(column139,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '138' Cells_Y, round(column140,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '139' Cells_Y, round(column141,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '140' Cells_Y, round(column142,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '141' Cells_Y, round(column143,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '142' Cells_Y, round(column144,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '143' Cells_Y, round(column145,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '144' Cells_Y, round(column146,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '145' Cells_Y, round(column147,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '146' Cells_Y, round(column148,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '147' Cells_Y, round(column149,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '148' Cells_Y, round(column150,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '149' Cells_Y, round(column151,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '150' Cells_Y, round(column152,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '151' Cells_Y, round(column153,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '152' Cells_Y, round(column154,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '153' Cells_Y, round(column155,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '154' Cells_Y, round(column156,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '155' Cells_Y, round(column157,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '156' Cells_Y, round(column158,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '157' Cells_Y, round(column159,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '158' Cells_Y, round(column160,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '159' Cells_Y, round(column161,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '160' Cells_Y, round(column162,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '161' Cells_Y, round(column163,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '162' Cells_Y, round(column164,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '163' Cells_Y, round(column165,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '164' Cells_Y, round(column166,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '165' Cells_Y, round(column167,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '166' Cells_Y, round(column168,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '167' Cells_Y, round(column169,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '168' Cells_Y, round(column170,4) BGMJ from {2} WHERE {1}='{0}' ", (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, ColumnName, fileName), true); |
|
} |
|
|
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
#endregion |
|
|
|
#region 行政区调入 |
|
dt = dbHelper.ExecuteDatatable("XZQDR", string.Format(@" select '6' Cells_X,Cells_Y,BGMJ from |
|
( |
|
select dlbm||QSXZ DLBM,sum(BGMJ)BGMJ |
|
from |
|
( |
|
select case when XZQTZLX='1' THEN bgq else bgh END dlbm,BGMJ,case when XZQTZLX='1' THEN BGQQSXZ else BGHQSXZ END QSXZ |
|
from ( |
|
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,BGHQSXZ,BGQQSXZ,XZQTZLX from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX , |
|
BGQQSXZ, BGHQSXZ |
|
from JCTJB_GQ where XZQTZLX in ('1','3') |
|
)A |
|
)A |
|
)A GROUP BY dlbm ,QSXZ |
|
)A left join TDLYXZYJLMJAQSBHTJB_2 B ON a.DLBM=b.bgh where Cells_X=6"), true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
stringBuilder.Clear(); |
|
foreach (var item in keyValuePairs) |
|
{ |
|
var Cells_Y = item.Key.Split(','); |
|
stringBuilder.Append($@" |
|
union all |
|
select '6' Cells_X,case when QSXZ = '国有' then '{Cells_Y[0]}' ELSE '{Cells_Y[1]}' end Cells_Y ,BGMJ from |
|
( |
|
select dlbm,SUM(BGMJ) BGMJ,QSXZ from |
|
( |
|
select case when XZQTZLX='1' THEN bgq else bgh END dlbm,BGMJ,case when XZQTZLX='1' THEN BGQQSXZ else BGHQSXZ END QSXZ from ( |
|
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQQSXZ, BGHQSXZ,XZQTZLX from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, XZQTZLX,BGQQSXZ, BGHQSXZ from JCTJB_GQ where XZQTZLX in ('1','3') |
|
)A |
|
)A |
|
)A WHERE dlbm in ({item.Value}) GROUP BY QSXZ |
|
)A |
|
"); |
|
} |
|
dt = dbHelper.ExecuteDatatable("TDLYXZBGB", stringBuilder.ToString().Substring(13), true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
#endregion |
|
|
|
#region 行政区调出 |
|
dt = dbHelper.ExecuteDatatable("XZQDC", string.Format(@"select '7' Cells_X,Cells_Y,BGMJ from |
|
( |
|
select bgq||BGQQSXZ DLBM,sum(BGMJ)BGMJ |
|
from |
|
( |
|
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,BGQQSXZ from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX , |
|
BGQQSXZ, BGHQSXZ |
|
from JCTJB_GQ where XZQTZLX in ('2','4') |
|
)A |
|
)A GROUP BY bgq,BGQQSXZ |
|
)A left join TDLYXZYJLMJAQSBHTJB_2 B ON a.DLBM=b.bgh where Cells_X=10"), true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
stringBuilder.Clear(); |
|
foreach (var item in keyValuePairs) |
|
{ |
|
var Cells_Y = item.Key.Split(','); |
|
stringBuilder.Append($@" |
|
union ALL |
|
select '7' Cells_X,case when BGQQSXZ = '国有' then '{Cells_Y[0]}' ELSE '{Cells_Y[1]}' end Cells_Y, BGMJ from |
|
( |
|
select SUM(BGMJ) BGMJ,BGQQSXZ from |
|
( |
|
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQQSXZ, BGHQSXZ from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ,XZQTZLX , |
|
BGQQSXZ, BGHQSXZ |
|
from JCTJB_GQ where XZQTZLX in ('2','4') |
|
)A WHERE bgq in ({item.Value}) |
|
)A GROUP BY BGQQSXZ |
|
)A "); |
|
} |
|
dt = dbHelper.ExecuteDatatable("TDLYXZBGB", stringBuilder.ToString().Substring(13), true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
CellHelper.SetFileToDisk(resultdataTable, savePath, 0); |
|
#endregion |
|
CellHelper.Import(savePath); |
|
return true; |
|
} |
|
catch (Exception e) |
|
{ |
|
throw e; |
|
} |
|
finally |
|
{ |
|
|
|
} |
|
} |
|
|
|
public BBPCModel Export_SQL_2(string savePath, string baseReportPath, IRDBHelper dbHelper, ref List<BBPCModel> pcModelList) |
|
{ |
|
string ncsjExcelPath = string.Empty; |
|
BBPCModel result = null; |
|
string fileName = string.Empty; |
|
try |
|
{ |
|
if (string.IsNullOrWhiteSpace(savePath)) |
|
{ |
|
MessageHelper.ShowError("存储位置错误请重试!"); |
|
return result; |
|
} |
|
string sourceFile = string.Empty; |
|
DirectoryInfo dir = new DirectoryInfo(baseReportPath); |
|
#region 数据赋值 |
|
FileInfo[] fileSet = dir.GetFiles("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")土地利用现状二级分类面积按权属性质*", SearchOption.AllDirectories); |
|
if (fileSet != null && fileSet.Length > 0) |
|
{ |
|
ncsjExcelPath = fileSet[0].FullName; |
|
fileName = fileSet[0].Name.Replace("(", "").Replace(")", "").Replace((MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, "").Replace(fileSet[0].Extension, ""); |
|
} |
|
//else |
|
//{ |
|
// fileSet = dir.GetFiles("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")土地利用现状二级分类面积按权属性质汇总表.xls", SearchOption.AllDirectories); |
|
// if (fileSet != null && fileSet.Length > 0) |
|
// { |
|
// ncsjExcelPath = fileSet[0].FullName; |
|
// } |
|
//} |
|
if (String.IsNullOrWhiteSpace(ncsjExcelPath) == true) |
|
{ |
|
return result; |
|
} |
|
string tempPath = AppDomain.CurrentDomain.BaseDirectory + "TempalateReports\\BGTJ\\土地利用现状二级分类面积按权属性质变化统计表.xlsx"; |
|
savePath = savePath + "\\土地利用现状二级分类面积按权属性质变化统计表.xlsx"; |
|
File.Copy(tempPath, savePath, true); |
|
DataTable resultdataTable = new DataTable(); |
|
DataTable dt = dbHelper.ExecuteDatatable("TDLYXZBGB", @" select Cells_X,Cells_Y,SUM(BGMJ) BGMJ from |
|
( |
|
select Cells_X,Cells_Y,BGMJ from |
|
( |
|
select bgh||BGHQSXZ DLBM,sum(BGMJ)BGMJ |
|
from |
|
( |
|
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,BGHQSXZ from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX , |
|
BGQQSXZ, BGHQSXZ |
|
from JCTJB_GQ where XZQTZLX not in ('1','2','3','4') |
|
)A WHERE bgq<>bgh or BGQQSXZ<>BGHQSXZ |
|
)A GROUP BY bgh ,BGHQSXZ |
|
)A left join TDLYXZYJLMJAQSBHTJB_2 B ON a.DLBM=b.bgh where Cells_X=9 |
|
)a GROUP BY Cells_X,Cells_Y |
|
union ALL |
|
select Cells_X,Cells_Y,SUM(BGMJ) BGMJ from |
|
( |
|
select Cells_X,Cells_Y,BGMJ from |
|
( |
|
select bgq||BGQQSXZ DLBM,sum(BGMJ)BGMJ |
|
from |
|
( |
|
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,BGQQSXZ from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX , |
|
BGQQSXZ, BGHQSXZ |
|
from JCTJB_GQ where XZQTZLX not in ('1','2','3','4') |
|
)A WHERE bgq<>bgh or BGQQSXZ<>BGHQSXZ |
|
)A GROUP BY bgq,BGQQSXZ |
|
)A left join TDLYXZYJLMJAQSBHTJB_2 B ON a.DLBM=b.bgh where Cells_X=10 |
|
)a GROUP BY Cells_X,Cells_Y ", true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
#endregion |
|
|
|
Dictionary<string, string> keyValuePairs = new Dictionary<string, string>(); |
|
keyValuePairs.Add("5,6", "'0303','0304','0306','0402','1105','1106','1108'"); |
|
keyValuePairs.Add("29,30", "'0101','0102','0103'"); |
|
keyValuePairs.Add("41,42", "'0201','0202','0203','0204'"); |
|
keyValuePairs.Add("56,57", "'0301','0302','0305','0307'"); |
|
keyValuePairs.Add("71,72", "'0401','0403','0404'"); |
|
keyValuePairs.Add("83,84", "'201','202','203','204','205'"); |
|
keyValuePairs.Add("101,102", "'1001','1002','1003','1007','1008','1009'"); |
|
keyValuePairs.Add("125,126", "'1101','1102','1103','1104','1107','1110'"); |
|
keyValuePairs.Add("146,147", "'1006','1202','1203','1204','1205','1206','1207'"); |
|
StringBuilder stringBuilder = new StringBuilder(); |
|
foreach (var item in keyValuePairs) |
|
{ |
|
var Cells_Y = item.Key.Split(','); |
|
stringBuilder.Append($@" |
|
union all |
|
select '9' Cells_X,case when BGHQSXZ = '国有' then '{Cells_Y[0]}' ELSE '{Cells_Y[1]}' end Cells_Y ,BGMJ from |
|
( |
|
select bgh,SUM(BGMJ) BGMJ,BGHQSXZ from |
|
( |
|
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQQSXZ, BGHQSXZ from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, XZQTZLX, |
|
BGQQSXZ, BGHQSXZ |
|
from JCTJB_GQ where XZQTZLX not in ('1','2','3','4') |
|
)A WHERE bgh in ({item.Value}) and (BGQQSXZ<>BGHQSXZ or bgq not in ({item.Value})) |
|
)A GROUP BY BGHQSXZ |
|
)A |
|
union ALL |
|
select '10' Cells_X,case when BGQQSXZ = '国有' then '{Cells_Y[0]}' ELSE '{Cells_Y[1]}' end Cells_Y, BGMJ from |
|
( |
|
select SUM(BGMJ) BGMJ,BGQQSXZ from |
|
( |
|
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQQSXZ, BGHQSXZ from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ,XZQTZLX , |
|
BGQQSXZ, BGHQSXZ |
|
from JCTJB_GQ where XZQTZLX not in ('1','2','3','4') |
|
)A WHERE bgq in ({item.Value}) and (BGQQSXZ<>BGHQSXZ or bgh not in ({item.Value})) |
|
)A GROUP BY BGQQSXZ |
|
)A |
|
"); |
|
} |
|
dt = dbHelper.ExecuteDatatable("TDLYXZBGB", stringBuilder.ToString().Substring(13), true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
#region 上年末面积 |
|
DataTable table = dbHelper.ExecuteDatatable("tab", string.Format(@"select case WHEN column1='{0}' THEN'column1' ELSE 'column2' END ColumnName from {1} WHERE column1='{0}' or column2='{0}' ", (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, fileName), true); |
|
if (table == null || table.Rows.Count == 0) |
|
{ |
|
return result; |
|
} |
|
string ColumnName = table.Rows[0][0].ToTrim(); |
|
if (ColumnName == "column1") |
|
{ |
|
dt = dbHelper.ExecuteDatatable("SNMMJ", string.Format(@" |
|
select '5' Cells_X, '1' Cells_Y, round(column2,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '2' Cells_Y, round(column3,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '3' Cells_Y, round(column4,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '4' Cells_Y, round(column5,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '5' Cells_Y, round(column6,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '6' Cells_Y, round(column7,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '7' Cells_Y, round(column8,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '8' Cells_Y, round(column9,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '9' Cells_Y, round(column10,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '10' Cells_Y, round(column11,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '11' Cells_Y, round(column12,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '12' Cells_Y, round(column13,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '13' Cells_Y, round(column14,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '14' Cells_Y, round(column15,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '15' Cells_Y, round(column16,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '16' Cells_Y, round(column17,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '17' Cells_Y, round(column18,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '18' Cells_Y, round(column19,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '19' Cells_Y, round(column20,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '20' Cells_Y, round(column21,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '21' Cells_Y, round(column22,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '22' Cells_Y, round(column23,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '23' Cells_Y, round(column24,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '24' Cells_Y, round(column25,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '25' Cells_Y, round(column26,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '26' Cells_Y, round(column27,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '27' Cells_Y, round(column28,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '28' Cells_Y, round(column29,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '29' Cells_Y, round(column30,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '30' Cells_Y, round(column31,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '31' Cells_Y, round(column32,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '32' Cells_Y, round(column33,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '33' Cells_Y, round(column34,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '34' Cells_Y, round(column35,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '35' Cells_Y, round(column36,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '36' Cells_Y, round(column37,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '37' Cells_Y, round(column38,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '38' Cells_Y, round(column39,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '39' Cells_Y, round(column40,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '40' Cells_Y, round(column41,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '41' Cells_Y, round(column42,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '42' Cells_Y, round(column43,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '43' Cells_Y, round(column44,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '44' Cells_Y, round(column45,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '45' Cells_Y, round(column46,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '46' Cells_Y, round(column47,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '47' Cells_Y, round(column48,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '48' Cells_Y, round(column49,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '49' Cells_Y, round(column50,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '50' Cells_Y, round(column51,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '51' Cells_Y, round(column52,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '52' Cells_Y, round(column53,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '53' Cells_Y, round(column54,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '54' Cells_Y, round(column55,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '55' Cells_Y, round(column56,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '56' Cells_Y, round(column57,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '57' Cells_Y, round(column58,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '58' Cells_Y, round(column59,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '59' Cells_Y, round(column60,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '60' Cells_Y, round(column61,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '61' Cells_Y, round(column62,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '62' Cells_Y, round(column63,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '63' Cells_Y, round(column64,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '64' Cells_Y, round(column65,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '65' Cells_Y, round(column66,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '66' Cells_Y, round(column67,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '67' Cells_Y, round(column68,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '68' Cells_Y, round(column69,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '69' Cells_Y, round(column70,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '70' Cells_Y, round(column71,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '71' Cells_Y, round(column72,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '72' Cells_Y, round(column73,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '73' Cells_Y, round(column74,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '74' Cells_Y, round(column75,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '75' Cells_Y, round(column76,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '76' Cells_Y, round(column77,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '77' Cells_Y, round(column78,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '78' Cells_Y, round(column79,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '79' Cells_Y, round(column80,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '80' Cells_Y, round(column81,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '81' Cells_Y, round(column82,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '82' Cells_Y, round(column83,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '83' Cells_Y, round(column84,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '84' Cells_Y, round(column85,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '85' Cells_Y, round(column86,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '86' Cells_Y, round(column87,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '87' Cells_Y, round(column88,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '88' Cells_Y, round(column89,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '89' Cells_Y, round(column90,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '90' Cells_Y, round(column91,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '91' Cells_Y, round(column92,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '92' Cells_Y, round(column93,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '93' Cells_Y, round(column94,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '94' Cells_Y, round(column95,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '95' Cells_Y, round(column96,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '96' Cells_Y, round(column97,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '97' Cells_Y, round(column98,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '98' Cells_Y, round(column99,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '99' Cells_Y, round(column100,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '100' Cells_Y, round(column101,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '101' Cells_Y, round(column102,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '102' Cells_Y, round(column103,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '103' Cells_Y, round(column104,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '104' Cells_Y, round(column105,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '105' Cells_Y, round(column106,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '106' Cells_Y, round(column107,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '107' Cells_Y, round(column108,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '108' Cells_Y, round(column109,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '109' Cells_Y, round(column110,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '110' Cells_Y, round(column111,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '111' Cells_Y, round(column112,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '112' Cells_Y, round(column113,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '113' Cells_Y, round(column114,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '114' Cells_Y, round(column115,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '115' Cells_Y, round(column116,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '116' Cells_Y, round(column117,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '117' Cells_Y, round(column118,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '118' Cells_Y, round(column119,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '119' Cells_Y, round(column120,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '120' Cells_Y, round(column121,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '121' Cells_Y, round(column122,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '122' Cells_Y, round(column123,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '123' Cells_Y, round(column124,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '124' Cells_Y, round(column125,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '125' Cells_Y, round(column126,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '126' Cells_Y, round(column127,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '127' Cells_Y, round(column128,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '128' Cells_Y, round(column129,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '129' Cells_Y, round(column130,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '130' Cells_Y, round(column131,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '131' Cells_Y, round(column132,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '132' Cells_Y, round(column133,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '133' Cells_Y, round(column134,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '134' Cells_Y, round(column135,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '135' Cells_Y, round(column136,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '136' Cells_Y, round(column137,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '137' Cells_Y, round(column138,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '138' Cells_Y, round(column139,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '139' Cells_Y, round(column140,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '140' Cells_Y, round(column141,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '141' Cells_Y, round(column142,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '142' Cells_Y, round(column143,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '143' Cells_Y, round(column144,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '144' Cells_Y, round(column145,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '145' Cells_Y, round(column146,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '146' Cells_Y, round(column147,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '147' Cells_Y, round(column148,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '148' Cells_Y, round(column149,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '149' Cells_Y, round(column150,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '150' Cells_Y, round(column151,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '151' Cells_Y, round(column152,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '152' Cells_Y, round(column153,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '153' Cells_Y, round(column154,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '154' Cells_Y, round(column155,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '155' Cells_Y, round(column156,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '156' Cells_Y, round(column157,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '157' Cells_Y, round(column158,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '158' Cells_Y, round(column159,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '159' Cells_Y, round(column160,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '160' Cells_Y, round(column161,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '161' Cells_Y, round(column162,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '162' Cells_Y, round(column163,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '163' Cells_Y, round(column164,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '164' Cells_Y, round(column165,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '165' Cells_Y, round(column166,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '166' Cells_Y, round(column167,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '167' Cells_Y, round(column168,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '168' Cells_Y, round(column169,4) BGMJ from {2} WHERE {1}='{0}' ", (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, ColumnName, fileName), true); |
|
} |
|
else if (ColumnName == "column2") |
|
{ |
|
dt = dbHelper.ExecuteDatatable("SNMMJ", string.Format(@" |
|
select '5' Cells_X, '1' Cells_Y, round(column3,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '2' Cells_Y, round(column4,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '3' Cells_Y, round(column5,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '4' Cells_Y, round(column6,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '5' Cells_Y, round(column7,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '6' Cells_Y, round(column8,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '7' Cells_Y, round(column9,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '8' Cells_Y, round(column10,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '9' Cells_Y, round(column11,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '10' Cells_Y, round(column12,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '11' Cells_Y, round(column13,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '12' Cells_Y, round(column14,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '13' Cells_Y, round(column15,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '14' Cells_Y, round(column16,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '15' Cells_Y, round(column17,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '16' Cells_Y, round(column18,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '17' Cells_Y, round(column19,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '18' Cells_Y, round(column20,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '19' Cells_Y, round(column21,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '20' Cells_Y, round(column22,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '21' Cells_Y, round(column23,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '22' Cells_Y, round(column24,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '23' Cells_Y, round(column25,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '24' Cells_Y, round(column26,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '25' Cells_Y, round(column27,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '26' Cells_Y, round(column28,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '27' Cells_Y, round(column29,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '28' Cells_Y, round(column30,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '29' Cells_Y, round(column31,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '30' Cells_Y, round(column32,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '31' Cells_Y, round(column33,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '32' Cells_Y, round(column34,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '33' Cells_Y, round(column35,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '34' Cells_Y, round(column36,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '35' Cells_Y, round(column37,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '36' Cells_Y, round(column38,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '37' Cells_Y, round(column39,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '38' Cells_Y, round(column40,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '39' Cells_Y, round(column41,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '40' Cells_Y, round(column42,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '41' Cells_Y, round(column43,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '42' Cells_Y, round(column44,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '43' Cells_Y, round(column45,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '44' Cells_Y, round(column46,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '45' Cells_Y, round(column47,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '46' Cells_Y, round(column48,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '47' Cells_Y, round(column49,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '48' Cells_Y, round(column50,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '49' Cells_Y, round(column51,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '50' Cells_Y, round(column52,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '51' Cells_Y, round(column53,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '52' Cells_Y, round(column54,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '53' Cells_Y, round(column55,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '54' Cells_Y, round(column56,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '55' Cells_Y, round(column57,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '56' Cells_Y, round(column58,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '57' Cells_Y, round(column59,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '58' Cells_Y, round(column60,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '59' Cells_Y, round(column61,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '60' Cells_Y, round(column62,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '61' Cells_Y, round(column63,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '62' Cells_Y, round(column64,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '63' Cells_Y, round(column65,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '64' Cells_Y, round(column66,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '65' Cells_Y, round(column67,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '66' Cells_Y, round(column68,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '67' Cells_Y, round(column69,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '68' Cells_Y, round(column70,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '69' Cells_Y, round(column71,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '70' Cells_Y, round(column72,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '71' Cells_Y, round(column73,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '72' Cells_Y, round(column74,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '73' Cells_Y, round(column75,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '74' Cells_Y, round(column76,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '75' Cells_Y, round(column77,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '76' Cells_Y, round(column78,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '77' Cells_Y, round(column79,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '78' Cells_Y, round(column80,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '79' Cells_Y, round(column81,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '80' Cells_Y, round(column82,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '81' Cells_Y, round(column83,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '82' Cells_Y, round(column84,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '83' Cells_Y, round(column85,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '84' Cells_Y, round(column86,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '85' Cells_Y, round(column87,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '86' Cells_Y, round(column88,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '87' Cells_Y, round(column89,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '88' Cells_Y, round(column90,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '89' Cells_Y, round(column91,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '90' Cells_Y, round(column92,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '91' Cells_Y, round(column93,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '92' Cells_Y, round(column94,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '93' Cells_Y, round(column95,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '94' Cells_Y, round(column96,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '95' Cells_Y, round(column97,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '96' Cells_Y, round(column98,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '97' Cells_Y, round(column99,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '98' Cells_Y, round(column100,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '99' Cells_Y, round(column101,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '100' Cells_Y, round(column102,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '101' Cells_Y, round(column103,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '102' Cells_Y, round(column104,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '103' Cells_Y, round(column105,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '104' Cells_Y, round(column106,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '105' Cells_Y, round(column107,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '106' Cells_Y, round(column108,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '107' Cells_Y, round(column109,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '108' Cells_Y, round(column110,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '109' Cells_Y, round(column111,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '110' Cells_Y, round(column112,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '111' Cells_Y, round(column113,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '112' Cells_Y, round(column114,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '113' Cells_Y, round(column115,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '114' Cells_Y, round(column116,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '115' Cells_Y, round(column117,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '116' Cells_Y, round(column118,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '117' Cells_Y, round(column119,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '118' Cells_Y, round(column120,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '119' Cells_Y, round(column121,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '120' Cells_Y, round(column122,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '121' Cells_Y, round(column123,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '122' Cells_Y, round(column124,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '123' Cells_Y, round(column125,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '124' Cells_Y, round(column126,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '125' Cells_Y, round(column127,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '126' Cells_Y, round(column128,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '127' Cells_Y, round(column129,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '128' Cells_Y, round(column130,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '129' Cells_Y, round(column131,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '130' Cells_Y, round(column132,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '131' Cells_Y, round(column133,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '132' Cells_Y, round(column134,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '133' Cells_Y, round(column135,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '134' Cells_Y, round(column136,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '135' Cells_Y, round(column137,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '136' Cells_Y, round(column138,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '137' Cells_Y, round(column139,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '138' Cells_Y, round(column140,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '139' Cells_Y, round(column141,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '140' Cells_Y, round(column142,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '141' Cells_Y, round(column143,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '142' Cells_Y, round(column144,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '143' Cells_Y, round(column145,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '144' Cells_Y, round(column146,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '145' Cells_Y, round(column147,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '146' Cells_Y, round(column148,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '147' Cells_Y, round(column149,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '148' Cells_Y, round(column150,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '149' Cells_Y, round(column151,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '150' Cells_Y, round(column152,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '151' Cells_Y, round(column153,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '152' Cells_Y, round(column154,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '153' Cells_Y, round(column155,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '154' Cells_Y, round(column156,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '155' Cells_Y, round(column157,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '156' Cells_Y, round(column158,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '157' Cells_Y, round(column159,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '158' Cells_Y, round(column160,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '159' Cells_Y, round(column161,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '160' Cells_Y, round(column162,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '161' Cells_Y, round(column163,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '162' Cells_Y, round(column164,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '163' Cells_Y, round(column165,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '164' Cells_Y, round(column166,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '165' Cells_Y, round(column167,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '166' Cells_Y, round(column168,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '167' Cells_Y, round(column169,4) BGMJ from {2} WHERE {1}='{0}' union ALL |
|
select '5' Cells_X, '168' Cells_Y, round(column170,4) BGMJ from {2} WHERE {1}='{0}' ", (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, ColumnName, fileName), true); |
|
} |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
#endregion |
|
|
|
#region 行政区调入 |
|
dt = dbHelper.ExecuteDatatable("XZQDR", string.Format(@" select '6' Cells_X,Cells_Y,BGMJ from |
|
( |
|
select dlbm||QSXZ DLBM,sum(BGMJ)BGMJ |
|
from |
|
( |
|
select case when XZQTZLX='1' THEN bgq else bgh END dlbm,BGMJ,case when XZQTZLX='1' THEN BGQQSXZ else BGHQSXZ END QSXZ |
|
from ( |
|
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,BGHQSXZ,BGQQSXZ,XZQTZLX from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX , |
|
BGQQSXZ, BGHQSXZ |
|
from JCTJB_GQ where XZQTZLX in ('1','3') |
|
)A |
|
)A |
|
)A GROUP BY dlbm ,QSXZ |
|
)A left join TDLYXZYJLMJAQSBHTJB_2 B ON a.DLBM=b.bgh where Cells_X=6"), true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
stringBuilder.Clear(); |
|
foreach (var item in keyValuePairs) |
|
{ |
|
var Cells_Y = item.Key.Split(','); |
|
stringBuilder.Append($@" |
|
union all |
|
select '6' Cells_X,case when QSXZ = '国有' then '{Cells_Y[0]}' ELSE '{Cells_Y[1]}' end Cells_Y ,BGMJ from |
|
( |
|
select dlbm,SUM(BGMJ) BGMJ,QSXZ from |
|
( |
|
select case when XZQTZLX='1' THEN bgq else bgh END dlbm,BGMJ,case when XZQTZLX='1' THEN BGQQSXZ else BGHQSXZ END QSXZ from ( |
|
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQQSXZ, BGHQSXZ,XZQTZLX from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, XZQTZLX,BGQQSXZ, BGHQSXZ from JCTJB_GQ where XZQTZLX in ('1','3') |
|
)A WHERE bgh in ({item.Value}) |
|
)A |
|
)A GROUP BY QSXZ |
|
)A |
|
"); |
|
} |
|
dt = dbHelper.ExecuteDatatable("TDLYXZBGB", stringBuilder.ToString().Substring(13), true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
#endregion |
|
|
|
#region 行政区调出 |
|
dt = dbHelper.ExecuteDatatable("XZQDC", string.Format(@"select '7' Cells_X,Cells_Y,BGMJ from |
|
( |
|
select bgq||BGQQSXZ DLBM,sum(BGMJ)BGMJ |
|
from |
|
( |
|
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,BGQQSXZ from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX , |
|
BGQQSXZ, BGHQSXZ |
|
from JCTJB_GQ where XZQTZLX in ('2','4') |
|
)A |
|
)A GROUP BY bgq,BGQQSXZ |
|
)A left join TDLYXZYJLMJAQSBHTJB_2 B ON a.DLBM=b.bgh where Cells_X=10"), true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
stringBuilder.Clear(); |
|
foreach (var item in keyValuePairs) |
|
{ |
|
var Cells_Y = item.Key.Split(','); |
|
stringBuilder.Append($@" |
|
union ALL |
|
select '7' Cells_X,case when BGQQSXZ = '国有' then '{Cells_Y[0]}' ELSE '{Cells_Y[1]}' end Cells_Y, BGMJ from |
|
( |
|
select SUM(BGMJ) BGMJ,BGQQSXZ from |
|
( |
|
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQQSXZ, BGHQSXZ from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ,XZQTZLX , |
|
BGQQSXZ, BGHQSXZ |
|
from JCTJB_GQ where XZQTZLX in ('2','4') |
|
)A WHERE bgq in ({item.Value}) |
|
)A GROUP BY BGQQSXZ |
|
)A "); |
|
} |
|
dt = dbHelper.ExecuteDatatable("TDLYXZBGB", stringBuilder.ToString().Substring(13), true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
CellHelper.SetFileToDisk(resultdataTable, savePath, 0); |
|
#endregion |
|
CellHelper.Import(savePath); |
|
|
|
#region 年末面积平差 |
|
Workbook workbook = new Workbook(savePath); |
|
Worksheet sheet = workbook.Worksheets[0]; |
|
Range dataRange = null; |
|
dataRange = sheet.Cells.CreateRange("H11:FN11"); |
|
var XJ = new List<int>() { 0, 3, 6, 9, 12, 13, 15, 18, 21, 22, 23, 24, 27, 30, 33, 34, 35, 36, 39, 42, 45, 48, 49, 50, 51, 52, 54, 57, 60, 63, 64, 65, 66, 69, 72, 75, 76, 77, 78, 81, 84, 87, 90, 93, 94, 95, 96, 99, 102, 105, 108, 111, 114, 117, 118, 119, 120, 123, 126, 129, 132, 135, 138, 139, 140, 141, 144, 147, 150, 153, 156, 159 }; |
|
for (int i = 0; i < dataRange.ColumnCount; i++) |
|
{ |
|
if (XJ.Contains(i)) continue; |
|
var negative = dataRange[0, i].Value.ToDouble(); |
|
if (negative < 0)//负值 |
|
{ |
|
if (result == null) |
|
{ |
|
result = new BBPCModel(); |
|
result.ReportName = 2; |
|
result.Columns = new List<PCColumnModel>(); |
|
} |
|
PCColumnModel coluModel = new PCColumnModel(); |
|
coluModel.Ids = new List<int>(); |
|
coluModel.IsActive = true; |
|
coluModel.ColumnIndex = i; |
|
coluModel.DiffValue = Math.Abs(negative); |
|
string sql = string.Empty; |
|
sql = $@"select ID from (select bgq||BGQQSXZ DLBM,group_concat(ID) ID,sum(BGMJ)BGMJ from(select ID,case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,BGQQSXZ from (select ID,substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX , BGQQSXZ, BGHQSXZ from JCTJB_GQ)A WHERE bgq<>bgh or BGQQSXZ<>BGHQSXZ)A GROUP BY bgq||BGQQSXZ)A WHERE DLBM = (select bgh from TDLYXZYJLMJAQSBHTJB_2 WHERE Cells_X = '9' AND Cells_Y = '{i + 7}')"; |
|
DataTable IDsTable = dbHelper.ExecuteDatatable("ids", sql, true); |
|
string ids = string.Empty; |
|
foreach (DataRow row in IDsTable.Rows) |
|
{ |
|
string strID = row["ID"].ToString(); |
|
if (string.IsNullOrEmpty(strID)) |
|
{ |
|
for (int j = 0; j < strID.Split(',').Length; j++) |
|
{ |
|
coluModel.Ids.Add(Convert.ToInt32(strID.Split(',')[j])); |
|
} |
|
} |
|
} |
|
result.Columns.Add(coluModel); |
|
} |
|
} |
|
#endregion |
|
|
|
return result; |
|
} |
|
catch (Exception e) |
|
{ |
|
throw e; |
|
} |
|
finally |
|
{ |
|
|
|
} |
|
} |
|
} |
|
}
|
|
|