ComplainsController.php

/app/Controller/ComplainsController.php

https://bitbucket.org/dosm123/crm
PHP | 492 lines | 442 code | 27 blank | 23 comment | 162 complexity | 45ea9376c7b8df6454517238f54ff777 MD5 | raw file
Possible License(s): LGPL-3.0, GPL-3.0, LGPL-2.1

<?php

class ComplainsController extends AppController{
  public $name = 'Complains';
  public $paginate = array(
			   'limit' => 25,
			   'order' => array(
					    'Complain.id' => 'desc'
					    ),
			   'recursive' => 2,
			   );
  
  public function index(){
    $this->loadModel('Board');
    $this->loadModel('Life');
    $this->loadmodel('User');
    $this->loadmodel('UserInfo');
    $this->loadmodel('Complex');
    $this->loadModel('BottomCategory');
    $this->loadModel('MidCategory');
    $this->loadModel('TopCategory');
    $data = null;
    $conditions = array();
    if($this->request->query){
      $data = $this->request->query['data'];
      $type = $data['Search']['type'];
      $input = $data['Search']['keyword'];
      if($type == "name"){
	$complex = array_keys($this->Complex->find('list', array('conditions' => array('Complex.name LIKE' => "%$input%"))));
	$conditions = array('or'=> array('Board.complex_id' => $complex,
					 'Life.complex_id' => $complex));
      }
      else if($type == "serial" || $type == "mac") {
	$conditions = array("or" => array("Board.$type LIKE" => "%$input%",
					  "Life.$type LIKE" => "%$input%"));
      }
      else if($type == "engineer") {
        $userinfo = array_keys($this->UserInfo->find('list', array('conditions' => array('UserInfo.name LIKE' => "%$input%"))));
	$conditions = array("Complain.engineer_id" => $userinfo);
      }
      else {
	$conditions = array("Complain.$type LIKE" => "%$input%");
      }
      if(!isset($data['Filter'])){
	$data['Filter'] = array('midcategory' => 'default', 'topcategory' => 'default',  'status' => 'default',  'method' => 'default', 'region_name' => 'default', 'media' => 'default');
      }
      foreach ( array_keys($data['Filter']) as $key) {
	if($data['Filter'][$key] != "default"){
	  // need a special process for foreignKeys
	  if($key == 'method'){
	    $conditions['or'] = array('Board.method' => $data['Filter'][$key],
				      'Life.method' => $data['Filter'][$key]);
	  }
	  else if($key == 'region_name'){
	    $ids = array_keys($this->Complex->find('list', array('conditions'=>array('region_name'=>$data['Filter']['region_name']))));
	    $conditions['or']['Board.complex_id'] = $ids;
	    $conditions['or']['Life.complex_id'] = $ids;
	  }
	  else if($key == 'topcategory' || $key == 'midcategory'){
	    $tmp_cond = null;
	    if($data['Filter']['topcategory'] != 'default' and $data['Filter']['midcategory'] != 'default'){
	      $top_ids = array_keys($this->BottomCategory->TopCategory->find('list', array('conditions' => array('category' => $data['Filter']['topcategory']))));
	      $mid_ids = array_keys($this->BottomCategory->MidCategory->find('list', array('conditions' => array('category' => $data['Filter']['midcategory']))));
	      $tmp_cond = array('top_category_id' => $top_ids,
				'mid_category_id' => $mid_ids);
	    }
	    else if($data['Filter']['topcategory'] != 'default'){
	      $top_ids = array_keys($this->BottomCategory->TopCategory->find('list', array('conditions' => array('category' => $data['Filter']['topcategory']))));
	      $tmp_cond = array('top_category_id' => $top_ids);
	    }
	    else if($data['Filter']['midcategory'] != 'default'){
	      $mid_ids = array_keys($this->BottomCategory->MidCategory->find('list', array('conditions' => array('category' => $data['Filter']['midcategory']))));
	      $tmp_cond = array('mid_category_id' => $mid_ids);
	    }
	    $bottoms = array_keys($this->BottomCategory->find('list', array('fields' => array('BottomCategory.category'), 'conditions'=>$tmp_cond)));
	    $conditions['Complain.bottom_category_id'] = $bottoms;
	  }
	  else if($key == 'media'){
	    if($data['Filter']['media'] == '보드'){
	      $conditions['not'] = array('Complain.board_id' => null);
	    }
	    else if ($data['Filter']['media'] == '라이프'){
	      $conditions['not'] = array('Complain.life_id' => null);
	    }
	  }
	  else if($key == 'status'){
	    if($data['Filter']['status'] == '0'){
	      $conditions['Complain.status'] = 0;
	    }
	    else if ($data['Filter']['status'] == '1'){
	      $conditions['Complain.status'] = 1;
	    }
	    else if ($data['Filter']['status'] == '2'){
	      $conditions['Complain.status'] = 2;
	    }
	    else if ($data['Filter']['status'] == '3'){
	      $conditions['Complain.status'] = 3;
	    }
	  }
	  else if($key == 'start_date'){
	        if($data['Filter'][$key]){
			$date = date('Y-m-d', strtotime($data['Filter'][$key]));
			$conditions['Complain.reg_date >='] = $date;
		}
	  }
	  else if($key == 'end_date'){
	        if($data['Filter'][$key]){
			$date = date('Y-m-d', strtotime($data['Filter'][$key]) + 86400);
		     	$conditions['Complain.reg_date <='] = $date;
		}
	  }
	}
      }
      // 검색 혹은 필터 내용 있으면 검색 처리
    }
    $this->paginate['conditions'] = $conditions;
        /* paginate 때문에 붙인거 */
    $this->Complain->unbindModel(array('hasMany' => array('Cs'), 'belongsTo' => array('CsBottomCategory')));
    $this->Complain->EngineerInfo->unbindModel(array('hasOne'=> array('User'), 'hasMany' => array('Complex', 'Complain')));
    $this->Complain->ReceiverInfo->unbindModel(array('hasOne'=> array('User'), 'hasMany' => array('Complex', 'Complain')));
    $this->Board->unbindModel(array('hasMany' => array('Complain')));
    $this->Life->unbindModel(array('hasMany' => array('Complain')));
    
    $this->set('complains', $this->paginate('Complain'));
    $this->set('medias', array('보드', '라이프'));
    $this->set('methods', Set::extract('/boards/method', $this->Board->query('select distinct method from boards')));
    $this->set('regions' , Set::extract('/complexes/region_name', $this->Complex->query('select distinct region_name from complexes')));
    $this->set('topcategories' , Set::extract('/top_categories/category', $this->TopCategory->query('select distinct category from top_categories')));
    
    $this->set('midcategories', Set::extract('/mid_categories/category', $this->MidCategory->query('select distinct category from mid_categories')));
    /* $this->set('methods', array_unique( */
    /* 				       array_merge(Set::extract('/Board/method', $complains), */
    /* 						   Set::extract('/Life/method', $complains)) */
    /* 				       ) */
    /* 	       ); */
    /* $this->set('regions', array_unique( */
    /* 				      array_merge(Set::extract('/Board/Complex/region_name', $complains), */
    /* 				      Set::extract('/Life/Complex/region_name', $complains)) */
    /* 			 	      )); */
    /* $this->set('topcategories', array_unique(Set::extract('/BottomCategory/TopCategory/category', $complains))); */
    /* $this->set('midcategories', array_unique(Set::extract('/BottomCategory/MidCategory/category', $complains))); */
    $this->set('searchinfo', $data);
  }
   public function add(){
     if($this->request->is('post')){
       foreach ( array_keys($this->request->data['Complain']) as $key ) {
	 if($this->request->data['Complain'][$key]['media'] == 'board'){
	   $this->request->data['Complain'][$key]['board_id'] = $this->request->data['Complain'][$key]['device_id'];
	 }
	 else if($this->request->data['Complain'][$key]['media'] == 'life'){
	   $this->request->data['Complain'][$key]['life_id'] = $this->request->data['Complain'][$key]['device_id'];
	 }
	 else {
	   $this->Session->setFlash('저장에 실패하였습니다');
	 }
       }
       if($this->Complain->saveAll($this->request->data['Complain'])){
	 $this->redirect('/complains');
       }
       else{
	 $this->Session->setFlash('저장에 실패하였습니다');
       }
     }
     $this->loadModel('TopCategory');
     $this->set('tops', $this->TopCategory->find('all'));
     $this->set('mids', $this->TopCategory->MidCategory->find('all'));
     $this->set('bottoms', $this->TopCategory->MidCategory->BottomCategory->find('all'));  
   }
   public function delete($complain_id){
     $complain = $this->Complain->findByid($complain_id);
     $this->Complain->id = $complain['Complain']['id'];
     if($this->Complain->delete()){
       $this->redirect('/complains');
     }

  }

