edm/application/module/ctrl/Customer_list_adminAction.class.php

<?php 

/**
 * 导入客户信息，导出客户信息
 */

require_once( ONU_ROOT . 'application/module/ctrl/Action.class.php');
require_once ( ONU_ROOT . 'application/lib/data/adminAction.php');

class Customer_list_adminAction extends Action{
	
	public function __construct(){
		parent::__construct();
		if(empty($_SESSION['mds_user'])){
			header("Location:/?a=index&m=admin_login");
			exit();
		}
//		$this->checkAdministratorRight('1');
	}

	//客户列表
	public function index(){

		require_once (ONU_ROOT . "frame/Page.class.php");

		$admin = new AdvertAdminAction();
		$uid   = $_SESSION['user_infos']['id'];

		$info = $_REQUEST['info'];

		$page     = $_REQUEST['page']?$_REQUEST['page']:1;
		$pageSize    = isset($_REQUEST['pagesize'])?$_REQUEST['pagesize']:20;
		
		$n        = ($page-1)*$pageSize;
		$pa       = "";//查询参数
		$pa='&pagesize='.$pageSize;
		$limit = " $n,$pageSize";
		$where = " where uid='$uid' ";

		$group_list = $admin->select_user_group($where);

		$username = trim($_REQUEST['username']);
		$group_name = $_REQUEST['group_name'];
		$isEmail = (!empty($username) && strpos($username, '@') !== false);

		if(!empty($username) && strpos($username, '@') !== false){
			$where.=" and email='$username'";
			$pa .= "&username=$username";
		}elseif(!empty($username)) {
			$where.=" and uname='$username'";
			$pa .= "&username=$username";
		}

		if(!empty($group_name)){
			$where.=" and group_name='$group_name'";
			$pa .= "&group_name=$group_name";
		}

//		if(!empty($date_show)){
//			$where.=" and create_time>=$begin_time and create_time<=$end_time";
//			$t = "$bd-$ed";
//			$pa .= "&date_show=$t";
//		}

		$customer_list = $admin->selesctCustomer($where,$limit);
		$all_total = $admin->selectCustomerCount($where);

		$p = new Page($all_total,$pageSize,$pa);
		$pp = $p->fpage();
      
      	$tmp_data = $admin->select_tmp_data();
		$tmp_num = count($tmp_data);

		$this->assign('tmp_num',$tmp_num);

		$this->assign('username',$username);
		$this->assign('list',$customer_list);
		$this->assign('group_list',$group_list);
		$this->assign('group_name',$group_name);
		$this->assign('info',$info);
		$this->assign('page',$page);
		 $this->assign('pagesize',$pageSize);
		$this->assign("show",$pp);
		$this->display("index.html");
	}

	public function uploadInvalidEmail(){
		$this->display("address_invalid_email.html");
	}

	public function addressInvalidEmail(){
		require_once ONU_ROOT . 'PhpOffice/PhpSpreadsheet/Spreadsheet.php';
		require_once ONU_ROOT . 'PhpOffice/PhpSpreadsheet/Reader/Csv.php';
		require_once ONU_ROOT . 'PhpOffice/PhpSpreadsheet/Reader/Xlsx.php';

		$file_mimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
 
		if(isset($_FILES['file']['name']) && in_array($_FILES['file']['type'], $file_mimes)) {
		
			$arr_file = explode('.', $_FILES['file']['name']);
			$extension = end($arr_file);
		
			if('csv' == $extension) {
				$reader = new Csv();
			} else {
				$reader = new Xlsx();
			}
		
			$spreadsheet = $reader->load($_FILES['file']['tmp_name']);
			
			$sheetData = $spreadsheet->getActiveSheet()->toArray();
			print_r($sheetData);
		}
		
	}

