244 lines
9.7 KiB
PHP
244 lines
9.7 KiB
PHP
<?php
|
|
namespace app\admin\controller;
|
|
|
|
use app\model\Admins;
|
|
use app\model\Onlines;
|
|
use app\model\Orders;
|
|
use app\model\Sales;
|
|
use app\model\Works;
|
|
use support\Log;
|
|
use support\Request;
|
|
|
|
class DataController extends base
|
|
{
|
|
/**
|
|
* 获取订单统计数据
|
|
* @param $times
|
|
* @param $os
|
|
* @return Orders[]|array|\think\Collection|\think\db\Query[]|\think\model\Collection
|
|
* @throws \think\db\exception\DataNotFoundException
|
|
* @throws \think\db\exception\DbException
|
|
* @throws \think\db\exception\ModelNotFoundException
|
|
*/
|
|
private function orderStatics($times, $os) {
|
|
$query = Orders::alias('o')->with('admin')
|
|
->leftJoin('admins a','a.id = o.admin_id')
|
|
->order('o.admin_id','desc')
|
|
->group('a.id,a.name,o.admin_id')
|
|
->fieldRaw('
|
|
count(o.id) as orders,
|
|
SUM(o.total_price) as total_price,
|
|
SUM(CASE
|
|
WHEN (o.os in (1,7) AND o.order_status = 4) OR (o.os in(3,5) AND o.order_status = 2) OR (o.os = 2 AND o.order_status = 5) THEN 1
|
|
ELSE 0
|
|
END) as assets,
|
|
SUM(CASE
|
|
WHEN (o.os in (1,7) AND o.order_status = 4) OR (o.os in(3,5) AND o.order_status = 2) OR (o.os = 2 AND o.order_status = 5) THEN asset_price
|
|
ELSE 0
|
|
END) as asset_price,
|
|
SUM(CASE
|
|
WHEN (o.os in (1,7) AND o.order_status = 5) OR (o.os in(3,5) AND o.order_status = 4) OR (o.os = 2 AND o.order_status = 1) THEN 1
|
|
ELSE 0
|
|
END) AS nopays,
|
|
SUM(CASE
|
|
WHEN (o.os in (1,7) AND o.order_status = 5) OR (o.os in(3,5) AND o.order_status = 4) OR (o.os = 2 AND o.order_status = 1) THEN actual_price
|
|
ELSE 0
|
|
END) AS nopay_price,
|
|
o.admin_id,a.name');
|
|
|
|
if($times) {
|
|
if (is_string($times)) {
|
|
$times = explode(',', $times);
|
|
}
|
|
$query->whereBetween('o.create_at',[strtotime($times[0])*1000,strtotime($times[1])*1000+999]);
|
|
}
|
|
if($os) {
|
|
$query->where('o.os', $os);
|
|
}
|
|
|
|
return $query->select();
|
|
}
|
|
public function index(Request $request) {
|
|
$times = $request->get('times');
|
|
$os = $request->get('os');
|
|
$list = $this->orderStatics($times, $os);
|
|
//统计每一列
|
|
$totalArr[1] = [
|
|
'write_rate' => 0,
|
|
'write_rate_price' => 0,
|
|
'orders' => 0,
|
|
'total_price' => 0,
|
|
'assets' => 0,
|
|
'asset_price' => 0,
|
|
'nopays' => 0,
|
|
'nopay_price' => 0,
|
|
'admin_id' => 0,
|
|
'm_orders' => 0,
|
|
'm_total_price' => 0,
|
|
'm_assets' => 0,
|
|
'm_asset_price' => 0,
|
|
'month_write_num' => 0,
|
|
'name' => '合计',
|
|
'admin' => [
|
|
'username' => '合计',
|
|
'name' => '合计',
|
|
'avatar' => '',
|
|
]
|
|
];
|
|
|
|
// 当月数据统计
|
|
$currentList = $this->orderStatics([date('Y-m-01'), date('Y-m-d H:i:s')], $os);
|
|
$currentList = array_column($currentList->toArray(), null, 'admin_id');
|
|
foreach ($list as $k => $v) {
|
|
$list[$k]['write_rate'] = number_format(($v['assets']/$v['orders'])*100,2);
|
|
$list[$k]['write_rate_price'] = number_format(($v['asset_price']/$v['total_price'])*100,2);
|
|
|
|
// 当月核销率(按订单)
|
|
$list[$k]['month_write_rate'] = 0;
|
|
// 当月核销率(按销售额)
|
|
$list[$k]['month_write_rate_price'] = 0;
|
|
// 当月核销数（按核销时间）
|
|
$list[$k]['month_write_num'] = Orders::query()->where('admin_id', $v['admin_id'])->where('verification_date', '>', date('Y-m-01'))->count();
|
|
if (isset($currentList[$v['admin_id']])) {
|
|
$currentAdmin = $currentList[$v['admin_id']];
|
|
$list[$k]['month_write_rate'] = number_format(($currentAdmin['assets']/$currentAdmin['orders'])*100,2);
|
|
$list[$k]['month_write_rate_price'] = number_format(($currentAdmin['asset_price']/$currentAdmin['total_price'])*100,2);
|
|
|
|
$totalArr[1]['m_orders'] += $currentAdmin['orders'];
|
|
$totalArr[1]['m_total_price'] += $currentAdmin['total_price'];
|
|
$totalArr[1]['m_assets'] += $currentAdmin['assets'];
|
|
$totalArr[1]['m_asset_price'] += $currentAdmin['asset_price'];
|
|
}
|
|
|
|
$totalArr[1]['orders'] += $list[$k]['orders'];
|
|
$totalArr[1]['total_price'] += $list[$k]['total_price'];
|
|
$totalArr[1]['assets'] += $list[$k]['assets'];
|
|
$totalArr[1]['asset_price'] += $list[$k]['asset_price'];
|
|
$totalArr[1]['nopays'] += $list[$k]['nopays'];
|
|
$totalArr[1]['nopay_price'] += $list[$k]['nopay_price'];
|
|
$totalArr[1]['month_write_num'] += $list[$k]['month_write_num'];
|
|
}
|
|
if (isset($totalArr[1]['orders']) && $totalArr[1]['orders'] > 0) {
|
|
$totalArr[1]['write_rate'] = (float)number_format(($totalArr[1]['assets']/$totalArr[1]['orders'])*100,2);
|
|
}
|
|
if (isset($totalArr[1]['total_price']) && $totalArr[1]['total_price'] > 0) {
|
|
$totalArr[1]['write_rate_price'] = (float)number_format(($totalArr[1]['asset_price']/$totalArr[1]['total_price'])*100,2);
|
|
}
|
|
if (isset($totalArr[1]['m_orders']) && $totalArr[1]['m_orders'] > 0) {
|
|
$totalArr[1]['month_write_rate'] = (float)number_format(($totalArr[1]['m_assets']/$totalArr[1]['m_orders'])*100,2);
|
|
}
|
|
if (isset($totalArr[1]['m_total_price']) && $totalArr[1]['m_total_price'] > 0) {
|
|
$totalArr[1]['month_write_rate_price'] = (float)number_format(($totalArr[1]['m_asset_price']/$totalArr[1]['m_total_price'])*100,2);
|
|
}
|
|
$list = array_merge($list->toArray(), $totalArr);
|
|
|
|
$excel = $request->get('excel');
|
|
if($excel == 1) {
|
|
$writer = new \XLSXWriter();
|
|
$writer->writeSheetHeader('跟进统计', [
|
|
'姓名' => 'string',
|
|
'订单数' => 'string',
|
|
'订单总金额' => 'price',
|
|
'核销数' => 'integer',
|
|
'核销金额' => 'price',
|
|
'未付款订单' => 'integer',
|
|
'未付款金额' => 'price',
|
|
'核销率(按订单)' => 'string',
|
|
'退款率' => 'string',
|
|
]);
|
|
|
|
$osList = [
|
|
1 => '美团',
|
|
2 => '快手',
|
|
3 => '抖音',
|
|
];
|
|
bcscale(2);
|
|
$list = array_values($list);
|
|
Log::info('lists:' . json_encode($list));
|
|
foreach($list as $val) {
|
|
$writer->writeSheetRow('跟进统计', [
|
|
$val['admin'] ? $val['admin']['name'] : '',
|
|
$val['orders'],
|
|
bcdiv($val['total_price'], 100),
|
|
$val['assets'],
|
|
bcdiv($val['asset_price'], 100),
|
|
$val['nopays'],
|
|
bcdiv($val['nopay_price'], 100),
|
|
$val['write_rate'] . '%',
|
|
$val['write_rate_price'] . '%',
|
|
]);
|
|
}
|
|
|
|
$file_name = "跟进统计-".date('Ymd-His').".xlsx";
|
|
|
|
$response = response();
|
|
$c = $writer->writeToString();
|
|
$response->withHeaders([
|
|
'Content-Type' => 'application/force-download',
|
|
'Content-Disposition' => 'attachment; filename="'.$file_name.'"',
|
|
'Content-Transfer-Encoding' => 'binary',
|
|
'Cache-Control' => 'max-age=0',
|
|
])->withBody($c);
|
|
return $response;
|
|
}
|
|
|
|
return $this->success(array_values($list), null, ['oss' => Orders::OSS]);
|
|
}
|
|
|
|
/**
|
|
* 在线时长
|
|
*/
|
|
public function online(Request $request) {
|
|
$times = $request->get('times');
|
|
$admin = $request->get('admin');
|
|
|
|
$online = new Onlines();
|
|
$list = $online->online($admin, $times);
|
|
|
|
return $this->success($list);
|
|
}
|
|
|
|
public function anchor(Request $request) {
|
|
$times = $request->get('times');
|
|
$admin = $request->get('admin');
|
|
if(!empty($times) && is_array($times) && count($times) >= 2) {
|
|
$start = date('Y-m-d 00:00:00',strtotime($times[0]));
|
|
$end = date('Y-m-d 23:59:59',strtotime($times[1]));
|
|
}else{
|
|
$start = date('Y-m-01 00:00:00');
|
|
$end = date('Y-m-d 23:59:59');
|
|
}
|
|
|
|
if($admin) {
|
|
$admins = Admins::where('is_anchor', 1)->where('username', $admin)->select();
|
|
}else{
|
|
$admins = Admins::where('is_anchor', 1)->select();
|
|
}
|
|
foreach($admins as $admin) {
|
|
$admin->works = Works::where('admin_id', $admin->id)->where('status',1)->whereBetween('start',[$start, $end])->order('start','asc')->select()->append(['total']);
|
|
}
|
|
|
|
return $this->success($admins->hidden(['password','remember_token']));
|
|
}
|
|
|
|
public function sale(Request $request) {
|
|
$query = Sales::with(['admin'])->order('date','desc')->order("id",'asc');
|
|
|
|
$admin = $request->get('admin');
|
|
if($admin) {
|
|
$admin_id = Admins::where('username', $admin)->value('id');
|
|
$query->where('admin_id', $admin_id);
|
|
}
|
|
$times = $request->get('times');
|
|
if($times) {
|
|
$start = date('Ymd', strtotime($times[0]));
|
|
$end = date('Ymd', strtotime($times[1]));
|
|
$query->whereBetween('date',[$start,$end]);
|
|
}
|
|
|
|
$list = $query->paginate($request->get('limit'));
|
|
|
|
return $this->success($list);
|
|
}
|
|
}
|