115 lines
4.3 KiB
C#
115 lines
4.3 KiB
C#
using Infrastructure;
|
||
using OpenAuth.App.Base;
|
||
using OpenAuth.App.Interface;
|
||
using OpenAuth.App.Response;
|
||
using OpenAuth.Repository;
|
||
using OpenAuth.Repository.Domain;
|
||
using SqlSugar;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Threading.Tasks;
|
||
using OpenAuth.App.BaseApp.Base;
|
||
|
||
namespace OpenAuth.App
|
||
{
|
||
public class DroneClueReportingApp : SqlSugarBaseApp<DroneClueReporting, SugarDbContext>
|
||
{
|
||
public DroneClueReportingApp(ISugarUnitOfWork<SugarDbContext> unitWork, ISimpleClient<DroneClueReporting> repository, IAuth auth) : base(unitWork, repository, auth)
|
||
{
|
||
}
|
||
|
||
public PageInfo<List<DroneClueReporting>> QueryClueReporting(string content, string beginDate, string endDate, int pageIndex, int pageSize)
|
||
{
|
||
int totalCount = 0;
|
||
|
||
var beginTime = Convert.ToDateTime(beginDate);
|
||
var endTime = Convert.ToDateTime(endDate);
|
||
|
||
var list = base.Repository.AsQueryable()
|
||
.WhereIF(!string.IsNullOrEmpty(content), a => a.Content.Contains(content))
|
||
.WhereIF((!string.IsNullOrEmpty(beginDate) && !string.IsNullOrEmpty(endDate)), a => SqlFunc.Between(a.CreateTime, beginTime, endTime))
|
||
.OrderBy(a => a.CreateTime, OrderByType.Desc)
|
||
.ToPageList(pageIndex, pageSize, ref totalCount);
|
||
|
||
return new PageInfo<List<DroneClueReporting>>
|
||
{
|
||
Items = list,
|
||
Total = totalCount
|
||
};
|
||
}
|
||
|
||
#region 摄像头信息查询
|
||
/// <summary>
|
||
/// 获取摄像头信息
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
public List<dynamic> GetCamerInfos()
|
||
{
|
||
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
|
||
{
|
||
var list = db.Queryable<DroneCameraInfo>()
|
||
.LeftJoin<DroneRtuInfo>((r, a) => r.rtuId == a.rtuid)
|
||
.Where((r, a) => r.lnglat != "")
|
||
.OrderBy((r, a) => a.rtuid)
|
||
.Select<dynamic>((r, a) => new
|
||
{
|
||
r.deviceId,
|
||
r.deviceNum,
|
||
r.address,
|
||
r.lnglat,
|
||
r.deviceName,
|
||
a.ip,
|
||
a.port,
|
||
a.appKey,
|
||
a.appSecret,
|
||
a.rtuName
|
||
}).ToList();
|
||
return list;
|
||
}
|
||
}
|
||
|
||
public bool AddCameraInfo(List<DroneCameraInfo> ca)
|
||
{
|
||
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
|
||
{
|
||
try
|
||
{
|
||
var list = db.Queryable<DroneCameraInfo>()?.Select(r => r.deviceNum).ToList();
|
||
if (list != null)
|
||
{
|
||
var calist = ca.Where(r => !list.Contains(r.deviceNum)).ToList();
|
||
db.Insertable(calist).ExecuteCommand();
|
||
}
|
||
return true;
|
||
}
|
||
catch
|
||
{
|
||
return false;
|
||
}
|
||
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据坐标点获取摄像头信息（2公里范围内）
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
public List<dynamic> GetCamerInfoByLngLat(string lng, string lat, double distince)
|
||
{
|
||
using (var db = base.UnitWork.CreateContext().Db.CopyNew())
|
||
{
|
||
//查询组织机构属于哪个村
|
||
var sql = new StringBuilder();
|
||
sql.AppendFormat($" SELECT c.\"deviceId\", c.\"deviceNum\",c.\"deviceName\",c.lnglat,c.address,r.ip,r.port,r.\"appKey\",r.\"appSecret\",r.\"rtuName\" from drone_camerainfo c left join drone_rtuinfo r on c.\"rtuId\"=r.rtuid\r\nwhere lnglat is not null and lnglat!='' and ST_DWithin(\r\n ST_GeographyFromText('POINT({lng} {lat})'),\r\n ST_GeographyFromText('POINT('||split_part(lnglat, ',', 1) ||' '|| split_part(lnglat, ',', 2) ||')'),{distince}\r\n)=TRUE;\r\n");
|
||
var communityidList = db.SqlQueryable<dynamic>(sql.ToString()).ToList();
|
||
return communityidList;
|
||
}
|
||
}
|
||
|
||
#endregion
|
||
|
||
}
|
||
}
|