	//添加客户
	public function add(){
		$admin = new AdvertAdminAction();
		$uid   = $_SESSION['user_infos']['id'];

		$where = "where uid='$uid' ";
		$group_list = $admin->select_user_group($where);

		if($_POST['email']){
			$data['uname'] = $_POST['username'];
			$data['email'] = $_POST['email'];
			$data['group_name'] = $_POST['group_name'];
			$data['country'] = $_POST['country'];
			$data['city'] = $_POST['city'];
			$data['tel'] = $_POST['tel'];
			$data['uid'] = $uid;
			$data['add_time'] = time();

			$is_exist = $admin->selectCustomerByEmail($data['email'],$data['uid']);
			if(empty($is_exist)){

				$add = $admin->add_customer($data);

				if($add){
					$info = "添加客户资料成功";
					header("Location:/?a=customer_list_admin&m=index&info=$info");
					exit();
				}else{
					$info = "添加客户资料失败";
					$this->assign('info',$info);
				}
			}else{
				$info = "您之前已经添加过此用户";
				header("Location:/?a=customer_list_admin&m=index&info=$info");
				exit();
			}

		}

		$this->assign('group_list',$group_list);
		$this->display('add.html');
	}

	//编辑客户
	public function edit(){
		$admin = new AdvertAdminAction();
		$uid   = $_SESSION['user_infos']['id'];
		$where = "where uid='$uid' ";
		$group_list = $admin->select_user_group($where);

		$id = $_REQUEST['id'];

		$one_info = $admin->selectOneCustomer($id);

		if($_POST['email']){
			$id = $_POST['id'];
			$data['uname'] = $_POST['username'];
			$data['email'] = $_POST['email'];
			$data['group_name'] = $_POST['group_name'];
			$data['country'] = $_POST['country'];
			$data['city'] = $_POST['city'];
			$data['tel'] = $_POST['tel'];
			$update = $admin->updateCustomer($data,$id);

			if($update){
				$info = "编辑客户资料成功";
				header("Location:/?a=customer_list_admin&m=index&info=$info");
				exit();
			}else{
				$info = "编辑客户资料失败";
				$this->assign('info',$info);
			}
		}

		$this->assign('id',$id);
		$this->assign('one_info',$one_info);
		$this->assign('group_list',$group_list);
		$this->display('edit.html');
	}

	//删除客户
	public function delete(){
		$admin = new AdvertAdminAction();
		$uid   = $_SESSION['user_infos']['id'];

		$id = $_REQUEST['id'];

		if($_REQUEST['id']){
			$delete = $admin->deleteCustomer($id,$uid);

			$info = "删除成功";
			header("Location:/?a=customer_list_admin&m=index&info=$info");
			exit();
		}

	}

	//批量删除客户
	public function batch_delete(){
		$admin = new AdvertAdminAction();
		$uid   = $_SESSION['user_infos']['id'];
		$id_array = $_POST['id_array'];
		$count    = count($id_array);

		if($count==1){
			$id = $id_array[0];
			$delete = $admin->deleteCustomer($id,$uid);

			$info = "删除成功";
			header("Location:/?a=customer_list_admin&m=index&info=$info");
			exit();

		}else if($count>1){
			$in_array = implode(',',$id_array);

			$delete = $admin->deleteBatchCustomer($in_array,$uid);
			$info = "删除成功";
			header("Location:/?a=customer_list_admin&m=index&info=$info");
			exit();
		}

	}



	//导出excel
	public function export(){
		$admin = new AdvertAdminAction();
		$uid   = $_SESSION['user_infos']['id'];

		$group_name = $_REQUEST['group_name'];

		$where = " where uid='$uid' ";

		if(!empty($group_name)){
			$where.=" and group_name='$group_name'";
		}

		$list = $admin->selesctCustomer($where);

		foreach($list as &$ls){
			$ls['add_time'] = date('Y-m-d H:i:s',strtotime($ls['add_time']));
		}
		$d = date('Y-m-d',time());
		$this->generateEXCEL($d."导出客户数据",$list);

	}

	public function test(){
		$admin = new AdvertAdminAction();
		$uid   = $_SESSION['user_infos']['id'];
		$blackListArr = $admin->selectAllBlack($uid);
		$ndrListArr = $admin->selectAllNdrUser($uid);
		echo $uid.PHP_EOL;
		print_r(array_column($blackListArr, 'email'));
		echo "------------------------".PHP_EOL;
		print_r(array_column($ndrListArr, 'email'));
	}

