// Functie salvare fisiere pentru luna precedenta pe data de 1 a lunii
public string SaveExcelFilesForLastMonth(string numePlc, RaportareDbContext context)
{
if (IsFirstDayOfMonth())
{
string dataFrom = DateTime.Now.AddMonths(-1).ToString("dd.MM.yyyy");
string dataTo = DateTime.Now.ToString("dd.MM.yyyy");
return(SaveExcelFileToDisk(dataFrom, dataTo, numePlc, context));
}
return("");
}
public Task StartAsync(CancellationToken cancellationToken)
{
_timer = new System.Timers.Timer();
_timer.Interval = 700;
_timer.Elapsed += DoWork;
_timer.Start();
IsStartedService = true;
// Start Scope service pentru a returna dbContext
scope = _scopeFactory.CreateScope();
// Creare dBContext din Scope
dbContext = scope.ServiceProvider.GetRequiredService <RaportareDbContext>();
//PlcService.probaIncrementare = 10;
return(Task.CompletedTask);
}
// Functie get last element added from Sql
public IndexModel GetLastElementFromSql(string plcName, RaportareDbContext context)
{
// Salvare valoare index citita in varabile statice raport
switch (plcName)
{
case "PlcCuptor":
if (IndexCuptor == 0)
{
return(context.IndexModels.ToList().Where(model => model.PlcName == "PlcCuptor").LastOrDefault());
}
break;
case "PlcGaddaF2":
if (IndexGaddaF2 == 0)
{
return(context.IndexModels.ToList().Where(model => model.PlcName == "PlcGaddaF2").LastOrDefault());
}
break;
case "PlcGaddaF4":
if (IndexGaddaF4 == 0)
{
return(context.IndexModels.ToList().Where(model => model.PlcName == "PlcGaddaF4").LastOrDefault());
}
break;
case "PlcElti":
if (IndexElti == 0)
{
return(context.IndexModels.ToList().Where(model => model.PlcName == "PlcElti").LastOrDefault());
}
break;
default:
break;
}
return(new IndexModel {
IndexValue = 0, GazValue = 0, PlcName = ""
});
}
// Functie actualizare a ultimului element la prima rulare in fiecare lista
public void UpdateLastElements(RaportareDbContext context)
{
try
{
if (IndexCuptor == 0)
{
IndexModel lastElementCuptor = GetLastElementFromSql("PlcCuptor", context);
IndexCuptor = (uint)lastElementCuptor.IndexValue;
ValoareConsumGazCuptor = lastElementCuptor.GazValue;
DataOraRaportFacut = lastElementCuptor.Data;
}
if (IndexGaddaF2 == 0)
{
IndexModel lastElementGaddaF2 = GetLastElementFromSql("PlcGaddaF2", context);
IndexGaddaF2 = (uint)lastElementGaddaF2.IndexValue;
ValoareConsumGazGaddaF2 = lastElementGaddaF2.GazValue;
DataOraRaportFacut = lastElementGaddaF2.Data;
}
if (IndexGaddaF4 == 0)
{
IndexModel lastElementGaddaF4 = GetLastElementFromSql("PlcGaddaF4", context);
IndexGaddaF4 = (uint)lastElementGaddaF4.IndexValue;
ValoareConsumGazGaddaF4 = lastElementGaddaF4.GazValue;
DataOraRaportFacut = lastElementGaddaF4.Data;
}
if (IndexElti == 0)
{
IndexModel lastElementElti = GetLastElementFromSql("PlcElti", context);
IndexElti = (uint)lastElementElti.IndexValue;
ValoareConsumGazElti = lastElementElti.GazValue;
DataOraRaportFacut = lastElementElti.Data;
}
}
catch (NullReferenceException ex)
{
}
}
// Functie creare fisier excel cu consumul lunar
// Functie exportare data to excel file and save to disk
// returneaza FilePath
public string SaveExcelFileToDisk(string dataFrom, string dataTo, string numePlc, RaportareDbContext _context)
{
//return Content(dataFrom + "<==>" + dataTo);
List <IndexModel> listaSql = _context.IndexModels.ToList();
IEnumerable <IndexModel> listaExcel = listaSql.Where(model => model.PlcName == numePlc);
// Extrage datele cuprinse intre limitele date de operator
IEnumerable <IndexModel> listaDeAfisat = listaExcel.Where(model => Auxiliar.IsDateBetween(model.Data, dataFrom, dataTo));
//var stream = new MemoryStream();
using (var pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(numePlc);
ws.Cells["A1:Z1"].Style.Font.Bold = true;
ws.Cells["A1"].Value = "Id";
ws.Cells["B1"].Value = "Data";
ws.Cells["C1"].Value = "Nume Plc";
ws.Cells["D1"].Value = "Index gaz";
ws.Cells["E1"].Value = "Consum gaz";
int rowStart = 2;
foreach (var elem in listaDeAfisat)
{
ws.Cells[string.Format("A{0}", rowStart)].Value = elem.Id;
ws.Cells[string.Format("B{0}", rowStart)].Value = elem.Data;
ws.Cells[string.Format("C{0}", rowStart)].Value = elem.PlcName;
ws.Cells[string.Format("D{0}", rowStart)].Value = elem.IndexValue;
ws.Cells[string.Format("E{0}", rowStart)].Value = elem.GazValue;
rowStart++;
}
ws.Cells["A:AZ"].AutoFitColumns();
//Write the file to the disk
string excelName = "RaportGaz_" + numePlc + "_" + DateTime.Now.AddMonths(-1).ToString("MMMM") + ".xlsx";
string filePath = string.Format("{0}/{1}", CreareFolderRaportare(numePlc), excelName);
FileInfo fi = new FileInfo(filePath);
pck.SaveAs(fi);
//pck.Save();
return(filePath);
}
}
// Functie verificare ora raport
// Setare Index si consum gaz PLC Cuptor, GaddaF2, GaddaF4 pt Plc-urile conectate
public bool VerificareOraRaport(string ora, RaportareDbContext context)
{
// La 10 minute verificam conexiune PLc-uri, si daca nu e facuta, incercam sa o refacem
if (DateTime.Now.ToString("HH:mm:ss").Substring(4, 4) == "5:00")
{
// Verificare conexiune Plc-uri, tinem in viata conexiunea cu plc-urile
foreach (PlcObjectModel plc in PlcServiceObject.ListaPlc)
{
if (!plc._client.IsConnected)
{
if (plc.IsAvailableIpAdress())
{
plc.ConnectPlc();
}
}
}
// Refresh values
PlcServiceObject.RefreshValuesListaPlc();
}
// Se verifica daca este ora raport si se inregistreaza date in SQL, se trimite mail
if (DateTime.Now.ToString("HH:mm:ss") == ora)
{
string filePathCuptor = "";
string filePathGaddaF2 = "";
string filePathGaddaF4 = "";
string filePathElti = "";
// Refresh values plc
PlcServiceObject.RefreshValuesListaPlc();
foreach (PlcObjectModel plc in PlcServiceObject.ListaPlc)
{
// Salvare valoare index citita in varabile statice raport
switch (plc.PlcName)
{
case "PlcCuptor":
AddToSqlIndex(context, GetIndexModelObject(plc.PlcName, plc.ValoareIndexGaz));
IndexCuptor = plc.ValoareIndexGaz;
// Creare Fisier excel cu consumul pe luna precedenta pe 1 a lunii
filePathCuptor = SaveExcelFilesForLastMonth(plc.PlcName, context);
break;
case "PlcGaddaF2":
AddToSqlIndex(context, GetIndexModelObject(plc.PlcName, plc.ValoareIndexGaz));
IndexGaddaF2 = plc.ValoareIndexGaz; //Dint
// Creare Fisier excel cu consumul pe luna precedenta pe 1 a lunii
filePathGaddaF2 = SaveExcelFilesForLastMonth(plc.PlcName, context);
break;
case "PlcGaddaF4":
AddToSqlIndex(context, GetIndexModelObject(plc.PlcName, plc.ValoareIndexGaz));
IndexGaddaF4 = plc.ValoareIndexGaz; //Dint
// Creare Fisier excel cu consumul pe luna precedenta pe 1 a lunii
filePathGaddaF4 = SaveExcelFilesForLastMonth(plc.PlcName, context);
break;
case "PlcElti":
AddToSqlIndex(context, GetIndexModelObject(plc.PlcName, plc.ValoareIndexGaz));
IndexElti = plc.ValoareIndexGaz; //Dint
// Creare Fisier excel cu consumul pe luna precedenta pe 1 a lunii
filePathElti = SaveExcelFilesForLastMonth(plc.PlcName, context);
break;
default:
break;
}
}
// Trimitere mail
string subiectTextMailDaily = String.Format("Consum gaz pe data: {0}",
DateTime.Now.ToString("dd MMMM, yyyy"));
string bodyTextMailDaily = ReturnBodyMailTextDaily(GetJsonForMail());
SendEmaildaily(ListaMailCuptor, subiectTextMailDaily, bodyTextMailDaily); // functie trimitere mail zilnic
// Functie trimitere mail lunar cu consumul inregistrat pe toata luna pe dat ade 1 a lunii
if (IsFirstDayOfMonth())
{
string subiectTextMailMonthly = String.Format("Consum gaz pe luna: {0}",
DateTime.Now.AddMonths(-1).ToString("MMMM, yyyy"));
string bodyTextMailMonthly = string.Format("Buna dimineata. <br><br>Atasat gasiti consumul de gaz " +
"pe luna {0}. <br><br>O zi buna.",
DateTime.Now.AddMonths(-1).ToString("MMMM, yyyy"));
// Functie trimitere mail lunar
SendEmailmonthly(ListaMailGadda, subiectTextMailMonthly, bodyTextMailMonthly, filePathCuptor,
filePathGaddaF2, filePathGaddaF4, filePathElti);
}
System.Threading.Thread.Sleep(1000);
return(true);
}
return(false);
}
// Functie salvare in database SQL index gaz model
public void AddToSqlIndex(RaportareDbContext context, IndexModel indexModel)
{
context.Add(AddToIndexModelGasValue(indexModel));
context.SaveChanges();
DataOraRaportFacut = DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
}