app/models/checkout_statistics.rb
# encoding: utf-8
class CheckoutStatistics
include ActiveModel::Conversion
include ActiveModel::Validations
extend ActiveModel::Naming
extend ActiveModel::Translation
def initialize(attributes = {})
attributes.each do |name, value|
send("#{name}=", value) rescue nil
end
end
def persisted? ; false ; end
attr_accessor :statistical_table_type, :checked_at_from, :checked_at_to, :aggregation_type, :classification_type_id, :first_aggregation, :second_aggregation
validates_presence_of :checked_at_from
validates_presence_of :checked_at_to
validates_presence_of :aggregation_type
validate :checked_at_from_valid?
validate :checked_at_to_valid?
validate :checked_at_range_valid?
def self.first_aggregations
return [[I18n.t('activerecord.models.user_group'), 'user_group']]
end
def self.second_aggregations
return [[I18n.t('activerecord.attributes.agent.grade'), 'grade']]
end
def make_data
# 集計日付を求める
if md = self.checked_at_from.match(/^([0-9]{4})-([0-9]{2})$/)
checked_at_from = "#{md[1]}-#{md[2]}-01"
else
checked_at_from = self.checked_at_from
end
if md = self.checked_at_to.match(/^([0-9]{4})-([0-9]{2})$/)
day = Date.new(md[1].to_i, md[2].to_i, 1).end_of_month
checked_at_to = day.to_s
else
checked_at_to = self.checked_at_to
end
# 集計方法1
if self.aggregation_type == "month"
# 月別
months = []
from_date_obj = Date.parse(checked_at_from)
to_date_obj = Date.parse(checked_at_to)
# 集計月を求める
if from_date_obj.mon == to_date_obj.mon
months << {:display_name => from_date_obj.strftime("%Y-%m"), :from_date => from_date_obj.beginning_of_day, :to_date => to_date_obj.end_of_day}
else
if from_date_obj.day != 1
months << {:display_name => from_date_obj.strftime("%Y-%m"), :from_date => from_date_obj.beginning_of_day, :to_date => from_date_obj.end_of_month.end_of_day}
end
(from_date_obj..to_date_obj).each{|i|
next if i.day != 1
if from_date_obj.year == i.year && from_date_obj.month == i.month
from_date = from_date_obj.beginning_of_day
else
from_date = i.beginning_of_day
end
if to_date_obj.year == i.year && to_date_obj.month == i.month
to_date = to_date_obj.end_of_day
else
to_date = Date.new(i.year, i.month, -1).end_of_day
end
months << {:display_name => i.strftime("%Y-%m"), :from_date => from_date, :to_date => to_date}
}
end
cols = months
elsif self.aggregation_type == "classification_type"
# 分類別
groups = Classification.select("group_identifier").where("classification_type_id = ?", self.classification_type_id).group("group_identifier")
classification_groups = []
groups.each do |group|
if group.group_identifier.present?
classification_ids = Classification.where("group_identifier = ?", group.group_identifier).pluck(:id)
else
# グループ未設定分
classification_ids = Classification.where("group_identifier is null").pluck(:id)
end
classification_groups << {:display_name => group.group_identifier, :classification_ids => classification_ids}
end
cols = classification_groups
end
# 集計方法2
if self.first_aggregation == "user_group"
first_aggregation_column = "users.user_group_id"
first_aggregations = UserGroup.all
end
if self.second_aggregation == "grade"
second_aggregation_column = "agents.grade_id"
second_aggregations = Keycode.where(:name => 'agent.grade')
end
data = []
sum_books = Array.new(cols.length, 0)
sum_persons = Array.new(cols.length, 0)
checkouts = Checkout.joins(:user => :agent)
checkouts = checkouts.joins(:item => {:manifestation => :manifestation_has_classifications})
if self.aggregation_type == "classification_type"
from_date_obj = Date.parse(checked_at_from)
to_date_obj = Date.parse(checked_at_to)
checkouts = checkouts.where("checked_at >= ? and checked_at <= ?", from_date_obj.beginning_of_day, to_date_obj.end_of_day)
end
first_aggregations.each do |first_aggregation|
first_checkouts = checkouts.where("#{first_aggregation_column} = ?", first_aggregation.id)
details = []
second_aggregations.each do |second_aggregation|
if User.joins(:agent).where("#{first_aggregation_column} = ?", first_aggregation.id).where("#{second_aggregation_column} = ?", second_aggregation.id).present?
second_checkouts = first_checkouts.where("#{second_aggregation_column} = ?", second_aggregation.id)
# 冊数
books = []
sum_book = 0
if self.aggregation_type == "month"
months.each do |month|
book = second_checkouts.select("checkouts.id").where("checked_at >= ? and checked_at <= ?", month[:from_date], month[:to_date]).reorder("checkouts.id").uniq
books << book.length
end
elsif self.aggregation_type == "classification_type"
classification_groups.each do |classification_group|
book = second_checkouts.select("checkouts.id").where("manifestation_has_classifications.classification_id in (?)", classification_group[:classification_ids]).reorder("checkouts.id").uniq
books << book.length
end
end
# 人数
persons = []
sum_person = 0
if self.aggregation_type == "month"
months.each do |month|
person = second_checkouts.select("checkouts.user_id").where("checked_at >= ? and checked_at <= ?", month[:from_date], month[:to_date]).reorder(:user_id).uniq
persons << person.length
end
elsif self.aggregation_type == "classification_type"
classification_groups.each do |classification_group|
person = second_checkouts.select("checkouts.user_id").where("manifestation_has_classifications.classification_id in (?)", classification_group[:classification_ids]).reorder(:user_id).uniq
persons << person.length
end
end
details << {:second_aggregation_name => second_aggregation.keyname, :books => books, :persons => persons}
end
end
# 学年未設定分
if User.joins(:agent).where("#{first_aggregation_column} = ?", first_aggregation.id).where("#{second_aggregation_column} is null").present?
second_checkouts = first_checkouts.where("#{second_aggregation_column} is null")
# 冊数
books = []
sum_book = 0
if self.aggregation_type == "month"
months.each do |month|
book = second_checkouts.select("checkouts.id").where("checked_at >= ? and checked_at <= ?", month[:from_date], month[:to_date]).reorder("checkouts.id").uniq
books << book.length
end
elsif self.aggregation_type == "classification_type"
classification_groups.each do |classification_group|
book = second_checkouts.select("checkouts.id").where("manifestation_has_classifications.classification_id in (?)", classification_group[:classification_ids]).reorder("checkouts.id").uniq
books << book.length
end
end
# 人数
persons = []
sum_person = 0
if self.aggregation_type == "month"
months.each do |month|
person = second_checkouts.select("checkouts.user_id").where("checked_at >= ? and checked_at <= ?", month[:from_date], month[:to_date]).reorder(:user_id).uniq
persons << person.length
end
elsif self.aggregation_type == "classification_type"
classification_groups.each do |classification_group|
person = second_checkouts.select("checkouts.user_id").where("manifestation_has_classifications.classification_id in (?)", classification_group[:classification_ids]).reorder(:user_id).uniq
persons << person.length
end
end
details << {:second_aggregation_name => "", :books => books, :persons => persons}
end
# 小計
books = Array.new(cols.length, 0)
persons = Array.new(cols.length, 0)
details.each do |detail|
cols.each_with_index do |col, index|
books[index] += detail[:books][index]
persons[index] += detail[:persons][index]
sum_books[index] += detail[:books][index]
sum_persons[index] += detail[:persons][index]
end
end
# 合計の出力は、第２集計が複数の場合のみとする
if details.length > 1
details << {:second_aggregation_name => I18n.t('statistical_table.subtotal'), :books => books, :persons => persons}
end
data << {:first_aggregation_name => first_aggregation.display_name, :details => details}
end
# 総合計
data << {:first_aggregation_name => I18n.t('statistical_table.total'), :details => [{:second_aggregation_name => "", :books => sum_books, :persons => sum_persons}]}
conditions = self
conditions.checked_at_from = checked_at_from
conditions.checked_at_to = checked_at_to
return {:data => data, :cols => cols, :conditions => conditions}
end
def self.output_excelx(output_data, filename = nil)
require 'axlsx'
# initialize
if filename.present?
# 定期作成
out_dir = "#{Rails.root}/private/system/checkout_statistics"
excel_filepath = "#{out_dir}/list#{Time.now.strftime('%s')}#{filename}.xlsx"
else
# 手動作成
out_dir = "#{Rails.root}/private/user_file/checkout_statistics"
excel_filepath = "#{out_dir}/list#{Time.now.strftime('%s')}#{rand(10)}.xlsx"
end
FileUtils.mkdir_p(out_dir) unless FileTest.exist?(out_dir)
Rails.logger.info "output_checkout_statistics_excelx filepath=#{excel_filepath}"
Axlsx::Package.new do |p|
wb = p.workbook
wb.styles do |s|
sheet = wb.add_worksheet(:name => I18n.t('activemodel.models.checkout_statistics'))
top_style = s.add_style :font_name => Setting.checkout_statistics_print_excelx.fontname, :sz => 16, :alignment => {:horizontal => :center, :vertical => :center}
default_style = s.add_style :font_name => Setting.checkout_statistics_print_excelx.fontname
# ヘッダ
sheet.add_row [I18n.t('activemodel.models.checkout_statistics')], :types => :string, :style => top_style
sheet.merge_cells "A1:O1"
condition = []
condition << I18n.t('statistical_table.checkout_period') + ":"
condition << output_data[:conditions].checked_at_from
condition << "～"
condition << output_data[:conditions].checked_at_to
condition << ""
condition << I18n.t('activemodel.attributes.checkout_statistics.aggregation_type') + ":"
condition << I18n.t("statistical_table.checkout_statistics.aggregation_type.#{output_data[:conditions].aggregation_type}")
if output_data[:conditions].aggregation_type == "classification_type"
classification_type = ClassificationType.where("id = ?", output_data[:conditions].classification_type_id).first
condition << classification_type.display_name
else
condition << ""
end
condition << ""
condition << I18n.t('activemodel.attributes.checkout_statistics.first_aggregation') + ":"
condition << I18n.t("statistical_table.checkout_statistics.first_aggregation.#{output_data[:conditions].first_aggregation}")
condition << I18n.t("statistical_table.checkout_statistics.second_aggregation.#{output_data[:conditions].second_aggregation}")
condition << ""
condition << I18n.t('statistical_table.output_date') + ":"
condition << Date.today
sheet.add_row condition, :types => :string, :style => default_style
# 項目名
columns = [I18n.t('activerecord.attributes.user.user_group'), I18n.t('activerecord.attributes.agent.grade'), ""]
output_data[:cols].each do |col|
columns << col[:display_name]
end
columns << I18n.t('statistical_table.total')
sheet.add_row columns, :types => :string, :style => default_style
# データ
output_data[:data].each do |datum|
datum[:details].each_with_index do |detail, index|
# 冊数
if index == 0
books_row = [datum[:first_aggregation_name]]
else
books_row = [""]
end
books_row << detail[:second_aggregation_name]
books_row << I18n.t('statistical_table.books')
books_row += detail[:books]
books_row << detail[:books].inject(:+)
sheet.add_row books_row, :types => :string, :style => default_style
# 人数
persons_row = ["", ""]
persons_row << I18n.t('statistical_table.persons')
persons_row += detail[:persons]
persons_row << detail[:persons].inject(:+)
sheet.add_row persons_row, :types => :string, :style => default_style
end
end
p.serialize(excel_filepath)
end
end
return excel_filepath
end
private
def checked_at_from_valid?
if checked_at_from.present?
unless /^[0-9]{4}-[0-9]{2}$/ =~ checked_at_from || /^[0-9]{4}-[0-9]{2}-[0-9]{2}$/ =~ checked_at_from
errors.add(:checked_at_from)
end
end
end
def checked_at_to_valid?
if checked_at_to.present?
unless /^[0-9]{4}-[0-9]{2}$/ =~ checked_at_to || /^[0-9]{4}-[0-9]{2}-[0-9]{2}$/ =~ checked_at_to
errors.add(:checked_at_to)
end
end
end
def checked_at_range_valid?
if checked_at_from.present? && checked_at_to.present?
if (/^[0-9]{4}-[0-9]{2}$/ =~ checked_at_from && /^[0-9]{4}-[0-9]{2}$/ =~ checked_at_to) || (/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/ =~ checked_at_from && /^[0-9]{4}-[0-9]{2}-[0-9]{2}$/ =~ checked_at_to)
if checked_at_from > checked_at_to
errors.add(:checked_at_from)
end
else
errors.add(:checked_at_from)
end
end
end
end