1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534 |
- <?php
- /**
- * 订单相关
- */
- defined('IN_WEB') or die('Include Error!');
- include_once dirname(__FILE__) . "/Base.php";
- class Payment extends Base
- {
- protected $summarylistTable;//汇总表
- protected $goods;//商品表
- private $_payBlackList; //支付需要过滤的黑名单
- private $_firstPayGoodsList = [12, 19, 44, 185, 320];//首冲商品列表
- public function __construct()
- {
- $this->summarylistTable = otable::summarylist();
- $this->goods = otable::goodsCrazy();
- $this->_payBlackList['uid'] = oo::commonOprModel("config")->payWhiteList();
- }
-
- /**
- * 获取订单列表
- */
- public function getList($param)
- {
- $data = array('list' => array(), 'total' => 0);
- $stime = oo::functions()->uint($param['stime']);
- $etime = oo::functions()->uint($param['etime']);
- $paymentType = oo::functions()->uint($param['payment_type']);
- $uid = oo::functions()->uint($param['userid']); // 用户ID
- $orderId = oo::functions()->escape($param['order_id']);//订单号
- $sl_platform_serial_num = $param['sl_platform_serial_num'];
- $channel = oo::functions()->uint($param['channel']);//渠道
- $sidList = json_decode($param['sidList'], 1);
- $page = oo::functions()->uint($param['page']);//起始页数
- $limit = oo::functions()->uint($param['limit']);//每页数量
- $uidSql = '';
- if(empty($sidList)) {
- return json_encode(['list'=>[], 'total'=>0]);
- }
- if($this->isAllSid($sidList)) {
- $where = " 1=1 ";
- }else {
- $where = " sl_sid IN ( ". implode(',', $sidList)." )";
- }
- if( empty($stime) ){$stime = strtotime('0:0:1');}//默认为当天
- if( empty($etime) ){$etime = time();}
- if( empty($paymentType) ){ $paymentType = 2; }
- if( !empty($uid)) { $uidSql = ' AND sl_uid = '.$uid; }
- if($paymentType > 1){ //已发货,已退款,退款已处理等状态
- $where .= " AND sl_status = {$paymentType} AND sl_pay_time >= {$stime} AND sl_pay_time <= {$etime} ";
- } else {
- if($paymentType == 1){
- $where .= " AND sl_status = {$paymentType} AND sl_order_time >= {$stime} AND sl_order_time <= {$etime} ";
- }else{
- $where .= " AND sl_order_time >= {$stime} AND sl_order_time <= {$etime} ";
- }
- }
- if(!empty($orderId)) {
- $where .= " AND sl_orderid = '{$orderId}' ";
- }
- if(!empty($sl_platform_serial_num)) {
- $where .= " AND sl_platform_serial_num = '{$sl_platform_serial_num}' ";
- }
- if(!empty($channel)) {
- $where .= " AND g.gchannel = $channel ";
- }
- $slTable = $this->summarylistTable;
- $goodsTable = $this->goods;
- $blackUidArr = $this->_payBlackList['uid'];
- $blackUidSql = '';
- if(!empty($blackUidArr) && empty($uid)) {
- $blackUidStr = implode(',', $blackUidArr);
- $blackUidSql = " AND sl_uid NOT IN ({$blackUidStr}) ";
- }
- $sql = " SELECT sl.*, g.* FROM {$slTable} AS sl LEFT JOIN {$goodsTable} AS g ON sl.sl_gid = g.gid WHERE {$where} $blackUidSql $uidSql ORDER BY sl_order_time DESC LIMIT $page, $limit ";
- $list = oo::commonOprDb('common')->getAll($sql, MYSQLI_ASSOC);
- //获取总数
- $totalSql = " SELECT count(1) as t FROM {$slTable} AS sl LEFT JOIN {$goodsTable} AS g ON sl.sl_gid = g.gid WHERE {$where} $blackUidSql $uidSql ";
- // oo::logs()->debug3(['tsql'=>$totalSql], 'Payment.php');
- $retTotal = oo::commonOprDb('common')->getOne($totalSql, MYSQLI_ASSOC);
- $total = intval($retTotal['t']);
- $data['list'] = $list;
- $data['total'] = $total;
- return json_encode($data);
- }
- /**
- * 获取订单列表
- */
- public function getTotal($param)
- {
- $stime = oo::functions()->uint($param['stime']);
- $etime = oo::functions()->uint($param['etime']);
- if( empty($stime) ){$stime = strtotime('-7 days');}//默认为8天前
- if( empty($etime) ){$etime = time();}
- $stime = strtotime(date('Y-m-d 00:00:01', $stime));
- $etime = strtotime(date('Y-m-d 23:59:59', $etime));
- $sidList = json_decode($param['sidList'], 1);
- if(empty($sidList)) {
- return json_encode([]);
- }
- $sidSqlStr = '';
- if(!$this->isAllSid($sidList)) {
- $sidSqlStr = " AND sl_sid IN ( ". implode(',', $sidList)." ) ";
- }
- $slTable = $this->summarylistTable;
- $goodsTable = $this->goods;
- $blackUidArr = $this->_payBlackList['uid'];
- $blackUidSql = '';
- if(!empty($blackUidArr)) {
- $blackUidStr = implode(',', $blackUidArr);
- $blackUidSql = " AND sl_uid NOT IN ({$blackUidStr}) ";
- }
- //查询时间范围内的成功订单
- $sql = " SELECT sl_pusd, sl_uid, sl_pay_time, gchannel, sl_sid
- FROM {$slTable} AS sl LEFT JOIN {$goodsTable} AS goods ON sl.sl_gid = goods.gid
- WHERE sl_status = 2 AND sl_pay_time >= {$stime} AND sl_pay_time <= {$etime} $blackUidSql $sidSqlStr ";
- // oo::logs()->debug3(['sql'=>$sql], 'Payment.php');
- $list = oo::commonOprDb('common')->getAll($sql, MYSQLI_ASSOC);
- //总订单数
- $sql2 = " SELECT id, sl_order_time , gchannel, sl_sid
- FROM {$slTable} AS sl LEFT JOIN {$goodsTable} AS goods ON sl.sl_gid = goods.gid
- WHERE sl_order_time >= {$stime} AND sl_order_time <= {$etime} $blackUidSql $sidSqlStr";
- $list2 = oo::commonOprDb('common')->getAll($sql2, MYSQLI_ASSOC);
- $dayList = $this->getDays($stime, $etime, 2);
- $totalMoney = $totalMoneyAndroid = $totalMoneyIos = $totalRecord = $totalUid = $allRecord = $totalChannel = $succChannel = $totalSid = $succSid = $vivoTotalMoney = [];
- $uid = [];
- // oo::logs()->debug3(['succ'=>json_encode($list)], 'Payment.php');
- // oo::logs()->debug3(['total'=>json_encode($list2)], 'Payment.php');
- //成功的订单
- foreach ($list as $_v) {
- $date = date('Ymd', $_v['sl_pay_time']);
- $totalMoney[$date] += number_format($_v['sl_pusd'], 2,".","") ;
- if($_v['sl_sid'] == 1){
- $totalMoneyAndroid[$date] += number_format($_v['sl_pusd'], 2,".","") ;
- }else{
- $totalMoneyIos[$date] += number_format($_v['sl_pusd'], 2,".","") ;
- }
- $totalRecord[$date] += 1 ;
- //处理当天重复下单的用户统计
- $uid[$date] ?? $uid[$date] = [];
- if(!in_array($_v['sl_uid'], $uid[$date])) {
- $totalUid[$date] += 1;
- array_push($uid[$date], $_v['sl_uid']);
- }
- //统计当天的渠道的订单数
- if(isset($succChannel[$date][$_v['gchannel']])) {
- $succChannel[$date][$_v['gchannel']] += 1;
- }else {
- $succChannel[$date][$_v['gchannel']] = 1;
- }
-
- //统计平台 当天成功
- if(isset($succSid[$date][$_v['sl_sid']])) {
- $succSid[$date][$_v['sl_sid']] += 1;
- }else {
- $succSid[$date][$_v['sl_sid']] = 1;
- }
- //VIVO收入
- $temp = oo::commonOprModel('member')->getUserInfo($_v['sl_uid']);
- if($temp['channel'] == 1){
- $vivoTotalMoney[$date] += number_format($_v['sl_pusd'],2,".","");
- $succSid[$date][3] += 1;
- }
- }
- //所有订单
- foreach ($list2 as $_v2) {
- $date = date('Ymd', $_v2['sl_order_time']);
- $allRecord[$date] += 1 ;
- //统计当天的渠道的订单数
- if(isset($totalChannel[$date][$_v2['gchannel']])) {
- $totalChannel[$date][$_v2['gchannel']] += 1;
- }else {
- $totalChannel[$date][$_v2['gchannel']] = 1;
- }
-
- //统计平台
- if(isset($totalSid[$date][$_v2['sl_sid']])) {
- $totalSid[$date][$_v2['sl_sid']] += 1;
- }else {
- $totalSid[$date][$_v2['sl_sid']] = 1;
- }
- }
-
- $dayList = $this->getDays($stime, $etime);
- $dayArr = [];
- foreach ($dayList as $day) {
- $dayArr[] = date('Ymd', strtotime($day));
- }
-
- //点击支付次数统计
- $payReportTotal = $payReportOtherTotal = [];
- if(!empty($dayArr)){
- $dateStr = implode(",",$dayArr);
- $table = otable::staData();
- $type = 'payReport';
- $sql = "SELECT * FROM {$table} WHERE date IN ({$dateStr}) AND type = '{$type}'";
- $payReportRet = oo::commonOprDb('statistics')->getAll($sql,MYSQLI_ASSOC);
- $sidList = [1,2];
- //10009支付成功 10010支付取消 10011支付失败
- $tempArr = [10009,10010,10011];
- foreach($payReportRet as $k => $v){
- if($v['date'] == date('Ymd')){
- $dataTemp = $dataTotalTemp = [];
- foreach($tempArr as $eventId){
- foreach($sidList as $sid) {
- $ret = oo::commonOprRedis("Statistics")->hGetAll(okeys::payReport($sid, $eventId));
- $dataTemp[$eventId]["total"][$sid] = array_sum($ret);
- }
- }
- foreach($sidList as $sid){
- $ret = oo::commonOprRedis("Statistics")->hGetAll(okeys::payReport($sid));
- $dataTotalTemp['sid'][$sid] = $ret;
- $dataTotalTemp['total'][$sid] = array_sum($ret);
- }
- foreach($dataTotalTemp['total'] as $sid => $val){
- $payReportTotal[$v['date']][$sid] += $val;
- }
- foreach($tempArr as $eventId){
- if(isset($dataTemp[$eventId]['total'])){
- foreach($dataTemp[$eventId]['total'] as $sid => $val){
- $payReportOtherTotal[$eventId][$v['date']][$sid] += $val;
- }
- }
- }
- }else{
- $v['data'] = json_decode($v['data'],true);
- if(isset($v['data']['total'])){
- foreach($v['data']['total'] as $sid => $val){
- $payReportTotal[$v['date']][$sid] += $val;
- }
- }
- foreach($tempArr as $eventId){
- if(isset($v['data'][$eventId]['total'])){
- foreach($v['data'][$eventId]['total'] as $sid => $val){
- $payReportOtherTotal[$eventId][$v['date']][$sid] += $val;
- }
- }
- }
- }
- }
- if(!isset($payReportTotal[date('Ymd')])){
- $dataTemp = $dataTotalTemp =[];
- foreach($tempArr as $eventId){
- foreach($sidList as $sid) {
- $ret = oo::commonOprRedis("Statistics")->hGetAll(okeys::payReport($sid, $eventId));
- $dataTemp[$eventId]["total"][$sid] = array_sum($ret);
- }
- }
- foreach($sidList as $sid){
- $ret = oo::commonOprRedis("Statistics")->hGetAll(okeys::payReport($sid));
- $dataTotalTemp['sid'][$sid] = $ret;
- $dataTotalTemp['total'][$sid] = array_sum($ret);
- }
- foreach($dataTotalTemp['total'] as $sid => $val){
- $payReportTotal[date('Ymd')][$sid] += $val;
- }
- foreach($tempArr as $eventId){
- if(isset($dataTemp[$eventId]['total'])){
- foreach($dataTemp[$eventId]['total'] as $sid => $val){
- $payReportOtherTotal[$eventId][date('Ymd')][$sid] += $val;
- }
- }
- }
- }
- }
- $data = [
- 'totalMoney' => $totalMoney,
- 'totalMoneyAndroid' => $totalMoneyAndroid,
- 'totalMoneyIos' => $totalMoneyIos,
- 'totalRecord' => $totalRecord,
- 'totalUid' => $totalUid,
- 'allRecord' => $allRecord,
- 'dayList' => $dayList,
- 'totalChannel' => $totalChannel,
- 'succChannel' => $succChannel,
- 'totalSid' => $totalSid,
- 'succSid' => $succSid,
- 'payReportTotal' => $payReportTotal,
- 'payReportOtherTotal' => $payReportOtherTotal,
- 'vivoTotalMoney' => $vivoTotalMoney,
- ];
- return json_encode($data);
- }
- /**
- * 获取订单列表
- */
- public function getTotalKeyData($param)
- {
- $totalKey = okeys::keydatatotal();
- $stime = oo::functions()->uint($param['stime']);
- $etime = oo::functions()->uint($param['etime']);
- if( empty($stime) ){$stime = strtotime('-7 days');}//默认为8天前
- if( empty($etime) ){$etime = time();}
- $stime = strtotime(date('Y-m-d 00:00:01', $stime));
- $etime = strtotime(date('Y-m-d 23:59:59', $etime));
- $sidList = json_decode($param['sidList'], 1);
- if(empty($sidList)) {
- return json_encode([]);
- }
- $isAllSid = $this->isAllSid($sidList);
- if($isAllSid) {
- $keydataInfo = oo::commonOprRedis('common')->get($totalKey);
- if(!empty($keydataInfo)) {
- return $keydataInfo;
- }
- }
- $where = '';
- $sidSqlStr = '';
- if(!$isAllSid) {
- $sidSqlStr = " AND sl_sid IN ( ". implode(',', $sidList)." ) ";
- }
- $slTable = $this->summarylistTable;
- $goodsTable = $this->goods;
- $blackUidArr = $this->_payBlackList['uid'];
- $blackUidSql = '';
- if(!empty($blackUidArr)) {
- $blackUidStr = implode(',', $blackUidArr);
- $blackUidSql = " AND sl_uid NOT IN ({$blackUidStr}) ";
- }
- $where = $sidSqlStr.$blackUidSql;
- //查询时间范围内的成功订单
- $sql = " SELECT sl_pusd, sl_uid, sl_pay_time, gchannel
- FROM {$slTable} AS sl LEFT JOIN {$goodsTable} AS goods ON sl.sl_gid = goods.gid
- WHERE sl_status = 2 AND sl_pay_time >= {$stime} AND sl_pay_time <= {$etime} $where ";
- $list = oo::commonOprDb('common')->getAll($sql, MYSQLI_ASSOC);
- //总订单数
- $sql2 = " SELECT id, sl_order_time , gchannel
- FROM {$slTable} AS sl LEFT JOIN {$goodsTable} AS goods ON sl.sl_gid = goods.gid
- WHERE sl_order_time >= {$stime} AND sl_order_time <= {$etime} $where ";
- $list2 = oo::commonOprDb('common')->getAll($sql2, MYSQLI_ASSOC);
- $dayList = $this->getDays($stime, $etime, 2);
- $totalMoney = $totalRecord = $totalUid = $allRecord = $totalChannel = $succChannel = [];
- $uid = [];
- // oo::logs()->debug3(['succ'=>json_encode($list)], 'Payment.php');
- // oo::logs()->debug3(['total'=>json_encode($list2)], 'Payment.php');
- //成功的订单
- foreach ($list as $_v) {
- $date = date('Ymd', $_v['sl_pay_time']);
- $totalMoney[$date] += round($_v['sl_pusd'], 2) ;
- $totalRecord[$date] += 1 ;
- //处理当天重复下单的用户统计
- $uid[$date] ?? $uid[$date] = [];
- if(!in_array($_v['sl_uid'], $uid[$date])) {
- $totalUid[$date] += 1;
- array_push($uid[$date], $_v['sl_uid']);
- }
- //统计当天的渠道的订单数
- if(isset($succChannel[$date][$_v['gchannel']])) {
- $succChannel[$date][$_v['gchannel']] += 1;
- }else {
- $succChannel[$date][$_v['gchannel']] = 1;
- }
- }
- //所有订单
- foreach ($list2 as $_v2) {
- $date = date('Ymd', $_v2['sl_order_time']);
- $allRecord[$date] += 1 ;
- //统计当天的渠道的订单数
- if(isset($totalChannel[$date][$_v2['gchannel']])) {
- $totalChannel[$date][$_v2['gchannel']] += 1;
- }else {
- $totalChannel[$date][$_v2['gchannel']] = 1;
- }
- }
- //计算总的付费用户和新增付费用户
- $loginPlayerNum = 0;
- $firstPayPlayerNum = 0;
- $firstPayPlayerNumLastDay = 0;//昨天新增付费用户
- //累计的付费用户数量
- $sqlTotal = " SELECT count(DISTINCT sl_uid) as allPayNum FROM {$slTable} as sl WHERE sl_status = 2 $where ";
- $retTotal = oo::commonOprDb('common')->getOne($sqlTotal, 1);
- $allPayNum = $retTotal['allPayNum'] ?? 0;
- $dateToday = date('Ymd', time());
- $dateLastDay = date('Ymd', strtotime('-1 days 0:0:1'));
- //获取新增的用户UID数组
- $newPlayerArr = oo::commonOprModel('statistics')->getAllPlayer($dateToday, 1, $sidList);
- $newPlayerNum = count($newPlayerArr);//今日新增的人数
- //获取登录的用户UID数组
- $loginPlayerArr = oo::commonOprModel('statistics')->getAllPlayer($dateToday, 2, $sidList);
- $loginPlayerNum = count($loginPlayerArr);
- //今天付费的用户UID
- $payTodayPlayer = $uid[$dateToday] ?? [];
- $newPayUidArr = array_intersect($payTodayPlayer, $newPlayerArr);//获取当天所以新增用户和当天付费的用户的交集
- $newPayNum = count($newPayUidArr);
- $loginAvg = oo::commonOprModel('statistics')->avgLoginTime();
- $loginAvg = floor($loginAvg);
- //首次付费的用户
- if(!empty($payTodayPlayer)) {
- $time = strtotime('0:0:0');
- foreach ($payTodayPlayer as $vuid) {
- $checkBought = $this->checkPayFirst($vuid, $time);
- $checkBought && $firstPayPlayerNum++ ;
- }
- }
- //昨天的数据
- $newPlayerArrLastDay = oo::commonOprModel('statistics')->getAllPlayer($dateLastDay, 1, $sidList);
- $newPlayerNumLastDay = count($newPlayerArrLastDay);//昨天新增的人数
- $payTodayPlayerLastDay = $uid[$dateLastDay] ?? [];
- $newPayUidArrLastDay = array_intersect($payTodayPlayerLastDay, $newPlayerArrLastDay);//获取当天所以新增用户和当天付费的用户的交集
- $newPayNumLastDay = count($newPayUidArrLastDay);
- //首次付费的用户 昨天
- if(!empty($payTodayPlayerLastDay)) {
- $time = strtotime('-1 days 0:0:0');
- foreach ($payTodayPlayerLastDay as $vuid) {
- $checkBought = $this->checkPayFirst($vuid, $time);
- $checkBought && $firstPayPlayerNumLastDay++ ;
- }
- }
-
- $data = [
- 'totalMoney' => $totalMoney,
- 'totalRecord' => $totalRecord,
- 'totalUid' => $totalUid,
- 'allRecord' => $allRecord,
- 'dayList' => $dayList,
- 'totalChannel' => $totalChannel,
- 'succChannel' => $succChannel,
- 'allPayNum' => $allPayNum,
- 'newPayNum' => $newPayNum,
- 'loginPlayerNum' => $loginPlayerNum,
- 'newPlayerNum' => $newPlayerNum,
- 'firstPayPlayerNum' => $firstPayPlayerNum,
- 'loginAvg' => $loginAvg,
- 'firstPayPlayerNumLastDay' => $firstPayPlayerNumLastDay,
- 'newPlayerNumLastDay' => $newPlayerNumLastDay,
- 'newPayNumLastDay' => $newPayNumLastDay,
- ];
- $jsonData = json_encode($data);
- if($isAllSid) {
- oo::commonOprRedis('common')->set($totalKey, $jsonData);
- oo::commonOprRedis('common')->expire($totalKey, 10*60);//过期时间10分钟
- }
- return $jsonData;
- }
- /**
- * 获取关键数据明细
- */
- public function getKeyDataDetail($param)
- {
- $totalKey = okeys::keydatadetail();
- $stime = oo::functions()->uint($param['stime']);
- $etime = oo::functions()->uint($param['etime']);
- if( empty($stime) ){$stime = strtotime('-7 days');}//默认为7天前
- if( empty($etime) ){$etime = strtotime('-1 days');}
- $stime = strtotime(date('Y-m-d 00:00:01', $stime));
- $etime = strtotime(date('Y-m-d 23:59:59', $etime));
- $sidList = json_decode($param['sidList'], 1);
- if(empty($sidList)) {
- return json_encode([]);
- }
- $isAllSid = $this->isAllSid($sidList);
- // if($isAllSid) {
- // $keydataInfo = oo::commonOprRedis('common')->get($totalKey);
- // if(!empty($keydataInfo)) {
- // die($keydataInfo);
- // }
- // }
- $sidSqlStr = '';
- if(!$isAllSid) {
- $sidSqlStr = " AND sl_sid IN ( ". implode(',', $sidList)." ) ";
- }
- $slTable = $this->summarylistTable;
- $goodsTable = $this->goods;
- $blackUidArr = $this->_payBlackList['uid'];
- $blackUidSql = '';
- if(!empty($blackUidArr)) {
- $blackUidStr = implode(',', $blackUidArr);
- $blackUidSql = " AND sl_uid NOT IN ({$blackUidStr}) ";
- }
- $where = $sidSqlStr.$blackUidSql;
- //查询时间范围内的成功订单
- $sql = " SELECT sl_pusd, sl_uid, sl_pay_time, gchannel, sl_sid
- FROM {$slTable} AS sl LEFT JOIN {$goodsTable} AS goods ON sl.sl_gid = goods.gid
- WHERE sl_status = 2 AND sl_pay_time >= {$stime} AND sl_pay_time <= {$etime} $where ";
- $list = oo::commonOprDb('common')->getAll($sql, MYSQLI_ASSOC);
- //总订单数
- $sql2 = " SELECT id, sl_order_time , gchannel
- FROM {$slTable} AS sl LEFT JOIN {$goodsTable} AS goods ON sl.sl_gid = goods.gid
- WHERE sl_order_time >= {$stime} AND sl_order_time <= {$etime} $where ";
- $list2 = oo::commonOprDb('common')->getAll($sql2, MYSQLI_ASSOC);
- $dayList = $this->getDays($stime, $etime, 2);
- $totalMoney = $totalRecord = $totalUid = $allRecord = $totalChannel = $succChannel = [];
- $uid = [];
- // oo::logs()->debug3(['succ'=>json_encode($list)], 'Payment.php');
- // oo::logs()->debug3(['total'=>json_encode($list2)], 'Payment.php');
- //android ios 付费率 arpu arppu
- $androidMoney = $iosMoney = $androidPayUser = $iosPayUser = [];
- //成功的订单
- foreach ($list as $_v) {
- $date = date('Ymd', $_v['sl_pay_time']);
- $totalMoney[$date] += round($_v['sl_pusd'], 2) ;
- $totalRecord[$date] += 1 ;
- //处理当天重复下单的用户统计
- $uid[$date] ?? $uid[$date] = [];
- if(!in_array($_v['sl_uid'], $uid[$date])) {
- $totalUid[$date] += 1;
- array_push($uid[$date], $_v['sl_uid']);
- }
- $androidPayUser[$date] ?? $androidPayUser[$date] = [];
- $iosPayUser[$date] ?? $iosPayUser[$date] = [];
- if($_v['sl_sid'] == 1){
- $androidMoney[$date] += $_v['sl_pusd'];
- if(!in_array($_v['sl_uid'],$androidPayUser[$date])){
- array_push($androidPayUser[$date], $_v['sl_uid']);
- }
- }else if($_v['sl_sid'] == 2){
- $iosMoney[$date] += $_v['sl_pusd'];
- if(!in_array($_v['sl_uid'],$iosPayUser[$date])){
- array_push($iosPayUser[$date], $_v['sl_uid']);
- }
- }
- }
- //所有订单
- foreach ($list2 as $_v2) {
- $date = date('Ymd', $_v2['sl_order_time']);
- $allRecord[$date] += 1 ;
- }
- //累计的付费用户数量
- $sqlTotal = " SELECT count(DISTINCT sl_uid) as allPayNum FROM {$slTable} as sl WHERE sl_status = 2 AND sl_pay_time <= {$etime} $where ";
- $retTotal = oo::commonOprDb('common')->getOne($sqlTotal, 1);
- $allPayNum = $retTotal['allPayNum'] ?? 0;
- $newPlayerNumList = $loginPlayerNumList = $newPayNumList = $firstPayPlayerNumList = $aarrrList = $payRateList = $payARPUList = $payARPPUList = $inviteNumList = $firstPayRateList = $newFirstPayRateList = [];
- $vivoPayRateList = $vivoPayARPUList = $vivoPayARPPUList = [];//vivo付费率 vivo ARPU vivo ARPPU
- $androidPayRateList = $androidPayARPUList = $androidPayARPPUList = [];//android付费率 android ARPU android ARPPU
- $iosPayRateList = $iosPayARPUList = $iosPayARPPUList = [];//ios付费率 ios ARPU ios ARPPU
- $userinfoTb = otable::userinfo(1);
- foreach ($dayList as $day) {
- $firstPayPlayerNum = 0;
- $dateTmp = date('Ymd', strtotime($day));
- //获取新增的用户
- $newPlayerArr = oo::commonOprModel('statistics')->getAllPlayer($dateTmp, 1, $sidList);
- $newPlayerNum = count($newPlayerArr);//今日新增的人数
- //获取登录的用户
- $loginPlayer = oo::commonOprModel('statistics')->getAllPlayer($dateTmp, 2, $sidList);
- $loginPlayerNum = count($loginPlayer);
- //今天付费的用户UID
- $payTodayPlayer = $uid[$dateTmp] ?? [];
- $newPayUidArr = array_intersect($payTodayPlayer, $newPlayerArr);//获取当天所以新增用户和当天付费的用户的交集
- $newPayNum = count($newPayUidArr);
- //首次付费的用户
- if(!empty($payTodayPlayer)) {
- $time = strtotime(date('Y-m-d 0:0:0', strtotime($day)));
- foreach ($payTodayPlayer as $vuid) {
- $checkBought = $this->checkPayFirst($vuid, $time);
- $checkBought && $firstPayPlayerNum++ ;
- }
- }
- $newPlayerNumList[$dateTmp] = $newPlayerNum;
- $loginPlayerNumList[$dateTmp] = $loginPlayerNum;
- $newPayNumList[$dateTmp] = $newPayNum;
- $firstPayPlayerNumList[$dateTmp] = $firstPayPlayerNum;
- //邀请人数
- $dataToday = oo::commonOprModel('invitecode')->getInviteNum($dateTmp);
- $inviteNum = $dataToday['fbAndNum'] + $dataToday['invAndNum'] + $dataToday['fbIosNum'] + $dataToday['invIosNum'];
- $inviteNumList[$dateTmp] = $inviteNum;//自传播人数列表
- //自传播占比 (FB邀请+邀请码邀请)/新增
- $aarrrList[$dateTmp] = empty($newPlayerNum) ? 0 : round($inviteNum * 100 / $newPlayerNum, 2);
- //计算付费率 付费人数/DAU
- $payPlayerTemp = $totalUid[$dateTmp] ?? 0;//支付是人数
- $payRateList[$dateTmp] = empty($loginPlayerNum) ? 0 : round($payPlayerTemp * 100 / $loginPlayerNum, 2);
- //ARPU 付费金额/DAU
- $payMoneyTemp = $totalMoney[$dateTmp] ?? 0;//支付的金额
- $payARPUList[$dateTmp] = empty($loginPlayerNum) ? 0 : round($payMoneyTemp / $loginPlayerNum, 2);
- //ARPPU 付费金额/付费人数
- $payARPPUList[$dateTmp] = empty($payPlayerTemp) ? 0 : round($payMoneyTemp/ $payPlayerTemp, 2);
- $firstPayRateList[$dateTmp] = empty($payPlayerTemp) ? 0 : round($firstPayPlayerNum * 100 / $payPlayerTemp, 2);
- $newFirstPayRateList[$dateTmp] = empty($payPlayerTemp) ? 0 : round($newPayNum * 100 / $payPlayerTemp, 2);
-
- //新增用户付费率
- $newFirstPayRateList2[$dateTmp] = empty($newPlayerNum) ? 0 : round($newPayNum * 100 / $newPlayerNum, 2);
- //vivo统计
- $vivoPlayer = [];//vivo活跃人数
- $vivoPayNum = 0;
- /*
- //这个统计超时了
- foreach($loginPlayer as $uidTemp){
- $temp = oo::commonOprModel('member')->getUserInfo($uidTemp);
- if($temp['channel'] == 1){
- $vivoPlayer[] = $uidTemp;
- }
- }
- */
- /*
- //去掉VIVO
- if(!empty($loginPlayer)){
- $loginPlayerStr = implode(",",$loginPlayer);
- $sql = "SELECT uid FROM {$userinfoTb} WHERE channel = 1 AND uid in({$loginPlayerStr})";
- $vivoPlayerRet = oo::commonOprDb('common')->getAll($sql, 1);
- foreach($vivoPlayerRet as $vivoPlayerRetTemp){
- $vivoPlayer[] = $vivoPlayerRetTemp['uid'];
- }
- }
- $vivoPayUser = array_intersect($payTodayPlayer,$vivoPlayer);
- $vivoPayNum = count($vivoPayUser);
- //vivo付费率 付费人数/DAU
- $vivoPayRateList[$dateTmp] = empty($vivoPlayer) ? 0 : round($vivoPayNum * 100 / count($vivoPlayer),2);
- */
- //vivo ARPU 付费金额/DAU
- if(!empty($blackUidSql) && !empty($vivoPayUser)){
- $vivoPayUserStr = implode(",",$vivoPayUser);
- $stimeTemp = strtotime($day . " 0:0:0");
- $etimeTemp = strtotime($day . " 23:59:59");
- $sql = "SELECT sum(sl_pusd) AS total FROM {$slTable} WHERE sl_status = 2 AND sl_sid <> -1 AND sl_pay_time >= {$stimeTemp} AND sl_pay_time <= {$etimeTemp} AND sl_uid in($vivoPayUserStr) {$blackUidSql} ";
- $vivoRet = oo::commonOprDb('common')->getOne($sql, 1);
- $vivoPayMoney = empty($vivoRet['total']) ? 0 : $vivoRet['total'];
- $vivoPayARPUList[$dateTmp] = empty($vivoPlayer) ? 0 : round($vivoPayMoney / count($vivoPlayer),2);
- $vivoPayARPPUList[$dateTmp] = empty($vivoPayNum) ? 0 : round($vivoPayMoney / $vivoPayNum,2);
- }else{
- $vivoPayARPUList[$dateTmp] = 0;
- $vivoPayARPPUList[$dateTmp] = 0;
- }
- //android ios 付费率 arpu arppu
- $androidPlayer = [];
- if(!empty($loginPlayer)){
- $loginPlayerStr = implode(",",$loginPlayer);
- $sql = "SELECT uid FROM {$userinfoTb} WHERE channel = 0 AND uid in({$loginPlayerStr})";
- $androidPlayerRet = oo::commonOprDb('common')->getAll($sql, 1);
- foreach($androidPlayerRet as $androidPlayerRetTemp){
- $androidPlayer[] = $androidPlayerRetTemp['uid'];
- }
- }
- $androidPayRateList[$dateTmp] = empty($androidPlayer) ? 0 : round(count($androidPayUser[$dateTmp]) * 100 / count($androidPlayer),2);//付费率 付费人数 / DAU
- $androidPayARPUList[$dateTmp] = empty($androidPlayer) ? 0 : round($androidMoney[$dateTmp] / count($androidPlayer),2);//ARPU 付费金额 / DAU
- $androidPayARPPUList[$dateTmp] = empty($androidPayUser[$dateTmp]) ? 0 : round($androidMoney[$dateTmp] / count($androidPayUser[$dateTmp]),2);//ARPPU 付费金额 / 付费人数
- $iosPlayer = oo::commonOprModel('statistics')->getAllPlayer($dateTmp, 2, [2]);
- $iosPayRateList[$dateTmp] = empty($iosPlayer) ? 0 : round(count($iosPayUser[$dateTmp]) * 100 / count($iosPlayer),2);//付费率 付费人数 / DAU
- $iosPayARPUList[$dateTmp] = empty($iosPlayer) ? 0 : round($iosMoney[$dateTmp] / count($iosPlayer),2);//ARPU 付费金额 / DAU
- $iosPayARPPUList[$dateTmp] = empty($iosPayUser[$dateTmp]) ? 0 : round($iosMoney[$dateTmp] / count($iosPayUser[$dateTmp]),2);//ARPPU 付费金额 / 付费人数
- }
- $data = [
- 'totalMoney' => $totalMoney,
- 'totalRecord' => $totalRecord,
- 'totalUid' => $totalUid,
- 'allRecord' => $allRecord,
- 'dayList' => $dayList,
- 'totalChannel' => $totalChannel,
- 'succChannel' => $succChannel,
- 'allPayNum' => $allPayNum,
- 'newPayNum' => $newPayNumList,
- 'loginPlayerNum' => $loginPlayerNumList,
- 'newPlayerNum' => $newPlayerNumList,
- 'firstPayPlayerNum' => $firstPayPlayerNumList,
- 'AARRR' => $aarrrList,
- 'payRate' => $payRateList,
- 'payARPU' => $payARPUList,
- 'payARPPU' => $payARPPUList,
- 'inviteNumList' => $inviteNumList,
- 'firstPayRateList' => $firstPayRateList,
- 'newFirstPayRateList' => $newFirstPayRateList,
- 'newFirstPayRateList2' => $newFirstPayRateList2,
- 'vivoPayRateList' => $vivoPayRateList,
- 'vivoPayARPUList' => $vivoPayARPUList,
- 'vivoPayARPPUList' => $vivoPayARPPUList,
- 'androidPayRateList' => $androidPayRateList,
- 'androidPayARPUList' => $androidPayARPUList,
- 'androidPayARPPUList' => $androidPayARPPUList,
- 'iosPayRateList' => $iosPayRateList,
- 'iosPayARPUList' => $iosPayARPUList,
- 'iosPayARPPUList' => $iosPayARPPUList,
- ];
- $jsonData = json_encode($data);
- // if($isAllSid) {
- // oo::commonOprRedis('common')->set($totalKey, $jsonData);
- // oo::commonOprRedis('common')->expireAt($totalKey, strtotime('23:59:59'));//过期时间当天23:59:59
- // }
- return $jsonData;
- }
- /*
- * 获取广告的用户
- */
- public function getAdvertisement($param)
- {
- $stime = oo::functions()->uint($param['stime']);
- $etime = oo::functions()->uint($param['etime']);
- if( empty($stime) ){$stime = strtotime('-7 days');}//默认为7天前
- if( empty($etime) ){$etime = strtotime('-1 days');}
- // $stime = strtotime(date('Y-m-d 0:0:0', $stime));
- // $etime = strtotime(date('Y-m-d 23:59:59', $etime));
- $sdate = date('Ymd', $stime);
- $edate = date('Ymd', $etime);
- $reportTable = otable::report();
- $where = '1=1';
- !empty($sdate) && $where .= " AND date >= {$sdate} ";
- !empty($edate) && $where .= " AND date <= {$edate} ";
- $sql = " SELECT ad_android, ad_ios, install, date FROM {$reportTable} WHERE {$where} ORDER BY date ASC";
- $ret = oo::commonOprDb('common')->getAll($sql , 1);
- $data = [];
- foreach ($ret as $value) {
- $data[$value['date']] = $value['ad_android'] + $value['ad_ios'] + $value['install'];
- }
- return json_encode($data);
- }
- /**
- * 查询是否是首次充值
- * @param $uid
- * @param $time
- * @return bool
- */
- private function checkPayFirst($uid, $time)
- {
- $pay = oo::commonOprModel('statistics')->checkPayFirst($uid, $time);
- return $pay;
- }
- // /**
- // * 计算天数差
- // */
- // protected function getDays($second1, $second2)
- // {
- // if ($second1 < $second2) {
- // $tmp = $second2;
- // $second2 = $second1;
- // $second1 = $tmp;
- // }
- // $days = ($second1 - $second2) / 86400;
- // $dayList = array();
- // for ($i=0; $i <= $days ; $i++) {
- // $dayList[] = date('Ymd', $second2 + 86400*$i);
- // }
- // return $dayList;
- // }
- /**
- * 获取玩家订单信息
- */
- public function userList($param)
- {
- $data = array('list' => array(), 'total' => 0);
- $stime = oo::functions()->uint($param['stime']);
- $etime = oo::functions()->uint($param['etime']);
- $paymentType = oo::functions()->uint($param['payment_type']);
- $length = oo::functions()->uint($param['length']);
- if( empty($stime) ){$stime = strtotime('0:0:1');}//默认为当天
- if( empty($etime) ){$etime = time();}
- if( empty($paymentType) ){ $paymentType = 2; }
- $wherePaymentType = ' 1=1 ';
- if( $paymentType == 2 ){
- $wherePaymentType = " sl_status = 2 ";
- }
- $sidList = json_decode($param['sidList'], 1);
- if(empty($sidList)) {
- return json_encode([]);
- }
- $sidSqlStr = '';
- if(!$this->isAllSid($sidList)) {
- $sidSqlStr = " AND sl_sid IN ( ". implode(',', $sidList)." ) ";
- }
- $where = $wherePaymentType.$sidSqlStr;
- $page = oo::functions()->uint($param['page']);//起始页数
- $limit = oo::functions()->uint($param['limit']);//每页页数
- $slTable = $this->summarylistTable;
- $blackUidArr = $this->_payBlackList['uid'];
- $limitStr = !empty($length) ? " " :" limit $page, $limit";
- // $blackUidStr = implode(',', $blackUidArr);
- $sql = " SELECT sl_uid as uid, count(1) as num, sum(sl_pusd) as money FROM {$slTable} WHERE {$where} AND sl_order_time >= {$stime} AND sl_order_time <= {$etime} AND sl_uid GROUP BY sl_uid ORDER BY money DESC {$limitStr} ";
- // oo::logs()->debug3(['sqlStr'=>$sql], 'Payment.php');
- $list = oo::commonOprDb('common')->getAll($sql, MYSQLI_ASSOC);
- //获取总数
- $totalSql = " SELECT COUNT(1) AS t FROM (SELECT COUNT(1) AS num FROM {$slTable} WHERE {$where} AND sl_order_time >= {$stime} AND sl_order_time <= {$etime} AND sl_uid GROUP BY sl_uid ) as aa";
- $totalRet = oo::commonOprDb('common')->getOne($totalSql, MYSQLI_ASSOC);
- $data['list'] = $list;
- $data['total'] = $totalRet['t'];
- $data['blackUidList'] = $blackUidArr;
- return json_encode($data);
- }
- /**
- * vip用户列表
- * @param $param
- * @return string
- */
- public function vipuserList($param)
- {
- $stime = oo::functions()->uint($param['stime']);
- $etime = oo::functions()->uint($param['etime']);
- $data =[];
- if( empty($etime) ){$etime = time();}
- if( empty($stime) ){$stime = $etime;}//如果stime为空,默认查etime的时间 否则查stime时间内的vip用户
- $sidList = json_decode($param['sidList'], 1);
- if(empty($sidList)) {
- return json_encode([]);
- }
- $sidSqlStr = '';
- if(!$this->isAllSid($sidList)) {
- $sidSqlStr = " AND sl_sid IN ( ". implode(',', $sidList)." ) ";
- }
- $where = $sidSqlStr;
- // $page = oo::functions()->uint($param['page']);//起始页数
- // $limit = oo::functions()->uint($param['limit']);//每页页数
- //2.27 -wsc-注释以下内容
- // $table = otable::vippayment();
- // $sql = " SELECT uid, updatetime,flag FROM {$table} WHERE etime >= {$stime} GROUP BY uid ORDER BY updatetime DESC ";
- // $ret = oo::commonOprDb('common')->getAll($sql, 1);
- //
- $list = [];
- // if(!empty($ret)) {
- // $now = time();
- // foreach ($ret as $v) {
- // //获取vip用户的信息
- // $uid = $v['uid'];
- // $info = oo::commonOprModel('vip')->getVipInfo($uid);
- // $type = $info['type'] ?? 0;
- // if($type <= 0) {
- // continue;
- // }
- // //如果是后台加的VIP
- // if($v['flag'] == 2) {
- // $type = 999;
- // }
- // $etime = $info['etime'] ?? '';
- // $tmp = &$list[$type][$uid];
- // $tmp['uid'] = $uid;
- // $tmp['type'] = $type;
- // empty($v['updatetime']) ? $tmp['updatetime'] = '' : $tmp['updatetime'] = date('Y-m-d H:i:s', $v['updatetime']);
- // $tmp['etime'] = date('Y-m-d H:i:s', $etime);
- // $tmp['days'] = oo::commonOprModel('vip')->betweenDays($now, $etime);
- // $list[$type]['total'] += 1;
- // }
- // }
- // $data['list'] = $list;
- return json_encode($data);
- }
- /**
- * BluePay对账
- */
- public function account($param)
- {
- $data = array();
- $stime = oo::functions()->uint($param['stime']);
- $etime = oo::functions()->uint($param['etime']);
- $sidList = json_decode($param['sidList'], 1);
- if(empty($sidList)) {
- return json_encode([]);
- }
- $sidSqlStr = '';
- if(!$this->isAllSid($sidList)) {
- $sidSqlStr = " AND sl_sid IN ( ". implode(',', $sidList)." ) ";
- }
- $sTable = otable::summarylist();
- $gTable = otable::goodsCrazy();
- // $blackUidArr = $this->_payBlackList['uid'];
- $blackUidArr = [1,2,3];
- $blackUidStr = implode(',', $blackUidArr);
- $sql = " SELECT sl_gid, sl_pusd, sl_pay_time, gchannel, gplatform,sl_sid FROM {$sTable} AS st LEFT JOIN {$gTable} AS gt ON st.sl_gid = gt.gid WHERE sl_sid != -1 AND sl_status = 2 AND sl_pay_time >= {$stime} AND sl_pay_time <= {$etime} AND sl_uid NOT IN ({$blackUidStr}) $sidSqlStr";
- $sucList = oo::commonOprDb('common')->getAll($sql, 1);
-
- $sucDeviceMoney = $sucChannelMoney = array();
- if ($sucList) {
- foreach ($sucList as $info) {
- // $gid = $info['sl_gid'];
- // $money = $info['sl_pusd'];
- $money = $info['sl_pusd'];
- // $moneyThb = $info['gpriceThb'];
- // $gchannel = $info['gchannel'];
- $gplatform = $info['sl_sid'];
- //汇总
- $sucDeviceMoney[$gplatform]['money'] += $money;//设备汇总
- // $sucDeviceMoney[$gplatform]['moneyThb'] += $moneyThb;//设备汇总
- // $sucChannelMoney[$gchannel][$gplatform]['money'] += ($money * 100);//渠道汇总
- // $sucChannelMoney[$gchannel][$gplatform]['moneyThb'] += ($moneyThb);//渠道汇总泰铢
- }
- }
- $data['sucDeviceMoney'] = $sucDeviceMoney;
- //$data['sucChannelMoney'] = $sucChannelMoney;
- return json_encode($data);
- }
- /**
- * 热门商品列表
- * @param $param
- */
- public function hotGoodsList($param)
- {
- $stime = oo::functions()->uint($param['stime']);
- $etime = oo::functions()->uint($param['etime']);
- $page = oo::functions()->uint($param['page']);//起始页数
- $limit = oo::functions()->uint($param['limit']);//每页数量
- if( empty($stime) ){$stime = strtotime('-7 days');}//默认为七天前天
- if( empty($etime) ){$etime = time();}
- $stime = strtotime(date('Y-m-d 00:00:01', $stime));
- $etime = strtotime(date('Y-m-d 23:59:59', $etime));
- $slTable = $this->summarylistTable;
- $goodsTable = $this->goods;
- $blackUidArr = $this->_payBlackList['uid'];
- $blackUidSql = '';
- if(!empty($blackUidArr)) {
- $blackUidStr = implode(',', $blackUidArr);
- $blackUidSql = " AND sl_uid NOT IN ({$blackUidStr}) ";
- }
- $sql = " SELECT gid, gname, sl_sid as gchannel, gimg, sl_pusd AS gprice, count(sl_gid) as num
- FROM {$slTable} AS sl
- JOIN {$goodsTable} AS goods ON sl.sl_gid = goods.gid
- WHERE sl.sl_sid <> -1 AND sl.sl_pusd > 0 AND sl.sl_status = 2 {$blackUidSql} AND sl_order_time >= {$stime} AND sl_order_time <= {$etime}
- GROUP BY sl_gid,sl_pusd,sl_sid
- ORDER BY count(sl_gid) DESC
- LIMIT $page, $limit";
- // oo::logs()->debug3(['sql'=>$sql], 'Payment.php');
- $list = oo::commonOprDb('common')->getAll($sql, MYSQLI_ASSOC);
- empty($list) && $list = [];
- return json_encode($list);
- }
- // /**
- // * 返回付费玩家数据
- // * @param $param
- // * @return string
- // */
- // public function getPayUserStatistics($param)
- // {
- // $data = array();
- // $stime = oo::functions()->uint($param['stime']);
- // $etime = oo::functions()->uint($param['etime']);
- //
- // $dayList = $this->getDays($stime, $etime);
- // $loseList = [];
- // foreach ($dayList as $day) {
- // $day = date('Ymd', strtotime($day));
- // $key = okeys::paystatistics($day);
- // $loseList[$day] = oo::commonOprRedis('common')->get($key);
- // }
- // $data['dayList'] = $dayList;
- // $data['list'] = $loseList;
- //
- // return json_encode($data);
- // }
- /**
- * 进行补发
- * @param $param
- */
- public function reissue($param)
- {
- $uid = oo::functions()->uint($param['uid']);
- $orderId = oo::functions()->escape($param['orderId']);
- if(empty($uid) || empty($orderId)) {
- $this->response(1, '参数错误');
- }
- //进行写入补发操作
- $result = oo::commonOprModel('payment')->pubDelivery($uid, $orderId);
- if($result !== true) {//如果补发失败
- oo::logs()->debug3(['uid'=>$uid, 'orderId' => $orderId, 'time' => time()], 'reissue_error.log');
- return $this->response(2, '补发失败');
- }
- return $this->response(0, '补发成功');
- }
- /**
- * 查询订单
- * @param $param
- */
- public function detail($param)
- {
- $orderId = oo::functions()->escape($param['orderId']);
- $uid = oo::functions()->uint($param['uid']);
- if(empty($uid) || empty($orderId)) {
- $this->response(1, '参数错误');
- }
- $table = otable::payment($uid);
- $sql = "SELECT platform_serial_num FROM {$table} WHERE order_id = '{$orderId}' LIMIT 1 ";
- $ret = oo::commonOprDb('common')->getOne($sql, 1);
- if(!empty($ret)) {
- return $this->response(0, '查询成功',$ret);
- }
- return $this->response(1, '查询失败');
- }
- /**
- * 新版对账
- */
- public function checking($param)
- {
- $data = array('list' => []);
- $channel = oo::functions()->uint($param['channletype']);//支付渠道
- $table = otable::earnmoney();
- $sql = "SELECT * FROM {$table} WHERE local_channel = {$channel} ORDER BY check_time DESC ";
- $list = oo::commonOprDb('common')->getAll($sql, 1);
- $data['list'] = $list;
- return json_encode($data);
- }
- /**
- * 新版对账,添加支付渠道金额处理入库
- */
- public function actionChecking($param)
- {
- $data = array('code' => -1);
- $channel = oo::functions()->uint($param['pay_channel']);//支付渠道
- $money = oo::functions()->uint($param['pay_money']);//总金额
- $ordernum = oo::functions()->uint($param['pay_ordernum']);//订单数
- $currency = oo::functions()->uint($param['pay_currency']);//货币单位
- $time = oo::functions()->escape($param['pay_time']);//记录时间
- $other_income = oo::functions()->escape($param['other_income']);//第三方实际财务收入
- $exchange_rate = oo::functions()->escape($param['exchange_rate']);//汇率
- if (empty($channel) || empty($money) || empty($ordernum) || empty($currency) || empty($time) || empty($exchange_rate)) {
- return json_encode($data);
- }
-
- $time = strtotime($time);
- $table = otable::earnmoney();
- //先查询是否有填写相关数据
- $sql = " SELECT * FROM {$table} WHERE local_channel = {$channel} AND check_time = {$time} ";
- $info = oo::commonOprDb('common')->getOne($sql, 1);
- if (!empty($info)) {//已经存在了
- return json_encode($data);
- }
- //查询本地数据入库
- $monthStart = mktime(0,0,0,date('m', $time),1,date('Y', $time));
- $temp = date('Y-m-d', $time);
- $monthEnd = mktime(23,59,59,date('m', $time),date('d', strtotime("$temp +1 month -1 day")),date('Y', $time));
- $sTable = otable::summarylist();
- $gTable = otable::goodsCrazy();
- // $blackUidArr = $this->_payBlackList['uid'];
- $blackUidArr = [1,2,3];
- $blackUidStr = implode(',', $blackUidArr);
- $sqlChannl = ' AND gchannel = '.$channel;
- $sql = " SELECT sl_pusd, sl_sid FROM {$sTable} AS st LEFT JOIN {$gTable} AS gt ON st.sl_gid = gt.gid WHERE sl_sid != -1 AND sl_status = 2 AND sl_pay_time >= {$monthStart} AND sl_pay_time <= {$monthEnd} AND sl_uid NOT IN ({$blackUidStr}) {$sqlChannl} ";
- $sucList = oo::commonOprDb('common')->getAll($sql, 1);
- $local_totalmoney = $local_ordernum = $local_income = 0;
- if (!empty($sucList)) {
- foreach ($sucList as $info) {
- $local_totalmoney += $info['sl_pusd'];
- $local_ordernum++;
- $tmpIncome = $this->channelSettlement($info['sl_sid'], $info['sl_pusd']);
- $local_income += $tmpIncome;
- }
- }
-
- //本地金额乘汇率
- $local_totalmoney = $local_totalmoney * $exchange_rate;
- $local_income = $local_income * $exchange_rate;
-
- $local_income = round($local_income, 2);
- //判断商家和本地的金额差距
- $moneygap = 0;
- if ($local_totalmoney != $money) {
- $moneygap = $money - $local_totalmoney;
- }
- $sqlInsert = "INSERT INTO {$table} (local_channel, local_totalmoney, local_ordernum, local_currency, other_channel, other_totalmoney, other_ordernum, other_currency, moneygap, check_time, local_income, other_income,exchange_rate) VALUES($channel, $local_totalmoney, $local_ordernum, $currency, $channel, $money, $ordernum, $currency, $moneygap, $time, $local_income, $other_income,$exchange_rate)";
- oo::commonOprDb('common')->query($sqlInsert);
- $data['code'] = 1;
- return json_encode($data);
- }
- //渠道结算
- public function channelSettlement($channel, $money)
- {
- $income = 0;
- switch ($channel) {
- case 1://谷歌 本地*70
- case 3://苹果 本地*70
- $income = $money * 0.7;
- break;
- case 2://短信
- $income = (1 - 0.05) * 0.7 * $money;
- break;
- case 4://Bluecoins 96%
- $income = $money * 0.96;
- break;
- case 5://Truemoney
- case 6://12call
- case 7://dtac 86%
- $income = $money * 0.86;
- break;
- case 8://line 90%
- $income = $money * 0.9;
- break;
- default:
- break;
- }
- return $income;
- }
- /**
- * ajax编辑财务收入
- */
- public function ajaxEditIncome($param){
- $id = oo::functions()->uint($param['id']);
- $field = oo::functions()->escape($param['field']);
- $val = oo::functions()->escape($param['val']);
- $table = otable::earnmoney();
- $sql = "UPDATE {$table} SET {$field} = '{$val}' WHERE id = {$id} LIMIT 1";
- oo::commonOprDb('common')->query($sql);
- }
- // /**
- // * 支付渠道屏蔽管理
- // */
- // public function switch($param){
- // include WWWROOT.'config/config.sids.php';
- // $versionkey = okeys::limitversion();
- // $sidkey = okeys::limitsid();
- // $versionRs = oo::commonOprRedis('common')->get($versionkey);
- // $sidRs = oo::commonOprRedis('common')->get($sidkey);
- // $sidlist = [];
- // foreach ($config['sidlist'] as $sid => $packname){
- // $k = okeys::limitsidversion($sid);
- // $version = oo::commonOprRedis('common')->get($k);
- // $sidlist[$sid]['version'] = $version ? $version : '1.5.3';
- // $sidlist[$sid]['packname'] = $packname;
- // }
- // die(json_encode(array('v' => $versionRs, 's' => $sidRs, 'sidlist' => $sidlist)));
- // }
- /**
- * 支付渠道屏蔽管理
- */
- public function actionSwitch($param){
- $name = oo::functions()->escape($param['keyname']);
- $val = oo::functions()->escape($param['keyval']);
- $name = strtoupper($name);
- $key = 'PAYMENT:'.$name;
- oo::commonOprRedis('common')->set($key, $val);
- }
- /**
- * 获取当日所有的收入
- * @param $param
- */
- public function getDailyIncome($param)
- {
- $stime = $param['stime'];
- $etime = $param['etime'];
- $table = otable::summarylist();
- $sql = " SELECT SUM(sl_pusd) AS money FROM {$table} WHERE sl_status = 2 AND sl_order_time >= {$stime} AND sl_order_time <= {$etime} ";
- $info = oo::commonOprDb('common')->getOne($sql, 1);
- $money = empty($info) ? 0 : round($info['money'], 2);
- return json_encode(array('money' => $money));
- }
- /**
- * 获取首冲商品统计
- * @param $param
- * @return string
- */
- public function getFirstBuy($param)
- {
- $stime = oo::functions()->uint($param['stime']);
- $etime = oo::functions()->uint($param['etime']);
- if( empty($stime) ){$stime = strtotime('-7 days');}//默认为8天前
- if( empty($etime) ){$etime = time();}
- $stime = strtotime(date('Y-m-d 00:00:01', $stime));
- $etime = strtotime(date('Y-m-d 23:59:59', $etime));
- $dayList = $this->getDays($stime, $etime);
- $blackUidArr = $this->_payBlackList['uid'];
- $blackUidSql = '';
- if(!empty($blackUidArr)) {
- $blackUidStr = implode(',', $blackUidArr);
- $blackUidSql = " AND sl_uid NOT IN ({$blackUidStr}) ";
- }
- $where = $blackUidSql;
- $slTable = otable::summarylist();
- $sql = " SELECT sl_gid, sl_pay_time, sl_uid FROM {$slTable} WHERE sl_status = 2 AND sl_pay_time >= {$stime} AND sl_pay_time <= {$etime} {$where}";
- $ret = oo::commonOprDb('common')->getAll($sql, 1);
- //获取登录的人数
- $loginPlayer = otable::sta_lp();
- $sql2 = "SELECT total, ordertime FROM {$loginPlayer} WHERE ordertime >= {$stime} AND ordertime <= {$etime} AND sid=999 ";
- $ret2 = oo::commonOprDb('common')->getAll($sql2, 1);
- $loginList = [];
- foreach ($ret2 as $v) {
- $loginDay = date('Y-m-d', $v['ordertime']);
- $loginList[$loginDay] = $v['total'];
- }
- $list = [];
- $uidList = [];
- foreach ($ret as $value) {
- $slDay = date('Y-m-d', $value['sl_pay_time']);
- $gid = $value['sl_gid'];
- $uid = $value['sl_uid'];
- //如果是首冲商品
- if(in_array($gid, $this->_firstPayGoodsList)) {
- $list[$slDay]['firstBuy'][$gid] += 1;
- }
- //记录该天的充值人数、首冲的人数
- if(!in_array($uid, $uidList)) {
- $list[$slDay]['payNum'] += 1;
- $time = strtotime("$slDay 00:00:00");
- $checkFirstPay = $this->checkPayFirst($uid, $time);
- $checkFirstPay && $list[$slDay]['firstPayNum'] += 1;
- }
- }
- $data['dayList'] = $dayList;
- $data['loginList'] = $loginList;
- $data['slList'] = $list;
- return json_encode($data);
- }
-
- /**
- * 商品购买成功率曲线
- * @return string
- * Created by: hss
- * Created on: 2020/3/16 14:58
- */
- public function goodsPaySuccess($param){
- $stime = oo::functions()->uint($param['stime']);
- $etime = oo::functions()->uint($param['etime']);
- $gname = oo::functions()->escape($param['gname']);
- if( empty($stime) ){$stime = strtotime('-7 days');}//默认为8天前
- if( empty($etime) ){$etime = time();}
- $dayList = $this->getDays($stime, $etime);
-
- $where = '';
- //查询某个商品
- if(!empty($gname)){
- $gTable = otable::goodsCrazy();
- $sql = " SELECT gid,gpid,gname FROM {$gTable} WHERE gname = '{$gname}'";
- $goodsCrazy = oo::commonOprDb('common')->getOne($sql, 1);
- if($goodsCrazy){
- $gid = $goodsCrazy['gid'];
- $where = " AND sl_gid = '{$gid}'";
- }
- }
- $sidList = json_decode($param['sidList'], 1);
- $isAllSid = $this->isAllSid($sidList);
- $sidSqlStr = '';
- if(!$isAllSid) {
- $sidSqlStr = " AND sl_sid IN ( ". implode(',', $sidList)." ) ";
- }
- $where .= $sidSqlStr;
-
- $blackUidArr = $this->_payBlackList['uid'];
- $blackUidSql = '';
- if(!empty($blackUidArr)) {
- $blackUidStr = implode(',', $blackUidArr);
- $blackUidSql = " AND sl_uid NOT IN ({$blackUidStr}) ";
- $where .= $blackUidSql;
- }
-
- $slTable = otable::summarylist();
- $retSuccess = $retAll = $retDau = [];
- foreach($dayList as $k => $v){
- $stime = strtotime($v. ' 0:0:1');
- $etime = strtotime($v. ' 23:59:59');
- //支付成功的订单
- $sql = " SELECT sl_gid,count(*) as count,sl_order_time FROM {$slTable} WHERE sl_sid <> -1 AND sl_status = 2 AND sl_order_time >= {$stime} AND sl_order_time <= {$etime} {$where} GROUP BY sl_gid";
- $retSuccess[] = oo::commonOprDb('common')->getAll($sql, 1);
-
- //所有订单
- $sql = " SELECT sl_gid,count(*) as count,sl_order_time FROM {$slTable} WHERE sl_sid <> -1 AND sl_order_time >= {$stime} AND sl_order_time <= {$etime} {$where} GROUP BY sl_gid";
- $retAll[] = oo::commonOprDb('common')->getAll($sql, 1);
-
- //获取DAU
- $infoLogin = oo::commonOprModel('statistics')->getOneLoginPlayer($stime, $etime);
- if ($infoLogin) {
- $retDau[] = $infoLogin['total'];
- }else{
- $retDau[] = 0;
- }
- }
-
- //商品数据
- $gTable = otable::goodsCrazy();
- if($gid){
- $sql = " SELECT gid,gpid,gname FROM {$gTable} WHERE gid = '{$gid}'";
- }else{
- $sql = " SELECT gid,gpid,gname FROM {$gTable}";
- }
-
- $goodsCrazy = oo::commonOprDb('common')->getAll($sql, 1);
-
- $data['retSuccess'] = $retSuccess;
- $data['retAll'] = $retAll;
- $data['dayList'] = $dayList;
- $data['goods'] = $goodsCrazy;
- $data['retDau'] = $retDau;
- return json_encode($data);
- }
-
- /**
- * 支付白名单
- * @return string
- * Created by: hss
- * Created on: 2020/3/18 11:19
- */
- public function payWhite($param){
- $content = $this->_payBlackList['uid'];
- if(!empty($content)){
- $content = implode(",",$content);
- }
- $data['list'] = $content;
- return json_encode($data);
- }
-
- /**
- * 设置白名单
- * @return string
- * Created by: hss
- * Created on: 2020/3/18 11:19
- */
- public function setPayWhite($param){
- $content = oo::functions()->escape($param['content']);
- // $content = explode(",",$content);
- // $content = array_filter($content);
- // $contentStr = '<?php
- // return array("uid"=>'.var_export($content, true).');';
- //
- // $ret = file_put_contents(COM_CFG.'config.payBlackList.php', $contentStr);
- $key = okeys::payWhiteList();
- $tb = otable::payWhiteList();
- $sql="UPDATE {$tb} SET content = '{$content}'";
- $ret = oo::commonOprDb('common')->query($sql,1);
- oo::commonOprRedis('config')->set($key,$content);
- return $ret?1:0;
- }
-
- /**
- * 首充分析
- * @return string
- */
- public function firstPunch($param){
- $start_time = microtime();
- //以下是用每日充值分析代码复用 旧逻辑
- $stime = oo::functions()->uint($param['stime']);
- $etime = oo::functions()->uint($param['etime']);
- if( empty($stime) ){$stime = strtotime('-7 days');}//默认为7天前
- if( empty($etime) ){$etime = strtotime('-1 days');}
- $stime = strtotime(date('Y-m-d 00:00:01', $stime));
- $etime = strtotime(date('Y-m-d 23:59:59', $etime));
- $sidList = json_decode($param['sidList'], 1);
- $isAllSid = $this->isAllSid($sidList);
- $sidSqlStr = '';
- if(!$isAllSid) {
- $sidSqlStr = " AND sl_sid IN ( ". implode(',', $sidList)." ) ";
- }
- $dayList = $this->getDays($stime, $etime, 2);
-
- $slTable = $this->summarylistTable;
- $goodsTable = $this->goods;
- $blackUidArr = $this->_payBlackList['uid'];
- $blackUidSql = '';
- if(!empty($blackUidArr)) {
- $blackUidStr = implode(',', $blackUidArr);
- $blackUidSql = " AND sl_uid NOT IN ({$blackUidStr}) ";
- }
- $where = $sidSqlStr.$blackUidSql;
-
- //查询时间范围内的成功订单
- $sql = " SELECT sl_pusd, sl_uid, sl_pay_time, gchannel
- FROM {$slTable} AS sl LEFT JOIN {$goodsTable} AS goods ON sl.sl_gid = goods.gid
- WHERE sl_status = 2 AND sl_pay_time >= {$stime} AND sl_pay_time <= {$etime} $where ";
- $list = oo::commonOprDb('common')->getAll($sql, MYSQLI_ASSOC);
-
- $uid = [];
- //成功的订单
- foreach ($list as $_v) {
- $date = date('Ymd', $_v['sl_pay_time']);
- //处理当天重复下单的用户统计
- $uid[$date] ?? $uid[$date] = [];
- if(!in_array($_v['sl_uid'], $uid[$date])) {
- array_push($uid[$date], $_v['sl_uid']);
- }
- }
- $firstPayPlayerNumList = $firstPayPlayerOrderNumList = $firstPunchNumList = $firstPunchOrderNumList = [];
- foreach ($dayList as $day) {
- $firstPayPlayerNum = 0;//首次付费用户数
- $firstPayPlayerArr = [];//首次付费的用户
- $dateTmp = date('Ymd', strtotime($day));
- //今天付费的用户UID
- $payTodayPlayer = $uid[$dateTmp] ?? [];
- //首次付费的用户
- if(!empty($payTodayPlayer)) {
- $time = strtotime(date('Y-m-d 0:0:0', strtotime($day)));
- foreach ($payTodayPlayer as $vuid) {
- $checkBought = $this->checkPayFirst($vuid, $time);
- $checkBought && $firstPayPlayerNum++ ;
- if($checkBought){
- $firstPayPlayerArr[] = $vuid;
- }
- }
- }
- $stime = strtotime(date('Y-m-d 00:00:01', strtotime($day)));
- $etime = strtotime(date('Y-m-d 23:59:59', strtotime($day)));
- //当天首次付费用户的订单数
- $firstPayPlayerOrderNum = 0;
- if(!empty($firstPayPlayerArr)){
- $uidStr = implode(',', $firstPayPlayerArr);
- $sql = " SELECT count(*) AS count FROM {$slTable} WHERE sl_uid in({$uidStr}) AND sl_status = 2 {$sidSqlStr} AND sl_pay_time >= {$stime} AND sl_pay_time <= {$etime} ";
-
- $ret = oo::commonOprDb('common')->getOne($sql, MYSQLI_ASSOC);
- $firstPayPlayerOrderNum = intval($ret['count']);
- }else{
- $firstPayPlayerOrderNum = $firstPayPlayerNum;
- }
-
- $firstPayPlayerNumList[] = $firstPayPlayerNum;
- $firstPayPlayerOrderNumList[] = $firstPayPlayerOrderNum;
-
-
- //每天购买首充礼包的人数/次数
- $firstPunchArr = [];
- $sql = " SELECT sl_uid,sl_pusd,sl_gid FROM {$slTable} WHERE sl_gid = 601 AND sl_status = 2 {$sidSqlStr} AND sl_pay_time >= {$stime} AND sl_pay_time <= {$etime} ";
- $ret = oo::commonOprDb('common')->getAll($sql, MYSQLI_ASSOC);
- foreach($ret as $k => $v){
- if(!isset($firstPunchArr[$v['sl_uid']])){
- $firstPunchArr[$v['sl_uid']] = $v;
- }
- }
- $firstPunchNumList[] = count($firstPunchArr);
- $firstPunchOrderNumList[] = count($ret);
- }
-
- $data = array(
- 'firstPayPlayerNumList'=>$firstPayPlayerNumList,
- 'firstPayPlayerOrderNumList'=>$firstPayPlayerOrderNumList,
- 'firstPunchNumList'=>$firstPunchNumList,
- 'firstPunchOrderNumList'=>$firstPunchOrderNumList,
- 'resultTime'=>microtime() - $start_time,//统计耗时
- );
-
- return json_encode($data);
-
- }
- }
|