<?php
namespace App\Controller;

use App\Controller\AppController;
use Cake\Mailer\Email;
use Cake\I18n\Time;

/**
 * Emails Controller
 *
 * @property \App\Model\Table\EmailsTable $Emails
 */
class EmailsController extends AppController
{
    public function deleteall() {
        $this->Emails->connection()->transactional(function ($conn) {
            $sqls = $this->Emails->schema()->truncateSql($this->Emails->connection());
            foreach ($sqls as $sql) {
                $this->Emails->connection()->execute($sql)->execute();
            }
        });
    }

    public function sendresult() {
        $this->loadModel('Offices');

        $emails = $this->Emails->find('all', [
            'conditions' => ['sent' => 0]
        ]);
        foreach($emails as $mail) {

            $query = $this->Offices->find('all', [
                'conditions' => ['dst_schluessel' => $mail->snr]
            ]);
            //debug($mail->snr);die;
            $office = $query->first();
            //debug($office->dst_name);
            //die;
            $email = new Email();
            $email->viewVars(['content' => $mail->text]);
            $email
                ->emailFormat('html')
                ->from(['abschlusspruefung@schulen.bayern.de' => 'Abschlusspruefung'])
                ->to($mail->snr . '@schulen.bayern.de')
                ->cc('abschlusspruefung@schulen.bayern.de')
                ->template('abschluss')
                ->subject($mail->subject . ' ' . $office->dst_name . ' - Übermittlung Abschlussprüfung Typ ' . $mail->examinationtype)
                ->send();

            $mail['sent'] = 1;
            //$line = $this->Apimports->patchEntity($line, (array) $data);                       
            $result = $this->Emails->save($mail);
        }   
        return $this->redirect(['controller' => 'Apresults', 'action' => 'export']);    
    }

        public function create() {
            $query = $this->Emails->find('all',[
                'order' => ['lieferung DESC']
            ]);
            $lastmail = $query->first();
            if(!empty($lastmail)) {
                $lastsent = $lastmail->lieferung;
            }
            else {
                $lastsent = 0;
            }
            //debug($lastsent);
            $this->loadModel('Apimports');
            $deliveries = $this->Apimports->find('all', [
                'conditions' => ["lieferung > $lastsent"],
                'order' => 'lieferung ASC'
            ]);
            $deliveries->select(['lieferung'])
                ->distinct(['lieferung']);
            //debug($deliveries);    
            foreach ($deliveries as $delivery) {
                //debug($delivery->lieferung);
                $items = $this->Apimports->find('all',[
                    'conditions' => ['lieferung' => $delivery->lieferung ]
                ])->toArray();
                $data = (object) [];
                $data->examinationtype = $items[0]['pruefungsart'];
                $data->snr = $items[0]['schule'];
                $data->createdate = $items[0]['erzeugungsdatum'];
                $data->date = Time::now();
                $data->lieferung = $delivery->lieferung;
                $data->sent = 0;
                $data->subject = $items[0]['schule'];
                $data->text = '<h2>Übermittlung der Ergebnisse der Abschlussprüfung Typ ' . $items[0]['pruefungsart'] . '</h2>';
                $data->text .= '<p>Für die Schule ' . $items[0]['schule'] . ' wurden am ' . $items[0]['erzeugungsdatum']->i18nFormat('dd.MM.yyyy') . ' um ' . $items[0]['erzeugungsdatum']->i18nFormat('HH:mm') . ' Uhr folgende Ergebnisse übermittelt:</p>';
                $data->text .= '<table border="1"><tr><th align="left">Anzahl</th><th align="left">Schülerstatus</th></tr>';
                foreach ($items as $item) {
                    //debug($item);
                    $data->text .= '<tr><td align="left">'. $item->anzahl . '</td><td align="left">' . $item->schuelerstatus .'</td></tr>';
                }
                $data->text .= '</table>';
                $data->text .= '<p>Bitte überprüfen Sie diese Ergebnisse sorgfältig und wenden Sie sich umgehend an den ASV Support, falls Unstimmigkeiten auftreten sollten.</p>';
                $data->text .= '<p>Ihr ASD-Team</p>';
                
                //debug($data);
                $email = $this->Emails->newEntity();
        
                $email = $this->Emails->patchEntity($email, (array) $data);
                $this->Emails->save($email);
            }
            //die;
            return $this->redirect(['action' => 'sendresult']);
        }

        public function import() {
            $options = [
                'length' => 0,
                'delimiter' => '|',
                'enclosure' => ' ',
                'escape' => '\\',
                'headers' => false,
                'text' => false,
                'excel_bom' => false,
            ];
            $this->loadModel('Apimports');
            // Tabelle leeren
            $this->Apimports->connection()->transactional(function ($conn) {
                $sqls = $this->Apimports->schema()->truncateSql($this->Apimports->connection());
                foreach ($sqls as $sql) {
                    $this->Apimports->connection()->execute($sql)->execute();
                }
            });
            
            $filepath = '/var/www/html/asd-control/webroot/files/ap/results.csv';
            $handle = fopen($filepath, "r");
            //$i = 1;
            //$header = fgetcsv($handle, $options['length'], $options['delimiter']);
            while ($row = fgetcsv($handle, $options['length'], $options['delimiter'])) {
                //debug($row);
                $data = (object) [];
                $data->schule           = trim($row[0]);
                $data->pruefungsart     = trim($row[1]);
                $data->anzahl           = trim($row[2]);
                $data->schuelerstatus   = trim($row[3]);                  
                $data->erzeugungsdatum  = date('Y-m-d H:i:s', strtotime(str_replace('-', '/', trim($row[4]))));
                $data->lieferung        = trim($row[5]);
                $line = $this->Apimports->newEntity();                           
                $line = $this->Apimports->patchEntity($line, (array) $data);                       
                $result = $this->Apimports->save($line);
                //$i++;
            }
            //die;
            return $this->redirect(['action' => 'create']);
        }


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

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

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

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

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

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

    /**
     * Edit method
     *
     * @param string|null $id Email 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)
    {
        $email = $this->Emails->get($id, [
            'contain' => []
        ]);
        if ($this->request->is(['patch', 'post', 'put'])) {
            $email = $this->Emails->patchEntity($email, $this->request->data);
            if ($this->Emails->save($email)) {
                $this->Flash->success(__('The email has been saved.'));

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

    /**
     * Delete method
     *
     * @param string|null $id Email 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']);
        $email = $this->Emails->get($id);
        if ($this->Emails->delete($email)) {
            $this->Flash->success(__('The email has been deleted.'));
        } else {
            $this->Flash->error(__('The email could not be deleted. Please, try again.'));
        }

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