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

4021 lines
178 KiB
C#
Raw Permalink Blame History

This file contains ambiguous Unicode characters!

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

using System.Data;
using System.IO.Compression;
using System.Text;
using Infrastructure;
using Infrastructure.Extensions;
using Infrastructure.Helpers;
using Microsoft.Extensions.Configuration;
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 OpenAuth.App.BaseApp.Base;
using OpenAuth.App.BaseApp.Shape;
using OpenAuth.App.BasicQueryService;
using OpenAuth.App.Common;
using OpenAuth.App.FormModule;
using OpenAuth.App.Interface;
using OpenAuth.App.Request;
using OpenAuth.App.ServiceApp.DroneCaseInfo.Request;
using OpenAuth.App.ServiceApp.DroneCaseInfo.Response;
using OpenAuth.App.ServiceApp.Request;
using OpenAuth.App.ServiceApp.Response;
using OpenAuth.Repository;
using OpenAuth.Repository.Domain;
using SqlSugar;
using Shapefile = NetTopologySuite.IO.Esri.Shapefile;
namespace OpenAuth.App.ServiceApp.DroneCaseInfo;
public class DroneCaseInfoMineralsApp : SqlSugarBaseApp<DroneCaseInfoMinerals, SugarDbContext>
{
private readonly IConfiguration _configuration;
private readonly ISqlSugarClient client;
private readonly WFProcessApp _processApp;
private readonly ShpLayerSourceApp _shpLayerSourceApp;
CommonDataManager _commonDataManager;
public DroneCaseInfoMineralsApp(ISugarUnitOfWork<SugarDbContext> unitWork,
ISimpleClient<DroneCaseInfoMinerals> repository, IAuth auth, ISqlSugarClient sqlSugarClient,
IConfiguration configuration, WFProcessApp processApp, ShpLayerSourceApp shpLayerSourceApp,
CommonDataManager commonDataManager) : base(unitWork,
repository,
auth)
{
_configuration = configuration;
_processApp = processApp;
_shpLayerSourceApp = shpLayerSourceApp;
this.client = sqlSugarClient;
_commonDataManager = commonDataManager;
}
/// <summary>
/// 案件详情
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Response<AddOrUpdateDroneCaseInfoReqExtCurrent>> GetCaseInfo(string id)
{
var res = new AddOrUpdateDroneCaseInfoReqExtCurrent();
res.Init();
//详情
DroneCaseInfoMinerals info = null;
var flag = StringExtension.IsGuid(id);
if (flag)
{
info = await Repository.AsSugarClient().Queryable<DroneCaseInfoMinerals>().Where(c => c.Id == id)
.FirstAsync();
}
else
{
info = await Repository.AsSugarClient().Queryable<DroneCaseInfoMinerals>().Where(c => c.case_no == id)
.FirstAsync();
}
//历史案件
var relationCaseNoList = await this.Repository.AsSugarClient().Queryable<DroneCaseInfoRelation>()
.Where(a => a.caseid == info.Id).Select(a => a.relation_case_no).ToListAsync();
//案件标签
var tags = await this.Repository.AsSugarClient().Queryable<DroneCaseInfoTag>()
.LeftJoin<SysCategory>((t, c) => t.tagid == c.Id.ToString())
.Where((t, c) => t.caseid == info.Id && c.TypeId == "DRONE_CASE_TAG")
.Select((t, c) => c.Name).ToListAsync();
//图斑的中心点
StringBuilder sql = new StringBuilder();
sql.AppendFormat(
$"SELECT st_astext(ST_Centroid(geom)) lnglat FROM \"drone_shp_data\" where relid = '{info.Id}' and geom is not null");
var dt = await this.Repository.AsSugarClient().Ado.GetDataTableAsync(sql.ToString());
if (dt.Rows.Count > 0)
{
string lngLat = dt.Rows[0]["lnglat"].ToString();
lngLat = lngLat.Replace("POINT(", "").Replace(")", "");
var lngLats = lngLat.Split(" ");
res.lng = Decimal.Round(Decimal.Parse(lngLats[0]), 6);
res.lat = Decimal.Round(Decimal.Parse(lngLats[1]), 6);
}
else
{
if (!string.IsNullOrEmpty(info.centerlng_base))
{
res.lng = Decimal.Round(Decimal.Parse(info.centerlng_base), 6);
}
if (!string.IsNullOrEmpty(info.centerlat_base))
{
res.lat = Decimal.Round(Decimal.Parse(info.centerlat_base), 6);
}
}
res.info = info;
res.relationCaseNo = relationCaseNoList;
res.tags = tags;
return new Response<AddOrUpdateDroneCaseInfoReqExtCurrent>
{
Result = res,
Message = "获取数据成功"
};
}
/// <summary>
/// 案件详情
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Response<DroneCaseInfoMinerals>> GetCaseInfoById(string id)
{
var info = await Repository.AsSugarClient().Queryable<DroneCaseInfoMinerals>()
.Where(c => c.Id == id)
.FirstAsync();
info.hefapanding_fill = "加工".Equals(info.typename) ? info.hefapanding_jgfill : info.hefapanding_fill;
return new Response<DroneCaseInfoMinerals>
{
Result = info,
Message = "获取数据成功"
};
}
#region 工作管理
/// <summary>
/// 图斑调整
/// </summary>
/// <param name="req"></param>
/// <returns>查询待填报图斑</returns>
[Obsolete]
public async Task<Response<PageInfo<List<dynamic>>>> LoadModifyingTaskList(MineralsTaskListReq req)
{
RefAsync<int> total = 0;
using var db = Repository.AsSugarClient();
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoMineralsSchemeCode").Value;
var authStrategyContext = _auth.GetCurrentUser();
if (authStrategyContext == null)
{
throw new Exception("用户未登录");
}
var user = authStrategyContext.User;
var orgList = authStrategyContext.Orgs;
var orgIds = orgList.Select(x => x.Id.ToString()).ToList();
var isCity = 0;
if (authStrategyContext.User.Id.Equals(-1))
{
isCity = 1;
}
var year = req.year == 0 ? DateTime.Now.Year + "" : req.year + "";
//var dateTime = req.year == 0 ? DateTime.Now : new DateTime(req.year, 1, 1);
DateTime? startTime = null, endTime = null;
if (req.startTime != null && req.endTime != null)
{
startTime = DateTime.Parse(req.startTime);
endTime = DateTime.Parse(req.endTime).AddDays(1).AddMilliseconds(-1);
}
// todo 县级驳回 市级驳回
var result = await db.Queryable<DroneCaseInfoMinerals>()
.LeftJoin<WFTask>((m, t) => m.Id.Equals(t.ProcessId))
.LeftJoin<DroneTaskFavorite>((m, t, f) => f.TaskId.Equals(t.Id) && f.FavoriteUserId == user.Id.ToString())
.Where((m, t) => t.State.Equals(1)) // 激活状态任务
.Where((m, t) => t.ProcessCode.Equals(schemeCode))
.Where((m, t) => m.handle_status_id.Equals(1)) // 图斑状态
.Where(m => m.is_closed.Equals(0))
.WhereIF(
!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("市级驳回") &&
req.nowStatus.Contains("县级驳回"),
(m, t) => m.is_tbsjbohui > 0 || m.is_tbxjbohui > 0 || m.is_zgsjbohui > 0 || m.is_zgxjbohui > 0)
.WhereIF(!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("市级驳回"),
(m, t) => m.is_tbsjbohui > 0 || m.is_zgsjbohui > 0)
.WhereIF(!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("县级驳回"),
(m, t) => m.is_tbxjbohui > 0 || m.is_zgxjbohui > 0)
.WhereIF(req.year != 0, m => m.syear_base.Equals(req.year.ToString()))
//.Where(m => m.syear_base.Equals(year))
//.Where(m => SqlFunc.DateIsSame(Convert.ToDateTime(m.xiafatime_base), dateTime, DateType.Year))
.WhereIF(isCity.Equals(0), (m, t) =>
orgIds.Contains(m.countyid) || orgIds.Contains(m.streetid) || orgIds.Contains(m.communityid)) // 数据权限
.WhereIF(!string.IsNullOrEmpty(req.countyid), m => m.countyid.Equals(req.countyid)) // 县区
.WhereIF(!string.IsNullOrEmpty(req.streetid), m => m.streetid.Equals(req.streetid)) // 街镇
.WhereIF(!string.IsNullOrEmpty(req.caseNo), m => m.case_no.Contains(req.caseNo)) // 图斑号查询
.WhereIF(!string.IsNullOrEmpty(req.tubanlaiyuan), m => m.tubanlaiyuan.Equals(req.tubanlaiyuan)) // 图斑来源
.WhereIF(!string.IsNullOrEmpty(req.typename), m => m.typename.Equals(req.typename)) // 图斑类型 开采 加工
.WhereIF(req.type == 1, (m, t, f) => f.FavoriteUserId.Equals(user.Id.ToString()))
.WhereIF(!string.IsNullOrEmpty(req.startTime) && !string.IsNullOrEmpty(req.endTime), m =>
SqlFunc.Between(m.xiafatime_base, startTime,
endTime)) // 时间段查询
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("weifaarea_base"), m => SqlFunc.ToDouble(m.weifaarea_base),
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("gengdi_area"), m => SqlFunc.ToDouble(m.weifaarea_base),
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("synchronoustime"),
m => m.xiafatime_base,
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
//县区 街镇 图斑号 处理状态 图斑类型 总面积 违法面积 耕地面积
.Select<dynamic>((m, t, f) => new
{
Fid = f.Id,
id = m.Id,
processid = t.ProcessId,
processcode = t.ProcessCode, // 流程编码
taskid = t.Id,
caseno = m.case_no,
handlestatusid = m.handle_status_id, // 处理状态
unitname = t.UnitName, // 节点名称
tubanlaiyuan = m.tubanlaiyuan, // 图斑来源
typename = m.typename, // 图斑类型
geomid = m.geomid, // 图斑id
weifaarea = m.weifaarea_punish, // 违法面积
countyid = m.countyid, // 县区
countyname = m.countyname, // 县区
streetid = m.streetid, // 街镇
streetname = m.streetname, // 街镇
m.weifaarea_base, // 图斑面积
gengdi_area = m.gengdi_area, // 耕地面积
//is_shijibohui：市级驳回 is_drawback： 县级驳回
is_shijibohui = m.is_zgsjbohui,
is_drawback = m.is_zgxjbohui,
shijibohui_count = m.is_tbsjbohui + m.is_zgsjbohui,
illegalarea = string.IsNullOrEmpty(m.weifazhandiarea_base)
? m.weifakaicaiarea_base
: m.weifazhandiarea_base // 违法面积
})
.ToPageListAsync(req.page, req.limit, total);
return new Response<PageInfo<List<dynamic>>>
{
Result = new PageInfo<List<dynamic>>
{
Total = total,
Items = result
}
};
}
/// <summary>
/// 图斑填报
/// </summary>
/// <param name="req"></param>
/// <returns>查询待填报，整改的图斑</returns>
public async Task<Response<PageInfo<List<dynamic>>>> LoadReportingTaskList(MineralsTaskListReq req)
{
RefAsync<int> total = 0;
using var db = Repository.AsSugarClient();
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoMineralsSchemeCode").Value;
var authStrategyContext = _auth.GetCurrentUser();
if (authStrategyContext == null)
{
throw new Exception("用户未登录");
}
var user = authStrategyContext.User;
var orgList = authStrategyContext.Orgs;
var orgIds = orgList.Select(x => x.Id.ToString()).ToList();
var isCity = 0;
if (authStrategyContext.User.Id.Equals(-1))
{
isCity = 1;
}
List<int> statusList = null;
if (!string.IsNullOrEmpty(req.nowStatus))
{
var temp = req.nowStatus.Split(",").ToList();
temp.Remove("市级驳回");
temp.Remove("县级驳回");
statusList = temp.Select(int.Parse).ToList();
}
var year = req.year == 0 ? DateTime.Now.Year + "" : req.year + "";
//var dateTime = req.year == 0 ? DateTime.Now : new DateTime(req.year, 1, 1);
DateTime? startTime = null, endTime = null;
if (req.startTime != null && req.endTime != null)
{
startTime = DateTime.Parse(req.startTime);
endTime = DateTime.Parse(req.endTime).AddDays(1).AddMilliseconds(-1);
}
DateTime now = DateTime.Now;
var result = await db.Queryable<DroneCaseInfoMinerals>()
.LeftJoin<WFTask>((m, t) => m.Id.Equals(t.ProcessId))
.LeftJoin<DroneTaskFavorite>((m, t, f) => f.TaskId.Equals(t.Id) && f.FavoriteUserId == user.Id.ToString())
.Where((m, t) => t.State.Equals(1)) // 激活状态任务
.Where((m, t) => t.ProcessCode.Equals(schemeCode))
.Where((m, t) => m.handle_status_id.Equals(1) || m.handle_status_id.Equals(2)) // 图斑状态
.Where(m => m.is_closed.Equals(0))
.WhereIF(
!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("市级驳回") &&
req.nowStatus.Contains("县级驳回"),
(m, t) => m.is_tbsjbohui > 0 || m.is_tbxjbohui > 0 || m.is_zgsjbohui > 0 || m.is_zgxjbohui > 0)
.WhereIF(!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("市级驳回"),
(m, t) => m.is_tbsjbohui > 0)
.WhereIF(!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("县级驳回"),
(m, t) => m.is_tbxjbohui > 0)
.WhereIF(req.year != 0, m => m.syear_base.Equals(req.year.ToString()))
//.Where(m => m.syear_base.Equals(year))
//.Where(m => SqlFunc.DateIsSame(Convert.ToDateTime(m.xiafatime_base), dateTime, DateType.Year))
.WhereIF(isCity.Equals(0), (m, t) =>
orgIds.Contains(m.countyid) || orgIds.Contains(m.streetid) || orgIds.Contains(m.communityid)) // 数据权限
.WhereIF(!string.IsNullOrEmpty(req.countyid), m => m.countyid.Equals(req.countyid)) // 县区
.WhereIF(!string.IsNullOrEmpty(req.streetid), m => m.streetid.Equals(req.streetid)) // 街镇
.WhereIF(!string.IsNullOrEmpty(req.caseNo), m => m.case_no.Contains(req.caseNo)) // 图斑号查询
.WhereIF(!string.IsNullOrEmpty(req.tubanlaiyuan), m => m.tubanlaiyuan.Equals(req.tubanlaiyuan)) // 图斑来源
.WhereIF(!string.IsNullOrEmpty(req.typename), m => m.typename.Equals(req.typename)) // 图斑类型 开采 加工
.WhereIF(req.type == 1, (m, t, f) => f.FavoriteUserId.Equals(user.Id.ToString()))
.WhereIF(statusList != null && statusList.Any(), m => statusList.Contains((int)m.handle_status_id)) // 图斑状态
// todo
.WhereIF(!string.IsNullOrEmpty(req.startTime) && !string.IsNullOrEmpty(req.endTime), m =>
SqlFunc.Between(m.xiafatime_base, startTime, endTime)) // 时间段查询
//县区 街镇 图斑号 处理状态 图斑类型 总面积 违法面积 耕地面积
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("weifaarea_base"), m => SqlFunc.ToDouble(m.weifaarea_base),
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("gengdi_area"), m => SqlFunc.ToDouble(m.weifaarea_base),
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("synchronoustime"),
m => m.xiafatime_base,
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
.Select<dynamic>((m, t, f) => new
{
Fid = f.Id,
id = m.Id,
processid = t.ProcessId,
processcode = t.ProcessCode, // 流程编码
taskid = t.Id,
caseno = m.case_no,
handlestatusid = m.handle_status_id, // 处理状态
unitname = t.UnitName,
tubanlaiyuan = m.tubanlaiyuan, // 图斑来源
typename = m.typename, // 图斑类型
geomid = m.geomid, // 图斑id
weifaarea = m.weifaarea_punish, // 违法面积
countyid = m.countyid, // 县区
countyname = m.countyname, // 县区
streetid = m.streetid, // 街镇
streetname = m.streetname, // 街镇
m.weifaarea_base, // 图斑面积
gengdi_area = m.gengdi_area, // 耕地面积
shijibohui_count = SqlFunc.IsNull(m.is_tbsjbohui, 0) + SqlFunc.IsNull(m.is_zgsjbohui, 0),
is_shijibohui = m.is_tbsjbohui,
is_drawback = m.is_tbxjbohui,
illegalarea = string.IsNullOrEmpty(m.weifazhandiarea_base)
? m.weifakaicaiarea_base
: m.weifazhandiarea_base, // 违法面积
timeout = (now > (t.TimeoutNotice ?? now)) ? 1 : 0
})
.ToPageListAsync(req.page, req.limit, total);
return new Response<PageInfo<List<dynamic>>>
{
Result = new PageInfo<List<dynamic>>
{
Total = total,
Items = result
}
};
}
/// <summary>
/// 填报审核
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public async Task<Response<PageInfo<List<MineralsCaseInfoResponse>>>> LoadReportingAuditTaskList(MineralsTaskListReq req)
{
RefAsync<int> total = 0;
using var db = Repository.AsSugarClient();
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoMineralsSchemeCode").Value;
var authStrategyContext = _auth.GetCurrentUser();
if (authStrategyContext == null)
{
throw new Exception("用户未登录");
}
var user = authStrategyContext.User;
var orgList = authStrategyContext.Orgs;
var orgIds = orgList.Select(x => x.Id.ToString()).ToList();
var isCity = 0;
if (authStrategyContext.User.Id.Equals(-1))
{
isCity = 1;
}
var orgLevel = orgList.Min(a => a.Level); // 0 临沂市 1 县区 2-街镇
if (orgLevel.Equals(0))
{
isCity = 1;
}
var year = req.year == 0 ? DateTime.Now.Year + "" : req.year + "";
//var dateTime = req.year == 0 ? DateTime.Now : new DateTime(req.year, 1, 1);
var result = await db.Queryable<DroneCaseInfoMinerals>()
.LeftJoin<WFTask>((m, t) => m.Id.Equals(t.ProcessId))
.LeftJoin<DroneTaskFavorite>((m, t, f) => f.TaskId.Equals(t.Id) && f.FavoriteUserId == user.Id.ToString())
.Where((m, t) => t.State.Equals(1)) // 激活状态任务
.Where((m, t) => t.ProcessCode.Equals(schemeCode))
.Where(m => m.is_closed.Equals(0))
.WhereIF(
!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("市级驳回") &&
req.nowStatus.Contains("县级驳回"),
(m, t) => m.is_tbsjbohui > 0 || m.is_tbxjbohui > 0 || m.is_zgsjbohui > 0 || m.is_zgxjbohui > 0)
.WhereIF(!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("市级驳回"),
(m, t) => m.is_tbsjbohui > 0)
.WhereIF(!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("县级驳回"),
(m, t) => m.is_tbxjbohui > 0)
.WhereIF(orgLevel.Equals(0), m => m.handle_status_id.Equals(4))
.WhereIF(orgLevel != 0, (m, t) => m.handle_status_id.Equals(3)) // 图斑状态
.WhereIF(req.year != 0, m => m.syear_base.Equals(req.year.ToString()))
//.Where(m => m.syear_base.Equals(year))
//.Where(m => SqlFunc.DateIsSame(Convert.ToDateTime(m.xiafatime_base), dateTime, DateType.Year))
.WhereIF(isCity.Equals(0), (m, t) =>
orgIds.Contains(m.countyid) || orgIds.Contains(m.streetid) || orgIds.Contains(m.communityid)) // 数据权限
.WhereIF(!string.IsNullOrEmpty(req.countyid), m => m.countyid.Equals(req.countyid)) // 县区
.WhereIF(!string.IsNullOrEmpty(req.streetid), m => m.streetid.Equals(req.streetid)) // 街镇
.WhereIF(!string.IsNullOrEmpty(req.caseNo), m => m.case_no.Contains(req.caseNo)) // 图斑号查询
.WhereIF(!string.IsNullOrEmpty(req.tubanlaiyuan), m => m.tubanlaiyuan.Equals(req.tubanlaiyuan)) // 图斑来源
.WhereIF(!string.IsNullOrEmpty(req.typename), m => m.typename.Equals(req.typename)) // 图斑类型 开采 加工
.WhereIF(req.type == 1, (m, t, f) => f.FavoriteUserId.Equals(user.Id.ToString()))
.WhereIF(!string.IsNullOrEmpty(req.startTime) && !string.IsNullOrEmpty(req.endTime), m =>
SqlFunc.Between(m.xiafatime_base, Convert.ToDateTime(req.startTime),
Convert.ToDateTime(req.endTime))) // 时间段查询
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("weifaarea_base"), m => SqlFunc.ToDouble(m.weifaarea_base),
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("gengdi_area"), m => SqlFunc.ToDouble(m.weifaarea_base),
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("synchronoustime"),
m => m.xiafatime_base,
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
//县区 街镇 图斑号 处理状态 图斑类型 总面积 违法面积 耕地面积
.Select<MineralsCaseInfoResponse>((m, t, f) => new MineralsCaseInfoResponse()
{
Fid = f.Id,
id = m.Id,
processid = t.ProcessId,
processcode = t.ProcessCode, // 流程编码
taskid = t.Id,
caseno = m.case_no,
handlestatusid = m.handle_status_id, // 处理状态
unitname = t.UnitName,
tubanlaiyuan = m.tubanlaiyuan, // 图斑来源
typename = m.typename, // 图斑类型
geomid = m.geomid, // 图斑id
weifaarea = m.weifaarea_punish, // 违法面积
countyid = m.countyid, // 县区
countyname = m.countyname, // 县区
streetid = m.streetid, // 街镇
streetname = m.streetname, // 街镇
weifaarea_base = m.weifaarea_base, // 图斑面积
gengdi_area = m.gengdi_area, // 耕地面积
is_tbsjbohui = m.is_tbsjbohui,
is_zgsjbohui = m.is_zgsjbohui,
is_shijibohui = m.is_tbsjbohui, //填报市级驳回
is_drawback = m.is_tbxjbohui, // 填报县级驳回
weifakaicaiarea_base = m.weifakaicaiarea_base ,
weifazhandiarea_base = m.weifazhandiarea_base,
})
.ToPageListAsync(req.page, req.limit, total);
foreach (var caseinfo in result)
{
caseinfo.shijibohui_count = caseinfo.is_tbsjbohui ?? 0 + caseinfo.is_zgsjbohui ?? 0;
caseinfo.illegalarea = caseinfo.typename.Equals("开采")
? caseinfo.weifakaicaiarea_base
: caseinfo.weifazhandiarea_base; // 违法面积
}
return new Response<PageInfo<List<MineralsCaseInfoResponse>>>
{
Result = new PageInfo<List<MineralsCaseInfoResponse>>
{
Total = total,
Items = result
}
};
}
/// <summary>
/// 图斑查处
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public async Task<Response<PageInfo<List<dynamic>>>> LoadExaminingTaskList(MineralsTaskListReq req)
{
RefAsync<int> total = 0;
using var db = Repository.AsSugarClient();
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoMineralsSchemeCode").Value;
var authStrategyContext = _auth.GetCurrentUser();
if (authStrategyContext == null)
{
throw new Exception("用户未登录");
}
var user = authStrategyContext.User;
var orgList = authStrategyContext.Orgs;
var orgIds = orgList.Select(x => x.Id.ToString()).ToList();
var isCity = 0;
if (authStrategyContext.User.Id.Equals(-1))
{
isCity = 1;
}
//var dateTime = req.year == 0 ? DateTime.Now : new DateTime(req.year, 1, 1);
var year = req.year == 0 ? DateTime.Now.Year + "" : req.year + "";
var result = await db.Queryable<DroneCaseInfoMinerals>()
.LeftJoin<WFTask>((m, t) => m.Id.Equals(t.ProcessId))
.LeftJoin<DroneTaskFavorite>((m, t, f) => f.TaskId.Equals(t.Id) && f.FavoriteUserId == user.Id.ToString())
.Where((m, t) => t.State.Equals(1)) // 激活状态任务
.Where((m, t) => t.ProcessCode.Equals(schemeCode))
.Where((m, t) => m.handle_status_id.Equals(5)) // 图斑状态 待查处
.Where(m => m.is_closed.Equals(0)) // 未关闭任务
.WhereIF(
!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("市级驳回") &&
req.nowStatus.Contains("县级驳回"),
(m, t) => m.is_tbsjbohui > 0 || m.is_tbxjbohui > 0 || m.is_zgsjbohui > 0 || m.is_zgxjbohui > 0)
.WhereIF(!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("市级驳回"),
(m, t) => m.is_zgsjbohui > 0)
.WhereIF(!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("县级驳回"),
(m, t) => m.is_zgxjbohui > 0)
.WhereIF(req.year != 0, m => m.syear_base.Equals(req.year.ToString()))
//.Where(m => m.syear_base.Equals(year))
//.Where(m => SqlFunc.DateIsSame(Convert.ToDateTime(m.xiafatime_base), dateTime, DateType.Year))
.WhereIF(isCity.Equals(0), (m, t) =>
orgIds.Contains(m.countyid) || orgIds.Contains(m.streetid) || orgIds.Contains(m.communityid)) // 数据权限
.WhereIF(req.register != null, m => m.islian_punish.Equals(req.register))
.WhereIF(!string.IsNullOrEmpty(req.countyid), m => m.countyid.Equals(req.countyid)) // 县区
.WhereIF(!string.IsNullOrEmpty(req.streetid), m => m.streetid.Equals(req.streetid)) // 街镇
.WhereIF(!string.IsNullOrEmpty(req.caseNo), m => m.case_no.Contains(req.caseNo)) // 图斑号查询
.WhereIF(!string.IsNullOrEmpty(req.tubanlaiyuan), m => m.tubanlaiyuan.Equals(req.tubanlaiyuan)) // 图斑来源
.WhereIF(!string.IsNullOrEmpty(req.typename), m => m.typename.Equals(req.typename)) // 图斑类型 开采 加工
.WhereIF(req.type == 1, (m, t, f) => f.FavoriteUserId.Equals(user.Id.ToString()))
.WhereIF(!string.IsNullOrEmpty(req.startTime) && !string.IsNullOrEmpty(req.endTime), m =>
SqlFunc.Between(m.xiafatime_base, Convert.ToDateTime(req.startTime),
Convert.ToDateTime(req.endTime))) // 时间段查询
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("weifaarea_base"), m => SqlFunc.ToDouble(m.weifaarea_base),
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("gengdi_area"), m => SqlFunc.ToDouble(m.weifaarea_base),
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("synchronoustime"),
m => m.xiafatime_base,
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
//县区 街镇 图斑号 处理状态 图斑类型 总面积 违法面积 耕地面积
.Select<dynamic>((m, t, f) => new
{
Fid = f.Id,
id = m.Id,
processid = t.ProcessId,
processcode = t.ProcessCode, // 流程编码
taskid = t.Id,
caseno = m.case_no,
handlestatusid = m.handle_status_id, // 处理状态
unitname = t.UnitName,
tubanlaiyuan = m.tubanlaiyuan, // 图斑来源
typename = m.typename, // 图斑类型
geomid = m.geomid, // 图斑id
weifaarea = m.weifaarea_punish, // 违法面积
countyid = m.countyid, // 县区
countyname = m.countyname, // 县区
streetid = m.streetid, // 街镇
streetname = m.streetname, // 街镇
m.weifaarea_base, // 图斑面积
gengdi_area = m.gengdi_area, // 耕地面积
shijibohui_count = m.is_tbsjbohui + m.is_zgsjbohui,
is_shijibohui = m.is_zgsjbohui,
is_drawback = m.is_zgxjbohui,
illegalarea = m.typename.Equals("开采") ? m.weifakaicaiarea_base : m.weifazhandiarea_base // 违法面积
})
.ToPageListAsync(req.page, req.limit, total);
return new Response<PageInfo<List<dynamic>>>
{
Result = new PageInfo<List<dynamic>>
{
Total = total,
Items = result
}
};
}
/// <summary>
/// 查处审核
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
[Obsolete(message: "矿产专题已不使用")]
public async Task<Response<PageInfo<List<dynamic>>>> LoadExaminingAuditTaskList(MineralsTaskListReq req)
{
RefAsync<int> total = 0;
using var db = Repository.AsSugarClient();
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoMineralsSchemeCode").Value;
var authStrategyContext = _auth.GetCurrentUser();
if (authStrategyContext == null)
{
throw new Exception("用户未登录");
}
var user = authStrategyContext.User;
var orgList = authStrategyContext.Orgs;
var orgIds = orgList.Select(x => x.Id.ToString()).ToList();
var isCity = 0;
if (authStrategyContext.User.Id.Equals(-1))
{
isCity = 1;
}
var orgLevel = orgList.Min(a => a.Level); // 0 临沂市 1 县区 2-街镇
if (orgLevel.Equals(0))
{
isCity = 1;
}
//var dateTime = req.year == 0 ? DateTime.Now : new DateTime(req.year, 1, 1);
var year = req.year == 0 ? DateTime.Now.Year + "" : req.year + "";
var result = await db.Queryable<DroneCaseInfoMinerals>()
.LeftJoin<WFTask>((m, t) => m.Id.Equals(t.ProcessId))
.LeftJoin<DroneTaskFavorite>((m, t, f) => f.TaskId.Equals(t.Id) && f.FavoriteUserId == user.Id.ToString())
.Where((m, t) => t.State.Equals(1)) // 激活状态任务
.Where((m, t) => t.ProcessCode.Equals(schemeCode))
.Where(m => m.is_closed.Equals(0)) // 未关闭任务
.WhereIF(
!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("市级驳回") &&
req.nowStatus.Contains("县级驳回"),
(m, t) => m.is_tbsjbohui > 0 || m.is_tbxjbohui > 0 || m.is_zgsjbohui > 0 || m.is_zgxjbohui > 0)
.WhereIF(!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("市级驳回"),
(m, t) => m.is_zgsjbohui > 0)
.WhereIF(!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("县级驳回"),
(m, t) => m.is_zgxjbohui > 0)
.WhereIF(orgLevel.Equals(0), m => m.handle_status_id.Equals(7))
.WhereIF(orgLevel != 0, m => m.handle_status_id.Equals(6)) // 图斑状态
.WhereIF(req.year != 0, m => m.syear_base.Equals(req.year.ToString()))
//.Where(m=>m.syear_base.Equals(year))
//.Where(m => SqlFunc.DateIsSame(Convert.ToDateTime(m.xiafatime_base), dateTime, DateType.Year))
.WhereIF(isCity.Equals(0), (m, t) =>
orgIds.Contains(m.countyid) || orgIds.Contains(m.streetid) || orgIds.Contains(m.communityid)) // 数据权限
.WhereIF(req.register != null, m => m.islian_punish.Equals(req.register))
.WhereIF(!string.IsNullOrEmpty(req.countyid), m => m.countyid.Equals(req.countyid)) // 县区
.WhereIF(!string.IsNullOrEmpty(req.streetid), m => m.streetid.Equals(req.streetid)) // 街镇
.WhereIF(!string.IsNullOrEmpty(req.caseNo), m => m.case_no.Contains(req.caseNo)) // 图斑号查询
.WhereIF(!string.IsNullOrEmpty(req.tubanlaiyuan), m => m.tubanlaiyuan.Equals(req.tubanlaiyuan)) // 图斑来源
.WhereIF(!string.IsNullOrEmpty(req.typename), m => m.typename.Equals(req.typename)) // 图斑类型 开采 加工
.WhereIF(req.type == 1, (m, t, f) => f.FavoriteUserId.Equals(user.Id.ToString()))
.WhereIF(!string.IsNullOrEmpty(req.startTime) && !string.IsNullOrEmpty(req.endTime), m =>
SqlFunc.Between(m.xiafatime_base, Convert.ToDateTime(req.startTime),
Convert.ToDateTime(req.endTime))) // 时间段查询
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("weifaarea_base"), m => SqlFunc.ToDouble(m.weifaarea_base),
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("gengdi_area"), m => SqlFunc.ToDouble(m.weifaarea_base),
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("synchronoustime"),
m => m.xiafatime_base,
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
//县区 街镇 图斑号 处理状态 图斑类型 总面积 违法面积 耕地面积
.Select<dynamic>((m, t, f) => new
{
Fid = f.Id,
id = m.Id,
processid = t.ProcessId,
processcode = t.ProcessCode, // 流程编码
taskid = t.Id,
caseno = m.case_no,
handlestatusid = m.handle_status_id, // 处理状态
unitname = t.UnitName,
tubanlaiyuan = m.tubanlaiyuan, // 图斑来源
typename = m.typename, // 图斑类型
geomid = m.geomid, // 图斑id
weifaarea = m.weifaarea_punish, // 违法面积
countyid = m.countyid, // 县区
countyname = m.countyname, // 县区
streetid = m.streetid, // 街镇
streetname = m.streetname, // 街镇
m.weifaarea_base, // 图斑面积
gengdi_area = m.gengdi_area, // 耕地面积
shijibohui_count = m.is_tbsjbohui + m.is_zgsjbohui,
m.is_tbsjbohui,
m.is_zgsjbohui,
m.is_tbxjbohui,
m.is_zgxjbohui,
illegalarea = m.typename.Equals("开采") ? m.weifakaicaiarea_base : m.weifazhandiarea_base // 违法面积
})
.ToPageListAsync(req.page, req.limit, total);
return new Response<PageInfo<List<dynamic>>>
{
Result = new PageInfo<List<dynamic>>
{
Total = total,
Items = result
}
};
}
/// <summary>
/// 图斑整改
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public async Task<Response<PageInfo<List<dynamic>>>> LoadRectifyingTaskList(MineralsTaskListReq req)
{
RefAsync<int> total = 0;
using var db = Repository.AsSugarClient();
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoMineralsSchemeCode").Value;
var authStrategyContext = _auth.GetCurrentUser();
if (authStrategyContext == null)
{
throw new Exception("用户未登录");
}
var user = authStrategyContext.User;
var orgList = authStrategyContext.Orgs;
var orgIds = orgList.Select(x => x.Id.ToString()).ToList();
var isCity = 0;
if (authStrategyContext.User.Id.Equals(-1))
{
isCity = 1;
}
//var dateTime = req.year == 0 ? DateTime.Now : new DateTime(req.year, 1, 1);
var year = req.year == 0 ? DateTime.Now.Year + "" : req.year + "";
var result = await db.Queryable<DroneCaseInfoMinerals>()
.LeftJoin<WFTask>((m, t) => m.Id.Equals(t.ProcessId))
.LeftJoin<DroneTaskFavorite>((m, t, f) => f.TaskId.Equals(t.Id) && f.FavoriteUserId == user.Id.ToString())
.Where((m, t) => t.State.Equals(1)) // 激活状态任务
.Where((m, t) => t.ProcessCode.Equals(schemeCode))
.Where((m, t) => m.handle_status_id.Equals(8)) // 图斑状态 查处后待整改
.Where(m => m.is_closed.Equals(0)) // 未关闭任务
.WhereIF(
!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("市级驳回") &&
req.nowStatus.Contains("县级驳回"),
(m, t) => m.is_tbsjbohui > 0 || m.is_tbxjbohui > 0 || m.is_zgsjbohui > 0 || m.is_zgxjbohui > 0)
.WhereIF(!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("市级驳回"),
(m, t) => m.is_zgsjbohui > 0)
.WhereIF(!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("县级驳回"),
(m, t) => m.is_zgxjbohui > 0)
.WhereIF(req.year != 0, m => m.syear_base.Equals(req.year.ToString()))
//.Where(m=>m.syear_base.Equals(year))
//.Where(m => SqlFunc.DateIsSame(Convert.ToDateTime(m.xiafatime_base), dateTime, DateType.Year))
.WhereIF(isCity.Equals(0), (m, t) =>
orgIds.Contains(m.countyid) || orgIds.Contains(m.streetid) || orgIds.Contains(m.communityid)) // 数据权限
.WhereIF(!string.IsNullOrEmpty(req.countyid), m => m.countyid.Equals(req.countyid)) // 县区
.WhereIF(!string.IsNullOrEmpty(req.streetid), m => m.streetid.Equals(req.streetid)) // 街镇
.WhereIF(!string.IsNullOrEmpty(req.caseNo), m => m.case_no.Contains(req.caseNo)) // 图斑号查询
.WhereIF(!string.IsNullOrEmpty(req.tubanlaiyuan), m => m.tubanlaiyuan.Equals(req.tubanlaiyuan)) // 图斑来源
.WhereIF(!string.IsNullOrEmpty(req.typename), m => m.typename.Equals(req.typename)) // 图斑类型 开采 加工
.WhereIF(req.type == 1, (m, t, f) => f.FavoriteUserId.Equals(user.Id.ToString()))
.WhereIF(!string.IsNullOrEmpty(req.startTime) && !string.IsNullOrEmpty(req.endTime), m =>
SqlFunc.Between(m.xiafatime_base, Convert.ToDateTime(req.startTime),
Convert.ToDateTime(req.endTime))) // 时间段查询
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("weifaarea_base"), m => SqlFunc.ToDouble(m.weifaarea_base),
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("gengdi_area"), m => SqlFunc.ToDouble(m.weifaarea_base),
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("synchronoustime"),
m => m.xiafatime_base,
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
//县区 街镇 图斑号 处理状态 图斑类型 总面积 违法面积 耕地面积
.Select<dynamic>((m, t, f) => new
{
Fid = f.Id,
id = m.Id,
processid = t.ProcessId,
processcode = t.ProcessCode, // 流程编码
taskid = t.Id,
caseno = m.case_no,
handlestatusid = m.handle_status_id, // 处理状态
unitname = t.UnitName,
tubanlaiyuan = m.tubanlaiyuan, // 图斑来源
typename = m.typename, // 图斑类型
geomid = m.geomid, // 图斑id
weifaarea = m.weifaarea_punish, // 违法面积
countyid = m.countyid, // 县区
countyname = m.countyname, // 县区
streetid = m.streetid, // 街镇
streetname = m.streetname, // 街镇
m.weifaarea_base, // 图斑面积
gengdi_area = m.gengdi_area, // 耕地面积
shijibohui_count = m.is_tbsjbohui + m.is_zgsjbohui,
is_shijibohui = m.is_zgsjbohui,
is_drawback = m.is_zgxjbohui,
illegalarea = m.typename.Equals("开采") ? m.weifakaicaiarea_base : m.weifazhandiarea_base // 违法面积
})
.ToPageListAsync(req.page, req.limit, total);
return new Response<PageInfo<List<dynamic>>>
{
Result = new PageInfo<List<dynamic>>
{
Total = total,
Items = result
}
};
}
/// <summary>
/// 整改审核
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public async Task<Response<PageInfo<List<dynamic>>>> LoadRectifyingAuditTaskList(MineralsTaskListReq req)
{
RefAsync<int> total = 0;
using var db = Repository.AsSugarClient();
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoMineralsSchemeCode").Value;
var authStrategyContext = _auth.GetCurrentUser();
if (authStrategyContext == null)
{
throw new Exception("用户未登录");
}
var user = authStrategyContext.User;
var orgList = authStrategyContext.Orgs;
var orgIds = orgList.Select(x => x.Id.ToString()).ToList();
var isCity = 0;
if (authStrategyContext.User.Id.Equals(-1))
{
isCity = 1;
}
var orgLevel = orgList.Min(a => a.Level); // 0 临沂市 1 县区 2-街镇
if (orgLevel.Equals(0))
{
isCity = 1;
}
//var dateTime = req.year == 0 ? DateTime.Now : new DateTime(req.year, 1, 1);
var year = req.year == 0 ? DateTime.Now.Year + "" : req.year + "";
var result = await db.Queryable<DroneCaseInfoMinerals>()
.LeftJoin<WFTask>((m, t) => m.Id.Equals(t.ProcessId))
.LeftJoin<DroneTaskFavorite>((m, t, f) => f.TaskId.Equals(t.Id) && f.FavoriteUserId == user.Id.ToString())
.Where((m, t) => t.State.Equals(1)) // 激活状态任务
.Where((m, t) => t.ProcessCode.Equals(schemeCode))
.Where(m => m.is_closed.Equals(0)) // 未关闭任务
.WhereIF(
!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("市级驳回") &&
req.nowStatus.Contains("县级驳回"),
(m, t) => m.is_tbsjbohui > 0 || m.is_tbxjbohui > 0 || m.is_zgsjbohui > 0 || m.is_zgxjbohui > 0)
.WhereIF(!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("市级驳回"),
(m, t) => m.is_zgsjbohui > 0)
.WhereIF(!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Contains("县级驳回"),
(m, t) => m.is_zgxjbohui > 0)
.WhereIF(orgLevel.Equals(0), m => m.handle_status_id.Equals(10))
.WhereIF(orgLevel != 0, m => m.handle_status_id.Equals(9))
.WhereIF(req.year != 0, m => m.syear_base.Equals(req.year.ToString()))
//.Where(m=>m.syear_base.Equals(year))
//.Where(m => SqlFunc.DateIsSame(Convert.ToDateTime(m.xiafatime_base), dateTime, DateType.Year))
.WhereIF(isCity.Equals(0), (m, t) =>
orgIds.Contains(m.countyid) || orgIds.Contains(m.streetid) || orgIds.Contains(m.communityid)) // 数据权限
.WhereIF(!string.IsNullOrEmpty(req.countyid), m => m.countyid.Equals(req.countyid)) // 县区
.WhereIF(!string.IsNullOrEmpty(req.streetid), m => m.streetid.Equals(req.streetid)) // 街镇
.WhereIF(!string.IsNullOrEmpty(req.caseNo), m => m.case_no.Contains(req.caseNo)) // 图斑号查询
.WhereIF(!string.IsNullOrEmpty(req.tubanlaiyuan), m => m.tubanlaiyuan.Equals(req.tubanlaiyuan)) // 图斑来源
.WhereIF(!string.IsNullOrEmpty(req.typename), m => m.typename.Equals(req.typename)) // 图斑类型 开采 加工
.WhereIF(req.type == 1, (m, t, f) => f.FavoriteUserId.Equals(user.Id.ToString()))
.WhereIF(!string.IsNullOrEmpty(req.startTime) && !string.IsNullOrEmpty(req.endTime), m =>
SqlFunc.Between(m.xiafatime_base, Convert.ToDateTime(req.startTime),
Convert.ToDateTime(req.endTime))) // 时间段查询
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("weifaarea_base"), m => SqlFunc.ToDouble(m.weifaarea_base),
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("gengdi_area"), m => SqlFunc.ToDouble(m.weifaarea_base),
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
.OrderByIF(
!string.IsNullOrEmpty(req.sortType) && !string.IsNullOrEmpty(req.order) &&
req.sortType.Equals("synchronoustime"),
m => m.xiafatime_base,
req.order != null && req.order.ToLower().Equals("desc") ? OrderByType.Desc : OrderByType.Asc)
//县区 街镇 图斑号 处理状态 图斑类型 总面积 违法面积 耕地面积
.Select<dynamic>((m, t, f) => new
{
Fid = f.Id,
id = m.Id,
processid = t.ProcessId,
processcode = t.ProcessCode, // 流程编码
taskid = t.Id,
caseno = m.case_no,
handlestatusid = m.handle_status_id, // 处理状态
unitname = t.UnitName,
tubanlaiyuan = m.tubanlaiyuan, // 图斑来源
typename = m.typename, // 图斑类型
geomid = m.geomid, // 图斑id
weifaarea = m.weifaarea_punish, // 违法面积
countyid = m.countyid, // 县区
countyname = m.countyname, // 县区
streetid = m.streetid, // 街镇
streetname = m.streetname, // 街镇
m.weifaarea_base, // 图斑面积
gengdi_area = m.gengdi_area, // 耕地面积
shijibohui_count = m.is_tbsjbohui + m.is_zgsjbohui,
is_shijibohui = m.is_zgsjbohui,
is_drawback = m.is_zgxjbohui,
illegalarea = m.typename.Equals("开采") ? m.weifakaicaiarea_base : m.weifazhandiarea_base // 违法面积
})
.ToPageListAsync(req.page, req.limit, total);
return new Response<PageInfo<List<dynamic>>>
{
Result = new PageInfo<List<dynamic>>
{
Total = total,
Items = result
}
};
}
#endregion
/// <summary>
/// 图斑列表
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<PageInfo<List<dynamic>>>> LoadCaseInfoMineralsTuBanList(CaseInfoMineralsTuBanReq req)
{
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
RefAsync<int> totalcount = 0;
string sql1 = "select min(\"Level\") from sys_userorg where \"UserId\"=" + user.Id;
int level = client.Ado.GetInt(sql1);
//该用户下包含所有部门
List<string> deplist = new List<string>();
string sqlpart = " ";
if (user != null && user.Id != -1 && level != 0)
{
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();
}
string orgs = "\'" + string.Join("\',\'", deplist) + "\'";
sqlpart = $" and (weizhixiannum_base in ({orgs}) or weizhizhennum_base in ({orgs})) ";
}
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoMineralsSchemeCode").Value;
string sqlcount =
$" select count(a.\"Id\")\r\n from drone_caseinfo_minerals a LEFT JOIN wf_process b on b.\"InstanceInfo\"->>'pkeyValue'=a.\"Id\" and b.\"SchemeCode\"='" +
schemeCode + "' where 1=1 and is_closed=0 and b.\"Id\" is not null";
string sql =
$"select a.\"Id\",a.syear_base,a.tubannum_base,a.laiyuan_base,a.weizhixianname_base,a.weizhizhenname_base,a.weizhicunname_base,a.type_base,a.kuangzhong_kcfill,a.shuoming_fill,a.zhongdianname_base,a.ziranbaohuname_base,a.weifaarea_base,a.gengdi_area,a.jbntarea_base,a.weifakaicaiarea_base,a.weifazhandiarea_base,a.jiagongzhuti_jgfill," +
$"a.kaicaiarea_kcfill,a.weifaarea_punish,b.\"ItemName\" as status_base,c.\"ItemName\" as hefapanding_fill,f.\"ItemName\" as hefapanding_jgfill,a.xiafatime_base,case when a.type_base = '加工' THEN a.jiagongzhuti_jgfill ELSE a.kaicaizhuti_kcfill END as kaicaizhuti_kcfill,d.\"ItemName\" as wefatype_kcfill,e.\"ItemName\" as weifatype_jgfill,a.zhenggaitype_jgzhg,a.chulishixian_base from drone_caseinfo_minerals a " +
$" LEFT JOIN wf_process s on s.\"InstanceInfo\"->>'pkeyValue'=a.\"Id\" and s.\"SchemeCode\"='" +
schemeCode + "' " +
$" left join sys_dataitemdetail b on CAST(a.status_base as VARCHAR)=b.\"ItemValue\" and b.\"ItemCode\"='kcdangqianzhuangtai' " +
$" left join sys_dataitemdetail c on CAST(a.hefapanding_fill as VARCHAR)=c.\"ItemValue\" and c.\"ItemCode\"='kcpandingjieguo' " +
$" left join sys_dataitemdetail d on CAST(a.wefatype_kcfill as VARCHAR)=d.\"ItemValue\" and d.\"ItemCode\"='kaicaiweifa' " +
$" left join sys_dataitemdetail f on CAST(a.hefapanding_jgfill as VARCHAR)=f.\"ItemValue\" and f.\"ItemCode\"='kcpandingjieguo' " +
$" left join sys_dataitemdetail e on CAST(a.weifatype_jgfill as VARCHAR)=e.\"ItemValue\" and e.\"ItemCode\"='jiagongweifa' " +
$" where 1=1 and is_closed=0 and s.\"Id\" is not null ";
if (!string.IsNullOrEmpty(req.syear_base))
{
sqlpart = sqlpart + $" and syear_base ='{req.syear_base}'";
}
if (!string.IsNullOrEmpty(req.startTime) && !string.IsNullOrEmpty(req.endTime))
{
DateTime datetime = DateTime.Parse(req.endTime);
DateTime datetime1 = datetime.AddDays(1);
sqlpart = sqlpart + $" and xiafatime_base between TO_DATE('" + req.startTime +
$"','YYYY-MM-DD') and TO_DATE('" + datetime1.ToString("yyyy-MM-dd") + $"','YYYY-MM-DD')";
}
if (!string.IsNullOrEmpty(req.type_base))
{
sqlpart = sqlpart + $" and type_base='" + req.type_base + "'";
}
if (!string.IsNullOrEmpty(req.tubanlaiyuan))
{
var tubanlaiyuanList = req.tubanlaiyuan.Split(",").ToList();
sqlpart = sqlpart + $" and laiyuan_base in ('{string.Join("','", tubanlaiyuanList)}')";
}
if (!string.IsNullOrEmpty(req.kuangzhong_kcfill))
{
sqlpart = sqlpart + $" and kuangzhong_kcfill='" + req.kuangzhong_kcfill + "'";
}
if (!string.IsNullOrEmpty(req.zhenggaitype_jgzhg))
{
sqlpart = sqlpart + $" and zhenggaitype_jgzhg='" + req.zhenggaitype_jgzhg + "'";
}
if (req.hefapanding_fill < 100)
{
sqlpart = sqlpart + $" and hefapanding_fill=" + req.hefapanding_fill + "";
}
if (!string.IsNullOrEmpty(req.countyid))
{
sqlpart = sqlpart + $" and weizhixiannum_base='" + req.countyid + "'";
}
if (!string.IsNullOrEmpty(req.streetid))
{
sqlpart = sqlpart + $" and weizhizhennum_base='" + req.streetid + "'";
}
if (!string.IsNullOrEmpty(req.status_base))
{
string[] str = req.status_base.Split(",");
string strstatus = "";
for (int i = 0; i < str.Length; i++)
{
strstatus = strstatus + str[i] + ",";
}
if (!string.IsNullOrEmpty(strstatus))
{
string statuslist = strstatus.Substring(0, strstatus.Length - 1);
sqlpart = sqlpart + $" and status_base in (" + statuslist + ")";
}
}
if (req.weifatype_jgfill < 100)
{
sqlpart = sqlpart + $" and weifatype_jgfill=" + req.weifatype_jgfill + "";
}
if (req.wefatype_kcfill < 100)
{
sqlpart = sqlpart + $" and wefatype_kcfill=" + req.wefatype_kcfill + "";
}
if (!string.IsNullOrEmpty(req.zhongdianflag_base))
{
sqlpart = sqlpart + $" and zhongdianflag_base='" + req.zhongdianflag_base + "'";
}
if (!string.IsNullOrEmpty(req.tubannum_base))
{
sqlpart = sqlpart + $" and tubannum_base like '%" + req.tubannum_base + "%'";
}
if (!string.IsNullOrEmpty(req.tubanArea1) && !string.IsNullOrEmpty(req.tubanArea2))
{
sqlpart = sqlpart + $" and CAST(COALESCE(NULLIF(weifaarea_base, '')::numeric, 0) AS numeric) between " +
double.Parse(req.tubanArea1) + " and " +
double.Parse(req.tubanArea2);
}
else if (!string.IsNullOrEmpty(req.tubanArea1) && string.IsNullOrEmpty(req.tubanArea2))
{
sqlpart = sqlpart + $" and CAST(COALESCE(NULLIF(weifaarea_base, '')::numeric, 0) AS numeric)> " +
double.Parse(req.tubanArea1);
}
else if (string.IsNullOrEmpty(req.tubanArea1) && !string.IsNullOrEmpty(req.tubanArea2))
{
sqlpart = sqlpart + $" and CAST(COALESCE(NULLIF(weifaarea_base, '')::numeric, 0) AS numeric)< " +
double.Parse(req.tubanArea2);
}
var count = await client.Ado.GetIntAsync(sqlcount + sqlpart);
sqlpart += $" ORDER BY xiafatime_base desc";
sqlpart += $" limit " + req.limit + " offset " + (req.page - 1) * req.limit;
Console.Write(sql + sqlpart);
var info = await client.SqlQueryable<dynamic>(sql + sqlpart).ToListAsync();
foreach (var item in info)
{
item.hefapanding_fill = "加工".Equals(item.type_base) ? item.hefapanding_jgfill : item.hefapanding_fill;
item.hefapanding_fill = "加工".Equals(item.type_base) ? item.hefapanding_jgfill : item.hefapanding_fill;
}
return new Response<PageInfo<List<dynamic>>>
{
Result = new PageInfo<List<dynamic>>
{
Items = info,
Total = count
}
};
}
/// <summary>
/// 图斑收藏
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public async Task<Response<bool>> AddTaskFavorite(DroneTaskFavorite model)
{
var count = await Repository.ChangeRepository<SugarRepositiry<DroneTaskFavorite>>().AsQueryable()
.Where(c => c.TaskId == model.TaskId && c.FavoriteUserId == model.FavoriteUserId).CountAsync();
if (count > 0)
{
return new Response<bool>
{
Code = 500,
Result = false,
Message = "案件已收藏"
};
}
var flag = await Repository.ChangeRepository<SugarRepositiry<DroneTaskFavorite>>().InsertAsync(model);
return new Response<bool>
{
Result = flag,
Message = flag ? "收藏成功" : "收藏失败"
};
}
/// <summary>
/// 取消收藏
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Response<bool>> CancelTaskFavorite(string id)
{
var flag = await Repository.ChangeRepository<SugarRepositiry<DroneTaskFavorite>>().DeleteByIdAsync(id);
return new Response<bool>
{
Result = flag,
Message = flag ? "删除成功" : "删除失败"
};
}
#region 统计台账
//开采台账
public async Task<Response<PageInfo<List<MineralsLedgerResp>>>> LoadMineralsLedger(QueryCaseOffencetReq req)
{
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
if (user == null)
{
return new Response<PageInfo<List<MineralsLedgerResp>>>
{
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();
}
}
string sqlpart = BuildSqlPart(req, level, deplist);
var resubmit = client.Queryable<ReSubmit>().Select(r => r.CaseNo).ToList();
string sql =
$"select syear_base,tubannum_base,miaoshu_base,original_case_no,TO_CHAR(rcvtime_base, 'YYYY-MM-DD HH24:MI:SS') AS rcvtime_base,case WHEN wefatype_kcfill = 1 THEN '无证开采类' WHEN wefatype_kcfill = 2 THEN '越界开采类' WHEN wefatype_kcfill = 3 THEN '持证矿山类' WHEN wefatype_kcfill = 4 THEN '以项目名义类' WHEN wefatype_kcfill = 5 THEN '其他' END as wefatype_kcfill, TO_CHAR(xiafatime_base, 'YYYY-MM-DD HH24:MI:SS') AS xiafatime_base,CASE WHEN caichujiazhi_kcfill > 10 THEN '是' ELSE '否' END AS isshexing,laiyuan_base,(COALESCE(weizhixiannum_base::text, '') || ' ' || COALESCE(weizhixianname_base, '')) as weizhixianname_base,(COALESCE(weizhizhennum_base::text, '') || ' ' || COALESCE(weizhizhenname_base, '')) as weizhizhenname_base,(COALESCE(weizhicunnum_base::text, '') || ' ' || COALESCE(weizhicunname_base, '')) as weizhicunname_base,kaicaizhuti_kcfill,case WHEN zhutitype_kcfill = 1 THEN '单位' WHEN zhutitype_kcfill = 2 THEN '企业' WHEN zhutitype_kcfill = 3 THEN '个人' END as zhutitype_kcfill,TO_CHAR(fashentime_kcfill, 'YYYY-MM-DD HH24:MI:SS') AS fashentime_kcfill,TO_CHAR(endtime_kcfill, 'YYYY-MM-DD HH24:MI:SS') AS endtime_kcfill," +
$"kuangzhong_kcfill,kaicaiarea_kcfill,fangliang_kcfill,chafengliang_kcfill,caichujiazhi_kcfill,CASE WHEN hefaqingxing_kcfill = '1' THEN '是' ELSE '否' END AS hefaqingxing_kcfill,ishedaocaisha_kcfill,CASE WHEN hefapanding_fill = '1' THEN '是' ELSE '否' END AS hefapanding_fill,CASE WHEN islian_punish = '1' THEN '是' ELSE '否' END AS islian_punish," +
$"isnewadd_kcfill,iszhongda_kcfill,iszhongdian_kcfill,tianbaoren_fill,xianshenheren_fill,TO_CHAR(chulishixian_base, 'YYYY-MM-DD HH24:MI:SS') AS chulishixian_base ,shuoming_fill from drone_caseinfo_minerals" +
$" a LEFT JOIN wf_process b on b.\"InstanceInfo\"->>'pkeyValue'=a.\"Id\" where 1=1 and is_closed =0 and b.\"Id\" is not null and type_base = '开采'";
var a = sql + sqlpart;
var caseList =
await client.SqlQueryable<MineralsLedgerResp>(sql + sqlpart)
.Where(r => !resubmit.Contains(r.original_case_no))
.ToPageListAsync(req.page, req.limit, totalcount);
return new Response<PageInfo<List<MineralsLedgerResp>>>
{
Result = new PageInfo<List<MineralsLedgerResp>>
{
Items = caseList,
Total = totalcount
}
};
}
public Response<MemoryStream> ListToExcelMineralsLedger(List<MineralsLedgerResp> 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;
}
public 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(0, 10 * 256);
sheet.SetColumnWidth(1, 18 * 256);
sheet.SetColumnWidth(2, 28 * 256);
sheet.SetColumnWidth(3, 28 * 256);
sheet.SetColumnWidth(4, 15 * 256);
sheet.SetColumnWidth(5, 30 * 256);
sheet.SetColumnWidth(6, 50 * 256);
sheet.SetColumnWidth(7, 50 * 256);
sheet.SetColumnWidth(8, 50 * 256);
sheet.SetColumnWidth(9, 30 * 256);
sheet.SetColumnWidth(10, 20 * 256);
sheet.SetColumnWidth(11, 28 * 256);
sheet.SetColumnWidth(12, 28 * 256);
sheet.SetColumnWidth(13, 20 * 256);
sheet.SetColumnWidth(14, 20 * 256);
sheet.SetColumnWidth(15, 20 * 256);
sheet.SetColumnWidth(16, 28 * 256);
sheet.SetColumnWidth(17, 20 * 256);
sheet.SetColumnWidth(18, 20 * 256);
sheet.SetColumnWidth(19, 12 * 256);
sheet.SetColumnWidth(20, 20 * 256);
sheet.SetColumnWidth(21, 20 * 256);
sheet.SetColumnWidth(22, 31 * 256);
sheet.SetColumnWidth(23, 10 * 256);
sheet.SetColumnWidth(24, 24 * 256);
sheet.SetColumnWidth(25, 20 * 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);
}
private void FillSheetWithData(HSSFWorkbook workbook, ISheet sheet, List<MineralsLedgerResp> 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++)
{
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(MineralsLedgerResp item, string key)
{
return key switch
{
"syear_base" => item.syear_base,
"tubannum_base" => item.tubannum_base,
"laiyuan_base" => item.laiyuan_base,
"weizhixianname_base" => item.weizhixianname_base,
"weizhizhenname_base" => item.weizhizhenname_base,
"weizhicunname_base" => item.weizhicunname_base,
"fashentime_kcfill" => item.fashentime_kcfill == null
? ""
: item.fashentime_kcfill.ToString("yyyy-MM-dd HH:mm:ss"),
"endtime_kcfill" => item.endtime_kcfill == null ? "" : item.endtime_kcfill.ToString("yyyy-MM-dd HH:mm:ss"),
"kaicaizhuti_kcfill" => item.kaicaizhuti_kcfill,
"zhutitype_kcfill" => item.zhutitype_kcfill,
"kuangzhong_kcfill" => item.kuangzhong_kcfill,
"kaicaiarea_kcfill" => item.kaicaiarea_kcfill,
"fangliang_kcfill" => item.fangliang_kcfill == null ? "" : item.fangliang_kcfill.ToString(),
"chafengliang_kcfill" => item.chafengliang_kcfill == null ? "" : item.chafengliang_kcfill.ToString(),
"isshexing" => item.isshexing,
"hefaqingxing_kcfill" => item.hefaqingxing_kcfill,
"ishedaocaisha_kcfill" => item.ishedaocaisha_kcfill,
"hefapanding_fill" => item.hefapanding_fill,
"islian_punish" => item.islian_punish,
"iszhongda_kcfill" => item.iszhongda_kcfill,
"isnewadd_kcfill" => item.isnewadd_kcfill,
"iszhongdian_kcfill" => item.iszhongdian_kcfill,
"tianbaoren_fill" => item.tianbaoren_fill,
"xianshenheren_fill" => item.xianshenheren_fill,
"chulishixian_base" => item.chulishixian_base,
"shuoming_fill" => item.shuoming_fill,
_ => string.Empty,
};
}
//加工台账
public async Task<Response<PageInfo<List<MineralsProcessResp>>>> LoadMineralsProcess(QueryCaseOffencetReq req)
{
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
if (user == null)
{
return new Response<PageInfo<List<MineralsProcessResp>>>
{
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();
}
}
string sqlpart = BuildSqlPart(req, level, deplist);
var resubmit = client.Queryable<ReSubmit>().Select(r => r.CaseNo).ToList();
string sql =
$"select syear_base,tubannum_base,type_base,original_case_no,miaoshu_base, TO_CHAR(xiafatime_base, 'YYYY-MM-DD HH24:MI:SS') AS xiafatime_base,TO_CHAR(rcvtime_base, 'YYYY-MM-DD HH24:MI:SS') AS rcvtime_base,laiyuan_base,weizhixiannum_base,(COALESCE(weizhixiannum_base::text, '') || ' ' || COALESCE(weizhixianname_base, '')) as weizhixianname_base,(COALESCE(weizhizhennum_base::text, '') || ' ' || COALESCE(weizhizhenname_base, '')) as weizhizhenname_base,(COALESCE(weizhicunnum_base::text, '') || ' ' || COALESCE(weizhicunname_base, '')) as weizhicunname_base,danweiname_jgzhg, case WHEN weifatype_jgfill = 1 THEN '违法占地' WHEN weifatype_jgfill = 2 THEN '购销非法开采矿产品' WHEN weifatype_jgfill = 3 THEN '其他' END as weifatype_jgfill,zhenggaitype_jgzhg,tianbaoren_fill," +
$"xianshenheren_fill,TO_CHAR(chulishixian_base, 'YYYY-MM-DD HH24:MI:SS') AS chulishixian_base,shuoming_fill " +
$" from drone_caseinfo_minerals a LEFT JOIN wf_process b on b.\"InstanceInfo\"->>'pkeyValue'=a.\"Id\" where 1=1 and is_closed =0 and b.\"Id\" is not null and type_base = '加工' ";
var a = sql + sqlpart;
var caseList =
await client.SqlQueryable<MineralsProcessResp>(sql + sqlpart)
.Where(r => !resubmit.Contains(r.original_case_no))
.ToPageListAsync(req.page, req.limit, totalcount);
return new Response<PageInfo<List<MineralsProcessResp>>>
{
Result = new PageInfo<List<MineralsProcessResp>>
{
Items = caseList,
Total = totalcount
}
};
}
public Response<MemoryStream> ListToExcelMineralsProcess(List<MineralsProcessResp> 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);
CreateHeaderRowProcess(sheet, headers, headerStyle);
var rowsToWrite = list.Skip(k * 60000).Take(60000).ToList();
FillSheetWithDataProcess(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 void CreateHeaderRowProcess(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, 28 * 256);
sheet.SetColumnWidth(3, 28 * 256);
sheet.SetColumnWidth(4, 15 * 256);
sheet.SetColumnWidth(5, 30 * 256);
sheet.SetColumnWidth(6, 50 * 256);
sheet.SetColumnWidth(7, 50 * 256);
sheet.SetColumnWidth(8, 50 * 256);
sheet.SetColumnWidth(9, 30 * 256);
sheet.SetColumnWidth(10, 24 * 256);
sheet.SetColumnWidth(11, 24 * 256);
sheet.SetColumnWidth(12, 24 * 256);
sheet.SetColumnWidth(13, 24 * 256);
sheet.SetColumnWidth(14, 24 * 256);
sheet.SetColumnWidth(15, 24 * 256);
}
private void FillSheetWithDataProcess(HSSFWorkbook workbook, ISheet sheet, List<MineralsProcessResp> 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++)
{
var cell = dataRow.CreateCell(j);
cell.CellStyle = contentStyle;
if (headers[j].key == "xuhao")
{
cell.SetCellValue(i + 1);
}
else
{
var objValue = GetCellValueProcess(item, headers[j].key);
cell.SetCellValue(objValue);
}
}
}
}
private string GetCellValueProcess(MineralsProcessResp item, string key)
{
return key switch
{
"syear_base" => item.syear_base,
"tubannum_base" => item.tubannum_base,
"laiyuan_base" => item.laiyuan_base,
"weizhixianname_base" => item.weizhixianname_base,
"weizhizhenname_base" => item.weizhizhenname_base,
"weizhicunname_base" => item.weizhicunname_base,
"danweiname_jgzhg" => item.danweiname_jgzhg,
"weifatype_jgfill" => item.weifatype_jgfill,
"zhenggaitype_jgzhg" => item.zhenggaitype_jgzhg,
"tianbaoren_fill" => item.tianbaoren_fill,
"xianshenheren_fill" => item.xianshenheren_fill,
"chulishixian_base" => item.chulishixian_base,
"shuoming_fill" => item.shuoming_fill,
_ => string.Empty,
};
}
private string BuildSqlPart(QueryCaseOffencetReq req, int level, List<string> deplist)
{
string result = string.Empty;
if (!string.IsNullOrEmpty(req.AreaId) && req.AreaId != "0")
{
if (level == 0)
{
result += $" AND weizhixiannum_base = " + req.AreaId;
}
if (level == 1)
{
result += $" AND weizhizhennum_base =" + req.AreaId;
}
}
else
{
if (deplist.Any())
{
string orgs = string.Join(",", deplist.Select(dep => $"'{dep}'"));
return
$" AND (weizhixiannum_base IN ({orgs}) OR weizhizhennum_base IN ({orgs}) OR weizhicunnum_base IN ({orgs}))";
}
}
if (req.StartTime.Year != 0001)
{
result += $" AND xiafatime_base >= '" + req.StartTime + "' and xiafatime_base <= '" + req.EndTime + "'";
}
return result;
}
#endregion
public Response<List<CaseInfoMineralTubanExport>> LoadCaseInfoMineralsExportTuBanList(CaseInfoMineralsTuBanReq req)
{
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
RefAsync<int> totalcount = 0;
string sql1 = "select min(\"Level\") from sys_userorg where \"UserId\"=" + user.Id;
int level = client.Ado.GetInt(sql1);
//该用户下包含所有部门
List<string> deplist = new List<string>();
string sqlpart = " ";
if (user != null && user.Id != -1 && level != 0)
{
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();
}
string orgs = "\'" + string.Join("\',\'", deplist) + "\'";
sqlpart = $" and (weizhixiannum_base in ({orgs}) or weizhizhennum_base in ({orgs})) ";
}
string sql =
$"select a.\"Id\",a.syear_base,a.tubannum_base,a.laiyuan_base,a.weizhixianname_base,a.weizhizhenname_base,a.weizhicunname_base,a.type_base,a.kuangzhong_kcfill,a.shuoming_fill,a.zhongdianname_base,a.ziranbaohuname_base,a.weifaarea_base,a.gengdi_area,a.jbntarea_base,a.gid geomid,a.jiagongzhuti_jgfill," +
$"a.kaicaiarea_kcfill,a.weifaarea_punish,b.\"ItemName\" as status_base,c.\"ItemName\" as hefapanding_fill,f.\"ItemName\" as hefapanding_jgfill,a.xiafatime_base,a.kaicaizhuti_kcfill,d.\"ItemName\" as wefatype_kcfill,e.\"ItemName\" as weifatype_jgfill,a.zhenggaitype_jgzhg,a.chulishixian_base from drone_caseinfo_minerals a " +
$" left join sys_dataitemdetail b on CAST(a.status_base as VARCHAR)=b.\"ItemValue\" and b.\"ItemCode\"='kcdangqianzhuangtai' " +
$" left join sys_dataitemdetail c on CAST(a.hefapanding_fill as VARCHAR)=c.\"ItemValue\" and c.\"ItemCode\"='kcpandingjieguo' " +
$" left join sys_dataitemdetail d on CAST(a.wefatype_kcfill as VARCHAR)=d.\"ItemValue\" and d.\"ItemCode\"='kaicaiweifa' " +
$" left join sys_dataitemdetail f on CAST(a.hefapanding_jgfill as VARCHAR)=f.\"ItemValue\" and d.\"ItemCode\"='kcpandingjieguo' " +
$" left join sys_dataitemdetail e on CAST(a.weifatype_jgfill as VARCHAR)=e.\"ItemValue\" and e.\"ItemCode\"='jiagongweifa' " +
$" LEFT JOIN wf_process s on s.\"InstanceInfo\"->>'pkeyValue'=a.\"Id\" where 1=1 and is_closed=0 and s.\"Id\" is not null ";
if (!string.IsNullOrEmpty(req.syear_base))
{
sqlpart = sqlpart + $" and syear_base ='{req.syear_base}'";
}
if (!string.IsNullOrEmpty(req.startTime) && !string.IsNullOrEmpty(req.endTime))
{
DateTime datetime = DateTime.Parse(req.endTime);
DateTime datetime1 = datetime.AddDays(1);
sqlpart = sqlpart + $" and xiafatime_base between TO_DATE('" + req.startTime +
$"','YYYY-MM-DD') and TO_DATE('" + datetime1.ToString("yyyy-MM-dd") + $"','YYYY-MM-DD')";
}
if (!string.IsNullOrEmpty(req.type_base))
{
sqlpart = sqlpart + $" and type_base='" + req.type_base + "'";
}
if (!string.IsNullOrEmpty(req.tubanlaiyuan))
{
var tubanlaiyuanList = req.tubanlaiyuan.Split(",").ToList();
sqlpart = sqlpart + $" and laiyuan_base in ('{string.Join("','", tubanlaiyuanList)}')";
}
if (!string.IsNullOrEmpty(req.zhenggaitype_jgzhg))
{
sqlpart = sqlpart + $" and zhenggaitype_jgzhg='" + req.zhenggaitype_jgzhg + "'";
}
if (!string.IsNullOrEmpty(req.kuangzhong_kcfill))
{
sqlpart = sqlpart + $" and kuangzhong_kcfill='" + req.kuangzhong_kcfill + "'";
}
if (req.hefapanding_fill < 100)
{
sqlpart = sqlpart + $" and hefapanding_fill=" + req.hefapanding_fill + "";
}
if (!string.IsNullOrEmpty(req.countyid))
{
sqlpart = sqlpart + $" and weizhixiannum_base='" + req.countyid + "'";
}
if (!string.IsNullOrEmpty(req.streetid))
{
sqlpart = sqlpart + $" and weizhizhennum_base='" + req.streetid + "'";
}
if (!string.IsNullOrEmpty(req.status_base))
{
string[] str = req.status_base.Split(",");
string strstatus = "";
for (int i = 0; i < str.Length; i++)
{
strstatus = strstatus + str[i] + ",";
}
if (!string.IsNullOrEmpty(strstatus))
{
string statuslist = strstatus.Substring(0, strstatus.Length - 1);
sqlpart = sqlpart + $" and status_base in (" + statuslist + ")";
}
}
if (req.weifatype_jgfill < 100)
{
sqlpart = sqlpart + $" and weifatype_jgfill=" + req.weifatype_jgfill + "";
}
if (req.wefatype_kcfill < 100)
{
sqlpart = sqlpart + $" and wefatype_kcfill=" + req.wefatype_kcfill + "";
}
if (!string.IsNullOrEmpty(req.zhongdianflag_base))
{
sqlpart = sqlpart + $" and zhongdianflag_base='" + req.zhongdianflag_base + "'";
}
if (!string.IsNullOrEmpty(req.tubannum_base))
{
sqlpart = sqlpart + $" and tubannum_base like '%" + req.tubannum_base + "%'";
}
if (!string.IsNullOrEmpty(req.tubanArea1) && !string.IsNullOrEmpty(req.tubanArea2))
{
sqlpart = sqlpart + $" and CAST(COALESCE(NULLIF(weifaarea_base, '')::numeric, 0) AS numeric) between " +
double.Parse(req.tubanArea1) + " and " +
double.Parse(req.tubanArea2);
}
else if (!string.IsNullOrEmpty(req.tubanArea1) && string.IsNullOrEmpty(req.tubanArea2))
{
sqlpart = sqlpart + $" and CAST(COALESCE(NULLIF(weifaarea_base, '')::numeric, 0) AS numeric)> " +
double.Parse(req.tubanArea1);
}
else if (string.IsNullOrEmpty(req.tubanArea1) && !string.IsNullOrEmpty(req.tubanArea2))
{
sqlpart = sqlpart + $" and CAST(COALESCE(NULLIF(weifaarea_base, '')::numeric, 0) AS numeric)< " +
double.Parse(req.tubanArea2);
}
sqlpart += $" ORDER BY xiafatime_base desc";
var info = client.Ado.SqlQuery<CaseInfoMineralTubanExport>(sql + sqlpart);
Response<List<CaseInfoMineralTubanExport>> response = new Response<List<CaseInfoMineralTubanExport>>();
response.Result = info;
return response;
}
public Response<MemoryStream> ListToExcelTuban(List<CaseInfoMineralTubanExport> 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); //将字体样式赋给样式对象
// 创建一个数值格式的CellStyle
ICellStyle numericStyle = workbook.CreateCellStyle();
// 创建一个数据格式对象
IDataFormat dataFormat = workbook.CreateDataFormat();
var font11 = 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(font11);
#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);
if ("xuhao".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 4 * 350);
}
else if ("syear_base".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 300);
}
else if ("tubannum_base".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 300);
}
else if ("weizhixianname_base".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 25 * 300);
}
else if ("weizhizhenname_base".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 15 * 300);
}
else if ("weizhicunname_base".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 300);
}
else if ("type_base".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 15 * 320);
}
else if ("kuangzhong_kcfill".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 350);
}
else if ("shuoming_fill".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 320);
}
else if ("zhongdianname_base".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 15 * 300);
}
else if ("ziranbaohuname_base".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 15 * 300);
}
else if ("weifaarea_base".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 15 * 300);
}
else if ("jbntarea_base".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 15 * 300);
}
else if ("kaicaiarea_kcfill".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("weifaarea_punish".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("status_base".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("hefapanding_fill".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 320);
}
else if ("xiafatime_base".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("kaicaizhuti_kcfill".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("wefatype_kcfill".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("weifatype_jgfill".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 15 * 300);
}
else if ("zhenggaitype_jgzhg".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 15 * 300);
}
else if ("chulishixian_base".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
}
#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++) //循环数据
{
var item = list[k * 60000 + i]; //获取数据
IRow dataRow = sheet.CreateRow(i + 1); //创建行
string qitauseto = "";
for (int j = 0; j < headers.Count; j++) //循环表头
{
var objValue = "";
if ("xuhao".Equals(headers[j].key))
{
objValue = i + 1 + "";
}
if ("syear_base".Equals(headers[j].key))
{
objValue = item.syear_base;
}
else if ("tubannum_base".Equals(headers[j].key))
{
objValue = item.tubannum_base;
}
else if ("weizhixianname_base".Equals(headers[j].key))
{
objValue = item.weizhixianname_base;
}
else if ("weizhizhenname_base".Equals(headers[j].key))
{
objValue = item.weizhizhenname_base;
}
else if ("weizhicunname_base".Equals(headers[j].key))
{
objValue = item.weizhicunname_base;
}
else if ("type_base".Equals(headers[j].key))
{
objValue = item.type_base;
}
else if ("weifaarea_base".Equals(headers[j].key))
{
if (string.IsNullOrEmpty(item.weifaarea_base))
{
objValue = item.weifaarea_base;
}
else
{
objValue = double.Parse(item.weifaarea_base).ToString("F2");
}
//创建单元格
var cell = dataRow.CreateCell(j);
cell.CellStyle = numericStyle;
if (objValue != null && !string.IsNullOrEmpty(objValue.ToString()))
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(double.Parse(objValue));
}
else
{
cell.SetCellValue(0.00d); //填充Excel单元格
}
continue;
}
else if ("jbntarea_base".Equals(headers[j].key))
{
if (string.IsNullOrEmpty(item.jbntarea_base))
{
objValue = item.jbntarea_base;
}
else
{
objValue = double.Parse(item.jbntarea_base).ToString("F2");
}
var cell = dataRow.CreateCell(j);
cell.CellStyle = numericStyle;
if (objValue != null && !string.IsNullOrEmpty(objValue.ToString()))
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(double.Parse(objValue));
}
else
{
cell.SetCellValue(0.00d); //填充Excel单元格
}
continue;
}
else if ("kaicaiarea_kcfill".Equals(headers[j].key))
{
if (string.IsNullOrEmpty(item.kaicaiarea_kcfill))
{
objValue = item.kaicaiarea_kcfill;
}
else
{
objValue = double.Parse(item.kaicaiarea_kcfill).ToString("F2");
}
var cell = dataRow.CreateCell(j);
cell.CellStyle = numericStyle;
if (objValue != null && !string.IsNullOrEmpty(objValue.ToString()))
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(double.Parse(objValue));
}
else
{
cell.SetCellValue(0.00d); //填充Excel单元格
}
continue;
}
else if ("weifaarea_punish".Equals(headers[j].key))
{
if (string.IsNullOrEmpty(item.weifaarea_punish))
{
objValue = item.weifaarea_punish;
}
else
{
objValue = double.Parse(item.weifaarea_punish).ToString("F2");
}
var cell = dataRow.CreateCell(j);
cell.CellStyle = numericStyle;
if (objValue != null && !string.IsNullOrEmpty(objValue.ToString()))
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(double.Parse(objValue));
}
else
{
cell.SetCellValue(0.00d); //填充Excel单元格
}
continue;
}
else if ("kuangzhong_kcfill".Equals(headers[j].key))
{
objValue = item.kuangzhong_kcfill;
}
else if ("shuoming_fill".Equals(headers[j].key))
{
objValue = item.shuoming_fill;
}
else if ("xiafatime_base".Equals(headers[j].key))
{
if (string.IsNullOrEmpty(item.xiafatime_base))
{
objValue = item.xiafatime_base;
}
else
{
DateTime dateValue2;
DateTime.TryParse(item.xiafatime_base, out dateValue2);
objValue = dateValue2.ToString("yyyy-MM-dd HH:mm:ss");
}
}
else if ("zhongdianname_base".Equals(headers[j].key))
{
objValue = item.zhongdianname_base;
}
else if ("ziranbaohuname_base".Equals(headers[j].key))
{
objValue = item.ziranbaohuname_base;
}
else if ("status_base".Equals(headers[j].key))
{
objValue = item.status_base;
}
else if ("hefapanding_fill".Equals(headers[j].key))
{
if ("加工".Equals(item.type_base))
{
objValue = item.hefapanding_jgfill;
}
else
{
objValue = item.hefapanding_fill;
}
}
else if ("kaicaizhuti_kcfill".Equals(headers[j].key))
{
if ("加工".Equal(item.type_base))
{
objValue = item.jiagongzhuti_jgfill;
}
else
{
objValue = item.kaicaizhuti_kcfill;
}
}
else if ("wefatype_kcfill".Equals(headers[j].key))
{
objValue = item.wefatype_kcfill;
}
else if ("weifatype_jgfill".Equals(headers[j].key))
{
objValue = item.weifatype_jgfill;
}
else if ("zhenggaitype_jgzhg".Equals(headers[j].key))
{
objValue = item.zhenggaitype_jgzhg;
}
else if ("chulishixian_base".Equals(headers[j].key))
{
if (item.chulishixian_base.IsNullOrEmpty() ||
"0001-01-01 00:00:00".Equals(item.chulishixian_base.ToString("yyyy-MM-dd HH:mm:ss")))
{
objValue = "";
}
else
{
objValue = item.chulishixian_base.ToString("yyyy-MM-dd HH:mm:ss");
}
}
//创建单元格
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;
}
public void ExportIllegalCaiKuangShapefile(CaseInfoMineralsTuBanReq req, string shpFilePath, string shpFilePathzip)
{
var response = LoadCaseInfoMineralsExportTuBanList(req).Result;
List<CaseInfoMineralTubanExport> list = new List<CaseInfoMineralTubanExport>();
for (int i = 0; i < response.Count; i++)
{
var caseinfo = response[i];
if (!string.IsNullOrEmpty(caseinfo.geomid))
{
list.Add(caseinfo);
}
}
List<int> gids = list.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 a.gid, ST_AsText(a.geom) AS geom,b.tubanlaiyuan,b.case_no,b.countyname,b.streetname,b.communityname FROM drone_shp_data a left join drone_caseinfo_single b on a.relid=b.\"Id\" WHERE gid IN ({gidsString})";
string query =
$"SELECT gid,case_no,typename,is_illegal_name,ST_AsText(geom) AS geom FROM view_drone_caseinfo_minerals WHERE gid IN ({gidsString})";
// 执行查询
var data = client.Ado.SqlQuery<dynamic>(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 },
// {"laiyuan",row.tubanlaiyuan==null?"无":row.tubanlaiyuan },
// {"case_no",row.case_no==null?"无":row.case_no },
// {"countyname",row.countyname==null?"无":row.countyname },
// {"streetname",row.streetname ==null?"无":row.streetname},
// {"comname",row.communityname ==null?"无":row.communityname}
//};
var attributes = new AttributesTable
{
{ "gid", row.gid },
{ "图斑编号", row.case_no == null ? "" : row.case_no },
{ "图斑类型", row.typename == null ? "" : row.typename },
{ "判定结果", row.is_illegal_name == null ? "" : row.is_illegal_name },
};
IFeature feature = new Feature(geometry, attributes);
features.Add(feature);
}
if (features.Count == 0)
{
throw new Exception("数据不可用");
}
// 导出 SHP 文件及其关联文件
//ExportToShapefileFour(shpFilePath, features);
ExportToShapefileFour(shpFilePath, features);
// 将文件打包成 ZIP
CreateZipFromShapefiles(shpFilePath, shpFilePathzip);
}
public async Task<Response<Dictionary<string, object>>> LoadMyUncompletedKuangChanTask(MineralsTaskListReq req)
{
//获取当前用户
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
RefAsync<int> totalcount = 0;
string sql1 = "select min(\"Level\") from sys_userorg where \"UserId\"=" + user.Id;
int level = client.Ado.GetInt(sql1);
//该用户下包含所有部门
List<string> deplist = new List<string>();
string sqlpart = " ";
if (user != null && user.Id != -1 && level != 0)
{
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();
}
string orgs = "\'" + string.Join("\',\'", deplist) + "\'";
sqlpart =
$" and (c.weizhixiannum_base in ({orgs}) or c.weizhizhennum_base in ({orgs}) or c.weizhicunname_base in ({orgs})) ";
}
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoMineralsSchemeCode").Value;
string sql = "";
string sqlcount = "";
int allCount = 0;
if (req.type == 0)
{
sqlcount =
$"select count(a.*) from wf_task a LEFT JOIN wf_process " +
$"b on a.\"ProcessId\"=b.\"Id\" \r\n RIGHT JOIN drone_caseinfo_minerals c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
schemeCode + "' ";
sql =
$"select a.\"Id\" as taskId,a.\"UnitName\" as unitName,c.type_base as typename,b.\"Title\" as title,c.xiafatime_base as createDate,c.tubannum_base as caseNo,c.weizhixianname_base countyname,c.weizhizhenname_base streetname,c.weizhicunname_base communityname,\r\nc.weifaarea_base area,a.\"ProcessCode\" as processcode,a.\"ProcessId\" as processid," +
$"c.jbntarea_base as gengdiArea,weifaarea_punish as illeagalArea,c.gid geomid,c.\"Id\" as caseid \r\n from wf_task a LEFT JOIN wf_process " +
$"b on a.\"ProcessId\"=b.\"Id\" \r\n RIGHT JOIN drone_caseinfo_minerals c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
schemeCode + "' ";
sqlpart = sqlpart + $" and a.\"UnitName\" in ('待填报','待整改','待查处','查处后待整改') and a.\"State\"=1 ";
allCount = await client.Ado.GetIntAsync(sqlcount + sqlpart);
}
if (req.type == 1)
{
sqlcount =
$"select count(a.*) from wf_task a " +
$" RIGHT JOIN ( select \"ProcessId\",max(\"CreateDate\") as createtime from\r\n\twf_task where \"UserId\"='" +
user.Id +
$"' and \"State\"=3 GROUP BY \"ProcessId\" ) t on a.\"CreateDate\"=t.createtime and a.\"UserId\"='" +
user.Id + $"' and a.\"State\"=3 " +
$" RIGHT JOIN wf_process " +
$"b on a.\"ProcessId\"=b.\"Id\" \r\n RIGHT JOIN drone_caseinfo_minerals c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
schemeCode + "' ";
sql =
$"select DISTINCT(a.\"ProcessId\") as proid, a.\"Id\" as taskId,concat('已',SUBSTRING(a.\"UnitName\" from 2 for 2)) as unitName,c.type_base as typename,b.\"Title\" as title,c.xiafatime_base as createDate,c.tubannum_base as caseNo,c.weizhixianname_base countyname,c.weizhizhenname_base streetname,c.weizhicunname_base communityname,\r\nc.weifaarea_base area,a.\"ProcessCode\" as processcode,a.\"ProcessId\" as processid," +
$"c.jbntarea_base as gengdiArea,weifaarea_punish as illeagalArea ,c.gid geomid,c.\"Id\" as caseid \r\n from wf_task a " +
$" RIGHT JOIN (select \"ProcessId\",max(\"CreateDate\") as createtime from\r\n\twf_task where \"UserId\"='" +
user.Id +
$"' and \"State\"=3 GROUP BY \"ProcessId\" ) t on a.\"CreateDate\"=t.createtime and a.\"UserId\"='" +
user.Id + $"' and a.\"State\"=3 " +
$" RIGHT JOIN wf_process " +
$"b on a.\"ProcessId\"=b.\"Id\" \r\n RIGHT JOIN drone_caseinfo_minerals c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
schemeCode + "' ";
sqlpart = sqlpart + $" and a.\"UserId\"='" + user.Id + "' and a.\"State\"=3 ";
allCount = await client.Ado.GetIntAsync(sqlcount + sqlpart);
}
if (req.year != 0)
{
sqlpart = sqlpart + $" and EXTRACT(YEAR FROM xiafatime_base) = {req.year}";
}
if (!string.IsNullOrEmpty(req.startTime) && !string.IsNullOrEmpty(req.endTime))
{
DateTime datetime = DateTime.Parse(req.endTime);
DateTime datetime1 = datetime.AddDays(1);
sqlpart = sqlpart + $" and TO_DATE(xiafatime_base, 'YYYY-MM-DD') between TO_DATE('" + req.startTime +
$"','YYYY-MM-DD') and TO_DATE('" + datetime1.ToString("yyyy-MM-dd") + $"','YYYY-MM-DD')";
}
if (!string.IsNullOrEmpty(req.caseNo))
{
sqlpart = sqlpart + $" and c.tubannum_base like '%" + req.caseNo + "%'";
}
if (!string.IsNullOrEmpty(req.typename))
{
sqlpart = sqlpart + $" and c.type_base='" + req.typename + "'";
}
if (!string.IsNullOrEmpty(req.nowStatus))
{
sqlpart = sqlpart + $" and c.status_base='" + req.nowStatus + "'";
}
if (!string.IsNullOrEmpty(req.tubanlaiyuan))
{
sqlpart = sqlpart + $" and laiyuan_base='" + req.tubanlaiyuan + "'";
}
if (!string.IsNullOrEmpty(req.countyid))
{
sqlpart = sqlpart + $" and weizhixiannum_base='" + req.countyid + "'";
}
if (!string.IsNullOrEmpty(req.streetid))
{
sqlpart = sqlpart + $" and weizhizhennum_base='" + req.streetid + "'";
}
var info = await client.SqlQueryable<dynamic>(sql + sqlpart).ToPageListAsync(req.page, req.limit, totalcount);
Dictionary<string, object> dic = new Dictionary<string, object>();
dic.Add("allcount", allCount);
PageInfo<List<dynamic>> list1 = new PageInfo<List<dynamic>>
{
Items = info,
Total = totalcount,
};
dic.Add("list", list1);
return new Response<Dictionary<string, object>>
{
Result = dic
};
}
public async Task<Response<Dictionary<string, object>>> LoadAllKuangChanTask(MineralsTaskListReq req)
{
//获取当前用户
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
RefAsync<int> totalcount = 0;
string sql1 = "select min(\"Level\") from sys_userorg where \"UserId\"=" + user.Id;
int level = client.Ado.GetInt(sql1);
//该用户下包含所有部门
List<string> deplist = new List<string>();
string sqlpart = " ";
if (user != null && user.Id != -1 && level != 0)
{
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();
}
string orgs = "\'" + string.Join("\',\'", deplist) + "\'";
sqlpart =
$" and (c.weizhixiannum_base in ({orgs}) or c.weizhizhennum_base in ({orgs}) or c.weizhicunname_base in ({orgs})) ";
}
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoMineralsSchemeCode").Value;
string sql = "";
string sqlcount = "";
int allCount = 0;
sqlcount =
$"select count(a.*) from drone_caseinfo_minerals a LEFT JOIN wf_process " +
$"b on b.\"InstanceInfo\"->>'pkeyValue'=a.\"Id\" where 1=1 and is_closed=0 and b.\"Id\" is not null and b.\"SchemeCode\"='" +
schemeCode + "' ";
sql =
$"select c.type_base as typename,c.laiyuan_base as tubanlaiyuan,b.\"Title\" as title,c.xiafatime_base as identificationTime,c.tubannum_base as caseNo,c.weizhixianname_base countyname,c.weizhizhenname_base streetname,c.weizhicunname_base communityname,\r\nc.weifaarea_base area," +
$"c.jbntarea_base as gengdiArea,weifaarea_punish as illeagalArea,c.gid geomid,c.\"Id\" \r\n from wf_process b " +
$" RIGHT JOIN drone_caseinfo_minerals c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where 1=1 and is_closed=0 and b.\"Id\" is not null and b.\"SchemeCode\"='" +
schemeCode + "' ";
allCount = await client.Ado.GetIntAsync(sqlcount + sqlpart);
if (req.year != 0)
{
sqlpart = sqlpart + $" and EXTRACT(YEAR FROM xiafatime_base) = {req.year}";
}
if (!string.IsNullOrEmpty(req.startTime) && !string.IsNullOrEmpty(req.endTime))
{
DateTime datetime = DateTime.Parse(req.endTime);
DateTime datetime1 = datetime.AddDays(1);
sqlpart = sqlpart + $" and TO_DATE(xiafatime_base, 'YYYY-MM-DD') between TO_DATE('" + req.startTime +
$"','YYYY-MM-DD') and TO_DATE('" + datetime1.ToString("yyyy-MM-dd") + $"','YYYY-MM-DD')";
}
if (!string.IsNullOrEmpty(req.caseNo))
{
sqlpart = sqlpart + $" and c.tubannum_base like '%" + req.caseNo + "%'";
}
if (!string.IsNullOrEmpty(req.typename))
{
sqlpart = sqlpart + $" and c.type_base='" + req.typename + "'";
}
if (!string.IsNullOrEmpty(req.nowStatus))
{
sqlpart = sqlpart + $" and c.status_base='" + req.nowStatus + "'";
}
if (!string.IsNullOrEmpty(req.tubanlaiyuan))
{
sqlpart = sqlpart + $" and laiyuan_base='" + req.tubanlaiyuan + "'";
}
if (!string.IsNullOrEmpty(req.countyid))
{
sqlpart = sqlpart + $" and weizhixiannum_base='" + req.countyid + "'";
}
if (!string.IsNullOrEmpty(req.streetid))
{
sqlpart = sqlpart + $" and weizhizhennum_base='" + req.streetid + "'";
}
var info = await client.SqlQueryable<dynamic>(sql + sqlpart).ToPageListAsync(req.page, req.limit, totalcount);
Dictionary<string, object> dic = new Dictionary<string, object>();
dic.Add("allcount", allCount);
PageInfo<List<dynamic>> list1 = new PageInfo<List<dynamic>>
{
Items = info,
Total = totalcount,
};
dic.Add("list", list1);
return new Response<Dictionary<string, object>>
{
Result = dic
};
}
public async Task<Response<PageInfo<List<dynamic>>>> LoadTaskList(TaskListReq req)
{
//获取当前用户
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
RefAsync<int> totalcount = 0;
string sql1 = "select min(\"Level\") from sys_userorg where \"UserId\"=" + user.Id;
int level = client.Ado.GetInt(sql1);
//该用户下包含所有部门
List<string> deplist = new List<string>();
string sqlpart = " ";
if (user != null && user.Id != -1 && level != 0)
{
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();
}
string orgs = "\'" + string.Join("\',\'", deplist) + "\'";
sqlpart =
$" and (c.weizhixiannum_base in ({orgs}) or c.weizhizhennum_base in ({orgs}) or c.weizhicunname_base in ({orgs})) ";
}
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoMineralsSchemeCode").Value;
string sql = "";
if (req.type == 0)
{
sql =
$"select t.\"Id\" as Fid,a.\"Id\" as taskId,a.\"UnitName\" as unitName,c.type_base as typename,b.\"Title\" as title,c.xiafatime_base as createDate,c.tubannum_base as caseNo,c.weizhixianname_base countyname,c.weizhizhenname_base streetname,c.weizhicunname_base communityname,\r\nc.weifaarea_base area,a.\"ProcessCode\" as processcode,a.\"ProcessId\" as processid," +
$"c.jbntarea_base,gengdi_area,weifaarea_punish as illeagalArea,c.gid geomid,c.\"Id\" as caseid \r\n from wf_task a LEFT JOIN drone_task_favorite t on a.\"Id\"=t.\"TaskId\" and t.\"FavoriteUserId\"='" +
user.Id + "' LEFT JOIN wf_process " +
$"b on a.\"ProcessId\"=b.\"Id\" \r\n RIGHT JOIN drone_caseinfo_minerals c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
schemeCode + "' ";
}
else
{
sql =
$"select t.\"Id\" as Fid,a.\"Id\" as taskId,a.\"UnitName\" as unitName,c.type_base as typename,b.\"Title\" as title,c.xiafatime_base as createDate,c.tubannum_base as caseNo,c.weizhixianname_base countyname,c.weizhizhenname_base streetname,c.weizhicunname_base communityname,\r\nc.weifaarea_base area,a.\"ProcessCode\" as processcode,a.\"ProcessId\" as processid," +
$"c.jbntarea_base,gengdi_area,weifaarea_punish as illeagalArea,c.gid geomid,c.\"Id\" as caseid \r\n from wf_task a RIGHT JOIN drone_task_favorite t on a.\"Id\"=t.\"TaskId\" and t.\"FavoriteUserId\"='" +
user.Id + "' LEFT JOIN wf_process " +
$"b on a.\"ProcessId\"=b.\"Id\" \r\n RIGHT JOIN drone_caseinfo_minerals c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
schemeCode + "' ";
}
sqlpart = sqlpart + $" and a.\"UnitName\" in ('待填报','待整改','待查处','查处后待整改') and a.\"State\"=1 ";
if (req.year != 0)
{
sqlpart = sqlpart + $" and EXTRACT(YEAR FROM xiafatime_base) = {req.year}";
}
if (!string.IsNullOrEmpty(req.startTime) && !string.IsNullOrEmpty(req.endTime))
{
DateTime datetime = DateTime.Parse(req.endTime);
DateTime datetime1 = datetime.AddDays(1);
sqlpart = sqlpart + $" and TO_DATE(xiafatime_base, 'YYYY-MM-DD') between TO_DATE('" + req.startTime +
$"','YYYY-MM-DD') and TO_DATE('" + datetime1.ToString("yyyy-MM-dd") + $"','YYYY-MM-DD')";
}
if (!string.IsNullOrEmpty(req.caseNo))
{
sqlpart = sqlpart + $" and c.tubannum_base like '%" + req.caseNo + "%'";
}
if (!string.IsNullOrEmpty(req.isBuildName))
{
sqlpart = sqlpart + $" and c.type_base='" + req.isBuildName + "'";
}
if (!string.IsNullOrEmpty(req.nowStatus))
{
sqlpart = sqlpart + $" and c.status_base='" + req.nowStatus + "'";
}
if (!string.IsNullOrEmpty(req.tubanlaiyuan))
{
sqlpart = sqlpart + $" and laiyuan_base='" + req.tubanlaiyuan + "'";
}
if (!string.IsNullOrEmpty(req.countyid))
{
sqlpart = sqlpart + $" and weizhixiannum_base='" + req.countyid + "'";
}
if (!string.IsNullOrEmpty(req.streetid))
{
sqlpart = sqlpart + $" and weizhizhennum_base='" + req.streetid + "'";
}
var info = await client.SqlQueryable<dynamic>(sql + sqlpart).ToPageListAsync(req.page, req.limit, totalcount);
return new Response<PageInfo<List<dynamic>>>
{
Result = new PageInfo<List<dynamic>>
{
Items = info,
Total = totalcount
}
};
}
public void UpdateDroneCaseInfo(DronCaseSingleCityUpdateReq req)
{
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
if (!string.IsNullOrEmpty(req.communityid) && !string.IsNullOrEmpty(req.communityname))
{
client.Updateable<DroneCaseInfoMinerals>().SetColumns(c => new DroneCaseInfoMinerals
{
countyid = req.countyid,
countyname = req.countyname,
streetid = req.streetid,
streetname = req.streetname,
communityid = req.communityid,
communityname = req.communityname
}).Where(a => a.Id == req.Id).ExecuteCommand();
}
else
{
client.Updateable<DroneCaseInfoSingle>().SetColumns(c => new DroneCaseInfoSingle
{
countyid = req.countyid,
countyname = req.countyname,
streetid = req.streetid,
streetname = req.streetname
}).Where(a => a.Id == req.Id).ExecuteCommand();
}
}
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);
List<string> oldcaseids = new List<string>();
List<string> oldcaseids1 = new List<string>();
Dictionary<string, string> map = new Dictionary<string, string>();
map.Add("caseId", req.caseid);
string sql5 = "select new_case_id from drone_case_split where original_case_id='" + req.caseid +
"' and is_recover=0";
var caseids1 = await db.Ado.SqlQueryAsync<string>(sql5);
if (caseids1.Count > 0)
{
return new Response<string>
{
Result = "还原失败",
Message = "当前选中的案件还有分割的子案件不允许还原"
};
}
if (req.type == 0)
{
while (true)
{
string sql = "select original_case_id from drone_case_split where new_case_id='" + map["caseId"] +
"' and is_recover=0";
var caseids = await db.Ado.SqlQuerySingleAsync<string>(sql);
if (string.IsNullOrEmpty(caseids))
{
break;
}
map["caseId"] = caseids;
}
if (map["caseId"] == req.caseid)
{
return new Response<string>
{
Result = "还原失败",
Message = "案件未拆不允许还原"
};
}
oldcaseids.Add(map["caseId"]);
caseId = map["caseId"];
while (true)
{
string param = "";
for (int i = 0; i < oldcaseids.Count; i++)
{
if (oldcaseids.Count - 1 == i)
{
param = "'" + oldcaseids[i] + "'";
}
else
{
param = "'" + oldcaseids[i] + "',";
}
}
string sql1 = "select new_case_id from drone_case_split where original_case_id in (" + param +
") and is_recover=0";
var caseids = await db.Ado.SqlQueryAsync<string>(sql1);
if (caseids == null || caseids.Count == 0)
{
break;
}
oldcaseids.Clear();
oldcaseids.AddRange(caseids);
oldcaseids1.AddRange(caseids);
}
var process1 = await Repository.ChangeRepository<SugarRepositiry<WFProcess>>().AsQueryable()
.Where(t => SqlFunc.JsonField(t.InstanceInfo, "pkeyValue").Equals(caseId))
.FirstAsync();
await _processApp.RecoverProcess(process1.Id);
// 还原案件
await db.Updateable<DroneCaseInfoSingle>().SetColumns(c => new DroneCaseInfoSingle
{
is_closed = 0,
is_split = 2
}).Where(a => a.Id == caseId).ExecuteCommandAsync();
foreach (var casePart in oldcaseids1)
{
// 根据案件id取得流程
var process2 = await Repository.ChangeRepository<SugarRepositiry<WFProcess>>().AsQueryable()
.Where(t => SqlFunc.JsonField(t.InstanceInfo, "pkeyValue").Equals(casePart))
.FirstAsync();
// 作废流程
var result = await _processApp.DeleteProcess(process2.Id);
var original = await db.Queryable<DroneCaseInfoSingle>().FirstAsync(t => t.Id == casePart);
await db.Updateable<DroneCaseSplit>().SetColumns(c => new DroneCaseSplit
{
IsRecover = 1
}).Where(a => a.NewCaseId == casePart).ExecuteCommandAsync();
// 关闭案件
await db.Updateable<DroneCaseInfoSingle>().SetColumns(c => new DroneCaseInfoSingle
{
is_closed = 1
}).Where(a => a.Id == casePart).ExecuteCommandAsync();
/* var splitRelation = new DroneCaseSplit();
splitRelation.NewCaseId = caseId;
splitRelation.OriginalCaseId = casePart.caseid;
relationships.Add(splitRelation);*/
}
await db.Ado.CommitTranAsync();
return new Response<string>
{
Result = "还原成功",
Message = "还原成功"
};
}
else
{
foreach (var casePart in req.parts)
{
var original = await db.Queryable<DroneCaseInfoSingle>().FirstAsync(t => t.Id == casePart.caseid);
// 关闭案件
var record = new DroneCaseInfoSingle
{
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<DroneCaseInfoSingle>();
var original1 = await db.Queryable<DroneCaseInfoSingle>().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<DroneCaseInfoSingle>();
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;
}
}
/// <summary>
/// 关闭案件
/// </summary>
/// <param name="id">案件id</param>
/// <returns></returns>
public async Task<Response<bool>> CloseMineralCaseInfo(string id, string closereason)
{
var user = _auth.GetCurrentUser().User;
var info = client.Queryable<DroneCaseInfoMinerals>().Where(r => r.Id == id).First();
using (var uow = base.UnitWork.CreateContext())
{
await uow.DroneCaseInfoMinerals.UpdateAsync(c => new DroneCaseInfoMinerals()
{
is_closed = 1,
close_reason = closereason
}, c => c.Id == id);
var flag = uow.Commit();
var flags = flag;
////更新航飞库数据
//if (info != null && !string.IsNullOrEmpty(info.original_case_no))
//{
// var param = new
// {
// CaseNo = info.original_case_no,
// Reason = closereason
// };
// var url = _configuration.GetSection("AppSetting:ResubmitUrl").Value;
// var apiUrl = url + "api/DroneCaseinfo/CloseCaseInfo";
// var urlflag = await HttpMethods.Post(apiUrl, Json.ToJson(param));
// var reslut = (JObject)JsonConvert.DeserializeObject(urlflag);
// flags = flag && (bool)reslut["result"];
//}
if (flags)
{
return new Response<bool>
{
Result = true,
Message = "关闭成功"
};
}
else
{
return new Response<bool>
{
Code = 500,
Result = false,
Message = "关闭失败"
};
}
}
}
#region
/// <summary>
/// 获取待审核图斑--矿产
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<PageInfo<List<dynamic>>>> LoadCaseInfoCheckTuBanList(CaseInfoTypeName req)
{
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
RefAsync<int> totalcount = 0;
string sql1 = "select min(\"Level\") from sys_userorg where \"UserId\"=" + user.Id;
int level = client.Ado.GetInt(sql1);
//该用户下包含所有部门
List<string> deplist = new List<string>();
string sqlpart = " ";
if (user != null && user.Id != -1 && level != 0)
{
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();
}
string orgs = "\'" + string.Join("\',\'", deplist) + "\'";
sqlpart = $" and (countyid in ({orgs}) or streetid in ({orgs}) or communityid in ({orgs})) ";
}
string sql =
$"select case when a.\"Id\" is not null then '复提' when b.\"Id\" is null and is_closed=0 then '待审核' when b.\"Id\" is not null and is_closed=0 then '已审核' \r\nwhen is_closed=1 then '已关闭' end as checkStatus ," +
$"a.\"Id\" as retributeId,c.\"Id\",c.tubannum_base as case_no,miaoshu_base as case_description,type_base as typename,weizhixianname_base as countyname,weizhizhenname_base as streetname,weizhicunname_base as communityname,panduren_base as identification_user,createdtime_base as createtime,c.original_case_no,is_closed," +
$"b.\"Id\" as processid from drone_caseinfo_minerals c LEFT JOIN wf_process b on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" LEFT JOIN drone_resubmit a on a.\"CaseNo\"=c.original_case_no and a.\"SubjectKey\"='" +
TubanZhuanTi.feifacaikuang + "' where 1=1 ";
if (!string.IsNullOrEmpty(req.typename))
{
sqlpart = sqlpart + $" and type_base='" + req.typename + "'";
}
if (!string.IsNullOrEmpty(req.countyid))
{
sqlpart = sqlpart + $" and weizhixiannum_base='" + req.countyid + "'";
}
if (!string.IsNullOrEmpty(req.streetid))
{
sqlpart = sqlpart + $" and weizhizhennum_base='" + req.streetid + "'";
}
if (!string.IsNullOrEmpty(req.communityid))
{
sqlpart = sqlpart + $" and weizhicunnum_base='" + req.communityid + "'";
}
if (!string.IsNullOrEmpty(req.nowStatus))
{
if ("待审核".Equal(req.nowStatus))
{
sqlpart = sqlpart + $" and b.\"Id\" is null and is_closed=0 and a.\"Id\" is null";
}
else if ("已审核".Equal(req.nowStatus))
{
sqlpart = sqlpart + $" and b.\"Id\" is not null and is_closed=0";
}
else if ("已关闭".Equal(req.nowStatus))
{
sqlpart = sqlpart + $" and is_closed=1";
}
else if ("复提".Equal(req.nowStatus))
{
sqlpart = sqlpart + $" and a.\"Id\" is not null";
}
else if ("复提待审核".Equal(req.nowStatus))
{
sqlpart = sqlpart + $" and ( b.\"Id\" is null and is_closed=0 or a.\"Id\" is not null) ";
}
}
if (!string.IsNullOrEmpty(req.caseNo))
{
sqlpart = sqlpart + $" and c.tubannum_base like '%" + req.caseNo + "%'";
}
if (!string.IsNullOrEmpty(req.originalCaseNo))
{
sqlpart = sqlpart + $" and c.original_case_no like '%" + req.originalCaseNo + "%'";
}
sqlpart += $" ORDER BY createdtime_base desc";
Console.WriteLine(sql + sqlpart);
var info = await client.SqlQueryable<dynamic>(sql + sqlpart).ToPageListAsync(req.page, req.limit, totalcount);
return new Response<PageInfo<List<dynamic>>>
{
Result = new PageInfo<List<dynamic>>
{
Items = info,
Total = totalcount
}
};
}
#endregion
#region 采矿统计
public Response<List<OffenceMineralsResp>> CaseMineralsForExcel(DateTime startTime, DateTime endTime)
{
// Desc:处理状态 1-待填报， 2-待整改， 3-待县级审核，
// 4-待市级审核 5-待查处 6. 查处待县级审核 7. 查处待市级审核
// 8. 查处后待整改 9. 整改后待县级审核 10. 整改后待市级审核 99-已归档
var user = _auth.GetCurrentUser().User;
var orgIds = base._auth.GetCurrentUser().Orgs;
List<OffenceMineralsResp> listResp = new List<OffenceMineralsResp>();
List<SysOrg> org = new List<SysOrg>();
List<DroneCaseInfoMinerals> caseList = new List<DroneCaseInfoMinerals>();
if (orgIds.Any(r => r.Level == 0) || user.Id == -1) // 查询全部
{
org = client.Queryable<SysOrg>().Where(r => r.Level == 1).ToList();
caseList = client.Queryable<DroneCaseInfoMinerals>()
.LeftJoin<WFProcess>((a, b) => SqlFunc.JsonField(b.InstanceInfo, "pkeyValue") == a.Id)
.Where((a, b) => a.is_closed == 0)
.Where((a, b) => !string.IsNullOrEmpty(b.Id))
.WhereIF(startTime.Year != 0001, (a, b) => a.xiafatime_base >= startTime && a.xiafatime_base <= endTime)
.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<DroneCaseInfoMinerals>()
.LeftJoin<WFProcess>((a, b) => SqlFunc.JsonField(b.InstanceInfo, "pkeyValue") == a.Id)
.Where((a, b) => a.is_closed == 0)
.Where((a, b) => !string.IsNullOrEmpty(b.Id))
.WhereIF(startTime.Year != 0001, (a, b) => a.xiafatime_base >= startTime && a.xiafatime_base <= endTime)
.ToList();
}
foreach (var item in org)
{
var countyIdString = item.Id.ToString();
var casesForCounty = caseList.Where(r => r.countyid == countyIdString || r.streetid == countyIdString)
.ToList();
//下发图斑
var xfwtzs = casesForCounty.Count();
//合法 填合法，且审核通过
var hf = casesForCounty.Count(r => r.hefapanding_fill == 1 && r.handle_status_id == 99);
//违法 只要填违法就算
var wf = casesForCounty.Count(r => r.hefapanding_fill == 2);
//其他 填其他，且审核通过
var qt = casesForCounty.Count(r => r.hefapanding_fill == 3 && r.handle_status_id == 99);
//已完成归档 违法的，且审核归档的
var ywcgd = casesForCounty.Count(r => r.hefapanding_fill == 2 && r.handle_status_id == 99);
//未完成归档 违法的，未审核归档的
var wwcgd = casesForCounty.Count(r => r.hefapanding_fill == 3 && r.handle_status_id != 99);
//违法采矿
var wfck = casesForCounty.Count(r =>
r.hefapanding_fill == 3 && r.handle_status_id != 99 && r.typename == "开采");
//违法加工
var wfjg = casesForCounty.Count(r =>
r.hefapanding_fill == 3 && r.handle_status_id != 99 && r.typename == "加工");
//整改率
var zgl = wf != 0 ? Math.Round((double)ywcgd / wf, 2) * 100 : 0;
//驳回率
//var bohui = casesForCounty.Count(r => r.is_tbsjbohui > 0 || r.is_zgsjbohui > 0);
var bhl = xfwtzs != 0
? Math.Round(
(double)casesForCounty.Where(r => (r.is_tbsjbohui ?? 0) > 0 || (r.is_zgsjbohui ?? 0) > 0)
.Sum(r => (r.is_tbsjbohui ?? 0) + (r.is_zgsjbohui ?? 0)) / xfwtzs, 2) * 100
: 0;
//上报率 上报数/县区图斑总数 上报数指县区填报的图斑并通过市级审核的图斑数
var shangbao = casesForCounty.Count(r => r.handle_status_id != 99);
var sbl = xfwtzs != 0 ? Math.Round((double)shangbao / xfwtzs, 2) * 100 : 0;
OffenceMineralsResp offenceResp = new()
{
countyid = item.Id,
countyname = item.Name,
xfwtzs = xfwtzs,
hf = hf,
wf = wf,
qt = qt,
ywcgd = ywcgd,
wwcgd = wwcgd,
wfck = wfck,
wfjg = wfjg,
zgl = Math.Round(zgl, 2),
bhl = Math.Round(bhl, 2),
sbl = Math.Round(sbl, 2)
};
listResp.Add(offenceResp);
}
listResp = listResp.OrderByDescending(r => r.zgl).ToList();
return new Response<List<OffenceMineralsResp>>
{
Result = listResp,
Message = "获取数据成功"
};
}
public Response<MemoryStream> CaseOfMineralsToExcel(DateTime startTime, DateTime endTime)
{
Response<MemoryStream> response = new Response<MemoryStream>();
try
{
var list = CaseMineralsForExcel(startTime, endTime).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);
row21.CreateCell(8);
row21.CreateCell(9);
row21.CreateCell(10);
row21.CreateCell(11);
row21.CreateCell(12);
IRow row22 = CreateRowWithHeight(sheet2, 2, 17);
row22.CreateCell(0).SetCellValue("序号");
row22.CreateCell(1).SetCellValue("县区");
row22.CreateCell(2).SetCellValue("下发问题总数");
row22.CreateCell(3).SetCellValue("合法");
row22.CreateCell(4).SetCellValue("其他");
row22.CreateCell(5).SetCellValue("违法");
row22.CreateCell(6).SetCellValue("已完成归档");
row22.CreateCell(7).SetCellValue("未完成归档");
row22.CreateCell(8).SetCellValue("未完成归档");
row22.CreateCell(9);
row22.CreateCell(10).SetCellValue("整改率(%)");
row22.CreateCell(11).SetCellValue("驳回率(%)");
row22.CreateCell(12).SetCellValue("上报率(%)");
IRow row23 = CreateRowWithHeight(sheet2, 3, 17);
row23.CreateCell(0).SetCellValue("序号");
row23.CreateCell(1).SetCellValue("县区");
row23.CreateCell(2).SetCellValue("下发问题总数");
row23.CreateCell(3).SetCellValue("合法");
row23.CreateCell(4).SetCellValue("其他");
row23.CreateCell(5).SetCellValue("违法");
row23.CreateCell(6).SetCellValue("已完成归档");
row23.CreateCell(7).SetCellValue("未完成归档");
row23.CreateCell(8).SetCellValue("违法采矿");
row23.CreateCell(9).SetCellValue("违法加工");
row23.CreateCell(10).SetCellValue("整改率(%)");
row23.CreateCell(11).SetCellValue("驳回率(%)");
row23.CreateCell(12).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, 10)); // 合并标题行
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));
sheet2.AddMergedRegion(new CellRangeAddress(2, 3, 2, 2));
sheet2.AddMergedRegion(new CellRangeAddress(2, 3, 3, 3));
sheet2.AddMergedRegion(new CellRangeAddress(2, 3, 4, 4));
sheet2.AddMergedRegion(new CellRangeAddress(2, 3, 5, 5));
sheet2.AddMergedRegion(new CellRangeAddress(2, 3, 6, 6));
sheet2.AddMergedRegion(new CellRangeAddress(2, 3, 7, 7));
sheet2.AddMergedRegion(new CellRangeAddress(2, 3, 10, 10));
sheet2.AddMergedRegion(new CellRangeAddress(2, 3, 11, 11));
sheet2.AddMergedRegion(new CellRangeAddress(2, 2, 8, 9));
// 设置列宽
int[] normalColumns2 = { 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };
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].xfwtzs);
row.CreateCell(3).SetCellValue(list[i].hf);
row.CreateCell(4).SetCellValue(list[i].qt);
row.CreateCell(5).SetCellValue(list[i].wf);
row.CreateCell(6).SetCellValue(list[i].ywcgd);
row.CreateCell(7).SetCellValue(list[i].wwcgd);
row.CreateCell(8).SetCellValue(list[i].wfck);
row.CreateCell(9).SetCellValue(list[i].wfjg);
row.CreateCell(10).SetCellValue(list[i].zgl);
row.CreateCell(11).SetCellValue(list[i].bhl);
row.CreateCell(12).SetCellValue(list[i].sbl);
SetCellStyle(row, contentStyle22);
}
#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;
}
#endregion
#region 新汇总
public Response<List<OffenceMineralsNewResp>> CaseMineralsForExcelNew(DateTime startTime, DateTime endTime,string tubanlaiyuan)
{
// Desc:处理状态 1-待填报， 2-待整改， 3-待县级审核，
// 4-待市级审核 5-待查处 6. 查处待县级审核 7. 查处待市级审核
// 8. 查处后待整改 9. 整改后待县级审核 10. 整改后待市级审核 99-已归档
var user = _auth.GetCurrentUser().User;
var orgIds = base._auth.GetCurrentUser().Orgs;
List<OffenceMineralsNewResp> listResp = new List<OffenceMineralsNewResp>();
List<SysOrg> org = new List<SysOrg>();
List<DroneCaseInfoMinerals> caseList = new List<DroneCaseInfoMinerals>();
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<DroneCaseInfoMinerals>()
.LeftJoin<WFProcess>((a, b) => SqlFunc.JsonField(b.InstanceInfo, "pkeyValue") == a.Id)
.Where((a, b) => a.is_closed == 0)
.Where((a, b) => !string.IsNullOrEmpty(b.Id))
.WhereIF(!string.IsNullOrEmpty(tubanlaiyuan),(a,b)=>tubanlaiyuanList.Contains(a.tubanlaiyuan))
.WhereIF(startTime.Year != 0001, (a, b) => a.xiafatime_base >= startTime && a.xiafatime_base <= endTime)
.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<DroneCaseInfoMinerals>()
.LeftJoin<WFProcess>((a, b) => SqlFunc.JsonField(b.InstanceInfo, "pkeyValue") == a.Id)
.Where((a, b) => a.is_closed == 0)
.Where((a, b) => !string.IsNullOrEmpty(b.Id))
.WhereIF(!string.IsNullOrEmpty(tubanlaiyuan),(a,b)=>tubanlaiyuanList.Contains(a.tubanlaiyuan))
.WhereIF(startTime.Year != 0001, (a, b) => a.xiafatime_base >= startTime && a.xiafatime_base <= endTime)
.ToList();
}
foreach (var item in org)
{
var countyIdString = item.Id.ToString();
var casesForCounty = caseList.Where(r => r.countyid == countyIdString || r.streetid == countyIdString)
.ToList();
//下发图斑
var xfwtzs = casesForCounty.Count();
//已完成
var ywc = casesForCounty.Count(r => r.handle_status_id == 99);
//未完成
var wwc = casesForCounty.Count(r => r.handle_status_id != 99);
//完成率
var wcl = xfwtzs != 0 ? Math.Round((double)ywc / xfwtzs, 2) * 100 : 0;
//定性合法（开采和加工）
var dxhf = casesForCounty.Count(r =>
(r.hefapanding_fill == 1 || r.hefapanding_jgfill == 1) && r.handle_status_id == 99);
//定性违法（开采和加工）
var dxwf = casesForCounty.Count(r =>
(r.hefapanding_fill == 2 || r.hefapanding_jgfill == 2) && r.handle_status_id == 99);
//定性其他（开采和加工）
var dxqt = casesForCounty.Count(r =>
(r.hefapanding_fill == 3 || r.hefapanding_jgfill == 3) && r.handle_status_id == 99);
//未定性
var dxwdx = casesForCounty.Count(r => r.handle_status_id != 99);
//分类 非法开采 定性违法里的非法开采
var flffkc = casesForCounty.Count(r => r.hefapanding_fill == 2 && r.handle_status_id == 99);
//分类 非法违法 定性违法里的非法加工
var flffjg = casesForCounty.Count(r => r.hefapanding_jgfill == 2 && r.handle_status_id == 99);
//超期未报
var cqwb = casesForCounty.Count(r =>
r.handle_status_id != 99 && DateTime.Now.AddDays(-90) > r.xiafatime_base);
OffenceMineralsNewResp offenceResp = new()
{
countyid = item.Id,
countyname = item.Name,
xfwtzs = xfwtzs,
ywc = ywc,
wwc = wwc,
wcl = Math.Round(wcl, 2),
dxhf = dxhf,
dxwf = dxwf,
dxqt = dxqt,
dxwdx = dxwdx,
flffkc = flffkc,
flffjg = flffjg,
cqwb = cqwb
};
listResp.Add(offenceResp);
}
listResp = listResp.OrderByDescending(r => r.wcl).ToList();
return new Response<List<OffenceMineralsNewResp>>
{
Result = listResp,
Message = "获取数据成功"
};
}
public Response<MemoryStream> CaseOfMineralsToExcelNew(DateTime startTime, DateTime endTime,string tubanlaiyuan)
{
Response<MemoryStream> response = new Response<MemoryStream>();
try
{
var list = CaseMineralsForExcelNew(startTime, endTime,tubanlaiyuan).Result;
HSSFWorkbook workbook = new HSSFWorkbook();
#region 样式设置
ICellStyle CreateCellStyle(HSSFWorkbook wb, short fontHeight, string fontName, HorizontalAlignment hAlign,
VerticalAlignment vAlign, bool bold = false)
{
IFont font = wb.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontHeight;
font.IsBold = bold;
ICellStyle style = wb.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.Alignment = hAlign;
style.VerticalAlignment = vAlign;
style.SetFont(font);
style.WrapText = true;
return style;
}
ICellStyle CreateCellStyle2(HSSFWorkbook wb, short fontHeight, string fontName, HorizontalAlignment hAlign,
VerticalAlignment vAlign, bool bold = false)
{
IFont font = wb.CreateFont();
font.FontName = fontName;
font.FontHeightInPoints = fontHeight;
font.IsBold = bold;
ICellStyle style = wb.CreateCellStyle();
style.Alignment = hAlign;
style.VerticalAlignment = vAlign;
style.SetFont(font);
style.WrapText = true;
return style;
}
// 创建右对齐样式
ICellStyle rightAlignStyle = workbook.CreateCellStyle();
rightAlignStyle.Alignment = HorizontalAlignment.Right; // 设置右对齐
rightAlignStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
rightAlignStyle.SetFont(font);
rightAlignStyle.WrapText = true;
//第二个sheet右对齐样式
ICellStyle rightAlignStyle2 = workbook.CreateCellStyle();
rightAlignStyle2.Alignment = HorizontalAlignment.Right; // 设置右对齐
rightAlignStyle2.VerticalAlignment = VerticalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.FontName = "宋体";
font1.FontHeightInPoints = 9;
rightAlignStyle2.SetFont(font1);
rightAlignStyle2.WrapText = true;
ICellStyle contentStyle =
CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
ICellStyle titleStyle =
CreateCellStyle2(workbook, 10, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
ICellStyle headerStyle1 = CreateCellStyle2(workbook, 18, "方正小标宋简体", HorizontalAlignment.Center,
VerticalAlignment.Center);
ICellStyle headerStyle2 =
CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Right, VerticalAlignment.Center);
ICellStyle headerStyle3 =
CreateCellStyle(workbook, 16, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
//第二个sheet
ICellStyle contentStyle22 =
CreateCellStyle(workbook, 12, "宋体", HorizontalAlignment.Center, VerticalAlignment.Center);
ICellStyle titleStyle2 =
CreateCellStyle2(workbook, 9, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
ICellStyle headerStyle21 = CreateCellStyle2(workbook, 18, "方正小标宋简体", HorizontalAlignment.Center,
VerticalAlignment.Center);
ICellStyle headerStyle23 =
CreateCellStyle(workbook, 12, "黑体", HorizontalAlignment.Center, VerticalAlignment.Center);
ICellStyle beizhuStyle21 =
CreateCellStyle2(workbook, 9, "宋体", HorizontalAlignment.Left, VerticalAlignment.Center);
#endregion
#region 第一个sheet
#region 创建表头
IRow CreateRowWithHeight(ISheet sht, int rowIndex, float heightInPoints)
{
IRow row = sht.CreateRow(rowIndex);
row.HeightInPoints = heightInPoints;
return row;
}
void SetCellStyle(IRow row, ICellStyle style)
{
foreach (ICell cell in row.Cells)
{
cell.CellStyle = style;
}
}
#endregion
#endregion
#region 第二个sheet
ISheet sheet2 = workbook.CreateSheet("Sheet2");
#region 创建表头
IRow row20 = CreateRowWithHeight(sheet2, 0, 42);
row20.CreateCell(0).SetCellValue("汇总表");
IRow row22 = CreateRowWithHeight(sheet2, 1, 24);
row22.CreateCell(0).SetCellValue("县区");
row22.CreateCell(1).SetCellValue("下发线索总数（个）");
row22.CreateCell(2).SetCellValue("总数");
row22.CreateCell(3);
row22.CreateCell(4);
row22.CreateCell(5).SetCellValue("定性");
row22.CreateCell(6);
row22.CreateCell(7);
row22.CreateCell(8);
row22.CreateCell(9).SetCellValue("分类");
row22.CreateCell(10);
row22.CreateCell(11).SetCellValue("超期未报（个）");
IRow row23 = CreateRowWithHeight(sheet2, 2, 69);
row23.CreateCell(0);
row23.CreateCell(1);
row23.CreateCell(2).SetCellValue("已完成（个）");
row23.CreateCell(3).SetCellValue("未完成（个）");
row23.CreateCell(4).SetCellValue("完成率（%）");
row23.CreateCell(5).SetCellValue("合法（个）");
row23.CreateCell(6).SetCellValue("违法（个）");
row23.CreateCell(7).SetCellValue("其他（个）");
row23.CreateCell(8).SetCellValue("未定性（个）");
row23.CreateCell(9).SetCellValue("非法开采（个）");
row23.CreateCell(10).SetCellValue("非法加工（个）");
row23.CreateCell(11);
// 设置样式
SetCellStyle(row20, headerStyle21);
SetCellStyle(row22, headerStyle23);
SetCellStyle(row23, headerStyle23);
// 合并单元格
sheet2.AddMergedRegion(new CellRangeAddress(0, 0, 0, 11)); // 合并标题行
sheet2.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0));
sheet2.AddMergedRegion(new CellRangeAddress(1, 2, 1, 1));
sheet2.AddMergedRegion(new CellRangeAddress(1, 1, 2, 4));
sheet2.AddMergedRegion(new CellRangeAddress(1, 1, 5, 8));
sheet2.AddMergedRegion(new CellRangeAddress(1, 1, 9, 10));
sheet2.AddMergedRegion(new CellRangeAddress(1, 2, 11, 11));
// 设置列宽
int[] normalColumns2 = { 0, 1, 2, 3, 4, 5, 6, 7, 8 };
foreach (var index in normalColumns2) sheet2.SetColumnWidth(index, 9 * 256); // 设置列宽
int[] normalColumns1 = { 9, 10, 11 };
foreach (var index in normalColumns1) sheet2.SetColumnWidth(index, 12 * 256); // 设置列宽
#endregion
#region 填充数据
for (int i = 0; i < list.Count - 1; i++)
{
var rowIndex = i + 3; // 数据从第5行开始
var row = CreateRowWithHeight(sheet2, rowIndex, 34);
row.CreateCell(0).SetCellValue(list[i].countyname ?? "");
row.CreateCell(1).SetCellValue(list[i].xfwtzs);
row.CreateCell(2).SetCellValue(list[i].ywc);
row.CreateCell(3).SetCellValue(list[i].wwc);
row.CreateCell(4).SetCellValue(list[i].wcl);
row.CreateCell(5).SetCellValue(list[i].dxhf);
row.CreateCell(6).SetCellValue(list[i].dxwf);
row.CreateCell(7).SetCellValue(list[i].dxqt);
row.CreateCell(8).SetCellValue(list[i].dxwdx);
row.CreateCell(9).SetCellValue(list[i].flffkc);
row.CreateCell(10).SetCellValue(list[i].flffjg);
row.CreateCell(11).SetCellValue(list[i].cqwb);
SetCellStyle(row, contentStyle22);
}
#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;
}
#endregion
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);
}
/// <summary>
/// 获取待审核图斑--矿产
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<List<CaseInfoMineralTubanExport>>> LoadCaseInfoCheckList(CaseInfoTypeName req)
{
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
string sql1 = "select min(\"Level\") from sys_userorg where \"UserId\"=" + user.Id;
int level = client.Ado.GetInt(sql1);
//该用户下包含所有部门
List<string> deplist = new List<string>();
string sqlpart = " ";
if (user != null && user.Id != -1 && level != 0)
{
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();
}
string orgs = "\'" + string.Join("\',\'", deplist) + "\'";
sqlpart = $" and (countyid in ({orgs}) or streetid in ({orgs}) or communityid in ({orgs})) ";
}
string sql =
$"select case when a.\"Id\" is not null then '复提' when b.\"Id\" is null and is_closed=0 then '待审核' when b.\"Id\" is not null and is_closed=0 then '已审核' \r\nwhen is_closed=1 then '已关闭' end as checkStatus ," +
$"a.\"Id\" as retributeId,c.\"Id\",c.tubannum_base as case_no,miaoshu_base as case_description,type_base as typename,weizhixianname_base as countyname,weizhizhenname_base as streetname,weizhicunname_base as communityname,panduren_base as identification_user,createdtime_base as createtime,c.original_case_no,is_closed," +
$"b.\"Id\" as processid,c.gid as geomid from drone_caseinfo_minerals c LEFT JOIN wf_process b on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" LEFT JOIN drone_resubmit a on a.\"CaseNo\"=c.original_case_no and a.\"SubjectKey\"='" +
TubanZhuanTi.feifacaikuang + "' where 1=1 ";
if (!string.IsNullOrEmpty(req.typename))
{
sqlpart = sqlpart + $" and type_base='" + req.typename + "'";
}
if (!string.IsNullOrEmpty(req.countyid))
{
sqlpart = sqlpart + $" and weizhixiannum_base='" + req.countyid + "'";
}
if (!string.IsNullOrEmpty(req.streetid))
{
sqlpart = sqlpart + $" and weizhizhennum_base='" + req.streetid + "'";
}
if (!string.IsNullOrEmpty(req.communityid))
{
sqlpart = sqlpart + $" and weizhicunnum_base='" + req.communityid + "'";
}
if (!string.IsNullOrEmpty(req.nowStatus))
{
if ("待审核".Equal(req.nowStatus))
{
sqlpart = sqlpart + $" and b.\"Id\" is null and is_closed=0 and a.\"Id\" is null";
}
else if ("已审核".Equal(req.nowStatus))
{
sqlpart = sqlpart + $" and b.\"Id\" is not null and is_closed=0";
}
else if ("已关闭".Equal(req.nowStatus))
{
sqlpart = sqlpart + $" and is_closed=1";
}
else if ("复提".Equal(req.nowStatus))
{
sqlpart = sqlpart + $" and a.\"Id\" is not null";
}
else if ("复提待审核".Equal(req.nowStatus))
{
sqlpart = sqlpart + $" and ( b.\"Id\" is null and is_closed=0 or a.\"Id\" is not null) ";
}
}
else
{
// 待审核接口
sqlpart = sqlpart +
$"and (a.\"Id\" is not null) or (b.\"Id\" is null and is_closed=0 and a.\"Id\" is null)";
}
if (!string.IsNullOrEmpty(req.caseNo))
{
sqlpart = sqlpart + $" and c.tubannum_base like '%" + req.caseNo + "%'";
}
if (!string.IsNullOrEmpty(req.originalCaseNo))
{
sqlpart = sqlpart + $" and c.original_case_no like '%" + req.originalCaseNo + "%'";
}
sqlpart += $" ORDER BY createdtime_base desc";
Console.WriteLine(sql + sqlpart);
var info = await client.SqlQueryable<CaseInfoMineralTubanExport>(sql + sqlpart).ToListAsync();
return new Response<List<CaseInfoMineralTubanExport>>
{
Result = info
};
}
/// <summary>
/// </summary>
/// <param name="req"></param>
/// <param name="shpFilePath"></param>
/// <param name="shpFilePathzip"></param>
/// <exception cref="Exception"></exception>
public void ExportApprovalCaseInfoShapefile(CaseInfoTypeName req, string shpFilePath, string shpFilePathzip)
{
var response = LoadCaseInfoCheckList(req).Result.Result;
List<CaseInfoMineralTubanExport> list = new List<CaseInfoMineralTubanExport>();
for (int i = 0; i < response.Count; i++)
{
var caseinfo = response[i];
if (!string.IsNullOrEmpty(caseinfo.geomid))
{
list.Add(caseinfo);
}
}
List<int> gids = list.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,case_no,typename,is_illegal_name,ST_AsText(geom) AS geom FROM view_drone_caseinfo_minerals WHERE gid IN ({gidsString})";
// 执行查询
var data = client.Ado.SqlQuery<dynamic>(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.typename == null ? "" : row.typename },
{ "判定结果", row.is_illegal_name == null ? "" : row.is_illegal_name },
};
IFeature feature = new Feature(geometry, attributes);
features.Add(feature);
}
if (features.Count == 0)
{
throw new Exception("数据不可用");
}
// 导出 SHP 文件及其关联文件
//ExportToShapefileFour(shpFilePath, features);
ExportToShapefileFour(shpFilePath, features);
// 将文件打包成 ZIP
CreateZipFromShapefiles(shpFilePath, shpFilePathzip);
}
}
Reference in New Issue View Git Blame Copy Permalink