colleague / study-project · Files

<?php
/**
 * Created by PhpStorm.
 * User: liyifei2012it
 * Date: 17/10/12
 * Time: 18:06
 */

namespace Common\Model;

class CourseStudyTimeModel extends \Com\Model
{

    // 构造方法
    public function __construct()
    {
        parent::__construct('StudyTime', 'oa_course_');
    }

    /**
     * 获取用户每个课程中最近学习的时间（产品需求变更：原来是首次学习时间）
     * @author liyifei
     *
     * @param string $uid
     * @param array $articleIds
     *
     * @return array
     */
    public function listFirstStudyTime($uid, $articleIds)
    {
        $conds = [
            'uid' => $uid,
            'article_id' => $articleIds,
        ];

        $wheres = [];
        $params = [];
        $this->_parse_where($wheres, $params, $conds);

        // 企业标记
        $wheres[] = "`{$this->prefield}domain`=?";
        $params[] = QY_DOMAIN;

        // 状态条件
        $wheres[] = "`{$this->prefield}status`<?";
        $params[] = $this->get_st_delete();

        $wheres_sql = implode(' AND ', $wheres);
        $sql = "SELECT `article_id`, MAX(`created`) `created` FROM __TABLE__ WHERE {$wheres_sql} GROUP BY `article_id`";

        return $this->_m->fetch_array($sql, $params);
    }

    /**
     * 获取用户课程学习次数
     * @author liyifei
     *
     * @param string $uid 人员UID
     * @param array $article_ids 课程ID数组
     *
     * @return array
     */
    public function listCourseStudyTotal($uid, $article_ids)
    {
        $conds = [
            'uid' => $uid,
            'article_id' => $article_ids,
        ];

        $wheres = [];
        $params = [];
        $this->_parse_where($wheres, $params, $conds);

        // 企业标记
        $wheres[] = "`{$this->prefield}domain`=?";
        $params[] = QY_DOMAIN;

        // 状态条件
        $wheres[] = "`{$this->prefield}status`<?";
        $params[] = $this->get_st_delete();

        $wheres_sql = implode(' AND ', $wheres);
        $sql = "SELECT `article_id`, count(*) total FROM __TABLE__ WHERE {$wheres_sql} GROUP BY `article_id`";

        return $this->_m->fetch_array($sql, $params);
    }

    /**
     * 批量获取人员学习时长
     * @author liyifei
     *
     * @param array $uids 人员UID数组
     * @param array $articles 常规课程id集合
     *
     * @return array
     */
    public function listStudyTimeByUids($uids, $articles = [])
    {
        $conds = [
            'uid' => $uids,

            // 课程学习时长，需要包含被删除课程的时长
            //'article_id' => $articles
        ];

        $wheres = [];
        $params = [];
        $this->_parse_where($wheres, $params, $conds);

        // 企业标记
        $wheres[] = "`{$this->prefield}domain`=?";
        $params[] = QY_DOMAIN;

        // 状态条件
        $wheres[] = "`{$this->prefield}status`<?";
        $params[] = $this->get_st_delete();

        $wheres_sql = implode(' AND ', $wheres);
        $sql = "SELECT `uid`, sum(`study_time`) `study_time` FROM __TABLE__ WHERE {$wheres_sql} GROUP BY `uid`";

        return $this->_m->fetch_array($sql, $params);
    }

    /**
     * 根据UID获取各课程学习时长
     * @author liyifei
     *
     * @param string $uid 人员UID
     * @param array $articleIds 课程ID数组
     *
     * @return array
     */
    public function listStudyTime($uid, $articleIds)
    {
        $conds = [
            'uid' => $uid,
            'article_id' => $articleIds,
        ];

        $wheres = [];
        $params = [];
        $this->_parse_where($wheres, $params, $conds);

        // 企业标记
        $wheres[] = "`{$this->prefield}domain`=?";
        $params[] = QY_DOMAIN;

        // 状态条件
        $wheres[] = "`{$this->prefield}status`<?";
        $params[] = $this->get_st_delete();

        $wheres_sql = implode(' AND ', $wheres);
        $sql = "SELECT `article_id`, sum(`study_time`) `study_time` FROM __TABLE__ WHERE {$wheres_sql} GROUP BY `article_id`";

        return $this->_m->fetch_array($sql, $params);
    }

