Example #1
0
        public static IDictionary<DateTime, int> CommentsByDate(DateTime min, DateTime max)
        {
            DataProvider dp = DataService.Provider;
            QueryCommand cmd = new QueryCommand(@"
                select " +
                    dp.SqlYearFunction("c.Published") + " as dvYear, " +
                    dp.SqlMonthFunction("c.Published") + " as dvMonth, " +
                    dp.SqlDayFunction("c.Published") + " as dvDay, " +
                    dp.SqlCountFunction("c.Id") + @" as IdCount
                from
                    graffiti_Comments AS c
                left outer join
                    graffiti_Posts AS p on p.Id = c.PostId
                where
                    c.Published >= " + dp.SqlVariable("MinDate") + @" and c.Published < " + dp.SqlVariable("MaxDate") + @"
                    and p.CategoryId in " + RolePermissionManager.GetInClauseForReadPermissions(GraffitiUsers.Current) + @"
                    and c.IsDeleted = 0
                group by " +
                    dp.SqlYearFunction("c.Published") + ", " +
                    dp.SqlMonthFunction("c.Published") + ", " +
                    dp.SqlDayFunction("c.Published")
                );

            Parameter pPublished = Comment.FindParameter("Published");
            cmd.Parameters.Add("MinDate", min, pPublished.DbType);
            cmd.Parameters.Add("MaxDate", max.AddDays(1), pPublished.DbType);

            return GetDateDictionary(cmd);
        }
Example #2
0
        public static ReportData CommentsByDateSingle(DateTime date)
        {
            // top 10
            DataProvider dp = DataService.Provider;
            QueryCommand cmd = new QueryCommand(@"
                select Title, Id, IdCount FROM ( SELECT
                    max(p.Title) as Title, p.Id
                    , " + dp.SqlCountFunction("c.Id") + @" as IdCount
                from
                    graffiti_Comments AS c
                left outer join
                    graffiti_Posts AS p on p.Id = c.PostId
                where
                    c.Published >= " + dp.SqlVariable("MinDate") + @" and c.Published < " + dp.SqlVariable("MaxDate") + @"
                    and p.CategoryId in " + RolePermissionManager.GetInClauseForReadPermissions(GraffitiUsers.Current) + @"
                    and c.IsDeleted = 0
                group by
                    p.Id ) as dv
                order by
                    IdCount desc
                ");

            Parameter pPublished = Comment.FindParameter("Published");
            cmd.Parameters.Add("MinDate", date, pPublished.DbType);
            cmd.Parameters.Add("MaxDate", date.AddDays(1), pPublished.DbType);

            return GetPostDictionary(cmd, 10);
        }
Example #3
0
        public static void RemoveLogsOlderThan(int hours)
        {
            DateTime dt = DateTime.Now.AddHours(-1*hours);

            QueryCommand command = new QueryCommand("DELETE FROM graffiti_Logs WHERE CreatedOn <= " + DataService.Provider.SqlVariable("CreatedOn"));
            command.Parameters.Add(Log.FindParameter("CreatedOn")).Value = dt;
            int i = DataService.ExecuteNonQuery(command);
            //if(i > 0)
            //    Info("Log", "{0} item(s) were just removed from the logs",i);
        }
Example #4
0
        public override int Insert(Table table, List<Parameter> parameters)
        {
            if (table.IsReadOnly)
                throw new Exception("Readonly tables (views) cannot recieve inserts");

            StringBuilder sb = new StringBuilder();
            QueryCommand command = new QueryCommand();

            sb.AppendFormat("INSERT INTO {0} (", QuoteName(table.TableName));

            bool isFirst = true;
            foreach (Column column in table.Columns)
            {
                if (column.Name != table.PrimaryKey)
                {
                    sb.AppendFormat(" {0} {1}"
                        , isFirst ? string.Empty : ", "
                        , QuoteName(column.Name)
                        );
                    isFirst = false;
                }
            }

            sb.Append(") VALUES (");
            isFirst = true;

            foreach (Column column in table.Columns)
            {
                if (column.Name != table.PrimaryKey)
                {
                    sb.AppendFormat(" {0} {1}", isFirst ? string.Empty : ", ", SqlVariable(column.Name));
                    command.Parameters.Add( parameters.Find( delegate(Parameter p) { return (p.Name == column.Name); } ) );
                    isFirst = false;
                }
            }

            sb.Append(")");
            command.Sql = sb.ToString();
            using(DbConnection conn = GetConnection())
            {
                conn.Open();
                DbCommand dbCommand = GetCommand(command, conn);
                dbCommand.ExecuteNonQuery();
                DbCommand c2 = GetFactory().CreateCommand();
                c2.CommandText = GetSelectNextId(table.TableName,table.PrimaryKey);
                c2.Connection = conn;
                object obj = c2.ExecuteScalar();
                conn.Close();
                return Int32.Parse(obj.ToString());
            }
        }
 public static int GetNextVersionId(int postid, int currentVersion)
 {
     if (postid <= 0)
         return 1;
     else
     {
         QueryCommand command = new QueryCommand("Select Max(v.Version) FROM graffiti_VersionStore as v where v.Name = " + DataService.Provider.SqlVariable("Name"));
         command.Parameters.Add(VersionStore.FindParameter("Name")).Value = "Post:" + postid.ToString();
         object obj = DataService.ExecuteScalar(command);
         if (obj == null || obj is System.DBNull)
             return 2;
         else
             return Math.Max(((int)obj), currentVersion) + 1;
     }
 }
Example #6
0
        public override string ToSQL(QueryCommand cmd, Table tbl, string letter, bool isFirst)
        {
            string result = isFirst ? "" : (_useOr ? " OR " : " AND ");

            result +=
                DataService.Provider.QuoteName( tbl.TableName )
                    + "." + DataService.Provider.QuoteName( _column.Name )
                + " "
                + DataService.GetComparisonOperator(_comp)
                + " "
                + DataService.Provider.SqlVariable(_column.Name + "_" + letter);

            cmd.Parameters.Add(_column.Name + "_" + letter, _value, _column.DbType);

            return result;
        }
Example #7
0
        public override string ToSQL(QueryCommand cmd, Table tbl, string letter, bool isFirst)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(isFirst ? "" : (_useOr ? " OR " : " AND "))
            .Append(DataService.Provider.QuoteName(tbl.TableName))
            .Append(".")
            .Append(DataService.Provider.QuoteName(_column.Name))
            .Append(" IN (");

            if (int_Ids != null && int_Ids.Length > 0)
            {
                sb.Append(int_Ids[0]);

                for (int i = 1; i < int_Ids.Length; i++)
                {
                    sb.Append(", ")
                    .Append(int_Ids[i]);
                }
            }
            else if (string_Keys != null && string_Keys.Length > 0)
            {
                sb.Append(DataService.Provider.SqlVariable(_column.Name + "_" + letter + "_0"));
                cmd.Parameters.Add(_column.Name + "_" + letter + "_0", string_Keys[0], _column.DbType);

                for (int i = 1; i < string_Keys.Length; i++)
                {
                    sb.Append(", ")
                    .Append(DataService.Provider.SqlVariable(_column.Name + "_" + letter + "_" + i));

                    cmd.Parameters.Add(_column.Name + "_" + letter + "_" + i, string_Keys[i], _column.DbType);
                }
            }

            sb.Append(")");

            return(sb.ToString());
        }
