watercat_web/application/index/model/statisticssqlmodel.php

<?php
/**
 * Created by PhpStorm.
 * User: sicilon_IT
 * Date: 2021/10/7
 * Time: 22:55
 */
namespace app\index\model;

use app\index\model\userconnectrecorddaymodel;
use think\Model;

class statisticssqlmodel extends Model
{
    /**
     * 获取电话统计数据
     * 20211206
     * wj
     */
    public function getTelnoConnectNum()
    {
        $sqlhavetime = "select DATE_FORMAT(calltime,'%Y-%m-%d %H:00:00') date,count(*) count from t_userconnectrecord
        where calltime>=DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00'),interval 1 day)
        and calltime<DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00'),interval 1 day)
        GROUP BY DATE_FORMAT(calltime,'%Y-%m-%d  %H:00:00')
        ORDER BY date desc";
        //暂时不用
        //$timelist = $this->query("select * from (" . $sqlhavetime . ") as t");
        $sqlcount = "select DATE_FORMAT(calltime,'%Y-%m-%d') date,count(*) count from t_userconnectrecord
        where calltime>=DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00'),interval 1 day)
        and calltime<DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00'),interval 1 day)
        GROUP BY DATE_FORMAT(calltime,'%Y-%m-%d')
        ORDER BY date desc";
        $countlist = $this->query("select * from (" . $sqlcount . ") as t");
        $data = ['countlist' => $countlist];
        return $data;
    }
    /**
     * 获取无代发数据
     * 20211206
     * wj
     */
    public function getinventcount1()
    {
        $sql = "select DATE_FORMAT(createdate,'%Y-%m-%d') date,isactive,ispass,count(*) count from t_invent
        where createdate>=DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00'),interval 1 day)
        and createdate<DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00'),interval 1 day)
        and releaseid is NULL
        GROUP BY DATE_FORMAT(createdate,'%Y-%m-%d'),isactive,ispass";
        $list = $this->query("select * from (" . $sql . ") as t");
        return $list;
    }

