selectRaw("count(1) as order_count, count(distinct(external_userid)) as pay_uv, " ." count(external_userid) as pay_pv, sum(pay_money) as pay_money, sum(origin_pay_money) as origin_pay_money") ->first(); $statData->pay_money = round($statData->pay_money / 10000, 4); $statData->order_count = intval($statData->order_count); $statData->pay_uv = intval($statData->pay_uv); $statData->pay_pv = intval($statData->pay_pv); $statData->origin_pay_money = round($statData->origin_pay_money / 10000, 2); return ['stat' => $statData, 'trend' => []]; } /** * 订单列表 * */ public static function getOrderList($params, $corpid, $page, $pageSize) { list($list, $count) = DjOrder::getOrderList($params, $page, $pageSize); # 获取公众号信息 $appIdList = $list->pluck('bind_app_id'); $appList = OfficialAccount::select(['mp_name', 'mp_app_id'])->whereIn('mp_app_id', $appIdList)->get(); # 获取客户信息 $userIds = $list->pluck('external_userid'); $userList = Customer::suffix($corpid)->select(['external_userid', 'avatar', 'name', 'id']) ->where('corpid', $corpid) ->whereIn('external_userid', $userIds) ->get(); # 回传枚举数据 $adReportActionType = DjOrder::AD_REPORT_ACTION_TYPE; $adReportOrderStatus = DjOrder::AD_REPORT_ORDER_STATUS; $adReportPurchaseStatus = DjOrder::AD_REPORT_PURCHASE_STATUS; $adReportOrderRules = DjOrder::AD_REPORT_ORDER_RULES; $channelArr = config('jiashu.channel'); # 订单来源 $orderSource = config('platform.base'); // $orderSource = DjOrder::ORDER_SOURCE; foreach ($list as $datum) { # 公众号信息补充 $wxAccountInfo = $appList->where('mp_app_id', $datum->bind_app_id)->first(); $wxAccountName = isset($wxAccountInfo->mp_name) ? $wxAccountInfo->mp_name : null; $wxAccountName = !empty($wxAccountName) ? $wxAccountName : (isset($channelArr[$datum->channel]) ? $channelArr[$datum->channel] : '-'); $datum->wx_account = $wxAccountName; # 用户信息补充 $userInfo = $userList->where('external_userid', $datum->external_userid)->first(); $datum->name = isset($userInfo->name) ? $userInfo->name : ''; $datum->customer_id = isset($userInfo->id) ? $userInfo->id : ''; # 订单金额处理 $datum->pay_money = round($datum->pay_money / 10000, 4); # 实际订单金额处理 $datum->origin_pay_money = round($datum->origin_pay_money / 10000, 2); # 订单回传状态(下单以及付费回传任何一个成功即可算作成功) $datum->ad_report_status = ($datum->ad_report_order_status == 1) ? 1 : (($datum->ad_report_purchase_status == 1) ? 1 : 0); # 回传类型 $datum->ad_report_action_type = isset($adReportActionType[$datum->ad_report_action_type]) ? $adReportActionType[$datum->ad_report_action_type] : $datum->ad_report_action_type; # 订单回传结果 $datum->ad_report_order_status = isset($adReportOrderStatus[$datum->ad_report_order_status]) ? $adReportOrderStatus[$datum->ad_report_order_status] : $datum->ad_report_order_status; # 订单回传规则 $datum->ad_report_order_rules = isset($adReportOrderRules[$datum->ad_report_order_rules]) ? $adReportOrderRules[$datum->ad_report_order_rules] : $datum->ad_report_order_rules; # 全量回传结果 $datum->ad_report_purchase_status = isset($adReportPurchaseStatus[$datum->ad_report_purchase_status]) ? $adReportPurchaseStatus[$datum->ad_report_purchase_status] : $datum->ad_report_purchase_status; # 订单来源 $datum->order_source = isset($orderSource[$datum->order_source]) ? $orderSource[$datum->order_source] : $datum->order_source; # 用户关注时间 $datum->mp_user_register_time = !empty($datum->mp_user_register_time) ? date('Y-m-d H:i:s', substr($datum->mp_user_register_time, 0, 10)) : null; # 下单时间 $datum->order_pay_time = date('Y-m-d H:i:s', substr($datum->order_pay_time, 0, 10)); # 投放账号 if(1 == $datum->order_type) { $datum->launch_account = $datum->wx_account; } else { $datum->launch_account = $datum->adq_account_id; } # 支付类型 $datum->pay_type = $datum->pay_type == 1 ? '小程序虚拟支付' : '其他类型支付'; } return [$list, $count]; } public static function actionUpload($orderId, $orderDataId, &$errcode) { try{ # 判断订单是否已经上报 $orderInfo = DjOrder::query()->where('order_id', $orderId)->where(function($query) use ($orderDataId) { if($orderDataId) $query->where('id', $orderDataId); })->first(); if(empty($orderInfo)) { $errcode = 3801;// 订单ID不合法 return false; } $reportStatus = ($orderInfo->ad_report_order_status == 1) ? 1 : (($orderInfo->ad_report_purchase_status == 1) ? 1 : 0); if(1 == $reportStatus) { $errcode = 3802;// 已经上报过了 return false; } # 更新订单投放账号对应的数据源id(防止由于数据源id错误导致回传失败) $adqAccountId = $orderInfo->adq_account_id; if(!empty($adqAccountId)) { $userActionSetId = OfficialWebUserActionSetId::query()->where('account_id', $adqAccountId) ->where('enable', 1)->value('web_user_action_set_id'); if(!empty($userActionSetId) && $userActionSetId != $orderInfo->bind_user_action_set_id){ DjOrder::query()->where('order_id', $orderId)->update(['bind_user_action_set_id' => $userActionSetId]); $orderInfo->bind_user_action_set_id = $userActionSetId; } } # 信息放入带处理队列中 $orderArr = array_merge($orderInfo->toArray(), ['ignore_black' => 1]); TencentAdAuth::intoActionAddList($orderArr); $errcode = 0; return true; } catch (\Exception $exception) { Log::logError('手动订单回传程序异常', [ 'order_id' => $orderId, 'message' => $exception->getFile().'('.$exception->getLine().'):'.$exception->getMessage(), 'trace' => $exception->getTraceAsString() ], 'interface'); EmailQueue::rPush('手动订单回传程序异常', json_encode([ 'order_id' => $orderId, 'message' => $exception->getFile().'('.$exception->getLine().'):'.$exception->getMessage() ], 256), ['song.shen@kuxuan-inc.com'], '猎羽'); return false; } } public static function orderExport($params, &$errcode) { ini_set('memory_limit', '2048M'); set_time_limit(0); # 防止重复点击 $key = 'Playlet::orderExport-'.json_encode($params, 1); $val = RedisModel::get($key); if($val) { $errcode = 2007; return ; } else { RedisModel::set($key, 1); RedisModel::expire($key, 10); } try{ $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $row_index = 1; $column_index = 1; /**设置标题**/ $title = ['订单ID', '企微主体', '公众号', '用户ID', '小程序注册时间', '企微用户关注时间', '是否首单', '下单时间', '下单金额' , '分成后订单金额', '支付类型', '订单来源', '剧名', '登录账号', '推广链接', '推广类型']; foreach ($title as $name){ $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$name); } // 只展示已支付订单 $params['pay_status'] = 1; list($list, $count) = DjOrder::getOrderList($params, 1, 100000); # 获取公众号信息 $appList = OfficialAccount::query()->select(['mp_name', 'mp_app_id'])->get(); # 获取企微信息 $corpList = AuthorizeCorp::query()->select(['corpid', 'corp_name'])->get(); # 订单来源 $orderSource = config('platform.base'); $channelArr = config('jiashu.channel'); foreach ($list as $datum) { $row_index++; $column_index=1; //重置列索引 # 公众号信息补充 $wxAccountInfo = $appList->where('mp_app_id', $datum->bind_app_id)->first(); $wxAccountName = isset($wxAccountInfo->mp_name) ? $wxAccountInfo->mp_name : null; $wxAccountName = !empty($wxAccountName) ? $wxAccountName : (isset($channelArr[$datum->channel]) ? $channelArr[$datum->channel] : '-'); $datum->wx_account = $wxAccountName; # 企微信息 $corpInfo = $corpList->where('corpid', $datum->system_corpid)->first(); $datum->corp_name = isset($corpInfo->corp_name) ? $corpInfo->corp_name : '-'; # 订单金额处理 $datum->pay_money = round($datum->pay_money / 10000, 4); # 实际订单金额处理 $datum->origin_pay_money = round($datum->origin_pay_money / 10000, 2); # 订单来源 $datum->order_source = isset($orderSource[$datum->order_source]) ? $orderSource[$datum->order_source] : $datum->order_source; # 用户关注时间 $maUserRegisterTime = !empty($datum->ma_user_register_time) ? date('Y-m-d H:i:s', substr($datum->ma_user_register_time, 0, 10)) : ''; $datum->ma_user_register_time = $maUserRegisterTime; # 下单时间 $datum->order_pay_time = date('Y-m-d H:i:s', substr($datum->order_pay_time, 0, 10)); # 是否首单 $datum->first_order = $datum->first_order_status == 1 ? '是' : ($datum->first_order_status == 2 ? '否' : '未知'); # 投放来源 $datum->plat_order_type = $datum->plat_order_type == 1 ? 'h5' : '小程序'; # 支付类型 $datum->pay_type = $datum->pay_type == 1 ? '小程序虚拟支付' : '其他类型支付'; $sheet->setCellValueByColumnAndRow($column_index++,$row_index,"'".$datum->order_id); $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$datum->corp_name); $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$datum->wx_account); $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$datum->user_id); $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$datum->ma_user_register_time); $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$datum->first_order); $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$datum->order_pay_time); $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$datum->origin_pay_money); $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$datum->pay_money); $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$datum->pay_type); $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$datum->order_source); $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$datum->playlet_name); $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$datum->login_account); $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$datum->promotion_title); $sheet->setCellValueByColumnAndRow($column_index++,$row_index,$datum->plat_order_type); } $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); header('Content-Type:application/vnd.ms-excel'); header('Content-Disposition:attachment;filename=订单导出.xlsx'); header('Cache-Control:max-age=0'); $writer->save('php://output'); } catch (\Exception $exception) { Log::logError('订单导出异常', [ 'params' => $params, 'file' => $exception->getFile(), 'line' => $exception->getLine(), 'message' => $exception->getMessage(), 'trace' => $exception->getTraceAsString(), ], 'OrderExportException'); EmailQueue::rPush('订单导出异常', json_encode([ 'params' => $params, 'file' => $exception->getFile(), 'line' => $exception->getLine(), 'message' => $exception->getMessage(), 'trace' => $exception->getTraceAsString(), ], 256), ['song.shen@kuxuan-inc.com'], '猎羽'); $errcode = 500; return ; } } public static function chargeFirst($orderInfo) { $flag = '否'; $order = DjOrder::query()->select('order_id')->where('order_source', 2) ->where('channel', $orderInfo['channel'])->where('user_id', strval($orderInfo['user_id'])) ->where('pay_status', 1) ->orderBy('order_pay_time', 'asc')->first(); if(isset($order->order_id) && $order->order_id == $orderInfo['order_id']) { $flag = '是'; } return $flag; } public static function orderList($params, $page, $pageSize) { // 只展示已支付订单 $params['pay_status'] = 1; list($list, $count) = DjOrder::getOrderList($params, $page, $pageSize); # 获取公众号信息 $appIdList = $list->pluck('bind_app_id'); $appIdList = array_unique($appIdList->toArray()); $appList = OfficialAccount::query()->select(['mp_name', 'mp_app_id'])->whereIn('mp_app_id', $appIdList)->get(); # 获取企微信息 $corpidList = $list->pluck('system_corpid'); $corpidList = array_unique($corpidList->toArray()); $corpList = AuthorizeCorp::query()->select(['corpid', 'corp_name'])->whereIn('corpid', $corpidList)->get(); # 获取企微客服信息 $corpUserList = DjUser::query()->select(['corpid', 'user_id', 'name'])->whereIn('corpid', $corpidList)->get(); # 订单来源 $orderSource = config('platform.base'); $channelArr = config('jiashu.channel'); foreach ($list as $datum) { # 公众号信息补充 $wxAccountInfo = $appList->where('mp_app_id', $datum->bind_app_id)->first(); $wxAccountName = isset($wxAccountInfo->mp_name) ? $wxAccountInfo->mp_name : null; $wxAccountName = !empty($wxAccountName) ? $wxAccountName : (isset($channelArr[$datum->channel]) ? $channelArr[$datum->channel] : '-'); $datum->wx_account = $wxAccountName; # 企微信息 $corpInfo = $corpList->where('corpid', $datum->system_corpid)->first(); $datum->corp_name = isset($corpInfo->corp_name) ? $corpInfo->corp_name : '-'; $userInfo = $corpUserList->where('corpid', $datum->system_corpid)->where('user_id', $datum->corp_user_id)->first(); $datum->user_name = isset($userInfo->name) ? $userInfo->name : '-'; # 订单金额处理 $datum->pay_money = round($datum->pay_money / 10000, 4); # 实际订单金额处理 $datum->origin_pay_money = round($datum->origin_pay_money / 10000, 2); # 订单来源 $datum->order_source = isset($orderSource[$datum->order_source]) ? $orderSource[$datum->order_source] : $datum->order_source; # 小程序关注时间 $maUserRegisterTime = !empty($datum->ma_user_register_time) ? date('Y-m-d H:i:s', substr($datum->ma_user_register_time, 0, 10)) : ''; $datum->ma_user_register_time = $maUserRegisterTime; # 企微用户关注时间 $mpUserRegisterTime = !empty($datum->mp_user_register_time) ? date('Y-m-d H:i:s', substr($datum->mp_user_register_time, 0, 10)) : ''; $datum->mp_user_register_time = $mpUserRegisterTime; # 下单时间 $datum->order_pay_time = date('Y-m-d H:i:s', substr($datum->order_pay_time, 0, 10)); # 是否首单 $datum->first_order_status = $datum->first_order_status == 1 ? '是' : ($datum->first_order_status == 2 ? '否' : '未知'); # 投放来源 $datum->plat_order_type = $datum->plat_order_type == 1 ? 'h5' : '小程序'; # 支付类型 $datum->pay_type = $datum->pay_type == 1 ? '小程序虚拟支付' : '其他类型支付'; } return [$list, $count]; } public static function orderExportOffline($params, &$errcode) { if(strtotime($params['end_date']) - strtotime($params['start_date']) > 31*86400) { $errcode = 3803; return ; } # 防止重复点击 $key = 'Playlet::orderExportOffline-'.json_encode($params, 1); $val = RedisModel::get($key); if($val) { $errcode = 2007; return ; } else { RedisModel::set($key, 1); RedisModel::expire($key, 10); } $indexName = '充值订单'; if (isset($params['corpid']) && !empty($params['corpid'])) { $corpName = AuthorizeCorp::query()->where('corpid', $params['corpid'])->value('corp_name'); $indexName .= '-(企微:'.$corpName.')'; } if (isset($params['order_id']) && !empty($params['order_id'])) { $indexName .= '-(订单ID:'.$params['order_id'].')'; } if (isset($params['order_source']) && !empty($params['order_source'])) { $list = config('platform.base'); $orderSource = $list[$params['order_source']] ?? ''; $indexName .= '-(付费来源:'.$orderSource.')'; } if (isset($params['start_date']) && !empty($params['start_date'])) { $indexName .= '-(起始日期:'.$params['start_date'].')'; } if (isset($params['end_date']) && !empty($params['end_date'])) { $indexName .= '-(截止日期:'.$params['end_date'].')'; } if (isset($params['account_id']) && !empty($params['account_id'])) { $indexName .= '-(adq账号:'.$params['account_id'].')'; } if (isset($params['is_ad_user']) && is_numeric($params['is_ad_user'])) { $isAdUser = $params['is_ad_user'] == 1 ? '投放推广' : '运营推广'; $indexName .= '-(收入来源:'.$isAdUser.')'; } if (isset($params['plat_order_type']) && is_numeric($params['plat_order_type'])) { $platOrderType = $params['plat_order_type'] == 1 ? 'H5' : '小程序'; $indexName .= '-(推广类型:'.$platOrderType.')'; } if (isset($params['app_id']) && !empty($params['app_id'])) { $appName = OfficialAccount::query()->where('mp_app_id', $params['app_id'])->value('mp_name'); $indexName .= '-(公众号:'.$appName.')'; } if (isset($params['order_pay_type']) && !empty($params['order_pay_type'])) { if(1 == $params['order_pay_type']) { $indexName .= '(小程序虚拟支付)'; } else { $indexName .= '(其他类型支付)'; } } $data['index_name'] = $indexName; $data['data_type'] = 1; $data['params'] = json_encode($params, JSON_UNESCAPED_UNICODE); $data['operator_id'] = \Auth::id(); $data['status'] = 1; $data['sys_group_id'] = $params['sys_group_id']; $res = ExportTask::query()->insert($data); if(!$res) { $errcode = 500; return; } } public static function orderExportList($sysGroupId, $page, $pageSize) { $offset = ($page -1) * $pageSize; $query = ExportTask::query()->where('sys_group_id', $sysGroupId) ->select(['created_at', 'index_name', 'file_url', 'status', 'operator_id']); $total = $query->count(); $result = $query->orderBy('id', 'desc') ->offset($offset) ->limit($pageSize) ->get(); $userData = Users::query()->get(); foreach ($result as &$item) { $item->file_url = str_replace('http://', 'https://', $item->file_url); $operatorInfo = $userData->where('id', $item->operator_id)->first(); $item->operator_name = $operatorInfo->name ?? null; } return [$result, $total]; } # 统一获取各个平台订单金额分成比例 public static function getOrderShareInProportion($orderSource) { $data = []; switch($orderSource) { case 1: // 柚子 case 2: // 嘉书 $data[1] = 0.88;// 安卓(小程序虚拟支付) $data[2] = 0.88;// ios(其他类型支付) break; case 3: // 迈步 $data[1] = 0.80;// 安卓(小程序虚拟支付) $data[2] = 0.88;// ios(其他类型支付) break; case 4: // 点众阳光 $data[1] = 0.80;// 安卓(小程序虚拟支付) $data[2] = 0.80;// ios(其他类型支付) break; case 5: // 花生 $data[1] = 0.80;// 安卓(小程序虚拟支付) $data[2] = 0.85;// ios(其他类型支付) break; case 6: // 容量 $data[1] = 0.84;// 安卓(小程序虚拟支付) $data[2] = 0.86;// ios(其他类型支付) break; case 7: // 九州 $data[1] = 0.80;// 安卓(小程序虚拟支付) $data[2] = 0.85;// ios(其他类型支付) break; case 8: // 映客 $data[1] = 0.792;// 安卓 $data[2] = 0.8415;// ios break; case 9: // 触摸 $data[1] = 0.80;// 安卓 $data[2] = 0.85;// ios break; case 10:// 番茄 $data[1] = 0.79;// 安卓 $data[2] = 0.90;// ios break; } return $data; } public static function getOrderShareInProportionNew($orderSource, $playletName, $sysGroupId) { $value = RedisModel::hGet(OrderShareInProportionConf::ORDER_SHARE_IN_PROPORTION_CONF, $sysGroupId . '-' . $orderSource); if(!empty($value)) { $conf = json_decode($value, 1); } else { $conf = []; $confInfo = OrderShareInProportionConf::getConfInfo($sysGroupId, $orderSource); if(!empty($confInfo)) { $androidPercent = $confInfo->android_percent; $iosPercent = $confInfo->ios_percent; $conf = [1 => $androidPercent/100, 2 => $iosPercent/100]; RedisModel::hSet(OrderShareInProportionConf::ORDER_SHARE_IN_PROPORTION_CONF, $sysGroupId . '-' . $orderSource, json_encode($conf)); } else { if(3 == $sysGroupId) EmailQueue::rPush('查找订单金额分成比例配置异常', $sysGroupId.'###'.$orderSource, ['song.shen@kuxuan-inc.com'], '猎羽'); } } return $conf; } # 设置第三方平台订单金额分成比例 public static function setShareInProportionConf($sysGroupId, $orderSource, $androidPercent, $iosPercent, &$errcode) { # 判断分成比例是否符合预期 小数形式 if($androidPercent > 100 || $androidPercent <= 0 || $iosPercent > 100 || $iosPercent <= 0) { $errcode = 3804; return false; } # 验证柚子 嘉书 点众 三个平台配置的分成比例是否一致 if(in_array($orderSource, [1, 2, 4]) && $androidPercent != $iosPercent) { $errcode = 3807; return false; } # 更新数据库 $con = [ 'sys_group_id' => $sysGroupId, 'order_source' => $orderSource, ]; $attr = [ 'android_percent' => $androidPercent, 'ios_percent' => $iosPercent, 'enable' => 1, 'update_time' => date('Y-m-d H:i:s') ]; $res = OrderShareInProportionConf::saveConf($con, $attr); if(!$res) { $errcode = 500; return false; } # 更新redis RedisModel::hSet(OrderShareInProportionConf::ORDER_SHARE_IN_PROPORTION_CONF, $sysGroupId . '-' . $orderSource, json_encode([1 => $androidPercent/100, 2 => $iosPercent/100])); // 记录操作日志 $data = [ 'sys_group_id' => $sysGroupId, 'order_source' => $orderSource, 'android_percent' => $androidPercent, 'ios_percent' => $iosPercent ]; OperateLog::saveData(1, json_encode($data), \Auth::id()); return true; } public static function getShareInProportionConf($sysGroupId) { $confList = OrderShareInProportionConf::getConfList($sysGroupId); $sourceList = config('platform.base'); foreach($confList as $confInfo) { $orderSource = $confInfo->order_source; $sourceName = $sourceList[$orderSource] ?? '其他'; $confInfo->source_name = $sourceName; } return $confList; } }