<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Export extends CI_Controller {
	public function __construct(){
		parent::__construct();
		$this->load->model('common_model');
		$this->load->model('users_model');
		if(get_cookie('admin_id'))
		{
			$this->session->set_userdata('admin_id',get_cookie('admin_id'));
		}
		if(!$this->session->userdata('admin_id'))
		{
			redirect('admin/login');
		}
	}
	
	public function index($cal_year){
		$content_data = array();
		$data['loginDetails'] = $this->common_model->getAllRow('feu_userdetails',array('id'=>$this->session->userdata('admin_id')),array('usr_users','feu_userdetails.user_id = usr_users.id')) ;
		$data['content'] = $this->load->view('admin/export/index', $content_data, TRUE);
		$this->load->view('admin/layout', $data);
	}

	public function download($cal_year){
		$site_address_name = $this->common_model->getAllRow('site_settings',array('code'=>'site_address_name'));
		$site_address_city = $this->common_model->getAllRow('site_settings',array('code'=>'site_address_city'));
		$site_address_country_zip = $this->common_model->getAllRow('site_settings',array('code'=>'site_address_country_zip'));
		$membershipsql = $this->db->query("SELECT feu_userdetails.user_id as user_id, `username`, `total_sum`, `payment_date`, `shop_orders`.`created`, `time`, `trn_id`, `article_id`, `order_id_masked`, `payment_method_id`, `vat`, usr_users.`ip_address`, `real_name`, `card_origin`, `country_name`, case when payment_date is NULL then DATE(FROM_UNIXTIME(shop_orders.created)) when payment_date is not NULL then payment_date end as required_date, email,is_booking FROM `shop_orders` LEFT JOIN `shop_carts` ON `shop_carts`.`id` = `shop_orders`.`cart_id` LEFT JOIN `shop_articles_carts` ON `shop_carts`.`id` = `shop_articles_carts`.`cart_id` LEFT JOIN `feu_userdetails` ON `feu_userdetails`.`user_id` = `shop_carts`.`user_id` LEFT JOIN `feu_countries` ON `feu_countries`.`country_code` = `feu_userdetails`.`country` LEFT JOIN `usr_users` ON `usr_users`.`id` = `feu_userdetails`.`user_id` WHERE `is_booking` = 0 AND `payed` = 1 AND `username` != '' AND `total_sum` < 140000 AND `shop_orders`.`cancelled` = 0 AND YEAR(FROM_UNIXTIME(shop_orders.created)) = ".$cal_year." ORDER BY required_date DESC");
		$getMembership = $membershipsql->result_array();

		$getEventListData1Sql = $this->db->query("SELECT CASE WHEN `payment_date` is not NULL then`payment_date` WHEN `payment_date` is NULL then DATE(FROM_UNIXTIME(`shop_orders`.`created`)) END as required_date, `username`, `total_sum`, `payment_date`, `shop_orders`.`created`, `title`, `trn_id`, `order_id_masked`, `evt_events`.`id` as `evt_id`, `payment_method_id`, event_unique_id, email,is_booking FROM `shop_orders` LEFT JOIN `shop_carts` ON `shop_carts`.`id` = `shop_orders`.`cart_id` LEFT JOIN `feu_userdetails` ON `feu_userdetails`.`user_id` = `shop_carts`.`user_id` LEFT JOIN `shop_booking_details` ON `shop_booking_details`.`cart_id` = `shop_carts`.`id` LEFT JOIN `evt_events` ON `evt_events`.`id` = `shop_booking_details`.`event_id` LEFT JOIN `usr_users` ON `usr_users`.`id` = `feu_userdetails`.`user_id` WHERE `is_booking` = 1 AND `payed` = 1 AND `username` != '' AND `cancelled` =0 AND YEAR(FROM_UNIXTIME(shop_orders.created)) = ".$cal_year." GROUP BY `shop_booking_details`.`cart_id` ORDER BY `shop_orders`.`created` DESC");
		$getEventListData1 = $getEventListData1Sql->result_array();
		//echo $this->db->last_query();

		$getEventListData2Sql = $this->db->query("SELECT CASE WHEN `payment_date` is not NULL then`payment_date` WHEN `payment_date` is NULL then DATE(FROM_UNIXTIME(`shop_orders`.`created`)) END as required_date,`username`, `total_sum`, `payment_date`, `shop_orders`.`created`, `title`, `trn_id`, `order_id_masked`, `evt_events`.`id` as `evt_id`, `payment_method_id`, event_unique_id, email,is_booking FROM `shop_orders` LEFT JOIN `shop_carts` ON `shop_carts`.`id` = `shop_orders`.`cart_id` LEFT JOIN `feu_userdetails` ON `feu_userdetails`.`user_id` = `shop_carts`.`user_id` LEFT JOIN `shop_articles_carts` ON `shop_articles_carts`.`cart_id` = `shop_carts`.`id` LEFT JOIN `evt_events` ON `evt_events`.`id` = `shop_articles_carts`.`event_id` LEFT JOIN `usr_users` ON `usr_users`.`id` = `feu_userdetails`.`user_id` WHERE `is_booking` = 1 AND `payed` = 1 AND `username` != '' AND `shop_orders`.`cancelled` =0 AND `event_id` >0 AND YEAR(FROM_UNIXTIME(shop_orders.created)) = ".$cal_year." ORDER BY `shop_orders`.`created` DESC");
		$getEventListData2 = $getEventListData2Sql->result_array();
		//echo $this->db->last_query();

		$getCheckinListDataSql = $this->db->query("SELECT * from (SELECT `feu_userdetails`.`username`, `checkin_amount`, `confirmed`, `shop_orders`.`created`, `title`, `trasaction_id`, `order_id_masked`, `evt_events`.`id` as `evt_id`, `checkin_payment_method_id`,event_guest.order_id,`event_guest`.`id`, `event_guest`.`id` as checkin_id,cape, drink_price, `confirmed` as required_date, `event_guest`.`id` as gust_id, event_unique_id, email FROM `shop_orders` LEFT JOIN `event_guest` ON `event_guest`.`order_id` = `shop_orders`.`order_id` LEFT JOIN `feu_userdetails` ON `feu_userdetails`.`username` = `event_guest`.`username` LEFT JOIN `evt_events` ON `evt_events`.`id` = `event_guest`.`event_id` LEFT JOIN `usr_users` ON `usr_users`.`id` = `feu_userdetails`.`user_id` WHERE `checkin_payed` = 1 AND `checkin_amount` >0 AND `feu_userdetails`.`username` != '' AND `cancelled` =0 AND YEAR(confirmed) = ".$cal_year."  ORDER BY `event_guest`.`id` DESC) as a GROUP BY a.`order_id` order by a.id DESC");
		$getCheckinListData = $getCheckinListDataSql->result_array();

		$getEventListData = array_merge($getMembership, $getEventListData1, $getEventListData2, $getCheckinListData);
		//echo $this->db->last_query();die;
		//pr($getEventListData);

		foreach ($getEventListData as $key => $row)
		{
		    $vc_array_name[$key] = $row['required_date'];
		}
		array_multisort($vc_array_name, SORT_DESC, $getEventListData);
		// pr($getEventListData);
		
		if($getEventListData)
		{ 
			$html='<style>
				   	body {font-family: sans-serif;
					    font-size: 10pt;
					}
					p { margin: 0pt;
					}
					td { vertical-align: top; }
					.items td {
					    border-left: 0.1mm solid #000000;
					    border-right: 0.1mm solid #000000;
					}
					table thead td { background-color: #b3975b;
					    border: 0.1mm solid #000000;
					    color: #fff;
					}
					.items td.blanktotal {
					    background-color: #FFFFFF;
					    border: 0mm none #000000;
					    border-top: 0.1mm solid #000000;
					    border-right: 0.0mm solid #000000;
					}
					.items td.totals {
					    text-align: right;
					    border-top: 0.1mm solid #000000;
					    border-right: 0.0mm solid #000000;
					    border-left: 0.0mm solid #000000;
					}
					.items td.totals1 {
					    text-align: right;
					    border-top: 0.0mm solid #000000;
					    border-right: 0.0mm solid #000000;
					    border-left: 0.0mm solid #000000;
					}
				   </style>
				   <div style="padding: 50px 10px 70px;">
			          <p style="margin:0;">'.$site_address_name['value'].'</p>
			          <p style="margin:0;">'.$site_address_city['value'].',</p>
			          <p style="margin:0;">'.$site_address_country_zip['value'].'</p>
			          <p style="padding-top: 10px;">UID: CHE-362.535.263</p>
			        </div>							  
				         <h2 style="padding: 10px;border-bottom:1px solid #ccc; display: block;">UNPAID BOOKING INVOICE</h2>
				         <table class="items" width="100%" style="font-size: 9pt; border-collapse: collapse;" cellpadding="8">
						    <thead>
						        <tr>
							        <td style="padding:10px ; font-weight: bold; height: 19px; line-height: 19px; float: left;">Order no.</td>
					               	<td style="padding:10px ; font-weight: bold; float: left; height: 19px; line-height: 19px;">Date</td>
					               	<td style="padding:10px ; font-weight: bold; float: left; height: 19px; line-height: 19px;">Beschreibung</td>
					               	<td style="padding:10px ; font-weight: bold; float: left; height: 19px; line-height: 19px;">Event ID</td>
					               	<td style="padding:10px ; font-weight: bold; float: left; height: 19px; line-height: 19px;">Event Title</td>
					               	<td style=" font-weight: bold; padding:10px ; float: left; height: 19px; line-height: 19px;">Total(&euro;)</td>
					               	<td style=" font-weight: bold; padding:10px ; float: left; height: 19px; line-height: 19px;">Payment</td>
					               	<td style=" font-weight: bold; padding:10px ; float: left; height: 19px; line-height: 19px;">Username</td>
					               	<td style=" font-weight: bold; padding:10px ; float: left; height: 19px; line-height: 19px;">Email</td>
				         		</tr>
				         	</thead>';
				            if(!empty($getEventListData)){ 
					         		$member_total=0;
						            foreach ($getEventListData as $key=>$data) {
						            	$amount = 0; 
										if(isset($data['gust_id'])){
											if($data['gust_id'] < 323) { 
												$amount = number_format(((50*$data['cape'])+$data['drink_price']), 2, '.', ''); 
											} else { 
												$amount = number_format($data['checkin_amount'], 2, '.', ''); 
											}
										} else {
											$amount = number_format($data['total_sum']/100,2, '.', '');
										}
										$member_total+=$amount;
			$html.='					<tbody style="border-bottom:1px solid #000;">
											<tr style="width: 100%; float: left; padding-left:10px;">         	
								 			   	<td style="padding:10px ; height: 19px; line-height: 19px; float: left;">'.(isset($data['checkin_id']) ? str_replace('CE-', '', $data['order_id_masked']).'-'.$data['checkin_id'] : $data['order_id_masked']).'</td>
								               	<td style="padding:10px ; float: left; height: 19px; line-height: 19px;">'.date('d-m-Y',strtotime($data['required_date'])).'</td>
								               	<td style="padding:10px ; float: left; height: 19px; line-height: 19px;">'.(isset($data['checkin_id']) ? "Event check-in" : ($data['is_booking'] == 1) ? 'Eventservice' : 'Membership').'</td>
								               	<td style="padding:10px ; float: left; height: 19px; line-height: 19px;">'.(isset($data['event_unique_id']) ? 'Evt-'.$data['event_unique_id'] : '').'</td>
								               	<td style="padding:10px ; float: left; height: 19px; line-height: 19px;">'.(isset($data['event_unique_id']) ? $data['title'] : '').'</td>
								               	<td style=" padding:10px ; float: left; height: 19px; line-height: 19px;">'.$amount.'</td>';
								               	if($data['payment_method_id']==12){ 
			$html.=					               	'<td style="padding:10px ; height: 19px; line-height: 19px; float: left;">Bank Transfer</td>'; 
												} 
												elseif($data['payment_method_id']==13){ 
			$html.=									'<td style="padding:10px ; height: 19px; line-height: 19px; float: left;">Saferpay</td>'; 
												} 
												elseif($data['payment_method_id']==14){ 
			$html.=									'<td style="padding:10px ; height: 19px; line-height: 19px; float: left;">Stripe</td>'; 
												} 
												else{ 
			$html.=									'<td style="padding:10px ; height: 19px; line-height: 19px; float: left;">Pay By Bank</td>'; 
												}
			$html.=					        '<td style="word-break: break-all; padding:10px ; float: left; height: 19px; line-height: 19px;">';
								               	if($this->session->userdata('admin_id')=='19624' || $this->session->userdata('admin_id')=='25267'){ 
			$html.=					               	$data['username']; 
												} 
			$html.=					        '</td>
											<td style=" padding:10px ; float: left; height: 19px; line-height: 19px;">'.$data['email'].'</td>
											</tr>';	
									}
						        }
			$html.='			</tbody>
							</table>
							<table  width="100%" style="font-size: 9pt; border-collapse: collapse;" cellpadding="8">
                               <tbody>
                                   <tr style="background:#ddd;">
									<td style="padding:10px; border-top:2px solid #000; width:30%; font-weight: bold;">Total</td>
									<td style="padding:10px; border-top:2px solid #000; font-weight: bold;">'.number_format($member_total/100, 2,',', '.').' &euro;</td>
								 </tr>
                               </tbody>
							</table>';
		}		
        //echo $html;die;
        $pdfFilePath = "All paid invoices.pdf";
        $this->load->library('m_pdf');

        $this->m_pdf->pdf->useOnlyCoreFonts = true;    // false is default
		//$this->m_pdf->pdf->SetProtection(array('print'));
		$this->m_pdf->pdf->SetTitle("Castleevents Title");
		$this->m_pdf->pdf->SetAuthor("Author");
		/*$this->m_pdf->pdf->SetWatermarkText("Castleevents.com");
		$this->m_pdf->pdf->showWatermarkText = true;
		$this->m_pdf->pdf->watermark_font = 'DejaVuSansCondensed';
		$this->m_pdf->pdf->watermarkTextAlpha = 0.1;*/
		$this->m_pdf->pdf->SetDisplayMode('fullpage'); 
		$this->m_pdf->pdf->WriteHTML($html);
		$this->m_pdf->pdf->Output($pdfFilePath, "D"); 
        die;
	}
}