has('begin_time') && $request->input('begin_time') ? $request->input('begin_time') : ''; $end_time = $request->has('end_time') && $request->input('end_time') ? $request->input('end_time') : ''; $gender = $request->input('gender',''); $data = SendOrderForceDayStatService::getSendOrderDayStatsByBook(compact('begin_time', 'end_time','gender')); return response()->collection(new SendOrderBookDayStatTransformer(), $data); } /** * @apiVersion 1.0.0 * @api {GET} exportBookSendOrderDayStats 获取派单统计 * @apiGroup sendOrder * @apiName exportBookSendOrderDayStats */ function exportGroupByBook(Request $request) { $end_time = $request->has('end_time') && $request->input('end_time') ? $request->input('end_time') : ''; $begin_time = $request->has('begin_time') && $request->input('begin_time') ? $request->input('begin_time') : ''; $gender = $request->input('gender',''); $data = SendOrderForceDayStatService::getSendOrderDayStatsByBook(compact('begin_time', 'end_time','gender')); header("Content-type:application/vnd.ms-excel"); header("Content-Disposition:attachment;filename=" . "书籍数据" . date("YmdHis") . ".csv"); echo iconv("UTF-8", "GBK", "\"书本名称\",\"男女频\",\"派单数\",\"充值总额\",\"成功订单数\",\"PV\",\"UV\",\"充值总额/UV\",\"注册用户/UV\",\"订单总数\",\"订单成功率\"\r\n"); foreach ($data as $item) { echo("\"" . mb_convert_encoding($item->book_name, "GBK", "UTF-8") . "\","); echo("\"" . mb_convert_encoding(($item->pid==1)?'男':'女', "GBK", "UTF-8") . "\","); echo("\"" . mb_convert_encoding($item->send_order_num, "GBK", "UTF-8") . "\","); echo("\"" . mb_convert_encoding($item->sum_recharge_amount, "GBK", "UTF-8") . "\","); echo("\"" . mb_convert_encoding($item->sum_paid_num, "GBK", "UTF-8") . "\","); echo("\"" . mb_convert_encoding($item->sum_pv, "GBK", "UTF-8") . "\","); echo("\"" . mb_convert_encoding($item->sum_uv, "GBK", "UTF-8") . "\","); $total_send_order_count = $item->sum_paid_num + $item->sum_unpaid_num; if (0 == $item->sum_uv) { $recharge_persent_result = '100%'; $register_persent_result = '100%'; } else { $recharge_persent_result = round(($item->sum_recharge_amount / $item->sum_uv) * 100, 2) . '%'; $register_persent_result = round(($item->sum_register_user_num / $item->sum_uv) * 100, 2) . '%'; } if (0 == $total_send_order_count) { $success_order_persent_result = '0%'; } else { $success_order_persent_result = round(($item->sum_paid_num / $total_send_order_count) * 100, 2) . '%'; } echo("\"" . mb_convert_encoding($recharge_persent_result, "GBK", "UTF-8") . "\","); echo("\"" . mb_convert_encoding($register_persent_result, "GBK", "UTF-8") . "\","); echo("\"" . mb_convert_encoding($total_send_order_count, "GBK", "UTF-8") . "\","); echo("\"" . $success_order_persent_result . "\"\r\n"); } exit(); } //注册用户/UV比值大于85%的派单链接的强关转化(强关用户数/注册用户数量)、付费转化(付费用户数/强关粉丝)、注册用户ARPU值(充值/注册用户) function newUserArpu(Request $request) { $begin_time = $request->has('begin_time') && $request->input('begin_time') ? $request->input('begin_time') : ''; $end_time = $request->has('end_time') && $request->input('end_time') ? $request->input('end_time') : ''; $bid = $request->has('bid') && $request->input('bid') ? $request->input('bid') : ''; $data = DB::select("select avg(sum_recharge_amount) avg_recharge_amount,avg(sum_force_user_num) avg_force_user_num,avg(sum_register_user_num) avg_register_user_num,avg(sum_pay_user_num) avg_pay_user_num from (select t.* from (select sum(recharge_amount) as sum_recharge_amount,sum(force_user_num) sum_force_user_num, sum(register_user_num) sum_register_user_num,sum(pay_user_num) sum_pay_user_num,sum(uv) sum_uv from send_orders_force_day_stats where total_uv > 20 and date >='{$begin_time}' and date <= '{$end_time}' and bid = '{$bid}' group by send_order_id) as t ) as tt where sum_register_user_num/sum_uv > 0.85"); return response()->success([ 'sub_per_register' => $data[0]->avg_register_user_num ? round(100 * $data[0]->avg_force_user_num / $data[0]->avg_register_user_num, 2) . '%' : 0, 'pay_per_sub' => $data[0]->avg_force_user_num ? round(100 * $data[0]->avg_pay_user_num / $data[0]->avg_force_user_num, 2) . '%' : 0, 'arpu' => $data[0]->avg_register_user_num ? round($data[0]->avg_recharge_amount / $data[0]->avg_register_user_num, 2) : 0 ]); } function deepReadRecordData(Request $request) { $bid = $request->input('bid'); if (empty($bid)) { return response()->error('LACK_PARAM'); } $total_count = $this->getCount($bid); $count = $request->input('count'); if (!$count) { $count = $total_count; } $book = BookConfigService::getBookById($bid); if ($count == $total_count) { $info = $this->getReadRecordDataFromRedis($bid); } else { $info = $this->getReadRecordDataFromDB($bid, $count); } $info['total_count'] = $total_count; $info['now_count'] = $count; $info['book_name'] = $book->book_name; return response()->success($info); } private function getReadRecordDataFromRedis($bid) { $key = 'book:' . $bid . ':deepread:uv'; $total = Redis::scard($key); $forseq = 30; $uv = []; $chapter = []; for ($i = 1; $i <= $forseq; $i++) { $chapter_key = 'book:' . $bid . ':chapterseq:' . $i; $uv[] = (int)Redis::scard($chapter_key); $chapter[] = '第' . $i . '章'; } return ['chapter' => $chapter, 'uv' => $uv, 'total_uv' => $total]; } private function getReadRecordDataFromDB($bid, $count) { $info = BookUvStat::where('bid', $bid)->where('count', $count)->orderBy('chapter_seq')->select('bid', 'chapter_seq', 'chapter_uv', 'total_uv', 'count')->get(); if (!$info) { return []; } $data = []; foreach ($info as $v) { $data['total_uv'] = $v->total_uv; $data['uv'][] = $v->chapter_uv; $data['chapter'][] = '第' . $v->chapter_seq . '章'; } return $data; } public function resetDeepReadRecordData(Request $request) { $bid = $request->input('bid'); $key = 'book:' . $bid . ':deepread:uv'; $total = Redis::scard($key); $forseq = 30; $info = BookUvStat::where('bid', $bid)->select('count')->orderBy('count', 'desc')->first(); $count = 1; if ($info && !empty($info->count)) { $count = $info->count + 1; } $data = []; for ($i = 1; $i <= $forseq; $i++) { $chapter_key = 'book:' . $bid . ':chapterseq:' . $i; $temp = (int)Redis::scard($chapter_key); $data[] = ['bid' => $bid, 'chapter_seq' => $i, 'total_uv' => $total, 'chapter_uv' => $temp, 'count' => $count, 'created_at' => date('Y-m-d H:i:s'), 'updated_at' => date('Y-m-d H:i:s')]; Redis::del($chapter_key); } DB::table('book_uv_stats')->insert($data); Redis::del($key); return response()->success(); } private function getCount($bid) { $info = BookUvStat::where('bid', $bid)->select('count')->orderBy('count', 'desc')->first(); $count = 1; if ($info && !empty($info->count)) { $count = $info->count + 1; } return $count; } }