<?php
namespace App\Controllers\Admin;
use App\Views;
use App\Models\CommonModel;
use App\Libraries\MyEncrypt;
use App\Libraries\PHPMailer;
use App\Libraries\PDF;

class Backup extends BaseController {

	/**
	* 
	* Start 
	* Create Date: 04 Oct, 2016
	* This controller to manage for import and export DB.
	* Created saravanan.
	* @see http://zhyip.com/
	* Last Updated: 04 Oct, 2016
	* End
	* 
	*/

	public function __construct() {
		$this->session = session();
		if($this->session->get('admin_login')) {
			
			$this->isSubadmin();

		}  else {
	    	return redirect()->to('admin/login');
	    }

	}

	private function isSubadmin() {
		$this->session = session();
        $this->router = service('router');
		if($this->session->get('user_type') == 'subadmin') {
			$controller = $this->router->controllerName();
			$pages = json_decode($this->session->get('permission'));
		
			if(!in_array(ucfirst($controller), $pages)) {
				return redirect()->to('admin');
			}
		}
	}


	public function index()
	{
		$this->db = db_connect();
		$myencrypt = new MyEncrypt(true);
		$this->common_model = model('CommonModel');
		$session = \Config\Services::session();
		$validation = \Config\Services::validation();
		$uri = service('uri');
		if(!$session->get('admin_login')) {
			return redirect()->to('admin/login');
		}
		$this->data['breadcrumbs'] = array();
		$this->data['breadcrumbs'][] = array(
			'text' => lang('language_lang.text_home'),
			'href' => base_url('admin')
		);
		$this->data['breadcrumbs'][] = array(
			'text' => lang('language_lang.support_lbl')
		);

		$this->data['module_name'] = 'settings';
		
    	if($this->request->getPost()) {
    		
    		$columns = $this->request->getPost('exportedfields');
    		
			if(sizeof($columns)>1) {
				$select_column = '';
				foreach($columns as $column){
					$select_column .= "`".$column."`, ";
				}
				$select_column =trim($select_column,", ");
				
			} else {
				$select_column='*';
			}
    		
    		$this->exportdoc($this->request->getPost('table'), $select_column, $this->request->getPost('format'));
    		
    	} else {
    		$tables = $this->db->listTables();
			$this->data['tables'] = $tables;
    		echo view('admin/backup', $this->data);
    	}
 		
	}

	public function archive($tablename='')
	{
		
    	if($this->request->getPost()) {
    		
    		$columns = $this->request->getPost('exportedfields');
    		
			if(sizeof($columns)>1) {
				$select_column = '';
				foreach($columns as $column){
					$select_column .= "`".$column."`, ";
				}
				$select_column =trim($select_column,", ");
				
			} else {
				$select_column='*';
			}
    		
    		$this->exportdoc($this->request->getPost('table'), $select_column, $this->request->getPost('format'));
    		
    	} else {
    		
    		if($tablename!='')
    		{
    			$tables = $tablename;
    		}
    		else
    		{
    			$tables = $this->db->list_tables();
    		}
    			    		
			$this->data['tables'] = $tables;
    		echo view('export', $this->data);
    	}
 		
	}

	public function export($auto='') {
		echo view('admin/db_backup');
		// $db = db_connect();

				// $this->dbutil = (new \CodeIgniter\Database\Database())->loadUtils($db);

		// Load the DB utility class
		// $this->load->dbutil();

		// Backup your entire database and assign it to a variable
		// $backup =& $this->dbutil->backup(); 

		// // Load the file helper and write the file to your server
		// $backup_file  = time().'mybackup.zip';
		
		// helper('file');

		// // // Load the download helper and send the file to your desktop
		
		// // force_download($backup_file, $backup);


		// $prefs = array(
  //           'tables'      => array(),  // Array of tables to backup.
  //           'ignore'      => array(),           // List of tables to omit from the backup
  //           'format'      => 'gzip',             // gzip, zip, txt
  //           'filename'    => $backup_file,    // File name - NEEDED ONLY WITH ZIP FILES
  //           'add_drop'    => TRUE,              // Whether to add DROP TABLE statements to backup file
  //           'add_insert'  => TRUE,              // Whether to add INSERT data to backup file
  //           'newline'     => "\n"               // Newline character used in backup file
  //       );

		// $backup = $this->dbutil->backup($prefs);
		// $path = './database/'.$backup_file;
		
		// if($auto=='') {
		// 	helper('download_helper');
		// 	force_download($prefs['filename'], $backup);
		// } else {
		// 	$status = write_file($path, $backup);
		// }
	}

