<?php
include_once(_DIR_EXT.'/HTML/c_page_TXT.php') ;
include_once('i_client.php') ;
class c_page_XLS extends c_page_txt
{ public $rec_member  ;
  public $client ;
  public $fields ;
  public $file_name ;
  public $pExcel ;
  public $aSheet ;
  public $style ;
  public $title ;

  public $first_col_char=65 ; // A
  public $first_row=1 ; // A

  public $cur_col=65 ; // A
  public $cur_row=1 ; // A

  function c_page_XLS()
  {

  }

  function body()
  { // подготовить страницу
    $this->prepare_Sheet() ;
    // заполняем содержимое
    $cmd=$_GET['cmd'] ;
    if ($_GET['cmd']) $this->$cmd($_GET) ;
    // отдаеи в браузер
    $this->put() ;
    // регистрируем в журнале
    LOGS()->reg_log('XLS export',$cmd,array('member_id'=>MEMBER()->id,'data'=>$_GET)) ;
  }


  function export_cars($options)
     {  $this->file_name='export_cars.xls' ;
        $this->title='Список автомобилей' ;

       $_usl=array() ; $arr_title=array() ;
       $_usl[]='clss=361' ;
       $_usl[]='enabled=1' ;
       if ($options['client_id'])
       { $ids_orgs=execSQL_line('select pkey from obj_site_personals where clss=207 and enabled=1 and client_id='.MEMBER()->id) ;
         //if (_sizeof($ids_orgs)) foreach($ids_orgs as $org_id) $arr_parens_ids[]=PERSONAL()->tree[$org_id]->get_list_child() ;
         //else                   $view_client=0 ; // если у клиента нет ни одной организации - сортрудников не показываем
         if (_sizeof($ids_orgs)) $_usl[]='parent in ('.implode(',',$ids_orgs).')' ;
       }
       if ($options['view_ext'])     $_usl[]='ext=1';
       if ($options['org_id'])       $_usl[]='parent='.$options['org_id'] ;
       if ($options['car_id'])       $_usl[]='pkey in ('.$options['car_id'].')' ;

       // формируеем заголовок
       if ($options['client_id'])       $arr_title['Клиент']=execSQL_value('select obj_name from obj_site_account where pkey='.$options['client_id']) ;
       if ($options['view_ext'])        $arr_title['Доп.списки']='Да' ;
       if ($options['org_id'])          $arr_title['Организация']=PERSONAL()->get_name_by_id($options['org_id']) ;
       if ($options['park_id'])         $arr_title['Автопарк']=PERSONAL()->get_name_by_id($options['park_id']) ; 
       if ($options['cars_id_search'])  $arr_title['Поиск авто по']=$options['cars_id_search'] ;

       if (_sizeof($_usl)) $usl=implode(' and ',$_usl) ; else $usl='' ;
       //echo $usl ;
       $recs=execSQL('select * from obj_site_personals_cars where '.$usl.' order by obj_name') ;

       $this->fields=array(  'model'=>array('title'=>'Марка, модель','width'=>40,'align'=>'left'),
                             'nomer'=>array('title'=>'Госномер','width'=>10,'align'=>'center'),
                             'typ'=>array('title'=>'Класс авто','width'=>10,'align'=>'center'),
                            'e_osago'=>array('title'=>'Дата окончания ОСАГО','width'=>20,'align'=>'center'),
                            'e_sts'=>array('title'=>'Дата окончания СТС','width'=>20,'align'=>'center'),
                            'e_to'=>array('title'=>'Дата окончания ТО','width'=>20,'align'=>'center')
                            ) ;

       // шапка таблицы
       $this->print_table_header() ;
       // данные таблицы
       if (_sizeof($recs)) foreach($recs as $rec)
         { $cur_col_char=$this->first_col_char ;
           if (_sizeof($this->fields)) foreach($this->fields as $fname=>$options)
           {  $value='' ;
              switch($fname)
              { case 'client':   $value=PERSONAL()->get_name_by_id($rec['parent']) ;  break ;
                case 'model':    $value=$rec['model'] ; break ;
                case 'nomer':    $value=$rec['obj_name'] ; break ;
                case 'typ':      $value=$rec['typ'] ; break ;
                case 'e_osago':
                case 'e_sts':
                case 'e_to':     $value=($rec[$fname])? date('d.m.Y',$rec[$fname]):'' ;   break ;
              }

             $col_char=iconv('windows-1251','UTF-8',chr($cur_col_char)) ;
             //$this->aSheet->setCellValueByColumnAndRow($col_char,$this->cur_row,$value);
             $this->aSheet->setCellValue($col_char.$this->cur_row,$value);
             if ($options['align']=='center') $this->aSheet->getStyle($col_char.$this->cur_row)->applyFromArray($this->style['center']);
             if ($options['align']=='left')   $this->aSheet->getStyle($col_char.$this->cur_row)->applyFromArray($this->style['left']);
             $cur_col_char++ ;
           }

           $this->cur_row++ ;
         }

     }


