using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using CB.Common;
using CB.Data;
using CB.Entity;
using CB.Entity.Frequency;
using CB.Interface.Infrastructure.Frequency;

namespace CB.Data.SqlServer.Frequency
{
    public class KL10FChongQingManage : Repository<KL10FInfo_ChongQing>, IKL10FChongQingService
    {
        public KL10FChongQingManage(string interfaceId)
            : base(interfaceId)
        {
        }

        public override bool Save(KL10FInfo_ChongQing entity)
        {
            DbParameter[] pars ={
                DbHelper.MakeInParam(InterfaceId,"@Term",(DbType)SqlDbType.BigInt,8,entity.Term),
                DbHelper.MakeInParam(InterfaceId,"@OpenCode1",(DbType)SqlDbType.Int,4,entity.OpenCode1),
                DbHelper.MakeInParam(InterfaceId,"@OpenCode2",(DbType)SqlDbType.Int,4,entity.OpenCode2),
                DbHelper.MakeInParam(InterfaceId,"@OpenCode3",(DbType)SqlDbType.Int,4,entity.OpenCode3),
                DbHelper.MakeInParam(InterfaceId,"@OpenCode4",(DbType)SqlDbType.Int,4,entity.OpenCode4),
                DbHelper.MakeInParam(InterfaceId,"@OpenCode5",(DbType)SqlDbType.Int,4,entity.OpenCode5),
                DbHelper.MakeInParam(InterfaceId,"@OpenCode6",(DbType)SqlDbType.Int,4,entity.OpenCode6),
                DbHelper.MakeInParam(InterfaceId,"@OpenCode7",(DbType)SqlDbType.Int,4,entity.OpenCode7),
                DbHelper.MakeInParam(InterfaceId,"@OpenCode8",(DbType)SqlDbType.Int,4,entity.OpenCode8),
                DbHelper.MakeInParam(InterfaceId,"@OpenTime",(DbType)SqlDbType.DateTime,8,entity.OpenTime),
                DbHelper.MakeInParam(InterfaceId,"@Addtime",(DbType)SqlDbType.DateTime,8,entity.Addtime)
                                };
            return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(InterfaceId, CommandType.StoredProcedure, "ugp_KL10F_ChongQing_save", pars)) > 0;
        }

        public override bool Update(KL10FInfo_ChongQing entity)
        {
            throw new NotImplementedException();
        }

        public override bool Delete(int Id)
        {
            var sqlString = string.Empty;
            DbParameter[] para = 
            {
                DbHelper.MakeInParam(InterfaceId,"@Id",(DbType)SqlDbType.Int,4,Id)
            };
            sqlString = string.Format(@"DELETE GP_KL10F_ChongQing WHERE Id = @Id");
            return TypeConverter.ObjectToInt(DbHelper.ExecuteNonQuery(InterfaceId, CommandType.Text, sqlString, para)) > 0;
        }

