public AccountTabModel GetAccountTabDataByUsername(string username)
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
var matchedPortfolios = (from p in db.GetTable <Portfolio>()
join s in db.GetTable <Stock>() on p.Ticker equals s.Ticker
where p.Username == username
select new { p, s }).ToList();
decimal stocksValue = 0;
foreach (var item in matchedPortfolios)
{
stocksValue += item.s.Price * item.p.NumStocks;
}
return((from a in db.GetTable <Account>()
where (a.Username == username)
select new AccountTabModel {
Username = a.Username,
StartingInvestment = a.StartingInvestment,
StocksValue = stocksValue,
AvailableCash = a.AvailableCash,
TotalValue = stocksValue + a.AvailableCash,
Position = (stocksValue + a.AvailableCash) - a.StartingInvestment,
TotalTrans = a.TotalTrans,
PositiveTrans = a.PositiveTrans,
NegativeTrans = a.NegativeTrans
}).SingleOrDefault());
}
public bool UpdateStock(string ticker, string equityName,
decimal price, decimal prevClosePrice)
{
try
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
Stock stock = db.Stocks.FirstOrDefault(x => x.Ticker == ticker);
if (stock == null)
{
return(false);
}
stock.EquityName = equityName;
stock.Price = price;
stock.PrevClosePrice = prevClosePrice;
db.SubmitChanges();
return(true);
}
catch
{
return(false);
}
}
public bool InsertNewTransaction(string ticker, string equityName,
DateTime date, string type,
long numStocks, decimal price,
decimal gainLossMoney,
decimal gainLossPercent)
{
try
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
Transaction transaction = new Transaction();
transaction.Ticker = ticker;
transaction.EquityName = equityName;
transaction.Date = date;
transaction.Type = type;
transaction.NumStocks = numStocks;
transaction.Price = price;
transaction.GainLossMoney = gainLossMoney;
transaction.GainLossPercent = gainLossPercent;
db.Transactions.InsertOnSubmit(transaction);
db.SubmitChanges();
return(true);
}
catch (Exception)
{
return(false);
}
}
public List <PortfolioTabModel> GetPortfoliosByUsername(string username)
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
//return db.Portfolios.FirstOrDefault(x => x.AccountID == accountID);
//if ((from a in db.GetTable<Account>()
// where (a.Username == username)
// select a.Username).ToString() == username)
//{
//}
return((from p in db.GetTable <Portfolio>()
join s in db.GetTable <Stock>() on p.Ticker equals s.Ticker
where p.Username == username
select new PortfolioTabModel
{
Ticker = s.Ticker,
EquityName = s.EquityName,
Price = s.Price,
Cost = p.Cost,
GainLossMoney = (s.Price - p.Cost) * p.NumStocks,
NumStocks = p.NumStocks,
ChangeMoney = s.Price - p.Cost,
Value = s.Price * p.NumStocks,
ChangePercent = Math.Round((((s.Price - p.Cost) / p.Cost) * 100), 3, MidpointRounding.AwayFromZero)
}).ToList());
}
public decimal GetPreviousClosePrice(string ticker)
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
return((from s in db.GetTable <Stock>()
where s.Ticker == ticker
select s.PrevClosePrice).Single());
}
public List <Stock> SearchStock(string searchData)
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
return((from s in db.GetTable <Stock>()
where ((s.Ticker.Contains(searchData)) || (s.EquityName.Contains(searchData)))
select s).ToList());
}
public bool Login(string username, string password)
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
string match = (from a in db.GetTable <Account>()
where (a.Username == username)
select a.Password).SingleOrDefault();
if (password == match && !string.IsNullOrWhiteSpace(password))
{
return(true);
}
return(false);
}
public bool DeleteStock(string ticker)
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
Stock stock = db.Stocks.FirstOrDefault(x => x.Ticker == ticker);
if (stock == null)
{
return(false);
}
db.Stocks.DeleteOnSubmit(stock);
db.SubmitChanges();
return(true);
}
public List <TransactionsTabModel> GetTransactionsByUserName(string username)
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
return((from t in db.GetTable <Transaction>()
from a in db.GetTable <Account>()
where ((t.Username == username) && (t.Username == a.Username))
select new TransactionsTabModel
{
Ticker = t.Ticker,
EquityName = t.EquityName,
Date = t.Date,
Type = t.Type,
NumStocks = t.NumStocks,
Price = t.Price,
GainLossMoney = t.GainLossMoney,
GainLossPercent = t.GainLossPercent
}).ToList());
}
public PortfolioTabModel GetPortfolioByUsernameAndTicker(string username, string ticker)
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
//return db.Portfolios.FirstOrDefault(x => x.AccountID == accountID);
return((from p in db.GetTable <Portfolio>()
join s in db.GetTable <Stock>() on p.Ticker equals s.Ticker
where ((p.Username == username) && (p.Ticker == ticker))
select new PortfolioTabModel
{
Ticker = s.Ticker,
EquityName = s.EquityName,
Price = s.Price,
Cost = p.Cost,
GainLossMoney = (s.Price - p.Cost) * p.NumStocks,
NumStocks = p.NumStocks,
ChangeMoney = s.Price - p.Cost,
Value = s.Price * p.NumStocks,
ChangePercent = Math.Round((((s.Price - p.Cost) / p.Cost) * 100), 3, MidpointRounding.AwayFromZero)
}).FirstOrDefault());
}
public bool InsertNewStock(string ticker, string equityName,
decimal price, decimal prevClosePrice)
{
try
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
Stock stock = new Stock();
stock.Ticker = ticker;
stock.EquityName = equityName;
stock.Price = price;
stock.PrevClosePrice = prevClosePrice;
db.Stocks.InsertOnSubmit(stock);
db.SubmitChanges();
return(true);
}
catch
{
return(false);
}
}
public bool InsertNewPortfolio(string username, string ticker, decimal cost,
int numStocks)
{
try
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
Portfolio portfolio = new Portfolio();
portfolio.Username = username;
portfolio.Ticker = ticker;
portfolio.Cost = cost;
portfolio.NumStocks = numStocks;
db.Portfolios.InsertOnSubmit(portfolio);
db.SubmitChanges();
return(true);
}
catch
{
return(false);
}
}
public bool DeletePortfolioByUsernameAndTicker(string username, string ticker)
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
var match = (from p in db.GetTable <Portfolio>()
where ((p.Username == username) && (p.Ticker == ticker))
select p).SingleOrDefault();
//Portfolio portfolio = db.Portfolios.FirstOrDefault(x => x.Username == username);
//if (portfolio == null) return false;
//db.Portfolios.DeleteOnSubmit(portfolio);
if (match == null)
{
return(false);
}
db.Portfolios.DeleteOnSubmit(match);
db.SubmitChanges();
return(true);
}
public bool SignUp(SignUpModel signUpInfo)
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
var matchedUser = (from a in db.GetTable <Account>()
where a.Username == signUpInfo.Username
select a).SingleOrDefault();
if (matchedUser == null)
{
try
{
Account account = new Account();
account.Username = signUpInfo.Username;
account.Password = signUpInfo.Password;
account.Fullname = signUpInfo.FullName;
account.FirstSecurityQuestion = signUpInfo.FirstSecurityQuestion;
account.FirstSecurityAnswer = signUpInfo.FirstSecurityAnswer;
account.SecondSecurityQuestion = signUpInfo.SecondSecurityQuestion;
account.SecondSecurityAnswer = signUpInfo.SecondSecurityAnswer;
account.StartingInvestment = 20000;
account.AvailableCash = 20000;
account.TotalTrans = 0;
account.PositiveTrans = 0;
account.NegativeTrans = 0;
db.Accounts.InsertOnSubmit(account);
db.SubmitChanges();
return(true);
}
catch (Exception)
{
return(false);
}
}
return(false);
}
public bool BuySell(string username, string ticker, long numStocks, string transactionType)
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
var matchedStock = (from s in db.GetTable <Stock>()
where s.Ticker == ticker
select s).SingleOrDefault();
var matchedUser = (from a in db.GetTable <Account>()
where a.Username == username
select a).SingleOrDefault();
if (transactionType.Equals(BUY, StringComparison.InvariantCultureIgnoreCase))
{
if (matchedUser.AvailableCash - (matchedStock.Price * numStocks) < 0) // Not Enough Available Cash for Buying.
{
return(false);
}
if (matchedStock != null && matchedUser != null)
{
try
{
Transaction transaction = new Transaction();
transaction.Username = username;
transaction.Ticker = matchedStock.Ticker;
transaction.EquityName = matchedStock.EquityName;
transaction.Date = DateTime.Now;
transaction.Type = BUY;
transaction.NumStocks = numStocks;
transaction.Price = matchedStock.Price;
db.Transactions.InsertOnSubmit(transaction);
var matchedPortfolio = (from p in db.GetTable <Portfolio>()
where ((p.Ticker == ticker) && (p.Username == username))
select p).SingleOrDefault();
if (matchedPortfolio == null)
{
Portfolio portfolio = new Portfolio();
portfolio.Username = username;
portfolio.Ticker = matchedStock.Ticker;
portfolio.Cost = matchedStock.Price;
portfolio.NumStocks = numStocks;
db.Portfolios.InsertOnSubmit(portfolio);
}
else
{
matchedPortfolio.Cost = Math.Round(((matchedPortfolio.NumStocks * matchedPortfolio.Cost) + (numStocks * matchedStock.Price)) / (matchedPortfolio.NumStocks + numStocks), 3, MidpointRounding.AwayFromZero);
matchedPortfolio.NumStocks += numStocks;
}
//var matchedPortfolios = (from p in db.GetTable<Portfolio>()
// join s in db.GetTable<Stock>() on p.Ticker equals s.Ticker
// where p.Username == username
// select new { p, s }).ToList();
//foreach (var item in matchedPortfolios)
//{
// matchedUser.StocksValue += item.s.Price * item.p.NumStocks;
//}
matchedUser.AvailableCash -= (matchedStock.Price * numStocks) + 10;
++matchedUser.TotalTrans;
db.SubmitChanges();
}
catch (Exception)
{
return(false);
}
return(true);
}
return(false);
}
if (transactionType.Equals(SELL, StringComparison.InvariantCultureIgnoreCase))
{
var matchedPortfolio = (from p in db.GetTable <Portfolio>()
where ((p.Ticker == ticker) && (p.Username == username))
select p).SingleOrDefault();
if (matchedPortfolio.NumStocks < numStocks) // Not Enough Available Shares for Selling.
{
return(false);
}
if (matchedStock != null && matchedUser != null && matchedPortfolio != null)
{
try
{
Transaction transaction = new Transaction();
transaction.Username = username;
transaction.Ticker = matchedStock.Ticker;
transaction.EquityName = matchedStock.EquityName;
transaction.Date = DateTime.Now;
transaction.Type = SELL;
transaction.NumStocks = numStocks;
transaction.Price = matchedStock.Price;
transaction.GainLossMoney = Math.Round((matchedStock.Price - matchedPortfolio.Cost) * numStocks, 3, MidpointRounding.AwayFromZero);
transaction.GainLossPercent = Math.Round((matchedStock.Price - matchedPortfolio.Cost) / matchedPortfolio.Cost, 3, MidpointRounding.AwayFromZero);
db.Transactions.InsertOnSubmit(transaction);
matchedPortfolio.NumStocks -= numStocks;
if (matchedPortfolio.NumStocks == 0)
{
db.Portfolios.DeleteOnSubmit(matchedPortfolio);
}
matchedUser.AvailableCash += (matchedStock.Price * numStocks) - 10;
++matchedUser.TotalTrans;
if (transaction.GainLossMoney > 0)
{
++matchedUser.PositiveTrans;
}
else if (transaction.GainLossMoney < 0)
{
++matchedUser.NegativeTrans;
}
db.SubmitChanges();
}
catch (Exception)
{
return(false);
}
return(true);
}
return(false);
}
return(false);
}
public Stock GetStock(string ticker)
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
return(db.Stocks.FirstOrDefault(x => x.Ticker == ticker));
}
public List <Stock> GetStockList()
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
return(db.Stocks.ToList());
}
public List <HistoryPriceModel> GetHistoryPrice(string ticker, string historyType)
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
var now = DateTime.Now;
var timeMark = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0);
switch (historyType)
{
case "1w":
var oneWeek = DateTime.Now.AddDays(-7);
timeMark = new DateTime(oneWeek.Year, oneWeek.Month, oneWeek.Day, 0, 0, 0);
break;
case "1m":
var oneMonth = DateTime.Now.AddMonths(-1);
timeMark = new DateTime(oneMonth.Year, oneMonth.Month, oneMonth.Day, 0, 0, 0);
break;
case "3m":
var threeMonths = DateTime.Now.AddMonths(-3);
timeMark = new DateTime(threeMonths.Year, threeMonths.Month, threeMonths.Day, 0, 0, 0);
break;
case "6m":
var sixMonths = DateTime.Now.AddMonths(-6);
timeMark = new DateTime(sixMonths.Year, sixMonths.Month, sixMonths.Day, 0, 0, 0);
break;
case "1y":
var oneYear = DateTime.Now.AddYears(-1);
timeMark = new DateTime(oneYear.Year, oneYear.Month, oneYear.Day, 0, 0, 0);
break;
case "2y":
var twoYears = DateTime.Now.AddYears(-2);
timeMark = new DateTime(twoYears.Year, twoYears.Month, twoYears.Day, 0, 0, 0);
break;
case "5y":
var fiveYears = DateTime.Now.AddYears(-5);
timeMark = new DateTime(fiveYears.Year, fiveYears.Month, fiveYears.Day, 0, 0, 0);
break;
case "max":
timeMark = new DateTime();
break;
default:
break;
}
return((from h in db.GetTable <History>()
where ((h.Ticker == ticker) && (h.Time >= timeMark))
orderby h.Time ascending
select new HistoryPriceModel
{
Time = h.Time,
Price = h.HistoryPrice
}).ToList());
}
public List <InsiderTrade> GetAllInsiderTrades()
{
DBStockTrainerDataContext db = new DBStockTrainerDataContext();
return(db.InsiderTrades.ToList());
}
