/// <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 static DataTable QueryRole(RoleQueryFilter filter, out int dataCount)
{
CustomDataCommand command = DataCommandManager.
CreateCustomDataCommandFromConfig("Query_Vendor_Role");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, filter, !string.IsNullOrWhiteSpace(filter.SortFields) ? filter.SortFields : "SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"RoleName", DbType.String,
"@RoleName", QueryConditionOperatorType.LeftLike,
filter.RoleName);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"Status", DbType.AnsiStringFixedLength,
"@Status", QueryConditionOperatorType.Equal,
filter.Status);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"ManufacturerSysNo", DbType.AnsiStringFixedLength,
"@ManufacturerSysNo", QueryConditionOperatorType.Equal,
filter.ManufacturerSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"VendorSysNo", DbType.AnsiStringFixedLength,
"@VendorSysNo", QueryConditionOperatorType.Equal,
filter.VendorSysNo);
command.CommandText = builder.BuildQuerySql();
var dt = command.ExecuteDataTable("Status", typeof(RoleStatus));
dataCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 礼品卡发票明细表查询
/// </summary>
public DataTable GiftInvoiceDetailReportQuery(GiftInvoiceDetaiReportQueryFilter filter, out int totalCount)
{
DataTable result = null;
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;
}
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryGiftInvoiceDetailReport");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingInfo, "a.SOID DESC"))
{
AddParameter4GiftInvoiceDetailReportQuery(filter, sqlBuilder);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var suffix = GetStockSuffix(filter.StockSysNo.Value);
dataCommand.CommandText = dataCommand.CommandText
.Replace("Invoice_detail", "Invoice_detail_" + suffix)
.Replace("Invoice_Result", "Invoice_Result_" + suffix)
.Replace("Invoice_TrackingNumber", "Invoice_TrackingNumber_" + suffix) + " ORDER BY OrderID ";
result = dataCommand.ExecuteDataTable("InvoiceType", "Invoice", "InvoiceDetailReportInvoiceType");
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
return(result);
}
/// <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="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable InvoiceSelfPrintQuery(InvoiceSelfPrintQueryFilter filter, out int totalCount)
{
DataTable result = null;
MapSelfPrintSortFileds(filter);
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("InvoiceReport.GetSelfPrintInvoiceList");
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, "sm.SysNo DESC"))
{
AddSelfPrintParameter(filter, sqlBuilder);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
result = dataCommand.ExecuteDataTable();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
return(result);
}
public System.Data.DataTable QueryAreaDelivery(QueryFilter.Common.AreaDeliveryQueryFilter filter, out int totalCount)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("GetAreaDeliveryInfo");
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, "Priority"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"WHArea", DbType.Int32,
"@WHArea", QueryConditionOperatorType.Equal,
filter.WHArea);
command.CommandText = builder.BuildQuerySql();
DataTable dt = command.ExecuteDataTable();
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 优惠券查询
/// </summary>
/// <param name="queryFilter"></param>
/// <returns></returns>
public static QueryResult QueryCouponList(CouponQueryFilter queryFilter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryCouponList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, queryFilter, string.IsNullOrEmpty(queryFilter.SortFields) ? "c.SysNo ASC" : queryFilter.SortFields))
{
//Set SQL WHERE Condition:
if (queryFilter.SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, queryFilter.SysNo);
}
if (queryFilter.MerchantSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.MerchantSysNo", DbType.Int32, "@MerchantSysNo", QueryConditionOperatorType.Equal, queryFilter.MerchantSysNo);
}
if (!string.IsNullOrWhiteSpace(queryFilter.CouponName))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.CouponName", DbType.String, "@CouponName", QueryConditionOperatorType.Like, queryFilter.CouponName);
}
if (!string.IsNullOrWhiteSpace(queryFilter.CouponCode))
{
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist,
"Select Top 1 1 FROM [OverseaECommerceManagement].[dbo].[CouponCode] as cc with(nolock) WHERE cc.CouponSysNo=c.SysNo AND cc.CouponCode=@CouponCode");
command.AddInputParameter("@CouponCode", DbType.String, queryFilter.CouponCode);
}
if (queryFilter.Status.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.Status", DbType.String, "@Status", QueryConditionOperatorType.Equal, queryFilter.Status);
}
if (queryFilter.BeginDateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.BeginDate", DbType.DateTime, "@BeginDateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.BeginDateFrom.Value.Date);
}
if (queryFilter.BeginDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.BeginDate", DbType.DateTime, "@BeginDateTo", QueryConditionOperatorType.LessThan, queryFilter.BeginDateTo.Value.Date.AddDays(1));
}
if (queryFilter.EndDateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.EndDate", DbType.DateTime, "@EndDateFrom", QueryConditionOperatorType.MoreThanOrEqual, queryFilter.EndDateFrom.Value.Date);
}
if (queryFilter.EndDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c.EndDate", DbType.DateTime, "@EndDateTo", QueryConditionOperatorType.LessThan, queryFilter.EndDateTo.Value.Date.AddDays(1));
}
command.CommandText = sqlBuilder.BuildQuerySql();
DataTable resultList = command.ExecuteDataTable(3, typeof(CouponStatus));
int totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(new QueryResult(resultList, queryFilter, totalCount));// { PageInfo = new PageInfo() { PageIndex = queryFilter.PageIndex, PageSize = queryFilter.PageSize, TotalCount = totalCount, SortBy = queryFilter.SortFields }, ResultList = resultList };
}
}
/// <summary>
/// 商品库龄报表
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryProductStockAgeReport(StockAgeReportQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = filter.PagingInfo.SortBy,
StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize,
MaximumRows = filter.PagingInfo.PageSize
};
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductStockAgeReport");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, pagingInfo, "p.[SysNo] DESC"))
{
command.AddInputParameter("@StatisticDate", DbType.DateTime, filter.StatisticDate.AddDays(1).AddSeconds(-1));
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c1.SysNo", DbType.Int32,
"@C1SysNo", QueryConditionOperatorType.Equal, filter.C1SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c2.sysno", DbType.Int32,
"@C2SysNo", QueryConditionOperatorType.Equal, filter.C2SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "c3.sysno", DbType.Int32,
"@C3SysNo", QueryConditionOperatorType.Equal, filter.C3SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ProductSysNo", DbType.Int32,
"@ProductSysNo", QueryConditionOperatorType.Equal, filter.ProductSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.StockSysNo", DbType.Int32,
"@StockSysNo", QueryConditionOperatorType.Equal, filter.StockSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vendor.sysno", DbType.Int32,
"@VendorSysNo", QueryConditionOperatorType.Equal, filter.VendorSysNo);
#region 根据选择的库龄类型构造时间条件
StringBuilder strUpdateProductStockAgeInventory = new StringBuilder();
foreach (string stockAgeTypeID in filter.StockAgeTypeList)
{
Tuple <DateTime, DateTime, string> dateRange = GetStockAgeDateRange(filter.StatisticDate.Date, stockAgeTypeID);
strUpdateProductStockAgeInventory.AppendFormat(@"UPDATE t
SET t.Amt{2} = i.Amount,t.Qty{2} = i.Quantity
FROM @ProductStockAgeInventory t
LEFT JOIN ipp3.dbo.FN_EC_GetProductPOInventory('{0}','{1}') i
ON t.ProductSysNo = i.ProductSysNo AND t.StockSysNo = i.StockSysNo AND t.VendorSysNo = i.VendorSysNo;"
, dateRange.Item1.ToString("yyyy-MM-dd HH:mm:ss"), dateRange.Item2.ToString("yyyy-MM-dd HH:mm:ss"), dateRange.Item3);
strUpdateProductStockAgeInventory.AppendLine();
}
#endregion
command.CommandText = builder.BuildQuerySql();
command.CommandText = command.CommandText.Replace("#strUpdateProductStockAgeInventory#", strUpdateProductStockAgeInventory.ToString());
DataTable dt = command.ExecuteDataTable();
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(dt);
}
}
private DataTable GetEmptyCategoryList(ProductDomainFilter filter, out int totalCount)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("ProductDomain_GetEmptyCategoryList");
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, "C1.[SysNo] DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C2.CompanyCode", DbType.AnsiStringFixedLength,
"@CompanyCode", QueryConditionOperatorType.Equal,
filter.CompanyCode);
command.CommandText = builder.BuildQuerySql();
var result = command.ExecuteDataTable();
totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(result);
}
}
public System.Data.DataTable QueryAdventProductsList(QueryFilter.Inventory.AdventProductsQueryFilter queryFilter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = queryFilter.PageInfo.SortBy;
pagingEntity.MaximumRows = queryFilter.PageInfo.PageSize;
pagingEntity.StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Inventory_QueryAdventProductsList");
using (var sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "ring.SysNo DESC"))
{
if (queryFilter.BrandSysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ring.BrandSysNo", DbType.Int32, "@BrandSysNo", QueryConditionOperatorType.Equal, queryFilter.BrandSysNo);
}
if (queryFilter.CategoryC3SysNo.HasValue)
{
sb.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ring.C3SysNo", DbType.Int32, "@C3SysNo", QueryConditionOperatorType.Equal, queryFilter.CategoryC3SysNo);
}
cmd.CommandText = sb.BuildQuerySql();
var resultData = cmd.ExecuteDataTable();
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(resultData);
}
}
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);
}
}
public static DataTable QueryStockShipType(StockShipTypeQueryFilter filter, out int count)
{
CustomDataCommand command = DataCommandManager.
CreateCustomDataCommandFromConfig("Query_Stock_QueryStockShipType");
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(
command.CommandText, command, filter, !string.IsNullOrWhiteSpace(filter.SortFields) ? filter.SortFields : "ss.SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"ss.SellerSysNo", DbType.String,
"@SellerSysNo", QueryConditionOperatorType.Equal,
filter.SellerSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"ss.StockSysNo", DbType.AnsiStringFixedLength,
"@StockSysNo", QueryConditionOperatorType.Equal,
filter.StockSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"ss.ShipTypeSysNo", DbType.AnsiStringFixedLength,
"@ShipTypeSysNo", QueryConditionOperatorType.Equal,
filter.ShipTypeSysNo);
command.CommandText = builder.BuildQuerySql();
count = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
return(command.ExecuteDataTable());
}
}
/// <summary>
/// 查询配件
/// </summary>
/// <returns></returns>
public virtual DataTable QueryAccessory(AccessoryQueryFilter queryCriteria, out int totalCount)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryAccessory");
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, "AccessoryInfo.SysNo DESC"))
{
if (!String.IsNullOrEmpty(queryCriteria.AccessoryName))
{
dataCommand.AddInputParameter("@AccessoryName", DbType.String, queryCriteria.AccessoryName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"AccessoriesName",
DbType.String, "@AccessoryName",
QueryConditionOperatorType.Like,
queryCriteria.AccessoryName);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = dataCommand.ExecuteDataTable();
totalCount = Convert.ToInt32(dataCommand.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);
}
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);
}
}
/// <summary>
/// 月销售情况报表
/// </summary>
/// <param name="filter">条件</param>
/// <returns></returns>
public static StatementResult <ArrayList> MonthSalesData(MonthSalesFilter filter)
{
StatementResult <ArrayList> result = new StatementResult <ArrayList>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("MonthSalesData");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "LogDate DESC"))
{
DateTime beginDate = DateTime.Parse(string.Format("{0}-{1}-01", filter.Year, filter.Month));
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "LogDate", DbType.DateTime,
"@BeginLogDate", QueryConditionOperatorType.MoreThanOrEqual, beginDate);
DateTime endDate = beginDate.AddMonths(1);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "LogDate", DbType.DateTime,
"@EndLogDate", QueryConditionOperatorType.LessThan, endDate);
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.Result = DbHelper.DatatableConvertArrayList(cmd.ExecuteDataTable());
result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
result.TotalValue = Convert.ToDecimal(cmd.GetParameterValue("@TotalAmount"));
return(result);
}
}
public DataTable QueryRMAList(PurchaseOrderQueryFilter queryFilter, out int totalCount)
{
DataTable dt = new DataTable();
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetRMA");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = queryFilter.PageInfo.SortBy,
StartRowIndex = queryFilter.PageInfo.PageIndex * queryFilter.PageInfo.PageSize,
MaximumRows = queryFilter.PageInfo.PageSize
};
using (DynamicQuerySqlBuilder builder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "VendorName,OutTime,PMUserSysNo"))
{
string sql = "WHERE 1=1 ";
if (queryFilter.PMList != null && queryFilter.PMList.Count > 0)
{
sql += "AND Product.PMUserSysNo IN (";
foreach (var pm in queryFilter.PMList)
{
sql += pm.ToString() + " , ";
}
sql = sql.Substring(0, sql.LastIndexOf(",") - 1) + ")";
}
sql += " AND RMA_Register.ResponseDesc IS NOT NULL AND DateAdd(Day,15,RMA_OutBound.OutTime)< '" + DateTime.Now + "'";
sql += " AND RMA_Register.Status=1 AND RMA_Register.OutBoundStatus = 1 ";
sql += " AND RMA_OutBound.CompanyCode= '" + queryFilter.CompanyCode + "'";
dataCommand.CommandText = builder.BuildQuerySql().Replace("StrWhere", sql);
dt = dataCommand.ExecuteDataTable();
totalCount = (int)dataCommand.GetParameterValue("@TotalCount");
}
return(dt);
}
public DataTable QueryPurchaseOrderBatchNumberList(PurchaseOrderBatchNumberQueryFilter 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 dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("SearchPOBatchNumberList");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "batch.ExpDate desc"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "batch.ProductSysNo",
DbType.Int32, "@ProductSysNo", QueryConditionOperatorType.Equal, queryFilter.ProductSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "batch.StockSysNo",
DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, queryFilter.StockSysNo);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
if (pagingInfo != null)
{
dataCommand.SetParameterValue("@StartNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex);
dataCommand.SetParameterValue("@EndNumber", queryFilter.PageInfo.PageSize * queryFilter.PageInfo.PageIndex + queryFilter.PageInfo.PageSize);
}
}
dt = dataCommand.ExecuteDataTable("Status", typeof(PurchaseOrderBatchInfoStatus));
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
public DataTable QueryShipTypePayType(QueryFilter.Common.ShipTypePayTypeQueryFilter filter, out int totalCount)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("QueryShipTypePayType");
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, "sp.SysNo DESC"))
{
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.Equal,
filter.SysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"ShipTypeSysNo", DbType.Int32,
"@ShipTypeSysNo", QueryConditionOperatorType.Equal,
filter.ShipTypeSysNo);
builder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"PayTypeSysNo", DbType.Int32,
"@PayTypeSysNo", QueryConditionOperatorType.Equal,
filter.PayTypeSysNo);
command.CommandText = builder.BuildQuerySql();
DataTable dt = command.ExecuteDataTable();
totalCount = Convert.ToInt32(command.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
}
public DataTable QueryWPMessageByUserSysNo(WPMessageQueryFilter filter, out int dataCount)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("WPMessage_QueryMessage");
using (DynamicQuerySqlBuilder queryBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, ToPagingInfo(filter.PageIndex, filter.PageSize, filter.SortField), "m.CategorySysNo DESC,m.SysNo"))
{
queryBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[CreateTime]", DbType.DateTime, "@BeginCreateTime", QueryConditionOperatorType.MoreThanOrEqual, filter.BeginCreateTime);
queryBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[CreateTime]", DbType.DateTime, "@EndCreateTime", QueryConditionOperatorType.LessThan, filter.EndCreateTime);
queryBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[CategorySysNo]", DbType.Int32, "@CategorySysNo", QueryConditionOperatorType.Equal, filter.CategorySysNo);
if (filter.WPMessageStatus.HasValue)
{
queryBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[Status]", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, filter.WPMessageStatus);
}
else
{
queryBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "m.[Status]", DbType.Int32, "@Status", QueryConditionOperatorType.NotEqual, WPMessageStatus.Completed);
}
queryBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, " wc.[Status] = 1");
command.CommandText = queryBuilder.BuildQuerySql();
}
command.SetParameterValue("@UserSysNo", filter.UserSysNo);
DataTable dt = command.ExecuteDataTable();
object o = command.GetParameterValue("@TotalCount");
dataCount = o == null ? 0 : (int)o;
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);
}
}
public DataTable QueryStorePageInfo(StorePageQueryFilter filter, out int totalCount)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryStorePageInfo");
PagingInfoEntity pagingInfo = new PagingInfoEntity()
{
SortField = filter.PageInfo.SortBy,
StartRowIndex = filter.PageInfo.PageIndex * filter.PageInfo.PageSize,
MaximumRows = filter.PageInfo.PageSize
};
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(dataCommand.CommandText, dataCommand, pagingInfo, "sp.SysNo desc"))
{
if (filter.MerchantSysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sp.SellerSysNo",
DbType.Int32, "@SellerSysNo", QueryConditionOperatorType.Equal, filter.MerchantSysNo.Value);
}
if (!string.IsNullOrEmpty(filter.PageType))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "sp.PageTypeKey",
DbType.String, "@PageTypeKey", QueryConditionOperatorType.Equal, filter.PageType);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = dataCommand.ExecuteDataTable();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 根据商品编号列表 获取商品关税信息列表
/// </summary>
/// <param name="sysNos">商品编号列表</param>
/// <returns></returns>
public DataTable GetProductTariffInfoProductSysNos(List <int> sysNos)
{
CustomDataCommand dc = DataCommandManager.CreateCustomDataCommandFromConfig("GetProductTariffInfoByProductSysNos");
dc.CommandText = dc.CommandText.Replace("#SysNos#", String.Join(",", sysNos));
return(dc.ExecuteDataTable());
}
public virtual DataTable GetETC(string webChannelID, out int totalCount)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryETC");
DataTable dt = cmd.ExecuteDataTable();
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
/// <summary>
/// 商品组查询
/// </summary>
/// <param name="queryCriteria"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public virtual DataTable QueryProductGroupInfo(ProductGroupQueryFilter queryCriteria, out int totalCount)
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryProductGroupInfo");
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, "pgi.SysNo DESC"))
{
if (!String.IsNullOrEmpty(queryCriteria.ProductGroupName))
{
dataCommand.AddInputParameter("@ProductGroupName", DbType.String, queryCriteria.ProductGroupName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"pgi.ProductName",
DbType.String, "@ProductGroupName",
QueryConditionOperatorType.Like,
queryCriteria.ProductGroupName);
}
if (queryCriteria.C3SysNo.HasValue)
{
dataCommand.AddInputParameter("@C3SysNo", DbType.Int32, queryCriteria.C3SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"pgi.C3SysNo",
DbType.Int32, "@C3SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.C3SysNo);
}
if (queryCriteria.C2SysNo.HasValue)
{
dataCommand.AddInputParameter("@C2SysNo", DbType.Int32, queryCriteria.C2SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"C.C2SysNo",
DbType.Int32, "@C2SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.C2SysNo);
}
if (queryCriteria.C1SysNo.HasValue)
{
dataCommand.AddInputParameter("@C1SysNo", DbType.Int32, queryCriteria.C1SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"D.C1SysNo",
DbType.Int32, "@C1SysNo",
QueryConditionOperatorType.Equal,
queryCriteria.C1SysNo);
}
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist,
" Select TOP 1 P.SysNo from IPP3.dbo.Product P WITH(NOLOCK) " +
"inner join OverseaContentManagement.dbo.ProductCommonInfo c WITH(NOLOCK) " +
"on c.SysNo=P.ProductCommonInfoSysno where c.ProductGroupSysno=pgi.SysNO");
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = dataCommand.ExecuteDataTable();
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);
}
}
public DataTable QueryTotalAmountByVendor(POVendorInvoiceQueryFilter 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("GetTotalAmountByVendor");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingEntity, "VendorSysNo"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vi.VendorSysNo",
DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, query.VendorSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vi.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, query.CompanyCode);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vi.InvoiceNumber",
DbType.AnsiString, "@InvoiceNumber", QueryConditionOperatorType.Like, query.InvoiceNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vi.InvoiceTime",
DbType.DateTime, "@InvoiceDateFrom", QueryConditionOperatorType.MoreThanOrEqual, query.InvoiceDateFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vi.InvoiceTime",
DbType.DateTime, "@InvoiceDateTo", QueryConditionOperatorType.LessThan, query.InvoiceDateTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vi.InputTime",
DbType.DateTime, "@InvoiceCreateDateFrom", QueryConditionOperatorType.MoreThanOrEqual, query.InvoiceCreateDateFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vi.InputTime",
DbType.DateTime, "@InvoiceCreateDateTo", QueryConditionOperatorType.LessThan, query.InvoiceCreateDateTo);
if (query.Status.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vi.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.Equal, query.Status);
}
else
{
if (query.IsFilterAbandon.Value)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vi.Status",
DbType.Int32, "@Status", QueryConditionOperatorType.MoreThan, -1);
}
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "vi.StockSysNo",
DbType.Int32, "@StockSysNo", QueryConditionOperatorType.Equal, query.StockSysNo);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = dataCommand.ExecuteDataTable();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
/// <summary>
/// 查询配送方式-产品
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable QueryShipTypeAreaPriceList(ShipTypeAreaPriceQueryFilter filter, out int TotalCount)
{
MapSortField(filter);
TotalCount = 0;
PagingInfoEntity pagingInfo = ToPagingInfo(filter.PagingInfo);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ShipTypeAreaPrice_QueryShipTypeAreaPrice");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingInfo, "SAP.SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SAP.ShipTypeSysNo", DbType.Int32, "@ShipTypeSysNo", QueryConditionOperatorType.Equal, filter.ShipTypeSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SAP.MerchantSysNo", DbType.Int32, "@VendorSysNo", QueryConditionOperatorType.Equal, filter.VendorSysNo);
if (filter.CompanyCustomer.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SAP.CompanyCustomer", DbType.Int32, "@CompanyCustomer", QueryConditionOperatorType.Equal, (int)filter.CompanyCustomer.Value);
}
if (!string.IsNullOrEmpty(filter.AreaName))
{
using (GroupCondition g = new GroupCondition(sqlBuilder, QueryConditionRelationType.AND))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.ProvinceName", DbType.String, "@ProvinceName", QueryConditionOperatorType.Equal, filter.AreaName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "A.CityName", DbType.String, "@CityName", QueryConditionOperatorType.Equal, filter.AreaName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "A.DistrictName", DbType.String, "@DistrictName", QueryConditionOperatorType.Equal, filter.AreaName);
}
}
if (filter.AreaSysNo.HasValue)
{
using (GroupCondition g = new GroupCondition(sqlBuilder, QueryConditionRelationType.AND))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.SysNO", DbType.String, "@SysNO", QueryConditionOperatorType.Equal, filter.AreaSysNo);
}
}
else if (filter.CitySysNo.HasValue)
{
using (GroupCondition g = new GroupCondition(sqlBuilder, QueryConditionRelationType.AND))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.SysNo", DbType.String, "@SysNo", QueryConditionOperatorType.Equal, filter.CitySysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "A.CitySysNo", DbType.String, "@CitySysNo", QueryConditionOperatorType.Equal, filter.CitySysNo);
}
}
else if (filter.ProvinceSysNo.HasValue)
{
using (GroupCondition g = new GroupCondition(sqlBuilder, QueryConditionRelationType.AND))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "A.SysNo", DbType.String, "@SysNo", QueryConditionOperatorType.Equal, filter.ProvinceSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.OR, "A.ProvinceSysNo", DbType.String, "@ProvinceSysNo", QueryConditionOperatorType.Equal, filter.ProvinceSysNo);
}
}
cmd.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = cmd.ExecuteDataTable("CompanyCustomer", typeof(ECCentral.BizEntity.Common.CompanyCustomer));
TotalCount = int.Parse(cmd.GetParameterValue("@TotalCount").ToString());
return(dt);
}
}
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);
}
}
