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 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();
}
}