531 行
19 KiB
PHP
可执行文件
531 行
19 KiB
PHP
可执行文件
<?php
|
||
|
||
namespace Common\Model;
|
||
|
||
use Think\Model\ViewModel;
|
||
|
||
//[1.6.88]
|
||
class MemberAnalyzeModel extends ViewModel
|
||
{
|
||
public $viewFields = array(
|
||
'RetailVip' => array(
|
||
'_table' => 'ls_retail_vip',
|
||
'id',
|
||
'name',
|
||
'nickname',
|
||
'headimgurl',
|
||
'mobile_phone',
|
||
'plus_type',
|
||
'grade_integral',
|
||
),
|
||
);
|
||
|
||
protected $tableName = 'retail_vip';
|
||
|
||
// 获取会员信息
|
||
public function getVip($where, $field, $page = 1, $pageSize = 10)
|
||
{
|
||
|
||
$list = $this->alias('rv')
|
||
->field($field)
|
||
->join('ls_retail_pay_order as rpo on rpo.vid = rv.id', 'LEFT')
|
||
->where($where)
|
||
->order('rv.id desc')
|
||
->page($page, $pageSize)
|
||
->select();
|
||
|
||
$total = $this->alias('rv')
|
||
->join('ls_retail_pay_order as rpo on rpo.vid = rv.id', 'LEFT')
|
||
->where($where)
|
||
->count();
|
||
$vip['list'] = $list;
|
||
$vip['total'] = $total;
|
||
return $vip;
|
||
}
|
||
|
||
|
||
/**
|
||
* 查询单条数据（find）
|
||
* @param array $map 条件
|
||
* @param string $field 字段 '*'
|
||
* @return array|bool $find|false
|
||
*/
|
||
public function getFind($map, $field = '*')
|
||
{
|
||
if (!$map) {
|
||
return false;
|
||
}
|
||
$find = $this->field($field)->where($map)->find();
|
||
return $find;
|
||
}
|
||
|
||
|
||
/**
|
||
* @param $map 条件
|
||
* @param string $field 获取的字段
|
||
* @param string $page 分页
|
||
* @param string $limit 数量
|
||
* @param string $order 排序
|
||
* @return bool|mixed $res|false
|
||
*/
|
||
public function getSelect($map, $field = '*', $page = '', $limit = '', $order = 'id asc')
|
||
{
|
||
if (!$map) {
|
||
return false;
|
||
}
|
||
if (!$page || !$limit) {
|
||
$select = $this->field($field)->where($map)->order($order)->select();
|
||
} else {
|
||
$select = $this->field($field)->where($map)->order($order)->page($page, $limit)->select();
|
||
}
|
||
return $select;
|
||
}
|
||
|
||
/**
|
||
* 统计数量（count）
|
||
* @parma [array] $map 条件
|
||
* @return [array|bool] $res|false
|
||
*/
|
||
public function getCount($map)
|
||
{
|
||
if (!$map) {
|
||
return 0;
|
||
}
|
||
$count = $this->where($map)->count();
|
||
return $count ? $count : 0;
|
||
}
|
||
|
||
|
||
/* ************************************** 会员统计Start ************************************** */
|
||
// 获取会员信息 -- 饼状图
|
||
public function getActiveUserBread($type, $store_id, $merchant_id)
|
||
{
|
||
$sleep = $type ? true : false;
|
||
$thisYear = date('Y'); // 当前年份
|
||
$thisMonth = date('n'); // 当前月份
|
||
|
||
// 当月
|
||
$active['one'] = $this->getFirstAndLast($thisYear, $thisMonth);
|
||
$member[0]['name'] = $thisYear . '-' . $thisMonth;
|
||
$member[0]['value'] = $this->getUserByTime($active['one']['begin_time'], $active['one']['end_time'], false, $sleep, $store_id, $merchant_id);
|
||
|
||
// 上一个月
|
||
$two_timestamp = $this->GetMonth(1);
|
||
$twoTime = explode('-', $two_timestamp);
|
||
$active['two'] = $this->getFirstAndLast($twoTime[0], $twoTime[1]);
|
||
$member[1]['name'] = $two_timestamp;
|
||
$member[1]['value'] = $this->getUserByTime($active['two']['begin_time'], $active['two']['end_time'], false, $sleep, $store_id, $merchant_id);
|
||
|
||
// 上两月
|
||
$three_timestamp = $this->GetMonth(2);
|
||
$threeTime = explode('-', $three_timestamp);
|
||
$active['three'] = $this->getFirstAndLast($threeTime[0], $threeTime[1]);
|
||
$member[2]['name'] = $three_timestamp;
|
||
$member[2]['value'] = $this->getUserByTime($active['three']['begin_time'], $active['three']['end_time'], false, $sleep, $store_id, $merchant_id);
|
||
|
||
// 上三月
|
||
$four_timestamp = $this->GetMonth(3);
|
||
$fourTime = explode('-', $four_timestamp);
|
||
$active['four'] = $this->getFirstAndLast($fourTime[0], $fourTime[1]);
|
||
$member[3]['name'] = $four_timestamp;
|
||
$member[3]['value'] = $this->getUserByTime($active['four']['begin_time'], $active['four']['end_time'], false, $sleep, $store_id, $merchant_id);
|
||
|
||
// 上四月
|
||
$five_timestamp = $this->GetMonth(4);
|
||
$fiveTime = explode('-', $five_timestamp);
|
||
$active['five'] = $this->getFirstAndLast($fiveTime[0], $fiveTime[1]);
|
||
$member[4]['name'] = $five_timestamp;
|
||
$member[4]['value'] = $this->getUserByTime($active['five']['begin_time'], $active['five']['end_time'], false, $sleep, $store_id, $merchant_id);
|
||
|
||
// 上五月
|
||
$six_timestamp = $this->GetMonth(5);
|
||
$sixTime = explode('-', $six_timestamp);
|
||
$active['six'] = $this->getFirstAndLast($sixTime[0], $sixTime[1]);
|
||
$member[5]['name'] = $six_timestamp;
|
||
$member[5]['value'] = $this->getUserByTime($active['six']['begin_time'], $active['six']['end_time'], false, $sleep, $store_id, $merchant_id);
|
||
|
||
if ($member) {
|
||
$res['status'] = 1;
|
||
$res['msg'] = '获取成功!';
|
||
$res['data'] = $member;
|
||
} else {
|
||
$res['status'] = 1;
|
||
$res['msg'] = '暂无数据,请稍后重试1!';
|
||
$res['data'] = [];
|
||
}
|
||
return $res;
|
||
}
|
||
|
||
// 根据月份获取用户数据
|
||
public function getActiveUserByMonth($timestamp, $type, $page, $pageSize, $store_id, $merchant_id)
|
||
{
|
||
$sleep = $type ? true : false;
|
||
|
||
if (!$timestamp) {
|
||
$res['status'] = 0;
|
||
$res['msg'] = '必要参数缺失!';
|
||
return $res;
|
||
}
|
||
|
||
$timeArr = explode('-', $timestamp);
|
||
$time_arr = $this->getFirstAndLast($timeArr[0], $timeArr[1]);
|
||
$user_arr = $this->getUserByTime($time_arr['begin_time'], $time_arr['end_time'], true, $sleep, $store_id, $merchant_id);
|
||
|
||
if ($user_arr['list']) {
|
||
$res = $this->getUserListByField($user_arr['list'], $time_arr['end_time'], $page, $pageSize, $sleep, $store_id, $merchant_id);
|
||
} else {
|
||
$res['status'] = 1;
|
||
$res['msg'] = '暂无数据,请稍后重试1!';
|
||
$res['data'] = [];
|
||
}
|
||
return $res;
|
||
}
|
||
|
||
/**
|
||
* @param $start_time
|
||
* @param $end_time
|
||
* @param bool $type true只返回数量
|
||
* @param bool $sleep false 沉睡会员
|
||
* @param int $store_id
|
||
* @param int $merchant_id
|
||
* @return int|mixed
|
||
*/
|
||
public function getUserByTime($start_time, $end_time, $type = false, $sleep = false, $store_id, $merchant_id)
|
||
{
|
||
if (!empty($store_id)) {
|
||
$where['store_id'] = $store_id;
|
||
}
|
||
|
||
if (!empty($merchant_id)) {
|
||
$where['merchant_id'] = $merchant_id;
|
||
}
|
||
|
||
$where['order_type'] = ['NEQ', 3];
|
||
$where['vid'] = ['gt', 0];
|
||
$where['add_time'] = ['between', [$start_time, $end_time]];
|
||
$PayOrderModel = new RetailPayOrderModel();
|
||
$payData['list'] = $PayOrderModel->getGroupFindField($where, 'vid', "vid", true);
|
||
$VipModel = new RetailVipModel();
|
||
$vipNum = $VipModel->getCount(['merchantid' => $merchant_id, 'addtime' => ['lt', $end_time]]);
|
||
|
||
if ($type) {
|
||
if ($payData['list']) {
|
||
$payData['total'] = count($payData['list']);
|
||
return $payData;
|
||
} else {
|
||
return 0;
|
||
}
|
||
} else {
|
||
if ($payData['list']) {
|
||
$payData['total'] = count($payData['list']);
|
||
if ($sleep) {
|
||
if ($vipNum - $payData['total'] > 0) {
|
||
$payData['total'] = $vipNum - $payData['total'];
|
||
} else {
|
||
$payData['total'] = 0;
|
||
}
|
||
}
|
||
return $payData['total'];
|
||
} else {
|
||
if ($sleep) {
|
||
return $vipNum;
|
||
} else {
|
||
return 0;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
// 根据条件筛选用户
|
||
function getUserListByField($user_arr, $end_time, $page, $pageSize, $sleep = false, $store_id, $merchant_id)
|
||
{
|
||
$page = $page ? $page : 1;
|
||
$pageSize = $pageSize ? $pageSize : 10;
|
||
|
||
$field = [
|
||
'id',
|
||
'name',
|
||
'nickname',
|
||
'headimgurl',
|
||
'mobile_phone',
|
||
'plus_type',
|
||
'grade_integral',
|
||
];
|
||
$where['merchantid'] = $merchant_id;
|
||
if ($sleep) {
|
||
$where['addtime'] = ['lt', $end_time];
|
||
$where['id'] = ['not in', $user_arr];
|
||
} else {
|
||
$where['id'] = ['in', $user_arr];
|
||
}
|
||
|
||
$list['list'] = $this->getSelect($where, $field, $page, $pageSize, 'id desc');
|
||
$list['total'] = $this->getCount($where);
|
||
|
||
if ($list['list']) {
|
||
foreach ($list['list'] as $key => &$value) {
|
||
$value['vip_type'] = $value['plus_type'] == 2 ? 'Plus 会员' : '常规会员';
|
||
|
||
if ($value['plus_type'] == 2) {
|
||
$value['vip_lv'] = '-';
|
||
} else {
|
||
$gradeName = M('retail_vip_integral')
|
||
->where(array('min_integral' => array('ELT', $value['grade_integral']), 'merchantid' => $merchant_id))
|
||
->order('min_integral desc')
|
||
->getField("cardname");
|
||
$value['vip_lv'] = $gradeName ? $gradeName : '无等级';
|
||
}
|
||
|
||
// 会员最后消费时间
|
||
$expense = [
|
||
'vid' => $value['id'],
|
||
'order_type' => ['NEQ', 3]
|
||
];
|
||
$time = M('retail_pay_order')->field('add_time')->where($expense)->order('id desc')->find();
|
||
if ($time['add_time']) {
|
||
$value['expense_last'] = date('Y-m-d H:i:s', $time['add_time']);
|
||
} else {
|
||
$value['expense_last'] = '-';
|
||
}
|
||
|
||
}
|
||
|
||
$res['status'] = 1;
|
||
$res['msg'] = '数据获取成功!';
|
||
$res['data'] = $list;
|
||
} else {
|
||
$res['status'] = 1;
|
||
$res['msg'] = '暂无数据,请稍后重试!';
|
||
$res['data'] = [];
|
||
}
|
||
return $res;
|
||
|
||
}
|
||
|
||
// 会员生日统计 -- 饼状图
|
||
public function getBirthUserBread($merchant_id)
|
||
{
|
||
$day_sql = "SELECT COUNT(id) AS `total`FROM `ls_retail_vip` WHERE DAY(birthday) = DAY(NOW()) AND `merchantid` = '$merchant_id' LIMIT 1";
|
||
$week_sql = "SELECT COUNT(id) AS `total`FROM `ls_retail_vip` WHERE WEEK(birthday) = WEEK(NOW()) AND `merchantid` = '$merchant_id' LIMIT 1";
|
||
$month_sql = "SELECT COUNT(id) AS `total`FROM `ls_retail_vip` WHERE MONTH(birthday) = MONTH(NOW()) AND `merchantid` = '$merchant_id' LIMIT 1";
|
||
$user[0]['name'] = '今日';
|
||
$user[0]['value'] = (int)M('retail_vip')->query($day_sql)[0]['total'];
|
||
$user[1]['name'] = '本周';
|
||
$user[1]['value'] = (int)M('retail_vip')->query($week_sql)[0]['total'];
|
||
$user[2]['name'] = '本月';
|
||
$user[2]['value'] = (int)M('retail_vip')->query($month_sql)[0]['total'];
|
||
|
||
$res['status'] = 1;
|
||
$res['msg'] = '获取成功!';
|
||
$res['data'] = $user;
|
||
return $res;
|
||
}
|
||
|
||
// 获取会员生日信息
|
||
public function getActiveUserByBirth($type, $page, $pageSize, $merchantid)
|
||
{
|
||
if ($page == 1) {
|
||
$page = 0;
|
||
$limit = $pageSize;
|
||
} else {
|
||
$limit = ($page - 1) * $pageSize + $pageSize;
|
||
$page = ($page - 1) * $pageSize;
|
||
}
|
||
|
||
if ($type == 1) {
|
||
$day_sql = "SELECT COUNT(id) AS `total`FROM `ls_retail_vip` WHERE MONTH(birthday) = MONTH(NOW()) AND DAY(birthday) = DAY(NOW()) AND `merchantid` = '$merchantid' LIMIT 1";
|
||
$day_sql_user = "SELECT `id`, `name`, `nickname`, `mobile_phone`, `plus_type`, `grade_integral`, `headimgurl`
|
||
FROM `ls_retail_vip`
|
||
WHERE DAY(birthday) = DAY(NOW()) AND `merchantid` = '$merchantid'
|
||
LIMIT " . $page . ',' . $limit;
|
||
} else if ($type == 2) {
|
||
$day_sql = "SELECT COUNT(id) AS `total`FROM `ls_retail_vip` WHERE WEEK(birthday) = WEEK(NOW()) AND `merchantid` = '$merchantid' LIMIT 1";
|
||
$day_sql_user = "SELECT `id`, `name`, `nickname`, `mobile_phone`, `plus_type`, `grade_integral`, `headimgurl`
|
||
FROM `ls_retail_vip`
|
||
WHERE WEEK(birthday) = WEEK(NOW()) AND `merchantid` = '$merchantid'
|
||
LIMIT " . $page . ',' . $limit;
|
||
} ELSE {
|
||
$day_sql = "SELECT COUNT(id) AS `total`FROM `ls_retail_vip` WHERE MONTH(birthday) = MONTH(NOW()) AND `merchantid` = '$merchantid' LIMIT 1";
|
||
$day_sql_user = "SELECT `id`, `name`, `nickname`, `mobile_phone`, `plus_type`, `grade_integral`, `headimgurl`
|
||
FROM `ls_retail_vip`
|
||
WHERE MONTH(birthday) = MONTH(NOW()) AND `merchantid` = '$merchantid'
|
||
LIMIT " . $page . ',' . $limit;
|
||
}
|
||
|
||
$user['total'] = M('retail_vip')->query($day_sql)[0]['total'];
|
||
$user['list'] = array_values(M('retail_vip')->query($day_sql_user));
|
||
if ($user['list']) {
|
||
foreach ($user['list'] as $key => &$value) {
|
||
$value['vip_type'] = $value['plus_type'] == 2 ? 'Plus 会员' : '常规会员';
|
||
|
||
if ($value['plus_type'] == 2) {
|
||
$value['vip_lv'] = '-';
|
||
} else {
|
||
$gradeName = M('retail_vip_integral')
|
||
->where(array('min_integral' => array('ELT', $value['grade_integral']), 'merchantid' => $merchantid))
|
||
->order('min_integral desc')
|
||
->getField("cardname");
|
||
$value['vip_lv'] = $gradeName ? $gradeName : '无等级';
|
||
}
|
||
|
||
$expense = [
|
||
'vid' => $value['id'],
|
||
'order_type' => ['NEQ', 3]
|
||
];
|
||
|
||
$time = M('retail_pay_order')->field('add_time')->where($expense)->order('id desc')->find();
|
||
if ($time['add_time']) {
|
||
$value['expense_last'] = date('Y-m-d H:i:s', $time['add_time']);
|
||
} else {
|
||
$value['expense_last'] = '-';
|
||
}
|
||
}
|
||
|
||
$res['status'] = 1;
|
||
$res['data'] = $user;
|
||
$res['msg'] = '数据获取成功!';
|
||
} else {
|
||
$res['status'] = 1;
|
||
$res['msg'] = '暂无数据,请稍后重试!';
|
||
$res['data'] = [];
|
||
}
|
||
return $res;
|
||
}
|
||
|
||
|
||
/* ************************************** 会员详细信息 Start ************************************** */
|
||
// 获取会员详细信息
|
||
public function getUserDetails($where, $field)
|
||
{
|
||
return M('retail_vip')->alias('ro')
|
||
->where($where)
|
||
->field($field)
|
||
->find();
|
||
}
|
||
|
||
// 获取Plus的会员时长
|
||
public function getPlusTime($where)
|
||
{
|
||
return M('retail_plus_vip')->alias('rpv')
|
||
->where($where)
|
||
->field(['rpv.start_time', 'rpv.end_time'])
|
||
->find();
|
||
}
|
||
|
||
// 获取会员余额
|
||
public function getUserRecharge($where, $field = '*', $page, $pageSize, $order = 'ro.id desc')
|
||
{
|
||
$result['list'] = M('recharge_order')->alias('ro')
|
||
->join('ls_retail_vip as rv on rv.id = ro.vid', 'LEFT')
|
||
->where($where)
|
||
->field($field)
|
||
->order($order)
|
||
->page($page, $pageSize)
|
||
->select();
|
||
|
||
$result['total'] = M('recharge_order')->alias('ro')
|
||
->join('ls_retail_vip as rv on rv.id = ro.vid', 'LEFT')
|
||
->where($where)
|
||
->field($field)
|
||
->count();
|
||
|
||
return $result;
|
||
}
|
||
|
||
// 获取会员积分
|
||
public function getUserIntegral($where, $field = '*', $page, $pageSize, $order = 'rio.id desc')
|
||
{
|
||
$result['list'] = M('retail_integral_order')->alias('rio')
|
||
->join('ls_retail_vip as rv on rv.id = rio.vid', 'LEFT')
|
||
->where($where)
|
||
->field($field)
|
||
->order($order)
|
||
->page($page, $pageSize)
|
||
->select();
|
||
|
||
$result['total'] = M('retail_integral_order')->alias('rio')
|
||
->join('ls_retail_vip as rv on rv.id = rio.vid', 'LEFT')
|
||
->where($where)
|
||
->field($field)
|
||
->count();
|
||
|
||
return $result;
|
||
}
|
||
|
||
// 获取会员订单
|
||
public function getUserBillOrder($where, $field = '*', $page, $pageSize, $order = 'rbo.id desc')
|
||
{
|
||
// ls_retail_bill_order
|
||
$result['list'] = M('retail_bill_order')->alias('rbo')
|
||
->join('ls_retail_bill_online as lrbo on rbo.id = lrbo.bill_id', 'LEFT')
|
||
->join('ls_retail_vip as rv on rv.id = rbo.vid', 'LEFT')
|
||
->where($where)
|
||
->field($field)
|
||
->order($order)
|
||
->page($page, $pageSize)
|
||
->select();
|
||
|
||
$result['total'] = M('retail_bill_order')->alias('rbo')
|
||
->join('ls_retail_bill_online as lrbo on rbo.id = lrbo.bill_id', 'LEFT')
|
||
->join('ls_retail_vip as rv on rv.id = rbo.vid', 'LEFT')
|
||
->where($where)
|
||
->field($field)
|
||
->count();
|
||
|
||
return $result;
|
||
}
|
||
|
||
// 获取Plus会员信息
|
||
public function getPlusUserRecord($where, $field = '*', $page, $pageSize, $order = 'rpc.id desc')
|
||
{
|
||
// ls_retail_plus_record
|
||
$result['list'] = M('retail_plus_record')->alias('rpc')
|
||
->join('ls_retail_bill_order as rbo on rbo.id = rpc.bill_id', 'LEFT')
|
||
->where($where)
|
||
->field($field)
|
||
->order($order)
|
||
->page($page, $pageSize)
|
||
->select();
|
||
|
||
$result['total'] = M('retail_plus_record')->alias('rpc')
|
||
->join('ls_retail_bill_order as rbo on rbo.id = rpc.bill_id', 'LEFT')
|
||
->where($where)
|
||
->field($field)
|
||
->count();
|
||
|
||
return $result;
|
||
}
|
||
|
||
|
||
/* ************************************** 公共方法Start ************************************** */
|
||
// 获取每月头尾时间戳
|
||
function getFirstAndLast($y = 2020, $m = 1)
|
||
{
|
||
if ($y == "") $y = date("Y");
|
||
if ($m == "") $m = date("m");
|
||
$m = sprintf("%02d", intval($m));
|
||
$y = str_pad(intval($y), 4, "0", STR_PAD_RIGHT);
|
||
$m > 12 || $m < 1 ? $m = 1 : $m = $m;
|
||
$firstDay = strtotime($y . $m . "01000000");
|
||
$firstDayStr = date("Y-m-01", $firstDay);
|
||
$lastDay = strtotime(date('Y-m-d 23:59:59', strtotime("$firstDayStr +1 month -1 day")));
|
||
return array("begin_time" => $firstDay, "end_time" => $lastDay);
|
||
}
|
||
|
||
//获取当前月份的前一月
|
||
function GetMonth($sign)
|
||
{
|
||
//得到系统的年月
|
||
$tmp_date = date("Ym");
|
||
//切割出年份
|
||
$tmp_year = substr($tmp_date, 0, 4);
|
||
//切割出月份
|
||
$tmp_mon = substr($tmp_date, 4, 2);
|
||
// 得到当前月份的下几月
|
||
$tmp_nextMonth = mktime(0, 0, 0, $tmp_mon + $sign, 1, $tmp_year);
|
||
// 得到当前月份的前几月
|
||
$tmp_forWardMonth = mktime(0, 0, 0, $tmp_mon - $sign, 1, $tmp_year);
|
||
return $fm_next_month = date("Y-n", $tmp_forWardMonth);
|
||
}
|
||
|
||
} |