<?php
namespace App\Modules\OfficialAccount\Models;
use DB;
use Illuminate\Database\Eloquent\Model;
class OfficialAccount extends Model
{
protected $tables = 'official_accounts';
protected $fillable = ['distribution_channel_id', 'nickname', 'name', 'head_img', 'appid', 'appsecret', 'verify_txt', 'alias', 'qrcode_url', 'principal_name', 'service_type_info', 'func_info', 'authorizer_refresh_token', 'is_auth', 'cancel_auth_time', 'official_account_type', 'verify_type_info', 'subscribe_top_num', 'subscribe_day_maximum', 'sort_no', 'is_enabled'];
/**
* 根据appid获取公众号
*/
static function officialAccountByAppid($appid)
{
return self::where('appid', isset($appid) ? $appid : '')->first();
}
/**
* 根据appid获取公众号
*/
static function officialAccountArrByAppid($appid)
{
$official_account = self::where('appid', isset($appid) ? $appid : '')->first();
if (!empty($official_account)) {
return $official_account->toArray();
} else {
return null;
}
}
/**
* 获取所有公众号
*/
static function getAllOfficialAccountDB()
{
return self::select('id','nickname','appid','distribution_channel_id')->get();
}
/**
* 根据id获取公众号
*/
static function officialAccountById($id)
{
return self::where('id', $id)->first();
}
/**
* 根据appid和distribution_channel_id获取公众号
*/
static function officialAccountByAppidAndChannelId($appid, $distribution_channel_id)
{
return self::where(['appid' => $appid, 'distribution_channel_id' => $distribution_channel_id])->first();
}
/**
* 获取所有可用已授权公众号
*/
static function officialAuthAccounts($is_auth, $is_enabled)
{
return self::where(['is_auth' => isset($is_auth) ? $is_auth : '', 'is_enabled' => isset($is_enabled) ? $is_enabled : ''])->groupBy('appid')->get();
}
/**
* 获取1个可用已授权公众号
*/
static function getOneOfficialAuthAccounts($distribution_channel_id, $is_auth, $is_enabled)
{
return self::where(['distribution_channel_id' => $distribution_channel_id, 'is_auth' => $is_auth, 'is_enabled' => $is_enabled])->first();
}
/**
* 获取所有可用已授权强关类型的公众号
*/
static function officialAuthSubscribeAccounts($is_auth, $is_enabled)
{
return self::where(['is_auth' => isset($is_auth) ? $is_auth : '', 'is_enabled' => isset($is_enabled) ? $is_enabled : '', 'official_account_type' => 'force_subscribe'])->groupBy('appid')->get();
}
/**
* 删除指定公众号
*/
static function deleteOfficialAccount($id)
{
if (empty($id)) return false;
return self::where(['id' => $id])->delete();
}
/**
* 根据分销渠道号获取授权公众号
*/
static function officialAuthAccountBydistributionChannelId($distribution_channel_id, $is_auth, $is_enabled)
{
return self::where(['distribution_channel_id' => isset($distribution_channel_id) ? $distribution_channel_id : '', 'is_auth' => isset($is_auth) ? $is_auth : '', 'is_enabled' => isset($is_enabled) ? $is_enabled : ''])->orderBy('sort_no', 'desc')->get();
}
/**
* 根据distribution_channel_id获取公众号数量
*/
static function officialAccountCountByChannelId($distribution_channel_id)
{
return self::where('distribution_channel_id', isset($distribution_channel_id) ? $distribution_channel_id : '')->count();
}
/**
* 根据分销渠道号获取服务号列表
*/
static function allOfficialAccountBydistributionChannelId($distribution_channel_id, $is_enabled)
{
return self::where(['distribution_channel_id' => isset($distribution_channel_id) ? $distribution_channel_id : '', 'is_enabled' => isset($is_enabled) ? $is_enabled : ''])->get();
}
/**
* 根据分销渠道号获取服务号列表以及强关类型
*/
static function allOfficialAccountBydistributionChannelIdAndGetForceType($distribution_channel_id, $is_enabled)
{
return self::LeftJoin('distribution_force_subscribe_setting', 'official_accounts.appid', '=', 'distribution_force_subscribe_setting.appid')
->select(DB::raw('official_accounts.*, distribution_force_subscribe_setting.force_subscribe_type,distribution_force_subscribe_setting.resource_url'))
->where(['official_accounts.distribution_channel_id' => isset($distribution_channel_id) ? $distribution_channel_id : '', 'official_accounts.is_enabled' => isset($is_enabled) ? $is_enabled : ''])->orderBy('official_accounts.sort_no', 'desc')->get();
}
/**
* 根据托管获取服务号列表
*/
static function getTrusteeshipOfficialAccounts()
{
return self::LeftJoin('custom_msg_switchs_msgs', 'official_accounts.distribution_channel_id', '=', 'custom_msg_switchs_msgs.distribution_channel_id')
->select(DB::raw('official_accounts.*, custom_msg_switchs_msgs.status,custom_msg_switchs_msgs.custom_category'))
->where('official_accounts.is_auth', 1)
->where('official_accounts.is_enabled', 1)
->where('custom_msg_switchs_msgs.status', 1)
->where('custom_msg_switchs_msgs.custom_category', 'auto_custom_trusteeship')
->groupBy('official_accounts.appid')
->get();
}
/**
* 根据托管获取服务号列表
*/
static function getTrusteeshipOfficialAccountsWithParams($params)
{
$res = self::LeftJoin('custom_msg_switchs_msgs', 'official_accounts.distribution_channel_id', '=', 'custom_msg_switchs_msgs.distribution_channel_id')
->select(DB::raw('official_accounts.*, custom_msg_switchs_msgs.status,custom_msg_switchs_msgs.custom_category'))
->where('official_accounts.is_auth', 1)
->where('official_accounts.is_enabled', 1)
->where('custom_msg_switchs_msgs.status', 1)
->where('custom_msg_switchs_msgs.custom_category', 'auto_custom_trusteeship');
if(isset($params['in_channle_ids'])&&$params['in_channle_ids']){
$res->whereIn('official_accounts.distribution_channel_id',$params['in_channle_ids']);
}
if(isset($params['not_in_channle_ids'])&&$params['not_in_channle_ids']){
$res->whereNotIn('official_accounts.distribution_channel_id',$params['not_in_channle_ids']);
}
return $res->groupBy('official_accounts.appid')->get();
}
static function get_account_appids($account):array{
return self::LeftJoin('distribution_channels', 'official_accounts.distribution_channel_id','distribution_channels.id')
->LeftJoin('channel_users', 'channel_users.id','distribution_channels.channel_user_id')
->select('official_accounts.appid')
->where('channel_users.phone', $account)
->groupBy('official_accounts.appid')
->pluck('appid')->all();
}
/**
* 获取渠道粉丝阈值信息
*/
static function getBusinessChannelData($channels = [], $distribution_channel_name = '', $official_account_name = '', $isAll = false)
{
$total_sql = "(select COUNT(1) from force_subscribe_users WHERE force_subscribe_users.appid=official_accounts.appid) as day_total_fans_num";
$sql = "(select COUNT(1) from force_subscribe_users WHERE force_subscribe_users.appid=official_accounts.appid and created_at>=curdate()) as day_fans_num";
$obj = self::select('official_accounts.distribution_channel_id',
"distribution_channels.nickname as distribution_channel_name",
'official_accounts.nickname as service_name',
'official_accounts.subscribe_day_maximum',
'official_accounts.subscribe_top_num',
DB::raw($sql),
DB::raw($total_sql))
->join("distribution_channels", 'official_accounts.distribution_channel_id', "=", "distribution_channels.id");
if ($channels) {
$obj = $obj->whereIn('official_accounts.distribution_channel_id', $channels);
}
if ($distribution_channel_name) {
$obj = $obj->where('distribution_channels.nickname', 'like', '%' . $distribution_channel_name . '%');
}
if ($official_account_name) {
$obj = $obj->where('official_accounts.nickname', 'like', '%' . $official_account_name . '%');
}
if ($isAll) {
return $obj->get();
} else {
return $obj->paginate();
}
}
public static function getInfoList($params = [])
{
$search_object = self::select('distribution_channel_id', 'nickname','principal_name')->orderBy('created_at', 'desc');
if (isset($params['start_date']) && $params['start_date']) $search_object->where('created_at', '>=', $params['start_date']);
if (isset($params['end_date']) && $params['end_date']) $search_object->where('created_at', '<=', $params['end_date']);
return $search_object->get();
}
}