/// <summary>
/// 测斜数据
/// </summary>
/// <param name="groupId">传感器组编号（只能是数字）</param>
/// <param name="startdate">开始时间（ISO时间）</param>
/// <param name="enddate">结束时间（ISO时间）</param>
/// <returns>深部位移数据</returns>
public List <CxData> GetByGroupDirectAndDateGroupByTime(int groupId, DateTime startdate, DateTime enddate)
{
using (var entities = new DW_iSecureCloud_EmptyEntities())
{
var query = from d in entities.T_THEMES_DEFORMATION_DEEP_DISPLACEMENT_DAILY
from sg in entities.T_DIM_SENSOR_GROUP_CEXIE
where d.SENSOR_ID == sg.SENSOR_ID &&
sg.GROUP_ID == groupId &&
d.ACQUISITION_DATETIME >= startdate && d.ACQUISITION_DATETIME <= enddate
select new
{
depth = sg.DEPTH,
xvalue = d.DEEP_CUMULATIVEDISPLACEMENT_X_VALUE,
yvalue = d.DEEP_CUMULATIVEDISPLACEMENT_Y_VALUE,
acquistiontime = d.ACQUISITION_DATETIME
};
var list = query.ToList();
return
(list.GroupBy(d => d.acquistiontime)
.OrderBy(d => d.Key)
.Select(
d =>
new CxData
{
DateTime = d.Key,
Data =
d.Select(
g =>
new CxInernalData
{
Depth = g.depth,
XValue = g.xvalue,
YValue = g.yvalue
}).ToList()
})
.ToList());
}
}
public void TestSaveAndDeleteReportInfo()
{
ReportGroup reportGroup = TestReportCreate.GetMonthReportGroup();
ReportFileBase file = new MonitorReportFile(reportGroup);
Assert.IsTrue(ReportConfigDal.SaveReportInfo(file.ReportInfo));
int fileRec;
using (var db = new DW_iSecureCloud_EmptyEntities())
{
fileRec = db.T_REPORT_COLLECTION.Where(r => r.Name == file.ReportInfo.Name).ToList().Count;
}
Assert.IsTrue(fileRec > 0);
Assert.IsTrue(ReportConfigDal.DeleteOldReportInfo(file.ReportInfo.Name));
using (var db = new DW_iSecureCloud_EmptyEntities())
{
fileRec = db.T_REPORT_COLLECTION.Where(r => r.FileFullName == file.ReportInfo.FullName).ToList().Count;
}
Assert.IsTrue(fileRec == 0);
}
public static bool DeleteOldReportInfo(string fileName)
{
using (var db = new DW_iSecureCloud_EmptyEntities())
{
try
{
var files = (from r in db.T_REPORT_COLLECTION
where r.Name.Equals(fileName)
select r).ToList();
foreach (var reportCollection in files)
{
db.T_REPORT_COLLECTION.Remove(reportCollection);
}
db.SaveChanges();
}
catch (Exception e)
{
Log.Warn(string.Format("报表信息数据库删除失败:{0}", fileName), e);
return(false);
}
return(true);
}
}
public override void WriteFile()
{
if (TemplateHandlerPrams.Structure.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Structure.Id.ToString(), "结构物id异常");
}
if (TemplateHandlerPrams.Factor.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Factor.Id.ToString(), "监测因素id异常");
}
if (!File.Exists(TemplateHandlerPrams.TemplateFileName))
{
logger.Warn("模版文件 : " + TemplateHandlerPrams.TemplateFileName + "未找到");
throw new FileNotFoundException("模版文件 : " + TemplateHandlerPrams.TemplateFileName);
}
FileStream ms;
if (!File.Exists(TemplateHandlerPrams.FileFullName))
{
_existOrNot = false;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.CreateNew, FileAccess.Write);
}
else
{
_existOrNot = true;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.Open, FileAccess.ReadWrite);
}
// 读取模版
Workbook ww;
using (var fs = new FileStream(TemplateHandlerPrams.TemplateFileName, FileMode.Open, FileAccess.Read))
{
ww = new Workbook(fs);
fs.Close();
}
if (Cdbh == null)
{
ms.Close();
throw new ArgumentException();
}
if (Cdbh.Count() > 0)
{
try
{
string sb = TemplateHandlerPrams.Structure.Id.ToString() + '_' + TemplateHandlerPrams.Factor.Id.ToString();
Thread.Sleep(500);
int cnt = ReportCounter.Get(sb);
cnt += 1;
DateTime dtTime = TemplateHandlerPrams.Date;
if (TemplateHandlerPrams.TemplateFileName.Contains("SwDailyReportThy"))
{
FillBasicInformation(ww, 3, 0, "[projName]", TemplateHandlerPrams.Organization.SystemName);
}
FillBasicInformation(ww, 2, 0, "[rptNo]", this.rptNo);
FillBasicInformation(ww, 4, 0, "[date]",
TemplateHandlerPrams.Date.AddDays((-1)).ToString("yyyy年MM月dd日"));
FillBasicInformation(ww, 4, 10, "[count]", cnt.ToString());
// 查询水位数据
var todayData = new Dictionary <string, string>();
var yesterData = new Dictionary <string, string>();
using (var db = new DW_iSecureCloud_EmptyEntities())
{
int stcid = TemplateHandlerPrams.Structure.Id;
DateTime dateToday = TemplateHandlerPrams.Date;
DateTime dateYesterday = TemplateHandlerPrams.Date.AddDays(-1);
var query = from sw in db.T_THEMES_ENVI_WATER_LEVEL
from s in db.T_DIM_SENSOR
where s.STRUCT_ID == stcid && s.SENSOR_ID == sw.SENSOR_ID
select new
{
sw.ID,
sw.SENSOR_ID,
s.SENSOR_LOCATION_DESCRIPTION,
sw.WATER_LEVEL_CUMULATIVEVALUE,
sw.ACQUISITION_DATETIME
};
var td = query.Where(d => d.ACQUISITION_DATETIME < dateToday)
.GroupBy(d => d.SENSOR_ID).Select(d => d.Select(s => s.ID).Max());
var ys =
query.Where(d => d.ACQUISITION_DATETIME < dateYesterday)
.GroupBy(d => d.SENSOR_ID)
.Select(d => d.Select(s => s.ID).Max());
todayData =
query.Where(d => td.Contains(d.ID))
.Select(d => new { d.SENSOR_LOCATION_DESCRIPTION, d.WATER_LEVEL_CUMULATIVEVALUE })
.ToDictionary(d => d.SENSOR_LOCATION_DESCRIPTION,
d => Convert.ToDecimal(d.WATER_LEVEL_CUMULATIVEVALUE).ToString("#0.000"));
yesterData =
query.Where(d => ys.Contains(d.ID))
.Select(d => new { d.SENSOR_LOCATION_DESCRIPTION, d.WATER_LEVEL_CUMULATIVEVALUE })
.ToDictionary(d => d.SENSOR_LOCATION_DESCRIPTION,
d => Convert.ToDecimal(d.WATER_LEVEL_CUMULATIVEVALUE).ToString("#0.000"));
}
var sheet = ww.Worksheets[0];
int StartRowIndex = 6;
int InsertRowIndex = 8;
Array.Sort(Cdbh);
if (Cdbh.Count() > 3)
{
if (TemplateHandlerPrams.TemplateFileName.Contains("SwDailyReportThy"))
{
if (Cdbh.Count() > 4)
{
sheet.Cells.InsertRows(InsertRowIndex, Cdbh.Length - 4);
}
}
else
{
sheet.Cells.InsertRows(InsertRowIndex, Cdbh.Length - 3);
}
}
int structId = TemplateHandlerPrams.Structure.Id;
int factorId = TemplateHandlerPrams.Factor.Id;
List <SensorList> list = DataAccess.FindSensorsByStructAndFactor(structId, factorId);
foreach (SensorList sensorList in list)
{
foreach (Sensor sensor in sensorList.Sensors)
{
bool todayFlag = false;
bool yestodayFlag = false;
//测点位置
sheet.Cells[StartRowIndex, 0].PutValue(sensor.Location);
//初始高程
decimal?waterLevelInit = DataAccess.GetWaterLevelInit(sensor.SensorId);
if (waterLevelInit == null)
{
sheet.Cells[StartRowIndex, 1].PutValue("/");
}
else
{
sheet.Cells[StartRowIndex, 1].PutValue(Convert.ToDecimal(Convert.ToDecimal(waterLevelInit).ToString("#0.000")));
}
//本次高程
if (todayData.ContainsKey(sensor.Location) && todayData[sensor.Location] != null)
{
sheet.Cells[StartRowIndex, 2].PutValue(Convert.ToDecimal(todayData[sensor.Location]));
}
else
{
sheet.Cells[StartRowIndex, 2].PutValue("/");
todayFlag = true;
}
//上次高程
if (yesterData.ContainsKey(sensor.Location) && yesterData[sensor.Location] != null)
{
sheet.Cells[StartRowIndex, 3].PutValue(Convert.ToDecimal(yesterData[sensor.Location]));
}
else
{
sheet.Cells[StartRowIndex, 3].PutValue("/");
yestodayFlag = true;
}
//本次变化量
//变化速率
if (todayFlag || yestodayFlag)
{
sheet.Cells[StartRowIndex, 4].PutValue("/");
sheet.Cells[StartRowIndex, 6].PutValue("/");
}
else
{
var temp = (Convert.ToDecimal(todayData[sensor.Location]) - Convert.ToDecimal(yesterData[sensor.Location])) * 1000;
sheet.Cells[StartRowIndex, 4].PutValue(Convert.ToDecimal(temp.ToString("#0.000")));
sheet.Cells[StartRowIndex, 6].PutValue(Convert.ToDecimal(temp.ToString("#0.000")));
}
//累计变化量
if (waterLevelInit == null || todayFlag)
{
sheet.Cells[StartRowIndex, 5].PutValue("/");
}
else
{
var init = Convert.ToDecimal(Convert.ToDecimal(waterLevelInit).ToString("#0.000"));
var value = (Convert.ToDecimal(todayData[sensor.Location]) - init) * 1000;
sheet.Cells[StartRowIndex, 5].PutValue(value);
}
// 预警值
sheet.Cells[StartRowIndex, 7].PutValue(limit);
StartRowIndex++;
}
}
Thread.Sleep(500);
ReportCounter.Inc(sb);
ww.Save(ms, SaveFormat.Excel97To2003);
ms.Close();
}
catch (Exception ex)
{
logger.Warn(ex);
ms.Close();
throw ex;
}
}
else
{
logger.WarnFormat("{0}没有找到对应的信息，结构物ID：{1}，监测因素ID：{2}", TemplateHandlerPrams.TemplateFileName, Convert.ToString(TemplateHandlerPrams.Structure.Id), Convert.ToString(TemplateHandlerPrams.Factor.Id));
ms.Close();
throw new ArgumentException();
}
}
public static List <ReportGroup> GetReportConfig()
{
using (var db = new DW_iSecureCloud_EmptyEntities())
{
//var temp = from c in db.T_REPORT_CONFIG
// join ct in db.T_REPORT_CONFIG_TEMPLATE on c.Id equals ct.ReportConfigId
// join t in db.T_REPORT_TEMPLATE on ct.ReportTemplateId equals t.Id
// where c.IsEnabled
// select new {c.Id, c.OrgId, c.StructId, c.ReportName, c.CreateInterval,c.NeedConfirmed, c.DateType, c.GetDataTime, t.Name, t.HandleName, t.FactorId, ct.Para1, ct.Para2, ct.Para3, ct.Para4};
//var result = from s in temp
// group s by new { s.Id, s.OrgId, s.StructId, s.ReportName, s.CreateInterval, s.NeedConfirmed, s.DateType, s.GetDataTime }
// into g
// select new ReportGroup()
// {
// Config = new ReportConfig{Id = g.Key.Id, OrgId = g.Key.OrgId, StructId = g.Key.StructId, ReportName = g.Key.ReportName,CreateInterval = g.Key.CreateInterval, IsNeedConfirmed = g.Key.NeedConfirmed, DateType = (DateType)g.Key.DateType, },
// Templates = (from p in g select new ReportTemplate{Name = p.Name, HandleName = p.HandleName, Para1 = p.Para1, Para2 = p.Para2, Para3 = p.Para3, Para4 = p.Para4 }).ToList()
// Templates = (from p in g select new ReportTemplate { Name = p.Name, HandleName = p.HandleName, FactorId = p.FactorId, Para = new List<string>(){p.Para1, p.Para2, p.Para3, p.Para4} }).ToList()
// };
//return result.ToList();
var query = from u in db.T_REPORT_CONFIG
join r in db.T_DIM_ORGANIZATION on u.OrgId equals r.ID into r1
from s in r1.DefaultIfEmpty()
join st in db.T_DIM_STRUCTURE on u.StructId equals st.ID into uo1
from f in uo1.DefaultIfEmpty()
join c in db.T_REPORT_CONFIG_TEMPLATE on u.Id equals c.ReportConfigId into ct
from t in ct.DefaultIfEmpty()
join te in db.T_REPORT_TEMPLATE on t.ReportTemplateId equals te.Id into config
from re in config.DefaultIfEmpty()
where u.IsEnabled
select
new
{
id = u.Id,
orgId = (int?)s.ID,
orgName = s.ABB_NAME_CN,
structId = (int?)f.ID,
structName = f.STRUCTURE_NAME_CN,
reportName = u.ReportName,
templateId = (int?)re.Id,
templateName = re.Name,
templateHandleName = re.HandleName,
param1 = t.Para1,
param2 = t.Para2,
param3 = t.Para3,
param4 = t.Para4,
factorId = re.FactorId,
templateDes = re.Description,
dateType = u.DateType,
createInterval = u.CreateInterval,
getDataTime = u.GetDataTime,
needConfirm = u.NeedConfirmed,
isEnabled = u.IsEnabled
};
var data = query.ToList();
return
(data.GroupBy(
s => new { s.id, s.orgId, s.structId, s.reportName, s.createInterval, s.needConfirm, s.dateType, s.getDataTime })
.Select(
g =>
new ReportGroup()
{
Config = new ReportConfig {
Id = g.Key.id, OrgId = g.Key.orgId, StructId = g.Key.structId, ReportName = g.Key.reportName, CreateInterval = g.Key.createInterval, IsNeedConfirmed = g.Key.needConfirm, DateType = (DateType)g.Key.dateType,
},
Templates =
g.Select(
p =>
new ReportTemplate
{
Name = p.templateName,
HandleName = p.templateHandleName,
FactorId = p.factorId,
Para = new List <string>()
{
p.param1, p.param2, p.param3, p.param4
}
}).ToList()
}).ToList());
}
}
public static bool SaveReportInfo(ReportInfo reportInfo)
{
using (var db = new DW_iSecureCloud_EmptyEntities())
{
var state = Convert.ToInt16(reportInfo.Statue).ToString();
//var report = new T_REPORT_COLLECTION
//{
// Id = reportInfo.Id,
// Name = reportInfo.Name,
// Date = reportInfo.CreatedDate,
// FileFullName = reportInfo.FullName,
// Status = Convert.ToInt16(reportInfo.Statue).ToString(),
// OrgId = reportInfo.OrgId,
// StructId = reportInfo.StructureId,
// DateType = Convert.ToInt16(reportInfo.DateType)
//};
//var entry = db.Entry(report);
//entry.State = System.Data.EntityState.Added;
if (state == "1")
{
var report = new T_REPORT_COLLECTION
{
Id = reportInfo.Id,
Name = reportInfo.Name,
Date = reportInfo.CreatedDate,
FileFullName = reportInfo.FullName,
Status = Convert.ToInt16(reportInfo.Statue).ToString(),
OrgId = reportInfo.OrgId,
StructId = reportInfo.StructureId,
DateType = Convert.ToInt16(reportInfo.DateType)
};
var entry = db.Entry(report);
entry.State = System.Data.EntityState.Added;
}
else if (state == "0")
{
var report2 = new T_REPORT_COLLECTION
{
Id = reportInfo.Id,
Name = reportInfo.Name,
UnconfirmedDate = reportInfo.CreatedDate,
UnconfirmedFileUrl = reportInfo.FullName,
Status = Convert.ToInt16(reportInfo.Statue).ToString(),
OrgId = reportInfo.OrgId,
StructId = reportInfo.StructureId,
DateType = Convert.ToInt16(reportInfo.DateType)
};
var entry2 = db.Entry(report2);
entry2.State = System.Data.EntityState.Added;
}
try
{
db.SaveChanges();
}
catch (Exception e)
{
Log.Warn(string.Format("报表信息插入数据库失败:{0}", reportInfo.Name), e);
return(false);
}
}
return(true);
}
public override void WriteFile()
{
if (TemplateHandlerPrams.Structure.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Structure.Id.ToString(), "结构物id异常");
}
if (TemplateHandlerPrams.Factor.Id == 0)
{
throw new ArgumentNullException(TemplateHandlerPrams.Factor.Id.ToString(), "监测因素id异常");
}
if (!File.Exists(TemplateHandlerPrams.TemplateFileName))
{
logger.Warn("模版文件 : " + TemplateHandlerPrams.TemplateFileName + "未找到");
throw new FileNotFoundException("模版文件 : " + TemplateHandlerPrams.TemplateFileName);
}
FileStream ms;
if (!File.Exists(TemplateHandlerPrams.FileFullName))
{
_existOrNot = false;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.CreateNew, FileAccess.Write);
}
else
{
_existOrNot = true;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.Open, FileAccess.ReadWrite);
}
// 读取模版
Workbook ww;
using (var fs = new FileStream(TemplateHandlerPrams.TemplateFileName, FileMode.Open, FileAccess.Read))
{
ww = new Workbook(fs);
fs.Close();
}
DateTime dtTime = TemplateHandlerPrams.Date;
var groups = new List <CxGroup>();
// 根据传感器列表创建sheet页
try
{
using (var db = new DW_iSecureCloud_EmptyEntities())
{
var stcId = TemplateHandlerPrams.Structure.Id;
var fctId = TemplateHandlerPrams.Factor.Id;
groups = (from s in db.T_DIM_SENSOR
from sg in db.T_DIM_SENSOR_GROUP_CEXIE
from g in db.T_DIM_GROUP
where
s.SENSOR_ID == sg.SENSOR_ID && sg.GROUP_ID == g.GROUP_ID && s.STRUCT_ID == stcId &&
s.SAFETY_FACTOR_TYPE_ID == fctId
select new { g.GROUP_ID, g.GROUP_NAME, sg.DEPTH }).ToList()
.GroupBy(g => new { g.GROUP_ID, g.GROUP_NAME })
.Select(
s =>
new CxGroup
{
Id = s.Key.GROUP_ID,
Name = s.Key.GROUP_NAME,
Depth = s.Select(d => d.DEPTH * -1).OrderBy(d => d).ToArray()
})
.ToList();
}
}
catch (Exception ex)
{
logger.Warn(ex.Message);
ms.Close();
throw ex;
}
if (groups.Count > 0)
{
try
{
string sb = TemplateHandlerPrams.Structure.Id.ToString() + '_' + TemplateHandlerPrams.Factor.Id.ToString();
Thread.Sleep(300);
int cs = ReportCounter.Get(sb);
cs += 1;
// 复制sheet
ww.Worksheets[0].Name = groups[0].Name ?? "测斜管" + (0 + 1);
for (int i = 1; i < groups.Count; i++)
{
var index = ww.Worksheets.AddCopy(0);
ww.Worksheets[index].Name = groups[i].Name ?? "测斜管" + (i + 1);
}
// 填写数据
for (int i = 0; i < ww.Worksheets.Count; i++)
{
var sheet = ww.Worksheets[i];
// 设置项目编号
var rptNo = sheet.Cells[2, 0].StringValue;
sheet.Cells[2, 0].PutValue(rptNo.Replace("[rptNo]", this.rptNo));
// 设置项目名称
if (!TemplateHandlerPrams.TemplateFileName.Contains("CxDailyReportNcdwy"))
{
var projName = sheet.Cells[3, 0].StringValue;
sheet.Cells[3, 0].PutValue(projName.Replace("[projName]",
TemplateHandlerPrams.Organization.SystemName));
}
// 设置监测点信息
var info = sheet.Cells[4, 0].StringValue;
sheet.Cells[4, 0].PutValue(
info.Replace("[date]", TemplateHandlerPrams.Date.AddDays(-1).ToString("yyyy年MM月dd日"))
.Replace("[sensor]", sheet.Name));
// 设置生成次数
var cout = sheet.Cells[4, 8].StringValue;
sheet.Cells[4, 8].PutValue(cout.Replace("[count]", cs.ToString()));
// 获取本次累积位移
var dataToday = DataAccess.GetByGroupDirectAndDateGroupByTime(
groups[i].Id,
TemplateHandlerPrams.Date.AddDays(-1).Date, /*当前时间0时0分0秒*/
TemplateHandlerPrams.Date.Date.AddSeconds(-1) /*当前时间23:59:59*/);
// 获取上次累积位移
var dataYesterday = DataAccess.GetByGroupDirectAndDateGroupByTime(
groups[i].Id,
TemplateHandlerPrams.Date.AddDays(-2).Date, /*前一天0时0分0秒*/
TemplateHandlerPrams.Date.AddDays((-1)).Date.AddSeconds(-1) /*前一天23:59:59*/);
int startRowIndex = 6;
#region 测斜管组下的深度个数如果超出模板设定范围,动态增加行
int InsertRowIndex = 8;
if (groups[i].Depth.Length > 3)
{
sheet.Cells.InsertRows(InsertRowIndex, groups[i].Depth.Length - 3);
}
#endregion
// 设置数据
foreach (var depth in groups[i].Depth)
{
// 设置深度
sheet.Cells[startRowIndex, 0].PutValue(depth);
// 上次累积位移
decimal?yesterdayValue = 0;
if (dataYesterday.Count != 0)
{
var depthValue = dataYesterday[0].Data.FirstOrDefault(d => d.Depth * -1 == depth);
if (depthValue != null)
{
yesterdayValue = depthValue.YValue;
}
}
// 本次累积位移
decimal?todayValue = 0;
if (dataToday.Count != 0)
{
var depthValue = dataToday[0].Data.FirstOrDefault(d => d.Depth * -1 == depth);
if (depthValue != null)
{
todayValue = depthValue.YValue;
}
}
// 变化量
decimal?dlta = todayValue - yesterdayValue;
// 设置数据
sheet.Cells[startRowIndex, 1].PutValue(dlta);
sheet.Cells[startRowIndex, 2].PutValue(yesterdayValue);
sheet.Cells[startRowIndex, 3].PutValue(todayValue);
sheet.Cells[startRowIndex, 5].PutValue(warnValue);
sheet.Cells[startRowIndex, 6].PutValue(limitValue);
startRowIndex++;
}
}
Thread.Sleep(300);
ReportCounter.Inc(sb);
ww.Save(ms, SaveFormat.Excel97To2003);
ms.Close();
}
catch (Exception ex)
{
logger.Warn(ex);
ms.Close();
throw ex;
}
}
else
{
logger.WarnFormat("{0}没有找到对应的信息，结构物ID：{1}，监测因素ID：{2}", TemplateHandlerPrams.TemplateFileName, Convert.ToString(TemplateHandlerPrams.Structure.Id), Convert.ToString(TemplateHandlerPrams.Factor.Id));
ms.Close();
throw new ArgumentException();
}
}
