using DapperORMCore.Context.DataContext;
using DapperORMCore.Model.CoreModel;
using DapperORMCore.Repository.IRepositorys;
using Microsoft.Extensions.Configuration;
using NXWMS.IService.NXWMS.Instock;
using NXWMS.Model.AppModels.Condition.Instock;
using NXWMS.Model.AppModels.Result.Balance;
using NXWMS.Model.AppModels.Result.Base;
using NXWMS.Model.AppModels.Result.Instock;
using NXWMS.Model.Common;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WestDistance.DapperORM.Repository.Repositorys;
namespace NXWMS.Service.NXWMS.Instock
{
///
/// 退料单服务
///
[AutoInject(typeof(IWmsInRetreatService), InjectType.Scope)]
public class WmsInRetreatService : ServiceBase, IWmsInRetreatService
{
#region 全局变量、构造注入
///
/// 系统操作仓储中转
///
private IDataRepositoryContext _dataContext;
///
/// SQL节点仓储
///
private ISQLNodeRepository _iSQLNodeRepository;
///
/// 配置
///
private IConfiguration _configuration;
///
/// 构造注入
///
///
///
///
public WmsInRetreatService(IDataRepositoryContext dataRepositoryContext, IConfiguration configuration, ISQLNodeRepository iSQLNodeRepository)
{
this._dataContext = dataRepositoryContext;
this._configuration = configuration;
this._iSQLNodeRepository = iSQLNodeRepository;
}
#endregion
///
/// 分页查询退料单主表数据
///
/// 退料单查询条件实体类对象
///
public OperateResultInfo> GetWmsInRetreatListForPage(WmsInRetreatSearchMd retreatSearchMd)
{
try
{
#region SQL语句生成
StringBuilder sqlCondition = new StringBuilder();
if (!string.IsNullOrEmpty(retreatSearchMd.RetreatNoMsg))
{
sqlCondition.Append($" AND RETREAT_NO = '{retreatSearchMd.RetreatNoMsg}'");
}
if (!string.IsNullOrEmpty(retreatSearchMd.SourceNoMsg))
{
sqlCondition.Append($" AND SOUCE_NO = '{retreatSearchMd.SourceNoMsg}'");
}
if (!string.IsNullOrEmpty(retreatSearchMd.RetreaterMsg))
{
sqlCondition.Append($" AND RETREATER = '{retreatSearchMd.RetreaterMsg}'");
}
if (!string.IsNullOrEmpty(retreatSearchMd.RetreatType))
{
sqlCondition.Append($" AND RETREAT_TYPE = '{retreatSearchMd.RetreatType}'");
}
if (!string.IsNullOrEmpty(retreatSearchMd.RetreatStatus))
{
sqlCondition.Append($" AND RETREAT_STATUS = '{retreatSearchMd.RetreatStatus}'");
}
if (!string.IsNullOrEmpty(retreatSearchMd.BatchNoMsg))
{
sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE BATCH_NO = '{retreatSearchMd.BatchNoMsg}')");
}
if (!string.IsNullOrEmpty(retreatSearchMd.MaterielMsg))
{
sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE MATERIEL_CODE LIKE '%{retreatSearchMd.MaterielMsg}%' OR MATERIEL_NAME LIKE '%{retreatSearchMd.MaterielMsg}%' OR MATERIEL_BARCODE LIKE '%{retreatSearchMd.MaterielMsg}%')");
}
if (!string.IsNullOrEmpty(retreatSearchMd.SupplierMsg))
{
sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE SUPPLIER_CODE LIKE '%{retreatSearchMd.SupplierMsg}%' OR SUPPLIER_NAME LIKE '%{retreatSearchMd.SupplierMsg}%')");
}
if (!string.IsNullOrEmpty(retreatSearchMd.StartCreateTime))
{
sqlCondition.Append($" AND CREATE_TIME >= '{retreatSearchMd.StartCreateTime}'");
}
if (!string.IsNullOrEmpty(retreatSearchMd.EndCreateTime))
{
sqlCondition.Append($" AND CREATE_TIME <= '{retreatSearchMd.EndCreateTime}'");
}
if (!string.IsNullOrEmpty(retreatSearchMd.StartProductTime))
{
sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE PRODUCT_DATE >= '{retreatSearchMd.StartProductTime}')");
}
if (!string.IsNullOrEmpty(retreatSearchMd.EndProductTime))
{
sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE PRODUCT_DATE <= '{retreatSearchMd.EndProductTime}')");
}
if (!string.IsNullOrEmpty(retreatSearchMd.StartExpTime))
{
sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE EXP_DATE >= '{retreatSearchMd.StartExpTime}')");
}
if (!string.IsNullOrEmpty(retreatSearchMd.EndExpTime))
{
sqlCondition.Append($" AND RETREAT_ID IN (SELECT RETREAT_ID FROM VW_WMS_IN_RETREAT_DTL WHERE EXP_DATE <= '{retreatSearchMd.EndExpTime}')");
}
StringBuilder sqlCountRetreatData = new StringBuilder($@"SELECT COUNT(1) FROM VW_WMS_IN_RETREAT WHERE 1=1");
sqlCountRetreatData.Append(sqlCondition.ToString());
int pageStartIndex = (retreatSearchMd.PageNum - 1) * retreatSearchMd.EveryPageQty;
int pageEndIndex = retreatSearchMd.PageNum * retreatSearchMd.EveryPageQty;
StringBuilder sqlQueryRetreatData = new StringBuilder($@"
SELECT
RETREAT_ID,
RETREAT_NO,
SOUCE_NO,
RETREAT_TYPE,
RETREAT_TYPE_NAME,
RETREAT_TIME,
RETREATER,
RETREAT_STATUS,
RETREAT_STATUS_NAME,
[DESCRIBE],
CREATE_BY,
CREATE_NAME,
CREATE_TIME,
UPDATE_BY,
UPDATE_NAME,
UPDATE_TIME
FROM
VW_WMS_IN_RETREAT
WHERE
1=1
{sqlCondition}
ORDER BY UPDATE_TIME DESC,CREATE_TIME DESC
OFFSET {pageStartIndex} ROWS
FETCH NEXT {retreatSearchMd.EveryPageQty} ROWS ONLY
");
#endregion
int dataCount = Convert.ToInt32(new DataRepository(_dataContext).ExecuteScalar(sqlCountRetreatData.ToString()));
List resultList = new DataRepository(_dataContext).Query(sqlQueryRetreatData.ToString()).ToList();
OperateResultInfo> retDataMsg = SuccessStatus(resultList);
retDataMsg.DataCount = dataCount;
return retDataMsg;
}
catch (Exception ex)
{
return FailMessageStatus>($"查询退料单数据发生异常,【{ex.Message}】", null);
}
}
///
/// 根据退料单主键Id查询退料单明细数据
///
/// 退料单对象
///
public OperateResultInfo GetWmsInRetreatDtlListForID(WmsInRetreatResult wmsInRetreat)
{
try
{
#region SQL语句生成
string sqlQueryWmsInRetreat = $@"
SELECT
RETREAT_ID,
RETREAT_NO,
SOUCE_NO,
RETREAT_TYPE,
RETREAT_TYPE_NAME,
RETREAT_TIME,
RETREATER,
RETREAT_STATUS,
RETREAT_STATUS_NAME,
[DESCRIBE],
CREATE_BY,
CREATE_NAME,
CREATE_TIME,
UPDATE_BY,
UPDATE_NAME,
UPDATE_TIME
FROM
VW_WMS_IN_RETREAT
WHERE
RETREAT_ID = '{wmsInRetreat.RETREAT_ID}'
";
string strWhere = string.Empty;
if (wmsInRetreat.REMARKS1 == "查询已删除明细数据")
{
strWhere = "1=1";
}
else if (wmsInRetreat.REMARKS1 == "查询未组盘完成的退料单明细")
{
strWhere = "RETREAT_DTL_STATUS < 99 AND RETREAT_TRAY_QTY < RETREAT_QTY";
}
else
{
strWhere = "RETREAT_DTL_STATUS < 111";
}
string sqlQueryWmsRetreatDtl = $@"
SELECT
*
FROM
VW_WMS_IN_RETREAT_DTL
WHERE
RETREAT_ID = '{wmsInRetreat.RETREAT_ID}'
AND {strWhere}
ORDER BY RETREAT_DTL_STATUS,RETREAT_DTL_ID
";
#endregion
List resultList = new DataRepository(_dataContext).Query(sqlQueryWmsInRetreat).ToList();
List resultDtlList = new DataRepository(_dataContext).Query(sqlQueryWmsRetreatDtl).ToList();
wmsInRetreat = resultList[0];
wmsInRetreat.WmsInRetreatDtlList = resultDtlList;
OperateResultInfo retDataMsg = SuccessStatus(wmsInRetreat);
return retDataMsg;
}
catch (Exception ex)
{
return FailMessageStatus($"查询退料单明细数据发生异常,【{ex.Message}】", null);
}
}
///
/// 新增退料单数据
///
/// 退料单主表对象
///
public OperateResultInfo AddWmsInRetreatData(WmsInRetreatResult wmsInRetreat)
{
try
{
#region SQL语句生成
List sqlList = new List();
wmsInRetreat.RETREAT_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("Retreat_Id"));
wmsInRetreat.RETREAT_NO = new DataRepository(_dataContext).GetSequenceMsg("Retreat_No");
wmsInRetreat.RETREAT_STATUS = 0;
StringBuilder sqlAddWmsRetreat = new StringBuilder();
string retreatTime = DateTime.Compare(wmsInRetreat.RETREAT_TIME, new DateTime()) == 0 ? "NULL" : $"'{wmsInRetreat.RETREAT_TIME}'";
sqlAddWmsRetreat.Append($@"
INSERT INTO [WMS_IN_RETREAT] (
[RETREAT_ID],
[RETREAT_NO],
[RETREATER],
[RETREAT_TYPE],
[RETREAT_TIME],
[SOUCE_NO],
[RETREAT_STATUS],
[DESCRIBE],
[CREATE_BY],
[CREATE_TIME],
[UPDATE_BY],
[UPDATE_TIME],
[DATA_VERSION],
[REMARKS1],
[REMARKS2],
[REMARKS3],
[REMARKS4],
[REMARKS5]
)
VALUES
(
'{wmsInRetreat.RETREAT_ID}',
'{wmsInRetreat.RETREAT_NO}',
'{wmsInRetreat.RETREATER}',
'{wmsInRetreat.RETREAT_TYPE}',
{retreatTime},
'{wmsInRetreat.SOUCE_NO}',
'{wmsInRetreat.RETREAT_STATUS}',
'{wmsInRetreat.DESCRIBE}',
'{wmsInRetreat.CREATE_BY}',
getdate(),
'{wmsInRetreat.UPDATE_BY}',
getdate(),
0,
NULL,
NULL,
NULL,
NULL,
NULL
);
");
sqlList.Add(sqlAddWmsRetreat.ToString());
foreach (WmsInRetreatDtlResult item in wmsInRetreat.WmsInRetreatDtlList)
{
StringBuilder sqlAddWmsInRetreatDtl = new StringBuilder();
item.RETREAT_DTL_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("Retreat_Dtl_Id"));
item.RETREAT_ID = wmsInRetreat.RETREAT_ID;
item.RETREAT_DTL_STATUS = 0;
sqlAddWmsInRetreatDtl.Append($@"
INSERT INTO [WMS_IN_RETREAT_DTL] (
[RETREAT_DTL_ID],
[RETREAT_ID],
[MATERIEL_CODE],
[MATERIEL_NAME],
[MATERIEL_BARCODE],
[MATERIEL_SPEC],
[BATCH_NO],
[PACKAGE_CODE],
[UNIT_CODE],
[RETREAT_QTY],
[RETREAT_TRAY_QTY],
[PUTAWAY_QTY],
[SUPPLIER_CODE],
[SUPPLIER_NAME],
[PRODUCT_DATE],
[EXP_DATE],
[RETREAT_DTL_STATUS],
[INSPECTION_RESULT],
[ITEM_STATUS],
[DESCRIBE],
[CREATE_BY],
[CREATE_TIME],
[UPDATE_BY],
[UPDATE_TIME],
[DATA_VERSION],
[REMARKS1],
[REMARKS2],
[REMARKS3],
[REMARKS4],
[REMARKS5]
)
VALUES
(
'{item.RETREAT_DTL_ID}',
'{item.RETREAT_ID}',
'{item.MATERIEL_CODE}',
'{item.MATERIEL_NAME}',
'{item.MATERIEL_BARCODE}',
'{item.MATERIEL_SPEC}',
'{item.BATCH_NO}',
'{item.PACKAGE_CODE}',
'{item.UNIT_CODE}',
'{item.RETREAT_QTY}',
'{item.RETREAT_TRAY_QTY}',
'{item.PUTAWAY_QTY}',
'{item.SUPPLIER_CODE}',
'{item.SUPPLIER_NAME}',
'{item.PRODUCT_DATE}',
'{item.EXP_DATE}',
'0',
'Wait',
'1',
'{item.DESCRIBE}',
'{item.CREATE_BY}',
getdate(),
'{item.UPDATE_BY}',
getdate(),
0,
NULL,
NULL,
NULL,
NULL,
NULL
);
");
sqlList.Add(sqlAddWmsInRetreatDtl.ToString());
}
#endregion
int row = new DataRepository(_dataContext).ExecSqlListTran(sqlList);
if (row > 0)
{
return SuccessMessageStatus("新增退料单数据成功!", row);
}
else
{
return FailMessageStatus("新增退料单数据失败!", row);
}
}
catch (Exception ex)
{
return FailMessageStatus($"新增退料单数据发生异常,【{ex.Message}】");
}
}
///
/// 修改退料单数据
///
/// 退料主表对象
///
public OperateResultInfo EditWmsInRetreatData(WmsInRetreatResult wmsInRetreat)
{
try
{
#region SQL语句生成
List sqlList = new List();
string retreatTime = DateTime.Compare(wmsInRetreat.RETREAT_TIME, new DateTime()) == 0 ? "NULL" : $"'{wmsInRetreat.RETREAT_TIME}'";
string sqlUpdateWmsInRetreat = $@"
UPDATE [WMS_IN_RETREAT]
SET
[RETREAT_NO] = '{wmsInRetreat.RETREAT_NO}',
[RETREATER] = '{wmsInRetreat.RETREATER}',
[RETREAT_TYPE] = '{wmsInRetreat.RETREAT_TYPE}',
[RETREAT_TIME] = {retreatTime},
[SOUCE_NO] = '{wmsInRetreat.SOUCE_NO}',
[RETREAT_STATUS] = '{wmsInRetreat.RETREAT_STATUS}',
[DESCRIBE] = '{wmsInRetreat.DESCRIBE}',
[UPDATE_BY] = '{wmsInRetreat.UPDATE_BY}',
[UPDATE_TIME] = GETDATE(),
[DATA_VERSION] = [DATA_VERSION] + 1
WHERE
[RETREAT_ID] = '{wmsInRetreat.RETREAT_ID}';
";
sqlList.Add(sqlUpdateWmsInRetreat);
foreach (WmsInRetreatDtlResult item in wmsInRetreat.WmsInRetreatDtlList)
{
if (item.REMARKS1 == "更新" || item.REMARKS1 == "删除")
{
string sqlUpdateWmsInRetreatDtl = $@"
UPDATE [WMS_IN_RETREAT_DTL]
SET
[MATERIEL_CODE] = '{item.MATERIEL_CODE}',
[MATERIEL_NAME] = '{item.MATERIEL_NAME}',
[MATERIEL_BARCODE] = '{item.MATERIEL_BARCODE}',
[MATERIEL_SPEC] = '{item.MATERIEL_SPEC}',
[BATCH_NO] = '{item.BATCH_NO}',
[PACKAGE_CODE] = '{item.PACKAGE_CODE}',
[UNIT_CODE] = '{item.UNIT_CODE}',
[RETREAT_QTY] = '{item.RETREAT_QTY}',
[RETREAT_TRAY_QTY] = '{item.RETREAT_TRAY_QTY}',
[PUTAWAY_QTY] = '{item.PUTAWAY_QTY}',
[SUPPLIER_CODE] = '{item.SUPPLIER_CODE}',
[SUPPLIER_NAME] = '{item.SUPPLIER_CODE}',
[PRODUCT_DATE] = '{item.PRODUCT_DATE}',
[EXP_DATE] = '{item.EXP_DATE}',
[RETREAT_DTL_STATUS] = '{item.RETREAT_DTL_STATUS}',
[INSPECTION_RESULT] = '{item.INSPECTION_RESULT}',
[ITEM_STATUS] = '{item.ITEM_STATUS}',
[DESCRIBE] = '{item.DESCRIBE}',
[UPDATE_BY] = '{item.UPDATE_BY}',
[UPDATE_TIME] = getdate(),
[DATA_VERSION] = [DATA_VERSION] + 1
WHERE
[RETREAT_DTL_ID] = '{item.RETREAT_DTL_ID}'
AND [RETREAT_ID] = '{wmsInRetreat.RETREAT_ID}';
";
sqlList.Add(sqlUpdateWmsInRetreatDtl);
}
if (item.REMARKS1 == "添加")
{
StringBuilder sqlAddWmsInRetreatDtl = new StringBuilder();
item.RETREAT_DTL_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("Retreat_Dtl_Id"));
item.RETREAT_ID = wmsInRetreat.RETREAT_ID;
item.RETREAT_DTL_STATUS = 0;
sqlAddWmsInRetreatDtl.Append($@"
INSERT INTO [WMS_IN_RETREAT_DTL] (
[RETREAT_DTL_ID],
[RETREAT_ID],
[MATERIEL_CODE],
[MATERIEL_NAME],
[MATERIEL_BARCODE],
[MATERIEL_SPEC],
[BATCH_NO],
[PACKAGE_CODE],
[UNIT_CODE],
[RETREAT_QTY],
[RETREAT_TRAY_QTY],
[PUTAWAY_QTY],
[SUPPLIER_CODE],
[SUPPLIER_NAME],
[PRODUCT_DATE],
[EXP_DATE],
[RETREAT_DTL_STATUS],
[INSPECTION_RESULT],
[ITEM_STATUS],
[DESCRIBE],
[CREATE_BY],
[CREATE_TIME],
[UPDATE_BY],
[UPDATE_TIME],
[DATA_VERSION],
[REMARKS1],
[REMARKS2],
[REMARKS3],
[REMARKS4],
[REMARKS5]
)
VALUES
(
'{item.RETREAT_DTL_ID}',
'{item.RETREAT_ID}',
'{item.MATERIEL_CODE}',
'{item.MATERIEL_NAME}',
'{item.MATERIEL_BARCODE}',
'{item.MATERIEL_SPEC}',
'{item.BATCH_NO}',
'{item.PACKAGE_CODE}',
'{item.UNIT_CODE}',
'{item.RETREAT_QTY}',
'{item.RETREAT_TRAY_QTY}',
'{item.PUTAWAY_QTY}',
'{item.SUPPLIER_CODE}',
'{item.SUPPLIER_NAME}',
'{item.PRODUCT_DATE}',
'{item.EXP_DATE}',
'0',
'Wait',
'1',
'{item.DESCRIBE}',
'{item.CREATE_BY}',
getdate(),
'{item.UPDATE_BY}',
getdate(),
0,
NULL,
NULL,
NULL,
NULL,
NULL
);
");
sqlList.Add(sqlAddWmsInRetreatDtl.ToString());
}
}
#endregion
int row = new DataRepository(_dataContext).ExecSqlListTran(sqlList);
if (row > 0)
{
return SuccessMessageStatus("修改退料单数据成功!", row);
}
else
{
return FailMessageStatus("修改退料单数据失败!", row);
}
}
catch (Exception ex)
{
return FailMessageStatus($"修改退料单数据发生异常,【{ex.Message}】");
}
}
///
/// 删除退料单数据
///
/// 退料单主表对象
///
public OperateResultInfo DeleteWmsInRetreatData(WmsInRetreatResult wmsInRetreat)
{
try
{
#region SQL语句生成
string[] retreatIdList = wmsInRetreat.RETREAT_NO.Split(',');
List sqlList = new List();
foreach (string item in retreatIdList)
{
string sqlDeleteWmsRetreat = $@"
UPDATE WMS_IN_RETREAT
SET RETREAT_STATUS = '111',
UPDATE_BY = '{wmsInRetreat.UPDATE_BY}',
UPDATE_TIME = GETDATE(),
DATA_VERSION = DATA_VERSION + 1
WHERE
RETREAT_ID = '{item}';
";
string sqlDeleteWmsInRetreatDtl = $@"
UPDATE WMS_IN_RETREAT_DTL
SET RETREAT_DTL_STATUS = '111',
UPDATE_BY = '{wmsInRetreat.UPDATE_BY}',
UPDATE_TIME = GETDATE(),
DATA_VERSION = DATA_VERSION + 1
WHERE
RETREAT_ID = '{item}';
";
sqlList.Add(sqlDeleteWmsRetreat);
sqlList.Add(sqlDeleteWmsInRetreatDtl);
/*
ToDo: 后续增加删除移至历史表中。
*/
}
#endregion
int row = new DataRepository(_dataContext).ExecSqlListTran(sqlList);
if (row > 0)
{
return SuccessMessageStatus("删除退料单数据成功!", row);
}
else
{
return FailMessageStatus("删除退料单数据失败!", row);
}
}
catch (Exception ex)
{
return FailMessageStatus($"删除退料单数据发生异常,【{ex.Message}】");
}
}
///
/// 分页查询退料记录数据
///
/// 退料记录查询条件实体类对象
///
public OperateResultInfo> GetWmsInRetreatRecordListForPage(WmsInRetreatRecordSearchMd retreatRecordSearchMd)
{
try
{
#region SQL语句生成
StringBuilder sqlCondition = new StringBuilder();
if (!string.IsNullOrEmpty(retreatRecordSearchMd.RetreatNoMsg))
{
sqlCondition.Append($" AND RETREAT_NO = '{retreatRecordSearchMd.RetreatNoMsg}'");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.SourceNoMsg))
{
sqlCondition.Append($" AND SOURCE_NO = '{retreatRecordSearchMd.SourceNoMsg}'");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.RegionNoMsg))
{
sqlCondition.Append($" AND REGION_CODE = '{retreatRecordSearchMd.RegionNoMsg}'");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.BinNoMsg))
{
sqlCondition.Append($" AND (BIN_CODE like '%{retreatRecordSearchMd.BinNoMsg}%' OR BIN_NAME like '%{retreatRecordSearchMd.BinNoMsg}%')");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.PalletNoMsg))
{
sqlCondition.Append($" AND PALLET_CODE = '{retreatRecordSearchMd.PalletNoMsg}'");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.RetreaterMsg))
{
sqlCondition.Append($" AND RETREATER = '{retreatRecordSearchMd.RetreaterMsg}'");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.MaterielMsg))
{
sqlCondition.Append($" AND (MATERIEL_CODE like '%{retreatRecordSearchMd.MaterielMsg}%' OR MATERIEL_NAME = '%{retreatRecordSearchMd.MaterielMsg}%')");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.MaterielTypeMsg))
{
sqlCondition.Append($" AND (MATERIEL_TYPE_CODE = '%{retreatRecordSearchMd.MaterielTypeMsg}%' OR MATERIEL_TYPE_NAME = '%{retreatRecordSearchMd.MaterielTypeMsg}%')");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.SupplierMsg))
{
sqlCondition.Append($" AND SUPPLIER_CODE = '{retreatRecordSearchMd.SupplierMsg}'");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.BatchNoMsg))
{
sqlCondition.Append($" AND BATCH_NO = '{retreatRecordSearchMd.SupplierMsg}'");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.ItemStatusMsg))
{
sqlCondition.Append($" AND ITEM_STATUS = '{retreatRecordSearchMd.ItemStatusMsg}'");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.InspectionResultMsg))
{
sqlCondition.Append($" AND INSPECTION_RESULT = '{retreatRecordSearchMd.InspectionResultMsg}'");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.RetreatRecordStatus))
{
sqlCondition.Append($" AND RETREAT_RECORD_STATUS = '{retreatRecordSearchMd.RetreatRecordStatus}'");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.StartRetreatTime))
{
sqlCondition.Append($" AND RETREAT_TIME >= '{retreatRecordSearchMd.StartRetreatTime}'");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.EndRetreatTime))
{
sqlCondition.Append($" AND RETREAT_TIME <= '{retreatRecordSearchMd.EndRetreatTime}'");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.StartProductTime))
{
sqlCondition.Append($" AND PRODUCT_DATE >= '{retreatRecordSearchMd.StartProductTime}'");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.EndProductTime))
{
sqlCondition.Append($" AND PRODUCT_DATE <= '{retreatRecordSearchMd.EndProductTime}'");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.StartExpTime))
{
sqlCondition.Append($" AND EXP_DATE >= '{retreatRecordSearchMd.StartExpTime}'");
}
if (!string.IsNullOrEmpty(retreatRecordSearchMd.EndExpTime))
{
sqlCondition.Append($" AND EXP_DATE <= '{retreatRecordSearchMd.EndExpTime}'");
}
StringBuilder sqlCountRetreatData = new StringBuilder($@"SELECT COUNT(1) FROM VW_WMS_IN_RETREAT_RECORD WHERE 1=1");
sqlCountRetreatData.Append(sqlCondition.ToString());
int pageStartIndex = (retreatRecordSearchMd.PageNum - 1) * retreatRecordSearchMd.EveryPageQty;
int pageEndIndex = retreatRecordSearchMd.PageNum * retreatRecordSearchMd.EveryPageQty;
StringBuilder sqlQueryRetreatData = new StringBuilder($@"
SELECT
RETREAT_RECORD_ID,
NEWID,
RETREAT_ID,
RETREAT_NO,
RETREAT_DTL_ID,
SOURCE_NO,
REGION_CODE,
REGION_NAME,
BIN_CODE,
BIN_NAME,
TRAY_CODE,
PALLET_CODE,
RETREAT_TIME,
RETREATER,
MATERIEL_TYPE_CODE,
MATERIEL_TYPE_NAME,
MATERIEL_CODE,
MATERIEL_NAME,
MATERIEL_BARCODE,
MATERIEL_SPEC,
SUPPLIER_CODE,
SUPPLIER_NAME,
BATCH_NO,
PACKAGE_CODE,
UNIT_CODE,
RETREAT_QTY,
RETREAT_REASON,
PRODUCT_DATE,
EXP_DATE,
RETREAT_RECORD_STATUS,
RETREAT_RECORD_STATUS_NAME,
INSPECTION_RESULT,
INSPECTION_RESULT_NAME,
ITEM_STATUS,
ITEM_STATUS_NAME,
[DESCRIBE],
CREATE_BY,
CREATE_NAME,
CREATE_TIME,
UPDATE_BY,
UPDATE_NAME,
UPDATE_TIME,
DATA_VERSION,
REMARKS1,
REMARKS2,
REMARKS3,
REMARKS4,
REMARKS5
FROM
VW_WMS_IN_RETREAT_RECORD
WHERE
1=1
{sqlCondition}
ORDER BY UPDATE_TIME DESC,CREATE_TIME DESC
OFFSET {pageStartIndex} ROWS
FETCH NEXT {retreatRecordSearchMd.EveryPageQty} ROWS ONLY
");
#endregion
int dataCount = Convert.ToInt32(new DataRepository(_dataContext).ExecuteScalar(sqlCountRetreatData.ToString()));
List resultList = new DataRepository(_dataContext).Query(sqlQueryRetreatData.ToString()).ToList();
OperateResultInfo> retDataMsg = SuccessStatus(resultList);
retDataMsg.DataCount = dataCount;
return retDataMsg;
}
catch (Exception ex)
{
return FailMessageStatus>($"查询退料记录数据发生异常,【{ex.Message}】", null);
}
}
///
/// 添加退料组盘数据
///
/// 组盘信息实体类对象
///
public OperateResultInfo AddWmsInRetreatTrayData(WmsStkTrayResult wmsStkTrayResult)
{
try
{
if (wmsStkTrayResult.WmsStkTrayDtlList.Count > 0)
{
#region SQL语句生成
List sqlList = new List();
string sqlQueryRetreatRegionBinMsg = $@"
SELECT
A.*, B.USER_NAME Create_Name,
C.USER_NAME Update_Name
FROM
BAS_BIN A
LEFT JOIN SYS_USER B ON A.CREATE_BY = B.USER_ID
LEFT JOIN SYS_USER C ON A.UPDATE_BY = C.USER_ID
WHERE
A.REGION_CODE = 'SHDJ_Region'
";
List resultList = new DataRepository(_dataContext).Query(sqlQueryRetreatRegionBinMsg).ToList();
BinResult binMd = resultList[0];
wmsStkTrayResult.TRAY_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("Tray_Id"));
wmsStkTrayResult.TRAY_CODE = new DataRepository(_dataContext).GetSequenceMsg("Tray_Code");
string sqlAddWmsStkTray = $@"
INSERT INTO [WMS_STK_TRAY] (
[TRAY_ID],
[TRAY_CODE],
[PALLET_CODE],
[WEIGHT],
[HEIGHT],
[TRAYS_TYPE],
[TRAY_LOADED_TYPE],
[TRAY_STATUS],
[DESCRIBE],
[CREATE_BY],
[CREATE_TIME],
[UPDATE_BY],
[UPDATE_TIME],
[DATA_VERSION],
[REMARKS1],
[REMARKS2],
[REMARKS3],
[REMARKS4],
[REMARKS5]
)
VALUES
(
'{wmsStkTrayResult.TRAY_ID}',
'{wmsStkTrayResult.TRAY_CODE}',
'{wmsStkTrayResult.PALLET_CODE}',
'{wmsStkTrayResult.WEIGHT}',
'{wmsStkTrayResult.HEIGHT}',
'{wmsStkTrayResult.TRAYS_TYPE}',
'{wmsStkTrayResult.TRAY_LOADED_TYPE}',
'{wmsStkTrayResult.TRAY_STATUS}',
'{wmsStkTrayResult.DESCRIBE}',
'{wmsStkTrayResult.CREATE_BY}',
getdate(),
'{wmsStkTrayResult.UPDATE_BY}',
getdate(),
0,
NULL,
NULL,
NULL,
NULL,
NULL
);
";
sqlList.Add(sqlAddWmsStkTray);
int putawayId = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("InPutaway_Id"));
string puawayNo = new DataRepository(_dataContext).GetSequenceMsg("InPutaway_No");
string sqlAddWmsInPutAway = $@"
INSERT INTO [WMS_IN_PUTAWAY] (
[PUTAWAY_ID],
[PUTAWAY_NO],
[SOURCE_NO],
[PUTAWAY_TYPE],
[TRAY_CODE],
[PALLET_CODE],
[SBIN_CODE],
[PUTAWAY_PRIORITY],
[PUTAWAY_STATUS],
[DESCRIBE],
[CREATE_BY],
[CREATE_TIME],
[UPDATE_BY],
[UPDATE_TIME],
[DATA_VERSION],
[REMARKS1],
[REMARKS2],
[REMARKS3],
[REMARKS4],
[REMARKS5]
)
VALUES
(
'{putawayId}',
'{puawayNo}',
'{wmsStkTrayResult.WmsStkTrayDtlList[0].WmsInRetreatRecord.RETREAT_NO}',
'{2}',
'{wmsStkTrayResult.TRAY_CODE}',
'{wmsStkTrayResult.PALLET_CODE}',
'{binMd.BIN_CODE}',
'{100}',
'{0}',
'{wmsStkTrayResult.DESCRIBE}',
'{wmsStkTrayResult.CREATE_BY}',
getdate(),
'{wmsStkTrayResult.UPDATE_BY}',
getdate(),
0,
NULL,
NULL,
NULL,
NULL,
NULL
);
";
sqlList.Add(sqlAddWmsInPutAway);
foreach (WmsStkTrayDtlResult item in wmsStkTrayResult.WmsStkTrayDtlList)
{
WmsStkTrayDtlExtResult trayDtlExtMd = item.WmsStkTrayDtlExtMd;
WmsInRetreatRecordResult retreatRecordMd = item.WmsInRetreatRecord;
item.TRAY_DTL_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("Tray_Dtl_Id"));
trayDtlExtMd.TRAY_DTL_EXT_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("Tray_Dtl_Ext_Id"));
trayDtlExtMd.TRAY_DTL_ID = item.TRAY_DTL_ID;
trayDtlExtMd.TRAY_ID = wmsStkTrayResult.TRAY_ID;
retreatRecordMd.RETREAT_RECORD_ID = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("Retreat_Record_Id"));
int putawayDtlId = Convert.ToInt32(new DataRepository(_dataContext).GetSequenceMsg("InPutaway_Dtl_Id"));
retreatRecordMd.TRAY_CODE = wmsStkTrayResult.TRAY_CODE;
retreatRecordMd.REGION_CODE = binMd.REGION_CODE;
retreatRecordMd.REGION_NAME = binMd.REGION_NAME;
retreatRecordMd.BIN_CODE = binMd.BIN_CODE;
retreatRecordMd.BIN_NAME = binMd.BIN_NAME;
string sqlAddWmsStkTrayDtl = $@"
INSERT INTO [WMS_STK_TRAY_DTL] (
[TRAY_DTL_ID],
[TRAY_ID],
[MATERIEL_ID],
[MATERIEL_CODE],
[MATERIEL_NAME],
[MATERIEL_BARCODE],
[QTY],
[TRAY_DTL_STATUS],
[DESCRIBE],
[CREATE_BY],
[CREATE_TIME],
[UPDATE_BY],
[UPDATE_TIME],
[DATA_VERSION],
[REMARKS1],
[REMARKS2],
[REMARKS3],
[REMARKS4],
[REMARKS5]
)
VALUES
(
'{item.TRAY_DTL_ID}',
'{item.TRAY_ID}',
'{item.MATERIEL_ID}',
'{item.MATERIEL_CODE}',
'{item.MATERIEL_NAME}',
'{item.MATERIEL_BARCODE}',
'{item.QTY}',
'{0}',
'{item.DESCRIBE}',
'{item.CREATE_BY}',
getdate(),
'{item.UPDATE_BY}',
getdate(),
0,
NULL,
NULL,
NULL,
NULL,
NULL
);
";
string sqlAddWmsStkTrayDtlExt = $@"
INSERT INTO [WMS_STK_TRAY_DTL_EXT] (
[TRAY_DTL_EXT_ID],
[TRAY_DTL_ID],
[MATERIEL_SPEC],
[PACKAGE_CODE],
[UNIT_CODE],
[BATCH_NO],
[SUPPLIER_CODE],
[SUPPLIER_NAME],
[PRODUCT_DATE],
[EXP_DATE],
[INSPECTION_RESULT],
[LOCK_FLAG],
[MIN_PKG_QTY],
[ECTEND_TIME_LEN],
[SUPPLIER_BATCH],
[ITEM_STATUS],
[DESCRIBE],
[CREATE_BY],
[CREATE_TIME],
[UPDATE_BY],
[UPDATE_TIME],
[DATA_VERSION],
[REMARKS1],
[REMARKS2],
[REMARKS3],
[REMARKS4],
[REMARKS5]
)
VALUES
(
'{trayDtlExtMd.TRAY_DTL_EXT_ID}',
'{trayDtlExtMd.TRAY_DTL_ID}',
'{trayDtlExtMd.MATERIEL_SPEC}',
'{trayDtlExtMd.PACKAGE_CODE}',
'{trayDtlExtMd.UNIT_CODE}',
'{trayDtlExtMd.BATCH_NO}',
'{trayDtlExtMd.SUPPLIER_CODE}',
'{trayDtlExtMd.SUPPLIER_NAME}',
'{trayDtlExtMd.PRODUCT_DATE}',
'{trayDtlExtMd.EXP_DATE}',
'{trayDtlExtMd.INSPECTION_RESULT}',
'{0}',
'{trayDtlExtMd.MIN_PKG_QTY}',
'{trayDtlExtMd.ECTEND_TIME_LEN}',
'{trayDtlExtMd.SUPPLIER_BATCH}',
'{trayDtlExtMd.ITEM_STATUS}',
'{trayDtlExtMd.DESCRIBE}',
'{trayDtlExtMd.CREATE_BY}',
getdate(),
'{trayDtlExtMd.UPDATE_BY}',
getdate(),
0,
NULL,
NULL,
NULL,
NULL,
NULL
);
";
string sqlAddWmsInRetreatRecord = $@"
INSERT INTO [WMS_IN_RETREAT_RECORD] (
[RETREAT_RECORD_ID],
[RETREAT_ID],
[RETREAT_NO],
[RETREAT_DTL_ID],
[SOURCE_NO],
[REGION_CODE],
[REGION_NAME],
[BIN_CODE],
[BIN_NAME],
[TRAY_CODE],
[PALLET_CODE],
[RETREAT_TIME],
[RETREATER],
[MATERIEL_CODE],
[MATERIEL_NAME],
[MATERIEL_BARCODE],
[MATERIEL_SPEC],
[SUPPLIER_CODE],
[SUPPLIER_NAME],
[BATCH_NO],
[PACKAGE_CODE],
[UNIT_CODE],
[RETREAT_QTY],
[PRODUCT_DATE],
[EXP_DATE],
[RETREAT_RECORD_STATUS],
[INSPECTION_RESULT],
[ITEM_STATUS],
[DESCRIBE],
[CREATE_BY],
[CREATE_TIME],
[UPDATE_BY],
[UPDATE_TIME],
[DATA_VERSION],
[REMARKS1],
[REMARKS2],
[REMARKS3],
[REMARKS4],
[REMARKS5]
)
VALUES
(
'{retreatRecordMd.RETREAT_RECORD_ID}',
'{retreatRecordMd.RETREAT_ID}',
'{retreatRecordMd.RETREAT_NO}',
'{retreatRecordMd.RETREAT_DTL_ID}',
'{retreatRecordMd.SOURCE_NO}',
'{retreatRecordMd.REGION_CODE}',
'{retreatRecordMd.REGION_NAME}',
'{retreatRecordMd.BIN_CODE}',
'{retreatRecordMd.BIN_NAME}',
'{retreatRecordMd.TRAY_CODE}',
'{retreatRecordMd.PALLET_CODE}',
'{retreatRecordMd.RETREAT_TIME}',
'{retreatRecordMd.RETREATER}',
'{retreatRecordMd.MATERIEL_CODE}',
'{retreatRecordMd.MATERIEL_NAME}',
'{retreatRecordMd.MATERIEL_BARCODE}',
'{retreatRecordMd.MATERIEL_SPEC}',
'{retreatRecordMd.SUPPLIER_CODE}',
'{retreatRecordMd.SUPPLIER_NAME}',
'{retreatRecordMd.BATCH_NO}',
'{retreatRecordMd.PACKAGE_CODE}',
'{retreatRecordMd.UNIT_CODE}',
'{retreatRecordMd.RETREAT_QTY}',
'{retreatRecordMd.PRODUCT_DATE}',
'{retreatRecordMd.EXP_DATE}',
'{retreatRecordMd.RETREAT_RECORD_STATUS}',
'{retreatRecordMd.INSPECTION_RESULT}',
'{retreatRecordMd.ITEM_STATUS}',
'{retreatRecordMd.DESCRIBE}',
'{retreatRecordMd.CREATE_BY}',
getdate(),
'{retreatRecordMd.UPDATE_BY}',
getdate(),
0,
NULL,
NULL,
NULL,
NULL,
NULL
);
";
string sqlAddWmsInPutAwayDtl = $@"
INSERT INTO [WMS_IN_PUTAWAY_DTL] (
[PUTAWAY_DTL_ID],
[PUTAWAY_ID],
[MATERIEL_CODE],
[MATERIEL_NAME],
[MATERIEL_BARCODE],
[MATERIEL_SPEC],
[BATCH_NO],
[PACKAGE_CODE],
[UNIT_CODE],
[PUTAWAY_QTY],
[SUPPLIER_CODE],
[SUPPLIER_NAME],
[PRODUCT_DATE],
[EXP_DATE],
[INSPECTION_RESULT],
[PUTAWAY_DTL_STATUS],
[ITEM_STATUS],
[DESCRIBE],
[CREATE_BY],
[CREATE_TIME],
[UPDATE_BY],
[UPDATE_TIME],
[DATA_VERSION],
[REMARKS1],
[REMARKS2],
[REMARKS3],
[REMARKS4],
[REMARKS5]
)
VALUES
(
'{putawayDtlId}',
'{putawayId}',
'{item.MATERIEL_CODE}',
'{item.MATERIEL_NAME}',
'{item.MATERIEL_BARCODE}',
'{trayDtlExtMd.MATERIEL_SPEC}',
'{trayDtlExtMd.BATCH_NO}',
'{trayDtlExtMd.PACKAGE_CODE}',
'{trayDtlExtMd.UNIT_CODE}',
'{item.QTY}',
'{trayDtlExtMd.SUPPLIER_CODE}',
'{trayDtlExtMd.SUPPLIER_NAME}',
'{trayDtlExtMd.PRODUCT_DATE}',
'{trayDtlExtMd.EXP_DATE}',
'{trayDtlExtMd.INSPECTION_RESULT}',
'{0}',
'{trayDtlExtMd.ITEM_STATUS}',
'{item.DESCRIBE}',
'{trayDtlExtMd.CREATE_BY}',
getdate(),
'{trayDtlExtMd.UPDATE_BY}',
getdate(),
0,
NULL,
NULL,
NULL,
NULL,
NULL
);
";
sqlList.Add(sqlAddWmsStkTrayDtl);
sqlList.Add(sqlAddWmsStkTrayDtlExt);
sqlList.Add(sqlAddWmsInRetreatRecord);
sqlList.Add(sqlAddWmsInPutAwayDtl);
string sqlUpdateWmsInRetreatDtl = $@"
UPDATE WMS_IN_RETREAT_DTL
SET
RETREAT_TRAY_QTY = RETREAT_TRAY_QTY + '{item.QTY}',
RETREAT_DTL_STATUS = '{55}',
UPDATE_BY = '{wmsStkTrayResult.UPDATE_BY}',
UPDATE_TIME = GETDATE(),
DATA_VERSION = DATA_VERSION + 1
WHERE
RETREAT_DTL_ID = '{retreatRecordMd.RETREAT_DTL_ID}'
AND RETREAT_ID = '{retreatRecordMd.RETREAT_ID}'
";
sqlList.Add(sqlUpdateWmsInRetreatDtl);
}
string sqlUpdateWmsInRetreat = $@"
IF EXISTS (
SELECT
1
FROM
WMS_IN_RETREAT_DTL
WHERE
RETREAT_DTL_STATUS = 55
AND RETREAT_ID = '{wmsStkTrayResult.WmsStkTrayDtlList[0].WmsInRetreatRecord.RETREAT_ID}'
) UPDATE WMS_IN_RETREAT
SET RETREAT_STATUS = 55,
UPDATE_BY = '{wmsStkTrayResult.UPDATE_BY}',
UPDATE_TIME = GETDATE(),
DATA_VERSION = DATA_VERSION + 1
WHERE
RETREAT_ID = '{wmsStkTrayResult.WmsStkTrayDtlList[0].WmsInRetreatRecord.RETREAT_ID}' ;
";
sqlList.Add(sqlUpdateWmsInRetreat);
#endregion
int row = new DataRepository(_dataContext).ExecSqlListTran(sqlList);
if (row > 0)
{
return SuccessMessageStatus("新增退料组盘数据成功!", row);
}
else
{
return FailMessageStatus("新增退料组盘数据失败!", row);
}
}
else
{
return FailMessageStatus($"传入数据不存在组盘明细数据!");
}
}
catch (Exception ex)
{
return FailMessageStatus($"添加退料组盘数据发生异常,【{ex.Message}】");
}
}
}
}