  function export_drivers($options)
     {  $this->file_name='export_drivers.xls' ;
        $this->title='Список водителей' ;

       $_usl=array() ; $arr_title=array() ;
       //$_usl[]='clss=211' ;
       $_usl[]='enabled=1' ;
       if ($options['client_id'])
       { $ids_orgs=execSQL_line('select pkey from obj_site_personals where clss=207 and enabled=1 and client_id='.MEMBER()->id) ;
         if (_sizeof($ids_orgs)) foreach($ids_orgs as $org_id) $arr_parens_ids[]=PERSONAL()->tree[$org_id]->get_list_child() ;
         //else                   $view_client=0 ; // если у клиента нет ни одной организации - сортрудников не показываем
         if (_sizeof($arr_parens_ids)) $_usl[]='parent in ('.implode(',',$arr_parens_ids).')' ;
         //if (_sizeof($ids_orgs)) $_usl[]='parent in ('.implode(',',$ids_orgs).')' ;
       }
       if ($options['org_id'])       $_usl[]='parent='.$options['org_id'] ;
       if ($options['car_id'])       $_usl[]='pkey in ('.$options['car_id'].')' ;

       if (_sizeof($_usl)) $usl=implode(' and ',$_usl) ; else $usl='' ;
       //echo $usl ;
       $recs=execSQL('select * from obj_site_personals where '.$usl.' order by obj_name') ;
       //print_2x_arr($recs) ;

       $this->fields=array( 'UUID'=>array('title'=>'UUID','width'=>30,'align'=>'left'),
                            'VU'=>array('title'=>'ВУ','width'=>10,'align'=>'left'),
                            'FIO'=>array('title'=>'ФИО','width'=>50,'align'=>'left'),
                            'pol'=>array('title'=>'Пол','width'=>15,'align'=>'center'),
                            'dr'=>array('title'=>'ДатаРожд','width'=>15,'align'=>'center'),
                            'phone'=>array('title'=>'Телефон','width'=>15,'align'=>'center'),
                            'park'=>array('title'=>'Автопарк','width'=>15,'align'=>'center'),
                            's_mc'=>array('title'=>'ДатаВыдачиМС','width'=>15,'align'=>'center')
                            ) ;


       // шапка таблицы
       $this->print_table_header() ;
       // данные таблицы
       if (_sizeof($recs)) foreach($recs as $rec)
         { $cur_col_char=$this->first_col_char ;
           if (_sizeof($this->fields)) foreach($this->fields as $fname=>$options)
           {  $value='' ;
              switch($fname)
               { case 'UUID':     $value=$rec['uid'] ;  break ;
                 case 'VU':       $value=$rec['driving'] ; break ;
                 case 'FIO':      $value=$rec['obj_name'] ; break ;
                 case 'pol':      $value=($rec['pol'])? 'Женский':'Мужской' ; break ;
                 //case 'dr':       $value=($rec[$fname])? date('d.m.Y',$rec[$fname]):'' ;   break ;
                 case 'dr':       $value=$rec[$fname] ;   break ; //  дата рождения храниться в виде строки
                 case 'phone':    $value=$rec['phone'] ;   break ;
                 case 'park':     $value=PERSONAL()->get_name_by_id($rec['parent']) ;   break ;
                 case 's_mc':     $value=($rec['s_medspravka'])? date('d.m.Y',$rec['s_medspravka']):'' ;   break ;
               }

             $col_char=iconv('windows-1251','UTF-8',chr($cur_col_char)) ;
             //$this->aSheet->setCellValueByColumnAndRow($col_char,$this->cur_row,$value);
             $this->aSheet->setCellValue($col_char.$this->cur_row,$value);
             if ($options['align']=='center') $this->aSheet->getStyle($col_char.$this->cur_row)->applyFromArray($this->style['center']);
             if ($options['align']=='left')   $this->aSheet->getStyle($col_char.$this->cur_row)->applyFromArray($this->style['left']);
             $cur_col_char++ ;
           }

           $this->cur_row++ ;
         }

     }

