public static void findNdbno(String foodSearch)
{
try
{
names.Clear();
ndbnoList.Clear();
String urlPartOne = "https://api.nal.usda.gov/ndb/search/?format=json&q=";
String urlPartTwo = "&sort=r&max=50&offset=0&api_key=m37cNkiJMin6FLxPuq6wDMqtFekEJYB6HJpbLrYb";
String url = urlPartOne + foodSearch + urlPartTwo;
var json = new WebClient().DownloadString(url);
var result = JsonConvert.DeserializeObject <Search>(json);
if (!indexresult.dataSearchResults.Columns.Contains("NDBno") && !indexresult.dataSearchResults.Columns.Contains("Name") &&
!indexresult.dataSearchResults.Columns.Contains("ND Score"))
{
indexresult.dataSearchResults.Columns.Add("NDBno", typeof(string));
indexresult.dataSearchResults.Columns.Add("Name", typeof(string));
indexresult.dataSearchResults.Columns.Add("ND Score", typeof(double));
}
else
{
indexresult.dataSearchResults.Clear();
}
//check usda api first, if result null, use second api
for (int i = 0; i < result.list.item.Count; i++)
{
newFood = new FoodItem();
newFood.ndbNo = Convert.ToString(result.list.item[i].ndbno);
newFood.name = Convert.ToString(result.list.item[i].name);
String urlPartOne2 = "https://api.nal.usda.gov/ndb/V2/reports?ndbno=";
String urlPartTwo2 = "&type=b&format=json&api_key=m37cNkiJMin6FLxPuq6wDMqtFekEJYB6HJpbLrYb";
String url2 = urlPartOne2 + newFood.ndbNo + urlPartTwo2;
var json2 = new WebClient().DownloadString(url2);
var result2 = JsonConvert.DeserializeObject <RootObject>(json2);
for (int j = 0; j < result2.foods.Count; j++)
{
// SOME OF THE NUTRIENT ID'S ARE CHANGED W/ VERSION TWO, I FIXED THEM
foreach (Nutrient item in result2.foods[j].food.nutrients)
{
if (Int32.Parse(item.nutrient_id) == 203)
{
newFood.protein = Double.Parse(item.value);
newFood.protein = Math.Round(newFood.protein, 2);
}
else if (Int32.Parse(item.nutrient_id) == 208)
{
newFood.kCal = Double.Parse(item.value);
newFood.kCal = Math.Round(newFood.kCal);
}
else if (Int32.Parse(item.nutrient_id) == 291)
{
newFood.fiber = Double.Parse(item.value);
newFood.fiber = Math.Round(newFood.fiber, 2);
}
else if (Int32.Parse(item.nutrient_id) == 318)
{
newFood.vitaminA = Double.Parse(item.value);
newFood.vitaminA = Math.Round(newFood.vitaminA, 2);
}
else if (Int32.Parse(item.nutrient_id) == 401)
{
newFood.vitaminC = Double.Parse(item.value);
newFood.vitaminC = Math.Round(newFood.vitaminC, 2);
}
else if (Int32.Parse(item.nutrient_id) == 301)
{
newFood.calcium = Double.Parse(item.value);
// newFood.calcium = Math.Round(newFood.calcium, 2);
}
else if (Int32.Parse(item.nutrient_id) == 303)
{
newFood.iron = Double.Parse(item.value);
// newFood.iron = Math.Round(newFood.iron, 2);
}
else if (Int32.Parse(item.nutrient_id) == 606)
{
newFood.satFat = Double.Parse(item.value);
newFood.satFat = Math.Round(newFood.satFat, 2);
}
else if (Int32.Parse(item.nutrient_id) == 269)
{
newFood.totalSugar = Double.Parse(item.value);
newFood.totalSugar = Math.Round(newFood.totalSugar, 2);
}
else if (Int32.Parse(item.nutrient_id) == 307)
{
newFood.sodium = Double.Parse(item.value);
newFood.sodium = Math.Round(newFood.sodium, 2);
}
else if (Int32.Parse(item.nutrient_id) == 204)
{
newFood.totalFat = Double.Parse(item.value);
newFood.totalFat = Math.Round(newFood.totalFat, 2);
}
else if (Int32.Parse(item.nutrient_id) == 605)
{
newFood.transFat = Double.Parse(item.value);
newFood.transFat = Math.Round(newFood.transFat, 2);
}
else if (Int32.Parse(item.nutrient_id) == 601)
{
newFood.cholesterol = Double.Parse(item.value);
newFood.cholesterol = Math.Round(newFood.cholesterol, 2);
}
else if (Int32.Parse(item.nutrient_id) == 205)
{
newFood.carbohydrates = Double.Parse(item.value);
newFood.carbohydrates = Math.Round(newFood.carbohydrates, 2);
}
if (result2.foods[0].food.ing != null)
{
newFood.ingredients = result2.foods[0].food.ing.desc;
}
double protein = newFood.protein / 50;
double fiber = newFood.fiber / 25;
double vitaminA = newFood.vitaminA / 5000;
double vitaminC = newFood.vitaminC / 60;
double calcium = newFood.calcium / 1000;
double iron = newFood.iron / 18;
//if any of the good value ratios are > 1, set them equal to 1 to follow algorithm rule
if (protein > 1)
{
protein = 1;
}
if (fiber > 1)
{
fiber = 1;
}
if (vitaminA > 1)
{
vitaminA = 1;
}
if (vitaminC > 1)
{
vitaminC = 1;
}
if (calcium > 1)
{
calcium = 1;
}
if (iron > 1)
{
iron = 1;
}
newFood.nR6 = (protein) + (fiber) + (vitaminA) + (vitaminC) + (calcium) + (iron);
newFood.liMT = (newFood.satFat / 20) + (newFood.totalSugar / 125) + (newFood.sodium / 2400);
double good = ((newFood.nR6 * 100) / newFood.kCal) * 100;
double bad = ((newFood.liMT * 100) / newFood.kCal) * 100;
newFood.NRF6 = good - bad;
newFood.NRF6 = Math.Round(newFood.NRF6, 5);
}
}
DataRow row = indexresult.dataSearchResults.NewRow();
row[0] = newFood.ndbNo;
row[1] = newFood.name;
row[2] = newFood.NRF6;
indexresult.dataSearchResults.Rows.Add(row);
}
indexresult.savedNdb_no = newFood.ndbNo;
indexresult.savedItemName = newFood.name;
indexresult.savedFoodGroup = newFood.foodGroup;
indexresult.savedNrf6 = newFood.NRF6;
}catch (Exception ex)
{
// response write opps! item cant be found
}
//else
//{
// string api2 = System.Web.HttpUtility.UrlPathEncode(foodSearch);
// // String urlAPI2pt1 = "https://api.edamam.com/api/food-database/parser?ingr=";
// // String urlAPI2pt2 = "&app_id ={cd27db7d} &app_key ={9d149ec2802f86f42a15dcbd16891ff9}&page = 0";
// String apiRequest = urlPartOne + api2 + urlPartTwo;
// var json2 = new WebClient().DownloadString(apiRequest);
// var result2 = JsonConvert.DeserializeObject<Search>(json2);
//}
}
/***
* Get the ndbno from a hidden field in front-end.
* Get data using FoodItem.findNdbno method and ndbno.
* Style the NR Score accordingly.
* Update data to modal section in front-end.
*/
protected void ExpandItem(object sender, EventArgs e)
{
// get data from front end
string ceresid = hidden_ceresid.Value;
string ceres_name = hidden_ceres_name.Value;
string ndbno = hidden_ndbno.Value;
string nrf6 = hidden_nrf6.Value;
string view_mode = hidden_view_mode.Value;
// To manage records with matched USDA items
if (ndbno != "")
{
FoodItem.findNdbno(ndbno);
double score = FoodItem.newFood.NRF6;
String colorScaleStyle = "background-color: ";
if (score <= 4.65)
{
colorScaleStyle += GradientColors.getColor1() + "; color: white;";
}
else if ((score >= 4.66) && (score <= 27.99))
{
colorScaleStyle += GradientColors.getColor2() + "; color: black;";
}
else if (score >= 28)
{
colorScaleStyle += GradientColors.getColor3() + "; color: white;";
}
else
{
// do nothing
}
switch (view_mode)
{
case "old":
nd_old_score_panel.Attributes["style"] = colorScaleStyle;
lblOldNdbno.Text = ndbno;
lblOldCeresId.Text = ceresid;
lblOldCeresName.Text = ceres_name;
lblOldFoodName.Text = FoodItem.newFood.name;
lblOldIndexResult.Text = Convert.ToString(Math.Round(score, 2));
txtOldKCal.Text = FoodItem.newFood.kCal.ToString();
txtOldSaturatedFat.Text = Math.Round(FoodItem.newFood.satFat, 2).ToString();
txtOldSodium.Text = Math.Round(FoodItem.newFood.sodium, 2).ToString();
txtOldFiber.Text = Math.Round(FoodItem.newFood.fiber, 2).ToString();
txtOldTotalSugar.Text = Math.Round(FoodItem.newFood.totalSugar, 2).ToString();
txtOldProtein.Text = Math.Round(FoodItem.newFood.protein, 2).ToString();
txtOldVitaminA.Text = Math.Round(FoodItem.newFood.vitaminA).ToString();
txtOldVitaminC.Text = Math.Round(FoodItem.newFood.vitaminC).ToString();
txtOldCalcium.Text = Math.Round(FoodItem.newFood.calcium).ToString();
txtOldIron.Text = Math.Round(FoodItem.newFood.iron).ToString();
lblNewCeresNumber.Text = ceresid;
break;
case "new":
nd_new_score_panel.Attributes["style"] = colorScaleStyle;
lblNewNdbno.Text = ndbno;
lblNewCeresId.Text = ceresid;
lblNewCeresName.Text = ceres_name;
lblNewFoodName.Text = FoodItem.newFood.name;
lblNewIndexResult.Text = Convert.ToString(Math.Round(score, 2));
txtNewCalories.Text = FoodItem.newFood.kCal.ToString();
txtNewSaturatedFat.Text = Math.Round(FoodItem.newFood.satFat, 2).ToString();
txtSodiumNew.Text = Math.Round(FoodItem.newFood.sodium, 2).ToString();
txtFiberNew.Text = Math.Round(FoodItem.newFood.fiber, 2).ToString();
txtAddedSugarNew.Text = Math.Round(FoodItem.newFood.totalSugar, 2).ToString();
txtProteinNew.Text = Math.Round(FoodItem.newFood.protein, 2).ToString();
txtVitaminDNew.Text = Math.Round((FoodItem.newFood.vitaminA / 5000) * 100).ToString();
txtCalciumNew.Text = Math.Round((FoodItem.newFood.calcium / 1000) * 100).ToString();
txtIronNew.Text = Math.Round((FoodItem.newFood.iron / 18) * 100).ToString();
txtPotassiumNew.Text = Math.Round((FoodItem.newFood.vitaminC / 60) * 100).ToString();
break;
default:
break;
}
// re-render bootstrap-select component
ddlFBCategories.CssClass = "selectpicker";
ddlFBCategories.Attributes["title"] = "Select a category";
ddlFBCategories.Attributes["data-width"] = "100%";
ddlFBCategories.Attributes["data-live-search"] = "true";
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "rerender", "$('.selectpicker').selectpicker('render');", true);
}
// To manage manually inputted items
else
{
double score = Double.Parse(hidden_nrf6.Value);
String colorScaleStyle = "";
if (score <= 4.65)
{
colorScaleStyle = GradientColors.getColor1();
}
else if ((score >= 4.66) && (score <= 27.99))
{
colorScaleStyle = GradientColors.getColor2();
}
else if (score >= 28)
{
colorScaleStyle = GradientColors.getColor3();
}
else
{
// do nothing
}
colorScaleStyle = "background-color: " + colorScaleStyle;
switch (view_mode)
{
case "old":
nd_old_score_panel.Attributes["style"] = colorScaleStyle;
lblOldIndexResult.Text = Convert.ToString(Math.Round(score, 2));
lblNewCeresNumber.Text = ceresid;
break;
case "new":
nd_new_score_panel.Attributes["style"] = colorScaleStyle;
lblNewIndexResult.Text = Convert.ToString(Math.Round(score, 2));
break;
default:
break;
}
// re-render bootstrap-select component
ddlFBCategories.CssClass = "selectpicker";
ddlFBCategories.Attributes["title"] = "Select a category";
ddlFBCategories.Attributes["data-width"] = "100%";
ddlFBCategories.Attributes["data-live-search"] = "true";
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "rerender", "$('.selectpicker').selectpicker('render');", true);
}
}
protected void btnSearch(object sender, EventArgs e)
{
String foodSearch = "";
if (txtSearch.Text != "")
{
switch (ddlCategory.SelectedIndex)
{
case 0: foodSearch = txtSearch.Text; break;
case 1: foodSearch = txtSearch.Text + "&fg=0100"; break;
case 2: foodSearch = txtSearch.Text + "&fg=0200"; break;
case 3: foodSearch = txtSearch.Text + "&fg=0300"; break;
case 4: foodSearch = txtSearch.Text + "&fg=0400"; break;
case 5: foodSearch = txtSearch.Text + "&fg=0500"; break;
case 6: foodSearch = txtSearch.Text + "&fg=0600"; break;
case 7: foodSearch = txtSearch.Text + "&fg=0700"; break;
case 8: foodSearch = txtSearch.Text + "&fg=0800"; break;
case 9: foodSearch = txtSearch.Text + "&fg=0900"; break;
case 10: foodSearch = txtSearch.Text + "&fg=1000"; break;
case 11: foodSearch = txtSearch.Text + "&fg=1100"; break;
case 12: foodSearch = txtSearch.Text + "&fg=1200"; break;
case 13: foodSearch = txtSearch.Text + "&fg=1300"; break;
case 14: foodSearch = txtSearch.Text + "&fg=1400"; break;
case 15: foodSearch = txtSearch.Text + "&fg=1500"; break;
case 16: foodSearch = txtSearch.Text + "&fg=1600"; break;
case 17: foodSearch = txtSearch.Text + "&fg=1700"; break;
case 18: foodSearch = txtSearch.Text + "&fg=1800"; break;
case 19: foodSearch = txtSearch.Text + "&fg=1900"; break;
case 20: foodSearch = txtSearch.Text + "&fg=2000"; break;
case 21: foodSearch = txtSearch.Text + "&fg=2100"; break;
case 22: foodSearch = txtSearch.Text + "&fg=2200"; break;
case 23: foodSearch = txtSearch.Text + "&fg=2500"; break;
case 24: foodSearch = txtSearch.Text + "&fg=3500"; break;
case 25: foodSearch = txtSearch.Text + "&fg=3600"; break;
default: break;
}
}
else
{
// show error
}
FoodItem.findNdbno(foodSearch);
Server.Transfer("/WebForms/indexresult.aspx");
}
/***
* Use ndbnoArray to fetch data from USDA API.
* Set values of newFoodArray with those data.
*/
protected void GetDataFromUsdaApi()
{
String urlRequest = "";
for (int i = 0; i < ndbnoArray.Length; i++)
{
if (ndbnoArray[i] != null)
{
if (i == 0)
{
urlRequest += ndbnoArray[i].ToString();
}
else
{
urlRequest += "&ndbno=";
urlRequest += ndbnoArray[i].ToString();
}
}
}
String urlPartOne = "https://api.nal.usda.gov/ndb/V2/reports?ndbno=";
String urlPartTwo = "&type=b&format=json&api_key=m37cNkiJMin6FLxPuq6wDMqtFekEJYB6HJpbLrYb";
String url = urlPartOne + urlRequest + urlPartTwo;
var json = new WebClient().DownloadString(url);
var result = JsonConvert.DeserializeObject <RootObject>(json);
for (int i = 0; i < result.foods.Count; i++)
{
FoodItem newFoodItem = new FoodItem();
foreach (Nutrient item in result.foods[i].food.nutrients)
{
newFoodItem.name = result.foods[i].food.desc.name;
newFoodItem.ndbNo = result.foods[i].food.desc.ndbno;
// Good nutrients
if (Int32.Parse(item.nutrient_id) == 203)
{
newFoodItem.protein = Double.Parse(item.value);
}
if (Int32.Parse(item.nutrient_id) == 291)
{
newFoodItem.fiber = Double.Parse(item.value);
}
if (Int32.Parse(item.nutrient_id) == 318)
{
newFoodItem.vitaminA = Double.Parse(item.value);
}
if (Int32.Parse(item.nutrient_id) == 401)
{
newFoodItem.vitaminC = Double.Parse(item.value);
}
if (Int32.Parse(item.nutrient_id) == 301)
{
newFoodItem.calcium = Double.Parse(item.value);
}
if (Int32.Parse(item.nutrient_id) == 303)
{
newFoodItem.iron = Double.Parse(item.value);
}
if (Int32.Parse(item.nutrient_id) == 208)
{
newFoodItem.kCal = Double.Parse(item.value);
}
// Bad nutrients
if (Int32.Parse(item.nutrient_id) == 606)
{
newFoodItem.satFat = Double.Parse(item.value);
}
if (Int32.Parse(item.nutrient_id) == 269)
{
newFoodItem.totalSugar = Double.Parse(item.value);
}
if (Int32.Parse(item.nutrient_id) == 307)
{
newFoodItem.sodium = Double.Parse(item.value);
}
}
newFoodItem.calculateNRF6();
newFoodArray[i] = newFoodItem;
}
}
/***
* Get the ndbno from a hidden field in front-end.
* Get data using FoodItem.findNdbno method and ndbno.
* Style the NR Score accordingly.
* Update data to modal section in front-end.
*/
protected void ExpandItem(object sender, EventArgs e)
{
string ndbno = lblNdbno.Value;
FoodItem.findNdbno(ndbno);
String ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
//check if item already exists in database, if it does hide the save button and show the update button
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand("Select * FROM wholesome_item WHERE ndb_no = @ndbno", connection);
command.Parameters.Add("@ndbno", SqlDbType.NVarChar, 8).Value = ndbno;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
btnUpdate.Visible = true;
btnSaveItem.Visible = false;
txtCeresStatus.Text = "Matched";
}
else
{
btnSaveItem.Visible = true;
txtCeresStatus.Text = "Unmatched";
btnUpdate.Visible = true;
}
connection.Close();
}
//saved into recent_index
//check if they login
if (!checkndbno(ndbno))
{
if (HttpContext.Current.User.IsInRole("Admin") || HttpContext.Current.User.IsInRole("Purchasing_Staff") ||
HttpContext.Current.User.IsInRole("Warehouse_Staff"))
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand("INSERT INTO RECENT_INDEX(NDB_NO,LOGINID,LastUpdated,LastUpdatedBy) VALUES (@NDB_NO, @ID,@LastUpdated, @LastUpdatedby);", connection);
command.Parameters.Add("@NDB_NO", SqlDbType.NVarChar, 8).Value = ndbno;
command.Parameters.Add("@ID", SqlDbType.Int).Value = getloginid();
command.Parameters.Add("@LastUpdatedBy", SqlDbType.NVarChar, 20).Value = HttpContext.Current.User.Identity.GetUserName();
command.Parameters.Add("@LastUpdated", SqlDbType.DateTime, 128).Value = DateTime.Now;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
else
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand command = new SqlCommand("INSERT INTO RECENT_INDEX(NDB_NO,LOGINID,LastUpdated,LastUpdatedBy) VALUES (@NDB_NO, @ID,@LastUpdated, @LastUpdatedby);", connection);
command.Parameters.Add("@NDB_NO", SqlDbType.NVarChar, 8).Value = ndbno;
command.Parameters.Add("@ID", SqlDbType.Int).Value = DBNull.Value;
command.Parameters.Add("@LastUpdatedBy", SqlDbType.NVarChar, 20).Value = HttpContext.Current.User.Identity.GetUserName();;
command.Parameters.Add("@LastUpdated", SqlDbType.DateTime, 128).Value = DateTime.Now;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
}
else
{
//do nothing
}
double score = FoodItem.newFood.NRF6;
String colorScaleStyle = "background-color: ";
if (score < 4.66)
{
colorScaleStyle += GradientColors.getColor1() + "; color: white;";
}
else if ((score >= 4.66) && (score <= 28))
{
colorScaleStyle += GradientColors.getColor2() + "; color: black;";
}
else if (score > 28)
{
colorScaleStyle += GradientColors.getColor3() + "; color: white;";
}
else
{
// do nothing
}
nd_score_panel.Attributes["style"] = colorScaleStyle;
lblFoodName.Text = FoodItem.newFood.name;
lblFoodName.Attributes["style"] = "font-weight: bold;";
lblIndexResult.Text = Convert.ToString(Math.Round(score, 2));
lblNdbno.Value = FoodItem.newFood.ndbNo;
lblName.Value = FoodItem.newFood.name;
txtcalories.Text = FoodItem.newFood.kCal.ToString();
txtsatfat.Text = Math.Round(FoodItem.newFood.satFat, 2).ToString();
txtsodium.Text = Math.Round(FoodItem.newFood.sodium, 2).ToString();
txtfiber.Text = Math.Round(FoodItem.newFood.fiber, 2).ToString();
txtsugar.Text = Math.Round(FoodItem.newFood.totalSugar, 2).ToString();
txtprotein.Text = Math.Round(FoodItem.newFood.protein, 2).ToString();
txtva.Text = Math.Round(FoodItem.newFood.vitaminA, 2).ToString();
txtvc.Text = Math.Round(FoodItem.newFood.vitaminC, 2).ToString();
txtcalcium.Text = Math.Round(FoodItem.newFood.calcium, 2).ToString();
txtiron.Text = Math.Round(FoodItem.newFood.iron, 2).ToString();
// re-render bootstrap-select component
ddlFBCategories.CssClass = "selectpicker";
ddlFBCategories.Attributes["title"] = "Select a category";
ddlFBCategories.Attributes["data-width"] = "100%";
ddlFBCategories.Attributes["data-live-search"] = "true";
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "rerender", "$('.selectpicker').selectpicker('render');", true);
}