/***********************************************************************
|
* Project: baifenBinfa
|
* ProjectName: 百分兵法管理系统
|
* Web: http://chuanyin.com
|
* Author:
|
* Email:
|
* CreateTime: 202403/02
|
* Description: 暂无
|
***********************************************************************/
|
|
using System;
|
using System.Collections.Generic;
|
using System.Threading.Tasks;
|
using CoreCms.Net.Configuration;
|
using CoreCms.Net.IRepository;
|
using CoreCms.Net.IRepository.UnitOfWork;
|
using CoreCms.Net.IServices;
|
using CoreCms.Net.Model.Entities;
|
using CoreCms.Net.Model.ViewModels.Basics;
|
using CoreCms.Net.Model.ViewModels.Echarts;
|
using CoreCms.Net.Model.ViewModels.UI;
|
using CoreCms.Net.Utility.Helper;
|
using SqlSugar;
|
|
|
namespace CoreCms.Net.Services
|
{
|
/// <summary>
|
/// 报表 接口实现
|
/// </summary>
|
public class CoreCmsReportsServices : BaseServices<GetOrdersReportsDbSelectOut>, ICoreCmsReportsServices
|
{
|
private readonly ICoreCmsReportsRepository _dal;
|
private readonly IUnitOfWork _unitOfWork;
|
public CoreCmsReportsServices(IUnitOfWork unitOfWork, ICoreCmsReportsRepository dal)
|
{
|
this._dal = dal;
|
base.BaseDal = dal;
|
_unitOfWork = unitOfWork;
|
}
|
|
/// <summary>
|
/// 订单报表
|
/// </summary>
|
/// <param name="num">数量</param>
|
/// <param name="where">查询条件</param>
|
/// <param name="section">查询值</param>
|
/// <param name="sTime">开始时间</param>
|
/// <param name="joinVal">筛选字段createTime/paymentTime </param>
|
/// <returns></returns>
|
public List<GetOrdersReportsDbSelectOut> GetOrderMark(int num, string where, int section, DateTime sTime, string joinVal)
|
{
|
|
var sqlStr = string.Empty;
|
string dbTypeString = AppSettingsConstVars.DbDbType;
|
if (dbTypeString == DbType.SqlServer.ToString())
|
{
|
sqlStr = @"SELECT tmp_x.number ,
|
ISNULL(SUM(o.orderAmount), 0) AS val ,
|
COUNT(o.orderId) AS num
|
FROM ( ( SELECT number
|
FROM MASTER..spt_values
|
WHERE TYPE = 'P'
|
AND number >= 0
|
AND number <= " + num + @"
|
) tmp_x
|
LEFT OUTER JOIN ( SELECT *
|
FROM CoreCmsOrder
|
WHERE isdel=0 " + where + @"
|
) o ON tmp_x.number = DATEDIFF(" + (section == 3600 ? "HOUR" : "DAY") + ", '" + sTime.ToString("yyyy-MM-dd HH:mm:ss") + @"', o." + joinVal + @")
|
)
|
GROUP BY tmp_x.number";
|
}
|
else if (dbTypeString == DbType.MySql.ToString())
|
{
|
sqlStr = @"select tmp_x.x,ifnull(sum(o.orderAmount),0) as val,count(o.orderId) as num
|
from
|
(SELECT @xi:=@xi+1 as x from
|
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
|
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
|
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
|
(SELECT @xi:=-1) x0 limit 0," + num + @") tmp_x
|
left join (
|
select * from CoreCmsOrder
|
where 1 = 1
|
" + where + @"
|
) o on tmp_x.x = ((unix_timestamp(o." + joinVal + @") - " + CommonHelper.ConvertDateTimeToInt(sTime) + @") div(" + section + @"))
|
group by tmp_x.x
|
";
|
}
|
Console.WriteLine(sqlStr);
|
var sp = new List<SugarParameter>();
|
|
var list = _dal.SqlQuery(sqlStr, sp);
|
|
return list;
|
}
|
|
|
/// <summary>
|
/// 支付单报表
|
/// </summary>
|
/// <param name="num">数量</param>
|
/// <param name="where">查询条件</param>
|
/// <param name="section">查询值</param>
|
/// <param name="sTime">开始时间</param>
|
/// <param name="joinVal">筛选字段createTime/paymentTime </param>
|
/// <returns></returns>
|
public List<GetOrdersReportsDbSelectOut> GetPaymentsMark(int num, string where, int section, DateTime sTime, string joinVal)
|
{
|
var sqlStr = string.Empty;
|
string dbTypeString = AppSettingsConstVars.DbDbType;
|
if (dbTypeString == DbType.SqlServer.ToString())
|
{
|
sqlStr = @"SELECT tmp_x.number ,
|
ISNULL(SUM(o.money), 0) AS val ,
|
COUNT(o.paymentId) AS num
|
FROM ( ( SELECT number
|
FROM MASTER..spt_values
|
WHERE TYPE = 'P'
|
AND number >= 0
|
AND number <= " + num + @"
|
) tmp_x
|
LEFT OUTER JOIN ( SELECT *
|
FROM CoreCmsBillPayments
|
WHERE 1=1 " + where + @"
|
) o ON tmp_x.number = DATEDIFF(" + (section == 3600 ? "HOUR" : "DAY") + ", '" + sTime.ToString("yyyy-MM-dd HH:mm:ss") + @"', o." + joinVal + @")
|
)
|
GROUP BY tmp_x.number";
|
}
|
else if (dbTypeString == DbType.MySql.ToString())
|
{
|
sqlStr = @"select tmp_x.x,ifnull(sum(o.money),0) as val,count(o.paymentId) as num
|
from
|
(SELECT @xi:=@xi+1 as x from
|
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
|
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
|
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
|
(SELECT @xi:=-1) x0 limit 0," + num + @") tmp_x
|
left join (
|
select * from CoreCmsBillPayments
|
where 1 = 1
|
" + where + @"
|
) o on tmp_x.x = ((unix_timestamp(o." + joinVal + @") - " + CommonHelper.ConvertDateTimeToInt(sTime) + @") div(" + section + @"))
|
group by tmp_x.x
|
";
|
}
|
|
var sp = new List<SugarParameter>();
|
|
|
var list = _dal.SqlQuery(sqlStr, sp);
|
|
return list;
|
}
|
|
|
|
/// <summary>
|
/// 退款单报表
|
/// </summary>
|
/// <param name="num">数量</param>
|
/// <param name="where">查询条件</param>
|
/// <param name="section">查询值</param>
|
/// <param name="sTime">开始时间</param>
|
/// <param name="joinVal">筛选字段createTime/paymentTime </param>
|
/// <returns></returns>
|
public List<GetOrdersReportsDbSelectOut> GetRefundMark(int num, string where, int section, DateTime sTime, string joinVal)
|
{
|
|
var sqlStr = string.Empty;
|
string dbTypeString = AppSettingsConstVars.DbDbType;
|
if (dbTypeString == DbType.SqlServer.ToString())
|
{
|
sqlStr = @"SELECT tmp_x.number ,
|
ISNULL(SUM(o.money), 0) AS val ,
|
COUNT(o.refundId) AS num
|
FROM ( ( SELECT number
|
FROM MASTER..spt_values
|
WHERE TYPE = 'P'
|
AND number >= 0
|
AND number <= " + num + @"
|
) tmp_x
|
LEFT OUTER JOIN ( SELECT *
|
FROM CoreCmsBillRefund
|
WHERE 1=1 " + where + @"
|
) o ON tmp_x.number = DATEDIFF(" + (section == 3600 ? "HOUR" : "DAY") + ", '" + sTime.ToString("yyyy-MM-dd HH:mm:ss") + @"', o." + joinVal + @")
|
)
|
GROUP BY tmp_x.number";
|
}
|
else if (dbTypeString == DbType.MySql.ToString())
|
{
|
sqlStr = @"select tmp_x.x,ifnull(sum(o.money),0) as val,count(o.refundId) as num
|
from
|
(SELECT @xi:=@xi+1 as x from
|
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
|
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
|
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
|
(SELECT @xi:=-1) x0 limit 0," + num + @") tmp_x
|
left join(
|
select* from CoreCmsBillRefund
|
where 1 = 1
|
" + where + @"
|
) o on tmp_x.x = ((unix_timestamp(o." + joinVal + @") - " + CommonHelper.ConvertDateTimeToInt(sTime) + @") div(" + section + @"))
|
group by tmp_x.x
|
";
|
}
|
|
var sp = new List<SugarParameter>();
|
|
var list = _dal.SqlQuery(sqlStr, sp);
|
|
return list;
|
}
|
|
/// <summary>
|
/// 用户提现报表
|
/// </summary>
|
/// <param name="num">数量</param>
|
/// <param name="where">查询条件</param>
|
/// <param name="section">查询值</param>
|
/// <param name="sTime">开始时间</param>
|
/// <param name="joinVal">筛选字段createTime/paymentTime </param>
|
/// <returns></returns>
|
public List<GetOrdersReportsDbSelectOut> GetTocashMark(int num, string where, int section, DateTime sTime, string joinVal)
|
{
|
|
var sqlStr = string.Empty;
|
string dbTypeString = AppSettingsConstVars.DbDbType;
|
if (dbTypeString == DbType.SqlServer.ToString())
|
{
|
sqlStr = @"SELECT tmp_x.number ,
|
ISNULL(SUM(o.money), 0) AS val ,
|
COUNT(o.id) AS num
|
FROM ( ( SELECT number
|
FROM MASTER..spt_values
|
WHERE TYPE = 'P'
|
AND number >= 0
|
AND number <= " + num + @"
|
) tmp_x
|
LEFT OUTER JOIN ( SELECT *
|
FROM CoreCmsUserTocash
|
WHERE 1=1 " + where + @"
|
) o ON tmp_x.number = DATEDIFF(" + (section == 3600 ? "HOUR" : "DAY") + ", '" + sTime.ToString("yyyy-MM-dd HH:mm:ss") + @"', o." + joinVal + @")
|
)
|
GROUP BY tmp_x.number";
|
}
|
else if (dbTypeString == DbType.MySql.ToString())
|
{
|
sqlStr = @"select tmp_x.x,ifnull(sum(o.money),0) as val,count(o.id) as num
|
from
|
(SELECT @xi:=@xi+1 as x from
|
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
|
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
|
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
|
(SELECT @xi:=-1) x0 limit 0," + num + @") tmp_x
|
left join (
|
select * from CoreCmsUserTocash
|
where 1 = 1
|
" + where + @"
|
) o on tmp_x.x = ((unix_timestamp(o." + joinVal + @") - " + CommonHelper.ConvertDateTimeToInt(sTime) + @") div(" + section + @"))
|
group by tmp_x.x
|
";
|
}
|
|
var sp = new List<SugarParameter>();
|
|
var list = _dal.SqlQuery(sqlStr, sp);
|
|
return list;
|
}
|
|
/// <summary>
|
/// 获取订单销量查询返回结果
|
/// </summary>
|
/// <param name="start"></param>
|
/// <param name="end"></param>
|
/// <param name="filter"></param>
|
/// <param name="filterSed"></param>
|
/// <param name="thesort"></param>
|
/// <param name="pageIndex"></param>
|
/// <param name="pageSize"></param>
|
/// <returns></returns>
|
public async Task<IPageList<GoodsSalesVolume>> GetGoodsSalesVolumes(string start, string end, string filter, string filterSed, string thesort, int pageIndex = 1, int pageSize = 5000)
|
{
|
return await _dal.GetGoodsSalesVolumes(start, end, filter, filterSed, thesort, pageIndex, pageSize);
|
}
|
|
|
/// <summary>
|
/// 获取商品收藏查询返回结果
|
/// </summary>
|
/// <param name="start"></param>
|
/// <param name="end"></param>
|
/// <param name="thesort"></param>
|
/// <param name="pageIndex"></param>
|
/// <param name="pageSize"></param>
|
/// <returns></returns>
|
public async Task<IPageList<GoodsCollection>> GetGoodsCollections(string start, string end, string thesort, int pageIndex = 1, int pageSize = 5000)
|
{
|
return await _dal.GetGoodsCollections(start, end, thesort, pageIndex, pageSize);
|
|
}
|
|
|
}
|
}
|