   function export_personals($options)
   {  $this->file_name='export_personals.xls' ;
      $this->title='Список сотрудников' ;

     $_usl=array() ; $arr_title=array() ;
     $_usl[]='clss=211' ;
     $_usl[]='enabled=1' ;
     if ($options['client_id'])
     { $ids_orgs=execSQL_line('select pkey from obj_site_personals where clss=207 and enabled=1 and client_id='.MEMBER()->id) ;
       if (_sizeof($ids_orgs)) foreach($ids_orgs as $org_id) $arr_parens_ids[]=PERSONAL()->tree[$org_id]->get_list_child() ;
       //else                   $view_client=0 ; // если у клиента нет ни одной организации - сортрудников не показываем
       if (_sizeof($arr_parens_ids)) $_usl[]='parent in ('.implode(',',$arr_parens_ids).')' ;
       //if (_sizeof($ids_orgs)) $_usl[]='parent in ('.implode(',',$ids_orgs).')' ;
     }
     if ($options['org_id'])       $_usl[]='parent='.$options['org_id'] ;
     if ($options['car_id'])       $_usl[]='pkey in ('.$options['car_id'].')' ;

     if (_sizeof($_usl)) $usl=implode(' and ',$_usl) ; else $usl='' ;
     //echo $usl ;
     $recs=execSQL('select * from obj_site_personals where '.$usl.' order by obj_name') ;
     //print_2x_arr($recs) ;

     $this->fields=array( 'UUID'=>array('title'=>'UUID','width'=>30,'align'=>'left'),
                          'PR'=>array('title'=>'Пропуск','width'=>15,'align'=>'center'),
                          'VU'=>array('title'=>'ВУ','width'=>10,'align'=>'left'),
                          'FIO'=>array('title'=>'ФИО','width'=>50,'align'=>'left'),
                          'pol'=>array('title'=>'Пол','width'=>15,'align'=>'center'),
                          'dr'=>array('title'=>'ДатаРожд','width'=>15,'align'=>'center'),
                          'phone'=>array('title'=>'Телефон','width'=>15,'align'=>'center'),
                          'park'=>array('title'=>'Подразделение','width'=>15,'align'=>'center'),
                          's_mc'=>array('title'=>'ДатаВыдачиМС','width'=>15,'align'=>'center'),
                          'work'=>array('title'=>'Должность','width'=>15,'align'=>'center')
                          ) ;


     // шапка таблицы
     $this->print_table_header() ;
     // данные таблицы
     if (_sizeof($recs)) foreach($recs as $rec)
       { $cur_col_char=$this->first_col_char ;
         if (_sizeof($this->fields)) foreach($this->fields as $fname=>$options)
         {  $value='' ;
            switch($fname)
             { case 'UUID':     $value=$rec['uid'] ;  break ;
               case 'PR':       $value=$rec['propusk'] ; break ;
               case 'VU':       $value=$rec['driving'] ; break ;
               case 'FIO':      $value=$rec['obj_name'] ; break ;
               case 'pol':      $value=($rec['pol'])? 'Женский':'Мужской' ; break ;
               case 'dr':       $value=$rec[$fname] ;   break ; //  дата рождения храниться в виде строки
               case 'phone':    $value=$rec['phone'] ;   break ;
               case 'park':     $value=PERSONAL()->get_name_by_id($rec['parent']) ;    break ;
               case 'work':     $value=$rec['working'] ; break ;
               case 's_mc':     $value=($rec['s_medspravka'])? date('d.m.Y',$rec['s_medspravka']):'' ;   break ;
             }

           $col_char=iconv('windows-1251','UTF-8',chr($cur_col_char)) ;
           //$this->aSheet->setCellValueByColumnAndRow($col_char,$this->cur_row,$value);
           $this->aSheet->setCellValue($col_char.$this->cur_row,$value);
           if ($options['align']=='center') $this->aSheet->getStyle($col_char.$this->cur_row)->applyFromArray($this->style['center']);
           if ($options['align']=='left')   $this->aSheet->getStyle($col_char.$this->cur_row)->applyFromArray($this->style['left']);
           $cur_col_char++ ;
         }

         $this->cur_row++ ;
       }

   }


