5301 lines
238 KiB
C#
5301 lines
238 KiB
C#
using System.Data;
|
||
using System.IO.Compression;
|
||
using System.Text;
|
||
using Infrastructure;
|
||
using Infrastructure.Extensions;
|
||
using Infrastructure.Helpers;
|
||
using Infrastructure.Utilities;
|
||
using Microsoft.AspNetCore.Http;
|
||
using NetTopologySuite.Features;
|
||
using NetTopologySuite.Geometries;
|
||
using NetTopologySuite.IO;
|
||
using Newtonsoft.Json;
|
||
using Newtonsoft.Json.Linq;
|
||
using NPOI.HSSF.UserModel;
|
||
using NPOI.SS.UserModel;
|
||
using NPOI.SS.Util;
|
||
using NPOI.Util;
|
||
using NPOI.XSSF.UserModel;
|
||
using OpenAuth.App.BasicQueryService;
|
||
using OpenAuth.App.FormModule;
|
||
using OpenAuth.App.Request;
|
||
using OpenAuth.App.Response;
|
||
using OpenAuth.App.ServiceApp.DroneCaseInfo.Request;
|
||
using OpenAuth.App.ServiceApp.Request;
|
||
using OpenAuth.App.ServiceApp.Response;
|
||
using OpenAuth.Repository;
|
||
using OpenAuth.Repository.Domain;
|
||
using SqlSugar;
|
||
using Yitter.IdGenerator;
|
||
using Shapefile = NetTopologySuite.IO.Esri.Shapefile;
|
||
|
||
namespace OpenAuth.App.ServiceApp.DroneCaseInfo;
|
||
|
||
public partial class DroneCaseInfoXcsjApp
|
||
{
|
||
#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 = 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 = 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
|
||
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<DroneCaseInfoXcsj> cases, Func<DroneCaseInfoXcsj, 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<DroneCaseInfoXcsj> caseList = new List<DroneCaseInfoXcsj>();
|
||
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<DroneCaseInfoXcsj>()
|
||
.WhereIF(!string.IsNullOrEmpty(tubanlaiyuan), r => tubanlaiyuanList.Contains(r.tubanlaiyuan))
|
||
.Where(r => r.synchronoustime >= startTime && r.synchronoustime <= endTime &&
|
||
r.is_closed == 0).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<DroneCaseInfoXcsj>()
|
||
.WhereIF(!string.IsNullOrEmpty(tubanlaiyuan), r => tubanlaiyuanList.Contains(r.tubanlaiyuan))
|
||
.Where(r => r.synchronoustime > startTime && r.synchronoustime <= endTime &&
|
||
r.is_closed == 0).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;
|
||
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 => r.area);
|
||
wfmjsum += wfmj;
|
||
double wfgdmj = SumArea(casesForCounty.Where(r => r.is_illegal == 1 || 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;
|
||
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,
|
||
zghsywfgdzs = wfzs - flhzs - bbsxzs - cczs,
|
||
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 - bzgdtbzscount - 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 = "获取数据成功"
|
||
};
|
||
}
|
||
|
||
public async Task<Response<PageInfo<List<DroneCaseInfoXcsj>>>> 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<DroneCaseInfoXcsj>()
|
||
.Where(r => r.synchronoustime >= req.StartTime && r.synchronoustime <= req.EndTime)
|
||
.WhereIF(!string.IsNullOrEmpty(req.tubanlaiyuan),r=>tubanlaiyuanList.Contains(r.tubanlaiyuan))
|
||
.Where(r => r.is_closed == 0);
|
||
|
||
// 根据用户角色和区域进行过滤
|
||
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(r => orgIds_c.Contains(r.countyid))
|
||
: baseQuery.Where(r => r.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(r => orgIds_c.Contains(r.streetid))
|
||
: baseQuery.Where(r => r.streetid == req.AreaId);
|
||
}
|
||
else
|
||
{
|
||
return new Response<PageInfo<List<DroneCaseInfoXcsj>>>
|
||
{
|
||
Result = new PageInfo<List<DroneCaseInfoXcsj>>(),
|
||
Message = "暂无数据"
|
||
};
|
||
}
|
||
|
||
// 根据案件类型进行过滤和排序
|
||
switch (req.CaseType)
|
||
{
|
||
case 2:
|
||
baseQuery = baseQuery.Where(r => r.is_illegal == 0 && r.handle_status_id == 5);
|
||
break;
|
||
case 3:
|
||
baseQuery = baseQuery.Where(r =>
|
||
(r.is_illegal == 1 && r.handle_status_id == 5) || r.handle_status_id != 5);
|
||
break;
|
||
case 4:
|
||
baseQuery = baseQuery.Where(r => r.is_illegal == 2 && r.handle_status_id == 5);
|
||
break;
|
||
case 5:
|
||
baseQuery = baseQuery.Where(r => r.weifaleixing == 1 && r.handle_status_id == 5 && r.is_illegal == 1);
|
||
break;
|
||
case 6:
|
||
baseQuery = baseQuery.Where(r =>
|
||
r.measure_name == "1" && r.handle_status_id == 5 && r.is_illegal == 1 && r.weifaleixing == 0);
|
||
break;
|
||
case 7:
|
||
//r.measure_name == "0" && r.handle_status_id == 5 && r.weifaleixing == 0 && r.is_illegal == 1
|
||
baseQuery = baseQuery.Where(r =>
|
||
r.measure_name == "0" && r.handle_status_id == 5 && r.is_illegal == 1 && r.weifaleixing == 0);
|
||
break;
|
||
case 8:
|
||
// (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)))
|
||
//baseQuery = baseQuery.Where(r => ((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))));
|
||
baseQuery = baseQuery.Where(r =>
|
||
((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))));
|
||
break;
|
||
case 9:
|
||
baseQuery = baseQuery.Where(r =>
|
||
((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))) &&
|
||
!(r.measure_name == "1" && r.handle_status_id == 5 && r.weifaleixing == 0) &&
|
||
!(r.measure_name == "0" && r.handle_status_id == 5 && r.weifaleixing == 0));
|
||
break;
|
||
case 10:
|
||
//(r.weifaleixing == 0 && r.is_illegal == 1 && r.handle_status_id == 5) || r.handle_status_id != 5)
|
||
baseQuery = baseQuery.Where(r =>
|
||
(r.weifaleixing == 0 && r.is_illegal == 1 && r.handle_status_id == 5) || r.handle_status_id != 5);
|
||
break;
|
||
default:
|
||
break;
|
||
}
|
||
|
||
baseQuery = baseQuery.OrderByDescending(r => r.synchronoustime);
|
||
|
||
// 获取分页结果
|
||
var result = await baseQuery.ToPageListAsync(req.page, req.limit, totalcount);
|
||
|
||
return new Response<PageInfo<List<DroneCaseInfoXcsj>>>
|
||
{
|
||
Result = new PageInfo<List<DroneCaseInfoXcsj>>
|
||
{
|
||
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 (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 (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)
|
||
{
|
||
Response<MemoryStream> response = new Response<MemoryStream>();
|
||
try
|
||
{
|
||
var list = CaseOffence(startTime, endTime,null).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 (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;
|
||
}
|
||
|
||
|
||
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);
|
||
|
||
string sql =
|
||
$"SELECT \"Id\", countyname, streetname,communityname,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, " +
|
||
$"COALESCE(area::numeric, 0) AS area,case when is_illegal=1 \r\n then actual_use_to when is_illegal=2 then qita_use_to end as qtsjyt, COALESCE(nongyongdi_area::numeric, 0) AS nongyongdiArea, COALESCE(yongjiujibennongtian_area::numeric, 0) AS yongnongArea, COALESCE(gengdi_area::numeric, 0) 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_xcsj WHERE is_closed = 0 and synchronoustime >= @StartTime and synchronoustime <= @EndTime {sqlpart}";
|
||
|
||
var caseList =
|
||
client.Ado.SqlQuery<DroneCaseByTypeResp>(sql,
|
||
new { StartTime = req.StartTime, EndTime = req.EndTime, @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_xcsj 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 = Path.ChangeExtension(shpPath, "prj");
|
||
File.WriteAllText(cpgPath, prjStr, Encoding.UTF8);
|
||
|
||
Shapefile.WriteAllFeatures(features, shpPath, encoding: Encoding.UTF8);
|
||
}
|
||
|
||
public void CreateZipFromShapefiles(string shpPath, string zipPath)
|
||
{
|
||
var files = new List<string>
|
||
{
|
||
Path.ChangeExtension(shpPath, "cpg"),
|
||
shpPath,
|
||
Path.ChangeExtension(shpPath, "shx"),
|
||
Path.ChangeExtension(shpPath, "dbf"),
|
||
Path.ChangeExtension(shpPath, "prj")
|
||
};
|
||
|
||
using (var zipArchive = ZipFile.Open(zipPath, ZipArchiveMode.Create))
|
||
{
|
||
foreach (var file in files)
|
||
{
|
||
if (File.Exists(file))
|
||
{
|
||
zipArchive.CreateEntryFromFile(file, 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
|
||
|
||
public async Task<Response<string>> CaseSplit(CaseSplitReq req)
|
||
{
|
||
var authStrategyContext = _auth.GetCurrentUser();
|
||
var user = authStrategyContext.User;
|
||
// 案件分宗 1. 关闭案件 2.撤消流程 3. 新建案件流程（生成关联图斑记录） 4. 生成拆分案件及拆分前案件关联关系
|
||
|
||
using var db = Repository.AsSugarClient();
|
||
|
||
try
|
||
{
|
||
await db.Ado.BeginTranAsync(IsolationLevel.ReadCommitted);
|
||
var original = await db.Queryable<DroneCaseInfoXcsj>().FirstAsync(t => t.Id == req.caseid);
|
||
var oriinalCaseNo = original.case_no;
|
||
if (original == null)
|
||
{
|
||
throw new Exception("图斑不存在");
|
||
}
|
||
|
||
var wktReader = new WKTReader();
|
||
foreach (var part in req.parts)
|
||
{
|
||
var geometry = wktReader.Read(part.geom);
|
||
}
|
||
|
||
// 根据案件id取得流程
|
||
var process = await Repository.ChangeRepository<SugarRepositiry<WFProcess>>().AsQueryable()
|
||
.FirstAsync(t => t.Id == req.processid);
|
||
|
||
// 作废流程
|
||
var result = await _processApp.DeleteProcess(process.Id);
|
||
// 生成拆分案件
|
||
var caseList = new List<DroneCaseInfoXcsj>(req.parts.Count);
|
||
var relationships = new List<DroneCaseSplit>(req.parts.Count);
|
||
|
||
|
||
var imageRefs = await Repository.ChangeRepository<SugarRepositiry<DroneImageRef>>().AsQueryable()
|
||
.Where(t => t.CaseId == req.caseid)
|
||
.Select(t => new DroneImageRef
|
||
{
|
||
FilePath = t.FilePath,
|
||
Orientation = t.Orientation,
|
||
Lng = t.Lng,
|
||
Lat = t.Lat
|
||
})
|
||
.ToListAsync();
|
||
using var httpClient = new HttpClient();
|
||
// http://192.168.10.125:9011/DroneEnforcement/2024/20241112/Image_Water_1731054661277.jpeg
|
||
var config = ConfigHelper.GetConfigRoot();
|
||
var baseUrl = config["ImgServer"];
|
||
var url = baseUrl + "/api/Platform/NewCopyImg";
|
||
// 文件路径 字段
|
||
var files = new Dictionary<string, string>();
|
||
// 方向角图片
|
||
var images = "";
|
||
if (!string.IsNullOrEmpty(original.anjianzhaopian))
|
||
{
|
||
var temp = original.anjianzhaopian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "anjianzhaopian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
// 拆除复耕
|
||
if (!string.IsNullOrEmpty(original.chaichufugenghoupic))
|
||
{
|
||
var temp = original.chaichufugenghoupic.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "chaichufugenghoupic");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
// 补办照片
|
||
if (!string.IsNullOrEmpty(original.bubanzhaopian))
|
||
{
|
||
var temp = original.bubanzhaopian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "bubanzhaopian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
// 图斑图片
|
||
if (!string.IsNullOrEmpty(original.casepic))
|
||
{
|
||
var temp = original.casepic.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "casepic");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(original.fujian))
|
||
{
|
||
var temp = original.fujian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "fujian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
// 整改附件
|
||
if (!string.IsNullOrEmpty(original.zhenggaifujian))
|
||
{
|
||
var temp = original.zhenggaifujian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "zhenggaifujian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
// 合法附件
|
||
if (!string.IsNullOrEmpty(original.hefafujian))
|
||
{
|
||
var temp = original.hefafujian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "hefafujian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
// 其它附近
|
||
if (!string.IsNullOrEmpty(original.qitafujian))
|
||
{
|
||
var temp = original.qitafujian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "qitafujian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
// 无人机复飞
|
||
if (!string.IsNullOrEmpty(original.wrjffzhaopian))
|
||
{
|
||
var temp = original.wrjffzhaopian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "wrjffzhaopian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
//现场核查照片
|
||
if (!string.IsNullOrEmpty(original.xchczhaopian))
|
||
{
|
||
var temp = original.xchczhaopian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "xchczhaopian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
|
||
images = images.Trim(',');
|
||
var param = new Dictionary<string, string>()
|
||
{
|
||
{ "path", images }
|
||
};
|
||
var content = new FormUrlEncodedContent(param);
|
||
var newImgRefs = new List<DroneImageRef>(imageRefs.Count * 2);
|
||
|
||
|
||
var i = 1;
|
||
foreach (var casePart in req.parts)
|
||
{
|
||
var caseInfo = original.MapTo<DroneCaseInfoXcsj>();
|
||
var caseId = Guid.NewGuid().ToString();
|
||
caseInfo.Id = caseId;
|
||
caseInfo.case_no = oriinalCaseNo + "-" + (i++);
|
||
caseInfo.nongyongdi_area = casePart.nongyongdi_area;
|
||
caseInfo.gengdi_area = casePart.gengdi_area;
|
||
caseInfo.yongjiujibennongtian_area = casePart.yongjiujibennongtian_area;
|
||
caseInfo.shengtaibaohuhongxian_area = casePart.shengtaibaohuhongxian_area;
|
||
caseInfo.guotukongjianguihua_area = casePart.guotukongjianguihua_area;
|
||
caseInfo.zhongdianquyu_area = casePart.zhongdianquyu_area;
|
||
caseInfo.area = casePart.area;
|
||
// 设置时间
|
||
caseInfo.createtime = DateTime.Now;
|
||
// 设置创建人
|
||
caseInfo.createuser = user.Id + "";
|
||
caseInfo.createusername = user.Name;
|
||
|
||
|
||
// 清理数据
|
||
caseInfo.anjianzhaopian = null;
|
||
caseInfo.chaichufugenghoupic = null;
|
||
caseInfo.bubanzhaopian = null;
|
||
caseInfo.casepic = null;
|
||
caseInfo.fujian = null;
|
||
caseInfo.zhenggaifujian = null;
|
||
caseInfo.hefafujian = null;
|
||
caseInfo.qitafujian = null;
|
||
caseInfo.wrjffzhaopian = null;
|
||
caseInfo.xchczhaopian = null;
|
||
|
||
// 复制图片
|
||
if (!string.IsNullOrEmpty(images))
|
||
{
|
||
var copyFileResponse = await httpClient.PostAsync(url, content);
|
||
if (copyFileResponse.IsSuccessStatusCode)
|
||
{
|
||
var resultContent = await copyFileResponse.Content.ReadAsStringAsync();
|
||
var obj = JsonConvert.DeserializeObject<Response<List<SysUploadFileNew>>>(resultContent);
|
||
if (!obj.Code.Equals(200))
|
||
{
|
||
throw new Exception("复制文件失败");
|
||
}
|
||
|
||
foreach (var sysUploadFileNew in obj.Result)
|
||
{
|
||
var originalPath = sysUploadFileNew.OriginalPath;
|
||
var newFilePath = sysUploadFileNew.FilePath;
|
||
var imgRef = imageRefs.FirstOrDefault(x => x.FilePath.Equals(originalPath));
|
||
if (imgRef != null)
|
||
{
|
||
// 填充信息
|
||
var temp = imgRef.Copy();
|
||
temp.Id = Guid.NewGuid().ToString();
|
||
temp.CaseId = caseInfo.Id;
|
||
temp.CreateTime = DateTime.Now;
|
||
temp.FilePath = newFilePath;
|
||
newImgRefs.Add(temp);
|
||
}
|
||
|
||
var field = files[originalPath];
|
||
switch (field)
|
||
{
|
||
case "fujian":
|
||
if (string.IsNullOrEmpty(caseInfo.fujian))
|
||
{
|
||
caseInfo.fujian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.fujian = caseInfo.fujian + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "anjianzhaopian":
|
||
if (string.IsNullOrEmpty(caseInfo.anjianzhaopian))
|
||
{
|
||
caseInfo.anjianzhaopian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.anjianzhaopian = caseInfo.anjianzhaopian + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "chaichufugenghoupic":
|
||
if (string.IsNullOrEmpty(caseInfo.chaichufugenghoupic))
|
||
{
|
||
caseInfo.chaichufugenghoupic = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.chaichufugenghoupic =
|
||
caseInfo.chaichufugenghoupic + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "bubanzhaopian":
|
||
if (string.IsNullOrEmpty(caseInfo.bubanzhaopian))
|
||
{
|
||
caseInfo.bubanzhaopian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.bubanzhaopian = caseInfo.bubanzhaopian + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "casepic":
|
||
if (string.IsNullOrEmpty(caseInfo.casepic))
|
||
{
|
||
caseInfo.casepic = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.casepic = caseInfo.casepic + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "zhenggaifujian":
|
||
if (string.IsNullOrEmpty(caseInfo.zhenggaifujian))
|
||
{
|
||
caseInfo.zhenggaifujian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.zhenggaifujian = caseInfo.zhenggaifujian + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "hefafujian":
|
||
if (string.IsNullOrEmpty(caseInfo.hefafujian))
|
||
{
|
||
caseInfo.hefafujian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.hefafujian = caseInfo.hefafujian + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "qitafujian":
|
||
if (string.IsNullOrEmpty(caseInfo.qitafujian))
|
||
{
|
||
caseInfo.qitafujian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.qitafujian = caseInfo.qitafujian + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "wrjffzhaopian":
|
||
if (string.IsNullOrEmpty(caseInfo.wrjffzhaopian))
|
||
{
|
||
caseInfo.wrjffzhaopian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.wrjffzhaopian = caseInfo.wrjffzhaopian + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "xchczhaopian":
|
||
if (string.IsNullOrEmpty(caseInfo.xchczhaopian))
|
||
{
|
||
caseInfo.xchczhaopian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.xchczhaopian = caseInfo.xchczhaopian + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
|
||
else
|
||
{
|
||
throw new Exception("复制文件失败");
|
||
}
|
||
}
|
||
|
||
|
||
// 填充图斑信息
|
||
var geometry = wktReader.Read(casePart.geom);
|
||
var gidStr = _commonDataManager.GetMaxKeyVal("gid", "drone_shp_data", 1);
|
||
var insertObj = new Dictionary<string, object>
|
||
{
|
||
{ "gid", gidStr.ToInt() },
|
||
{ "geom", geometry.AsBinary() },
|
||
{ "relid", caseId }
|
||
};
|
||
// 插入图斑
|
||
await db.Insertable(insertObj).AS("drone_shp_data").ExecuteCommandAsync();
|
||
caseInfo.geomid = gidStr;
|
||
caseList.Add(caseInfo);
|
||
// 原始案件与拆分案件关联
|
||
var splitRelation = new DroneCaseSplit();
|
||
splitRelation.NewCaseId = caseId;
|
||
splitRelation.OriginalCaseId = req.caseid;
|
||
relationships.Add(splitRelation);
|
||
}
|
||
|
||
if (newImgRefs.Count > 0)
|
||
{
|
||
await db.Insertable(newImgRefs).ExecuteCommandAsync();
|
||
}
|
||
|
||
await db.Insertable(caseList).ExecuteCommandAsync();
|
||
//新案件与原始案件关联
|
||
await db.Insertable(relationships).ExecuteCommandAsync();
|
||
|
||
|
||
// 发起新流程
|
||
foreach (var caseInfo in caseList)
|
||
{
|
||
var instanceInfo = process.InstanceInfo;
|
||
instanceInfo.pkeyValue = caseInfo.Id;
|
||
await _processApp.SaveDraft(caseInfo.Id, process.SchemeCode, user.Id + "",
|
||
Json.ToJson(instanceInfo), process.ParentProcessId, process.ParentNodeId, process.ParentTaskId, 0,
|
||
caseInfo.case_description);
|
||
await _processApp.Create(caseInfo.Id, "", user.Id + "", null, Json.ToJson(instanceInfo),
|
||
caseInfo.case_description);
|
||
}
|
||
|
||
// 关闭案件
|
||
var record = new DroneCaseInfoXcsj
|
||
{
|
||
Id = req.caseid,
|
||
is_closed = 1,
|
||
is_split = 1,
|
||
close_userid = user.Id + "",
|
||
close_user = user.Name,
|
||
close_time = DateTime.Now
|
||
};
|
||
Console.WriteLine("运行前==========================================");
|
||
var x = await db.Updateable(record).IgnoreNullColumns().ExecuteCommandAsync();
|
||
Console.WriteLine("运行后============================================ " + x);
|
||
await db.Ado.CommitTranAsync();
|
||
return new Response<string>
|
||
{
|
||
Result = "拆分成功",
|
||
Message = "拆分成功"
|
||
};
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
await db.Ado.RollbackTranAsync();
|
||
throw e;
|
||
}
|
||
}
|
||
|
||
public async Task<Response<string>> CaseRecover(CaseRecoverReq req)
|
||
{
|
||
var authStrategyContext = _auth.GetCurrentUser();
|
||
var user = authStrategyContext.User;
|
||
using var db = Repository.AsSugarClient();
|
||
|
||
var relationships = new List<DroneCaseSplit>(req.parts.Count);
|
||
var caseId = Guid.NewGuid().ToString();
|
||
try
|
||
{
|
||
await db.Ado.BeginTranAsync(IsolationLevel.ReadCommitted);
|
||
var subCaseId = req.caseid;
|
||
// 这里是验证当前选中的图斑，是否有被分割，但当和它一起被分割出来的图斑被分割时，还原该图斑会有问题
|
||
// 以下代码校验指定图斑是否被分割过
|
||
string sql5 =
|
||
$"select new_case_id from drone_case_split where original_case_id='{subCaseId}' and is_recover=0";
|
||
var caseids1 = await db.Ado.SqlQueryAsync<string>(sql5);
|
||
if (caseids1.Count > 0)
|
||
{
|
||
return new Response<string>
|
||
{
|
||
Result = "当前图斑存在分割的子图斑不允许还原",
|
||
Message = "还原失败"
|
||
};
|
||
}
|
||
|
||
var sql6 = @"SELECT
|
||
count(*)
|
||
FROM
|
||
drone_case_split s join drone_caseinfo_xcsj c on s.new_case_id = c.""Id"" and c.is_split = '1'
|
||
WHERE
|
||
s.original_case_id = (select original_case_id from drone_case_split where new_case_id = @x)
|
||
AND s.new_case_id != @x
|
||
AND s.is_recover = '0'";
|
||
var count = await db.Ado.SqlQuerySingleAsync<int>(sql6, new { x = subCaseId });
|
||
if (count > 0)
|
||
{
|
||
return new Response<string>
|
||
{
|
||
Result = "当前图斑一起分割的图斑存在分割的子图斑",
|
||
Message = "还原失败"
|
||
};
|
||
}
|
||
|
||
if (req.type == 0) // 目前此值必定为0
|
||
{
|
||
var sql =
|
||
$"select original_case_id from drone_case_split where new_case_id='{subCaseId}' and is_recover=0";
|
||
// 原始图斑
|
||
var oldCaseId = await db.Ado.SqlQuerySingleAsync<string>(sql);
|
||
|
||
if (string.IsNullOrEmpty(oldCaseId))
|
||
{
|
||
return new Response<string>
|
||
{
|
||
Result = "图斑未拆",
|
||
Message = "还原失败"
|
||
};
|
||
}
|
||
|
||
// 恢复还原图斑
|
||
await _processApp.RecoverProcess(oldCaseId);
|
||
// 还原案件
|
||
await db.Updateable<DroneCaseInfoXcsj>()
|
||
.SetColumns(c =>
|
||
new DroneCaseInfoXcsj
|
||
{
|
||
is_closed = 0,
|
||
is_split = 2
|
||
}).Where(a => a.Id == oldCaseId).ExecuteCommandAsync();
|
||
// 处理子图斑
|
||
// 查询子图斑
|
||
var subCaseQuery =
|
||
$"select new_case_id from drone_case_split where is_recover=0 and original_case_id = '{oldCaseId}'";
|
||
var newCaseIds = db.Ado.SqlQuery<string>(subCaseQuery);
|
||
foreach (var casePart in newCaseIds)
|
||
{
|
||
// 作废流程
|
||
var result = await _processApp.DeleteProcess(casePart);
|
||
// todo 做意外处理，当执行失败时，
|
||
await db.Updateable<DroneCaseSplit>().SetColumns(c => new DroneCaseSplit
|
||
{
|
||
IsRecover = 1
|
||
}).Where(a => a.NewCaseId == casePart).ExecuteCommandAsync();
|
||
// 关闭
|
||
await db.Updateable<DroneCaseInfoXcsj>().SetColumns(c => new DroneCaseInfoXcsj
|
||
{
|
||
is_closed = 1
|
||
}).Where(a => a.Id == casePart).ExecuteCommandAsync();
|
||
}
|
||
|
||
await db.Ado.CommitTranAsync();
|
||
return new Response<string>
|
||
{
|
||
Result = "还原成功",
|
||
Message = "还原成功"
|
||
};
|
||
}
|
||
else
|
||
{
|
||
foreach (var casePart in req.parts)
|
||
{
|
||
var original = await db.Queryable<DroneCaseInfoXcsj>().FirstAsync(t => t.Id == casePart.caseid);
|
||
// 关闭案件
|
||
var record = new DroneCaseInfoXcsj
|
||
{
|
||
Id = casePart.caseid,
|
||
is_closed = 1
|
||
};
|
||
await db.Updateable(record).ExecuteCommandAsync();
|
||
// 根据案件id取得流程
|
||
var process2 = await Repository.ChangeRepository<SugarRepositiry<WFProcess>>().AsQueryable()
|
||
.Where(t => SqlFunc.JsonField(t.InstanceInfo, "pkeyValue").Equals(casePart.caseid))
|
||
.FirstAsync();
|
||
// 作废流程
|
||
var result = await _processApp.DeleteProcess(process2.Id);
|
||
var splitRelation1 = new DroneCaseSplit();
|
||
splitRelation1.NewCaseId = caseId;
|
||
splitRelation1.OriginalCaseId = casePart.caseid;
|
||
relationships.Add(splitRelation1);
|
||
}
|
||
|
||
await db.Insertable(relationships).ExecuteCommandAsync();
|
||
// 生成新案件
|
||
var caseList = new List<DroneCaseInfoXcsj>();
|
||
var original1 = await db.Queryable<DroneCaseInfoXcsj>().FirstAsync(t => t.Id == req.parts[0].caseid);
|
||
var process = await Repository.ChangeRepository<SugarRepositiry<WFProcess>>().AsQueryable()
|
||
.Where(t => SqlFunc.JsonField(t.InstanceInfo, "pkeyValue").Equals(original1.case_no))
|
||
.FirstAsync();
|
||
var wktReader = new WKTReader();
|
||
var caseInfo = original1.MapTo<DroneCaseInfoXcsj>();
|
||
caseInfo.Id = caseId;
|
||
caseInfo.nongyongdi_area = req.nongyongdi_area;
|
||
caseInfo.gengdi_area = req.gengdi_area;
|
||
caseInfo.yongjiujibennongtian_area = req.yongjiujibennongtian_area;
|
||
caseInfo.shengtaibaohuhongxian_area = req.shengtaibaohuhongxian_area;
|
||
caseInfo.guotukongjianguihua_area = req.guotukongjianguihua_area;
|
||
caseInfo.zhongdianquyu_area = req.zhongdianquyu_area;
|
||
caseInfo.area = req.area;
|
||
// 设置时间
|
||
caseInfo.createtime = DateTime.Now;
|
||
// 设置创建人
|
||
caseInfo.createuser = user.Id + "";
|
||
caseInfo.createusername = user.Name;
|
||
// 填充图斑信息
|
||
var geometry = wktReader.Read(req.geom);
|
||
var gidStr = _commonDataManager.GetMaxKeyVal("gid", "drone_shp_data", 1);
|
||
var insertObj = new Dictionary<string, object>
|
||
{
|
||
{ "gid", gidStr.ToInt() },
|
||
{ "geom", geometry.AsBinary() }
|
||
};
|
||
// 插入图斑
|
||
await db.Insertable(insertObj).AS("drone_shp_data").ExecuteCommandAsync();
|
||
caseInfo.geomid = gidStr;
|
||
caseList.Add(caseInfo);
|
||
|
||
await db.Insertable(caseList).ExecuteCommandAsync();
|
||
|
||
// 发起新流程
|
||
var processId = Guid.NewGuid().ToString();
|
||
var instanceInfo = process.InstanceInfo;
|
||
instanceInfo.pkeyValue = caseInfo.Id;
|
||
string x = Json.ToJson(instanceInfo);
|
||
Console.WriteLine("instance: " + x);
|
||
await _processApp.SaveDraft(caseInfo.Id, process.SchemeCode, user.Id + "",
|
||
Json.ToJson(instanceInfo), process.ParentProcessId, process.ParentNodeId, process.ParentTaskId, 0,
|
||
caseInfo.case_description);
|
||
await _processApp.Create(caseInfo.Id, process.SchemeCode, user.Id + "", null, Json.ToJson(instanceInfo),
|
||
caseInfo.case_description);
|
||
|
||
await db.Ado.CommitTranAsync();
|
||
|
||
return new Response<string>
|
||
{
|
||
Result = "拆分成功",
|
||
Message = "拆分成功"
|
||
};
|
||
}
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
await db.Ado.RollbackTranAsync();
|
||
throw e;
|
||
}
|
||
}
|
||
|
||
#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)
|
||
{
|
||
RefAsync<int> totalcount = 0;
|
||
//查询案件的填报(anjianzhaopian)和整改图片信息(chaichufugenghoupic),补办手续（bubanzhaopian）
|
||
var caseImg = await client.Queryable<DroneCaseInfoXcsj>().FirstAsync(r => r.Id == caseid);
|
||
List<string> imageList = new List<string>();
|
||
|
||
if (caseImg != null)
|
||
{
|
||
// 合并
|
||
AddImagesToList(caseImg.anjianzhaopian, imageList);
|
||
AddImagesToList(caseImg.chaichufugenghoupic, imageList);
|
||
AddImagesToList(caseImg.bubanzhaopian, imageList);
|
||
}
|
||
|
||
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)
|
||
{
|
||
try
|
||
{
|
||
//var imgList = Newtonsoft.Json.JsonConvert.DeserializeObject<List<DroneImageRef>>(info);
|
||
client.Deleteable<DroneImageRef>(r => r.FilePath == path).ExecuteCommand();
|
||
return new Response<bool>
|
||
{
|
||
Result = true,
|
||
Message = "删除成功",
|
||
};
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
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;
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 案件操作历史记录
|
||
|
||
/// <summary>
|
||
/// 获取所有的案件信息
|
||
/// </summary>
|
||
/// <param name="req">案件编号查询</param>
|
||
/// <returns></returns>
|
||
public async Task<Response<PageInfo<List<DroneCaseInfoXcsj>>>> LoadCaseInfoListForUpdate(DroneCaseForChangeReq req)
|
||
{
|
||
RefAsync<int> totalcount = 0;
|
||
//var caseList = await client.Queryable<DroneCaseInfoXcsj>()
|
||
// .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<DroneCaseInfoXcsj>()
|
||
.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))
|
||
.OrderByDescending(r => r.createtime)
|
||
.Select(r => new DroneCaseInfoXcsj
|
||
{
|
||
Id = r.Id.SelectAll(),
|
||
})
|
||
.ToPageListAsync(req.page, req.limit, totalcount);
|
||
return new Response<PageInfo<List<DroneCaseInfoXcsj>>>()
|
||
{
|
||
Result = new PageInfo<List<DroneCaseInfoXcsj>>
|
||
{
|
||
Items = caseList,
|
||
Total = totalcount
|
||
}
|
||
};
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取单个案件信息
|
||
/// </summary>
|
||
/// <param name="id">案件id</param>
|
||
/// <returns></returns>
|
||
public async Task<Response<DroneCaseInfoXcsj>> LoadCaseInfoById(string id)
|
||
{
|
||
var caseInfo = await client.Queryable<DroneCaseInfoXcsj>().FirstAsync(r => r.Id == id);
|
||
|
||
return new Response<DroneCaseInfoXcsj>()
|
||
{
|
||
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(DroneCaseInfoXcsj req)
|
||
{
|
||
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
|
||
{
|
||
// 使用事务提交数据
|
||
var transResult = await db.UseTranAsync(async () =>
|
||
{
|
||
// 记录案件信息
|
||
var caseInfo = await db.Queryable<DroneCaseInfoXcsj>().FirstAsync(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;
|
||
await db.Insertable(caseHistory).ExecuteCommandAsync();
|
||
|
||
// 更新案件信息
|
||
await db.Updateable(req)
|
||
.UpdateColumns(c => new
|
||
{
|
||
c.case_no,
|
||
c.case_name,
|
||
c.case_description,
|
||
c.address,
|
||
c.lng,
|
||
c.lat,
|
||
//c.typeid,
|
||
//c.typename,
|
||
c.drone_no,
|
||
c.countyid,
|
||
c.countyname,
|
||
c.streetid,
|
||
c.streetname,
|
||
c.communityid,
|
||
c.communityname,
|
||
//c.remark,
|
||
c.area,
|
||
c.nongyongdi_area,
|
||
c.gengdi_area,
|
||
c.yongjiujibennongtian_area,
|
||
c.zhongdianquyu_area,
|
||
c.shengtaibaohuhongxian_area,
|
||
c.guotukongjianguihua_area,
|
||
//c.casepic
|
||
}).ExecuteCommandAsync();
|
||
});
|
||
|
||
return new Response<bool>
|
||
{
|
||
Code = 200,
|
||
Result = transResult.IsSuccess,
|
||
Message = transResult.IsSuccess ? "更新成功" : "更新失败"
|
||
};
|
||
}
|
||
}
|
||
|
||
#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;
|
||
|
||
DroneCaseInfoXcsj cas = new DroneCaseInfoXcsj();
|
||
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<DroneCaseInfoXcsj>().ToList();
|
||
//var caseInfo = client.Queryable<DroneCaseInfoXcsj>().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<DroneCaseInfoXcsj>().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<DroneCaseInfoXcsj>()
|
||
.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> { };
|
||
}
|
||
|
||
#endregion
|
||
} |