<?php

class DmodelFidelityFidelity extends Model
{

  public function getRangePontos($data)
  {

    $where = $this->getFilterUnion($data);
    $join = $this->getJoinsUnion($data);

    $sql = "SELECT * FROM (
              SELECT COUNT(fp.fidelity_present_id) AS total, FLOOR(fp.pontos/101) AS `range`,
                           (100* FLOOR(fp.pontos/101)+1) AS minimo,
                           (100* FLOOR(fp.pontos/101)+100) AS `maximo`
                          FROM oc_fidelity_present fp
                          $join
                          WHERE fp.pontos <= 1000 $where
                          GROUP BY `range`
              UNION
              SELECT COUNT(fp.fidelity_present_id) AS total, FLOOR(fp.pontos/501) AS `range`,
                           (500* FLOOR(fp.pontos/501)+1) AS minimo,
                           (500* FLOOR(fp.pontos/501)+500) AS `maximo`
                          FROM oc_fidelity_present fp
                          $join
								          WHERE fp.pontos between 1001 and 5000 $where
                          GROUP BY `range`
              UNION
              SELECT COUNT(fp.fidelity_present_id) AS total, FLOOR(fp.pontos/1001) AS `range`,
                           (1000* FLOOR(fp.pontos/1001)+1) AS minimo,
                           (1000* FLOOR(fp.pontos/1001)+1000) AS `maximo`
                          FROM oc_fidelity_present fp
                          $join
								          WHERE fp.pontos between 5001 and 10000 $where
                          GROUP BY `range`
              UNION
              SELECT COUNT(fp.fidelity_present_id) AS total, FLOOR(fp.pontos/2001) AS `range`,
                           (2000* FLOOR(fp.pontos/2001)+1) AS minimo,
                           (2000* FLOOR(fp.pontos/2001)+2000) AS `maximo`
                          FROM oc_fidelity_present fp
                          $join
								          WHERE fp.pontos > 10001 $where
                          GROUP BY `range`) ranges where total > 0";

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

    return $query->rows;

  }

  protected function getFilterUnion($data)
  {

    $where = " AND p.date_available <= NOW() AND p.status = '1' ";

    if(!empty($data['filter_category_id']) && ($data['filter_category_id'] > 0)){
      $where    .= " AND (nsub.category_id = '" . (int)$data['filter_category_id']
                . "' OR ncat.category_id = '" . (int)$data['filter_category_id']
                . "' OR ndep.category_id = '" . (int)$data['filter_category_id']."') ";
    }

    return $where;
  }

  protected function getJoinsUnion($data)
  {

    $join = " INNER JOIN oc_product p ON (fp.product_id = p.product_id and p.quantity > 0) ";

    if(!empty($data['filter_category_id']) && ($data['filter_category_id'] > 0)){
      $join .=
      "   INNER JOIN oc_category nsub ON (p.category_id  = nsub.category_id)
          LEFT  JOIN oc_category ncat ON (nsub.parent_id = ncat.category_id)
          LEFT  JOIN oc_category ndep ON (ncat.parent_id = ndep.category_id)
      ";
    }

    return $join;

  }

  public function getConfiFidelity(){

    $fidelity_empresa_id = $this->config->get('fidelity_empresa_id');

    if($fidelity_empresa_id == Type_FidelityConfigs::Tp_Multiempresa){
      $sql = "select * from oc_fidelity where empresaId is null";
    }else{
      $sql = "select * from oc_fidelity where empresaId = $fidelity_empresa_id";
    }

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

    return $query->row;

  }


  public function getCategoryPointsByProductId($product_id){

    $sql = /*"SELECT fps.pontos
            FROM oc_product_to_category ptc
            INNER JOIN oc_category_description cd ON(ptc.category_id = cd.category_id)
            INNER JOIN oc_fidelity_pts_sg fps on (fps.category_id = ptc.category_id)
            WHERE ptc.product_id = '".$product_id."' LIMIT 0,1";*/
            "SELECT fps.pontos
            From oc_fidelity_pts_sg fps
            INNER JOIN oc_product  p ON (p.category_id = fps.category_id)
            WHERE p.product_id = $product_id LIMIT 0,1";


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

    return $query->row;

  }

}
