public IActionResult ViewReportOnMotnh()
{
ViewBag.Data = DateTime.Now;
ReportMonthViewModel raport = _reportService.GetViewModelForSelectedMonth(_context, DateTime.Now);
return(View(raport));
}
public ActionResult ReportMonth(ReportMonthViewModel model, FormCollection collection)
{
ViewBag.ReportList = "first active";
DateTime start = model.DateStart;
model.DateStart = start.AddYears(-543);
DateTime end = model.DateEnd;
model.DateEnd = end.AddYears(-543);
model.Report = new ReportMonth();
model.Report.Repairs = ReportManager.ReportRepairMonth(model.DateStart, model.DateEnd);
model.Insurances = InsuranceManager.GetAll().OrderBy(m => m.sInsuranceName).ToList();
ViewBag.ReportHeader = String.Format("สรุปบริการประจำเดือน {0}", DateExtension.DateThaiFormat2(model.DateStart));
return View(model);
}
public IActionResult ViewReportOnMotnh(string datepicker, string submitButon)
{
DateTime data = Convert.ToDateTime(datepicker);
ViewBag.Data = data;
ReportMonthViewModel raport = _reportService.GetViewModelForSelectedMonth(_context, data);
if (submitButon == "Show data") // If it is click Show Data Button Return View with data
{
return(View(raport));
}
if (submitButon == "Export To Excel") // Else if it is clicked export to excel return an excel file report
{
List <IndexModel> listaIndex = _context.Indexes.Where(elem => elem.DataOra.Month == data.Month && elem.DataOra.Year == data.Year).ToList();
return(_reportService.GetExcelFileFromReport(listaIndex, raport));
}
return(View(raport));
}
// GET: Reporting/Month/{year}/{month}
public async Task <ActionResult> Month(int year, int month)
{
using (var context = await GetCrmContext())
{
var query = new ReportQuery(context);
var date = new DateTime(year, month, 1);
var viewModel = new ReportMonthViewModel
{
Title = date.ToShortDateString(),
Breadcrumb = new Breadcrumb(),
Accounts = query.GetAccounts(date),
Year = year,
Month = month
};
return(View(viewModel));
}
}
//สรุปรายการซ่อมประจำเดือน
public ActionResult ReportMonth()
{
ViewBag.ReportList = "first active";
ReportMonthViewModel model = new ReportMonthViewModel();
model.Report = new ReportMonth();
DateTime start = DateExtension.FirstDayOfMonthFromDateTime(DateTime.Now);
model.DateStart = Convert.ToDateTime(start.AddYears(543).ToString("MM/dd/yyyy"));
DateTime end = DateExtension.LastDayOfMonthFromDateTime(DateTime.Now);
model.DateEnd = Convert.ToDateTime(end.AddYears(543).ToString("MM/dd/yyyy"));
model.Report.Repairs = ReportManager.ReportRepairMonth(start, end);
//model.Report.Claims = ReportManager.ReportClaim(start, end);
model.Insurances = InsuranceManager.GetAll().OrderBy(m => m.sInsuranceName).ToList();
ViewBag.ReportHeader = String.Format("สรุปบริการประจำเดือน {0}", DateExtension.DateThaiFormat2(start));
return View(model);
}
// Get Excel File From Report
public FileStreamResult GetExcelFileFromReport(List <IndexModel> listOfIndexes, ReportMonthViewModel report)
{
var controler = new ProbaController(); // Initialize new controller to use File Method for Excel File
var stream = new MemoryStream();
using (var pck = new ExcelPackage(stream))
{
// EXCEL WORKSHEET FOR INDEXES
ExcelWorksheet wsIndex = pck.Workbook.Worksheets.Add($"{report.An}.{report.Luna} Indexe orare");
wsIndex.Cells["A1:Z1"].Style.Font.Bold = true;
wsIndex.Cells["A1"].Value = "Clock";
wsIndex.Cells["B1"].Value = "EDIS status";
wsIndex.Cells["C1"].Value = "Energy +A";
wsIndex.Cells["D1"].Value = "Energy -A";
wsIndex.Cells["E1"].Value = "Energy +Ri";
wsIndex.Cells["F1"].Value = "Energy +Rc";
wsIndex.Cells["G1"].Value = "Energy -Ri";
wsIndex.Cells["H1"].Value = "Energy -Rc";
int rowStart = 2;
foreach (var elem in listOfIndexes)
{
wsIndex.Cells[string.Format("A{0}", rowStart)].Value = elem.DataOra;
wsIndex.Cells[string.Format("B{0}", rowStart)].Value = elem.EdisStatus;
wsIndex.Cells[string.Format("C{0}", rowStart)].Value = elem.IndexEnergyPlusA;
wsIndex.Cells[string.Format("D{0}", rowStart)].Value = elem.IndexEnergyMinusA;
wsIndex.Cells[string.Format("E{0}", rowStart)].Value = elem.IndexEnergyPlusRi;
wsIndex.Cells[string.Format("F{0}", rowStart)].Value = elem.IndexEnergyPlusRc;
wsIndex.Cells[string.Format("G{0}", rowStart)].Value = elem.IndexEnergyMinusRi;
wsIndex.Cells[string.Format("H{0}", rowStart)].Value = elem.IndexEnergyMinusRc;
// Set background Yellow color for fix Hour when it starts a new day
if (elem.DataOra.Hour == 0)
{
wsIndex.Cells[string.Format("A{0}:Z{0}", rowStart)].Style.Fill.PatternType = ExcelFillStyle.Solid;
wsIndex.Cells[string.Format("A{0}:Z{0}", rowStart)].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Yellow);
}
rowStart++;
}
wsIndex.Cells["A:AZ"].AutoFitColumns();
// Another SHEET REport
// EXCEL WORKSHEET FOR A+ Energii orare
ExcelWorksheet wsEnergyPlusA = pck.Workbook.Worksheets.Add($"{report.An}.{report.Luna} A+ energii orare");
// Set Head of table header Style
wsEnergyPlusA.Cells["A1:AG1"].Style.Font.Bold = true;
// Set Values of Table Header
wsEnergyPlusA.Cells["A1"].Value = "Ora/ Zi";
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyPlusA.Cells[$"{GetCharStringsFromNumber(65 + i)}1"].Value = i;
}
// Set Values from report in excel file
for (int j = 0; j < 24; j++)
{
wsEnergyPlusA.Cells[$"A{j + 2}"].Style.Font.Bold = true;
wsEnergyPlusA.Cells[$"A{j + 2}"].Value = j + 1;
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyPlusA.Cells[$"{GetCharStringsFromNumber(65 + i)}{j + 2}"].Value = report.TabeleValori[0].Valori[i, j];
}
}
// Show Values per each day of mounth
wsEnergyPlusA.Cells[$"A{27}"].Value = " Total: ";
for (int i = 1; i <= (report.ZileInLuna); i++)
{
wsEnergyPlusA.Cells[$"{GetCharStringsFromNumber(65 + i)}27"].Value = report.TabeleValori[0].TotalperZi[i];
}
wsEnergyPlusA.Cells[$"A27:AG27"].Style.Font.Bold = true;
wsEnergyPlusA.Cells[$"{GetCharStringsFromNumber(66 + report.ZileInLuna)}27"].Value = $"{report.TabeleValori[0].TotalperLuna} kWh";
wsEnergyPlusA.Cells["A:AZ"].AutoFitColumns();
// Another SHEET REport
// EXCEL WORKSHEET FOR Ri+ Energii orare
ExcelWorksheet wsEnergyPlusRi = pck.Workbook.Worksheets.Add($"{report.An}.{report.Luna} Ri+ energii orare");
// Set Head of table header Style
wsEnergyPlusRi.Cells["A1:AG1"].Style.Font.Bold = true;
// Set Values of Table Header
wsEnergyPlusRi.Cells["A1"].Value = "Ora/ Zi";
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyPlusRi.Cells[$"{GetCharStringsFromNumber(65 + i)}1"].Value = i;
}
// Set Values from report in excel file
for (int j = 0; j < 24; j++)
{
wsEnergyPlusRi.Cells[$"A{j + 2}"].Style.Font.Bold = true;
wsEnergyPlusRi.Cells[$"A{j + 2}"].Value = j + 1;
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyPlusRi.Cells[$"{GetCharStringsFromNumber(65 + i)}{j + 2}"].Value = report.TabeleValori[1].Valori[i, j];
}
}
// Show Values per each day of mounth
wsEnergyPlusRi.Cells[$"A{27}"].Value = " Total: ";
for (int i = 1; i <= (report.ZileInLuna); i++)
{
wsEnergyPlusRi.Cells[$"{GetCharStringsFromNumber(65 + i)}27"].Value = report.TabeleValori[1].TotalperZi[i];
}
wsEnergyPlusRi.Cells[$"A27:AG27"].Style.Font.Bold = true;
wsEnergyPlusRi.Cells[$"{GetCharStringsFromNumber(66 + report.ZileInLuna)}27"].Value = $"{report.TabeleValori[1].TotalperLuna} kVArh";
wsEnergyPlusRi.Cells["A:AZ"].AutoFitColumns();
// Another SHEET REport
// EXCEL WORKSHEET FOR Rc- Energii orare
ExcelWorksheet wsEnergyMinusRc = pck.Workbook.Worksheets.Add($"{report.An}.{report.Luna} Rc- energii orare");
// Set Head of table header Style
wsEnergyMinusRc.Cells["A1:AG1"].Style.Font.Bold = true;
// Set Values of Table Header
wsEnergyMinusRc.Cells["A1"].Value = "Ora/ Zi";
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyMinusRc.Cells[$"{GetCharStringsFromNumber(65 + i)}1"].Value = i;
}
// Set Values from report in excel file
for (int j = 0; j < 24; j++)
{
wsEnergyMinusRc.Cells[$"A{j + 2}"].Style.Font.Bold = true;
wsEnergyMinusRc.Cells[$"A{j + 2}"].Value = j + 1;
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyMinusRc.Cells[$"{GetCharStringsFromNumber(65 + i)}{j + 2}"].Value = report.TabeleValori[2].Valori[i, j];
}
}
// Show Values per each day of mounth
wsEnergyMinusRc.Cells[$"A{27}"].Value = " Total: ";
for (int i = 1; i <= (report.ZileInLuna); i++)
{
wsEnergyMinusRc.Cells[$"{GetCharStringsFromNumber(65 + i)}27"].Value = report.TabeleValori[2].TotalperZi[i];
}
wsEnergyMinusRc.Cells[$"A27:AG27"].Style.Font.Bold = true;
wsEnergyMinusRc.Cells[$"{GetCharStringsFromNumber(66 + report.ZileInLuna)}27"].Value = $"{report.TabeleValori[2].TotalperLuna} kVArh";
wsEnergyMinusRc.Cells["A:AZ"].AutoFitColumns();
// Another SHEET REport
// EXCEL WORKSHEET FOR Cos Fi Inductiv Energii orare
ExcelWorksheet wsCosFiInductiv = pck.Workbook.Worksheets.Add($"{report.An}.{report.Luna} Cos Fi inductiv");
// Set Head of table header Style
wsCosFiInductiv.Cells["A1:AG1"].Style.Font.Bold = true;
// Set Values of Table Header
wsCosFiInductiv.Cells["A1"].Value = "Ora/ Zi";
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsCosFiInductiv.Cells[$"{GetCharStringsFromNumber(65 + i)}1"].Value = i;
}
// Set Values from report in excel file
for (int j = 0; j < 24; j++)
{
wsCosFiInductiv.Cells[$"A{j + 2}"].Style.Font.Bold = true;
wsCosFiInductiv.Cells[$"A{j + 2}"].Value = j + 1;
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsCosFiInductiv.Cells[$"{GetCharStringsFromNumber(65 + i)}{j + 2}"].Value = report.TabeleValori[3].Valori[i, j];
}
}
// Show Values per each day of mounth
wsCosFiInductiv.Cells[$"A{27}"].Value = " Total: ";
for (int i = 1; i <= (report.ZileInLuna); i++)
{
wsCosFiInductiv.Cells[$"{GetCharStringsFromNumber(65 + i)}27"].Value = report.TabeleValori[3].TotalperZi[i];
}
wsCosFiInductiv.Cells[$"A27:AG27"].Style.Font.Bold = true;
wsCosFiInductiv.Cells[$"{GetCharStringsFromNumber(66 + report.ZileInLuna)}27"].Value = $"{report.TabeleValori[3].TotalperLuna}";
wsCosFiInductiv.Cells["A:AZ"].AutoFitColumns();
// Another SHEET REport
// EXCEL WORKSHEET FOR Cos Fi Capacitiv Energii orare
ExcelWorksheet wsCosFiCapacitiv = pck.Workbook.Worksheets.Add($"{report.An}.{report.Luna} Cos Fi capacitiv");
// Set Head of table header Style
wsCosFiCapacitiv.Cells["A1:AG1"].Style.Font.Bold = true;
// Set Values of Table Header
wsCosFiCapacitiv.Cells["A1"].Value = "Ora/ Zi";
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsCosFiCapacitiv.Cells[$"{GetCharStringsFromNumber(65 + i)}1"].Value = i;
}
// Set Values from report in excel file
for (int j = 0; j < 24; j++)
{
wsCosFiCapacitiv.Cells[$"A{j + 2}"].Style.Font.Bold = true;
wsCosFiCapacitiv.Cells[$"A{j + 2}"].Value = j + 1;
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsCosFiCapacitiv.Cells[$"{GetCharStringsFromNumber(65 + i)}{j + 2}"].Value = report.TabeleValori[4].Valori[i, j];
}
}
// Show Values per each day of mounth
wsCosFiCapacitiv.Cells[$"A{27}"].Value = " Total: ";
for (int i = 1; i <= (report.ZileInLuna); i++)
{
wsCosFiCapacitiv.Cells[$"{GetCharStringsFromNumber(65 + i)}27"].Value = report.TabeleValori[4].TotalperZi[i];
}
wsCosFiCapacitiv.Cells[$"A27:AG27"].Style.Font.Bold = true;
wsCosFiCapacitiv.Cells[$"{GetCharStringsFromNumber(66 + report.ZileInLuna)}27"].Value = $"{report.TabeleValori[4].TotalperLuna}";
wsCosFiCapacitiv.Cells["A:AZ"].AutoFitColumns();
// Another SHEET REport
// EXCEL WORKSHEET FOR Ri+ Energii orare Facturare
ExcelWorksheet wsEnergyEnergiiFacturare = pck.Workbook.Worksheets.Add($"{report.An}.{report.Luna} Ri+ energii orare fact");
// Set Head of table header Style
wsEnergyEnergiiFacturare.Cells["A1:AG1"].Style.Font.Bold = true;
// Set Values of Table Header
wsEnergyEnergiiFacturare.Cells["A1"].Value = "Ora/ Zi";
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyEnergiiFacturare.Cells[$"{GetCharStringsFromNumber(65 + i)}1"].Value = i;
}
// Set Values from report in excel file
for (int j = 0; j < 24; j++)
{
wsEnergyEnergiiFacturare.Cells[$"A{j + 2}"].Style.Font.Bold = true;
wsEnergyEnergiiFacturare.Cells[$"A{j + 2}"].Value = j + 1;
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyEnergiiFacturare.Cells[$"{GetCharStringsFromNumber(65 + i)}{j + 2}"].Value = report.TabeleValori[5].Valori[i, j];
}
}
// Show Values per each day of mounth
wsEnergyEnergiiFacturare.Cells[$"A{27}"].Value = " Total: ";
for (int i = 1; i <= (report.ZileInLuna); i++)
{
wsEnergyEnergiiFacturare.Cells[$"{GetCharStringsFromNumber(65 + i)}27"].Value = report.TabeleValori[5].TotalperZi[i];
}
wsEnergyEnergiiFacturare.Cells[$"A27:AG27"].Style.Font.Bold = true;
wsEnergyEnergiiFacturare.Cells[$"{GetCharStringsFromNumber(66 + report.ZileInLuna)}27"].Value = $"{report.TabeleValori[5].TotalperLuna} kVArh";
wsEnergyEnergiiFacturare.Cells["A:AZ"].AutoFitColumns();
// Final part Excel File
pck.Save();
}
stream.Position = 0;
string excelName = $"{report.An}.{report.Luna}_RaportEnergy.xlsx";
return(controler.File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName));
}
// Get Values For Selected Month
public ReportMonthViewModel GetViewModelForSelectedMonth(ReportDbContext context, DateTime data)
{
ReportMonthViewModel raport = GetNewReporthValoriVieModelByDate(data);
for (int i = 1; i <= raport.ZileInLuna; i++)
{
for (int j = 0; j < 24; j++)
{
//raport.TabeleValori[0].Valori[i, j]
var variabile = context.Indexes.Where(elem =>
elem.DataOra.Year == data.Year &&
elem.DataOra.Month == data.Month &&
elem.DataOra.Day == i &&
elem.DataOra.Hour == j
).ToList();
if (variabile == null)
{
continue;
}
var variabila = variabile.Select(x => new ElemSelectieModel
{
EnergyPlusA = x.ValueEnergyPlusA,
EnergyPlusRi = x.ValueEnergyPlusRi,
EnergyMinusRc = x.ValueEnergyMinusRc,
CosFiInductiv = x.CosFiInductiv,
CosFiCapacitiv = x.CosFiCapacitiv,
RiPlusEnergiiOrare = x.EnergiiOrareFacturareRiPlus
}
).FirstOrDefault();
if (variabila == null)
{
continue;
}
raport.TabeleValori[0].Valori[i, j] = variabila.EnergyPlusA;
raport.TabeleValori[1].Valori[i, j] = variabila.EnergyPlusRi;
raport.TabeleValori[2].Valori[i, j] = variabila.EnergyMinusRc;
raport.TabeleValori[3].Valori[i, j] = variabila.CosFiInductiv;
raport.TabeleValori[4].Valori[i, j] = variabila.CosFiCapacitiv;
raport.TabeleValori[5].Valori[i, j] = variabila.RiPlusEnergiiOrare;
}
for (int j = 0; j < 24; j++)
{
raport.TabeleValori[0].TotalperZi[i] += raport.TabeleValori[0].Valori[i, j];
raport.TabeleValori[1].TotalperZi[i] += raport.TabeleValori[1].Valori[i, j];
raport.TabeleValori[2].TotalperZi[i] += raport.TabeleValori[2].Valori[i, j];
raport.TabeleValori[5].TotalperZi[i] += raport.TabeleValori[5].Valori[i, j];
}
// For cos Fi Total for each day
raport.TabeleValori[3].TotalperZi[i] = GetCalculateValueCosFiInductiv(new IndexModel {
ValueEnergyPlusA = raport.TabeleValori[0].TotalperZi[i], ValueEnergyPlusRi = raport.TabeleValori[1].TotalperZi[i]
});
raport.TabeleValori[4].TotalperZi[i] = GetCalculateValueCosFiCapacitiv(new IndexModel {
ValueEnergyPlusA = raport.TabeleValori[0].TotalperZi[i], ValueEnergyMinusRc = raport.TabeleValori[2].TotalperZi[i]
});
raport.TabeleValori[0].TotalperLuna += raport.TabeleValori[0].TotalperZi[i];
raport.TabeleValori[1].TotalperLuna += raport.TabeleValori[1].TotalperZi[i];
raport.TabeleValori[2].TotalperLuna += raport.TabeleValori[2].TotalperZi[i];
raport.TabeleValori[5].TotalperLuna += raport.TabeleValori[5].TotalperZi[i];
}
// For cos Fi afisam total per motnh
raport.TabeleValori[3].TotalperLuna = GetCalculateValueCosFiInductiv(new IndexModel {
ValueEnergyPlusA = raport.TabeleValori[0].TotalperLuna, ValueEnergyPlusRi = raport.TabeleValori[1].TotalperLuna
});
raport.TabeleValori[4].TotalperLuna = GetCalculateValueCosFiCapacitiv(new IndexModel {
ValueEnergyPlusA = raport.TabeleValori[0].TotalperLuna, ValueEnergyMinusRc = raport.TabeleValori[2].TotalperLuna
});
return(raport);
}
public ReportMonthWindow(bool check, USER User)
{
InitializeComponent();
this.DataContext = (reportMonthViewModel = new ReportMonthViewModel(check, User));
}
