原方案：
/**
* 累计销量
*/
public function totalSales()
{
$uid = User::getUserId();
if ($uid === false) {
return JsonService::successful('error', [
'need_login' => 1,
'info' => self::LOGIN_TIP
]);
}
//$total_fee = Cache::get("{$uid}_totalSales_total_fee");
$total_fee = false;
if ($total_fee === false) {
try {
$user_list = sellUser::list($uid);
if ($user_list === false) {
throw new ApiException(self::ERROR_TIP, 0x3e1999dd);
}
$total_fee = 0.00;
foreach ($user_list as $user) {
$fee = Fee::paidTotalCount(
$user['uid'],
0,
0
);
if ($fee === false) {
throw new ApiException(self::ERROR_TIP, 0x6f1e0685);
}
$total_fee = $total_fee + $fee;
}
//$total_fee = Fee::totalSales($uid);
/*
Cache::set(
"{$uid}_totalSales_total_fee",
sprintf("%.0f", $total_fee),
3600+mt_rand(-600, 600)
);*/
} catch (\Exception $e) {
Log::write([
'exception_message' => $e->getMessage(),
'code' => '0x' . dechex($e->getCode()),
'file' => __FILE__,
'line' => __LINE__
], 'notice');
return json([
'code' => '0x' . dechex($e->getCode()),
'msg' => self::ERROR_TIP,
'data' => []
]);
}
}
return json([
'code' => 200,
'msg' => 'success',
'data' => $total_fee
]);
}
/**
* 登录用户在线支付的总金额（不含退款）。
*
* @param int $uid 付款用户
* @param int $pay_start_time 开始时间（支付）
* @param int $pay_end_time 结束时间（支付）
*
* @return float|bool 查询成功返回金额合计，否则返回false
*/
public static function paidTotalCount(
$uid = 0,
$pay_start_time = 0,
$pay_end_time = 0
) {
$circle_sql = '';
$recharge_sql = '';
$good_sql = '';
$yue_sql = '';
$bind_param = [];
$bind_param[] = $uid;
if ($pay_start_time > 0 && $pay_end_time > 0) {
//$circle_sql = ' AND co_pay_at>=? AND co_pay_at<?';
//$recharge_sql = ' AND pay_time>=? AND pay_time<?';
$good_sql = ' AND pay_time>=? AND pay_time<? ';
//$yue_sql = ' AND yo_pay_at>=? AND yo_pay_at<?';
$bind_param[] = $pay_start_time;
$bind_param[] = $pay_end_time;
}
try {
//圈子订单
/*
$sum_circle = Db::query("
SELECT IFNULL(sum(co_actual_pay-co_refund_amount), 0) AS total_amount FROM osx_circle_order
WHERE co_uid=? $circle_sql AND co_del_at=0 AND co_pay_status=1;
", $bind_param);
if ($sum_circle === false) {
throw new DbException(self::QUERY_FAIL, [], Db::getLastSql());
}
*/
//充值订单
/*
$sum_recharge = Db::query("
SELECT IFNULL(sum(price-refund_price), 0) AS total_amount FROM osx_user_recharge
WHERE uid=? $recharge_sql and paid=1;
", $bind_param);
if ($sum_recharge === false) {
throw new DbException(self::QUERY_FAIL, [], Db::getLastSql());
}
*/
//商品订单
//原SQL SELECT IFNULL(sum(pay_price-refund_price), 0) AS total_amount FROM osx_store_order
$sum_good = Db::query("
SELECT IFNULL(sum(pay_price), 0) AS total_amount FROM osx_store_order
WHERE uid=? $good_sql and paid=1;
", $bind_param);
if ($sum_good === false) {
throw new DbException(self::QUERY_FAIL, [], Db::getLastSql());
}
//悦订单
/*
$sum_yue = Db::query("
SELECT IFNULL(sum(yo_actual_pay-yo_refund_amount), 0) AS total_amount FROM osx_yue_order
WHERE yo_uid=? $yue_sql and yo_pay_status=1 AND yo_type=1;
", $bind_param);
if ($sum_yue === false) {
throw new DbException(self::QUERY_FAIL, [], Db::getLastSql());
}*/
} catch (\Exception $e) {
Log::write([
'exception_message' => $e->getMessage(),
'last_sql' => Db::getLastSql(),
'file' => __FILE__,
'line' => __LINE__
], 'notice');
return false;
}
return
//($sum_circle[0]['total_amount'] >= 0 ? $sum_circle[0]['total_amount'] : 0)
//+ ($sum_recharge[0]['total_amount'] >= 0 ? $sum_recharge[0]['total_amount'] : 0)
//+
($sum_good[0]['total_amount'] >= 0 ? $sum_good[0]['total_amount'] : 0);
//+ ($sum_yue[0]['total_amount'] >= 0 ? $sum_yue[0]['total_amount'] : 0);
}
/**
* 被介绍人列表
*
* @param int $uid 介绍人的uid
*
* @return array|bool 查询成功返回二维数组或空数组。否则返回false
*
* [
* ["uid" => 2],
* ["uid" => 13],
* ["uid" => 698]
* ]
*/
public static function list($uid = 0)
{
try {
$q_list = Db::query("
SELECT uc_uid AS uid FROM osx_user_character AS o_u_c
WHERE uc_superior=? AND uc_del_at=0;
", [$uid]);
if ($q_list === false) {
throw new DbException(self::QUERY_FAIL, [], Db::getLastSql());
}
} catch (\Exception $e) {
Log::write([
'exception_message' => $e->getMessage(),
'last_sql' => Db::getLastSql(),
'file' => __FILE__,
'line' => __LINE__
], 'notice');
return false;
}
return $q_list;
}
接口耗时 453 毫秒。
新方案：
/**
* 累计销量
*
* @param int $uid 登录用户
*
* @return string|false 查询成功返回金额，失败返回false
*
* @throws \think\Exception\DbException
*/
public static function totalSales($uid = 0)
{
$qTotalSales = Db::query('
select
IFNULL(sum(oso.pay_price-oso.refund_price), 0) as total_amount
from
osx_user_character ouc
inner join osx_store_order oso on
ouc.uc_uid = oso.uid
where
ouc.uc_superior = ?
and oso.paid = 1;
', [$uid]);
if ($qTotalSales === false) {
throw new DbException(self::QUERY_FAIL, [], Db::getLastSql());
}
return $qTotalSales[0]['total_amount'] ?? 0;
}
接口耗时 46 毫秒