	public function import() 
	{
		$this->session = session();

		$this->db =db_connect();
		
		$field_name = "importdb";
		if(isset($_FILES[$field_name])) {

			$allowed =  array('sql');
			$filename = $_FILES[$field_name]['name'];
			$ext = pathinfo($filename, PATHINFO_EXTENSION);

			if(!in_array($ext,$allowed) ) {
			    $this->session->setFlashData('error_message', 'Failed! File type Invalid.');
				return redirect()->to('admin/backup');
			} else {
				$MyFile = file_get_contents($_FILES[$field_name]['tmp_name']);

				$templine = '';
				// Read in entire file
				$lines = file($_FILES[$field_name]['tmp_name']);

				if ($lines) {
      				$sql = '';
      				$this->db->query("SET CHARACTER SET utf8");
				    $this->db->query("SET @@session.sql_mode = 'MYSQL40'");

					foreach($lines as $line) {
				        if ($line && (substr($line, 0, 2) != '--') && (substr($line, 0, 1) != '#')) {
				        	$sql .= $line;

				          	if (preg_match('/;\s*$/', $line)) {

				            	$import_status = $this->db->query($sql);

				            	$sql = '';
				          	}
				        }
				    }
				}

				/*
				// Loop through each line
				foreach ($lines as $line)
				{
				    // Skip it if it's a comment
				    if (substr($line, 0, 2) == '--' || $line == '')
				        continue;

				    // Add this line to the current segment
				    $templine .= $line;
				    // If it has a semicolon at the end, it's the end of the query
				    if (substr(trim($line), -1, 1) == ';')
				    {
				        // Perform the query
				    	$import_status = $this->db->query($templine) or  $this->db->error();
				        // Reset temp variable to empty
				        $templine = '';
				    }
				}
				*/

				if($import_status) {
					$this->session->setFlashData('success_message', 'Success! Database Restored');
					return redirect()->to('admin/backup');
				} else {
					$this->session->setFlashData('error_message', 'Failed! Database not restored check file type.');
					return redirect()->to('admin/backup');
				}
			}
		} else {
			echo view('admin/backup');
		}
		
	}

	public function getTables(){
		// $this->
		$tables = $this->db->list_tables();
		$this->data['tables'] = "epin";
		echo view('export', $this->data['tables']);
	}

	public function getColumns($tableName){
		$tableName = "arm_".$tableName;
		$fields = $this->db->list_fields($tableName);
		// $this->data['field'] = $tables;

		header('Content-Type: application/x-json; charset=utf-8');
 		echo(json_encode($fields));

		//echo view('export', $this->data['field']);
	}

	function exportdoc($tableName, $select_column, $format)
	{
		switch ($format) {
			case 'csv':
				$this->load->dbutil();
		        $delimiter = ",";
		        $newline = "\r\n";
		        $filename = $tableName.'.csv';
		        $query = "SELECT ".$select_column." FROM arm_".$tableName;
		        $result = $this->db->query($query);
		        
		        $data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
		        force_download($filename, $data);
				break;
			case 'xml':
				$this->load->dbutil();
		        $config = array (
					'root'    => 'root',
					'element' => 'element', 
					'newline' => "\n", 
					'tab'    => "\t"
		        );
		        $filename = $tableName.'.xml';
		        $query = "SELECT ".$select_column." FROM arm_".$tableName;
		        $result = $this->db->query($query);
		        $data = $this->dbutil->xml_from_result($result, $config);
        		force_download($filename, $data);
				break;
			case 'pdf':
				$this->load->dbutil();
		        $delimiter = ",";
		        $newline = "\r\n";
		        $filename = $tableName.'.pdf';
		        $query = "SELECT ".$select_column." FROM arm_".$tableName; 
		        $result = $this->db->query($query);
		        $data1 = $this->dbutil->csv_from_result($result, $delimiter, $newline);
				$data = $this->csvToTable($data1);
				$this->pdfexport($data,$tableName);
		        
				break;
			
		}
	}

	function CSV()
	{
        $this->load->dbutil();
        $delimiter = ",";
        $newline = "\r\n";
        $filename = "customers.csv";
        $query = "SELECT * FROM arm_members";
        $result = $this->db->query($query);
        $data = $this->dbutil->csv_from_result($result, $delimiter, $newline);
        force_download($filename, $data);
	}

	function XML()
	{
        $this->load->dbutil();
        $config = array (
			'root'    => 'root',
			'element' => 'element', 
			'newline' => "\n", 
			'tab'    => "\t"
        );
        $filename = "customers.xml";
        $query = "SELECT * FROM arm_members";
        $result = $this->db->query($query);
        $data = $this->dbutil->xml_from_result($result, $config);
        force_download($filename, $data);
	}

