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_IncExpRecordDAL : IPay_IncExpRecordDAL
    {

        private Database _dataBase = null;

        public Pay_IncExpRecordDAL()
        {
            _dataBase = new Database();
        }
        public Pay_IncExpRecordDAL(Database database)
        {
            _dataBase = database;
        }
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.Pay_IncExpRecord trueModel = model as Model.Pay_IncExpRecord;
            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("@PayerName",trueModel.PayerName),
					new SqlParameter("@PayeeId",trueModel.PayeeId),
					new SqlParameter("@PayeeName",trueModel.PayeeName),
					new SqlParameter("@CreateTime",trueModel.CreateTime),
					new SqlParameter("@Remark",trueModel.Remark),
                    new SqlParameter("@BalanceAccount",trueModel.BalanceAccount)
            };
            try
            {
                _dataBase.Query("sp_Pay_IncExpRecord_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_IncExpRecord trueModel = model as Model.Pay_IncExpRecord;
            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("@PayerName",trueModel.PayerName),
					new SqlParameter("@PayeeId",trueModel.PayeeId),
					new SqlParameter("@PayeeName",trueModel.PayeeName),
					new SqlParameter("@CreateTime",trueModel.CreateTime),
					new SqlParameter("@Remark",trueModel.Remark) 
            };
            try
            {
                _dataBase.Query("sp_Pay_IncExpRecord_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_IncExpRecord trueModel = model as Model.Pay_IncExpRecord;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                	new SqlParameter("@Keyid",trueModel.Keyid)
            };
            try
            {
                _dataBase.Query("sp_Pay_IncExpRecord_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_IncExpRecord> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 根据账户编号分页查询(type:支出-1收入1)
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="PayId"></param>
        /// <returns></returns>
        public IEnumerable<Model.Pay_IncExpRecord> SelectModelPage(Infrastructure.Query.Pagination pagination, int? PayId,int? Type)
        {
            if (PayId == null || PayId <= 0)
                return null;//错误数据返会空 
            try
            {
                string condtion = "";
                switch (Type)
                {
                    case -1:
                        condtion = string.Format(" PayId = '{0}' and TradingType={1}", PayId, Type);
                        break;
                    case 1:
                        condtion = string.Format(" PayeeId ='{0}'  and TradingType={1}", PayId, Type);
                        break;
                    default:
                        break;
                }

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

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

        /// <summary>
        /// 分页返回全部流水账
        /// </summary>
        /// <param name="query"></param>
        /// <param name="payId"></param>
        /// <returns></returns>
        public IEnumerable<Model.Pay_IncExpRecord> SelectAllModel(Infrastructure.Query.Pagination pagination, int? PayId)
        {
            if (PayId == null || PayId <= 0)
                return null;//错误数据返会空 
            try
            {
                string condtion = "";
                condtion = string.Format("  (TradingType=-1 AND PayId={0}) OR (TradingType=1 AND PayeeId={0}) ", PayId);
                IList<Pay_IncExpRecord> result = _dataBase.SelectModelPage<Model.Pay_IncExpRecord>(pagination, "*", "Pay_IncExpRecord", "CreateTime DESC", "CreateTime DESC,TradingType desc", condtion) as IList<Pay_IncExpRecord>;//执行查询
                return null == result ? null : result;//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        /// <summary>
        /// 根据条件分页返回全部流水账
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="payId"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="tradingType"></param>
        /// <param name="balanceAccount"></param>
        /// <param name="memberType"></param>
        /// <param name="memberName"></param>
        /// <returns></returns>
        public IEnumerable<Model.Pay_IncExpRecord> SelectAllModelByPaging(Infrastructure.Query.Pagination pagination, int? payId, string startDate, string endDate, int tradingType, int balanceAccount, string memberType, string memberName, string province, string city, string county)
        {
            if (payId == null || payId <= 0)
                return null;//错误数据返会空 
            try
            {
                string selectTarget = "t.*";
                string fromSouce = @"(
                                SELECT a.*,c.MemberType AS MemberTypeName,c.Name AS MemberName,d.Name as BalanceAccountName,c.Province,c.City,c.County
                                FROM Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
                                ON a.PayId=b.keyid INNER JOIN dbo.EC_MemberBasic c
                                ON b.MemberId=c.MemberId left join Sys_Dictionary d on a.BalanceAccount=d.meanvalue
                                and d.DicType='收支科目'
                                WHERE TradingType=1
                                UNION
                                SELECT a.*,c.MemberType AS MemberTypeName,c.Name AS MemberName,d.Name as BalanceAccountName,c.Province,c.City,c.County
                                FROM Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
                                ON a.PayeeId=b.keyid INNER JOIN dbo.EC_MemberBasic c
                                ON b.MemberId=c.MemberId left join Sys_Dictionary d on a.BalanceAccount=d.meanvalue
                                and d.DicType='收支科目'
                                WHERE TradingType=-1
                                ) t ";
                fromSouce += string.Format(" where ((t.TradingType=-1 AND t.PayId={0}) OR (t.TradingType=1 AND t.PayeeId={0}) or (t.TradingType=-1 AND t.PayeeId={0})) ", payId);
                if (!string.IsNullOrEmpty(startDate))
                {
                    fromSouce += " and t.CreateTime>='" + startDate + "' ";
                }
                if (!string.IsNullOrEmpty(endDate))
                {
                    fromSouce += " and t.CreateTime<'" + endDate + "' ";
                }
                if (tradingType!=0)
                {
                    fromSouce += " and t.TradingType='" + tradingType + "' ";
                }
                if (balanceAccount != -1)
                {
                    fromSouce += " and t.balanceAccount='" + balanceAccount + "' ";
                }
                if (!string.IsNullOrEmpty(memberType))
                {
                    fromSouce += " and t.MemberTypeName='" + memberType + "' ";
                }
                if (!string.IsNullOrEmpty(memberName))
                {
                    fromSouce += " and t.MemberName like '%" + memberName + "%'";
                }
                if (!string.IsNullOrEmpty(province))
                {
                    fromSouce += " and t.Province='"+province+"'";
                }
                if (!string.IsNullOrEmpty(city))
                {
                    fromSouce += " and t.City='" + city + "'";
                }
                if (!string.IsNullOrEmpty(county))
                {
                    fromSouce += " and t.County='" + county + "'";
                }
                IList<Pay_IncExpRecord> result = _dataBase.SelectModelPage<Model.Pay_IncExpRecord>(pagination, selectTarget, fromSouce, "t.CreateTime DESC", "CreateTime DESC,Keyid desc", string.Empty) as IList<Pay_IncExpRecord>;//执行查询
                return null == result ? null : result;//返回结果
                
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取已收佣金明细
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="payId"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="province"></param>
        /// <param name="city"></param>
        /// <param name="county"></param>
        /// <returns></returns>
        public IEnumerable<Model.Pay_IncExpRecord> SelectAllModelByPaging(Infrastructure.Query.Pagination pagination, int? payId, string startDate, string endDate, string province, string city, string county)
        {
            if (payId == null || payId <= 0)
                return null;//错误数据返会空 
            try
            {
                string selectTarget = "t.*";
                string fromSouce = @"(
                                SELECT a.*,c.MemberType AS MemberTypeName,c.Name AS MemberName,d.Name as BalanceAccountName,c.Province,c.City,c.County
                                FROM Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
                                ON a.PayId=b.keyid INNER JOIN dbo.EC_MemberBasic c
                                ON b.MemberId=c.MemberId left join Sys_Dictionary d on a.BalanceAccount=d.meanvalue
                                and d.DicType='收支科目'
                                WHERE TradingType=1
                                UNION
                                SELECT a.*,c.MemberType AS MemberTypeName,c.Name AS MemberName,d.Name as BalanceAccountName,c.Province,c.City,c.County
                                FROM Pay_IncExpRecord a INNER JOIN dbo.Pay_PaymentAccount b
                                ON a.PayeeId=b.keyid INNER JOIN dbo.EC_MemberBasic c
                                ON b.MemberId=c.MemberId left join Sys_Dictionary d on a.BalanceAccount=d.meanvalue
                                and d.DicType='收支科目'
                                WHERE TradingType=-1
                                ) t ";
                fromSouce += string.Format(" where ((t.TradingType=-1 AND t.PayId={0}) OR (t.TradingType=1 AND t.PayeeId={0}) or (t.TradingType=-1 AND t.PayeeId={0})) ", payId);
                if (!string.IsNullOrEmpty(startDate))
                {
                    fromSouce += " and t.CreateTime>='" + startDate + "' ";
                }
                if (!string.IsNullOrEmpty(endDate))
                {
                    fromSouce += " and t.CreateTime<'" + endDate + "' ";
                }
                
                fromSouce += " and t.TradingType=1 ";

                fromSouce += " and (t.balanceAccount=3 or t.balanceAccount=10) ";


                if (!string.IsNullOrEmpty(province))
                {
                    fromSouce += " and t.Province='" + province + "'";
                }
                if (!string.IsNullOrEmpty(city))
                {
                    fromSouce += " and t.City='" + city + "'";
                }
                if (!string.IsNullOrEmpty(county))
                {
                    fromSouce += " and t.County='" + county + "'";
                }
                IList<Pay_IncExpRecord> result = _dataBase.SelectModelPage<Model.Pay_IncExpRecord>(pagination, selectTarget, fromSouce, "t.CreateTime DESC", "CreateTime DESC,Keyid desc", string.Empty) as IList<Pay_IncExpRecord>;//执行查询
                return null == result ? null : result;//返回结果

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 查询欠款明细
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="province"></param>
        /// <param name="city"></param>
        /// <param name="county"></param>
        /// <returns></returns>
        public IEnumerable<Pay_OwedMoney> selectAllOwedMoneyList(Infrastructure.Query.Pagination pagination,string startDate, string endDate, string province, string city, string county)
        {
            string selectTarget = " c.Name as MemberName,a.OwedAmount,a.Subject,a.CreateTime ";

            string fromSouce = @" dbo.Pay_OwedMoney a INNER JOIN dbo.Pay_PaymentAccount b
                        ON a.Payid=b.Keyid INNER JOIN dbo.EC_MemberBasic c ON c.MemberId=b.MemberId
                        WHERE a.IsEffective=1";
            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(county))
            {
                fromSouce += " and c.County='" + county + "'";
            }

            var result = _dataBase.SelectModelPage<Model.Pay_OwedMoney>(pagination, selectTarget, fromSouce, " a.CreateTime DESC ", " CreateTime DESC ", string.Empty);//执行查询
            return null == result ? null : result;//返回结果
        }


        /// <summary>
        /// 查询时间段内会员授信情况
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="currentPayId"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="selectProvince"></param>
        /// <param name="selectCity"></param>
        /// <param name="selectCounty"></param>
        /// <returns></returns>
        public IEnumerable<Model.Pay_IncExpRecord> SelectAllCreditByPaging(Infrastructure.Query.Pagination pagination, int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty)
        {
            if (currentPayId <= 0)
                return null;//错误数据返会空 
            try
            {
                string WhereString = " 1=1 ";
                string WhereString1 = "";
                string WhereString2 = "";
                string selectTarget = "t.*";
                if (!string.IsNullOrEmpty(selectProvince))
                {
                    WhereString += " and a.Province='" + selectProvince + "' ";
                }

                if (!string.IsNullOrEmpty(selectCity))
                {
                    WhereString += " and a.City='" + selectCity + "' ";
                }

                if (!string.IsNullOrEmpty(selectCounty))
                {
                    WhereString += " and a.County='" + selectCounty + "' ";
                }
                if (!string.IsNullOrEmpty(startDate))
                {
                    WhereString2 += " and  d.CreateTime >= '" + startDate + "' ";
                }
                if (!string.IsNullOrEmpty(endDate))
                {
                    WhereString1 += " and  c.CreateTime< '" + endDate + "' ";
                    WhereString2 += " and  d.CreateTime< '" + endDate + "' ";
                }

                string fromSouce = @" ( select * from  ( SELECT a.Name as TradingName,SUM(ISNULL(c.TradingMoney,0)) AS TradingMoney,SUM(ISNULL(d.TradingMoney,0)) AS ResidualMoney FROM dbo.EC_MemberBasic a INNER JOIN dbo.Pay_PaymentAccount b ON a.MemberId = b.MemberId LEFT JOIN dbo.Pay_CreditRecard c ON b.Keyid = c.PayId AND c.PayId <> 23 " + WhereString1 + " LEFT JOIN dbo.Pay_IncExpRecord d ON b.Keyid = d.PayId AND d.TradingType=-1 AND d.PayId=23 AND d.BalanceAccount=9 " + WhereString2 + " where " + WhereString + " GROUP BY a.Name ) as m where m.TradingMoney > 0 or ( m.TradingMoney = 0 and  m.ResidualMoney <> 0) ) t ";

                IList<Pay_IncExpRecord> result = _dataBase.SelectModelPage<Model.Pay_IncExpRecord>(pagination, selectTarget, fromSouce, "t.TradingName DESC", "TradingName DESC", string.Empty) as IList<Pay_IncExpRecord>;//执行查询
                return null == result ? null : result;//返回结果

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 查询时间段内会员授信使用
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="currentPayId"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="selectProvince"></param>
        /// <param name="selectCity"></param>
        /// <param name="selectCounty"></param>
        /// <returns></returns>
        public IEnumerable<Model.Pay_IncExpRecord> SelectUseCreditByPaging(Infrastructure.Query.Pagination pagination, int currentPayId, string startDate, string endDate, string selectProvince, string selectCity, string selectCounty)
        {
            if (currentPayId <= 0)
                return null;//错误数据返会空 
            try
            {
                string WhereString = "";
                string selectTarget = "t.*";
                if (!string.IsNullOrEmpty(selectProvince))
                {
                    WhereString += " and a.Province='" + selectProvince + "' ";
                }

                if (!string.IsNullOrEmpty(selectCity))
                {
                    WhereString += " and a.City='" + selectCity + "' ";
                }

                if (!string.IsNullOrEmpty(selectCounty))
                {
                    WhereString += " and a.County='" + selectCounty + "' ";
                }
                if (!string.IsNullOrEmpty(startDate))
                {
                    WhereString += " and  d.CreateTime >= '" + startDate + "' ";
                }
                if (!string.IsNullOrEmpty(endDate))
                {
                    WhereString += " and  d.CreateTime< '" + endDate + "' ";
                }

                string fromSouce = @"( SELECT d.* FROM dbo.Pay_IncExpRecord d LEFT JOIN dbo.EC_MemberBasic a INNER JOIN dbo.Pay_PaymentAccount b ON a.MemberId = b.MemberId ON b.Keyid = d.PayId WHERE d.TradingType=-1 AND d.PayId=23 AND d.BalanceAccount=9 " + WhereString + " ) t ";

                IList<Pay_IncExpRecord> result = _dataBase.SelectModelPage<Model.Pay_IncExpRecord>(pagination, selectTarget, fromSouce, "t.TradingName DESC", "TradingName DESC", string.Empty) as IList<Pay_IncExpRecord>;//执行查询
                return null == result ? null : result;//返回结果

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}