wangdu_public_api/app/Modules/OfficialAccount/Models/OfficialAccount.php

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