public static List <ProductPurchaseQueryBasicInfo> QueryProductPurchase(ProductPurchaseQueryFilter queryFilter, out int totalCount) { DataTable dt = new DataTable(); #region CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductPurchase"); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = queryFilter.SortFields, StartRowIndex = queryFilter.PageIndex * queryFilter.PageSize, MaximumRows = queryFilter.PageSize }; BuildSearchPOCondition(queryFilter, dataCommand, pagingInfo); if (!string.IsNullOrEmpty(queryFilter.StatusList)) { dataCommand.CommandText = dataCommand.CommandText.Replace("@Status", " @Status OR PO.Status IN (1,2,3,-2,5,6)"); } EnumColumnList enumList = new EnumColumnList(); enumList.Add("status", typeof(PurchaseOrderStatus)); enumList.Add("PaySettleCompany", typeof(PaySettleCompany)); List <ProductPurchaseQueryBasicInfo> list = dataCommand.ExecuteEntityList <ProductPurchaseQueryBasicInfo>(); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(list); #endregion }
public ActionResult AjaxQuery() { ProductPurchaseQueryFilter queryCriteria = BuildQueryFilterEntity <ProductPurchaseQueryFilter>(); queryCriteria.VendorSysNo = UserAuthHelper.GetCurrentUser().SellerSysNo.ToString(); QueryResult <ProductPurchaseQueryBasicInfo> result = ProductPurchaseService.QueryProductPurchase(queryCriteria); return(AjaxGridJson(result)); }
public static QueryResult <ProductPurchaseQueryBasicInfo> QueryProductPurchase(ProductPurchaseQueryFilter queryCriteria) { int totalCount = 0; QueryResult <ProductPurchaseQueryBasicInfo> result = new QueryResult <ProductPurchaseQueryBasicInfo>(); List <ProductPurchaseQueryBasicInfo> list = ProductPurchaseDA.QueryProductPurchase(queryCriteria, out totalCount); result.ResultList = list; result.PageInfo = new PageInfo { PageIndex = queryCriteria.PageIndex, PageSize = queryCriteria.PageSize, TotalCount = totalCount, }; return(result); }
private static void BuildSearchPOCondition(ProductPurchaseQueryFilter 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.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); } 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 + ")"); } #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.LeaseFlag.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po.LeaseFlag", DbType.Int32, "@LeaseFlag", QueryConditionOperatorType.Equal, queryFilter.LeaseFlag.Value == true ? 1 : 0); } if (!string.IsNullOrEmpty(queryFilter.VerifyStatus)) { if (queryFilter.VerifyStatus == "1") { replaceSQL1 += " and po.TPStatus ='1' "; replaceSQL3 += " and po.TPStatus ='1' "; } if (queryFilter.VerifyStatus == "2") { replaceSQL1 += " and po.TPStatus = '2' "; replaceSQL3 += " and po.TPStatus = '2' "; } } 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() + "%'"; } if (queryFilter.BrandSysNo.HasValue) { replaceSQL1 += string.Format(@" AND EXISTS(SELECT TOP 1 1 FROM IPP3.dbo.PO_Item POI INNER JOIN IPP3.dbo.Product PRO ON POI.ProductSysNo = PRO.SysNo INNER JOIN [OverseaContentManagement].dbo.Brand BRA ON BRA.SysNo = PRO.BrandSysNo WHERE POI.POSysNo = PO.SysNO AND BRA.SysNo = {0}) ", queryFilter.BrandSysNo.Value); } dataCommand.CommandText = sqlBuilder.BuildQuerySql().Replace("replaceSQL1", replaceSQL1).Replace("replaceSQL2", replaceSQL2).Replace("replaceSQL3", replaceSQL3); if (pagingInfo != null) { dataCommand.SetParameterValue("@StartNumber", queryFilter.PageSize * queryFilter.PageIndex); dataCommand.SetParameterValue("@EndNumber", queryFilter.PageSize * queryFilter.PageIndex + queryFilter.PageSize); } } }