<?php

namespace App\Controller;

use App\Controller\AppController;
use ZipArchive;

/**
 * Imports Controller
 *
 * @property \App\Model\Table\ImportsTable $Imports
 */
class ImportsController extends AppController {

    public function ftp() {
        $ftp_server = "10.190.22.35";
        $ftp_port = "2587";
        $ftp_user = "LfStaD-Log";
        $ftp_pass = "135!Log!086";
        $local_file = '/test/test.zip';
        $zip_pass = '!AP-AS-2018!';
        $local_folder = '/test';

        $conn_id = ftp_connect($ftp_server, $ftp_port) or die("Couldn't connect to $ftp_server");

        if (@ftp_login($conn_id, $ftp_user, $ftp_pass)) {
            echo "Angemeldet als $ftp_user@$ftp_server\n";
        } else {
            echo "Anmeldung als $ftp_user nicht möglich\n";
        }

        ftp_chdir($conn_id, 'ap-csv');
        $contents = ftp_nlist($conn_id, ".");
        //debug($contents);
        $abschluss = array_search('Abschlusspruefung_2018.zip',$contents);
        //debug($abschluss);
        //die;
        ftp_get($conn_id, $local_file, $contents[$abschluss], FTP_BINARY);

        $zip = new ZipArchive();
        $zip_status = $zip->open($local_file);
        
        if ($zip_status === true) {
            if ($zip->setPassword($zip_pass)) {
                if (!$zip->extractTo('/test/results/'))
                    echo "Extraction failed (wrong password?)";
            }
            //debug($zip->statIndex(0));
            $file = $zip->statIndex(0)['name'];
            //debug($file);
            //debug($file);
            $zip->close();
        }
        else {
            die("Failed opening archive: " . @$zip->getStatusString() . " (code: " . $zip_status . ")");
        }
        rename('/test/results/'.$file,'/var/www/html/asd-control/webroot/files/ap/results.csv');
        exec('/usr/bin/smbclient -U \'BAYKM/km-tri-wil%HiegTzs!182\' //KM-Svr-Daten/Projektshare -c \'cd /14_Materialien/Austausch/WiTr/ap; put /var/www/html/asd-control/webroot/files/ap/results.csv results-ap.csv\'');
        //die;
        //debug($contents);die;

        /* $this->loadModel('Imports');
        $imports = $this->Imports->find('all');

         foreach ($imports as $import) {
            //debug($import);
            $file = glob($local_folder . '/results/' . explode('.', $import->filename)['0'] . '_*');
            copy($file['0'], '/var/www/html/asd-control/webroot/files/' . $import->filename);
            $parts = explode('/', $file['0']);
            $filename = end($parts);
            rename($file['0'], $local_folder . '/old/' . $filename);
        }
        $this->loadModel('Examinations');
        $examinations = $this->Examinations->find('all', [
            'contain' => ['Imports']]);
        foreach ($examinations as $examination) {
            //debug($examination);
            $job['examination_id'] = $examination->id;
            $i = 1;
            foreach ($examination->imports as $import) {
                $job['files']['file-' . $i]['filename'] = $import->filename;
                $i++;
            }
            //debug($job);
            $this->csv2db($job);
        } */
        ftp_close($conn_id);
        return $this->redirect(['controller' => 'Emails', 'action' => 'import']);
    }

 

    function addModels($importtype) {
        //debug($importtype->mapping);
        //debug(json_decode($importtype->mapping));
        foreach (json_decode($importtype->mapping) as $map_key => $map_value) {
            if (substr($map_key, -3) == '_id') {
                $map_key_array = explode('_', $map_key);
                //debug(ucfirst($map_key_array[0] . 's'));
                //debug($map_key_array[0]);
                $this->loadModel(ucfirst($map_key_array[0] . 's'));
            }
        }
    }

