public virtual QueryResult QueryOutBoundNotReturn(OutBoundNotReturnQueryFilter request) { int totalCount; var dataTable = ObjectFactory <IReportQueryDA> .Instance.QueryOutBoundNotReturn(request, out totalCount); dataTable.Columns.Add("SendEmailCount", typeof(int)); dataTable.Columns.Add("IsContact", typeof(Boolean)); if (dataTable.Rows.Count > 0) { foreach (DataRow dr in dataTable.Rows) { dr["SendEmailCount"] = dr["IsSendMail"] == DBNull.Value ? 0 : dr["IsSendMail"]; DateTime now = DateTime.Now; VendorStatus Vendor_Status; if (!Enum.TryParse(dr["Vendor_Status"].ToString(), out Vendor_Status)) { //转换失败赋个条件判断不需要的值... Vendor_Status = VendorStatus.UnAvailable; } DateTime ValidDate, ExpiredDate; ValidDate = dr["ValidDate"] == DBNull.Value ? now.AddDays(10) : Convert.ToDateTime(dr["ValidDate"]); ExpiredDate = dr["ExpiredDate"] == DBNull.Value ? now.AddDays(-10) : Convert.ToDateTime(dr["ExpiredDate"]); decimal?TotalPOMoney, ContractAmt; TotalPOMoney = dr["TotalPOMoney"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["TotalPOMoney"]); ContractAmt = dr["ContractAmt"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["ContractAmt"]); bool IsContact = false; if (Vendor_Status == VendorStatus.Available) { if (now >= ValidDate && now <= ExpiredDate) { IsContact = true; } else if (TotalPOMoney > ContractAmt) { IsContact = true; } } dr["IsContact"] = IsContact; } } return(new QueryResult() { Data = dataTable, TotalCount = totalCount }); }
public void ExportOutBoundExcelFile(OutBoundNotReturnQueryVM queryVM, ColumnSet[] columns) { OutBoundNotReturnQueryFilter queryFilter = new OutBoundNotReturnQueryFilter(); queryFilter = queryVM.ConvertVM <OutBoundNotReturnQueryVM, OutBoundNotReturnQueryFilter>(); queryFilter.PagingInfo = new PagingInfo { PageSize = ConstValue.MaxRowCountLimit, PageIndex = 0, SortBy = string.Empty }; queryFilter.CompanyCode = CPApplication.Current.CompanyCode; string relativeUrl = "/RMAService/Report/OutBoundNotReturn/Query"; restClient.ExportFile(relativeUrl, queryFilter, columns); }
public void QueryOutBoundNotReturn(OutBoundNotReturnQueryVM queryVM, int PageSize, int PageIndex, string SortField, EventHandler <RestClientEventArgs <dynamic> > callback) { OutBoundNotReturnQueryFilter queryFilter = new OutBoundNotReturnQueryFilter(); queryFilter = queryVM.ConvertVM <OutBoundNotReturnQueryVM, OutBoundNotReturnQueryFilter>(); queryFilter.PagingInfo = new PagingInfo { PageSize = PageSize, PageIndex = PageIndex, SortBy = SortField }; queryFilter.CompanyCode = CPApplication.Current.CompanyCode; string relativeUrl = "/RMAService/Report/OutBoundNotReturn/Query"; restClient.QueryDynamicData(relativeUrl, queryFilter, (obj, args) => { if (args.FaultsHandle()) { return; } callback(obj, args); }); }
public virtual DataTable QueryOutBoundNotReturn(OutBoundNotReturnQueryFilter 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("QueryNotReturnPerformance"); using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, " ob.SysNo DESC ")) { #region conditions if (filter.C3SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "product.Category3SysNo", DbType.Int32, "@C3SysNo", QueryConditionOperatorType.Equal, filter.C3SysNo.Value ); } else if (filter.C2SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "category.Category2Sysno", DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal, filter.C2SysNo.Value ); } else if (filter.C1SysNo.HasValue) { sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "category.Category1Sysno", DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal, filter.C1SysNo.Value ); } if (filter.HasResponse.HasValue) { sqlBuilder.ConditionConstructor.AddNullCheckCondition( QueryConditionRelationType.AND, "register.ResponseDesc", filter.HasResponse.Value ? QueryConditionOperatorType.IsNotNull : QueryConditionOperatorType.IsNull ); } sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "ob.OutTime", DbType.DateTime, "@OutTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.OutTimeFrom ); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "ob.OutTime", DbType.DateTime, "@OutTimeTo", QueryConditionOperatorType.LessThan, filter.OutTimeTo ); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "product.PMUserSysNo", DbType.Int32, "@PMUserSysNo", QueryConditionOperatorType.Equal, filter.PMUserSysNo ); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "product.SysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, filter.ProductSysNo ); if (filter.SendDays.HasValue) { sqlBuilder.ConditionConstructor.AddCustomCondition( QueryConditionRelationType.AND, "DATEADD(DAY, @SendDays, ob.OutTime) < GETDATE()" ); cmd.AddInputParameter("@SendDays", DbType.Int32, filter.SendDays.Value); } sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "so.SysNo", DbType.Int32, "@SOSysNo", QueryConditionOperatorType.Equal, filter.SOSysNo ); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "ob.VendorSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, filter.VendorSysNo ); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "register.RefundStatus", DbType.Int32, "@RefundStatus", QueryConditionOperatorType.Equal, filter.RefundStatus ); sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "register.RevertStatus", DbType.Int32, "@RevertStatus", QueryConditionOperatorType.Equal, filter.RevertStatus ); // only query Handling register sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "register.Status", DbType.Int32, "@RegisterStatus", QueryConditionOperatorType.Equal, 1 ); // only query SendAlready outbund sqlBuilder.ConditionConstructor.AddCondition( QueryConditionRelationType.AND, "register.OutBoundStatus", DbType.Int32, "@OutboundStatus", QueryConditionOperatorType.Equal, 1 ); sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "register.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode); #endregion cmd.CommandText = sqlBuilder.BuildQuerySql(); EnumColumnList enumList = new EnumColumnList(); enumList.Add("RefundStatus", typeof(RMARefundStatus)); enumList.Add("RevertStatus", typeof(RMARevertStatus)); enumList.Add("Vendor_Status", typeof(VendorStatus)); CodeNamePairColumnList codeList = new CodeNamePairColumnList(); codeList.Add("PayPeriodType", "RMA", "VendorPayPeriodType"); DataTable dt = cmd.ExecuteDataTable(enumList, codeList); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(dt); } }