暫無描述

DisRoiByDayHistory.php 3.2KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576
  1. <?php
  2. namespace App\Console\Commands;
  3. use Illuminate\Console\Command;
  4. use DB;
  5. use App\AdCost;
  6. class DisRoiByDayHistory extends Command {
  7. protected $signature = 'DisRoiByDayHistory';
  8. /**
  9. * The console command description.
  10. *
  11. * @var string
  12. */
  13. protected $description = '地域7日roi';
  14. public function handle()
  15. {
  16. $this->DisRoiByDayHistory();
  17. }
  18. public function DisRoiByDayHistory(){
  19. for($i=23; $i>=7; $i--){
  20. $stime = date('Y-m-d', strtotime('-'.$i.' day'));
  21. $e = $i-7;
  22. $etime = $date = date('Y-m-d', strtotime('-'.$e.' day'));
  23. //计算date 7天内的roi
  24. $result = AdCost::select(DB::raw('sum(cost) as total_cost, sum(conversion_times) as conversion_times, city'))->where('ad_time', $stime)->groupBy('city')->get();
  25. $result = json_decode(json_encode($result), true);
  26. foreach($result as $k=>&$v){
  27. $data = array();
  28. $data['ad_time'] = $stime;
  29. $data['city'] = $v['city'];
  30. //新粉收入
  31. //当日加粉
  32. $city_name = str_replace('市', '', $v['city']);
  33. $phones = DB::table('customers')->where('fanTime', $stime)->where('receiverCity','like', '%'.$city_name.'%')->lists('phone');
  34. #当日加粉7日订单总计:
  35. $order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->whereIn('receiverMobile', $phones)->where('createTime', '>=', $stime)->where('createTime', '<', $etime)->where('is_del', 0)->first();
  36. #当日新粉成单:
  37. $new_order = DB::table('order')->select(DB::raw('sum(receivedAmount) as order_amount, count(1) as order_count'))->where('createTime','>=', $stime)->where('createTime','<=', $stime.' 23:59:59')->where('is_del', 0)->whereIn('receiverMobile', $phones)->first();
  38. // 1.当日新粉成单数
  39. $data['new_fan_order_count'] = $new_order->order_count;
  40. // 2.当日新粉成交额
  41. $data['new_fan_order_amount'] = $new_order->order_amount;
  42. // 3.当日粉丝总成交额
  43. $data['order_amount'] = $order->order_amount;
  44. // 7日总单数
  45. $data['order_count'] = $order->order_count;
  46. //新粉roi
  47. //$v['new_roi'] = $v['total_cost']>0 ? round($v['new_order_amount'] / $v['total_cost'], 4) * 100 .'%' : '';
  48. //7日累计roi
  49. //$v['total_roi'] = $v['total_cost']>0 ? round($v['order_amount'] / $v['total_cost'], 4) * 100 .'%' : '';
  50. $data['fan_count'] = count($phones);
  51. $data['gzh_count'] = $v['conversion_times'];
  52. $data['cost'] = $v['total_cost'];
  53. //插入数据
  54. $res = $this->insertData($data);
  55. }
  56. }
  57. }
  58. /**
  59. * @param idate:统计日期 rate:成单率 date:昨日
  60. *
  61. */
  62. public function insertData($data){
  63. return DB::table('district_roi_7')->insert($data);
  64. }
  65. }