using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LeaRun.Application.Entity.SecondaryWaterSupply;
using LeaRun.Application.IService.SecondaryWaterSupply;
using LeaRun.Data;
using System.Data;
using LeaRun.Util.WebControl;
using LeaRun.Util;
namespace LeaRun.Application.Service.SecondaryWaterSupply
{
public class DeviceService : IDeviceService
{
SqlHelper sqlHelper = new SqlHelper("SecondyWaterSupplyDb");//二次洪水
///
/// 保存设备信息
///
///
///
public bool SaveDevice(DeviceEntity device)
{
try
{
string insertSql = "INSERT INTO SecondaryWaterSupplyBase(PumpHouseName,ManufacturerCode,PumpingStationNumber,longitude,latitude)VALUES('"+device.PumpHouseName+"', '"+
device.ManufacturerCode+"', '"+device.PumpingStationNumber+"', '"+device.Longitude+"', '"+ device.Latitude+"')";
int result = sqlHelper.ExecuteNoParams(insertSql, CommandType.Text);
return result > 0;
}
catch (Exception ex)
{
return false;
}
}
///
/// 获取设备列表
///
/// 分页参数
/// 查询关键字
///
public IEnumerable GetDeviceList(Pagination pagination , string keyWord)
{
try
{
string selSql = "SELECT * FROM SecondaryWaterSupplyBase WHERE DeleteMark=0";
int total = pagination.records;
DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, "SecondaryWaterSupplyId", true, pagination.rows, pagination.page, out total, null);
pagination.records = total;
return DataHelper.DataTableToT(dtResult);
}
catch (Exception ex)
{
return null;
}
}
///
/// 获取所有设备列表
///
///
public IEnumerable GetAllDevice()
{
try
{
string selSql = "SELECT * FROM SecondaryWaterSupplyBase WHERE DeleteMark=0";
DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
return DataHelper.DataTableToT(dtResult);
}
catch (Exception ex)
{
return null;
}
}
///
/// 更新设备信息
///
///
///
public bool UpdateDevice(DeviceEntity device)
{
try
{
string updataSql = "UPDATE SecondaryWaterSupplyBase SET PumpHouseName = '" + device.PumpHouseName + "', ManufacturerCode ='" + device.ManufacturerCode
+ "', PumpingStationNumber ='" + device.PumpingStationNumber + "', longitude ='" + device.Longitude + "', latitude ='"+ device.Latitude
+ "' WHERE SecondaryWaterSupplyId= " + device.SecondaryWaterSupplyId;
int result = sqlHelper.ExecuteNoParams(updataSql, CommandType.Text);
return result > 0;
}
catch (Exception ex)
{
return false;
}
}
///
/// 获取单一表设备信息
///
///
///
public DeviceEntity GetDeviceById(string SecondaryWaterSupplyId)
{
try
{
string selSql = "SELECT * FROM SecondaryWaterSupplyBase WHERE SecondaryWaterSupplyId= " + SecondaryWaterSupplyId;
DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
List lists = DataHelper.DataTableToT(dtResult);
return lists[0];
}
catch (Exception ex)
{
return null;
}
}
///
/// 删除设备信息
///
///
///
public bool DeleteDevice(string SecondaryWaterSupplyId)
{
try
{
string delSql = "UPDATE SecondaryWaterSupplyBase SET DeleteMark=1 WHERE SecondaryWaterSupplyId= " + SecondaryWaterSupplyId;
int result = sqlHelper.ExecuteNoParams(delSql, CommandType.Text);
return result > 0;
}
catch (Exception ex)
{
return false;
}
}
///
/// 获取所有设备最新信息
///
///
public IEnumerable GetAllDeviceData()
{
try
{
string selSql = "SELECT * FROM dbo.SecondaryWaterSupplyBase WHERE DeleteMark=0 ";
DataTable dtDevice = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
StringBuilder hisSql = new StringBuilder();
for (int row = 0; row < dtDevice.Rows.Count; row++)
{
string tabName = dtDevice.Rows[row]["TableName"].ToString();
string deviceName = dtDevice.Rows[row]["PumpHouseName"].ToString();
hisSql.Append("SELECT TOP 1 '" + deviceName + "' as PumpHouseName , * FROM " + tabName + " ORDER BY GetDateTime DESC");
if (row < dtDevice.Rows.Count - 1)
{
hisSql.Append("UNION");
}
}
DataTable dtHis = sqlHelper.ExecuteDataTable(hisSql.ToString(),CommandType.Text, null);
return DataHelper.DataTableToT(dtHis);
}
catch(Exception ex)
{
return null;
}
}
///
/// 获取设备实时数据
///
///
///
///
public IEnumerable GetAllDeviceRealTimeData(string ids, Pagination pagination)
{
try
{
string selSql = "SELECT * FROM dbo.SecondaryWaterSupplyBase WHERE SecondaryWaterSupplyId IN (" + ids + ")";
DataTable dtDevice = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
StringBuilder hisSql = new StringBuilder();
for (int row = 0; row < dtDevice.Rows.Count; row++)
{
string tabName = dtDevice.Rows[row]["TableName"].ToString();
string deviceName = dtDevice.Rows[row]["PumpHouseName"].ToString();
hisSql.Append("SELECT TOP 1 '" + deviceName + "' as PumpHouseName , * FROM " + tabName + " ORDER BY GetDateTime DESC");
if (row < dtDevice.Rows.Count - 1)
{
hisSql.Append("UNION");
}
}
int total = pagination.records;
DataTable dtHis = sqlHelper.ExecuteDataTable(hisSql.ToString(), "SecondaryWaterSupplyId", true, pagination.rows, pagination.page, out total, null);
pagination.records = total;
return DataHelper.DataTableToT(dtHis);
}
catch (Exception ex)
{
return null;
}
}
}
}