ZZWX/monitor/Monitor/Admin/Controller/MonitorController.class.php

<?php
namespace Admin\Controller;
use Think\Controller;
class MonitorController extends Controller {
	public function _empty($name){
		//把所有城市的操作解析到city方法
		$this->display('Monitor/error404');
	}
	public function index(){
		$this->redirect("monitorList");
	}
	public function monitorList(){
		
		$map['site_name']=I("request.site_name");
		
		$map['hotel_name']=$_REQUEST['hotel_name'];
		$encode = mb_detect_encoding($map['hotel_name'], array("UTF-8","ASCII","GB2312","GBK","BIG5"));
		if ($encode !="UTF-8"){
			$map['hotel_name']=mb_convert_encoding($map['hotel_name'],"UTF-8",$encode);
		}
		//$monitor=M("Type");
		//$res=$monitor->select();
		
		//$res=$monitor->query($sql);
		//$this->assign("monitorList",$res);
	
		$monitor=M();
		
		$sql="select s.site_name,h.url,h.hotel_name,q.query_date,r.room_type,r.room_price,r.site_price,r.breakfast from room r inner join queryDate q on r.date_id= q.id inner join hotel h on q.hotel_id=h.id inner join site s on h.site_id=s.id where q.query_date>(GETDATE()-1)";
		if($map['site_name'] >0 ){
			$sql="select s.site_name,h.url,h.hotel_name,q.query_date,r.room_type,r.room_price,r.site_price,r.breakfast from room r inner join queryDate q on r.date_id= q.id inner join hotel h on q.hotel_id=h.id inner join site s on h.site_id=s.id where s.id=".$map['site_name']."and q.query_date>(GETDATE()-1)";
		}
		if ($map['hotel_name'] !=""){
			$sql="select s.site_name,h.url,h.hotel_name,q.query_date,r.room_type,r.room_price,r.site_price,r.breakfast from room r inner join queryDate q on r.date_id= q.id inner join hotel h on q.hotel_id=h.id inner join site s on h.site_id=s.id where h.hotel_name like '%".$map['hotel_name']."%'"."and q.query_date>(GETDATE()-1)";
		}
		if($map['site_name'] >0 && $map['hotel_name'] !=""){
			$sql="select s.site_name,h.url,h.hotel_name,q.query_date,r.room_type,r.room_price,r.site_price,r.breakfast from room r inner join queryDate q on r.date_id= q.id inner join hotel h on q.hotel_id=h.id inner join site s on h.site_id=s.id where s.id=".$map['site_name']." and h.hotel_name like '%".$map['hotel_name']."%'"."and q.query_date>(GETDATE()-1)";
		}
		
		$count= count($monitor->query($sql));// 查询满足要求的总记录数
		$Page= new \Think\Page($count,30);// 实例化分页类 传入总记录数和每页显示的记录数(25)
	

		
		$whereNotIn="select top ".$Page->firstRow." a.id from room a inner join queryDate b on a.date_id= b.id inner join hotel c on b.hotel_id=c.id inner join site d on c.site_id=d.id";
		$sql1="select top ".$Page->listRows." r.id,s.site_name,h.url,h.hotel_name,q.query_date,r.room_type,r.room_price,r.site_price,r.breakfast from room r inner join queryDate q on r.date_id= q.id inner join hotel h on q.hotel_id=h.id inner join site s on h.site_id=s.id  where r.id not in (".$whereNotIn.")"."and q.query_date>(GETDATE()-1)";
		if($map['site_name'] >0 ){
			$whereNotIn="select top ".$Page->firstRow." a.id from room a inner join queryDate b on a.date_id= b.id inner join hotel c on b.hotel_id=c.id inner join site d on c.site_id=d.id where d.id=".$map['site_name'];
			$sql1="select top ".$Page->listRows." r.id,s.site_name,h.url,h.hotel_name,q.query_date,r.room_type,r.room_price,r.site_price,r.breakfast from room r inner join queryDate q on r.date_id= q.id inner join hotel h on q.hotel_id=h.id inner join site s on h.site_id=s.id  where r.id not in (".$whereNotIn.")"."and q.query_date>(GETDATE()-1)";
			$sql1.=" and s.id=".$map['site_name'];
		}
		if ($map['hotel_name'] !=""){
			$whereNotIn="select top ".$Page->firstRow." a.id from room a inner join queryDate b on a.date_id= b.id inner join hotel c on b.hotel_id=c.id inner join site d on c.site_id=d.id where c.hotel_name like '%".$map['hotel_name']."%'";
			$sql1="select top ".$Page->listRows." r.id,s.site_name,h.url,h.hotel_name,q.query_date,r.room_type,r.room_price,r.site_price,r.breakfast from room r inner join queryDate q on r.date_id= q.id inner join hotel h on q.hotel_id=h.id inner join site s on h.site_id=s.id  where r.id not in (".$whereNotIn.")"."and q.query_date>(GETDATE()-1)";
			$sql1.=" and h.hotel_name like '%".$map['hotel_name']."%'";
		}
		if ($map['site_name'] >0 && $map['hotel_name'] !=""){
			$whereNotIn="select top ".$Page->firstRow." a.id from room a inner join queryDate b on a.date_id= b.id inner join hotel c on b.hotel_id=c.id inner join site d on c.site_id=d.id where d.id=".$map['site_name']." and  c.hotel_name like '%".$map['hotel_name']."%'";
			$sql1="select top ".$Page->listRows." r.id,s.site_name,h.url,h.hotel_name,q.query_date,r.room_type,r.room_price,r.site_price,r.breakfast from room r inner join queryDate q on r.date_id= q.id inner join hotel h on q.hotel_id=h.id inner join site s on h.site_id=s.id  where r.id not in (".$whereNotIn.")"."and q.query_date>(GETDATE()-1)";
			$sql1.=" and s.id=".$map['site_name']." and h.hotel_name like '%".$map['hotel_name']."%'";
		}
		
		$sql1.= " order by r.id";
		// 进行分页数据查询 注意limit方法的参数要使用Page类的属性
		//$list = $monitor->where('status=1')->order('id')->limit($Page->firstRow.','.$Page->listRows)->select();
		$list=$monitor->query($sql1);
		//$list=$monitor->field( "type.id,site.site_name,hotel.hotel_name,type.room_type,hotel.url,type.room_price" ) ->join( "hotel on type.hotel_id=hotel.id" ) ->join( "site on site.id=hotel.id" ) ->limit($Page->firstRow.",".$Page->listRows)->order("type.id") ->select();

		
		$Page->setConfig('prev', '上一页');
		$Page->setConfig('next', '下一页');
		$Page->setConfig('first', '首页');
		$Page->setConfig('last', '尾页');
		$Page -> setConfig('theme','%HEADER% %FIRST% %UP_PAGE% %LINK_PAGE% %DOWN_PAGE% %END%');
		foreach ($map as $key =>$val){
			$Page->parameter[$key]=$val;
		}
		$show= $Page->show();// 分页显示输出
		$this->assign('list',$list);// 赋值数据集
		$this->assign('page',$show);// 赋值分页输出
		$sql="select * from site order by id ";
		$siteRes=$monitor->query($sql);
		array_unshift($siteRes, array("id"=>0,"site_name"=>"所有"));
		$this->assign("site",$siteRes);
		$this->assign("site_name",$map['site_name']);
		$this->assign("hotel_name",$map['hotel_name']);
		$this->display();
	}
	public function monitorDel(){
		$id=I("get.id");
		$monitor=M();
		//$res=$monitor->select();
		$sql="delete from room where id=".$id;
		$res=$monitor->execute($sql);
	   if ($res>0){
	        echo json_encode(array("errorCode"=>0,"info"=>"删除成功"));
	        exit;
	    }else {
	        echo json_encode(array("errorCode"=>1,"info"=>"删除失败"));
	        exit;
	    }
	}
	public function monitorAdd(){
		
		$monitor=M();
		if (!empty($_POST)){
 			$validate = array(				
 					array('hotel_name','require','酒店名称不能为空！'), // 仅仅需要进行验证码的验证
 					array('room_type','require','房间类型不能为空！'), // 仅仅需要进行验证码的验证
 					array('url','require','url不能为空！'), // 仅仅需要进行验证码的验证
 					array('price','require','价格不能为空！'), // 仅仅需要进行验证码的验证
 			);
 			$monitor-> setProperty("_validate",$validate);
			if($_POST['id']==""){
				$hotel=M("Hotel");
				$siteid=I("post.site_name");
				$url=I("post.url_addr");
				$hotelname=I("post.hotel_name");
				$id=$hotel->where("site_id=".$siteid." and url='".$url."'")->getField('id');
				if($id){
				    $queryDateData['hotel_id']=$id;
				}else {
				    $hotelData['site_id']=$siteid;
				    $hotelData['hotel_name']=I("post.hotel_name");
				    $hotelData['url']=$url;
				    $insert=$hotel->add($hotelData);
				    $queryDateData['hotel_id']=$insert;
				}
				$priceStr=I("price");
				$priceArraystr=explode('|', $priceStr);
				for ($index=0; $index<count($priceArraystr); $index++){
				    $datePrice = $priceArraystr[$index];
				    $arr = explode("P", $datePrice);
				    $temp_date = $arr[0];
				    $price = $arr[1];
				    $date = date('Y-m-d',strtotime($temp_date));			    
				    $queryDate=M("QueryDate");				
				    $queryDateData['query_date'] = $date;
				    $queryDateId=$queryDate->table("queryDate")->where("hotel_id='".$queryDateData['hotel_id']."' and query_date='".$date."'")->getField('id');
				    if ($queryDateId){
				        $roomData['date_id']=$queryDateId;
				    }else{
				        $in=$queryDate->table("queryDate")->add($queryDateData);
				        if($in){
				            $roomData['date_id']=$in;
				        }else{
				            echo json_encode(array("errorCode"=>1,"errorInfo"=>"保存失败"));
				            exit;
				        }
				    }
				    $room=M("Room");
				    $roomData['room_type']=I("post.room_type");
				    $roomData['room_price']=$price;
				    $roomData['breakfast']=I("post.breakfast");
				    $roomRes=$room->where("date_id='".$roomData['date_id']."' and room_type='".$roomData['room_type']."' and breakfast='".$roomData['breakfast']."'")->getField('id');
				    
				    if ($roomRes){
				        $roomsave=$room->where("id=".$roomRes)->save($roomData);
				    }else{
				        $roomInsert=$room->add($roomData);
				        if ($roomInsert){
				        }else{
				            echo json_encode(array("errorCode"=>1,"errorInfo"=>"保存失败"));
				            exit;
				        }
				    }
				}
				echo json_encode(array("errorCode"=>0,"errorInfo"=>"保存成功"));
				exit;
				
			}else{
				$id=I("post.id");
				$hid=I("post.hid");
				$hotel=M("Hotel");
				$siteid=I("post.site_name");
				$hotelname=I("post.hotel_name");
				$url=I("post.url_addr");
				$hotelData['site_id']=$siteid;
				$hotelData['hotel_name']=I("post.hotel_name");
				$hotelData['url']=$url;
				$hotelsave=$hotel->where("id=".$hid)->save($hotelData);
				if ($hotelsave !== false){
				}else {
				    echo json_encode(array("errorCode"=>1,"info"=>"更新失败"));
				    exit;
				}
				$room=M("Room");
				$roomData['room_type']=I("post.room_type");
				$roomData['room_price']=I("post.price");
				$roomData['breakfast']=I("post.breakfast");
				$roomData['hotel_id']=$id;
				$roomsave=$room->where("id=".$id)->save($roomData);	
				if ($roomsave !== false){
					echo json_encode(array("errorCode"=>0,"info"=>"更新成功"));
					exit;
				}else {
					echo json_encode(array("errorCode"=>1,"info"=>"更新失败"));
					exit;
				}
			}
			
		}else {
			$sql="select * from site order by id ";
			$res=$monitor->query($sql);
			$this->assign("site",$res);
			//$this->redirect("monitorAdd");
			$this->display("monitorAdd");
		}
	}
	public function monitorEdit(){
		if (!empty($_POST)){			
		}else{			
			$id=I("get.id");
			$monitor=M();
			$sql="select h.id as hid,r.id as rid,s.site_name,h.hotel_name,q.query_date,r.room_type,h.url,r.room_price,r.breakfast from room r inner join queryDate q on r.date_id= q.id inner join hotel h on q.hotel_id=h.id inner join site s on h.site_id=s.id where r.id=".$id;
			$res=$monitor->query($sql);
			$this->assign("edit",$res[0]);
			//print_r($res);
			$sql="select * from site order by id";
			$res=$monitor->query($sql);
			$this->assign("site",$res);
			$this->display("monitorEdit");
		}
	}
	
