public static User FindByLoginId(string loginId)
{
using (var db = new CATBERT3Entities())
{
var user =
db.Database.SqlQuery<User>(
"SELECT DISTINCT users.* " +
"FROM Catbert3.dbo.Users AS users " +
"INNER JOIN Catbert3.dbo.Permissions permissions ON permissions.UserID = users.UserID " +
"INNER JOIN Catbert3.dbo.Applications AS apps ON permissions.ApplicationID = apps.ApplicationID " +
"WHERE apps.Abbr LIKE '" + ApplicationsAbbr +
"' AND permissions.Inactive = 0 ").FirstOrDefault(u => u.LoginID.Equals(loginId));
if (user != null)
{
user.Units = db.Database.SqlQuery<Unit>("SELECT unit.* " +
"FROM catbert3.dbo.Unit unit " +
"INNER JOIN Catbert3.dbo.UnitAssociations AS unitAssociations ON unit.UnitID = unitAssociations.UnitID " +
"INNER JOIN Catbert3.dbo.Applications AS applications ON unitAssociations.ApplicationID = applications.ApplicationID " +
"INNER JOIN Catbert3.dbo.Users AS users ON unitAssociations.UserID = users.UserID " +
"WHERE (applications.Abbr LIKE '" + ApplicationsAbbr + "') " +
" AND (unitAssociations.Inactive = 0) " +
" AND users.LoginID = '" + loginId + "'").ToList();
user.Roles =
db.Database.SqlQuery<Role>("SELECT roles.RoleID, roles.Role AS Role1, permissions.Inactive " +
"FROM catbert3.dbo.Roles roles " +
"INNER JOIN Catbert3.dbo.Permissions AS permissions ON roles.RoleID = permissions.RoleID " +
"INNER JOIN Catbert3.dbo.Applications AS applications ON permissions.ApplicationID = applications.ApplicationID " +
"INNER JOIN Catbert3.dbo.Users AS users ON permissions.UserID = users.UserID " +
"WHERE applications.Abbr LIKE '" + ApplicationsAbbr + "' " +
" AND permissions.Inactive = 0 " +
" AND users.LoginID = '" + loginId + "'").ToList();
return user;
}
return null;
}
}