/**
* 统计回购数据
*/
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;
}
