<?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.
 */

CLASS Getexcel {
    
    public static function importDataFromFile($file, $sheet_index = 0) {
        require_once('../Additional/PHPExcel.php');
        require_once('../Additional/PHPExcel/IOFactory.php');
        $error_mesg = '';
        /*
        if (!empty($file['error'])) {
            switch ($file['error']) {
                case UPLOAD_ERR_INI_SIZE:
                    $error_mesg .= "Uploaded file is too big (" . $file['size'] . " bytes).<br />";
                    break;
                case UPLOAD_ERR_FORM_SIZE:
                    $error_mesg .= "Uploaded file is too big (" . $file['size'] . " bytes).<br />";
                    break;
                case UPLOAD_ERR_PARTIAL:
                    $error_mesg .= "The uploaded file was only partially uploaded.<br />";
                    break;
                case UPLOAD_ERR_NO_FILE:
                    $error_mesg .= "No file was uploaded.<br />";
                    break;
                default:
                    $error_mesg .= "Error No : " . $file['error'] . ".<br />";
            } // switch
        }
         */
        if (empty($error_mesg)) {
            $file = $file['tmp_name'];
            $objReader = PHPExcel_IOFactory::createReaderForFile($file);
            $objReader->setReadDataOnly(true);
            $objPHPExcel = $objReader->load($file);
            $objWorksheet = $objPHPExcel->setActiveSheetIndex($sheet_index);
            $new = true;
            $arrData = array();
            $baris = 0;
            $kolom = 1;
            foreach ($objWorksheet->getRowIterator() as $row) {
                $kolom = 1;
                if ($new) {
                    $new = false;
                    continue;
                }
                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(false); // This loops all cells, even if it is not set. By default, only cells that are set will be iterated.
                foreach ($cellIterator as $cell) {
                    $arrData[$baris]['col'.$kolom] = $cell->getValue();
                    $kolom++;
                }
                $baris++;
            }
			return array('status' => true, 'data' => $arrData);
			//print_r($arrData);die;
        } else {
            return array('status' => false, 'error' => $error_mesg);
		}	

        //insert into temporary table
        /*$columns = '';
        $insertColumns = '';

        for ($i = 1; $i <= $kolom; $i++) {
            $columns .= ', `col' . $i . '` VARCHAR(255) NOT NULL ';
            $insertColumns .= (!empty($insertColumns) ? ', ' : '') . ' `col' . $i . '` ';
        }
        $tableName = 'importNew';
        $sqlCreateTable = 'CREATE TEMPORARY TABLE `' . $tableName . '` (
			      `id` int(10) NOT NULL AUTO_INCREMENT
			      ' . $columns . '
			      , PRIMARY KEY (`id`)
		      )';
        Database::build()->directlyQuery($sqlCreateTable);
        foreach ($arrData as $k => $v) {
            $_values = '';
            for ($i = 0; $i < $kolom; $i++) {
                $_values .= (!empty($_values) ? ', ' : '') . ' "' . mysql_real_escape_string($v[$i]) . '" ';
            }
            $sql = 'INSERT INTO `' . $tableName . '` (' . $insertColumns . ') VALUES (
		' . $_values . '
		)';
			Database::build()->directlyQuery($sql);
        }
        return array('status' => true, 'table' => $tableName);*/
    }
    
    public static function importExcelSource($file, $arrLabel, $sheet_index = 0) {
		$root = Config::load()->root();
        require_once($root.'/Additional/PHPExcel.php');
        require_once($root.'/Additional/PHPExcel/IOFactory.php');
        $error_mesg = '';
        //$file = $file['tmp_name'];
        $objReader = PHPExcel_IOFactory::createReaderForFile($file);
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($file);
        $objWorksheet = $objPHPExcel->setActiveSheetIndex($sheet_index);
        $new = true;
        $arrData = array();
        $baris = 0;
        $kolom = 0;
        foreach ($objWorksheet->getRowIterator() as $row) {
            $kolom = 0;
            if ($new) {
                $new = false;
                continue;
            }
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false); // This loops all cells, even if it is not set. By default, only cells that are set will be iterated.
            foreach ($cellIterator as $cell) {
                $arrData[$baris][$kolom] = $cell->getValue();
                $kolom++;
            }
            $status = false;
            for ($i = 0; $i <= $kolom; $i++) {
                if (!empty($arrData[$baris][$i])) {
                    $status = true;
                }
            }
            if ($status) {
                $baris++;
                $status = false;
            }
        }
		
		return array('status' => true, 'data' => $arrData);

        /*if (count($arrLabel) != $kolom) {
            return array('status' => false, 'error' => 'Total field mis match');
        } else {*/
			/*$arrConvData = array();
            unset($arrData[$baris]);
			print_r($arrData);die;
			foreach($arrData as $key => $val) {
                foreach($val as $key2 => $val2) {
                    $val2 = !empty($val2) ? $val2 : "";
					if(isset($arrLabel[$key2])) {
						$arrConvData[$key][$arrLabel[$key2]] = $val2;
					}
                }
            }
			return array('status' => true, 'data' => $arrConvData);*/
        //}
    }
	
	public static function importExcelWithColumn($file, $sheet_index = 0) {
        require_once('../Additional/PHPExcel.php');
        require_once('../Additional/PHPExcel/IOFactory.php');
        $error_mesg = '';
        $file = $file['tmp_name'];
        $objReader = PHPExcel_IOFactory::createReaderForFile($file);
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($file);
        $objWorksheet = $objPHPExcel->setActiveSheetIndex($sheet_index);
        $new = false;
        $arrData = array();
        $baris = 0;
        $kolom = 0;
        foreach ($objWorksheet->getRowIterator() as $row) {
            $kolom = 0;
            if ($baris == 0) {
                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(false); // This loops all cells, even if it is not set. By default, only cells that are set will be iterated.
                foreach ($cellIterator as $cell) {
                    $arrData[$baris][$kolom] = $cell->getValue();
                    $kolom++;
                }
                $status = false;
                for ($i = 0; $i <= $kolom; $i++) {
                    if (!empty($arrData[$baris][$i])) {
                        $status = true;
                    }
                }
                if ($status) {
                    $baris++;
                    $status = false;
                }
            }
        }
        
        return array('status' => true, 'data' => $arrData);
    }	
	
	public static function importExcelSourceWithLabel($file, $arrLabel, $sheet_index = 0) {
		$root = Config::load()->root();
        require_once($root.'/Additional/PHPExcel.php');
        require_once($root.'/Additional/PHPExcel/IOFactory.php');
        $error_mesg = '';
        //$file = $file['tmp_name'];
        $objReader = PHPExcel_IOFactory::createReaderForFile($file);
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($file);
        $objWorksheet = $objPHPExcel->setActiveSheetIndex($sheet_index);
        $new = true;
        $arrData = array();
        $baris = 0;
        $kolom = 0;
        foreach ($objWorksheet->getRowIterator() as $row) {
            $kolom = 0;
            if ($new) {
                $new = false;
                continue;
            }
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false); // This loops all cells, even if it is not set. By default, only cells that are set will be iterated.
            foreach ($cellIterator as $cell) {
                $arrData[$baris][$kolom] = $cell->getValue();
                $kolom++;
            }
            $status = false;
            for ($i = 0; $i <= $kolom; $i++) {
                if (!empty($arrData[$baris][$i])) {
                    $status = true;
                }
            }
            if ($status) {
                $baris++;
                $status = false;
            }
        }

        /*if (count($arrLabel) != $kolom) {
            return array('status' => false, 'error' => 'Total field mis match');
        } else {*/
			$arrConvData = array();
            unset($arrData[$baris]);
			foreach($arrData as $key => $val) {
                foreach($val as $key2 => $val2) {
                    $val2 = !empty($val2) ? $val2 : "";
					if(isset($arrLabel[$key2])) {
						$arrConvData[$key][$arrLabel[$key2]] = $val2;
					}
                }
            }
			return array('status' => true, 'data' => $arrConvData);
        //}
    }

}