public QueryResult QueryCustomerPointLog(ECCentral.QueryFilter.Customer.CustomerPointLogQueryFilter request)
{
int totalCount;
DataTable dataTable = ObjectFactory <ICustomerQueryDA> .Instance.QueryCustomerPointLog(request, out totalCount);
return(new QueryResult()
{
Data = dataTable,
TotalCount = totalCount
});
}
/// <summary>
/// 积分历史查询
/// </summary>
/// <param name="queryCriteria"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public virtual DataTable QueryCustomerPointLog(ECCentral.QueryFilter.Customer.CustomerPointLogQueryFilter queryCriteria, out int totalCount)
{
totalCount = 0;
DataTable dt = null;
if (queryCriteria.IsCashPoint.HasValue && queryCriteria.IsCashPoint.Value == YNStatus.Y)
{
//现金转积分（只有获得历史）
dt = QueryCustomerPointLogByForOther(queryCriteria, out totalCount);
}
else
{
//queryCriteria.ResultType=1 获得历史，queryCriteria.ResultType=-1 消费历史
dt = QueryCustomerPointLogForAuto(queryCriteria, out totalCount);
}
return(dt);
}
/// <summary>
/// 现金转积分 的 积分历史记录
/// </summary>
/// <param name="queryCriteria"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
private DataTable QueryCustomerPointLogByForOther(ECCentral.QueryFilter.Customer.CustomerPointLogQueryFilter queryCriteria, out int totalCount)
{
totalCount = 0;
var customCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryOtherPointLog");
PagingInfoEntity pagingInfo = new PagingInfoEntity();
pagingInfo.SortField = queryCriteria.PagingInfo.SortBy;
pagingInfo.StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize;
pagingInfo.MaximumRows = queryCriteria.PagingInfo.PageSize;
using (var sqlBuilder = new DynamicQuerySqlBuilder(customCommand.CommandText, customCommand,
pagingInfo,
"a.SysNo asc"))
{
if (queryCriteria.CustomerSysNo.HasValue && queryCriteria.CustomerSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.CustomerSysNo",
DbType.Int32,
"@CustomerSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.CustomerSysNo);
}
if (queryCriteria.PointType.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ObtainType",
DbType.Int32,
"@ObtainType",
QueryConditionOperatorType.Equal,
queryCriteria.PointType);
}
if (queryCriteria.IsUseCreateDate)
{
if (queryCriteria.CreateTimeFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Indate", DbType.DateTime, "@CreateTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
queryCriteria.CreateTimeFrom);
}
if (queryCriteria.CreateTimeTo.HasValue)
{
//选择的DateTime只有日期部分，查询包含当日的信息所以日期加1
//2012-1-1 --- 2012-1-2 则为 >= 2012-1-1 0:0:0 and < 2012-1-3 0:0:0
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Indate", DbType.DateTime, "@CreateTimeTo",
QueryConditionOperatorType.LessThan,
queryCriteria.CreateTimeTo.Value);
}
}
//现金转积分
sqlBuilder.ConditionConstructor.AddCustomCondition(QueryConditionRelationType.AND, @"
(([ObtainType] = 19
AND (memo ='客户多汇款转积分'
OR memo = '客户多付款－产品调价'
OR memo = '客户多付款－拆单/运费转积分'
OR memo = '客户多付款－客户作废/更改订单需要转积分'))
OR [ObtainType] IN (39,37,44)) " );
//if (!string.IsNullOrEmpty(queryCriteria.CompanyCode))
//{
// sqlBuilder.ConditionConstructor.AddCondition(
// QueryConditionRelationType.AND,
// "a.CompanyCode",
// DbType.AnsiStringFixedLength,
// "@CompanyCode",
// QueryConditionOperatorType.Equal,
// queryCriteria.CompanyCode);
//}
customCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = customCommand.ExecuteDataTable();
if (null != dt && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
dr["PointLogTypeName"] = CodeNamePairManager.GetName("Customer", "AdjustPointType", dr["pointlogtype"].ToString());
}
}
totalCount = int.Parse(customCommand.GetParameterValue("TotalCount").ToString());
return(dt);
}
}
/// <summary>
/// 积分历史记录
/// </summary>
/// <param name="queryCriteria"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
private DataTable QueryCustomerPointLogForAuto(ECCentral.QueryFilter.Customer.CustomerPointLogQueryFilter queryCriteria, out int totalCount)
{
totalCount = 0;
CustomDataCommand customCommand = null;
string orderField = string.Empty;
if (queryCriteria.ResultType == 1)
{
//获取历史
customCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPointObtainLog");
orderField = "a.SysNo asc";
}
else
{
//消费历史
customCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPointConsumeLog");
orderField = "a.SysNo desc";
}
PagingInfoEntity pagingInfo = new PagingInfoEntity();
pagingInfo.SortField = queryCriteria.PagingInfo.SortBy;
pagingInfo.StartRowIndex = queryCriteria.PagingInfo.PageIndex * queryCriteria.PagingInfo.PageSize;
pagingInfo.MaximumRows = queryCriteria.PagingInfo.PageSize;
using (var sqlBuilder = new DynamicQuerySqlBuilder(customCommand.CommandText, customCommand,
pagingInfo,
orderField))
{
if (queryCriteria.CustomerSysNo.HasValue && queryCriteria.CustomerSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.CustomerSysNo",
DbType.Int32,
"@CustomerSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.CustomerSysNo);
}
if (queryCriteria.OrderSysNo.HasValue && queryCriteria.OrderSysNo > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"a.SoSysNo",
DbType.Int32,
"@SoSysNo",
QueryConditionOperatorType.Equal,
queryCriteria.OrderSysNo);
}
if (queryCriteria.PointType.HasValue)
{
string fieldName = queryCriteria.ResultType == 1 ? "a.ObtainType" : "a.ConsumeType";
string parameterName = queryCriteria.ResultType == 1 ? "@ObtainType" : "@ConsumeType";
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
fieldName,
DbType.Int32,
parameterName,
QueryConditionOperatorType.Equal,
queryCriteria.PointType);
}
if (queryCriteria.IsUseCreateDate)
{
if (queryCriteria.CreateTimeFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Indate", DbType.DateTime, "@CreateTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
queryCriteria.CreateTimeFrom);
}
if (queryCriteria.CreateTimeTo.HasValue)
{
//选择的DateTime只有日期部分，查询包含当日的信息所以日期加1
//2012-1-1 --- 2012-1-2 则为 >= 2012-1-1 0:0:0 and < 2012-1-3 0:0:0
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Indate", DbType.DateTime, "@CreateTimeTo",
QueryConditionOperatorType.LessThan,
queryCriteria.CreateTimeTo.Value);
}
}
//if (!string.IsNullOrEmpty(queryCriteria.CompanyCode))
//{
// sqlBuilder.ConditionConstructor.AddCondition(
// QueryConditionRelationType.AND,
// "a.CompanyCode",
// DbType.AnsiStringFixedLength,
// "@CompanyCode",
// QueryConditionOperatorType.Equal,
// queryCriteria.CompanyCode);
//}
customCommand.CommandText = sqlBuilder.BuildQuerySql();
DataTable dt = customCommand.ExecuteDataTable();
if (null != dt && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
dr["PointLogTypeName"] = CodeNamePairManager.GetName("Customer", "AdjustPointType", dr["pointlogtype"].ToString());
}
}
totalCount = int.Parse(customCommand.GetParameterValue("TotalCount").ToString());
return(dt);
}
}
