zhuishuyun_quickapp/app/Modules/Statistic/Services/SendStatsEmailService.php

<?php
/**
 * Created by PhpStorm.
 * User: z_yang
 * Date: 2018/03/09
 * Time: 20:18
 */

namespace App\Modules\Statistic\Services;

use DB;
use PHPMailer\PHPMailer\PHPMailer;

class SendStatsEmailService
{
    /**
     * 获取外部派单作品数据
     * @param $start_date
     * @param $to_date
     * @return mixed
     */
   public static function getOrderStats($start_date,$to_date) {
       $to_query = ['tmp.register_uv_rate>=0.85','tmp.register_uv_rate<=0.3'];
       //$to_query2 = ['s.register_user_num/s.uv>=0.85','s.register_user_num/s.uv>0.3 and s.register_user_num/s.uv<0.85','s.register_user_num/s.uv<=0.3'];
       $finnal = [[],[],[]];
       foreach ($to_query as $key=>$value){
           $sql = "select t.*,book_categories.pid,book_configs.recommend_index,books.category_id from (SELECT tmp.bid,tmp.book_name,sum(tmp.uv) UV,
sum(tmp.register_user_num) register_user_sum,
sum(tmp.recharge_amount) recharge_sum,
sum(tmp.force_user_num) force_user_sum,
sum(tmp.pay_user_num) pay_user_num,
count(tmp.bid) send_orders_num
  FROM (
		select s.bid,s.book_name,s.uv,s.register_user_num,s.recharge_amount,s.force_user_num,s.pay_user_num,s.register_user_num/s.uv as register_uv_rate 
		from send_orders_stats as s where s.send_time between '".$start_date."' and '".$to_date."'
	) tmp 
	WHERE $value GROUP BY tmp.bid) as t,books,book_categories,book_configs where books.id = t.bid and book_configs.bid=t.bid and book_categories.id = books.category_id";

           $res = DB::select($sql);
           foreach ($res as $item) {
               $temp = array(
                   'bid'=>$item->bid,
                   'book_name'=>$item->book_name,
                   'UV'=>$item->UV,
                   'register_user_sum'=>$item->register_user_sum,
                   'pay_user_num'=>$item->pay_user_num,
                   'recharge_sum'=>$item->recharge_sum,
                   'force_user_sum'=>$item->force_user_sum,
                   'pid'=>$item->pid,
                   'send_orders_num'=>$item->send_orders_num
               );

               $finnal[$key][] = $temp;
           }
       }