  public function edit($boardlife , $boardlife_id , $complain_id){
    $this->loadModel('Complex');
    $this->loadModel('TopCategory');
    $this->set('tops', $this->TopCategory->find('all'));
    $this->set('mids', $this->TopCategory->MidCategory->find('all'));
    $this->set('bottoms', $this->TopCategory->MidCategory->BottomCategory->find('all', array('recursive' => 0)));  
    $complain = $this->Complain->findById($complain_id);
    if(!parent::array_empty($complain['Board'])){
      $complex_id = $complain['Board']['complex_id'];
      $media = 'board';
    }
    else if(!parent::array_empty($complain['Life'])){
      $complex_id = $complain['Life']['complex_id'];
      $media = 'life';
    }
    $this->set('complain', $complain);
    $this->set('media', $media);
    $this->set('complex', $this->Complex->findById($complex_id));
    if($this->request->is('post')){
      if($this->request->data['Complain']['bottom_category_id'] == 'default'){
	$this->request->data['Complain']['bottom_category_id'] = 1;
      }
      $this->Complain->id = $complain_id;

      if($this->Complain->save($this->request->data['Complain'])){
	$this->redirect('/complains/view/'.$boardlife.'/'.$boardlife_id.'/'.$complain_id);
      }
    }
  }
  public function view($media, $media_id, $complain_id=NULL){
    $this->loadModel('Complex');
    if($media == 'board'){
        $this->loadModel('Board');
	$boardlife = $this->Board->findById($media_id);
	$complains = $this->Complain->find('all', array('conditions' => array('board_id' => $media_id)));
    }
    else if($media == 'life'){
        $this->loadModel('Life');
	$boardlife = $this->Life->findById($media_id);
	$complains = $this->Complain->find('all', array('conditions' => array('life_id' => $media_id)));
    }
    $complex_id = $boardlife['Complex']['id'];
    $this->loadModel('UserInfo');
    $this->loadModel('TopCategory');
    $this->loadModel('BottomCategory');
    $this->loadModel('CsMaterial');
    $this->loadModel('CsAdditionalInfo');
    $this->set('tops', $this->TopCategory->find('all'));
    $this->set('mids', $this->TopCategory->MidCategory->find('all'));
    $this->set('bottoms', $this->BottomCategory->find('all'));
    $complex = $this->Complex->findById($complex_id);
    $this->set('complex', $complex);
    $this->set('installer', $this->UserInfo->findById($complex['Complex']['installer_id']));
    $this->set('users', $this->UserInfo->find('all'));
    $this->set('complains', $complains);
    $this->set('materials', $this->CsMaterial->find('all'));
    $this->set('additions', $this->CsAdditionalInfo->find('all'));
    $this->set('media', $media);
    $this->set('media_id', $media_id);
    $this->set('boardlife', $boardlife);
    $this->set('complain_id', $complain_id);
  }
  public function ajaxAdd(){
    if($this->request->is('post')){
      if($this->request->data['media'] == 'board'){
	$this->request->data['board_id'] = $this->request->data['device_id'];
      }
      else if($this->request->data['media'] == 'life'){
	$this->request->data['life_id'] = $this->request->data['device_id'];
      }
      else {
	$this->Session->setFlash('저장에 실패하였습니다');
      }
      if($this->request->data['bottom_category_id'] == 'default'){
	$this->request->data['bottom_category_id'] = 1;
      }
      $this->request->data['name'] = parent::eucconv($this->request->data['name']);
      $this->request->data['phone'] = parent::eucconv($this->request->data['phone']);
      $this->request->data['title'] = parent::eucconv($this->request->data['title']);
      $this->request->data['comment'] = parent::eucconv($this->request->data['comment']);

      if($this->Complain->save($this->request->data)){
	echo 'good';
      }
      else{
	echo 'bad';
      }
    }
    $this->render('/complains/ajaxAdd', 'ajax');
  }
  
