public TurnipInfo GetTurnipTableEntry(int id)
{
TurnipInfo turnipInfo = null;
try
{
string sqlString = "SELECT WeekNum, Id, Name, BuyPrice, SellPrices, Pattern, FirstTime FROM Turnips WHERE Id = @Id";
using SqliteConnection connection = new SqliteConnection(_connectionString);
connection.Open();
SqliteCommand command = connection.CreateCommand();
command.CommandText = sqlString;
command.Parameters.AddWithValue("@Id", id);
SqliteDataReader dataReader = command.ExecuteReader();
if (dataReader.Read())
{
turnipInfo = TurnipInfo.Create(dataReader);
}
connection.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(turnipInfo);
}
public bool AddPatternToRecord(int id, string name, PatternEnum pattern)
{
try
{
EnsureTableIsClear();
TurnipInfo turnipInfo = _turnipRepository.GetTurnipTableEntry(id);
if (turnipInfo != null) //Update
{
turnipInfo.Pattern = pattern;
_turnipRepository.UpdateTurnipTableEntry(turnipInfo);
}
else //Insert
{
_turnipRepository.InsertIntoTurnipsTable(new TurnipInfo()
{
WeekNum = _weekNum,
Id = id,
Name = name,
Pattern = pattern
});
}
return(true);
}
catch
{
return(false);
}
}
public List <TurnipInfo> GetAllTurnipsTableEntries()
{
List <TurnipInfo> turnipInfos = new List <TurnipInfo>();
try
{
string sqlString = "SELECT WeekNum, Id, Name, BuyPrice, SellPrices, Pattern, FirstTime FROM Turnips";
using SqliteConnection connection = new SqliteConnection(_connectionString);
connection.Open();
SqliteCommand command = connection.CreateCommand();
command.CommandText = sqlString;
SqliteDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
turnipInfos.Add(TurnipInfo.Create(dataReader));
}
connection.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(turnipInfos);
}
public void InsertIntoTurnipsTable(TurnipInfo turnipInfo)
{
try
{
string sqlString = "INSERT INTO Turnips (WeekNum, Id, Name, BuyPrice, SellPrices, Pattern, FirstTime) VALUES " +
"(@WeekNum, @Id, @Name, @BuyPrice, @SellPrices, @Pattern, @FirstTime)";
using SqliteConnection connection = new SqliteConnection(_connectionString);
connection.Open();
SqliteCommand command = connection.CreateCommand();
command.CommandText = sqlString;
command.Parameters.AddWithValue("@WeekNum", turnipInfo.WeekNum);
command.Parameters.AddWithValue("@Id", turnipInfo.Id);
command.Parameters.AddWithValue("@Name", turnipInfo.Name);
command.Parameters.AddWithValue("@BuyPrice", turnipInfo.BuyPrice);
command.Parameters.AddWithValue("@SellPrices", turnipInfo.SellPricesString());
command.Parameters.AddWithValue("@Pattern", turnipInfo.Pattern.ToString());
command.Parameters.AddWithValue("@FirstTime", turnipInfo.FirstTime);
command.ExecuteNonQuery();
connection.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
public bool AddFirstTimeToRecord(int id, string name, bool firstTime)
{
try
{
EnsureTableIsClear();
TurnipInfo turnipInfo = _turnipRepository.GetTurnipTableEntry(id);
if (turnipInfo != null) //Update
{
turnipInfo.FirstTime = firstTime;
_turnipRepository.UpdateTurnipTableEntry(turnipInfo);
}
else //Insert
{
_turnipRepository.InsertIntoTurnipsTable(new TurnipInfo()
{
WeekNum = _weekNum,
Id = id,
Name = name,
FirstTime = firstTime
});
}
return(true);
}
catch
{
return(false);
}
}
private void EnsureTableIsClear()
{
UpdateWeekNum();
TurnipInfo firstEntry = _turnipRepository.GetAllTurnipsTableEntries().FirstOrDefault();
if (firstEntry != null && firstEntry.WeekNum != _weekNum) //New week has started and we must clean out the table
{
_turnipRepository.DeleteAllTurnipTableEntries(); //Delete all entries for a new week
}
}
public bool AddOrUpdateSellPriceInDB(int id, string name, int price, DayOfWeek?dayOfWeek = null, bool?isMorning = null)
{
EnsureTableIsClear();
if (dayOfWeek == null || isMorning == null)
{
DateTime date = DateTimeOffsetter.ToUSCentralTime(DateTime.Now).DateTime;
dayOfWeek = date.DayOfWeek;
isMorning = date.Hour < 12;
}
TurnipInfo turnipInfo = GetTurnipEntry(id);
if (turnipInfo == null) //Need to create a new Turnip Entry
{
turnipInfo = new TurnipInfo()
{
Id = id,
Name = name,
WeekNum = _weekNum,
BuyPrice = -1 //Default value
};
_turnipRepository.InsertIntoTurnipsTable(turnipInfo);
}
//Get the count of sell prices and what count we expect to be at
int sellPricesCount = turnipInfo.SellPrices.Count();
int countAsOfDate = (int)dayOfWeek * 2;
countAsOfDate += (bool)isMorning ? -1 : 0;
if (sellPricesCount == countAsOfDate) //Overwrite
{
turnipInfo.SellPrices[turnipInfo.SellPrices.Count - 1] = price;
}
else if (sellPricesCount < countAsOfDate) //Add blank prices in
{
for (int counter = sellPricesCount; counter < countAsOfDate - 1; counter++)
{
turnipInfo.SellPrices.Add(-1);
}
turnipInfo.SellPrices.Add(price);
}
else if (sellPricesCount > countAsOfDate) //Overwrite the desired sell price
{
turnipInfo.SellPrices[countAsOfDate - 1] = price;
}
_turnipRepository.UpdateTurnipTableEntry(turnipInfo);
return(true);
}
private static string _baseUrl = "https://turnipprophet.io/?prices="; //Url goes ...?prices=100.97.85...
public static string GenerateTurnipUrl(TurnipInfo turnipInfo)
{
string response = $"{_baseUrl}{turnipInfo.BuyPriceUrlString()}.{turnipInfo.SellPricesUrlString()}";
if (turnipInfo.FirstTime)
response += "&first=true";
if (turnipInfo.Pattern != PatternEnum.Unknown)
response += $"&pattern={(int)turnipInfo.Pattern}";
return response;
}
public void UpdateTurnipTableEntry(TurnipInfo turnipInfo)
{
try
{
string sqlString = "UPDATE Turnips SET Name = @Name, BuyPrice = @BuyPrice, SellPrices = @SellPrices, Pattern = @Pattern, FirstTime = @FirstTime Where Id = @Id";
using SqliteConnection connection = new SqliteConnection(_connectionString);
connection.Open();
SqliteCommand command = connection.CreateCommand();
command.CommandText = sqlString;
command.Parameters.AddWithValue("@Name", turnipInfo.Name);
command.Parameters.AddWithValue("@BuyPrice", turnipInfo.BuyPrice);
command.Parameters.AddWithValue("@SellPrices", turnipInfo.SellPricesString());
command.Parameters.AddWithValue("@Pattern", turnipInfo.Pattern);
command.Parameters.AddWithValue("@FirstTime", turnipInfo.FirstTime);
command.Parameters.AddWithValue("@Id", turnipInfo.Id);
command.ExecuteNonQuery();
connection.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
public bool AddOrUpdateBuyPriceInTable(int id, string name, int buyPrice)
{
EnsureTableIsClear();
TurnipInfo turnipInfo = GetTurnipEntry(id);
if (turnipInfo != null) //This entry must be updated
{
turnipInfo.Name = name;
turnipInfo.BuyPrice = buyPrice;
_turnipRepository.UpdateTurnipTableEntry(turnipInfo);
}
else //This entry must be inserted
{
_turnipRepository.InsertIntoTurnipsTable(new TurnipInfo()
{
WeekNum = _weekNum,
Id = id,
Name = name,
BuyPrice = buyPrice
});
}
return(true);
}
