PHP F::db方法代码示例

 /**
  * 生成招聘信息报表
  *
  * @access public
  * @param int $startTime
  * @param int $endTime
  * @return array
  */
 public function recruitment($startTime, $endTime)
 {
     #获取时间段内产生的招聘信息
     $userList = F::db()->query('select user_id from user_tag where tag_id = ? and bind_time between ? and ?', array(self::CANdDIDATE_TAG_ID, date('Y-m-d H:i:s', $startTime), date('Y-m-d H:i:s', $endTime)))->fetch_column_all('user_id');
     #计算参加初试人数/参加复试人数/通过招聘人数/入职人数
     $returnData = array('first' => 0, 'second' => 0, 'success' => 0, 'entry' => 0);
     $returnDataDetail = array('first' => array(), 'second' => array(), 'success' => array(), 'entry' => array());
     foreach ($userList as $key => $value) {
         $eventList = F::db()->fetch_all('select * from event where user_id = ? and status = 1', array($value));
         $auditionInfo = $this->auditionModel->seachById($value);
         $returnData['first']++;
         $returnDataDetail['first'][$value] = $auditionInfo;
         foreach ($eventList as $eventIndex => $eventValue) {
             if ($eventValue['content'] == '#复试 @王则琼') {
                 $returnData['second']++;
                 $returnDataDetail['second'][$value] = $auditionInfo;
             }
         }
         if ($this->tagModel->userHadTagById($value, self::INTERVIEW_SUCCESS)) {
             $returnData['success']++;
             $returnDataDetail['success'][$value] = $auditionInfo;
         }
         if ($this->tagModel->userHadTagById($value, self::ENTRY)) {
             $returnData['entry']++;
             $returnDataDetail['entry'][$value] = $auditionInfo;
         }
         unset($eventList);
         unset($auditionInfo);
     }
     return array('rate' => $returnData, 'detail' => $returnDataDetail);
 }

 /**
  * 统计现在所有的人曾经创建的标签
  * 
  * @return array
  */
 public function sta()
 {
     $eventList = F::db()->fetch_all('select content, create_user_id from event where status = 1 order by event.id desc');
     #整理标签信息
     $returnData = array();
     foreach ($eventList as $value) {
         $tagArray = $this->getTagFromEvent($value['content']);
         foreach ($tagArray as $index) {
             preg_match('/R\\w{1,}/', $index, $match);
             if (isset($match[0]) && $match[0]) {
                 continue;
             }
             if (!in_array($index, $returnData[$value['create_user_id']])) {
                 $returnData[$value['create_user_id']][] = $index;
             }
         }
     }
     #补充用户信息
     foreach ($returnData as $key => $value) {
         $userInfo = $this->userModel->get($key);
         $returnData[$userInfo[0]['nickname']] = implode(',', $value);
         unset($returnData[$key]);
     }
     return $returnData;
 }

 /**
  * 生成一份新的授权信息
  * 
  * @access public
  * @param int $userId 用户id
  * @return array
  */
 public function create($userId)
 {
     #生成相关信息
     $this->accessToken = $this->_createToken($userId);
     $this->refreshToken = $this->_createToken($userId);
     $this->expireTime = time() + self::DEFAULT_TOKEN_TIME;
     #写入数据库
     F::db()->execute('replace into token set user_id = ?, token = ?, refresh_token = ?, create_time = ?, expire_time = ?', array($userId, $this->accessToken, $this->refreshToken, date(self::DEFAULT_TIME_STYLE), date(self::DEFAULT_TIME_STYLE, $this->expireTime)));
     return array('accessToken' => $this->accessToken, 'refreshToken' => $this->refreshToken, 'expireTime' => $this->expireTime);
 }

 /**
  * 每日凌晨统计使用非法维修记录信息
  * @return void
  */
 public function illegalAccountCount()
 {
     #获取起至时间
     $date = date('Y-m-d');
     $startTime = strtotime($date);
     $endTime = strtotime('+1day', $startTime);
     #获取违规数据
     $list = F::db()->fetch_all('select count(id) as num, create_user_id from event where content like "%#维修%" and type != 3 and create_time between ? and ? and status = 1 group by create_user_id', array(date(TIMESTYLE, $startTime), date(TIMESTYLE, $endTime - 1)));
     foreach ($list as $value) {
         $username = F::db()->query('select nickname from user_profile where user_id = ? limit 1', array($value['create_user_id']))->fetch_column('nickname');
         #写入错误日志文件
         file_put_contents('/home/wwwlogs/errorData/' . date('Y-m-d'), $username . ' 违规书写维修记录' . $value['num'] . '个' . "\r\n", FILE_APPEND);
     }
     unset($list);
     F::rest()->show_result();
 }

 /**
  * 获取总计注册内容账户数目
  * @return int
  */
 public function getTotalUserNum($keyword)
 {
     if ($keyword) {
         $num = F::db()->query('select count(id) as num from account left join user_profile on account.user_id = user_profile.user_id
             where (nickname like ' . "'%{$keyword}%'" . ' or phone_num like ' . "'%{$keyword}%'" . ') and type = ? and status = 1', array(1))->fetch_column('num');
     } else {
         $num = F::db()->query('select count(1) as num from account where type = ? and status = 1', array(1))->fetch_column('num');
     }
     return $num ? $num : 0;
 }

 /**
  * 获取评论信息详情
  * 
  * @access public
  * @param int $id
  * @return array
  */
 public function get($id)
 {
     $detail = F::db()->fetch('select id, user_profile.user_id as userId, content, time, replay_id as replayId, nickname, photo 
         from comment left join user_profile on comment.user_id = user_profile.user_id where id = ? and status = 1 limit 1', array($id));
     return is_array($detail) ? $detail : array();
 }

 /**
  * 生成一个异步通知
  * @return boolean
  */
 public function createSyncNotify()
 {
     $_createTime = isset($this->sendRequestParam['create_time']) ? $this->sendRequestParam['create_time'] : date(TIMESTYLE);
     $this->queueId = F::db()->execute('insert into queue set taskphp = ?, param = ?, create_time = ?, status = ?', array($this->sendRequestUr, serialize($this->sendRequestParam), $_createTime, 0))->insert_id();
     return TRUE;
 }

 /**
  * 用户接口调用日志纪录
  * 
  * @access public
  * @param int $userId
  * @param string $APIName
  * @param string $param
  * @return boolean
  */
 protected function userLog($userId, $APIName, $param)
 {
     F::db()->execute('insert into user_log set user_id = ?, API_name = ?, param = ?, time = ?', array($userId, $APIName, serialize($param), date('Y-m-d H:i:s')));
 }

 /**
  * 根据手机号码, 获取面试人员id
  * 
  * @access private
  * @param string $phoneNum
  * @return int | boolean
  */
 private function getUserIdByPhoneNum($phoneNum)
 {
     $userId = F::db()->query('select account.user_id as user_id from account left join user_tag on account.user_id = user_tag.user_id 
         where phone_num = ? and status = 1 and type != 1 and tag_id = ?', array($phoneNum, self::AUDITION_TAG_ID))->fetch_column('user_id');
     return $userId ? $userId : FALSE;
 }

 public function seachAll($name)
 {
     if (!trim($name)) {
         return array();
     }
     $list = F::db()->fetch_all('SELECT user_profile.user_id as userId, nickname, phone_num FROM `user_profile` left join account on user_profile.user_id = account.user_id
         where nickname like ? and status = 1 limit 20 ', array($name . '%'));
     return is_array($list) ? $list : array();
 }

 /**
  * 通过用户id获取房间信息
  * @param  int $userId 用户id
  * @return array
  */
 private function _getRoomInfoByUser($userId)
 {
     #获取用户所有的tag信息
     $userTagList = F::db()->fetch_all('select name, type from `user_tag` left join tag on user_tag.tag_id = tag.id where user_id = ?', array($userId));
     foreach ($userTagList as $value) {
         if ($value['type'] == 2) {
             $projectName = $value['name'];
         }
         if (preg_match('/\\w{1,}/', $value['name'])) {
             $roomName = $value['name'];
         }
     }
     return $projectName && $roomName ? array('roomName' => $roomName, 'projectName' => $projectName) : array();
 }

 /**
  * 获取全部项目信息列表
  * 
  * @access public
  * @return array
  */
 public function getALLProjectInfo()
 {
     $list = F::db()->fetch_all('select id, name from tag where type = 2');
     return is_array($list) ? $list : array();
 }

 public function seachGroupRoomListByName($name, $page, $num, $userId = 0)
 {
     #获取房间群组列表
     if ($name) {
         $list = F::db()->fetch_all('select id, name from `group` where name like ? and status = 1 and group_type = 1 order by id desc limit ?, ?', array('%' . $name . '%', ($page - 1) * $num, $num));
     } else {
         #获取用户所属项目
         $userProjectInfo = $this->getUserProjectId($userId);
         if (empty($userProjectInfo)) {
             return array();
         }
         $list = F::db()->fetch_all('select id, name from `group` where status = 1 and group_type = 1 and group_project in (?) order by id desc limit ?, ?', array(implode(',', $userProjectInfo), ($page - 1) * $num, $num));
     }
     foreach ($list as &$value) {
         #获取群组标签列表
         $value['tagList'] = $this->getGroupTagList($value['id']);
         #获取群组操作列表
         $value['acctionList'] = $this->getGroupAcctionUserList($value['id']);
         #获取群组最后一则事件信息
         $value['lastEvent'] = $this->getGroupEventList($value['id'], 1, 1);
         $value['lastEvent'] = $value['lastEvent'][0];
     }
     return is_array($list) ? $list : array();
 }

 /**
  * 写入统计结果
  * @param  array  $result 统计结果
  * @param  int    $id     申请统计id
  * @return boolean
  */
 private function _countLog(array $result, $id)
 {
     F::db()->execute('update count set result = ?, is_executed = 1 where id = ?', array(serialize($result), $id));
     return TRUE;
 }