private void ExcelReplaceValueDiffByType(IWorkbook workbook, ISheet sheet, IRow row, dynamic valuePair, DateTime startTime, DateTime endTime,
int reportType, string bmfId, string precision, int startIndex, ref int endIndex)
{
ICellStyle cellstyle = GetCellStyle(workbook, precision);
switch (reportType)
{
#region 年（每月）
case 1:
var monthList = (List <MonRecord>)valuePair;
for (DateTime sTime = startTime; sTime <= endTime; sTime = sTime.AddMonths(1), startIndex++)
{
ICell cell = row.CreateCell(startIndex);
var cellValue = monthList.FirstOrDefault(a => a.MfId == bmfId && a.HTime == sTime);
if (cellValue == null)
{
cellValue = new MonRecord();
}
cell.SetCellValue(cellValue == null ? 0.00 : GetPrecision(precision, cellValue.TotalData));
cell.CellStyle = cellstyle;
}
endIndex = startIndex;
break;
#endregion
#region 月（每天）
case 2:
var dayList = (List <DayRecord>)valuePair;
for (DateTime sTime = startTime; sTime <= endTime; sTime = sTime.AddDays(1), startIndex++)
{
ICell cell = row.CreateCell(startIndex);
var cellValue = dayList.FirstOrDefault(a => a.MfId == bmfId && a.HTime == sTime);
if (cellValue == null)
{
cellValue = new DayRecord();
}
cell.SetCellValue(cellValue != null ? GetPrecision(precision, cellValue.TotalData) : 0.00);
cell.CellStyle = cellstyle;
}
endIndex = startIndex;
break;
#endregion
#region 比
case 3:
var yoyList = (List <ComparsionDto>)valuePair;
for (DateTime sTime = startTime; sTime <= endTime; sTime = sTime.AddMonths(1), startIndex++)
{
var yoyEntity = yoyList.FirstOrDefault(a => a.MfId == bmfId && a.HTime == sTime);
if (yoyEntity == null)
{
yoyEntity = new ComparsionDto();
}
ICell currentCell = row.CreateCell(startIndex);
currentCell.SetCellValue(GetPrecision(precision, yoyEntity.CurrentData));
currentCell.CellStyle = cellstyle;
startIndex = startIndex + 1;
ICell lastCell = row.CreateCell(startIndex);
lastCell.SetCellValue(GetPrecision(precision, yoyEntity.LastData));
lastCell.CellStyle = cellstyle;
startIndex = startIndex + 1;
ICell rateCell = row.CreateCell(startIndex);
rateCell.SetCellValue(GetPrecision(precision, yoyEntity.Rate));
rateCell.CellStyle = cellstyle;
}
var totalCurrent = yoyList.Where(a => a.MfId == bmfId).Sum(a => a.CurrentData);
var totalLast = yoyList.Where(a => a.MfId == bmfId).Sum(a => a.LastData);
var totalRate = totalLast == 0 ? 100 : (totalCurrent - totalLast) / totalLast;
ICell totalCell = row.CreateCell(startIndex);
totalCell.SetCellValue(GetPrecision(precision, totalCurrent));
totalCell.CellStyle = cellstyle;
startIndex = startIndex + 1;
ICell totalLastCell = row.CreateCell(startIndex);
totalLastCell.SetCellValue(GetPrecision(precision, totalLast));
totalLastCell.CellStyle = cellstyle;
startIndex = startIndex + 1;
ICell totalRateCell = row.CreateCell(startIndex);
totalRateCell.SetCellValue(GetPrecision(precision, totalRate));
totalRateCell.CellStyle = cellstyle;
endIndex = startIndex;
break;
#endregion
#region 环比
case 4:
var qoqList = (List <ComparsionClass>)valuePair;
var mfId = new Guid(bmfId);
var qoqEntity = qoqList.FirstOrDefault(a => a.ReferenceId == mfId);
if (qoqEntity == null)
{
qoqEntity = new ComparsionClass();
}
ICell yesterdayValue = row.CreateCell(startIndex);
yesterdayValue.SetCellValue(GetPrecision(precision, qoqEntity.YesterDayVal));
yesterdayValue.CellStyle = cellstyle;
startIndex++;
ICell todayValue = row.CreateCell(startIndex);
todayValue.SetCellValue(GetPrecision(precision, qoqEntity.CurrentVal));
todayValue.CellStyle = cellstyle;
startIndex++;
ICell qoqRate = row.CreateCell(startIndex);
var rateValue = qoqEntity.YesterDayVal == 0 ? 0 : GetPrecision(precision, (qoqEntity.CurrentVal - qoqEntity.YesterDayVal) * 100 / qoqEntity.YesterDayVal);
qoqRate.SetCellValue(rateValue);
qoqRate.CellStyle = cellstyle;
startIndex++;
ICell monthValue = row.CreateCell(startIndex);
monthValue.SetCellValue(GetPrecision(precision, qoqEntity.CurrentMonthVal));
monthValue.CellStyle = cellstyle;
startIndex++;
ICell lastMonthValue = row.CreateCell(startIndex);
lastMonthValue.SetCellValue(GetPrecision(precision, qoqEntity.LastMonthVal));
lastMonthValue.CellStyle = cellstyle;
startIndex++;
ICell qoqMonthRate = row.CreateCell(startIndex);
var monthRateValue = qoqEntity.LastMonthVal == 0 ? 0.0 : GetPrecision(precision, (qoqEntity.CurrentMonthVal - qoqEntity.LastMonthVal) * 100 / qoqEntity.LastMonthVal);
qoqMonthRate.SetCellValue(monthRateValue);
qoqMonthRate.CellStyle = cellstyle;
endIndex = startIndex;
break;
#endregion
#region 日抄表能耗
case 5:
var dayCheckList = (List <RealTimeData>)valuePair;
var dayCheckEntity = dayCheckList.FirstOrDefault(a => a.mftId == bmfId);
if (dayCheckEntity == null)
{
dayCheckEntity = new RealTimeData();
}
ICell preValue = row.CreateCell(startIndex);
preValue.SetCellValue(GetPrecision(precision, dayCheckEntity.PreData));
preValue.CellStyle = cellstyle;
startIndex++;
ICell preDateValue = row.CreateCell(startIndex);
preDateValue.SetCellValue(dayCheckEntity.LastTime);
preDateValue.CellStyle = cellstyle;
startIndex++;
ICell currentDateValue = row.CreateCell(startIndex);
currentDateValue.SetCellValue(dayCheckEntity.Htime);
currentDateValue.CellStyle = cellstyle;
startIndex++;
ICell currentValue = row.CreateCell(startIndex);
currentValue.SetCellValue(GetPrecision(precision, dayCheckEntity.NextData));
currentValue.CellStyle = cellstyle;
startIndex++;
ICell energyValue = row.CreateCell(startIndex);
energyValue.SetCellValue(GetPrecision(precision, dayCheckEntity.Sum));
energyValue.CellStyle = cellstyle;
startIndex++;
endIndex = startIndex;
break;
#endregion
#region 峰谷平
case 6:
#endregion
#region 日跨时间范围
case 7:
#endregion
#region 班组能耗
case 8:
#endregion
#region 时间点
case 9:
#endregion
#region 灵活式
case 10:
break;
#endregion
}
}
private void GetReportDataSource(ISheet sheet, HSSFWorkbook workbook, int rptType, string precision,
string startTime, string endTime, bool isShouldResizeColumn, Guid companyId)
{
List <Guid> bmfIds = new List <Guid>();
GetBmfIds(sheet, bmfIds);
Trans trans = new Trans();
DateTime stime = DateTime.Now;
DateTime etime = DateTime.Now;
switch (rptType)
{
#region 年(每月)报表
case 1:
trans.stime = startTime;
trans.etime = trans.stime;
var monthRecords = DataHelper.GetMonRecordByWhere(trans, bmfIds, companyId);
SheetVariableReplace(sheet, workbook, monthRecords.OrderBy(a => a.HTime).ToList(),
new DateTime(int.Parse(trans.stime), 1, 1),
new DateTime(int.Parse(trans.stime), 12, 31),
rptType, precision, isShouldResizeColumn);
break;
#endregion
#region 月(每日)报表
case 2:
var dateArr = startTime.Split('-');
var year = dateArr[0];
var month = dateArr[1];
var days = DateTime.DaysInMonth(int.Parse(year), int.Parse(month));
trans.stime = year + "-" + month + "-01";
trans.etime = year + "-" + month + "-" + days;
var dayRecords = DataHelper.GetDayRecordByWhere(trans, bmfIds, companyId);
SheetVariableReplace(sheet, workbook, dayRecords.OrderBy(a => a.HTime).ToList(),
DateTime.Parse(trans.stime), DateTime.Parse(trans.etime),
rptType, precision, isShouldResizeColumn);
break;
#endregion
#region 比报表
case 3:
trans.stime = startTime;
trans.etime = trans.stime;
var yearMonthRecords = DataHelper.GetMonRecordByWhere(trans, bmfIds, companyId);
trans.stime = (Convert.ToInt32(trans.stime) - 1).ToString();
trans.tempetime = trans.stime;
var lastYearMonthRecords = DataHelper.GetMonRecordByWhere(trans, bmfIds, companyId);
var yoyList = new List <ComparsionDto>();
DateTime yoysTime = new DateTime(int.Parse(startTime), 1, 1);
DateTime yoyeTime = new DateTime(int.Parse(startTime), 12, 1);
DateTime lastHtime = yoysTime.AddYears(-1);
foreach (var bmfId in bmfIds)
{
lastHtime = yoysTime.AddYears(-1);
for (DateTime sstime = yoysTime; sstime <= yoyeTime; sstime = sstime.AddMonths(1), lastHtime = lastHtime.AddMonths(1))
{
var mfId = bmfId.ToString();
double totalData = 0.0d;
var currentData = yearMonthRecords.FirstOrDefault(a => a.MfId == mfId && a.HTime == sstime);
var lastData = lastYearMonthRecords.FirstOrDefault(a => a.MfId == mfId && a.HTime == lastHtime);
if (lastData == null || lastData.TotalData == 0)
{
if (currentData != null && currentData.TotalData != 0)
{
totalData = 100;
}
else
{
totalData = 0;
}
}
else if (currentData == null || currentData.TotalData == 0)
{
if (lastData != null && lastData.TotalData != 0)
{
totalData = -100;
}
else
{
totalData = 0;
}
}
else
{
totalData = (currentData.TotalData - lastData.TotalData) / lastData.TotalData;
}
ComparsionDto record = new ComparsionDto
{
HTime = sstime,
MfId = mfId,
CurrentData = currentData == null ? 0 : currentData.TotalData,
LastData = lastData == null ? 0 : lastData.TotalData,
Rate = totalData,
};
yoyList.Add(record);
}
}
SheetVariableReplace(sheet, workbook, yoyList.OrderBy(a => a.HTime).ToList(),
yoysTime, yoyeTime, rptType, precision, isShouldResizeColumn);
break;
#endregion
#region 环比
case 4:
bmfIds = bmfIds.Distinct().ToList();
var now = DateTime.Now;
var yesterday = now.AddDays(-1);
var qoqmonth = DateTime.Parse(now.ToString("yyyy-MM-01"));
var lastMonth = qoqmonth.AddMonths(-1);
List <ComparsionClass> cmpList = new List <ComparsionClass>();
foreach (var mfId in bmfIds)
{
BaseRecord dayRecord = DataHelper.GetDayRecord(mfId, companyId, now);
ComparsionClass comparsion = new ComparsionClass();
comparsion.CurrentVal = dayRecord == null ? 0 : dayRecord.TotalData;
dayRecord = DataHelper.GetDayRecord(mfId, companyId, yesterday);
comparsion.YesterDayVal = dayRecord == null ? 0 : dayRecord.TotalData;
dayRecord = DataHelper.GetMonthRecord(mfId, companyId, qoqmonth);
comparsion.CurrentMonthVal = dayRecord == null ? 0 : dayRecord.TotalData;
dayRecord = DataHelper.GetMonthRecord(mfId, companyId, lastMonth);
comparsion.LastMonthVal = dayRecord == null ? 0 : dayRecord.TotalData;
comparsion.ReferenceId = mfId;
cmpList.Add(comparsion);
}
SheetVariableReplace(sheet, workbook, cmpList, DateTime.Now, DateTime.Now,
rptType, precision, isShouldResizeColumn);
break;
#endregion
#region 日抄表能耗报表
case 5:
var factory = ServiceFactory.CreateService <ICollectValuesService>();
var time = DateTime.Parse(startTime);
stime = time.AddSeconds(-1);
List <RealTimeData> datas = new List <RealTimeData>();
foreach (var bmfId in bmfIds)
{
RealTimeData data = new RealTimeData();
var autodata = factory.GetEarliestAutoDatasByMfIdLessThanTime(companyId, bmfId, stime);//cy
if (autodata != null)
{
data.LastTime = autodata.Mt.ToString("yyyy-MM-dd HH:mm:ss");
}
data.PreData = autodata == null ? 0 : autodata.Mv;
var nextautodata = factory.GetEarliestAutoDatasByMfIdLessThanTime(companyId, bmfId, DateTime.Now); //cy
data.NextData = nextautodata == null ? 0 : nextautodata.Mv;
data.mftId = bmfId.ToString();
data.Sum = data.NextData - data.PreData;
if (nextautodata != null)
{
data.Htime = nextautodata.Mt.ToString("yyyy-MM-dd HH:mm:ss"); //当前时间
}
datas.Add(data);
}
factory.CloseService();
SheetVariableReplace(sheet, workbook, datas, DateTime.Now, DateTime.Now,
rptType, precision, isShouldResizeColumn);
break;
#endregion
#region 峰谷平能耗报表
case 6:
#endregion
#region 日跨时间范围报表
case 7:
#endregion
#region 班组能耗报表
case 8:
#endregion
#region 时间点抄表报表
case 9:
#endregion
#region 灵活式结构
case 10:
#endregion
#region 定类型报表
case 11:
break;
#endregion
}
}