public static List<Card> GetAllCards()
{
String query = "SELECT * FROM cards;";
DBCore dbconnection = new DBCore();
DataTable dtCards = dbconnection.ExecuteQuery(query);
return ConvertTableToList(dtCards);
}
public static List<Keyword> GetLatestKeywords()
{
String query = "SELECT id, name, text, hasxvalue, guid, version FROM keywordabilities AS allkeywords WHERE version in (SELECT MAX(version) FROM keywordabilities WHERE guid = allkeywords.guid);";
DBCore dbconnection = new DBCore();
DataTable dtResults = dbconnection.ExecuteQuery(query);
return ConvertTableToKeywordList(dtResults);
}
public static List<Keyword> GetAllVersionsofKeyword(string keywordID)
{
String query = String.Format("SELECT id, name, text, hasxvalue, guid, version FROM keywordabilities WHERE guid = '{0}';", keywordID);
DBCore dbconnection = new DBCore();
DataTable dtResults = dbconnection.ExecuteQuery(query);
return ConvertTableToKeywordList(dtResults);
}
public static List<KeywordInstance> GetAbilitiesForCard(int cardID)
{
String query = "SELECT id, name, text, hasxvalue, guid, version, xvalue FROM keywordabilites INNER JOIN cardabilities WHERE id IN (SELECT keywordid FROM cardabilities WHERE cardid = {0});";
DBCore dbconnection = new DBCore();
DataTable dtResults = dbconnection.ExecuteQuery(query);
return ConvertTableToInstanceList(dtResults);
}
public static List<Card> GetAllLatestCards()
{
String query = @"SELECT * FROM cards AS allcards
WHERE version = (SELECT MAX(version) FROM cards WHERE guid = allcards.guid);";
DBCore dbconnection = new DBCore();
DataTable dtCards = dbconnection.ExecuteQuery(query);
return ConvertTableToList(dtCards);
}
public static bool SaveCardToDBVersioned(Card card)
{
String versionQuery = String.Format("SELECT MAX(version) FROM cards WHERE cards.guid = '{0}';", card.Guid);
DBCore dbConnection = new DBCore();
int version = dbConnection.ExecuteScalar(versionQuery) + 1;
int success = SaveCardToDBAsIs(card);
return success > 0;
}
public static List<Card> GetAllVersionsFromGUID(String guid)
{
String query = "SELECT * FROM cards WHERE guid={0} ORDER BY version ASC;";
DBCore dbConnection = new DBCore();
DataTable dtResult = dbConnection.ExecuteQuery(query);
List<Card> allVersions = new List<Card>();
foreach (DataRow cardRow in dtResult.Rows)
{
allVersions.Add(ConvertRowToCard(cardRow));
}
return allVersions;
}
public static Card GetLatestVersionFromGUID(String id)
{
String query = String.Format("SELECT * FROM cards WHERE guid='{0}' AND version in (SELECT MAX(version) FROM cards WHERE guid='{0}');", id);
DBCore dbConnection = new DBCore();
DataTable dtResult = dbConnection.ExecuteQuery(query);
if (dtResult == null || dtResult.Rows.Count == 0)
{
return null;
}
else
{
return ConvertRowToCard(dtResult.Rows[0]);
}
}
public static Keyword GetLatestVersionofKeywordbyID(string keywordID)
{
String query = "SELECT id, name, text, hasxvalue, guid, version FROM keywordabilities AS allkeywords WHERE id = '{0}' AND version in (SELECT MAX(version) FROM keywordabilities WHERE guid = allkeywords.guid);";
DBCore dbconnection = new DBCore();
DataTable dtResults = dbconnection.ExecuteQuery(query);
List<Keyword> results = ConvertTableToKeywordList(dtResults);
if (results == null || results.Count == 0)
{
return null;
}
else
{
return results[0];
}
}
public static int SaveKeyword(Keyword keywordToSave)
{
int version = 0;
DBCore dbConnection = new DBCore();
if (keywordToSave.Guid != String.Empty)
{
String versionQuery = String.Format("SELECT MAX(version) from keywordabilities WHERE guid = '{0}';", keywordToSave.Guid);
version = dbConnection.ExecuteScalar(versionQuery) + 1;
}
if (version == 0)
{
String guid = "KW-" + Guid.NewGuid().ToString().Replace("-", "").Substring(0, 12);
keywordToSave.Guid = guid;
}
keywordToSave.Version = version;
String abilityMarkup = new Serializer(typeof(Keyword)).Serialize(keywordToSave);
String insertQuery = String.Format(@"INSERT INTO keywordabilities
(guid, name, text, hasxvalue, version)
VALUES ('{0}', '{1}', '{2}', {3}, {4});",
keywordToSave.Guid, keywordToSave.Name, abilityMarkup, keywordToSave.HasXValue, keywordToSave.Version);
int keywordID = dbConnection.ExecuteInsertQuery(insertQuery);
return keywordID;
}
public static bool SaveKeywordInstance(int cardID, KeywordInstance instanceToSave)
{
String insertQuery = String.Format(@"INSERT INTO cardabilities
(cardid, keywordid, xvalue)
VALUES ({0}, {1}, {2});",
cardID, instanceToSave.ID, instanceToSave.XValue);
DBCore dbConnection = new DBCore();
int success = dbConnection.ExecuteInsertQuery(insertQuery);
return success > -1;
}
private static int SaveCardToDBAsIs(Card card)
{
if (card.Version == 0)
{
String guid = Guid.NewGuid().ToString().Replace("-", "").Substring(0, 12);
card.Guid = "C" + guid;
}
card.Flavor = card.Flavor.Replace("\"", "\\\"");
Serializer cardSerializer = new Serializer(typeof(Card));
String queryMarkup = cardSerializer.Serialize(card);
card.Flavor.Replace("\\\"", "\"");
// int deleteStart = queryMarkup.IndexOf("/*");
// int deleteEnd = queryMarkup.IndexOf("*/", deleteStart + 2);
// queryMarkup = queryMarkup.Remove(deleteStart, deleteEnd - deleteStart + 1);
DBCore dbConnection = new DBCore();
String query = String.Format("INSERT INTO cards (version, guid, editor, markup) VALUES ({0}, '{1}', '{2}', '{3}');", card.Version, card.ID, card.Creds.Designer, queryMarkup);
int success = dbConnection.ExecuteInsertQuery(query);
query = String.Format("SELECT LATEST_INSERT_ID()");
int newID = dbConnection.ExecuteScalar(query);
card.ID = newID;
AbilityDB.SaveAbilitiesOnCard(card);
return newID;
}