	public function monitorImport(){
		if ( IS_POST){
			$import=I('post.import');
			if ($import =="导入"){
				if ($_FILES['importFile']['error'] ==0){
					$response=upload();
					if($response[0]==1){
						$type=strrchr($response[1], ".");
						import('Org.PHPExcel.PHPExcel');
						if ($type==".xlsx")
							$objReader = \PHPExcel_IOFactory::createReader('Excel2007');
						else 
							$objReader = \PHPExcel_IOFactory::createReader('Excel5');
						$objReader->setReadDataOnly(true);
						$objPHPExcel = $objReader->load($response[1]);
						$objWorksheet = $objPHPExcel->getActiveSheet();
						$highestRow = $objWorksheet->getHighestRow();
						$highestColumn = $objWorksheet->getHighestColumn();
						$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
						$excelData = array();
						for ($row = 1; $row<= $highestRow;$row++){
							for ($col=0;$col<$highestColumnIndex;$col++) {
								$excelData[$row][]=(string)$objWorksheet->getCellByColumnAndRow($col,$row)->getValue();
							}
						}
						$data=array();
						$result=array(0,0);
						unset($excelData[1]);
						$Monitor=M('Monitor');
						foreach ($excelData as $k=>$v){
							$data['site_name']=$v[0];
							$data['hotel_name']=$v[1];
							$data['room_type']=$v[2];
							$data['url']=$v[3];
							$data['price']=$v[4];
							$res=$Monitor->add($data);
							if ($res>0){
								$result[0]+=1;
							}else {
								$result[1]+=1;
							}
						}
						$this->success("导入成功".$result[0]."条"."<br>导入失败".$result[1]."条","monitorList",3);
					}else{
						$this->error($response[1]);
					}
				}else if($_FILES['importFile']['error'] ==4){
					$this->error("请选择上传文件");
				}else{
					$this->error("上传异常");
				}
			}
		}
	}
	public function priceList(){
	    $map['site_name']=I("request.site_name");
	    
	    $map['hotel_name']=$_REQUEST['hotel_name'];
	    $encode = mb_detect_encoding($map['hotel_name'], array("UTF-8","ASCII","GB2312","GBK","BIG5"));
	    if ($encode !="UTF-8"){
	        $map['hotel_name']=mb_convert_encoding($map['hotel_name'],"UTF-8",$encode);
	    }
	    $map['room_type']=$_REQUEST['room_type'];
	    $encode = mb_detect_encoding($map['room_type'], array("UTF-8","ASCII","GB2312","GBK","BIG5"));
	    if ($encode !="UTF-8"){
	        $map['room_type']=mb_convert_encoding($map['room_type'],"UTF-8",$encode);
	    }
	    $map['breakfast_type']=$_REQUEST['breakfast_type'];
// 	    if($_REQUEST['breakfast_type']){
// 	       $map['breakfast_type']=$_REQUEST['breakfast_type'];
// 	    }else{
// 	        $map['breakfast_type'] = -1;
// 	    }
	    $map['start_time']=$_REQUEST['start_time'];
	    $map['end_time']=$_REQUEST['end_time'];
	    $map['date_type']=$_REQUEST['date_type'];
	    $monitor=M();
	    if($map['date_type'] == 1){
	    	$sql="select r.id,s.site_name,h.hotel_name,h.url,r.room_type,r.breakfast,q.query_date,r.room_price,r.site_price from room r inner join queryDate q on r.date_id=q.id inner join hotel h on q.hotel_id= h.id inner join site s on h.site_id=s.id where r.flag=0";
	    }else{
	    	$sql="select r.id,s.site_name,h.hotel_name,h.url,r.room_type,r.breakfast,q.query_date,r.room_price,r.site_price from room r inner join queryDate q on r.date_id=q.id inner join hotel h on q.hotel_id= h.id inner join site s on h.site_id=s.id where r.flag=1";
	    }
 	    if($map['site_name'] >0 ){
 	        $sql.= " and s.id=".$map['site_name'];
	    }
	    if ($map['hotel_name'] !=""){
	        $sql.= " and h.hotel_name like '%".$map['hotel_name']."%'";
	    }
	    if($map['room_type'] !=""){
            $sql.= " and r.room_type='".$map['room_type']."'";
	    }
	    if($map['breakfast_type'] !="" && $map['breakfast_type'] !="-1"){
	        $sql.= " and r.breakfast='".$map['breakfast_type']."'";
	    }
	    if($map['start_time'] !="" && $map['end_time'] !=""){
	        $sql.= "and q.query_date>='".$map['start_time']."'";
	        $sql.= "and q.query_date<='".$map['end_time']."'";
	    }else{
	        $sql.= "and q.query_date>(GETDATE()-1)";
	    }
 	    $sql.= " order by r.id";
	    $list=$monitor->query($sql);
	    for ($index=0; $index<count($list); $index++){
	        $list[$index]["query_date"]=$list[$index]["query_date"].$this->wk($list[$index]["query_date"]);
	        $list[$index]["dwk"]=$this->dwk($list[$index]["query_date"]);
	    }
	    $this->assign('list',$list);// 赋值数据集
	    $this->assign('json',json_encode($list));// 赋值数据集
	    $sql="select * from site order by id ";
	    $siteRes=$monitor->query($sql);
	    array_unshift($siteRes, array("id"=>0,"site_name"=>"所有"));
	    $this->assign("site",$siteRes);
	    $this->assign("site_name",$map['site_name']);
	    $this->assign("hotel_name",$map['hotel_name']);
	    $this->assign("room_type",$map['room_type']);
	    $this->assign("breakfast",$map['breakfast_type']);
	    $this->assign("start_time",$map['start_time']);
	    $this->assign("end_time",$map['end_time']);
	    $this->assign("date_type",$map['date_type']);
	    $this->display();
	}
	public function monitorSave(){
	    $id=I("get.id");
	    $price=I("get.price");
	    $room=M("Room");
	    $data[room_price]=$price;
	    $data[flag]=0;
	    $roomsave=$room->where("id=".$id)->save($data); 	
	    if ($roomsave !==false){
	        echo json_encode(array("errorCode"=>0,"info"=>"保存成功"));
	        exit;
	    }else {
	        echo json_encode(array("errorCode"=>1,"info"=>"保存失败"));
	        exit;
	    }	    
	}
	public function monitorSaveAll(){
	    $priceStr=I("get.price");
	    $room=M("Room");
	    $priceArraystr=explode('|', $priceStr);
	    for ($index=0; $index<count($priceArraystr); $index++){
	        $datePrice = $priceArraystr[$index];
	        $arr = explode("P", $datePrice);
	        $data[room_price]=$arr[0];
	        $data[flag]=0;
	        $roomsave=$room->where("id=".$arr[1])->save($data);
	        if ($roomsave !==false){
	            //echo json_encode(array("errorCode"=>0,"info"=>"保存成功"));
	        }else {
	            echo json_encode(array("errorCode"=>1,"info"=>"保存失败"));
	            exit;
	        }
	    }
	    echo json_encode(array("errorCode"=>0,"info"=>"保存成功"));
	}
	public function monitorDelete(){
	    $id=I("get.id");
	    $monitor=M();
	    $sql="delete from room where id=".$id;
	    $res=$monitor->execute($sql);
	    if ($res>0){
	        echo json_encode(array("errorCode"=>0,"info"=>"删除成功"));
	        exit;
	    }else {
	        echo json_encode(array("errorCode"=>1,"info"=>"删除失败"));
	        exit;
	    }
	}
	function wk($date1) {
	    $datearr = explode("-",$date1);     //将传来的时间使用“-”分割成数组
	    $year = $datearr[0];       //获取年份
	    $month = sprintf('%02d',$datearr[1]);  //获取月份
	    $day = sprintf('%02d',$datearr[2]);      //获取日期
	    $hour = $minute = $second = 0;   //默认时分秒均为0
	    $dayofweek = mktime($hour,$minute,$second,$month,$day,$year);    //将时间转换成时间戳
	    $shuchu = date("w",$dayofweek);      //获取星期值
	    $weekarray=array("星期日","星期一","星期二","星期三","星期四","星期五","星期六");
	    return "(".$weekarray[$shuchu].")";
	}
	function dwk($date1) {
		$datearr = explode("-",$date1);     //将传来的时间使用“-”分割成数组
		$year = $datearr[0];       //获取年份
		$month = sprintf('%02d',$datearr[1]);  //获取月份
		$day = sprintf('%02d',$datearr[2]);      //获取日期
		$hour = $minute = $second = 0;   //默认时分秒均为0
		$dayofweek = mktime($hour,$minute,$second,$month,$day,$year);    //将时间转换成时间戳
		$shuchu = date("w",$dayofweek)+'';      //获取星期值
		$weekarray=array("星期日","星期一","星期二","星期三","星期四","星期五","星期六");
		return $shuchu;
	}
}