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