Example #8
0
        public override string ToSQL(QueryCommand cmd, Table tbl, string letter, bool isFirst)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(isFirst ? "" : (_useOr ? " OR " : " AND "))
                .Append( DataService.Provider.QuoteName( tbl.TableName ) )
                .Append( "." )
                .Append( DataService.Provider.QuoteName( _column.Name ) )
                .Append( " IN (" );

            if(int_Ids != null && int_Ids.Length > 0)
            {
                sb.Append( int_Ids[0] );

                for(int i = 1; i < int_Ids.Length; i++)
                {
                    sb.Append( ", " )
                        .Append( int_Ids[i] );
                }
            }
            else if(string_Keys != null && string_Keys.Length > 0)
            {
                sb.Append( DataService.Provider.SqlVariable( _column.Name + "_" + letter + "_0" ) );
                cmd.Parameters.Add( _column.Name + "_" + letter + "_0", string_Keys[0], _column.DbType );

                for(int i = 1; i<string_Keys.Length; i++)
                {
                    sb.Append( ", " )
                        .Append( DataService.Provider.SqlVariable( _column.Name + "_" + letter + "_" + i ) );

                    cmd.Parameters.Add( _column.Name + "_" + letter + "_" + i, string_Keys[i], _column.DbType );
                }
            }

            sb.Append(")");

            return sb.ToString();
        }
Example #9
0
        public override string ToSQL(QueryCommand cmd, Table tbl, string letter, bool isFirst)
        {
            if (_wheres.Count > 1)
            {
                string result = isFirst ? "" : (_useOr ? " OR " : " AND ");
                result += "(";

                int position = 1;
                foreach (WHERE where in _wheres)
                {
                    //result += (position == 1) ? " " : (_orChildren ? " OR " : " AND ");
                    result += where.ToSQL(cmd, tbl, letter + "_" + position, position == 1);
                    position++;
                }

                result += ")";

                return result;
            }
            else if (_wheres.Count == 1)
                return _wheres[0].ToSQL(cmd, tbl, letter, false);

            return string.Empty;
        }
