function data_center_user_list()
{
$result = get_filter();
$filter = array();
if ($result === false) {
/* 过滤条件 */
$filter['user_name'] = empty($_REQUEST['user_name']) ? '' : trim(urldecode($_REQUEST['user_name']));
$filter['admin_id'] = empty($_REQUEST['admin_id']) ? 0 : intval($_REQUEST['admin_id']);
$filter['address'] = empty($_REQUEST['address']) ? '' : trim($_REQUEST['address']);
$filter['zipcode'] = empty($_REQUEST['zipcode']) ? '' : trim($_REQUEST['zipcode']);
$filter['home_phone'] = empty($_REQUEST['tel']) ? '' : trim($_REQUEST['tel']);
$filter['mobile_phone'] = empty($_REQUEST['mobile']) ? 0 : intval($_REQUEST['mobile']);
$filter['country'] = empty($_REQUEST['country']) ? 0 : intval($_REQUEST['country']);
$filter['province'] = empty($_REQUEST['province']) ? 0 : intval($_REQUEST['province']);
$filter['city'] = empty($_REQUEST['city']) ? 0 : intval($_REQUEST['city']);
$filter['district'] = empty($_REQUEST['district']) ? 0 : intval($_REQUEST['district']);
$filter['platform'] = empty($_REQUEST['platform']) ? 0 : intval($_REQUEST['platform']);
$filter['from_where'] = !intval($_REQUEST['from_where']) ? 0 : intval($_REQUEST['from_where']);
$filter['eff_id'] = empty($_REQUEST['eff_id']) ? 0 : intval($_REQUEST['eff_id']);
$filter['start_time'] = empty($_REQUEST['start_time']) ? 0 : $_REQUEST['start_time'];
$filter['end_time'] = empty($_REQUEST['end_time']) ? 0 : $_REQUEST['end_time'];
$filter['purchase'] = empty($_REQUEST['purchase']) ? 0 : trim($_REQUEST['purchase']);
$filter['district'] = empty($_REQUEST['district']) ? 0 : intval($_REQUEST['district']);
$filter['city'] = empty($_REQUEST['city']) ? 0 : $_REQUEST['city'];
$filter['province'] = empty($_REQUEST['province']) ? 0 : $_REQUEST['province'];
$filter['address'] = empty($_REQUEST['address']) ? 0 : trim($_REQUEST['address']);
$filter['number_purchased'] = empty($_REQUEST['number_purchased']) ? 0 : intval($_REQUEST['number_purchased']);
$filter['start_time'] = strtotime(stamp2date($_REQUEST['start_time'], 'Y-m-d H:i:s'));
$filter['end_time'] = strtotime(stamp2date($_REQUEST['end_time'], 'Y-m-d H:i:s'));
//李健均的有无服务顾客分类
if (isset($_REQUEST['type'])) {
$customer_type = urldecode($_REQUEST['type']);
if (!in_array($customer_type, array(77, 66))) {
$filter['type'] = empty($_REQUEST['type']) ? 0 : $customer_type;
} else {
$filter['type'] = 2;
$service_where = $customer_type == 66 ? ' AND service_time IS NOT NULL ' : ' AND service_time IS NULL ';
}
}
$filter['cat_tag'] = empty($_REQUEST['cat_tag']) ? 0 : intval($_REQUEST['cat_tag']);
if (isset($_REQUEST['is_ajax']) && $_REQUEST['is_ajax'] == 1) {
$filter['keywords'] = json_str_iconv($filter['keywords']);
}
$ex_where = ' WHERE 1 ';
// 顾客搜索
if (!empty($_REQUEST['keywords']) || isset($_REQUEST['keywords'])) {
$filter['keyfields'] = mysql_real_escape_string(trim($_REQUEST['keyfields']));
$filter['keywords'] = mysql_real_escape_string(trim(urldecode($_REQUEST['keywords'])));
$ex_where .= " AND {$filter['keyfields']} LIKE '%{$filter['keywords']}%' ";
}
foreach ($filter as $key => $val) {
if (!empty($val)) {
if ($key == 'type') {
$condition .= "&{$key}=" . urlencode($val);
continue;
}
$condition .= "&{$key}={$val}";
}
}
if ($filter['platform']) {
$ex_where .= " AND platform='{$filter['platform']}' ";
}
if ($filter['purchase']) {
$ex_where .= " AND purchase='{$filter['purchase']}' ";
}
if ($filter['number_purchased']) {
$ex_where .= " AND number_purchased>={$filter['number_purchased']} ";
}
/* 按顾客来源显示 顾客列表 */
if ($filter['from_where']) {
$ex_where .= " AND u.from_where='{$filter['from_where']}' ";
}
// 顾客姓名
if ($filter['user_name']) {
$ex_where .= " AND u.user_name LIKE '%{$filter['user_name']}%' ";
}
$sql = 'SELECT COUNT(*) FROM ' . $GLOBALS['ecs']->table('users') . ' u ';
$ex_where .= ' AND u.admin_id IN(0,493) ';
// 区
if ($filter['district']) {
$sql .= ', ' . $GLOBALS['ecs']->table('region') . ' r, ' . $GLOBALS['ecs']->table('user_address') . ' a ';
$ex_where .= " AND u.user_id=a.user_id AND a.district=r.region_id AND a.district={$filter['district']}";
} elseif ($filter['city']) {
$sql .= ', ' . $GLOBALS['ecs']->table('region') . ' r, ' . $GLOBALS['ecs']->table('user_address') . ' a ';
$ex_where .= " AND u.user_id=a.user_id AND a.city=r.region_id AND a.city={$filter['city']}";
} elseif ($filter['province']) {
$sql .= ', ' . $GLOBALS['ecs']->table('region') . ' r, ' . $GLOBALS['ecs']->table('user_address') . ' a ';
$ex_where .= " AND u.user_id=a.user_id AND a.province=r.region_id AND a.province={$filter['province']}";
}
// 详细地址
if ($filter['saddress']) {
$sql .= ', ' . $GLOBALS['ecs']->table('user_address') . ' a ';
$ex_where .= " AND a.address LIKE '%{$filter['saddress']}%' AND a.user_id=u.user_id";
}
// 自定义分类
$admin_id = $filter['admin_id'] == 0 ? $_SESSION['admin_id'] : $filter['admin_id'];
if ($filter['cat_tag']) {
$ex_where .= " AND user_cat='{$admin_id}-{$filter['cat_tag']}' ";
} elseif ($_REQUEST['act'] == 'user_cat_list') {
$ex_where .= " AND user_cat NOT LIKE '{$admin_id}-%' ";
}
if ($filter['start_time'] && $filter['end_time']) {
if ($filter['start_time'] > $filter['end_time']) {
$time_tmp = $filter['end_time'];
$filter['end_time'] = $filter['start_time'];
$filter['start_time'] = $time_tmp;
}
$ex_where .= " AND u.add_time BETWEEN '{$filter['start_time']}' AND '{$filter['end_time']}'";
}
if ($filter['type']) {
$ex_where .= " AND u.customer_type IN ({$filter['type']}) ";
}
// 功效分类
if ($filter['eff_id'] && $filter['eff_id'] > 0) {
$ex_where .= " AND u.eff_id={$filter['eff_id']}";
} else {
$ex_where .= ' AND u.eff_id=0 ';
}
$sql .= $ex_where . $service_where;
$filter['record_count'] = $GLOBALS['db']->getOne($sql);
/* 分页大小 */
$filter['page'] = empty($_REQUEST['page']) || intval($_REQUEST['page']) <= 0 ? 1 : intval($_REQUEST['page']);
if (isset($_REQUEST['page_size']) && intval($_REQUEST['page_size']) > 0) {
$filter['page_size'] = intval($_REQUEST['page_size']);
} else {
$filter['page_size'] = 20;
}
$filter['page_count'] = $filter['record_count'] > 0 ? ceil($filter['record_count'] / $filter['page_size']) : 1;
// 设置分页
$page_set = array(1, 2, 3, 4, 5, 6, 7);
if ($filter['page'] > 4) {
foreach ($page_set as &$val) {
$val += $filter['page'] - 4;
}
}
if (end($page_set) > $filter['page_count']) {
$page_set = array();
for ($i = 7; $i >= 0; $i--) {
if ($filter['page_count'] - $i > 0) {
$page_set[] = $filter['page_count'] - $i;
}
}
}
$sql = 'SELECT u.age_group,u.admin_name,u.user_id,u.is_black,e.eff_name,c.bind_time,u.user_name,u.sex,' . 'IF(u.birthday="2012-01-01",u.age_group,(YEAR(NOW())-YEAR(u.birthday))) birthday,u.is_validated,u.user_money,' . 'u.add_time,u.remarks,u.service_time FROM ' . $GLOBALS['ecs']->table('users') . ' u LEFT JOIN ' . $GLOBALS['ecs']->table('memship_number') . ' c ON c.user_id=u.user_id LEFT JOIN ' . $GLOBALS['ecs']->table('effects') . ' e ON e.eff_id=u.eff_id';
//判断一个月内转移的顾客
$_REQUEST['transfer_time'] && ($filter['transfer_time'] = $_REQUEST['transfer_time']);
if ($filter['transfer_time']) {
$ex_where .= ' AND u.transfer_time>' . $filter['transfer_time'];
}
if ($filter['district']) {
$sql .= ', ' . $GLOBALS['ecs']->table('region') . ' r, ' . $GLOBALS['ecs']->table('user_address') . ' a ';
$ex_where .= " AND u.user_id=a.user_id AND a.district=r.region_id AND a.district={$filter['district']}";
} elseif ($filter['city']) {
$sql .= ', ' . $GLOBALS['ecs']->table('region') . ' r, ' . $GLOBALS['ecs']->table('user_address') . ' a ';
$ex_where .= " AND u.user_id=a.user_id AND a.city=r.region_id AND a.city={$filter['city']}";
} elseif ($filter['province']) {
$sql .= ', ' . $GLOBALS['ecs']->table('region') . ' r, ' . $GLOBALS['ecs']->table('user_address') . ' a ';
$ex_where .= " AND u.user_id=a.user_id AND a.province=r.region_id AND a.province={$filter['province']}";
}
$ex_where .= $service_where . ' ORDER by service_time ASC LIMIT ' . ($filter['page'] - 1) * $filter['page_size'] . ', ' . $filter['page_size'];
$sql .= $ex_where;
$filter['keywords'] = stripslashes($filter['keywords']);
set_filter($filter, $sql);
} else {
$sql = $result['sql'];
$filter = $result['filter'];
}
$user_list = $GLOBALS['db']->getAll($sql);
// 正常查询
if (in_array($filter['keyfields'], array('home_phone', 'mobile_phone', 'qq', 'wechat', 'aliww'))) {
// 客服
$where = ' u.admin_id>0';
$sql_select = 'SELECT u.age_group,u.admin_name,u.user_id,u.is_black,c.card_number,c.bind_time,u.user_name,u.sex,' . 'IF(u.birthday="2012-01-01",u.age_group,(YEAR(NOW())-YEAR(u.birthday))) birthday,u.is_validated,u.add_time,' . 'u.remarks,u.service_time FROM ' . $GLOBALS['ecs']->table('users') . ' u LEFT JOIN ' . $GLOBALS['ecs']->table('memship_number') . ' c ON c.user_id=u.user_id LEFT JOIN ' . $GLOBALS['ecs']->table('effects') . ' e ON e.eff_id=u.eff_id LEFT JOIN ' . $GLOBALS['ecs']->table('user_contact') . " uc ON u.user_id=uc.user_id WHERE {$where} AND u.customer_type IN ({$filter['type']})" . ' AND uc.contact_name="%s" AND uc.contact_value="%s"';
switch ($filter['keyfields']) {
case 'home_phone':
$user_info = $GLOBALS['db']->getAll(sprintf($sql_select, 'tel', $filter['keywords']));
break;
case 'mobile_phone':
$user_info = $GLOBALS['db']->getAll(sprintf($sql_select, 'mobile', $filter['keywords']));
break;
default:
$user_info = $GLOBALS['db']->getAll(sprintf($sql_select, $filter['keyfields'], $filter['keywords']));
}
$user_list += $user_info;
}
foreach ($user_list as &$val) {
$val['add_time'] = date('Y-m-d', $val['add_time']);
$val['transfer_time'] = $val['transfer_time'] ? date('Y-m-d', $val['transfer_time']) : '-';
$val['service_time'] = date('Y-m-d', $val['service_time']);
$val['bind_time'] = date('Y-m-d', $val['bind_time']);
$val['bind_time'] = date('Y-m-d', $val['bind_time']);
if ($val['is_black'] == 1) {
$val['user_name'] = '<font class="font_blacklist" >' . $val['user_name'] . '</font>';
}
}
$arr = array('user_list' => $user_list, 'filter' => $filter, 'page_count' => $filter['page_count'], 'record_count' => $filter['record_count'], 'page_size' => $filter['page_size'], 'page' => $filter['page'], 'page_set' => $page_set, 'condition' => $condition, 'start' => ($filter['page'] - 1) * $filter['page_size'] + 1, 'end' => $filter['page'] * $filter['page_size']);
return $arr;
}
/**
* 异常订单列表
*/
function abnormal_order_list()
{
// 获取条件数据
$now_time = time();
$condition = '';
$filter['o'] = isset($_REQUEST['o']) ? intval($_REQUEST['o']) : 0;
$filter['free_shipping'] = isset($_REQUEST['free_shipping']) ? intval($_REQUEST['free_shipping']) : 0;
$filter['free_ems'] = isset($_REQUEST['free_ems']) ? intval($_REQUEST['free_ems']) : 0;
$filter['gift'] = isset($_REQUEST['gift']) ? intval($_REQUEST['gift']) : 0;
$filter['start_time'] = isset($_REQUEST['start_time']) ? strtotime(stamp2date($_REQUEST['start_time'], 'Y-m-d H:i:s')) : strtotime(date('Y-m-01 00:00', $now_time)) - 6 * 3600;
$filter['end_time'] = isset($_REQUEST['end_time']) ? strtotime(stamp2date($_REQUEST['end_time'], 'Y-m-d H:i:s')) : $now_time;
// 收集查询条件
foreach ($filter as $key => $val) {
if (!empty($val)) {
$condition .= "&{$key}={$val}";
}
}
// 设置查询的条件
$query = array('o' => ' final_amount=0 ', 'f' => ' (shipping_fee=0 AND final_amount<=50) ', 'e' => ' (shipping_id=19 AND shipping_fee=0) ', 'g' => ' ( is_gift=0 AND goods_price=0 ) ');
// 0元订单
if (isset($_REQUEST['o']) && $_REQUEST['o']) {
$sql_where[] = $query['o'];
} elseif (isset($_REQUEST['free_shipping']) && $_REQUEST['free_shipping']) {
$sql_where[] = $query['f'];
} elseif (isset($_REQUEST['free_ems']) && $_REQUEST['free_ems']) {
$sql_where[] = $query['e'];
} elseif (isset($_REQUEST['gift']) && $_REQUEST['gift']) {
$sql_where[] = $query['g'];
} else {
$sql_where = $query;
}
// 构造基础查询条件
if (count($sql_where)) {
$sql_where = ' AND (' . implode(' OR ', $sql_where) . ') ';
}
// 设置查询权限
if (!admin_priv('abnormal_list_all', '', false)) {
$sql_where .= " AND add_admin_id={$_SESSION['admin_id']} ";
}
// 统计时间
$sql_where .= " AND o.add_time BETWEEN {$filter['start_time']} AND {$filter['end_time']} AND order_status IN (1,5) AND shipping_status IN (0,1,2) ";
// 统计记录总数
$sql_select_count = 'SELECT DISTINCT o.order_id FROM ' . $GLOBALS['ecs']->table('order_info') . ' o, ' . $GLOBALS['ecs']->table('users') . ' u, ' . $GLOBALS['ecs']->table('admin_user') . ' a, ' . $GLOBALS['ecs']->table('order_goods') . " g WHERE o.admin_id=a.user_id AND o.user_id=u.user_id AND o.order_id=g.order_id {$sql_where}";
$order_id = $GLOBALS['db']->getCol($sql_select_count);
$filter['record_count'] = count($order_id);
/* 分页大小 */
$filter['page'] = empty($_REQUEST['page']) || intval($_REQUEST['page']) <= 0 ? 1 : intval($_REQUEST['page']);
if (isset($_REQUEST['page_size']) && intval($_REQUEST['page_size']) > 0) {
$filter['page_size'] = intval($_REQUEST['page_size']);
} else {
$filter['page_size'] = 15;
}
$filter['page_count'] = $filter['record_count'] > 0 ? ceil($filter['record_count'] / $filter['page_size']) : 1;
// 设置分页
$page_set = array(1, 2, 3, 4, 5, 6, 7);
if ($filter['page'] > 4) {
foreach ($page_set as &$val) {
$val += $filter['page'] - 4;
}
}
if (end($page_set) > $filter['page_count']) {
$page_set = array();
for ($i = 7; $i >= 0; $i--) {
if ($filter['page_count'] - $i > 0) {
$page_set[] = $filter['page_count'] - $i;
}
}
}
$sql_select = 'SELECT o.*,a.user_name add_admin,u.user_name buyer,u.aliww,u.qq,r.role_describe FROM ' . $GLOBALS['ecs']->table('order_info') . ' o, ' . $GLOBALS['ecs']->table('users') . ' u, ' . $GLOBALS['ecs']->table('order_goods') . ' g, ' . $GLOBALS['ecs']->table('admin_user') . ' a, ' . $GLOBALS['ecs']->table('role') . ' r WHERE o.admin_id=a.user_id AND o.user_id=u.user_id ' . ' AND o.order_id=g.order_id AND r.role_id=o.platform ' . "{$sql_where} GROUP BY g.order_id ORDER BY o.add_time DESC LIMIT " . ($filter['page'] - 1) * $filter['page_size'] . ",{$filter['page_size']}";
$res = $GLOBALS['db']->getAll($sql_select);
foreach ($res as &$val) {
$val['formated_order_amount'] = price_format($val['order_amount']);
$val['formated_money_paid'] = price_format($val['money_paid']);
$val['formated_total_fee'] = price_format($val['final_amount']);
$val['short_order_time'] = date('Y-m-d H:i', $val['add_time']);
}
$arr = array('orders' => $res, 'filter' => $filter, 'page_count' => $filter['page_count'], 'record_count' => $filter['record_count'], 'page_size' => $filter['page_size'], 'page' => $filter['page'], 'page_set' => $page_set, 'condition' => $condition, 'start' => ($filter['page'] - 1) * $filter['page_size'] + 1, 'end' => $filter['page'] * $filter['page_size']);
return $arr;
}
* 使用；不允许对程序代码以任何形式任何目的的再发布。
* ============================================================================
* $Author: yehuaixiao $
* $Id: order.php 17157 2010-05-13 06:02:31Z yehuaixiao $
*/
define('IN_ECS', true);
require dirname(__FILE__) . '/includes/init.php';
require_once ROOT_PATH . 'includes/lib_order.php';
require_once ROOT_PATH . 'includes/lib_goods.php';
require_once ROOT_PATH . '/' . ADMIN_PATH . '/includes/lib_goods.php';
$file = basename($_SERVER['PHP_SELF'], '.php');
date_default_timezone_set('Asia/Shanghai');
$nowtime = time();
if (!empty($_REQUEST['start_time']) && !empty($_REQUEST['end_time'])) {
$smarty->assign('start_time', stamp2date($_REQUEST['start_time'], 'Y-m-d H:i'));
$smarty->assign('end_time', stamp2date($_REQUEST['end_time'], 'Y-m-d H:i'));
}
/*-- 财务子菜单 --*/
if ($_REQUEST['act'] == 'menu') {
$file = strstr(basename($_SERVER['PHP_SELF']), '.', true);
$nav = list_nav();
$smarty->assign('nav_2nd', $nav[1][$file]);
$smarty->assign('nav_3rd', $nav[2]);
$smarty->assign('file_name', $file);
die($smarty->fetch('left.htm'));
} elseif ($_REQUEST['act'] == 'everyday_order_check') {
$res = array();
if (!admin_priv('everyday_order_check', '', false)) {
$res = array('timeout' => 2000, 'req_msg' => true, 'code' => 0, 'message' => '对不起，该帐号没有权限访问！');
die($json->encode($res));
}
/**
* 每月新增顾客
*/
function user_stats_monthly()
{
$filter['end_time'] = empty($_REQUEST['end_time']) ? time() : strtotime(stamp2date($_REQUEST['end_time'], 'Y-m-d'));
$filter['start_time'] = empty($_REQUEST['start_time']) ? strtotime(date('Y-m-01 00:00:00', $filter['end_time'])) : strtotime(stamp2date($_REQUEST['start_time'], 'Y-m-d'));
$filter['role_id'] = empty($_REQUEST['role_id']) ? 0 : intval($_REQUEST['role_id']);
$where = '';
// 统计的时间范围 默认为当前时间 向前推12个月
if ($filter['end_time'] > $filter['start_time']) {
$where .= " AND add_time BETWEEN {$filter['start_time']} AND {$filter['end_time']} ";
} else {
$where .= " AND add_time BETWEEN {$filter['end_time']} AND {$filter['start_time']} ";
}
// 获取时间标签
$sql_select = 'SELECT DISTINCT FROM_UNIXTIME(add_time, "%Y-%m") FROM ' . $GLOBALS['ecs']->table('users') . " WHERE 1 {$where}";
$date_list = $GLOBALS['db']->getCol($sql_select);
// 统计的团队条件
if (admin_priv('user_stats_monthly_all', '', false)) {
if ($filter['role_id']) {
$where .= " AND role_id={$filter['role_id']} ";
} else {
$where .= ' AND role_id IN (' . OFFLINE_SALE . ') ';
}
} elseif (admin_priv('user_stats_monthly_part', '', false)) {
$where .= " AND role_id={$_SESSION['role_id']} ";
} elseif (admin_priv('user_stats_monthly_row', '', false)) {
}
$sql_select = 'SELECT COUNT(*) user_num,FROM_UNIXTIME(add_time,"%Y-%m") date_month,admin_id,role_id FROM ' . $GLOBALS['ecs']->table('users') . " WHERE admin_id>0 AND customer_type IN (2, 3, 4, 5, 11) {$where} " . ' GROUP BY DATE_FORMAT(FROM_UNIXTIME(add_time), "%Y-%m"),admin_id ORDER BY role_id';
$result = $GLOBALS['db']->getAll($sql_select);
$stats = array();
$final = array();
foreach ($result as $val) {
$stats[$val['admin_id']][$val['date_month']] = $val['user_num'];
@($final[$val['role_id']]['共计'][$val['date_month']] += $val['user_num']);
}
// 获取客服列表
$sql_select = 'SELECT user_id,user_name,role_id FROM ' . $GLOBALS['ecs']->table('admin_user') . ' WHERE stats=1 AND role_id IN (' . OFFLINE_SALE . ')';
if (!admin_priv('all', '', false) && $filter['role_id']) {
$sql_select .= " AND role_id={$filter['role_id']} ";
}
$result = $GLOBALS['db']->getAll($sql_select);
$stats_tmp = array();
foreach ($result as $val) {
$stats_tmp[$val['role_id']][$val['user_id']] = isset($stats[$val['user_id']]) ? $stats[$val['user_id']] : 0;
}
$admin_list = array();
foreach ($result as $val) {
$admin_list[$val['user_id']] = $val['user_name'];
}
@($admin_list['共计'] = '共计');
foreach ($stats_tmp as $key => &$val) {
foreach ($final as $k => $v) {
if ($key == $k) {
$val += $v;
}
}
}
return array('stats' => $stats_tmp, 'admin_list' => $admin_list, 'date_list' => $date_list);
}
