public ResponseUserList GetUserDetail(string aUsername)
{
ResponseUserList theResponse = new ResponseUserList();
if (aUsername.Equals(""))
{
theResponse.statusCode = 1;
theResponse.statusDescription = "No username was provided";
return theResponse;
}
openDataConnection();
SqlCommand cmdUserDetail = new SqlCommand("SELECT \"User\".*, UserAssociation.AssociatedID FROM \"User\" LEFT JOIN UserAssociation ON \"User\".Username = UserAssociation.Username WHERE \"User\".Username = '******'", theConnection);
theReader = cmdUserDetail.ExecuteReader();
if (theReader.HasRows)
{
theResponse.users = new List<StarbucksUser>();
while (theReader.Read())
{
StarbucksUser thisUser = new StarbucksUser();
thisUser.username = aUsername;
thisUser.firstName = theReader["FirstName"].ToString();
thisUser.lastName = theReader["LastName"].ToString();
thisUser.emailAddress = theReader["EmailAddress"].ToString();
thisUser.phoneNumber = theReader["PhoneNumber"].ToString();
thisUser.userType = (int)theReader["UserTypeID"];
thisUser.password = theReader["Password"].ToString();
thisUser.state = Boolean.Parse(theReader["State"].ToString());
thisUser.associatedID = theReader["AssociatedID"].ToString();
if (thisUser.userType == 2)
{
thisUser.associatedFieldName = "Provider";
}
else if (thisUser.userType == 3)
{
thisUser.associatedFieldName = "CDC";
}
theResponse.users.Add(thisUser);
}
theResponse.statusCode = 0;
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "The user " + aUsername + " could not be found";
}
theReader.Close();
if (theResponse.users != null && theResponse.users.Count > 0)
{
for (int i = 0, l = theResponse.users.Count; i < l; i++)
{
StarbucksUser thisUser = theResponse.users[i];
if (thisUser != null)
{
if (!thisUser.associatedID.ToUpper().Equals("NULL") && !thisUser.associatedID.Equals(""))
{
if (thisUser.userType == 2)
{
SqlCommand cmdGetAssociatedValue = new SqlCommand("SELECT * FROM Provider WHERE ProviderID = " + thisUser.associatedID, theConnection);
theReader = cmdGetAssociatedValue.ExecuteReader();
string providerName = "";
if (theReader.HasRows)
{
while (theReader.Read())
{
providerName = theReader["ProviderName"].ToString();
}
}
theReader.Close();
thisUser.associatedFieldValue = providerName;
}
else if (thisUser.userType == 3)
{
SqlCommand cmdGetAssociatedValue = new SqlCommand("SELECT * FROM CDC WHERE CDCID = " + thisUser.associatedID, theConnection);
theReader = cmdGetAssociatedValue.ExecuteReader();
string cdcName = "";
if (theReader.HasRows)
{
while (theReader.Read())
{
cdcName = theReader["CDCName"].ToString();
}
}
theReader.Close();
thisUser.associatedFieldValue = cdcName;
}
}
}
}
}
closeDataConnection();
return theResponse;
}
public ResponseUserList GetAllUsersByTypeAndProvider(string userType, string providerId)
{
ResponseUserList theResponse = new ResponseUserList();
openDataConnection();
SqlCommand cmdGetCDCIDByProviderID = new SqlCommand("select CDCID from CDC where ProviderID =" + Convert.ToInt32(providerId), theConnection);
//cmdGetCDCIDByProviderID.Parameters.AddWithValue("@providerID", Convert.ToInt32(providerId));
theReader = cmdGetCDCIDByProviderID.ExecuteReader();
List<int> cdcId = new List<int>();
if (theReader.HasRows)
{
while (theReader.Read())
{
cdcId.Add(int.Parse(theReader["CDCID"].ToString()));
}
}
theReader.Close();
List<StarbucksUser> listOfUsers = new List<StarbucksUser>();
foreach (int id in cdcId)
{
SqlCommand cmdGetAllUsers = new SqlCommand("SELECT \"User\".*, UserType.TypeName, UserAssociation.AssociatedID FROM \"User\" JOIN UserType ON \"User\".UserTypeID = UserType.TypeID LEFT JOIN UserAssociation ON \"User\".Username = UserAssociation.Username WHERE UserTypeID = " + userType + "and AssociatedID = " + id, theConnection);
theReader = cmdGetAllUsers.ExecuteReader();
if (theReader.HasRows)
{
while (theReader.Read())
{
StarbucksUser thisUser = new StarbucksUser();
thisUser.username = theReader["Username"].ToString();
thisUser.firstName = theReader["FirstName"].ToString();
thisUser.lastName = theReader["LastName"].ToString();
thisUser.phoneNumber = theReader["PhoneNumber"].ToString();
thisUser.emailAddress = theReader["EmailAddress"].ToString();
thisUser.userType = (int)theReader["UserTypeID"];
thisUser.userTypeName = theReader["TypeName"].ToString();
thisUser.state = Boolean.Parse(theReader["State"].ToString());
thisUser.associatedID = theReader["AssociatedID"].ToString();
if (thisUser.userType == 2)
{
thisUser.associatedFieldName = "Provider";
}
else if (thisUser.userType == 3)
{
thisUser.associatedFieldName = "CDC";
}
listOfUsers.Add(thisUser);
}
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 4;
theResponse.statusDescription = "There are no users in the database";
}
theReader.Close();
}
theResponse.users = listOfUsers;
if (theResponse.users != null && theResponse.users.Count > 0)
{
for (int i = 0, l = theResponse.users.Count; i < l; i++)
{
StarbucksUser thisUser = theResponse.users[i];
if (thisUser != null)
{
if (!thisUser.associatedID.ToUpper().Equals("NULL") && !thisUser.associatedID.Equals(""))
{
if (thisUser.userType == 2)
{
SqlCommand cmdGetAssociatedValue = new SqlCommand("SELECT * FROM Provider WHERE ProviderID = " + thisUser.associatedID, theConnection);
theReader = cmdGetAssociatedValue.ExecuteReader();
string providerName = "";
if (theReader.HasRows)
{
while (theReader.Read())
{
providerName = theReader["ProviderName"].ToString();
}
}
theReader.Close();
thisUser.associatedFieldValue = providerName;
}
else if (thisUser.userType == 3)
{
SqlCommand cmdGetAssociatedValue = new SqlCommand("SELECT * FROM CDC WHERE CDCID = " + thisUser.associatedID, theConnection);
theReader = cmdGetAssociatedValue.ExecuteReader();
string cdcName = "";
if (theReader.HasRows)
{
while (theReader.Read())
{
cdcName = theReader["CDCName"].ToString();
}
}
theReader.Close();
thisUser.associatedFieldValue = cdcName;
}
}
}
}
}
closeDataConnection();
return theResponse;
}
public Response UpdateUserPassword(StarbucksUser aUserModel)
{
Response theResponse = new Response();
if (aUserModel != null)
{
if (aUserModel.username == null)
{
theResponse.statusDescription = "Username was not supplied";
}
if (aUserModel.password == null)
{
theResponse.statusDescription = "Password was not supplied";
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdUpdateUser = new SqlCommand("UpdateUserPassword", theConnection);
cmdUpdateUser.Parameters.AddWithValue("@username", aUserModel.username);
cmdUpdateUser.Parameters.AddWithValue("@password", aUserModel.password);
cmdUpdateUser.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = cmdUpdateUser.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "The password of the user " + aUserModel.username + " could not be updated";
}
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Expected User Model not received";
}
closeDataConnection();
return theResponse;
}
public Response CreateUser(StarbucksUser aUserModel)
{
Response theResponse = new Response();
if (aUserModel != null)
{
if (aUserModel.username == null)
{
theResponse.statusDescription = "Username was not supplied";
}
if (aUserModel.password == null)
{
theResponse.statusDescription = "Password was not supplied";
}
if (aUserModel.firstName == null)
{
theResponse.statusDescription = "First Name was not supplied";
}
if (aUserModel.lastName == null)
{
theResponse.statusDescription = "Last Name was not supplied";
}
if (aUserModel.userType == 0)
{
theResponse.statusDescription = "User Type was not supplied";
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdCheckUserExists = new SqlCommand("UserExists", theConnection);
cmdCheckUserExists.Parameters.AddWithValue("@username", aUserModel.username.ToString());
cmdCheckUserExists.CommandType = System.Data.CommandType.StoredProcedure;
theReader = cmdCheckUserExists.ExecuteReader();
if (theReader.HasRows)
{
theReader.Close();
theResponse.statusCode = 3;
theResponse.statusDescription = "Username already exists";
}
else
{
theReader.Close();
SqlCommand cmdCreateUser = new SqlCommand("CreateUser", theConnection);
cmdCreateUser.Parameters.AddWithValue("@username", aUserModel.username.ToString());
cmdCreateUser.Parameters.AddWithValue("@password", aUserModel.password.ToString());
cmdCreateUser.Parameters.AddWithValue("@firstName", aUserModel.firstName.ToString());
cmdCreateUser.Parameters.AddWithValue("@lastName", aUserModel.lastName.ToString());
cmdCreateUser.Parameters.AddWithValue("@userTypeID", aUserModel.userType.ToString());
cmdCreateUser.Parameters.AddWithValue("@phoneNumber", aUserModel.phoneNumber != null ? aUserModel.phoneNumber.ToString() : (object)DBNull.Value);
cmdCreateUser.Parameters.AddWithValue("@emailAddress", aUserModel.emailAddress != null ? aUserModel.emailAddress.ToString() : (object)DBNull.Value);
cmdCreateUser.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = cmdCreateUser.ExecuteNonQuery();
if (numRowsAffected > 0)
{
if (aUserModel.associatedID != null && !aUserModel.associatedID.Equals(""))
{
SqlCommand cmdAssociate = new SqlCommand("AssociateUserToProvider", theConnection);
cmdAssociate.Parameters.AddWithValue("@username", aUserModel.username);
cmdAssociate.Parameters.AddWithValue("@providerID", Int32.Parse(aUserModel.associatedID));
cmdAssociate.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffectedInAssociation = cmdAssociate.ExecuteNonQuery();
if (numRowsAffectedInAssociation > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
SqlCommand cmdDeleteUser = new SqlCommand("DeleteUser", theConnection);
cmdDeleteUser.Parameters.AddWithValue("@username", aUserModel.username);
cmdDeleteUser.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffectedInDeletion = cmdDeleteUser.ExecuteNonQuery();
theResponse.statusCode = 3;
theResponse.statusDescription = "The user could not be created as it could not be associated to the supplied Provider ID or CDC ID";
}
}
else
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
}
}
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Expected User Model not received";
}
return theResponse;
}
public Response UpdateUser(StarbucksUser aUserModel)
{
Response theResponse = new Response();
if (aUserModel != null)
{
if (aUserModel.username == null)
{
theResponse.statusDescription = "Username was not supplied";
}
if (aUserModel.firstName == null)
{
theResponse.statusDescription = "First Name was not supplied";
}
if (aUserModel.lastName == null)
{
theResponse.statusDescription = "Last Name was not supplied";
}
if (theResponse.statusDescription.Equals(""))
{
openDataConnection();
SqlCommand cmdUpdateUser = new SqlCommand("UpdateUser", theConnection);
cmdUpdateUser.Parameters.AddWithValue("@username", aUserModel.username);
cmdUpdateUser.Parameters.AddWithValue("@firstName", aUserModel.firstName);
cmdUpdateUser.Parameters.AddWithValue("@lastName", aUserModel.lastName);
cmdUpdateUser.Parameters.AddWithValue("@phoneNumber", aUserModel.phoneNumber != null ? aUserModel.phoneNumber.ToString() : (object)DBNull.Value);
cmdUpdateUser.Parameters.AddWithValue("@emailAddress", aUserModel.emailAddress != null ? aUserModel.emailAddress.ToString() : (object)DBNull.Value);
cmdUpdateUser.CommandType = System.Data.CommandType.StoredProcedure;
int numRowsAffected = cmdUpdateUser.ExecuteNonQuery();
if (numRowsAffected > 0)
{
theResponse.statusCode = 0;
theResponse.statusDescription = "";
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "The user " + aUserModel.username + " could not be updated";
}
}
}
else
{
theResponse.statusCode = 6;
theResponse.statusDescription = "Expected User Model not received";
}
closeDataConnection();
return theResponse;
}