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: wgj
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'wgj'
${ noResults }
NDBGJK5.0/Kingo.Plugin.YJJK/ModelEntity/MJHZJCTJBHelper.cs

843 lines
43 KiB
Raw Permalink Normal View History Unescape

Initial commit
6 months ago
using KGIS.Framework.DBOperator;
using KGIS.Framework.Utils;
using KGIS.Framework.Utils.ExtensionMethod;
using KGIS.Framework.Utils.Helper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace Kingo.Plugin.YJJK.ModelEntity
{
/// <summary>
/// 面积汇总基础统计表平差计算
/// </summary>
public class MJHZJCTJBHelper
{
public MJHZJCTJBHelper()
{
}
private string mdbpath;
public string MDBPath
{
get
{
if (string.IsNullOrWhiteSpace(mdbpath))
{
mdbpath = "";// Env.Instance.Project.GetProjMDBPath();
}
return mdbpath;
}
set { mdbpath = value; }
}
public string TempMDBPath { get; set; }
/// <summary>
/// 处理耕地种植属性为耕种的图斑可不标注问题
/// </summary>
/// <param name="dbHelper"></param>
private void SetGDZZSXDMIsGZ(IRDBHelper dbHelper)
{
/*
* 修改人：高山
* 修改时间：2018.12.6
* 修改原因：参与北京评测的公司都认为，扣除地类编码需要参与分组：地类图斑层中的图版存在扣除面积的，要单独提取出来做分组
*/
string mjhztj4 = @"SELECT ZLDWMC,ZLDWDM,QSDWDM,QSXZ,GDLX,GDPDJB,CZCSXM,TBXHDM,ZZSXDM,FRDBS,MSSM ,DLBM,TBDLMJ into MJHZTJB4 FROM (
SELECT ZLDWMC,LEFT(ZLDWDM,12) AS ZLDWDM,LEFT(QSDWDM,12) AS QSDWDM,QSXZ,'' as GDLX,'' as GDPDJB,CZCSXM,
'' as TBXHDM,'' as ZZSXDM,FRDBS,MSSM,KCDLBM AS DLBM,KCMJ AS TBDLMJ FROM DLTB WHERE KCDLBM IS NOT NULL
AND KCMJ IS NOT NULL AND KCDLBM <> '' AND KCMJ > 0
UNION ALL
(SELECT ZLDWMC,LEFT(ZLDWDM,12) AS ZLDWDM,LEFT(QSDWDM,12) AS QSDWDM,QSXZ,GDLX,GDPDJB,CZCSXM,TBXHDM,ZZSXDM,FRDBS,MSSM, DLBM ,TBDLMJ FROM DLTB)
)";
dbHelper.ExecuteNonQueryWithException(mjhztj4, CommandType.Text);
//此处处理这几个字段是因为字段值为null和空字符串不一致会影响分组
mjhztj4 = @"UPDATE MJHZTJB4 set GDLX='' WHERE GDLX IS NULL";
dbHelper.ExecuteNonQueryWithException(mjhztj4, CommandType.Text);
mjhztj4 = @"UPDATE MJHZTJB4 set GDPDJB='' WHERE GDPDJB IS NULL";
dbHelper.ExecuteNonQueryWithException(mjhztj4, CommandType.Text);
mjhztj4 = @"UPDATE MJHZTJB4 set TBXHDM='' WHERE TBXHDM IS NULL";
dbHelper.ExecuteNonQueryWithException(mjhztj4, CommandType.Text);
mjhztj4 = @"UPDATE MJHZTJB4 set ZZSXDM='' WHERE ZZSXDM IS NULL;";
dbHelper.ExecuteNonQueryWithException(mjhztj4, CommandType.Text);
//string mjhztj3 = @"select ZLDWMC,ZLDWDM,QSDWDM,QSXZ,GDLX,GDPDJB,CZCSXM,TBXHDM,GDZZSXDM,FRDBS,MSSM ,DLBM,TBDLMJ,KCMJ
//into MJHZTJB3 from ( SELECT ZLDWMC,LEFT(ZLDWDM,12) AS ZLDWDM,LEFT(QSDWDM,12) AS QSDWDM,QSXZ,GDLX,GDPDJB,CZCSXM,TBXHDM,
// GDZZSXDM,FRDBS,MSSM,DLBM as DLBM, Sum(TBDLMJ) as TBDLMJ ,Sum(KCMJ) as KCMJ FROM DLTB GROUP BY ZLDWMC, LEFT(ZLDWDM,12),
//LEFT(QSDWDM,12),QSXZ,GDLX,GDPDJB,CZCSXM,TBXHDM,GDZZSXDM,FRDBS,MSSM,DLBM)";
string mjhztj3 = @"SELECT ZLDWMC,ZLDWDM,QSDWDM,QSXZ,GDLX,GDPDJB,CZCSXM,TBXHDM,ZZSXDM,FRDBS,MSSM ,DLBM,TBDLMJ
INTO MJHZTJB3 FROM
(SELECT ZLDWMC,ZLDWDM,QSDWDM,QSXZ,GDLX,GDPDJB,CZCSXM,TBXHDM,ZZSXDM,FRDBS,MSSM,DLBM as DLBM, Sum(TBDLMJ) as TBDLMJ
FROM MJHZTJB4 GROUP BY ZLDWMC,ZLDWDM,QSDWDM,QSXZ,GDLX,GDPDJB,CZCSXM,TBXHDM,ZZSXDM,FRDBS,MSSM,DLBM
)";
dbHelper.ExecuteNonQueryWithException(mjhztj3, CommandType.Text);
//string updatesql = "update MJHZTJB3 set GDZZSXDM='GZ' where dlbm like '01%' and (GDZZSXDM is null or GDZZSXDM='')";
//dbHelper.ExecuteNonQueryWithException(updatesql, CommandType.Text);
DataTable dtmjhztj3 = dbHelper.ExecuteDatatable("dtmjhztj3", " select * from MJHZTJB3 where dlbm like '01%'", true);
if (dtmjhztj3 != null && dtmjhztj3.Rows.Count > 0)
{
for (int i = 0; i < dtmjhztj3.Rows.Count; i++)
{
if (string.IsNullOrWhiteSpace(dtmjhztj3.Rows[i]["ZZSXDM"].ToTrim()))
{
string QSXZ = !string.IsNullOrEmpty(dtmjhztj3.Rows[i]["QSXZ"].ToTrim()) ? " and QSXZ='" + dtmjhztj3.Rows[i]["QSXZ"] + "'" : " and (QSXZ is null or QSXZ='')";
string GDLX = !string.IsNullOrEmpty(dtmjhztj3.Rows[i]["GDLX"].ToTrim()) ? " and GDLX='" + dtmjhztj3.Rows[i]["GDLX"] + "'" : " and (GDLX is null or GDLX='')";
string GDPDJB = !string.IsNullOrEmpty(dtmjhztj3.Rows[i]["GDPDJB"].ToTrim()) ? " and GDPDJB='" + dtmjhztj3.Rows[i]["GDPDJB"] + "'" : " and (GDPDJB is null or GDPDJB='')";
string CZCSXM = !string.IsNullOrEmpty(dtmjhztj3.Rows[i]["CZCSXM"].ToTrim()) ? " and CZCSXM='" + dtmjhztj3.Rows[i]["CZCSXM"] + "'" : " and (CZCSXM is null or CZCSXM='')";
string TBXHDM = !string.IsNullOrEmpty(dtmjhztj3.Rows[i]["TBXHDM"].ToTrim()) ? " and TBXHDM='" + dtmjhztj3.Rows[i]["TBXHDM"] + "'" : " and (TBXHDM is null or TBXHDM='')";
string GDZZSXDM = !string.IsNullOrEmpty(dtmjhztj3.Rows[i]["ZZSXDM"].ToTrim()) ? " and ZZSXDM='" + dtmjhztj3.Rows[i]["ZZSXDM"] + "'" : " and (ZZSXDM is null or ZZSXDM='')";
string FRDBS = !string.IsNullOrEmpty(dtmjhztj3.Rows[i]["FRDBS"].ToTrim()) ? " and FRDBS='" + dtmjhztj3.Rows[i]["FRDBS"] + "'" : " and (FRDBS is null or FRDBS='')";
string dtwhere = string.Format(" DLBM like '01%' and ZLDWDM='{0}' and QSDWDM='{1}' and MSSM='{2}' {3} ", dtmjhztj3.Rows[i]["ZLDWDM"], dtmjhztj3.Rows[i]["QSDWDM"], dtmjhztj3.Rows[i]["MSSM"], QSXZ + GDLX + GDPDJB + CZCSXM + TBXHDM + GDZZSXDM + FRDBS);
dbHelper.ExecuteNonQueryWithException(string.Format("update MJHZTJB3 set ZZSXDM='GZ' where {0}", dtwhere), CommandType.Text);
}
}
}
}
/// <summary>
/// 拼接地类编码SQL语句
/// </summary>
/// <param name="ejdllist"></param>
/// <returns></returns>
private string GetInsertC1(List<string> ejdllist)
{
string resultylstr = string.Empty;
#region 计算地类编码集合
//foreach (var item in ejdllist)
//{
resultylstr = ",D" + string.Join(",D", ejdllist);
//resultylstr = resultylstr + string.Format(" ,D{0} ", item);
//}
#endregion
return resultylstr;
}
/// <summary>
/// 获取所有地类编码
/// </summary>
/// <param name="ejdllist"></param>
/// <returns></returns>
private string Getsumc(List<string> ejdllist, bool IsPZWJSYT = false)
{
string resultylstr = string.Empty;
#region 计算地类编码集合
foreach (var item in ejdllist)
{
if (IsPZWJSYT)
{
resultylstr = resultylstr + string.Format(" ,sum(IIF(PZYT='{0}',DCMJ,0)) as D{0}", item);
}
else
{
resultylstr = resultylstr + string.Format(" ,sum(IIF(DLBM='{0}',TBDLMJ,0)) as D{0}", item);
}
}
#endregion
return resultylstr;
}
private string Getsumc_YJJBNTTB(List<string> ejdllist)
{
string resultylstr = string.Empty;
#region 计算地类编码集合
foreach (var item in ejdllist)
{
resultylstr = resultylstr + string.Format(" ,sum(IIF(DLBM='{0}',TBMJ,0)) as D{0}", item);
}
#endregion
return resultylstr;
}
/// <summary>
/// 更新图斑面积（图斑面积调平）
/// </summary>
/// <param name="dbHelper"></param>
/// <param name="zfvalue">加减调平数</param>
/// <param name="js">基数（0.01）</param>
/// <param name="cjzsmdt">村级汇总数据表数据</param>
/// <param name="cjzsm">总数量</param>
/// <param name="f">起始值</param>
/// <param name="ftpzmj">调平面积</param>
private void UpdateTBMJ(IRDBHelper dbHelper, decimal zfvalue, decimal js, DataTable cjzsmdt, int cjzsm, int f, decimal ftpzmj, string mssm)
{
//已知村的调平面积，调平村内图斑面积
//村内的图斑调平面积=村的调平面积除以调平基数，得到调平数目，用调平数目除以图斑数量，得到商 e2,余数 f2,
string updatedbsql = "update MJHZJCTJB set TBZMJ=TBZMJ{2} where bsm in (select top {1} bsm from MJHZJCTJB where ZLDWDM='{0}' and mssm='" + mssm + "' and TBZMJ > 0.01 {3} order by TBZMJ desc,bsm desc )";//前余数个（f2）图斑的调平sql语句
//string updatedbsql2 = "update MJHZJCTJB set TBZMJ=TBZMJ{2} where bsm in (select top {1} bsm from MJHZJCTJB where ZLDWDM='{0}' and mssm='" + mssm + "' and TBZMJ>0 {3} order by TBZMJ,bsm )";//商数个（f2）图斑的调平sql语句
string updatedbsql2 = "update MJHZJCTJB set TBZMJ=TBZMJ{1} where ZLDWDM='{0}' and mssm='" + mssm + "' and TBZMJ > 0.01 {2} ";//商数个（f2）图斑的调平sql语句
for (int i = f; i < cjzsm; i++)
{
int cjzsm2 = Convert.ToInt32(cjzsmdt.Rows[i]["C"]);
int tpsm2 = (int)(ftpzmj / js);
if (cjzsm2 == 0)
continue;
int e2 = tpsm2 / cjzsm2;
int f2 = tpsm2 % cjzsm2;
string tbmjwhere = string.Empty;
if (f2 > 0)
{
//decimal ftpzmj2 = (e2 + 1) * js;
decimal ftpzmj2 = js;
string tpvalue = string.Empty;
if (zfvalue < 0)
{
tpvalue = "-" + ftpzmj2.ToDecimal(2);
tbmjwhere = " and TBZMJ>=" + ftpzmj2;//为了以防万一出现负数
}
else
{
tpvalue = "+" + ftpzmj2.ToDecimal(2);
}
dbHelper.ExecuteNonQueryWithException(string.Format(updatedbsql, cjzsmdt.Rows[i]["ZLDWDM"], f2, tpvalue, tbmjwhere), CommandType.Text);
}
if (e2 > 0)
{
decimal ftpzmj2 = e2 * js;
string tpvalue = string.Empty;
if (zfvalue < 0)
{
tpvalue = "-" + ftpzmj2.ToDecimal(2);
tbmjwhere = " and TBZMJ>=" + ftpzmj2;//为了以防万一出现负数
}
else
{
tpvalue = "+" + ftpzmj2.ToDecimal(2);
}
dbHelper.ExecuteNonQueryWithException(string.Format(updatedbsql2, cjzsmdt.Rows[i]["ZLDWDM"], tpvalue, tbmjwhere), CommandType.Text);
}
}
}
/// <summary>
/// 末级地类平差
/// </summary>
/// <param name="dbHelper"></param>
private void EJDLPCData(IRDBHelper dbHelper, string tableName = "MJHZJCTJB")
{
DataTable updatedt = dbHelper.ExecuteDatatable(tableName, ("select * from " + tableName), false);
if (updatedt == null || updatedt.Rows.Count == 0)
{
return;
throw new Exception("初始基础统计表错误！");
}
List<DLBMTJEntity> dlbmlist;
List<string> ejdllist;
GetDLBMListByDT(updatedt, out dlbmlist, out ejdllist);
ejdllist.Reverse();
updatedt.PrimaryKey = new DataColumn[] { updatedt.Columns["BSM"] };
for (int i = 0; i < updatedt.Rows.Count; i++)
{
string strZLDWDM = updatedt.Rows[i]["ZLDWDM"].ToString();
string strZLDWMC = updatedt.Rows[i]["ZLDWMC"].ToString();
//获取每个小类的集合（平方米）
List<EJDLEntityClass> ejdllist1 = new List<EJDLEntityClass>();
int xh = ejdllist.Count;
foreach (var item in ejdllist)
{
string strPZYT = item;
double dbl = updatedt.Rows[i]["D" + item].ToDouble();
xh--;
EJDLEntityClass ejdlitem = new EJDLEntityClass();
ejdlitem.XH = xh;
ejdlitem.EJDLName = item;
ejdlitem.MetersValue = updatedt.Rows[i]["D" + item].ToDecimal(2);
if (ejdlitem.MetersValue <= 0)
{
ejdlitem.MetersValue = 0;
}
ejdlitem.HectareValue = (ejdlitem.MetersValue * 0.0001m).ToDecimal(2);
updatedt.Rows[i]["D" + item] = ejdlitem.HectareValue;
if (ejdlitem.MetersValue > 0)
{
ejdllist1.Add(ejdlitem);
}
}
//如果存在大于等于10000平方米的，则小于10000平方米的排除
if (ejdllist1.Count(x => x.MetersValue >= 10000) > 0)
{
while (ejdllist1.Count(x => x.MetersValue < 10000) > 0)
{
ejdllist1.Remove(ejdllist1.FirstOrDefault(x => x.MetersValue < 10000));
}
}
else
{
//去除平方米面积为0的
while (ejdllist1.Count(x => x.MetersValue <= 0) > 0)
{
ejdllist1.Remove(ejdllist1.FirstOrDefault(x => x.MetersValue <= 0));
}
}
bool pc = true;
while (pc)
{
pc = false;
decimal h = 0;
Dictionary<string, decimal> tempupdateejdldic = new Dictionary<string, decimal>();
Dictionary<string, decimal> updateejdldic = new Dictionary<string, decimal>();
foreach (var item in ejdllist)
{
decimal tempv = updatedt.Rows[i]["D" + item].ToDecimal(2);
if (tempv < 0)
{
tempv = 0;
updatedt.Rows[i]["D" + item] = tempv;
}
h = h + tempv;
//如果平方米当中存在，则添加
if (ejdllist1.Count(x => x.EJDLName == item) > 0)
{
tempupdateejdldic.Add(item, tempv);
}
}
var ejdlquery = from t in ejdllist1 orderby t.MetersValue descending, t.XH descending select t;
foreach (var item in ejdlquery)
{
updateejdldic.Add(item.EJDLName, tempupdateejdldic[item.EJDLName]);
}
decimal g = updatedt.Rows[i]["TBZMJ"].ToDecimal(2);
if (updateejdldic.Count == 0)
{
if (g > 0)
{
updatedt.Rows[i]["D" + ejdllist[0]] = g;
if (ejdllist1.Count == 0)
{
break;
}
pc = true;
continue;
}
}
decimal zfvalue = 1;
decimal mjc = 0;
decimal js = (decimal)0.01;//调平基数0.01
if (h > g)
{
zfvalue = -1;
mjc = h - g;
//为了避免负数，则把0的排除
while (updateejdldic.Count(x => x.Value <= 0) > 0)
{
updateejdldic.Remove(updateejdldic.FirstOrDefault(x => x.Value <= 0).Key);
}
if (updateejdldic.Count == 0)
{
if (mjc > 0)
{
updatedt.Rows[i]["D" + ejdllist[0]] = updatedt.Rows[i]["D" + ejdllist[0]].ToDecimal(2) - mjc;
pc = true;
continue;
}
}
}
else
{
mjc = g - h;
}
//如果面积差值大于0，进行平差
if (mjc > 0)
{
int tpsm = (int)(mjc / js);
int cjzsm = updateejdldic.Count;
if (cjzsm == 0)
continue;
int e = tpsm / cjzsm;
int f = tpsm % cjzsm;
if (f > 0)
{
//余数调平面积值
decimal ftpzmj = (e + 1) * js;
for (int x = 0; x < f; x++)
{
updatedt.Rows[i]["D" + updateejdldic.ElementAt(x).Key] = updateejdldic.ElementAt(x).Value + ftpzmj * zfvalue;
}
}
if (e > 0)
{
//商数调平面积值
decimal ftpzmj = e * js;
for (int x = f; x < cjzsm; x++)
{
updatedt.Rows[i]["D" + updateejdldic.ElementAt(x).Key] = updateejdldic.ElementAt(x).Value + ftpzmj * zfvalue;
}
}
pc = true;
continue;
}
}
//填充一级类汇总面积
foreach (var item in dlbmlist)
{
decimal yjdlmj = 0;
if (item.EJDLList != null && item.EJDLList.Count > 0)
{
foreach (var item2 in item.EJDLList)
{
yjdlmj = yjdlmj + updatedt.Rows[i]["D" + item2].ToDecimal(2);
}
updatedt.Rows[i]["D" + item.YJDL] = yjdlmj;
}
}
}
dbHelper.SaveTable(tableName, true);
}
private List<string> dlbm00List = new List<string>() { "0303", "0304", "0306", "0402", "0603", "1105", "1106", "1108" };
/// <summary>
/// 根据统计表列，获取地类编码一级类和二级类集合
/// </summary>
/// <param name="updatedt"></param>
/// <param name="dlbmlist"></param>
/// <param name="ejdllist"></param>
public void GetDLBMListByDT(DataTable updatedt, out List<DLBMTJEntity> dlbmlist, out List<string> ejdllist)
{
dlbmlist = new List<DLBMTJEntity>();
ejdllist = new List<string>();
#region 计算地类编码集合
foreach (DataColumn item in updatedt.Columns)
{
if (item.ColumnName.IndexOf("D") == 0 && item.ColumnName.Length == 3)
{
DLBMTJEntity dlbmtjitem = new DLBMTJEntity();
dlbmtjitem.YJDL = item.ColumnName.Substring(1);
dlbmtjitem.EJDLList = new List<string>();
foreach (DataColumn item2 in updatedt.Columns)
{
if (dlbmtjitem.YJDL.Equals("00") && dlbm00List.Contains(item2.ColumnName.Substring(1)) && item2.ColumnName.Length > 3)
{
dlbmtjitem.EJDLList.Add(item2.ColumnName.Substring(1));
continue;
}
if (item2.ColumnName.IndexOf(dlbmtjitem.YJDL) == 1 && item2.ColumnName.Length > 3 && !dlbm00List.Contains(item2.ColumnName.Substring(1)))
{
dlbmtjitem.EJDLList.Add(item2.ColumnName.Substring(1));
}
}
dlbmlist.Add(dlbmtjitem);
}
}
foreach (var item in dlbmlist)
{
if (item.EJDLList != null && item.EJDLList.Count > 0)
{
ejdllist.AddRange(item.EJDLList);
}
else
{
ejdllist.Add(item.YJDL);
}
}
#endregion
}
/// <summary>
/// 删除表操作
/// </summary>
/// <param name="helper"></param>
/// <param name="tableName"></param>
private void DropTable(IRDBHelper helper, string tableName)
{
try
{
if (helper == null || string.IsNullOrEmpty(tableName))
{
return;
}
string sql = " drop table " + tableName;
int a = helper.ExecuteNonQuery(sql, CommandType.Text);
}
catch (Exception ex)
{
LogAPI.Debug(ex);
}
}
/// <summary>
/// 统计平方米
/// </summary>
/// <param name="dbHelper"></param>
/// <param name="ishz">是否需要自己汇总(任意一个范围的平方米成果数据库，调用此方法就可以出报表)</param>
private void AddMeterMJPC(IRDBHelper dbHelper, bool ishz = true)
{
if (ishz)
{
//第一步：清空当前表数据
string deletesql = " delete from MJHZJCTJB2";
dbHelper.ExecuteNonQueryWithException(deletesql, CommandType.Text);
deletesql = " delete from MJHZTJB2";
dbHelper.ExecuteNonQueryWithException(deletesql, CommandType.Text);
DropTable(dbHelper, "MJHZTJB3");
string[] grouparry = new string[] { "QSXZ", "GDLX", "GDPDJB", "CZCSXM", "TBXHDM", "ZZSXDM", "FRDBS" };
foreach (var item in grouparry)
{
dbHelper.ExecuteNonQueryWithException(string.Format("update DLTB set {0}=null where {0}=''", item), CommandType.Text);
}
//处理耕地种植属性为耕种的图斑可不标注问题
SetGDZZSXDMIsGZ(dbHelper);
//第二步：按照座落、权属代码、权属性质、耕地类型、耕地坡度级别、城镇村属性码、图斑细化代码、耕地种植属性、描述说明、飞入地标识、地类编码分类汇总图斑地类面积、田坎面积到基础表2
string mjhztj2 = "insert into MJHZTJB2(ZLDWDM,QSDWDM,QSXZ,GDLX,GDPDJB,CZCSXM,TBXHDM,ZZSXDM,FRDBS,MSSM,DLBM,TBDLMJ,KCMJ) SELECT LEFT(ZLDWDM,12) AS ZLDWDM,LEFT(QSDWDM,12) AS QSDWDM,QSXZ,GDLX,GDPDJB,CZCSXM,TBXHDM,ZZSXDM,FRDBS,MSSM,DLBM, Sum(TBDLMJ) ,Sum(KCMJ) FROM MJHZTJB3 GROUP BY LEFT(ZLDWDM,12),LEFT(QSDWDM,12),QSXZ,GDLX,GDPDJB,CZCSXM,TBXHDM,ZZSXDM,FRDBS,MSSM,DLBM";
dbHelper.ExecuteNonQueryWithException(mjhztj2, CommandType.Text);
}
#region 计算平方米二级类汇总
DropTable(dbHelper, "MJHZJCTJB2");
DataTable mjhzdt = dbHelper.ExecuteDatatable("hztjb", "select * from MJHZJCTJB where 1=2", true);
List<DLBMTJEntity> dlbmlist;
List<string> ejdllist;
GetDLBMListByDT(mjhzdt, out dlbmlist, out ejdllist);
string insertc = GetInsertC1(ejdllist);
string sumc = Getsumc(ejdllist);
dbHelper.ExecuteNonQueryWithException("select * into MJHZJCTJB2 from (select * from MJHZJCTJB where 1=2) ", CommandType.Text);
string cjmjhzdtsql2 = "insert into MJHZJCTJB2(ZLDWDM,QSDWDM,QSXZ,GDLX,GDPDJB,CZCSXM,TBXHDM,ZZSXDM,FRDBS,MSSM,TBZMJ " + insertc + ") SELECT ZLDWDM,QSDWDM,QSXZ,GDLX,GDPDJB,CZCSXM,TBXHDM,ZZSXDM,FRDBS,MSSM, round(Sum(TBDLMJ),2) AS TBZMJ " + sumc + " FROM MJHZTJB2 GROUP BY ZLDWDM,QSDWDM,QSXZ,GDLX,GDPDJB,CZCSXM,TBXHDM,ZZSXDM,FRDBS,MSSM";
dbHelper.ExecuteNonQueryWithException(cjmjhzdtsql2, CommandType.Text);
//dbHelper.ExecuteNonQueryWithException("UPDATE MJHZJCTJB2 SET D1203=D1203+KCMJ", CommandType.Text);
#endregion
#region 填出二级类到一级类平方米
string updatesetstr = "";
//填充一级类汇总面积
foreach (var item in dlbmlist)
{
if (item.EJDLList != null && item.EJDLList.Count > 0)
{
updatesetstr = updatesetstr + "D" + item.YJDL + "=";
foreach (var item2 in item.EJDLList)
{
updatesetstr = updatesetstr + "D" + item2 + "+";
}
updatesetstr = updatesetstr.TrimEnd('+') + ",";
}
}
dbHelper.ExecuteNonQueryWithException("UPDATE MJHZJCTJB2 SET " + updatesetstr.TrimEnd(','), CommandType.Text);
#endregion
}
private bool VerificationLicense()
{
try
{
//if (Env.Instance.KeyType != "F")//非本地授权 直接返回
//{
return true;
//}
//RunIDHelper id = new RunIDHelper();
//return id.QCCheck();
}
catch (Exception ex)
{
LogAPI.Debug(ex);
return false;
}
}
#region 批准未建设土地平差
/// <summary>
/// 更新图斑面积（图斑面积调平）
/// </summary>
/// <param name="dbHelper"></param>
/// <param name="zfvalue">加减调平数</param>
/// <param name="js">基数（0.01）</param>
/// <param name="cjzsmdt">村级汇总数据表数据</param>
/// <param name="cjzsm">总数量</param>
/// <param name="f">起始值</param>
/// <param name="ftpzmj">调平面积</param>
private void PZWJSYDUpdateTBMJ(IRDBHelper dbHelper, decimal zfvalue, decimal js, DataTable cjzsmdt, int cjzsm, int f, decimal ftpzmj, string mssm, bool IsPZWJSYT = false)
{
string updatedbsql = null;
string updatedbsql2 = null;
if (IsPZWJSYT)
{
//已知村的调平面积，调平村内图斑面积
//村内的图斑调平面积=村的调平面积除以调平基数，得到调平数目，用调平数目除以图斑数量，得到商 e2,余数 f2,
updatedbsql = "update MJHZJCTJB_PZWJSYD_YT set TBZMJ=TBZMJ{2} where bsm in (select top {1} bsm from MJHZJCTJB_PZWJSYD where ZLDWDM='{0}' and mssm='" + mssm + "' and TBZMJ > 0.01 {3} order by TBZMJ desc,bsm desc )";//前余数个（f2）图斑的调平sql语句 //string updatedbsql2 = "update MJHZJCTJB set TBZMJ=TBZMJ{2} where bsm in (select top {1} bsm from MJHZJCTJB where ZLDWDM='{0}' and mssm='" + mssm + "' and TBZMJ>0 {3} order by TBZMJ,bsm )";//商数个（f2）图斑的调平sql语句
updatedbsql2 = "update MJHZJCTJB_PZWJSYD_YT set TBZMJ=TBZMJ{1} where ZLDWDM='{0}' and TBZMJ > 0.01 {2} and mssm='" + mssm + "'";//商数个（f2）图斑的调平sql语句
}
else
{
//已知村的调平面积，调平村内图斑面积
//村内的图斑调平面积=村的调平面积除以调平基数，得到调平数目，用调平数目除以图斑数量，得到商 e2,余数 f2,
updatedbsql = "update MJHZJCTJB_PZWJSYD set TBZMJ=TBZMJ{2} where bsm in (select top {1} bsm from MJHZJCTJB_PZWJSYD where ZLDWDM='{0}' and mssm='" + mssm + "' and TBZMJ > 0.01 {3} order by TBZMJ desc,bsm desc )";//前余数个（f2）图斑的调平sql语句 //string updatedbsql2 = "update MJHZJCTJB set TBZMJ=TBZMJ{2} where bsm in (select top {1} bsm from MJHZJCTJB where ZLDWDM='{0}' and mssm='" + mssm + "' and TBZMJ>0 {3} order by TBZMJ,bsm )";//商数个（f2）图斑的调平sql语句
updatedbsql2 = "update MJHZJCTJB_PZWJSYD set TBZMJ=TBZMJ{1} where ZLDWDM='{0}' and mssm='" + mssm + "' and TBZMJ > 0.01 {2} ";//商数个（f2）图斑的调平sql语句
}
for (int i = f; i < cjzsm; i++)
{
int cjzsm2 = Convert.ToInt32(cjzsmdt.Rows[i]["C"]);
int tpsm2 = (int)(ftpzmj / js);
if (cjzsm2 == 0)
continue;
int e2 = tpsm2 / cjzsm2;
int f2 = tpsm2 % cjzsm2;
string tbmjwhere = string.Empty;
if (f2 > 0)
{
//decimal ftpzmj2 = (e2 + 1) * js;
decimal ftpzmj2 = js;
string tpvalue = string.Empty;
if (zfvalue < 0)
{
tpvalue = "-" + ftpzmj2.ToDecimal(2);
tbmjwhere = " and TBZMJ>=" + ftpzmj2;//为了以防万一出现负数
}
else
{
tpvalue = "+" + ftpzmj2.ToDecimal(2);
}
dbHelper.ExecuteNonQueryWithException(string.Format(updatedbsql, cjzsmdt.Rows[i]["ZLDWDM"], f2, tpvalue, tbmjwhere), CommandType.Text);
}
if (e2 > 0)
{
decimal ftpzmj2 = e2 * js;
string tpvalue = string.Empty;
if (zfvalue < 0)
{
tpvalue = "-" + ftpzmj2.ToDecimal(2);
tbmjwhere = " and TBZMJ>=" + ftpzmj2;//为了以防万一出现负数
}
else
{
tpvalue = "+" + ftpzmj2.ToDecimal(2);
}
dbHelper.ExecuteNonQueryWithException(string.Format(updatedbsql2, cjzsmdt.Rows[i]["ZLDWDM"], tpvalue, tbmjwhere), CommandType.Text);
}
}
}
#endregion
#region 永久基本农田平差
private void AddMeterMJPC_YJJBNTTB(IRDBHelper dbHelper, bool ishz = true)
{
#region 计算平方米二级类汇总
DropTable(dbHelper, "MJHZJCTJB_YJJBNTTB2");
DataTable mjhzdt = dbHelper.ExecuteDatatable("hztjb", "select * from MJHZJCTJB_YJJBNTTB where 1=2", true);
List<DLBMTJEntity> dlbmlist;
List<string> ejdllist;
GetDLBMListByDT(mjhzdt, out dlbmlist, out ejdllist);
string insertc = GetInsertC1(ejdllist);
string sumc = Getsumc_YJJBNTTB(ejdllist);
dbHelper.ExecuteNonQueryWithException("select * into MJHZJCTJB_YJJBNTTB2 from (select * from MJHZJCTJB_YJJBNTTB where 1=2) ", CommandType.Text);
string cjmjhzdtsql2 = "insert into MJHZJCTJB_YJJBNTTB2 (MSSM, ZLDWDM,QSDWDM,QSXZ,GDLX,GDPDJB,CZCSXM,TBXHDM,ZZSXDM,FRDBS,TBZMJ " + insertc + ") SELECT " +
"MSSM, ZLDWDM,'','','','','','','','', round(Sum(TBMJ),2) AS TBZMJ " + sumc + " FROM MJHZTJB7 GROUP BY MSSM, ZLDWDM";
dbHelper.ExecuteNonQueryWithException(cjmjhzdtsql2, CommandType.Text);
#endregion
#region 填出二级类到一级类平方米
string updatesetstr = "";
//填充一级类汇总面积
foreach (var item in dlbmlist)
{
if (item.EJDLList != null && item.EJDLList.Count > 0)
{
updatesetstr = updatesetstr + "D" + item.YJDL + "=";
foreach (var item2 in item.EJDLList)
{
updatesetstr = updatesetstr + "D" + item2 + "+";
}
updatesetstr = updatesetstr.TrimEnd('+') + ",";
}
}
dbHelper.ExecuteNonQueryWithException("UPDATE MJHZJCTJB_YJJBNTTB2 SET " + updatesetstr.TrimEnd(','), CommandType.Text);
#endregion
}
private void YJJBNTTBPC_ZMJ(decimal xjmjkzs, IRDBHelper dbHelper, string mssm)
{
bool pc = true;
while (pc)
{
pc = false;
decimal cjzmj = 0M;
cjzmj = dbHelper.ExecuteDatatable("cjzmj", string.Format("SELECT sum(TBZMJ) FROM MJHZJCTJB_YJJBNTTB WHERE MSSM = '{0}'", mssm), true).Rows[0][0].ToDecimal(2);
//平差正反值，（后续平差是加，还是减）
decimal zfvalue = 1;
//面积差值
decimal mjc = 0;
decimal js = (decimal)0.01;//调平基数0.01
if (cjzmj > xjmjkzs)
{
zfvalue = -1;
mjc = cjzmj - xjmjkzs;
}
else if (cjzmj < xjmjkzs)
{
mjc = xjmjkzs - cjzmj;
}
if (mjc > 5M)
{
MessageHelper.ShowCaution(string.Format("永久基本农田{0}图斑总面积与实际面积相差过大差值超过5公顷无法平差请检查数据。", mssm == "00" ? "陆地" : "海岛"));
return;
}
//如果面积差值大于0，进行平差
if (mjc > 0)
{
//调平数目
int tpsm = (int)(mjc / js);
//此处sql语句必须要排序，以便最大面积图斑靠前
DataTable cjzsmdt = null;
cjzsmdt = dbHelper.ExecuteDatatable("cjzsm", "SELECT ZLDWDM,count(*) as C, sum(TBZMJ) AS TBZMJ FROM MJHZJCTJB_YJJBNTTB where mssm='" + mssm + "' and TBZMJ>0 group by ZLDWDM order by sum(TBZMJ) desc", true);
int cjzsm = cjzsmdt.Rows.Count;
if (cjzsm == 0)
{
LogAPI.Debug("此条SQL执行后无数据");
continue;
}
int e = tpsm / cjzsm;//商数
int f = tpsm % cjzsm;//余数
if (f > 0)
{
//余数村调平面积值
decimal ftpzmj = (e + 1) * js;
YJJBNTTBUpdateTBMJ(dbHelper, zfvalue, js, cjzsmdt, f, 0, ftpzmj, mssm);
}
if (e > 0)
{
//商数村调平面积值
decimal ftpzmj = e * js;
YJJBNTTBUpdateTBMJ(dbHelper, zfvalue, js, cjzsmdt, cjzsm, f, ftpzmj, mssm);
}
cjzmj = dbHelper.ExecuteDatatable("cjzmj", "SELECT sum(TBZMJ) FROM MJHZJCTJB_YJJBNTTB where mssm='" + mssm + "'", true).Rows[0][0].ToDecimal(2);
//再次确认是否调平？
if (cjzmj != xjmjkzs)
{
pc = true;
continue;
}
}
}
}
private void YJJBNTTBUpdateTBMJ(IRDBHelper dbHelper, decimal zfvalue, decimal js, DataTable cjzsmdt, int cjzsm, int f, decimal ftpzmj, string mssm)
{
string updatedbsql = null;
string updatedbsql2 = null;
//已知村的调平面积，调平村内图斑面积
//村内的图斑调平面积=村的调平面积除以调平基数，得到调平数目，用调平数目除以图斑数量，得到商 e2,余数 f2,
updatedbsql = "update MJHZJCTJB_YJJBNTTB set TBZMJ=TBZMJ{2} where bsm in (select top {1} bsm from MJHZJCTJB_PZWJSYD where mssm = '{4}' AND ZLDWDM='{0}' and TBZMJ > 0.01 {3} order by TBZMJ desc,bsm desc )";//前余数个（f2）图斑的调平sql语句 //string updatedbsql2 = "update MJHZJCTJB set TBZMJ=TBZMJ{2} where bsm in (select top {1} bsm from MJHZJCTJB where ZLDWDM='{0}' and mssm='" + mssm + "' and TBZMJ>0 {3} order by TBZMJ,bsm )";//商数个（f2）图斑的调平sql语句
updatedbsql2 = "update MJHZJCTJB_YJJBNTTB set TBZMJ=TBZMJ{1} where mssm = '{3}' AND ZLDWDM='{0}' and TBZMJ > 0.01 {2} ";//商数个（f2）图斑的调平sql语句
for (int i = f; i < cjzsm; i++)
{
int cjzsm2 = Convert.ToInt32(cjzsmdt.Rows[i]["C"]);
int tpsm2 = (int)(ftpzmj / js);
if (cjzsm2 == 0)
continue;
int e2 = tpsm2 / cjzsm2;
int f2 = tpsm2 % cjzsm2;
string tbmjwhere = string.Empty;
if (f2 > 0)
{
//decimal ftpzmj2 = (e2 + 1) * js;
decimal ftpzmj2 = js;
string tpvalue = string.Empty;
if (zfvalue < 0)
{
tpvalue = "-" + ftpzmj2.ToDecimal(2);
tbmjwhere = " and TBZMJ>=" + ftpzmj2;//为了以防万一出现负数
}
else
{
tpvalue = "+" + ftpzmj2.ToDecimal(2);
}
dbHelper.ExecuteNonQueryWithException(string.Format(updatedbsql, cjzsmdt.Rows[i]["ZLDWDM"], f2, tpvalue, tbmjwhere, mssm), CommandType.Text);
}
if (e2 > 0)
{
decimal ftpzmj2 = e2 * js;
string tpvalue = string.Empty;
if (zfvalue < 0)
{
tpvalue = "-" + ftpzmj2.ToDecimal(2);
tbmjwhere = " and TBZMJ>=" + ftpzmj2;//为了以防万一出现负数
}
else
{
tpvalue = "+" + ftpzmj2.ToDecimal(2);
}
dbHelper.ExecuteNonQueryWithException(string.Format(updatedbsql2, cjzsmdt.Rows[i]["ZLDWDM"], tpvalue, tbmjwhere, mssm), CommandType.Text);
}
}
}
#endregion
}
public class DLTB_YJJBNTModel
{
public int? FID_DLTB { get; set; }
public string BSM { get; set; }
public string YSDM { get; set; }
public string TBYBH { get; set; }
public string TBBH { get; set; }
public string DLBM { get; set; }
public string DLMC { get; set; }
public string QSXZ { get; set; }
public string QSDWDM { get; set; }
public string QSDWMC { get; set; }
public string ZLDWDM { get; set; }
public string ZLDWMC { get; set; }
public double? TBMJ { get; set; }//IsNullOrEmpty(row["TBMJ"]) ? (double?)(null) : (double)row["TBMJ"],
public string KCDLBM { get; set; }
public double? KCXS { get; set; } //IsNullOrEmpty(row["KCXS"]) ? (double?)(null) : (double)row["KCXS"],
public double? KCMJ { get; set; } //IsNullOrEmpty(row["KCMJ"]) ? (double?)(null) : (double)row["KCMJ"],
public double? TBDLMJ { get; set; }// IsNullOrEmpty(row["TBDLMJ"]) ? (double?)(null) : (double)row["TBDLMJ"],
public double? GDLX { get; set; }// row["GDLX"],
public double? GDPDJB { get; set; }//row["GDPDJB"],
public double? XZDWKD { get; set; }// IsNullOrEmpty(row["XZDWKD"]) ? (double?)(null) : (double)row["XZDWKD"],
public string TBXHDM { get; set; }
public string TBXHMC { get; set; }
public string ZZSXDM { get; set; }
public string ZZSXMC { get; set; }
public int? GDDB { get; set; }// IsNullOrEmpty(row["GDDB"]) ? (int?)(null) : (int)row["GDDB"],
public string FRDBS { get; set; }
public string CZCSXM { get; set; }
public int SJNF { get; set; }// IsNullOrEmpty(row["SJNF"]) ? (long?)(null) : (int)row["SJNF"],
public string MSSM { get; set; }
public string HDMC { get; set; }
public string BZ { get; set; }
public string XZQFID { get; set; }
public int? FID_YJJBNTTB { get; set; } //IsNullOrEmpty(row["FID_YJJBNTTB"]) ? (long?)(null) : (long)row["FID_YJJBNTTB"],
public string BSM_1 { get; set; }
public string YSDM_1 { get; set; }
public string JBNTTBBH { get; set; }
public string TBBH_1 { get; set; }
public string DLBM_1 { get; set; }
public string DLMC_1 { get; set; }
public string QSXZ_1 { get; set; }
public string QSDWDM_1 { get; set; }
public string QSDWMC_1 { get; set; }
public string ZLDWDM_1 { get; set; }
public string ZLDWMC_1 { get; set; }
public string GDLX_1 { get; set; }
public string JBNTLX { get; set; }
public string ZLDJDM { get; set; }
public string PDJB { get; set; }
public string KCLX { get; set; }
public string KCDLBM_1 { get; set; }
public double? TKXS { get; set; }// IsNullOrEmpty(row["TKXS"]) ? (double?)(null) : (double)row["TKXS"],
public double? XZDWMJ { get; set; }//IsNullOrEmpty(row["XZDWMJ"]) ? (double?)(null) : (double)row["XZDWMJ"],
public double? LXDWMJ { get; set; }// IsNullOrEmpty(row["LXDWMJ"]) ? (double?)(null) : (double)(double)row["LXDWMJ"],
public double? TKMJ { get; set; }// IsNullOrEmpty(row["TKMJ"]) ? (double?)(null) : (double)row["TKMJ"],
public double? TBMJ_1 { get; set; } //IsNullOrEmpty(row["TBMJ_1"]) ? (double?)(null) : (double)row["TBMJ_1"],
public double? JBNTMJ { get; set; }// IsNullOrEmpty(row["JBNTMJ"]) ? (double?)(null) : (double)row["JBNTMJ"],
public string DLBZ { get; set; }
}
}