public JsonResult HeatMapInvProductionData(int stationId, string slctDate)
{
InverterProductionView mdl = new InverterProductionView();
try
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("tr-TR");
DateTime datePicker = DateTime.Now;
string[] year_month = slctDate.Split('-');
int _year = Convert.ToInt32(year_month[0]);
int _month = Convert.ToInt32(year_month[1]);
datePicker = new DateTime(_year, _month, 1);
NUMBER_FORMAT_DTO convertFormat = ConvertNumberFormatInvHeatMapMonthly(datePicker.ToString());
long _numberDateBegin = convertFormat._begin;
long _numberDateEnd = convertFormat._end;
MontlyInverterProduction mi = new MontlyInverterProduction();
var inverterDetail = DB.Inverters.Where(w => w.STATION_ID == stationId && w.IS_DELETED == false).ToList();
var tblInv = inverterDetail.OrderBy(o => int.Parse(o.NAME.Split(' ')[1])).ToList();
//var adoInv = DB.Database.SqlQuery<TBL_INV_OZET>("select * from TBL_INV_OZET WHERE STATION_ID=370 AND TARIH_NUMBER>='20190400000000' AND TARIH_NUMBER<='20190432000000'").ToList();
//var tblInvTable = DB.InvSums.Where(w => w.STATION_ID == stationId && w.TARIH_NUMBER >= _numberDateBegin && w.TARIH_NUMBER <= _numberDateEnd && (Math.Round(((decimal)w.TARIH_NUMBER.Value%1000000)/10000))>7).ToList();
var listInvOzet = DB.InvSums.Where(ww => ww.STATION_ID == stationId && ww.TARIH_NUMBER >= _numberDateBegin && ww.TARIH_NUMBER <= _numberDateEnd && ww.Tarih.Value.Hour > 7) // int.Parse(ww.TARIH_NUMBER.ToString().Substring(8, 2)) > 07 //(Math.Round(((decimal)ww.TARIH_NUMBER.Value % 1000000) / 10000)) > 7
.GroupBy(grp => new { grp.Tarih.Value.Day, grp.Inv_Id })
.Select(s => new InvMonthlyValue {
day = s.Key.Day, inv = s.Key.Inv_Id, energy = (float)Math.Round((s.Max(m => m.Gunluk_Enerji.Value)) / 1000, 2)
})
.ToList();
//mi.listInvValue = DB.InvSums.Join(DB.Inverters.Where(ww => ww.STATION_ID == stationId),
// invs => invs.Inv_Id, inv => inv.ID, (inv, invs) => inv)
// .Where(w => w.STATION_ID == stationId && w.TARIH_NUMBER >= 20190400000000 && w.TARIH_NUMBER <= 20190432000000)
// .GroupBy(grp => new { grp.Tarih.Value.Day, grp.Inv_Id })
// .Select(s => new InvMonthlyValue { day = s.Key.Day, inv = s.Key.Inv_Id, energy = (float)Math.Round((s.Max(m => m.Gunluk_Enerji.Value)) / 1000, 1) })
// .OrderBy(o => o.day).ThenBy(t => t.inv).ToList();
var MonthDaysCount = DateTime.DaysInMonth(_year, _month);
List <InvMonthlyValue> orderByList = new List <InvMonthlyValue>();
for (int i = 1; i <= MonthDaysCount; i++)
{
foreach (var inv in tblInv)
{
var reg = listInvOzet.Where(w => w.day == i && w.inv == inv.ID).FirstOrDefault();
if (reg == null)
{
orderByList.Add(new InvMonthlyValue {
day = i, inv = inv.ID, energy = null
});
}
else
{
orderByList.Add(reg);
}
}
}
mi.listInvValue = orderByList.OrderBy(o => o.day).ThenBy(t => t.inv).ToList();
var groupDay = mi.listInvValue.GroupBy(grp => grp.day).ToList();
foreach (var itemDay in groupDay)
{
mi.listDay.Add(itemDay.Key);
}
foreach (var invName in tblInv)
{
mi.listInvName.Add(invName.NAME);
}
mdl.invModel = mi;
mdl.ErrorMessage = "";
}
catch (Exception ex)
{
mdl.ErrorMessage = ex.ToString();
}
return(Json(mdl));
}
public FileContentResult ExportToExcel(int stationId, string slctDate)
{
Thread.CurrentThread.CurrentCulture = new CultureInfo("tr-TR");
DateTime datePicker = DateTime.Now;
string[] year_month = slctDate.Split('-');
int _year = Convert.ToInt32(year_month[0]);
int _month = Convert.ToInt32(year_month[1]);
datePicker = new DateTime(_year, _month, 1);
var stat = DB.Stations.Where(w => w.ID == stationId).FirstOrDefault();
NUMBER_FORMAT_DTO convertFormat = ConvertNumberFormatInvHeatMapMonthly(datePicker.ToString());
long _numberDateBegin = convertFormat._begin;
long _numberDateEnd = convertFormat._end;
MontlyInverterProduction mi = new MontlyInverterProduction();
var inverterDetail = DB.Inverters.Where(w => w.STATION_ID == stationId && w.IS_DELETED == false).ToList();
var tblInv = inverterDetail.OrderBy(o => int.Parse(o.NAME.Split(' ')[1])).ToList();
var listInvOzet = DB.InvSums.Where(ww => ww.STATION_ID == stationId && ww.TARIH_NUMBER >= _numberDateBegin && ww.TARIH_NUMBER <= _numberDateEnd && ww.Tarih.Value.Hour > 7)
.GroupBy(grp => new { grp.Tarih.Value.Day, grp.Inv_Id })
.Select(s => new InvMonthlyValue {
day = s.Key.Day, inv = s.Key.Inv_Id, energy = (float)Math.Round((s.Max(m => m.Gunluk_Enerji.Value)) / 1000, 2)
})
.ToList();
var MonthDaysCount = DateTime.DaysInMonth(_year, _month);
List <InvMonthlyValue> orderByList = new List <InvMonthlyValue>();
for (int i = 1; i <= MonthDaysCount; i++)
{
foreach (var inv in tblInv)
{
var reg = listInvOzet.Where(w => w.day == i && w.inv == inv.ID).FirstOrDefault();
if (reg == null)
{
orderByList.Add(new InvMonthlyValue {
day = i, inv = inv.ID, energy = null, name = inv.NAME
});
}
else
{
reg.name = inv.NAME;
orderByList.Add(reg);
}
}
}
mi.listInvValue = orderByList.OrderBy(o => o.day).ThenBy(t => t.inv).ToList();
List <INV_PROD_EXCEL_DTO> invProdList = mi.listInvValue.Select(s => new INV_PROD_EXCEL_DTO
{
DATE = new DateTime(_year, _month, s.day),
INVERTERNAME = s.name,
PRODUCTION = s.energy == null ? 0 : Math.Round((decimal)s.energy, 2)
}).ToList();
string fileName = stat.NAME.ToUpper() + " INVERTER_PRODUCTION_MONTHLY.xlsx";
string[] columns = { "DATE", "INVERTERNAME", "PRODUCTION" };
byte[] filecontent = ExcelExportHelper.ExportExcel(invProdList, stat.NAME, true, columns);
return(File(filecontent, ExcelExportHelper.ExcelContentType, fileName));
}
