3584 lines
165 KiB
C#
3584 lines
165 KiB
C#
using OpenAuth.App.BaseApp.Base;
|
||
using OpenAuth.Repository.Domain;
|
||
using OpenAuth.Repository;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Threading.Tasks;
|
||
using OpenAuth.App.BaseApp.Shape;
|
||
using SqlSugar;
|
||
using OpenAuth.App.Interface;
|
||
using Microsoft.Extensions.Configuration;
|
||
using Infrastructure;
|
||
using OpenAuth.App.ServiceApp.DroneCaseInfo.Request;
|
||
using OpenAuth.App.ServiceApp.DroneCaseInfo.Response;
|
||
using System.Dynamic;
|
||
using Microsoft.AspNetCore.Mvc;
|
||
using NetTopologySuite.IO;
|
||
using OpenAuth.App.BasicQueryService;
|
||
using System.Data;
|
||
using Infrastructure.Extensions;
|
||
using NetTopologySuite.Features;
|
||
using OpenAuth.App.ServiceApp.Response;
|
||
using NetTopologySuite.Geometries;
|
||
using System.IO.Compression;
|
||
using Shapefile = NetTopologySuite.IO.Esri.Shapefile;
|
||
using NPOI.HSSF.UserModel;
|
||
using NPOI.SS.UserModel;
|
||
using OpenAuth.App.FormModule;
|
||
using OpenAuth.App.Common;
|
||
using OpenAuth.App.Request;
|
||
using Newtonsoft.Json.Linq;
|
||
using Newtonsoft.Json;
|
||
using Infrastructure.Helpers;
|
||
using NPOI.Util;
|
||
|
||
namespace OpenAuth.App.ServiceApp.DroneCaseInfo
|
||
{
|
||
public class DroneCaseInfoSTHXApp : SqlSugarBaseApp<DroneCaseInfoSTHX, SugarDbContext>
|
||
{
|
||
private readonly IConfiguration _configuration;
|
||
private readonly ISqlSugarClient client;
|
||
private readonly WFProcessApp _processApp;
|
||
private readonly ShpLayerSourceApp _shpLayerSourceApp;
|
||
private readonly WFSchemeInfoApp _wfSchemeInfoApp;
|
||
CommonDataManager _commonDataManager;
|
||
public DroneCaseInfoSTHXApp(ISugarUnitOfWork<SugarDbContext> unitWork,
|
||
ISimpleClient<DroneCaseInfoSTHX> repository, IAuth auth, ISqlSugarClient sqlSugarClient,
|
||
IConfiguration configuration, WFProcessApp processApp, ShpLayerSourceApp shpLayerSourceApp,
|
||
WFSchemeInfoApp wfSchemeInfoApp, CommonDataManager commonDataManager) : base(unitWork, repository, auth)
|
||
{
|
||
_configuration = configuration;
|
||
_processApp = processApp;
|
||
_shpLayerSourceApp = shpLayerSourceApp;
|
||
this.client = sqlSugarClient;
|
||
_processApp = processApp;
|
||
_wfSchemeInfoApp = wfSchemeInfoApp;
|
||
_auth = auth;
|
||
_commonDataManager = commonDataManager;
|
||
}
|
||
|
||
#region 接收办理
|
||
|
||
/// <summary>
|
||
/// 接收办理/图斑调整
|
||
/// </summary>
|
||
/// <param name="req"></param>
|
||
/// <returns></returns>
|
||
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.countyid in ({orgs}) or c.streetid in ({orgs}) or c.communityid in ({orgs})) ";
|
||
}
|
||
|
||
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoSTHXSchemeCode").Value;
|
||
string sql = "";
|
||
if (req.type == 0)
|
||
{
|
||
sql =
|
||
$"select c.is_drawback,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 \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_sthx c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
|
||
schemeCode + "' ";
|
||
}
|
||
else
|
||
{
|
||
sql =
|
||
$"select c.is_drawback,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 \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_sthx c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
|
||
schemeCode + "' ";
|
||
}
|
||
|
||
|
||
if (req.isIllegal < 100)
|
||
{
|
||
sqlpart = sqlpart + $" and c.is_illegal=" + req.isIllegal + "";
|
||
}
|
||
|
||
if (req.weifaleixing < 100)
|
||
{
|
||
sqlpart = sqlpart + $" and c.weifaleixing=" + req.weifaleixing + "";
|
||
}
|
||
////查询县区可看的数据
|
||
//if (level != 0)
|
||
//{
|
||
// sqlpart = sqlpart + $" and c.countyusable = 1";
|
||
//}
|
||
|
||
if (!string.IsNullOrEmpty(req.measureName))
|
||
{
|
||
sqlpart = sqlpart + $" and c.measure_name='" + req.measureName + "'";
|
||
}
|
||
|
||
sqlpart = sqlpart + $" and a.\"UnitName\" in ('待填报','待接收','待整改')";
|
||
// 任务状态 1-激活状态 3-
|
||
sqlpart = sqlpart + "and (a.\"State\"=1 or (a.\"State\"= 7 and c.is_split = 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.display == 1)
|
||
{
|
||
sqlpart = sqlpart + " and (c.is_closed = 0 or (c.is_closed = 1 and c.is_split = 1))";
|
||
}
|
||
else
|
||
{
|
||
sqlpart = sqlpart + " and c.is_closed=0";
|
||
}
|
||
|
||
if (req.year != 0)
|
||
{
|
||
sqlpart = sqlpart + $" and EXTRACT(YEAR FROM c.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(COALESCE(NULLIF(" + req.sortType +
|
||
$", '')::numeric, 0) 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
|
||
}
|
||
};
|
||
}
|
||
|
||
/// <summary>
|
||
/// 添加收藏
|
||
/// </summary>
|
||
/// <param name="model"></param>
|
||
/// <returns></returns>
|
||
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 = "收藏失败"
|
||
};
|
||
}
|
||
|
||
/// <summary>
|
||
/// 删除收藏
|
||
/// </summary>
|
||
/// <param name="id"></param>
|
||
/// <returns></returns>
|
||
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 = "删除失败"
|
||
};
|
||
}
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 填报审核
|
||
|
||
/// <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>>> LoadTaskCount(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:DroneCaseInfoSTHXSchemeCode").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<DroneCaseInfoSTHX>((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) => p.SchemeCode.Equals(schemeCode))
|
||
.Where((t, p, c) => c.is_closed.Equals(0))
|
||
.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<DroneCaseInfoSTHX>()
|
||
.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,
|
||
issplit = s.is_split,
|
||
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
|
||
.Where(t => t.isclosed.Equals(0) || t.issplit.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)) // 整改审核 */
|
||
.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.illegal = grouping.list.Where(t =>
|
||
t.isillegal != null && t.isillegal.Equals(1) && 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<DroneCaseInfoSTHX>()
|
||
.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,
|
||
isclosed = s.is_closed,
|
||
issplit = s.is_split,
|
||
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.isclosed.Equals(0) || t.issplit.Equals(1))
|
||
.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.illegal = grouping.list.Where(t =>
|
||
t.isillegal != null && t.isillegal.Equals(1) && 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:DroneCaseInfoSTHXSchemeCode").Value;
|
||
// 查询数据
|
||
var dateTime = req.year == 0 ? DateTime.Now : new DateTime(req.year, 1, 1);
|
||
int totalCount = 0;
|
||
using (var db = Repository.AsSugarClient())
|
||
{
|
||
var info = await db.Queryable<WFTask>()
|
||
.LeftJoin<WFProcess>((t, p) => t.ProcessId == p.Id && p.SchemeCode.Equals(schemeCode))
|
||
.LeftJoin<DroneCaseInfoSTHX>((t, p, c) => p.Id == 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.is_closed.Equals(0) || c.is_split.Equals(1))
|
||
//.Where((t, p, c, f) => p.SchemeCode.Equals(schemeCode))
|
||
.WhereIF(!string.IsNullOrEmpty(req.tubanlaiyuan),
|
||
(t, p, c, f) => c.tubanlaiyuan.Equals(req.tubanlaiyuan))
|
||
.WhereIF(!string.IsNullOrEmpty(req.picihao), (t, p, c, f) => c.picihao.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(isCity == 0, (t, p, c, f) => c.countyusable.Equals(1))
|
||
// 图斑编号即案件编号
|
||
.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)))*/
|
||
// 标注类型-> 合法 其他 非粮化 拆除复耕 补办手续
|
||
.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.is_illegal.Equals(1))
|
||
.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);
|
||
return new Response<PageInfo<List<dynamic>>>
|
||
{
|
||
Result = new PageInfo<List<dynamic>>
|
||
{
|
||
Items = info,
|
||
Total = totalCount
|
||
}
|
||
};
|
||
}
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 图斑调整
|
||
|
||
/// <summary>
|
||
/// 案件还原合并
|
||
/// </summary>
|
||
/// <param name="req"></param>
|
||
/// <returns></returns>
|
||
/// <exception cref="Exception"></exception>
|
||
public async Task<Response<string>> CaseRecover(CaseRecoverReq req)
|
||
{
|
||
var authStrategyContext = _auth.GetCurrentUser();
|
||
var user = authStrategyContext.User;
|
||
using var db = Repository.AsSugarClient();
|
||
|
||
var relationships = new List<DroneCaseSplit>(req.parts.Count);
|
||
var caseId = Guid.NewGuid().ToString();
|
||
try
|
||
{
|
||
await db.Ado.BeginTranAsync(IsolationLevel.ReadCommitted);
|
||
var subCaseId = req.caseid;
|
||
// 这里是验证当前选中的图斑，是否有被分割，但当和它一起被分割出来的图斑被分割时，还原该图斑会有问题
|
||
// 以下代码校验指定图斑是否被分割过
|
||
string sql5 =
|
||
$"select new_case_id from drone_case_split where original_case_id='{subCaseId}' and is_recover=0";
|
||
var caseids1 = await db.Ado.SqlQueryAsync<string>(sql5);
|
||
if (caseids1.Count > 0)
|
||
{
|
||
return new Response<string>
|
||
{
|
||
Result = "当前图斑存在分割的子图斑不允许还原",
|
||
Message = "还原失败"
|
||
};
|
||
}
|
||
|
||
var sql6 = @"SELECT
|
||
count(*)
|
||
FROM
|
||
drone_case_split s join drone_caseinfo_sthx c on s.new_case_id = c.""Id"" and c.is_split = '1'
|
||
WHERE
|
||
s.original_case_id = (select original_case_id from drone_case_split where new_case_id = @x)
|
||
AND s.new_case_id != @x
|
||
AND s.is_recover = '0'";
|
||
var count = await db.Ado.SqlQuerySingleAsync<int>(sql6, new { x = subCaseId });
|
||
if (count > 0)
|
||
{
|
||
return new Response<string>
|
||
{
|
||
Result = "当前图斑一起分割的图斑存在分割的子图斑",
|
||
Message = "还原失败"
|
||
};
|
||
}
|
||
|
||
if (req.type == 0) // 目前此值必定为0
|
||
{
|
||
var sql =
|
||
$"select original_case_id from drone_case_split where new_case_id='{subCaseId}' and is_recover=0";
|
||
// 原始图斑
|
||
var oldCaseId = await db.Ado.SqlQuerySingleAsync<string>(sql);
|
||
|
||
if (string.IsNullOrEmpty(oldCaseId))
|
||
{
|
||
return new Response<string>
|
||
{
|
||
Result = "图斑未拆",
|
||
Message = "还原失败"
|
||
};
|
||
}
|
||
|
||
// 恢复还原图斑
|
||
await _processApp.RecoverProcess(oldCaseId);
|
||
// 还原案件
|
||
await db.Updateable<DroneCaseInfoSTHX>()
|
||
.SetColumns(c =>
|
||
new DroneCaseInfoSTHX
|
||
{
|
||
is_closed = 0,
|
||
is_split = 2
|
||
}).Where(a => a.Id == oldCaseId).ExecuteCommandAsync();
|
||
// 处理子图斑
|
||
// 查询子图斑
|
||
var subCaseQuery =
|
||
$"select new_case_id from drone_case_split where is_recover=0 and original_case_id = '{oldCaseId}'";
|
||
var newCaseIds = db.Ado.SqlQuery<string>(subCaseQuery);
|
||
foreach (var casePart in newCaseIds)
|
||
{
|
||
// 作废流程
|
||
var result = await _processApp.DeleteProcess(casePart);
|
||
// todo 做意外处理，当执行失败时，
|
||
await db.Updateable<DroneCaseSplit>().SetColumns(c => new DroneCaseSplit
|
||
{
|
||
IsRecover = 1
|
||
}).Where(a => a.NewCaseId == casePart).ExecuteCommandAsync();
|
||
// 关闭
|
||
await db.Updateable<DroneCaseInfoSTHX>().SetColumns(c => new DroneCaseInfoSTHX
|
||
{
|
||
is_closed = 1
|
||
}).Where(a => a.Id == casePart).ExecuteCommandAsync();
|
||
}
|
||
|
||
await db.Ado.CommitTranAsync();
|
||
return new Response<string>
|
||
{
|
||
Result = "还原成功",
|
||
Message = "还原成功"
|
||
};
|
||
}
|
||
else
|
||
{
|
||
foreach (var casePart in req.parts)
|
||
{
|
||
var original = await db.Queryable<DroneCaseInfoSTHX>().FirstAsync(t => t.Id == casePart.caseid);
|
||
// 关闭案件
|
||
var record = new DroneCaseInfoSTHX
|
||
{
|
||
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<DroneCaseInfoSTHX>();
|
||
var original1 =
|
||
await db.Queryable<DroneCaseInfoSTHX>().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<DroneCaseInfoSTHX>();
|
||
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 new Exception(e.Message);
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 案件分宗
|
||
/// </summary>
|
||
/// <param name="req"></param>
|
||
/// <returns></returns>
|
||
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<DroneCaseInfoSTHX>().FirstAsync(t => t.Id == req.caseid);
|
||
var oriinalCaseNo = original.case_no;
|
||
if (original == null)
|
||
{
|
||
throw new Exception("图斑不存在");
|
||
}
|
||
|
||
var wktReader = new WKTReader();
|
||
foreach (var part in req.parts)
|
||
{
|
||
var geometry = wktReader.Read(part.geom);
|
||
}
|
||
|
||
// 根据案件id取得流程
|
||
var process = await Repository.ChangeRepository<SugarRepositiry<WFProcess>>().AsQueryable()
|
||
.FirstAsync(t => t.Id == req.processid);
|
||
// 作废流程
|
||
var result = await _processApp.DeleteProcess(process.Id);
|
||
// 生成拆分案件
|
||
var caseList = new List<DroneCaseInfoSTHX>(req.parts.Count);
|
||
var relationships = new List<DroneCaseSplit>(req.parts.Count);
|
||
|
||
|
||
var imageRefs = await Repository.ChangeRepository<SugarRepositiry<DroneImageRef>>().AsQueryable()
|
||
.Where(t => t.CaseId == req.caseid)
|
||
.Select(t => new DroneImageRef
|
||
{
|
||
FilePath = t.FilePath,
|
||
Orientation = t.Orientation,
|
||
Lng = t.Lng,
|
||
Lat = t.Lat
|
||
})
|
||
.ToListAsync();
|
||
using var httpClient = new HttpClient();
|
||
// http://192.168.10.125:9011/DroneEnforcement/2024/20241112/Image_Water_1731054661277.jpeg
|
||
var config = ConfigHelper.GetConfigRoot();
|
||
var baseUrl = config["ImgServer"];
|
||
var url = baseUrl + "/api/Platform/NewCopyImg";
|
||
// 文件路径 字段
|
||
var files = new Dictionary<string, string>();
|
||
// 方向角图片
|
||
var images = "";
|
||
if (!string.IsNullOrEmpty(original.anjianzhaopian))
|
||
{
|
||
var temp = original.anjianzhaopian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "anjianzhaopian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
// 拆除复耕
|
||
if (!string.IsNullOrEmpty(original.chaichufugenghoupic))
|
||
{
|
||
var temp = original.chaichufugenghoupic.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "chaichufugenghoupic");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
// 补办照片
|
||
if (!string.IsNullOrEmpty(original.bubanzhaopian))
|
||
{
|
||
var temp = original.bubanzhaopian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "bubanzhaopian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
// 图斑图片
|
||
if (!string.IsNullOrEmpty(original.casepic))
|
||
{
|
||
var temp = original.casepic.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "casepic");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
if (!string.IsNullOrEmpty(original.fujian))
|
||
{
|
||
var temp = original.fujian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "fujian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
// 整改附件
|
||
if (!string.IsNullOrEmpty(original.zhenggaifujian))
|
||
{
|
||
var temp = original.zhenggaifujian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "zhenggaifujian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
// 合法附件
|
||
if (!string.IsNullOrEmpty(original.hefafujian))
|
||
{
|
||
var temp = original.hefafujian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "hefafujian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
// 其它附近
|
||
if (!string.IsNullOrEmpty(original.qitafujian))
|
||
{
|
||
var temp = original.qitafujian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "qitafujian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
// 无人机复飞
|
||
if (!string.IsNullOrEmpty(original.wrjffzhaopian))
|
||
{
|
||
var temp = original.wrjffzhaopian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "wrjffzhaopian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
//现场核查照片
|
||
if (!string.IsNullOrEmpty(original.xchczhaopian))
|
||
{
|
||
var temp = original.xchczhaopian.Trim(',');
|
||
var imgArray = temp.Split(",");
|
||
foreach (var se in imgArray)
|
||
{
|
||
files.Add(se, "xchczhaopian");
|
||
}
|
||
|
||
images += "," + temp;
|
||
}
|
||
|
||
|
||
images = images.Trim(',');
|
||
var param = new Dictionary<string, string>()
|
||
{
|
||
{ "path", images }
|
||
};
|
||
var content = new FormUrlEncodedContent(param);
|
||
var newImgRefs = new List<DroneImageRef>(imageRefs.Count * 2);
|
||
|
||
var i = 1;
|
||
foreach (var casePart in req.parts)
|
||
{
|
||
var caseInfo = original.MapTo<DroneCaseInfoSTHX>();
|
||
var caseId = Guid.NewGuid().ToString();
|
||
caseInfo.Id = caseId;
|
||
caseInfo.case_no = oriinalCaseNo + "-" + (i++);
|
||
caseInfo.nongyongdi_area = casePart.nongyongdi_area;
|
||
caseInfo.gengdi_area = casePart.gengdi_area;
|
||
caseInfo.yongjiujibennongtian_area = casePart.yongjiujibennongtian_area;
|
||
caseInfo.shengtaibaohuhongxian_area = casePart.shengtaibaohuhongxian_area;
|
||
caseInfo.guotukongjianguihua_area = casePart.guotukongjianguihua_area;
|
||
caseInfo.zhongdianquyu_area = casePart.zhongdianquyu_area;
|
||
caseInfo.area = casePart.area;
|
||
// 设置时间
|
||
caseInfo.createtime = DateTime.Now;
|
||
// 设置创建人
|
||
caseInfo.createuser = user.Id + "";
|
||
caseInfo.createusername = user.Name;
|
||
|
||
// 清理数据
|
||
caseInfo.anjianzhaopian = null;
|
||
caseInfo.chaichufugenghoupic = null;
|
||
caseInfo.bubanzhaopian = null;
|
||
caseInfo.casepic = null;
|
||
caseInfo.fujian = null;
|
||
caseInfo.zhenggaifujian = null;
|
||
caseInfo.hefafujian = null;
|
||
caseInfo.qitafujian = null;
|
||
caseInfo.wrjffzhaopian = null;
|
||
caseInfo.xchczhaopian = null;
|
||
|
||
// 复制图片
|
||
if (!string.IsNullOrEmpty(images))
|
||
{
|
||
var copyFileResponse = await httpClient.PostAsync(url, content);
|
||
if (copyFileResponse.IsSuccessStatusCode)
|
||
{
|
||
var resultContent = await copyFileResponse.Content.ReadAsStringAsync();
|
||
var obj = JsonConvert.DeserializeObject<Response<List<SysUploadFileNew>>>(resultContent);
|
||
if (!obj.Code.Equals(200))
|
||
{
|
||
throw new Exception("复制文件失败");
|
||
}
|
||
|
||
foreach (var sysUploadFileNew in obj.Result)
|
||
{
|
||
var originalPath = sysUploadFileNew.OriginalPath;
|
||
var newFilePath = sysUploadFileNew.FilePath;
|
||
var imgRef = imageRefs.FirstOrDefault(x => x.FilePath.Equals(originalPath));
|
||
if (imgRef != null)
|
||
{
|
||
// 填充信息
|
||
var temp = imgRef.Copy();
|
||
temp.Id = Guid.NewGuid().ToString();
|
||
temp.CaseId = caseInfo.Id;
|
||
temp.CreateTime = DateTime.Now;
|
||
temp.FilePath = newFilePath;
|
||
newImgRefs.Add(temp);
|
||
}
|
||
|
||
var field = files[originalPath];
|
||
switch (field)
|
||
{
|
||
case "fujian":
|
||
if (string.IsNullOrEmpty(caseInfo.fujian))
|
||
{
|
||
caseInfo.fujian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.fujian = caseInfo.fujian + "," + newFilePath;
|
||
}
|
||
break;
|
||
case "anjianzhaopian":
|
||
if (string.IsNullOrEmpty(caseInfo.anjianzhaopian))
|
||
{
|
||
caseInfo.anjianzhaopian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.anjianzhaopian = caseInfo.anjianzhaopian + "," + newFilePath;
|
||
}
|
||
break;
|
||
case "chaichufugenghoupic":
|
||
if (string.IsNullOrEmpty(caseInfo.chaichufugenghoupic))
|
||
{
|
||
caseInfo.chaichufugenghoupic = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.chaichufugenghoupic =
|
||
caseInfo.chaichufugenghoupic + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "bubanzhaopian":
|
||
if (string.IsNullOrEmpty(caseInfo.bubanzhaopian))
|
||
{
|
||
caseInfo.bubanzhaopian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.bubanzhaopian = caseInfo.bubanzhaopian + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "casepic":
|
||
if (string.IsNullOrEmpty(caseInfo.casepic))
|
||
{
|
||
caseInfo.casepic = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.casepic = caseInfo.casepic + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "zhenggaifujian":
|
||
if (string.IsNullOrEmpty(caseInfo.zhenggaifujian))
|
||
{
|
||
caseInfo.zhenggaifujian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.zhenggaifujian = caseInfo.zhenggaifujian + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "hefafujian":
|
||
if (string.IsNullOrEmpty(caseInfo.hefafujian))
|
||
{
|
||
caseInfo.hefafujian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.hefafujian = caseInfo.hefafujian + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "qitafujian":
|
||
if (string.IsNullOrEmpty(caseInfo.qitafujian))
|
||
{
|
||
caseInfo.qitafujian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.qitafujian = caseInfo.qitafujian + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "wrjffzhaopian":
|
||
if (string.IsNullOrEmpty(caseInfo.wrjffzhaopian))
|
||
{
|
||
caseInfo.wrjffzhaopian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.wrjffzhaopian = caseInfo.wrjffzhaopian + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
case "xchczhaopian":
|
||
if (string.IsNullOrEmpty(caseInfo.xchczhaopian))
|
||
{
|
||
caseInfo.xchczhaopian = newFilePath;
|
||
}
|
||
else
|
||
{
|
||
caseInfo.xchczhaopian = caseInfo.xchczhaopian + "," + newFilePath;
|
||
}
|
||
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
|
||
else
|
||
{
|
||
throw new Exception("复制文件失败");
|
||
}
|
||
}
|
||
|
||
|
||
|
||
// 填充图斑信息
|
||
var geometry = wktReader.Read(casePart.geom);
|
||
var gidStr = _commonDataManager.GetMaxKeyVal("gid", "drone_shp_data", 1);
|
||
var insertObj = new Dictionary<string, object>
|
||
{
|
||
{ "gid", gidStr.ToInt() },
|
||
{ "geom", geometry.AsBinary() },
|
||
{ "relid", caseId }
|
||
};
|
||
// 插入图斑
|
||
await db.Insertable(insertObj).AS("drone_shp_data").ExecuteCommandAsync();
|
||
caseInfo.geomid = gidStr;
|
||
caseList.Add(caseInfo);
|
||
// 原始案件与拆分案件关联
|
||
var splitRelation = new DroneCaseSplit();
|
||
splitRelation.NewCaseId = caseId;
|
||
splitRelation.OriginalCaseId = req.caseid;
|
||
relationships.Add(splitRelation);
|
||
}
|
||
|
||
if (newImgRefs.Count > 0)
|
||
{
|
||
await db.Insertable(newImgRefs).ExecuteCommandAsync();
|
||
}
|
||
await db.Insertable(caseList).ExecuteCommandAsync();
|
||
//新案件与原始案件关联
|
||
await db.Insertable(relationships).ExecuteCommandAsync();
|
||
|
||
// 发起新流程
|
||
foreach (var caseInfo in caseList)
|
||
{
|
||
var instanceInfo = process.InstanceInfo;
|
||
instanceInfo.pkeyValue = caseInfo.Id;
|
||
await _processApp.SaveDraft(caseInfo.Id, process.SchemeCode, user.Id + "",
|
||
Json.ToJson(instanceInfo), process.ParentProcessId, process.ParentNodeId, process.ParentTaskId,
|
||
0,
|
||
caseInfo.case_description);
|
||
await _processApp.Create(caseInfo.Id, "", user.Id + "", null, Json.ToJson(instanceInfo),
|
||
caseInfo.case_description);
|
||
}
|
||
|
||
// 关闭案件
|
||
var record = new DroneCaseInfoSTHX
|
||
{
|
||
Id = req.caseid,
|
||
is_closed = 1,
|
||
is_split = 1,
|
||
close_userid = user.Id + "",
|
||
close_user = user.Name,
|
||
close_time = DateTime.Now
|
||
};
|
||
Console.WriteLine("运行前==========================================");
|
||
var x = await db.Updateable(record).IgnoreNullColumns().ExecuteCommandAsync();
|
||
Console.WriteLine("运行后============================================ " + x);
|
||
await db.Ado.CommitTranAsync();
|
||
return new Response<string>
|
||
{
|
||
Result = "拆分成功",
|
||
Message = "拆分成功"
|
||
};
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
await db.Ado.RollbackTranAsync();
|
||
throw new Exception(e.Message);
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 更新案件行政区划
|
||
/// </summary>
|
||
/// <param name="req"></param>
|
||
public void UpdateDroneCaseInfo(DronCaseSingleCityUpdateReq req)
|
||
{
|
||
var authStrategyContext = _auth.GetCurrentUser();
|
||
var user = authStrategyContext.User;
|
||
if (!string.IsNullOrEmpty(req.communityid) && !string.IsNullOrEmpty(req.communityname))
|
||
{
|
||
client.Updateable<DroneCaseInfoSTHX>().SetColumns(c => new DroneCaseInfoSTHX
|
||
{
|
||
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<DroneCaseInfoSTHX>().SetColumns(c => new DroneCaseInfoSTHX
|
||
{
|
||
countyid = req.countyid,
|
||
countyname = req.countyname,
|
||
streetid = req.streetid,
|
||
streetname = req.streetname
|
||
}).Where(a => a.Id == req.Id).ExecuteCommand();
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取图片信息
|
||
/// </summary>
|
||
/// <param name="caseid"></param>
|
||
/// <returns></returns>
|
||
public async Task<Response<List<DroneImageRef>>> LoadCaseImgList(string caseid, string category)
|
||
{
|
||
List<string> imageList = new List<string>();
|
||
RefAsync<int> totalcount = 0;
|
||
switch (category)
|
||
{
|
||
|
||
case "违法用地":
|
||
//查询案件的填报(anjianzhaopian)和整改图片信息(chaichufugenghoupic),补办手续（bubanzhaopian）
|
||
var caseImg1 = await client.Queryable<DroneCaseInfoSingle>().FirstAsync(r => r.Id == caseid);
|
||
|
||
if (caseImg1 != null)
|
||
{
|
||
// 合并
|
||
AddImagesToList(caseImg1.anjianzhaopian, imageList);
|
||
AddImagesToList(caseImg1.chaichufugenghoupic, imageList);
|
||
AddImagesToList(caseImg1.bubanzhaopian, imageList);
|
||
}
|
||
|
||
break;
|
||
case "非法采矿卫片下发":
|
||
var caseImg2 = await client.Queryable<DroneCaseInfoSatellite>().FirstAsync(r => r.Id == caseid);
|
||
|
||
if (caseImg2 != null)
|
||
{
|
||
// 合并
|
||
AddImagesToList(caseImg2.anjianzhaopian, imageList);
|
||
AddImagesToList(caseImg2.chaichufugenghoupic, imageList);
|
||
AddImagesToList(caseImg2.bubanzhaopian, imageList);
|
||
}
|
||
|
||
break;
|
||
case "非法采矿工作管理":
|
||
var caseImg3 = await client.Queryable<DroneCaseInfoMinerals>().FirstAsync(r => r.Id == caseid);
|
||
|
||
if (caseImg3 != null)
|
||
{
|
||
// 合并
|
||
AddImagesToList(caseImg3.zhaopian_fill, imageList);
|
||
AddImagesToList(caseImg3.zhaopianqian_jgzhg, imageList);
|
||
AddImagesToList(caseImg3.zhaopianhou_jgzhg, imageList);
|
||
AddImagesToList(caseImg3.zhaopian_reform, imageList);
|
||
//AddImagesToList(caseImg3.waiyepaizhao, imageList);
|
||
}
|
||
|
||
break;
|
||
case "耕地非粮化":
|
||
var caseImg4 = await client.Queryable<DroneCaseInfoFLH>().FirstAsync(r => r.Id == caseid);
|
||
|
||
if (caseImg4 != null)
|
||
{
|
||
// 合并
|
||
AddImagesToList(caseImg4.anjianzhaopian, imageList);
|
||
AddImagesToList(caseImg4.chaichufugenghoupic, imageList);
|
||
AddImagesToList(caseImg4.bubanzhaopian, imageList);
|
||
}
|
||
|
||
break;
|
||
case "重点问题Ⅰ类":
|
||
var caseImg5 = await client.Queryable<DroneCaseInfoZdwt1>().FirstAsync(r => r.Id == caseid);
|
||
|
||
if (caseImg5 != null)
|
||
{
|
||
// 合并
|
||
AddImagesToList(caseImg5.anjianzhaopian, imageList);
|
||
}
|
||
|
||
break;
|
||
case "重点问题Ⅱ类":
|
||
var caseImg6 = await client.Queryable<DroneCaseInfoZdwt2>().FirstAsync(r => r.Id == caseid);
|
||
|
||
if (caseImg6 != null)
|
||
{
|
||
// 合并
|
||
AddImagesToList(caseImg6.anjianzhaopian, imageList);
|
||
}
|
||
|
||
break;
|
||
case "巡察审计":
|
||
//查询案件的填报(anjianzhaopian)和整改图片信息(chaichufugenghoupic),补办手续（bubanzhaopian）
|
||
var caseImg7 = await client.Queryable<DroneCaseInfoXcsj>().FirstAsync(r => r.Id == caseid);
|
||
if (caseImg7 != null)
|
||
{
|
||
// 合并
|
||
AddImagesToList(caseImg7.anjianzhaopian, imageList);
|
||
AddImagesToList(caseImg7.chaichufugenghoupic, imageList);
|
||
AddImagesToList(caseImg7.bubanzhaopian, imageList);
|
||
}
|
||
|
||
break;
|
||
case "生态红线":
|
||
//查询案件的填报(anjianzhaopian)和整改图片信息(chaichufugenghoupic),补办手续（bubanzhaopian）
|
||
var caseImg8 = await client.Queryable<DroneCaseInfoSTHX>().FirstAsync(r => r.Id == caseid);
|
||
|
||
if (caseImg8 != null)
|
||
{
|
||
// 合并
|
||
AddImagesToList(caseImg8.anjianzhaopian, imageList);
|
||
AddImagesToList(caseImg8.chaichufugenghoupic, imageList);
|
||
AddImagesToList(caseImg8.bubanzhaopian, imageList);
|
||
}
|
||
|
||
break;
|
||
}
|
||
|
||
var caseList = await client.Queryable<DroneImageRef>()
|
||
.WhereIF(!string.IsNullOrEmpty(caseid), r => r.CaseId == caseid)
|
||
.Where(r => imageList.Contains(r.FilePath))
|
||
.Select(r => new DroneImageRef()
|
||
{
|
||
Id = r.Id.SelectAll(),
|
||
})
|
||
.ToListAsync();
|
||
return new Response<List<DroneImageRef>>()
|
||
{
|
||
Result = caseList
|
||
};
|
||
}
|
||
|
||
void AddImagesToList(string images, List<string> list)
|
||
{
|
||
if (!string.IsNullOrEmpty(images))
|
||
{
|
||
list.AddRange(images.Split(',').Where(item => !string.IsNullOrWhiteSpace(item)));
|
||
}
|
||
}
|
||
|
||
public async Task<Response<DroneCaseInfoSTHX>> GetCaseInfoById(string id)
|
||
{
|
||
var info = await Repository.AsSugarClient().Queryable<DroneCaseInfoSTHX>().Where(c => c.Id == id)
|
||
.FirstAsync();
|
||
return new Response<DroneCaseInfoSTHX>
|
||
{
|
||
Result = info,
|
||
Message = "获取数据成功"
|
||
};
|
||
}
|
||
public Response<List<dynamic>> loadStreet()
|
||
{
|
||
var authStrategyContext = _auth.GetCurrentUser();
|
||
var user = authStrategyContext.User;
|
||
string sql0 = "select count(\"Level\") from sys_userorg where \"UserId\"=" + user.Id;
|
||
int cout = client.Ado.GetInt(sql0);
|
||
if (cout == 0 && user.Id != -1)
|
||
{
|
||
List<dynamic> lists = new List<dynamic>();
|
||
Response<List<dynamic>> response = new Response<List<dynamic>>();
|
||
response.Result = lists;
|
||
return response;
|
||
}
|
||
|
||
string sql1 = "select min(\"Level\") from sys_userorg where \"UserId\"=" + user.Id;
|
||
int level = client.Ado.GetInt(sql1);
|
||
if (user != null && user.Id != -1 && level != 0)
|
||
{
|
||
string sql = $"select \"OrgId\" from sys_userorg where \"Level\"=1 and \"UserId\"=" + user.Id;
|
||
string sql2 = $"select \"OrgId\" from sys_userorg where \"Level\"=2 and \"UserId\"=" + user.Id;
|
||
var level1 = client.Ado.SqlQuery<dynamic>(sql);
|
||
var level2 = client.Ado.SqlQuery<dynamic>(sql2);
|
||
string sql3 = "";
|
||
string sql4 = "";
|
||
if (level1.Count > 0)
|
||
{
|
||
string str = "";
|
||
for (int i = 0; i < level1.Count; i++)
|
||
{
|
||
var item = level1[i];
|
||
|
||
if (i == level1.Count - 1)
|
||
{
|
||
str = str + "'" + item.OrgId + "'";
|
||
}
|
||
else
|
||
{
|
||
str = str + "'" + item.OrgId + "',";
|
||
}
|
||
}
|
||
|
||
sql3 = $"select \"Id\",\"Name\" from sys_org where \"ParentId\" in(" + str + ")";
|
||
}
|
||
|
||
if (level2.Count > 0)
|
||
{
|
||
string str = "";
|
||
for (int i = 0; i < level2.Count; i++)
|
||
{
|
||
var item = level2[i];
|
||
|
||
if (i == level2.Count - 1)
|
||
{
|
||
str = str + "'" + item.OrgId + "'";
|
||
}
|
||
else
|
||
{
|
||
str = str + "'" + item.OrgId + "',";
|
||
}
|
||
}
|
||
|
||
sql4 = $"select \"Id\",\"Name\" from sys_org where \"Id\" in(" + str + ")";
|
||
}
|
||
|
||
List<dynamic> list = new List<dynamic>();
|
||
if (!string.IsNullOrEmpty(sql3))
|
||
{
|
||
var list1 = client.Ado.SqlQuery<dynamic>(sql3);
|
||
list.AddRange(list1);
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(sql4))
|
||
{
|
||
var list2 = client.Ado.SqlQuery<dynamic>(sql4);
|
||
list.AddRange(list2);
|
||
}
|
||
|
||
Response<List<dynamic>> response = new Response<List<dynamic>>();
|
||
response.Result = list;
|
||
return response;
|
||
}
|
||
else
|
||
{
|
||
string sql5 = $"select \"Id\",\"Name\" from sys_org where \"Level\"=2";
|
||
var list2 = client.Ado.SqlQuery<dynamic>(sql5);
|
||
Response<List<dynamic>> response = new Response<List<dynamic>>();
|
||
response.Result = list2;
|
||
return response;
|
||
}
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 判读详情
|
||
/// <summary>
|
||
/// 案件详情
|
||
/// </summary>
|
||
/// <param name="id"></param>
|
||
/// <returns></returns>
|
||
public async Task<Response<AddOrUpdateDroneCaseInfoReqExtCurrent>> GetCaseInfo(string id)
|
||
{
|
||
var res = new AddOrUpdateDroneCaseInfoReqExtCurrent();
|
||
res.Init();
|
||
//详情
|
||
DroneCaseInfoSTHX info = null;
|
||
var flag = StringExtension.IsGuid(id);
|
||
if (flag)
|
||
{
|
||
info = await Repository.AsSugarClient().Queryable<DroneCaseInfoSTHX>().Where(c => c.Id == id)
|
||
.FirstAsync();
|
||
}
|
||
else
|
||
{
|
||
info = await Repository.AsSugarClient().Queryable<DroneCaseInfoSTHX>().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 = "获取数据成功"
|
||
};
|
||
}
|
||
#endregion
|
||
|
||
#region 案件关闭
|
||
/// <summary>
|
||
/// 关闭案件
|
||
/// </summary>
|
||
/// <param name="id">案件id</param>
|
||
/// <returns></returns>
|
||
public async Task<Response<bool>> CloseSTHXCaseInfo(string id, string closereason)
|
||
{
|
||
var user = _auth.GetCurrentUser().User;
|
||
using (var uow = base.UnitWork.CreateContext())
|
||
{
|
||
var info = client.Queryable<DroneCaseInfoSTHX>().Where(r => r.Id == id).First();
|
||
await uow.DroneCaseInfoSTHX.UpdateAsync(c => new DroneCaseInfoSTHX()
|
||
{
|
||
is_closed = 1,
|
||
is_intact = 99,
|
||
close_time = DateTime.Now,
|
||
close_user = user.Name,
|
||
close_userid = user.Id.ToString(),
|
||
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, 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
|
||
|
||
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:DroneCaseInfoSTHXSchemeCode").Value;
|
||
|
||
string sqlcount = $" select count(a.\"Id\")\r\n from drone_caseinfo_sthx 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\",qita_shuoming,chuliqingkuangsm,tubanlaiyuan,countyname,streetname,communityname,case_no as caseNo,synchronoustime identificationTime,area,\r\n gengdi_area as gengdiArea,typename,yongjiujibennongtian_area yongjiujibennongtianarea,nongyongdi_area nongyongdiarea, " +
|
||
$"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 measure_name='1'\r\n then '补办手续' when measure_name='0' then '恢复原地类' end as measureName,\r\nqita_use_to as qitaUseTo\r\n from drone_caseinfo_sthx 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 (level != 0)
|
||
//{
|
||
// sqlpart = sqlpart + $" and countyusable = 1";
|
||
//}
|
||
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.weifaleixing))
|
||
{
|
||
// 判断违法
|
||
sqlpart = sqlpart + "and is_illegal = '1'";
|
||
sqlpart = sqlpart + $" and weifaleixing='" + req.weifaleixing + "'";
|
||
}
|
||
|
||
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))
|
||
{
|
||
var tubanlaiyuan = req.tubanlaiyuan;
|
||
var tubanlaiyuanList = tubanlaiyuan.Split(',').ToList();
|
||
sqlpart = sqlpart + $" and tubanlaiyuan in ('{string.Join("','", tubanlaiyuanList)}')";
|
||
}
|
||
|
||
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(COALESCE(NULLIF(area, '')::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(area, '')::numeric, 0) AS numeric)> " +
|
||
double.Parse(req.tubanArea1);
|
||
}
|
||
else if (string.IsNullOrEmpty(req.tubanArea1) && !string.IsNullOrEmpty(req.tubanArea2))
|
||
{
|
||
sqlpart = sqlpart + $" and CAST(COALESCE(NULLIF(area, '')::numeric, 0) AS numeric)< " +
|
||
double.Parse(req.tubanArea2);
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(req.gengdiArea1) && !string.IsNullOrEmpty(req.gengdiArea2))
|
||
{
|
||
sqlpart = sqlpart + $" and CAST(COALESCE(NULLIF(gengdi_area, '')::numeric, 0) 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(COALESCE(NULLIF(gengdi_area, '')::numeric, 0) AS numeric)> " +
|
||
double.Parse(req.gengdiArea1);
|
||
}
|
||
else if (string.IsNullOrEmpty(req.gengdiArea1) && !string.IsNullOrEmpty(req.gengdiArea2))
|
||
{
|
||
sqlpart = sqlpart + $" and CAST(COALESCE(NULLIF(gengdi_area, '')::numeric, 0) AS numeric)< " +
|
||
double.Parse(req.gengdiArea2);
|
||
}
|
||
|
||
|
||
if (!string.IsNullOrEmpty(req.jibenArea1) && !string.IsNullOrEmpty(req.jibenArea2))
|
||
{
|
||
sqlpart = sqlpart +
|
||
$" and CAST(COALESCE(NULLIF(yongjiujibennongtian_area, '')::numeric, 0) 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(COALESCE(NULLIF(yongjiujibennongtian_area, '')::numeric, 0) AS numeric)> " +
|
||
double.Parse(req.jibenArea1);
|
||
}
|
||
else if (string.IsNullOrEmpty(req.jibenArea1) && !string.IsNullOrEmpty(req.jibenArea2))
|
||
{
|
||
sqlpart = sqlpart +
|
||
$" and CAST(COALESCE(NULLIF(yongjiujibennongtian_area, '')::numeric, 0) 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 ("4".Equals(item.qitauseto))
|
||
{
|
||
item.actualuseto = "实地未变化";
|
||
}
|
||
else if ("6".Equals(item.qitauseto))
|
||
{
|
||
item.actualuseto = "未有建设行为";
|
||
}
|
||
|
||
|
||
else
|
||
{
|
||
item.actualuseto = "";
|
||
}
|
||
}
|
||
}
|
||
|
||
return new Response<PageInfo<List<dynamic>>>
|
||
{
|
||
Result = new PageInfo<List<dynamic>>
|
||
{
|
||
Items = info,
|
||
Total = count
|
||
}
|
||
};
|
||
}
|
||
|
||
public async Task<Response<Dictionary<string, object>>> LoadCaseInfoTuBanListApp(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 sqlcount = $"select count(*) from drone_caseinfo_sthx where 1=1 and is_closed=0";
|
||
int allCount = client.Ado.GetInt(sqlcount + sqlpart);
|
||
string sql =
|
||
$" select \"Id\",qita_shuoming,chuliqingkuangsm,tubanlaiyuan,countyname,streetname,communityname,is_shijibohui,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 measure_name='0'\r\n then '拆除复耕' when measure_name='1' then '补办手续' end as measureName,\r\nqita_use_to as qitaUseTo\r\n from drone_caseinfo_sthx where 1=1 and is_closed=0 ";
|
||
if (req.year != 0)
|
||
{
|
||
sqlpart = sqlpart + $" and EXTRACT(YEAR FROM synchronoustime) = {req.year}";
|
||
}
|
||
//查询县区可看的数据
|
||
//if (level != 0)
|
||
//{
|
||
// sqlpart = sqlpart + $" and countyusable = 1";
|
||
//}
|
||
|
||
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.weifaleixing))
|
||
{
|
||
// 判断违法
|
||
sqlpart = sqlpart + "and is_illegal = '1'";
|
||
sqlpart = sqlpart + $" and weifaleixing='" + req.weifaleixing + "'";
|
||
}
|
||
|
||
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))
|
||
{
|
||
sqlpart = sqlpart + $" and handle_status_name='" + req.nowStatus + "'";
|
||
}
|
||
|
||
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(COALESCE(NULLIF(area, '')::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(area, '')::numeric, 0) AS numeric)> " +
|
||
double.Parse(req.tubanArea1);
|
||
}
|
||
else if (string.IsNullOrEmpty(req.tubanArea1) && !string.IsNullOrEmpty(req.tubanArea2))
|
||
{
|
||
sqlpart = sqlpart + $" and CAST(COALESCE(NULLIF(area, '')::numeric, 0) AS numeric)< " +
|
||
double.Parse(req.tubanArea2);
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(req.gengdiArea1) && !string.IsNullOrEmpty(req.gengdiArea2))
|
||
{
|
||
sqlpart = sqlpart + $" and CAST(COALESCE(NULLIF(gengdi_area, '')::numeric, 0) 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(COALESCE(NULLIF(gengdi_area, '')::numeric, 0) AS numeric)> " +
|
||
double.Parse(req.gengdiArea1);
|
||
}
|
||
else if (string.IsNullOrEmpty(req.gengdiArea1) && !string.IsNullOrEmpty(req.gengdiArea2))
|
||
{
|
||
sqlpart = sqlpart + $" and CAST(COALESCE(NULLIF(gengdi_area, '')::numeric, 0) AS numeric)< " +
|
||
double.Parse(req.gengdiArea2);
|
||
}
|
||
|
||
|
||
if (!string.IsNullOrEmpty(req.jibenArea1) && !string.IsNullOrEmpty(req.jibenArea2))
|
||
{
|
||
sqlpart = sqlpart +
|
||
$" and CAST(COALESCE(NULLIF(yongjiujibennongtian_area, '')::numeric, 0) 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(COALESCE(NULLIF(yongjiujibennongtian_area, '')::numeric, 0) AS numeric)> " +
|
||
double.Parse(req.jibenArea1);
|
||
}
|
||
else if (string.IsNullOrEmpty(req.jibenArea1) && !string.IsNullOrEmpty(req.jibenArea2))
|
||
{
|
||
sqlpart = sqlpart +
|
||
$" and CAST(COALESCE(NULLIF(yongjiujibennongtian_area, '')::numeric, 0) AS numeric)< " +
|
||
double.Parse(req.jibenArea2);
|
||
}
|
||
|
||
sqlpart += $" ORDER BY synchronoustime desc";
|
||
var info = await client.SqlQueryable<dynamic>(sql + sqlpart).ToPageListAsync(req.page, req.limit, totalcount);
|
||
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
|
||
{
|
||
item.actualuseto = "";
|
||
}
|
||
}
|
||
}
|
||
|
||
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>>> 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:DroneCaseInfoSTHXSchemeCode").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_sthx c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
|
||
schemeCode + "' ";
|
||
if (string.IsNullOrEmpty(req.lat) || string.IsNullOrEmpty(req.lng))
|
||
{
|
||
sql =
|
||
$"select 0 as distance, 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 ,c.is_shijibohui,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_sthx c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
|
||
schemeCode + "' ";
|
||
}
|
||
else
|
||
{
|
||
sql =
|
||
$"select earth_distance(ll_to_earth (c.lat,c.lng),ll_to_earth(" + req.lat + "," + req.lng +
|
||
$")) as distance,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 ,c.is_shijibohui,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_sthx 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_sthx 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 ,c.is_shijibohui,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_sthx 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 (level != 0)
|
||
//{
|
||
// sqlpart = sqlpart + $" and c.countyusable = 1";
|
||
//}
|
||
if (!string.IsNullOrEmpty(req.typename))
|
||
{
|
||
sqlpart = sqlpart + $" and c.typename='" + req.typename + "'";
|
||
}
|
||
if (!string.IsNullOrEmpty(req.nowStatus))
|
||
{
|
||
string[] str = req.nowStatus.Split(",");
|
||
int isDarwback = 0;
|
||
string strstatus = "";
|
||
if (req.nowStatus.Contains("市级驳回") && req.nowStatus.Contains("县级驳回"))
|
||
{
|
||
sqlpart = sqlpart + $" and (c.is_drawback = '1' or c.is_shijibohui = '1')";
|
||
|
||
}
|
||
else if (req.nowStatus.Contains("县级驳回"))
|
||
{
|
||
sqlpart = sqlpart + $" and c.is_drawback = '1'";
|
||
}
|
||
else if (req.nowStatus.Contains("市级驳回"))
|
||
{
|
||
sqlpart = sqlpart + $" and c.is_shijibohui = '1'";
|
||
}
|
||
for (int i = 0; i < str.Length; i++)
|
||
{
|
||
|
||
|
||
|
||
if ("县级驳回".Equals(str[i]))
|
||
{
|
||
|
||
continue;
|
||
}
|
||
|
||
if ("市级驳回".Equals(str[i]))
|
||
{
|
||
|
||
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.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);
|
||
}
|
||
|
||
if (string.IsNullOrEmpty(req.lat) || string.IsNullOrEmpty(req.lng))
|
||
{
|
||
sqlpart += $" ORDER BY c.synchronoustime desc";
|
||
}
|
||
else
|
||
{
|
||
sqlpart += $" ORDER BY distance";
|
||
}
|
||
|
||
|
||
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 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 qita_shuoming,chuliqingkuangsm,picihao,xjshenhejieguo,sjshenhejieguo,xianjiyijian,shijiyijian,verifyuser,case_description,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,nongyongdi_area nongyongdiarea,shengtaibaohuhongxian_area shengtaibaohuhongxianarea," +
|
||
$"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 measure_name='1'\r\n then '补办手续' when measure_name='0' then '恢复原地类' end as measureName,\r\nqita_use_to as qitaUseTo,geomid\r\n from drone_caseinfo_sthx 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 (level != 0)
|
||
//{
|
||
// sqlpart = sqlpart + $" and countyusable = 1";
|
||
//}
|
||
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.weifaleixing))
|
||
{
|
||
// 判断违法
|
||
sqlpart = sqlpart + "and is_illegal = '1'";
|
||
sqlpart = sqlpart + $" and weifaleixing='" + req.weifaleixing + "'";
|
||
}
|
||
|
||
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(COALESCE(NULLIF(area, '')::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(area, '')::numeric, 0) AS numeric)> " +
|
||
double.Parse(req.tubanArea1);
|
||
}
|
||
else if (string.IsNullOrEmpty(req.tubanArea1) && !string.IsNullOrEmpty(req.tubanArea2))
|
||
{
|
||
sqlpart = sqlpart + $" and CAST(COALESCE(NULLIF(area, '')::numeric, 0) AS numeric)< " +
|
||
double.Parse(req.tubanArea2);
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(req.gengdiArea1) && !string.IsNullOrEmpty(req.gengdiArea2))
|
||
{
|
||
sqlpart = sqlpart + $" and CAST(COALESCE(NULLIF(gengdi_area, '')::numeric, 0) 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(COALESCE(NULLIF(gengdi_area, '')::numeric, 0) AS numeric)> " +
|
||
double.Parse(req.gengdiArea1);
|
||
}
|
||
else if (string.IsNullOrEmpty(req.gengdiArea1) && !string.IsNullOrEmpty(req.gengdiArea2))
|
||
{
|
||
sqlpart = sqlpart + $" and CAST(COALESCE(NULLIF(gengdi_area, '')::numeric, 0) AS numeric)< " +
|
||
double.Parse(req.gengdiArea2);
|
||
}
|
||
|
||
|
||
if (!string.IsNullOrEmpty(req.jibenArea1) && !string.IsNullOrEmpty(req.jibenArea2))
|
||
{
|
||
sqlpart = sqlpart +
|
||
$" and CAST(COALESCE(NULLIF(yongjiujibennongtian_area, '')::numeric, 0) 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(COALESCE(NULLIF(yongjiujibennongtian_area, '')::numeric, 0) AS numeric)> " +
|
||
double.Parse(req.jibenArea1);
|
||
}
|
||
else if (string.IsNullOrEmpty(req.jibenArea1) && !string.IsNullOrEmpty(req.jibenArea2))
|
||
{
|
||
sqlpart = sqlpart +
|
||
$" and CAST(COALESCE(NULLIF(yongjiujibennongtian_area, '')::numeric, 0) 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 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); //将字体样式赋给样式对象
|
||
|
||
// 创建一个数值格式的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 ("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, 25 * 300);
|
||
}
|
||
else if ("streetname".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 15 * 300);
|
||
}
|
||
else if ("communityname".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 20 * 300);
|
||
}
|
||
else if ("typename".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 15 * 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, 15 * 300);
|
||
}
|
||
else if ("gengdiArea".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 15 * 300);
|
||
}
|
||
else if ("yongjiujibennongtianarea".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 15 * 300);
|
||
}
|
||
else if ("nongyongdiarea".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 15 * 300);
|
||
}
|
||
else if ("shengtaibaohuhongxianarea".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 20 * 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 ("pandingyijushuoming".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 15 * 300);
|
||
}
|
||
else if ("examiner_name".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 12 * 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 ("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.identificationTime.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.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
|
||
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.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
|
||
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.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
|
||
cell.SetCellValue(double.Parse(objValue));
|
||
}
|
||
else
|
||
{
|
||
cell.SetCellValue(0.00d); //填充Excel单元格
|
||
}
|
||
continue;
|
||
}
|
||
else if ("nongyongdiarea".Equals(headers[j].key))
|
||
{
|
||
if (string.IsNullOrEmpty(item.nongyongdiarea))
|
||
{
|
||
objValue = item.nongyongdiarea;
|
||
}
|
||
else
|
||
{
|
||
|
||
objValue = double.Parse(item.nongyongdiarea).ToString("F2");
|
||
}
|
||
var cell = dataRow.CreateCell(j);
|
||
cell.CellStyle = numericStyle;
|
||
if (objValue != null && !string.IsNullOrEmpty(objValue.ToString()))
|
||
{
|
||
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
|
||
cell.SetCellValue(double.Parse(objValue));
|
||
}
|
||
else
|
||
{
|
||
cell.SetCellValue(0.00d); //填充Excel单元格
|
||
}
|
||
continue;
|
||
}
|
||
else if ("shengtaibaohuhongxianarea".Equals(headers[j].key))
|
||
{
|
||
if (string.IsNullOrEmpty(item.shengtaibaohuhongxianarea))
|
||
{
|
||
objValue = item.shengtaibaohuhongxianarea;
|
||
}
|
||
else
|
||
{
|
||
|
||
objValue = double.Parse(item.shengtaibaohuhongxianarea).ToString("F2");
|
||
}
|
||
var cell = dataRow.CreateCell(j);
|
||
cell.CellStyle = numericStyle;
|
||
if (objValue != null && !string.IsNullOrEmpty(objValue.ToString()))
|
||
{
|
||
cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric);
|
||
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 ("4".Equals(item.qitaUseTo))
|
||
{
|
||
qitauseto = "实地未变化";
|
||
}
|
||
else if ("6".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 ("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 ("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 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);
|
||
//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_sthx b on a.relid=b.\"Id\" WHERE gid IN ({gidsString})";
|
||
string query =
|
||
$"SELECT gid,case_no,handle_status_name,typename,original_case_no,gengdi_area, is_illegal_name,is_intact_name," +
|
||
$"area,yongjiujibennongtian_area,xiangmumc,xiangmuzhuti,weifaleixing,measure_name,countyname,streetname,communityname,createtime,synchronoustime,tubanlaiyuan,ST_AsText(geom) AS geom FROM view_drone_caseinfo_sthx 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.handle_status_name == null ? "" : row.handle_status_name },
|
||
{ "判读状态", row.is_intact_name == null ? "" : row.is_intact_name },
|
||
{ "图斑类型", row.typename == null ? "" : row.typename },
|
||
{ "判定结果", row.is_illegal_name == null ? "" : row.is_illegal_name },
|
||
{ "图斑面积(亩)", row.area == null ? "" : row.area },
|
||
{ "耕地面积", row.gengdi_area == null ? "" : row.gengdi_area },
|
||
{ "永农面积", row.yongjiujibennongtian_area == null ? "" : row.yongjiujibennongtian_area },
|
||
{ "项目名称", row.xiangmumc == null ? "" : row.xiangmumc },
|
||
{ "违法类型", row.weifaleixing == null ? "" : row.weifaleixing.ToString() },
|
||
{ "处理措施", row.measure_name == null ? "" : row.measure_name },
|
||
{ "创建时间", row.synchronoustime == null ? "" : row.synchronoustime.ToString() },
|
||
{ "图斑来源", row.tubanlaiyuan == null ? "" : row.tubanlaiyuan },
|
||
};
|
||
|
||
IFeature feature = new Feature(geometry, attributes);
|
||
features.Add(feature);
|
||
}
|
||
|
||
if (features.Count == 0)
|
||
{
|
||
throw new Exception("数据不可用");
|
||
}
|
||
|
||
// 导出 SHP 文件及其关联文件
|
||
ExportToShapefileFour(shpFilePath, features);
|
||
// 将文件打包成 ZIP
|
||
CreateZipFromShapefiles(shpFilePath, shpFilePathzip);
|
||
}
|
||
|
||
|
||
public void ExportToShapefileFour(string shpPath, List<IFeature> features)
|
||
{
|
||
//var geometryFactory = new GeometryFactory();
|
||
|
||
//// 写入 CPG 文件（定义字符编码）
|
||
//var cpgPath = System.IO.Path.ChangeExtension(shpPath, "cpg");
|
||
//File.WriteAllText(cpgPath, "UTF-8", Encoding.UTF8);
|
||
//// 获取 SHP 文件头
|
||
//var header = ShapefileDataWriter.GetHeader(features[0], features.Count);
|
||
|
||
//// 创建 ShapefileDataWriter
|
||
//var shapeFileWriter = new ShapefileDataWriter(shpPath, geometryFactory)
|
||
//{
|
||
// Header = header
|
||
//};
|
||
|
||
//// 写入 SHP 文件
|
||
//shapeFileWriter.Write(features);
|
||
|
||
// 写入 CPG 文件（定义字符编码）
|
||
string prjStr = @"GEOGCS[""GCS_China_Geodetic_Coordinate_System_2000"",DATUM[""D_China_2000"",SPHEROID[""CGCS2000"",6378137.0,298.257222101]],PRIMEM[""Greenwich"",0.0],UNIT[""Degree"",0.0174532925199433]]";
|
||
var cpgPath = Path.ChangeExtension(shpPath, "prj");
|
||
File.WriteAllText(cpgPath, prjStr, Encoding.UTF8);
|
||
|
||
Shapefile.WriteAllFeatures(features, shpPath, encoding: Encoding.UTF8);
|
||
}
|
||
|
||
public void CreateZipFromShapefiles(string shpPath, string zipPath)
|
||
{
|
||
var files = new List<string>
|
||
{
|
||
Path.ChangeExtension(shpPath, "cpg"),
|
||
shpPath,
|
||
Path.ChangeExtension(shpPath, "shx"),
|
||
Path.ChangeExtension(shpPath, "dbf"),
|
||
Path.ChangeExtension(shpPath, "prj")
|
||
};
|
||
|
||
using (var zipArchive = ZipFile.Open(zipPath, ZipArchiveMode.Create))
|
||
{
|
||
foreach (var file in files)
|
||
{
|
||
if (File.Exists(file))
|
||
{
|
||
zipArchive.CreateEntryFromFile(file, Path.GetFileName(file));
|
||
}
|
||
}
|
||
}
|
||
}
|
||
public Geometry ParseGeometry(string wkt)
|
||
{
|
||
if (string.IsNullOrEmpty(wkt))
|
||
{
|
||
return null;
|
||
}
|
||
|
||
var reader = new WKTReader();
|
||
return reader.Read(wkt);
|
||
}
|
||
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_sthx 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.sthx + "' 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
|
||
}
|
||
};
|
||
}
|
||
}
|
||
|
||
|
||
} |