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