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

class Csv_export extends CI_Controller {
	public function __construct()
	{//echo date('d-m-Y H:i:s'); die;
		parent::__construct();
		$this->load->model('common_model');
		/*if($this->session->userdata('user_id'))
		{
			redirect('profile');
		}*/
		$this->load->library('Mobile_detect');
		//$this->load->library('Excel');
	}
	
	public function export_membership(){
		//echo strtotime(date('2020-01-01')).'='.strtotime(date('2020-09-06'));die;
		$getUserListData = $this->common_model->getAllMultipleJoin('shop_orders',array('is_booking'=>0, 'payed'=>1,'username !='=> '', 'total_sum <'=>140000, 'shop_orders.cancelled'=>0, 'shop_orders.created >='=>strtotime(date('2020-01-01')), 'shop_orders.created <='=>strtotime(date('2020-12-04'))), 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;
		//echo '<pre>';print_r($getUserListData);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';

			$output_filename = 'Membership-Payments-2020.csv';
			$output_handle = @fopen( 'php://output', 'w' );
			
			header( 'Content-type: text/csv' );
			header( 'Content-Disposition: attachment; filename=' . $output_filename );
			
			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 (&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();
		}
		die();
	}

	public function export_booking(){
		//echo strtotime(date('2019-01-01')).'='.strtotime(date('2019-12-31'));die;
		$getUserListData = $this->common_model->getAllMultipleJoin('shop_orders',array('is_booking'=>1, 'payed'=>1,'username !='=> '', 'shop_orders.cancelled'=>0, 'shop_orders.created >='=>strtotime(date('2020-01-01')), 'shop_orders.created <='=>strtotime(date('2020-12-04'))), 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'), array('shop_booking_details','shop_booking_details.cart_id = shop_carts.id'), array('evt_events','evt_events.id = shop_booking_details.event_id')),'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, event_unique_id', 'shop_booking_details.cart_id');
		//echo $this->db->last_query();die;
		//echo '<pre>';print_r($getUserListData);die;
        if(!empty($getUserListData))
		{
			$csv_fields=array();
			$csv_fields[] = 'Rechnung';
			$csv_fields[] = 'Mitgliedsnummer';
			$csv_fields[] = 'Event ID';
			$csv_fields[] = 'Username';
			$csv_fields[] = 'Währung';
			$csv_fields[] = 'Betrag';
			$csv_fields[] = 'Zahlungsdatum';
			$csv_fields[] = 'Zahlungsart';
			$csv_fields[] = 'Transaktions Ref. Nr.';
			$csv_fields[] = 'Schweizer Kunden';
			$csv_fields[] = 'IP Adresse';
			$csv_fields[] = 'Kartenursprung';
			$csv_fields[] = 'Besitzername';
			$csv_fields[] = 'Land';

			$output_filename = 'Booking-Payments-2020.csv';
			$output_handle = @fopen( 'php://output', 'w' );
			
			header( 'Content-type: text/csv' );
			header( 'Content-Disposition: attachment; filename=' . $output_filename );
			
			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['Event ID']=$Result['event_unique_id'];
				$leadArray['Username']=html_entity_decode($Result['username']);
				$leadArray['Währung']='Euro (&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['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['Event ID']="";
			$leadArray['Username']="";
			$leadArray['Währung']="";
			$leadArray['Betrag']="";
			$leadArray['Zahlungsdatum']="";
			$leadArray['Zahlungsart']="";
			$leadArray['Transaktions Ref. Nr.']="";
			$leadArray['Schweizer Kunden']="";
			$leadArray['IP Adresse']="";
			$leadArray['Kartenursprung']="";
			$leadArray['Besitzername']="";
			$leadArray['Land']="";
			fputcsv( $output_handle, $leadArray );


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


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

			fclose( $output_handle ); 
			die();
		}
		die();
	}
}
	