Example #10
0
        public static int GetPostIdByName(string name)
        {
            object id = ZCache.Get<object>("PostIdByName-" + name);
            if (id == null)
            {
                string postName;
                string categoryName = null;

                if (name.Contains("/"))
                {
                    string[] parts = name.Split('/');

                    for (int i = 0; i < parts.Length; i++)
                        parts[i] = Util.CleanForUrl(parts[i]);

                    switch(parts.Length)
                    {
                        case 2:
                            categoryName = parts[0];
                            postName = parts[1];
                            break;
                        case 3:
                            categoryName = parts[0] + "/" + parts[1];
                            postName = parts[2];
                            break;
                        default:
                            return -1;
                    }
                }
                else
                    postName = Util.CleanForUrl(name);

                int categoryId = -1;
                if (categoryName != null)
                {
                    CategoryCollection the_categories = categories.GetCachedCategories();

                    foreach (Category category in the_categories)
                        if (category.LinkName == categoryName)
                            categoryId = category.Id;

                    if (categoryId == -1)
                        return -1;
                }

                List<Parameter> parameters = Post.GenerateParameters();

                /* this is supposed to be TOP 1, but the ExecuteScalar will pull only the first one */
                QueryCommand cmd = new QueryCommand("Select Id FROM graffiti_Posts Where Name = " + DataService.Provider.SqlVariable("Name") + " and IsDeleted = 0");
                cmd.Parameters.Add(Post.FindParameter(parameters, "Name")).Value = postName;

                if (categoryId > -1)
                {
                    cmd.Sql += " and CategoryId = " + DataService.Provider.SqlVariable("CategoryId");
                    cmd.Parameters.Add(Post.FindParameter(parameters, "CategoryId")).Value = categoryId;
                }

                cmd.Sql += " order by CategoryId asc";

                object postobj = DataService.ExecuteScalar(cmd);
                if (postobj != null)
                {
                    id = postobj;
                    ZCache.InsertCache("PostIdByName-" + name, (int)id, 60);
                }
                else
                    id = -1;
            }
            return (int) id;
        }
Example #11
0
        public static ReportData MostPopularPosts()
        {
            // top 5
            DataProvider dp = DataService.Provider;
            QueryCommand cmd = new QueryCommand(@"
                SELECT Title, Id, IdCount FROM ( SELECT
                    p.Title, p.Id, " + dp.SqlCountFunction("p.Id") + @" as IdCount
                from
                    graffiti_Post_Statistics AS ps
                left outer join
                    graffiti_Posts AS p on p.Id = ps.PostId
                where p.CategoryId in " + RolePermissionManager.GetInClauseForReadPermissions(GraffitiUsers.Current) + @"
                group by
                    p.Title, p.Id) as dv
                order by
                    IdCount desc
                ");

            return GetPostDictionary(cmd, 5);
        }
Example #12
0
 public abstract string ToSQL(QueryCommand cmd, Table tbl, string letter, bool isFirst);
Example #13
0
 public abstract string ToSQL(QueryCommand cmd, Table tbl, string letter, bool isFirst);
Example #14
0
 private static ReportData GetPostDictionary(QueryCommand command)
 {
     return GetPostDictionary(command, int.MaxValue);
 }
Example #15
0
        public static int GetPublishedCommentCount(string user)
        {
            int count = 0;

            QueryCommand cmd;

            cmd = new QueryCommand(
            @"select " + DataService.Provider.SqlCountFunction("a.PostId") + @" from graffiti_Comments a
                        inner join graffiti_Posts b on a.PostId = b.Id
                        where b.IsPublished <> 0 and b.IsDeleted = 0
                        and a.IsPublished <> 0 and a.IsDeleted = 0 and b.Status = 1");

            if (!String.IsNullOrEmpty(user))
            {
                cmd.Sql += " and b.CreatedBy = " + DataService.Provider.SqlVariable("CreatedBy");
                cmd.Parameters.Add(Post.FindParameter("CreatedBy")).Value = user;
            }

            try
            {
                count = Convert.ToInt32(DataService.ExecuteScalar(cmd));
            }
            catch (Exception) { }

            return count;
        }
Example #16
0
 public static int ExecuteNonQuery(QueryCommand cmd)
 {
     return(_dp.ExecuteNonQuery(cmd));
 }
Example #17
0
        public static int ViewsByPostSingleCount(int postId, DateTime min, DateTime max)
        {
            DataProvider dp = DataService.Provider;
            QueryCommand cmd = new QueryCommand(@"
                select
                    " + dp.SqlCountFunction("ps.Id") + @" as IdCount
                from
                    graffiti_Post_Statistics AS ps
                where
                    ps.DateViewed >= " + dp.SqlVariable("MinDate") + @" and ps.DateViewed < " + dp.SqlVariable("MaxDate") + @"
                    and ps.PostId = " + dp.SqlVariable("PostId")
                );

            List<Parameter> parameters = PostStatistic.GenerateParameters();
            Parameter pDateViewed = PostStatistic.FindParameter(parameters, "DateViewed");
            cmd.Parameters.Add("MinDate", min, pDateViewed.DbType);
            cmd.Parameters.Add("MaxDate", max.AddDays(1), pDateViewed.DbType);
            cmd.Parameters.Add(PostStatistic.FindParameter(parameters, "PostId")).Value = postId;

            return (int)DataService.ExecuteScalar(cmd);
        }
Example #18
0
        public static List<CategoryCount> GetCategoryCountForStatus(PostStatus status, string authorID)
        {
            List<CategoryCount> catCounts = new List<CategoryCount>();
            List<CategoryCount> final = new List<CategoryCount>();

            DataProvider dp = DataService.Provider;
            QueryCommand cmd = new QueryCommand(String.Empty);

            if (String.IsNullOrEmpty(authorID))
            {
                cmd.Sql = @"select c.Id, " + dp.SqlCountFunction("c.Name") + @" as IdCount, p.CategoryId from graffiti_Posts AS p
                inner join graffiti_Categories AS c on p.CategoryId = c.Id
                where p.Status = " + dp.SqlVariable("Status") + @" and p.IsDeleted = 0
                group by c.Id, p.CategoryId";
            }
            else
            {
                cmd.Sql = @"select c.Id, " + dp.SqlCountFunction("c.Name") + @" as IdCount, p.CategoryId from ((graffiti_Posts AS p
                inner join graffiti_Categories AS c on p.CategoryId = c.Id)
                inner join graffiti_Users AS u on p.CreatedBy = u.Name)
                where p.Status = " + dp.SqlVariable("Status") + @" and p.IsDeleted = 0 and u.Id = " + dp.SqlVariable("AuthorId") +
                @" group by c.Id, p.CategoryId";
            }

            cmd.Parameters.Add(Post.FindParameter("Status")).Value = (int)status;

            if (!String.IsNullOrEmpty(authorID))
            {
                cmd.Parameters.Add("AuthorId", Convert.ToInt32(authorID), Graffiti.Core.User.FindParameter("Id").DbType);
            }

            using (IDataReader reader = DataService.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    CategoryCount catCount = new CategoryCount();
                    catCount.ID = Int32.Parse(reader["Id"].ToString());
                    catCount.Count = Int32.Parse(reader["IdCount"].ToString());
                    catCount.CategoryId = Int32.Parse(reader["CategoryId"].ToString());

                    catCounts.Add(catCount);
                }

                reader.Close();
            }

            // populate the category name
            CategoryCollection cats = new CategoryController().GetAllCachedCategories();

            List<CategoryCount> tempParentList = new List<CategoryCount>();

            foreach (CategoryCount cc in catCounts)
            {
                Category temp = cats.Find(
                                 delegate(Category c)
                                 {
                                     return c.Id == cc.ID;
                                 });

                if (temp != null)
                {
                    cc.Name = temp.Name;
                    cc.ParentId = temp.ParentId;
                }

                if (cc.Count > 0 && cc.ParentId >= 1)
                {
                    // if it's not already in the list, add it
                    CategoryCount parent = catCounts.Find(
                                                delegate(CategoryCount cac)
                                                {
                                                    return cac.ID == cc.ParentId;
                                                });

                    if (parent == null)
                    {
                        parent = tempParentList.Find(
                                                    delegate(CategoryCount cac)
                                                    {
                                                        return cac.ID == cc.ParentId;
                                                    });

                        if (parent == null)
                        {
                            Category tempParent = cats.Find(
                                                    delegate(Category cttemp)
                                                    {
                                                        return cttemp.Id == cc.ParentId;
                                                    });

                            parent = new CategoryCount();
                            parent.ID = tempParent.Id;
                            parent.ParentId = tempParent.ParentId;
                            parent.Name = tempParent.Name;
                            parent.Count = 0;

                            tempParentList.Add(parent);
                        }
                    }
                }
            }

            catCounts.AddRange(tempParentList);

            List<CategoryCount> filteredPermissions = new List<CategoryCount>();
            filteredPermissions.AddRange(catCounts);

            foreach (CategoryCount ac in catCounts)
            {
                if (!RolePermissionManager.GetPermissions(ac.CategoryId, GraffitiUsers.Current).Read)
                    filteredPermissions.Remove(ac);
            }

            foreach (CategoryCount ac in filteredPermissions)
            {
                CategoryCount existing = final.Find(
                                                delegate(CategoryCount catcount)
                                                {
                                                    return catcount.ID == ac.ID;
                                                });

                if (existing == null)
                {
                    final.Add(ac);
                }
                else
                {
                    existing.Count += ac.Count;
                }
            }

            return final;
        }
