public IList <BankAccount> ListAccountsByLogin(string login)
{
using (var context = new HsrTechContext())
{
HsrTechADO connection = new HsrTechADO(context.Database.Connection.ConnectionString);
var data = connection.ExecuteQuery
($@"
select t1.*
from BankAccount t1
inner join Client t2
on t1.ClientId = t2.ClientId
where t2.Login = '******'
");
List <BankAccount> listAccounts = new List <BankAccount>();
foreach (var item in data)
{
var property = item as Dictionary <string, object>;
BankAccount account = new BankAccount()
{
Balance = Convert.ToDecimal(property.ValueAsDecimal("Balance")),
ClientId = int.Parse(property.ValueAsString("ClientId")),
Limit = int.Parse(property.ValueAsString("Limit")),
NumberAccount = int.Parse(property.ValueAsString("NumberAccount")),
OpenDate = Convert.ToDateTime(property.ValueAsDateTimeNullable("OpenDate"))
};
listAccounts.Add(account);
}
return(listAccounts);
}
}
public Statistics GetStatisticsByLogin(string login, StatisticsOptions options)
{
using (var context = new HsrTechContext())
{
HsrTechADO connection = new HsrTechADO(context.Database.Connection.ConnectionString);
var clientId = getClientIdByLogin(login, connection);
var select = $@"
COUNT(*) AS 'Count',
DATEPART(YEAR, OpenDate) AS 'Year',
DATEPART(MONTH, OpenDate) AS 'Month',
DATEPART(DAY, OpenDate) AS 'Day'
";
var groupby = $@"
DATEPART(DAY, OpenDate),
DATEPART(MONTH, OpenDate),
DATEPART(YEAR, OpenDate)
";
var orderby = $@"'Year', 'Month', 'Day'";
if (options.GroupMode == StatisticsOptionsGroupModes.HOURS)
{
select += ", DATEPART(HOUR, OpenDate) AS 'Hour'";
groupby = "DATEPART(HOUR, OpenDate), " + groupby;
orderby += ", 'Hour'";
}
else if (options.GroupMode == StatisticsOptionsGroupModes.MINUTES)
{
select += ", DATEPART(HOUR, OpenDate) AS 'Hour', DATEPART(MINUTE, OpenDate) AS 'Minute'";
groupby = "DATEPART(HOUR, OpenDate), DATEPART(MINUTE, OpenDate), " + groupby;
orderby += ", 'Hour', 'Minute'";
}
var data = connection.ExecuteQuery
($@" SELECT {select} FROM BankAccount WHERE ClientId = {clientId} GROUP BY {groupby} ORDER BY {orderby}");
List <StatisticsItem> listItems = new List <StatisticsItem>();
foreach (var item in data)
{
var property = item as Dictionary <string, object>;
StatisticsItem statistic = new StatisticsItem(
int.Parse(property.ValueAsString("Year")),
int.Parse(property.ValueAsString("Month")),
int.Parse(property.ValueAsString("Day")),
(int)options.GroupMode > 0 ? int.Parse(property.ValueAsString("Hour")) : 0,
(int)options.GroupMode > 1 ? int.Parse(property.ValueAsString("Minute")) : 0,
int.Parse(property.ValueAsString("Count"))
);
listItems.Add(statistic);
}
return(new Statistics(options, listItems));
}
}
public virtual void Delete(params T[] items)
{
using (var context = new HsrTechContext())
{
foreach (T item in items)
{
context.Entry(item).State = EntityState.Deleted;
}
context.SaveChanges();
}
}
public virtual void AddNoValidate(params T[] items)
{
using (var context = new HsrTechContext())
{
foreach (T item in items)
{
context.Entry(item).State = EntityState.Added;
}
context.Configuration.ValidateOnSaveEnabled = false;
context.SaveChanges();
}
}
public bool ValidateLogin(string login, string password)
{
using (var context = new HsrTechContext()) {
HsrTechADO connection = new HsrTechADO(context.Database.Connection.ConnectionString);
var data = connection.ExecuteQuery($"select count(*) as auth from Client where Login = '******' and Password = '******'");
var property = data[0] as Dictionary <string, object>;
int countUser = int.Parse(property.ValueAsString("auth"));
return(countUser == 0 ? false : true);
}
}
public void CreateAccount(decimal balance, int limit, string login)
{
using (var context = new HsrTechContext())
{
HsrTechADO connection = new HsrTechADO(context.Database.Connection.ConnectionString);
int clientID = getClientIdByLogin(login, connection);
var insertAccount = connection.ExecuteQuery
($@"
insert into BankAccount (OpenDate,Balance,ClientId,Limit) values (Getdate(),{balance.ToString().Replace(",", ".")},{clientID},{limit})
");
}
}
public bool Transfer(decimal value, int numberAccount, int typeTransfer, string login, int userNumberAccount)
{
try
{
using (var context = new HsrTechContext())
{
HsrTechADO connection = new HsrTechADO(context.Database.Connection.ConnectionString);
var query = connection.ExecuteQuery
($@"
select ClientId
from Client
where Login = '******'
");
var property = query[0] as Dictionary <string, object>;
int clientID = int.Parse(property.ValueAsString("ClientId"));
var query2 = connection.ExecuteQuery
($@"
select Balance
from BankAccount
where NumberAccount = {userNumberAccount}
");
var property2 = query2[0] as Dictionary <string, object>;
decimal balanceUser = property2.ValueAsDecimal("Balance");
if ((balanceUser - value) <= 0)
{
return(false);
}
else
{
var update = connection.ExecuteQuery
($@"
begin tran
update BankAccount set Balance = {(balanceUser - value).ToString().Replace(",", ".")} where ClientId = {clientID} and NumberAccount = {userNumberAccount}
update BankAccount set Balance = balance + {(value).ToString().Replace(",", ".")} where ClientId = {clientID} and NumberAccount = {numberAccount}
insert into HistoricalTransaction (NumberAccount,Date,Value,FlagTransaction) values ({userNumberAccount},Getdate(),{value.ToString().Replace(",", ".")},1);
commit
");
}
}
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public virtual T GetSingle(Func <T, bool> where, params Expression <Func <T, object> >[] navProperties)
{
T item = null;
using (var context = new HsrTechContext())
{
IQueryable <T> dbQuery = context.Set <T>();
foreach (Expression <Func <T, object> > navigationProperty in navProperties)
{
dbQuery = dbQuery.Include <T, object>(navigationProperty);
}
item = dbQuery.AsNoTracking().FirstOrDefault(where);
}
return(item);
}
public virtual IList <T> GetList(Func <T, bool> where, params Expression <Func <T, object> >[] navProperties)
{
List <T> list;
using (var context = new HsrTechContext())
{
IQueryable <T> dbQuery = context.Set <T>();
foreach (Expression <Func <T, object> > navigationProperty in navProperties)
{
dbQuery = dbQuery.Include <T, object>(navigationProperty);
}
list = dbQuery.AsNoTracking().Where(where).AsQueryable <T>().ToList <T>();
}
return(list);
}
public void CreateAccount(decimal balance, int limit, string login)
{
using (var context = new HsrTechContext())
{
HsrTechADO connection = new HsrTechADO(context.Database.Connection.ConnectionString);
var data = connection.ExecuteQuery
($@"
select ClientId
from Client
where Login = '******'
");
var property = data[0] as Dictionary <string, object>;
int clientID = int.Parse(property.ValueAsString("ClientId"));
var insertAccount = connection.ExecuteQuery
($@"
insert into BankAccount (OpenDate,Balance,ClientId,Limit) values (Getdate(),{balance.ToString().Replace(",", ".")},{clientID},{limit})
");
}
}
public virtual IList <T> GetAll(params Expression <Func <T, object> >[] navProperties)
{
List <T> list;
using (var context = new HsrTechContext())
{
IQueryable <T> dbQuery = context.Set <T>();
if (navProperties != null)
{
dbQuery = navProperties.Aggregate(dbQuery, (current, include) => current.Include(include));
}
foreach (Expression <Func <T, object> > navigationProperty in navProperties)
{
dbQuery = dbQuery.Include <T, object>(navigationProperty);
}
list = dbQuery.AsNoTracking().ToList <T>();
}
return(list);
}
public BankAccount GetAccountByNumberAccount(int numberAccount)
{
using (var context = new HsrTechContext())
{
HsrTechADO connection = new HsrTechADO(context.Database.Connection.ConnectionString);
var data = connection.ExecuteQuery
($@"
select t1.*
from BankAccount t1
where t1.NumberAccount = {numberAccount}
");
var property = data[0] as Dictionary <string, object>;
BankAccount account = new BankAccount()
{
Balance = Convert.ToDecimal(property.ValueAsDecimal("Balance")),
ClientId = int.Parse(property.ValueAsString("ClientId")),
Limit = int.Parse(property.ValueAsString("Limit")),
NumberAccount = int.Parse(property.ValueAsString("NumberAccount")),
OpenDate = Convert.ToDateTime(property.ValueAsDateTimeNullable("OpenDate"))
};
return(account);
}
}
public IList <AccountCreation> GetAccountsCreated(DateTime?startDate, DateTime?endDate, int type)
{
using (var context = new HsrTechContext())
{
HsrTechADO connection = new HsrTechADO(context.Database.Connection.ConnectionString);
var additionalFilter = String.Empty;
var additionalSelectHour = "+ '-' + STR(datepart(HOUR,t1.OpenDate)) +'h'";
var additionalSelectMinute = "+ ':' +STR(datepart(MINUTE,t1.OpenDate)) + 'm'";
var additionalGroupBy = String.Empty;
var additionalGroupByHour = ", STR(datepart(HOUR,t1.OpenDate))";
var additionalGroupByMinute = ", STR(datepart(MINUTE,t1.OpenDate))";
if (type == 2)
{
additionalFilter += additionalSelectHour;
additionalGroupBy += additionalGroupByHour;
}
else if (type == 3)
{
additionalFilter += additionalSelectHour + additionalSelectMinute;
additionalGroupBy += additionalGroupByHour + additionalGroupByMinute;
}
var dateConditional = String.Empty;
if (startDate.HasValue)
{
dateConditional += $" where t1.OpenDate >= '{startDate.Value.ToString("yyyy-MM-dd")}'";
}
if (endDate.HasValue)
{
dateConditional += dateConditional.Length > 0 ? " and" : " where ";
dateConditional += $" t1.OpenDate <= '{endDate.Value.ToString("yyyy-MM-dd")}'";
}
var query = $@"
select COUNT(*) AS QUANTITY,
REPLACE(
STR(datepart(YEAR,t1.OpenDate)) + '-' +
STR(datepart(MONTH,t1.OpenDate)) + '-' +
STR(datepart(DAY,t1.OpenDate))
{additionalFilter} , ' ', '')
as TIMESTAMP
from BankAccount t1
{dateConditional}
group by datepart(YEAR,t1.OpenDate),
datepart(MONTH,t1.OpenDate),
datepart(DAY,t1.OpenDate)
{additionalGroupBy}
";
var data = connection.ExecuteQuery(query);
var listAccounts = new List <AccountCreation>();
foreach (var item in data)
{
var property = item as Dictionary <string, object>;
var acc = new AccountCreation()
{
Quantity = Convert.ToInt32(property.ValueAsString("QUANTITY")),
Time = property.ValueAsString("TIMESTAMP")
};
listAccounts.Add(acc);
}
return(listAccounts);
}
}
public bool Transfer(decimal value, int numberAccount, int typeTransfer, string login, int userNumberAccount)
{
try
{
using (var context = new HsrTechContext())
{
decimal valorDisponivel;
HsrTechADO connection = new HsrTechADO(context.Database.Connection.ConnectionString);
var query = connection.ExecuteQuery
($@"
select ClientId
from Client
where Login = '******'
");
var property = query[0] as Dictionary <string, object>;
int clientID = int.Parse(property.ValueAsString("ClientId"));
//CONTA REMETENTE
var query2 = connection.ExecuteQuery
($@"
select Balance, Limit
from BankAccount
where NumberAccount = {userNumberAccount}
");
var property2 = query2[0] as Dictionary <string, object>;
decimal balanceUser = property2.ValueAsDecimal("Balance");
decimal limiteUser = property2.ValueAsDecimal("Limit");
//CONTA DESTINO
var queryAccountTarget = connection.ExecuteQuery
($@"
select Balance, Limit
from BankAccount
where NumberAccount = {numberAccount}
");
var property3 = queryAccountTarget[0] as Dictionary <string, object>;
decimal balanceUsertarget = property3.ValueAsDecimal("Balance");
decimal limiteUsertarget = property3.ValueAsDecimal("Limit");
if (typeTransfer == 0)
{
valorDisponivel = balanceUser;
}
else
{
valorDisponivel = limiteUser;
}
if ((valorDisponivel - value) < 0)
{
return(false);
}
else
{
//se for Conta corrente, altera o Balance
if (typeTransfer == 0)
{
var update = connection.ExecuteQuery
($@"
begin tran
update BankAccount set Balance = {(balanceUser - value).ToString().Replace(",", ".")} where ClientId = {clientID} and NumberAccount = {userNumberAccount} ;
update BankAccount set Balance = {(balanceUsertarget + value).ToString().Replace(",", ".")} where ClientId = {numberAccount} and NumberAccount = {numberAccount}
insert into HistoricalTransaction (NumberAccount,NumberAccountTarget,Date,Value,FlagTransaction) values ({userNumberAccount},{numberAccount},'{DateTime.Now.ToString("dd/MM/yyyy HH:mm")}','{value.ToString().Replace(",", ".")}',{typeTransfer});
commit
");
}
//Caso for credito, altera o limite
else
{
var update = connection.ExecuteQuery
($@"
begin tran
update BankAccount set Limit = {(limiteUser - value).ToString().Replace(",", ".")} where ClientId = {clientID} and NumberAccount = {userNumberAccount} ;
update BankAccount set Limit = {(limiteUsertarget + value).ToString().Replace(",", ".")} where ClientId = {numberAccount} and NumberAccount = {numberAccount}
insert into HistoricalTransaction (NumberAccount,NumberAccountTarget,Date,Value,FlagTransaction) values ({userNumberAccount},{numberAccount},'{DateTime.Now.ToString("dd/MM/yyyy HH:mm")}','{value.ToString().Replace(",", ".")}',{typeTransfer});
commit
");
}
}
}
return(true);
}
catch (Exception ex)
{
return(false);
}
}
