public static string GetDbUsedSpace()
{
try
{
const int MaxDbSizeMb = 10000;
//Populating a DataTable from database.
DataTable dt = new DataTable();
dt.Columns.Add("EspacoUsado", typeof(int));
dt.Columns.Add("EspacoLivre", typeof(int));
dt.Columns.Add("PercUsado", typeof(int));
dt.Columns.Add("PercLivre", typeof(int));
StringBuilder strQuery = new StringBuilder();
strQuery.AppendLine("SELECT ");
strQuery.AppendLine("CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB ");
strQuery.AppendLine("FROM sys.tables t ");
strQuery.AppendLine("INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id ");
strQuery.AppendLine("INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id ");
strQuery.AppendLine("INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id ");
strQuery.AppendLine("INNER JOIN sys.schemas s ON t.schema_id = s.schema_id ");
strQuery.AppendLine("GROUP BY t.Name, s.Name, p.Rows ");
strQuery.AppendLine("ORDER BY s.Name, t.Name ");
double totalSize = 0;
using (SqlConnection sqlConn = new SqlConnection(VARS.ConnectionString))
using (SqlCommand sqlCmd = new SqlCommand(strQuery.ToString(), sqlConn))
{
sqlConn.Open();
using (SqlDataReader dr = sqlCmd.ExecuteReader())
while (dr.Read())
{
totalSize += Convert.ToDouble(dr[0]);
}
}
int usedSpace = Convert.ToInt32(totalSize);
int freeSpace = MaxDbSizeMb - usedSpace;
int percFreeSpace = freeSpace * 100 / MaxDbSizeMb;
int percUsedSpace = 100 - percFreeSpace;
dt.Rows.Add(usedSpace, freeSpace, percFreeSpace, percUsedSpace);
return(VARS.ConvertDataTabletoString(dt));
}
catch (Exception ex)
{
Debug.WriteLine("GetDbUsedSpace(): " + ex.Message);
return(string.Empty);
}
}
public static string GetLastAVG()
{
//Populating a DataTable from database.
DataTable dt = new DataTable();
dt.Columns.Add("Media_D", typeof(double));
dt.Columns.Add("Media_C", typeof(int));
dt.Columns.Add("Media_T", typeof(string));
dt.Columns.Add("Min_D", typeof(double));
dt.Columns.Add("Min_C", typeof(int));
dt.Columns.Add("Min_T", typeof(string));
dt.Columns.Add("Max_D", typeof(double));
dt.Columns.Add("Max_C", typeof(int));
dt.Columns.Add("Max_T", typeof(string));
if (LAST_SEARCH_STATS.Count == 3)
{
dt.Rows.Add(LAST_SEARCH_STATS[0].Value, (int)LAST_SEARCH_STATS[0].Classe, LAST_SEARCH_STATS[0].DataHora.ToString("dd/MM/yyyy HH:mm:ss"),
LAST_SEARCH_STATS[1].Value, (int)LAST_SEARCH_STATS[1].Classe, LAST_SEARCH_STATS[1].DataHora.ToString("dd/MM/yyyy HH:mm:ss"),
LAST_SEARCH_STATS[2].Value, (int)LAST_SEARCH_STATS[2].Classe, LAST_SEARCH_STATS[2].DataHora.ToString("dd/MM/yyyy HH:mm:ss"));
}
return(VARS.ConvertDataTabletoString(dt));
}
public static string GetAlertasProducao(string id, string DtInicio, string DtFim, string Offset)
{
try
{
if (string.IsNullOrWhiteSpace(Offset) || !Diversos.IsNumeric(Offset))
{
Offset = "0";
}
Maquina maquina = VARS.Maquinas.First(x => x.Id == Convert.ToInt32(id));
//Populating a DataTable from database.
DataTable dt = new DataTable();
StringBuilder strQuery = new StringBuilder();
strQuery.Append("SELECT TOP 1000 TipoAlerta.Tipo as 'A', (Alertas.Diametro + @OFFSET) AS 'D', FORMAT(Alertas.DataHora, 'dd/MM/yyyy HH:mm:ss') AS 'T' FROM Alertas ");
strQuery.Append("INNER JOIN TipoAlerta ON Alertas.IdAlerta = TipoAlerta.Id ");
strQuery.Append("WHERE Alertas.IdMaquina = @IdMaquina AND Alertas.DataHora BETWEEN @DATA1 AND @DATA2 ");
strQuery.Append("ORDER BY Alertas.Id");
if (maquina == null)
{
throw new Exception("máquina não encontrada!");
}
else
{
using (SqlConnection sqlConn = new SqlConnection(VARS.ConnectionString))
using (SqlCommand sqlCmd = new SqlCommand(strQuery.ToString(), sqlConn))
{
sqlCmd.Parameters.Add("@IdMaquina", SqlDbType.TinyInt).Value = Convert.ToInt32(id);
sqlCmd.Parameters.AddWithValue("@OFFSET", Convert.ToDouble(Offset.Replace(".", ",")));
sqlCmd.Parameters.Add("@DATA1", SqlDbType.DateTime).Value = DateTime.Parse(DtInicio);
sqlCmd.Parameters.Add("@DATA2", SqlDbType.DateTime).Value = DateTime.Parse(DtFim);
using (SqlDataAdapter sda = new SqlDataAdapter(sqlCmd))
sda.Fill(dt);
}
}
return(VARS.ConvertDataTabletoString(dt));
}
catch (Exception ex)
{
Debug.WriteLine("GetAlertasProducao(): " + ex.Message);
return(string.Empty);
}
}
public static string GetLastClassificacoes()
{
//Populating a DataTable from database.
DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Diametro", typeof(double));
dt.Columns.Add("DataHora", typeof(string));
for (int i = 0; i < LAST_CLASS_TABLE.Count; i++)
{
dt.Rows.Add((i + 1), LAST_CLASS_TABLE[i].Value, LAST_CLASS_TABLE[i].DataHora.ToString("dd/MM/yyyy HH:mm:ss"));
}
return(VARS.ConvertDataTabletoString(dt));
}
public static string GetUserControlInfos(string id)
{
//Populating a DataTable from database.
DataTable dt = new DataTable();
dt.Columns.Add("Diametro", typeof(double));
dt.Columns.Add("Classificacao", typeof(int));
dt.Columns.Add("Error", typeof(bool));
dt.Columns.Add("Timestamp", typeof(string));
try
{
Maquina maquina = VARS.Maquinas.First(x => x.Id == Convert.ToInt32(id));
if (maquina == null)
{
throw new Exception("máquina não encontrada!");
}
uint maxDifSec = 30;
using (SqlConnection sqlConn = new SqlConnection(VARS.ConnectionString))
using (SqlCommand sqlCmd = new SqlCommand("select top 1 diametro, ABS(DATEDIFF(SECOND, getdate() , DtUpload)) AS DateDiff, DtUpload from " + maquina.DataTable + " order by id desc", sqlConn))
{
sqlConn.Open();
using (SqlDataReader dr = sqlCmd.ExecuteReader())
if (dr.Read())
{
dt.Rows.Add(Math.Round(Convert.ToDouble(dr[0]), 3), (int)maquina.Classe.GetClassificacao(Convert.ToDouble(dr[0])), Convert.ToUInt32(dr[1]) > maxDifSec, Convert.ToDateTime(dr[2]).ToString("dd/MM/yyyy HH:mm:ss"));
}
else
{
throw new Exception("sem dados lidos!");
}
}
}
catch (Exception ex)
{
Debug.WriteLine("GetUserControlInfos(): " + ex.Message);
dt.Rows.Clear();
dt.Rows.Add(0, true);
}
return(VARS.ConvertDataTabletoString(dt));
}
public static string GetLastChartInfo(string id)
{
//Populating a DataTable from database.
DataTable dt = new DataTable();
dt.Columns.Add("Diametro", typeof(double));
dt.Columns.Add("Classificacao", typeof(int));
dt.Columns.Add("Timestamp", typeof(string));
try
{
Maquina maquina = VARS.Maquinas.First(x => x.Id == Convert.ToInt32(id));
int lastSec = -1;
if (maquina == null)
{
throw new Exception("máquina não encontrada!");
}
else
if (maquina.GraficoPontos.GetLastNPoints(1200)) //antes - 240 (2min)
{
foreach (Maquina.PontosGraph.DataPoint point in maquina.GraficoPontos.Pontos)
{
if (lastSec != point.DataHora.Second) //xanato para so enviar pontos a cada seg
{
dt.Rows.Add(Math.Round(point.Value, 3), (int)point.Classe, point.DataHora.ToString("dd/MM/yyyy HH:mm:ss"));
}
lastSec = point.DataHora.Second;
}
}
}
catch (Exception ex)
{
Debug.WriteLine("GetLastChartInfo(): " + ex.Message);
return(string.Empty);
}
return(VARS.ConvertDataTabletoString(dt));
}
public static string GetTbl()
{
try
{
//Populating a DataTable from database.
DataTable dt = new DataTable();
using (SqlConnection sqlConn = new SqlConnection(VARS.ConnectionString))
using (SqlCommand sqlCmd = new SqlCommand("SELECT Id, Nome, Username, Password, Nivel, Avatar, FORMAT(UltimoAcesso, 'dd/MM/yyyy HH:mm:ss') as 'UltimoAcesso' FROM Utilizadores WHERE Id > 0 ORDER BY Id", sqlConn))
using (SqlDataAdapter sda = new SqlDataAdapter(sqlCmd))
sda.Fill(dt);
return(VARS.ConvertDataTabletoString(dt));
}
catch (Exception ex)
{
Debug.WriteLine("GetTbl(): " + ex.Message);
return(string.Empty);
}
}
public static string GetTbl()
{
try
{
//Populating a DataTable from database.
DataTable dt = new DataTable();
using (SqlConnection sqlConn = new SqlConnection(VARS.ConnectionString))
using (SqlCommand sqlCmd = new SqlCommand("SELECT Id, Nome, Email, FORMAT(DtAlteracao, 'dd/MM/yyyy HH:mm:ss') as 'DtAlteracao', Ativo FROM Emails WHERE Id > 0 ORDER BY Id", sqlConn))
using (SqlDataAdapter sda = new SqlDataAdapter(sqlCmd))
sda.Fill(dt);
return(VARS.ConvertDataTabletoString(dt));
}
catch (Exception ex)
{
Debug.WriteLine("GetTbl(): " + ex.Message);
return(string.Empty);
}
}
public static string GetAlertasProducao(string id)
{
try
{
Maquina maquina = VARS.Maquinas.First(x => x.Id == Convert.ToInt32(id));
//Populating a DataTable from database.
DataTable dt = new DataTable();
StringBuilder strQuery = new StringBuilder();
strQuery.Append("SELECT Alertas.Id, TipoAlerta.Tipo, Alertas.Diametro, FORMAT(Alertas.DataHora, 'dd/MM/yyyy HH:mm:ss') AS 'DataHora' FROM Alertas ");
strQuery.Append("INNER JOIN TipoAlerta ON Alertas.IdAlerta = TipoAlerta.Id ");
strQuery.Append("WHERE Alertas.IdMaquina = @IdMaquina AND Alertas.DataHora >= DATEADD (dd, " + (VARS.NUM_OF_DAYS_TO_SHOW_ALERT).ToString() + ", GETDATE()) ");
strQuery.Append("ORDER BY Alertas.Id DESC");
if (maquina == null)
{
throw new Exception("máquina não encontrada!");
}
else
{
using (SqlConnection sqlConn = new SqlConnection(VARS.ConnectionString))
using (SqlCommand sqlCmd = new SqlCommand(strQuery.ToString(), sqlConn))
{
sqlCmd.Parameters.Add("@IdMaquina", SqlDbType.TinyInt).Value = Convert.ToInt32(id);
using (SqlDataAdapter sda = new SqlDataAdapter(sqlCmd))
sda.Fill(dt);
}
}
return(VARS.ConvertDataTabletoString(dt));
}
catch (Exception ex)
{
Debug.WriteLine("GetAlertasProducao(): " + ex.Message);
return(string.Empty);
}
}
public static string GetGraph(string id, string DtInicio, string DtFim, string Offset)
{
//Populating a DataTable from database.
DataTable dt = new DataTable();
dt.Columns.Add("D", typeof(double));
//dt.Columns.Add("C", typeof(int));
dt.Columns.Add("T", typeof(string));
int maxOfRecords = 1000;
if (string.IsNullOrWhiteSpace(Offset) || !Diversos.IsNumeric(Offset))
{
Offset = "0";
}
try
{
Maquina maquina = VARS.Maquinas.First(x => x.Id == Convert.ToInt32(id));
// int lastSec = -1;
if (maquina == null)
{
throw new Exception("máquina não encontrada!");
}
else
if (maquina.GraficoPontos.GetPoints(DateTime.Parse(DtInicio), DateTime.Parse(DtFim).AddMinutes(1).AddSeconds(-1), Convert.ToDouble(Offset.Replace(".", ","))))
{
//get stats
LAST_SEARCH_STATS.Clear();
//get class
LAST_CLASS_TABLE.Clear();
LAST_CLASS = Classificacoes.Classificacao.Undefined;
long numPointsClasse2 = 0;
long numPointsClasse3 = 0;
long numPointsNC = 0;
Classificacoes.Classificacao lastClass = Classificacoes.Classificacao.Undefined;
int increment = (int)Math.Ceiling((double)maquina.GraficoPontos.Pontos.Count / maxOfRecords);
for (int i = 0; i < maquina.GraficoPontos.Pontos.Count; i++)
{
if (i % increment == 0 || (maquina.GraficoPontos.Pontos[i].Classe != Classificacoes.Classificacao.Conforme && maquina.GraficoPontos.Pontos[i].Classe != lastClass))
{
// dt.Rows.Add(Math.Round(maquina.GraficoPontos.Pontos[i].Value, 3), (int)maquina.GraficoPontos.Pontos[i].Classe, maquina.GraficoPontos.Pontos[i].DataHora.ToString("dd/MM/yyyy HH:mm:ss"));
dt.Rows.Add(Math.Round(maquina.GraficoPontos.Pontos[i].Value, 3), maquina.GraficoPontos.Pontos[i].DataHora.ToString("dd/MM/yyyy HH:mm:ss"));
}
if (LAST_CLASS_TABLE.Count < 1000)
{
if (maquina.GraficoPontos.Pontos[i].Classe != Classificacoes.Classificacao.Conforme)
{
if (maquina.GraficoPontos.Pontos[i].Classe != lastClass)
{
LAST_CLASS_TABLE.Add(maquina.GraficoPontos.Pontos[i]);
}
}
}
lastClass = maquina.GraficoPontos.Pontos[i].Classe;
if (maquina.GraficoPontos.Pontos[i].Classe == Classificacoes.Classificacao.Classe2)
{
numPointsClasse2++;
}
else if (maquina.GraficoPontos.Pontos[i].Classe == Classificacoes.Classificacao.Classe3)
{
numPointsClasse3++;
}
else if (maquina.GraficoPontos.Pontos[i].Classe == Classificacoes.Classificacao.NaoConforme)
{
numPointsNC++;
}
}
//analise a conformidade da bobine
if (numPointsNC > 0 || numPointsClasse3 > maquina.Classe.MaxPontosClasse3 || numPointsClasse2 > maquina.Classe.MaxPontosClasse2)
{
LAST_CLASS = Classificacoes.Classificacao.NaoConforme;
}
else if (numPointsClasse3 > 0)
{
LAST_CLASS = Classificacoes.Classificacao.Classe3;
}
else if (numPointsClasse2 > 0)
{
LAST_CLASS = Classificacoes.Classificacao.Classe2;
}
else
{
LAST_CLASS = Classificacoes.Classificacao.Conforme;
}
//get stats
double aux = Math.Round(maquina.GraficoPontos.Pontos.Select(x => x.Value).Average(), 3);
LAST_SEARCH_STATS.Add(new Maquina.PontosGraph.DataPoint(aux, DateTime.Now, maquina.Classe.GetClassificacao(aux)));
LAST_SEARCH_STATS.Add(maquina.GraficoPontos.Minimum);
LAST_SEARCH_STATS.Add(maquina.GraficoPontos.Maximum);
}
}
catch (Exception ex)
{
Debug.WriteLine("GetGraph(): " + ex.Message);
return(string.Empty);
}
return(VARS.ConvertDataTabletoString(dt));
}
