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; }