/// <summary>
/// 查询
/// </summary>
/// <param name="query"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="sortField"></param>
/// <param name="callback"></param>
public void Query(AuditRefundQueryVM query, int pageSize, int pageIndex, string sortField, Action <AuditRefundQueryResultVM> callback)
{
AuditRefundQueryFilter filter = query.ConvertVM <AuditRefundQueryVM, AuditRefundQueryFilter>();
filter.PagingInfo = new PagingInfo()
{
PageIndex = pageIndex,
PageSize = pageSize,
SortBy = sortField
};
string relativeUrl = "/InvoiceService/SOIncomeRefund/Query";
restClient.QueryDynamicData(relativeUrl, filter, (obj, args) =>
{
if (args.FaultsHandle())
{
return;
}
AuditRefundQueryResultVM result = new AuditRefundQueryResultVM();
result.ResultList = DynamicConverter <AuditRefundVM> .ConvertToVMList(args.Result.Rows);
result.TotalCount = args.Result.TotalCount;
callback(result);
});
}
public QueryResult QueryAuditRefund(AuditRefundQueryFilter request)
{
int totalCount;
var dataTable = ObjectFactory <IAuditRefundQueryDA> .Instance.Query(request, out totalCount);
return(new QueryResult()
{
Data = dataTable,
TotalCount = totalCount
});
}
public QueryResult ExportAuditRefund(AuditRefundQueryFilter request)
{
int totalCount;
var dataTable = ObjectFactory <IAuditRefundQueryDA> .Instance.Query(request, out totalCount);
dataTable.Columns.Add("RefundStatusDesc");
foreach (DataRow row in dataTable.Rows.AsParallel())
{
if (string.IsNullOrWhiteSpace(row["RefundStatus"].ToString()))
{
row["RefundStatus"] = "N/A";
}
if (row.IsNull("RefundStatus") || row["RefundStatus"].ToString() == "-99")
{
row["RefundStatusDesc"] = "N/A";
}
else
{
row["RefundStatusDesc"] = ((RefundStatus)row["RefundStatus"]).ToDisplayText();
}
if (!row.IsNull("ShipRejected") && row["ShipRejected"].ToString() == "1")
{
if (!row.IsNull("IncomeAmt"))
{
row["RefundCashAmt"] = ((decimal)row["IncomeAmt"]).ToString(InvoiceConst.StringFormat.DecimalFormat);
}
else
{
row["RefundCashAmt"] = "0.00";
}
}
else
{
if (!row.IsNull("RefundCashAmt"))
{
row["RefundCashAmt"] = ((decimal)row["RefundCashAmt"]).ToString(InvoiceConst.StringFormat.DecimalFormat);
}
else
{
row["RefundCashAmt"] = "0.00";
}
}
}
return(new QueryResult()
{
Data = dataTable,
TotalCount = totalCount
});
}
public void ExportExcelFile(AuditRefundQueryVM queryVM, ColumnSet[] columnSet)
{
AuditRefundQueryFilter queryFilter = queryVM.ConvertVM <AuditRefundQueryVM, AuditRefundQueryFilter>();
queryFilter.PagingInfo = new PagingInfo
{
PageSize = ConstValue.MaxRowCountLimit,
PageIndex = 0,
SortBy = null
};
queryFilter.CompanyCode = CPApplication.Current.CompanyCode;
string relativeUrl = "/InvoiceService/SOIncomeRefund/Export";
restClient.ExportFile(relativeUrl, queryFilter, columnSet);
}
public DataTable Query(AuditRefundQueryFilter filter, out int totalCount)
{
DataTable result = null;
PagingInfoEntity pagingInfo = new PagingInfoEntity();
if (filter.PagingInfo != null)
{
MapSortField(filter.PagingInfo);
pagingInfo.MaximumRows = filter.PagingInfo.PageSize;
pagingInfo.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
pagingInfo.SortField = filter.PagingInfo.SortBy;
}
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetAuditRefundList");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingInfo, "SOIncomeBank.SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[SysNo]", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.Id);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[Status]", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.AuditStatus);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[HaveAutoRMA]", DbType.Int32, "@HaveAutoRMA", QueryConditionOperatorType.Equal, filter.ShipRejected);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[OrderType]", DbType.Int32, "@OrderType", QueryConditionOperatorType.Equal, filter.OrderType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[RefundPayType]", DbType.Int32, "@RefundType", QueryConditionOperatorType.Equal, filter.RefundPayType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[RefundReason]", DbType.Int32, "@RMAReason", QueryConditionOperatorType.Equal, filter.RMAReasonCode);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[OrderSysNo]", DbType.Int32, "@RMANumber", QueryConditionOperatorType.Equal, filter.RMANumber);
if (!string.IsNullOrEmpty(filter.OrderNumber))
{
List <int> OrderNumberList = new List <int>();
int[] OrderNumberArray = Array.ConvertAll <string, int>(filter.OrderNumber.Split('.'),
new Converter <string, int>((source) =>
{
return(Convert.ToInt32(string.IsNullOrEmpty(source) ? "0" : source));
}));
OrderNumberList.AddRange(OrderNumberArray);
sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "SOIncomeBank.SOSysNo", DbType.Int32, OrderNumberList);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[CreateTime]", DbType.DateTime, "@CreateTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.CreateTimeFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[CreateTime]", DbType.DateTime, "@CreateTimeTo", QueryConditionOperatorType.LessThanOrEqual, filter.CreateTimeTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[AuditTime]", DbType.DateTime, "@AuditTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.AuditTimeFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[AuditTime]", DbType.DateTime, "@AuditTimeTo", QueryConditionOperatorType.LessThanOrEqual, filter.AuditTimeTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RMARefund.Status", DbType.Int32, "@RMAStatus", QueryConditionOperatorType.Equal, filter.RMAStatus);
if (filter.CashRelated)
{
using (GroupCondition group = new GroupCondition(sqlBuilder, QueryConditionRelationType.AND))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RMARefund.[CashFlag]", DbType.Int32, "@CashFlag", QueryConditionOperatorType.Equal, 0);
sqlBuilder.ConditionConstructor.AddNullCheckCondition(QueryConditionRelationType.OR, "RMARefund.[CashFlag]", QueryConditionOperatorType.IsNull);
}
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncome.[Status]", DbType.Int32, "@RefundStatus", QueryConditionOperatorType.Equal, filter.RefundStatus);
//为提高性能改为左匹配
if (!string.IsNullOrEmpty(filter.CustomerID))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, string.Format(" Customer.CustomerID like '{0}%'", filter.CustomerID));
}
switch (filter.OperationType)
{
case OperationSignType.LessThanOrEqual:
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.RefundCashAmt", DbType.Decimal, "@RefundAmount", QueryConditionOperatorType.LessThanOrEqual, filter.RefundAmount);
break;
case OperationSignType.Equal:
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.RefundCashAmt", DbType.Decimal, "@RefundAmount", QueryConditionOperatorType.Equal, filter.RefundAmount);
break;
case OperationSignType.MoreThanOrEqual:
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.RefundCashAmt", DbType.Decimal, "@RefundAmount", QueryConditionOperatorType.MoreThanOrEqual, filter.RefundAmount);
break;
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[CompanyCode]", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "NetPayExt.[Status]", DbType.Int32, "@WLTRefundStatus", QueryConditionOperatorType.Equal, filter.WLTRefundStatus);
if (!string.IsNullOrEmpty(filter.PayTypeSysNo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOMaster.payTypeSysNo",
DbType.Int32, "@payTypeSysNo", QueryConditionOperatorType.Equal, Int32.Parse(filter.PayTypeSysNo));
}
if (filter.IsVAT.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOMaster.IsVAT",
DbType.Int32, "@IsVAT", QueryConditionOperatorType.Equal, filter.IsVAT.Value);
}
if (filter.StockSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
@"exists (SELECT TOP 1 1 FROM OverseaOrderManagement.dbo.V_OM_SO_Item ITEM WITH(NOLOCK)
WHERE item.SOSysNo = SOMaster.SysNo
AND item.WarehouseNumber = @WareHouseNumber)");
cmd.AddInputParameter("@WareHouseNumber", DbType.Int32, filter.StockSysNo.Value);
}
cmd.CommandText = sqlBuilder.BuildQuerySql();
cmd.CommandTimeout = TimeOut;
EnumColumnList enumColumns = new EnumColumnList();
enumColumns.Add("AuditStatus", typeof(RefundStatus));
enumColumns.Add("OrderType", typeof(RefundOrderType));
enumColumns.Add("RefundStatus", typeof(SOIncomeStatus));
enumColumns.Add("RefundPayType", typeof(RefundPayType));
enumColumns.Add("Source", typeof(NetPaySource));
result = cmd.ExecuteDataTable(enumColumns);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
}
return(result);
}
