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_GoodsInfoDAL : IOA_GoodsInfoDAL
    {

        private Database _dataBase = null;

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

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_GoodsInfo trueModel = model as Model.OA_GoodsInfo;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                	new SqlParameter("@FirmId",trueModel.FirmId),
					new SqlParameter("@BrandId",trueModel.BrandId),
					new SqlParameter("@SpecificationId",trueModel.SpecificationId),
					new SqlParameter("@GoodsName",trueModel.GoodsName),
					new SqlParameter("@GoodsTypeId",trueModel.GoodsTypeId),
					new SqlParameter("@Weight",trueModel.Weight),
					new SqlParameter("@StatusId",trueModel.StatusId),
					new SqlParameter("@Price",trueModel.Price),
					new SqlParameter("@GoodsLength",trueModel.GoodsLength),
					new SqlParameter("@GoodsWidth",trueModel.GoodsWidth),
					new SqlParameter("@GoodsHeight",trueModel.GoodsHeight),
					new SqlParameter("@Unit",trueModel.Unit),
					new SqlParameter("@WarningInventory",trueModel.WarningInventory),
					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_GoodsInfo_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_GoodsInfo trueModel = model as Model.OA_GoodsInfo;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    new SqlParameter("@Keyid",trueModel.Keyid),
                	new SqlParameter("@FirmId",trueModel.FirmId),
					new SqlParameter("@BrandId",trueModel.BrandId),
					new SqlParameter("@SpecificationId",trueModel.SpecificationId),
					new SqlParameter("@GoodsName",trueModel.GoodsName),
					new SqlParameter("@GoodsTypeId",trueModel.GoodsTypeId),
					new SqlParameter("@Weight",trueModel.Weight),
					new SqlParameter("@StatusId",trueModel.StatusId),
					new SqlParameter("@Price",trueModel.Price),
					new SqlParameter("@GoodsLength",trueModel.GoodsLength),
					new SqlParameter("@GoodsWidth",trueModel.GoodsWidth),
					new SqlParameter("@GoodsHeight",trueModel.GoodsHeight),
					new SqlParameter("@Unit",trueModel.Unit),
					new SqlParameter("@WarningInventory",trueModel.WarningInventory),
					new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
					new SqlParameter("@Operator",trueModel.Operator),
					new SqlParameter("@Remark",trueModel.Remark) ,
                    new SqlParameter("@OrderNum",trueModel.OrderNum)
            };
            IList<SqlParameter> sqlParmsWhere = new List<SqlParameter>()
            {
                    new SqlParameter("@CommodityId",trueModel.GoodsTypeId),
                	new SqlParameter("@GoodsId",trueModel.Keyid)
            };
            try
            {
                _dataBase.ExecuteSql("UPDATE dbo.OA_Brand SET CommodityId = @CommodityId WHERE GoodsId=@GoodsId", sqlParmsWhere.ToArray<SqlParameter>());
                _dataBase.ExecuteSql("UPDATE dbo.OA_PaperWeight SET CommodityId = @CommodityId WHERE GoodsId=@GoodsId", sqlParmsWhere.ToArray<SqlParameter>());
                _dataBase.ExecuteSql("UPDATE dbo.OA_Specification SET CommodityId = @CommodityId WHERE GoodsId=@GoodsId", sqlParmsWhere.ToArray<SqlParameter>());
                _dataBase.Query("sp_OA_GoodsInfo_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_GoodsInfo trueModel = model as Model.OA_GoodsInfo;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                new SqlParameter("@Keyid",trueModel.Keyid)
            };
            try
            {
                _dataBase.Query("sp_OA_GoodsInfo_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_GoodsInfo> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<Model.OA_GoodsInfo> SelectModelPage(Pagination pa, Guid FirmId, string GoodsName, string CommodityName)
        {

            string Condition = " where a.FirmId='" + FirmId + "' ";
            if (!string.IsNullOrEmpty(GoodsName))
            {
                Condition += "  and a.GoodsName like '%" + GoodsName + "%' ";
            }
            if (!string.IsNullOrEmpty(CommodityName))
            {
                Condition += "  and e.CommodityName like '%" + CommodityName + "%' ";
            }
            string selTarget = "a.* ,b.Name AS brandName,c.Name as SpecificationName,d.Name as  StatusName,e.CommodityName as CommodityName";

            string fromSource = @" oa_GoodsInfo AS a left join oa_brand AS b ON a.BrandId=b.Keyid left join OA_Specification AS c  on a.SpecificationId=c.Keyid left join Sys_Dictionary AS d on a.StatusId=d.Keyid left join OA_Commodity as e  on a.GoodsTypeId=e.Keyid " + Condition + "";

            return _dataBase.SelectModelPage<Model.OA_GoodsInfo>(pa, selTarget, fromSource, " a.GoodsTypeId,a.OrderNum ASC ", "GoodsTypeId ,OrderNum ASC", "");

        }

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

        public Model.OA_GoodsInfo SelectSingleModel(string Keyid)
        {
            try
            {
                string Condition = "where Keyid='" + Keyid + "' ";
                string selTarget = " a.* ,b.Name AS brandName,c.Name as SpecificationName,d.Name as  StatusName,e.CommodityName as CommodityName  ";
                string fromSource = "  (select * from oa_GoodsInfo   " + Condition + ") AS a left join oa_brand AS b ON a.BrandId=b.Keyid left join OA_Specification AS c  on a.SpecificationId=c.Keyid left join Sys_Dictionary AS d on a.StatusId=d.Keyid left join OA_Commodity as e  on a.GoodsTypeId=e.Keyid";
                return _dataBase.SelectModel<Model.OA_GoodsInfo>(selTarget, fromSource)[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取table类型的全部数据
        /// </summary>
        /// <returns></returns>
        public DataTable SelectTableList(Guid FirmId)
        {
            string selectTarget = " * ";
            string fromSource = " OA_GoodsInfo  where FirmId='" + FirmId + "' ";
            return _dataBase.SelectModel(selectTarget, fromSource);
        }

        public DataTable SlectModleByCommity(string GoodsTypeId, Guid FirmId)
        {
            string selectTarget = " * ";
            string fromSource = string.Empty;
            if (string.IsNullOrEmpty(GoodsTypeId))
            {
                fromSource = " OA_GoodsInfo where FirmId = '" + FirmId + "'";
            }
            else
            {
                fromSource = " OA_GoodsInfo  where FirmId = '" + FirmId + "' and GoodsTypeId='" + GoodsTypeId + "' ";
            }
            return _dataBase.SelectModel(selectTarget, fromSource);
        }

        /// <summary>
        /// 货品名字是否存在
        /// </summary>
        /// <param name="GoodsName"></param>
        /// <param name="FirmId"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool isExistGoodsName(String GoodsName, Guid FirmId, int ID)
        {
            Model.OA_GoodsInfo model;
            String selectTarget = " * ";
            string fromSouce = "OA_GoodsInfo where GoodsName='" + GoodsName + "' and FirmId='" + FirmId + "'";
            if (_dataBase.SelectModel<Model.OA_GoodsInfo>(selectTarget, fromSouce).Count > 0)
            {
                model = _dataBase.SelectModel<Model.OA_GoodsInfo>(selectTarget, fromSouce)[0];
            }
            else
            {
                model = null;
            }


            if (model == null)
            {
                return true;
            }
            else
            {
                if (ID == model.Keyid)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
    }
}