    /***
     * 各招工电话联系 联系数量 列表
     * 20211206
     * wj
     */
    public function getinventitemtelnoconnectlist($count = false, $page = 1, $size = 10)
    {
        $sql = "select uc.sourceid,i.code,u.telno,count(*) count from t_userconnectrecord as uc
        join t_invent as i on i.id=uc.sourceid
        join t_userinfo as u on u.id=i.createuid
        where uc.sourcetype=1
        GROUP BY uc.sourceid
        ORDER BY count desc";
        if (!$count) {
            $index = ($page - 1) * $size;
            $sql .= " limit " . $index . "," . $size;
        }
        if ($count) {
            $list = $this->query("select count(*) count from (" . $sql . ") as t");
        } else {
            $list = $this->query("select * from (" . $sql . ") as t");
        }
        return $list;
    }
    /***
     * 各招工电话联系 联系数量 列表 今日
     * 20211220
     * wj
     */
    public function getinventitemtelnoconnectbydaylist($count = false, $page = 1, $size = 10)
    {
        $m_ucrd = new userconnectrecorddaymodel();
        $day = date("Ymd");
        $isExist = $m_ucrd->isexist($day);
        if (!$isExist) {
            return false;
        }
        $tablename = 't_userconnectrecord_' . $value;
        $sql = "select uc.sourceid,i.code,u.telno,count(*) count from " . $tablename . " as uc
        join t_invent as i on i.id=uc.sourceid
        join t_userinfo as u on u.id=i.createuid
        where uc.sourcetype=1
        GROUP BY uc.sourceid
        ORDER BY count desc";
        if (!$count) {
            $index = ($page - 1) * $size;
            $sql .= " limit " . $index . "," . $size;
        }
        if ($count) {
            $list = $this->query("select count(*) count from (" . $sql . ") as t");
        } else {
            $list = $this->query("select * from (" . $sql . ") as t");
        }
        return $list;
    }
    /***
     * 近两日 打电话数量及人数
     * 20211220
     * wj
     */
    public function getinventcount2()
    {
        $m_ucrd = new userconnectrecorddaymodel();
        $day = [
            date("Ymd"),
            date("Ymd", strtotime("-1 day")),
        ];
        $list = [];
        foreach ($day as $key => $value) {
            $valueDay = date('Y-m-d', strtotime($value));
            $isExist = $m_ucrd->isexist($value);
            if (!$isExist) {
                $list[] = ['date' => $valueDay, 'count_suid' => 0, 'total' => 0];
                continue;
            }
            $tablename = 't_userconnectrecord_' . $value;
            $sql = "select count(senduserid) count_suid,sum(count) total from (
                select senduserid,count(*) count from " . $tablename . " GROUP BY senduserid
                ) as t";
            $data = $this->query($sql);
            $item = $data[0];
            $item['count_suid'] = empty($item['count_suid']) ? 0 : $item['count_suid'];
            $item['total'] = empty($item['total']) ? 0 : $item['total'];
            $item['date'] = $valueDay;
            $list[] = $item;
        }
        return $list;
    }

    /***
     * 各招工电话联系 间隔时间 列表
     * 20211206
     * wj
     */
    public function getinventitemtelnointervallist($count = false, $page = 1, $size = 10)
    {
        $sql = "select * from (
            select ucid,sourceid,code,telno,time
            from (
               select uc.id ucid,uc.sourceid,i.code,i.createdate,uc.calltime,ROUND((UNIX_TIMESTAMP(uc.calltime)-UNIX_TIMESTAMP(i.passtime))/60,0) time,u.telno
               from t_userconnectrecord as uc
               join t_invent as i on i.id=uc.sourceid
               join t_userinfo as u on u.id=i.createuid
                 where passtime is not null
               ORDER BY time asc
            ) as tab
            GROUP BY sourceid
            ) as tab ORDER BY time asc";
        if (!$count) {
            $index = ($page - 1) * $size;
            $sql .= " limit " . $index . "," . $size;
        }
        if ($count) {
            $list = $this->query("select count(*) count from (" . $sql . ") as t");
        } else {
            $list = $this->query("select * from (" . $sql . ") as t");
        }
        return $list;
    }
    /***
     * 各招工电话联系 间隔时间 列表 今日  平均值
     * 20211220
     * wj
     */
    public function getinventitemtelnoavgbydaylist()
    {
        $m_ucrd = new userconnectrecorddaymodel();
        $day = [
            date("Ymd"),
            date("Ymd", strtotime("-1 day")),
        ];
        $list = [];
        foreach ($day as $key => $value) {
            $valueDay = date('Y-m-d', strtotime($value));
            $isExist = $m_ucrd->isexist($value);
            if (!$isExist) {
                $list[] = ['avg_time' => 0, 'date' => $valueDay];
                continue;
            }
            $tablename = 't_userconnectrecord_' . $value;
            $sql = "select avg(time) avg_time from (
                select ucid,sourceid,code,telno,time
                from (
                    select uc.id ucid,uc.sourceid,i.code,i.createdate,uc.calltime,ROUND((UNIX_TIMESTAMP(uc.calltime)-UNIX_TIMESTAMP(i.passtime))/60,0) time,u.telno
                    from " . $tablename . " as uc
                    join t_invent as i on i.id=uc.sourceid
                    join t_userinfo as u on u.id=i.createuid
                        where passtime is not null and passtime like '%" . $valueDay . "%'
                    ORDER BY time asc
                ) as tab
                GROUP BY sourceid
                ) as tab";
            $data = $this->query($sql);
            if (empty($data)) {
                $item['avg_time'] = 0;
            } else {
                $item['avg_time'] = empty($data[0]['avg_time']) ? 0 : $data[0]['avg_time'];
            }
            $item['date'] = $valueDay;
            $list[] = $item;
        }
        return $list;
    }

    /***
     * 招工 已招满
     * 20211206
     * wj
     */
    public function getinventfull()
    {
        $sql = "select count(*) count from t_evaluate where evaluatememo like '%已招满%'";
        $list = $this->query("select * from (" . $sql . ") as t");
        return $list;
    }

    /***
     * 招工没接电话
     * 20211206
     * wj
     */
    public function getinventnotanswer()
    {
        $sql = "select count(*) count from t_evaluate where evaluatememo like '%没接电话%'";
        $list = $this->query("select * from (" . $sql . ") as t");
        return $list;
    }

    /***
     * 统计用户积分
     * 20211206
     * wj
     */
    public function getuseraccount($count = false, $page = 1, $size = 10)
    {
        $sql = "select u.id,u.wname,goldscore,siliverscore,goldscore+siliverscore sumscore
        from t_useraccount as ua
        join (
           select senduserid from t_userconnectrecord GROUP BY senduserid
        ) as uc on uc.senduserid = ua.userid
        join t_userinfo as u on u.id = ua.userid ORDER BY goldscore,siliverscore";
        if (!$count) {
            $index = ($page - 1) * $size;
            $sql .= " limit " . $index . "," . $size;
        }
        if ($count) {
            $list = $this->query("select count(*) count from (" . $sql . ") as t");
        } else {
            $list = $this->query("select * from (" . $sql . ") as t");
        }
        return $list;
    }
}