wangdu_public_api/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 = 'wphelper@126.com'; //$mail->Username = 'tushengxiang@126.com';                // SMTP username
            $mail->Password = 'ylwtsmt123';                           // SMTP password
            $mail->SMTPSecure = 'ssl';                            // Enable TLS encryption, `ssl` also accepted
            $mail->Port = 587;                                    // TCP port to connect to

            //Recipients
            $mail->setFrom('wphelper@126.com', 'System');
            $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;
        }
    }

    public static function SendMultyHtmlEmailWithAcce(array $to_email, array $param, array $accessorys = [])
    {
        $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 = 'wphelper@126.com'; //$mail->Username = 'tushengxiang@126.com';                // SMTP username
            $mail->Password = 'ylwtsmt123';                           // SMTP password
            $mail->SMTPSecure = 'ssl';                            // Enable TLS encryption, `ssl` also accepted
            $mail->Port = 587;                                    // TCP port to connect to

            //Recipients
            $mail->setFrom('wphelper@126.com', 'system');
            $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 ($accessorys) {
                foreach ($accessorys as $accessory) {
                    $mail->addAttachment($accessory);
                }
            }

            $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;
        }
    }
}