Example #19
0
 public static IDataReader ExecuteReader(QueryCommand cmd)
 {
     return(_dp.ExecuteReader(cmd));
 }
Example #20
0
        public static void UpdateViewCount(int postid)
        {
            QueryCommand command = new QueryCommand("UPDATE graffiti_Posts Set Views = Views + 1 WHERE Id = " + DataService.Provider.SqlVariable("Id"));
            command.Parameters.Add(Post.FindParameter("Id")).Value = postid;
            DataService.ExecuteNonQuery(command);

            PostStatistic ps = new PostStatistic();
            ps.PostId = postid;
            ps.DateViewed = DateTime.Now;

            ps.Save();
        }
Example #21
0
        public static void UpdatePostStatus(int id, PostStatus status)
        {
            //UpdateVersionCount(id);

            QueryCommand command = new QueryCommand("Update graffiti_Posts Set Status = " + DataService.Provider.SqlVariable("Status") + " Where Id = " + DataService.Provider.SqlVariable("Id"));
            List<Parameter> parameters = Post.GenerateParameters();
            command.Parameters.Add(Post.FindParameter(parameters, "Status")).Value = (int)status;
            command.Parameters.Add(Post.FindParameter(parameters, "Id")).Value = id;

            DataService.ExecuteNonQuery(command);

            ZCache.RemoveByPattern("Posts-");
            ZCache.RemoveCache("Post-" + id);
        }
