public DataTable QueryRMAList(PurchaseOrderQueryFilter queryFilter, out int totalCount) { DataTable dt = new DataTable(); CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetRMA"); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = queryFilter.PageInfo.SortBy, StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize, MaximumRows = queryFilter.PageInfo.PageSize }; using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "VendorName,OutTime,PMUserSysNo")) { string sql = "WHERE 1=1 "; if (queryFilter.PMList != null && queryFilter.PMList.Count > 0) { sql += "AND Product.PMUserSysNo IN ("; foreach (var pm in queryFilter.PMList) { sql += pm.ToString() + " , "; } sql = sql.Substring(0, sql.LastIndexOf(",") - 1) + ")"; } sql += " AND RMA_Register.ResponseDesc IS NOT NULL AND DateAdd(Day,15,RMA_OutBound.OutTime)< '" + DateTime.Now + "'"; sql += " AND RMA_Register.Status=1 AND RMA_Register.OutBoundStatus = 1 "; sql += " AND RMA_OutBound.CompanyCode= '" + queryFilter.CompanyCode + "'"; dataCommand.CommandText = builder.BuildQuerySql().Replace("StrWhere", sql); dt = dataCommand.ExecuteDataTable(); totalCount = (int)dataCommand.GetParameterValue("@TotalCount"); } return(dt); }
private void btnSearch_Click(object sender, RoutedEventArgs e) { if (!ValidationManager.Validate(this.SearchCondition)) { return; } if (queryVM.HasValidationErrors) { return; } queryFilter = EntityConverter <PurchaseOrderQueryVM, PurchaseOrderQueryFilter> .Convert(queryVM); newQueryFilter = queryFilter.DeepCopy(); if (this.chkAdvancedSearch.IsChecked.Value) { tempFilter = queryFilter; } else { IsNormalQuery(); tempFilter = newQueryFilter; } //TODO:权限管理,这里给查询赋予最高权限: tempFilter.PMQueryType = PMQueryType.All; this.QueryResultGrid.Bind(); }
void PurchaseOrderEIMSInvoiceList_Loaded(object sender, RoutedEventArgs e) { this.Loaded -= PurchaseOrderEIMSInvoiceList_Loaded; queryFilter = new PurchaseOrderQueryFilter(); serviceFacade = new PurchaseOrderFacade(CurrentPage); LoadAvailableEIMSList(); }
/// <summary> /// 查询采购单列表 /// </summary> /// <param name="queryFilter"></param> /// <param name="callback"></param> public void QueryPurchaseOrders(PurchaseOrderQueryFilter queryFilter, EventHandler <RestClientEventArgs <dynamic> > callback) { queryFilter.CompanyCode = CPApplication.Current.CompanyCode; string relativeUrl = "POService/PurchaseOrder/QueryPurchaseOrderList"; restClient.QueryDynamicData(relativeUrl, queryFilter, callback); }
public void ExportExcelForPurchaseOrders(PurchaseOrderQueryFilter queryFilter, ColumnSet[] columns) { queryFilter.CompanyCode = CPApplication.Current.CompanyCode; string relativeUrl = "POService/PurchaseOrder/QueryPurchaseOrderList"; restClient.ExportFile(relativeUrl, queryFilter, columns); }
public QueryResult QueryPurchaseOrderEIMSInvoiceInfo(PurchaseOrderQueryFilter queryFilter) { QueryResult result = new QueryResult() { Data = ObjectFactory <IPurchaseOrderQueryDA> .Instance.QuertPurchaseOrderEIMSInvoiceInfo(Convert.ToInt32(queryFilter.VendorSysNo)), TotalCount = 0 }; return(result); }
public QueryResult QueryPurchaseOrderHistory(PurchaseOrderQueryFilter filter) { int totalCount = 0; QueryResult result = new QueryResult() { Data = ObjectFactory <IPurchaseOrderQueryDA> .Instance.QueryPurchaseOrderHistory(filter, out totalCount) }; result.TotalCount = totalCount; return(result); }
public QueryResult QueryPurchaseOrderRMAList(PurchaseOrderQueryFilter request) { int totalCount = 0; QueryResult returnResult = new QueryResult() { Data = ObjectFactory <IPurchaseOrderQueryDA> .Instance.QueryRMAList(request, out totalCount) }; returnResult.TotalCount = totalCount; return(returnResult); }
private void btnSearch_Click(object sender, RoutedEventArgs e) { if (string.IsNullOrEmpty(queryVM.ProductSysNo) && string.IsNullOrEmpty(queryVM.ProductID)) { Window.Alert("商品ID和商品编号,至少要填写一项!"); return; } //搜索操作: filter = EntityConverter <PurchaseOrderQueryVM, PurchaseOrderQueryFilter> .Convert(this.queryVM); this.QueryResultGrid.Bind(); }
public QueryResult CountPurchaseOrderList(PurchaseOrderQueryFilter request) { request.PMAuthorizedList = new List <int>(); request.PMAuthorizedList = ObjectFactory <PurchaseOrderAppService> .Instance.GetAuthorizedPMList(request.PMQueryType.Value, request.CurrentUserName, request.CompanyCode).Select(x => x.SysNo.Value).ToList(); QueryResult returnResult = new QueryResult() { Data = ObjectFactory <IPurchaseOrderQueryDA> .Instance.CountPurchaseOrder(request) }; return(returnResult); }
public override void OnPageLoad(object sender, EventArgs e) { base.OnPageLoad(sender, e); this.serviceFacade = new PurchaseOrderFacade(this); filter = new PurchaseOrderQueryFilter(); if (!string.IsNullOrEmpty(this.Request.Param)) { string[] paramsArray = this.Request.Param.Split('|'); queryVM.ProductSysNo = paramsArray[0]; if (paramsArray.Length > 1) { queryVM.ProductID = paramsArray[1]; } btnSearch_Click(null, null); } this.DataContext = queryVM; }
private void QueryResultGrid_ExportAllClick(object sender, EventArgs e) { //权限控制: if (!AuthMgr.HasFunctionPoint(AuthKeyConst.PO_PurchaseOrder_ExportPOList)) { Window.Alert("对不起,你没有权限进行此操作!"); return; } //导出全部: if (null == tempFilter || this.QueryResultGrid.ItemsSource == null || this.QueryResultGrid.TotalCount == 0) { Window.Alert("没有可供导出的数据!"); return; } PurchaseOrderQueryFilter exportQueryRequest = Newegg.Oversea.Silverlight.Utilities.UtilityHelper.DeepClone <PurchaseOrderQueryFilter>(tempFilter); exportQueryRequest.PageInfo = new QueryFilter.Common.PagingInfo() { PageIndex = 0, PageSize = ConstValue.MaxRowCountLimit }; foreach (DataGridColumn col in QueryResultGrid.Columns) { if (col.Visibility == Visibility.Collapsed) { if (col is Newegg.Oversea.Silverlight.Controls.Data.DataGridTextColumn) { (col as Newegg.Oversea.Silverlight.Controls.Data.DataGridTextColumn).NeedExport = false; } else if (col is Newegg.Oversea.Silverlight.Controls.Data.DataGridTemplateColumn) { (col as Newegg.Oversea.Silverlight.Controls.Data.DataGridTemplateColumn).NeedExport = false; } } } ColumnSet columnSet = new ColumnSet(QueryResultGrid); columnSet.Add("status", ResPurchaseOrderQuery.GridHeader_Status); serviceFacade.ExportExcelForPurchaseOrders(exportQueryRequest, new ColumnSet[] { columnSet }); }
public DataTable CountPurchaseOrder(PurchaseOrderQueryFilter queryFilter) { var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetPOCount"); BuildCountPOCondition(queryFilter, dataCommand, new PagingInfoEntity()); var data = dataCommand.ExecuteDataTable(); #region 前台的显示控制 for (int i = -2; i <= 10; i++) { var rows = data.Select("status=" + i); //待分摊 Status=2 已去掉 if (rows.Length == 0 && i != 2) { data.Rows.Add(new string[4] { i.ToString(), "0", "0", "0" }); } } #endregion return(data); }
public override void OnPageLoad(object sender, EventArgs e) { queryVM = new PurchaseOrderQueryVM(); serviceFacade = new PurchaseOrderFacade(this); vendorFacade = new VendorFacade(this); queryFilter = new PurchaseOrderQueryFilter() { PageInfo = new QueryFilter.Common.PagingInfo() }; queryRMAFilter = new PurchaseOrderQueryFilter() { PageInfo = new QueryFilter.Common.PagingInfo() }; this.DataContext = queryVM; LoadComboBoxData(); base.OnPageLoad(sender, e); if (null != this.Request.QueryString) { if (this.Request.QueryString.Keys.Contains("ProductSysNo")) { queryVM.ProductSysNo = this.Request.QueryString["ProductSysNo"]; } if (this.Request.QueryString.Keys.Contains("QueryStatus")) { queryVM.StatusList = this.Request.QueryString["QueryStatus"]; } if (this.Request.QueryString.Keys.Contains("POSysNo")) { POSysNo = this.Request.QueryString["POSysNo"]; queryVM.POSysNoExtention = POSysNo; } btnSearch_Click(null, null); } SetAccessControl(); }
public DataTable QueryPurchaseOrderHistory(PurchaseOrderQueryFilter queryFilter, out int totalCount) { DataTable dt = new DataTable(); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = queryFilter.PageInfo.SortBy, StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize, MaximumRows = queryFilter.PageInfo.PageSize }; CustomDataCommand command = command = DataCommandManager.CreateCustomDataCommandFromConfig("POhistory"); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingInfo, "intime desc")) { if (!string.IsNullOrEmpty(queryFilter.ProductSysNo)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po_item.ProductSysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo); } sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "po_master.Status", QueryConditionOperatorType.In, "4,6,7,8"); if (!string.IsNullOrEmpty(queryFilter.StockSysNo) && queryFilter.StockSysNo != "-999999") { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po_master.StockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po_master.CompanyCode", DbType.Int32, "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode); command.CommandText = sqlBuilder.BuildQuerySql(); dt = command.ExecuteDataTable(); totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); } return(dt); }
public PurchaseOrderStatisticsQuery(PurchaseOrderQueryFilter queryFilter) { InitializeComponent(); filter = queryFilter; this.Loaded += new RoutedEventHandler(PurchaseOrderStatisticsQuery_Loaded); }
private void BuildSearchPOCondition(PurchaseOrderQueryFilter queryFilter, CustomDataCommand dataCommand, PagingInfoEntity pagingInfo) { using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "po.sysno desc")) { #region build search condition string replaceSQL1 = ""; string replaceSQL2 = "where 1=1"; string replaceSQL3 = "where 1=1"; if (queryFilter.ETATimeFrom.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.ETATime", DbType.DateTime, "@ETATimeFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.ETATimeFrom); } if (queryFilter.ETATimeTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.ETATime", DbType.DateTime, "@ETATimeTo", QueryConditionOperatorType.LessThan, queryFilter.ETATimeTo); } if (queryFilter.FromTotalAmount.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.TotalAmt", DbType.Decimal, "@FromTotalAmount", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.FromTotalAmount.Value); } if (queryFilter.ToTotalAmount.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.TotalAmt", DbType.Decimal, "@ToTotalAmount", QueryConditionOperatorType.LessThanOrEqual, queryFilter.ToTotalAmount.Value); } if (!string.IsNullOrEmpty(queryFilter.LogisticsNumber)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.LogisticsNumber", DbType.String, "@LogisticsNumber", QueryConditionOperatorType.Equal, queryFilter.LogisticsNumber); } if (!string.IsNullOrEmpty(queryFilter.ExpressName)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.ExpressName", DbType.String, "@ExpressName", QueryConditionOperatorType.Like, queryFilter.ExpressName); } if (queryFilter.CreateTimeBegin.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CreateTime", DbType.DateTime, "@CreateTime", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateTimeBegin); } if (queryFilter.CreateTimeTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CreateTime", DbType.DateTime, "@CreateTimeTo", QueryConditionOperatorType.LessThan, queryFilter.CreateTimeTo); } if (queryFilter.Status.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, (int)queryFilter.Status); } if (!string.IsNullOrEmpty(queryFilter.StatusList)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, 999); } if (queryFilter.IsConsign.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.IsConsign", DbType.Int32, "@IsConsign", QueryConditionOperatorType.Equal, (int)queryFilter.IsConsign); } if (!string.IsNullOrEmpty(queryFilter.VendorSysNo)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.VendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo); } #region 权限筛选 #region 添加供应商(创建PO单的对象) int vendorCreateUserSysNo = int.Parse(AppSettingManager.GetSetting("PO", "VendorCreateUserSysNo")); queryFilter.PMAuthorizedList.Add(vendorCreateUserSysNo); #endregion 添加供应商(创建PO单的对象) //by Jack.W.Wang 2012-11-8 CRL21776--------------------------BEGIN if (!(queryFilter.IsManagerPM ?? false)) { string sqlStr = @"Select ProductLineSysNo FROM OverseaContentManagement.dbo.V_CM_ProductLine_PMs AS p " + "WHERE PMUserSysNo=" + ServiceContext.Current.UserSysNo + " OR CHARINDEX(';'+CAST(" + ServiceContext.Current.UserSysNo + " AS VARCHAR(20))+';',';'+p.BackupPMSysNoList+';')>0"; sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "po.ProductLineSysNo", QueryConditionOperatorType.In, sqlStr); } var createUserSysNo = queryFilter.PMSysNo; if (!string.IsNullOrEmpty(createUserSysNo)) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "po.PMSysNo in(" + createUserSysNo + ")"); } //{ // var createIsValid = queryFilter.PMAuthorizedList.Exists( // x => x == Convert.ToInt32(createUserSysNo)); // if (createIsValid) // { // sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, // "po.PMSysNo in(" + createUserSysNo + ")"); // } // else // { // sqlBuilder.ConditionConstructor.AddInCondition( // QueryConditionRelationType.AND, // "po.PMSysNo", // DbType.Int32, // new List<int> { -999 }); // } //} //else //{ // sqlBuilder.ConditionConstructor.AddInCondition( // QueryConditionRelationType.AND, // "po.PMSysNo", // DbType.Int32, // queryFilter.PMAuthorizedList); //} //by Jack.W.Wang 2012-11-8 CRL21776--------------------------END #endregion if (string.IsNullOrEmpty(queryFilter.POSysNoExtention)) { if (queryFilter.POSysNo != "" && queryFilter.POSysNo != null && Regex.IsMatch(queryFilter.POSysNo, "^[0-9]+$")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.poid", DbType.String, "@poid", QueryConditionOperatorType.Like, queryFilter.POSysNo.Trim()); } } else { string[] poSysNoStr = queryFilter.POSysNoExtention.Split('.'); List <string> poSysNoList = new List <string>(); foreach (string s in poSysNoStr) { poSysNoList.Add(s); } sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "po.poid", DbType.String, poSysNoList); } if (!string.IsNullOrEmpty(queryFilter.IsApportion)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.IsApportion", DbType.Int32, "@IsApportion", QueryConditionOperatorType.Equal, queryFilter.IsApportion); } if (!string.IsNullOrEmpty(queryFilter.CreatePOSysNo)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CreateUserSysNo", DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, queryFilter.CreatePOSysNo); } if (queryFilter.PaySettleCompany.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.PaySettleCompany", DbType.Int32, "@PaySettleCompany", QueryConditionOperatorType.Equal, (int)queryFilter.PaySettleCompany); } if (!string.IsNullOrEmpty(queryFilter.CurrencySysNo)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CurrencySysNo", DbType.Int32, "@CurrencySysNo", QueryConditionOperatorType.Equal, queryFilter.CurrencySysNo); } if (!string.IsNullOrEmpty(queryFilter.IsStockStatus)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.PartlyReceiveStatus", DbType.Int32, "@PartlyReceiveStatus", QueryConditionOperatorType.Equal, queryFilter.IsStockStatus); } if (queryFilter.InStockFrom.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.InTime", DbType.DateTime, "@InTime", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.InStockFrom.Value); } if (queryFilter.InStockTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.InTime", DbType.DateTime, "@InStockTo", QueryConditionOperatorType.LessThan, queryFilter.InStockTo.Value.AddDays(1)); } if (queryFilter.POType.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.POType", DbType.Int32, "@POType", QueryConditionOperatorType.Equal, (int)queryFilter.POType); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CompanyCode", System.Data.DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode); if (queryFilter.VerifyStatus.HasValue) { if (queryFilter.VerifyStatus == PurchaseOrderVerifyStatus.WaitingTLAudit) { replaceSQL1 += " and po.TPStatus ='1' "; replaceSQL3 += " and po.TPStatus ='1' "; sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.Status", DbType.Int32, "@WaitingAudit", QueryConditionOperatorType.Equal, (int)PurchaseOrderStatus.WaitingAudit); } if (queryFilter.VerifyStatus == PurchaseOrderVerifyStatus.WaitingPMDAudit) { replaceSQL1 += " and po.TPStatus = '2' "; replaceSQL3 += " and po.TPStatus = '2' "; sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.Status", DbType.Int32, "@WaitingAudit", QueryConditionOperatorType.Equal, (int)PurchaseOrderStatus.WaitingAudit); } } if (!string.IsNullOrEmpty(queryFilter.ProductSysNo) && Regex.IsMatch(queryFilter.ProductSysNo, "^[0-9]+$")) { replaceSQL1 += string.Format(" and exists ( select top 1 sysno from ipp3.dbo.po_item po_item where po.sysno=po_item.posysno and productsysno = {0} )", queryFilter.ProductSysNo); replaceSQL3 += string.Format(" and exists ( select top 1 sysno from ipp3.dbo.po_item po_item where po.sysno=po_item.posysno and productsysno = {0} )", queryFilter.ProductSysNo); } if (!string.IsNullOrEmpty(queryFilter.StockSysNo)) { //添加在途商品查询 if (queryFilter.IsPurchaseQtySearch.HasValue && queryFilter.IsPurchaseQtySearch.Value) { //获取仓库数据 string[] stockArray = queryFilter.QueryStock.Split(','); List <int> stockList = new List <int>(stockArray.Length); for (int i = 0; i < stockArray.Length; i++) { stockList.Add(int.Parse(stockArray[i])); } sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "po.ITStockSysNo", DbType.Int32, stockList); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.StockSysNo", DbType.Int32, "@TransferStock", QueryConditionOperatorType.Equal, 50); sqlBuilder.ConditionConstructor.EndGroupCondition(); sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.OR, "po.StockSysNo", DbType.Int32, stockList); sqlBuilder.ConditionConstructor.EndGroupCondition(); } else { if (queryFilter.StockSysNo == "50") { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.StockSysNo", DbType.Int32, "@TransferStock", QueryConditionOperatorType.Equal, 50); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.ITStockSysNo", DbType.Int32, "@TransferMiddleStock", QueryConditionOperatorType.Equal, queryFilter.TranferStock); } else { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.StockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo); } } } if (queryFilter.IsPurchaseQtySearch.HasValue && queryFilter.IsPurchaseQtySearch.Value && !string.IsNullOrEmpty(queryFilter.QueryStatus)) { string[] statusArray = queryFilter.QueryStatus.Split(','); List <int> statusList = new List <int>(statusArray.Length); for (int i = 0; i < statusArray.Length; i++) { statusList.Add(int.Parse(statusArray[i])); } sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "po.Status", DbType.Int32, statusList); } #endregion if (queryFilter.PrintTime.HasValue) { replaceSQL2 += string.Format(" and exists (select top 1 1 from scm.dbo.poLog with(nolock) GROUP BY Purno having scm.dbo.poLog.purno= po.sysno and convert(varchar(10),MAX(recdate),120) = convert(varchar(10),'{0}',120))", queryFilter.PrintTime.Value.ToString("yyyy-MM-dd")); replaceSQL3 += string.Format(" and convert(varchar(10),a.MaxTastDate,120) = convert(varchar(10),'{0}',120)", queryFilter.PrintTime.Value.ToString("yyyy-MM-dd")); } if (!string.IsNullOrEmpty(queryFilter.AuditUser)) { replaceSQL2 += " and con_apport.DisplayName like '" + queryFilter.AuditUser.Trim() + "%' OR con_audit.DisplayName like '" + queryFilter.AuditUser.Trim() + "%'"; replaceSQL3 += " and con_apport.DisplayName like '" + queryFilter.AuditUser.Trim() + "%' OR con_audit.DisplayName like '" + queryFilter.AuditUser.Trim() + "%'"; } dataCommand.CommandText = sqlBuilder.BuildQuerySql().Replace("replaceSQL1", replaceSQL1).Replace("replaceSQL2", replaceSQL2).Replace("replaceSQL3", replaceSQL3); if (pagingInfo != null) { dataCommand.SetParameterValue("@StartNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex); dataCommand.SetParameterValue("@EndNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex + queryFilter.PageInfo.PageSize); } } }
private void BuildCountPOCondition(PurchaseOrderQueryFilter queryFilter, CustomDataCommand dataCommand, PagingInfoEntity pagingInfoEntity) { using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfoEntity, "po.sysno desc")) { #region build search condition string sql = ""; if (queryFilter.CreateTimeBegin.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CreateTime", DbType.DateTime, "@CreateTime", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateTimeBegin.Value); } if (queryFilter.CreateTimeTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CreateTime", DbType.DateTime, "@CreateTimeTo", QueryConditionOperatorType.LessThan, queryFilter.CreateTimeTo.Value.AddDays(1)); } if (!string.IsNullOrEmpty(queryFilter.AuditUser)) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "(con_apport.DisplayName like '" + queryFilter.AuditUser.Trim() + "%' OR con_audit.DisplayName like '" + queryFilter.AuditUser.Trim() + "%') "); } if (queryFilter.Status.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, (int)queryFilter.Status.Value); } if (queryFilter.IsConsign.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.IsConsign", DbType.Int32, "@IsConsign", QueryConditionOperatorType.Equal, (int)queryFilter.IsConsign); } if (!string.IsNullOrEmpty(queryFilter.VendorSysNo)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.VendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo); } #region 权限筛选 #region 添加商家(创建PO单的对象) int vendorCreateUserSysNo = int.Parse(AppSettingManager.GetSetting("PO", "VendorCreateUserSysNo")); queryFilter.PMAuthorizedList.Add(vendorCreateUserSysNo); #endregion 添加商家(创建PO单的对象) var createUserSysNo = queryFilter.PMSysNo; if (!string.IsNullOrEmpty(createUserSysNo)) { var createIsValid = queryFilter.PMAuthorizedList.Exists( x => x == Convert.ToInt32(createUserSysNo)); if (createIsValid) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "po.PMSysNo in(" + createUserSysNo + ")"); } else { sqlBuilder.ConditionConstructor.AddInCondition( QueryConditionRelationType.AND, "po.PMSysNo", DbType.Int32, new List <int> { -999 }); } } else { sqlBuilder.ConditionConstructor.AddInCondition( QueryConditionRelationType.AND, "po.PMSysNo", DbType.Int32, queryFilter.PMAuthorizedList); } #endregion if (string.IsNullOrEmpty(queryFilter.POSysNoExtention)) { if (queryFilter.POSysNo != "" && queryFilter.POSysNo != null && Regex.IsMatch(queryFilter.POSysNo, "^[0-9]+$")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.poid", DbType.String, "@poid", QueryConditionOperatorType.Like, queryFilter.POSysNo.Trim()); } } else { string[] poSysNoStr = queryFilter.POSysNoExtention.Split('.'); List <string> poSysNoList = new List <string>(); foreach (string s in poSysNoStr) { poSysNoList.Add(s); } sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "po.poid", DbType.String, poSysNoList); } if (queryFilter.IsApportion != "") { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.IsApportion", DbType.Int32, "@IsApportion", QueryConditionOperatorType.Equal, queryFilter.IsApportion); } if (queryFilter.CreatePOSysNo != "") { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CreateUserSysNo", DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, queryFilter.CreatePOSysNo); } if (queryFilter.CompanySysNo != "") { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.SettlementCompany", DbType.Int32, "@SettlementCompany", QueryConditionOperatorType.Equal, queryFilter.CompanySysNo); } if (queryFilter.CurrencySysNo != "") { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CurrencySysNo", DbType.Int32, "@CurrencySysNo", QueryConditionOperatorType.Equal, queryFilter.CurrencySysNo); } if (queryFilter.IsStockStatus != "") { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.PartlyReceiveStatus", DbType.Int32, "@PartlyReceiveStatus", QueryConditionOperatorType.Equal, queryFilter.IsStockStatus); } if (queryFilter.InStockFrom.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.InTime", DbType.DateTime, "@InTime", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.InStockFrom.Value); } if (queryFilter.InStockTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.InTime", DbType.DateTime, "@InStockTo", QueryConditionOperatorType.LessThan, queryFilter.InStockTo.Value.AddDays(1)); } if (queryFilter.PrintTime.HasValue) { sql += string.Format(" and convert(varchar(10),a.MaxTastDate,120) = convert(varchar(10),'{0}',120)", (queryFilter.PrintTime.Value).ToString("yyyy-MM-dd")); } if (queryFilter.POType.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.POType", DbType.Int32, "@POType", QueryConditionOperatorType.Equal, (int)queryFilter.POType); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode); if (queryFilter.VerifyStatus.HasValue) { if (queryFilter.VerifyStatus == PurchaseOrderVerifyStatus.WaitingTLAudit) { sql += " and po.TPStatus ='1' "; } if (queryFilter.VerifyStatus == PurchaseOrderVerifyStatus.WaitingPMDAudit) { sql += " and po.TPStatus = '2' "; } } if (!string.IsNullOrEmpty(queryFilter.ProductSysNo) && Regex.IsMatch(queryFilter.ProductSysNo, "^[0-9]+$")) { sql += string.Format(" and exists ( select top 1 sysno from ipp3.dbo.po_item po_item where po.sysno=po_item.posysno and productsysno = {0} )", queryFilter.ProductSysNo); } if (queryFilter.StockSysNo != "" || queryFilter.QueryStock != "") { //添加在途商品查询 if (queryFilter.IsPurchaseQtySearch == true) { //获取仓库数据 string[] stockArray = queryFilter.QueryStock.Split(','); List <int> stockList = new List <int>(stockArray.Length); for (int i = 0; i < stockArray.Length; i++) { stockList.Add(int.Parse(stockArray[i])); } sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "po.ITStockSysNo", DbType.Int32, stockList); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.StockSysNo", DbType.Int32, "@TransferStock", QueryConditionOperatorType.Equal, 50); sqlBuilder.ConditionConstructor.EndGroupCondition(); sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.OR, "po.StockSysNo", DbType.Int32, stockList); sqlBuilder.ConditionConstructor.EndGroupCondition(); } else { if (queryFilter.StockSysNo == "50") { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.StockSysNo", DbType.Int32, "@TransferStock", QueryConditionOperatorType.Equal, 50); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.ITStockSysNo", DbType.Int32, "@TransferMiddleStock", QueryConditionOperatorType.Equal, queryFilter.TranferStock); } else { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.StockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo); } } } if (queryFilter.IsPurchaseQtySearch == true && queryFilter.QueryStatus != string.Empty) { string[] statusArray = queryFilter.QueryStatus.Split(','); List <int> statusList = new List <int>(statusArray.Length); for (int i = 0; i < statusArray.Length; i++) { statusList.Add(int.Parse(statusArray[i])); } sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "po.Status", DbType.Int32, statusList); } #endregion dataCommand.CommandText = sqlBuilder.BuildQuerySql().Replace("ReplaceSql", sql); if (pagingInfoEntity != null) { dataCommand.SetParameterValue("@StartNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex); dataCommand.SetParameterValue("@EndNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex + queryFilter.PageInfo.PageSize); } } }