    /**
     * 获取课程已参与学习人数排行
     * @author zhonglei
     *
     * @param array $conds 条件
     * @param int $limit 数据总数，0为取所有数据
     *
     * @return array
     */
    public function listCourseStudyRank($conds, $limit = 0)
    {
        $wheres = [];
        $params = [];

        if (is_array($conds) && !empty($conds)) {
            $this->_parse_where($wheres, $params, $conds);
        }

        // 企业标记
        $wheres[] = "`{$this->prefield}domain`=?";
        $params[] = QY_DOMAIN;
        // 状态条件
        $wheres[] = "`{$this->prefield}status`<?";
        $params[] = $this->get_st_delete();

        $wheres_sql = implode(' AND ', $wheres);
        $sql = "SELECT *, COUNT(DISTINCT(`uid`)) `total` FROM __TABLE__ WHERE {$wheres_sql} GROUP BY `article_id` ORDER BY `total` DESC";

        if ($limit > 0) {
            $sql .= " LIMIT {$limit}";
        }

        return $this->_m->fetch_array($sql, $params);
    }

    /**
     * 获取用户学习时长排行
     * @author zhonglei
     *
     * @param array $conds 条件
     * @param int $limit 数据总数，0为取所有数据
     *
     * @return array
     */
    public function listUserRank($conds, $limit = 0)
    {
        $wheres = [];
        $params = [];

        if (is_array($conds) && !empty($conds)) {
            $this->_parse_where($wheres, $params, $conds);
        }

        // 企业标记
        $wheres[] = "`{$this->prefield}domain`=?";
        $params[] = QY_DOMAIN;
        // 状态条件
        $wheres[] = "`{$this->prefield}status`<?";
        $params[] = $this->get_st_delete();

        $wheres_sql = implode(' AND ', $wheres);
        $sql = "SELECT *, SUM(`study_time`) `total` FROM __TABLE__ WHERE {$wheres_sql} GROUP BY `uid` ORDER BY `total` DESC";

        if ($limit > 0) {
            $sql .= " LIMIT {$limit}";
        }

        return $this->_m->fetch_array($sql, $params);
    }

    /**
     * 参与学习的总人数（人员去重，无论学习是否完成）
     * @author liyifei
     *
     * @param int $starttime 开始时间
     * @param int $endtime 结束时间
     * @param array $articles 常规课程id集合
     *
     * @return array
     */
    public function getStudyUserTotal($starttime, $endtime, $articles = [])
    {
        $wheres = [];
        $params = [];

        $conds = [
            'created >= ?' => $starttime,
            'created <= ?' => $endtime,
            'article_id' => $articles
        ];
        if (is_array($conds) && !empty($conds)) {
            $this->_parse_where($wheres, $params, $conds);
        }

        // 企业标记
        $wheres[] = "`{$this->prefield}domain`=?";
        $params[] = QY_DOMAIN;

        // 状态条件
        $wheres[] = "`{$this->prefield}status`<?";
        $params[] = $this->get_st_delete();

        $wheres_sql = implode(' AND ', $wheres);
        $sql = "SELECT COUNT(DISTINCT `uid`) `study_user_total` FROM __TABLE__ WHERE {$wheres_sql}";

        return $this->_m->result($sql, $params);
    }

    /**
     * 获取累计学习时长（单位：秒）
     * @author zhonglei
     *
     * @param array $conds 条件
     *
     * @return array
     */
    public function getTotalStudyTime($conds)
    {
        $wheres = [];
        $params = [];

        if (is_array($conds) && !empty($conds)) {
            $this->_parse_where($wheres, $params, $conds);
        }

        // 企业标记
        $wheres[] = "`{$this->prefield}domain`=?";
        $params[] = QY_DOMAIN;

        // 状态条件
        $wheres[] = "`{$this->prefield}status`<?";
        $params[] = $this->get_st_delete();

        $wheres_sql = implode(' AND ', $wheres);
        $sql = "SELECT SUM(`study_time`) `total` FROM __TABLE__ WHERE {$wheres_sql}";

        return $this->_m->result($sql, $params);
    }