Example #22
0
        public static void UpdateCommentCount(int postid)
        {
            QueryCommand command = null;
            DataProvider dp = DataService.Provider;

            if (Util.IsAccess)
            {
                Query q1 = Comment.CreateQuery();
                q1.AndWhere(Comment.Columns.PostId, postid);
                q1.AndWhere(Comment.Columns.IsPublished, true);
                q1.AndWhere(Comment.Columns.IsDeleted,false);

                int Comment_Count = q1.GetRecordCount();

                Query q2 = Comment.CreateQuery();
                q2.AndWhere(Comment.Columns.PostId, postid);
                q2.AndWhere(Comment.Columns.IsPublished, false);
                q2.AndWhere(Comment.Columns.IsDeleted, false);

                int Pending_Comment_Count = q2.GetRecordCount();

                command = new QueryCommand("UPDATE graffiti_Posts Set Comment_Count = "
                    + dp.SqlVariable("Comment_Count")
                    + ", Pending_Comment_Count = " + dp.SqlVariable("Pending_Comment_Count")
                    + " WHERE Id = " + dp.SqlVariable("Id"));
                List<Parameter> parameters = Post.GenerateParameters();
                command.Parameters.Add(Post.FindParameter(parameters, "Comment_Count")).Value = Comment_Count;
                command.Parameters.Add(Post.FindParameter(parameters, "Pending_Comment_Count")).Value = Pending_Comment_Count;
                command.Parameters.Add(Post.FindParameter(parameters, "Id")).Value = postid;
            }
            else
            {
                string sql =
                    @"Update graffiti_Posts
                    Set
                        Comment_Count = (Select " + dp.SqlCountFunction() + @" FROM graffiti_Comments AS c where c.PostId = " + dp.SqlVariable("Id") + @" and c.IsPublished = 1 and c.IsDeleted = 0),
                        Pending_Comment_Count = (Select " + dp.SqlCountFunction() + @" FROM graffiti_Comments AS c where c.PostId = " + dp.SqlVariable("Id") + @" and c.IsPublished = 0 and c.IsDeleted = 0)
                   Where Id = " + dp.SqlVariable("Id");

                command = new QueryCommand(sql);
                command.Parameters.Add(Post.FindParameter("Id")).Value = postid;
            }

            DataService.ExecuteNonQuery(command);
        }
        //public List<IGraffitiUser> GetUsers(string role)
        //{
        //    throw new NotImplementedException();
        //}
        public string[] GetUsersInRole(string roleName)
        {
            QueryCommand command = new QueryCommand("SELECT u.Name FROM graffiti_Users AS u INNER JOIN graffiti_UserRoles AS ur on u.Id = ur.UserId WHERE ur.RoleName = " + DataService.Provider.SqlVariable("RoleName"));
            command.Parameters.Add(UserRole.FindParameter("RoleName")).Value = roleName;
            List<string> userNames = new List<string>();
            using(IDataReader reader = DataService.ExecuteReader(command))
            {
                while(reader.Read())
                {
                    userNames.Add(reader["Name"] as string);
                }

                reader.Close();
            }

            return userNames.ToArray();
        }
Example #24
0
 public static object ExecuteScalar(QueryCommand cmd)
 {
     return(_dp.ExecuteScalar(cmd));
 }
Example #25
0
        public static IDictionary<DateTime, int> ViewsByPostSingle(int postId, DateTime min, DateTime max)
        {
            DataProvider dp = DataService.Provider;
            QueryCommand cmd = new QueryCommand(@"
                select " +
                    dp.SqlYearFunction("ps.DateViewed") + " as dvYear, " +
                    dp.SqlMonthFunction("ps.DateViewed") + " as dvMonth, " +
                    dp.SqlDayFunction("ps.DateViewed") + " as dvDay, " +
                    dp.SqlCountFunction("ps.Id") + @" as IdCount
                from
                    graffiti_Post_Statistics AS ps
                where
                    ps.DateViewed >= " + dp.SqlVariable("MinDate") + @" and ps.DateViewed < " + dp.SqlVariable("MaxDate") + @"
                    and ps.PostId = " + dp.SqlVariable("PostId") + @"
                group by " +
                    dp.SqlYearFunction("ps.DateViewed") + ", " +
                    dp.SqlMonthFunction("ps.DateViewed") + ", " +
                    dp.SqlDayFunction("ps.DateViewed")
                );

            List<Parameter> parameters = PostStatistic.GenerateParameters();
            Parameter pDateViewed = PostStatistic.FindParameter(parameters, "DateViewed");
            cmd.Parameters.Add("MinDate", min, pDateViewed.DbType);
            cmd.Parameters.Add("MaxDate", max.AddDays(1), pDateViewed.DbType);
            cmd.Parameters.Add(PostStatistic.FindParameter(parameters, "PostId")).Value = postId;

            return GetDateDictionary(cmd);
        }