  public function excelOut(){
    $this->loadModel('Complex');
    $this->loadModel('Board');
    $this->loadModel('BottomCategory');
    $this->loadModel('CsBottomCategory');
    $this->loadModel('EngineerInfo');
    $this->loadModel('ReceiverInfo');
    $this->loadModel('Life');
    $this->loadModel('UserInfo');

    $currentTime = date("Y-m-d H:i:s");
    date_default_timezone_set('Asia/Seoul');
    
    $users = $this->UserInfo->find('list', array('fields'=> array('UserInfo.name')));

    $conditions = array();
    if($this->request->query){
      $data = $this->request->query['data'];
      $type = $data['Search']['type'];
      $input = $data['Search']['keyword'];
      if($type == "name"){
	$complex = array_keys($this->Complex->find('list', array('conditions' => array('Complex.name LIKE' => "%$input%"))));
	$conditions = array('or'=> array('Board.complex_id' => $complex,
					 'Life.complex_id' => $complex));
      }
      else if($type == "serial" || $type == "mac") {
	$conditions = array("or" => array("Board.$type LIKE" => "%$input%",
					  "Life.$type LIKE" => "%$input%"));
      }
      else if($type == "engineer") {
	$userinfo = array_keys($this->UserInfo->find('list', array('conditions' => array('UserInfo.name LIKE' => "%$input%"))));
	$conditions = array("Complain.engineer_id" => $userinfo);
      }
      else {
	$conditions = array("Complain.$type LIKE" => "%$input%");
      }
      if(!isset($data['Filter'])){
	$data['Filter'] = array('midcategory' => 'default', 'topcategory' => 'default',  'status' => 'default',  'method' => 'default', 'region_name' => 'default', 'media' => 'default');
      }
      foreach ( array_keys($data['Filter']) as $key) {
	if($data['Filter'][$key] != "default"){
	  // need a special process for foreignKeys
	  if($key == 'method'){
	    $conditions['or'] = array('Board.method' => $data['Filter'][$key],
				      'Life.method' => $data['Filter'][$key]);
	  }
	  else if($key == 'region_name'){
	    $ids = array_keys($this->Complex->find('list', array('conditions'=>array('region_name'=>$data['Filter']['region_name']))));
	    $conditions['or']['Board.complex_id'] = $ids;
	    $conditions['or']['Life.complex_id'] = $ids;
	  }
	  else if($key == 'topcategory' || $key == 'midcategory'){
	    $tmp_cond = null;
	    if($data['Filter']['topcategory'] != 'default' and $data['Filter']['midcategory'] != 'default'){
	      $top_ids = array_keys($this->BottomCategory->TopCategory->find('list', array('conditions' => array('category' => $data['Filter']['topcategory']))));
	      $mid_ids = array_keys($this->BottomCategory->MidCategory->find('list', array('conditions' => array('category' => $data['Filter']['midcategory']))));
	      $tmp_cond = array('top_category_id' => $top_ids,
				'mid_category_id' => $mid_ids);
	    }
	    else if($data['Filter']['topcategory'] != 'default'){
	      $top_ids = array_keys($this->BottomCategory->TopCategory->find('list', array('conditions' => array('category' => $data['Filter']['topcategory']))));
	      $tmp_cond = array('top_category_id' => $top_ids);
	    }
	    else if($data['Filter']['midcategory'] != 'default'){
	      $mid_ids = array_keys($this->BottomCategory->MidCategory->find('list', array('conditions' => array('category' => $data['Filter']['midcategory']))));
	      $tmp_cond = array('mid_category_id' => $mid_ids);
	    }
	    $bottoms = array_keys($this->BottomCategory->find('list', array('fields' => array('BottomCategory.category'), 'conditions'=>$tmp_cond)));
	    $conditions['Complain.bottom_category_id'] = $bottoms;
	  }
	  else if($key == 'media'){
	    if($data['Filter']['media'] == '보드'){
	      $conditions['not'] = array('Complain.board_id' => null);
	    }
	    else if ($data['Filter']['media'] == '라이프'){
	      $conditions['not'] = array('Complain.life_id' => null);
	    }
	  }
	  else if($key == 'status'){
	    if($data['Filter']['status'] == '0'){
	      $conditions['Complain.status'] = 0;
	    }
	    else if ($data['Filter']['status'] == '1'){
	      $conditions['Complain.status'] = 1;
	    }
	    else if ($data['Filter']['status'] == '2'){
	      $conditions['Complain.status'] = 2;
	    }
	    else if ($data['Filter']['status'] == '3'){
	      $conditions['Complain.status'] = 3;
	    }
	  }
	  else if($key == 'start_date'){
	    if($data['Filter'][$key]){
	      $date = date('Y-m-d', strtotime($data['Filter'][$key]));
	      $conditions['Complain.reg_date >='] = $date;
	    }
	  }
	  else if($key == 'end_date'){
	    if($data['Filter'][$key]){
	      $date = date('Y-m-d', strtotime($data['Filter'][$key]) + 86400);
	      $conditions['Complain.reg_date <='] = $date;
	    }
	  }
	}
      }
    }
    
    $this->Complain->unbindModel(array('hasMany' => array('Cs'), 'belongsTo' => array('CsBottomCategory')));
    $this->Board->unbindModel(array('hasMany' => array('Complain')));
    $this->Life->unbindModel(array('hasMany' => array('Complain')));
    $this->EngineerInfo->unbindModel(array('hasOne' => array('User'), 'hasMany' => array('Complex', 'Complain')));
    $this->ReceiverInfo->unbindModel(array('hasOne' => array('User'), 'hasMany' => array('Complex', 'Complain')));
    $complainInfos = $this->Complain->find('all', array('recursive' => 2,'conditions' => $conditions));
    
    /** PHPExcel */
    require_once './Classes/PHPExcel.php';


    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();

    // Set properties
    $objPHPExcel->getProperties()->setCreator("CRM System")
      ->setLastModifiedBy("CRM System")
      ->setTitle("CRM System Complain report")
      ->setSubject("CRM System Complain report")
      ->setDescription("CRM System Complain report on {$currentTime}")
      ->setKeywords("Complain")
      ->setCategory("Complain result");
    
    // Add header
    $objPHPExcel->setActiveSheetIndex(0)
      ->setCellValue('A1', parent::utf8conv('등록일자'))
      ->setCellValue('B1', parent::utf8conv('대분류'))
      ->setCellValue('C1', parent::utf8conv('중분류'))
      ->setCellValue('D1', parent::utf8conv('소분류'))
      ->setCellValue('E1', parent::utf8conv('서버정보'))
      ->setCellValue('F1', parent::utf8conv('통신방식'))
      ->setCellValue('G1', parent::utf8conv('주소'))
      ->setCellValue('H1', parent::utf8conv('단지명'))
      ->setCellValue('I1', parent::utf8conv('위치정보'))
      ->setCellValue('J1', parent::utf8conv('매체'))
      ->setCellValue('K1', parent::utf8conv('시리얼'))
      ->setCellValue('L1', parent::utf8conv('제목'))
      ->setCellValue('M1', parent::utf8conv('내용'))
      ->setCellValue('N1', parent::utf8conv('요청자'))
      ->setCellValue('O1', parent::utf8conv('요청자 연락처'))
      ->setCellValue('P1', parent::utf8conv('접수자'))
      ->setCellValue('Q1', parent::utf8conv('처리자'))
      ->setCellValue('R1', parent::utf8conv('처리일자'));

    // Add some data
    $index = 2;
    foreach($complainInfos as $complainInfo){
      $objPHPExcel->setActiveSheetIndex(0)
	->setCellValue("A{$index}", parent::utf8conv($complainInfo['Complain']['reg_date']))
	->setCellValue("B{$index}", parent::utf8conv($complainInfo['BottomCategory']['TopCategory']['category']))
	->setCellValue("C{$index}", parent::utf8conv($complainInfo['BottomCategory']['MidCategory']['category']))
	->setCellValue("D{$index}", parent::utf8conv($complainInfo['BottomCategory']['category']));
      
      $serverInfo = NULL;
      $method = NULL;
      $addr = NULL;
      $complex_name = NULL;
      $location = NULL;
      $media = NULL;
      $serial = NULL;
      if(!parent::array_empty($complainInfo['Board'])){
	$serverInfo = $complainInfo['Board']['server_name'];
	$method = $complainInfo['Board']['method'];
	$addr = $complainInfo['Board']['Complex']['first_addr'].' '.$complainInfo['Board']['Complex']['mid_addr'].' '.$complainInfo['Board']['Complex']['last_addr'].' '.$complainInfo['Board']['Complex']['addr'];
	$complex_name = $complainInfo['Board']['Complex']['name'];
	$location = $complainInfo['Board']['location'];
	$media = '보드';
	$serial = $complainInfo['Board']['serial'];
      }
      else if(!parent::array_empty($complainInfo['Life'])){
	$serverInfo = $complainInfo['Life']['aed_name'];
	$method = $complainInfo['Life']['method'];
	$addr = $complainInfo['Life']['Complex']['first_addr'].' '.$complainInfo['Life']['Complex']['mid_addr'].' '.$complainInfo['Life']['Complex']['last_addr'].' '.$complainInfo['Life']['Complex']['addr'];
	$complex_name = $complainInfo['Life']['Complex']['name'];
	$location = $complainInfo['Life']['location'];
	$media = '라이프';
	$serial = $complainInfo['Life']['serial'];
      }
      
      $objPHPExcel->setActiveSheetIndex(0)
	->setCellValue("E{$index}", parent::utf8conv($serverInfo))
	->setCellValue("F{$index}", parent::utf8conv($method))
	->setCellValue("G{$index}", parent::utf8conv($addr))
	->setCellValue("H{$index}", parent::utf8conv($complex_name))
	->setCellValue("I{$index}", parent::utf8conv($location))
	->setCellValue("J{$index}", parent::utf8conv($media))
	->setCellValue("K{$index}", parent::utf8conv($serial))
	->setCellValue("L{$index}", parent::utf8conv($complainInfo['Complain']['title']))
	->setCellValue("M{$index}", parent::utf8conv($complainInfo['Complain']['comment']))
	->setCellValue("N{$index}", parent::utf8conv($complainInfo['Complain']['name']))
	->setCellValue("O{$index}", parent::utf8conv($complainInfo['Complain']['phone']))
	->setCellValue("P{$index}", parent::utf8conv($users[$complainInfo['Complain']['receiver_id']]))
	->setCellValue("Q{$index}", parent::utf8conv($users[$complainInfo['Complain']['engineer_id']]))
	->setCellValue("R{$index}", parent::utf8conv($complainInfo['Complain']['end_date']));
      $index++;
    }
    // Rename sheet
    $objPHPExcel->getActiveSheet()->setTitle(parent::utf8conv('민원정보'));


    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);


    // Redirect output to a client’s web browser (Excel2007)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="민원정보'.$currentTime.'.xlsx"');
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
    exit;
  }
}

?>