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>
/// set the ticketsysno, startdata and enddate
/// </summary>
/// <param name="command"></param>
private void SetTheSearchCondition(DynamicQuerySqlBuilder sqlBuilder,
QueryFilter.Common.LogQueryFilter logquery)
{
if (logquery.ISSOLog)
{
sqlBuilder.ConditionConstructor.AddCondition
(QueryConditionRelationType.AND, "[LOG].SOSysNo",
DbType.Int32, "@SOSysNo",
QueryConditionOperatorType.Equal,
logquery.TicketSysNo);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition
(QueryConditionRelationType.AND, "[LOG].TicketSysNo",
DbType.Int32, "@TicketSysNo",
QueryConditionOperatorType.Equal,
logquery.TicketSysNo);
}
sqlBuilder.ConditionConstructor.AddCondition
(QueryConditionRelationType.AND, "[LOG].OPtTime",
DbType.DateTime, "@StartOPtTime",
QueryConditionOperatorType.MoreThanOrEqual,
logquery.StartDate);
if (logquery.EndDate.HasValue)
{
var endDate = Convert.ToDateTime
(logquery.EndDate.Value.ToShortDateString() + " 23:59:59");
sqlBuilder.ConditionConstructor.AddCondition
(QueryConditionRelationType.AND, "[LOG].OPtTime",
DbType.String, "@EndOPtTime",
QueryConditionOperatorType.LessThanOrEqual,
endDate);
}
if (logquery.CancelOutStore)
{
sqlBuilder.ConditionConstructor.AddCondition
(QueryConditionRelationType.AND, "[LOG].TicketType",
DbType.Int32, "@TicketType",
QueryConditionOperatorType.Equal,
(int)c.BizLogType.Sale_SO_CancelOutStock);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "LOG.CompanyCode", DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, "8601");
}
