plugins/orders/lib/orders_plugin/report.rb
module OrdersPlugin::Report
protected
def report_products_by_supplier(products_by_suppliers)
p = Axlsx::Package.new
p.use_autowidth = true
wb = p.workbook
# create styles
defaults = { fg_color: "000000", sz: 8, alignment: { horizontal: :left, vertical: :center, wrap_text: false }, border: 0 }
redcell = wb.styles.add_style bg_color: "E8D0DC", fg_color: "000000", sz: 8, b: true, wrap_text: true, alignment: { horizontal: :left }, border: 0
yellowcell = wb.styles.add_style bg_color: "FCE943", fg_color: "000000", sz: 9, b: true, wrap_text: true, alignment: { horizontal: :left }, border: 0
greencell = wb.styles.add_style(defaults.merge(bg_color: "00AE00", fg_color: "ffffff", b: true))
bluecell = wb.styles.add_style(defaults.merge(bg_color: "99CCFF", b: true))
default = wb.styles.add_style(defaults.merge(border: 0))
# bluecell_b_top = wb.styles.add_style(defaults.merge({bg_color: "99CCFF", b: true, border: {style: :thin, color: "FF000000", edges: [:top]}}))
# date = wb.styles.add_style(defaults.merge({format_code: t('lib.report.mm_dd_yy_hh_mm_am_pm')}))
currency = wb.styles.add_style(defaults.merge(format_code: t("number.currency.format.xlsx_currency")))
# border_top = wb.styles.add_style border: {style: :thin, color: "FF000000", edges: [:top]}
# supplier block start index (shifts on the loop for each supplier)
sbs = 3
# create sheet and populates
wb.add_worksheet(name: t("lib.report.products_report")) do |sheet|
sheet.add_row [t("lib.report.alert_formulas"), "", "", "", "", "", "", "", "", "", ""], style: yellowcell
sheet.add_row [""]
sheet.merge_cells "A1:K1"
total_selled_sum = 0
total_parcelled_sum = 0
products_by_suppliers.each do |supplier, products|
next if supplier.blank?
sheet.add_row [t("lib.report.supplier"), "", t("lib.report.phone"), "", t("lib.report.mail"), "", "", "", "", "", ""], style: bluecell
sheet.merge_cells "A#{sbs}:B#{sbs}"
selled_sum = 0
parcelled_sum = 0
# sp = index of the start of the products list / ep = index of the end of the products list
sp = sbs + 3
ep = sp + products.count - 1
sheet.add_row [supplier.abbreviation_or_name, "", supplier.profile.contact_phone, "", supplier.profile.contact_email, "", "", "", "", "", ""],
style: default
sbe = sbs + 1
["A#{sbe}:B#{sbe}", "C#{sbe}:D#{sbe}", "E#{sbe}:F#{sbe}"].each { |c| sheet.merge_cells c }
sheet.add_row [
t("lib.report.product_cod"), t("lib.report.product_name"), t("lib.report.qty_ordered"),
t("lib.report.stock_qtt"), t("lib.report.min_stock"), t("lib.report.qtt_to_be_parcelled"), t("lib.report.projected_stock"),
t("lib.report.un"), t("lib.report.price_un"), t("lib.report.selled_value"), t("lib.report.value_parcel")
], style: greencell
# pl = product line
pl = sp
products.each do |product|
stock_qty_formula = "=IF(C#{pl}-D#{pl}+E#{pl}>0, C#{pl}-D#{pl}+E#{pl},0)"
stock_qty_value = product.quantity_ordered
stock_formula = "=D#{pl}-C#{pl}+F#{pl}"
stock_value = 0
unit = product.unit.singular rescue ""
total_price_formula = "=F#{pl}*I#{pl}"
total_price_value = product.quantity_ordered * product.price rescue 0
# FIXME: correct this calc for stock
selled_sum += total_price_value
parcelled_sum += total_price_value
sheet.add_row [product.id, product.name, product.quantity_ordered,
0, 0, stock_qty_formula, stock_formula,
unit, product.price, total_price_value, total_price_formula],
style: [default, default, default,
default, default, default, default,
default, currency, currency, currency],
formula_values: [nil, nil, nil,
nil, nil, stock_qty_value, stock_value,
nil, nil, nil, total_price_value]
pl += 1
end
total_selled_sum += selled_sum
total_parcelled_sum += parcelled_sum
sheet.add_row [t("lib.report.total_selled_value"), "", "=SUM(J#{sp}:J#{ep})",
t("lib.report.total_parcel_value"), "", "=SUM(k#{sp}:k#{ep})",
"", "", "", ""],
formula_values: [nil, nil, selled_sum,
nil, nil, parcelled_sum,
nil, nil, nil, nil],
style: [redcell, redcell, currency,
redcell, redcell, currency,
default, default, default, default]
row = ep + 1
["A#{row}:B#{row}", "D#{row}::#{row}"].each { |c| sheet.merge_cells c }
sheet.add_row [""]
sbs = ep + 3
end
sheet.add_row [t("lib.report.selled_total"), "=SUM(J1:J1000)", t("lib.report.parcelled_total"), "=SUM(K1:K1000)"],
style: [redcell, default, redcell, default],
formula_values: [nil, total_selled_sum, nil, total_parcelled_sum]
sheet.column_widths 11, 29, 13, 10, 12, 12, 12, 10, 10, 14, 14
end # closes spreadsheet
tmp_dir = Dir.mktmpdir "noosfero-"
report_file = tmp_dir + "/report.xlsx"
p.serialize report_file
report_file
end
def report_orders_by_consumer(orders)
p = Axlsx::Package.new
wb = p.workbook
# create styles
defaults = { fg_color: "000000", sz: 8, alignment: { horizontal: :left, vertical: :center, wrap_text: true }, border: 0 }
greencell = wb.styles.add_style(defaults.merge(bg_color: "00AE00", fg_color: "ffffff", b: true))
bluecell = wb.styles.add_style(defaults.merge(bg_color: "99CCFF", b: true))
default = wb.styles.add_style(defaults.merge(border: 0))
bluecell_b_top = wb.styles.add_style(defaults.merge(bg_color: "99CCFF", b: true, border: { style: :thin, color: "FF000000", edges: [:top] }))
date = wb.styles.add_style(defaults.merge(format_code: t("lib.report.mm_dd_yy_hh_mm_am_pm")))
currency = wb.styles.add_style(defaults.merge(format_code: t("number.currency.format.xlsx_currency")))
# border_top = wb.styles.add_style border: {style: :thin, color: "FF000000", edges: [:top]}
redcell = wb.styles.add_style bg_color: "E8D0DC", fg_color: "000000", sz: 8, b: true, wrap_text: true, alignment: { horizontal: :left }, border: 0
yellowcell = wb.styles.add_style bg_color: "FCE943", fg_color: "000000", sz: 9, b: true, wrap_text: true, alignment: { horizontal: :left }, border: 0
# create sheet and populates
wb.add_worksheet(name: t("lib.report.closed_orders")) do |sheet|
# supplier block start index (shifts on the loop for each supplier)
sbs = 3
sheet.add_row [t("lib.report.alert_formulas"), "", "", "", "", "", ""], style: yellowcell
sheet.add_row [""]
sheet.merge_cells "A1:G1"
productsStart = sbs + 5
productsEnd = 0
selled_sum = 0
total_price_without_margin = 0
orders.each do |order|
sheet.add_row [t("lib.report.order_code"), t("lib.report.member_name"), "", t("lib.report.phone"), "", t("lib.report.mail"), ""], style: bluecell_b_top
["B#{sbs}:C#{sbs}", "D#{sbs}:E#{sbs}", "F#{sbs}:G#{sbs}"].each { |c| sheet.merge_cells c }
sbs += 1
sheet.add_row [order.code, order.consumer_data[:name], "", order.consumer_data[:contact_phone], "", order.consumer_data[:email], ""], style: default
["B#{sbs}:C#{sbs}", "D#{sbs}:E#{sbs}", "F#{sbs}:G#{sbs}"].each { |c| sheet.merge_cells c }
sbs += 1
sheet.add_row [t("lib.report.created"), t("lib.report.modified"), t("lib.report.payment_method"), t("lib.report.delivery_option"), "", "", ""],
style: bluecell
["D#{sbs}:E#{sbs}"].each { |c| sheet.merge_cells c }
# sp = index of the start of the products list / ep = index of the end of the products list
sp = sbs + 3
productsEnd = ep = sp + order.items.count - 1
payment_method = _ OrdersPlugin::Order::PaymentMethods[order.payment_data[:method].to_sym].call rescue ""
sheet.add_row [order.created_at, order.updated_at, payment_method, order.supplier_delivery_data[:name], "", "", "", ""],
style: [date, date, default, default]
sbs += 1
sheet.add_row [t("lib.report.product_cod"), t("lib.report.supplier"), t("lib.report.product_name"),
t("lib.report.qty_ordered"), t("lib.report.un"), t("lib.report.price_un"), t("lib.report.value")], style: greencell
["D#{sbs}:E#{sbs}"].each { |c| sheet.merge_cells c }
sbe = sp
sum = 0
order.items.each do |item|
formula_value = item.price * item.status_quantity rescue 0
formula_value_s = CurrencyFields.localized_number(formula_value)
unit = item.product.unit.singular rescue ""
# for the case in which the item is aggregated by other products we chose to use the item idhave to
if item.supplier_products.size > 1
id = item.id
else
id = item.supplier_products.first.id rescue item.id
end
supplier_name = item.suppliers.first.abbreviation_or_name rescue item.order.profile.name
sheet.add_row [id, supplier_name,
item.name, item.status_quantity,
unit, item.product.price,
"=F#{sbe}*D#{sbe}"],
style: [default, default, default, default, default, currency, currency],
formula_values: [nil, nil, nil, nil, nil, nil, formula_value_s]
selled_sum += item.status_quantity * item.price rescue 0
total_price_without_margin += item.price_without_margins * item.status_quantity
sbe += 1
sum += formula_value
end # closes order.items.each
sheet.add_row ["", "", "", "", t("lib.report.total_value"), "=SUM(G#{sp}:G#{ep})", ""], style: [default] * 4 + [bluecell, currency, default],
formula_values: [nil, nil, nil, nil, nil, sum, nil]
sheet.add_row [""]
sbs = sbe + 2
end
sheet.add_row [t("lib.report.selled_total"), "", "=SUM(G#{productsStart}:G#{productsEnd})", t("lib.report.total_price_without_margin"), "", "", total_price_without_margin],
formula_values: [nil, nil, selled_sum, nil, nil, nil, nil],
style: [redcell, redcell, currency, redcell, redcell, redcell, currency]
["A#{sbs}:B#{sbs}", "D#{sbs}:F{sbs}"].each { |c| sheet.merge_cells c }
sheet.column_widths 15, 30, 30, 9, 8, 10, 11
end # closes spreadsheet
tmp_dir = Dir.mktmpdir "noosfero-"
report_file = tmp_dir + "/report.xlsx"
p.serialize report_file
report_file
end # closes def
end