public async Task <ColumnOption> GetColumnOptionsForUserAndPage(string userName, string pageName) { var key = $"column.option.{userName}.{pageName}"; if (CacheHandler.Get(key, out ColumnOption cachedObject)) { return(cachedObject); } using (var Db = new AtmContext()) { var result = (await Db.Database.SqlQuery <string>("exec [up_p_getProfileCustom] @userName = {0}, @pageName = {1}", userName, pageName).ToListAsync()).FirstOrDefault(); if (result == null) { return(null); } var columnOption = JsonConvert.DeserializeObject <ColumnOption>(result); if (columnOption != null) { if (columnOption.AvailableColumns == null) { columnOption.AvailableColumns = new List <AvailableColumn>(); } if (columnOption.Columns == null) { columnOption.Columns = new List <Column>(); } CacheHandler.Add(columnOption, key); } return(columnOption); } }
public async Task <List <DriverHelper> > GetDriverHelpersAsync() { using (var Db = new AtmContext()) { return(await Db.Database.SqlQuery <DriverHelper>("exec [up_p_getDriverHelpers]").ToListAsync()); } }
public async Task <List <PayScale> > GetAll() { using (var Db = new AtmContext()) { return(await Db.Database .SqlQuery <PayScale>("exec [up_p_getPayScales]" ).ToListAsync()); } }
public Menu GetByUserName(string userName) { var key = $"{userName}.menu"; if (CacheHandler.Get(key, out Menu cachedObject)) { return(cachedObject); } else { var result = new Menu { Title = "ATM" }; using (var Db = new AtmContext()) { // Create a SQL command to execute the sproc var cmd = Db.Database.Connection.CreateCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = $"[dbo].[up_user_getMenuFunctions]"; cmd.Parameters.Add(new SqlParameter("@userName", userName)); try { // Run the sproc Db.Database.Connection.Open(); var reader = cmd.ExecuteReader(); // Read Blogs from the first result set result.Sections = ((IObjectContextAdapter)Db) .ObjectContext .Translate <MenuSection>(reader).ToList(); // Move to second result set reader.NextResult(); var items = ((IObjectContextAdapter)Db) .ObjectContext .Translate <MenuItem>(reader).ToList(); foreach (var section in result.Sections) { section.MenuItems.AddRange(items.Where(i => i.SectionId == section.SectionId)); } } finally { Db.Database.Connection.Close(); } CacheHandler.Add(result, key); return(result); } } }
public bool IsApprover(string userName) { using (var Db = new AtmContext()) { var result = Db.Database .SqlQuery <int>("exec [up_user_isApprover] @userName = {0}", userName ).ToList().FirstOrDefault(); return(result > 0); // false if 0 } }
public async Task <List <PayScaleRate> > GetRatesForCenterAndPayScale(int centerNo, int payScale) { using (var Db = new AtmContext()) { var result = await Db.Database .SqlQuery <PayScaleRate>("exec [up_p_getCenterPayScaleRates] @sygmaCenterNo = {0}, @payScaleId = {1}", centerNo, payScale ).ToListAsync(); return(result); } }
public async Task UpdateColumnOptionAsync(string userName, string pageName, string columnOption) { var key = $"column.option.{userName}.{pageName}"; using (var Db = new AtmContext()) { await Db.Database.ExecuteSqlCommandAsync( "[up_p_saveProfileCustom] @userName, @pageName, @Columns", new SqlParameter("@userName", userName), new SqlParameter("@pageName", pageName), new SqlParameter("@Columns", columnOption) ); CacheHandler.Remove(key); } }
public async Task <List <BasicCenter> > GetAll(string userName) { var key = $"center.list.{userName}"; if (CacheHandler.Get(key, out List <BasicCenter> cachedObject)) { return(cachedObject); } using (var Db = new AtmContext()) { var result = await Db.Database .SqlQuery <BasicCenter>("exec [up_getCenters] @userName = {0}", userName ).ToListAsync(); CacheHandler.Add(result, key); return(result); } }
public bool IsValiduser(string userName, string route) { var key = $"{userName}.{route}"; if (CacheHandler.Get(key, out bool cachedObject)) { return(cachedObject); } using (var Db = new AtmContext()) { var result = Db.Database .SqlQuery <bool>("exec [up_sec_isValidUser] @userName = {0}, @rootUrl = {1}", userName, route ).ToList().FirstOrDefault(); CacheHandler.Add(result, key); return(result); } }
public async Task <FormCriteria> GetFormCriteriaAsync(string userName) { using (var Db = new AtmContext()) { // Create command from the context in order to execute // the `GetReferrer` proc var command = Db.Database.Connection.CreateCommand(); command.CommandType = System.Data.CommandType.StoredProcedure; command.CommandText = $"[dbo].[up_p_getFormCriteria]"; command.Parameters.Add(new SqlParameter("@userName", userName)); var result = new FormCriteria(); try { Db.Database.Connection.Open(); var reader = await command.ExecuteReaderAsync(); // Drop down to the wrapped `ObjectContext` to get access to // the `Translate` method var objectContext = ((IObjectContextAdapter)Db).ObjectContext; result.Status = objectContext.Translate <FormStatus>(reader).ToList();//(reader, 0, MergeOption.AppendOnly); reader.NextResult(); result.FormType = objectContext.Translate <FormType>(reader).ToList();//, "Set2", MergeOption.AppendOnly); reader.NextResult(); result.Centers = objectContext.Translate <SygmaCenter>(reader).ToList();//, "Set3", MergeOption.AppendOnly); } finally { Db.Database.Connection.Close(); } return(result); } }
public async Task <List <PayrollForm> > GetFormsAsync(FormsQueryRequest request) { var param = new object[] { new SqlParameter("@formId", SqlDbType.Int) { Value = SqlInt32.Null, IsNullable = true }, new SqlParameter("@sygmaCenterNo", SqlDbType.Int) { Value = SqlInt32.Null, IsNullable = true }, new SqlParameter("@routeNo", SqlDbType.VarChar, 4) { Value = DBNull.Value, IsNullable = true }, new SqlParameter("@weekending", SqlDbType.DateTime) { Value = request.WeekEnding }, new SqlParameter("@fromDate", SqlDbType.DateTime) { Value = DBNull.Value, IsNullable = true }, new SqlParameter("@toDate", SqlDbType.DateTime) { Value = DBNull.Value, IsNullable = true }, new SqlParameter("@statusId", SqlDbType.Int) { Value = SqlInt32.Null, IsNullable = true }, new SqlParameter("@formTypeId", SqlDbType.Int) { Value = SqlInt32.Null, IsNullable = true }, new SqlParameter("@actualsUpdated", SqlDbType.Int) { Value = SqlInt32.Null, IsNullable = true }, new SqlParameter("@employeeString", SqlDbType.VarChar, 100) { Value = DBNull.Value, IsNullable = true }, new SqlParameter("@tractorString", SqlDbType.VarChar, 100) { Value = DBNull.Value, IsNullable = true }, new SqlParameter("@trailerString", SqlDbType.VarChar, 100) { Value = DBNull.Value, IsNullable = true }, new SqlParameter("@userName", SqlDbType.VarChar, 20) { Value = request.UserName } }; using (var Db = new AtmContext()) { var result = await Db.Database .SqlQuery <PayrollForm>("exec [up_p_getForms] @userName = {0}, @weekending = {1}", request.UserName, request.WeekEnding ).ToListAsync(); return(result); } }