/// <summary>
/// 当日的用能概况
/// 初始加载：获取用户名查询建筑列表，第一栋建筑对应的分类，第一个分类对应的回路当日的用能概况
/// </summary>
/// <param name="userName">用户名</param>
/// <returns>返回完整的数据：包含建筑列表，能源按钮列表，回路列表，以及第一支路数据</returns>
public CircuitOverviewViewModel GetCircuitOverviewViewModel(string userName)
{
DateTime today = DateTime.Now;
IHomeDbContext homeContext = new HomeDbContext();
List <BuildViewModel> builds = homeContext.GetBuildsByUserName(userName);
string buildId = builds.First().BuildID;
List <EnergyItemDict> energys = reportContext.GetEnergyItemDictByBuild(buildId);
string energyCode = energys.First().EnergyItemCode;
List <CircuitList> circuits = reportContext.GetCircuitListByBIdAndEItemCode(buildId, energyCode);
string circuitId = circuits.First().CircuitId;
List <CircuitValue> loadData = context.GetCircuitLoadValueList(buildId, circuitId, today.ToString());
List <CircuitValue> dayData = context.GetCircuitMomDayValueList(buildId, circuitId, today.ToString());
List <CircuitValue> monthData = context.GetCircuitMomMonthValueList(buildId, circuitId, today.ToString());
List <CircuitValue> last48HoursData = context.GetCircuit48HoursValueList(buildId, circuitId, today.ToString());
List <CircuitValue> last31DayData = context.GetCircuit31DaysValueList(buildId, circuitId, today.ToString());
List <CircuitValue> last12MonthData = context.GetCircuit12MonthValueList(buildId, circuitId, today.ToString());
List <CircuitValue> last3YearData = context.GetCircuit3YearValueList(buildId, circuitId, today.ToString());
CircuitOverviewViewModel circuitOverviewView = new CircuitOverviewViewModel();
circuitOverviewView.Builds = builds;
circuitOverviewView.Energys = energys;
circuitOverviewView.Circuits = circuits;
circuitOverviewView.LoadData = loadData;
circuitOverviewView.MomDayData = dayData;
circuitOverviewView.MomMonthData = monthData;
circuitOverviewView.Last48HoursData = last48HoursData;
circuitOverviewView.Last31DaysData = last31DayData;
circuitOverviewView.Last12MonthData = last12MonthData;
circuitOverviewView.Last3YearData = last3YearData;
return(circuitOverviewView);
}
public DepartmentCompareViewModel GetViewModelByBuild(string userName, string buildId)
{
DateTime today = DateTime.Now;
IHomeDbContext homeContext = new HomeDbContext();
List <BuildViewModel> builds = homeContext.GetBuildsByUserName(userName);
List <EnergyItemDict> energys = reportContext.GetEnergyItemDictByBuild(buildId);
string energyCode;
if (energys.Count > 0)
{
energyCode = energys.First().EnergyItemCode;
}
else
{
energyCode = "";
}
ITreeViewDbContext treeViewDb = new TreeViewDbContext();
List <TreeViewModel> treeView = treeViewDb.GetDepartmentTreeViewList(buildId, energyCode);
string departmentID = treeView.First().Id;
List <EMSValue> CompareValue = context.GetDepartmentCompareValueList(buildId, energyCode, departmentID, today.ToString("yyyy-MM-dd"));
DepartmentCompareViewModel CompareViewModel = new DepartmentCompareViewModel();
CompareViewModel.Builds = builds;
CompareViewModel.Energys = energys;
CompareViewModel.TreeView = treeView;
CompareViewModel.CompareData = CompareValue;
return(CompareViewModel);
}
public EnergyItemReportViewModel GetEnergyItemReportViewModelByBuild(string userName, string buildId)
{
DateTime today = DateTime.Now;
IHomeDbContext homeContext = new HomeDbContext();
List <BuildViewModel> builds = homeContext.GetBuildsByUserName(userName);
List <EnergyItemDict> energys = reportContext.GetEnergyItemDictByBuild(buildId);
IEnergyItemTreeViewDbContext energyItemtreeView = new EnergyItemTreeViewDbContext();
List <TreeViewModel> treeView = energyItemtreeView.GetEnergyItemTreeViewList(buildId);
List <EnergyItemInfo> EnergyItemInfos = energyItemtreeView.GetEnergyItemInfoList(buildId);
string[] formulaIDs = GetEnergyItemCodes(EnergyItemInfos);
List <ReportValue> reportValue = context.GetReportValueList(formulaIDs, today.ToString(), "DD");
EnergyItemReportViewModel energyItemReportView = new EnergyItemReportViewModel();
energyItemReportView.Builds = builds;
energyItemReportView.Energys = energys;
energyItemReportView.TreeView = treeView;
energyItemReportView.Data = reportValue;
energyItemReportView.ReportType = "DD";
return(energyItemReportView);
}
public void TestGetNameByCode()
{
HomeDbContext context = new HomeDbContext();
EnergyItemDict energy = context.GetEnergyItemByCode("01000");
Console.WriteLine(energy.EnergyItemName + ":" + energy.EnergyItemCode);
}
public DepartmentRankViewModel GetViewModelByBuild(string userName, string buildId)
{
DateTime today = DateTime.Now;
DateTime monthBegin = new DateTime(today.Year, today.Month, 1);
DateTime monthEnd = monthBegin.AddMonths(1).AddDays(-1);
IHomeDbContext homeContext = new HomeDbContext();
List <BuildViewModel> builds = homeContext.GetBuildsByUserName(userName);
List <EnergyItemDict> energys = reportContext.GetEnergyItemDictByBuild(buildId);
string energyCode;
if (energys.Count > 0)
{
energyCode = energys.First().EnergyItemCode;
}
else
{
energyCode = "";
}
List <EMSValue> list = context.GetRankList(buildId, monthBegin.ToString("yyyy-MM-dd"), monthEnd.ToString("yyyy-MM-dd"), energyCode);
DepartmentRankViewModel model = new DepartmentRankViewModel();
model.Builds = builds;
model.Energys = energys;
model.RankValues = list;
return(model);
}
public EnergyItemCompareViewModel GetEnergyItemCompareViewModelByBuild(string userName, string buildId)
{
DateTime today = DateTime.Now;
IHomeDbContext homeContext = new HomeDbContext();
List <BuildViewModel> builds = homeContext.GetBuildsByUserName(userName);
List <EnergyItemDict> energys = reportContext.GetEnergyItemDictByBuild(buildId);
//string energyCode = energys.First().EnergyItemCode;
IEnergyItemTreeViewDbContext energyItemtreeView = new EnergyItemTreeViewDbContext();
List <TreeViewModel> treeView = energyItemtreeView.GetEnergyItemTreeViewList(buildId);
string treeId;
if (treeView.Count == 0)
{
treeId = "";
}
else
{
treeId = treeView.First().Id;
}
List <EnergyItemValue> energyItemCompareValue = context.GetEnergyItemCompareValueList(buildId, treeId, today.ToString());
EnergyItemCompareViewModel energyItemCompareView = new EnergyItemCompareViewModel();
energyItemCompareView.Builds = builds;
energyItemCompareView.Energys = energys;
energyItemCompareView.TreeView = treeView;
energyItemCompareView.CompareData = energyItemCompareValue;
return(energyItemCompareView);
}
public MultiRateViewModel GetViewModel(string userName)
{
DateTime today = DateTime.Now;
IHomeDbContext homeContext = new HomeDbContext();
List <BuildViewModel> builds = homeContext.GetBuildsByUserName(userName);
string buildId = builds.First().BuildID;
List <EnergyItemDict> energys = context.GetEnergyItemDictByBuild(buildId);
string energyCode = "01000";
List <EMS.DAL.Entities.CircuitList> circuits = context.GetCircuitListByBIdAndEItemCode(buildId, energyCode);
string[] circuitIds = GetCircuitIds(circuits);
List <TreeViewModel> treeView = GetTreeListViewModel(buildId, energyCode);
List <MultiRateData> data = context.GetReportValueList(buildId, energyCode, "MM", today.ToShortDateString());
MultiRateViewModel circuitReportView = new MultiRateViewModel();
circuitReportView.Builds = builds;
circuitReportView.Energys = energys;
circuitReportView.TreeView = treeView;
circuitReportView.Data = data;
circuitReportView.ReportType = "MM";
return(circuitReportView);
}
public void TestRepository()
{
IHomeDbContext context = new HomeDbContext();
BuildInfo build = context.GetBuildById("000001G001");
Assert.AreEqual(build.BuildId, "000001G001");
}
public void TestGetEnergyItemValues()
{
HomeDbContext context = new HomeDbContext();
List <EnergyItem> list = context.GetEnergyItemValues("000001G001", "2018-01-07");
foreach (EnergyItem item in list)
{
Console.WriteLine("分项名称:{0},分项编号：{1}，分项数据：{2}，建筑编号：{3}", item.EnergyItemName, item.EnergyItemCode, item.Value, item.BuildID);
}
}
public void TestGetBuildsByUserName()
{
HomeDbContext context = new HomeDbContext();
List <BuildViewModel> list = context.GetBuildsByUserName("admin");
foreach (BuildViewModel item in list)
{
Console.WriteLine("建筑ID:{0},建筑名称：{1}", item.BuildID, item.BuildName);
}
}
public void TestGetHourValues()
{
HomeDbContext context = new HomeDbContext();
List <HourValue> list = context.GetHourValues("000001G001", "2018-01-07 12:00:00");
foreach (HourValue item in list)
{
Console.WriteLine("分类名称:{0},时间：{1}，数据：{2}", item.EnergyItemCode, item.ValueTime.ToString("yyyy-MM-dd HH:mm:ss"), item.Value);
}
}
public void TestEnergyClassify()
{
HomeDbContext context = new HomeDbContext();
List <EnergyClassify> list = context.GetEnergyClassifyValues("000001G001", "2018-01-07");
foreach (EnergyClassify item in list)
{
Console.WriteLine("分类名称:{0},当月数据：{1}，当年数据：{2}，单位：{3}，比率：{4}", item.EnergyItemName, item.MonthValue, item.YearValue, item.Unit, item.EnergyRate);
}
}
//private static Hashtable cache = Hashtable.Synchronized(new Hashtable());//缓存
/// <summary>
/// 帮我们返回当前线程内的数据库上下文，如果当前线程内没有上下文，那么创建一个上下文，并保证
/// 上下文是实例在线程内部唯一
/// 在EF4.0以前使用ObjectsContext对象
/// </summary>
/// <returns></returns>
public static DbContext GetCurrentDbContext()
{
//当第二次执行的时候直接取出线程嘈里面的对象
//使用静态state对象作为线程内部唯一的独用的数据槽(一块内存空间)
//数据存储在线程栈中
//线程内共享一个单例
DbContext dbcontext = GetData("DbContext") as DbContext;
//判断线程里面是否有数据
if (dbcontext == null) //线程的数据槽里面没有次上下文
{
dbcontext = new HomeDbContext();
//存储指定对象
SetData("DbContext", dbcontext);
}
return(dbcontext);
}
public EnergyItemOverviewModel GetEnergyItemOverviewViewModelByBuild(string userName, string buildId)
{
DateTime today = DateTime.Now;
IHomeDbContext homeContext = new HomeDbContext();
List <BuildViewModel> builds = homeContext.GetBuildsByUserName(userName);
List <EnergyItemValue> momDay = context.GetEnergyItemMomDayValueList(buildId, today.ToString());
List <EnergyItemValue> rankByMonth = context.GetEnergyItemRankByMonthValueList(buildId, today.ToString());
List <EnergyItemValue> last31DayPieChart = context.GetEnergyItemLast31DayPieChartValueList(buildId, today.ToString());
List <EnergyItemValue> last31Day = context.GetEnergyItemLast31DayValueList(buildId, today.ToString());
EnergyItemOverviewModel energyItemOverviewView = new EnergyItemOverviewModel();
energyItemOverviewView.Builds = builds;
energyItemOverviewView.EnergyItemMomDay = momDay;
energyItemOverviewView.EnergyItemRankByMonth = rankByMonth;
energyItemOverviewView.EnergyItemLast31DayPieChart = last31DayPieChart;
energyItemOverviewView.EnergyItemLast31Day = last31Day;
return(energyItemOverviewView);
}
/// <summary>
/// 部门用能统计报表
/// 初始加载：获取用户名查询建筑列表，第一栋建筑对应的分类，第一个分类对应的所有部门的用能天报表
/// </summary>
/// <param name="userName">用户名</param>
/// <returns>返回完整的数据：包含建筑列表，能源按钮列表，部门列表，以及用能数据天报表</returns>
public DepartmentReportViewModel GetViewModel(string userName)
{
DateTime today = DateTime.Now;
IHomeDbContext homeContext = new HomeDbContext();
List <BuildViewModel> builds = homeContext.GetBuildsByUserName(userName);
string buildId = builds.First().BuildID;
List <EnergyItemDict> energys = reportContext.GetEnergyItemDictByBuild(buildId);
//增加分类能耗过滤信息
//修改方法参数，修改SQL语句
string energyCode;
if (energys.Count > 0)
{
energyCode = energys.First().EnergyItemCode;
}
else
{
energyCode = "";
}
ITreeViewDbContext treeViewDb = new TreeViewDbContext();
List <TreeViewModel> treeView = treeViewDb.GetDepartmentTreeViewList(buildId, energyCode);
string[] departmentIDs = treeViewDb.GetDepartmentIDs(buildId, energyCode);
List <ReportValue> reportValue = context.GetReportValueList(energyCode, departmentIDs, today.ToString("yyyy-MM-dd 00:00:00"), "DD");
DepartmentReportViewModel reportView = new DepartmentReportViewModel();
reportView.Builds = builds;
reportView.Energys = energys;
reportView.TreeView = treeView;
reportView.Data = reportValue;
reportView.ReportType = "DD";
return(reportView);
}
public NoWorkDayViewModel GetViewModel(string userName)
{
DateTime today = DateTime.Now;
string beginDate = today.ToString("yyyy-MM-01 00:00:00");
string endDate = today.ToString("yyyy-MM-dd HH:mm:00");
string energyCode = "";
IHomeDbContext homeContext = new HomeDbContext();
List <BuildViewModel> builds = homeContext.GetBuildsByUserName(userName);
string buildID = "";
if (builds.Count > 0)
{
buildID = builds.First().BuildID;
}
List <EnergyItemDict> energys = tvcontext.GetEnergyItemDictByBuild(buildID);
if (energys.Count > 0)
{
energyCode = energys.First().EnergyItemCode;
}
List <TreeViewModel> treeView = tvcontext.GetCircuitTreeListViewModel(buildID, energyCode);
List <NoWorkDay> data = context.GetCircuitData(buildID, energyCode, beginDate, endDate);
NoWorkDayViewModel viewModel = new NoWorkDayViewModel();
viewModel.Builds = builds;
viewModel.Energys = energys;
viewModel.TreeView = treeView;
viewModel.Data = data;
return(viewModel);
}
public CircuitCompareViewModel GetCircuitCompareVMWithBuild(string userName, string buildId)
{
DateTime today = DateTime.Now;
IHomeDbContext homeContext = new HomeDbContext();
List <BuildViewModel> builds = homeContext.GetBuildsByUserName(userName);
List <EnergyItemDict> energys = reportContext.GetEnergyItemDictByBuild(buildId);
string energyCode = energys.First().EnergyItemCode;
List <TreeViewModel> treeView = GetTreeListViewModel(buildId, energyCode);
List <EMS.DAL.Entities.CircuitList> circuits = reportContext.GetCircuitListByBIdAndEItemCode(buildId, energyCode);
string circuitId = circuits.First().CircuitId;
List <CircuitValue> compareData = context.GetCircuitCompareValueList(buildId, circuitId, today.ToString());
CircuitCompareViewModel circuitCompareView = new CircuitCompareViewModel();
circuitCompareView.Builds = builds;
circuitCompareView.Energys = energys;
circuitCompareView.TreeView = treeView;
circuitCompareView.CompareData = compareData;
return(circuitCompareView);
}
public StudentRepository(HomeDbContext context) : base(context)
{
}
public Excel ExportReportToExcel(string basePath, string buildId, string energyCode, string[] regionIds, string date, string type)
{
type = type.ToUpper();
string templatePath = basePath + "/DayReportTemplate.xls";
string reportType = " 日报(" + date + ")";
if (type == "MM")
{
reportType = " 月报(" + date + ")";
templatePath = basePath + "/MonthReportTemplate.xls";
}
else if (type == "YY")
{
reportType = " 年报(" + date + ")";
templatePath = basePath + "/YearReportTemplate.xls";
}
else if (type == "DD")
{
reportType = " 日报(" + date + ")";
templatePath = basePath + "/DayReportTemplate.xls";
}
//根据模板生成Excel
FileStream file = new FileStream(templatePath, FileMode.Open, FileAccess.Read);
HSSFWorkbook book = new HSSFWorkbook(file);
HSSFSheet sheet = (HSSFSheet)book.GetSheet("Sheet1");
//创建单元格格式：数字型单元格默认取小数点后两位
ICellStyle style = book.CreateCellStyle();
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
//获取当前建筑名
IHomeDbContext homeContext = new HomeDbContext();
BuildInfo build = homeContext.GetBuildById(buildId);
//获取能源类型
EnergyItemDict energyItem = reportContext.GetUnitByEnergyCode(energyCode);
//string[] circuitIds = circuits.Split(',');
List <ReportValue> data = context.GetReportValueList(energyCode, regionIds, date, type);
var totalList = data.GroupBy(d => new { d.Time })
.Select(group => new ReportValue
{
Id = "Total",
Name = "总计",
Time = group.Key.Time,
Value = group.Sum(p => p.Value)
}).ToList();
data.AddRange(totalList);
//设置Excel标题
sheet.GetRow(0).GetCell(0).SetCellValue(build.BuildName + " 区域用能 " + reportType);
//设置Excel中报表的能源类别
sheet.GetRow(1).GetCell(1).SetCellValue(energyItem.EnergyItemName);
//设置Excel中报表的能源单位
sheet.GetRow(1).GetCell(5).SetCellValue(energyItem.EnergyItemUnit);
//根据传入circuitIds填充excel
int rowId = 0;
for (int i = 0; i < regionIds.Length + 1; i++)
{
//使用lamda表达式筛选List中Id与传入的Id对应的仪表：一次填充一行Excel
List <ReportValue> current;
if (i == regionIds.Length)
{
current = data.FindAll(p => p.Id == "Total");
}
else
{
current = data.FindAll(p => p.Id == regionIds[i]);
}
if (current.Count > 0)
{
IRow row = sheet.CreateRow(rowId + 3);
row.CreateCell(0).SetCellValue(current[0].Name);
decimal total = 0;
switch (type)
{
case "DD":
foreach (var item in current)
{ //遍历筛选出列表中当前回路中每个小时的数据，填充到Excel中
//当前行的第一列已经填充回路名称，根据时间设置向右偏移一位，将对应数据写入到当前单元格，并设置格式
if (item.Time != null)
{
DateTime time = Convert.ToDateTime(item.Time);
row.CreateCell(time.Hour + 1).SetCellValue((double)item.Value);
row.GetCell(time.Hour + 1).CellStyle = style;
total += Convert.ToDecimal(item.Value);
}
}
row.CreateCell(25).SetCellValue((double)total);
row.GetCell(25).CellStyle = style;
break;
case "MM":
foreach (var item in current)
{
if (item.Time != null)
{
DateTime time = Convert.ToDateTime(item.Time);
row.CreateCell(time.Day).SetCellValue((double)item.Value);
row.GetCell(time.Day).CellStyle = style;
total += Convert.ToDecimal(item.Value);
}
}
row.CreateCell(32).SetCellValue((double)total);
row.GetCell(32).CellStyle = style;
break;
case "YY":
foreach (var item in current)
{
if (item.Time != null)
{
DateTime time = Convert.ToDateTime(item.Time);
row.CreateCell(time.Month).SetCellValue((double)item.Value);
row.GetCell(time.Month).CellStyle = style;
total += Convert.ToDecimal(item.Value);
}
}
row.CreateCell(13).SetCellValue((double)total);
row.GetCell(13).CellStyle = style;
break;
}
rowId++;
}
}
Excel excel = new Excel();
excel.Name = build.BuildName + "区域用能" + reportType + "[" + Guid.NewGuid().ToString("N") + "].xls";
using (MemoryStream stream = new MemoryStream())
{
book.Write(stream);
stream.Seek(0, SeekOrigin.Begin);
excel.Data = stream.ToArray();
}
return(excel);
}
public Repository(HomeDbContext context)
{
Context = context;
Table = Context.Set <T>();
}
public AddressDao(HomeDbContext context)
{
Context = context;
}
public SectionRepository(HomeDbContext context) : base(context)
{
}
public ApplicationDao(HomeDbContext context)
{
Context = context;
}
public Uow(HomeDbContext dbContext)
{
_context = dbContext;
}
