using IStation.Model;
|
using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using System;
|
using System.Collections.Generic;
|
using System.IO;
|
using System.Linq;
|
|
namespace IStation.BLL
|
{
|
/// <summary>
|
/// 导出 WaterDesk 所需数据
|
/// </summary>
|
public partial class ExportWaterDeskDataHelper
|
{
|
public class ExportInfo
|
{
|
public string Tag { get; set; }
|
public int Index { get; set; }
|
public List<SignalRecord> SignalRecords { get; set; }
|
}
|
|
public static bool ExportLast()
|
{
|
var ms = new BLL.MonitorDataSources().GetAll().Last();
|
return Export(ms.ID);
|
}
|
|
public static bool Export(long monitorDataSourcesId)
|
{
|
var bllSummary = new BLL.MonitorDataSetSummary();
|
var yearMonths = bllSummary.GetYearMonth(monitorDataSourcesId);
|
if (yearMonths == null || !yearMonths.Any())
|
return false;
|
|
var bllEquipment = new BLL.Equipment();
|
var enginePumps = bllEquipment.GetAll();
|
if (enginePumps == null || !enginePumps.Any())
|
return false;
|
|
|
var bllMonitorPoint = new BLL.MonitorPoint();
|
var bllEquipmentMonitorMapping = new BLL.EquipmentMonitorMapping();
|
var bllMonitorDataSet = new BLL.MonitorDataSet();
|
|
|
var monitorPointExSignalWithSignalTypes = new BLL.MonitorPoint().GetAllExSignalWithSignalType();
|
monitorPointExSignalWithSignalTypes = monitorPointExSignalWithSignalTypes.Where(x => !string.IsNullOrEmpty(x.TagName)).ToList();
|
if (monitorPointExSignalWithSignalTypes == null || !monitorPointExSignalWithSignalTypes.Any())
|
return false;
|
|
|
|
|
var wls = monitorPointExSignalWithSignalTypes.Where(x => x.SignalType == IStation.SignalType.水位).ToList();
|
var wls_ids = wls.Select(x => x.SignalID).ToList();
|
|
var qs = monitorPointExSignalWithSignalTypes.Where(x => x.SignalType == IStation.SignalType.瞬时流量).ToList();
|
var qs_ids = qs.Select(x => x.SignalID).ToList();
|
|
var ps = monitorPointExSignalWithSignalTypes.Where(x => x.SignalType == IStation.SignalType.压力).ToList();
|
var ps_ids = ps.Select(x => x.SignalID).ToList();
|
|
var rs = monitorPointExSignalWithSignalTypes.Where(x => x.SignalType == IStation.SignalType.运行状态).ToList();
|
var rs_ids = rs.Select(x => x.SignalID).ToList();
|
|
//var hzs = monitorPointExSignalWithSignalTypes.Where(x => x.SignalType == IStation.SignalType.频率).ToList();
|
//var hzs_id_list = hzs.Select(x => x.SignalID).ToList();
|
|
var speeds = monitorPointExSignalWithSignalTypes.Where(x => x.SignalType == IStation.SignalType.转速).ToList();
|
//var speeds_ids = speeds.Where(x=> x.Importance==0).Select(x => x.SignalID).ToList();
|
var speeds_ids = speeds.Select(x => x.SignalID).ToList();
|
|
var hzs_ids = new List<long>();
|
hzs_ids.AddRange(rs_ids);
|
// hzs_ids.AddRange(hzs_id_list);
|
hzs_ids.AddRange(speeds_ids);
|
|
Dictionary<long, double> temp = new Dictionary<long, double>();
|
|
foreach (var enginePump in enginePumps)
|
{
|
var equipmentMonitorMappings = bllEquipmentMonitorMapping.GetByEquipmentID(enginePump.ID);
|
if (equipmentMonitorMappings == null || !equipmentMonitorMappings.Any())
|
continue;
|
var pump = bllEquipment.GetChildPumpByEnginePumpID(enginePump.ID);
|
foreach (var item in speeds)
|
{
|
if (equipmentMonitorMappings.Exists(x => x.MonitorPointID == item.MonitorPointID))
|
{
|
temp.Add(item.SignalID, pump.RatedParas.Nr);
|
}
|
}
|
}
|
|
var root_path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "WaterDeskData");
|
foreach (var yearMonth in yearMonths)
|
{
|
var year = yearMonth.Year;
|
var month = yearMonth.Month;
|
var y_m_path = Path.Combine(root_path, $"{year}年{month}月");
|
if (!Directory.Exists(y_m_path))
|
{
|
Directory.CreateDirectory(y_m_path);
|
}
|
|
var wls_packets = bllMonitorDataSet.GetSignalRecordPacket(monitorDataSourcesId, wls_ids, year, month);
|
if (wls_packets != null && wls_packets.Any())
|
{
|
var infos = new List<ExportInfo>();
|
for (int i = 0; i < wls.Count; i++)
|
{
|
var mp = wls[i];
|
var info = new ExportInfo();
|
info.Tag = mp.TagName;
|
info.Index = i;
|
info.SignalRecords = wls_packets.Where(x => x.SignalID == mp.SignalID).SelectMany(x => x.RecordList).ToList();
|
infos.Add(info);
|
}
|
|
var path = Path.Combine(y_m_path, "水位-时间模式.xls");
|
Export2Excel(path, infos);
|
}
|
|
var qs_packets = bllMonitorDataSet.GetSignalRecordPacket(monitorDataSourcesId, qs_ids, year, month);
|
if (qs_packets != null && qs_packets.Any())
|
{
|
var infos = new List<ExportInfo>();
|
for (int i = 0; i < qs.Count; i++)
|
{
|
var mp = qs[i];
|
var info = new ExportInfo();
|
info.Tag = mp.TagName;
|
info.Index = i;
|
info.SignalRecords = qs_packets.Where(x => x.SignalID == mp.SignalID).SelectMany(x => x.RecordList).ToList();
|
infos.Add(info);
|
}
|
|
var path = Path.Combine(y_m_path, "流量-时间模式.xls");
|
Export2Excel(path, infos);
|
|
var path_csv = Path.Combine(y_m_path, "流量-Scada.csv");
|
ExportCSV(path_csv, infos);
|
}
|
|
|
|
var hzs_packets = bllMonitorDataSet.GetSignalRecordPacket(monitorDataSourcesId, hzs_ids, year, month);
|
if (hzs_packets != null && hzs_packets.Any())
|
{
|
var infos = new List<ExportInfo>();
|
for (int i = 0; i < hzs_ids.Count; i++)
|
{
|
var mp = monitorPointExSignalWithSignalTypes.Find(x => x.SignalID == hzs_ids[i]);
|
var info = new ExportInfo();
|
info.Tag = mp.TagName;
|
info.Index = i;
|
if (mp.SignalType == IStation.SignalType.转速)
|
{
|
var nr = temp[mp.SignalID];
|
var old_records = hzs_packets.Where(x => x.SignalID == mp.SignalID).SelectMany(x => x.RecordList).ToList();
|
//info.SignalRecords = old_records.Select(x => new Model.SignalRecord(x.Time, x.Value / nr)).ToList();
|
var records = new List<Model.SignalRecord>();
|
foreach (var item in old_records)
|
{
|
var time = item.Time;
|
var value = item.Value / nr;
|
value = value < 0 ? 0 : value;
|
value = value > 1 ? 1 : value;
|
records.Add(new SignalRecord(time, value));
|
}
|
info.SignalRecords = records;
|
}
|
else if (mp.SignalType == IStation.SignalType.频率)
|
{
|
var old_records = hzs_packets.Where(x => x.SignalID == mp.SignalID).SelectMany(x => x.RecordList).ToList();
|
var records = new List<Model.SignalRecord>();
|
foreach (var item in old_records)
|
{
|
var time = item.Time;
|
var value = item.Value / 50;
|
value = value < 0 ? 0 : value;
|
value = value > 1 ? 1 : value;
|
records.Add(new SignalRecord(time, value));
|
}
|
|
info.SignalRecords = records;
|
}
|
else
|
{
|
info.SignalRecords = hzs_packets.Where(x => x.SignalID == mp.SignalID).SelectMany(x => x.RecordList).ToList();
|
}
|
infos.Add(info);
|
}
|
|
var path = Path.Combine(y_m_path, "转速比-时间模式.xls");
|
Export2Excel(path, infos);
|
}
|
|
var ps_packets = bllMonitorDataSet.GetSignalRecordPacket(monitorDataSourcesId, ps_ids, year, month);
|
if (ps_packets != null && ps_packets.Any())
|
{
|
var infos = new List<ExportInfo>();
|
for (int i = 0; i < ps.Count; i++)
|
{
|
var mp = ps[i];
|
|
var info = new ExportInfo();
|
info.Tag = mp.TagName;
|
info.Index = i;
|
|
var signalRecords = ps_packets.Where(x => x.SignalID == mp.SignalID).SelectMany(x => x.RecordList).ToList();
|
//if (signalRecords != null)
|
//{
|
// for (int j = 0; j < signalRecords.Count; j++)
|
// {
|
// var r = signalRecords[j];
|
// r.Value= Unit.UnitHHelper.fromMPa(Unit.eUnitH.M, r.Value);
|
// }
|
//}
|
info.SignalRecords = signalRecords?.Select(x=>new SignalRecord(x.Time, Unit.UnitHHelper.toM(Unit.eUnitH.MPa, x.Value))).ToList();
|
infos.Add(info);
|
}
|
|
var path = Path.Combine(y_m_path, "压力-Scada.csv");
|
ExportCSV(path, infos);
|
}
|
|
}
|
|
return true;
|
}
|
|
|
public static bool Export2Excel(string fileName, List<ExportInfo> infos)
|
{
|
|
//try
|
//{
|
HSSFWorkbook theBook = new HSSFWorkbook();
|
var theSheet1 = theBook.CreateSheet("Sheet1");
|
|
IRow rowTile = theSheet1.CreateRow(0);
|
foreach (ExportInfo info in infos)
|
{
|
rowTile.CreateCell(info.Index).SetCellValue(info.Tag);
|
}
|
|
|
foreach (var item in infos)
|
{
|
for (int i = 1; i < item.SignalRecords.Count; i++)
|
{
|
var record = item.SignalRecords[i];
|
|
IRow row;
|
if (theSheet1.LastRowNum < i)
|
{
|
row = theSheet1.CreateRow(i);
|
}
|
else
|
{
|
row = theSheet1.GetRow(i);
|
}
|
|
|
|
row.CreateCell(item.Index).SetCellValue(record.Value == IStation.Error.Default ? 0 : record.Value);
|
}
|
}
|
|
//强制Excel在打开时重新计算所有公式
|
theSheet1.ForceFormulaRecalculation = true;
|
using (FileStream fs = File.OpenWrite(fileName))
|
{
|
theBook.Write(fs);
|
}
|
//}
|
//catch (Exception ex)
|
//{
|
|
// XtraMessageBox.Show(ex.Message);
|
//}
|
|
return true;
|
}
|
|
public static void ExportCSV(string fileName, List<ExportInfo> infos)
|
{
|
using (var fs = new FileStream(fileName, System.IO.FileMode.Create, System.IO.FileAccess.Write, FileShare.ReadWrite))
|
using (var sw = new StreamWriter(fs, System.Text.Encoding.UTF8))
|
{
|
sw.WriteLine("ScadaID,Time,Value");
|
foreach (var item in infos)
|
{
|
for (int i = 0; i < item.SignalRecords.Count; i++)
|
{
|
var r = item.SignalRecords[i];
|
var v = r.Value == IStation.Error.Default ? 0 : r.Value;
|
sw.WriteLine($"{item.Tag},{r.Time:yyyy-MM-dd HH:mm:ss},{v}");
|
}
|
}
|
}
|
}
|
|
}
|
}
|
