edm/application/module/ctrl/Sms_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 Sms_customer_list_adminAction extends Action{
	
	public function __construct(){
		parent::__construct();
		if(empty($_SESSION['mds_user'])){
			header("Location:/?a=index&m=admin_login");
			exit();
		}
	}

	//客户列表
	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_sms_user_group($where);

		$tel = trim($_REQUEST['tel']);
		$group_name = $_REQUEST['group_name'];

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

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

		$where.=" and is_block=0";
		$customer_list = $admin->selesctSmsCustomer($where,$limit);
		$all_total = $admin->selectSmsCustomerCount($where);

		$p = new Page($all_total,$pageSize,$pa);
		$pp = $p->fpage();
      
      	
		$this->assign('tel',$tel);
		$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 blackList(){

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

		$admin = new AdvertAdminAction();

		$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 1=1 ";

		$tel = trim($_REQUEST['tel']);
		if(!empty($tel)) {
			$where.=" and tel='$tel'";
		}

		$where.=" and is_block=1";
		$customer_list = $admin->selesctSmsCustomer($where,$limit);
		$all_total = $admin->selectSmsCustomerCount($where);

		$p = new Page($all_total,$pageSize,$pa);
		$pp = $p->fpage();
      
      	
		$this->assign('tel',$tel);
		$this->assign('list',$customer_list);
		$this->assign('info',$info);
		$this->assign('page',$page);
		 $this->assign('pagesize',$pageSize);
		$this->assign("show",$pp);
		$this->display("blacklist.html");
	}

	public function remove(){

		$admin = new AdvertAdminAction();
		$uid   = $_SESSION['user_infos']['id'];
		$info = $_REQUEST['info'];
		$where = " where uid='$uid' ";
		$group_list = $admin->select_sms_user_group($where);

		$this->assign('group_list',$group_list);
		$this->assign('info',$info);
		$this->display("remove.html");
	}


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

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

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

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

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

				if($add){
					$info = "添加手机号成功";
					header("Location:/?a=sms_customer_list_admin&m=index&info=$info");
					exit();
				}else{
					$info = "添加手机号失败";
					$this->assign('info',$info);
				}
			}else{
				$info = "您之前已经添加过此用户";
				header("Location:/?a=sms_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_sms_user_group($where);

		$id = $_REQUEST['id'];

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

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

			if($update){
				$info = "编辑客户资料成功";
				header("Location:/?a=sms_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->deleteSmsCustomer($id,$uid);

			$info = "删除成功";
			header("Location:/?a=sms_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->deleteSmsCustomer($id,$uid);

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

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

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

	}



	

	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->selectSmsBlackList();
				$blackListArr = array_column($blackList, 'tel');
				$values = '';
				foreach($excel_array as $array){
					if(!empty($array[2])){
						$uname = str_replace("'", '', $array[0]);
						$tel = trim($array[1]);
						$country = trim($array[2]);
						$groupName = trim($array[3]);

						if(in_array($tel, $blackListArr)) {
							continue;
						}

						$values .= "('{$uname}', '{$country}', '{$tel}', {$uid}, '{$groupName}', 0),";
					}

				}
				if($values) {
					$values = trim($values, ",");
					$insert = $admin->import_sms_customer($values);
					$info = '导入数据成功';
				}
			}

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

		}

	}

	public function importBlackList(){

		$admin = new AdvertAdminAction();
		$uid   = 99;

		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->selectSmsBlackList($uid);
				$blackListArr = array_column($blackList, 'tel');
				$values = '';
				foreach($excel_array as $array){
					if(!empty($array[2])){
						$uname = str_replace("'", '', $array[0]);
						$tel = trim($array[1]);
						$country = trim($array[2]);
						$groupName = trim($array[3]);

						if(in_array($tel, $blackListArr)) {
							continue;
						}

						$values .= "('{$uname}', '{$country}', '{$tel}', {$uid}, '{$groupName}', 1),";
					}

				}
				if($values) {
					$values = trim($values, ",");
					$insert = $admin->import_sms_customer($values);
					$info = '导入数据成功';
				}
			}

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

		}

	}

	public function deleteByExcel(){

		$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");
				$telsArr = array();
				foreach($excel_array as $array){
					if(!empty($array[2])){
						$uname = str_replace("'", '', $array[0]);
						$tel = trim($array[1]);
						$country = trim($array[2]);
						$groupName = trim($array[3]);

						$telsArr[] = $tel;
						
					}

				}
				if($telsArr) {
					$telsStr = implode(',', $telsArr);
					$result = $admin->deleteSmsCustomerByExcel($telsStr, $uid);
					$info = '删除数据成功';
				}
			}

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

		}

	}

	public function deleteByGroup() {
		$admin = new AdvertAdminAction();
		$uid   = $_SESSION['user_infos']['id'];
		$groupName = $_POST['group_name'];
		
		if($groupName) {
			$delete_c = $admin->deleteSmsCustomerByGroup($groupName, $uid);
			$info = '删除 ' . $groupName . ' 客户分组成功';
		} else {
			$error = "删除失败";
		}
		header("Location:/?a=sms_customer_list_admin&m=remove&info=$info&error=$error");
		exit();
	}

	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'";
		}
		$where.=" and is_block=0";
		$list = $admin->selesctSmsCustomer($where);

		$d = date('Y-m-d',time());
		$this->generateEXCEL($d."导出客户数据",$list);

	}

	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', 'Name');
		$resultPHPExcel->getActiveSheet()->setCellValue('B1', 'Tel');
		$resultPHPExcel->getActiveSheet()->setCellValue('C1', 'Country');
		$resultPHPExcel->getActiveSheet()->setCellValue('D1', 'Group');

		//设置水平居中
		$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('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);


		$i = 2;
		foreach($data as $item){
			$resultPHPExcel->getActiveSheet()->setCellValue('A' . $i, $item['uname']);
			$resultPHPExcel->getActiveSheet()->setCellValue('B' . $i, $item['tel']);
			$resultPHPExcel->getActiveSheet()->setCellValue('C' . $i, $item['country']);
			$resultPHPExcel->getActiveSheet()->setCellValue('D' . $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()->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()->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/Excel2007.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();
					}


				}

				array_push($excel_array, $strs);
			}

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