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

namespace CY.SQLDAL
{
    public class OA_InventoryDAL : IOA_InventoryDAL
    {

        private Database _dataBase = null;

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

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

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

        /// <summary>
        /// 仓库管理
        /// </summary>
        /// <param name="pa"></param>
        /// <param name="GoodsName"></param>
        /// <param name="FirmId"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_Inventory> SelectModelList(Pagination pa, Guid FirmId, string PaperWeightId, string WarehouseId, string SpecificationId, string BrandId, string GoodsTypeId, string GoodsId, string Operator, DateTime? beginTime, DateTime? endTime)
        {
            string condition = " where a.FirmId='" + FirmId + "'";

            if (beginTime.HasValue)
            {
                condition += string.Format(" and CAST(a.LastUpdateTime AS DATE) >='{0}'", beginTime);
            }

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

            if (!string.IsNullOrEmpty(PaperWeightId))
            {
                condition += string.Format(" and  a.PaperWeightId='{0}'", PaperWeightId);
            }

            if (!string.IsNullOrEmpty(SpecificationId))
            {
                condition += string.Format(" and   a.SpecificationId='{0}'", SpecificationId);
            }

            if (!string.IsNullOrEmpty(BrandId))
            {
                condition += string.Format(" and  a.BrandId='{0}'", BrandId);
            }

            if (!string.IsNullOrEmpty(WarehouseId))
            {
                condition += string.Format(" and  a.WarehouseId='{0}'", WarehouseId);
            }

            if (!string.IsNullOrEmpty(GoodsTypeId))
            {
                condition += string.Format(" and  b.GoodsTypeId='{0}'", GoodsTypeId);
            }

            if (!string.IsNullOrEmpty(GoodsId))
            {
                condition += string.Format(" and  a.GoodsId='{0}'", GoodsId);
            }
            if (!string.IsNullOrEmpty(Operator))
            {
                condition += string.Format(" and  a.Operator='{0}'", Operator);
            }

            condition += string.Format(" and  a.InventoryCount > '{0}'", 0);

            string selTarget = " a.*,b.GoodsName as GoodsName ,c.WarehouseName,f.Name AS BrandName,j.Name AS SpeceicationName ,h.Name AS PaperWeightName,oacd.IsPaper ";
            string fromSource = "(select * from dbo.OA_Inventory ) 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_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 OA_Commodity as oacd on b.GoodsTypeId = oacd.Keyid " + condition;
            return _dataBase.SelectModelPage<Model.OA_Inventory>(pa, selTarget, fromSource, "c.WarehouseName,b.GoodsName,f.Name,j.Name,h.Name ", "WarehouseName,GoodsName,BrandName,SpeceicationName,PaperWeightName");
        }

