<?php

defined('BASEPATH') or exit('No direct script access allowed');

class ws_model extends CI_Model
{

    public function login($identity, $password)
    {

        if (empty($identity) || empty($password)) {
            return;
        }

        $query = $this->db->select('id,username, password, ruolo')
            ->where('username', $identity)
            ->where('password', $password)
            ->limit(1)
            ->order_by('id', 'desc')
            ->get('users');
        $user = $query->row();

        return $user;
    }

    //RECUPERO MATRICOLA
    public function get_matricola($MAART = '', $MADE2)
    {
        $query = "SELECT a.CORAGS, m.MAART, m.MAVAR, m.MATIT, m.MAMET, m.MAARM, m.MAVAM, m.MATIM, m.MAMEM, m.MADAT, m.MADE1, m.MADE2, m.MAPRD, m.MACTP, m.MACLA, m.MADAE, art.ARCA05, art.ARDES,  "
            . " car.CACOD, car.CADES, LEFT(car.CADES,LENGTH(car.CADES)-6) AS DESCR_BTN, SUBSTR(car.CADES, LENGTH(car.CADES)-4,2) AS AA_REVISIONE, RIGHT(car.CADES,2) AS AA_COLLAUDO  "
            . "from MATRI00F as m";
        $query .= " left join anaco00f as a on m.MACTP=a.COCONT";
        $query .= " left join ARTAR00F as art on m.MAART = art.ARCDA";
        $query .= " left join TCA0500F as car on art.ARCA05 = car.CACOD";
        $query .= " WHERE trim(m.MADE2)='" . trim($MADE2) . "'";
        $query .= " AND a.comast='281'";

        if (!empty($MAART))
            $query .= " AND trim(m.MAART)='" . trim($MAART) . "' ";

        $risultato = $this->db->query($query);

        $retRisultato = $risultato->result();

        if (!empty($retRisultato)) {
            $car05articolo = $retRisultato[0]->CACOD;

            if (empty($car05articolo)) {
                $caratteristicaDefault = $this->getCaratteristica05Default();
                if (!empty($caratteristicaDefault)) {
                    $retRisultato[0]->CACOD = $caratteristicaDefault[0]->CACOD;
                    $retRisultato[0]->CADES = $caratteristicaDefault[0]->CADES;
                    $retRisultato[0]->DESCR_BTN = $caratteristicaDefault[0]->DESCR_BTN;
                    $retRisultato[0]->AA_REVISIONE = $caratteristicaDefault[0]->AA_REVISIONE;
                    $retRisultato[0]->AA_COLLAUDO = $caratteristicaDefault[0]->AA_COLLAUDO;
                }
            }
        }
        return $retRisultato;
    }

    public function inserisci_matricola($post)
    {
        $query = "select NMNUM from TBLNM00F WHERE NMDIT=0 AND NMAAC='MATR' AND NMCDE='INT'";
        $risultato = $this->db->query($query);
        $num = ($risultato->row()->NMNUM) + 1;

        $query = "UPDATE TBLNM00F SET NMNUM=" . $num . " WHERE NMDIT=0 AND NMAAC='MATR' AND NMCDE='INT'";
        $res = $this->db->query($query);
        $query = "INSERT INTO MATRI00F"
            . "(MAART, MAVAR, MATIT, MAMET, MAARM, MAVAM, MATIM, MAMEM, MADAT, MADE1, MADE2, MAPRD, MACTP, MACLA, MAMOD, MADAE) "
            . " VALUES "
            . "('" . str_pad($post['articolo'], 15, ' ') . "','',0,'','" . str_pad($num, 15, ' ') . "','',0,''," . $post['anno'] . "0101,'','" . $post['matricola'] . "',''," . $post['fornitore'] . ",'" . $post['classe_di_fuoco'] . "','" . $post['modello'] . "'," . $post['anno'] . "0101)";
        //        var_dump($query);
        //        die();
        $res = $this->db->query($query);

        return $res;
    }

