需求：在传递一组职位编号的时候，需要统计该职位的 当天的投递情况 和 有历史记录以来总的投递量
解决方案一：
每次都进行一次数据库查询，遍历职位id，再根据职位id去查询相应时间内的投递量
/**
* [exportData 导出相应职位的投递量]
* @param [array] $params [需要查询的职位id,时间等]
* @return [array] [相应职位id的投递量]
*/
public function exportData($params){
//进行数据库查询 [查询所要导出的数据]
$getWhere = '1 =1';
//开始时间和结束时间
if( (isset($params['start_date']) && $params['start_date']) && (isset($params['end_date']) && $params['end_date']) ){
$getWhere.=" and `addtime` between '{$params['start_date']}' and '{$params['end_date']}' ";
}
$jobidsArr = explode("," , $params['jobids']);
$count = [];
$dealCount = [];
$actionTotal = [];
$actionDealTotal = [];
foreach ($jobidsArr as $key => $value) {
$sql = "SELECT count(*) as count from hr_action where jobid = ".$value. " and ".$getWhere;
$count[$key] = $this->db->findOne($sql);//返回数组 //获取当天的处理状态
$sqlDeal = "SELECT count(*) as count from hr_action where jobid = ".$value. " and `astatus` > 0 and ".$getWhere;
$dealCount[$key] = $this->db->findOne($sqlDeal);//返回数组 //总职位数和总处理情况进行查询
$sqlTotal = "SELECT count(*) as count from hr_action where jobid = $value";
$actionTotal[$key] = $this->db->findOne($sqlTotal); $sqlDealTotal = "SELECT count(*) as count from hr_action where jobid = $value and `astatus` > 0"; $actionDealTotal[$key] = $this->db->findOne($sqlDealTotal); } foreach ($count as $ckey => $cvalue) {
$actionCount[$ckey] = $cvalue['count'];
}
foreach ($dealCount as $dkey => $dvalue) {
$actionDealCount[$dkey] = $dvalue['count'];
}
foreach ($actionTotal as $atkey => $atvalue) {
$actionTotalCount[$atkey] = $atvalue['count'];
}
foreach ($actionDealTotal as $adtkey => $adtvalue) {
$actionDealTotalCount[$adtkey] = $adtvalue['count'];
} return ['jobid'=> $jobidsArr , 'actionCount'=> $actionCount , 'actionDealCount' => $actionDealCount , 'actionTotalCount'=>$actionTotalCount,'actionDealTotalCount'=>$actionDealTotalCount];
}
以上这种方法思路确实很简单，但是仔细查看，在运行的时候可以算一下进行了多少次的数据库查询，每一次的foreach循环都会进行四次查询数据库，当我一次传递30个职位的时候，就是4*30 次数据库查询了，由于数据库中的数量比较大，每次都全部便利一次这样的查询不满才怪呢。咳咳咳咳。。
方法二：尽量的减少数据库的查询，对数据库的操作越少越好。为什么不在第一次遍历投递情况的时候，把所有的投递情况都遍历出来保存到一个数组中，然后对数组进行操作，把对数据库的操作转换成php在内存中操作，这样不是挺好的吗？
因此，首次遍历的时候利用in 查询，把所有的投递情况都查询出来。
/**
* [exportData 导出相应职位的投递量]
* @param [array] $params [需要查询的职位id,时间等]
* @return [array] [相应职位id的投递量]
*/
public function exportData($params){
//进行数据库查询 [查询所要导出的数据]
$getWhere = '1 =1';
//开始时间和结束时间
if( (isset($params['start_date']) && $params['start_date']) && (isset($params['end_date']) && $params['end_date']) ){
$getWhere.=" and `addtime` between '{$params['start_date']}' and '{$params['end_date']}' ";
}
$jobids = $params['jobids'];//获取传入的职位id
$jobidsArr = explode("," , $params['jobids']); //根据职位id来查询相应的职位信息和公司信息
$sqlInfo = "SELECT j.`title`,j.`id` from hr_job as j where id in ($jobids)";
$jobTitle = $this->db->find($sqlInfo);
$sqlCompany = "SELECT c.`comname`,j.`id` from hr_company as c right join hr_job as j on j.`user_id` = c.`user_id` where j.`id` in ($jobids)";
$jobCompany = $this->db->find($sqlCompany); //print_r($jobTitle);
$newjobTemp = [];
foreach ($jobTitle as $key => $value) {
$newjobTemp[$value['id']] = $value['title'];
} $companyTemp = [];
foreach ($jobCompany as $key => $value) {
$companyTemp[$value['id']] = $value['comname'];
}
//print_r($newjobTemp);
//
// /*************总职位数和总处理情况进行查询*************/
$sqlTotal = "SELECT `jobid`,`addtime` ,`astatus` from hr_action where jobid in ($jobids)";
$sqlDealTotal = "SELECT `jobid`,`addtime` ,`astatus` from hr_action where jobid in ($jobids) and ".$getWhere; $retTotal = $this->db->find($sqlTotal);//返回总数
$retDealTotal = $this->db->find($sqlDealTotal);//返回当天处理总数 /*************总职位数和总处理情况进行查询*************/ $temp1 = [];
foreach ($retTotal as $ckey => $cvalue) {
$temp1[$ckey] = $cvalue['jobid'];
}
$actionCount1 = array_count_values($temp1);
//print_r($actionCount1);//投递总数 $temp2 = [];
foreach ($retTotal as $dkey => $dvalue) {
$temp2[$dkey] = [$dvalue['astatus'],$dvalue['jobid']];
}
//print_r($temp2); $temp3 = [];
foreach ($retDealTotal as $atkey => $atvalue) {
$temp3[$atkey] = $atvalue['jobid'];
}
$actionCount3 = array_count_values($temp3);
//print_r($actionCount3); $temp4 = [];
foreach ($retDealTotal as $adtkey => $adtvalue) {
$temp4[$adtkey] = [$adtvalue['astatus'],$adtvalue['jobid']];
}
//print_r($temp4); //按照预定义格式输出
$actionCount = [];//总投递量
$actionDayCount = [];//当天投递总量
$adealCount = [];//定义一个新数组，存放相对应的总处理量
$adaydealCount = [];//定义一个新数组，存放相对应的当天总处理量
$actionDealCount = [];//存放转换后的总处理量
$actionDayDealCount = [];//存放转换后的当天处理量
$actionJobTitle = [];//职位名称
$actionJobCompany = [];//公司名称
foreach ($jobidsArr as $key => $value) {//循环传入的IDS $i = ;
foreach ($temp2 as $t2key => $t2value) {
if ($value == $t2value[]) {
if ($t2value[] > ) { $i++; }
}
$adealCount[$value] = $i;
} $j = ;
foreach ($temp4 as $t4key => $t4value) {
if ($value == $t4value[]) {
if ($t4value[] > ) { $j++; }
}
$adaydealCount[$value] = $j;
} $actionCount[$key] = $actionCount1[$value];
$actionDayCount[$key] = isset($actionCount3[$value])?$actionCount3[$value]:;
$actionDealCount[$key] = $adealCount[$value];
$actionDayDealCount[$key] = $adaydealCount[$value];
$actionJobTitle[$key] = $newjobTemp[$value];
$actionJobCompany[$key] = $companyTemp[$value];
} // print_r($actionJobTitle);
/* print_r($actionCount);//总投递量
print_r($actionDayCount);//当天投递总量
print_r($actionDealCount);//总处理量
print_r($actionDayDealCount);//当天处理量*/ return ['jobid'=> $jobidsArr , 'actionCount'=> $actionCount , 'actionDealCount' => $actionDealCount , 'actionDayCount'=>$actionDayCount,'actionDayDealCount'=>$actionDayDealCount,'actionJobTitle'=>$actionJobTitle , 'actionJobCompany'=>$actionJobCompany];
}
导出数据，都是使用的同一套方法
//导出相应职位的投递情况
public function exportData($params = array()){
/*[start_date] => 2016-11-23 12:09:05
[end_date] => 2016-11-24 12:09:07
[jobids] => 12,23,234,345,345,4564,5646*/
/*echo "<pre>";
print_r($params);*/
$jobModel = new joblist_jobModel();
$job_action_count = $jobModel->exportData($params); ini_set('memory_limit','500M');
set_time_limit(); $jobids = $job_action_count['jobid'];//查询的职位id
$actionDayCount = $job_action_count['actionDayCount'];//当天的投递量
$actionDayDealCount = $job_action_count['actionDayDealCount'];//当天处理量
$actionCount = $job_action_count['actionCount'];//总投递量
$actionDealCount = $job_action_count['actionDealCount'];//总处理量
$actionJobTitle = $job_action_count['actionJobTitle'];//职位标题
$actionJobCompany = $job_action_count['actionJobCompany'];//企业名称
if(empty($jobids)){
exit("暂无数据");
} $temp = [];
foreach ($jobids as $key => $value) {
$temp[$key] = [$value,$actionJobCompany[$key],$actionJobTitle[$key],$actionDayCount[$key], $actionDayDealCount[$key], $actionCount[$key], $actionDealCount[$key]];
}
/*print_r($temp);
exit;*/ $column_name = array();
header ( "Content-type:application/vnd.ms-excel" );
header ( "Content-Disposition:filename=" . iconv ( "UTF-8", "GBK", date("Y-m-d_H_i_s" , time())."-".rand(,) ) . ".csv" );
// 打开PHP文件句柄，php://output 表示直接输出到浏览器
$fp = fopen('php://output', 'a');
$head = ['职位编号','企业名称','职位名称','当天的投递量','当天的处理量','总投递量','总处理量'];
foreach ($head as $i => $v) {
$column_name[$i] = iconv('utf-8', 'GBK', $v);
}
fputcsv($fp, $column_name);
foreach ($temp as $tkey => $tvalue ) {
$rows = array();
foreach ($tvalue as $newkey => $newvalue) {
$rows[] = iconv('utf-8', 'GBK', $newvalue);
}
fputcsv($fp, $rows);
}
ob_flush();
flush();
die();
}