    function csv2db($import) {
        $options = [
            'length' => 0,
            'delimiter' => ';',
            'enclosure' => ' ',
            'escape' => '\\',
            'headers' => false,
            'text' => false,
            'excel_bom' => false,
        ];
        $this->loadModel('Configs');
        $config = $this->Configs->find('all', array('conditions' => array('title' => 'path')))->first();
        $path = $config->value;
        $examination_id = $import['examination_id'];
        //debug($examination_id);
        foreach ($import['files'] as $key => $file) {
            //$file_name = (object) [];
            $import_id = explode('-', $key)[1];
            //debug($file);
            $importtype = $this->Imports->find('all', array('conditions' => array('filename' => $file['filename'])))->first();
            //debug($importtype);
            //if (!empty($file['filename'])) {
            //    $file_name->filename = $file['filename'];
            //    $importtype = $this->Imports->patchEntity($importtype, (array) $file_name);
            //    $this->Imports->save($importtype);

            //    $filepath = $path . DS . $file['filename'];
            //} else {
            //    if ($file['csv']['error'] == 0) {
            //        if (!empty($file['csv']['name'])) {
            //            $importtype->filename = $file['csv']['name'];
            //            $this->Imports->save($importtype);
            //        }
            //        $filepath = $import[$key]['csv']['tmp_name'];
            //    }
            //}
            $filepath = $path . DS . $file['filename'];
            //debug($file);
            //debug($filepath);
            //die;
            if (!empty($filepath)) {
                $this->loadModel($importtype->tablename);

                if ($importtype->clear) {
                    $dtable = $importtype->tablename;
                    $conn = $this->$dtable->connection();
                    $conn->query('DELETE FROM ' . strtolower($dtable) . ' WHERE examination_id = ' . $examination_id);
                }
                $this->addModels($importtype);  /* Zusaetzliche Models laden */

                $table = $importtype->tablename;
                $columns = $this->$table->schema()->columns();
                $schema = (object) [];
                foreach ($columns as $column) {
                    $schema->$column = $this->$table->schema()->columnType($column);
                }
                //debug($schema);die;
                $options['delimiter'] = $importtype['field_separator'];
                if (!empty($importtype['enclosure'])) {
                    $options['enclosure'] = $importtype['enclosure'];
                }
                $filepath = glob($filepath)[0];
                $handle = fopen($filepath, "r");
                $i = 1;

                while ($row = fgetcsv($handle, $options['length'], $options['delimiter'])) {
                    $data = (object) [];
                    if ($i > $importtype->skip) {
                        foreach (json_decode($importtype->mapping) as $key => $value) {
                            if (substr($key, -3) == '_id') {
                                $key_array = explode('_', $key);
                                $ftable = ucfirst($key_array[0] . 's');
                                $field = $key_array[0] . '_id';
                                $data->$field = $this->$ftable->find('all', array('conditions' => array($key_array[1] => $row[$value]), 'fields' => array('id')))->first()->id;
                            } else {
                                if ($schema->$key == 'datetime') {
                                    $date = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', $row[$value])));
                                    $data->$key = $date;
                                } elseif ($schema->$key == 'integer') {
                                    $data->$key = str_replace(".", "", $row[$value]);
                                } else {
                                    $data->$key = $row[$value];
                                }
                            }
                        }
                        if ($importtype->update_table) {
                            $value = current((Array) $data);
                            $table_key = array_keys((Array) $data)[0];
                            $line = $this->$table->find('all', array('conditions' => array('examination_id' => $examination_id, $table_key => $value)))->first();
                            $line = $this->$table->patchEntity($line, (array) $data);
                        } else {
                            $line = $this->$table->newEntity();
                            $data->examination_id = $examination_id;
                            $line = $this->$table->patchEntity($line, (array) $data);
                        }
                        $result = $this->$table->save($line);
                    }
                    $i++;
                }
                $filepath = '';
            } else {
                
            }
        }
    }

    public function listImports() {
        if ($this->request->is('post')) {

            $import = $this->request->data();
            $examination_id = $import['examination_id'];
            $this->csv2db($import);
            return $this->redirect(['controller' => 'Departments', 'action' => 'list_dep/' . $examination_id]);
        }
        $list = $this->Imports->find('all', [
            'contain' => ['Schooltypes', 'Examinations']
        ]);
        $this->set('list', $list);
    }

    /**
     * Index method
     *
     * @return \Cake\Network\Response|null
     */
    public function index() {
        $this->paginate = [
            'contain' => ['Schooltypes', 'Examinations']
        ];
        $imports = $this->paginate($this->Imports);

        $this->set(compact('imports'));
        $this->set('_serialize', ['imports']);
    }

    /**
     * View method
     *
     * @param string|null $id Import id.
     * @return \Cake\Network\Response|null
     * @throws \Cake\Datasource\Exception\RecordNotFoundException When record not found.
     */
    public function view($id = null) {
        $import = $this->Imports->get($id, [
            'contain' => ['Schooltypes']
        ]);

        $this->set('import', $import);
        $this->set('_serialize', ['import']);
    }

    /**
     * Add method
     *
     * @return \Cake\Network\Response|null Redirects on successful add, renders view otherwise.
     */
    public function add() {
        $import = $this->Imports->newEntity();
        if ($this->request->is('post')) {
            //debug($this->request->data); die;
            $import = $this->Imports->patchEntity($import, $this->request->data);
            if ($this->Imports->save($import)) {
                $this->Flash->success(__('The import has been saved.'));

                return $this->redirect(['action' => 'index']);
            }
            $this->Flash->error(__('The import could not be saved. Please, try again.'));
        }
        $schooltypes = $this->Imports->Schooltypes->find('list', ['limit' => 200]);
        $examinations = $this->Imports->Examinations->find('list', ['limit' => 200]);
        $this->set(compact('import', 'schooltypes', 'examinations'));
        $this->set('_serialize', ['import']);
    }

    /**
     * Edit method
     *
     * @param string|null $id Import id.
     * @return \Cake\Network\Response|null Redirects on successful edit, renders view otherwise.
     * @throws \Cake\Network\Exception\NotFoundException When record not found.
     */
    public function edit($id = null) {
        $import = $this->Imports->get($id, [
            'contain' => []
        ]);
        if ($this->request->is(['patch', 'post', 'put'])) {
            $import = $this->Imports->patchEntity($import, $this->request->data);
            if ($this->Imports->save($import)) {
                $this->Flash->success(__('The import has been saved.'));

                return $this->redirect(['action' => 'index']);
            }
            $this->Flash->error(__('The import could not be saved. Please, try again.'));
        }
        $schooltypes = $this->Imports->Schooltypes->find('list', ['limit' => 200]);
        $examinations = $this->Imports->Examinations->find('list', ['limit' => 200]);
        $this->set(compact('import', 'schooltypes', 'examinations'));
        $this->set('_serialize', ['import']);
    }

    /**
     * Delete method
     *
     * @param string|null $id Import id.
     * @return \Cake\Network\Response|null Redirects to index.
     * @throws \Cake\Datasource\Exception\RecordNotFoundException When record not found.
     */
    public function delete($id = null) {
        $this->request->allowMethod(['post', 'delete']);
        $import = $this->Imports->get($id);
        if ($this->Imports->delete($import)) {
            $this->Flash->success(__('The import has been deleted.'));
        } else {
            $this->Flash->error(__('The import could not be deleted. Please, try again.'));
        }

        return $this->redirect(['action' => 'index']);
    }

}
