Beispiel #1
0
        public static List <PostSQL> GetAllSql(int categoryID, string textsearch)
        {
            var           list = new List <PostSQL>();
            StringBuilder sql  = new StringBuilder();

            sql.AppendLine("BEGIN");

            if (categoryID > 0)
            {
                sql.AppendLine(String.Empty);
                sql.AppendLine("WITH category AS(");
                sql.AppendLine("    SELECT");
                sql.AppendLine("            ID");
                sql.AppendLine("    ,       Title");
                sql.AppendLine("    ,       ParentID");
                sql.AppendLine("    FROM");
                sql.AppendLine("            tbl_PostCategory");
                sql.AppendLine("    WHERE");
                sql.AppendLine("            1 = 1");
                sql.AppendLine("    AND     ID = " + categoryID);
                sql.AppendLine("");
                sql.AppendLine("    UNION ALL");
                sql.AppendLine("");
                sql.AppendLine("    SELECT");
                sql.AppendLine("            CHI.ID");
                sql.AppendLine("    ,       CHI.Title");
                sql.AppendLine("    ,       CHI.ParentID");
                sql.AppendLine("    FROM");
                sql.AppendLine("            category AS PAR");
                sql.AppendLine("    INNER JOIN tbl_PostCategory AS CHI");
                sql.AppendLine("        ON PAR.ID = CHI.ParentID");
                sql.AppendLine(")");
                sql.AppendLine("SELECT");
                sql.AppendLine("        ID");
                sql.AppendLine(",       Title");
                sql.AppendLine(",       ParentID");
                sql.AppendLine("INTO #category");
                sql.AppendLine("FROM category");
            }

            sql.AppendLine(String.Empty);
            sql.AppendLine("    SELECT");
            sql.AppendLine("            POS.*");
            sql.AppendLine("    INTO #Post");
            sql.AppendLine("    FROM");
            sql.AppendLine("            tbl_Post AS POS");
            sql.AppendLine("    WHERE");
            sql.AppendLine("            1 = 1");

            if (!string.IsNullOrEmpty(textsearch))
            {
                sql.AppendLine("    AND (POS.Title like N'%" + textsearch + "%')");
            }

            if (categoryID > 0)
            {
                sql.AppendLine("    AND EXISTS(");
                sql.AppendLine("            SELECT");
                sql.AppendLine("                    NULL AS DUMMY");
                sql.AppendLine("            FROM");
                sql.AppendLine("                    #category");
                sql.AppendLine("            WHERE");
                sql.AppendLine("                    ID = POS.CategoryID");
                sql.AppendLine("    )");
            }

            sql.AppendLine("     ORDER BY");
            sql.AppendLine("            POS.ID");
            sql.AppendLine("     ;");
            sql.AppendLine(String.Empty);
            sql.AppendLine("     SELECT");
            sql.AppendLine("             c.Title AS CategoryName");
            sql.AppendLine("     ,       p.*");
            sql.AppendLine("     FROM");
            sql.AppendLine("             #Post AS p");
            sql.AppendLine("     LEFT JOIN (");
            sql.AppendLine("             SELECT");
            sql.AppendLine("                     ID");
            sql.AppendLine("             ,       Title");
            sql.AppendLine("             FROM");
            sql.AppendLine("                     dbo.tbl_PostCategory");
            sql.AppendLine("     ) AS c");
            sql.AppendLine("     ON c.ID = p.CategoryID");
            sql.AppendLine("     ORDER BY");
            sql.AppendLine("             p.ID");
            sql.AppendLine("     ;");
            sql.AppendLine(String.Empty);
            sql.AppendLine(" END");

            var reader = (IDataReader)SqlHelper.ExecuteDataReader(sql.ToString());

            while (reader.Read())
            {
                var entity = new PostSQL();

                entity.ID = Convert.ToInt32(reader["ID"]);

                if (!string.IsNullOrEmpty(reader["Image"].ToString()))
                {
                    entity.Image = reader["Image"].ToString();
                }
                else
                {
                    entity.Image = "/App_Themes/Ann/image/placeholder.png";
                }

                if (reader["Title"] != DBNull.Value)
                {
                    entity.Title = reader["Title"].ToString();
                }


                if (reader["Content"] != DBNull.Value)
                {
                    entity.Content = reader["Content"].ToString();
                }
                if (reader["Featured"] != DBNull.Value)
                {
                    entity.Featured = reader["Featured"].ToString().ToInt(0);
                }
                if (reader["CategoryName"] != DBNull.Value)
                {
                    entity.CategoryName = reader["CategoryName"].ToString();
                }
                if (reader["CategoryID"] != DBNull.Value)
                {
                    entity.CategoryID = reader["CategoryID"].ToString().ToInt(0);
                }
                if (reader["CreatedDate"] != DBNull.Value)
                {
                    entity.CreatedDate = Convert.ToDateTime(reader["CreatedDate"]);
                }
                if (reader["Status"] != DBNull.Value)
                {
                    entity.Status = reader["Status"].ToString().ToInt(0);
                }
                list.Add(entity);
            }
            reader.Close();

            var list_featured = list.Where(x => x.Featured == 1).OrderByDescending(x => x.CreatedDate).ToList();
            var list_normal   = list.Where(x => x.Featured == 0).OrderByDescending(x => x.CreatedDate).ToList();

            return(list_featured.Concat(list_normal).ToList());
        }
