/// <summary>
/// 获取单个设备信息
/// </summary>
/// <param name="deviceSerial"></param>
public void GetEzvizDevice(string deviceSerial)
{
var postData = "accessToken=" + _ezvizAccessToken + "&deviceSerial=" + deviceSerial;
var device = ToHttp.ToPost <Device>("https://open.ys7.com/api/lapp/device/info", postData);
LogHelper.WriteLog(typeof(string), JsonConvert.SerializeObject(device));
if (device.code == "200")
{
string status;
if (device.data.status == 0)
{
status = "离线";
}
else if (device.data.status == 1)
{
status = "在线";
}
else
{
status = "未知";
}
if (!_dbContext2.XlProject.Any(x => x.XlShebeiId == device.data.deviceSerial && x.IsDeleted == 0))
{
XlProject xlProject = new XlProject();
xlProject.AddPeople = "狗";
xlProject.AddTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
xlProject.XlProjectUuid = Guid.NewGuid();
xlProject.XlShebeiId = device.data.deviceSerial;
xlProject.XlShebeiType = "五水共治污水监控";
xlProject.IsDeleted = 0;
xlProject.ShebeiAddress = device.data.deviceName;
xlProject.ShebeiType = status;
xlProject.UrlType = 3;
_dbContext2.XlProject.Add(xlProject);
LogHelper.WriteLog(typeof(string), "添加" + device.data.deviceName);
}
else
{
var xlp = _dbContext2.XlProject.FirstOrDefault(x => x.XlShebeiId == device.data.deviceSerial && x.IsDeleted == 0);
xlp.ShebeiAddress = device.data.deviceName;
xlp.ShebeiType = status;
xlp.UrlType = 3;
LogHelper.WriteLog(typeof(string), "修改" + device.data.deviceName);
}
_dbContext2.SaveChanges();
}
else if (device.code == "10002")
{
LogHelper.WriteLog(typeof(string), "token失效");
GetEzvizAccessToken();
GetEzvizDevice(deviceSerial);
}
else
{
LogHelper.WriteLog(typeof(string), device.code + "|" + device.msg);
}
}
public IActionResult XlProjectImport(IFormFile excelfile)
{
var response = ResponseModelFactory.CreateInstance;
using (_dbContext)
{
DateTime beginTime = DateTime.Now;
string sWebRootFolder = _hostingEnvironment.WebRootPath + "\\UploadFiles\\ImportSocialGovernExcel";
//var schoolinfo = _dbContext.SchoolInforManagement.AsQueryable();
string uploadtitle = "雪亮工程信息导入" + DateTime.Now.ToString("yyyyMMddHHmmss");
string sFileName = $"{uploadtitle}.xlsx";
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
//string conStr = ConnectionStrings.DefaultConnection;
string responsemsgsuccess = "";
string responsemsgrepeat = "";
string responsemsgdefault = "";
int successcount = 0;
int repeatcount = 0;
int defaultcount = 0;
//string today = DateTime.Now.ToString("yyyy-MM-dd");
try
{
//把excelfile中的数据复制到file中
using (FileStream fs = new FileStream(file.ToString(), FileMode.Create)) //初始化一个指定路径和创建模式的FileStream
{
excelfile.CopyTo(fs);
fs.Flush(); //清空stream的缓存，并且把缓存中的数据输出到file
}
DataTable dt = Haikan3.Utils.ExcelTools.ExcelToDataTable(file.ToString(), "Sheet1", true);
if (dt == null || dt.Rows.Count == 0)
{
response.SetFailed("表格无数据");
return(Ok(response));
}
else
{
if (!dt.Columns.Contains("设备ID"))
{
response.SetFailed("无‘设备ID’列");
return(Ok(response));
}
if (!dt.Columns.Contains("设备类型"))
{
response.SetFailed("无‘设备类型’列");
return(Ok(response));
}
if (!dt.Columns.Contains("位置"))
{
response.SetFailed("无‘位置’列");
return(Ok(response));
}
if (!dt.Columns.Contains("经度"))
{
response.SetFailed("无‘经度’列");
return(Ok(response));
}
if (!dt.Columns.Contains("纬度"))
{
response.SetFailed("无‘纬度’列");
return(Ok(response));
}
if (!dt.Columns.Contains("管理人信息"))
{
response.SetFailed("无‘管理人信息’列");
return(Ok(response));
}
if (!dt.Columns.Contains("设备状态"))
{
response.SetFailed("无‘设备状态’列");
return(Ok(response));
}
for (int i = 0; i < dt.Rows.Count; i++)
{
var entity = new XlProject();
entity.XlProjectUuid = Guid.NewGuid();
if (!string.IsNullOrEmpty(dt.Rows[i]["设备ID"].ToString()))
{
entity.XlShebeiId = dt.Rows[i]["设备ID"].ToString();
}
else
{
responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行设备ID为空" + "</p></br>";
defaultcount++;
continue;
}
if (!string.IsNullOrEmpty(dt.Rows[i]["设备类型"].ToString()))
{
entity.XlShebeiType = dt.Rows[i]["设备类型"].ToString();
}
else
{
responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行设备类型为空" + "</p></br>";
defaultcount++;
continue;
}
if (!string.IsNullOrEmpty(dt.Rows[i]["位置"].ToString()))
{
entity.ShebeiAddress = dt.Rows[i]["位置"].ToString();
}
else
{
responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行位置为空" + "</p></br>";
defaultcount++;
continue;
}
if (!string.IsNullOrEmpty(dt.Rows[i]["经度"].ToString()))
{
entity.Lon = dt.Rows[i]["经度"].ToString();
}
else
{
responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行经度为空" + "</p></br>";
defaultcount++;
continue;
}
if (!string.IsNullOrEmpty(dt.Rows[i]["纬度"].ToString()))
{
entity.Lat = dt.Rows[i]["纬度"].ToString();
}
else
{
responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行纬度为空" + "</p></br>";
defaultcount++;
continue;
}
if (!string.IsNullOrEmpty(dt.Rows[i]["管理人信息"].ToString()))
{
entity.AdminInfo = dt.Rows[i]["管理人信息"].ToString();
}
else
{
responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行管理人信息为空" + "</p></br>";
defaultcount++;
continue;
}
if (!string.IsNullOrEmpty(dt.Rows[i]["设备状态"].ToString()))
{
entity.ShebeiType = dt.Rows[i]["设备状态"].ToString();
}
else
{
responsemsgdefault += "<p style='color:red'>" + "第" + (i + 2) + "行设备状态为空" + "</p></br>";
defaultcount++;
continue;
}
entity.IsDeleted = 0;
entity.AddTime = DateTime.Now.ToString("yyyy-MM-dd");
entity.AddPeople = AuthContextService.CurrentUser.DisplayName;
_dbContext.XlProject.Add(entity);
_dbContext.SaveChanges();
successcount++;
}
}
responsemsgsuccess = "<p style='color:green'>导入成功:" + successcount + "条</p></br>" + responsemsgsuccess;
responsemsgrepeat = "<p style='color:orange'>重复需手动修改数据:" + repeatcount + "条</p></br>" + responsemsgrepeat;
responsemsgdefault = "<p style='color:red'>导入失败:" + defaultcount + "条</p></br>" + responsemsgdefault;
ToLog.AddLog("导入", "成功:导入:雪亮工程信息数据", _dbContext);
//DateTime endTime = DateTime.Now;
//TimeSpan useTime = endTime - beginTime;
//string taketime = "导入时间" + useTime.TotalSeconds.ToString() + "秒 ";
response.SetData(JsonConvert.DeserializeObject(JsonConvert.SerializeObject(new
{
//time = taketime,
successmsg = responsemsgsuccess
,
repeatmsg = responsemsgrepeat,
defaultmsg = responsemsgdefault
})));
return(Ok(response));
}
catch (Exception ex)
{
response.SetFailed(ex.Message);
return(Ok(response));
}
}
}
/// <summary>
/// 全部监控的同步
/// </summary>
public void Monitoring2()
{
//_dbContext1.Dispose();
//_dbContext1 = new HaikanHeQiaoContext();
cameraList = new List <HaikanSmartTownCockpit.WatchDog.Camera.List>();
GetCameras(1);
for (int i = 0; i < cameraList.Count; i++)
{
var cid = cameraList[i].indexCode;
if (Queryable.Any <XlProject>(_dbContext1.XlProject, x => x.XlShebeiId == cid && x.IsDeleted == 0))
{
LogHelper.WriteLog(typeof(string), "监控信息修改:" + cid);
//Console.WriteLine("监控信息修改:" + cid);
var entity = Queryable.FirstOrDefault <XlProject>(_dbContext1.XlProject, x => x.XlShebeiId == cid && x.IsDeleted == 0);
entity.ShebeiAddress = cameraList[i].name;
entity.XlShebeiType = cameraList[i].regionName;
//entity.Lat = cameraList[i].latitude;
//entity.Lon = cameraList[i].longitude;
entity.ShebeiType = GetOnlineState(cid);
entity.UrlType = 1;
}
else
{
LogHelper.WriteLog(typeof(string), "监控信息添加:" + cid);
//Console.WriteLine("监控信息添加:" + cid);
XlProject project = new XlProject()
{
XlProjectUuid = Guid.NewGuid(),
XlShebeiId = cid,
XlShebeiType = cameraList[i].regionName,
ShebeiAddress = cameraList[i].name,
Lon = cameraList[i].longitude,
Lat = cameraList[i].latitude,
IsDeleted = 0,
ShebeiType = GetOnlineState(cid),
AddPeople = "狗",
AddTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
UrlType = 1,
};
_dbContext1.XlProject.Add(project);
LogHelper.WriteLog(typeof(string), "添加" + JsonConvert.SerializeObject(project));
//Console.WriteLine("添加" + JsonConvert.SerializeObject(project));
}
var num = _dbContext1.SaveChanges();
Console.WriteLine("改变：" + num);
if (cameraList[i].regionName == "垃圾分类监控")
{
if (Queryable.Any <Ygiene>(_dbContext1.Ygiene, x => x.YgieneMonitorId == cid && x.IsDeleted == 0))
{
LogHelper.WriteLog(typeof(string), "case9修改:" + cid);
//Console.WriteLine("case9修改:" + cid);
var entity = Queryable.FirstOrDefault <Ygiene>(_dbContext1.Ygiene, x => x.YgieneMonitorId == cid && x.IsDeleted == 0);
entity.YgieneType = cameraList[i].regionName;
entity.YgieneAddress = cameraList[i].name;
//entity.Lat = cameraList[i].latitude;
//entity.Lon = cameraList[i].longitude;
entity.YgieneStaues = GetOnlineState(cid);
entity.UrlType = 1;
}
else
{
LogHelper.WriteLog(typeof(string), "case9添加:" + cid);
//Console.WriteLine("case9添加:" + cid);
Ygiene ygiene = new Ygiene()
{
YgieneUuid = Guid.NewGuid(),
YgieneMonitorId = cid,
YgieneAddress = cameraList[i].name,
Lon = cameraList[i].longitude,
Lat = cameraList[i].latitude,
AddPeople = "狗",
AddTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
YgieneStaues = GetOnlineState(cid),
YgieneType = cameraList[i].regionName,
IsDeleted = 0,
UrlType = 1,
};
_dbContext1.Ygiene.Add(ygiene);
LogHelper.WriteLog(typeof(string), "添加卫生点" + JsonConvert.SerializeObject(ygiene));
//Console.WriteLine("添加卫生点" + JsonConvert.SerializeObject(ygiene));
}
_dbContext1.SaveChanges();
}
}
cameraList = new List <HaikanSmartTownCockpit.WatchDog.Camera.List>();
//GC.Collect();
}
/// <summary>
/// 同步所有的监控(雪亮工程),从程序运行目录下的vinfo.xlsx文件读取指定的监控
/// </summary>
public void Monitoring2()
{
jiankong = new List <Model3.List>();
//LogHelper.WriteLog(typeof(string), "vinfo文件路径" + Application.ExecutablePath.ToString() + "/vinfo.xlsx");
//var file = Application.ExecutablePath.ToString()+ "/vinfo.xlsx";
var file = "D://vinfo.xlsx";
DataTable dt = ExselData.ExcelToDataTable(file.ToString(), "Sheet1", true);
//获取海康平台上所有的监控
Jiankong();
for (int i = 0; i < dt.Rows.Count; i++)
{
LogHelper.WriteLog(typeof(string), "进入循环了code:" + dt.Rows[i]["name"]);
var jkzy = jiankong.Find(x => x.name == dt.Rows[i]["name"].ToString());
LogHelper.WriteLog(typeof(string), "jkzy的值" + jkzy);
var cid = dt.Rows[i]["name"].ToString();
var entity = _dbContext.XlProject.Any(x => x.ShebeiAddress == cid && x.IsDeleted == 0);
LogHelper.WriteLog(typeof(string), "entity的值:" + entity);
LogHelper.WriteLog(typeof(string), "开始判断是否有了此数据");
// 添加
if (jkzy != null && entity == false)
{
LogHelper.WriteLog(typeof(string), "开始录入");
XlProject xlProject = new XlProject();
xlProject.XlProjectUuid = Guid.NewGuid();
xlProject.XlShebeiType = dt.Rows[i]["type"].ToString();
xlProject.Lat = jkzy.latitude;
xlProject.Lon = jkzy.longitude;
xlProject.ShebeiAddress = jkzy.name;
xlProject.AddPeople = "狗";
xlProject.IsDeleted = 0;
xlProject.AddTime = DateTime.Now.ToString();
xlProject.XlShebeiId = jkzy.cameraIndexCode;
xlProject.ShebeiType = GetJianKState(jkzy.cameraIndexCode);
xlProject.UrlType = 2;
_dbContext.XlProject.Add(xlProject);
_dbContext.SaveChanges();
LogHelper.WriteLog(typeof(string), "录入一条code:" + jkzy.cameraIndexCode);
}
else if (jkzy != null && entity) // 修改
{
var xlp = _dbContext.XlProject.FirstOrDefault(x => x.ShebeiAddress == cid && x.IsDeleted == 0);
xlp.XlShebeiId = jkzy.cameraIndexCode;
xlp.Lat = jkzy.latitude;
xlp.Lon = jkzy.longitude;
xlp.ShebeiType = GetJianKState(jkzy.cameraIndexCode);
_dbContext.SaveChanges();
LogHelper.WriteLog(typeof(string), "修改:" + jkzy.cameraIndexCode);
}
else
{
LogHelper.WriteLog(typeof(string), "没有录入");
}
}
jiankong = new List <Model3.List>();
}
