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);
}
}
/// <summary>
/// 查询礼品卡制作
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryGiftCardFabricationMaster(GiftCardFabricationFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
if (filter.PageInfo == null)
{
pagingEntity = null;
}
else
{
pagingEntity.SortField = filter.PageInfo.SortBy;
pagingEntity.MaximumRows = filter.PageInfo.PageSize;
pagingEntity.StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize;
}
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GiftCard_QueryGiftCardFabricationMaster");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "GG.GiftCardFabricationSysNo DESC"))
{
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "B.ReferenceSOSysNo", DbType.Int32, "@ReferenceSOSysNo", QueryConditionOperatorType.Equal, filter.ReferenceSOSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "GM.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "GM.Title", DbType.String, "@Title", QueryConditionOperatorType.Like, filter.Title);
if (filter.Status.HasValue)
{
if (filter.Status.Value == -2)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "PV.Status IN (4,6,7,8)");
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "GM.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.Status);
}
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "GM.POSysNo", DbType.Int32, "@POSysNo", QueryConditionOperatorType.Like, filter.POSysNo);
if (filter.InDateTo != null)
{
filter.InDateTo = (filter.InDateTo == filter.InDateFrom ? filter.InDateTo.Value.AddDays(1) : filter.InDateTo);
}
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "GM.InDate", DbType.DateTime, "@InDate", QueryConditionOperatorType.LessThanOrEqual, QueryConditionOperatorType.MoreThanOrEqual, filter.InDateTo, filter.InDateFrom);
if (filter.Status.HasValue)
{
cmd.CommandText = sqlBuilder.BuildQuerySql().Replace("#StrWhere2#", " where GG.Status=" + filter.Status.Value).Replace("#StrWhere3#", " where GG.Status=" + filter.Status.Value + " ");
}
else
{
cmd.CommandText = sqlBuilder.BuildQuerySql().Replace("#StrWhere2#", "").Replace("#StrWhere3#", "");
}
EnumColumnList enumList = new EnumColumnList();
enumList.Add("POStatus", typeof(ECCentral.BizEntity.PO.PurchaseOrderStatus));//前台展示状态
//enumList.Add("CardType", typeof(ECCentral.BizEntity.IM.GiftCardType));
CodeNamePairColumnList pairList = new CodeNamePairColumnList();
pairList.Add("Status", "IM", "GiftCardFabricationStatus"); //
var dt = cmd.ExecuteDataTable(enumList, pairList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public System.Data.DataTable QueryConsignSettlement(QueryFilter.PO.ConsignSettleQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryVendorSettle");
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> {
29, 30, 31
});
}
else if (queryFilter.IsAutoSettle == AutoSettleStatus.Hand.ToString())
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Vendor.PayPeriodType", DbType.Int32,
"@PayPeriodType", QueryConditionOperatorType.Equal, 12);
}
if (!(queryFilter.IsManagerPM ?? false))
{
string sqlStr = @"Select
ProductLineSysNo
FROM OverseaContentManagement.dbo.V_CM_ProductLine_PMs AS p " +
"WHERE PMUserSysNo=" + ServiceContext.Current.UserSysNo + " OR CHARINDEX(';'+CAST(" + ServiceContext.Current.UserSysNo + " AS VARCHAR(20))+';',';'+p.BackupPMSysNoList+';')>0";
builder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "Settle.ProductLineSysNo", QueryConditionOperatorType.In, sqlStr);
}
if (queryFilter.IsLease.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.IsToLease", DbType.AnsiStringFixedLength,
"@IsToLease", QueryConditionOperatorType.Equal, (int)queryFilter.IsLease.Value);
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.PMSysno", DbType.AnsiStringFixedLength,
"@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, queryFilter.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, (null == queryFilter.Status ? (int?)null : (int)queryFilter.Status.Value));
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.Memo", DbType.String,
"@Memo", QueryConditionOperatorType.Like, queryFilter.Memo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.ConsignRange", DbType.String,
"@ConsignRange", QueryConditionOperatorType.Equal, queryFilter.ConsignRange);
}
////TODO:取有权限的PMList SysNo集合
//builder.ConditionConstructor.AddInCondition(
// QueryConditionRelationType.AND, "Settle.PMSysno", DbType.Int32, queryFilter.AccessiblePMSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Settle.CompanyCode", DbType.AnsiStringFixedLength,
"@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
command.CommandText = builder.BuildQuerySql();
EnumColumnList colList = new EnumColumnList
{
{ 21, typeof(SettleStatus) },
{ 25, typeof(VendorConsignFlag) },
{ 28, typeof(PaySettleCompany) }
};
CodeNamePairColumnList codeNameColList = new CodeNamePairColumnList();
codeNameColList.Add("PayPeriodType", "Invoice", "VendorPayPeriod");
//colList.Add("PayPeriodType", typeof(VendorPayPeriodType));
dt = command.ExecuteDataTable(colList, codeNameColList);
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
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());
}
}
}
return(dt);
}
/// <summary>
/// 查询公告及促销评论
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryNewsAdvReply(NewsAdvReplyQueryFilter 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("News_QueryNewsAdvReplyList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "N.SysNo DESC"))
{
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "N.CreateDate", DbType.DateTime, "@CreateDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThanOrEqual, filter.InDateFrom, filter.InDateTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "N.CustomerSysNo", DbType.Int32, "@CustomerSysNo", QueryConditionOperatorType.Equal, filter.CustomerSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "N.LastEditUserSysNo", DbType.Int32, "@LastEditUserSysNo", QueryConditionOperatorType.Like, filter.LastEditUserSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "N.ReferenceType", DbType.Int32, "@ReferenceType", QueryConditionOperatorType.Equal, filter.ReferenceType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "N.ReferenceSysNo", DbType.Int32, "@ReferenceSysNo", QueryConditionOperatorType.Equal, filter.ReferenceSysNo);
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CI.LastEditUserID", DbType.String, "@LastEditUserID", QueryConditionOperatorType.Like, filter.LastEditUserID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SI.DisplayName", DbType.String, "@LastEditUserID", QueryConditionOperatorType.Like, filter.LastEditUserID);
if (filter.IsUploadImage != null)
{
if (filter.IsUploadImage == ECCentral.BizEntity.MKT.NYNStatus.Yes)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "N.Image is not null");
}
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "N.Image", DbType.Int32, "@Image", QueryConditionOperatorType.IsNotNull, "");
else
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, "N.Image is null");
}
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "N.[Image]", DbType.String, "@Image", filter.IsUploadImage == ECCentral.BizEntity.MKT.NYNStatus.Yes?QueryConditionOperatorType.IsNotNull:QueryConditionOperatorType.IsNull,);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "N.ReplyContent", DbType.String, "@ReplyContent", QueryConditionOperatorType.Like, filter.ReplyContent);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "N.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Like, filter.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "N.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "N.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("Status", typeof(ECCentral.BizEntity.MKT.NewsAdvReplyStatus)); //前台展示状态
enumList.Add("ReplyHasReplied", typeof(ECCentral.BizEntity.MKT.NYNStatus)); //是否存在回复
CodeNamePairColumnList pairList = new CodeNamePairColumnList();
pairList.Add("ReferenceType", "MKT", "CommentsCategory"); //ReferenceType评论类型
var dt = cmd.ExecuteDataTable(enumList, pairList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
//if (dt != null && dt.Rows.Count > 0)
//{
// foreach (DataRow dr in dt.Rows)
// {
// if (!string.IsNullOrEmpty(dr["Image"].ToString()))
// {
// dr["Image"] = "http://c3.neweggimages.com.cn/CImages/Promotion/P80/" + dr["Image"].ToString().Replace(",", ",http://c3.neweggimages.com.cn/CImages/Promotion/P80/");
// dr["LinkImage"] = "http://c3.neweggimages.com.cn/CImages/Promotion/Original/" + dr["Image"].ToString().Replace(",", ",http://c3.neweggimages.com.cn/CImages/Promotion/Original/");
// }
// }
//}
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 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);
}
}
/// <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 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);
}
}
public virtual DataTable Query(QueryFilter.Customer.RefundRequestQueryFilter queryCriteria, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = queryCriteria.PagingInfo.SortBy;
pagingEntity.MaximumRows = queryCriteria.PagingInfo.PageSize;
pagingEntity.StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize;
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryRefundRequest");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, " R.SysNo DESC "))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.RequestType",
DbType.String, "@RequestType",
QueryConditionOperatorType.Equal,
queryCriteria.RequestType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.SysNo",
DbType.Int32, "@SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.SOSysNo",
DbType.Int32, "@SOSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.SOSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.Status",
DbType.String, "@Status",
QueryConditionOperatorType.Equal,
queryCriteria.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.RefundType",
DbType.String, "@RefundType",
QueryConditionOperatorType.Equal,
queryCriteria.RefundType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.CustomerID",
DbType.String, "@CustomerID",
QueryConditionOperatorType.Equal,
queryCriteria.CustomerId);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.EditUser",
DbType.String, "@EditUser",
QueryConditionOperatorType.Equal,
queryCriteria.EditUserName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.InDate",
DbType.DateTime, "@DateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
queryCriteria.CreateFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.InDate",
DbType.DateTime, "@DateTo",
QueryConditionOperatorType.LessThan,
queryCriteria.CreateTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.EditDate",
DbType.DateTime, "@EditDateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
queryCriteria.EditFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"R.EditDate",
DbType.DateTime, "@EditDateTo",
QueryConditionOperatorType.LessThan,
queryCriteria.EditTo);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"R.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
QueryConditionOperatorType.Equal,
queryCriteria.CompanyCode);
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add(4, typeof(RefundRequestType));
enumList.Add(12, typeof(RefundRequestStatus));
CodeNamePairColumnList cpList = new CodeNamePairColumnList();
cpList.Add(5, "Customer", "RefundType");
DataTable dt = cmd.ExecuteDataTable(enumList, cpList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
