<?php
require_once (PATH_CONTROLLERS . "Common.php") ;
require_once (PATH_MODELS . "admin/LocalLangTermTypeClass.php") ;
require_once (PATH_MODELS . "admin/LocalLangTermClass.php") ;
require_once (PATH_MODELS . "admin/LanguageTypeClass.php") ;
require_once (PATH_MODELS . "admin/LocalLangTermDefinedClass.php") ;
require_once (PATH_MODELS . "admin/LocalLangAppTypeClass.php") ;
require_once (PATH_CODE . "excel/PHPExcel.php") ;



class LocalTerm extends ControllerBase {
	private $type = "" ;
	private $APP_TYPE  = 1; // System terms
	
	
	function __construct() {
		$this->db = $_SESSION[SE_DB] ;
	}
	
	function __destruct() {
		unset($this->db) ;
	}
	
	public function processRequest($params) {
		$this->type = REQ_VIEW ;
		
		try {
			$this->db->open() ;
			if (isset($params) && count($params) > 0) {
				if (isset($params['type']))
					$this->type = $params['type'] ;
			}
			switch ($this->type) {
				case REQ_UPDATE:
					$this->updateRecord($params) ;
					break ;
				case REQ_REPORT:
					$this->getReport($params) ;
					break ;
				case REQ_EXPORT:
					$this->getExport($params) ;
					break ;
				case REQ_LIST:
					$this->getList($params) ;
					break ;
				case REQ_VIEW:
					$this->getView() ;
					break ;
				default:
					$this->sendJsonResponse(Status::Error,"invalid request.","",$this->type) ;
					break ;
			}
			$this->db->close() ;
			return true ;
		} catch (Exception $e) {
			$this->db->close() ;
			die ($e->getMessage()) ;
		}
	}

	private function updateRecord($params) {
		$infos 	= $this->getParam($params,'infos',"");
		$termType = $this->getParam($params,'termType', 0);
		$appType = $this->getParam($params,'appType', 0);
		
		if($infos == "" || $termType== 0) {
			$this->sendJsonResponse(Status::Error,"Dont receive any values to save.","",$this->type) ;
			return ;
		}
		$tblDefinedTerm = new LocalLangTermDefinedClass($this->db);
		
		// format of receiving data: termID - term key - termID_langId - termDefined
		foreach ($infos as $info) {
			$termId 	= $info[0];
			$termKey 	= $info[1];
			$infoSize = count($info);	
			if($infoSize < 3)
				continue;

			try {
				for ($i = 2; $i < $infoSize; $i = $i+2) {
					$datas = array() ;
					$datas[] = $this->db->fieldValue(LocalLangTermDefinedTable::C_TERM_ID,$termId) ;
					$datas[] = $this->db->fieldValue(LocalLangTermDefinedTable::C_TERM_TYPE,$termType) ;
					$datas[] = $this->db->fieldValue(LocalLangTermDefinedTable::C_APP_TYPE,$appType) ;
					$datas[] = $this->db->fieldValue(LocalLangTermDefinedTable::C_DESC,$termKey) ;
					
					$idTranslate 	= $info[$i];
					$ids			= explode("_", $idTranslate);
					$langId 		= $ids[1];
					
					$termTranslate	= trim($info[$i+1]);
					$datas[] = $this->db->fieldValue(LocalLangTermDefinedTable::C_TRANSLATION,$termTranslate) ;
					$datas[] = $this->db->fieldValue(LocalLangTermDefinedTable::C_TRANSLATION_LANG,$langId) ;
					
				
					if($tblDefinedTerm->isFound($termId, $langId, $appType) == false) {
						$tblDefinedTerm->addRecord($datas) ;
					} else { 
						$tblDefinedTerm->updateRecord($termId, $langId, $datas, $appType) ;
					}
				}
			} catch(Exception $ex) {
				Log::write('[LocalTerm]' . $ex->getMessage());
				$this->sendJsonResponse(Status::Error,"Sorry, there is a error in updating local term to the system.","",$this->type) ;
				return;
			}
		}
		$this->sendJsonResponse(Status::Ok,"","",$this->type);
	}

