<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

include 'connection.php';

include '../Classes/PHPExcel/IOFactory.php';
include '../Classes/PHPExcel.php';

//ini_set('display_errors', 1);
//ini_set('display_startup_errors', 1);
//error_reporting(E_ALL);

$name = $_FILES['upload-excel']['name'];
$tmpName = $_FILES['upload-excel']['tmp_name'];
$newfilename = '';

$error_count = 0;
$error_table = '<table class="instalment-table-black" border="1" cellpadding="15"><thead><th>Row no</th><th>Error message <span id="close_error_table" class="btn btn-sm btn-danger">X</span> </th></thead><tbody>';

$data_result = array();
$data_result['status'] = 300;

require_once('functions.test_voting.php');

$test_voting_obj = new Test_voting();

//get test voting activated languages id's 
$test_voting_languages_ids_arr = $test_voting_obj->getTestVotingAllowedLanguagesIds();

require_once('functions.self_assessment_test_sentences.php');
$sats_obj = new Self_assessment_test_sentences();

//Get maximum no. of sentences to upload in db table value
$max_no_sentneces_upload = $sats_obj->getMaxNoOfSelfAssessmentTestSentencesUploadValue();

//Get maximum no. of sentences to upload in excel file value
$max_no_sentneces_upload_excel = $sats_obj->getMaxNoOfSelfAssessmentTestSentencesUploadExcel();

$max_no_sentneces_upload_excel_incl_heading = $max_no_sentneces_upload_excel + 1;

$min_sentneces_in_excel = 2;

//Get require minimum words count in sentence to upload from excel 
$min_require_words_count_in_sentence = $sats_obj->getRequireMinWordsCountInSentence();

