using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CY.IDAL.Inquiry;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
using System.Data.SqlTypes;
using CY.Model;
using CY.Infrastructure.Query;
using System.Transactions;

namespace CY.SQLDAL
{
    /// <summary>
    /// 询价记录操作接口--SQL实现
    /// </summary>
    public class RecordInfoDAL : IRecordInfoDAL
    {
        private Database _dataBase = null;

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

        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.Inquiry_RecordInfo trueModel = model as Model.Inquiry_RecordInfo;
            if (trueModel == null)
            {
                return false;
            }

            using (XmlTextReader rdr = new XmlTextReader(trueModel.PrintParameter, XmlNodeType.Document, null))
            {
              
                SqlXml sqlXml = new SqlXml(rdr); 
                SqlParameter printPar=new SqlParameter("@PrintParameter", SqlDbType.Xml, sqlXml.Value.Length);
                printPar.Value = sqlXml;
                SqlParameter[] parameters = {
					new SqlParameter("@InquiryPersonId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@FirmId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@PrintTypeId", SqlDbType.Int,4),
					new SqlParameter("@PrintCount", SqlDbType.Int,4),
					new SqlParameter("@SysPrice", SqlDbType.Money,8),
					new SqlParameter("@ResultStatus", SqlDbType.Int,4),
					new SqlParameter("@OperateTime", SqlDbType.DateTime),
                    new SqlParameter("@PersonTypeId", SqlDbType.Int,4),
                    new SqlParameter("@FirmPrice",DBNull.Value),
                    printPar
                };
                parameters[0].Value = trueModel.InquiryPersonId;
                parameters[1].Value = trueModel.FirmId;
                parameters[2].Value = trueModel.PrintTypeId;
                parameters[3].Value = trueModel.PrintCount;
                parameters[4].Value = trueModel.SysPrice;
                parameters[5].Value = trueModel.ResultStatus;
                parameters[6].Value = trueModel.OperateTime;
                parameters[7].Value = trueModel.PersonTypeId;
                try
                {
                    _dataBase.Query("Inquiry_RecordInfo_ADD", CommandType.StoredProcedure, parameters);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return true;
        }

        public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.Inquiry_RecordInfo trueModel = model as Model.Inquiry_RecordInfo;
            if (trueModel == null)
            {
                return false;
            }
            SqlParameter firmPricePar = null;
            if (!trueModel.FirmPrice.HasValue)
            {
                firmPricePar = new SqlParameter("@FirmPrice", DBNull.Value);
            }
            else
            {
                firmPricePar = new SqlParameter("@FirmPrice", trueModel.FirmPrice.Value);
            }
            SqlParameter customerPricePar = null;
            if (!trueModel.CustomerPrice.HasValue)
            {
                customerPricePar = new SqlParameter("@CustomerPrice", DBNull.Value);
            }
            else
            {
                customerPricePar = new SqlParameter("@CustomerPrice", trueModel.CustomerPrice.Value);
            }
            SqlParameter lastPricePar = null;
            if (!trueModel.LastPrice.HasValue)
            {
                lastPricePar = new SqlParameter("@LastPrice", DBNull.Value);
            }
            else
            {
                lastPricePar = new SqlParameter("@LastPrice", trueModel.LastPrice.Value);
            }
            SqlParameter resultTimePar = null;
            if (!trueModel.ResultTime.HasValue)
            {
                resultTimePar = new SqlParameter("@ResultTime", DBNull.Value);
            }
            else
            {
                resultTimePar = new SqlParameter("@ResultTime", trueModel.ResultTime.Value);
            }
            SqlParameter orderLinkPar = null;
            if (string.IsNullOrEmpty(trueModel.OrderLink))
            {
                orderLinkPar = new SqlParameter("@OrderLink", DBNull.Value);
            }
            else
            {
                orderLinkPar = new SqlParameter("@OrderLink", trueModel.OrderLink);
            }
            SqlParameter[] parameters = {
					new SqlParameter("@KeyId", SqlDbType.Int,4),
					new SqlParameter("@ResultStatus", SqlDbType.Int,4),
					new SqlParameter("@OperateTime", SqlDbType.DateTime),
                    firmPricePar,
                    customerPricePar,
                    lastPricePar,
                    resultTimePar,
                    orderLinkPar
                };
            parameters[0].Value = trueModel.KeyId;
            parameters[1].Value = trueModel.ResultStatus;
            parameters[2].Value = trueModel.OperateTime;
            try
            {
                _dataBase.Query("Inquiry_RecordInfo_Update", CommandType.StoredProcedure, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.Inquiry_RecordInfo trueModel = model as Model.Inquiry_RecordInfo;
            if (trueModel == null)
            {
                return false;
            }
            SqlParameter[] parameters = {
					new SqlParameter("@KeyId", SqlDbType.Int,4)
                };
            parameters[0].Value = trueModel.KeyId;
            try
            {
                _dataBase.Query("Inquiry_RecordInfo_Delete", CommandType.StoredProcedure, parameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 批量删除记录
        /// </summary>
        /// <param name="keyIdList"></param>
        /// <returns></returns>
        public bool DeleteModelList(List<int> keyIdList)
        {
            if (keyIdList == null || keyIdList.Count == 0)
            {
                return true;
            }
            bool isSuccess = true;
            using (TransactionScope scope = new TransactionScope())
            {
                foreach (int keyId in keyIdList)
                {
                    Inquiry_RecordInfo model = GetModel(keyId);
                    isSuccess = DeleteModel(model);
                    if (!isSuccess)
                        break;
                }
                if (isSuccess)
                    scope.Complete();
            }
            return isSuccess;
        }

        public Model.Inquiry_RecordInfo GetModel(int key)
        {
            string condition = string.Empty;
            condition = " KeyId=" + key;
            IList<Inquiry_RecordInfo> result = _dataBase.SelectModel<Inquiry_RecordInfo>("*", "Inquiry_RecordInfo", condition);
            return null == result || result.Count == 0 ? null : result[0];
        }


        public IList<Inquiry_RecordInfo> GetModeListByMy(Guid inquiryPersonId, DateTime startTime, DateTime endTime, int printTypeId, string firmName, int resultStatus, Pagination pa)
        {
            string selectTarget = " a.*,b.Name AS FirmName,c.PrintName AS PrintTypeName,d.Name as ResultStatusStr ";
            string fromSouce = " Inquiry_RecordInfo a INNER JOIN dbo.EC_MemberBasic b ON a.FirmId=b.MemberId INNER JOIN dbo.SysInquiry_PrintingType c ON a.printtypeId=c.KeyId inner join Sys_Dictionary d on a.resultStatus=d.meanvalue and d.DicType='成交状态' where a.InquiryPersonId='" + inquiryPersonId.ToString() + "' ";
            if (startTime != DateTime.MinValue && endTime == DateTime.MaxValue)
            {
                fromSouce += " and CAST(a.OperateTime AS DATE) >='" + startTime + "'";
            }
            else if (startTime == DateTime.MinValue && endTime != DateTime.MaxValue)
            {
                fromSouce += " and CAST(a.OperateTime AS DATE) <='" + endTime + "'";
            }
            else if (startTime != DateTime.MinValue && endTime != DateTime.MaxValue)
            {
                fromSouce += " and CAST(a.OperateTime AS DATE) >='" + startTime + "' and CAST(a.OperateTime AS DATE) <='" + endTime + "'";
            }

            if (printTypeId != -1)
            {
                fromSouce += " and a.printTypeId=" + printTypeId;
            }

            if (!string.IsNullOrEmpty(firmName))
            {
                fromSouce += " and b.Name like '%"+firmName+"%'";
            }
            if (resultStatus!=-1)
            {
                fromSouce += " and a.resultStatus='" + resultStatus+"'";
            }
            return _dataBase.SelectModelPage<Model.Inquiry_RecordInfo>(pa, selectTarget, fromSouce, "a.OperateTime desc ", "OperateTime desc ", string.Empty);
        }

        public IList<Inquiry_RecordInfo> GetModeListByFirm(Guid firmId, DateTime startTime, DateTime endTime, int printTypeId, string customerName, int resultStatus, Pagination pa)
        {
            string selectTarget = @" T.KeyId ,T.InquiryPersonId ,T.FirmId,T.PrintTypeId,T.PrintCount,
                                    T.SysPrice,T.FirmPrice,T.CustomerPrice,
                                    T.LastPrice,T.ResultStatus ,T.ResultStatusStr,T.ResultTime ,T.OrderLink ,T.PersonTypeId,T.FirmName,T.OperateTime,T.PrintTypeName ";
            string fromSouce = @"
                            (

                            SELECT a.KeyId ,a.InquiryPersonId ,a.FirmId,a.PrintTypeId,a.PrintCount,
                                    a.SysPrice,a.FirmPrice,a.CustomerPrice,
                                    a.LastPrice,a.ResultStatus ,a.ResultTime ,a.OrderLink ,a.OperateTime,a.PersonTypeId,
                                    b.Name AS FirmName,c.PrintName AS PrintTypeName,d.Name as ResultStatusStr
                            FROM Inquiry_RecordInfo a
                            INNER JOIN dbo.EC_MemberBasic b 
                            ON a.InquiryPersonId=b.MemberId AND a.PersonTypeId=1
                            INNER JOIN dbo.SysInquiry_PrintingType c ON a.printtypeId=c.KeyId
                            inner join Sys_Dictionary d on a.resultStatus=d.meanvalue and d.DicType='成交状态' 
                            where a.FirmId='{0}'
                            UNION
                            SELECT a.KeyId ,a.InquiryPersonId ,a.FirmId,a.PrintTypeId,a.PrintCount,
                                    a.SysPrice,a.FirmPrice,a.CustomerPrice,
                                    a.LastPrice,a.ResultStatus ,a.ResultTime ,a.OrderLink ,a.OperateTime,a.PersonTypeId,
                                    b.CompanyName AS FirmName,c.PrintName AS PrintTypeName,d.Name as ResultStatusStr
                            FROM Inquiry_RecordInfo a
                            INNER JOIN dbo.OA_CorporateClients b 
                            ON a.InquiryPersonId=b.Keyid AND a.PersonTypeId=2
                            INNER JOIN dbo.SysInquiry_PrintingType c ON a.printtypeId=c.KeyId 
                            inner join Sys_Dictionary d on a.resultStatus=d.meanvalue and d.DicType='成交状态' 
                            where a.FirmId='{0}'
                            UNION
                            SELECT a.KeyId ,a.InquiryPersonId ,a.FirmId,a.PrintTypeId,a.PrintCount,
                                    a.SysPrice,a.FirmPrice,a.CustomerPrice,
                                    a.LastPrice,a.ResultStatus ,a.ResultTime ,a.OrderLink ,a.OperateTime,a.PersonTypeId,
                                    CASE
                                    WHEN b.KeyId IS NOT NULL THEN b.CustomerName+'(陌生客户)'  
                                    ELSE '陌生客户'
                                    END AS FirmName,
                                    c.PrintName AS PrintTypeName,d.Name as ResultStatusStr
                            FROM Inquiry_RecordInfo a
                            LEFT JOIN Inquiry_StrangeCustomerInfo b
                            ON a.KeyId=b.RecordId 
                            INNER JOIN dbo.SysInquiry_PrintingType c ON a.printtypeId=c.KeyId
                            inner join Sys_Dictionary d on a.resultStatus=d.meanvalue and d.DicType='成交状态'  
                            WHERE a.PersonTypeId=3 and a.FirmId='{0}'
                            ) T 
                             ";
            fromSouce=string.Format(fromSouce,firmId);
            fromSouce += " where 1=1 ";
            if (startTime != DateTime.MinValue && endTime == DateTime.MaxValue)
            {
                fromSouce += " and CAST(T.OperateTime AS DATE) >='" + startTime + "'";
            }
            else if (startTime == DateTime.MinValue && endTime != DateTime.MaxValue)
            {
                fromSouce += " and CAST(T.OperateTime AS DATE) <='" + endTime + "'";
            }
            else if (startTime != DateTime.MinValue && endTime != DateTime.MaxValue)
            {
                fromSouce += " and CAST(T.OperateTime AS DATE) >='" + startTime + "' and CAST(T.OperateTime AS DATE)<='" + endTime + "'";
            }


            if (printTypeId != -1)
            {
                fromSouce += " and T.printTypeId=" + printTypeId;
            }

            if (!string.IsNullOrEmpty(customerName))
            {
                fromSouce += " and T.FirmName like '%" + customerName + "%'";
            }
            if (resultStatus!=-1)
            {
                fromSouce += " and T.resultStatus='" + resultStatus + "'";
            }
            return _dataBase.SelectModelPage<Model.Inquiry_RecordInfo>(pa, selectTarget, fromSouce, "T.OperateTime desc ", "OperateTime desc", string.Empty);
        }

    }
}