<?php

class ExportController extends Zend_Controller_Action {

	public function init() {
		$information = new Zend_Session_Namespace('information');	
		if( Lib_Permission::CheckPermission('export') != true ){
			$information->permissionError = true;
			$this->_helper->redirector('index','index');		
		}
		
		$admin = new Zend_Session_Namespace('admin');
		if( $admin->admin_auth != true ){
			$this->_helper->redirector('index','login');
		}			
	}	
	
	
	public function indexAction() {	
		$admin = new Zend_Session_Namespace('admin');
		$information = new Zend_Session_Namespace('information');
		$db = Zend_Registry::get('db');

		$v = $this->_request->getPost();
		
		
		$datafield = array(
			'documents_sent' => 2, 
			'documents_pay4' => 8, 
			'documents_pay' => 5, 
			'documents_pay3' => 7, 
			'documents_pay2' => 6, 
			'documents_prepared' => 9
		);
		$this->view->statusfield = $datafield;
		
		
		if( isset($v['exportDocuments']) ){
		
		
			$Car = new Car();	
			$dataCar = $Car->fetchAll(null, 'car_nr ASC'); 
		
			foreach($dataCar as $key => $val){
				$vCar[$val['car_id']] = $val['car_nr'];
			}
			

			$select = $db->select();
			$select->from('documents')->join('project', 'documents.project_id = project.project_id')->join('company', 'project.company_id = company.company_id')->join('client', 'client.client_id = documents.client_id')->joinLeft('documents_car', 'documents_car.documents_id = documents.documents_id', array('car_id'));
			/*
			if( $v['companyList'] != '' ){
				$companyList = explode(',',$v['companyList']);
				$where = '';
				foreach( $companyList as $key => $value1 ){
					if( Lib_Permission::getPermissionCompany( $value1 ) == 1 ){
						if( $where == '' ) $where ='company.company_id = '.$value1;
						else $where = $where.' OR company.company_id = '.$value1;
					}
				}
				$select->where($where);
			}
			*/
			if( $v['projectList'] != '' ){
				$projectList = explode(',',$v['projectList']);
				$where = '';
				foreach( $projectList as $key => $value1 ){
					if( Lib_Permission::getPermissionProject( $value1 ) == 1 ){
						if( $where == '' ) $where ='project.project_id = '.$value1;
						else $where = $where.' OR project.project_id = '.$value1;
					}
				}
				$select->where($where);
			}
			
			if( $v['documentsList'] != '' ){
				$documentsList = explode(',',$v['documentsList']);
				$where = '';
				foreach( $documentsList as $key => $value1 ){
					if( $where == '' ) $where ="documents.documents_name = '".$value1."'";
					else $where = $where." OR documents.documents_name = '".$value1."'";
				}
				$select->where($where);
			}
			
			if( $v['clientList'] != '' ){
				$clientList = explode(',',$v['clientList']);
				$where = '';
				foreach( $clientList as $key => $value1 ){
					if( $where == '' ) $where ="client.client_id = '".$value1."'";
					else $where = $where." OR client.client_id = '".$value1."'";
				}
				$select->where($where);
			}
			
			if( $v['carList'] != '' ){
				$carList = explode(',',$v['carList']);
				$where = '';
				foreach( $carList as $key => $value1 ){
					if( $where == '' ) $where ="documents_car.car_id = '".$value1."'";
					else $where = $where." OR documents_car.car_id = '".$value1."'";
				}
				$select->where($where);
			}
				
				
			// data dokumentu
			if( isset($v['documents_data_start']) AND $v['documents_data_start'] != '' ){
				$select->where("documents.documents_data >= '".Lib_Date::convertDate($v['documents_data_start'],"d-m-Y","Y-m-d")."'");				
			}			
			if( isset($v['documents_data_stop']) AND $v['documents_data_stop'] != '' ){
				$select->where("documents.documents_data <= '".Lib_Date::convertDate($v['documents_data_stop'],"d-m-Y","Y-m-d")."'");				
			}
				
				
			// cel platnosci 
			if( isset($v['documents_cp_start']) AND $v['documents_cp_start'] != '' ){
				$select->where("documents.documents_cp >= '".Lib_Date::convertDate($v['documents_cp_start'],"d-m-Y","Y-m-d")."'");				
			}			
			if( isset($v['documents_cp_stop']) AND $v['documents_cp_stop'] != '' ){
				$select->where("documents.documents_cp <= '".Lib_Date::convertDate($v['documents_cp_stop'],"d-m-Y","Y-m-d")."'");				
			}
			
			
			
			
			
			foreach( $v['filtrStatus'] as $key => $val ){
			
				if( $key == 'documents_prepared' ){
					if( $val != 'all' ){				
						$val2 = $val-1;
						$select->where("( client.client_activ_prepared_documents = 1 AND documents.".$key." = ".$val2." )");	
					}				
				}else{
					if( $val != 'all' ){				
						$val2 = $val-1;
						$select->where("documents.".$key." = ".$val2);	
					}		
				}
			}
			
			
			$select->order(array('documents.documents_ins ASC','documents.documents_checkdate DESC','documents.documents_data DESC'));  
			$stmt = $db->query($select);
		//	echo $select;die();
			$data = $stmt->fetchAll();
		
			$objPHPExcel = new PHPExcel();
			
			$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);			
			$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
			$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(20);
			
			$i = 1;				
			$objPHPExcel->setActiveSheetIndex(0)
					->setCellValue('A'.$i, @LANG_export_excel_documents_data)	
					->setCellValue('B'.$i, @LANG_export_excel_documents_client)		
					->setCellValue('C'.$i, @LANG_export_excel_documents_nr)
					->setCellValue('D'.$i, @LANG_export_excel_project_name)					
					->setCellValue('E'.$i, @LANG_export_excel_company_name)
					->setCellValue('F'.$i, @LANG_export_excel_documents_name)
					->setCellValue('G'.$i, @LANG_export_excel_documents_car_id)							
					->setCellValue('H'.$i, @LANG_export_excel_documents_checkdate)					
					->setCellValue('I'.$i, @LANG_export_excel_documents_price_netto)
					->setCellValue('J'.$i, @LANG_export_excel_documents_price_tax)
					->setCellValue('K'.$i, @LANG_export_excel_documents_price_brutto)
					->setCellValue('L'.$i, @LANG_export_excel_documents_price_rabate)
					->setCellValue('M'.$i, @LANG_export_excel_documents_price_pay_method)
					->setCellValue('N'.$i, @LANG_export_excel_documents_price_pay_sum)
					->setCellValue('O'.$i, @LANG_export_excel_documents_price_pay_summ)
					->setCellValue('P'.$i, @LANG_export_excel_documents_price_pay_date)
					->setCellValue('Q'.$i, @LANG_export_excel_documents_info)
					->setCellValue('R'.$i, @LANG_export_excel_project_employee_manager_name);
					
					
			if( $data ) foreach($data as $key => $value){
				$i++;
				
				if( $value['documents_price_pay_method']!='met0' ) $metp = @constant(@LANG_doc_.$value['documents_price_pay_method']);
				else $metp = '';
				
				$car_nr = '';
				if( $value['car_id'] != null ){
					$car_nr = $vCar[$value['car_id']];
				}
				
				$objPHPExcel->setActiveSheetIndex(0)
						->setCellValue('A'.$i, Lib_Date::convertDate($value['documents_data'],"Y-m-d","d.m.Y") )
						->setCellValue('B'.$i, $value['client_name'])
						->setCellValue('C'.$i, $value['documents_nr'])
						->setCellValue('D'.$i, $value['project_name'])
						->setCellValue('E'.$i, $value['company_name'])
						->setCellValue('F'.$i, @constant(@LANG_doc_.$value['documents_name']))
						->setCellValue('G'.$i, $car_nr)
						->setCellValue('H'.$i, $value['documents_checkdate'])
						->setCellValue('I'.$i, $value['documents_price_netto'])
						->setCellValue('J'.$i, $value['documents_price_tax_sum'])
						->setCellValue('K'.$i, $value['documents_price_brutto'])
						->setCellValue('L'.$i, $value['documents_price_rabate_sum'])
						->setCellValue('M'.$i, $metp)
						->setCellValue('N'.$i, $value['documents_price_pay'])
						->setCellValue('O'.$i, $value['documents_price_paym'])
						->setCellValue('P'.$i, $value['documents_paydate'])
						->setCellValue('Q'.$i, $value['documents_note'])
						->setCellValue('R'.$i, isset($value['project_employee_manager_name'])?$value['project_employee_manager_name']:" " );
				
			}

			// 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 (Excel5)
			header('Content-Type: application/vnd.ms-excel');
			header('Content-Disposition: attachment;filename="Export-'.date('Y-m-d').'.xls"');
			header('Cache-Control: max-age=0');
			// If you're serving to IE 9, then the following may be needed
			header('Cache-Control: max-age=1');

			// If you're serving to IE over SSL, then the following may be needed
			header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
			header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
			header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
			header ('Pragma: public'); // HTTP/1.0

			$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
			$objWriter->save('php://output');	
		
			$this->_helper->viewRenderer->setNoRender();
		
		
		}
		
		
		
