55128.cn_kjh.55128.cn_m.55128.cn/CP55128/CB.Data.SqlServer/TrendChart/TrendChartManage.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

using CB.Common;
using CB.Entity;
using CB.Interface.Infrastructure;

namespace CB.Data.SqlServer
{
    public class TrendChartManage : Repository<TrendChartInfo>, ITrendChartService
    {
        public TrendChartManage(string interfaceId)
            : base(interfaceId)
        {
        }

        public override bool Save(TrendChartInfo entity)
        {
            DbParameter[] pars =
            {
                DbHelper.MakeInParam(InterfaceId,"@Name",(DbType)SqlDbType.NChar,20,entity.Name),
                DbHelper.MakeInParam(InterfaceId,"@Cid",(DbType)SqlDbType.Int,4,entity.Cid),
                DbHelper.MakeInParam(InterfaceId,"@Tid",(DbType)SqlDbType.Int,4,entity.Tid),
                DbHelper.MakeInParam(InterfaceId,"@Status",(DbType)SqlDbType.Int,4,(int)entity.Status),
                DbHelper.MakeInParam(InterfaceId,"@OrderBy",(DbType)SqlDbType.Int,4,entity.OrderBy),
                DbHelper.MakeInParam(InterfaceId,"@Url",(DbType)SqlDbType.NVarChar,200,entity.Url),
                DbHelper.MakeInParam(InterfaceId,"@Type",(DbType)SqlDbType.Int,4,(int)entity.Type),
                DbHelper.MakeInParam(InterfaceId,"@hTitle",(DbType)SqlDbType.NVarChar,500,entity.hTitle),
                DbHelper.MakeInParam(InterfaceId,"@hKeywords",(DbType)SqlDbType.NVarChar,500,entity.hKeywords),
                DbHelper.MakeInParam(InterfaceId,"@hDescription",(DbType)SqlDbType.NVarChar,500,entity.hDescription),
                DbHelper.MakeInParam(InterfaceId,"@Direction",(DbType)SqlDbType.Int,4,entity.Direction),
                DbHelper.MakeInParam(InterfaceId,"@TemplateId",(DbType)SqlDbType.Int,4,entity.TemplateId)
            };
            return DbHelper.ExecuteNonQuery(InterfaceId, CommandType.Text,
                @"INSERT INTO [DT_TrendChart]([Name],[Cid],[Tid],[Status],[OrderBy],[Url],[Type],[hTitle],[hKeywords],[hDescription],[Direction],[TemplateId])
                    VALUES (@Name,@Cid,@Tid,@Status,@OrderBy,@Url,@Type,@hTitle,@hKeywords,@hDescription,@Direction,@TemplateId)", pars) > 0 ? true : false;
        }

        public override bool Update(TrendChartInfo entity)
        {
            DbParameter[] pars =
            {
                DbHelper.MakeInParam(InterfaceId,"@Id",(DbType)SqlDbType.Int,4,entity.Id),
                DbHelper.MakeInParam(InterfaceId,"@Name",(DbType)SqlDbType.NChar,20,entity.Name),
                DbHelper.MakeInParam(InterfaceId,"@Cid",(DbType)SqlDbType.Int,4,entity.Cid),
                DbHelper.MakeInParam(InterfaceId,"@Tid",(DbType)SqlDbType.Int,4,entity.Tid),
                DbHelper.MakeInParam(InterfaceId,"@Status",(DbType)SqlDbType.Int,4,(int)entity.Status),
                DbHelper.MakeInParam(InterfaceId,"@OrderBy",(DbType)SqlDbType.Int,4,entity.OrderBy),
                DbHelper.MakeInParam(InterfaceId,"@Url",(DbType)SqlDbType.NVarChar,200,entity.Url),
                DbHelper.MakeInParam(InterfaceId,"@Type",(DbType)SqlDbType.Int,4,(int)entity.Type),
                DbHelper.MakeInParam(InterfaceId,"@hTitle",(DbType)SqlDbType.NVarChar,500,entity.hTitle),
                DbHelper.MakeInParam(InterfaceId,"@hKeywords",(DbType)SqlDbType.NVarChar,500,entity.hKeywords),
                DbHelper.MakeInParam(InterfaceId,"@hDescription",(DbType)SqlDbType.NVarChar,500,entity.hDescription),
                DbHelper.MakeInParam(InterfaceId,"@Direction",(DbType)SqlDbType.Int,4,entity.Direction),
                DbHelper.MakeInParam(InterfaceId,"@TemplateId",(DbType)SqlDbType.Int,4,entity.TemplateId)
            };
            return DbHelper.ExecuteNonQuery(InterfaceId, CommandType.Text,
                @"UPDATE [DT_TrendChart] SET [Name]=@Name,[Cid]=@Cid,[Tid]=@Tid,[Status]=@Status,[OrderBy]=@OrderBy,[Url]=@Url,
                    [Type]=@Type,[hTitle]=@hTitle,[hKeywords]=@hKeywords,[hDescription]=@hDescription,[Direction]=@Direction,[TemplateId]=@TemplateId WHERE [Id]=@Id", pars) > 0 ? true : false;
        }

        public override bool Delete(int Id)
        {
            DbParameter[] pars =
            {
                DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,50,"DELETE FROM [DT_TrendChart] WHERE [Id]=@Id"),
                DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,10,"@Id int"),
                DbHelper.MakeInParam(InterfaceId,"@Id",(DbType)SqlDbType.Int,4,Id)
            };
            return DbHelper.ExecuteNonQuery(InterfaceId,CommandType.StoredProcedure, "dbo.sp_executesql", pars) > 0 ? true : false;
        }

