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 nosendList(){

        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_send=2";
        $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("nosendList.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'] = trim($_POST['tel']);
			$data['group_name'] = trim($_POST['group_name']);
			$data['country'] = trim($_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 addBlack(){
        $admin = new AdvertAdminAction();
        if($_POST['tel']){
            $info = '添加失败';
            $uname = $_POST['username'];
            $tel = trim($_POST['tel']);
            $country = trim($_POST['country']);
            $group_name ='黑名单';
            $user = $admin->selectOneCustomerBymobile($tel);//查询手机号是否存在
           
            if($user){
                $add =$admin->updateSmsCustomerBlcak($user);
            }else{
                $uid =99;
                $values = "('{$uname}', '{$country}', '{$tel}', {$uid}, '{$group_name}', 1, 2)";
                $add =$admin->import_sms_customer($values);
            }
            if($add){
                $info = "添加成功";
                header("Location:/?a=sms_customer_list_admin&m=blacklist&info=$info");
                exit();
            }else{
                $this->assign('info',$info);
            }

        }
        $this->display('addBlack.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'] = trim($_POST['group_name']);
			$data['country'] = trim($_POST['country']);
			$data['tel'] = trim($_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();
        $city_code =$admin->selectCityCode();
        $new=array();
        foreach ($city_code as $v){
            $new[]=$v['code'];
        }
        $is_send=1;
		$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;
						}
						if($country=='United States'||$country=='Canada'){
                            $code = substr($tel , 0 , 3);
                            if (in_array($code, $new)) {
                                $is_send=1;
                            }else{
                                $is_send=2;
                            }
                        }
						$values .= "('{$uname}', '{$country}', '{$tel}', {$uid}, '{$groupName}', 0, '{$is_send}'),";
					}

				}

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

						if(in_array($tel, $blackListArr)) {
							continue;
						}
						$telsArr[] = $tel;

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

				}
				if($telsArr) {
					$telsStr = implode(',', $telsArr);
					$result = $admin->deleteSmsCustomerByTel($telsStr);
					$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[0])){
						$tel = trim($array[0]);

						$telsArr[] = $tel;
						
					}

				}
				if($telsArr) {
					$telsStr = "'" . implode("','", $telsArr) . "'";
					$result = $admin->deleteSmsCustomerByTel($telsStr);
					$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;
	}

	public function exportBlackList(){
		$admin = new AdvertAdminAction();

		$list = $admin->getAllSmsBlacklist();

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

	}
}