watercat_web/application/index/model/userconnectrecordmodel.php

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

use think\Model;

class userconnectrecordmodel extends Model
{
    protected $table = 't_userconnectrecord';

    public function insinfo($arr)
    {
        if (!isset($arr['calltime']) || empty($arr['calltime'])) {
            $arr['calltime'] = date("Y-m-d H:i:s");
        }
        $id = $this->insertGetId($arr);
        return $id;
    }

    /***
     * 校验数据是否可添加
     * 20211215
     * wj
     */
    public function checkDataForInsert($arr)
    {
        $where = [
            'senduserid' => $arr['senduserid'],
            'calltime' => ['like', '%' . date('Y-m-d') . '%'],
        ];
        $row = $this->where($where)->count();
        if ($row > 0) {
            return backarr(0, "数据已存在");
        }
        return backarr(1, "数据可新增");
    }
    /*
     * 20211129
     * steelxu5
     * 根据主动联系人的用户id，返回联系数据
     * todo 当联系数据多时，后期需要改成按起始日期统计，分步加载,
     */
    public function seltotalbyuserid($userid)
    {
        $strsql = 'select substr(calltime,1,10) as calldate,count(id) as connectcount,sum(senderevaluate) as evaluatecount';
        $strsql = $strsql . " from t_userconnectrecord where senduserid=" . $userid;
        $strsql = $strsql . " group by substr(calltime,1,10)";
        $rlist = $this->query($strsql);
        $rlist = collection($rlist)->toArray();
        return $rlist;
    }

    /*
     * 20211130
     * steelxu5
     * 获取某一天的，某人的联系记录
     */
    public function selinfolistbyuiddate($userid, $date)
    {
        $strsql = 'select *';
        $strsql = $strsql . " from t_userconnectrecord where senduserid=" . $userid . " and calltime like '" . $date . "%' ";
        $rlist = $this->query($strsql);
        $rlist = collection($rlist)->toArray();
        return $rlist;
    }

    /*
     * 20211201
     * steelxu5
     * 更新评价状态
     */
    public function updatesenderevaluebysid($sourceid, $sourcetype)
    {
        $where_arr['sourcetype'] = $sourcetype;
        $where_arr['sourceid'] = $sourceid;
        $update_arr['senderevaluate'] = 1;
        $count = $this->where($where_arr)->update($update_arr);
        return $count;
    }

    /*
     * 20211209
     * wj
     * 获得联系电话列表
     */
    public function getconnectlist($where, $count = false, $page = 1, $size = 10)
    {
        $sql = "
            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 t_userconnectrecord 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";
        $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['callday']) && !empty($where['callday']) && is_string($where['callday'])) {
            $where['callday'] = date('Y-m-d', strtotime($where['callday']));
            $usewhere[] = "calltime like '%" . $where['callday'] . "%'";
        }
        //工作类型
        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->query("select count(*) count from (" . $tabsql . ") as t");
        } else {
            $list = $this->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 t_userconnectrecord as uc join t_invent as i on uc.sourceid=i.id";
        $group = " group by hour ORDER BY hour asc";
        $usewhere = [];
        if (isset($where['callday']) && !empty($where['callday']) && is_string($where['callday'])) {
            $where['callday'] = date('Y-m-d', strtotime($where['callday']));
            $usewhere[] = "calltime like '%" . $where['callday'] . "%'";
        } else {
            $dayStr = date('Y-m-d');
            $usewhere[] = "calltime like '%" . $dayStr . "%'";
        }
        if (!empty($usewhere)) {
            $tabwhere = implode(' and ', $usewhere);
            $tabsql .= ' where ' . $tabwhere;
        }
        $tabsql .= $group;
        $list = $this->query("select * from (" . $tabsql . ") as t");
        return $list;
    }
    /*
     * 20211222
     * wj
     * 统计联系电话联系数量
     */
    public function getconnectbyinvent($where, $count = false, $page = 1, $size = 10)
    {
        $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";
        $group = "  GROUP BY uc.sourceid ORDER BY i.id desc";
        $usewhere = [
            #'i.isactive' => 1,
            'i.ispass' => 1,
        ];
        if (isset($where['callday']) && !empty($where['callday']) && is_string($where['callday'])) {
            $where['callday'] = date('Y-m-d', strtotime($where['callday']));
            $usewhere[] = "calltime like '%" . $where['callday'] . "%'";
        } else {
            $dayStr = date('Y-m-d');
            $usewhere[] = "calltime like '%" . $dayStr . "%'";
        }
        if (!empty($usewhere)) {
            $tabwhere = implode(' and ', $usewhere);
            $tabsql .= ' where ' . $tabwhere;
        }
        $tabsql .= $group;
        if ($count) {
            $list = $this->query("select count(*) count from (" . $tabsql . ") as t");
            $count = $list[0]['count'];
            return $count;
        } else {
            $index = ($page - 1) * $size + 1;
            $list = $this->query("select * from (" . $tabsql . ") as t limit " . $index . "," . $size);
        }
        return $list;
    }
}