4410 lines
214 KiB
C#
4410 lines
214 KiB
C#
using System.Drawing;
|
||
using System.Dynamic;
|
||
using System.Text;
|
||
using DocumentFormat.OpenXml.Spreadsheet;
|
||
using Hopetry.App.Common;
|
||
using Infrastructure;
|
||
using Infrastructure.Extensions;
|
||
using Infrastructure.Helpers;
|
||
using Microsoft.Extensions.Configuration;
|
||
using NetTopologySuite.Features;
|
||
using Newtonsoft.Json.Linq;
|
||
using Newtonsoft.Json;
|
||
using NPOI.HSSF.UserModel;
|
||
using NPOI.SS.UserModel;
|
||
using NPOI.XWPF.UserModel;
|
||
using OpenAuth.App.BaseApp.Base;
|
||
using OpenAuth.App.BaseApp.Shape;
|
||
using OpenAuth.App.FormModule;
|
||
using OpenAuth.App.Interface;
|
||
using OpenAuth.App.Request;
|
||
using OpenAuth.App.Response;
|
||
using OpenAuth.App.ServiceApp.DroneCaseInfo.Request;
|
||
using OpenAuth.App.ServiceApp.DroneCaseInfo.Response;
|
||
using OpenAuth.App.ServiceApp.Response;
|
||
using OpenAuth.Repository;
|
||
using OpenAuth.Repository.Domain;
|
||
using SqlSugar;
|
||
using PictureType = NPOI.XWPF.UserModel.PictureType;
|
||
using Org.BouncyCastle.Bcpg.OpenPgp;
|
||
using OpenAuth.App.BasicQueryService;
|
||
|
||
namespace OpenAuth.App.ServiceApp.DroneCaseInfo
|
||
{
|
||
/// <summary>
|
||
/// 重点问题2
|
||
/// </summary>
|
||
public partial class DroneCaseInfoZdwt2App : SqlSugarBaseApp<DroneCaseInfoZdwt2, SugarDbContext>
|
||
{
|
||
private readonly IConfiguration _configuration;
|
||
private readonly ISqlSugarClient client;
|
||
private readonly WFProcessApp _processApp;
|
||
private readonly ShpLayerSourceApp _shpLayerSourceApp;
|
||
CommonDataManager _commonDataManager;
|
||
public DroneCaseInfoZdwt2App(ISugarUnitOfWork<SugarDbContext> unitWork,
|
||
ISimpleClient<DroneCaseInfoZdwt2> repository, IAuth auth, ISqlSugarClient sqlSugarClient,
|
||
IConfiguration configuration, WFProcessApp processApp, ShpLayerSourceApp shpLayerSourceApp, CommonDataManager commonDataManager) : base(unitWork,
|
||
repository,
|
||
auth)
|
||
{
|
||
_configuration = configuration;
|
||
_processApp = processApp;
|
||
_shpLayerSourceApp = shpLayerSourceApp;
|
||
this.client = sqlSugarClient;
|
||
_commonDataManager = commonDataManager;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 查询案件列表
|
||
/// </summary>
|
||
/// <param name="req"></param>
|
||
/// <returns></returns>
|
||
public async Task<Response<PageInfo<List<DroneCaseInfoZdwt2>>>> LoadCaseInfoListInterface(
|
||
QueryCaseInfoListReq req)
|
||
{
|
||
using var db = Repository.AsSugarClient();
|
||
var userInfo = _auth.GetCurrentUser();
|
||
//返回值
|
||
RefAsync<int> total = 0;
|
||
//查询条件
|
||
var query = QueryCaseInfoList(db, req, userInfo.User);
|
||
|
||
var querySql = query.ToSqlString();
|
||
if (!userInfo.User.Id.Equals(-1))
|
||
{
|
||
var idList = string.Join(",", userInfo.Orgs.Select(t => t.Id).MapToList<string>());
|
||
idList = idList.Replace(",", "','");
|
||
var whereSql =
|
||
$"select CAST(\"Id\" AS VARCHAR) from sys_org where string_to_array(\"CascadeId\",'.') && ARRAY['{idList}']";
|
||
querySql = $" select * from ({query.ToSqlString()}) t where communityid in ({whereSql}) ";
|
||
}
|
||
|
||
var caseList = await db.SqlQueryable<DroneCaseInfoZdwt2>(querySql)
|
||
.ToPageListAsync(req.page, req.limit, total);
|
||
|
||
|
||
/*.Select(c => new DroneCaseInfoExtCurrent()
|
||
{
|
||
Id = c.Id,
|
||
CaseNo = c.CaseNo,
|
||
CaseName = c.CaseName,
|
||
CaseDescription = c.CaseDescription,
|
||
StartTime = c.StartTime,
|
||
EndTime = c.EndTime,
|
||
Address = c.Address,
|
||
Lng = c.Lng,
|
||
Lat = c.Lat,
|
||
typeid = c.typeid,
|
||
TypeName = c.TypeName,
|
||
handle_status_id = c.handle_status_id,
|
||
HandleStatusName = c.HandleStatusName,
|
||
CaseStatusId = c.CaseStatusId,
|
||
CaseStatusName = c.CaseStatusName,
|
||
createtime = c.createtime,
|
||
createuser = c.createuser,
|
||
DroneNo = c.DroneNo,
|
||
DealUserId = c.DealUserId,
|
||
DealUsername = c.DealUsername,
|
||
CreateUsername = c.CreateUsername,
|
||
IsDelete = c.IsDelete,
|
||
countyid = c.countyid,
|
||
CountyName = c.CountyName,
|
||
streetid = c.streetid,
|
||
StreetName = c.StreetName,
|
||
communityid = c.communityid,
|
||
CommunityName = c.CommunityName,
|
||
Remark = c.Remark,
|
||
IsClosed = c.IsClosed,
|
||
Area = c.Area,
|
||
IsIllegal = c.IsIllegal,
|
||
CloseUser = c.CloseUser,
|
||
CloseTime = c.CloseTime,
|
||
identification_user = c.identification_user,
|
||
identification_time = c.identification_time,
|
||
IsIntact = c.IsIntact,
|
||
CloseUserId = c.CloseUserId,
|
||
identification_userid = c.identification_userid,
|
||
VerifyUserId = c.VerifyUserId,
|
||
VerifyUser = c.VerifyUser,
|
||
VerifyTime = c.VerifyTime,
|
||
IsImprove = c.IsImprove,
|
||
ImproveReason = c.ImproveReason,
|
||
is_dispense = c.is_dispense,
|
||
DispenseUserid = c.DispenseUserid,
|
||
DispenseTime = c.DispenseTime,
|
||
DispenseUsername = c.DispenseUsername,
|
||
verifystatus = c.verifystatus,
|
||
VerifyStatusName = c.VerifyStatusName,
|
||
IsChecked = c.IsChecked,
|
||
DealTime = c.DealTime,
|
||
PrePhaseTime = c.PrePhaseTime,
|
||
LaterPhaseTime = c.LaterPhaseTime,
|
||
PrePhaseImg = c.PrePhaseImg,
|
||
LaterPhaseImg = c.LaterPhaseImg,
|
||
IsVerification = c.IsVerification,
|
||
is_drawback = c.is_drawback,
|
||
BackToUserid = c.BackToUserid,
|
||
BackToUsername = c.BackToUsername,
|
||
})*/
|
||
|
||
return new Response<PageInfo<List<DroneCaseInfoZdwt2>>>
|
||
{
|
||
Result = new PageInfo<List<DroneCaseInfoZdwt2>>
|
||
{
|
||
Items = caseList,
|
||
Total = total
|
||
}
|
||
};
|
||
}
|
||
|
||
/// <summary>
|
||
/// 案件详情
|
||
/// </summary>
|
||
/// <param name="id"></param>
|
||
/// <returns></returns>
|
||
public async Task<Response<AddOrUpdateDroneCaseInfoReqExtCurrent>> GetCaseInfo(string id)
|
||
{
|
||
var res = new AddOrUpdateDroneCaseInfoReqExtCurrent();
|
||
res.Init();
|
||
//详情
|
||
DroneCaseInfoZdwt2 info = null;
|
||
var flag = StringExtension.IsGuid(id);
|
||
if (flag)
|
||
{
|
||
info = await Repository.AsSugarClient().Queryable<DroneCaseInfoZdwt2>().Where(c => c.Id == id)
|
||
.FirstAsync();
|
||
}
|
||
else
|
||
{
|
||
info = await Repository.AsSugarClient().Queryable<DroneCaseInfoZdwt2>().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 = "获取数据成功"
|
||
};
|
||
}
|
||
|
||
|
||
public async Task<Response<DroneCaseInfoZdwt2>> GetCaseInfoById(string id)
|
||
{
|
||
var info = await Repository.AsSugarClient().Queryable<DroneCaseInfoZdwt2>().Where(c => c.Id == id)
|
||
.FirstAsync();
|
||
return new Response<DroneCaseInfoZdwt2>
|
||
{
|
||
Result = info,
|
||
Message = "获取数据成功"
|
||
};
|
||
}
|
||
|
||
/// <summary>
|
||
/// 案件导出
|
||
/// </summary>
|
||
/// <param name="id"></param>
|
||
/// <returns></returns>
|
||
public string DroneCaseInfoExport(string id)
|
||
{
|
||
using (var db = Repository.AsSugarClient())
|
||
{
|
||
var model = GetCaseInfo(id);
|
||
var info = (DroneCaseInfoZdwt2)model.Result.Result.info;
|
||
|
||
Dictionary<string, object> dic = new Dictionary<string, object>();
|
||
dic.Add("CASE_NO", info.case_no);
|
||
dic.Add("CREATEUSERNAME", info.createusername);
|
||
dic.Add("ADDRESS", info.address);
|
||
dic.Add("LNG", info.lng);
|
||
dic.Add("LAT", info.lat);
|
||
dic.Add("DEAL_USERNAME", info.deal_username);
|
||
dic.Add("HANDLE_STATUS_NAME", info.handle_status_name);
|
||
dic.Add("CASE_DESCRIPTION", info.case_description);
|
||
dic.Add("CREATETIME", info.createtime);
|
||
var root = AppDomain.CurrentDomain.BaseDirectory;
|
||
|
||
string pathGuid = Guid.NewGuid().ToString();
|
||
var out_path = "ExportWordModel\\" + pathGuid + "\\drone_enforcement.docx";
|
||
if (!Directory.Exists(root + "ExportWordModel\\" + pathGuid))
|
||
{
|
||
Directory.CreateDirectory(root + "ExportWordModel\\" + pathGuid);
|
||
}
|
||
|
||
WordHelper.Export(root + "ExportWordModel\\drone_enforcement.docx", root + out_path, dic);
|
||
|
||
XWPFDocument doc = null;
|
||
|
||
//打开文件
|
||
using (var stream = File.OpenRead(root + out_path))
|
||
{
|
||
doc = new XWPFDocument(stream); //创建一个word文档
|
||
}
|
||
|
||
out_path = "ExportWordModel\\" + pathGuid + "\\drone_enforcement_n.docx";
|
||
|
||
string picFilePath = _configuration.GetSection("PictureFilesPath").Value;
|
||
|
||
//表格
|
||
var table = doc.Tables[0];
|
||
//列
|
||
var cell = table.Rows[4].GetCell(1);
|
||
|
||
foreach (var para in cell.Paragraphs)
|
||
{
|
||
var runs = para.Runs;
|
||
foreach (var gr in runs)
|
||
{
|
||
var text = gr.Text;
|
||
gr.ReplaceText("#PICLIST#", "");
|
||
// todo 取案件图片，问题是得取哪些图片
|
||
var picList = new List<string>();
|
||
foreach (var item in picList)
|
||
{
|
||
if (!File.Exists(picFilePath + item))
|
||
continue;
|
||
//图片路径
|
||
var image = Image.FromFile(picFilePath + item);
|
||
//添加图片
|
||
gr.AddPicture(ImageHelper.ImgToStream(image), (int)PictureType.PNG,
|
||
Guid.NewGuid().ToString() + ".png", image.Width * 800, image.Height * 800);
|
||
}
|
||
|
||
break;
|
||
}
|
||
}
|
||
|
||
using (FileStream fs = new FileStream(root + out_path, FileMode.OpenOrCreate, FileAccess.Write))
|
||
{
|
||
doc.Write(fs);
|
||
Console.WriteLine("生成word成功");
|
||
}
|
||
|
||
Task.Run(() =>
|
||
{
|
||
//过一分钟后把文件夹删除
|
||
Thread.Sleep(1000 * 30);
|
||
Directory.Delete(root + "ExportWordModel\\" + pathGuid, true);
|
||
});
|
||
|
||
return out_path;
|
||
}
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 查询条件组装
|
||
/// </summary>
|
||
/// <param name="db"></param>
|
||
/// <param name="req"></param>
|
||
/// <param name="user"></param>
|
||
/// <param name="is_order"></param>
|
||
/// <returns></returns>
|
||
private ISugarQueryable<DroneCaseInfoZdwt2> QueryCaseInfoList(ISqlSugarClient db, QueryCaseInfoListReq req,
|
||
SysUser user,
|
||
int? is_order = 1, List<string> roles = null)
|
||
{
|
||
DateTime? threeTime = DateTime.Now.AddDays(-3);
|
||
DateTime? sevenTime = DateTime.Now.AddDays(-7);
|
||
|
||
|
||
//查询用户角色绑定的流程有哪些
|
||
var flowList = new List<string>();
|
||
var flowSorts = new List<int?>();
|
||
var roleNameList = new List<string>();
|
||
//查询部门及下级部门
|
||
var orgid = db.Queryable<SysOrg>().ToChildList(a => a.ParentId, req.countyid).Select(a => a.Id.ToString())
|
||
.ToList();
|
||
if (roles != null && req.is_verify_filter != null)
|
||
{
|
||
flowList = db.Queryable<DroneRelevance>()
|
||
.Where(c => roles.Contains(c.SecondId) && c.Key == Define.FLOW_ROLE).Select(c => c.FirstId)
|
||
.ToList();
|
||
//查询用户绑定的流程Sort
|
||
flowSorts = db.Queryable<DroneFlowInstance>()
|
||
.Where(c => flowList.Contains(c.Id) && c.status == 0 && c.instance_name == "CaseVerify")
|
||
.Select(c => c.sort).ToList();
|
||
roleNameList = db.Queryable<SysRole>().Where(c => roles.Contains(c.Id.ToString())).Select(c => c.Name)
|
||
.ToList();
|
||
}
|
||
|
||
//待优化
|
||
var casetype = db.Queryable<DroneRelevance>()
|
||
.LeftJoin<DroneRelevance>((a, b) => a.FirstId == b.FirstId)
|
||
.LeftJoin<SysUserRole>((a, b, c) => b.SecondId == c.RoleId.ToString())
|
||
.Where((a, b, c) => c.UserId == user.Id && a.Key == Define.TOPIC_CASETYPE && b.Key == Define.TOPIC_ROLE)
|
||
.Select(a => a.SecondId).Distinct().ToList();
|
||
var query = db.Queryable<DroneCaseInfoZdwt2>()
|
||
.Where(c => c.is_delete == 0 || c.is_delete == null)
|
||
//关键字过滤数据
|
||
.WhereIF(!string.IsNullOrEmpty(req.key),
|
||
c => c.case_no.Contains(req.key) || c.case_name.Contains(req.key) ||
|
||
c.case_description.Contains(req.key))
|
||
//是否完整：判读页面 用来查看是否已判读的
|
||
.WhereIF(req.is_intact != null && req.is_intact != 0, c => c.is_intact == req.is_intact)
|
||
.WhereIF(req.is_intact == 0, c => c.is_intact == null || c.is_intact == 0)
|
||
//上报人
|
||
.WhereIF(req.is_reporter == 1 && user.Account != Define.SYSTEM_USERNAME,
|
||
c => c.createuser == user.Id.ToString())
|
||
//处理人：相当于 局管理员和乡镇管理员用
|
||
.WhereIF(req.is_dealer == 1 && user.Account != Define.SYSTEM_USERNAME, c =>
|
||
SqlFunc.Subqueryable<SysUserOrg>()
|
||
.Where(it => it.OrgId.ToString() == c.communityid && it.UserId == user.Id).Any()
|
||
|| SqlFunc.Subqueryable<SysUserOrg>()
|
||
.Where(it => it.OrgId.ToString() == c.streetid && it.UserId == user.Id).Any()
|
||
|| SqlFunc.Subqueryable<SysUserOrg>()
|
||
.Where(it => it.OrgId.ToString() == c.countyid && it.UserId == user.Id).Any()
|
||
|| c.createuser == user.Id.ToString())
|
||
//执行人过滤：相当于 执法人员用
|
||
.WhereIF(req.dispense_dealer == 1 && user.Account != Define.SYSTEM_USERNAME,
|
||
c => SqlFunc.Subqueryable<DroneRelevance>().Where(it =>
|
||
it.SecondId == c.Id && it.Key == Define.USERDRONECASE && it.FirstId == user.Id.ToString())
|
||
.Any())
|
||
//专题权限过滤
|
||
.WhereIF(req.is_dealer == 1 && user.Account != Define.SYSTEM_USERNAME, c => casetype.Contains(c.typeid))
|
||
//分发状态：乡镇管理员用来查是否分发的
|
||
.WhereIF(req.is_dispense == 0, c => c.is_dispense == null || c.is_dispense == 0)
|
||
.WhereIF(req.is_dispense == 1, c => c.is_dispense == 1)
|
||
//处理状态：未办理、办理中、已办结
|
||
.WhereIF(req.handle_status_id != null, c => c.handle_status_id == req.handle_status_id)
|
||
//案件状态
|
||
.WhereIF(!string.IsNullOrEmpty(req.case_status_id), c => c.case_status_id == req.case_status_id)
|
||
//开始时间
|
||
.WhereIF(req.report_start_time != null, c => c.createtime >= req.report_start_time)
|
||
//结束时间
|
||
.WhereIF(req.report_end_time != null, c => c.createtime <= req.report_end_time)
|
||
//上报人
|
||
.WhereIF(!string.IsNullOrEmpty(req.report_name), c => c.createusername.Contains(req.report_name))
|
||
//执行人
|
||
.WhereIF(!string.IsNullOrEmpty(req.deal_username), c => c.deal_username.Contains(req.deal_username))
|
||
//判读开始时间
|
||
.WhereIF(req.identification_start_time != null,
|
||
c => c.identification_time >= req.identification_start_time)
|
||
//判读结束时间
|
||
.WhereIF(req.identification_end_time != null, c => c.identification_time <= req.identification_end_time)
|
||
//县id
|
||
.WhereIF(!string.IsNullOrEmpty(req.countyid),
|
||
c => c.countyid.Equals(req.countyid))
|
||
////镇id
|
||
.WhereIF(!string.IsNullOrEmpty(req.streetid), c => c.streetid.Equals(req.streetid))
|
||
////村id
|
||
.WhereIF(!string.IsNullOrEmpty(req.communityid), c => c.communityid.Equals(req.communityid))
|
||
//判读人id
|
||
.WhereIF(!string.IsNullOrEmpty(req.identification_userid),
|
||
c => c.identification_userid == req.identification_userid)
|
||
//判读人
|
||
.WhereIF(!string.IsNullOrEmpty(req.identification_user),
|
||
c => c.identification_user.Contains(req.identification_user))
|
||
//判读人账号
|
||
.WhereIF(!string.IsNullOrEmpty(req.identification_account),
|
||
c => SqlFunc.Subqueryable<SysUser>().Where(it =>
|
||
c.identification_userid == it.Id.ToString() && it.Account == req.identification_account).Any())
|
||
//审核人
|
||
.WhereIF(!string.IsNullOrEmpty(req.verify_user), c => c.verifyuser.Contains(req.verify_user))
|
||
//是否违法
|
||
.WhereIF(req.is_illegal != null, c => c.is_illegal == req.is_illegal)
|
||
////是否核销
|
||
//.WhereIF(req.is_verification == 1, c => c.IsVerification == req.is_verification)
|
||
//.WhereIF(req.is_verification == 0, c => c.IsVerification == null || c.IsVerification == 0)
|
||
//是否退回
|
||
.WhereIF(req.is_drawback == 0, c => c.is_drawback == null || c.is_drawback == 0)
|
||
.WhereIF(req.is_drawback == 1, c => c.is_drawback == 1)
|
||
////是否根据退回给谁过滤
|
||
//.WhereIF(req.is_back_to_userid == 1, c => c.BackToUserid == user.Id.ToString())
|
||
//是否核查过滤
|
||
.WhereIF(req.is_examine == 0, c => c.is_jieshou == null || c.is_jieshou == 0)
|
||
.WhereIF(req.is_examine == 1, c => c.is_jieshou == 1)
|
||
//处理措施
|
||
.WhereIF(!string.IsNullOrEmpty(req.measure_name), c => c.measure_name == req.measure_name)
|
||
//处理措施
|
||
.WhereIF(req.measure_name_type == 0, c => c.measure_name == "拟拆除")
|
||
.WhereIF(req.measure_name_type == 1, c => c.measure_name == "查处")
|
||
.WhereIF(req.measure_name_type == 2, c => c.measure_name == "拟完善手续")
|
||
////工作区过滤
|
||
//.WhereIF(!string.IsNullOrEmpty(req.work_area_name),
|
||
// c => SqlFunc.Subqueryable<SysOrg>()
|
||
// .Where(it => it.Id.ToString() == c.communityid && it.CustomCode == req.work_area_name).Any())
|
||
//审核权限过滤
|
||
.WhereIF(req.is_verify_filter == 1,
|
||
c => flowSorts.Contains(c.verifystatus) && roleNameList.Contains("案件审核"))
|
||
.WhereIF(req.is_verify_filter == 0, c => !flowSorts.Contains(c.verifystatus))
|
||
/*
|
||
* 统计相关用到的过滤条件
|
||
*/
|
||
//超期时间标识
|
||
.WhereIF(req.out_time_flag == 1, c => c.identification_time >= threeTime) //1-3天
|
||
.WhereIF(req.out_time_flag == 2,
|
||
c => c.identification_time < threeTime && c.identification_time > sevenTime) //3-7天
|
||
.WhereIF(req.out_time_flag == 3, c => c.identification_time < sevenTime) //7天以上
|
||
.WhereIF(req.out_time_flag == 4,
|
||
c => c.identification_time > DateTime.Now.AddDays(-30) &&
|
||
c.identification_time < DateTime.Now.AddDays(-7)) //30天内
|
||
.WhereIF(req.out_time_flag == 5, c => c.identification_time < DateTime.Now.AddDays(-30)) //30天以上
|
||
//是否处理
|
||
.WhereIF(req.is_deal == 1,
|
||
c => c.handle_status_id != 0 || (c.handle_status_id == 0 && c.is_jieshou == 1))
|
||
//是否整改完成
|
||
.WhereIF(req.is_complete == 0, c => c.handle_status_id != 2)
|
||
//24小时未核查
|
||
.WhereIF(req.is_not_deal_hour24 == 1,
|
||
c => c.handle_status_id == 0 && (c.is_jieshou == null || c.is_jieshou == 0) &&
|
||
c.identification_time < DateTime.Now.AddDays(-1))
|
||
|
||
//案件编号
|
||
.WhereIF(!string.IsNullOrEmpty(req.case_no), c => c.case_no.Contains(req.case_no))
|
||
//案件类型
|
||
.WhereIF(!string.IsNullOrEmpty(req.typeid), c => c.typeid == req.typeid)
|
||
//地址
|
||
.WhereIF(!string.IsNullOrEmpty(req.address), c => c.address.Contains(req.address))
|
||
//案件描述
|
||
.WhereIF(!string.IsNullOrEmpty(req.case_description),
|
||
c => c.case_description.Contains(req.case_description));
|
||
|
||
|
||
if (!string.IsNullOrEmpty(req.work_area_name))
|
||
{
|
||
var orgList = db.Queryable<SysOrg>().Where(c => c.CustomCode == req.work_area_name).Select(c => c.Id)
|
||
.ToList();
|
||
}
|
||
|
||
//标签检索
|
||
if (!string.IsNullOrEmpty(req.tagids))
|
||
{
|
||
var tags = req.tagids.Trim().Split(",").ToList();
|
||
var ids = db.Queryable<DroneCaseInfoTag>().Where(a => req.tagids.Contains(a.tagid))
|
||
.Select(a => a.caseid)
|
||
.ToList();
|
||
query = query.Where(c => ids.Contains(c.Id));
|
||
}
|
||
|
||
//排序
|
||
if (is_order == 1)
|
||
{
|
||
if (req.is_intact == 1 && req.handle_status_id == 0)
|
||
query = query.OrderBy(c => c.identification_time, OrderByType.Desc);
|
||
else if (req.is_intact == 1 && req.handle_status_id == 1)
|
||
query = query.OrderBy(c => c.deal_time, OrderByType.Desc);
|
||
else if (req.is_intact == 1 && req.handle_status_id == 2)
|
||
query = query.OrderBy(c => c.verifytime, OrderByType.Desc);
|
||
else
|
||
query = query.OrderBy(c => c.createtime, OrderByType.Desc);
|
||
}
|
||
|
||
return query;
|
||
}
|
||
|
||
#region 查询待办任务统计数据
|
||
|
||
/// <summary>
|
||
/// 查询待办任务统计数据
|
||
/// </summary>
|
||
/// <param name="year">年份</param>
|
||
/// <returns></returns>
|
||
|
||
#region 注释修改前方法
|
||
|
||
//public async Task<Response<List<dynamic>>> LoadTaskCount(int year, string tubanlaiyuan, string picihao, int illegal)
|
||
//{
|
||
// Console.WriteLine("开始时间：" + DateTime.Now.ToString());
|
||
// //
|
||
// //获取当前用户
|
||
// 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:DroneCaseInfoZdwt2SchemeCode").Value;
|
||
// // 查询数据
|
||
// var dateTime = year == 0 ? DateTime.Now : new DateTime(year, 1, 1);
|
||
// Console.WriteLine("组织运行结束时间：" + DateTime.Now.ToString());
|
||
// using var db = Repository.AsSugarClient();
|
||
// var query = db.Queryable<WFTask>()
|
||
// .LeftJoin<WFProcess>((t, p) => t.ProcessId == p.Id)
|
||
// .LeftJoin<DroneCaseInfoZdwt2>((t, p, c) => p.Id == c.Id)
|
||
// .Where((t, p, c) => SqlFunc.DateIsSame((DateTime)t.CreateDate, dateTime, DateType.Year))
|
||
// .Where((t, p, c) => c.countyid != null)
|
||
// .Where((t, p, c) => p.SchemeCode.Equals(schemeCode))
|
||
// .WhereIF(!string.IsNullOrEmpty(tubanlaiyuan), (t, p, c) => c.tubanlaiyuan.Equals(tubanlaiyuan))
|
||
// .WhereIF(!string.IsNullOrEmpty(picihao), (t, p, c) => c.tubanlaiyuan.Equals(picihao))
|
||
// .WhereIF(isCity == 0, (t, p, c)
|
||
// => orgIds.Contains(c.countyid) || orgIds.Contains(c.streetid) || orgIds.Contains(c.communityid))
|
||
// .Select((t, p, c) => new
|
||
// {
|
||
// taskid = t.Id,
|
||
// taskstatus = t.State,
|
||
// unitname = t.UnitName, // 初审（待审核） 复审（待销号）
|
||
// isouttime = t.IsOutTime, // 0未超期，1超期，2严重超期
|
||
// isbuildingname = c.is_build_name, // 标注类型 在建 已建成 持续变化
|
||
// isillegal = c.is_illegal, // 0-合法，1-违法，2-其他
|
||
// caseno = c.case_no,
|
||
// countyname = c.countyname,
|
||
// countyid = c.countyid,
|
||
// streetname = c.streetname,
|
||
// streatid = c.streetid,
|
||
// receive = c.is_jieshou,
|
||
// illegaltype = c.weifaleixing, // 违法类型 0非农化1非粮化
|
||
// measurename = c.measure_name, // 整改措施（0拆除复耕1补办手续
|
||
// handstatusid = c.handle_status_id, // 5 已归档
|
||
// handstatusname = c.handle_status_name // 4. 待核销 5. 已
|
||
// });
|
||
// var info = await query.ToListAsync();
|
||
|
||
// Console.WriteLine("主查询运行结束时间：" + DateTime.Now.ToString());
|
||
// // todo 判断当前用户是市级人员还是县级人员 市级待核销任务 县级审核任务
|
||
// // 县级待审核，市级，待销号 县级， （待核销+已核销）/总数 市级，已核销/总数
|
||
// // 查询信息包含 区划 任务id 督办时间 ，案件部门信息 is_illegal weifaleixing measure_name
|
||
// // 如果是市级是不是查整个人临沂市的数据
|
||
// // 返回 level areaid areaname
|
||
// var dataList = new List<dynamic>(info.Count);
|
||
// // 取得所有区县或乡镇信息
|
||
// // //合法：已经归档的合法案件 is_illegal
|
||
// //违法：已经归档的违法案件，未归档的案件 is_illegal
|
||
// //其他：已经归档的其他案件 is_illegal
|
||
// //非粮化：已经归档的非粮化 weifaleixing
|
||
// //补办手续：已经归档的补办手续 measure_name
|
||
// //拆除拆除复耕：已经归档的拆除 measure_name
|
||
// if (isCity == 1)
|
||
// {
|
||
// var groupDataList = info.GroupBy(d => new { d.countyid, d.countyname });
|
||
// foreach (var grouping in groupDataList)
|
||
// {
|
||
// dynamic item = new ExpandoObject();
|
||
// item.level = 1;
|
||
// item.areaid = grouping.Key.countyid; // 区划id
|
||
// item.areaname = grouping.Key.countyname; // 区划名称
|
||
// // 任务总数
|
||
// item.totaltask = grouping.GroupBy(t => t.caseno).Count();
|
||
// // 接收案件任务
|
||
// item.receivetask = grouping.Where(t => t.receive.Equals(1)).GroupBy(t => t.caseno).Count();
|
||
// // 处理状态 0待接收1待判定2待整改3县级审核4市级审核5已归档
|
||
// // 已核销任务数
|
||
// item.verificatedtask =
|
||
// grouping.Where(t => t.handstatusid != null && t.handstatusid.Equals(5)).GroupBy(t => t.caseno)
|
||
// .Count(); //
|
||
// // 超期任务数
|
||
// item.overduetask = grouping.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
|
||
// .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.Where(t =>
|
||
// t.isillegal != null && t.isillegal.Equals(0) && t.handstatusid != null &&
|
||
// t.handstatusid.Equals(5)).GroupBy(t => t.caseno).Count();
|
||
// // 其它案件
|
||
// item.ilegalcase = grouping.Where(t =>
|
||
// t.isillegal != null && t.isillegal.Equals(2) && t.handstatusid != null &&
|
||
// t.handstatusid.Equals(5))
|
||
// .GroupBy(t => t.caseno).Count();
|
||
// // 非粮化
|
||
// item.nonfoodcase = grouping.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.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.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);
|
||
// }
|
||
// }
|
||
// else
|
||
// {
|
||
// var groupDataList = info.GroupBy(d => new { d.streatid, d.streetname });
|
||
// foreach (var grouping in groupDataList)
|
||
// {
|
||
// dynamic item = new ExpandoObject();
|
||
// item.level = 2;
|
||
// item.areaid = grouping.Key.streatid; // 区划id
|
||
// item.areaname = grouping.Key.streetname; // 区划名称
|
||
// item.totaltask = grouping.GroupBy(t => t.caseno).Count(); // 任务总数
|
||
|
||
// // 接收案件任务
|
||
// item.receivetask = grouping.Where(t => t.receive.Equals(1)).GroupBy(t => t.caseno).Count();
|
||
// // 处理状态 0待接收1待判定2待整改3县级审核4市级审核5已归档
|
||
// // 已核销任务数
|
||
// item.verificatedtask =
|
||
// grouping.Where(t => t.handstatusid != null && t.handstatusid.Equals(5)).GroupBy(t => t.caseno)
|
||
// .Count(); //
|
||
// // 超期任务数
|
||
// item.overduetask = grouping.Where(t =>
|
||
// (t.isouttime != null && t.isouttime.Equals(1)) ||
|
||
// (t.isouttime != null && t.isouttime.Equals(2)))
|
||
// .GroupBy(t => t.caseno).Count();
|
||
// // 待审核任务数
|
||
// item.verifytask = grouping
|
||
// .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.Where(t =>
|
||
// t.isillegal != null && t.isillegal.Equals(0) && t.handstatusid != null &&
|
||
// t.handstatusid.Equals(5)).GroupBy(t => t.caseno).Count();
|
||
// // 其它案件
|
||
// item.ilegalcase = grouping.Where(t =>
|
||
// t.isillegal != null && t.isillegal.Equals(2) && t.handstatusid != null &&
|
||
// t.handstatusid.Equals(5))
|
||
// .GroupBy(t => t.caseno).Count();
|
||
// // 非粮化
|
||
// item.nonfoodcase = grouping.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.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.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
|
||
// };
|
||
//}
|
||
|
||
#endregion
|
||
|
||
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:DroneCaseInfoZdwt2SchemeCode").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<DroneCaseInfoZdwt2>((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));
|
||
//.WhereIF(isCity == 0, (t, p, c)
|
||
// => c.countyusable.Equals(1));
|
||
|
||
// 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<DroneCaseInfoZdwt2>()
|
||
.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.issplit.Equals(1) || t.isclosed.Equals(0))
|
||
/*.WhereIF(illegal != 0,
|
||
t => t.isillegal.Equals(0) || t.isillegal.Equals(2) ||
|
||
(t.isillegal.Equals(1) && t.illegaltype.Equals(1))) // 审核监督（填报审核）
|
||
.WhereIF(illegal == 0, t => t.isillegal.Equals(1) && t.illegaltype.Equals(0)) // 整改审核 */
|
||
.Where(t => t.unitname != null && t.unitname.Equals("市级审核") && t.taskstatus != null &&
|
||
t.taskstatus.Equals(1)).GroupBy(t => t.caseno).Count();
|
||
//合法案件
|
||
item.legalcase = grouping.list.Where(t =>
|
||
t.isillegal != null && t.isillegal.Equals(0) && t.handstatusid != null &&
|
||
t.handstatusid.Equals(5)).GroupBy(t => t.caseno).Count();
|
||
// 其它案件
|
||
item.ilegalcase = grouping.list.Where(t =>
|
||
t.isillegal != null && t.isillegal.Equals(2) && t.handstatusid != null &&
|
||
t.handstatusid.Equals(5))
|
||
.GroupBy(t => t.caseno).Count();
|
||
// 非粮化
|
||
item.nonfoodcase = grouping.list.Where(t =>
|
||
t.isillegal != null && t.isillegal.Equals(1) && t.illegaltype != null &&
|
||
t.illegaltype.Equals(1) &&
|
||
t.handstatusid != null &&
|
||
t.handstatusid.Equals(5)).GroupBy(t => t.caseno).Count();
|
||
// 补办手续 整改措施（0拆除复耕1补办手续）
|
||
item.makeupcase = grouping.list.Where(t =>
|
||
t.handstatusid != null && t.handstatusid.Equals(5) && t.isillegal != null &&
|
||
t.isillegal.Equals(1) && t.measurename != null && t.measurename.Equals("1"))
|
||
.GroupBy(t => t.caseno)
|
||
.Count();
|
||
// 复耕
|
||
item.rehabilitationcase = grouping.list.Where(t =>
|
||
t.handstatusid != null && t.handstatusid.Equals(5) && t.isillegal != null &&
|
||
t.isillegal.Equals(1) && t.measurename != null && t.measurename.Equals("0"))
|
||
.GroupBy(t => t.caseno)
|
||
.Count();
|
||
// 市级，已核销/总数
|
||
// 县级（待审核） 市级(待销号）
|
||
dataList.Add(item);
|
||
}
|
||
|
||
|
||
dataList = dataList.OrderBy(a => a.sortno).ToList();
|
||
}
|
||
else
|
||
{
|
||
var groupDataList = await query.GroupBy((t, p, c) => new { c.streetid, c.streetname }).Select(
|
||
(t, p, c) =>
|
||
new
|
||
{
|
||
c.streetid,
|
||
c.streetname,
|
||
list = SqlFunc.Subqueryable<DroneCaseInfoZdwt2>()
|
||
.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.issplit.Equals(1) || t.isclosed.Equals(0))
|
||
.Where(t => t.unitname != null && t.unitname.Equals("县级审核") && t.taskstatus != null &&
|
||
t.taskstatus.Equals(1))
|
||
/*.WhereIF(illegal != 0,
|
||
t => t.isillegal.Equals(0) || t.isillegal.Equals(2) ||
|
||
(t.isillegal.Equals(1) && t.illegaltype.Equals(1))) // 审核监督
|
||
.WhereIF(illegal == 0, t => t.isillegal.Equals(1) && t.illegaltype.Equals(0)) // 违法*/
|
||
.GroupBy(t => t.caseno).Count();
|
||
//合法案件
|
||
item.legalcase = grouping.list.Where(t =>
|
||
t.isillegal != null && t.isillegal.Equals(0) && t.handstatusid != null &&
|
||
t.handstatusid.Equals(5)).GroupBy(t => t.caseno).Count();
|
||
// 其它案件
|
||
item.ilegalcase = grouping.list.Where(t =>
|
||
t.isillegal != null && t.isillegal.Equals(2) && t.handstatusid != null &&
|
||
t.handstatusid.Equals(5))
|
||
.GroupBy(t => t.caseno).Count();
|
||
// 非粮化
|
||
item.nonfoodcase = grouping.list.Where(t =>
|
||
t.isillegal != null && t.isillegal.Equals(1) && t.illegaltype != null &&
|
||
t.illegaltype.Equals(1) &&
|
||
t.handstatusid != null &&
|
||
t.handstatusid.Equals(5)).GroupBy(t => t.caseno).Count();
|
||
// 补办手续
|
||
item.makeupcase = grouping.list.Where(t =>
|
||
t.handstatusid != null && t.handstatusid.Equals(5) && t.isillegal != null &&
|
||
t.isillegal.Equals(1) && t.measurename != null && t.measurename.Equals("1"))
|
||
.GroupBy(t => t.caseno)
|
||
.Count();
|
||
// 复耕
|
||
item.rehabilitationcase = grouping.list.Where(t =>
|
||
t.handstatusid != null && t.handstatusid.Equals(5) && t.isillegal != null &&
|
||
t.isillegal.Equals(1) && t.measurename != null && t.measurename.Equals("0"))
|
||
.GroupBy(t => t.caseno)
|
||
.Count();
|
||
dataList.Add(item);
|
||
}
|
||
}
|
||
|
||
Console.WriteLine("统计计算结束时间：" + DateTime.Now.ToString());
|
||
return new Response<List<dynamic>>
|
||
{
|
||
Result = dataList
|
||
};
|
||
}
|
||
|
||
#endregion
|
||
|
||
|
||
/// <summary>
|
||
/// 查询案件统计数据
|
||
/// </summary>
|
||
/// <param name="year">年份</param>
|
||
/// <returns></returns>
|
||
public async Task<Response<List<dynamic>>> LoadDroneCaseInfoCount(int year)
|
||
{
|
||
//获取当前用户
|
||
var user = _auth.GetCurrentUser().User;
|
||
//该用户下包含所有部门
|
||
List<string> deplist = new List<string>();
|
||
long level = 0;
|
||
if (user != null && user.Id != -1)
|
||
{
|
||
var departs = client.Queryable<SysUserOrg>().Where(r => r.UserId == user.Id).ToList();
|
||
foreach (var item in departs)
|
||
{
|
||
var list = client.Queryable<SysOrg>().ToChildList(it => it.ParentId, item.OrgId)
|
||
?.Select(it => it.Id.ToString()).ToList();
|
||
deplist = deplist.Union(list).ToList();
|
||
}
|
||
|
||
//获取用户分配部门的最高级别
|
||
var uorg = departs.OrderBy(r => r.Level).FirstOrDefault();
|
||
if (uorg != null)
|
||
{
|
||
level = uorg.Level;
|
||
}
|
||
}
|
||
|
||
DateTime begintime = DateTime.Now.AddYears(-1);
|
||
DateTime endtime = DateTime.Now;
|
||
if (year != 0)
|
||
{
|
||
begintime = Convert.ToDateTime(year.ToString() + "-01-01");
|
||
endtime = Convert.ToDateTime((year + 1).ToString() + "-01-01");
|
||
}
|
||
|
||
//查询数据
|
||
var query = client.Queryable<DroneCaseInfoZdwt2>()
|
||
.WhereIF(year != 0, r => r.createtime >= begintime && r.createtime < endtime)
|
||
//查询县区可看的数据
|
||
//.WhereIF(level != 0, r => r.countyusable.Equals(1))
|
||
.WhereIF(user.Id != -1,
|
||
r => deplist.Contains(r.countyid) || deplist.Contains(r.streetid) || deplist.Contains(r.countyid));
|
||
List<dynamic> res = new List<dynamic>();
|
||
//按级别聚合
|
||
switch (level)
|
||
{
|
||
case 0:
|
||
|
||
res = await query.GroupBy(c => new { c.countyid, c.countyname })
|
||
.Select<dynamic>(c => new
|
||
{
|
||
areaid = c.countyid,
|
||
areaname = c.countyname,
|
||
level = 1,
|
||
count = SqlFunc.AggregateCount(c.Id)
|
||
}).ToListAsync();
|
||
// Process result0
|
||
break;
|
||
case 1:
|
||
res = await query.GroupBy(c => new { c.streetid, c.streetname })
|
||
.Select<dynamic>(c => new
|
||
{
|
||
areaid = c.streetid,
|
||
areaname = c.streetname,
|
||
level = 2,
|
||
count = SqlFunc.AggregateCount(c.Id)
|
||
})
|
||
.ToListAsync();
|
||
// Process result1
|
||
break;
|
||
case 2:
|
||
res = await query.GroupBy(c => new { c.communityid, c.communityname })
|
||
.Select<dynamic>(c => new
|
||
{
|
||
areaid = c.communityid,
|
||
areaname = c.communityname,
|
||
level = 3,
|
||
count = SqlFunc.AggregateCount(c.Id)
|
||
})
|
||
.ToListAsync();
|
||
// Process result2
|
||
break;
|
||
default:
|
||
// Handle default case
|
||
break;
|
||
}
|
||
|
||
return new Response<List<dynamic>>
|
||
{
|
||
Result = res
|
||
};
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 查询案件详细信息
|
||
/// </summary>
|
||
/// <param name="req">请求数据信息</param>
|
||
/// <returns></returns>
|
||
public async Task<Response<PageInfo<List<dynamic>>>> LoadDroneCaseInfoDetail(DroneInfoStatisticsReq req)
|
||
{
|
||
//定义且实例化分页数据
|
||
RefAsync<int> totalcount = 0;
|
||
|
||
//获取当前用户
|
||
var user = _auth.GetCurrentUser().User;
|
||
//该用户下包含所有部门
|
||
List<string> deplist = new List<string>();
|
||
long level = 0;
|
||
if (user != null && user.Id != -1)
|
||
{
|
||
var departs = client.Queryable<SysUserOrg>().Where(r => r.UserId == user.Id).ToList();
|
||
foreach (var item in departs)
|
||
{
|
||
var list = client.Queryable<SysOrg>().ToChildList(it => it.ParentId, item.OrgId)
|
||
?.Select(it => it.Id.ToString()).ToList();
|
||
deplist = deplist.Union(list).ToList();
|
||
}
|
||
|
||
//获取用户分配部门的最高级别
|
||
var uorg = departs.OrderBy(r => r.Level).FirstOrDefault();
|
||
if (uorg != null)
|
||
{
|
||
level = uorg.Level;
|
||
}
|
||
}
|
||
|
||
DateTime begintime = DateTime.Now.AddYears(-1);
|
||
DateTime endtime = DateTime.Now;
|
||
if (req.year != 0)
|
||
{
|
||
begintime = Convert.ToDateTime(req.year.ToString() + "-01-01");
|
||
endtime = Convert.ToDateTime((req.year + 1).ToString() + "-01-01");
|
||
}
|
||
|
||
//查询数
|
||
if (req.type == 0)
|
||
{
|
||
var info = await client.Queryable<DroneCaseInfoZdwt2>().LeftJoin<DroneCaseFavorite>((r, c) =>
|
||
r.case_no == c.CaseNo && c.FavoriteUserId == user.Id.ToString())
|
||
.WhereIF(req.year != 0, r => r.createtime >= begintime && r.createtime < endtime) //年份
|
||
//.WhereIF(level != 0, r => r.countyusable.Equals(1))//查询县区可看的数据
|
||
.WhereIF(req.level == 1, r => r.countyid == req.areaid) //统计页面传的区域id
|
||
.WhereIF(req.level == 2, r => r.streetid == req.areaid)
|
||
.WhereIF(req.level == 3, r => r.communityid == req.areaid)
|
||
.WhereIF(!string.IsNullOrEmpty(req.countyid), r => r.countyid == req.countyid) //县
|
||
.WhereIF(!string.IsNullOrEmpty(req.streetid), r => r.streetid == req.streetid) //镇
|
||
.WhereIF(!string.IsNullOrEmpty(req.geomid), r => r.geomid == req.geomid) //图斑号
|
||
.WhereIF(req.is_illegal != null, r => r.is_illegal == req.is_illegal) //图斑类型
|
||
.WhereIF(req.weifaleixing != null, r => r.weifaleixing == req.weifaleixing) //违法类型
|
||
.WhereIF(!string.IsNullOrEmpty(req.measure_name), r => r.measure_name == req.measure_name) //整改措施
|
||
.WhereIF(req.is_build_complete != null, r => r.is_build_complete == req.is_build_complete) //标注类型
|
||
.WhereIF(req.handle_status_id != null, r => r.handle_status_id == req.handle_status_id) //图斑状态
|
||
.WhereIF(user.Id != -1,
|
||
r => deplist.Contains(r.countyid) || deplist.Contains(r.streetid) ||
|
||
deplist.Contains(r.countyid)) //非超级管理员用户筛选
|
||
.OrderByIF(string.IsNullOrEmpty(req.sort), r => r.createtime)
|
||
.Select<dynamic>((r, c) => new
|
||
{
|
||
id = r.Id, //主键
|
||
r.case_name, //名称
|
||
r.case_description, //描述
|
||
r.case_no, //案件编号
|
||
r.geomid,
|
||
r.address, //地址
|
||
r.createtime, //时间
|
||
r.area, //面积（总）
|
||
r.nongyongdi_area, //农用地面积
|
||
r.gengdi_area, //耕地面积
|
||
r.yongjiujibennongtian_area, //永久基本农田面积
|
||
r.zhongdianquyu_area, //重点区域面积
|
||
r.shengtaibaohuhongxian_area, //生态保护红线面积
|
||
r.guotukongjianguihua_area, //国土空间规划面积
|
||
Fid = c.Id
|
||
}).ToPageListAsync(req.page, req.limit, totalcount);
|
||
return new Response<PageInfo<List<dynamic>>>
|
||
{
|
||
Result = new PageInfo<List<dynamic>>
|
||
{
|
||
Items = info,
|
||
Total = totalcount
|
||
}
|
||
};
|
||
}
|
||
else
|
||
{
|
||
//查询数据
|
||
var info = await client.Queryable<DroneCaseInfoZdwt2>()
|
||
.RightJoin<DroneCaseFavorite>((r, c) =>
|
||
r.case_no == c.CaseNo && c.FavoriteUserId == user.Id.ToString())
|
||
.WhereIF(req.year != 0, r => r.createtime >= begintime && r.createtime < endtime) //年份
|
||
//.WhereIF(level != 0, r => r.countyusable.Equals(1))//查询县区可看的数据
|
||
.WhereIF(req.level == 1, r => r.countyid == req.areaid) //统计页面传的区域id
|
||
.WhereIF(req.level == 2, r => r.streetid == req.areaid)
|
||
.WhereIF(req.level == 3, r => r.communityid == req.areaid)
|
||
.WhereIF(!string.IsNullOrEmpty(req.countyid), r => r.countyid == req.countyid) //县
|
||
.WhereIF(!string.IsNullOrEmpty(req.streetid), r => r.streetid == req.streetid) //镇
|
||
.WhereIF(!string.IsNullOrEmpty(req.geomid), r => r.geomid == req.geomid) //图斑号
|
||
.WhereIF(req.is_illegal != null, r => r.is_illegal == req.is_illegal) //图斑类型
|
||
.WhereIF(req.weifaleixing != null, r => r.weifaleixing == req.weifaleixing) //违法类型
|
||
.WhereIF(!string.IsNullOrEmpty(req.measure_name), r => r.measure_name == req.measure_name) //整改措施
|
||
.WhereIF(req.is_build_complete != null, r => r.is_build_complete == req.is_build_complete) //标注类型
|
||
.WhereIF(req.handle_status_id != null, r => r.handle_status_id == req.handle_status_id) //图斑状态
|
||
.WhereIF(user.Id != -1,
|
||
r => deplist.Contains(r.countyid) || deplist.Contains(r.streetid) ||
|
||
deplist.Contains(r.countyid)) //非超级管理员用户筛选
|
||
.OrderByIF(string.IsNullOrEmpty(req.sort), r => r.createtime)
|
||
.Select<dynamic>((r, c) => new
|
||
{
|
||
id = r.Id, //主键
|
||
r.case_name, //名称
|
||
r.case_description, //描述
|
||
r.case_no, //案件编号
|
||
r.geomid,
|
||
r.address, //地址
|
||
r.createtime, //时间
|
||
r.area, //面积（总）
|
||
r.nongyongdi_area, //农用地面积
|
||
r.gengdi_area, //耕地面积
|
||
r.yongjiujibennongtian_area, //永久基本农田面积
|
||
r.zhongdianquyu_area, //重点区域面积
|
||
r.shengtaibaohuhongxian_area, //生态保护红线面积
|
||
r.guotukongjianguihua_area, //国土空间规划面积
|
||
Fid = c.Id
|
||
}).ToPageListAsync(req.page, req.limit, totalcount);
|
||
return new Response<PageInfo<List<dynamic>>>
|
||
{
|
||
Result = new PageInfo<List<dynamic>>
|
||
{
|
||
Items = info,
|
||
Total = totalcount
|
||
}
|
||
};
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 查询任务列表数据
|
||
/// </summary>
|
||
/// <param name="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:DroneCaseInfoZdwt2SchemeCode").Value;
|
||
// 查询数据
|
||
var dateTime = req.year == 0 ? DateTime.Now : new DateTime(req.year, 1, 1);
|
||
int totalCount = 0;
|
||
using (var db = Repository.AsSugarClient())
|
||
{
|
||
var info = db.Queryable<WFTask>()
|
||
.LeftJoin<WFProcess>((t, p) => t.ProcessId == p.Id && p.SchemeCode.Equals(schemeCode))
|
||
.LeftJoin<DroneCaseInfoZdwt2>((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 == 0 || c.is_split == 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.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
|
||
}).ToPageList(req.page, req.limit, ref totalCount);
|
||
return new Response<PageInfo<List<dynamic>>>
|
||
{
|
||
Result = new PageInfo<List<dynamic>>
|
||
{
|
||
Items = info,
|
||
Total = totalCount
|
||
}
|
||
};
|
||
}
|
||
|
||
|
||
//
|
||
/*foreach (dynamic o in info)
|
||
{
|
||
var taskId = (string)o.taskeid;
|
||
var schemeId = (string)o.schemeid;
|
||
// 查询任务信息
|
||
using var db = Repository.AsSugarClient();
|
||
var wfTask = await db.Queryable<WFTask>().Where(a => a.Id.Equals(taskId)).FirstAsync();
|
||
var unitId = wfTask.UnitId;
|
||
var isSupervise = wfTask.IsSupervise;
|
||
//未超时，短暂超时和严重超时
|
||
o.supervisestate = 0; //未超时
|
||
o.cancelsupervise = 0; // 是否可取消督办
|
||
// 督办任务
|
||
if (isSupervise.Equals(1))
|
||
{
|
||
var superviseTime = wfTask.SuperviseTime;
|
||
var wfScheme = await db.Queryable<WFScheme>().Where(a => a.Id.Equals(schemeId)).FirstAsync();
|
||
var contentJObject = (JObject)JsonConvert.DeserializeObject(wfScheme.Content);
|
||
var wfData = (JArray)contentJObject["wfData"];
|
||
foreach (var unit in wfData)
|
||
{
|
||
var id = unit["id"]?.ToString();
|
||
if (id != null && id.Equals(unitId))
|
||
{
|
||
// 超时通知 isOvertimeMessage
|
||
//第一次通知(时) overtimeMessageStart
|
||
//通知间隔(时) overtimeMessageInterval 1
|
||
// 超时流转时间(时) overtimeGo 12
|
||
// 超时通知策略 overtimeMessageType
|
||
// todo 查询超时配置
|
||
if (unit["isOvertimeMessage"].ToBool())
|
||
{
|
||
DateTime now = DateTime.Now;
|
||
var overtimeMessageInterval = unit["overtimeMessageInterval"].ToInt();
|
||
var overtimeGo = unit["overtimeGo"].ToInt();
|
||
if (now
|
||
.CompareTo(superviseTime.ToDateTime()
|
||
.AddHours(overtimeGo + overtimeMessageInterval)) > 0)
|
||
{
|
||
// 严重超期
|
||
o.supervisestate = 2;
|
||
}
|
||
else if (now.CompareTo(superviseTime.ToDateTime().AddHours(overtimeGo)) > 0)
|
||
{
|
||
o.supervisestate = 1; // 短暂超期
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
o.cancelsupervise = 0;
|
||
var superviseId = (long)o.superviseid;
|
||
// 设置督办是否可取消
|
||
if (user.Id.Equals(-1))
|
||
{
|
||
o.cancelsupervise = 1;
|
||
}
|
||
else
|
||
{
|
||
//当前用户所属部门
|
||
var orgIds = authStrategyContext.Orgs.Select(x => x.Id).ToList();
|
||
var count = await db.Queryable<SysUser>()
|
||
.LeftJoin<SysUserOrg>((u, uo) => u.Id == uo.UserId)
|
||
.LeftJoin<SysOrg>((u, uo, g) => uo.OrgId == g.Id)
|
||
.Where($"string_to_array(g.\"CascadeId\",'.') && ARRAY['{string.Join(",", orgIds)}']")
|
||
.Where((u, uo, g) => u.Id == superviseId).CountAsync();
|
||
if (count > 0)
|
||
{
|
||
o.cancelsupervise = 1;
|
||
}
|
||
}
|
||
}
|
||
}*/
|
||
}
|
||
|
||
/// <summary>
|
||
/// 查询任务列表数据
|
||
/// </summary>
|
||
/// <param name="areaid">区域id</param>
|
||
/// <param name="level">等级</param>
|
||
/// <param name="year">年</param>
|
||
/// <param name="is_build_complete">标注类型</param>
|
||
/// <param name="geomid">图斑号</param>
|
||
/// <returns></returns>
|
||
public async Task<Response<PageInfo<List<dynamic>>>> LoadTaskIllegalDetailList(TaskDetailReq req)
|
||
{
|
||
// todo 批次号，
|
||
//获取当前用户
|
||
var authStrategyContext = _auth.GetCurrentUser();
|
||
var user = authStrategyContext.User;
|
||
var orgList = authStrategyContext.Orgs;
|
||
var leafNode = new List<SysOrg>();
|
||
// 统计该区划下数据 计算那些祖先节点不存在于列表中的
|
||
var isCity = 0;
|
||
if (user.Id == -1)
|
||
{
|
||
isCity = 1;
|
||
}
|
||
else
|
||
{
|
||
foreach (var sysOrg in orgList)
|
||
{
|
||
if (sysOrg.ParentId == 0)
|
||
{
|
||
isCity = 1;
|
||
orgList = orgList.Where(t => t.ParentId.Equals(0)).ToList();
|
||
break;
|
||
}
|
||
|
||
var ids = sysOrg.CascadeId.Split(".");
|
||
if (orgList.Any(org => org.Id != sysOrg.Id && ids.Contains(org.Id.ToString())))
|
||
{
|
||
leafNode.Add(sysOrg);
|
||
}
|
||
}
|
||
|
||
if (isCity != 1) // 顶级节点
|
||
{
|
||
orgList = orgList.Except(leafNode).ToList();
|
||
}
|
||
}
|
||
|
||
var orgIds = orgList.Select(x => x.Id.ToString()).ToList();
|
||
var schemeCode = _configuration.GetSection("AppSetting:DroneCaseInfoZdwt2SchemeCode").Value;
|
||
// 查询数据
|
||
var dateTime = req.year == 0 ? DateTime.Now : new DateTime(req.year, 1, 1);
|
||
RefAsync<int> totalCount = 0;
|
||
using var db = Repository.AsSugarClient();
|
||
var info = await db.Queryable<WFTask>()
|
||
.LeftJoin<WFProcess>((t, p) => t.ProcessId == p.Id)
|
||
.LeftJoin<DroneCaseInfoZdwt2>((t, p, c) => SqlFunc.JsonField(p.InstanceInfo, "pkeyValue") == c.Id)
|
||
.LeftJoin<DroneTaskFavorite>((t, p, c, f) => f.TaskId == t.Id && f.FavoriteUserId == user.Id.ToString())
|
||
.Where((t, p, c, f) => SqlFunc.DateIsSame((DateTime)c.synchronoustime, dateTime, DateType.Year))
|
||
.Where((t, p, c, f) => c.countyid != null)
|
||
.Where((t, p, c, f) => c.weifaleixing.Equals(0)) // 非农化
|
||
.Where((t, p, c, f) => c.is_illegal.Equals(1)) // 违法
|
||
.Where((t, p, c, f) => p.SchemeCode.Equals(schemeCode))
|
||
.WhereIF(!string.IsNullOrEmpty(req.tubanlaiyuan),
|
||
(t, p, c, f) => c.tubanlaiyuan.Equals(req.tubanlaiyuan))
|
||
.WhereIF(!string.IsNullOrEmpty(req.picihao), (t, p, c, f) => c.tubanlaiyuan.Equals(req.picihao))
|
||
// 激活状态
|
||
.Where((t, p, c, f) => t.State.Equals(1))
|
||
.WhereIF(req.type == 1, (t, p, c, f) => f.FavoriteUserId.Equals(user.Id.ToString()))
|
||
.WhereIF(isCity == 0, (t, p, c, f)
|
||
=> (orgIds.Contains(c.countyid) || orgIds.Contains(c.streetid) || orgIds.Contains(c.communityid)) &&
|
||
t.UnitName.Equals("县级审核"))
|
||
.WhereIF(isCity == 1, (t, p, c, f) => t.UnitName.Equals("市级审核"))
|
||
//.WhereIF(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))
|
||
// 标注类型-> 非粮化 拆除复耕 补办手续
|
||
.WhereIF(!string.IsNullOrEmpty(req.is_build_name) && req.is_build_name.Equals("非粮化"),
|
||
(t, p, c, f) => c.weifaleixing.Equals(1))
|
||
.WhereIF(!string.IsNullOrEmpty(req.is_build_name) && req.is_build_name.Equals("拆除复耕"),
|
||
(t, p, c, f) => c.measure_name.Equals("0"))
|
||
.WhereIF(!string.IsNullOrEmpty(req.is_build_name) && req.is_build_name.Equals("补办手续"),
|
||
(t, p, c, f) => c.measure_name.Equals("1"))
|
||
.WhereIF(string.IsNullOrEmpty(req.is_build_name),
|
||
(t, p, c, f) => c.is_illegal.Equals(1))
|
||
.WhereIF(!string.IsNullOrEmpty(req.areaid),
|
||
(t, p, c, f) => c.countyid.Equals(req.areaid) || c.streetid.Equals(req.areaid))
|
||
// 驳回过滤
|
||
.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
|
||
}
|
||
};
|
||
}
|
||
|
||
public void GenModel()
|
||
{
|
||
using (var db = Repository.AsSugarClient())
|
||
{
|
||
db.DbFirst.IsCreateAttribute().StringNullable().CreateClassFile("c:\\Demo\\1", "Models");
|
||
}
|
||
}
|
||
|
||
public void UpdateDroneCaseInfo(DronCaseSingleCityUpdateReq req)
|
||
{
|
||
var authStrategyContext = _auth.GetCurrentUser();
|
||
var user = authStrategyContext.User;
|
||
|
||
/* try
|
||
{*/
|
||
|
||
if (!string.IsNullOrEmpty(req.communityid) && !string.IsNullOrEmpty(req.communityname))
|
||
{
|
||
client.Updateable<DroneCaseInfoZdwt2>().SetColumns(c => new DroneCaseInfoZdwt2
|
||
{
|
||
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<DroneCaseInfoZdwt2>().SetColumns(c => new DroneCaseInfoZdwt2
|
||
{
|
||
countyid = req.countyid,
|
||
countyname = req.countyname,
|
||
streetid = req.streetid,
|
||
streetname = req.streetname
|
||
}).Where(a => a.Id == req.Id).ExecuteCommand();
|
||
}
|
||
|
||
// 根据案件id取得流程
|
||
/* var process = await Repository.ChangeRepository<SugarRepositiry<WFProcess>>().AsQueryable()
|
||
.Where(t => SqlFunc.JsonField(t.InstanceInfo, "pkeyValue").Equals(req.Id)&&t.EnabledMark==1)
|
||
.FirstAsync();
|
||
var original1 = await db.Queryable<DroneCaseInfoZdwt2>().FirstAsync(t => t.Id == req.Id);
|
||
|
||
var caseInfo = original1.MapTo<DroneCaseInfoZdwt2>();
|
||
// 作废流程
|
||
var result = _processApp.DeleteProcess(process.Id);
|
||
// 发起新流程
|
||
var processId = Guid.NewGuid().ToString();
|
||
var instanceInfo = process.InstanceInfo;
|
||
instanceInfo.pkeyValue = req.Id;
|
||
string x = Json.ToJson(instanceInfo);
|
||
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);
|
||
}
|
||
catch (Exception e)
|
||
{
|
||
await db.Ado.RollbackTranAsync();
|
||
throw e;
|
||
}*/
|
||
}
|
||
|
||
public string dealIllegalCaseInfo(DealIllegalCaseInfo req)
|
||
{
|
||
string sql = $"update drone_caseinfo_zdwt2 set result_name='" + req.resultName + "',registr_number='" +
|
||
req.registrNumber + "',illegal_contact='" + req.illegalContact +
|
||
"',illegal_shenfenzhenghao='" +
|
||
req.illegalShenfenzhenghao + "' where \"Id\"='" + req.id + "'";
|
||
client.Ado.ExecuteCommand(sql);
|
||
return "修改成功";
|
||
}
|
||
|
||
public string dronecaseinfoFavarite(string caseInfo, int type)
|
||
{
|
||
var user = _auth.GetCurrentUser().User;
|
||
string id = Guid.NewGuid().ToString();
|
||
string sql = "";
|
||
if (type == 1)
|
||
{
|
||
sql = sql + "insert into drone_case_favorite values(" + id + "," + caseInfo + "," + user.Id + ")";
|
||
}
|
||
|
||
if (type == 0)
|
||
{
|
||
sql = sql + "delete from drone_case_favorite where \"CaseNo\"=" + caseInfo +
|
||
" and \"FavoriteUserId\"=" +
|
||
user.Id;
|
||
}
|
||
|
||
client.Ado.ExecuteCommand(sql);
|
||
return "收藏成功";
|
||
}
|
||
|
||
public async Task<Response<PageInfo<List<DroneCaseFavorite>>>> getFavariteList(int page, int limit)
|
||
{
|
||
var user = _auth.GetCurrentUser().User;
|
||
RefAsync<int> totalNumber = 0;
|
||
var table = await client.Queryable<DroneCaseFavorite>().Where(o => long.Parse(o.FavoriteUserId) == user.Id)
|
||
.ToPageListAsync(page, limit, totalNumber);
|
||
return new Response<PageInfo<List<DroneCaseFavorite>>>()
|
||
{
|
||
Result = new PageInfo<List<DroneCaseFavorite>>
|
||
{
|
||
Items = table,
|
||
Total = totalNumber
|
||
}
|
||
};
|
||
}
|
||
|
||
public async Task<Response<bool>> AddCaseFavorite(DroneCaseFavorite model)
|
||
{
|
||
var _count = await base.Repository.ChangeRepository<SugarRepositiry<DroneCaseFavorite>>().AsQueryable()
|
||
.Where(c => c.CaseNo == model.CaseNo && c.FavoriteUserId == model.FavoriteUserId).CountAsync();
|
||
if (_count > 0)
|
||
return new Response<bool>
|
||
{
|
||
Code = 500,
|
||
Result = false,
|
||
Message = "案件已收藏"
|
||
};
|
||
var flag = await base.Repository.ChangeRepository<SugarRepositiry<DroneCaseFavorite>>().InsertAsync(model);
|
||
if (flag)
|
||
return new Response<bool>
|
||
{
|
||
Result = true,
|
||
Message = "收藏成功"
|
||
};
|
||
else
|
||
return new Response<bool>
|
||
{
|
||
Code = 500,
|
||
Result = false,
|
||
Message = "收藏失败"
|
||
};
|
||
}
|
||
|
||
public async Task<Response<bool>> DeleteFavoriteCase(string id)
|
||
{
|
||
var flag = await base.Repository.ChangeRepository<SugarRepositiry<DroneCaseFavorite>>().DeleteByIdAsync(id);
|
||
if (flag)
|
||
return new Response<bool>
|
||
{
|
||
Result = true,
|
||
Message = "删除成功"
|
||
};
|
||
else
|
||
return new Response<bool>
|
||
{
|
||
Code = 500,
|
||
Result = false,
|
||
Message = "删除失败"
|
||
};
|
||
}
|
||
|
||
public async Task<Response<bool>> AddTaskFavorite(DroneTaskFavorite model)
|
||
{
|
||
var _count = await base.Repository.ChangeRepository<SugarRepositiry<DroneTaskFavorite>>().AsQueryable()
|
||
.Where(c => c.TaskId == model.TaskId && c.FavoriteUserId == model.FavoriteUserId).CountAsync();
|
||
if (_count > 0)
|
||
return new Response<bool>
|
||
{
|
||
Code = 500,
|
||
Result = false,
|
||
Message = "案件已收藏"
|
||
};
|
||
var flag = await base.Repository.ChangeRepository<SugarRepositiry<DroneTaskFavorite>>().InsertAsync(model);
|
||
if (flag)
|
||
return new Response<bool>
|
||
{
|
||
Result = true,
|
||
Message = "收藏成功"
|
||
};
|
||
else
|
||
return new Response<bool>
|
||
{
|
||
Code = 500,
|
||
Result = false,
|
||
Message = "收藏失败"
|
||
};
|
||
}
|
||
|
||
public async Task<Response<bool>> DeleteTaskFavoriteCase(string id)
|
||
{
|
||
var flag = await base.Repository.ChangeRepository<SugarRepositiry<DroneTaskFavorite>>().DeleteByIdAsync(id);
|
||
if (flag)
|
||
return new Response<bool>
|
||
{
|
||
Result = true,
|
||
Message = "删除成功"
|
||
};
|
||
else
|
||
return new Response<bool>
|
||
{
|
||
Code = 500,
|
||
Result = false,
|
||
Message = "删除失败"
|
||
};
|
||
}
|
||
|
||
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:DroneCaseInfoZdwt2SchemeCode").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_zdwt2 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_zdwt2 c on b.\"InstanceInfo\"->>'pkeyValue'=c.\"Id\" where a.\"ProcessCode\"='" +
|
||
schemeCode + "' ";
|
||
}
|
||
|
||
sqlpart = sqlpart + $" and a.\"UnitName\" in ('待填报','待接收','待整改')";
|
||
// 任务状态 1-激活状态 3-
|
||
sqlpart = sqlpart + "and (a.\"State\"=1 or (a.\"State\"= 7 and c.is_split = 1))";
|
||
if (req.isIllegal < 100)
|
||
{
|
||
sqlpart = sqlpart + $" and c.is_illegal=" + req.isIllegal + "";
|
||
}
|
||
//查询县区可看的数据
|
||
//if (level != 0)
|
||
//{
|
||
// sqlpart = sqlpart + $" and c.countyusable = 1";
|
||
//}
|
||
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.weifaleixing < 100)
|
||
{
|
||
sqlpart = sqlpart + $" and c.weifaleixing=" + req.weifaleixing + "";
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(req.measureName))
|
||
{
|
||
sqlpart = sqlpart + $" and c.measure_name='" + req.measureName + "'";
|
||
}
|
||
|
||
if (0 == req.landType)
|
||
{
|
||
sqlpart = sqlpart + $" and c.typename like '%农用地%'";
|
||
}
|
||
else if (1 == req.landType)
|
||
{
|
||
sqlpart = sqlpart + $" and c.typename like '%建设用地%'";
|
||
}
|
||
else if (2 == req.landType)
|
||
{
|
||
sqlpart = sqlpart + $" and c.typename like '%推堆土%'";
|
||
}
|
||
|
||
if (req.year != 0)
|
||
{
|
||
sqlpart = sqlpart + $" and EXTRACT(YEAR FROM synchronoustime) = {req.year}";
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(req.tubanlaiyuan))
|
||
{
|
||
sqlpart = sqlpart + $" and c.tubanlaiyuan='" + req.tubanlaiyuan + "'";
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(req.picihao))
|
||
{
|
||
sqlpart = sqlpart + $" and c.picihao='" + req.picihao + "'";
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(req.isBuildName))
|
||
{
|
||
sqlpart = sqlpart + $" and c.is_build_name='" + req.isBuildName + "'";
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(req.startTime) && !string.IsNullOrEmpty(req.endTime))
|
||
{
|
||
DateTime datetime = DateTime.Parse(req.endTime);
|
||
DateTime datetime1 = datetime.AddDays(1);
|
||
sqlpart = sqlpart + $" and synchronoustime between TO_DATE('" + req.startTime +
|
||
$"','YYYY-MM-DD') and TO_DATE('" + datetime1.ToString("yyyy-MM-dd") + $"','YYYY-MM-DD')";
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(req.streetid))
|
||
{
|
||
sqlpart = sqlpart + $" and c.streetid='" + req.streetid + "'";
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(req.countyid))
|
||
{
|
||
sqlpart = sqlpart + $" and c.countyid='" + req.countyid + "'";
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(req.caseNo))
|
||
{
|
||
sqlpart = sqlpart + $" and c.case_no like '%" + req.caseNo + "%'";
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(req.nowStatus))
|
||
{
|
||
string[] str = req.nowStatus.Split(",");
|
||
int isDarwback = 0;
|
||
string strstatus = "";
|
||
for (int i = 0; i < str.Length; i++)
|
||
{
|
||
// 县级驳回 is_drawback 市级驳回 is_shijibohui
|
||
if (req.nowStatus.Contains("市级驳回") && req.nowStatus.Contains("县级驳回"))
|
||
{
|
||
sqlpart = sqlpart + $" and (c.is_drawback = '1' or c.is_shijibohui = '1')";
|
||
continue;
|
||
}
|
||
|
||
if ("县级驳回".Equals(str[i]))
|
||
{
|
||
sqlpart = sqlpart + $" and c.is_drawback = '1'";
|
||
continue;
|
||
}
|
||
|
||
if ("市级驳回".Equals(str[i]))
|
||
{
|
||
sqlpart = sqlpart + $" and c.is_shijibohui = '1'";
|
||
continue;
|
||
}
|
||
|
||
|
||
strstatus = strstatus + "'" + str[i] + "'" + ",";
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(strstatus))
|
||
{
|
||
string statuslist = strstatus.Substring(0, strstatus.Length - 1);
|
||
sqlpart = sqlpart + $" and a.\"UnitName\" in (" + statuslist + ")";
|
||
}
|
||
else
|
||
{
|
||
sqlpart = sqlpart + $" and a.\"UnitName\" in ('待接收','待填报','待整改')";
|
||
}
|
||
}
|
||
else
|
||
{
|
||
sqlpart = sqlpart + $" and a.\"UnitName\" in ('待接收','待填报','待整改')";
|
||
}
|
||
|
||
|
||
if (!string.IsNullOrEmpty(req.isSplit))
|
||
{
|
||
string[] str = req.isSplit.Split(",");
|
||
if (str.Length == 1)
|
||
{
|
||
if (str[0].Equals("正常"))
|
||
{
|
||
sqlpart = sqlpart + "and (c.is_split = '0' or c.is_split = '2')";
|
||
}
|
||
else
|
||
{
|
||
sqlpart = sqlpart + "and c.is_split = '1'";
|
||
}
|
||
}
|
||
}
|
||
|
||
if (req.isOutTime == 0)
|
||
{
|
||
sqlpart = sqlpart + $" and a.\"IsOutTime\"=" + req.isOutTime + "";
|
||
}
|
||
|
||
if (req.isOutTime == 1)
|
||
{
|
||
sqlpart = sqlpart + $" and a.\"IsOutTime\">=" + req.isOutTime + "";
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(req.tubanArea1) && !string.IsNullOrEmpty(req.tubanArea2))
|
||
{
|
||
sqlpart = sqlpart + $" and CAST(c.area AS numeric) between " + double.Parse(req.tubanArea1) +
|
||
" and " +
|
||
double.Parse(req.tubanArea2);
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(req.gengdiArea1) && !string.IsNullOrEmpty(req.gengdiArea2))
|
||
{
|
||
sqlpart = sqlpart + $" and CAST(c.gengdi_area AS numeric) between " + double.Parse(req.gengdiArea1) +
|
||
" and " + double.Parse(req.gengdiArea2);
|
||
}
|
||
|
||
if (!string.IsNullOrEmpty(req.sortType))
|
||
{
|
||
if ("synchronoustime".Equals(req.sortType))
|
||
{
|
||
sqlpart += $" order by {req.sortType} {req.order}";
|
||
}
|
||
else
|
||
{
|
||
//sqlpart += $" order by CAST(" + req.sortType + $" AS numeric) {req.order}";
|
||
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
|
||
}
|
||
};
|
||
}
|
||
|
||
|
||
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:DroneCaseInfoZdwt2SchemeCode").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_zdwt2 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_zdwt2 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_zdwt2 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_zdwt2 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_zdwt2 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<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;
|
||
}
|
||
}
|
||
|
||
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:DroneCaseInfoZdwt2SchemeCode").Value;
|
||
|
||
string sqlcount = $" select count(a.\"Id\")\r\n from drone_caseinfo_zdwt2 a LEFT JOIN wf_process b on b.\"InstanceInfo\"->>'pkeyValue'=a.\"Id\" and b.\"SchemeCode\"='" +
|
||
schemeCode + "' where 1=1 and is_closed=0 and b.\"Id\" is not null";
|
||
string sql =
|
||
$" select a.\"Id\",tubanlaiyuan,zj_xmmc,zj_xmzt,zj_sjyt,qita_qksm,is_chuli,chuliqingkuangsm,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 '其他' when is_illegal=3 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='2' then '没收' end as measureName,\r\nqita_use_to as qitaUseTo\r\n from drone_caseinfo_zdwt2 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 ("1".Equals(item.qita_qksm))
|
||
{
|
||
item.qita_qksm = "不存在经营";
|
||
}
|
||
else if ("2".Equals(item.qita_qksm))
|
||
{
|
||
item.qita_qksm = "已优化调整";
|
||
}
|
||
|
||
if ("自建自用".Equals(item.isillegal))
|
||
{
|
||
item.xiangmumc = item.zj_xmmc;
|
||
item.xiangmuzhuti = item.zj_xmzt;
|
||
item.actualuseto = item.zj_sjyt;
|
||
}
|
||
|
||
}
|
||
|
||
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_zdwt2 where 1=1 and is_closed=0";
|
||
int allCount = client.Ado.GetInt(sqlcount + sqlpart);
|
||
string sql =
|
||
$" select \"Id\",tubanlaiyuan,zj_xmmc,zj_xmzt,zj_sjyt,qita_qksm,is_chuli,chuliqingkuangsm,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 '其他' when is_illegal=3 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='2' then '没收' end as measureName,\r\nqita_use_to as qitaUseTo\r\n from drone_caseinfo_zdwt2 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 ("1".Equals(item.qita_qksm))
|
||
{
|
||
item.qita_qksm = "不存在经营";
|
||
}
|
||
else if ("2".Equals(item.qita_qksm))
|
||
{
|
||
item.qita_qksm = "已优化调整";
|
||
}
|
||
if ("自建自用".Equals(item.isillegal))
|
||
{
|
||
item.xiangmumc = item.zj_xmmc;
|
||
item.xiangmuzhuti = item.zj_xmzt;
|
||
item.actualuseto = item.zj_sjyt;
|
||
}
|
||
}
|
||
|
||
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 picihao,zj_xmmc,zj_xmzt,zj_sjyt,qita_qksm,is_chuli,chuliqingkuangsm,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 '其他' when is_illegal=3 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='2' then '没收' end as measureName,\r\nqita_use_to as qitaUseTo,geomid\r\n from drone_caseinfo_zdwt2 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 ("1".Equals(item.qita_qksm))
|
||
{
|
||
item.qita_qksm = "不存在经营";
|
||
}
|
||
else if ("2".Equals(item.qita_qksm))
|
||
{
|
||
item.qita_qksm = "已优化调整";
|
||
}
|
||
if ("自建自用".Equals(item.isIllegal))
|
||
{
|
||
item.xiangmumc = item.zj_xmmc;
|
||
item.xiangmuzhuti = item.zj_xmzt;
|
||
item.actualUseTo = item.zj_sjyt;
|
||
}
|
||
}
|
||
|
||
Response<List<CaseInfoTubanExport>> response = new Response<List<CaseInfoTubanExport>>();
|
||
response.Result = info;
|
||
return response;
|
||
}
|
||
|
||
public void ExportCaseInfoShapefile(CaseInfoTuBanReq req, string shpFilePath, string shpFilePathzip)
|
||
{
|
||
var response = ExprotLoadCaseInfoTuBanList(req).Result;
|
||
List<CaseInfoTubanExport> list = new List<CaseInfoTubanExport>();
|
||
for (int i = 0; i < response.Count; i++)
|
||
{
|
||
var caseinfo = response[i];
|
||
if (!string.IsNullOrEmpty(caseinfo.geomid))
|
||
{
|
||
list.Add(caseinfo);
|
||
}
|
||
}
|
||
|
||
List<int> gids = list.SelectMany(item =>
|
||
item.geomid.Split(',')
|
||
.Select(id => int.TryParse(id, out var gid) ? gid : (int?)null)
|
||
.Where(gid => gid.HasValue)
|
||
.Select(gid => gid.Value)).ToList();
|
||
|
||
string gidsString = string.Join(",", gids);
|
||
//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_zdwt2 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_zdwt2 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 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 ("qita_qksm".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 15 * 300);
|
||
}
|
||
else if ("pandingyijushuoming".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 15 * 300);
|
||
}
|
||
else if ("examiner_name".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 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 ("is_chuli".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 10 * 300);
|
||
}
|
||
else if ("chuliqingkuangsm".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 20 * 320);
|
||
}
|
||
else if ("remark".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 20 * 320);
|
||
}
|
||
else if ("xjshenhejieguo".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 20 * 320);
|
||
}
|
||
else if ("sjshenhejieguo".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 20 * 320);
|
||
}
|
||
else if ("xianjiyijian".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 20 * 320);
|
||
}
|
||
else if ("shijiyijian".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 20 * 320);
|
||
}
|
||
else if ("verifyuser".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 12 * 300);
|
||
}
|
||
else if ("hexiaoren".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 12 * 300);
|
||
}
|
||
else if ("verifytime".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 20 * 320);
|
||
}
|
||
else if ("hexiaotime".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 20 * 320);
|
||
}
|
||
else if ("case_description".Equals(headers[i].key))
|
||
{
|
||
sheet.SetColumnWidth(i, 20 * 320);
|
||
}
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 填充数据
|
||
|
||
var val = (k + 1) * 60000;
|
||
var num = 60000;
|
||
if (val > list.Count)
|
||
{
|
||
num = list.Count - k * 60000;
|
||
}
|
||
|
||
for (int i = 0; i < num; i++) //循环数据
|
||
{
|
||
var item = list[k * 60000 + i]; //获取数据
|
||
|
||
IRow dataRow = sheet.CreateRow(i + 1); //创建行
|
||
string qitauseto = "";
|
||
for (int j = 0; j < headers.Count; j++) //循环表头
|
||
{
|
||
var objValue = "";
|
||
if ("xuhao".Equals(headers[j].key))
|
||
{
|
||
objValue = i + 1 + "";
|
||
}
|
||
|
||
if ("tubanlaiyuan".Equals(headers[j].key))
|
||
{
|
||
objValue = item.tubanlaiyuan;
|
||
}
|
||
else if ("countyname".Equals(headers[j].key))
|
||
{
|
||
objValue = item.countyname;
|
||
}
|
||
else if ("streetname".Equals(headers[j].key))
|
||
{
|
||
objValue = item.streetname;
|
||
}
|
||
else if ("communityname".Equals(headers[j].key))
|
||
{
|
||
objValue = item.communityname;
|
||
}
|
||
else if ("typename".Equals(headers[j].key))
|
||
{
|
||
objValue = item.typename;
|
||
}
|
||
|
||
else if ("caseNo".Equals(headers[j].key))
|
||
{
|
||
objValue = item.caseNo;
|
||
}
|
||
else if ("identificationTime".Equals(headers[j].key))
|
||
{
|
||
if (item.identificationTime.IsNullOrEmpty() ||
|
||
"0001-01-01 00:00:00".Equals(
|
||
item.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))
|
||
{
|
||
|
||
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))
|
||
{
|
||
|
||
objValue = item.actualUseTo;
|
||
|
||
}
|
||
else if ("qita_qksm".Equals(headers[j].key))
|
||
{
|
||
objValue = item.qita_qksm;
|
||
}
|
||
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 ("is_chuli".Equals(headers[j].key))
|
||
{
|
||
if (1 == item.is_chuli)
|
||
{
|
||
objValue = "是";
|
||
}
|
||
if (2 == item.is_chuli)
|
||
{
|
||
objValue = "否";
|
||
}
|
||
|
||
}
|
||
else if ("chuliqingkuangsm".Equals(headers[j].key))
|
||
{
|
||
objValue = item.chuliqingkuangsm;
|
||
}
|
||
else if ("remark".Equals(headers[j].key))
|
||
{
|
||
objValue = item.remark;
|
||
}
|
||
else if ("xjshenhejieguo".Equals(headers[j].key))
|
||
{
|
||
objValue = item.xjshenhejieguo;
|
||
}
|
||
else if ("sjshenhejieguo".Equals(headers[j].key))
|
||
{
|
||
objValue = item.sjshenhejieguo;
|
||
}
|
||
else if ("xianjiyijian".Equals(headers[j].key))
|
||
{
|
||
objValue = item.xianjiyijian;
|
||
}
|
||
else if ("shijiyijian".Equals(headers[j].key))
|
||
{
|
||
objValue = item.shijiyijian;
|
||
}
|
||
else if ("verifyuser".Equals(headers[j].key))
|
||
{
|
||
objValue = item.verifyuser;
|
||
}
|
||
else if ("hexiaoren".Equals(headers[j].key))
|
||
{
|
||
objValue = item.hexiaoren;
|
||
}
|
||
else if ("verifytime".Equals(headers[j].key))
|
||
{
|
||
if (item.verifytime.IsNullOrEmpty() ||
|
||
"0001-01-01 00:00:00".Equals(item.verifytime.ToString("yyyy-MM-dd HH:mm:ss")))
|
||
{
|
||
objValue = "";
|
||
}
|
||
else
|
||
{
|
||
objValue = item.verifytime.ToString("yyyy-MM-dd HH:mm:ss");
|
||
}
|
||
}
|
||
else if ("hexiaotime".Equals(headers[j].key))
|
||
{
|
||
if (string.IsNullOrEmpty(item.hexiaotime))
|
||
{
|
||
objValue = item.hexiaotime;
|
||
}
|
||
else
|
||
{
|
||
DateTime dateValue2;
|
||
DateTime.TryParse(item.hexiaotime, out dateValue2);
|
||
objValue = dateValue2.ToString("yyyy-MM-dd HH:mm:ss");
|
||
}
|
||
}
|
||
else if ("case_description".Equals(headers[j].key))
|
||
{
|
||
objValue = item.case_description;
|
||
}
|
||
|
||
//创建单元格
|
||
dataRow.CreateCell(j);
|
||
dataRow.Cells[j].CellStyle = style; //添加单元格样式
|
||
if (objValue != null && !string.IsNullOrEmpty(objValue.ToString()))
|
||
{
|
||
dataRow.Cells[j].SetCellValue(objValue.ToString()); //填充Excel单元格
|
||
}
|
||
else
|
||
{
|
||
dataRow.Cells[j].SetCellValue(""); //填充Excel单元格
|
||
}
|
||
}
|
||
}
|
||
|
||
#endregion
|
||
|
||
response.Result = new MemoryStream();
|
||
workbook.Write(response.Result);
|
||
workbook = null;
|
||
response.Result.Close();
|
||
response.Result.Dispose();
|
||
response.Code = 200;
|
||
response.Message = "获取成功";
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
response.Code = 500;
|
||
response.Message = ex.Message;
|
||
}
|
||
|
||
return response;
|
||
}
|
||
|
||
public async Task<Response<PageInfo<List<dynamic>>>> LoadCaseInfoIllegalList(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 \"Id\",countyname,streetname,case_no as caseNo,createtime identificationTime,area,\r\n gengdi_area as gengdiArea,case when is_illegal=0 \r\n then '合法' when is_illegal=1 then '违法' when is_illegal=2 then '其他' end as isIllegal,handle_status_name as handleStatusName," +
|
||
$"xiangmumc,xiangmuzhuti,case when weifaleixing=0 \r\n then '非农化违法用地' when weifaleixing=1 then '非粮化违法用地' end as weifaleixing,illegal_contact as illegalContact,illegal_shenfenzhenghao as illegalShenfenzhenghao," +
|
||
$"case when cast(measure_name AS numeric)=0 \r\n then '拆除复耕' when cast(measure_name AS numeric)=1 then '补办手续' end as measureName,case when result_name='0' then '立案' when result_name='1' then '非立案'when result_name='2' then '移交其他部门处理' when result_name='3' then '未处理' end as resultName,registr_number as registrNumber " +
|
||
$"\r\n from drone_caseinfo_zdwt2 where 1=1 and is_illegal=1 ";
|
||
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.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 (!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 + "%'";
|
||
}
|
||
|
||
var info = await client.SqlQueryable<dynamic>(sql + sqlpart)
|
||
.ToPageListAsync(req.page, req.limit, totalcount);
|
||
return new Response<PageInfo<List<dynamic>>>
|
||
{
|
||
Result = new PageInfo<List<dynamic>>
|
||
{
|
||
Items = info,
|
||
Total = totalcount
|
||
}
|
||
};
|
||
}
|
||
|
||
public Response<List<CaseInfoTubanExport>> ExprotLoadCaseInfoIllegalList(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 c.streetid in ({orgs}) or c.communityid in ({orgs})) ";
|
||
}
|
||
|
||
string sql =
|
||
$" select \"Id\",countyname,streetname,case_no as caseNo,createtime identificationTime,area,\r\n gengdi_area as gengdiArea,case when is_illegal=0 \r\n then '合法' when is_illegal=1 then '违法' when is_illegal=2 then '其他' end as isIllegal,handle_status_name as handleStatusName," +
|
||
$"xiangmumc,xiangmuzhuti,case when weifaleixing=0 \r\n then '非农化违法用地' when weifaleixing=1 then '非粮化违法用地' end as weifaleixing,illegal_contact as illegalContact,illegal_shenfenzhenghao as illegalShenfenzhenghao," +
|
||
$"case when cast(measure_name AS numeric)=0 \r\n then '拆除复耕' when cast(measure_name AS numeric)=1 then '补办手续' end as measureName,case when result_name='0' then '立案' when result_name='1' then '非立案'when result_name='2' then '移交其他部门处理' when result_name='3' then '未处理' end as resultName,registr_number as registrNumber" +
|
||
$"\r\n from drone_caseinfo_zdwt2 where 1=1 is_illegal=1 ";
|
||
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.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 (!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 + "%'";
|
||
}
|
||
|
||
var info = client.Ado.SqlQuery<CaseInfoTubanExport>(sql + sqlpart);
|
||
Response<List<CaseInfoTubanExport>> response = new Response<List<CaseInfoTubanExport>>();
|
||
response.Result = info;
|
||
return response;
|
||
}
|
||
|
||
public Response<MemoryStream> ListToExcelIllegal(List<CaseInfoTubanExport> list, List<ModuleColumn> headers)
|
||
{
|
||
Response<MemoryStream> response = new Response<MemoryStream>();
|
||
try
|
||
{
|
||
HSSFWorkbook workbook = new HSSFWorkbook();
|
||
|
||
#region 内容样式
|
||
|
||
IFont font1 = workbook.CreateFont(); //创建一个字体样式对象
|
||
font1.FontName = "Microsoft YaHei"; //和excel里面的字体对应
|
||
//font1.Boldweight = short.MaxValue;//字体加粗
|
||
font1.FontHeightInPoints = 12; //字体大小
|
||
ICellStyle style = workbook.CreateCellStyle(); //创建样式对象
|
||
style.BorderBottom = BorderStyle.Thin;
|
||
style.BorderLeft = BorderStyle.Thin;
|
||
style.BorderRight = BorderStyle.Thin;
|
||
style.BorderTop = BorderStyle.Thin;
|
||
style.Alignment = HorizontalAlignment.Center;
|
||
style.VerticalAlignment = VerticalAlignment.Center;
|
||
style.SetFont(font1); //将字体样式赋给样式对象
|
||
style.WrapText = true;
|
||
|
||
#endregion
|
||
|
||
#region 标题样式
|
||
|
||
IFont font = workbook.CreateFont(); //创建一个字体样式对象
|
||
font.FontName = "Microsoft YaHei"; //和excel里面的字体对应
|
||
font.Boldweight = (short)FontBoldWeight.Bold; //字体加粗
|
||
font.FontHeightInPoints = 12; //字体大小
|
||
ICellStyle style1 = workbook.CreateCellStyle(); //创建样式对象
|
||
style1.BorderBottom = BorderStyle.Thin;
|
||
style1.BorderLeft = BorderStyle.Thin;
|
||
style1.BorderRight = BorderStyle.Thin;
|
||
style1.BorderTop = BorderStyle.Thin;
|
||
style1.Alignment = HorizontalAlignment.Center;
|
||
style1.VerticalAlignment = VerticalAlignment.Center;
|
||
style1.SetFont(font); //将字体样式赋给样式对象
|
||
|
||
#endregion
|
||
|
||
#region 创建表头
|
||
|
||
int m = list.Count / 60000 + 1;
|
||
for (int k = 0; k < m; k++)
|
||
{
|
||
ISheet sheet = workbook.CreateSheet("Sheet" + k.ToString());
|
||
IRow rowHeader = sheet.CreateRow(0);
|
||
rowHeader.Height = 20 * 30;
|
||
for (int i = 0; i < headers.Count; i++)
|
||
{
|
||
ModuleColumn header = headers[i];
|
||
rowHeader.CreateCell(i);
|
||
rowHeader.Cells[i].CellStyle = style1;
|
||
rowHeader.Cells[i].SetCellValue(header.value);
|
||
sheet.SetColumnWidth(i, 20 * 350);
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 填充数据
|
||
|
||
var val = (k + 1) * 60000;
|
||
var num = 60000;
|
||
if (val > list.Count)
|
||
{
|
||
num = list.Count - k * 60000;
|
||
}
|
||
|
||
for (int i = 0; i < num; i++) //循环数据
|
||
{
|
||
var item = list[k * 60000 + i]; //获取数据
|
||
|
||
IRow dataRow = sheet.CreateRow(i + 1); //创建行
|
||
for (int j = 0; j < headers.Count; j++) //循环表头
|
||
{
|
||
var objValue = "";
|
||
|
||
if ("countyname".Equals(headers[j].key))
|
||
{
|
||
objValue = item.countyname;
|
||
}
|
||
else if ("streetname".Equals(headers[j].key))
|
||
{
|
||
objValue = item.streetname;
|
||
}
|
||
else if ("caseNo".Equals(headers[j].key))
|
||
{
|
||
objValue = item.caseNo;
|
||
}
|
||
else if ("identificationTime".Equals(headers[j].key))
|
||
{
|
||
objValue = item.identificationTime.ToString("yyyy-MM-dd HH:mm:ss");
|
||
}
|
||
else if ("area".Equals(headers[j].key))
|
||
{
|
||
objValue = item.area;
|
||
}
|
||
else if ("gengdiArea".Equals(headers[j].key))
|
||
{
|
||
objValue = item.gengdiArea;
|
||
}
|
||
else if ("isillegal".Equals(headers[j].key))
|
||
{
|
||
objValue = item.isIllegal;
|
||
}
|
||
else if ("handleStatusName".Equals(headers[j].key))
|
||
{
|
||
objValue = item.handleStatusName;
|
||
}
|
||
else if ("xiangmumc".Equals(headers[j].key))
|
||
{
|
||
objValue = item.xiangmumc;
|
||
}
|
||
else if ("xiangmuzhuti".Equals(headers[j].key))
|
||
{
|
||
objValue = item.xiangmuzhuti;
|
||
}
|
||
else if ("weifaleixing".Equals(headers[j].key))
|
||
{
|
||
objValue = item.weifaleixing;
|
||
}
|
||
else if ("measureName".Equals(headers[j].key))
|
||
{
|
||
objValue = item.measureName;
|
||
}
|
||
else if ("resultName".Equals(headers[j].key))
|
||
{
|
||
objValue = item.resultName;
|
||
}
|
||
else if ("registrNumber".Equals(headers[j].key))
|
||
{
|
||
objValue = item.registrNumber;
|
||
}
|
||
else if ("illegalContact".Equals(headers[j].key))
|
||
{
|
||
objValue = item.illegalContact;
|
||
}
|
||
else if ("illegalShenfenzhenghao".Equals(headers[j].key))
|
||
{
|
||
objValue = item.illegalShenfenzhenghao;
|
||
}
|
||
|
||
|
||
//创建单元格
|
||
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 string getShpDataInfo(QueryShpDataInfo shpDataInfo)
|
||
{
|
||
/* string sql = "SELECT ST_AsText(ST_Centroid(geom)) FROM drone_shp_data where gid =" + shpDataInfo.gid;
|
||
string point = client.Ado.GetString(sql);
|
||
string[] lat = point.Replace("POINT(", "").Replace(")", "").Split(" ");
|
||
double maxLat = double.Parse(lat[0]) + 0.5;
|
||
double mixLat = double.Parse(lat[0]) - 0.5;
|
||
double maxLng = double.Parse(lat[1]) + 0.4;
|
||
double minLng = double.Parse(lat[1]) - 0.4;*/
|
||
// string bbox = mixLat + "," + minLng + "," + maxLat + "," + maxLng;
|
||
/* string bbox = "118.098230269564,34.5705540674492,118.108435854995,34.579809145669";
|
||
string layers = StringUtil.Join(shpDataInfo.layers, ",");
|
||
string url = "http://192.168.10.132:8080/geoserver/ne/wms?service=WMS&version=1.1.0&request=GetMap&layers=";
|
||
url = url + layers + "&CQL_FILTER =INCLUDE;" + "case_no=" + shpDataInfo.relid + "&bbox=" + bbox +
|
||
"&width=768&height=646&srs=EPSG:4326&format=image/png&TRANSPARENT=TRUE";
|
||
Console.WriteLine(url);
|
||
// string url1 = "http://60.213.14.14:8060/geoserver/feixian/wms?service=WMS&version=1.1.0&request=GetMap&layers=feixian:images,feixian:zhenjie,feixian:anjianjilu&CQL_FILTER=Name=%27%E5%88%98%E6%B4%8B%27;INCLUDE;streetid=%27c0069852-a10c-4017-b8da-3386c165968e%27&bbox=117.607435742,35.02093722300003,118.23673036800008,35.55038089100005&width=768&height=646&srs=EPSG:4326&format=image/png&TRANSPARENT=TRUE";
|
||
var draftResult = url.GetAsync().Result;
|
||
byte[] img = draftResult.GetBytesAsync().Result;
|
||
MemoryStream memStream = new MemoryStream(img);
|
||
Image imge = Image.FromStream(memStream);
|
||
imge.Save("D:/fwtool/" + "abc.png");*/
|
||
|
||
|
||
return "";
|
||
}
|
||
|
||
private ISugarQueryable<Sugar_Drone_caseinfo_single> QueryCaseInfoSingleList(SqlSugarClient db,
|
||
QueryCaseInfoListReq req, SysUser user, int? is_order = 1, List<string> roles = null)
|
||
{
|
||
DateTime? threeTime = DateTime.Now.AddDays(-3);
|
||
DateTime? sevenTime = DateTime.Now.AddDays(-7);
|
||
|
||
|
||
//查询用户角色绑定的流程有哪些
|
||
var flowList = new List<string>();
|
||
var flowSorts = new List<int?>();
|
||
var roleNameList = new List<string>();
|
||
//查询部门及下级部门
|
||
var orgid = db.Queryable<SysOrg>().ToChildList(a => a.ParentId, req.countyid).Select(a => a.Id.ToString())
|
||
.ToList();
|
||
if (roles != null && req.is_verify_filter != null)
|
||
{
|
||
flowList = db.Queryable<DroneRelevance>()
|
||
.Where(c => roles.Contains(c.SecondId) && c.Key == Define.FLOW_ROLE).Select(c => c.FirstId)
|
||
.ToList();
|
||
//查询用户绑定的流程Sort
|
||
flowSorts = db.Queryable<DroneFlowInstance>()
|
||
.Where(c => flowList.Contains(c.Id) && c.status == 0 && c.instance_name == "CaseVerify")
|
||
.Select(c => c.sort).ToList();
|
||
roleNameList = db.Queryable<SysRole>().Where(c => roles.Contains(c.Id.ToString())).Select(c => c.Name)
|
||
.ToList();
|
||
}
|
||
|
||
//待优化
|
||
var casetype = db.Queryable<DroneRelevance>()
|
||
.LeftJoin<DroneRelevance>((a, b) => a.FirstId == b.FirstId)
|
||
.LeftJoin<SysUserRole>((a, b, c) => b.SecondId == c.RoleId.ToString())
|
||
.Where((a, b, c) => c.UserId == user.Id && a.Key == Define.TOPIC_CASETYPE && b.Key == Define.TOPIC_ROLE)
|
||
.Select(a => a.SecondId).Distinct().ToList();
|
||
var query = db.Queryable<Sugar_Drone_caseinfo_single>().Where(c => c.is_delete == 0 || c.is_delete == null)
|
||
//关键字过滤数据
|
||
.WhereIF(!string.IsNullOrEmpty(req.key),
|
||
c => c.case_no.Contains(req.key) || c.case_name.Contains(req.key) ||
|
||
c.case_description.Contains(req.key))
|
||
//是否完整：判读页面 用来查看是否已判读的
|
||
.WhereIF(req.is_intact != null && req.is_intact != 0, c => c.is_intact == req.is_intact)
|
||
.WhereIF(req.is_intact == 0, c => c.is_intact == null || c.is_intact == 0)
|
||
//上报人
|
||
.WhereIF(req.is_reporter == 1 && user.Account != Define.SYSTEM_USERNAME,
|
||
c => c.createuser == user.Id.ToString())
|
||
//处理人：相当于 局管理员和乡镇管理员用
|
||
.WhereIF(req.is_dealer == 1 && user.Account != Define.SYSTEM_USERNAME, c =>
|
||
SqlFunc.Subqueryable<SysUserOrg>()
|
||
.Where(it => it.OrgId.ToString() == c.communityid && it.UserId == user.Id).Any()
|
||
|| SqlFunc.Subqueryable<SysUserOrg>()
|
||
.Where(it => it.OrgId.ToString() == c.streetid && it.UserId == user.Id).Any()
|
||
|| SqlFunc.Subqueryable<SysUserOrg>()
|
||
.Where(it => it.OrgId.ToString() == c.countyid && it.UserId == user.Id).Any()
|
||
|| c.createuser == user.Id.ToString())
|
||
//执行人过滤：相当于 执法人员用
|
||
.WhereIF(req.dispense_dealer == 1 && user.Account != Define.SYSTEM_USERNAME,
|
||
c => SqlFunc.Subqueryable<DroneRelevance>().Where(it =>
|
||
it.SecondId == c.Id && it.Key == Define.USERDRONECASE && it.FirstId == user.Id.ToString())
|
||
.Any())
|
||
////专题权限过滤
|
||
//.WhereIF(req.is_dealer == 1 && user.Account != Define.SYSTEM_USERNAME, c => (SqlFunc.Subqueryable<DroneRelevance>()
|
||
// .Where(dr2 => SqlFunc.Subqueryable<DroneRelevance>()
|
||
// .Where(dr1 => SqlFunc.Subqueryable<SysUserRole>()
|
||
// .Where(sur => sur.UserId == user.Id)
|
||
// .Select(sur => sur.RoleId.ToString())
|
||
// .Contains(dr1.SecondId) && dr1.Key == Define.TOPIC_ROLE).Select(dr1 => dr1.FirstId)
|
||
// .Contains(dr2.FirstId) && dr2.Key == Define.TOPIC_CASETYPE).Select(dr2 => dr2.SecondId)).Contains(c.typeid))
|
||
//专题权限过滤
|
||
.WhereIF(req.is_dealer == 1 && user.Account != Define.SYSTEM_USERNAME, c => casetype.Contains(c.typeid))
|
||
//分发状态：乡镇管理员用来查是否分发的
|
||
.WhereIF(req.is_dispense == 0, c => c.is_dispense == null || c.is_dispense == 0)
|
||
.WhereIF(req.is_dispense == 1, c => c.is_dispense == 1)
|
||
//处理状态：未办理、办理中、已办结
|
||
.WhereIF(req.handle_status_id != null, c => c.handle_status_id == req.handle_status_id)
|
||
//案件状态
|
||
.WhereIF(!string.IsNullOrEmpty(req.case_status_id), c => c.case_status_id == req.case_status_id)
|
||
//开始时间
|
||
.WhereIF(req.report_start_time != null, c => c.createtime >= req.report_start_time)
|
||
//结束时间
|
||
.WhereIF(req.report_end_time != null, c => c.createtime <= req.report_end_time)
|
||
//上报人
|
||
.WhereIF(!string.IsNullOrEmpty(req.report_name), c => c.createusername.Contains(req.report_name))
|
||
//执行人
|
||
.WhereIF(!string.IsNullOrEmpty(req.deal_username), c => c.deal_username.Contains(req.deal_username))
|
||
//判读开始时间
|
||
.WhereIF(req.identification_start_time != null,
|
||
c => c.identification_time >= req.identification_start_time)
|
||
//判读结束时间
|
||
.WhereIF(req.identification_end_time != null, c => c.identification_time <= req.identification_end_time)
|
||
//县id
|
||
.WhereIF(!string.IsNullOrEmpty(req.countyid),
|
||
c => orgid.Contains(c.countyid) || orgid.Contains(c.streetid) || orgid.Contains(c.communityid))
|
||
////镇id
|
||
//.WhereIF(!string.IsNullOrEmpty(req.countyid), c => orgid.Contains(c.streetid))
|
||
////村id
|
||
//.WhereIF(!string.IsNullOrEmpty(req.countyid), c => orgid.Contains(c.communityid))
|
||
//判读人id
|
||
.WhereIF(!string.IsNullOrEmpty(req.identification_userid),
|
||
c => c.identification_userid == req.identification_userid)
|
||
//判读人
|
||
.WhereIF(!string.IsNullOrEmpty(req.identification_user),
|
||
c => c.identification_user.Contains(req.identification_user))
|
||
//判读人账号
|
||
.WhereIF(!string.IsNullOrEmpty(req.identification_account),
|
||
c => SqlFunc.Subqueryable<SysUser>().Where(it =>
|
||
c.identification_userid == it.Id.ToString() && it.Account == req.identification_account).Any())
|
||
//审核人
|
||
.WhereIF(!string.IsNullOrEmpty(req.verify_user), c => c.verifyuser.Contains(req.verify_user))
|
||
//是否违法
|
||
.WhereIF(req.is_illegal != null, c => c.is_illegal == req.is_illegal)
|
||
//是否核销
|
||
//.WhereIF(req.is_verification == 1, c => c.is_verification == req.is_verification)
|
||
//.WhereIF(req.is_verification == 0, c => c.is_verification == null || c.is_verification == 0)
|
||
//是否退回
|
||
.WhereIF(req.is_drawback == 0, c => c.is_drawback == null || c.is_drawback == 0)
|
||
.WhereIF(req.is_drawback == 1, c => c.is_drawback == 1)
|
||
//是否根据退回给谁过滤
|
||
//.WhereIF(req.is_back_to_userid == 1, c => c.back_to_userid == user.Id.ToString())
|
||
//是否核查过滤
|
||
//.WhereIF(req.is_examine == 0, c => c.is_examine == null || c.is_examine == 0)
|
||
//.WhereIF(req.is_examine == 1, c => c.is_examine == 1)
|
||
//处理措施
|
||
.WhereIF(!string.IsNullOrEmpty(req.measure_name), c => c.measure_name == req.measure_name)
|
||
//处理措施
|
||
.WhereIF(req.measure_name_type == 0, c => c.measure_name == "拟拆除")
|
||
.WhereIF(req.measure_name_type == 1, c => c.measure_name == "查处")
|
||
.WhereIF(req.measure_name_type == 2, c => c.measure_name == "拟完善手续")
|
||
//工作区过滤
|
||
.WhereIF(!string.IsNullOrEmpty(req.work_area_name),
|
||
c => SqlFunc.Subqueryable<SysOrg>()
|
||
.Where(it => it.Id.ToString() == c.communityid && it.CustomCode == req.work_area_name).Any())
|
||
//审核权限过滤
|
||
.WhereIF(req.is_verify_filter == 1,
|
||
c => flowSorts.Contains(c.verifystatus) && roleNameList.Contains("案件审核"))
|
||
.WhereIF(req.is_verify_filter == 0, c => !flowSorts.Contains(c.verifystatus))
|
||
/*
|
||
* 统计相关用到的过滤条件
|
||
*/
|
||
//超期时间标识
|
||
.WhereIF(req.out_time_flag == 1, c => c.identification_time >= threeTime) //1-3天
|
||
.WhereIF(req.out_time_flag == 2,
|
||
c => c.identification_time < threeTime && c.identification_time > sevenTime) //3-7天
|
||
.WhereIF(req.out_time_flag == 3, c => c.identification_time < sevenTime) //7天以上
|
||
.WhereIF(req.out_time_flag == 4,
|
||
c => c.identification_time > DateTime.Now.AddDays(-30) &&
|
||
c.identification_time < DateTime.Now.AddDays(-7)) //30天内
|
||
.WhereIF(req.out_time_flag == 5, c => c.identification_time < DateTime.Now.AddDays(-30)) //30天以上
|
||
//是否处理
|
||
//.WhereIF(req.is_deal == 1, c => c.handle_status_id != 0 || (c.handle_status_id == 0 && c.is_examine == 1))
|
||
//是否整改完成
|
||
.WhereIF(req.is_complete == 0, c => c.handle_status_id != 2)
|
||
//24小时未核查
|
||
// .WhereIF(req.is_not_deal_hour24 == 1, c => c.handle_status_id == 0 && (c.is_examine == null || c.is_examine == 0) && c.identification_time < DateTime.Now.AddDays(-1))
|
||
|
||
//村名称
|
||
.WhereIF(!string.IsNullOrEmpty(req.communityname), c => c.communityname.Contains(req.communityname))
|
||
//案件编号
|
||
.WhereIF(!string.IsNullOrEmpty(req.case_no), c => c.case_no.Contains(req.case_no))
|
||
//案件类型
|
||
.WhereIF(!string.IsNullOrEmpty(req.typeid), c => c.typeid == req.typeid)
|
||
//地址
|
||
.WhereIF(!string.IsNullOrEmpty(req.address), c => c.address.Contains(req.address))
|
||
//案件描述
|
||
.WhereIF(!string.IsNullOrEmpty(req.case_description),
|
||
c => c.case_description.Contains(req.case_description));
|
||
|
||
|
||
if (!string.IsNullOrEmpty(req.work_area_name))
|
||
{
|
||
var orgList = db.Queryable<SysOrg>().Where(c => c.CustomCode == req.work_area_name).Select(c => c.Id)
|
||
.ToList();
|
||
}
|
||
|
||
//标签检索
|
||
if (!string.IsNullOrEmpty(req.tagids))
|
||
{
|
||
var tags = req.tagids.Trim().Split(",").ToList();
|
||
var ids = db.Queryable<DroneCaseInfoTag>().Where(a => req.tagids.Contains(a.tagid))
|
||
.Select(a => a.caseid)
|
||
.ToList();
|
||
query = query.Where(c => ids.Contains(c.Id));
|
||
}
|
||
|
||
//排序
|
||
if (is_order == 1)
|
||
{
|
||
if (req.is_intact == 1 && req.handle_status_id == 0)
|
||
query = query.OrderBy(c => c.identification_time, OrderByType.Desc);
|
||
else if (req.is_intact == 1 && req.handle_status_id == 1)
|
||
query = query.OrderBy(c => c.deal_time, OrderByType.Desc);
|
||
else if (req.is_intact == 1 && req.handle_status_id == 2)
|
||
query = query.OrderBy(c => c.verifytime, OrderByType.Desc);
|
||
else
|
||
query = query.OrderBy(c => c.createtime, OrderByType.Desc);
|
||
}
|
||
|
||
return query;
|
||
}
|
||
|
||
#region 案件关闭--重点问题2
|
||
/// <summary>
|
||
/// 关闭案件--重点问题2
|
||
/// </summary>
|
||
/// <param name="id">案件id</param>
|
||
/// <returns></returns>
|
||
public async Task<Response<bool>> CloseCaseInfoZdwt2(string id, string closereason)
|
||
{
|
||
var user = _auth.GetCurrentUser().User;
|
||
var info = client.Queryable<DroneCaseInfoZdwt2>().Where(r => r.Id == id).First();
|
||
using (var uow = base.UnitWork.CreateContext())
|
||
{
|
||
await uow.DroneCaseInfoZdwt2.UpdateAsync(r => new DroneCaseInfoZdwt2()
|
||
{
|
||
is_closed = 1,
|
||
is_intact = 99,
|
||
close_time = DateTime.Now,
|
||
close_user = user.Name,
|
||
close_userid = user.Id.ToString(),
|
||
close_reason = closereason
|
||
}, r => r.Id == id);
|
||
|
||
var flag = uow.Commit();
|
||
var flags = flag;
|
||
//更新航飞库数据
|
||
//if (info != null && !string.IsNullOrEmpty(info.original_case_no))
|
||
//{
|
||
// var param = new
|
||
// {
|
||
// CaseNo = info.original_case_no,
|
||
// Reason = closereason
|
||
// };
|
||
// var url = _configuration.GetSection("AppSetting:ResubmitUrl").Value;
|
||
// var apiUrl = url + "api/DroneCaseinfo/CloseCaseInfo";
|
||
// var urlflag = await HttpMethods.Post(apiUrl, Infrastructure.Json.ToJson(param));
|
||
|
||
// var reslut = (JObject)JsonConvert.DeserializeObject(urlflag);
|
||
// flags = flag && (bool)reslut["result"];
|
||
|
||
//}
|
||
|
||
if (flags)
|
||
{
|
||
return new Response<bool>
|
||
{
|
||
Result = true,
|
||
Message = "关闭成功"
|
||
};
|
||
}
|
||
else
|
||
{
|
||
return new Response<bool>
|
||
{
|
||
Code = 500,
|
||
Result = false,
|
||
Message = "关闭失败"
|
||
};
|
||
}
|
||
}
|
||
}
|
||
#endregion
|
||
}
|
||
} |