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;
using CY.Model;

namespace CY.SQLDAL
{
    public class OA_OutOfStorageDAL : IOA_OutOfStorageDAL
    {

        private Database _dataBase = null;

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

        public OA_OutOfStorageDAL(Database database)
        {
            _dataBase = database;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_OutOfStorage trueModel = model as Model.OA_OutOfStorage;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                	new SqlParameter("@FirmId",trueModel.FirmId),
					new SqlParameter("@WarehouseId",trueModel.WarehouseId),
					new SqlParameter("@CargoSpaceId",trueModel.CargoSpaceId),
					new SqlParameter("@GoodsId",trueModel.GoodsId),
					new SqlParameter("@InfoType",trueModel.InfoType),
					new SqlParameter("@SuppliersId",trueModel.SuppliersId),
					new SqlParameter("@Price",trueModel.Price),
					new SqlParameter("@Quantity",trueModel.Quantity),
					new SqlParameter("@AllMoney",trueModel.AllMoney),
					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)
            };
            try
            {
                _dataBase.Query("sp_OA_OutOfStorage_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_OutOfStorage trueModel = model as Model.OA_OutOfStorage;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                new SqlParameter("@Keyid",trueModel.Keyid),
                	new SqlParameter("@FirmId",trueModel.FirmId),
					new SqlParameter("@WarehouseId",trueModel.WarehouseId),
					new SqlParameter("@CargoSpaceId",trueModel.CargoSpaceId),
					new SqlParameter("@GoodsId",trueModel.GoodsId),
					new SqlParameter("@InfoType",trueModel.InfoType),
					new SqlParameter("@SuppliersId",trueModel.SuppliersId),
					new SqlParameter("@Price",trueModel.Price),
					new SqlParameter("@Quantity",trueModel.Quantity),
					new SqlParameter("@AllMoney",trueModel.AllMoney),
					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)
            };
            try
            {
                _dataBase.Query("sp_OA_OutOfStorage_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_OutOfStorage trueModel = model as Model.OA_OutOfStorage;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                new SqlParameter("@Keyid",trueModel.Keyid),
            };
            try
            {
                _dataBase.Query("sp_OA_OutOfStorage_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_OutOfStorage> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<Model.OA_OutOfStorage> getModelList(Pagination pa, Guid FirmId, string Operator, string InfoType, string SuppliersId, string GoodsNameId, string WarehouseId, DateTime? beginTime, DateTime? endTime, string BrandName, string SpeceicationName, string PaperWeightName, string GoodsTypeId)
        {
            string Condition = " where  FirmId='" + FirmId + "'";
            if (beginTime.HasValue)
            {
                Condition += string.Format(" and CAST(LastUpdateTime AS DATE) >='{0}'", beginTime);
            }

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

            if (!string.IsNullOrEmpty(Operator))
            {
                Condition += string.Format(" and  Operator='{0}'", Operator);
            }
            if (!string.IsNullOrEmpty(WarehouseId))
            {
                Condition += string.Format(" and  WarehouseId='{0}'", WarehouseId);
            }
            if (!string.IsNullOrEmpty(SuppliersId))
            {
                Condition += string.Format(" and  SuppliersId='{0}'", SuppliersId);
            }
            if (!string.IsNullOrEmpty(InfoType))
            {
                Condition += string.Format(" and  InfoType='{0}'", InfoType);
            }
            if (!string.IsNullOrEmpty(GoodsNameId))
            {
                Condition += string.Format(" and  GoodsId='{0}'", GoodsNameId);
            }
            string Condition2 = " where 1=1 ";

            if (!string.IsNullOrEmpty(BrandName))
            {
                Condition2 += string.Format(" and  f.Keyid ='{0}'", BrandName);
            }
            if (!string.IsNullOrEmpty(SpeceicationName))
            {
                Condition2 += string.Format(" and  j.Keyid='{0}'", SpeceicationName);
            }
            if (!string.IsNullOrEmpty(PaperWeightName))
            {
                Condition2 += string.Format(" and  h.Keyid='{0}'", PaperWeightName);
            }
            if (!string.IsNullOrEmpty(GoodsTypeId))
            {
                Condition2 += string.Format(" and  b.GoodsTypeId='{0}'", GoodsTypeId);
            }

            string selTarget = " a.*,b.GoodsName as GoodsName ,c.WarehouseName ,e.Name as SuppliersName  ,f.Name AS BrandName,j.Name AS SpeceicationName ,h.Name AS PaperWeightName,t.CommodityName AS CommodityName ";
            string fromSource = " (select * from  dbo.OA_OutOfStorage  " + Condition + "  ) AS a left join OA_GoodsInfo  AS b on  a.GoodsId=b.Keyid  left join        dbo.OA_WarehouseInfo as c on  a.WarehouseId=c.KeyId   left join dbo.OA_Suppliers AS e on a.SuppliersId=e.Keyid    LEFT JOIN dbo.OA_Brand  AS f ON a.BrandId = f.Keyid LEFT JOIN dbo.OA_Specification AS j ON a.SpecificationId = j.Keyid LEFT JOIN  dbo.OA_PaperWeight AS h ON a.PaperWeightId =h.Keyid  LEFT JOIN dbo.OA_Commodity AS t ON b.GoodsTypeId=t.Keyid " + Condition2;

            return _dataBase.SelectModelPage<Model.OA_OutOfStorage>(pa, selTarget, fromSource, "  a.LastUpdateTime DESC  ");
        }

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

        /// <summary>
        /// 获取条出入库信息
        /// </summary>
        /// <param name="keyid"></param>
        /// <returns></returns>
        public OA_OutOfStorage GetModelInfo(int? keyid)
        {
            string selTarget = " a.*,b.GoodsName as GoodsName ,c.WarehouseName ,e.Name as SuppliersName  ,f.Name AS BrandName,j.Name AS SpeceicationName ,h.Name AS PaperWeightName,t.CommodityName AS CommodityName";
            string fromSource = " (select * from  dbo.OA_OutOfStorage  where Keyid =" + keyid + "  ) AS a left join OA_GoodsInfo  AS b on  a.GoodsId=b.Keyid  left join        dbo.OA_WarehouseInfo as c on  a.WarehouseId=c.KeyId   left join dbo.OA_Suppliers AS e on a.SuppliersId=e.Keyid    LEFT JOIN dbo.OA_Brand  AS f ON a.BrandId = f.Keyid LEFT JOIN dbo.OA_Specification AS j ON a.SpecificationId = j.Keyid LEFT JOIN  dbo.OA_PaperWeight AS h ON a.PaperWeightId =h.Keyid  LEFT JOIN dbo.OA_Commodity AS t ON b.GoodsTypeId=t.Keyid";
            if (_dataBase.SelectModel<Model.OA_OutOfStorage>(selTarget, fromSource).Count > 0)
            {
                return _dataBase.SelectModel<Model.OA_OutOfStorage>(selTarget, fromSource)[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取单个信息
        /// </summary>
        /// <param name="Keyid"></param>
        /// <returns></returns>
        public OA_OutOfStorage GetModelByKeyid(int? Keyid)
        {
            try
            {
                List<OA_OutOfStorage> m_OA_OutOfStorageList = _dataBase.SelectModel<OA_OutOfStorage>(" * ", " OA_OutOfStorage ", " Keyid='" + Keyid + "'") as List<OA_OutOfStorage>;
                if (m_OA_OutOfStorageList != null && m_OA_OutOfStorageList.Count > 0)
                {
                    return m_OA_OutOfStorageList[0];
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IEnumerable<Model.OA_OutOfStorage> 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.WarehouseName ,e.Name as SuppliersName  ,f.Name AS BrandName,j.Name AS SpeceicationName ,h.Name AS PaperWeightName,t.CommodityName AS CommodityName ";
            string fromSource = " (select * from  dbo.OA_OutOfStorage  " + Condition + "  ) AS a left join OA_GoodsInfo  AS b on  a.GoodsId=b.Keyid  left join        dbo.OA_WarehouseInfo as c on  a.WarehouseId=c.KeyId   left join dbo.OA_Suppliers AS e on a.SuppliersId=e.Keyid    LEFT JOIN dbo.OA_Brand  AS f ON a.BrandId = f.Keyid LEFT JOIN dbo.OA_Specification AS j ON a.SpecificationId = j.Keyid LEFT JOIN  dbo.OA_PaperWeight AS h ON a.PaperWeightId =h.Keyid  LEFT JOIN dbo.OA_Commodity AS t ON b.GoodsTypeId=t.Keyid ";

            return _dataBase.SelectModelPage<Model.OA_OutOfStorage>(pa, selTarget, fromSource, "  a.LastUpdateTime DESC  ");
        }
    }
}