/// <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>
/// <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>
/// <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);
}
}
/// <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);
}
}
/// <summary>
/// 查询近7天未下过订单的用户
/// </summary>
/// <param name="filter">查询条件</param>
/// <returns></returns>
public static StatementResult <ArrayList> QuerySevenDayNotHaveOrder(QueryCategoryCustomersFilter filter)
{
StatementResult <ArrayList> result = new StatementResult <ArrayList>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QuerySevenDayNotHaveOrder");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "CustomerID DESC"))
{
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.Result = DbHelper.DatatableConvertArrayList(cmd.ExecuteDataTable());
result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(result);
}
}
/// <summary>
/// 用户流失分析报表
/// </summary>
/// <param name="filter">条件</param>
/// <returns></returns>
public static StatementResult <ArrayList> CustomersLoseAnalyze(CustomersLoseAnalyzeFilter filter)
{
StatementResult <ArrayList> result = new StatementResult <ArrayList>();
result.ServicePageIndex = filter.ServicePageIndex;
result.PageSize = filter.PageSize;
PagingInfoEntity page = DataAccessUtil.ToPagingInfo(filter);
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("CustomersLoseAnalyze");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, page, "LogDate ASC"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "LogDate", DbType.DateTime,
"@LogDate", QueryConditionOperatorType.LessThan, DateTime.Parse(DateTime.Now.AddDays(-1 * filter.Day).ToShortDateString()));
cmd.CommandText = sqlBuilder.BuildQuerySql();
result.Result = DbHelper.DatatableConvertArrayList(cmd.ExecuteDataTable());
result.TotalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(result);
}
}