1635 lines
76 KiB
C#
1635 lines
76 KiB
C#
using MySql.Data.MySqlClient;
|
||
using ServiceShared.Models.Database;
|
||
using static ServiceShared.Models.Database.Device;
|
||
using static ServiceShared.Models.Database.Results;
|
||
|
||
namespace ServiceShared.Database.Controllers
|
||
{
|
||
public class Results
|
||
{
|
||
private DbContext _DbContext;
|
||
|
||
/// <summary>
|
||
/// Constructor for results database controller
|
||
/// </summary>
|
||
/// <param name="dbContext">Database context, that contains connection to the mysql database</param>
|
||
public Results(DbContext dbContext)
|
||
{
|
||
this._DbContext = dbContext;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Returns results by pgs and udid from the database
|
||
/// </summary>
|
||
/// <param name="pgs">pgs hash(partial pk)</param>
|
||
/// <param name="udid">udid(partial pk)</param>
|
||
/// <returns>Returns results object from the database</returns>
|
||
public Models.Database.Results GetResults(string pgs, string udid)
|
||
{
|
||
Models.Database.Results result = null;
|
||
|
||
try
|
||
{
|
||
if(!string.IsNullOrEmpty(pgs) && !string.IsNullOrEmpty(udid))
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"select
|
||
r.pgs,
|
||
r.pgs_hash,
|
||
d.udid,
|
||
d.device_token,
|
||
d.device_type,
|
||
r.client_public_key,
|
||
r.server_public_key,
|
||
r.file_checksum,
|
||
r.available,
|
||
r.available_ts,
|
||
r.picked_up,
|
||
r.picked_up_ts,
|
||
r.notified,
|
||
r.notified_ts,
|
||
d.verificator_hash,
|
||
r.status,
|
||
r.modified,
|
||
r.created,
|
||
r.not_found_counter,
|
||
r.not_found_ts,
|
||
r.notification_counter
|
||
from
|
||
results r
|
||
inner join
|
||
device d on d.udid = r.udid
|
||
where
|
||
r.pgs = @pgs and
|
||
d.udid = @udid";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
|
||
MySqlDataReader reader = cmd.ExecuteReader();
|
||
|
||
if (reader != null && reader.HasRows && reader.Read())
|
||
{
|
||
result = this.ReadResults(reader);
|
||
}
|
||
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @pgs = " + pgs +
|
||
", @udid = " + udid, Log.Types.LOG);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "GetResults(string, string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Returns not found results
|
||
/// <param name="pastMinutes">past minutes, after that the not found results should be checked again</param>
|
||
/// </summary>
|
||
/// <returns>Returns results object from the database</returns>
|
||
public List<Models.Database.Results> GetNotFoundResults(int pastMinutes, int maxTryNotFoundResults)
|
||
{
|
||
List<Models.Database.Results> result = null;
|
||
|
||
try
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"select
|
||
r.pgs,
|
||
r.pgs_hash,
|
||
d.udid,
|
||
d.device_token,
|
||
d.device_type,
|
||
r.client_public_key,
|
||
r.server_public_key,
|
||
r.file_checksum,
|
||
r.available,
|
||
r.available_ts,
|
||
r.picked_up,
|
||
r.picked_up_ts,
|
||
r.notified,
|
||
r.notified_ts,
|
||
d.verificator_hash,
|
||
r.status,
|
||
r.modified,
|
||
r.created,
|
||
r.not_found_counter,
|
||
r.not_found_ts,
|
||
r.notification_counter
|
||
from
|
||
results r
|
||
inner join
|
||
device d on d.udid = r.udid
|
||
where
|
||
r.status = @status and
|
||
r.not_found_counter < @max_not_found_counter and
|
||
r.not_found_ts is not null";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("status", ServiceShared.Models.Database.Results.ResultsStatus.NOT_FOUND.ToString()));
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("max_not_found_counter", maxTryNotFoundResults));
|
||
|
||
MySqlDataReader reader = cmd.ExecuteReader();
|
||
|
||
if (reader != null && reader.HasRows)
|
||
{
|
||
result = new List<Models.Database.Results>();
|
||
|
||
DateTime now = DateTime.Now;
|
||
|
||
while (reader.Read())
|
||
{
|
||
Models.Database.Results results = this.ReadResults(reader);
|
||
|
||
Log.Debug("[BW] check not found past minutes: " + (now - results.NotFoundTS.Value).TotalMinutes.ToString());
|
||
|
||
if (result != null && results.NotFoundTS.HasValue && (now - results.NotFoundTS.Value).TotalMinutes >= (pastMinutes - 5))
|
||
{
|
||
result.Add(results);
|
||
}
|
||
}
|
||
}
|
||
|
||
client.Close();
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "GetNotFoundResults(int)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Returns completed results that could not be notified
|
||
/// </summary>
|
||
/// <returns>Returns results object from the database</returns>
|
||
public List<Models.Database.Results> GetNotNotifiedResults()
|
||
{
|
||
List<Models.Database.Results> result = null;
|
||
|
||
try
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"select
|
||
r.pgs,
|
||
r.pgs_hash,
|
||
d.udid,
|
||
d.device_token,
|
||
d.device_type,
|
||
r.client_public_key,
|
||
r.server_public_key,
|
||
r.file_checksum,
|
||
r.available,
|
||
r.available_ts,
|
||
r.picked_up,
|
||
r.picked_up_ts,
|
||
r.notified,
|
||
r.notified_ts,
|
||
d.verificator_hash,
|
||
r.status,
|
||
r.modified,
|
||
r.created,
|
||
r.not_found_counter,
|
||
r.not_found_ts,
|
||
r.notification_counter
|
||
from
|
||
results r
|
||
inner join
|
||
device d on d.udid = r.udid
|
||
where
|
||
r.notified <> 1 and
|
||
r.available = 1 and
|
||
r.status = @status and
|
||
r.notification_counter <= 3";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("status", ServiceShared.Models.Database.Results.ResultsStatus.COMPLETED.ToString()));
|
||
|
||
MySqlDataReader reader = cmd.ExecuteReader();
|
||
|
||
if (reader != null && reader.HasRows)
|
||
{
|
||
result = new List<Models.Database.Results>();
|
||
|
||
while(reader.Read())
|
||
{
|
||
Models.Database.Results results = this.ReadResults(reader);
|
||
|
||
if(result != null)
|
||
{
|
||
result.Add(results);
|
||
}
|
||
}
|
||
}
|
||
|
||
client.Close();
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "GetNotNotifiedResults()");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Returns not found results that could not be notified
|
||
/// </summary>
|
||
/// <returns>Returns results object from the database</returns>
|
||
public List<Models.Database.Results> GetNotFoundNotNotifiedResults(int maxNotFoundTries)
|
||
{
|
||
List<Models.Database.Results> result = null;
|
||
|
||
try
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"select
|
||
r.pgs,
|
||
r.pgs_hash,
|
||
d.udid,
|
||
d.device_token,
|
||
d.device_type,
|
||
r.client_public_key,
|
||
r.server_public_key,
|
||
r.file_checksum,
|
||
r.available,
|
||
r.available_ts,
|
||
r.picked_up,
|
||
r.picked_up_ts,
|
||
r.notified,
|
||
r.notified_ts,
|
||
d.verificator_hash,
|
||
r.status,
|
||
r.modified,
|
||
r.created,
|
||
r.not_found_counter,
|
||
r.not_found_ts,
|
||
r.notification_counter
|
||
from
|
||
results r
|
||
inner join
|
||
device d on d.udid = r.udid
|
||
where
|
||
r.notified <> 1 and
|
||
r.available = 0 and
|
||
r.status = @status and
|
||
r.not_found_counter >= @not_found_counter and
|
||
r.notification_counter <= 3";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("status", ServiceShared.Models.Database.Results.ResultsStatus.NOT_FOUND.ToString()));
|
||
cmd.Parameters.Add(new MySqlParameter("not_found_counter", maxNotFoundTries));
|
||
|
||
MySqlDataReader reader = cmd.ExecuteReader();
|
||
|
||
if (reader != null && reader.HasRows)
|
||
{
|
||
result = new List<Models.Database.Results>();
|
||
|
||
while (reader.Read())
|
||
{
|
||
Models.Database.Results results = this.ReadResults(reader);
|
||
|
||
if (result != null)
|
||
{
|
||
result.Add(results);
|
||
}
|
||
}
|
||
}
|
||
|
||
client.Close();
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "GetNotFoundNotNotifiedResults()");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Returns list of results by pgs(partial pk) from the database
|
||
/// </summary>
|
||
/// <param name="pgs">pgs hash(partial pk)</param>
|
||
/// <returns>Returns results object from the database</returns>
|
||
public List<Models.Database.Results> GetResults(string pgs)
|
||
{
|
||
List<Models.Database.Results> result = null;
|
||
|
||
try
|
||
{
|
||
if (!string.IsNullOrEmpty(pgs))
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"select
|
||
r.pgs,
|
||
r.pgs_hash,
|
||
d.udid,
|
||
d.device_token,
|
||
d.device_type,
|
||
r.client_public_key,
|
||
r.server_public_key,
|
||
r.file_checksum,
|
||
r.available,
|
||
r.available_ts,
|
||
r.picked_up,
|
||
r.picked_up_ts,
|
||
r.notified,
|
||
r.notified_ts,
|
||
d.verificator_hash,
|
||
r.status,
|
||
r.modified,
|
||
r.created,
|
||
r.not_found_counter,
|
||
r.not_found_ts,
|
||
r.notification_counter
|
||
from
|
||
results r
|
||
inner join
|
||
device d on d.udid = r.udid
|
||
where
|
||
r.pgs = @pgs";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
|
||
|
||
MySqlDataReader reader = cmd.ExecuteReader();
|
||
|
||
if (reader != null && reader.HasRows)
|
||
{
|
||
result = new List<Models.Database.Results>();
|
||
|
||
while(reader.Read())
|
||
{
|
||
Models.Database.Results dbResults = this.ReadResults(reader);
|
||
|
||
if(dbResults != null)
|
||
{
|
||
result.Add(dbResults);
|
||
}
|
||
}
|
||
}
|
||
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @pgs = " + pgs, Log.Types.LOG);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "GetResults(string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Returns device by udid
|
||
/// </summary>
|
||
/// <param name="udid">udid (pk)</param>
|
||
/// <returns>Returns Device object</returns>
|
||
public Device GetDevice(string udid)
|
||
{
|
||
Device result = null;
|
||
|
||
try
|
||
{
|
||
if (!string.IsNullOrEmpty(udid))
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"select
|
||
d.udid,
|
||
d.device_token,
|
||
d.public_key,
|
||
d.verificator_hash,
|
||
d.pat_hash,
|
||
d.pin,
|
||
d.device_type,
|
||
d.created,
|
||
d.modified
|
||
from
|
||
device d
|
||
where
|
||
d.udid = @udid";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
|
||
MySqlDataReader reader = cmd.ExecuteReader();
|
||
|
||
if (reader != null && reader.HasRows && reader.Read())
|
||
{
|
||
result = this.ReadDevice(reader);
|
||
}
|
||
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @udid = " + udid, Log.Types.LOG);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "GetLastDeviceRecord(string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Checks if device exists
|
||
/// </summary>
|
||
/// <param name="udid">udid of device</param>
|
||
/// <returns>returns true if exists</returns>
|
||
public bool DeviceExists(string udid)
|
||
{
|
||
bool result = false;
|
||
|
||
try
|
||
{
|
||
if (!string.IsNullOrEmpty(udid))
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
|
||
/** insert device **/
|
||
cmd.CommandText = @"select count(*) cnt from device where udid = @udid";
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
|
||
string res = cmd.ExecuteScalar().ToString();
|
||
|
||
if (!string.IsNullOrEmpty(res))
|
||
{
|
||
int r = 0;
|
||
|
||
if (Int32.TryParse(res, out r))
|
||
{
|
||
result = (r > 0);
|
||
}
|
||
}
|
||
|
||
client.Close();
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "DeviceExists(string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Creates results in the database
|
||
/// </summary>
|
||
/// <param name="results">Results object</param>
|
||
/// <returns>returns true if success</returns>
|
||
public bool Create(Models.Database.Results results)
|
||
{
|
||
bool result = false;
|
||
|
||
try
|
||
{
|
||
if (results != null &&
|
||
!string.IsNullOrEmpty(results.PGS) &&
|
||
results.PGS.Length == 128 &&
|
||
!string.IsNullOrEmpty(results.PGS_HASH) &&
|
||
!string.IsNullOrEmpty(results.UDID) &&
|
||
(results.UDID.Length >= 10 && results.UDID.Length <= 64) &&
|
||
!string.IsNullOrEmpty(results.DeviceToken) &&
|
||
(results.DeviceToken.Length >= 10 && results.DeviceToken.Length <= 255) &&
|
||
results.DeviceType != DeviceTypes.NONE &&
|
||
!string.IsNullOrEmpty(results.ClientPublicKey) &&
|
||
(results.ClientPublicKey.Length >= 32 && results.ClientPublicKey.Length <= 64) &&
|
||
!string.IsNullOrEmpty(results.VerificationHash) &&
|
||
results.VerificationHash.Length == 128)
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
|
||
if(!this.DeviceExists(results.UDID) && !string.IsNullOrEmpty(results.PIN))
|
||
{
|
||
/** insert device **/
|
||
cmd.CommandText = @"insert into device (udid,
|
||
device_token,
|
||
public_key,
|
||
verificator_hash,
|
||
pin,
|
||
device_type)
|
||
|
||
values (@udid,
|
||
@device_token,
|
||
@public_key,
|
||
@verificator_hash,
|
||
@pin,
|
||
@device_type)";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("udid", results.UDID));
|
||
cmd.Parameters.Add(new MySqlParameter("device_token", results.DeviceToken));
|
||
cmd.Parameters.Add(new MySqlParameter("public_key", results.ClientPublicKey));
|
||
cmd.Parameters.Add(new MySqlParameter("verificator_hash", results.VerificationHash));
|
||
cmd.Parameters.Add(new MySqlParameter("pin", results.PIN));
|
||
cmd.Parameters.Add(new MySqlParameter("device_type", results.DeviceType));
|
||
|
||
cmd.ExecuteNonQuery();
|
||
|
||
/** log **/
|
||
Log.Write(cmd.CommandText + "@udid = " + results.UDID +
|
||
", @device_token = " + results.DeviceToken +
|
||
", @public_key = " + results.ClientPublicKey +
|
||
", @verificator_hash = " + results.VerificationHash.ToString() +
|
||
", @pin = " + results.PIN +
|
||
", @device_type = " + results.DeviceType,
|
||
Log.Types.LOG);
|
||
}
|
||
|
||
|
||
/** insert results **/
|
||
cmd = client.CreateCommand();
|
||
|
||
cmd.CommandText = @"insert into results (pgs,
|
||
pgs_hash,
|
||
udid,
|
||
client_public_key,
|
||
server_public_key,
|
||
available,
|
||
available_ts,
|
||
status,
|
||
file_checksum)
|
||
|
||
values (@pgs,
|
||
@pgs_hash,
|
||
@udid,
|
||
@client_public_key,
|
||
@server_public_key,
|
||
@available,
|
||
@available_ts,
|
||
@status,
|
||
@file_checksum)";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("pgs", results.PGS));
|
||
cmd.Parameters.Add(new MySqlParameter("pgs_hash", results.PGS_HASH));
|
||
cmd.Parameters.Add(new MySqlParameter("udid", results.UDID));
|
||
cmd.Parameters.Add(new MySqlParameter("client_public_key", results.ClientPublicKey));
|
||
cmd.Parameters.Add(new MySqlParameter("server_public_key", (!string.IsNullOrEmpty(results.ServerPublicKey) ? results.ServerPublicKey : DBNull.Value)));
|
||
cmd.Parameters.Add(new MySqlParameter("available", results.Available));
|
||
cmd.Parameters.Add(new MySqlParameter("available_ts", (results.AvailableTS.HasValue ? results.AvailableTS.Value : DBNull.Value)));
|
||
cmd.Parameters.Add(new MySqlParameter("status", results.Status.ToString()));
|
||
cmd.Parameters.Add(new MySqlParameter("file_checksum", (!string.IsNullOrEmpty(results.FileChecksum) ? results.FileChecksum : DBNull.Value)));
|
||
|
||
cmd.ExecuteNonQuery();
|
||
result = true;
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @pgs = " + results.PGS +
|
||
", @pgs_hash = " + results.PGS_HASH +
|
||
", @udid = " + results.UDID +
|
||
", @client_public_key = " + results.ClientPublicKey +
|
||
", @server_public_key = " + (!string.IsNullOrEmpty(results.ServerPublicKey) ? results.ServerPublicKey : "") +
|
||
", @available = " + results.Available.ToString() +
|
||
", @available_ts = " + (results.AvailableTS.HasValue ? results.AvailableTS.Value.ToString() : "") +
|
||
", @status = " + results.Status.ToString() +
|
||
", @file_checksum = " + (!string.IsNullOrEmpty(results.FileChecksum) ? results.FileChecksum : ""),
|
||
Log.Types.LOG);
|
||
}
|
||
}
|
||
else
|
||
{
|
||
Log.Critical(new Exception("Missing some of parameters to insert into results"), "ServiceOutside.Database.Controllers.Results", "Create(Models.Results)");
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "Create(Models.Results)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Updates status of results in the database
|
||
/// </summary>
|
||
/// <param name="pgs">pgs (partial pk)</param>
|
||
/// <param name="udid">udid (partial pk)</param>
|
||
/// <param name="available">available, that should be set</param>
|
||
/// <param name="server_public_key">server public key, that was used for the encryption by server side</param>
|
||
/// <param name="file_checksum">Checksum of file content(base64) before encrypted</param>
|
||
/// <param name="available_ts">timestamp of available, that should be set</param>
|
||
/// <param name="status">status of results</param>
|
||
/// <param name="pat_hash">patient aes hash that can be decrypted by service inside</param>
|
||
/// <returns>Returns true if success</returns>
|
||
public bool UpdateStatus(string pgs, string udid, bool available, string server_public_key, string file_checksum, DateTime available_ts, ResultsStatus status, string pat_hash = null)
|
||
{
|
||
bool result = false;
|
||
|
||
try
|
||
{
|
||
if (!string.IsNullOrEmpty(pgs) &&
|
||
pgs.Length == 128 &&
|
||
!string.IsNullOrEmpty(udid) &&
|
||
status != ResultsStatus.NONE)
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"update
|
||
results r
|
||
inner join
|
||
device d on d.udid = r.udid
|
||
set
|
||
r.available = @available,
|
||
r.available_ts = @available_ts,
|
||
r.status = @status,
|
||
r.server_public_key = @server_public_key,
|
||
r.file_checksum = @file_checksum,
|
||
r.picked_up = 0,
|
||
r.picked_up_ts = null,
|
||
r.modified = now(),
|
||
d.modified = now(),
|
||
d.pat_hash = @pat_hash
|
||
where
|
||
r.pgs = @pgs and
|
||
d.udid = @udid";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("available", available));
|
||
cmd.Parameters.Add(new MySqlParameter("available_ts", available_ts.ToString("yyyy-MM-dd HH:mm:ss")));
|
||
cmd.Parameters.Add(new MySqlParameter("status", status.ToString()));
|
||
cmd.Parameters.Add(new MySqlParameter("server_public_key", (!string.IsNullOrEmpty(server_public_key) ? server_public_key : DBNull.Value)));
|
||
cmd.Parameters.Add(new MySqlParameter("file_checksum", (!string.IsNullOrEmpty(file_checksum) ? file_checksum : DBNull.Value)));
|
||
cmd.Parameters.Add(new MySqlParameter("pat_hash", (!string.IsNullOrEmpty(pat_hash) ? pat_hash : DBNull.Value)));
|
||
cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
cmd.ExecuteNonQuery();
|
||
result = true;
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @available = " + available +
|
||
", @available_ts = " + available_ts.ToString("yyyy-MM-dd HH:mm:ss") +
|
||
", @status = " + status.ToString() +
|
||
", @server_public_key = " + (!string.IsNullOrEmpty(server_public_key) ? server_public_key : "" ) +
|
||
", @file_checksum = " + (!string.IsNullOrEmpty(file_checksum) ? file_checksum : "") +
|
||
", @pat_hash = " + (!string.IsNullOrEmpty(pat_hash) ? pat_hash : "") +
|
||
", @pgs = " + pgs +
|
||
", @udid = " + udid,
|
||
Log.Types.LOG);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "UpdateStatus(string, string, bool, string, string, DateTime, ResultsStatus, string=null)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Updates status to rejected
|
||
/// </summary>
|
||
/// <param name="pgs">pgs(partial pk), that should be updated</param>
|
||
/// <param name="udid">udid(partial pk), that should be updated</param>
|
||
/// <returns>returns true if success</returns>
|
||
public bool SetReject(string pgs, string udid)
|
||
{
|
||
bool result = false;
|
||
|
||
try
|
||
{
|
||
if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128 && !string.IsNullOrEmpty(udid))
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"update results set
|
||
status = @status,
|
||
available = 0,
|
||
available_ts = null,
|
||
file_checksum = null,
|
||
modified = now()
|
||
where
|
||
pgs = @pgs and
|
||
udid = @udid";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("status", ResultsStatus.REJECTED.ToString()));
|
||
cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
cmd.ExecuteNonQuery();
|
||
result = true;
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @status = " + ResultsStatus.REJECTED.ToString() +
|
||
", @pgs = " + pgs +
|
||
", @udid = " + udid,
|
||
Log.Types.LOG);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "SetReject(string, string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Updates status to NOT_FOUND
|
||
/// </summary>
|
||
/// <param name="pgs">pgs(partial pk), that should be updated</param>
|
||
/// <param name="udid">udid(partial pk), that should be updated</param>
|
||
/// <returns>returns true if success</returns>
|
||
public bool SetNotFound(string pgs, string udid)
|
||
{
|
||
bool result = false;
|
||
|
||
try
|
||
{
|
||
if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128 && !string.IsNullOrEmpty(udid))
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"update results set
|
||
status = @status,
|
||
available = 0,
|
||
available_ts = null,
|
||
file_checksum = null,
|
||
not_found_counter = not_found_counter + 1,
|
||
not_found_ts = now(),
|
||
modified = now()
|
||
where
|
||
pgs = @pgs and
|
||
udid = @udid";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("status", ResultsStatus.NOT_FOUND.ToString()));
|
||
cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
cmd.ExecuteNonQuery();
|
||
result = true;
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @status = " + ResultsStatus.NOT_FOUND.ToString() +
|
||
", @pgs = " + pgs +
|
||
", @udid = " + udid,
|
||
Log.Types.LOG);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "SetNotFound(string, string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Sets the notified flag to true and timestamp of notified flag
|
||
/// </summary>
|
||
/// <param name="pgs">pgs(partial pk), that should be updated</param>
|
||
/// <param name="udid">udid(partial pk), that should be updated</param>
|
||
/// <returns>returns true if success</returns>
|
||
public bool SetNotified(string pgs, string udid)
|
||
{
|
||
bool result = false;
|
||
|
||
try
|
||
{
|
||
if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128)
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"update results
|
||
set notified = 1,
|
||
notified_ts = now(),
|
||
notification_counter = notification_counter + 1,
|
||
modified = now()
|
||
where
|
||
pgs = @pgs and
|
||
udid = @udid";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
|
||
cmd.ExecuteNonQuery();
|
||
result = true;
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @pgs = " + pgs + ", @udid = " + udid, Log.Types.LOG);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "SetNotified(string, string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Sets counter of notification try
|
||
/// </summary>
|
||
/// <param name="pgs">pgs(partial pk), that should be updated</param>
|
||
/// <param name="udid">udid(partial pk), that should be updated</param>
|
||
/// <returns>returns true if success</returns>
|
||
public bool SetNotificationTry(string pgs, string udid)
|
||
{
|
||
bool result = false;
|
||
|
||
try
|
||
{
|
||
if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128)
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"update results set
|
||
notification_counter = notification_counter + 1,
|
||
modified = now()
|
||
where
|
||
pgs = @pgs and
|
||
udid = @udid";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
|
||
cmd.ExecuteNonQuery();
|
||
result = true;
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @pgs = " + pgs + ", @udid = " + udid, Log.Types.LOG);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "SetNotificationTry(string, string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Sets the pickedup flag to true and timestamp of pickedup flag
|
||
/// </summary>
|
||
/// <param name="pgs">pgs(partial pk), that should be updated</param>
|
||
/// <param name="udid">udid(partial pk), that should be updated</param>
|
||
/// <returns>returns true if success</returns>
|
||
public bool SetPickedUp(string pgs, string udid)
|
||
{
|
||
bool result = false;
|
||
|
||
try
|
||
{
|
||
if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128)
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"update results set picked_up = 1, picked_up_ts = now(), modified = now() where pgs = @pgs and udid = @udid";
|
||
cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
cmd.ExecuteNonQuery();
|
||
result = true;
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @pgs = " + pgs + ", @udid = " + udid, Log.Types.LOG);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "SetPickedUp(string, string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Delete results in the database
|
||
/// </summary>
|
||
/// <param name="pgs">pgs hash(partial pk)</param>
|
||
/// <param name="udid">udid hash(partial pk)</param>
|
||
/// <returns>returns true if success</returns>
|
||
public bool Delete(string pgs, string udid)
|
||
{
|
||
bool result = false;
|
||
|
||
try
|
||
{
|
||
if (!string.IsNullOrEmpty(pgs) && pgs.Length == 128 && !string.IsNullOrEmpty(udid))
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"delete from results where pgs = @pgs and udid = @udid";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("pgs", pgs));
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
|
||
cmd.ExecuteNonQuery();
|
||
result = true;
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @pgs = " + pgs + ", @udid = " + udid, Log.Types.LOG);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "Delete(string, string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Delete statuses from the database
|
||
///
|
||
/// Statuses must contain pgs and udid
|
||
/// </summary>
|
||
/// <returns>returns true if success</returns>
|
||
public bool RemoveStatuses(List<ServiceShared.Models.Database.Status> statuses)
|
||
{
|
||
bool result = false;
|
||
|
||
try
|
||
{
|
||
if (statuses != null && statuses.Count > 0)
|
||
{
|
||
foreach(ServiceShared.Models.Database.Status status in statuses)
|
||
{
|
||
result = this.Delete(status.pgs, status.udid);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "RemoveStatuses(List<Status>)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Removes all recors for the device
|
||
/// </summary>
|
||
/// <param name="udid">udid hash(partial pk)</param>
|
||
/// <param name="verificator_hash">verificator_hash, that validate the client permission</param>
|
||
/// <returns>returns true if success</returns>
|
||
public bool DeleteDevice(string udid, string verificator_hash)
|
||
{
|
||
bool result = false;
|
||
|
||
try
|
||
{
|
||
if (!string.IsNullOrEmpty(udid) && !string.IsNullOrEmpty(udid))
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"delete from device where udid = @udid and verificator_hash = @verificator_hash";
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
cmd.Parameters.Add(new MySqlParameter("verificator_hash", verificator_hash));
|
||
cmd.ExecuteNonQuery();
|
||
result = true;
|
||
client.Close();
|
||
Log.Write(cmd.CommandText + ", @udid = " + udid + ", @verificator_hash = " + verificator_hash, Log.Types.LOG);
|
||
|
||
/** Results will be removed by cascade action **/
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "Delete(string, string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Updates verificator hash for the device
|
||
/// </summary>
|
||
/// <param name="udid">udid hash(partial pk)</param>
|
||
/// <param name="pin">client pin</param>
|
||
/// <param name="old_verificator_hash">old verificator hash</param>
|
||
/// <param name="new_verificator_hash">new verificator hash</param>
|
||
/// <returns>returns true if success</returns>
|
||
public bool UpdateVerificatorHash(string udid, string pin, string old_verificator_hash, string new_verificator_hash)
|
||
{
|
||
bool result = false;
|
||
|
||
try
|
||
{
|
||
if (!string.IsNullOrEmpty(udid) &&
|
||
!string.IsNullOrEmpty(old_verificator_hash) && old_verificator_hash.Length == 128 &&
|
||
!string.IsNullOrEmpty(new_verificator_hash) && new_verificator_hash.Length == 128 &&
|
||
!string.IsNullOrEmpty(pin) && pin.Length == 5)
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"update device
|
||
set verificator_hash = @new_verificator_hash, pin = @pin
|
||
where
|
||
udid = @udid and
|
||
verificator_hash = @old_verificator_hash";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("new_verificator_hash", new_verificator_hash));
|
||
cmd.Parameters.Add(new MySqlParameter("pin", pin));
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
cmd.Parameters.Add(new MySqlParameter("old_verificator_hash", old_verificator_hash));
|
||
cmd.ExecuteNonQuery();
|
||
result = true;
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @new_verificator_hash = " + new_verificator_hash +
|
||
", @new_verificator_hash = " + old_verificator_hash +
|
||
", @udid = " + udid, Log.Types.LOG);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "UpdateVerificatorHash(string, string, string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Updates device token for client
|
||
/// </summary>
|
||
/// <param name="udid">udid hash(partial pk)</param>
|
||
/// <param name="verificator_hash">client verificator_hash</param>
|
||
/// <param name="device_token">device_token that should be updated</param>
|
||
/// <returns>returns true if success</returns>
|
||
public bool UpdateDeviceToken(string udid, string verificator_hash, string device_token)
|
||
{
|
||
bool result = false;
|
||
|
||
try
|
||
{
|
||
if (!string.IsNullOrEmpty(udid) &&
|
||
!string.IsNullOrEmpty(verificator_hash) && verificator_hash.Length == 128 &&
|
||
!string.IsNullOrEmpty(device_token))
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"update device
|
||
set device_token = @device_token
|
||
where
|
||
udid = @udid and
|
||
verificator_hash = @verificator_hash";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("device_token", device_token));
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
cmd.Parameters.Add(new MySqlParameter("verificator_hash", verificator_hash));
|
||
cmd.ExecuteNonQuery();
|
||
result = true;
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @device_token = " + device_token +
|
||
", @udid = " + udid +
|
||
", @verificator_hash = " + verificator_hash, Log.Types.LOG);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "UpdateDeviceToken(string, string, string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Check if device with verificator_hash has a record in the database
|
||
/// </summary>
|
||
// <param name="udid">udid hash(partial pk)</param>
|
||
/// <param name="verificator_hash">verificator_hash, that validate the client permission</param>
|
||
/// <returns>returns true if some of records could be found else false</returns>
|
||
public bool DeviceHasResults(string udid, string verificator_hash)
|
||
{
|
||
bool result = false;
|
||
|
||
try
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"select
|
||
count(*) cnt
|
||
from
|
||
results r
|
||
inner join
|
||
device d on d.udid = r.udid
|
||
where
|
||
r.udid = @udid and
|
||
d.verificator_hash = @verificator_hash";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
cmd.Parameters.Add(new MySqlParameter("verificator_hash", verificator_hash));
|
||
cmd.ExecuteNonQuery();
|
||
|
||
string res = cmd.ExecuteScalar().ToString();
|
||
|
||
if (!string.IsNullOrEmpty(res))
|
||
{
|
||
int r = 0;
|
||
|
||
if (Int32.TryParse(res, out r))
|
||
{
|
||
result = (r > 0);
|
||
}
|
||
}
|
||
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @udid = " + udid + ", @verificator_hash = " + verificator_hash, Log.Types.LOG);
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Error(ex, "ServiceOutside.Database.Controllers.Results", "DeviceHasResults(string, string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Returns results by udid
|
||
/// </summary>
|
||
/// <param name="udid">udid hash(partial pk)</param>
|
||
/// <param name="verificator_hash">verificator_hash, that validate the client permission</param>
|
||
/// <returns>returns true if some of records could be found else false</returns>
|
||
public List<ServiceShared.Models.Database.Results> GetDeviceResults(string udid, string verificator_hash)
|
||
{
|
||
List<ServiceShared.Models.Database.Results> result = null;
|
||
|
||
try
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"select
|
||
r.pgs,
|
||
r.pgs_hash,
|
||
d.udid,
|
||
d.device_token,
|
||
d.device_type,
|
||
r.client_public_key,
|
||
r.server_public_key,
|
||
r.file_checksum,
|
||
r.available,
|
||
r.available_ts,
|
||
r.picked_up,
|
||
r.picked_up_ts,
|
||
r.notified,
|
||
r.notified_ts,
|
||
d.verificator_hash,
|
||
d.pin,
|
||
r.status,
|
||
r.modified,
|
||
r.created,
|
||
r.not_found_counter,
|
||
r.not_found_ts,
|
||
r.notification_counter
|
||
from
|
||
device d
|
||
inner join
|
||
results r on r.udid = d.udid
|
||
where
|
||
d.udid = @udid and
|
||
d.verificator_hash = @verificator_hash";
|
||
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
cmd.Parameters.Add(new MySqlParameter("verificator_hash", verificator_hash));
|
||
|
||
MySqlDataReader reader = cmd.ExecuteReader();
|
||
|
||
if (reader != null && reader.HasRows)
|
||
{
|
||
result = new List<Models.Database.Results>();
|
||
|
||
while (reader.Read())
|
||
{
|
||
Models.Database.Results dbResults = this.ReadResults(reader);
|
||
|
||
if (dbResults != null)
|
||
{
|
||
result.Add(dbResults);
|
||
}
|
||
}
|
||
}
|
||
|
||
client.Close();
|
||
Log.Write(cmd.CommandText + ", @udid = " + udid + ", @verificator_hash = " + verificator_hash, Log.Types.LOG);
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Error(ex, "ServiceOutside.Database.Controllers.Results", "GetDeviceResults(string, string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Deletes not mapped results for x old days from the database
|
||
/// </summary>
|
||
/// <param name="oldDays">number of old days</param>
|
||
/// <returns>true if success</returns>
|
||
public bool CleanUp(int oldDays)
|
||
{
|
||
bool result = false;
|
||
|
||
try
|
||
{
|
||
if (oldDays > 1)
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = "delete from results where datediff(now(), created) > @olddays";
|
||
cmd.Parameters.Add(new MySqlParameter("olddays", oldDays));
|
||
cmd.ExecuteNonQuery();
|
||
result = true;
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @olddays = " + oldDays, Log.Types.LOG);
|
||
}
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Error(ex, "ServiceOutside.Database.Controllers.Results", "CleanUpNotMapped(int)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Count opened reults(not pickedup) for the udid
|
||
/// </summary>
|
||
/// <param name="results">Results with UDID and IMEI</param>
|
||
/// <returns></returns>
|
||
public int CountOpenedRequest(Models.Database.Results results)
|
||
{
|
||
int result = 0;
|
||
|
||
try
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"select
|
||
count(*) cnt
|
||
from
|
||
device d
|
||
inner join
|
||
results r on r.udid = d.udid
|
||
where
|
||
d.udid = @udid and
|
||
d.verificator_hash = @verificator_hash and
|
||
r.picked_up <> 1";
|
||
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("udid", results.UDID));
|
||
cmd.Parameters.Add(new MySqlParameter("verificator_hash", results.VerificationHash));
|
||
cmd.ExecuteNonQuery();
|
||
|
||
string res = cmd.ExecuteScalar().ToString();
|
||
|
||
if(!string.IsNullOrEmpty(res))
|
||
{
|
||
int r = 0;
|
||
|
||
if(Int32.TryParse(res, out r))
|
||
{
|
||
result = r;
|
||
}
|
||
}
|
||
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + "@udid = " + results.UDID +
|
||
"@verificator_hash = " + results.VerificationHash, Log.Types.LOG);
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Error(ex, "ServiceOutside.Database.Controllers.Results", "CountOpenedRequest(Models.Results)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Returns all of opened(still not pickedup) pgs´s requests for the device
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
public List<Status> GetOpened(string udid, string verificator_hash, int maxOpenedRequests = 10, int maxNotFoundTries = 2)
|
||
{
|
||
List<Status> result = null;
|
||
|
||
try
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"select
|
||
r.pgs,
|
||
r.pgs_hash,
|
||
d.udid,
|
||
d.device_token,
|
||
d.device_type,
|
||
r.client_public_key,
|
||
r.server_public_key,
|
||
r.file_checksum,
|
||
r.available,
|
||
r.available_ts,
|
||
r.picked_up,
|
||
r.picked_up_ts,
|
||
r.notified,
|
||
r.notified_ts,
|
||
d.verificator_hash,
|
||
r.status,
|
||
r.modified,
|
||
r.created,
|
||
r.not_found_counter,
|
||
r.not_found_ts,
|
||
r.notification_counter
|
||
from
|
||
device d
|
||
inner join
|
||
results r on r.udid = d.udid
|
||
where
|
||
d.udid = @udid and
|
||
d.verificator_hash = @verificator_hash and
|
||
r.picked_up <> 1
|
||
limit " + maxOpenedRequests;
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
cmd.Parameters.Add(new MySqlParameter("verificator_hash", verificator_hash));
|
||
|
||
MySqlDataReader reader = cmd.ExecuteReader();
|
||
|
||
if (reader != null && reader.HasRows)
|
||
{
|
||
if(result == null)
|
||
{
|
||
result = new List<Status>();
|
||
}
|
||
|
||
while(reader.Read())
|
||
{
|
||
Models.Database.Results results = this.ReadResults(reader);
|
||
|
||
if(results != null)
|
||
{
|
||
if(results.Status == ResultsStatus.NOT_FOUND)
|
||
{
|
||
Log.Debug("Found not found results try " + results.NotFoundCounter + "/" + maxNotFoundTries);
|
||
|
||
/** send only not found results if max tries has been reached **/
|
||
if (results.NotFoundCounter >= maxNotFoundTries)
|
||
{
|
||
result.Add(new Status(results));
|
||
}
|
||
}
|
||
else
|
||
{
|
||
result.Add(new Status(results));
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @udid = " + udid +
|
||
", @verificator_hash = " + verificator_hash, Log.Types.LOG);
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Error(ex, "ServiceOutside.Database.Controllers.Results", "GetOpened(string, string, int)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Returns pin of device
|
||
/// </summary>
|
||
/// <returns>returns pin of device </returns>
|
||
public string GetPIN(string udid, string verificator_hash)
|
||
{
|
||
string result = null;
|
||
|
||
try
|
||
{
|
||
using (MySqlConnection client = this._DbContext.CreateConnection())
|
||
{
|
||
client.Open();
|
||
|
||
MySqlCommand cmd = client.CreateCommand();
|
||
cmd.CommandText = @"select
|
||
d.pin
|
||
from
|
||
device d
|
||
where
|
||
d.udid = @udid and
|
||
d.verificator_hash = @verificator_hash";
|
||
|
||
cmd.Parameters.Add(new MySqlParameter("udid", udid));
|
||
cmd.Parameters.Add(new MySqlParameter("verificator_hash", verificator_hash));
|
||
|
||
MySqlDataReader reader = cmd.ExecuteReader();
|
||
|
||
if (reader != null && reader.HasRows)
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
result = (string)reader["pin"];
|
||
}
|
||
}
|
||
|
||
client.Close();
|
||
|
||
Log.Write(cmd.CommandText + ", @udid = " + udid +
|
||
", @verificator_hash = " + verificator_hash, Log.Types.LOG);
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Error(ex, "ServiceOutside.Database.Controllers.Results", "GetPIN(string, string)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Reads results from mysql data reader
|
||
/// </summary>
|
||
/// <param name="reader">MySqlDataReader with current connection context</param>
|
||
/// <returns>Returns Results object</returns>
|
||
private Models.Database.Results ReadResults(MySqlDataReader reader)
|
||
{
|
||
Models.Database.Results result = null;
|
||
|
||
try
|
||
{
|
||
if (reader["pgs"] != null && reader["pgs"] != DBNull.Value)
|
||
{
|
||
result = new Models.Database.Results();
|
||
result.PGS = (string)reader["pgs"];
|
||
result.PGS_HASH = (string)reader["pgs_hash"];
|
||
result.UDID = (string)reader["udid"];
|
||
result.DeviceToken = (string)reader["device_token"];
|
||
result.ClientPublicKey = (string)reader["client_public_key"];
|
||
result.ServerPublicKey = (reader["server_public_key"] != null && reader["server_public_key"] != DBNull.Value) ? (string)reader["server_public_key"] : null;
|
||
result.FileChecksum = (reader["file_checksum"] != null && reader["file_checksum"] != DBNull.Value) ? (string)reader["file_checksum"] : null;
|
||
result.Available = (reader["available"] != null && reader["available"] != DBNull.Value && Convert.ToInt32(reader["available"].ToString()) == 1);
|
||
result.AvailableTS = ((reader["available_ts"] != null && reader["available_ts"] != DBNull.Value) ? (DateTime)reader["available_ts"] : null);
|
||
result.PickedUp = (reader["picked_up"] != null && reader["picked_up"] != DBNull.Value && Convert.ToInt32(reader["picked_up"].ToString()) == 1);
|
||
result.PickedUpTS = (reader["picked_up_ts"] != null && reader["picked_up_ts"] != DBNull.Value) ? (DateTime)reader["picked_up_ts"] : null;
|
||
result.Notified = (reader["notified"] != null && reader["notified"] != DBNull.Value && Convert.ToInt32(reader["notified"].ToString()) == 1);
|
||
result.NotifiedTS = (reader["notified_ts"] != null && reader["notified_ts"] != DBNull.Value) ? (DateTime)reader["notified_ts"] : null;
|
||
result.VerificationHash = (string)reader["verificator_hash"];
|
||
result.Modified = (reader["modified"] != null && reader["modified"] != DBNull.Value) ? (DateTime)reader["modified"] : null;
|
||
result.Created = (DateTime)reader["created"];
|
||
|
||
string device_type = ((reader["device_type"] != null && reader["device_type"] != DBNull.Value) ? (string)reader["device_type"] : null);
|
||
|
||
DeviceTypes deviceType = DeviceTypes.NONE;
|
||
|
||
if (!string.IsNullOrEmpty(device_type) && Enum.TryParse(device_type, out deviceType) && deviceType != DeviceTypes.NONE)
|
||
{
|
||
result.DeviceType = deviceType;
|
||
}
|
||
|
||
string status = ((reader["status"] != null && reader["status"] != DBNull.Value) ? (string)reader["status"] : null);
|
||
|
||
ResultsStatus outStatus = ResultsStatus.NONE;
|
||
|
||
if (!string.IsNullOrEmpty(status) && Enum.TryParse(status, out outStatus) && outStatus != ResultsStatus.NONE)
|
||
{
|
||
result.Status = outStatus;
|
||
}
|
||
|
||
result.NotFoundCounter = (reader["not_found_counter"] != null && reader["not_found_counter"] != DBNull.Value ?Convert.ToInt32(reader["not_found_counter"].ToString()) : 0);
|
||
|
||
result.NotFoundTS = (reader["not_found_ts"] != null && reader["not_found_ts"] != DBNull.Value) ? (DateTime)reader["not_found_ts"] : null;
|
||
|
||
result.NotificationCounter = (reader["notification_counter"] != null && reader["notification_counter"] != DBNull.Value ? Convert.ToInt32(reader["notification_counter"].ToString()) : 0);
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "ReadResults(MySqlDataReader)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// Reads device from mysql data reader
|
||
/// </summary>
|
||
/// <param name="reader">MySqlDataReader with current connection context</param>
|
||
/// <returns>Returns device object</returns>
|
||
private Device ReadDevice(MySqlDataReader reader)
|
||
{
|
||
Device result = null;
|
||
|
||
try
|
||
{
|
||
if (reader["udid"] != null && reader["udid"] != DBNull.Value)
|
||
{
|
||
result = new Device();
|
||
result.UDID = (string)reader["udid"];
|
||
result.DeviceToken = (string)reader["device_token"];
|
||
result.VerificationHash = (string)reader["verificator_hash"];
|
||
result.ClientPublicKey = (string)reader["public_key"];
|
||
result.PIN = (string)reader["pin"];
|
||
result.PatHash = ((reader["pat_hash"] != null && reader["pat_hash"] != DBNull.Value) ? (string)reader["pat_hash"] : null);
|
||
|
||
string device_type = ((reader["device_type"] != null && reader["device_type"] != DBNull.Value) ? (string)reader["device_type"] : null);
|
||
|
||
Device.DeviceTypes deviceType = Device.DeviceTypes.NONE;
|
||
|
||
if (!string.IsNullOrEmpty(device_type) && Enum.TryParse(device_type, out deviceType) && deviceType != Device.DeviceTypes.NONE)
|
||
{
|
||
result.DeviceType = deviceType;
|
||
}
|
||
|
||
result.Modified = (reader["modified"] != null && reader["modified"] != DBNull.Value) ? (DateTime)reader["modified"] : null;
|
||
result.Created = (DateTime)reader["created"];
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Log.Critical(ex, "ServiceOutside.Database.Controllers.Results", "ReadDevice(MySqlDataReader)");
|
||
}
|
||
|
||
return result;
|
||
}
|
||
}
|
||
}
|