ads.cs

最好用的站点内容管理系统 全部源代码都有

//======================================================
//==     (c)2008 aspxcms inc by NeTCMS v1.0              ==
//==          Forum:bbs.aspxcms.com                   ==
//==         Website:www.aspxcms.com                  ==
//======================================================
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using NetCMS.Model;
using NetCMS.DALFactory;
using NetCMS.DALProfile;
using NetCMS.Config;

namespace NetCMS.DALSQLServer
{
    public class Ads : DbBase, IAds
    {
        private string SiteID;
        public Ads()
        {
            SiteID = NetCMS.Global.Current.SiteID;
        }

        /// <summary>
        /// 获得广告列表/分类列表
        /// </summary>
        /// <param name="ali"></param>
        /// <returns></returns>
        public DataTable list(NetCMS.Model.AdsListInfo ali)
        {
            string tempselect = "";
            string str_Sql = "";
            switch (ali.type)
            {
                case "Ads":
                    switch (ali.showAdsType)
                    {
                        case "showadstype":
                            if (ali.adsType != null && ali.adsType != "" && ali.adsType != string.Empty && ali.adsType != "-1")
                                tempselect = "And adType=" + ali.adsType + "";
                            else
                                tempselect = "";

                            if (ali.showSiteID != null && ali.showSiteID != "" && ali.showSiteID != string.Empty)
                                tempselect += " And SiteID='" + ali.showSiteID + "'";
                            else
                                tempselect += " And SiteID='" + SiteID + "'";

                            str_Sql = "Select AdID,adName,adType,creatTime,CusID,isLock From " + Pre + "ads Where" +
                                      " 1=1 " + tempselect + " Order By Id Desc";
                            break;
                        case "search":
                            if (SiteID == "0")
                                tempselect = "";
                            else
                                tempselect = " And SiteID='" + SiteID + "'";

                            switch (ali.searchType)
                            {
                                case "adsname":
                                    str_Sql = "Select AdID,adName,adType,creatTime,CusID,isLock From " + Pre + "ads" +
                                              " Where adName Like '%" + ali.SearchKey + "%' " + tempselect + " Order By Id Desc";
                                    break;
                                case "user":
                                    str_Sql = "Select AdID,adName,adType,creatTime,CusID,isLock From " + Pre + "ads" +
                                              " Where CusID In(Select UserNum From " + Pre + "sys_User Where UserName" +
                                              "  Like '%" + ali.SearchKey + "%') " + tempselect + " Order By Id Desc";
                                    break;
                                default:
                                    str_Sql = "Select AdID,adName,adType,creatTime,CusID,isLock From " + Pre + "ads Where" +
                                              " 1=1 " + tempselect + " Order By Id Desc";
                                    break;
                            }
                            break;
                        case "site":
                            if (ali.adsType != null && ali.adsType != "" && ali.adsType != string.Empty && ali.adsType != "-1")
                                tempselect = "And adType=" + ali.adsType + "";
                            else
                                tempselect = "";

                            if (ali.showSiteID != null && ali.showSiteID != "" && ali.showSiteID != string.Empty)
                                tempselect += " And SiteID='" + ali.showSiteID + "'";
                            else
                                tempselect += " And SiteID='" + SiteID + "'";

                            str_Sql = "Select AdID,adName,adType,creatTime,CusID,isLock From " + Pre + "ads Where" +
                                      " 1=1 " + tempselect + " Order By Id Desc";
                            break;
                        default:
                            str_Sql = "Select AdID,adName,adType,creatTime,CusID,isLock From " + Pre + "ads Where" +
                                      " SiteID='" + SiteID + "' Order By Id Desc";
                            break;
                    }
                    break;
                case "Class":
                    if (ali.showSiteID != null && ali.showSiteID != "" && ali.showSiteID != string.Empty)
                        tempselect = " And SiteID='" + ali.showSiteID + "'";
                    else
                        tempselect = " And SiteID='" + SiteID + "'";

                    str_Sql = "Select AcID,Cname,Adprice,creatTime From " + Pre + "ads_class Where ParentID='0' " +
                              "" + tempselect + " Order By Id Desc";
                    break;
                case "Stat":
                    if (ali.showSiteID != null && ali.showSiteID != "" && ali.showSiteID != string.Empty)
                        tempselect = " And SiteID='" + ali.showSiteID + "'";
                    else
                        tempselect = " And SiteID='" + SiteID + "'";

                    str_Sql = "Select AdID,adName,clickNum,showNum From " + Pre + "ads Where 1=1 " + tempselect + " Order By Id Desc";
                    break;
            }
            return DbHelper.ExecuteTable(CommandType.Text, str_Sql, null);
        }

