/// <summary>Obtém as notícias existentes por linguagem </summary>
protected override NewsList GetNewsFromDBByLang( string lang)
{
PostGre.PostGreParam [] param = new PostGreParam[1];
param[0] = new PostGreParam( lang,NpgsqlDbType.Varchar );
DataSet ds = PostGreServerUtility.getFromDB("OrionsBelt_GetNewsByLang", param);
return NewsFromDataSet(ds);
}
/// <summary>Regista uma nova notícia</summary>
protected override void Register( Entry entry )
{
PostGre.PostGreParam [] param = new PostGreParam[3];
param[0] = new PostGreParam( entry.Title,NpgsqlDbType.Varchar );
param[1] = new PostGreParam( entry.Content,NpgsqlDbType.Varchar );
param[2] = new PostGreParam( entry.Language,NpgsqlDbType.Varchar );
PostGreServerUtility.executeNonQuery2("OrionsBelt_InsertNews",param);
}
/// <summary>
/// Salva a informao da excepo
/// </summary>
/// <param name="exceptionInfo">objecto que encapsula a informao da excepo</param>
public override void save( ExceptionInfo exceptionInfo )
{
PostGreParam[] parameters = new PostGreParam[4];
parameters[0] = new PostGreParam( exceptionInfo.Name,NpgsqlDbType.Varchar, 100 );
parameters[1] = new PostGreParam( exceptionInfo.Message,NpgsqlDbType.Varchar, 3000 );
parameters[2] = new PostGreParam( exceptionInfo.StackTrace,NpgsqlDbType.Varchar, 3000 );
parameters[3] = new PostGreParam( exceptionInfo.Date,NpgsqlDbType.Timestamp );
PostGreServerUtility.executeNonQuery2( "OrionsBelt_ExceptionLogSave", parameters );
}
public override int Register( AllianceInfo info )
{
PostGre.PostGreParam [] param = new PostGreParam[5];
param[0] = new PostGreParam( info.Name ,NpgsqlDbType.Varchar,150 );
param[1] = new PostGreParam( info.Tag ,NpgsqlDbType.Varchar,150 );
param[2] = new PostGreParam( info.Motto ,NpgsqlDbType.Varchar,150 );
param[3] = new PostGreParam( info.Ranking ,NpgsqlDbType.Integer );
param[4] = new PostGreParam( info.RankingBattles ,NpgsqlDbType.Integer );
DataSet ds = PostGreServerUtility.getFromDB("OrionsBelt_AllianceRegisterAlliance", param );
return int.Parse(ds.Tables[0].Rows[0][0].ToString());
}
public override void Save( AllianceInfo info )
{
PostGre.PostGreParam [] param = new PostGreParam[6];
param[0] = new PostGreParam( info.Name ,NpgsqlDbType.Varchar,150 );
param[1] = new PostGreParam( info.Tag ,NpgsqlDbType.Varchar,150 );
param[2] = new PostGreParam( info.Motto ,NpgsqlDbType.Varchar,150 );
param[3] = new PostGreParam( info.Ranking ,NpgsqlDbType.Integer );
param[4] = new PostGreParam( info.RankingBattles ,NpgsqlDbType.Integer );
param[5] = new PostGreParam( info.Id ,NpgsqlDbType.Integer );
PostGreServerUtility.executeNonQuery2("orionsbelt_alliancesavealliance", param );
}
public override string checkUser(int id)
{
PostGre.PostGreParam [] param = new PostGreParam[1];
param[0] = new PostGreParam(id,NpgsqlDbType.Integer);
DataSet ds = PostGreServerUtility.getFromDB("OrionsBelt_UsersGetMailFromId",param);
if( ds.Tables.Count == 0 )
throw new AlnitakException("O Dataset deveria ter pelo menos 1 resultado @ SqlServerUserUtility::checkUser(int id)");
return ds.Tables[0].Rows[0]["user_mail"].ToString();
}
public override AllianceInfo Get( int id )
{
PostGre.PostGreParam [] param = new PostGreParam[1];
param[0] = new PostGreParam( id ,NpgsqlDbType.Integer );
DataSet alliances = PostGreServerUtility.getFromDB("OrionsBelt_AllianceGetAllianceById", param );
ArrayList allianceArray = FromDataSet(alliances);
if( allianceArray.Count > 0 ) {
return (AllianceInfo)allianceArray[0];
}
return null;
}
public override string[] getAllSectionsRolesFromDB( int section_id )
{
PostGre.PostGreParam [] param = new PostGreParam[1];
param[0] = new PostGreParam(section_id,NpgsqlDbType.Integer);
DataSet roles = PostGreServerUtility.getFromDB("OrionsBelt_SectionsGetAllSectionsRoles", param );
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[0];
}
return rolesArray;
}
public static void executeNonQuery(string procedure, Hashtable param)
{
NpgsqlConnection conn = new NpgsqlConnection(OrionGlobals.getConnectionString("connectionStringPG"));
NpgsqlCommand cmd = new NpgsqlCommand(procedure, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
if (param != null)
{
IDictionaryEnumerator iter = param.GetEnumerator();
while (iter.MoveNext())
{
if (iter.Value.GetType() == typeof(string))
{
cmd.Parameters.Add((string)iter.Key, (string)iter.Value);
}
else
{
PostGreParam p = (PostGreParam)iter.Value;
NpgsqlParameter pa;
if (p.HasSize)
{
pa = cmd.Parameters.Add((string)iter.Key, p.Type, p.Size);
}
else
{
pa = cmd.Parameters.Add((string)iter.Key, p.Type);
}
pa.Value = p.Value;
}
}
}
try {
conn.Open();
cmd.ExecuteNonQuery();
} catch (NpgsqlException e) {
throw new AlnitakException(String.Format("Excepcao a correr o SP '{0}' @ PostGreServerUtility::executeNonQuery - {1}", procedure, e.Message), e);
} finally {
conn.Close();
}
}
/// <summary>Regista um scan</summary>
public override void Register( Scan scan )
{
MemoryStream stream = new MemoryStream();
formatter.Serialize(stream, scan);
byte[] data = stream.ToArray();
PostGre.PostGreParam [] parameters = new PostGreParam[2];
parameters[0] = new PostGreParam(scan.SourcePlanetId,NpgsqlDbType.Integer);
parameters[1] = new PostGreParam(data,NpgsqlDbType.Bytea);
PostGreServerUtility.executeNonQuery2("OrionsBelt_InsertScans",parameters);
}
public override IList getInactiveUsers()
{
PostGre.PostGreParam [] param = new PostGreParam[1];
param[0] = new PostGreParam(DateTime.Now.AddMonths(-1),NpgsqlDbType.Date);
DataSet ds = PostGreServerUtility.getFromDB("OrionsBelt_UsersGetInative",param);
return PopulateInativeUsers(ds);
}
/// <summary>Retorna as roles de um User</summary>
public override string[] getRoles( string userMail )
{
PostGre.PostGreParam [] param = new PostGreParam[1];
param[0] = new PostGreParam(userMail,NpgsqlDbType.Varchar,40);
DataSet dataset = PostGreServerUtility.getFromDB("OrionsBelt_UsersGetUserRoles",param);
string[] roles = new string[dataset.Tables[0].Rows.Count];
int i = 0;
bool insertMade = false;
foreach ( DataRow role in dataset.Tables[0].Rows ) {
if( !(role[0] is System.DBNull) ) {
roles[i++] = (string)role[0];
insertMade = true;
}
}
if(insertMade) {
return roles;
}
return new string[0];
}
/// <summary>
/// Regista um User
/// </summary>
public override void registerUser( string nick, string mail, string pass )
{
PostGreParam[] param = new PostGreParam[4];
param[0] = new PostGreParam(mail,NpgsqlDbType.Varchar,30);
param[1] = new PostGreParam(hashPassword(pass),NpgsqlDbType.Varchar,40);
param[2] = new PostGreParam(nick,NpgsqlDbType.Varchar,30);
param[3] = new PostGreParam(CultureModule.RequestLanguage,NpgsqlDbType.Varchar,5);
PostGreServerUtility.executeNonQuery2("OrionsBelt_UsersRegisterUser", param);
}
/// <summary>
/// Guarda um User com os seus dados na Base de Dados
/// </summary>
public override void saveUser( User user, string password )
{
PostGreParam[] param = new PostGreParam[20];
param[0] = new PostGreParam(user.UserId,NpgsqlDbType.Integer);
if( password != string.Empty ) {
param[1] = new PostGreParam(hashPassword(password),NpgsqlDbType.Varchar,40);
}else {
param[1] = new PostGreParam("",NpgsqlDbType.Varchar,40);
}
param[2] = new PostGreParam(user.RulerId,NpgsqlDbType.Integer);
param[3] = new PostGreParam(user.Nick,NpgsqlDbType.Varchar,30);
param[4] = new PostGreParam(user.Skin,NpgsqlDbType.Integer);
param[5] = new PostGreParam(user.Lang,NpgsqlDbType.Varchar,5);
param[6] = new PostGreParam(user.Website,NpgsqlDbType.Varchar,30);
param[7] = new PostGreParam(user.Avatar,NpgsqlDbType.Varchar,250);
param[8] = new PostGreParam(user.Msn,NpgsqlDbType.Varchar,30);
param[9] = new PostGreParam(user.Icq,NpgsqlDbType.Varchar,30);
param[10] = new PostGreParam(user.Jabber,NpgsqlDbType.Varchar,30);
param[11] = new PostGreParam(user.Aim,NpgsqlDbType.Varchar,30);
param[12] = new PostGreParam(user.Yahoo,NpgsqlDbType.Varchar,30);
param[13] = new PostGreParam(user.Signature,NpgsqlDbType.Varchar,255);
param[14] = new PostGreParam(user.ImagesDir,NpgsqlDbType.Varchar,100);
param[15] = new PostGreParam(user.EloRanking,NpgsqlDbType.Integer);
param[16] = new PostGreParam(user.AllianceId,NpgsqlDbType.Integer);
param[17] = new PostGreParam(user.AllianceRank,NpgsqlDbType.Varchar,15);
param[18] = new PostGreParam(user.Wins,NpgsqlDbType.Integer);
param[19] = new PostGreParam(user.Losses,NpgsqlDbType.Integer);
PostGreServerUtility.executeNonQuery2("OrionsBelt_UsersUpdateUser",param);
}
public static DataSet getFromDB( string procedure, PostGreParam[] param )
{
if( param == null )
throw new AlnitakException("Tem de passar parmetros ao SP @ PostGreServerUtility::getFromDB");
NpgsqlDataAdapter NpgsqlDataAdapter = new NpgsqlDataAdapter( procedure , OrionGlobals.getConnectionString("connectionStringPG") );
if( procedure.ToLower().IndexOf("select") == -1 ) {
NpgsqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
}
NpgsqlDataAdapter.SelectCommand.CommandTimeout = 0;
int i = 0;
foreach(PostGreParam p in param ) {
NpgsqlParameter pa = NpgsqlDataAdapter.SelectCommand.Parameters.Add("$"+ i++, p.Type );
if(p.HasSize) {
pa.Size = p.Size;
}
pa.Value = p.Value;
}
DataSet dataSet = new DataSet();
try {
NpgsqlDataAdapter.Fill( dataSet );
} catch( NpgsqlException e ) {
throw new AlnitakException(String.Format("Excepcao a correr o SP '{0}' @ PostGreServerUtility::getFromDB - {1}",procedure,e.Message),e);
}
if( dataSet.Tables.Count == 0 )
throw new AlnitakException( procedure + " no retornou nenhum valor.");
return dataSet;
}
public static void executeNonQuery2( string procedure, PostGreParam[] param )
{
NpgsqlConnection conn = new NpgsqlConnection(OrionGlobals.getConnectionString("connectionStringPG"));
NpgsqlCommand cmd = new NpgsqlCommand(procedure, conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
if( param != null ) {
int i = 0;
foreach( PostGreParam p in param ) {
NpgsqlParameter pa = cmd.Parameters.Add( "$"+ i++ , p.Type );
if(p.HasSize) {
pa.Size = p.Size;
}
pa.Value = p.Value;
}
}
try {
conn.Open();
cmd.ExecuteNonQuery();
} catch( NpgsqlException e ) {
throw new AlnitakException(String.Format("Excepcao a correr o SP '{0}' @ PostGreServerUtility::executeNonQuery - {1}",procedure,e.Message),e);
} finally {
conn.Close();
}
}
public override string getAvatar( int rulerId )
{
PostGre.PostGreParam [] param = new PostGreParam[1];
param[0] = new PostGreParam(rulerId,NpgsqlDbType.Integer);
DataSet ds = PostGreServerUtility.getFromDB("OrionsBelt_UsersGetAvatar",param);
object o = ds.Tables[0].Rows[0][0];
if( ds.Tables[0].Rows.Count != 1 || o is System.DBNull ) {
return User.DefaultAvatar;
}
string s = o.ToString();
return s == string.Empty?User.DefaultAvatar:s;
}
public override void setAllianceMembers( AllianceInfo info )
{
PostGre.PostGreParam [] param = new PostGreParam[1];
param[0] = new PostGreParam(info.Id,NpgsqlDbType.Integer);
DataSet ds = PostGreServerUtility.getFromDB("OrionsBelt_AllianceGetAllianceMembers",param);
ArrayList all = PopulateUsers(ds, "user_id");
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;
}