public static void RemoveOccupyStockUser(string CustomerSysNo)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("RemoveOccupyStockUser");
// command.AddInputParameter("@RemoveID", System.Data.DbType.String, CustomerSysNo);
command.ReplaceParameterValue("@RemoveID", CustomerSysNo);
command.ReplaceParameterValue("@CompanyCode", CompanyCode);
command.ExecuteNonQuery();
}
/// <summary>
/// 通过订单系统编号(多个订单系统编号通过逗号分隔)取得订单确认关联信息列表
/// </summary>
/// <param name="SOSysNos">订单系统编号(多个订单系统编号通过逗号分隔)</param>
/// <returns></returns>
public List <PostIncomeConfirmInfo> GetConfirmedListBySOSysNo(string SOSysNos)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetPostIncomeConfirmedListBySONo");
dataCommand.ReplaceParameterValue("#ConfirmedSoSysNo", SOSysNos);
return(dataCommand.ExecuteEntityList <PostIncomeConfirmInfo>());
}
private static void ReplaceVisitOrderStrWhere(CustomerVisitQueryFilter filter, CustomDataCommand command)
{
string visitOrderStrWhere = "where 1=1";
if (filter.SpiderOrderDateFrom.HasValue)
{
visitOrderStrWhere += " and OrderDate>='" + filter.SpiderOrderDateFrom + "'";
}
if (filter.SpiderOrderDateTo.HasValue)
{
visitOrderStrWhere += " and OrderDate<'" + filter.SpiderOrderDateTo + "'";
}
command.ReplaceParameterValue("#VisitOrderStrWhere#",
visitOrderStrWhere);
}
public DataTable QueryRefund(RefundQueryFilter filter, out int totalCount)
{
if (!string.IsNullOrEmpty(filter.PagingInfo.SortBy))
{
string sortCondition = filter.PagingInfo.SortBy.Trim();
Match match = Regex.Match(sortCondition, @"^(?<SortColumn>[\S]+)(?:\s+(?<SortType>ASC|DESC))?$", RegexOptions.IgnoreCase);
if (match.Groups["SortColumn"].Success)
{
string sortColumn = match.Groups["SortColumn"].Value;
string sortType = match.Groups["SortType"].Success ? match.Groups["SortType"].Value : "DESC";
switch (sortColumn)
{
case "RefundID":
filter.PagingInfo.SortBy = String.Format("{0} {1}", "RMA.RefundID", sortType);
break;
case "SOSysNo":
filter.PagingInfo.SortBy = String.Format("{0} {1}", "RMA.SOSysNo", sortType);
break;
case "SOInvoiceNo":
filter.PagingInfo.SortBy = String.Format("{0} {1}", "RMA.SOInvoiceNo", sortType);
break;
case "CreateTime":
filter.PagingInfo.SortBy = String.Format("{0} {1}", "RMA.CreateTime", sortType);
break;
case "AuditTime":
filter.PagingInfo.SortBy = String.Format("{0} {1}", "RMA.AuditTime", sortType);
break;
case "RefundTime":
filter.PagingInfo.SortBy = String.Format("{0} {1}", "RMA.RefundTime", sortType);
break;
case "RefundStatus":
filter.PagingInfo.SortBy = String.Format("{0} {1}", "RMA.Status", sortType);
break;
case "AuditStatus":
filter.PagingInfo.SortBy = String.Format("{0} {1}", "D.Status", sortType);
break;
case "Customer":
filter.PagingInfo.SortBy = String.Format("{0} {1}", "E.CustomerName", sortType);
break;
case "ShippedWarehouse":
filter.PagingInfo.SortBy = String.Format("{0} {1}", "G.ShippedWarehouse", sortType);
break;
case "InvoiceWarehouse":
filter.PagingInfo.SortBy = String.Format("{0} {1}", "F.StockName", sortType);
break;
}
}
}
string commandName = (!string.IsNullOrEmpty(filter.SOSysNoString)) ?
"QueryRefundWithSoSysNOParam" :
"QueryRefund";
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig(commandName);
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, "RMA.SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"RMA.RefundID", DbType.String,
"@RefundID", QueryConditionOperatorType.Like,
filter.RefundID);
builder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND,
"RMA.CreateTime", DbType.DateTime, "@CreateTime",
QueryConditionOperatorType.MoreThanOrEqual,
QueryConditionOperatorType.LessThan,
filter.CreateTimeFrom,
filter.CreateTimeTo);
builder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND,
"RMA.RefundTime", DbType.DateTime,
"@RefundTime", QueryConditionOperatorType.MoreThanOrEqual,
QueryConditionOperatorType.LessThan,
filter.RefundTimeFrom,
filter.RefundTimeTo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RMA.CustomerSysNo", DbType.Int32, "@CustomerSysNo", QueryConditionOperatorType.Equal, filter.CustomerSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RMA.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.Status);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "D.Status", DbType.Int32, "@AuditStatus", QueryConditionOperatorType.Equal, filter.AuditStatus);
if (filter.ProductSysNo != null)
{
ConditionConstructor subQueryBuilder = builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist, "SELECT TOP 1 1 FROM dbo.RMA_Register WITH (NOLOCK) INNER JOIN RMA_Refund_item WITH (NOLOCK) ON RMA_Register.[SysNo] = RMA_Refund_item.[RegisterSysNo]");
subQueryBuilder.AddCustomCondition(QueryConditionRelationType.AND, "RMA_Refund_item.[RefundSysNo] = RMA.[SysNo]");
subQueryBuilder.AddCondition(QueryConditionRelationType.AND, "ProductSysNo", DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, filter.ProductSysNo);
}
if (filter.IsVIP != null)
{
builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "E.VIPRank", DbType.Int32, "@VIPRank1", QueryConditionOperatorType.Equal, filter.IsVIP.Value ? VIPRank.VIPAuto : VIPRank.NormalAuto);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "E.VIPRank", DbType.Int32, "@VIPRank2", QueryConditionOperatorType.Equal, filter.IsVIP.Value ? VIPRank.VIPManual : VIPRank.NormalManual);
builder.ConditionConstructor.EndGroupCondition();
}
if (filter.WarehouseCreated != null && filter.WarehouseCreated.Trim() != String.Empty)
{
ConditionConstructor subQueryBuilder = builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist, "SELECT TOP 1 1 FROM dbo.RMA_Register WITH (NOLOCK) INNER JOIN RMA_Refund_item WITH (NOLOCK) ON RMA_Register.[SysNo] = RMA_Refund_item.[RegisterSysNo]");
subQueryBuilder.AddCustomCondition(QueryConditionRelationType.AND, "RMA_Refund_item.[RefundSysNo] = RMA.[SysNo]");
subQueryBuilder.AddCondition(QueryConditionRelationType.AND, "LocationWarehouse", DbType.AnsiStringFixedLength, "@LocationWarehouse", QueryConditionOperatorType.Equal, filter.WarehouseCreated);
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RMA.InvoiceLocation", DbType.AnsiStringFixedLength, "@InvoiceLocation", QueryConditionOperatorType.Equal, filter.InvoiceLocation);
if (filter.WarehouseShipped != null && filter.WarehouseShipped != String.Empty)
{
ConditionConstructor subQueryBuilder = builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist, "SELECT TOP 1 1 FROM dbo.RMA_Register WITH (NOLOCK) INNER JOIN RMA_Refund_item WITH (NOLOCK) ON RMA_Register.[SysNo] = RMA_Refund_item.[RegisterSysNo]");
subQueryBuilder.AddCustomCondition(QueryConditionRelationType.AND, "RMA_Refund_item.[RefundSysNo] = RMA.[SysNo]");
subQueryBuilder.AddCondition(QueryConditionRelationType.AND, "ShippedWarehouse", DbType.AnsiStringFixedLength, "@ShippedWarehouse", QueryConditionOperatorType.Equal, filter.WarehouseShipped);
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RMA.[CompanyCode]", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
if (!string.IsNullOrEmpty(filter.SOSysNoString))
{
command.AddInputParameter("@SoSysNoStr", DbType.AnsiString,
filter.SOSysNoString);
}
command.CommandText = builder.BuildQuerySql();
command.ReplaceParameterValue("#OrderType#", SOIncomeOrderType.RO);
DataTable dt = command.ExecuteDataTable(new EnumColumnList {
{ "Status", typeof(RMARefundStatus) },
{ "AuditStatus", typeof(RefundStatus) }
});
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}