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

3117 lines
137 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.Dynamic;
using System.IO.Compression;
using System.Text;
using DocumentFormat.OpenXml.Spreadsheet;
using Infrastructure;
using Infrastructure.Extensions;
using Microsoft.Extensions.Configuration;
using NetTopologySuite.Features;
using NetTopologySuite.Geometries;
using NetTopologySuite.IO;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
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.Response;
using OpenAuth.Repository;
using OpenAuth.Repository.Domain;
using SqlSugar;
using Infrastructure.Helpers;
namespace OpenAuth.App.ServiceApp.DroneCaseInfo;
public class DroneCaseInfoSatelliteApp : SqlSugarBaseApp<DroneCaseInfoSatellite, SugarDbContext>
{
private readonly IConfiguration _configuration;
private readonly ISqlSugarClient client;
private readonly WFProcessApp _processApp;
private readonly ShpLayerSourceApp _shpLayerSourceApp;
CommonDataManager _commonDataManager;
public DroneCaseInfoSatelliteApp(ISugarUnitOfWork<SugarDbContext> unitWork,
ISimpleClient<DroneCaseInfoSatellite> 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;
}
public async Task<Response<DroneCaseInfoSatellite>> GetCaseInfoById(string id)
{
var info = await Repository.AsSugarClient().Queryable<DroneCaseInfoSatellite>().Where(c => c.Id == id)
.FirstAsync();
return new Response<DroneCaseInfoSatellite>
{
Result = info,
Message = "获取数据成功"
};
}
/// <summary>
/// 案件详情
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Response<AddOrUpdateDroneCaseInfoReqExtCurrent>> GetCaseInfo(string id)
{
var res = new AddOrUpdateDroneCaseInfoReqExtCurrent();
res.Init();
//详情
DroneCaseInfoSatellite info = null;
var flag = StringExtension.IsGuid(id);
if (flag)
{
info = await Repository.AsSugarClient().Queryable<DroneCaseInfoSatellite>().Where(c => c.Id == id)
.FirstAsync();
}
else
{
info = await Repository.AsSugarClient().Queryable<DroneCaseInfoSatellite>().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
{
res.lng = info.lng;
res.lat = info.lat;
}
res.info = info;
res.relationCaseNo = relationCaseNoList;
res.tags = tags;
return new Response<AddOrUpdateDroneCaseInfoReqExtCurrent>
{
Result = res,
Message = "获取数据成功"
};
}
/// <summary>
/// 填报审核
/// </summary>
/// <param name="year"></param>
/// <param name="tubanlaiyuan"></param>
/// <param name="picihao"></param>
/// <param name="illegal"></param>
/// <returns></returns>
public async Task<Response<List<dynamic>>> LoadReportCaseCount(int year, string tubanlaiyuan, string picihao,
int illegal)
{
//获取当前用户
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
var orgList = authStrategyContext.Orgs;
var leafNode = new List<SysOrg>();
// 统计该区划下数据 计算那些祖先节点不存在于列表中的
var isCity = 0; // isCity 等于1时，查询全市区数据
if (user.Id == -1)
{
isCity = 1;
}
else
{
foreach (var sysOrg in orgList)
{
if (sysOrg.ParentId == 0) // 该节点是临沂市
{
isCity = 1;
orgList = orgList.Where(t => t.ParentId.Equals(sysOrg.Id)).ToList();
// 跳出for循环
break;
}
var ids = sysOrg.CascadeId.Split(".");
// 排队自身
if (orgList.Any(org => org.Id != sysOrg.Id && ids.Contains(org.Id.ToString())))
{
leafNode.Add(sysOrg);
}
}
if (isCity != 1) // 顶级节点
{
orgList = orgList.Except(leafNode).ToList();
}
}
var orgIds = orgList.Select(x => x.Id.ToString()).ToList();
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoSatelliteSchemeCode").Value;
// 查询数据
var dateTime = year == 0 ? DateTime.Now : new DateTime(year, 1, 1);
var query = client.Queryable<WFTask>()
.LeftJoin<WFProcess>((t, p) => t.ProcessId == p.Id)
.LeftJoin<DroneCaseInfoSatellite>((t, p, c) => p.Id == c.Id)
.WhereIF(year != 0,(t, p, c) => SqlFunc.DateIsSame((DateTime)c.synchronoustime, dateTime, DateType.Year))
.Where((t, p, c) => c.countyid != null)
.Where((t, p, c) => c.is_closed.Equals(0))
.Where((t, p, c) => p.SchemeCode.Equals(schemeCode))
.WhereIF(!string.IsNullOrEmpty(tubanlaiyuan), (t, p, c) => c.tubanlaiyuan.Equals(tubanlaiyuan))
.WhereIF(!string.IsNullOrEmpty(picihao), (t, p, c) => c.tubanlaiyuan.Equals(picihao))
.WhereIF(isCity == 0, (t, p, c)
=> orgIds.Contains(c.countyid) || orgIds.Contains(c.streetid) || orgIds.Contains(c.communityid));
// todo 判断当前用户是市级人员还是县级人员 市级待核销任务 县级审核任务
// 县级待审核，市级，待销号 县级， （待核销+已核销）/总数 市级，已核销/总数
// 查询信息包含 区划 任务id 督办时间 ，案件部门信息 is_illegal weifaleixing measure_name
// 如果是市级是不是查整个人临沂市的数据
// 返回 level areaid areaname
var dataList = new List<dynamic>();
// 取得所有区县或乡镇信息
// //合法：已经归档的合法案件 is_illegal
//违法：已经归档的违法案件，未归档的案件 is_illegal
//其他：已经归档的其他案件 is_illegal
//非粮化：已经归档的非粮化 weifaleixing
//补办手续：已经归档的补办手续 measure_name
//拆除拆除复耕：已经归档的拆除 measure_name
if (isCity == 1)
{
var groupDataList = await query.GroupBy((t, p, c) => new { c.countyid, c.countyname }).Select((t, p, c) =>
new
{
c.countyid,
c.countyname,
list = SqlFunc.Subqueryable<DroneCaseInfoSatellite>()
.LeftJoin<WFTask>((s, f) => s.Id == f.ProcessId)
.Where(s => s.countyid == c.countyid).ToList((s, f) => new ExamineItemResponse
{
taskid = f.Id,
taskstatus = f.State,
unitname = f.UnitName, // 初审（待审核） 复审（待销号）
isouttime = f.IsOutTime, // 0未超期，1超期，2严重超期
isbuildingname = s.is_build_name, // 标注类型 在建 已建成 持续变化
isillegal = s.is_illegal, // 0-合法，1-违法，2-其他
caseno = s.case_no,
countyname = s.countyname,
countyid = s.countyid,
streetname = s.streetname,
streatid = s.streetid,
receive = s.is_jieshou,
isclosed = s.is_closed,
illegaltype = s.weifaleixing, // 违法类型 0非农化1非粮化
measurename = s.measure_name, // 整改措施（0拆除复耕1补办手续
handstatusid = s.handle_status_id, // 5 已归档
handstatusname = s.handle_status_name // 4. 待核销 5. 已
})
}).ToListAsync();
foreach (var grouping in groupDataList)
{
dynamic item = new ExpandoObject();
item.level = 1;
item.areaid = grouping.countyid; // 区划id
item.areaname = grouping.countyname; // 区划名称
// 任务总数
string sql = $"select \"SortNo\" from sys_org where \"Id\"=" + grouping.countyid;
var sortNo = client.Ado.GetInt(sql);
item.sortno = sortNo;
item.totaltask = grouping.list.GroupBy(t => t.caseno).Count();
// 接收案件任务
item.receivetask = grouping.list.Where(t => t.receive.Equals(1) && t.isclosed.Equals(0))
.GroupBy(t => t.caseno).Count();
// 处理状态 0待接收1待判定2待整改3县级审核4市级审核5已归档
// 已核销任务数
item.verificatedtask =
grouping.list.Where(t => t.handstatusid != null && t.handstatusid.Equals(5)).GroupBy(t => t.caseno)
.Count(); //
// 超期任务数
item.overduetask = grouping.list.Where(t => t.handstatusid != null && t.handstatusid.Equals(5)).Where(
t =>
(t.isouttime != null && t.isouttime.Equals(1)) ||
(t.isouttime != null && t.isouttime.Equals(2)))
.GroupBy(t => t.caseno).Count();
// 待核销任务数
item.verifytask = grouping.list
.WhereIF(illegal != 0,
t => t.isillegal.Equals(0) || t.isillegal.Equals(2) ||
(t.isillegal.Equals(1) && t.illegaltype.Equals(1))) // 审核监督（填报审核）
.WhereIF(illegal == 0, t => t.isillegal.Equals(1) && t.illegaltype.Equals(0)) // 整改审核
.Where(t => t.unitname != null && t.unitname.Equals("市级审核") && t.taskstatus != null &&
t.taskstatus.Equals(1)).GroupBy(t => t.caseno).Count();
//合法案件
item.legalcase = grouping.list.Where(t =>
t.isillegal != null && t.isillegal.Equals(0) && t.handstatusid != null &&
t.handstatusid.Equals(5)).GroupBy(t => t.caseno).Count();
// 其它案件
item.ilegalcase = grouping.list.Where(t =>
t.isillegal != null && t.isillegal.Equals(2) && t.handstatusid != null &&
t.handstatusid.Equals(5))
.GroupBy(t => t.caseno).Count();
// 非粮化
item.nonfoodcase = grouping.list.Where(t =>
t.isillegal != null && t.isillegal.Equals(1) && t.illegaltype != null && t.illegaltype.Equals(1) &&
t.handstatusid != null &&
t.handstatusid.Equals(5)).GroupBy(t => t.caseno).Count();
// 补办手续 整改措施（0拆除复耕1补办手续）
item.makeupcase = grouping.list.Where(t =>
t.handstatusid != null && t.handstatusid.Equals(5) && t.isillegal != null &&
t.isillegal.Equals(1) && t.measurename != null && t.measurename.Equals("1"))
.GroupBy(t => t.caseno)
.Count();
// 复耕
item.rehabilitationcase = grouping.list.Where(t =>
t.handstatusid != null && t.handstatusid.Equals(5) && t.isillegal != null &&
t.isillegal.Equals(1) && t.measurename != null && t.measurename.Equals("0"))
.GroupBy(t => t.caseno)
.Count();
// 市级，已核销/总数
// 县级（待审核） 市级(待销号）
dataList.Add(item);
}
dataList = dataList.OrderBy(a => a.sortno).ToList();
}
else
{
var groupDataList = await query.GroupBy((t, p, c) => new { c.streetid, c.streetname }).Select((t, p, c) =>
new
{
c.streetid,
c.streetname,
list = SqlFunc.Subqueryable<DroneCaseInfoSatellite>()
.LeftJoin<WFTask>((s, f) => s.Id == f.ProcessId)
.Where(s => s.streetid == c.streetid).ToList((s, f) => new ExamineItemResponse
{
taskid = f.Id,
taskstatus = f.State,
unitname = f.UnitName, // 初审（待审核） 复审（待销号）
isouttime = f.IsOutTime, // 0未超期，1超期，2严重超期
isbuildingname = s.is_build_name, // 标注类型 在建 已建成 持续变化
isillegal = s.is_illegal, // 0-合法，1-违法，2-其他
caseno = s.case_no,
countyname = s.countyname,
countyid = s.countyid,
streetname = s.streetname,
streatid = s.streetid,
receive = s.is_jieshou,
illegaltype = s.weifaleixing, // 违法类型 0非农化1非粮化
measurename = s.measure_name, // 整改措施（0拆除复耕1补办手续
handstatusid = s.handle_status_id, // 5 已归档
handstatusname = s.handle_status_name // 4. 待核销 5. 已
})
}).ToListAsync();
foreach (var grouping in groupDataList)
{
dynamic item = new ExpandoObject();
item.level = 2;
item.areaid = grouping.streetid; // 区划id
item.areaname = grouping.streetname; // 区划名称
item.totaltask = grouping.list.GroupBy(t => t.caseno).Count(); // 任务总数
// 接收案件任务
item.receivetask = grouping.list.Where(t => t.receive.Equals(1)).GroupBy(t => t.caseno).Count();
// 处理状态 0待接收1待判定2待整改3县级审核4市级审核5已归档
// 已核销任务数
item.verificatedtask =
grouping.list.Where(t => t.handstatusid != null && t.handstatusid.Equals(5)).GroupBy(t => t.caseno)
.Count(); //
// 超期任务数
item.overduetask = grouping.list.Where(t =>
(t.isouttime != null && t.isouttime.Equals(1)) ||
(t.isouttime != null && t.isouttime.Equals(2)))
.GroupBy(t => t.caseno).Count();
// 待审核任务数
item.verifytask = grouping.list
.Where(t => t.unitname != null && t.unitname.Equals("县级审核") && t.taskstatus != null &&
t.taskstatus.Equals(1))
.WhereIF(illegal != 0,
t => t.isillegal.Equals(0) || t.isillegal.Equals(2) ||
(t.isillegal.Equals(1) && t.illegaltype.Equals(1))) // 审核监督
.WhereIF(illegal == 0, t => t.isillegal.Equals(1) && t.illegaltype.Equals(0)) // 违法
.GroupBy(t => t.caseno).Count();
//合法案件
item.legalcase = grouping.list.Where(t =>
t.isillegal != null && t.isillegal.Equals(0) && t.handstatusid != null &&
t.handstatusid.Equals(5)).GroupBy(t => t.caseno).Count();
// 其它案件
item.ilegalcase = grouping.list.Where(t =>
t.isillegal != null && t.isillegal.Equals(2) && t.handstatusid != null &&
t.handstatusid.Equals(5))
.GroupBy(t => t.caseno).Count();
// 非粮化
item.nonfoodcase = grouping.list.Where(t =>
t.isillegal != null && t.isillegal.Equals(1) && t.illegaltype != null && t.illegaltype.Equals(1) &&
t.handstatusid != null &&
t.handstatusid.Equals(5)).GroupBy(t => t.caseno).Count();
// 补办手续
item.makeupcase = grouping.list.Where(t =>
t.handstatusid != null && t.handstatusid.Equals(5) && t.isillegal != null &&
t.isillegal.Equals(1) && t.measurename != null && t.measurename.Equals("1"))
.GroupBy(t => t.caseno)
.Count();
// 复耕
item.rehabilitationcase = grouping.list.Where(t =>
t.handstatusid != null && t.handstatusid.Equals(5) && t.isillegal != null &&
t.isillegal.Equals(1) && t.measurename != null && t.measurename.Equals("0"))
.GroupBy(t => t.caseno)
.Count();
dataList.Add(item);
}
}
Console.WriteLine("统计计算结束时间：" + DateTime.Now.ToString());
return new Response<List<dynamic>>
{
Result = dataList
};
}
/// <summary>
/// 填报审核第二层接口
/// </summary>
/// <param name="areaid">区域id</param>
/// <param name="level">等级</param>
/// <param name="year">年</param>
/// <param name="is_build_complete">标注类型</param>
/// <param name="geomid">图斑号</param>
/// <returns></returns>
public async Task<Response<PageInfo<List<dynamic>>>> LoadTaskDetailList(TaskDetailReq req)
{
// todo 批次号，
//获取当前用户
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
var orgList = authStrategyContext.Orgs;
var leafNode = new List<SysOrg>();
// 统计该区划下数据 计算那些祖先节点不存在于列表中的
var isCity = 0;
if (user.Id == -1)
{
isCity = 1;
}
else
{
foreach (var sysOrg in orgList)
{
if (sysOrg.ParentId == 0)
{
isCity = 1;
orgList = orgList.Where(t => t.ParentId.Equals(0)).ToList();
break;
}
var ids = sysOrg.CascadeId.Split(".");
if (orgList.Any(org => org.Id != sysOrg.Id && ids.Contains(org.Id.ToString())))
{
leafNode.Add(sysOrg);
}
}
if (isCity != 1) // 顶级节点
{
orgList = orgList.Except(leafNode).ToList();
}
}
var orgIds = orgList.Select(x => x.Id.ToString()).ToList();
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoSatelliteSchemeCode").Value;
// 查询数据
var dateTime = req.year == 0 ? DateTime.Now : new DateTime(req.year, 1, 1);
RefAsync<int> totalCount = 0;
using var db = Repository.AsSugarClient();
var info = await db.Queryable<WFTask>()
.LeftJoin<WFProcess>((t, p) => t.ProcessId == p.Id)
.LeftJoin<DroneCaseInfoSatellite>((t, p, c) => SqlFunc.JsonField(p.InstanceInfo, "pkeyValue") == c.Id)
.LeftJoin<DroneTaskFavorite>((t, p, c, f) => f.TaskId == t.Id && f.FavoriteUserId == user.Id.ToString())
.WhereIF(req.year != 0,(t, p, c, f) => SqlFunc.DateIsSame((DateTime)c.synchronoustime, dateTime, DateType.Year))
.Where((t, p, c, f) => c.countyid != null)
.Where((t, p, c, f) => p.SchemeCode.Equals(schemeCode))
.Where((t, p, c, f) => c.is_closed.Equals(0))
.WhereIF(!string.IsNullOrEmpty(req.tubanlaiyuan), (t, p, c, f) => c.tubanlaiyuan.Equals(req.tubanlaiyuan))
.WhereIF(!string.IsNullOrEmpty(req.picihao), (t, p, c, f) => c.tubanlaiyuan.Equals(req.picihao))
// 激活状态
.Where((t, p, c, f) => t.State.Equals(1))
.WhereIF(req.type == 1, (t, p, c, f) => f.FavoriteUserId.Equals(user.Id.ToString()))
.WhereIF(isCity == 0, (t, p, c, f)
=> (orgIds.Contains(c.countyid) || orgIds.Contains(c.streetid) || orgIds.Contains(c.communityid)) &&
t.UnitName.Equals("县级审核"))
.WhereIF(isCity == 1, (t, p, c, f) => t.UnitName.Equals("市级审核"))
// 图斑编号即案件编号
.WhereIF(!string.IsNullOrEmpty(req.geomid), (t, p, c, f) => c.case_no.Contains(req.geomid))
.Where((t, p, c, f) => c.is_illegal.Equals(0) || c.is_illegal.Equals(2) ||
(c.is_illegal.Equals(1) && c.weifaleixing.Equals(1)))
//.Where((t, p, c, f) => c.weifaleixing.Equals(0)) // 非农化
//.Where((t, p, c, f) => c.is_illegal.Equals(1)) // 违法
// 标注类型-> 合法 其他 非粮化 拆除复耕 补办手续
.WhereIF(!string.IsNullOrEmpty(req.is_build_name) && req.is_build_name.Equals("合法"),
(t, p, c, f) => c.is_illegal.Equals(0))
.WhereIF(!string.IsNullOrEmpty(req.is_build_name) && req.is_build_name.Equals("其他"),
(t, p, c, f) => c.is_illegal.Equals(2))
.WhereIF(!string.IsNullOrEmpty(req.is_build_name) && req.is_build_name.Equals("非粮化"),
(t, p, c, f) => c.weifaleixing.Equals(1))
.WhereIF(!string.IsNullOrEmpty(req.is_build_name) && req.is_build_name.Equals("拆除复耕"),
(t, p, c, f) => c.measure_name.Equals("0"))
.WhereIF(!string.IsNullOrEmpty(req.is_build_name) && req.is_build_name.Equals("补办手续"),
(t, p, c, f) => c.measure_name.Equals("1"))
.WhereIF(!string.IsNullOrEmpty(req.areaid),
(t, p, c, f) => c.countyid.Equals(req.areaid) || c.streetid.Equals(req.areaid))
// 驳回过滤
.WhereIF(!string.IsNullOrEmpty(req.nowStatus) && req.nowStatus.Length > 5,
(t, p, c, f) => c.is_drawback.Equals(1)
|| c.is_shijibohui.Equals(1))
.WhereIF(!string.IsNullOrEmpty(req.nowStatus)
&& req.nowStatus.Length < 5 && req.nowStatus.Contains("市级驳回"),
(t, p, c, f) => c.is_shijibohui.Equals(1))
.WhereIF(!string.IsNullOrEmpty(req.nowStatus)
&& req.nowStatus.Length < 5 && req.nowStatus.Contains("县级驳回"),
(t, p, c, f) => c.is_drawback.Equals(1))
.OrderByIF(!string.IsNullOrEmpty(req.sort) && !SqlFunc.ToLower("CreateDate").Equals(req.sort.ToLower()),
$"CAST({req.sort} AS numeric) {req.order}")
.OrderByIF(string.IsNullOrEmpty(req.sort) || req.sort.ToLower().Equals("CreateDate".ToLower()),
(t, p, c, f) => t.CreateDate,
(string.IsNullOrEmpty(req.order) || req.order.Equals("asc")) ? OrderByType.Asc : OrderByType.Desc)
.Select<dynamic>((t, p, c, f) => new
{
Fid = f.Id,
schemeId = p.SchemeId,
taskeid = t.Id,
taskstate = t.State,
tasktype = t.Type,
processcode = t.ProcessCode,
processid = t.ProcessId,
unitname = t.UnitName,
isouttime = t.IsOutTime,
c.case_no,
c.case_description,
c.address,
c.geomid,
c.area,
c.nongyongdi_area,
c.gengdi_area,
c.yongjiujibennongtian_area,
c.zhongdianquyu_area,
c.shengtaibaohuhongxian_area,
c.guotukongjianguihua_area,
c.Id,
c.is_illegal,
c.is_build_name,
c.countyid,
c.countyname,
c.streetname,
c.communityname,
c.streetid,
c.communityid,
c.is_drawback,
c.is_shijibohui,
c.xianjiyijian,
c.shijiyijian,
c.xjshenhejieguo,
c.sjshenhejieguo
})
.ToPageListAsync(req.page, req.limit, totalCount);
//
/*foreach (dynamic o in info)
{
var taskId = (string)o.taskeid;
var schemeId = (string)o.schemeid;
// 查询任务信息
using var db = Repository.AsSugarClient();
var wfTask = await db.Queryable<WFTask>().Where(a => a.Id.Equals(taskId)).FirstAsync();
var unitId = wfTask.UnitId;
var isSupervise = wfTask.IsSupervise;
//未超时，短暂超时和严重超时
o.supervisestate = 0; //未超时
o.cancelsupervise = 0; // 是否可取消督办
// 督办任务
if (isSupervise.Equals(1))
{
var superviseTime = wfTask.SuperviseTime;
var wfScheme = await db.Queryable<WFScheme>().Where(a => a.Id.Equals(schemeId)).FirstAsync();
var contentJObject = (JObject)JsonConvert.DeserializeObject(wfScheme.Content);
var wfData = (JArray)contentJObject["wfData"];
foreach (var unit in wfData)
{
var id = unit["id"]?.ToString();
if (id != null && id.Equals(unitId))
{
// 超时通知 isOvertimeMessage
//第一次通知(时) overtimeMessageStart
//通知间隔(时) overtimeMessageInterval 1
// 超时流转时间(时) overtimeGo 12
// 超时通知策略 overtimeMessageType
// todo 查询超时配置
if (unit["isOvertimeMessage"].ToBool())
{
DateTime now = DateTime.Now;
var overtimeMessageInterval = unit["overtimeMessageInterval"].ToInt();
var overtimeGo = unit["overtimeGo"].ToInt();
if (now
.CompareTo(superviseTime.ToDateTime()
.AddHours(overtimeGo + overtimeMessageInterval)) > 0)
{
// 严重超期
o.supervisestate = 2;
}
else if (now.CompareTo(superviseTime.ToDateTime().AddHours(overtimeGo)) > 0)
{
o.supervisestate = 1; // 短暂超期
}
}
}
}
o.cancelsupervise = 0;
var superviseId = (long)o.superviseid;
// 设置督办是否可取消
if (user.Id.Equals(-1))
{
o.cancelsupervise = 1;
}
else
{
//当前用户所属部门
var orgIds = authStrategyContext.Orgs.Select(x => x.Id).ToList();
var count = await db.Queryable<SysUser>()
.LeftJoin<SysUserOrg>((u, uo) => u.Id == uo.UserId)
.LeftJoin<SysOrg>((u, uo, g) => uo.OrgId == g.Id)
.Where($"string_to_array(g.\"CascadeId\",'.') && ARRAY['{string.Join(",", orgIds)}']")
.Where((u, uo, g) => u.Id == superviseId).CountAsync();
if (count > 0)
{
o.cancelsupervise = 1;
}
}
}
}*/
return new Response<PageInfo<List<dynamic>>>
{
Result = new PageInfo<List<dynamic>>
{
Items = info,
Total = totalCount
}
};
}
/// <summary>
/// 整改审核
/// </summary>
/// <param name="year"></param>
/// <param name="tubanlaiyuan"></param>
/// <param name="picihao"></param>
/// <param name="illegal"></param>
/// <returns></returns>
/// <exception cref="NotImplementedException"></exception>
public async Task<Response<List<dynamic>>> LoadReformCaseCount(int year, string tubanlaiyuan, string picihao,
int illegal)
{
//获取当前用户
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
var orgList = authStrategyContext.Orgs;
var leafNode = new List<SysOrg>();
// 统计该区划下数据 计算那些祖先节点不存在于列表中的
var isCity = 0; // isCity 等于1时，查询全市区数据
if (user.Id == -1)
{
isCity = 1;
}
else
{
foreach (var sysOrg in orgList)
{
if (sysOrg.ParentId == 0) // 该节点是临沂市
{
isCity = 1;
orgList = orgList.Where(t => t.ParentId.Equals(sysOrg.Id)).ToList();
// 跳出for循环
break;
}
var ids = sysOrg.CascadeId.Split(".");
// 排队自身
if (orgList.Any(org => org.Id != sysOrg.Id && ids.Contains(org.Id.ToString())))
{
leafNode.Add(sysOrg);
}
}
if (isCity != 1) // 顶级节点
{
orgList = orgList.Except(leafNode).ToList();
}
}
var orgIds = orgList.Select(x => x.Id.ToString()).ToList();
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoSatelliteSchemeCode").Value;
// 查询数据
var dateTime = year == 0 ? DateTime.Now : new DateTime(year, 1, 1);
var query = client.Queryable<WFTask>()
.LeftJoin<WFProcess>((t, p) => t.ProcessId == p.Id)
.LeftJoin<DroneCaseInfoSatellite>((t, p, c) => p.Id == c.Id)
.WhereIF(year != 0,(t, p, c) => SqlFunc.DateIsSame((DateTime)c.synchronoustime, dateTime, DateType.Year))
.Where((t, p, c) => c.countyid != null)
.Where((t, p, c) => c.is_closed.Equals(0))
.Where((t, p, c) => p.SchemeCode.Equals(schemeCode))
.WhereIF(!string.IsNullOrEmpty(tubanlaiyuan), (t, p, c) => c.tubanlaiyuan.Equals(tubanlaiyuan))
.WhereIF(!string.IsNullOrEmpty(picihao), (t, p, c) => c.tubanlaiyuan.Equals(picihao))
.WhereIF(isCity == 0, (t, p, c)
=> orgIds.Contains(c.countyid) || orgIds.Contains(c.streetid) || orgIds.Contains(c.communityid));
// todo 判断当前用户是市级人员还是县级人员 市级待核销任务 县级审核任务
// 县级待审核，市级，待销号 县级， （待核销+已核销）/总数 市级，已核销/总数
// 查询信息包含 区划 任务id 督办时间 ，案件部门信息 is_illegal weifaleixing measure_name
// 如果是市级是不是查整个人临沂市的数据
// 返回 level areaid areaname
var dataList = new List<dynamic>();
// 取得所有区县或乡镇信息
// //合法：已经归档的合法案件 is_illegal
//违法：已经归档的违法案件，未归档的案件 is_illegal
//其他：已经归档的其他案件 is_illegal
//非粮化：已经归档的非粮化 weifaleixing
//补办手续：已经归档的补办手续 measure_name
//拆除拆除复耕：已经归档的拆除 measure_name
if (isCity == 1)
{
var groupDataList = await query.GroupBy((t, p, c) => new { c.countyid, c.countyname }).Select((t, p, c) =>
new
{
c.countyid,
c.countyname,
list = SqlFunc.Subqueryable<DroneCaseInfoSatellite>()
.LeftJoin<WFTask>((s, f) => s.Id == f.ProcessId)
.Where(s => s.countyid == c.countyid).ToList((s, f) => new ExamineItemResponse
{
taskid = f.Id,
taskstatus = f.State,
unitname = f.UnitName, // 初审（待审核） 复审（待销号）
isouttime = f.IsOutTime, // 0未超期，1超期，2严重超期
isbuildingname = s.is_build_name, // 标注类型 在建 已建成 持续变化
isillegal = s.is_illegal, // 0-合法，1-违法，2-其他
caseno = s.case_no,
countyname = s.countyname,
countyid = s.countyid,
streetname = s.streetname,
streatid = s.streetid,
receive = s.is_jieshou,
isclosed = s.is_closed,
illegaltype = s.weifaleixing, // 违法类型 0非农化1非粮化
measurename = s.measure_name, // 整改措施（0拆除复耕1补办手续
handstatusid = s.handle_status_id, // 5 已归档
handstatusname = s.handle_status_name // 4. 待核销 5. 已
})
}).ToListAsync();
foreach (var grouping in groupDataList)
{
dynamic item = new ExpandoObject();
item.level = 1;
item.areaid = grouping.countyid; // 区划id
item.areaname = grouping.countyname; // 区划名称
// 任务总数
string sql = $"select \"SortNo\" from sys_org where \"Id\"=" + grouping.countyid;
var sortNo = client.Ado.GetInt(sql);
item.sortno = sortNo;
item.totaltask = grouping.list.GroupBy(t => t.caseno).Count();
// 接收案件任务
item.receivetask = grouping.list.Where(t => t.receive.Equals(1) && t.isclosed.Equals(0))
.GroupBy(t => t.caseno).Count();
// 处理状态 0待接收1待判定2待整改3县级审核4市级审核5已归档
// 已核销任务数
item.verificatedtask =
grouping.list.Where(t => t.handstatusid != null && t.handstatusid.Equals(5)).GroupBy(t => t.caseno)
.Count(); //
// 超期任务数
item.overduetask = grouping.list.Where(t => t.handstatusid != null && t.handstatusid.Equals(5)).Where(
t =>
(t.isouttime != null && t.isouttime.Equals(1)) ||
(t.isouttime != null && t.isouttime.Equals(2)))
.GroupBy(t => t.caseno).Count();
// 待核销任务数
item.verifytask = grouping.list
.WhereIF(illegal != 0,
t => t.isillegal.Equals(0) || t.isillegal.Equals(2) ||
(t.isillegal.Equals(1) && t.illegaltype.Equals(1))) // 审核监督（填报审核）
.WhereIF(illegal == 0, t => t.isillegal.Equals(1) && t.illegaltype.Equals(0)) // 整改审核
.Where(t => t.unitname != null && t.unitname.Equals("市级审核") && t.taskstatus != null &&
t.taskstatus.Equals(1)).GroupBy(t => t.caseno).Count();
//合法案件
item.legalcase = grouping.list.Where(t =>
t.isillegal != null && t.isillegal.Equals(0) && t.handstatusid != null &&
t.handstatusid.Equals(5)).GroupBy(t => t.caseno).Count();
// 其它案件
item.ilegalcase = grouping.list.Where(t =>
t.isillegal != null && t.isillegal.Equals(2) && t.handstatusid != null &&
t.handstatusid.Equals(5))
.GroupBy(t => t.caseno).Count();
// 非粮化
item.nonfoodcase = grouping.list.Where(t =>
t.isillegal != null && t.isillegal.Equals(1) && t.illegaltype != null && t.illegaltype.Equals(1) &&
t.handstatusid != null &&
t.handstatusid.Equals(5)).GroupBy(t => t.caseno).Count();
// 补办手续 整改措施（0拆除复耕1补办手续）
item.makeupcase = grouping.list.Where(t =>
t.handstatusid != null && t.handstatusid.Equals(5) && t.isillegal != null &&
t.isillegal.Equals(1) && t.measurename != null && t.measurename.Equals("1"))
.GroupBy(t => t.caseno)
.Count();
// 复耕
item.rehabilitationcase = grouping.list.Where(t =>
t.handstatusid != null && t.handstatusid.Equals(5) && t.isillegal != null &&
t.isillegal.Equals(1) && t.measurename != null && t.measurename.Equals("0"))
.GroupBy(t => t.caseno)
.Count();
// 市级，已核销/总数
// 县级（待审核） 市级(待销号）
dataList.Add(item);
}
dataList = dataList.OrderBy(a => a.sortno).ToList();
}
else
{
var groupDataList = await query.GroupBy((t, p, c) => new { c.streetid, c.streetname }).Select((t, p, c) =>
new
{
c.streetid,
c.streetname,
list = SqlFunc.Subqueryable<DroneCaseInfoSatellite>()
.LeftJoin<WFTask>((s, f) => s.Id == f.ProcessId)
.Where(s => s.streetid == c.streetid).ToList((s, f) => new ExamineItemResponse
{
taskid = f.Id,
taskstatus = f.State,
unitname = f.UnitName, // 初审（待审核） 复审（待销号）
isouttime = f.IsOutTime, // 0未超期，1超期，2严重超期
isbuildingname = s.is_build_name, // 标注类型 在建 已建成 持续变化
isillegal = s.is_illegal, // 0-合法，1-违法，2-其他
caseno = s.case_no,
countyname = s.countyname,
countyid = s.countyid,
streetname = s.streetname,
streatid = s.streetid,
receive = s.is_jieshou,
illegaltype = s.weifaleixing, // 违法类型 0非农化1非粮化
measurename = s.measure_name, // 整改措施（0拆除复耕1补办手续
handstatusid = s.handle_status_id, // 5 已归档
handstatusname = s.handle_status_name // 4. 待核销 5. 已
})
}).ToListAsync();
foreach (var grouping in groupDataList)
{
dynamic item = new ExpandoObject();
item.level = 2;
item.areaid = grouping.streetid; // 区划id
item.areaname = grouping.streetname; // 区划名称
item.totaltask = grouping.list.GroupBy(t => t.caseno).Count(); // 任务总数
// 接收案件任务
item.receivetask = grouping.list.Where(t => t.receive.Equals(1)).GroupBy(t => t.caseno).Count();
// 处理状态 0待接收1待判定2待整改3县级审核4市级审核5已归档
// 已核销任务数
item.verificatedtask =
grouping.list.Where(t => t.handstatusid != null && t.handstatusid.Equals(5)).GroupBy(t => t.caseno)
.Count(); //
// 超期任务数
item.overduetask = grouping.list.Where(t =>
(t.isouttime != null && t.isouttime.Equals(1)) ||
(t.isouttime != null && t.isouttime.Equals(2)))
.GroupBy(t => t.caseno).Count();
// 待审核任务数
item.verifytask = grouping.list
.Where(t => t.unitname != null && t.unitname.Equals("县级审核") && t.taskstatus != null &&
t.taskstatus.Equals(1))
.WhereIF(illegal != 0,
t => t.isillegal.Equals(0) || t.isillegal.Equals(2) ||
(t.isillegal.Equals(1) && t.illegaltype.Equals(1))) // 审核监督
.WhereIF(illegal == 0, t => t.isillegal.Equals(1) && t.illegaltype.Equals(0)) // 违法
.GroupBy(t => t.caseno).Count();
//合法案件
item.legalcase = grouping.list.Where(t =>
t.isillegal != null && t.isillegal.Equals(0) && t.handstatusid != null &&
t.handstatusid.Equals(5)).GroupBy(t => t.caseno).Count();
// 其它案件
item.ilegalcase = grouping.list.Where(t =>
t.isillegal != null && t.isillegal.Equals(2) && t.handstatusid != null &&
t.handstatusid.Equals(5))
.GroupBy(t => t.caseno).Count();
// 非粮化
item.nonfoodcase = grouping.list.Where(t =>
t.isillegal != null && t.isillegal.Equals(1) && t.illegaltype != null && t.illegaltype.Equals(1) &&
t.handstatusid != null &&
t.handstatusid.Equals(5)).GroupBy(t => t.caseno).Count();
// 补办手续
item.makeupcase = grouping.list.Where(t =>
t.handstatusid != null && t.handstatusid.Equals(5) && t.isillegal != null &&
t.isillegal.Equals(1) && t.measurename != null && t.measurename.Equals("1"))
.GroupBy(t => t.caseno)
.Count();
// 复耕
item.rehabilitationcase = grouping.list.Where(t =>
t.handstatusid != null && t.handstatusid.Equals(5) && t.isillegal != null &&
t.isillegal.Equals(1) && t.measurename != null && t.measurename.Equals("0"))
.GroupBy(t => t.caseno)
.Count();
dataList.Add(item);
}
}
Console.WriteLine("统计计算结束时间：" + DateTime.Now.ToString());
return new Response<List<dynamic>>
{
Result = dataList
};
}
/// <summary>
/// 查询任务列表数据 整改审核第二层接口
/// </summary>
/// <param name="areaid">区域id</param>
/// <param name="level">等级</param>
/// <param name="year">年</param>
/// <param name="is_build_complete">标注类型</param>
/// <param name="geomid">图斑号</param>
/// <returns></returns>
public async Task<Response<PageInfo<List<dynamic>>>> LoadTaskIllegalDetailList(TaskDetailReq req)
{
// todo 批次号，
//获取当前用户
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
var orgList = authStrategyContext.Orgs;
var leafNode = new List<SysOrg>();
// 统计该区划下数据 计算那些祖先节点不存在于列表中的
var isCity = 0;
if (user.Id == -1)
{
isCity = 1;
}
else
{
foreach (var sysOrg in orgList)
{
if (sysOrg.ParentId == 0)
{
isCity = 1;
orgList = orgList.Where(t => t.ParentId.Equals(0)).ToList();
break;
}
var ids = sysOrg.CascadeId.Split(".");
if (orgList.Any(org => org.Id != sysOrg.Id && ids.Contains(org.Id.ToString())))
{
leafNode.Add(sysOrg);
}
}
if (isCity != 1) // 顶级节点
{
orgList = orgList.Except(leafNode).ToList();
}
}
var orgIds = orgList.Select(x => x.Id.ToString()).ToList();
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoSatelliteSchemeCode").Value;
// 查询数据
var dateTime = req.year == 0 ? DateTime.Now : new DateTime(req.year, 1, 1);
RefAsync<int> totalCount = 0;
using var db = Repository.AsSugarClient();
var info = await db.Queryable<WFTask>()
.LeftJoin<WFProcess>((t, p) => t.ProcessId == p.Id)
.LeftJoin<DroneCaseInfoSatellite>((t, p, c) => SqlFunc.JsonField(p.InstanceInfo, "pkeyValue") == c.Id)
.LeftJoin<DroneTaskFavorite>((t, p, c, f) => f.TaskId == t.Id && f.FavoriteUserId == user.Id.ToString())
.WhereIF(req.year != 0,(t, p, c, f) => SqlFunc.DateIsSame((DateTime)c.synchronoustime, dateTime, DateType.Year))
.Where((t, p, c, f) => c.countyid != null)
.Where((t, p, c, f) => c.weifaleixing.Equals(0)) // 非农化
.Where((t, p, c, f) => c.is_illegal.Equals(1)) // 违法
.Where((t, p, c, f) => p.SchemeCode.Equals(schemeCode))
.Where((t, p, c, f) => c.is_closed.Equals(0))
.WhereIF(!string.IsNullOrEmpty(req.tubanlaiyuan), (t, p, c, f) => c.tubanlaiyuan.Equals(req.tubanlaiyuan))
.WhereIF(!string.IsNullOrEmpty(req.picihao), (t, p, c, f) => c.tubanlaiyuan.Equals(req.picihao))
// 激活状态
.Where((t, p, c, f) => t.State.Equals(1))
.WhereIF(req.type == 1, (t, p, c, f) => f.FavoriteUserId.Equals(user.Id.ToString()))
.WhereIF(isCity == 0, (t, p, c, f)
=> (orgIds.Contains(c.countyid) || orgIds.Contains(c.streetid) || orgIds.Contains(c.communityid)) &&
t.UnitName.Equals("县级审核"))
.WhereIF(isCity == 1, (t, p, c, f) => t.UnitName.Equals("市级审核"))
// 图斑编号即案件编号
.WhereIF(!string.IsNullOrEmpty(req.geomid), (t, p, c, f) => c.case_no.Contains(req.geomid))
// 标注类型-> 非粮化 拆除复耕 补办手续
.WhereIF(!string.IsNullOrEmpty(req.is_build_name) && req.is_build_name.Equals("非粮化"),
(t, p, c, f) => c.weifaleixing.Equals(1))
.WhereIF(!string.IsNullOrEmpty(req.is_build_name) && req.is_build_name.Equals("拆除复耕"),
(t, p, c, f) => c.measure_name.Equals("0"))
.WhereIF(!string.IsNullOrEmpty(req.is_build_name) && req.is_build_name.Equals("补办手续"),
(t, p, c, f) => c.measure_name.Equals("1"))
.WhereIF(string.IsNullOrEmpty(req.is_build_name),
(t, p, c, f) => c.is_illegal.Equals(1))
.WhereIF(!string.IsNullOrEmpty(req.areaid),
(t, p, c, f) => c.countyid.Equals(req.areaid) || c.streetid.Equals(req.areaid))
.OrderByIF(!string.IsNullOrEmpty(req.sort) && !SqlFunc.ToLower("CreateDate").Equals(req.sort.ToLower()),
$"CAST({req.sort} AS numeric) {req.order}")
.OrderByIF(string.IsNullOrEmpty(req.sort) || req.sort.ToLower().Equals("CreateDate".ToLower()),
(t, p, c, f) => t.CreateDate,
(string.IsNullOrEmpty(req.order) || req.order.Equals("asc")) ? OrderByType.Asc : OrderByType.Desc)
.Select<dynamic>((t, p, c, f) => new
{
Fid = f.Id,
schemeId = p.SchemeId,
taskeid = t.Id,
taskstate = t.State,
tasktype = t.Type,
processcode = t.ProcessCode,
processid = t.ProcessId,
unitname = t.UnitName,
isouttime = t.IsOutTime,
c.case_no,
c.case_description,
c.address,
c.geomid,
c.area,
c.nongyongdi_area,
c.gengdi_area,
c.yongjiujibennongtian_area,
c.zhongdianquyu_area,
c.shengtaibaohuhongxian_area,
c.guotukongjianguihua_area,
c.Id,
c.is_illegal,
c.is_build_name,
c.countyid,
c.countyname,
c.streetname,
c.communityname,
c.streetid,
c.communityid,
c.is_drawback,
c.is_shijibohui,
c.xianjiyijian,
c.shijiyijian,
c.xjshenhejieguo,
c.sjshenhejieguo
})
.ToPageListAsync(req.page, req.limit, totalCount);
return new Response<PageInfo<List<dynamic>>>
{
Result = new PageInfo<List<dynamic>>
{
Items = info,
Total = totalCount
}
};
}
/// <summary>
/// 接收办理
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<PageInfo<List<dynamic>>>> LoadCaiKuangTaskList(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.countyid in ({orgs}) or c.streetid in ({orgs}) or c.communityid in ({orgs})) ";
}
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoSatelliteSchemeCode").Value;
string sql = "";
if (req.type == 0)
{
sql =
$"select c.is_shijibohui,c.xianjiyijian,c.shijiyijian,c.xjshenhejieguo,c.sjshenhejieguo,t.\"Id\" as Fid, a.\"Id\" as taskId,a.\"UnitName\" as unitName,c.createtime as createDate,c.is_split,c.case_no as caseNo,c.countyname,c.streetname,c.is_build_name as isBuildName,\r\nc.area,a.\"ProcessCode\" as processcode,a.\"ProcessId\" as processid," +
$"c.gengdi_area as gengdiArea,yongjiujibennongtian_area as yongjiujibennongtianArea ,nongyongdi_area as nongyongdiArea,c.geomid,c.\"Id\" as id,c.is_drawback as isDrawback \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\nLEFT JOIN drone_caseinfo_wpxf c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
schemeCode + "' ";
}
else
{
sql =
$"select c.is_shijibohui,c.xianjiyijian,c.shijiyijian,c.xjshenhejieguo,c.sjshenhejieguo,t.\"Id\" as Fid, a.\"Id\" as taskId,a.\"UnitName\" as unitName,c.is_split,c.createtime as createDate,c.case_no as caseNo,c.countyname,c.streetname,c.is_build_name as isBuildName,\r\nc.area,a.\"ProcessCode\" as processcode,a.\"ProcessId\" as processid," +
$"c.gengdi_area as gengdiArea,yongjiujibennongtian_area as yongjiujibennongtianArea ,nongyongdi_area as nongyongdiArea,c.geomid,c.\"Id\" as id,c.is_drawback as isDrawback \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\nLEFT JOIN drone_caseinfo_wpxf c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
schemeCode + "' ";
}
sqlpart = sqlpart + "and a.\"State\"=1 ";
if (req.isIllegal < 100)
{
sqlpart = sqlpart + $" and c.is_illegal=" + req.isIllegal + "";
}
if (req.weifaleixing < 100)
{
sqlpart = sqlpart + $" and c.weifaleixing=" + req.weifaleixing + "";
}
if (!string.IsNullOrEmpty(req.measureName))
{
sqlpart = sqlpart + $" and c.measure_name='" + req.measureName + "'";
}
/* if (req.landType == null)// 图斑调整查询
{
sqlpart = sqlpart + $" and a.\"UnitName\" in ('待填报','待接收')";
// 任务状态 1-激活状态 3-
sqlpart = sqlpart + "and (a.\"State\"=1 or (a.\"State\"= 7 and c.is_split = 1))";
}
else
{
sqlpart = sqlpart + "and a.\"State\"=1 ";
}
if (0 == req.landType)
{
sqlpart = sqlpart + $" and c.typename like '%农用地%'";
}
else if (1 == req.landType)
{
sqlpart = sqlpart + $" and c.typename like '%建设用地%'";
}
else if (2 == req.landType)
{
sqlpart = sqlpart + $" and c.typename like '%推堆土%'";
}*/
if (req.year != 0)
{
sqlpart = sqlpart + $" and EXTRACT(YEAR FROM synchronoustime) = {req.year}";
}
if (!string.IsNullOrEmpty(req.tubanlaiyuan))
{
sqlpart = sqlpart + $" and c.tubanlaiyuan='" + req.tubanlaiyuan + "'";
}
if (!string.IsNullOrEmpty(req.picihao))
{
sqlpart = sqlpart + $" and c.picihao='" + req.picihao + "'";
}
if (!string.IsNullOrEmpty(req.isBuildName))
{
sqlpart = sqlpart + $" and c.is_build_name='" + req.isBuildName + "'";
}
if (!string.IsNullOrEmpty(req.startTime) && !string.IsNullOrEmpty(req.endTime))
{
DateTime datetime = DateTime.Parse(req.endTime);
DateTime datetime1 = datetime.AddDays(1);
sqlpart = sqlpart + $" and synchronoustime between TO_DATE('" + req.startTime +
$"','YYYY-MM-DD') and TO_DATE('" + datetime1.ToString("yyyy-MM-dd") + $"','YYYY-MM-DD')";
}
if (!string.IsNullOrEmpty(req.streetid))
{
sqlpart = sqlpart + $" and c.streetid='" + req.streetid + "'";
}
if (!string.IsNullOrEmpty(req.countyid))
{
sqlpart = sqlpart + $" and c.countyid='" + req.countyid + "'";
}
if (!string.IsNullOrEmpty(req.caseNo))
{
sqlpart = sqlpart + $" and c.case_no like '%" + req.caseNo + "%'";
}
if (!string.IsNullOrEmpty(req.nowStatus))
{
string[] str = req.nowStatus.Split(",");
int isDarwback = 0;
string strstatus = "";
for (int i = 0; i < str.Length; i++)
{
// 县级驳回 is_drawback 市级驳回 is_shijibohui
if (req.nowStatus.Contains("市级驳回") && req.nowStatus.Contains("县级驳回"))
{
sqlpart = sqlpart + $" and (c.is_drawback = '1' or c.is_shijibohui = '1')";
continue;
}
if ("县级驳回".Equals(str[i]))
{
sqlpart = sqlpart + $" and c.is_drawback = '1'";
continue;
}
if ("市级驳回".Equals(str[i]))
{
sqlpart = sqlpart + $" and c.is_shijibohui = '1'";
continue;
}
strstatus = strstatus + "'" + str[i] + "'" + ",";
}
if (!string.IsNullOrEmpty(strstatus))
{
string statuslist = strstatus.Substring(0, strstatus.Length - 1);
sqlpart = sqlpart + $" and a.\"UnitName\" in (" + statuslist + ")";
}
else
{
sqlpart = sqlpart + $" and a.\"UnitName\" in ('待接收','待填报','待整改')";
}
}
else
{
sqlpart = sqlpart + $" and a.\"UnitName\" in ('待接收','待填报','待整改')";
}
if (!string.IsNullOrEmpty(req.isSplit))
{
string[] str = req.isSplit.Split(",");
if (str.Length == 1)
{
if (str[0].Equals("正常"))
{
sqlpart = sqlpart + "and (c.is_split = '0' or c.is_split = '2')";
}
else
{
sqlpart = sqlpart + "and c.is_split = '1'";
}
}
}
if (req.isOutTime == 0)
{
sqlpart = sqlpart + $" and a.\"IsOutTime\"=" + req.isOutTime + "";
}
if (req.isOutTime == 1)
{
sqlpart = sqlpart + $" and a.\"IsOutTime\">=" + req.isOutTime + "";
}
if (!string.IsNullOrEmpty(req.tubanArea1) && !string.IsNullOrEmpty(req.tubanArea2))
{
sqlpart = sqlpart + $" and CAST(c.area AS numeric) between " + double.Parse(req.tubanArea1) + " and " +
double.Parse(req.tubanArea2);
}
if (!string.IsNullOrEmpty(req.gengdiArea1) && !string.IsNullOrEmpty(req.gengdiArea2))
{
sqlpart = sqlpart + $" and CAST(c.gengdi_area AS numeric) between " + double.Parse(req.gengdiArea1) +
" and " + double.Parse(req.gengdiArea2);
}
if (!string.IsNullOrEmpty(req.sortType))
{
if ("synchronoustime".Equals(req.sortType))
{
sqlpart += $" order by {req.sortType} {req.order}";
}
else
{
sqlpart += $" order by CAST(" + req.sortType + $" AS numeric) {req.order}";
}
}
else
{
sqlpart += $" ORDER BY c.synchronoustime desc";
}
var info = await client.SqlQueryable<dynamic>(sql + sqlpart).ToPageListAsync(req.page, req.limit, totalcount);
foreach (dynamic o in info)
{
if (o.is_split == 1)
{
o.unitname = "已关闭";
}
}
return new Response<PageInfo<List<dynamic>>>
{
Result = new PageInfo<List<dynamic>>
{
Items = info,
Total = totalcount
}
};
}
public async Task<Response<bool>> AddTaskFavorite(DroneTaskFavorite model)
{
var _count = await base.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 base.Repository.ChangeRepository<SugarRepositiry<DroneTaskFavorite>>().InsertAsync(model);
if (flag)
return new Response<bool>
{
Result = true,
Message = "收藏成功"
};
else
return new Response<bool>
{
Code = 500,
Result = false,
Message = "收藏失败"
};
}
public async Task<Response<bool>> DeleteTaskFavoriteCase(string id)
{
var flag = await base.Repository.ChangeRepository<SugarRepositiry<DroneTaskFavorite>>().DeleteByIdAsync(id);
if (flag)
return new Response<bool>
{
Result = true,
Message = "删除成功"
};
else
return new Response<bool>
{
Code = 500,
Result = false,
Message = "删除失败"
};
}
/// <summary>
/// 图斑调整列表
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<PageInfo<List<dynamic>>>> LoadCaseInfoTuBanList(CaseInfoTuBanReq 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})) ";
}
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoSatelliteSchemeCode").Value;
string sqlcount = $" select count(a.\"Id\")\r\n from drone_caseinfo_wpxf 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\",tubanlaiyuan,countyname,streetname,communityname,case_no as caseNo,synchronoustime identificationTime,area,\r\n gengdi_area as gengdiArea,typename,yongjiujibennongtian_area yongjiujibennongtianarea," +
$"case when is_illegal=0 \r\n then '合法' when is_illegal=1 then '违法' when is_illegal=2 then '其他' else '违法' end as isIllegal,handle_status_name as handleStatusName," +
$"xiangmumc,xiangmuzhuti,actual_use_to as actualUseTo,case when weifaleixing=0 \r\n then '非农化违法用地' when weifaleixing=1 then '非粮化违法用地' end as weifaleixing," +
$"case when cast(measure_name AS numeric)=0 \r\n then '拆除复耕' when cast(measure_name AS numeric)=1 then '补办手续' end as measureName,\r\nqita_use_to as qitaUseTo\r\n from drone_caseinfo_wpxf 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 ";
if (req.year != 0)
{
sqlpart = sqlpart + $" and EXTRACT(YEAR FROM synchronoustime) = {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 synchronoustime between TO_DATE('" + req.startTime +
$"','YYYY-MM-DD') and TO_DATE('" + datetime1.ToString("yyyy-MM-dd") + $"','YYYY-MM-DD')";
}
if (!string.IsNullOrEmpty(req.measureName))
{
// 判定违法
sqlpart = sqlpart + "and is_illegal = '1'";
sqlpart = sqlpart + $" and measure_name='" + req.measureName + "'";
}
if (!string.IsNullOrEmpty(req.typename))
{
sqlpart = sqlpart + $" and typename='" + req.typename + "'";
}
if (!string.IsNullOrEmpty(req.tubanlaiyuan))
{
sqlpart = sqlpart + $" and tubanlaiyuan='" + req.tubanlaiyuan + "'";
}
if (!string.IsNullOrEmpty(req.picihao))
{
sqlpart = sqlpart + $" and picihao='" + req.picihao + "'";
}
if (!string.IsNullOrEmpty(req.isBuildName))
{
sqlpart = sqlpart + $" and is_build_name='" + req.isBuildName + "'";
}
if (req.isIllegal < 5)
{
sqlpart = sqlpart + $" and is_illegal=" + req.isIllegal + "";
}
if (!string.IsNullOrEmpty(req.countyid))
{
sqlpart = sqlpart + $" and countyid='" + req.countyid + "'";
}
if (!string.IsNullOrEmpty(req.streetid))
{
sqlpart = sqlpart + $" and streetid='" + req.streetid + "'";
}
if (!string.IsNullOrEmpty(req.nowStatus))
{
string[] str = req.nowStatus.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 handle_status_name in (" + statuslist + ")";
}
}
if (!string.IsNullOrEmpty(req.caseNo))
{
sqlpart = sqlpart + $" and case_no like '%" + req.caseNo + "%'";
}
if (!string.IsNullOrEmpty(req.originalcaseno))
{
sqlpart = sqlpart + $" and original_case_no like '%" + req.originalcaseno + "%'";
}
if (!string.IsNullOrEmpty(req.tubanArea1) && !string.IsNullOrEmpty(req.tubanArea2))
{
sqlpart = sqlpart + $" and CAST(area 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(area AS numeric)> " + double.Parse(req.tubanArea1);
}
else if (string.IsNullOrEmpty(req.tubanArea1) && !string.IsNullOrEmpty(req.tubanArea2))
{
sqlpart = sqlpart + $" and CAST(area AS numeric)< " + double.Parse(req.tubanArea2);
}
if (!string.IsNullOrEmpty(req.gengdiArea1) && !string.IsNullOrEmpty(req.gengdiArea2))
{
sqlpart = sqlpart + $" and CAST(gengdi_area AS numeric) between " + double.Parse(req.gengdiArea1) +
" and " + double.Parse(req.gengdiArea2);
}
else if (!string.IsNullOrEmpty(req.gengdiArea1) && string.IsNullOrEmpty(req.gengdiArea2))
{
sqlpart = sqlpart + $" and CAST(gengdi_area AS numeric)> " + double.Parse(req.gengdiArea1);
}
else if (string.IsNullOrEmpty(req.gengdiArea1) && !string.IsNullOrEmpty(req.gengdiArea2))
{
sqlpart = sqlpart + $" and CAST(gengdi_area AS numeric)< " + double.Parse(req.gengdiArea2);
}
if (!string.IsNullOrEmpty(req.jibenArea1) && !string.IsNullOrEmpty(req.jibenArea2))
{
sqlpart = sqlpart + $" and CAST(yongjiujibennongtian_area AS numeric) between " +
double.Parse(req.jibenArea1) +
" and " + double.Parse(req.jibenArea2);
}
else if (!string.IsNullOrEmpty(req.jibenArea1) && string.IsNullOrEmpty(req.jibenArea2))
{
sqlpart = sqlpart + $" and CAST(yongjiujibennongtian_area AS numeric)> " + double.Parse(req.jibenArea1);
}
else if (string.IsNullOrEmpty(req.jibenArea1) && !string.IsNullOrEmpty(req.jibenArea2))
{
sqlpart = sqlpart + $" and CAST(yongjiujibennongtian_area AS numeric)< " + double.Parse(req.jibenArea2);
}
var count = await client.Ado.GetIntAsync(sqlcount + sqlpart);
sqlpart += $" ORDER BY synchronoustime 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)
{
if ("其他".Equals(item.isillegal))
{
if ("1".Equals(item.qitauseto))
{
item.actualuseto = "不改变原用地性质的光伏用地";
}
else if ("2".Equals(item.qitauseto))
{
item.actualuseto = "设施农业用地";
}
else if ("3".Equals(item.qitauseto))
{
item.actualuseto = "农村道路";
}
else if ("4".Equals(item.qitauseto))
{
item.actualuseto = "实地未变化";
}
else if ("5".Equals(item.qitauseto))
{
item.actualuseto = "临时用地";
}
else
{
item.actualuseto = "";
}
}
}
return new Response<PageInfo<List<dynamic>>>
{
Result = new PageInfo<List<dynamic>>
{
Items = info,
Total = count
}
};
}
public Response<List<CaseInfoTubanExport>> ExprotLoadCaseInfoTuBanList(CaseInfoTuBanReq 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 picihao,xjshenhejieguo,sjshenhejieguo,xianjiyijian,shijiyijian,case_description,verifyuser,hexiaoren,verifytime,hexiaotime,tubanlaiyuan,countyname,streetname,communityname,case_no as caseNo,synchronoustime identificationTime,area,\r\n gengdi_area as gengdiArea,typename,jieshou_people,jieshou_time,pandingyijushuoming,examiner_name,examine_time," +
$"transactor_name,transact_time,yongjiujibennongtian_area yongjiujibennongtianarea,remark," +
$"case when is_illegal=0 \r\n then '合法' when is_illegal=1 then '违法' when is_illegal=2 then '其他' else '违法' end as isIllegal,handle_status_name as handleStatusName," +
$"xiangmumc,xiangmuzhuti,actual_use_to as actualUseTo,case when weifaleixing=0 \r\n then '非农化违法用地' when weifaleixing=1 then '非粮化违法用地' end as weifaleixing," +
$"case when cast(measure_name AS numeric)=0 \r\n then '拆除复耕' when cast(measure_name AS numeric)=1 then '补办手续' end as measureName,\r\nqita_use_to as qitaUseTo,geomid\r\n from drone_caseinfo_wpxf 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 ";
if (req.year != 0)
{
sqlpart = sqlpart + $" and EXTRACT(YEAR FROM synchronoustime) = {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 synchronoustime between TO_DATE('" + req.startTime +
$"','YYYY-MM-DD') and TO_DATE('" + datetime1.ToString("yyyy-MM-dd") + $"','YYYY-MM-DD')";
}
if (!string.IsNullOrEmpty(req.measureName))
{
sqlpart = sqlpart + $" and measure_name='" + req.measureName + "'";
}
if (!string.IsNullOrEmpty(req.typename))
{
sqlpart = sqlpart + $" and typename='" + req.typename + "'";
}
if (!string.IsNullOrEmpty(req.tubanlaiyuan))
{
sqlpart = sqlpart + $" and tubanlaiyuan='" + req.tubanlaiyuan + "'";
}
if (!string.IsNullOrEmpty(req.picihao))
{
sqlpart = sqlpart + $" and picihao='" + req.picihao + "'";
}
if (!string.IsNullOrEmpty(req.isBuildName))
{
sqlpart = sqlpart + $" and is_build_name='" + req.isBuildName + "'";
}
if (req.isIllegal < 5)
{
sqlpart = sqlpart + $" and is_illegal=" + req.isIllegal + "";
}
if (!string.IsNullOrEmpty(req.countyid))
{
sqlpart = sqlpart + $" and countyid='" + req.countyid + "'";
}
if (!string.IsNullOrEmpty(req.streetid))
{
sqlpart = sqlpart + $" and streetid='" + req.streetid + "'";
}
if (!string.IsNullOrEmpty(req.nowStatus))
{
string[] str = req.nowStatus.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 handle_status_name in (" + statuslist + ")";
}
}
if (!string.IsNullOrEmpty(req.caseNo))
{
sqlpart = sqlpart + $" and case_no like '%" + req.caseNo + "%'";
}
if (!string.IsNullOrEmpty(req.originalcaseno))
{
sqlpart = sqlpart + $" and original_case_no like '%" + req.originalcaseno + "%'";
}
if (!string.IsNullOrEmpty(req.tubanArea1) && !string.IsNullOrEmpty(req.tubanArea2))
{
sqlpart = sqlpart + $" and CAST(area 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(area AS numeric)> " + double.Parse(req.tubanArea1);
}
else if (string.IsNullOrEmpty(req.tubanArea1) && !string.IsNullOrEmpty(req.tubanArea2))
{
sqlpart = sqlpart + $" and CAST(area AS numeric)< " + double.Parse(req.tubanArea2);
}
if (!string.IsNullOrEmpty(req.gengdiArea1) && !string.IsNullOrEmpty(req.gengdiArea2))
{
sqlpart = sqlpart + $" and CAST(gengdi_area AS numeric) between " + double.Parse(req.gengdiArea1) +
" and " + double.Parse(req.gengdiArea2);
}
else if (!string.IsNullOrEmpty(req.gengdiArea1) && string.IsNullOrEmpty(req.gengdiArea2))
{
sqlpart = sqlpart + $" and CAST(gengdi_area AS numeric)> " + double.Parse(req.gengdiArea1);
}
else if (string.IsNullOrEmpty(req.gengdiArea1) && !string.IsNullOrEmpty(req.gengdiArea2))
{
sqlpart = sqlpart + $" and CAST(gengdi_area AS numeric)< " + double.Parse(req.gengdiArea2);
}
if (!string.IsNullOrEmpty(req.jibenArea1) && !string.IsNullOrEmpty(req.jibenArea2))
{
sqlpart = sqlpart + $" and CAST(yongjiujibennongtian_area AS numeric) between " +
double.Parse(req.jibenArea1) +
" and " + double.Parse(req.jibenArea2);
}
else if (!string.IsNullOrEmpty(req.jibenArea1) && string.IsNullOrEmpty(req.jibenArea2))
{
sqlpart = sqlpart + $" and CAST(yongjiujibennongtian_area AS numeric)> " + double.Parse(req.jibenArea1);
}
else if (string.IsNullOrEmpty(req.jibenArea1) && !string.IsNullOrEmpty(req.jibenArea2))
{
sqlpart = sqlpart + $" and CAST(yongjiujibennongtian_area AS numeric)< " + double.Parse(req.jibenArea2);
}
sqlpart += $" ORDER BY synchronoustime desc";
var info = client.Ado.SqlQuery<CaseInfoTubanExport>(sql + sqlpart);
foreach (var item in info)
{
if ("其他".Equals(item.isIllegal))
{
item.actualUseTo = item.qitaUseTo;
}
}
Response<List<CaseInfoTubanExport>> response = new Response<List<CaseInfoTubanExport>>();
response.Result = info;
return response;
}
public void ExportCaseInfoShapefile(CaseInfoTuBanReq req, string shpFilePath, string shpFilePathzip)
{
var response = ExprotLoadCaseInfoTuBanList(req).Result;
List<CaseInfoTubanExport> list = new List<CaseInfoTubanExport>();
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);
if (string.IsNullOrEmpty(gidsString))
{
throw new Exception("暂无数据");
}
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_wpxf b on a.relid=b.\"Id\" WHERE gid IN ({gidsString})";
// 执行查询
var data = client.Ado.SqlQuery<dynamic>(query);
if (data == null || data.Count == 0)
{
}
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 }
};
IFeature feature = new Feature(geometry, attributes);
features.Add(feature);
}
if (features.Count == 0)
{
throw new Exception("数据不可用");
}
// 导出 SHP 文件及其关联文件
ExportToShapefileFour(shpFilePath, features);
// 将文件打包成 ZIP
CreateZipFromShapefiles(shpFilePath, shpFilePathzip);
}
static void ExportToShapefileFour(string shpPath, List<IFeature> features)
{
var geometryFactory = new GeometryFactory();
// 获取 SHP 文件头
var header = ShapefileDataWriter.GetHeader(features[0], features.Count);
// 创建 ShapefileDataWriter
var shapeFileWriter = new ShapefileDataWriter(shpPath, geometryFactory)
{
Header = header
};
// 写入 SHP 文件
shapeFileWriter.Write(features);
// 写入 CPG 文件（定义字符编码）
var cpgPath = Path.ChangeExtension(shpPath, "cpg");
File.WriteAllText(cpgPath, "UTF-8");
}
static void CreateZipFromShapefiles(string shpPath, string zipPath)
{
var files = new List<string>
{
shpPath,
Path.ChangeExtension(shpPath, "shx"),
Path.ChangeExtension(shpPath, "dbf"),
Path.ChangeExtension(shpPath, "cpg")
};
using (var zipArchive = ZipFile.Open(zipPath, ZipArchiveMode.Create))
{
foreach (var file in files)
{
if (File.Exists(file))
{
zipArchive.CreateEntryFromFile(file, Path.GetFileName(file));
}
}
}
}
private Geometry ParseGeometry(string wkt)
{
if (string.IsNullOrEmpty(wkt))
{
return null;
}
var reader = new WKTReader();
return reader.Read(wkt);
}
public Response<MemoryStream> ListToExcelTuban(List<CaseInfoTubanExport> list, List<ModuleColumn> headers)
{
Response<MemoryStream> response = new Response<MemoryStream>();
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
#region 内容样式
IFont font1 = workbook.CreateFont(); //创建一个字体样式对象
font1.FontName = "Microsoft YaHei"; //和excel里面的字体对应
//font1.Boldweight = short.MaxValue;//字体加粗
font1.FontHeightInPoints = 12; //字体大小
ICellStyle style = workbook.CreateCellStyle(); //创建样式对象
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.SetFont(font1); //将字体样式赋给样式对象
style.WrapText = true;
#endregion
#region 标题样式
IFont font = workbook.CreateFont(); //创建一个字体样式对象
font.FontName = "Microsoft YaHei"; //和excel里面的字体对应
font.Boldweight = (short)FontBoldWeight.Bold; //字体加粗
font.FontHeightInPoints = 12; //字体大小
ICellStyle style1 = workbook.CreateCellStyle(); //创建样式对象
style1.BorderBottom = BorderStyle.Thin;
style1.BorderLeft = BorderStyle.Thin;
style1.BorderRight = BorderStyle.Thin;
style1.BorderTop = BorderStyle.Thin;
style1.Alignment = HorizontalAlignment.Center;
style1.VerticalAlignment = VerticalAlignment.Center;
style1.SetFont(font); //将字体样式赋给样式对象
#endregion
// 创建一个数值格式的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);
#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 ("tubanlaiyuan".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 300);
}
else if ("picihao".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 300);
}
else if ("countyname".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 15 * 300);
}
else if ("streetname".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 15 * 300);
}
else if ("communityname".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 15 * 300);
}
else if ("typename".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 320);
}
else if ("caseNo".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 350);
}
else if ("identificationTime".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 320);
}
else if ("area".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("gengdiArea".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("yongjiujibennongtianarea".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("isillegal".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("handleStatusName".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("jieshou_people".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("jieshou_time".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 320);
}
else if ("xiangmumc".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("xiangmuzhuti".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("actualUseTo".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("weifaleixing".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 15 * 300);
}
else if ("pandingyijushuoming".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 15 * 300);
}
else if ("examiner_name".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 10 * 300);
}
else if ("examine_time".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 320);
}
else if ("measureName".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("transactor_name".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("transact_time".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 320);
}
else if ("remark".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 320);
}
else if ("xjshenhejieguo".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 320);
}
else if ("sjshenhejieguo".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 320);
}
else if ("xianjiyijian".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 320);
}
else if ("shijiyijian".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 320);
}
else if ("verifyuser".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("hexiaoren".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 12 * 300);
}
else if ("verifytime".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 320);
}
else if ("hexiaotime".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 320);
}
else if ("case_description".Equals(headers[i].key))
{
sheet.SetColumnWidth(i, 20 * 320);
}
}
#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 ("tubanlaiyuan".Equals(headers[j].key))
{
objValue = item.tubanlaiyuan;
}
else if ("countyname".Equals(headers[j].key))
{
objValue = item.countyname;
}
else if ("streetname".Equals(headers[j].key))
{
objValue = item.streetname;
}
else if ("communityname".Equals(headers[j].key))
{
objValue = item.communityname;
}
else if ("typename".Equals(headers[j].key))
{
objValue = item.typename;
}
else if ("caseNo".Equals(headers[j].key))
{
objValue = item.caseNo;
}
else if ("identificationTime".Equals(headers[j].key))
{
if (item.identificationTime.IsNullOrEmpty() ||
"0001-01-01 00:00:00".Equals(item.examine_time.ToString("yyyy-MM-dd HH:mm:ss")))
{
objValue = "";
}
else
{
objValue = item.identificationTime.ToString("yyyy-MM-dd HH:mm:ss");
}
}
else if ("area".Equals(headers[j].key))
{
if (string.IsNullOrEmpty(item.area))
{
objValue = item.area;
}
else
{
objValue = double.Parse(item.area).ToString("F2");
}
var cell = dataRow.CreateCell(j);
cell.CellStyle = numericStyle;
if (objValue != null && !string.IsNullOrEmpty(objValue.ToString()))
{
cell.SetCellValue(double.Parse(objValue));
}
else
{
cell.SetCellValue(0.00d); //填充Excel单元格
}
continue;
}
else if ("gengdiArea".Equals(headers[j].key))
{
if (string.IsNullOrEmpty(item.gengdiArea))
{
objValue = item.gengdiArea;
}
else
{
objValue = double.Parse(item.gengdiArea).ToString("F2");
}
var cell = dataRow.CreateCell(j);
cell.CellStyle = numericStyle;
if (objValue != null && !string.IsNullOrEmpty(objValue.ToString()))
{
cell.SetCellValue(double.Parse(objValue));
}
else
{
cell.SetCellValue(0.00d); //填充Excel单元格
}
continue;
}
else if ("yongjiujibennongtianarea".Equals(headers[j].key))
{
if (string.IsNullOrEmpty(item.yongjiujibennongtianarea))
{
objValue = item.yongjiujibennongtianarea;
}
else
{
objValue = double.Parse(item.yongjiujibennongtianarea).ToString("F2");
}
var cell = dataRow.CreateCell(j);
cell.CellStyle = numericStyle;
if (objValue != null && !string.IsNullOrEmpty(objValue.ToString()))
{
cell.SetCellValue(double.Parse(objValue));
}
else
{
cell.SetCellValue(0.00d); //填充Excel单元格
}
continue;
}
else if ("isillegal".Equals(headers[j].key))
{
if ("其他".Equals(item.isIllegal))
{
objValue = item.isIllegal;
if ("1".Equals(item.qitaUseTo))
{
qitauseto = "不改变原用地性质的光伏用地";
}
else if ("2".Equals(item.qitaUseTo))
{
qitauseto = "设施农业用地";
}
else if ("3".Equals(item.qitaUseTo))
{
qitauseto = "农村道路";
}
else if ("4".Equals(item.qitaUseTo))
{
qitauseto = "实地未变化";
}
else if ("5".Equals(item.qitaUseTo))
{
qitauseto = "临时用地";
}
else
{
item.actualUseTo = "";
}
}
else
{
objValue = item.isIllegal;
}
}
else if ("handleStatusName".Equals(headers[j].key))
{
objValue = item.handleStatusName;
}
else if ("jieshou_people".Equals(headers[j].key))
{
objValue = item.jieshou_people;
}
else if ("jieshou_time".Equals(headers[j].key))
{
if (string.IsNullOrEmpty(item.jieshou_time))
{
objValue = item.jieshou_time;
}
else
{
DateTime dateValue2;
DateTime.TryParse(item.jieshou_time, out dateValue2);
objValue = dateValue2.ToString("yyyy-MM-dd HH:mm:ss");
}
}
else if ("xiangmumc".Equals(headers[j].key))
{
objValue = item.xiangmumc;
}
else if ("xiangmuzhuti".Equals(headers[j].key))
{
objValue = item.xiangmuzhuti;
}
else if ("actualUseTo".Equals(headers[j].key))
{
if ("其他".Equals(item.isIllegal))
{
if (string.IsNullOrEmpty(item.qitaUseTo))
{
objValue = "";
}
else
{
objValue = qitauseto;
}
}
else
{
objValue = item.actualUseTo;
}
}
else if ("weifaleixing".Equals(headers[j].key))
{
objValue = item.weifaleixing;
}
else if ("pandingyijushuoming".Equals(headers[j].key))
{
objValue = item.pandingyijushuoming;
}
else if ("examiner_name".Equals(headers[j].key))
{
objValue = item.examiner_name;
}
else if ("examine_time".Equals(headers[j].key))
{
if (item.examine_time.IsNullOrEmpty() ||
"0001-01-01 00:00:00".Equals(item.examine_time.ToString("yyyy-MM-dd HH:mm:ss")))
{
objValue = "";
}
else
{
objValue = item.examine_time.ToString("yyyy-MM-dd HH:mm:ss");
}
}
else if ("measureName".Equals(headers[j].key))
{
objValue = item.measureName;
}
else if ("transactor_name".Equals(headers[j].key))
{
objValue = item.transactor_name;
}
else if ("transact_time".Equals(headers[j].key))
{
if (item.transact_time.IsNullOrEmpty() ||
"0001-01-01 00:00:00".Equals(item.transact_time.ToString("yyyy-MM-dd HH:mm:ss")))
{
objValue = "";
}
else
{
objValue = item.transact_time.ToString("yyyy-MM-dd HH:mm:ss");
}
}
else if ("remark".Equals(headers[j].key))
{
objValue = item.remark;
}
else if ("xjshenhejieguo".Equals(headers[j].key))
{
objValue = item.xjshenhejieguo;
}
else if ("sjshenhejieguo".Equals(headers[j].key))
{
objValue = item.sjshenhejieguo;
}
else if ("xianjiyijian".Equals(headers[j].key))
{
objValue = item.xianjiyijian;
}
else if ("shijiyijian".Equals(headers[j].key))
{
objValue = item.shijiyijian;
}
else if ("verifyuser".Equals(headers[j].key))
{
objValue = item.verifyuser;
}
else if ("hexiaoren".Equals(headers[j].key))
{
objValue = item.hexiaoren;
}
else if ("verifytime".Equals(headers[j].key))
{
if (item.verifytime.IsNullOrEmpty() ||
"0001-01-01 00:00:00".Equals(item.verifytime.ToString("yyyy-MM-dd HH:mm:ss")))
{
objValue = "";
}
else
{
objValue = item.verifytime.ToString("yyyy-MM-dd HH:mm:ss");
}
}
else if ("hexiaotime".Equals(headers[j].key))
{
if (string.IsNullOrEmpty(item.hexiaotime))
{
objValue = item.hexiaotime;
}
else
{
DateTime dateValue2;
DateTime.TryParse(item.hexiaotime, out dateValue2);
objValue = dateValue2.ToString("yyyy-MM-dd HH:mm:ss");
}
}
else if ("case_description".Equals(headers[j].key))
{
objValue = item.case_description;
}
//创建单元格
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 async Task<Response<Dictionary<string, object>>> LoadMyUncompletedTask(TaskUncompletReq 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.countyid in ({orgs}) or c.streetid in ({orgs}) or c.communityid in ({orgs})) ";
}
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoSatelliteSchemeCode").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\nLEFT JOIN drone_caseinfo_wpxf c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
schemeCode + "' ";
sql =
$"select a.\"Id\" as taskId,a.\"UnitName\" as unitName,c.typename,b.\"Title\" as title,c.synchronoustime as createDate,c.case_no as caseNo,c.countyname,c.streetname,c.communityname,\r\nc.area,a.\"ProcessCode\" as processcode,a.\"ProcessId\" as processid," +
$"c.gengdi_area as gengdiArea,yongjiujibennongtian_area as yongjiujibennongtianArea ,nongyongdi_area as nongyongdiArea,c.geomid,c.\"Id\" as caseid,c.is_drawback as isDrawback \r\n from wf_task a LEFT JOIN wf_process " +
$"b on a.\"ProcessId\"=b.\"Id\" \r\nLEFT JOIN drone_caseinfo_wpxf c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
schemeCode + "' ";
sqlpart = sqlpart + $" and a.\"UnitName\" in ('待接收','待填报','待整改') and c.is_closed=0 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_wpxf 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.typename,b.\"Title\" as title,c.synchronoustime as createDate,c.case_no as caseNo,c.countyname,c.streetname,c.communityname,\r\nc.area,a.\"ProcessCode\" as processcode,a.\"ProcessId\" as processid," +
$"c.gengdi_area as gengdiArea,yongjiujibennongtian_area as yongjiujibennongtianArea ,nongyongdi_area as nongyongdiArea,c.geomid,c.\"Id\" as caseid,c.is_drawback as isDrawback \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_wpxf c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
schemeCode + "' ";
sqlpart = sqlpart + $" and a.\"UserId\"='" + user.Id + "' and c.is_closed=0 and a.\"State\"=3 ";
allCount = await client.Ado.GetIntAsync(sqlcount + sqlpart);
}
if (!string.IsNullOrEmpty(req.caseNo))
{
sqlpart = sqlpart + $" and c.case_no like '%" + req.caseNo + "%'";
}
if (!string.IsNullOrEmpty(req.typename))
{
sqlpart = sqlpart + $" and c.typename='" + req.typename + "'";
}
if (!string.IsNullOrEmpty(req.tubanArea1) && !string.IsNullOrEmpty(req.tubanArea2))
{
sqlpart = sqlpart + $" and CAST(c.area 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(c.area AS numeric)> " + double.Parse(req.tubanArea1);
}
else if (string.IsNullOrEmpty(req.tubanArea1) && !string.IsNullOrEmpty(req.tubanArea2))
{
sqlpart = sqlpart + $" and CAST(c.area AS numeric)< " + double.Parse(req.tubanArea2);
}
if (!string.IsNullOrEmpty(req.gengdiArea1) && !string.IsNullOrEmpty(req.gengdiArea2))
{
sqlpart = sqlpart + $" and CAST(c.gengdi_area AS numeric) between " + double.Parse(req.gengdiArea1) +
" and " + double.Parse(req.gengdiArea2);
}
else if (!string.IsNullOrEmpty(req.gengdiArea1) && string.IsNullOrEmpty(req.gengdiArea2))
{
sqlpart = sqlpart + $" and CAST(c.gengdi_area AS numeric)> " + double.Parse(req.gengdiArea1);
}
else if (string.IsNullOrEmpty(req.gengdiArea1) && !string.IsNullOrEmpty(req.gengdiArea2))
{
sqlpart = sqlpart + $" and CAST(c.gengdi_area AS numeric)< " + double.Parse(req.gengdiArea2);
}
if (!string.IsNullOrEmpty(req.nongyongdiArea1) && !string.IsNullOrEmpty(req.nongyongdiArea2))
{
sqlpart = sqlpart + $" and CAST(c.nongyongdi_area AS numeric) between " +
double.Parse(req.nongyongdiArea1) +
" and " + double.Parse(req.nongyongdiArea2);
}
else if (!string.IsNullOrEmpty(req.nongyongdiArea1) && string.IsNullOrEmpty(req.nongyongdiArea2))
{
sqlpart = sqlpart + $" and CAST(c.nongyongdi_area AS numeric)> " + double.Parse(req.nongyongdiArea1);
}
else if (string.IsNullOrEmpty(req.nongyongdiArea1) && !string.IsNullOrEmpty(req.nongyongdiArea2))
{
sqlpart = sqlpart + $" and CAST(c.nongyongdi_area AS numeric)< " + double.Parse(req.nongyongdiArea2);
}
if (!string.IsNullOrEmpty(req.jibenArea1) && !string.IsNullOrEmpty(req.jibenArea2))
{
sqlpart = sqlpart + $" and CAST(c.yongjiujibennongtian_area AS numeric) between " +
double.Parse(req.jibenArea1) +
" and " + double.Parse(req.jibenArea2);
}
else if (!string.IsNullOrEmpty(req.jibenArea1) && string.IsNullOrEmpty(req.jibenArea2))
{
sqlpart = sqlpart + $" and CAST(c.yongjiujibennongtian_area AS numeric)> " +
double.Parse(req.jibenArea1);
}
else if (string.IsNullOrEmpty(req.jibenArea1) && !string.IsNullOrEmpty(req.jibenArea2))
{
sqlpart = sqlpart + $" and CAST(c.yongjiujibennongtian_area AS numeric)< " + double.Parse(req.jibenArea2);
}
sqlpart += $" ORDER BY c.synchronoustime desc";
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 void UpdateDroneCaseInfo(DronCaseSingleCityUpdateReq req)
{
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
if (!string.IsNullOrEmpty(req.communityid) && !string.IsNullOrEmpty(req.communityname))
{
client.Updateable<DroneCaseInfoSatellite>().SetColumns(c => new DroneCaseInfoSatellite
{
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<DroneCaseInfoSatellite>().SetColumns(c => new DroneCaseInfoSatellite
{
countyid = req.countyid,
countyname = req.countyname,
streetid = req.streetid,
streetname = req.streetname
}).Where(a => a.Id == req.Id).ExecuteCommand();
}
}
/// <summary>
/// 案件分宗/图斑分割/案件分割
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public async Task<Response<string>> CaseSplit(CaseSplitReq req)
{
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
// 案件分宗 1. 关闭案件 2.撤消流程 3. 新建案件流程（生成关联图斑记录） 4. 生成拆分案件及拆分前案件关联关系
using var db = Repository.AsSugarClient();
try
{
await db.Ado.BeginTranAsync(IsolationLevel.ReadCommitted);
var original = await db.Queryable<DroneCaseInfoSatellite>().FirstAsync(t => t.Id == req.caseid);
var originalCaseNo = original.case_no;
if (original == null)
{
throw new Exception("图斑不存在");
}
var wktReader = new WKTReader();
foreach (var part in req.parts)
{
var geometry = wktReader.Read(part.geom);
}
// 根据案件id取得流程
var process = await Repository.ChangeRepository<SugarRepositiry<WFProcess>>().AsQueryable()
.FirstAsync(t => t.Id == req.processid);
// 作废流程
var result = await _processApp.DeleteProcess(process.Id);
// 生成拆分案件
var caseList = new List<DroneCaseInfoSatellite>(req.parts.Count);
var relationships = new List<DroneCaseSplit>(req.parts.Count);
var i = 1;
foreach (var casePart in req.parts)
{
var caseInfo = original.MapTo<DroneCaseInfoSatellite>();
var caseId = Guid.NewGuid().ToString();
caseInfo.Id = caseId;
caseInfo.case_no = originalCaseNo + "-" + (i++);
caseInfo.nongyongdi_area = casePart.nongyongdi_area;
caseInfo.gengdi_area = casePart.gengdi_area;
caseInfo.yongjiujibennongtian_area = casePart.yongjiujibennongtian_area;
caseInfo.shengtaibaohuhongxian_area = casePart.shengtaibaohuhongxian_area;
caseInfo.guotukongjianguihua_area = casePart.guotukongjianguihua_area;
caseInfo.zhongdianquyu_area = casePart.zhongdianquyu_area;
caseInfo.area = casePart.area;
// 设置时间
caseInfo.createtime = DateTime.Now;
// 设置创建人
caseInfo.createuser = user.Id + "";
caseInfo.createusername = user.Name;
// todo 去掉某些属性值
// 填充图斑信息
var geometry = wktReader.Read(casePart.geom);
var gidStr = _commonDataManager.GetMaxKeyVal("gid", "drone_shp_data", 1);
var insertObj = new Dictionary<string, object>
{
{ "gid", gidStr.ToInt() },
{ "geom", geometry.AsBinary() },
{"relid", caseId}
};
// 插入图斑
await db.Insertable(insertObj).AS("drone_shp_data").ExecuteCommandAsync();
caseInfo.geomid = gidStr;
caseList.Add(caseInfo);
// 原始案件与拆分案件关联
var splitRelation = new DroneCaseSplit();
splitRelation.NewCaseId = caseId;
splitRelation.OriginalCaseId = req.caseid;
relationships.Add(splitRelation);
}
await db.Insertable(caseList).ExecuteCommandAsync();
//新案件与原始案件关联
await db.Insertable(relationships).ExecuteCommandAsync();
// 发起新流程
foreach (var caseInfo in caseList)
{
var instanceInfo = process.InstanceInfo;
instanceInfo.pkeyValue = caseInfo.Id;
await _processApp.SaveDraft(caseInfo.Id, process.SchemeCode, user.Id + "",
Json.ToJson(instanceInfo), process.ParentProcessId, process.ParentNodeId, process.ParentTaskId, 0,
caseInfo.case_description);
await _processApp.Create(caseInfo.Id, "", user.Id + "", null, Json.ToJson(instanceInfo),
caseInfo.case_description);
}
// 关闭案件
var record = new DroneCaseInfoSatellite
{
Id = req.caseid,
is_closed = 1,
is_split = 1,
close_userid = user.Id + "",
close_user = user.Name,
close_time = DateTime.Now
};
await db.Updateable(record).IgnoreNullColumns().ExecuteCommandAsync();
await db.Ado.CommitTranAsync();
return new Response<string>
{
Result = "拆分成功",
Message = "拆分成功"
};
}
catch (Exception e)
{
await db.Ado.RollbackTranAsync();
throw e;
}
}
public async Task<Response<string>> CaseRecover(CaseRecoverReq req)
{
var authStrategyContext = _auth.GetCurrentUser();
var user = authStrategyContext.User;
using var db = Repository.AsSugarClient();
var relationships = new List<DroneCaseSplit>(req.parts.Count);
var caseId = Guid.NewGuid().ToString();
try
{
await db.Ado.BeginTranAsync(IsolationLevel.ReadCommitted);
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<DroneCaseInfoSatellite>().SetColumns(c => new DroneCaseInfoSatellite
{
is_closed = 0,
is_split = 2
}).Where(a => a.Id == caseId).ExecuteCommandAsync();
foreach (var casePart in oldcaseids1)
{
var original = await db.Queryable<DroneCaseInfoSatellite>().FirstAsync(t => t.Id == casePart);
// 关闭案件
var record = new DroneCaseInfoSatellite
{
Id = casePart,
is_closed = 1
};
await db.Updateable(record).ExecuteCommandAsync();
/* var splitRelation = new DroneCaseSplit();
splitRelation.NewCaseId = caseId;
splitRelation.OriginalCaseId = casePart.caseid;
relationships.Add(splitRelation);*/
await db.Updateable<DroneCaseSplit>().SetColumns(c => new DroneCaseSplit
{
IsRecover = 1
}).Where(a => a.NewCaseId == casePart).ExecuteCommandAsync();
}
await db.Ado.CommitTranAsync();
return new Response<string>
{
Result = "还原成功",
Message = "还原成功"
};
}
else
{
foreach (var casePart in req.parts)
{
var original = await db.Queryable<DroneCaseInfoSatellite>()
.FirstAsync(t => t.Id == casePart.caseid);
// 关闭案件
var record = new DroneCaseInfoSatellite
{
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<DroneCaseInfoSatellite>();
var original1 = await db.Queryable<DroneCaseInfoSatellite>()
.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<DroneCaseInfoSatellite>();
caseInfo.Id = caseId;
caseInfo.nongyongdi_area = req.nongyongdi_area;
caseInfo.gengdi_area = req.gengdi_area;
caseInfo.yongjiujibennongtian_area = req.yongjiujibennongtian_area;
caseInfo.shengtaibaohuhongxian_area = req.shengtaibaohuhongxian_area;
caseInfo.guotukongjianguihua_area = req.guotukongjianguihua_area;
caseInfo.zhongdianquyu_area = req.zhongdianquyu_area;
caseInfo.area = req.area;
// 设置时间
caseInfo.createtime = DateTime.Now;
// 设置创建人
caseInfo.createuser = user.Id + "";
caseInfo.createusername = user.Name;
// 填充图斑信息
var geometry = wktReader.Read(req.geom);
var gidStr = _commonDataManager.GetMaxKeyVal("gid", "drone_shp_data", 1);
var insertObj = new Dictionary<string, object>
{
{ "gid", gidStr.ToInt() },
{ "geom", geometry.AsBinary() }
};
// 插入图斑
await db.Insertable(insertObj).AS("drone_shp_data").ExecuteCommandAsync();
caseInfo.geomid = gidStr;
caseList.Add(caseInfo);
await db.Insertable(caseList).ExecuteCommandAsync();
// 发起新流程
var processId = Guid.NewGuid().ToString();
var instanceInfo = process.InstanceInfo;
instanceInfo.pkeyValue = caseInfo.Id;
string x = Json.ToJson(instanceInfo);
Console.WriteLine("instance: " + x);
await _processApp.SaveDraft(caseInfo.Id, process.SchemeCode, user.Id + "",
Json.ToJson(instanceInfo), process.ParentProcessId, process.ParentNodeId, process.ParentTaskId, 0,
caseInfo.case_description);
await _processApp.Create(caseInfo.Id, process.SchemeCode, user.Id + "", null, Json.ToJson(instanceInfo),
caseInfo.case_description);
await db.Ado.CommitTranAsync();
return new Response<string>
{
Result = "拆分成功",
Message = "拆分成功"
};
}
}
catch (Exception e)
{
await db.Ado.RollbackTranAsync();
throw e;
}
}
#region 案件关闭--矿产卫片下发
/// <summary>
/// 关闭案件--矿产卫片下发
/// </summary>
/// <param name="id">案件id</param>
/// <returns></returns>
public async Task<Response<bool>> CloseCaseInfoSatellite(string id, string closereason)
{
var user = _auth.GetCurrentUser().User;
var info = client.Queryable<DroneCaseInfoSatellite>().Where(r => r.Id == id).First();
using (var uow = base.UnitWork.CreateContext())
{
await uow.DroneCaseInfoSatellite.UpdateAsync(r => new DroneCaseInfoSatellite()
{
is_closed = 1,
is_intact = 99,
close_time = DateTime.Now,
close_user = user.Name,
close_userid = user.Id.ToString(),
close_reason = closereason
}, r => r.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, Infrastructure.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 = "关闭失败"
};
}
}
}
#endregion
#region 案件审核--卫片下发
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.case_no,case_description,typename,countyname,streetname,communityname,identification_user,identification_time,createtime,c.original_case_no,is_closed," +
$"b.\"Id\" as processid from drone_caseinfo_wpxf 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.wfydwp + "' where 1=1 ";
if (!string.IsNullOrEmpty(req.typename))
{
sqlpart = sqlpart + $" and typename='" + req.typename + "'";
}
if (!string.IsNullOrEmpty(req.countyid))
{
sqlpart = sqlpart + $" and countyid='" + req.countyid + "'";
}
if (!string.IsNullOrEmpty(req.streetid))
{
sqlpart = sqlpart + $" and streetid='" + req.streetid + "'";
}
if (!string.IsNullOrEmpty(req.communityid))
{
sqlpart = sqlpart + $" and communityid='" + 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.case_no like '%" + req.caseNo + "%'";
}
if (!string.IsNullOrEmpty(req.originalCaseNo))
{
sqlpart = sqlpart + $" and c.original_case_no like '%" + req.originalCaseNo + "%'";
}
sqlpart += $" ORDER BY createtime 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
}
Reference in New Issue View Git Blame Copy Permalink