public string Load(string userId)
{
List <Foods.NewFood> xx = new List <Foods.NewFood>();
try {
Foods.FoodData foodData = new Foods.FoodData();
List <Foods.FoodGroup> foodGroups = new List <Foods.FoodGroup>();
DataBase db = new DataBase();
Global G = new Global();
string sql = "SELECT id, food, foodGroup FROM myfoods ORDER BY food ASC";
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, dataBase))) {
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
Foods.NewFood x = new Foods.NewFood();
x.id = reader.GetValue(0) == DBNull.Value ? "" : reader.GetString(0);
x.food = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1);
x.foodGroup.code = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.foodGroup.parent = "A";
xx.Add(x);
}
}
}
foodData.foods = xx;
foodData.foodGroups = null;
}
return(JsonConvert.SerializeObject(foodData, Formatting.None));
} catch (Exception e) {
L.SendErrorLog(e, null, userId, "MyFoods", "Load");
return(JsonConvert.SerializeObject(xx, Formatting.None));
}
}
public string Search(string userId, string query)
{
List <Foods.NewFood> xx = new List <Foods.NewFood>();
try {
Foods.FoodData foodData = new Foods.FoodData();
List <Foods.FoodGroup> foodGroups = new List <Foods.FoodGroup>();
DataBase db = new DataBase();
Global G = new Global();
query = G.SpecChrSearchQuery(query);
//db.CreateDataBase(userId, db.myFoods);
string sql = string.Format(@"SELECT id, food, foodGroup FROM myfoods {0} ORDER BY food ASC"
, !string.IsNullOrWhiteSpace(query) ? string.Format("WHERE LOWER(food) LIKE LOWER('%{0}%')", query) : "");
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, dataBase))) {
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
Foods.NewFood x = new Foods.NewFood();
x.id = reader.GetValue(0) == DBNull.Value ? "" : reader.GetString(0);
x.food = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1);
x.foodGroup.code = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.foodGroup.parent = "A";
xx.Add(x);
}
}
}
foodData.foods = xx;
foodData.foodGroups = null;
}
return(JsonConvert.SerializeObject(foodData, Formatting.None));
} catch (Exception e) {
L.SendErrorLog(e, null, userId, "MyFoods", "Search");
return(JsonConvert.SerializeObject(xx, Formatting.None));
}
}
public string Load(string userId)
{
try {
SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, dataBase));
connection.Open();
string sql = @"SELECT f.id, f.food, f.foodGroup, '', f.foodGroupVitaminLost, f.quantity, f.unit, f.mass, f.energy, f.carbohydrates, f.proteins, f.fats,
f.cerealsServ, f.vegetablesServ, f.fruitServ, f.meatServ, f.milkServ, f.fatsServ, f.otherFoodsServ,
f.starch, f.totalSugar, f.glucose, f.fructose, f.saccharose, f.maltose, f.lactose, f.fibers, f.saturatedFats,
f.monounsaturatedFats, f.polyunsaturatedFats, f.trifluoroaceticAcid, f.cholesterol, f.sodium, f.potassium,
f.calcium, f.magnesium,f.phosphorus, f.iron, f.copper, f.zinc, f.chlorine, f.manganese, f.selenium, f.iodine,
f.retinol, f.carotene, f.vitaminD, f.vitaminE, f.vitaminB1, f.vitaminB2,f.vitaminB3, f.vitaminB6, f.vitaminB12,
f.folate, f.pantothenicAcid, f.biotin, f.vitaminC, f.vitaminK
FROM myfoods AS f
ORDER BY food ASC";
SQLiteCommand command = new SQLiteCommand(sql, connection);
List <Foods.NewFood> xx = new List <Foods.NewFood>();
Foods.FoodData foodData = new Foods.FoodData();
List <Foods.FoodGroup> foodGroups = new List <Foods.FoodGroup>();
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Foods.NewFood x = new Foods.NewFood();
x.id = reader.GetValue(0) == DBNull.Value ? "" : reader.GetString(0);
x.food = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1);
x.foodGroup.code = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.foodGroup.title = reader.GetValue(3) == DBNull.Value ? "" : reader.GetString(3);
x.foodGroup.parent = "A";
xx.Add(x);
}
foodData.foods = xx;
foodData.foodGroups = null;
connection.Close();
string json = JsonConvert.SerializeObject(foodData, Formatting.None);
return(json);
} catch (Exception e) { return("Error: " + e); }
}
