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

class Cron_month_payment_records extends CI_Controller
{
	public function index()
	{
		//echo date('Y-m-d H:i:s');
		$this->load->model('common_model');
		$cal_year = date('Y', strtotime(date('Y-m')." -1 month"));
		$cal_month = date('m', strtotime(date('Y-m')." -1 month"));
		$cal_mon = date('M', strtotime(date('Y-m')." -1 month"));

		$membership_where = "is_booking = 0 AND payed = 1 AND username != '' AND total_sum < 140000 AND shop_orders.cancelled = 0 AND MONTH(FROM_UNIXTIME(shop_orders.created)) = ".$cal_month." AND YEAR(FROM_UNIXTIME(shop_orders.created)) = ".$cal_year;
		$getUserListData = $this->common_model->getAllMultipleJoin('shop_orders',$membership_where, array(array('shop_carts','shop_carts.id = shop_orders.cart_id'), array('shop_articles_carts','shop_carts.id = shop_articles_carts.cart_id'), array('feu_userdetails','feu_userdetails.user_id = shop_carts.user_id'),  array('feu_countries','feu_countries.country_code = feu_userdetails.country')),'shop_orders.created DESC','username, total_sum, payment_date, shop_orders.created, time, trn_id, article_id, order_id_masked, payment_method_id, vat, ip_address, real_name, card_origin, feu_userdetails.user_id as user_id, country_name');
		//echo $this->db->last_query();die;
        if(!empty($getUserListData))
		{
			$csv_fields=array();
			$csv_fields[] = 'Rechnung';
			$csv_fields[] = 'Mitgliedsnummer';
			$csv_fields[] = 'Username';
			$csv_fields[] = 'Währung';
			$csv_fields[] = 'Betrag';
			$csv_fields[] = 'Zahlungsdatum';
			$csv_fields[] = 'Zahlungsart';
			$csv_fields[] = 'Transaktions Ref. Nr.';
			$csv_fields[] = 'Mitgliedschaft';
			$csv_fields[] = 'Laufzeit';
			$csv_fields[] = 'Mitgliedschaft bis';
			$csv_fields[] = 'Schweizer Kunden';
			$csv_fields[] = 'IP Adresse';
			$csv_fields[] = 'Kartenursprung';
			$csv_fields[] = 'Besitzername';
			$csv_fields[] = 'Land';

			$fileNameExcel1 = FCPATH.'assets/userfiles/monthly_report/Membership-Payments_'.$cal_mon.'-'.$cal_year.'.csv'; 
			$output_handle = @fopen( $fileNameExcel1, 'w' );
			
			fputcsv( $output_handle, $csv_fields );
			$i=1;
			$total = 0;
			foreach ($getUserListData as $Result) {
				$total = $total + $Result['total_sum'];
				//$leadArray = (array) $Result; // Cast the Object to an array
				$leadArray['Rechnung']=$Result['order_id_masked'];
				$leadArray['Mitgliedsnummer']=$Result['user_id'];
				$leadArray['Username']=html_entity_decode($Result['username']);
				$leadArray['Währung']='Euro (€)';
				$leadArray['Betrag']=number_format($Result['total_sum']/100,2,',','.');
				$leadArray['Zahlungsdatum']=($Result['payment_date']!='') ? date('d-m-Y',strtotime($Result['payment_date'])) : date('d-m-Y',$Result['created']);
				$running = 0;
				if($Result['payment_method_id']==13){
					$payment_method_id = 'Six Payment Services';
				} elseif($Result['payment_method_id']==14){
					$payment_method_id = 'Stripe';
				} else {
					$payment_method_id = 'Banküberweisung';
				}
				$leadArray['Zahlungsart']=$payment_method_id;
				$leadArray['Transaktions Ref. Nr.']=$Result['trn_id'];
				$leadArray['Mitgliedschaft']=($Result['article_id']==4 || $Result['total_sum'] > 50000) ? "Premium" : "Basic";
				$leadArray['Laufzeit']=($Result['time']==365) ? "12 Monate" : "6 Monate";
				$leadArray['Mitgliedschaft bis']=date('d-m-Y', ($Result['created']+($Result['time']*86400)));
				$leadArray['Schweizer Kunden']=($Result['vat']==0) ? "No" : "Yes";
				$leadArray['IP Adresse']=$Result['ip_address'];
				$leadArray['Kartenursprung']=$Result['card_origin'];
				$leadArray['Besitzername']=$Result['real_name'];
				$leadArray['Land']=$Result['country_name'];
				fputcsv( $output_handle, $leadArray );
				$i++;
			}

			$leadArray['Rechnung']="";
			$leadArray['Mitgliedsnummer']="";
			$leadArray['Username']="";
			$leadArray['Währung']="";
			$leadArray['Betrag']="";
			$leadArray['Zahlungsdatum']="";
			$leadArray['Zahlungsart']="";
			$leadArray['Transaktions Ref. Nr.']="";
			$leadArray['Mitgliedschaft']="";
			$leadArray['Laufzeit']="";
			$leadArray['Mitgliedschaft bis']="";
			$leadArray['Schweizer Kunden']="";
			$leadArray['IP Adresse']="";
			$leadArray['Kartenursprung']="";
			$leadArray['Besitzername']="";
			$leadArray['Land']="";
			fputcsv( $output_handle, $leadArray );


			$leadArray['Rechnung']="";
			$leadArray['Mitgliedsnummer']="";
			$leadArray['Username']="";
			$leadArray['Währung']="";
			$leadArray['Betrag']="";
			$leadArray['Zahlungsdatum']="";
			$leadArray['Zahlungsart']="";
			$leadArray['Transaktions Ref. Nr.']="";
			$leadArray['Mitgliedschaft']="";
			$leadArray['Laufzeit']="";
			$leadArray['Mitgliedschaft bis']="";
			$leadArray['Schweizer Kunden']="";
			$leadArray['IP Adresse']="";
			$leadArray['Kartenursprung']="";
			$leadArray['Besitzername']="";
			$leadArray['Land']="";
			fputcsv( $output_handle, $leadArray );


			$leadArray['Rechnung']="";
			$leadArray['Mitgliedsnummer']="";
			$leadArray['Username']="Total";
			$leadArray['Währung']='Euro (€)';
			$leadArray['Betrag']=number_format($total/100,2,',','.');
			$leadArray['Zahlungsdatum']="";
			$leadArray['Zahlungsart']="";
			$leadArray['Transaktions Ref. Nr.']="";
			$leadArray['Mitgliedschaft']="";
			$leadArray['Laufzeit']="";
			$leadArray['Mitgliedschaft bis']="";
			$leadArray['Schweizer Kunden']="";
			$leadArray['IP Adresse']="";
			$leadArray['Kartenursprung']="";
			$leadArray['Besitzername']="";
			$leadArray['Land']="";
			fputcsv( $output_handle, $leadArray );

			fclose( $output_handle ); 
		}
		//die;
		/*****************participation*********************/

		$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 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` WHERE `is_booking` = 1 AND `payed` = 1 AND `username` != '' AND `cancelled` =0 AND MONTH(FROM_UNIXTIME(shop_orders.created)) = ".$cal_month." 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 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` WHERE `is_booking` = 1 AND `payed` = 1 AND `username` != '' AND `shop_orders`.`cancelled` =0 AND `event_id` >0 AND MONTH(FROM_UNIXTIME(shop_orders.created)) = ".$cal_month." 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`,cape, drink_price, `confirmed` as required_date, `event_guest`.`id` as gust_id, event_unique_id 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` WHERE `checkin_payed` = 1 AND `checkin_amount` >0 AND `feu_userdetails`.`username` != '' AND `cancelled` =0 AND MONTH(confirmed) = ".$cal_month." 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($getEventListData1, $getEventListData2, $getCheckinListData);
		//echo $this->db->last_query();die;
		//pr($getEventListData);
        if(!empty($getEventListData))
		{
			$csv_fields1=array();
			$csv_fields1[] = 'Rechnung';
			$csv_fields1[] = 'Username';
			$csv_fields1[] = 'Währung';
			$csv_fields1[] = 'Betrag';
			$csv_fields1[] = 'Zahlungsdatum';
			$csv_fields1[] = 'Event ID';
			$csv_fields1[] = 'Beschreibung';
			$csv_fields1[] = 'Bezeichnung';
			$csv_fields1[] = 'Zahlungsart';
			$csv_fields1[] = 'Transaktions Ref. Nr.';

			$fileNameExcel2 = FCPATH.'assets/userfiles/monthly_report/Eventbuchungen_'.$cal_mon.'-'.$cal_year.'.csv';  
			$output_filename = 'Eventbuchungen.csv';
			$output_handle1 = @fopen( $fileNameExcel2, 'w' );
			
			fputcsv( $output_handle1, $csv_fields1 );
			$i=1;
			foreach ($getEventListData as $Result1) {
				//$leadArray = (array) $Result1; // Cast the Object to an array
				$leadArray1['Rechnung']=$Result1['order_id_masked'];
				$leadArray1['Username']=html_entity_decode($Result1['username']);
				$leadArray1['Währung']='Euro (€)';

				$amount = 0; 
				if(isset($Result1['gust_id'])){
					if($Result1['gust_id'] < 323) { 
						$amount = number_format(((50*$Result1['cape'])+$Result1['drink_price']), 2,',', '.'); 
					} else { 
						$amount = number_format($Result1['checkin_amount'], 2,',', '.'); 
					}
				} else {
					$amount = number_format($Result1['total_sum']/100,2,',','.');
				}
				$leadArray1['Betrag']=$amount;

				$leadArray1['Zahlungsdatum']=date('d-m-Y',strtotime($Result1['required_date']));
				$leadArray1['Event ID']='Evt-'.$Result1['event_unique_id'];
				$leadArray1['Beschreibung']=isset($Result1['confirmed']) ? "Event check-in" : "Eventservice";
				$leadArray1['Bezeichnung']=eventDetails($Result1['evt_id']);
				if(isset($Result1['checkin_payment_method_id'])){
					if($Result1['checkin_payment_method_id']==1){
						$payment_method_id = 'Six Payment Services';
					} else if($Result1['checkin_payment_method_id']==2){
						$payment_method_id = 'Premium users need not pay';
					} else {
						$payment_method_id = 'Banküberweisung';
					}
				} else {
					if($Result1['payment_method_id']==13){
						$payment_method_id = 'Six Payment Services';
					} elseif($Result1['payment_method_id']==14){
						$payment_method_id = 'Stripe';
					} else {
						$payment_method_id = 'Banküberweisung';
					}
				}
				$leadArray1['Zahlungsart']=$payment_method_id;
				$leadArray1['Transaktions Ref. Nr.']=isset($Result1['trasaction_id']) ? $Result1['trasaction_id'] : $Result1['trn_id'];
				fputcsv( $output_handle1, $leadArray1 );
				$i++;
			}
			fclose( $output_handle1 ); 
		}


	


		$to = 'info@anexus.ch';
		//$to = 'testdevloper007@gmail.com';
		$sub = 'Monthly Report for '.$cal_mon.'-'.$cal_year;
		$body= '<p>Please find the attached files.</p>';
		

		$from='info@castleevents.com';
		$email_config = Array(
			'protocol' => 'smtp',
              'smtp_host' => 'smtp.mandrillapp.com',
              'smtp_user' => 'castleevents',
              'smtp_pass' => 'TveFwCTbtHjorrsBDKwJ8Q',
              'smtp_port' => 587,
              'mailtype' => 'html',
              'crlf' => "rn",
              'newline' => "rn",
              'charset' => 'utf-8',
              'wordwrap' => TRUE
		);

		$this->load->library('email');
		$this->email->initialize($email_config);
		$this->email->from($from, 'CASTLEEVENTS');
		$this->email->to($to); 
		//$this->email->bcc('info@castleevents.com');  
		
		$this->email->subject($sub);
		$this->email->message($body);
		$this->email->attach($fileNameExcel1);
		$this->email->attach($fileNameExcel2);
		$this->email->attach($fileNameExcel3);
		$mail = $this->email->send();

		@unlink($fileNameExcel1);
		@unlink($fileNameExcel2);
	}
}