/// <summary>
/// 供移动端调用
/// </summary>
/// <param name="dl"></param>
/// <returns></returns>
//public async Task<IApiResult> GetDeviceList(Models.Parameter.PUsersByUserID user)
//{
// return await Task.Run(() => {
// ApiResult hr = new IApiResult();
// string strSql = @"select d.DeviceID,d.SerialNumber, case when DeviceName='' then d.SerialNumber else DeviceName end DeviceName,l.LastCommunication ,l.DataContext,l.Speed,d.GroupID,d.HireExpireDate,datediff(MI,StopStartUtcDate,serverutcdate) StopTime,
// (select COUNT(-1) from ExceptionMessage e where e.Deleted=0 and e.DeviceID=d.DeviceID)ExceptionCount,l.OLat Lat,l.OLng Lng,di.DataText as Model,case when di.AccountID=2 then 7 else di.SortOrder end as OffLineMinute
// from devices d
// left join lklocation l on l.DeviceID = d.DeviceID inner join Dictionary di on di.DataValue=d.Model
// where d.UserID = 7 and d.deleted = 0
// group by d.DeviceID,d.SerialNumber,d.DeviceName,l.DataContext,l.Speed ,d.GroupID,l.LastCommunication,d.HireExpireDate,StopStartUtcDate,serverutcdate,l.OLat,l.OLng,di.DataText,d.ServerID2,di.SortOrder,di.AccountID
// order by d.DeviceName collate Chinese_PRC_CS_AS_KS_WS asc";
// using (System.Data.IDbConnection conn = new SqlConnection(connectionString))
// {
// var list = conn.QueryAsync<Models.Entity.Monitor.DeviceList>(strSql, new { UserID = user.userid }).Result.ToList();
// list.ForEach(item => {
// try
// {
// string IsStop = "1"; //运动
// if (item.Speed < 7.5) //速度 小于7.5 的过滤掉
// {
// item.Speed = 0.00;
// IsStop = "0"; // 停止
// }
// string[] ContextList = (item.DataContext ?? "0-0-0-0").Split('-');
// if (ContextList.Length == 1)
// {
// item.DataContext = "0-0-0-0-" + item.DataContext;
// ContextList = item.DataContext.Split('-');
// }
// item.Status = GetDevicesStatus(item.LastCommunication, item.HireExpireDate, item.OffLineMinute);
// int statusmin = 0;
// if (item.Status == 2)
// {
// statusmin = item.LastCommunication == default(DateTime) ? 0 : (DateTime.Now - item.LastCommunication).TotalMinutes.ToString("0").toInt();
// }
// else if (item.Status == 4)
// {
// statusmin = (DateTime.Now - item.HireExpireDate).TotalMinutes.ToString("0").toInt();
// }
// else if (item.Status == 1 && IsStop == "0")
// {
// statusmin = item.StopTime ?? 0;
// }
// item.OffLineMinute = null;
// item.GpsStatusMinute = statusmin;
// }
// catch (Exception ex)
// {
// Log.Error(this, ex);
// }
// });
// hr.result = list;
// hr.message = "查询成功.";
// }
// return hr;
// });
//}
public async Task <IApiResult> GetDevicesList(Models.Parameter.P_Users.P_UsersByUserID dl)
{
return(await Task.Run(() =>
{
IApiResult hr = new IApiResult();
//count(e.ExceptionID) ExceptionCount left join ExceptionMessage e on e.DeviceID = d.deviceid
string strSql = @"select d.DeviceID,d.SerialNumber,d.DeviceName,ISNULL(g.GroupID,-1) GroupID, ISNULL(GroupName,'Default')GroupName, d.UserID, Username,l.LastCommunication,
datediff(MI,l.LastCommunication, getdate()) status,Speed,l.DataContext,l.Course,d.Icon
,DATEADD(HH,8, l.DeviceUtcDate)DeviceUtcDate,datediff(MI,StopStartUtcDate,serverutcdate) StopTime,d.CarImg,d.Model,di.DataText, DATEDIFF(mi,l.lastcommunication,getdate()) OfflineTime,
l.DataType,DATEADD(HH,8 ,l.StopStartUtcDate)StopStartUtcDate,d.HireExpireDate,l.OLat Lat,l.OLng Lng,case when di.AccountID=2 then 7 else di.SortOrder end as offLineMi
from Devices d full join Groups g on g.GroupID=d.GroupID left join LKLocation l on l.DeviceID = d.DeviceID left join Dictionary di on d.Model=di.DataValue
where (g.UserID = @UserID or d.UserID=@UserID) and d.Deleted !=1 order by g.GroupID desc, d.DeviceName collate Chinese_PRC_CS_AS_KS_WS asc, StopTime, [status],OfflineTime desc ";
using (System.Data.IDbConnection conn = new SqlConnection(connectionString))
{
var list = conn.QueryAsync <Models.Entity.Monitor.DevicesList>(strSql, new { UserID = dl.userid }).Result.ToList();
list.ForEach((item) =>
{
if (Mgoo.CarRent.Position.ZCChinaLocation.InOutChina(item.Lat, item.Lng))
{
Position.Point point = Position.PositionUtil.gps84_To_Gcj02(item.Lat, item.Lng);
item.Lng = point.Lng;
item.Lat = point.Lat;
}
});
hr.result = list;
hr.message = $"一共查询到{list.Count}条数据.";
}
return hr;
}));
}
public Task <IApiResult> GetDevice(P_Devices.PDevicesByDeviceID arg)
{
return(Task.Run(() =>
{
IApiResult hr = new IApiResult();
try
{
using (CarRentEntities cre = new CarRentEntities())
{
Func <double, double, bool> inoutChina = (lat, lng) => Mgoo.CarRent.Position.ZCChinaLocation.InOutChina(Convert.ToDouble(lat), Convert.ToDouble(lng));
var devs = from d in cre.Devices
join l in cre.LKLocation
on d.DeviceID equals l.DeviceID
into dl
from dli in dl.DefaultIfEmpty()
where d.DeviceID == arg.deviceid && d.Deleted == false
// let po = new Position.Point(Convert.ToDouble(dli.OLat ?? -1.00m), Convert.ToDouble(dli.OLng ?? -1.00m))
//let isChina = inoutChina(po.Lat, po.Lng)
//let point = isChina ? new Position.Point(po.Lat, po.Lng) : Position.PositionUtil.gps84_To_Gcj02(po.Lat, po.Lng)
select new Models.Return.R_Devices.GetDevice_Result
{
DeviceID = d.DeviceID,
DeviceName = d.DeviceName,
SerialNumber = d.SerialNumber,
Status = d.Status,
DataContext = dli.DataContext ?? "",
Lat = dli.OLat ?? -1.00m,
Lng = dli.OLng ?? -1.00m,
Speed = dli.Speed ?? default(decimal),
LastCommunication = dli.LastCommunication == null ? default(DateTime) : dli.LastCommunication,
Course = dli.Course ?? default(decimal),
IsStop = dli.IsStop ?? default(int)
};
var list = devs.ToList();
list.ForEach((item) =>
{
//如果是在中国大陆则需要转换一下坐标
if (Position.ZCChinaLocation.InOutChina(Convert.ToDouble(item.Lat), Convert.ToDouble(item.Lng)))
{
Position.Point point = Position.PositionUtil.gps84_To_Gcj02(Convert.ToDouble(item.Lat), Convert.ToDouble(item.Lng));
item.Lng = Convert.ToDecimal(point.Lng);
item.Lat = Convert.ToDecimal(point.Lat);
}
});
hr.result = list;
hr.message = $"查询到{list.Count}条数据.";
}
}
catch (Exception ex)
{
hr.message = ex.Message;
hr.code = StatusCode.error;
Log.Error(this, ex);
}
return hr;
}));
}
public async Task <IApiResult> GetList(Models.Parameter.P_Users.P_UsersByUserID user)
{
return(await Task.Run(() =>
{
IApiResult hr = new IApiResult();
string strSql = @"select d.DeviceID,d.SerialNumber, case when DeviceName='' then d.SerialNumber else DeviceName end DeviceName,d.Status,l.LastCommunication ,l.DataContext,l.Speed,d.GroupID,d.HireExpireDate,datediff(MI,StopStartUtcDate,serverutcdate) StopTime,
(select COUNT(-1) from ExceptionMessage e where e.Deleted=0 and e.DeviceID=d.DeviceID)ExceptionCount,l.OLat Lat,l.OLng Lng,di.DataText as Model,case when di.AccountID=2 then 7 else di.SortOrder end as OffLineMinute,l.IsStop
from devices d
left join lklocation l on l.DeviceID = d.DeviceID inner join Dictionary di on di.DataValue=d.Model
where d.UserID = @UserID and d.deleted = 0
group by d.DeviceID,d.SerialNumber,d.DeviceName,l.DataContext,l.Speed ,d.GroupID,l.LastCommunication,d.HireExpireDate,StopStartUtcDate,serverutcdate,l.OLat,l.OLng,di.DataText,d.ServerID2,di.SortOrder,di.AccountID,d.Status,l.IsStop
order by d.DeviceName collate Chinese_PRC_CS_AS_KS_WS asc";
try
{
using (System.Data.IDbConnection conn = new SqlConnection(connectionString))
{
var list = conn.QueryAsync <Models.Entity.Monitor.DeviceList>(strSql, new { UserID = user.userid }).Result.ToList();
list.ForEach(item =>
{
try
{
string[] ContextList = (item.DataContext ?? "0-0-0-0").Split('-');
if (ContextList.Length == 1)
{
item.DataContext = "0-0-0-0-" + item.DataContext;
ContextList = item.DataContext.Split('-');
}
if (Mgoo.CarRent.Position.ZCChinaLocation.InOutChina(item.Lat, item.Lng))
{
Position.Point point = Position.PositionUtil.gps84_To_Gcj02(item.Lat, item.Lng);
item.Lng = point.Lng;
item.Lat = point.Lat;
}
item.GpsStatus = GetDevicesStatus(item.LastCommunication, item.HireExpireDate, item.OffLineMinute ?? 20);
int statusmin = 0;
if (item.GpsStatus == 2)
{
statusmin = item.LastCommunication == default(DateTime) ? 0 : (DateTime.Now - item.LastCommunication).TotalMinutes.ToString("0").toInt();
}
else if (item.GpsStatus == 4)
{
statusmin = (DateTime.Now - item.HireExpireDate).TotalMinutes.ToString("0").toInt();
}
else if (item.GpsStatus == 1 && item.IsStop == 0)
{
statusmin = item.StopTime ?? 0;
}
item.OffLineMinute = null;
item.GpsStatusMinute = statusmin;
item.StopTime = null;
}
catch (Exception ex)
{
Log.Error(this, ex);
}
});
hr.result = list;
hr.message = $"查询到{list.Count}条数据.";
}
}
catch (Exception ex)
{
Log.Error(this, ex);
hr.message = ex.Message;
hr.code = StatusCode.error;
}
return hr;
}));
}
