<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Export extends CI_Controller {

	public function __construct()
	{
		parent::__construct();

		session_start();
		
		if(!isset($_SESSION['admin']) && $_SESSION['admin'] == '')
		{
			redirect('backend/main/index');
		}

		
		$this->load->model('admin_m');		
		$this->load->model('audits_m');	
		$this->load->model('membership_m');
		$this->load->model('index_m');

	}	
	
	function pdf()
	{
		
		$html = $this->load->view('backend/pdf',$data,TRUE);
	
		$filename = 's';
		pdf_create($html, $filename);
	}
	
	function num_to_letters($n)
	{
		$n -= 1;
		for ($r = ""; $n >= 0; $n = intval($n / 26) - 1)
			$r = chr($n % 26 + 0x41) . $r;
		return $r;
	}
	
	function letters_to_num($letters) {
		$num = 0;
		$arr = array_reverse(str_split($letters));

		for ($i = 0; $i < count($arr); $i++) {
			$num += (ord(strtolower($arr[$i])) - 96) * (pow(26,$i));
		}
		return $num;
	}
	
	 
	public function index()
	{	
		ini_set('memory_limit', '2048M');

		$listing = $this->membership_m->listing_type(1);
		
		$listing_all = array();
		foreach($listing as $i)
		{
			$i['admin'] = $this->membership_m->designated_admin($i['id']);
			$i['fields'] = $this->membership_m->get_all_values($i['id']);
			
			$listing_all[] = $i;
		}
		
		
		$header = file_get_contents('regular.txt');
		$header = explode('	',$header);
		
		$header1[] = array(
			'title' => 'Overview',
			'position' => 1,
		);
		
		$header1[] = array(
			'title' => 'Local Details',
			'position' => 4,
		);
		
		$header1[] = array(
			'title' => 'Overseas Details',
			'position' => 10,
		);
		
		
		$header1[] = array(
			'title' => 'CEO / Head of Singapore office',
			'position' => 15,
		);
		
		$header1[] = array(
			'title' => 'Business contact / Client services',
			'position' => 19,
		);
		
		
		$header1[] = array(
			'title' => 'Management and organisation',
			'position' => 26,
		);

		
		$header1[] = array(
			'title' => 'I.T. department contact',
			'position' => 90,
		);
		
		
		$header1[] = array(
			'title' => 'Mailing list and areas of interest',
			'position' => 106,
		);


		$header1[] = array(
			'title' => 'Designated Admin',
			'position' => 138,
		);



		
		$header2[] = array(
			'title' => 'Head of Equities / Fixed Income / Research / Alternatives (or equivalent)',
			'position' => 'Z',
			'start' => 'Z',
			'end' => 'AO',
		);
		
		$header2[] = array(
			'title' => 'Head of Sales & Distribution (Institutional/Retail) / Product / Business Development / Client Services (or equivalent)',
			'position' => 'AP',
			'start' => 'AP',
			'end' => 'BE',
		);
		
		
		$header2[] = array(
			'title' => 'Head of Compliance / Risk / Legal / Technology / HR (or equivalent)',
			'position' => 'BF',
			'start' => 'BF',
			'end' => 'BU',
		);
		
			
		$header2[] = array(
			'title' => 'Head of ESG / Digital Innovation / Chief Technology Officer (if any) (or equivalent)',
			'position' => 'BV',
			'start' => 'BV',
			'end' => 'CK',
		);														

		
		
		//$object->getActiveSheet()->mergeCells('P'.$a.':P'.$b);
		//$object->getActiveSheet()->getStyle('B10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
		$this->load->library("excel");
		$object = new PHPExcel();
		$object->getActiveSheet()->setTitle("Regular");
		$object->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
		
		$object->setActiveSheetIndex(0);
		
		
		$excel_row = 1;
		
		foreach($header1 as $h)
		{
			$object->getActiveSheet()->getStyle($this->num_to_letters($h['position']-1).$excel_row)->getFont()->setBold(true);	
			
			$object->getActiveSheet()->setCellValueByColumnAndRow($h['position']-1, 1, $h['title']);
		}
		
		
		$excel_row = 2;
		
		foreach($header2 as $h)
		{
			$object->getActiveSheet()->mergeCells($h['start'].$excel_row.':'.$h['end'].$excel_row);
			$object->getActiveSheet()->getStyle($h['position'].$excel_row)->getFont()->setBold(true);	
			
			$object->getActiveSheet()->setCellValueByColumnAndRow($this->letters_to_num($h['position'])-1, $excel_row, $h['title']);			
			
		}
		
		
		
		$excel_row = 3;
		
		foreach($header as $counter=>$h)
		{
			$object->getActiveSheet()->getStyle($this->num_to_letters($counter).$excel_row)->getFont()->setBold(true);	
			$object->getActiveSheet()->setCellValueByColumnAndRow($counter, $excel_row, $h);
		}

		
		$excel_row = 4;
		
		foreach($listing_all as $counter=>$h)
		{
	
			foreach($h['fields'] as $counter=>$tt)
			{
				if($tt['meta_key'] >= 136)
					continue;
				
				$current_col = $tt['meta_key'];
				
				$object->getActiveSheet()->setCellValueByColumnAndRow($tt['meta_key'], $excel_row, $tt['meta_value']);
			}
			
			
			$current_col++;
			
			foreach($h['admin'] as $a)
			{				
				$object->getActiveSheet()->setCellValueByColumnAndRow($current_col+1, $excel_row, $a['name']);
				$object->getActiveSheet()->setCellValueByColumnAndRow($current_col+2, $excel_row, $a['designation']);
				$object->getActiveSheet()->setCellValueByColumnAndRow($current_col+3, $excel_row, $a['email']);
				$object->getActiveSheet()->setCellValueByColumnAndRow($current_col+4, $excel_row, $a['contactno']);
				$current_col = $current_col+4;
			}
			
			$current_col = 0;
			$excel_row++;
		}
		
		
		
		foreach ($object->getWorksheetIterator() as $worksheet) {
			foreach ($worksheet->getColumnIterator() as $column) {							
				
				$worksheet
					->getColumnDimension($column->getColumnIndex())
					->setAutoSize(true);
			} 
		}
	   
		$object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="Regular.xls"');
		$object_writer->save('php://output');
	}

	
	 
	public function associate()
	{	
		
		$listing = $this->membership_m->listing_type(2);
		
		$listing_all = array();
		foreach($listing as $i)
		{
			$i['admin'] = $this->membership_m->designated_admin($i['id']);
			$i['fields'] = $this->membership_m->get_all_values($i['id']);
			
			$listing_all[] = $i;
		}
		
		
		$header = file_get_contents('associate.txt');
		$header = explode('	',$header);
		
		$header1[] = array(
			'title' => 'Overview',
			'position' => 1,
		);
		
		$header1[] = array(
			'title' => 'Local Details',
			'position' => 4,
		);
		
		
		$header1[] = array(
			'title' => 'CEO / Head of Singapore office',
			'position' => 6,
		);
		
		$header1[] = array(
			'title' => 'Business contact / Client services',
			'position' => 10,
		);
		
		
		$header1[] = array(
			'title' => 'Management and organisation',
			'position' => 17,
		);

		
		$header1[] = array(
			'title' => 'I.T. department contact',
			'position' => 33,
		);
		
		
		$header1[] = array(
			'title' => 'Mailing list and areas of interest',
			'position' => 49,
		);


		$header1[] = array(
			'title' => 'Designated Admin',
			'position' => 82,
		);

		
		//$object->getActiveSheet()->mergeCells('P'.$a.':P'.$b);
		//$object->getActiveSheet()->getStyle('B10')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
		$this->load->library("excel");
		$object = new PHPExcel();
		$object->getActiveSheet()->setTitle("Regular");
		$object->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
		
		$object->setActiveSheetIndex(0);
		
		
		$excel_row = 1;
		
		foreach($header1 as $h)
		{
			$object->getActiveSheet()->getStyle($this->num_to_letters($h['position']-1).$excel_row)->getFont()->setBold(true);	
			
			$object->getActiveSheet()->setCellValueByColumnAndRow($h['position']-1, 1, $h['title']);
		}
		
		
		
		$excel_row = 2;
		
		foreach($header as $counter=>$h)
		{
			$object->getActiveSheet()->getStyle($this->num_to_letters($counter).$excel_row)->getFont()->setBold(true);	
			$object->getActiveSheet()->setCellValueByColumnAndRow($counter, $excel_row, $h);
		}

		
		$excel_row = 3;
		
		foreach($listing_all as $counter=>$h)
		{
	
			foreach($h['fields'] as $counter=>$tt)
			{
				if($tt['meta_key'] >= 80)
					continue;
				
				$current_col = $tt['meta_key'];
				
				$object->getActiveSheet()->setCellValueByColumnAndRow($tt['meta_key'], $excel_row, $tt['meta_value']);
			}
			
			
			$current_col++;
			
			foreach($h['admin'] as $a)
			{				
				$object->getActiveSheet()->setCellValueByColumnAndRow($current_col+1, $excel_row, $a['name']);
				$object->getActiveSheet()->setCellValueByColumnAndRow($current_col+2, $excel_row, $a['designation']);
				$object->getActiveSheet()->setCellValueByColumnAndRow($current_col+3, $excel_row, $a['email']);
				$object->getActiveSheet()->setCellValueByColumnAndRow($current_col+4, $excel_row, $a['contactno']);
				$current_col = $current_col+4;
			}
			
			$current_col = 0;
			$excel_row++;
		}
		
		
		
		foreach ($object->getWorksheetIterator() as $worksheet) {
			foreach ($worksheet->getColumnIterator() as $column) {							
				
				$worksheet
					->getColumnDimension($column->getColumnIndex())
					->setAutoSize(true);
			} 
		}
	   
		$object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="Associate.xls"');
		$object_writer->save('php://output');
	
	}

}

/* End of file welcome.php */

/* Location: ./application/controllers/welcome.php */