updateOrInsert(['UserID' => $UserID], $arr); $key = "AccountCookie_$UserID"; Redis::set($key, json_encode($arr)); Redis::expire($key, 36000); } /** * 保存广告点击的 Cookie 数据到 AccountCookie 表。 * * 查重优先级:SPE_KEY > UserID > FPID > FF。 * 安全保护:如果通过 FPID/FF 找到的记录已绑定了其他 UserID, * 不覆盖旧记录,而是插入新记录,防止串号。 */ public static function saveCookie($UserID, $data, $FPID = '', $FF = '',$SPE_KEY='') { $agent = $_SERVER['HTTP_USER_AGENT']; $table = TableName::QPAccountsDB() . 'AccountCookie'; $checkKey = ""; $checkValue = ""; if (!empty($SPE_KEY)) { $checkKey = 'SPE_KEY'; $checkValue = $SPE_KEY; } else if (!empty($UserID)) { $checkKey = 'UserID'; $checkValue = $UserID; } else if (!empty($FPID)) { $checkKey = 'FPID'; $checkValue = $FPID; } else if (!empty($FF)) { $checkKey = 'FF'; $checkValue = $FF; } if (empty($checkKey)) return; $existing = DB::table($table)->where($checkKey, $checkValue)->first(); $arr = [ 'UserID' => $UserID, 'FPID' => $FPID, 'FF' => $FF, 'SPE_KEY' => $SPE_KEY, 'Cookie' => $data['cookie'], 'Params' => $data['params'], 'LocalStorage' => $data['localStorage']?? "", 'UrlSign' => $data['url_sign'], 'Platform' => $data['type'], 'GameUA' => $agent, 'ClickUA' => $data['agent'], 'Origin' => $data['origin'] ?? "", 'Locale' => $data['locale'] ?? $_SERVER['HTTP_ACCEPT_LANGUAGE'], 'ClickTime' => date('Y-m-d H:i:s', $data['time']), 'UniqueAction' => 2, 'IP' => IpLocation::getIP(), ]; if (!$existing) { DB::table($table)->insert($arr); if (!empty($UserID)) { if (strstr($arr['Params'], 'gclid') || strstr($arr['Params'], 'fbclid')) { (new AccountsSource())->notExistsInsert($UserID, "APK_AD(" . $arr['Platform'] . ")"); } } if ($arr['Platform'] == 'kw') { $cookie = $arr['Cookie']; } if ($arr['Platform'] == 'gg') { $cookie = $arr['Params']; } }else if(!empty($data['cookie'])){ $existingUserID = $existing->UserID ?? 0; if (!empty($existingUserID) && $existingUserID != 0 && !empty($UserID) && $UserID != $existingUserID) { $arr['UserID'] = $UserID; DB::table($table)->insert($arr); return; } $shouldInsertNew = false; if (in_array($checkKey, ['FPID', 'FF'])) { $newFbclid = self::extractFbclid($data['cookie'] ?? ''); $existingFbclid = self::extractFbclid($existing->Cookie ?? ''); $newChannel = $data['url_sign'] ?? ''; $existingChannel = $existing->UrlSign ?? ''; if (!empty($newFbclid) && !empty($existingFbclid) && $newFbclid !== $existingFbclid) { $shouldInsertNew = true; } elseif (!empty($newChannel) && !empty($existingChannel) && $newChannel !== $existingChannel) { $newParamsFbclid = ''; $existingParamsFbclid = ''; $newParams = json_decode($data['params'] ?? '', true); $existingParams = json_decode($existing->Params ?? '', true); if (is_array($newParams)) $newParamsFbclid = $newParams['fbclid'] ?? ''; if (is_array($existingParams)) $existingParamsFbclid = $existingParams['fbclid'] ?? ''; if (!empty($newParamsFbclid) && !empty($existingParamsFbclid) && $newParamsFbclid !== $existingParamsFbclid) { $shouldInsertNew = true; } } } if ($shouldInsertNew) { DB::table($table)->insert($arr); } else { if (!empty($existingUserID) && $existingUserID != 0) { $arr['UserID'] = $existingUserID; } DB::table($table)->where('ID', $existing->ID)->update($arr); } } } /** * 注册后将 UserID 回填到匹配的 AccountCookie 记录。 * * 两阶段匹配: * 1. 同渠道(sameChannel):UrlSign == Channel,匹配即绑。 * 2. 跨渠道纠偏(crossChannel):FBIOS → Safari/PWA 跳转导致渠道不一致时的兜底。 * 额外约束:唯一命中(或 fbclid 精准匹配) + 时间窗口内。 * 成功后把用户 Channel 纠正为 Cookie 的 UrlSign(同步 GlobalUserInfo + AccountsInfo)。 * * 匹配器优先级(高→低): * SPE_KEY > FPID+FF+IP > FPID+FF > FPID+IP > FF+IP > FPID > FF > IP(仅跨渠道,窗口5min) * * 多条候选时用 fbclid 精准匹配,匹配不到则取最新。 * * @param object &$user GlobalUserInfo(引用,跨渠道命中会修改 Channel) * @param string $SPE_KEY quickSave 生成的特征哈希 * @param string $fbclid 从 _fbc cookie 提取的 Facebook 点击 ID * @param string $cookie 客户端上报的完整 cookie 串(与 API 域名不同域时需 body 传参) * @return object|false 更新后的 AccountCookie 记录,或 false */ public static function bindCookie(&$user, $SPE_KEY='', $fbclid='', $cookie='') { // 注册成功后回填 AccountCookie 的 UserID。 // 这里的目标不是“尽量绑上”,而是“尽量不绑错”。 $UserID=$user->UserID; $FPID=$user->FPID; $FF=$user->FF; $IP=$user->RegisterIP; $Channel = $user->Channel ?? ''; $registerTime = !empty($user->RegisterDate) ? strtotime($user->RegisterDate) : time(); if (empty($UserID)) { return false; } $logContext = [ 'user_id' => $UserID, 'channel' => $Channel, 'fpid' => $FPID, 'ff' => $FF, 'ip' => $IP, 'spe_key' => $SPE_KEY, 'fbclid' => $fbclid, 'cookie_has_fbc' => is_string($cookie) && strpos($cookie, '_fbc') !== false, 'register_date' => $user->RegisterDate ?? '', ]; $table = TableName::QPAccountsDB() . 'AccountCookie'; $updateArr = ['UserID' => $UserID]; if (!empty($FPID)) { $updateArr['FPID'] = $FPID; } if (!empty($FF)) { $updateArr['FF'] = $FF; } if (!empty($SPE_KEY)) { $updateArr['SPE_KEY'] = $SPE_KEY; } $baseQuery = function () use ($table) { // 只允许回填原始未绑定用户的数据,避免覆盖已经有 UserID 的老记录。 return DB::table($table) ->where('UserID', 0); }; $sameChannelMatchers = []; $crossChannelMatchers = []; // 匹配强度从高到低排列:越靠前越可信。 // 先跑“同渠道”命中;只有完全找不到时,才尝试“跨渠道纠偏”。 if (!empty($SPE_KEY)) { $sameChannelMatchers[] = ['label' => 'same_channel:SPE_KEY', 'query' => $baseQuery()->where('SPE_KEY', $SPE_KEY)]; $crossChannelMatchers[] = ['label' => 'cross_channel:SPE_KEY', 'query' => $baseQuery()->where('SPE_KEY', $SPE_KEY)]; } if (!empty($FPID) && !empty($FF) && !empty($IP)) { $sameChannelMatchers[] = ['label' => 'same_channel:FPID+FF+IP', 'query' => $baseQuery()->where('FPID', $FPID)->where('FF', $FF)->where('IP', $IP)]; $crossChannelMatchers[] = ['label' => 'cross_channel:FPID+FF+IP', 'query' => $baseQuery()->where('FPID', $FPID)->where('FF', $FF)->where('IP', $IP)]; } if (!empty($FPID) && !empty($FF)) { $sameChannelMatchers[] = ['label' => 'same_channel:FPID+FF', 'query' => $baseQuery()->where('FPID', $FPID)->where('FF', $FF)]; $crossChannelMatchers[] = ['label' => 'cross_channel:FPID+FF', 'query' => $baseQuery()->where('FPID', $FPID)->where('FF', $FF)]; } if (!empty($FPID) && !empty($IP)) { $sameChannelMatchers[] = ['label' => 'same_channel:FPID+IP', 'query' => $baseQuery()->where('FPID', $FPID)->where('IP', $IP)]; $crossChannelMatchers[] = ['label' => 'cross_channel:FPID+IP', 'query' => $baseQuery()->where('FPID', $FPID)->where('IP', $IP)]; } if (!empty($FF) && !empty($IP)) { $sameChannelMatchers[] = ['label' => 'same_channel:FF+IP', 'query' => $baseQuery()->where('FF', $FF)->where('IP', $IP)]; $crossChannelMatchers[] = ['label' => 'cross_channel:FF+IP', 'query' => $baseQuery()->where('FF', $FF)->where('IP', $IP)]; } if (!empty($FPID)) { $sameChannelMatchers[] = ['label' => 'same_channel:FPID', 'query' => $baseQuery()->where('FPID', $FPID)]; $crossChannelMatchers[] = ['label' => 'cross_channel:FPID', 'query' => $baseQuery()->where('FPID', $FPID)]; } if (!empty($FF)) { $sameChannelMatchers[] = ['label' => 'same_channel:FF', 'query' => $baseQuery()->where('FF', $FF)]; $crossChannelMatchers[] = ['label' => 'cross_channel:FF', 'query' => $baseQuery()->where('FF', $FF)]; } if (!empty($IP)) { $crossChannelMatchers[] = [ 'label' => 'cross_channel:IP_only', 'query' => $baseQuery()->where('IP', $IP), 'time_window' => 300, ]; } foreach ($sameChannelMatchers as $matcher) { $query = $matcher['query']; if (!empty($Channel)) { $query->where('UrlSign', $Channel); } $candidates = (clone $query) ->orderBy('CreateTime', 'desc') ->orderBy('ID', 'desc') ->get(); $record = self::pickBestCandidate($candidates, $fbclid); if (!$record) { continue; } Util::WriteLog('bindCookie', $logContext + [ 'stage' => 'matched', 'matcher' => $matcher['label'], 'before' => (array)$record, ]); $rowUpdate = self::applyFbcCookieBackfillToUpdate($updateArr, $record, $cookie); DB::table($table)->where('ID', $record->ID)->update($rowUpdate); $updatedRecord = DB::table($table)->where('ID', $record->ID)->first(); Util::WriteLog('bindCookie', $logContext + [ 'stage' => 'updated', 'matcher' => $matcher['label'], 'after' => (array)$updatedRecord, ]); return $updatedRecord; } foreach ($crossChannelMatchers as $matcher) { $query = $matcher['query']; if (!empty($Channel)) { $query->where('UrlSign', '<>', $Channel); } $records = (clone $query) ->orderBy('CreateTime', 'desc') ->orderBy('ID', 'desc') ->get(); if ($records->isEmpty()) { continue; } if ($records->count() > 1 && empty($fbclid)) { continue; } $record = self::pickBestCandidate($records, $fbclid); $cookieTime = strtotime($record->CreateTime); $maxWindow = $matcher['time_window'] ?? 600; if ($cookieTime === false || abs($cookieTime - $registerTime) > $maxWindow) { Util::WriteLog('bindCookie', $logContext + [ 'stage' => 'skipped', 'matcher' => $matcher['label'], 'reason' => 'time_window_exceeded', 'candidate' => (array)$record, ]); // 纠偏只接受注册前后 10 分钟内的 Cookie,防止把历史 Cookie 误绑到新用户。 continue; } Util::WriteLog('bindCookie', $logContext + [ 'stage' => 'matched', 'matcher' => $matcher['label'], 'before' => (array)$record, 'channel_corrected_to' => $record->UrlSign, ]); $rowUpdate = self::applyFbcCookieBackfillToUpdate($updateArr, $record, $cookie); DB::table($table)->where('ID', $record->ID)->update($rowUpdate); if (!empty($record->UrlSign) && isset($user->Channel) && $user->Channel != $record->UrlSign) { $oldChannel = $user->Channel; $user->Channel = $record->UrlSign; try { $user->save(); } catch (\Exception $exception) { Log::error($exception->getMessage()); } DB::table(TableName::QPAccountsDB() . 'AccountsInfo') ->where('UserID', $UserID) ->update(['Channel' => $record->UrlSign]); Util::WriteLog('bindCookie', $logContext + [ 'stage' => 'channel_corrected', 'old_channel' => $oldChannel, 'new_channel' => $record->UrlSign, ]); } $updatedRecord = DB::table($table)->where('ID', $record->ID)->first(); Util::WriteLog('bindCookie', $logContext + [ 'stage' => 'updated', 'matcher' => $matcher['label'], 'after' => (array)$updatedRecord, 'channel_corrected_to' => $user->Channel ?? '', ]); return $updatedRecord; } Util::WriteLog('bindCookie', $logContext + ['stage' => 'no_match']); return false; } /** * 加载用户的 AccountCookie 记录,用于 S2S 事件上报(CAPI / Kwai / Google)。 * * 查找优先级:UserID 精确查 → FPID/FF 指纹查。 * 多条候选时用 fbclid 精准匹配,匹配不到则取最新。 * 通过 FPID/FF 找到记录后,仅补充空缺字段(FPID/FF/UserID), * 绝不覆盖已有的 UserID,防止串号。 * * @param string $UserID 用户 ID * @param string $FPID 浏览器指纹 ID * @param string $FF 字体指纹哈希 * @param string $cookie _fbc cookie 值或完整 cookie 字符串,用于 fbclid 匹配 * @return array|null AccountCookie 记录数组,或 null */ public static function loadCookie($UserID='', $FPID = '', $FF = '', $cookie = '') { $table = TableName::QPAccountsDB() . 'AccountCookie'; $ac = null; $fbclid = self::extractFbclid($cookie); if (!empty($UserID)) { $candidates = DB::table($table) ->where('UserID', $UserID) ->orderBy('CreateTime', 'desc') ->get(); $ac = self::pickBestCandidate($candidates, $fbclid); } if (!$ac && (!empty($FPID) || !empty($FF))) { $query = DB::table($table); if (!empty($FPID) && !empty($FF)) { $query->where(function ($q) use ($FPID, $FF) { $q->where('FPID', $FPID)->orWhere('FF', $FF); }); } elseif (!empty($FPID)) { $query->where('FPID', $FPID); } else { $query->where('FF', $FF); } $candidates = $query->orderBy('CreateTime', 'desc')->get(); $ac = self::pickBestCandidate($candidates, $fbclid); if ($ac) { $updateArr = []; if (!empty($FPID) && empty($ac->FPID)) $updateArr['FPID'] = $FPID; if (!empty($FF) && empty($ac->FF)) $updateArr['FF'] = $FF; if (!empty($UserID) && (empty($ac->UserID) || $ac->UserID == 0)) { $updateArr['UserID'] = $UserID; } if (!empty($updateArr)) { DB::table($table)->where('ID', $ac->ID)->update($updateArr); } } } if ($ac) { return (array)$ac; } return null; } /** * bindCookie 成功更新行时:若请求里带有效 _fbc,而库中 Cookie 字段尚无 _fbc,则补写 Cookie。 * * @param array $updateArr 原 update 数组(UserID/FPID/FF/SPE_KEY) * @param object $record 当前命中的 AccountCookie 行 * @param string $incoming 客户端上报的 cookie 串 * @return array 合并后的 update 数组 */ protected static function applyFbcCookieBackfillToUpdate(array $updateArr, $record, $incomingCookie) { $merged = self::mergeIncomingFbcIntoAccountCookie($record->Cookie ?? '', $incomingCookie); if ($merged !== null) { $updateArr['Cookie'] = $merged; } return $updateArr; } /** * 请求侧有有效 _fbc,且库中 Cookie 不含 _fbc 时,生成应写入的 Cookie 全文。 * - 库为空:采用客户端完整串(含 _fbp 等) * - 库有其它键无 _fbc:前置 _fbc=...; 保留原串 * * @return string|null 需更新时返回新串;否则 null */ public static function mergeIncomingFbcIntoAccountCookie($storedCookie, $incomingCookie) { $incomingCookie = is_string($incomingCookie) ? trim($incomingCookie) : ''; if ($incomingCookie === '') { return null; } if (self::extractFbclid($incomingCookie) === '') { return null; } $storedCookie = trim((string)$storedCookie); if ($storedCookie !== '' && preg_match('/_fbc\s*=/i', $storedCookie)) { return null; } if (preg_match('/_fbc\s*=\s*([^;]+)/', $incomingCookie, $m)) { $fbcPair = '_fbc=' . trim($m[1]); } elseif (preg_match('/\bfb\.\d+\.\d+\.[^;\s]+/', $incomingCookie, $m)) { $fbcPair = '_fbc=' . $m[0]; } else { return null; } if ($storedCookie === '') { return $incomingCookie; } return $fbcPair . '; ' . $storedCookie; } /** * 从 _fbc cookie 中提取 fbclid。 * * _fbc 格式:fb.{version}.{timestamp}.{fbclid} * 例:fb.1.1774133029549.IwZXh0bgNhZW0BMABh... * * 支持三种输入: * - 完整 cookie 字符串(_fbc=fb.1.xxx;_fbp=...)→ 提取 _fbc 值再取第4段 * - 纯 _fbc 值(fb.1.xxx.yyy)→ 直接取第4段 * - 纯 fbclid 字符串 → 原样返回 * * @param string $cookieStr cookie 字符串 * @return string fbclid,或空串 */ public static function extractFbclid($cookieStr) { if (empty($cookieStr)) return ''; $fbc = ''; if (preg_match('/_fbc=([^;]+)/', $cookieStr, $m)) { $fbc = $m[1]; } elseif (str_starts_with($cookieStr, 'fb.')) { $fbc = $cookieStr; } if (empty($fbc)) return ''; $parts = explode('.', $fbc, 4); if (count($parts) < 4 || empty($parts[3])) return ''; return $parts[3]; } /** * 从多条候选记录中,用 fbclid 匹配最精准的一条,匹配不到则取最新。 * $candidates 必须已按 CreateTime desc 排序。 */ public static function pickBestCandidate($candidates, $fbclid = '') { if ($candidates->isEmpty()) return null; if ($candidates->count() === 1) return $candidates->first(); if (!empty($fbclid)) { $match = $candidates->first(function ($row) use ($fbclid) { return !empty($row->Cookie) && str_contains($row->Cookie, $fbclid); }); if ($match) return $match; } return $candidates->first(); } /** 将一条广告点击数据压入该渠道的最近记录队列(最多保留 50 条)。 */ public static function addRecentsNew($data, $url_sign) { $recents = self::getRecentsNew($url_sign); array_push($recents, $data); if (count($recents) > 50) { array_shift($recents); } Redis::set("recent_apks_$url_sign", json_encode($recents)); } public static function setRecentsNew($recents, $url_sign) { Redis::set("recent_apks_$url_sign", json_encode($recents)); } public static function getRecentsNew($url_sign) { $recents = []; if (Redis::exists("recent_apks_$url_sign")) { $recents = json_decode(Redis::get("recent_apks_$url_sign"), true); } if (!isset($recents) || empty($recents)) $recents = []; return $recents; } public const KWAI_EVENT = [ 'EVENT_DOWNLOAD' => 'EVENT_DOWNLOAD',//download 'EVENT_COMPLETE_REGISTRATION' => 'EVENT_COMPLETE_REGISTRATION',//reg 'EVENT_PURCHASE' => 'EVENT_PURCHASE',//purchase 'EVENT_FIRST_DEPOSIT' => 'EVENT_FIRST_DEPOSIT',//d0 'EVENT_ADD_TO_CART' => 'EVENT_ADD_TO_CART',// 'EVENT_CONTENT_VIEW' => 'EVENT_CONTENT_VIEW',// ]; /** 快手 S2S 事件上报:根据 UserID 加载 Cookie,如果是快手渠道则发送事件。 */ public static function kwaiEvent($userid, $event, $event_values = []) { // $cookies=['Cookie' => $data['cookie'], // 'UrlSign' => $data['url_sign'], // 'Platform' => $data['type']] $user = self::loadCookie($userid); /* * { CampaignID: '987654321', adSETID: '123456789', CreativeID: '123456789', click_id: 'CCpgibAfpRkSWv9zxgDuFHX71q5u_vMxqmvRClE6enEynSNeQAaSvb3CCcXoqXaPwbYpThJBW5n6-62kmmECfY5He3KFgOJB9YErIJ9cIUk=', pixel_id: '12345678987654321' } */ if (isset($user) && $user['Platform'] == 'kw') { $params = json_decode($user['Cookie'], true); self::sendToKwai($params, $event, $event_values); } } /** 向快手广告 API 发送转化事件(生产模式,trackFlag=false)。 */ public static function sendToKwai($params, $event, $event_values = []) { if ((isset($params['pixel_id']) && $params['pixel_id'] == '545692252418097195') || (isset($params['kwai_api_id']) && $params['kwai_api_id'] == '545692252418097195')) { $access_token = '1Wvai0OBGGQlO9eVZIm+wnv1tq5hL9IudwMRdntfms4='; } if ((isset($params['pixel_id']) && $params['pixel_id'] == '545692410115539004') || (isset($params['kwai_api_id']) && $params['kwai_api_id'] == '545692410115539004')) { $access_token = 'cwYDbfB+9lWmVaSBWNxhFUVtL764Or8AzxGVclhG44g='; } try { $url = 'http://www.adsnebula.com/log/common/api'; $data = [ "access_token" => $access_token, "clickid" => $params['click_id'], "event_name" => $event, "is_attributed" => 1, "mmpcode" => "PL", "pixelId" => $params['pixel_id'] ?? $params['kwai_api_id'], "pixelSdkVersion" => "9.9.9", "properties" => json_encode($event_values), "testFlag" => false, "trackFlag" => false ]; $httpCurl = new HttpCurl(); $result = $httpCurl->curlPost($url, $data, 'json', true); Util::WriteLog("kwai", $result); } catch (\Exception $e) { Util::WriteLog("kwai_error", compact("params", "event", "event_values")); Util::WriteLog("kwai_error", $e->getMessage() . $e->getTraceAsString()); } } /** 向快手广告 API 发送转化事件(测试模式,trackFlag=true)。 */ public static function sendToKwaiTest($params, $event, $event_values = []) { if ((isset($params['pixel_id']) && $params['pixel_id'] == '545692252418097195') || (isset($params['kwai_api_id']) && $params['kwai_api_id'] == '545692252418097195')) { $access_token = '1Wvai0OBGGQlO9eVZIm+wnv1tq5hL9IudwMRdntfms4='; } if ((isset($params['pixel_id']) && $params['pixel_id'] == '545692410115539004') || (isset($params['kwai_api_id']) && $params['kwai_api_id'] == '545692410115539004')) { $access_token = 'cwYDbfB+9lWmVaSBWNxhFUVtL764Or8AzxGVclhG44g='; } try { $url = 'http://www.adsnebula.com/log/common/api'; $data = [ "access_token" => $access_token, "clickid" => $params['click_id'], "event_name" => $event, "is_attributed" => 1, "mmpcode" => "PL", "pixelId" => $params['pixel_id'] ?? $params['kwai_api_id'], "pixelSdkVersion" => "9.9.9", "properties" => json_encode($event_values), "testFlag" => false, "trackFlag" => true ]; $httpCurl = new HttpCurl(); $result = $httpCurl->curlPost($url, $data, 'json', true); Util::WriteLog("kwai", $data); Util::WriteLog("kwai", $result); } catch (\Exception $e) { Util::WriteLog("kwai_error", compact("params", "event", "event_values")); Util::WriteLog("kwai_error", $e->getMessage() . $e->getTraceAsString()); } } /** 对老用户设置游戏控分参数(首次设置后不再重复写入)。 */ public static function ControlOldUser($UserID, $Params = '') { $openGames = config('games.openKGame'); $query = DB::table('QPTreasureDB.dbo.UserScoreControl')->where('UserID', $UserID)->first(); if (!$query) { $build_sql = DB::connection('write')->table('QPTreasureDB.dbo.UserScoreControl'); Util::WriteLog('control_old_user', compact("UserID", "Params")); $data = [ 'ControlScore' => (int)(-40 * NumConfig::NUM_VALUE), 'EffectiveScore' => 0, 'ControlKindID' => -1, 'Remarks' => '', 'InsertDate' => date('Y-m-d H:i:s'), 'ControlRadian' => 0 ]; $build_sql->updateOrInsert(['UserID' => $UserID], $data); foreach ($openGames as $GameID) { $KindData = [ 'UserID' => $UserID, 'KindID' => $GameID, 'ControlRadian' => 50, 'ControlDate' => date('Y-m-d H:i:s') ]; DB::connection('write')->table('QPTreasureDB.dbo.UserControlKind')->updateOrInsert(['UserID' => $UserID, 'KindID' => $GameID], $KindData); } } } }