<?php


// load library
require 'php-excel.class.php';

    $dId = "2";
    $sId = "5";
    

    $conn = mysql_connect('localhost','root','');
    mysql_select_db('ees_request',$conn);
    mysql_query("SET NAMES 'utf8'",$conn);
    
    $sql0 = "SELECT DISTINCT application_form.`userId`,jos_users.surname, jos_users.name, application_form.departmentId,application_form.sectionId, department.departmentName, section.sectionTitle 
             FROM `application_form`,jos_users, department, section
             WHERE application_form.userId=jos_users.id AND `submited`='1' AND application_form.`departmentId`='".$dId."' AND `sectionId`='".$sId."'
             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";
    $result0 = mysql_query($sql0);
    $row0 = mysql_fetch_array($result0);   
    $nrows0 = mysql_num_rows($result0);
 
 $data2 = array(
            1 => array('Τμήμα: '.$row0['departmentName']),
                 array($row0['sectionTitle']),
                 array(''),  
                 array('Α/Α','Επώνυμο','Όνομα','Κινητό Τηλέφωνο', 'E-Mail','Διδακτορικό','Master','Πανεπιστήμιο','ΤΕΙ','Επαγγελματική Εμπειρία', 'Εκπαιδευτική Εμπειρία', 'Ξένες Γλώσσες','Επιστημονική Δραστηριότητα', 'Ώρες Διαθεσιμότητας'));