    /**
     * 学习课程总数（所有用户累计学习的课程数；每个员工学习课程去重后分别计数，总和）
     * @author liyifei
     *
     * @param int $starttime 开始时间
     * @param int $endtime 结束时间
     * @param array $articles 常规课程id集合
     *
     * @return array
     */
    public function getUserStudyCourseTotal($starttime, $endtime, $articles = [])
    {
        $wheres = [];
        $params = [];

        $conds = [
            'created >= ?' => $starttime,
            'created <= ?' => $endtime,
            'article_id' => $articles
        ];
        if (is_array($conds) && !empty($conds)) {
            $this->_parse_where($wheres, $params, $conds);
        }

        // 企业标记
        $wheres[] = "`{$this->prefield}domain`=?";
        $params[] = QY_DOMAIN;

        // 状态条件
        $wheres[] = "`{$this->prefield}status`<?";
        $params[] = $this->get_st_delete();

        $wheres_sql = implode(' AND ', $wheres);
        $sql = "SELECT SUM(`course_total`) `total` FROM (SELECT `uid`, COUNT(DISTINCT `article_id`) `course_total` FROM __TABLE__ WHERE {$wheres_sql} GROUP BY `uid`) AS `tab_result`";

        return $this->_m->result($sql, $params);
    }

    /**
     * 学习素材总数（所有用户累计学习的素材数；每个员工学习素材去重后分别计数，总和）
     * @author liyifei
     *
     * @param int $starttime 开始时间
     * @param int $endtime 结束时间
     * @param array $articles 常规课程id集合
     *
     * @return array
     */
    public function getUserStudySourceTotal($starttime, $endtime, $articles = [])
    {
        $wheres = [];
        $params = [];

        $conds = [
            'created >= ?' => $starttime,
            'created <= ?' => $endtime,
            'source_id > ?' => 0,
            'article_id' => $articles
        ];
        if (is_array($conds) && !empty($conds)) {
            $this->_parse_where($wheres, $params, $conds);
        }

        // 企业标记
        $wheres[] = "`{$this->prefield}domain`=?";
        $params[] = QY_DOMAIN;

        // 状态条件
        $wheres[] = "`{$this->prefield}status`<?";
        $params[] = $this->get_st_delete();

        $wheres_sql = implode(' AND ', $wheres);
        $sql = "SELECT SUM(`source_total`) `total` FROM (SELECT `uid`, COUNT(DISTINCT `source_id`) `source_total` FROM __TABLE__ WHERE {$wheres_sql} GROUP BY `uid`) AS `tab_result`";

        return $this->_m->result($sql, $params);
    }

    /**
     * 多用户可学习的课程列表
     * @author liyifei
     *
     * @param array $conds 条件
     * @param array $pages 分页
     *
     * @return array
     */
    public function listUserAllowStudy($conds, $pages = [])
    {
        $wheres = [];
        $params = [];

        if (is_array($conds) && !empty($conds)) {
            $this->_parse_where($wheres, $params, $conds);
        }

        // 企业标记
        $wheres[] = "`{$this->prefield}domain` = ?";
        $params[] = QY_DOMAIN;

        // 状态条件
        $wheres[] = "`{$this->prefield}status` < ?";
        $params[] = $this->get_st_delete();

        $wheres_sql = implode(' AND ', $wheres);
        $sql = "SELECT article_id, uid, MAX(`created`) as `latest_study_time` 
                FROM __TABLE__ 
                WHERE {$wheres_sql} 
                GROUP BY uid, article_id
                ORDER BY `created` DESC";

        // 分页
        if (is_array($pages) && !empty($pages)) {
            $sql .= " LIMIT {$pages[0]}, {$pages[1]}";
        }

        return $this->_m->fetch_array($sql, $params);
    }

    /**
     * 多用户可学习的课程总数
     * @author liyifei
     *
     * @param array $conds 筛选条件
     *
     * @return mixed
     */
    public function totalUserAllowStudy($conds)
    {
        $wheres = [];
        $params = [];

        if (is_array($conds) && !empty($conds)) {
            $this->_parse_where($wheres, $params, $conds);
        }

        // 企业标记
        $wheres[] = "`{$this->prefield}domain` = ?";
        $params[] = QY_DOMAIN;

        // 状态条件
        $wheres[] = "`{$this->prefield}status` < ?";
        $params[] = $this->get_st_delete();

        $wheres_sql = implode(' AND ', $wheres);
        $sql = "SELECT COUNT(*) FROM (SELECT article_id, uid FROM __TABLE__ 
                WHERE {$wheres_sql}
                GROUP BY uid, article_id) AS res_tab";

