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);
}
//读取目标（如果不存在则创建，否则直接使用）
Workbook md;
FileStream ms;
if (!File.Exists(TemplateHandlerPrams.FileFullName))
{
ExistOrNot = false;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.CreateNew, FileAccess.Write);
md = new Workbook(ms);
}
else
{
ExistOrNot = true;
ms = new FileStream(TemplateHandlerPrams.FileFullName, FileMode.Open, FileAccess.ReadWrite);
md = new Workbook(ms);
}
// 读取模版
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
{
DateTime dtTime = TemplateHandlerPrams.Date;
FillBasicInformation(ww, 0, 0, "projectName", TemplateHandlerPrams.Organization.SystemName);
FillBasicInformation(ww, 2, 0, "contractorUnit", TemplateHandlerPrams.Structure.ConstructionCompany);
FillBasicInformation(ww, 2, 12, "contractNo", "数据库暂无");
FillBasicInformation(ww, 3, 0, "supervisingUnit", "数据库暂无");
FillBasicInformation(ww, 3, 12, "factorNo", TemplateHandlerPrams.Factor.Id.ToString());
FillBasicInformation(ww, 4, 0, "position", TemplateHandlerPrams.Structure.Name);
FillBasicInformation(ww, 5, 3, "instrumentName", SensorProduct.ProductName);
//获取采集时间上周一数据
var mondayDate = GetLastWeekEachDay("Monday", dtTime);
var mondayData = DataAccess.GetUnifyData(SensorsId, mondayDate, mondayDate.AddDays(+1).AddMilliseconds(-1), true);
FillDateTime(ww, 7, 3, mondayDate);
FillDateTime(ww, 5, 0, "dateFrom", mondayDate);
//获取采集时间上周二数据
var tuesdayDate = GetLastWeekEachDay("Tuesday", dtTime);
var tuesdayData = DataAccess.GetUnifyData(SensorsId, tuesdayDate, tuesdayDate.AddDays(+1).AddMilliseconds(-1), true);
FillDateTime(ww, 7, 4, tuesdayDate);
//获取采集时间上周三数据
var wednesdayDate = GetLastWeekEachDay("Wednesday", dtTime);
var wednesdayData = DataAccess.GetUnifyData(SensorsId, wednesdayDate, wednesdayDate.AddDays(+1).AddMilliseconds(-1),
true);
FillDateTime(ww, 7, 5, wednesdayDate);
//获取采集时间上周四数据
var thursdayDate = GetLastWeekEachDay("Thursday", dtTime);
var thursdayData = DataAccess.GetUnifyData(SensorsId, tuesdayDate, thursdayDate.AddDays(+1).AddMilliseconds(-1), true);
FillDateTime(ww, 7, 6, thursdayDate);
//获取采集时间上周五数据
var fridayDate = GetLastWeekEachDay("Friday", dtTime);
var fridayData = DataAccess.GetUnifyData(SensorsId, fridayDate, fridayDate.AddDays(+1).AddMilliseconds(-1), true);
FillDateTime(ww, 7, 7, fridayDate);
//获取采集时间上周六数据
var saturdayDate = GetLastWeekEachDay("Saturday", dtTime);
var saturdayData = DataAccess.GetUnifyData(SensorsId, saturdayDate, saturdayDate.AddDays(+1).AddMilliseconds(-1), true);
FillDateTime(ww, 7, 8, saturdayDate);
//获取采集时间上周日数据
var sundayDate = GetLastWeekEachDay("Sunday", dtTime);
var sundayData = DataAccess.GetUnifyData(SensorsId, sundayDate, sundayDate.AddDays(+1).AddMilliseconds(-1), true);
FillDateTime(ww, 5, 0, "dateTo", sundayDate);
FillDateTime(ww, 7, 9, sundayDate);
//本周变化量
var mondayFirstData = DataAccess.GetUnifyData(SensorsId, mondayDate, mondayDate.AddDays(+1).AddMilliseconds(-1), false);
var sheet = ww.Worksheets[0];
int startRowIndex = 8;
for (int i = 0; i < Cdbh.Length; i++)
{
sheet.Cells[startRowIndex, 0].PutValue(Cdbh[i]);
FillEveryDayData(ww, startRowIndex, 3, 0, 0, startRowIndex, 1, Cdbh[i], mondayData);
FillEveryDayData(ww, startRowIndex, 4, 0, 0, startRowIndex, 1, Cdbh[i], tuesdayData);
FillEveryDayData(ww, startRowIndex, 5, 0, 0, startRowIndex, 1, Cdbh[i], wednesdayData);
FillEveryDayData(ww, startRowIndex, 6, 0, 0, startRowIndex, 1, Cdbh[i], tuesdayData);
FillEveryDayData(ww, startRowIndex, 7, 0, 0, startRowIndex, 1, Cdbh[i], fridayData);
FillEveryDayData(ww, startRowIndex, 8, 0, 0, startRowIndex, 1, Cdbh[i], saturdayData);
FillEveryDayData(ww, startRowIndex, 9, 0, 0, startRowIndex, 1, Cdbh[i], saturdayData);
FillBCData(ww, startRowIndex, 13, 0, 0, Cdbh[i], sundayData);
FillVariation(ww, startRowIndex, 10, 0, 0, Cdbh[i], mondayFirstData, sundayData);
FillLJVariation(ww, startRowIndex, 11);
FillLastWeekLJVariation(ww, startRowIndex, 2, 11, 10);
FillVaryRate(ww, startRowIndex, 12);
startRowIndex++;
}
if (ExistOrNot == true)
{
md.Worksheets.RemoveAt(TemplateHandlerPrams.Factor.NameCN);
Worksheet tmp = md.Worksheets.Add(TemplateHandlerPrams.Factor.NameCN);
tmp.Copy(ww.Worksheets[0]);
}
else
{
md.Worksheets[0].Copy(ww.Worksheets[0]);
md.Worksheets[0].Name = TemplateHandlerPrams.Factor.NameCN;
}
md.Save(ms, SaveFormat.Excel97To2003);
ms.Close();
}
catch (Exception ex)
{
logger.Warn(ex.Message);
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();
}
}