<?php
namespace Controller\fob_ai;
use Controller\Base;
use Lib\Mail\MailFun;
use Lib\Verify;
use Model\folderSql;
use function Swoole\Coroutine\Http\request;
/**
* 黑格 fob 那边专用 业务
* 为定制逻辑
* @author：dc
* @time 2024/7/18 11:40
* Class MailList
* @package Controller\fob_ai
*/
class MailListV2 extends Base {
private $cache = 600;
private function getTable(){
if(app()->request('table')=='lists2'){
return 'lists_2';
}
return 'lists';
}
/**
* 当前邮箱下指定的文件夹
* @param string $folder
* @return array
* @throws \Lib\Err
* @author：dc
* @time 2024/7/19 11:37
*/
private function getFolderId(string $folder,$emails=[]){
if(!$emails){
$emails = $this->getEmails('id');
}
$k = md5(json_encode($emails));
// 查询 文件夹
static $folderList;
if(empty($folderList[$k])){
$folderList[$k] = db()->all(folderSql::all($emails));
}
$folder_id = [];
// 文件夹id
if($folderList[$k]){
foreach ($folderList[$k] as $item){
if(folderAlias($item['folder']) == $folder){
$folder_id[] = $item['id'];
}
}
}
if(!$folder_id){
app()->e('folder_not_fount');
}
return $folder_id;
}
/**
* 处理like条件
* @param $str
* @return string
* @author：dc
* @time 2024/9/4 11:14
*/
private function getLikeStr($str){
if(str_starts_with($str, '^')){
return addslashes(mb_substr($str,1,99)).'%%';
}
return '%%'.addslashes($str).'%%';
}
/**
* 邮件列表
* 接收参数
* page 分页
* limit 每页数量
* mail_id 指定邮件id
* attachment 是否附件
* emails 邮箱
* folder 文件夹
* from_not_in 不在这些发件箱的邮件
* @throws \Lib\Err
* @author：dc
* @time 2024/7/18 11:40
*/
public function lists(){
// 分页 页数
$page = app()->request('page',1,'intval');
$page = $page ? $page : 1;
$limit = app()->request('limit',20,'intval');
$limit = $limit ? $limit : 1;
// 指定id
$ids = app()->requestArr('mail_id');
foreach ($ids as $i=>$d){
if(!is_numeric($d)){
unset($ids[$i]);
}
}
$where = [];
// 目录
$folder = app()->request('folder','收件箱');
// 只允许查询这里文件夹
if(!in_array($folder,['收件箱','发件箱','垃圾箱','星标邮件','预热收件箱','预热发件箱','自动回复收件箱','Starred','草稿箱'])){
app()->e('folder_not_fount');
}
$where['is_hots'] = 0;
if($folder=='星标邮件'||$folder=='Starred'){
$folder = '收件箱';
$where['flagged'] = 1; // 星标
unset($where['is_hots']);
}elseif ($folder=='预热收件箱'){
$folder = '收件箱';
$where['is_hots'] = 1;
}elseif ($folder=='预热发件箱'){
$folder = '发件箱';
$where['is_hots'] = 1;
} elseif ($folder=='自动回复收件箱'){
$is_auto_inbox = 1;
$folder = '收件箱';
$where['is_hots'] = 0;
}elseif ($folder=='草稿箱'){
$folder = '草稿箱';
$where['is_hots'] = 0;
}
$seen = app()->request('seen',-1,'intval');
// 已读/未读
if(paramHas('seen')){
if(in_array($seen,[0,1])){
$where['seen'] = $seen;
}
}
// 搜索标题
$keyword = app()->request('keyword');
if($keyword){
$where['subject.like'] = "%%".addcslashes(str_replace('%','',$keyword),"'\"")."%%";
}
// 联系人
$address = app()->request('address');
if($address){
if(is_array($address)){
// 发贱人
if(Verify::sEmail($address['from']??'')){
if($folder=='发件箱'){
$where['to'] = $address['from'];
}else{
$where['from'] = $address['from'];
}
}
}
}
// 星标就不加文件夹赛选
if(empty($where['flagged'])){
$where['_'] = trim(trim($this->assignSql($folder)),'and');
}else{
$where['_'] = trim(trim($this->assignSql2()),'and');
}
if($ids) $where['id'] = $ids;
if(app()->request('attachment',0,'bool_Val')){
// 附件
$where['is_file'] = 1; //附件
}
// 软删
$where['deleted'] = 0;
$filed = '`id`,`uid`,`subject`,`from`,`from_name`,`flagged`,`seen`,`udate`,`folder_id`,`is_file`,`description`,`'.$this->getTable().'`.`email_id`,`to_name`,`draft`';
$filedhot = '`id`,`uid`,`subject`,`from`,`from_name`,`flagged`,`seen`,`udate`,`folder_id`,`is_file`,`description`,`lists_hot`.`email_id`,`to_name`';
$sql = "select %s from `".$this->getTable()."` where ".dbWhere($where);
$sqlhot = "select %s from `lists_hot` where ".dbWhere($where);
/************ 2024-09-02 *************/
if($folder=='收件箱' && empty($where['flagged']) && !$ids) {
$sql = "select %s from `".$this->getTable()."` left join `lists_auto` on `".$this->getTable()."`.`id`= `lists_auto`.`list_id` where ".dbWhere($where);
if(!empty($is_auto_inbox)){
$sql.= ' and `lists_auto`.`list_id` IS NOT NULL';
}else{
$sql.= ' and `lists_auto`.`list_id` IS NULL';
// 模糊标题搜索
$subject = app()->request('subject');
if ($subject) {
$subject = is_array($subject) ? $subject : [$subject];
foreach ($subject as $k => $sub) {
$subject[$k] = '`subject` ' . (empty($is_auto_inbox) ? ' not ' : '') . 'like "' . $this->getLikeStr($sub) . '"';
}
}
// 不在此邮箱中的
$from_not_in_like = app()->request('from_not_in_like');
if ($from_not_in_like) {
$from_not_in_like = is_array($from_not_in_like) ? $from_not_in_like : [$from_not_in_like];
foreach ($from_not_in_like as $k => $sub) {
$from_not_in_like[$k] = '`from` ' . (empty($is_auto_inbox) ? ' not ' : '') . 'like "' . $this->getLikeStr($sub) . '"';
}
}
if ($subject || $from_not_in_like) {
$sql .= ' and (';
if ($subject) $sql .= implode(empty($is_auto_inbox) ? ' and ' : ' or ', $subject);
if ($from_not_in_like) $sql .= ' and (' . implode(empty($is_auto_inbox) ? ' and ' : ' or ', $from_not_in_like) . ')';
$sql .= ')';
}
}
}
/************ 2024-09-02 *************/
// 查询列表数据
$lists = db()->all(sprintf(empty($where['is_hots'])?$sql:$sqlhot,empty($where['is_hots'])?$filed:$filedhot).(empty($where['flagged'])?'':' group by `udate`')." order by `udate` desc limit {$limit} offset ".(($page-1)*$limit));
// map
$lists = array_map(function ($v){
$v['uuid'] = md5($v['email_id'].'-'.$v['folder_id'].'-'.$v['uid']);
if(!empty($v['description'])){
$v['description'] = @html_entity_decode($v['description'], ENT_COMPAT, 'UTF-8');
}
$v['to_name'] = @json_decode($v['to_name'],true);
$v['to_name'] = $v['to_name']?:[];
if($v['to_name']){
if(!empty($v['to_name'][0]['email'])){
$v['to'] = $v['to_name'][0]['email'];
}
$v['to_name'] = MailFun::mb_coding($v['to_name'][0]['name']??'');
}
if(is_array($v['to_name'])){
$v['to_name'] = '';
}
return $v;
},$lists?:[]);
if($lists){
// 总数
if(empty($where['flagged'])){
$total = db()->cache($this->cache)->count(sprintf(empty($where['is_hots'])?$sql:$sqlhot,"count(*)"));
}else{
$total = db()->cache($this->cache)->count("select count(*) from (".sprintf($sql,"`id`").' group by `udate`) as t');
}
}else{
$total = 0;
}
app()->_json(listsPage($lists,$total,$page,$limit));
}
private function assignSql($folder){
// 被分配的
$assign = app()->request('assign');
$sql = '';
$fids = [];
if(!empty($assign['email_id'])){
// 此处请求中的
$email = array_values(array_intersect($assign['email_id'],$this->getEmails('id')));
if($email){
$fids = $this->getFolderId($folder,$email);
// 有目录id和from
if($fids && !empty($assign['from'])){
if($folder=='发件箱'){
$assign['from'] = is_array($assign['from']) ? array_map(function ($v){
return '`to` like "%%'.$v.'%%"';
},$assign['from']) : ['`to` like "%%'.$assign['from'].'%%"'];
$sql = dbWhere(['folder_id'=>$fids, '_'=> '('.implode(' or ',$assign['from']).')']);
}else{
$sql = dbWhere(['folder_id'=>$fids, 'from'=>$assign['from']]);
}
}
}
// 返回 不在assign中的目录
$afids = array_values(array_diff($this->getFolderId($folder),$fids));
if($sql && $afids){
return '(('.dbWhere(['folder_id'=>$afids]).') or ('.$sql.')) and ';
}else if($afids){
return dbWhere(['folder_id'=>$afids]).' and ';
}else if($sql&&!$afids){
return '('.$sql.') and ';
}
return '`folder_id` = -1 and ';
}
return dbWhere(['folder_id'=>$this->getFolderId($folder)]).' and ';
}
private function assignSql2(){
// 被分配的
$assign = app()->request('assign');
$sql = '';
if(!empty($assign['email_id'])){
// 分配的联系人
$email = array_values(array_intersect($assign['email_id'],$this->getEmails('id')));
if($email&&!empty($assign['from'])){
$sql = dbWhere(['email_id'=>$email]).' and ('.dbWhere(['from'=>$assign['from']]).' or '.dbWhere(['to'=>$assign['from']]).')';
}
// 分配的邮箱
$email2 = array_diff($this->getEmails('id'),$assign['email_id']);
if($email2){
if($sql){
return '('.dbWhere(['email_id'=>$email2]).' or ('.$sql.')) and ';
}
return dbWhere(['email_id'=>$email2]).' and ';
}
if($sql){
return '('.$sql.') and ';
}
return '`email_id` = -1 and ';
}
// 默认
return dbWhere(['email_id'=>$this->getEmails('id')]).' and ';
}
private function assignSql3($folder){
// 被分配的
$assign = app()->request('assign');
$sql = '';
$fids = [];
if(!empty($assign['email_id'])){
// 此处请求中的
$email = array_diff($this->getEmails('id'),$assign['email_id']);
if($email){
$fids = $this->getFolderId($folder,$email);
// 有目录id和from
if($fids){
return dbWhere(['folder_id'=>$fids]).' and ';
}
}
return '`folder_id` = -1 and ';
}
return dbWhere(['folder_id'=>$this->getFolderId($folder)]).' and ';
}
/**
* 统计收件箱的数量
* @author：dc
* @time 2024/7/19 10:15
*/
public function count(){
if(!$this->getEmails('id')){
app()->e('email_request_required');
}
// 时间 必须是数组
$udate = app()->request('udate');
if($udate && is_array($udate) && count($udate) == 2){
$udate = array_values($udate);
$where['udate.between'] = array_map(function ($v){
if(is_numeric($v)) return $v;
return strtotime($v);
},$udate);
}
// show_count_filed
$show_count_filed = app()->requestArr('show_count_filed',['inbox', 'send', 'unseen', 'flagged', 'junk', 'hot_inbox', 'hot_send']);
$sql = "select count(*) from `".$this->getTable()."` where ";
$sqlhot = "select count(*) from `lists_hot` where ";
$where['deleted'] = 0; //未删状态
// 发件箱
if(in_array('hot_send',$show_count_filed)){
// $where['folder_id'] = $this->getFolderId('发件箱');
// 预热发件箱
$where['is_hots'] = 1;
$fCount = db()->cache(86400)->count($sql.$this->assignSql3('发件箱').dbWhere($where));
$fCount += db()->cache(86400)->count($sqlhot.$this->assignSql3('发件箱').dbWhere($where));
}
// 预热收件箱
if(in_array('hot_inbox',$show_count_filed)) {
// $where['folder_id'] = $this->getFolderId('收件箱');
$where['is_hots'] = 1;
$sCount = db()->cache(86400)->count($sql .$this->assignSql3('收件箱'). dbWhere($where));
$sCount += db()->cache(86400)->count($sqlhot .$this->assignSql3('收件箱'). dbWhere($where));
}
unset($where['folder_id']);
if(in_array('send',$show_count_filed)) {
// $where['folder_id'] = $this->getFolderId('发件箱');
$where['is_hots'] = 0;
$faCount = db()->cache($this->cache)->count($sql . $this->assignSql('发件箱').dbWhere($where));
}
// 垃圾箱
if(in_array('junk',$show_count_filed)) {
// $where['folder_id'] = $this->getFolderId('垃圾箱');
$where['is_hots'] = 0;
$lajiCount = db()->cache($this->cache)->count($sql . $this->assignSql('垃圾箱').dbWhere($where));
}
// 收件箱
// $where['folder_id'] = $this->getFolderId('收件箱');
$where['is_hots'] = 0;
$sql = "select count(*) from `".$this->getTable()."` left join `lists_auto` on `".$this->getTable()."`.`id`= `lists_auto`.`list_id` where `lists_auto`.`list_id` IS NULL and ".$this->assignSql('收件箱');
// 模糊标题搜索
$subject = app()->request('subject');
if($subject){
$subject = is_array($subject) ? $subject : [$subject];
foreach ($subject as $k=>$sub){
$subject[$k] = '`subject` not like "'.$this->getLikeStr($sub).'"';
}
}
// 不在此邮箱中的
$from_not_in_like = app()->request('from_not_in_like');
if($from_not_in_like){
$from_not_in_like = is_array($from_not_in_like) ? $from_not_in_like : [$from_not_in_like];
foreach ($from_not_in_like as $k=>$sub){
$from_not_in_like[$k] = '`from` not like "'.$this->getLikeStr($sub).'"';
}
}
if($subject || $from_not_in_like){
$sql .= ' (';
if($subject) $sql.= implode(' and ',$subject);
if($from_not_in_like) $sql.= ' and ('.implode(' and ',$from_not_in_like).')';
$sql .= ') and ';
}
// 收件箱
if(in_array('inbox',$show_count_filed)) {
$shouCount = db()->cache($this->cache)->count($sql . dbWhere($where));
}
// 收件箱中标记为已回复的邮件
if(in_array('replied',$show_count_filed)){
// $where['answered'] = 1;
$repliedCount = isset($shouCount) ? $shouCount : db()->cache($this->cache)->count($sql . dbWhere($where));
// unset($where['answered']);
}
// 未读
if(in_array('unseen',$show_count_filed)) {
$where['seen'] = 0;
$where['is_hots'] = 0;
$seenCount = db()->cache($this->cache)->count($sql . dbWhere($where));
unset($where['seen']);
}
// 星标
if(in_array('flagged',$show_count_filed)) {
$where['flagged'] = 1;
unset($where['folder_id']);
// $where['email_id'] = $emails;
unset($where['is_hots']);
$flaggedCount = db()->cache($this->cache)->count("SELECT COUNT(*) FROM (select `id` from `".$this->getTable()."` where " . $this->assignSql2().dbWhere($where).' group by `udate`) as c');
}
$data = [];
if(isset($shouCount)) $data['inbox'] = $shouCount;
if(isset($repliedCount)) $data['replied'] = $repliedCount;
if(isset($faCount)) $data['send'] = $faCount;
if(isset($seenCount)) $data['unseen'] = $seenCount;
if(isset($flaggedCount)) $data['flagged'] = $flaggedCount;
if(isset($lajiCount)) $data['junk'] = $lajiCount;
if(isset($sCount)) $data['hot_inbox'] = $sCount;
if(isset($fCount)) $data['hot_send'] = $fCount;
app()->_json($data);
}
}