<?php include_once "DBHelper.php";
define('HOST', '127.0.0.1');
define('USER', 'root');
define('PASSWORD', '');
define('DBNAME', 'sxbcms'); /*define('HOST', '120.25.144.215');
define('USER', 'root');
define('PASSWORD', '88ac86754a');
define('DBNAME', 'sxbcms');*/ class student extends DBHelper{ function __construct()
{
parent::__construct();
} public function students(){
$sql = "select user.id , user.mobile ,name, sex, user.birthday ,area, user.email, `fgraduate` from hr_users user left join hr_student on user.id = hr_student.user_id where user.usertype =1 ";
$ret = $this->find($sql);
return $ret;
} //获取最高的学位 limit 1
public function edu($user_id){
$sql = "select `user_id`, `fdegree`, `grade` , `fschool` , `fmajor` from hr_student_eduexp where user_id = $user_id order by fdegree desc limit 1";
//echo $sql;
$ret = $this->find($sql);
return $ret;
} public function citys(&$data){
$sql ="select * from hr_city ";
$ret = $this->find($sql);
$temp = [] ;
foreach ($ret as $key => $value) {
$temp[$value['id']] = $value['cityname'];
}
foreach($data as $key=>$value){
$city = $value['area'];//获取area 的id
if(isset($temp[$city]))
{
$data[$key]['citys']=$temp[$city];
unset($data[$key]['city']);
}
}
} public function csv($file,$list)
{
$fh = fopen($file,'w') or die("Can't open file.csv"); foreach ($list as $sales_line) {
if (fputcsv($fh, $sales_line) === false) {
die("Can't write CSV line");
}
}
fclose($fh) or die("Can't close file.csv"); }
} $stu = new student();
$listStu = $stu->students();
foreach ($listStu as $key => $value) {
$user_id = $value['id'];//获取用户的id
if(!empty($user_id)){
$eduStu = $stu->edu($user_id);
/*echo "<pre>";print_r($eduStu);echo "</pre>";*/
//根据user_id遍历教育经历,过滤掉为空的教育经历
if(is_array($eduStu)){
foreach ($eduStu as $edu) {
$listStu[$key]['fdegree'] = $edu['fdegree'];
$listStu[$key]['grade'] = $edu['grade'];
$listStu[$key]['fschool'] = $edu['fschool'];
$listStu[$key]['fmajor'] = $edu['fmajor'];
}
}
}
} $stu->citys($listStu);
$stu->csv("student123.csv", $listStu); ?>
DBhelper.php
<?php
header("Content-type:text/html;charset=utf-8"); class DBHelper{ protected $conn; function __construct()
{ $this->InitConn(); } private function InitConn()
{ $this->conn = new mysqli(HOST, USER, PASSWORD, DBNAME); if ($this->conn->connect_errno) { printf("Connect failed: %s\n", $this->conn->connect_error); exit(); } $this->conn->query("set names utf8"); } public function changeDb($user, $pass, $ip, $db)
{ $this->conn = new mysqli($ip, $user, $pass, $db); if ($this->conn->connect_errno) { printf("Connect failed: %s\n", $this->conn->connect_error); exit(); } $this->conn->query("set names utf8"); } public function Execute($sql)
{
echo $sql."\n\n";
return $this->conn->query($sql); } public function find($sql)
{ $ret = []; $list = $this->conn->query($sql); while ($row = $list->fetch_array(MYSQLI_ASSOC)) { $ret[] = $row; } return $ret; } public function findOne($sql)
{ $list = $this->conn->query($sql); $row = $list->fetch_array(MYSQLI_ASSOC); return $row; } public function Save($table ,$data)
{
$sql ="insert into $table set ";
foreach ($data as $key => $value) {
$data[$key] = addslashes($value);
}
foreach ($data as $key => $value) {
$sql =$sql ."`".$key."`='". $value ."',";
}
$sql = substr($sql,,count($sql)-);
echo $sql."\n";
return $this->conn->query($sql);
} public function Close()
{ $this->conn->close(); } public function executesql($sql)
{
return $this->conn->query($sql);
} }
举例子
<?php include_once "DBHelper.php";
define('HOST', '120.25.144.215');
define('USER', 'root');
define('PASSWORD', '88ac86754a');
define('DBNAME', 'sxbcms'); /*
自己注册企业列表 */ /**
*
*/
class enterprise extends DBHelper
{ function __construct()
{
parent::__construct();
} public function listEnt()
{
$sql = "select hr_company.id , comfullname ,email ,mobile ,scale ,industry,mainproduct,enterprise ,hr_users.id as user_id from hr_company left join hr_users on hr_company.user_id = hr_users.id
where user_id not in (select user_id from hr_edit_create_company)"; $ret = $this->find($sql);
return $ret;
} /*
发不过的职位总数 */ public function jobs()
{
$sql ="select count(*) as ct ,user_id from hr_job where addtime > '2016-01-01' and addtime < '2016-09-30' group by user_id" ;
$ret = $this->find($sql);
$temp =[];
foreach ($ret as $key => $value) {
$uid = $value['user_id'];
$temp[$uid] = $value['ct'];
} return $temp; } public function citys(&$data)
{
$sql ="select * from hr_city ";
$ret = $this->find($sql);
$temp = [] ;
foreach ($ret as $key => $value) {
$temp[$value['id']] = $value['cityname'];
}
foreach($data as $key=>$value){ $provinces = $value['provinces'];
$city = $value['city'];
if(isset($temp[$provinces]))
{
$data[$key]['pro']=$temp[$provinces];
$data[$key]['citys']=$temp[$city];
unset($data[$key]['provinces']);
unset($data[$key]['city']);
} } } //设置行业
public function injertindustry(&$data)
{
$sql ="select * from hr_industry";
$ret = $this->find($sql);
$temp = [] ;
foreach ($ret as $key => $value) {
$temp[$value['id']] = $value['name'];
}
foreach ($data as $key => $value) {
$industry =$value['industry']; if(isset($temp[$industry])){
$name =$temp[$industry];
$data[$key]['industry_name'] = $name;
}else
{
$data[$key]['industry_name'] =""; }
}
} public function Position(&$data)
{
$sql ="select * from hr_position";
$ret = $this->find($sql);
$temp = [] ;
foreach ($ret as $key => $value) {
$temp[$value['id']] = $value['name'];
}
foreach ($data as $key => $value) {
$jobtype =$value['jobtype']; if(isset($temp[$jobtype])){
$name =$temp[$jobtype];
$data[$key]['jobtype'] = $name;
}else
{
$data[$key]['jobtype'] =""; }
} } /*
职位的投递量
*/
public function listJobBYCompany()
{
//获取全部的job-id和对应的company
$sql = "select user_id ,id from hr_job";
$ret = $this->find($sql); $sql = "select count(*) , jobid from hr_action group by jobid ";
$list = $this->find($sql); $temp =[];
foreach ($list as $key => $value) {
$jobid =$value['jobid'];
$temp[$jobid] =$value;
} foreach ($ret as $key => $value) {
$jobid =$value['id'];
$ret[$key]['total'] =$temp[$jobid];
} return $temp; } public function listContainJob($user_id,$email=null ,$name=null)
{
$sql = "select id , title,num ,addtime ,provinces ,city,jobtype ,viewnum from hr_job where user_id=".$user_id;
$list = $this->find($sql);
foreach ($list as $key => $value) {
$jobid =$value["id"];
$sql = "select count(*) as ct from hr_action where jobid=".$jobid;
$ct = $this->findOne($sql);
$list[$key]['deliver'] = $ct['ct'];
$list[$key]['email'] = $email;
$list[$key]['name'] = $name;
$sql = "select count(*) as ct from hr_action where jobid=".$jobid ." and open_viwe = 1";
$ct = $this->findOne($sql);
$list[$key]['resumeviewn'] = $ct['ct'];
}
return $list;
} public function csv($file,$list)
{
$fh = fopen($file,'w') or die("Can't open file.csv"); foreach ($list as $sales_line) {
if (fputcsv($fh, $sales_line) === false) {
die("Can't write CSV line");
}
}
fclose($fh) or die("Can't close file.csv"); } } $ent= new enterprise();
$list = $ent->listEnt();
$jobs =$ent->jobs();
foreach ($list as $key => $value) {
$uid = $value['user_id'];
if(isset($jobs[$uid])){
$list[$key]['total'] = $jobs[$uid];
}else
{
$list[$key]['total'] = ;
} }
$ent->injertindustry($list);
$jobs =[];
foreach ($list as $key => $value) {
$uid = $value['user_id'];
if(intval($uid)>){
$email =$value['email'];
$name =$value['comfullname'];
$li = $ent->listContainJob($uid,$email ,$name);
if(!empty($li))
{
foreach($li as $item)
{
array_push($jobs, $item);
}
}
} } $ent->citys($jobs);
$ent->Position($jobs);
$ent->csv("ent.csv", $list);
$ent->csv("jobs.csv", $jobs);