Generate Voucher Sales Report

Purpose

This script exports voucher sales transactions to an XLSX report for a selected date.

Prerequisites

• Rails environment with production data access
• Gems: figaro, xlsxtream, faraday
• Env variable: GB_PRIMEPAY_SECRET_KEY

Configuration

Update these values before running:

• filepath output location (default /tmp/data4.xlsx)
• date report date

Execution

rails c --production
# paste and run script

Script

filepath = '/tmp/data4.xlsx'
date = Date.today - 1.day

tickets = Ticket.includes(:user, ticket_transaction: { restaurant: :translations }, ticket_group: :translations)
              .where('created_at >= ? AND created_at <= ?', date.beginning_of_day, date.end_of_day)

SECRET_KEY = Figaro.env.GB_PRIMEPAY_SECRET_KEY!

def api_client(secret_key = SECRET_KEY)
  @api_client ||= Faraday.new(::GB_PRIMEPAY_BASE_URL) do |conn|
    conn.headers['Content-Type'] = 'application/json; charset=utf-8'
    conn.headers['Authorization'] = "Basic #{Base64.strict_encode64("#{secret_key}:")}"
    conn.response :logger
    conn.adapter Faraday.default_adapter
  end
end

Xlsxtream::Workbook.open(filepath) do |xlsx|
  xlsx.write_worksheet('Voucher Report') do |sheet|
    sheet.add_row([
      'Transaction ID', 'Voucher Purchase Date', 'Customer Name', 'Voucher Package Name',
      'Total Voucher Value', 'Partner Name', 'Payment Status', 'Voucher Active Status',
      'Quota', 'GB Pay - ignore', 'Payment Type', 'Payment Gateway', 'Paid at Date',
      'Paid Time', 'phone', 'email', 'loyalty level'
    ])

    tickets.find_each do |ticket|
      customer_name = ticket.user.present? ? ticket.user.name : ticket.guest.name
      partner_name = ticket.ticket_transaction.restaurant&.name_en
      charges = ticket.ticket_transaction.charges.success_scope
      transaction_ids = charges.present? ? (charges.map(&:transaction_id).uniq.to_sentence.presence || '') : ''
      payment_type_provider = ticket.ticket_transaction.payment_type_provider

      sheet.add_row([
        ticket.ticket_transaction_id,
        ticket.created_at.in_time_zone('Asia/Bangkok'),
        customer_name,
        ticket.ticket_group.name_en,
        ticket.amount.amount,
        partner_name,
        ticket.ticket_transaction.status_property['status'],
        ticket.active ? 'Active' : 'Not Active',
        ticket.ticket_group.quantity,
        transaction_ids,
        payment_type_provider ? payment_type_provider.to_s.split('_').join(' ').titleize : '',
        'Hungry Hub',
        ticket.created_at.in_time_zone('Asia/Bangkok').strftime('%d/%m/%Y'),
        ticket.created_at.in_time_zone('Asia/Bangkok').strftime('%H:%M:%S'),
        ticket.user&.phone_v2_full,
        ticket.user&.email,
        ticket.user&.user_loyalty&.state
      ])
    end
  end
end

puts "Report generated at #{filepath}"