/// <summary>
/// 查询公告
/// </summary>
/// <param name="filter">查询条件</param>
public static QueryResult<Notices> QueryNotices(NoticesQueryFilter filter)
{
QueryResult<Notices> result = new QueryResult<Notices>();
PagingInfoEntity page = new PagingInfoEntity();
page.SortField = (filter.SortList == null || filter.SortList.Count == 0) ? null : filter.SortListToString();
page.MaximumRows = filter.PageSize;
page.StartRowIndex = (filter.PageIndex - 1) * filter.PageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryNotices");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo", DbType.Int32, "@SysNo",
QueryConditionOperatorType.Equal, filter.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Contents", DbType.String, "@Contents",
QueryConditionOperatorType.Like, filter.Contents);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32, "@Status",
QueryConditionOperatorType.Equal, filter.Status);
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.ResultList = cmd.ExecuteEntityList<Notices>();
int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
result.PagingInfo = new PagingInfo() { PageIndex = filter.PageIndex, PageSize = filter.PageSize, TotalCount = totalCount };
return result;
}
}
public static PagingInfoEntity ToPagingInfo(FilterBase filter)
{
PagingInfoEntity page = new PagingInfoEntity();
page.MaximumRows = filter.PageSize;
page.StartRowIndex = filter.ServicePageIndex * filter.PageSize;
return page;
}
public DataTable Query(HotSaleCategoryQueryFilter query, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = query.PageInfo.SortBy;
pagingEntity.MaximumRows = query.PageInfo.PageSize;
pagingEntity.StartRowIndex = query.PageInfo.PageIndex * query.PageInfo.PageSize;
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("WebSite_QueryHotSaleCategory");
var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "h.TransactionNumber desc");
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "h.Status",
DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal,
query.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "h.Position",
DbType.Int32, "@Position", QueryConditionOperatorType.Equal,
query.Position);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "h.GroupName",
DbType.String, "@GroupName", QueryConditionOperatorType.Like,
query.GroupName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "h.PageType",
DbType.Int32, "@PageType", QueryConditionOperatorType.Equal,
query.PageType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "h.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal,
query.CompanyCode);
//TODO:添加ChannelID参数
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumConfig = new EnumColumnList();
enumConfig.Add("Status", typeof(ADStatus));
var dt = cmd.ExecuteDataTable(enumConfig);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
/// <summary>
/// 查询用户
/// </summary>
/// <param name="pageIndex">取第几页</param>
/// <param name="pageSize">每页显示几条</param>
/// <param name="userID">用户登录名</param>
/// <param name="status">状态</param>
/// <param name="payUserBeginTime">充值使用时间起</param>
/// <param name="payUseEndTime">充值使用时间止</param>
/// <returns></returns>
public static PageList<List<User>> QueryUser(int pageIndex, int pageSize, string userID, int status, DateTime payUserBeginTime, DateTime payUseEndTime)
{
PagingInfoEntity page = new PagingInfoEntity();
page.MaximumRows = pageSize;
page.StartRowIndex = pageIndex * pageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Users_Query");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "UserID", DbType.String, "@UserID", QueryConditionOperatorType.Like, userID);
if (status >= 0)
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PayUseBeginTime", DbType.DateTime, "@PayUseBeginTime", QueryConditionOperatorType.MoreThanOrEqual, payUserBeginTime);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PayUseEndTime", DbType.DateTime, "@PayUseEndTime", QueryConditionOperatorType.LessThanOrEqual, payUseEndTime);
cmd.CommandText = sqlBuilder.BuildQuerySql();
List<User> result = cmd.ExecuteEntityList<User>();
int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return new PageList<List<User>>(result, pageIndex, pageSize, totalCount);
}
}
/// <summary>
/// 查询商品批次信息
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public virtual DataTable QueryProductBatch(ProductBatchQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = "batch.BatchNumber";
pagingEntity.MaximumRows = filter.PagingInfo.PageSize;
pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetProductBatchInfoByProductAndStock");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "batch.BatchNumber"))
{
if (filter.ProductSysNo.HasValue && filter.ProductSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "p.SysNo"
, DbType.Int32
, "@ProductSysNo"
, QueryConditionOperatorType.Equal
, filter.ProductSysNo);
}
if (filter.StockSysNo.HasValue && filter.StockSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "bStock.StockSysNo"
, DbType.Int32
, "@StockSysNo"
, QueryConditionOperatorType.Equal
, filter.StockSysNo);
}
cmd.CommandText = sqlBuilder.BuildQuerySql();
var result = cmd.ExecuteDataTable();
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(result);
}
}
/// <summary>
/// 查询礼品券关联请求
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryGiftVoucherProductRelationReq(GiftCardProductFilter 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_GetVoucherRelationRequestByRelationPaging");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "GP.SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "GP.AuditStatus", DbType.AnsiStringFixedLength,
"@AuditStatus", QueryConditionOperatorType.Equal, "W");
if (filter.SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "GV.GiftVoucherSysNo"
, DbType.Int32, "@GiftVoucherSysNo", QueryConditionOperatorType.Equal, filter.SysNo);
}
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("AuditStatus", typeof(ECCentral.BizEntity.IM.GVRReqAuditStatus));
var dt = cmd.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 查询热门关键字
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryHotKeywords(HotKeywordsQueryFilter 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("Keyword_QueryHotKeywordsList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "A.SysNo DESC"))
{
//TODO:添加渠道查询条件
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sk.PageType", DbType.Int32, "@PageType", QueryConditionOperatorType.Equal, filter.PageType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sk.PageID", DbType.Int32, "@PageID", QueryConditionOperatorType.Equal, filter.PageID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sk.Keyword", DbType.String, "@Keyword", QueryConditionOperatorType.Like, filter.Keywords);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sk.Priority", DbType.Int32, "@Priority", QueryConditionOperatorType.Equal, filter.Priority);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sk.EditUserSysNo", DbType.Int32, "@EditUserSysNo", QueryConditionOperatorType.Equal, filter.EditUserSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sk.IsOnlineShow", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.IsOnlineShow);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sk.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "sk.EditDate", DbType.DateTime, "@EditDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThan, filter.EditDateFrom, filter.EditDateTo);
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "sk.HiddenDate", DbType.DateTime, "@HiddenDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThan, filter.InvalidDateFrom, filter.InvalidDateTo);
cmd.CommandText = sqlBuilder.BuildQuerySql();
var dt = cmd.ExecuteDataTable <ECCentral.BizEntity.MKT.NYNStatus>("IsOnlineShow");
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public virtual DataTable GetRMARegisterList(RMARegisterQueryFilter 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 = DataCommandManager.CreateCustomDataCommandFromConfig("GetRMARegisterList");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"B.RequestSysNo",
DbType.Int32,
"@RequestSysNo ",
QueryConditionOperatorType.Equal,
filter.RequestSysNo);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"A.ProductSysNo",
DbType.Int32,
"@ProductSysNo",
QueryConditionOperatorType.Equal,
filter.ProductSysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"A.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode);
cmd.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = cmd.ExecuteDataTable();
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 查询默认关键字
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryDefaultKeywords(DefaultKeywordsQueryFilter 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("Keyword_QueryDefaultKeywordsList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "TransactionNumber DESC"))
{
//TODO:添加渠道查询条件
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "DefaultKeyword", DbType.String, "@DefaultKeyword", QueryConditionOperatorType.Like, filter.Keywords);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PageType", DbType.Int32, "@PageType", QueryConditionOperatorType.Equal, filter.PageType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PageID", DbType.Int32, "@PageID", QueryConditionOperatorType.Equal, filter.PageID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, filter.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "BeginDate", DbType.DateTime, "@BeginDate",
QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThan, filter.BeginDateFrom, filter.BeginDateTo);
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "EndDate", DbType.DateTime, "@EndDate",
QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThan, filter.EndDateFrom, filter.EndDateTo);
cmd.CommandText = sqlBuilder.BuildQuerySql();
var dt = cmd.ExecuteDataTable <ADStatus>("Status");
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public virtual DataTable Query(FPCheckQueryFilter 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("QueryFPCheckMaster");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, " a.sysno ASC "))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
QueryConditionOperatorType.Equal, "8601");
cmd.CommandText = sqlBuilder.BuildQuerySql();
cmd.SetParameterValue("@ChannelID", queryCriteria.ChannelID);
DataTable dt = cmd.ExecuteDataTable();
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable QuerySecondDomain(SecondDomainQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = filter.PageInfo.SortBy,
StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize,
MaximumRows = filter.PageInfo.PageSize
};
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QuerySecondDomain");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingInfo, "v.[SysNo] DESC"))
{
builder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "v.SysNo", DbType.Int32, filter.VendorSysNoList);
command.CommandText = builder.BuildQuerySql();
DataTable dt = command.ExecuteDataTable();
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 用户流失分析报表
/// </summary>
/// <param name="filter">条件</param>
/// <returns></returns>
public static StatementResult <ArrayList> CustomersLoseAnalyze(CustomersLoseAnalyzeFilter filter)
{
StatementResult <ArrayList> result = new StatementResult <ArrayList>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("CustomersLoseAnalyze");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "LogDate ASC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "LogDate", DbType.DateTime,
"@LogDate", QueryConditionOperatorType.LessThan, DateTime.Parse(DateTime.Now.AddDays(-1 * filter.Day).ToShortDateString()));
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.Result = DbHelper.DatatableConvertArrayList(cmd.ExecuteDataTable());
result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(result);
}
}
/// <summary>
/// 查询自动匹配关键字
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QuerySearchedKeywords(SearchedKeywordsFilter 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("Keyword_QuerySearchKeyword");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC"))
{
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "EditDate", DbType.DateTime, "@EditDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThanOrEqual, filter.InDateFrom, filter.InDateTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Keywords", DbType.String, "@Keywords", QueryConditionOperatorType.Like, filter.Keywords);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CreateUserType", DbType.Int32, "@CreateUserType", QueryConditionOperatorType.Like, filter.CreateUserType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.String, "@Status", QueryConditionOperatorType.Equal, filter.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "EditUser", DbType.String, "@EditUser", QueryConditionOperatorType.Equal, filter.EditUser);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
cmd.CommandText = sqlBuilder.BuildQuerySql();
var dt = cmd.ExecuteDataTable <ECCentral.BizEntity.MKT.ADStatus>("Status");
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
private DataTable GetVendorSettleCanbePayList(CanBePayOrderQueryFilter filter, out int totalCount)
{
DataTable result = null;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetVendorSettleCanBePayList");
PagingInfoEntity pagingInfo = CreatePagingInfo(filter);
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingInfo, "vendorSettle.SysNo desc"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vendorsettle.SettleID",
DbType.String, "@SettleID", QueryConditionOperatorType.Like, filter.OrderID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vendorsettle.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.VendorSettleStatus);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vendorsettle.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
result = ExecuteDataTable(dataCommand, out totalCount);
}
return(result);
}
public DataTable QueryPurchaseOrderHistory(PurchaseOrderQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
CustomDataCommand command = command = DataCommandManager.CreateCustomDataCommandFromConfig("POhistory");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingInfo, "intime desc"))
{
if (!string.IsNullOrEmpty(queryFilter.ProductSysNo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po_item.ProductSysNo",
DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo);
}
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "po_master.Status", QueryConditionOperatorType.In,
"4,6,7,8");
if (!string.IsNullOrEmpty(queryFilter.StockSysNo) && queryFilter.StockSysNo != "-999999")
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po_master.StockSysNo",
DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "po_master.CompanyCode",
DbType.Int32, "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
command.CommandText = sqlBuilder.BuildQuerySql();
dt = command.ExecuteDataTable();
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
}
return(dt);
}
/// <summary>
/// 优惠券发放记录查询
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryCouponCodeCustomerLog(CouponCodeCustomerLogFilter 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("QueryCouponCodeCustomerLog");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "Coupon.InDate desc"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Coupon.CouponName", DbType.String,
"@CouponName", QueryConditionOperatorType.Like, filter.CouponName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Customer.SysNo", DbType.String,
"@CustomerSysNo", QueryConditionOperatorType.Equal, filter.CustomerSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Customer.CustomerID", DbType.String,
"@CustomerID", QueryConditionOperatorType.Equal, filter.CustomerID);
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "Coupon.SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThanOrEqual,
filter.CouponBeginNo, filter.CouponEndNo);
sqlBuilder.ConditionConstructor.AddBetweenCondition(QueryConditionRelationType.AND, "CustomerLog.GetCouponCodeDate", DbType.DateTime,
"@GetCouponCodeDate", QueryConditionOperatorType.MoreThanOrEqual, QueryConditionOperatorType.LessThan,
filter.BeginUseDate, filter.EndUseDate);
cmd.CommandText = sqlBuilder.BuildQuerySql();
var dt = cmd.ExecuteDataTable(new EnumColumnList {
{ "Status", typeof(CouponsStatus) }
});
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 查询移仓单
/// </summary>
/// <param name="queryCriteria"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public virtual DataTable QueryShiftRequest(ShiftRequestQueryFilter queryCriteria, out int totalCount)
{
PagingInfoEntity pagingEntity = PageInfoToEntity(queryCriteria.PagingInfo);
pagingEntity.SortField = ShiftRequestSortFieldMapping(pagingEntity.SortField);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryShiftRequest");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "a.SysNo DESC"))
{
//Build Query Condition
BuildeCondition(queryCriteria, pagingEntity, cmd, sqlBuilder);
EnumColumnList enumColumnList = new EnumColumnList();
enumColumnList.Add("RequestStatus", typeof(ShiftRequestStatus));
enumColumnList.Add("SOStatus", typeof(ECCentral.BizEntity.SO.SOStatus));
enumColumnList.Add("SpecialShiftType", typeof(SpecialShiftRequestType));
var resultData = cmd.ExecuteDataTable(enumColumnList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(resultData);
}
}
public DataTable RefundAdjustQuery(RefundAdjustQueryFilter filter, out int totalCount)
{
MapSortField(filter);
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.PagingInfo.SortBy;
pagingEntity.MaximumRows = filter.PagingInfo.PageSize;
pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Customer_Query_RefundAdjust");
using (var sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "R.[SysNo] DESC"))
{
AddRefundAdjustParameters(filter, cmd, sb);
//EnumColumnList colList = new EnumColumnList();
//colList.Add("AdjustType", typeof(RefundAdjustType));
//colList.Add("RefundPayType", typeof(RefundPayType));
DataTable dt = cmd.ExecuteDataTable();
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable Query(RefundBalanceQueryFilter queryFilter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = queryFilter.PagingInfo.SortBy;
pagingEntity.MaximumRows = queryFilter.PagingInfo.PageSize;
pagingEntity.StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize;
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryRefundBalance");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, null, "RB.[SysNo] DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RB.[OrgRefundSysNo]", DbType.Int32, "@RefundSysNo", QueryConditionOperatorType.Equal, queryFilter.RefundSysNo);
cmd.CommandText = sqlBuilder.BuildQuerySql();
cmd.ReplaceParameterValue("#OrderType#", SOIncomeOrderType.RO_Balance);
EnumColumnList enumList = new EnumColumnList();
enumList.Add("Status", typeof(RefundBalanceStatus));
enumList.Add("AuditStatus", typeof(RefundStatus));
DataTable dt = cmd.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// SAP供应商查询
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public virtual DataTable QueryVendor(SAPVendorQueryFilter query, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = query.PagingInfo.SortBy;
pagingEntity.MaximumRows = query.PagingInfo.PageSize;
pagingEntity.StartRowIndex = query.PagingInfo.PageIndex * query.PagingInfo.PageSize;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QuerySAPVendor");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingEntity, "SV.VendorSysNo desc"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SV.VendorSysNo",
DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, query.VendorSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SV.CompanyCode",
DbType.StringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, query.CompanyCode);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = dataCommand.ExecuteDataTable();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
public virtual DataTable QueryCategory(CategoryQueryFilter queryCriteria, out int totalCount)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCategory");
var pagingInfo = new PagingInfoEntity
{
SortField = queryCriteria.PagingInfo.SortBy,
StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize,
MaximumRows = queryCriteria.PagingInfo.PageSize
};
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "SysNo DESC"))
{
if (!String.IsNullOrEmpty(queryCriteria.CategoryName))
{
dataCommand.AddInputParameter("@CategoryName", DbType.String, queryCriteria.CategoryName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C3Name",
DbType.String, "@CategoryName",
QueryConditionOperatorType.Like,
queryCriteria.CategoryName);
}
if (queryCriteria.Status != null)
{
dataCommand.AddInputParameter("@Status", DbType.String, queryCriteria.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"Status",
DbType.String, "@Status",
QueryConditionOperatorType.Equal,
queryCriteria.Status);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = dataCommand.ExecuteDataTable("CategoryStatus", typeof(CategoryStatus));
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable QueryVirtualRequestCloseLog(VirtualRequestQueryFilter queryFilter, out int totalCount)
{
if (queryFilter == null)
{
totalCount = 0;
return(null);
}
DataTable dt = new DataTable();
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PagingInfo.SortBy,
StartRowIndex = queryFilter.PagingInfo.PageIndex * queryFilter.PagingInfo.PageSize,
MaximumRows = queryFilter.PagingInfo.PageSize
};
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryVirtualInventoryCloseLog");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, " SysNo DESC"))
{
if (queryFilter.SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "VirtualRequestSysNo",
DbType.AnsiStringFixedLength, "@VirtualRequestSysNo", QueryConditionOperatorType.Equal, queryFilter.SysNo.Value.ToString());
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList list = new EnumColumnList();
list.Add("ActionType", typeof(VirtualRequestActionType));
list.Add("VirtualRequestStatus", typeof(VirtualRequestStatus));
dt = dataCommand.ExecuteDataTable(list);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
return(dt);
}
/// <summary>
/// 查询同义词
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryThesaurusKeywords(ThesaurusKeywordsQueryFilter 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("ThesaurusKeywords_QueryThesaurusKeywordsList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "TransactionNumber DESC"))
{
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "WordContent", DbType.String, "@Keywords", QueryConditionOperatorType.Like, filter.ThesaurusWords);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Type", DbType.String, "@Type", QueryConditionOperatorType.Equal, filter.Type);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.String, "@Status", QueryConditionOperatorType.Equal, filter.Status);
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("Status", typeof(ECCentral.BizEntity.MKT.ADTStatus));//前台展示状态
enumList.Add("Type", typeof(ECCentral.BizEntity.MKT.ThesaurusWordsType));
var dt = cmd.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
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);
}
}
/// <summary>
/// 查询中文词库
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QuerySegment(SegmentQueryFilter 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("Segment_GetSegmentList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC"))
{
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Keywords", DbType.String, "@Keywords", QueryConditionOperatorType.Like, filter.Keywords);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.String, "@Status", QueryConditionOperatorType.Equal, filter.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
cmd.CommandText = sqlBuilder.BuildQuerySql();
//CodeNamePairColumnList pairList = new CodeNamePairColumnList();
//pairList.Add("Status", "MKT", "KeywordsStatus");
//var dt = cmd.ExecuteDataTable(pairList);
var dt = cmd.ExecuteDataTable <ECCentral.BizEntity.MKT.KeywordsStatus>("Status");
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable Query(PageTypeQueryFilter 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("PageType_QueryPageType");
var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "A.SysNo DESC");
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"A.PageTypeName",
DbType.String,
"@PageTypeName",
QueryConditionOperatorType.Like,
filter.PageTypeName);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"A.CompanyCode",
DbType.AnsiStringFixedLength,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode);
//TODO:添加ChannelID参数
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumConfig = new EnumColumnList();
enumConfig.Add("Status", typeof(ADStatus));
var dt = cmd.ExecuteDataTable(enumConfig);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
/// <summary>
/// 发票打印查询
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable InvoicePrintAllQuery(InvoicePrintAllQueryFilter filter, out int totalCount)
{
DataTable result = null;
MapSortField4InvoicePrintAllQuery(filter);
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetInvoicePrintAll");
dataCommand.AddInputParameter("@NEG", DbType.AnsiStringFixedLength, InvoiceType.SELF);
dataCommand.AddInputParameter("@MET", DbType.AnsiStringFixedLength, InvoiceType.MET);
PagingInfoEntity pagingInfo = new PagingInfoEntity();
if (filter.PagingInfo != null)
{
pagingInfo.MaximumRows = filter.PagingInfo.PageSize;
pagingInfo.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
pagingInfo.SortField = filter.PagingInfo.SortBy;
}
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingInfo, "so.SysNo DESC"))
{
AddParameter4InvoicePrintAllQuery(filter, sqlBuilder);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumColumns = new EnumColumnList();
enumColumns.Add("SOStatus", typeof(ECCentral.BizEntity.SO.SOStatus));
CodeNamePairColumnList codeNamePairColunms = new CodeNamePairColumnList();
codeNamePairColunms.Add("InvoiceType", "Invoice", "SOIsVAT");
result = dataCommand.ExecuteDataTable(enumColumns, codeNamePairColunms);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
return(result);
}
public System.Data.DataTable QueryTopItem(QueryFilter.MKT.TopItemFilter 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("TopItemList_Query");
cmd.SetParameterValue("@FrontPageSize", filter.FrontPageSize);
cmd.SetParameterValue("@CategoryType", filter.PageType);
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "OrderPriority ASC,CreateTime DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.[Status]", DbType.String, "@Status", QueryConditionOperatorType.Equal, 1);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.[ProductType]", DbType.String, "@ProductType", QueryConditionOperatorType.Equal, 0);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.C1SysNo", DbType.String, "@C1SysNo", QueryConditionOperatorType.Equal, filter.C1SysNo);
if (filter.PageType.HasValue)
{
PageTypePresentationType pType = PageTypeUtil.ResolvePresentationType(ModuleType.TopItem, filter.PageType.Value.ToString());
if (pType == PageTypePresentationType.Category2)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.C2SysNo", DbType.String, "@C2SysNo", QueryConditionOperatorType.Equal, filter.RefSysNo);
}
else if (pType == PageTypePresentationType.Category3)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.C3SysNo", DbType.String, "@C3SysNo", QueryConditionOperatorType.Equal, filter.RefSysNo);
}
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.[ProductID]", DbType.String, "@ProductID", QueryConditionOperatorType.Equal, filter.ProductID);
cmd.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = cmd.ExecuteDataTable();
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public System.Data.DataTable QuerySaleGiftLog(SaleGiftLogQueryFilter 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("QuerySaleGiftLog");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "A.SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.ProductSysNo",
DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, filter.ProductSysNo);
cmd.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = cmd.ExecuteDataTable("IsOnlineShow", typeof(GiftIsOnlineShow));
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable QueryProductChannelInfo(ProductChannelInfoQueryFilter queryCriteria, out int totalCount)
{
var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductChannelInfo");
var pagingInfo = new PagingInfoEntity
{
SortField = queryCriteria.PagingInfo.SortBy,
StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize,
MaximumRows = queryCriteria.PagingInfo.PageSize
};
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "CP.SysNo DESC"))
{
if (queryCriteria.ChannelSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"CM.Sysno",
DbType.Int32, "@ChannelSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.ChannelSysNo);
}
if (queryCriteria.C1SysNo != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C1.Sysno",
DbType.Int32, "@C1SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.C1SysNo.Value);
}
if (queryCriteria.C2SysNo != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C2.Sysno",
DbType.Int32, "@C2SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.C2SysNo.Value);
}
if (queryCriteria.C3SysNo != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C3.Sysno",
DbType.Int32, "@C3SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.C3SysNo.Value);
}
if (queryCriteria.ProductSysNo != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.SysNo",
DbType.Int32, "@ProductSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.ProductSysNo.Value);
}
if (!string.IsNullOrEmpty(queryCriteria.ProductID))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.ProductID",
DbType.String, "@ProductID",
QueryConditionOperatorType.Equal,
queryCriteria.ProductID);
}
if (!string.IsNullOrEmpty(queryCriteria.Status))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"CP.Status",
DbType.String, "@Status",
QueryConditionOperatorType.Equal,
queryCriteria.Status);
}
//渠道商品ID
if (!string.IsNullOrEmpty(queryCriteria.ChannelProductID))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"CP.SynProductID",
DbType.String, "@ChannelProductID",
QueryConditionOperatorType.Like,
queryCriteria.ChannelProductID);
}
//taobaoSKU
if (!string.IsNullOrEmpty(queryCriteria.TaobaoSKU))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"CP.TaoBaoSku",
DbType.String, "@TaobaoSKU",
QueryConditionOperatorType.Like,
queryCriteria.TaobaoSKU);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var enumList = new EnumColumnList {
{ "Status", typeof(ProductChannelInfoStatus) }
, { "IsAppointInventory", typeof(BooleanEnum) }
, { "IsUsePromotionPrice", typeof(BooleanEnum) }
};
DataTable dt = dataCommand.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable Query(AuditRefundQueryFilter filter, out int totalCount)
{
DataTable result = null;
PagingInfoEntity pagingInfo = new PagingInfoEntity();
if (filter.PagingInfo != null)
{
MapSortField(filter.PagingInfo);
pagingInfo.MaximumRows = filter.PagingInfo.PageSize;
pagingInfo.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
pagingInfo.SortField = filter.PagingInfo.SortBy;
}
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetAuditRefundList");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingInfo, "SOIncomeBank.SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[SysNo]", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.Id);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[Status]", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.AuditStatus);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[HaveAutoRMA]", DbType.Int32, "@HaveAutoRMA", QueryConditionOperatorType.Equal, filter.ShipRejected);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[OrderType]", DbType.Int32, "@OrderType", QueryConditionOperatorType.Equal, filter.OrderType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[RefundPayType]", DbType.Int32, "@RefundType", QueryConditionOperatorType.Equal, filter.RefundPayType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[RefundReason]", DbType.Int32, "@RMAReason", QueryConditionOperatorType.Equal, filter.RMAReasonCode);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[OrderSysNo]", DbType.Int32, "@RMANumber", QueryConditionOperatorType.Equal, filter.RMANumber);
if (!string.IsNullOrEmpty(filter.OrderNumber))
{
List <int> OrderNumberList = new List <int>();
int[] OrderNumberArray = Array.ConvertAll <string, int>(filter.OrderNumber.Split('.'),
new Converter <string, int>((source) =>
{
return(Convert.ToInt32(string.IsNullOrEmpty(source) ? "0" : source));
}));
OrderNumberList.AddRange(OrderNumberArray);
sqlBuilder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND, "SOIncomeBank.SOSysNo", DbType.Int32, OrderNumberList);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[CreateTime]", DbType.DateTime, "@CreateTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.CreateTimeFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[CreateTime]", DbType.DateTime, "@CreateTimeTo", QueryConditionOperatorType.LessThanOrEqual, filter.CreateTimeTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[AuditTime]", DbType.DateTime, "@AuditTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, filter.AuditTimeFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[AuditTime]", DbType.DateTime, "@AuditTimeTo", QueryConditionOperatorType.LessThanOrEqual, filter.AuditTimeTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RMARefund.Status", DbType.Int32, "@RMAStatus", QueryConditionOperatorType.Equal, filter.RMAStatus);
if (filter.CashRelated)
{
using (GroupCondition group = new GroupCondition(sqlBuilder, QueryConditionRelationType.AND))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RMARefund.[CashFlag]", DbType.Int32, "@CashFlag", QueryConditionOperatorType.Equal, 0);
sqlBuilder.ConditionConstructor.AddNullCheckCondition(QueryConditionRelationType.OR, "RMARefund.[CashFlag]", QueryConditionOperatorType.IsNull);
}
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncome.[Status]", DbType.Int32, "@RefundStatus", QueryConditionOperatorType.Equal, filter.RefundStatus);
//为提高性能改为左匹配
if (!string.IsNullOrEmpty(filter.CustomerID))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, string.Format(" Customer.CustomerID like '{0}%'", filter.CustomerID));
}
switch (filter.OperationType)
{
case OperationSignType.LessThanOrEqual:
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.RefundCashAmt", DbType.Decimal, "@RefundAmount", QueryConditionOperatorType.LessThanOrEqual, filter.RefundAmount);
break;
case OperationSignType.Equal:
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.RefundCashAmt", DbType.Decimal, "@RefundAmount", QueryConditionOperatorType.Equal, filter.RefundAmount);
break;
case OperationSignType.MoreThanOrEqual:
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.RefundCashAmt", DbType.Decimal, "@RefundAmount", QueryConditionOperatorType.MoreThanOrEqual, filter.RefundAmount);
break;
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOIncomeBank.[CompanyCode]", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "NetPayExt.[Status]", DbType.Int32, "@WLTRefundStatus", QueryConditionOperatorType.Equal, filter.WLTRefundStatus);
if (!string.IsNullOrEmpty(filter.PayTypeSysNo))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOMaster.payTypeSysNo",
DbType.Int32, "@payTypeSysNo", QueryConditionOperatorType.Equal, Int32.Parse(filter.PayTypeSysNo));
}
if (filter.IsVAT.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SOMaster.IsVAT",
DbType.Int32, "@IsVAT", QueryConditionOperatorType.Equal, filter.IsVAT.Value);
}
if (filter.StockSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND,
@"exists (SELECT TOP 1 1 FROM OverseaOrderManagement.dbo.V_OM_SO_Item ITEM WITH(NOLOCK)
WHERE item.SOSysNo = SOMaster.SysNo
AND item.WarehouseNumber = @WareHouseNumber)");
cmd.AddInputParameter("@WareHouseNumber", DbType.Int32, filter.StockSysNo.Value);
}
cmd.CommandText = sqlBuilder.BuildQuerySql();
cmd.CommandTimeout = TimeOut;
EnumColumnList enumColumns = new EnumColumnList();
enumColumns.Add("AuditStatus", typeof(RefundStatus));
enumColumns.Add("OrderType", typeof(RefundOrderType));
enumColumns.Add("RefundStatus", typeof(SOIncomeStatus));
enumColumns.Add("RefundPayType", typeof(RefundPayType));
enumColumns.Add("Source", typeof(NetPaySource));
result = cmd.ExecuteDataTable(enumColumns);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
}
return(result);
}
public DataTable QueryConsignToAccountLogTotalAmt(QueryFilter.PO.ConsignToAccountLogQueryFilter queryFilter)
{
DataTable dt = new DataTable();
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryAccountLogTotalAmt");
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, "Consign.SysNo DESC"))
{
if (queryFilter != null && (queryFilter.SysNoList == null || queryFilter.SysNoList.Count <= 0))
{
if (queryFilter.CreateTimeFrom.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.CreateTime", DbType.DateTime,
"@CreateTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateTimeFrom.Value);
}
if (queryFilter.CreateTimeTo.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.CreateTime", DbType.DateTime,
"@CreateTimTo", QueryConditionOperatorType.LessThan, queryFilter.CreateTimeTo.Value.AddDays(1));
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Product.Product", DbType.String,
"@ProductName", QueryConditionOperatorType.Like, queryFilter.ProductName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Product.SysNo", DbType.Int32,
"@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.StockSysNo", DbType.Int32,
"@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.SettleType", DbType.String,
"@SettleType", QueryConditionOperatorType.Equal, queryFilter.SettleType == null ? null : queryFilter.SettleType.ToString());
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Vendor.VendorName", DbType.String,
"@VendorName", QueryConditionOperatorType.Like, queryFilter.VendorName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.VendorSysNo", DbType.Int32,
"@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo);
if (queryFilter.ConsignToAccType.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.ConsignToAccType", DbType.String,
"@ConsignToAccType", QueryConditionOperatorType.Equal, (int)queryFilter.ConsignToAccType.Value);
}
if (queryFilter.Status.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.Status", DbType.String,
"@Status", QueryConditionOperatorType.Equal, (int)queryFilter.Status.Value);
}
}
else if (queryFilter != null && queryFilter.SysNoList != null && queryFilter.SysNoList.Count > 0)
{
builder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND
, "Consign.sysno", DbType.Int32, queryFilter.SysNoList);
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.CompanyCode", DbType.String,
"@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
command.CommandText = builder.BuildQuerySql();
dt = command.ExecuteDataTable();
return(dt);
}
}
public System.Data.DataTable QueryConsignToAccountLog(QueryFilter.PO.ConsignToAccountLogQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryConsinToAccountLog");
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, "Consign.SysNo DESC"))
{
if (pagingInfo != null && (queryFilter.SysNoList == null || queryFilter.SysNoList.Count <= 0))
{
if (queryFilter.CreateTimeFrom.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.CreateTime", DbType.DateTime,
"@CreateTimeFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.CreateTimeFrom.Value);
}
if (queryFilter.CreateTimeTo.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.CreateTime", DbType.DateTime,
"@CreateTimTo", QueryConditionOperatorType.LessThan, queryFilter.CreateTimeTo.Value.AddDays(1));
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Product.Product", DbType.String,
"@ProductName", QueryConditionOperatorType.Like, queryFilter.ProductName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Product.SysNo", DbType.Int32,
"@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.StockSysNo", DbType.Int32,
"@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.SettleType", DbType.String,
"@SettleType", QueryConditionOperatorType.Equal, queryFilter.SettleType == null ? null : queryFilter.SettleType.ToString());
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Vendor.VendorName", DbType.String,
"@VendorName", QueryConditionOperatorType.Like, queryFilter.VendorName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.VendorSysNo", DbType.Int32,
"@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo);
if (queryFilter.ConsignToAccType.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.ConsignToAccType", DbType.String,
"@ConsignToAccType", QueryConditionOperatorType.Equal, (int)queryFilter.ConsignToAccType.Value);
}
if (queryFilter.Status.HasValue)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.Status", DbType.String,
"@Status", QueryConditionOperatorType.Equal, (int)queryFilter.Status.Value);
}
}
else if (queryFilter != null && queryFilter.SysNoList != null && queryFilter.SysNoList.Count > 0)
{
builder.ConditionConstructor.AddInCondition <int>(QueryConditionRelationType.AND
, "Consign.sysno", DbType.Int32, queryFilter.SysNoList);
}
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Consign.CompanyCode", DbType.String,
"@CompanyCode", QueryConditionOperatorType.Equal, queryFilter.CompanyCode);
command.CommandText = builder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList
{
{ "Status", typeof(ConsignToAccountLogStatus) },
{ "ReferenceType", typeof(ConsignToAccountType) },
{ "SettleType", typeof(SettleType) }
};
dt = command.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
if (null != dt && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["InDate"] != null && dt.Rows[i]["InDate"] != DBNull.Value)
{
dt.Rows[i]["InDateString"] = string.Format("{0}[{1}]", EnumHelper.GetDisplayText((ConsignToAccountType)Enum.Parse(typeof(ConsignToAccountType), dt.Rows[i]["ReferenceType"].ToString())), dt.Rows[i]["InDate"]);
}
}
}
}
return(dt);
}
/// <summary>
/// 查询充值卡
/// </summary>
/// <param name="pageIndex">取第几页</param>
/// <param name="pageSize">每页显示几条</param>
/// <param name="category">卡类型</param>
/// <param name="status">状态</param>
/// <param name="beginTime">有效期起</param>
/// <param name="endTime">有效期止</param>
/// <returns></returns>
public static PageList<List<PayCard>> QueryPayCard(int pageIndex, int pageSize, int category, int status, DateTime beginTime, DateTime endTime)
{
PagingInfoEntity page = new PagingInfoEntity();
page.MaximumRows = pageSize;
page.StartRowIndex = pageIndex * pageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("PayCard_Query");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
{
if (category > 0)
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "CategorySysNo", DbType.Int32, "@CategorySysNo", QueryConditionOperatorType.Equal, category);
if (status >= 0)
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "BeginTime", DbType.DateTime, "@BeginTime", QueryConditionOperatorType.MoreThanOrEqual, beginTime);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "EndTime", DbType.DateTime, "@EndTime", QueryConditionOperatorType.LessThanOrEqual, endTime);
cmd.CommandText = sqlBuilder.BuildQuerySql();
List<PayCard> result = cmd.ExecuteEntityList<PayCard>();
int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return new PageList<List<PayCard>>(result, pageIndex, pageSize, totalCount);
}
}