        return $this->_m->result($sql, $params);
    }

    /**
     * 多用户未完成的课程列表
     * @author liyifei
     *
     * @param array $conds 筛选条件
     *      + array uids 人员UID数组
     *      + array article_ids 课程ID数组
     * @param array $pages 分页
     *
     * @return array
     */
    public function listUserUnCompleteCourse($conds, $pages = [])
    {
        $domain = QY_DOMAIN;
        $status = $this->get_st_delete();

        // 人员条件
        $where_uids = '';
        if (is_array($conds['uids']) && !empty($conds['uids'])) {
            $uids = implode("','", $conds['uids']);
            $where_uids = " AND st.`uid` IN ('{$uids}') ";
        }

        // 课程条件
        $where_aids = '';
        if (is_array($conds['article_ids']) && !empty($conds['article_ids'])) {
            $article_ids = implode(",", $conds['article_ids']);
            $where_aids = " AND st.`article_id` IN ({$article_ids}) ";
        }

        $sql = "SELECT st.`uid`, st.`article_id`, MAX(st.`created`) AS `latest_study_time`, cc.`created` AS `complete_time`
                FROM __TABLE__ AS st LEFT JOIN `oa_course_complete` AS cc ON st.`article_id`=cc.`article_id` AND st.`uid`=cc.`uid` 
                WHERE st.`domain` = '{$domain}' AND st.`status` < {$status}  
                AND cc.`created` IS NULL 
                {$where_uids}
                {$where_aids}
                GROUP BY st.`uid`, st.`article_id` ORDER BY `latest_study_time` DESC";

        // 分页
        if (is_array($pages) && !empty($pages)) {
            $sql .= " LIMIT {$pages[0]}, {$pages[1]}";
        }

        return $this->_m->fetch_array($sql);
    }

    /**
     * 多用户未完成的课程总数
     *
     * @param array $conds 筛选条件
     *      + array uids 人员UID数组
     *      + array article_ids 课程ID数组
     *
     * @return mixed
     */
    public function countUserUnCompleteCourse($conds)
    {
        // 人员条件
        $where_uids = '';
        if (is_array($conds['uids']) && !empty($conds['uids'])) {
            $uids = implode("','", $conds['uids']);
            $where_uids = " AND st.`uid` IN ('{$uids}') ";
        }

        // 课程条件
        $where_aids = '';
        if (is_array($conds['article_ids']) && !empty($conds['article_ids'])) {
            $article_ids = implode(",", $conds['article_ids']);
            $where_aids = " AND st.`article_id` IN ({$article_ids}) ";
        }

        $domain = QY_DOMAIN;
        $status = $this->get_st_delete();

        $sql = "SELECT COUNT(*) FROM (SELECT st.`uid`, st.`article_id`, MAX(st.`created`) AS `latest_study_time`
                FROM __TABLE__ AS st LEFT JOIN `oa_course_complete` AS cc ON st.`article_id`=cc.`article_id` AND st.`uid`=cc.`uid` 
                WHERE st.`domain` = '{$domain}' AND st.`status` < {$status}  
                AND cc.`created` IS NULL 
                {$where_uids}
                {$where_aids}
                GROUP BY st.`uid`, st.`article_id`) AS res_tab";

        return $this->_m->result($sql);
    }

    /**
     * 根据人员ID、课程ID数组，获取该人员最近学习情况
     *
     * @param array $datas 人员ID、课程ID数组
     *      + string $datas[]['uid']
     *      + string $datas[]['article_id']
     *
     * @return array
     */
    public function listUserStudy($datas)
    {
        $domain = QY_DOMAIN;
        $status = $this->get_st_delete();
        $where = "`{$this->prefield}domain`='{$domain}' AND `{$this->prefield}status`<{$status} AND";
        foreach ($datas as $data) {
            $where .= " (`article_id` = {$data['article_id']} AND `uid` = '{$data['uid']}') OR";
        }
        $where = substr($where, 0, -3);

        $sql = "SELECT `article_id`, `uid`, MAX(`created`) AS `latest_study_time` FROM __TABLE__ WHERE {$where}";

        return $this->_m->fetch_array($sql);
    }

    /**
     * 时间区间内，学习人数（按人员去重）、学习次数（按人员不去重）
     * @author liyifei
     *
     * @param int $starttime 开始时间
     * @param int $endtime 结束时间
     * @param int $is_unique 人员是否去重（1=否；2=是）
     * @param array $articles 常规课程id集合
     *
     * @return array
     */
    public function listStudyUserTotal($starttime, $endtime, $is_unique, $articles = [])
    {
        $wheres = [];
        $params = [];

        $conds = [
            'created >= ?' => $starttime,
            'created < ?' => $endtime,
            'article_id' => $articles,
        ];
        if (is_array($conds) && !empty($conds)) {
            $this->_parse_where($wheres, $params, $conds);
        }

        // 企业标记
        $wheres[] = "`{$this->prefield}domain`=?";
        $params[] = QY_DOMAIN;

        // 状态条件
        $wheres[] = "`{$this->prefield}status`<?";
        $params[] = $this->get_st_delete();

        $wheres_sql = implode(' AND ', $wheres);

        // 待搜索数据
        $distinct = $is_unique == 2 ? 'DISTINCT' : '';

        $sql = "SELECT FROM_UNIXTIME(`created`/1000, '%Y-%m-%d %H:00:00') AS `hours`, COUNT({$distinct} `uid`) as `total` 
                FROM `oa_course_study_time`
                WHERE {$wheres_sql}
                GROUP BY `hours`";

        return $this->_m->fetch_array($sql, $params);
    }

    /**
     * 时间区间内，人员学习次数（1小时内访问2次同个课程时，按1次计算；访问2个课程时，则按2次计算）
     * @author liyifei
     *
     * @param int $starttime 开始时间
     * @param int $endtime 结束时间
     * @param array $articles 常规课程id集合
     *
     * @return array
     */
    public function listStudyUserCount($starttime, $endtime, $articles = [])
    {
        $wheres = [];
        $params = [];

        $conds = [
            'created >= ?' => $starttime,
            'created < ?' => $endtime,
            'article_id' => $articles,
        ];
        if (is_array($conds) && !empty($conds)) {
            $this->_parse_where($wheres, $params, $conds);
        }

        // 企业标记
        $wheres[] = "`{$this->prefield}domain`=?";
        $params[] = QY_DOMAIN;

        // 状态条件
        $wheres[] = "`{$this->prefield}status`<?";
        $params[] = $this->get_st_delete();

        $wheres_sql = implode(' AND ', $wheres);

        $sql = "SELECT `hours`, SUM(`course_total`) `total` FROM (
                SELECT FROM_UNIXTIME(`created`/1000, '%Y-%m-%d %H:00:00') AS `hours`, COUNT(DISTINCT `article_id`) as `course_total`, `uid`
                FROM `oa_course_study_time`
                WHERE {$wheres_sql}
                GROUP BY `hours`, `uid`) AS res_tab GROUP BY `hours`";

        return $this->_m->fetch_array($sql, $params);
    }

    /**
     * 时间区间内，每日学习时长
     * @author liyifei
     *
     * @param int $starttime 开始时间
     * @param int $endtime 结束时间
     * @param array $articles 常规课程id集合
     *
     * @return array
     */
    public function listStudyTimeTotalByDays($starttime, $endtime, $articles = [])
    {
        $wheres = [];
        $params = [];

        $conds = [
            'created >= ?' => $starttime,
            'created <= ?' => $endtime,
            'article_id' => $articles,
        ];
        if (is_array($conds) && !empty($conds)) {
            $this->_parse_where($wheres, $params, $conds);
        }

        // 企业标记
        $wheres[] = "`{$this->prefield}domain`=?";
        $params[] = QY_DOMAIN;

        // 状态条件
        $wheres[] = "`{$this->prefield}status`<?";
        $params[] = $this->get_st_delete();

        $wheres_sql = implode(' AND ', $wheres);

        $sql = "SELECT FROM_UNIXTIME(`created`/1000, '%Y-%m-%d') AS `days`, SUM(`study_time`) `total`
                FROM `oa_course_study_time`
                WHERE {$wheres_sql}
                GROUP BY `days`";

        return $this->_m->fetch_array($sql, $params);
    }
}