public double GetDouble(int i)
{
return(_reader.GetDouble(i));
}
private void PopulateStarsChart(string zipcode, string city)
{
List <KeyValuePair <String, double> > data = new List <KeyValuePair <string, double> >();
using (NpgsqlConnection sqlconn = new NpgsqlConnection(MainWindow.login))
{//Start SQL interaction
sqlconn.Open();
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = sqlconn;
cmd.CommandText = "SELECT name, AVG(stars) From Category NATURAL JOIN (SELECT bid, stars FROM Business WHERE zipcode = '" + zipcode + "' AND city = '" + city + "') AS foo GROUP BY name;";
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
data.Add(new KeyValuePair <string, double>(reader.GetString(0), reader.GetDouble(1)));
}
}
AverageStarsGraph.DataContext = data;
}
sqlconn.Close();
}//End SQL interaction
}
public static int Acquire_Card(string i_username)
{
try
{
if (DBManagment.has_session(i_username) == false) //check if token is valid / has session
{
//throw new Exception("Not logged in / Invalid token");
return(1);
}
var con = new NpgsqlConnection(DBManagment.cs);
string sql = "Select coins from game_user where username = @username";
var cmd = new NpgsqlCommand(sql, con);
con.Open();
cmd.Parameters.AddWithValue("username", i_username);
cmd.Prepare();
NpgsqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
int player_coins = rdr.GetInt32(0);
rdr.Close();
if (player_coins >= 5)
{
if (Check_enough_cards_store() == true)
{
//select the store table and creating random numbers that are smaller than the number of cards in store
int number_of_available_cards = 0; // available cards in store
Random rnd = new Random();
int rnum = 0;
for (int i = 0; i < 5; i++) //5 random numbers are generated and the card with the same rownumer is added to the stack of the player (5 as a package)
{
number_of_available_cards = Get_number_of_card_store(); //getting the number of avalable cards each time after deleting one
rnum = rnd.Next(number_of_available_cards);
sql = "select row_number() OVER (ORDER BY card_id) AS row_id, st.* from store st limit 1 offset @num";
var cmd2 = new NpgsqlCommand(sql, con);
cmd2.Parameters.AddWithValue("num", rnum);
NpgsqlDataReader rdr2 = cmd2.ExecuteReader();
//the specific random card
rdr2.Read();
string card_id = rdr2.GetString(4);
string name = rdr2.GetString(1);
string card_type = rdr2.GetString(5);
ElementarType elementar_type = (ElementarType)Enum.Parse(typeof(ElementarType), Convert.ToString(rdr2.GetValue(2)));
double damage = rdr2.GetDouble(3);
rdr2.Close();
//add card to users stack
sql = "insert into all_user_cards (card_id, username, name, card_type, elementar_Type, damage) values (@card_id, @username, @name, @card_type, @elementar_type, @damage)";
var cmd3 = new NpgsqlCommand(sql, con);
cmd3.Parameters.AddWithValue("card_id", card_id);
cmd3.Parameters.AddWithValue("username", i_username);
cmd3.Parameters.AddWithValue("name", name);
cmd3.Parameters.AddWithValue("card_type", card_type);
cmd3.Parameters.AddWithValue("elementar_type", elementar_type);
cmd3.Parameters.AddWithValue("damage", damage);
cmd3.Prepare();
cmd3.ExecuteNonQuery();
////////
//delete from store beacause card is already sold
sql = "Delete from store where card_id = @id";
var cmd4 = new NpgsqlCommand(sql, con);
cmd4.Parameters.AddWithValue("id", card_id);
cmd4.Prepare();
cmd4.ExecuteNonQuery();
}
if (Decreasing_coins_from_user(i_username) == true)
{
return(0);
}
else
{
//throw new Exception("Error: can't decrease users coins");
return(3);
}
}
else
{
//throw new Exception("Error: not enough Cards in Store!");
return(2);
}
}
else
{
//throw new Exception("Error: Not enough Coins!");
return(4);
}
}
catch (Exception e)
{
Console.WriteLine("{0} Exception caught.", e);
return(5);
}
}
public string AquirePackage(string usertoken, string packageId)
{
User user;
ClientSingleton.GetInstance.ClientMap.TryGetValue(usertoken, out user);
if (user.Coins == 0)
{
return("POST ERR - No coins");
}
using NpgsqlConnection con = new NpgsqlConnection(_cs);
try
{
con.Open();
}
catch (PostgresException e)
{
return("POST ERR - No DB connection");
}
List <string> cardIds = null;
string sqlAllPackages = "SELECT * FROM packages";
using NpgsqlCommand cmdPackages = new NpgsqlCommand(sqlAllPackages, con);
using NpgsqlDataReader reader = cmdPackages.ExecuteReader();
while (reader.Read())
{
if (reader.GetString(0) == packageId)
{
try
{
cardIds = JsonSerializer.Deserialize <PackageDTO>(reader.GetString(1)).CardIds;
}
catch (Exception e)
{
cardIds = null;
}
}
}
reader.Close();
if (cardIds == null || cardIds.Count == 0)
{
return("POST ERR - Invalid PackageID");
}
CardPackage pack = new CardPackage();
string sqlAllCards = "SELECT * FROM cards";
using NpgsqlCommand cmdCards = new NpgsqlCommand(sqlAllCards, con);
using NpgsqlDataReader readerCards = cmdCards.ExecuteReader();
while (readerCards.Read())
{
foreach (var cardId in cardIds)
{
if (readerCards.GetString(0) == cardId)
{
CardDTO cardDto = new CardDTO(readerCards.GetString(0), readerCards.GetString(1), readerCards.GetString(2), readerCards.GetString(3), readerCards.GetDouble(4));
Card card = cardDto.ToCard();
pack.AddCard(card);
}
}
}
user.AddCardsToStack(pack);
readerCards.Close();
_userDataService.PersistUserData(user, usertoken);
return("POST OK - Package aquired");
}
public List <IElement> GetTours()
{
using NpgsqlConnection con = new NpgsqlConnection(_connectionString);
try
{
con.Open();
}
catch (NpgsqlException e)
{
_log.Error("No DB connection");
return(null);
}
string createSql = @"CREATE TABLE IF NOT EXISTS tours(tourid VARCHAR, tourname VARCHAR, description VARCHAR, tourstart VARCHAR, tourend VARCHAR, distance DOUBLE PRECISION)";
using NpgsqlCommand createCmd = new NpgsqlCommand(createSql, con);
createCmd.ExecuteNonQuery();
string sql = "SELECT * FROM tours";
using NpgsqlCommand cmd = new NpgsqlCommand(sql, con);
List <IElement> tours = new List <IElement>();
using NpgsqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
tours.Add(new Tour(Guid.Parse(rdr.GetString(0)), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4), rdr.GetDouble(5)));
}
rdr.Close();
_log.Info("Tours read from DB");
return(tours);
}
public List <Statistics> GetDateStatistics(Statistics _statistics)
{
float month;
float year;
float totalAnxiety;
Statistics statistic;
List <Statistics> dateList = new List <Statistics>();
try
{
conn = DAO.getConnection();
NpgsqlTransaction tran = conn.BeginTransaction();
NpgsqlCommand command = new NpgsqlCommand("estadistica_fechas(@initdate, @enddate)", conn);
NpgsqlParameter initDateConsult = new NpgsqlParameter();
NpgsqlParameter endDateConsult = new NpgsqlParameter();
initDateConsult.ParameterName = "@initdate";
endDateConsult.ParameterName = "@enddate";
initDateConsult.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;
endDateConsult.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;
initDateConsult.Direction = ParameterDirection.Input;
endDateConsult.Direction = ParameterDirection.Input;
initDateConsult.Value = _statistics._InitialDate;
endDateConsult.Value = _statistics._EndingDate;
command.Parameters.Add(initDateConsult);
command.Parameters.Add(endDateConsult);
command.CommandType = CommandType.StoredProcedure;
NpgsqlDataReader dr = command.ExecuteReader();
try
{
while (dr.Read())
{
month = (float)dr.GetDouble(0);
year = (float)dr.GetDouble(1);
totalAnxiety = (float)dr.GetDouble(2);
statistic = new Statistics(month, year, totalAnxiety);
dateList.Add(statistic);
}
dr.Close();
tran.Commit();
return(dateList);
}
catch (Exception ex)
{
throw ex;
}
}
catch (NpgsqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public override void DoWork(object idTaskStatus)
{
var session = Db <WebEnvironmentFiact> .NSession;
var status = session.Get <StatusTask>((int)idTaskStatus);
try
{
cls_IPSIAPI api = new PSI_API.cls_IPSIAPIClass();
var loginInfo = new typLoginInfo
{
strUser = ConfigurationManager.AppSettings["psiLogin"],
strPassword = ConfigurationManager.AppSettings["psiPassword"],
strUserClass = ConfigurationManager.AppSettings["psiUserClass"],
strView = ConfigurationManager.AppSettings["psiView"]
};
api.vbLogin(loginInfo);
using (OleDbConnection excelConnection = new OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", ConfigurationManager.AppSettings["eitpConfigFile"])))
{
excelConnection.Open();
using (OleDbCommand excelCommand = new OleDbCommand(String.Format("select * from [{0}$]", ConfigurationManager.AppSettings["configSheetName"]), excelConnection))
{
using (OleDbDataReader excelReader = excelCommand.ExecuteReader())
{
while (excelReader.Read())
{
if (excelReader.GetValue(5) == DBNull.Value)
{
continue;
}
String pbTag = excelReader.GetString(5) +
ConfigurationManager.AppSettings["pbTagSuffix"];
String pTag = excelReader.GetString(5) + ConfigurationManager.AppSettings["ptagSuffix"];
String qTag = excelReader.GetString(5) + ConfigurationManager.AppSettings["qTagSuffix"];
String tTag = excelReader.GetString(5) + ConfigurationManager.AppSettings["tTagSuffix"];
String internalTag = excelReader.GetString(1);
if (String.IsNullOrEmpty(internalTag))
{
continue;
}
using (NpgsqlConnection connection = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["eitp"].ConnectionString))
{
String sql = String.Format(ConfigurationManager.AppSettings["eitpDAYsql"],
internalTag);
try
{
using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
{
connection.Open();
using (NpgsqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader.GetValue(1) != DBNull.Value)
{
api.vbSetStdValue(pbTag, "D",
new typeValue
{
Wert = reader.GetDouble(1),
Zeit = reader.GetDateTime(0)
});
}
if (reader.GetValue(2) != DBNull.Value)
{
api.vbSetStdValue(pTag, "D",
new typeValue
{
Wert = reader.GetDouble(2),
Zeit = reader.GetDateTime(0)
});
}
if (reader.GetValue(3) != DBNull.Value)
{
api.vbSetStdValue(tTag, "D",
new typeValue
{
Wert = reader.GetDouble(3),
Zeit = reader.GetDateTime(0)
});
}
}
}
}
}
catch (COMException ex)
{
}
}
}
}
}
}
api.vbLogout();
session.Evict(status);
status = session.Get <StatusTask>(idTaskStatus);
status.TaskExecType = TaskExecType.Succeed;
}
catch (Exception e)
{
session.Evict(status);
status = session.Get <StatusTask>(idTaskStatus);
status.Error = e.ToString();
status.TaskExecType = TaskExecType.Failure;
}
status.EndRun = DateTime.Now;
session.Save(status);
session.Flush();
}
