php连表查询数据并导出

需求：为了促进空白区域招商，按照地理位置（省/市/区）统计，每个地区的商家数量（包括优质，普通等级别）以及车辆数量（拖车、服务车）

 

表1-【商家表】：商家id，商家地址，商家级别（1为首选商家、2为优质商家、3为普通商家等）  

表2-【车表】：车的id，车的所属商家id，车的级别（service_3389为1 、service_3398为1 ，其中一个为1 就是服务车，否则的话就是拖车）

表3-【地址表】：包含省、市、区的汉字表示以及4位 、8位、12位的数字展示 （省是4位，市是8位等）

        思路：各自在三个表中查询到对应内容（地址列表、车辆列表、商家列表），获取到对应数据之后，将所有的数据匹配到地址集合中。

 

function exportCorp_zyy($param){
		global $dao,$enumVars;
		$DictionaryVars = new DictionaryVars();
		$areaService = new areaService();
		$sql = "select member_id,high_quality,content_area from m_corp where publish in (0,1,2)";//查询所有的商家数据（未关店）
		$sql_car  ="select member_id,content_area,content_carnum,service_6039,service_6098 from r_car where publish ='1'";//查询所有车辆
		$sql_city = "select auto_code,modules_name from cityarea where 1=1 ";
		
		if($param['area']){
			if(strlen($param['area'])>4){
				$code=substr($param['area'], 0,4);//截取省市的字段名称
			}else{
				$code = $param['area'];
			}
			$sql_city .=" and auto_code like '{$code}%'";
			$sql.=" and content_area like '{$param['area']}%'";
		}
		if($param['member_id']){
			$sql.=" and member_id in ({$param['member_id']})";
			$sql_car .= " and member_id in ({$param['member_id']})";
		}	
		
		$city_data_list = $dao->get_datalist($sql_city);//所有的城市数据
		$city_array = array();//放城市的数组
		$xian_array = array();//县级数组
			
		if(is_array($city_data_list) && count($city_data_list)){//判断数组，第二个判断是否有数据
			foreach ($city_data_list as $key_city=>$vl_city){
				$city_array[$vl_city['auto_code']] = $vl_city['modules_name'];
				if(strlen($vl_city['auto_code'])==12){
					$xian_array[$vl_city['auto_code']]['xian_name'] = $vl_city['modules_name'];
				}
			}
		}
		foreach ($xian_array as $key_xian_1=>$vl_xian_1){
			$province_code=substr($key_xian_1, 0,4);//截取省市的字段名称
			$xian_array[$key_xian_1]['province_name'] =$city_array[$province_code];
			$city_code=substr($key_xian_1, 0,8);//截取市级的名称
			$xian_array[$key_xian_1]['city_name'] =$city_array[$city_code];
		}
		$xian_array["0"]=array(
				'xian_name'=>"无所属区县",
				'city_name'=>"无所属城市",
				'province_name'=>"无所属省份"
		);//初始化无数据的省市区
	
		$data_list=$dao->get_datalist($sql);//所有店的数组
		$city_array_member=array();
		if(is_array($data_list) && count($data_list)){//判断数组，第二个判断是否有数据
			foreach ($data_list as $key=>$vl){
				$area=$vl['content_area'];
				if(!is_array($xian_array[$area]) || count($xian_array[$area])==0 || $area==""){
					$area=0;
				}
				$high = $vl['high_quality'];
				if($high == '0'){
					$xian_array[$area]['s0']+=1;//普通商家
				}else if($high == '1'){
					$xian_array[$area]['s1']+=1;//优质
				}else if($high == '2'){
					$xian_array[$area]['s2']+=1;//首派
				}else if($high == '5'){
					$xian_array[$area]['s5']+=1;//临时商家
				}
				$city_array_member[$vl['member_id']]=$area;
			}
		}
		
		$corpMemberIdStr = "";
		if(is_array($city_array_member) && count($city_array_member)){
			$corpMemberIdArr = array_keys($city_array_member);
			$corpMemberIdStr = join(",", $corpMemberIdArr);
			if($corpMemberIdStr){
				$sql_car .= " and member_id in({$corpMemberIdStr})";
			}
		}
		$data_list_car=$dao->get_datalist($sql_car);
		if(is_array($data_list_car) && count($data_list_car)){//判断数组，第二个判断是否有数据
			foreach ($data_list_car as $key_car=>$vl_car){
				$area=$city_array_member[$vl_car["member_id"]];
				if(!is_array($xian_array[$area]) || count($xian_array[$area])==0 || $area==""){
					$area=0;
				}
				if($vl_car["service_6039"] == '1'  || $vl_car["service_6098"]=='1'){
					$xian_array[$area]['tuo_car']+=1;//拖车
				}else {
					$xian_array[$area]['server_car']+=1;//服务车
				}
			}
		}
				
		$objPHPExcel = new PHPExcel();
		$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
		$CellValueArr=array(
				array('title'=>"所在省份",'width'=>'14','is_newLine'=>0),
				array('title'=>"所在城市",'width'=>'14','is_newLine'=>0),
				array('title'=>"所在区县",'width'=>'30','is_newLine'=>0),
				array('title'=>"首派商家数量",'width'=>'30','is_newLine'=>0),
				array('title'=>"优质商家数量",'width'=>'30','is_newLine'=>0),
				array('title'=>"普通商家数量",'width'=>'30','is_newLine'=>0),
				array('title'=>"临时商家数量",'width'=>'30','is_newLine'=>0),
				array('title'=>"拖车数量",'width'=>'20','is_newLine'=>0),
				array('title'=>"服务车数量",'width'=>'20','is_newLine'=>0),
		);
		$cellLen=count($CellValueArr);
		$i="A";
		$objActSheet = $objPHPExcel->getActiveSheet();
		foreach ($CellValueArr as $val){
			$col_title = $i . '1';
			$width=$val['width']==""?20:$val['width'];
			//设置表头
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue($col_title,$val['title']);
			//设置行宽
			$objActSheet->getColumnDimension($i)->setWidth($width);
			if($val['is_newLine']){
				$objActSheet->getStyle($i)->getAlignment()->setWrapText(true);//设置自动换行
			}
			$i++;
		}
		$totalnum=count($xian_array)+1;
		for($n=1;$n<=$totalnum;$n++){
			$i="A";
			for($s=1;$s<$cellLen;$s++){
				$objPHPExcel->getActiveSheet()->getStyle($i.$n)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				$i++;
			}
		}
		$num=2;
		foreach($xian_array as $k => $v1){
			$objPHPExcel->getActiveSheet()//Excel的第A列，uid是你查出数组的键值，下面以此类推
			->setCellValueExplicit('A'.$num, $v1['province_name'])
			->setCellValueExplicit('B'.$num, $v1['city_name'])
			->setCellValueExplicit('C'.$num, $v1['xian_name'])
			->setCellValueExplicit('D'.$num, $v1["s2"])
			->setCellValueExplicit('E'.$num, $v1['s1'])
			->setCellValueExplicit('F'.$num, $v1['s0'])
			->setCellValueExplicit('G'.$num, $v1['s5'])
			->setCellValueExplicit('H'.$num, $v1['tuo_car'])
			->setCellValueExplicit('I'.$num, $v1['server_car']);
			$num++;
		}
		$filename=iconv( "UTF-8","gb2312", '信息数据_zyy'.date("YmdHis"));
		header("Content-Type:application/vnd.ms-execl");
		header("Content-Type:application/octet-stream");
		header('Content-Disposition:attachment;filename='.$filename.'.xls');
		$objWriter->save('php://output');
	}