wangguanjie
/
NDBGJK5.0
4
1
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
年度变更建库软件5.0版本
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.
2 Commits
4 Branches
0 Tags
121 MiB
Tag: Branch: Tree: HHJ
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'HHJ'
${ noResults }
NDBGJK5.0/Kingo.Plugin.YJJK/ZLBBReport/ExportQSXZBHTJB.cs

2276 lines
146 KiB
Raw Permalink Normal View History Unescape

Initial commit
6 months ago
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.YJJK.ModelEntity;
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.YJJK.ZLBBReport
{
/// <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
)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
)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'
)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'
)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'
)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'
)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
{
}
}
}
}