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