123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220 |
- 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");//二次洪水
-
- /// <summary>
- /// 保存设备信息
- /// </summary>
- /// <param name="device"></param>
- /// <returns></returns>
- 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;
- }
- }
- /// <summary>
- /// 获取设备列表
- /// </summary>
- /// <param name="pagination">分页参数</param>
- /// <param name="keyWord">查询关键字</param>
- /// <returns></returns>
- public IEnumerable<DeviceEntity> 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<DeviceEntity>(dtResult);
- }
- catch (Exception ex)
- {
- return null;
- }
- }
- /// <summary>
- /// 获取所有设备列表
- /// </summary>
- /// <returns></returns>
- public IEnumerable<DeviceEntity> GetAllDevice()
- {
- try
- {
- string selSql = "SELECT * FROM SecondaryWaterSupplyBase WHERE DeleteMark=0";
- DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
- return DataHelper.DataTableToT<DeviceEntity>(dtResult);
- }
- catch (Exception ex)
- {
- return null;
- }
- }
- /// <summary>
- /// 更新设备信息
- /// </summary>
- /// <param name="device"></param>
- /// <returns></returns>
- 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;
- }
- }
- /// <summary>
- /// 获取单一表设备信息
- /// </summary>
- /// <param name="SecondaryWaterSupplyId"></param>
- /// <returns></returns>
- public DeviceEntity GetDeviceById(string SecondaryWaterSupplyId)
- {
- try
- {
- string selSql = "SELECT * FROM SecondaryWaterSupplyBase WHERE SecondaryWaterSupplyId= " + SecondaryWaterSupplyId;
- DataTable dtResult = sqlHelper.ExecuteDataTable(selSql, CommandType.Text, null);
- List<DeviceEntity> lists = DataHelper.DataTableToT<DeviceEntity>(dtResult);
- return lists[0];
- }
- catch (Exception ex)
- {
- return null;
- }
- }
- /// <summary>
- /// 删除设备信息
- /// </summary>
- /// <param name="SecondaryWaterSupplyId"></param>
- /// <returns></returns>
- 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;
- }
-
- }
- /// <summary>
- /// 获取所有设备最新信息
- /// </summary>
- /// <returns></returns>
- public IEnumerable<DeviceEntity> 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<DeviceEntity>(dtHis);
- }
- catch(Exception ex)
- {
- return null;
- }
- }
- /// <summary>
- /// 获取设备实时数据
- /// </summary>
- /// <param name="ids"></param>
- /// <param name="pagination"></param>
- /// <returns></returns>
- public IEnumerable<DeviceEntity> 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<DeviceEntity>(dtHis);
- }
- catch (Exception ex)
- {
- return null;
- }
- }
- }
- }
|