	//导入excel
	public function import(){

		$admin = new AdvertAdminAction();
		$uid   = $_SESSION['user_infos']['id'];

		if($_POST['to_import_user']=='yes'){
			$filename = $_FILES['inputExcel']['name'];
			$tmp_name = $_FILES['inputExcel']['tmp_name'];

			$excel_array = $this->uploadFile($filename,$tmp_name);

			if(!empty($excel_array['error'])){
				//导入失败
				$info = $excel_array['error'];

			}else{

				unset($excel_array[0]);//若第一排的数据是字段名的话，删除
				header("Content-type: text/html; charset=utf-8");
				$blackList = $admin->selectAllBlack($uid);
				$blackListArr = array_column($blackList, 'email');
				$ndrList = $admin->selectAllNdrUser($uid);
				$ndrListArr = array_column($ndrList, 'email');
       	
              	$delEmailArr = array_flip(array_flip(array_merge($blackListArr,$ndrListArr)));
              
             	$oldUserEmailList = $admin->selesctCustomerByUid($uid);
				$oldUserEmailListArr = array_column($oldUserEmailList, 'email');
				$oldUserEmailListArr = array_map('strtolower', $oldUserEmailListArr);

				foreach($excel_array as $array){

					if(!empty($array[1])){
						$data['uname'] = str_replace("'", '', $array[0]);;
						$data['email'] = $email_tmp	 = trim($array[1]);
						$data['tel'] 		 = trim($array[2]);
						$data['country'] 	= trim($array[3]);
						$data['city'] 	 = trim($array[4]);
						$data['group_name'] = trim($array[5]);
						$data['add_time'] 	   	 = time();
						$data['uid']  = $uid;

						//先查询一下用户邮箱是否在退信列表，如果在退信列表就不导入
						//$where = "uid = '$uid'";
						//$is_ndr_user = $admin->select_ndr_user($where,$data['email']);
						//过滤字符串
						if( strstr($email_tmp,"'") ){
							$info = "导入失败".$email_tmp."含有单引号!";
							header("Location:/?a=customer_list_admin&m=index&info=$info");
							exit();
						}
						//查询在不在黑名单里面，如果在就不导入
						//$is_black_user = $admin->selectBlackByEmail($data['email'],$data['uid']);
						
						if(!in_array($data['email'], $delEmailArr)){
								//$is_exist = $admin->selectCustomerByEmail($data['email'],$data['uid']);
								if(!in_array(strtolower($data['email']), $oldUserEmailListArr)){
									$insertData[] = $data;
								}else{
									//修改用户分组
									if(!empty($data['group_name'])){
										$upData[] = $data;
									}
								}
						}
					}

				}
              	$insert = $up = true;
              	if(count($insertData) > 0){
                	$insert = $admin->addpl_customer($insertData);
              	}
               	if(count($upData) > 0){
                	$up = $admin->updateCustomerGroupByEmail($upData);
              	}
                $info = "导入成功";
			}

			header("Location:/?a=customer_list_admin&m=index&info=$info");
			exit();

		}

	}


	//导出excel
	function generateEXCEL($filename = 'report',$data = ''){

		//引入PHPExcel相关文件
		require_once( ONU_ROOT . 'phpexcel/PHPExcel.php');
		require_once( ONU_ROOT . 'phpexcel/PHPExcel/IOFactory.php');
		require_once( ONU_ROOT . 'phpexcel/PHPExcel/Writer/Excel5.php');

		//新建
		$resultPHPExcel = new PHPExcel();
		//设置参数

		//设值
		$resultPHPExcel->getActiveSheet()->setCellValue('A1', '客户名');
		$resultPHPExcel->getActiveSheet()->setCellValue('B1', '客户邮箱');
		$resultPHPExcel->getActiveSheet()->setCellValue('C1', '手机号');
		$resultPHPExcel->getActiveSheet()->setCellValue('D1', '国家');
		$resultPHPExcel->getActiveSheet()->setCellValue('E1', '城市 ');
		$resultPHPExcel->getActiveSheet()->setCellValue('F1', '所属分组');

		//设置水平居中
		$resultPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$resultPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$resultPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$resultPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$resultPHPExcel->getActiveSheet()->getStyle('E1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$resultPHPExcel->getActiveSheet()->getStyle('F1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

		//设置水平居中
		$resultPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$resultPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$resultPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$resultPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$resultPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$resultPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


		$i = 2;
		foreach($data as $item){
			$resultPHPExcel->getActiveSheet()->setCellValue('A' . $i, $item['uname']);
			$resultPHPExcel->getActiveSheet()->setCellValue('B' . $i, $item['email']);
			$resultPHPExcel->getActiveSheet()->setCellValue('C' . $i, $item['tel']);
			$resultPHPExcel->getActiveSheet()->setCellValue('D' . $i, $item['country']);
			$resultPHPExcel->getActiveSheet()->setCellValue('E' . $i, $item['city']);
			$resultPHPExcel->getActiveSheet()->setCellValue('F' . $i, $item['group_name']);

			//设置单元格宽度
			$resultPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);
			$resultPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
			$resultPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
			$resultPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
			$resultPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
			$resultPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);

			//设置水平居中
			$resultPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$resultPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$resultPHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$resultPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$resultPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$resultPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			//设置行高度
			$resultPHPExcel->getActiveSheet()->getRowDimension("1")->setRowHeight(20);
			$resultPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(20);

			$i ++;
		}

