public string LoadClientMenues(string userId, string clientId, int?limit)
{
try {
List <NewMenu> xx = new List <NewMenu>();
//db.CreateDataBase(userId, db.menues);
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, userDataBase))) {
connection.Open();
string sql = string.Format(@"
SELECT id, title, diet, date, note, userId, clientId, userGroupId, energy
FROM menues WHERE userId = '{0}' AND clientId = '{1}'
ORDER BY rowid DESC {2}", userId, clientId, limit != null ? string.Format("LIMIT {0}", limit): "");
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
Clients.Client client = new Clients.Client();
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
NewMenu x = new NewMenu();
x.id = reader.GetValue(0) == DBNull.Value ? "" : reader.GetString(0);
x.title = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1);
x.diet = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.date = reader.GetValue(3) == DBNull.Value ? DateTime.UtcNow.ToString() : reader.GetString(3);
x.note = reader.GetValue(4) == DBNull.Value ? "" : reader.GetString(4);
x.userId = reader.GetValue(5) == DBNull.Value ? "" : reader.GetString(5);
//x.client = (reader.GetValue(6) == DBNull.Value || reader.GetValue(7) == DBNull.Value) ? new Clients.NewClient() : client.GetClient(reader.GetString(7), reader.GetString(6));
x.client = client.GetClient(userId, clientId);
x.userGroupId = reader.GetValue(7) == DBNull.Value ? "" : reader.GetString(7);
x.energy = reader.GetValue(8) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetString(8));
xx.Add(x);
}
}
}
}
return(JsonConvert.SerializeObject(xx, Formatting.None));
} catch (Exception e) {
L.SendErrorLog(e, null, userId, "Menus", "LoadClientMenues");
return(JsonConvert.SerializeObject(e.Message, Formatting.None));
}
}
private NewMenu GetData(SQLiteDataReader reader, string userId)
{
Clients.Client client = new Clients.Client();
NewMenu x = new NewMenu();
x.id = reader.GetValue(0) == DBNull.Value ? "" : reader.GetString(0);
x.title = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1);
x.diet = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.date = reader.GetValue(3) == DBNull.Value ? DateTime.UtcNow.ToString() : reader.GetString(3);
x.note = reader.GetValue(4) == DBNull.Value ? "" : reader.GetString(4);
x.userId = reader.GetValue(5) == DBNull.Value ? "" : reader.GetString(5);
x.client = (reader.GetValue(6) == DBNull.Value || reader.GetValue(7) == DBNull.Value) ? new Clients.NewClient() : client.GetClient(reader.GetString(7), reader.GetString(6));
x.userGroupId = reader.GetValue(7) == DBNull.Value ? "" : reader.GetString(7);
x.energy = reader.GetValue(8) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetString(8));
string data = reader.GetValue(9) == DBNull.Value ? null : reader.GetString(9);
if (!string.IsNullOrWhiteSpace(data))
{
x.data = JsonConvert.DeserializeObject <Data>(data); // new sistem: menu saved in db
}
else
{
string jsonData = GetJsonFile(userId, x.id); // old sistem: menu saved in json file.
if (!string.IsNullOrWhiteSpace(jsonData))
{
x.data = JsonConvert.DeserializeObject <Data>(jsonData);
}
}
if (x.data.selectedFoods.Count == 0)
{
x.data.selectedFoods = new List <Foods.NewFood>();
x.data.selectedInitFoods = new List <Foods.NewFood>();
Meals M = new Meals();
x.data.meals = M.LoadData();
}
if (x.data != null)
{
x.splitMealDesc = MealTitleDesc(x.data.meals);
}
x.client.clientData = new ClientsData.NewClientData();
string myMeals = reader.GetValue(10) == DBNull.Value ? null : reader.GetString(10);
if (!string.IsNullOrWhiteSpace(myMeals))
{
x.client.clientData.myMeals = JsonConvert.DeserializeObject <MyMeals.NewMyMeals>(myMeals); // new sistem: myMeals saved in db
}
else
{
x.client.clientData.myMeals = JsonConvert.DeserializeObject <MyMeals.NewMyMeals>(GetMyMealsJsonFile(userId, x.id)); // old sistem: myMeals saved in json file.
}
return(x);
}
public string Load(string userGroupId, int limit, int offset, string search, string clientId, string userId, double?energyMin, double?energyMax)
{
List <NewMenu> xx = new List <NewMenu>();
try {
//db.CreateDataBase(userGroupId, db.menues);
Global G = new Global();
search = G.SpecChrSearchQuery(search);
string whereSql = null;
if (!string.IsNullOrWhiteSpace(search) && string.IsNullOrEmpty(clientId) && energyMin == null && energyMax == null)
{
whereSql = string.Format("WHERE (LOWER(title) LIKE '%{0}%' OR LOWER(note) LIKE '%{0}%' OR LOWER(id) = '{0}')", search);
}
else if (!string.IsNullOrWhiteSpace(search) && !string.IsNullOrEmpty(clientId))
{
whereSql = string.Format("WHERE clientId = '{1}' AND (LOWER(title) LIKE '%{0}%' OR LOWER(note) LIKE '%{0}%' OR LOWER(id) = '{0}')", search, clientId);
}
else if (!string.IsNullOrWhiteSpace(search) && string.IsNullOrEmpty(clientId))
{
whereSql = string.Format("WHERE (LOWER(title) LIKE '%{0}%' OR LOWER(note) LIKE '%{0}%' OR LOWER(id) = '{0}')", search);
}
else if (string.IsNullOrWhiteSpace(search) && !string.IsNullOrEmpty(clientId))
{
whereSql = string.Format("WHERE clientId = '{0}'", clientId);
}
else
{
whereSql = null;
}
if (energyMin >= 0 && energyMax > 0)
{
whereSql = string.Format(" {0} (CAST(energy AS DOUBLE) >= {1} AND CAST(energy as DOUBLE) <= {2})", string.IsNullOrEmpty(whereSql) ? "WHERE" : string.Format("{0} AND", whereSql), energyMin, energyMax);
}
if (!string.IsNullOrEmpty(userId))
{
whereSql = string.Format("{0} userId = '{1}'", string.IsNullOrEmpty(whereSql) ? "WHERE" : string.Format("{0} AND", whereSql), userId);
}
string sql = string.Format(@"SELECT id, title, diet, date, note, userId, clientId, userGroupId, energy FROM menues {0}
ORDER BY rowid DESC LIMIT {1} OFFSET {2} ", whereSql, limit, offset);
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userGroupId, userDataBase))) {
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
Clients.Client client = new Clients.Client();
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
NewMenu x = new NewMenu();
x.id = reader.GetValue(0) == DBNull.Value ? "" : reader.GetString(0);
x.title = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1);
x.diet = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.date = reader.GetValue(3) == DBNull.Value ? DateTime.UtcNow.ToString() : reader.GetString(3);
x.note = reader.GetValue(4) == DBNull.Value ? "" : reader.GetString(4);
x.userId = reader.GetValue(5) == DBNull.Value ? "" : reader.GetString(5);
if (!string.IsNullOrEmpty(clientId))
{
x.client = client.GetClient(userGroupId, clientId);
}
else
{
x.client = (reader.GetValue(6) == DBNull.Value || reader.GetValue(7) == DBNull.Value) ? new Clients.NewClient() : client.GetClient(reader.GetString(7), reader.GetString(6));
}
x.userGroupId = reader.GetValue(7) == DBNull.Value ? "" : reader.GetString(7);
x.energy = reader.GetValue(8) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetString(8));
xx.Add(x);
}
}
}
}
if (xx.Count > 0)
{
foreach (var m in xx)
{
if (!string.IsNullOrEmpty(m.userId))
{
Users U = new Users();
m.author = U.GetUserFullName(m.userId, true);
}
}
}
return(JsonConvert.SerializeObject(xx, Formatting.None));
} catch (Exception e) {
L.SendErrorLog(e, null, userGroupId, "Menus", "Load");
return(JsonConvert.SerializeObject(xx, Formatting.None));
}
}
public string Load(string userId, int limit, int offset, string search)
{
try {
db.CreateDataBase(userId, db.menues);
List <NewMenu> xx = new List <NewMenu>();
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, dataBase))) {
connection.Open();
string sql = string.Format(@"SELECT id, title, diet, date, note, userId, clientId, userGroupId, energy
FROM menues
{0}
ORDER BY rowid DESC LIMIT {1} OFFSET {2} "
, !string.IsNullOrWhiteSpace(search) ? string.Format("WHERE title LIKE '%{0}%' OR note LIKE '%{0}%' OR energy LIKE '{0}%'", search) : ""
, limit
, offset);
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
Clients.Client client = new Clients.Client();
using (SQLiteDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
NewMenu x = new NewMenu();
x.id = reader.GetValue(0) == DBNull.Value ? "" : reader.GetString(0);
x.title = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1);
x.diet = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.date = reader.GetValue(3) == DBNull.Value ? DateTime.UtcNow.ToString() : reader.GetString(3);
x.note = reader.GetValue(4) == DBNull.Value ? "" : reader.GetString(4);
x.userId = reader.GetValue(5) == DBNull.Value ? "" : reader.GetString(5);
x.client = (reader.GetValue(6) == DBNull.Value || reader.GetValue(7) == DBNull.Value) ? new Clients.NewClient() : client.GetClient(reader.GetString(7), reader.GetString(6));
x.userGroupId = reader.GetValue(7) == DBNull.Value ? "" : reader.GetString(7);
x.energy = reader.GetValue(8) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetString(8));
xx.Add(x);
}
}
}
connection.Close();
}
return(JsonConvert.SerializeObject(xx, Formatting.None));
} catch (Exception e) { return(e.Message); }
}
public NewMenu WeeklyMenu(string userId, string menuId)
{
try {
NewMenu x = new NewMenu();
if (!string.IsNullOrEmpty(menuId))
{
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, dataBase))) {
connection.Open();
string sql = string.Format(@"SELECT id, title, diet, date, note, userId, clientId, userGroupId, energy
FROM menues
WHERE id = '{0}'", menuId);
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
Clients.Client client = new Clients.Client();
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.diet = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.date = reader.GetValue(3) == DBNull.Value ? DateTime.UtcNow.ToString() : reader.GetString(3);
x.note = reader.GetValue(4) == DBNull.Value ? "" : reader.GetString(4);
x.userId = reader.GetValue(5) == DBNull.Value ? "" : reader.GetString(5);
x.client = reader.GetValue(6) == DBNull.Value ? new Clients.NewClient() : client.GetClient(x.userId, reader.GetString(6));
x.userGroupId = reader.GetValue(7) == DBNull.Value ? "" : reader.GetString(7);
x.energy = reader.GetValue(8) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetString(8));
x.data = JsonConvert.DeserializeObject <Data>(GetJsonFile(userId, x.id));
}
}
}
connection.Close();
}
}
return(x);
} catch (Exception e) { return(new NewMenu()); }
}
public string Get(string userId, string id)
{
try {
NewMenu x = new NewMenu();
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, dataBase))) {
connection.Open();
string sql = @"SELECT id, title, diet, date, note, userId, clientId, userGroupId, energy
FROM menues
WHERE id = @id";
using (SQLiteCommand command = new SQLiteCommand(sql, connection)) {
command.Parameters.Add(new SQLiteParameter("id", id));
Clients.Client client = new Clients.Client();
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.diet = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2);
x.date = reader.GetValue(3) == DBNull.Value ? DateTime.UtcNow.ToString() : reader.GetString(3);
x.note = reader.GetValue(4) == DBNull.Value ? "" : reader.GetString(4);
x.userId = reader.GetValue(5) == DBNull.Value ? "" : reader.GetString(5);
x.client = (reader.GetValue(6) == DBNull.Value || reader.GetValue(7) == DBNull.Value) ? new Clients.NewClient() : client.GetClient(reader.GetString(7), reader.GetString(6));
x.userGroupId = reader.GetValue(7) == DBNull.Value ? "" : reader.GetString(7);
x.energy = reader.GetValue(8) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetString(8));
x.data = JsonConvert.DeserializeObject <Data>(GetJsonFile(userId, x.id));
x.client.clientData = new ClientsData.NewClientData();
x.client.clientData.myMeals = JsonConvert.DeserializeObject <MyMeals.NewMyMeals>(GetMyMealsJsonFile(userId, x.id));
}
}
}
connection.Close();
}
return(JsonConvert.SerializeObject(x, Formatting.None));
} catch (Exception e) { return(e.Message); }
}