Example #26
0
        public static List<AuthorCount> GetAuthorCountForStatus(PostStatus status, string categoryID)
        {
            List<AuthorCount> autCounts = new List<AuthorCount>();
            List<AuthorCount> final = new List<AuthorCount>();

            QueryCommand cmd = new QueryCommand(
                    @"select u.Id, " + DataService.Provider.SqlCountFunction("u.Id") + @" as IdCount, u.ProperName, p.CategoryId from graffiti_Posts AS p
                    inner join graffiti_Users as u on p.CreatedBy = u.Name
                    where p.Status = " + DataService.Provider.SqlVariable("Status") + @" and p.IsDeleted = 0");

            if(!String.IsNullOrEmpty(categoryID))
            {
                cmd.Sql += " and p.CategoryId = " + DataService.Provider.SqlVariable("CategoryId");
            }

            cmd.Sql += " group by u.Id, u.ProperName, p.CategoryId";

            List<Parameter> parameters = Post.GenerateParameters();
            cmd.Parameters.Add(Post.FindParameter(parameters, "Status")).Value = (int)status;

            if (!String.IsNullOrEmpty(categoryID))
            {
                cmd.Parameters.Add(Post.FindParameter(parameters, "CategoryId")).Value = Convert.ToInt32(categoryID);
            }

            using (IDataReader reader = DataService.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    AuthorCount autCount = new AuthorCount();
                    autCount.ID = Int32.Parse(reader["Id"].ToString());
                    autCount.Count = Int32.Parse(reader["IdCount"].ToString());
                    autCount.Name = reader["ProperName"].ToString();
                    autCount.CategoryId = Int32.Parse(reader["CategoryId"].ToString());

                    autCounts.Add(autCount);
                }

                List<AuthorCount> filteredPermissions = new List<AuthorCount>();
                filteredPermissions.AddRange(autCounts);

                foreach (AuthorCount ac in autCounts)
                {
                    if (!RolePermissionManager.GetPermissions(ac.CategoryId, GraffitiUsers.Current).Read)
                        filteredPermissions.Remove(ac);
                }

                foreach (AuthorCount ac in filteredPermissions)
                {
                    AuthorCount existing = final.Find(
                                                    delegate(AuthorCount authcount)
                                                    {
                                                        return authcount.Name == ac.Name;
                                                    });

                    if (existing == null)
                    {
                        final.Add(ac);
                    }
                    else
                    {
                        existing.Count += ac.Count;
                    }
                }

                reader.Close();
            }

            return final;
        }
Example #27
0
        private static IDictionary<DateTime, int> GetDateDictionary(QueryCommand command)
        {
            Dictionary<DateTime, int> dates = new Dictionary<DateTime, int>();
            using (IDataReader reader = DataService.ExecuteReader(command))
            {
                while (reader.Read())
                    dates.Add(
                        new DateTime(Int32.Parse(reader["dvYear"].ToString()), Int32.Parse(reader["dvMonth"].ToString()),
                                     Int32.Parse(reader["dvDay"].ToString())),
                        Int32.Parse( reader["IdCount"].ToString()) );

                reader.Close();
            }
            return dates;
        }
Example #28
0
 public static PostCollection FetchPostsByTagAndCategory(string tagName, int categoryId)
 {
     QueryCommand command = new QueryCommand("SELECT p.* FROM graffiti_Posts AS p INNER JOIN graffiti_Tags AS t ON p.Id = t.PostId WHERE p.CategoryId = " + categoryId.ToString() + " and p.IsPublished <> 0 and p.IsDeleted = 0 and p.Published <= " + DataService.Provider.SqlVariable("Published") + " and t.Name = " + DataService.Provider.SqlVariable("Name") + " ORDER BY p.Published DESC");
     command.Parameters.Add(Post.FindParameter("Published")).Value = SiteSettings.CurrentUserTime;
     command.Parameters.Add(Tag.FindParameter("Name")).Value = tagName;
     PostCollection pc = new PostCollection();
     pc.LoadAndCloseReader(DataService.ExecuteReader(command));
     return pc;
 }
Example #29
0
        private static ReportData GetPostDictionary(QueryCommand command, int top)
        {
            ReportData data = new ReportData();
            if ( top < 1 ) top = int.MaxValue;
            int counter = 0;

            using (IDataReader reader = DataService.ExecuteReader(command))
            {
                while (reader.Read())
                {
                    data.Counts.Add(Int32.Parse(reader["Id"].ToString()), Int32.Parse(reader["IdCount"].ToString()));
                    data.Titles.Add(Int32.Parse(reader["Id"].ToString()), reader["Title"] as string);
                    /* if for some reason the database return more than Int32.MaxValue this would still exit.  But, the IDictionaries wouldn't  */
                    counter++;
                    if( counter == top ) break;
                }

                reader.Close();
            }
            return data;
        }
Example #30
0
        public static IDictionary<DateTime, int> CommentsByPostSingle(int postId, DateTime min, DateTime max)
        {
            DataProvider dp = DataService.Provider;
            QueryCommand cmd = new QueryCommand(@"
                select " +
                    dp.SqlYearFunction("c.Published") + " as dvYear, " +
                    dp.SqlMonthFunction("c.Published") + " as dvMonth, " +
                    dp.SqlDayFunction("c.Published") + " as dvDay, " +
                    dp.SqlCountFunction("c.Id") + @" as IdCount
                from
                    graffiti_Comments AS c
                where
                    c.Published >= " + dp.SqlVariable("MinDate") + @" and c.Published < " + dp.SqlVariable("MaxDate") + @"
                    and c.PostId = " + dp.SqlVariable("PostId") + @"
                    and c.IsDeleted = 0
                group by " +
                    dp.SqlYearFunction("c.Published") + ", " +
                    dp.SqlMonthFunction("c.Published") + ", " +
                    dp.SqlDayFunction("c.Published")
                );

            List<Parameter> parameters = Comment.GenerateParameters();
            Parameter pPublished = Comment.FindParameter(parameters, "Published");
            cmd.Parameters.Add("MinDate", min, pPublished.DbType);
            cmd.Parameters.Add("MaxDate", max.AddDays(1), pPublished.DbType);
            cmd.Parameters.Add(Comment.FindParameter(parameters, "PostId")).Value = postId;

            return GetDateDictionary(cmd);
        }
