using HH.WMS.Common;
|
using HH.WMS.Entitys;
|
using HH.WMS.Entitys.Basic;
|
using HH.WMS.Entitys.Common;
|
using HH.WMS.Entitys.Entitys;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace HH.WMS.DAL.InStock
|
{
|
public class TN_WM_ARRIVALDAL : DapperBaseDAL
|
{
|
#region 更新到货单状态
|
/// <summary>
|
/// 提交到货单-主表
|
/// </summary>
|
/// <param name="entity">审批实体</param>
|
/// <param name="examineType">审批类型（已提交、已审核、已反审、已驳回）</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
|
public OperateResult ExamineMst(List<TN_WM_B_EXAMINEEntity> entity, string examineType, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var model in entity)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(" UPDATE TN_WM_ARRIVAL_MST SET CN_S_STATE=@CN_S_STATE,CN_S_AUDITOR=@CN_S_AUDITOR,CN_T_AUDIT_TIME=@CN_T_AUDIT_TIME,CN_S_AUDIT_REMARK=@CN_S_AUDIT_REMARK WHERE CN_S_ARRIVAL_NO=@CN_S_ARRIVAL_NO");
|
|
result = ExecuteTranSql(strSql.ToString(), new
|
{
|
CN_S_STATE = examineType,
|
CN_S_ARRIVAL_NO = model.CN_S_FROM_NO,
|
CN_S_AUDITOR = model.CN_S_CREATOR,
|
CN_T_AUDIT_TIME = DateTime.Now,
|
CN_S_AUDIT_REMARK = model.CN_S_OPINION
|
}, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
|
/// <summary>
|
/// 提交到货单-子表
|
/// </summary>
|
/// <param name="entity">审批实体</param>
|
/// <param name="examineType">审批类型（已提交、已审核、已反审、已驳回）</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
|
public OperateResult ExamineDtl(List<TN_WM_B_EXAMINEEntity> entity, string examineType, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var model in entity)
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append(" UPDATE TN_WM_ARRIVAL_DTL SET CN_S_STATE=@CN_S_STATE WHERE CN_S_ARRIVAL_NO=@CN_S_ARRIVAL_NO");
|
|
result = ExecuteTranSql(strSql.ToString(), new
|
{
|
CN_S_STATE = examineType,
|
CN_S_ARRIVAL_NO = model.CN_S_FROM_NO
|
}, trans);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
#endregion
|
|
#region 更改到货单状态
|
/// <summary>
|
/// 更改到货单状态(已码盘)
|
/// </summary>
|
/// <param name="list">更新列表</param>
|
/// <param name="state">状态</param>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-17</History>
|
public OperateResult UpdateArrivalState(List<TrayItemRow> list, string state)
|
{
|
OperateResult result = new OperateResult();
|
foreach (var item in list)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
sbStr.Append(" IF EXISTS( SELECT CN_S_ARRIVAL_NO FROM TN_WM_ARRIVAL_DTL P LEFT JOIN ");
|
sbStr.Append(" (SELECT SUM(CN_F_QUANTITY) CN_F_QUANTITY,CN_S_ITEM_CODE,CN_S_LOT_NO FROM TN_WM_B_TRAY_ITEM_REL ");
|
sbStr.Append(" WHERE CN_S_LOT_NO='" + item.CN_S_LOT_NO + "' GROUP BY CN_S_LOT_NO,CN_S_ITEM_CODE) DT ON P.CN_S_ITEM_CODE=DT.CN_S_ITEM_CODE ");
|
sbStr.Append(" WHERE DT.CN_F_QUANTITY>=P.CN_F_QUANTITY AND P.CN_S_ARRIVAL_NO='" + item.CN_S_OP_NO + "') ");
|
sbStr.Append(" UPDATE TN_WM_ARRIVAL_DTL SET CN_S_STATE='" + state + "' WHERE CN_S_ARRIVAL_NO='" + item.CN_S_OP_NO + "' ");
|
result = ExecuteTranSql(sbStr.ToString(), null, null);
|
if (result.Status == ResultStatus.Error)
|
return result;
|
}
|
return result;
|
}
|
#endregion
|
|
#region 入库单联机新增-选择来源
|
/// <summary>
|
/// 入库单页面，选择来源单据
|
/// </summary>
|
/// <param name="searchModel">实体</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-22</History>
|
public OperateResult GetFromArrivalList(SearchModel searchModel)
|
{
|
var condition = searchModel.SearchCondition;
|
string ConStr = "where 1=1 ";
|
if (condition != null)
|
{
|
if (!string.IsNullOrEmpty(condition.CN_S_ARRIVAL_NO.ToString()))
|
{
|
ConStr = ConStr + " and CN_S_ARRIVAL_NO like '%" + Util.ToStringInput(condition.CN_S_ARRIVAL_NO) + "%'";
|
}
|
if (!string.IsNullOrEmpty(condition.CN_S_OP_TYPE.ToString()))
|
{
|
ConStr = ConStr + " and CN_S_OP_TYPE = '" + Util.ToStringInput(condition.CN_S_OP_TYPE) + "'";
|
}
|
|
}
|
string sql = @"(select CN_S_ARRIVAL_NO CN_S_OP_NO,*,CN_S_VENDOR_NO CN_S_VENDOR_CODE from TN_WM_ARRIVAL_MST WHERE CN_S_STATE in ('" + Constants.State_Audit + "','" + Constants.State_BeenCheck + "')) T " + ConStr;
|
|
OperateResult result = new OperateResult();
|
|
return ExecutePagingResult(sql, searchModel.PageIndex, searchModel.PageSize, "", "");
|
}
|
|
public List<TN_WM_ARRIVAL_DTLEntity> GetFromArrivalDtl(string arrivalNo)
|
{
|
StringBuilder sbStr = new StringBuilder();
|
|
sbStr.Append("SELECT * FROM TN_WM_ARRIVAL_DTL WHERE CN_S_ARRIVAL_NO='" + arrivalNo + "'");
|
|
return ExecuteQuery<TN_WM_ARRIVAL_DTLEntity>(sbStr.ToString());
|
}
|
#endregion
|
|
#region 查询到货单列表
|
/// <summary>
|
/// 查询到货单列表
|
/// </summary>
|
/// <param name="searchModel">实体</param>
|
/// <returns></returns>
|
/// <History>[HANHE(XDL)] CREATED BY 2018-11-24</History>
|
public DataTable GetArrivalList(SearchModel searchModel, out long total, bool isExport)
|
{
|
var condition = searchModel.SearchCondition;
|
|
string sql = " select * from TN_WM_ARRIVAL_MST where 1=1 ";
|
string ConStr = "";
|
if (condition != null)
|
{
|
if (!string.IsNullOrEmpty(condition.CN_S_ARRIVAL_NO.ToString()))
|
{
|
ConStr = ConStr + " and CN_S_ARRIVAL_NO like '%" + Util.ToStringInput(condition.CN_S_ARRIVAL_NO) + "%'";
|
}
|
string state = condition.CN_S_STATE.ToString();
|
if (!string.IsNullOrEmpty(state))
|
{
|
if (state.IndexOf(",") > 0)
|
{
|
state = "'" + state.Replace(",", "','") + "'";
|
ConStr = ConStr + " and CN_S_STATE in (" + state + ")";
|
}
|
else
|
{
|
ConStr = ConStr + " and CN_S_STATE='" + state + "'";
|
}
|
|
}
|
if (!string.IsNullOrEmpty(condition.CN_S_OP_TYPE.ToString()))
|
{
|
ConStr = ConStr + " and CN_S_OP_TYPE = '" + Util.ToStringInput(condition.CN_S_OP_TYPE) + "'";
|
}
|
|
if (!string.IsNullOrEmpty(condition.CN_T_START.ToString()))
|
{
|
ConStr = ConStr + " and CN_T_CREATE >= '" + Util.ToStringInput(condition.CN_T_START) + " 00:00:00'";
|
}
|
if (!string.IsNullOrEmpty(condition.CN_T_END.ToString()))
|
{
|
ConStr = ConStr + " and CN_T_CREATE <= '" + Util.ToStringInput(condition.CN_T_END) + " 23:59:59'";
|
}
|
|
if (!string.IsNullOrEmpty(condition.CN_S_VENDOR_NAME.ToString()))
|
{
|
ConStr = ConStr + " and CN_S_VENDOR_NAME like '%" + Util.ToStringInput(condition.CN_S_VENDOR_NAME) + "%'";
|
}
|
if (!string.IsNullOrEmpty(condition.IsHandWork.ToString()))
|
{
|
if (condition.IsHandWork.ToString() == "true" || condition.IsHandWork.ToString() == "True")
|
ConStr = ConStr + " and (CN_S_FROM_NO='' or CN_S_FROM_NO IS NULL) ";
|
else
|
ConStr = ConStr + " and (CN_S_FROM_NO!='' and CN_S_FROM_NO IS not NULL) ";
|
}
|
}
|
if (!string.IsNullOrEmpty(ConStr))
|
{
|
sql = sql + ConStr;
|
}
|
OperateResult result = new OperateResult();
|
DataTable dt = new DataTable();
|
if (!isExport)
|
{
|
return ExecutePagingData(" (" + sql + ") t ", searchModel.PageIndex, searchModel.PageSize, out total, "", "");
|
}
|
else
|
{
|
total = 0;//无效
|
return ExecuteDataTable(sql, "");
|
}
|
}
|
#endregion
|
|
#region 查询到货单主表
|
/// <summary>
|
/// 查询到货单主表
|
/// </summary>
|
/// <returns></returns>
|
/// <History> [HANHE(lt)] CREATED BY 2018-11-24</History>
|
public DataTable GetMstList(string sqlwhere)
|
{
|
string sql = "select * from TN_WM_ARRIVAL_MST {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteDataTable(sql);
|
}
|
#endregion
|
|
#region 查询到货单子表
|
/// <summary>
|
/// 查询到货单子表
|
/// </summary>
|
/// <returns></returns>
|
/// <History> [HANHE(lt)] CREATED BY 2018-11-24</History>
|
public DataTable GetDtlList(string sqlwhere)
|
{
|
string sql = "select * from TN_WM_ARRIVAL_DTL {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteDataTable(sql);
|
}
|
#endregion
|
|
#region 查询到货单
|
/// <summary>
|
/// 查询到货单子表
|
/// </summary>
|
/// <returns></returns>
|
/// <History> [HANHE(lt)] CREATED BY 2018-11-24</History>
|
public DataTable GetMstJoinDtl(string sqlwhere)
|
{
|
string sql = "SELECT * FROM tn_wm_arrival_mst AS a INNER JOIN tn_wm_arrival_dtl AS b ON b.CN_S_ARRIVAL_NO = a.CN_S_ARRIVAL_NO {0}";
|
sql = string.Format(sql, sqlwhere);
|
return ExecuteDataTable(sql);
|
}
|
#endregion
|
|
#region 获取OMS确认数据
|
/// <summary>
|
/// 获取OMS确认数据
|
/// </summary>
|
/// <param name="ArrivalNo"></param>
|
/// <returns></returns>
|
public DataTable GetArrivalOmsConfirmData(string ArrivalNo)
|
{
|
string sql = @"SELECT ARRM.CN_S_OP_TYPE,INM.CN_S_OP_NO,ARRD.CN_N_FROM_ROW_NO,INM.CN_S_FROM_NO,INM.CN_S_OWNER,INM.CN_S_STOCK_CODE,INM.CN_GUID,INM.CN_S_STATE,
|
ARRD.CN_S_ITEM_CODE,ARRD.CN_S_ITEM_NAME,ARRD.CN_F_QUANTITY PLANQTY,ARRD.CN_F_QUANTITY ACTUALQTY
|
FROM TN_WM_ARRIVAL_DTL ARRD
|
LEFT JOIN TN_WM_ARRIVAL_MST ARRM ON ARRM.CN_GUID=ARRD.CN_S_MSTGUID
|
LEFT JOIN tn_wm_in_mst INM ON INM.CN_S_OP_NO=ARRM.CN_S_FROM_NO
|
WHERE ARRD.CN_S_ARRIVAL_NO='" + ArrivalNo + @"' AND INM.CN_S_OP_TYPE!='销售退库' AND INM.CN_S_FROM_NO IS NOT NULL ";
|
return ExecuteDataTable(sql);
|
}
|
#endregion
|
|
/// <summary>
|
/// 升累积到货数的量
|
/// </summary>
|
/// <param name="opNoList"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult UpdateAddupAllot(string opNoList, IDbTransaction trans)
|
{
|
string sql = "UPDATE TN_WM_ARRIVAL_DTL SET CN_F_ADDUP_ALLOT_QTY=ISNULL(CN_F_ADDUP_ALLOT_QTY,0)+ISNULL(CN_F_QUANTITY,0) ";
|
sql = sql + " WHERE CN_S_ARRIVAL_NO in ('" + opNoList + "') ";
|
return ExecuteTranSql(sql, null, trans);
|
}
|
|
public DataTable GetArrivalDtlData(string arrivalNo)
|
{
|
string sql = @" SELECT d.*,INM.CN_S_OWNER,m.CN_S_DELIVERY_NOTE,m.CN_S_OP_TYPE,m.CN_S_STOCK_CODE
|
,m.CN_T_DATE,m.CN_S_LOT_CODE,m.CN_S_DELIVERY_NOTE,m.CN_S_VENDOR_NO,m.CN_S_VENDOR_NAME
|
,IND.CN_F_ACTUAL_PRICE ,IND.CN_F_ACTUAL_MONEY,INM.CN_S_FROM_NO as CN_S_CG_FROM_NO,IND.CN_N_ROW_NO
|
FROM TN_WM_ARRIVAL_DTL d left join tn_wm_arrival_mst m on d.CN_S_ARRIVAL_NO=m.CN_S_ARRIVAL_NO
|
left join TN_WM_IN_MST INM on inm.CN_S_OP_NO=m.CN_S_FROM_NO
|
LEFT JOIN TN_WM_IN_DTL IND ON IND.CN_S_OP_NO=INM.CN_S_OP_NO and d.CN_N_FROM_ROW_NO=ind.CN_N_ROW_NO
|
WHERE d.CN_S_ARRIVAL_NO='" + arrivalNo + @"'";
|
return ExecuteDataTable(sql);
|
|
}
|
|
/// <summary>
|
/// 降累积到货数的量
|
/// </summary>
|
/// <param name="opNoList"></param>
|
/// <param name="trans"></param>
|
/// <returns></returns>
|
public OperateResult DeleteAddupAllot(string opNoList, IDbTransaction trans)
|
{
|
OperateResult result = new OperateResult();
|
string sql = @" UPDATE TN_WM_ARRIVAL_DTL SET CN_F_ADDUP_ALLOT_QTY=ISNULL(CN_F_ADDUP_ALLOT_QTY,0)-ISNULL(CN_F_QUANTITY,0) WHERE CN_S_ARRIVAL_NO in (" + opNoList + ")";
|
result = ExecuteTranSql(sql.ToString(), null, trans);
|
return result;
|
}
|
|
public DataTable GetPutPosition(string arrivalCode, string itemCode)
|
{
|
string sql = @" SELECT DTL.CN_S_PUT_POSITION,DTL.CN_S_ITEM_CODE,DTL.CN_S_ITEM_NAME,DTL.CN_S_ITEM_STATE,MST.CN_S_OWNER,MST.CN_S_LOT_CODE,DTL.CN_S_PRODUCTION_BATCH
|
FROM TN_WM_ARRIVAL_DTL DTL left join TN_WM_ARRIVAL_MST MST ON DTL.CN_S_ARRIVAL_NO=MST.CN_S_ARRIVAL_NO
|
WHERE DTL.CN_S_ARRIVAL_NO='" + arrivalCode + "' AND DTL.CN_S_ITEM_CODE='" + itemCode + "'";
|
return ExecuteDataTable(sql);
|
}
|
}
|
}
|