        /// <summary>
        /// 得到广告的子分类
        /// </summary>
        /// <param name="classid">父分类ID</param>
        /// <returns></returns>
        public DataTable childlist(string classid)
        {
            string str_Sql = "Select AcID,Cname,Adprice,creatTime From " + Pre + "ads_class Where" +
                             " SiteID='" + SiteID + "' And ParentID='" + classid + "'";
            return DbHelper.ExecuteTable(CommandType.Text, str_Sql, null);
        }

        /// <summary>
        /// 锁定广告
        /// </summary>
        /// <param name="id"></param>
        public void Lock(string id)
        {
            string str_Sql = "Update " + Pre + "ads Set isLock=1 Where AdID='" + id + "' And SiteID='" + SiteID + "'";
            DbHelper.ExecuteNonQuery(CommandType.Text, str_Sql, null);
        }

        /// <summary>
        /// 解锁广告
        /// </summary>
        /// <param name="id"></param>
        public void UnLock(string id)
        {
            string str_Sql = "Update " + Pre + "ads Set isLock=0 Where AdID='" + id + "' And SiteID='" + SiteID + "'";
            DbHelper.ExecuteNonQuery(CommandType.Text, str_Sql, null);
        }
        /// <summary>
        /// 删除所有的广告
        /// </summary>
        public void DelAllAds()
        {
            string str_Sql = "Delete From " + Pre + "adstxt Where AdID In (Select AdID From " + Pre + "ads Where SiteID='" + SiteID + "')";
            DbHelper.ExecuteNonQuery(CommandType.Text, str_Sql, null);
            string str_Sql1 = "Delete From " + Pre + "ads_stat Where AdID In (Select AdID From " + Pre + "ads Where SiteID='" + SiteID + "')";
            DbHelper.ExecuteNonQuery(CommandType.Text, str_Sql1, null);
            string str_Sql2 = "Delete From " + Pre + "ads Where SiteID='" + SiteID + "'";
            DbHelper.ExecuteNonQuery(CommandType.Text, str_Sql2, null);
        }
        /// <summary>
        /// 得到广告
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable AdsDt(string id)
        {
            string str_Sql = "";
            if (id != null && id != "" && id != string.Empty)
                str_Sql = "Select AdID,ClassID From " + Pre + "ads Where SiteID='" + SiteID + "' And AdID In (" + id + ")";
            else
                str_Sql = "Select AdID,ClassID From " + Pre + "ads Where SiteID='" + SiteID + "'";

            return DbHelper.ExecuteTable(CommandType.Text, str_Sql, null);
        }
        /// <summary>
        /// 批量删除广告
        /// </summary>
        /// <param name="id"></param>
        public void DelPAds(string id)
        {
            SqlConnection Conn = new SqlConnection(DBConfig.CmsConString);
            Conn.Open();
            SqlTransaction tran = Conn.BeginTransaction();
            try
            {
                string str_Sql = "Delete From " + Pre + "adstxt Where AdID In (" + id + ")";
                DbHelper.ExecuteNonQuery(tran, CommandType.Text, str_Sql, null);
                string str_Sql1 = "Delete From " + Pre + "ads_stat Where AdID In (" + id + ")";
                DbHelper.ExecuteNonQuery(tran, CommandType.Text, str_Sql1, null);
                string str_Sql2 = "Delete From " + Pre + "ads Where SiteID='" + SiteID + "' And AdID In(" + id + ")";
                DbHelper.ExecuteNonQuery(tran, CommandType.Text, str_Sql2, null);
                tran.Commit();
                Conn.Close();
            }
            catch (SqlException e)
            {
                tran.Rollback();
                Conn.Close();
                throw e;
            }
        }

        /// <summary>
        /// 删除所有广告分类
        /// </summary>
        public void DelAllAdsClass()
        {
            string str_Sql = "Delete From " + Pre + "adstxt Where AdID In (Select AdID From " + Pre + "ads Where ClassID In(Select " +
                             "AcID From " + Pre + "ads_class Where SiteID='" + SiteID + "'))";
            DbHelper.ExecuteNonQuery(CommandType.Text, str_Sql, null);
            string str_Sql1 = "Delete From " + Pre + "ads_stat Where AdID In (Select AdID From " + Pre + "ads Where ClassID In(Select " +
                              "AcID From " + Pre + "ads_class Where SiteID='" + SiteID + "'))";
            DbHelper.ExecuteNonQuery(CommandType.Text, str_Sql1, null);
            string str_Sql2 = "Delete From " + Pre + "ads Where And ClassID In(Select " +
                              "AcID From " + Pre + "ads_class Where SiteID='" + SiteID + "')";
            DbHelper.ExecuteNonQuery(CommandType.Text, str_Sql2, null);

            string str_Sql3 = "Delete From " + Pre + "ads_class Where SiteID='" + SiteID + "'";
            DbHelper.ExecuteNonQuery(CommandType.Text, str_Sql3, null);