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

namespace CY.SQLDAL
{
    public class OA_ProcurementDAL : IOA_ProcurementDAL
    {

        private Database _dataBase = null;

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

        public OA_ProcurementDAL(Database dDatabase)
        {
            _dataBase = dDatabase;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_Procurement trueModel = model as Model.OA_Procurement;
            if (trueModel == null)
            {
                return false;
            }
            SqlParameter SuppliersId = null;
            if (trueModel.SuppliersId.HasValue)
            {
                SuppliersId = new SqlParameter("@SuppliersId", trueModel.SuppliersId);
            }
            else
            {
                SuppliersId = new SqlParameter("@SuppliersId", DBNull.Value);
            }


            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    new SqlParameter("@FirmId",trueModel.FirmId),
                    SuppliersId,
					new SqlParameter("@GoodsId",trueModel.GoodsId),
					new SqlParameter("@ClearingStatusId",trueModel.ClearingStatusId),
					new SqlParameter("@Price",trueModel.Price),
					new SqlParameter("@Quantity",trueModel.Quantity),
					new SqlParameter("@AllMoney",trueModel.AllMoney),
					new SqlParameter("@PurchaseTime",trueModel.PurchaseTime),
					new SqlParameter("@PurchaseStatusId",trueModel.PurchaseStatusId),
					new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
					new SqlParameter("@Operator",trueModel.Operator),
					new SqlParameter("@Remark",trueModel.Remark) ,
                    new SqlParameter("@SpecificationId",trueModel.SpecificationId),
                    new SqlParameter("@BrandId",trueModel.BrandId),
                    new SqlParameter("@PaperWeightId",trueModel.PaperWeightId),
            	    new SqlParameter("@tanPrice",trueModel.TanPrice??0),
                    new SqlParameter("@ReceiveMoney",decimal.Parse("0")),
                    new SqlParameter("@Commodityid",trueModel.Commodityid),
                     new SqlParameter("@orderId",trueModel.orderId.HasValue?trueModel.orderId.Value:0),
                      new SqlParameter("@DocumentName",string.IsNullOrEmpty(trueModel.DocumentName)?"":trueModel.DocumentName)
            };
            try
            {
                _dataBase.Query("sp_OA_Procurement_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_Procurement trueModel = model as Model.OA_Procurement;
            if (trueModel == null)
            {
                return false;
            }

            SqlParameter SuppliersId = null;
            if (trueModel.SuppliersId.HasValue)
            {
                SuppliersId = new SqlParameter("@SuppliersId", trueModel.SuppliersId);
            }
            else
            {
                SuppliersId = new SqlParameter("@SuppliersId", DBNull.Value);
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            { 
                    new SqlParameter("@Keyid",trueModel.Keyid),
                    new SqlParameter("@FirmId",trueModel.FirmId),
                    SuppliersId,
					new SqlParameter("@GoodsId",trueModel.GoodsId),
					new SqlParameter("@ClearingStatusId",trueModel.ClearingStatusId),
					new SqlParameter("@Price",trueModel.Price),
					new SqlParameter("@Quantity",trueModel.Quantity),
					new SqlParameter("@AllMoney",trueModel.AllMoney),
					new SqlParameter("@PurchaseTime",trueModel.PurchaseTime),
					new SqlParameter("@PurchaseStatusId",trueModel.PurchaseStatusId),
					new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
					new SqlParameter("@Operator",trueModel.Operator),
					new SqlParameter("@Remark",trueModel.Remark) ,
                    new SqlParameter("@SpecificationId",trueModel.SpecificationId),
                    new SqlParameter("@BrandId",trueModel.BrandId),
                    new SqlParameter("@PaperWeightId",trueModel.PaperWeightId),
            	    new SqlParameter("@tanPrice",trueModel.TanPrice??0),
            	    new SqlParameter("@ReceiveMoney",trueModel.ReceiveMoney),
                    new SqlParameter("@Commodityid",trueModel.Commodityid),
                     new SqlParameter("@WarehouseName",string.IsNullOrEmpty(trueModel.WarehouseName)?"":trueModel.WarehouseName),
            };
            try
            {
                _dataBase.Query("sp_OA_Procurement_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_Procurement trueModel = model as Model.OA_Procurement;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                new SqlParameter("@Keyid",trueModel.Keyid)
            };
            try
            {
                _dataBase.Query("sp_OA_Procurement_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_Procurement> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 分页列表
        /// </summary>
        /// <param name="pa"></param>
        /// <param name="FirmId"></param>
        /// <param name="Operator"></param>
        /// <param name="PurchaseStatusId"></param>
        /// <param name="ClearingStatusId"></param>
        /// <param name="SuppliersId"></param>
        /// <param name="GoodsId"></param>
        /// <param name="GoodTypeID"></param>
        /// <param name="beginTime"></param>
        /// <param name="endTime"></param>
        /// <param name="BrandName"></param>
        /// <param name="PaperWeight"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_Procurement> getModelList(Pagination pa, Guid FirmId, string Operator, string PurchaseStatusId, string ClearingStatusId, string SuppliersId, string GoodsId, string GoodTypeID, DateTime? beginTime, DateTime? endTime, string BrandName, string PaperWeight)
        {
            string Condition = " where  FirmId='" + FirmId + "'";
            if (beginTime.HasValue)
            {
                Condition += string.Format(" and CAST(PurchaseTime AS DATE) >='{0}'", beginTime);
            }

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

            if (!string.IsNullOrEmpty(PurchaseStatusId))
            {
                Condition += string.Format(" and  PurchaseStatusId='{0}'", PurchaseStatusId);
            }
            if (!string.IsNullOrEmpty(Operator))
            {
                Condition += string.Format(" and  Operator='{0}'", Operator);
            }
            if (!string.IsNullOrEmpty(ClearingStatusId))
            {
                Condition += string.Format(" and  ClearingStatusId='{0}'", ClearingStatusId);
            }
            if (!string.IsNullOrEmpty(SuppliersId))
            {
                Condition += string.Format(" and  SuppliersId='{0}'", SuppliersId);
            }
            if (!string.IsNullOrEmpty(GoodsId))
            {
                Condition += string.Format(" and  GoodsId='{0}'", GoodsId);
            }
            string Condition2 = " where 1=1  ";
            if (!string.IsNullOrEmpty(GoodTypeID))
            {
                Condition2 += string.Format(" and b.GoodsTypeId='{0}'", GoodTypeID);
            }
            string BandCondition = string.Empty;
            if (!string.IsNullOrEmpty(BrandName))
            {
                BandCondition += string.Format(" and  h.Name like '%{0}%' ", BrandName);
            }
            string PaperWeightCondition = string.Empty;
            if (!string.IsNullOrEmpty(PaperWeight))
            {
                PaperWeightCondition += string.Format(" and  l.Name like '%{0}%' ", PaperWeight);
            }

            string selTarget = " a.*,b.GoodsName as GoodsName ,c.Name as ClearingStatus  ,d.Name as PurchaseStatus,e.Name as SuppliersName,   g.GoodsTypeId as GoodsTypeId    ,h.Name AS BrandName ,j.Name AS SpeceicationName ,l.Name AS PaperWeightName";

            string fromSource = "  (select * from OA_Procurement  " + Condition + " ) AS a left join OA_GoodsInfo  AS b on  a.GoodsId=b.Keyid left join sys_Dictionary AS c on a.ClearingStatusId=c.keyid left join sys_Dictionary AS d on a.PurchaseStatusId=d.keyid  LEFT JOIN dbo.OA_Brand  AS h ON a.BrandId=h.Keyid LEFT JOIN dbo.OA_Specification  AS j ON a.SpecificationId=j.Keyid LEFT JOIN dbo.OA_PaperWeight AS l ON a.PaperWeightId = l.Keyid  left join dbo.OA_Suppliers AS e on a.SuppliersId=e.Keyid  left join OA_GoodsInfo as g on a.GoodsId=g.Keyid  " + Condition2 + " " + BandCondition + " " + PaperWeightCondition + " ";
            return _dataBase.SelectModelPage<Model.OA_Procurement>(pa, selTarget, fromSource, " a.LastUpdateTime DESC ");

        }

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

        /// <summary>
        /// 获取单条数据
        /// </summary>
        /// <param name="Keyid"></param>
        /// <returns></returns>
        public Model.OA_Procurement SelectAllModel(string Keyid)
        {
            try
            {
                string selTarget = " a.*,b.GoodsName as GoodsName ,c.Name as ClearingStatus  ,d.Name as PurchaseStatus,e.Name as SuppliersName,   g.GoodsTypeId as GoodsTypeId ,h.Name AS BrandName,j.Name AS SpeceicationName,L.Name AS PaperWeightName ";

                string fromSource = "  (select * from OA_Procurement  where Keyid='" + Keyid + "') AS a left join OA_GoodsInfo  AS b on  a.GoodsId=b.Keyid left join sys_Dictionary AS c on a.ClearingStatusId=c.keyid left join sys_Dictionary AS d on a.PurchaseStatusId=d.keyid  left join dbo.OA_Suppliers AS e on a.SuppliersId=e.Keyid  left join OA_GoodsInfo as g on a.GoodsId=g.Keyid  LEFT JOIN dbo.OA_Brand AS h ON a.BrandId= h.Keyid LEFT JOIN  dbo.OA_Specification AS j ON a.SpecificationId= j.Keyid LEFT JOIN  dbo.OA_PaperWeight AS L ON a.PaperWeightId =L.Keyid";

                return _dataBase.SelectModel<Model.OA_Procurement>(selTarget, fromSource)[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 金额统计
        /// </summary>
        /// <param name="pa"></param>
        /// <param name="FirmId"></param>
        /// <param name="Operator"></param>
        /// <param name="PurchaseStatusId"></param>
        /// <param name="ClearingStatusId"></param>
        /// <param name="SuppliersId"></param>
        /// <param name="GoodsId"></param>
        /// <param name="GoodTypeID"></param>
        /// <param name="beginTime"></param>
        /// <param name="endTime"></param>
        /// <param name="BrandName"></param>
        /// <param name="PaperWeight"></param>
        /// <returns></returns>
        public Model.OA_Procurement SumMoney(Pagination pa, Guid FirmId, string Operator, string PurchaseStatusId, string ClearingStatusId, string SuppliersId, string GoodsId, string GoodTypeID, DateTime? beginTime, DateTime? endTime, string BrandName, string PaperWeight)
        {
            try
            {

                string Condition = " where  FirmId='" + FirmId + "'";
                if (beginTime.HasValue)
                {
                    Condition += string.Format(" and CAST(PurchaseTime AS DATE) >='{0}'", beginTime);
                }

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

                if (!string.IsNullOrEmpty(PurchaseStatusId))
                {
                    Condition += string.Format(" and  PurchaseStatusId='{0}'", PurchaseStatusId);
                }
                if (!string.IsNullOrEmpty(Operator))
                {
                    Condition += string.Format(" and  Operator='{0}'", Operator);
                }
                if (!string.IsNullOrEmpty(ClearingStatusId))
                {
                    Condition += string.Format(" and  ClearingStatusId='{0}'", ClearingStatusId);
                }
                if (!string.IsNullOrEmpty(SuppliersId))
                {
                    Condition += string.Format(" and  SuppliersId='{0}'", SuppliersId);
                }
                if (!string.IsNullOrEmpty(GoodsId))
                {
                    Condition += string.Format(" and  GoodsId='{0}'", GoodsId);
                }
                string Condition2 = " where 1=1  ";
                if (!string.IsNullOrEmpty(GoodTypeID))
                {
                    Condition2 += string.Format(" and b.GoodsTypeId='{0}'", GoodTypeID);
                }
                string BandCondition = string.Empty;
                if (!string.IsNullOrEmpty(BrandName))
                {
                    BandCondition += string.Format(" and  h.Name like '%{0}%' ", BrandName);
                }
                string PaperWeightCondition = string.Empty;
                if (!string.IsNullOrEmpty(PaperWeight))
                {
                    PaperWeightCondition += string.Format(" and  l.Name like '%{0}%' ", PaperWeight);
                }

                string selTarget = " sum(AllMoney) as AllMoney,sum(ReceiveMoney) as ReceiveMoney";

                string fromSource = "  (select * from OA_Procurement  " + Condition + " ) AS a left join OA_GoodsInfo  AS b on  a.GoodsId=b.Keyid left join sys_Dictionary AS c on a.ClearingStatusId=c.keyid left join sys_Dictionary AS d on a.PurchaseStatusId=d.keyid  LEFT JOIN dbo.OA_Brand  AS h ON a.BrandId=h.Keyid LEFT JOIN dbo.OA_Specification  AS j ON a.SpecificationId=j.Keyid LEFT JOIN dbo.OA_PaperWeight AS l ON a.PaperWeightId = l.Keyid  left join dbo.OA_Suppliers AS e on a.SuppliersId=e.Keyid  left join OA_GoodsInfo as g on a.GoodsId=g.Keyid  " + Condition2 + " " + BandCondition + " " + PaperWeightCondition + " ";
                IList<Model.OA_Procurement> m_OA_ProcurementList = _dataBase.SelectModel<Model.OA_Procurement>(selTarget, fromSource, "");
                if (m_OA_ProcurementList != null && m_OA_ProcurementList.Count > 0)
                {
                    return m_OA_ProcurementList[0];
                }
                else
                {
                    return new Model.OA_Procurement();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 分页列表
        /// </summary>
        /// <param name="pa"></param>
        /// <param name="FirmId"></param>
        /// <param name="Keyids"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_Procurement> getModelList(Pagination pa, Guid FirmId, string Keyids)
        {
            string Condition = " where  FirmId='" + FirmId + "'";


            if (!string.IsNullOrEmpty(Keyids))
            {
                Condition += string.Format(" and  Keyid  in ({0}) ", Keyids);
            }


            string selTarget = " a.*,b.GoodsName as GoodsName ,c.Name as ClearingStatus  ,d.Name as PurchaseStatus,e.Name as SuppliersName,   g.GoodsTypeId as GoodsTypeId    ,h.Name AS BrandName ,j.Name AS SpeceicationName ,l.Name AS PaperWeightName";

            string fromSource = "  (select * from OA_Procurement  " + Condition + " ) AS a left join OA_GoodsInfo  AS b on  a.GoodsId=b.Keyid left join sys_Dictionary AS c on a.ClearingStatusId=c.keyid left join sys_Dictionary AS d on a.PurchaseStatusId=d.keyid  LEFT JOIN dbo.OA_Brand  AS h ON a.BrandId=h.Keyid LEFT JOIN dbo.OA_Specification  AS j ON a.SpecificationId=j.Keyid LEFT JOIN dbo.OA_PaperWeight AS l ON a.PaperWeightId = l.Keyid  left join dbo.OA_Suppliers AS e on a.SuppliersId=e.Keyid  left join OA_GoodsInfo as g on a.GoodsId=g.Keyid  ";
            return _dataBase.SelectModelPage<Model.OA_Procurement>(pa, selTarget, fromSource, " a.LastUpdateTime DESC ");

        }
    }
}