Beispiel #2
0
        public static List <PostSQL> GetAllSql(int categoryID, string textsearch, string PostStatus, string WebPublish, string CreatedDate, string CreatedBy)
        {
            var           list = new List <PostSQL>();
            StringBuilder sql  = new StringBuilder();

            sql.AppendLine("BEGIN");

            if (categoryID > 0)
            {
                sql.AppendLine(String.Empty);
                sql.AppendLine("WITH category AS(");
                sql.AppendLine("    SELECT");
                sql.AppendLine("            ID");
                sql.AppendLine("    ,       Title");
                sql.AppendLine("    ,       ParentID");
                sql.AppendLine("    FROM");
                sql.AppendLine("            tbl_PostCategory");
                sql.AppendLine("    WHERE");
                sql.AppendLine("            1 = 1");
                sql.AppendLine("    AND     ID = " + categoryID);
                sql.AppendLine("");
                sql.AppendLine("    UNION ALL");
                sql.AppendLine("");
                sql.AppendLine("    SELECT");
                sql.AppendLine("            CHI.ID");
                sql.AppendLine("    ,       CHI.Title");
                sql.AppendLine("    ,       CHI.ParentID");
                sql.AppendLine("    FROM");
                sql.AppendLine("            category AS PAR");
                sql.AppendLine("    INNER JOIN tbl_PostCategory AS CHI");
                sql.AppendLine("        ON PAR.ID = CHI.ParentID");
                sql.AppendLine(")");
                sql.AppendLine("SELECT");
                sql.AppendLine("        ID");
                sql.AppendLine(",       Title");
                sql.AppendLine(",       ParentID");
                sql.AppendLine("INTO #category");
                sql.AppendLine("FROM category");
            }

            sql.AppendLine(String.Empty);
            sql.AppendLine("    SELECT");
            sql.AppendLine("            POS.*");
            sql.AppendLine("    INTO #Post");
            sql.AppendLine("    FROM");
            sql.AppendLine("            tbl_Post AS POS");
            sql.AppendLine("    WHERE");
            sql.AppendLine("            1 = 1");

            if (!string.IsNullOrEmpty(textsearch))
            {
                sql.AppendLine("    AND (POS.Title like N'%" + textsearch + "%')");
            }

            if (!string.IsNullOrEmpty(PostStatus))
            {
                sql.AppendLine("    AND (POS.Status = " + PostStatus.ToInt() + ")");
            }

            if (!string.IsNullOrEmpty(WebPublish))
            {
                sql.AppendLine("    AND (POS.WebPublish = '" + WebPublish + "')");
            }

            if (!string.IsNullOrEmpty(CreatedBy))
            {
                sql.AppendLine("    AND (POS.CreatedBy = '" + CreatedBy + "')");
            }

            if (!string.IsNullOrEmpty(CreatedDate))
            {
                DateTime fromdate = DateTime.Today;
                DateTime todate   = DateTime.Now;
                switch (CreatedDate)
                {
                case "today":
                    fromdate = DateTime.Today;
                    todate   = DateTime.Now;
                    break;

                case "yesterday":
                    fromdate = fromdate.AddDays(-1);
                    todate   = DateTime.Today;
                    break;

                case "beforeyesterday":
                    fromdate = DateTime.Today.AddDays(-2);
                    todate   = DateTime.Today.AddDays(-1);
                    break;

                case "week":
                    int days = DateTime.Today.DayOfWeek == DayOfWeek.Sunday ? 7 : (int)DateTime.Today.DayOfWeek;
                    fromdate = fromdate.AddDays(-days + 1);
                    todate   = DateTime.Now;
                    break;

                case "thismonth":
                    fromdate = new DateTime(fromdate.Year, fromdate.Month, 1);
                    todate   = DateTime.Now;
                    break;

                case "lastmonth":
                    var thismonth = new DateTime(fromdate.Year, fromdate.Month, 1);
                    fromdate = thismonth.AddMonths(-1);
                    todate   = thismonth;
                    break;

                case "7days":
                    fromdate = DateTime.Today.AddDays(-6);
                    todate   = DateTime.Now;
                    break;

                case "30days":
                    fromdate = DateTime.Today.AddDays(-29);
                    todate   = DateTime.Now;
                    break;
                }
                sql.AppendLine(String.Format("	AND	(CONVERT(NVARCHAR(10), POS.CreatedDate, 121) BETWEEN CONVERT(NVARCHAR(10), '{0:yyyy-MM-dd}', 121) AND CONVERT(NVARCHAR(10), '{1:yyyy-MM-dd}', 121))", fromdate, todate));
            }

            if (categoryID > 0)
            {
                sql.AppendLine("    AND EXISTS(");
                sql.AppendLine("            SELECT");
                sql.AppendLine("                    NULL AS DUMMY");
                sql.AppendLine("            FROM");
                sql.AppendLine("                    #category");
                sql.AppendLine("            WHERE");
                sql.AppendLine("                    ID = POS.CategoryID");
                sql.AppendLine("    )");
            }

            sql.AppendLine("     ORDER BY");
            sql.AppendLine("            POS.ID");
            sql.AppendLine("     ;");
            sql.AppendLine(String.Empty);
            sql.AppendLine("     SELECT");
            sql.AppendLine("             c.Title AS CategoryName");
            sql.AppendLine("     ,       p.*");
            sql.AppendLine("     FROM");
            sql.AppendLine("             #Post AS p");
            sql.AppendLine("     LEFT JOIN (");
            sql.AppendLine("             SELECT");
            sql.AppendLine("                     ID");
            sql.AppendLine("             ,       Title");
            sql.AppendLine("             FROM");
            sql.AppendLine("                     dbo.tbl_PostCategory");
            sql.AppendLine("     ) AS c");
            sql.AppendLine("     ON c.ID = p.CategoryID");
            sql.AppendLine("     ORDER BY");
            sql.AppendLine("             p.ID");
            sql.AppendLine("     ;");
            sql.AppendLine(String.Empty);
            sql.AppendLine(" END");

            var reader = (IDataReader)SqlHelper.ExecuteDataReader(sql.ToString());

            while (reader.Read())
            {
                var entity = new PostSQL();

                entity.ID = Convert.ToInt32(reader["ID"]);

                if (!string.IsNullOrEmpty(reader["Image"].ToString()))
                {
                    entity.Image = reader["Image"].ToString();
                }
                else
                {
                    entity.Image = "/App_Themes/Ann/image/placeholder.png";
                }

                if (reader["Title"] != DBNull.Value)
                {
                    entity.Title = reader["Title"].ToString();
                }


                if (reader["Content"] != DBNull.Value)
                {
                    entity.Content = reader["Content"].ToString();
                }
                if (reader["Featured"] != DBNull.Value)
                {
                    entity.Featured = reader["Featured"].ToString().ToInt(0);
                }
                if (reader["CategoryName"] != DBNull.Value)
                {
                    entity.CategoryName = reader["CategoryName"].ToString();
                }
                if (reader["CategoryID"] != DBNull.Value)
                {
                    entity.CategoryID = reader["CategoryID"].ToString().ToInt(0);
                }
                if (reader["CreatedDate"] != DBNull.Value)
                {
                    entity.CreatedDate = Convert.ToDateTime(reader["CreatedDate"]);
                }
                if (reader["Status"] != DBNull.Value)
                {
                    entity.Status = reader["Status"].ToString().ToInt(0);
                }
                if (reader["WebPublish"] != DBNull.Value)
                {
                    entity.WebPublish = reader["WebPublish"].ToString().ToBool();
                }
                if (reader["WebUpdate"] != DBNull.Value)
                {
                    entity.WebUpdate = Convert.ToDateTime(reader["WebUpdate"]);
                }
                if (reader["CreatedBy"] != DBNull.Value)
                {
                    entity.CreatedBy = reader["CreatedBy"].ToString();
                }
                list.Add(entity);
            }
            reader.Close();

            var list_featured = list.Where(x => x.Featured == 1).OrderByDescending(x => x.CreatedDate).ToList();
            var list_normal   = list.Where(x => x.Featured == 0).OrderByDescending(x => x.CreatedDate).ToList();

            return(list_featured.Concat(list_normal).ToList());
        }