if( in_array($_SESSION['tm_language_id'], $test_voting_languages_ids_arr ) ) { 	

	if (!empty($name)) { // upload file	
	
		$temp = explode(".", $name);
		$newfilename = $temp[0] . rand(1, 999999) . '.' . end($temp);
		
		if (move_uploaded_file($tmpName, "../temp/" . $newfilename)) {		
			
			$uploaded_by_user_id = $_SESSION['tm_id'];
			
			$language_id = trim($_SESSION['tm_language_id']);	
			
			$objPHPExcel = PHPExcel_IOFactory::load("../temp/" . $newfilename);

			$worksheet = $objPHPExcel->getSheet(0);
	
			$worksheetTitle = $worksheet->getTitle();
			$highestRow = $worksheet->getHighestRow(); // e.g. 10
			$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
			$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
			$nrColumns = ord($highestColumn) - 64;					
	
			//sentences should be 101 including heading
			if( $highestRow >= $min_sentneces_in_excel ) {				
				
				//get total no. of self assessment sentences uploaded count by user
				$self_assessment_test_sentences_upload_count = $sats_obj->getTotalSelfAssessmentTestSentnecesUploadedCount( $uploaded_by_user_id );
				
				//check user's no. of self assessment sentences are less than max no. of sentences to upload
				if( $self_assessment_test_sentences_upload_count < $max_no_sentneces_upload ) {													
						
						for ($row2 = 2; $row2 <= $highestRow; $row2++) {
						
							$data2 = array();
							
							for ($col2 = 0; $col2 < $highestColumnIndex; ++$col2) {
								$cell2 = $worksheet->getCellByColumnAndRow($col2, $row2);
								$val2 = $cell2->getValue();
								$dataType2 = PHPExcel_Cell_DataType::dataTypeForValue($val2);
								$data2[] = trim($val2);
							}							
							
							$original_message2 = $data2[0];
							$translated_message2 = $data2[1];	

							$error = 0;
							$error_message = '';							

							if (sizeof($data2) >= 1) {

								//start validation
								
								//check if empty original message
								if ($original_message2 == '') {									
									$error = 1;
									$error_message .= 'Empty original message. ';																	
								} else {									
									//get duplicate self assessment test sentences by language and original message
									$self_assessment_test_sentences_count = $sats_obj->getSelfAssessmentTestSentencesCountByLanguageIdAndOriginalSentence( $language_id, $original_message2 );									
									if( $self_assessment_test_sentences_count > 0 ) {										
										$error = 1;
										$error_message .= 'Duplicate sentence. ';
																				
									}									
								}
								
								//check if empty translated message
								if ($translated_message2 == '') {								
									$error = 1;
									$error_message .= 'Empty translated message. ';			
								}
								
								//check min words require validation 
								if( str_word_count($original_message2) < $min_require_words_count_in_sentence ) {
									$error = 1;
									$error_message .= 'Requires minimum ' . $min_require_words_count_in_sentence . ' words in sentence';			
								} 
								
								
								if ($error == 0) {
									//don't add error
								}

								if ($error == 1) {
									$error_table .= '<tr><td>' . $row2 . '</td><td>' . $error_message . '</td></tr>';
									$error_count++;
								}
								
								//end validation
									
								//check total sentences uploaded by user
								$self_assessment_test_sentences_upload_count2 = $sats_obj->getTotalSelfAssessmentTestSentnecesUploadedCount( $uploaded_by_user_id );
								
								//check duplicate self assessment test sentences by language and original message
								$self_assessment_test_sentences_count2 = $sats_obj->getSelfAssessmentTestSentencesCountByLanguageIdAndOriginalSentence( $language_id, $original_message2 );
								
								if( $original_message2 !='' && $translated_message2 != '' && $self_assessment_test_sentences_count2 == 0 && str_word_count($original_message2) >= $min_require_words_count_in_sentence ) { 
								
									if( $self_assessment_test_sentences_upload_count2 < $max_no_sentneces_upload ) {
										
										//insert in regular self_assessment_test_sentences db table
										//insert uploaded sentences into the self_assessment_test_sentences db table and get sentence id
										$sentence_id = $sats_obj->insertSelfAssessmentTestSentenceAndGetSentenceId( $language_id, $original_message2, $translated_message2, $uploaded_by_user_id );
										
										//insert first suggestion into the self_assessment_test_sentence_suggestion db table
										$sats_obj->insertFirstSelfAssessmentTestSentenceSuggestion( $sentence_id, $uploaded_by_user_id, $translated_message2 );
									
									} else {
										
										//insert extras sentences in self_assessment_test_sentences_extras db table
										$sats_obj->insertSelfAssessmentTestSentenceExtras( $language_id, $original_message2, $translated_message2, $uploaded_by_user_id );	
										
									}								
								
								}
								
								//check total sentences uploaded by user
								$total_sentences_upload_count = $sats_obj->getTotalSelfAssessmentTestSentnecesUploadedCount( $uploaded_by_user_id );
								
								if( $total_sentences_upload_count >= $max_no_sentneces_upload ) {
									
									$data_result['status'] = 200;
									
								} else {
									
									$remaining_sentences2 = $max_no_sentneces_upload_excel - $total_sentences_upload_count;
									
									$data_result['msg'] = $total_sentences_upload_count . ' sentences uploaded! ' . 'Please upload ' . $remaining_sentences2 . ' unique sentences to proceed. '; ;
									
								}				
								
								
							} 
							
						} // column for loop ends here						
				
					
				} else {					
					
					$data_result['msg'] = 'You have exceeded max no. of sentences upload limit. ';				
					
				}			
				
				
			} else {
				
				$remaining_sentences = $max_no_sentneces_upload_excel_incl_heading - $highestRow;
				$data_result['msg'] = 'Please upload ' . $remaining_sentences . ' unique sentences to proceed. ';
				
			}	
			
			$error_table .= '</tbody></table>';
			
			$data_result['error_count'] = $error_count;
			
			if( $error_count >= 1 ) {
				$data_result['error_table'] = $error_table;
			}
			
			unlink("../temp/" . $newfilename);
		}
	} 
}

echo json_encode($data_result);