<?php
	/**
	 * Export users in the database in a csv format
	 * Francis Brassard <francis.brassard@transcontinental.ca>
	 * 2010/11/18
	 */
	header('Content-type: text/html; charset=utf-8');
	
	ini_set( 'display_errors', 1 );
	ini_set( 'error_reporting', E_ALL );
	
	if( preg_match( '/preprod\.lesaffaires\.com$/', $_SERVER['HTTP_HOST'] ) ) {
		$host = 'mtl2tcmysql01dmz.tcm.int';
		$username = 'lesaffaires';
		$password = 'prec3UFR';
		$db = 'preprod_lesaffaires';
	} elseif( preg_match( '/lesaffaires\.com$/', $_SERVER['HTTP_HOST'] ) ) {
		$host = 'mtl2tcmysql01dmz.tcm.int';
		$username = 'lesaffaires';
		$password = 'prec3UFR';
		$db = 'lesaffaires';
	} else {
		$host = '';
		$username = '';
		$password = '';
		$db = '';
	}
	 
	mysql_connect( $host, $username, $password );
	mysql_select_db( $db );
	
	$returnArray = array();
	
	$query = "
		SELECT
			user.id,
			user.username,
			user.firstname,
			user.lastname,
			user.email,
			user.zip,
			user.premium,
			user.birthdate,
			user.gender,
			user.city,
			user.country,
			user.title,
			user.company,
			user_size.name as company_size,
			user.informations_transcontinental,
			user.informations_partners,
			user_activity.name as nom_activite,
			user_education.name as niveau_education,
			user_profession.name as nom_profession,
			user_revenu.name as tranche_revenu
		FROM
			user
			LEFT JOIN user_activity ON user.activity = user_activity.id
			LEFT JOIN user_education ON user.education = user_education.id
			LEFT JOIN user_profession ON user.profession = user_profession.id
			LEFT JOIN user_revenu ON user.revenu = user_revenu.id
			LEFT JOIN user_size ON user.company_size = user_size.id
		ORDER BY
			username ASC
	";
	
	$sql = mysql_query( $query );
	
	$handle = fopen( 'users.csv', 'w+' );
	
	$titles = array(
		'id',
		'username',
		'firstname',
		'lastname',
		'email',
		'zip',
		'premium',
		'birthdate',
		'gender',
		'city',
		'country',
		'title',
		'company',
		'company_size',
		'informations_transcontinental',
		'informations_partners',
		'nom_activite',
		'niveau_education',
		'nom_profession',
		'tranche_revenu',
		'newsletters',
	);
	
	fputcsv( $handle, $titles );
	
	while( $row = mysql_fetch_assoc( $sql ) ) {
		
		$newsletters_query = "
			SELECT
				newsletter.title
			FROM
				newsletter_user
				LEFT JOIN newsletter ON newsletter.id = newsletter_user.newsletter_id AND newsletter_user.status = 1
			WHERE
				newsletter_user.user_id = " . $row['id'] . "
		";
		$newsletters_sql = mysql_query( $newsletters_query );
		$newsletters_array = array();
		while( $newsletter_row = mysql_fetch_assoc( $newsletters_sql ) ) {
			$newsletters_array[] = $newsletter_row['title'];
		}
		
		
		$row['newsletters'] = implode( ', ', $newsletters_array );
		
		fputcsv( $handle, $row );
		
		$returnArray[] = $row;
	}
	
	echo '<a href="users.csv">t&eacute;l&eacute;charger la liste d\'utilisateur</a>';
?>