public string DeleteRecipeImg(Recipes.NewRecipe x, string userId)
{
try {
string img = null;
if (x.recipeImg.Contains("?"))
{
img = x.recipeImg.Remove(x.recipeImg.IndexOf("?"));
}
else
{
img = x.recipeImg;
}
string path = string.Format("~/upload/users/{0}/recipes/{1}/recipeimg/{2}", userId, x.id, img);
if (File.Exists(Server.MapPath(path)))
{
File.Delete(Server.MapPath(path));
return(null);
}
else
{
return("no file");
}
} catch (Exception e) {
return(e.Message);
}
}
public string UpdateStatus(Recipes.NewRecipe x)
{
x.sharingData.resp = new Global.Response();
try {
if (!x.sharingData.adminSave)
{
x.sharingData.resp.isSuccess = false;
x.sharingData.resp.msg = "error";
return(JsonConvert.SerializeObject(x.sharingData.resp, Formatting.None));
}
x.energy = x.data.selectedFoods.Sum(a => a.energy);
string sql = string.Format(@"UPDATE recipes SET energy = '{0}', status = {1}, statusNote = '{2}' WHERE id = '{3}'", x.energy, x.sharingData.status.code, x.sharingData.status.note, x.id);
using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0}", Server.MapPath(dataSource)))) {
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
command.ExecuteNonQuery();
}
}
x.sharingData.resp.isSuccess = true;
x.sharingData.resp.msg = "saved";
return(JsonConvert.SerializeObject(x.sharingData.resp, Formatting.None));
} catch (Exception e) {
x.sharingData.resp.isSuccess = false;
x.sharingData.resp.msg = e.Message;
L.SendErrorLog(e, x.id, x.sharingData.recipeOwner.userId, "SharingRecipes", "UpdateStatus");
return(JsonConvert.SerializeObject(x.sharingData.resp, Formatting.None));
}
}
public string Load(string userId, int?status, bool showUserRecipes, int limit, int offset, string lang)
{
List <Recipes.NewRecipe> xx = new List <Recipes.NewRecipe>();
try {
//string path = Server.MapPath(string.Format("~/App_Data/{0}", dataBase));
//db.CreateGlobalDataBase(path, db.sharingrecipes);
string sql_condition = null;
if (!string.IsNullOrEmpty(userId) || status != null || !string.IsNullOrEmpty(lang))
{
sql_condition = "WHERE";
if (!string.IsNullOrEmpty(userId))
{
sql_condition = string.Format("{0} userId {1} '{2}'", sql_condition, showUserRecipes ? "=" : "<>", userId);
}
if (status != null && status >= 0)
{
sql_condition = string.Format("{0} {1} status = '{2}'", sql_condition, !string.IsNullOrEmpty(userId) ? "AND" : "", status);
}
if (!string.IsNullOrEmpty(lang))
{
sql_condition = string.Format("{0} {1} lang = '{2}'", sql_condition, !string.IsNullOrEmpty(userId) || status != null ? "AND" : "", lang);
}
}
string sql = string.Format(@"SELECT {0} FROM recipes {1} ORDER BY status ASC, rowid DESC LIMIT {2} OFFSET {3}", mainSql, sql_condition, limit, offset);
using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0}", Server.MapPath(dataSource)))) {
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
Recipes.NewRecipe x = GetData(reader);
x.sharingData.recipeId = x.id;
// x.mealGroup.title = R.GetMealGroupTitle(x.mealGroup.code);
x.recipeImg = Recipes.GetRecipeImg(x.sharingData.recipeOwner.userGroupId, x.id);
x.recipeImgPath = Recipes.GetRecipeImgPath(userId, x.id, x.recipeImg);
xx.Add(x);
}
}
}
}
return(JsonConvert.SerializeObject(xx, Formatting.None));
} catch (Exception e) {
L.SendErrorLog(e, null, userId, "SharingRecipes", "Load");
return(JsonConvert.SerializeObject(xx, Formatting.None));
}
}
private Recipes.NewRecipe GetData(SQLiteDataReader reader)
{
Recipes.NewRecipe x = new Recipes.NewRecipe();
try {
x.sharingData = new SharingData();
x.id = reader.GetValue(0) == DBNull.Value ? null : reader.GetString(0);
x.sharingData.recipeOwner = new Users.NewUser();
x.sharingData.recipeOwner.userId = reader.GetValue(1) == DBNull.Value ? null : reader.GetString(1);
x.sharingData.recipeOwner.userGroupId = reader.GetValue(2) == DBNull.Value ? null : reader.GetString(2);
x.sharingData.recordDate = reader.GetValue(3) == DBNull.Value ? null : reader.GetString(3);
x.title = reader.GetValue(4) == DBNull.Value ? null : reader.GetString(4);
x.description = reader.GetValue(5) == DBNull.Value ? null : reader.GetString(5);
x.energy = reader.GetValue(6) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetString(6));
x.mealGroup = new Recipes.MealGroup();
x.mealGroup.code = reader.GetValue(7) == DBNull.Value ? null : reader.GetString(7);
// x.mealGroup.title = R.GetMealGroupTitle(x.mealGroup.code);
x.sharingData.status = new Status();
x.sharingData.status.code = reader.GetValue(8) == DBNull.Value ? 0 : reader.GetInt32(8);
x.sharingData.status.title = GetStatusTitle(x.sharingData.status.code);
x.sharingData.status.ico = GetStatusIcon(x.sharingData.status.code);
x.sharingData.status.style = GetStatusStyle(x.sharingData.status.code);
x.sharingData.status.note = reader.GetValue(9) == DBNull.Value ? null : reader.GetString(9);
x.sharingData.rank = reader.GetValue(10) == DBNull.Value ? 0 : reader.GetInt32(10);
x.sharingData.like = reader.GetValue(11) == DBNull.Value ? 0 : reader.GetInt32(11);
x.sharingData.views = reader.GetValue(12) == DBNull.Value ? 0 : reader.GetInt32(12);
x.sharingData.lang = reader.GetValue(13) == DBNull.Value ? null : reader.GetString(13);
x.mealGroups = R.InitMealGroups(x.mealGroup.code);
Users U = new Users();
x.sharingData.recipeOwner.firstName = U.GetUserFullName(x.sharingData.recipeOwner.userId, false);
x.isShared = false;
x.consumers = reader.GetValue(14) == DBNull.Value ? 1 : reader.GetInt32(14);
x.data = R.GetRecipeData(x.sharingData.recipeOwner.userGroupId, x.id);
if (x.data != null)
{
if (x.data.selectedFoods.Count > 0)
{
x.carbohydrates = x.data.selectedFoods.Sum(a => a.carbohydrates);
x.proteins = x.data.selectedFoods.Sum(a => a.proteins);
x.fats = x.data.selectedFoods.Sum(a => a.fats);
}
}
return(x);
} catch (Exception e) {
L.SendErrorLog(e, x.id, x.sharingData.recipeOwner.userId, "SharingRecipes", "GetData");
return(x);
}
}
public Recipes.NewRecipe GetRecipeById(string id)
{
Recipes.NewRecipe x = new Recipes.NewRecipe();
x.sharingData = InitSharingData();
using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0}", Server.MapPath(dataSource)))) {
connection.Open();
string sql = string.Format("SELECT {0} FROM recipes WHERE id = '{1}'", mainSql, id);
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
Clients.Client client = new Clients.Client();
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
x = GetData(reader);
}
}
}
}
return(x);
}
public string Get(string userId, string id, bool isForEdit) // ***** isForEdit (only for save as my recipe) *****
{
try {
Recipes.NewRecipe x = new Recipes.NewRecipe();
if (!string.IsNullOrEmpty(id))
{
using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0}", Server.MapPath(dataSource)))) {
connection.Open();
string sql = string.Format("SELECT {0} FROM recipes WHERE id = '{1}'", mainSql, id);
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
Clients.Client client = new Clients.Client();
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
x = GetData(reader);
}
}
}
}
x.data = R.GetRecipeData(x.sharingData.recipeOwner.userGroupId, x.id);
x.sharingData.recipeId = x.id;
// x.mealGroup.title = R.GetMealGroupTitle(x.mealGroup.code);
x.recipeImg = Recipes.GetRecipeImg(x.sharingData.recipeOwner.userGroupId, x.id);
x.recipeImgPath = Recipes.GetRecipeImgPath(userId, id, x.recipeImg);
x.mealGroups = R.InitMealGroups(x.mealGroup.code);
x.userId = userId;
if (x.userId != null && x.userId != x.sharingData.recipeOwner.userId && isForEdit)
{
x.id = null;
}
x.isShared = true;
x.dishDesc = new Meals.DishDesc();
x.dishDesc.title = x.title;
x.dishDesc.desc = x.description;
x.dishDesc.id = x.id;
}
return(JsonConvert.SerializeObject(x, Formatting.None));
} catch (Exception e) {
L.SendErrorLog(e, id, userId, "SharingRecipes", "Get");
return(e.Message);
}
}
public string Save(Recipes.NewRecipe x)
{
x.sharingData.resp = new Global.Response();
try {
//string path = Server.MapPath(string.Format("~/App_Data/{0}", dataBase));
//db.CreateGlobalDataBase(path, db.sharingrecipes);
List <Recipes.MealGroup> selectedMealGroups = x.mealGroups.Where(a => a.isSelected).ToList();
string mealGroup = string.Join(";", x.mealGroups.Select(a => a.code).Distinct().ToList());
x.energy = x.data.selectedFoods.Sum(a => a.energy);
string sql = null;
if (!Check(x.id) || x.sharingData.adminSave)
{
sql = string.Format(@"BEGIN;
INSERT OR REPLACE INTO recipes ({0})
VALUES ('{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', {9}, '{10}', {11}, {12}, {13}, '{14}', {15});
COMMIT;", mainSql, x.id, x.sharingData.recipeOwner.userId, x.sharingData.recipeOwner.userGroupId, x.sharingData.recordDate, x.title, x.description, x.energy, mealGroup, x.sharingData.status.code, x.sharingData.status.note, x.sharingData.rank, x.sharingData.like, x.sharingData.views, x.sharingData.lang, x.consumers);
}
else
{
sql = string.Format(@"UPDATE recipes SET recordDate = '{1}', title = '{2}', desc = '{3}', energy = '{4}', mealGroup = '{5}', status = {6}, lang = '{7}', consumers = {8} WHERE id = '{0}'", x.id, x.sharingData.recordDate, x.title, x.description, x.energy, mealGroup, x.sharingData.status.code, x.sharingData.lang, x.consumers);
}
using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0}", Server.MapPath(dataSource)))) {
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
command.ExecuteNonQuery();
}
}
x.sharingData.resp.isSuccess = true;
x.sharingData.resp.msg = "saved";
return(JsonConvert.SerializeObject(x.sharingData.resp, Formatting.None));
} catch (Exception e) {
x.sharingData.resp.isSuccess = false;
x.sharingData.resp.msg = e.Message;
L.SendErrorLog(e, x.id, x.sharingData.recipeOwner.userId, "SharingRecipes", "Save");
return(JsonConvert.SerializeObject(x.sharingData.resp, Formatting.None));
}
}
public string Search(string userId, string query, string mealGroup, double?energyMin, double?energyMax, string lang)
{
List <Recipes.NewRecipe> xx = new List <Recipes.NewRecipe>();
try {
Global G = new Global();
query = G.SpecChrSearchQuery(query);
using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0}", Server.MapPath(dataSource)))) {
connection.Open();
string sql = string.Format(@"SELECT {0} FROM recipes {1} {2} {3} {4} {5} ORDER BY rowid DESC"
, mainSql
, (string.IsNullOrWhiteSpace(query) && string.IsNullOrEmpty(mealGroup) && string.IsNullOrEmpty(lang) && energyMin == null && energyMax == null) ? "" : "WHERE"
, !string.IsNullOrWhiteSpace(query) ? string.Format("(LOWER(title) LIKE '%{0}%' OR LOWER(desc) LIKE '%{0}%')", query) : ""
, !string.IsNullOrEmpty(mealGroup) ? string.Format(" {0} mealGroup LIKE '%{1}%'", !string.IsNullOrEmpty(query) ? "AND" : "", mealGroup) : ""
, (energyMin >= 0 && energyMax > 0) ? string.Format(" {0} (CAST(energy AS DOUBLE) >= {1} AND CAST(energy as DOUBLE) <= {2})", !string.IsNullOrEmpty(query) || !string.IsNullOrEmpty(mealGroup) ? "AND" : "", energyMin, energyMax) : ""
, !string.IsNullOrEmpty(lang) ? string.Format(" {0} lang = '{1}'", (!string.IsNullOrEmpty(query) || !string.IsNullOrEmpty(mealGroup) || (energyMin >= 0 && energyMax > 0)) ? "AND" : "", lang) : "");
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
Recipes.NewRecipe x = GetData(reader);
x.sharingData.recipeId = x.id;
// x.mealGroup.title = R.GetMealGroupTitle(x.mealGroup.code);
x.recipeImg = Recipes.GetRecipeImg(x.sharingData.recipeOwner.userGroupId, x.id);
x.recipeImgPath = Recipes.GetRecipeImgPath(userId, x.id, x.recipeImg);
xx.Add(x);
}
}
}
}
return(JsonConvert.SerializeObject(xx, Formatting.None));
} catch (Exception e) {
L.SendErrorLog(e, query, userId, "SharingRecipes", "Search");
return(JsonConvert.SerializeObject(xx, Formatting.None));
}
}