Example #31
0
        private static void DeleteByColumn(Column column,  bool state)
        {
            List<string> idsToDelete = new List<string>();
            List<int> postIdsChanged = new List<int>();

            Query q = CreateQuery();
            q.AndWhere(column,state);
            q.AndWhere(Columns.Published, DateTime.Now.AddDays(-1 * Int32.Parse(ConfigurationManager.AppSettings["Graffiti::Comments::DaysToDelete"] ?? "7")),Comparison.LessOrEquals);
            q.Top = "25";
            q.OrderByAsc(Columns.Published);
            CommentCollection cc = CommentCollection.FetchByQuery(q);
            foreach(Comment c in cc)
            {
                idsToDelete.Add(c.Id.ToString());
                if (!postIdsChanged.Contains(c.PostId))
                    postIdsChanged.Add(c.PostId);
            }

            if(idsToDelete.Count > 0)
            {
                QueryCommand deleteCommand =
                    new QueryCommand("DELETE FROM graffiti_Comments where Id in (" + string.Join(",", idsToDelete.ToArray()) + ")");

                DataService.ExecuteNonQuery(deleteCommand);

                foreach(int pid in postIdsChanged)
                    Core.Post.UpdateCommentCount(pid);

                Log.Info("Deleted Comments", idsToDelete.Count + " comment(s) were removed from the database since they were older than " + (ConfigurationManager.AppSettings["Graffiti::Comments::DaysToDelete"] ?? "7") + " days and marked as " + ((column.Name == "IsDeleted") ? " deleted" : " unpublished") );
            }
        }
Example #32
0
        public static int CommentsByPostSingleCount(int postId, DateTime min, DateTime max)
        {
            DataProvider dp = DataService.Provider;
            QueryCommand cmd = new QueryCommand(@"
                select
                    " + dp.SqlCountFunction("c.Id") + @" as IdCount
                from
                    graffiti_Comments c
                inner join
                    graffiti_Posts p on p.Id = c.PostId
                where
                    c.Published >= " + dp.SqlVariable("MinDate") + @" and c.Published < " + dp.SqlVariable("MaxDate") + @" and c.PostId = " + dp.SqlVariable("PostId") + @"
                    and c.IsDeleted = 0"
                );

            List<Parameter> parameters = Comment.GenerateParameters();
            Parameter pPublished = Comment.FindParameter(parameters, "Published");
            cmd.Parameters.Add("MinDate", min, pPublished.DbType);
            cmd.Parameters.Add("MaxDate", max.AddDays(1), pPublished.DbType);
            cmd.Parameters.Add(Comment.FindParameter(parameters, "PostId")).Value = postId;

            return (int)DataService.ExecuteScalar(cmd);
        }
Example #33
0
        /// <summary>
        /// Returns the number of records matching the current query.
        /// </summary>
        /// <returns></returns>
        public int GetRecordCount()
        {
            QueryCommand command = new QueryCommand("");
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ")
                .Append( DataService.Provider.SqlCountFunction() )
                .Append(" as RecordCount FROM ")
                .Append( DataService.Provider.QuoteName( _Table.TableName ) );

            if (_wheres.Count > 0)
            {
                sb.Append(" WHERE ");

                int position = 1;
                foreach (WHERE where in _wheres)
                {
                    sb.Append(where.ToSQL(command, _Table, GetNextLetter(position), position == 1));
                    position++;
                }
            }

            command.Sql = sb.ToString();
            object obj = DataService.ExecuteScalar(command);
            int count = 0;
            if (obj == null || obj is DBNull)
                return count;

            if ( obj is Int32 )
                count = (int) obj;
            else if ( obj is Int64 )
                // this may cause errors in the future...if we begin to support Int64 keys
                count = (long) obj > Convert.ToInt64(Int32.MaxValue) ? Int32.MaxValue : Convert.ToInt32( (long) obj );
            else if ( obj is Int16 )
                count = Convert.ToInt32( (short) obj );

            return count;
        }