		//设置导出文件名
		$xlsWriter = new PHPExcel_Writer_Excel5($resultPHPExcel);

		ob_end_clean();//清除缓冲区,避免乱码

		//ob_start(); ob_flush();
		header("Content-Type: application/vnd.ms-excel; charset=UTF-8");

		header("Content-Type: application/force-download");

		header("Content-Type: application/octet-stream");

		header("Content-Type: application/download");

		//header('Content-Disposition:inline;filename="'.$outputFileName.'"');
		header('Content-Disposition:attachment;filename='.'"'.$filename.'.xls"'); //可以对文件名进行处理

		header("Content-Transfer-Encoding: binary");

		header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");

		header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");

		header("Cache-Control: must-revalidate, post-check=0, pre-check=0");

		header("Pragma: no-cache");

		$xlsWriter->save('php://output');

		exit;
	}

	//导入Excel文件
	function uploadFile($file,$filetempname)
	{
		//自己设置的上传文件存放路径
		$filePath = 'static/upload/';
		$str = "";
		//下面的路径按照你PHPExcel的路径来修改
		require_once ONU_ROOT . 'phpexcel/PHPExcel.php';
		require_once ONU_ROOT . 'phpexcel/PHPExcel/IOFactory.php';
		require_once ONU_ROOT . 'phpexcel/PHPExcel/Reader/Excel5.php';
		//require_once ONU_ROOT . 'phpexcel/PHPExcel/Reader/CSV.php';

		//注意设置时区
		$time=date("y-m-d-H-i-s");//去当前上传的时间
		//获取上传文件的扩展名
		$extend=strrchr ($file,'.');
		//上传后的文件名
		$name=$time.$extend;
		$uploadfile=$filePath.$name;//上传后的文件名地址
		//move_uploaded_file() 函数将上传的文件移动到新位置。若成功，则返回 true，否则返回 false。
		$result=move_uploaded_file($filetempname,$uploadfile);//假如上传到当前目录下

		//echo $result;
		if($result) //如果上传文件成功，就执行导入excel操作
		{
			$objReader = PHPExcel_IOFactory::createReader('Excel5');//use excel2007 for 2007 format
			$objPHPExcel = $objReader->load($uploadfile);
			$sheet = $objPHPExcel->getSheet(0);
			$highestRow = $sheet->getHighestRow();           //取得总行数
			$highestColumn = $sheet->getHighestColumn(); //取得总列数

			/* 第二种方法*/
			$objWorksheet = $objPHPExcel->getActiveSheet();
			$highestRow = $objWorksheet->getHighestRow();
			echo 'highestRow='.$highestRow;
			echo "<br>";
			$highestColumn = $objWorksheet->getHighestColumn();
			$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数
			echo 'highestColumnIndex='.$highestColumnIndex;
			echo "<br>";
			$headtitle=array();

			//获取到的excel数据
			$excel_array = array();

			for ($row = 1;$row <= $highestRow;$row++)
			{
				$strs=array();
				//注意highestColumnIndex的列数索引从0开始
				for ($col = 0;$col < $highestColumnIndex;$col++)
				{
					$strs[$col] =$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();

					if($strs[$col] instanceof PHPExcel_RichText){     //富文本转换字符串
						$strs[$col] = $strs[$col]->__toString();
					}

					//第七个数字是日期型的
//					if($col==7){
//						$strs[$col]=gmdate("Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($strs[$col]));
//					}

				}

				array_push($excel_array, $strs);
			}

		}
		else
		{
			$excel_array['error'] = "导入失败！";
		}
		return $excel_array;
	}

	// class   end
}
?>