      return $finnal;
   }

   /**
     * 获取外部派单作品数据
     * @param $start_date
     * @param $to_date
     * @return mixed
     */
   public static function getOrderStatsNew() {
       $to_query = ['tmp.register_uv_rate>=0.85','tmp.register_uv_rate<=0.3'];
       //$to_query2 = ['s.register_user_num/s.uv>=0.85','s.register_user_num/s.uv>0.3 and s.register_user_num/s.uv<0.85','s.register_user_num/s.uv<=0.3'];
       $finnal = [[],[]];
       foreach ($to_query as $key=>$value){
           $level = $key+1;
           $res = DB::select("select * from (select t.*,book_categories.pid,book_configs.recommend_index,books.category_id from (SELECT tmp.bid,tmp.book_name,tmp.charge_type,tmp.is_on_shelf,sum(tmp.uv) UV,
sum(tmp.register_user_num) register_user_sum,
sum(tmp.recharge_amount) recharge_sum,
sum(tmp.force_user_num) force_user_sum,
sum(tmp.pay_user_num) pay_user_num,
SUM(tmp.uv_one_day) AS uv_one_day_sum,
SUM(tmp.uv_three_day) AS uv_three_day_sum,
SUM(tmp.recharge_amount_in_one_day)-SUM(tmp.one_day_year_recharge) AS recharge_amount_in_one_day,
SUM(tmp.recharge_amount_in_three_days)-SUM(tmp.three_day_year_recharge) AS recharge_amount_in_three_days
  FROM (
		select s.bid,s.book_name,book_configs.charge_type,book_configs.is_on_shelf,s.uv,s.register_user_num,s.recharge_amount,send_order_extra_stats.uv_one_day,send_order_extra_stats.uv_three_day,
		IFNULL((SELECT sum(price) FROM orders WHERE orders.send_order_id=s.send_order_id AND orders.`status`='PAID' AND orders.order_type='YEAR' AND orders.created_at > s.send_time AND UNIX_TIMESTAMP(orders.created_at)<=(UNIX_TIMESTAMP(s.send_time)+84600)),0) AS one_day_year_recharge,
		IFNULL((SELECT sum(price) FROM orders WHERE orders.send_order_id=s.send_order_id AND orders.`status`='PAID' AND orders.order_type='YEAR' AND orders.created_at > s.send_time AND UNIX_TIMESTAMP(orders.created_at)<=(UNIX_TIMESTAMP(s.send_time)+259200)),0) AS three_day_year_recharge,
		s.recharge_amount_in_one_day,s.recharge_amount_in_three_days,s.year_paid_num,
		s.force_user_num,s.pay_user_num,s.register_user_num/s.uv as register_uv_rate,s.force_user_num/s.register_user_num as force_sub_rate 
		from send_orders_stats as s JOIN send_order_extra_stats ON send_order_extra_stats.send_order_id=s.send_order_id 
		join book_configs on book_configs.bid=s.bid where book_configs.is_on_shelf in (1,2) and s.send_time between '".date('Y-m-d',strtotime('-34 day'))."' and '".date('Y-m-d 23:59:59',strtotime('-4 day'))."' and s.uv >= 100
	) tmp 
	WHERE $value and tmp.force_sub_rate <= 0.65 GROUP BY tmp.bid) as t,books,book_categories,book_configs where books.id = t.bid and book_configs.bid=t.bid and book_categories.id = books.category_id and book_categories.pid=2) tmp2 where tmp2.uv_one_day_sum >=1000
");
           //$res = DB::select($sql);
           foreach ($res as $item) {
               $one_day_recharge_uv_rate = $item->uv_one_day_sum>0?round($item->recharge_amount_in_one_day/$item->uv_one_day_sum,2):0;
               $three_days_recharge_uv_rate = $item->uv_three_day_sum>0?round($item->recharge_amount_in_three_days/$item->uv_three_day_sum,2):0;
               $three_days_recharge_one_day_uv = $item->uv_one_day_sum>0?round($item->recharge_amount_in_three_days/$item->uv_one_day_sum,2):0;
               if(($level==1 && (($item->charge_type=='BOOK' && $one_day_recharge_uv_rate>=1)||($item->charge_type=='CHAPTER' && $one_day_recharge_uv_rate>=0.8)))
                   ||
                   //($level==2 && (($item->charge_type=='BOOK' && $one_day_recharge_uv_rate>=0.85)||($item->charge_type=='CHAPTER' && $three_days_recharge_uv_rate>=1)))
                   ($level==2 && (($item->charge_type=='BOOK' && $one_day_recharge_uv_rate>=0.85)||($item->charge_type=='CHAPTER' && $three_days_recharge_one_day_uv>=1.15)))
               ){
                   $temp = array(
                       'bid'=>$item->bid,
                       'book_name'=>$item->book_name,
                       'UV'=>$item->UV,
                       'register_user_sum'=>$item->register_user_sum,
                       'pay_user_num'=>$item->pay_user_num,
                       'recharge_sum'=>$item->recharge_sum,
                       'force_user_sum'=>$item->force_user_sum,
                       'pid'=>$item->pid,
                       'charge_type'=>$item->charge_type,
                       'recharge_amount_in_one_day'=>$item->recharge_amount_in_one_day,
                       'recharge_amount_in_three_days'=>$item->recharge_amount_in_three_days,
                       'uv_one_day_sum'=>$item->uv_one_day_sum,
                       'uv_three_day_sum'=>$item->uv_three_day_sum,
                       'is_on_shelf'=>$item->is_on_shelf
                   );
                   $finnal[$key][] = $temp;
               }

           }
       }

      return $finnal;
   }

   public static function SendHtmlEmailWithAcce(array $to_email,array $param,$accessory=''){
       $mail = new PHPMailer(true);// Passing `true` enables exceptions
       $mail->CharSet = 'UTF-8';//'UTF-8';
       try {
           //Server settings
           $mail->SMTPDebug = 2;                                 // Enable verbose debug output
           $mail->isSMTP();                                      // Set mailer to use SMTP
           $mail->Host = 'smtp.126.com';     //$mail->Host = 'smtp.126.com'; Specify main and backup SMTP servers
           $mail->SMTPAuth = true;                               // Enable SMTP authentication
           $mail->Username = 'tushengxiang@126.com'; //$mail->Username = 'tushengxiang@126.com';                // SMTP username
           $mail->Password = 'personsincere199';                           // SMTP password
           $mail->SMTPSecure = 'ssl';                            // Enable TLS encryption, `ssl` also accepted
           $mail->Port = 587;                                    // TCP port to connect to

           //Recipients
           $mail->setFrom('tushengxiang@126.com', 'tusx');
           $mail->addAddress($to_email[0]['address'], $to_email[0]['name']);
           array_shift($to_email);
           foreach ($to_email as $item) {
               $mail->addCC($item['address'], $item['name']);     // Add a recipient
           }

           //Attachments
           if($accessory) $mail->addAttachment($accessory);// Add attachments

           $mail->isHTML(true);                                  // Set email format to HTML
           $mail->Subject = $param['subject'];
           $mail->Body    = $param['body'];
           $mail->send();
           //echo 'Message has been sent';
       } catch (\Exception $e) {
           echo 'Message could not be sent. Mailer Error: ', $mail->ErrorInfo;
       }
   }

}