	private function getView() {
		ob_start() ;
// 		$languages = $this->getLanguage();
// 		$DEFAULT_TERM_TYPE = 1;
// 		$tableInfo = $this->getListInfo($this->APP_TYPE, $DEFAULT_TERM_TYPE, $languages);
		include (PATH_VIEWS . "admin/LocalTermView.php") ;
		echo Util::minifyHtml(ob_get_clean()) ;
	}
	
	private function getExport($params=null) {
		$appType = $this->getParam($params,'appType',-1);
		$termType = $this->getParam($params,'termType',-1);
		$langId   = $this->getParam($params,'language',-1);
		
		$filter = "" ;
		
	
		$languages = $this->getLanguageById($langId);
        
		//$arrayInfo = $this->getTermsInfo($appType, $termType, $languages);
        $arrayInfo = $this->getTermIds($appType,$termType, $langId);
		
	
        $expfile = "" ;
        $excel = new PHPExcel();
        $excel->getProperties()->setCreator("bttan")
        ->setLastModifiedBy("bttan")
        ->setTitle("Translated Term")
        ->setSubject("Translated Term")
        ->setDescription("Translated Terms")
        ->setKeywords("Language")
        ->setCategory("");

        $tblLanguageType = new LanguageTypeClass($this->db);
        $tblLocalLangTermType = new LocalLangTermTypeClass($this->db);
        $tblLocalLangAppType = new LocalLangAppTypeClass($this->db);
        
        $appRows = $tblLocalLangAppType->getTable();
        $termRows = $tblLocalLangTermType->getTable();
        
        Log::write('here');
        $i = 0;
        foreach($appRows as $appRow){
            foreach($termRows as $termRow){
                //Log::write($tblLocalLangTermType->getDescription($termType) . ' - ' . $tblLocalLangAppType->getDescription($appType));
                
                $colno = array();
                $colno[0] = "A";
                $colno[1] = "B" ;
                $colno[2] = "C" ;
                
                //($table,$code,$desc,$id)
                $excel->createSheet($i);
                
                $excel->setActiveSheetIndex($i);
                
                $excel->getActiveSheet()->setCellValue('A1',$_SESSION[SE_ORGNAME])
                ->setCellValue('A2', "Translated Terms")
                ->setCellValue('A4', 'Language : ' . $tblLanguageType->getDescription($langId))
                ->setCellValue('A5', 'Screen : ' .$termRow[LocalLangTermTypeTable::C_DESC])
                ->setCellValue('A6', 'App : ' .$appRow[LocalLangAppTypeTable::C_DESC]) ;

                $excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
                $excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
                $excel->getActiveSheet()->mergeCells('A1:'.$colno[count($colno)-1].'1');
                $excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                $excel->getActiveSheet()->getStyle('A2')->getFont()->setSize(16);
                $excel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
                $excel->getActiveSheet()->mergeCells('A2:'.$colno[count($colno)-1].'2');
                $excel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                $excel->getActiveSheet()->getStyle('A4')->getFont()->setBold(true);
                $excel->getActiveSheet()->mergeCells('A4:'.$colno[count($colno)-1].'4');
                $excel->getActiveSheet()->getStyle('A5')->getFont()->setBold(true);
                $excel->getActiveSheet()->mergeCells('A5:'.$colno[count($colno)-1].'5');
                $excel->getActiveSheet()->getStyle('A6')->getFont()->setBold(true);
                $excel->getActiveSheet()->mergeCells('A6:'.$colno[count($colno)-1].'6');

                $excel = $this->generateExcel($excel, $appRow[LocalLangAppTypeTable::C_ID], $termRow[LocalLangTermTypeTable::C_ID], $langId);
                
                $excel->setActiveSheetIndex($i);
                $excel->getActiveSheet()->setTitle(substr($termRow[LocalLangTermTypeTable::C_DESC],0,15) . ' - ' . substr($appRow[LocalLangAppTypeTable::C_DESC],0,3));
                
                $i++;
            }
        }
        
        $excel->setActiveSheetIndex(0);
        
        if ($expfile == "")
            $expfile = "translation_eClaims.xlsx" ;


        $objWriter = new PHPExcel_Writer_Excel2007($excel);
        $objWriter->save($expfile);


        header('Content-Description: File Transfer');
        header('Content-Type: application/force-download');
        header('Content-Disposition: attachement; filename="' . basename($expfile) . '"');
        header('Content-Transfer-Encoding: binary');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Length: ' . filesize($expfile));
        ob_clean();
        flush();
        readfile($expfile);
        /*
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8');
        header('Content-Disposition: attachment;filename=' . $expfile);
        header('Cache-Control: max-age=0');
        header('Content-Transfer-Encoding: binary');
        $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
        $objWriter->save('php://output');*/
		unset($rows) ;
	}
    
