<?php

    error_reporting(E_ALL);
    
    require_once('../Classes/PHPExcel.php');
    
    //  Database Connection
    $db_connection = mysql_connect('localhost','root','');
    @mysql_select_db('ees_request',$db_connection) or die("Unable to select database.");
    mysql_query('SET NAMES utf8;',$db_connection);
  
    // Retrieve all users from the database in order to create user's xls file with your id
    $allUsersQuery = "SELECT DISTINCT application_form.`userId`,jos_users.surname, jos_users.name 
                      FROM `application_form`,jos_users, department, section
                      WHERE application_form.userId=jos_users.id AND `submited`='1'
                      AND application_form.departmentId=department.id AND application_form.sectionId=section.id AND department.id=section.departmentId
                      ORDER BY jos_users.surname, jos_users.name";
    $allUsersResult = mysql_query($allUsersQuery);
    $allUsersRow = mysql_fetch_array($allUsersResult);
    
    // Retrieve personal information of each user
    $userQuery = "SELECT DISTINCT application_form.`userId`,jos_users.surname, jos_users.name, jos_users.status,jos_users.mobileNumber, jos_users.email 
                  FROM `application_form`,jos_users
                  WHERE application_form.userId=jos_users.id AND `submited`='1'
                  AND jos_users.id='".$allUsersRow['userId']."'
                  ORDER BY jos_users.surname, jos_users.name";
    $userResult = mysql_query($userQuery);
    $userRow = mysql_fetch_array($userResult);
    
    // Retrieve degrees of each user
    $degreeQuery = "SELECT DISTINCT jos_users.id, jos_users.surname, jos_users.name, jos_users.mobileNumber, jos_users.email, degree.title, degree.university, degree_level.level, DATE_FORMAT(degree.degreeDate,'%d-%m-%Y') AS degreeDate_ , degree.grade  
                   FROM jos_users,degree,degree_level, application_form
                   WHERE jos_users.id=degree.userId AND application_form.userId=jos_users.id
                   AND degree.degreeLevelId=degree_level.id
                   AND `submited`='1'  
                   AND jos_users.id='".$allUsersRow['userId']."' ORDER BY degree.degreeLevelId";
    $degreeResult = mysql_query($degreeQuery);
    
    // Retrieve professional experience of user
    
    $proExpQuery = "SELECT DISTINCT jos_users.id, pro_experience.*, DATE_FORMAT(since, '%d-%m-%Y') AS since_, DATE_FORMAT(until, '%d-%m-%Y') AS until_, FORMAT(DATEDIFF(until,since)/30,1) AS dmonths
                    FROM jos_users,pro_experience, application_form
                    WHERE jos_users.id=pro_experience.userId 
                    AND application_form.userId=jos_users.id
                    AND jos_users.id='".$allUsersRow['userId']."' AND `submited`='1'";
    $proExpResult = mysql_query($proExpQuery);
    
    // Retrieve Educational experience of user 
    
    $educExpQuery = "SELECT DISTINCT jos_users.id, educ_experience.*, DATE_FORMAT(since, '%d-%m-%Y') AS since_, DATE_FORMAT(until, '%d-%m-%Y') AS until_, FORMAT(DATEDIFF(until,since)/30,1) AS dmonths
                     FROM jos_users,educ_experience, application_form
                     WHERE jos_users.id=educ_experience.userId 
                     AND application_form.userId=jos_users.id
                     AND jos_users.id='".$allUsersRow['userId']."' AND `submited`='1'";
    $educExpResult = mysql_query($educExpQuery); 
    
    // Retrieve Languages of user
    
    $langQuery = "SELECT DISTINCT jos_users.id, language_type.language, language.titleCertificate, language.snCertificate, language.teachability
                     FROM jos_users,language,language_type, application_form
                     WHERE jos_users.id=language.userId 
                     AND application_form.userId=jos_users.id
                     AND language.userId=jos_users.id
                     AND language.languageTypeId=language_type.id
                     AND jos_users.id='".$allUsersRow['userId']."' AND `submited`='1'";
    $langResult = mysql_query($langQuery);
    
    // Retrieve Scientific Activity of user   
    
    $sntActQuery ="SELECT DISTINCT jos_users.id, scientific_activity.* 
                   FROM jos_users,scientific_activity, application_form
                   WHERE jos_users.id=scientific_activity.userId 
                   AND application_form.userId=jos_users.id
                   AND scientific_activity.userId=jos_users.id
                   AND jos_users.id='".$allUsersRow['userId']."' AND `submited`='1'";
    $sntActResult = mysql_query($sntActQuery);
    

    // Create new PHPExcel object
    $objPHPExcel = new PHPExcel();
  
    // Add column's title for id,surname,name,email,mobile phone
    $objPHPExcel->getActiveSheet()->setCellValue('A2','Id');
    $objPHPExcel->getActiveSheet()->setCellValue('B2','Επώνυμο');
    $objPHPExcel->getActiveSheet()->setCellValue('C2','Όνομα');
    $objPHPExcel->getActiveSheet()->setCellValue('D2','Ιδιότητα');
    $objPHPExcel->getActiveSheet()->setCellValue('E2','Κιν.Τηλέφωνο');
    $objPHPExcel->getActiveSheet()->setCellValue('F2', 'Email'); 

    // Add entries of Personal information of user id,surname,name,email,mobile phone
    $objPHPExcel->getActiveSheet()->setCellValue('A3', $userRow['userId']);
    $objPHPExcel->getActiveSheet()->setCellValue('B3', $userRow['surname']);
    $objPHPExcel->getActiveSheet()->setCellValue('C3', $userRow['name']);
    $objPHPExcel->getActiveSheet()->setCellValue('D3', $userRow['status']);
    $objPHPExcel->getActiveSheet()->setCellValue('E3', $userRow['mobileNumber']);
    $objPHPExcel->getActiveSheet()->setCellValue('F3', $userRow['email']); 
    
    // Add column's title for DEGREE
    $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Επίπεδο Πτυχίου');
    $objPHPExcel->getActiveSheet()->setCellValue('B5', 'Τίτλος Πτυχίου/Διπλώματος');
    $objPHPExcel->getActiveSheet()->setCellValue('C5', 'Ίδρυμα');
    $objPHPExcel->getActiveSheet()->setCellValue('D5', 'Ημ/νία Λήψης');
    $objPHPExcel->getActiveSheet()->setCellValue('E5', 'Βαθμός');
    
    // Add entries of DEGREEs
    $cell = 6;
    while( $degreeRow = mysql_fetch_array($degreeResult) ) {
    
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$cell, $degreeRow['level']);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$cell, $degreeRow['title']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$cell, $degreeRow['university']);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$cell, $degreeRow['degreeDate_']);
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$cell, $degreeRow['grade']);
        $cell++;   
    }

    // Rename sheet  
    $objPHPExcel->getActiveSheet()->setTitle('Σπουδές');
    // END OF DEGREEs ------------------------------------------------------------------------------------  
    
    // Create a new worksheet Pro Experience, after the default sheet ------------------------------------
    $objPHPExcel->createSheet();
    
    // Add columns to the sheet Pro Experience for user's informations
    $objPHPExcel->setActiveSheetIndex(1);
    $objPHPExcel->getActiveSheet()->setCellValue('A2','Id');
    $objPHPExcel->getActiveSheet()->setCellValue('B2','Επώνυμο');
    $objPHPExcel->getActiveSheet()->setCellValue('C2','Όνομα');
    
    // Add entry of user's informations
    $objPHPExcel->getActiveSheet()->setCellValue('A3', $userRow['userId']);
    $objPHPExcel->getActiveSheet()->setCellValue('B3', $userRow['surname']);
    $objPHPExcel->getActiveSheet()->setCellValue('C3', $userRow['name']);
    
    // Add column's title for Pro experience
    $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Αντικείμενο');
    $objPHPExcel->getActiveSheet()->setCellValue('B5', 'Ιδιότητα');
    $objPHPExcel->getActiveSheet()->setCellValue('C5', 'Από');
    $objPHPExcel->getActiveSheet()->setCellValue('D5', 'Έως');
    $objPHPExcel->getActiveSheet()->setCellValue('E5', 'Μήνες');  
    $objPHPExcel->getActiveSheet()->setCellValue('F5', 'Αρ.Δικαιολογητικού'); 
    
    // Add entries of Pro Experience
    $cell = 6;
    while( $proExpRow = mysql_fetch_array($proExpResult) ) {
    
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$cell, $proExpRow['scope']);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$cell, $proExpRow['status']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$cell, $proExpRow['since_']);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$cell, $proExpRow['until_']);
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$cell, $proExpRow['dmonths']);
        $objPHPExcel->getActiveSheet()->setCellValue('F'.$cell, $proExpRow['docNumber']);  
        $cell++;   
    }

    // Rename sheet  
    $objPHPExcel->getActiveSheet()->setTitle('Επαγγ.Εμπειρία'); 
    
    // END OF PRO EXPERIENCE ------------------------------------------------------------------------------- 
    
    // Create a new worksheet Educ Experience, after the Pro Experience sheet ------------------------------
    $objPHPExcel->createSheet();
    
    // Add columns to the sheet Educ Experience for user's informations
    $objPHPExcel->setActiveSheetIndex(2);
    $objPHPExcel->getActiveSheet()->setCellValue('A2','Id');
    $objPHPExcel->getActiveSheet()->setCellValue('B2','Επώνυμο');
    $objPHPExcel->getActiveSheet()->setCellValue('C2','Όνομα');
    
    // Add entry of user's informations
    $objPHPExcel->getActiveSheet()->setCellValue('A3', $userRow['userId']);
    $objPHPExcel->getActiveSheet()->setCellValue('B3', $userRow['surname']);
    $objPHPExcel->getActiveSheet()->setCellValue('C3', $userRow['name']);
    
    // Add column's title for Educ experience
    $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Μάθημα');
    $objPHPExcel->getActiveSheet()->setCellValue('B5', 'Εκπ.Ίδρυμα');
    $objPHPExcel->getActiveSheet()->setCellValue('C5', 'Τμήμα');
    $objPHPExcel->getActiveSheet()->setCellValue('D5', 'Από');
    $objPHPExcel->getActiveSheet()->setCellValue('E5', 'Έως');  
    $objPHPExcel->getActiveSheet()->setCellValue('F5', 'Εξάμηνα');
    $objPHPExcel->getActiveSheet()->setCellValue('G5', 'Μήνες');  
    $objPHPExcel->getActiveSheet()->setCellValue('H5', 'Αρ.Δικαιολογητικού'); 
        
    // Add entries of Educ Experience
    $cell = 6;
    while( $educExpRow = mysql_fetch_array($educExpResult) ) {
    
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$cell, $educExpRow['lesson']);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$cell, $educExpRow['educInstitution']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$cell, $educExpRow['department']);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$cell, $educExpRow['since_']);
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$cell, $educExpRow['until_']);
        $objPHPExcel->getActiveSheet()->setCellValue('F'.$cell, $educExpRow['semesters']); 
        $objPHPExcel->getActiveSheet()->setCellValue('G'.$cell, $educExpRow['dmonths']);
        $objPHPExcel->getActiveSheet()->setCellValue('H'.$cell, $educExpRow['docNumber']);   
        $cell++;   
    }
    
    // Rename sheet  
    $objPHPExcel->getActiveSheet()->setTitle('Εκπαιδ.Εμπειρία'); 
    
    // END OF EDUC EXPERIENCE ------------------------------------------------------------------------------

    // Create a new worksheet Languages, after the Educ Experience sheet -----------------------------------
    $objPHPExcel->createSheet();
    
    // Add columns to the sheet Languages for user's informations
    $objPHPExcel->setActiveSheetIndex(3);
    $objPHPExcel->getActiveSheet()->setCellValue('A2','Id');
    $objPHPExcel->getActiveSheet()->setCellValue('B2','Επώνυμο');
    $objPHPExcel->getActiveSheet()->setCellValue('C2','Όνομα');

    // Add entry of user's informations
    $objPHPExcel->getActiveSheet()->setCellValue('A3', $userRow['userId']);
    $objPHPExcel->getActiveSheet()->setCellValue('B3', $userRow['surname']);
    $objPHPExcel->getActiveSheet()->setCellValue('C3', $userRow['name']);

    // Add column's title for Languages
    $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Γλώσσα');
    $objPHPExcel->getActiveSheet()->setCellValue('B5', 'Τίτλος Πιστοποιητικού');
    $objPHPExcel->getActiveSheet()->setCellValue('C5', 'Διδακτ.Ικανότητα');
    $objPHPExcel->getActiveSheet()->setCellValue('D5', 'Αρ.Δικαιολογητικού');
    
    // Add entries of Languages
    $cell = 6;
    while( $langRow = mysql_fetch_array($langResult) ) {
    
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$cell, $langRow['language']);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$cell, $langRow['titleCertificate']);
            if( $langRow['teachability'] == '0' ) $teachability = "ΟΧΙ";
            else $teachability = "ΝΑΙ";
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$cell, $teachability); 
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$cell, $langRow['snCertificate']);
        $cell++;   
    }
    
    // Rename sheet  
    $objPHPExcel->getActiveSheet()->setTitle('Ξένες_Γλώσσες'); 
    
    // END OF LANGUAGES --------------------------------------------------------------------------------  
    
     // Create a new worksheet Scientific Activity, after the Educ Experience sheet --------------------
    $objPHPExcel->createSheet();
    
    // Add columns to the sheet Scientific Activity for user's informations
    $objPHPExcel->setActiveSheetIndex(4);
    $objPHPExcel->getActiveSheet()->setCellValue('A2','Id');
    $objPHPExcel->getActiveSheet()->setCellValue('B2','Επώνυμο');
    $objPHPExcel->getActiveSheet()->setCellValue('C2','Όνομα');
    
    // Add entry of user's informations
    $objPHPExcel->getActiveSheet()->setCellValue('A3', $userRow['userId']);
    $objPHPExcel->getActiveSheet()->setCellValue('B3', $userRow['surname']);
    $objPHPExcel->getActiveSheet()->setCellValue('C3', $userRow['name']);

    // Add column's title for Scientific Activity
    $objPHPExcel->getActiveSheet()->setCellValue('A5', 'Συγγραφέας');
    $objPHPExcel->getActiveSheet()->setCellValue('B5', 'Τίτλος');
    $objPHPExcel->getActiveSheet()->setCellValue('C5', 'Περιοδικό/Συνέδριο');
    $objPHPExcel->getActiveSheet()->setCellValue('D5', 'Τόμος');
    $objPHPExcel->getActiveSheet()->setCellValue('E5', 'Σελίδες');
    $objPHPExcel->getActiveSheet()->setCellValue('F5', 'Έτος');
    $objPHPExcel->getActiveSheet()->setCellValue('G5', 'Αρ.Δικαιολογητικού');
    
    // Add entries of Scientific Activity
    $cell = 6;
    while( $sntActRow = mysql_fetch_array($sntActResult) ) {
    
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$cell, $sntActRow['author']);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$cell, $sntActRow['title']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$cell, $sntActRow['magazineCouncil']);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$cell, $sntActRow['tome']); 
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$cell, $sntActRow['page']);
        $objPHPExcel->getActiveSheet()->setCellValue('F'.$cell, $sntActRow['year']);
        $objPHPExcel->getActiveSheet()->setCellValue('G'.$cell, $sntActRow['docNumber']); 
        $cell++;   
    }
    
    // Rename sheet  
    $objPHPExcel->getActiveSheet()->setTitle('Επιστ.Δραστηριότητα'); 
    
    // END OF Scientific Activity ---------------------------------------------------------------------------     
     
        
    // Set active sheet index to the first sheet, so Excel opens this as the first sheet
    $objPHPExcel->setActiveSheetIndex(0);
    
    // Save Excel 2003 file
    
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('f_'.$allUsersRow['userId'].'.xls'); 

?>