public string ExportFeeApportion()
{
var inputValue = _ntsPage.Request.Form["Inputs"];
var query = Newtonsoft.Json.JsonConvert.DeserializeObject<Queryfeeapportion>(inputValue);
List<FeeApportionListClass> dtRef = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IFee_ApportionService>("Fee_ApportionService").GetFeeApportDataList(query);
try
{
//
if ((dtRef != null) && (dtRef.Count > 0))
{
DataTable dtReport = TableView.CreateFee_ApportionDataTable();
for (var r = 0; r < dtRef.Count; r++)
{
DataRow dr = dtReport.NewRow();
dr[1] = dtRef[r].Tm.ToString();
dr[2] = dtRef[r].Obj.ToString();
dr[3] = Math.Round(dtRef[r].BeforeVal, 2);
dr[4] = Math.Round(dtRef[r].ApportionVal, 2);
dr[5] = Math.Round(dtRef[r].TotalVal, 2);
dtReport.Rows.Add(dr);
}
string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\";
if (!Directory.Exists(temp_path))
{
Directory.CreateDirectory(temp_path);
string[] files = Directory.GetFiles(temp_path);
foreach (string fn in files)
{
File.Delete(temp_path + fn);
}
}
string save_path = DateTime.Now.Ticks + ".xls";
string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\费用分摊表.xls";
TemplateParam param = new TemplateParam("费用分摊表", new CellParam(0, 0), "", new CellParam(3, 0), false, new CellParam(4, 0));
//TemplateParam param = new TemplateParam("itemCodeName", new CellParam(1, 1),"",null, false, new CellParam(5, 0));
param.DataColumn = new[] { 0, 1, 2, 3, 4, 5 };
param.ItemUnit = "（单位：元 ）";
param.ItemUnitCell = new CellParam(3, 5);
dtReport.TableName = "费用分摊表";
ExportHelper.ExportExcel(dtReport, temp_path + save_path, templatePath, param);
return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}";
}
else
{
return "{\"status\":\"error\",\"msg\":\"导出失败：当前无任何数据\"}";
}
}
catch (Exception ex)
{
return "{\"status\":\"error\",\"msg\":\"导出失败：由于当前无数据或其他原因导致" + ex.Message + "\"}";
}
}
public string ExportLoadForecast()
{
var inputValue = _ntsPage.Request.Form["Inputs"];
var query = Newtonsoft.Json.JsonConvert.DeserializeObject<QueryLoadForecast>(inputValue);
ResultLoadForecastMap dtRef = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.ILoadForecastService>("LoadForecastService").GetLoadForecastChart(query);
try
{
if ((dtRef != null) && (dtRef.LoadForecast.Count > 0))
{
DataTable dtReport = TableView.CreateFee_ForecastDataTable();
List<ResultLoadForecastList> listNew = dtRef.LoadForecast;
for (var r = 0; r < listNew.Count; r++)
{
DataRow dr = dtReport.NewRow();
dr[1] = listNew[r].TimeArea.ToString();
dr[2] = listNew[r].ForeCast.ToString();
if (listNew[r].History==-9999)
{
dr[3] = "--";
dr[4] = "--";
dr[5] = "--";
}
else
{
dr[3] = listNew[r].History.ToString();
dr[4] = listNew[r].Deviation.ToString();
dr[5] = listNew[r].Pecent.ToString();
}
dtReport.Rows.Add(dr);
}
string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\";
if (!Directory.Exists(temp_path))
{
Directory.CreateDirectory(temp_path);
string[] files = Directory.GetFiles(temp_path);
foreach (string fn in files)
{
File.Delete(temp_path + fn);
}
}
string save_path = DateTime.Now.Ticks + ".xls";
string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\负荷预测表.xls";
TemplateParam param = new TemplateParam("负荷预测表", new CellParam(0, 0), "", new CellParam(3, 0), false, new CellParam(4, 0));
//TemplateParam param = new TemplateParam("itemCodeName", new CellParam(1, 1),"",null, false, new CellParam(5, 0));
param.DataColumn = new[] { 0, 1, 2, 3, 4, 5 };
//param.ItemUnit = "（单位：元";
//param.ItemUnitCell = new CellParam(3, 5);
dtReport.TableName = "负荷预测表";
ExportHelper.ExportExcel(dtReport, temp_path + save_path, templatePath, param);
return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}";
}
else
{
return "{\"status\":\"error\",\"msg\":\"导出失败：当前无任何数据\"}";
}
}
catch (Exception ex)
{
return "{\"status\":\"error\",\"msg\":\"导出失败：由于当前无数据或其他原因导致" + ex.Message + "\"}";
}
}
public string ExportSysLogExcel()
{
try
{
#region 获取数据
var inputValue = _ntsPage.Request.Form["Inputs"];
var query = Newtonsoft.Json.JsonConvert.DeserializeObject<QuerySysLogContact>(inputValue);
query.PageCurrent = 1;
query.PageSize = 100000000;
var result = new NTS.EMS.Config.BLL.QuerySysLogBll().GetSysLog(query);
#endregion
#region 组织数据
#endregion
if (result.SysLogList.Count > 0)
{
DataTable dt = TableView.CreateSysLogDataTable();
for (var i = 0; i < result.SysLogList.Count; i++)
{
DataRow dr = dt.NewRow();
dr[1] = result.SysLogList[i].SysNo;
dr[2] = result.SysLogList[i].ModelName;
dr[3] = result.SysLogList[i].LogContent;
dr[4] = result.SysLogList[i].LogTime;
dr[5] = result.SysLogList[i].OpType == OpType.Operate ? "操作" : "配置";
dr[6] = result.SysLogList[i].UserName;
dt.Rows.Add(dr);
}
string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\";
if (!Directory.Exists(temp_path))
{
Directory.CreateDirectory(temp_path);
string[] files = Directory.GetFiles(temp_path);
foreach (string fn in files)
{
File.Delete(temp_path + fn);
}
}
string save_path = DateTime.Now.Ticks + ".xls";
string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\日志查询表.xls";
string suTitle = "";
string startTime = query.StartTime.ToString("yyyy-MM-dd");
string endTime = query.EndTime.ToString("yyyy-MM-dd");
if (query.StartTime.ToShortDateString() == "1900-1-1")
{
startTime = result.SysLogList.Min(p => p.LogTime).ToString("yyyy-MM-dd");
}
if (query.EndTime.ToShortDateString() == "1900-1-1")
{
endTime = result.SysLogList.Max(p => p.LogTime).ToString("yyyy-MM-dd");
}
suTitle = startTime + "~" + endTime;
TemplateParam param = new TemplateParam("日志查询", new CellParam(0, 0), suTitle, new CellParam(3, 0), false, new CellParam(4, 0));
param.DataColumn = new[] { 0, 6, 4, 5, 2, 3 };
param.SortColumn = 0;
dt.TableName = "日志查询";
ExportHelper.ExportExcel(dt, temp_path + save_path, templatePath, param);
return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}";
}
else
{
return "{\"status\":\"error\",\"msg\":\"导出失败：当前无任何数据\"}";
}
}
catch (Exception ex)
{
return "{\"status\":\"error\",\"msg\":\"导出失败：由于当前无数据或其他原因导致" + ex.Message + "\"}";
}
}
/// <summary>
/// 从模版中导出Excel文件
/// </summary>
/// <param name="dt">数据源DataTable</param>
/// <param name="path">导出文件路径</param>
/// <param name="templatePath">模版路径</param>
/// <param name="param"></param>
public static void ExportExcel(DataTable dt, string path, string templatePath, TemplateParam param)
{
FileStream templateFile = new FileStream(templatePath, FileMode.Open, FileAccess.Read);//读取模版文件
hssfworkbook = new HSSFWorkbook(templateFile);
ISheet sheet1 = hssfworkbook.GetSheet(string.IsNullOrEmpty(dt.TableName) ? "Sheet1" : dt.TableName);//获取第一张工作表
ICellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
//插入标题
if (param.TitleCell != null)
{
IRow rowTitle = sheet1.GetRow(param.TitleCell.Row);
ICell cellTitle = rowTitle.GetCell(param.TitleCell.Cell);
if (!string.IsNullOrEmpty(param.Title))
cellTitle.SetCellValue(param.Title+cellTitle.StringCellValue);
}
//插入副标题
if (param.SubTitleCell != null)
{
IRow rowSubTitle = sheet1.GetRow(param.SubTitleCell.Row);
ICell cellSubTitle = rowSubTitle.GetCell(param.SubTitleCell.Cell);
if (!string.IsNullOrEmpty(param.SubTitle))
cellSubTitle.SetCellValue(param.SubTitle);
}
//插入单位
if (param.ItemUnitCell != null)
{
IRow rowItemUnit = sheet1.GetRow(param.ItemUnitCell.Row);
ICell cellItemUnit = rowItemUnit.GetCell(param.ItemUnitCell.Cell);
if (!string.IsNullOrEmpty(param.ItemUnit))
cellItemUnit.SetCellValue(param.ItemUnit);
}
int columnCount = param.DataColumn.Length > 0 ? param.DataColumn.Length : dt.Columns.Count;
//插入表格内容
if (param.DataCell != null)
{
if (param.DataTitle)
{
IRow rowDataTitle = sheet1.GetRow(param.DataCell.Row);
ICell cellDataTitle;
for (int m = 0; m < columnCount; m++)
{
cellDataTitle = rowDataTitle.GetCell(m);
cellDataTitle.CellStyle = style;
string title = dt.Columns[param.DataColumn[m]].ColumnName;
if (m == param.SortColumn)
title = "排序";
cellDataTitle.SetCellValue(title);
}
}
IRow row;
ICell cell;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
row = sheet1.GetRow(i + param.DataCell.Row + 1) ?? sheet1.CreateRow(i + param.DataCell.Row + 1);
for (int j = 0; j < columnCount; j++)
{
cell = row.GetCell(j) ?? row.CreateCell(j);
cell.CellStyle = style;
object obj = dt.Rows[i][param.DataColumn[j]];
if (j == param.SortColumn)
obj = i + 1;
if(param.SpecialColumn.Count>0)
{
if (param.SpecialColumn.ContainsKey(j))
{
obj = param.SpecialColumn[j];
}
}
SetCellValue(cell, obj, dt.Columns[param.DataColumn[j]]);
}
}
}
}
sheet1.ForceFormulaRecalculation = true;
FileStream file = new FileStream(path, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
/// <summary>
/// 从模版中导出Excel文件
/// </summary>
/// <param name="dt">数据源DataTable</param>
/// <param name="path">导出文件路径</param>
/// <param name="templatePath">模版路径</param>
/// <param name="param"></param>
public static void ExportExcel(DataTable dt, string path, string templatePath, TemplateParam param)
{
FileStream templateFile = new FileStream(templatePath, FileMode.Open, FileAccess.Read);//读取模版文件
hssfworkbook = new HSSFWorkbook(templateFile);
ISheet sheet1 = hssfworkbook.GetSheet(string.IsNullOrEmpty(dt.TableName) ? "Sheet1" : dt.TableName);//获取第一张工作表
ICellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
//插入标题
if (param.TitleCell != null)
{
IRow rowTitle = sheet1.GetRow(param.TitleCell.Row);
ICell cellTitle = rowTitle.GetCell(param.TitleCell.Cell);
if (!string.IsNullOrEmpty(param.Title))
{
cellTitle.SetCellValue(param.Title + cellTitle.StringCellValue);
}
}
//插入副标题
if (param.SubTitleCell != null)
{
IRow rowSubTitle = sheet1.GetRow(param.SubTitleCell.Row);
ICell cellSubTitle = rowSubTitle.GetCell(param.SubTitleCell.Cell);
if (!string.IsNullOrEmpty(param.SubTitle))
{
cellSubTitle.SetCellValue(param.SubTitle);
}
}
//插入单位
if (param.ItemUnitCell != null)
{
IRow rowItemUnit = sheet1.GetRow(param.ItemUnitCell.Row);
ICell cellItemUnit = rowItemUnit.GetCell(param.ItemUnitCell.Cell);
if (!string.IsNullOrEmpty(param.ItemUnit))
{
cellItemUnit.SetCellValue(param.ItemUnit);
}
}
int columnCount = param.DataColumn.Length > 0 ? param.DataColumn.Length : dt.Columns.Count;
//插入表格内容
if (param.DataCell != null)
{
if (param.DataTitle)
{
IRow rowDataTitle = sheet1.GetRow(param.DataCell.Row);
ICell cellDataTitle;
for (int m = 0; m < columnCount; m++)
{
cellDataTitle = rowDataTitle.GetCell(m);
cellDataTitle.CellStyle = style;
string title = dt.Columns[param.DataColumn[m]].ColumnName;
if (m == param.SortColumn)
{
title = "排序";
}
cellDataTitle.SetCellValue(title);
}
}
IRow row;
ICell cell;
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
row = sheet1.GetRow(i + param.DataCell.Row + 1) ?? sheet1.CreateRow(i + param.DataCell.Row + 1);
for (int j = 0; j < columnCount; j++)
{
cell = row.GetCell(j) ?? row.CreateCell(j);
cell.CellStyle = style;
object obj = dt.Rows[i][param.DataColumn[j]];
if (j == param.SortColumn)
{
obj = i + 1;
}
if (param.SpecialColumn.Count > 0)
{
if (param.SpecialColumn.ContainsKey(j))
{
obj = param.SpecialColumn[j];
}
}
SetCellValue(cell, obj, dt.Columns[param.DataColumn[j]]);
}
}
}
}
sheet1.ForceFormulaRecalculation = true;
FileStream file = new FileStream(path, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
public string ExportExcelCostQuery()
{
var inputValue = _ntsPage.Request.Form["Inputs"];
var query = Newtonsoft.Json.JsonConvert.DeserializeObject<QueryCost>(inputValue);
var result = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.ICostQueryService>("CostQueryService").GetCostQuery(query);
string objectName = "";
switch(query.ObjType)
{
case AreaType.Area:
objectName = new BLL.BaseLayerObject().GetBaseLayerObjectList(
string.Format(" and layerobjectid={0}", query.ObjectId), " order by LayerObjectID")[0].LayerObjectName;
break;
case AreaType.Liquid:
objectName = new BLL.BaseLayerObject().GetBaseFuncLayerObjectList(
string.Format(" and layerobjectid={0}", query.ObjectId), " order by LayerObjectID")[0].LayerObjectName;
break;
}
var dept = string.Empty;
List<Model.Itemcode> itList = null;
string unit = "";
string itemCodeName = "";
itList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID");
if (itList.Count > 0)
{
dept = itList[0].Unit;
}
unit = query.ItemCode == "00000" ? "T" : dept;
itemCodeName = itList.Count == 0 ? "总能耗" : itList[0].ItemCodeName;
try
{
if (result.FeeTbl != null)
{
if (result.FeeTbl.FeeList.Count > 0)
{
DataTable dt = TableView.CreateCostQueryDataTable(result);
for (var r = 0; r < result.FeeTbl.FeeList.Count; r++)
{
DataRow dr = dt.NewRow();
for (int j = 1; j <= dt.Columns.Count - 1; j++)
{
dr[j] = result.FeeTbl.FeeList[r][j - 1];
}
dt.Rows.Add(dr);
}
string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\";
if (!Directory.Exists(temp_path))
{
Directory.CreateDirectory(temp_path);
string[] files = Directory.GetFiles(temp_path);
foreach (string fn in files)
{
File.Delete(temp_path + fn);
}
}
string save_path = DateTime.Now.Ticks + ".xls";
string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\费用查询.xls";
string time = "";
switch (query.Particle)
{
case Particle.Month:
time = query.StartTime.Year + "-" + query.StartTime.Month + "月";
break;
case Particle.Year:
time = query.StartTime.Year + "年";
break;
}
TemplateParam param = new TemplateParam(objectName + " 费用查询---" + itemCodeName + "能耗",
new CellParam(0, 0), time, new CellParam(3, 0), true,
new CellParam(4, 0));
List<int> columnIndex = new List<int>();
for (int i = 0; i <= dt.Columns.Count - 1; i++)
{
columnIndex.Add(i);
}
param.DataColumn = columnIndex.ToArray();
param.ItemUnit = "（单位：" + unit + "）";
param.ItemUnitCell = new CellParam(3, 5);
param.SortColumn = 0;
dt.TableName = "费用查询";
ExportHelper.ExportExcel(dt, temp_path + save_path, templatePath, param);
return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}";
}
else
{
return "{\"status\":\"error\",\"msg\":\"导出失败：当前无任何数据\"}";
}
}
else
{
return "{\"status\":\"error\",\"msg\":\"导出失败：当前无任何数据\"}";
}
}
catch (Exception ex)
{
return "{\"status\":\"error\",\"msg\":\"导出失败：由于当前无数据或其他原因导致" + ex.Message + "\"}";
}
}
public string ExportQueryLineChart()
{
var inputValue = _ntsPage.Request.Form["Inputs"];
var query = Newtonsoft.Json.JsonConvert.DeserializeObject<BasicQuery>(inputValue);
var result = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IChart>("Chart")
.GetQueryLineChart(query);
try
{
string name = new BLL.BaseLayerObject().GetBaseLayerObjectList(
string.Format(" and layerobjectid={0}", query.ObjectNum), " order by LayerObjectID")[0].LayerObjectName;
string itemUnit = result.Dept[0];
var dept = string.Empty;
var itList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID");
if (itList.Count > 0)
{
dept = itList[0].Unit;
}
string itemName = query.ItemCode == "00000" ? "总能耗" : dept;
var ItemList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID");
string ItemName = query.ItemCode == "00000" ? "总能耗" : ItemList[0].ItemCodeName;
DataTable dt = TableView.CreateBaseDataTable();
if (query.ItemCode != "00000")
{
for (var r = 0; r < result.Enery[itList[0].ItemCodeName].Count; r++)
{
DataRow dr = dt.NewRow();
dr[1] = result.ObjectName[r];
dr[2] = query.ObjectNum;
dr[3] = name;
dr[4] = result.ObjectName[r];
dr[5] = result.ObjectName[r];
dr[6] = itemName;
dr[7] = decimal.Round(decimal.Parse(result.Enery[itList[0].ItemCodeName][r].ToString()), 2).ToString();
dt.Rows.Add(dr);
}
}
else
{
foreach (var i in result.Enery)
{
DataRow drs = dt.NewRow();
drs[1] = i.Key;
drs[2] = "-";
drs[3] = "-";
drs[4] = "-";
drs[5] = "-";
drs[6] = "-";
drs[7] = "-";
dt.Rows.Add(drs);
for (var r = 0; r < result.Enery[i.Key].Count; r++)
{
DataRow dr = dt.NewRow();
dr[1] = result.ObjectName[r];
dr[2] = query.ObjectNum;
dr[3] = name;
dr[4] = result.ObjectName[r];
dr[5] = result.ObjectName[r];
dr[6] = itemName;
dr[7] = decimal.Round(decimal.Parse(result.Enery[i.Key][r].ToString()), 2);
dt.Rows.Add(dr);
}
}
}
if (dt != null)
{
string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\";
if (!Directory.Exists(temp_path))
{
Directory.CreateDirectory(temp_path);
string[] files = Directory.GetFiles(temp_path);
foreach (string fn in files)
{
File.Delete(temp_path + fn);
}
}
string save_path = DateTime.Now.Ticks + ".xls";
string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\能耗查询.xls";
TemplateParam param = new TemplateParam(name + " " + ItemName + " ", new CellParam(0, 0), query.StartTime.ToString("yyyy-MM-dd") + "~" + query.EndTime.ToString("yyyy-MM-dd"), new CellParam(3, 0), false, new CellParam(4, 0));
param.DataColumn = new[] { 0, 3, 1, 7 };
param.ItemUnit = "（单位：" + itemUnit + "）";
param.ItemUnitCell = new CellParam(3, 4);
dt.TableName = "能耗查询统计";
ExportHelper.ExportExcel(dt, temp_path + save_path, templatePath, param);
return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}";
}
else
{
return "{\"status\":\"error\",\"msg\":\"导出失败：当前无任何数据\"}";
}
}
catch (Exception ex)
{
return "{\"status\":\"error\",\"msg\":\"导出失败：由于当前无数据或其他原因导致" + ex.Message + "\"}";
}
}
public string ExportExcelDataRankingNew()
{
var inputValue = _ntsPage.Request.Form["Inputs"];
var query = Newtonsoft.Json.JsonConvert.DeserializeObject<QueryOrderObjects>(inputValue);
string icode = query.ItemCode;
query.QueryType = EnergyAnalyseQueryType.Default;
ResultOrder resultAll = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IQueryEnery>("EneryQuery")
.GetShopOrderNew(query);
query.QueryType = EnergyAnalyseQueryType.UnitArea;
ResultOrder resultArea = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IQueryEnery>("EneryQuery")
.GetShopOrderNew(query);
query.QueryType = EnergyAnalyseQueryType.UnitPerson;
ResultOrder resultPerson = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IQueryEnery>("EneryQuery")
.GetShopOrderNew(query);
var dept = string.Empty;
var itList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID");
if (itList.Count > 0)
{
dept = itList[0].Unit;
}
string unit = icode == "00000" ? "T" : dept;
//switch (query.QueryType)
//{
// case EnergyAnalyseQueryType.Convert2Co2:
// case EnergyAnalyseQueryType.Convert2Coal:
// unit = "T";//标准煤单位
// break;
// case EnergyAnalyseQueryType.Convert2Money:
// unit = "元";//标准煤单位
// break;
//}
string itemCodeName = itList.Count == 0 ? "总能耗" : itList[0].ItemCodeName;
try
{
if (query.AreaIdLst.Count > 0)
{
DataTable dt = TableView.CreateOrderBaseDataTable();
for (var r = 0; r < resultAll.OrderLst.Count; r++)
{
DataRow dr = dt.NewRow();
dr[1] = resultAll.OrderLst[r].Obj;
dr[2] = query.AreaIdLst;
dr[3] = resultAll.OrderLst[r].Obj;
dr[4] = resultAll.OrderLst[r].Obj;
dr[5] = resultAll.OrderLst[r].Obj;
dr[6] = itemCodeName;
dr[7] = decimal.Round(decimal.Parse(resultAll.OrderLst[r].Val.ToString()), 2).ToString();
dr[8] = resultArea.OrderLst == null ? "0" : decimal.Round(decimal.Parse(resultArea.OrderLst[r].Val.ToString()), 2).ToString();
dr[9] = resultPerson.OrderLst == null ? "0" : decimal.Round(decimal.Parse(resultPerson.OrderLst[r].Val.ToString()), 2).ToString();
dt.Rows.Add(dr);
}
string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\";
if (!Directory.Exists(temp_path))
{
Directory.CreateDirectory(temp_path);
string[] files = Directory.GetFiles(temp_path);
foreach (string fn in files)
{
File.Delete(temp_path + fn);
}
}
string save_path = DateTime.Now.Ticks + ".xls";
string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\新能耗排名表.xls";
if (!File.Exists(templatePath))
{
return "{\"status\":\"error\",\"msg\":\"未发现Excel模板文件\"}";
}
TemplateParam param = new TemplateParam(itemCodeName + " ", new CellParam(0, 0), query.StartTime.ToString("yyyy-MM-dd") + "~" + query.EndTime.ToString("yyyy-MM-dd"), new CellParam(3, 0), false, new CellParam(4, 0));
param.DataColumn = new[] { 0, 3, 7, 8, 9 };
param.ItemUnit = "（单位：" + unit + "）";
param.ItemUnitCell = new CellParam(3, 4);
param.SortColumn = 0;
dt.TableName = "能耗排名表";
ExportHelper.ExportExcel(dt, temp_path + save_path, templatePath, param);
return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}";
}
else
{
return "{\"status\":\"error\",\"msg\":\"导出失败：当前无任何数据\"}";
}
}
catch (Exception ex)
{
return "{\"status\":\"error\",\"msg\":\"导出失败：由于当前无数据或其他原因导致" + ex.Message + "\"}";
}
}
public string ExportExcelEnergyAnalyse()
{
var inputValue = _ntsPage.Request.Form["Inputs"];
var query = Newtonsoft.Json.JsonConvert.DeserializeObject<QueryAnalyse>(inputValue);
string icode = query.ItemCode;
EnergyAnalyseQueryType tempQueryType = query.QueryType;
query.QueryType = EnergyAnalyseQueryType.Default;//总能耗
ResultEnergyAnalyse resultAll =
Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IChart>("Chart").GetEnergyAnalyseLineChart(query);
ResultEnergyAnalyse resultArea = null;
ResultEnergyAnalyse resultPerson = null;
if (query.IsDevice == 0)
{//
query.QueryType = EnergyAnalyseQueryType.UnitArea;//单位面积
resultArea = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IChart>("Chart")
.GetEnergyAnalyseLineChart(query);
query.QueryType = EnergyAnalyseQueryType.UnitPerson;//人均
resultPerson = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IChart>("Chart")
.GetEnergyAnalyseLineChart(query);
}
var dept = string.Empty;
List<Model.Itemcode> itList = null;
string unit = "";
string itemCodeName = "";
if (query.IsDevice == 1)
{
var deviceList = new NTS.WEB.BLL.BaseLayerObject().GetDeviceObjectList(string.Format(" and deviceid={0}", query.ObjectId), " order by deviceid");
itList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + deviceList[0].ItemCodeID + "'", " order by ItemcodeID");
if (itList.Count > 0)
{
unit = itList[0].Unit;//单个分类分项单位
itemCodeName = itList[0].ItemCodeName;
}
if (query.ItemCode == "00000")
{//总能耗
unit = "T";//标准煤单位
itemCodeName = "总能耗";
}
}
else
{
itList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID");
if (itList.Count > 0)
{
dept = itList[0].Unit;
}
unit = icode == "00000" ? "T" : dept;
itemCodeName = itList.Count == 0 ? "总能耗" : itList[0].ItemCodeName;
}
//query.QueryType = tempQueryType;
//switch (query.QueryType)
//{
// case EnergyAnalyseQueryType.Convert2Co2:
// case EnergyAnalyseQueryType.Convert2Coal:
// unit = "T";//标准煤单位
// break;
// case EnergyAnalyseQueryType.Convert2Money:
// unit = "元";//标准煤单位
// break;
//}
try
{
if (resultAll.OrderLst.Count > 0)
{
DataTable dt = TableView.CreateOrderBaseDataTable();
for (var r = 0; r < resultAll.OrderLst.Count; r++)
{
DataRow dr = dt.NewRow();
dr[1] = resultAll.OrderLst[r].Tm;
dr[2] = query.ObjectId;
dr[3] = resultAll.OrderLst[r].Obj;
dr[4] = resultAll.OrderLst[r].Tm;
dr[5] = resultAll.OrderLst[r].Tm;
dr[6] = itemCodeName;
dr[7] = decimal.Round(decimal.Parse(resultAll.OrderLst[r].Val.ToString()), 2).ToString();
if (query.IsDevice == 0)
{
dr[8] = resultArea.OrderLst == null ? "0" : decimal.Round(decimal.Parse(resultArea.OrderLst[r].Val.ToString()), 2).ToString();
dr[9] = resultPerson.OrderLst == null ? "0" : decimal.Round(decimal.Parse(resultPerson.OrderLst[r].Val.ToString()), 2).ToString();
}
dt.Rows.Add(dr);
}
string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\";
if (!Directory.Exists(temp_path))
{
Directory.CreateDirectory(temp_path);
string[] files = Directory.GetFiles(temp_path);
foreach (string fn in files)
{
File.Delete(temp_path + fn);
}
}
string save_path = DateTime.Now.Ticks + ".xls";
string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\能耗分析表.xls";
if (query.IsDevice == 1)
{
templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\能耗分析表_设备.xls";
}
TemplateParam param = new TemplateParam(resultAll.OrderLst[0].Obj + " " + itemCodeName + " ", new CellParam(0, 0), query.StartTime.ToString("yyyy-MM-dd") + "~" + query.EndTime.ToString("yyyy-MM-dd"), new CellParam(3, 0), false, new CellParam(4, 0));
param.DataColumn = new[] { 0, 3, 1, 7, 8, 9 };
if (query.IsDevice == 1)
{
param.DataColumn = new[] { 0, 3, 1, 7 };
}
param.ItemUnit = "（单位：" + unit + "）";
param.ItemUnitCell = new CellParam(3, 5);
param.SortColumn = 0;
dt.TableName = "能耗分析表";
ExportHelper.ExportExcel(dt, temp_path + save_path, templatePath, param);
return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}";
}
else
{
return "{\"status\":\"error\",\"msg\":\"导出失败：当前无任何数据\"}";
}
}
catch (Exception ex)
{
return "{\"status\":\"error\",\"msg\":\"导出失败：由于当前无数据或其他原因导致" + ex.Message + "\"}";
}
}
public string ExportExcelDataRanking()
{
var inputValue = _ntsPage.Request.Form["Inputs"];
var query = Newtonsoft.Json.JsonConvert.DeserializeObject<NTS.WEB.DataContact.QueryOrder>(inputValue);
query.PageCurrent = 1;
query.PageSize = 10000;
string icode = query.ItemCode;
var result = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IQueryEnery>("EneryQuery")
.GetShopOrder(query);
query.Particle = "area";
var result2 = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IQueryEnery>("EneryQuery")
.GetShopOrder(query);
var dept = string.Empty;
var itList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID");
if (itList.Count > 0)
{
dept = itList[0].Unit;
}
string itemName = icode == "00000" ? "T" : dept;
var ItemList = new BLL.Itemcode().GetItemcodeList(" and ItemCodeNumber='" + query.ItemCode + "'", " order by ItemcodeID");
string ItemName = query.ItemCode == "00000" ? "总能耗" : ItemList[0].ItemCodeName;
try
{
string unittype = string.IsNullOrEmpty(HttpContext.Current.Request["unittype"]) ? "" : HttpContext.Current.Request["unittype"];//
string type = "0";
string name = "";
switch (type)
{
case "0":
name = "总能耗";
break;
case "1":
name = "面积能耗";
break;
case "2":
name = "人均能耗";
break;
}
if (query.ObjectNum.Count > 0)
{
DataTable dt = TableView.CreateOrderBaseDataTable();
for (var r = 0; r < result.OrderList.Count; r++)
{
DataRow dr = dt.NewRow();
dr[1] = result.OrderList[r].Title;
dr[2] = query.ObjectNum;
dr[3] = result.OrderList[r].Title;
dr[4] = result.OrderList[r].Title;
dr[5] = result.OrderList[r].Title;
dr[6] = itemName;
dr[7] = decimal.Round(decimal.Parse(result.OrderList[r].Energy.ToString()), 2).ToString();
dr[8] = decimal.Round(decimal.Parse(result2.OrderList[r].Energy.ToString()), 2).ToString();
dt.Rows.Add(dr);
}
string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\";
if (!Directory.Exists(temp_path))
{
Directory.CreateDirectory(temp_path);
string[] files = Directory.GetFiles(temp_path);
foreach (string fn in files)
{
File.Delete(temp_path + fn);
}
}
string save_path = DateTime.Now.Ticks + ".xls";
string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\能耗排名表.xls";
TemplateParam param = new TemplateParam(ItemName + " ", new CellParam(0, 0), query.StartTime.ToString("yyyy-MM-dd") + "~" + query.EndTime.ToString("yyyy-MM-dd"), new CellParam(3, 0), false, new CellParam(4, 0));
param.DataColumn = new[] { 0, 3, 7, 8 };
param.ItemUnit = "（单位：" + dept + "）";
param.ItemUnitCell = new CellParam(3, 4);
param.SortColumn = 0;
dt.TableName = "能耗排名表";
ExportHelper.ExportExcel(dt, temp_path + save_path, templatePath, param);
return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}";
}
else
{
return "{\"status\":\"error\",\"msg\":\"导出失败：当前无任何数据\"}";
}
}
catch (Exception ex)
{
return "{\"status\":\"error\",\"msg\":\"导出失败：由于当前无数据或其他原因导致" + ex.Message + "\"}";
}
}
/// <summary>
/// 导出所有能源信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public string ExportItemEnergy(string starttime, string endtime, string objectid, string timeunit, string counttype, string SavePath)
{
try
{
Itemcode itemcodeBll = new Itemcode();
ReportQueryModelNew model = new ReportQueryModelNew();
model.startime = DateTime.Parse(starttime);
model.endtime = DateTime.Parse(endtime);
model.objectid = objectid;
model.timeunit = (ReportStyleNew)(int.Parse(timeunit));
model.objecttype = 0;
model.itemcode = counttype;
string strPath = SavePath; // AppDomain.CurrentDomain.BaseDirectory + "temp_file\\";
string strTemPath = AppDomain.CurrentDomain.BaseDirectory + "ExcelTemplate\\项目能耗查询.xls";
IList<Model.Itemcode> itemcode = itemcodeBll.GetItemcodeList(
" and itemcodenumber = '" + counttype + "'", "");
string strUnit = "mch";
if(itemcode.Count>0)
{
strUnit = itemcode[0].Unit;
}
#region 第2种方法
DataTable dtlist = GetItemcodeData(model);
string name = GetBaseLayerObjectName(model.objectid);
#endregion
if (dtlist != null)
{
string temp_path = strPath; //AppDomain.CurrentDomain.BaseDirectory + "temp_file\\";
if (!Directory.Exists(temp_path))
{
Directory.CreateDirectory(temp_path);
string[] files = Directory.GetFiles(temp_path);
foreach (string fn in files)
{
File.Delete(temp_path + fn);
}
}
string save_path = DateTime.Now.Ticks + ".xls";
string templatePath = strTemPath; // AppDomain.CurrentDomain.BaseDirectory + "template\\区域能耗查询.xls";
string datetime = model.startime.ToString("yyyy-MM-dd") + "~" + (model.timeunit == ReportStyleNew.DayStyle ? model.endtime.ToString("yyyy-MM-dd HH:59:59") : model.endtime.ToString("yyyy-MM-dd"));
TemplateParam param = new TemplateParam(name, new CellParam(0, 0), datetime, new CellParam(3, 0), false, new CellParam(4, 0));
//param.DataColumn = new[] { 0, 3, 6, 7, 8, 9};
param.DataColumn = new[] { 0, 1, 3, 6, 7 };
param.ItemUnit = "能耗值:(" + strUnit + "）";
param.ItemUnitCell = new CellParam(4, 4);
dtlist.TableName = "能耗查询统计";
string strDownFile = temp_path + save_path;
//return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}";
ExportHelper.ExportExcel(dtlist, strDownFile, strTemPath, param);
//return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}";
return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}";
}
else
{
return "{\"status\":\"error\",\"msg\":\"导出失败：当前无任何数据\"}";
}
}
catch (Exception ex)
{
return "{\"status\":\"error\",\"msg\":\"导出失败:没有数据！\"}";
}
}
/// <summary>
/// 导出所有能源信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public string ExportAllEnergy(string starttime, string endtime, string objectid, string timeunit, string SavePath)
{
try
{
ReportQueryModelNew model = new ReportQueryModelNew();
model.startime = DateTime.Parse(starttime);
model.endtime = DateTime.Parse(endtime);
model.objectid = objectid;
model.timeunit = (ReportStyleNew)(int.Parse(timeunit));
model.objecttype = 0;
string strPath = SavePath; // AppDomain.CurrentDomain.BaseDirectory + "temp_file\\";
string strTemPath = AppDomain.CurrentDomain.BaseDirectory + "ExcelTemplate\\区域能耗查询.xls";
#region 第2种方法
DataTable dtItemCode = GetItemCode(" and itemcodenumber in ('01000','02000','03000','04000')");
DataTable dtlist = GetItemcodeDataAll(model, dtItemCode);
string name = GetBaseLayerObjectName(model.objectid);
#endregion
if (dtlist != null)
{
string temp_path = strPath; //AppDomain.CurrentDomain.BaseDirectory + "temp_file\\";
if (!Directory.Exists(temp_path))
{
Directory.CreateDirectory(temp_path);
string[] files = Directory.GetFiles(temp_path);
foreach (string fn in files)
{
File.Delete(temp_path + fn);
}
}
string save_path = DateTime.Now.Ticks + ".xls";
string templatePath = strTemPath; // AppDomain.CurrentDomain.BaseDirectory + "template\\区域能耗查询.xls";
string datetime = model.startime.ToString("yyyy-MM-dd") + "~" + (model.timeunit == ReportStyleNew.DayStyle ? model.endtime.ToString("yyyy-MM-dd HH:59:59") : model.endtime.ToString("yyyy-MM-dd"));
TemplateParam param = new TemplateParam(name, new CellParam(0, 0), datetime, new CellParam(3, 0), false, new CellParam(4, 0));
//param.DataColumn = new[] { 0, 3, 6, 7, 8, 9};
param.DataColumn = new[] { 0, 3, 1, 6, 7, 8, 9, 10 };
dtlist.TableName = "能耗查询统计";
string strDownFile = temp_path + save_path;
ExportHelper.ExportExcel(dtlist, strDownFile, strTemPath, param);
return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}";
}
else
{
return "{\"status\":\"error\",\"msg\":\"导出失败：当前无任何数据\"}";
//return "导出失败：当前无任何数据!";
}
}
catch (Exception ex)
{
return "{\"status\":\"error\",\"msg\":\"导出失败:没有数据\"}";
}
}
public string ExportAlarm()
{
var inputValue = _ntsPage.Request.Form["Inputs"];
var query = Newtonsoft.Json.JsonConvert.DeserializeObject<QueryAlarmNew>(inputValue);
var res =
Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IAlarmService>("AlarmService").GetAlarmList(query, "");
List<NTS.WEB.ResultView.AlarmNewList> dtRef = res.data;
try
{
if ((dtRef != null) && (dtRef.Count > 0))
{
// DataTable dtReport = TableView.CreateAlarmDataTable();
DataTable dtReport = new DataTable();
for (var r = 0; r < dtRef.Count; r++)
{
DataRow dr = dtReport.NewRow();
dr[1] = dtRef[r].Time.ToString();
dr[2] = dtRef[r].Object.ToString();
dr[3] = dtRef[r].Position;
dr[4] = dtRef[r].Info;
dr[5] = dtRef[r].AlarmItem;
dr[6] = dtRef[r].Class;
dr[7] = dtRef[r].AlarmStatus;
dtReport.Rows.Add(dr);
}
string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\";
if (!Directory.Exists(temp_path))
{
Directory.CreateDirectory(temp_path);
string[] files = Directory.GetFiles(temp_path);
foreach (string fn in files)
{
File.Delete(temp_path + fn);
}
}
string save_path = DateTime.Now.Ticks + ".xls";
string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\告警管理表.xls";
TemplateParam param = new TemplateParam("告警管理表", new CellParam(0, 0), "", new CellParam(3, 0), false, new CellParam(4, 0));
//TemplateParam param = new TemplateParam("itemCodeName", new CellParam(1, 1),"",null, false, new CellParam(5, 0));
param.DataColumn = new[] { 0, 1, 2, 3, 4, 5, 6, 7 };
//param.ItemUnit = "（单位：元";
//param.ItemUnitCell = new CellParam(3, 5);
dtReport.TableName = "告警管理表";
ExportHelper.ExportExcel(dtReport, temp_path + save_path, templatePath, param);
return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}";
}
else
{
return "{\"status\":\"error\",\"msg\":\"导出失败：当前无任何数据\"}";
}
}
catch (Exception ex)
{
return "{\"status\":\"error\",\"msg\":\"导出失败：由于当前无数据或其他原因导致" + ex.Message + "\"}";
}
}
public string ExportQueryObjs()
{
var inputValue = _ntsPage.Request["inputs"]; ;
var oderObject = Newtonsoft.Json.JsonConvert.DeserializeObject<QueryOrderObjects>(inputValue);
//var inputValue = _ntsPage.Request.Form["Inputs"];
//var tabId = int.Parse(_ntsPage.Request.Form["TabId"]);
//var query = Newtonsoft.Json.JsonConvert.DeserializeObject<QueryCompare>(inputValue);
//var result = new ResultCompare();
QueryCompare query = new QueryCompare();
query.StartTime = oderObject.StartTime;
query.EndTime = oderObject.EndTime;
query.ObjectNum = oderObject.AreaIdLst;
query.ItemCode = oderObject.ItemCode;
query.ObjType = oderObject.ObjType;
query.Unit = oderObject.Particle;
var result = new ResultCompare();
switch (oderObject.QueryType)
{
case EnergyAnalyseQueryType.Default:
result = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IEnergyContrastService>("EnergyContrastService")
.GetCompareChart(oderObject);
break;
case EnergyAnalyseQueryType.UnitArea:
result = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IEnergyContrastService>("EnergyContrastService")
.GetAreaCompareChart(oderObject);
break;
case EnergyAnalyseQueryType.UnitPerson:
result = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IEnergyContrastService>("EnergyContrastService")
.GetPersonNumCompareChart(oderObject); ;
break;
default:
result = Framework.Common.BaseWcf.CreateChannel<ServiceInterface.IEnergyContrastService>("EnergyContrastService")
.GetCompareChart(oderObject);
break;
}
string strDepName = "";
if (result.Dept.Count > 0)
{
strDepName = result.Dept[0].ToString();
}
else
{
throw new Exception("没有数据信息");
}
switch (oderObject.QueryType)
{
case EnergyAnalyseQueryType.Convert2Co2:
strDepName = "T";
break;
case EnergyAnalyseQueryType.Convert2Coal:
strDepName = "T";
break;
case EnergyAnalyseQueryType.Convert2Money:
strDepName = "元";
break;
}
ResultContrast resultCon = JsonForHightChartsNew.IndexContrastObjsChart(query, "对比柱状图", result.ObjectName.ToArray(), strDepName,
result.Enery);
try
{
if (resultCon.ContrastLst.Count > 0)
{
DataTable dt = TableView.CreateContrastDataTable();
for (var r = 0; r < resultCon.ContrastLst.Count; r++)
{
DataRow dr = dt.NewRow();
dr[1] = resultCon.ContrastLst[r].Tm.ToString();
dr[2] = resultCon.ContrastLst[r].Obj;
dr[3] = resultCon.ContrastLst[r].EneType;
dr[4] = resultCon.ContrastLst[r].Val;
dt.Rows.Add(dr);
}
string temp_path = AppDomain.CurrentDomain.BaseDirectory + "temp_file\\";
if (!Directory.Exists(temp_path))
{
Directory.CreateDirectory(temp_path);
string[] files = Directory.GetFiles(temp_path);
foreach (string fn in files)
{
File.Delete(temp_path + fn);
}
}
string save_path = DateTime.Now.Ticks + ".xls";
string templatePath = AppDomain.CurrentDomain.BaseDirectory + "template\\能耗对比表.xls";
TemplateParam param = new TemplateParam("能耗对比表", new CellParam(0, 0), "", new CellParam(3, 0), false, new CellParam(4, 0));
//TemplateParam param = new TemplateParam("itemCodeName", new CellParam(1, 1),"",null, false, new CellParam(5, 0));
param.DataColumn = new[] { 0, 1, 2, 3, 4 };
param.ItemUnit = "（单位：" + result.Dept[0].ToString() + "）";
param.ItemUnitCell = new CellParam(3, 4);
param.SortColumn = 0;
dt.TableName = "能耗对比表";
ExportHelper.ExportExcel(dt, temp_path + save_path, templatePath, param);
return "{\"status\":\"success\",\"msg\":\"" + "/temp_file/" + save_path + "\"}";
}
else
{
return "{\"status\":\"error\",\"msg\":\"导出失败：当前无任何数据\"}";
}
}
catch (Exception ex)
{
return "{\"status\":\"error\",\"msg\":\"导出失败：由于当前无数据或其他原因导致" + ex.Message + "\"}";
}
}