   //----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   //
   //  сттандрантые операции подготовки XLS
   //
   //----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  //подготовка страницы  
  function prepare_Sheet()
  { set_include_path(_DIR_TO_ADDONS.'/PHPExcel/');
    include_once(_DIR_TO_ADDONS.'/PHPExcel/PHPExcel.php');
    include_once(_DIR_TO_ADDONS.'/PHPExcel/PHPExcel/IOFactory.php');
      
    $this->pExcel = new PHPExcel();
    $this->pExcel->setActiveSheetIndex(0);
    $this->aSheet = $this->pExcel->getActiveSheet();

    //настройки для шрифтов
    $this->style['Font'] = array('font'=>array('name'=>'Arial Cyr','size'=>'8','bold'=>false));
    $this->style['boldFont'] = array('font'=>array('name'=>'Arial Cyr','size'=>'10','bold'=>true));
    $this->style['center'] = array('font'=>array('name'=>'Arial Cyr','size'=>'8'),'alignment'=>array('horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_CENTER,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_TOP));
    $this->style['left'] = array('font'=>array('name'=>'Arial Cyr','size'=>'8'),'alignment'=>array('horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_LEFT,'vertical'=>PHPExcel_Style_Alignment::VERTICAL_TOP));
  }

  function print_table_header()
  { // заголовок документа
    $this->aSheet->setTitle($this->title);

    $cur_col_char=$this->first_col_char ;
    if (_sizeof($this->fields)) foreach($this->fields as $fname=>$options)
    { $col_char=iconv('windows-1251','UTF-8',chr($cur_col_char)) ;
      $this->aSheet->getColumnDimension($col_char)->setWidth($options['width']); //
      $this->aSheet->setCellValue($col_char.$this->cur_row,$options['title']);
      $this->aSheet->getStyle($col_char.$this->cur_row)->applyFromArray($this->style['boldFont'])->applyFromArray($this->style['center']);
      $cur_col_char++ ;
    }
    $this->cur_row++ ;
  }

  //отдаем пользователю в браузер
  function put()
  { include("PHPExcel/Writer/Excel5.php");
    $objWriter = new PHPExcel_Writer_Excel5($this->pExcel);
    header('Content-Type: application/vnd.ms-excel;name="'.$this->file_name.'"');
    header('Content-Disposition: attachment;filename="'.$this->file_name.'"');
    header('Cache-Control: max-age=0');
    $objWriter->save('php://output');
  }

}



?>
