public TransactionResult <ICollection <SocialMediaAccount> > FindByUserId(int userId)
{
const string sql = "SELECT * FROM SocialMediaAccount WHERE " +
"User_FK = @userId";
var retVal = new TransactionResult <ICollection <SocialMediaAccount> >();
try
{
using (var connection = new MySqlConnection(Config.DatabaseConnectionString))
{
connection.Open();
retVal.Data = connection.Query <SocialMediaAccount>(sql,
new
{
userId
}).ToList();
}
retVal.Success = true;
foreach (var account in retVal.Data.Where(a => a != null))
{
var vendorRetVal = socialMediaVendorRepo.Find(account.VendorName);
account.Vendor = vendorRetVal.Data;
if (!vendorRetVal.Success)
{
retVal.Success = false;
retVal.Details = vendorRetVal.Details;
}
}
}
catch (Exception e)
{
Log.Error($"Could not perform FIND in SocialMediaAccount Table using UserId - '{userId}'", e);
retVal.Success = false;
retVal.Details = e.Message;
}
return(retVal);
}
public TransactionResult <int> Insert(Beer appObj)
{
const string sql = "INSERT INTO Beer (Brewery_FK, UntappdId, BreweryDbId, " +
"BeerName, ABV, Description, IBU, Style, ShortStyle, LabelUrl) " +
"VALUES " +
"(@BreweryId, @UntappdId, @BreweryDbId, @BeerName, " +
"@ABV, @Description, @IBU, @Style, @ShortStyle, @LabelUrl);" +
"SELECT LAST_INSERT_ID();";
var retVal = new TransactionResult <int>();
try
{
using (var connection = new MySqlConnection(Config.DatabaseConnectionString))
{
connection.Open();
retVal.Data = connection.Query <int>(sql, new
{
BreweryId = appObj.Brewery_FK,
appObj.UntappdId,
appObj.BreweryDbId,
appObj.BeerName,
appObj.ABV,
appObj.Description,
appObj.IBU,
appObj.Style,
appObj.ShortStyle,
appObj.LabelUrl
}).Single();
retVal.Success = true;
}
}
catch (Exception e)
{
Log.Error($"Could not insert '{JsonConvert.SerializeObject(appObj)}.'", e);
retVal.Success = false;
retVal.Details = e.Message;
}
return(retVal);
}
public override TransactionResult <UserBeerRanking> Update(UserBeerRanking appObj)
{
const string sql =
"UPDATE UserBeerRanking SET " +
"Score = @Score " +
"UserRankPosition = @UserRankPosition " +
"HaveTried = @HaveTried " +
"UserReview = @UserReview " +
"WHERE User_FK = @UserId " +
"AND Beer_FK = @BeerId";
var retVal = new TransactionResult <UserBeerRanking>();
try
{
using (var connection = new MySqlConnection(Config.DatabaseConnectionString))
{
connection.Open();
connection.Execute(sql,
new
{
UserId = appObj.User_FK,
appObj.Beer.BeerId,
appObj.Score,
appObj.UserRankPosition,
appObj.HaveTried,
appObj.UserReview
});
}
retVal.Success = true;
retVal.Data = appObj;
}
catch (Exception e)
{
Log.Error($"Could not update '{JsonConvert.SerializeObject(appObj)}.'", e);
retVal.Success = false;
retVal.Details = e.Message;
}
return(retVal);
}
public TransactionResult <ICollection <UserBeerRanking> > FindAllByBeerId(int id)
{
const string sql = "SELECT * FROM UserBeerRanking " +
"WHERE Beer_FK = @id";
var retVal = new TransactionResult <ICollection <UserBeerRanking> >();
try
{
using (var connection = new MySqlConnection(Config.DatabaseConnectionString))
{
connection.Open();
retVal.Data = connection.Query <UserBeerRanking>(sql,
new
{
id
}).ToList();
}
if (retVal.Data != null)
{
foreach (var beerRanking in retVal.Data)
{
if (beerRanking == null)
{
continue;
}
beerRanking.Beer = beerRepo.FindById(beerRanking.Beer_FK).Data;
}
}
retVal.Success = true;
}
catch (Exception e)
{
Log.Error($"Could not perform FIND using Beer_FK - '{id}'", e);
retVal.Success = false;
retVal.Details = e.Message;
}
return(retVal);
}
public TransactionResult <UserInfo> FindByUntappdId(int id)
{
const string sql = "SELECT * FROM UserInfo WHERE " +
"UntappdId = @Id";
var retVal = new TransactionResult <UserInfo>();
try
{
using (var connection = new MySqlConnection(Config.DatabaseConnectionString))
{
connection.Open();
retVal.Data = connection.Query <UserInfo>(sql,
new
{
id
}).FirstOrDefault();
}
if (retVal.Data != null)
{
var accounts = socialAccRepo.FindByUserId(retVal.Data.UserId).Data;
retVal.Data.SocialAccounts = accounts?.ToList();
var rankings = rankRepo.FindAllByUserId(retVal.Data.UserId).Data;
retVal.Data.BeerRankings = rankings?.ToList();
}
retVal.Success = true;
}
catch (Exception e)
{
Log.Error($"Could not perform FIND using UntappdId - '{id}'", e);
retVal.Success = false;
retVal.Details = e.Message;
}
return(retVal);
}
public TransactionResult <ICollection <UserBeerRanking> > FindAllByBreweryDbId(int id)
{
const string sql = "SELECT * FROM UserBeerRanking AS r " +
"INNER JOIN Beer AS b " +
"ON r.Beer_FK = b.BeerId " +
"WHERE b.BreweryDbId = @id";
var retVal = new TransactionResult <ICollection <UserBeerRanking> >();
try
{
using (var connection = new MySqlConnection(Config.DatabaseConnectionString))
{
retVal.Data = connection.Query <UserBeerRanking, Beer, UserBeerRanking>(
sql,
(ranking, beer) =>
{
ranking.Beer = beer;
return(ranking);
},
splitOn: "Beer_FK",
param:
new
{
id
}).Distinct().ToList();
}
retVal.Success = true;
}
catch (Exception e)
{
Log.Error($"Could not perform FIND using UntappdId - '{id}'", e);
retVal.Success = false;
retVal.Details = e.Message;
}
return(retVal);
}
public TransactionResult <UserBeerRanking> FindSingleByUserAndBeerId(int userId, int beerId)
{
const string sql = "SELECT * FROM UserBeerRanking " +
"WHERE User_FK = @UserId AND Beer_FK = @BeerId";
var retVal = new TransactionResult <UserBeerRanking>();
try
{
using (var connection = new MySqlConnection(Config.DatabaseConnectionString))
{
connection.Open();
retVal.Data = connection.Query <UserBeerRanking, Beer, UserBeerRanking>(sql,
(r, b) =>
{
r.Beer = b;
return(r);
},
splitOn: "Beer_FK",
param:
new
{
UserId = userId,
BeerId = beerId
}).FirstOrDefault();
retVal.Success = true;
}
}
catch (Exception e)
{
Log.Error($"Could not perform FIND using User_FK - '{userId}', Beer_FK - '{beerId}'", e);
retVal.Success = false;
retVal.Details = e.Message;
}
return(retVal);
}
public override TransactionResult <SocialMediaAccount> Update(SocialMediaAccount appObj, int userId)
{
const string sql =
"UPDATE SocialMediaAccount SET " +
"AccountId = @AccountId " +
"WHERE User_FK = @UserId " +
"AND Vendor_FK = @Vendor";
var retVal = new TransactionResult <SocialMediaAccount>();
try
{
using (var connection = new MySqlConnection(Config.DatabaseConnectionString))
{
connection.Open();
connection.Execute(sql,
new
{
UserId = userId,
appObj.AccountId,
appObj.Vendor.Vendor
});
}
retVal.Success = true;
retVal.Data = appObj;
}
catch (Exception e)
{
Log.Error($"Could not update '{JsonConvert.SerializeObject(appObj)}.'", e);
retVal.Success = false;
retVal.Details = e.Message;
}
return(retVal);
}
public TransactionResult <ICollection <Brewery> > FindAllBreweries(bool getBeers)
{
const string sql = "SELECT * FROM Brewery;";
var retVal = new TransactionResult <ICollection <Brewery> >();
try
{
using (var connection = new MySqlConnection(Config.DatabaseConnectionString))
{
connection.Open();
retVal.Data = connection.Query <Brewery>(sql).ToList();
}
if (getBeers)
{
foreach (var brewery in retVal.Data)
{
brewery.Beers = beerRepo.FindByBreweryId(brewery.BreweryId).Data.ToList();
}
}
retVal.Success = true;
}
catch (Exception e)
{
Log.Error(
$"Ran into an error while trying to retrieve 'All' breweries and {(getBeers ? "associated " : "no ")}beers.",
e);
retVal.Success = false;
retVal.Details = e.Message;
}
return(retVal);
}
public TransactionResult <Brewery> FindBreweryDbId(int id, bool getBeers)
{
const string sql = "SELECT * FROM Brewery WHERE " +
"BreweryDbId = @Id";
var retVal = new TransactionResult <Brewery>();
try
{
using (var connection = new MySqlConnection(Config.DatabaseConnectionString))
{
connection.Open();
retVal.Data = connection.Query <Brewery>(sql,
new
{
Id = id
}).FirstOrDefault();
}
if (retVal?.Data != null && getBeers)
{
retVal.Data.Beers = beerRepo.FindByBreweryId(retVal.Data.BreweryId).Data.ToList();
}
retVal.Success = true;
}
catch (Exception e)
{
Log.Error($"Could not perform FIND using BreweryDbId - {id}'", e);
retVal.Success = false;
retVal.Details = e.Message;
}
return(retVal);
}
public override TransactionResult <Brewery> Update(Brewery appObj)
{
const string sql = "UPDATE Brewery SET " +
"BreweryName = @BreweryName " +
"Address = @Address " +
"Phone = @Phone " +
"UntappdId = @UntappdId " +
"BreweryDbId = @BreweryDbId " +
"LabelUrl = @LabelUrl " +
"OrgType = @OrgType " +
"WHERE BreweryId = @BreweryId";
var retVal = new TransactionResult <Brewery>();
try
{
using (var connection = new MySqlConnection(Config.DatabaseConnectionString))
{
connection.Open();
connection.Execute(sql,
new
{
appObj.BreweryName,
appObj.Address,
appObj.Phone,
appObj.UntappdId,
appObj.BreweryDbId,
appObj.LabelUrl,
appObj.OrgType,
appObj.BreweryId
});
}
foreach (var beer in retVal.Data.Beers)
{
if (beer.BeerId < 100)
{
beer.Brewery_FK = retVal.Data.BreweryId;
var beerRet = beerRepo.Insert(beer);
if (beerRet.Success)
{
beer.BeerId = beerRet.Data;
}
}
}
retVal.Data = appObj;
retVal.Success = true;
}
catch (Exception e)
{
Log.Error($"Could not update '{JsonConvert.SerializeObject(appObj)}.'", e);
retVal.Success = false;
retVal.Details = e.Message;
}
return(retVal);
}
public override TransactionResult <UserInfo> Update(UserInfo appObj)
{
const string sql = "UPDATE UserInfo SET " +
"UserName = @UserName " +
"UserEmail = @UserEmail " +
"FirstName = @FirstName " +
"LastName = @LastName " +
"Location = @Location " +
"UntappdId = @UntappdId " +
"WHERE UserId = @UserId;";
var retVal = new TransactionResult <UserInfo>();
try
{
using (var connection = new MySqlConnection(Config.DatabaseConnectionString))
{
connection.Open();
connection.Execute(sql,
new
{
appObj.UserName,
appObj.UserEmail,
appObj.FirstName,
appObj.LastName,
appObj.Location,
appObj.UntappdId,
appObj.UserId
});
}
var userRankingCollection = new List <TransactionResult <UserBeerRanking> >();
appObj.BeerRankings.ForEach(r => r.User_FK = appObj.UserId);
foreach (var ranking in appObj.BeerRankings)
{
userRankingCollection.Add(rankRepo.Update(ranking));
}
var socialAccountsCollection = new List <TransactionResult <SocialMediaAccount> >();
foreach (var account in appObj.SocialAccounts)
{
socialAccountsCollection.Add(socialAccRepo.Update(account, appObj.UserId));
}
appObj.BeerRankings = userRankingCollection.Where(e => e.Success).Select(e => e.Data).ToList();
appObj.SocialAccounts = socialAccountsCollection.Where(e => e.Success).Select(e => e.Data).ToList();
retVal.Data = appObj;
retVal.Success = true;
}
catch (Exception e)
{
Log.Error($"Could not update '{JsonConvert.SerializeObject(appObj)}.'", e);
retVal.Success = false;
retVal.Details = e.Message;
}
return(retVal);
}
