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