    public function get_fornitori($param)
    {
        $param = strtoupper($param);
        $query = "
        SELECT  COCONT, 
        COCONT || ' - ' || Trim(Replace(Replace(CORAGS, '''', ' '), '\"', ' '))  AS CORAGS
        FROM   anaco00f 
        WHERE  1=1
            AND CODITT = '1' 
            AND comast = '281'  
            AND (COCONT LIKE '%$param%' OR CORAGS LIKE '%$param%')";
        //echo $query;
        $risultato = $this->db->query($query);
        return $risultato->result();
    }

    public function get_articoli_autocomplete($param)
    {
        // FILTRATO per aesse impianti ARLIS TOGLIENDO LINEE MANUTENZIONI E LINEE VITA solo ARCA01 = est

        $param = strtoupper($param);
        $query = "SELECT  trim(ARCDA) as ARCDA, 
        trim(ARCDA)|| ' - '|| trim(ARDES) as ARDES
        FROM ARTAR00f 
        where trim(ARCA17) = '1'
        AND trim(ARCA03)='E'
        AND (ARCDA like '%$param%' OR ARDES LIKE '%$param%') 
        AND TRIM(ARLIS) <> 'LMA' AND TRIM(ARLIS)<>'LV'
        AND TRIM(ARCA01)='EST' ORDER BY ARCDA";

        // $file_pointer = fopen("C:\\Users\\user02\\Desktop\\TEST\\test.txt", "a+");
        // fwrite($file_pointer, $query);

        $risultato = $this->db->query($query);
        return $risultato->result();
    }

    public function get_articoli($param)
    {
        $query = "SELECT *
        FROM   ARTAR00f 
        where trim(ARCA17) = '1'
        AND trim(ARCA03)='E'";
        if (!is_null($param) || !empty($param)) {
            $param = strtoupper($param);
            $query .= " AND ARCDA LIKE '%$param%' ";
        }
        // $file_pointer = fopen("C:\\Users\\user02\\Desktop\\TEST\\test.txt", "a+");
        // fwrite($file_pointer, $query);

        //echo $query;
        $risultato = $this->db->query($query);
        return $risultato->result();
    }

    public function get_articoli_etichetta($param)
    {

        $query = "SELECT *
        FROM   ARTAR00f 
        where trim(ARCA17) = '1'
        AND trim(ARCA03)='E'";
        if (!is_null($param) || !empty($param)) {
            $param = trim(strtoupper($param));
            $query .= " AND TRIM(ARCDA) = TRIM('$param') ";
        }

        //echo $query;
        $risultato = $this->db->query($query);
        return $risultato->result();
    }

    public function get_articoli_Listener($param)
    {

        $query = "SELECT *
        FROM   ARTAR00f 
        where trim(ARCA17) = '1'
        AND trim(ARCA03)='E'";
        if (!is_null($param) || !empty($param)) {
            $param = trim(strtoupper($param));
            $query .= " AND ARCDA = '$param' ";
        }

        //echo $query;
        $risultato = $this->db->query($query);
        return $risultato->result();
    }

    public function set_local_ip($param)
    {
        $data = array('local_ip' => $param);
        if ($this->db->insert('rete', $data)) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function delete_local_ip()
    {
        $query = "TRUNCATE TABLE rete";
        // $risultato = $this->db->query($query);
        // return $risultato->result();
        if ($this->db->query($query)) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function get_qr_code()
    {
        $DB = $this->load->database('mysql', TRUE);

        $query = $DB
            ->limit(1)
            ->order_by('id', 'desc')
            ->get('qr_code');
        $qr_code = $query->row();

        return $qr_code;
    }

    public function insert_qr_code($param)
    {
        $DB = $this->load->database('mysql', TRUE);

        if ($DB->insert('qr_code', $param)) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    public function invia_stampa($ip, $porta, $etichetta)
    {

        // Salvo lo stato corrente di error reporting
        $defaulErrorReporting = error_reporting();

        // Disattivo gli tutti gli errori PHP, in modo che in caso di errore,
        // non venga restituita la pagina html di errore di CodeIgniter,
        // e la chiamata possa restituire l'errore 500.
        error_reporting(0);

        $fp = pfsockopen($ip, $porta, $errno, $errstr, 30); // Porta 9100 Standard per TCP/IP

        // Se la connessione fallisce, genero un'eccezione
        if (!$fp) {
            // Reimposto la gestione degli errori allo stato precedente
            error_reporting($defaulErrorReporting);
            throw new Exception('Errore di stampa.</br>' . $errstr);
        }

        fputs($fp, $etichetta);
        fclose($fp);

        // Reimposto la gestine deglie errori allo stato precedente
        error_reporting($defaulErrorReporting);
    }

    public function getCaratteristiche05()
    {
        $query = "
        SELECT CACOD, 
        CADES, 
        LEFT(CADES,LENGTH(CADES)-6) AS DESCR_BTN,
        SUBSTR(CADES, LENGTH(CADES)-4,2) AS AA_REVISIONE, 
        RIGHT(CADES,2) AS AA_COLLAUDO 
        FROM TCA0500F";

        //echo $query;
        $risultato = $this->db->query($query);
        return $risultato->result();
    }

    public function getCaratteristica05Default()
    {
        $query = "
        SELECT CACOD, 
        CADES, 
        LEFT(CADES,LENGTH(CADES)-6) AS DESCR_BTN,
        SUBSTR(CADES, LENGTH(CADES)-4,2) AS AA_REVISIONE, 
        RIGHT(CADES,2) AS AA_COLLAUDO 
        FROM TCA0500F
        where CACOD ='0001'";

        //echo $query;
        $risultato = $this->db->query($query);
        return $risultato->result();
    }

    public function getArticolo($codArticolo)
    {
        $query = "
        SELECT *
        FROM ARTAR00F
        WHERE trim(ARCDA) = '$codArticolo'";

        $risultato = $this->db->query($query);

        $objReturn = $this->utility_model->utf8ize($risultato->result());
        $objReturn = json_encode($objReturn);

        return $objReturn;
    }
}