        public override TrendChartInfo Get<TKey>(TKey key)
        {
            TrendChartInfo Entity = null;
            DbParameter[] para = 
            {
                DbHelper.MakeInParam(InterfaceId,"@id",(DbType)SqlDbType.Int,4,key)
            };
            IDataReader reader = DbHelper.ExecuteReader(InterfaceId,CommandType.Text,
                "SELECT TOP 1 [Id],[Name],[Cid],[Tid],[Status],[Type],[OrderBy],[hTitle],[hKeywords],[hDescription],[Url],[Direction],[TemplateId] FROM [DT_TrendChart] WHERE id= @id", para);
            if (reader.Read())
            {
                Entity = LoadEntity(reader);
            }
            return Entity;
        }

        public TrendChartInfo Get(TrendChartInfo entity)
        {
            TrendChartInfo reInfo = null;
            string sql = "SELECT TOP 1 [Id],[Name],[Cid],[Tid],[Status],[Type],[OrderBy],[hTitle],[hKeywords],[hDescription],[Url],[Direction],[TemplateId] FROM [DT_TrendChart] WHERE 1=1 ";
            if (!string.IsNullOrEmpty(entity.Name))
            {
                sql += " AND [Name]='" + entity.Name + "'";
            }
            if (0 < entity.Cid)
            {
                sql += " AND [Cid]=" + entity.Cid;
            }
            if (0 < entity.Tid)
            {
                sql += " AND [Tid]=" + entity.Tid;
            }
            sql += " ORDER BY OrderBy";
            using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId,CommandType.Text, sql))
            {
                if (reader.Read())
                {
                    reInfo = LoadEntity(reader);
                }
            }
            return reInfo;
        }

        public override IList<TrendChartInfo> ToList()
        {
            IList<TrendChartInfo> list = new List<TrendChartInfo>();
            using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId,CommandType.Text,
                "SELECT [Id],[Name],[Cid],[Tid],[Status],[Type],[OrderBy],[hTitle],[hKeywords],[hDescription],[Url],[Direction],[TemplateId] FROM [DT_TrendChart] ORDER BY [OrderBy] ASC"))
            {
                while (reader.Read())
                { list.Add(LoadEntity(reader)); }
            }
            return list;
        }

        public override IList<TrendChartInfo> ToList(TrendChartInfo entity)
        {
            throw new NotImplementedException();
        }

        public override IList<TrendChartInfo> ToPaging(TrendChartInfo entity, int pageSize, int pageIndex, out int recordCount)
        {
            recordCount = 0;
            IList<TrendChartInfo> list = new List<TrendChartInfo>();
            string where = "1=1 ";
            if (null != entity)
            {
                if (!string.IsNullOrEmpty(entity.Name))
                {
                    where += " AND [Name] Like '%" + entity.Name + "%'";
                }
                if (0 < entity.Cid)
                {
                    where += " AND [Cid]=" + entity.Cid;
                }
                if (0 < entity.Tid)
                {
                    where += " AND [Tid]=" + entity.Tid;
                }
            }
            DbParameter[] pars ={
                DbHelper.MakeInParam(InterfaceId,"@pageSize",(DbType)SqlDbType.Int,4,pageSize),
                DbHelper.MakeInParam(InterfaceId,"@page",(DbType)SqlDbType.Int,4,pageIndex),
                DbHelper.MakeInParam(InterfaceId,"@tableName",(DbType)SqlDbType.NVarChar,500,"[DT_TrendChart]"),
                DbHelper.MakeInParam(InterfaceId,"@field",(DbType)SqlDbType.NVarChar,1000,
                    "[Id],[Name],[Cid],[Tid],[Status],[Type],[OrderBy],[hTitle],[hKeywords],[hDescription],[Url],[Direction],[TemplateId]"),
                DbHelper.MakeInParam(InterfaceId,"@orderField",(DbType)SqlDbType.NVarChar,50,"[Cid],[Id] ASC"),
                DbHelper.MakeInParam(InterfaceId,"@where",(DbType)SqlDbType.NVarChar,2000,where)
                                };
            using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId,CommandType.StoredProcedure, "usp_st_page", pars))
            {
                while (reader.Read())
                {
                    list.Add(LoadEntity(reader));
                }
                if (reader.NextResult() && reader.Read())
                {
                    recordCount = reader.GetInt32(0);
                }
            }
            return list;
        }

        public void UpdateTrendSmart(int startId, int endId)
        {
            DbParameter[] pars ={
                DbHelper.MakeInParam(InterfaceId,"@startid",(DbType)SqlDbType.Int,4,startId),
                DbHelper.MakeInParam(InterfaceId,"@endid",(DbType)SqlDbType.Int,4,endId)
                                };
            DbHelper.ExecuteNonQuery(InterfaceId, CommandType.StoredProcedure, "usp_TrendSmart_update", pars);
        }

        public IList<TrendChartInfo> GetTrendSmartList(int chartId, int topSize)
        {
            DbParameter[] pars =
            {
                DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,300,
                @"SELECT TOP (@topSize) [Id],[Name],[Cid],[Tid],[Status],[Type],[OrderBy],[hTitle],[hKeywords],[hDescription],[Url],[Direction],[TemplateId] FROM [DT_TrendChart]
WHERE [Id] IN (SELECT TOP (@topSize) [EndId] FROM [DT_TrendSmart] WHERE [StartId]=@chartId ORDER BY [Hits] DESC) ORDER BY [Id]"),
                DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,30,"@topSize int,@chartId int"),
                DbHelper.MakeInParam(InterfaceId,"@topSize",(DbType)SqlDbType.Int,4,topSize),
                DbHelper.MakeInParam(InterfaceId,"@chartId",(DbType)SqlDbType.Int,4,chartId)
            };
            IList<TrendChartInfo> list = new List<TrendChartInfo>();
            using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId, CommandType.StoredProcedure, "dbo.sp_executesql", pars))
            {
                while (reader.Read())
                {
                    list.Add(LoadEntity(reader));
                }
                reader.Close();
            }
            return list;
        }

        protected override TrendChartInfo LoadEntity(IDataReader reader)
        {
            return new TrendChartInfo
            {
                Id = TypeConverter.ObjectToInt(reader["Id"]),
                Name = reader["Name"].ToString().Trim(),
                Cid = TypeConverter.ObjectToInt(reader["Cid"]),
                Tid = TypeConverter.ObjectToInt(reader["Tid"]),
                Status = (TrendChartStatus)Enum.Parse(typeof(TrendChartStatus), reader["Status"].ToString()),
                Type = (TrendChartIdType)Enum.Parse(typeof(TrendChartIdType), reader["type"].ToString()),
                OrderBy = TypeConverter.ObjectToInt(reader["OrderBy"]),
                hTitle = reader["hTitle"].ToString().Trim(),
                hKeywords = reader["hKeywords"].ToString().Trim(),
                hDescription = reader["hDescription"].ToString().Trim(),
                Url = reader["Url"].ToString().Trim(),
                Direction = TypeConverter.ObjectToInt(reader["Direction"]),
                TemplateId = TypeConverter.ObjectToInt(reader["TemplateId"])
            };
        }

        protected override TrendChartInfo LoadEntity(DataRow dr)
        {
            return new TrendChartInfo
            {
                Id = TypeConverter.ObjectToInt(dr["Id"]),
                Name = dr["Name"].ToString().Trim(),
                Cid = TypeConverter.ObjectToInt(dr["Cid"]),
                Tid = TypeConverter.ObjectToInt(dr["Tid"]),
                Status = (TrendChartStatus)Enum.Parse(typeof(TrendChartStatus), dr["Status"].ToString()),
                Type = (TrendChartIdType)Enum.Parse(typeof(TrendChartIdType), dr["type"].ToString()),
                OrderBy = TypeConverter.ObjectToInt(dr["OrderBy"]),
                hTitle = dr["hTitle"].ToString().Trim(),
                hKeywords = dr["hKeywords"].ToString().Trim(),
                hDescription = dr["hDescription"].ToString().Trim(),
                Url = dr["Url"].ToString().Trim(),
                Direction = TypeConverter.ObjectToInt(dr["Direction"]),
                TemplateId = TypeConverter.ObjectToInt(dr["TemplateId"])
            };
        }
    }
}