        public override KL10FInfo_ChongQing Get<TKey>(TKey key)
        {
            KL10FInfo_ChongQing Entity = null;
            DbParameter[] para = 
            {
                DbHelper.MakeInParam(InterfaceId,"@Id",(DbType)SqlDbType.Int,4,key)
            };
            using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId, CommandType.Text,
                "SELECT TOP 1 [Id],[Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[OpenCode6],[OpenCode7],[OpenCode8],[OpenTime],[Addtime] FROM [GP_KL10F_ChongQing] WHERE [Id]= @Id", para))
            {
                if (reader.Read())
                {
                    Entity = LoadEntity(reader);
                }
                reader.Dispose();
            }
            return Entity;
        }

        public override IList<KL10FInfo_ChongQing> ToList()
        {
            IList<KL10FInfo_ChongQing> list = new List<KL10FInfo_ChongQing>();
            using (DataTable dt = DbHelper.ExecuteDatatable(InterfaceId,
                "SELECT [Id],[Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[OpenCode6],[OpenCode7],[OpenCode8],[OpenTime],[Addtime] FROM [GP_KL10F_ChongQing] ORDER BY [Term] asc"))
            {
                if (null != dt && 0 < dt.Rows.Count)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        var entity = new KL10FInfo_ChongQing
                        {
                            Term = TypeConverter.ObjectToInt(dr["Term"]),
                            OpenCode1 = TypeConverter.ObjectToInt(dr["OpenCode1"]),
                            OpenCode2 = TypeConverter.ObjectToInt(dr["OpenCode2"]),
                            OpenCode3 = TypeConverter.ObjectToInt(dr["OpenCode3"]),
                            OpenCode4 = TypeConverter.ObjectToInt(dr["OpenCode4"]),
                            OpenCode5 = TypeConverter.ObjectToInt(dr["OpenCode5"]),
                            OpenCode6 = TypeConverter.ObjectToInt(dr["OpenCode6"]),
                            OpenCode7 = TypeConverter.ObjectToInt(dr["OpenCode7"]),
                            OpenCode8 = TypeConverter.ObjectToInt(dr["OpenCode8"])
                        };
                        entity.OpenCode = new List<int>() { entity.OpenCode1, entity.OpenCode2, entity.OpenCode3, entity.OpenCode4, entity.OpenCode5, entity.OpenCode6, entity.OpenCode7, entity.OpenCode8 };
                        list.Add(entity);
                    }
                }
                dt.Dispose();
            }
            return list;
        }

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

        public override IList<KL10FInfo_ChongQing> ToPaging(KL10FInfo_ChongQing entity, int pageSize, int pageIndex, out int recordCount)
        {
            throw new NotImplementedException();
        }

        protected override KL10FInfo_ChongQing LoadEntity(DataRow dr)
        {
            return new KL10FInfo_ChongQing
            {
                Id = TypeConverter.ObjectToInt(dr["Id"]),
                Term = Convert.ToInt64(dr["Term"]),
                OpenCode1 = TypeConverter.ObjectToInt(dr["OpenCode1"]),
                OpenCode2 = TypeConverter.ObjectToInt(dr["OpenCode2"]),
                OpenCode3 = TypeConverter.ObjectToInt(dr["OpenCode3"]),
                OpenCode4 = TypeConverter.ObjectToInt(dr["OpenCode4"]),
                OpenCode5 = TypeConverter.ObjectToInt(dr["OpenCode5"]),
                OpenCode6 = TypeConverter.ObjectToInt(dr["OpenCode6"]),
                OpenCode7 = TypeConverter.ObjectToInt(dr["OpenCode7"]),
                OpenCode8 = TypeConverter.ObjectToInt(dr["OpenCode8"]),
                OpenTime = TypeConverter.ObjectToDateTime(dr["OpenTime"], DateTime.MinValue),
                Addtime = TypeConverter.ObjectToDateTime(dr["Addtime"], DateTime.MinValue),
            };
        }

        protected override KL10FInfo_ChongQing LoadEntity(IDataReader reader)
        {
            return new KL10FInfo_ChongQing
           {
               Id = TypeConverter.ObjectToInt(reader["Id"]),
               Term = Convert.ToInt64(reader["Term"]),
               OpenCode1 = TypeConverter.ObjectToInt(reader["OpenCode1"]),
               OpenCode2 = TypeConverter.ObjectToInt(reader["OpenCode2"]),
               OpenCode3 = TypeConverter.ObjectToInt(reader["OpenCode3"]),
               OpenCode4 = TypeConverter.ObjectToInt(reader["OpenCode4"]),
               OpenCode5 = TypeConverter.ObjectToInt(reader["OpenCode5"]),
               OpenCode6 = TypeConverter.ObjectToInt(reader["OpenCode6"]),
               OpenCode7 = TypeConverter.ObjectToInt(reader["OpenCode7"]),
               OpenCode8 = TypeConverter.ObjectToInt(reader["OpenCode8"]),
               OpenTime = TypeConverter.ObjectToDateTime(reader["OpenTime"], DateTime.MinValue),
               Addtime = TypeConverter.ObjectToDateTime(reader["Addtime"], DateTime.MinValue),
           };
        }
        public LotteryOpenCode GetLatestOpenCodeForCatch()
        {
            LotteryOpenCode entity = null;
            using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId, CommandType.Text, "SELECT TOP 1 [Term],[OpenTime],[Addtime] FROM [GP_KL10F_ChongQing] ORDER BY [OpenTime] DESC"))
            {
                if (reader.Read())
                {
                    entity = new LotteryOpenCode()
                    {
                        Term = Convert.ToInt64(reader["Term"]),
                        OpenTime = TypeConverter.ObjectToDateTime(reader["OpenTime"], DateTime.MinValue),
                        Addtime = TypeConverter.ObjectToDateTime(reader["OpenTime"], DateTime.MinValue)
                    };
                }
                reader.Close();
            }
            return entity;
        }
        public IList<KL10FInfo_ChongQing> GetTopListDesc(LotterySearchField fields)
        {
            throw new NotImplementedException();
        }
        public IList<KL10FInfo_ChongQing> ToListForTrend(long term, LotterySearchField fields)
        {
            IList<KL10FInfo_ChongQing> list = new List<KL10FInfo_ChongQing>();
            DbParameter[] para;
            para = new DbParameter[]
                        {
                            DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,200,"SELECT top 2 [Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[OpenCode6],[OpenCode7],[OpenCode8],[OpenTime] FROM [GP_KL10F_ChongQing] WHERE [Term]<=@qi ORDER BY [Term] DESC"),
                            DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,10,"@qi int"),
                            DbHelper.MakeInParam(InterfaceId,"@qi",(DbType)SqlDbType.Int,4,term)
                        };
            using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId, CommandType.StoredProcedure, "dbo.sp_executesql", para))
            {
                while (reader.Read())
                {
                    var entity = new KL10FInfo_ChongQing
                    {
                        Term = TypeConverter.ObjectToInt(reader["Term"]),
                        OpenCode1 = TypeConverter.ObjectToInt(reader["OpenCode1"]),
                        OpenCode2 = TypeConverter.ObjectToInt(reader["OpenCode2"]),
                        OpenCode3 = TypeConverter.ObjectToInt(reader["OpenCode3"]),
                        OpenCode4 = TypeConverter.ObjectToInt(reader["OpenCode4"]),
                        OpenCode5 = TypeConverter.ObjectToInt(reader["OpenCode5"]),
                        OpenCode6 = TypeConverter.ObjectToInt(reader["OpenCode6"]),
                        OpenCode7 = TypeConverter.ObjectToInt(reader["OpenCode7"]),
                        OpenCode8 = TypeConverter.ObjectToInt(reader["OpenCode8"]),
                        OpenTime = TypeConverter.ObjectToDateTime(reader["OpenTime"])
                    };
                    entity.OpenCode = new List<int>() { entity.OpenCode1, entity.OpenCode2, entity.OpenCode3, entity.OpenCode4, entity.OpenCode5,entity.OpenCode6,entity.OpenCode7,entity.OpenCode8 };
                    list.Add(entity);
                }
                reader.Dispose();
            }
            return list;
        }

        public IList<KL10FInfo_ChongQing> GetListToEnd(long term, LotterySearchField fields)
        {
            IList<KL10FInfo_ChongQing> list = new List<KL10FInfo_ChongQing>();
            DbParameter[] para;
            para = new DbParameter[]
                        {
                            DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,200,"SELECT  [Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[OpenCode6],[OpenCode7],[OpenCode8] FROM [GP_KL10F_ChongQing] WHERE [Term] >= @qi ORDER BY [Term] ASC"),
                            DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,10,"@qi int"),
                            DbHelper.MakeInParam(InterfaceId,"@qi",(DbType)SqlDbType.Int,4,term)
                        };
            using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId, CommandType.StoredProcedure, "dbo.sp_executesql", para))
            {
                while (reader.Read())
                {
                    var entity = new KL10FInfo_ChongQing
                    {
                        Term = TypeConverter.ObjectToInt(reader["Term"]),
                        OpenCode1 = TypeConverter.ObjectToInt(reader["OpenCode1"]),
                        OpenCode2 = TypeConverter.ObjectToInt(reader["OpenCode2"]),
                        OpenCode3 = TypeConverter.ObjectToInt(reader["OpenCode3"]),
                        OpenCode4 = TypeConverter.ObjectToInt(reader["OpenCode4"]),
                        OpenCode5 = TypeConverter.ObjectToInt(reader["OpenCode5"]),
                        OpenCode6 = TypeConverter.ObjectToInt(reader["OpenCode6"]),
                        OpenCode7 = TypeConverter.ObjectToInt(reader["OpenCode7"]),
                        OpenCode8 = TypeConverter.ObjectToInt(reader["OpenCode8"])
                    };
                    entity.OpenCode = new List<int>() { entity.OpenCode1, entity.OpenCode2, entity.OpenCode3, entity.OpenCode4, entity.OpenCode5, entity.OpenCode6, entity.OpenCode7, entity.OpenCode8 };
                    list.Add(entity);
                }
                reader.Dispose();
            }
            return list;
        }

        public IList<KL10FInfo_ChongQing> GetListToEnd(long term, TrendChartSearchField fields)
        {
            IList<KL10FInfo_ChongQing> list = new List<KL10FInfo_ChongQing>();
            DbParameter[] para;
            int numRepeat = null == fields ? 0 : fields.NumRepeat;

            string query = "";
            int topSize = 0;
            if (null != fields)
            {
                if (fields.Year > 0)
                {
                    query += " and Term between " + (fields.Year * 1000).ToString() + " and " + ((fields.Year + 1) * 1000).ToString() + " ";
                    fields.Record = 1500;
                }
                else if (fields.StartTerm > 0 && fields.EndTerm > 0)
                { query += " and Term between " + fields.StartTerm.ToString() + " and " + fields.EndTerm.ToString() + " "; fields.Record = 1500; }
                else if (fields.Record == 0)
                { fields.Record = 30; }
                topSize = fields.Record;
            }

            string topSql = "";
            if (topSize > 0) topSql = " top (" + topSize.ToString() + ")";

            para = new DbParameter[]
                        {
                            DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,2000,"SELECT "+topSql+" [Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[OpenCode6],[OpenCode7],[OpenCode8] FROM [GP_KL10F_ChongQing] WHERE  OpenCode1 > -1 and [Term]>=@qi "+ query+" ORDER BY [Term] DESC"),
                            DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,10,"@qi int"),
                            DbHelper.MakeInParam(InterfaceId,"@qi",(DbType)SqlDbType.Int,4,term)
                        };
            using (DataTable dt = DbHelper.ExecuteDatatable(InterfaceId, CommandType.StoredProcedure, "dbo.sp_executesql", para))
            {
                if (null != dt && 0 < dt.Rows.Count)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        var entity = new KL10FInfo_ChongQing
                        {
                            Term = TypeConverter.ObjectToInt(dr["Term"]),
                            OpenCode1 = TypeConverter.ObjectToInt(dr["OpenCode1"]),
                            OpenCode2 = TypeConverter.ObjectToInt(dr["OpenCode2"]),
                            OpenCode3 = TypeConverter.ObjectToInt(dr["OpenCode3"]),
                            OpenCode4 = TypeConverter.ObjectToInt(dr["OpenCode4"]),
                            OpenCode5 = TypeConverter.ObjectToInt(dr["OpenCode5"]),
                            OpenCode6 = TypeConverter.ObjectToInt(dr["OpenCode6"]),
                            OpenCode7 = TypeConverter.ObjectToInt(dr["OpenCode7"]),
                            OpenCode8 = TypeConverter.ObjectToInt(dr["OpenCode8"])
                        };
                        entity.OpenCode = new List<int>() { entity.OpenCode1, entity.OpenCode2, entity.OpenCode3, entity.OpenCode4, entity.OpenCode5, entity.OpenCode6, entity.OpenCode7, entity.OpenCode8 };
                        list.Add(entity);
                    }
                }
                dt.Dispose();
            }
            return list;
        }
        public int GetMaxTerm()
        {
            throw new NotImplementedException();
        }


        public IList<KL10FInfo_ChongQing> ToListForNextTrend(long term, LotterySearchField fields)
        {
            IList<KL10FInfo_ChongQing> list = new List<KL10FInfo_ChongQing>();
            DbParameter[] para;
            para = new DbParameter[]
                        {
                            DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,400,"SELECT TOP 2 [Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[OpenCode6],[OpenCode7],[OpenCode8] FROM [GP_KL10F_ChongQing] WHERE [Term]>=@qi  ORDER BY [Term] ASC"),
                            DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,10,"@qi int"),
                            DbHelper.MakeInParam(InterfaceId,"@qi",(DbType)SqlDbType.Int,4,term)
                        };
            using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId, CommandType.StoredProcedure, "dbo.sp_executesql", para))
            {
                while (reader.Read())
                {
                    var entity = new KL10FInfo_ChongQing
                    {
                        Term = TypeConverter.ObjectToInt(reader["Term"]),
                        OpenCode1 = TypeConverter.ObjectToInt(reader["OpenCode1"]),
                        OpenCode2 = TypeConverter.ObjectToInt(reader["OpenCode2"]),
                        OpenCode3 = TypeConverter.ObjectToInt(reader["OpenCode3"]),
                        OpenCode4 = TypeConverter.ObjectToInt(reader["OpenCode4"]),
                        OpenCode5 = TypeConverter.ObjectToInt(reader["OpenCode5"]),
                        OpenCode6 = TypeConverter.ObjectToInt(reader["OpenCode6"]),
                        OpenCode7 = TypeConverter.ObjectToInt(reader["OpenCode7"]),
                        OpenCode8 = TypeConverter.ObjectToInt(reader["OpenCode8"])
                    };
                    entity.OpenCode = new List<int>() { entity.OpenCode1, entity.OpenCode2, entity.OpenCode3, entity.OpenCode4, entity.OpenCode5, entity.OpenCode6, entity.OpenCode7, entity.OpenCode8 };
                    list.Add(entity);
                }
                reader.Dispose();
            }
            return list;
        }
        public IList<KL10FInfo_ChongQing> ToListByYear(DateTime SearchTime)
        {
            DateTime yesterday = SearchTime.Date;
            DateTime tomorrow = SearchTime.AddDays(1).Date;
            string sql = "SELECT [Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[OpenCode6],[OpenCode7],[OpenCode8],[OpenTime] FROM [GP_KL10F_ChongQing] WHERE [OpenTime]>'" + yesterday + "' AND [Opentime]<'" + tomorrow + "' ORDER BY [Term] asc";
            IList<KL10FInfo_ChongQing> list = new List<KL10FInfo_ChongQing>();
            using (DataTable dt = DbHelper.ExecuteDatatable(InterfaceId,
                sql))
            {
                if (null != dt && 0 < dt.Rows.Count)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        var entity = new KL10FInfo_ChongQing
                        {
                            Term = TypeConverter.ObjectToInt(dr["Term"]),
                            OpenCode1 = TypeConverter.ObjectToInt(dr["OpenCode1"]),
                            OpenCode2 = TypeConverter.ObjectToInt(dr["OpenCode2"]),
                            OpenCode3 = TypeConverter.ObjectToInt(dr["OpenCode3"]),
                            OpenCode4 = TypeConverter.ObjectToInt(dr["OpenCode4"]),
                            OpenCode5 = TypeConverter.ObjectToInt(dr["OpenCode5"]),
                            OpenCode6 = TypeConverter.ObjectToInt(dr["OpenCode6"]),
                            OpenCode7 = TypeConverter.ObjectToInt(dr["OpenCode7"]),
                            OpenCode8 = TypeConverter.ObjectToInt(dr["OpenCode8"]),
                            OpenTime = TypeConverter.ObjectToDateTime(dr["Opentime"])
                        };
                        entity.OpenCode = new List<int>() { entity.OpenCode1, entity.OpenCode2, entity.OpenCode3, entity.OpenCode4, entity.OpenCode5, entity.OpenCode6, entity.OpenCode7, entity.OpenCode8 };
                        list.Add(entity);
                    }
                }
                dt.Dispose();
            }
            return list;
        }


        public IList<KL10FInfo_ChongQing> GetListByDate(DateTime Date)
        {
            var startTime = Date.Date;
            var endTime = Date.Date.AddDays(1).AddSeconds(-1);
            string sql = "SELECT [Id],[Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[OpenCode6],[OpenCode7],[OpenCode8],[OpenTime],[Addtime] FROM [GP_KL10F_ChongQing] WHERE [OpenTime]>='" + startTime + "' AND [Opentime]<='" + endTime + "' ORDER BY [Term] asc";
            IList<KL10FInfo_ChongQing> list = new List<KL10FInfo_ChongQing>();
            using (DataTable dt = DbHelper.ExecuteDatatable(InterfaceId,
                sql))
            {
                if (null != dt && 0 < dt.Rows.Count)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        var entity = new KL10FInfo_ChongQing
                        {
                            Id = TypeConverter.ObjectToInt(dr["Id"]),
                            Term = TypeConverter.ObjectToInt(dr["Term"]),
                            OpenCode1 = TypeConverter.ObjectToInt(dr["OpenCode1"]),
                            OpenCode2 = TypeConverter.ObjectToInt(dr["OpenCode2"]),
                            OpenCode3 = TypeConverter.ObjectToInt(dr["OpenCode3"]),
                            OpenCode4 = TypeConverter.ObjectToInt(dr["OpenCode4"]),
                            OpenCode5 = TypeConverter.ObjectToInt(dr["OpenCode5"]),
                            OpenCode6 = TypeConverter.ObjectToInt(dr["OpenCode6"]),
                            OpenCode7 = TypeConverter.ObjectToInt(dr["OpenCode7"]),
                            OpenCode8 = TypeConverter.ObjectToInt(dr["OpenCode8"]),
                            OpenTime = TypeConverter.ObjectToDateTime(dr["Opentime"])
                        };
                        entity.OpenCode = new List<int>() { entity.OpenCode1, entity.OpenCode2, entity.OpenCode3, entity.OpenCode4, entity.OpenCode5 };
                        list.Add(entity);
                    }
                }
                dt.Dispose();
            }
            return list;
        }
    }
}