/// <summary>Obtm todos os utilizadores com a role passada</summary>
public override int[] getUsers( int role )
{
string query = string.Format("select IDUsers from Users inner join UserRoles on Users.IDUsers = UserRoles.IDUser where UserRoles.IDRoles = {0}", role);
HttpContext.Current.Trace.Write("MySQL","Query String: " + query);
return fetchUsersIds( MySqlUtility.getQuery(query) );
}
/// <summary>Obtm todos os scans de um planeta</summary>
public override Scan[] GetScans(Planet scanner)
{
string query = string.Format("SELECT * FROM Scans WHERE SourcePlanetId={0} ORDER BY Id DESC LIMIT 0, 40", scanner.Id);
DataSet ds = MySqlUtility.getQuery(query);
return(DataSetToScans(ds));
}
/// <summary>Actualiza a data do ltimo login</summary>
public override void updateLastLogin( string userMail )
{
MySqlUtility.executeNonQuery(
string.Format("update Users set user_lastLogin = NOW() where user_mail = '{0}'",
userMail
)
);
}
/// <summary>Obtm todos ordenados por Ranking</summary>
public override User[] getUsersRanking()
{
int count = 50;
string query = string.Format("select * from Users order by user_rank desc limit 0, {0}", count);
DataSet ds = MySqlUtility.getQuery(query);
return PopulateUserRankings(count, ds, "IDUsers");
}
/// <summary>Obtm um scan dado o seu id</summary>
public override Scan GetScan(int id)
{
string query = string.Format("SELECT * FROM Scans WHERE Id={0}", id);
DataSet ds = MySqlUtility.getQuery(query);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(DataSetToScans(ds)[0]);
}
/// <summary>Modifica a password de um utilizador</summary>
public override bool resetPassword( string userMail, string newPassword )
{
if( !checkUser(userMail) ) {
return false;
}
MySqlUtility.executeNonQuery(
string.Format("update Users set user_pass = '******' where user_mail = '{1}'",
newPassword, userMail
)
);
return true;
}
/// <summary>Retorna as roles de um User dado o seu ID</summary>
public string[] getRoles( int id )
{
string query = string.Format("SELECT roles_roleName FROM `UserRoles` inner join `Roles` on UserRoles.IDRoles=Roles.IDRoles WHERE UserRoles.IDUser={0}", id);
DataSet ds = MySqlUtility.getQuery(query);
string[] roles = new string[ds.Tables[0].Rows.Count];
int i = 0;
foreach( DataRow row in ds.Tables[0].Rows ) {
roles[i++] = row[0].ToString();
}
return roles;
}
/// <summary>Obtm todos ordenados por Ranking</summary>
public override IList getInactiveUsers()
{
DateTime now = DateTime.Now;
int m = now.Month - 1;
if( m <= 0 ) {
m = 12;
}
string query = string.Format("select * from Users where user_ruler_id!=-1 and user_lastlogin < '{0}-{1}-{2}'", now.Year, GetNumber(m), GetNumber(now.Day));
Log.log(query);
DataSet ds = MySqlUtility.getQuery(query);
return PopulateUserRankings(ds.Tables[0].Rows.Count, ds, "IDUsers");
}
/// <summary>Obtm todos os scans de um jogador em todos os planetas</summary>
public override Scan[] GetScans(Ruler ruler)
{
StringWriter writer = new StringWriter();
writer.Write("SELECT * FROM Scans WHERE SourcePlanetId={0}", ruler.Planets[0].Id);
for (int i = 1; i < ruler.Planets.Length; ++i)
{
writer.Write(" or SourcePlanetId={0} ", ruler.Planets[i].Id);
}
writer.Write(" ORDER BY Id DESC LIMIT 0, 60");
DataSet ds = MySqlUtility.getQuery(writer.ToString());
return(DataSetToScans(ds));
}
/// <summary>Regista um scan</summary>
public override void Register(Scan scan)
{
string script = string.Format("INSERT INTO Scans VALUES('', {0}, '{1}', {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, {14} )",
scan.SourcePlanetId, scan.Target.ToString(), scan.ScanLevel,
scan.Intercepted?1:0, scan.Success?1:0,
scan.Turn,
scan.Culture, scan.HasCommsSatellite, scan.HasGate,
scan.HasStarGate, scan.HasStarPort, scan.InBattle, scan.NumberOfFleets,
scan.TargetPlanetOwner,
scan.TotalShips
);
Chronos.Utils.Log.log(script);
MySqlUtility.executeNonQuery(script);
}
/// <summary>Regista uma nova notícia</summary>
protected override void Register(Entry entry)
{
/*CREATE TABLE News (
* Id int NOT NULL auto_increment,
* Title varchar(100) NOT NULL,
* Content mediumtext NOT NULL,
* Date datetime NOT NULL default '0000-00-00 00:00:00',
* PRIMARY KEY (Id)
* ) TYPE=MyISAM;
*/
string query = string.Format("INSERT INTO News(Title, Content, Date) VALUES('{0}', '{1}', NOW())",
entry.Title,
entry.Content
);
MySqlUtility.executeNonQuery(query);
}
/// <summary>Retorna todas as roles da BD</summ<ry>
public override string[] getAllSectionsRolesFromDB(int section_id)
{
//string query = "select roles_roleName from SectionRoles inner join Roles on sectionRoles_role_id = IDRoles where sectionroles_section_id = " + section_id;
//DataSet roles = MySqlUtility.getQuery(query);
DataSet roles = MySqlUtility.getAll("Roles");
int results = roles.Tables[0].Rows.Count;
string[] rolesArray = new string[results];
for (int i = 0; i < results; ++i)
{
DataRow row = roles.Tables[0].Rows[i];
rolesArray[i] = (string)row["roles_roleName"];
}
return(rolesArray);
}
public override void setAllianceMembers( AllianceInfo info ){
string query = string.Format("SELECT * FROM `Users` where abs(user_alliance_id) = {0}", info.Id);
DataSet ds = MySqlUtility.getQuery(query);
ArrayList all = PopulateUsers(ds, "IDUsers");
ArrayList members = new ArrayList();
ArrayList wannabe = new ArrayList();
foreach( User user in all ) {
if( user.AllianceId < 0 ) {
wannabe.Add(user);
} else {
members.Add(user);
}
}
info.Members = members;
info.Wannabe = wannabe;
}
/// <summary>
/// carrega todas as excepes ocorridas para um array de excepes
/// </summary>
/// <returns></returns>
public override ExceptionInfo[] load()
{
DataSet dataset = MySqlUtility.getAll("Exceptions", "ORDER BY Id DESC LIMIT 0, 30");
int i = 0, count = dataset.Tables[0].Rows.Count;
ExceptionInfo[] exceptions = new ExceptionInfo[count];
foreach (DataRow row in dataset.Tables[0].Rows)
{
_exception = row;
exceptions[i++] = new ExceptionInfo(
(int)getField("id"),
(string)getField("Name"),
(DateTime)getField("Date"),
(string)getField("Message"),
(string)getField("StackTrace")
);
}
return(exceptions);
}
public override AllianceInfo Get(int id)
{
DataSet alliances = MySqlUtility.getQuery("select * from Alliance where alliance_id=" + id);
return((AllianceInfo)FromDataSet(alliances)[0]);
}
public override ArrayList GetAll()
{
DataSet alliances = MySqlUtility.getAll("Alliance");
return(FromDataSet(alliances));
}
/// <summary>
/// Carrega as seces existentes para uma base de dados
/// </summary>
/// <returns>um objecto do tipo SectionCollection com todas as collections</returns>
public override UtilityCollection getAllSectionsFromDB()
{
return(storeSections(MySqlUtility.getAll("Sections")));
}
/// <summary>Obtém as notícias existentes</summary>
protected override NewsList GetNewsFromDB()
{
DataSet ds = MySqlUtility.getQuery("SELECT * FROM News ORDER BY Date DESC LIMIT 0, 10");
return(NewsFromDataSet(ds));
}
/// <summary>
/// retorna a informao das namedPages da Base de Dados
/// </summary>
/// <returns>collection com as namedPages</returns>
public override UtilityCollection getAllNamedPagesFromDB()
{
DataSet result = MySqlUtility.getAll("NamedPage");
return(storeNamedPages(result));
}
/// <summary>
/// remove todas as excepes
/// </summary>
public override void removeAll()
{
MySqlUtility.executeNonQuery("DELETE FROM `Exceptions`");
}
/// <summary>Obtm a quantidade de utilizadores registados</summary>
public override int getUserCount()
{
return MySqlUtility.executeNonQuery("select count(*) from Users");
}