    private function generateExcel($excel, $appType, $termType, $langId){
        
        $arrayInfo = $this->getTermIds($appType,$termType, $langId);
        
        $rowidx = 8;
        $cellidx = "";
        $cellrange = "" ;
        
		$colno = array();
		$colno[0] = "A";
		$colno[1] = "B" ;
		$colno[2] = "C" ;

        $excel->getActiveSheet()
        ->setCellValue("A".$rowidx, "ID")
        ->setCellValue("B".$rowidx, "Term")
        ->setCellValue("C".$rowidx, "Translated Terms");


        for ($i=0 ;$i < count($colno);$i++) {
            $cellidx = $colno[$i].$rowidx ;
            $excel->getActiveSheet()->getStyle($cellidx)->getFont()->setBold(true);
            $excel->getActiveSheet()->getStyle($cellidx)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        }

        
        $rowidx++;
        foreach($arrayInfo as $row){
            $excel->getActiveSheet()
            ->setCellValue("A".$rowidx, $row[LocalLangTermTable::C_TERM_ID])
            ->setCellValue("B".$rowidx, $row[LocalLangTermTable::C_DESC])
            ->setCellValue("C".$rowidx, $row[LocalLangTermDefinedTable::C_TRANSLATION]);

            for ($i=2;$i<count($colno);$i++) {
                $excel->getActiveSheet()->getStyle($colno[$i].$rowidx)->getNumberFormat()->setFormatCode("#,###,##0.00");
            }
            $excel->getActiveSheet()->getStyle($colno[1].$rowidx)->getNumberFormat()->setFormatCode("#,###,##0");
            $rowidx++ ;
        }

        for ($i=0;$i < count($colno);$i++) {
            $excel->getActiveSheet()->getColumnDimension($colno[$i])->setAutoSize(true);
        }
        
        return $excel;
    }
	
	private function getReport($params=null) {
		require_once(PATH_LIB . 'ListPdf.php');
		
		$appType = $this->getParam($params,'appType',-1);
		$termType = $this->getParam($params,'termType',-1);
		$langId   = $this->getParam($params,'language',-1);
		
		if($termType == -1 || $langId == -1){
			$this->sendJsonResponse(Status::Error,"Can not find data filter to generate pdf.","",$this->type);
			return;
		}
		
		$languages = $this->getLanguageById($langId);
		$arrayInfo = $this->getTermsInfo($appType, $termType, $languages);
		$langIds = $this->getLangIds($languages);
		
		$i = 'items';
		$nr = 'newrow';
		$datas = array() ;
		
		$arrayTermId = $this->getTermIds($appType, $termType);
		foreach ($arrayTermId as $itemTermId) {
			$items = array() ;
			$items[$i][] = $this->createPdfItem($itemTermId,30,0,"R") ;
			$items[$i][] = $this->createPdfItem($arrayInfo["$itemTermId"]["key"],150) ;			
			
			for($j = 0; $j < count($languages); $j++) {
				$termTranslate = "";
				if(isset($langIds[$j])) {
					$termTranslate = $arrayInfo["$itemTermId"]["$langIds[$j]"];
					$items[$i][] = $this->createPdfItem($termTranslate,150) ;
				} else {
					$items[$i][] = $this->createPdfItem("",150) ;
				}
			}

			$items[$nr] = "1" ;
			$datas[] = $items ;
		}
		
		$cols = array() ;
		$cols[] = $this->createPdfItem("ID",30,0,"C","B");
		$cols[] = $this->createPdfItem("Term",150,"C","B") ;
		$cols[] = $this->createPdfItem($languages[0]["desc"],150,"C","B") ;

		$pdf = new ListPdf('L');
		$pdf->setCompanyName($_SESSION[SE_COYNAME]) ;
		$pdf->setReportTitle("Term Defined Listing") ;
		$pdf->setColumnsHeader($cols) ;
		$pdf->render($datas) ;
		$pdf->Output('TermDefined.pdf', 'I');
		unset($rows) ;
		unset($datas) ;
		unset($params) ;
		unset($items) ;
		unset($grp) ;
		unset($cols) ;
	}
	
