350 lines
14 KiB
C#
350 lines
14 KiB
C#
|
using MySql.Data.MySqlClient;
|
|||
|
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Linq;
|
|||
|
|
using System.Text;
|
|||
|
|
using System.Threading.Tasks;
|
|||
|
|
|
|||
|
|
namespace ServiceShared.Database.Controllers
|
|||
|
|
{
|
|||
|
|
public class Traces
|
|||
|
|
{
|
|||
|
|
private DbContext _DbContext;
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// Constructor Traces database controller
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="dbContext">DbContext</param>
|
|||
|
|
public Traces(DbContext dbContext)
|
|||
|
|
{
|
|||
|
|
this._DbContext = dbContext;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// Creates Traces log in the database
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="results">Traces object</param>
|
|||
|
|
/// <returns>returns true if success</returns>
|
|||
|
|
public bool Create(Models.Database.Traces traces)
|
|||
|
|
{
|
|||
|
|
bool result = false;
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (traces != null &&
|
|||
|
|
!string.IsNullOrEmpty(traces.PGS) &&
|
|||
|
|
traces.PGS.Length == 128 &&
|
|||
|
|
!string.IsNullOrEmpty(traces.UDID) &&
|
|||
|
|
(traces.UDID.Length >= 10 && traces.UDID.Length <= 64) &&
|
|||
|
|
!string.IsNullOrEmpty(traces.Message))
|
|||
|
|
{
|
|||
|
|
using (MySqlConnection client = this._DbContext.CreateConnection())
|
|||
|
|
{
|
|||
|
|
client.Open();
|
|||
|
|
MySqlCommand cmd = client.CreateCommand();
|
|||
|
|
cmd.CommandText = @"insert into traces (pgs,
|
|||
|
|
udid,
|
|||
|
|
message,
|
|||
|
|
trace_type)
|
|||
|
|
|
|||
|
|
values (@pgs,
|
|||
|
|
@udid,
|
|||
|
|
@message,
|
|||
|
|
@trace_type)";
|
|||
|
|
|
|||
|
|
cmd.Parameters.Add(new MySqlParameter("pgs", traces.PGS));
|
|||
|
|
cmd.Parameters.Add(new MySqlParameter("udid", traces.UDID));
|
|||
|
|
cmd.Parameters.Add(new MySqlParameter("message", traces.Message));
|
|||
|
|
cmd.Parameters.Add(new MySqlParameter("trace_type", traces.TraceType.ToString()));
|
|||
|
|
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
result = true;
|
|||
|
|
client.Close();
|
|||
|
|
|
|||
|
|
Log.Write(cmd.CommandText + ", @pgs = " + traces.PGS +
|
|||
|
|
", @udid = " + traces.UDID +
|
|||
|
|
", @message = " + traces.Message +
|
|||
|
|
", @trace_type = " + traces.TraceType.ToString(),
|
|||
|
|
Log.Types.TRACE);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
Log.Critical(ex, "ServiceOutside.Database.Controllers.Traces", "Create(Models.Traces)");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return result;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// Creates Traces log in the database
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="udid">udif of device</param>
|
|||
|
|
/// <returns>returns true if success</returns>
|
|||
|
|
public bool DeleteDevice(string udid)
|
|||
|
|
{
|
|||
|
|
bool result = false;
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (!string.IsNullOrEmpty(udid) && udid.Length >= 10 && udid.Length <= 64)
|
|||
|
|
{
|
|||
|
|
using (MySqlConnection client = this._DbContext.CreateConnection())
|
|||
|
|
{
|
|||
|
|
client.Open();
|
|||
|
|
MySqlCommand cmd = client.CreateCommand();
|
|||
|
|
cmd.CommandText = @"delete from traces where udid = @udid";
|
|||
|
|
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
|||
|
|
cmd.ExecuteNonQuery();
|
|||
|
|
result = true;
|
|||
|
|
client.Close();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
Log.Critical(ex, "ServiceOutside.Database.Controllers.Traces", "DeleteDevice(string)");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return result;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// Returns Traces by pgs and udid from the database
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="pgs">pgs</param>
|
|||
|
|
/// <param name="udid">udid</param>
|
|||
|
|
/// <returns>returns traces object from the database</returns>
|
|||
|
|
public List<Models.Database.Traces> GetTraces(string pgs, string udid)
|
|||
|
|
{
|
|||
|
|
List<Models.Database.Traces> result = null;
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128)
|
|||
|
|
{
|
|||
|
|
using (MySqlConnection client = this._DbContext.CreateConnection())
|
|||
|
|
{
|
|||
|
|
client.Open();
|
|||
|
|
MySqlCommand cmd = client.CreateCommand();
|
|||
|
|
cmd.CommandText = @"select
|
|||
|
|
t.id,
|
|||
|
|
t.pgs,
|
|||
|
|
t.udid,
|
|||
|
|
t.message,
|
|||
|
|
t.trace_type,
|
|||
|
|
t.created
|
|||
|
|
from
|
|||
|
|
traces t
|
|||
|
|
where
|
|||
|
|
t.pgs = @pgs and
|
|||
|
|
t.udid = @udid
|
|||
|
|
order by
|
|||
|
|
t.created desc";
|
|||
|
|
|
|||
|
|
cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
|
|||
|
|
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
|||
|
|
|
|||
|
|
MySqlDataReader reader = cmd.ExecuteReader();
|
|||
|
|
|
|||
|
|
if (reader != null && reader.HasRows)
|
|||
|
|
{
|
|||
|
|
result = new List<Models.Database.Traces>();
|
|||
|
|
|
|||
|
|
while (reader.Read())
|
|||
|
|
{
|
|||
|
|
Models.Database.Traces dbTraces = this.ReadTraces(reader);
|
|||
|
|
|
|||
|
|
if (dbTraces != null)
|
|||
|
|
{
|
|||
|
|
result.Add(dbTraces);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
client.Close();
|
|||
|
|
|
|||
|
|
Log.Write(cmd.CommandText + ", @pgs = " + pgs +
|
|||
|
|
", @udid = " + udid, Log.Types.TRACE);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
Log.Critical(ex, "ServiceOutside.Database.Controllers.Traces", "GetResults(string, string)");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return result;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// Returns Traces by pgs from the database
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="pgs">pgs</param>
|
|||
|
|
/// <returns>returns traces object from the database</returns>
|
|||
|
|
public List<Models.Database.Traces> GetTraces(string pgs)
|
|||
|
|
{
|
|||
|
|
List<Models.Database.Traces> result = null;
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128)
|
|||
|
|
{
|
|||
|
|
using (MySqlConnection client = this._DbContext.CreateConnection())
|
|||
|
|
{
|
|||
|
|
client.Open();
|
|||
|
|
MySqlCommand cmd = client.CreateCommand();
|
|||
|
|
cmd.CommandText = @"select
|
|||
|
|
t.id,
|
|||
|
|
t.pgs,
|
|||
|
|
t.udid,
|
|||
|
|
t.message,
|
|||
|
|
t.trace_type,
|
|||
|
|
t.created
|
|||
|
|
from
|
|||
|
|
traces t
|
|||
|
|
where
|
|||
|
|
t.pgs = @pgs
|
|||
|
|
order by
|
|||
|
|
t.created desc";
|
|||
|
|
|
|||
|
|
cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
|
|||
|
|
|
|||
|
|
MySqlDataReader reader = cmd.ExecuteReader();
|
|||
|
|
|
|||
|
|
if (reader != null && reader.HasRows)
|
|||
|
|
{
|
|||
|
|
result = new List<Models.Database.Traces>();
|
|||
|
|
|
|||
|
|
while (reader.Read())
|
|||
|
|
{
|
|||
|
|
Models.Database.Traces dbTraces = this.ReadTraces(reader);
|
|||
|
|
|
|||
|
|
if (dbTraces != null)
|
|||
|
|
{
|
|||
|
|
result.Add(dbTraces);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
client.Close();
|
|||
|
|
|
|||
|
|
Log.Write(cmd.CommandText + ", @pgs = " + pgs, Log.Types.TRACE);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
Log.Critical(ex, "ServiceOutside.Database.Controllers.Traces", "GetResults(string)");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return result;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// Returns Traces from the database for date range
|
|||
|
|
/// </summary>
|
|||
|
|
/// <returns>returns traces object from the database</returns>
|
|||
|
|
public List<Models.Database.Traces> GetTraces(DateTime from, DateTime? to = null)
|
|||
|
|
{
|
|||
|
|
List<Models.Database.Traces> result = null;
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
using (MySqlConnection client = this._DbContext.CreateConnection())
|
|||
|
|
{
|
|||
|
|
client.Open();
|
|||
|
|
MySqlCommand cmd = client.CreateCommand();
|
|||
|
|
cmd.CommandText = @"select
|
|||
|
|
t.id,
|
|||
|
|
t.pgs,
|
|||
|
|
t.udid,
|
|||
|
|
t.message,
|
|||
|
|
t.trace_type,
|
|||
|
|
t.created
|
|||
|
|
from
|
|||
|
|
traces t
|
|||
|
|
where
|
|||
|
|
t.created >= @from " + (to.HasValue ? " and t.created <= @to" : "") +
|
|||
|
|
" order by " +
|
|||
|
|
" t.created desc";
|
|||
|
|
|
|||
|
|
cmd.Parameters.Add(new MySqlParameter("from", from.ToString("yyyy-MM-dd HH:mm:ii")));
|
|||
|
|
|
|||
|
|
if(to.HasValue)
|
|||
|
|
{
|
|||
|
|
cmd.Parameters.Add(new MySqlParameter("to", to.Value.ToString("yyyy-MM-dd HH:mm:ii")));
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
MySqlDataReader reader = cmd.ExecuteReader();
|
|||
|
|
|
|||
|
|
if (reader != null && reader.HasRows)
|
|||
|
|
{
|
|||
|
|
result = new List<Models.Database.Traces>();
|
|||
|
|
|
|||
|
|
while (reader.Read())
|
|||
|
|
{
|
|||
|
|
Models.Database.Traces dbTraces = this.ReadTraces(reader);
|
|||
|
|
|
|||
|
|
if (dbTraces != null)
|
|||
|
|
{
|
|||
|
|
result.Add(dbTraces);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
client.Close();
|
|||
|
|
|
|||
|
|
Log.Write(cmd.CommandText + ", @from = " + from.ToString("yyyy-MM-dd HH:mm:ii") +
|
|||
|
|
(to.HasValue ? " @to = " + to.Value.ToString("yyyy-MM-dd HH:mm:ii") : ""), Log.Types.LOG);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
Log.Critical(ex, "ServiceOutside.Database.Controllers.Traces", "GetResults(DateTime, DateTime)");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return result;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// Reads traces from mysql data reader
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="reader">MySqlDataReader with current connection context</param>
|
|||
|
|
/// <returns>Returns Traces object</returns>
|
|||
|
|
private Models.Database.Traces ReadTraces(MySqlDataReader reader)
|
|||
|
|
{
|
|||
|
|
Models.Database.Traces result = null;
|
|||
|
|
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
if (reader["pgs"] != null && reader["pgs"] != DBNull.Value)
|
|||
|
|
{
|
|||
|
|
result = new Models.Database.Traces();
|
|||
|
|
result.Id = (long)reader["id"];
|
|||
|
|
result.PGS = (string)reader["pgs"];
|
|||
|
|
result.UDID = (string)reader["udid"];
|
|||
|
|
result.Message = (string)reader["message"];
|
|||
|
|
|
|||
|
|
string trace_type = ((reader["device_type"] != null && reader["device_type"] != DBNull.Value) ? (string)reader["device_type"] : null);
|
|||
|
|
Log.Types traceType = Log.Types.TRACE;
|
|||
|
|
|
|||
|
|
if (!string.IsNullOrEmpty(trace_type) && Enum.TryParse(trace_type, out traceType) && traceType != Log.Types.TRACE)
|
|||
|
|
{
|
|||
|
|
result.TraceType = traceType;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
result.Created = (DateTime)reader["created"];
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
catch (Exception ex)
|
|||
|
|
{
|
|||
|
|
Log.Critical(ex, "ServiceOutside.Database.Controllers.Traces", "ReadTraces(MySqlDataReader)");
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return result;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|