watercat_web/application/index/model/userconnectrecorddaymodel.php

<?php
/*
 * @Author: wang jun
 * @Date: 2021-09-28 11:13:58
 * @Last Modified by: wang jun
 * @Last Modified time: 2021-12-24 16:49:03
 */
namespace app\index\model;

use think\Config;
use think\Db;
use think\Log;
use think\Model;

class userconnectrecorddaymodel
{
    protected $tableBase = "t_userconnectrecord";
    protected $db;
    public $isCreateFirst; //是否第一次创建
    public function __construct()
    {
        $connection = Config::get('database');
        $this->db = Db::connect($connection);
        $this->table = $this->tableBase . "_" . date("Ymd");
        $this->createTableProcess();
    }
    private function createTableProcess()
    {
        $isHasTable = $this->hasTable();
        if (!$isHasTable) {
            $this->createTable();
        } else {
            $this->isCreateFirst = false;
        }
    }
    /**
     * 构造校验表是否存在
     * 20212116
     * wj
     * */
    private function hasTable()
    {
        $sessionTable = session($this->tableBase, '', 'submeter');
        $isCheck = false;
        if ($sessionTable) {
            if (date("Ymd") != substr($sessionTable, mb_strlen($sessionTable), -8)) {
                $isCheck = true;
            }
        } else {
            $isCheck = true;
        }
        if (!$isCheck) {
            return true;
        }
        session(null, 'submeter');
        $sql = "show Table STATUS where Name='" . $this->table . "'";
        $data = $this->db->query($sql);
        $count = count($data);
        if ($count <= 0) {
            return false;
        }
        session($this->tableBase, $this->table, 'submeter');
        return true;
    }
    private function createTable()
    {
        $sql = "show create table " . $this->tableBase;
        $data = $this->db->query($sql);
        $createData = $data[0]['Create Table'];
        $createData = str_replace("`" . $this->tableBase . "`", "`" . $this->table . "`", $createData);
        $this->db->query($createData);
        log::info($this->table . " create");
        session($this->tableBase, $this->table, 'submeter');
        $this->isCreateFirst = true;
    }
    /***
     * 设置表名
     * 20211217
     * wj
     */
    public function setTableName($daystr)
    {
        if (empty($daystr) || !is_string($daystr)) {
            return false;
        }
        $isUse = false;
        if (isDate($daystr, 'Ymd')) {
            $isUse = true;
        }
        if (!$isUse) {
            return false;
        }
        $tableName = $this->tableBase . "_" . $daystr;
        $this->table = $tableName;
        session($this->tableBase, $this->table, 'submeter');
        log::info($this->table . " use");
        return true;
    }
    /***
     * 判断表是否已存在
     * 20211217
     * wj
     */
    public function isexist($daystr)
    {
        if (!isDate($daystr, 'Ymd')) {
            return false;
        }
        $tableName = $this->tableBase . "_" . $daystr;
        $sql = "show Table STATUS where Name='" . $tableName . "'";
        $data = $this->db->query($sql);
        $count = count($data);
        if ($count <= 0) {
            return false;
        }
        return true;
    }
    /***
     * 插入数据
     * 20211217
     * wj
     */
    public function insinfo($arr)
    {
        log::info($this->table . " insert");
        if (!isset($arr['calltime']) || empty($arr['calltime'])) {
            $arr['calltime'] = date("Y-m-d H:i:s");
        }
        $id = $this->db->name($this->table)->insertGetId($arr);
        return $id;
    }
    /*
     * 202112017
     * wj
     * 获得联系电话列表
     */
    public function getconnectlist($where, $count = false, $page = 1, $size = 10)
    {
        $sql = "select * from (
            select uc.id,uc.senduserid,us.wname as swname,us.id as sid,us.telno as stelno,uc.reciveruserid,ur.id as rid,ur.wname as rwname,ur.telno as rtelno,i.code,i.id as iid,i.worktype,uc.calltime
            from " . $this->table . " as uc
            join t_invent as i on uc.sourceid=i.id
            join t_userinfo as us on uc.senduserid=us.id
            join t_userinfo as ur on uc.reciveruserid=ur.id
            ORDER BY uc.calltime desc)
            as tab ";
        $tabsql = "select * from (" . $sql . ") as tab";
        $usewhere = [];
        //打电话人
        if (isset($where['swname']) && !empty($where['swname']) && is_string($where['swname'])) {
            $usewhere[] = "swname like '%" . $where['swname'] . "%'";
        }
        //拨打电话
        if (isset($where['stelno']) && !empty($where['stelno']) && is_string($where['stelno'])) {
            $usewhere[] = "stelno like '%" . $where['stelno'] . "%'";
        }
        //被联系人
        if (isset($where['rwname']) && !empty($where['rwname']) && is_string($where['rwname'])) {
            $usewhere[] = "rwname like '%" . $where['rwname'] . "%'";
        }
        //被联系电话
        if (isset($where['rtelno']) && !empty($where['rtelno']) && is_string($where['rtelno'])) {
            $usewhere[] = "rtelno like '%" . $where['rtelno'] . "%'";
        }
        //招工code
        if (isset($where['code']) && !empty($where['code']) && is_string($where['code'])) {
            $usewhere[] = "code like '%" . $where['code'] . "%'";
        }
        //打电话时间段
        if (isset($where['calltime']) && !empty($where['calltime']) && is_array($where['calltime'])) {
            $usewhere[] = "calltime >= '" . $where['calltime'][0] . "'";
            $usewhere[] = "calltime < '" . $where['calltime'][1] . "'";
        }
        //工作类型
        if (isset($where['worktype']) && !empty($where['worktype']) && is_string($where['worktype'])) {
            $usewhere[] = "worktype like '%" . $where['worktype'] . "%'";
        }
        if (!empty($usewhere)) {
            $tabwhere = implode(' and ', $usewhere);
            $tabsql .= ' where ' . $tabwhere;
        }
        if (!$count) {
            $index = ($page - 1) * $size;
            $tabsql .= " limit " . $index . "," . $size;
        }
        if ($count) {
            $list = $this->db->query("select count(*) count from (" . $tabsql . ") as t");
        } else {
            $list = $this->db->query("select * from (" . $tabsql . ") as t");
        }
        return $list;
    }
    /*
     * 20211220
     * wj
     * 统计联系电话的时间段
     */
    public function getconnecttimebucket($where)
    {
        $tabsql = "select DATE_FORMAT(calltime,'%H') as hour,count(*) count from " . $this->table . " as uc join t_invent as i on uc.sourceid=i.id group by hour ORDER BY hour asc";
        $list = $this->db->query("select * from (" . $tabsql . ") as t");
        return $list;
    }
    /*
     * 20211222
     * wj
     * 统计联系电话联系数量
     */
    public function getconnectbyinvent($where, $count = false, $page = 1, $size = 10)
    {
        #i.isactive=1 and
        $tabsql = "select uc.sourceid,i.id,i.createdate,i.code,i.info,i.isactive,i.ispass,count(uc.id) count
        from " . $this->table . " as uc
        join t_invent as i on uc.sourceid=i.id where i.ispass=1
        GROUP BY uc.sourceid ORDER BY i.id desc";
        if ($count) {
            $list = $this->db->query("select count(*) count from (" . $tabsql . ") as t");
            $count = $list[0]['count'];
            return $count;
        } else {
            $index = ($page - 1) * $size;
            $list = $this->db->query("select * from (" . $tabsql . ") as t limit " . $index . "," . $size);
        }
        return $list;
    }
}