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

namespace CY.SQLDAL
{
    /// <summary>
    /// 品牌相关操作--SQL数据库操作
    /// </summary>
    public class BrandInfoDAL : IBrandInfoDAL
    {
        private Database _dataBase = null;

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

        public BrandInfoDAL(Database dataBase)
        {
            _dataBase = dataBase;
        }

        /// <summary>
        /// 新增品牌
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.SysInquiry_BrandInfo trueModel = model as Model.SysInquiry_BrandInfo;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                	new SqlParameter("@BrandName",trueModel.BrandName),
					new SqlParameter("@Status",trueModel.Status),
                    new SqlParameter("@OrderNum",trueModel.OrderNum),
                    new SqlParameter(){ParameterName="@KeyId", SqlDbType=SqlDbType.Int,Direction=ParameterDirection.Output}
            };
            try
            {
                _dataBase.Query("SysInquiry_BrandInfo_ADD", 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.SysInquiry_BrandInfo trueModel = model as Model.SysInquiry_BrandInfo;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                	new SqlParameter("@BrandName",trueModel.BrandName),
					new SqlParameter("@Status",trueModel.Status),
                    new SqlParameter("@KeyId",trueModel.KeyId),
                    new SqlParameter("@OrderNum",trueModel.OrderNum)
            };
            try
            {
                _dataBase.Query("SysInquiry_BrandInfo_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

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

        /// <summary>
        /// 分页返回品牌列表
        /// </summary>
        /// <param name="brandName">品牌名称</param>
        /// <param name="pagination">分页参数</param>
        /// <returns></returns>
        public IEnumerable<Model.SysInquiry_BrandInfo> SelectModelPage(int paperTypeId, int paperId, string brandName, int status, Infrastructure.Query.Pagination pagination)
        {
            //string condition=string.Empty;
            //condition = " 1=1 ";
            //if(!string.IsNullOrEmpty(brandName))
            //{
            //    condition +=" and BrandName like '%"+brandName+"%' ";
            //}
            //if (status != -1)
            //{
            //    condition += " and Status="+status;
            //}
            //return _dataBase.SelectModelPage<SysInquiry_BrandInfo>(pagination, "*", "SysInquiry_BrandInfo", "OrderNum", "OrderNum", condition);
            string selectTarget = @" t.* ";
            string fromSouce = @" ( select  distinct a.*,c.PaperName,e.OrderNum as paperTypeOrderNum,c.OrderNum AS PaperOrderNum,
                                    CASE 
                                    WHEN d.KeyId IS NULL THEN 0
                                    ELSE 1
                                    END AS IsDefaultBrand
                                from 
                                dbo.SysInquiry_BrandInfo a LEFT JOIN dbo.SysInquiry_BrandListByPaper b ON a.KeyId=b.BrandId
                                LEFT JOIN dbo.SysInquiry_PaperInfo c ON b.PaperId=c.PapeId and c.status='true'
                                LEFT JOIN dbo.SysInquiry_PaperInfo d ON a.KeyId=d.DefaultBrandId 
                                left join SysInquiry_PaperType e on e.KeyId=c.PaperTypeId
                                where 1=1 ";

            if (!string.IsNullOrEmpty(brandName))
            {
                fromSouce += " and a.BrandName like '%" + brandName + "%' ";
            }
            if (status != -1)
            {
                fromSouce += " and a.Status=" + status;
            }
            if (paperTypeId != -1)
            {
                fromSouce += " and c.PaperTypeId=" + paperTypeId;
            }

            if (paperId != -1)
            {
                fromSouce += " and b.PaperId=" + paperId;
            }
            fromSouce += " ) as t";
            IEnumerable<Model.SysInquiry_BrandInfo> list = _dataBase.SelectModelPage<SysInquiry_BrandInfo>(pagination, selectTarget, fromSouce, "t.paperTypeOrderNum,t.PaperOrderNum,t.OrderNum", "paperTypeOrderNum,PaperOrderNum,OrderNum");
            return list;
        }

        /// <summary>
        /// 根据主键返回品牌实体
        /// </summary>
        /// <param name="keyid">主键ID</param>
        /// <returns>品牌实体</returns>
        public SysInquiry_BrandInfo SelectModelByKey(int keyid)
        {
            //string condition=string.Empty;
            //condition=" KeyId="+keyid;
            //IList<SysInquiry_BrandInfo> result = _dataBase.SelectModel<SysInquiry_BrandInfo>("*", "SysInquiry_BrandInfo", condition);
            //return null == result || result.Count == 0 ? null : result[0];

            string selectTarget = @"a.*,c.PaperName,c.PaperTypeId,c.PapeId,
                                    CASE 
                                    WHEN d.KeyId IS NULL THEN 0
                                    ELSE 1
                                    END AS IsDefaultBrand";
            string fromSouce = @" dbo.SysInquiry_BrandInfo a LEFT JOIN dbo.SysInquiry_BrandListByPaper b ON a.KeyId=b.BrandId
                                LEFT JOIN dbo.SysInquiry_PaperInfo c ON b.PaperId=c.PapeId 
                                LEFT JOIN dbo.SysInquiry_PaperInfo d ON a.KeyId=d.DefaultBrandId ";
            string condition = string.Empty;
            condition = " a.KeyId=" + keyid;
            IList<SysInquiry_BrandInfo> result = _dataBase.SelectModel<SysInquiry_BrandInfo>(selectTarget, fromSouce, condition);
            return null == result || result.Count == 0 ? null : result[0];
        }

        /// <summary>
        /// 判断是否有相同的品牌名称
        /// </summary>
        /// <param name="brandName"></param>
        /// <returns></returns>
        public bool IsExistsBrandName(string brandName, int PaperId)
        {
            string condition = string.Empty;
            condition = " a.BrandName='" + brandName + "' and b.PaperId='" + PaperId + "'";
            IList<SysInquiry_BrandInfo> result = _dataBase.SelectModel<SysInquiry_BrandInfo>(" a.* ", "SysInquiry_BrandInfo as a left join SysInquiry_BrandListByPaper as b on a.KeyId = b.BrandId", condition);
            if (result != null && result.Count != 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 批量删除品牌
        /// </summary>
        /// <param name="keyIds"></param>
        /// <returns></returns>
        public bool DeleteBrandInfoList(List<int> keyIds)
        {
            bool isSuccess = true;
            using (TransactionScope scope = new TransactionScope())
            {
                foreach (int keyId in keyIds)
                {
                    SysInquiry_BrandInfo model = SelectModelByKey(keyId);
                    isSuccess = DeleteModel(model);
                    if (!isSuccess)
                        break;
                }
                if (isSuccess)
                    scope.Complete();
            }
            return isSuccess;
        }

        /// <summary>
        /// 返回所有有效的品牌列表
        /// </summary>
        /// <returns></returns>
        public IEnumerable<SysInquiry_BrandInfo> GetBrandList()
        {
            string condition = string.Empty;
            condition = " Status=1 order by OrderNum ";
            IList<SysInquiry_BrandInfo> result = _dataBase.SelectModel<SysInquiry_BrandInfo>("*", "SysInquiry_BrandInfo", condition);
            return result;
        }

        public bool InsertBrandModel(SysInquiry_BrandInfo model, SysInquiry_BrandListByPaper brandPaper, SysInquiry_PaperInfo paperModel)
        {
            bool isSuccess = true;
            PaperInfoDAL _paperInfoDAL = new PaperInfoDAL(_dataBase);
            using (TransactionScope scope = new TransactionScope())
            {
                if (model == null)
                {
                    isSuccess = false;
                }
                else
                {
                    IList<SqlParameter> sqlParms = new List<SqlParameter>()
                    {
                	        new SqlParameter("@BrandName",model.BrandName),
					        new SqlParameter("@Status",model.Status),
                            new SqlParameter("@OrderNum",model.OrderNum),
                            new SqlParameter(){ParameterName="@KeyId", SqlDbType=SqlDbType.Int,Direction=ParameterDirection.Output}
                    };
                    try
                    {
                        _dataBase.Query("SysInquiry_BrandInfo_ADD", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
                        model.KeyId = sqlParms[3].Value.ToInt32().Value;
                        brandPaper.BrandId = model.KeyId;
                        if (paperModel != null)
                        {
                            paperModel.DefaultBrandId = brandPaper.BrandId;
                        }
                        isSuccess = true;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                        isSuccess = false;
                    }
                    if (isSuccess)
                    {
                        isSuccess = _paperInfoDAL.InsertBrandByPaper(brandPaper, paperModel);
                    }
                }
                if (isSuccess)
                    scope.Complete();
            }
            return isSuccess;

        }

        public bool UpdateBrandModel(SysInquiry_BrandInfo model, SysInquiry_BrandListByPaper brandPaper, SysInquiry_PaperInfo paperModel, SysInquiry_PaperInfo orPaperModel)
        {
            bool isSuccess = true;
            PaperInfoDAL _paperInfoDAL = new PaperInfoDAL(_dataBase);
            using (TransactionScope scope = new TransactionScope())
            {
                if (model == null)
                {
                    isSuccess = false;
                }
                else
                {
                    IList<SqlParameter> sqlParms = new List<SqlParameter>()
                    {
                	        new SqlParameter("@BrandName",model.BrandName),
					        new SqlParameter("@Status",model.Status),
                            new SqlParameter("@KeyId",model.KeyId),
                            new SqlParameter("@OrderNum",model.OrderNum)
                    };
                    try
                    {
                        _dataBase.Query("SysInquiry_BrandInfo_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
                        isSuccess = true;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    if (isSuccess)
                    {
                        isSuccess = _paperInfoDAL.UpdateBrandByPaper(brandPaper, paperModel, orPaperModel);
                    }
                    if (isSuccess)
                        scope.Complete();
                }
            }
            return isSuccess;
        }

        /// <summary>
        /// 获取最新排序顺序
        /// </summary>
        /// <returns></returns>
        public int GetOrderNumByMax(int paperId)
        {
            int orderNum = 1;
            IList<SysInquiry_BrandInfo> result = _dataBase.SelectModel<SysInquiry_BrandInfo>(" MAX(OrderNum)+1 AS OrderNum ", "dbo.SysInquiry_BrandInfo a LEFT JOIN dbo.SysInquiry_BrandListByPaper b ON a.KeyId=b.BrandId", " b.PaperId=" + paperId);
            if (result != null && result.Count > 0)
            {
                if (result[0].OrderNum.HasValue)
                {
                    orderNum = result[0].OrderNum.Value;
                }
                else
                {
                    orderNum = 1;
                }
            }
            return orderNum;
        }
    }
}