15天cookie算法
15天cookie脚本名称:extSourceStat_7Day_Orders(名称中的7天有历史原因在里面，不用在意);
<?php /* _ooOoo_ o8888888o 88" . "88 (| -_- |) O\ = /O ____/`---'\____ .' \\| |// `. / \\||| : |||// \ / _||||| -:- |||||- \ | | \\\ - /// | | | \_| ''\---/'' | | \ .-\__ `-` ___/-. / ___`. .' /--.--\ `. . __ ."" '< `.___\_<|>_/___.' >'"". | | : `- \`.;`\ _ /`;.`/ - ` : | | \ \ `-. \_ __\ /__ _/ .-` / / ======`-.____`-.___\_____/___.-`____.-'====== `=---=' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 佛祖保佑 永无BUG */ /** Log文件格式2012/7/4 列号 字段含义 取值 ------------------------------------------------------------------------- 1 年月日时间 2 时间戳 3 记录类型 1：页面跳转 2：点击（不带来pv，uv，订单） 4 用户id 未登录：0 5 商品id 当前页是加入购物车或者商详，则取当前商品id,,否则：0 6 分站id 7 Guid 用户唯一表示串，进网站时生成 8 Tag 当前url中跟踪tag,没有则为空 9 Pageid 当前页面id 10 Page_level 当前页面level 11 url 当前页面url，不包含？及参数 12 ReferPageid 页面来源页面id，没有则0 13 Refer 页面来源url，没有则为空 14 Clientip 客户端ip 15 分辨率 客户端分辨率 16 颜色 页面颜色 17 Agent 客户端agent参数 ------------------------------------------------------------------------- **/ error_reporting(E_ALL); require_once('Config.php'); require_once('ToolUtil.php'); require_once('Logger.php'); require_once('./constant.inc.php'); require_once('./bak_db_config.inc.php'); //这里面定义了一些数据库相关的信息 // 一级来源ID define('DIRECT_LEVEL_1_ID', 66666); define('DIRECT_LEVEL_1_NAME', '直接访问'); define('SEARCH_LEVEL_1_ID', 77777); define('SEARCH_LEVEL_1_NAME', '搜索引擎'); define('CHANNEL_LEVEL_1_ID', 88888); define('CHANNEL_LEVEL_1_NAME', '渠道推广'); define('OTHER_LEVEL_1_ID', 99999); define('OTHER_LEVEL_1_NAME', '其他来源'); // TAG 系统页面来源ID // 直接访问 define('SOURCE_ID_DIRECT', 0); // 内部跳转 define('SOURCE_ID_INNER', -1); // 其它来源 define('SOURCE_ID_OTHER', -2); // 用于统计的ID define('STAT_OTHER_LEVEL_2_ID', 44444); // 在系统中未查到的推广TAG，默认设置为此ID define('TAG_NOT_EXIST_SOURCE_ID', -33333); // 渠道数据统计，代表全部的ID define('CHANNEL_ALL_SOURCE_ID', -555); define('CHANNEL_ALL_SOURCE_NAME', '全部'); {//_searchSourceData $_searchSourceData = array( 14 => array( 'level_1_id' => SEARCH_LEVEL_1_ID, 'level_1_name' => SEARCH_LEVEL_1_NAME, 'level_2_id' => 14, 'level_2_name' => '百度', ), 24 => array( 'level_1_id' => SEARCH_LEVEL_1_ID, 'level_1_name' => SEARCH_LEVEL_1_NAME, 'level_2_id' => 24, 'level_2_name' => '谷歌', ), 34 => array( 'level_1_id' => SEARCH_LEVEL_1_ID, 'level_1_name' => SEARCH_LEVEL_1_NAME, 'level_2_id' => 24, 'level_2_name' => '谷歌', ), 44 => array( 'level_1_id' => SEARCH_LEVEL_1_ID, 'level_1_name' => SEARCH_LEVEL_1_NAME, 'level_2_id' => 44, 'level_2_name' => '搜搜', ), 54 => array( 'level_1_id' => SEARCH_LEVEL_1_ID, 'level_1_name' => SEARCH_LEVEL_1_NAME, 'level_2_id' => 54, 'level_2_name' => '搜狗', ), 64 => array( 'level_1_id' => SEARCH_LEVEL_1_ID, 'level_1_name' => SEARCH_LEVEL_1_NAME, 'level_2_id' => 64, 'level_2_name' => '有道', ), 74 => array( 'level_1_id' => SEARCH_LEVEL_1_ID, 'level_1_name' => SEARCH_LEVEL_1_NAME, 'level_2_id' => 74, 'level_2_name' => '必应', ), 84 => array( 'level_1_id' => OTHER_LEVEL_1_ID, 'level_1_name' => OTHER_LEVEL_1_NAME, 'level_2_id' => 84, 'level_2_name' => 'hao.qq.com', ), 94 => array( 'level_1_id' => OTHER_LEVEL_1_ID, 'level_1_name' => OTHER_LEVEL_1_NAME, 'level_2_id' => 94, 'level_2_name' => 'hao123.com', ), 104 => array( 'level_1_id' => OTHER_LEVEL_1_ID, 'level_1_name' => OTHER_LEVEL_1_NAME, 'level_2_id' => 104, 'level_2_name' => '2345.com', ), 114 => array( 'level_1_id' => OTHER_LEVEL_1_ID, 'level_1_name' => OTHER_LEVEL_1_NAME, 'level_2_id' => 114, 'level_2_name' => '114la.com', ), 124 => array( 'level_1_id' => OTHER_LEVEL_1_ID, 'level_1_name' => OTHER_LEVEL_1_NAME, 'level_2_id' => 124, 'level_2_name' => 'tao123.com', ), 134 => array( 'level_1_id' => OTHER_LEVEL_1_ID, 'level_1_name' => OTHER_LEVEL_1_NAME, 'level_2_id' => 134, 'level_2_name' => 'hao.360.cn', ), 144 => array( 'level_1_id' => OTHER_LEVEL_1_ID, 'level_1_name' => OTHER_LEVEL_1_NAME, 'level_2_id' => 144, 'level_2_name' => '123.sogou.com', ), 154 => array( 'level_1_id' => OTHER_LEVEL_1_ID, 'level_1_name' => OTHER_LEVEL_1_NAME, 'level_2_id' => 154, 'level_2_name' => '265.com', ), 164 => array( 'level_1_id' => OTHER_LEVEL_1_ID, 'level_1_name' => OTHER_LEVEL_1_NAME, 'level_2_id' => 164, 'level_2_name' => 'etao.com', ), 174 => array( 'level_1_id' => SEARCH_LEVEL_1_ID, 'level_1_name' => SEARCH_LEVEL_1_NAME, 'level_2_id' => 174, 'level_2_name' => '360', ), ); } $_sourceChannelData = array(); $_statSourceData_Level_1 = array(); $_statSourceData_Level_2 = array(); $_statSourceData_Level_3 = array(); $_tag_Level_1 = array(); $_tag_Level_2 = array(); // 用户订单数据 $_login_order_data = array(); // 生成外投推广来源ID function _getLevel3TagID($level_1_id, $level_2_id, $level_3_id) { $tag_source_id = $level_3_id + $level_2_id * 100000 + $level_1_id * 10000000; $tag_source_id = $tag_source_id * 10 + PAGE_TYPE_EXT_PROMOTION; return "{$tag_source_id}"; } function _getSourceChannelData() { GLOBAL $_sourceChannelData; GLOBAL $_statSourceData_Level_2; GLOBAL $_statSourceData_Level_3; GLOBAL $_tag_Level_1; GLOBAL $_tag_Level_2; $mysql = ToolUtil::getDBObj('icson'); if (false === $mysql) { Logger::err("Get mysql db [icson] failed: " . ToolUtil::$errMsg . "\n"); return false; } $table_name = "t_tag_source"; $sqlstmt_level_0 = "SELECT s_name, tag_id FROM {$table_name} WHERE s_level = 0"; $sqlstmt_level_1 = "SELECT s_name, tag_fid, level_1_id FROM {$table_name} WHERE s_level = 1"; $sqlstmt_level_2 = "SELECT s_name, level_1_id, level_2_id FROM {$table_name} WHERE s_level = 2"; $sqlstmt_level_3 = "SELECT s_name, level_1_id, level_2_id, level_3_id FROM {$table_name} WHERE s_level = 3"; $level_0_data = array(); $level_1_data = array(); $level_2_data = array(); $level_3_data = array(); // get level 0 $rows = $mysql->getRows($sqlstmt_level_0); if (false === $rows) { Logger::err("Query mysql db [icson].[{$table_name}] failed: " . $mysql->errMsg . "\n"); return false; } else { foreach ($rows as &$row) { $level_0_data[$row['tag_id']] = $row; $_statSourceData_Level_2[intval($row['tag_id'])] = array( 'level_2_id' => intval($row['tag_id']), 'level_2_name' => $row['s_name'], 'level_1_id' => CHANNEL_LEVEL_1_ID, 'level_1_name' => CHANNEL_LEVEL_1_NAME, ); } } // get level 1 $rows = $mysql->getRows($sqlstmt_level_1); if (false === $rows) { Logger::err("Query mysql db [icson].[{$table_name}] failed: " . $mysql->errMsg . "\n"); return false; } else { foreach ($rows as &$row) { $level_1_data[$row['level_1_id']] = $row; } } // get level 2 $rows = $mysql->getRows($sqlstmt_level_2); if (false === $rows) { Logger::err("Query mysql db [icson].[{$table_name}] failed: " . $mysql->errMsg . "\n"); return false; } else { foreach ($rows as &$row) { $level_2_data[$row['level_1_id']][$row['level_2_id']] = $row; } } // get level 3 $rows = $mysql->getRows($sqlstmt_level_3); if (false === $rows) { Logger::err("Query mysql db [icson].[{$table_name}] failed: " . $mysql->errMsg . "\n"); return false; } else { foreach ($rows as &$row) { $tag_source_id = _getLevel3TagID($row['level_1_id'], $row['level_2_id'], $row['level_3_id']); $level_3_data[$tag_source_id] = $row; } } // 获取TAG映射数据 foreach ($level_3_data as $tag_source_id => &$tag_data) { $tag_level_1_id = $tag_data['level_1_id']; $tag_level_2_id = $tag_data['level_2_id']; $tag_level_3_id = $tag_data['level_3_id']; $tag_level_1_name = $level_1_data[$tag_level_1_id]['s_name']; $tag_level_2_name = $level_2_data[$tag_level_1_id][$tag_level_2_id]['s_name']; $tag_level_3_name = $tag_data['s_name']; $level_3_id = $tag_level_1_id; $level_2_id = $level_1_data[$tag_level_1_id]['tag_fid']; $level_1_id = CHANNEL_LEVEL_1_ID; $level_3_name = $tag_level_1_name; $level_2_name = $level_0_data[$level_2_id]['s_name']; $level_1_name = CHANNEL_LEVEL_1_NAME; $_sourceChannelData[$tag_source_id] = array( // TAG 系统level 'tag_level_1_id' => $tag_level_1_id, 'tag_level_2_id' => $tag_level_2_id, 'tag_level_3_id' => $tag_level_3_id, 'tag_level_1_name' => $tag_level_1_name, 'tag_level_2_name' => $tag_level_2_name, 'tag_level_3_name' => $tag_level_3_name, // 统计结果level 'level_1_id' => $level_1_id, 'level_2_id' => $level_2_id, 'level_3_id' => $level_3_id, 'level_1_name' => $level_1_name, 'level_2_name' => $level_2_name, 'level_3_name' => $level_3_name, ); // tag level 1 $_tag_Level_1[$tag_level_1_id] = array( 'tag_level_1_id' => $tag_level_1_id, 'tag_level_1_name' => $tag_level_1_name, ); // tag level 2 $_tag_Level_2[$tag_level_1_id][$tag_level_2_id] = array( 'tag_level_1_id' => $tag_level_1_id, 'tag_level_2_id' => $tag_level_2_id, 'tag_level_1_name' => $tag_level_1_name, 'tag_level_2_name' => $tag_level_2_name, ); // stat level 3 data $_statSourceData_Level_3[$level_3_id] = array( 'level_1_id' => $level_1_id, 'level_2_id' => $level_2_id, 'level_3_id' => $level_3_id, 'level_1_name' => $level_1_name, 'level_2_name' => $level_2_name, 'level_3_name' => $level_3_name, ); } // 直接访问 $_sourceChannelData[SOURCE_ID_DIRECT] = array( // 统计结果level 'level_1_id' => DIRECT_LEVEL_1_ID, 'level_1_name' => DIRECT_LEVEL_1_NAME, ); // 其它来源 $_sourceChannelData[SOURCE_ID_OTHER] = array( // 统计结果level 'level_2_id' => STAT_OTHER_LEVEL_2_ID, 'level_2_name' => '其它', 'level_1_id' => OTHER_LEVEL_1_ID, 'level_1_name' => OTHER_LEVEL_1_NAME, ); return true; } function _getStatSourceData_Level_X() { GLOBAL $_statSourceData_Level_1; GLOBAL $_statSourceData_Level_2; GLOBAL $_searchSourceData; // get level 1 $_statSourceData_Level_1[CHANNEL_LEVEL_1_ID] = array( 'level_1_id' => CHANNEL_LEVEL_1_ID, 'level_1_name' => CHANNEL_LEVEL_1_NAME, ); $_statSourceData_Level_1[DIRECT_LEVEL_1_ID] = array( 'level_1_id' => DIRECT_LEVEL_1_ID, 'level_1_name' => DIRECT_LEVEL_1_NAME, ); $_statSourceData_Level_1[SEARCH_LEVEL_1_ID] = array( 'level_1_id' => SEARCH_LEVEL_1_ID, 'level_1_name' => SEARCH_LEVEL_1_NAME, ); $_statSourceData_Level_1[OTHER_LEVEL_1_ID] = array( 'level_1_id' => OTHER_LEVEL_1_ID, 'level_1_name' => OTHER_LEVEL_1_NAME, ); // get level 2 foreach ($_searchSourceData as $source_id => $source_data) { $_statSourceData_Level_2[$source_id] = $source_data; } $_statSourceData_Level_2[STAT_OTHER_LEVEL_2_ID] = array( 'level_2_id' => STAT_OTHER_LEVEL_2_ID, 'level_2_name' => '其它', 'level_1_id' => OTHER_LEVEL_1_ID, 'level_1_name' => OTHER_LEVEL_1_NAME, ); return true; } {//_getOrderUserFirstOrderDate 这个地方通过orderusers的user_id去获取用户的首次下单时间，会发生找不到首次下单的时间的情况 function _getOrderUserFirstOrderDate($order_users) { $user_first_order_date = array(); $user_ids = array(); $mssql = false; foreach ($order_users as $user_id => $dummy_val) { $user_ids[] = $user_id; if (count($user_ids) >= 500) { $sql_stmt = "SELECT user_id, CONVERT(VARCHAR, first_order_date, 112) first_order_date FROM t_aggregation_user_first_order_date "; $sql_stmt .= "WHERE user_id IN (" . implode(",", $user_ids) . ")"; $rows = _getRowsWithRetries('USER_ORDER', $sql_stmt, $mssql); if (false !== $rows) { foreach ($rows as $row) { $user_first_order_date[$row['user_id']] = $row['first_order_date']; } } $user_ids = array(); } } if (count($user_ids) > 0) { $sql_stmt = "SELECT user_id, CONVERT(VARCHAR, first_order_date, 112) first_order_date FROM t_aggregation_user_first_order_date "; $sql_stmt .= "WHERE user_id IN (" . implode(",", $user_ids) . ")"; $rows = _getRowsWithRetries('USER_ORDER', $sql_stmt, $mssql); if (false !== $rows) { foreach ($rows as $row) { $user_first_order_date[$row['user_id']] = $row['first_order_date']; } } $user_ids = array(); } echo "Get user first order date: count: ", count($user_first_order_date), "\n"; echo "order users : count: ", count($order_users), "\n"; return $user_first_order_date; } } {//合并订单数据 function _mergeOrderData(&$order_data, $access_key, $user_id, $user_type, $row) { if (!isset($order_data[$access_key])) { $order_data[$access_key] = array(); } if (!isset($order_data[$access_key][$user_type])) { $order_data[$access_key][$user_type] = array(); } if (!isset($order_data[$access_key][$user_type][$user_id])) { $order_data[$access_key][$user_type][$user_id] = array(); } if (!isset($order_data[$access_key][$user_type][$user_id]['order_ids'])) { $order_data[$access_key][$user_type][$user_id]['order_ids'] = array(); } if (!isset($order_data[$access_key][$user_type][$user_id]['order_ids'][$row['OId']])) { $order_data[$access_key][$user_type][$user_id]['order_ids'][$row['OId']] = array(); $order_data[$access_key][$user_type][$user_id]['order_ids'][$row['OId']]['Qty'] = 0; $order_data[$access_key][$user_type][$user_id]['order_ids'][$row['OId']]['OFee'] = 0.0; } // 当日下单的订单 $order_data[$access_key][$user_type][$user_id]['order_ids'][$row['OId']]['Qty'] += intval($row['Qty']); $order_data[$access_key][$user_type][$user_id]['order_ids'][$row['OId']]['OFee'] += floatval($row['OFee']); return true; } } {//获取订单数据 function _getSourceOrdersData($s_date) { GLOBAL $_login_order_data; $date_start = $s_date; $date_end = date("Y-m-d", strtotime("+1 day", strtotime($date_start))); $date_intstr = date('Ymd', strtotime($s_date)); // 当日下单的订单数据 $order_data = array(); // 出库订单数据 $outstock_data = array(); // 订单ID对应的SourceID $order_sourceid = array(); $sql_stmt = <<<SQL_STMT -- SELECT ISNULL(OTag, '') as OTag, -- SO_Item.SOSysNo as OId, -- CONVERT(VARCHAR, SO_Master.OrderDate, 112) OrderDate, -- Quantity as Qty, -- (Price * Quantity) as OFee, -- SO_Master.CustomerSysNo as UId, -- (CASE WHEN SO_Master.SalesManSysNo = 666666 THEN 1999 ELSE SO_Master.SiteNo END) as wh_id -- from t_deal td -- left join t_trade tr on td.Fdeal_id = tr.fdeal_id -- left join etl_users..t_user_buyer u on u.Fwg_uid=td.Fbuyer_id -- where td.Fdeal_gen_time >= '{$date_start}' AND td.Fdeal_gen_time < '{$date_end}' and td.Fdeal_property3 = 64 SELECT ISNULL(tr.ext_11002, '') as OTag, td.Fbusiness_deal_id as OId, CONVERT(VARCHAR, td.Fdeal_gen_time, 112) OrderDate, tr.Ftrade_buy_num as Qty, (tr.Ftrade_buy_num*tr.Ftrade_buy_price / 100.00) as OFee, ISNULL(u.Ficson_uid,0) as UId, (case when td.Fdeal_source in(2,3,8) then 1969 else td.fsite_id end) as wh_id from t_deal td left join t_trade tr on td.Fdeal_id = tr.fdeal_id left join etl_users..t_user_buyer u on u.Fwg_uid=td.Fbuyer_id where td.Fdeal_gen_time >= '{$date_start}' AND td.Fdeal_gen_time < '{$date_end}' and td.Fdeal_property3 = 64 and td.Fdeal_source not in(2,3,8) SQL_STMT; $sql_stmt_out = <<<SQL_STMT_OUT --SELECT ISNULL(OTag, '') as OTag, -- SO_Item.SOSysNo as OId, -- CONVERT(VARCHAR, SO_Master.OrderDate, 112) OrderDate, -- Quantity as Qty, -- (Price * Quantity) as OFee, -- SO_Master.CustomerSysNo as UId, -- (CASE WHEN SO_Master.SalesManSysNo = 666666 THEN 1999 ELSE SO_Master.SiteNo END) as wh_id --FROM SO_Item --INNER JOIN SO_Master ON SO_Item.SOSysNo = SO_Master.SysNo --WHERE SO_Master.OutTime >= '{$date_start}' AND SO_Master.OutTime < '{$date_end}' -- AND SO_Master.Status IN (4, -4, -5) -- AND SO_Master.SalesManSysNo NOT IN (10, 7611007, 888888, 999999, 999888, 999998) -- AND SO_Item.ProductType = 0 SELECT ISNULL(tr.ext_11002, '') as OTag, td.Fbusiness_deal_id as OId, CONVERT(VARCHAR, td.Fdeal_gen_time, 112) OrderDate, tr.Ftrade_buy_num as Qty, (tr.Ftrade_buy_num*tr.Ftrade_buy_price / 100.00) as OFee, ISNULL(u.Ficson_uid,0) as UId, (case when td.Fdeal_source in(2,3,8) then 1969 else td.fsite_id end) as wh_id from t_deal td left join t_trade tr on td.Fdeal_id = tr.fdeal_id left join etl_users..t_user_buyer u on u.Fwg_uid=td.Fbuyer_id where td.Fdeal_seller_consign_time >= '{$date_start}' AND td.Fdeal_seller_consign_time < '{$date_end}' and tr.Ftrade_state IN (3,4,9) and td.Fdeal_property3 = 64 and td.Fdeal_source not in(2,3,8) SQL_STMT_OUT; // 获取订单数据 $mssql = false; $order_rows = _getRowsWithRetries('ETL0_ICSONJD_ORDERS', $sql_stmt, $mssql); // 查询数据失败 if (false === $order_rows) { return false; } // 查询数据失败 if (false === $order_rows || empty($order_rows)) { Logger::err("get order data [ERP_SYNC_{$site_id}] failed:" . $mssql->errMsg . "\n"); return false; } // 获取当日出库的订单数据 $outstock_rows = _getRowsWithRetries('ETL0_ICSONJD_ORDERS', $sql_stmt_out, $mssql); // 查询数据失败 if (false === $outstock_rows) { Logger::err("get out stock order data [ERP_SYNC_{$site_id}] failed:" . $mssql->errMsg . "\n"); return false; } // Step 1.0: Get user first order date $order_users = array(); foreach ($order_rows as &$row) { $order_users[$row['UId']] = 0; } $user_first_order_date = _getOrderUserFirstOrderDate($order_users);//有些人的首次下单时间会找不到 // Step 1.1: 获取订单外部来源 foreach ($order_rows as &$row) { $order_id = $row['OId']; $user_id = intval($row['UId']); $source_id = SOURCE_ID_DIRECT; //直接访问 if (isset($order_sourceid[$order_id])) { continue; } // 订单跟踪码格式：0级页面tag-1级页面tag-2级页面tag-3级页面tag.level // 有很多whid 为1999的 没有otag $tags = explode("-", $row['OTag']); if (!isset($tags[3])) { continue; } else { $source_id = (int) (intval($tags[0]) / 100000); if($source_id < 0){ continue; } } // 非法的Source ID算作其它来源 if ( ($source_id > 0) && ($source_id % 10 != PAGE_TYPE_EXT_PROMOTION) && ($source_id % 10 != PAGE_TYPE_EXT_SEARCH) ) { continue; } // 到这里已经可以确定是外部来源 if ($source_id > 0) { $order_sourceid[$order_id] = $source_id; } } foreach ($order_rows as &$row) { $source_id = -99999; $tags = explode("-", $row['OTag']); if (isset($tags[3])) { $source_id = (int) (intval($tags[0]) / 100000); if($source_id < 0){ $source_id = -99999; } // 非法的Source ID算作其它来源 else if ( ($source_id > 0) && ($source_id % 10 != PAGE_TYPE_EXT_PROMOTION) && ($source_id % 10 != PAGE_TYPE_EXT_SEARCH) ) { $source_id = -99999; } } if (-99999 == $source_id) { $order_id = $row['OId']; if (isset($order_sourceid[$order_id])) { $source_id = $order_sourceid[$order_id]; } else { //continue; } } $wh_id = intval($row['wh_id']); $user_id = intval($row['UId']); $access_key = "{$wh_id}_{$source_id}"; $access_allwh_key = "0_{$source_id}"; $uid = $row['UId']; $user_type = USER_TYPE_NEW; if (isset($user_first_order_date[$uid])) { $user_type = ($user_first_order_date[$uid] == $row['OrderDate']) ? USER_TYPE_NEW : USER_TYPE_RETURN; } _mergeOrderData($order_data, $access_key, $user_id, $user_type, $row); _mergeOrderData($order_data, $access_allwh_key, $user_id, $user_type, $row); } // end of foreach ($rows as $row) ... // Step 2.0: Get user first order date $order_users = array(); foreach ($outstock_rows as &$row) { $order_users[$row['UId']] = 0; } $user_first_order_date = _getOrderUserFirstOrderDate($order_users); // Step 2.1: 获取订单外部来源 foreach ($outstock_rows as &$row) { $order_id = $row['OId']; $user_id = intval($row['UId']); $source_id = SOURCE_ID_DIRECT; if (isset($order_sourceid[$order_id])) { continue; } // 订单跟踪码格式：0级页面tag-1级页面tag-2级页面tag-3级页面tag.level $tags = explode("-", $row['OTag']); if (!isset($tags[3])) { continue; } else { $source_id = (int) (intval($tags[0]) / 100000); if($source_id < 0){ continue; } } // 非法的Source ID算作其它来源 if ( ($source_id > 0) && ($source_id % 10 != PAGE_TYPE_EXT_PROMOTION) && ($source_id % 10 != PAGE_TYPE_EXT_SEARCH) ) { continue; } // 到这里已经可以确定是外部来源 if ($source_id > 0) { $order_sourceid[$order_id] = $source_id; } } foreach ($outstock_rows as &$row) { $source_id = -99999; $tags = explode("-", $row['OTag']); if (isset($tags[3])) { $source_id = (int) (intval($tags[0]) / 100000); if($source_id < 0){ $source_id = -99999; } // 非法的Source ID算作其它来源 else if ( ($source_id > 0) && ($source_id % 10 != PAGE_TYPE_EXT_PROMOTION) && ($source_id % 10 != PAGE_TYPE_EXT_SEARCH) ) { $source_id = -99999; } } if (-99999 == $source_id) { $order_id = $row['OId']; if (isset($order_sourceid[$order_id])) { $source_id = $order_sourceid[$order_id]; } else { continue; } } $wh_id = intval($row['wh_id']); $user_id = intval($row['UId']); $access_key = "{$wh_id}_{$source_id}"; $access_allwh_key = "0_{$source_id}"; $uid = $row['UId']; $user_type = USER_TYPE_NEW; if (isset($user_first_order_date[$uid])) { $user_type = ($user_first_order_date[$uid] == $row['OrderDate']) ? USER_TYPE_NEW : USER_TYPE_RETURN; } _mergeOrderData($outstock_data, $access_key, $user_id, $user_type, $row); _mergeOrderData($outstock_data, $access_allwh_key, $user_id, $user_type, $row); } // end of foreach ($rows as $row) ... // 当日下单的订单数据 foreach ($order_data as $access_key => &$access_data) { foreach ($access_data as $user_type => &$users) { foreach ($users as $user_id => &$user_data) { $order_num = 0; $order_product_num = 0; $order_fee = 0.0; if (isset($user_data['order_ids'])) { $order_num = count($user_data['order_ids']); foreach ($user_data['order_ids'] as $order_id => &$single_order_data) { $order_product_num += intval($single_order_data['Qty']); $order_fee += floatval($single_order_data['OFee']); } } if (!isset($_login_order_data[$access_key])) { $_login_order_data[$access_key] = array(); } if (!isset($_login_order_data[$access_key][$user_type])) { $_login_order_data[$access_key][$user_type] = array(); } if (!isset($_login_order_data[$access_key][$user_type][$user_id])) { $_login_order_data[$access_key][$user_type][$user_id] = array(); } $_login_order_data[$access_key][$user_type][$user_id]['order_num'] = $order_num; $_login_order_data[$access_key][$user_type][$user_id]['order_product_num'] = $order_product_num; $_login_order_data[$access_key][$user_type][$user_id]['order_fee'] = $order_fee; } } // end of foreach ($access_data as $user_type => &$users) { ... } // end of foreach ($order_data as $access_key => &$access_data) { ... // 出库订单数据 foreach ($outstock_data as $access_key => &$access_data) { foreach ($access_data as $user_type => &$users) { foreach ($users as $user_id => &$user_data) { $out_num = 0; $out_product_num = 0; $out_fee = 0.0; if (isset($user_data['order_ids'])) { $out_num = count($user_data['order_ids']); foreach ($user_data['order_ids'] as $order_id => &$single_order_data) { $out_product_num += intval($single_order_data['Qty']); $out_fee += floatval($single_order_data['OFee']); } } if (!isset($_login_order_data[$access_key])) { $_login_order_data[$access_key] = array(); } if (!isset($_login_order_data[$access_key][$user_type])) { $_login_order_data[$access_key][$user_type] = array(); } if (!isset($_login_order_data[$access_key][$user_type][$user_id])) { $_login_order_data[$access_key][$user_type][$user_id] = array(); } $_login_order_data[$access_key][$user_type][$user_id]['out_num'] = $out_num; $_login_order_data[$access_key][$user_type][$user_id]['out_product_num'] = $out_product_num; $_login_order_data[$access_key][$user_type][$user_id]['out_fee'] = $out_fee; } } // end of foreach ($access_data as $user_type => $users) { ... } // end of foreach ($outstock_data as $access_key => &$access_data) { ... $mssql = false; return true; } } function _generalUpdateResultPVOrderData(&$result_data, &$source_key, &$pv_order_data) { $order_num = isset($pv_order_data['order_num']) ? $pv_order_data['order_num'] : 0; $order_product_num = isset($pv_order_data['order_product_num']) ? $pv_order_data['order_product_num'] : 0; $order_fee = isset($pv_order_data['order_fee']) ? $pv_order_data['order_fee'] : 0.0; $out_num = isset($pv_order_data['out_num']) ? $pv_order_data['out_num'] : 0; $out_product_num = isset($pv_order_data['out_product_num']) ? $pv_order_data['out_product_num'] : 0; $out_fee = isset($pv_order_data['out_fee']) ? $pv_order_data['out_fee'] : 0.0; if (!isset($result_data[$source_key])) { $result_data[$source_key] = array(); $result_data[$source_key]['order_num'] = 0; $result_data[$source_key]['order_product_num'] = 0; $result_data[$source_key]['order_fee'] = 0.0; $result_data[$source_key]['out_num'] = 0; $result_data[$source_key]['out_product_num'] = 0; $result_data[$source_key]['out_fee'] = 0.0; } // 下单数据 $result_data[$source_key]['order_num'] += $order_num; $result_data[$source_key]['order_product_num'] += $order_product_num; $result_data[$source_key]['order_fee'] += $order_fee; $result_data[$source_key]['out_num'] += $out_num; $result_data[$source_key]['out_product_num'] += $out_product_num; $result_data[$source_key]['out_fee'] += $out_fee; return true; } function _getChannelSourceResultData(&$channel_source_result_data) { GLOBAL $_login_order_data; // Step 2: 获取已登录用户的数据 // 包括新老用户，未下过订单的用户算新用户 $order_users_data = array(); $outstock_users_data = array(); // Get order data foreach ($_login_order_data as $access_key => &$access_data) { // $access_key 格式: 分站ID_来源ID $key_array = explode("_", $access_key); if (!isset($key_array[1])) { continue; } $source_id = $key_array[1]; // 只获取外投推广数据 if ($source_id % 10 != PAGE_TYPE_EXT_PROMOTION) { continue; } foreach ($access_data as $user_type => &$users) { $source_key = "{$access_key}_{$user_type}"; foreach ($users as $user_id => &$user_data) { _generalUpdateResultPVOrderData($channel_source_result_data, $source_key, $user_data); $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; // 统计下单人数 if ($order_num > 0) { if (!isset($order_users_data[$source_key])) { $order_users_data[$source_key] = array(); } $order_users_data[$source_key][$user_id] = 0; } // 统计出库人数 if ($out_num > 0) { if (!isset($outstock_users_data[$source_key])) { $outstock_users_data[$source_key] = array(); } $outstock_users_data[$source_key][$user_id] = 0; } } } // end of foreach ($access_data as $user_type => &$users) { ... } // end of foreach ($_login_order_data as $access_key => &$access_data) { ... // 统计下单，出库人数 foreach ($order_users_data as $source_key => $source_data) { $order_user_num = count($source_data); $channel_source_result_data[$source_key]['order_user_num'] = $order_user_num; } foreach ($outstock_users_data as $source_key => $source_data) { $out_user_num = count($source_data); $channel_source_result_data[$source_key]['out_user_num'] = $out_user_num; } // Get order data foreach ($_login_order_data as $access_key => &$access_data) { // $access_key 格式: 分站ID_来源ID $key_array = explode("_", $access_key); if (!isset($key_array[1])) { continue; } $source_id = $key_array[1]; // 只获取外投推广数据 if ($source_id % 10 != PAGE_TYPE_EXT_PROMOTION) { continue; } $user_type = USER_TYPE_ALL; $source_key = "{$access_key}_{$user_type}"; foreach ($access_data as $user_type => &$users) { foreach ($users as $user_id => &$user_data) { _generalUpdateResultPVOrderData($channel_source_result_data, $source_key, $user_data); $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; // 统计下单人数 if ($order_num > 0) { if (!isset($order_users_data[$source_key])) { $order_users_data[$source_key] = array(); } $order_users_data[$source_key][$user_id] = 0; } // 统计出库人数 if ($out_num > 0) { if (!isset($outstock_users_data[$source_key])) { $outstock_users_data[$source_key] = array(); } $outstock_users_data[$source_key][$user_id] = 0; } } } // end of foreach ($access_data as $user_type => &$users) { ... } // end of foreach ($_login_order_data as $access_key => &$access_data) { ... // 统计下单，出库人数 foreach ($order_users_data as $source_key => $source_data) { $order_user_num = count($source_data); $channel_source_result_data[$source_key]['order_user_num'] = $order_user_num; } foreach ($outstock_users_data as $source_key => $source_data) { $out_user_num = count($source_data); $channel_source_result_data[$source_key]['out_user_num'] = $out_user_num; } $order_users_data = null; $outstock_users_data = null; return true; } function _storeChannelSourceResultData($s_date, &$channel_source_result_data) { GLOBAL $_sourceChannelData; // 写入点击流DB $mssql = false; $db_name = 'ICSON_STATISTICS_CLICKFLOW'; $table_name = "t_aggregation_daily_ext_source_channel_rawdata_test2"; // $channel_source_result_data 格式: 分站ID_来源ID_用户类型 foreach ($channel_source_result_data as $source_key => &$source_data) { $key_array = explode("_", $source_key); if (!isset($key_array[2])) { continue; } $wh_id = $key_array[0]; $source_id = $key_array[1]; $user_type = $key_array[2]; $level_1_id = isset($_sourceChannelData[$source_id]['tag_level_1_id']) ? $_sourceChannelData[$source_id]['tag_level_1_id'] : 0; $level_2_id = isset($_sourceChannelData[$source_id]['tag_level_2_id']) ? $_sourceChannelData[$source_id]['tag_level_2_id'] : 0; $level_3_id = isset($_sourceChannelData[$source_id]['tag_level_3_id']) ? $_sourceChannelData[$source_id]['tag_level_3_id'] : 0; $order_num = isset($source_data['order_num']) ? $source_data['order_num'] : 0; $order_product_num = isset($source_data['order_product_num']) ? $source_data['order_product_num'] : 0; $order_fee = isset($source_data['order_fee']) ? $source_data['order_fee'] : 0.0; $order_user_num = isset($source_data['order_user_num']) ? $source_data['order_user_num'] : 0; $out_num = isset($source_data['out_num']) ? $source_data['out_num'] : 0; $out_product_num = isset($source_data['out_product_num']) ? $source_data['out_product_num'] : 0; $out_fee = isset($source_data['out_fee']) ? $source_data['out_fee'] : 0.0; $out_user_num = isset($source_data['out_user_num']) ? $source_data['out_user_num'] : 0; $condition = "s_date='{$s_date}' and warehouse_id={$wh_id} and level_1_id={$level_1_id} and level_2_id={$level_2_id} and level_3_id={$level_3_id} "; $condition .= "and source_id={$source_id} and user_type={$user_type}"; $row = array( 'order_num_7day' => $order_num, 'order_product_num_7day' => $order_product_num, 'order_fee_7day' => $order_fee, 'order_user_num_7day' => $order_user_num, 'out_num_7day' => $out_num, 'out_product_num_7day' => $out_product_num, 'out_fee_7day' => $out_fee, 'out_user_num_7day' => $out_user_num, ); //var_dump($db_name); $ret = _updateWithRetries($db_name, $table_name, $row, $condition, $mssql); //var_dump($ret); if (false === $ret) { Logger::err("Update source channel stat data failed"); continue; } } // end of foreach ($channel_source_result_data as $source_key => &$source_data) { ... return true; } ///////////////////////////////////////////////////////////////////////////////////////////////////////// // 获取三级数据 ///////////////////////////////////////////////////////////////////////////////////////////////////////// // 只有渠道推广才有三级统计数据 function _getStatL3ResultData(&$stat_level_3_result_data) { GLOBAL $_login_order_data; GLOBAL $_sourceChannelData; GLOBAL $_statSourceData_Level_1; GLOBAL $_statSourceData_Level_2; GLOBAL $_statSourceData_Level_3; // 获取订单数据 $login_level_3_order_data = array(); foreach ($_login_order_data as $access_key => &$access_data) { // $access_key 格式: 分站ID_来源ID $key_array = explode("_", $access_key); if (!isset($key_array[1])) { continue; } $wh_id = $key_array[0]; $source_id = $key_array[1]; // 只获取外投推广数据 if ($source_id % 10 != PAGE_TYPE_EXT_PROMOTION) { continue; } if (!isset($_sourceChannelData[$source_id])) { continue; } $level_3_id = $_sourceChannelData[$source_id]['level_3_id']; $l3_access_key = "{$wh_id}_{$level_3_id}"; foreach ($access_data as $user_type => &$users) { foreach ($users as $user_id => &$user_data) { $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $order_product_num = isset($user_data['order_product_num']) ? $user_data['order_product_num'] : 0; $order_fee = isset($user_data['order_fee']) ? $user_data['order_fee'] : 0.0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; $out_product_num = isset($user_data['out_product_num']) ? $user_data['out_product_num'] : 0; $out_fee = isset($user_data['out_fee']) ? $user_data['out_fee'] : 0.0; if (!isset($login_level_3_order_data[$l3_access_key])) { $login_level_3_order_data[$l3_access_key] = array(); } if (!isset($login_level_3_order_data[$l3_access_key][$user_type])) { $login_level_3_order_data[$l3_access_key][$user_type] = array(); } if (!isset($login_level_3_order_data[$l3_access_key][$user_type][$user_id])) { $login_level_3_order_data[$l3_access_key][$user_type][$user_id] = array(); $login_level_3_order_data[$l3_access_key][$user_type][$user_id]['order_num'] = 0; $login_level_3_order_data[$l3_access_key][$user_type][$user_id]['order_product_num'] = 0; $login_level_3_order_data[$l3_access_key][$user_type][$user_id]['order_fee'] = 0; $login_level_3_order_data[$l3_access_key][$user_type][$user_id]['out_num'] = 0; $login_level_3_order_data[$l3_access_key][$user_type][$user_id]['out_product_num'] = 0; $login_level_3_order_data[$l3_access_key][$user_type][$user_id]['out_fee'] = 0; } // 订单数据 $login_level_3_order_data[$l3_access_key][$user_type][$user_id]['order_num'] += $order_num; $login_level_3_order_data[$l3_access_key][$user_type][$user_id]['order_product_num'] += $order_product_num; $login_level_3_order_data[$l3_access_key][$user_type][$user_id]['order_fee'] += $order_fee; // 出库订单数据 $login_level_3_order_data[$l3_access_key][$user_type][$user_id]['out_num'] += $out_num; $login_level_3_order_data[$l3_access_key][$user_type][$user_id]['out_product_num'] += $out_product_num; $login_level_3_order_data[$l3_access_key][$user_type][$user_id]['out_fee'] += $out_fee; } // end of foreach ($users as $user_id => &$user_data) { ... } // end of foreach ($access_data as $user_type => $users) { ... } // end of foreach ($_login_order_data as $access_key => &$access_data) { ... // Step 2.1: 获取已登录用户的数据 // 包括新老用户，未下过订单的用户算作新用户 $order_users_data = array(); $outstock_users_data = array(); // Get order data foreach ($login_level_3_order_data as $l3_access_key => &$l3_access_data) { // $l3_access_key 格式: 分站ID_三级来源ID foreach ($l3_access_data as $user_type => &$users) { $source_key = "{$l3_access_key}_{$user_type}"; foreach ($users as $user_id => &$user_data) { _generalUpdateResultPVOrderData($stat_level_3_result_data, $source_key, $user_data); $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; // 统计下单人数 if ($order_num > 0) { if (!isset($order_users_data[$source_key])) { $order_users_data[$source_key] = array(); } $order_users_data[$source_key][$user_id] = 0; } // 统计出库人数 if ($out_num > 0) { if (!isset($outstock_users_data[$source_key])) { $outstock_users_data[$source_key] = array(); } $outstock_users_data[$source_key][$user_id] = 0; } } } // end of foreach ($$l3_access_data as $user_type => &$users) { ... } // end of foreach ($login_level_3_order_data as $l3_access_key => &$l3_access_data) { ... // 统计下单，出库人数 foreach ($order_users_data as $source_key => &$source_data) { $order_user_num = count($source_data); $stat_level_3_result_data[$source_key]['order_user_num'] = $order_user_num; } foreach ($outstock_users_data as $source_key => &$source_data) { $out_user_num = count($source_data); $stat_level_3_result_data[$source_key]['out_user_num'] = $out_user_num; } // Step 2.2: 获取已登录用户的数据 // 包括新老用户，且包括未下过订单的用户 $order_users_data = array(); $outstock_users_data = array(); // Get order data foreach ($login_level_3_order_data as $l3_access_key => &$l3_access_data) { // $l2_access_key 格式: 分站ID_三级来源ID $user_type = USER_TYPE_ALL; $source_key = "{$l3_access_key}_{$user_type}"; foreach ($l3_access_data as $user_type => &$users) { foreach ($users as $user_id => &$user_data) { _generalUpdateResultPVOrderData($stat_level_3_result_data, $source_key, $user_data); $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; // 统计下单人数 if ($order_num > 0) { if (!isset($order_users_data[$source_key])) { $order_users_data[$source_key] = array(); } $order_users_data[$source_key][$user_id] = 0; } // 统计出库人数 if ($out_num > 0) { if (!isset($outstock_users_data[$source_key])) { $outstock_users_data[$source_key] = array(); } $outstock_users_data[$source_key][$user_id] = 0; } } // end of foreach ($users as $user_id => &$user_data) { ... } // end of foreach ($l3_access_data as $user_type => &$users) { ... } // end of foreach ($login_level_3_order_data as $l3_access_key => &$l3_access_data) { ... // 统计下单，出库人数 foreach ($order_users_data as $source_key => &$source_data) { $order_user_num = count($source_data); $stat_level_3_result_data[$source_key]['order_user_num'] = $order_user_num; } foreach ($outstock_users_data as $source_key => &$source_data) { $out_user_num = count($source_data); $stat_level_3_result_data[$source_key]['out_user_num'] = $out_user_num; } return true; } // 只有渠道推广才有三级统计数据 function _storeStatL3ResultData($s_date, &$stat_level_3_result_data) { GLOBAL $_sourceChannelData; GLOBAL $_statSourceData_Level_1; GLOBAL $_statSourceData_Level_2; GLOBAL $_statSourceData_Level_3; // 写入点击流DB $mssql = false; $db_name = 'ICSON_STATISTICS_CLICKFLOW'; $table_name = "t_aggregation_daily_ext_source_level_3_test2"; // $stat_level_3_result_data 格式: 分站ID_三级来源ID_用户类型 foreach ($stat_level_3_result_data as $source_key => &$source_data) { $key_array = explode("_", $source_key); if (!isset($key_array[2])) { continue; } $wh_id = $key_array[0]; $level_3_id = $key_array[1]; $user_type = $key_array[2]; $level_1_id = $_statSourceData_Level_3[$level_3_id]['level_1_id']; $level_2_id = $_statSourceData_Level_3[$level_3_id]['level_2_id']; $order_num = isset($source_data['order_num']) ? $source_data['order_num'] : 0; $order_product_num = isset($source_data['order_product_num']) ? $source_data['order_product_num'] : 0; $order_fee = isset($source_data['order_fee']) ? $source_data['order_fee'] : 0.0; $order_user_num = isset($source_data['order_user_num']) ? $source_data['order_user_num'] : 0; $out_num = isset($source_data['out_num']) ? $source_data['out_num'] : 0; $out_product_num = isset($source_data['out_product_num']) ? $source_data['out_product_num'] : 0; $out_fee = isset($source_data['out_fee']) ? $source_data['out_fee'] : 0.0; $out_user_num = isset($source_data['out_user_num']) ? $source_data['out_user_num'] : 0; $condition = "s_date='{$s_date}' and warehouse_id={$wh_id} and level_1_id={$level_1_id} and level_2_id={$level_2_id} and level_3_id={$level_3_id} "; $condition .= "and user_type={$user_type}"; $row = array( 'order_num_7day' => $order_num, 'order_product_num_7day' => $order_product_num, 'order_fee_7day' => $order_fee, 'order_user_num_7day' => $order_user_num, 'out_num_7day' => $out_num, 'out_product_num_7day' => $out_product_num, 'out_fee_7day' => $out_fee, 'out_user_num_7day' => $out_user_num, ); $ret = _updateWithRetries($db_name, $table_name, $row, $condition, $mssql); if (false === $ret) { Logger::err("Update source channel stat data failed"); continue; } } // end of foreach ($stat_level_3_result_data as $source_key => &$source_data) { ... return true; } ///////////////////////////////////////////////////////////////////////////////////////////////////////// // 获取二级数据 ///////////////////////////////////////////////////////////////////////////////////////////////////////// function _getStatL2ResultData(&$stat_level_2_result_data) { GLOBAL $_login_order_data; GLOBAL $_sourceChannelData; GLOBAL $_searchSourceData; GLOBAL $_statSourceData_Level_1; GLOBAL $_statSourceData_Level_2; GLOBAL $_statSourceData_Level_3; $login_level_2_order_data = array(); foreach ($_login_order_data as $access_key => &$access_data) { // $access_key 格式: 分站ID_来源ID $key_array = explode("_", $access_key); if (!isset($key_array[1])) { continue; } $wh_id = $key_array[0]; $source_id = $key_array[1]; $level_2_id = STAT_OTHER_LEVEL_2_ID; // 直接访问处于一级 if (SOURCE_ID_DIRECT == $source_id) { continue; } // 外投推广数据 if (SOURCE_ID_OTHER == $source_id) { $level_2_id = STAT_OTHER_LEVEL_2_ID; } else if ($source_id % 10 == PAGE_TYPE_EXT_PROMOTION) { if (!isset($_sourceChannelData[$source_id])) { echo "Promotion Source: {$source_id} Not Exists!\n"; continue; } $level_2_id = $_sourceChannelData[$source_id]['level_2_id']; } else if ($source_id % 10 == PAGE_TYPE_EXT_SEARCH) { if (!isset($_searchSourceData[$source_id])) { echo "Search Source: {$source_id} Not Exists!\n"; continue; } $level_2_id = $_searchSourceData[$source_id]['level_2_id']; } $l2_access_key = "{$wh_id}_{$level_2_id}"; foreach ($access_data as $user_type => &$users) { foreach ($users as $user_id => &$user_data) { $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $order_product_num = isset($user_data['order_product_num']) ? $user_data['order_product_num'] : 0; $order_fee = isset($user_data['order_fee']) ? $user_data['order_fee'] : 0.0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; $out_product_num = isset($user_data['out_product_num']) ? $user_data['out_product_num'] : 0; $out_fee = isset($user_data['out_fee']) ? $user_data['out_fee'] : 0.0; if (!isset($login_level_2_order_data[$l2_access_key])) { $login_level_2_order_data[$l2_access_key] = array(); } if (!isset($login_level_2_order_data[$l2_access_key][$user_type])) { $login_level_2_order_data[$l2_access_key][$user_type] = array(); } if (!isset($login_level_2_order_data[$l2_access_key][$user_type][$user_id])) { $login_level_2_order_data[$l2_access_key][$user_type][$user_id] = array(); $login_level_2_order_data[$l2_access_key][$user_type][$user_id]['order_num'] = 0; $login_level_2_order_data[$l2_access_key][$user_type][$user_id]['order_product_num'] = 0; $login_level_2_order_data[$l2_access_key][$user_type][$user_id]['order_fee'] = 0.0; $login_level_2_order_data[$l2_access_key][$user_type][$user_id]['out_num'] = 0; $login_level_2_order_data[$l2_access_key][$user_type][$user_id]['out_product_num'] = 0; $login_level_2_order_data[$l2_access_key][$user_type][$user_id]['out_fee'] = 0.0; } // 订单数据 $login_level_2_order_data[$l2_access_key][$user_type][$user_id]['order_num'] += $order_num; $login_level_2_order_data[$l2_access_key][$user_type][$user_id]['order_product_num'] += $order_product_num; $login_level_2_order_data[$l2_access_key][$user_type][$user_id]['order_fee'] += $order_fee; // 出库订单数据 $login_level_2_order_data[$l2_access_key][$user_type][$user_id]['out_num'] += $out_num; $login_level_2_order_data[$l2_access_key][$user_type][$user_id]['out_product_num'] += $out_product_num; $login_level_2_order_data[$l2_access_key][$user_type][$user_id]['out_fee'] += $out_fee; } // end of foreach ($access_data as $user_id => &$user_data) { ... } } // end of foreach ($_login_access_data as $access_key => &$access_data) { ... // Step 2.1: 获取已登录用户的数据 // 包括新老用户，未下过订单的用户算作新用户 $order_users_data = array(); $outstock_users_data = array(); // Get order data foreach ($login_level_2_order_data as $l2_access_key => &$l2_access_data) { // $l2_access_key 格式: 分站ID_二级来源ID foreach ($l2_access_data as $user_type => &$users) { $source_key = "{$l2_access_key}_{$user_type}"; foreach ($users as $user_id => &$user_data) { _generalUpdateResultPVOrderData($stat_level_2_result_data, $source_key, $user_data); $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; // 统计下单人数 if ($order_num > 0) { if (!isset($order_users_data[$source_key])) { $order_users_data[$source_key] = array(); } $order_users_data[$source_key][$user_id] = 0; } // 统计出库人数 if ($out_num > 0) { if (!isset($outstock_users_data[$source_key])) { $outstock_users_data[$source_key] = array(); } $outstock_users_data[$source_key][$user_id] = 0; } } // end of foreach ($users as $user_id => &$user_data) { ... } } // end of foreach ($_login_access_data as $access_key => &$access_data) { ... // 统计下单，出库人数 foreach ($order_users_data as $source_key => &$source_data) { $order_user_num = count($source_data); $stat_level_2_result_data[$source_key]['order_user_num'] = $order_user_num; } foreach ($outstock_users_data as $source_key => &$source_data) { $out_user_num = count($source_data); $stat_level_2_result_data[$source_key]['out_user_num'] = $out_user_num; } // Step 2.2: 获取已登录用户的数据 // 包括新老用户，且包括未下过订单的用户 $order_users_data = array(); $outstock_users_data = array(); // Get Order data foreach ($login_level_2_order_data as $l2_access_key => &$l2_access_data) { // $l2_access_key 格式: 分站ID_二级来源ID $user_type = USER_TYPE_ALL; $source_key = "{$l2_access_key}_{$user_type}"; foreach ($l2_access_data as $user_type => &$users) { foreach ($users as $user_id => &$user_data) { _generalUpdateResultPVOrderData($stat_level_2_result_data, $source_key, $user_data); $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; // 统计下单人数 if ($order_num > 0) { if (!isset($order_users_data[$source_key])) { $order_users_data[$source_key] = array(); } $order_users_data[$source_key][$user_id] = 0; } // 统计出库人数 if ($out_num > 0) { if (!isset($outstock_users_data[$source_key])) { $outstock_users_data[$source_key] = array(); } $outstock_users_data[$source_key][$user_id] = 0; } } // end of foreach ($access_data as $user_id => &$user_data) { ... } } // end of foreach ($_login_access_data as $access_key => &$access_data) { ... // 统计下单，出库人数 foreach ($order_users_data as $source_key => &$source_data) { $order_user_num = count($source_data); $stat_level_2_result_data[$source_key]['order_user_num'] = $order_user_num; } foreach ($outstock_users_data as $source_key => &$source_data) { $out_user_num = count($source_data); $stat_level_2_result_data[$source_key]['out_user_num'] = $out_user_num; } return true; } function _storeStatL2ResultData($s_date, &$stat_level_2_result_data) { GLOBAL $_sourceChannelData; GLOBAL $_statSourceData_Level_1; GLOBAL $_statSourceData_Level_2; GLOBAL $_statSourceData_Level_3; // 写入点击流DB $mssql = false; $db_name = 'ICSON_STATISTICS_CLICKFLOW'; $table_name = "t_aggregation_daily_ext_source_level_2_test2"; // $stat_level_2_result_data 格式: 分站ID_二级来源ID_用户类型 foreach ($stat_level_2_result_data as $source_key => &$source_data) { $key_array = explode("_", $source_key); if (!isset($key_array[2])) { continue; } $wh_id = $key_array[0]; $level_2_id = $key_array[1]; $user_type = $key_array[2]; $level_1_id = $_statSourceData_Level_2[$level_2_id]['level_1_id']; $order_num = isset($source_data['order_num']) ? $source_data['order_num'] : 0; $order_product_num = isset($source_data['order_product_num']) ? $source_data['order_product_num'] : 0; $order_fee = isset($source_data['order_fee']) ? $source_data['order_fee'] : 0.0; $order_user_num = isset($source_data['order_user_num']) ? $source_data['order_user_num'] : 0; $out_num = isset($source_data['out_num']) ? $source_data['out_num'] : 0; $out_product_num = isset($source_data['out_product_num']) ? $source_data['out_product_num'] : 0; $out_fee = isset($source_data['out_fee']) ? $source_data['out_fee'] : 0.0; $out_user_num = isset($source_data['out_user_num']) ? $source_data['out_user_num'] : 0; $condition = "s_date='{$s_date}' and warehouse_id={$wh_id} and level_1_id={$level_1_id} and level_2_id={$level_2_id} "; $condition .= "and user_type={$user_type}"; $row = array( 'order_num_7day' => $order_num, 'order_product_num_7day' => $order_product_num, 'order_fee_7day' => $order_fee, 'order_user_num_7day' => $order_user_num, 'out_num_7day' => $out_num, 'out_product_num_7day' => $out_product_num, 'out_fee_7day' => $out_fee, 'out_user_num_7day' => $out_user_num, ); $ret = _updateWithRetries($db_name, $table_name, $row, $condition, $mssql); if (false === $ret) { Logger::err("Update source channel stat data failed"); continue; } } // end of foreach ($stat_level_2_result_data as $source_key => &$source_data) { ... return true; } ///////////////////////////////////////////////////////////////////////////////////////////////////////// // 获取一级数据 ///////////////////////////////////////////////////////////////////////////////////////////////////////// function _getStatL1ResultData(&$stat_level_1_result_data) { GLOBAL $_login_order_data; GLOBAL $_sourceChannelData; GLOBAL $_searchSourceData; GLOBAL $_statSourceData_Level_1; GLOBAL $_statSourceData_Level_2; GLOBAL $_statSourceData_Level_3; $login_level_1_order_data = array(); foreach ($_login_order_data as $access_key => &$access_data) { // $access_key 格式: 分站ID_来源ID $key_array = explode("_", $access_key); if (!isset($key_array[1])) { continue; } $wh_id = $key_array[0]; $source_id = $key_array[1]; $level_1_id = OTHER_LEVEL_1_ID; // 直接访问处于一级 if (SOURCE_ID_DIRECT == $source_id) { $level_1_id = DIRECT_LEVEL_1_ID; } // 外投推广数据 else if (SOURCE_ID_OTHER == $source_id) { $level_1_id = OTHER_LEVEL_1_ID; } else if ($source_id % 10 == PAGE_TYPE_EXT_PROMOTION) { if (!isset($_sourceChannelData[$source_id])) { echo "Promotion Source: {$source_id} Not Exists!\n"; continue; } $level_1_id = $_sourceChannelData[$source_id]['level_1_id']; } else if ($source_id % 10 == PAGE_TYPE_EXT_SEARCH) { if (!isset($_searchSourceData[$source_id])) { echo "Search Source: {$source_id} Not Exists!\n"; continue; } $level_1_id = $_searchSourceData[$source_id]['level_1_id']; } $l1_access_key = "{$wh_id}_{$level_1_id}"; foreach ($access_data as $user_type => &$users) { foreach ($users as $user_id => &$user_data) { $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $order_product_num = isset($user_data['order_product_num']) ? $user_data['order_product_num'] : 0; $order_fee = isset($user_data['order_fee']) ? $user_data['order_fee'] : 0.0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; $out_product_num = isset($user_data['out_product_num']) ? $user_data['out_product_num'] : 0; $out_fee = isset($user_data['out_fee']) ? $user_data['out_fee'] : 0.0; if (!isset($login_level_1_order_data[$l1_access_key])) { $login_level_1_order_data[$l1_access_key] = array(); } if (!isset($login_level_1_order_data[$l1_access_key][$user_type])) { $login_level_1_order_data[$l1_access_key][$user_type] = array(); } if (!isset($login_level_1_order_data[$l1_access_key][$user_type][$user_id])) { $login_level_1_order_data[$l1_access_key][$user_type][$user_id] = array(); $login_level_1_order_data[$l1_access_key][$user_type][$user_id]['order_num'] = 0; $login_level_1_order_data[$l1_access_key][$user_type][$user_id]['order_product_num'] = 0; $login_level_1_order_data[$l1_access_key][$user_type][$user_id]['order_fee'] = 0.0; $login_level_1_order_data[$l1_access_key][$user_type][$user_id]['out_num'] = 0; $login_level_1_order_data[$l1_access_key][$user_type][$user_id]['out_product_num'] = 0; $login_level_1_order_data[$l1_access_key][$user_type][$user_id]['out_fee'] = 0.0; } // 订单数据 $login_level_1_order_data[$l1_access_key][$user_type][$user_id]['order_num'] += $order_num; $login_level_1_order_data[$l1_access_key][$user_type][$user_id]['order_product_num'] += $order_product_num; $login_level_1_order_data[$l1_access_key][$user_type][$user_id]['order_fee'] += $order_fee; // 出库订单数据 $login_level_1_order_data[$l1_access_key][$user_type][$user_id]['out_num'] += $out_num; $login_level_1_order_data[$l1_access_key][$user_type][$user_id]['out_product_num'] += $out_product_num; $login_level_1_order_data[$l1_access_key][$user_type][$user_id]['out_fee'] += $out_fee; } // end of foreach ($access_data as $user_id => &$user_data) { ... } } // end of foreach ($_login_access_data as $access_key => &$access_data) { ... // Step 2.1: 获取已登录用户的数据 // 包括新老用户，未下过订单的用户算作新用户 $order_users_data = array(); $outstock_users_data = array(); // Get order data foreach ($login_level_1_order_data as $l1_access_key => &$l1_access_data) { // $l1_access_key 格式: 分站ID_一级来源ID foreach ($l1_access_data as $user_type => &$users) { $source_key = "{$l1_access_key}_{$user_type}"; foreach ($users as $user_id => &$user_data) { _generalUpdateResultPVOrderData($stat_level_1_result_data, $source_key, $user_data); $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; // 统计下单人数 if ($order_num > 0) { if (!isset($order_users_data[$source_key])) { $order_users_data[$source_key] = array(); } $order_users_data[$source_key][$user_id] = 0; } // 统计出库人数 if ($out_num > 0) { if (!isset($outstock_users_data[$source_key])) { $outstock_users_data[$source_key] = array(); } $outstock_users_data[$source_key][$user_id] = 0; } } // end of foreach ($access_data as $user_id => &$user_data) { ... } } // end of foreach ($_login_access_data as $access_key => &$access_data) { ... // 统计下单，出库人数 foreach ($order_users_data as $source_key => &$source_data) { $order_user_num = count($source_data); $stat_level_1_result_data[$source_key]['order_user_num'] = $order_user_num; } foreach ($outstock_users_data as $source_key => &$source_data) { $out_user_num = count($source_data); $stat_level_1_result_data[$source_key]['out_user_num'] = $out_user_num; } // Step 2.2: 获取已登录用户的数据 // 包括新老用户，且包括未下过订单的用户 $order_users_data = array(); $outstock_users_data = array(); // Get order data foreach ($login_level_1_order_data as $l1_access_key => &$l1_access_data) { // $l1_access_key 格式: 分站ID_一级来源ID $user_type = USER_TYPE_ALL; $source_key = "{$l1_access_key}_{$user_type}"; foreach ($l1_access_data as $user_type => &$users) { foreach ($users as $user_id => &$user_data) { _generalUpdateResultPVOrderData($stat_level_1_result_data, $source_key, $user_data); $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; // 统计下单人数 if ($order_num > 0) { if (!isset($order_users_data[$source_key])) { $order_users_data[$source_key] = array(); } $order_users_data[$source_key][$user_id] = 0; } // 统计出库人数 if ($out_num > 0) { if (!isset($outstock_users_data[$source_key])) { $outstock_users_data[$source_key] = array(); } $outstock_users_data[$source_key][$user_id] = 0; } } // end of foreach ($access_data as $user_id => &$user_data) { ... } } // end of foreach ($_login_access_data as $access_key => &$access_data) { ... // 统计下单，出库人数 foreach ($order_users_data as $source_key => &$source_data) { $order_user_num = count($source_data); $stat_level_1_result_data[$source_key]['order_user_num'] = $order_user_num; } foreach ($outstock_users_data as $source_key => &$source_data) { $out_user_num = count($source_data); $stat_level_1_result_data[$source_key]['out_user_num'] = $out_user_num; } return true; } function _storeStatL1ResultData($s_date, &$stat_level_1_result_data) { GLOBAL $_sourceChannelData; GLOBAL $_statSourceData_Level_1; GLOBAL $_statSourceData_Level_1; GLOBAL $_statSourceData_Level_3; // 写入点击流DB $mssql = false; $db_name = 'ICSON_STATISTICS_CLICKFLOW'; $table_name = "t_aggregation_daily_ext_source_level_1_test2"; // $stat_level_1_result_data 格式: 分站ID_二级来源ID_用户类型 foreach ($stat_level_1_result_data as $source_key => &$source_data) { $key_array = explode("_", $source_key); if (!isset($key_array[2])) { continue; } $wh_id = $key_array[0]; $level_1_id = $key_array[1]; $user_type = $key_array[2]; $order_num = isset($source_data['order_num']) ? $source_data['order_num'] : 0; $order_product_num = isset($source_data['order_product_num']) ? $source_data['order_product_num'] : 0; $order_fee = isset($source_data['order_fee']) ? $source_data['order_fee'] : 0.0; $order_user_num = isset($source_data['order_user_num']) ? $source_data['order_user_num'] : 0; $out_num = isset($source_data['out_num']) ? $source_data['out_num'] : 0; $out_product_num = isset($source_data['out_product_num']) ? $source_data['out_product_num'] : 0; $out_fee = isset($source_data['out_fee']) ? $source_data['out_fee'] : 0.0; $out_user_num = isset($source_data['out_user_num']) ? $source_data['out_user_num'] : 0; $condition = "s_date='{$s_date}' and warehouse_id={$wh_id} and level_1_id={$level_1_id} "; $condition .= "and user_type={$user_type}"; $row = array( 'order_num_7day' => $order_num, 'order_product_num_7day' => $order_product_num, 'order_fee_7day' => $order_fee, 'order_user_num_7day' => $order_user_num, 'out_num_7day' => $out_num, 'out_product_num_7day' => $out_product_num, 'out_fee_7day' => $out_fee, 'out_user_num_7day' => $out_user_num, ); $ret = _updateWithRetries($db_name, $table_name, $row, $condition, $mssql); if (false === $ret) { Logger::err("Update source channel stat data failed"); continue; } } // end of foreach ($stat_level_1_result_data as $source_key => &$source_data) { ... return true; } /////////////////////////////////////////////////////////////////////////////////////// // 渠道数据 一级、 二级 TAG 汇总 /////////////////////////////////////////////////////////////////////////////////////// function _getAggL2ChannelResultData(&$agg_channel_result_data) { GLOBAL $_login_order_data; GLOBAL $_sourceChannelData; GLOBAL $_statSourceData_Level_1; GLOBAL $_statSourceData_Level_2; GLOBAL $_statSourceData_Level_3; GLOBAL $_tag_Level_1; GLOBAL $_tag_Level_2; $login_agg_l2_order_data = array(); foreach ($_login_order_data as $access_key => &$access_data) { // $access_key 格式: 分站ID_来源ID $key_array = explode("_", $access_key); if (!isset($key_array[1])) { continue; } $wh_id = $key_array[0]; $source_id = $key_array[1]; // 只获取外投推广数据 if ($source_id % 10 != PAGE_TYPE_EXT_PROMOTION) { continue; } if (!isset($_sourceChannelData[$source_id])) { continue; } $tag_level_1_id = $_sourceChannelData[$source_id]['tag_level_1_id']; $tag_level_2_id = $_sourceChannelData[$source_id]['tag_level_2_id']; $agg_l2_access_key = "{$wh_id}_{$tag_level_1_id}_{$tag_level_2_id}"; foreach ($access_data as $user_type => &$users) { foreach ($users as $user_id => &$user_data) { $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $order_product_num = isset($user_data['order_product_num']) ? $user_data['order_product_num'] : 0; $order_fee = isset($user_data['order_fee']) ? $user_data['order_fee'] : 0.0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; $out_product_num = isset($user_data['out_product_num']) ? $user_data['out_product_num'] : 0; $out_fee = isset($user_data['out_fee']) ? $user_data['out_fee'] : 0.0; if (!isset($login_agg_l2_order_data[$agg_l2_access_key])) { $login_agg_l2_order_data[$agg_l2_access_key] = array(); } if (!isset($login_agg_l2_order_data[$agg_l2_access_key][$user_type])) { $login_agg_l2_order_data[$agg_l2_access_key][$user_type] = array(); } if (!isset($login_agg_l2_order_data[$agg_l2_access_key][$user_type][$user_id])) { $login_agg_l2_order_data[$agg_l2_access_key][$user_type][$user_id] = array(); $login_agg_l2_order_data[$agg_l2_access_key][$user_type][$user_id]['order_num'] = 0; $login_agg_l2_order_data[$agg_l2_access_key][$user_type][$user_id]['order_product_num'] = 0; $login_agg_l2_order_data[$agg_l2_access_key][$user_type][$user_id]['order_fee'] = 0.0; $login_agg_l2_order_data[$agg_l2_access_key][$user_type][$user_id]['out_num'] = 0; $login_agg_l2_order_data[$agg_l2_access_key][$user_type][$user_id]['out_product_num'] = 0; $login_agg_l2_order_data[$agg_l2_access_key][$user_type][$user_id]['out_fee'] = 0.0; } // 订单数据 $login_agg_l2_order_data[$agg_l2_access_key][$user_type][$user_id]['order_num'] += $order_num; $login_agg_l2_order_data[$agg_l2_access_key][$user_type][$user_id]['order_product_num'] += $order_product_num; $login_agg_l2_order_data[$agg_l2_access_key][$user_type][$user_id]['order_fee'] += $order_fee; // 出库订单数据 $login_agg_l2_order_data[$agg_l2_access_key][$user_type][$user_id]['out_num'] += $out_num; $login_agg_l2_order_data[$agg_l2_access_key][$user_type][$user_id]['out_product_num'] += $out_product_num; $login_agg_l2_order_data[$agg_l2_access_key][$user_type][$user_id]['out_fee'] += $out_fee; } // end of foreach ($access_data as $user_id => &$user_data) { ... } } // end of foreach ($_login_access_data as $access_key => &$access_data) { ... // Step 2.1: 获取已登录用户的数据 // 包括新老用户，未下过订单的用户算作新用户 $order_users_data = array(); $outstock_users_data = array(); foreach ($login_agg_l2_order_data as $agg_l2_access_key => &$agg_l2_access_data) { // $agg_l2_access_key 格式: 分站ID_一级TAGID_二级TAGID foreach ($agg_l2_access_data as $user_type => &$users) { $source_key = "{$agg_l2_access_key}_{$user_type}"; foreach ($users as $user_id => &$user_data) { _generalUpdateResultPVOrderData($agg_channel_result_data, $source_key, $user_data); $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; // 统计下单人数 if ($order_num > 0) { if (!isset($order_users_data[$source_key])) { $order_users_data[$source_key] = array(); } $order_users_data[$source_key][$user_id] = 0; } // 统计出库人数 if ($out_num > 0) { if (!isset($outstock_users_data[$source_key])) { $outstock_users_data[$source_key] = array(); } $outstock_users_data[$source_key][$user_id] = 0; } } // end of foreach ($access_data as $user_id => &$user_data) { ... } } // end of foreach ($_login_access_data as $access_key => &$access_data) { ... // 统计下单，出库人数 foreach ($order_users_data as $source_key => &$source_data) { $order_user_num = count($source_data); $agg_channel_result_data[$source_key]['order_user_num'] = $order_user_num; } foreach ($outstock_users_data as $source_key => &$source_data) { $out_user_num = count($source_data); $agg_channel_result_data[$source_key]['out_user_num'] = $out_user_num; } // Step 2.2: 获取已登录用户的数据 // 包括新老用户，且包括未下过订单的用户 $order_users_data = array(); $outstock_users_data = array(); foreach ($login_agg_l2_order_data as $agg_l2_access_key => &$agg_l2_access_data) { // $agg_l2_access_key 格式: 分站ID_一级TAGID_二级TAGID $user_type = USER_TYPE_ALL; $source_key = "{$agg_l2_access_key}_{$user_type}"; foreach ($agg_l2_access_data as $user_type => &$users) { foreach ($users as $user_id => &$user_data) { _generalUpdateResultPVOrderData($agg_channel_result_data, $source_key, $user_data); $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; // 统计下单人数 if ($order_num > 0) { if (!isset($order_users_data[$source_key])) { $order_users_data[$source_key] = array(); } $order_users_data[$source_key][$user_id] = 0; } // 统计出库人数 if ($out_num > 0) { if (!isset($outstock_users_data[$source_key])) { $outstock_users_data[$source_key] = array(); } $outstock_users_data[$source_key][$user_id] = 0; } } // end of foreach ($access_data as $user_id => &$user_data) { ... } } // end of foreach ($_login_access_data as $access_key => &$access_data) { ... // 统计下单，出库人数 foreach ($order_users_data as $source_key => &$source_data) { $order_user_num = count($source_data); $agg_channel_result_data[$source_key]['order_user_num'] = $order_user_num; } foreach ($outstock_users_data as $source_key => &$source_data) { $out_user_num = count($source_data); $agg_channel_result_data[$source_key]['out_user_num'] = $out_user_num; } $order_users_data = null; $outstock_users_data = null; return true; } function _getAggL1ChannelResultData(&$agg_channel_result_data) { GLOBAL $_login_order_data; GLOBAL $_sourceChannelData; GLOBAL $_statSourceData_Level_1; GLOBAL $_statSourceData_Level_2; GLOBAL $_statSourceData_Level_3; GLOBAL $_tag_Level_1; GLOBAL $_tag_Level_2; $login_agg_l1_order_data = array(); foreach ($_login_order_data as $access_key => &$access_data) { // $access_key 格式: 分站ID_来源ID $key_array = explode("_", $access_key); if (!isset($key_array[1])) { continue; } $wh_id = $key_array[0]; $source_id = $key_array[1]; // 只获取外投推广数据 if ($source_id % 10 != PAGE_TYPE_EXT_PROMOTION) { continue; } if (!isset($_sourceChannelData[$source_id])) { continue; } $tag_level_1_id = $_sourceChannelData[$source_id]['tag_level_1_id']; $tag_level_2_id = CHANNEL_ALL_SOURCE_ID; $agg_l1_access_key = "{$wh_id}_{$tag_level_1_id}_{$tag_level_2_id}"; foreach ($access_data as $user_type => &$users) { foreach ($users as $user_id => &$user_data) { $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $order_product_num = isset($user_data['order_product_num']) ? $user_data['order_product_num'] : 0; $order_fee = isset($user_data['order_fee']) ? $user_data['order_fee'] : 0.0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; $out_product_num = isset($user_data['out_product_num']) ? $user_data['out_product_num'] : 0; $out_fee = isset($user_data['out_fee']) ? $user_data['out_fee'] : 0.0; if (!isset($login_agg_l1_order_data[$agg_l1_access_key])) { $login_agg_l1_order_data[$agg_l1_access_key] = array(); } if (!isset($login_agg_l1_order_data[$agg_l1_access_key][$user_type])) { $login_agg_l1_order_data[$agg_l1_access_key][$user_type] = array(); } if (!isset($login_agg_l1_order_data[$agg_l1_access_key][$user_type][$user_id])) { $login_agg_l1_order_data[$agg_l1_access_key][$user_type][$user_id] = array(); $login_agg_l1_order_data[$agg_l1_access_key][$user_type][$user_id]['order_num'] = 0; $login_agg_l1_order_data[$agg_l1_access_key][$user_type][$user_id]['order_product_num'] = 0; $login_agg_l1_order_data[$agg_l1_access_key][$user_type][$user_id]['order_fee'] = 0.0; $login_agg_l1_order_data[$agg_l1_access_key][$user_type][$user_id]['out_num'] = 0; $login_agg_l1_order_data[$agg_l1_access_key][$user_type][$user_id]['out_product_num'] = 0; $login_agg_l1_order_data[$agg_l1_access_key][$user_type][$user_id]['out_fee'] = 0.0; } // 订单数据 $login_agg_l1_order_data[$agg_l1_access_key][$user_type][$user_id]['order_num'] += $order_num; $login_agg_l1_order_data[$agg_l1_access_key][$user_type][$user_id]['order_product_num'] += $order_product_num; $login_agg_l1_order_data[$agg_l1_access_key][$user_type][$user_id]['order_fee'] += $order_fee; // 出库订单数据 $login_agg_l1_order_data[$agg_l1_access_key][$user_type][$user_id]['out_num'] += $out_num; $login_agg_l1_order_data[$agg_l1_access_key][$user_type][$user_id]['out_product_num'] += $out_product_num; $login_agg_l1_order_data[$agg_l1_access_key][$user_type][$user_id]['out_fee'] += $out_fee; } // end of foreach ($access_data as $user_id => &$user_data) { ... } } // end of foreach ($_login_access_data as $access_key => &$access_data) { ... // Step 2.1: 获取已登录用户的数据 // 包括新老用户，未下过订单的用户算作新用户 $order_users_data = array(); $outstock_users_data = array(); // Get order data foreach ($login_agg_l1_order_data as $agg_l1_access_key => &$agg_l1_access_data) { // $agg_l1_access_key 格式: 分站ID_一级TAGID_二级TAGID foreach ($agg_l1_access_data as $user_type => &$users) { $source_key = "{$agg_l1_access_key}_{$user_type}"; foreach ($users as $user_id => &$user_data) { _generalUpdateResultPVOrderData($agg_channel_result_data, $source_key, $user_data); $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; // 统计下单人数 if ($order_num > 0) { if (!isset($order_users_data[$source_key])) { $order_users_data[$source_key] = array(); } $order_users_data[$source_key][$user_id] = 0; } // 统计出库人数 if ($out_num > 0) { if (!isset($outstock_users_data[$source_key])) { $outstock_users_data[$source_key] = array(); } $outstock_users_data[$source_key][$user_id] = 0; } } // end of foreach ($access_data as $user_id => &$user_data) { ... } } // end of foreach ($_login_access_data as $access_key => &$access_data) { ... // 统计下单，出库人数 foreach ($order_users_data as $source_key => &$source_data) { $order_user_num = count($source_data); $agg_channel_result_data[$source_key]['order_user_num'] = $order_user_num; } foreach ($outstock_users_data as $source_key => &$source_data) { $out_user_num = count($source_data); $agg_channel_result_data[$source_key]['out_user_num'] = $out_user_num; } // Step 2.2: 获取已登录用户的数据 // 包括新老用户，且包括未下过订单的用户 $order_users_data = array(); $outstock_users_data = array(); foreach ($login_agg_l1_order_data as $agg_l1_access_key => &$agg_l1_access_data) { // $agg_l1_access_key 格式: 分站ID_一级TAGID_二级TAGID $user_type = USER_TYPE_ALL; $source_key = "{$agg_l1_access_key}_{$user_type}"; foreach ($agg_l1_access_data as $user_type => &$users) { foreach ($users as $user_id => &$user_data) { _generalUpdateResultPVOrderData($agg_channel_result_data, $source_key, $user_data); $order_num = isset($user_data['order_num']) ? $user_data['order_num'] : 0; $out_num = isset($user_data['out_num']) ? $user_data['out_num'] : 0; // 统计下单人数 if ($order_num > 0) { if (!isset($order_users_data[$source_key])) { $order_users_data[$source_key] = array(); } $order_users_data[$source_key][$user_id] = 0; } // 统计出库人数 if ($out_num > 0) { if (!isset($outstock_users_data[$source_key])) { $outstock_users_data[$source_key] = array(); } $outstock_users_data[$source_key][$user_id] = 0; } } // end of foreach ($access_data as $user_id => &$user_data) { ... } } // end of foreach ($_login_access_data as $access_key => &$access_data) { ... // 统计下单，出库人数 foreach ($order_users_data as $source_key => &$source_data) { $order_user_num = count($source_data); $agg_channel_result_data[$source_key]['order_user_num'] = $order_user_num; } foreach ($outstock_users_data as $source_key => &$source_data) { $out_user_num = count($source_data); $agg_channel_result_data[$source_key]['out_user_num'] = $out_user_num; } $order_users_data = null; $outstock_users_data = null; return true; } function _storeAggChannelResultData($s_date, &$agg_channel_result_data) { GLOBAL $_sourceChannelData; GLOBAL $_statSourceData_Level_1; GLOBAL $_statSourceData_Level_2; GLOBAL $_statSourceData_Level_3; GLOBAL $_tag_Level_1; GLOBAL $_tag_Level_2; // 写入点击流DB $mssql = false; $db_name = 'ICSON_STATISTICS_CLICKFLOW'; $table_name = "t_aggregation_daily_ext_source_channel_stat_test2"; // $agg_channel_result_data 格式: 分站ID_一级TAGID_二级TAGID_用户类型 foreach ($agg_channel_result_data as $source_key => &$source_data) { $key_array = explode("_", $source_key); if (!isset($key_array[3])) { continue; } $wh_id = $key_array[0]; $tag_level_1_id = $key_array[1]; $tag_level_2_id = $key_array[2]; $user_type = $key_array[3]; $order_num = isset($source_data['order_num']) ? $source_data['order_num'] : 0; $order_product_num = isset($source_data['order_product_num']) ? $source_data['order_product_num'] : 0; $order_fee = isset($source_data['order_fee']) ? $source_data['order_fee'] : 0.0; $order_user_num = isset($source_data['order_user_num']) ? $source_data['order_user_num'] : 0; $out_num = isset($source_data['out_num']) ? $source_data['out_num'] : 0; $out_product_num = isset($source_data['out_product_num']) ? $source_data['out_product_num'] : 0; $out_fee = isset($source_data['out_fee']) ? $source_data['out_fee'] : 0.0; $out_user_num = isset($source_data['out_user_num']) ? $source_data['out_user_num'] : 0; $condition = "s_date='{$s_date}' and warehouse_id={$wh_id} and level_1_id={$tag_level_1_id} and level_2_id={$tag_level_2_id} "; $condition .= "and user_type={$user_type}"; $row = array( 'order_num_7day' => $order_num, 'order_product_num_7day' => $order_product_num, 'order_fee_7day' => $order_fee, 'order_user_num_7day' => $order_user_num, 'out_num_7day' => $out_num, 'out_product_num_7day' => $out_product_num, 'out_fee_7day' => $out_fee, 'out_user_num_7day' => $out_user_num, ); $ret = _updateWithRetries($db_name, $table_name, $row, $condition, $mssql); if (false === $ret) { Logger::err("Update source channel stat data failed"); continue; } } // end of foreach ($agg_channel_result_data as $source_key => &$source_data) { ... return true; } function _runSingleDay($s_date) { GLOBAL $_login_order_data; $_login_order_data = array(); $date_str = date("Ymd", strtotime($s_date)); $daily_log_filename = CLICKFLOW_DATA_DIR . "row_data_{$date_str}"; echo "Ext Source Order Statistic For: [{$s_date}] Started at: ", date("Y-m-d H:i:s"), ".\n"; // Step 1: 获取订单数据 _getSourceOrdersData($s_date); echo "Step 1: Get Orders Data Complete: ", date("Y-m-d H:i:s"), ".\n"; // Step 2: Get Channel Source Result Data $channel_source_result_data = array(); _getChannelSourceResultData($channel_source_result_data); _storeChannelSourceResultData($s_date, $channel_source_result_data); // 不再使用，释放资源 $channel_source_result_data = null; echo "Step 2: Get and Store Channel Source Result Data Complete: ", date("Y-m-d H:i:s"), ".\n"; // Step 3: Get Stat Level 3 Result Data // 只有渠道推广才有三级统计数据 $stat_level_3_result_data = array(); _getStatL3ResultData($stat_level_3_result_data); _storeStatL3ResultData($s_date, $stat_level_3_result_data); $stat_level_3_result_data = null; echo "Step 3: Get Stat Level 3 Result Data Complete: ", date("Y-m-d H:i:s"), ".\n"; // Step 4: Get Stat Level 2 Result Data $stat_level_2_result_data = array(); _getStatL2ResultData($stat_level_2_result_data); _storeStatL2ResultData($s_date, $stat_level_2_result_data); $stat_level_2_result_data = null; echo "Step 4: Get Stat Level 2 Result Data Complete: ", date("Y-m-d H:i:s"), ".\n"; // Step 5: Get Stat Level 1 Result Data $stat_level_1_result_data = array(); _getStatL1ResultData($stat_level_1_result_data); _storeStatL1ResultData($s_date, $stat_level_1_result_data); $stat_level_1_result_data = null; echo "Step 5: Get Stat Level 1 Result Data Complete: ", date("Y-m-d H:i:s"), ".\n"; // Step 6: Channel Level 1 and Level 2 Aggregation Result Data $agg_channel_result_data = array(); _getAggL2ChannelResultData($agg_channel_result_data); _getAggL1ChannelResultData($agg_channel_result_data); _storeAggChannelResultData($s_date, $agg_channel_result_data); $agg_channel_result_data = null; echo "Step 6: Channel Level 1 and Level 2 Aggregation Result Data Complete: ", date("Y-m-d H:i:s"), ".\n"; return true; } function run($s_date_from, $s_date_to) { // 获取TAG系统数据 _getSourceChannelData(); _getStatSourceData_Level_X(); echo "Step 0: Get Source Channle and Stat Data Complete: ", date("Y-m-d H:i:s"), ".\n\n"; for ($date_iter = $s_date_from; $date_iter <= $s_date_to; $date_iter = date('Y-m-d', strtotime('+1 day', strtotime($date_iter)))) { echo "================================================================\n"; _runSingleDay($date_iter); echo "================================================================\n"; } return true; } $process_start = date("Y-m-d H:i:s"); $_s_date_from = isset($argv[1]) ? $argv[1] : date("Y-m-d", strtotime("-1 day")); $_s_date_to = isset($argv[2]) ? $argv[2] : $_s_date_from; echo "extSourceStat_7Day_Orders Start at: ", date("Y-m-d H:i:s"), " For: ", "[{$_s_date_from} ~ {$_s_date_to}]", ".\n"; run($_s_date_from, $_s_date_to); $process_end = date("Y-m-d H:i:s"); echo "extSourceStat_7Day_Orders Done, For: [{$_s_date_from} - {$_s_date_to}], Done.[{$process_start} ~ {$process_end}].\n";
15天cookie脚本运行环境:10.206.8.75 机器 kamilzhou_crontab BI_75机器crontab列表 ;
20 6 * * * /data/clickflow/shell/extSourceStat_7Day_Orders.sh >> /data/clickflow/shell/log/extSourceStat_7Day_Orders.log
15天cookie脚本执行步骤:
①_getSourceChannelData();//获取Tag系统数据(和推广渠道相关)
Tag数据存在mysql表上10.180.92.30:3306 DB(icson) USER(user_icson) PASSWD(icson)
get_level_2
get_level_3 以此类推
