format('Y-m-d'); $start = $date; $end = date('Y-m-d', strtotime($date) + 86400); $offset = 0; $limit = 1000; $data = []; for ($i=0;$i<512;$i++) { //章节订购 $chapter_table = 'zsy_sub.chapter_orders'.$i; $force_user_table = 'yueduyun.force_subscribe_users'; $users = DB::connection('chapter_order_mysql')->select("select appid,{$chapter_table}.distribution_channel_id,{$chapter_table}.uid,sum(fee) sum_fee,sum(charge_balance) sum_charge_balance,sum(reward_balance) sum_reward_balance from {$chapter_table} left join {$force_user_table} on {$chapter_table}.uid = {$force_user_table}.uid where {$chapter_table}.created_at >= '{$date}' and {$chapter_table}.created_at < '{$end}' group by {$chapter_table}.uid"); print_r("select appid,{$chapter_table}.distribution_channel_id,{$chapter_table}.uid,sum(fee) sum_fee,sum(charge_balance) sum_charge_balance,sum(reward_balance) sum_reward_balance from {$chapter_table} left join {$force_user_table} on {$chapter_table}.uid = {$force_user_table}.uid where {$chapter_table}.created_at >= '{$date}' and {$chapter_table}.created_at < '{$end}' group by {$chapter_table}.uid" . "\n"); foreach ($users as $user) { if(!$user->appid) { //获取第一个appid $official_account = DB::connection('chapter_order_mysql')->table('yueduyun.official_accounts')->where('distribution_channel_id',$user->distribution_channel_id)->first(); if($official_account) { $user->appid = $official_account->appid; }else{ $user->appid = 'no'; } } if($user->appid) { @$data[$user->appid]['fee'] += $user->sum_fee; @$data[$user->appid]['charge_balance'] += $user->sum_charge_balance; @$data[$user->appid]['reward_balance'] += $user->sum_reward_balance; } } } //按本订购 $chapter_table = 'yueduyun.book_orders'; $force_user_table = 'yueduyun.force_subscribe_users'; $users = DB::connection('chapter_order_mysql')->select("select appid,{$chapter_table}.distribution_channel_id,{$chapter_table}.uid,sum(fee) sum_fee,sum(charge_balance) sum_charge_balance,sum(reward_balance) sum_reward_balance from {$chapter_table} left join {$force_user_table} on {$chapter_table}.uid = {$force_user_table}.uid where {$chapter_table}.created_at >= '{$date}' and {$chapter_table}.created_at < '{$end}' group by {$chapter_table}.uid"); print_r("select appid,{$chapter_table}.distribution_channel_id,{$chapter_table}.uid,sum(fee) sum_fee,sum(charge_balance) sum_charge_balance,sum(reward_balance) sum_reward_balance from {$chapter_table} left join {$force_user_table} on {$chapter_table}.uid = {$force_user_table}.uid where {$chapter_table}.created_at >= '{$date}' and {$chapter_table}.created_at < '{$end}' group by {$chapter_table}.uid" . "\n"); foreach ($users as $user) { if(!$user->appid) { //获取第一个appid $official_account = DB::connection('chapter_order_mysql')->table('yueduyun.official_accounts')->where('distribution_channel_id',$user->distribution_channel_id)->first(); if($official_account) { $user->appid = $official_account->appid; }else{ $user->appid = 'no'; } } if($user->appid) { @$data[$user->appid]['fee'] += $user->sum_fee; @$data[$user->appid]['charge_balance'] += $user->sum_charge_balance; @$data[$user->appid]['reward_balance'] += $user->sum_reward_balance; } } foreach ($data as $appid=>$_item) { $_data = []; $_data['appid'] = $appid; $_data['fee'] = $_item['fee']; $_data['date'] = $date; $_data['charge_balance'] = $_item['charge_balance']; $_data['reward_balance'] = $_item['reward_balance']; $official_account = DB::connection('chapter_order_mysql')->table('yueduyun.official_accounts')->where('appid',$appid)->first(); $_data['official_account_name'] = $official_account ? $official_account->nickname : ''; DB::connection('chapter_order_mysql')->table('yueduyun.official_account_day_sub_stats')->insert($_data); } Log::info("======服务号订阅数据 【任务执行结束】=====" . date("y-m-d H:i:s" . "\n")); print_r("======服务号订阅数据 【任务执行结束】=====" . date("y-m-d H:i:s" . "\n")); } } }