public DataTable QueryCategoryProperty(CategoryPropertyQueryFilter queryCriteria, out int totalCount)
{
var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCategoryProperty");
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, "P.SysNo DESC"))
{
if (queryCriteria.CategorySysNo > 0)
{
//dataCommand.AddInputParameter("@CategorySysNo", DbType.Int32, queryCriteria.CategorySysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"CategorySysNo",
DbType.Int32, "@CategorySysNo",
QueryConditionOperatorType.Equal,
queryCriteria.CategorySysNo);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var enumList = new EnumColumnList();
enumList.Add("IsItemSearch", typeof(CategoryPropertyStatus));
enumList.Add("IsInAdvSearch", typeof(CategoryPropertyStatus));
enumList.Add("IsMustInput", typeof(CategoryPropertyStatus));
enumList.Add("WebDisplayStyle", typeof(WebDisplayStyle));
enumList.Add("PropertyType", typeof(PropertyType));
DataTable dt = dataCommand.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 查询商品
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryProductEx(NeweggProductQueryFilter filter, out int totalCount)
{
DataCommand command = DataCommandManager.GetDataCommand("QueryProductEx");
filter.CompanyCode = "8601";
filter.CurrentUserSysNo = ServiceContext.Current.UserSysNo;
CreateQueryProductCommand(filter, command);
var sort = filter.PagingInfo.SortBy;
var sortField = "";
var sortType = "";
if (!String.IsNullOrEmpty(sort))
{
var input = Regex.Replace(filter.PagingInfo.SortBy.Trim(), "\\s+", " ");
var pageArry = input.Split(' ');
if (pageArry.Length == 2)
{
sortField = pageArry[0];
sortType = pageArry[1];
}
}
command.SetParameterValue("@SortField", sortField);
command.SetParameterValue("@SortType", sortType);
command.SetParameterValue("@PageSize", filter.PagingInfo.PageSize);
command.SetParameterValue("@PageCurrent", filter.PagingInfo.PageIndex);
var columnConfig = new EnumColumnList
{
{ "Status", typeof(ProductStatus) }
};
var table = command.ExecuteDataTable(columnConfig);
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(table);
}
public DataTable QuerySOLog(QueryFilter.Common.LogQueryFilter filter, out int totalCount)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetSOLog");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = filter.PagingInfo.SortBy,
StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize,
MaximumRows = filter.PagingInfo.PageSize
};
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, pagingInfo, "[LOG].[SysNo] DESC"))
{
SetTheSearchCondition(builder, filter);
command.CommandText = builder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("Status", typeof(LogSOStatus));
enumList.Add("TicketType", typeof(c.BizLogType));
DataTable dt = command.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable GetRmaPolicyLog(RmaPolicyLogQueryFilter filter, out int totalCount)
{
DataCommand cmd = DataCommandManager.GetDataCommand("GetRmaPolicyLog");
cmd.SetParameterValue("@RmaPolicySysNo", filter.RmaPolicySysNO);
cmd.SetParameterValue("@RmaPolicy", filter.RmaPolicy);
cmd.SetParameterValue("@UpdateDateTo", filter.UpdateDateTo);
cmd.SetParameterValue("@UpdateDateFrom", filter.UpdateDateFrom);
cmd.SetParameterValue("@EditUserName", filter.EidtUserName);
cmd.SetParameterValue("@pageIndex", filter.PagingInfo.PageIndex);
cmd.SetParameterValue("@pageSize", filter.PagingInfo.PageSize);
cmd.SetParameterValue("@sortField", filter.PagingInfo.SortBy);
EnumColumnList enumList = new EnumColumnList
{
{ "Status", typeof(RmaPolicyStatus) },
{ "Type", typeof(RmaPolicyType) },
{ "IsOnlineRequst", typeof(IsOnlineRequst) },
{ "OperationType", typeof(RmaLogActionType) },
};
DataTable dt = new DataTable();
dt = cmd.ExecuteDataTable(enumList);
totalCount = (int)cmd.GetParameterValue("@totalCount");
return(dt);
}
public DataTable Query(HelpCenterQueryFilter 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("HelpCenter_GetHelpTopic");
var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "B.SysNo DESC");
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"B.Priority",
DbType.Int32,
"@Priority",
QueryConditionOperatorType.Equal,
filter.Priority);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"B.Status",
DbType.AnsiStringFixedLength,
"@Status",
QueryConditionOperatorType.Equal,
filter.Status);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"B.Type",
DbType.AnsiStringFixedLength,
"@Type",
QueryConditionOperatorType.Equal,
filter.Type);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"B.CategorySysNo",
DbType.Int32,
"@CategorySysNo",
QueryConditionOperatorType.Equal,
filter.CategorySysNo);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"B.CompanyCode",
DbType.AnsiStringFixedLength,
"@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode);
//TODO:添加ChannelID参数
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumConfig = new EnumColumnList();
enumConfig.Add("Status", typeof(ADStatus));
enumConfig.Add("Type", typeof(FeatureType));
var dt = cmd.ExecuteDataTable(enumConfig);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
/// <summary>
/// 查询品牌
/// </summary>
/// <returns></returns>
public virtual DataTable QueryBrand(BrandQueryFilter queryCriteria, out int totalCount)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetBrandListByQuery");
dataCommand.SetParameterValue("@BrandName", string.IsNullOrEmpty(queryCriteria.BrandNameLocal)?null:string.Format("%{0}%", queryCriteria.BrandNameLocal));
dataCommand.SetParameterValue("@ManufacturerName", string.IsNullOrEmpty(queryCriteria.ManufacturerName) ? null : string.Format("%{0}%", queryCriteria.ManufacturerName));
dataCommand.SetParameterValue("@Status", queryCriteria.Status);
dataCommand.SetParameterValue("@BrandSysNo", queryCriteria.BrandSysNo);
dataCommand.SetParameterValue("@ManufacturerSysNo", queryCriteria.ManufacturerSysNo);
dataCommand.SetParameterValue("@Category1SysNo", queryCriteria.Category1SysNo);
dataCommand.SetParameterValue("@Category2SysNo", queryCriteria.Category2SysNo);
dataCommand.SetParameterValue("@Category3SysNo", queryCriteria.Category3SysNo);
dataCommand.SetParameterValue("@IsAuthorized", queryCriteria.IsAuthorized);
dataCommand.SetParameterValue("@AuthorizedStatus", queryCriteria.AuthorizedStatus);
dataCommand.SetParameterValue("@BrandStory", queryCriteria.IsBrandStory);
dataCommand.SetParameterValue("@SortField", queryCriteria.PagingInfo.SortBy);
dataCommand.SetParameterValue("@PageSize", queryCriteria.PagingInfo.PageSize);
dataCommand.SetParameterValue("@PageCurrent", queryCriteria.PagingInfo.PageIndex);
dataCommand.SetParameterValue("@Priority", queryCriteria.Priority == "" ? null : queryCriteria.Priority);
EnumColumnList enumList = new EnumColumnList
{
{ "Status", typeof(ValidStatus) },
};
DataTable dt = new DataTable();
dt = dataCommand.ExecuteDataTable(enumList);
totalCount = (int)dataCommand.GetParameterValue("@TotalCount");
return(dt);
}
/// <summary>
/// 查询赠品
/// </summary>
/// <param name="queryCriteria"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GetGifts(RepeatPromotionQueryFilter queryCriteria, out int totalCount)
{
var pagingInfo = new PagingInfoEntity
{
SortField = queryCriteria.PageInfo.SortBy,
MaximumRows = queryCriteria.PageInfo.PageSize,
StartRowIndex = queryCriteria.PageInfo.PageIndex * queryCriteria.PageInfo.PageSize
};
var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetGifts");
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "C.SysNo DESC"))
{
var sysNoList = (new QueryGift()).GetGiftSysNo(queryCriteria);
var sysNoStr = sysNoList.Count <= 0 ? "-1" : sysNoList.Join(",");
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND,
"C.SysNo",
QueryConditionOperatorType.In,
sysNoStr);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var enumList = new EnumColumnList {
{ "Status", typeof(SaleGiftStatus) }, { "Type", typeof(SaleGiftType) }
};
DataTable dt = dataCommand.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
public System.Data.DataTable QueryPurchaseOrderList(QueryFilter.PO.PurchaseOrderQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
#region
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPOList");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
BuildSearchPOCondition(queryFilter, dataCommand, pagingInfo);
if (!string.IsNullOrEmpty(queryFilter.StatusList))
{
dataCommand.CommandText = dataCommand.CommandText.Replace("@Status", " @Status OR PO.Status IN (1,2,3,-2,5,6)");
}
EnumColumnList enumList = new EnumColumnList();
enumList.Add("status", typeof(PurchaseOrderStatus));
enumList.Add("PaySettleCompany", typeof(PaySettleCompany));
dt = dataCommand.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
#endregion
}
/// <summary>
/// 根据query得到CPSUser信息
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GetCpsUser(CpsUserQueryFilter query, out int totalCount)
{
DataCommand cmd = DataCommandManager.GetDataCommand("GetCpsUser");
cmd.SetParameterValue("@Status", query.AuditStatus);
cmd.SetParameterValue("@UserType", query.UserType);
cmd.SetParameterValue("@WebSiteType", query.WebSiteType);
cmd.SetParameterValue("@IsAvailable", query.IsActive);
cmd.SetParameterValue("@CustomerID", query.CustomerID);
cmd.SetParameterValue("@ReceivablesName", query.ReceivablesName);
cmd.SetParameterValue("@Email", query.Email);
cmd.SetParameterValue("@Phone", query.Phone);
cmd.SetParameterValue("@ImMessenger", query.ImMessenger);
cmd.SetParameterValue("@RegisterDateFrom", query.RegisterDateFrom);
cmd.SetParameterValue("@RegisterDateTo", query.RegisterDateTo);
cmd.SetParameterValue("@PageIndex", query.PageInfo.PageIndex);
cmd.SetParameterValue("@PageSize", query.PageInfo.PageSize);
cmd.SetParameterValue("@SortFiled", query.PageInfo.SortBy);
EnumColumnList enumList = new EnumColumnList
{
{ "Status", typeof(AuditStatus) },
{ "UserType", typeof(UserType) },
{ "IsAvailable", typeof(IsActive) },
{ "BankLock", typeof(IsLock) },
{ "BankAccountType", typeof(UserType) }
};
DataTable dt = new DataTable();
dt = cmd.ExecuteDataTable(enumList);
totalCount = (int)cmd.GetParameterValue("@TotalCount");
return(dt);
}
public System.Data.DataSet Query(QueryFilter.MKT.AmbassadorNewsQueryFilter 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("GetAmbassadorNewsList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "a.SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.NewsType", DbType.String, "@NewsType",
QueryConditionOperatorType.Equal, 12);
cmd.AddInputParameter("@SysNo", DbType.Int32, filter.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Title", DbType.String, "@Title",
QueryConditionOperatorType.LeftLike, filter.Title);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.CreateDate", DbType.DateTime, "@CreateDateTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.InDateFromTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.CreateDate", DbType.DateTime, "@CreateDateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.InDateFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.ReferenceSysNo", DbType.String, "@ReferenceSysNo",
QueryConditionOperatorType.Equal, filter.ReferenceSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.CompanyCode", DbType.String, "@CompanyCode",
QueryConditionOperatorType.Equal, filter.CompanyCode);
cmd.CommandText = sqlBuilder.BuildQuerySql();
var ds = cmd.ExecuteDataSet();
EnumColumnList enumConfigNews = new EnumColumnList();
enumConfigNews.Add("Status", typeof(AmbassadorNewsStatus));
cmd.ConvertEnumColumn(ds.Tables[0], enumConfigNews);
if (Int32.TryParse(cmd.GetParameterValue("@TotalCount").ToString(), out totalCount))
{
}
else
{
totalCount = 0;
}
return(ds);
}
}
public virtual DataTable QueryCallsEventLog(CustomerCallsEventLogFilter 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("QueryCallsEventLog");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.Customer_CallingSysNo",
DbType.Int32,
"@CallsEventsSysNo ",
QueryConditionOperatorType.Equal,
filter.CallsEventsSysNo);
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enunlist = new EnumColumnList();
enunlist.Add(3, typeof(CallsEventsStatus));
enunlist.Add(7, typeof(CustomerCallReason));
CodeNamePairColumnList cpList = new CodeNamePairColumnList();
cpList.Add(2, "Customer", "RecordOrigion");
DataTable dt = cmd.ExecuteDataTable(enunlist, cpList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// SAP确认人查询
/// </summary>
/// <param name="query"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public virtual DataTable QueryIPPUser(SAPIPPUserQueryFilter 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("QuerySAPIPPUser");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingEntity, "I.SysNo desc"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "I.PayType",
DbType.Int32, "@PayType", QueryConditionOperatorType.Equal, query.PayType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "I.CustID",
DbType.String, "@CustID", QueryConditionOperatorType.Equal, query.CustID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "I.SystemConfirmID",
DbType.String, "@SystemConfirmID", QueryConditionOperatorType.Equal, query.SystemConfirmID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "I.CompanyCode",
DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, query.CompanyCode);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumColumnList = new EnumColumnList();
enumColumnList.Add("Status", typeof(SAPStatus));
DataTable dt = dataCommand.ExecuteDataTable(enumColumnList);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable GetProductExtByQuery(ProductExtQueryFilter query, out int totalCount)
{
DataCommand dc = DataCommandManager.GetDataCommand("GetItemExtList");
dc.SetParameterValue("@ProductID", query.ProductID);
dc.SetParameterValue("@ProductStatus", query.ProductStatus);
if (query.ProductType != null)
{
dc.SetParameterValue("@ProductType", LegacyEnumMapper.ConvertProductType(query.ProductType.Value));
}
else
{
dc.SetParameterValue("@ProductType", null);
}
dc.SetParameterValue("@Manufacturer", query.Manufacturer);
dc.SetParameterValue("@ProductPrice", query.ProductPrice);
dc.SetParameterValue("@IsPermitRefund", query.IsPermitRefund);
dc.SetParameterValue("@Category1", query.Category1);
dc.SetParameterValue("@Category2", query.Category2);
dc.SetParameterValue("@Category3", query.Category3);
dc.SetParameterValue("@SortField", query.PagingInfo.SortBy);
dc.SetParameterValue("@PageSize", query.PagingInfo.PageSize);
dc.SetParameterValue("@PageCurrent", query.PagingInfo.PageIndex);
EnumColumnList enumList = new EnumColumnList
{
{ "Status", typeof(ProductStatus) },
{ "IsPermitRefund", typeof(IsDefault) }
};
DataTable dt = new DataTable();
dt = dc.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(dc.GetParameterValue("@TotalCount"));
return(dt);
}
/// <summary>
/// 查询外网搜素
/// </summary>
/// <param name="queryCriteria"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryKeyword(InternetKeywordQueryFilter queryCriteria, out int totalCount)
{
var pagingInfo = new PagingInfoEntity
{
SortField = queryCriteria.PageInfo.SortBy,
MaximumRows = queryCriteria.PageInfo.PageSize,
StartRowIndex = queryCriteria.PageInfo.PageIndex * queryCriteria.PageInfo.PageSize
};
var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryKeyword");
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "C.SysNo DESC"))
{
if (!string.IsNullOrWhiteSpace(queryCriteria.SearchKeyword))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"SearchKeyword",
DbType.String, "@SearchKeyword",
QueryConditionOperatorType.Like,
queryCriteria.SearchKeyword);
}
if (queryCriteria.Status != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.Status",
DbType.String, "@Status",
QueryConditionOperatorType.Equal,
queryCriteria.Status.Value);
}
if (queryCriteria.BeginDate != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"InDate",
DbType.String, "@BeginDate",
QueryConditionOperatorType.MoreThanOrEqual,
queryCriteria.BeginDate.Value);
}
if (queryCriteria.EndDate != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"InDate",
DbType.String, "@EndDate",
QueryConditionOperatorType.LessThan,
queryCriteria.EndDate.Value);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var enumList = new EnumColumnList {
{ "Status", typeof(IsDefaultStatus) }
};
DataTable dt = dataCommand.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 查询EIMS单据
/// </summary>
/// <param name="filter">查询条件集合</param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable EIMSInvoiceQuery(EIMSInvoiceQueryFilter filter, out int totalCount)
{
CustomDataCommand cmd;
if (filter.IsC1Summary == null ||
filter.IsC1Summary == false)
{
cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ExternalSYS_Query_EIMSInvoice");
}
else
{
cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ExternalSYS_Query_EIMSInvoiceBYC1Name");
}
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), "vendor.SysNo DESC"))
{
AddEIMSInvoiceParameters(filter, sb, cmd);
DataTable dt = cmd.ExecuteDataTable();
EnumColumnList enumColList = new EnumColumnList();
CodeNamePairColumnList codeNameColList = new CodeNamePairColumnList();
codeNameColList.Add("EIMSType", "ExternalSYS", "EIMSType");
codeNameColList.Add("ReceiveType", "ExternalSYS", "ReceivedType");
codeNameColList.Add("Status", "ExternalSYS", "InvoiceStatus");
cmd.ConvertColumn(dt, enumColList, codeNameColList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 获取详细的礼品卡操作日志
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public List <GiftCardRedeemLog> GetGiftCardRedeemLogJoinSOMaster(string code)
{
DataCommand dc = DataCommandManager.GetDataCommand("GiftCard_GetGiftCardRedeemLogJoinSOMaster");
dc.SetParameterValue("@Code", code);
EnumColumnList enumList = new EnumColumnList();
enumList.Add("OrderStatus", typeof(ECCentral.BizEntity.SO.SOStatus));
enumList.Add("RedeemStatus", typeof(ValidStatus));
var dt = dc.ExecuteDataTable(enumList);
if (dt != null && dt.Rows.Count > 0)
{
List <GiftCardRedeemLog> list = new List <GiftCardRedeemLog>();
foreach (DataRow dr in dt.Rows)
{
list.Add(DataMapper.GetEntity <GiftCardRedeemLog>(dr));
}
return(list);
}
else
{
return(null);
}
//using (IDataReader reader = dc.ExecuteDataReader())
//{
// return DataMapper.GetEntityList<GiftCardOperateLog, List<GiftCardOperateLog>>(reader);
//}
}
public DataTable QueryFloorMaster(FloorMasterQueryFilter 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("Floor_QueryFloorMaster");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "PageCode ASC, Priority ASC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"PageCode", DbType.String, "@PageCode",
QueryConditionOperatorType.Equal, filter.PageCode);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"PageType", DbType.Int32, "@PageType",
QueryConditionOperatorType.Equal, filter.PageType);
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumConfig = new EnumColumnList();
enumConfig.Add("PageType", typeof(PageCodeType));
enumConfig.Add("Status", typeof(ADStatus));
var dt = cmd.ExecuteDataTable(enumConfig);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable SocietyCommissionQuery(CommissionTypeQueryFilter filter, out int totalCount)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("SocietyCommissionQuery");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = filter.PagingInfo.SortBy,
StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize,
MaximumRows = filter.PagingInfo.PageSize
};
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, pagingInfo, "SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.Equal,
filter.SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"OrganizationID", DbType.String,
"@OrganizationID", QueryConditionOperatorType.Like,
filter.OrganizationID);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"OrganizationName", DbType.String,
"@OrganizationName", QueryConditionOperatorType.Like,
filter.OrganizationName);
command.CommandText = builder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("IsOnlineShow", typeof(HYNStatus));
DataTable dt = command.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 查询配送方式-产品
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryShipTypeProductList(ShipTypeProductQueryFilter filter, out int totalCount)
{
totalCount = 0;
object _itemRange = null;;
object _type = null;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ShipTypeProduct_QueryShipTypeProduct");
PagingInfoEntity pagingInfo = ToPagingInfo(filter.PagingInfo);
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingInfo, "SPI.SysNo DESC"))
{
if (filter.ShipTypeProductType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPI.[Type]", DbType.String, "@Type", QueryConditionOperatorType.Equal, EnumCodeMapper.TryGetCode(filter.ShipTypeProductType, out _type));
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPI.Description", DbType.String, "@Description", QueryConditionOperatorType.Like, filter.Description);
if (filter.ProductRange.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPI.ItemRange", DbType.String, "@ItemRange", QueryConditionOperatorType.Equal, EnumCodeMapper.TryGetCode(filter.ProductRange, out _itemRange));
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPI.ProductID", DbType.String, "@ProductID", QueryConditionOperatorType.Equal, filter.ProductID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPM.ShipTypeSysNo", DbType.Int32, "@ShipTypeSysNo", QueryConditionOperatorType.Equal, filter.ShippingType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SPM.StockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, filter.WareHouse);
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList EnumList = new EnumColumnList();
EnumList.Add("ItemRange", typeof(ECCentral.BizEntity.Common.ProductRange));
EnumList.Add("Type", typeof(ECCentral.BizEntity.Common.ShipTypeProductType));
DataTable dt = cmd.ExecuteDataTable(EnumList);
totalCount = int.Parse(cmd.GetParameterValue("TotalCount").ToString());
return(dt);
}
}
/// <summary>
/// 查询促销计划
/// </summary>
/// <param name="queryCriteria"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GeSaleCountDownPlan(RepeatPromotionQueryFilter queryCriteria, out int totalCount)
{
var pagingInfo = new PagingInfoEntity
{
SortField = queryCriteria.PageInfo.SortBy,
MaximumRows = queryCriteria.PageInfo.PageSize,
StartRowIndex = queryCriteria.PageInfo.PageIndex * queryCriteria.PageInfo.PageSize
};
var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetSaleCountDowns");
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "C.SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.ProductSysNo",
DbType.Int32, "@ProductSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.ProductSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.Status",
DbType.Int32, "@Status",
QueryConditionOperatorType.In,
new List <object> {
CountdownStatus.WaitForVerify, CountdownStatus.Ready
, CountdownStatus.Running, CountdownStatus.Init, CountdownStatus.VerifyFaild
});
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.IsPromotionSchedule",
DbType.Int32, "@IsPromotionSchedule",
QueryConditionOperatorType.Equal,
1);
if (queryCriteria.StartDate != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.EndTime",
DbType.String, "@StartDate",
QueryConditionOperatorType.MoreThanOrEqual,
queryCriteria.StartDate.Value);
}
if (queryCriteria.EndDate != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.StartTime",
DbType.String, "@EndDate",
QueryConditionOperatorType.LessThan,
queryCriteria.EndDate.Value);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var enumList = new EnumColumnList {
{ "Status", typeof(CountdownStatus) }
};
DataTable dt = dataCommand.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable Query(ConsignAdjustQueryFilter queryFilter, out int totalCount)
{
if (queryFilter == null)
{
totalCount = -1;
return(null);
}
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryConsignAdjustList");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, pagingInfo, "C.SysNo DESC"))
{
if (queryFilter.VendorSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.VendorSysNo",
DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, queryFilter.VendorSysNo.Value);
}
if (queryFilter.PMSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.PMSysNo",
DbType.Int32, "@PMSysNo", QueryConditionOperatorType.Equal, queryFilter.PMSysNo.Value);
}
if (queryFilter.Status.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status.Value);
}
//if (queryFilter.SettleSysno.HasValue)
//{
// sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SettleSysno",
// DbType.Int32, "@SettleSysno", QueryConditionOperatorType.Equal, queryFilter.SettleSysno.Value);
//}
if (queryFilter.SettleRange.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.SettleRange",
DbType.String, "@SettleRange", QueryConditionOperatorType.Equal, queryFilter.SettleRange.Value.ToString("yyyy-MM"));
}
command.CommandText = sqlBuilder.BuildQuerySql();
command.SetParameterValue("@StartNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex);
command.SetParameterValue("@EndNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex + queryFilter.PageInfo.PageSize);
EnumColumnList columnEnums = new EnumColumnList();
columnEnums.Add("Status", typeof(ConsignAdjustStatus));
DataTable dt = command.ExecuteDataTable(columnEnums);
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 查询配送方式
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryShipTypeList(ShipTypeQueryFilter filter, out int totalCount)
{
MapSortField(filter);
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("ShipType_QueryShipType");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "ST.SysNo DESC"))
{
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ST.ShipTypeID", DbType.String, "@ShipTypeID", QueryConditionOperatorType.Equal, filter.ShipTypeID);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ST.ShipTypeName", DbType.String, "@ShipTypeName", QueryConditionOperatorType.Like, filter.ShipTypeName);
//if(filter.IsOnlineShow.HasValue&&filter.IsOnlineShow.Value==ECCentral.BizEntity.Common.HYNStatus.Hide)
// sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ST.IsOnlineShow", DbType.Int32, "@IsOnlineShow", QueryConditionOperatorType.NotEqual, ECCentral.BizEntity.Common.HYNStatus.Hide);
//else
if (filter.IsOnlineShow != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ST.IsOnlineShow", DbType.Int32, "@IsOnlineShow", QueryConditionOperatorType.Equal, filter.IsOnlineShow.GetHashCode());
}
if (filter.IsWithPackFee != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ST.IsWithPackFee", DbType.Int32, "@IsWithPackFee", QueryConditionOperatorType.Equal, filter.IsWithPackFee.GetHashCode());
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ST.OnlyForStockSysNo", DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, filter.StockSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ST.CompanyCode", DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("IsOnlineShow", typeof(ECCentral.BizEntity.Common.HYNStatus));
enumList.Add("IsWithPackFee", typeof(ECCentral.BizEntity.MKT.NYNStatus));
enumList.Add("DeliveryPromise", typeof(ECCentral.BizEntity.Common.DeliveryStatusFor24H));
enumList.Add("PackStyle", typeof(ECCentral.BizEntity.Common.ShippingPackStyle));
enumList.Add("ShipTypeEnum", typeof(ECCentral.BizEntity.Common.ShippingTypeEnum));
enumList.Add("DeliveryType", typeof(ECCentral.BizEntity.Common.ShipDeliveryType));
enumList.Add("StoreType", typeof(ECCentral.BizEntity.IM.StoreType));
//CodeNamePairColumnList pairList = new CodeNamePairColumnList();
//pairList.Add("PackStyle", "Common", "ShippingPackStyle");
//pairList.Add("ShipTypeEnum", "Common", "ShipTypeEnum");
//pairList.Add("DeliveryType", "Common", "DeliveryType");
var dt = cmd.ExecuteDataTable(enumList);//<ECCentral.BizEntity.MKT.ADStatus>("Status");
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);//.Tables[0];
}
}
public DataSet QueryAmbassadorBasicInfo(QueryFilter.MKT.NeweggAmbassadorQueryFilter 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("GetNeweggAmbassadorList");
cmd.SetParameterValue("@NeweggAmbassadorID", filter.AmbassadorID);
cmd.SetParameterValue("@NeweggAmbassadorName", string.IsNullOrEmpty(filter.AmbassadorName)?"":string.Format("%{0}%", filter.AmbassadorName.Trim()));
cmd.SetParameterValue("@CustomerMark", filter.Status);
cmd.SetParameterValue("@AreaSysNo", filter.AreaSysNo);
cmd.SetParameterValue("@BigProvinceSysNo", filter.BigAreaSysNo);
if (!string.IsNullOrEmpty(filter.PagingInfo.SortBy))
{
string[] sortInfo = filter.PagingInfo.SortBy.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
if (sortInfo.Length > 1)
{
cmd.SetParameterValue("@SortField", sortInfo[0]);
cmd.SetParameterValue("@SortType", sortInfo[1]);
}
else
{
cmd.SetParameterValue("@SortField", null);
cmd.SetParameterValue("@SortType", null);
}
}
else
{
cmd.SetParameterValue("@SortField", null);
cmd.SetParameterValue("@SortType", null);
}
cmd.SetParameterValue("@PageSize", filter.PagingInfo.PageSize);
cmd.SetParameterValue("@PageCurrent", filter.PagingInfo.PageIndex);
DataSet ds = cmd.ExecuteDataSet();
EnumColumnList enumConfigNews = new EnumColumnList();
enumConfigNews.Add("CustomerMark", typeof(AmbassadorStatus));
if (Int32.TryParse(cmd.GetParameterValue("@TotalCount").ToString(), out totalCount))
{
}
else
{
totalCount = 0;
}
cmd.ConvertEnumColumn(ds.Tables[0], enumConfigNews);
return(ds);
}
public static DataTable UserQuery(UserQueryFilter filter, out int dataCount)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("Query_Vendor_User");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(command.CommandText, command, filter, !string.IsNullOrWhiteSpace(filter.SortFields) ? filter.SortFields : "a.SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.UserName", DbType.String,
"@UserName", QueryConditionOperatorType.LeftLike,
filter.UserName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.UserID", DbType.String,
"@UserID", QueryConditionOperatorType.LeftLike,
filter.UserID);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Status", DbType.AnsiStringFixedLength,
"@Status", QueryConditionOperatorType.Equal, filter.UserStatus);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
QueryConditionOperatorType.Equal,
filter.CompanyCode);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.VendorSysNo", DbType.AnsiStringFixedLength, "@ManufacturerSysNo",
QueryConditionOperatorType.Equal,
filter.ManufacturerSysNo);
if (!string.IsNullOrEmpty(filter.SerialNum))
{
string[] serialNum = filter.SerialNum.Split('-');
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.VendorSysNo", DbType.Int32,
"@VendorSysNo", QueryConditionOperatorType.Equal,
serialNum[0]);
if (serialNum.Length > 1)
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.UserNum", DbType.Int32,
"@UserNum", QueryConditionOperatorType.Equal,
Convert.ToInt32(serialNum[1]).ToString());
}
}
command.CommandText = builder.BuildQuerySql();
var dt = command.ExecuteDataTable();
EnumColumnList enumColList = new EnumColumnList();
enumColList.Add("Status", typeof(UserStatus));
command.ConvertEnumColumn(dt, enumColList);
dataCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
private DataTable ExecuteDataTable(CustomDataCommand dataCommand, out int totalCount)
{
EnumColumnList enumColumns = new EnumColumnList();
enumColumns.Add("OrderType", typeof(PayableOrderType));
var result = dataCommand.ExecuteDataTable(enumColumns);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(result);
}
/// <summary>
/// 查询团购
/// </summary>
/// <param name="queryCriteria"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable GetProductGroupBuying(RepeatPromotionQueryFilter queryCriteria, out int totalCount)
{
var pagingInfo = new PagingInfoEntity
{
SortField = queryCriteria.PageInfo.SortBy,
MaximumRows = queryCriteria.PageInfo.PageSize,
StartRowIndex = queryCriteria.PageInfo.PageIndex * queryCriteria.PageInfo.PageSize
};
var dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetProductGroupBuying");
using (var sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "C.SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.ProductSysNo",
DbType.Int32, "@ProductSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.ProductSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.Status",
DbType.String, "@Status",
QueryConditionOperatorType.In,
new List <object> {
GroupBuyingStatus.Init, GroupBuyingStatus.Pending
, GroupBuyingStatus.Active
});
if (queryCriteria.StartDate != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.EndDate",
DbType.String, "@StartDate",
QueryConditionOperatorType.MoreThanOrEqual,
queryCriteria.StartDate.Value);
}
if (queryCriteria.EndDate != null)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.BeginDate",
DbType.String, "@EndDate",
QueryConditionOperatorType.LessThan,
queryCriteria.EndDate.Value);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var enumList = new EnumColumnList {
{ "Status", typeof(GroupBuyingStatus) }, { "IsByGroup", typeof(IsDefaultShow) }
};
DataTable dt = dataCommand.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
private DataSet ExecuteDataCommandForTrackingInfo(CustomDataCommand dataCommand, out int totalCount)
{
DataSet ds = new DataSet();
//注册CodeNamePair类型
CodeNamePairColumnList codeNamePairColumnList = new CodeNamePairColumnList();
codeNamePairColumnList.Add("LossType", "Invoice", "TrackingInfoLoseType", "LossTypeID");
EnumColumnList enumColumnList = new EnumColumnList();
enumColumnList.Add("Status", typeof(TrackingInfoStatus));
enumColumnList.Add("OrderType", typeof(SOIncomeOrderType));
enumColumnList.Add("Source", typeof(NetPaySource));
enumColumnList.Add("OrderStatus", typeof(SOIncomeStatus));
enumColumnList.Add("IncomeStyle", typeof(SOIncomeOrderStyle));
enumColumnList.Add("InType", typeof(TrackingInfoStyle));
DataTable resultDT = dataCommand.ExecuteDataTable(enumColumnList, codeNamePairColumnList);
resultDT.TableName = "DataResult";
ds.Tables.Add(resultDT);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
#region 构造统计信息
DataTable statisticDT = new DataTable("StatisticResult");
statisticDT.Columns.AddRange(new DataColumn[]
{
new DataColumn("OrderAmt", typeof(decimal)), /* 订单金额 */
new DataColumn("PrepayAmt", typeof(decimal)), /* 预收金额 */
new DataColumn("GiftCardPayAmt", typeof(decimal)), /* 礼品卡金额 */
new DataColumn("IncomeAmt", typeof(decimal)), /* 实收金额 */
new DataColumn("UnpayedAmt", typeof(decimal)) /* 未收金额 */
});
DataRow row = statisticDT.NewRow();
statisticDT.Rows.Add(row);
row["OrderAmt"] = dataCommand.GetParameterValue("@OrderAmt");
row["PrepayAmt"] = dataCommand.GetParameterValue("@PrepayAmt");
row["GiftCardPayAmt"] = dataCommand.GetParameterValue("@GiftCardPayAmt");
row["IncomeAmt"] = dataCommand.GetParameterValue("@IncomeAmt");
row["UnpayedAmt"] = dataCommand.GetParameterValue("@UnpayedAmt");
ds.Tables.Add(statisticDT);
#endregion 构造统计信息
return(ds);
}
public DataTable QueryPriceChange(ChangePriceFilter query, out int totalCount)
{
DataTable result = null;
PagingInfoEntity pagingInfo = new PagingInfoEntity();
if (query.PagingInfo != null)
{
pagingInfo.MaximumRows = query.PagingInfo.PageSize;
pagingInfo.StartRowIndex = query.PagingInfo.PageIndex * query.PagingInfo.PageSize;
pagingInfo.SortField = query.PagingInfo.SortBy;
}
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("PriceChange_QueryPriceChange");
using (DynamicQuerySqlBuilder sql = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingInfo, "SysNo DESC"))
{
sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PC.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, query.SysNo);
sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "P.ProductsysNo", DbType.Int32, "@ProductsysNo", QueryConditionOperatorType.Equal, query.ProductsysNo);
sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PC.Memo", DbType.String, "@Memo", QueryConditionOperatorType.Like, query.Memo);
if (query.Status.HasValue)
{
sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PC.Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, query.Status);
}
sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PC.BeginDate", DbType.DateTime, "@BeginDate", QueryConditionOperatorType.Equal, query.BeginDate);
sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PC.EndDate", DbType.DateTime, "@EndDate", QueryConditionOperatorType.Equal, query.EndDate);
sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"P.C3SysNo", DbType.Int32, "@C3SysNo", QueryConditionOperatorType.Equal, query.C3SysNo);
sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C3.C2SysNo", DbType.Int32, "@C2SysNo", QueryConditionOperatorType.Equal, query.C2SysNo);
sql.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C2.C1SysNo", DbType.Int32, "@C1SysNo", QueryConditionOperatorType.Equal,
query.C1SysNo);
cmd.CommandText = sql.BuildQuerySql();
EnumColumnList enumColumns = new EnumColumnList();
enumColumns.Add("Status", typeof(RequestPriceStatus));
result = cmd.ExecuteDataTable(enumColumns);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(result);
}
}
/// <summary>
/// 根据SysNo查询Memo
/// </summary>
/// <param name="sysNo"></param>
/// <returns></returns>
public DataTable GetMemoBySysNo(int?sysNo)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Invoice_QueryBySysNo");
cmd.AddInputParameter("@SysNo", DbType.Int32, sysNo);
DataTable dt = cmd.ExecuteDataTable();
EnumColumnList enumColList = new EnumColumnList();
enumColList.Add("AuditStatus", typeof(PayableAuditStatus));
cmd.ConvertColumn(dt, enumColList, null);
return(dt);
}
public virtual DataTable SimpleQuery(CustomerSimpleQueryFilter 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;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Customer_SimpleQuery");
using (var sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "SysNo DESC"))
{
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"C.SysNo", DbType.Int32, "@SystemNumber",
QueryConditionOperatorType.Equal,
queryCriteria.CustomerSysNo);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.CustomerID", DbType.String, "@CustomerID", QueryConditionOperatorType.LeftLike, queryCriteria.CustomerID);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.CustomerName", DbType.String, "@CustomerName", QueryConditionOperatorType.LeftLike, queryCriteria.CustomerName);
// sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.MemberShipCard", DbType.String, "@MemberShipCard", QueryConditionOperatorType.LeftLike, queryCriteria.CustomerName);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.Email", DbType.AnsiString, "@Email", QueryConditionOperatorType.LeftLike, queryCriteria.CustomerEmail);
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "C.Status", DbType.AnsiStringFixedLength, "@Status", QueryConditionOperatorType.Equal, queryCriteria.Status);
if (!string.IsNullOrEmpty(queryCriteria.CustomerPhone))
{
//添加CustomerPhone子查询，并添加一个参数
sb.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "", QueryConditionOperatorType.Exist,
@"SELECT 1 FROM OverseaCustomerManagement.[dbo].[CustomerPhone] WITH(NOLOCK)
WHERE OverseaCustomerManagement.[dbo].[CustomerPhone].Phone like '%'+@Phone+'%'
AND OverseaCustomerManagement.[dbo].CustomerPhone.CustomerSysNo = C.SysNo" );
cmd.AddInputParameter("@Phone", DbType.String, queryCriteria.CustomerPhone);
}
sb.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"C.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode",
QueryConditionOperatorType.Equal,
queryCriteria.CompanyCode);
cmd.CommandText = sb.BuildQuerySql();
EnumColumnList enumColumnList = new EnumColumnList();
enumColumnList.Add(2, typeof(CustomerStatus));
enumColumnList.Add(30, typeof(CustomerType));
enumColumnList.Add(17, typeof(CustomerRank));
enumColumnList.Add(19, typeof(VIPRank));
DataTable dt = cmd.ExecuteDataTable();
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
