예제 #1
0
        public DodoItemCollection GetItems(int skip, int take, string userId)
        {
            var ret = new DodoItemCollection();

            using (var conn = CreateConnection())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select uniqueId from {0} where " +
                                      "visible = true and " +
                                      "userid = ?userId ";

                    cmd.CommandText += "order by keywords {1} ";
                    cmd.CommandText += "limit {2}, {3} ";

                    cmd.CommandText = string.Format(cmd.CommandText,
                                                    DodoDb.table_poweq1,
                                                    DodoApplication.collateMySql,
                                                    skip, take);

                    cmd.Parameters.AddWithValue("userId", userId);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ret.Add(DodoItemBase.GetItem <DodoItem>(
                                        app, reader.GetInt64(reader.GetOrdinal("uniqueId"))));
                        }
                    }
                }
            }
            return(ret);
        }
예제 #2
0
        public List <DodoItem> GetFolders(DodoSession session, long folderId)
        {
            var ret = new List <DodoItem>();

            using (var conn = CreateConnection())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select uniqueId, keywords, userId " +
                                      "from poweq1 " +
                                      "where userId = @userId and type = 'folder' and folderId = @folderid " +
                                      "order by keywords";

                    cmd.Parameters.AddWithValue("userId", session.userId);
                    cmd.Parameters.AddWithValue("folderid", folderId);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ret.Add(DodoItemBase.GetItem <DodoItem>(
                                        app, reader.GetInt64(reader.GetOrdinal("uniqueId"))));
                        }
                    }
                }
            }
            return(ret);
        }
예제 #3
0
        public DodoItem GetRecord(long uniqueId)
        {
            DodoItem ret = null;

            using (var conn = CreateConnection())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select " +
                                      "uniqueId, userId, keywords, modified, registered, type, fileName, " +
                                      "fileContentType, fileSize, boxText, folderId, weight, visible " +
                                      "from poweq1 " +
                                      "where uniqueid = @uniqueId";

                    // SqlUtility.AddParameter(cmd, "uniqueId", uniqueId);
                    cmd.Parameters.AddWithValue("uniqueId", uniqueId);

                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            ret = DodoItemBase.GetItem <DodoItem>(
                                app, reader.GetInt64(reader.GetOrdinal("uniqueId")));
                        }
                    }
                }
            }
            return(ret);
        }
예제 #4
0
        public DodoItem GetFolder(string id, DodoSession s)
        {
            DodoItem ret = null;

            if (id.CompareTo("-1") == 0)
            {
                // objeto nuevo
                ret          = new DodoItem();
                ret.UniqueId = -1;
                ret.UserId   = s.userId;
            }
            else
            {
                using (var conn = CreateConnection())
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "select uniqueId from poweq1 where uniqueId = @uniqueId";

                        cmd.Parameters.AddWithValue("uniqueId", id);
                        using (var reader = cmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                ret = DodoItemBase.GetItem <DodoItem>(
                                    app, reader.GetInt64(reader.GetOrdinal("uniqueId")));
                            }
                        }
                    }
            }
            return(ret);
        }
예제 #5
0
        public IEnumerable <DodoItem> GetLastItems(string userId, string typeItem)
        {
            using (var conn = CreateConnection())
                using (var cmd = conn.CreateCommand())
                {
                    var sql = "select uniqueId from {0}";
                    sql += " where userId = @userId and type = @type";
                    sql += " order by registered desc";
                    sql += " limit 0, 10";

                    cmd.CommandText = string.Format(sql, table_poweq1);

                    cmd.Parameters.AddWithValue("userId", Convert.ToInt64(userId));
                    cmd.Parameters.AddWithValue("type", typeItem);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            yield return(DodoItemBase.GetItem <DodoItem>(
                                             app, reader.GetInt64(reader.GetOrdinal("uniqueId"))));
                        }
                    }
                }
        }
예제 #6
0
        public DodoItemCollection SearchMyItems(string userId, string keySearch)
        {
            var ret = new DodoItemCollection();

            using (var conn = CreateConnection())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select uniqueId from poweq1 where " +
                                      "visible = true and " +
                                      "userid = ?userId and ";

                    if (keySearch.IndexOf("|") > 0)
                    {
                        keySearch        = keySearch.Replace("|", @"\.*");
                        cmd.CommandText += string.Format("keywords regexp '{0}' ", keySearch);
                    }
                    else
                    {
                        cmd.CommandText += "keywords like concat('%', @keywords, '%') ";
                        cmd.Parameters.AddWithValue("keywords", keySearch);
                    }

                    cmd.CommandText += "order by keywords {0}";

                    cmd.CommandText = string.Format(cmd.CommandText,
                                                    DodoApplication.collateMySql);

                    cmd.Parameters.AddWithValue("userId", userId);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ret.Add(DodoItemBase.GetItem <DodoItem>(
                                        app, reader.GetInt64(reader.GetOrdinal("uniqueId"))));
                        }
                    }
                }
            }
            return(ret);
        }
예제 #7
0
        public DodoItemCollection SearchMyRecords(int userId, string keySearch, long folderId)
        {
            var ret = new DodoItemCollection();

            using (var conn = CreateConnection())
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select a.uniqueId " +
                                      "from poweq1 a where " +
                                      "a.userid = ?userId and " +
                                      "a.keywords like concat('%', ?keywords, '%')";

                    if (folderId > -1)
                    {
                        cmd.CommandText += " and a.folderId = ?folderId";
                    }

                    cmd.CommandText += " order by a.weight desc, a.keywords {0}";

                    cmd.CommandText = string.Format(cmd.CommandText, DodoApplication.collateMySql);

                    cmd.Parameters.AddWithValue("userId", userId);
                    cmd.Parameters.AddWithValue("keywords", keySearch);
                    cmd.Parameters.AddWithValue("folderId", folderId);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ret.Add(DodoItemBase.GetItem <DodoItem>(
                                        app, reader.GetInt64(reader.GetOrdinal("uniqueId"))));
                        }
                    }
                }

            return(ret);
        }
예제 #8
0
        public IEnumerable <DodoItem> GetRecordsFromFolder(long folderId)
        {
            using (var conn = CreateConnection())
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select uniqueId from poweq1 a where " +
                                      "a.folderId = ?folderId and visible = true ";
                    cmd.CommandText += "order by a.weight desc, a.keywords {0}";
                    cmd.CommandText  = string.Format(cmd.CommandText, DodoApplication.collateMySql);

                    cmd.Parameters.AddWithValue("folderId", folderId);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            yield return(DodoItemBase.GetItem <DodoItem>(
                                             app, reader.GetInt64(reader.GetOrdinal("uniqueId"))));
                        }
                    }
                }
            }
        }
예제 #9
0
        public DodoItem GetItem(long id)
        {
            DodoItem ret = DodoItemBase.GetItem <DodoItem>(app, id);

            return(ret);
        }