using HH.WMS.Common; using HH.WMS.Entitys; using HH.WMS.Entitys.Common; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; using HH.WMS.Entitys.Basic; namespace HH.WMS.DAL.OutStock { public class TN_WM_OUT_MSTDAL : DapperBaseDAL { #region 获取出库订单所有客户 /// /// 获取出库订单所有客户 /// /// public List GetDistinctCustom() { string sql = "SELECT DISTINCT CN_S_CUSTOMER FROM TN_WM_OUT_MST"; return ExecuteQuery(sql); } #endregion #region 设置合单 /// /// 设置合单 /// /// /// /// public OperateResult UpdateMstMerage(List outMstList, IDbTransaction trans) { string sql = "UPDATE TN_WM_OUT_MST SET CN_S_STATE=@CN_S_STATE,CN_S_PARENT_NO=@CN_S_PARENT_NO WHERE CN_S_OP_NO =@CN_S_OP_NO"; return ExecuteTranSql(sql, outMstList, trans); } #endregion #region 批量修改主子表状态 /// /// 批量修改主子表状态 /// /// /// /// /// public OperateResult UpdateState(List outMstList, IDbTransaction trans) { string sql = "UPDATE TN_WM_OUT_MST SET CN_S_STATE=@CN_S_STATE WHERE CN_S_OP_NO =@CN_S_OP_NO"; var result = ExecuteTranSql(sql, outMstList, trans); if (result.Success) { sql = "UPDATE TN_WM_OUT_DTL SET CN_S_STATE=@CN_S_STATE WHERE CN_S_OP_NO =@CN_S_OP_NO"; List outDtlList = new List(); outMstList.ForEach(p => { if (p.OutDtlList != null && p.OutDtlList.Any()) outDtlList.AddRange(p.OutDtlList); }); if (outDtlList.Any()) result = ExecuteTranSql(sql, outDtlList, trans); } return result; } #endregion #region 修改状态和子表数量 /// /// 修改状态和子表数量 /// /// /// /// public OperateResult UpdateStateAndQty(List outMstList, IDbTransaction trans) { string sql = "UPDATE TN_WM_OUT_MST SET CN_S_STATE=@CN_S_STATE WHERE CN_S_OP_NO =@CN_S_OP_NO"; var result = ExecuteTranSql(sql, outMstList, trans); if (result.Success) { sql = "UPDATE TN_WM_OUT_DTL SET CN_S_STATE=@CN_S_STATE,CN_F_QUANTITY=@CN_F_QUANTITY WHERE CN_S_OP_NO =@CN_S_OP_NO"; List outDtlList = new List(); outMstList.ForEach(p => { if (p.OutDtlList != null && p.OutDtlList.Any()) outDtlList.AddRange(p.OutDtlList); }); if (outDtlList.Any()) result = ExecuteTranSql(sql, outDtlList, trans); } return result; } #endregion #region 修改主表波次 /// /// 修改主表波次 /// /// /// /// public OperateResult UpdateWave(List outMstList, IDbTransaction trans) { string sql = "UPDATE TN_WM_OUT_MST SET CN_S_WAVE_CODE=@CN_S_WAVE_CODE WHERE CN_S_OP_NO =@CN_S_OP_NO"; return ExecuteTranSql(sql, outMstList, trans); } #endregion #region 修改打印状态 /// /// 修改打印状态 /// /// /// /// /// public OperateResult UpdatePrint(List outMstList, PrintType printType, IDbTransaction trans) { string sql = @""; switch (printType) { case PrintType.Logistics: sql = @"UPDATE TN_WM_OUT_MST SET CN_C_LOGISTICS_LIST_PRINT='Y' WHERE CN_S_OP_NO=@CN_S_OP_NO"; break; case PrintType.Invoice: sql = @"UPDATE TN_WM_OUT_MST SET CN_C_INVOICE_PRINT='Y' WHERE CN_S_OP_NO=@CN_S_OP_NO"; break; case PrintType.Sorting: sql = @"UPDATE TN_WM_OUT_MST SET CN_C_SORTING_LIST_PRINT='Y' WHERE CN_S_OP_NO=@CN_S_OP_NO"; break; default: return OperateResult.Succeed(); } if (string.IsNullOrEmpty(sql)) return OperateResult.Error(); return ExecuteTranSql(sql, outMstList, trans); } #endregion #region 根据包装箱编码查询数据 /// /// 根据包装箱编码查询数据 /// /// /// [HanHe(LT)] CREATED 2019/1/8 public DataTable GetOutPack(string sqlwhere) { string sql = "SELECT CONVERT(varchar(100), CN_T_DATE, 111) AS CN_T_DATES,* FROM TN_WM_B_OUT_PACK {0}"; sql = string.Format(sql, sqlwhere); return ExecuteDataTable(sql, null); } /// /// 根据包装箱编码查询数据 /// /// /// [HanHe(LT)] CREATED 2019/1/8 public DataTable GetBoxdtl(string sqlwhere) { string sql = "SELECT ROW_NUMBER() OVER ( ORDER BY CN_S_ITEM_CODE ) AS CN_N_ROW_NO,CN_S_ITEM_CODE,CN_S_ITEM_NAME,SUM(CN_F_QUANTITY) AS CN_F_QUANTITY,CN_S_MEASURE_UNIT FROM TN_WM_B_BOX_DTL WHERE 1=1 {0} GROUP BY CN_S_ITEM_CODE,CN_S_ITEM_NAME,CN_S_MEASURE_UNIT"; sql = string.Format(sql, sqlwhere); return ExecuteDataTable(sql, null); } #endregion public List GetShortageData(string increaseNo) { string sql = @"SELECT OUTMST.* FROM tn_wm_out_dtl B LEFT JOIN TN_WM_INCREASE_INVENTORY_DTL D ON B.CN_S_ITEM_CODE=D.CN_S_ITEM_CODE AND B.CN_S_ITEM_STATE=D.CN_S_ITEM_STATE AND b.CN_S_PRODUCTION_BATCH=D.CN_S_PRODUCTION_BATCH left join TN_WM_INCREASE_INVENTORY_MST M ON D.CN_S_OP_NO=M.CN_S_OP_NO LEFT JOIN TN_WM_OUT_MST OUTMST ON OUTMST.CN_S_OP_NO=B.CN_S_OP_NO AND OUTMST.CN_S_OWNER=M.CN_S_OWNER WHERE M.CN_S_OP_NO='" + increaseNo + "' AND OUTMST.CN_S_STATE='" + Constants.State_Shortage + "'"; return ExecuteQuery(sql); } #region 获取非内销的审核单据 /// /// 获取非内销的审核单据 /// /// public List GetNeNxAuditedList() { string sql = @"SELECT * FROM TN_WM_OUT_MST WHERE CN_S_STATE =@CN_S_STATE AND CN_S_SALES_STYLE!=@CN_S_SALES_STYLE AND CN_B_CANCEL = 0"; return ExecuteQuery(sql, new { CN_S_STATE = Constants.State_Audit, CN_S_SALES_STYLE = Constants.SalesStyle_NX }); } /// /// 搬运时获取是否存在缺货状态的发货通知单 /// /// 打码实体 /// public List GetBuqueOutList(MONGO_PRINT_BARCODE printBarCode) { string sql = @"SELECT CN_F_QUANTITY FROM TN_WM_OUT_DTL DTL LEFT JOIN TN_WM_OUT_MST MST ON DTL.CN_S_OP_NO=MST.CN_S_OP_NO WHERE DTL.CN_S_ITEM_CODE=@CN_S_ITEM_CODE AND DTL.CN_S_ITEM_STATE=@CN_S_ITEM_STATE AND MST.CN_S_LOT_CODE=@CN_S_LOT_CODE AND DTL.CN_S_PRODUCTION_BATCH=@CN_S_PRODUCTION_BATCH AND MST.CN_S_STATE='" + Constants.State_Shortage + "' AND DTL.CN_S_STATE!='已搬运' "; return ExecuteQuery(sql, new { CN_S_ITEM_CODE = printBarCode.CN_S_ITEM_CODE, CN_S_ITEM_STATE = printBarCode.CN_S_ITEM_STATE, CN_S_LOT_CODE = printBarCode.CN_S_LOT_NO, CN_S_PRODUCTION_BATCH = printBarCode.CN_S_PRODUCTION_BATCH }); } #endregion } }