/// <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 FinanceExport(FinanceQueryFilter filter, out int totalCount, out double totalPayAmt)
{
string sqlName = string.Empty;
string orderStr = "SysNo DESC";
if (filter.IsGroupByVendor == true)
{
sqlName = "Invoice_Query_FinanceGroupByVendor";
}
else
{
sqlName = "Invoice_Query_Finance";
if (!string.IsNullOrEmpty(filter.PagingInfo.SortBy))
{
orderStr = filter.PagingInfo.SortBy;
}
}
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig(sqlName);
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), orderStr))
{
cmd.CommandText = sb.BuildQuerySql();
if (filter.IsGroupByVendor == true)
{
AddFinanceParametersGroupByVendor(cmd, filter);
}
else
{
AddFinanceParameters(filter, cmd);
}
//合计已到应付总额
cmd.AddOutParameter("@TotalPayableAmt", DbType.Double, 12);
DataTable dt = cmd.ExecuteDataTable();
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
var totalPayableAmtParam = cmd.GetParameterValue("@TotalPayableAmt");
if (totalPayableAmtParam != DBNull.Value)
{
totalPayAmt = Convert.ToDouble(totalPayableAmtParam);
}
else
{
totalPayAmt = 0.0;
}
return(dt);
}
}
private DataSet ExecuteDataTable(CustomDataCommand dataCommand, BalanceAccountQueryFilter filter, out int totalCount)
{
DataSet result = new DataSet();
EnumColumnList enumColumns = new EnumColumnList();
enumColumns.Add("PrepayType", typeof(BalanceAccountPrepayType));
DataTable resultDT = dataCommand.ExecuteDataTable(enumColumns);
resultDT.TableName = "DataResult";
result.Tables.Add(resultDT);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
#region 计算统计结果
decimal TotalStartBalance = Convert.ToDecimal(dataCommand.GetParameterValue("@StartBalance"));
decimal TotalEndBalance = Convert.ToDecimal(dataCommand.GetParameterValue("@EndBalance"));
decimal PayedIn = Convert.ToDecimal(dataCommand.GetParameterValue("@PayedIn"));
decimal PayedOut = Convert.ToDecimal(dataCommand.GetParameterValue("@PayedOut"));
if (resultDT != null && resultDT.Rows.Count > 0)
{
//没有开始时间时每位客户的期初以及所有客户期初的总额都为0.00
if (!filter.CreateTimeFrom.HasValue)
{
TotalStartBalance = 0.00M;
foreach (DataRow row in resultDT.Rows)
{
row["StartBalance"] = 0.00M;
}
}
}
DataTable statisticDT = new DataTable("StatisticResult");
statisticDT.Columns.AddRange(new DataColumn[]
{
new DataColumn("TotalStartBalance", typeof(decimal)),
new DataColumn("TotalEndBalance", typeof(decimal)),
new DataColumn("PayedIn", typeof(decimal)),
new DataColumn("PayedOut", typeof(decimal))
});
statisticDT.Rows.Add(TotalStartBalance, TotalEndBalance, PayedIn, PayedOut);
result.Tables.Add(statisticDT);
#endregion 计算统计结果
return(result);
}
/// <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>
/// 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 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>
/// <param name="filter">查询条件</param>
/// <returns></returns>
public static QueryResult <Functions> QueryFunctions(FunctionsQueryFilter filter)
{
QueryResult <Functions> result = new QueryResult <Functions>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryFunctions");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32,
"@Status1", QueryConditionOperatorType.NotEqual, CommonStatus.Deleted);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.Equal, filter.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "FunctionName", DbType.String,
"@FunctionName", QueryConditionOperatorType.Like, filter.FunctionName);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "FunctionKey", DbType.String,
"@FunctionKey", QueryConditionOperatorType.Like, filter.FunctionKey);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Description", DbType.String,
"@Description", QueryConditionOperatorType.Like, filter.Description);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32,
"@Status", QueryConditionOperatorType.Equal, filter.Status);
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.ResultList = cmd.ExecuteEntityList <Functions>();
result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(result);
}
}
/// <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);
}
}
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>
/// <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);
}
}
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>
/// <param name="pageInfo"></param>
/// <param name="customerSysNo"></param>
/// <returns></returns>
public static QueryResult <GroupBuyingTicketInfo> QueryGroupBuyingTicketInfo(PageInfo pageInfo, int customerSysNo)
{
QueryResult <GroupBuyingTicketInfo> result = new QueryResult <GroupBuyingTicketInfo>();
result.PageInfo = pageInfo;
PagingInfoEntity page = new PagingInfoEntity();
page.MaximumRows = pageInfo.PageSize;
page.StartRowIndex = pageInfo.PageIndex * pageInfo.PageSize;
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GroupBuying_QueryGroupBuyingTicketInfo");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "Ticket.OrderSysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Ticket.[CustomerSysNo]", DbType.Int32, "@CustomerSysNo", QueryConditionOperatorType.Equal, customerSysNo);
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.ResultList = cmd.ExecuteEntityList <GroupBuyingTicketInfo>();
int totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
result.PageInfo.TotalCount = totalCount;
return(result);
}
}
/// <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 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);
}
}
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 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="filter">查询条件</param>
/// <returns></returns>
public static QueryResult <Logs> QueryLogs(LogsQueryFilter filter)
{
QueryResult <Logs> result = new QueryResult <Logs>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryLogs");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Classes", DbType.Int32,
"@Classes", QueryConditionOperatorType.Like, filter.Classes);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Section", DbType.Int32,
"@Section", QueryConditionOperatorType.Like, filter.Section);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Family", DbType.Int32,
"@Family", QueryConditionOperatorType.Like, filter.Family);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "RefenceSysNo", DbType.Int32,
"@RefenceSysNo", QueryConditionOperatorType.Like, filter.RefenceSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Contents", DbType.String,
"@Contents", QueryConditionOperatorType.Like, filter.Contents);
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.ResultList = cmd.ExecuteEntityList <Logs>();
result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(result);
}
}
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>
/// <returns></returns>
public static QueryResult <GiftsGrantRecord> QueryGiftsGrantRecord(GiftsGrantRecordQueryFilter filter)
{
QueryResult <GiftsGrantRecord> result = new QueryResult <GiftsGrantRecord>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryGiftsGrantRecord");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "M.SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.SysNo", DbType.Int32,
"@SysNo", QueryConditionOperatorType.Equal, filter.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GiftSysNo", DbType.Int32,
"@GiftSysNo", QueryConditionOperatorType.Equal, filter.GiftSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantCounts", DbType.Int64,
"@GrantCounts", QueryConditionOperatorType.Like, filter.GrantCounts);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantDate", DbType.DateTime,
"@GrantDate", QueryConditionOperatorType.Like, filter.GrantDate);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantPerson", DbType.String,
"@GrantPerson", QueryConditionOperatorType.Like, filter.GrantPerson);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "M.GrantPlace", DbType.String,
"@GrantPlace", QueryConditionOperatorType.Like, filter.GrantPlace);
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.ResultList = cmd.ExecuteEntityList <GiftsGrantRecord>();
result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(result);
}
}
/// <summary>
/// 分页查询新闻公告
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static QueryResult <NewsInfo> QueryNewsInfo(NewsQueryFilter filter)
{
CustomDataCommand command = DataCommandManager.CreateCustomDataCommandFromConfig("Content_QueryTopic");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(command.CommandText, command, filter.ConvertToPaging(), "TopMost DESC,SysNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "SysNo", DbType.Int32, "@SysNo", QueryConditionOperatorType.Equal, filter.SysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "Status", DbType.Int32, "@Status", QueryConditionOperatorType.Equal, 1);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ReferenceSysNo", DbType.Int32, "@ReferenceSysNo", QueryConditionOperatorType.Equal, filter.ReferenceSysNo);
if (filter.NewsType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "NewsType", DbType.Int32, "@NewsType", QueryConditionOperatorType.Equal, (int)filter.NewsType.Value);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "PageShowInheritance", DbType.Int32, "@PageShowInheritance", QueryConditionOperatorType.Equal, filter.PageShowInheritance);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ExpireDate", DbType.Time, "@ExpireDateBegin", QueryConditionOperatorType.MoreThan, DateTime.Now);
command.CommandText = sqlBuilder.BuildQuerySql();
var newsList = command.ExecuteEntityList <NewsInfo>();
var totalCount = Convert.ToInt32(command.GetParameterValue("@TotalCount"));
QueryResult <NewsInfo> result = new QueryResult <NewsInfo>();
result.ResultList = newsList;
result.PageInfo = filter.ConvertToPageInfo(totalCount);
return(result);
}
}
/// <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);
}
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 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 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 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 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>
/// 查询配送方式-产品
/// </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);
}
}
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);
}
}
