get('order_category'); $searchTime = $request->get('order_addtimes'); if (!empty($searchTime)) { $searchTime[0] = strtotime($searchTime[0]); $searchTime[1] = strtotime($searchTime[1]); } if (!$orderCategory) { return json_fail('参数异常'); } if ($orderCategory == 'SERVICE') { $orderCategory = ['SERVICE', 'CHNMED', 'CHNNCD']; } else if ($orderCategory == 'ALL') { $orderCategory = ['SERVICE', 'CHNMED', 'CHNNCD', 'GOODS', 'PACKAGE', 'MEALS', 'RECHARGE', 'COMBINE', 'PARTNER', 'REFERRER', 'VIP']; } else { $orderCategory = [$orderCategory]; } $where = ''; if (in_array('RECHARGE', $orderCategory)) { $where = " and join_pay_order_id not in ('COMBINE','PARTNER','REFERRER')"; } $orderCategoryStr = "'" . implode("','", $orderCategory) . "'"; $todayTimeUnix = strtotime(date('Y-m-d')); $yesterdayStart = strtotime(date('Y-m-d', strtotime("-1 days"))); $yesterdayEnd = strtotime(date('Y-m-d 23:59:59', strtotime("-1 days"))); $monthStart = strtotime(date('Y-m-01')); $monthEnd = strtotime(date('Y-m-t 23:59:59')); // 今日收入 $statistics['todayRevenue'] = PayDetail::whereRaw('CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= ? ', [$todayTimeUnix]) ->where('pay_status', 'SUCCESS') ->where('pay_prepayid','<>', '') ->whereIn('pay_category', $orderCategory) ->when(in_array('RECHARGE', $orderCategory), function ($query) { $query->whereNotIn('join_pay_order_id', ['COMBINE', 'PARTNER', 'REFERRER']); }) ->sum('pay_amount'); $todayRevenueOrderNbrModel = Db::select("select count(1) as total from (select JSON_EXTRACT(join_pay_object_json,'$.order_id') as order_id from app_pay_detail where CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= {$todayTimeUnix} and pay_status = 'SUCCESS' and pay_prepayid != '' and pay_category in ({$orderCategoryStr}) {$where} group by order_id) count"); $statistics['todayRevenueOrderNbr'] = $todayRevenueOrderNbrModel[0]->total; // 昨日收入 $statistics['yesterdayRevenue'] = PayDetail::whereRaw('CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= ? and CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) <= ?', [$yesterdayStart, $yesterdayEnd]) ->where('pay_status', 'SUCCESS') ->where('pay_prepayid','<>', '') ->whereIn('pay_category', $orderCategory) ->when(in_array('RECHARGE', $orderCategory), function ($query) { $query->whereNotIn('join_pay_order_id', ['COMBINE', 'PARTNER', 'REFERRER']); }) ->sum('pay_amount'); $yesterdayRevenueOrderNbrModel = Db::select("select count(1) as total from (select JSON_EXTRACT(join_pay_object_json,'$.order_id') as order_id from app_pay_detail where CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= {$yesterdayStart} and CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) <= {$yesterdayEnd} and pay_status = 'SUCCESS' and pay_prepayid != '' and pay_category in ({$orderCategoryStr}) {$where} group by order_id) count"); $statistics['yesterdayRevenueOrderNbr'] = $yesterdayRevenueOrderNbrModel[0]->total; // 本月收入 $statistics['monthRevenue'] = PayDetail::whereRaw('CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= ? and CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) <= ?', [$monthStart, $monthEnd]) ->where('pay_status', 'SUCCESS') ->where('pay_prepayid','<>', '') ->whereIn('pay_category', $orderCategory) ->when(in_array('RECHARGE', $orderCategory), function ($query) { $query->whereNotIn('join_pay_order_id', ['COMBINE', 'PARTNER', 'REFERRER']); }) ->sum('pay_amount'); $monthRevenueOrderNbrModel = Db::select("select count(1) as total from (select JSON_EXTRACT(join_pay_object_json,'$.order_id') as order_id from app_pay_detail where CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) >= {$monthStart} and CAST(UNIX_TIMESTAMP(pay_paytimes) as SIGNED) <= {$monthEnd} and pay_status = 'SUCCESS' and pay_prepayid != '' and pay_category in ({$orderCategoryStr}) {$where} group by order_id) count"); $statistics['monthRevenueOrderNbr'] = $monthRevenueOrderNbrModel[0]->total; // 累计收入 $statistics['totalRevenue'] = PayDetail::where('pay_status', 'SUCCESS') ->where('pay_prepayid','<>', '') ->whereIn('pay_category', $orderCategory) ->sum('pay_amount'); $totalRevenueOrderNbrModel = Db::select("select count(1) as total from (select JSON_EXTRACT(join_pay_object_json,'$.order_id') as order_id from app_pay_detail where pay_status = 'SUCCESS' and pay_prepayid != '' and pay_category in ({$orderCategoryStr}) {$where} group by order_id) count"); $statistics['totalRevenueOrderNbr'] = $totalRevenueOrderNbrModel[0]->total; // 今日退款 $todayRefundItem = Db::select("select SUM(d2.pay_amount) as amount,count(1) as total from app_pay_detail d1 inner join app_pay_detail d2 ON d1.join_pay_order_id=d2.join_pay_order_id and d2.pay_category='REFUND' where CAST(UNIX_TIMESTAMP(d2.pay_paytimes) as SIGNED) >= ? AND d1.pay_category in ({$orderCategoryStr}) ", [$todayTimeUnix]); $statistics['todayRefund'] = $todayRefundItem[0]->amount; $statistics['todayRefundNbr'] = $todayRefundItem[0]->total; // 累计退款 $totalRefundItem = Db::select("select SUM(d2.pay_amount) as amount,count(1) as total from app_pay_detail d1 inner join app_pay_detail d2 ON d1.join_pay_order_id=d2.join_pay_order_id and d2.pay_category='REFUND' where d1.pay_category in ({$orderCategoryStr}) "); $statistics['totalRefund'] = $totalRefundItem[0]->amount; $statistics['totalRefundNbr'] = $totalRefundItem[0]->total; if (in_array('MEALS', $orderCategory)) { // 挂账 $sql = "select SUM(order_amount_pay) as amount, count(1) as total from app_order where order_status_payment = 'AWAITING'"; if (!empty($searchTime)) { $sql .= " and order_addtimes > {$searchTime[0]} and order_addtimes < $searchTime[1]"; } $awaitingModel = Db::select($sql); $statistics['awaitingRevenue'] = $awaitingModel[0]->amount; $statistics['awaitingRevenueOrderNbr'] = $awaitingModel[0]->total; } return json_success('success', $statistics); } }