HC_YFZX
/
feixian_weifajianguan
3
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
feixian_weifajianguan/OpenAuth.App/ServiceApp/DroneCaseInfo/DroneCaseInfoSingleApp1.cs

6750 lines
307 KiB
C#
Raw Permalink Blame History

This file contains ambiguous Unicode characters!

This file contains ambiguous Unicode characters that may be confused with others in your current locale. If your use case is intentional and legitimate, you can safely ignore this warning. Use the Escape button to highlight these characters.

using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Dynamic;
using System.IO.Compression;
using System.Text;
using System.Text.RegularExpressions;
using Castle.Core.Internal;
using ClosedXML.Excel;
using DocumentFormat.OpenXml.Bibliography;
using DocumentFormat.OpenXml.Drawing;
using DocumentFormat.OpenXml.Drawing.Diagrams;
using DocumentFormat.OpenXml.EMMA;
using DocumentFormat.OpenXml.Office2010.Excel;
using DocumentFormat.OpenXml.Office2010.ExcelAc;
using DocumentFormat.OpenXml.Presentation;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Wordprocessing;
using Enyim.Caching.Memcached;
using Flurl.Http;
using Hopetry.App.Common;
using ICSharpCode.SharpZipLib.GZip;
using Infrastructure;
using Infrastructure.Extensions;
using Infrastructure.Helpers;
using Infrastructure.Utilities;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using NetTopologySuite.Features;
using NetTopologySuite.Geometries;
using NetTopologySuite.IO;
using NetTopologySuite.IO.Esri.Shp;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Npgsql.TypeHandlers;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.Util;
using NPOI.XSSF.UserModel;
using NPOI.XWPF.UserModel;
using OpenAuth.App.BaseApp.Base;
using OpenAuth.App.BaseApp.Shape;
using OpenAuth.App.BasicQueryService;
using OpenAuth.App.FormModule;
using OpenAuth.App.Interface;
using OpenAuth.App.Request;
using OpenAuth.App.Response;
using OpenAuth.App.ServiceApp.DroneCaseInfo.Request;
using OpenAuth.App.ServiceApp.DroneCaseInfo.Response;
using OpenAuth.App.ServiceApp.Request;
using OpenAuth.App.ServiceApp.Response;
using OpenAuth.App.Shape;
using OpenAuth.Repository;
using OpenAuth.Repository.Domain;
using Org.BouncyCastle.Ocsp;
using SqlSugar;
using Yitter.IdGenerator;
using static OpenAuth.App.ServiceApp.DroneCaseInfo.Request.CaseSplitReq;
using CellType = NPOI.SS.UserModel.CellType;
namespace OpenAuth.App.ServiceApp.DroneCaseInfo;
public partial class DroneCaseInfoSingleApp
{
#region 统计
/// <summary>
/// 综合统计
/// </summary>
/// <returns></returns>
public async Task<Response<JArray>> CaseSynthesisCensus(QueryCaseInfoListReq req)
{
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
var currentUser = _auth.GetCurrentUser();
var _user = currentUser.User;
string countyName = _configuration.GetSection("CountyName").Value;
if (string.IsNullOrEmpty(countyName)) countyName = "费县";
var orgList = await db.Queryable<SysOrg>().Where(c => c.ParentName == countyName)
.OrderBy(c => c.SortNo, OrderByType.Asc).ToListAsync();
//无人机发现数量
var reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var allCountList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.streetname })
.Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//完成核查数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var handleStatusList = QueryCaseInfoList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//24小时未核查 (不包含已退回的部分)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_not_deal_hour24 = 1;
reqBody.is_drawback = 0;
var notDealHour24List = QueryCaseInfoList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//分类型统计案件数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var typeCountList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.typeid })
.Select(c => new
{
c.streetid,
c.typeid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法类型数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var illegalCountList = QueryCaseInfoList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.is_illegal }).Select(c => new
{
c.streetid,
c.is_illegal,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleStatusList = QueryCaseInfoList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.handle_status_id }).Select(c => new
{
c.streetid,
c.handle_status_id,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态 面积
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleAreaList = QueryCaseInfoList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.handle_status_id }).Select(c => new
{
c.streetid,
c.handle_status_id,
area = SqlFunc.AggregateSum(SqlFunc.ToDecimal(c.area)),
}).ToList();
//3日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 1;
reqBody.is_illegal = 1;
var notComplete3List = QueryCaseInfoList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//7日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 2;
reqBody.is_illegal = 1;
var notComplete7List = QueryCaseInfoList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 4;
reqBody.is_illegal = 1;
var notComplete30List = QueryCaseInfoList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日以上未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 5;
reqBody.is_illegal = 1;
var notComplete30MoreList = QueryCaseInfoList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//var typeList = await db.Queryable<SysCategory>().Where(c => c.TypeId == "DRONE_CASE_TYPE").ToListAsync();
var typepid = await db.Queryable<SysDataItem>().Where(c => c.ItemCode == "DRONE_CASE_TYPE").FirstAsync();
var typeList = await db.Queryable<SysDataItemDetail>().Where(c => c.ItemId == typepid.ItemId).ToListAsync();
JArray array = new JArray();
for (int i = 0; i < orgList.Count; i++)
{
var item = orgList[i];
//无人机发现数量
int _allCount = allCountList.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//完成核查数量
int _handleStatus = handleStatusList.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//24小时未核查
int _notDealHour24 = notDealHour24List.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//违法类型数量
int _illegal0Count = illegalCountList.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == 0)
.Sum(c => c.count);
int _illegal1Count = illegalCountList.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == 1)
.Sum(c => c.count);
int _illegal2Count = illegalCountList.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == 2)
.Sum(c => c.count);
int _illegalNullCount = illegalCountList
.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == null).Sum(c => c.count);
//违法 状态查询
int _illegalHandle2Status = illegalHandleStatusList
.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.count);
int _illegalHandle01Status = illegalHandleStatusList
.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.count);
//违法 面积查询
decimal? _illegalHandleAreaList =
illegalHandleAreaList.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.area);
if (_illegalHandleAreaList != null)
{
_illegalHandleAreaList = Decimal.Round(_illegalHandleAreaList.Value / 666, 2);
}
decimal? _illegalHandle2AreaList = illegalHandleAreaList
.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.area);
if (_illegalHandle2AreaList != null)
{
_illegalHandle2AreaList = Decimal.Round(_illegalHandle2AreaList.Value / 666, 2);
}
decimal? _illegalHandle01AreaList = illegalHandleAreaList
.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.area);
if (_illegalHandle01AreaList != null)
{
_illegalHandle01AreaList = Decimal.Round(_illegalHandle01AreaList.Value / 666, 2);
}
//3日内未整改
int _notComplete3 = notComplete3List.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//7日内未整改
int _notComplete7 = notComplete7List.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//30日内未整改
int _notComplete30 = notComplete30List.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//30日以上未整改
int _notComplete30More =
notComplete30MoreList.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
JObject obj = new JObject();
obj.Add("streetid", item.Id);
obj.Add("streetname", item.Name);
//分类型统计案件数量
//int _typeCount = typeCountList.Where(c => c.streetid == item.Id).Sum(c => c.count);
var typeid_fanxin = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANXIN").First()?.ItemDetailId;
var typeid_jiagai = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_JIAGAI").First()?.ItemDetailId;
var typeid_fanjian =
typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANJIAN").First()?.ItemDetailId;
var typeid_cunliang = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_CUNLIANG").First()
?.ItemDetailId;
var typeid_tuitu = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_TUITU").First()?.ItemDetailId;
List<string> notTypeids = new List<string>
{
typeid_fanxin,
typeid_jiagai,
typeid_fanjian,
typeid_cunliang,
typeid_tuitu,
};
var typeFanxinCount = typeCountList
.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_fanxin).Sum(c => c.count);
var typeJiagaiCount = typeCountList
.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_jiagai).Sum(c => c.count);
var typeFanjianCount = typeCountList
.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_fanjian).Sum(c => c.count);
var typeCunliangCount = typeCountList
.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_cunliang).Sum(c => c.count);
var typeTuituCount = typeCountList
.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_tuitu).Sum(c => c.count);
var typeOtherCount = typeCountList
.Where(c => c.streetid == item.Id.ToString() && !notTypeids.Contains(c.typeid)).Sum(c => c.count);
//上报案件数量
obj.Add("allCount", _allCount);
//完成外业核查数量
obj.Add("handleStatus", _handleStatus);
//24h内未核查
obj.Add("notDealHour24", _notDealHour24);
obj.Add("typeFanxinCount", typeFanxinCount);
obj.Add("typeJiagaiCount", typeJiagaiCount);
obj.Add("typeFanjianCount", typeFanjianCount);
obj.Add("typeCunliangCount", typeCunliangCount);
obj.Add("typeTuituCount", typeTuituCount);
obj.Add("typeOtherCount", typeOtherCount);
//合法
obj.Add("illegal0Count", _illegal0Count);
//违法 外业核实后确定违法数
obj.Add("illegal1Count", _illegal1Count);
//伪变化
obj.Add("illegal2Count", _illegal2Count);
//违法类型 未知类型数量
obj.Add("illegalNullCount", _illegalNullCount);
//违法面积
obj.Add("illegalHandleAreaList", _illegalHandleAreaList);
//整改销号数
obj.Add("illegalHandle2Status", _illegalHandle2Status);
//尚未整改宗数
obj.Add("illegalHandle01Status", _illegalHandle01Status);
//销号面积
obj.Add("illegalHandle2AreaList", _illegalHandle2AreaList);
//未整改面积
obj.Add("illegalHandle01AreaList", _illegalHandle01AreaList);
//3日内未整改完成
obj.Add("notComplete3", _notComplete3);
//7日内未整改完成
obj.Add("notComplete7", _notComplete7);
//30日内未整改完成
obj.Add("notComplete30", _notComplete30);
//30日以上未整改完成
obj.Add("notComplete30More", _notComplete30More);
array.Add(obj);
}
JObject _obj = new JObject();
_obj.Add("streetname", "合计");
//上报案件数量
_obj.Add("allCount", array.Sum(c => int.Parse(c["allCount"].ToString())));
//完成外业核查数量
_obj.Add("handleStatus", array.Sum(c => int.Parse(c["handleStatus"].ToString())));
//24h内未核查
_obj.Add("notDealHour24", array.Sum(c => int.Parse(c["notDealHour24"].ToString())));
_obj.Add("typeFanxinCount", array.Sum(c => int.Parse(c["typeFanxinCount"].ToString())));
_obj.Add("typeJiagaiCount", array.Sum(c => int.Parse(c["typeJiagaiCount"].ToString())));
_obj.Add("typeFanjianCount", array.Sum(c => int.Parse(c["typeFanjianCount"].ToString())));
_obj.Add("typeCunliangCount", array.Sum(c => int.Parse(c["typeCunliangCount"].ToString())));
_obj.Add("typeTuituCount", array.Sum(c => int.Parse(c["typeTuituCount"].ToString())));
_obj.Add("typeOtherCount", array.Sum(c => int.Parse(c["typeOtherCount"].ToString())));
//合法
_obj.Add("illegal0Count", array.Sum(c => int.Parse(c["illegal0Count"].ToString())));
//违法 外业核实后确定违法数
_obj.Add("illegal1Count", array.Sum(c => int.Parse(c["illegal1Count"].ToString())));
//伪变化
_obj.Add("illegal2Count", array.Sum(c => int.Parse(c["illegal2Count"].ToString())));
//违法类型 未知类型数量
_obj.Add("illegalNullCount", array.Sum(c => int.Parse(c["illegalNullCount"].ToString())));
//违法面积
_obj.Add("illegalHandleAreaList", array.Sum(c => decimal.Parse(c["illegalHandleAreaList"].ToString())));
//整改销号数
_obj.Add("illegalHandle2Status", array.Sum(c => int.Parse(c["illegalHandle2Status"].ToString())));
//尚未整改宗数
_obj.Add("illegalHandle01Status", array.Sum(c => int.Parse(c["illegalHandle01Status"].ToString())));
//销号面积
_obj.Add("illegalHandle2AreaList", array.Sum(c => decimal.Parse(c["illegalHandle2AreaList"].ToString())));
//未整改面积
_obj.Add("illegalHandle01AreaList", array.Sum(c => decimal.Parse(c["illegalHandle01AreaList"].ToString())));
//3日内未整改完成
_obj.Add("notComplete3", array.Sum(c => int.Parse(c["notComplete3"].ToString())));
//7日内未整改完成
_obj.Add("notComplete7", array.Sum(c => int.Parse(c["notComplete7"].ToString())));
//30日内未整改完成
_obj.Add("notComplete30", array.Sum(c => int.Parse(c["notComplete30"].ToString())));
//30日以上未整改完成
_obj.Add("notComplete30More", array.Sum(c => int.Parse(c["notComplete30More"].ToString())));
array.Add(_obj);
return new Response<JArray>
{
Result = array,
Message = "获取数据成功"
};
}
}
public async Task<Response<JArray>> CaseSynthesisCensusSingle(QueryCaseInfoListReq req)
{
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
var currentUser = _auth.GetCurrentUser();
var _user = currentUser.User;
string sql1 = "select min(\"Level\") from sys_userorg where \"UserId\"=" + _user.Id;
int level = db.Ado.GetInt(sql1);
string countyName = "";
if ((string.IsNullOrEmpty(req.countyid) && level == 0) ||
(_user.Id == -1 && string.IsNullOrEmpty(req.countyid)))
{
countyName = "临沂市";
var orgList = await db.Queryable<SysOrg>().Where(c => c.ParentName == countyName)
.OrderBy(c => c.SortNo, OrderByType.Asc).ToListAsync();
//无人机发现数量
var reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var allCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//完成核查数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var handleStatusList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//24小时未核查 (不包含已退回的部分)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_not_deal_hour24 = 1;
reqBody.is_drawback = 0;
var notDealHour24List = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//分类型统计案件数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var typeCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.typeid }).Select(c => new
{
c.countyid,
c.typeid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法类型数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var illegalCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.is_illegal }).Select(c => new
{
c.countyid,
c.is_illegal,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleStatusList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.handle_status_id }).Select(c => new
{
c.countyid,
c.handle_status_id,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态 面积
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleAreaList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.handle_status_id }).Select(c => new
{
c.countyid,
c.handle_status_id,
area = SqlFunc.AggregateSum(SqlFunc.ToDecimal(c.area)),
}).ToList();
//3日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 1;
reqBody.is_illegal = 1;
var notComplete3List = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//7日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 2;
reqBody.is_illegal = 1;
var notComplete7List = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 4;
reqBody.is_illegal = 1;
var notComplete30List = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日以上未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 5;
reqBody.is_illegal = 1;
var notComplete30MoreList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//var typeList = await db.Queryable<SysCategory>().Where(c => c.TypeId == "DRONE_CASE_TYPE").ToListAsync();
var typepid = await db.Queryable<SysDataItem>().Where(c => c.ItemCode == "DRONE_CASE_TYPE")
.FirstAsync();
var typeList = await db.Queryable<SysDataItemDetail>().Where(c => c.ItemId == typepid.ItemId)
.ToListAsync();
JArray array = new JArray();
for (int i = 0; i < orgList.Count; i++)
{
var item = orgList[i];
//无人机发现数量
int _allCount = allCountList.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//完成核查数量
int _handleStatus = handleStatusList.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//24小时未核查
int _notDealHour24 = notDealHour24List.Where(c => c.countyid == item.Id.ToString())
.Sum(c => c.count);
//违法类型数量
int _illegal0Count = illegalCountList
.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == 0).Sum(c => c.count);
int _illegal1Count = illegalCountList
.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == 1).Sum(c => c.count);
int _illegal2Count = illegalCountList
.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == 2).Sum(c => c.count);
int _illegalNullCount = illegalCountList
.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == null).Sum(c => c.count);
//违法 状态查询
int _illegalHandle2Status = illegalHandleStatusList
.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.count);
int _illegalHandle01Status = illegalHandleStatusList
.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.count);
//违法 面积查询
decimal? _illegalHandleAreaList = illegalHandleAreaList.Where(c => c.countyid == item.Id.ToString())
.Sum(c => c.area);
if (_illegalHandleAreaList != null)
{
_illegalHandleAreaList = Decimal.Round(_illegalHandleAreaList.Value / 666, 2);
}
decimal? _illegalHandle2AreaList = illegalHandleAreaList
.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.area);
if (_illegalHandle2AreaList != null)
{
_illegalHandle2AreaList = Decimal.Round(_illegalHandle2AreaList.Value / 666, 2);
}
decimal? _illegalHandle01AreaList = illegalHandleAreaList
.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.area);
if (_illegalHandle01AreaList != null)
{
_illegalHandle01AreaList = Decimal.Round(_illegalHandle01AreaList.Value / 666, 2);
}
//3日内未整改
int _notComplete3 = notComplete3List.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//7日内未整改
int _notComplete7 = notComplete7List.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//30日内未整改
int _notComplete30 = notComplete30List.Where(c => c.countyid == item.Id.ToString())
.Sum(c => c.count);
//30日以上未整改
int _notComplete30More = notComplete30MoreList.Where(c => c.countyid == item.Id.ToString())
.Sum(c => c.count);
JObject obj = new JObject();
obj.Add("streetid", item.Id);
obj.Add("streetname", item.Name);
//分类型统计案件数量
//int _typeCount = typeCountList.Where(c => c.streetid == item.Id).Sum(c => c.count);
var typeid_fanxin = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANXIN").First()
?.ItemDetailId;
var typeid_jiagai = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_JIAGAI").First()
?.ItemDetailId;
var typeid_fanjian = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANJIAN").First()
?.ItemDetailId;
var typeid_cunliang = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_CUNLIANG").First()
?.ItemDetailId;
var typeid_tuitu = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_TUITU").First()
?.ItemDetailId;
List<string> notTypeids = new List<string>
{
typeid_fanxin,
typeid_jiagai,
typeid_fanjian,
typeid_cunliang,
typeid_tuitu,
};
var typeFanxinCount = typeCountList
.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_fanxin).Sum(c => c.count);
var typeJiagaiCount = typeCountList
.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_jiagai).Sum(c => c.count);
var typeFanjianCount = typeCountList
.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_fanjian).Sum(c => c.count);
var typeCunliangCount = typeCountList
.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_cunliang).Sum(c => c.count);
var typeTuituCount = typeCountList
.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_tuitu).Sum(c => c.count);
var typeOtherCount = typeCountList
.Where(c => c.countyid == item.Id.ToString() && !notTypeids.Contains(c.typeid))
.Sum(c => c.count);
//上报案件数量
obj.Add("allCount", _allCount);
//完成外业核查数量
obj.Add("handleStatus", _handleStatus);
//24h内未核查
obj.Add("notDealHour24", _notDealHour24);
obj.Add("typeFanxinCount", typeFanxinCount);
obj.Add("typeJiagaiCount", typeJiagaiCount);
obj.Add("typeFanjianCount", typeFanjianCount);
obj.Add("typeCunliangCount", typeCunliangCount);
obj.Add("typeTuituCount", typeTuituCount);
obj.Add("typeOtherCount", typeOtherCount);
//合法
obj.Add("illegal0Count", _illegal0Count);
//违法 外业核实后确定违法数
obj.Add("illegal1Count", _illegal1Count);
//伪变化
obj.Add("illegal2Count", _illegal2Count);
//违法类型 未知类型数量
obj.Add("illegalNullCount", _illegalNullCount);
//违法面积
obj.Add("illegalHandleAreaList", _illegalHandleAreaList);
//整改销号数
obj.Add("illegalHandle2Status", _illegalHandle2Status);
//尚未整改宗数
obj.Add("illegalHandle01Status", _illegalHandle01Status);
//销号面积
obj.Add("illegalHandle2AreaList", _illegalHandle2AreaList);
//未整改面积
obj.Add("illegalHandle01AreaList", _illegalHandle01AreaList);
//3日内未整改完成
obj.Add("notComplete3", _notComplete3);
//7日内未整改完成
obj.Add("notComplete7", _notComplete7);
//30日内未整改完成
obj.Add("notComplete30", _notComplete30);
//30日以上未整改完成
obj.Add("notComplete30More", _notComplete30More);
array.Add(obj);
}
JObject _obj = new JObject();
_obj.Add("streetname", "合计");
//上报案件数量
_obj.Add("allCount", array.Sum(c => int.Parse(c["allCount"].ToString())));
//完成外业核查数量
_obj.Add("handleStatus", array.Sum(c => int.Parse(c["handleStatus"].ToString())));
//24h内未核查
_obj.Add("notDealHour24", array.Sum(c => int.Parse(c["notDealHour24"].ToString())));
_obj.Add("typeFanxinCount", array.Sum(c => int.Parse(c["typeFanxinCount"].ToString())));
_obj.Add("typeJiagaiCount", array.Sum(c => int.Parse(c["typeJiagaiCount"].ToString())));
_obj.Add("typeFanjianCount", array.Sum(c => int.Parse(c["typeFanjianCount"].ToString())));
_obj.Add("typeCunliangCount", array.Sum(c => int.Parse(c["typeCunliangCount"].ToString())));
_obj.Add("typeTuituCount", array.Sum(c => int.Parse(c["typeTuituCount"].ToString())));
_obj.Add("typeOtherCount", array.Sum(c => int.Parse(c["typeOtherCount"].ToString())));
//合法
_obj.Add("illegal0Count", array.Sum(c => int.Parse(c["illegal0Count"].ToString())));
//违法 外业核实后确定违法数
_obj.Add("illegal1Count", array.Sum(c => int.Parse(c["illegal1Count"].ToString())));
//伪变化
_obj.Add("illegal2Count", array.Sum(c => int.Parse(c["illegal2Count"].ToString())));
//违法类型 未知类型数量
_obj.Add("illegalNullCount", array.Sum(c => int.Parse(c["illegalNullCount"].ToString())));
//违法面积
_obj.Add("illegalHandleAreaList", array.Sum(c => decimal.Parse(c["illegalHandleAreaList"].ToString())));
//整改销号数
_obj.Add("illegalHandle2Status", array.Sum(c => int.Parse(c["illegalHandle2Status"].ToString())));
//尚未整改宗数
_obj.Add("illegalHandle01Status", array.Sum(c => int.Parse(c["illegalHandle01Status"].ToString())));
//销号面积
_obj.Add("illegalHandle2AreaList",
array.Sum(c => decimal.Parse(c["illegalHandle2AreaList"].ToString())));
//未整改面积
_obj.Add("illegalHandle01AreaList",
array.Sum(c => decimal.Parse(c["illegalHandle01AreaList"].ToString())));
//3日内未整改完成
_obj.Add("notComplete3", array.Sum(c => int.Parse(c["notComplete3"].ToString())));
//7日内未整改完成
_obj.Add("notComplete7", array.Sum(c => int.Parse(c["notComplete7"].ToString())));
//30日内未整改完成
_obj.Add("notComplete30", array.Sum(c => int.Parse(c["notComplete30"].ToString())));
//30日以上未整改完成
_obj.Add("notComplete30More", array.Sum(c => int.Parse(c["notComplete30More"].ToString())));
array.Add(_obj);
return new Response<JArray>
{
Result = array,
Message = "获取数据成功"
};
}
else if (string.IsNullOrEmpty(req.countyid) && level == 1)
{
string sql2 = "select \"OrgId\" from sys_userorg where \"Level\"=1 and \"UserId\"=" + _user.Id;
var liststring = db.Ado.SqlQuery<long>(sql2);
var orgList = await db.Queryable<SysOrg>().Where(c => liststring.Contains(c.Id))
.OrderBy(c => c.SortNo, OrderByType.Asc).ToListAsync();
//无人机发现数量
var reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var allCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//完成核查数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var handleStatusList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//24小时未核查 (不包含已退回的部分)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_not_deal_hour24 = 1;
reqBody.is_drawback = 0;
var notDealHour24List = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//分类型统计案件数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var typeCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.typeid }).Select(c => new
{
c.countyid,
c.typeid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法类型数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var illegalCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.is_illegal }).Select(c => new
{
c.countyid,
c.is_illegal,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleStatusList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.handle_status_id }).Select(c => new
{
c.countyid,
c.handle_status_id,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态 面积
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleAreaList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.handle_status_id }).Select(c => new
{
c.countyid,
c.handle_status_id,
area = SqlFunc.AggregateSum(SqlFunc.ToDecimal(c.area)),
}).ToList();
//3日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 1;
reqBody.is_illegal = 1;
var notComplete3List = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//7日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 2;
reqBody.is_illegal = 1;
var notComplete7List = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 4;
reqBody.is_illegal = 1;
var notComplete30List = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日以上未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 5;
reqBody.is_illegal = 1;
var notComplete30MoreList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//var typeList = await db.Queryable<SysCategory>().Where(c => c.TypeId == "DRONE_CASE_TYPE").ToListAsync();
var typepid = await db.Queryable<SysDataItem>().Where(c => c.ItemCode == "DRONE_CASE_TYPE")
.FirstAsync();
var typeList = await db.Queryable<SysDataItemDetail>().Where(c => c.ItemId == typepid.ItemId)
.ToListAsync();
JArray array = new JArray();
for (int i = 0; i < orgList.Count; i++)
{
var item = orgList[i];
//无人机发现数量
int _allCount = allCountList.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//完成核查数量
int _handleStatus = handleStatusList.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//24小时未核查
int _notDealHour24 = notDealHour24List.Where(c => c.countyid == item.Id.ToString())
.Sum(c => c.count);
//违法类型数量
int _illegal0Count = illegalCountList
.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == 0).Sum(c => c.count);
int _illegal1Count = illegalCountList
.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == 1).Sum(c => c.count);
int _illegal2Count = illegalCountList
.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == 2).Sum(c => c.count);
int _illegalNullCount = illegalCountList
.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == null).Sum(c => c.count);
//违法 状态查询
int _illegalHandle2Status = illegalHandleStatusList
.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.count);
int _illegalHandle01Status = illegalHandleStatusList
.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.count);
//违法 面积查询
decimal? _illegalHandleAreaList = illegalHandleAreaList.Where(c => c.countyid == item.Id.ToString())
.Sum(c => c.area);
if (_illegalHandleAreaList != null)
{
_illegalHandleAreaList = Decimal.Round(_illegalHandleAreaList.Value / 666, 2);
}
decimal? _illegalHandle2AreaList = illegalHandleAreaList
.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.area);
if (_illegalHandle2AreaList != null)
{
_illegalHandle2AreaList = Decimal.Round(_illegalHandle2AreaList.Value / 666, 2);
}
decimal? _illegalHandle01AreaList = illegalHandleAreaList
.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.area);
if (_illegalHandle01AreaList != null)
{
_illegalHandle01AreaList = Decimal.Round(_illegalHandle01AreaList.Value / 666, 2);
}
//3日内未整改
int _notComplete3 = notComplete3List.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//7日内未整改
int _notComplete7 = notComplete7List.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//30日内未整改
int _notComplete30 = notComplete30List.Where(c => c.countyid == item.Id.ToString())
.Sum(c => c.count);
//30日以上未整改
int _notComplete30More = notComplete30MoreList.Where(c => c.countyid == item.Id.ToString())
.Sum(c => c.count);
JObject obj = new JObject();
obj.Add("streetid", item.Id);
obj.Add("streetname", item.Name);
//分类型统计案件数量
//int _typeCount = typeCountList.Where(c => c.streetid == item.Id).Sum(c => c.count);
var typeid_fanxin = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANXIN").First()
?.ItemDetailId;
var typeid_jiagai = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_JIAGAI").First()
?.ItemDetailId;
var typeid_fanjian = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANJIAN").First()
?.ItemDetailId;
var typeid_cunliang = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_CUNLIANG").First()
?.ItemDetailId;
var typeid_tuitu = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_TUITU").First()
?.ItemDetailId;
List<string> notTypeids = new List<string>
{
typeid_fanxin,
typeid_jiagai,
typeid_fanjian,
typeid_cunliang,
typeid_tuitu,
};
var typeFanxinCount = typeCountList
.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_fanxin).Sum(c => c.count);
var typeJiagaiCount = typeCountList
.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_jiagai).Sum(c => c.count);
var typeFanjianCount = typeCountList
.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_fanjian).Sum(c => c.count);
var typeCunliangCount = typeCountList
.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_cunliang).Sum(c => c.count);
var typeTuituCount = typeCountList
.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_tuitu).Sum(c => c.count);
var typeOtherCount = typeCountList
.Where(c => c.countyid == item.Id.ToString() && !notTypeids.Contains(c.typeid))
.Sum(c => c.count);
//上报案件数量
obj.Add("allCount", _allCount);
//完成外业核查数量
obj.Add("handleStatus", _handleStatus);
//24h内未核查
obj.Add("notDealHour24", _notDealHour24);
obj.Add("typeFanxinCount", typeFanxinCount);
obj.Add("typeJiagaiCount", typeJiagaiCount);
obj.Add("typeFanjianCount", typeFanjianCount);
obj.Add("typeCunliangCount", typeCunliangCount);
obj.Add("typeTuituCount", typeTuituCount);
obj.Add("typeOtherCount", typeOtherCount);
//合法
obj.Add("illegal0Count", _illegal0Count);
//违法 外业核实后确定违法数
obj.Add("illegal1Count", _illegal1Count);
//伪变化
obj.Add("illegal2Count", _illegal2Count);
//违法类型 未知类型数量
obj.Add("illegalNullCount", _illegalNullCount);
//违法面积
obj.Add("illegalHandleAreaList", _illegalHandleAreaList);
//整改销号数
obj.Add("illegalHandle2Status", _illegalHandle2Status);
//尚未整改宗数
obj.Add("illegalHandle01Status", _illegalHandle01Status);
//销号面积
obj.Add("illegalHandle2AreaList", _illegalHandle2AreaList);
//未整改面积
obj.Add("illegalHandle01AreaList", _illegalHandle01AreaList);
//3日内未整改完成
obj.Add("notComplete3", _notComplete3);
//7日内未整改完成
obj.Add("notComplete7", _notComplete7);
//30日内未整改完成
obj.Add("notComplete30", _notComplete30);
//30日以上未整改完成
obj.Add("notComplete30More", _notComplete30More);
array.Add(obj);
}
JObject _obj = new JObject();
_obj.Add("streetname", "合计");
//上报案件数量
_obj.Add("allCount", array.Sum(c => int.Parse(c["allCount"].ToString())));
//完成外业核查数量
_obj.Add("handleStatus", array.Sum(c => int.Parse(c["handleStatus"].ToString())));
//24h内未核查
_obj.Add("notDealHour24", array.Sum(c => int.Parse(c["notDealHour24"].ToString())));
_obj.Add("typeFanxinCount", array.Sum(c => int.Parse(c["typeFanxinCount"].ToString())));
_obj.Add("typeJiagaiCount", array.Sum(c => int.Parse(c["typeJiagaiCount"].ToString())));
_obj.Add("typeFanjianCount", array.Sum(c => int.Parse(c["typeFanjianCount"].ToString())));
_obj.Add("typeCunliangCount", array.Sum(c => int.Parse(c["typeCunliangCount"].ToString())));
_obj.Add("typeTuituCount", array.Sum(c => int.Parse(c["typeTuituCount"].ToString())));
_obj.Add("typeOtherCount", array.Sum(c => int.Parse(c["typeOtherCount"].ToString())));
//合法
_obj.Add("illegal0Count", array.Sum(c => int.Parse(c["illegal0Count"].ToString())));
//违法 外业核实后确定违法数
_obj.Add("illegal1Count", array.Sum(c => int.Parse(c["illegal1Count"].ToString())));
//伪变化
_obj.Add("illegal2Count", array.Sum(c => int.Parse(c["illegal2Count"].ToString())));
//违法类型 未知类型数量
_obj.Add("illegalNullCount", array.Sum(c => int.Parse(c["illegalNullCount"].ToString())));
//违法面积
_obj.Add("illegalHandleAreaList", array.Sum(c => decimal.Parse(c["illegalHandleAreaList"].ToString())));
//整改销号数
_obj.Add("illegalHandle2Status", array.Sum(c => int.Parse(c["illegalHandle2Status"].ToString())));
//尚未整改宗数
_obj.Add("illegalHandle01Status", array.Sum(c => int.Parse(c["illegalHandle01Status"].ToString())));
//销号面积
_obj.Add("illegalHandle2AreaList",
array.Sum(c => decimal.Parse(c["illegalHandle2AreaList"].ToString())));
//未整改面积
_obj.Add("illegalHandle01AreaList",
array.Sum(c => decimal.Parse(c["illegalHandle01AreaList"].ToString())));
//3日内未整改完成
_obj.Add("notComplete3", array.Sum(c => int.Parse(c["notComplete3"].ToString())));
//7日内未整改完成
_obj.Add("notComplete7", array.Sum(c => int.Parse(c["notComplete7"].ToString())));
//30日内未整改完成
_obj.Add("notComplete30", array.Sum(c => int.Parse(c["notComplete30"].ToString())));
//30日以上未整改完成
_obj.Add("notComplete30More", array.Sum(c => int.Parse(c["notComplete30More"].ToString())));
array.Add(_obj);
return new Response<JArray>
{
Result = array,
Message = "获取数据成功"
};
}
else
{
if (level > 1)
{
JArray array1 = new JArray();
JObject _obj1 = new JObject();
_obj1.Add("streetname", "合计");
//上报案件数量
_obj1.Add("allCount", 0);
//完成外业核查数量
_obj1.Add("handleStatus", 0);
//24h内未核查
_obj1.Add("notDealHour24", 0);
_obj1.Add("typeFanxinCount", 0);
_obj1.Add("typeJiagaiCount", 0);
_obj1.Add("typeFanjianCount", 0);
_obj1.Add("typeCunliangCount", 0);
_obj1.Add("typeTuituCount", 0);
_obj1.Add("typeOtherCount", 0);
//合法
_obj1.Add("illegal0Count", 0);
//违法 外业核实后确定违法数
_obj1.Add("illegal1Count", 0);
//伪变化
_obj1.Add("illegal2Count", 0);
//违法类型 未知类型数量
_obj1.Add("illegalNullCount", 0);
//违法面积
_obj1.Add("illegalHandleAreaList", 0);
//整改销号数
_obj1.Add("illegalHandle2Status", 0);
//尚未整改宗数
_obj1.Add("illegalHandle01Status", 0);
//销号面积
_obj1.Add("illegalHandle2AreaList", 0);
//未整改面积
_obj1.Add("illegalHandle01AreaList", 0);
//3日内未整改完成
_obj1.Add("notComplete3", 0);
//7日内未整改完成
_obj1.Add("notComplete7", 0);
//30日内未整改完成
_obj1.Add("notComplete30", 0);
//30日以上未整改完成
_obj1.Add("notComplete30More", 0);
array1.Add(_obj1);
return new Response<JArray>
{
Result = array1,
Message = "获取数据成功"
};
}
string sql = "select \"Name\" from sys_org where \"Id\"=" + req.countyid;
string name = db.Ado.GetString(sql);
var orgList = await db.Queryable<SysOrg>().Where(c => c.ParentName == name)
.OrderBy(c => c.SortNo, OrderByType.Asc).ToListAsync();
//无人机发现数量
var reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var allCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//完成核查数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var handleStatusList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//24小时未核查 (不包含已退回的部分)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_not_deal_hour24 = 1;
reqBody.is_drawback = 0;
var notDealHour24List = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//分类型统计案件数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var typeCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.typeid }).Select(c => new
{
c.streetid,
c.typeid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法类型数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var illegalCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.is_illegal }).Select(c => new
{
c.streetid,
c.is_illegal,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleStatusList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.handle_status_id }).Select(c => new
{
c.streetid,
c.handle_status_id,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态 面积
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleAreaList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.handle_status_id }).Select(c => new
{
c.streetid,
c.handle_status_id,
area = SqlFunc.AggregateSum(SqlFunc.ToDecimal(c.area)),
}).ToList();
//3日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 1;
reqBody.is_illegal = 1;
var notComplete3List = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//7日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 2;
reqBody.is_illegal = 1;
var notComplete7List = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 4;
reqBody.is_illegal = 1;
var notComplete30List = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日以上未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 5;
reqBody.is_illegal = 1;
var notComplete30MoreList = QueryCaseInfoSingleList(db, reqBody, _user, 0)
.GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//var typeList = await db.Queryable<SysCategory>().Where(c => c.TypeId == "DRONE_CASE_TYPE").ToListAsync();
var typepid = await db.Queryable<SysDataItem>().Where(c => c.ItemCode == "DRONE_CASE_TYPE")
.FirstAsync();
var typeList = await db.Queryable<SysDataItemDetail>().Where(c => c.ItemId == typepid.ItemId)
.ToListAsync();
JArray array = new JArray();
for (int i = 0; i < orgList.Count; i++)
{
var item = orgList[i];
//无人机发现数量
int _allCount = allCountList.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//完成核查数量
int _handleStatus = handleStatusList.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//24小时未核查
int _notDealHour24 = notDealHour24List.Where(c => c.streetid == item.Id.ToString())
.Sum(c => c.count);
//违法类型数量
int _illegal0Count = illegalCountList
.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == 0).Sum(c => c.count);
int _illegal1Count = illegalCountList
.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == 1).Sum(c => c.count);
int _illegal2Count = illegalCountList
.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == 2).Sum(c => c.count);
int _illegalNullCount = illegalCountList
.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == null).Sum(c => c.count);
//违法 状态查询
int _illegalHandle2Status = illegalHandleStatusList
.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.count);
int _illegalHandle01Status = illegalHandleStatusList
.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.count);
//违法 面积查询
decimal? _illegalHandleAreaList = illegalHandleAreaList.Where(c => c.streetid == item.Id.ToString())
.Sum(c => c.area);
if (_illegalHandleAreaList != null)
{
_illegalHandleAreaList = Decimal.Round(_illegalHandleAreaList.Value / 666, 2);
}
decimal? _illegalHandle2AreaList = illegalHandleAreaList
.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.area);
if (_illegalHandle2AreaList != null)
{
_illegalHandle2AreaList = Decimal.Round(_illegalHandle2AreaList.Value / 666, 2);
}
decimal? _illegalHandle01AreaList = illegalHandleAreaList
.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.area);
if (_illegalHandle01AreaList != null)
{
_illegalHandle01AreaList = Decimal.Round(_illegalHandle01AreaList.Value / 666, 2);
}
//3日内未整改
int _notComplete3 = notComplete3List.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//7日内未整改
int _notComplete7 = notComplete7List.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//30日内未整改
int _notComplete30 = notComplete30List.Where(c => c.streetid == item.Id.ToString())
.Sum(c => c.count);
//30日以上未整改
int _notComplete30More = notComplete30MoreList.Where(c => c.streetid == item.Id.ToString())
.Sum(c => c.count);
JObject obj = new JObject();
obj.Add("streetid", item.Id);
obj.Add("streetname", item.Name);
//分类型统计案件数量
//int _typeCount = typeCountList.Where(c => c.streetid == item.Id).Sum(c => c.count);
var typeid_fanxin = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANXIN").First()
?.ItemDetailId;
var typeid_jiagai = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_JIAGAI").First()
?.ItemDetailId;
var typeid_fanjian = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANJIAN").First()
?.ItemDetailId;
var typeid_cunliang = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_CUNLIANG").First()
?.ItemDetailId;
var typeid_tuitu = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_TUITU").First()
?.ItemDetailId;
List<string> notTypeids = new List<string>
{
typeid_fanxin,
typeid_jiagai,
typeid_fanjian,
typeid_cunliang,
typeid_tuitu,
};
var typeFanxinCount = typeCountList
.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_fanxin).Sum(c => c.count);
var typeJiagaiCount = typeCountList
.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_jiagai).Sum(c => c.count);
var typeFanjianCount = typeCountList
.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_fanjian).Sum(c => c.count);
var typeCunliangCount = typeCountList
.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_cunliang).Sum(c => c.count);
var typeTuituCount = typeCountList
.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_tuitu).Sum(c => c.count);
var typeOtherCount = typeCountList
.Where(c => c.streetid == item.Id.ToString() && !notTypeids.Contains(c.typeid))
.Sum(c => c.count);
//上报案件数量
obj.Add("allCount", _allCount);
//完成外业核查数量
obj.Add("handleStatus", _handleStatus);
//24h内未核查
obj.Add("notDealHour24", _notDealHour24);
obj.Add("typeFanxinCount", typeFanxinCount);
obj.Add("typeJiagaiCount", typeJiagaiCount);
obj.Add("typeFanjianCount", typeFanjianCount);
obj.Add("typeCunliangCount", typeCunliangCount);
obj.Add("typeTuituCount", typeTuituCount);
obj.Add("typeOtherCount", typeOtherCount);
//合法
obj.Add("illegal0Count", _illegal0Count);
//违法 外业核实后确定违法数
obj.Add("illegal1Count", _illegal1Count);
//伪变化
obj.Add("illegal2Count", _illegal2Count);
//违法类型 未知类型数量
obj.Add("illegalNullCount", _illegalNullCount);
//违法面积
obj.Add("illegalHandleAreaList", _illegalHandleAreaList);
//整改销号数
obj.Add("illegalHandle2Status", _illegalHandle2Status);
//尚未整改宗数
obj.Add("illegalHandle01Status", _illegalHandle01Status);
//销号面积
obj.Add("illegalHandle2AreaList", _illegalHandle2AreaList);
//未整改面积
obj.Add("illegalHandle01AreaList", _illegalHandle01AreaList);
//3日内未整改完成
obj.Add("notComplete3", _notComplete3);
//7日内未整改完成
obj.Add("notComplete7", _notComplete7);
//30日内未整改完成
obj.Add("notComplete30", _notComplete30);
//30日以上未整改完成
obj.Add("notComplete30More", _notComplete30More);
array.Add(obj);
}
JObject _obj = new JObject();
_obj.Add("streetname", "合计");
//上报案件数量
_obj.Add("allCount", array.Sum(c => int.Parse(c["allCount"].ToString())));
//完成外业核查数量
_obj.Add("handleStatus", array.Sum(c => int.Parse(c["handleStatus"].ToString())));
//24h内未核查
_obj.Add("notDealHour24", array.Sum(c => int.Parse(c["notDealHour24"].ToString())));
_obj.Add("typeFanxinCount", array.Sum(c => int.Parse(c["typeFanxinCount"].ToString())));
_obj.Add("typeJiagaiCount", array.Sum(c => int.Parse(c["typeJiagaiCount"].ToString())));
_obj.Add("typeFanjianCount", array.Sum(c => int.Parse(c["typeFanjianCount"].ToString())));
_obj.Add("typeCunliangCount", array.Sum(c => int.Parse(c["typeCunliangCount"].ToString())));
_obj.Add("typeTuituCount", array.Sum(c => int.Parse(c["typeTuituCount"].ToString())));
_obj.Add("typeOtherCount", array.Sum(c => int.Parse(c["typeOtherCount"].ToString())));
//合法
_obj.Add("illegal0Count", array.Sum(c => int.Parse(c["illegal0Count"].ToString())));
//违法 外业核实后确定违法数
_obj.Add("illegal1Count", array.Sum(c => int.Parse(c["illegal1Count"].ToString())));
//伪变化
_obj.Add("illegal2Count", array.Sum(c => int.Parse(c["illegal2Count"].ToString())));
//违法类型 未知类型数量
_obj.Add("illegalNullCount", array.Sum(c => int.Parse(c["illegalNullCount"].ToString())));
//违法面积
_obj.Add("illegalHandleAreaList", array.Sum(c => decimal.Parse(c["illegalHandleAreaList"].ToString())));
//整改销号数
_obj.Add("illegalHandle2Status", array.Sum(c => int.Parse(c["illegalHandle2Status"].ToString())));
//尚未整改宗数
_obj.Add("illegalHandle01Status", array.Sum(c => int.Parse(c["illegalHandle01Status"].ToString())));
//销号面积
_obj.Add("illegalHandle2AreaList",
array.Sum(c => decimal.Parse(c["illegalHandle2AreaList"].ToString())));
//未整改面积
_obj.Add("illegalHandle01AreaList",
array.Sum(c => decimal.Parse(c["illegalHandle01AreaList"].ToString())));
//3日内未整改完成
_obj.Add("notComplete3", array.Sum(c => int.Parse(c["notComplete3"].ToString())));
//7日内未整改完成
_obj.Add("notComplete7", array.Sum(c => int.Parse(c["notComplete7"].ToString())));
//30日内未整改完成
_obj.Add("notComplete30", array.Sum(c => int.Parse(c["notComplete30"].ToString())));
//30日以上未整改完成
_obj.Add("notComplete30More", array.Sum(c => int.Parse(c["notComplete30More"].ToString())));
array.Add(_obj);
return new Response<JArray>
{
Result = array,
Message = "获取数据成功"
};
}
}
}
public Response<MemoryStream> ListToExcel(JArray list, List<ModuleColumn> headers)
{
Response<MemoryStream> response = new Response<MemoryStream>();
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
#region 内容样式
IFont font1 = workbook.CreateFont(); //创建一个字体样式对象
font1.FontName = "Microsoft YaHei"; //和excel里面的字体对应
//font1.Boldweight = short.MaxValue;//字体加粗
font1.FontHeightInPoints = 12; //字体大小
ICellStyle style = workbook.CreateCellStyle(); //创建样式对象
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.SetFont(font1); //将字体样式赋给样式对象
style.WrapText = true;
#endregion
#region 标题样式
IFont font = workbook.CreateFont(); //创建一个字体样式对象
font.FontName = "Microsoft YaHei"; //和excel里面的字体对应
font.Boldweight = (short)FontBoldWeight.Bold; //字体加粗
font.FontHeightInPoints = 12; //字体大小
ICellStyle style1 = workbook.CreateCellStyle(); //创建样式对象
style1.BorderBottom = BorderStyle.Thin;
style1.BorderLeft = BorderStyle.Thin;
style1.BorderRight = BorderStyle.Thin;
style1.BorderTop = BorderStyle.Thin;
style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style1.VerticalAlignment = VerticalAlignment.Center;
style1.SetFont(font); //将字体样式赋给样式对象
#endregion
#region 创建表头
int m = list.Count() / 60000 + 1;
for (int k = 0; k < m; k++)
{
ISheet sheet = workbook.CreateSheet("Sheet" + k.ToString());
IRow rowHeader = sheet.CreateRow(0);
rowHeader.Height = 20 * 30;
for (int i = 0; i < headers.Count; i++)
{
ModuleColumn header = headers[i];
rowHeader.CreateCell(i);
rowHeader.Cells[i].CellStyle = style1;
rowHeader.Cells[i].SetCellValue(header.value);
sheet.SetColumnWidth(i, 20 * 350);
}
#endregion
#region 填充数据
var val = (k + 1) * 60000;
var num = 60000;
if (val > list.Count())
{
num = list.Count() - k * 60000;
}
for (int i = 0; i < num; i++) //循环数据
{
JObject item = (JObject)list[k * 60000 + i]; //获取数据
IRow dataRow = sheet.CreateRow(i + 1); //创建行
for (int j = 0; j < headers.Count; j++) //循环表头
{
//数据处理
var objValue = "";
if (item.ContainsKey(headers[j].key))
{
objValue = item[headers[j].key].ToString();
}
else
{
objValue = "";
}
//创建单元格
dataRow.CreateCell(j);
dataRow.Cells[j].CellStyle = style; //添加单元格样式
if (objValue != null && !string.IsNullOrEmpty(objValue.ToString()))
{
dataRow.Cells[j].SetCellValue(objValue.ToString()); //填充Excel单元格
}
else
{
dataRow.Cells[j].SetCellValue(""); //填充Excel单元格
}
}
}
}
#endregion
response.Result = new MemoryStream();
workbook.Write(response.Result);
workbook = null;
response.Result.Close();
response.Result.Dispose();
response.Code = 200;
response.Message = "获取成功";
}
catch (Exception ex)
{
response.Code = 500;
response.Message = ex.Message;
}
return response;
}
/// <summary>
/// 综合统计，统计镇权限
/// </summary>
/// <returns></returns>
public JArray CaseSynthesisCensusStreet(QueryCaseInfoListReq req)
{
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
//当前账号的权限
var currentUser = _auth.GetCurrentUser();
var _user = currentUser.User;
var currentOrgs = currentUser.Orgs.Select(c => c.Id).ToList();
//当前县名称
string countyName = _configuration.GetSection("CountyName").Value;
if (string.IsNullOrEmpty(countyName)) countyName = "费县";
//查询系统中镇的数据
var streetList = db.Queryable<SysOrg>().Where(c => c.ParentName == countyName).ToList();
//当前用户所属的镇
var currentStreet = streetList.Where(c => currentOrgs.Contains(c.Id)).FirstOrDefault();
if (currentStreet == null)
throw new Exception("没有权限");
JArray array = new JArray();
if (currentStreet.Name == "费城街道")
{
// 获取工作区案件统计的数据
// array = GetSynthesisCensusWorkAreaData(db, currentStreet, _user, req);
}
else
{
// 获取街道案件统计数据
array = GetSynthesisCensusStreetData(db, currentStreet, _user, req);
}
return array;
}
}
/// <summary>
/// 获取街道案件统计数据
/// </summary>
/// <param name="db"></param>
/// <param name="currentStreet"></param>
/// <param name="_user"></param>
/// <param name="req"></param>
/// <returns></returns>
private JArray GetSynthesisCensusStreetData(SqlSugarClient db, SysOrg currentStreet, SysUser _user,
QueryCaseInfoListReq req)
{
var orgList = db.Queryable<SysOrg>().Where(c => c.ParentId == currentStreet.Id)
.OrderBy(c => c.SortNo, OrderByType.Asc).ToList();
//无人机发现数量
var reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.streetid = currentStreet.Id.ToString();
var allCountList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid }).Select(c => new
{
c.communityid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//完成核查数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var handleStatusList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid }).Select(c =>
new
{
c.communityid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//24小时未核查 (不包含已退回的部分)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_not_deal_hour24 = 1;
reqBody.is_drawback = 0;
reqBody.streetid = currentStreet.Id.ToString();
var notDealHour24List = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid }).Select(c =>
new
{
c.communityid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//分类型统计案件数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.streetid = currentStreet.Id.ToString();
var typeCountList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid, c.typeid })
.Select(c => new
{
c.communityid,
c.typeid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法类型数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var illegalCountList = QueryCaseInfoList(db, reqBody, _user, 0)
.GroupBy(c => new { c.communityid, c.is_illegal }).Select(c => new
{
c.communityid,
c.is_illegal,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var illegalHandleStatusList = QueryCaseInfoList(db, reqBody, _user, 0)
.GroupBy(c => new { c.communityid, c.handle_status_id }).Select(c => new
{
c.communityid,
c.handle_status_id,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态 面积
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var illegalHandleAreaList = QueryCaseInfoList(db, reqBody, _user, 0)
.GroupBy(c => new { c.communityid, c.handle_status_id }).Select(c => new
{
c.communityid,
c.handle_status_id,
area = SqlFunc.AggregateSum(SqlFunc.ToDecimal(c.area)),
}).ToList();
//3日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 1;
reqBody.is_illegal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var notComplete3List = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid }).Select(c =>
new
{
c.communityid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//7日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 2;
reqBody.is_illegal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var notComplete7List = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid }).Select(c =>
new
{
c.communityid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 4;
reqBody.is_illegal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var notComplete30List = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid }).Select(c =>
new
{
c.communityid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日以上未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 5;
reqBody.is_illegal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var notComplete30MoreList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid }).Select(
c => new
{
c.communityid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
var typepid = db.Queryable<SysDataItem>().Where(c => c.ItemCode == "DRONE_CASE_TYPE").First();
var typeList = db.Queryable<SysDataItemDetail>().Where(c => c.ItemId == typepid.ItemId).ToList();
//var typeList = db.Queryable<Sugar_Category>().Where(c => c.TypeId == "DRONE_CASE_TYPE").ToList();
JArray array = new JArray();
for (int i = 0; i < orgList.Count; i++)
{
var item = orgList[i];
//无人机发现数量
int _allCount = allCountList.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.count);
//完成核查数量
int _handleStatus = handleStatusList.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.count);
//24小时未核查
int _notDealHour24 = notDealHour24List.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.count);
//违法类型数量
int _illegal0Count = illegalCountList.Where(c => c.communityid == item.Id.ToString() && c.is_illegal == 0)
.Sum(c => c.count);
int _illegal1Count = illegalCountList.Where(c => c.communityid == item.Id.ToString() && c.is_illegal == 1)
.Sum(c => c.count);
int _illegal2Count = illegalCountList.Where(c => c.communityid == item.Id.ToString() && c.is_illegal == 2)
.Sum(c => c.count);
int _illegalNullCount = illegalCountList
.Where(c => c.communityid == item.Id.ToString() && c.is_illegal == null).Sum(c => c.count);
//违法 状态查询
int _illegalHandle2Status = illegalHandleStatusList
.Where(c => c.communityid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.count);
int _illegalHandle01Status = illegalHandleStatusList
.Where(c => c.communityid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.count);
//违法 面积查询
decimal? _illegalHandleAreaList =
illegalHandleAreaList.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.area);
if (_illegalHandleAreaList != null)
{
_illegalHandleAreaList = Decimal.Round(_illegalHandleAreaList.Value / 666, 2);
}
decimal? _illegalHandle2AreaList = illegalHandleAreaList
.Where(c => c.communityid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.area);
if (_illegalHandle2AreaList != null)
{
_illegalHandle2AreaList = Decimal.Round(_illegalHandle2AreaList.Value / 666, 2);
}
decimal? _illegalHandle01AreaList = illegalHandleAreaList
.Where(c => c.communityid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.area);
if (_illegalHandle01AreaList != null)
{
_illegalHandle01AreaList = Decimal.Round(_illegalHandle01AreaList.Value / 666, 2);
}
//3日内未整改
int _notComplete3 = notComplete3List.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.count);
//7日内未整改
int _notComplete7 = notComplete7List.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.count);
//30日内未整改
int _notComplete30 = notComplete30List.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.count);
//30日以上未整改
int _notComplete30More =
notComplete30MoreList.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.count);
JObject obj = new JObject();
//所属镇
obj.Add("streetid", currentStreet.Id);
obj.Add("streetname", currentStreet.Name);
//所属社区
obj.Add("communityid", item.Id);
obj.Add("communityname", item.Name);
obj.Add("is_area", 0);
//分类型统计案件数量
//int _typeCount = typeCountList.Where(c => c.streetid == item.Id).Sum(c => c.count);
var typeid_fanxin = typeList.Where(c => c.ItemCode == "DRONE_CASE_TYPE_FANXIN").First().ItemDetailId;
var typeid_jiagai = typeList.Where(c => c.ItemCode == "DRONE_CASE_TYPE_JIAGAI").First().ItemDetailId;
var typeid_fanjian = typeList.Where(c => c.ItemCode == "DRONE_CASE_TYPE_FANJIAN").First().ItemDetailId;
var typeid_cunliang = typeList.Where(c => c.ItemCode == "DRONE_CASE_TYPE_CUNLIANG").First().ItemDetailId;
var typeid_tuitu = typeList.Where(c => c.ItemCode == "DRONE_CASE_TYPE_TUITU").First().ItemDetailId;
List<string> notTypeids = new List<string>
{
typeid_fanxin,
typeid_jiagai,
typeid_fanjian,
typeid_cunliang,
typeid_tuitu,
};
var typeFanxinCount = typeCountList
.Where(c => c.communityid == item.Id.ToString() && c.typeid == typeid_fanxin).Sum(c => c.count);
var typeJiagaiCount = typeCountList
.Where(c => c.communityid == item.Id.ToString() && c.typeid == typeid_jiagai).Sum(c => c.count);
var typeFanjianCount = typeCountList
.Where(c => c.communityid == item.Id.ToString() && c.typeid == typeid_fanjian).Sum(c => c.count);
var typeCunliangCount = typeCountList
.Where(c => c.communityid == item.Id.ToString() && c.typeid == typeid_cunliang).Sum(c => c.count);
var typeTuituCount = typeCountList
.Where(c => c.communityid == item.Id.ToString() && c.typeid == typeid_tuitu).Sum(c => c.count);
var typeOtherCount = typeCountList
.Where(c => c.communityid == item.Id.ToString() && !notTypeids.Contains(c.typeid)).Sum(c => c.count);
//上报案件数量
obj.Add("allCount", _allCount);
//完成外业核查数量
obj.Add("handleStatus", _handleStatus);
//24h内未核查
obj.Add("notDealHour24", _notDealHour24);
obj.Add("typeFanxinCount", typeFanxinCount);
obj.Add("typeJiagaiCount", typeJiagaiCount);
obj.Add("typeFanjianCount", typeFanjianCount);
obj.Add("typeCunliangCount", typeCunliangCount);
obj.Add("typeTuituCount", typeTuituCount);
obj.Add("typeOtherCount", typeOtherCount);
//合法
obj.Add("illegal0Count", _illegal0Count);
//违法 外业核实后确定违法数
obj.Add("illegal1Count", _illegal1Count);
//伪变化
obj.Add("illegal2Count", _illegal2Count);
//违法类型 未知类型数量
obj.Add("illegalNullCount", _illegalNullCount);
//违法面积
obj.Add("illegalHandleAreaList", _illegalHandleAreaList);
//整改销号数
obj.Add("illegalHandle2Status", _illegalHandle2Status);
//尚未整改宗数
obj.Add("illegalHandle01Status", _illegalHandle01Status);
//销号面积
obj.Add("illegalHandle2AreaList", _illegalHandle2AreaList);
//未整改面积
obj.Add("illegalHandle01AreaList", _illegalHandle01AreaList);
//3日内未整改完成
obj.Add("notComplete3", _notComplete3);
//7日内未整改完成
obj.Add("notComplete7", _notComplete7);
//30日内未整改完成
obj.Add("notComplete30", _notComplete30);
//30日以上未整改完成
obj.Add("notComplete30More", _notComplete30More);
array.Add(obj);
}
JObject _obj = new JObject();
//所属镇
_obj.Add("streetid", currentStreet.Id);
_obj.Add("streetname", currentStreet.Name);
_obj.Add("communityid", "");
_obj.Add("communityname", "合计");
_obj.Add("is_area", 0);
//上报案件数量
_obj.Add("allCount", array.Sum(c => int.Parse(c["allCount"].ToString())));
//完成外业核查数量
_obj.Add("handleStatus", array.Sum(c => int.Parse(c["handleStatus"].ToString())));
//24h内未核查
_obj.Add("notDealHour24", array.Sum(c => int.Parse(c["notDealHour24"].ToString())));
_obj.Add("typeFanxinCount", array.Sum(c => int.Parse(c["typeFanxinCount"].ToString())));
_obj.Add("typeJiagaiCount", array.Sum(c => int.Parse(c["typeJiagaiCount"].ToString())));
_obj.Add("typeFanjianCount", array.Sum(c => int.Parse(c["typeFanjianCount"].ToString())));
_obj.Add("typeCunliangCount", array.Sum(c => int.Parse(c["typeCunliangCount"].ToString())));
_obj.Add("typeTuituCount", array.Sum(c => int.Parse(c["typeTuituCount"].ToString())));
_obj.Add("typeOtherCount", array.Sum(c => int.Parse(c["typeOtherCount"].ToString())));
//合法
_obj.Add("illegal0Count", array.Sum(c => int.Parse(c["illegal0Count"].ToString())));
//违法 外业核实后确定违法数
_obj.Add("illegal1Count", array.Sum(c => int.Parse(c["illegal1Count"].ToString())));
//伪变化
_obj.Add("illegal2Count", array.Sum(c => int.Parse(c["illegal2Count"].ToString())));
//违法类型 未知类型数量
_obj.Add("illegalNullCount", array.Sum(c => int.Parse(c["illegalNullCount"].ToString())));
//违法面积
_obj.Add("illegalHandleAreaList", array.Sum(c => decimal.Parse(c["illegalHandleAreaList"].ToString())));
//整改销号数
_obj.Add("illegalHandle2Status", array.Sum(c => int.Parse(c["illegalHandle2Status"].ToString())));
//尚未整改宗数
_obj.Add("illegalHandle01Status", array.Sum(c => int.Parse(c["illegalHandle01Status"].ToString())));
//销号面积
_obj.Add("illegalHandle2AreaList", array.Sum(c => decimal.Parse(c["illegalHandle2AreaList"].ToString())));
//未整改面积
_obj.Add("illegalHandle01AreaList", array.Sum(c => decimal.Parse(c["illegalHandle01AreaList"].ToString())));
//3日内未整改完成
_obj.Add("notComplete3", array.Sum(c => int.Parse(c["notComplete3"].ToString())));
//7日内未整改完成
_obj.Add("notComplete7", array.Sum(c => int.Parse(c["notComplete7"].ToString())));
//30日内未整改完成
_obj.Add("notComplete30", array.Sum(c => int.Parse(c["notComplete30"].ToString())));
//30日以上未整改完成
_obj.Add("notComplete30More", array.Sum(c => int.Parse(c["notComplete30More"].ToString())));
array.Add(_obj);
return array;
}
#endregion
#region 无人机发现违法行为情况统计明细表
public Response<List<OffenceResp>> CaseOffence(DateTime startTime, DateTime endTime, string tubanlaiyuan)
{
//合法：已经归档的合法案件 is_illegal
//违法：已经归档的违法案件，未归档的案件 is_illegal
//其他：已经归档的其他案件 is_illegal
//非粮化：已经归档的非粮化 weifaleixing 并且is_illegal=1 非农化也是
//补办手续：已经归档的补办手续 measure_name并且is_illegal=1 weifaleixing =0
//拆除拆除复耕：已经归档的拆除 measure_name并且is_illegal=1 weifaleixing =0
//核实后： 已归档非农 + 所有未归档的 + 耕地大于0
//整改后剩余新增违法耕地： 所有未归档的 + 耕地大于0
var user = _auth.GetCurrentUser().User;
var orgIds = base._auth.GetCurrentUser().Orgs;
List<OffenceResp> listResp = new List<OffenceResp>();
//只查询农用地案件
//var casetype = client.Queryable<SysDataItemDetail>()
// .Where(r => r.ItemCode == "DRONE_CASE_TYPE" && r.ItemValue == "DRONE_CASE_TYPE_NYD").First()?.ItemDetailId;
//总计
int xftbzscount = 0,
hfzscount = 0,
wfzscount = 0,
fnhzscount = 0,
flhzscount = 0,
qtzscount = 0,
bbsxzscount = 0,
cczscount = 0,
bzgdtbzscount = 0,
hshxzwfgdzscount = 0,
zghsywfgdzscount = 0;
double xfgdmjsum = 0.0,
xfmjsum = 0.0,
hfmjsum = 0.0,
hfgdmjsum = 0.0,
wfmjsum = 0.0,
wfgdmjsum = 0.0,
qtmjsum = 0.0,
qtgdmjsum = 0.0,
fnhmjsum = 0.0,
fnhgdmjsum = 0.0,
flhmjsum = 0.0,
flhgdmjsum = 0.0,
bbsxmjsum = 0.0,
bbsxgdmjsum = 0.0,
ccmjsum = 0.0,
ccgdmjsum = 0.0,
bzgdmjsum = 0.0,
hshxzwfgdgdmjsum = 0.0,
zghsywfgdgdmjsum = 0.0;
double SumArea(IEnumerable<DroneCaseInfoSingle> cases, Func<DroneCaseInfoSingle, string> selector)
{
var sum = cases
.Select(selector)
.Where(area => !string.IsNullOrEmpty(area))
.Sum(area => double.TryParse(area, out var result) ? result : 0);
return Math.Round(sum, 2); // 保留两位小数
}
List<SysOrg> org = new List<SysOrg>();
List<DroneCaseInfoSingle> caseList = new List<DroneCaseInfoSingle>();
List<string> tubanlaiyuanList = string.IsNullOrEmpty(tubanlaiyuan)
? new List<string>()
: tubanlaiyuan.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
if (orgIds.Any(r => r.Level == 0) || user.Id == -1) // 查询全部
{
org = client.Queryable<SysOrg>().Where(r => r.Level == 1).ToList();
caseList = client.Queryable<DroneCaseInfoSingle>()
.LeftJoin<WFProcess>((a, b) => SqlFunc.JsonField(b.InstanceInfo, "pkeyValue") == a.Id)
.Where((a, b) => a.synchronoustime >= startTime && a.synchronoustime <= endTime &&
a.typename == "农用地" && a.is_closed == 0)
.WhereIF(!string.IsNullOrEmpty(tubanlaiyuan), (a, b) => tubanlaiyuanList.Contains(a.tubanlaiyuan))
.Where((a, b) => !string.IsNullOrEmpty(b.Id)).ToList();
}
else if (orgIds.Any(r => r.Level == 1))
{
//县区数据
org = client.Queryable<SysOrg>()
.Where(r => r.Level == 2 && orgIds.Select(a => a.Id).Contains(r.ParentId)).ToList();
caseList = client.Queryable<DroneCaseInfoSingle>()
.LeftJoin<WFProcess>((a, b) => SqlFunc.JsonField(b.InstanceInfo, "pkeyValue") == a.Id)
.WhereIF(!string.IsNullOrEmpty(tubanlaiyuan), (a, b) => tubanlaiyuanList.Contains(a.tubanlaiyuan))
.Where((a, b) => a.synchronoustime >= startTime && a.synchronoustime <= endTime &&
a.typename == "农用地" && a.is_closed == 0)
.Where((a, b) => !string.IsNullOrEmpty(b.Id)).ToList();
}
foreach (var item in org)
{
var countyIdString = item.Id.ToString();
var casesForCounty = caseList.Where(r => r.countyid == countyIdString || r.streetid == countyIdString)
.ToList();
xftbzscount += casesForCounty.Count;
int wfzs = casesForCounty.Count(r =>
(r.is_illegal == 1 && r.handle_status_id == 5) || r.handle_status_id != 5);
wfzscount += wfzs;
int fnhzs = casesForCounty.Count(r =>
(r.weifaleixing == 0 && r.is_illegal == 1 && r.handle_status_id == 5) || r.handle_status_id != 5);
fnhzscount += fnhzs;
int flhzs = casesForCounty.Count(r => r.weifaleixing == 1 && r.handle_status_id == 5 && r.is_illegal == 1);
flhzscount += flhzs;
int hfzs = casesForCounty.Count(r => r.is_illegal == 0 && r.handle_status_id == 5);
hfzscount += hfzs;
int qtzs = casesForCounty.Count(r => r.is_illegal == 2 && r.handle_status_id == 5);
qtzscount += qtzs;
int bbsxzs = casesForCounty.Count(r =>
r.measure_name == "1" && r.handle_status_id == 5 && r.weifaleixing == 0 && r.is_illegal == 1);
bbsxzscount += bbsxzs;
int cczs = casesForCounty.Count(r =>
r.measure_name == "0" && r.handle_status_id == 5 && r.weifaleixing == 0 && r.is_illegal == 1);
cczscount += cczs;
int bzgdtbzs = casesForCounty.Count(r =>
(string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00")
&& r.handle_status_id == 5 && r.is_illegal == 1 && r.weifaleixing == 0);
bzgdtbzscount += bzgdtbzs;
//核实后新增违法耕地 核实后： 已归档非农 + 所有未归档的 + 耕地大于0
var hshxzwfgdzs = casesForCounty.Count(r =>
(!(string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00") &&
r.handle_status_id != 5) ||
(!(string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00") &&
r.handle_status_id == 5 && r.weifaleixing == 0 && r.is_illegal == 1));
hshxzwfgdzscount += hshxzwfgdzs;
//整改后剩余新增违法耕地： 所有未归档的 + 耕地大于0
var zghsywfgdzs = casesForCounty.Count(r =>
(!(string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00")) &&
r.handle_status_id != 5);
zghsywfgdzscount += zghsywfgdzs;
double xfgdmj = SumArea(casesForCounty, r => r.gengdi_area);
xfgdmjsum += xfgdmj;
double xfmj = SumArea(casesForCounty, r => r.area);
xfmjsum += xfmj;
double hfmj = SumArea(casesForCounty.Where(r => r.is_illegal == 0 && r.handle_status_id == 5),
r => r.area);
hfmjsum += hfmj;
double hfgdmj = SumArea(casesForCounty.Where(r => r.is_illegal == 0 && r.handle_status_id == 5),
r => r.gengdi_area);
hfgdmjsum += hfgdmj;
double wfmj = SumArea(
casesForCounty.Where(r => (r.is_illegal == 1 && r.handle_status_id == 5) || r.handle_status_id != 5),
r => r.area);
wfmjsum += wfmj;
double wfgdmj = SumArea(
casesForCounty.Where(r => (r.is_illegal == 1 && r.handle_status_id == 5) || r.handle_status_id != 5),
r => r.gengdi_area);
wfgdmjsum += wfgdmj;
double qtmj = SumArea(casesForCounty.Where(r => r.is_illegal == 2 && r.handle_status_id == 5),
r => r.area);
qtmjsum += qtmj;
double qtgdmj = SumArea(casesForCounty.Where(r => r.is_illegal == 2 && r.handle_status_id == 5),
r => r.gengdi_area);
qtgdmjsum += qtgdmj;
double fnhmj = SumArea(
casesForCounty.Where(r =>
(r.weifaleixing == 0 && r.is_illegal == 1 && r.handle_status_id == 5) || r.handle_status_id != 5),
r => r.area);
fnhmjsum += fnhmj;
double fnhgdmj = SumArea(
casesForCounty.Where(r =>
(r.weifaleixing == 0 && r.is_illegal == 1 && r.handle_status_id == 5) || r.handle_status_id != 5),
r => r.gengdi_area);
fnhgdmjsum += fnhgdmj;
double flhmj = SumArea(
casesForCounty.Where(r => r.weifaleixing == 1 && r.handle_status_id == 5 && r.is_illegal == 1),
r => r.area);
flhmjsum += flhmj;
double flhgdmj = SumArea(
casesForCounty.Where(r => r.weifaleixing == 1 && r.handle_status_id == 5 && r.is_illegal == 1),
r => r.gengdi_area);
flhgdmjsum += flhgdmj;
double bbsxmj = SumArea(
casesForCounty.Where(r =>
r.measure_name == "1" && r.handle_status_id == 5 && r.weifaleixing == 0 && r.is_illegal == 1),
r => r.area);
bbsxmjsum += bbsxmj;
double bbsxgdmj = SumArea(
casesForCounty.Where(r =>
r.measure_name == "1" && r.handle_status_id == 5 && r.weifaleixing == 0 && r.is_illegal == 1),
r => r.gengdi_area);
bbsxgdmjsum += bbsxgdmj;
double ccmj = SumArea(
casesForCounty.Where(r =>
r.measure_name == "0" && r.handle_status_id == 5 && r.weifaleixing == 0 && r.is_illegal == 1),
r => r.area);
ccmjsum += ccmj;
double ccgdmj = SumArea(
casesForCounty.Where(r =>
r.measure_name == "0" && r.handle_status_id == 5 && r.weifaleixing == 0 && r.is_illegal == 1),
r => r.gengdi_area);
ccgdmjsum += ccgdmj;
//不占耕地
double bzgdmj =
SumArea(casesForCounty.Where(r =>
(string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00")
&& r.handle_status_id == 5 && r.is_illegal == 1 && r.weifaleixing == 0),
r => r.gengdi_area);
bzgdmjsum += bzgdmj;
//核实后新增违法耕地
var hshxzwfgdgdmj = SumArea(casesForCounty.Where(r =>
(!(string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00") &&
r.handle_status_id != 5) ||
(!(string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00") &&
r.handle_status_id == 5 && r.weifaleixing == 0 && r.is_illegal == 1)),
r => r.gengdi_area);
hshxzwfgdgdmjsum += hshxzwfgdgdmj;
//整改后剩余新增违法耕地
var zghsywfgdgdmj = SumArea(casesForCounty.Where(r =>
(!(string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00")) &&
r.handle_status_id != 5),
r => r.gengdi_area);
zghsywfgdgdmjsum += zghsywfgdgdmj;
OffenceResp offenceResp = new OffenceResp
{
countyid = item.Id,
countyname = item.Name,
xftbzs = casesForCounty.Count,
xfgdmj = xfgdmj,
xfmj = xfmj,
hfzs = hfzs,
hfmj = hfmj,
hfgdmj = hfgdmj,
wfzs = wfzs,
wfmj = wfmj,
wfgdmj = wfgdmj,
qtzs = qtzs,
qtmj = qtmj,
qtgdmj = qtgdmj,
fnhzs = fnhzs,
fnhmj = fnhmj,
fnhgdmj = fnhgdmj,
flhzs = flhzs,
flhmj = flhmj,
flhgdmj = flhgdmj,
bbsxzs = bbsxzs,
bbsxmj = bbsxmj,
bbsxgdmj = bbsxgdmj,
cczs = cczs,
ccmj = ccmj,
ccgdmj = ccgdmj,
bzgdtbzs = bzgdtbzs,
//hshxzwfgdzs = (wfzs - flhzs - bzgdtbzs),
//hshxzwfgdgdmj = wfgdmj - flhgdmj,
hshxzwfgdzs = hshxzwfgdzs,
hshxzwfgdgdmj = hshxzwfgdgdmj,
//zghsywfgdzs = wfzs - flhzs - bbsxzs - cczs,
zghsywfgdzs = zghsywfgdzs,
zghsywfgdgdmj = zghsywfgdgdmj
//zghsywfgdgdmj = wfgdmj - flhgdmj - bbsxgdmj - ccgdmj
};
listResp.Add(offenceResp);
}
listResp = listResp.OrderByDescending(r => r.zghsywfgdgdmj).ToList();
//hshxzwfgdzscount = (wfzscount - flhzscount - bzgdtbzscount);
//hshxzwfgdgdmjsum = wfgdmjsum - flhgdmjsum;
//zghsywfgdzscount = wfzscount - flhzscount - bbsxzscount - cczscount;
//zghsywfgdgdmjsum = wfgdmjsum - flhgdmjsum - bbsxgdmjsum - ccgdmjsum;
listResp.Add(new OffenceResp
{
countyid = 0,
countyname = "总计",
xftbzs = xftbzscount,
xfgdmj = xfgdmjsum,
xfmj = xfmjsum,
hfzs = hfzscount,
hfmj = hfmjsum,
hfgdmj = hfgdmjsum,
wfzs = wfzscount,
wfmj = wfmjsum,
wfgdmj = wfgdmjsum,
qtzs = qtzscount,
qtmj = qtmjsum,
qtgdmj = qtgdmjsum,
fnhzs = fnhzscount,
fnhmj = fnhmjsum,
fnhgdmj = fnhgdmjsum,
flhzs = flhzscount,
flhmj = flhmjsum,
flhgdmj = flhgdmjsum,
bbsxzs = bbsxzscount,
bbsxmj = bbsxmjsum,
bbsxgdmj = bbsxgdmjsum,
cczs = cczscount,
ccmj = ccmjsum,
ccgdmj = ccgdmjsum,
bzgdtbzs = bzgdtbzscount,
hshxzwfgdzs = hshxzwfgdzscount,
hshxzwfgdgdmj = Math.Round(hshxzwfgdgdmjsum, 2),
zghsywfgdzs = zghsywfgdzscount,
zghsywfgdgdmj = Math.Round(zghsywfgdgdmjsum, 2)
});
return new Response<List<OffenceResp>>
{
Result = listResp,
Message = "获取数据成功"
};
}
/// <summary>
/// 无人机全域巡查图斑整改情况统计表
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public Response<List<OffenceResp>> CaseOffenceForExcel(DateTime startTime, DateTime endTime, string tubanlaiyuan)
{
//核实后新增违法耕地 ：占耕地的未归档图斑+占耕地的已归档的非农化类型图斑
//整改后剩余违法耕地 ：占耕地的未归档的图斑
var user = _auth.GetCurrentUser().User;
var orgIds = base._auth.GetCurrentUser().Orgs;
List<OffenceResp> listResp = new List<OffenceResp>();
//只查询农用地案件
//var casetype = client.Queryable<SysDataItemDetail>()
// .Where(r => r.ItemCode == "DRONE_CASE_TYPE" && r.ItemValue == "DRONE_CASE_TYPE_NYD").First()?.ItemDetailId;
//总计
int xftbzscount = 0,
hshxzwfgdzscount = 0,
zghsywfgdzscount = 0;
double xfgdmjsum = 0.0,
hshxzwfgdgdmjsum = 0.0,
zghsywfgdgdmjsum = 0.0;
double SumArea(IEnumerable<DroneCaseInfoSingle> cases, Func<DroneCaseInfoSingle, string> selector)
{
var sum = cases
.Select(selector)
.Where(area => !string.IsNullOrEmpty(area))
.Sum(area => double.TryParse(area, out var result) ? result : 0);
return Math.Round(sum, 2); // 保留两位小数
}
List<SysOrg> org = new List<SysOrg>();
List<DroneCaseInfoSingle> caseList = new List<DroneCaseInfoSingle>();
var tubanlaiyuanList =
string.IsNullOrEmpty(tubanlaiyuan) ? new List<string>() : tubanlaiyuan.Split(",").ToList();
if (orgIds.Any(r => r.Level == 0) || user.Id == -1) // 查询全部
{
org = client.Queryable<SysOrg>().Where(r => r.Level == 1).ToList();
caseList = client.Queryable<DroneCaseInfoSingle>()
.LeftJoin<WFProcess>((a, b) => SqlFunc.JsonField(b.InstanceInfo, "pkeyValue") == a.Id)
.WhereIF(!string.IsNullOrEmpty(tubanlaiyuan), (a, b) => tubanlaiyuanList.Contains(a.tubanlaiyuan))
.Where((a, b) => a.synchronoustime >= startTime && a.synchronoustime <= endTime &&
a.typename == "农用地" && a.is_closed == 0)
.Where((a, b) => !string.IsNullOrEmpty(b.Id)).ToList();
}
else if (orgIds.Any(r => r.Level == 1))
{
//县区数据
org = client.Queryable<SysOrg>()
.Where(r => r.Level == 2 && orgIds.Select(a => a.Id).Contains(r.ParentId)).ToList();
caseList = client.Queryable<DroneCaseInfoSingle>()
.LeftJoin<WFProcess>((a, b) => SqlFunc.JsonField(b.InstanceInfo, "pkeyValue") == a.Id)
.WhereIF(!string.IsNullOrEmpty(tubanlaiyuan), (a, b) => tubanlaiyuanList.Contains(a.tubanlaiyuan))
.Where((a, b) => a.synchronoustime >= startTime && a.synchronoustime <= endTime &&
a.typename == "农用地" && a.is_closed == 0)
.Where((a, b) => !string.IsNullOrEmpty(b.Id)).ToList();
}
foreach (var item in org)
{
var countyIdString = item.Id.ToString();
var casesForCounty = caseList.Where(r => r.countyid == countyIdString || r.streetid == countyIdString)
.ToList();
//下发占耕地的图斑
var xftbzs = casesForCounty.Count(r =>
!(string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00"));
xftbzscount += xftbzs;
double xfgdmj = SumArea(casesForCounty, r => r.gengdi_area);
xfgdmjsum += xfgdmj;
//核实后新增违法耕地 ：占耕地的未归档图斑+占耕地的已归档的非农化类型图斑
var hshxzwfgdzs = casesForCounty.Count(r =>
(!(string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00") &&
r.handle_status_id != 5) ||
(!(string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00") &&
r.handle_status_id == 5 && r.weifaleixing == 0 && r.is_illegal == 1));
hshxzwfgdzscount += hshxzwfgdzs;
var hshxzwfgdgdmj = SumArea(casesForCounty.Where(r =>
(!(string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00") &&
r.handle_status_id != 5) ||
(!(string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00") &&
r.handle_status_id == 5 && r.weifaleixing == 0 && r.is_illegal == 1)), r => r.gengdi_area);
hshxzwfgdgdmjsum += hshxzwfgdgdmj;
//整改后剩余违法耕地 ：占耕地的未归档的图斑
var zghsywfgdzs = casesForCounty.Count(r =>
(!(string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00")) &&
r.handle_status_id != 5);
zghsywfgdzscount += zghsywfgdzs;
var zghsywfgdgdmj =
SumArea(
casesForCounty.Where(r =>
(!(string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00")) &&
r.handle_status_id != 5), r => r.gengdi_area);
zghsywfgdgdmjsum += zghsywfgdgdmj;
OffenceResp offenceResp = new OffenceResp
{
countyid = item.Id,
countyname = item.Name,
xftbzs = xftbzs,
xfgdmj = xfgdmj,
hshxzwfgdzs = hshxzwfgdzs,
hshxzwfgdgdmj = hshxzwfgdgdmj,
zghsywfgdzs = zghsywfgdzs,
zghsywfgdgdmj = zghsywfgdgdmj
};
listResp.Add(offenceResp);
}
listResp = listResp.OrderByDescending(r => r.zghsywfgdgdmj).ToList();
listResp.Add(new OffenceResp
{
countyid = 0,
countyname = "总计",
xftbzs = xftbzscount,
xfgdmj = xfgdmjsum,
hshxzwfgdzs = hshxzwfgdzscount,
hshxzwfgdgdmj = Math.Round(hshxzwfgdgdmjsum, 2),
zghsywfgdzs = zghsywfgdzscount,
zghsywfgdgdmj = Math.Round(zghsywfgdgdmjsum, 2)
});
return new Response<List<OffenceResp>>
{
Result = listResp,
Message = "获取数据成功"
};
}
/// <summary>
/// 无人机全域巡查图斑下发情况统计表
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public Response<List<OffenceTBResp>> CaseOffencetbForExcel(DateTime startTime, DateTime endTime,
string tubanlaiyuan)
{
var user = _auth.GetCurrentUser().User;
var orgIds = base._auth.GetCurrentUser().Orgs;
List<OffenceTBResp> listResp = new List<OffenceTBResp>();
double SumArea(IEnumerable<DroneCaseInfoSingle> cases, Func<DroneCaseInfoSingle, string> selector)
{
var sum = cases
.Select(selector)
.Where(area => !string.IsNullOrEmpty(area))
.Sum(area => double.TryParse(area, out var result) ? result : 0);
return Math.Round(sum, 2); // 保留两位小数
}
List<SysOrg> org = new List<SysOrg>();
List<DroneCaseInfoSingle> caseList = new List<DroneCaseInfoSingle>();
var tubanlaiyuanList =
string.IsNullOrEmpty(tubanlaiyuan) ? new List<string>() : tubanlaiyuan.Split(",").ToList();
if (orgIds.Any(r => r.Level == 0) || user.Id == -1) // 查询全部
{
org = client.Queryable<SysOrg>().Where(r => r.Level == 1).ToList();
caseList = client.Queryable<DroneCaseInfoSingle>()
.LeftJoin<WFProcess>((a, b) => SqlFunc.JsonField(b.InstanceInfo, "pkeyValue") == a.Id)
.Where((a, b) => a.synchronoustime >= startTime && a.synchronoustime <= endTime && a.is_closed == 0)
.WhereIF(!string.IsNullOrEmpty(tubanlaiyuan), (a, b) => tubanlaiyuanList.Contains(a.tubanlaiyuan))
.Where((a, b) => !string.IsNullOrEmpty(b.Id)).ToList();
}
else if (orgIds.Any(r => r.Level == 1))
{
//县区数据
org = client.Queryable<SysOrg>()
.Where(r => r.Level == 2 && orgIds.Select(a => a.Id).Contains(r.ParentId)).ToList();
caseList = client.Queryable<DroneCaseInfoSingle>()
.LeftJoin<WFProcess>((a, b) => SqlFunc.JsonField(b.InstanceInfo, "pkeyValue") == a.Id)
.WhereIF(!string.IsNullOrEmpty(tubanlaiyuan), (a, b) => tubanlaiyuanList.Contains(a.tubanlaiyuan))
.Where((a, b) => a.synchronoustime >= startTime && a.synchronoustime <= endTime && a.is_closed == 0)
.Where((a, b) => !string.IsNullOrEmpty(b.Id)).ToList();
}
foreach (var item in org)
{
var countyIdString = item.Id.ToString();
var casesForCounty = caseList.Where(r => r.countyid == countyIdString || r.streetid == countyIdString)
.ToList();
//建设用地
var jsydtbzs = casesForCounty.Count(r => r.typename == "建设用地");
double jsydmj = SumArea(casesForCounty.Where(r => r.typename == "建设用地"), r => r.area);
//农用地
var nydtbzs = casesForCounty.Count(r => r.typename == "农用地");
double nydmj = SumArea(casesForCounty.Where(r => r.typename == "农用地"), r => r.area);
double nydgdmj = SumArea(casesForCounty.Where(r => r.typename == "农用地"), r => r.gengdi_area);
//推堆土
var tdttbzs = casesForCounty.Count(r => r.typename == "推堆土");
double tdtmj = SumArea(casesForCounty.Where(r => r.typename == "推堆土"), r => r.area);
double tdtgdmj = SumArea(casesForCounty.Where(r => r.typename == "推堆土"), r => r.gengdi_area);
//总计
var zjttbzs = jsydtbzs + nydtbzs + tdttbzs;
double zjmj = jsydmj + nydmj + tdtmj;
double zjgdmj = nydgdmj + tdtgdmj;
OffenceTBResp offenceResp = new OffenceTBResp
{
countyid = item.Id,
countyname = item.Name,
jsydtbzs = jsydtbzs,
jsydmj = Math.Round(jsydmj, 2),
nydtbzs = nydtbzs,
nydmj = Math.Round(nydmj, 2),
nydgdmj = Math.Round(nydgdmj, 2),
tdttbzs = tdttbzs,
tdtmj = Math.Round(tdtmj, 2),
tdtgdmj = Math.Round(tdtgdmj, 2),
zjttbzs = zjttbzs,
zjmj = Math.Round(zjmj, 2),
zjgdmj = Math.Round(zjgdmj, 2),
};
listResp.Add(offenceResp);
}
listResp = listResp.OrderByDescending(r => r.zjttbzs).ToList();
return new Response<List<OffenceTBResp>>
{
Result = listResp,
Message = "获取数据成功"
};
}
public async Task<Response<PageInfo<List<DroneCaseInfoSingle>>>> LoadCaseInfoListOffence(QueryCaseOffencetReq req)
{
var user = _auth.GetCurrentUser().User;
var orgIds = base._auth.GetCurrentUser().Orgs;
RefAsync<int> totalcount = 0;
//// 只查询农用地案件
//var casetype = client.Queryable<SysDataItemDetail>()
// .Where(r => r.ItemCode == "DRONE_CASE_TYPE" && r.ItemValue == "DRONE_CASE_TYPE_NYD")
// .Select(r => r.ItemDetailId)
// .First();
// 查询案件列表的基础查询
List<string> tubanlaiyuanList = string.IsNullOrEmpty(req.tubanlaiyuan)
? new List<string>()
: req.tubanlaiyuan.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
var baseQuery = client.Queryable<DroneCaseInfoSingle>()
.LeftJoin<WFProcess>((a, b) => b.Id == a.Id)
.WhereIF(!string.IsNullOrEmpty(req.tubanlaiyuan), (a, b) => tubanlaiyuanList.Contains(a.tubanlaiyuan))
.Where((a, b) => a.synchronoustime >= req.StartTime && a.synchronoustime <= req.EndTime)
.Where((a, b) => a.typename == "农用地" && a.is_closed == 0)
.Where((a, b) => !string.IsNullOrEmpty(b.Id));
// 根据用户角色和区域进行过滤
if (orgIds.Any(r => r.Level == 0) || user.Id == -1)
{
var orgIds_c = client.Queryable<SysOrg>().Where(r => r.Level == 1).Select(r => r.Id.ToString()).ToList();
baseQuery = req.AreaId == "0"
? baseQuery.Where(a => orgIds_c.Contains(a.countyid))
: baseQuery.Where(a => a.countyid == req.AreaId);
}
else if (orgIds.Any(r => r.Level == 1))
{
//var orgIds_c = orgIds.Where(r => r.Level == 2).Select(r=>r.Id.ToString()).ToList();
var orgIds_c = client.Queryable<SysOrg>()
.Where(r => r.Level == 2 && orgIds.Select(a => a.Id).Contains(r.ParentId)).Select(r => r.Id.ToString())
.ToList();
baseQuery = req.AreaId == "0"
? baseQuery.Where(a => orgIds_c.Contains(a.streetid))
: baseQuery.Where(a => a.streetid == req.AreaId);
}
else
{
return new Response<PageInfo<List<DroneCaseInfoSingle>>>
{
Result = new PageInfo<List<DroneCaseInfoSingle>>(),
Message = "暂无数据"
};
}
// 根据案件类型进行过滤和排序
switch (req.CaseType)
{
case 2:
baseQuery = baseQuery.Where(a => a.is_illegal == 0 && a.handle_status_id == 5);
break;
case 3:
baseQuery = baseQuery.Where(a =>
(a.is_illegal == 1 && a.handle_status_id == 5) || a.handle_status_id != 5);
break;
case 4:
baseQuery = baseQuery.Where(a => a.is_illegal == 2 && a.handle_status_id == 5);
break;
case 5:
baseQuery = baseQuery.Where(a => a.weifaleixing == 1 && a.handle_status_id == 5 && a.is_illegal == 1);
break;
case 6:
baseQuery = baseQuery.Where(a =>
a.measure_name == "1" && a.handle_status_id == 5 && a.is_illegal == 1 && a.weifaleixing == 0);
break;
case 7:
baseQuery = baseQuery.Where(a =>
a.measure_name == "0" && a.handle_status_id == 5 && a.is_illegal == 1 && a.weifaleixing == 0);
break;
case 8:
//baseQuery = baseQuery.Where(a =>
// ((a.is_illegal == 1 && a.handle_status_id == 5) || a.handle_status_id != 5) &&
// !(a.weifaleixing == 1 && a.handle_status_id == 5 && a.is_illegal == 1) &&
// !((string.IsNullOrEmpty(a.gengdi_area) || a.gengdi_area == "0" || a.gengdi_area == "0.00") &&
// a.handle_status_id == 5
// && (a.handle_status_id != 5 || (a.is_illegal == 1 && a.handle_status_id == 5))));
baseQuery = baseQuery.Where(a =>
(!(string.IsNullOrEmpty(a.gengdi_area) || a.gengdi_area == "0" || a.gengdi_area == "0.00") &&
a.handle_status_id != 5) ||
(a.handle_status_id == 5 && a.weifaleixing == 0 && a.is_illegal == 1 &&
!(string.IsNullOrEmpty(a.gengdi_area) || a.gengdi_area == "0" || a.gengdi_area == "0.00")));
break;
case 9:
//baseQuery = baseQuery.Where(a =>
// ((a.is_illegal == 1 && a.handle_status_id == 5) || a.handle_status_id != 5) &&
// !(a.weifaleixing == 1 && a.handle_status_id == 5 && a.is_illegal == 1) &&
// !((string.IsNullOrEmpty(a.gengdi_area) || a.gengdi_area == "0" || a.gengdi_area == "0.00") &&
// a.handle_status_id == 5 &&
// (a.handle_status_id != 5 || (a.is_illegal == 1 && a.handle_status_id == 5))) &&
// !(a.measure_name == "1" && a.handle_status_id == 5 && a.weifaleixing == 0) &&
// !(a.measure_name == "0" && a.handle_status_id == 5 && a.weifaleixing == 0));
baseQuery = baseQuery.Where(a =>
(!(string.IsNullOrEmpty(a.gengdi_area) || a.gengdi_area == "0" || a.gengdi_area == "0.00")) &&
a.handle_status_id != 5);
break;
case 10:
//(r.weifaleixing == 0 && r.is_illegal == 1 && r.handle_status_id == 5) || r.handle_status_id != 5)
baseQuery = baseQuery.Where(a =>
(a.weifaleixing == 0 && a.is_illegal == 1 && a.handle_status_id == 5) || a.handle_status_id != 5);
break;
default:
break;
}
baseQuery = baseQuery.OrderByDescending(a => a.synchronoustime);
// 获取分页结果
var result = await baseQuery.ToPageListAsync(req.page, req.limit, totalcount);
return new Response<PageInfo<List<DroneCaseInfoSingle>>>
{
Result = new PageInfo<List<DroneCaseInfoSingle>>
{
Items = result,
Total = totalcount
},
Message = "获取数据成功"
};
}
//导出汇总
public Response<MemoryStream> CaseOffenceToExcel(List<OffenceResp> list)
{
Response<MemoryStream> response = new Response<MemoryStream>();
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
#region 样式设置
ICellStyle CreateCellStyle(HSSFWorkbook wb, short fontHeight, string fontName, HorizontalAlignment hAlign,
VerticalAlignment vAlign, bool bold = false)
{
IFont font = wb.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontHeight;
font.IsBold = bold;
ICellStyle style = wb.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.Alignment = hAlign;
style.VerticalAlignment = vAlign;
style.SetFont(font);
style.WrapText = true;
return style;
}
ICellStyle contentStyle =
CreateCellStyle(workbook, 20, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
ICellStyle titleStyle =
CreateCellStyle(workbook, 12, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
ICellStyle headerStyle1 = CreateCellStyle(workbook, 36, "方正小标宋简体", HorizontalAlignment.Center,
VerticalAlignment.Center);
ICellStyle headerStyle2 =
CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
ICellStyle headerStyle3 =
CreateCellStyle(workbook, 20, "黑体", HorizontalAlignment.Center, VerticalAlignment.Center);
#endregion
#region 创建表头
IRow CreateRowWithHeight(ISheet sht, int rowIndex, float heightInPoints)
{
IRow row = sht.CreateRow(rowIndex);
row.HeightInPoints = heightInPoints;
return row;
}
void SetCellStyle(IRow row, ICellStyle style)
{
foreach (NPOI.SS.UserModel.ICell cell in row.Cells)
{
cell.CellStyle = style;
}
}
IRow row0 = CreateRowWithHeight(sheet, 0, 100);
row0.CreateCell(0).SetCellValue("无人机全域巡查图斑情况统计表");
for (int k = 1; k < 29; k++) row0.CreateCell(k);
IRow row1 = CreateRowWithHeight(sheet, 1, 55);
row1.CreateCell(0).SetCellValue("合法、其他、非粮化、补办手续、拆除复耕均需市级审核通过才可判定为举证类型。");
for (int k = 1; k < 29; k++) row1.CreateCell(k);
IRow row2 = CreateRowWithHeight(sheet, 2, 65);
row2.CreateCell(0).SetCellValue("县区");
row2.CreateCell(1).SetCellValue("下发图斑");
row2.CreateCell(4).SetCellValue("合法");
row2.CreateCell(7).SetCellValue("违法");
row2.CreateCell(16).SetCellValue("其他");
row2.CreateCell(19).SetCellValue("整改情况");
row2.CreateCell(25).SetCellValue("新增违法占用耕地情况");
row2.CreateCell(28);
IRow row3 = CreateRowWithHeight(sheet, 3, 65);
for (int k = 1; k < 29; k++) row3.CreateCell(k);
// 填充表头内容
row3.CreateCell(0).SetCellValue("县区");
row3.CreateCell(1).SetCellValue("下发图斑");
row3.CreateCell(2);
row3.CreateCell(4).SetCellValue("合法");
row3.CreateCell(5);
row3.CreateCell(6);
row3.CreateCell(7).SetCellValue("小计");
row3.CreateCell(8);
row3.CreateCell(9);
row3.CreateCell(10).SetCellValue("非农化");
row3.CreateCell(11);
row3.CreateCell(12);
row3.CreateCell(13).SetCellValue("非粮化");
row3.CreateCell(14);
row3.CreateCell(15);
row3.CreateCell(16).SetCellValue("其他");
row3.CreateCell(17);
row3.CreateCell(18);
row3.CreateCell(19).SetCellValue("补办手续");
row3.CreateCell(20);
row3.CreateCell(21);
row3.CreateCell(22).SetCellValue("拆除复耕");
row3.CreateCell(23);
row3.CreateCell(24);
row3.CreateCell(25).SetCellValue("核实后新增违法耕地");
row3.CreateCell(26);
row3.CreateCell(27).SetCellValue("整改后剩余违法耕地");
row3.CreateCell(28);
for (var k = 1; k < 29; k++)
{
row3.Height = 65 * 20;
}
IRow row4 = CreateRowWithHeight(sheet, 4, 65);
row4.CreateCell(0).SetCellValue("县区");
row4.CreateCell(1).SetCellValue("宗数");
row4.CreateCell(2).SetCellValue("面积");
row4.CreateCell(3).SetCellValue("耕地面积");
row4.CreateCell(4).SetCellValue("宗数");
row4.CreateCell(5).SetCellValue("面积");
row4.CreateCell(6).SetCellValue("耕地面积");
row4.CreateCell(7).SetCellValue("宗数");
row4.CreateCell(8).SetCellValue("面积");
row4.CreateCell(9).SetCellValue("耕地面积");
row4.CreateCell(10).SetCellValue("宗数");
row4.CreateCell(11).SetCellValue("面积");
row4.CreateCell(12).SetCellValue("耕地面积");
row4.CreateCell(13).SetCellValue("宗数");
row4.CreateCell(14).SetCellValue("面积");
row4.CreateCell(15).SetCellValue("耕地面积");
row4.CreateCell(16).SetCellValue("宗数");
row4.CreateCell(17).SetCellValue("面积");
row4.CreateCell(18).SetCellValue("耕地面积");
row4.CreateCell(19).SetCellValue("宗数");
row4.CreateCell(20).SetCellValue("面积");
row4.CreateCell(21).SetCellValue("耕地面积");
row4.CreateCell(22).SetCellValue("宗数");
row4.CreateCell(23).SetCellValue("面积");
row4.CreateCell(24).SetCellValue("耕地面积");
row4.CreateCell(25).SetCellValue("宗数");
row4.CreateCell(26).SetCellValue("耕地面积");
row4.CreateCell(27).SetCellValue("宗数");
row4.CreateCell(28).SetCellValue("耕地面积");
SetCellStyle(row0, headerStyle1);
SetCellStyle(row1, headerStyle2);
SetCellStyle(row2, headerStyle3);
SetCellStyle(row3, headerStyle3);
SetCellStyle(row4, headerStyle3);
// 合并单元格
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 28));
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 28));
sheet.AddMergedRegion(new CellRangeAddress(2, 4, 0, 0));
sheet.AddMergedRegion(new CellRangeAddress(2, 3, 1, 3));
sheet.AddMergedRegion(new CellRangeAddress(2, 3, 4, 6));
sheet.AddMergedRegion(new CellRangeAddress(2, 2, 7, 15));
sheet.AddMergedRegion(new CellRangeAddress(2, 3, 16, 18));
sheet.AddMergedRegion(new CellRangeAddress(2, 2, 19, 24));
sheet.AddMergedRegion(new CellRangeAddress(2, 2, 25, 28));
sheet.AddMergedRegion(new CellRangeAddress(3, 3, 7, 9));
sheet.AddMergedRegion(new CellRangeAddress(3, 3, 10, 12));
sheet.AddMergedRegion(new CellRangeAddress(3, 3, 13, 15));
sheet.AddMergedRegion(new CellRangeAddress(3, 3, 19, 21));
sheet.AddMergedRegion(new CellRangeAddress(3, 3, 22, 24));
sheet.AddMergedRegion(new CellRangeAddress(3, 3, 25, 26));
sheet.AddMergedRegion(new CellRangeAddress(3, 3, 27, 28));
// 设置列宽度
int[] wideColumns = { 2, 3, 5, 6, 8, 9, 11, 12, 14, 15, 17, 18, 20, 21, 23, 24, 26, 28 };
foreach (var index in wideColumns) sheet.SetColumnWidth(index, 23 * 256);
int[] normalColumns = { 1, 4, 7, 10, 13, 16, 19, 22, 25, 27 };
foreach (var index in normalColumns) sheet.SetColumnWidth(index, 18 * 256);
sheet.SetColumnWidth(0, 20 * 256);
#endregion
#region 填充数据
for (int i = 0; i < list.Count; i++)
{
var rowIndex = i + 5;
var row = CreateRowWithHeight(sheet, rowIndex, 80);
row.CreateCell(0).SetCellValue(list[i].countyname ?? "");
row.CreateCell(1).SetCellValue(list[i].xftbzs);
row.CreateCell(2).SetCellValue(list[i].xfmj);
row.CreateCell(3).SetCellValue(list[i].xfgdmj);
row.CreateCell(4).SetCellValue(list[i].hfzs);
row.CreateCell(5).SetCellValue(list[i].hfmj);
row.CreateCell(6).SetCellValue(list[i].hfgdmj);
row.CreateCell(7).SetCellValue(list[i].wfzs);
row.CreateCell(8).SetCellValue(list[i].wfmj);
row.CreateCell(9).SetCellValue(list[i].wfgdmj);
row.CreateCell(10).SetCellValue(list[i].fnhzs);
row.CreateCell(11).SetCellValue(list[i].fnhmj);
row.CreateCell(12).SetCellValue(list[i].fnhgdmj);
row.CreateCell(13).SetCellValue(list[i].flhzs);
row.CreateCell(14).SetCellValue(list[i].flhmj);
row.CreateCell(15).SetCellValue(list[i].flhgdmj);
row.CreateCell(16).SetCellValue(list[i].qtzs);
row.CreateCell(17).SetCellValue(list[i].qtmj);
row.CreateCell(18).SetCellValue(list[i].qtgdmj);
row.CreateCell(19).SetCellValue(list[i].bbsxzs);
row.CreateCell(20).SetCellValue(list[i].bbsxmj);
row.CreateCell(21).SetCellValue(list[i].bbsxgdmj);
row.CreateCell(22).SetCellValue(list[i].cczs);
row.CreateCell(23).SetCellValue(list[i].ccmj);
row.CreateCell(24).SetCellValue(list[i].ccgdmj);
row.CreateCell(25).SetCellValue(list[i].hshxzwfgdzs);
row.CreateCell(26).SetCellValue(list[i].hshxzwfgdgdmj);
row.CreateCell(27).SetCellValue(list[i].zghsywfgdzs);
row.CreateCell(28).SetCellValue(list[i].zghsywfgdgdmj);
SetCellStyle(row, contentStyle);
}
#endregion
response.Result = new MemoryStream();
workbook.Write(response.Result);
response.Result.Position = 0;
workbook.Close();
response.Code = 200;
response.Message = "获取成功";
}
catch (Exception ex)
{
response.Code = 500;
response.Message = ex.Message;
}
return response;
}
////导出简易表1
//public Response<MemoryStream> CaseOffenceSimpleToExcel(DateTime startTime, DateTime endTime)
//{
// Response<MemoryStream> response = new Response<MemoryStream>();
// try
// {
// var list = CaseOffence(startTime, endTime).Result;
// HSSFWorkbook workbook = new HSSFWorkbook();
// ISheet sheet = workbook.CreateSheet("Sheet1");
// #region 样式设置
// ICellStyle CreateCellStyle(HSSFWorkbook wb, short fontHeight, string fontName, HorizontalAlignment hAlign, VerticalAlignment vAlign, bool bold = false)
// {
// IFont font = wb.CreateFont();
// font.FontName = fontName;
// font.FontHeightInPoints = fontHeight;
// font.IsBold = bold;
// ICellStyle style = wb.CreateCellStyle();
// style.BorderBottom = BorderStyle.Thin;
// style.BorderLeft = BorderStyle.Thin;
// style.BorderRight = BorderStyle.Thin;
// style.BorderTop = BorderStyle.Thin;
// style.Alignment = hAlign;
// style.VerticalAlignment = vAlign;
// style.SetFont(font);
// style.WrapText = true;
// return style;
// }
// ICellStyle CreateCellStyle2(HSSFWorkbook wb, short fontHeight, string fontName, HorizontalAlignment hAlign, VerticalAlignment vAlign, bool bold = false)
// {
// IFont font = wb.CreateFont();
// font.FontName = fontName;
// font.FontHeightInPoints = fontHeight;
// font.IsBold = bold;
// ICellStyle style = wb.CreateCellStyle();
// style.Alignment = hAlign;
// style.VerticalAlignment = vAlign;
// style.SetFont(font);
// style.WrapText = true;
// return style;
// }
// // 创建右对齐样式
// ICellStyle rightAlignStyle = workbook.CreateCellStyle();
// rightAlignStyle.Alignment = HorizontalAlignment.Right; // 设置右对齐
// rightAlignStyle.VerticalAlignment = VerticalAlignment.Center;
// IFont font = workbook.CreateFont();
// font.FontName = "宋体";
// font.FontHeightInPoints = 12;
// rightAlignStyle.SetFont(font);
// rightAlignStyle.WrapText = true;
// //第二个sheet右对齐样式
// ICellStyle rightAlignStyle2 = workbook.CreateCellStyle();
// rightAlignStyle2.Alignment = HorizontalAlignment.Right; // 设置右对齐
// rightAlignStyle2.VerticalAlignment = VerticalAlignment.Center;
// IFont font1 = workbook.CreateFont();
// font1.FontName = "宋体";
// font1.FontHeightInPoints = 9;
// rightAlignStyle2.SetFont(font1);
// rightAlignStyle2.WrapText = true;
// ICellStyle contentStyle = CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
// ICellStyle titleStyle = CreateCellStyle2(workbook, 10, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
// ICellStyle headerStyle1 = CreateCellStyle2(workbook, 18, "方正小标宋简体", HorizontalAlignment.Center, VerticalAlignment.Center);
// ICellStyle headerStyle2 = CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Right, VerticalAlignment.Center);
// ICellStyle headerStyle3 = CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
// //第二个sheet
// ICellStyle contentStyle22 = CreateCellStyle(workbook, 12, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
// ICellStyle titleStyle2 = CreateCellStyle2(workbook, 9, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
// ICellStyle headerStyle21 = CreateCellStyle2(workbook, 18, "方正小标宋简体", HorizontalAlignment.Center, VerticalAlignment.Center);
// ICellStyle headerStyle23 = CreateCellStyle(workbook, 12, "黑体", HorizontalAlignment.Center, VerticalAlignment.Center);
// ICellStyle beizhuStyle21 = CreateCellStyle2(workbook, 9, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
// #endregion
// #region 第一个sheet
// #region 创建表头
// IRow CreateRowWithHeight(ISheet sht, int rowIndex, float heightInPoints)
// {
// IRow row = sht.CreateRow(rowIndex);
// row.HeightInPoints = heightInPoints;
// return row;
// }
// void SetCellStyle(IRow row, ICellStyle style)
// {
// foreach (NPOI.SS.UserModel.ICell cell in row.Cells)
// {
// cell.CellStyle = style;
// }
// }
// IRow row0 = CreateRowWithHeight(sheet, 1, 30);
// row0.CreateCell(0).SetCellValue($"{startTime.Year}年{startTime.Month}-{endTime.Month}月土地卫片新增违法占用等地情况统计表");
// for (int k = 1; k < 4; k++) row0.CreateCell(k);
// IRow row1 = CreateRowWithHeight(sheet, 2, 15);
// row1.CreateCell(0).SetCellValue("统计时间：" + DateTime.Now.ToString("M月d日HH:mm"));
// row1.CreateCell(1);
// row1.CreateCell(2);
// row1.CreateCell(3).SetCellValue("单位：亩");
// //for (int k = 1; k < 4; k++) row1.CreateCell(k);
// IRow row2 = CreateRowWithHeight(sheet, 3, 30);
// row2.CreateCell(0).SetCellValue("序号");
// row2.CreateCell(1).SetCellValue("县区");
// row2.CreateCell(2).SetCellValue("宗数");
// row2.CreateCell(3).SetCellValue("剩余违法耕地");
// // 设置样式
// SetCellStyle(row0, headerStyle1);
// SetCellStyle(row1, titleStyle);
// SetCellStyle(row2, headerStyle3);
// row1.Cells[3].CellStyle = rightAlignStyle;
// // 合并单元格
// sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 3)); // 合并标题行
// sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 1));
// // 设置列宽
// int[] normalColumns = { 1, 2 };
// foreach (var index in normalColumns) sheet.SetColumnWidth(index, 20 * 256); // 设置列宽
// sheet.SetColumnWidth(0, 12 * 256); // 设置列宽
// sheet.SetColumnWidth(3, 30 * 256); // 设置列宽
// #endregion
// #region 填充数据
// for (int i = 0; i < list.Count-1; i++)
// {
// var rowIndex = i + 4; // 数据从第5行开始
// var row = CreateRowWithHeight(sheet, rowIndex, 30);
// row.CreateCell(0).SetCellValue(i + 1);
// row.CreateCell(1).SetCellValue(list[i].countyname ?? "");
// row.CreateCell(2).SetCellValue(list[i].zghsywfgdzs);
// row.CreateCell(3).SetCellValue(list[i].zghsywfgdgdmj);
// SetCellStyle(row, contentStyle);
// }
// //创建总计行
// var rowzj1 = CreateRowWithHeight(sheet, list.Count + 3, 34);
// sheet.AddMergedRegion(new CellRangeAddress(list.Count + 3, list.Count + 3, 0, 1));
// rowzj1.CreateCell(0).SetCellValue("总计");
// rowzj1.CreateCell(1);
// rowzj1.CreateCell(2).SetCellValue(list[list.Count - 1].zghsywfgdzs);
// rowzj1.CreateCell(3).SetCellValue(list[list.Count - 1].zghsywfgdgdmj);
// SetCellStyle(rowzj1, contentStyle);
// #endregion
// #endregion
// #region 第二个sheet
// ISheet sheet2 = workbook.CreateSheet("Sheet2");
// #region 创建表头
// IRow row20 = CreateRowWithHeight(sheet2, 0, 38);
// row20.CreateCell(0).SetCellValue("无人机发现违法行为统计表");
// for (int k = 1; k < 8; k++) row0.CreateCell(k);
// IRow row21 = CreateRowWithHeight(sheet2, 1, 27);
// row21.CreateCell(0).SetCellValue("统计时间：" + DateTime.Now.ToString("yyyy年M月d日HH:mm"));
// row21.CreateCell(1);
// row21.CreateCell(2);
// row21.CreateCell(3);
// row21.CreateCell(4);
// row21.CreateCell(5);
// row21.CreateCell(6);
// row21.CreateCell(7).SetCellValue("单位：个、亩");
// IRow row22 = CreateRowWithHeight(sheet2, 2, 34);
// row22.CreateCell(0).SetCellValue("序号");
// row22.CreateCell(1).SetCellValue("县区");
// row22.CreateCell(2).SetCellValue("下发图斑");
// row22.CreateCell(3);
// row22.CreateCell(4).SetCellValue("核实后新增违法耕地");
// row22.CreateCell(5);
// row22.CreateCell(6).SetCellValue("整改后剩余违法耕地");
// row22.CreateCell(7);
// IRow row23 = CreateRowWithHeight(sheet2, 3, 34);
// row23.CreateCell(0).SetCellValue("序号");
// row23.CreateCell(1).SetCellValue("县区");
// row23.CreateCell(2).SetCellValue("宗数");
// row23.CreateCell(3).SetCellValue("耕地面积");
// row23.CreateCell(4).SetCellValue("宗数");
// row23.CreateCell(5).SetCellValue("耕地面积");
// row23.CreateCell(6).SetCellValue("宗数");
// row23.CreateCell(7).SetCellValue("耕地面积");
// // 设置样式
// SetCellStyle(row20, headerStyle21);
// SetCellStyle(row21, titleStyle2);
// SetCellStyle(row22, headerStyle23);
// SetCellStyle(row23, headerStyle23);
// row21.Cells[7].CellStyle = rightAlignStyle2;
// // 合并单元格
// sheet2.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7)); // 合并标题行
// sheet2.AddMergedRegion(new CellRangeAddress(1, 1, 0, 2));
// sheet2.AddMergedRegion(new CellRangeAddress(2, 2, 2, 3));
// sheet2.AddMergedRegion(new CellRangeAddress(2, 2, 4, 5));
// sheet2.AddMergedRegion(new CellRangeAddress(2, 2, 6, 7));
// sheet2.AddMergedRegion(new CellRangeAddress(2, 3, 0, 0));
// sheet2.AddMergedRegion(new CellRangeAddress(2, 3, 1, 1));
// // 设置列宽
// int[] normalColumns2 = { 2,3,4,5,6,7 };
// foreach (var index in normalColumns2) sheet2.SetColumnWidth(index, 11 * 256); // 设置列宽
// sheet2.SetColumnWidth(0, 6 * 256); // 设置列宽
// sheet2.SetColumnWidth(1, 10 * 256); // 设置列宽
// #endregion
// #region 填充数据
// for (int i = 0; i < list.Count - 1; i++)
// {
// var rowIndex = i + 4; // 数据从第5行开始
// var row = CreateRowWithHeight(sheet2, rowIndex, 34);
// row.CreateCell(0).SetCellValue(i + 1);
// row.CreateCell(1).SetCellValue(list[i].countyname ?? "");
// row.CreateCell(2).SetCellValue(list[i].xftbzs);
// row.CreateCell(3).SetCellValue(list[i].xfgdmj);
// row.CreateCell(4).SetCellValue(list[i].hshxzwfgdzs);
// row.CreateCell(5).SetCellValue(list[i].hshxzwfgdgdmj);
// row.CreateCell(6).SetCellValue(list[i].zghsywfgdzs);
// row.CreateCell(7).SetCellValue(list[i].zghsywfgdgdmj);
// SetCellStyle(row, contentStyle22);
// }
// //创建总计行
// var rowzj = CreateRowWithHeight(sheet2, list.Count + 3, 34);
// sheet2.AddMergedRegion(new CellRangeAddress(list.Count + 3, list.Count + 3, 0, 1));
// rowzj.CreateCell(0).SetCellValue("总计");
// rowzj.CreateCell(1);
// rowzj.CreateCell(2).SetCellValue(list[list.Count - 1].xftbzs);
// rowzj.CreateCell(3).SetCellValue(list[list.Count - 1].xfgdmj);
// rowzj.CreateCell(4).SetCellValue(list[list.Count - 1].hshxzwfgdzs);
// rowzj.CreateCell(5).SetCellValue(list[list.Count - 1].hshxzwfgdgdmj);
// rowzj.CreateCell(6).SetCellValue(list[list.Count - 1].zghsywfgdzs);
// rowzj.CreateCell(7).SetCellValue(list[list.Count - 1].zghsywfgdgdmj);
// SetCellStyle(rowzj, contentStyle22);
// //创建备注行
// var rowbz = CreateRowWithHeight(sheet2, list.Count + 4, 15);
// sheet2.AddMergedRegion(new CellRangeAddress(list.Count + 4, list.Count + 4, 0, 7));
// rowbz.CreateCell(0).SetCellValue("备注：此统计表以“整改后剩余违法耕地”耕地面积进行排序");
// rowbz.CreateCell(1);
// rowbz.CreateCell(2);
// rowbz.CreateCell(3);
// rowbz.CreateCell(4);
// rowbz.CreateCell(5);
// rowbz.CreateCell(6);
// rowbz.CreateCell(7);
// SetCellStyle(rowbz, beizhuStyle21);
// #endregion
// #endregion
// response.Result = new MemoryStream();
// workbook.Write(response.Result);
// response.Result.Position = 0;
// workbook.Close();
// response.Code = 200;
// response.Message = "获取成功";
// }
// catch (Exception ex)
// {
// response.Code = 500;
// response.Message = ex.Message;
// }
// return response;
//}
//导出简易表1
public Response<MemoryStream> CaseOffenceSimpleToExcel(DateTime startTime, DateTime endTime, string tubanlaiyuan)
{
Response<MemoryStream> response = new Response<MemoryStream>();
try
{
var list = CaseOffence(startTime, endTime, tubanlaiyuan).Result;
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
#region 样式设置
ICellStyle CreateCellStyle(HSSFWorkbook wb, short fontHeight, string fontName, HorizontalAlignment hAlign,
VerticalAlignment vAlign, bool bold = false)
{
IFont font = wb.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontHeight;
font.IsBold = bold;
ICellStyle style = wb.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.Alignment = hAlign;
style.VerticalAlignment = vAlign;
style.SetFont(font);
style.WrapText = true;
return style;
}
ICellStyle CreateCellStyle2(HSSFWorkbook wb, short fontHeight, string fontName, HorizontalAlignment hAlign,
VerticalAlignment vAlign, bool bold = false)
{
IFont font = wb.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontHeight;
font.IsBold = bold;
ICellStyle style = wb.CreateCellStyle();
style.Alignment = hAlign;
style.VerticalAlignment = vAlign;
style.SetFont(font);
style.WrapText = true;
return style;
}
// 创建右对齐样式
ICellStyle rightAlignStyle = workbook.CreateCellStyle();
rightAlignStyle.Alignment = HorizontalAlignment.Right; // 设置右对齐
rightAlignStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
rightAlignStyle.SetFont(font);
rightAlignStyle.WrapText = true;
ICellStyle contentStyle =
CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
ICellStyle titleStyle =
CreateCellStyle2(workbook, 10, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
ICellStyle headerStyle1 = CreateCellStyle2(workbook, 18, "方正小标宋简体", HorizontalAlignment.Center,
VerticalAlignment.Center);
ICellStyle headerStyle2 =
CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Right, VerticalAlignment.Center);
ICellStyle headerStyle3 = CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Center,
VerticalAlignment.Center, true);
#endregion
#region 第一个sheet
#region 创建表头
IRow CreateRowWithHeight(ISheet sht, int rowIndex, float heightInPoints)
{
IRow row = sht.CreateRow(rowIndex);
row.HeightInPoints = heightInPoints;
return row;
}
void SetCellStyle(IRow row, ICellStyle style)
{
foreach (NPOI.SS.UserModel.ICell cell in row.Cells)
{
cell.CellStyle = style;
}
}
IRow row0 = CreateRowWithHeight(sheet, 1, 38);
row0.CreateCell(0).SetCellValue($"{startTime.Year}年{startTime.Month}-{endTime.Month}月土地卫片新增违法占用耕地情况统计表");
for (int k = 1; k < 4; k++) row0.CreateCell(k);
IRow row1 = CreateRowWithHeight(sheet, 2, 15);
row1.CreateCell(0).SetCellValue("统计时间：" + DateTime.Now.ToString("M月d日HH:mm"));
row1.CreateCell(1);
row1.CreateCell(2);
row1.CreateCell(3).SetCellValue("单位：亩");
//for (int k = 1; k < 4; k++) row1.CreateCell(k);
IRow row2 = CreateRowWithHeight(sheet, 3, 34);
row2.CreateCell(0).SetCellValue("序号");
row2.CreateCell(1).SetCellValue("县区");
row2.CreateCell(2).SetCellValue("宗数");
row2.CreateCell(3).SetCellValue("剩余违法耕地");
// 设置样式
SetCellStyle(row0, headerStyle1);
SetCellStyle(row1, titleStyle);
SetCellStyle(row2, headerStyle3);
row1.Cells[3].CellStyle = rightAlignStyle;
// 合并单元格
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 3)); // 合并标题行
sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, 1));
// 设置列宽
int[] normalColumns = { 2, 3 };
foreach (var index in normalColumns) sheet.SetColumnWidth(index, 30 * 256); // 设置列宽
sheet.SetColumnWidth(0, 12 * 256); // 设置列宽
sheet.SetColumnWidth(1, 20 * 256); // 设置列宽
#endregion
#region 填充数据
for (int i = 0; i < list.Count - 1; i++)
{
var rowIndex = i + 4; // 数据从第5行开始
var row = CreateRowWithHeight(sheet, rowIndex, 34);
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(list[i].countyname ?? "");
row.CreateCell(2).SetCellValue(list[i].zghsywfgdzs);
row.CreateCell(3).SetCellValue(list[i].zghsywfgdgdmj);
SetCellStyle(row, contentStyle);
}
//创建总计行
var rowzj1 = CreateRowWithHeight(sheet, list.Count + 3, 34);
sheet.AddMergedRegion(new CellRangeAddress(list.Count + 3, list.Count + 3, 0, 1));
rowzj1.CreateCell(0).SetCellValue("总计");
rowzj1.CreateCell(1);
rowzj1.CreateCell(2).SetCellValue(list[list.Count - 1].zghsywfgdzs);
rowzj1.CreateCell(3).SetCellValue(list[list.Count - 1].zghsywfgdgdmj);
SetCellStyle(rowzj1, contentStyle);
#endregion
#endregion
response.Result = new MemoryStream();
workbook.Write(response.Result);
response.Result.Position = 0;
workbook.Close();
response.Code = 200;
response.Message = "获取成功";
}
catch (Exception ex)
{
response.Code = 500;
response.Message = ex.Message;
}
return response;
}
//导出简易表2
public Response<MemoryStream> CaseOffenceSimpleToExcel2(DateTime startTime, DateTime endTime, string tubanlaiyuan)
{
Response<MemoryStream> response = new Response<MemoryStream>();
try
{
var list = CaseOffence(startTime, endTime, tubanlaiyuan).Result;
HSSFWorkbook workbook = new HSSFWorkbook();
#region 样式设置
ICellStyle CreateCellStyle(HSSFWorkbook wb, short fontHeight, string fontName, HorizontalAlignment hAlign,
VerticalAlignment vAlign, bool bold = false)
{
IFont font = wb.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontHeight;
font.IsBold = bold;
ICellStyle style = wb.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.Alignment = hAlign;
style.VerticalAlignment = vAlign;
style.SetFont(font);
style.WrapText = true;
return style;
}
ICellStyle CreateCellStyle2(HSSFWorkbook wb, short fontHeight, string fontName, HorizontalAlignment hAlign,
VerticalAlignment vAlign, bool bold = false)
{
IFont font = wb.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontHeight;
font.IsBold = bold;
ICellStyle style = wb.CreateCellStyle();
style.Alignment = hAlign;
style.VerticalAlignment = vAlign;
style.SetFont(font);
style.WrapText = true;
return style;
}
// 创建右对齐样式
ICellStyle rightAlignStyle = workbook.CreateCellStyle();
rightAlignStyle.Alignment = HorizontalAlignment.Right; // 设置右对齐
rightAlignStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
rightAlignStyle.SetFont(font);
rightAlignStyle.WrapText = true;
//第二个sheet右对齐样式
ICellStyle rightAlignStyle2 = workbook.CreateCellStyle();
rightAlignStyle2.Alignment = HorizontalAlignment.Right; // 设置右对齐
rightAlignStyle2.VerticalAlignment = VerticalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.FontName = "宋体";
font1.FontHeightInPoints = 9;
rightAlignStyle2.SetFont(font1);
rightAlignStyle2.WrapText = true;
ICellStyle contentStyle =
CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
ICellStyle titleStyle =
CreateCellStyle2(workbook, 10, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
ICellStyle headerStyle1 = CreateCellStyle2(workbook, 18, "方正小标宋简体", HorizontalAlignment.Center,
VerticalAlignment.Center);
ICellStyle headerStyle2 =
CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Right, VerticalAlignment.Center);
ICellStyle headerStyle3 =
CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
//第二个sheet
ICellStyle contentStyle22 =
CreateCellStyle(workbook, 12, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
ICellStyle titleStyle2 =
CreateCellStyle2(workbook, 9, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
ICellStyle headerStyle21 = CreateCellStyle2(workbook, 18, "方正小标宋简体", HorizontalAlignment.Center,
VerticalAlignment.Center);
ICellStyle headerStyle23 =
CreateCellStyle(workbook, 12, "黑体", HorizontalAlignment.Center, VerticalAlignment.Center);
ICellStyle beizhuStyle21 =
CreateCellStyle2(workbook, 9, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
#endregion
#region 第一个sheet
#region 创建表头
IRow CreateRowWithHeight(ISheet sht, int rowIndex, float heightInPoints)
{
IRow row = sht.CreateRow(rowIndex);
row.HeightInPoints = heightInPoints;
return row;
}
void SetCellStyle(IRow row, ICellStyle style)
{
foreach (NPOI.SS.UserModel.ICell cell in row.Cells)
{
cell.CellStyle = style;
}
}
#endregion
#endregion
#region 第二个sheet
ISheet sheet2 = workbook.CreateSheet("Sheet2");
#region 创建表头
IRow row20 = CreateRowWithHeight(sheet2, 0, 38);
row20.CreateCell(0).SetCellValue("无人机全域巡查违法用地情况统计表");
//for (int k = 1; k < 8; k++) row20.CreateCell(k);
IRow row21 = CreateRowWithHeight(sheet2, 1, 27);
row21.CreateCell(0).SetCellValue("统计时间：" + DateTime.Now.ToString("yyyy年M月d日HH:mm"));
row21.CreateCell(1);
row21.CreateCell(2);
row21.CreateCell(3);
row21.CreateCell(4);
row21.CreateCell(5);
row21.CreateCell(6);
row21.CreateCell(7).SetCellValue("单位：个、亩");
IRow row22 = CreateRowWithHeight(sheet2, 2, 34);
row22.CreateCell(0).SetCellValue("序号");
row22.CreateCell(1).SetCellValue("县区");
row22.CreateCell(2).SetCellValue("下发图斑");
row22.CreateCell(3);
row22.CreateCell(4).SetCellValue("核实后新增违法耕地");
row22.CreateCell(5);
row22.CreateCell(6).SetCellValue("整改后剩余违法耕地");
row22.CreateCell(7);
IRow row23 = CreateRowWithHeight(sheet2, 3, 34);
row23.CreateCell(0).SetCellValue("序号");
row23.CreateCell(1).SetCellValue("县区");
row23.CreateCell(2).SetCellValue("宗数");
row23.CreateCell(3).SetCellValue("耕地面积");
row23.CreateCell(4).SetCellValue("宗数");
row23.CreateCell(5).SetCellValue("耕地面积");
row23.CreateCell(6).SetCellValue("宗数");
row23.CreateCell(7).SetCellValue("耕地面积");
// 设置样式
SetCellStyle(row20, headerStyle21);
SetCellStyle(row21, titleStyle2);
SetCellStyle(row22, headerStyle23);
SetCellStyle(row23, headerStyle23);
row21.Cells[7].CellStyle = rightAlignStyle2;
// 合并单元格
sheet2.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7)); // 合并标题行
sheet2.AddMergedRegion(new CellRangeAddress(1, 1, 0, 2));
sheet2.AddMergedRegion(new CellRangeAddress(2, 2, 2, 3));
sheet2.AddMergedRegion(new CellRangeAddress(2, 2, 4, 5));
sheet2.AddMergedRegion(new CellRangeAddress(2, 2, 6, 7));
sheet2.AddMergedRegion(new CellRangeAddress(2, 3, 0, 0));
sheet2.AddMergedRegion(new CellRangeAddress(2, 3, 1, 1));
// 设置列宽
int[] normalColumns2 = { 2, 3, 4, 5, 6, 7 };
foreach (var index in normalColumns2) sheet2.SetColumnWidth(index, 11 * 256); // 设置列宽
sheet2.SetColumnWidth(0, 6 * 256); // 设置列宽
sheet2.SetColumnWidth(1, 10 * 256); // 设置列宽
#endregion
#region 填充数据
for (int i = 0; i < list.Count - 1; i++)
{
var rowIndex = i + 4; // 数据从第5行开始
var row = CreateRowWithHeight(sheet2, rowIndex, 34);
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(list[i].countyname ?? "");
row.CreateCell(2).SetCellValue(list[i].xftbzs);
row.CreateCell(3).SetCellValue(list[i].xfgdmj);
row.CreateCell(4).SetCellValue(list[i].hshxzwfgdzs);
row.CreateCell(5).SetCellValue(list[i].hshxzwfgdgdmj);
row.CreateCell(6).SetCellValue(list[i].zghsywfgdzs);
row.CreateCell(7).SetCellValue(list[i].zghsywfgdgdmj);
SetCellStyle(row, contentStyle22);
}
//创建总计行
var rowzj = CreateRowWithHeight(sheet2, list.Count + 3, 34);
sheet2.AddMergedRegion(new CellRangeAddress(list.Count + 3, list.Count + 3, 0, 1));
rowzj.CreateCell(0).SetCellValue("总计");
rowzj.CreateCell(1);
rowzj.CreateCell(2).SetCellValue(list[list.Count - 1].xftbzs);
rowzj.CreateCell(3).SetCellValue(list[list.Count - 1].xfgdmj);
rowzj.CreateCell(4).SetCellValue(list[list.Count - 1].hshxzwfgdzs);
rowzj.CreateCell(5).SetCellValue(list[list.Count - 1].hshxzwfgdgdmj);
rowzj.CreateCell(6).SetCellValue(list[list.Count - 1].zghsywfgdzs);
rowzj.CreateCell(7).SetCellValue(list[list.Count - 1].zghsywfgdgdmj);
SetCellStyle(rowzj, contentStyle22);
//创建备注行
var rowbz = CreateRowWithHeight(sheet2, list.Count + 4, 15);
sheet2.AddMergedRegion(new CellRangeAddress(list.Count + 4, list.Count + 4, 0, 7));
rowbz.CreateCell(0).SetCellValue("备注：此统计表以“整改后剩余违法耕地”耕地面积进行排序");
rowbz.CreateCell(1);
rowbz.CreateCell(2);
rowbz.CreateCell(3);
rowbz.CreateCell(4);
rowbz.CreateCell(5);
rowbz.CreateCell(6);
rowbz.CreateCell(7);
SetCellStyle(rowbz, beizhuStyle21);
#endregion
#endregion
response.Result = new MemoryStream();
workbook.Write(response.Result);
response.Result.Position = 0;
workbook.Close();
response.Code = 200;
response.Message = "获取成功";
}
catch (Exception ex)
{
response.Code = 500;
response.Message = ex.Message;
}
return response;
}
/// <summary>
/// 导出无人机全域巡查图斑整改情况统计表
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public Response<MemoryStream> CaseOfWrjqyxctbzgqktjbToExcel(DateTime startTime, DateTime endTime,
string tubanlaiyuan)
{
Response<MemoryStream> response = new Response<MemoryStream>();
try
{
var list = CaseOffenceForExcel(startTime, endTime, tubanlaiyuan).Result;
HSSFWorkbook workbook = new HSSFWorkbook();
#region 样式设置
ICellStyle CreateCellStyle(HSSFWorkbook wb, short fontHeight, string fontName, HorizontalAlignment hAlign,
VerticalAlignment vAlign, bool bold = false)
{
IFont font = wb.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontHeight;
font.IsBold = bold;
ICellStyle style = wb.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.Alignment = hAlign;
style.VerticalAlignment = vAlign;
style.SetFont(font);
style.WrapText = true;
return style;
}
ICellStyle CreateCellStyle2(HSSFWorkbook wb, short fontHeight, string fontName, HorizontalAlignment hAlign,
VerticalAlignment vAlign, bool bold = false)
{
IFont font = wb.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontHeight;
font.IsBold = bold;
ICellStyle style = wb.CreateCellStyle();
style.Alignment = hAlign;
style.VerticalAlignment = vAlign;
style.SetFont(font);
style.WrapText = true;
return style;
}
// 创建右对齐样式
ICellStyle rightAlignStyle = workbook.CreateCellStyle();
rightAlignStyle.Alignment = HorizontalAlignment.Right; // 设置右对齐
rightAlignStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
rightAlignStyle.SetFont(font);
rightAlignStyle.WrapText = true;
//第二个sheet右对齐样式
ICellStyle rightAlignStyle2 = workbook.CreateCellStyle();
rightAlignStyle2.Alignment = HorizontalAlignment.Right; // 设置右对齐
rightAlignStyle2.VerticalAlignment = VerticalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.FontName = "宋体";
font1.FontHeightInPoints = 9;
rightAlignStyle2.SetFont(font1);
rightAlignStyle2.WrapText = true;
ICellStyle contentStyle =
CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
ICellStyle titleStyle =
CreateCellStyle2(workbook, 10, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
ICellStyle headerStyle1 = CreateCellStyle2(workbook, 18, "方正小标宋简体", HorizontalAlignment.Center,
VerticalAlignment.Center);
ICellStyle headerStyle2 =
CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Right, VerticalAlignment.Center);
ICellStyle headerStyle3 =
CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
//第二个sheet
ICellStyle contentStyle22 =
CreateCellStyle(workbook, 12, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
ICellStyle titleStyle2 =
CreateCellStyle2(workbook, 9, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
ICellStyle headerStyle21 = CreateCellStyle2(workbook, 18, "方正小标宋简体", HorizontalAlignment.Center,
VerticalAlignment.Center);
ICellStyle headerStyle23 =
CreateCellStyle(workbook, 12, "黑体", HorizontalAlignment.Center, VerticalAlignment.Center);
ICellStyle beizhuStyle21 =
CreateCellStyle2(workbook, 9, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
#endregion
#region 第一个sheet
#region 创建表头
IRow CreateRowWithHeight(ISheet sht, int rowIndex, float heightInPoints)
{
IRow row = sht.CreateRow(rowIndex);
row.HeightInPoints = heightInPoints;
return row;
}
void SetCellStyle(IRow row, ICellStyle style)
{
foreach (NPOI.SS.UserModel.ICell cell in row.Cells)
{
cell.CellStyle = style;
}
}
#endregion
#endregion
#region 第二个sheet
ISheet sheet2 = workbook.CreateSheet("Sheet2");
#region 创建表头
IRow row20 = CreateRowWithHeight(sheet2, 0, 38);
row20.CreateCell(0).SetCellValue("无人机全域巡查图斑整改情况统计表");
//for (int k = 1; k < 8; k++) row20.CreateCell(k);
IRow row21 = CreateRowWithHeight(sheet2, 1, 27);
row21.CreateCell(0).SetCellValue("统计时间：" + DateTime.Now.ToString("yyyy年M月d日HH:mm"));
row21.CreateCell(1);
row21.CreateCell(2);
row21.CreateCell(3);
row21.CreateCell(4);
row21.CreateCell(5);
row21.CreateCell(6);
row21.CreateCell(7).SetCellValue("单位：个、亩");
IRow row22 = CreateRowWithHeight(sheet2, 2, 34);
row22.CreateCell(0).SetCellValue("序号");
row22.CreateCell(1).SetCellValue("县区");
row22.CreateCell(2).SetCellValue("下发图斑");
row22.CreateCell(3);
row22.CreateCell(4).SetCellValue("核实后新增违法耕地");
row22.CreateCell(5);
row22.CreateCell(6).SetCellValue("整改后剩余违法耕地");
row22.CreateCell(7);
IRow row23 = CreateRowWithHeight(sheet2, 3, 34);
row23.CreateCell(0).SetCellValue("序号");
row23.CreateCell(1).SetCellValue("县区");
row23.CreateCell(2).SetCellValue("宗数");
row23.CreateCell(3).SetCellValue("耕地面积");
row23.CreateCell(4).SetCellValue("宗数");
row23.CreateCell(5).SetCellValue("耕地面积");
row23.CreateCell(6).SetCellValue("宗数");
row23.CreateCell(7).SetCellValue("耕地面积");
// 设置样式
SetCellStyle(row20, headerStyle21);
SetCellStyle(row21, titleStyle2);
SetCellStyle(row22, headerStyle23);
SetCellStyle(row23, headerStyle23);
row21.Cells[7].CellStyle = rightAlignStyle2;
// 合并单元格
sheet2.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7)); // 合并标题行
sheet2.AddMergedRegion(new CellRangeAddress(1, 1, 0, 2));
sheet2.AddMergedRegion(new CellRangeAddress(2, 2, 2, 3));
sheet2.AddMergedRegion(new CellRangeAddress(2, 2, 4, 5));
sheet2.AddMergedRegion(new CellRangeAddress(2, 2, 6, 7));
sheet2.AddMergedRegion(new CellRangeAddress(2, 3, 0, 0));
sheet2.AddMergedRegion(new CellRangeAddress(2, 3, 1, 1));
// 设置列宽
int[] normalColumns2 = { 2, 3, 4, 5, 6, 7 };
foreach (var index in normalColumns2) sheet2.SetColumnWidth(index, 11 * 256); // 设置列宽
sheet2.SetColumnWidth(0, 6 * 256); // 设置列宽
sheet2.SetColumnWidth(1, 10 * 256); // 设置列宽
#endregion
#region 填充数据
for (int i = 0; i < list.Count - 1; i++)
{
var rowIndex = i + 4; // 数据从第5行开始
var row = CreateRowWithHeight(sheet2, rowIndex, 34);
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(list[i].countyname ?? "");
row.CreateCell(2).SetCellValue(list[i].xftbzs);
row.CreateCell(3).SetCellValue(list[i].xfgdmj);
row.CreateCell(4).SetCellValue(list[i].hshxzwfgdzs);
row.CreateCell(5).SetCellValue(list[i].hshxzwfgdgdmj);
row.CreateCell(6).SetCellValue(list[i].zghsywfgdzs);
row.CreateCell(7).SetCellValue(list[i].zghsywfgdgdmj);
SetCellStyle(row, contentStyle22);
}
//创建总计行
var rowzj = CreateRowWithHeight(sheet2, list.Count + 3, 34);
sheet2.AddMergedRegion(new CellRangeAddress(list.Count + 3, list.Count + 3, 0, 1));
rowzj.CreateCell(0).SetCellValue("总计");
rowzj.CreateCell(1);
rowzj.CreateCell(2).SetCellValue(list[list.Count - 1].xftbzs);
rowzj.CreateCell(3).SetCellValue(list[list.Count - 1].xfgdmj);
rowzj.CreateCell(4).SetCellValue(list[list.Count - 1].hshxzwfgdzs);
rowzj.CreateCell(5).SetCellValue(list[list.Count - 1].hshxzwfgdgdmj);
rowzj.CreateCell(6).SetCellValue(list[list.Count - 1].zghsywfgdzs);
rowzj.CreateCell(7).SetCellValue(list[list.Count - 1].zghsywfgdgdmj);
SetCellStyle(rowzj, contentStyle22);
//创建备注行
var rowbz = CreateRowWithHeight(sheet2, list.Count + 4, 15);
sheet2.AddMergedRegion(new CellRangeAddress(list.Count + 4, list.Count + 4, 0, 7));
rowbz.CreateCell(0).SetCellValue("备注：此统计表以“整改后剩余违法耕地”耕地面积进行排序");
rowbz.CreateCell(1);
rowbz.CreateCell(2);
rowbz.CreateCell(3);
rowbz.CreateCell(4);
rowbz.CreateCell(5);
rowbz.CreateCell(6);
rowbz.CreateCell(7);
SetCellStyle(rowbz, beizhuStyle21);
#endregion
#endregion
response.Result = new MemoryStream();
workbook.Write(response.Result);
response.Result.Position = 0;
workbook.Close();
response.Code = 200;
response.Message = "获取成功";
}
catch (Exception ex)
{
response.Code = 500;
response.Message = ex.Message;
}
return response;
}
/// <summary>
/// 导出无人机全域巡查图斑下发情况统计表
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public Response<MemoryStream> CaseOfWrjqyxctbxfqktjbToExcel(DateTime startTime, DateTime endTime,
string tubanlaiyuan)
{
Response<MemoryStream> response = new Response<MemoryStream>();
try
{
var list = CaseOffencetbForExcel(startTime, endTime, tubanlaiyuan).Result;
HSSFWorkbook workbook = new HSSFWorkbook();
#region 样式设置
ICellStyle CreateCellStyle(HSSFWorkbook wb, short fontHeight, string fontName, HorizontalAlignment hAlign,
VerticalAlignment vAlign, bool bold = false)
{
IFont font = wb.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontHeight;
font.IsBold = bold;
ICellStyle style = wb.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.Alignment = hAlign;
style.VerticalAlignment = vAlign;
style.SetFont(font);
style.WrapText = true;
return style;
}
ICellStyle CreateCellStyle2(HSSFWorkbook wb, short fontHeight, string fontName, HorizontalAlignment hAlign,
VerticalAlignment vAlign, bool bold = false)
{
IFont font = wb.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontHeight;
font.IsBold = bold;
ICellStyle style = wb.CreateCellStyle();
style.Alignment = hAlign;
style.VerticalAlignment = vAlign;
style.SetFont(font);
style.WrapText = true;
return style;
}
// 创建右对齐样式
ICellStyle rightAlignStyle = workbook.CreateCellStyle();
rightAlignStyle.Alignment = HorizontalAlignment.Right; // 设置右对齐
rightAlignStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
rightAlignStyle.SetFont(font);
rightAlignStyle.WrapText = true;
//第二个sheet右对齐样式
ICellStyle rightAlignStyle2 = workbook.CreateCellStyle();
rightAlignStyle2.Alignment = HorizontalAlignment.Right; // 设置右对齐
rightAlignStyle2.VerticalAlignment = VerticalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.FontName = "宋体";
font1.FontHeightInPoints = 9;
rightAlignStyle2.SetFont(font1);
rightAlignStyle2.WrapText = true;
ICellStyle contentStyle =
CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
ICellStyle titleStyle =
CreateCellStyle2(workbook, 10, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
ICellStyle headerStyle1 = CreateCellStyle2(workbook, 18, "方正小标宋简体", HorizontalAlignment.Center,
VerticalAlignment.Center);
ICellStyle headerStyle2 =
CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Right, VerticalAlignment.Center);
ICellStyle headerStyle3 =
CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
//第二个sheet
ICellStyle contentStyle22 =
CreateCellStyle(workbook, 12, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
ICellStyle titleStyle2 =
CreateCellStyle2(workbook, 12, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
ICellStyle headerStyle21 = CreateCellStyle2(workbook, 18, "方正小标宋简体", HorizontalAlignment.Center,
VerticalAlignment.Center);
ICellStyle headerStyle23 =
CreateCellStyle(workbook, 12, "黑体", HorizontalAlignment.Center, VerticalAlignment.Center);
ICellStyle beizhuStyle21 =
CreateCellStyle2(workbook, 12, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
#endregion
#region 第一个sheet
#region 创建表头
IRow CreateRowWithHeight(ISheet sht, int rowIndex, float heightInPoints)
{
IRow row = sht.CreateRow(rowIndex);
row.HeightInPoints = heightInPoints;
return row;
}
void SetCellStyle(IRow row, ICellStyle style)
{
foreach (NPOI.SS.UserModel.ICell cell in row.Cells)
{
cell.CellStyle = style;
}
}
#endregion
#endregion
#region 第二个sheet
ISheet sheet2 = workbook.CreateSheet("Sheet2");
#region 创建表头
IRow row20 = CreateRowWithHeight(sheet2, 0, 28);
row20.CreateCell(0).SetCellValue("无人机全域巡查图斑下发情况统计表");
//for (int k = 1; k < 8; k++) row20.CreateCell(k);
IRow row21 = CreateRowWithHeight(sheet2, 1, 15);
row21.CreateCell(0).SetCellValue("统计时间：" + DateTime.Now.ToString("yyyy年M月d日HH:mm"));
row21.CreateCell(1);
row21.CreateCell(2);
row21.CreateCell(3);
row21.CreateCell(4);
row21.CreateCell(5);
row21.CreateCell(6);
row21.CreateCell(7);
row21.CreateCell(8);
row21.CreateCell(9);
row21.CreateCell(10).SetCellValue("单位：个、亩");
row21.CreateCell(11);
IRow row22 = CreateRowWithHeight(sheet2, 2, 25);
row22.CreateCell(0).SetCellValue("县区");
row22.CreateCell(1).SetCellValue("建设用地");
row22.CreateCell(2);
row22.CreateCell(3).SetCellValue("农用地");
row22.CreateCell(4);
row22.CreateCell(5);
row22.CreateCell(6).SetCellValue("推堆土");
row22.CreateCell(7);
row22.CreateCell(8);
row22.CreateCell(9).SetCellValue("总计");
row22.CreateCell(10);
row22.CreateCell(11);
IRow row23 = CreateRowWithHeight(sheet2, 3, 34);
row23.CreateCell(0).SetCellValue("县区");
row23.CreateCell(1).SetCellValue("个数");
row23.CreateCell(2).SetCellValue("面积");
row23.CreateCell(3).SetCellValue("个数");
row23.CreateCell(4).SetCellValue("面积");
row23.CreateCell(5).SetCellValue("耕地面积");
row23.CreateCell(6).SetCellValue("个数");
row23.CreateCell(7).SetCellValue("面积");
row23.CreateCell(8).SetCellValue("耕地面积");
row23.CreateCell(9).SetCellValue("个数");
row23.CreateCell(10).SetCellValue("面积");
row23.CreateCell(11).SetCellValue("耕地面积");
// 设置样式
SetCellStyle(row20, headerStyle21);
SetCellStyle(row21, titleStyle2);
SetCellStyle(row22, headerStyle23);
SetCellStyle(row23, headerStyle23);
row21.Cells[7].CellStyle = rightAlignStyle2;
// 合并单元格
sheet2.AddMergedRegion(new CellRangeAddress(0, 0, 0, 11)); // 合并标题行
sheet2.AddMergedRegion(new CellRangeAddress(1, 1, 0, 2));
sheet2.AddMergedRegion(new CellRangeAddress(2, 2, 1, 2));
sheet2.AddMergedRegion(new CellRangeAddress(2, 2, 3, 5));
sheet2.AddMergedRegion(new CellRangeAddress(2, 2, 6, 8));
sheet2.AddMergedRegion(new CellRangeAddress(2, 2, 9, 11));
sheet2.AddMergedRegion(new CellRangeAddress(2, 3, 0, 0)); //县区
sheet2.AddMergedRegion(new CellRangeAddress(1, 1, 10, 11));
// 设置列宽
int[] normalColumns2 = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 };
foreach (var index in normalColumns2) sheet2.SetColumnWidth(index, 10 * 256); // 设置列宽
#endregion
#region 填充数据
for (int i = 0; i < list.Count - 1; i++)
{
var rowIndex = i + 4; // 数据从第5行开始
var row = CreateRowWithHeight(sheet2, rowIndex, 22);
row.CreateCell(0).SetCellValue(list[i].countyname ?? "");
row.CreateCell(1).SetCellValue(list[i].jsydtbzs);
row.CreateCell(2).SetCellValue(list[i].jsydmj);
row.CreateCell(3).SetCellValue(list[i].nydtbzs);
row.CreateCell(4).SetCellValue(list[i].nydmj);
row.CreateCell(5).SetCellValue(list[i].nydgdmj);
row.CreateCell(6).SetCellValue(list[i].tdttbzs);
row.CreateCell(7).SetCellValue(list[i].tdtmj);
row.CreateCell(8).SetCellValue(list[i].tdtgdmj);
row.CreateCell(9).SetCellValue(list[i].zjttbzs);
row.CreateCell(10).SetCellValue(list[i].zjmj);
row.CreateCell(11).SetCellValue(list[i].zjgdmj);
SetCellStyle(row, contentStyle22);
}
//创建备注行
var rowbz = CreateRowWithHeight(sheet2, list.Count + 3, 15);
sheet2.AddMergedRegion(new CellRangeAddress(list.Count + 3, list.Count + 3, 0, 11));
rowbz.CreateCell(0).SetCellValue("备注：此表以“总计”图斑个数降序排序。");
rowbz.CreateCell(1);
rowbz.CreateCell(2);
rowbz.CreateCell(3);
rowbz.CreateCell(4);
rowbz.CreateCell(5);
rowbz.CreateCell(6);
rowbz.CreateCell(7);
rowbz.CreateCell(8);
rowbz.CreateCell(9);
rowbz.CreateCell(10);
rowbz.CreateCell(11);
SetCellStyle(rowbz, beizhuStyle21);
#endregion
#endregion
response.Result = new MemoryStream();
workbook.Write(response.Result);
response.Result.Position = 0;
workbook.Close();
response.Code = 200;
response.Message = "获取成功";
}
catch (Exception ex)
{
response.Code = 500;
response.Message = ex.Message;
}
return response;
}
public Response<List<DroneCaseByTypeResp>> ExprotCaseListByType(QueryCaseOffencetReq req)
{
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
if (user == null)
{
return new Response<List<DroneCaseByTypeResp>>
{
Code = 401,
Message = "Unauthorized"
};
}
RefAsync<int> totalcount = 0;
string sql1 = "select min(\"Level\") from sys_userorg where \"UserId\"=@UserId";
int level = client.Ado.GetInt(sql1, new { UserId = user.Id });
List<string> deplist = new List<string>();
if (level != 0 && user.Id != -1)
{
var departs = client.Queryable<SysUserOrg>().Where(r => r.UserId == user.Id).ToList();
foreach (var item in departs)
{
var list = client.Queryable<SysOrg>().ToChildList(it => it.ParentId, item.OrgId)
?.Select(it => it.Id.ToString()).ToList();
deplist = deplist.Union(list).ToList();
}
}
//var casetype = client.Queryable<SysDataItemDetail>()
// .Where(r => r.ItemCode == "DRONE_CASE_TYPE" && r.ItemValue == "DRONE_CASE_TYPE_NYD")
// .First()?.ItemDetailId;
//var casetype = "农用地";
string sqlpart = BuildSqlPart(req.AreaId, level, deplist);
if (!string.IsNullOrEmpty(req.tubanlaiyuan))
{
var tubanlaiyuanList = req.tubanlaiyuan.Split(",").ToList();
sqlpart += $" and tubanlaiyuan in ('{string.Join("','", tubanlaiyuanList)}')";
}
//var resubmit = client.Queryable<ReSubmit>().Select(r => r.CaseNo).ToList();
string sql = @$"SELECT a.""Id"", countyname, streetname,communityname,original_case_no,geomid,
remark,(date_trunc('second', synchronoustime))::text as synchronoustime,
picihao,tubanlaiyuan, case_no AS caseNo,
case_description,xjshenhejieguo,xianjiyijian,verifyuser,verifytime,
sjshenhejieguo,shijiyijian,hexiaoren,(date_trunc('second', hexiaotime))::text as hexiaotime,
typeid, typename,jieshou_people,jieshou_time,
pandingyijushuoming,examiner_name,examine_time,
transactor_name,transact_time, createtime AS identificationTime,
NULLIF(area, '')::numeric AS area,
case when is_illegal=1 then actual_use_to when is_illegal=2 then qita_use_to end as qtsjyt,
NULLIF(nongyongdi_area, '')::numeric AS nongyongdiArea,
NULLIF(yongjiujibennongtian_area, '')::numeric, 0 AS yongnongArea,
NULLIF(gengdi_area, '')::numeric AS gengdiArea, CASE WHEN is_illegal = 0 THEN '合法' WHEN is_illegal = 1 THEN '违法' WHEN is_illegal = 2 THEN '其他' END AS isIllegal
,handle_status_name AS handleStatusName, xiangmumc, xiangmuzhuti,
CASE WHEN weifaleixing = 0 THEN '非农化违法用地' WHEN weifaleixing = 1 THEN '非粮化违法用地' END AS weifaleixing,
illegal_contact AS illegalContact, illegal_shenfenzhenghao AS illegalShenfenzhenghao,
CASE WHEN CAST(measure_name AS numeric) = 0 THEN '拆除复耕' WHEN CAST(measure_name AS numeric) = 1 THEN '补办手续' END AS measureName,
CASE WHEN result_name = '0' THEN '立案' WHEN result_name = '1' THEN '非立案' WHEN result_name = '2' THEN '移交其他部门处理' WHEN result_name = '3' THEN '未处理' END AS resultName,
registr_number AS registrNumber
FROM drone_caseinfo_single a
LEFT JOIN wf_process b on b.""InstanceInfo""->>'pkeyValue'=a.""Id""
WHERE is_closed = 0 and b.""Id"" is not null and synchronoustime >= @StartTime and synchronoustime <= @EndTime and typename = @CaseTypeId {sqlpart}";
var caseList =
client.Ado.SqlQuery<DroneCaseByTypeResp>(sql,
new { StartTime = req.StartTime, EndTime = req.EndTime, CaseTypeId = "农用地", @AreaId = req.AreaId });
caseList = FilterCasesByType(req.CaseType, caseList);
return new Response<List<DroneCaseByTypeResp>> { Result = caseList };
}
private string BuildSqlPart(string areaId, int level, List<string> deplist)
{
if (!string.IsNullOrEmpty(areaId) && areaId != "0")
{
if (level == 0)
{
return $" AND countyid = @AreaId";
}
if (level == 1)
{
return $" AND streetid = @AreaId";
}
}
if (deplist.Any())
{
string orgs = string.Join(",", deplist.Select(dep => $"'{dep}'"));
return $" AND (countyid IN ({orgs}) OR streetid IN ({orgs}) OR communityid IN ({orgs}))";
}
return string.Empty;
}
private List<DroneCaseByTypeResp> FilterCasesByType(int caseType, List<DroneCaseByTypeResp> caseList)
{
switch (caseType)
{
case 2:
return caseList.Where(r => r.isIllegal == "合法" && r.handleStatusName == "已归档").ToList();
case 3:
return caseList.Where(r =>
(r.isIllegal == "违法" && r.handleStatusName == "已归档") || r.handleStatusName != "已归档").ToList();
case 4:
return caseList.Where(r => r.isIllegal == "其他" && r.handleStatusName == "已归档").ToList();
case 5:
return caseList.Where(r => r.weifaleixing == "非粮化违法用地" && r.handleStatusName == "已归档").ToList();
case 6:
return caseList.Where(r => r.measureName == "补办手续" && r.handleStatusName == "已归档").ToList();
case 7:
//r.measure_name == "0" && r.handle_status_id == 5 && r.weifaleixing == 0 && r.is_illegal == 1
return caseList.Where(r =>
r.measureName == "拆除复耕" && r.handleStatusName == "已归档" && r.weifaleixing == "非农化违法用地").ToList();
case 8:
//((r.is_illegal == 1 && r.handle_status_id == 5) || r.handle_status_id != 5)&&
//!(r.weifaleixing == 1 && r.handle_status_id == 5 && r.is_illegal == 1) &&
//!((string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00") && r.handle_status_id == 5
// && (r.handle_status_id != 5 || (r.is_illegal == 1 && r.handle_status_id == 5))));
return caseList = caseList.Where(r =>
((r.isIllegal == "违法" && r.handleStatusName == "已归档") || r.handleStatusName != "已归档") &&
!(r.weifaleixing == "非粮化违法用地" && r.handleStatusName == "已归档") &&
!((r.gengdiArea == 0 && r.handleStatusName == "已归档") && (r.handleStatusName != "已归档" ||
(r.isIllegal == "违法" &&
r.handleStatusName == "已归档")))).ToList();
case 9:
// !((string.IsNullOrEmpty(r.gengdi_area) || r.gengdi_area == "0" || r.gengdi_area == "0.00") && r.handle_status_id == 5 && (r.handle_status_id != 5 || (r.is_illegal == 1 && r.handle_status_id == 5))) &&
return caseList = caseList.Where(r =>
((r.isIllegal == "违法" && r.handleStatusName == "已归档") || r.handleStatusName != "已归档") &&
!(r.weifaleixing == "非粮化违法用地" && r.handleStatusName == "已归档") &&
//!((r.gengdiArea == 0 && r.handleStatusName == "已归档") && (r.handleStatusName != "已归档" || (r.isIllegal == "违法" && r.handleStatusName == "已归档"))) &&
!(r.measureName == "补办手续" && r.handleStatusName == "已归档") &&
!(r.measureName == "拆除复耕" && r.handleStatusName == "已归档"))
.ToList();
case 10:
//&& r.is_illegal == 1 && r.handle_status_id == 5
//casesForCounty.Count(r => (r.weifaleixing == 0 && r.is_illegal == 1 && r.handle_status_id == 5) || r.handle_status_id != 5);
return caseList = caseList.Where(r =>
(r.weifaleixing == "非农化违法用地" && r.isIllegal == "违法" && r.handleStatusName == "已归档") ||
r.handleStatusName != "已归档").ToList();
default:
return caseList;
}
}
public Response<MemoryStream> ListToExcelByCaseType(List<DroneCaseByTypeResp> list, List<ModuleColumn> headers)
{
var response = new Response<MemoryStream>();
try
{
var workbook = new HSSFWorkbook();
var contentStyle = CreateCellStyle(workbook, false);
var headerStyle = CreateCellStyle(workbook, true);
int sheetCount = list.Count / 60000 + 1;
for (int k = 0; k < sheetCount; k++)
{
var sheet = workbook.CreateSheet("Sheet" + k);
CreateHeaderRow(sheet, headers, headerStyle);
var rowsToWrite = list.Skip(k * 60000).Take(60000).ToList();
FillSheetWithData(workbook, sheet, rowsToWrite, headers, contentStyle);
}
using (var ms = new MemoryStream())
{
workbook.Write(ms);
response.Result = new MemoryStream(ms.ToArray());
}
response.Code = 200;
response.Message = "获取成功";
}
catch (Exception ex)
{
response.Code = 500;
response.Message = ex.Message;
}
return response;
}
private ICellStyle CreateCellStyle(HSSFWorkbook workbook, bool isHeader)
{
var font = workbook.CreateFont();
font.FontName = "Microsoft YaHei";
font.FontHeightInPoints = 12;
if (isHeader)
{
font.Boldweight = (short)FontBoldWeight.Bold;
}
var style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.SetFont(font);
style.WrapText = true;
return style;
}
private void CreateHeaderRow(ISheet sheet, List<ModuleColumn> headers, ICellStyle headerStyle)
{
var rowHeader = sheet.CreateRow(0);
rowHeader.Height = 20 * 30;
for (int i = 0; i < headers.Count; i++)
{
var header = headers[i];
var cell = rowHeader.CreateCell(i);
cell.CellStyle = headerStyle;
cell.SetCellValue(header.value);
//sheet.SetColumnWidth(i, 20 * 350);
}
sheet.SetColumnWidth(0, 10 * 256);
sheet.SetColumnWidth(1, 18 * 256);
sheet.SetColumnWidth(2, 10 * 256);
sheet.SetColumnWidth(3, 28 * 256);
sheet.SetColumnWidth(4, 12 * 256);
sheet.SetColumnWidth(5, 12 * 256);
sheet.SetColumnWidth(6, 15 * 256);
sheet.SetColumnWidth(7, 52 * 256);
sheet.SetColumnWidth(8, 12 * 256);
sheet.SetColumnWidth(9, 24 * 256);
sheet.SetColumnWidth(10, 10 * 256);
sheet.SetColumnWidth(11, 12 * 256);
sheet.SetColumnWidth(12, 10 * 256);
sheet.SetColumnWidth(13, 10 * 256);
sheet.SetColumnWidth(14, 10 * 256);
sheet.SetColumnWidth(15, 10 * 256);
sheet.SetColumnWidth(16, 10 * 256);
sheet.SetColumnWidth(17, 24 * 256);
sheet.SetColumnWidth(18, 15 * 256);
sheet.SetColumnWidth(19, 12 * 256);
sheet.SetColumnWidth(20, 17 * 256);
sheet.SetColumnWidth(21, 17 * 256);
sheet.SetColumnWidth(22, 31 * 256);
sheet.SetColumnWidth(23, 10 * 256);
sheet.SetColumnWidth(24, 24 * 256);
sheet.SetColumnWidth(25, 10 * 256);
sheet.SetColumnWidth(26, 12 * 256);
sheet.SetColumnWidth(27, 24 * 256);
sheet.SetColumnWidth(28, 30 * 256);
sheet.SetColumnWidth(29, 30 * 256);
sheet.SetColumnWidth(30, 30 * 256);
sheet.SetColumnWidth(31, 14 * 256);
sheet.SetColumnWidth(32, 30 * 256);
sheet.SetColumnWidth(33, 30 * 256);
sheet.SetColumnWidth(34, 30 * 256);
sheet.SetColumnWidth(35, 14 * 256);
sheet.SetColumnWidth(36, 30 * 256);
sheet.SetColumnWidth(37, 30 * 256);
}
private void FillSheetWithData(HSSFWorkbook workbook, ISheet sheet, List<DroneCaseByTypeResp> list,
List<ModuleColumn> headers,
ICellStyle contentStyle)
{
// 创建一个数值格式的CellStyle
ICellStyle numericStyle = workbook.CreateCellStyle();
// 创建一个数据格式对象
IDataFormat dataFormat = workbook.CreateDataFormat();
var font = workbook.CreateFont();
font.FontName = "Microsoft YaHei";
font.FontHeightInPoints = 12;
// 设置单元格格式为数值型
numericStyle.DataFormat = dataFormat.GetFormat("0.00");
numericStyle.BorderBottom = BorderStyle.Thin;
numericStyle.BorderLeft = BorderStyle.Thin;
numericStyle.BorderRight = BorderStyle.Thin;
numericStyle.BorderTop = BorderStyle.Thin;
numericStyle.Alignment = HorizontalAlignment.Center;
numericStyle.VerticalAlignment = VerticalAlignment.Center;
numericStyle.SetFont(font);
for (int i = 0; i < list.Count; i++)
{
var item = list[i];
var dataRow = sheet.CreateRow(i + 1);
for (int j = 0; j < headers.Count; j++)
{
if (headers[j].key == "area" || headers[j].key == "gengdiArea" || headers[j].key == "yongnongArea" ||
headers[j].key == "nongyongdiArea")
{
var cell = dataRow.CreateCell(j);
cell.CellStyle = numericStyle;
var objValue = GetCellDoubleValue(item, headers[j].key);
cell.SetCellValue(objValue);
}
else
{
var cell = dataRow.CreateCell(j);
cell.CellStyle = contentStyle;
if (headers[j].key == "xuhao")
{
cell.SetCellValue(i + 1);
}
else
{
var objValue = GetCellValue(item, headers[j].key);
cell.SetCellValue(objValue);
}
}
}
}
}
private string GetCellValue(DroneCaseByTypeResp item, string key)
{
return key switch
{
"tubanlaiyuan" => item.tubanlaiyuan,
"picihao" => item.picihao,
"caseNo" => item.caseNo,
"countyname" => item.countyname,
"streetname" => item.streetname,
"case_description" => item.case_description,
"typename" => item.typename,
"synchronoustime" => item.synchronoustime == null
? ""
: item.synchronoustime.ToString("yyyy-MM-dd HH:mm:ss"),
//"area" => item.area,
//"gengdiArea" => item.gengdiArea,
//"yongnongArea" => item.yongnongArea,
"isillegal" => item.isIllegal,
"handleStatusName" => item.handleStatusName,
"xiangmumc" => item.xiangmumc,
"xiangmuzhuti" => item.xiangmuzhuti,
"weifaleixing" => item.weifaleixing,
"measureName" => item.measureName,
"communityname" => item.communityname,
"qtsjyt" => string.IsNullOrEmpty(item.qtsjyt) ? "" : SJYT(item.qtsjyt),
"jieshou_people" => item.jieshou_people,
"jieshou_time" => item.jieshou_time == null ? "" :
DateTime.Parse(item.jieshou_time) == DateTime.MinValue ? string.Empty :
DateTime.Parse(item.jieshou_time).ToString("yyyy-MM-dd HH:mm:ss"),
"pandingyijushuoming" => item.pandingyijushuoming,
"examiner_name" => item.examiner_name,
"examine_time" => item.examine_time == DateTime.MinValue
? string.Empty
: item.examine_time.ToString("yyyy-MM-dd HH:mm:ss"),
"transactor_name" => item.transactor_name,
"transact_time" => item.transact_time == DateTime.MinValue
? string.Empty
: item.transact_time.ToString("yyyy-MM-dd HH:mm:ss"),
"remark" => item.remark,
"xjshenhejieguo" => item.xjshenhejieguo,
"xianjiyijian" => item.xianjiyijian,
"verifyuser" => item.verifyuser,
"verifytime" => item.verifytime == null ? "" :
DateTime.Parse(item.verifytime) == DateTime.MinValue ? string.Empty :
DateTime.Parse(item.verifytime).ToString("yyyy-MM-dd HH:mm:ss"),
"sjshenhejieguo" => item.sjshenhejieguo,
"shijiyijian" => item.shijiyijian,
"hexiaoren" => item.hexiaoren,
"hexiaotime" => item.hexiaotime == null ? "" : item.hexiaotime.ToString("yyyy-MM-dd HH:mm:ss"),
//item.hexiaotime == null ? "" : DateTime.Parse(item.hexiaotime) == DateTime.MinValue ? string.Empty : DateTime.Parse(item.hexiaotime).ToString("yyyy-MM-dd HH:mm:ss"),
_ => string.Empty,
};
}
private double GetCellDoubleValue(DroneCaseByTypeResp item, string key)
{
return key switch
{
"area" => item.area,
"gengdiArea" => item.gengdiArea,
"yongnongArea" => item.yongnongArea,
"nongyongdiArea" => item.nongyongdiArea,
};
}
private string SJYT(string qtsjyt)
{
string sjytvalue = "";
switch (qtsjyt)
{
case "1":
sjytvalue = "不改变原用地性质的光伏用地";
break;
case "2":
sjytvalue = "设施农业用地";
break;
case "3":
sjytvalue = "农村道路";
break;
case "4":
sjytvalue = "实地未变化";
break;
case "5":
sjytvalue = "临时用地";
break;
default:
sjytvalue = qtsjyt;
break;
}
return sjytvalue;
}
private string FormatDateTime(DateTimeOffset dateTime)
{
return dateTime == DateTimeOffset.MinValue ? string.Empty : dateTime.ToString("yyyy-MM-dd HH:mm:ss");
}
public void ExportToShapefileold(QueryCaseOffencetReq req, string shpFilePath)
{
var response = ExprotCaseListByType(req).Result;
List<int> gids = response.SelectMany(item =>
item.geomid.Split(',')
.Select(id => int.TryParse(id, out var gid) ? gid : (int?)null)
.Where(gid => gid.HasValue)
.Select(gid => gid.Value)).ToList();
string gidsString = string.Join(",", gids);
string query = $"SELECT gid, ST_AsText(geom) AS geom FROM drone_shp_data WHERE gid IN ({gidsString})";
// 执行查询
var data = client.Ado.SqlQuery<DroneShpDataResp>(query);
if (data == null || data.Count == 0)
{
throw new Exception("暂无数据");
}
List<IFeature> features = new List<IFeature>();
foreach (var row in data)
{
var geometry = ParseGeometry(row.geom);
if (geometry == null)
{
throw new Exception("数据不可用");
}
var attributes = new AttributesTable
{
{ "gid", row.gid },
};
IFeature feature = new Feature(geometry, attributes);
features.Add(feature);
}
if (features.Count == 0)
{
throw new Exception("数据不可用");
}
var geometryFactory = new GeometryFactory(new PrecisionModel(), 4326);
var shpDataWriter = new ShapefileDataWriter(shpFilePath, geometryFactory)
{
Header = ShapefileDataWriter.GetHeader(features[0], features.Count)
};
shpDataWriter.Write(features);
}
#region xin
#endregion
public void ExportToShapefile(QueryCaseOffencetReq req, string shpFilePath, string shpFilePathzip)
{
var response = ExprotCaseListByType(req).Result;
List<int> gids = response.SelectMany(item =>
item.geomid.Split(',')
.Select(id => int.TryParse(id, out var gid) ? gid : (int?)null)
.Where(gid => gid.HasValue)
.Select(gid => gid.Value)).ToList();
if (gids.Count < 1)
{
throw new Exception("暂无数据");
}
string gidsString = string.Join(",", gids);
//string query = $"SELECT gid,case_no,handle_status_name,typename,countyid,streetid, ST_AsText(geom) AS geom FROM view_drone_shp_data WHERE gid IN ({gidsString})";
string query =
$"SELECT gid,case_no,handle_status_name,typename,original_case_no,gengdi_area, is_illegal_name,is_intact_name," +
$"area,yongjiujibennongtian_area,xiangmumc,xiangmuzhuti,weifaleixing,measure_name,countyname,streetname,communityname,synchronoustime,tubanlaiyuan,ST_AsText(geom) AS geom FROM view_drone_caseinfo_shp WHERE gid IN ({gidsString})";
// 执行查询
var data = client.Ado.SqlQuery<DroneShpDataResp>(query);
if (data == null || data.Count == 0)
{
throw new Exception("暂无数据");
}
List<IFeature> features = new List<IFeature>();
foreach (var row in data)
{
var geometry = ParseGeometry(row.geom);
if (geometry == null)
{
throw new Exception("数据不可用");
}
var attributes = new AttributesTable
{
{ "gid", row.gid },
{ "图斑编号", row.case_no == null ? "" : row.case_no },
{ "当前状态", row.handle_status_name == null ? "" : row.handle_status_name },
{ "判读状态", row.is_intact_name == null ? "" : row.is_intact_name },
{ "图斑类型", row.typename == null ? "" : row.typename },
{ "判定结果", row.is_illegal_name == null ? "" : row.is_illegal_name },
{ "图斑面积(亩)", row.area == null ? "" : row.area },
{ "耕地面积", row.gengdi_area == null ? "" : row.gengdi_area },
{ "永农面积", row.yongjiujibennongtian_area == null ? "" : row.yongjiujibennongtian_area },
{ "项目名称", row.xiangmumc == null ? "" : row.xiangmumc },
{ "违法类型", row.weifaleixing == null ? "" : row.weifaleixing },
{ "处理措施", row.measure_name == null ? "" : row.measure_name },
{ "创建时间", row.synchronoustime == null ? "" : row.synchronoustime.ToString() },
{ "图斑来源", row.tubanlaiyuan == null ? "" : row.tubanlaiyuan },
};
IFeature feature = new Feature(geometry, attributes);
features.Add(feature);
}
if (features.Count == 0)
{
throw new Exception("数据不可用");
}
// 导出 SHP 文件及其关联文件
ExportToShapefileFour(shpFilePath, features);
// 将文件打包成 ZIP
CreateZipFromShapefiles(shpFilePath, shpFilePathzip);
}
public void ExportToShapefileFour(string shpPath, List<IFeature> features)
{
//var geometryFactory = new GeometryFactory();
//// 写入 CPG 文件（定义字符编码）
//var cpgPath = System.IO.Path.ChangeExtension(shpPath, "cpg");
//File.WriteAllText(cpgPath, "UTF-8", Encoding.UTF8);
//// 获取 SHP 文件头
//var header = ShapefileDataWriter.GetHeader(features[0], features.Count);
//// 创建 ShapefileDataWriter
//var shapeFileWriter = new ShapefileDataWriter(shpPath, geometryFactory)
//{
// Header = header
//};
//// 写入 SHP 文件
//shapeFileWriter.Write(features);
// 写入 CPG 文件（定义字符编码）
string prjStr =
@"GEOGCS[""GCS_China_Geodetic_Coordinate_System_2000"",DATUM[""D_China_2000"",SPHEROID[""CGCS2000"",6378137.0,298.257222101]],PRIMEM[""Greenwich"",0.0],UNIT[""Degree"",0.0174532925199433]]";
var cpgPath = System.IO.Path.ChangeExtension(shpPath, "prj");
File.WriteAllText(cpgPath, prjStr, Encoding.UTF8);
NetTopologySuite.IO.Esri.Shapefile.WriteAllFeatures(features, shpPath, encoding: Encoding.UTF8);
}
public void CreateZipFromShapefiles(string shpPath, string zipPath)
{
var files = new List<string>
{
System.IO.Path.ChangeExtension(shpPath, "cpg"),
shpPath,
System.IO.Path.ChangeExtension(shpPath, "shx"),
System.IO.Path.ChangeExtension(shpPath, "dbf"),
System.IO.Path.ChangeExtension(shpPath, "prj")
};
using (var zipArchive = ZipFile.Open(zipPath, ZipArchiveMode.Create))
{
foreach (var file in files)
{
if (File.Exists(file))
{
zipArchive.CreateEntryFromFile(file, System.IO.Path.GetFileName(file));
}
}
}
}
public Geometry ParseGeometry(string wkt)
{
if (string.IsNullOrEmpty(wkt))
{
return null;
}
var reader = new WKTReader();
return reader.Read(wkt);
}
#endregion
#region 使用cmd导出shp文件
//public void ExportToShapefileByCmd(QueryCaseOffencetReq req, string shpFilePath, string shpFilePathzip)
//{
// var response = ExprotCaseListByType(req).Result;
// List<int> gids = response.SelectMany(item =>
// item.geomid.Split(',')
// .Select(id => int.TryParse(id, out var gid) ? gid : (int?)null)
// .Where(gid => gid.HasValue)
// .Select(gid => gid.Value)).ToList();
// string gidsString = string.Join(",", gids);
// //参数
// string _path = _configuration.GetSection("PgSql:PostGisPath").Value;
// string DataBase = _configuration.GetSection("PgSql:DataBase").Value;
// string UserID = _configuration.GetSection("PgSql:UserID").Value;
// string PORT = _configuration.GetSection("PgSql:PORT").Value;
// string Host = _configuration.GetSection("PgSql:Host").Value;
// string Pwd = _configuration.GetSection("PgSql:Pwd").Value;
// string strDir = "pgsql2shp -f " + shpFilePath + " -h " + Host + " -u " + UserID + " -P " + Pwd + " -p " + PORT + " " + DataBase + " \"SELECT * FROM view_drone_shp_data WHERE gid IN (" + gidsString + ")\"";
// //调用cmd
// CommonData.ExeCmdProcess(strDir, _path);
// // 将文件打包成 ZIP
// CreateZipFromShapefiles(shpFilePath, shpFilePathzip);
//}
#endregion
#region 添加图片信息
public Response<bool> AddCaseImg(List<DroneImageRef> imgList)
{
try
{
//var imgList = Newtonsoft.Json.JsonConvert.DeserializeObject<List<DroneImageRef>>(info);
client.Insertable<DroneImageRef>(imgList).ExecuteCommand();
return new Response<bool>
{
Result = true,
Message = "添加成功",
};
}
catch (Exception ex)
{
return new Response<bool>
{
Result = false,
Message = "添加失败",
};
}
}
/// <summary>
/// 获取图片信息
/// </summary>
/// <param name="caseid"></param>
/// <returns></returns>
public async Task<Response<List<DroneImageRef>>> LoadCaseImgList(string caseid, string category)
{
List<string> imageList = new List<string>();
RefAsync<int> totalcount = 0;
switch (category)
{
case "设施农用地":
//查询案件的填报(anjianzhaopian)和整改图片信息(chaichufugenghoupic),补办手续（bubanzhaopian）
var caseImg0 = await client.Queryable<DroneSsnyd>().FirstAsync(r => r.Id == caseid);
if (caseImg0 != null)
{
// 合并
AddImagesToList(caseImg0.wjzp_kg, imageList);
AddImagesToList(caseImg0.njzp_kg, imageList);
AddImagesToList(caseImg0.wjzp_kgzg, imageList);
AddImagesToList(caseImg0.njzp_kgzg, imageList);
AddImagesToList(caseImg0.wjzp_jz, imageList);
AddImagesToList(caseImg0.njzp_jz, imageList);
AddImagesToList(caseImg0.wjzp_jzzg, imageList);
AddImagesToList(caseImg0.njzp_jzzg, imageList);
AddImagesToList(caseImg0.wjzp_wg, imageList);
AddImagesToList(caseImg0.njzp_wg, imageList);
AddImagesToList(caseImg0.wjzp_wgzg, imageList);
AddImagesToList(caseImg0.njzp_wgzg, imageList);
List<DroneSsnydRcjg> subs = await client.Queryable<DroneSsnydRcjg>()
.Where(r => r.ssnyd_id == caseid).ToListAsync();
foreach (var sub in subs)
{
AddImagesToList(sub.wjzp_rc, imageList);
AddImagesToList(sub.njzp_rc, imageList);
AddImagesToList(sub.wjzp_rczg, imageList);
AddImagesToList(sub.njzp_rczg, imageList);
AddImagesToList(sub.wjzp_rcfh, imageList);
AddImagesToList(sub.njzp_rcfh, imageList);
}
}
break;
case "违法用地":
//查询案件的填报(anjianzhaopian)和整改图片信息(chaichufugenghoupic),补办手续（bubanzhaopian）
var caseImg1 = await client.Queryable<DroneCaseInfoSingle>().FirstAsync(r => r.Id == caseid);
if (caseImg1 != null)
{
// 合并
AddImagesToList(caseImg1.anjianzhaopian, imageList);
AddImagesToList(caseImg1.chaichufugenghoupic, imageList);
AddImagesToList(caseImg1.bubanzhaopian, imageList);
}
break;
case "非法采矿卫片下发":
var caseImg2 = await client.Queryable<DroneCaseInfoSatellite>().FirstAsync(r => r.Id == caseid);
if (caseImg2 != null)
{
// 合并
AddImagesToList(caseImg2.anjianzhaopian, imageList);
AddImagesToList(caseImg2.chaichufugenghoupic, imageList);
AddImagesToList(caseImg2.bubanzhaopian, imageList);
}
break;
case "非法采矿工作管理":
var caseImg3 = await client.Queryable<DroneCaseInfoMinerals>().FirstAsync(r => r.Id == caseid);
if (caseImg3 != null)
{
// 合并
AddImagesToList(caseImg3.zhaopian_fill, imageList);
AddImagesToList(caseImg3.zhaopianqian_jgzhg, imageList);
AddImagesToList(caseImg3.zhaopianhou_jgzhg, imageList);
AddImagesToList(caseImg3.zhaopian_reform, imageList);
//AddImagesToList(caseImg3.waiyepaizhao, imageList);
}
break;
case "耕地非粮化":
var caseImg4 = await client.Queryable<DroneCaseInfoFLH>().FirstAsync(r => r.Id == caseid);
if (caseImg4 != null)
{
// 合并
AddImagesToList(caseImg4.anjianzhaopian, imageList);
AddImagesToList(caseImg4.chaichufugenghoupic, imageList);
AddImagesToList(caseImg4.bubanzhaopian, imageList);
}
break;
case "重点问题Ⅰ类":
var caseImg5 = await client.Queryable<DroneCaseInfoZdwt1>().FirstAsync(r => r.Id == caseid);
if (caseImg5 != null)
{
// 合并
AddImagesToList(caseImg5.anjianzhaopian, imageList);
}
break;
case "重点问题Ⅱ类":
var caseImg6 = await client.Queryable<DroneCaseInfoZdwt2>().FirstAsync(r => r.Id == caseid);
if (caseImg6 != null)
{
// 合并
AddImagesToList(caseImg6.anjianzhaopian, imageList);
}
break;
case "巡察审计":
//查询案件的填报(anjianzhaopian)和整改图片信息(chaichufugenghoupic),补办手续（bubanzhaopian）
var caseImg7 = await client.Queryable<DroneCaseInfoXcsj>().FirstAsync(r => r.Id == caseid);
if (caseImg7 != null)
{
// 合并
AddImagesToList(caseImg7.anjianzhaopian, imageList);
AddImagesToList(caseImg7.chaichufugenghoupic, imageList);
AddImagesToList(caseImg7.bubanzhaopian, imageList);
}
break;
}
var caseList = await client.Queryable<DroneImageRef>()
.WhereIF(!string.IsNullOrEmpty(caseid), r => r.CaseId == caseid)
.Where(r => imageList.Contains(r.FilePath))
.Select(r => new DroneImageRef()
{
Id = r.Id.SelectAll(),
})
.ToListAsync();
return new Response<List<DroneImageRef>>()
{
Result = caseList
};
}
void AddImagesToList(string images, List<string> list)
{
if (!string.IsNullOrEmpty(images))
{
list.AddRange(images.Split(',').Where(item => !string.IsNullOrWhiteSpace(item)));
}
}
public Response<bool> DeleteCaseImg(string path, string fieldName, string caseid, string dataTable)
{
try
{
client.Ado.BeginTran();
//删除相位角图片数据
client.Deleteable<DroneImageRef>(r => r.FilePath == path).ExecuteCommand();
//更新案件信息
path = path.Replace(@"\", @"\\");
string sql = "UPDATE " + dataTable + " SET " + fieldName + " = regexp_replace(" + fieldName + ", ',?" +
path + "', '','g') WHERE \"Id\" = '" + caseid + "'";
//path = path + ",";
//string sql = "UPDATE " + dataTable + " SET " + fieldName + " = REPLACE(" + fieldName + ", '" + path + "', '') WHERE \"Id\" = '" + caseid + "'";
var a = client.Ado.ExecuteCommand(sql);
client.Ado.CommitTran();
return new Response<bool>
{
Result = true,
Message = "删除成功",
};
}
catch (Exception ex)
{
client.Ado.RollbackTran();
return new Response<bool>
{
Result = false,
Message = "删除失败",
};
}
}
#endregion
#region 获取图斑信息
/// <summary>
/// 获取图片信息
/// </summary>
/// <param name="caseid"></param>
/// <returns></returns>
public async Task<Response<List<DroneShpData>>> LoadDroneShpDataById(string caseid)
{
RefAsync<int> totalcount = 0;
var caseList = await client
.SqlQueryable<DroneShpData>("select st_astext(geom) as geom from drone_shp_data where relid = '" + caseid +
"'")
.ToListAsync();
return new Response<List<DroneShpData>>()
{
Result = caseList
};
}
/// <summary>
/// 获取图斑中心点数据
/// </summary>
/// <param name="tablename"></param>
/// <param name="filter"></param>
/// <returns></returns>
public dynamic GetCenterPoints(string tablename, string filter)
{
StringBuilder sql = new StringBuilder();
if (string.IsNullOrEmpty(filter))
{
filter = "1=1";
}
sql.AppendFormat(
$" SELECT gid,ST_AsText(ST_Transform(ST_Centroid(geom), 4326)) AS centroid_point \r\nFROM {tablename} where {filter}");
var result = client.Ado.SqlQuery<dynamic>(sql.ToString());
return result;
}
/// <summary>
/// 根据表名获取矢量切片
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public byte[] QueryVectorTileByTable(QueryVectorTileByTableReq req)
{
VectorTileSearchModel searchModel = new VectorTileSearchModel
{
x = req.x,
y = req.y,
z = req.z,
//field = "gid,fwbh,bh,fwlx,\"jzfsQg\",bxdcyy,fwlb,\"kzgzcsQg\",aqjdnf,address,fwsjfs,jgsj,cs,\"jzndQg\",dcmj,ywlfbxqx,sfjgaqjd,sfkzjg,\"kzcszpQg\",\"houseType\",\"hzlxQg\",hzxm,\"jglxQg\",aqjdjl,\"czqtQg\",jzmc,\"lwgQg\",\"dbkjqtQg\",fwmj,fwyt,zff,\"jzytQg\",streetid,streetname,communityid,communityname,",
field = req.field,
table = req.table,
filter = req.filter,
};
var result = _commonDataManager.VectorTile(searchModel);
return result;
}
/// <summary>
/// 根据表名获取矢量切片
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<byte[]> QueryVectorTileByTableAsync(QueryVectorTileByTableReq req)
{
VectorTileSearchModel searchModel = new VectorTileSearchModel
{
x = req.x,
y = req.y,
z = req.z,
//field = "gid,fwbh,bh,fwlx,\"jzfsQg\",bxdcyy,fwlb,\"kzgzcsQg\",aqjdnf,address,fwsjfs,jgsj,cs,\"jzndQg\",dcmj,ywlfbxqx,sfjgaqjd,sfkzjg,\"kzcszpQg\",\"houseType\",\"hzlxQg\",hzxm,\"jglxQg\",aqjdjl,\"czqtQg\",jzmc,\"lwgQg\",\"dbkjqtQg\",fwmj,fwyt,zff,\"jzytQg\",streetid,streetname,communityid,communityname,",
field = req.field,
table = req.table,
filter = req.filter,
};
var result = await _commonDataManager.VectorTileAsync(searchModel);
return result;
}
#endregion
#region 案件操作历史记录
/// <summary>
/// 获取所有的案件信息
/// </summary>
/// <param name="req">案件编号查询</param>
/// <returns></returns>
public async Task<Response<PageInfo<List<DroneCaseInfoSingle>>>> LoadCaseInfoListForUpdate(
DroneCaseForChangeReq req)
{
RefAsync<int> totalcount = 0;
//var caseList = await client.Queryable<DroneCaseInfoSingle>()
// .WhereIF(!string.IsNullOrEmpty(req.key), r => r.case_no.Contains(req.key))
// .OrderByDescending(r => r.createtime)
// .Select(r => new CaseInfoForUpdateResp
// {
// Id = r.Id,
// case_no = r.case_no,
// case_name = r.case_name,
// case_description = r.case_description,
// address = r.address,
// lng = r.lng,
// lat = r.lat,
// typeid = r.typeid,
// typename = r.typename,
// drone_no = r.drone_no,
// countyid = r.countyid,
// countyname = r.countyname,
// streetid = r.streetid,
// streetname = r.streetname,
// communityid = r.communityid,
// communityname = r.communityname,
// remark = r.remark,
// area = r.area,
// nongyongdi_area = r.nongyongdi_area,
// gengdi_area = r.gengdi_area,
// yongjiujibennongtian_area = r.yongjiujibennongtian_area,
// zhongdianquyu_area = r.zhongdianquyu_area,
// shengtaibaohuhongxian_area = r.shengtaibaohuhongxian_area,
// guotukongjianguihua_area = r.guotukongjianguihua_area,
// })
// .ToPageListAsync(req.page, req.limit, totalcount);
var caseList = await client.Queryable<DroneCaseInfoSingle>()
.WhereIF(!string.IsNullOrEmpty(req.key), r => r.case_no.Contains(req.key))
.WhereIF(!string.IsNullOrEmpty(req.originalcaseno), r => r.original_case_no.Contains(req.originalcaseno))
.Where(r => r.handle_status_id < 4 && r.is_closed == 0)
.OrderByDescending(r => r.createtime)
.Select(r => new DroneCaseInfoSingle
{
Id = r.Id.SelectAll(),
})
.ToPageListAsync(req.page, req.limit, totalcount);
return new Response<PageInfo<List<DroneCaseInfoSingle>>>()
{
Result = new PageInfo<List<DroneCaseInfoSingle>>
{
Items = caseList,
Total = totalcount
}
};
}
/// <summary>
/// 获取单个案件信息
/// </summary>
/// <param name="id">案件id</param>
/// <returns></returns>
public async Task<Response<DroneCaseInfoSingle>> LoadCaseInfoById(string id)
{
var caseInfo = await client.Queryable<DroneCaseInfoSingle>().FirstAsync(r => r.Id == id);
return new Response<DroneCaseInfoSingle>()
{
Result = caseInfo
};
}
/// <summary>
/// 获取案件历史信息
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<PageInfo<List<DroneCaseInfoSingleHistory>>>> LoadCaseHistoryInfoList(PageReq req)
{
RefAsync<int> totalcount = 0;
var caseList = await client.Queryable<DroneCaseInfoSingleHistory>()
.WhereIF(!string.IsNullOrEmpty(req.key), r => r.Id == req.key)
.OrderByDescending(r => r.recordingtime)
.ToPageListAsync(req.page, req.limit, totalcount);
return new Response<PageInfo<List<DroneCaseInfoSingleHistory>>>()
{
Result = new PageInfo<List<DroneCaseInfoSingleHistory>>
{
Items = caseList,
Total = totalcount
}
};
}
//修改案件信息
public async Task<Response<bool>> UpdateCaseInfo(DroneCaseInfoSingle req, string shppath)
{
using (var db = base.UnitWork.CreateContext())
{
StringBuilder geom = new StringBuilder();
if (!string.IsNullOrEmpty(shppath))
{
geom = await ReadShapefile(shppath, req.Id);
}
// 使用事务提交数据
// 记录案件信息
var caseInfo = await db.DroneCaseInfoSingle.GetFirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
{
throw new Exception("图斑信息未找到");
}
var caseHistory = caseInfo.MapTo<DroneCaseInfoSingleHistory>();
caseHistory.newid = Guid.NewGuid().ToString();
caseHistory.recordingtime = DateTime.Now;
caseHistory.recordingpeople = _auth.GetUserId();
await db.DroneCaseInfoSingleHistory.InsertAsync(caseHistory);
req.nongyongdi_area = string.IsNullOrEmpty(req.nongyongdi_area)
? "0"
: Math.Round(double.Parse(req.nongyongdi_area), 2).ToString();
req.area = string.IsNullOrEmpty(req.area) ? "0" : Math.Round(double.Parse(req.area), 2).ToString();
req.gengdi_area = string.IsNullOrEmpty(req.gengdi_area)
? "0"
: Math.Round(double.Parse(req.gengdi_area), 2).ToString();
req.yongjiujibennongtian_area = string.IsNullOrEmpty(req.yongjiujibennongtian_area)
? "0"
: Math.Round(double.Parse(req.yongjiujibennongtian_area), 2).ToString();
req.zhongdianquyu_area = string.IsNullOrEmpty(req.zhongdianquyu_area)
? "0"
: Math.Round(double.Parse(req.zhongdianquyu_area), 2).ToString();
req.shengtaibaohuhongxian_area = string.IsNullOrEmpty(req.shengtaibaohuhongxian_area)
? "0"
: Math.Round(double.Parse(req.shengtaibaohuhongxian_area), 2).ToString();
req.guotukongjianguihua_area = string.IsNullOrEmpty(req.guotukongjianguihua_area)
? "0"
: Math.Round(double.Parse(req.guotukongjianguihua_area), 2).ToString();
// 更新案件信息
await db.DroneCaseInfoSingle.UpdateAsync(it => new DroneCaseInfoSingle()
{
case_no = req.case_no,
case_description = req.case_description,
address = req.address,
//typeid = req.typeid,
//typename = req.typename,
countyid = req.countyid,
countyname = req.countyname,
streetid = req.streetid,
streetname = req.streetname,
communityid = req.communityid,
communityname = req.communityname,
area = req.area,
casepic = req.casepic,
nongyongdi_area = req.nongyongdi_area,
gengdi_area = req.gengdi_area,
yongjiujibennongtian_area = req.yongjiujibennongtian_area,
zhongdianquyu_area = req.zhongdianquyu_area,
shengtaibaohuhongxian_area = req.shengtaibaohuhongxian_area,
guotukongjianguihua_area = req.guotukongjianguihua_area,
remark = req.remark,
}, it => it.Id == req.Id);
if (!string.IsNullOrEmpty(shppath))
{
//记录历史 多图斑数据未处理
var shpdata = await db.DroneShpData.GetFirstAsync(r => r.relid == req.Id);
var shpHistory = shpdata.MapTo<DroneShpDataHistory>();
shpHistory.id = Guid.NewGuid().ToString();
shpHistory.recordingtime = DateTime.Now;
shpHistory.recordingpeople = _auth.GetUserId();
await db.DroneShpDataHistory.InsertAsync(shpHistory);
await db.Db.Ado.ExecuteCommandAsync(geom.ToString());
}
var flag = db.Commit();
return new Response<bool>
{
Code = 200,
Result = flag,
Message = flag ? "更新成功" : "更新失败"
};
}
}
private async Task<StringBuilder> ReadShapefile(string zipFilePath, string Id)
{
var extractPath = zipFilePath.Substring(0, zipFilePath.LastIndexOf(".")) + "extract";
//解压缩
await UnZip(zipFilePath, extractPath);
var searchPattern = "*.shp"; // 设置你想要遍历的文件后缀名
var fileName = Directory.GetFiles(extractPath, searchPattern, SearchOption.AllDirectories);
string shpFileName = fileName[0];
// 创建一个 ShapefileDataReader 对象
using (var reader = new ShapefileDataReader(shpFileName, GeometryFactory.Default))
{
StringBuilder geomSql = new StringBuilder();
while (reader.Read())
{
// 获取几何数据
Geometry geometry = reader.Geometry;
if (!geometry.IsValid)
{
throw new Exception("图斑未闭合，请重新整理数据");
}
// 如果几何数据是一个多边形（Polygon），需要转换为 MultiPolygon
MultiPolygon multiPolygon;
if (geometry is Polygon polygon)
{
// 单个Polygon转MultiPolygon
multiPolygon = new MultiPolygon(new[] { polygon });
}
else if (geometry is MultiPolygon existingMultiPolygon)
{
// 如果已经是MultiPolygon，直接使用
multiPolygon = existingMultiPolygon;
}
else
{
// 如果是其他类型的几何图形，可以根据需求进行处理
throw new Exception("几何图形不是多边形或多重多边形类型");
}
// 将几何数据转换为 WKT 格式，并用 SRID 4326
var geometryForWgs84 = multiPolygon.Copy();
geometryForWgs84.SRID = 4326;
//var geometryForWgs84 = GeometryFactory.Default.WithSRID(4326).CreateGeometry(geometry);
geomSql.AppendFormat(
$" update drone_shp_data set geom = '{geometryForWgs84.AsText()}' where \"relid\" = '{Id}';");
}
return geomSql;
}
}
#region 多图斑案件修改
public async Task<Response<bool>> UpdateCaseInfoMore(DroneCaseInfoSingle req, string shppath)
{
using (var db = base.UnitWork.CreateContext())
{
// 记录案件信息
var caseInfo = await db.DroneCaseInfoSingle.GetFirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
{
throw new Exception("图斑信息未找到");
}
var caseHistory = caseInfo.MapTo<DroneCaseInfoSingleHistory>();
caseHistory.newid = Guid.NewGuid().ToString();
caseHistory.recordingtime = DateTime.Now;
caseHistory.recordingpeople = _auth.GetUserId();
await db.DroneCaseInfoSingleHistory.InsertAsync(caseHistory);
req.nongyongdi_area = string.IsNullOrEmpty(req.nongyongdi_area)
? "0"
: Math.Round(double.Parse(req.nongyongdi_area), 2).ToString();
req.area = string.IsNullOrEmpty(req.area) ? "0" : Math.Round(double.Parse(req.area), 2).ToString();
req.gengdi_area = string.IsNullOrEmpty(req.gengdi_area)
? "0"
: Math.Round(double.Parse(req.gengdi_area), 2).ToString();
req.yongjiujibennongtian_area = string.IsNullOrEmpty(req.yongjiujibennongtian_area)
? "0"
: Math.Round(double.Parse(req.yongjiujibennongtian_area), 2).ToString();
req.zhongdianquyu_area = string.IsNullOrEmpty(req.zhongdianquyu_area)
? "0"
: Math.Round(double.Parse(req.zhongdianquyu_area), 2).ToString();
req.shengtaibaohuhongxian_area = string.IsNullOrEmpty(req.shengtaibaohuhongxian_area)
? "0"
: Math.Round(double.Parse(req.shengtaibaohuhongxian_area), 2).ToString();
req.guotukongjianguihua_area = string.IsNullOrEmpty(req.guotukongjianguihua_area)
? "0"
: Math.Round(double.Parse(req.guotukongjianguihua_area), 2).ToString();
// 更新案件信息
await db.DroneCaseInfoSingle.UpdateAsync(it => new DroneCaseInfoSingle()
{
case_no = req.case_no,
case_description = req.case_description,
address = req.address,
//typeid = req.typeid,
//typename = req.typename,
countyid = req.countyid,
countyname = req.countyname,
streetid = req.streetid,
streetname = req.streetname,
communityid = req.communityid,
communityname = req.communityname,
area = req.area,
casepic = req.casepic,
nongyongdi_area = req.nongyongdi_area,
gengdi_area = req.gengdi_area,
yongjiujibennongtian_area = req.yongjiujibennongtian_area,
zhongdianquyu_area = req.zhongdianquyu_area,
shengtaibaohuhongxian_area = req.shengtaibaohuhongxian_area,
guotukongjianguihua_area = req.guotukongjianguihua_area,
remark = req.remark,
}, it => it.Id == req.Id);
if (!string.IsNullOrEmpty(shppath))
{
var geomList = await ReadShapefileMore(shppath);
//记录历史 多图斑数据未处理
var shpdata = await db.DroneShpData.GetListAsync(r => r.relid == req.Id);
if (geomList.Count != shpdata.Count)
{
throw new Exception("图斑数据信息不匹配");
}
for (int i = 0; i < shpdata.Count; i++)
{
shpdata[i].geom = geomList[i];
var shpHistory = shpdata[i].MapTo<DroneShpDataHistory>();
shpHistory.id = Guid.NewGuid().ToString();
shpHistory.recordingtime = DateTime.Now;
shpHistory.recordingpeople = _auth.GetUserId();
await db.DroneShpDataHistory.InsertAsync(shpHistory);
await db.DroneShpData.UpdateAsync(shpdata[i]);
}
}
var flag = db.Commit();
return new Response<bool>
{
Code = 200,
Result = flag,
Message = flag ? "更新成功" : "更新失败"
};
}
}
private async Task<List<string>> ReadShapefileMore(string zipFilePath)
{
var extractPath = zipFilePath.Substring(0, zipFilePath.LastIndexOf(".")) + "extract";
//解压缩
await UnZip(zipFilePath, extractPath);
var searchPattern = "*.shp"; // 设置你想要遍历的文件后缀名
var fileName = Directory.GetFiles(extractPath, searchPattern, SearchOption.AllDirectories);
string shpFileName = fileName[0];
// 创建一个 ShapefileDataReader 对象
using (var reader = new ShapefileDataReader(shpFileName, GeometryFactory.Default))
{
List<string> geomList = new List<string>();
while (reader.Read())
{
// 获取几何数据
Geometry geometry = reader.Geometry;
if (!geometry.IsValid)
{
throw new Exception("图斑未闭合，请重新整理数据");
}
// 如果几何数据是一个多边形（Polygon），需要转换为 MultiPolygon
MultiPolygon multiPolygon;
if (geometry is Polygon polygon)
{
// 单个Polygon转MultiPolygon
multiPolygon = new MultiPolygon(new[] { polygon });
}
else if (geometry is MultiPolygon existingMultiPolygon)
{
// 如果已经是MultiPolygon，直接使用
multiPolygon = existingMultiPolygon;
}
else
{
// 如果是其他类型的几何图形，可以根据需求进行处理
throw new Exception("几何图形不是多边形或多重多边形类型");
}
// 将几何数据转换为 WKT 格式，并用 SRID 4326
var geometryForWgs84 = multiPolygon.Copy();
geometryForWgs84.SRID = 4326;
geomList.Add(geometryForWgs84.AsText());
}
return geomList;
}
}
#endregion
private static async Task UnZip(string zipFilePath, string extractPath)
{
await Task.Run(() =>
{
Directory.CreateDirectory(extractPath);
// 设置字符编码
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
var gbk = Encoding.GetEncoding("utf-8");
using (var archive = ZipFile.OpenRead(zipFilePath))
{
// 列举ZIP文件中的条目
foreach (var entry in archive.Entries)
{
var xxx = gbk.GetString(Encoding.Default.GetBytes(entry.FullName));
Console.WriteLine(xxx);
}
// 提取ZIP文件中的所有文件到指定目录
foreach (var entry in archive.Entries)
{
if (entry.Name != string.Empty)
{
// 确保完整路径存在 entry.FullName 是否可以编码
var fixedEntryName = entry.FullName.Replace("/", "");
var destinationPath =
System.IO.Path.GetFullPath(System.IO.Path.Combine(extractPath, fixedEntryName));
Console.WriteLine("解压文件路径：" + destinationPath);
if (!destinationPath.StartsWith(System.IO.Path.GetFullPath(extractPath) +
System.IO.Path.DirectorySeparatorChar))
{
throw new UnauthorizedAccessException("试图提取的文件超出了目标文件夹的路径边界。");
}
// 提取条目到目标路径
entry.ExtractToFile(destinationPath, overwrite: true);
}
}
}
// 遍历解压目录，是否有shp后缀的文件
if (!Directory.Exists(extractPath))
{
throw new Exception("文件解压失败");
}
});
}
/// <summary>
/// 修改案件信息
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public async Task<Response<bool>> UpdateCaseInfoForAll(UpdateDroneCaseDataForAllReq req)
{
//_logger.LogError("保存表单数据接收: " + Newtonsoft.Json.JsonConvert.SerializeObject(req));
switch (req.subject)
{
case "违法用地":
using (var db = UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoSingle.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
await db.DroneCaseInfoSingle.UpdateAsync(it => new DroneCaseInfoSingle()
{
case_no = req.case_no,
case_description = req.case_description,
address = req.address,
typeid = req.typeid,
typename = req.typename,
countyid = req.countyid,
countyname = req.countyname,
streetid = req.streetid,
streetname = req.streetname,
communityid = req.communityid,
communityname = req.communityname,
area = req.area,
casepic = req.casepic,
nongyongdi_area = req.nongyongdi_area,
gengdi_area = req.gengdi_area,
yongjiujibennongtian_area = req.yongjiujibennongtian_area,
zhongdianquyu_area = req.zhongdianquyu_area,
shengtaibaohuhongxian_area = req.shengtaibaohuhongxian_area,
guotukongjianguihua_area = req.guotukongjianguihua_area,
remark = req.remark,
tubanlaiyuan = req.tubanlaiyuan,
}, it => it.Id == req.Id);
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
case "耕地非粮化":
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoFLH.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
await db.DroneCaseInfoFLH.UpdateAsync(it => new DroneCaseInfoFLH()
{
case_no = req.case_no,
case_description = req.case_description,
address = req.address,
typeid = req.typeid,
typename = req.typename,
countyid = req.countyid,
countyname = req.countyname,
streetid = req.streetid,
streetname = req.streetname,
communityid = req.communityid,
communityname = req.communityname,
area = req.area,
casepic = req.casepic,
nongyongdi_area = req.nongyongdi_area,
gengdi_area = req.gengdi_area,
yongjiujibennongtian_area = req.yongjiujibennongtian_area,
zhongdianquyu_area = req.zhongdianquyu_area,
shengtaibaohuhongxian_area = req.shengtaibaohuhongxian_area,
guotukongjianguihua_area = req.guotukongjianguihua_area,
remark = req.remark,
tubanlaiyuan = req.tubanlaiyuan,
}, it => it.Id == req.Id);
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
case "巡察审计":
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoXcsj.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
await db.DroneCaseInfoXcsj.UpdateAsync(it => new DroneCaseInfoXcsj()
{
case_no = req.case_no,
case_description = req.case_description,
address = req.address,
typeid = req.typeid,
typename = req.typename,
countyid = req.countyid,
countyname = req.countyname,
streetid = req.streetid,
streetname = req.streetname,
communityid = req.communityid,
communityname = req.communityname,
area = req.area,
casepic = req.casepic,
nongyongdi_area = req.nongyongdi_area,
gengdi_area = req.gengdi_area,
yongjiujibennongtian_area = req.yongjiujibennongtian_area,
zhongdianquyu_area = req.zhongdianquyu_area,
shengtaibaohuhongxian_area = req.shengtaibaohuhongxian_area,
guotukongjianguihua_area = req.guotukongjianguihua_area,
remark = req.remark,
tubanlaiyuan = req.tubanlaiyuan,
}, it => it.Id == req.Id);
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
case "重点问题Ⅰ类":
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoZdwt1.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
await db.DroneCaseInfoZdwt1.UpdateAsync(it => new DroneCaseInfoZdwt1()
{
case_no = req.case_no,
case_description = req.case_description,
address = req.address,
typeid = req.typeid,
typename = req.typename,
countyid = req.countyid,
countyname = req.countyname,
streetid = req.streetid,
streetname = req.streetname,
communityid = req.communityid,
communityname = req.communityname,
area = req.area,
casepic = req.casepic,
nongyongdi_area = req.nongyongdi_area,
gengdi_area = req.gengdi_area,
yongjiujibennongtian_area = req.yongjiujibennongtian_area,
zhongdianquyu_area = req.zhongdianquyu_area,
shengtaibaohuhongxian_area = req.shengtaibaohuhongxian_area,
guotukongjianguihua_area = req.guotukongjianguihua_area,
remark = req.remark,
tubanlaiyuan = req.tubanlaiyuan,
//countyusable=req.countyusable
}, it => it.Id == req.Id);
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
case "重点问题Ⅱ类":
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoZdwt2.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
await db.DroneCaseInfoZdwt2.UpdateAsync(it => new DroneCaseInfoZdwt2()
{
case_no = req.case_no,
case_description = req.case_description,
address = req.address,
typeid = req.typeid,
typename = req.typename,
countyid = req.countyid,
countyname = req.countyname,
streetid = req.streetid,
streetname = req.streetname,
communityid = req.communityid,
communityname = req.communityname,
area = req.area,
casepic = req.casepic,
nongyongdi_area = req.nongyongdi_area,
gengdi_area = req.gengdi_area,
yongjiujibennongtian_area = req.yongjiujibennongtian_area,
zhongdianquyu_area = req.zhongdianquyu_area,
shengtaibaohuhongxian_area = req.shengtaibaohuhongxian_area,
guotukongjianguihua_area = req.guotukongjianguihua_area,
remark = req.remark,
tubanlaiyuan = req.tubanlaiyuan,
//countyusable = req.countyusable
}, it => it.Id == req.Id);
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
case "非法采矿":
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoMinerals.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
await db.DroneCaseInfoMinerals.UpdateAsync(it => new DroneCaseInfoMinerals()
{
case_no = req.case_no,
case_description = req.case_description,
//address = req.address,
//typeid = req.typeid,
typename = req.typename,
countyid = req.countyid,
countyname = req.countyname,
streetid = req.streetid,
streetname = req.streetname,
communityid = req.communityid,
communityname = req.communityname,
weifaarea_base = req.area,
tubanpic_base = req.tubanpic_base,
gengdi_area = req.gengdi_area,
jbntarea_base = req.yongjiujibennongtian_area,
stbhhxarea_base = req.shengtaibaohuhongxian_area,
cskfbjarea_base = req.cskfbjarea_base,
weifakaicaiarea_base = req.weifakaicaiarea_base,
weifazhandiarea_base = req.weifazhandiarea_base,
syear_base = req.syear_base,
zhongdianflag_base = req.zhongdianflag_base,
zhongdianname_base = req.zhongdianname_base,
ziranbaohuflag_base = req.ziranbaohuflag_base,
ziranbaohuname_base = req.ziranbaohuname_base,
tubanlaiyuan = req.tubanlaiyuan,
}, it => it.Id == req.Id);
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
case "生态保护红线监管":
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoSTHX.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
await db.DroneCaseInfoSTHX.UpdateAsync(it => new DroneCaseInfoSTHX()
{
case_no = req.case_no,
case_description = req.case_description,
address = req.address,
typeid = req.typeid,
typename = req.typename,
countyid = req.countyid,
countyname = req.countyname,
streetid = req.streetid,
streetname = req.streetname,
communityid = req.communityid,
communityname = req.communityname,
area = req.area,
casepic = req.casepic,
nongyongdi_area = req.nongyongdi_area,
gengdi_area = req.gengdi_area,
yongjiujibennongtian_area = req.yongjiujibennongtian_area,
zhongdianquyu_area = req.zhongdianquyu_area,
shengtaibaohuhongxian_area = req.shengtaibaohuhongxian_area,
guotukongjianguihua_area = req.guotukongjianguihua_area,
remark = req.remark,
tubanlaiyuan = req.tubanlaiyuan,
//countyusable=req.countyusable
}, it => it.Id == req.Id);
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
case "生态修复":
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoSTXF.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
await db.DroneCaseInfoSTXF.UpdateAsync(it => new DroneCaseInfoSTXF()
{
case_no = req.case_no,
case_description = req.case_description,
address = req.address,
typeid = req.typeid,
typename = req.typename,
countyid = req.countyid,
countyname = req.countyname,
streetid = req.streetid,
streetname = req.streetname,
communityid = req.communityid,
communityname = req.communityname,
area = req.area,
casepic = req.casepic,
nongyongdi_area = req.nongyongdi_area,
gengdi_area = req.gengdi_area,
yongjiujibennongtian_area = req.yongjiujibennongtian_area,
zhongdianquyu_area = req.zhongdianquyu_area,
shengtaibaohuhongxian_area = req.shengtaibaohuhongxian_area,
guotukongjianguihua_area = req.guotukongjianguihua_area,
remark = req.remark,
tubanlaiyuan = req.tubanlaiyuan,
//countyusable=req.countyusable
}, it => it.Id == req.Id);
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
default:
return new Response<bool> { Result = false, Code = 500, Message = "暂无该专题" };
}
}
/// <summary>
/// 修改案件信息
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public async Task<Response<bool>> UpdateCaseNo(UpdateDroneCaseNoReq req)
{
string caseno;
bool isUnique;
Random random = new Random();
switch (req.subject)
{
case "违法用地":
using (var db = UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoSingle.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
if (!string.IsNullOrEmpty(caseInfo.case_no))
{
await db.DroneCaseInfoSingle.UpdateAsync(it => new DroneCaseInfoSingle()
{
synchronoustime = DateTime.Now
}, it => it.Id == req.Id);
}
else
{
do
{
caseno = "FN" + caseInfo.countyid + DateTime.Now.ToString("yyyyMMdd") +
random.Next(1000, 9999).ToString();
var count = await db.DroneCaseInfoSingle.AsQueryable().Where(c => c.case_no == caseno)
.CountAsync();
isUnique = count == 0;
} while (!isUnique);
await db.DroneCaseInfoSingle.UpdateAsync(it => new DroneCaseInfoSingle()
{
case_no = caseno,
synchronoustime = DateTime.Now
}, it => it.Id == req.Id);
}
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
case "耕地非粮化":
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoFLH.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
if (!string.IsNullOrEmpty(caseInfo.case_no))
{
await db.DroneCaseInfoFLH.UpdateAsync(it => new DroneCaseInfoFLH()
{
synchronoustime = DateTime.Now
}, it => it.Id == req.Id);
}
else
{
do
{
caseno = "FN" + caseInfo.countyid + DateTime.Now.ToString("yyyyMMdd") +
random.Next(1000, 9999).ToString();
var count = await db.DroneCaseInfoFLH.AsQueryable().Where(c => c.case_no == caseno)
.CountAsync();
isUnique = count == 0;
} while (!isUnique);
await db.DroneCaseInfoFLH.UpdateAsync(it => new DroneCaseInfoFLH()
{
case_no = caseno,
synchronoustime = DateTime.Now
}, it => it.Id == req.Id);
}
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
case "巡察审计":
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoXcsj.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
if (!string.IsNullOrEmpty(caseInfo.case_no))
{
await db.DroneCaseInfoXcsj.UpdateAsync(it => new DroneCaseInfoXcsj()
{
synchronoustime = DateTime.Now
}, it => it.Id == req.Id);
}
else
{
do
{
caseno = caseInfo.countyid + DateTime.Now.ToString("yyyyMMdd") +
random.Next(1000, 9999).ToString();
var count = await db.DroneCaseInfoXcsj.AsQueryable().Where(c => c.case_no == caseno)
.CountAsync();
isUnique = count == 0;
} while (!isUnique);
await db.DroneCaseInfoXcsj.UpdateAsync(it => new DroneCaseInfoXcsj()
{
case_no = caseno,
synchronoustime = DateTime.Now
}, it => it.Id == req.Id);
}
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
case "重点问题Ⅰ类":
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoZdwt1.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
if (!string.IsNullOrEmpty(caseInfo.case_no))
{
await db.DroneCaseInfoZdwt1.UpdateAsync(it => new DroneCaseInfoZdwt1()
{
synchronoustime = DateTime.Now
}, it => it.Id == req.Id);
}
else
{
do
{
caseno = "WJ" + caseInfo.countyid + DateTime.Now.ToString("yyyyMMdd") +
random.Next(1000, 9999).ToString();
var count = await db.DroneCaseInfoZdwt1.AsQueryable().Where(c => c.case_no == caseno)
.CountAsync();
isUnique = count == 0;
} while (!isUnique);
await db.DroneCaseInfoZdwt1.UpdateAsync(it => new DroneCaseInfoZdwt1()
{
case_no = caseno,
synchronoustime = DateTime.Now
}, it => it.Id == req.Id);
}
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
case "重点问题Ⅱ类":
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoZdwt2.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
if (!string.IsNullOrEmpty(caseInfo.case_no))
{
await db.DroneCaseInfoZdwt2.UpdateAsync(it => new DroneCaseInfoZdwt2()
{
synchronoustime = DateTime.Now
}, it => it.Id == req.Id);
}
else
{
do
{
caseno = "WJ" + caseInfo.countyid + DateTime.Now.ToString("yyyyMMdd") +
random.Next(1000, 9999).ToString();
var count = await db.DroneCaseInfoZdwt2.AsQueryable().Where(c => c.case_no == caseno)
.CountAsync();
isUnique = count == 0;
} while (!isUnique);
await db.DroneCaseInfoZdwt2.UpdateAsync(it => new DroneCaseInfoZdwt2()
{
case_no = caseno,
synchronoustime = DateTime.Now
}, it => it.Id == req.Id);
}
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
case "非法采矿":
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoMinerals.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
if (!string.IsNullOrEmpty(caseInfo.case_no))
{
await db.DroneCaseInfoMinerals.UpdateAsync(it => new DroneCaseInfoMinerals()
{
xiafatime_base = DateTime.Now
}, it => it.Id == req.Id);
}
else
{
do
{
caseno = "KC" + caseInfo.countyid + DateTime.Now.ToString("yyyyMMdd") +
random.Next(1000, 9999).ToString();
var count = await db.DroneCaseInfoMinerals.AsQueryable().Where(c => c.case_no == caseno)
.CountAsync();
isUnique = count == 0;
} while (!isUnique);
await db.DroneCaseInfoMinerals.UpdateAsync(it => new DroneCaseInfoMinerals()
{
case_no = caseno,
xiafatime_base = DateTime.Now
}, it => it.Id == req.Id);
}
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
case "生态保护红线监管":
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoSTHX.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
if (!string.IsNullOrEmpty(caseInfo.case_no))
{
await db.DroneCaseInfoSTHX.UpdateAsync(it => new DroneCaseInfoSTHX()
{
synchronoustime = DateTime.Now
}, it => it.Id == req.Id);
}
else
{
do
{
caseno = caseInfo.countyid + DateTime.Now.ToString("yyyyMMdd") +
random.Next(1000, 9999).ToString();
var count = await db.DroneCaseInfoSTHX.AsQueryable().Where(c => c.case_no == caseno)
.CountAsync();
isUnique = count == 0;
} while (!isUnique);
await db.DroneCaseInfoSTHX.UpdateAsync(it => new DroneCaseInfoSTHX()
{
case_no = caseno,
synchronoustime = DateTime.Now
}, it => it.Id == req.Id);
}
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
case "生态修复":
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = await db.DroneCaseInfoSTXF.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
if (!string.IsNullOrEmpty(caseInfo.case_no))
{
await db.DroneCaseInfoSTXF.UpdateAsync(it => new DroneCaseInfoSTXF()
{
synchronoustime = DateTime.Now
}, it => it.Id == req.Id);
}
else
{
do
{
caseno = caseInfo.countyid + DateTime.Now.ToString("yyyyMMdd") +
random.Next(1000, 9999).ToString();
var count = await db.DroneCaseInfoSTXF.AsQueryable().Where(c => c.case_no == caseno)
.CountAsync();
isUnique = count == 0;
} while (!isUnique);
await db.DroneCaseInfoSTXF.UpdateAsync(it => new DroneCaseInfoSTXF()
{
case_no = caseno,
synchronoustime = DateTime.Now
}, it => it.Id == req.Id);
}
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
case "设施农业监管":
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = await db.DroneSsnyd.AsQueryable().FirstAsync(r => r.Id == req.Id);
if (caseInfo == null)
throw new Exception("未找到图斑信息");
if (!string.IsNullOrEmpty(caseInfo.xiangmu_no))
{
await db.DroneSsnyd.UpdateAsync(it => new DroneSsnyd()
{
synchronoustime = DateTime.Now,
handle_status_id=1
}, it => it.Id == req.Id);
}
else
{
do
{
caseno = caseInfo.countyid + DateTime.Now.ToString("yyyyMMdd") +
random.Next(1000, 9999).ToString();
var count = await db.DroneSsnyd.AsQueryable().Where(c => c.xiangmu_no == caseno)
.CountAsync();
isUnique = count == 0;
} while (!isUnique);
await db.DroneSsnyd.UpdateAsync(it => new DroneSsnyd()
{
xiangmu_no = caseno,
synchronoustime = DateTime.Now,
handle_status_id=1
}, it => it.Id == req.Id);
}
var result = db.Commit();
if (result)
return new Response<bool> { Result = true, Code = 200, Message = "更新成功" };
else
return new Response<bool> { Result = false, Code = 500, Message = "更新失败" };
}
default:
return new Response<bool> { Result = false, Code = 500, Message = "暂无该专题" };
}
}
public async Task<Response<bool>> IsReSubmit(string originalcaseno, string subjectkey)
{
Random random = new Random();
using (var uow = base.UnitWork.CreateContext())
{
var data1 = await uow.ReSubmit.AsQueryable()
.Where(r => r.CaseNo == originalcaseno && r.SubjectKey == subjectkey).ToListAsync();
if (data1.Count > 0)
{
return new Response<bool> { Result = false, Code = 500, Message = "案件已复提,无法审核" };
}
else
{
return new Response<bool> { Result = true, Code = 200, Message = "可以审核" };
}
}
}
#endregion
#region 导出shp文件 四种格式
#endregion
#region 导入案件信息
/// <summary>
/// 上传用户信息
/// </summary>
/// <param name="formFiles"></param>
/// <returns></returns>
public Response<bool> ImportUserInfo(IFormFileCollection formFiles)
{
IFormFile file = formFiles[0];
//存储文件到服务器
if (file != null)
{
if (file.FileName.IndexOf(".xls") > 0 || file.FileName.IndexOf(".xlsx") > 0)
{
//数据库导入
IWorkbook workbook = null;
if (file.FileName.IndexOf(".xlsx") > 0)
{
using (var stream = file.OpenReadStream())
{
workbook = new XSSFWorkbook(stream); //excel的版本2007
}
}
else if (file.FileName.IndexOf(".xls") > 0)
{
using (var stream = file.OpenReadStream())
{
workbook = new HSSFWorkbook(stream); //excel的版本2003
}
}
//数据处理
using (var uow = base.UnitWork.CreateContext())
{
//获取sheet
ISheet sheet;
sheet = workbook.GetSheetAt(0);
//处理sheet数据
string res = "";
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
//获取有效数据行数
int lastRow = sheet.LastRowNum;
int rowCount = 0;
//具体excel数据解析
for (int i = 1; i <= lastRow; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null || string.IsNullOrEmpty(row.GetCell(0).ToString())) continue;
DroneCaseInfoSingle cas = new DroneCaseInfoSingle();
cas.case_description = row.GetCell(0).ToString();
cas.address = row.GetCell(1).ToString();
//坐标lng
if (string.IsNullOrEmpty(row.GetCell(2).ToString()))
{
cas.lng = Convert.ToDecimal(row.GetCell(2).ToString());
}
else
{
cas.lng = null;
}
//坐标lat
if (string.IsNullOrEmpty(row.GetCell(3).ToString()))
{
cas.lat = Convert.ToDecimal(row.GetCell(3).ToString());
}
else
{
cas.lat = null;
}
//类型
cas.typename = row.GetCell(4).ToString();
if (!string.IsNullOrEmpty(row.GetCell(4).ToString()))
{
var ditem = client.Queryable<SysDataItemDetail>()
.Where(r => r.ItemName == row.GetCell(4).ToString()).First();
if (ditem != null)
{
cas.typeid = ditem.ItemDetailId;
}
}
SysUser user = new SysUser();
user.Id = YitIdHelper.NextId();
user.CreateTime = DateTime.Now;
user.Name = row.GetCell(4).ToString();
user.CreateId = _auth.GetCurrentUser().User.Id;
user.Account = row.GetCell(5).ToString();
user.Password = Md5Helper.Encrypt(user.Account, 32).ToLower();
if (uow.User.IsAny(u => u.Account == user.Account))
{
res += "账号" + user.Account + "已存在,";
}
else
{
user.Secretkey = Md5Helper.Encrypt(CommonHelper.CreateNo(), 16).ToLower();
if (string.IsNullOrEmpty(user.Password))
{
user.Password = Md5Helper
.Encrypt(
DESEncrypt.Encrypt(Md5Helper.Hash(user.Account), user.Secretkey).ToLower(),
32).ToLower(); //如果客户端没提供密码，默认密码同账号
}
else
{
user.Password = Md5Helper
.Encrypt(DESEncrypt.Encrypt(user.Password, user.Secretkey).ToLower(), 32)
.ToLower();
}
uow.User.Insert(user);
if (!string.IsNullOrEmpty(row.GetCell(7).ToString()))
{
//为用户分配角色
SysUserRole userrole = new SysUserRole();
userrole.UserId = user.Id;
userrole.RoleId = Convert.ToInt64(row.GetCell(7).ToString());
//删除用户相关角色
uow.SysUserRole.DeleteById(user.Id);
//添加
uow.SysUserRole.Insert(userrole);
}
if (!string.IsNullOrEmpty(row.GetCell(2).ToString()))
{
//为用户分配部门
SysUserOrg userorg = new SysUserOrg();
userorg.UserId = user.Id;
userorg.OrgId = Convert.ToInt64(row.GetCell(2).ToString());
userorg.PositionId = 0;
userorg.Level = Convert.ToInt16(row.GetCell(3).ToString());
//删除用户相关部门
uow.SysUserOrg.DeleteById(user.Id);
//重新添加本次部门
uow.SysUserOrg.Insert(userorg);
}
}
}
}
var flag = uow.Commit();
return new Response<bool>
{
Result = flag,
Message = flag == true ? "更新成功 " + res : "更新失败"
};
}
}
else
{
return new Response<bool>
{
Result = false,
Message = "上传文件类型错误，请上传Excel文件"
};
}
}
else
{
return new Response<bool>
{
Result = false,
Message = "文件为空"
};
}
}
#endregion
#region 测试代码
public Response<bool> UpdateYijian()
{
var caseInfo = client.Queryable<DroneCaseInfoSingle>().ToList();
//var caseInfo = client.Queryable<DroneCaseInfoSingle>().Where(r => r.case_no== "FN371325202408124154").ToList();
foreach (var item in caseInfo)
{
var tasklog = client.Queryable<WFTaskLog>().Where(r => r.ProcessId == item.Id)
.OrderByDescending(r => r.CreateDate).ToList();
if (tasklog.Count > 0)
{
//修改县级审核意见及结果
var info = tasklog.Where(r => r.UnitName == "县级审核").FirstOrDefault();
if (info != null)
{
item.xianjiyijian = info.Des;
item.xjshenhejieguo = info.OperationName;
item.verifyuser = info.UserName;
item.verifytime = DateTime.Parse(info.CreateDate.ToString("yyyy-MM-dd HH:mm:ss"));
item.is_drawback = info.OperationName == "驳回" ? 1 : item.is_drawback;
}
//修改市级审核意见及结果
var infosj = tasklog.Where(r => r.UnitName == "市级审核").FirstOrDefault();
if (infosj != null)
{
item.shijiyijian = infosj.Des;
item.sjshenhejieguo = infosj.OperationName;
item.hexiaoren = infosj.UserName;
item.hexiaotime = DateTime.Parse(infosj.CreateDate.ToString("yyyy-MM-dd HH:mm:ss"));
item.is_shijibohui = infosj.OperationName == "驳回" ? 1 : item.is_shijibohui;
}
client.Updateable(item).UpdateColumns(c => new
{
c.xianjiyijian,
c.xjshenhejieguo,
c.shijiyijian,
c.sjshenhejieguo,
c.verifyuser,
c.verifytime,
c.hexiaoren,
c.hexiaotime,
c.is_drawback,
c.is_shijibohui
})
.ExecuteCommand();
}
}
return new Response<bool> { };
}
public Response<bool> UpdateYijianwpxf()
{
var caseInfo = client.Queryable<DroneCaseInfoSatellite>().ToList();
//var caseInfo = client.Queryable<DroneCaseInfoSingle>().Where(r => r.case_no== "FN371325202408124154").ToList();
foreach (var item in caseInfo)
{
var tasklog = client.Queryable<WFTaskLog>().Where(r => r.ProcessId == item.Id)
.OrderByDescending(r => r.CreateDate).ToList();
if (tasklog.Count > 0)
{
//修改县级审核意见及结果
var info = tasklog.Where(r => r.UnitName == "县级审核").FirstOrDefault();
if (info != null)
{
item.xianjiyijian = info.Des;
item.xjshenhejieguo = info.OperationName;
item.verifyuser = info.UserName;
item.verifytime = DateTime.Parse(info.CreateDate.ToString("yyyy-MM-dd HH:mm:ss"));
item.is_drawback = info.OperationName == "驳回" ? 1 : item.is_drawback;
}
//修改市级审核意见及结果
var infosj = tasklog.Where(r => r.UnitName == "市级审核").FirstOrDefault();
if (infosj != null)
{
item.shijiyijian = infosj.Des;
item.sjshenhejieguo = infosj.OperationName;
item.hexiaoren = infosj.UserName;
//item.hexiaotime = infosj.CreateDate.ToString("yyyy-MM-dd HH:mm:ss");
item.is_shijibohui = infosj.OperationName == "驳回" ? 1 : item.is_shijibohui;
}
client.Updateable(item).UpdateColumns(c => new
{
c.xianjiyijian,
c.xjshenhejieguo,
c.shijiyijian,
c.sjshenhejieguo,
c.verifyuser,
c.verifytime,
c.hexiaoren,
c.hexiaotime,
c.is_drawback,
c.is_shijibohui
}).ExecuteCommand();
}
}
return new Response<bool> { };
}
public Response<bool> UpdateYijianHnad()
{
var caseInfo = client.Queryable<DroneCaseInfoSingle>()
.Where(r => r.handle_status_name == "" || r.handle_status_name == null).ToList();
foreach (var item in caseInfo)
{
var task = client.Queryable<WFTask>().Where(r => r.ProcessId == item.Id).First(r => r.State == 1);
if (task != null)
{
if (task.UnitName == "待填报")
{
item.handle_status_name = task.UnitName;
item.handle_status_id = 1;
}
else if (task.UnitName == "待接收")
{
item.handle_status_name = task.UnitName;
item.handle_status_id = 0;
}
else if (task.UnitName == "待整改")
{
item.handle_status_name = task.UnitName;
item.handle_status_id = 2;
}
else if (task.UnitName == "县级审核")
{
item.handle_status_name = task.UnitName;
item.handle_status_id = 3;
}
client.Updateable(item).UpdateColumns(c => new
{
c.handle_status_name,
c.handle_status_id,
}).ExecuteCommand();
}
}
return new Response<bool> { };
}
public async Task<Response<bool>> SynchronousImg()
{
HttpClient _client = new HttpClient
{
BaseAddress = new Uri("http://localhost:98")
};
var caseInfo = client.Queryable<DroneCaseInfoSingle>().ToList();
List<string> noImg = new List<string>();
foreach (var item in caseInfo)
{
//chaichufugenghoupic，anjianzhaopian，bubanzhaopian，xchczhaopian
if (!string.IsNullOrEmpty(item.chaichufugenghoupic))
{
var endStr = await GetEndStr(item.chaichufugenghoupic);
client.Updateable<DroneCaseInfoSingle>(item)
.UpdateColumns(r => new
{
chaichufugenghoupic = endStr
}).Where(r => r.Id == item.Id).ExecuteCommand();
}
if (!string.IsNullOrEmpty(item.anjianzhaopian))
{
var endStr = await GetEndStr(item.anjianzhaopian);
client.Updateable<DroneCaseInfoSingle>(item)
.UpdateColumns(r => new
{
anjianzhaopian = endStr
}).Where(r => r.Id == item.Id).ExecuteCommand();
}
if (!string.IsNullOrEmpty(item.bubanzhaopian))
{
var endStr = await GetEndStr(item.bubanzhaopian);
client.Updateable<DroneCaseInfoSingle>(item)
.UpdateColumns(r => new
{
bubanzhaopian = endStr
}).Where(r => r.Id == item.Id).ExecuteCommand();
}
if (!string.IsNullOrEmpty(item.xchczhaopian))
{
var endStr = await GetEndStr(item.xchczhaopian);
client.Updateable<DroneCaseInfoSingle>(item)
.UpdateColumns(r => new
{
xchczhaopian = endStr
}).Where(r => r.Id == item.Id).ExecuteCommand();
}
}
return new Response<bool> { };
}
private async Task<string> GetEndStr(string beginStr)
{
HttpClient _client = new HttpClient
{
BaseAddress = new Uri("http://localhost:98")
};
string endStr = "";
//chaichufugenghoupic，anjianzhaopian，bubanzhaopian，xchczhaopian
if (!string.IsNullOrEmpty(beginStr))
{
var img = beginStr.Split(',').ToList();
foreach (var it in img)
{
string url = $"/api/Platform/ExistsImg?path={Uri.EscapeDataString(it)}";
HttpResponseMessage response = await _client.GetAsync(url);
response.EnsureSuccessStatusCode();
string responseBody = await response.Content.ReadAsStringAsync();
var obj = Newtonsoft.Json.JsonConvert.DeserializeObject<Response<bool>>(responseBody);
if (obj.Result == false && obj.Code == 200)
{
//更新图片信息
img.Remove(it);
}
}
endStr = string.Join(",", img);
}
return endStr;
}
/// <summary>
/// 修改案件信息
/// </summary>
/// <param name="path"></param>
/// <param name="fieldName"></param>
/// <param name="caseid"></param>
/// <param name="dataTable"></param>
/// <returns></returns>
public Response<bool> UpdateCaseImg(string newpath, string oldpath, string newcaseid)
{
try
{
client.Ado.BeginTran();
//获取相位角图片数据
var olddif = client.Queryable<DroneImageRef>().First(r => r.FilePath == oldpath);
if (olddif != null)
{
DroneImageRef droneImageRef = new DroneImageRef()
{
Id = Guid.NewGuid().ToString(),
CaseId = newcaseid,
FilePath = newpath,
Orientation = olddif.Orientation,
CreateTime = DateTime.Now,
Lng = olddif.Lng,
Lat = olddif.Lat,
};
client.Insertable<DroneImageRef>(droneImageRef).ExecuteCommand();
}
client.Ado.CommitTran();
return new Response<bool>
{
Result = true,
Message = "更新成功",
};
}
catch (Exception ex)
{
client.Ado.RollbackTran();
return new Response<bool>
{
Result = false,
Message = "更新失败",
};
}
}
public Response<bool> UpdateCase(string newpath, string newcaseid, string field, string tablename)
{
try
{
//更新案件信息
//newpath = newpath.Replace(@"\", @"\\");
string sql = "UPDATE " + tablename + " SET " + field + " = '" + newpath + "' WHERE \"Id\" = '" +
newcaseid + "'";
var a = client.Ado.ExecuteCommand(sql);
return new Response<bool>
{
Result = true,
Message = "更新成功",
};
}
catch (Exception ex)
{
return new Response<bool>
{
Result = false,
Message = "更新失败",
};
}
}
#endregion
}
Reference in New Issue View Git Blame Copy Permalink