public virtual DataTable QueryInventoryList(InventoryQueryFilter queryFilter, out int totalCount) { DataTable dt = new DataTable(); if (queryFilter == null) { totalCount = 0; return(null); } var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryInventory"); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = queryFilter.PagingInfo.SortBy, StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize, MaximumRows = queryFilter.PagingInfo.PageSize }; using (var sqlBuilder = new DynamicQuerySqlBuilder( dataCommand.CommandText, dataCommand, pagingInfo, "b.SysNo")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, "8601");//[Mark][Alan.X.Luo 硬编码] if (queryFilter.ProductSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.SysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo.Value); } //if (string.IsNullOrEmpty(queryFilter.AuthorizedPMsSysNumber)) //{ // queryFilter.AuthorizedPMsSysNumber = "-999"; //} if (queryFilter.ManufacturerSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.ManufacturerSysNo", DbType.Int32, "@ManufacturerSysNo", QueryConditionOperatorType.Equal, queryFilter.ManufacturerSysNo.Value); } if (queryFilter.BrandSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.BrandSysNo", DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal, queryFilter.BrandSysNo.Value); } if (queryFilter.C3SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c3.SysNo", DbType.Int32, "@C3SysNo", QueryConditionOperatorType.Equal, queryFilter.C3SysNo.Value); } if (queryFilter.C2SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c2.SysNo", DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal, queryFilter.C2SysNo.Value); } if (queryFilter.C1SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c1.SysNo", DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal, queryFilter.C1SysNo.Value); } if (queryFilter.VendorSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "plm.LastVendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo.Value); } if (queryFilter.IsInventoryWarning.HasValue && queryFilter.IsInventoryWarning.Value == true) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "((pe.[SafeQty] IS NOT NULL) AND a.[AvailableQty]+a.[ConsignQty]<=pe.[SafeQty])"); } sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "b.PMUserSysNo", QueryConditionOperatorType.In, queryFilter.AuthorizedPMsSysNumber); dataCommand.CommandText = sqlBuilder.BuildQuerySql(); dt = dataCommand.ExecuteDataTable(); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); } if (queryFilter.IsUnPayShow.HasValue && queryFilter.IsUnPayShow.Value) { if (dt != null && dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { int productSysNo = int.Parse(row["ProductSysNo"].ToString()); row["UnPayOrderQty"] = GetUnPayQtyTotal(productSysNo); } } } return(dt); }
// 查询会员渠道信息 public DataTable GetProductChannelMemberPriceInfoUrl(ProductChannelInfoMemberQueryFilter queryCriteria , out int totalCount) { var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetProductChannelMemberPriceResult"); var pagingInfo = new PagingInfoEntity { SortField = queryCriteria.PagingInfo.SortBy, StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize, MaximumRows = queryCriteria.PagingInfo.PageSize }; using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "B.SysNo DESC")) { if (queryCriteria.ChannelSysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.SysNo", DbType.Int32, "@ChannelSysNo", QueryConditionOperatorType.Equal, queryCriteria.ChannelSysNo); } if (queryCriteria.C1SysNo != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C1.Sysno", DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal, queryCriteria.C1SysNo.Value); } if (queryCriteria.C2SysNo != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C2.Sysno", DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal, queryCriteria.C2SysNo.Value); } if (queryCriteria.C3SysNo != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C3.Sysno", DbType.Int32, "@C3SysNo", QueryConditionOperatorType.Equal, queryCriteria.C3SysNo.Value); } if (!string.IsNullOrEmpty(queryCriteria.ProductID)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "p.ProductID", DbType.String, "@ProductID", QueryConditionOperatorType.Equal, queryCriteria.ProductID); } //动态生成SQL语句 dataCommand.CommandText = sqlBuilder.BuildQuerySql(); DataTable dt = dataCommand.ExecuteDataTable(); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } }
/// <summary> /// 新建POitems /// </summary> /// <param name="entitylist"></param> /// <param name="docNo"></param> public virtual void InsertPOItem(List <APInvoicePOItemInfo> entitylist, int docNo) { if (entitylist != null && entitylist.Count > 0) { StringBuilder sqlBuilder = new StringBuilder(); string sqlHeader = @"INSERT INTO [OverseaInvoiceReceiptManagement].[dbo].[APInvoice_PO_Item] ([DocNo] ,[PoNo] ,[PoWarehouseNo] ,[PoCurrency] ,[PoAmt] ,[EIMSNo] ,[EIMSAmt] ,[EIMSNetAmt] ,[EIMSNetTaxAmt] ,[PoNetAmt] ,[PaymentAmt] ,[PoBaselineDate] ,[PoPaymentTerm] ,[Status] ,[InUser] ,[InDate] ,[OrderType] ,[BatchNumber] ,[PayableTaxAmt])"; sqlBuilder.Append(sqlHeader); int countFlag = 0; foreach (APInvoicePOItemInfo entity in entitylist) { sqlBuilder.Append("\r\nSELECT "); sqlBuilder.Append(docNo.ToString()); AppendNumeric(sqlBuilder, entity.PoNo); AppendNumeric(sqlBuilder, entity.PoStockSysNo ?? 0); AppendNumeric(sqlBuilder, 1); AppendNumeric(sqlBuilder, entity.PoAmt ?? 0); AppendText(sqlBuilder, entity.EIMSNo); AppendText(sqlBuilder, entity.EIMSAmt); if (entity.EIMSNetAmt == null) { entity.EIMSNetAmt = 0; } AppendText(sqlBuilder, entity.EIMSNetAmt); if (entity.EIMSNetTaxAmt == null) { entity.EIMSNetTaxAmt = 0; } AppendText(sqlBuilder, entity.EIMSNetTaxAmt); AppendNumeric(sqlBuilder, entity.PoNetAmt); AppendText(sqlBuilder, entity.PaymentAmt ?? 0); AppendText(sqlBuilder, entity.PoBaselineDate); AppendNumeric(sqlBuilder, entity.PoPaymentTerm); AppendText(sqlBuilder, "D"); AppendText(sqlBuilder, ""); AppendText(sqlBuilder, DateTime.Now); AppendNumeric(sqlBuilder, (int)entity.OrderType); AppendText(sqlBuilder, entity.BatchNumber); AppendNumeric(sqlBuilder, entity.PayableTaxAmt); if (++countFlag != entitylist.Count) { sqlBuilder.Append(" UNION ALL"); } } CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("InsertAPPOItem"); command.CommandType = CommandType.Text; command.CommandText = sqlBuilder.ToString(); command.ExecuteNonQuery(); } }
public decimal QueryCommissionTotalAmt(QueryFilter.PO.CommissionQueryFilter queryFilter) { DataTable dt = new DataTable(); CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("SearchCommissionTotalAmt"); string whereSql = "WHERE 1=1"; if (queryFilter.SysNo.HasValue) { dataCommand.AddInputParameter("@SysNo", DbType.Int32, queryFilter.SysNo.Value); whereSql += " AND commMaster.SysNo = @SysNo"; } if (queryFilter.VendorSysNo.HasValue) { dataCommand.AddInputParameter("@VendorSysNo", DbType.Int32, queryFilter.VendorSysNo.Value); whereSql += " AND commMaster.MerchantSysNo = @VendorSysNo"; } //InDate if (queryFilter.InDateBegin.HasValue) { whereSql += " AND commMaster.InDate >= @InDateBegin"; dataCommand.AddInputParameter("@InDateBegin", DbType.DateTime, queryFilter.InDateBegin.Value); } if (queryFilter.InDateEnd.HasValue) { whereSql += " AND commMaster.InDate <= @InDateEnd"; dataCommand.AddInputParameter("@InDateEnd", DbType.DateTime, queryFilter.InDateEnd.Value.AddDays(1)); } //OutListDate出单时间 if (queryFilter.OutListDateBegin.HasValue) { whereSql += " AND commMaster.EndDate >= @OutListDateBegin"; dataCommand.AddInputParameter("@OutListDateBegin", DbType.DateTime, queryFilter.OutListDateBegin.Value); } if (queryFilter.OutListDateEnd.HasValue) { whereSql += " AND commMaster.EndDate <= @OutListDateEnd"; dataCommand.AddInputParameter("@OutListDateEnd", DbType.DateTime, queryFilter.OutListDateEnd.Value.AddDays(1)); } if (!string.IsNullOrEmpty(queryFilter.CompanyCode)) { whereSql += " AND commMaster.CompanyCode = @CompanyCode "; dataCommand.AddInputParameter("@CompanyCode", DbType.String, queryFilter.CompanyCode); } if (queryFilter.PageInfo != null) { dataCommand.AddOutParameter("@TotalCount", DbType.Int32, 4); dataCommand.AddOutParameter("@TotalAmt", DbType.String, 10); } dataCommand.CommandText = dataCommand.CommandText.Replace("#StrWhere#", whereSql); EnumColumnList list = new EnumColumnList(); list.Add("Status", typeof(VendorCommissionMasterStatus)); dataCommand.ExecuteScalar(); decimal totalAmt = Convert.ToDecimal(dataCommand.GetParameterValue("@TotalAmt")); return(totalAmt); }
private DataTable GetProductDomainList(ProductDomainFilter filter, out int totalCount) { CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("ProductDomain_GetProductDomainList"); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = filter.PagingInfo.SortBy, StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize, MaximumRows = filter.PagingInfo.PageSize }; using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder( command.CommandText, command, pagingInfo, "D.[SysNo] DESC")) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "D.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "D.SysNo", DbType.Int32, "@ProductDomainSysNo", QueryConditionOperatorType.Equal, filter.ProductDomainSysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "D.ProductDomainName", DbType.String, "@ProductDomainName", QueryConditionOperatorType.Like, filter.ProductDomainName); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.BrandSysNo", DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal, filter.BrandSysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.C1SysNo", DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal, filter.Category1SysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.C2SysNo", DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal, filter.Category2SysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.PMSysNo", DbType.Int32, "@PMSysNo", QueryConditionOperatorType.Equal, filter.PMSysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "D.ProductDomainLeaderUserSysNo", DbType.Int32, "@ProductDomainLeaderUserSysNo", QueryConditionOperatorType.Equal, filter.ProductDomainLeaderUserSysNo); command.CommandText = builder.BuildQuerySql(); DataTable result = command.ExecuteDataTable(new EnumColumnList { { "Status", typeof(ValidStatus) } }); totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); var list = from e in result.Rows.Cast <DataRow>() select new { SysNo = (int)e["SysNo"] }; var relatedProductDomains = list.Select(e => e.SysNo); var cmd = DataCommandManager.GetDataCommand("ProductDepartment_MerchandiserList"); cmd.SetParameterValue("@ProductDomainSysNoList", relatedProductDomains.Join(",")); DataTable dt = cmd.ExecuteDataTable(); var merchandiserList = from e in dt.Rows.Cast <DataRow>() select new { SysNo = (int)e["SysNo"], ProductDomainSysNo = (int)e["ProductDomainSysNo"], MerchandiserSysNo = (int)e["MerchandiserSysNo"] }; var realtedMerchandiserList = (from e in merchandiserList group e by e.ProductDomainSysNo).ToDictionary(e => e.Key, e => e.ToList()); result.Columns.Add("DepartmentMerchandiserNameList", typeof(string)); result.Columns.Add("DepartmentMerchandiserSysNoListStr", typeof(string)); foreach (DataRow row in result.Rows) { int sysNo = int.Parse(row["SysNo"].ToString()); if (realtedMerchandiserList.ContainsKey(sysNo)) { row["DepartmentMerchandiserSysNoListStr"] = realtedMerchandiserList[sysNo].Select(p => p.MerchandiserSysNo).ToList().Join(";"); row["DepartmentMerchandiserNameList"] = GetUserNameList(realtedMerchandiserList[sysNo].Select(p => p.MerchandiserSysNo).ToList().Join(";"), filter.CompanyCode); } row["BackupUserList"] = GetUserNameList(row["BackupUserList"].ToString(), filter.CompanyCode); } return(result); } }
public DataTable FinanceQuery(FinanceQueryFilter filter, out int totalCount, out double totalPayAmt) { string sqlName = string.Empty; string orderStr = "SysNo DESC"; if (filter.IsGroupByVendor == true) { sqlName = "Invoice_Query_FinanceGroupByVendor"; } else { sqlName = "Invoice_Query_Finance"; if (!string.IsNullOrEmpty(filter.PagingInfo.SortBy)) { orderStr = filter.PagingInfo.SortBy; } } CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig(sqlName); using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), orderStr)) { cmd.CommandText = sb.BuildQuerySql(); if (filter.IsGroupByVendor == true) { AddFinanceParametersGroupByVendor(cmd, filter); } else { AddFinanceParameters(filter, cmd); } //合计已到应付总额 cmd.AddOutParameter("@TotalPayableAmt", DbType.Double, 12); DataTable dt = cmd.ExecuteDataTable(); EnumColumnList enumColList = new EnumColumnList(); CodeNamePairColumnList codeNameColList = new CodeNamePairColumnList(); if (filter.IsGroupByVendor == false || filter.IsGroupByVendor == null) { enumColList.Add("AuditStatus", typeof(PayableAuditStatus)); //codeNameColList.Add("OrderType", "Invoice", "OrderType"); enumColList.Add("OrderType", typeof(PayableOrderType)); //codeNameColList.Add("OrderStatus", "Invoice", "OrderStatus"); codeNameColList.Add("InvoiceStatus", "Invoice", "InvoiceStatus"); codeNameColList.Add("IsConsign", "Invoice", "VendorType"); } else { codeNameColList.Add("IsConsign", "Invoice", "VendorType"); } cmd.ConvertColumn(dt, enumColList, codeNameColList); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); var totalPayableAmtParam = cmd.GetParameterValue("@TotalPayableAmt"); if (totalPayableAmtParam != DBNull.Value) { totalPayAmt = Convert.ToDouble(totalPayableAmtParam); } else { totalPayAmt = 0.0; } return(dt); } }
public System.Data.DataTable QueryCollectionPayment(QueryFilter.PO.CollectionPaymentFilter queryFilter, out int totalCount) { DataTable dt = new DataTable(); CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCollVendorSettle"); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = queryFilter.PageInfo.SortBy, StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize, MaximumRows = queryFilter.PageInfo.PageSize }; using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingInfo, "Settle.[SysNo] DESC")) { if (queryFilter != null) { if (queryFilter.CreateDateFrom.HasValue) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.CreateTime", DbType.DateTime, "@CreateTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateDateFrom.Value); } if (queryFilter.CreateDateTo.HasValue) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.CreateTime", DbType.DateTime, "@CreateDateTo", QueryConditionOperatorType.LessThan, queryFilter.CreateDateTo.Value.AddDays(1)); } builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "UserInfo.DisPlayName", DbType.String, "@DisplayName", QueryConditionOperatorType.Like, queryFilter.CreateUser); if (queryFilter.SettledDateFrom.HasValue) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.SettleTime", DbType.DateTime, "@SettleTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.SettledDateFrom.Value); } if (queryFilter.SettledDateTo.HasValue) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.SettleTime", DbType.DateTime, "@SettledDateTo", QueryConditionOperatorType.LessThan, queryFilter.SettledDateTo.Value.AddDays(1)); } if (queryFilter.PaySettleCompany.HasValue) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Vendor.PaySettleCompany", DbType.Int32, "@PaySettleCompany", QueryConditionOperatorType.Equal, queryFilter.PaySettleCompany.Value); } //是否自动结算 if (queryFilter.IsAutoSettle == AutoSettleStatus.Auto.ToString()) { builder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "Vendor.PayPeriodType", DbType.Int32, new List <int> { 72, 73, 74 }); } else if (queryFilter.IsAutoSettle == AutoSettleStatus.Hand.ToString()) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Vendor.PayPeriodType", DbType.Int32, "@PayPeriodType", QueryConditionOperatorType.Equal, 71); } /////////////crl17950 代销结算单增加Pm查询条件/////////////////////////// if (queryFilter.PMSysno.HasValue) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.PMSysno", DbType.Int32, "@PMSysno", QueryConditionOperatorType.Equal, queryFilter.PMSysno); } ////////////////////////////////////////////////////////////////////////// builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.SettleID", DbType.String, "@SettleID", QueryConditionOperatorType.Equal, queryFilter.SettleID); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.StockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo); //builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Vendor.VendorName", DbType.String, // "@VendorName", QueryConditionOperatorType.Like, query.Condition.VendorName); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.VendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.Memo", DbType.String, "@Memo", QueryConditionOperatorType.Like, queryFilter.Memo); } //builder.ConditionConstructor.AddInCondition( // QueryConditionRelationType.AND, "Settle.PMSysno", DbType.Int32, AuthorizedPMs()); // ProviderHelper.SetCommonParams(builder, "Settle"); command.CommandText = builder.BuildQuerySql(); EnumColumnList colList = new EnumColumnList(); colList.Add("PaySettleCompany", typeof(PaySettleCompany)); //{ // { 19, typeof(SettleStatus) } //}; CodeNamePairColumnList codeNameColList = new CodeNamePairColumnList(); codeNameColList.Add("PayPeriodType", "Invoice", "VendorPayPeriod"); //colList.Add("PayPeriodType", typeof(VendorPayPeriodType)); colList.Add("Status", typeof(POCollectionPaymentSettleStatus)); colList.Add("IsConsign", typeof(PurchaseOrderConsignFlag)); dt = command.ExecuteDataTable(colList, codeNameColList); if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["CreateUser"] = (dt.Rows[i]["CreateTime"] == null || dt.Rows[i]["CreateTime"] == DBNull.Value) ? string.Empty : string.Format("{0}[{1}]", dt.Rows[i]["CreateUser"].ToString(), dt.Rows[i]["CreateTime"].ToString()); dt.Rows[i]["AuditUser"] = (dt.Rows[i]["AuditTime"] == null || dt.Rows[i]["AuditTime"] == DBNull.Value) ? string.Empty : string.Format("{0}[{1}]", dt.Rows[i]["AuditUser"].ToString(), dt.Rows[i]["AuditTime"].ToString()); dt.Rows[i]["SettleUser"] = (dt.Rows[i]["SettleTime"] == null || dt.Rows[i]["SettleTime"] == DBNull.Value) ? string.Empty : string.Format("{0}[{1}]", dt.Rows[i]["SettleUser"].ToString(), dt.Rows[i]["SettleTime"].ToString()); } } totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount")); } return(dt); }
public DataTable QuerySellerProductRequest(SellerProductRequestQueryFilter queryCriteria, out int totalCount) { var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QuerySellerProductRequest"); var pagingInfo = new PagingInfoEntity { SortField = queryCriteria.PagingInfo.SortBy, StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize, MaximumRows = queryCriteria.PagingInfo.PageSize }; using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "A.SysNo DESC")) { if (queryCriteria.C1SysNo != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "E.C1Sysno", DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal, queryCriteria.C1SysNo.Value); } if (queryCriteria.C2SysNo != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "E.Sysno", DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal, queryCriteria.C2SysNo.Value); } if (queryCriteria.C3SysNo != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "D.Sysno", DbType.Int32, "@C3SysNo", QueryConditionOperatorType.Equal, queryCriteria.C3SysNo.Value); } if (queryCriteria.ProductSysNo != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "B.SysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryCriteria.ProductSysNo.Value); } if (!string.IsNullOrEmpty(queryCriteria.ProductID)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "B.ProductID", DbType.String, "@ProductID", QueryConditionOperatorType.Equal, queryCriteria.ProductID); } if (queryCriteria.RequestStartDate != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.InDate", DbType.DateTime, "@RequestStartDate", QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.RequestStartDate.Value); } if (queryCriteria.RequestEndDate != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.InDate", DbType.DateTime, "@RequestEndDate", QueryConditionOperatorType.LessThan, queryCriteria.RequestEndDate.Value); } if (!string.IsNullOrEmpty(queryCriteria.ProductName)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.ProductName ", DbType.String, "@ProductName", QueryConditionOperatorType.Like, queryCriteria.ProductName); } if (!string.IsNullOrEmpty(queryCriteria.Auditor)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Auditor", DbType.String, "@Auditor", QueryConditionOperatorType.Like, queryCriteria.Auditor); } if (!string.IsNullOrEmpty(queryCriteria.EditUser)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.EditUser", DbType.String, "@EditUser", QueryConditionOperatorType.Like, queryCriteria.EditUser); } if (!string.IsNullOrEmpty(queryCriteria.CommonSKUNumber)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.CommonSKUNumber", DbType.String, "@CommonSKUNumber", QueryConditionOperatorType.Like, queryCriteria.CommonSKUNumber); } if (!queryCriteria.Status.ToString().Equals("0")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Status", DbType.String, "@Status", QueryConditionOperatorType.Equal, queryCriteria.Status); } if (!queryCriteria.Type.ToString().Equals("0")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Type", DbType.String, "@Type", QueryConditionOperatorType.Equal, queryCriteria.Type); } //else //{ // sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, // "A.Type", // DbType.String, "@Type", // QueryConditionOperatorType.In, // new List<object> { SellerProductRequestType.NewCreated, SellerProductRequestType.ParameterUpdate }); //} dataCommand.CommandText = sqlBuilder.BuildQuerySql(); var enumList = new EnumColumnList { { "Status", typeof(SellerProductRequestStatus) } , { "Type", typeof(SellerProductRequestType) } , { "IsTakePictures", typeof(SellerProductRequestTakePictures) } , { "IsConsign", typeof(VendorConsignFlag) } , { "IsOfferInvoice", typeof(SellerProductRequestOfferInvoice) } }; DataTable dt = dataCommand.ExecuteDataTable(enumList); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } }
public QueryResult <ComboQueryResult> Query(ComboQueryFilter filter) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.SortField = filter.SortFields; pagingEntity.MaximumRows = filter.PageSize; pagingEntity.StartRowIndex = filter.PageIndex * filter.PageSize; if (!string.IsNullOrEmpty(pagingEntity.SortField) && pagingEntity.SortField.Contains("DiscountAmt")) { pagingEntity.SortField = pagingEntity.SortField.Replace("DiscountAmt", "Isnull(Sum(si.[Quantity] * si.[Discount]),0)"); } //if (!string.IsNullOrEmpty(pagingEntity.SortField) && pagingEntity.SortField.Contains("PriceDiff")) //{ // pagingEntity.SortField = pagingEntity.SortField.Replace("PriceDiff", "Isnull(Sum(pr.[CurrentPrice]*si.[Quantity] - pr.UnitCost*si.[Quantity]+si.[Quantity]* si.[Discount]),0)"); //} var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCombo"); using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "sm.[CreateTime] DESC")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sm.SysNo", DbType.Int32, "@SystemNumber", QueryConditionOperatorType.Equal, filter.SysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sm.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.Status); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sm.SaleRuleName", DbType.String, "@SaleRuleName", QueryConditionOperatorType.Like, filter.Name); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "sm.VendorSysNo", DbType.Int32, "@MerchantSysNo", QueryConditionOperatorType.Equal, filter.SellerSysNo); cmd.CommandText = sqlBuilder.BuildQuerySql(); //构造商品系统编号,商品编号查询参数 string strWhere = ""; if (filter.ProductSysNo.HasValue) { strWhere += " and p.SysNo=@ProductSysNo "; cmd.AddInputParameter("@ProductSysNo", DbType.Int32, filter.ProductSysNo); } if (!string.IsNullOrWhiteSpace(filter.ProductID)) { strWhere += " and p.ProductID=@ProductID"; cmd.AddInputParameter("@ProductID", DbType.AnsiString, filter.ProductID); } cmd.ReplaceParameterValue("#StrWhere_Product#", strWhere); var dataList = cmd.ExecuteEntityList <ComboQueryResult>(); int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); var result = new QueryResult <ComboQueryResult>(); result.ResultList = dataList; result.PageInfo = new PageInfo() { TotalCount = totalCount, PageIndex = filter.PageIndex, PageSize = filter.PageSize }; return(result); } }
/// <summary> /// 查询代购订单信息。 /// </summary> /// <param name="filter"></param> /// <param name="totalCount"></param> /// <returns></returns> public DataSet QueryPurchaseOrderInfo(NeweggAmbassadorOrderQueryFilter filter, out int totalCount) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.SortField = filter.PagingInfo.SortBy; pagingEntity.MaximumRows = filter.PagingInfo.PageSize; pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize; var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetPanliOrderList"); cmd.SetParameterValue("@CustomersType", filter.SelectedUserType); cmd.SetParameterValue("@NeweggAmbassadorID", filter.CustomerID); cmd.SetParameterValue("@SOID", filter.OrderSysNo); cmd.SetParameterValue("@OrderDateFrom", filter.OrderTimeFrom); cmd.SetParameterValue("@OrderDateTo", filter.OrderTimeTo); cmd.SetParameterValue("@PointCreateFrom", filter.PointTimeFrom); cmd.SetParameterValue("@PointCreateTo", filter.PointTimeTo); cmd.SetParameterValue("@SOStatus", filter.SelectedSOStatus); cmd.SetParameterValue("@PointStatus", filter.SelectedPointStatus); cmd.SetParameterValue("@BigProvinceSysNo", filter.BigAreaSysNo); if (!string.IsNullOrEmpty(filter.PagingInfo.SortBy)) { string[] sortInfo = filter.PagingInfo.SortBy.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); if (sortInfo.Length > 1) { cmd.SetParameterValue("@SortField", sortInfo[0]); cmd.SetParameterValue("@SortType", sortInfo[1]); } else { cmd.SetParameterValue("@SortField", null); cmd.SetParameterValue("@SortType", null); } } else { cmd.SetParameterValue("@SortField", null); cmd.SetParameterValue("@SortType", null); } cmd.SetParameterValue("@PageSize", filter.PagingInfo.PageSize); cmd.SetParameterValue("@PageCurrent", filter.PagingInfo.PageIndex); cmd.SetParameterValue("@CompanyCode", filter.CompanyCode); DataSet ds = cmd.ExecuteDataSet(); EnumColumnList enumConfigNews = new EnumColumnList(); enumConfigNews.Add("SOStatus", typeof(SOStatus)); enumConfigNews.Add("RASysNo", typeof(NYNStatus)); enumConfigNews.Add("IsAddPoint", typeof(PointStatus)); enumConfigNews.Add("PayStatus", typeof(PayStatus)); cmd.ConvertEnumColumn(ds.Tables[0], enumConfigNews); totalCount = Convert.ToInt32(ds.Tables[1].Rows[0]["TotalCount"]); return(ds); }
public System.Data.DataTable QueryVirtualPurchaseOrderList(QueryFilter.PO.VirtualPurchaseOrderQueryFilter queryFilter, out int totalCount) { totalCount = 0; DataTable dt = new DataTable(); DataTable returnDt = new DataTable(); CustomDataCommand dataCommand; PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = queryFilter.PageInfo.SortBy, StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize, MaximumRows = queryFilter.PageInfo.PageSize }; if (queryFilter.DelayDays.HasValue || queryFilter.EstimateDelayDays.HasValue) { if (queryFilter.IsHasHistory == true) { dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryAllVSPO"); } else { dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryAllVSPONoHistory"); } } else { if (queryFilter.IsHasHistory == true) { dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryVSPO"); } else { dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryVSPONohistory"); } } #region using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "VSO.sysno desc")) { if (queryFilter.CreateDateFrom.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.CreateTime", DbType.DateTime, "@CreateTime", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateDateFrom.Value); } if (queryFilter.CreateDateTo.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.CreateTime", DbType.DateTime, "@CreateTimeTo", QueryConditionOperatorType.LessThan, queryFilter.CreateDateTo.Value.AddDays(1)); } if (queryFilter.PayTypeSysNo.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOMaster.PayTypeSysNo", DbType.Int32, "@PayTypeSysNo", QueryConditionOperatorType.Equal, queryFilter.PayTypeSysNo); } if (queryFilter.PMLeaderSysNo.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Product.PMUserSysNo", DbType.Int32, "@PMLeaderSysNo", QueryConditionOperatorType.Equal, queryFilter.PMLeaderSysNo); } if (queryFilter.PMExecSysNo.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.PMHandleUserSysNo", DbType.Int32, "@PMExecSysNo", QueryConditionOperatorType.Equal, queryFilter.PMExecSysNo); } if (queryFilter.ProductSysNo.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.ProductSysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo); } if (queryFilter.SOStatus.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOMaster.Status", DbType.Int32, "@SOStatus", QueryConditionOperatorType.Equal, queryFilter.SOStatus); } if (queryFilter.Status.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status); } if (queryFilter.StockSysNo.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOItem.WarehouseNumber", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo); } if (queryFilter.InStockOrderType.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.InStockOrderType", DbType.Int32, "@InStockOrderType", QueryConditionOperatorType.Equal, queryFilter.InStockOrderType); } string sysNo = ""; if (!string.IsNullOrEmpty(queryFilter.VSPOSysNo) && !string.IsNullOrWhiteSpace(queryFilter.VSPOSysNo)) { sysNo = " AND VSO.SysNo IN ( " + ArraryConvertToString(queryFilter.VSPOSysNo, '.') + " ) "; } string soSysNo = ""; if (!string.IsNullOrWhiteSpace(queryFilter.SOSysNo) && !string.IsNullOrEmpty(queryFilter.SOSysNo)) { soSysNo = " AND VSO.SOSysNo IN ( " + ArraryConvertToString(queryFilter.SOSysNo, '.') + " ) "; } sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.CompanyCode", System.Data.DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode); if (queryFilter.InStockStatus.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VSO.InStockStatus", DbType.Int32, "@InStockStatus", QueryConditionOperatorType.Equal, queryFilter.InStockStatus); } if (queryFilter.POStatus.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "pomaster.Status", DbType.Int32, "@POStatus", QueryConditionOperatorType.Equal, queryFilter.POStatus); } if (queryFilter.ShiftStatus.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "st_shift.Status", DbType.Int32, "@st_shift", QueryConditionOperatorType.Equal, queryFilter.ShiftStatus); } if (queryFilter.TransferStatus.HasValue) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "St_Transfer.Status", DbType.Int32, "@St_Transfer", QueryConditionOperatorType.Equal, queryFilter.TransferStatus); } if (queryFilter.C3SysNo.HasValue && queryFilter.C3SysNo.Value > 0) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "category.Category3Sysno", DbType.Int32, "@c3sysno", QueryConditionOperatorType.Equal, queryFilter.C3SysNo); } else { if (queryFilter.C2SysNo.HasValue && queryFilter.C2SysNo.Value > 0) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "category.Category2Sysno", DbType.Int32, "@c2sysno", QueryConditionOperatorType.Equal, queryFilter.C2SysNo); } else { if (queryFilter.C1SysNo.HasValue && queryFilter.C1SysNo.Value > 0) { sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "category.Category1Sysno", DbType.Int32, "@c1sysno", QueryConditionOperatorType.Equal, queryFilter.C1SysNo); } } } dataCommand.CommandText = sb.BuildQuerySql().Replace("AppendSql", sysNo + soSysNo); if (queryFilter.POStatus.HasValue || queryFilter.ShiftStatus.HasValue || queryFilter.TransferStatus.HasValue) { string sqlStatusQuery = @" LEFT JOIN ipp3.dbo.PO_Item poitem (NOLOCK) ON (POItem.POSysNo = VSO.InStockOrderSysNo and VSO.ProductSysNo = POItem.ProductSysNo) LEFT JOIN ipp3.dbo.St_Shift_Item st_shift_item WITH (NOLOCK) ON (st_shift_item.shiftsysno = VSO.InStockOrderSysNo and VSO.ProductSysNo = st_shift_item.ProductSysNo) LEFT JOIN ipp3.dbo.st_Transfer_Item St_Transfer_Item WITH (NOLOCK) ON (St_Transfer_Item.TransferSysNo=VSO.InStockOrderSysNo and VSO.ProductSysNo=St_Transfer_Item.ProductSysNo and TransferType=2) " ; dataCommand.CommandText = dataCommand.CommandText.Replace("#StatusQuery#", sqlStatusQuery); } else { dataCommand.CommandText = dataCommand.CommandText.Replace("#StatusQuery#", string.Empty); } if (queryFilter.C3SysNo.HasValue || queryFilter.C2SysNo.HasValue || queryFilter.C1SysNo.HasValue) { string sqlCategoryQuery = @" LEFT JOIN OverseaContentManagement.dbo.V_CM_CategoryInfo category WITH (NOLOCK) ON product.C3SysNo = category.Category3Sysno" ; dataCommand.CommandText = dataCommand.CommandText.Replace("#CategoryQuery#", sqlCategoryQuery); } else { dataCommand.CommandText = dataCommand.CommandText.Replace("#CategoryQuery#", string.Empty); } EnumColumnList columnEnums = new EnumColumnList(); columnEnums.Add("Status", typeof(VirtualPurchaseOrderStatus)); columnEnums.Add("SOStatus", typeof(SOStatus)); columnEnums.Add("POStatus", typeof(PurchaseOrderStatus)); columnEnums.Add("ShiftStatus", typeof(ShiftRequestStatus)); columnEnums.Add("TransferStatus", typeof(ConvertRequestStatus)); columnEnums.Add("InStockStatus", typeof(InStockStatus)); columnEnums.Add("InStockOrderType", typeof(VirtualPurchaseInStockOrderType)); dt = dataCommand.ExecuteDataTable(columnEnums); returnDt = dt.Copy(); if (!queryFilter.DelayDays.HasValue && !queryFilter.EstimateDelayDays.HasValue) { totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); } int DelayDays = 0; if (queryFilter.DelayDays.HasValue) { DelayDays = Convert.ToInt32(queryFilter.DelayDays.ToString().Trim()); } foreach (DataRow item in dt.Rows) { VirtualPurchaseInStockOrderType InStockOrderType = (VirtualPurchaseInStockOrderType)Enum.Parse(typeof(VirtualPurchaseInStockOrderType), item["InStockOrderType"].ToString()); DateTime NowDate = DateTime.Now; switch (InStockOrderType) { case VirtualPurchaseInStockOrderType.PO: NowDate = item["RealInstockTime"] != null && item["RealInstockTime"] != DBNull.Value ? Convert.ToDateTime(item["RealInstockTime"].ToString()) : DateTime.Now; break; case VirtualPurchaseInStockOrderType.Convert: NowDate = item["RealInStockTimeForTransfer"] != null && item["RealInStockTimeForTransfer"] != DBNull.Value ? Convert.ToDateTime(item["RealInStockTimeForTransfer"].ToString()) : DateTime.Now; break; case VirtualPurchaseInStockOrderType.Shift: NowDate = item["RealInStockTimeForShift"] != null && item["RealInStockTimeForShift"] != DBNull.Value ? Convert.ToDateTime(item["RealInStockTimeForShift"].ToString()) : DateTime.Now; break; } CheckDelayDays(item, NowDate, Convert.ToDateTime(item["CreateTime"].ToString()), DelayDays); } if (queryFilter.EstimateDelayDays.HasValue) { foreach (DataRow item in dt.Rows) { DateTime NowDate = DateTime.Now; if (item["EstimateArriveTime"] != null && item["EstimateArriveTime"] != DBNull.Value) { NowDate = Convert.ToDateTime(item["EstimateArriveTime"]); } CheckYJDelayDays(item, NowDate, Convert.ToDateTime(item["CreateTime"].ToString()), Convert.ToInt32(queryFilter.EstimateDelayDays.Value.ToString())); } } SetResult(dt); if (queryFilter.DelayDays.HasValue || queryFilter.EstimateDelayDays.HasValue) { int startNumber = queryFilter.PageInfo.PageSize.Value * queryFilter.PageInfo.PageIndex.Value; int pageSize = queryFilter.PageInfo.PageSize.Value; //if (ToExecel) //{ // startNumber = 0; // pageSize = 1000000; //} //IEnumerable<DataRow> list = dt.Clone().AsEnumerable(); List <DataRow> list = new List <DataRow>(); foreach (DataRow dr in dt.Rows) { list.Add(dr); } if (queryFilter.DelayDays.HasValue) { list = list.Where(a => (int)a["IsDelay"] == 1).ToList(); } if (queryFilter.EstimateDelayDays.HasValue) { list = list.Where(a => (int)a["DelayDays"] == 1).ToList(); } totalCount = list.Count(); list = list.OrderBy(p => p["RowNumber"]).Skip(startNumber).Take(pageSize).ToList(); returnDt.Rows.Clear(); foreach (DataRow dr in list) { returnDt.ImportRow(dr); } } return(returnDt); } #endregion }
public DataTable QueryInvoiceInput(InvoiceInputQueryFilter query, out int totalCount) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.SortField = query.PagingInfo.SortBy; pagingEntity.MaximumRows = query.PagingInfo.PageSize; pagingEntity.StartRowIndex = query.PagingInfo.PageIndex * query.PagingInfo.PageSize; CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetInvoiceMasterByQuery"); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder( dataCommand.CommandText, dataCommand, pagingEntity, "M.DocNo DESC")) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "M.CompanyCode" , DbType.AnsiStringFixedLength , "@CompanyCode" , QueryConditionOperatorType.Equal , query.CompanyCode); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "M.DocNo" , DbType.Int32 , "@DocNo" , QueryConditionOperatorType.Equal , query.DocNo); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "M.VendorSysNo" , DbType.Int32 , "@VendorSysNo" , QueryConditionOperatorType.Equal , query.VendorSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "M.InDate" , DbType.DateTime , "@CreateDateFrom" , QueryConditionOperatorType.MoreThanOrEqual , query.CreateDateFrom); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "M.InDate" , DbType.DateTime , "@CreateDateTo" , QueryConditionOperatorType.LessThanOrEqual , query.CreateDateTo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "M.ConfirmDate" , DbType.DateTime , "@ConfirmDateFrom" , QueryConditionOperatorType.MoreThanOrEqual , query.ConfirmDateFrom); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "M.ConfirmDate" , DbType.DateTime , "@ConfirmDateTo" , QueryConditionOperatorType.LessThanOrEqual , query.ConfirmDateTo); if (!string.IsNullOrEmpty(query.POList)) { sqlBuilder.ConditionConstructor.AddCustomCondition( QueryConditionRelationType.AND , string.Format(@"Exists(SELECT TOP 1 1 FROM [OverseaInvoiceReceiptManagement].[dbo].[APInvoice_PO_Item] P WITH(NOLOCK) WHERE P.PoNo IN({0}) AND P.DocNo = M.DocNo )" , query.POList.Replace(".", ","))); } if (!string.IsNullOrEmpty(query.APList)) { sqlBuilder.ConditionConstructor.AddCustomCondition( QueryConditionRelationType.AND , string.Format(@"Exists(SELECT TOP 1 1 FROM [OverseaInvoiceReceiptManagement].[dbo].[APInvoice_Invo_Item] A WITH(NOLOCK) WHERE A.InvoiceNo IN({0}) AND A.DocNo = M.DocNo)" , query.APList.Replace(".", ","))); } if (query.HasDiff.HasValue) { if (query.HasDiff.Value) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "M.DiffTaxAmt" , DbType.Decimal , "@DiffTaxAmt" , QueryConditionOperatorType.NotEqual , 0m); } else { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "M.DiffTaxAmt" , DbType.Decimal , "@DiffTaxAmt" , QueryConditionOperatorType.Equal , 0m); } } sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "M.Status" , DbType.Int32 , "@Status" , QueryConditionOperatorType.Equal , query.Status); if (query.ComeFrom.HasValue) { if (query.ComeFrom.Value == 0) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "M.VendorPortalSysNo" , DbType.Int32 , "@VendorPortalSysNo" , QueryConditionOperatorType.IsNull , query.ComeFrom); } else { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "M.VendorPortalSysNo" , DbType.Int32 , "@VendorPortalSysNo" , QueryConditionOperatorType.IsNotNull , query.ComeFrom); } } if (query.PaySettleCompany.HasValue) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "V.PaySettleCompany" , DbType.Int32 , "@PaySettleCompany" , QueryConditionOperatorType.Equal , query.PaySettleCompany.Value); } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); EnumColumnList enumList = new EnumColumnList(); enumList.Add("Status", typeof(APInvoiceMasterStatus)); enumList.Add("SapImportedStatus", typeof(SapImportedStatus)); enumList.Add("DiffTaxTreatmentType", typeof(InvoiceDiffType)); DataTable dt = dataCommand.ExecuteDataTable(enumList); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } }
/// <summary> /// 查询商品 /// </summary> /// <returns></returns> public virtual DataTable QueryProduct(ProductQueryFilter filter, out int totalCount) { CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProduct"); using (var sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, ToPagingInfo(filter.PagingInfo), "Product.SysNo Desc")) { #region AddParameter //商品ID sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Product.ProductID", DbType.String, "@ProductID", QueryConditionOperatorType.LeftLike, StringUtility.IsEmpty(filter.ProductID) ? filter.ProductID : filter.ProductID.Trim() ); //商品系统编号 sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Product.SysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, filter.ProductSysNo ); //商品名称 sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Product.ProductTitle", DbType.String, "@ProductName", QueryConditionOperatorType.Like, filter.ProductName ); if (filter.VendorSysNo.HasValue) { // 供应商系统编号 // string template = string.Format(@"SELECT DISTINCT PO_ITM.ProductSysNo // FROM [IPP3].[dbo].[PO_Item] PO_ITM WITH(NOLOCK) // INNER JOIN [IPP3].[dbo].[PO_Master] PO_MST WITH(NOLOCK) // ON PO_MST.SysNo = PO_ITM.POSysNo WHERE PO_MST.VendorSysNo={0}", filter.VendorSysNo.Value); // sb.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "Product.SysNo", // QueryConditionOperatorType.In, template); sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Product.MerchantSysNo", DbType.Int32, "@MerchantSysNo", QueryConditionOperatorType.Equal, filter.VendorSysNo ); } //PM操作人员 sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Product.PMUserSysNo", DbType.Int32, "@PMUserSysNo", QueryConditionOperatorType.Equal, filter.PMUserSysNo ); //商品类型 if (filter.ProductType.HasValue) { //需要转变映射关系 sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Product.ProductType", DbType.Int32, "@ProductType", QueryConditionOperatorType.Equal, (int)filter.ProductType.Value ); } //商品状态 if (filter.ProductStatus.HasValue) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Product.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, (int)filter.ProductStatus.Value ); } //商品状态不为作废状态 if (filter.IsNotAbandon.HasValue && filter.IsNotAbandon.Value) { sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Product.Status", DbType.Int32, "@IsNotAbandon", QueryConditionOperatorType.NotEqual, ProductStatus.Abandon ); } //品牌 sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Product.BrandSysNo", DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal, filter.BrandSysNo ); //创建时间 sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Product.[CreateTime]", DbType.DateTime, "@CreateDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.CreateDateFrom ); sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Product.[CreateTime]", DbType.DateTime, "@CreateDateTo", QueryConditionOperatorType.LessThanOrEqual, filter.CreateDateTo ); //第三级分类 sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Product.C3SysNo", DbType.Int32, "@C3SysNo", QueryConditionOperatorType.Equal, filter.C3SysNo ); sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "C3.C2SysNo", DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal, filter.C2SysNo ); sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "C2.C1SysNo", DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal, filter.C1SysNo ); //仓库查询后续提供 //sb.ConditionConstructor.AddCondition( // QueryConditionRelationType.AND, // "Inventory.WareHouseSysNumber", // DbType.Int32, // "@StockSysNo", // QueryConditionOperatorType.Equal, // filter.StockSysNo // ); //频道? //是否代销 sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Product.IsConsign", DbType.String, "@IsConsign", QueryConditionOperatorType.Equal, filter.IsConsign ); //库存数量 if (filter.OnlineQty.HasValue) { QueryConditionOperatorType operatorType = QueryConditionOperatorType.Equal; switch (filter.OnlineCondition.ToLower()) { case "greater": operatorType = QueryConditionOperatorType.MoreThan; break; case "less": operatorType = QueryConditionOperatorType.LessThan; break; default: operatorType = QueryConditionOperatorType.Equal; break; } sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Inventory.OnlineQty", DbType.Int32, "@OnlineQty", operatorType, filter.OnlineQty ); } sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "EX.CompanyProduct", DbType.Int32, "@CompanyProduct", QueryConditionOperatorType.Equal, filter.AZCustomer ); sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Product.MerchantSysNo", DbType.Int32, "@MerchantSysNo", QueryConditionOperatorType.Equal, filter.MerchantSysNo ); //公司编码 sb.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "Product.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode ); if (filter.ProductIds != null && filter.ProductIds.Count > 0) { sb.ConditionConstructor.AddInCondition <string>( QueryConditionRelationType.AND, "Product.ProductID", DbType.String, filter.ProductIds ); } cmd.CommandText = sb.BuildQuerySql(); #endregion EnumColumnList columnConfig = new EnumColumnList(); columnConfig.Add("Status", typeof(ProductStatus)); columnConfig.Add("ProductType", typeof(ProductType)); columnConfig.Add("InventoryType", typeof(ProductInventoryType)); var dt = cmd.ExecuteDataTable(columnConfig); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(dt); } }
/// <summary> /// pm工作指标监控查询 /// </summary> /// <param name="queryFilter"></param> /// <param name="totalCount"></param> /// <returns></returns> public virtual DataTable QueryPMMonitoringPerformanceIndicators(PMMonitoringPerformanceIndicatorsQueryFilter queryFilter, out int totalCount) { DataTable dt = new DataTable(); if (queryFilter == null) { totalCount = 0; return(null); } var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPMMPIV3"); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = queryFilter.PagingInfo.SortBy, StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize, MaximumRows = queryFilter.PagingInfo.PageSize }; List <string> sort = new List <string>(); List <string> pms = new List <string>(); List <string> sourceData = new List <string>(); string sourceDataG = string.Empty; List <string> group = new List <string>(); List <string> where = new List <string>(); //init sort.Add("PStocks.Category1SysNo"); sort.Add("PStocks.Category2SysNo"); group.Add("Category1SysNo"); group.Add("Category2SysNo"); if (!string.IsNullOrEmpty(queryFilter.SelectedCategory1)) { where.Add("PStocks.Category1SysNo=" + queryFilter.SelectedCategory1); pms.Add("pp1.Category1SysNo= " + queryFilter.SelectedCategory1); } if (!string.IsNullOrEmpty(queryFilter.SelectedCategory2)) { where.Add("PStocks.Category2SysNo=" + queryFilter.SelectedCategory2); pms.Add("pp1.Category2SysNo= " + queryFilter.SelectedCategory2); } else { pms.Add("pp1.Category2SysNo=PStocks.Category2SysNo"); } if (!string.IsNullOrEmpty(queryFilter.SelectedPMSysNo)) { where.Add("PStocks.PMUserSysNo=" + queryFilter.SelectedPMSysNo); pms.Add("pp1.PMUserSysNo= " + queryFilter.SelectedPMSysNo); sort.Add("PStocks.PMUserSysNo"); group.Add("PStocks.PMUserSysNo"); } if (queryFilter.StockSysNo != null && queryFilter.StockSysNo != 0) { //[Mark][Alan.X.Luo 硬编码] if (queryFilter.StockSysNo == 51) { //[Mark][Alan.X.Luo 硬编码] pms.Add("(pp1.StockSysNo=51 or pp1.StockSysNo=59)"); //[Mark][Alan.X.Luo 硬编码] sourceDataG = ",CASE WHEN pp.StockSysNo=59 THEN 51 ELSE pp.StockSysNo End"; sourceData.Add("CASE WHEN SUM(isnull(IsOutOfStock,0))=2 THEN 1 ELSE 0 END as IsOutOfStock"); //[Mark][Alan.X.Luo 硬编码] sourceData.Add("CASE WHEN pp.StockSysNo=59 THEN 51 ELSE pp.StockSysNo End AS StockSysNo"); //Losing sourceData.Add("CASE WHEN SUM(isnull(IsOutOfStock,0))=2 THEN sum(isnull(Losing,0)) ELSE 0 END AS Losing"); } else { pms.Add("pp1.StockSysNo=" + queryFilter.StockSysNo); sourceDataG = ",pp.StockSysNo"; sourceData.Add("SUM(isnull(IsOutOfStock,0)) as IsOutOfStock"); sourceData.Add("pp.StockSysNo"); sourceData.Add("sum(isnull(Losing,0)) AS Losing"); } where.Add("PStocks.StockSysNo=" + queryFilter.StockSysNo); group.Add("StockSysNo"); sort.Add("PStocks.StockSysNo"); } else { sourceData.Add("CASE WHEN SUM(isnull(IsOutOfStock,0))=6 THEN 1 ELSE 0 END AS IsOutOfStock"); sourceData.Add("CASE WHEN SUM(isnull(IsOutOfStock,0))=6 THEN sum(isnull(Losing,0)) ELSE 0 END AS Losing"); } if (!string.IsNullOrEmpty(queryFilter.AVGSaledQty)) { string Operators = string.Empty; if (queryFilter.AVGSaledQtyCondition == ">=") { Operators = ">="; } else if (queryFilter.AVGSaledQtyCondition == "=") { Operators = "="; } else if (queryFilter.AVGSaledQtyCondition == "<=") { Operators = "<="; } if (Operators == "<=" || queryFilter.AVGSaledQty == "0") { where.Add("(PStocks.AVGDS" + Operators + queryFilter.AVGSaledQty + " OR PStocks.AVGDS is null)"); //pms.Add("(pa1.AVGDailySales" + Operators + queryEntity.Condition.AVGSaledQty + " OR pa1.AVGDailySales is null)"); } else { where.Add("PStocks.AVGDS " + Operators + queryFilter.AVGSaledQty); //pms.Add("pa1.AVGDailySales" + Operators + queryEntity.Condition.AVGSaledQty); } } string sortStr = string.Join(",", sort.ToArray()); string pmsStr = string.Join(" And ", pms.ToArray()); string sourceDataStr = string.Join(",", sourceData.ToArray()); string groupStr = string.Join(",", group.ToArray()); string whereStr = string.Join(" And ", where.ToArray()); dataCommand.ReplaceParameterValue("#SortColumnName#", sortStr); dataCommand.ReplaceParameterValue("#PMCondition#", "Where " + pmsStr); dataCommand.ReplaceParameterValue("#SourceData#", sourceDataStr); dataCommand.ReplaceParameterValue("#Group#", "Group by " + groupStr); dataCommand.ReplaceParameterValue("#StrWhere#", where.Count == 0 ? "" : "Where " + whereStr); dataCommand.ReplaceParameterValue("#SourceDataG#", sourceDataG); dataCommand.ReplaceParameterValue("#AvailableSalesDays#", queryFilter.AvailableSaledDays); using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "c1.C1Name")) { dataCommand.CommandText = sqlBuilder.BuildQuerySql(); dt = dataCommand.ExecuteDataTable(); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); } dt.Columns.Add("LSDRate", typeof(System.String)); dt.Columns.Add("ShortageRate", typeof(System.String)); CalcPMData(dt); return(dt); }
public DataTable QueryModifiedVirtualRequest(VirtualRequestQueryFilter queryCriteria, out int totalCount) { DataTable dt = new DataTable(); if (queryCriteria == null) { totalCount = 0; return(null); } PagingInfoEntity pagingInfo = new PagingInfoEntity() { StartRowIndex = 0, MaximumRows = int.MaxValue }; CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryVirtualRequest"); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, null, "a.CreateTime DESC")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryCriteria.CompanyCode); if (queryCriteria.RequestStatus.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, (int)queryCriteria.RequestStatus); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductSysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryCriteria.ProductSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryCriteria.SysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryCriteria.StockSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateUserSysNo", DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, queryCriteria.CreateUserSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.VirtualType", DbType.Int32, "@VirtualType", QueryConditionOperatorType.Equal, queryCriteria.VirtualRequestType); if (queryCriteria.StartDate.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime", DbType.DateTime, "@DateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.StartDate); } if (queryCriteria.EndDate.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime", DbType.DateTime, "@DateTo", QueryConditionOperatorType.LessThan, queryCriteria.EndDate.Value.AddDays(1)); } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); dataCommand.CommandText = dataCommand.CommandText.Replace("#BY#", "CreateDate DESC"); dt = dataCommand.ExecuteDataTable(new EnumColumnList() { { "RequestStatus", typeof(ECCentral.BizEntity.Inventory.VirtualRequestStatus) } }); if (null != dt && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { dr["VirtualTypeString"] = CodeNamePairManager.GetName("Inventory", "VirtualRequestType", dr["VirtualType"].ToString()); } } totalCount = dt.Rows.Count; } return(dt); }
public DataSet QueryTrackingInfo(TrackingInfoQueryFilter filter, out int totalCount) { DataSet result = null; PagingInfoEntity pagingInfo = CreatePagingInfo(filter.PagingInfo); CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryTrackingInfo"); using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingInfo, "Temp.SysNo desc")) { if (!string.IsNullOrEmpty(filter.OrderSysNo)) { var orderSysNoList = filter.OrderSysNo.Split('.').ToList() .ConvertAll(x => Convert.ToInt32(x.Trim())); builder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "Temp.OrderSysNo", DbType.Int32, orderSysNoList); } var statusList = new List <string>(); //业务跟进 if (filter.HasStatusFollow) { statusList.Add("A"); } //提交报损 if (filter.HasStatusSubmit) { statusList.Add("S"); } //核销完毕 if (filter.HasStatusConfirm) { statusList.Add("C"); } //部分选择时再添加该条件 if (statusList.Count > 0 && statusList.Count < 3) { builder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "Temp.Status", DbType.AnsiStringFixedLength, statusList); } builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.OrderType", DbType.Int32, "@OrderType", QueryConditionOperatorType.Equal, filter.OrderType); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.IncomeStyle", DbType.Int32, "@IncomeStyle", QueryConditionOperatorType.Equal, filter.IncomeStyle); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.PayTypeSysNo", DbType.Int32, "@PayTypeSysNo", QueryConditionOperatorType.Equal, filter.PayTypeSysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.OutDate", DbType.DateTime, "@OutFromDate", QueryConditionOperatorType.MoreThanOrEqual, filter.OutFromDate); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.OutDate", DbType.DateTime, "@OutToDate", QueryConditionOperatorType.LessThanOrEqual, filter.OutToDate); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.ShipTypeSysNo", DbType.Int32, "@ShipTypeSysNo", QueryConditionOperatorType.Equal, filter.ShipTypeSysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.LossType", DbType.Int32, "@LossType", QueryConditionOperatorType.Equal, filter.LossType); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.ResponsibleUserName", DbType.String, "@ResponsibleUserName", QueryConditionOperatorType.Equal, filter.ResponsibleUserName); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.InType", DbType.Int32, "@InType", QueryConditionOperatorType.Equal, filter.InType); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Temp.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); cmd.AddOutParameter("@OrderAmt", DbType.Double, 12); cmd.AddOutParameter("@PrepayAmt", DbType.Double, 12); cmd.AddOutParameter("@GiftCardPayAmt", DbType.Double, 12); cmd.AddOutParameter("@IncomeAmt", DbType.Double, 12); cmd.AddOutParameter("@UnpayedAmt", DbType.Double, 12); cmd.CommandText = builder.BuildQuerySql(); result = ExecuteDataCommandForTrackingInfo(cmd, out totalCount); } return(result); }
/// <summary> /// 查询虚库申请单 /// </summary> /// <returns></returns> public DataTable QueryVirtualRequest(VirtualRequestQueryFilter queryFilter, out int totalCount) { if (queryFilter == null) { totalCount = 0; return(null); } DataTable dt = new DataTable(); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = queryFilter.PagingInfo.SortBy, StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize, MaximumRows = queryFilter.PagingInfo.PageSize }; CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryVirtualRequest"); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "a.SysNo DESC")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode); if (queryFilter.RequestStatus.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, (int)queryFilter.RequestStatus); } if (queryFilter.ProductSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductSysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo.Value); } if (queryFilter.SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryFilter.SysNo.Value); } if (queryFilter.StockSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo.Value); } if (queryFilter.CreateUserSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateUserSysNo", DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, queryFilter.CreateUserSysNo.Value); } if (queryFilter.VirtualRequestType.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.VirtualType", DbType.Int32, "@VirtualType", QueryConditionOperatorType.Equal, (int)queryFilter.VirtualRequestType); } if (queryFilter.StartDate.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime", DbType.DateTime, "@DateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.StartDate.Value); } if (queryFilter.EndDate.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime", DbType.DateTime, "@DateTo", QueryConditionOperatorType.LessThan, queryFilter.EndDate.Value.AddDays(1)); } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); EnumColumnList list = new EnumColumnList(); list.Add("RequestStatus", typeof(VirtualRequestStatus)); dt = dataCommand.ExecuteDataTable(list); if (null != dt && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { dr["VirtualTypeString"] = CodeNamePairManager.GetName("Inventory", "VirtualRequestType", dr["VirtualType"].ToString()); } } totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); } return(dt); }
/// <summary> /// 产品评论查询 /// </summary> /// <param name="filter">The filter.</param> /// <param name="totalCount">The total count.</param> /// <returns></returns> public static List <ProductReviewQueryBasicInfo> QueryProductReviewBasicInfoList(ProductReviewQueryFilter filter, out int totalCount) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.SortField = filter.SortFields; pagingEntity.MaximumRows = filter.PageSize; pagingEntity.StartRowIndex = filter.PageIndex * filter.PageSize; CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("ProductReview_QueryProductReviewDetail"); using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingEntity, string.IsNullOrEmpty(pagingEntity.SortField) ? pagingEntity.SortField : "A.SysNo DESC")) { if (filter.SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.SysNo" , DbType.Int32 , "@SysNo" , QueryConditionOperatorType.Equal , filter.SysNo); } if (filter.CustomerCategory.HasValue) { //顾客类型 sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "D.CompanyCustomer" , DbType.Int32 , "@CustomerCategory" , QueryConditionOperatorType.Equal , filter.CustomerCategory); } if (!string.IsNullOrEmpty(filter.Title)) { dataCommand.AddInputParameter("@Title", DbType.String, filter.Title); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.Title" , DbType.String , "@Title", QueryConditionOperatorType.Like , filter.Title); } if (filter.CustomerSysNo.HasValue) { //顾客 sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.CustomerSysNo" , DbType.Int32, "@CustomerSysNo" , QueryConditionOperatorType.Equal , filter.CustomerSysNo); } if (!string.IsNullOrEmpty(filter.Operation) && !string.IsNullOrEmpty(filter.Score) && filter.Operation != "0" && filter.Score != "0") { sqlBuilder.ConditionConstructor.AddCustomCondition( QueryConditionRelationType.AND , "A.Score" + filter.Operation + filter.Score); } if (filter.VendorType.HasValue) { if (filter.VendorType == 1)//商家ID { sqlBuilder.ConditionConstructor.AddCustomCondition( QueryConditionRelationType.AND , "VD.VendorType=0");//中蛋特有 } else { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "VD.SysNo" , DbType.Int32 , "@VendorType" , QueryConditionOperatorType.Equal , filter.VendorType); } } if (filter.ProductGroupNo != 0)//商品组ID { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "PC.ProductGroupSysno" , DbType.Int32 , "@GroupID" , QueryConditionOperatorType.Equal , filter.ProductGroupNo); } //是否商品组 if (filter.IsByGroup) { if (filter.ProductSysNo > 0) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "B.SysNo in(SELECT ProductSysNo FROM [OverseaContentManagement].[dbo].[V_CM_AllProductInfo] WHERE [ProductGroupSysno] IN (SELECT [ProductGroupSysno] FROM [OverseaContentManagement].[dbo].[V_CM_AllProductInfo] WHERE ProductID='" + filter.ProductID + "' AND ProductSysNo=" + filter.ProductSysNo + "))"); } } else { if (!string.IsNullOrEmpty(filter.ProductID)) { dataCommand.AddInputParameter("@ProductID", DbType.String, filter.ProductID); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "B.ProductID" , DbType.String , "@ProductID" , QueryConditionOperatorType.Like , filter.ProductID); } if (!string.IsNullOrEmpty(filter.ProductName)) { dataCommand.AddInputParameter("@ProductName", DbType.String, filter.ProductName); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "B.ProductName", DbType.String, "@ProductName", QueryConditionOperatorType.Like, filter.ProductName); } } //是否有用候选 if (filter.MostUseFulCandidate != null) { if (filter.MostUseFulCandidate == 0) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.MostUseFul" , DbType.Int32 , "@MostUseFulCandidate" , QueryConditionOperatorType.NotEqual , 1); } else if (filter.MostUseFulCandidate == 1) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.MostUseFul" , DbType.Int32 , "@MostUseFulCandidate" , QueryConditionOperatorType.Equal , filter.MostUseFulCandidate); } } //是否最有用 if (filter.MostUseFul != null) { if (filter.MostUseFul == 0) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.MostUseFul", DbType.Int32 , "@MostUseFul" , QueryConditionOperatorType.NotEqual , 2); } else if (filter.MostUseFul == 1) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.MostUseFul" , DbType.Int32 , "@MostUseFul" , QueryConditionOperatorType.Equal , 2); } } //评论类型 if (filter.ReviewType.HasValue) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.ReviewType" , DbType.Int32, "@MostUseFul" , QueryConditionOperatorType.Equal , (int)filter.ReviewType.Value); } if (filter.UsefulCount.HasValue) { //用户有用评价数 sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.UsefulCount" , DbType.Int32 , "@UsefulCount" , QueryConditionOperatorType.Equal , filter.UsefulCount); } //评论状态 if (!string.IsNullOrEmpty(filter.Status)) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.Status" , DbType.String, "@Status" , QueryConditionOperatorType.Equal , filter.Status.Substring(0, 1)); if (filter.Status.Length > 1) { if (filter.Status.Substring(2, 1) == "1") { sqlBuilder.ConditionConstructor.AddCustomCondition( QueryConditionRelationType.AND , "A.EditUser='******'"); } else if (filter.Status.Substring(2, 1) == "2") { sqlBuilder.ConditionConstructor.AddCustomCondition( QueryConditionRelationType.AND , "A.EditUser<>'System'"); } } } if (filter.SellerSysNo.HasValue) { dataCommand.AddInputParameter("@SellerSysNo", DbType.String, filter.SellerSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "B.MerchantSysNo", DbType.String, "@SellerSysNo", QueryConditionOperatorType.Equal, filter.SellerSysNo); } if (filter.IsTop.HasValue) { //置顶 sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.IsTop" , DbType.String , "@IsTop" , QueryConditionOperatorType.Equal , filter.IsTop); } if (filter.IsBottom.HasValue) { //置底 sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.IsBottom" , DbType.String, "@IsBottom" , QueryConditionOperatorType.Equal , filter.IsBottom); } if (filter.IsDigest.HasValue) { //是否精华 sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.IsDigest" , DbType.String , "@IsDigest" , QueryConditionOperatorType.Equal , filter.IsDigest); } //sqlBuilder.ConditionConstructor.AddBetweenCondition( // QueryConditionRelationType.AND // , "A.InDate" // , DbType.DateTime // , "@InDate" // , QueryConditionOperatorType.MoreThanOrEqual // , QueryConditionOperatorType // .LessThanOrEqual // , filter.InDateFrom // , filter.InDateTo); if (filter.InDateFrom != null) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "P.InDate", DbType.DateTime, "@InDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.InDateFrom ); } if (filter.InDateTo != null) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "P.InDate", DbType.DateTime, "@InDateTo", QueryConditionOperatorType.LessThan, filter.InDateTo ); } //sqlBuilder.ConditionConstructor.AddBetweenCondition( // QueryConditionRelationType.AND // , "A.EditDate" // , DbType.DateTime // , "@EditDate" // , QueryConditionOperatorType.MoreThanOrEqual // , QueryConditionOperatorType.LessThanOrEqual // , filter.EditDateFrom, filter.EditDateTo); if (filter.EditDateFrom != null) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "P.EditDate", DbType.DateTime, "@EditDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.InDateFrom ); } if (filter.EditDateTo != null) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "P.InDate", DbType.DateTime, "@EditDateTo", QueryConditionOperatorType.LessThan, filter.EditDateTo ); } if (!string.IsNullOrEmpty(filter.EditUser)) { //更新人 dataCommand.AddInputParameter("@EditUser", DbType.String, filter.EditUser); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.EditUser" , DbType.String, "@EditUser" , QueryConditionOperatorType.Like , filter.EditUser); } if (filter.PMUserSysNo.HasValue) { //PM sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "B.PMUserSysNo" , DbType.Int32 , "@PMUserSysNo" , QueryConditionOperatorType.Equal , filter.PMUserSysNo); } if (filter.ProductStatus.HasValue) { //商品状态 sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "B.Status" , DbType.Int32, "@ProductStatus" , QueryConditionOperatorType.Equal , filter.ProductStatus); } //商品类别 if (filter.Category3SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "B.C3SysNo" , DbType.Int32 , "@C3SysNo" , QueryConditionOperatorType.Equal , filter.Category3SysNo); } else { string c3 = string.Empty; if (filter.Category1SysNo.HasValue) { c3 += " AND Category1Sysno = " + filter.Category1SysNo; } if (filter.Category2SysNo.HasValue) { c3 += " AND Category2Sysno = " + filter.Category2SysNo; } if (!string.IsNullOrEmpty(c3)) { sqlBuilder.ConditionConstructor.AddCustomCondition( QueryConditionRelationType.AND , "B.C3SysNo IN (SELECT Category3Sysno FROM OverseaContentManagement.dbo.V_CM_CategoryInfo WHERE 1=1 AND CompanyCode =" + filter.CompanyCode + c3 + ")"); } } //CS处理状态 //if (filter.ComplainStatus == null) // sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "CM.Status IS NOT NULL"); //else sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CM.Status", DbType.Int32, "@CSProcessStatus", QueryConditionOperatorType.Equal, filter.ComplainStatus); //首页蛋友热评 if (filter.IsIndexPageHotComment != null) { if (filter.IsIndexPageHotComment == CommonYesOrNo.No) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.SysNo NOT IN (SELECT ProductReviewSysno FROM OverseaECommerceManagement.dbo.ProductReview_Homepage with (nolock) WHERE Type = 'H')"); } else if (filter.IsIndexPageHotComment == CommonYesOrNo.Yes) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.SysNo IN (SELECT ProductReviewSysno FROM OverseaECommerceManagement.dbo.ProductReview_Homepage with (nolock) WHERE Type = 'H')"); } } //首页服务热线 if (filter.IsIndexPageServiceHotComment != null) { if (filter.IsIndexPageServiceHotComment == CommonYesOrNo.No) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.SysNo NOT IN (SELECT ProductReviewSysno FROM OverseaECommerceManagement.dbo.ProductReview_Homepage with (nolock) WHERE Type = 'S')"); } else if (filter.IsIndexPageServiceHotComment == CommonYesOrNo.Yes) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "A.SysNo IN (SELECT ProductReviewSysno FROM OverseaECommerceManagement.dbo.ProductReview_Homepage with (nolock) WHERE Type = 'S')"); } } if (!string.IsNullOrEmpty(filter.CompanyCode)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "A.CompanyCode" , DbType.String , "@CompanyCode" , QueryConditionOperatorType.Equal , filter.CompanyCode); } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); //EnumColumnList enumList = new EnumColumnList(); //enumList.Add("IsTop", typeof(CommonYesOrNo)); //enumList.Add("IsBottom", typeof(CommonYesOrNo)); //enumList.Add("MostUseFulCandidate", typeof(CommonYesOrNo)); //enumList.Add("MostUseFul", typeof(CommonYesOrNo)); //enumList.Add("IsDigest", typeof(CommonYesOrNo)); //enumList.Add("ComplainStatus", typeof(ReviewProcessStatus)); //CodeNamePairColumnList pairList = new CodeNamePairColumnList(); //pairList.Add("Status", "MKT", "ReplyStatus");//评论状态 //DataTable dataTable = dataCommand.ExecuteDataTable(enumList, pairList); //List<ProductReviewQueryBasicInfo> list = new List<ProductReviewQueryBasicInfo>(); //foreach (DataRow row in dataTable.Rows) //{ // list.Add(DataMapper.GetEntity<ProductReviewQueryBasicInfo>(row)); //} List <ProductReviewQueryBasicInfo> list = dataCommand.ExecuteEntityList <ProductReviewQueryBasicInfo>(); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(list); } }
public virtual DataTable Query(CustomerPointsAddRequestFilter queryCriteria, out int totalCount) { totalCount = 0; CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("SelectCustomerPointAddRequest"); PagingInfoEntity pagingInfo = BuildPagingInfoEntity(queryCriteria.PageInfo); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder( cmd.CommandText, cmd, pagingInfo, "a.SysNo DESC")) { if (queryCriteria.SystemNumber.HasValue) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "a.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryCriteria.SystemNumber); } else { //创建时间开始 sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime", DbType.DateTime, "@CreateTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.CreateDateFrom); //创建时间结束 sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateTime", DbType.DateTime, "@CreateTimeTo", QueryConditionOperatorType.LessThan, queryCriteria.CreateDateTo); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "b.CustomerID", DbType.String, "@CustomerID", QueryConditionOperatorType.Equal, queryCriteria.CustomerID); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "a.SOSysNo", DbType.Int32, "@SOSysNo", QueryConditionOperatorType.Equal, queryCriteria.SOSysNo); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "a.NewEggAccount", DbType.String, "@NewEggAccount", QueryConditionOperatorType.Equal, queryCriteria.NeweggAccountDesc); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "a.OwnByDepartment", DbType.String, "@OwnByDepartment", QueryConditionOperatorType.Equal, queryCriteria.OwnByDepartmentDesc); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "a.memo", DbType.String, "@OwnByReason", QueryConditionOperatorType.Equal, queryCriteria.OwnByReasonDesc); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "a.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, queryCriteria.Status); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryCriteria.CompanyCode); } cmd.CommandText = sqlBuilder.BuildQuerySql(); DataTable dt = cmd.ExecuteDataTable("status", typeof(CustomerPointsAddRequestStatus)); totalCount = int.Parse(cmd.GetParameterValue("@TotalCount").ToString()); return(dt); } }
/// <summary> /// 产品评论查询回复 /// </summary> /// <param name="filter">The filter.</param> /// <param name="totalCount">The total count.</param> /// <returns></returns> public static DataTable QueryProductReviewReply(ProductReviewReplyQueryFilter filter, out int totalCount) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.SortField = filter.SortFields; pagingEntity.MaximumRows = filter.PageSize; pagingEntity.StartRowIndex = filter.PageIndex * filter.PageSize; CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("ProductReview_QueryProductReviewReplyList"); using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingEntity, "A.SysNo DESC")) { //顾客类型 sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "D.CompanyCustomer" , DbType.Int32 , "@CustomerCategory" , QueryConditionOperatorType.Equal , filter.CustomerCategory); //回复关键字 sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "B.Title" , DbType.String , "@Content" , QueryConditionOperatorType.Like , filter.Content); //顾客 sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.CustomerSysNo" , DbType.Int32 , "@CustomerSysNo" , QueryConditionOperatorType.Equal , filter.CustomerSysNo); //评论状态 if (!string.IsNullOrEmpty(filter.Status)) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.Status", DbType.String, "@Status" , QueryConditionOperatorType.Equal , filter.Status.Substring(0, 1)); if (filter.Status.Length > 1) { if (filter.Status.Substring(2, 1) == "1") { sqlBuilder.ConditionConstructor.AddCustomCondition( QueryConditionRelationType.AND , "A.EditUser='******'"); } else if (filter.Status.Substring(2, 1) == "2") { sqlBuilder.ConditionConstructor.AddCustomCondition( QueryConditionRelationType.AND , "A.EditUser<>'System'"); } } } //评论回复类型 对应 ReplySource sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.Type" , DbType.String , "@Type" , QueryConditionOperatorType.Equal , filter.Type); //回复后跟随语 //sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.NeedAdditionalText", DbType.String, "@NeedAdditionalText", QueryConditionOperatorType.Equal, filter.NeedAdditionalText); //编号查询暂时不存在 //sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.ReviewSysNo", DbType.Int32, "@ReviewSysNo", QueryConditionOperatorType.Equal, filter.ReviewSysNo); sqlBuilder.ConditionConstructor.AddBetweenCondition( QueryConditionRelationType.AND , "A.InDate" , DbType.DateTime , "@InDate" , QueryConditionOperatorType.MoreThanOrEqual , QueryConditionOperatorType.LessThanOrEqual , filter.InDateFrom , filter.InDateTo); sqlBuilder.ConditionConstructor.AddBetweenCondition( QueryConditionRelationType.AND , "A.EditDate" , DbType.DateTime , "@EditDate" , QueryConditionOperatorType.MoreThanOrEqual , QueryConditionOperatorType.LessThanOrEqual , filter.EditDateFrom , filter.EditDateTo); //更新人 sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "A.EditUser" , DbType.String, "@EditUser" , QueryConditionOperatorType.Like , filter.EditUser); //商品状态 sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "C.Status", DbType.Int32 , "@ProductStatus" , QueryConditionOperatorType.Equal , filter.ProductStatus); if (filter.VendorType == 1)//商家ID { sqlBuilder.ConditionConstructor.AddCustomCondition( QueryConditionRelationType.AND , "VD.VendorType=0");//中蛋特有 } else { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "VD.SysNo" , DbType.Int32 , "@VendorType" , QueryConditionOperatorType.Equal , filter.VendorType); } if (filter.ProductGroupNo != 0)//商品组ID { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "PC.ProductGroupSysno" , DbType.Int32, "@GroupID" , QueryConditionOperatorType.Equal , filter.ProductGroupNo); } //是否商品组 if (filter.IsByGroup) { if (filter.ProductSysNo > 0) { sqlBuilder.ConditionConstructor.AddCustomCondition( QueryConditionRelationType.AND , "C.SysNo in(SELECT ProductSysNo FROM [OverseaContentManagement].[dbo].[V_CM_AllProductInfo] WHERE [ProductGroupSysno] IN (SELECT [ProductGroupSysno] FROM [OverseaContentManagement].[dbo].[V_CM_AllProductInfo] WHERE ProductID='" + filter.ProductID + "' AND ProductSysNo=" + filter.ProductSysNo + "))"); } } else { if (!string.IsNullOrEmpty(filter.ProductID)) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND , "C.ProductID", DbType.String, "@ProductID" , QueryConditionOperatorType.Equal , filter.ProductID); } } //PM sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "PC.PMUserSysNo", DbType.Int32 , "@PMUserSysNo" , QueryConditionOperatorType.Equal , filter.PMUserSysNo); //商品类别 if (filter.Category3SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "C.C3SysNo" , DbType.Int32 , "@C3SysNo" , QueryConditionOperatorType.Equal , filter.Category3SysNo); } else { string c3 = string.Empty; if (filter.Category1SysNo.HasValue) { c3 += " AND Category1Sysno = " + filter.Category1SysNo; } if (filter.Category2SysNo.HasValue) { c3 += " AND Category2Sysno = " + filter.Category2SysNo; } if (!string.IsNullOrEmpty(c3)) { sqlBuilder.ConditionConstructor.AddCustomCondition( QueryConditionRelationType.AND , "C.C3SysNo IN (SELECT Category3Sysno FROM OverseaContentManagement.dbo.V_CM_CategoryInfo WHERE 1=1" + c3 + ")"); } } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); CodeNamePairColumnList pairList = new CodeNamePairColumnList(); pairList.Add("Type", "MKT", "ReplySource"); //回复类型 pairList.Add("Status", "MKT", "ReplyStatus"); //评论状态 DataTable dataTable = dataCommand.ExecuteDataTable(pairList); List <ProductReviewQueryBasicInfo> list = new List <ProductReviewQueryBasicInfo>(); foreach (DataRow row in dataTable.Rows) { list.Add(DataMapper.GetEntity <ProductReviewQueryBasicInfo>(row)); } totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dataTable); } }
public System.Data.DataTable QueryCommission(QueryFilter.PO.CommissionQueryFilter queryFilter, out int totalCount, out decimal totalAmt) { DataTable dt = new DataTable(); CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("SearchCommission"); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = queryFilter.PageInfo.SortBy, StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize, MaximumRows = queryFilter.PageInfo.PageSize }; string whereSql = "WHERE 1=1"; if (queryFilter.SysNo.HasValue) { dataCommand.AddInputParameter("@SysNo", DbType.Int32, queryFilter.SysNo.Value); whereSql += " AND commMaster.SysNo = @SysNo"; } if (queryFilter.VendorSysNo.HasValue) { dataCommand.AddInputParameter("@VendorSysNo", DbType.Int32, queryFilter.VendorSysNo.Value); whereSql += " AND commMaster.MerchantSysNo = @VendorSysNo"; } //InDate if (queryFilter.InDateBegin.HasValue) { whereSql += " AND commMaster.InDate >= @InDateBegin"; dataCommand.AddInputParameter("@InDateBegin", DbType.DateTime, queryFilter.InDateBegin.Value); } if (queryFilter.InDateEnd.HasValue) { whereSql += " AND commMaster.InDate <= @InDateEnd"; dataCommand.AddInputParameter("@InDateEnd", DbType.DateTime, queryFilter.InDateEnd.Value.AddDays(1)); } //OutListDate出单时间 if (queryFilter.OutListDateBegin.HasValue) { whereSql += " AND commMaster.EndDate >= @OutListDateBegin"; dataCommand.AddInputParameter("@OutListDateBegin", DbType.DateTime, queryFilter.OutListDateBegin.Value); } if (queryFilter.OutListDateEnd.HasValue) { whereSql += " AND commMaster.EndDate <= @OutListDateEnd"; dataCommand.AddInputParameter("@OutListDateEnd", DbType.DateTime, queryFilter.OutListDateEnd.Value.AddDays(1)); } if (!string.IsNullOrEmpty(queryFilter.CompanyCode)) { whereSql += " AND commMaster.CompanyCode = @CompanyCode "; dataCommand.AddInputParameter("@CompanyCode", DbType.String, queryFilter.CompanyCode); } if (queryFilter.PageInfo != null) { dataCommand.AddInputParameter("@StartNumber", DbType.Int32, queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex); dataCommand.AddInputParameter("@EndNumber", DbType.Int32, queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex + queryFilter.PageInfo.PageSize); dataCommand.AddOutParameter("@TotalCount", DbType.Int32, 4); dataCommand.AddOutParameter("@TotalAmt", DbType.String, 10); } dataCommand.CommandText = dataCommand.CommandText.Replace("#StrWhere#", whereSql); EnumColumnList list = new EnumColumnList(); list.Add("Status", typeof(VendorCommissionMasterStatus)); dt = dataCommand.ExecuteDataTable(list); totalAmt = Convert.ToDecimal(dataCommand.GetParameterValue("@TotalAmt")); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); }
/// <summary> /// 查询商品附件 /// </summary> /// <returns></returns> public virtual DataTable QueryProductAttachment(ProductAttachmentQueryFilter queryCriteria, out int totalCount) { CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductAttachment"); var pagingInfo = new PagingInfoEntity { SortField = queryCriteria.PagingInfo.SortBy, StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize, MaximumRows = queryCriteria.PagingInfo.PageSize }; using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "P.ProductSysNo DESC")) { if (!String.IsNullOrEmpty(queryCriteria.ProductID)) { dataCommand.AddInputParameter("@ProductID", DbType.String, queryCriteria.ProductID); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.ProductID", DbType.String, "@ProductID", QueryConditionOperatorType.Equal, queryCriteria.ProductID); } if (!String.IsNullOrEmpty(queryCriteria.ProductName)) { dataCommand.AddInputParameter("@ProductName", DbType.String, queryCriteria.ProductName); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.ProductName", DbType.String, "@ProductName", QueryConditionOperatorType.Like, queryCriteria.ProductName); } if (!String.IsNullOrEmpty(queryCriteria.AttachmentID)) { dataCommand.AddInputParameter("@AttachmentID", DbType.String, queryCriteria.AttachmentID); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.AttachmentSysNo", DbType.String, "@AttachmentID", QueryConditionOperatorType.Like, queryCriteria.AttachmentID); } if (!String.IsNullOrEmpty(queryCriteria.AttachmentName)) { dataCommand.AddInputParameter("@AttachmentName", DbType.String, queryCriteria.AttachmentName); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.AttachmentName", DbType.String, "@AttachmentName", QueryConditionOperatorType.Like, queryCriteria.AttachmentName); } if (!String.IsNullOrEmpty(queryCriteria.EditUser)) { dataCommand.AddInputParameter("@EditUser", DbType.String, queryCriteria.EditUser); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.InUser", DbType.String, "@EditUser", QueryConditionOperatorType.Like, queryCriteria.EditUser); } //sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "P.InDate", DbType.DateTime, "@InDate", QueryConditionOperatorType.LessThanOrEqual, QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.InDateEnd, queryCriteria.InDateStart); if (queryCriteria.InDateStart != null) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "P.InDate", DbType.DateTime, "@InDateStart", QueryConditionOperatorType.MoreThanOrEqual, queryCriteria.InDateStart ); } if (queryCriteria.InDateEnd != null) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "P.InDate", DbType.DateTime, "@InDateEnd", QueryConditionOperatorType.LessThan, queryCriteria.InDateEnd ); } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); EnumColumnList enumList = new EnumColumnList(); enumList.Add("ProductStatus", typeof(ECCentral.BizEntity.IM.ProductStatus)); DataTable dt = dataCommand.ExecuteDataTable(enumList); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } }
private DataTable GetAggregateStyleProductDomainList(ProductDomainFilter filter, out int totalCount) { CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("ProductDomain_GetAggregateStyleProductDomainList"); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = filter.PagingInfo.SortBy, StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize, MaximumRows = filter.PagingInfo.PageSize }; using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder( command.CommandText, command, pagingInfo, "[CategorySysNo] DESC")) { builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "D.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "D.SysNo", DbType.Int32, "@ProductDomainSysNo", QueryConditionOperatorType.Equal, filter.ProductDomainSysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "D.ProductDomainName", DbType.String, "@ProductDomainName", QueryConditionOperatorType.Like, filter.ProductDomainName); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.BrandSysNo", DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal, filter.BrandSysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.C1SysNo", DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal, filter.Category1SysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.C2SysNo", DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal, filter.Category2SysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.PMSysNo", DbType.Int32, "@PMSysNo", QueryConditionOperatorType.Equal, filter.PMSysNo); builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "D.ProductDomainLeaderUserSysNo", DbType.Int32, "@ProductDomainLeaderUserSysNo", QueryConditionOperatorType.Equal, filter.ProductDomainLeaderUserSysNo); command.CommandText = builder.BuildQuerySql(); DataTable result = command.ExecuteDataTable(new EnumColumnList { { "Status", typeof(ValidStatus) } }); totalCount = (int)command.GetParameterValue("@TotalCount"); result.Columns.Add("BrandName", typeof(string)); //得到当前分页结果中相关的pm c2分组结果, 用该内容找到相应的品牌 var list = from e in result.Rows.Cast <DataRow>() select new { DataType = (int)e["DataType"], C2SysNo = (int)e["C2SysNo"], PMSysNo = (int)e["PMSysNo"] }; var xmlQuery = new XElement("Groups", from e in list where e.DataType == 2 select new XElement("Group", new XElement("PMSysNo", e.PMSysNo), new XElement("C2SysNo", e.C2SysNo))); var cmdQuery = DataCommandManager.GetDataCommand("ProductDomain_GetBrandsByPMAndC2SysNo"); cmdQuery.SetParameterValue("@xmlQuery", xmlQuery.ToString()); var ds = cmdQuery.ExecuteDataSet(); var brands = from e in ds.Tables[0].Rows.Cast <DataRow>() select new { PMSysNo = (int)e["PMSysNo"], C2SysNo = (int)e["C2SysNo"], BrandSysNo = (int)e["BrandSysNo"], BrandName = e["BrandName"].ToString() }; foreach (DataRow r in result.Rows) { r["BackupUserList"] = GetUserNameList(r["BackupUserList"].ToString(), filter.CompanyCode); if ((int)r["DataType"] == 2) { r["BrandName"] = (from e in brands where e.PMSysNo == (int)r["PMSysNo"] && e.C2SysNo == (int)r["C2SysNo"] select e.BrandName).Join(", "); } } return(result); } }
public DataSet Query(GroupBuyingLotteryQueryFilter filter, out int totalCount) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.SortField = filter.PagingInfo.SortBy; pagingEntity.MaximumRows = filter.PagingInfo.PageSize; pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize; var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetGroupBuyingLotteryList"); using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "M.SysNo DESC")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.[ProductGroupBuyingSysNo]", DbType.Int32, "@ProductGroupBuyingSysNo", QueryConditionOperatorType.Equal, filter.GroupBuyingSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.LotteryDate", DbType.DateTime, "@BeginDateFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.BeginDateFrom); //sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, // "M.LotteryDate", DbType.DateTime, "@BeginDateTo", // QueryConditionOperatorType.LessThan, // filter.BeginDateTo); //sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, // "M.LotteryDate", DbType.DateTime, "@EndDateFrom", // QueryConditionOperatorType.MoreThanOrEqual, // filter.EndDateFrom); if (filter.EndDateTo != null) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.LotteryDate", DbType.DateTime, "@EndDateTo", QueryConditionOperatorType.LessThan, filter.EndDateTo.Value.AddDays(1)); } object _rank; if (filter.RankType != null && EnumCodeMapper.TryGetCode(filter.RankType, out _rank)) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "C.Rank", DbType.Int32, "@Rank", QueryConditionOperatorType.Equal, _rank); } sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "M.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); cmd.CommandText = sqlBuilder.BuildQuerySql(); var ds = cmd.ExecuteDataSet(); cmd.ConvertEnumColumn(ds.Tables[0], new EnumColumnList { { "Rank", typeof(CustomerRank) } }); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(ds); } }
public System.Data.DataTable QuerySaleGift(SaleGiftQueryFilter filter, out int totalCount) { PagingInfoEntity pagingEntity = new PagingInfoEntity(); pagingEntity.SortField = filter.PageInfo.SortBy; pagingEntity.MaximumRows = filter.PageInfo.PageSize; pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize; var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("SaleGift_QueryGift"); using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC")) { //sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.WebChannelID", DbType.AnsiStringFixedLength, // "@WebChannelID", QueryConditionOperatorType.Equal, filter.ChannelID); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "B.BrandSysNo", DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal, filter.BrandSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "D.BrandSysNo", DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal, filter.BrandSysNo); sqlBuilder.ConditionConstructor.EndGroupCondition(); sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "B.C3SysNo", DbType.Int32, "@Category3SysNo", QueryConditionOperatorType.Equal, filter.Category3SysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "D.Category3SysNo", DbType.Int32, "@Category3SysNo", QueryConditionOperatorType.Equal, filter.Category3SysNo); sqlBuilder.ConditionConstructor.EndGroupCondition(); #region Jack.G.tang 2013-1-7 update Bug 95316 /*修改原因:新增Category1和Category2的查询条件 */ if (filter.Category1SysNo > 0) { sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "C3.Category1SysNo", DbType.Int32, "@Category1SysNo", QueryConditionOperatorType.Equal, filter.Category1SysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "Category3.Category1SysNo", DbType.Int32, "@Category1SysNo", QueryConditionOperatorType.Equal, filter.Category1SysNo); sqlBuilder.ConditionConstructor.EndGroupCondition(); } if (filter.Category2SysNo > 0) { sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "C3.Category2SysNo", DbType.Int32, "@Category2SysNo", QueryConditionOperatorType.Equal, filter.Category2SysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "Category3.Category2SysNo", DbType.Int32, "@Category2SysNo", QueryConditionOperatorType.Equal, filter.Category2SysNo); sqlBuilder.ConditionConstructor.EndGroupCondition(); } #endregion if (filter.MasterProductSysNo.HasValue) { ProductBasic pMaster = GetProductBasic(filter.MasterProductSysNo.Value); sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "B.ProductSysNo", DbType.Int32, "@MasterProductSysNo", QueryConditionOperatorType.Equal, filter.MasterProductSysNo); if (pMaster.C3SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "B.C3SysNo", DbType.Int32, "@MasterC3SysNo", QueryConditionOperatorType.Equal, pMaster.C3SysNo); } if (pMaster.BrandSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "B.BrandSysNo", DbType.Int32, "@MasterBrandSysNo", QueryConditionOperatorType.Equal, pMaster.BrandSysNo); } sqlBuilder.ConditionConstructor.EndGroupCondition(); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.ProductSysNo", DbType.Int32, "@GiftProductSysNo", QueryConditionOperatorType.Equal, filter.GiftProductSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.InUser", DbType.String, "@PMUser", QueryConditionOperatorType.Equal, filter.PMUser); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.SysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Status", DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, filter.Status); if (!string.IsNullOrEmpty(filter.PromotionName)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.PromotionName", DbType.String, "@PromotionName", QueryConditionOperatorType.Like, filter.PromotionName); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Type", DbType.AnsiStringFixedLength, "@Type", QueryConditionOperatorType.Equal, filter.Type); if (filter.ActivityDateFrom.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.BeginDate", DbType.DateTime, "@BeginDate", QueryConditionOperatorType.MoreThanOrEqual, filter.ActivityDateFrom); } if (filter.ActivityDateTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.EndDate", DbType.DateTime, "@EndDate", QueryConditionOperatorType.LessThan, filter.ActivityDateTo.Value); } if (filter.VendorSysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, filter.VendorSysNo); } cmd.CommandText = sqlBuilder.BuildQuerySql(); DataSet ds = cmd.ExecuteDataSet(); DataTable dt1 = ds.Tables[0]; DataTable dt2 = ds.Tables[1]; DataTable dt3 = ds.Tables[2]; if (dt1 != null && dt1.Rows.Count > 0) { foreach (DataRow row in dt1.Rows) { object status; EnumCodeMapper.TryGetEnum(row["Status"], typeof(SaleGiftStatus), out status); row["Status"] = status; if (status != null) { row["StatusName"] = ((SaleGiftStatus)status).ToDisplayText(); } object type; EnumCodeMapper.TryGetEnum(row["Type"], typeof(SaleGiftType), out type); row["Type"] = type; if (type != null) { row["TypeName"] = ((SaleGiftType)type).ToDisplayText(); } if (((SaleGiftType)type) == SaleGiftType.Full) { row["MasterProducts"] = "商品范围"; } else { if (dt2 != null && dt2.Rows.Count > 0) { string masterProducts = ""; foreach (DataRow row2 in dt2.Rows) { if (row2["SysNo"].ToString() == row["SysNo"].ToString()) { masterProducts += row2["ProductID"].ToString() + Environment.NewLine; } } row["MasterProducts"] = masterProducts; } } if (dt3 != null && dt3.Rows.Count > 0) { string giftProducts = ""; foreach (DataRow row3 in dt3.Rows) { if (row3["SysNo"].ToString() == row["SysNo"].ToString()) { giftProducts += row3["ProductID"].ToString() + Environment.NewLine; } } row["GiftProducts"] = giftProducts; } } } totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(dt1); } }
public System.Data.DataTable QueryVendorList(QueryFilter.PO.VendorQueryFilter queryFilter, out int totalCount) { if (queryFilter == null) { totalCount = -1; return(null); } CustomDataCommand dataCommand = null; if (queryFilter.RANKStatus == VendorRankStatus.AuditWaiting) { dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryVendorListByLeverRequest"); } else { dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryVendorList"); } PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = queryFilter.PageInfo.SortBy, StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize, MaximumRows = queryFilter.PageInfo.PageSize }; using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "V.SysNo desc")) { if (!string.IsNullOrEmpty(queryFilter.InvoiceType)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ex.InvoiceType", DbType.AnsiStringFixedLength, "@InvoiceType", QueryConditionOperatorType.Equal, queryFilter.InvoiceType); } if (!string.IsNullOrEmpty(queryFilter.StockType)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ex.StockType", DbType.AnsiStringFixedLength, "@StockType", QueryConditionOperatorType.Equal, queryFilter.StockType); } if (!string.IsNullOrEmpty(queryFilter.ShippingType)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ex.ShippingType", DbType.AnsiStringFixedLength, "@ShippingType", QueryConditionOperatorType.Equal, queryFilter.ShippingType); } if (queryFilter.IsConsign.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.IsConsign", DbType.Int32, "@IsConsign", QueryConditionOperatorType.Equal, queryFilter.IsConsign); } if (!string.IsNullOrEmpty(queryFilter.Account)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.Account", DbType.String, "@Account", QueryConditionOperatorType.Equal, queryFilter.Account); } if (queryFilter.VendorSysNo.HasValue) { //临时修改,支持在供应商编号输入VendorID查询, Ocean string SysNoList = queryFilter.VendorSysNo.Value.ToString(); if (queryFilter.VendorSysNo.Value < 1000000) { int newSysNo = queryFilter.VendorSysNo.Value + 1000000; SysNoList = SysNoList + ',' + newSysNo.ToString(); } sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "v.SysNo", QueryConditionOperatorType.In, SysNoList); //sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.SysNo", //DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo.Value); } if (!string.IsNullOrEmpty(queryFilter.VendorName)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.VendorName", DbType.String, "@VendorName", QueryConditionOperatorType.Like, queryFilter.VendorName); } if (!string.IsNullOrEmpty(queryFilter.Address)) { queryFilter.Address.Replace("'", "’"); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.Address", DbType.String, "@Address", QueryConditionOperatorType.LeftLike, queryFilter.Address.Trim()); } if (!string.IsNullOrEmpty(queryFilter.Contact)) { queryFilter.Contact.Replace("'", "’"); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.Contact", DbType.String, "@Contact", QueryConditionOperatorType.LeftLike, queryFilter.Contact.Trim()); } if (!string.IsNullOrEmpty(queryFilter.Phone)) { queryFilter.Phone.Replace("'", "’"); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.Phone", DbType.String, "@Phone", QueryConditionOperatorType.LeftLike, queryFilter.Phone.Trim()); } if (queryFilter.Status != null && queryFilter.Status.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, (int)queryFilter.Status); } if (!string.IsNullOrEmpty(queryFilter.RANK)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.RANK", DbType.String, "@RANK", QueryConditionOperatorType.Equal, queryFilter.RANK.Trim()); } //合作起止日期 if (queryFilter.ExpiredDateFrom.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.ExpiredDate", DbType.DateTime, "@ExpiredDateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.ExpiredDateFrom.Value); } if (queryFilter.ExpiredDateTo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.ExpiredDate", DbType.DateTime, "@ExpiredDateTo", QueryConditionOperatorType.LessThanOrEqual, queryFilter.ExpiredDateTo.Value); } if (queryFilter.RequestStatus.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, @"( SELECT top 1 f.Status FROM IPP3.dbo.Vendor_ModifyRequest f WITH(NOLOCK) WHERE f.VendorSysNo =V.sysno AND f.RequestType = 0 ORDER BY sysno DESC)" , DbType.Int32, "@RequestStatus", QueryConditionOperatorType.Equal, queryFilter.RequestStatus.Value); } if (queryFilter.VendorType.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.VendorType", System.Data.DbType.Int32, "@VendorType", QueryConditionOperatorType.Equal, queryFilter.VendorType); } if (queryFilter.PaySettleCompany.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.PaySettleCompany", System.Data.DbType.Int32, "@PaySettleCompany", QueryConditionOperatorType.Equal, queryFilter.PaySettleCompany.Value); } sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.CompanyCode", System.Data.DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode); if (queryFilter.EPortSysNo.HasValue && queryFilter.EPortSysNo > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "V.[ePortSysNo]", System.Data.DbType.Int32, "@EPortSysNo", QueryConditionOperatorType.Equal, queryFilter.EPortSysNo.Value); } #region [ //代理品牌 二级类 三级类 代理级别 ] if ((queryFilter.ManufacturerSysNo != null && queryFilter.ManufacturerSysNo.Trim() != "") || (queryFilter.AgentLevel != null && queryFilter.AgentLevel.Trim() != "") || (queryFilter.C1SysNo != null && queryFilter.C1SysNo.Trim() != "")) { string subCond = @"SELECT distinct vendorsysno FROM IPP3.dbo.Vendor_Manufacturer WITH(NOLOCK) "; string strWhere = string.Empty; //代理品牌 二级类 三级类 代理级别 if (!string.IsNullOrEmpty(queryFilter.ManufacturerSysNo)) { strWhere += "ManufacturerSysNo=" + queryFilter.ManufacturerSysNo.Trim(); } if (!string.IsNullOrEmpty(queryFilter.AgentLevel)) { if (strWhere != string.Empty) { strWhere += " AND "; } strWhere += "AgentLevel='" + queryFilter.AgentLevel.Trim() + "'"; } if (!string.IsNullOrEmpty(queryFilter.C2SysNo)) { if (strWhere != string.Empty) { strWhere += " AND "; } strWhere += "C2SysNo=" + queryFilter.C2SysNo.Trim(); } if (!string.IsNullOrEmpty(queryFilter.C3SysNo)) { if (strWhere != string.Empty) { strWhere += " AND "; } strWhere += "C3SysNo=" + queryFilter.C3SysNo.Trim(); } if (!string.IsNullOrEmpty(strWhere)) { subCond += "WHERE " + strWhere; } sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "V.SysNo", QueryConditionOperatorType.In, subCond); } #endregion dataCommand.CommandText = sqlBuilder.BuildQuerySql(); dataCommand.SetParameterValue("@StartNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex); dataCommand.SetParameterValue("@EndNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex + queryFilter.PageInfo.PageSize); EnumColumnList columnEnums = new EnumColumnList(); columnEnums.Add(19, typeof(VendorStatus)); columnEnums.Add(30, typeof(VendorConsignFlag)); columnEnums.Add("AuditStatus", typeof(VendorFinanceRequestStatus)); columnEnums.Add("PaySettleCompany", typeof(PaySettleCompany)); DataTable dt = dataCommand.ExecuteDataTable(columnEnums); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } }
/// <summary> /// 根据输入的PONO获取对应的POItems信息 /// </summary> /// <param name="condition"></param> /// <param name="payableOrderType"></param> /// <returns></returns> public virtual List <APInvoicePOItemInfo> GetPOInputItemsHand(List <int> condition, PayableOrderType payableOrderType) { List <int> orderStatus = new List <int>(); List <int> orderType = new List <int>(); string customCondition = string.Empty; if (payableOrderType == PayableOrderType.PO) { orderStatus.Add(4); orderStatus.Add(6); orderStatus.Add(7); orderStatus.Add(8); orderStatus.Add(9); orderType.Add(0); orderType.Add(5); customCondition = @" NOT Exists ( SELECT PoNo FROM OverseaInvoiceReceiptManagement.dbo.APInvoice_PO_Item ai WITH(NOLOCK) WHERE Status = 'A' and pay.BatchNumber=ai.BatchNumber AND pay.OrderSysNo=ai.PoNo AND pay.OrderType=ai.OrderType UNION ALL SELECT ai.PoNo FROM OverseaInvoiceReceiptManagement.dbo.APInvoice_PO_Item ai WITH(NOLOCK) INNER JOIN OverseaInvoiceReceiptManagement.dbo.APInvoice_Master am WITH(NOLOCK) ON am.DocNo = ai.DocNo and pay.BatchNumber=ai.BatchNumber AND pay.OrderSysNo=ai.PoNo AND pay.OrderType=ai.OrderType WHERE am.Status >= 0 ) "; } else if (payableOrderType == PayableOrderType.RMAPOR) { orderStatus.Add(3); orderStatus.Add(5); orderType.Add(9); customCondition = string.Empty; } //else if (payableOrderType == PayableOrderType.SubInvoice) //{ // orderStatus.Add(3); // orderStatus.Add(2); // orderStatus.Add(1); // orderType.Add(7); // customCondition = string.Empty; //} //else { orderStatus.Add(3); orderType.Add(1); customCondition = string.Empty; } CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetPOInputItemsHand"); List <APInvoicePOItemInfo> result = new List <APInvoicePOItemInfo>(); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder( dataCommand.CommandText, dataCommand, null, "POM.VendorSysNo")) { sqlBuilder.ConditionConstructor.AddInCondition( QueryConditionRelationType.AND, "pay.OrderStatus", DbType.Int32, orderStatus); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "pay.InvoiceStatus", DbType.Int32, "@InvoiceStatus", QueryConditionOperatorType.Equal, 0); sqlBuilder.ConditionConstructor.AddInCondition( QueryConditionRelationType.AND, "pay.ordertype", DbType.Int32, orderType); sqlBuilder.ConditionConstructor.AddInCondition( QueryConditionRelationType.AND, "pay.OrderSysNo", DbType.Int32, condition); //sqlBuilder.ConditionConstructor.AddCondition( // QueryConditionRelationType.AND, // "pay.CompanyCode", // DbType.AnsiStringFixedLength, // "@CompanyCode", // QueryConditionOperatorType.Equal, // CPContext.Current.CompanyCode); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "pay.PayStatus", DbType.Int32, "@PayStatus", QueryConditionOperatorType.NotEqual, -1); if (customCondition != string.Empty) { sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, customCondition); } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); result = dataCommand.ExecuteEntityList <APInvoicePOItemInfo>(); } return(result); }
public DataTable QueryProducts(VirtualRequestQueryProductsFilter filter, out int totalCount) { totalCount = 0; if (filter == null) { return(null); } CustomDataCommand dataCommand = null; if (filter.StockSysNo.HasValue) { dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryVirtualItems"); } else { dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryVirtualItemsWithInventory"); } PagingInfoEntity pageEntity = PagingInfoToPagingInfoEntity(filter.PagingInfo); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pageEntity, "a.SysNo DESC")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.StockType", DbType.AnsiStringFixedLength, "@StockType", QueryConditionOperatorType.NotEqual, "MET"); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "v.StockType", DbType.AnsiStringFixedLength, "@StockType", QueryConditionOperatorType.IsNull, DBNull.Value); sqlBuilder.ConditionConstructor.EndGroupCondition(); sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.ShippingType", DbType.AnsiStringFixedLength, "@ShippingType", QueryConditionOperatorType.NotEqual, "MET"); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "v.ShippingType", DbType.AnsiStringFixedLength, "@ShippingType", QueryConditionOperatorType.IsNull, DBNull.Value); sqlBuilder.ConditionConstructor.EndGroupCondition(); sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "v.InvoiceType", DbType.AnsiStringFixedLength, "@InvoiceType", QueryConditionOperatorType.NotEqual, "MET"); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "v.InvoiceType", DbType.AnsiStringFixedLength, "@InvoiceType", QueryConditionOperatorType.IsNull, DBNull.Value); sqlBuilder.ConditionConstructor.EndGroupCondition(); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductID", DbType.String, "@ItemCode", QueryConditionOperatorType.Like, filter.ProductID); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductName", DbType.String, "@ItemName", QueryConditionOperatorType.Like, filter.ProductName); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductType", DbType.Int32, "@ItemType", QueryConditionOperatorType.Equal, GetPorductTypeEnumInt(filter.ProductType)); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.Status", DbType.Int32, "@Status", filter.Operator == VirtualRequestQueryProductsFilter.OperationType.Equal ? QueryConditionOperatorType.Equal : QueryConditionOperatorType.NotEqual, (int?)filter.Status); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.PMUserSysNo", DbType.Int32, "@PMSysNumber", QueryConditionOperatorType.Equal, filter.PMSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.stockSysNo", DbType.Int32, "@WarehouseSysNumber", QueryConditionOperatorType.Equal, filter.StockSysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "d.Category1Sysno", DbType.Int32, "@Category1SysNumber", QueryConditionOperatorType.Equal, filter.Category1SysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "d.Category2Sysno", DbType.Int32, "@Category2SysNumber", QueryConditionOperatorType.Equal, filter.Category2SysNo); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "d.Category3Sysno", DbType.Int32, "@Category3SysNumber", QueryConditionOperatorType.Equal, filter.Category3SysNo); dataCommand.CommandText = sqlBuilder.BuildQuerySql(); DataTable dt = dataCommand.ExecuteDataTable(new EnumColumnList() { { "Status", typeof(ECCentral.BizEntity.IM.ProductStatus) } }); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); return(dt); } }
/// <summary> /// 获取无效的发票号码 /// </summary> /// <param name="invoiceList"></param> /// <param name="entity"></param> /// <returns></returns> public virtual List <string> GetInvalidInvoiceNo(List <string> invoiceList, APInvoiceItemInputEntity entity) { CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetInvalidInvoiceNo"); List <string> result = new List <string>(); using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder( dataCommand.CommandText, dataCommand, null, "API.InvoiceNo")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "APM.VendorSysNo" , DbType.Int32 , "@VendorSysNo" , QueryConditionOperatorType.Equal , entity.VendorSysNo); sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "API.[Status]" , DbType.String , "@ItemStatus" , QueryConditionOperatorType.Equal , APInvoiceItemStatus.Active); sqlBuilder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.OR); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "API.[Status]" , DbType.String , "@ItemNStatus" , QueryConditionOperatorType.Equal , APInvoiceItemStatus.Deactive); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND , "APM.[Status]" , DbType.Int32 , "@MasterStatus" , QueryConditionOperatorType.MoreThanOrEqual , APInvoiceMasterStatus.Origin); sqlBuilder.ConditionConstructor.EndGroupCondition(); sqlBuilder.ConditionConstructor.EndGroupCondition(); sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND , "API.InvoiceNo" , DbType.String , invoiceList); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "API.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, entity.CompanyCode); dataCommand.CommandText = sqlBuilder.BuildQuerySql(); var dataSet = dataCommand.ExecuteDataSet(); if (dataSet.Tables[0].Rows.Count > 0) { foreach (DataRow dr in dataSet.Tables[0].Rows) { result.Add(dr["InvoiceNo"].ToString()); } } } return(result); }
public virtual DataTable QueryInventoryStockList(InventoryQueryFilter queryFilter, out int totalCount) { DataTable dt = new DataTable(); if (queryFilter == null) { totalCount = 0; return(null); } var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryInventoryStock"); PagingInfoEntity pagingInfo = new PagingInfoEntity() { SortField = queryFilter.PagingInfo.SortBy, StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize, MaximumRows = queryFilter.PagingInfo.PageSize }; using (var sqlBuilder = new DynamicQuerySqlBuilder( dataCommand.CommandText, dataCommand, pagingInfo, "b.SysNo")) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode); if (queryFilter.ProductSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.SysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo.Value); } if (queryFilter.StockSysNo.HasValue && queryFilter.StockSysNo.Value > 0) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo.Value); } if (!string.IsNullOrEmpty(queryFilter.PositionInWarehouse)) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.Position1", DbType.String, "@PositionInWarehouse", QueryConditionOperatorType.Like, queryFilter.PositionInWarehouse); } if (queryFilter.IsAccountQtyLargerThanZero.HasValue && queryFilter.IsAccountQtyLargerThanZero.Value) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.AccountQty", DbType.Int32, "@AccountQuantity", QueryConditionOperatorType.MoreThan, 0); } //if (string.IsNullOrEmpty(queryFilter.AuthorizedPMsSysNumber)) //{ // queryFilter.AuthorizedPMsSysNumber = "-999"; //} //sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "b.PMUserSysNo", //QueryConditionOperatorType.In, queryFilter.AuthorizedPMsSysNumber); if (queryFilter.ManufacturerSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.ManufacturerSysNo", DbType.Int32, "@ManufacturerSysNo", QueryConditionOperatorType.Equal, queryFilter.ManufacturerSysNo.Value); } if (queryFilter.BrandSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.BrandSysNo", DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal, queryFilter.BrandSysNo.Value); } if (queryFilter.C3SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c3.SysNo", DbType.Int32, "@C3SysNo", QueryConditionOperatorType.Equal, queryFilter.C3SysNo.Value); } if (queryFilter.C2SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c2.SysNo", DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal, queryFilter.C2SysNo.Value); } if (queryFilter.C1SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c1.SysNo", DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal, queryFilter.C1SysNo.Value); } if (queryFilter.VendorSysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "plm.LastVendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo.Value); } dataCommand.CommandText = sqlBuilder.BuildQuerySql(); dt = dataCommand.ExecuteDataTable(); totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount")); } if (queryFilter.IsUnPayShow.HasValue && queryFilter.IsUnPayShow.Value) { if (dt != null && dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { int productSysNo = int.Parse(row["ProductSysNo"].ToString()); int stockSysNo = int.Parse(row["StockSysNo"].ToString()); row["UnPayOrderQty"] = GetUnPayQtyByStock(productSysNo, stockSysNo); } } } return(dt); }