There was not much information about the city's premium gift certificate, so I tried to make it easier to use with LINE Bot without permission.

Introduction

I live in Kawaguchi City, Saitama Prefecture, and recently received a leaflet saying that I would issue a "gift certificate with premium". Roughly speaking, if you buy a gift certificate for 20,000 yen, you can buy 24,000 yen at a local shop. I thought this was quite convenient, so I accessed the homepage to find out what kind of store it can be used in, but there is no store list, and there is a direct link to a PDF that summarizes the store information. It was only. .. ..

This makes searching difficult. .. .. So, I made it into a LINE Bot without permission ・ Search by keyword ・ Search for the nearest available store from location information I tried to implement it so that it can be done.

What to explain in this article

Creating a Rails application Deploy to Heroku Preparing the LIEN Bot Implementation of LIENBot (Echolalia BOT) Read CSV file Implementation of simple search function

The detailed implementation will be divided into the second part.

environment

Ruby 2.6.6 Rails 6.0.3.3

Advance preparation

Install rbenv git installation heroku account registration Some tool that can convert PDF to CSV (I did it with Adobe Acrobat)

Creating a Rails application

First, create a directory for the application (If you use the method in this article, it will be the application name as it is, so let's create it after thinking)

$ mkdir kawaguchi_ticketl_inebot
$ cd kawaguchi_ticketl_inebot

I think that the version of ruby is not very old, but I will specify 2.6.6 for the time being.

$ rbenv install 2.6.6
$ rbenv local 2.6.6

Run bundle init to create a Gemfile

$ bundle init

Remove the Rails comment out of the created Gemfile and bundle install

$ bundle install --path=vendor/bundle

Since it is only used for LINE Bot, create a Rails application in api mode. I will make it with postgreql so that I can give it to heroku smoothly. You will be asked if you want to overwrite the Gemfile, but it's okay to overwrite it.

$ bundle exec rails new . --api -d postgresql
$ bundle exec rails db:create

Once you've done this, start the server and just check if you can access it. Check by accessing http: // localhost: 3000 /

$ bundle exec rails s

You can do it

(reference) This article was very easy to understand [Until a beginner publishes his own LINE bot in Ruby](https://qiita.com/taki-ikat/items/2a0187b30e4baca8b9c7#2-line-developers%E3%82%B3%E3%83%B3%E3 % 82% BD% E3% 83% BC% E3% 83% AB% E3% 81% A7% E3% 83% 81% E3% 83% A3% E3% 83% 8D% E3% 83% AB% E3% 82 % 92% E4% BD% 9C% E6% 88% 90) Manage ruby version with rbenv

Deploy to Heroku

You can do it later, but I'll push it to heroku once. If you haven't registered or set up herokub yet, please do so first.

$ heroku create
Creating app... done, ⬢ young-temple-xxxxxx
https://young-temple-xxxxxx.herokuapp.com/ | https://git.heroku.com/young-temple-xxxxxx.git
$ git add .
$ git commit -m 'first commit'
$ git push heroku master

Make a note of the URL that appears when you create heroku, as you'll use it later. (Here, https://young-temple-xxxxxx.herokuapp.com/)

You can give it to github etc., but skip it for the time being

(reference) Flow from pushing to github and giving to heroku

Preparation of LINE Bot

Channel registration

Refer to here https://developers.line.biz/ja/docs/messaging-api/getting-started/#using-console

Bot registration

Refer to here https://developers.line.biz/ja/docs/messaging-api/building-bot/

The Webhook URL cannot be set yet, so we will set it later.

Set the channel access token and channel secret to heroku's environment variables

$ heroku config:set LINE_CHANNEL_SECRET=xxxxxxxxxxxxxxxxxx
$ heroku config:set LINE_CHANNEL_TOKEN=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Implementation of LINE Bot

Add the following to the Gemfile

gem 'line-bot-api'

$ bundle install

Add routes

routes.rb

Rails.application.routes.draw do
  # For details on the DSL available within this file, see https://guides.rubyonrails.org/routing.html
  post '/callback' => 'webhook#callback'
end

Create a controller

$ rails g controller webhook  

First, let's make it according to the sample of https://github.com/line/line-bot-sdk-ruby

app/controllers/webhook_controller.rb

class WebhookController < ApplicationController
  require 'line/bot'

  def client
    @client ||= Line::Bot::Client.new { |config|
      config.channel_secret = ENV["LINE_CHANNEL_SECRET"]
      config.channel_token = ENV["LINE_CHANNEL_TOKEN"]
    }
  end

  def callback
    body = request.body.read

    signature = request.env['HTTP_X_LINE_SIGNATURE']
    unless client.validate_signature(body, signature)
      error 400 do 'Bad Request' end
    end

    events = client.parse_events_from(body)
    events.each do |event|
      case event
      when Line::Bot::Event::Message
        case event.type
        when Line::Bot::Event::MessageType::Text
          message = {
            type: 'text',
            text: event.message['text']
          }
          client.reply_message(event['replyToken'], message)
        when Line::Bot::Event::MessageType::Image, Line::Bot::Event::MessageType::Video
          response = client.get_message_content(event.message['id'])
          tf = Tempfile.open("content")
          tf.write(response.body)
        end
      end

      # Don't forget to return a successful response
      "OK"
    end
  end
end

I'll give it to heroku

$ git add .
$ git commit -m 'add controller'
$ git push heroku master

Set the URL displayed when performing heroku create as the Webhook URL referring to here https://developers.line.biz/ja/docs/messaging-api/building-bot/

Up to this point, "Aum Return Bot" is completed

Read CSV file

Since the bot created so far is just a parrot return bot, we will read the data and make it searchable.

This time I will read these CSV From here Converted the obtained PDF to CSV thing

Create a model for CSV to load

$ bundle exec rails g model store
Running via Spring preloader in process 76501
      invoke  active_record
      create    db/migrate/20200911182431_create_stores.rb
      create    app/models/store.rb
      invoke    test_unit
      create      test/models/store_test.rb
      create      test/fixtures/stores.yml

Edit migration file

db/migrate/20200911182431_create_stores.rb

class CreateStores < ActiveRecord::Migration[6.0]
  def change
    create_table :stores do |t|
      t.string :store_association_name, comment: 'Store name'
      t.string :store_name, comment: 'Store name'
      t.string :postal_code, comment: 'Postal code'
      t.string :address, comment: 'Street address'
      t.string :tel, comment: 'phone'
      t.string :lineup, comment: 'Product name'
      t.timestamps
    end
  end
end

Perform migration

$ bundle exec rake db:migrat

Now that we have the place to put the data, we will create a program to take in the CSV.

$ bundle exec rails g task import_csv
Running via Spring preloader in process 76763
      create  lib/tasks/import_csv.rake

lib/tasks/import_csv.rake

require 'csv'
namespace :import_csv do
  desc 'Import the PDF of the PDF issued by Kawaguchi City Shopping Street'
  task :store, ['file_name'] => :environment do |_, args|
    #Get the path of the file to import.
    #Since there are likely to be multiple file names, specify the file name when executing the task
    path = Rails.root.to_s + '/db/csv/' + args.file_name
    #Array for storing data to be imported
    list = []
    CSV.foreach(path, headers: true) do |row|
      list << {
          #Please adjust according to the header of the CSV to be imported.
          store_association_name: row['Store name'],
          store_name: row['Store name'],
          postal_code: row['Postal code'],
          address: row['Street address'],
          tel: row['phone'],
          lineup: row['Product name']
      }
    end
    puts 'Start import process'
    begin
      Store.create!(list)
      puts 'Import complete'
    rescue => exception
      puts 'Import failure'
      puts exception
    end
  end
end

Check if the task is registered

$ bundle exec rake -T
rake about                           # List versions of all Rails frameworks and the environment
rake action_mailbox:ingress:exim     # Relay an inbound email from Exim to Action Mailbox (URL and INGRESS_PASSWO...
rake action_mailbox:ingress:postfix  # Relay an inbound email from Postfix to Action Mailbox (URL and INGRESS_PAS...
rake action_mailbox:ingress:qmail    # Relay an inbound email from Qmail to Action Mailbox (URL and INGRESS_PASSW...
rake action_mailbox:install          # Copy over the migration
rake action_text:install             # Copy over the migration, stylesheet, and JavaScript files
rake active_storage:install          # Copy over the migration needed to the application
rake app:template                    # Applies the template supplied by LOCATION=(/path/to/template) or URL
rake app:update                      # Update configs and some other initially generated files (or use just updat...
rake db:create                       # Creates the database from DATABASE_URL or config/database.yml for the curr...
rake db:drop                         # Drops the database from DATABASE_URL or config/database.yml for the curren...
rake db:environment:set              # Set the environment value for the database
rake db:fixtures:load                # Loads fixtures into the current environment's database
rake db:migrate                      # Migrate the database (options: VERSION=x, VERBOSE=false, SCOPE=blog)
rake db:migrate:status               # Display status of migrations
rake db:prepare                      # Runs setup if database does not exist, or runs migrations if it does
rake db:rollback                     # Rolls the schema back to the previous version (specify steps w/ STEP=n)
rake db:schema:cache:clear           # Clears a db/schema_cache.yml file
rake db:schema:cache:dump            # Creates a db/schema_cache.yml file
rake db:schema:dump                  # Creates a db/schema.rb file that is portable against any DB supported by A...
rake db:schema:load                  # Loads a schema.rb file into the database
rake db:seed                         # Loads the seed data from db/seeds.rb
rake db:seed:replant                 # Truncates tables of each database for current environment and loads the seeds
rake db:setup                        # Creates the database, loads the schema, and initializes with the seed data...
rake db:structure:dump               # Dumps the database structure to db/structure.sql
rake db:structure:load               # Recreates the databases from the structure.sql file
rake db:version                      # Retrieves the current schema version number
rake import_csv:store[file_name]     #Import the PDF of the PDF issued by Kawaguchi City Shopping Street
rake log:clear                       # Truncates all/specified *.log files in log/ to zero bytes (specify which l...
rake middleware                      # Prints out your Rack middleware stack
rake restart                         # Restart app by touching tmp/restart.txt
rake secret                          # Generate a cryptographically secure secret key (this is typically used to ...
rake stats                           # Report code statistics (KLOCs, etc) from the application or engine
rake test                            # Runs all tests in test folder except system ones
rake test:db                         # Run tests quickly, but also reset db
rake test:system                     # Run system tests only
rake time:zones[country_or_offset]   # List all time zones, list by two-letter country code (`rails time:zones[US...
rake tmp:clear                       # Clear cache, socket and screenshot files from tmp/ (narrow w/ tmp:cache:cl...
rake tmp:create                      # Creates tmp directories for cache, sockets, and pids
rake yarn:install                    # Install all JavaScript dependencies as specified via Yarn
rake zeitwerk:check                  # Checks project structure for Zeitwerk compatibility

It seems that the task is registered.

Next, install the CSV file to be imported. Create a directory called csv under db and put the CSV file there If you have trouble preparing, get it from here

Execute the rake command for importing CSV

$ bundle exec rake import_csv:store['kawaguchi.csv']
Start import process
Import complete

Make sure you really have the data

$ bundle exec rails c
Running via Spring preloader in process 77369
Loading development environment (Rails 6.0.3.3)
irb(main):001:0> Store.all
  Store Load (0.7ms)  SELECT "stores".* FROM "stores" LIMIT $1  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Store id: 1, store_association_name: nil, store_name: "EK Auto Co., Ltd.", postal_code: "332-0025", address: "Haramachi 16-10", tel: "255-4980", lineup: "Vehicle inspection, sheet metal, general repair, new car, used car sales", created_at: "2020-09-11 18:37:12", updated_at: "2020-09-11 18:37:12">, #<Store id: 2, store_association_name: nil, store_name: "ACE-LAB", postal_code: "332-0034", address: "Namiki 3-3-19", tel: "287-9465", lineup: "Beauty salon", created_at: "2020-09-11 18:37:12", updated_at: "2020-09-11 18:37:12">...

It seems to be included.

Add a simple search function to the Store

app/models/store.rb

class Store < ApplicationRecord
  def self.search(txt)
    Store.where(lineup: txt)
    .or(Store.where(store_association_name: txt))
    .or(Store.where(store_name: txt)).limit(5)
  end

  def self.get_search_message(txt)
    stores = Store.search(txt)
    message = []
    stores.each do |s|
      message << s.store_name
    end
    message << 'There's no result found' if message.blank?
    message.join(', ')
  end
end

app/controllers/webhook_controller.rb

class WebhookController < ApplicationController
  require 'line/bot'

  def client
    @client ||= Line::Bot::Client.new { |config|
      config.channel_secret = ENV["LINE_CHANNEL_SECRET"]
      config.channel_token = ENV["LINE_CHANNEL_TOKEN"]
    }
  end

  def callback
    body = request.body.read

    signature = request.env['HTTP_X_LINE_SIGNATURE']
    unless client.validate_signature(body, signature)
      error 400 do 'Bad Request' end
    end

    events = client.parse_events_from(body)
    events.each do |event|
      case event
      when Line::Bot::Event::Message
        case event.type
        when Line::Bot::Event::MessageType::Text
          message = {
            type: 'text',
            #Correct ↓
            text: Store.get_search_message(event.message['text'])
          }
          client.reply_message(event['replyToken'], message)
        when Line::Bot::Event::MessageType::Image, Line::Bot::Event::MessageType::Video
          response = client.get_message_content(event.message['id'])
          tf = Tempfile.open("content")
          tf.write(response.body)
        end
      end

      # Don't forget to return a successful response
      "OK"
    end
  end
end

It's too easy, but let's give it to heroku once as we will fill in the details in the second part

$ git add .
$ git commit -m 'easy search'
$ git push heroku master

You need to do what you did in your own environment in heroku's environment.

$ heroku run rake db:migrate
$ heroku run rake import_csv:store['kawaguchi.csv']

Now you have a bot that returns search results