|
/********************************************************************************
|
|
** auth： DBS
|
|
** date： 2018/11/27 14:17:07
|
|
** desc： 尚未编写描述
|
|
** Ver.: V1.0.0
|
|
*********************************************************************************/
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using HH.WMS.Common;
|
using HH.WMS.DAL;
|
using HH.WMS.DAL.Algorithm;
|
using HH.WMS.DAL.AllQuery;
|
using HH.WMS.DAL.Basic;
|
using HH.WMS.Entitys;
|
using HH.WMS.Entitys.Algorithm;
|
using HH.WMS.Entitys.Basic;
|
using HH.WMS.Entitys.Common;
|
using HH.WMS.Entitys.Check;
|
using HH.WMS.Entitys.Entitys;
|
|
namespace HH.WMS.BLL.AllQuery
|
{
|
public class InventoryBll : DapperBaseBLL
|
{
|
#region 查询出入库业务
|
/// <summary>
|
/// 查询出入库业务
|
/// </summary>
|
/// <param name="pageIndex"></param>
|
/// <param name="pageSize"></param>
|
/// <param name="searchModel"></param>
|
/// <param name="orderBy"></param>
|
/// <returns></returns>
|
/// <History>[Hanhe(dbs)] created 2018/12/2</History>
|
public OperateResult GetOrderBussList(int pageIndex, int pageSize, dynamic searchModel, string orderBy = "")
|
{
|
var v = new
|
{
|
CN_S_STOCK_CODE = Util.ToString(searchModel.CN_S_STOCK_CODE),
|
CN_S_OP_TYPE = Util.ToString(searchModel.CN_S_OP_TYPE),
|
CN_S_OP_NO = Util.ToStringInput(searchModel.CN_S_OP_NO),
|
CN_S_ITEM_CODE = Util.ToStringInput(searchModel.CN_S_ITEM_CODE),
|
CN_S_ITEM_NAME = Util.ToStringInput(searchModel.CN_S_ITEM_NAME),
|
CN_S_MODEL = Util.ToStringInput(searchModel.CN_S_MODEL),
|
CN_T_START = Util.ToString(searchModel.CN_T_START),
|
CN_T_END = Util.ToString(searchModel.CN_T_END)
|
};
|
string sqlWhere = " WHERE 1=1 ";
|
if (!string.IsNullOrEmpty(v.CN_S_STOCK_CODE)) //仓库编码
|
{
|
sqlWhere += " AND CN_S_STOCK_CODE = '" + v.CN_S_STOCK_CODE + "'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_S_OP_TYPE)) //业务号
|
{
|
sqlWhere += " AND CN_S_OP_TYPE='" + v.CN_S_OP_TYPE + "'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_S_OP_NO)) //业务编码
|
{
|
sqlWhere += " AND CN_S_OP_NO='" + v.CN_S_OP_NO + "'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_S_ITEM_CODE)) //物料编码
|
{
|
sqlWhere += " AND CN_S_ITEM_CODE like '%" + v.CN_S_ITEM_CODE + "%'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_S_ITEM_NAME)) //物料名称
|
{
|
sqlWhere += " AND CN_S_ITEM_NAME like '%" + v.CN_S_ITEM_NAME + "%'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_S_MODEL)) //规格
|
{
|
sqlWhere += " AND CN_S_MODEL like '%" + v.CN_S_MODEL + "%'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_T_START))
|
{
|
sqlWhere += " AND CN_T_CREATE >= '" + Convert.ToDateTime(v.CN_T_START.ToString()).ToString("yyyy-MM-dd") + "' ";
|
}
|
|
if (!string.IsNullOrEmpty(v.CN_T_END))
|
{
|
sqlWhere += " AND CN_T_CREATE <= '" + Convert.ToDateTime(v.CN_T_END).ToString("yyyy-MM-dd") + "' ";
|
}
|
//if (!string.IsNullOrEmpty(Creates)) //操作员
|
//{
|
// sqlWhere += " AND CN_S_CREATOR_BY='" + Creates + "'";
|
//}
|
//if (!string.IsNullOrEmpty(Type)) //类型
|
//{
|
// sqlWhere += " AND CN_S_OP_TYPE='" + Type + "'";
|
//}
|
|
////开始时间
|
//if (!string.IsNullOrEmpty(StraDate))
|
//{
|
// sqlWhere += " AND CN_T_CREATE >= '" + StraDate + "' ";
|
//}
|
////结束时间
|
//if (!string.IsNullOrEmpty(EndDate))
|
//{
|
// sqlWhere += " AND CN_T_CREATE <= DateAdd(dd, 1, '" + EndDate + "')";
|
//}
|
|
orderBy = " order by CN_T_CREATE desc";
|
|
long total = 0;
|
DataTable dt = CreateDAL<InventoryDal>().GetOrderBussList(pageIndex, pageSize, out total, sqlWhere, orderBy);
|
dt.Columns["CN_S_STOCK_CODE"].ReadOnly = false;
|
foreach (DataRow dr in dt.Rows)
|
{
|
TN_AB_STOCKEntity entity = GetStock().Find(x => x.CN_S_STOCK_CODE.Equals(dr["CN_S_STOCK_CODE"].ToString().Trim()));
|
if (entity != null)
|
dr["CN_S_STOCK_CODE"] = entity.CN_S_STOCK_NAME;
|
}
|
|
return OperateResult.Succeed(null, new
|
{
|
rows = dt,
|
total = total
|
});
|
}
|
#endregion
|
|
#region 查询库区
|
/// <summary>
|
/// 查询库区
|
/// </summary>
|
/// <param name="searchModel"></param>
|
/// <param name="export">是否非导出</param>
|
/// <param name="orderBy">排序</param>
|
/// <returns></returns>
|
/// <History>[Hanhe(dbs)] created 2018/12/2</History>
|
public OperateResult GetInventoryList(SearchModel searchModel, bool export, string orderBy = "")
|
{
|
var v = new
|
{
|
CN_S_STOCK_CODE = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_CODE),
|
CN_S_STOCK_AREA = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_AREA),
|
CN_S_ITEM_CODE = Util.ToString(searchModel.SearchCondition.CN_S_ITEM_CODE),
|
CN_S_ITEM_NAME = Util.ToString(searchModel.SearchCondition.CN_S_ITEM_NAME),
|
CN_S_MODEL = Util.ToString(searchModel.SearchCondition.CN_S_MODEL),
|
CN_S_OP_NO = Util.ToString(searchModel.SearchCondition.CN_S_OP_NO),
|
CN_S_STATE = Util.ToString(searchModel.SearchCondition.CN_S_STATE),
|
CN_S_PRODUCTION_BATCH = Util.ToString(searchModel.SearchCondition.CN_S_PRODUCTION_BATCH),
|
CN_S_OWNER = Util.ToString(searchModel.SearchCondition.CN_S_OWNER),
|
showArea = Convert.ToBoolean(searchModel.SearchCondition.showArea),
|
PageIndex = Util.ToInt(searchModel.PageIndex),
|
PageSize = Util.ToInt(searchModel.PageSize),
|
CN_S_LOT_NO = Util.ToString(searchModel.SearchCondition.CN_S_LOT_NO)
|
};
|
|
long total = 0;
|
DataTable dt = new DataTable();
|
if (!v.showArea)
|
{
|
dt = GetStockNotPageInventor(export, v, out total);
|
Log.Info("InventoryBll", "true");
|
}
|
else
|
{
|
dt = GetAreakNotPageInventor(export, v, out total);
|
}
|
dt.Columns.Add("CN_F_SAFETY_STOCK");
|
dt.Columns.Add("CN_S_STOCK_NAME");
|
dt.Columns.Add("CN_S_AREA_NAME");
|
OperateResult result = new OperateResult();
|
if (dt.Rows.Count > 0)
|
{
|
Log.Info("InventoryBll", "1");
|
//获取物料
|
List<string> itemCodelist = dt.AsEnumerable().Select(x => x.Field<string>("CN_S_ITEM_CODE")).ToList();
|
List<AutoBomItemEntity> items = CreateDAL<TN_WMS_ITEMDAL>().GetItemList(itemCodelist);
|
|
Log.Info("InventoryBll", "2");
|
//获取库区
|
List<AutoBomStockAreaEntity> arealist = CreateDAL<TN_AB_B_STOCK_AREADAL>().GetArea(v.CN_S_STOCK_CODE, 4);
|
Log.Info("InventoryBll", "3");
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[i]["CN_S_ITEM_CODE"].ToString()))
|
{
|
List<AutoBomItemEntity> item = items.Where(x => x.CN_S_ITEM_CODE.Equals(dt.Rows[i]["CN_S_ITEM_CODE"].ToString())).ToList();
|
dt.Rows[i]["CN_F_SAFETY_STOCK"] = item.Count == 0 ? "0" : item[0].CN_F_SAFETY_STOCK.ToString();
|
dt.Rows[i]["CN_F_PLANNED_QTY"] = item.Count == 0 ? "0" : item[0].CN_F_NW.ToString();
|
dt.Rows[i]["CN_S_MEASURE_UNIT"] = item.Count == 0 ? "" : item[0].CN_S_MEASURE_UNIT.ToString();
|
}
|
Log.Info("InventoryBll", "4");
|
|
if (!string.IsNullOrEmpty(dt.Rows[i]["CN_S_STOCK_CODE"].ToString())) //获取仓库名称
|
{
|
dt.Rows[i]["CN_S_STOCK_NAME"] = GetStockName(dt.Rows[i]["CN_S_STOCK_CODE"].ToString().Trim());
|
}
|
Log.Info("InventoryBll", "5");
|
if (v.showArea)
|
{
|
if (!string.IsNullOrEmpty(dt.Rows[i]["CN_S_STOCK_AREA"].ToString()))//获取库区名称
|
{
|
AutoBomStockAreaEntity area = arealist.Find(x => x.CN_S_AREA_CODE.Equals(dt.Rows[i]["CN_S_STOCK_AREA"].ToString().Trim()));
|
dt.Rows[i]["CN_S_AREA_NAME"] = area != null ? area.CN_S_AREA_NAME : "";
|
}
|
}
|
Log.Info("InventoryBll", "6");
|
|
|
}
|
result.Status = ResultStatus.Success;
|
result.Data = new
|
{
|
rows = dt,
|
total = total
|
};
|
}
|
else
|
{
|
result.Status = ResultStatus.Success;
|
}
|
return result;
|
}
|
|
private DataTable GetStockNotPageInventor(bool page, dynamic v, out long total)
|
{
|
if (page)
|
{
|
Log.Info("InventoryBll", "GetStockNotPageInventor");
|
return CreateDAL<DapperDAL<TN_WM_B_STOCK_QTYEntity>>().GetPagingData(v.PageIndex, v.PageSize, out total, new List<SearchWhere>() {
|
new SearchWhere(){key="CN_S_STOCK_CODE", sign= WhereSign.Eq, value=v.CN_S_STOCK_CODE},
|
new SearchWhere(){key="CN_S_OP_NO", sign= WhereSign.Eq, value=v.CN_S_OP_NO},
|
new SearchWhere(){key="CN_S_STATE", sign= WhereSign.Eq, value=v.CN_S_STATE},
|
new SearchWhere(){key="CN_S_ITEM_CODE", sign= WhereSign.Like, value=v.CN_S_ITEM_CODE},
|
new SearchWhere(){key="CN_S_ITEM_NAME", sign= WhereSign.Like, value=v.CN_S_ITEM_NAME},
|
new SearchWhere(){key="CN_S_PRODUCTION_BATCH", sign= WhereSign.Like, value=v.CN_S_PRODUCTION_BATCH},
|
new SearchWhere(){key="CN_S_MODEL", sign= WhereSign.Like, value=v.CN_S_MODEL},
|
new SearchWhere(){key="CN_F_QUANTITY", sign= WhereSign.Gt, value=0},
|
new SearchWhere(){key="CN_S_OWNER", sign= WhereSign.Eq, value=v.CN_S_OWNER},
|
new SearchWhere(){key="CN_S_LOT_NO", sign= WhereSign.Like, value=v.CN_S_LOT_NO}
|
}, true, new
|
{
|
CN_S_ITEM_CODE = "%" + v.CN_S_ITEM_CODE + "%",
|
CN_S_ITEM_NAME = "%" + v.CN_S_ITEM_NAME + "%",
|
CN_S_MODEL = "%" + v.CN_S_MODEL + "%",
|
CN_S_PRODUCTION_BATCH = "%" + v.CN_S_PRODUCTION_BATCH + "%",
|
CN_S_OP_NO = v.CN_S_OP_NO,
|
CN_S_STATE = v.CN_S_STATE,
|
CN_S_STOCK_CODE = v.CN_S_STOCK_CODE,
|
CN_F_QUANTITY = 0,
|
CN_S_OWNER = v.CN_S_OWNER,
|
CN_S_LOT_NO = "%" + v.CN_S_LOT_NO + "%"
|
}, " ORDER BY CN_S_ITEM_CODE ");
|
}
|
else
|
{
|
total = 0;
|
return CreateDAL<DapperDAL<TN_WM_B_STOCK_QTYEntity>>().GetData(new List<SearchWhere>() {
|
new SearchWhere(){key="CN_S_STOCK_CODE", sign= WhereSign.Eq, value=v.CN_S_STOCK_CODE},
|
new SearchWhere(){key="CN_S_OP_NO", sign= WhereSign.Eq, value=v.CN_S_OP_NO},
|
new SearchWhere(){key="CN_S_STATE", sign= WhereSign.Eq, value=v.CN_S_STATE},
|
new SearchWhere(){key="CN_S_ITEM_CODE", sign= WhereSign.Like, value=v.CN_S_ITEM_CODE},
|
new SearchWhere(){key="CN_S_ITEM_NAME", sign= WhereSign.Like, value=v.CN_S_ITEM_NAME},
|
new SearchWhere(){key="CN_S_PRODUCTION_BATCH", sign= WhereSign.Like, value=v.CN_S_PRODUCTION_BATCH},
|
new SearchWhere(){key="CN_S_MODEL", sign= WhereSign.Like, value=v.CN_S_MODEL},
|
new SearchWhere(){key="CN_F_QUANTITY", sign= WhereSign.Gt, value=0},
|
new SearchWhere(){key="CN_S_OWNER", sign= WhereSign.Eq, value=v.CN_S_OWNER},
|
new SearchWhere(){key="CN_S_LOT_NO", sign= WhereSign.Like, value=v.CN_S_LOT_NO}
|
}, true, new
|
{
|
CN_S_ITEM_CODE = "%" + v.CN_S_ITEM_CODE + "%",
|
CN_S_ITEM_NAME = "%" + v.CN_S_ITEM_NAME + "%",
|
CN_S_MODEL = "%" + v.CN_S_MODEL + "%",
|
CN_S_PRODUCTION_BATCH = "%" + v.CN_S_PRODUCTION_BATCH + "%",
|
CN_S_OP_NO = v.CN_S_OP_NO,
|
CN_S_STATE = v.CN_S_STATE,
|
CN_S_STOCK_CODE = v.CN_S_STOCK_CODE,
|
CN_F_QUANTITY = 0,
|
CN_S_OWNER = v.CN_S_OWNER,
|
CN_S_LOT_NO = "%" + v.CN_S_LOT_NO + "%"
|
}, " ORDER BY CN_S_ITEM_CODE ");
|
}
|
}
|
|
private DataTable GetAreakNotPageInventor(bool page, dynamic v, out long total)
|
{
|
if (page)
|
{
|
return CreateDAL<DapperDAL<TN_WM_B_AREA_QTYEntity>>().GetPagingData(v.PageIndex, v.PageSize, out total, new List<SearchWhere>() {
|
new SearchWhere(){key="CN_S_STOCK_CODE", sign= WhereSign.Eq, value=v.CN_S_STOCK_CODE},
|
new SearchWhere(){key="CN_S_OP_NO", sign= WhereSign.Eq, value=v.CN_S_OP_NO},
|
new SearchWhere(){key="CN_S_STOCK_AREA", sign= WhereSign.Eq, value=v.CN_S_STOCK_AREA},
|
new SearchWhere(){key="CN_S_ITEM_CODE", sign= WhereSign.Like, value=v.CN_S_ITEM_CODE},
|
new SearchWhere(){key="CN_S_ITEM_NAME", sign= WhereSign.Like, value=v.CN_S_ITEM_NAME},
|
new SearchWhere(){key="CN_S_PRODUCTION_BATCH", sign= WhereSign.Like, value=v.CN_S_PRODUCTION_BATCH},
|
new SearchWhere(){key="CN_S_MODEL", sign= WhereSign.Like, value=v.CN_S_MODEL},
|
new SearchWhere(){key="CN_S_STATE", sign= WhereSign.Eq, value=v.CN_S_STATE},
|
new SearchWhere(){key="CN_F_QUANTITY", sign= WhereSign.Gt, value=0},
|
new SearchWhere(){key="CN_S_OWNER", sign= WhereSign.Eq, value=v.CN_S_OWNER},
|
new SearchWhere(){key="CN_S_LOT_NO", sign= WhereSign.Like, value=v.CN_S_LOT_NO}
|
}, true, new
|
{
|
CN_S_ITEM_CODE = "%" + v.CN_S_ITEM_CODE + "%",
|
CN_S_ITEM_NAME = "%" + v.CN_S_ITEM_NAME + "%",
|
CN_S_MODEL = "%" + v.CN_S_MODEL + "%",
|
CN_S_PRODUCTION_BATCH = "%" + v.CN_S_PRODUCTION_BATCH + "%",
|
CN_S_OP_NO = v.CN_S_OP_NO,
|
CN_S_STATE = v.CN_S_STATE,
|
CN_S_STOCK_CODE = v.CN_S_STOCK_CODE,
|
CN_S_STOCK_AREA = v.CN_S_STOCK_AREA,
|
CN_S_OWNER = v.CN_S_OWNER,
|
CN_F_QUANTITY = 0,
|
CN_S_LOT_NO = "%" + v.CN_S_LOT_NO + "%"
|
}, " ORDER BY CN_S_ITEM_CODE ");
|
}
|
else
|
{
|
total = 0;
|
return CreateDAL<DapperDAL<TN_WM_B_AREA_QTYEntity>>().GetData(new List<SearchWhere>() {
|
new SearchWhere(){key="CN_S_STOCK_CODE", sign= WhereSign.Eq, value=v.CN_S_STOCK_CODE},
|
new SearchWhere(){key="CN_S_OP_NO", sign= WhereSign.Eq, value=v.CN_S_OP_NO},
|
new SearchWhere(){key="CN_S_STOCK_AREA", sign= WhereSign.Eq, value=v.CN_S_STOCK_AREA},
|
new SearchWhere(){key="CN_S_ITEM_CODE", sign= WhereSign.Like, value=v.CN_S_ITEM_CODE},
|
new SearchWhere(){key="CN_S_ITEM_NAME", sign= WhereSign.Like, value=v.CN_S_ITEM_NAME},
|
new SearchWhere(){key="CN_S_PRODUCTION_BATCH", sign= WhereSign.Like, value=v.CN_S_PRODUCTION_BATCH},
|
new SearchWhere(){key="CN_S_MODEL", sign= WhereSign.Like, value=v.CN_S_MODEL},
|
new SearchWhere(){key="CN_S_STATE", sign= WhereSign.Eq, value=v.CN_S_STATE},
|
new SearchWhere(){key="CN_F_QUANTITY", sign= WhereSign.Gt, value=0},
|
new SearchWhere(){key="CN_S_OWNER", sign= WhereSign.Eq, value=v.CN_S_OWNER},
|
new SearchWhere(){key="CN_S_LOT_NO", sign= WhereSign.Like, value=v.CN_S_LOT_NO}
|
}, true, new
|
{
|
CN_S_ITEM_CODE = "%" + v.CN_S_ITEM_CODE + "%",
|
CN_S_ITEM_NAME = "%" + v.CN_S_ITEM_NAME + "%",
|
CN_S_MODEL = "%" + v.CN_S_MODEL + "%",
|
CN_S_PRODUCTION_BATCH = "%" + v.CN_S_PRODUCTION_BATCH + "%",
|
CN_S_OP_NO = v.CN_S_OP_NO,
|
CN_S_STATE = v.CN_S_STATE,
|
CN_S_STOCK_CODE = v.CN_S_STOCK_CODE,
|
CN_S_STOCK_AREA = v.CN_S_STOCK_AREA,
|
CN_S_OWNER = v.CN_S_OWNER,
|
CN_F_QUANTITY = 0,
|
CN_S_LOT_NO = "%" + v.CN_S_LOT_NO + "%"
|
}, " ORDER BY CN_S_ITEM_CODE ");
|
}
|
}
|
#endregion
|
|
#region 汇总指定日期内的出入库业务量
|
/// <summary>
|
/// 汇总指定日期内的出入库业务量
|
/// </summary>
|
/// <param name="day"></param>
|
/// <returns></returns>
|
/// <History>[Hanhe(dbs)] created 2018-12-21</History>
|
public DataTable GetBussLine(int day)
|
{
|
DataTable dt = CreateDAL<InventoryDal>().GetBussLine(day);
|
return dt;
|
}
|
#endregion
|
|
#region 获取货位的历史上下架记录
|
/// <summary>
|
/// 获取货位的历史上下架记录
|
/// </summary>
|
/// <param name="searchModel"></param>
|
/// <returns></returns>
|
/// <History>[Hanhe(DBS)] CREATED BY 2018-12-03</History>
|
public OperateResult GetLocationHistory(SearchModel searchModel)
|
{
|
long total = 0;
|
DataTable dt = CreateDAL<InventoryDal>().GetLocationHistory(searchModel, out total);
|
dt.Columns["CN_S_STOCK_CODE"].ReadOnly = false;
|
dt.Columns["CN_S_STOCK_AREA"].ReadOnly = false;
|
foreach (DataRow dr in dt.Rows)
|
{
|
dr["CN_S_STOCK_CODE"] = GetStockName(dr["CN_S_STOCK_CODE"].ToString().Trim());
|
dr["CN_S_STOCK_AREA"] = GetAreaName(dr["CN_S_STOCK_AREA"].ToString().Trim());
|
}
|
return OperateResult.Succeed(null, new
|
{
|
rows = dt,
|
total = total
|
});
|
}
|
|
|
|
#endregion
|
|
#region 获取货位的历史上下架记录 巨星
|
/// <summary>
|
/// 获取货位的历史上下架记录
|
/// </summary>
|
/// <param name="searchModel"></param>
|
/// <returns></returns>
|
/// <History>[Hanhe(DBS)] CREATED BY 2018-12-03</History>
|
public OperateResult GetLocationHistoryJx(SearchModel searchModel)
|
{
|
long total = 0;
|
DataTable dt = CreateDAL<InventoryDal>().GetLocationHistoryJx(searchModel, out total);
|
dt.Columns["CN_S_STOCK_CODE"].ReadOnly = false;
|
dt.Columns["CN_S_STOCK_AREA"].ReadOnly = false;
|
foreach (DataRow dr in dt.Rows)
|
{
|
dr["CN_S_STOCK_CODE"] = GetStockName(dr["CN_S_STOCK_CODE"].ToString().Trim());
|
dr["CN_S_STOCK_AREA"] = GetAreaName(dr["CN_S_STOCK_AREA"].ToString().Trim());
|
}
|
return OperateResult.Succeed(null, new
|
{
|
rows = dt,
|
total = total
|
});
|
}
|
|
|
|
#endregion
|
|
//public OperateResult HistoryLine(SearchModel searchModel)
|
//{
|
// DataTable dt = CreateDAL<InventoryDal>().HistoryLine(searchModel);
|
// return OperateResult.Succeed(null, dt);
|
//}
|
|
#region 查询盘点报表
|
/// <summary>
|
/// 查询盘点报表
|
/// </summary>
|
/// <param name="searchModel"></param>
|
/// <returns></returns>
|
/// <History>[Hanhe(DBS)] CREATED BY 2019-1-10</History>
|
public OperateResult GetCheckDtl(SearchModel searchModel)
|
{
|
long total = 0;
|
var v = new
|
{
|
CN_S_STOCK_CODE = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_CODE),
|
CN_S_OP_NO = Util.ToStringInput(searchModel.SearchCondition.CN_S_OP_NO),
|
CN_S_LOCATION_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_LOCATION_CODE),
|
CN_T_START = Util.ToString(searchModel.SearchCondition.CN_T_START),
|
CN_T_END = Util.ToString(searchModel.SearchCondition.CN_T_END),
|
};
|
string strWhere = "";
|
if (!string.IsNullOrEmpty(v.CN_S_STOCK_CODE))
|
{
|
strWhere += " and b.CN_S_STOCK_CODE='" + v.CN_S_STOCK_CODE + "'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_S_OP_NO))
|
{
|
strWhere += " and b.CN_S_OP_NO='" + v.CN_S_OP_NO + "'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_S_LOCATION_CODE))
|
{
|
strWhere += " and CN_S_LOCATION_CODE like '%" + v.CN_S_LOCATION_CODE + "%'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_T_START))
|
{
|
strWhere += " and convert(date,b.CN_T_CREATE) >= '" + Convert.ToDateTime(v.CN_T_START.ToString()).ToString("yyyy-MM-dd") + "'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_T_END))
|
{
|
strWhere += " and convert(date,b.CN_T_CREATE) <= '" + Convert.ToDateTime(v.CN_T_END).ToString("yyyy-MM-dd") + "'";
|
}
|
DataTable dt = CreateDAL<InventoryDal>().GetCheckResultDtl(searchModel.PageIndex, searchModel.PageSize, strWhere, out total);
|
dt.Columns["CN_S_STOCK_CODE"].ReadOnly = false;
|
foreach (DataRow dr in dt.Rows)
|
{
|
dr["CN_S_STOCK_CODE"] = GetStock().Find(x => x.CN_S_STOCK_CODE.Equals(dr["CN_S_STOCK_CODE"].ToString().Trim())).CN_S_STOCK_NAME;
|
}
|
return OperateResult.Succeed(null, new
|
{
|
rows = dt,
|
total = total
|
});
|
}
|
#endregion
|
|
#region 汇总查询盘点结果汇总数
|
/// <summary>
|
/// 汇总查询盘点结果汇总数
|
/// </summary>
|
/// <param name="searchModel"></param>
|
/// <returns></returns>
|
/// <History>[Hanhe(DBS)] CREATED BY 2019-1-10</History>
|
public OperateResult GetCheckDtlTotal(SearchModel searchModel)
|
{
|
var v = new
|
{
|
CN_S_STOCK_CODE = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_CODE),
|
CN_S_OP_NO = Util.ToStringInput(searchModel.SearchCondition.CN_S_OP_NO),
|
CN_S_LOCATION_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_LOCATION_CODE),
|
CN_S_ITEM_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_CODE),
|
CN_S_ITEM_NAME = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_NAME),
|
CN_T_START = Util.ToString(searchModel.SearchCondition.CN_T_START),
|
CN_T_END = Util.ToString(searchModel.SearchCondition.CN_T_END),
|
};
|
string strWhere = "";
|
if (!string.IsNullOrEmpty(v.CN_S_STOCK_CODE))
|
{
|
strWhere += " and b.CN_S_STOCK_CODE='" + v.CN_S_STOCK_CODE + "'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_S_OP_NO))
|
{
|
strWhere += " and b.CN_S_OP_NO like '%" + v.CN_S_OP_NO + "%'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_S_LOCATION_CODE))
|
{
|
strWhere += " and CN_S_LOCATION_CODE like '%" + v.CN_S_LOCATION_CODE + "%'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_S_ITEM_CODE))
|
{
|
strWhere += " and CN_S_ITEM_CODE like '%" + v.CN_S_ITEM_CODE + "%'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_S_ITEM_NAME))
|
{
|
strWhere += " and CN_S_ITEM_NAME like '%" + v.CN_S_ITEM_NAME + "%'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_T_START))
|
{
|
strWhere += " and convert(date,b.CN_T_CREATE) >= '" + Convert.ToDateTime(v.CN_T_START.ToString()).ToString("yyyy-MM-dd") + "'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_T_END))
|
{
|
strWhere += " and convert(date,b.CN_T_CREATE) <= '" + Convert.ToDateTime(v.CN_T_END).ToString("yyyy-MM-dd") + "'";
|
}
|
|
string totalSql = @"SELECT 'Money'=0.00,CN_S_ITEM_CODE,SUM(CN_F_QUANTITY) CN_F_QUANTITY,SUM(CN_F_CHECK_QTY) CN_F_CHECK_QTY
|
FROM tn_wm_check_list a join tn_wm_check_mst b on a.CN_S_OP_NO= b.CN_S_OP_NO
|
where b.CN_S_STATE='已完成' " + strWhere + " group by CN_S_ITEM_CODE";
|
DataTable totalDt = CreateDAL<InventoryDal>().ExecuteDataTable(totalSql);
|
|
List<TN_AB_B_ITEM_PRICEEntity> items = CreateDAL<TN_AB_B_ITEM_PRICEDAL>().GetItemList(totalDt.AsEnumerable().Select(x => x.Field<string>("CN_S_ITEM_CODE")).ToList());
|
|
TN_AB_B_ITEM_PRICEEntity item;
|
totalDt.Columns["Money"].ReadOnly = false;
|
foreach (DataRow dr in totalDt.Rows)
|
{
|
item = items.Find(x => x.CN_S_ITEM_CODE.Equals(dr["CN_S_ITEM_CODE"]));
|
if (item != null)
|
dr["Money"] = Convert.ToDecimal(item.CN_F_PRICE) * (Convert.ToDecimal(dr["CN_F_CHECK_QTY"].ToString()) - Convert.ToDecimal(dr["CN_F_QUANTITY"].ToString()));
|
}
|
return OperateResult.Succeed("", new
|
{
|
CN_F_QUANTITY = totalDt.AsEnumerable().Sum(y => y.Field<decimal>("CN_F_QUANTITY")),
|
CN_F_CHECK_QTY = totalDt.AsEnumerable().Sum(y => y.Field<decimal>("CN_F_CHECK_QTY")) - totalDt.AsEnumerable().Sum(y => y.Field<decimal>("CN_F_QUANTITY")),
|
Money = totalDt.AsEnumerable().Sum(y => y.Field<decimal>("Money"))
|
});
|
}
|
#endregion
|
|
#region 物料盘点汇总
|
/// <summary>
|
/// 物料盘点汇总
|
/// </summary>
|
/// <param name="searchModel"></param>
|
/// <returns></returns>
|
/// <History>[Hanhe(DBS)] CREATED BY 2019-1-10</History>
|
public OperateResult GetItemCheckDtl(SearchModel searchModel)
|
{
|
long total = 0;
|
var v = new
|
{
|
CN_S_STOCK_CODE = Util.ToString(searchModel.SearchCondition.CN_S_STOCK_CODE),
|
CN_S_ITEM_CODE = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_CODE),
|
CN_S_ITEM_NAME = Util.ToStringInput(searchModel.SearchCondition.CN_S_ITEM_NAME),
|
CN_S_MODEL = Util.ToStringInput(searchModel.SearchCondition.CN_S_MODEL)
|
};
|
string strWhere = "";
|
if (!string.IsNullOrEmpty(v.CN_S_STOCK_CODE))
|
{
|
strWhere += " and b.CN_S_STOCK_CODE='" + v.CN_S_STOCK_CODE + "'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_S_ITEM_CODE))
|
{
|
strWhere += " and CN_S_ITEM_CODE like '%" + v.CN_S_ITEM_CODE + "%'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_S_ITEM_NAME))
|
{
|
strWhere += " and CN_S_ITEM_NAME like '%" + v.CN_S_ITEM_NAME + "%'";
|
}
|
if (!string.IsNullOrEmpty(v.CN_S_MODEL))
|
{
|
strWhere += " and CN_S_MODEL like '%" + v.CN_S_MODEL + "%'";
|
}
|
|
DataTable dt = CreateDAL<InventoryDal>().GetItemCheckResultDtl(searchModel.PageIndex, searchModel.PageSize, strWhere, out total);
|
dt.Columns["CN_S_STOCK_CODE"].ReadOnly = false;
|
foreach (DataRow dr in dt.Rows)
|
{
|
dr["CN_S_STOCK_CODE"] = GetStock().Find(x => x.CN_S_STOCK_CODE.Equals(dr["CN_S_STOCK_CODE"].ToString().Trim())).CN_S_STOCK_NAME;
|
}
|
return OperateResult.Succeed(null, new
|
{
|
rows = dt,
|
total = total
|
});
|
}
|
#endregion
|
|
#region 查询货位库存
|
/// <summary>
|
/// 查询货位库存
|
/// </summary>
|
/// <param name="searchModel"></param>
|
/// <returns></returns>
|
/// <History>[Hanhe(DBS)] CREATED BY 2019-1-10</History>
|
public OperateResult GetLocationInventoryList(SearchModel searchModel, bool export)
|
{
|
long total = 0;
|
DataTable dt = CreateDAL<InventoryDal>().GetLocationInventoryList(searchModel, out total, export);
|
dt.Columns["CN_S_STOCK_CODE"].ReadOnly = false;
|
dt.Columns["CN_S_STOCK_AREA"].ReadOnly = false;
|
dt.Columns["CN_F_PLANNED_QTY"].ReadOnly = false;
|
dt.Columns["CN_F_PLANNED_QTY"].MaxLength = 32;
|
foreach (DataRow dr in dt.Rows)
|
{
|
dr["CN_S_STOCK_CODE"] = GetStock().Find(x => x.CN_S_STOCK_CODE.Equals(dr["CN_S_STOCK_CODE"].ToString().Trim())).CN_S_STOCK_NAME;
|
dr["CN_S_STOCK_AREA"] = GetAreaName(dr["CN_S_STOCK_AREA"].ToString().Trim());
|
try
|
{
|
var item = CreateDAL<TN_WMS_ITEMDAL>().GetItemEntity(dr["CN_S_ITEM_CODE"].ToString());
|
dr["CN_F_PLANNED_QTY"] = item.CN_F_NW.ToString();
|
}
|
catch (Exception ex)
|
{
|
Log.Detail("报错", ex.Message);
|
dr["CN_F_PLANNED_QTY"] = 0;
|
}
|
|
}
|
return OperateResult.Succeed(null, new
|
{
|
rows = dt,
|
total = total
|
});
|
}
|
|
public OperateResult GetLocationInventoryListJx(SearchModel searchModel, bool export)
|
{
|
long total = 0;
|
DataTable dt = CreateDAL<InventoryDal>().GetLocationInventoryListJx(searchModel, out total, export);
|
dt.Columns["CN_S_STOCK_CODE"].ReadOnly = false;
|
dt.Columns["CN_S_STOCK_AREA"].ReadOnly = false;
|
foreach (DataRow dr in dt.Rows)
|
{
|
dr["CN_S_STOCK_CODE"] = GetStock().Find(x => x.CN_S_STOCK_CODE.Equals(dr["CN_S_STOCK_CODE"].ToString().Trim())).CN_S_STOCK_NAME;
|
dr["CN_S_STOCK_AREA"] = GetAreaName(dr["CN_S_STOCK_AREA"].ToString().Trim());
|
|
}
|
return OperateResult.Succeed(null, new
|
{
|
rows = dt,
|
total = total
|
});
|
}
|
#endregion
|
|
|
public OperateResult GetInventorySummary(SearchModel searchModel)
|
{
|
|
long total = 0;
|
DataTable dt = CreateDAL<InventoryDal>().GetInventorySummary(searchModel, out total);
|
dt.Columns["CN_S_STOCK_CODE"].ReadOnly = false;
|
dt.Columns["CN_F_PACKING_QTY"].ReadOnly = false;
|
//dt.Columns["CN_S_STOCK_AREA"].ReadOnly = false;
|
foreach (DataRow dr in dt.Rows)
|
{
|
//领料出库
|
if ("正常出库".Equals(dr["CN_S_EXT2"].ToString()))
|
{
|
var upHistorys = CreateDAL<DapperDAL<TN_WM_UP_HISTORYEntity>>().GetList(new
|
{
|
CN_S_EXT2 = "退料入库",
|
CN_S_ITEM_CODE = dr["CN_S_ITEM_CODE"].ToString(),
|
CN_S_MODEL = dr["CN_S_MODEL"].ToString(),
|
CN_S_LOT_NO = dr["CN_S_LOT_NO"].ToString()
|
});
|
if (upHistorys != null)
|
{
|
if (upHistorys.Count() > 0)
|
{
|
foreach (var upHistory in upHistorys)
|
{
|
dr["CN_F_PACKING_QTY"] = Convert.ToDecimal(dr["CN_F_PACKING_QTY"]) - upHistory.CN_F_PACKING_QTY;
|
}
|
}
|
}
|
}
|
dr["CN_S_STOCK_CODE"] = GetStockName(dr["CN_S_STOCK_CODE"].ToString().Trim());
|
dr["CN_F_PACKING_QTY"] = Math.Round(Convert.ToDecimal(dr["CN_F_PACKING_QTY"]));
|
|
}
|
return OperateResult.Succeed(null, new
|
{
|
rows = dt,
|
total = total
|
});
|
}
|
|
public OperateResult ExportInventorySummary(SearchModel searchModel)
|
{
|
|
long total = 0;
|
DataTable dt = CreateDAL<InventoryDal>().ExportInventorySummary(searchModel);
|
dt.Columns["CN_S_STOCK_CODE"].ReadOnly = false;
|
dt.Columns["CN_F_PACKING_QTY"].ReadOnly = false;
|
foreach (DataRow dr in dt.Rows)
|
{
|
if ("正常出库".Equals(dr["CN_S_EXT2"].ToString()))
|
{
|
var upHistorys = CreateDAL<DapperDAL<TN_WM_UP_HISTORYEntity>>().GetList(new
|
{
|
CN_S_EXT2 = "退料入库",
|
CN_S_ITEM_CODE = dr["CN_S_ITEM_CODE"].ToString(),
|
CN_S_MODEL = dr["CN_S_MODEL"].ToString(),
|
CN_S_LOT_NO = dr["CN_S_LOT_NO"].ToString()
|
});
|
if (upHistorys != null)
|
{
|
if (upHistorys.Count() > 0)
|
{
|
foreach (var upHistory in upHistorys)
|
{
|
dr["CN_F_PACKING_QTY"] = Convert.ToDecimal(dr["CN_F_PACKING_QTY"]) - upHistory.CN_F_PACKING_QTY;
|
}
|
}
|
}
|
}
|
dr["CN_S_STOCK_CODE"] = GetStockName(dr["CN_S_STOCK_CODE"].ToString().Trim());
|
|
dr["CN_F_PACKING_QTY"] = Math.Round(Convert.ToDecimal(dr["CN_F_PACKING_QTY"]));
|
}
|
return OperateResult.Succeed(null, new
|
{
|
rows = dt,
|
total = total
|
});
|
}
|
|
#region 更新记录
|
/// <summary>
|
/// 更新记录
|
/// </summary>
|
/// <returns></returns>
|
public List<TN_WM_VERSION_LOGEntity> GetVersionLog()
|
{
|
return CreateDAL<InventoryDal>().GetVersionLog();
|
}
|
#endregion
|
|
|
public OperateResult GetHistory(SearchModel searchModel)
|
{
|
DataTable dt = CreateDAL<InventoryDal>().GetHistory(searchModel);
|
dt.Columns.Add("CN_F_ALL_QTY", Type.GetType("System.String"));
|
dt.Columns.Add("CN_N_ROW_NO", Type.GetType("System.String"));
|
// dt.Columns.Add("CN_F_PLANNED_QTY", Type.GetType("System.String"));
|
//dt.Columns.Add("CN_S_SELECT", Type.GetType("System.String"));
|
dt.Columns["CN_S_STOCK_CODE"].ReadOnly = false;
|
//decimal fWeight = 0;
|
if (dt.Rows.Count > 0)
|
{
|
Log.Info("InventoryBll", "1");
|
//获取物料
|
// List<string> itemCodelist = dt.AsEnumerable().Select(x => x.Field<string>("CN_S_ITEM_CODE")).ToList();
|
// List<AutoBomItemEntity> items = CreateDAL<TN_WMS_ITEMDAL>().GetItemList(itemCodelist);
|
foreach (DataRow dr in dt.Rows)
|
{
|
dr["CN_S_STOCK_CODE"] = GetStockName(dr["CN_S_STOCK_CODE"].ToString().Trim());
|
//if (!string.IsNullOrEmpty(dr["CN_S_ITEM_CODE"].ToString()))
|
//{
|
// List<AutoBomItemEntity> item = items.Where(x => x.CN_S_ITEM_CODE.Equals(dr["CN_S_ITEM_CODE"].ToString())).ToList();
|
// fWeight = item.Count == 0 ? 0 : item[0].CN_F_NW;
|
// if(fWeight <=0)
|
// {
|
// dr["CN_F_PLANNED_QTY"] = "0";
|
// }
|
// else
|
// {
|
// dr["CN_F_PLANNED_QTY"] =Math.Round(decimal.Parse(dr["CN_F_QUANTITY"].ToString())/ fWeight).ToString();
|
// }
|
//}
|
}
|
}
|
return OperateResult.Succeed("", dt);
|
}
|
|
public OperateResult SetPrintNo(dynamic data)
|
{
|
return CreateDAL<InventoryDal>().SetPrintNo(data);
|
}
|
|
public OperateResult CancelPrintNo(string orderNo, string orderType)
|
{
|
var result = CreateDAL<InventoryDal>().CancelPrintNo(orderNo, orderType);
|
if (result.AffectedRows <= 0)
|
{
|
return OperateResult.Error("未找到需要取消的数据！");
|
}
|
return result;
|
}
|
}
|
}
|
