using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CY.IDAL;
using System.Data.SqlClient;
using System.Data;
using CY.Model;

namespace CY.SQLDAL
{
    public class Pay_PromotionRecordDAL : IPay_PromotionRecordDAL
    {

        private Database _dataBase = null;

        public Pay_PromotionRecordDAL()
        {
            _dataBase = new Database();
        }
        public Pay_PromotionRecordDAL(Database database)
        {
            _dataBase = database;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.Pay_PromotionRecord trueModel = model as Model.Pay_PromotionRecord;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    new SqlParameter("@PayId",trueModel.PayId),
					new SqlParameter("@TradingName",trueModel.TradingName),
					new SqlParameter("@TradingType",trueModel.TradingType),
					new SqlParameter("@TradingState",trueModel.TradingState),
					new SqlParameter("@TradingMoney",trueModel.TradingMoney),
					new SqlParameter("@ResidualMoney",trueModel.ResidualMoney),
					new SqlParameter("@CreateTime",trueModel.CreateTime),
					new SqlParameter("@Remark",trueModel.Remark),
                    new SqlParameter("@IsProxy",trueModel.IsProxy),
                    new SqlParameter("@TardingRemark",trueModel.TardingRemark),
                    new SqlParameter("@SendMemberId",trueModel.SendMemberId),
                    new SqlParameter("@GetMemberId",trueModel.GetMemberId),
                    new SqlParameter("@RewardType",trueModel.RewardType),
                    new SqlParameter("@DataType",trueModel.DataType)
            };
            try
            {
                _dataBase.Query("sp_Pay_PromotionRecord_Insert", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.Pay_PromotionRecord trueModel = model as Model.Pay_PromotionRecord;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {	
                    new SqlParameter("@Keyid",trueModel.Keyid),
                    new SqlParameter("@PayId",trueModel.PayId),
					new SqlParameter("@TradingName",trueModel.TradingName),
					new SqlParameter("@TradingType",trueModel.TradingType),
					new SqlParameter("@TradingState",trueModel.TradingState),
					new SqlParameter("@TradingMoney",trueModel.TradingMoney),
					new SqlParameter("@ResidualMoney",trueModel.ResidualMoney),
					new SqlParameter("@CreateTime",trueModel.CreateTime),
					new SqlParameter("@Remark",trueModel.Remark) ,
                    new SqlParameter("@IsProxy",trueModel.IsProxy),
                    new SqlParameter("@TardingRemark",trueModel.TardingRemark),
                    new SqlParameter("@SendMemberId",trueModel.SendMemberId),
                    new SqlParameter("@GetMemberId",trueModel.GetMemberId),
                    new SqlParameter("@RewardType",trueModel.RewardType),
                    new SqlParameter("@DataType",trueModel.DataType)
            };
            try
            {
                _dataBase.Query("sp_Pay_PromotionRecord_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.Pay_PromotionRecord trueModel = model as Model.Pay_PromotionRecord;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                	new SqlParameter("@Keyid",trueModel.Keyid)
            };
            try
            {
                _dataBase.Query("sp_Pay_PromotionRecord_DeleteRow", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="query"></param>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public IEnumerable<Model.Pay_PromotionRecord> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 单个查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IEnumerable<Model.Pay_PromotionRecord> SelectAllModel(Infrastructure.Query.Query query)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 推广记录
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="PayId"></param>
        /// <param name="TradingType"></param>
        /// <param name="TradingState"></param>
        /// <param name="IsProxy"></param>
        /// <param name="CreatStartTime"></param>
        /// <param name="CreatEndTime"></param>
        /// <param name="DataType"></param>
        /// <returns></returns>
        public IEnumerable<Model.Pay_PromotionRecord> SelectAllModel(Infrastructure.Query.Pagination pagination, int? PayId, int? TradingType, int? TradingState, string IsProxy, DateTime? CreatStartTime, DateTime? CreatEndTime,string DataType)
        {
            if (PayId == null || PayId <= 0)
                return null;//错误数据返会空 
            try
            {
                string condtion = " 1=1 ";
                if (PayId > 0)
                {
                    condtion = condtion + " and PayId ='" + PayId + "' ";
                }
                if (TradingType != null)
                {
                    condtion = condtion + " and TradingType ='" + TradingType + "' ";
                }
                if (TradingState != null)
                {
                    condtion = condtion + " and TradingState ='" + TradingState + "' ";
                }
                if (!string.IsNullOrEmpty(IsProxy))
                {
                    condtion = condtion + " and IsProxy ='" + IsProxy + "' ";
                }
                if (!string.IsNullOrEmpty(DataType))
                {
                    condtion = condtion + " and DataType ='" + DataType + "' ";
                }
                if (CreatStartTime != null)
                {
                    condtion = condtion + " and CreateTime >='" + CreatStartTime + "' ";
                }
                if (CreatEndTime != null)
                {
                    condtion = condtion + " and CreateTime <='" + CreatEndTime.Value.AddDays(1) + "' ";
                }

                IList<Pay_PromotionRecord> result = _dataBase.SelectModelPage<Model.Pay_PromotionRecord>(pagination, "*", "Pay_PromotionRecord", "CreateTime DESC", "CreateTime DESC", condtion) as IList<Pay_PromotionRecord>;//执行查询
                return null == result ? null : result;//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public IEnumerable<Model.Pay_PromotionRecord> SelectAllModel(Infrastructure.Query.Pagination pagination, int TradingType, string startDate, string endDate, string province, string city, string country)
        {
            IEnumerable<Model.Pay_PromotionRecord> list = null;
            string selectTarget = " a.*,c.Name as MemberName ";
            string fromSouce = " dbo.Pay_PromotionRecord a INNER JOIN dbo.Pay_PaymentAccount b ON a.Payid=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId ";
            fromSouce += " where 1=1 and a.TradingState=-1 ";
            fromSouce += " and a.tradingType=" + TradingType;
            if (!string.IsNullOrEmpty(startDate))
                fromSouce += " And a.CreateTime >= '" + startDate + "' ";
            if (!string.IsNullOrEmpty(endDate))
                fromSouce += " And a.CreateTime < '" + endDate + "' ";

            if (!string.IsNullOrEmpty(province))
            {
                fromSouce += " and c.Province='" + province + "'";
            }

            if (!string.IsNullOrEmpty(city))
            {
                fromSouce += " and c.City='" + city + "'";
            }

            if (!string.IsNullOrEmpty(country))
            {
                fromSouce += " and c.County='" + country + "'";
            }

            list = _dataBase.SelectModelPage<Pay_PromotionRecord>(pagination, selectTarget, fromSouce, " a.CreateTime desc ", " CreateTime desc ", string.Empty);
            return list;
        }
        
        /// <summary>
        /// 获取上个月的推广排名
        /// </summary>
        /// <param name="CreatStartTime"></param>
        /// <param name="CreatEndTime"></param>
        /// <returns></returns>
        public IEnumerable<Model.Pay_PromotionRecord> SelectAllModelByLastMonth(DateTime? CreatStartTime, DateTime? CreatEndTime)
        {
            try
            {
                IList<Pay_PromotionRecord> result = _dataBase.SelectModel<Model.Pay_PromotionRecord>(" TOP (10) c.Name AS MemberName,SUM(TradingMoney) AS AllMoney ", " dbo.Pay_PromotionRecord AS a LEFT JOIN dbo.Pay_PaymentAccount AS b ON a.PayId = b.Keyid LEFT JOIN dbo.EC_MemberBasic AS c ON b.MemberId = c.MemberId ", " CreateTime >= '" + DateTime.Now.AddMonths(-1).ToString("yyyy-MM") + "-01' and CreateTime < '" + DateTime.Now.ToString("yyyy-MM") + "-01' and DataType='推广' AND RewardType ='推广金' GROUP BY c.Name ") as IList<Pay_PromotionRecord>;//执行查询
                return null == result ? null : result;//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
    }
}