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

namespace CY.SQLDAL
{
    public class OA_RemittanceAdviceDAL : IOA_RemittanceAdviceDAL
    {

        private Database _dataBase = null;

        public OA_RemittanceAdviceDAL()
        {
            _dataBase = new Database();
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_RemittanceAdvice trueModel = model as Model.OA_RemittanceAdvice;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                
                new SqlParameter("@ReceivablesFirm",trueModel.ReceivablesFirm),
				new SqlParameter("@RemittanceFirmId",trueModel.RemittanceFirmId),
				new SqlParameter("@RemittanceMoney",trueModel.RemittanceMoney),
				new SqlParameter("@RemittanceBankId",trueModel.RemittanceBankId),
				new SqlParameter("@TransactionsId",trueModel.TransactionsId),
				new SqlParameter("@TradingChannelsId",trueModel.TradingChannelsId),
				new SqlParameter("@TrandingSummary",trueModel.TrandingSummary),
				new SqlParameter("@TrandingExplain",trueModel.TrandingExplain),
				new SqlParameter("@TrandingPlace",trueModel.TrandingPlace),
				new SqlParameter("@PaymentAccount",trueModel.PaymentAccount),
				new SqlParameter("@TrandingStatus",trueModel.TrandingStatus),
				new SqlParameter("@TrandingTime",trueModel.TrandingTime),
				new SqlParameter("@SuerTime",DBNull.Value),
				new SqlParameter("@SuerMoney",DBNull.Value),
				new SqlParameter("@PublishTime",trueModel.PublishTime),
				new SqlParameter("@Remark",trueModel.Remark),
				new SqlParameter("@SuerPerson",DBNull.Value),
				new SqlParameter("@Publisher",trueModel.Publisher) 

            };
            try
            {
                _dataBase.Query("sp_OA_RemittanceAdvice_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.OA_RemittanceAdvice trueModel = model as Model.OA_RemittanceAdvice;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {new SqlParameter("@Keyid",trueModel.Keyid),
                new SqlParameter("@ReceivablesFirm",trueModel.ReceivablesFirm),
					new SqlParameter("@RemittanceFirmId",trueModel.RemittanceFirmId),
					new SqlParameter("@RemittanceMoney",trueModel.RemittanceMoney),
					new SqlParameter("@RemittanceBankId",trueModel.RemittanceBankId),
					new SqlParameter("@TransactionsId",trueModel.TransactionsId),
					new SqlParameter("@TradingChannelsId",trueModel.TradingChannelsId),
					new SqlParameter("@TrandingSummary",trueModel.TrandingSummary),
					new SqlParameter("@TrandingExplain",trueModel.TrandingExplain),
					new SqlParameter("@TrandingPlace",trueModel.TrandingPlace),
					new SqlParameter("@PaymentAccount",trueModel.PaymentAccount),
					new SqlParameter("@TrandingStatus",trueModel.TrandingStatus),
					new SqlParameter("@TrandingTime",trueModel.TrandingTime),
					new SqlParameter("@SuerTime",trueModel.SuerTime),
					new SqlParameter("@SuerMoney",trueModel.SuerMoney),
					new SqlParameter("@PublishTime",trueModel.PublishTime),
					new SqlParameter("@Remark",trueModel.Remark),
					new SqlParameter("@SuerPerson",trueModel.SuerPerson),
					new SqlParameter("@Publisher",trueModel.Publisher) 
            };
            try
            {
                _dataBase.Query("sp_OA_RemittanceAdvice_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.OA_RemittanceAdvice trueModel = model as Model.OA_RemittanceAdvice;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                new SqlParameter("@Keyid",trueModel.Keyid)
            };
            try
            {
                _dataBase.Query("sp_OA_RemittanceAdvice_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.OA_RemittanceAdvice> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
        {
            throw new NotImplementedException();
        }
        /// <summary>
        /// 单个查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_RemittanceAdvice> SelectAllModel(Infrastructure.Query.Query query)
        {
            throw new NotImplementedException();
        }
        /// <summary>
        /// 全部汇款信息
        /// </summary>
        /// <param name="pa"></param>
        /// <param name="RemittanceFirmId"></param>
        /// <param name="ReceivablesFirm"></param>
        /// <param name="BankId"></param>
        /// <param name="TrandingStatus"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_RemittanceAdvice> SelectModelList(Infrastructure.Query.Pagination pa, Guid RemittanceFirmId, string ReceivablesFirm, string BankId, string TrandingStatus)
        {

            string condition = " where RemittanceFirmId='" + RemittanceFirmId + "' ";
            
            if (!string.IsNullOrEmpty(ReceivablesFirm))
            {
                condition += " and   ReceivablesFirm = '"+ ReceivablesFirm + "'";
            }
            if (!string.IsNullOrEmpty(BankId))
            {
                condition += "  and  RemittanceBankId='" + BankId + "'";
            }
          if(!string.IsNullOrEmpty(TrandingStatus))
          {
              condition += " and TrandingStatus='" + TrandingStatus + "'  ";
          }

          string selTarget = " a.*,b.Name TransactionsName,c.Name  TradingChannelsName,d.Name as RemittanceBankName,e.Name as TrandingStatusName  ,g.Name as ReceiveFirmName , h.Name as RemittanceFirmName";
          string fromSource = " (select * from  dbo.OA_RemittanceAdvice" + condition + " )as  a left join dbo.EC_MemberBasic as h on a.RemittanceFirmId=h.MemberId  left join dbo.EC_MemberBasic as g on a.ReceivablesFirm=g.MemberId  left join dbo.Sys_Dictionary as b on a.TransactionsId=b.Keyid left join Sys_Dictionary as c on a.TradingChannelsId=c.Keyid left join Sys_Dictionary as d on a.RemittanceBankId = d.keyid left join Sys_Dictionary as e on( a.TrandingStatus=e.MeanValue and e.DicType='交易状态')";
          return _dataBase.SelectModelPage<Model.OA_RemittanceAdvice>(pa, selTarget, fromSource, "a.PublishTime DESC");
        }
        public IEnumerable<Model.OA_RemittanceAdvice> SelectALLModelList(Infrastructure.Query.Pagination pa, Guid ReceivablesFirm, string RemittanceFirmId, string BankId, string TrandingStatus, DateTime? beginTime, DateTime? endTime)
        {

            string condition = " where ReceivablesFirm='" + ReceivablesFirm + "' ";
            if (beginTime.HasValue)
            {
                condition += string.Format(" and CAST(TrandingTime AS DATE) >='{0}'", beginTime);
            }

            if (endTime.HasValue)
            {
                condition += string.Format(" and CAST(TrandingTime AS DATE) <='{0}'", endTime);
            }
            if (!string.IsNullOrEmpty(BankId))
            {
                condition += "  and  RemittanceBankId='" + BankId + "'";
            }
            if (!string.IsNullOrEmpty(TrandingStatus))
            {
                condition += " and TrandingStatus='" + TrandingStatus + "'  ";
            }
            //if (beginTime.HasValue)
            //{
            //    condition += string.Format(" and CAST(TrandingTime AS DATE) >='{0}'", beginTime);
            //}

            //if (endTime.HasValue)
            //{
            //    condition += string.Format(" and CAST(TrandingTime AS DATE) <='{0}'", endTime);
            //}

            string selTarget = " a.*,b.Name TransactionsName,c.Name  TradingChannelsName,d.Name as RemittanceBankName,e.Name as TrandingStatusName  ,g.Name as ReceiveFirmName , h.Name as RemittanceFirmName";
            string fromSource = " (select * from  dbo.OA_RemittanceAdvice" + condition + " )as  a left join dbo.EC_MemberBasic as h on (a.RemittanceFirmId=h.MemberId)  left join dbo.EC_MemberBasic as g on a.ReceivablesFirm=g.MemberId  left join dbo.Sys_Dictionary as b on a.TransactionsId=b.Keyid left join Sys_Dictionary as c on a.TradingChannelsId=c.Keyid left join Sys_Dictionary as d on a.RemittanceBankId = d.keyid left join Sys_Dictionary as e on( a.TrandingStatus=e.MeanValue and e.DicType='交易状态')";
            fromSource += "where h.name like '%" + RemittanceFirmId + "%'  ";
            return _dataBase.SelectModelPage<Model.OA_RemittanceAdvice>(pa, selTarget, fromSource, "a.PublishTime DESC");
        }
        /// <summary>
        /// 产寻单条汇款信息
        /// </summary>
        /// <param name="Keyid"></param>
        /// <returns></returns>
        public Model.OA_RemittanceAdvice SelectSingleModel(string Keyid)
        {
            string condition = " where Keyid='"+Keyid+"' ";
            string selTarget = " a.*,b.Name TransactionsName,c.Name  TradingChannelsName,d.Name as RemittanceBankName,e.Name as TrandingStatusName  ,g.Name as ReceiveFirmName , h.Name as RemittanceFirmName";
            string fromSource = " (select * from  dbo.OA_RemittanceAdvice" + condition + " )as  a left join dbo.EC_MemberBasic as h on a.RemittanceFirmId=h.MemberId  left join dbo.EC_MemberBasic as g on a.ReceivablesFirm=g.MemberId  left join dbo.Sys_Dictionary as b on a.TransactionsId=b.Keyid left join Sys_Dictionary as c on a.TradingChannelsId=c.Keyid left join Sys_Dictionary as d on a.RemittanceBankId = d.keyid left join Sys_Dictionary as e on( a.TrandingStatus=e.MeanValue and e.DicType='交易状态')";
            return _dataBase.SelectModel<Model.OA_RemittanceAdvice>(selTarget, fromSource)[0];
        }


    }
}