/// <summary>
/// Author: Ibrahim Abuzaid
/// Data Transfer Object to represent a User from the
/// Database
///
/// Added 3/4 By Ibarahim
/// Updated Stored Procedure and Method Name 4/14/16 Emily
///
/// Updated method - SP calls for active 4/21/16 Steve Hoover
/// </summary>
public static List <User> RetrieveUserList(Active active)
{
var user = new List <User>();
int _active = 0;
var conn = DBConnection.GetDBConnection();
var query = @"Admin.spSelectUsers";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = CommandType.StoredProcedure;
if (active == Active.active)
{
_active = 1;
}
cmd.Parameters.AddWithValue("@Active", _active);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
User currentUser = new User()
{
UserID = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
Zip = reader.GetString(3),
EmailAddress = reader.GetString(4),
UserName = reader.GetString(5),
Password = reader.GetString(6),
Active = reader.GetBoolean(7)
};
if (reader.IsDBNull(3))
{
currentUser.Zip = null;
}
else
{
currentUser.Zip = reader.GetString(3);
}
if (reader.IsDBNull(4))
{
currentUser.EmailAddress = null;
}
else
{
currentUser.EmailAddress = reader.GetString(4);
}
if (reader.IsDBNull(8))
{
currentUser.RegionId = null;
}
else
{
currentUser.RegionId = reader.GetInt32(8);
}
user.Add(currentUser);
}
}
else
{
throw new ApplicationException("Data not found");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(user);
}
/// <summary>
/// Rhett Allen
/// Created: 2016/02/26
///
/// Get a single user based on the id in the database
/// </summary>
/// <param name="userID">The UserID in the database</param>
/// <returns>The specified plant object</returns>
public static User RetrieveUserByID(int userID)
{
User user = new User();
var conn = DBConnection.GetDBConnection();
var query = @"Admin.spSelectUser";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserID", userID);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
user = new User()
{
UserID = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
UserName = reader.GetString(5),
Password = reader.GetString(6),
Active = reader.GetBoolean(7)
};
// Rhett Allen 3/6/16 - changed ExecuteReader to accept null values
if (reader.IsDBNull(3))
{
user.Zip = null;
}
else
{
user.Zip = reader.GetString(3);
}
if (reader.IsDBNull(4))
{
user.EmailAddress = null;
}
else
{
user.EmailAddress = reader.GetString(4);
}
if (reader.IsDBNull(8))
{
user.RegionId = null;
}
else
{
user.RegionId = reader.GetInt32(8);
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(user);
}
//Updated class name 4/14/16 Emily
public static User RetrieveUserByUsername(string username)
{
User user;
var conn = DBConnection.GetDBConnection();
var query = @"Admin.spSelectUserByUsername";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@username", username);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
int? regionID = 0;
string zip = "";
if (!reader.IsDBNull(4))
{
zip = reader.GetString(4);
}
if (!reader.IsDBNull(6))
{
// Rhett Allen 3/24/16 - regionID is now being assigned
regionID = reader.GetInt32(6);
}
else
{
// Rhett Allen 3/24/16 - added else. Made region default null instead of zero since region is nullable
regionID = null;
}
user = new User()
{
UserID = reader.GetInt32(0),
UserName = reader.GetString(1),
FirstName = reader.GetString(2),
LastName = reader.GetString(3),
Zip = zip,
EmailAddress = reader.GetString(5),
RegionId = regionID,
Active = reader.GetBoolean(7)
};
}
else
{
throw new ApplicationException("Data not found");
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(user);
}
/// <summary>
/// Author: Chris Schwebch
/// Updated: Nick King
/// Date: 04/6/16
/// Gets gardens the user belongs to returns garen list
/// </summary>
public static List <Group> RetrieveGardenInfo(int userID)
{
//var gardenInfo = new List<Garden>();
string groupName = null;
Group _Group = new Group();
List <Group> groupList = new List <Group>();
var conn = DBConnection.GetDBConnection();
string query = "Gardens.spSelectUserGardens";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserID", userID);
//cmd.Parameters.AddWithValue("@Active", 1);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
if (groupName == null || !groupName.Equals(reader.GetString(6)))
{
_Group = new Group();
_Group.GardenList = new List <Garden>();
groupName = reader.GetString(6);
_Group.Name = reader.GetString(6);
//_Group.GroupLeader = reader.String(7);
_Group.GroupID = reader.GetInt32(1);
groupList.Add(_Group);
}
if (groupName.Equals(reader.GetString(6)))
{
Garden _Garden = new Garden();
_Garden.GardenID = reader.GetInt32(0);
_Garden.GroupID = reader.GetInt32(1);
_Garden.GardenName = reader.GetString(2);
_Garden.UserID = reader.GetInt32(3);
_Garden.GardenDescription = reader.GetString(4);
_Garden.GardenRegion = reader.GetString(5);
_Group.GardenList.Add(_Garden);
}
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(groupList);
}
/// <summary>
/// Retrieve ExpertRequests to be processed by an administrator.
///
/// Created By: Trent Cullinan 03/15/2016
/// </summary>
/// <param name="accessToken">To confirm access as administrator.</param>
/// <returns>Collection of ExpertRequests that need processing.</returns>
public List <ExpertRequest> RetrieveExpertRequests(AccessToken accessToken)
{
List <ExpertRequest> requests = null;
//if (RetrieveAdminRoleStatus(accessToken))
//{
requests = new List <ExpertRequest>();
var conn = DBConnection.GetDBConnection();
var cmd = new SqlCommand("Admin.spSelectExpertRequests", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Active", true);
try
{
conn.Open();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
requests.Add(new ExpertRequest()
{
RequestID
= reader.GetInt32(0),
User
= new User()
{
UserID
= reader.GetInt32(1),
UserName
= reader.GetString(2),
FirstName
= reader.GetString(3),
LastName
= reader.GetString(4),
EmailAddress
= reader.GetString(5)
},
RequestDate
= reader.GetDateTime(6),
RequestTitle
= reader.GetString(7),
RequestContent
= reader.GetString(8)
});
}
}
catch (SqlException)
{
throw;
}
finally
{
conn.Close();
}
//}
//else
//{
// throw new Exception("User must be an admin to access this method.");
//}
return(requests);
}
/// <summary>
/// Rhett Allen
/// Created Date: 4/7/16
/// Gets a list of nutrients
/// </summary>
/// <returns>List of nutrients</returns>
public static List <Nutrient> RetrieveNutrient()
{
List <Nutrient> nutrients = new List <Nutrient>();
var conn = DBConnection.GetDBConnection();
var query = "Expert.spSelectNutrients";
var cmd = new SqlCommand(query, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Nutrient nutrient = new Nutrient()
{
NutrientID = reader.GetInt32(0),
Name = reader.GetString(1),
Description = reader.GetString(2),
CreatedBy = reader.GetInt32(3),
CreatedDate = reader.GetDateTime(4)
};
if (reader.IsDBNull(5))
{
nutrient.ModifiedBy = null;
}
else
{
nutrient.ModifiedBy = reader.GetInt32(5);
}
if (reader.IsDBNull(6))
{
nutrient.ModifiedDate = null;
}
else
{
nutrient.ModifiedDate = reader.GetDateTime(6);
}
nutrients.Add(nutrient);
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
return(nutrients);
}
