<?php 

class auditoria_model extends CI_Model {
    function __construct () {
        parent::__construct();
    }

    function audit_product ($id, $start_date, $end_date, $from) {
        $query = $this->db->query("
            SELECT TO_CHAR(audit.create_time, 'DD/MM/YYYY') AS \"Fecha\",
            TO_CHAR(audit.create_time, 'HH24:MI:SS') AS \"Hora\",
            audit.document_type AS \"Tipo\", audit.document_id AS \"Numero\",
            audit.quantity AS \"Cantidad\", audit.accu AS \"Acumulada\"
            FROM audit
            WHERE audit.product_id = ".$id."
            AND audit.create_time BETWEEN '".$start_date."' AND '".$end_date."'
            ORDER BY create_time ASC
            LIMIT 40 OFFSET ".$from."
        ");
        return $query->num_rows() === 0 ? NULL : $query->result();
    }

    function search_product ($text) {
        $query = $this->db->query("
            SELECT id, name FROM products
            WHERE name LIKE '%".$text."%'
            LIMIT 5
        ");

        if ($query->num_rows() === 0) {
            return NULL;
        }

        return $query->result();
    }

    function audit_sale ($product_id, $quantity, $create_time, $document_id) {
        $this->db->query(
            "INSERT INTO audit (product_id, warehouse_id, document_type, document_id, 
            quantity, accu, create_time) VALUES (".$product_id.",
            1, 'venta', ".$document_id.", ".$quantity.",
            (COALESCE((SELECT accu FROM audit
            WHERE product_id = ".$product_id." ORDER BY id DESC LIMIT 1), 0) - ".$quantity."),
            '".$create_time."')"
        );
    }

    function audit_reception ($product_id, $quantity, $document_id) {
        $this->db->query(
            "INSERT INTO audit (product_id, warehouse_id, document_type, document_id, 
            quantity, accu) VALUES (".$product_id.",
            1, 'recepcion', ".$document_id.", ".$quantity.",
            (COALESCE((SELECT accu FROM audit
            WHERE product_id = ".$product_id." ORDER BY id DESC LIMIT 1), 0) + ".$quantity."))"
        );
    }

    function audit_devolution ($product_id, $quantity, $document_id) {
        $this->db->query(
            "INSERT INTO audit (product_id, warehouse_id, document_type, document_id, 
            quantity, accu) VALUES (".$product_id.",
            1, 'devolucion', ".$document_id.", ".$quantity.",
            (COALESCE((SELECT accu FROM audit
            WHERE product_id = ".$product_id." ORDER BY id DESC LIMIT 1), 0) + ".$quantity."))"
        );
    }

    function remove_audit ($document_id) {
        $this->db->query(
            "DELETE FROM audit WHERE document_id = " . $document_id);
    }
}