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

5209 lines
250 KiB
C#
Raw Permalink Blame History

This file contains ambiguous Unicode characters!

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

using Dm.Config;
using Flurl.Http;
using Infrastructure;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Npgsql.Replication.PgOutput.Messages;
using NUnit.Framework;
using OpenAuth.App.Base;
using OpenAuth.App.BasicQueryService;
using OpenAuth.App.Request;
using OpenAuth.App.Response;
using OpenAuth.App.Interface;
using OpenAuth.Repository;
using OpenAuth.Repository.Domain;
using SixLabors.ImageSharp;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using OpenAuth.App.FormScheme.FormHelpers;
using Infrastructure.Helpers;
using NetTaste;
using NUnit.Framework.Interfaces;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using OpenAuth.App.BaseApp.Base;
using OpenAuth.App.FormModule;
using Org.BouncyCastle.Ocsp;
namespace OpenAuth.App
{
public partial class DroneCaseinfoApp : SqlSugarBaseApp<Repository.Domain.DroneCaseInfo, SugarDbContext>
{
private IConfiguration _configuration;
private DroneCaseInfoManager droneCaseInfoManager;
private WFProcessApp _wfProcessApp;
ISqlSugarClient client;
CommonDataManager _commonDataManager;
//处理状态
List<QueryHandleStatusListReq> handleStatusList = new List<QueryHandleStatusListReq>()
{
new QueryHandleStatusListReq(){ id = 0,is_examine = 0,name="待核查"},
new QueryHandleStatusListReq(){ id = 0,is_examine = 1,name="待办理"},
new QueryHandleStatusListReq(){ id = 1,name="待审核"},
new QueryHandleStatusListReq(){ id = 2,name="已归档"},
};
/*
* 根据不同的标签来区分文件的不同分类
* drone_files表中tag字段区分
*/
//整改后照片标签
private const string afterPicTag = "AfterPicTag";
//合法文件标签
private const string EvidenceFileTag = "EvidenceFileTag";
//处罚通知书
private const string punishPicTag = "punishPicTag";
//交款通知书
private const string paymentPicTag = "paymentPicTag";
//政府同意完善手续证明
private const string agreeCheckoutPicTag = "agreeCheckoutPicTag";
//办理手续
private const string checkoutPicTag = "checkoutPicTag";
//勘测定界图
private const string boundaryPicTag = "boundaryPicTag";
public DroneCaseinfoApp(ISugarUnitOfWork<SugarDbContext> unitWork, ISimpleClient<Repository.Domain.DroneCaseInfo> repository, IAuth auth, IConfiguration configuration, DroneCaseInfoManager droneCaseInfoManager, ISqlSugarClient client, WFProcessApp wFProcessApp, CommonDataManager commonDataManager) : base(unitWork, repository, auth)
{
_configuration = configuration;
this.droneCaseInfoManager = droneCaseInfoManager;
this.client = client;
_wfProcessApp = wFProcessApp;
_commonDataManager = commonDataManager;
}
#region 案件
/// <summary>
/// 收藏案件
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
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 = "收藏失败"
};
}
/// <summary>
/// 获取收藏的案件信息
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<PageInfo<List<Repository.Domain.DroneCaseInfo>>>> LoadFavoriteCases(PageReq req)
{
RefAsync<int> totalCount = 0;
var user = _auth.GetCurrentUser().User;
var listFavorites = await base.Repository.ChangeRepository<SugarRepositiry<DroneCaseFavorite>>().AsQueryable().Where(p => p.FavoriteUserId.Equals(user.Id.ToString())).ToListAsync();
List<string> caseNoList = new List<string>();
foreach (var item in listFavorites)
{
caseNoList.Add(item.CaseNo);
}
var caseList = await base.Repository.AsQueryable().Where(p => caseNoList.Contains(p.case_no))
.ToPageListAsync(req.page, req.limit, totalCount);
return new Response<PageInfo<List<Repository.Domain.DroneCaseInfo>>>()
{
Result = new PageInfo<List<Repository.Domain.DroneCaseInfo>>
{
Items = caseList,
Total = totalCount
},
Message = "获取数据成功"
};
}
/// <summary>
/// 获取收藏列表
/// </summary>
/// <returns></returns>
public async Task<Response<dynamic>> FavoriteCaseList(string uid)
{
var favoriteList = await base.Repository.ChangeRepository<SugarRepositiry<DroneCaseFavorite>>().AsQueryable().Where(p => p.FavoriteUserId == uid).Select(c => new { id = c.Id, c.CaseNo }).ToListAsync();
return new Response<dynamic>()
{
Result = favoriteList,
Message = "获取数据成功"
};
}
/// <summary>
/// 批量删除收藏的案件
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public async Task<Response<bool>> DeleteFavoriteCases(string[] ids)
{
var flag = await base.Repository.ChangeRepository<SugarRepositiry<DroneCaseFavorite>>().DeleteByIdsAsync(ids);
if (flag)
return new Response<bool>
{
Result = true,
Message = "删除成功"
};
else
return new Response<bool>
{
Result = false,
Code = 500,
Message = "删除失败"
};
}
/// <summary>
/// 删除收藏的案件
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
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 = "删除失败"
};
}
/// <summary>
/// 添加操作案件日志
/// </summary>
/// <param name="db"></param>
/// <param name="model"></param>
public void AddDroneCaseLog(DroneCaseOperationLog model)
{
model.Id = SqlSugar.SnowFlakeSingle.instance.getID().ToString();
model.createtime = DateTime.Now;
client.Insertable(model).ExecuteCommand();
}
/// <summary>
/// 获取案件记录
/// </summary>
/// <param name="caseid"></param>
/// <returns></returns>
public async Task<Response<List<DroneCaseOperationLog>>> LoadDroneCaseLog(string caseid)
{
List<string> logTypeList = new List<string>
{
"VerifyCaseInfoSuccess", // 案件审核
"DrawbackCase", // 案件退回
"RecallCase", // 案件撤回
"AddDroneCaseDeal", // 案件处理
"UpdateDroneCaseDeal", // 案件处理
"VerificatCase", // 案件核销
};
var list = await base.Repository.ChangeRepository<SugarRepositiry<DroneCaseOperationLog>>().AsQueryable().Where(c => c.caseid == caseid && logTypeList.Contains(c.description)).OrderBy(c => c.createtime, OrderByType.Asc).IgnoreColumns(c => new { c.request_data }).ToListAsync();
return new Response<List<DroneCaseOperationLog>>
{
Result = list,
Message = "获取数据成功"
};
}
/// <summary>
/// 无人机添加案件
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<string>> BackhaulDroneCaseByDrone(AddDroneCaseByDroneReq req)
{
var url = _configuration.GetSection("PictureFilesApi").Value;
var libUrl = _configuration.GetSection("DroneEnforcementLibApi").Value;
var user = await base.Repository.ChangeRepository<SugarRepositiry<SysUser>>().GetFirstAsync(r => r.Account == req.account);
Sugar_Drone_caseinfo model = req.MapTo<Sugar_Drone_caseinfo>();
var picList = req.pic_list == null ? new List<string>() : req.pic_list.Select(a => url.Replace("\\", "/") + a).ToList();
var result = await (libUrl + "api/DroneCaseinfo/BackhaulDroneCaseByDrone")
.PostJsonAsync(new
{
remark = req.remark,
lng = req.lng,
lat = req.lat,
pic_list = picList,
userid = user.Id,
username = user.Name
})
.ReceiveString();
return new Response<string>
{
Result = result,
Message = result,
};
}
/// <summary>
/// 上报案件
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<string>> AddDroneCaseInfo(AddOrUpdateDroneCaseInfoReq req)
{
var user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
//案件实体
var model = req.info;
model.Init(user, db);
var picList = req.pic_list;
var videoList = req.video_list;
List<DroneFiles> filesList = new List<DroneFiles>();
//图片
for (int i = 0; i < picList.Count; i++)
{
DroneFiles fileModel = new DroneFiles();
fileModel.Id = Guid.NewGuid().ToString();
fileModel.path = picList[i];
fileModel.type = 0;
fileModel.tablename = "drone_caseinfo";
fileModel.createtime = DateTime.Now;
fileModel.createuser = user.Id.ToString();
fileModel.createusername = user.Name;
fileModel.is_delete = 0;
fileModel.relid = model.Id;
filesList.Add(fileModel);
}
//视频
for (int i = 0; i < videoList.Count; i++)
{
DroneFiles fileModel = new DroneFiles();
fileModel.Id = Guid.NewGuid().ToString();
fileModel.path = videoList[i];
fileModel.type = 1;
fileModel.tablename = "drone_caseinfo";
fileModel.createtime = DateTime.Now;
fileModel.createuser = user.Id.ToString();
fileModel.createusername = user.Name;
fileModel.is_delete = 0;
fileModel.relid = model.Id;
filesList.Add(fileModel);
}
//使用事务提交数据
var transFlag = await db.UseTranAsync(async () =>
{
//新增案件数据
await db.Insertable(model).ExecuteCommandAsync();
//新增文件数据
await db.Insertable(filesList).ExecuteCommandAsync();
});
AddDroneCaseLog(new DroneCaseOperationLog { request_data = req.ToJson(), description = "AddDroneCaseInfo", caseid = model.Id, createuserid = user.Id.ToString(), createusername = user.Name });
//发送websocket消息
//SendWebSocketMessage();
if (transFlag.IsSuccess)
return new Response<string>
{
Result = model.Id,
Message = "上报成功"
};
else
return new Response<string>
{
Code = 500,
Message = "上报失败"
};
}
}
/// <summary>
/// 修改完善案件
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<string>> UpdateDroneCaseInfo(AddOrUpdateDroneCaseInfoReq req)
{
var user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
//案件实体
var model = req.info;
model.Update(user, db);
//查询面积
var areaDecimal = await db.Queryable<DroneShpData>().Where(c => c.relid == model.Id).SumAsync(c => c.area);
if (areaDecimal != null)
{
model.area = Decimal.Round((Decimal)areaDecimal, 2).ToString();
}
var picList = req.pic_list;
var videoList = req.video_list;
var existFiles = await db.Queryable<DroneFiles>().Where(c => c.relid == req.info.Id).ToListAsync();
List<DroneFiles> filesList = new List<DroneFiles>();
//图片
for (int i = 0; i < picList.Count; i++)
{
var item = picList[i];
var count = existFiles.Where(c => c.path == item).Count();
if (count > 0)
continue;
DroneFiles fileModel = new DroneFiles();
fileModel.Id = Guid.NewGuid().ToString();
fileModel.path = picList[i];
fileModel.type = 0;
fileModel.tablename = "drone_caseinfo";
fileModel.createtime = DateTime.Now;
fileModel.createuser = user.Id.ToString();
fileModel.createusername = user.Name;
fileModel.is_delete = 0;
fileModel.relid = model.Id;
filesList.Add(fileModel);
}
//视频
for (int i = 0; i < videoList.Count; i++)
{
var item = videoList[i];
var count = existFiles.Where(c => c.path == item).Count();
if (count > 0)
continue;
DroneFiles fileModel = new DroneFiles();
fileModel.Id = Guid.NewGuid().ToString();
fileModel.path = videoList[i];
fileModel.type = 1;
fileModel.tablename = "drone_caseinfo";
fileModel.createtime = DateTime.Now;
fileModel.createuser = user.Id.ToString();
fileModel.createusername = user.Name;
fileModel.is_delete = 0;
fileModel.relid = model.Id;
filesList.Add(fileModel);
}
//使用事务提交数据
var transFlag = await db.UseTranAsync(async () =>
{
//新增案件数据
await db.Updateable(model).IgnoreColumns(c => new { c.deal_time }).ExecuteCommandAsync();
//新增文件数据
await db.Insertable(filesList).ExecuteCommandAsync();
});
AddDroneCaseLog(new DroneCaseOperationLog { request_data = req.ToJson(), description = "UpdateDroneCaseInfo", caseid = model.Id, createuserid = user.Id.ToString(), createusername = user.Name });
if (transFlag.IsSuccess)
return new Response<string>
{
Result = model.Id,
Message = "上报成功"
};
else
return new Response<string>
{
Code = 500,
Message = "上报失败"
};
}
}
/// <summary>
/// 事件判读
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<string>> UpdateDroneCaseInfoIntact(AddOrUpdateDroneCaseInfoReq req)
{
var user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
//案件实体
var model = req.info;
model.Update(user, db);
//是否判读、判读人和判读时间
model.is_intact = 1;
model.identification_user = user.Name;
model.identification_userid = user.Id.ToString();
model.identification_time = DateTime.Now;
//查询面积
var areaDecimal = await db.Queryable<DroneShpData>().Where(c => c.relid == model.Id).SumAsync(c => c.area);
if (areaDecimal != null)
{
model.area = Decimal.Round((Decimal)areaDecimal, 2).ToString();
}
var picList = req.pic_list;
var videoList = req.video_list;
var existFiles = await db.Queryable<DroneFiles>().Where(c => c.relid == req.info.Id).ToListAsync();
List<DroneFiles> filesList = new List<DroneFiles>();
//图片
for (int i = 0; i < picList.Count; i++)
{
var item = picList[i];
var count = existFiles.Where(c => c.path == item).Count();
if (count > 0)
continue;
DroneFiles fileModel = new DroneFiles();
fileModel.Id = Guid.NewGuid().ToString();
fileModel.path = picList[i];
fileModel.type = 0;
fileModel.tablename = "drone_caseinfo";
fileModel.createtime = DateTime.Now;
fileModel.createuser = user.Id.ToString();
fileModel.createusername = user.Name;
fileModel.is_delete = 0;
fileModel.relid = model.Id;
filesList.Add(fileModel);
}
//视频
for (int i = 0; i < videoList.Count; i++)
{
var item = videoList[i];
var count = existFiles.Where(c => c.path == item).Count();
if (count > 0)
continue;
DroneFiles fileModel = new DroneFiles();
fileModel.Id = Guid.NewGuid().ToString();
fileModel.path = videoList[i];
fileModel.type = 1;
fileModel.tablename = "drone_caseinfo";
fileModel.createtime = DateTime.Now;
fileModel.createuser = user.Id.ToString();
fileModel.createusername = user.Name;
fileModel.is_delete = 0;
fileModel.relid = model.Id;
filesList.Add(fileModel);
}
//使用事务提交数据
var transFlag = await db.UseTranAsync(async () =>
{
//新增案件数据
await db.Updateable(model).IgnoreColumns(c => new { c.deal_time }).ExecuteCommandAsync();
//新增文件数据
await db.Insertable(filesList).ExecuteCommandAsync();
});
AddDroneCaseLog(new DroneCaseOperationLog { request_data = req.ToJson(), description = "UpdateDroneCaseInfoIntact", caseid = model.Id, createuserid = user.Id.ToString(), createusername = user.Name });
if (transFlag.IsSuccess)
return new Response<string>
{
Result = model.Id,
Message = "上报成功"
};
else
return new Response<string>
{
Code = 500,
Message = "上报失败"
};
}
}
/// <summary>
/// 分配案件
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<bool>> DispenseDroneCaseData(AddOrUpdateDispenseDroneCaseDataReq req)
{
//当前用户
var user = _auth.GetCurrentUser().User;
// 用户和案件进行绑定
// 用于执法人员 查询分配给自己的案件
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
var key = Define.USERDRONECASE;
List<DroneRelevance> list = new List<DroneRelevance>();
foreach (var item in req.userid)
{
DroneRelevance model = new DroneRelevance();
model.Id = Guid.NewGuid().ToString();
model.Description = "";
model.Key = key;
model.Status = 0;
model.OperateTime = DateTime.Now;
model.OperatorId = "";
model.FirstId = item;
model.SecondId = req.caseid;
model.ThirdId = "";
model.ExtendInfo = "";
list.Add(model);
}
//使用事务提交数据
var transFlag = await db.UseTranAsync(async () =>
{
//先删除当前案件已分配的数据
await db.Deleteable<DroneRelevance>().Where(c => c.Key == key && c.SecondId == req.caseid).ExecuteCommandAsync();
//添加新的分配的数据
await db.Insertable(list).ExecuteCommandAsync();
//记录案件的分配人
await db.Updateable<Sugar_Drone_caseinfo>().SetColumns(c => new Sugar_Drone_caseinfo()
{
dispense_time = DateTime.Now,
dispense_userid = user.Id.ToString(),
dispense_username = user.Name,
is_dispense = 1
}).Where(c => c.Id == req.caseid).ExecuteCommandAsync();
});
if (transFlag.IsSuccess)
return new Response<bool>
{
Result = true,
Message = "分配成功"
};
else
return new Response<bool>
{
Code = 500,
Result = false,
Message = "分配失败"
};
}
}
/// <summary>
/// 获取案件绑定关联关系
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Response<string[]>> GetDispenseDroneCaseData(string id)
{
var result = await base.Repository.ChangeRepository<SugarRepositiry<DroneRelevance>>().AsQueryable().Where(c => c.SecondId == id && c.Key == Define.USERDRONECASE).Select(c => c.FirstId).ToListAsync();
return new Response<string[]>
{
Result = result.ToArray(),
Message = "分配成功"
};
}
/// <summary>
/// 关闭案件
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Response<bool>> CloseDroneCaseInfo(string id)
{
var user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
var flag = await db.Updateable<Sugar_Drone_caseinfo>()
.SetColumns(c => new Sugar_Drone_caseinfo
{
is_intact = 99,
close_time = DateTime.Now,
close_user = user.Name,
close_userid = user.Id.ToString()
})
.Where(c => c.Id == id)
.ExecuteCommandAsync();
if (flag > 0)
return new Response<bool>
{
Result = true,
Message = "关闭成功"
};
else
return new Response<bool>
{
Code = 500,
Result = false,
Message = "关闭失败"
};
}
}
/// <summary>
/// 审核通过
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Response<bool>> VerifyCaseInfoSuccess(string id)
{
var user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
var flag = await db.Updateable<Sugar_Drone_caseinfo>()
.SetColumns(c => new Sugar_Drone_caseinfo()
{
handle_status_id = 2,
handle_status_name = "已办结",
verifyuserid = user.Id.ToString(),
verifyuser = user.Name,
verifytime = DateTime.Now
})
.Where(c => c.Id == id)
.ExecuteCommandAsync();
AddDroneCaseLog(new DroneCaseOperationLog { request_data = id, description = "VerifyCaseInfoSuccess", caseid = id, createuserid = user.Id.ToString(), createusername = user.Name });
if (flag > 0)
return new Response<bool>
{
Result = true,
Message = "审核成功"
};
else
return new Response<bool>
{
Code = 500,
Result = false,
Message = "审核失败"
};
}
}
/// <summary>
/// 审核改进
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<bool>> VerifyCaseInfoFailed(AddOrUpdateVerifyCaseSuccessReq req)
{
var user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
var caseInfo = await db.Queryable<Sugar_Drone_caseinfo>().Where(c => c.Id == req.id).FirstAsync();
//处理状态名称
string handle_status_name = handleStatusList.Where(c => c.id == 0 && c.is_examine == 1).FirstOrDefault().name;
Sugar_Drone_message message = new Sugar_Drone_message();
message.Init(user);
//消息通知
message.msg_type = 1;
message.msg_title = $"图斑编号：{caseInfo.case_no} 需要改进处理";
message.msg_content = $"图斑编号：{caseInfo.case_no} 需要改进处理。备注：{req.remark}";
message.case_no = caseInfo.case_no; //案件编号
message.caseid = caseInfo.Id; //案件id
Sugar_Drone_message_relevance relevance = new Sugar_Drone_message_relevance();
relevance.Init(user);
relevance.userid = caseInfo.deal_userid;
relevance.username = caseInfo.deal_username;
relevance.messageid = message.Id;
DroneImproveRecord record = new DroneImproveRecord();
record.Id = Guid.NewGuid().ToString();
record.reason = req.remark;
record.caseid = caseInfo.Id;
record.deal_userid = caseInfo.deal_userid;
record.receiver = caseInfo.deal_username;
record.createtime = DateTime.Now;
record.createuser = user.Id.ToString();
//使用事务提交数据
var transFlag = await db.UseTranAsync(async () =>
{
//更改案件处理状态
var flag = await db.Updateable<Sugar_Drone_caseinfo>().SetColumns(c => new Sugar_Drone_caseinfo()
{
handle_status_id = 0, //改成未办理0
handle_status_name = "退回未办理",//未办理
is_improve = 1, //是否改进
improve_reason = req.remark //改进原因
}).Where(c => c.Id == req.id).ExecuteCommandAsync();
//插入改进记录
var recordFlag = await db.Insertable(record).ExecuteCommandAsync();
//插入通知和关联
var flag1 = await db.Insertable(message).ExecuteCommandAsync();
var flag2 = await db.Insertable(relevance).ExecuteCommandAsync();
});
AddDroneCaseLog(new DroneCaseOperationLog { request_data = req.ToJson(), description = "VerifyCaseInfoFailed", caseid = req.id, createuserid = user.Id.ToString(), createusername = user.Name });
if (transFlag.IsSuccess)
return new Response<bool>
{
Result = true,
Message = "成功"
};
else
return new Response<bool>
{
Code = 500,
Result = false,
Message = "失败"
};
}
}
/// <summary>
/// 上报事件
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<string>> AddCaseEvent(AddOrUpdateDroneCaseEventReq req)
{
var _user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
Sugar_Drone_caseinfo model = new Sugar_Drone_caseinfo();
model.Id = Guid.NewGuid().ToString();
model.lat = req.lat;
model.lng = req.lng;
model.remark = req.remark;
var picList = req.pic_list;//图片
List<DroneFiles> filesList = new List<DroneFiles>();
//图片
for (int i = 0; i < picList.Count; i++)
{
DroneFiles fileModel = new DroneFiles();
fileModel.Id = Guid.NewGuid().ToString();
fileModel.path = picList[i];
fileModel.type = 0;
fileModel.tablename = "drone_caseinfo";
fileModel.createtime = DateTime.Now;
fileModel.createuser = _user.Id.ToString();
fileModel.createusername = _user.Name;
fileModel.is_delete = 0;
fileModel.relid = model.Id;
filesList.Add(fileModel);
}
//使用事务提交数据
var transFlag = await db.UseTranAsync(async () =>
{
//新增案件数据
await db.Insertable(model).InsertColumns(c => new
{
c.Id,
c.lat,
c.lng,
c.remark
}).ExecuteCommandAsync();
//新增文件数据
await db.Insertable(filesList).ExecuteCommandAsync();
});
AddDroneCaseLog(new DroneCaseOperationLog { request_data = req.ToJson(), description = "AddCaseEvent", caseid = model.Id, createuserid = _user.Id.ToString(), createusername = _user.Name });
if (transFlag.IsSuccess)
return new Response<string>
{
Result = model.Id,
Message = "上报成功"
};
else
return new Response<string>
{
Code = 500,
Message = "上报失败"
};
}
}
/// <summary>
/// 上报案件-分配给自己临时接口
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<string>> AddTemporaryDroneCaseInfo(AddOrUpdateDroneCaseInfoReq req)
{
var user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
//案件实体
var model = req.info;
model.Init(user, db);
var picList = req.pic_list;
var videoList = req.video_list;
List<DroneFiles> filesList = new List<DroneFiles>();
//图片
for (int i = 0; i < picList.Count; i++)
{
DroneFiles fileModel = new DroneFiles();
fileModel.Id = Guid.NewGuid().ToString();
fileModel.path = picList[i];
fileModel.type = 0;
fileModel.tablename = "drone_caseinfo";
fileModel.createtime = DateTime.Now;
fileModel.createuser = user.Id.ToString();
fileModel.createusername = user.Name;
fileModel.is_delete = 0;
fileModel.relid = model.Id;
filesList.Add(fileModel);
}
//视频
for (int i = 0; i < videoList.Count; i++)
{
DroneFiles fileModel = new DroneFiles();
fileModel.Id = Guid.NewGuid().ToString();
fileModel.path = videoList[i];
fileModel.type = 1;
fileModel.tablename = "drone_caseinfo";
fileModel.createtime = DateTime.Now;
fileModel.createuser = user.Id.ToString();
fileModel.createusername = user.Name;
fileModel.is_delete = 0;
fileModel.relid = model.Id;
filesList.Add(fileModel);
}
//使用事务提交数据
var transFlag = await db.UseTranAsync(async () =>
{
//新增案件数据
await db.Insertable(model).ExecuteCommandAsync();
//新增文件数据
await db.Insertable(filesList).ExecuteCommandAsync();
});
if (transFlag.IsSuccess)
return new Response<string>
{
Result = req.info.Id,
Message = "上报成功"
};
else
return new Response<string>
{
Code = 500,
Message = "上报失败"
};
}
}
#endregion
#region 案件列表
/// <summary>
/// 查询条件组装
/// </summary>
/// <param name="db"></param>
/// <param name="req"></param>
/// <param name="user"></param>
/// <param name="is_order"></param>
/// <returns></returns>
private ISugarQueryable<DroneCaseInfo> QueryCaseInfoList(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<DroneCaseInfo>().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;
}
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;
}
/// <summary>
/// 查询案件和处理信息列表
/// </summary>
/// <returns></returns>
public async Task<Response<PageInfo<List<CaseInfoAndDealInfoResponse>>>> LoadCaseInfoAndDealInfoList(QueryCaseInfoListReq req)
{
var user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
//查询条件组装
var query = QueryCaseInfoList(db, req, user);
//总数
RefAsync<int> total = 0;
//拉取数据
var caseList = await query.ToPageListAsync(req.page, req.limit, total);
//查询所有的案件id
var caseIds = caseList.Select(c => c.Id).ToList();
List<CaseInfoAndDealInfoResponse> list = new List<CaseInfoAndDealInfoResponse>();
//查询对应的案件处理信息
var dealList = db.Queryable<DroneCaseDeal>().Where(c => caseIds.Contains(c.caseid) && (c.is_delete == 0 || c.is_delete == null)).ToList();
for (int i = 0; i < caseList.Count; i++)
{
var caseItem = caseList[i];
CaseInfoAndDealInfoResponse model = new CaseInfoAndDealInfoResponse();
model.caseinfo = caseItem;
var dealItem = dealList.Where(c => c.caseid == caseItem.Id).OrderByDescending(c => c.createtime).FirstOrDefault();
if (dealItem == null) dealItem = new DroneCaseDeal();
model.dealinfo = dealItem;
list.Add(model);
}
return new Response<PageInfo<List<CaseInfoAndDealInfoResponse>>>
{
Result = new PageInfo<List<CaseInfoAndDealInfoResponse>>
{
Items = list,
Total = total
},
Message = "获取数据成功"
};
}
}
/// <summary>
/// 查询案件列表
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<PageInfo<List<DroneCaseInfoExt>>>> LoadCaseInfoListInterface(QueryCaseInfoListReq req)
{
var user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
int total = 0;
//查询条件
var query = QueryCaseInfoList(db, req, user);
//查询拉取数据
var caseList = query.Select(c => new DroneCaseInfoExt
{
Id = c.Id,
case_no = c.case_no,
case_name = c.case_name,
case_description = c.case_description,
start_time = c.start_time,
end_time = c.end_time,
address = c.address,
lng = c.lng,
lat = c.lat,
typeid = c.typeid,
typename = c.typename,
handle_status_id = c.handle_status_id,
handle_status_name = c.handle_status_name,
case_status_id = c.case_status_id,
case_status_name = c.case_status_name,
createtime = c.createtime,
createuser = c.createuser,
drone_no = c.drone_no,
deal_userid = c.deal_userid,
deal_username = c.deal_username,
createusername = c.createusername,
is_delete = c.is_delete,
countyid = c.countyid,
countyname = c.countyname,
streetid = c.streetid,
streetname = c.streetname,
communityid = c.communityid,
communityname = c.communityname,
remark = c.remark,
is_closed = c.is_closed,
area = c.area,
is_illegal = c.is_illegal,
close_user = c.close_user,
close_time = c.close_time,
identification_user = c.identification_user,
identification_time = c.identification_time,
is_intact = c.is_intact,
close_userid = c.close_userid,
identification_userid = c.identification_userid,
verifyuserid = c.verifyuserid,
verifyuser = c.verifyuser,
verifytime = c.verifytime,
is_improve = c.is_improve,
improve_reason = c.improve_reason,
is_dispense = c.is_dispense,
dispense_userid = c.dispense_userid,
dispense_time = c.dispense_time,
dispense_username = c.dispense_username,
verifystatus = c.verifystatus,
verifystatusname = c.verifystatusname,
is_checked = c.is_checked,
deal_time = c.deal_time,
pre_phase_time = c.pre_phase_time,
later_phase_time = c.later_phase_time,
pre_phase_img = c.pre_phase_img,
later_phase_img = c.later_phase_img,
is_verification = c.is_verification,
is_drawback = c.is_drawback,
back_to_userid = c.back_to_userid,
back_to_username = c.back_to_username,
}).ToPageList(req.page, req.limit, ref total);
//查询的id
var caseIdList = caseList.Select(c => c.Id).ToList();
//查询的图片
var picList = await db.Queryable<DroneFiles>().Where(c => caseIdList.Contains(c.relid) && c.type == 0 && c.tablename == "drone_caseinfo").Select(c => new { caseid = c.relid, c.path }).ToListAsync();
//遍历案件
for (int i = 0; i < caseList.Count; i++)
{
//给图片赋值
var caseModel = caseList[i];
caseModel.case_pic_list = picList.Where(c => c.caseid == caseModel.Id).Select(c => c.path).ToList();
}
return new Response<PageInfo<List<DroneCaseInfoExt>>>
{
Result = new PageInfo<List<DroneCaseInfoExt>>
{
Items = caseList,
Total = total
},
Message = "获取数据成功"
};
}
}
/// <summary>
/// 查询案件列表
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<PageInfo<List<DroneCaseInfo>>>> LoadCaseInfoList(QueryCaseInfoListReq req)
{
req.Init();
DateTime curDatetime = DateTime.Now;
var user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
//总条数
RefAsync<int> totalNumber = 0;
//组装查询条件
var query = QueryCaseInfoList(db, req, user);
//分页查询数据，拉取数据
var list = await query.ToPageListAsync(req.page, req.limit, totalNumber);
return new Response<PageInfo<List<DroneCaseInfo>>>
{
Result = new PageInfo<List<DroneCaseInfo>>
{
Items = list,
Total = totalNumber
},
Message = "获取数据成功"
};
}
}
/// <summary>
/// 加载处理数据列表
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public async Task<Response<PageInfo<List<DroneCaseInfoExt>>>> LoadDealCaseInfoList(QueryCaseInfoListReq obj)
{
obj.Init();
var req = obj.MapTo<QueryCaseInfoListReq>();
req.is_intact = 1;
req.is_dealer = 1;
var user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
RefAsync<int> total = 0;
//查询条件
var query = QueryCaseInfoList(db, req, user);
//查询拉取数据
var _caseList = await query.ToPageListAsync(req.page, req.limit, total);
string _caseListStr = JsonConvert.SerializeObject(_caseList);
var caseList = JsonConvert.DeserializeObject<List<DroneCaseInfoExt>>(_caseListStr);
//查询的id
var caseIdList = caseList.Select(c => c.Id).ToList();
//查询的图片
var picList = await db.Queryable<DroneFiles>().Where(c => caseIdList.Contains(c.relid) && c.type == 0 && c.tablename == "drone_caseinfo").Select(c => new { caseid = c.relid, c.path }).ToListAsync();
//案件标签
var tags = await db.Queryable<DroneCaseInfoTag>()
.LeftJoin<SysDataItemDetail>((t, c) => t.tagid == c.ItemDetailId)
.Where((t, c) => caseIdList.Contains(t.caseid) && c.ItemCode == "DRONE_CASE_TAG")
.Select((t, c) => new
{
t.caseid,
c.ItemDetailId,
c.ItemName
}).ToListAsync();
//遍历案件
for (int i = 0; i < caseList.Count; i++)
{
//给图片赋值
var caseModel = caseList[i];
caseModel.case_pic_list = picList.Where(c => c.caseid == caseModel.Id).Select(c => c.path).ToList();
caseModel.tags = tags.Where(c => c.caseid == caseModel.Id).Select(c => c.ItemName).ToList();
}
return new Response<PageInfo<List<DroneCaseInfoExt>>>
{
Result = new PageInfo<List<DroneCaseInfoExt>>
{
Items = caseList,
Total = total
},
Message = "获取数据成功"
};
}
}
/// <summary>
/// 根据案件分配查询案件
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public async Task<Response<PageInfo<List<DroneCaseInfoExt>>>> LoadDealCaseInfoListByLaw(QueryCaseInfoListReq obj)
{
//判断是否是乡镇管理员和审核管理员
var currentUser = _auth.GetCurrentUser();
//当前用户
var user = currentUser.User;
var roles = currentUser.Roles.Select(c => c.Id.ToString()).ToList();
//查询是否为案件处理角色
var _count = currentUser.Roles.Where(c => c.Name.Contains("案件处理")).Count();
obj.Init();
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
List<DroneCaseInfoExt> caseList = new List<DroneCaseInfoExt>();
RefAsync<int> total = 0;
ISugarQueryable<DroneCaseInfo> query = null;
var req = obj.MapTo<QueryCaseInfoListReq>();
if (_count == 0)
{
//已判读
req.is_intact = 1;
//根据组织机构过滤
req.is_dealer = 1;
//查询条件
query = QueryCaseInfoList(db, req, user, roles: roles);
}
else
{
//已判读
req.is_intact = 1;
//根据接收人处理人权限过滤
req.dispense_dealer = 1;
//查询条件
query = QueryCaseInfoList(db, req, user);
}
//拉取数据
var _caseList = await query.ToPageListAsync(req.page, req.limit, total);
string _caseListStr = JsonConvert.SerializeObject(_caseList);
caseList = JsonConvert.DeserializeObject<List<DroneCaseInfoExt>>(_caseListStr);
//查询的id
var caseIdList = caseList.Select(c => c.Id).ToList();
//查询的图片
var picList = db.Queryable<DroneFiles>().Where(c => caseIdList.Contains(c.relid) && c.type == 0 && c.tablename == "drone_caseinfo").Select(c => new { caseid = c.relid, c.path }).ToList();
//案件标签
var tags = await db.Queryable<DroneCaseInfoTag>()
.LeftJoin<SysDataItemDetail>((t, c) => t.tagid == c.ItemDetailId)
.Where((t, c) => caseIdList.Contains(t.caseid) && c.ItemCode == "DRONE_CASE_TAG")
.Select((t, c) => new
{
t.caseid,
c.ItemDetailId,
c.ItemName
}).ToListAsync();
//遍历案件
for (int i = 0; i < caseList.Count; i++)
{
//给图片赋值
var caseModel = caseList[i];
caseModel.case_pic_list = picList.Where(c => c.caseid == caseModel.Id).Select(c => c.path).ToList();
caseModel.tags = tags.Where(c => c.caseid == caseModel.Id).Select(c => c.ItemName).ToList();
}
return new Response<PageInfo<List<DroneCaseInfoExt>>>
{
Result = new PageInfo<List<DroneCaseInfoExt>>
{
Items = caseList,
Total = total
},
Message = "获取数据成功"
};
}
}
#endregion
#region 案件数量统计App查询案件 全部 未办理 办理中 已办结数量
/// <summary>
/// 查询案件公共过滤方法
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
private ISugarQueryable<Sugar_Drone_caseinfo> GetCaseQueryable(SqlSugarClient db, GetQueryCaseReq req = null, int? order = null)
{
if (req == null) req = new GetQueryCaseReq();
req.Init();
var query = db.Queryable<Sugar_Drone_caseinfo>()
//未删除
.Where(c => c.is_delete == null || c.is_delete == 0)
//已判读
.Where(c => c.is_intact == 1)
//处理状态
.WhereIF(req.handle_status_id != null, c => c.handle_status_id == req.handle_status_id)
//是否核查
.WhereIF(req.is_examine == 0, c => c.is_examine == 0 || c.is_examine == null)
.WhereIF(req.is_examine == 1, c => c.is_examine == 1)
//处理状态
.WhereIF(req.handle_status_list.Count != 0, c => req.handle_status_list.Contains(c.handle_status_id))
//是否合法
.WhereIF(req.is_illegal != null, c => c.is_illegal == req.is_illegal)
//处理措施
.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.streetid), c => c.streetid == req.streetid)
;
return query;
}
/// <summary>
/// 案件数量统计
/// </summary>
/// <returns></returns>
public async Task<Response<JObject>> CaseCountCensus(QueryCaseInfoListReq req)
{
//统计相关接口
//尽量减少查询数据库数据
//查询数据返回值尽量少
//数据类型统计使用GroupBy统计count，再进行类型过滤
//当前用户
var user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
var list = await QueryCaseInfoList(db, new QueryCaseInfoListReq
{
//以判读
is_intact = 1,
//上报人过滤
is_reporter = req.is_reporter,
//处理人过滤
is_dealer = req.is_dealer,
//执行人过滤：相当于 执法人员用
dispense_dealer = req.dispense_dealer,
}, user, 0)
.GroupBy(c => new { c.handle_status_id, c.is_examine })
.Select(c => new { c.handle_status_id, c.is_examine, count = SqlFunc.AggregateCount(c.Id) })
.ToListAsync();
//统计数量
JObject obj = new JObject();
obj.Add("total", list.Sum(c => c.count));
obj.Add("status0_0", list.Where(c => c.handle_status_id == 0 && (c.is_examine == null || c.is_examine == 0)).Sum(c => c.count));
obj.Add("status0_1", list.Where(c => c.handle_status_id == 0 && c.is_examine == 1).Sum(c => c.count));
obj.Add("status0", list.Where(c => c.handle_status_id == 0).Sum(c => c.count));
obj.Add("status1", list.Where(c => c.handle_status_id == 1).Sum(c => c.count));
obj.Add("status2", list.Where(c => c.handle_status_id == 2).Sum(c => c.count));
return new Response<JObject>
{
Result = obj,
Message = "获取数据成功"
};
}
}
/// <summary>
/// 待审核案件及未读消息数量
/// </summary>
/// <returns></returns>
public Response<JObject> UnreadMsgAndCaseCount()
{
JObject obj = new JObject
{
{ "unreadMsgCount", LoadMessageList(new QueryDroneCaseinfoReq { is_receiver = 1,msg_type = 1, is_read = 0, page = 1, limit = 1 }).Result.Result.Total },
//{ "unreadMsgCount",0 },
{ "wait2VerifyCaseCount", LoadWaitToVerifyCaseList(new QueryCaseInfoListReq { page = 1,limit = 1 }).Total },
{ "completeCaseCount", LoadDealCaseInfoListByLaw(new QueryCaseInfoListReq { handle_status_id = 2, page = 1,limit = 1 }).Result.Result.Total }
};
return new Response<JObject>
{
Result = obj,
Message = "获取数据成功"
};
}
/// <summary>
/// 首页通知统计数量
/// </summary>
/// <returns></returns>
public Response<JObject> GetIndexCensusDataApp()
{
var current = _auth.GetCurrentUser();
var user = current.User;
string countyName = _configuration.GetSection("CountyName").Value;
if (string.IsNullOrEmpty(countyName)) countyName = "费县";
//过滤到县级和镇级的组织机构，为了获取当前用户管理的组织机构
var orgs = current.Orgs.Where(c => c.Name == countyName || c.ParentName == countyName).OrderBy(c => c.CascadeId).ToList();
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
//组织机构
string orgName = string.Empty;
string orgid = string.Empty;
if (orgs.Count != 0)
{
orgName = orgs[0].Name;
orgid = orgs[0].Id.ToString();
}
//七天前，为了算超期的案件数量
var sevenDaysAgo = DateTime.Now.AddDays(-7);
var totalCount = QueryCaseInfoList(db, new QueryCaseInfoListReq { is_intact = 1, is_dealer = 1 }, user).Count();
//已整改的数量
var handleCount = QueryCaseInfoList(db, new QueryCaseInfoListReq { is_intact = 1, is_dealer = 1, handle_status_id = 1 }, user).Count() + QueryCaseInfoList(db, new QueryCaseInfoListReq { is_intact = 1, is_dealer = 1, handle_status_id = 2 }, user).Count();
//未整改的数量
var notHandleCount = QueryCaseInfoList(db, new QueryCaseInfoListReq { is_intact = 1, is_dealer = 1, handle_status_id = 0 }, user).Count();
//超期未整改的数量
var outTimeNotHandleCount = QueryCaseInfoList(db, new QueryCaseInfoListReq { is_intact = 1, is_dealer = 1, handle_status_id = 0 }, user).Where(c => c.identification_time < sevenDaysAgo).Count();
JObject result = new JObject();
result.Add("orgname", orgName);
result.Add("total", totalCount);
result.Add("handleCount", handleCount);
result.Add("notHandleCount", notHandleCount);
result.Add("outTimeNotHandleCount", outTimeNotHandleCount);
return new Response<JObject>
{
Result = result,
Message = "获取数据成功"
};
}
}
/// <summary>
/// 违建分类统计
/// </summary>
/// <returns></returns>
public async Task<Response<object>> GetDroneTypeCount(string streetId, int? is_dealer = null)
{
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
var list = await GetCaseQueryable(db)
.GroupBy(c => new { c.typename })
.Select(c => new
{
c.typename,
count = SqlFunc.AggregateCount(c.Id)
}).ToListAsync();
var user = _auth.GetCurrentUser().User;
//待核查
var status0 = QueryCaseInfoList(db, new QueryCaseInfoListReq { is_intact = 1, is_dealer = is_dealer, handle_status_id = 0, is_examine = 0, streetid = streetId }, user).Count();
//合法
var status1 = QueryCaseInfoList(db, new QueryCaseInfoListReq { is_intact = 1, is_dealer = is_dealer, is_illegal = 0, streetid = streetId }, user).Count();
//拆除
var status2 = QueryCaseInfoList(db, new QueryCaseInfoListReq { is_intact = 1, is_dealer = is_dealer, is_illegal = 1, measure_name_type = 0, streetid = streetId }, user).Count();
//查处
var status3 = QueryCaseInfoList(db, new QueryCaseInfoListReq { is_intact = 1, is_dealer = is_dealer, is_illegal = 1, measure_name_type = 1, streetid = streetId }, user).Count();
//完善手续
var status4 = QueryCaseInfoList(db, new QueryCaseInfoListReq { is_intact = 1, is_dealer = is_dealer, is_illegal = 1, measure_name_type = 2, streetid = streetId }, user).Count();
//伪变化
var status5 = QueryCaseInfoList(db, new QueryCaseInfoListReq { is_intact = 1, is_dealer = is_dealer, is_illegal = 2, streetid = streetId }, user).Count();
JArray array = new JArray();
JObject obj0 = new JObject();
obj0.Add("typename", "待核查");
obj0.Add("count", status0);
JObject obj1 = new JObject();
obj1.Add("typename", "合法");
obj1.Add("count", status1);
JObject obj2 = new JObject();
obj2.Add("typename", "拆除");
obj2.Add("count", status2);
JObject obj3 = new JObject();
obj3.Add("typename", "查处");
obj3.Add("count", status3);
JObject obj4 = new JObject();
obj4.Add("typename", "完善手续");
obj4.Add("count", status4);
JObject obj5 = new JObject();
obj5.Add("typename", "伪变化");
obj5.Add("count", status5);
array.Add(obj0);
array.Add(obj1);
array.Add(obj2);
array.Add(obj3);
array.Add(obj4);
array.Add(obj5);
return new Response<object>
{
Result = array,
Message = "获取数据成功"
};
}
}
/// <summary>
/// 案件详情
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Response<AddOrUpdateDroneCaseInfoReqExt>> GetCaseInfo(string id)
{
AddOrUpdateDroneCaseInfoReqExt res = new AddOrUpdateDroneCaseInfoReqExt();
res.Init();
//详情
Sugar_Drone_caseinfo info = null;
var flag = Infrastructure.Extensions.StringExtension.IsGuid(id);
if (flag)
{
info = await this.Repository.AsSugarClient().Queryable<Sugar_Drone_caseinfo>().Where(c => c.Id == id).FirstAsync();
}
else
{
info = await this.Repository.AsSugarClient().Queryable<Sugar_Drone_caseinfo>().Where(c => c.case_no == id).FirstAsync();
}
//工作区
var work_area = string.Empty;
if (info.streetname.Contains("费城街道") && !string.IsNullOrEmpty(info.communityid))
work_area = await this.Repository.AsSugarClient().Queryable<SysOrg>().Where(c => c.Id.ToString() == info.communityid).Select(c => c.CustomCode).FirstAsync();
//文件
var fileList = await this.Repository.AsSugarClient().Queryable<DroneFiles>().Where(c => c.relid == info.Id && (c.is_delete == null || c.is_delete == 0) && c.tablename == "drone_caseinfo").ToListAsync();
//图片
var pic_list = fileList.Where(c => c.type == 0).GroupBy(c => c.path).Select(c => c.Key).ToList();
var pics = fileList.Where(c => c.type == 0).ToList();
//视频
var video_list = fileList.Where(c => c.type == 1).GroupBy(c => c.path).Select(c => c.Key).ToList();
var videos = fileList.Where(c => c.type == 1).ToList();
//历史案件
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.work_area = work_area;
res.info = info;
res.pic_list = pic_list;
res.video_list = video_list;
res.pics = pics;
res.videos = videos;
res.relationCaseNo = relationCaseNoList;
res.tags = tags;
return new Response<AddOrUpdateDroneCaseInfoReqExt>
{
Result = res,
Message = "获取数据成功"
};
}
/// <summary>
/// 查询案件和处理信息的详情内容
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Response<CaseInfoAndDealInfoByIdResponse>> GetCaseInfoAndDealInfo(string id)
{
CaseInfoAndDealInfoByIdResponse result = new CaseInfoAndDealInfoByIdResponse();
result.caseinfo = (await GetCaseInfo(id)).Result;
//result.dealinfo = GetDroneCaseDeal("", id);
return new Response<CaseInfoAndDealInfoByIdResponse>
{
Result = result,
Message = "获取数据成功"
};
}
/// <summary>
/// 获取案件处理流程
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Response<List<CaseFlowLogModel>>> GetCaseFlowLog(string id)
{
using (var db = base.UnitWork.CreateContext())
{
var caseInfo = new Sugar_Drone_caseinfo();
if (Infrastructure.Extensions.StringExtension.IsGuid(id))
{
caseInfo = await this.Repository.AsSugarClient().Queryable<Sugar_Drone_caseinfo>().Where(c => c.Id == id).FirstAsync();
}
else
{
caseInfo = await this.Repository.AsSugarClient().Queryable<Sugar_Drone_caseinfo>().Where(c => c.case_no == id).FirstAsync();
}
var dealInfo = await this.Repository.AsSugarClient().Queryable<DroneCaseDeal>().Where(c => c.caseid == caseInfo.Id && (c.is_delete == null || c.is_delete == 0)).OrderBy(c => c.createtime, OrderByType.Desc).FirstAsync();
int? status = 1;
List<CaseFlowLogModel> list = new List<CaseFlowLogModel>();
//下发
CaseFlowLogModel first = new CaseFlowLogModel
{
status = 1,
name = "下发",
user_name = caseInfo.identification_user,
time = caseInfo.identification_time == null ? "" : caseInfo.identification_time.Value.ToString("yyyy-MM-dd HH:mm:ss"),
};
list.Add(first);
//核查
if (caseInfo.handle_status_id > 0 || (caseInfo.is_examine == 1 && caseInfo.handle_status_id == 0))
{
caseInfo.examiner_name = string.IsNullOrEmpty(caseInfo.examiner_name) ? "已核查" : caseInfo.examiner_name;
CaseFlowLogModel second = new CaseFlowLogModel
{
status = 1,
name = "核查",
user_name = caseInfo.examiner_name,
time = caseInfo.examine_time == null ? "" : caseInfo.examine_time.Value.ToString("yyyy-MM-dd HH:mm:ss"),
};
list.Add(second);
}
else
{
CaseFlowLogModel second = new CaseFlowLogModel
{
status = 0,
name = "核查",
user_name = "",
time = "",
};
list.Add(second);
}
//办理
if (caseInfo.handle_status_id > 0 && (caseInfo.is_illegal == 0 || caseInfo.is_illegal == 1))
{
CaseFlowLogModel third = new CaseFlowLogModel
{
status = 1,
user_name = caseInfo.deal_username,
time = caseInfo.deal_time == null ? "" : caseInfo.deal_time.Value.ToString("yyyy-MM-dd HH:mm:ss"),
};
if (caseInfo.is_illegal == 0)
third.name = "合法举证";
else
third.name = dealInfo.measure_name;
list.Add(third);
}
else if (caseInfo.handle_status_id == 0 && caseInfo.is_examine == 1)
{
CaseFlowLogModel third = new CaseFlowLogModel
{
status = 0,
name = "办理",
user_name = "",
time = "",
};
list.Add(third);
}
//审核
if (caseInfo.handle_status_id == 2)
{
CaseFlowLogModel fourth = new CaseFlowLogModel
{
status = 1,
name = "审核",
user_name = caseInfo.verifyuser,
time = caseInfo.verifytime == null ? "" : caseInfo.verifytime.Value.ToString("yyyy-MM-dd HH:mm:ss"),
};
list.Add(fourth);
//归档
CaseFlowLogModel fifth = new CaseFlowLogModel
{
status = 1,
name = "归档",
user_name = "已归档",
time = "",
};
list.Add(fifth);
}
else
{
CaseFlowLogModel fourth = new CaseFlowLogModel
{
status = 0,
name = "审核",
user_name = "",
time = "",
};
list.Add(fourth);
//归档
CaseFlowLogModel fifth = new CaseFlowLogModel
{
status = 0,
name = "归档",
user_name = "",
time = "",
};
list.Add(fifth);
}
return new Response<List<CaseFlowLogModel>>
{
Result = list,
Message = "获取数据成功"
};
}
}
/// <summary>
/// 查询案件
/// </summary>
/// <returns></returns>
public async Task<Response<object>> LoadCaseInfoData()
{
var user = _auth.GetCurrentUser().User;
var list = await this.Repository.AsSugarClient().Queryable<DroneCaseInfo>()
//是否完整性、是否关闭、是否删除过滤
.Where(c => c.is_intact == 1 && (c.is_closed == 0 || c.is_closed == null) && (c.is_delete == 0 || c.is_delete == null))
//执行人过滤
.WhereIF(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())
.OrderBy(c => c.createtime, OrderByType.Desc)
.Select(c => new { c.Id, c.lng, c.lat, c.case_name, c.handle_status_id })
.ToListAsync();
return new Response<object>
{
Result = list,
Message = "获取数据成功"
};
}
/// <summary>
/// 删除案件
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public async Task<Response<bool>> DeleteDroneCaseInfo(string[] ids)
{
var flag = await this.Repository.AsSugarClient().Updateable<Sugar_Drone_caseinfo>()
.SetColumns(c => new Sugar_Drone_caseinfo { is_delete = 1 })
.Where(c => ids.Contains(c.Id))
.ExecuteCommandAsync();
if (flag > 0)
return new Response<bool>
{
Result = true,
Message = "删除成功"
};
else
return new Response<bool>
{
Result = false,
Code = 500,
Message = "删除失败"
};
}
/// <summary>
/// 删除文件
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public async Task<Response<bool>> DeleteDroneFiles(string[] ids)
{
if (ids == null)
throw new Exception("ids is null");
int flag = -1;
flag = await this.Repository.AsSugarClient().Updateable<DroneFiles>()
.SetColumns(c => new DroneFiles { is_delete = 1 })
.Where(c => ids.Contains(c.Id))
.ExecuteCommandAsync();
if (flag >= 0)
return new Response<bool>
{
Result = true,
Message = "删除成功"
};
else
return new Response<bool>
{
Result = false,
Code = 500,
Message = "删除失败"
};
}
/// <summary>
/// 删除文件 - 通过路径删除
/// </summary>
/// <param name="paths"></param>
/// <returns></returns>
public async Task<Response<bool>> DeleteDroneFilesByPath(string[] paths)
{
if (paths == null)
throw new Exception("paths is null");
int flag = -1;
flag = await this.Repository.AsSugarClient().Updateable<DroneFiles>()
.SetColumns(c => new DroneFiles { is_delete = 1 })
.Where(c => paths.Contains(c.path))
.ExecuteCommandAsync();
if (flag >= 0)
return new Response<bool>
{
Result = true,
Message = "删除成功"
};
else
return new Response<bool>
{
Result = false,
Message = "删除失败"
};
}
/// <summary>
/// 获取所有案件中心点
/// APP获取中心点和图层的坐标点集合
/// </summary>
/// <returns></returns>
public async Task<Response<JToken>> GetAllDroneCaseCenterApp(string id)
{
var user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
if (!Infrastructure.Extensions.StringExtension.IsGuid(id))
{
id = (await db.Queryable<Sugar_Drone_caseinfo>().FirstAsync(a => a.case_no == id)).Id;
}
var query = db.Queryable<Sugar_Drone_caseinfo, DroneShpData>((a, b) => new JoinQueryInfos(
JoinType.Left, a.Id == b.relid
))
//过滤判读、是否删除、是否关闭、geom不为空
.Where((a, b) => a.is_intact == 1 && (a.is_delete == 0 || a.is_delete == null) && b.geom != null)
//根据权限过滤、组织机构权限、自己添加的数据权限
.WhereIF(user.Account != Define.SYSTEM_USERNAME, a => SqlFunc.Subqueryable<SysUserOrg>().Where(it => it.OrgId.ToString() == a.communityid && it.UserId == user.Id).Any()
|| SqlFunc.Subqueryable<SysUserOrg>().Where(it => it.OrgId.ToString() == a.streetid && it.UserId == user.Id).Any()
|| SqlFunc.Subqueryable<SysUserOrg>().Where(it => it.OrgId.ToString() == a.countyid && it.UserId == user.Id).Any()
)
//是否查询单条数据
.WhereIF(!string.IsNullOrEmpty(id), (a, b) => a.Id == id);
//查询：案件表关联图层表
var dt = query
//查询字段
.Select<dynamic>("a.case_no,a.handle_status_id,a.\"Id\" as id,a.countyname,a.streetname,a.communityname,a.createtime,a.case_description,a.address,a.typename,a.handle_status_name,st_astext(ST_Centroid(geom)) as point,st_astext(geom) cover")
//转为DataTable
.ToDataTable();
//转换JToken
var resJToken = dt.ToJson().ToJToken();
//返回的Array数据
JArray array = new JArray();
for (int i = 0; i < resJToken.Count(); i++)
{
JObject obj = new JObject();
var item = resJToken[i];
obj.Add("id", item["id"]);
obj.Add("case_no", item["case_no"]);
obj.Add("handle_status_id", item["handle_status_id"]);
obj.Add("countyname", item["countyname"]);
obj.Add("streetname", item["streetname"]);
obj.Add("communityname", item["communityname"]);
obj.Add("createtime", item["createtime"]);
obj.Add("case_description", item["case_description"]);
obj.Add("address", item["address"]);
obj.Add("typename", item["typename"]);
obj.Add("handle_status_name", item["handle_status_name"]);
//中心点数据
string pointStr = item["point"].ToString();
var pointInfo = pointStr.Replace("POINT(", "").Replace(")", "").Split(" ");
//中心点经纬度
obj.Add("longitude", Decimal.Round(Decimal.Parse(pointInfo[0]), 6));
obj.Add("latitude", Decimal.Round(Decimal.Parse(pointInfo[1]), 6));
//面数据
string coverStr = item["cover"].ToString();
//面的所有点
var point_list = coverStr.Replace("MULTIPOLYGON ZM (((", "").Replace(")", "").Replace("(", "").Split(",");
//面的数组
JArray pointArray = new JArray();
//遍历面的数据
foreach (var pointItem in point_list)
{
JObject pointObj = new JObject();
var pointItemList = pointItem.Split(" ");
pointObj.Add("longitude", Decimal.Round(Decimal.Parse(pointItemList[0]), 6));
pointObj.Add("latitude", Decimal.Round(Decimal.Parse(pointItemList[1]), 6));
pointArray.Add(pointObj);
}
//添加面的点数据
obj.Add("point_list", pointArray);
array.Add(obj);
}
return new Response<JToken>
{
Result = array,
Message = "获取数据成功"
};
}
}
/// <summary>
/// 添加图片保存
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<string>> AddDroneCasePic(AddFroneCasePicReq req)
{
var _user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
List<DroneFiles> filesList = new List<DroneFiles>();
if (string.IsNullOrEmpty(req.caseid) || string.IsNullOrEmpty(req.path))
{
throw new Exception("保存失败：图斑id和路径不能都为空！");
}
DroneFiles pic = new DroneFiles();
pic.Id = SqlSugar.SnowFlakeSingle.instance.getID().ToString();
pic.path = req.path;
pic.type = 0;
pic.tablename = "drone_caseinfo";
pic.createtime = DateTime.Now;
pic.createuser = _user.Id.ToString();
pic.createusername = _user.Name;
pic.is_delete = 0;
pic.relid = req.caseid;
var flag = await db.Insertable(pic).ExecuteCommandAsync();
if (flag >= 0)
return new Response<string>
{
Result = pic.Id,
Message = "删除成功"
};
else
return new Response<string>
{
Message = "添加图片失败"
};
}
}
/// <summary>
/// 添加案件处理图片
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<string>> AddDroneDealPic(AddDroneDealPicReq req)
{
var _user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
List<DroneFiles> filesList = new List<DroneFiles>();
if (string.IsNullOrEmpty(req.deal_id) || string.IsNullOrEmpty(req.path))
{
throw new Exception("保存失败：案件id和路径不能都为空！");
}
DroneFiles pic = new DroneFiles();
pic.Id = SqlSugar.SnowFlakeSingle.instance.getID().ToString();
pic.path = req.path;
pic.type = req.type;
pic.tablename = "drone_case_deal";
pic.createtime = DateTime.Now;
pic.createuser = _user.Id.ToString();
pic.createusername = _user.Name;
pic.is_delete = 0;
pic.relid = req.deal_id;
pic.tag = req.tag;
pic.latitude = req.latitude;
pic.longitude = req.longitude;
pic.angle = req.angle;
var flag = await db.Insertable(pic).ExecuteCommandAsync();
if (flag >= 0)
return new Response<string>
{
Result = pic.Id,
Message = "删除成功"
};
else
return new Response<string>
{
Message = "添加图片失败"
};
}
}
/// <summary>
/// 更新删除图片
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public async Task<Response<bool>> DeleteDroneCasePic(string id)
{
var flag = await this.Repository.AsSugarClient().Updateable<DroneFiles>().Where(c => id == c.Id).SetColumns(c => c.is_delete == 1).ExecuteCommandAsync();
if (flag >= 0)
return new Response<bool>
{
Result = true,
Message = "图片删除成功"
};
else
return new Response<bool>
{
Result = false,
Message = "图片删除失败"
};
}
#endregion
#region 通知
/// <summary>
/// 查询所有通信信息
/// </summary>
/// <returns></returns>
public async Task<Response<PageInfo<JToken>>> LoadMessageList(QueryDroneCaseinfoReq req)
{
var user = _auth.GetCurrentUser().User;
RefAsync<int> totalCount = 0;
List<DroneMessage> listPage = new List<DroneMessage>();
if (user.Account == Define.SYSTEM_USERNAME)
{
//超级管理员 看到全部通知
listPage = await base.Repository.ChangeRepository<SugarRepositiry<DroneMessage>>().AsQueryable().Where(c => c.is_delete == 0 || c.is_delete == null)
//根据标题过滤
.WhereIF(!string.IsNullOrEmpty(req.msg_title), c => c.msg_title.Contains(req.msg_title))
//通知类型过滤
.WhereIF(req.msg_type != null, c => c.msg_type == req.msg_type)
//排序
.OrderBy(c => c.createtime, OrderByType.Desc)
//分页
.ToPageListAsync(req.page, req.limit, totalCount);
}
else
{
listPage = await base.Repository.ChangeRepository<SugarRepositiry<DroneMessage>>().AsQueryable().Where(c => c.is_delete == 0 || c.is_delete == null)
//根据标题过滤
.WhereIF(!string.IsNullOrEmpty(req.msg_title), c => c.msg_title.Contains(req.msg_title))
//创建人过滤
.WhereIF(req.is_create == 1, c => c.createuser == user.Id.ToString())
//接收人过滤
.WhereIF(req.is_receiver == 1, c => SqlFunc.Subqueryable<Sugar_Drone_message_relevance>().Where(it => it.messageid == c.Id && it.userid == user.Id.ToString()).Any()
|| SqlFunc.Subqueryable<DroneRoleOrgRel>().Where(it => it.messageid == c.Id
&& SqlFunc.Subqueryable<SysUserOrg>().Where(itA => itA.OrgId.ToString() == it.orgid && itA.UserId == user.Id).Any()).Any()
|| SqlFunc.Subqueryable<DroneRoleOrgRel>().Where(it => it.messageid == c.Id
&& SqlFunc.Subqueryable<SysUserRole>().Where(itA => itA.RoleId.ToString() == it.roleid && itA.UserId == user.Id).Any()).Any()
)
//通知类型过滤
.WhereIF(req.msg_type != null, c => c.msg_type == req.msg_type)
//是否已读
.WhereIF(req.is_read == 1, c => SqlFunc.Subqueryable<DroneMessageRelevance>().Where(it => c.Id == it.messageid && it.is_read == 1).Any())
.WhereIF(req.is_read == 0, c => !SqlFunc.Subqueryable<DroneMessageRelevance>().Where(it => c.Id == it.messageid && it.is_read == 1).Any())
//排序
.OrderBy(c => c.createtime, OrderByType.Desc)
//分页
.ToPageListAsync(req.page, req.limit, totalCount);
}
var relList = await base.Repository.ChangeRepository<SugarRepositiry<DroneMessageRelevance>>().AsQueryable().Where(c => c.userid == user.Id.ToString()).Select(c => new { c.is_read, c.messageid }).ToListAsync();
JToken jTokenList = listPage.ToJson().ToJToken();
for (int i = 0; i < jTokenList.Count(); i++)
{
var item = jTokenList[i];
string messageid = item["Id"].ToString();
var _relItem = relList.Where(c => c.messageid == messageid).FirstOrDefault();
int? is_read = 0;
if (_relItem != null)
{
is_read = _relItem.is_read;
}
item["is_read"] = is_read;
}
return new Response<PageInfo<JToken>>
{
Result = new PageInfo<JToken>
{
Items = jTokenList,
Total = totalCount
},
Message = "获取数据成功",
};
}
/// <summary>
/// 查询通知信息详情
/// </summary>
/// <param name="id">通知信息id</param>
/// <returns></returns>
public async Task<Response<DroneMessage>> LoadMessageOld(string id)
{
return new Response<DroneMessage>
{
Result = await base.Repository.ChangeRepository<SugarRepositiry<DroneMessage>>().AsQueryable().Where(c => c.Id == id).FirstAsync(),
Message = "获取数据成功"
};
}
/// <summary>
/// 查询通知信息详情
/// </summary>
/// <param name="id">通知信息id</param>
/// <returns></returns>
public async Task<Response<QueryDroneCaseRecipientUserReq>> LoadMessage(string id)
{
var messageinfo = await base.Repository.ChangeRepository<SugarRepositiry<DroneMessage>>().AsQueryable().Where(c => c.Id == id).FirstAsync();
var model = messageinfo.MapTo<QueryDroneCaseRecipientUserReq>();
model.roles = base.Repository.ChangeRepository<SugarRepositiry<DroneRoleOrgRel>>().AsQueryable().Where(c => c.Id == id).Select(r => r.roleid).Distinct().ToArray();
model.orgs = base.Repository.ChangeRepository<SugarRepositiry<DroneRoleOrgRel>>().AsQueryable().Where(c => c.Id == id).Select(r => r.orgid).Distinct().ToArray();
return new Response<QueryDroneCaseRecipientUserReq>
{
Result = model,
Message = "获取数据成功"
};
}
/// <summary>
/// 根据权限查看通知详情
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Response<DroneMessage>> LoadMessageUser(string id)
{
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
var model = db.Queryable<DroneMessageRelevance, SysUser>((r, u) => new JoinQueryInfos(
JoinType.Left, r.userid == u.Id.ToString()
))
.Where(r => r.messageid == id).Select((r, u) => new
{
r.userid,
r.username
}).ToList();
return new Response<DroneMessage>
{
Result = await db.Queryable<DroneMessage>().Where(c => c.Id == id).FirstAsync(),
Message = "获取数据成功"
};
}
}
/// <summary>
/// 添加通知消息
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<string>> AddMessage(QueryDroneCaseRecipientUserReq req)
{
var _user = _auth.GetCurrentUser().User;
//初始化数据
req.Init();
var model = req.MapTo<Sugar_Drone_message>();
model.Init(_user);
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
//关联表数据
List<DroneMessageRelevance> relevanceList = new List<DroneMessageRelevance>();
//过滤条件：三表联合过滤
//Sugar_Relevance 用到角色过滤
//Sugar_Org 用到组织机构编码过滤
//不使用SqlFunc.SubQeury过滤是因为组织机构向级联查询Id过多，会导致Sql长度过大引起查询失效
Expressionable<SysUser, SysUserRole, SysUserOrg, SysOrg> exp = new Expressionable<SysUser, SysUserRole, SysUserOrg, SysOrg>();
exp.And((c, d, e, f) => false)
//角色过滤
.OrIF(req.roles.Length > 0, (c, d, e, f) => req.roles.Contains(d.RoleId.ToString()))
//用户过滤
.OrIF(req.recipientUserId.Length > 0, (c, d, e, f) => req.recipientUserId.Contains(c.Id.ToString()))
//发送给所有人
.OrIF(req.is_all == 1, (c, d, e, f) => c.Id.ToString() != null);
//组织列表
var orgInfoList = await db.Queryable<SysOrg>().WhereIF(req.orgs.Length > 0, c => req.orgs.Contains(c.Id.ToString())).WhereIF(req.is_all == 1, c => c.Id != null).ToListAsync();
//查询组织机构编码
var orgList = orgInfoList.Select(c => c.CascadeId).ToList();
//遍历组织机构编码
foreach (var item in orgList)
{
//部门过滤
exp.Or((c, d, e, f) => SqlFunc.StartsWith(f.CascadeId, item));
}
//添加当前用户
var userList = await db.Queryable<SysUser, SysUserRole, SysUserOrg, SysOrg>((c, d, e, f) => new JoinQueryInfos(
JoinType.Left, c.Id == d.UserId,
JoinType.Left, c.Id == e.UserId,
JoinType.Left, e.OrgId == f.Id
)).Where(exp.ToExpression())
.Select((c, d, e, f) => c)
.GroupBy(c => new { c.Id, c.Name })
.Select(c => new SysUser { Id = c.Id, Name = c.Name })
.ToListAsync();
var count = userList.Where(c => c.Id == _user.Id).Count();
if (count == 0)
{
//添加当前用户
userList.Add(new SysUser { Id = _user.Id, Name = _user.Name });
}
//遍历用户
for (int i = 0; i < userList.Count; i++)
{
var item = userList[i];
DroneMessageRelevance modelRel = new DroneMessageRelevance();
modelRel.Id = Guid.NewGuid().ToString();
modelRel.userid = item.Id.ToString();
modelRel.username = item.Name;
modelRel.createtime = DateTime.Now;
modelRel.createuser = _user.Id.ToString();
modelRel.is_read = 0;
modelRel.messageid = model.Id;
relevanceList.Add(modelRel);
}
//关联表数据
List<DroneRoleOrgRel> droList = new List<DroneRoleOrgRel>();
//角色列表
var roleList = db.Queryable<SysRole>().WhereIF(req.roles.Length > 0, c => req.roles.Contains(c.Id.ToString())).WhereIF(req.is_all == 1, c => c.Id != null).ToList();
for (int i = 0; i < roleList.Count; i++)
{
for (int j = 0; j < orgList.Count; j++)
{
//关联表
DroneRoleOrgRel modelRel = new DroneRoleOrgRel();
modelRel.Id = Guid.NewGuid().ToString();
modelRel.roleid = roleList[i].Id.ToString();
modelRel.orgid = orgList[j].ToString();
modelRel.createtime = DateTime.Now;
modelRel.createuser = _user.Id.ToString();
modelRel.messageid = model.Id;
droList.Add(modelRel);
}
}
//多表数据 使用事务提交
var transFlag = await db.UseTranAsync(async () =>
{
//通知数据
var flag = await db.Insertable(model).ExecuteCommandAsync();
//关联数据
var flag1 = await db.Insertable(relevanceList).ExecuteCommandAsync();
//关联数据
var flag2 = await db.Insertable(droList).ExecuteCommandAsync();
});
if (transFlag.IsSuccess)
{
return new Response<string>
{
Result = model.Id,
Message = "添加成功"
};
}
else
{
throw new Exception("添加失败");
}
}
}
/// <summary>
/// 根据部门角色关联进行添加
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public async Task<Response<string>> AddMessRoleOrg(QueryDroneCaseRecipientOrgRoleReq req)
{
var _user = _auth.GetCurrentUser().User;
var model = req.MapTo<Sugar_Drone_message>();
model.Init(_user);
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
//关联表数据
List<DroneRoleOrgRel> relevanceList = new List<DroneRoleOrgRel>();
//角色列表
var roleList = await db.Queryable<SysRole>().WhereIF(req.roles.Length > 0, c => req.roles.Contains(c.Id.ToString())).WhereIF(req.is_all == 1, c => c.Id != null).ToListAsync();
//组织列表
var orgList = await db.Queryable<SysOrg>().WhereIF(req.orgs.Length > 0, c => req.orgs.Contains(c.Id.ToString())).WhereIF(req.is_all == 1, c => c.Id != null).ToListAsync();
for (int i = 0; i < roleList.Count; i++)
{
for (int j = 0; j < orgList.Count; j++)
{
//关联表
DroneRoleOrgRel modelRel = new DroneRoleOrgRel();
modelRel.Id = Guid.NewGuid().ToString();
modelRel.roleid = roleList[i].Id.ToString();
modelRel.orgid = orgList[j].Id.ToString();
modelRel.createtime = DateTime.Now;
modelRel.createuser = _user.Id.ToString();
modelRel.messageid = model.Id;
relevanceList.Add(modelRel);
}
}
//多表数据 使用事务提交
var transFlag = await db.UseTranAsync(async () =>
{
//通知数据
var flag = await db.Insertable(model).ExecuteCommandAsync();
//关联数据
var flag1 = await db.Insertable(relevanceList).ExecuteCommandAsync();
});
if (transFlag.IsSuccess)
{
return new Response<string>
{
Result = model.Id,
Message = "添加成功"
};
}
else
{
throw new Exception("添加失败");
}
}
}
/// <summary>
/// 编辑通知消息
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public async Task<Response<string>> UpdateMessage(DroneMessage obj)
{
var _user = _auth.GetCurrentUser().User;
obj.createusername = _user.Name;
obj.createuser = _user.Id.ToString();
var flag = await this.Repository.AsSugarClient().Updateable<DroneMessage>(obj).ExecuteCommandAsync();
if (flag > 0)
{
return new Response<string>
{
Result = obj.Id,
Message = "编辑成功"
};
}
else
{
throw new Exception("编辑失败");
}
}
/// <summary>
/// 更新删除通知消息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Response<bool>> UpdateDelMessage(string id)
{
var flag = await this.Repository.AsSugarClient().Updateable<DroneMessage>().SetColumns(c => new DroneMessage
{
is_delete = 1
}).Where(c => id == c.Id).ExecuteCommandAsync();
if (flag > 0)
{
return new Response<bool>
{
Result = true,
Message = "删除成功"
};
}
else
{
throw new Exception("删除失败");
}
}
/// <summary>
/// 设置通知已读
/// </summary>
/// <param name="messageid"></param>
/// <returns></returns>
public async Task<Response<bool>> SetMessageRead(string messageid)
{
var user = _auth.GetCurrentUser().User;
var model = await base.Repository.ChangeRepository<SugarRepositiry<DroneMessageRelevance>>().AsQueryable()
.Where(c => c.userid == user.Id.ToString() && c.messageid == messageid)
.FirstAsync();
int flag = 0;
if (model == null)
{
model = new DroneMessageRelevance();
model.Id = Guid.NewGuid().ToString();
model.userid = user.Id.ToString();
model.username = user.Name;
model.createtime = DateTime.Now;
model.createuser = user.Id.ToString();
model.messageid = messageid;
model.is_delete = 0;
model.is_read = 1;
flag = await this.Repository.AsSugarClient().Insertable(model).ExecuteCommandAsync();
}
else
{
flag = await this.Repository.AsSugarClient().Updateable<DroneMessageRelevance>()
.SetColumns(c => new DroneMessageRelevance { is_read = 1 })
.Where(c => c.messageid == messageid && c.userid == user.Id.ToString())
.ExecuteCommandAsync();
}
if (flag > 0)
{
return new Response<bool>
{
Result = true,
Message = "设置成功"
};
}
else
{
throw new Exception("设置失败");
}
}
#endregion
#region 图层
/// <summary>
/// 根据表名获取矢量切片
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
public byte[] QueryVectorTileByTable(QueryVectorTileByTableReq req)
{
VectorTileSearchModel searchModel = new VectorTileSearchModel
{
x = req.x,
y = req.y,
z = req.z,
//field = "gid,fwbh,bh,fwlx,\"jzfsQg\",bxdcyy,fwlb,\"kzgzcsQg\",aqjdnf,address,fwsjfs,jgsj,cs,\"jzndQg\",dcmj,ywlfbxqx,sfjgaqjd,sfkzjg,\"kzcszpQg\",\"houseType\",\"hzlxQg\",hzxm,\"jglxQg\",aqjdjl,\"czqtQg\",jzmc,\"lwgQg\",\"dbkjqtQg\",fwmj,fwyt,zff,\"jzytQg\",streetid,streetname,communityid,communityname,",
field = req.field,
table = req.table,
filter = req.filter,
};
var result = _commonDataManager.VectorTile(searchModel);
return result;
}
/// <summary>
/// 添加违建图层
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public async Task<Response<string>> AddIllegalBuildeLayer(DroneShpData model)
{
var _user = _auth.GetCurrentUser().User;
model.createtime = DateTime.Now;
model.createuser = _user.Name;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
//获取主键
string _gid = _commonDataManager.GetMaxKeyVal("gid", "drone_shp_data", 1);
model.gid = int.Parse(_gid);//转为数字类型
//格式化数据
string _wktModel = _commonDataManager.WktDataConvert(model.geom, "MULTIPOLYGON ZM", 4);
model.geom = null;
StringBuilder geomSql = new StringBuilder();
geomSql.AppendFormat($" update drone_shp_data set geom = st_geomfromtext('{_wktModel}',4326) where gid = '{_gid}'");
//批量更新面积
StringBuilder sql = new StringBuilder();
sql.AppendFormat($" update drone_shp_data set area = st_area(st_transform(geom,4527)) where geom is not null and (area is null)");
//使用事务提交数据
var transFlag = await db.UseTranAsync(async () =>
{
//插入图斑数据
var flag = await db.Insertable(model).ExecuteCommandAsync();
//修改图斑数据
var flagGeom = await db.Ado.ExecuteCommandAsync(geomSql.ToString());
//修改图斑面积
var flagUpdate = await db.Ado.ExecuteCommandAsync(sql.ToString());
});
if (transFlag.IsSuccess)
return new Response<string>
{
Result = _gid,
Message = "新增成功"
};
else
return new Response<string>
{
Message = "新增失败"
};
}
}
/// <summary>
/// 编辑违法建筑和图层
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public async Task<Response<string>> UpdateIllegalBuildeLayer(DroneShpData model)
{
var _user = _auth.GetCurrentUser().User;
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
//格式化数据
string _wktModel = _commonDataManager.WktDataConvert(model.geom, "MULTIPOLYGON ZM", 4);
model.geom = _commonDataManager.WktToGeometry(_wktModel, 4326);
//更新图斑的数据
StringBuilder sqlUpdate = new StringBuilder();
sqlUpdate.AppendFormat($" update drone_shp_data set geom = '{model.geom}' where gid = '{model.gid}';");
//更新图斑的面积
StringBuilder sql = new StringBuilder();
sql.AppendFormat($" update drone_shp_data set area = st_area(st_transform(geom,4527)) where gid = {model.gid}");
//使用事务提交数据
var transFlag = await db.UseTranAsync(async () =>
{
//更新数据
var flag = await db.Ado.ExecuteCommandAsync(sqlUpdate.ToString());
//更新图斑面积
var flagUpdate = await db.Ado.ExecuteCommandAsync(sql.ToString());
});
if (transFlag.IsSuccess)
return new Response<string>
{
Result = model.gid.ToString(),
Message = "编辑成功"
};
else
return new Response<string>
{
Message = "编辑失败"
};
}
}
// <summary>
/// 删除图斑
/// </summary>
/// <param name="gid"></param>
/// <returns></returns>
public async Task<Response<bool>> DeleteDroneLayers(int gid)
{
var flag = await client.Deleteable<DroneShpData>().Where(c => c.gid == gid).ExecuteCommandAsync();
if (flag > 0)
return new Response<bool>
{
Result = true,
Message = "删除成功"
};
else
return new Response<bool>
{
Result = false,
Message = "删除失败"
};
}
/// <summary>
/// 查询主页违法图层数据
/// 获取所有图斑，主页用
/// </summary>
/// <param name="reqObj"></param>
/// <returns></returns>
public JToken LoadAllDroneGeoJson(QueryAllDroneGeoJsonReq reqObj)
{
QueryGeoJsonCommonReq req = new QueryGeoJsonCommonReq();
req.tablename = "drone_shp_data";
StringBuilder whereBuilder = new StringBuilder();
whereBuilder.AppendFormat($" where 1=1");
if (!string.IsNullOrEmpty(reqObj.typeid))
{
whereBuilder.AppendFormat($" and relid in (select \"Id\" from drone_caseinfo where typeid = '{reqObj.typeid}')");
}
req.where = whereBuilder.ToString();
req.pageIndex = 1;
req.limit = 9999;
var res = _commonDataManager.PgsqlGeoJsonCommon(req);
return res;
}
/// <summary>
/// 获取GeoJson
/// PC获取图层的GeoJson（判读页面用）
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public JToken GetDroneGeoJson(string id)
{
QueryGeoJsonCommonReq req = new QueryGeoJsonCommonReq();
req.tablename = "drone_shp_data";
req.where = "where relid = '" + id + "'";
req.pageIndex = 1;
req.limit = 2000;
var res = _commonDataManager.PgsqlGeoJsonCommon(req);
return res;
}
/// <summary>
/// app获取GeoJson
/// 替代app获取中心点和图层坐标点集合
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Response<JToken>> GetDroneGeoJsonApp(string id)
{
var user = _auth.GetCurrentUser().User;
StringBuilder sqlColumn = new StringBuilder();
sqlColumn.AppendFormat($" select aa.gid,aa.relid as id,bb.handle_status_id from drone_shp_data aa");
sqlColumn.AppendFormat($" left join drone_caseinfo bb on aa.relid = bb.\"Id\" ");
sqlColumn.AppendFormat($" where bb.is_intact = 1");
sqlColumn.AppendFormat($" and (is_delete = 0 or is_delete is null)");
sqlColumn.AppendFormat($" and (is_closed = 0 or is_closed is null)");
sqlColumn.AppendFormat($" and geom is not null");
sqlColumn.AppendFormat($" and (bb.communityid in (select CAST(\"OrgId\" AS VARCHAR) from sys_userorg where CAST(\"UserId\" AS VARCHAR) = '{user.Id}') or bb.createuser = '{user.Id}')");
if (!string.IsNullOrEmpty(id))
sqlColumn.AppendFormat($" and aa.relid = '{id}'");
sqlColumn.AppendFormat($" order by aa.gid asc ");
StringBuilder sqlGeom = new StringBuilder();
sqlGeom.AppendFormat($" select st_asgeojson(\"geom\") geom from drone_shp_data aa");
sqlGeom.AppendFormat($" left join drone_caseinfo bb on aa.relid = bb.\"Id\" ");
sqlGeom.AppendFormat($" where bb.is_intact = 1");
sqlGeom.AppendFormat($" and (is_delete = 0 or is_delete is null)");
sqlGeom.AppendFormat($" and (is_closed = 0 or is_closed is null)");
sqlGeom.AppendFormat($" and geom is not null");
sqlGeom.AppendFormat($" and (bb.communityid in (select CAST(\"OrgId\" AS VARCHAR) from sys_userorg where CAST(\"UserId\" AS VARCHAR) = '{user.Id}') or bb.createuser = '{user.Id}')");
if (!string.IsNullOrEmpty(id))
sqlGeom.AppendFormat($" and aa.relid = '{id}'");
sqlGeom.AppendFormat($" order by aa.gid asc ");
var res = _commonDataManager.PgsqlGeoJsonCommon(sqlColumn, sqlGeom, 1, 99999);
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
var dtCenter = await db.Queryable<DroneCaseInfo, DroneShpData>((a, b) => new JoinQueryInfos(
JoinType.Left, a.Id == b.relid
))
//过滤判读、是否删除、是否关闭、geom不为空
.Where((a, b) => a.is_intact == 1 && (a.is_delete == 0 || a.is_delete == null) && (a.is_closed == 0 || a.is_closed == null) && b.geom != null)
//根据权限过滤、组织机构权限、自己添加的数据权限
.Where($" ( a.communityid in (select CAST(\"OrgId\" AS VARCHAR) from sys_userorg where CAST(\"UserId\" AS VARCHAR) = '{user.Id}') or a.createuser = '{user.Id}')")
//是否查询单条数据
.WhereIF(!string.IsNullOrEmpty(id), (a, b) => a.Id == id)
//查询字段
.Select<dynamic>("gid,a.\"Id\" as id,typename,st_asgeojson(ST_Centroid(geom)) as point")
//转为DataTable
.ToDataTableAsync();
//转换格式
var jtokenCenter = dtCenter.ToJson().ToJToken();
//feature集合
JArray centerFeatureArr = new JArray();
//遍历每条数据
foreach (var item in jtokenCenter)
{
//feature对象
JObject centerFeatureObj = new JObject();
//类型
string _typename = item["typename"] == null ? "" : item["typename"].ToString();
if (_typename.IndexOf("-") > 0)
_typename = _typename.Substring(_typename.LastIndexOf("-") + 1);
//属性
JObject property = new JObject();
property.Add("id", item["id"]);
property.Add("gid", item["gid"]);
property.Add("typename", _typename);
//给feature对象赋值
centerFeatureObj.Add("type", "Feature");
centerFeatureObj.Add("properties", property);
centerFeatureObj.Add("geometry", item["point"].ToString().ToJToken());
//添加到feature集合
centerFeatureArr.Add(centerFeatureObj);
}
JObject pointObj = new JObject();
pointObj.Add("type", "FeatureCollection");
pointObj.Add("features", centerFeatureArr);
JObject obj = new JObject();
obj.Add("geojson", res);
obj.Add("points", pointObj);
return new Response<JToken>
{
Result = obj,
Message = "获取数据成功"
};
}
}
/// <summary>
/// 根据经纬度获取组织机构
/// </summary>
/// <param name="lng"></param>
/// <param name="lat"></param>
/// <returns></returns>
public async Task<Response<JObject>> GetOrgAreaByPoint(decimal lng, decimal lat)
{
//县id
string countyid = "";
//镇id
string streetid = "";
//村id
string communityid = "";
//SqlSugar实例
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
//查询坐标属于哪个县
StringBuilder sql = new StringBuilder();
sql.AppendFormat($" SELECT xzqmc FROM \"shp_drone_county\" where ST_Within(st_geomfromtext('POINT({lng} {lat})',4326), geom) = 't'");
var countyList = await db.SqlQueryable<dynamic>(sql.ToString()).ToListAsync();
if (countyList.Count > 0)
{
//县名称
string countyName = countyList[0].xzqmc;
//根据名称查询组织机构
var countyModel = await db.Queryable<SysOrg>().Where(c => c.Name == countyName).FirstAsync();
//组织机构不为空，给县id赋值
if (countyModel != null)
countyid = countyModel.Id.ToString();
}
//查询坐标属于哪个镇
sql = new StringBuilder();
sql.AppendFormat($" SELECT xzqmc FROM \"shp_drone_town\" where ST_Within(st_geomfromtext('POINT({lng} {lat})',4326), geom) = 't'");
var streetList = await db.SqlQueryable<dynamic>(sql.ToString()).ToListAsync();
if (streetList.Count > 0)
{
//镇名称
string streetName = streetList[0].xzqmc;
//根据名称查询组织机构
var streetModel = await db.Queryable<SysOrg>().Where(c => c.Name == streetName).FirstAsync();
//组织机构不为空，给镇id赋值
if (streetModel != null)
streetid = streetModel.Id.ToString();
}
if (!string.IsNullOrEmpty(streetid))
{
//查询组织机构属于哪个村
sql = new StringBuilder();
sql.AppendFormat($" SELECT zldwmc FROM \"shp_drone_community\" where ST_Within(st_geomfromtext('POINT({lng} {lat})',4326), geom) = 't'");
var communityidList = await db.SqlQueryable<dynamic>(sql.ToString()).ToListAsync();
if (communityidList.Count > 0)
{
//村名称
string communityName = communityidList[0].zldwmc;
//根据村名称和镇id查询组织机构
var communityModel = await db.Queryable<SysOrg>().Where(c => SqlFunc.StartsWith(c.Name, communityName) && c.ParentId.ToString() == streetid).FirstAsync();
//组织机构不为空，给村id赋值
if (communityModel != null)
communityid = communityModel.Id.ToString();
}
}
//拼接json数据，返回到前台
JObject obj = new JObject();
obj.Add("countyid", countyid);
obj.Add("streetid", streetid);
obj.Add("communityid", communityid);
return new Response<JObject>
{
Result = obj,
Message = "获取数据成功"
};
}
}
/// <summary>
/// 计算点与shp之间的距离
/// </summary>
/// <returns></returns>
public async Task<Response<bool>> GetPointShpDistance(string lng, string lat, string caseid)
{
//查询坐标点到
StringBuilder sql = new StringBuilder();
sql.AppendFormat($" select ");
sql.AppendFormat($" ST_Distance(");
sql.AppendFormat($" ST_Transform(st_geomfromtext('POINT({lng} {lat})',4326), 26986),");
sql.AppendFormat($" ST_Transform(st_setsrid(geom,4326), 26986)) as distance");
sql.AppendFormat($" from drone_shp_data where relid = '{caseid}' ");
sql.AppendFormat($" order by ST_Distance(");
sql.AppendFormat($" ST_Transform(st_geomfromtext('POINT({lng} {lat})',4326), 26986),");
sql.AppendFormat($" ST_Transform(st_setsrid(geom,4326), 26986)) limit 1");
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
var result = await db.Ado.GetDataTableAsync(sql.ToString());
if (result.Rows.Count > 0)
{
string distance = result.Rows[0]["distance"].ToString();
var distanceDecimal = Decimal.Round(Decimal.Parse(distance), 4);
int limitShpDistance = int.Parse(_configuration.GetSection("AppSetting:LimitShpDistance").Value);
if (distanceDecimal < limitShpDistance)
{
return new Response<bool>
{
Result = true,
Message = "获取数据成功"
};
}
}
throw new Exception("不在处理范围内");
}
}
#endregion
#region 统计
/// <summary>
/// 综合统计
/// </summary>
/// <returns></returns>
public async Task<Response<JArray>> CaseSynthesisCensus(QueryCaseInfoListReq req)
{
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
var currentUser = _auth.GetCurrentUser();
var _user = currentUser.User;
string countyName = _configuration.GetSection("CountyName").Value;
if (string.IsNullOrEmpty(countyName)) countyName = "费县";
var orgList = await db.Queryable<SysOrg>().Where(c => c.ParentName == countyName).OrderBy(c => c.SortNo, OrderByType.Asc).ToListAsync();
//无人机发现数量
var reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var allCountList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//完成核查数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var handleStatusList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//24小时未核查 (不包含已退回的部分)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_not_deal_hour24 = 1;
reqBody.is_drawback = 0;
var notDealHour24List = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//分类型统计案件数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var typeCountList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.typeid }).Select(c => new
{
c.streetid,
c.typeid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法类型数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var illegalCountList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.is_illegal }).Select(c => new
{
c.streetid,
c.is_illegal,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleStatusList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.handle_status_id }).Select(c => new
{
c.streetid,
c.handle_status_id,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态 面积
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleAreaList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.handle_status_id }).Select(c => new
{
c.streetid,
c.handle_status_id,
area = SqlFunc.AggregateSum(SqlFunc.ToDecimal(c.area)),
}).ToList();
//3日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 1;
reqBody.is_illegal = 1;
var notComplete3List = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//7日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 2;
reqBody.is_illegal = 1;
var notComplete7List = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 4;
reqBody.is_illegal = 1;
var notComplete30List = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日以上未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 5;
reqBody.is_illegal = 1;
var notComplete30MoreList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//var typeList = await db.Queryable<SysCategory>().Where(c => c.TypeId == "DRONE_CASE_TYPE").ToListAsync();
var typepid = await db.Queryable<SysDataItem>().Where(c => c.ItemCode == "DRONE_CASE_TYPE").FirstAsync();
var typeList = await db.Queryable<SysDataItemDetail>().Where(c => c.ItemId == typepid.ItemId).ToListAsync();
JArray array = new JArray();
for (int i = 0; i < orgList.Count; i++)
{
var item = orgList[i];
//无人机发现数量
int _allCount = allCountList.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//完成核查数量
int _handleStatus = handleStatusList.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//24小时未核查
int _notDealHour24 = notDealHour24List.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//违法类型数量
int _illegal0Count = illegalCountList.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == 0).Sum(c => c.count);
int _illegal1Count = illegalCountList.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == 1).Sum(c => c.count);
int _illegal2Count = illegalCountList.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == 2).Sum(c => c.count);
int _illegalNullCount = illegalCountList.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == null).Sum(c => c.count);
//违法 状态查询
int _illegalHandle2Status = illegalHandleStatusList.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.count);
int _illegalHandle01Status = illegalHandleStatusList.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.count);
//违法 面积查询
decimal? _illegalHandleAreaList = illegalHandleAreaList.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.area);
if (_illegalHandleAreaList != null)
{
_illegalHandleAreaList = Decimal.Round(_illegalHandleAreaList.Value / 666, 2);
}
decimal? _illegalHandle2AreaList = illegalHandleAreaList.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.area);
if (_illegalHandle2AreaList != null)
{
_illegalHandle2AreaList = Decimal.Round(_illegalHandle2AreaList.Value / 666, 2);
}
decimal? _illegalHandle01AreaList = illegalHandleAreaList.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.area);
if (_illegalHandle01AreaList != null)
{
_illegalHandle01AreaList = Decimal.Round(_illegalHandle01AreaList.Value / 666, 2);
}
//3日内未整改
int _notComplete3 = notComplete3List.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//7日内未整改
int _notComplete7 = notComplete7List.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//30日内未整改
int _notComplete30 = notComplete30List.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//30日以上未整改
int _notComplete30More = notComplete30MoreList.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
JObject obj = new JObject();
obj.Add("streetid", item.Id);
obj.Add("streetname", item.Name);
//分类型统计案件数量
//int _typeCount = typeCountList.Where(c => c.streetid == item.Id).Sum(c => c.count);
var typeid_fanxin = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANXIN").First()?.ItemDetailId;
var typeid_jiagai = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_JIAGAI").First()?.ItemDetailId;
var typeid_fanjian = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANJIAN").First()?.ItemDetailId;
var typeid_cunliang = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_CUNLIANG").First()?.ItemDetailId;
var typeid_tuitu = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_TUITU").First()?.ItemDetailId;
List<string> notTypeids = new List<string>
{
typeid_fanxin,
typeid_jiagai,
typeid_fanjian,
typeid_cunliang,
typeid_tuitu,
};
var typeFanxinCount = typeCountList.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_fanxin).Sum(c => c.count);
var typeJiagaiCount = typeCountList.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_jiagai).Sum(c => c.count);
var typeFanjianCount = typeCountList.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_fanjian).Sum(c => c.count);
var typeCunliangCount = typeCountList.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_cunliang).Sum(c => c.count);
var typeTuituCount = typeCountList.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_tuitu).Sum(c => c.count);
var typeOtherCount = typeCountList.Where(c => c.streetid == item.Id.ToString() && !notTypeids.Contains(c.typeid)).Sum(c => c.count);
//上报案件数量
obj.Add("allCount", _allCount);
//完成外业核查数量
obj.Add("handleStatus", _handleStatus);
//24h内未核查
obj.Add("notDealHour24", _notDealHour24);
obj.Add("typeFanxinCount", typeFanxinCount);
obj.Add("typeJiagaiCount", typeJiagaiCount);
obj.Add("typeFanjianCount", typeFanjianCount);
obj.Add("typeCunliangCount", typeCunliangCount);
obj.Add("typeTuituCount", typeTuituCount);
obj.Add("typeOtherCount", typeOtherCount);
//合法
obj.Add("illegal0Count", _illegal0Count);
//违法 外业核实后确定违法数
obj.Add("illegal1Count", _illegal1Count);
//伪变化
obj.Add("illegal2Count", _illegal2Count);
//违法类型 未知类型数量
obj.Add("illegalNullCount", _illegalNullCount);
//违法面积
obj.Add("illegalHandleAreaList", _illegalHandleAreaList);
//整改销号数
obj.Add("illegalHandle2Status", _illegalHandle2Status);
//尚未整改宗数
obj.Add("illegalHandle01Status", _illegalHandle01Status);
//销号面积
obj.Add("illegalHandle2AreaList", _illegalHandle2AreaList);
//未整改面积
obj.Add("illegalHandle01AreaList", _illegalHandle01AreaList);
//3日内未整改完成
obj.Add("notComplete3", _notComplete3);
//7日内未整改完成
obj.Add("notComplete7", _notComplete7);
//30日内未整改完成
obj.Add("notComplete30", _notComplete30);
//30日以上未整改完成
obj.Add("notComplete30More", _notComplete30More);
array.Add(obj);
}
JObject _obj = new JObject();
_obj.Add("streetname", "合计");
//上报案件数量
_obj.Add("allCount", array.Sum(c => int.Parse(c["allCount"].ToString())));
//完成外业核查数量
_obj.Add("handleStatus", array.Sum(c => int.Parse(c["handleStatus"].ToString())));
//24h内未核查
_obj.Add("notDealHour24", array.Sum(c => int.Parse(c["notDealHour24"].ToString())));
_obj.Add("typeFanxinCount", array.Sum(c => int.Parse(c["typeFanxinCount"].ToString())));
_obj.Add("typeJiagaiCount", array.Sum(c => int.Parse(c["typeJiagaiCount"].ToString())));
_obj.Add("typeFanjianCount", array.Sum(c => int.Parse(c["typeFanjianCount"].ToString())));
_obj.Add("typeCunliangCount", array.Sum(c => int.Parse(c["typeCunliangCount"].ToString())));
_obj.Add("typeTuituCount", array.Sum(c => int.Parse(c["typeTuituCount"].ToString())));
_obj.Add("typeOtherCount", array.Sum(c => int.Parse(c["typeOtherCount"].ToString())));
//合法
_obj.Add("illegal0Count", array.Sum(c => int.Parse(c["illegal0Count"].ToString())));
//违法 外业核实后确定违法数
_obj.Add("illegal1Count", array.Sum(c => int.Parse(c["illegal1Count"].ToString())));
//伪变化
_obj.Add("illegal2Count", array.Sum(c => int.Parse(c["illegal2Count"].ToString())));
//违法类型 未知类型数量
_obj.Add("illegalNullCount", array.Sum(c => int.Parse(c["illegalNullCount"].ToString())));
//违法面积
_obj.Add("illegalHandleAreaList", array.Sum(c => decimal.Parse(c["illegalHandleAreaList"].ToString())));
//整改销号数
_obj.Add("illegalHandle2Status", array.Sum(c => int.Parse(c["illegalHandle2Status"].ToString())));
//尚未整改宗数
_obj.Add("illegalHandle01Status", array.Sum(c => int.Parse(c["illegalHandle01Status"].ToString())));
//销号面积
_obj.Add("illegalHandle2AreaList", array.Sum(c => decimal.Parse(c["illegalHandle2AreaList"].ToString())));
//未整改面积
_obj.Add("illegalHandle01AreaList", array.Sum(c => decimal.Parse(c["illegalHandle01AreaList"].ToString())));
//3日内未整改完成
_obj.Add("notComplete3", array.Sum(c => int.Parse(c["notComplete3"].ToString())));
//7日内未整改完成
_obj.Add("notComplete7", array.Sum(c => int.Parse(c["notComplete7"].ToString())));
//30日内未整改完成
_obj.Add("notComplete30", array.Sum(c => int.Parse(c["notComplete30"].ToString())));
//30日以上未整改完成
_obj.Add("notComplete30More", array.Sum(c => int.Parse(c["notComplete30More"].ToString())));
array.Add(_obj);
return new Response<JArray>
{
Result = array,
Message = "获取数据成功"
};
}
}
public async Task<Response<JArray>> CaseSynthesisCensusSingle(QueryCaseInfoListReq req)
{
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
var currentUser = _auth.GetCurrentUser();
var _user = currentUser.User;
string sql1 = "select min(\"Level\") from sys_userorg where \"UserId\"=" + _user.Id;
int level = db.Ado.GetInt(sql1);
string countyName = "";
if ((string.IsNullOrEmpty(req.countyid) && level == 0) || (_user.Id == -1 && string.IsNullOrEmpty(req.countyid)))
{
countyName = "临沂市";
var orgList = await db.Queryable<SysOrg>().Where(c => c.ParentName == countyName).OrderBy(c => c.SortNo, OrderByType.Asc).ToListAsync();
//无人机发现数量
var reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var allCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//完成核查数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var handleStatusList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//24小时未核查 (不包含已退回的部分)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_not_deal_hour24 = 1;
reqBody.is_drawback = 0;
var notDealHour24List = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//分类型统计案件数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var typeCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.typeid }).Select(c => new
{
c.countyid,
c.typeid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法类型数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var illegalCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.is_illegal }).Select(c => new
{
c.countyid,
c.is_illegal,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleStatusList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.handle_status_id }).Select(c => new
{
c.countyid,
c.handle_status_id,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态 面积
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleAreaList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.handle_status_id }).Select(c => new
{
c.countyid,
c.handle_status_id,
area = SqlFunc.AggregateSum(SqlFunc.ToDecimal(c.area)),
}).ToList();
//3日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 1;
reqBody.is_illegal = 1;
var notComplete3List = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//7日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 2;
reqBody.is_illegal = 1;
var notComplete7List = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 4;
reqBody.is_illegal = 1;
var notComplete30List = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日以上未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 5;
reqBody.is_illegal = 1;
var notComplete30MoreList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//var typeList = await db.Queryable<SysCategory>().Where(c => c.TypeId == "DRONE_CASE_TYPE").ToListAsync();
var typepid = await db.Queryable<SysDataItem>().Where(c => c.ItemCode == "DRONE_CASE_TYPE").FirstAsync();
var typeList = await db.Queryable<SysDataItemDetail>().Where(c => c.ItemId == typepid.ItemId).ToListAsync();
JArray array = new JArray();
for (int i = 0; i < orgList.Count; i++)
{
var item = orgList[i];
//无人机发现数量
int _allCount = allCountList.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//完成核查数量
int _handleStatus = handleStatusList.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//24小时未核查
int _notDealHour24 = notDealHour24List.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//违法类型数量
int _illegal0Count = illegalCountList.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == 0).Sum(c => c.count);
int _illegal1Count = illegalCountList.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == 1).Sum(c => c.count);
int _illegal2Count = illegalCountList.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == 2).Sum(c => c.count);
int _illegalNullCount = illegalCountList.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == null).Sum(c => c.count);
//违法 状态查询
int _illegalHandle2Status = illegalHandleStatusList.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.count);
int _illegalHandle01Status = illegalHandleStatusList.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.count);
//违法 面积查询
decimal? _illegalHandleAreaList = illegalHandleAreaList.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.area);
if (_illegalHandleAreaList != null)
{
_illegalHandleAreaList = Decimal.Round(_illegalHandleAreaList.Value / 666, 2);
}
decimal? _illegalHandle2AreaList = illegalHandleAreaList.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.area);
if (_illegalHandle2AreaList != null)
{
_illegalHandle2AreaList = Decimal.Round(_illegalHandle2AreaList.Value / 666, 2);
}
decimal? _illegalHandle01AreaList = illegalHandleAreaList.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.area);
if (_illegalHandle01AreaList != null)
{
_illegalHandle01AreaList = Decimal.Round(_illegalHandle01AreaList.Value / 666, 2);
}
//3日内未整改
int _notComplete3 = notComplete3List.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//7日内未整改
int _notComplete7 = notComplete7List.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//30日内未整改
int _notComplete30 = notComplete30List.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//30日以上未整改
int _notComplete30More = notComplete30MoreList.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
JObject obj = new JObject();
obj.Add("streetid", item.Id);
obj.Add("streetname", item.Name);
//分类型统计案件数量
//int _typeCount = typeCountList.Where(c => c.streetid == item.Id).Sum(c => c.count);
var typeid_fanxin = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANXIN").First()?.ItemDetailId;
var typeid_jiagai = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_JIAGAI").First()?.ItemDetailId;
var typeid_fanjian = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANJIAN").First()?.ItemDetailId;
var typeid_cunliang = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_CUNLIANG").First()?.ItemDetailId;
var typeid_tuitu = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_TUITU").First()?.ItemDetailId;
List<string> notTypeids = new List<string>
{
typeid_fanxin,
typeid_jiagai,
typeid_fanjian,
typeid_cunliang,
typeid_tuitu,
};
var typeFanxinCount = typeCountList.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_fanxin).Sum(c => c.count);
var typeJiagaiCount = typeCountList.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_jiagai).Sum(c => c.count);
var typeFanjianCount = typeCountList.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_fanjian).Sum(c => c.count);
var typeCunliangCount = typeCountList.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_cunliang).Sum(c => c.count);
var typeTuituCount = typeCountList.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_tuitu).Sum(c => c.count);
var typeOtherCount = typeCountList.Where(c => c.countyid == item.Id.ToString() && !notTypeids.Contains(c.typeid)).Sum(c => c.count);
//上报案件数量
obj.Add("allCount", _allCount);
//完成外业核查数量
obj.Add("handleStatus", _handleStatus);
//24h内未核查
obj.Add("notDealHour24", _notDealHour24);
obj.Add("typeFanxinCount", typeFanxinCount);
obj.Add("typeJiagaiCount", typeJiagaiCount);
obj.Add("typeFanjianCount", typeFanjianCount);
obj.Add("typeCunliangCount", typeCunliangCount);
obj.Add("typeTuituCount", typeTuituCount);
obj.Add("typeOtherCount", typeOtherCount);
//合法
obj.Add("illegal0Count", _illegal0Count);
//违法 外业核实后确定违法数
obj.Add("illegal1Count", _illegal1Count);
//伪变化
obj.Add("illegal2Count", _illegal2Count);
//违法类型 未知类型数量
obj.Add("illegalNullCount", _illegalNullCount);
//违法面积
obj.Add("illegalHandleAreaList", _illegalHandleAreaList);
//整改销号数
obj.Add("illegalHandle2Status", _illegalHandle2Status);
//尚未整改宗数
obj.Add("illegalHandle01Status", _illegalHandle01Status);
//销号面积
obj.Add("illegalHandle2AreaList", _illegalHandle2AreaList);
//未整改面积
obj.Add("illegalHandle01AreaList", _illegalHandle01AreaList);
//3日内未整改完成
obj.Add("notComplete3", _notComplete3);
//7日内未整改完成
obj.Add("notComplete7", _notComplete7);
//30日内未整改完成
obj.Add("notComplete30", _notComplete30);
//30日以上未整改完成
obj.Add("notComplete30More", _notComplete30More);
array.Add(obj);
}
JObject _obj = new JObject();
_obj.Add("streetname", "合计");
//上报案件数量
_obj.Add("allCount", array.Sum(c => int.Parse(c["allCount"].ToString())));
//完成外业核查数量
_obj.Add("handleStatus", array.Sum(c => int.Parse(c["handleStatus"].ToString())));
//24h内未核查
_obj.Add("notDealHour24", array.Sum(c => int.Parse(c["notDealHour24"].ToString())));
_obj.Add("typeFanxinCount", array.Sum(c => int.Parse(c["typeFanxinCount"].ToString())));
_obj.Add("typeJiagaiCount", array.Sum(c => int.Parse(c["typeJiagaiCount"].ToString())));
_obj.Add("typeFanjianCount", array.Sum(c => int.Parse(c["typeFanjianCount"].ToString())));
_obj.Add("typeCunliangCount", array.Sum(c => int.Parse(c["typeCunliangCount"].ToString())));
_obj.Add("typeTuituCount", array.Sum(c => int.Parse(c["typeTuituCount"].ToString())));
_obj.Add("typeOtherCount", array.Sum(c => int.Parse(c["typeOtherCount"].ToString())));
//合法
_obj.Add("illegal0Count", array.Sum(c => int.Parse(c["illegal0Count"].ToString())));
//违法 外业核实后确定违法数
_obj.Add("illegal1Count", array.Sum(c => int.Parse(c["illegal1Count"].ToString())));
//伪变化
_obj.Add("illegal2Count", array.Sum(c => int.Parse(c["illegal2Count"].ToString())));
//违法类型 未知类型数量
_obj.Add("illegalNullCount", array.Sum(c => int.Parse(c["illegalNullCount"].ToString())));
//违法面积
_obj.Add("illegalHandleAreaList", array.Sum(c => decimal.Parse(c["illegalHandleAreaList"].ToString())));
//整改销号数
_obj.Add("illegalHandle2Status", array.Sum(c => int.Parse(c["illegalHandle2Status"].ToString())));
//尚未整改宗数
_obj.Add("illegalHandle01Status", array.Sum(c => int.Parse(c["illegalHandle01Status"].ToString())));
//销号面积
_obj.Add("illegalHandle2AreaList", array.Sum(c => decimal.Parse(c["illegalHandle2AreaList"].ToString())));
//未整改面积
_obj.Add("illegalHandle01AreaList", array.Sum(c => decimal.Parse(c["illegalHandle01AreaList"].ToString())));
//3日内未整改完成
_obj.Add("notComplete3", array.Sum(c => int.Parse(c["notComplete3"].ToString())));
//7日内未整改完成
_obj.Add("notComplete7", array.Sum(c => int.Parse(c["notComplete7"].ToString())));
//30日内未整改完成
_obj.Add("notComplete30", array.Sum(c => int.Parse(c["notComplete30"].ToString())));
//30日以上未整改完成
_obj.Add("notComplete30More", array.Sum(c => int.Parse(c["notComplete30More"].ToString())));
array.Add(_obj);
return new Response<JArray>
{
Result = array,
Message = "获取数据成功"
};
}
else if (string.IsNullOrEmpty(req.countyid) && level == 1)
{
string sql2 = "select \"OrgId\" from sys_userorg where \"Level\"=1 and \"UserId\"=" + _user.Id;
var liststring = db.Ado.SqlQuery<long>(sql2);
var orgList = await db.Queryable<SysOrg>().Where(c => liststring.Contains(c.Id)).OrderBy(c => c.SortNo, OrderByType.Asc).ToListAsync();
//无人机发现数量
var reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var allCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//完成核查数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var handleStatusList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//24小时未核查 (不包含已退回的部分)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_not_deal_hour24 = 1;
reqBody.is_drawback = 0;
var notDealHour24List = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//分类型统计案件数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var typeCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.typeid }).Select(c => new
{
c.countyid,
c.typeid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法类型数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var illegalCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.is_illegal }).Select(c => new
{
c.countyid,
c.is_illegal,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleStatusList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.handle_status_id }).Select(c => new
{
c.countyid,
c.handle_status_id,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态 面积
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleAreaList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.handle_status_id }).Select(c => new
{
c.countyid,
c.handle_status_id,
area = SqlFunc.AggregateSum(SqlFunc.ToDecimal(c.area)),
}).ToList();
//3日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 1;
reqBody.is_illegal = 1;
var notComplete3List = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//7日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 2;
reqBody.is_illegal = 1;
var notComplete7List = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 4;
reqBody.is_illegal = 1;
var notComplete30List = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日以上未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 5;
reqBody.is_illegal = 1;
var notComplete30MoreList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.countyid, c.streetname }).Select(c => new
{
c.countyid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//var typeList = await db.Queryable<SysCategory>().Where(c => c.TypeId == "DRONE_CASE_TYPE").ToListAsync();
var typepid = await db.Queryable<SysDataItem>().Where(c => c.ItemCode == "DRONE_CASE_TYPE").FirstAsync();
var typeList = await db.Queryable<SysDataItemDetail>().Where(c => c.ItemId == typepid.ItemId).ToListAsync();
JArray array = new JArray();
for (int i = 0; i < orgList.Count; i++)
{
var item = orgList[i];
//无人机发现数量
int _allCount = allCountList.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//完成核查数量
int _handleStatus = handleStatusList.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//24小时未核查
int _notDealHour24 = notDealHour24List.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//违法类型数量
int _illegal0Count = illegalCountList.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == 0).Sum(c => c.count);
int _illegal1Count = illegalCountList.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == 1).Sum(c => c.count);
int _illegal2Count = illegalCountList.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == 2).Sum(c => c.count);
int _illegalNullCount = illegalCountList.Where(c => c.countyid == item.Id.ToString() && c.is_illegal == null).Sum(c => c.count);
//违法 状态查询
int _illegalHandle2Status = illegalHandleStatusList.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.count);
int _illegalHandle01Status = illegalHandleStatusList.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.count);
//违法 面积查询
decimal? _illegalHandleAreaList = illegalHandleAreaList.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.area);
if (_illegalHandleAreaList != null)
{
_illegalHandleAreaList = Decimal.Round(_illegalHandleAreaList.Value / 666, 2);
}
decimal? _illegalHandle2AreaList = illegalHandleAreaList.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.area);
if (_illegalHandle2AreaList != null)
{
_illegalHandle2AreaList = Decimal.Round(_illegalHandle2AreaList.Value / 666, 2);
}
decimal? _illegalHandle01AreaList = illegalHandleAreaList.Where(c => c.countyid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.area);
if (_illegalHandle01AreaList != null)
{
_illegalHandle01AreaList = Decimal.Round(_illegalHandle01AreaList.Value / 666, 2);
}
//3日内未整改
int _notComplete3 = notComplete3List.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//7日内未整改
int _notComplete7 = notComplete7List.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//30日内未整改
int _notComplete30 = notComplete30List.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
//30日以上未整改
int _notComplete30More = notComplete30MoreList.Where(c => c.countyid == item.Id.ToString()).Sum(c => c.count);
JObject obj = new JObject();
obj.Add("streetid", item.Id);
obj.Add("streetname", item.Name);
//分类型统计案件数量
//int _typeCount = typeCountList.Where(c => c.streetid == item.Id).Sum(c => c.count);
var typeid_fanxin = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANXIN").First()?.ItemDetailId;
var typeid_jiagai = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_JIAGAI").First()?.ItemDetailId;
var typeid_fanjian = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANJIAN").First()?.ItemDetailId;
var typeid_cunliang = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_CUNLIANG").First()?.ItemDetailId;
var typeid_tuitu = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_TUITU").First()?.ItemDetailId;
List<string> notTypeids = new List<string>
{
typeid_fanxin,
typeid_jiagai,
typeid_fanjian,
typeid_cunliang,
typeid_tuitu,
};
var typeFanxinCount = typeCountList.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_fanxin).Sum(c => c.count);
var typeJiagaiCount = typeCountList.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_jiagai).Sum(c => c.count);
var typeFanjianCount = typeCountList.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_fanjian).Sum(c => c.count);
var typeCunliangCount = typeCountList.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_cunliang).Sum(c => c.count);
var typeTuituCount = typeCountList.Where(c => c.countyid == item.Id.ToString() && c.typeid == typeid_tuitu).Sum(c => c.count);
var typeOtherCount = typeCountList.Where(c => c.countyid == item.Id.ToString() && !notTypeids.Contains(c.typeid)).Sum(c => c.count);
//上报案件数量
obj.Add("allCount", _allCount);
//完成外业核查数量
obj.Add("handleStatus", _handleStatus);
//24h内未核查
obj.Add("notDealHour24", _notDealHour24);
obj.Add("typeFanxinCount", typeFanxinCount);
obj.Add("typeJiagaiCount", typeJiagaiCount);
obj.Add("typeFanjianCount", typeFanjianCount);
obj.Add("typeCunliangCount", typeCunliangCount);
obj.Add("typeTuituCount", typeTuituCount);
obj.Add("typeOtherCount", typeOtherCount);
//合法
obj.Add("illegal0Count", _illegal0Count);
//违法 外业核实后确定违法数
obj.Add("illegal1Count", _illegal1Count);
//伪变化
obj.Add("illegal2Count", _illegal2Count);
//违法类型 未知类型数量
obj.Add("illegalNullCount", _illegalNullCount);
//违法面积
obj.Add("illegalHandleAreaList", _illegalHandleAreaList);
//整改销号数
obj.Add("illegalHandle2Status", _illegalHandle2Status);
//尚未整改宗数
obj.Add("illegalHandle01Status", _illegalHandle01Status);
//销号面积
obj.Add("illegalHandle2AreaList", _illegalHandle2AreaList);
//未整改面积
obj.Add("illegalHandle01AreaList", _illegalHandle01AreaList);
//3日内未整改完成
obj.Add("notComplete3", _notComplete3);
//7日内未整改完成
obj.Add("notComplete7", _notComplete7);
//30日内未整改完成
obj.Add("notComplete30", _notComplete30);
//30日以上未整改完成
obj.Add("notComplete30More", _notComplete30More);
array.Add(obj);
}
JObject _obj = new JObject();
_obj.Add("streetname", "合计");
//上报案件数量
_obj.Add("allCount", array.Sum(c => int.Parse(c["allCount"].ToString())));
//完成外业核查数量
_obj.Add("handleStatus", array.Sum(c => int.Parse(c["handleStatus"].ToString())));
//24h内未核查
_obj.Add("notDealHour24", array.Sum(c => int.Parse(c["notDealHour24"].ToString())));
_obj.Add("typeFanxinCount", array.Sum(c => int.Parse(c["typeFanxinCount"].ToString())));
_obj.Add("typeJiagaiCount", array.Sum(c => int.Parse(c["typeJiagaiCount"].ToString())));
_obj.Add("typeFanjianCount", array.Sum(c => int.Parse(c["typeFanjianCount"].ToString())));
_obj.Add("typeCunliangCount", array.Sum(c => int.Parse(c["typeCunliangCount"].ToString())));
_obj.Add("typeTuituCount", array.Sum(c => int.Parse(c["typeTuituCount"].ToString())));
_obj.Add("typeOtherCount", array.Sum(c => int.Parse(c["typeOtherCount"].ToString())));
//合法
_obj.Add("illegal0Count", array.Sum(c => int.Parse(c["illegal0Count"].ToString())));
//违法 外业核实后确定违法数
_obj.Add("illegal1Count", array.Sum(c => int.Parse(c["illegal1Count"].ToString())));
//伪变化
_obj.Add("illegal2Count", array.Sum(c => int.Parse(c["illegal2Count"].ToString())));
//违法类型 未知类型数量
_obj.Add("illegalNullCount", array.Sum(c => int.Parse(c["illegalNullCount"].ToString())));
//违法面积
_obj.Add("illegalHandleAreaList", array.Sum(c => decimal.Parse(c["illegalHandleAreaList"].ToString())));
//整改销号数
_obj.Add("illegalHandle2Status", array.Sum(c => int.Parse(c["illegalHandle2Status"].ToString())));
//尚未整改宗数
_obj.Add("illegalHandle01Status", array.Sum(c => int.Parse(c["illegalHandle01Status"].ToString())));
//销号面积
_obj.Add("illegalHandle2AreaList", array.Sum(c => decimal.Parse(c["illegalHandle2AreaList"].ToString())));
//未整改面积
_obj.Add("illegalHandle01AreaList", array.Sum(c => decimal.Parse(c["illegalHandle01AreaList"].ToString())));
//3日内未整改完成
_obj.Add("notComplete3", array.Sum(c => int.Parse(c["notComplete3"].ToString())));
//7日内未整改完成
_obj.Add("notComplete7", array.Sum(c => int.Parse(c["notComplete7"].ToString())));
//30日内未整改完成
_obj.Add("notComplete30", array.Sum(c => int.Parse(c["notComplete30"].ToString())));
//30日以上未整改完成
_obj.Add("notComplete30More", array.Sum(c => int.Parse(c["notComplete30More"].ToString())));
array.Add(_obj);
return new Response<JArray>
{
Result = array,
Message = "获取数据成功"
};
}
else
{
if (level > 1)
{
JArray array1 = new JArray();
JObject _obj1 = new JObject();
_obj1.Add("streetname", "合计");
//上报案件数量
_obj1.Add("allCount", 0);
//完成外业核查数量
_obj1.Add("handleStatus", 0);
//24h内未核查
_obj1.Add("notDealHour24", 0);
_obj1.Add("typeFanxinCount", 0);
_obj1.Add("typeJiagaiCount", 0);
_obj1.Add("typeFanjianCount", 0);
_obj1.Add("typeCunliangCount", 0);
_obj1.Add("typeTuituCount", 0);
_obj1.Add("typeOtherCount", 0);
//合法
_obj1.Add("illegal0Count", 0);
//违法 外业核实后确定违法数
_obj1.Add("illegal1Count", 0);
//伪变化
_obj1.Add("illegal2Count", 0);
//违法类型 未知类型数量
_obj1.Add("illegalNullCount", 0);
//违法面积
_obj1.Add("illegalHandleAreaList", 0);
//整改销号数
_obj1.Add("illegalHandle2Status", 0);
//尚未整改宗数
_obj1.Add("illegalHandle01Status", 0);
//销号面积
_obj1.Add("illegalHandle2AreaList", 0);
//未整改面积
_obj1.Add("illegalHandle01AreaList", 0);
//3日内未整改完成
_obj1.Add("notComplete3", 0);
//7日内未整改完成
_obj1.Add("notComplete7", 0);
//30日内未整改完成
_obj1.Add("notComplete30", 0);
//30日以上未整改完成
_obj1.Add("notComplete30More", 0);
array1.Add(_obj1);
return new Response<JArray>
{
Result = array1,
Message = "获取数据成功"
};
}
string sql = "select \"Name\" from sys_org where \"Id\"=" + req.countyid;
string name = db.Ado.GetString(sql);
var orgList = await db.Queryable<SysOrg>().Where(c => c.ParentName == name).OrderBy(c => c.SortNo, OrderByType.Asc).ToListAsync();
//无人机发现数量
var reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var allCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//完成核查数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var handleStatusList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//24小时未核查 (不包含已退回的部分)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_not_deal_hour24 = 1;
reqBody.is_drawback = 0;
var notDealHour24List = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//分类型统计案件数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
var typeCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.typeid }).Select(c => new
{
c.streetid,
c.typeid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法类型数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
var illegalCountList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.is_illegal }).Select(c => new
{
c.streetid,
c.is_illegal,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleStatusList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.handle_status_id }).Select(c => new
{
c.streetid,
c.handle_status_id,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态 面积
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
var illegalHandleAreaList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.handle_status_id }).Select(c => new
{
c.streetid,
c.handle_status_id,
area = SqlFunc.AggregateSum(SqlFunc.ToDecimal(c.area)),
}).ToList();
//3日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 1;
reqBody.is_illegal = 1;
var notComplete3List = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//7日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 2;
reqBody.is_illegal = 1;
var notComplete7List = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 4;
reqBody.is_illegal = 1;
var notComplete30List = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日以上未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 5;
reqBody.is_illegal = 1;
var notComplete30MoreList = QueryCaseInfoSingleList(db, reqBody, _user, 0).GroupBy(c => new { c.streetid, c.streetname }).Select(c => new
{
c.streetid,
c.streetname,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//var typeList = await db.Queryable<SysCategory>().Where(c => c.TypeId == "DRONE_CASE_TYPE").ToListAsync();
var typepid = await db.Queryable<SysDataItem>().Where(c => c.ItemCode == "DRONE_CASE_TYPE").FirstAsync();
var typeList = await db.Queryable<SysDataItemDetail>().Where(c => c.ItemId == typepid.ItemId).ToListAsync();
JArray array = new JArray();
for (int i = 0; i < orgList.Count; i++)
{
var item = orgList[i];
//无人机发现数量
int _allCount = allCountList.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//完成核查数量
int _handleStatus = handleStatusList.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//24小时未核查
int _notDealHour24 = notDealHour24List.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//违法类型数量
int _illegal0Count = illegalCountList.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == 0).Sum(c => c.count);
int _illegal1Count = illegalCountList.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == 1).Sum(c => c.count);
int _illegal2Count = illegalCountList.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == 2).Sum(c => c.count);
int _illegalNullCount = illegalCountList.Where(c => c.streetid == item.Id.ToString() && c.is_illegal == null).Sum(c => c.count);
//违法 状态查询
int _illegalHandle2Status = illegalHandleStatusList.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.count);
int _illegalHandle01Status = illegalHandleStatusList.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.count);
//违法 面积查询
decimal? _illegalHandleAreaList = illegalHandleAreaList.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.area);
if (_illegalHandleAreaList != null)
{
_illegalHandleAreaList = Decimal.Round(_illegalHandleAreaList.Value / 666, 2);
}
decimal? _illegalHandle2AreaList = illegalHandleAreaList.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.area);
if (_illegalHandle2AreaList != null)
{
_illegalHandle2AreaList = Decimal.Round(_illegalHandle2AreaList.Value / 666, 2);
}
decimal? _illegalHandle01AreaList = illegalHandleAreaList.Where(c => c.streetid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.area);
if (_illegalHandle01AreaList != null)
{
_illegalHandle01AreaList = Decimal.Round(_illegalHandle01AreaList.Value / 666, 2);
}
//3日内未整改
int _notComplete3 = notComplete3List.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//7日内未整改
int _notComplete7 = notComplete7List.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//30日内未整改
int _notComplete30 = notComplete30List.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
//30日以上未整改
int _notComplete30More = notComplete30MoreList.Where(c => c.streetid == item.Id.ToString()).Sum(c => c.count);
JObject obj = new JObject();
obj.Add("streetid", item.Id);
obj.Add("streetname", item.Name);
//分类型统计案件数量
//int _typeCount = typeCountList.Where(c => c.streetid == item.Id).Sum(c => c.count);
var typeid_fanxin = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANXIN").First()?.ItemDetailId;
var typeid_jiagai = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_JIAGAI").First()?.ItemDetailId;
var typeid_fanjian = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_FANJIAN").First()?.ItemDetailId;
var typeid_cunliang = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_CUNLIANG").First()?.ItemDetailId;
var typeid_tuitu = typeList.Where(c => c.ItemValue == "DRONE_CASE_TYPE_TUITU").First()?.ItemDetailId;
List<string> notTypeids = new List<string>
{
typeid_fanxin,
typeid_jiagai,
typeid_fanjian,
typeid_cunliang,
typeid_tuitu,
};
var typeFanxinCount = typeCountList.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_fanxin).Sum(c => c.count);
var typeJiagaiCount = typeCountList.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_jiagai).Sum(c => c.count);
var typeFanjianCount = typeCountList.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_fanjian).Sum(c => c.count);
var typeCunliangCount = typeCountList.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_cunliang).Sum(c => c.count);
var typeTuituCount = typeCountList.Where(c => c.streetid == item.Id.ToString() && c.typeid == typeid_tuitu).Sum(c => c.count);
var typeOtherCount = typeCountList.Where(c => c.streetid == item.Id.ToString() && !notTypeids.Contains(c.typeid)).Sum(c => c.count);
//上报案件数量
obj.Add("allCount", _allCount);
//完成外业核查数量
obj.Add("handleStatus", _handleStatus);
//24h内未核查
obj.Add("notDealHour24", _notDealHour24);
obj.Add("typeFanxinCount", typeFanxinCount);
obj.Add("typeJiagaiCount", typeJiagaiCount);
obj.Add("typeFanjianCount", typeFanjianCount);
obj.Add("typeCunliangCount", typeCunliangCount);
obj.Add("typeTuituCount", typeTuituCount);
obj.Add("typeOtherCount", typeOtherCount);
//合法
obj.Add("illegal0Count", _illegal0Count);
//违法 外业核实后确定违法数
obj.Add("illegal1Count", _illegal1Count);
//伪变化
obj.Add("illegal2Count", _illegal2Count);
//违法类型 未知类型数量
obj.Add("illegalNullCount", _illegalNullCount);
//违法面积
obj.Add("illegalHandleAreaList", _illegalHandleAreaList);
//整改销号数
obj.Add("illegalHandle2Status", _illegalHandle2Status);
//尚未整改宗数
obj.Add("illegalHandle01Status", _illegalHandle01Status);
//销号面积
obj.Add("illegalHandle2AreaList", _illegalHandle2AreaList);
//未整改面积
obj.Add("illegalHandle01AreaList", _illegalHandle01AreaList);
//3日内未整改完成
obj.Add("notComplete3", _notComplete3);
//7日内未整改完成
obj.Add("notComplete7", _notComplete7);
//30日内未整改完成
obj.Add("notComplete30", _notComplete30);
//30日以上未整改完成
obj.Add("notComplete30More", _notComplete30More);
array.Add(obj);
}
JObject _obj = new JObject();
_obj.Add("streetname", "合计");
//上报案件数量
_obj.Add("allCount", array.Sum(c => int.Parse(c["allCount"].ToString())));
//完成外业核查数量
_obj.Add("handleStatus", array.Sum(c => int.Parse(c["handleStatus"].ToString())));
//24h内未核查
_obj.Add("notDealHour24", array.Sum(c => int.Parse(c["notDealHour24"].ToString())));
_obj.Add("typeFanxinCount", array.Sum(c => int.Parse(c["typeFanxinCount"].ToString())));
_obj.Add("typeJiagaiCount", array.Sum(c => int.Parse(c["typeJiagaiCount"].ToString())));
_obj.Add("typeFanjianCount", array.Sum(c => int.Parse(c["typeFanjianCount"].ToString())));
_obj.Add("typeCunliangCount", array.Sum(c => int.Parse(c["typeCunliangCount"].ToString())));
_obj.Add("typeTuituCount", array.Sum(c => int.Parse(c["typeTuituCount"].ToString())));
_obj.Add("typeOtherCount", array.Sum(c => int.Parse(c["typeOtherCount"].ToString())));
//合法
_obj.Add("illegal0Count", array.Sum(c => int.Parse(c["illegal0Count"].ToString())));
//违法 外业核实后确定违法数
_obj.Add("illegal1Count", array.Sum(c => int.Parse(c["illegal1Count"].ToString())));
//伪变化
_obj.Add("illegal2Count", array.Sum(c => int.Parse(c["illegal2Count"].ToString())));
//违法类型 未知类型数量
_obj.Add("illegalNullCount", array.Sum(c => int.Parse(c["illegalNullCount"].ToString())));
//违法面积
_obj.Add("illegalHandleAreaList", array.Sum(c => decimal.Parse(c["illegalHandleAreaList"].ToString())));
//整改销号数
_obj.Add("illegalHandle2Status", array.Sum(c => int.Parse(c["illegalHandle2Status"].ToString())));
//尚未整改宗数
_obj.Add("illegalHandle01Status", array.Sum(c => int.Parse(c["illegalHandle01Status"].ToString())));
//销号面积
_obj.Add("illegalHandle2AreaList", array.Sum(c => decimal.Parse(c["illegalHandle2AreaList"].ToString())));
//未整改面积
_obj.Add("illegalHandle01AreaList", array.Sum(c => decimal.Parse(c["illegalHandle01AreaList"].ToString())));
//3日内未整改完成
_obj.Add("notComplete3", array.Sum(c => int.Parse(c["notComplete3"].ToString())));
//7日内未整改完成
_obj.Add("notComplete7", array.Sum(c => int.Parse(c["notComplete7"].ToString())));
//30日内未整改完成
_obj.Add("notComplete30", array.Sum(c => int.Parse(c["notComplete30"].ToString())));
//30日以上未整改完成
_obj.Add("notComplete30More", array.Sum(c => int.Parse(c["notComplete30More"].ToString())));
array.Add(_obj);
return new Response<JArray>
{
Result = array,
Message = "获取数据成功"
};
}
}
}
public Response<MemoryStream> ListToExcel(JArray list, List<ModuleColumn> headers)
{
Response<MemoryStream> response = new Response<MemoryStream>();
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
#region 内容样式
IFont font1 = workbook.CreateFont(); //创建一个字体样式对象
font1.FontName = "Microsoft YaHei"; //和excel里面的字体对应
//font1.Boldweight = short.MaxValue;//字体加粗
font1.FontHeightInPoints = 12; //字体大小
ICellStyle style = workbook.CreateCellStyle(); //创建样式对象
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.Alignment = NPOI.SS.UserModel.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 = NPOI.SS.UserModel.HorizontalAlignment.Center;
style1.VerticalAlignment = VerticalAlignment.Center;
style1.SetFont(font); //将字体样式赋给样式对象
#endregion
#region 创建表头
int m = list.Count() / 60000 + 1;
for (int k = 0; k < m; k++)
{
ISheet sheet = workbook.CreateSheet("Sheet" + k.ToString());
IRow rowHeader = sheet.CreateRow(0);
rowHeader.Height = 20 * 30;
for (int i = 0; i < headers.Count; i++)
{
ModuleColumn header = headers[i];
rowHeader.CreateCell(i);
rowHeader.Cells[i].CellStyle = style1;
rowHeader.Cells[i].SetCellValue(header.value);
sheet.SetColumnWidth(i, 20 * 350);
}
#endregion
#region 填充数据
var val = (k + 1) * 60000;
var num = 60000;
if (val > list.Count())
{
num = list.Count() - k * 60000;
}
for (int i = 0; i < num; i++) //循环数据
{
JObject item = (JObject)list[k * 60000 + i]; //获取数据
IRow dataRow = sheet.CreateRow(i + 1); //创建行
for (int j = 0; j < headers.Count; j++) //循环表头
{
//数据处理
var objValue = "";
if (item.ContainsKey(headers[j].key))
{
objValue = item[headers[j].key].ToString();
}
else
{
objValue = "";
}
//创建单元格
dataRow.CreateCell(j);
dataRow.Cells[j].CellStyle = style; //添加单元格样式
if (objValue != null && !string.IsNullOrEmpty(objValue.ToString()))
{
dataRow.Cells[j].SetCellValue(objValue.ToString()); //填充Excel单元格
}
else
{
dataRow.Cells[j].SetCellValue(""); //填充Excel单元格
}
}
}
}
#endregion
response.Result = new MemoryStream();
workbook.Write(response.Result);
workbook = null;
response.Result.Close();
response.Result.Dispose();
response.Code = 200;
response.Message = "获取成功";
}
catch (Exception ex)
{
response.Code = 500;
response.Message = ex.Message;
}
return response;
}
/// <summary>
/// 综合统计，统计镇权限
/// </summary>
/// <returns></returns>
public JArray CaseSynthesisCensusStreet(QueryCaseInfoListReq req)
{
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
//当前账号的权限
var currentUser = _auth.GetCurrentUser();
var _user = currentUser.User;
var currentOrgs = currentUser.Orgs.Select(c => c.Id).ToList();
//当前县名称
string countyName = _configuration.GetSection("CountyName").Value;
if (string.IsNullOrEmpty(countyName)) countyName = "费县";
//查询系统中镇的数据
var streetList = db.Queryable<SysOrg>().Where(c => c.ParentName == countyName).ToList();
//当前用户所属的镇
var currentStreet = streetList.Where(c => currentOrgs.Contains(c.Id)).FirstOrDefault();
if (currentStreet == null)
throw new Exception("没有权限");
JArray array = new JArray();
if (currentStreet.Name == "费城街道")
{
// 获取工作区案件统计的数据
// array = GetSynthesisCensusWorkAreaData(db, currentStreet, _user, req);
}
else
{
// 获取街道案件统计数据
array = GetSynthesisCensusStreetData(db, currentStreet, _user, req);
}
return array;
}
}
/// <summary>
/// 获取街道案件统计数据
/// </summary>
/// <param name="db"></param>
/// <param name="currentStreet"></param>
/// <param name="_user"></param>
/// <param name="req"></param>
/// <returns></returns>
private JArray GetSynthesisCensusStreetData(SqlSugarClient db, SysOrg currentStreet, SysUser _user, QueryCaseInfoListReq req)
{
var orgList = db.Queryable<SysOrg>().Where(c => c.ParentId == currentStreet.Id).OrderBy(c => c.SortNo, OrderByType.Asc).ToList();
//无人机发现数量
var reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.streetid = currentStreet.Id.ToString();
var allCountList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid }).Select(c => new
{
c.communityid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//完成核查数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var handleStatusList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid }).Select(c => new
{
c.communityid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//24小时未核查 (不包含已退回的部分)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_not_deal_hour24 = 1;
reqBody.is_drawback = 0;
reqBody.streetid = currentStreet.Id.ToString();
var notDealHour24List = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid }).Select(c => new
{
c.communityid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//分类型统计案件数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.streetid = currentStreet.Id.ToString();
var typeCountList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid, c.typeid }).Select(c => new
{
c.communityid,
c.typeid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法类型数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_deal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var illegalCountList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid, c.is_illegal }).Select(c => new
{
c.communityid,
c.is_illegal,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态数量
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var illegalHandleStatusList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid, c.handle_status_id }).Select(c => new
{
c.communityid,
c.handle_status_id,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//违法数据 整改状态 面积
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_illegal = 1;
reqBody.is_deal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var illegalHandleAreaList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid, c.handle_status_id }).Select(c => new
{
c.communityid,
c.handle_status_id,
area = SqlFunc.AggregateSum(SqlFunc.ToDecimal(c.area)),
}).ToList();
//3日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 1;
reqBody.is_illegal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var notComplete3List = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid }).Select(c => new
{
c.communityid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//7日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 2;
reqBody.is_illegal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var notComplete7List = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid }).Select(c => new
{
c.communityid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日内未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 4;
reqBody.is_illegal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var notComplete30List = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid }).Select(c => new
{
c.communityid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
//30日以上未整改 (外业确认违法的案件)
reqBody = req.MapTo<QueryCaseInfoListReq>();
reqBody.is_intact = 1;
reqBody.is_complete = 0;
reqBody.out_time_flag = 5;
reqBody.is_illegal = 1;
reqBody.streetid = currentStreet.Id.ToString();
var notComplete30MoreList = QueryCaseInfoList(db, reqBody, _user, 0).GroupBy(c => new { c.communityid }).Select(c => new
{
c.communityid,
count = SqlFunc.AggregateCount(c.Id),
}).ToList();
var typepid = db.Queryable<SysDataItem>().Where(c => c.ItemCode == "DRONE_CASE_TYPE").First();
var typeList = db.Queryable<SysDataItemDetail>().Where(c => c.ItemId == typepid.ItemId).ToList();
//var typeList = db.Queryable<Sugar_Category>().Where(c => c.TypeId == "DRONE_CASE_TYPE").ToList();
JArray array = new JArray();
for (int i = 0; i < orgList.Count; i++)
{
var item = orgList[i];
//无人机发现数量
int _allCount = allCountList.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.count);
//完成核查数量
int _handleStatus = handleStatusList.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.count);
//24小时未核查
int _notDealHour24 = notDealHour24List.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.count);
//违法类型数量
int _illegal0Count = illegalCountList.Where(c => c.communityid == item.Id.ToString() && c.is_illegal == 0).Sum(c => c.count);
int _illegal1Count = illegalCountList.Where(c => c.communityid == item.Id.ToString() && c.is_illegal == 1).Sum(c => c.count);
int _illegal2Count = illegalCountList.Where(c => c.communityid == item.Id.ToString() && c.is_illegal == 2).Sum(c => c.count);
int _illegalNullCount = illegalCountList.Where(c => c.communityid == item.Id.ToString() && c.is_illegal == null).Sum(c => c.count);
//违法 状态查询
int _illegalHandle2Status = illegalHandleStatusList.Where(c => c.communityid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.count);
int _illegalHandle01Status = illegalHandleStatusList.Where(c => c.communityid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.count);
//违法 面积查询
decimal? _illegalHandleAreaList = illegalHandleAreaList.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.area);
if (_illegalHandleAreaList != null)
{
_illegalHandleAreaList = Decimal.Round(_illegalHandleAreaList.Value / 666, 2);
}
decimal? _illegalHandle2AreaList = illegalHandleAreaList.Where(c => c.communityid == item.Id.ToString() && c.handle_status_id == 2).Sum(c => c.area);
if (_illegalHandle2AreaList != null)
{
_illegalHandle2AreaList = Decimal.Round(_illegalHandle2AreaList.Value / 666, 2);
}
decimal? _illegalHandle01AreaList = illegalHandleAreaList.Where(c => c.communityid == item.Id.ToString() && c.handle_status_id < 2).Sum(c => c.area);
if (_illegalHandle01AreaList != null)
{
_illegalHandle01AreaList = Decimal.Round(_illegalHandle01AreaList.Value / 666, 2);
}
//3日内未整改
int _notComplete3 = notComplete3List.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.count);
//7日内未整改
int _notComplete7 = notComplete7List.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.count);
//30日内未整改
int _notComplete30 = notComplete30List.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.count);
//30日以上未整改
int _notComplete30More = notComplete30MoreList.Where(c => c.communityid == item.Id.ToString()).Sum(c => c.count);
JObject obj = new JObject();
//所属镇
obj.Add("streetid", currentStreet.Id);
obj.Add("streetname", currentStreet.Name);
//所属社区
obj.Add("communityid", item.Id);
obj.Add("communityname", item.Name);
obj.Add("is_area", 0);
//分类型统计案件数量
//int _typeCount = typeCountList.Where(c => c.streetid == item.Id).Sum(c => c.count);
var typeid_fanxin = typeList.Where(c => c.ItemCode == "DRONE_CASE_TYPE_FANXIN").First().ItemDetailId;
var typeid_jiagai = typeList.Where(c => c.ItemCode == "DRONE_CASE_TYPE_JIAGAI").First().ItemDetailId;
var typeid_fanjian = typeList.Where(c => c.ItemCode == "DRONE_CASE_TYPE_FANJIAN").First().ItemDetailId;
var typeid_cunliang = typeList.Where(c => c.ItemCode == "DRONE_CASE_TYPE_CUNLIANG").First().ItemDetailId;
var typeid_tuitu = typeList.Where(c => c.ItemCode == "DRONE_CASE_TYPE_TUITU").First().ItemDetailId;
List<string> notTypeids = new List<string>
{
typeid_fanxin,
typeid_jiagai,
typeid_fanjian,
typeid_cunliang,
typeid_tuitu,
};
var typeFanxinCount = typeCountList.Where(c => c.communityid == item.Id.ToString() && c.typeid == typeid_fanxin).Sum(c => c.count);
var typeJiagaiCount = typeCountList.Where(c => c.communityid == item.Id.ToString() && c.typeid == typeid_jiagai).Sum(c => c.count);
var typeFanjianCount = typeCountList.Where(c => c.communityid == item.Id.ToString() && c.typeid == typeid_fanjian).Sum(c => c.count);
var typeCunliangCount = typeCountList.Where(c => c.communityid == item.Id.ToString() && c.typeid == typeid_cunliang).Sum(c => c.count);
var typeTuituCount = typeCountList.Where(c => c.communityid == item.Id.ToString() && c.typeid == typeid_tuitu).Sum(c => c.count);
var typeOtherCount = typeCountList.Where(c => c.communityid == item.Id.ToString() && !notTypeids.Contains(c.typeid)).Sum(c => c.count);
//上报案件数量
obj.Add("allCount", _allCount);
//完成外业核查数量
obj.Add("handleStatus", _handleStatus);
//24h内未核查
obj.Add("notDealHour24", _notDealHour24);
obj.Add("typeFanxinCount", typeFanxinCount);
obj.Add("typeJiagaiCount", typeJiagaiCount);
obj.Add("typeFanjianCount", typeFanjianCount);
obj.Add("typeCunliangCount", typeCunliangCount);
obj.Add("typeTuituCount", typeTuituCount);
obj.Add("typeOtherCount", typeOtherCount);
//合法
obj.Add("illegal0Count", _illegal0Count);
//违法 外业核实后确定违法数
obj.Add("illegal1Count", _illegal1Count);
//伪变化
obj.Add("illegal2Count", _illegal2Count);
//违法类型 未知类型数量
obj.Add("illegalNullCount", _illegalNullCount);
//违法面积
obj.Add("illegalHandleAreaList", _illegalHandleAreaList);
//整改销号数
obj.Add("illegalHandle2Status", _illegalHandle2Status);
//尚未整改宗数
obj.Add("illegalHandle01Status", _illegalHandle01Status);
//销号面积
obj.Add("illegalHandle2AreaList", _illegalHandle2AreaList);
//未整改面积
obj.Add("illegalHandle01AreaList", _illegalHandle01AreaList);
//3日内未整改完成
obj.Add("notComplete3", _notComplete3);
//7日内未整改完成
obj.Add("notComplete7", _notComplete7);
//30日内未整改完成
obj.Add("notComplete30", _notComplete30);
//30日以上未整改完成
obj.Add("notComplete30More", _notComplete30More);
array.Add(obj);
}
JObject _obj = new JObject();
//所属镇
_obj.Add("streetid", currentStreet.Id);
_obj.Add("streetname", currentStreet.Name);
_obj.Add("communityid", "");
_obj.Add("communityname", "合计");
_obj.Add("is_area", 0);
//上报案件数量
_obj.Add("allCount", array.Sum(c => int.Parse(c["allCount"].ToString())));
//完成外业核查数量
_obj.Add("handleStatus", array.Sum(c => int.Parse(c["handleStatus"].ToString())));
//24h内未核查
_obj.Add("notDealHour24", array.Sum(c => int.Parse(c["notDealHour24"].ToString())));
_obj.Add("typeFanxinCount", array.Sum(c => int.Parse(c["typeFanxinCount"].ToString())));
_obj.Add("typeJiagaiCount", array.Sum(c => int.Parse(c["typeJiagaiCount"].ToString())));
_obj.Add("typeFanjianCount", array.Sum(c => int.Parse(c["typeFanjianCount"].ToString())));
_obj.Add("typeCunliangCount", array.Sum(c => int.Parse(c["typeCunliangCount"].ToString())));
_obj.Add("typeTuituCount", array.Sum(c => int.Parse(c["typeTuituCount"].ToString())));
_obj.Add("typeOtherCount", array.Sum(c => int.Parse(c["typeOtherCount"].ToString())));
//合法
_obj.Add("illegal0Count", array.Sum(c => int.Parse(c["illegal0Count"].ToString())));
//违法 外业核实后确定违法数
_obj.Add("illegal1Count", array.Sum(c => int.Parse(c["illegal1Count"].ToString())));
//伪变化
_obj.Add("illegal2Count", array.Sum(c => int.Parse(c["illegal2Count"].ToString())));
//违法类型 未知类型数量
_obj.Add("illegalNullCount", array.Sum(c => int.Parse(c["illegalNullCount"].ToString())));
//违法面积
_obj.Add("illegalHandleAreaList", array.Sum(c => decimal.Parse(c["illegalHandleAreaList"].ToString())));
//整改销号数
_obj.Add("illegalHandle2Status", array.Sum(c => int.Parse(c["illegalHandle2Status"].ToString())));
//尚未整改宗数
_obj.Add("illegalHandle01Status", array.Sum(c => int.Parse(c["illegalHandle01Status"].ToString())));
//销号面积
_obj.Add("illegalHandle2AreaList", array.Sum(c => decimal.Parse(c["illegalHandle2AreaList"].ToString())));
//未整改面积
_obj.Add("illegalHandle01AreaList", array.Sum(c => decimal.Parse(c["illegalHandle01AreaList"].ToString())));
//3日内未整改完成
_obj.Add("notComplete3", array.Sum(c => int.Parse(c["notComplete3"].ToString())));
//7日内未整改完成
_obj.Add("notComplete7", array.Sum(c => int.Parse(c["notComplete7"].ToString())));
//30日内未整改完成
_obj.Add("notComplete30", array.Sum(c => int.Parse(c["notComplete30"].ToString())));
//30日以上未整改完成
_obj.Add("notComplete30More", array.Sum(c => int.Parse(c["notComplete30More"].ToString())));
array.Add(_obj);
return array;
}
#endregion
#region 关闭案件，同步航飞库复提关闭用
/// <summary>
/// 关闭案件
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task<Response<bool>> CloseDroneCaseInfos(string no, string subject)
{
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
{
int flag = 1;
switch (subject)
{
case "Subject_WFYD":
flag = await db.Updateable<DroneCaseInfoSingle>()
.SetColumns(c => new DroneCaseInfoSingle
{
is_intact = 99,
close_time = DateTime.Now,
is_closed = 1,
close_reason = "复提关闭"
})
.Where(c => c.original_case_no == no)
.ExecuteCommandAsync();
break;
case "Subject_GDFLH":
flag = await db.Updateable<DroneCaseInfoFLH>()
.SetColumns(c => new DroneCaseInfoFLH
{
is_intact = 99,
close_time = DateTime.Now,
is_closed = 1,
close_reason = "复提关闭"
})
.Where(c => c.original_case_no == no)
.ExecuteCommandAsync();
break;
case "Subject_FFCK":
flag = await db.Updateable<DroneCaseInfoMinerals>()
.SetColumns(c => new DroneCaseInfoMinerals
{
is_closed = 1,
close_reason = "复提关闭"
})
.Where(c => c.original_case_no == no)
.ExecuteCommandAsync();
break;
case "Subject_XCSJ":
flag = await db.Updateable<DroneCaseInfoXcsj>()
.SetColumns(c => new DroneCaseInfoXcsj
{
is_intact = 99,
close_time = DateTime.Now,
is_closed = 1,
close_reason = "复提关闭"
})
.Where(c => c.original_case_no == no)
.ExecuteCommandAsync();
break;
case "Subject_ZDWT1":
flag = await db.Updateable<DroneCaseInfoZdwt1>()
.SetColumns(c => new DroneCaseInfoZdwt1
{
is_intact = 99,
close_time = DateTime.Now,
is_closed = 1,
close_reason = "复提关闭"
})
.Where(c => c.original_case_no == no)
.ExecuteCommandAsync();
break;
case "Subject_ZDWT2":
flag = await db.Updateable<DroneCaseInfoZdwt2>()
.SetColumns(c => new DroneCaseInfoZdwt2
{
is_intact = 99,
close_time = DateTime.Now,
is_closed = 1,
close_reason = "复提关闭"
})
.Where(c => c.original_case_no == no)
.ExecuteCommandAsync();
break;
case "Subject_STBHHXJG":
flag = await db.Updateable<DroneCaseInfoSTHX>()
.SetColumns(c => new DroneCaseInfoSTHX
{
is_intact = 99,
close_time = DateTime.Now,
is_closed = 1,
close_reason = "复提关闭"
})
.Where(c => c.original_case_no == no)
.ExecuteCommandAsync();
break;
case "Subject_STXF":
flag = await db.Updateable<DroneCaseInfoSTXF>()
.SetColumns(c => new DroneCaseInfoSTXF
{
is_intact = 99,
close_time = DateTime.Now,
is_closed = 1,
close_reason = "复提关闭"
})
.Where(c => c.original_case_no == no)
.ExecuteCommandAsync();
break;
}
if (flag > 0)
return new Response<bool>
{
Result = true,
Message = "关闭成功"
};
else
return new Response<bool>
{
Code = 500,
Result = false,
Message = "关闭失败"
};
}
}
#endregion
}
}
Reference in New Issue View Git Blame Copy Permalink