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;
|
}
|
}
|
}
|
}
|