        /// <summary>
        /// 仓库管理
        /// </summary>
        /// <param name="pa"></param>
        /// <param name="GoodsName"></param>
        /// <param name="FirmId"></param>
        /// <returns></returns>
        public decimal SelectModelListTotalPrice(Pagination pa, Guid FirmId, string PaperWeightId, string WarehouseId, string SpecificationId, string BrandId, string GoodsTypeId, string GoodsId, string Operator, DateTime? beginTime, DateTime? endTime)
        {
            try
            {
                string condition = " where a.FirmId='" + FirmId + "'";

                if (beginTime.HasValue)
                {
                    condition += string.Format(" and CAST(a.LastUpdateTime AS DATE) >='{0}'", beginTime);
                }

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

                if (!string.IsNullOrEmpty(PaperWeightId))
                {
                    condition += string.Format(" and  a.PaperWeightId='{0}'", PaperWeightId);
                }

                if (!string.IsNullOrEmpty(SpecificationId))
                {
                    condition += string.Format(" and   a.SpecificationId='{0}'", SpecificationId);
                }

                if (!string.IsNullOrEmpty(BrandId))
                {
                    condition += string.Format(" and  a.BrandId='{0}'", BrandId);
                }

                if (!string.IsNullOrEmpty(WarehouseId))
                {
                    condition += string.Format(" and  a.WarehouseId='{0}'", WarehouseId);
                }

                if (!string.IsNullOrEmpty(GoodsTypeId))
                {
                    condition += string.Format(" and  b.GoodsTypeId='{0}'", GoodsTypeId);
                }

                if (!string.IsNullOrEmpty(GoodsId))
                {
                    condition += string.Format(" and  a.GoodsId='{0}'", GoodsId);
                }
                if (!string.IsNullOrEmpty(Operator))
                {
                    condition += string.Format(" and  a.Operator='{0}'", Operator);
                }

                condition += string.Format(" and  a.InventoryCount > '{0}'", 0);

                string selTarget = " SUM(a.TotalPrice) AS TotalPrice ";
                string fromSource = "(select * from dbo.OA_Inventory ) 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_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 OA_Commodity as oacd on b.GoodsTypeId = oacd.Keyid " + condition;
                IList<OA_Inventory> m_OA_InventoryList = _dataBase.SelectModel<Model.OA_Inventory>(selTarget, fromSource).ToList();
                decimal Money = 0;
                if (m_OA_InventoryList != null && m_OA_InventoryList.Count > 0)
                {
                    Money = m_OA_InventoryList[0].TotalPrice ?? 0;
                }
                return Money;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 库存打印列表
        /// </summary>
        /// <param name="FirmId"></param>
        /// <param name="Keyid"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_Inventory> SelectModelListByKeyid(Guid FirmId, string Keyid)
        {
            string condition = " where a.FirmId='" + FirmId + "'";

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

            condition += string.Format(" and  a.InventoryCount > '{0}'", 0);

            string selTarget = " a.*,b.GoodsName as GoodsName ,c.WarehouseName,f.Name AS BrandName,j.Name AS SpeceicationName ,h.Name AS PaperWeightName,oacd.IsPaper ";
            string fromSource = "(select * from dbo.OA_Inventory ) 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_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 OA_Commodity as oacd on b.GoodsTypeId = oacd.Keyid " + condition + " order by c.WarehouseName,b.GoodsName,f.Name,j.Name,h.Name";
            return _dataBase.SelectModel<Model.OA_Inventory>(selTarget, fromSource);


        }

        /// <summary>
        /// 库存打印列表
        /// </summary>
        /// <param name="FirmId"></param>
        /// <param name="Keyid"></param>
        /// <returns></returns>
        public decimal SelectModelListByKeyidTotalPrice(Guid FirmId, string Keyid)
        {
            try
            {
                string condition = " where a.FirmId='" + FirmId + "'";
                if (!string.IsNullOrEmpty(Keyid))
                {
                    condition += string.Format(" and a.Keyid in ({0})", Keyid);
                }
                condition += string.Format(" and  a.InventoryCount > '{0}'", 0);
                string selTarget = " SUM(a.TotalPrice) AS TotalPrice ";
                string fromSource = "(select * from dbo.OA_Inventory ) 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_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 OA_Commodity as oacd on b.GoodsTypeId = oacd.Keyid " + condition;
                IList<OA_Inventory> m_OA_InventoryList = _dataBase.SelectModel<Model.OA_Inventory>(selTarget, fromSource).ToList();
                decimal Money = 0;
                if (m_OA_InventoryList != null && m_OA_InventoryList.Count > 0)
                {
                    Money = m_OA_InventoryList[0].TotalPrice ?? 0;
                }
                return Money;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取货品的详细信息
        /// </summary>
        /// <param name="pa"></param>
        /// <param name="FirmId"></param>
        /// <param name="CargoSpaceName"></param>
        /// <param name="WarehouseName"></param>
        /// <param name="GoodsName"></param>
        /// <param name="CommodityName"></param>
        /// <param name="BrandName"></param>
        /// <param name="SpecificationName"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_Inventory> SelecetModelDetailInfoList(Pagination pa, Guid FirmId, string WarehouseName,string GoodsName, string CommodityName, string BrandName, string SpecificationName)
        {

            string selectTarget = " a.*,b.Name AS CargoSpaceName , c.WarehouseName AS WarehouseName ,g.BrandName,g.SpecificationName,g.GoodsName ,g.CommodityName ,g.IsPaper   ,g.CommodityId";

            string condition = " where a.FirmId='" + FirmId + "'  and  g.IsPaper='true'";
            if (!string.IsNullOrEmpty(WarehouseName))
            {
                condition += string.Format("  and  a.WarehouseId='{0}' ", WarehouseName);
            }

            if (!string.IsNullOrEmpty(BrandName))
            {
                condition += string.Format("  and  g.BrandId='{0}' ", BrandName);
            }
            if (!string.IsNullOrEmpty(SpecificationName))
            {
                condition += string.Format("  and  g.SpecificationId='{0}' ", SpecificationName);
            }
            if (!string.IsNullOrEmpty(GoodsName))
            {
                condition += string.Format("  and  g.GoodsId='{0}' ", GoodsName);
            }

            if (!string.IsNullOrEmpty(CommodityName))
            {
                condition += string.Format("  and  g.CommodityId='{0}' ", CommodityName);
            }

            string fromSource = "dbo.OA_Inventory  AS a   LEFT JOIN  dbo.OA_CargoSpace AS b ON a.CargoSpaceId=b.Keyid " +
          "  LEFT JOIN dbo.OA_WarehouseInfo AS c ON a.WarehouseId = c.Keyid"
          + " LEFT JOIN (SELECT d.* ,e.Name AS BrandName ,f.Name AS SpecificationName ,j.CommodityName,j.IsPaper,j.Keyid AS CommodityId FROM dbo.OA_GoodsInfo AS  d "
          + "LEFT JOIN dbo.OA_Brand AS e ON d.BrandId = e.Keyid LEFT JOIN dbo.OA_Specification AS f ON d.SpecificationId=f.Keyid " +
          "LEFT JOIN dbo.OA_Commodity AS j ON d.GoodsTypeId=j.Keyid  ) AS g ON a.GoodsId=g.Keyid  " + condition;
            return _dataBase.SelectModelPage<Model.OA_Inventory>(pa, selectTarget, fromSource, "  a.LastUpdateTime ");
        }

        public IEnumerable<OA_FirmAccount> getAllSubject(Guid FirmId, string AccountType)
        {
            string selectTarget = "  Keyid,AccountName,AccountType,UserName,Balance";
            string condition = " where FirmId='" + FirmId + "'";
            if (!string.IsNullOrEmpty(AccountType))
            {
                condition += string.Format("  and  AccountType='{0}' ", AccountType);
            }

            string fromSource = " OA_FirmAccount  " + condition;
            return _dataBase.SelectModel<Model.OA_FirmAccount>(selectTarget, fromSource);


        }

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

        /// <summary>
        /// 判断仓库是否存在此产品
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public OA_Inventory IfHasModel(OA_Inventory m_OA_Inventory)
        {
            if (m_OA_Inventory == null)
                return null;
            try
            {
                string selTarget = " * ";
                string fromSource = " dbo.OA_Inventory where GoodsId='" + m_OA_Inventory.GoodsId + "' and WarehouseId='" + m_OA_Inventory.WarehouseId + "' and FirmId='" + m_OA_Inventory.FirmId + "' and BrandId='" + m_OA_Inventory.BrandId + "' and SpecificationId='" + m_OA_Inventory.SpecificationId + "' and PaperWeightId='" + m_OA_Inventory.PaperWeightId + "'";

                List<OA_Inventory> m_OA_InventoryList = _dataBase.SelectModel<Model.OA_Inventory>(selTarget, fromSource) as List<OA_Inventory>;
                if (m_OA_InventoryList != null && m_OA_InventoryList.Count > 0)
                    return m_OA_InventoryList[0];
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }

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