public JsonResult _GetIpDatInfo(DatFileSearchModel searchModel)
{
try
{
if (!this.CheckSearchModelIsNull(searchModel))
{
throw new BusinessException("请选择查询条件。");
}
// ReportSearchStatementModel reportSearchStatementModel = PrepareSearchStatement( searchModel);
//GridModel<object[]> gridModel = GetReportAjaxPageData<object[]>(reportSearchStatementModel);
// StringBuilder stringBd = IpDatInfoStringBuilder(gridModel.Data);
string sql = this.GetSearchIpDatInfoSql(searchModel);
IList<object[]> objList = base.genericMgr.FindAllWithNativeSql<object[]>(sql);
return Json(new { Info = IpDatInfoStringBuilder(objList).ToString() });
}
catch (BusinessException ex)
{
SaveBusinessExceptionMessage(ex);
}
catch (Exception ex)
{
SaveErrorMessage(ex);
}
return Json(null);
}
public ActionResult List(GridCommand command, DatFileSearchModel searchModel)
{
//SearchCacheModel searchCacheModel = this.ProcessSearchModel(command, searchModel);
TempData["DatFileSearchModel"] = searchModel;
if (this.CheckSearchModelIsNull(searchModel))
{
TempData["_AjaxMessage"] = "";
}
else
{
SaveWarningMessage(Resources.ErrorMessage.Errors_NoConditions);
}
ViewBag.PageSize = base.ProcessPageSize(command.PageSize);
return View();
}
public ActionResult List(GridCommand command, DatFileSearchModel searchModel)
{
// ViewBag.Type = searchModel.Type;
TempData["DatFileSearchModel"] = searchModel;
ViewBag.IsCreateDat = searchModel.IsCreateDat;
ViewBag.HandResult = searchModel.HandResult;
ViewBag.Item = searchModel.Item;
if (this.CheckSearchModelIsNull(searchModel))
{
TempData["_AjaxMessage"] = "";
}
else
{
SaveWarningMessage(Resources.ErrorMessage.Errors_NoConditions);
}
ViewBag.PageSize = base.ProcessPageSize(command.PageSize);
return View();
}
private string GetSearchIpDatInfoSql(DatFileSearchModel searchModel)
{
string IpDetailSql = "select * from ORD_IpDet_8 WHERE 1=1 ";
string IpMasterSql = "select * from ORD_IpMstr_8 WHERE 1=1 ";
string CreateIpDATSql = "select * from FIS_CreateIpDAT WHERE 1=1 ";
string LesInLogSql = "select * from FIS_LesINLog WHERE 1=1 ";
//string LocTransSql = "select * from VIEW_LocTrans WHERE 1=1 ";
//string InvLocSql = "select * from sconit5_si.dbo.SI_SAP_InvLoc WHERE 1=1";
//string InvTransSql = "select * from sconit5_si.dbo.SI_SAP_InvTrans WHERE 1=1";
string IpMasterWhere = " and 1=1";
string IpDetailWhere = " and 1=1";
string LesInLogWhere = " and 1=1";
if (!string.IsNullOrEmpty(searchModel.Supplier))
{
IpMasterSql += " AND PartyFrom LIKE '%" + searchModel.Supplier + "%'";
IpMasterWhere += " AND im.PartyFrom LIKE '%" + searchModel.Supplier + "%'";
}
if (!string.IsNullOrEmpty(searchModel.IpNo))
{
IpMasterSql += " AND IpNo LIKE '%" + searchModel.IpNo + "%'";
IpMasterWhere += " AND im.IpNo LIKE '%" + searchModel.IpNo + "%'";
IpDetailSql += " AND IpNo LIKE '%" + searchModel.IpNo + "%'";
IpDetailWhere += " AND im.IpNo LIKE '%" + searchModel.IpNo + "%'";
}
if (!string.IsNullOrEmpty(searchModel.Location))
{
IpDetailSql += " AND LocTo LIKE '%" + searchModel.Location + "%'";
IpDetailWhere += " AND ipdet.LocTo LIKE '%" + searchModel.Location + "%'";
}
if (!string.IsNullOrEmpty(searchModel.Item))
{
IpDetailSql += " AND Item LIKE '%" + searchModel.Item + "%'";
IpDetailWhere += " AND ipdet.Item LIKE '%" + searchModel.Item + "%'";
}
if (!string.IsNullOrEmpty(searchModel.WmsNo))
{
LesInLogSql += " AND WmsNo LIKE '%" + searchModel.WmsNo + "%'";
LesInLogWhere += " AND lesLog.WmsNo LIKE '%" + searchModel.WmsNo + "%'";
}
if (!string.IsNullOrEmpty(searchModel.HandResult))
{
LesInLogSql += " AND HandResult='" + searchModel.HandResult + "'";
LesInLogWhere += " AND lesLog.HandResult='" + searchModel.HandResult + "'";
}
if (!string.IsNullOrEmpty(searchModel.MoveType))
{
LesInLogSql += " AND MoveType='" + searchModel.MoveType + "'";
LesInLogWhere += " AND lesLog.MoveType='" + searchModel.MoveType + "'";
//SET @LesInLogSql=@LesInLogSql+' AND MoveType = @MoveType_1'
}
if (searchModel.IsCs != null)
{
if (searchModel.IsCs == 1)
{
IpDetailSql += " AND BillTerm=3";
IpDetailWhere += " AND ipdet.BillTerm =3";
}
else
{
IpDetailSql += " AND BillTerm<>3";
IpDetailWhere += " AND ipdet.BillTerm <>3";
}
}
if (searchModel.StartDate != null && searchModel.EndDate != null)
{
IpDetailSql += " AND CreateDate BETWEEN '" + searchModel.StartDate + "' And '" + searchModel.EndDate + "' ";
IpDetailWhere += " AND ipdet.CreateDate BETWEEN '" + searchModel.StartDate + "' And '" + searchModel.EndDate + "' ";
}
if (searchModel.StartDate != null && searchModel.EndDate == null)
{
IpDetailSql += " AND CreateDate > '" + searchModel.StartDate + "' ";
IpDetailWhere += " AND ipdet.CreateDate > '" + searchModel.StartDate + "' ";
}
if (searchModel.StartDate == null && searchModel.EndDate != null)
{
IpDetailSql += " AND CreateDate < '" + searchModel.EndDate + "' ";
IpDetailWhere += " AND ipdet.CreateDate < '" + searchModel.EndDate + "' ";
}
string AllSelectSql = "select top(500) im.PartyFrom,ipdet.IpNo,ipdet.OrderNo,ipdet.Item,ipdet.ItemDesc,ipdet.RefItemCode,ipdet.Qty,ipdet.RecQty,"
+ "IpDat.CreateUserNm,IpDat.IsCreateDat,IpDat.TIME_STAMP1,IpDat.FileName, "
+ "lesLog.MoveType,lesLog.WMSNo,lesLog.HandTime,lesLog.HandResult,lesLog.ErrorCause,lesLog.IsCreateDat as isCreateLogDat,lesLog.FileName as logfilename"
//+ ",viewLocTr.IsCS ,viewLocTr.TransType"
//+ ",siInvLoc.SourceId,siInvTrans.Status,siInvTrans.ErrorMessage"
+ " from (" + IpDetailSql + ") as ipdet "
+ " left join (" + IpMasterSql + ") as im on im.IpNo=ipdet.IpNo "
+ " left join (" + CreateIpDATSql + ") as IpDat on ipdet.IpNo=IpDat.ASN_NO and ipdet.Seq=IpDat.ASN_ITEM "
+ " left join (" + LesInLogSql + ") as lesLog on ipdet.IpNo=lesLog.ASNNo and ipdet.Item=lesLog.Item and ipdet.ExtNo=lesLog.ExtNo and ipdet.ExtSeq=lesLog.POLine"
//+ " left join (" + LocTransSql + ") as viewLocTr on ipdet.Id=viewLocTr.IpDetId"
//+ " left join (" + InvLocSql + ") as siInvLoc on viewLocTr.Id=siInvLoc.SourceId"
//+ " left join (" + @InvTransSql + ") as siInvTrans on siInvLoc.FRBNR=siInvTrans.FRBNR and siInvLoc.SGTXT=siInvTrans.SGTXT"
+ " where 1=1 " + IpMasterWhere + " " + IpDetailWhere + " " + LesInLogWhere;
return AllSelectSql;
}
private string GetSearchOrderDatInfoSql(DatFileSearchModel searchModel)
{
string OrderDetailSql = "select * from ORD_OrderDet_2 WHERE 1=1 and LocFrom like 'LOC%' or LocFrom like 'SQCK%' ";
string CreateOrderDATSql = "select * from FIS_CreateOrderDAT WHERE 1=1 ";
string WMSDatFileSql = "select * from FIS_WMSDatFile WHERE 1=1 ";
string LesInLogSql = "select * from FIS_LesINLog WHERE 1=1 ";
//string LocTransSql = "select * from VIEW_LocTrans WHERE 1=1 ";
//string InvLocSql = "select * from sconit5_si.dbo.SI_SAP_InvLoc WHERE 1=1";
//string InvTransSql = "select * from sconit5_si.dbo.SI_SAP_InvTrans WHERE 1=1";
string OrderDetailWhere = " and 1=1";
string LesInLogWhere = " and 1=1";
string WMSDatFileWhere = " and 1=1";
string LocTransWhere = " and 1=1";
if (!string.IsNullOrEmpty(searchModel.Supplier))
{
OrderDetailSql += " AND ManufactureParty LIKE '%" + searchModel.Supplier + "%'";
OrderDetailWhere += " AND od.ManufactureParty LIKE '%" + searchModel.Supplier + "%'";
}
if (!string.IsNullOrEmpty(searchModel.OrderNo))
{
OrderDetailSql += " AND OrderNo LIKE '%" + searchModel.OrderNo + "%'";
OrderDetailWhere += " AND od.OrderNo LIKE '%" + searchModel.OrderNo + "%'";
}
if (!string.IsNullOrEmpty(searchModel.Location))
{
OrderDetailSql += " AND LocTo LIKE '%" + searchModel.Location + "%'";
OrderDetailWhere += " AND od.LocTo LIKE '%" + searchModel.Location + "%'";
}
if (!string.IsNullOrEmpty(searchModel.Item))
{
OrderDetailSql += " AND Item LIKE '%" + searchModel.Item + "%'";
OrderDetailWhere += " AND od.Item LIKE '%" + searchModel.Item + "%'";
}
if (!string.IsNullOrEmpty(searchModel.WmsNo))
{
WMSDatFileSql += " AND WMSId LIKE '%" + searchModel.WmsNo + "%'";
WMSDatFileWhere += " AND WMSDat.WMSId LIKE '%" + searchModel.WmsNo + "%'";
}
if (!string.IsNullOrEmpty(searchModel.WmsPickNo))//安吉拣货单号
{
WMSDatFileSql += " AND WmsNo LIKE '%" + searchModel.WmsPickNo + "%'";
WMSDatFileWhere += " AND WMSDat.WmsNo LIKE '%" + searchModel.WmsPickNo + "%'";
}
if (!string.IsNullOrEmpty(searchModel.HandResult))
{
LesInLogSql += " AND HandResult='" + searchModel.HandResult + "'";
LesInLogWhere += " AND lesLog.HandResult='" + searchModel.HandResult + "'";
}
if (!string.IsNullOrEmpty(searchModel.MoveType))
{
LesInLogSql += " AND MoveType='" + searchModel.MoveType + "'";
LesInLogWhere += " AND lesLog.MoveType='" + searchModel.MoveType + "'";
//SET @LesInLogSql=@LesInLogSql+' AND MoveType = @MoveType_1'
}
//if (searchModel.IsCs != null)
//{
// LocTransSql += " AND IsCS='" + searchModel.IsCs + "'";
// LocTransWhere += " AND lesLog.IsCS='" + searchModel.IsCs + "'";
//}
if (searchModel.StartDate != null && searchModel.EndDate != null)
{
OrderDetailSql += " AND CreateDate BETWEEN '" + searchModel.StartDate + "' And '" + searchModel.EndDate + "' ";
OrderDetailWhere += " AND od.CreateDate BETWEEN '" + searchModel.StartDate + "' And '" + searchModel.EndDate + "' ";
}
if (searchModel.StartDate != null && searchModel.EndDate == null)
{
OrderDetailSql += " AND CreateDate > '" + searchModel.StartDate + "' ";
OrderDetailWhere += " AND od.CreateDate > '" + searchModel.StartDate + "' ";
}
if (searchModel.StartDate == null && searchModel.EndDate != null)
{
OrderDetailSql += " AND CreateDate < '" + searchModel.EndDate + "' ";
OrderDetailWhere += " AND od.CreateDate < '" + searchModel.EndDate + "' ";
}
string AllSelectSql = "select top(500) od.ManufactureParty,od.OrderNo,od.Item,od.ItemDesc,od.RefItemCode,od.OrderQty,od.RecQty,"
+ "orderDat.CreateUserNm,orderDat.IsCreateDat,orderDat.TIME_STAMP1,orderDat.FileName,"
+ "WMSDat.CreateDate,WMSDat.Qty, WMSDat.IsHand,WMSDat.WMSId,WMSDat.FileName as wmsDatFileName,"
+ "lesLog.MoveType,lesLog.HandTime,lesLog.HandResult,lesLog.ErrorCause,lesLog.IsCreateDat as isCreateLogDat,lesLog.FileName as logfilename"
//+ "viewLocTr.IsCS ,viewLocTr.TransType,"
//+ "siInvLoc.SourceId,siInvTrans.Status,siInvTrans.ErrorMessage"
+ " from (" + OrderDetailSql + ") as od "
+ " left join (" + CreateOrderDATSql + ") as orderDat on od.Id=orderDat.ZPLISTNO"
+ " left join (" + WMSDatFileSql + ") as WMSDat on od.Id=WMSDat.WmsLine "
+ " left join (" + LesInLogSql + ") as lesLog on WMSDat.WMSId=lesLog.WMSNo"
//+ " left join (" + LocTransSql + ") as viewLocTr on od.Id=viewLocTr.OrderDetId"
//+ " left join (" + InvLocSql + ") as siInvLoc on viewLocTr.Id=siInvLoc.SourceId "
//+ " left join (" + InvTransSql + ") as siInvTrans on siInvLoc.FRBNR=siInvTrans.FRBNR and siInvLoc.SGTXT=siInvTrans.SGTXT"
+ " where 1=1 " + OrderDetailWhere + LesInLogWhere + LocTransWhere + WMSDatFileWhere;
return AllSelectSql;
}
public JsonResult _GetOrderDatInfo(DatFileSearchModel searchModel)
{
try
{
if (!this.CheckSearchModelIsNull(searchModel))
{
throw new BusinessException("请选择查询条件。");
}
string sql = this.GetSearchOrderDatInfoSql(searchModel);
IList<object[]> objList = base.genericMgr.FindAllWithNativeSql<object[]>(sql);
return Json(new { Info = OrderDatInfoStringBuilder(objList).ToString() });
}
catch (BusinessException ex)
{
SaveBusinessExceptionMessage(ex);
}
catch (Exception ex)
{
SaveErrorMessage(ex);
}
return Json(null);
}
private ReportSearchStatementModel PrepareSearchStatement(DatFileSearchModel searchModel)
{
ReportSearchStatementModel reportSearchStatementModel = new ReportSearchStatementModel();
reportSearchStatementModel.ProcedureName = "USP_Report_GetFISDatInfo";
/*
* @IpNo varchar(50),
@Supplier varchar(50),
@Location varchar(50),
@Item varchar(50),
@WmsNo varchar(50),
@HandResult varchar(50),
@MoveType varchar(50),
--@SapLocation varchar(50),
@IsCs varchar(50),
@StartDate datetime,
@EndDate datetime
* */
SqlParameter[] parameters = new SqlParameter[10];
parameters[0] = new SqlParameter("@IpNo", SqlDbType.VarChar, 50);
parameters[0].Value = searchModel.IpNo;
parameters[1] = new SqlParameter("@Supplier", SqlDbType.VarChar, 50);
parameters[1].Value = searchModel.Supplier;
parameters[2] = new SqlParameter("@Location", SqlDbType.VarChar, 50);
parameters[2].Value = searchModel.Location;
parameters[3] = new SqlParameter("@Item", SqlDbType.VarChar, 50);
parameters[3].Value = searchModel.Item;
parameters[4] = new SqlParameter("@WmsNo", SqlDbType.VarChar, 50);
parameters[4].Value = searchModel.WmsNo;
parameters[5] = new SqlParameter("@HandResult", SqlDbType.VarChar, 50);
parameters[5].Value = searchModel.HandResult;
parameters[6] = new SqlParameter("@MoveType", SqlDbType.VarChar, 50);
parameters[6].Value = searchModel.MoveType;
parameters[7] = new SqlParameter("@IsCs", SqlDbType.VarChar, 50);
parameters[7].Value = searchModel.IsCs;
parameters[8] = new SqlParameter("@StartDate", SqlDbType.DateTime);
parameters[8].Value = searchModel.StartDate;
parameters[9] = new SqlParameter("@EndDate", SqlDbType.DateTime);
parameters[9].Value = searchModel.EndDate;
reportSearchStatementModel.Parameters = parameters;
return reportSearchStatementModel;
}
public ActionResult _AjaxList(GridCommand command, DatFileSearchModel searchModel)
{
if (!this.CheckSearchModelIsNull(searchModel))
{
return PartialView(new GridModel(new List<LesINLog>()));
}
#region old
//SearchStatementModel searchStatementModel = PrepareSearchStatement(command, searchModel);
//TempData["searchLesInLogStatementModel"] = searchStatementModel;
//GridModel<LesINLog> List = GetAjaxPageData<LesINLog>(searchStatementModel, command);
//IList<IpDetail> ipDetailList = base.genericMgr.FindAll<IpDetail>("select i from IpDetail as i where OrderType = 8");
//IList<ReceiptDetail> recDetailList = base.genericMgr.FindAll<ReceiptDetail>("select d from ReceiptDetail as d where OrderType = 8");
//foreach (LesINLog lesINLog in List.Data)
//{
// IList<IpDetail> matchedIpDetail = (from ip in ipDetailList
// where ip.IpNo == lesINLog.ASNNo && ip.ExternalOrderNo == lesINLog.ExtNo
// && ip.ExternalSequence == lesINLog.POLine
// select ip).ToList();
// if (matchedIpDetail != null && matchedIpDetail.Count > 0)
// {
// lesINLog.ShipQty = matchedIpDetail.First().Qty;
// lesINLog.LocTo = matchedIpDetail.First().LocationTo;
// }
// IList<ReceiptDetail> matchedRecDetail = (from rd in recDetailList
// where rd.ReceiptNo == lesINLog.PO
// select rd).ToList();
// if (matchedRecDetail != null && matchedRecDetail.Count > 0)
// {
// lesINLog.ReceivedQty = matchedRecDetail.First().ReceivedQty;
// }
//}
//TempData["searchLesInLogTotal"] = List.Total;
#endregion
string sql=this.StringBuilderPrepareSearchStatement(command, searchModel).ToString();
TempData["searchSql"] = sql;
// sb.Append(string.Format( ") as t1 where t1.RowId between {0} and {1}",(command.Page-1)*command.PageSize,command.Page*command.PageSize));
IList<object> countList = base.genericMgr.FindAllWithNativeSql<object>("select count(*) from ("+sql+") as t2");
string lesInLogSql = "select * from ("+sql;
IList<object[]> searchList = base.genericMgr.FindAllWithNativeSql<object[]>(lesInLogSql + string.Format(") as t2 where t2.RowId between {0} and {1}", (command.Page - 1) * command.PageSize, command.Page * command.PageSize));
IList<LesINLog> lesInLogList = new List<LesINLog>();
if (searchList != null && searchList.Count > 0)
{
//Id, Type, MoveType, Sequense, PO, POLine, WMSNo,
//WMSLine, HandTime, Item, HandResult, ErrorCause, IsCreateDat, FileName, ASNNo, ExtNo
#region
lesInLogList = (from tak in searchList
select new LesINLog
{
Id = (int)tak[1],
Type = (string)tak[2],
MoveType = (string)tak[3],
// Sequense = (string)tak[0],
PO = (string)tak[5],
POLine = (string)tak[6],
WMSNo = (string)tak[7],
WMSLine = (string)tak[8],
HandTime = Convert.ToDateTime(tak[9]),
Item = (string)tak[10],
HandResult = (string)tak[11],
ErrorCause = tak[12] != null ? (string)tak[12] : string.Empty,
IsCreateDat = (bool)tak[13],
FileName = (string)tak[14],
ASNNo = (string)tak[15],
ExtNo = (string)tak[16],
ShipQty = tak[17] != null ? (decimal)tak[17] : 0,
LocTo = tak[18] != null ? (string)tak[18] : string.Empty,
ReceivedQty = tak[19]!=null?(decimal)tak[19]:0,
}).ToList();
#endregion
}
GridModel<LesINLog> gridModelOrderDet = new GridModel<LesINLog>();
gridModelOrderDet.Total = Convert.ToInt32(countList[0]);
gridModelOrderDet.Data = lesInLogList;
return PartialView(gridModelOrderDet);
}
private StringBuilder StringBuilderPrepareSearchStatement(GridCommand command, DatFileSearchModel searchModel)
{
StringBuilder sb = new StringBuilder();
// sb.Append("select * from (");
if (command.SortDescriptors.Count > 0)
{
sb.Append(string.Format("select RowId=ROW_NUMBER()OVER( order by {0} desc) ,* from ", command.SortDescriptors[0].Member));
}
else
{
sb.Append("select RowId=ROW_NUMBER()OVER( order by FileName desc),* from ");
}
sb.Append(@"(select
lesInlog.Id, lesInlog.Type, lesInlog.MoveType, lesInlog.Sequense, lesInlog.PO, lesInlog.POLine, lesInlog.WMSNo, lesInlog.WMSLine, lesInlog.HandTime, lesInlog.Item, lesInlog.HandResult, lesInlog.ErrorCause, lesInlog.IsCreateDat, lesInlog.FileName, lesInlog.ASNNo, lesInlog.ExtNo
,ipdet.Qty as ShipQty,IpDet.LocTo as LocTo,recDet.RecQty as ReceivedQty
from FIS_LesINLog as lesInlog with(NOLOCK) left join ORD_IpDet_8 as ipDet with(NOLOCK)
on lesInlog.ASNNo=ipDet.IpNo and lesInlog.ExtNo=ipDet.ExtNo and lesInlog.POLine=ipDet.ExtSeq
left join ORD_RecDet_8 as recDet with(NOLOCK) on lesInlog.PO=recDet.RecNo where lesInlog.Type='MIGO'");
if (!string.IsNullOrEmpty(searchModel.AsnNo))
{
sb.Append(string.Format(" and lesInlog.ASNNo='{0}'",searchModel.AsnNo));
}
if (!string.IsNullOrEmpty(searchModel.MoveType))
{
sb.Append(string.Format(" and lesInlog.MoveType='{0}'", searchModel.MoveType));
}
if (!string.IsNullOrEmpty(searchModel.ExtNo))
{
sb.Append(string.Format(" and lesInlog.ExtNo='{0}'", searchModel.ExtNo));
}
if (!string.IsNullOrEmpty(searchModel.PoLine))
{
sb.Append(string.Format(" and lesInlog.PoLine='{0}'", searchModel.PoLine));
}
if (!string.IsNullOrEmpty(searchModel.HandResult))
{
sb.Append(string.Format(" and lesInlog.HandResult='{0}'", searchModel.HandResult));
}
if (!string.IsNullOrEmpty(searchModel.Item))
{
sb.Append(string.Format(" and lesInlog.Item='{0}'", searchModel.Item));
}
if (!string.IsNullOrEmpty(searchModel.WmsNo))
{
sb.Append(string.Format(" and lesInlog.WMSNo='{0}'", searchModel.WmsNo));
}
if (searchModel.IsCreateDat!=null)
{
sb.Append(string.Format(" and lesInlog.IsCreateDat='{0}'", searchModel.IsCreateDat));
}
if (searchModel.StartDate != null & searchModel.EndDate != null)
{
sb.Append(string.Format(" and lesInlog.HandTime between '{0}' and '{1}'", searchModel.StartDate.Value, searchModel.EndDate.Value));
}
else if (searchModel.StartDate != null & searchModel.EndDate == null)
{
sb.Append(string.Format(" and lesInlog.HandTime > '{0}'", searchModel.StartDate.Value));
}
else if (searchModel.StartDate == null & searchModel.EndDate != null)
{
sb.Append(string.Format(" and lesInlog.HandTime < '{0}'", searchModel.EndDate.Value));
}
// sb.Append(string.Format( ") as t1 where t1.RowId between {0} and {1}",(command.Page-1)*command.PageSize,command.Page*command.PageSize));
sb.Append(") as t1");
return sb;
}
private SearchStatementModel PrepareSearchStatement(GridCommand command, DatFileSearchModel searchModel)
{
string whereStatement = string.Empty;
IList<object> param = new List<object>();
HqlStatementHelper.AddLikeStatement("ASNNo", searchModel.AsnNo, HqlStatementHelper.LikeMatchMode.Anywhere, "l", ref whereStatement, param);
HqlStatementHelper.AddLikeStatement("Type", "MIGO", HqlStatementHelper.LikeMatchMode.Start, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("MoveType", searchModel.MoveType, "l", ref whereStatement, param);
HqlStatementHelper.AddLikeStatement("ExtNo", searchModel.ExtNo, HqlStatementHelper.LikeMatchMode.Anywhere, "l", ref whereStatement, param);
HqlStatementHelper.AddLikeStatement("PoLine", searchModel.PoLine, HqlStatementHelper.LikeMatchMode.Anywhere, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("HandResult", searchModel.HandResult, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("IsCreateDat", searchModel.IsCreateDat, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("WMSNo", searchModel.WmsNo, "l", ref whereStatement, param);
if (searchModel.StartDate != null & searchModel.EndDate != null)
{
HqlStatementHelper.AddBetweenStatement("HandTime", searchModel.StartDate.Value.ToString("yyMMddHHmmss"), searchModel.EndDate.Value.ToString("yyMMddHHmmss"), "l", ref whereStatement, param);
}
else if (searchModel.StartDate != null & searchModel.EndDate == null)
{
HqlStatementHelper.AddGeStatement("HandTime", searchModel.StartDate.Value.ToString("yyMMddHHmmss"), "l", ref whereStatement, param);
}
else if (searchModel.StartDate == null & searchModel.EndDate != null)
{
HqlStatementHelper.AddLeStatement("HandTime", searchModel.EndDate.Value.ToString("yyMMddHHmmss"), "l", ref whereStatement, param);
}
string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
if (command.SortDescriptors.Count == 0)
{
sortingStatement = " order by FileName desc";
}
else
{
sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
}
SearchStatementModel searchStatementModel = new SearchStatementModel();
searchStatementModel.SelectCountStatement = "select count(*) from LesINLog as l";
searchStatementModel.SelectStatement = "select l from LesINLog as l";
searchStatementModel.WhereStatement = whereStatement;
searchStatementModel.SortingStatement = sortingStatement;
searchStatementModel.Parameters = param.ToArray<object>();
return searchStatementModel;
}
public ActionResult _AjaxList(GridCommand command, DatFileSearchModel searchModel)
{
if (!this.CheckSearchModelIsNull(searchModel))
{
return PartialView(new GridModel(new List<WMSDatFile>()));
}
string sql = this.StringBuilderPrepareSearchStatement(command, searchModel).ToString();
TempData["searchSql"] = sql;
//IList<object> countList = base.genericMgr.FindAllWithNativeSql<object>("select count(*) from (" + sql + ") as t2");
//string lesInLogSql = "select * from (" + sql;
//IList<object[]> searchList = base.genericMgr.FindAllWithNativeSql<object[]>(lesInLogSql + string.Format(") as t2 where t2.RowId between {0} and {1}", (command.Page - 1) * command.PageSize, command.Page * command.PageSize));
IList<object[]> searchList = base.genericMgr.FindAllWithNativeSql<object[]>(sql);
IList<WMSDatFile> wMSDatFileList = new List<WMSDatFile>();
var returnlList = new List<WMSDatFile>();
if (searchList != null && searchList.Count > 0)
{
#region
wMSDatFileList = (from tak in searchList
select new WMSDatFile
{
WmsLine=((int)tak[0]).ToString(),
Id = tak[1] != null ? (int)tak[1] : 1,
WmsNo = (string)tak[2],
MoveType = (string)tak[3]+(string)tak[4],
// Sequense = (string)tak[0],
WMSId = (string)tak[5],
Item = (string)tak[6],
Uom = (string)tak[7],
UMLGO = (string)tak[8],
Qty = tak[9] != null ? (decimal)tak[9] : 0,
IsHand = tak[10] != null ? (bool)tak[10] : false,
CreateDateFormat = tak[11] != null ? (DateTime?)tak[11] : null,
ItemDescription = tak[12] != null ? (string)tak[12] : string.Empty,
ReferenceItemCode = (string)tak[13],
OrderQty = tak[25]!=null && (decimal?)tak[25]==1?(decimal)tak[24]:(decimal)tak[14],
ReceiveTotal = tak[15] != null ? (decimal)tak[15] : 0,
CancelQty = tak[16] != null ? (decimal)tak[16] : 0,
LGORT = tak[17] != null?(string)tak[17]:string.Empty,
RequirementDate = tak[18] != null ? (DateTime?)tak[18] : null,
OrderNo = (string)tak[19],
PartyTo = (string)tak[20],
PartyFrom = (string)tak[21],
WindowTime = (DateTime)tak[22],
OrderStrategyDescription = systemMgr.GetCodeDetailDescription(Sconit.CodeMaster.CodeMaster.FlowStrategy, int.Parse((tak[23]).ToString())),
ReceiveLotSize = tak[25]!=null && (decimal?)tak[25]==1?true:false,
}).ToList();
#endregion
#region
var cancelList = wMSDatFileList.Where(w => w.MoveType == "312" || w.MoveType == "412").ToList();
returnlList = wMSDatFileList.Where(w => w.MoveType != "312" && w.MoveType != "412").ToList();
if (cancelList != null && cancelList.Count > 0)
{
foreach (WMSDatFile c in cancelList)
{
if (c.MoveType == "312")
{
var cancelFile = returnlList.Where(r => r.MoveType == "311" && c.MoveType == "312" && r.SOBKZ == c.SOBKZ && r.Qty == c.Qty && r.WmsLine == c.WmsLine
&& r.ReceiveTotal - r.CancelQty == 0 && r.WmsNo == c.WmsNo).ToList();
if (cancelFile != null && cancelFile.Count > 0)
{
returnlList.Remove(cancelFile.First());
}
}
else if (c.MoveType == "412")
{
var cancelFile = returnlList.Where(r => r.MoveType == "411" && c.MoveType == "412" && r.SOBKZ == c.SOBKZ && r.Qty == c.Qty && r.WmsLine == c.WmsLine
&& r.ReceiveTotal - r.CancelQty == 0 && r.WmsNo == c.WmsNo).ToList();
if (cancelFile != null && cancelFile.Count > 0)
{
returnlList.Remove(cancelFile.First());
}
}
}
}
#region 冲销的相互抵消
//foreach (WMSDatFile wMSDatFile in wMSDatFileList)
//{
// if (wMSDatFile.MoveType == null)
// {
// continue;
// }
// foreach (WMSDatFile wmsFile in wMSDatFileList)
// {
// if (wmsFile.MoveType == null)
// {
// continue;
// }
// if (wMSDatFile.MoveType + wMSDatFile.SOBKZ == "311" && wmsFile.MoveType + wmsFile.SOBKZ == "312" && wmsFile.Qty == wMSDatFile.Qty && wmsFile.WmsLine == wMSDatFile.WmsLine
// && wmsFile.ReceiveTotal - wmsFile.CancelQty == 0 && wMSDatFile.ReceiveTotal - wMSDatFile.CancelQty == 0 && wmsFile.WmsNo==wMSDatFile.WmsNo)
// {
// wmsFile.MoveType = null;
// wMSDatFile.MoveType = null;
// break;
// }
// else if (wMSDatFile.MoveType + wMSDatFile.SOBKZ == "311K" && wmsFile.MoveType + wmsFile.SOBKZ == "312K" && wmsFile.Qty == wMSDatFile.Qty && wmsFile.WmsLine == wMSDatFile.WmsLine
// && wmsFile.ReceiveTotal - wmsFile.CancelQty == 0 && wMSDatFile.ReceiveTotal - wMSDatFile.CancelQty == 0 && wmsFile.WmsNo == wMSDatFile.WmsNo)
// {
// wmsFile.MoveType = null;
// wMSDatFile.MoveType = null;
// break;
// }
// else if (wMSDatFile.MoveType + wMSDatFile.SOBKZ == "411" && wmsFile.MoveType + wMSDatFile.SOBKZ == "412" && wmsFile.Qty == wMSDatFile.Qty && wmsFile.WmsLine == wMSDatFile.WmsLine
// && wmsFile.ReceiveTotal - wmsFile.CancelQty == 0 && wMSDatFile.ReceiveTotal - wMSDatFile.CancelQty == 0 && wmsFile.WmsNo == wMSDatFile.WmsNo)
// {
// wmsFile.MoveType = null;
// wMSDatFile.MoveType = null;
// break;
// }
// else if (wMSDatFile.MoveType + wMSDatFile.SOBKZ == "411K" && wmsFile.MoveType + wMSDatFile.SOBKZ == "412K" && wmsFile.Qty == wMSDatFile.Qty && wmsFile.WmsLine == wMSDatFile.WmsLine
// && wmsFile.ReceiveTotal - wmsFile.CancelQty == 0 && wMSDatFile.ReceiveTotal - wMSDatFile.CancelQty == 0 && wmsFile.WmsNo == wMSDatFile.WmsNo)
// {
// wmsFile.MoveType = null;
// wMSDatFile.MoveType = null;
// break;
// }
// }
//}
#endregion
#endregion
}
//IEnumerable<WMSDatFile> wmsList = wMSDatFileList.Where(o => o.MoveType != null && o.MoveType != "312" && o.MoveType != "412");
//var count = wMSDatFileList.Where(o => o.MoveType == null || o.MoveType == "312" || o.MoveType == "412");
GridModel<WMSDatFile> gridModelOrderDet = new GridModel<WMSDatFile>();
gridModelOrderDet.Total = returnlList.Count;
gridModelOrderDet.Data = returnlList.Skip((command.Page - 1) * command.PageSize).Take(command.PageSize);
return PartialView(gridModelOrderDet);
}
private StringBuilder StringBuilderPrepareSearchStatement(GridCommand command, DatFileSearchModel searchModel)
{
StringBuilder sb = new StringBuilder();
// sb.Append("select * from (");
//if (command.SortDescriptors.Count > 0)
//{
// sb.Append(string.Format("select RowId=ROW_NUMBER()OVER( order by {0} asc),* from ", command.SortDescriptors[0].Member));
//}
//else
//{
// sb.Append("select RowId=ROW_NUMBER()OVER( order by WindowTime asc),* from");
//}
sb.Append(@" select orderDet.Id,dat.Id as datId,dat.WmsNo,dat.MoveType,dat.SOBKZ,dat.WMSId,orderDet.Item,orderDet.Uom,dat.UMLGO,dat.Qty,dat.IsHand,dat.CreateDate,
orderDet.ItemDesc as ItemDescription,orderDet.RefItemCode as ReferenceItemCode,
orderDet.OrderQty as OrderQty,
dat.ReceiveTotal,dat.CancelQty,dat.LGORT,orderDet.CreateDate as RequirementDate,orderDet.OrderNo,m.PartyTo,m.PartyFrom,m.WindowTime,m.OrderStrategy,orderDet.UnitPrice,orderDet.RecLotSize
from Ord_OrderDet_2 as orderDet with(nolock)
inner join Ord_OrderMstr_2 as m with(nolock) on orderDet.OrderNo=m.OrderNo
left join FIS_WMSDatFile as dat with(nolock) on dat.OrderDetId=orderDet.Id
where 1=1 and m.CreateDate>'2013-10-01' and orderDet.OrderQty>0 and m.Status not in(0,5) ");
//if (!string.IsNullOrEmpty(searchModel.AsnNo))
//{
// sb.Append(string.Format(" and lesInlog.ASNNo='{0}'", searchModel.AsnNo));
//}
if (!string.IsNullOrEmpty(searchModel.OrderNo))
{
sb.Append(string.Format(" and orderDet.OrderNo = '{0}'", searchModel.OrderNo));
}
if (!string.IsNullOrEmpty(searchModel.LGORT))
{
sb.Append(string.Format(" and dat.LGORT = '{0}'", searchModel.LGORT));
}
if (!string.IsNullOrEmpty(searchModel.UMLGO))
{
sb.Append(string.Format(" and dat.UMLGO = '{0}'", searchModel.UMLGO));
}
//if (!string.IsNullOrEmpty(searchModel.OrderNo))
//{
// sb.Append(string.Format(" and dat.WmsLine in(select Convert(varchar(50),Id) from view_OrderDet where OrderNo='{0}')", searchModel.OrderNo));
//}
if (!string.IsNullOrEmpty(searchModel.WmsPickNo))
{
sb.Append(string.Format(" and dat.WmsNo='{0}'", searchModel.WmsPickNo));
}
if (!string.IsNullOrEmpty(searchModel.MoveType))
{
sb.Append(string.Format(" and dat.MoveType='{0}'", searchModel.MoveType));
}
if (!string.IsNullOrEmpty(searchModel.Item))
{
sb.Append(string.Format(" and orderDet.Item='{0}'", searchModel.Item));
}
if (!string.IsNullOrEmpty(searchModel.WMSId))
{
sb.Append(string.Format(" and dat.WMSId='{0}'", searchModel.WMSId));
}
if (searchModel.StartDate != null & searchModel.EndDate != null)
{
sb.Append(string.Format(" and m.WindowTime between '{0}' and '{1}'", searchModel.StartDate, searchModel.EndDate));
}
else if (searchModel.StartDate != null & searchModel.EndDate == null)
{
sb.Append(string.Format(" and m.WindowTime > '{0}'", searchModel.StartDate));
}
else if (searchModel.StartDate == null & searchModel.EndDate != null)
{
sb.Append(string.Format(" and m.WindowTime < '{0}'", searchModel.EndDate));
}
if (searchModel.IsClsoe)
{
sb.Append(string.Format(" and (dat.Qty+dat.CancelQty)>dat.ReceiveTotal ", searchModel.WMSId));
}
if (searchModel.IsNoneOut)
{
sb.Append(string.Format(" and dat.Id is null and orderDet.OrderQty>orderDet.RecQty"));
}
if (!string.IsNullOrWhiteSpace(searchModel.PartyTo))
{
sb.Append(string.Format(" and m.PartyTo='{0}'",searchModel.PartyTo));
}
if (!string.IsNullOrWhiteSpace(searchModel.PartyFrom))
{
sb.Append(string.Format(" and m.PartyFrom='{0}'", searchModel.PartyFrom));
}
else {
sb.Append(string.Format(" and m.PartyFrom in ('SQC','LOC')", searchModel.PartyFrom));
}
if (searchModel.OrderStrategy!=null)
{
if (searchModel.OrderStrategy.Value == 1 || searchModel.OrderStrategy.Value == 0)
{
sb.Append(string.Format(" and m.OrderStrategy in (0,1) "));
}
else
{
sb.Append(string.Format(" and m.OrderStrategy={0} ", searchModel.OrderStrategy.Value));
}
}
//sb.Append(") as t1");
if (command.SortDescriptors.Count == 0)
{
sb.Append(" order by WindowTime asc");
}
else
{
sb.Append(HqlStatementHelper.GetSortingStatement(command.SortDescriptors));
}
return sb;
}
private SearchStatementModel PrepareSearchStatement(GridCommand command, DatFileSearchModel searchModel)
{
string whereStatement = string.Empty;
if (!string.IsNullOrEmpty(searchModel.OrderNo))
{
whereStatement += "where WmsLine in (select Convert(varchar(50),Id) from OrderDetail where OrderNo='"+searchModel.OrderNo+"')";
}
IList<object> param = new List<object>();
HqlStatementHelper.AddLikeStatement("WmsNo", searchModel.WmsPickNo, HqlStatementHelper.LikeMatchMode.Anywhere, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("MoveType", searchModel.MoveType, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("Item", searchModel.Item, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("WMSId", searchModel.WMSId, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("LGORT", searchModel.LGORT, "l", ref whereStatement, param);
HqlStatementHelper.AddEqStatement("UMLGO", searchModel.UMLGO, "l", ref whereStatement, param);
if (searchModel.StartDate != null & searchModel.EndDate != null)
{
HqlStatementHelper.AddBetweenStatement("CreateDate", searchModel.StartDate, searchModel.EndDate, "l", ref whereStatement, param);
}
else if (searchModel.StartDate != null & searchModel.EndDate == null)
{
HqlStatementHelper.AddGeStatement("CreateDate", searchModel.StartDate, "l", ref whereStatement, param);
}
else if (searchModel.StartDate == null & searchModel.EndDate != null)
{
HqlStatementHelper.AddLeStatement("CreateDate", searchModel.EndDate, "l", ref whereStatement, param);
}
string sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
if (command.SortDescriptors.Count == 0)
{
sortingStatement = " order by CreateDate desc";
}
else
{
sortingStatement = HqlStatementHelper.GetSortingStatement(command.SortDescriptors);
}
SearchStatementModel searchStatementModel = new SearchStatementModel();
searchStatementModel.SelectCountStatement = "select count(*) from WMSDatFile as l";
searchStatementModel.SelectStatement = "select l from WMSDatFile as l";
searchStatementModel.WhereStatement = whereStatement;
searchStatementModel.SortingStatement = sortingStatement;
searchStatementModel.Parameters = param.ToArray<object>();
return searchStatementModel;
}
