/** * 统计回购数据 */ function buy_back_stats($field) { if (!isset($_REQUEST['start_time'], $_REQUEST['end_time'])) { $filter['start_time'] = date('Y-m-01 00:00:00', time()); $filter['end_time'] = date('Y-m-t 23:59:59', time()); } else { $filter['start_time'] = $_REQUEST['start_time'] . ' 00:00:00'; $filter['end_time'] = $_REQUEST['end_time'] . ' 23:59:59'; } $ex_where = ' AND user_id>0'; if ($field == 'team') { if (admin_priv('buy_back_stats_all', '', false)) { $ex_where .= ' AND team>0 '; } else { if (admin_priv('personal_repo_trans-part', '', false)) { $trans_role_list = implode(',', trans_part_list()); $ex_where .= " AND team IN ({$trans_role_list}) "; } else { $ex_where .= " AND team={$_SESSION['role_id']} "; } } } else { if (admin_priv('personal_repo_all', '', false)) { $ex_where .= ' AND admin_id>0 '; } elseif (admin_priv('personal_repo_part', '', false)) { $ex_where .= " AND team={$_SESSION['role_id']} "; } else { $ex_where .= " AND admin_id={$_SESSION['admin_id']} "; } } // 统计各平台老顾客购买人数 //$sql_select = "SELECT COUNT(DISTINCT user_id) times,$field FROM ".$GLOBALS['ecs']->table('order_info'). // ' WHERE order_status IN (1,5) AND shipping_status IN (0,1,2) AND order_type IN (3,4,5,6,7) AND team<>23'. // " $ex_where GROUP BY user_id HAVING COUNT(user_id)>1"; $available_type = VALID_ORDER_TYPE; $start_time = '1316719262'; $end_time = $_SERVER['REQUEST_TIME']; $condition = " add_time BETWEEN {$start_time} AND {$end_time} "; $sql_select = "SELECT 1 times,{$field},user_id FROM " . $GLOBALS['ecs']->table('order_info') . " WHERE {$condition} AND order_status IN (1,5) AND shipping_status IN (0,1,2) AND order_type IN ({$available_type}) AND team<>23" . " {$ex_where} "; $all_old_users_res = $GLOBALS['db']->getAll($sql_select); //过虑只购买过一次的顾客 //if ($_SESSION['admin_id'] == 142) { $all_old_users_res = rebuy_repeat($all_old_users_res, 'user_id'); //} $platform_old_users_count = array(); // 各平台老顾客购买总人数 $all_old_users_count = 0; // 老顾客购买总人数 foreach ($all_old_users_res as $val) { $platform_old_users_count[$val[$field]] += $val['times']; $all_old_users_count += $val['times']; } // 统计各平台有购买记录的顾客总数量 $sql_select = "SELECT 1 times,{$field} FROM " . $GLOBALS['ecs']->table('order_info') . " WHERE {$condition} AND order_status IN (1,5) AND shipping_status IN (0,1,2) AND team<>23 AND final_amount>0 AND order_type IN ({$available_type}) {$ex_where}"; $platform_users_res = $GLOBALS['db']->getAll($sql_select); $platform_users_res = rebuy_repeat($platform_users_res, 'team'); // 计算有购买记录的顾客总数量 $all_users_count = 0; $platform_users_count = array(); foreach ($platform_users_res as $val) { $platform_users_count[$val[$field]] = $val['times']; $all_users_count += $val['times']; } // 老顾客数量/顾客总数量 $platform_users_rate = array(); foreach ($platform_users_count as $key => $value) { @($platform_users_rate[$key] = sprintf('%.2f%%', round($platform_old_users_count[$key] / $value * 100, 4))); } if ($all_users_count) { $all_users_rate = sprintf('%.2f%%', round($all_old_users_count / $all_users_count * 100, 4)); } else { $all_users_rate = '-'; } // 统计各平台订单总数量 $sql_select = "SELECT COUNT(1) times,{$field},SUM(final_amount) total_amount FROM " . $GLOBALS['ecs']->table('order_info') . " WHERE {$condition} AND order_status IN (1,5) AND shipping_status IN (0,1,2) AND team<>23 AND order_type IN ({$available_type}) {$ex_where} GROUP BY {$field}"; //if ($_SESSION['admin_id'] == 142) { // echo $sql_select;exit; //} $platform_order_res = $GLOBALS['db']->getAll($sql_select); // 计算订单总数量 $all_order_count = 0; $all_order_amount = 0; $platform_order_count = array(); $platform_order_amount = array(); foreach ($platform_order_res as $val) { $platform_order_count[$val[$field]] = $val['times']; $platform_order_amount[$val[$field]] = $val['total_amount']; $all_order_count += $val['times']; $all_order_amount = bcadd($all_order_amount, $val['total_amount'], 2); } // 统计各平台老顾客购买人次 $sql_select = "SELECT COUNT(order_id) times,{$field},SUM(final_amount) old_total_amount FROM " . $GLOBALS['ecs']->table('order_info') . ' WHERE order_status IN (1,5) AND shipping_status IN (0,1,2) AND team<>23 AND order_type IN (3,4,5,6,7)' . " {$ex_where} GROUP BY user_id HAVING COUNT(user_id)>1"; $platform_old_order_res = $GLOBALS['db']->getAll($sql_select); $platform_old_order_count = array(); $platform_old_order_amount = array(); $all_old_order_count = 0; $all_old_order_amount = 0; foreach ($platform_old_order_res as $val) { @($platform_old_order_count[$val[$field]] += $val['times']); @($platform_old_order_amount[$val[$field]] = bcadd($platform_old_order_amount[$val[$field]], $val['old_total_amount'], 2)); $all_old_order_count += $val['times']; $all_old_order_amount = bcadd($all_old_order_amount, $val['old_total_amount'], 2); } // 老顾客购买次数/订单总量 foreach ($platform_order_count as $key => $val) { @($platform_order_rate[$key] = sprintf('%.2f%%', round($platform_old_order_count[$key] / $val * 100, 4))); } unset($val); if ($all_order_count) { $all_order_rate = sprintf('%.2f%%', round($all_old_order_count / $all_order_count * 100, 4)); } else { $all_order_rate = '-'; } // 老顾客购买总金额/所有订单总金额 foreach ($platform_order_amount as $key => $val) { if ($val > 0) { @($platform_amount_rate[$key] = sprintf('%.2f%%', round($platform_old_order_amount[$key] / $val * 100, 4))); } else { $platform_amount_rate[$key] = '-'; } } if ($all_order_amount) { $all_amount_rate = sprintf('%.2f%%', round($all_old_order_amount / $all_order_amount * 100, 4)); } else { $all_amount_rate = '-'; } // 统计时间条件 $ex_where .= ' AND add_time BETWEEN ' . strtotime($filter['start_time']) . ' AND ' . strtotime($filter['end_time']); $sql_select = "SELECT COUNT(order_id) order_num,COUNT(DISTINCT user_id) user_num,SUM(final_amount) final_amount,{$field},platform FROM " . $GLOBALS['ecs']->table('order_info') . ' WHERE order_status=5 AND shipping_status IN (1,2) AND team<>23 AND order_type IN (3,4,5,6,7)' . " {$ex_where} AND user_id IN (SELECT DISTINCT user_id FROM " . $GLOBALS['ecs']->table('order_info') . ' WHERE order_status IN (1,5) AND ' . ' shipping_status IN (0,1,2) AND team<>23 AND order_type IN (3,4,5,6,7) AND add_time<' . strtotime($filter['start_time']) . ') GROUP BY user_id '; $result = $GLOBALS['db']->getAll($sql_select); $current_old = array(); foreach ($result as $key => &$val) { // 计算每个平台的老顾客数量 订单数量 订单金额 @($current_old[$val[$field]]['old_users_num'] += $val['user_num']); @($current_old[$val[$field]]['old_order_num'] += $val['order_num']); @($current_old[$val[$field]]['old_final_amount'] = bcadd($current_old[$val[$field]]['old_final_amount'], $val['final_amount'], 2)); // 计算总量 @($current_old['total']['old_users_num'] += $val['user_num']); @($current_old['total']['old_order_num'] += $val['order_num']); @($current_old['total']['old_final_amount'] = bcadd($current_old['total']['old_final_amount'], $val['final_amount'], 2)); } unset($result); // 获取起止时间内所有购买了产品的顾客信息 $sql_select = "SELECT COUNT(order_id) order_num,COUNT(DISTINCT user_id) user_num,SUM(final_amount) final_amount,{$field},platform FROM " . $GLOBALS['ecs']->table('order_info') . ' WHERE order_status IN (1,5) AND shipping_status IN (0,1,2) AND team<>23 AND order_type IN (3,4,5,6,7)' . " {$ex_where} GROUP BY user_id"; $result = $GLOBALS['db']->getAll($sql_select); $current_all = array(); foreach ($result as $val) { // 计算每个平台的新增顾客数量 订单数量 订单金额 @($current_all[$val[$field]]['order_num'] += $val['order_num']); @($current_all[$val[$field]]['users_num'] += $val['user_num']); @($current_all[$val[$field]]['final_amount'] = bcadd($current_all[$val[$field]]['final_amount'], $val['final_amount'], 2)); // 计算总量 @($current_all['total']['order_num'] += $val['order_num']); @($current_all['total']['users_num'] += $val['user_num']); @($current_all['total']['final_amount'] = bcadd($current_all['total']['final_amount'], $val['final_amount'], 2)); } // 合并数据 $result = array(); $arr_keys = array_unique(array_merge(array_keys($current_all), array_keys($current_old))); foreach ($arr_keys as $val) { if (!empty($current_all[$val]) && !empty($current_old[$val])) { $result[$val] = _array_merge($current_all[$val], $current_old[$val]); } elseif (!empty($current_all[$val])) { $result[$val] = $current_all[$val]; } elseif (!empty($current_old[$val])) { $result[$val] = $current_old[$val]; } } // 计算百分比 foreach ($result as &$val) { @($val['users_rate'] = sprintf("%.2f%%", round($val['old_users_num'] / $val['users_num'] * 100, 4))); @($val['order_rate'] = sprintf("%.2f%%", round($val['old_order_num'] / $val['order_num'] * 100, 4))); @($val['amount_rate'] = sprintf("%.2f%%", round($val['old_final_amount'] / $val['final_amount'] * 100, 4))); } // 与全部数据进行合并 foreach ($result as $k => &$v) { @($v['platform_users'] = $platform_users_count[$k]); @($v['platform_order'] = $platform_order_count[$k]); @($v['platform_amount'] = $platform_order_amount[$k]); @($v['platform_old_users'] = $platform_old_users_count[$k]); @($v['platform_old_order'] = $platform_old_order_count[$k]); @($v['platform_old_amount'] = $platform_old_order_amount[$k]); @($v['platform_users_rate'] = $platform_users_rate[$k]); @($v['platform_order_rate'] = $platform_order_rate[$k]); @($v['platform_amount_rate'] = $platform_amount_rate[$k]); @$platform_users_count[$k] > 0 && ($v['total_rate'] = sprintf("%.2f%%", round(@$v['old_users_num'] / $platform_users_count[$k] * 100, 4))); } unset($val); foreach ($result as $val) { $result['total']['platform_users'] += $val['platform_users']; $result['total']['platform_old_users'] += $val['platform_old_users']; $result['total']['platform_order'] += $val['platform_order']; $result['total']['platform_old_order'] += $val['platform_old_order']; @($result['total']['old_amount'] = bcadd($result['total']['old_amount'], $val['platform_old_amount'], 2)); @($result['total']['amount'] = bcadd($result['total']['amount'], $val['platform_amount'], 2)); } unset($val); if ($result['total']['platform_users']) { $result['total']['total_rate'] = sprintf("%.2f%%", round($result['total']['old_users_num'] / $result['total']['platform_users'] * 100, 4)); } else { $result['total']['total_rate'] = '-'; } $result['total']['platform_users_rate'] = $all_users_rate; $result['total']['platform_order_rate'] = $all_order_rate; $result['total']['platform_amount_rate'] = $all_amount_rate; $total = $result['total']; unset($result['total']); ksort($result); $result['total'] = $total; foreach ($result as &$val) { if ($val['final_amount'] > 0) { @($val['old_rate'] = sprintf("%.2f%%", round($val['old_final_amount'] / $val['final_amount'] * 100, 4))); } else { $val['old_rate'] = '-'; } } return $result; }
protected function _up_home() { global $bio, $warning; $v = $this->__(w('send address')); if (!empty($v->send)) { $v = _array_merge($v, $this->__(array_merge(w('password firstname lastname country status'), _array_keys(w('gender birth_day birth_month birth_year'), 0)))); if (empty($v->address)) { $warning->set('empty_address'); } if (empty($v->password)) { $warning->set('empty_password'); } if (!email_format($v->address)) { $warning->set('bad_address'); } if (!($v->alias = _low($v->firstname . $v->lastname))) { $warning->set('bad_alias'); } if ($this->alias_exists($v->alias)) { $warning->set('record_alias'); } if (!($v->country = $this->country_exists($v->country))) { $warning->set('bad_country'); } if (!$v->birth_day || !$v->birth_month || !$v->birth_year) { $warning->set('bad_birth'); } $v->birth = _timestamp($v->birth_month, $v->birth_day, $v->birth_year); $v->name = trim($v->firstname) . ' ' . trim($v->lastname); $sql_insert = array('type' => 0, 'level' => 0, 'active' => 1, 'alias' => $v->alias, 'name' => $v->firstname . ' ' . $v->lastname, 'first' => $v->firstname, 'last' => $v->lastname, 'key' => HashPassword($v->password), 'address' => $v->address, 'gender' => $v->gender, 'birth' => $v->birth, 'birthlast' => 0, 'regip' => $bio->v('ip'), 'regdate' => time(), 'session_time' => time(), 'lastpage' => '', 'timezone' => -6, 'dst' => 0, 'dateformat' => 'd M Y H:i', 'lang' => 'sp', 'country' => $v->country, 'avatar' => '', 'actkey' => '', 'recovery' => 0, 'fails' => 0); $bio->id = sql_put('_bio', prefix('bio', $sql_insert)); $sql_insert = array('bio' => $bio->id, 'name' => $v->address, 'primary' => 1); sql_put('_bio_address', prefix('address', $sql_insert)); echo 'OK'; exit; } //$gi = geoip_open(XFS.XCOR . 'store/geoip.dat', GEOIP_STANDARD); $geoip_code = ''; if ($bio->v('ip') != '127.0.0.1') { // GeoIP if (!@function_exists('geoip_country_code_by_name')) { //require_once(XFS.XCOR . 'geoip.php'); } //$geoip_code = @geoip_country_code_by_name($bio->v('ip')); } for ($i = 1; $i < 32; $i++) { if ($i == 1) { _style('birth_day'); } _style('birth_day.row', array('DAY' => $i)); } for ($i = 1; $i < 13; $i++) { if ($i == 1) { _style('birth_month'); } _style('birth_month.row', array('MONTH' => $i)); } for ($i = date('Y'); $i > 1900; $i--) { if ($i == date('Y')) { _style('birth_year'); } _style('birth_year.row', array('YEAR' => $i)); } //_pre($geoip_code, true); /* $sql = 'SELECT * FROM _countries ORDER BY country_name'; $countries = sql_rowset($sql); $v->country = ($v->country) ? $v->country : ((isset($country_codes[$geoip_code])) ? $country_codes[$geoip_code] : $country_codes['gt']); foreach ($countries as $i => $row) { if (!$i) _style('countries'); _style('countries.row', array( 'V_ID' => $row->country_id, 'V_NAME' => $row->country_name, 'V_SEL' => 0) ); } * */ return; }