// admin log in test Abhi
/// <summary>
/// Given a partially complete FloraObj, query the database for a complete one.
/// </summary>
/// <param name="FLO"></param>
/// <returns></returns>
public string checkAdminLogin(FloraObj admLog)
{
string result = "check username & password";
string sqlQueryString = "";
SqlConnection conn = new SqlConnection(conn_string);
SqlCommand command = new SqlCommand();
command.Connection = conn;
if (!(string.IsNullOrEmpty(admLog.UserName) && string.IsNullOrEmpty(admLog.Password)))
{
sqlQueryString = "SELECT username,password from AdminTb";
command.Connection.Open();
//---Run the Query
command.CommandText = sqlQueryString;
SqlDataReader ReturnResult = command.ExecuteReader();
//---cycle through the return values and build a list of the floraobjs which match
if (ReturnResult.HasRows)
{
ReturnResult.Read();
string username = ReturnResult["username"].ToString().Trim();
string password = ReturnResult["password"].ToString().Trim();
if(username == admLog.UserName && password == admLog.Password)
result = "Authenticated";
return (result);
}
else
{
result = "check username & password";
return result;
}
}
return result;
}
bool CompareObjects(FloraObj FO, FloraObj FO2, bool bFullQuery)
{
bool bMatch = FO.PlantId.Trim().ToLower() == FO2.PlantId.Trim().ToLower();
bMatch = bMatch && (FO.Name.Trim().ToLower() == FO2.Name.Trim().ToLower());
bMatch = bMatch && (FO.Pattern.Trim().ToLower() == FO2.Pattern.Trim().ToLower());
bMatch = bMatch && FO.Shape.Trim().ToLower() == FO2.Shape.Trim().ToLower();
bMatch = bMatch && (FO.TextureFoliage.Trim().ToLower() == FO2.TextureFoliage.Trim().ToLower());
bMatch = bMatch && (FO.ColorFlower.Trim().ToLower() == FO2.ColorFlower.Trim().ToLower());
bMatch = bMatch && (FO.ColorFoliage.Trim().ToLower() == FO2.ColorFoliage.Trim().ToLower() && FO.ColorFruitSeed.Trim().ToLower() == FO2.ColorFruitSeed.Trim().ToLower());
if (bMatch && bFullQuery)
{
string[] types1 = FO.Type.Split(',');
string[] types2 = FO2.Type.Split(',');
int length1 = types1.GetLength(0);
int length2 = types2.GetLength(0);
bMatch = compareSplit(types1, types2, length1, length2);
}
if (bMatch && bFullQuery)
{
string[] state1 = FO.USState.Split(',');
string[] state2 = FO2.USState.Split(',');
int length1 = state1.GetLength(0);
int length2 = state2.GetLength(0);
bMatch = compareSplit(state1, state2, length1, length2);
}
if(bMatch)
{
// bMatch = (FO2.ImageURL == imageURL(FO.PlantId));
}
return bMatch;
}
private void TestDelete(string plantid)
{
FloraObj FLO = new FloraObj();
FLO.PlantId = plantid;
FLO.UserName = "******";
FLO.Password = "******";
//---Test delete by plant id
string result = Delete(FLO);
if (result == "Deleted Entry")
{
Console.WriteLine("Test Delete: SUCCESS");
success++;
}
else
{
Console.WriteLine("Test Delete: FAILED");
error++;
}
total++;
FLO.PlantId = "Not in Database";
//---Test delete by plant id that doesn't exist
result = Delete(FLO);
if (result == "Invalid plantID")
{
Console.WriteLine("Test Delete: SUCCESS");
success++;
}
else
{
Console.WriteLine("Test Delete: FAILED");
error++;
}
total++;
FLO.PlantId = "";
//---Test delete by plant id that doesn't exist
result = Delete(FLO);
if (result == "Authenticated")
{
Console.WriteLine("Test Delete: SUCCESS");
success++;
}
else
{
Console.WriteLine("Test Delete: FAILED");
error++;
}
total++;
}
private string Login(string username, string password)
{
FloraObj Flora = new FloraObj();
Flora.UserName = username;
Flora.Password = password;
Console.WriteLine("Testing Login");
try
{
HttpWebRequest GETRequest = (HttpWebRequest)WebRequest.Create(url);
GETRequest.Method = "PUT";
string json = JsonConvert.SerializeObject(Flora);
using (var streamWriter = new StreamWriter(GETRequest.GetRequestStream()))
{
streamWriter.Write(json);
streamWriter.Flush();
streamWriter.Close();
}
HttpWebResponse GETResponse = (HttpWebResponse)GETRequest.GetResponse();
Stream GETResponseStream = GETResponse.GetResponseStream();
StreamReader sr = new StreamReader(GETResponseStream);
string resultjson = sr.ReadToEnd();
FloraObj FLO = JsonConvert.DeserializeObject<FloraObj>(resultjson);
return FLO.Result;
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine("Test Login: FAILED");
error++;
return "";
}
total++;
}
/// <summary>
/// Insert the flora object
/// </summary>
/// <param name="Flora">Insert the given flora object</param>
FloraObj Insert(FloraObj Flora)
{
Console.WriteLine("Testing Insert");
try
{
HttpWebRequest GETRequest = (HttpWebRequest)WebRequest.Create(url);
GETRequest.Method = "PUT";
string json = JsonConvert.SerializeObject(Flora);
using (var streamWriter = new StreamWriter(GETRequest.GetRequestStream()))
{
streamWriter.Write(json);
streamWriter.Flush();
streamWriter.Close();
}
HttpWebResponse GETResponse = (HttpWebResponse)GETRequest.GetResponse();
Stream GETResponseStream = GETResponse.GetResponseStream();
StreamReader sr = new StreamReader(GETResponseStream);
string resultjson = sr.ReadToEnd();
FloraObj FLO = JsonConvert.DeserializeObject<FloraObj>(resultjson);
if (FLO.Result == "Inserted Entry")
{
Console.WriteLine("Test Insert: SUCCESS");
success++;
}
else
{
Console.WriteLine("Test Insert: FAILED");
error++;
}
total++;
return FLO;
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine("Test Insert: FAILED");
error++;
return null;
}
total++;
}
private void FillTestObject()
{
TestObj = new FloraObj();
TestObj.PlantId = "ABGR4";
TestObj.Name = "Abelia ??grandiflora";
TestObj.ColorFlower = "Purple";
TestObj.ColorFoliage = "Dark Green";
TestObj.ColorFruitSeed = "Brown";
TestObj.TextureFoliage = "Medium";
TestObj.Shape = "Semi-Erect";
TestObj.Pattern = "Dicot";
TestObj.USState = "FL";
TestObj.Type = "Shrub";
TestObj.ImageURL = "";
}
private FloraObj FillInsertObject(string plantid)
{
FloraObj FL = new FloraObj();
FL.PlantId = plantid;
FL.Name = "Abelia ??grandiflora";
FL.ColorFlower = "Purple";
FL.ColorFoliage = "Dark Green";
FL.ColorFruitSeed = "Brown";
FL.TextureFoliage = "Medium";
FL.Shape = "Semi-Erect";
FL.Pattern = "Dicot";
FL.USState = "FL";
FL.Type = "Shrub";
FL.ImageURL = "Test";
FL.UserName = "******";
FL.Password = "******";
return FL;
}
string Delete(FloraObj FLO)
{
Console.WriteLine("Testing Delete");
try
{
string json = JsonConvert.SerializeObject(FLO);
HttpWebRequest GETRequest = (HttpWebRequest)WebRequest.Create(url);
GETRequest.Method = "DELETE";
using (var streamWriter = new StreamWriter(GETRequest.GetRequestStream()))
{
streamWriter.Write(json);
streamWriter.Flush();
streamWriter.Close();
}
HttpWebResponse GETResponse = (HttpWebResponse)GETRequest.GetResponse();
Stream GETResponseStream = GETResponse.GetResponseStream();
StreamReader sr = new StreamReader(GETResponseStream);
string resultjson = sr.ReadToEnd();
FLO = JsonConvert.DeserializeObject<FloraObj>(resultjson);
return FLO.Result;
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine("Test Delete: FAILED");
error++;
return "Exception";
}
total++;
}
//end login check
public IList<FloraObj> Query(FloraObj FLO)
{
IList<FloraObj> floraObjList = new List<FloraObj>();
string sqlQueryString = "";
bool bAdvancedQuery = false;
int count;
if (string.IsNullOrEmpty(FLO.Type) && string.IsNullOrEmpty(FLO.USState))
{
//****--assumes that all of the other fields are filled in.
//---The basic query
sqlQueryString =
"SELECT DISTINCT Plant.plant_id, name, color_flower, color_foliage, color_fruit_seed, texture_foliage, shape, pattern, image FROM Plant";
//---There are no WHERE statements
count = 0;
}
else
{
bAdvancedQuery = true;
//****--NEED WHERE CLAUSES.
//---The advanced query
sqlQueryString =
"SELECT DISTINCT Plant.plant_id, name, color_flower, color_foliage, color_fruit_seed, texture_foliage, shape, pattern, image, us_state, type FROM Plant,Location,PlantType WHERE Plant.plant_id=Location.plant_id AND Plant.plant_id=PlantType.plant_id";
//---There are two conditionals here
count = 2;
}
//---set up the database connection
SqlConnection conn = new SqlConnection(conn_string);
SqlCommand command = new SqlCommand();
command.Connection = conn;
//---Because we have 2 assignments, we set the count of fields we are checking to 2
//---Build the query string - for each field, add a WHERE clause
sqlQueryString = BuildSQLWhere("Plant.plant_id", FLO.PlantId, "@plantid", count, sqlQueryString, command, out count);
sqlQueryString = BuildSQLWhere("name", FLO.Name, "@name", count, sqlQueryString, command, out count);
sqlQueryString = BuildSQLWhere("color_flower", FLO.ColorFlower, "@color_flower", count, sqlQueryString, command, out count);
sqlQueryString = BuildSQLWhere("color_foliage", FLO.ColorFoliage, "@color_foliage", count, sqlQueryString, command, out count);
sqlQueryString = BuildSQLWhere("color_fruit_seed", FLO.ColorFruitSeed, "@color_fruit_seed", count, sqlQueryString, command, out count);
sqlQueryString = BuildSQLWhere("texture_foliage", FLO.TextureFoliage, "@texture_foliage", count, sqlQueryString, command, out count);
sqlQueryString = BuildSQLWhere("shape", FLO.Shape, "@shape", count, sqlQueryString, command, out count);
sqlQueryString = BuildSQLWhere("pattern", FLO.Pattern, "@pattern", count, sqlQueryString, command, out count);
sqlQueryString = BuildSQLWhere("image", FLO.ImageURL, "@pattern", count, sqlQueryString, command, out count);
if (bAdvancedQuery)
{
//---Special case query for US state and type
sqlQueryString = BuildSQLWhere("us_state", FLO.USState, "@us_state", count, sqlQueryString, command, out count);
sqlQueryString = BuildSQLWhere("type", FLO.Type, "@type", count, sqlQueryString, command, out count);
}
//---Open the connection
command.Connection.Open();
//---Run the Query
command.CommandText = sqlQueryString;
SqlDataReader ReturnResult = command.ExecuteReader();
//---cycle through the return values and build a list of the floraobjs which match
while (ReturnResult.Read())
{
//---Get the plant id to start
string plant_id = ReturnResult.GetString(0);
//---Determine if an object already exists
FloraObj AddObj = findPlant_Id(floraObjList, plant_id);
if (AddObj == null)
{
AddObj = new FloraObj();
AddObj.PlantId = plant_id;
AddObj.Name = ReturnResult.GetString(1);
AddObj.ColorFlower = ReturnResult.GetString(2);
AddObj.ColorFoliage = ReturnResult.GetString(3);
AddObj.ColorFruitSeed = ReturnResult.GetString(4);
AddObj.TextureFoliage = ReturnResult.GetString(5);
AddObj.Shape = ReturnResult.GetString(6);
AddObj.Pattern = ReturnResult.GetString(7);
AddObj.ImageURL = ReturnResult.GetString(8);
if(string.IsNullOrEmpty(AddObj.ImageURL) || AddObj.ImageURL.ToUpper() == empty_image)
{
AddObj.ImageURL = default_image;
}
AddObj.GoogleImageURL = ThirdPartyLinks.GoogleImageLink(AddObj.Name);
AddObj.GoogleURL = ThirdPartyLinks.GoogleLink(AddObj.Name);
if (bAdvancedQuery)
{
AddObj.USState = ReturnResult.GetString(9);
AddObj.Type = ReturnResult.GetString(10);
}
//---Add this floraobj to our list of objects
floraObjList.Add(AddObj);
}
/* else
{
//---This object exists. All the values should be the same except for location or type
int length;
//---Check if this state value is here
bool bFound = UpdateComplexField(AddObj.USState, ReturnResult.GetString(9), out length);
if (!bFound && length > 0)
{
AddObj.USState += "," + ReturnResult.GetString(9);
}
else if (length == 0)
{
AddObj.USState = ReturnResult.GetString(9);
}
//---Check if the type value is here
bFound = UpdateComplexField(AddObj.Type, ReturnResult.GetString(10), out length);
if (!bFound && length > 0)
{
AddObj.Type += "," + ReturnResult.GetString(10);
}
else if (length == 0)
{
AddObj.Type = ReturnResult.GetString(10);
}
}*/
}
//---Close the database connection. Do we want a persistent connection? For now, we will just do connections on a case-by-case basis
command.Connection.Close();
return floraObjList;
}
public void Insert(FloraObj FLO)
{
string plantid;
List < FloraObj> returnList = new List<FloraObj>();
// check user name & password here ok or not
if ((FLO.Result = checkAdminLogin(FLO)) == "Authenticated")
{
returnList.Add(FLO);
//---If we are just a login object, then exit
if (FLO.IsLogin())
{
//---Add this object back on the list with the result and return
return;
}
if(string.IsNullOrEmpty(FLO.PlantId) && string.IsNullOrEmpty(FLO.Name))
{
FLO.Result = "Insert Failed: need plantid or name";
return;
}
//using parametirized query
string sqlInserString =
"INSERT INTO plant (plant_id,Name, Color_flower,color_foliage,color_fruit_seed,texture_foliage, shape, pattern,image) VALUES (@plant_id,@Name, @Color_flower,@color_foliage,@color_fruit_seed,@texture_foliage,@shape,@pattern,@image)";
SqlConnection conn = new SqlConnection(conn_string);
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.Connection.Open();
command.CommandText = sqlInserString;
if (FLO.Type == null)
FLO.Type = "";
if (FLO.USState == null)
FLO.USState = "";
if (FLO.ColorFoliage == null)
FLO.ColorFoliage = "";
if (FLO.ColorFlower == null)
FLO.ColorFlower = "";
if (FLO.PlantId == null)
FLO.PlantId = "";
if (FLO.ColorFruitSeed == null)
FLO.ColorFruitSeed = "";
if (FLO.TextureFoliage == null)
FLO.TextureFoliage = "";
if (FLO.Shape == null)
FLO.Shape= "";
if (FLO.Pattern == null)
FLO.Pattern = "";
if (FLO.ImageURL == null)
FLO.ImageURL = "";
if (FLO.Name == null)
FLO.Name = "";
if (FLO.PlantId == null || FLO.PlantId == "")
{
plantid = getplantid(FLO.Name);
}
else
{
plantid = getplantid(FLO.PlantId);
}
SqlParameter plant_id = new SqlParameter("@plant_id",plantid );
SqlParameter Name = new SqlParameter("@Name", FLO.Name);
SqlParameter Color_flower = new SqlParameter("@Color_flower", FLO.ColorFlower);
SqlParameter Color_foliage = new SqlParameter("@Color_foliage", FLO.ColorFoliage);
SqlParameter Color_fruit_seed = new SqlParameter("@color_fruit_seed", FLO.ColorFruitSeed);
SqlParameter texture = new SqlParameter("@texture_foliage", FLO.TextureFoliage);
SqlParameter shape = new SqlParameter("@shape", FLO.Shape);
SqlParameter pattern = new SqlParameter("@pattern", FLO.Pattern);
SqlParameter image = new SqlParameter("@image", FLO.ImageURL);
command.Parameters.AddRange(new SqlParameter[]{
plant_id,Name, Color_flower,Color_foliage,Color_fruit_seed,texture, shape, pattern,image});
command.ExecuteNonQuery();
command.Connection.Close();
insertlocation(plantid, FLO.USState);
insertplanttype(plantid, FLO.Type);
FLO.PlantId = plantid;
FLO.Result = "Inserted Entry";
}
// Check Admin USer & pass.
}
// delete for plant table.
/// <summary>
/// Delete the passed in floraobj
/// </summary>
/// <param name="FLO"></param>
public void Delete(FloraObj FLO)
{
List<FloraObj> returnList = new List<FloraObj>();
if ((FLO.Result = checkAdminLogin(FLO)) == "Authenticated")
{
returnList.Add(FLO);
//---If we are just a login object, then exit
if (FLO.IsLogin())
{
//---Add this object back on the list with the result and return
return;
}
//using parametirized query
string sqlInserString = "DELETE FROM plant WHERE plant_id=@plant_id ";
SqlConnection conn = new SqlConnection(conn_string);
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.Connection.Open();
command.CommandText = sqlInserString;
if(FLO.PlantId == "" || !checkplantid(FLO.PlantId))
{
FLO.Result = "Invalid plantID";
return;
}
SqlParameter plant_id = new SqlParameter("@plant_id", FLO.PlantId);
command.Parameters.AddRange(new SqlParameter[]{
plant_id});
command.ExecuteNonQuery();
command.Connection.Close();
Deletetype(FLO.PlantId);
Deletelocation(FLO.PlantId);
FLO.Result = "Deleted Entry";
}
else
{
//---Add this object back on the list with the result and return
returnList.Add(FLO);
}
}