		$Company = new Company();
		$data = $Company->fetchAll(Lib_Permission::setPermissionCompanySQL(), " company_name ASC "); 		
		if( count($data) > 0 ) $this->view->dataTableCompany = $data;
		
		$dataCarDB = $db->query("SELECT * FROM `car` ORDER BY car_nr ASC")->fetchAll();
		$this->view->dataTableCar = $dataCarDB;	
		
		$dataClientDB = $db->query("SELECT * FROM `client` ORDER BY client_name ASC")->fetchAll();
		$this->view->dataTableClient = $dataClientDB;	
	}
	
	
	public function projectlistAction() {	
		$admin = new Zend_Session_Namespace('admin');
		$information = new Zend_Session_Namespace('information');		
		$db = Zend_Registry::get('db');
		
		$v = $this->_request->getPost();
		
		
		$select = $db->select();
		$select->from('company')->join('project', 'company.company_id = project.company_id');
		
		if( isset($v['companyFiltr']) AND $v['companyFiltr'] != '' ){
			foreach( $v['companyFiltr'] as $key => $value ){
				$select->orWhere('project.company_id = '.$value);
				//echo$value;
			}
		}
		
		$select->where(Lib_Permission::setPermissionCompanySQL());
		$select->where(Lib_Permission::setPermissionProjectSQL());
		
		$select->order(array('company.company_name ASC','project.project_name ASC'));  
		$stmt = $db->query($select);
		$data = $stmt->fetchAll();		
		if( count($data) > 0 ) $this->view->dataTableProject = $data;
	}
	
	
	public function fileAction() {	
		$admin = new Zend_Session_Namespace('admin');
		$information = new Zend_Session_Namespace('information');

		$objPHPExcel = new PHPExcel();

		$objPHPExcel->setActiveSheetIndex(0)
					->setCellValue('A1', 'Hello')
					->setCellValue('B2', 'world!')
					->setCellValue('C1', 'Hello')
					->setCellValue('D2', 'world!');

		// 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 (Excel5)
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="Export-'.date('Y-m-d').'.xls"');
		header('Cache-Control: max-age=0');
		// If you're serving to IE 9, then the following may be needed
		header('Cache-Control: max-age=1');

		// If you're serving to IE over SSL, then the following may be needed
		header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
		header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
		header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
		header ('Pragma: public'); // HTTP/1.0

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

		$this->_helper->viewRenderer->setNoRender();
	}

}