	private function getData($appType,$termType) {
		$params = array() ;
		$sql = " select  t." . LocalLangTermTable::C_TERM_ID . ",t." . LocalLangTermTable::C_DESC
		. ", td." .LocalLangTermDefinedTable::C_TRANSLATION . ", td." .LocalLangTermDefinedTable::C_TRANSLATION_LANG
		. " from " . LocalLangTermTable::C_TABLE . " as t "
		. " left join " . LocalLangTermDefinedTable::C_TABLE . " as td on t." . LocalLangTermTable::C_TERM_ID . " = td." . LocalLangTermDefinedTable::C_TERM_ID
		. " left join " . LanguageTypeTable::C_TABLE . " as la  on la." . LanguageTypeTable::C_ID . " = td." . LocalLangTermDefinedTable::C_TRANSLATION_LANG
		. " where t." . LocalLangTermTable::C_APP_ID . "= ".$appType . " and t." . LocalLangTermTable::C_TERM_TYPE_ID . "= ". $termType
		. " order by t." .LocalLangTermTable::C_TERM_ID ;
		
		$rows = $this->db->getTable($sql,$params) ;
		return $rows;
	}

	
	private function getLangIds($languages) {
		$arrayInfo = array();
		$langSize = count($languages);
		for ($i = 0; $i < $langSize; $i++) {
			$arrayInfo[] = $languages[$i]["code"];
		}
		return $arrayInfo;
	}
	
	private function getList($params) {
		$appType = $this->getParam($params,'appType',-1);
		$termType = $this->getParam($params,'termType',-1);
		$langId   = $this->getParam($params,'language',-1);
		
        if($termType == -1 || $langId == -1 || $appType == -1) {
			$this->sendJsonResponse(Status::Error,"Can not find data to filter.","",$this->type);
			return;
		}
		
		//$languages = $this->getLanguageById($langId);
		$list = $this->getListInfo($appType, $termType, $langId);
		
		$this->sendJsonResponse(Status::Ok,"",$list,$this->type);
	}
	
	private function getTermIds($appType,$termType, $langId) {
		$arrReturn = array();
		$params = array() ;
        
		$sql = " select  T." . LocalLangTermTable::C_TERM_ID . ",T." . LocalLangTermTable::C_DESC . ", COALESCE(D." . LocalLangTermDefinedTable::C_TRANSLATION . ", '') AS " . LocalLangTermDefinedTable::C_TRANSLATION . " "
            . " from " . LocalLangTermTable::C_TABLE . " as T "
            . " left outer join " . LocalLangTermDefinedTable::C_TABLE . " D " . 
                " ON (T." . LocalLangTermTable::C_APP_ID . " = D." . LocalLangTermDefinedTable::C_APP_TYPE . " " . 
                " and T." . LocalLangTermTable::C_TERM_TYPE_ID . " = D." . LocalLangTermDefinedTable::C_TERM_TYPE . " " . 
                " and T." . LocalLangTermTable::C_TERM_ID . " = D." . LocalLangTermDefinedTable::C_TERM_ID . " " . 
                " AND D." . LocalLangTermDefinedTable::C_TRANSLATION_LANG . " = " . $langId . ") "
		. " where T." . LocalLangTermTable::C_APP_ID . "= ".$appType . " and T." . LocalLangTermTable::C_TERM_TYPE_ID . "= ". $termType
		. " order by T." .LocalLangTermTable::C_TERM_ID ;
        
        //Log::write($sql);
		$rows = $this->db->getTable($sql,$params) ;
		if(count($rows) == 0) {
			return $arrReturn;
		}
        
		return $rows;
	}
	
