public DataTable SelectByCardId(string cardId)
{
QueryLite query = new QueryLite(ConfigurationManager.ConnectionStrings["EbabobaConnectionString"].ConnectionString);
query.Add($"select * from Schedule where CardId = {cardId}");
var returnValue = query.ExecuteAndGet();
query.Clear();
return(returnValue);
}
public DataTable SelectAllIncome()
{
QueryLite query = new QueryLite(ConfigurationManager.ConnectionStrings["EbabobaConnectionString"].ConnectionString);
query.Add($"select * from History where IsIncome = 1");
var returnValue = query.ExecuteAndGet();
query.Clear();
return(returnValue);
}
public DataTable SelectAll()
{
QueryLite query = new QueryLite(ConfigurationManager.ConnectionStrings["EbabobaConnectionString"].ConnectionString);
query.Add($"select * from TransactionType");
var returnValue = query.ExecuteAndGet();
query.Clear();
return(returnValue);
}
public int SelectTypesCount()
{
QueryLite query = new QueryLite(ConfigurationManager.ConnectionStrings["EbabobaConnectionString"].ConnectionString);
query.Add($"select Count(TransactionTypeId) from TransactionType");
var returnValue = query.ExecuteAndGet();
query.Clear();
int count = returnValue.Rows.Count;
return(count);
}
public Currency(string id)
{
QueryLite query = new QueryLite(ConfigurationManager.ConnectionStrings["EbabobaConnectionString"].ConnectionString);
query.Add($"select * from Currency where @CurrencyId = {id}");
query.AddParameter("CurrencyId", id.ToString());
var dt = query.ExecuteAndGet();
this.CurrencyId = id;
this.Name = dt.Rows[0]["Name"].ToString();
query.Clear();
}
public TransactionType(string id)
{
QueryLite query = new QueryLite(ConfigurationManager.ConnectionStrings["EbabobaConnectionString"].ConnectionString);
query.Add($"select * from TransactionType where @TransactionTypeId = {id}");
query.AddParameter("TransactionTypeId", id.ToString());
var dt = query.ExecuteAndGet();
this.TransactionTypeId = id;
this.Name = dt.Rows[0]["Name"].ToString();
this.IsIncome = dt.Rows[0]["IsIncome"].ToString();
query.Clear();
}
public Schedule(string id)
{
QueryLite query = new QueryLite(ConfigurationManager.ConnectionStrings["EbabobaConnectionString"].ConnectionString);
query.Add($"select * from Schedule where ScheduleId = {id}");
query.AddParameter("ScheduleId", id.ToString());
var dt = query.ExecuteAndGet();
this.ScheduleId = id;
this.CardId = dt.Rows[0]["CardId"].ToString();
this.Date = dt.Rows[0]["Date"].ToString();
this.Frequency = dt.Rows[0]["Frequency"].ToString();
this.IsIncome = dt.Rows[0]["IsIncome"].ToString();
this.Sum = dt.Rows[0]["Sum"].ToString();
this.TypeId = dt.Rows[0]["TypeId"].ToString();
query.Clear();
}
public History(string id)
{
QueryLite query = new QueryLite("*Connection string here*");
query.Add($"select * from History where @id = {id}");
query.AddParameter("id", id.ToString());
var dt = query.ExecuteAndGet();
this.HistoryId = id;
this.CardId = dt.Rows[0]["CardId"].ToString();
this.Date = dt.Rows[0]["Date"].ToString();
this.IsIncome = dt.Rows[0]["IsIncome"].ToString();
this.Sum = dt.Rows[0]["Sum"].ToString();
this.TypeId = dt.Rows[0]["TypeId"].ToString();
this.CurrencyId = dt.Rows[0]["CurrencyId"].ToString();
query.Clear();
}
public DataTable GetTransactionsInfo(bool outcome, bool income, DateTime?dateBegin, DateTime?dateEnd, object type, object currency)
{
QueryLite query = new QueryLite(ConfigurationManager.ConnectionStrings["EbabobaConnectionString"].ConnectionString);
query.Add($"select tt.Name 'Тип', concat((case h.IsIncome when 1 then '+' else '-' end), h.Sum) 'Cумма', c.Name 'Валюта', h.Date 'Дата' from History h " +
$"join Currency c on h.CurrencyId = c.CurrencyId " +
$"join TransactionType tt on tt.TransactionTypeId = h.TypeId ");
var wheres = new Dictionary <string, string>();
if (type != null)
{
wheres.Add("h.TypeId", type.ToString());
}
if (currency != null)
{
wheres.Add("h.Currencyid", currency.ToString());
}
if (dateEnd != null)
{
query.Add($" and '{dateEnd.ToString()}' > h.Date");
}
if (dateBegin != null)
{
query.Add($" and '{dateBegin.ToString()}' < h.Date");
}
if (outcome && income)
{
query.Add(" and h.IsIncome in (1, 0)");
}
else if (!outcome && income)
{
query.Add(" and h.IsIncome = 1");
}
else if (outcome && !income)
{
query.Add(" and h.IsIncome = 0");
}
query.AddWheres(wheres);
var returnValue = query.ExecuteAndGet();
query.Clear();
return(returnValue);
}
public Dictionary <string, double> GetHistoryByCard(string cardId, bool isIncome)
{
Dictionary <string, double> cardHistory = new Dictionary <string, double>();
QueryLite query = new QueryLite(ConfigurationManager.ConnectionStrings["EbabobaConnectionString"].ConnectionString);
query.Add($"select Sum(Sum) as Sum, Date from History where CardId = @CardId and IsIncome = @IsIncome group by Date order by Date");
query.AddParameter("@CardId", cardId);
query.AddParameter("@isIncome", isIncome ? "1" : "0");
var dt = query.ExecuteAndGet();
foreach (DataRow row in dt.Rows)
{
cardHistory.Add(Convert.ToDateTime(row["Date"].ToString()).ToShortDateString(), double.Parse(row["Sum"].ToString()));
}
query.Clear();
return(cardHistory);
}