コード例 #1
0
ファイル: Menues.cs プロジェクト: igprog/ppweb
 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));
     }
 }
コード例 #2
0
ファイル: Menues.cs プロジェクト: igprog/ppweb
    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);
    }
コード例 #3
0
ファイル: Menues.cs プロジェクト: igprog/ppweb
    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));
        }
    }
コード例 #4
0
ファイル: Menues.cs プロジェクト: mkbiltek2019/ppweb
 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); }
 }
コード例 #5
0
ファイル: Menues.cs プロジェクト: mkbiltek2019/ppweb
 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()); }
 }
コード例 #6
0
ファイル: Menues.cs プロジェクト: mkbiltek2019/ppweb
 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); }
 }