	private function getTermsInfo($appType,$termType,$languages) {
		$rows = $this->getData($appType, $termType);
		
		if(count($rows) == 0) {
			return array();
		}
		
		$langSize 		= count($languages);
		$arrayInfo 		= array();
		
		foreach ($rows as $row) {
			$termId 	= $row[LocalLangTermTable::C_TERM_ID];
				
			if(!isset($arrayInfo["$termId"])) {
				for ($i = 0; $i < $langSize; $i++){
					$code = $languages[$i]["code"];
					$arrayInfo["$termId"]["$code"] = "";
				}
			}
		
			$termDesc 	= $row[LocalLangTermTable::C_DESC] ;
			$termDefine = $row[LocalLangTermDefinedTable::C_TRANSLATION] ;
			$langId		= $row[LocalLangTermDefinedTable::C_TRANSLATION_LANG] ;
				
			$arrayInfo["$termId"]["id"] = $termId;
			$arrayInfo["$termId"]["key"] = $termDesc;
			$arrayInfo["$termId"]["$langId"] = $termDefine;
		}
		
		return $arrayInfo;
	}
	
	private function getListInfo($appType,$termType,$langid) {
		$arrayTermId = $this->getTermIds($appType, $termType, $langid);
		
		$list    = "";
		if(count($arrayTermId) == 0) {
			$list .= "<tr><td colspan='3'>No Record Found.</td></tr>" ;
			return $list;
		}
		
		foreach ($arrayTermId as $itemTermId) {
			$list .= "<tr class='term-rows'>" ;
			$list .= "<td style='width:4%;height:1px' class='termid'>" . $itemTermId[LocalLangTermTable::C_TERM_ID] . "</td>" ;
			$list .= "<td style='width: 24%;' class='termKey'>" . $itemTermId[LocalLangTermTable::C_DESC] . "</td>" ;
            $inputId = $itemTermId[LocalLangTermTable::C_TERM_ID]."_".$langid;
			$list .= "<td style='width: 24%;' class='inputValue'> <input class='text-field' type='text' maxlength='250' size='40' id='".$inputId."' value='".$itemTermId[LocalLangTermDefinedTable::C_TRANSLATION]."' /></td>" ;
			
            /*
			for($i = 0; $i < count($languages); $i++) {
				$termTranslate = "";
				if(isset($langIds[$i])) {
					$termTranslate = $arrayInfo["$itemTermId"]["$langIds[$i]"];
					$inputId = $itemTermId."_".$langIds[$i];
					$list .= "<td style='width: 24%;' class='inputValue'> <input class='text-field' type='text' maxlength='250' size='40' id='".$inputId."' value='".$termTranslate."' /></td>" ;
				} else {
					$list .= "<td style='width: 24%;' class='noInputValue'></td>" ;
				}
			}*/
	
			$list .= "</tr>" ;
		}
		return  $list;
	}
	
	private function getTermType() {
		$filter = array();
		$vls = $this->getValueList(LocalLangTermTypeTable::C_TABLE, LocalLangTermTypeTable::C_ID, LocalLangTermTypeTable::C_DESC,array('code'=>'0','desc'=>'Select Term Type'),$filter) ;
		return Util::createOptionValue($vls) ;
	}
	
	private function getAppType() {
		$filter = array();
		$vls = $this->getValueList(LocalLangAppTypeTable::C_TABLE, LocalLangAppTypeTable::C_ID, LocalLangAppTypeTable::C_DESC,array('code'=>'0','desc'=>'Select App Type'),$filter) ;
		return Util::createOptionValue($vls) ;
	}
	
	private function getLanguage() {
		$filter = array();
		$vls = $this->getValueList(LanguageTypeTable::C_TABLE, LanguageTypeTable::C_ID, LanguageTypeTable::C_DISPLAY,null,$filter) ;
		return $vls;
	}
	
	private function getLanguageById($langId) {
		$filter 	= array();
		$filter[] 	= $this->db->fieldValue(LanguageTypeTable::C_ID,$langId) ;
		$vls = $this->getValueList(LanguageTypeTable::C_TABLE, LanguageTypeTable::C_ID, LanguageTypeTable::C_DISPLAY,null,$filter) ;
		return $vls;
	}
	
	
	private function getLanOptions() {
		$filter = array();
		$vls = $this->getValueList(LanguageTypeTable::C_TABLE, LanguageTypeTable::C_ID, LanguageTypeTable::C_DISPLAY,array('code'=>'0','desc'=>'Select Language'),$filter) ;
		return Util::createOptionValue($vls) ;
	}
}
?>