using HanHe.Utility.Data;
|
using HH.WMS.Entitys;
|
using HH.WMS.Entitys.Check;
|
using HH.WMS.Entitys.Common;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using System.Data.OracleClient;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace HH.WMS.DAL.Check
|
{
|
public class TN_WM_CHECK_MSTDAL : DapperBaseDAL
|
{
|
#region 获取分页列表
|
/// <summary>
|
/// 获取分页列表
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/7/3</history>
|
public DataTable GetDataTable(dynamic obj)
|
{
|
var isFirstLevelOrg = obj.Value<bool>("isFirstLevelOrg");
|
var currentOrgCode = obj.Value<string>("currentOrgCode");
|
string sqlWhere = obj.sqlWhere;
|
if (!isFirstLevelOrg)
|
{
|
//sqlWhere += " AND CN_S_NEXT_ORG_CODE='" + currentOrgCode + "' ";
|
sqlWhere += " AND ((CN_S_CURR_ORG_CODE='" + currentOrgCode + "' or CN_S_NEXT_ORG_CODE='" + currentOrgCode + "' ) OR CN_S_NEXT_ORG_CODE='') ";
|
}
|
|
DbCommand cmd = DataAccess.GetStoredProcCommand("prc_query");
|
this.DataAccess.AddInParameter(cmd, "TableName", ComDbType.CHAR, "TN_WM_CHECK_MST");
|
this.DataAccess.AddInParameter(cmd, "WhereStr", ComDbType.CHAR, sqlWhere);
|
this.DataAccess.AddInParameter(cmd, "OrderByStr", ComDbType.CHAR, obj.OrdeBy);
|
this.DataAccess.AddInParameter(cmd, "PageSize", ComDbType.INT, obj.pageSize);
|
this.DataAccess.AddInParameter(cmd, "PageIndex", ComDbType.INT, obj.pageIndex);
|
this.DataAccess.AddOutParameter(cmd, "TotalPage", ComDbType.INT, 4);
|
this.DataAccess.AddOutParameter(cmd, "TotalRecord", ComDbType.INT, 4);
|
|
//如果是oracle 增加特性
|
if (!string.IsNullOrEmpty(Now_dbType) && Now_dbType.Equals("ORACLE"))
|
{
|
//处理游标类型 因DbType无游标类型,游标类型单独处理
|
ComDbType.AddOrcOutParameter(cmd, "v_cur", OracleType.Cursor);
|
}
|
try
|
{
|
//获得列表
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
obj.PageCount = Convert.ToInt32(this.DataAccess.GetParameterValue(cmd, "TotalRecord"));
|
return dt;
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
}
|
|
/// <summary>
|
/// 获取分页列表
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/7/3</history>
|
public DataTable GetDataTable(string sqlWhere, string OrdeBy, int pageSize, int pageIndex)
|
{
|
DbCommand cmd = DataAccess.GetStoredProcCommand("prc_query");
|
this.DataAccess.AddInParameter(cmd, "TableName", ComDbType.CHAR, "TN_WM_CHECK_MST");
|
this.DataAccess.AddInParameter(cmd, "WhereStr", ComDbType.CHAR, sqlWhere);
|
this.DataAccess.AddInParameter(cmd, "OrderByStr", ComDbType.CHAR, OrdeBy);
|
this.DataAccess.AddInParameter(cmd, "PageSize", ComDbType.INT, pageSize);
|
this.DataAccess.AddInParameter(cmd, "PageIndex", ComDbType.INT, pageIndex);
|
this.DataAccess.AddOutParameter(cmd, "TotalPage", ComDbType.INT, 4);
|
this.DataAccess.AddOutParameter(cmd, "TotalRecord", ComDbType.INT, 4);
|
|
//如果是oracle 增加特性
|
if (!string.IsNullOrEmpty(Now_dbType) && Now_dbType.Equals("ORACLE"))
|
{
|
//处理游标类型 因DbType无游标类型,游标类型单独处理
|
ComDbType.AddOrcOutParameter(cmd, "v_cur", OracleType.Cursor);
|
}
|
try
|
{
|
//获得列表
|
DataTable dt = DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
//obj.PageCount = Convert.ToInt32(this.DataAccess.GetParameterValue(cmd, "TotalRecord"));
|
return dt;
|
}
|
catch (Exception)
|
{
|
|
throw;
|
}
|
}
|
#endregion
|
|
#region 查询盘点业务主表
|
/// <summary>
|
/// 查询盘点业务主表
|
/// </summary>
|
/// <param name="sqlwhere">查询条件</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/7/3</history>
|
public DataTable Getentity(string sqlwhere)
|
{
|
string sql = "SELECT * FROM TN_WM_CHECK_MST WHERE 1=1 {0}";
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteDataTable(this.DataAccess, cmd);
|
}
|
#endregion
|
|
/// <summary>
|
/// 获取货位物料信息
|
/// </summary>
|
/// <param name="sqlwhere">查询条件</param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/7/12</history>
|
public List<TN_WM_CHECK_DTLEntity> Getlist(string sqlwhere, string type)
|
{
|
var sql = "";
|
if (type == "物料盘点")
|
{
|
sql = @"select * from (SELECT a.CN_S_STOCK_CODE,a.CN_S_STOCK_AREA,b.CN_S_ITEM_CODE,b.CN_S_ITEM_NAME,CN_S_ITEM_STATE,'' AS CN_S_LOCATION_CODE,'' as CN_S_LOT_NO,b.CN_S_OWNER,b.CN_S_MODEL,b.CN_S_FIGURE_NO,SUM(CN_F_QUANTITY) AS CN_F_QUANTITY, CN_S_MEASURE_UNIT FROM dbo.tn_wm_b_tray_location AS a LEFT JOIN TN_WM_B_TRAY_ITEM_MST AS b ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE
|
GROUP BY a.CN_S_STOCK_CODE,a.CN_S_STOCK_AREA,b.CN_S_ITEM_CODE,b.CN_S_ITEM_NAME,CN_S_ITEM_STATE,b.CN_S_OWNER,b.CN_S_MODEL,b.CN_S_FIGURE_NO,CN_S_MEASURE_UNIT) b {0} ";
|
}
|
else
|
{
|
|
sql = @"select * from (SELECT a.CN_S_STOCK_CODE,a.CN_S_STOCK_AREA,a.CN_S_LOCATION_CODE,b.CN_S_ITEM_CODE,b.CN_S_ITEM_NAME,CN_S_ITEM_STATE,'' as CN_S_LOT_NO,b.CN_S_OWNER,b.CN_S_MODEL,b.CN_S_FIGURE_NO,ISNULL(CN_F_QUANTITY,0) AS CN_F_QUANTITY, CN_S_MEASURE_UNIT FROM
|
dbo.tn_wm_b_tray_location AS a
|
LEFT JOIN TN_WM_B_TRAY_ITEM_MST AS b ON a.CN_S_TRAY_CODE=b.CN_S_TRAY_CODE
|
) b {0} ";
|
}
|
sql = string.Format(sql, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return DataAccessExtensive.ExecuteListEntity<TN_WM_CHECK_DTLEntity>(this.DataAccess, cmd, SetEntity);
|
|
}
|
|
/// <summary>
|
/// 设置实体的各列
|
/// </summary>
|
private void SetEntity(TN_WM_CHECK_DTLEntity entity, IDataReader reader)
|
{
|
SetEntityUti(entity, "CN_S_STOCK_CODE", "CN_S_STOCK_CODE", reader);
|
SetEntityUti(entity, "CN_S_STOCK_AREA", "CN_S_STOCK_AREA", reader);
|
SetEntityUti(entity, "CN_S_ITEM_CODE", "CN_S_ITEM_CODE", reader);
|
SetEntityUti(entity, "CN_S_ITEM_NAME", "CN_S_ITEM_NAME", reader);
|
SetEntityUti(entity, "CN_S_ITEM_STATE", "CN_S_ITEM_STATE", reader);
|
SetEntityUti(entity, "CN_S_LOT_NO", "CN_S_LOT_NO", reader);
|
SetEntityUti(entity, "CN_S_OWNER", "CN_S_OWNER", reader);
|
SetEntityUti(entity, "CN_S_MODEL", "CN_S_MODEL", reader);
|
SetEntityUti(entity, "CN_S_FIGURE_NO", "CN_S_FIGURE_NO", reader);
|
SetEntityUti(entity, "CN_F_QUANTITY", "CN_F_QUANTITY", reader);
|
SetEntityUti(entity, "CN_S_MEASURE_UNIT", "CN_S_MEASURE_UNIT", reader);
|
SetEntityUti(entity, "CN_S_LOCATION_CODE", "CN_S_LOCATION_CODE", reader);
|
|
}
|
|
#region 根据盘点单号修改状态
|
/// <summary>
|
/// 根据盘点单号修改状态
|
/// </summary>
|
/// <param name="CN_S_OP_NO"></param>
|
/// <param name="CN_S_STATE"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/7/12</history>
|
public SqlExecuteResult UpdateStateByNo(string CN_S_OP_NO, string CN_S_STATE, DbTransaction trans)
|
{
|
string sql = "UPDATE dbo.tn_wm_check_mst SET CN_S_STATE='{0}' WHERE CN_S_OP_NO IN ('{1}')";
|
sql = string.Format(sql, CN_S_STATE, CN_S_OP_NO);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return ExecuteCommand(cmd, trans);
|
}
|
#endregion
|
|
|
/// <summary>
|
/// 更新数据
|
/// </summary>
|
/// <history>[HanHe(lt)] CREATED 2018/7/3</history>
|
public OperateResult UpdateMstByOpNo(string state, string sqlwhere, IDbTransaction trans)
|
{
|
string sql = "update tn_wm_check_mst set CN_S_STATE='{0}' {1}";
|
sql = string.Format(sql, state, sqlwhere);
|
DbCommand cmd = DataAccess.GetSqlStringCommand(sql);
|
return ExecuteTranSql(sql, null, trans);
|
}
|
|
#region 空货位 更新货位拓展表
|
/// <summary>
|
/// 更新货位拓展表
|
/// </summary>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/12/20</history>
|
public OperateResult UpdateLocationExt(string locationCodes, IDbTransaction trans)
|
{
|
string sql = @" UPDATE tn_wm_b_location_ext SET CN_S_USE_STATE='空' FROM tn_wm_b_location_ext AS a WITH (NOLOCK)
|
LEFT JOIN dbo.tn_wm_b_tray_location AS b WITH (NOLOCK) ON a.CN_S_LOCATION_CODE=b.CN_S_LOCATION_CODE
|
WHERE a.CN_S_LOCATION_CODE IN ('" + locationCodes + "') AND b.CN_S_LOCATION_CODE IS NULL";
|
return ExecuteTranSql(sql, null, trans);
|
}
|
#endregion
|
|
|
#region 空货位 更新货位拓展表
|
/// <summary>
|
/// 更新货位拓展表
|
/// </summary>
|
/// <returns></returns>
|
/// <history>[HanHe(lt)] CREATED 2018/12/20</history>
|
public OperateResult DeleteTrayLocation(string trayCodes, IDbTransaction trans)
|
{
|
string sql = @" DELETE tn_wm_b_tray_location FROM tn_wm_b_tray_location AS a WITH (NOLOCK)
|
LEFT JOIN TN_WM_B_TRAY_ITEM_MST AS b WITH (NOLOCK) ON b.CN_S_TRAY_CODE = a.CN_S_TRAY_CODE
|
INNER JOIN dbo.tn_wm_tray_info AS c WITH (NOLOCK) ON c.CN_S_TRAY_CODE = a.CN_S_TRAY_CODE
|
WHERE CN_S_TRAY_TYPE='虚拟托盘' AND a.CN_S_TRAY_CODE IN ('" + trayCodes + "') AND b.CN_S_TRAY_CODE IS NULL";
|
return ExecuteTranSql(sql, null, trans);
|
}
|
#endregion
|
|
|
/// <summary>
|
/// 根据盘点单号获取盘点明细
|
/// </summary>
|
/// <param name="opNo"></param>
|
/// <returns></returns>
|
/// <history>[HanHe(dbs)] CREATED 2019/3/28</history>
|
public List<TN_WM_CHECK_LISTEntity> GetCheckList(string opNo,string type)
|
{
|
string sql = string.Empty;
|
if (type.Equals("货位盘点"))
|
{
|
sql = @"select b.CN_S_STOCK_AREA,a.CN_S_LOCATION_CODE,c.CN_S_TRAY_CODE,c.CN_S_TRAY_GRID,c.CN_S_ITEM_CODE,c.CN_S_ITEM_NAME,
|
c.CN_S_MODEL,c.CN_S_FIGURE_NO,c.CN_S_ITEM_STATE,c.CN_S_MEASURE_UNIT,c.CN_S_OWNER,sum(d.CN_F_QUANTITY) CN_F_QUANTITY,
|
d.CN_S_PACKING_UNIT,d.CN_F_PACKING_QTY
|
from tn_wm_check_dtl a
|
join tn_wm_b_tray_location b on a.CN_S_LOCATION_CODE=b.CN_S_LOCATION_CODE
|
left join TN_WM_B_TRAY_ITEM_MST c on b.CN_S_TRAY_CODE=c.CN_S_TRAY_CODE
|
join TN_WM_B_TRAY_ITEM_DTL d on c.CN_GUID=d.CN_PARENT_GUID
|
where CN_S_OP_NO=@CN_S_OP_NO
|
group by b.CN_S_STOCK_AREA,a.CN_S_LOCATION_CODE,c.CN_S_TRAY_CODE,c.CN_S_TRAY_GRID,c.CN_S_ITEM_CODE,c.CN_S_ITEM_NAME,
|
c.CN_S_MODEL,c.CN_S_FIGURE_NO,c.CN_S_ITEM_STATE,c.CN_S_MEASURE_UNIT,c.CN_S_OWNER,
|
d.CN_S_PACKING_UNIT,d.CN_F_PACKING_QTY";
|
}
|
else if (type.Equals("物料盘点"))
|
{
|
sql = @"select
|
d.CN_S_STOCK_AREA,d.CN_S_LOCATION_CODE,d.CN_S_TRAY_CODE,b.CN_S_TRAY_GRID,b.CN_S_ITEM_CODE,b.CN_S_ITEM_NAME,b.CN_S_MODEL,
|
b.CN_S_FIGURE_NO,b.CN_S_ITEM_STATE,b.CN_S_MEASURE_UNIT,b.CN_S_OWNER,sum(c.CN_F_QUANTITY) CN_F_QUANTITY,c.CN_S_PACKING_UNIT,c.CN_F_PACKING_QTY
|
from tn_wm_check_dtl a join TN_WM_B_TRAY_ITEM_MST b on a.CN_S_ITEM_CODE=b.CN_S_ITEM_CODE
|
and a.CN_S_ITEM_STATE=b.CN_S_ITEM_STATE and a.CN_S_OWNER=b.CN_S_OWNER
|
join TN_WM_B_TRAY_ITEM_DTL c on b.CN_GUID=c.CN_PARENT_GUID
|
join tn_wm_b_tray_location d on b.CN_S_TRAY_CODE=d.CN_S_TRAY_CODE where CN_S_OP_NO=@CN_S_OP_NO
|
group by d.CN_S_STOCK_AREA,d.CN_S_LOCATION_CODE,d.CN_S_TRAY_CODE,b.CN_S_TRAY_GRID,b.CN_S_ITEM_CODE,b.CN_S_ITEM_NAME,b.CN_S_MODEL,
|
b.CN_S_FIGURE_NO,b.CN_S_ITEM_STATE,b.CN_S_MEASURE_UNIT,b.CN_S_OWNER,c.CN_S_PACKING_UNIT,c.CN_F_PACKING_QTY";
|
}
|
return ExecuteQuery<TN_WM_CHECK_LISTEntity>(sql, new { CN_S_OP_NO = opNo });
|
}
|
}
|
}
|
