watercat_web/application/index/model/useraccountuserrecordmodel.php

<?php
/*
 * @Author: wang jun
 * @Date: 2021-09-27 08:39:30
 * @Last Modified by: wang jun
 * @Last Modified time: 2021-12-29 16:39:24
 */
namespace app\index\model;

use think\Model;

class useraccountuserrecordmodel extends Model
{
    protected $table = 't_useraccountuserrecord';
    public function insertData($data)
    {
        $id = $this->insertGetId($data);
        return empty($id) ? false : $id;
    }

    /***
     * 校验数据是否可添加
     * 20211215
     * wj
     * 20211216 废弃
     */
    /*public function checkDataForInsert($arr)
    {
        $where = [
            'userid' => $arr['userid'],
            'usetime' => ['like', '%' . date('Y-m-d') . '%'],
        ];
        $row = $this->where($where)->count();
        if ($row > 0) {
            return backarr(0, "数据已存在");
        }
        return backarr(1, "数据可新增");
    }*/
    public function getInfo($where, $field = "*", $row = true)
    {
        $info = $this->field($field)->where($where);
        if ($row) {
            $info = $info->find();
        } else {
            $info = $info->select();
        }
        return empty($info) ? false : $info;
    }
    public function updateinfo($where, $updateData)
    {
        $row = $this->where($where)->update($updateData);
        return empty($row) ? false : $row;
    }
    public function deleteinfo($where)
    {
        $row = $this->where($where)->delete();
        return empty($row) ? false : $row;
    }
    public function getList($where = [], $field = "*", $page = 1, $size = 10, $order = "id desc", $group = "", $row = false)
    {
        $sqlObj = $this->where($where);
        if ("count" != $field) {
            $sqlObj = $sqlObj->field($field)->order($order)->group($group)->page($page, $size);
            if ($row) {
                $data = $sqlObj->find();
            } else {
                $data = $sqlObj->select();
            }
        } else {
            $data = $sqlObj = $sqlObj->count();
        }
        return $data;
    }

    /***
     * 获取用户消费的积分数 银积分 通过类型
     * type 1 昨天 2今天
     * wj
     * 20211130
     */
    public function getsumscorebytype($type, $minsocre = 5, $count = false, $page = 1, $size = 10, $order = "id desc")
    {
        $field = 'u.id,u.wname,u.telno,sum(us.usescore) as sum, sum(us.usescore)-' . $minsocre . ' as givescore,tab2.givesum';
        $sql = "select " . $field . "
        from t_useraccountuserrecord as us
        join t_userinfo as u on us.userid=u.id ";
        $sql2 = "select userid,sum(addscore) as givesum from t_useraccountaddrecord
        where sourcetype=2 and actiontype=5 and createdate>=DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00')
        GROUP BY userid";
        $sql .= " left join (" . $sql2 . ") as tab2 on tab2.userid=us.userid where ";
        $date = '';
        switch ($type) {
            case 1:
                //昨天
                $date = "us.usetime >=DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00'),interval 1 day) and us.usetime <DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00')";
                break;
            case 2:
                //今天
                $date = "us.usetime >=DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00')";
                break;
        }
        if (empty($date)) {
            return false;
        }
        $sql .= $date;
        $sql .= " group by us.userid HAVING sum(us.usescore)>=" . $minsocre;
        if ($count) {
            $sql = "select count(*) as count from ( " . $sql . " ) as tab";
        } else {
            $index = ($page - 1) * $size;
            $sql = "select * from ( " . $sql . " ) as tab order by " . $order . " limit " . $index . "," . $size;
        }
        $data = $this->query($sql);
        if ($count) {
            $data = $data[0]['count'];
        }
        return $data;
    }

    /***
     * 获取用户消费的积分数 银积分 通过查询
     * time 数组
     * wj
     * 20211207
     */
    public function getsumscorebywhere($where, $minsocre = 5, $count = false, $page = 1, $size = 10, $order = "id desc")
    {
        $field = 'u.id,u.wname,u.telno,sum(us.usescore) as sum, sum(us.usescore)-' . $minsocre . ' as givescore,tab2.givesum,tab3.sumscore';
        $sql = "select " . $field . "
        from t_useraccountuserrecord as us
        join t_userinfo as u on us.userid=u.id ";
        $sql2 = "select userid,sum(addscore) as givesum from t_useraccountaddrecord
        where sourcetype=2 and actiontype=5 and createdate>=DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00')
        GROUP BY userid";
        $sql3 = "select  userid,goldscore+siliverscore as sumscore from t_useraccount";
        $sql .= " left join (" . $sql2 . ") as tab2 on tab2.userid=us.userid left join (" . $sql3 . ") as tab3 on tab3.userid=us.userid ";
        $useWhere = [];
        if (isset($where['time']) && is_array($where['time'])) {
            $useWhere[] = "us.usetime >=from_unixtime(unix_timestamp('" . $where['time'][0] . "'), '%Y-%m-%d %H:%i:%s') and us.usetime < from_unixtime(unix_timestamp('" . $where['time'][1] . "'), '%Y-%m-%d %H:%i:%s') ";
        }
        if (isset($where['wname']) && !empty($where['wname']) && is_string($where['wname'])) {
            $useWhere[] = "u.wname like '%" . $where['wname'] . "%'";
        }
        if (isset($where['telno']) && !empty($where['telno']) && is_string($where['telno'])) {
            $useWhere[] = "u.telno like '%" . $where['telno'] . "%'";
        }
        if (!empty($useWhere)) {
            $sql .= 'where ' . implode(' and ', $useWhere);
        }
        $sql .= " group by us.userid HAVING sum(us.usescore)>=" . $minsocre;
        if ($count) {
            $sql = "select count(*) as count from ( " . $sql . " ) as tab";
        } else {
            $index = ($page - 1) * $size;
            $sql = "select * from ( " . $sql . " ) as tab order by " . $order . " limit " . $index . "," . $size;
        }
        $data = $this->query($sql);
        if ($count) {
            $data = $data[0]['count'];
        }
        return $data;
    }
}