	function pdfexport($data,$tableName) {

		// $names = str_replace('arm_', '', $tableName);
		$this->load->library("Pdf");

		// create new PDF document
	    $pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, 'A0', true, 'UTF-8', false);    
	 
	    // set document information
	    $pdf->SetCreator(PDF_CREATOR);
	    $pdf->SetAuthor('ARM Infotech');
	    $pdf->SetTitle($tableName);
	    $pdf->SetSubject($tableName.'List');
	    // $pdf->SetKeywords('TCPDF, PDF, example, test, guide');   
	 
	    // set default header data
	    $pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, $tableName, 'testing', array(0,64,255), array(0,64,128));
	    $pdf->setFooterData(array(0,64,0), array(0,64,128)); 
	 
	    // set header and footer fonts
	    $pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
	    $pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));  
	 
	    // set default monospaced font
	    $pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED); 
	 
	    // set margins
	    $pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
	    $pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
	    $pdf->SetFooterMargin(PDF_MARGIN_FOOTER);    
	 
	    // set auto page breaks
	    $pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM); 
	 
	    // set image scale factor
	    $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
	     
	 
	    // set default font subsetting mode
	    $pdf->setFontSubsetting(true);   
	 
	    // Set font
	    // dejavusans is a UTF-8 Unicode font, if you only need to
	    // print standard ASCII chars, you can use core fonts like
	    // helvetica or times to reduce file size.
	    $pdf->SetFont('dejavusans', '', 14, '', true);   
	 
	    // Add a page
	    // This method has several options, check the source code documentation for more information.
	    $pdf->AddPage(); 
	 
	    // set text shadow effect
	    $pdf->setTextShadow(array('enabled'=>true, 'depth_w'=>0.2, 'depth_h'=>0.2, 'color'=>array(196,196,196), 'opacity'=>1, 'blend_mode'=>'Normal'));
	    
	    //$this->data['customers'] = $this->common_model->GetCustomers();
        // print_r($this->data['customers']);

        
       
	   	// Print text using writeHTMLCell()
    	$pdf->writeHTMLCell(0, 0, '', '', $data, 0, 1, 0, true, '', true);   

    	// download file
    	$pdf->Output($tableName.'.pdf', 'D');

    	//view pdf file
    	//$pdf->Output('customers.pdf', 'I');
    	
		
	}

	function csvToTable($csv_content){
	  $table = "<table>";
	  $rows = str_getcsv($csv_content, "\n");
	  foreach($rows as &$row){
	    $table .= "<tr>";
	    $cells = str_getcsv($row);
	    foreach($cells as &$cell){
	      $table .= "<td>$cell</td>";
	    }
	    $table .= "</tr>";
	  }
	  $table .= "</table>";
	  return $table;
	}

	public function resetdb() {

		$this->export($auto='1');

		$reset_status = $this->db->query("TRUNCATE `deposit`");
		$reset_status = $this->db->query("TRUNCATE `history`");
		$reset_status = $this->db->query("TRUNCATE `message_controls`");
		$reset_status = $this->db->query("TRUNCATE `news_controls`");
		$reset_status = $this->db->query("TRUNCATE `tickets`");
		$reset_status = $this->db->query("TRUNCATE `ticket_conversation`");
		$reset_status = $this->db->query("TRUNCATE `trackip`");
		$reset_status = $this->db->query("TRUNCATE `uusers_online`");
		
		$reset_status = $this->db->query("DELETE FROM `uusers` WHERE `uusersid` > 1 ");
		$reset_status = $this->db->query("ALTER TABLE `uusers` AUTO_INCREMENT = 2 ");
		$reset_status = $this->db->query("DELETE FROM `uusers_matrix` WHERE `id` > 1 ");
		$reset_status = $this->db->query("ALTER TABLE `uusers_matrix` AUTO_INCREMENT = 2 ");

		$reset_status = $this->db->query("DELETE FROM `uusers_payment` WHERE `uusersid` > 1 ");
		$reset_status = $this->db->query("ALTER TABLE `uusers_payment` AUTO_INCREMENT = 2");

		$reset_status = $this->db->query("DELETE FROM `uusers_security` WHERE `id` > 1 ");
		$reset_status = $this->db->query("ALTER TABLE `uusers_security` AUTO_INCREMENT = 2 ");

		$reset_status = $this->db->query("UPDATE `uusers_matrix` SET `user_count` = '0' WHERE `uusers_matrix`.`id` = 1 ");
		if($reset_status) {
			return redirect()->to('admin');
		} else {
			echo "Reset Database is failure";
		}

	}

}
