public string Save(string userId, NewWeeklyMenus x)
{
try {
db.CreateDataBase(userId, db.weeklymenus);
if (string.IsNullOrEmpty(x.id) && Check(userId, x.title))
{
return("error");
}
else
{
if (string.IsNullOrEmpty(x.id))
{
x.id = Convert.ToString(Guid.NewGuid());
}
string sql = "";
SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, dataBase));
connection.Open();
SQLiteCommand command = new SQLiteCommand(sql, connection);
sql = string.Format(@"BEGIN;
INSERT OR REPLACE INTO weeklymenus (id, title, note, dietId, diet, menuList, date, clientId, userId, userGroupId)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}');
COMMIT;", x.id, x.title, x.note, x.diet.id, x.diet.diet, string.Join(",", x.menuList), x.date, x.client.clientId, x.userId, x.userGroupId);
command = new SQLiteCommand(sql, connection);
command.ExecuteNonQuery();
connection.Close();
return(JsonConvert.SerializeObject(x, Formatting.None));
}
} catch (Exception e) { return(e.Message); }
}
public string Init(Users.NewUser user, Clients.NewClient client, string lang)
{
NewWeeklyMenus x = new NewWeeklyMenus();
x.id = null;
x.title = null;
x.note = null;
x.diet = new Diets.NewDiet();
x.diet.id = client.clientData.diet.id;
x.diet.diet = t.Tran(client.clientData.diet.diet, lang);
x.menuList = new List <string>()
{
"", "", "", "", "", "", ""
};
x.menuDes = new List <MenuDes>()
{
new MenuDes(), new MenuDes(), new MenuDes(), new MenuDes(), new MenuDes(), new MenuDes(), new MenuDes()
};
x.date = DateTime.UtcNow.ToString();
x.client = client;
x.userId = user.userId;
x.userGroupId = user.userGroupId;
x.isNutritionStandard = false;
return(JsonConvert.SerializeObject(x, Formatting.None));
}
public string Get(string userId, string id, string lang, bool isNutritionStandard)
{
try {
NewWeeklyMenus x = new NewWeeklyMenus();
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, dataBase))) {
connection.Open();
string sql = string.Format(@"SELECT w.id, w.title, w.note, w.dietId, w.diet, w.menuList, w.date, w.clientId, c.firstName, c.lastName, w.userId, w.userGroupId FROM weeklymenus w
LEFT OUTER JOIN clients c ON w.clientId = w.clientId
WHERE w.id = '{0}' GROUP BY w.id ORDER BY w.rowid DESC", id);
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
x = WeeklyMenuData(reader, lang);
x.menuDes = GetMenuDes(connection, x.menuList, lang, isNutritionStandard);
}
}
}
}
return(JsonConvert.SerializeObject(x, Formatting.None));
} catch (Exception e) {
L.SendErrorLog(e, id, userId, "WeeklyMenus", "Get");
return(JsonConvert.SerializeObject(e.Message, Formatting.None));
}
}
public string Save(Users.NewUser user, NewWeeklyMenus x)
{
SaveResponse r = new SaveResponse();
try {
//db.CreateDataBase(user.userGroupId, db.weeklymenus);
//db.AddColumn(user.userGroupId, db.GetDataBasePath(user.userGroupId, dataBase), db.weeklymenus, "isNutritionStandard", "VARCHAR"); //new column in menues tbl.
if (string.IsNullOrEmpty(x.id) && Check(user.userGroupId, x.title))
{
r.data = x;
r.msg = "there is already a menu with the same name";
r.isSuccess = false;
return(JsonConvert.SerializeObject(r, Formatting.None));
}
Global G = new Global();
if (!G.CheckUserPermission(user, x.userId) && !string.IsNullOrWhiteSpace(x.id))
{
r.isSuccess = false;
r.msg = "you can only save menus that you have created yourself";
return(JsonConvert.SerializeObject(r, Formatting.None));
}
if (string.IsNullOrEmpty(x.id))
{
x.id = Convert.ToString(Guid.NewGuid());
}
x.title = G.RemoveSingleQuotes(x.title);
x.note = G.RemoveSingleQuotes(x.note);
string sql = string.Format(@"BEGIN;
INSERT OR REPLACE INTO weeklymenus (id, title, note, dietId, diet, menuList, date, clientId, userId, userGroupId, isNutritionStandard)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}');
COMMIT;", x.id, x.title, x.note, x.diet.id, x.diet.diet, string.Join(",", x.menuList), x.date, x.client.clientId, x.userId, x.userGroupId, x.isNutritionStandard);
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(user.userGroupId, dataBase))) {
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
command.ExecuteNonQuery();
}
}
r.data = x;
r.msg = "saved";
r.isSuccess = true;
return(JsonConvert.SerializeObject(r, Formatting.None));
} catch (Exception e) {
r.data = x;
r.msg = e.Message;
r.msg1 = "report a problem";
r.isSuccess = false;
L.SendErrorLog(e, x.id, user.userId, "WeeklyMenus", "Save");
return(JsonConvert.SerializeObject(r, Formatting.None));
}
}
private NewWeeklyMenus WeeklyMenuData(SQLiteDataReader reader, string lang)
{
NewWeeklyMenus x = new NewWeeklyMenus();
x.id = reader.GetValue(0) == DBNull.Value ? "" : reader.GetString(0);
x.title = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1);
x.note = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.diet = new Diets.NewDiet();
x.diet.id = reader.GetValue(3) == DBNull.Value ? "" : reader.GetString(3);
x.diet.diet = reader.GetValue(4) == DBNull.Value ? "" : t.Tran(reader.GetString(4), lang);
x.menuList = reader.GetValue(5) == DBNull.Value ? new List <string>() : reader.GetString(5).Split(',').ToList();
x.date = reader.GetValue(6) == DBNull.Value ? DateTime.UtcNow.ToString() : reader.GetString(6);
x.client = new Clients.NewClient();
x.client.clientId = reader.GetValue(7) == DBNull.Value ? "" : reader.GetString(7);
x.client.firstName = reader.GetValue(8) == DBNull.Value ? "" : reader.GetString(8);
x.client.lastName = reader.GetValue(9) == DBNull.Value ? "" : reader.GetString(9);
x.userId = reader.GetValue(10) == DBNull.Value ? "" : reader.GetString(10);
x.userGroupId = reader.GetValue(11) == DBNull.Value ? "" : reader.GetString(11);
return(x);
}
public string Delete(Users.NewUser user, NewWeeklyMenus menu, string lang)
{
var x = new Global.Response();
try {
if (!string.IsNullOrEmpty(user.userGroupId) && !string.IsNullOrEmpty(menu.id))
{
Global G = new Global();
if (!G.CheckUserPermission(user, menu.userId))
{
x.isSuccess = false;
x.msg = "you can only delete menus that you have created yourself";
return(JsonConvert.SerializeObject(x, Formatting.None));
}
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(user.userGroupId, dataBase))) {
connection.Open();
string sql = string.Format("DELETE FROM weeklymenus WHERE id = '{0}'", menu.id);
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
command.ExecuteNonQuery();
}
}
// List<NewWeeklyMenus> xx = LoadWeeklyMenus(user.userGroupId, 20, 0, null, null, null, lang);
x.isSuccess = true;
x.msg = "deleted";
return(JsonConvert.SerializeObject(x, Formatting.None));
}
else
{
x.isSuccess = false;
x.msg = "error";
return(JsonConvert.SerializeObject(x, Formatting.None));
}
} catch (Exception e) {
x.isSuccess = false;
x.msg = e.Message;
L.SendErrorLog(e, menu.id, user.userId, "WeeklyMenus", "Delete");
return(JsonConvert.SerializeObject(x, Formatting.None));
}
}
public List <NewWeeklyMenus> LoadWeeklyMenus(string userId, string lang)
{
db.CreateDataBase(userId, db.weeklymenus);
List <NewWeeklyMenus> xx = new List <NewWeeklyMenus>();
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, dataBase))) {
connection.Open();
string sql = @"SELECT w.id, w.title, w.note, w.dietId, w.diet, w.menuList, w.date, w.clientId, c.firstName, c.lastName, w.userId, w.userGroupId FROM weeklymenus w
LEFT OUTER JOIN clients c ON w.clientId = w.clientId
GROUP BY w.id
ORDER BY w.rowid DESC";
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
NewWeeklyMenus x = new NewWeeklyMenus();
x.id = reader.GetValue(0) == DBNull.Value ? "" : reader.GetString(0);
x.title = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1);
x.note = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.diet = new Diets.NewDiet();
x.diet.id = reader.GetValue(3) == DBNull.Value ? "" : reader.GetString(3);
x.diet.diet = reader.GetValue(4) == DBNull.Value ? "" : t.Tran(reader.GetString(4), lang);
x.menuList = reader.GetValue(5) == DBNull.Value ? new List <string>() : reader.GetString(5).Split(',').ToList();
x.date = reader.GetValue(6) == DBNull.Value ? DateTime.UtcNow.ToString() : reader.GetString(6);
x.client = new Clients.NewClient();
x.client.clientId = reader.GetValue(7) == DBNull.Value ? "" : reader.GetString(7);
x.client.firstName = reader.GetValue(8) == DBNull.Value ? "" : reader.GetString(8);
x.client.lastName = reader.GetValue(9) == DBNull.Value ? "" : reader.GetString(9);
x.userId = reader.GetValue(10) == DBNull.Value ? "" : reader.GetString(10);
x.userGroupId = reader.GetValue(11) == DBNull.Value ? "" : reader.GetString(11);
xx.Add(x);
}
}
}
connection.Close();
}
return(xx);
}
public string Get(string userId, string id, string lang)
{
try {
NewWeeklyMenus x = new NewWeeklyMenus();
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, dataBase))) {
connection.Open();
string sql = string.Format(@"SELECT w.id, w.title, w.note, w.dietId, w.diet, w.menuList, w.date, w.clientId, c.firstName, c.lastName, w.userId, w.userGroupId FROM weeklymenus w
LEFT OUTER JOIN clients c ON w.clientId = w.clientId
WHERE w.id = '{0}' GROUP BY w.id ORDER BY w.rowid DESC", id);
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
x.id = reader.GetValue(0) == DBNull.Value ? "" : reader.GetString(0);
x.title = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1);
x.note = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.diet = new Diets.NewDiet();
x.diet.id = reader.GetValue(3) == DBNull.Value ? "" : reader.GetString(3);
x.diet.diet = reader.GetValue(4) == DBNull.Value ? "" : t.Tran(reader.GetString(4), lang);
x.menuList = reader.GetValue(5) == DBNull.Value ? new List <string>() : reader.GetString(5).Split(',').ToList();
//TODO: menu data
x.menuDes = GetMenuDes(connection, x.menuList, lang);
x.date = reader.GetValue(6) == DBNull.Value ? DateTime.UtcNow.ToString() : reader.GetString(6);
x.client = new Clients.NewClient();
x.client.clientId = reader.GetValue(7) == DBNull.Value ? "" : reader.GetString(7);
x.client.firstName = reader.GetValue(8) == DBNull.Value ? "" : reader.GetString(8);
x.client.lastName = reader.GetValue(9) == DBNull.Value ? "" : reader.GetString(9);
x.userId = reader.GetValue(10) == DBNull.Value ? "" : reader.GetString(10);
x.userGroupId = reader.GetValue(11) == DBNull.Value ? "" : reader.GetString(11);
}
}
}
connection.Close();
}
return(JsonConvert.SerializeObject(x, Formatting.None));
} catch (Exception e) { return(JsonConvert.SerializeObject(e.Message, Formatting.None)); }
}