<?php
  
  class devolucion_model extends CI_Model
  {
    function __construct()
    {
      parent::__construct();
    }

    function apply_devolution ($data, $customer_id) {
      $query = $this->db->query("SELECT ((COALESCE(MAX(id), 0)) + 1) as id FROM credit_notes");
      $id = $query->row()->id;

      $sale_id = $this->db->query("SELECT id FROM sales_transactions WHERE folio_doc = '".$data['folio']."'");
      $sale_id = $sale_id->row()->id;

      $this->db->query(
        "UPDATE sales_transactions SET refund = 1, credit_note = ".$id." WHERE id = ".$sale_id
      );

      $this->db->query(
        "INSERT INTO credit_notes (id,sales_transactions_id,document_types_id,customers_id,update_time) VALUES (".$id.", ".$sale_id.", 61, ".$customer_id.", CURRENT_TIMESTAMP)"
      );

      return $id;
    }

    function revert_devolution ($credit_note_id) {
      $this->db->query(
        "UPDATE sales_transactions SET refund = 0, credit_note = NULL WHERE credit_note = " . $credit_note_id);

      $this->db->query(
        "DELETE FROM credit_notes WHERE id = " . $credit_note_id);
    }

    function get_document_customer($folio) {
      $query = $this->db->query("SELECT customers_id as id FROM sales_transactions WHERE folio_doc = '" . $folio . "'");

      return $query->row();
    }

    function create_customer($data) {
      $query = $this->db->query("SELECT id FROM customers WHERE rut = '".$data['rut_customer']."'");

      if ($query->num_rows() > 0) {
        $id = $query->row()->id;

        $this->db->query("UPDATE customers SET first_name = '".$data['first_name_customer']."', last_name = '".$data['last_name_customer']."', turn = '".$data['turn_customer']."', phone = '".$data['phone_customer']."', email = '".$data['email_customer']."', address = '".$data['address_customer']." ".$data['address_number_customer']."', postal_code = ".$data['postal_customer'].", commune = '".$data['commune_customer']."', city = '".$data['city_customer']."'  WHERE id = ".$id);

        return array(
          'id' => $id,
          'exist' => TRUE
        );
      }

      $query = $this->db->query("SELECT ((COALESCE(MAX(id), 0)) + 1) as id FROM customers");
      $id = $query->row()->id;

      $cardcode = 'C'.substr($data['rut_customer'], 0, strlen($data['rut_customer']) - 2);

      $this->db->query("INSERT INTO customers (id,customer_types_id,rut,first_name,last_name,email,is_active,phone,address,communes_id,cities_id,turn,postal_code,commune,city,cardcode,business_name) VALUES (".$id.",1,'".$data['rut_customer']."','".$data['first_name_customer']."','".$data['last_name_customer']."','".$data['email_customer']."',true,'".$data['phone_customer']."','".$data['address_customer']." ".$data['address_number_customer']."',1,1,'".$data['turn_customer']."',".$data['postal_customer'].",'".$data['commune_customer']."','".$data['city_customer']."', '".$cardcode."','".$data['first_name_customer']." ".$data['last_name_customer']."')");

      return array(
        'id' => $id,
        'exist' => FALSE,
        'cardcode' => $cardcode
      );
    }

    function delete_customer($rut) {
      $query = $this->db->query("DELETE FROM customers WHERE rut = '".$rut."'");
    }

    function get_document ($folio) {

      $query = $this->db->query(
        "SELECT id, document_types_id as type, refund as retornada, folio_doc as folio, to_char(create_time, 'DD/MM/YYYY HH24:MI:SS') as fecha_hora, sessions_id as session,
        COALESCE((discount_rate * 100), 0) as descuento,
        discounts as monto_descuento, redondeo, net_total as total
        FROM sales_transactions
        WHERE document_types_id <> 52
        AND folio_doc = '".$folio."'"
      );

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

      return $query->row();
    }

    function get_detail ($sale_id) {
      $query = $this->db->query(
        "select
        sales_transactions_details.id as \"id\",
        sales_transactions_details.products_id as \"products_id\",
        products.name as producto,
        sales_transactions_details.quantity_items as cantidad,
        sales_transactions_details.item_price as precio,
        sales_transactions_details.total_price as total,
        sales_transactions_details.refund as devuelto,
        sales_transactions_details.refund_quantity as cantidad_devuelta
        from sales_transactions_details
        inner join products
        on sales_transactions_details.products_id = products.id
        where sales_transactions_details.sales_transactions_id = ".$sale_id."
        order by sales_transactions_details.id ASC"
      );

      return $query->result();
    }

    function get_pay ($sale_id) {
      $query = $this->db->query(
        "select
        payment_methods.name as nombre,
        payments.amount as monto,
        payments.voucher_number as voucher
        from payments
        inner join payment_methods
        on payments.payment_methods_id = payment_methods.id
        where payments.sales_transactions_id = ".$sale_id
      );

      return $query->result();
    }
  }