// generate file (constructor parameters are optional)
$xls = new Excel_XML('UTF-8', false, 'Αναλυτική Κατάσταση'); 
$xls->addArray($data2);
$filename = substr($row0['sectionTitle'],0,10);  
$xls->openGenerator("file_".$row0['departmentId']."_".$row0['sectionId']);
    
    for( $i = 1; $i <= $nrows0; $i++ )  {
        
        
        $id = $row0['userId'];      
        
    $sql01 = "SELECT DISTINCT application_form.`userId`,jos_users.surname, jos_users.name,jos_users.mobileNumber, jos_users.email 
             FROM `application_form`,jos_users
             WHERE application_form.userId=jos_users.id AND `submited`='1' AND `departmentId`='".$dId."' AND `sectionId`='".$sId."'
             AND jos_users.id='".$id."'
             ORDER BY jos_users.surname, jos_users.name";
    $result01 = mysql_query($sql01);
    $row01 = mysql_fetch_array($result01);
        
     // Show DEGREES for each user ---
        $sql = "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='".$id."' AND application_form.departmentId='".$dId."' AND application_form.sectionId='".$sId."' ORDER BY jos_users.surname, jos_users.name";
        $result = mysql_query($sql);
        
        $nrow = mysql_num_rows($result);
        $row = mysql_fetch_array($result);
        
            $name =  $row01['name'];
            $surname = $row01['surname'];
            $mobileNumber = $row01['mobileNumber'];
            $email = $row01['email'];
            
            do{
                if( $row['level'] == "Διδακτορικό") {
                    $title = $row['title'];
                    $department = $row['university'];
                    $level = $row['level'];  
                    $degreeDate = $row['degreeDate_'];
                    $grade = $row['grade'];
                    $strDegree1 = $strDegree1."-".$title."-".$department."-".$level."-".$degreeDate."-".$grade." #";              
                }
                 
                if( $row['level'] == "Μεταπτυχιακό") {
                    $title = $row['title'];
                    $department = $row['university'];
                    $level = $row['level'];  
                    $degreeDate = $row['degreeDate_'];   
                    $grade = $row['grade'];
                    $strDegree2 = $strDegree2."-".$title."-".$department."-".$level."-".$degreeDate."-".$grade." #";        
                }
                if( $row['level'] == "Πανεπιστήμιο") {
                    $title = $row['title'];
                    $department = $row['university'];
                    $level = $row['level'];  
                    $degreeDate = $row['degreeDate_'];   
                    $grade = $row['grade'];
                    $strDegree3 = $strDegree3."-".$title."-".$department."-".$level."-".$degreeDate."-".$grade." #";              
                }
                if( $row['level'] == "ΤΕΙ") {
                    $title = $row['title'];
                    $department = $row['university'];
                    $level = $row['level'];  
                    $degreeDate = $row['degreeDate_'];   
                    $grade = $row['grade'];
                    $strDegree4 = $strDegree4."-".$title."-".$department."-".$level."-".$degreeDate."-".$grade." #";            
                }
         
            } while( $row = mysql_fetch_array($result));
    // END OF DEGREES ---         

    // Show PRO_EXPERIENCE for each user ---        
            $sql2 = "SELECT DISTINCT jos_users.id, pro_experience.*, DATE_FORMAT(since, '%d-%m-%Y') AS since_, DATE_FORMAT(until, '%d-%m-%Y') AS until_
                     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='".$row0['userId']."' AND `submited`='1' 
                     AND application_form.departmentId='".$dId."' AND application_form.sectionId='".$sId."'";
                     
            $result2 = mysql_query($sql2);
            
            while ( $row2 = mysql_fetch_array($result2)) {
                
                $scope = $row2['scope'];
                $status = $row2['status'];
                $docNumber = $row2['docNumber'];
                $since = $row2['since_'];
                $until = $row2['until_'];
                $strProExpr = $strProExpr."-".$scope."-".$status." ΑΠΟ ".$since." ΕΩΣ ".$until." Αρ.Δικ: ".$docNumber." #";
            }
    // END OF PRO_EXPERIENCE
    
    // Show EDUC_EXPERIENCE for each user
    
            $sql3 = "SELECT DISTINCT jos_users.id, educ_experience.*, DATE_FORMAT(since, '%d-%m-%Y') AS since_, DATE_FORMAT(until, '%d-%m-%Y') AS until_
                     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='".$row0['userId']."' AND `submited`='1'
                     AND application_form.departmentId='".$dId."' AND application_form.sectionId='".$sId."';";
            
            $result3 = mysql_query($sql3);
            
            while( $row3 = mysql_fetch_array( $result3 ) ) {
                
                $lesson = $row3['lesson'];
                $educInstitution = $row3['educInstitution'];
                $department = $row3['department'];
                $since = $row3['since_'];
                $until = $row3['until_'];
                $semesters = $row3['semesters'];
                $docNumber = $row3['docNumber'];
                
                $strEducExpr = $strEducExpr."-".$lesson."-".$educInstitution."-".$department." ΑΠΟ ".$since." ΕΩΣ ".$until." Εξάμ.: ".$semesters." Αρ.Δικ: ".$docNumber." #";
            }
    // END OF EDUC_EXPERIENCE
    
    // Show LANGUAGES for each user
            
            $sql4 = "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='".$row0['userId']."' AND `submited`='1' 
                     AND application_form.departmentId='".$dId."' AND application_form.sectionId='".$sId."'";
            
            $result4 = mysql_query($sql4);
            
            while( $row4 = mysql_fetch_array( $result4 ) ) {
                
                $language         = $row4['language'];
                $titleCertificate = $row4['titleCertificate']; 
                $snCertificate    = $row4['snCertificate'];
                if( $row4['teachabilitiy'] == '0' ) $teachability = "ΟΧΙ";
                else $teachability = "ΝΑΙ";
                
                $strLanguages = $strLanguages."-".$language."-".$titleCertificate." Αρ.Δικ: ".$snCertificate." Διδ.Ικανότητα: ".$teachability." #";
            }
    // END OF LANGUAGES
    
    // Show SCIENTIFIC_ACTIVITY for each user
            
            $sql5 = "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='".$row0['userId']."' AND `submited`='1' 
                     AND application_form.departmentId='".$dId."' AND application_form.sectionId='".$sId."'";
            $result5 = mysql_query($sql5);
            
            while( $row5 = mysql_fetch_array( $result5 ) ) {
                $author = $row5['author'];
                $title = $row5['title'];
                $magazineCouncil = $row5['magazineCouncil'];
                $tome = $row5['tome'];
                $page = $row5['page'];
                $year = $row5['year'];
                $docNumber = $row5['docNumber'];
                
                $strSntActy = $strSntActy."-".$author."-".$title."-".$magazineCouncil."-".$tome."-".$page."-".$year." Αρ.Δικ: ".$docNumber." #";
            }         
    // END OF SCIENTIFIC_ACTIVITY
    
    // Show AVAILABLE_HOURS for each user
            $sql6 = "SELECT DISTINCT jos_users.id, available_hours.* 
                     FROM jos_users,available_hours, application_form
                     WHERE jos_users.id=available_hours.userId 
                     AND application_form.userId=jos_users.id
                     AND jos_users.id='".$row0['userId']."' AND `submited`='1' 
                     AND application_form.departmentId='".$dId."' AND application_form.sectionId='".$sId."'";
            $result6 = mysql_query($sql6);
            
            while( $row6 = mysql_fetch_array( $result6) ) {
                
                $day = $row6['day'];  
                $fromHour = $row6['fromHour']; 
                $toHour = $row6['toHour'];
                $comment = $row6['comment'];
                
                $strAvailHrs = $strAvailHrs."-".$day." ΑΠΟ ".$fromHour." ΕΩΣ ".$toHour."-".$comment." # ";
            }
    
    // END OF AVAILABLE_HOURS
                           
            $data2 = array(
                array($i, mb_strtoupper($surname,'UTF-8'), mb_strtoupper($name,'UTF-8'), $mobileNumber, $email, 
                      $strDegree1, $strDegree2, $strDegree3, $strDegree4, 
                      $strProExpr, $strEducExpr, $strLanguages, $strSntActy, $strAvailHrs, ''));  
            
            $xls->addArray($data2);
            
            $row0 = mysql_fetch_array($result0);    
                    
            $strDegree1   = '';
            $strDegree2   = '';  
            $strDegree3   = '';  
            $strDegree4   = '';  
            $strProExpr   = '';  
            $strEducExpr  = '';
            $strLanguages = '';
            $strSntActy   = '';
            $strAvailHrs  = '';
    } 

