public virtual DataTable QueryRMATracking(RMATrackingQueryFilter 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("QueryTracking");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, " A.SysNo DESC "))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.RegisterSysNo", DbType.Int32, "@RegisterSysNo", QueryConditionOperatorType.Equal, filter.RegisterSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.SOSysNo", DbType.Int32, "@SOSysNo", QueryConditionOperatorType.Equal, filter.SOSysNo);
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "A.CreateTime", DbType.DateTime, "@CreateTime", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThan, filter.CreateTimeFrom, filter.CreateTimeTo);
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "A.UpDateTime", DbType.DateTime, "@CloseTime", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThan, filter.CloseTimeFrom, filter.CloseTimeTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.CreateUserSysNo", DbType.Int32, "@CreateUserSysNo", QueryConditionOperatorType.Equal, filter.CreateUserSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.UpdateUserSysNo", DbType.Int32, "@UpdateUserSysNo", QueryConditionOperatorType.Equal, filter.UpdateUserSysNo);
if (filter.NextHandler != null)
{
ConditionConstructor subQueryBuilder = sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist, "Select SysNo From dbo.RMA_Register RMA with(nolock)");
subQueryBuilder.AddCustomCondition(QueryConditionRelationType.AND, "RMA.SysNo=A.RegisterSysNo");
subQueryBuilder.AddCondition(QueryConditionRelationType.AND, "RMA.NextHandler", DbType.Int32, "@NextHandler", QueryConditionOperatorType.Equal, filter.NextHandler);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.[CompanyCode]", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("Status", typeof(InternalMemoStatus));
CodeNamePairColumnList codeNameList = new CodeNamePairColumnList();
codeNameList.Add("Source", "RMA", "RMAInternalMemoSourceType");
DataTable dt = cmd.ExecuteDataTable(enumList, codeNameList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable UserQuery(VendorUserQueryFilter filter, out int dataCount)
{
if (filter.PagingInfo.SortBy != null)
{
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";
#region switch
switch (sortColumn)
{
case "SysNo":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.SysNo", sortType);
break;
case "RoleName":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.RoleName", sortType);
break;
case "Status":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.Status", sortType);
break;
case "InUser":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.InUser", sortType);
break;
case "InDate":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.InDate", sortType);
break;
case "EditUser":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.EditUser", sortType);
break;
case "EditDate":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.EditDate", sortType);
break;
case "VendorSysNo":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "a.VendorSysNo", sortType);
break;
case "VendorName":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "b.VendorName", sortType);
break;
case "VendorStatus":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "b.Status", sortType);
break;
case "Rank":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "b.Rank", sortType);
break;
case "IsConsign":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "b.IsConsign", sortType);
break;
}
#endregion
}
}
CustomDataCommand command = DataCommandManager.
CreateCustomDataCommandFromConfig("External_Query_Vendor");
using (var builder = new DynamicQuerySqlBuilder(
command.CommandText, command, HelpDA.ToPagingInfo(filter.PagingInfo), "a.SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.Equal,
filter.SysNo);
if (!string.IsNullOrEmpty(filter.SerialNum))
{
string[] serialNum = filter.SerialNum.Split('-');
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.VendorSysNo", DbType.Int32,
"@VendorSysNo", QueryConditionOperatorType.Equal,
serialNum[0]);
if (serialNum.Length > 1)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.UserNum", DbType.Int32,
"@UserNum", QueryConditionOperatorType.Equal,
Convert.ToInt32(serialNum[1]).ToString());
}
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.UserName", DbType.String,
"@UserName", QueryConditionOperatorType.LeftLike,
filter.UserName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.UserID", DbType.String,
"@UserID", QueryConditionOperatorType.LeftLike,
filter.UserID);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Status", DbType.AnsiStringFixedLength,
"@Status", QueryConditionOperatorType.Equal,
((char?)filter.UserStatus).ToString());
if (filter.RoleSysNo.HasValue)
{
builder.ConditionConstructor.BeginGroupCondition(QueryConditionRelationType.AND);
ConditionConstructor subQueryBuilder = builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null,
QueryConditionOperatorType.Exist,
@"SELECT TOP 1 1
FROM (SELECT
UserSysNo
FROM [IPP3].[dbo].[VendorUser_User_Role] WITH(NOLOCK)
WHERE
RoleSysNo=@RoleSysNo
) AS RESULT
WHERE
RESULT.[UserSysNo]=a.[SysNo]");
builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.OR, null, QueryConditionOperatorType.Exist,
@"SELECT TOP 1 1
FROM (SELECT
UserSysNo
FROM [IPP3].[dbo].[VendorUser_RoleMapping] WITH(NOLOCK)
WHERE
RoleSysNo=@RoleSysNo
) AS RESULT
WHERE
RESULT.[UserSysNo]=a.[SysNo]");
command.AddInputParameter("@RoleSysNo", DbType.Int32, filter.RoleSysNo);
builder.ConditionConstructor.EndGroupCondition();
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.VendorSysNo", DbType.Int32,
"@VendorSysNo", QueryConditionOperatorType.Equal,
filter.VendorSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.VendorName", DbType.String,
"@VendorName", QueryConditionOperatorType.Like,
filter.VendorName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.ExpiredDate", DbType.DateTime,
"@ExpiredDateFrom", QueryConditionOperatorType.MoreThanOrEqual,
filter.ExpiredDateFrom);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.ExpiredDate", DbType.DateTime,
"@ExpiredDateTo", QueryConditionOperatorType.LessThanOrEqual,
filter.ExpiredDateTo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.Contact", DbType.String,
"@Contact", QueryConditionOperatorType.LeftLike,
filter.Contact);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.Phone", DbType.String,
"@Phone", QueryConditionOperatorType.LeftLike,
filter.Phone);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.Address", DbType.String,
"@Address", QueryConditionOperatorType.LeftLike,
filter.Address);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.Status", DbType.Int32,
"@VendorStatus", QueryConditionOperatorType.Equal,
filter.VendorStatus);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.Rank", DbType.String,
"@Rank", QueryConditionOperatorType.Equal,
filter.Rank);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"b.IsConsign", DbType.Int32,
"@IsConsign", QueryConditionOperatorType.Equal,
filter.ConsignType);
if (filter.ManufacturerSysNo.HasValue ||
(!string.IsNullOrEmpty(filter.AgentLevel) &&
filter.AgentLevel.Trim() != "") ||
filter.C1SysNo.HasValue)
{
string subCond = @"SELECT distinct [VendorSysNo]
FROM dbo.Vendor_Manufacturer WITH(NOLOCK) ";
string strWhere = string.Empty;
//代理品牌 二级类 三级类 代理级别
if (filter.ManufacturerSysNo.HasValue)
{
strWhere += "ManufacturerSysNo=@ManufacturerSysNo";
command.AddInputParameter("@ManufacturerSysNo",
DbType.Int32, filter.ManufacturerSysNo);
}
if (!string.IsNullOrEmpty(filter.AgentLevel) &&
filter.AgentLevel.Trim() != "")
{
if (strWhere != string.Empty)
{
strWhere += " AND ";
}
strWhere += "AgentLevel=@AgentLevel";
command.AddInputParameter("@AgentLevel",
DbType.String, filter.AgentLevel);
}
if (filter.C3SysNo.HasValue)
{
if (strWhere != string.Empty)
{
strWhere += " AND ";
}
strWhere += "C3SysNo=@C3SysNo";
command.AddInputParameter("@C3SysNo",
DbType.Int32, filter.C3SysNo);
}
else
{
if (filter.C2SysNo.HasValue)
{
if (strWhere != string.Empty)
{
strWhere += " AND ";
}
strWhere += "C2SysNo=@C2SysNo";
command.AddInputParameter("@C2SysNo",
DbType.Int32, filter.C2SysNo);
}
else
{
if (filter.C1SysNo.HasValue)
{
if (strWhere != string.Empty)
{
strWhere += " AND ";
}
strWhere += @"C2SysNo in (SELECT
[Category2Sysno]
FROM [OverseaContentManagement].[dbo].[V_CM_CategoryInfo] WITH(NOLOCK)
WHERE
[Category1Sysno]=@C1SysNo)";
command.AddInputParameter("@C1SysNo",
DbType.Int32, filter.C1SysNo);
}
}
}
if (!string.IsNullOrEmpty(strWhere))
{
subCond += "WHERE status=0 and " + strWhere;
}
builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "b.SysNo",
QueryConditionOperatorType.In, subCond);
}
command.CommandText = builder.BuildQuerySql();
DataTable dt = command.ExecuteDataTable();
var enumColList = new EnumColumnList();
enumColList.Add("IsConsign", typeof(VendorConsignFlag));
enumColList.Add("Status", typeof(ValidStatus));
enumColList.Add("VendorStatus", typeof(VendorStatus));
command.ConvertEnumColumn(dt, enumColList);
dataCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable QueryRegister(RegisterQueryFilter 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;
CustomDataCommand cmd = null;
if (filter.IsRepeatRegister.HasValue && filter.IsRepeatRegister.Value)
{
cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryRegistersForRepeatRegister");
}
else
{
cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryRegisters");
}
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, " register.SysNo DESC "))
{
#region build conditions
#region
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.CustomerSysNo",
DbType.Int32,
"@CustomerSysNo",
QueryConditionOperatorType.Equal,
filter.CustomerSysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.ProductSysNo",
DbType.Int32,
"@ProductSysNo",
QueryConditionOperatorType.Equal,
filter.ProductSysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.RequestID",
DbType.String,
"@RequestID",
QueryConditionOperatorType.Equal,
filter.RequestID
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.IsRecommendRefund",
DbType.Int32,
"@IsRecommendRefund",
QueryConditionOperatorType.Equal,
filter.IsRecommendRefund
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.RequestType",
DbType.Int32,
"@RequestTypeCode",
QueryConditionOperatorType.Equal,
filter.RequestType
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.SOSysNo",
DbType.Int32,
"@SOSysNo",
QueryConditionOperatorType.Equal,
filter.SOSysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.SysNo",
DbType.Int32,
"@SysNo",
QueryConditionOperatorType.Equal,
filter.RegisterSysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"customer.CustomerID",
DbType.String,
"@CustomerID",
QueryConditionOperatorType.Equal,
filter.CustomerID
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.CreateTime",
DbType.DateTime,
"@CreateTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.CreateTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.CreateTime",
DbType.DateTime,
"@CreateTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.CreateTimeTo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.RevertStatus",
DbType.Int32,
"@RevertStatusCode",
QueryConditionOperatorType.Equal,
filter.RevertStatus
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.NewProductStatus",
DbType.Int32,
"@NewProductStatusCode",
QueryConditionOperatorType.Equal,
filter.NewProductStatus
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.OutBoundStatus",
DbType.Int32,
"@OutBoundStatusCode",
QueryConditionOperatorType.Equal,
filter.OutBoundStatus
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.ReturnStatus",
DbType.Int32,
"@ReturnStatusCode",
QueryConditionOperatorType.Equal,
filter.ReturnStatus
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.Status",
DbType.Int32,
"@StatusCode",
QueryConditionOperatorType.Equal,
filter.RequestStatus
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.RefundStatus",
DbType.Int32,
"@RefundStatusCode",
QueryConditionOperatorType.Equal,
filter.RefundStatus
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.NextHandler",
DbType.Int32,
"@NextHandlerCode",
QueryConditionOperatorType.Equal,
filter.NextHandler
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.IsWithin7Days",
DbType.Int32,
"@IsWithin7Days",
QueryConditionOperatorType.Equal,
filter.IsWithin7Days
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.RMAReason",
DbType.Int32,
"@RMAReason",
QueryConditionOperatorType.Equal,
filter.RMAReason
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"product.PMUserSysNo",
DbType.Int32,
"@PMUserSysNo",
QueryConditionOperatorType.Equal,
filter.PMUserSysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"category3.Category1SysNo",
DbType.Int32,
"@C1SysNo",
QueryConditionOperatorType.Equal,
filter.Category1SysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"category3.Category2SysNo",
DbType.Int32,
"@C2SysNo",
QueryConditionOperatorType.Equal,
filter.Category2SysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"category3.Category3SysNo",
DbType.Int32,
"@C3SysNo",
QueryConditionOperatorType.Equal,
filter.Category3SysNo
);
//销售方
if (filter.SellersType != null)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.InvoiceType",
DbType.AnsiStringFixedLength,
"@InvoiceType",
QueryConditionOperatorType.Equal,
filter.SellersType
);
}
//是否已打印标签
if (filter.IsLabelPrinted.HasValue)
{
string isLabelPrinted = string.Empty;
if (filter.IsLabelPrinted == false)
{
isLabelPrinted = "N";
}
else if (filter.IsLabelPrinted == true)
{
isLabelPrinted = "Y";
}
sqlBuilder.ConditionConstructor.AddCondition
(
QueryConditionRelationType.AND,
"request.IsLabelPrinted",
DbType.String,
"@IsLabelPrinted",
QueryConditionOperatorType.Equal,
isLabelPrinted
);
}
//商家处理结果
sqlBuilder.ConditionConstructor.AddCondition
(
QueryConditionRelationType.AND,
"register.SellerOperationInfo",
DbType.String,
"@SellerOperationInfo",
QueryConditionOperatorType.Equal,
filter.SellerOperationInfo
);
if (filter.IsRepeatRegister.HasValue)
{
if (filter.IsRepeatRegister.Value)
{
QueryConditionOperatorType qcOperator = QueryConditionOperatorType.MoreThan;
if (filter.CompareSymbol == CompareSymbolType.Equal)
{
qcOperator = QueryConditionOperatorType.Equal;
}
else if (filter.CompareSymbol == CompareSymbolType.LessThan)
{
qcOperator = QueryConditionOperatorType.LessThan;
}
else if (filter.CompareSymbol == CompareSymbolType.MoreThan)
{
qcOperator = QueryConditionOperatorType.MoreThan;
}
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"Repeat.RepeatProductCount",
DbType.Int32,
"@RepeatProductCount",
qcOperator,
filter.ProductCount);
}
else
{
string feedBackCondition1 = string.Empty;
string feedBackCondition2 = string.Empty;
feedBackCondition1 = @"SELECT 1
FROM [OverseaServiceManagement].[dbo].[RepeatRegister] WITH(NOLOCK)
WHERE SOSysNo = request.SOSysNo
AND [ProductSysNo] = register.ProductSysNo
AND [CurrentCount] = 1" ;
feedBackCondition2 = @"SELECT 1
FROM [OverseaServiceManagement].[dbo].[RepeatRegister] WITH(NOLOCK)
WHERE SOSysNo = request.SOSysNo
AND [ProductSysNo] = register.ProductSysNo" ;
using (GroupCondition group = new GroupCondition(sqlBuilder, QueryConditionRelationType.AND))
{
sqlBuilder.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
null,
QueryConditionOperatorType.Exist,
feedBackCondition1
);
sqlBuilder.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.OR,
null,
QueryConditionOperatorType.NotExist,
feedBackCondition2
);
}
}
}
if (!string.IsNullOrEmpty(filter.NextHandlerList) && filter.NextHandler == RMANextHandler.Dun)
{
string feedBackCondition = string.Empty;
feedBackCondition = "SELECT 1 FROM dbo.RMA_Register_Dunlog Dunlog WITH(NOLOCK) WHERE Dunlog.RegisterSysNo = register.SysNo"
+ " AND Dunlog.FeedBack IN (" + filter.NextHandlerList + ")";
ConditionConstructor subCondition = sqlBuilder.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
null,
QueryConditionOperatorType.Exist,
feedBackCondition
);
}
#endregion
#region DateTime conditions
if (filter.IsUnReceive.HasValue && filter.IsUnReceive.Value)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(
QueryConditionRelationType.AND, "request.RecvTime", QueryConditionOperatorType.IsNull
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.RecvTime",
DbType.DateTime,
"@RecvTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.RecvTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"request.RecvTime",
DbType.DateTime,
"@RecvTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.RecvTimeTo
);
}
if (filter.IsUnReturn.HasValue && filter.IsUnReturn.Value)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(
QueryConditionRelationType.AND, "rmareturn.ReturnTime", QueryConditionOperatorType.IsNull
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rmareturn.ReturnTime",
DbType.DateTime,
"@ReturnTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.ReturnTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rmareturn.ReturnTime",
DbType.DateTime,
"@ReturnTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.ReturnTimeTo
);
}
if (filter.IsUnRefund.HasValue && filter.IsUnRefund.Value)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(
QueryConditionRelationType.AND, "refund.RefundTime", QueryConditionOperatorType.IsNull
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"refund.RefundTime",
DbType.DateTime,
"@RefundTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.RefundTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"refund.RefundTime",
DbType.DateTime,
"@RefundTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.RefundTimeTo
);
}
if (filter.IsUnResponse.HasValue && filter.IsUnResponse.Value)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(
QueryConditionRelationType.AND, "register.ResponseTime", QueryConditionOperatorType.IsNull
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.ResponseTime",
DbType.DateTime,
"@ResponseTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.ResponseTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.ResponseTime",
DbType.DateTime,
"@ResponseTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.ResponseTimeTo
);
}
if (filter.IsUnOutbound.HasValue && filter.IsUnOutbound.Value)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(
QueryConditionRelationType.AND, "outbound.OutTime", QueryConditionOperatorType.IsNull
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"outbound.OutTime",
DbType.DateTime,
"@OutBoundTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.OutboundTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"outbound.OutTime",
DbType.DateTime,
"@OutBoundTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.OutboundTimeTo
);
}
if (filter.IsUnCheck.HasValue && filter.IsUnCheck.Value)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(
QueryConditionRelationType.AND, "register.CheckTime", QueryConditionOperatorType.IsNull
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.CheckTime",
DbType.DateTime,
"@CheckTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.CheckTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.CheckTime",
DbType.DateTime,
"@CheckTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.CheckTimeTo
);
}
if (filter.IsUnRevert.HasValue && filter.IsUnRevert.Value)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(
QueryConditionRelationType.AND, "rmarevert.OutTime", QueryConditionOperatorType.IsNull
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rmarevert.OutTime",
DbType.DateTime,
"@RevertTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.RevertTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"rmarevert.OutTime",
DbType.DateTime,
"@RevertTimeTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.RevertTimeTo
);
}
#endregion
#region VIP condition
if (filter.IsVIP.HasValue)
{
using (GroupCondition group = new GroupCondition(sqlBuilder, QueryConditionRelationType.AND))
{
if (filter.IsVIP == true)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"customer.VIPRank",
DbType.Int32,
"@AutoVIP",
QueryConditionOperatorType.Equal,
2 // VIPRank.AutoVIP
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.OR,
"customer.VIPRank",
DbType.Int32,
"@ManualVIP",
QueryConditionOperatorType.Equal,
4 // VIPRank.ManualVIP
);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"customer.VIPRank",
DbType.Int32,
"@AutoNonVIP",
QueryConditionOperatorType.Equal,
1 // VIPRank.AutoNonVIP
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.OR,
"customer.VIPRank",
DbType.Int32,
"@ManualNonVIP",
QueryConditionOperatorType.Equal,
3 // VIPRank.ManualNonVIP
);
}
}
}
#endregion
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.CompanyCode",
DbType.String,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode
);
#endregion
cmd.CommandText = MakeSqlWithCondition(filter, sqlBuilder.BuildQuerySql());
EnumColumnList enumList = new EnumColumnList();
enumList.Add("OutBoundStatus", typeof(RMAOutBoundStatus));
enumList.Add("RevertStatus", typeof(RMARevertStatus));
enumList.Add("RefundStatus", typeof(RMARefundStatus));
enumList.Add("ReturnStatus", typeof(RMAReturnStatus));
enumList.Add("NewProductStatus", typeof(RMANewProductStatus));
enumList.Add("Status", typeof(RMARequestStatus));
enumList.Add("InvoiceType", typeof(SellersType));
CodeNamePairColumnList codeNameList = new CodeNamePairColumnList();
codeNameList.Add("RMAReason", "RMA", "RMAReason");
codeNameList.Add("SellerOperationInfo", "RMA", "SellerOperationInfo");
var dt = cmd.ExecuteDataTable(enumList, codeNameList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
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);
}
}
/// <summary>
/// 查询VendorProdcut
/// </summary>
/// <returns></returns>
public DataTable VendorProductQuery(VendorProductQueryFilter filter, out int TotalCount)
{
CustomDataCommand command = DataCommandManager.
CreateCustomDataCommandFromConfig("External_Query_VendorProduct");
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(command.CommandText, command, HelpDA.ToPagingInfo(filter.PagingInfo), "a.SysNo DESC"))
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.ProductType", DbType.Int32,
"@ProductType", QueryConditionOperatorType.Equal,
0);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Status", DbType.Int32,
"@Status", QueryConditionOperatorType.MoreThanOrEqual,
0);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.ManufacturerSysNo", DbType.Int32,
"@ManufacturerSysNo", QueryConditionOperatorType.Equal,
filter.VendorManufacturerSysNo);
command.AddInputParameter("@UserSysNo", DbType.Int32, filter.UserSysNo);
if (filter.C3SysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.C3SysNo", DbType.Int32,
"@C3SysNo", QueryConditionOperatorType.Equal,
filter.C3SysNo);
}
else
{
if (filter.C2SysNo.HasValue)
{
ConditionConstructor subQueryBuilder = sb.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
"a.C3SysNo",
QueryConditionOperatorType.In,
@"SELECT [Category3Sysno]
FROM [OverseaContentManagement].[dbo].[V_CM_CategoryInfo]
WHERE [Category2Sysno]=@C2SysNo");
command.AddInputParameter("@C2SysNo", DbType.Int32, filter.C2SysNo);
}
}
if (!filter.IsAuto)
{
QueryConditionOperatorType ot = QueryConditionOperatorType.Exist;
if (!filter.IsMapping)
{
ot = QueryConditionOperatorType.NotExist;
}
sb.ConditionConstructor.AddSubQueryCondition(
QueryConditionRelationType.AND,
null,
ot,
@"SELECT TOP 1 1
FROM [IPP3].[dbo].VendorUser_ProductMapping
WHERE
ProductSysNo=a.sysno
AND UserSysNo=@UserSysNo
AND VendorExSysNo=@VendorExSysNo");
}
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.MerchantSysNo",
DbType.Int32,
"@MerchantSysNo",
QueryConditionOperatorType.Equal, filter.VendorSysNo);
command.AddInputParameter(
"@VendorManufacturerSysNo",
DbType.Int32,
filter.ManufacturerSysNo);
command.CommandText = sb.BuildQuerySql();
DataTable dt = command.ExecuteDataTable();
TotalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable RoleQuery(VendorRoleQueryFilter filter, out int dataCount)
{
if (filter.PagingInfo != null && filter.PagingInfo.SortBy != null)
{
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";
#region switch
switch (sortColumn)
{
case "SysNo":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "SysNo", sortType);
break;
case "RoleName":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "RoleName", sortType);
break;
case "Status":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "Status", sortType);
break;
case "InUser":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "InUser", sortType);
break;
case "InDate":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "InDate", sortType);
break;
case "EditUser":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "EditUser", sortType);
break;
case "EditDate":
filter.PagingInfo.SortBy =
String.Format("{0} {1}", "EditDate", sortType);
break;
}
#endregion
}
}
CustomDataCommand command = DataCommandManager.
CreateCustomDataCommandFromConfig("ExternalSYS_Query_VendorRole");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, HelpDA.ToPagingInfo(filter.PagingInfo), "SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.Equal,
filter.RoleSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"RoleName", DbType.String,
"@RoleName", QueryConditionOperatorType.LeftLike,
filter.RoleName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"Status", DbType.AnsiStringFixedLength,
"@Status", QueryConditionOperatorType.Equal,
filter.Status);
if (!string.IsNullOrEmpty(filter.PrivilegeName))
{
ConditionConstructor subQueryBuilder = builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist,
@"SELECT 1
FROM (
SELECT
[RoleSysNo]
FROM [IPP3].[dbo].[VendorUser_Role_Privilege] WITH(NOLOCK)
WHERE
[PrivilegeSysNo] IN (
SELECT
[SysNo]
FROM [IPP3].[dbo].[VendorUser_Privilege] WITH(NOLOCK)
WHERE
[Memo] LIKE @PrivilegeName
)
) AS RESULT
WHERE
RESULT.[RoleSysNo]=[VendorUser_Role].[SysNo]");
command.AddInputParameter("@PrivilegeName", DbType.String,
filter.PrivilegeName + "%");
}
command.CommandText = builder.BuildQuerySql();
var dt = command.ExecuteDataTable("Status", typeof(ValidStatus));
dataCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}