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); } }