// create a simple 2-dimensional array
/*
$data = array(
        1 => array ('Name', 'Surname'),
        array('Schwarz', 'Oliver'),
        array('Test', 'Peter')
        );
*/


$xls->generateXML('my-test');

       
    $sql7 = "SELECT * FROM lesson WHERE departmentId='".$dId."' AND sectionId='".$sId."'";
    $result7 = mysql_query($sql7);
    
    while($row7 = mysql_fetch_array($result7)) {
        
        $data3 = array(
                        1 => array($row7['lessonTitle']),
                            array(' '),
                            array('Α/Α','Επώνυμο','Όνομα','Διδακτορικό','Master','Πανεπιστήμιο','ΤΕΙ','Επαγγελματική Εμπειρία', 'Εκπαιδευτική Εμπειρία', 'Ξένες Γλώσσες','Επιστημονική Δραστηριότητα', 'Ώρες Διαθεσιμότητας')); 

              
        $xls->addArray($data3);
    
        $lessonId = $row7['id'];
        
        
        $lTitle = $row7['lessonTitle'];
        
        $sql8 = "SELECT application_form.userId,jos_users.surname,jos_users.name,jos_users.mobileNumber,jos_users.email,lesson.id,lesson.lessonTitle, application_form.departmentId, application_form.sectionId 
        FROM `application_form`,jos_users,lesson 
        WHERE application_form.userId=jos_users.id AND application_form.lessonId=lesson.id 
        AND lesson.id='".$lessonId."' AND application_form.submited='1' 
        ORDER BY jos_users.surname,jos_users.name";
        
        $result8 = mysql_query($sql8); 
        
        $j = 0;
        while( $row8 = mysql_fetch_array($result8) ) {
            
            $surname = $row8['surname'];
            $name = $row8['name'];
            $mobileNumber = $row8['mobileNumber'];
            $email = $row8['email'];
            
            $data4 = array(
                            array($j+=1,mb_strtoupper($surname,'UTF-8'), mb_strtoupper($name,'UTF-8'))
                     );
            $xls->addArray($data4);
        }     
            $lTitle1 = substr($lTitle,0,20).$row7['id'];
        
            $xls->addNewSheet($lTitle1);
            $data3 = array();
            $data4 = array();
    }

  
$xls->closeGeneration();

?>