POS-Server/app/models/category.rb

class Category < ActiveRecord::Base
  ##--- Associaciones
  has_many :products, -> { where "products.status = 1" }
  belongs_to :parent, class_name: "Category"
  has_many :children, -> { where "categories.status!= 0" }, class_name: "Category", foreign_key: 'parent_id'

  enum status: [:erased, :active, :inactive]

  ##--- Llevar registro de Actividad del usuario
  audited

  ##--- Validaciones previas de guardar
  validates :category, presence: { message: "Debe capturar el nombre de la línea de producto." }

  ##--- Tipo de vistas / consultas
  scope :vigentes, -> { where("status != 0").order("status ASC, category ASC") }
  scope :activos, -> { where("status = 1").order("category ASC") }
  scope :activos_padre, -> { where("categories.status = 1 AND categories.parent_id = 0").order("categories.category ASC") }
  scope :get_parents, -> { where("categories.parent_id = 0 and categories.status != 0").order("categories.category ASC") }

  def descendents
    self_and_descendents - [self]
  end

  def self_and_descendents
    self.class.tree_for(self)
  end

  def self.tree_for(instance)
    where("#{table_name}.id IN (#{tree_sql_for(instance)})").order("#{table_name}.id")
  end

  def self.sales_by_category(pointsale_id)
    @pointsale = Pointsale.find(pointsale_id)
    @category_parents = Category.get_parents
    @sales_details = Pointsale.find(pointsale_id).sales_details.includes(:sale).where('sales.status != 1')
    puts "PUNTO DE VENTA: #{@pointsale.name}"
    puts "CATEGORIAS: #{@category_parents.count}"
    puts "TOTAL DE PRODUCTOS VENDIDOS: #{@sales_details.count}"

    cats = Hash.new
    @category_parents.each do |category|
      products_ids = category.children.includes(:products).pluck('products.id')
      prods_from_category = @sales_details.where('product_id in (?) ', products_ids).count
      percent_of_this_cat = (prods_from_category * 100) / @sales_details.count
      # puts "CATEGORIA: #{category.category} #{percent_of_this_cat}%"
      cats[category.category.to_s] = percent_of_this_cat unless percent_of_this_cat.zero?
    end
    cats.sort_by { |_key, value| value }.reverse
  end

  def self.tree_sql_for(instance)
    tree_sql = <<-SQL
      WITH RECURSIVE search_tree(id, path) AS (
          SELECT id, ARRAY[id]
          FROM #{table_name}
          WHERE id = #{instance.id}
        UNION ALL
          SELECT #{table_name}.id, path || #{table_name}.id
          FROM search_tree
          JOIN #{table_name} ON #{table_name}.parent_id = search_tree.id
          WHERE NOT #{table_name}.id = ANY(path)
      )
      SELECT id FROM search_tree ORDER BY path
    SQL
  end

  before_save do
    self.parent_id = 0 if parent_id.nil?
  end
end