public SQLUploadID GenerateNewUploadEntry(Contributor _Contributor, DateTime _DateTime /* = DateTime.UtcNow*/)
{
var conn = OpenConnection();
try
{
using (var cmd = new NpgsqlCommand("INSERT INTO uploadtable(id, uploadtime, contributor) VALUES (DEFAULT, :UploadTime, :Contributor) RETURNING id", conn))
{
{
var uploadTimeParam = new NpgsqlParameter("UploadTime", NpgsqlDbType.Timestamp);
uploadTimeParam.Value = _DateTime;
cmd.Parameters.Add(uploadTimeParam);
}
{
var contributorParam = new NpgsqlParameter("Contributor", NpgsqlDbType.Integer);
contributorParam.Value = _Contributor.GetContributorID();
cmd.Parameters.Add(contributorParam);
}
using (var reader = cmd.ExecuteReader())
{
if (reader.Read() == true)
{
return(new SQLUploadID(reader.GetInt32(0)));
}
}
}
}
finally
{
CloseConnection();
}
return(new SQLUploadID(0));
}
public bool GetInspectsInfoForContributor(Contributor _Contributor, out DateTime _Earliest, out DateTime _Latest, out int _Count)
{
_Earliest = DateTime.MaxValue;
_Latest = DateTime.MinValue;
_Count = 0;
var conn = OpenConnection();
try
{
using (var cmd = new NpgsqlCommand("SELECT MIN(pd.updatetime), MAX(pd.updatetime), COUNT(*) FROM uploadtable up INNER JOIN playerdatatable pd ON up.id = pd.uploadid WHERE up.contributor = :ContributorID", conn))
{
cmd.Parameters.Add(new NpgsqlParameter("ContributorID", NpgsqlDbType.Integer)).Value = _Contributor.ContributorID;
using (var reader = cmd.ExecuteReader())
{
if (reader.Read() == true && reader.IsDBNull(0) == false)
{
_Earliest = reader.GetTimeStamp(0).DateTime;
_Latest = reader.GetTimeStamp(1).DateTime;
_Count = reader.GetInt32(2);
return(true);
}
}
}
}
finally
{
CloseConnection();
}
return(false);
}
public int GetRealmInspectsForContributor(Contributor _Contributor, WowRealm _Realm)
{
var conn = OpenConnection();
try
{
using (var cmd = new NpgsqlCommand("SELECT COUNT(*) FROM uploadtable up INNER JOIN playerdatatable pd ON up.id = pd.uploadid INNER JOIN playertable player ON player.id = pd.playerid WHERE up.contributor = :ContributorID AND player.realm = :Realm", conn))
{
cmd.Parameters.Add(new NpgsqlParameter("ContributorID", NpgsqlDbType.Integer)).Value = _Contributor.ContributorID;
cmd.Parameters.Add(new NpgsqlParameter("Realm", NpgsqlDbType.Integer)).Value = (int)_Realm;
using (var reader = cmd.ExecuteReader())
{
if (reader.Read() == true && reader.IsDBNull(0) == false)
{
return(reader.GetInt32(0));
}
}
}
}
finally
{
CloseConnection();
}
return(-1);
}
public DateTime GetLatestInspectForContributor(Contributor _Contributor)
{
var conn = OpenConnection();
try
{
using (var cmd = new NpgsqlCommand("SELECT MAX(pd.updatetime) FROM uploadtable up INNER JOIN playerdatatable pd ON up.id = pd.uploadid WHERE up.contributor = :ContributorID", conn))
{
cmd.Parameters.Add(new NpgsqlParameter("ContributorID", NpgsqlDbType.Integer)).Value = _Contributor.ContributorID;
using (var reader = cmd.ExecuteReader())
{
if (reader.Read() == true && reader.IsDBNull(0) == false)
{
return(reader.GetTimeStamp(0).DateTime);
}
}
}
}
finally
{
CloseConnection();
}
return(DateTime.MinValue);
}