Example #34
0
        public static ReportData GetViewsByPost(DateTime min, DateTime max)
        {
            // top 10
            DataProvider dp = DataService.Provider;
            QueryCommand cmd = new QueryCommand(@"
                select Title, Id, IdCount FROM ( SELECT
                    max(p.Title) as Title, p.Id, " + dp.SqlCountFunction("p.Id") + @" as IdCount
                from
                    graffiti_Post_Statistics AS ps
                left outer join
                    graffiti_Posts AS p on p.Id = ps.PostId
                where
                    ps.DateViewed >= " + dp.SqlVariable("MinDate") + @" and ps.DateViewed < " + dp.SqlVariable("MaxDate") + @"
                    and p.CategoryId in " + RolePermissionManager.GetInClauseForReadPermissions(GraffitiUsers.Current) + @"
                group by
                    p.Id) as dv
                order by
                    IdCount desc
                ");

            Parameter pDateViewed = PostStatistic.FindParameter("DateViewed");
            cmd.Parameters.Add("MinDate", min, pDateViewed.DbType);
            cmd.Parameters.Add("MaxDate", max.AddDays(1), pDateViewed.DbType);

            return GetPostDictionary(cmd, 10);
        }
Example #35
0
 protected virtual DbCommand GetCommand(QueryCommand qryCommand, DbConnection conn)
 {
     return(GetCommand(DbTrace.GetMarker(), qryCommand, conn));
 }
Example #36
0
        public static List<PostCount> GetPostCounts(int catID, string user)
        {
            List<PostCount> postCounts = new List<PostCount>();
            List<PostCount> final = new List<PostCount>();

            List<Parameter> parameters = Post.GenerateParameters();
            QueryCommand cmd = new QueryCommand("Select Status, CategoryId, " + DataService.Provider.SqlCountFunction("Id") + " as StatusCount FROM graffiti_Posts Where IsDeleted = 0");

            if(catID > 0)
            {
                cmd.Sql += " and CategoryId = " + DataService.Provider.SqlVariable("CategoryId");
                cmd.Parameters.Add(Post.FindParameter(parameters, "CategoryId")).Value = catID;
            }

            if(!String.IsNullOrEmpty(user))
            {
                cmd.Sql += " and CreatedBy = " + DataService.Provider.SqlVariable("CreatedBy");
                cmd.Parameters.Add(Post.FindParameter(parameters, "CreatedBy")).Value = user;
            }

            cmd.Sql += " group by Status, CategoryId";

            using (IDataReader reader = DataService.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    PostCount postCount = new PostCount();
                    postCount.PostStatus = (PostStatus)Int32.Parse(reader["Status"].ToString());
                    postCount.Count = Int32.Parse(reader["StatusCount"].ToString());
                    postCount.CategoryId = Int32.Parse(reader["CategoryId"].ToString());

                    postCounts.Add(postCount);
                }

                reader.Close();
            }

            List<PostCount> filteredPermissions = new List<PostCount>();
            filteredPermissions.AddRange(postCounts);

            foreach (PostCount ac in postCounts)
            {
                if (!RolePermissionManager.GetPermissions(ac.CategoryId, GraffitiUsers.Current).Read)
                    filteredPermissions.Remove(ac);
            }

            foreach (PostCount ac in filteredPermissions)
            {
                PostCount existing = final.Find(
                                                delegate(PostCount postcount)
                                                {
                                                    return postcount.PostStatus == ac.PostStatus;
                                                });

                if (existing == null)
                {
                    final.Add(ac);
                }
                else
                {
                    existing.Count += ac.Count;
                }
            }

            return final;
        }
Example #37
0
        /// <summary>
        ///     Returns an IDataReader based on the query results.
        /// </summary>
        /// <returns></returns>
        public IDataReader ExecuteReader()
        {
            QueryCommand command = DataService.CreateQueryCommandFromQuery(this);

            return(DataService.ExecuteReader(command));
        }
Example #38
0
        public virtual QueryCommand CreateQueryCommandFromQuery(Query q)
        {
            StringBuilder columnList = new StringBuilder();
            QueryCommand  command    = new QueryCommand("");
            bool          isFirst    = true;

            foreach (Column column in q.Table.Columns)
            {
                columnList.AppendFormat("{1} {0}", QuoteName(column.Name), isFirst ? string.Empty : ",");
                isFirst = false;
            }

            string whereStatement = null;

            if (q.Wheres.Count > 0)
            {
                StringBuilder _whereStatement = new StringBuilder();
                int           position        = 1;
                foreach (WHERE where in q.Wheres)
                {
                    _whereStatement.Append(where.ToSQL(command, q.Table, GetNextLetter(position), position == 1));
                    position++;
                }
                whereStatement = _whereStatement.ToString();
            }

            string orderStatement = null;

            if (q.Orders.Count > 0)
            {
                isFirst = true;
                foreach (string order in q.Orders)
                {
                    if (isFirst)
                    {
                        orderStatement = " Order By ";
                    }

                    orderStatement += (isFirst ? " " : " , ") + order;
                    isFirst         = false;
                }
            }

            if (q.PageIndex == 0 || q.PageSize == 0)
            {
                command.Sql = SelectSql(columnList.ToString(), q.Table.TableName, whereStatement, orderStatement, q.Top);
            }
            else if (q.PageIndex == 1)
            {
                command.Sql = SelectSql(columnList.ToString(), q.Table.TableName, whereStatement, orderStatement,
                                        q.PageSize.ToString());
            }
            else
            {
                command.Sql =
                    SelectPagedSql(columnList.ToString(), q.Table.TableName, whereStatement, orderStatement,
                                   q.Table.PrimaryKey, q.PageIndex, q.PageSize);
            }

            return(command);
        }