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_CommodityDAL : IOA_CommodityDAL
    {
        private Database _dataBase = null;

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

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

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_Commodity trueModel = model as Model.OA_Commodity;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                new SqlParameter("@FirmId",trueModel.FirmId),
					new SqlParameter("@CommodityName",trueModel.CommodityName),
					new SqlParameter("@IsPaper",trueModel.IsPaper),
					new SqlParameter("@ParentId",trueModel.ParentId),
					new SqlParameter("@IsUsed",trueModel.IsUsed),
					new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
					new SqlParameter("@Operator",trueModel.Operator),
					new SqlParameter("@Remark",trueModel.Remark) 
                    ,new SqlParameter("@OrderNum",trueModel.OrderNum)
            };
            try
            {
                _dataBase.Query("sp_OA_Commodity_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_Commodity trueModel = model as Model.OA_Commodity;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                new SqlParameter("@Keyid",trueModel.Keyid),
					new SqlParameter("@FirmId",trueModel.FirmId),
					new SqlParameter("@CommodityName",trueModel.CommodityName),
					new SqlParameter("@IsPaper",trueModel.IsPaper),
					new SqlParameter("@ParentId",trueModel.ParentId),
					new SqlParameter("@IsUsed",trueModel.IsUsed),
					new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
					new SqlParameter("@Operator",trueModel.Operator),
					new SqlParameter("@Remark",trueModel.Remark)
                    ,new SqlParameter("@OrderNum",trueModel.OrderNum)

            };
            try
            {
                _dataBase.Query("sp_OA_Commodity_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_Commodity trueModel = model as Model.OA_Commodity;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                 new SqlParameter("@Keyid",trueModel.Keyid)
            };
            try
            {
                _dataBase.Query("sp_OA_Commodity_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_Commodity> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 返回一个datatable用于上级类别
        /// </summary>
        /// <returns></returns>
        public DataTable SelectModelPage(Guid FirmId, string IsPaper, string IsUsed)
        {
            string selecTarget = " * ";
            string condition = "  where FirmId='" + FirmId + "' ";
            if (!string.IsNullOrEmpty(IsPaper))
            {
                condition += " and  IsPaper='" + IsPaper + "'";
            }
            if (!string.IsNullOrEmpty(IsUsed))
            {
                condition += " and  IsUsed='" + IsUsed + "'";
            }
            string fromSorce = "  OA_Commodity " + condition;
            return _dataBase.SelectModel(selecTarget, fromSorce);
        }

        /// <summary>
        /// 　通过Firmid和CommodityName返回全部的类别
        /// </summary>
        /// <param name="pa"></param>
        /// <param name="FirmId"></param>
        /// <param name="CommodityName"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_Commodity> SelectModelPage(Pagination pa, Guid FirmId, string CommodityName)
        {
            string Condition = "where FirmId='" + FirmId + "'";
            if (!string.IsNullOrEmpty(CommodityName))
            {
                Condition += "  and  CommodityName like '%" + CommodityName + "%'";
            }
            string selTarget = " a.*,b.CommodityName AS UpCommodeyName";
            string fromSource = "(SELECT * FROM OA_Commodity " + Condition + ") AS a LEFT JOIN dbo.OA_Commodity AS b  ON a.ParentId = b.Keyid  ";
            return _dataBase.SelectModelPage<Model.OA_Commodity>(pa, selTarget, fromSource, "a.OrderNum  ");

        }

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

        public Model.OA_Commodity SelectSingleModel(string Keyid)
        {
            try
            {
                string selTarget = " a.*,b.CommodityName AS UpCommodeyName";
                string fromSource = "(SELECT * FROM OA_Commodity  where Keyid=" + Keyid + ") AS a LEFT JOIN dbo.OA_Commodity AS b  ON a.ParentId = b.Keyid  ";
                if (_dataBase.SelectModel<Model.OA_Commodity>(selTarget, fromSource) != null)
                {
                    return _dataBase.SelectModel<Model.OA_Commodity>(selTarget, fromSource)[0];
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 得到厂商对应的纸张类别ID
        /// </summary>
        /// <param name="FirmId"></param>
        /// <returns></returns>
        public Model.OA_Commodity getCommodityId(Guid FirmId)
        {
            string selTarget = " * ";
            string fromSource = " OA_Commodity WHERE FirmId ='" + FirmId + "' AND IsPaper='1'";
            if (_dataBase.SelectModel<Model.OA_Commodity>(selTarget, fromSource).Count > 0)
            {
                return _dataBase.SelectModel<Model.OA_Commodity>(selTarget, fromSource)[0];

            }
            else
            {
                return null;
            }

        }

        /// <summary>
        /// 得到厂商对应的版材Id
        /// </summary>
        /// <param name="FirmId"></param>
        /// <returns></returns>
        public Model.OA_Commodity getBanCaiId(Guid FirmId)
        {
            try
            {
                string selTarget = " * ";
                string fromSource = " OA_Commodity WHERE FirmId ='" + FirmId + "' AND IsPaper='2'";
                List<CY.Model.OA_Commodity> m_OA_CommodityList = _dataBase.SelectModel<Model.OA_Commodity>(selTarget, fromSource).ToList();
                if (m_OA_CommodityList != null && m_OA_CommodityList.Count > 0)
                {
                    return m_OA_CommodityList[0];
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}