 $:.unshift File.dirname(__FILE__)  # ロードパスにカレントディレクトリを追加
require 'sinatra/base'
require "line/bot"
require "date"
require "rexml/document"
require "json"
require 'nkf'
require 'time'
require "fileutils"
require "rmagick"
require 'open-uri'
require 'uri'
require 'csv'
require '/var/www/lib/aws.rb'
require '/var/www/lib/userpostgres.rb'
require '/var/www/lib/appexeExcelDoc.rb'
require 'httparty'

#=====================================
# queue用メソッド
#=====================================

#=====================================
# queue：config
#=====================================

def set_dbname
  return "gifuroge"
end

def set_commonbase # 画像やスコアボードを保存している場所
  return "/var/www/html/images/gifuRoge"
end

def set_commonbase2 # 画像やスコアボードを保存している場所をアドレスにしたもの
  return "/var/www/html/images/gifuRoge"
  #return "https://rogaining.sumasen.net/images/gifuRoge"
end

def set_originbase # 汎用的な画像を格納している場所。基本的に弄らなくていいはず
  return "/var/www/html/images/gifuRoge/asset"
  #return "https://rogaining.sumasen.net/images/gifuRoge/asset"
end

def queue_error_log_base # キューでエラーが出た時にそれを格納するテキストファイルの場所
  return "/home/ubuntu/gifuRogePrintQueError.log"
end

def s3_key
  return "AKIA6LVMTADSVEB5LZ2H"
end

def s3_Skey
  return "KIbm47dqVBxSmeHygrh5ENV1uXzJMc7fLnJOvtUm"
end

def s3_bucket
  return "sumasenrogaining"
end

def s3_region
  return "us-west-2"
end

def s3_domain
  return "https://sumasenrogaining.s3.us-west-2.amazonaws.com"
end

def get_team_name(zekken_number, event_code)
  @pgconn = UserPostgres.new
  dbname = set_dbname()
  @pgconn.connectPg("localhost", "mobilous", 0, dbname)
  team_table = UserPostgresTable.new
  team_table.useTable(@pgconn, 'team_table')

  where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"
  record = team_table.find2(where).first  # 期待するのは1レコードだけなので、firstを使って最初のものを取得
  
  if record
    return record["team_name"]
  else
    return nil  # team_nameが見つからない場合
  end
end

def get_class_name(zekken_number, event_code)
  @pgconn = UserPostgres.new
  dbname = set_dbname()
  @pgconn.connectPg("localhost", "mobilous", 0, dbname)
  team_table = UserPostgresTable.new
  team_table.useTable(@pgconn, 'team_table')

  where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"
  record = team_table.find2(where).first  # 期待するのは1レコードだけなので、firstを使って最初のものを取得

  if record
    return record["class_name"]
  else
    return nil  # team_nameが見つからない場合
  end
end

def get_unique_event_codes(pgconn)
  anytable = UserPostgresTable.new
  anytable.useTable(pgconn, 'gps_information')
  records = anytable.find2(nil, nil, "DISTINCT event_code")
  records.map { |row| row["event_code"] }
end

def get_top_users(pgconn, event_code, class_name)
  anytable = UserPostgresTable.new
  anytable.useTable(pgconn, 'gps_information')
  
  # テーブル構造に合わせてクエリを修正
  where_clause = "event_code = '#{event_code}'"
  order_clause = "zekken_number ASC, create_at DESC"
  
  records = anytable.find2(where_clause, order_clause)
  
  # クラス名でフィルタリング（データベースに class_name カラムがない場合）
  filtered_records = records.select { |r| r["class_name"] == class_name }
  
  # Simulate DISTINCT ON by filtering in Ruby
  top_users = filtered_records.group_by { |r| r["zekken_number"] }
                              .map { |_, user_records| user_records.first }
                              .first(3)
  
  top_users.map do |user|
    {
      "zekken_number" => user["zekken_number"],
      "team_name" => user["team_name"],
      "class_name" => class_name,
      "colabo_company_memo" => user["colabo_company_memo"]
    }
  end
end

def get_top_users_by_class(pgconn, event_code)
  gps_table = UserPostgresTable.new
  gps_table.useTable(pgconn, 'gps_information')
  
  checkpoint_table = UserPostgresTable.new
  checkpoint_table.useTable(pgconn, 'checkpoint_table')
  
  team_table = UserPostgresTable.new
  team_table.useTable(pgconn, 'team_table')

  gps_records = gps_table.find2("event_code = '#{event_code}' AND colabo_company_memo = 'FC岐阜'")
  checkpoint_records = checkpoint_table.find2("event_code = '#{event_code}'")
  team_records = team_table.find2("event_code = '#{event_code}'")

  checkpoint_data = checkpoint_records.group_by { |r| r["cp_number"] }
  team_data = team_records.group_by { |r| r["zekken_number"] }

  user_data = gps_records.group_by { |r| r["zekken_number"] }.map do |zekken_number, records|
    team_info = team_data[zekken_number]&.first || {}
    
    daily_scores = calculate_daily_scores(records, checkpoint_data)
    best_score = daily_scores.max_by { |_, score| score[:point] }

    {
      "zekken_number" => zekken_number,
      "event_code" => event_code,
      "team_name" => team_info["team_name"],
      "class_name" => team_info["class_name"],
      "best_score" => best_score[1][:point],
      "best_cp_count" => best_score[1][:cp_count],
      "best_late_point" => best_score[1][:late_point],
      "best_overtime_penalty" => best_score[1][:overtime_penalty],
      "best_duration_minutes" => best_score[1][:duration_minutes],
      "last_checkin" => best_score[1][:end_time],
      "daily_scores" => daily_scores.transform_keys { |k| k.to_s }
    }
  end

  user_data.group_by { |u| u["class_name"] }.transform_values do |users|
    users.sort_by { |u| [-u["best_score"], -u["best_cp_count"]] }.first(3)
  end
end

def get_all_users_by_class(pgconn, event_code)
  gps_table = UserPostgresTable.new
  gps_table.useTable(pgconn, 'gps_information')
  
  checkpoint_table = UserPostgresTable.new
  checkpoint_table.useTable(pgconn, 'checkpoint_table')
  
  team_table = UserPostgresTable.new
  team_table.useTable(pgconn, 'team_table')

  gps_records = gps_table.find2("event_code = '#{event_code}' AND colabo_company_memo = 'FC岐阜'")
  checkpoint_records = checkpoint_table.find2("event_code = '#{event_code}'")
  team_records = team_table.find2("event_code = '#{event_code}'")

  checkpoint_data = checkpoint_records.group_by { |r| r["cp_number"] }
  team_data = team_records.group_by { |r| r["zekken_number"] }

  user_data = gps_records.group_by { |r| r["zekken_number"] }.map do |zekken_number, records|
    team_info = team_data[zekken_number]&.first || {}
    
    daily_scores = calculate_daily_scores(records, checkpoint_data)
    best_score = daily_scores.max_by { |_, score| score[:point] }

    {
      "zekken_number" => zekken_number,
      "event_code" => event_code,
      "team_name" => team_info["team_name"],
      "class_name" => team_info["class_name"],
      "best_score" => best_score[1][:point],
      "best_cp_count" => best_score[1][:cp_count],
      "best_late_point" => best_score[1][:late_point],
      "best_overtime_penalty" => best_score[1][:overtime_penalty],
      "best_duration_minutes" => best_score[1][:duration_minutes],
      "last_checkin" => best_score[1][:end_time],
      "daily_scores" => daily_scores.transform_keys { |k| k.to_s }
    }
  end

  user_data.group_by { |u| u["class_name"] }.transform_values do |users|
    users.sort_by { |u| [-u["best_score"], -u["best_cp_count"]] }
  end
end

def calculate_daily_scores(records, checkpoint_data)
  records.group_by { |r| Date.parse(r["create_at"]) }.transform_values do |day_records|
    photo_point = 0
    buy_point = 0
    late_point = 0
    cp_count = 0

    # 開始時間と終了時間を特定
    start_time = Time.parse(day_records.min_by { |r| r["create_at"] }["create_at"])
    end_time = Time.parse(day_records.max_by { |r| r["create_at"] }["create_at"])

    day_records.each do |r|
      cp_info = checkpoint_data[r["cp_number"]]&.first || {}
      
      if r["cp_number"].to_i > 0
        cp_count += 1
        
        if !r["buy_flag"] && !r["minus_photo_flag"]
          photo_point += cp_info["photo_point"].to_i
        end

        if r["buy_flag"]
          buy_point += cp_info["buy_point"].to_i
        end
      end

      late_point += r["late_point"].to_i
    end

    # 超過時間のペナルティ計算
    duration_seconds = (end_time - start_time).to_i
    overtime_seconds = [duration_seconds - 18000, 0].max  # 5時間 = 18000秒
    
    overtime_penalty = if overtime_seconds > 0
                         base_penalty = 50  # 1秒でも超えたら50点引く
                         additional_penalty = (overtime_seconds / 60.0).floor * 50  # 1分ごとに追加で50点引く
                         base_penalty + additional_penalty
                       else
                         0
                       end

    point = photo_point + buy_point - late_point - overtime_penalty
    
    {
      point: point,
      cp_count: cp_count,
      late_point: late_point,
      overtime_penalty: overtime_penalty,
      start_time: start_time.strftime("%Y-%m-%d %H:%M:%S"),
      end_time: end_time.strftime("%Y-%m-%d %H:%M:%S"),
      duration_minutes: (duration_seconds / 60.0).ceil
    }
  end
end

# get_top_users_by_class メソッドは変更なし

def calculate_points(records, checkpoint_data)
  photo_point = 0
  buy_point = 0
  late_point = 0
  cp_count = 0

  records.each do |r|
    cp_info = checkpoint_data[r["cp_number"]]&.first || {}
    
    if r["cp_number"].to_i > 0
      cp_count += 1
      
      if !r["buy_flag"] && !r["minus_photo_flag"]
        photo_point += cp_info["photo_point"].to_i
      end

      if r["buy_flag"]
        buy_point += cp_info["buy_point"].to_i
      end
    end

    late_point += r["late_point"].to_i
  end

  point = photo_point + buy_point - late_point
  
  [point, cp_count, late_point]
end

def calculate_user_scores(pgconn, zekken_number, event_code)
  gps_table = UserPostgresTable.new
  gps_table.useTable(pgconn, 'gps_information')

  today = Date.today
  start_date = Date.new(today.year, today.month, 1)
  end_date = Date.new(today.year, today.month, -1)  # 月末日

  where_clause = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}' " \
                 "AND create_at >= '#{start_date}' AND create_at <= '#{end_date}' " \
                 "AND colabo_company_memo = 'FC岐阜'"
  
  records = gps_table.find2(where_clause)

  total_score = records.count { |r| r["cp_number"].to_i > 0 }
  last_checkin = records.map { |r| r["create_at"] }.max

  {
    "best_score" => total_score,
    "total_score" => total_score,
    "last_checkin" => last_checkin
  }
end

##移動経路画像作成

# 1. 上位ユーザーの移動データを取得する関数
def get_top_users_routes(pgconn, event_code, class_name, limit = 3)
  anytable = UserPostgresTable.new
  anytable.useTable(pgconn, 'gps_information')
  
  top_users = get_top_users(pgconn, event_code, class_name)
  
  routes = {}
  top_users.each do |user|
    zekken_number = user["zekken_number"]
    where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}' ORDER BY create_at ASC"
    route = anytable.find2(where)
    routes[zekken_number] = route.map do |point|
      {
        latitude: point['latitude'].to_f,
        longitude: point['longitude'].to_f,
        timestamp: point['create_at']
      }
    end
  end
  
  routes
end

# 2. 移動経路を描画する関数
def draw_route(image, route, color)
  draw = Magick::Draw.new
  draw.stroke(color)
  draw.stroke_width(2)
  
  points = route.map do |point|
    [point['longitude'].to_f, point['latitude'].to_f]
  end
  
  draw.polyline(*points.flatten)
  draw.draw(image)
end

# 3. 画像生成と保存を行う関数
def generate_route_image(routes, event_code)
  width = 800
  height = 600
  image = Magick::Image.new(width, height) { self.background_color = 'white' }
  
  # ルートの座標を正規化するための関数
  normalize = lambda do |val, min, max|
    (val - min) / (max - min)
  end
  
  # すべてのルートの座標範囲を取得
  all_points = routes.values.flatten
  min_lat = all_points.map { |p| p['latitude'].to_f }.min
  max_lat = all_points.map { |p| p['latitude'].to_f }.max
  min_lon = all_points.map { |p| p['longitude'].to_f }.min
  max_lon = all_points.map { |p| p['longitude'].to_f }.max
  
  colors = ['red', 'blue', 'green']
  routes.each_with_index do |(zekken_number, route), index|
    normalized_route = route.map do |point|
      x = normalize.call(point['longitude'].to_f, min_lon, max_lon) * width
      y = height - normalize.call(point['latitude'].to_f, min_lat, max_lat) * height
      [x, y]
    end
    
    draw_route(image, normalized_route, colors[index])
  end
  
  filename = "top_routes_#{event_code}_#{Time.now.strftime('%Y%m%d%H%M%S')}.png"
  image.write(filename)
  filename
end



#=====================================
# queue:util
#=====================================

def to_boolean str
  str.to_s.downcase == "true"
end

#=====================================
# queue：DB
#=====================================

# SELECT

def getFinalReport zekken_number, event_code

  @pgconn=UserPostgres.new
  dbname = set_dbname()
  @pgconn.connectPg("localhost","mobilous",0,dbname)

  anytable=UserPostgresTable.new
  anytable.useTable( @pgconn,'report_list' )

  where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

  list = anytable.find2(where)
  result = ""

  list.each { |rec|
    result = rec["report_address"]
  }

  @pgconn.disconnect

  if result == "" || result == nil then
    return "no report"
  end

  return result

end

def getGpsInfo zekken_number, event_code

  @pgconn=UserPostgres.new
  dbname = set_dbname()
  @pgconn.connectPg("localhost","mobilous",0,dbname)

  anytable=UserPostgresTable.new
  anytable.useTable( @pgconn,'gps_information' )

  where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}' ORDER BY serial_number"
  p "where : #{where}"
  list = anytable.find2(where)
  result = []
  p "list : #{list}"
  if list == nil then
    @pgconn.disconnect
    return []
  end

  count = 0

  list.each { |rec|
    result[count] = {"cp_number" => rec['cp_number']}
    count += 1
  }

  @pgconn.disconnect

  if result != "" && result != nil then
    return result
  else
    return []
  end
end

def getPhotoFromRR zekken_number, event_code, num #RR => RouteResult
  @pgconn=UserPostgres.new
  dbname = set_dbname()
  @pgconn.connectPg("localhost","mobilous",0,dbname)

  if num <= 60
    table = 'route_result'
  else
    table = 'route_result_2'
  end

  anytable=UserPostgresTable.new
  anytable.useTable( @pgconn,table )

  where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

  list = anytable.find2(where)
  result = {}

  if list == nil then
    return {"result" => "ERROR"}
  end

  docpath = set_commonbase() + '/' + event_code + '/' + zekken_number

  if( !Dir.exist?(docpath) )then
    if( Dir.mkdir( docpath,0755 ) == 0 ) then
    end
  end

  list.each { |rec|
    for i in 1..num do
      begin
        if rec["cp#{i}_ph"].include?(s3_domain()) then
          extension = result["cp#{i}_ph"].to_s.split('.').last
          result["cp#{i}_ph"] = rec["cp#{i}_ph"].gsub(s3_domain(),set_commonbase()).gsub(".#{extension}","_th.#{extension}")
        elsif rec["cp#{i}_ph"].include?(set_commonbase2()) then
          result["cp#{i}_ph"] = rec["cp#{i}_ph"].gsub(set_commonbase2(),set_commonbase())
        else
          result["cp#{i}_ph"] = "#{set_commonbase()}/asset/photo_none.png"
        end
      rescue => e
        p "error : #{e}, result[cp#{i}_ph] :#{result["cp#{i}_ph"]}"
        result["cp#{i}_ph"] = "#{set_commonbase()}/asset/photo_none.png"
      end
      if result["cp#{i}_ph"].include?("asset/asset") then
        result["cp#{i}_ph"].gsub!('asset/asset', 'asset')
      end
      if File.exist?(result["cp#{i}_ph"]) == false then
        begin
          filename = rec["cp#{i}_ph"].split('/').last
          fullpath = docpath + '/' + filename
          fullpath2 = s3_domain + '/' + URI.encode_www_form_component(event_code, enc=nil) + '/' + zekken_number + '/' + filename
          URI.open(fullpath2) do |image|
            File.open(fullpath, 'w') do |file|
              file.write(image.read)
            end
          end
          extension = filename.split('.').last

          filenameTh = filename.gsub(".#{extension}","_th.#{extension}")

          imageOri = Magick::Image.read(fullpath).first
          imageTh = imageOri.scale(image_size_width(), image_size_height())
          imageTh.write("temp/#{filenameTh}")

          nextpath = docpath + '/' + filenameTh

          FileUtils.mv("/var/www/temp/#{filenameTh}",nextpath)
          # File.delete(fullpath)

          result["cp#{i}_ph"] = nextpath
        rescue => e
          p "error : #{e}, rec[cp#{i}_ph] :#{rec["cp#{i}_ph"]}"
          result["cp#{i}_ph"] = "#{set_commonbase()}/asset/photo_none.png"
        end
      end

      begin
        result["cp#{i}_p"] = rec["cp#{i}_p"]
      rescue => e
        p "error : #{e}, result[cp#{i}_p] : #{result["cp#{i}_p"]}"
      end

      begin
        result["cp#{i}_s"] = rec["cp#{i}_s"].gsub(set_commonbase2(),set_commonbase())
      rescue => e
        p "error : #{e}, result[cp#{i}_s] : #{result["cp#{i}_s"]}"
        result["cp#{i}_s"] = "#{set_commonbase()}/asset/photo_none.png"
      end
      if result["cp#{i}_s"].include?("asset/asset") then
        result["cp#{i}_s"].gsub!('asset/asset', 'asset')
      end
      if File.exist?(result["cp#{i}_s"]) == false then
        result["cp#{i}_s"] = "#{set_commonbase()}/asset/photo_none.png"
      end
    end
  }

  @pgconn.disconnect

  if result == [] || result == nil then
    return {"result" => "ERROR"}
  end

  result["result"] = "OK"

  return result
end

def getPrintQueueMemory

  @pgconn=UserPostgres.new
  dbname = set_dbname()
  @pgconn.connectPg("localhost","mobilous",0,dbname)

  anytable=UserPostgresTable.new
  anytable.useTable( @pgconn,'print_queue_memory' )

  where = "0 = 0"

  list = anytable.find2(where)
  result = []

  list.each { |rec|
    result.push(rec)
  }

  @pgconn.disconnect

  return result

end

# INSERT

def inputFinalReport zekken_number, event_code, pdf_address
  @pgconn=UserPostgres.new
  dbname = set_dbname()
  @pgconn.connectPg("localhost","mobilous",0,dbname)

  anytable=UserPostgresTable.new
  anytable.useTable( @pgconn,'report_list' )

  title = ["zekken_number", "event_code", "report_address"]
  record = {"zekken_number" => zekken_number, "event_code" => event_code, "report_address" => pdf_address}

  sql = anytable.makeInsertKeySet(title,record)
  begin
    ret = anytable.exec(sql)         # true or false
  rescue => error
    p error
  end

  @pgconn.disconnect

  return "200 OK"
end

# UPDATE

def changeFinalReport zekken_number, event_code, pdf_address

  @pgconn=UserPostgres.new
  dbname = set_dbname()
  @pgconn.connectPg("localhost","mobilous",0,dbname)

  rec = {"report_address" => pdf_address}
  where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

  begin
    @pgconn.update("report_list",rec,where)
  rescue => e
    p e

    @pgconn.disconnect

    return "UPDATE ERROR"
  end

  @pgconn.disconnect

  return "OK"

end

def removeQueueMemory

  @pgconn=UserPostgres.new
  dbname = set_dbname()
  @pgconn.connectPg("localhost","mobilous",0,dbname)

  anytable=UserPostgresTable.new
  anytable.useTable( @pgconn,'print_queue_memory' )

  where = "serial_number = (SELECT min(serial_number) FROM print_queue_memory)"

  begin
    p anytable.remove(where)

    @pgconn.disconnect

    return "200 OK"
  rescue => e
    p e
    @pgconn.disconnect
    return "200 OK"
  end

end

#=====================================
# queue：s3
#=====================================

def s3Uploader data_dir, filepath, filename

  bucket = s3_bucket().freeze
  region = s3_region().freeze
  access_key = s3_key().freeze
  secret_key = s3_Skey().freeze

  aws_storage = S3_StorageUtil.new( access_key,secret_key,region,bucket,data_dir )
  aws_bucket = S3_FolderUtil.new( "offlineRecog/work",aws_storage )

  originPath = filepath
  destPath = data_dir
  filename = filename

  aws_bucket.uploadFile( originPath,destPath,filename )

  aws_url = s3_domain() + '/' + data_dir + '/' + filename

  return aws_url

end

#=====================================
# queue：scoreboard
#=====================================

def makeScoreboard zekken_number, event_code, reprintF = false, budleF = false
  p "makeScoreboard"
  filepath = getScoreboardGeneral(zekken_number, event_code)
  p "return filepath"
  if filepath == "no_data" then
    p "no data error"
    return "no data error"
  end

  if reprintF then
    date_memory = DateTime.now.to_s.gsub(':','-').gsub('+','_')
    nextpath = set_commonbase() + "/#{event_code}" + "/scoreboard/#{zekken_number}_scoreboard_" + date_memory + ".xlsx"
  else
    nextpath = set_commonbase() + "/#{event_code}" + "/scoreboard/#{zekken_number}_scoreboard.xlsx"
  end
  p "nextpath set"
  if( !Dir.exist?(set_commonbase() + "/#{event_code}" + "/scoreboard") )then
    if( Dir.mkdir( set_commonbase() + "/#{event_code}" + "/scoreboard",0755 ) == 0 ) then
      #p "mkdir #{commonbase}"
    end
  end
  p "dir set"
  puts "filepath=#{filepath},nextpath=#{nextpath}"
  FileUtils.mv(filepath,nextpath)
  p "file move"
  docpath_user = set_commonbase() + "/#{event_code}" + "/scoreboard"
  pdffile = "#{zekken_number}_scoreboard"
 
  puts "PDF file will be #{pdffile}_#{date_memory} ..." 
  if reprintF then
    pdffile = pdffile + '_' + date_memory
  end
  p "bofore PDF"
  command ="/home/mobilousInstance/jodconverter-cli-4.4.5-SNAPSHOT/bin/jodconverter-cli -o #{docpath_user}/#{pdffile}.xlsx #{docpath_user}/#{pdffile}.pdf" #郡上のイベント後はこちらの仕様に置き換える
  system( command )
  puts command
  #system("echo 'sumasen@123' |sudo -S /var/www/lib/generatePDF.sh #{docpath_user} #{pdffile}")
  p "after PDF"
  aws_url = s3Uploader("#{event_code}/scoreboard", docpath_user, pdffile + '.pdf')
  p "s3 upload complete"
  if getFinalReport(zekken_number, event_code) == "no report" then
    inputFinalReport(zekken_number, event_code, aws_url)
  else
    changeFinalReport(zekken_number, event_code, aws_url)
  end
  p "report complete"
  # File.delete(nextpath)
  if budleF == false then
    # File.delete(nextpath.gsub('.xlsx','.pdf'))
    return nextpath.gsub('.xlsx','.pdf')
  end
end

def getScoreboardGeneral zekken_number, event_code
  p "getScoreboardGeneral"
  #usernameエラーで引っかかる
  #
  #$bean.read_bean
  #username = $bean.username
  username = 'test'
  record = {}

  record["zekken_number"] = zekken_number
  p "zekken_number : #{zekken_number}, event_code : #{event_code}"
  result = getGpsInfo(zekken_number, event_code)

  if result  == [] then
    p "getGPSInfo no_data"
    return "no_data"
  end

  count = 0

  result.each { |rec|
    count += 1
  }

  result2 = getPhotoFromRR(zekken_number, event_code, count)

  if result2 == {} || result2["result"] == "ERROR" then
    p "getPhotoFromRP no_data"
    return "no_data"

  end

  record.merge!(result2)

  if count <= 60 then
    document = "scoreboardGeneral"
  else
    document = "scoreboardGeneral2"
  end

  count = count * 2 + 1

  record["num_image"] = count
  #return record.to_s #昼休み後ここから
  ExcelObject.resetClass

  doc = AppExeExcelDoc.new
  #doc = AppExeGeneralDocument.new
  doc.init( username,0,document,"jp","/var/www/docbase","/home/mobilousInstance" )	# <== "/home/mobilousInstance" を出力先のベースフォルダーにしてください。

  pgconn=UserPostgres.new
  dbname = set_dbname()
  pgconn.connectPg("localhost","mobilous",0,dbname)

  rec = JSON.parse( JSON.generate(record) )
  #return rec 
  #@sheetname = sheet
  #filename = doc.makeReport( pgconn,rec,"outfile.xlsx",0 )	# ==> Use ini file defnition 2021-11-15
  doc.makeReport( pgconn,rec )

  # make a excel file under /var/www/docbase/(@username)/(@projectno)/

  #file_path = filename.to_s.gsub(/wrote excel file on /,"")
  #puts "=========> file_path = #{file_path}"
  pgconn.disconnect
  filename = doc.outfile
  file_path = doc.outpath
  puts "=========> file_path = #{file_path}, filename = #{filename}"

  return file_path

end

#====================================
# キューイング処理本体
#====================================

$queue = Queue.new

Thread.new do
  loop do
    begin
      item = JSON.parse($queue.pop)
      p "queue pop : #{item}"
      pdf_path = makeScoreboard(item["zekken_number"],item["event_code"],to_boolean(item["reprintF"]),false)

      # 印刷確認処理
      if pdf_path && File.exist?(pdf_path)
        print_command = "lpr -P scoreboard_printer #{pdf_path}"
        result = system(print_command)
        puts "印刷結果: #{result ? '成功' : '失敗'}"
      end

      result = removeQueueMemory()
      if result == "delete error" then
        timestamp = Time.now.strftime("%Y-%m-%d %H:%M:%S")
        item_value = defined?(item) ? item.to_s : ""
        error_message = "#{timestamp}=> #{item_value} : queue memory delete fail\n"
        File.open(queue_error_log_base(), "a") do |file|
          file.write(error_message)
        end
      end
    rescue => e
      timestamp = Time.now.strftime("%Y-%m-%d %H:%M:%S")
      item_value = defined?(item) ? item.to_s : ""
      error_message = "#{timestamp}=> #{item_value} : #{e.message}\n"
      File.open(queue_error_log_base(), "a") do |file|
        file.write(error_message)
      end
    end
  end
end

data = getPrintQueueMemory()
p "data : #{data}"
data.each { |rec|
  json = JSON.parse(rec["queue_detail"])

  jhash = {"zekken_number" => json["zekken_number"], "event_code" => json["event_code"], "reprintF" => json["reprintF"]}

  new_json = JSON.generate(jhash)

  p "queue add : #{new_json}"

  $queue << new_json
}

#====================================
# sinatraここから
#====================================

module Sinatra
    module MobServer_gifuroge
        module Helpers



#====================================
# utils
#====================================

    def crossdomain
      headers 'Access-Control-Allow-Origin' => '*'
    end

    def headjson
      headers 'Content-Type' => 'application/json; charset=utf-8'
    end

#====================================
# config
#====================================

    def client
      # p "boot client method!"
      @client ||= Line::Bot::Client.new { |config|
        config.channel_id = "1657548864"
        config.channel_secret = "01846a8d9d1832e55626098fca072e05"
        config.channel_token = "MGv4aes9X9aTDBBv7ocR8jgine8ct7f4XpLreUt6qg8/WuEhTWhNJTa1vlbJIVOxvWTGR4SBpECM8SkccZCGF4rfrQxsATf20AK5/QNZlXZ6rh8J1R3Sth8yaX1mMNIa1ZJMvw2whdVSe90ZdywZsgdB04t89/1O/w1cDnyilFU="
      }
    end

    def set_dbname
      return "gifuroge"
    end

    def set_commonbase # 画像やスコアボードを保存している場所
      return "/var/www/html/images/gifuRoge"
    end

    def set_commonbase2 # 画像やスコアボードを保存している場所をアドレスにしたもの
      return "https://rogaining.sumasen.net/images/gifuRoge"
    end

    def set_originbase # 汎用的な画像を格納している場所。基本的に弄らなくていいはず
      return "https://rogaining.sumasen.net/images/gifuRoge/asset"
    end

    def set_rogSimBase #ロゲイニングシミュレーターの位置
      return "/home/mobilousInstance/rogaining/rogaining"
    end

    def location_threshold # 位置情報の閾値
      return 30
    end

    def special_threshold
      return 100
    end

    def latitude_1m # 1mあたりの緯度
      return 0.000008983148616
    end

    def longitude_1m # 1mあたりの経度
      return 0.000010966382364
    end

    def image_size_width
      return 150
    end

    def image_size_height
      return 105
    end

    def s3_key
      return "AKIA6LVMTADSVEB5LZ2H"
    end

    def s3_Skey
      return "KIbm47dqVBxSmeHygrh5ENV1uXzJMc7fLnJOvtUm"
    end

    def s3_bucket
      return "sumasenrogaining"
    end

    def s3_region
      return "us-west-2"
    end

    def s3_domain
      return "https://sumasenrogaining.s3.us-west-2.amazonaws.com"
    end

    def latitude_threshols mode = nil
      if mode == "special"
        return special_threshold() * latitude_1m()
      end
      return location_threshold() * latitude_1m()
    end

    def longitude_threshols mode = nil
      if mode == "special"
        return special_threshold() * longitude_1m()
      end
      return location_threshold() * longitude_1m()
    end

#====================================
# text
#====================================

    def text_importantError errorCode
      return "エラーコード：#{errorCode}\nゼッケン番号とエラーコードを添えて、運営に問い合わせて下さい。"
    end

    # エラーコード一覧
    # STA-001 : パスワード入力のキャンセルに失敗した状態。応急対応：運営側で当該ユーザーIDのchat_statusレコードを削除。
    # STA-002 : 処理には成功したがchat_statusのレコードデリートに失敗した状態。応急対応：処理には成功していることを伝えた上で、運営側で当該ユーザーIDのchat_statusレコードを削除。
    # STA-003 : 写真受付のキャンセルに失敗した状態。応急対応：運営側で当該ユーザーのchat_statusレコードを削除
    # USR-001 : LINEのユーザー名を取得できていない状態。応急対応：エラーを無視してゼッケン番号を登録してもらう。ゼッケン番号とユーザーIDの紐付けが出来たら、手動でユーザー名をuser_tableに追加する。
    # USR-002 : ログインに成功したが、user_tableのレコードアップデートに失敗した状態。応急対応：ログを確認し、実際にログインに成功しているか検証した上で、手動でゼッケン番号とチーム名をuser_tableに追加する。
    # USR-003 : ログアウトの処理に失敗した。応急対応：LINEのアカウント名を聞き取り、手動でuser_tableからteam_nameとzekken_nameの中身を消去する。
    # DBS-001 : そのユーザーが何時間部門のユーザーなのかを認識できなくなった状態。データベースに何かしらの問題が生じていることが考えられるため、竜一に連絡

    def text_zekkenRegister zekken_number
      return "#{zekken_number}の代表登録を受け付けました。\n確認のためパスワードを入力し、送信して下さい。"
    end

    def text_teamRegister user_name, team_name
      return "#{user_name}さんをチーム：#{team_name}の代表として登録しました。"
    end

    def text_noZekken1 user_name
      return "#{user_name}さんは現在チームの代表として登録されていません。\nあなたがチームの代表であるなら、"
    end

    def text_noZekken2
      return "ゼッケン番号：[ゼッケン番号]"
    end

    def text_noZekken3
      return "の形でゼッケン番号を登録して下さい。"
    end

    def text_noZekkenNumber
      return "入力されたゼッケン番号は存在しません。タイプミスがないか確認し、再度送信して下さい。"
    end

    def text_alreadyZekken
      return "ログインに成功しましたが、入力されたゼッケン番号のチームは既に代表が登録されています。\nあなたが代表で他の方が代表登録してしまっている場合、その方に「ログアウト」と入力して貰ってから再度パスワードを入力して下さい。\n他の誰かが代表登録しているわけでもないのにこのエラーが出る場合、不正ログインが疑われますので、運営に問い合わせて下さい。"
    end

    def text_passwordError
      return "パスワードが一致しません。もう一度入力して下さい。\nゼッケン番号の入力からやり直す場合は「キャンセル」と入力して下さい。"
    end

    def text_AuthorizationCancel
      return "受付をキャンセルしました。"
    end

    def text_loguotSuccess
      return "ログアウトの処理が完了しました。"
    end

    def text_errorCPnumber
      return "CP番号は自然数で入力してください。"
    end

    def text_errorNoCP cp_number
      return "CP番号：#{cp_number}に該当するチェックポイントがありません。もう一度ご確認ください。"
    end

    def text_getCPnumber cp_number, cp_name
      return "CP番号：#{cp_number}は#{cp_name}です。よろしいですか？\n宜しければ、続けて写真を送って下さい。\n間違っていれば「NO」と返信して下さい。"
    end

    def text_noLocation
      return "位置情報の周辺15mにチェックポイントを発見できませんでした。"
    end

    def text_noLocationCSB cp_number, cp_name
      return "位置情報の周辺15mに#{cp_number}:#{cp_name}を発見できませんでした。\nチェックポイントの指定をやり直す場合は「キャンセル」と返信して下さい。"
    end

    def text_registredCP cp_number, cp_name
      return "#{cp_number}:#{cp_name}は既に登録されています。"
    end

    def text_registreCP cp_number, cp_name
      return "#{cp_number}:#{cp_name}を登録しました。"
    end

    def text_standByBuyPoint cp_number, cp_name
      return "#{cp_number}:#{cp_name}はサービスポイントのチェックポイントです。\nチェックポイントで撮影した写真をアップロードして下さい。特定の対象を写真に含めると加点対象になります。\n想定と異なるチェックポイントが認識されてしまった場合は「キャンセル」で写真受付をキャンセル出来ます。"
    end

    def text_BuyPointError cp_number, cp_name
      return "#{cp_number}:#{cp_name}はサービスポイントのチェックポイントなので位置情報では認証できません。\n写真をアップロードして下さい。"
    end

    def text_getGPSlogger
      return "GPSloggerのデータを受け取りました。結果発表をお待ち下さい。"
    end

    def text_approval_cp cp_name
      return "チェックポイント：#{cp_name}の写真が承認されました。"
    end

    def text_not_approval_cp cp_name, reason
      return "チェックポイント：#{cp_name}の写真が承認されませんでした。\n事由：#{reason}\nお手数ですが、CPの宣言からやり直して下さい。"
    end

    def text_goalCheck1
      return "ゴールチェックポイントを確認しましたが、ゴールは"
    end

    def text_goalCheck2
      return "ゴール：[ゴールした時刻]"
    end

    def text_goalCheck3
      return "と入力して下さい。"
    end

    def text_getGoal goal_time
      return "#{goal_time}でゴールタイムを記録しました。続けてタイマーの写真を送って下さい。\nゴールタイムの入力ミスなどがあった場合には「キャンセル」で、写真受付をキャンセルして、再度ゴールタイムを登録して下さい。"
    end

    def text_getGoalPhoto
      return "ゴールの写真を記録しました。お疲れ様でした。\n印刷受付にレポートを受け取りに来て下さい。"
    end

    def text_alreadyGoal
      return "既にゴールタイムの記録は完了しております。結果発表をお待ち下さい。"
    end

    def text_goalError
      return "ゴールタイムの認識に失敗しました。「○○:○○」若しくは「○○時○○分」で入力して下さい。\n正しく入力しているのにこのエラーが出ている場合は運営に問い合わせて下さい。"
    end

    def text_notAgent
      return "パスワードが一致する代理人が存在しません。"
    end

    def text_agentStart event_name
      return "イベント：#{event_name}の代理人としてログインしました。"
    end
    
    def text_ask_for_receipt_image cp_number
      return "写真を登録しました。cp: #{cp_number}は対象の商品を購入したレシートの画像を送っていただくとさらにポイントが追加されます。\nレシート写真を送ってください。\n「キャンセル」でレシート写真受付をキャンセル出来ます。"
    end

    def text_insertComplete cp_number, cp_name, insert_number
      return "#{cp_number}:#{cp_name}を#{insert_number}に挿入しました。"
    end

    def text_noCPregist cp_number
      return "このチームの通過記録に#{cp_number}を通過したという記録はありません。"
    end

    def text_deleteCP sn
      return "通し番号：#{sn}を削除しました。"
    end

    def text_moveCP sn, new_sn
      return "通し番号：#{sn}の記録を通し番号：#{new_sn}に移動しました。"
    end

    def text_moveSameNum
      return "移動元と移動先に同じ通し番号が入力されています。"
    end

    def text_moveOverNum
      return "存在しない通し番号が指定されています。"
    end

    def text_goalUpdate goal_time
      return "ゴール時間を#{goal_time}に更新し、遅刻点も再計算しました。"
    end

    def text_reprint
      return "新しいレポートの生成を開始しました。"
    end

    def text_argumentError_Insert
      return "INSERTの引数が正しくありません。\nINSERT [挿入したい通し番号] [挿入したいチェックポイント番号]\nの書式で入力して下さい。"
    end

    def text_notNaturakNumber
      return "挿入する番号は自然数で入力して下さい。"
    end

    def text_startStand
      return "「スタート」を実行するとチェックポイント通過情報が初期化されます。\n問題なければ「YES」、中断する場合は「NO」と入力して下さい。"
    end

    def text_startRun
      return "チェックポイント通過情報を初期化し、ロゲイニングを開始する準備が整いました。"
    end

    def text_noEventName
      return "該当するイベントコードが見つかりませんでした。"
    end

    def text_zekkenNotExist zekken_number
      return "#{zekken_number}に該当するチームがありませんでした。"
    end

    def text_agentLogin team_name
      return "#{team_name}に代理ログインしました。"
    end

    def text_agentLogout
      return "代理ログアウトしました。"
    end

#====================================
# DB:SELECT (get)
#====================================

    def getUserName userId

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'user_table' )

      where = "userid = '" + userId.to_s + "'"

      list = anytable.find2(where)
      result = {}

      if list == nil then
        return getNewUserName(userId)
      end

      list.each { |rec|
        result = rec
      }

      @pgconn.disconnect

      if result != {} && result != nil then
        return result
      else
        return getNewUserName(userId)
      end
    end

    def getUserIdByZekkenNumber zekken_number, event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'user_table' )

      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

      list = anytable.find2(where)
      result = ""

      if list == nil then
        return "no zekken number"
      end

      list.each { |rec|
        result = rec["userid"]
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return "no zekken number"
      end
    end

    def getZekkenNumberByUserId userId

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'user_table' )

      where = "userid = '#{userId}'"

      list = anytable.find2(where)
      result = ""

      if list == nil then
        return "no zekken number"
      end

      list.each { |rec|
        result = rec["zekken_number"]
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return "no zekken number"
      end
    end


    def getCPname cp_number, event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'checkpoint_table' )

      where = "cp_number = " + cp_number.to_s + " AND event_code = '#{event_code}'"

      list = anytable.find2(where)
      result = ""

      if list == nil then
        return "no cp error"
      end

      list.each { |rec|
        result = rec["cp_name"]
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return "no cp error"
      end
    end

    def getMaxNumByGI zekken_number, event_code # GI => gps_information

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'gps_information' )

      where = "serial_number = (SELECT max(serial_number) FROM gps_information WHERE zekken_number = '#{zekken_number}' AND event_code = '#{event_code}' )"

      list = anytable.find2(where)
      result = ""

      if list == nil then
        return 0
      end

      list.each { |rec|
        result = rec['serial_number']
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return 0
      end

    end

    def getZekkenListByUT #UT => user_table

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'user_table' )

      list = anytable.find2("zekken_number IS NOT NULL")
      result = []

      if list == nil then
        return []
      end

      list.each { |rec|
        result.push(rec['zekken_number'])
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return []
      end
    end

    def getZekkenListByTT event_code #TT => team_table

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'team_table' )

      list = anytable.find2("class_name != 'test' AND event_code = '#{event_code}'")
      result = []

      if list == nil then
        return []
      end

      list.each { |rec|
        result.push(rec['zekken_number'])
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return []
      end
    end

    def getPhotoList zekken, event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'gps_information' )

      where = "zekken_number = '#{zekken}' AND event_code = '#{event_code}'"

      list = anytable.find2(where)
      result = []

      if list == nil then
        return []
      end

      list.each { |rec|
          if rec['image_address'] != "#{set_originbase}/location_checked.png" && rec['image_address'] != "#{set_originbase}/location_checked.png" && rec['image_address'] != "#{set_originbase}/photo_none.png" && rec['image_address'] != "#{set_originbase}/asset/photo_none.png" && rec['image_address'] != "#{set_originbase}/start.png" && rec['image_address'] != "#{set_originbase}/asset/start.png" then
            result.push(rec['image_address'])
        end
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return []
      end
    end

    def getGpsInfo zekken_number, event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'gps_information' )

      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}' ORDER BY serial_number"

      list = anytable.find2(where)
      result = []

      if list == nil then
        return []
      end

      count = 0

      list.each { |rec|
        result[count] = {"cp_number" => rec['cp_number']}
        count += 1
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return []
      end
    end

    def getCPnameByThreshols latitude, longitude, event_code, cp_number = nil

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'checkpoint_table' )

      if cp_number.to_i == 999 then
        where = "latitude BETWEEN #{latitude - latitude_threshols("special")} AND #{latitude + latitude_threshols("special")} AND longitude BETWEEN #{longitude - longitude_threshols("special")} AND #{longitude + longitude_threshols("special")} AND event_code = '#{event_code}'"
      else
        where = "latitude BETWEEN #{latitude - latitude_threshols()} AND #{latitude + latitude_threshols()} AND longitude BETWEEN #{longitude - longitude_threshols()} AND #{longitude + longitude_threshols()} AND event_code = '#{event_code}'"
      end

      if cp_number != nil
        where = where + " AND cp_number = #{cp_number}"
      end

      p "where : #{where}"

      list = anytable.find2(where)
      result = {}

      if list == nil then
        result["result"] = "no cp error"
        return result
      end

      list.each { |rec|
        result["cp_number"] = rec["cp_number"]
        result["cp_name"] = rec["cp_name"]
        result["buy_point"] = rec["buy_point"]
      }

      @pgconn.disconnect

      if result != {} && result != nil then
        result["result"] = "OK"
        return result
      else
        result["result"] = "no cp error"
        return result
      end
    end

    def getTeamDataByZekken_number zekken_number, event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'team_table' )

      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

      list = anytable.find2(where)
      result = {}

      if list == nil then
        result["result"] = "ERROR"
        @pgconn.disconnect
        return result
      end

      list.each { |rec|
        result["zekken_number"] = rec["zekken_number"]
        result["team_name"] = rec["team_name"]
        result["class_name"] = rec["class_name"]
      }

      @pgconn.disconnect

      if result != {} && result != nil then
        result["result"] = "OK"
        return result
      else
        result["result"] = "ERROR"
        return result
      end
    end


    def getPhotoFromRR zekken_number, event_code, num #RR => RouteResult
      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      if num <= 60
        table = 'route_result'
      else
        table = 'route_result_2'
      end

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,table )

      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

      list = anytable.find2(where)
      result = {}

      if list == nil then
        @pgconn.disconnect
        return {"result" => "ERROR"}
      end

      docpath = set_commonbase() + '/' + event_code + '/' + zekken_number

      if( !Dir.exist?(docpath) )then
        if( Dir.mkdir( docpath,0755 ) == 0 ) then
        end
      end

      list.each { |rec|
        for i in 1..num do
          begin
            if rec["cp#{i}_ph"].include?(s3_domain()) then
              extension = result["cp#{i}_ph"].to_s.split('.').last
              result["cp#{i}_ph"] = rec["cp#{i}_ph"].gsub(s3_domain(),set_commonbase()).gsub(".#{extension}","_th.#{extension}")
            elsif rec["cp#{i}_ph"].include?(set_commonbase()) then
              result["cp#{i}_ph"] = rec["cp#{i}_ph"].gsub(set_commonbase(),set_commonbase2())
            else
              result["cp#{i}_ph"] = "#{set_commonbase()}/asset/photo_none.png"
            end
          rescue => e
            p "error : #{e}, result[cp#{i}_ph] :#{result["cp#{i}_ph"]}"
            result["cp#{i}_ph"] = "#{set_commonbase()}/asset/photo_none.png"
          end
          if result["cp#{i}_ph"].include?("asset/asset") then
            result["cp#{i}_ph"].gsub!('asset/asset', 'asset')
          end
          if File.exist?(result["cp#{i}_ph"]) == false then
            begin
              filename = rec["cp#{i}_ph"].split('/').last
              fullpath = docpath + '/' + filename
              fullpath2 = s3_domain + '/' + URI.encode_www_form_component(event_code, enc=nil) + '/' + zekken_number + '/' + filename
              URI.open(fullpath2) do |image|
                File.open(fullpath, 'w') do |file|
                  file.write(image.read)
                end
              end
              extension = filename.split('.').last

              filenameTh = filename.gsub(".#{extension}","_th.#{extension}")

              imageOri = Magick::Image.read(fullpath).first
              imageTh = imageOri.scale(image_size_width(), image_size_height())
              imageTh.write("temp/#{filenameTh}")

              nextpath = docpath + '/' + filenameTh

              FileUtils.mv("/var/www/temp/#{filenameTh}",nextpath)
              # File.delete(fullpath)

              result["cp#{i}_ph"] = nextpath
            rescue => e
              p "error : #{e}, rec[cp#{i}_ph] :#{rec["cp#{i}_ph"]}"
              result["cp#{i}_ph"] = "#{set_commonbase()}/asset/photo_none.png"
            end
          end
          begin
            result["cp#{i}_s"] = rec["cp#{i}_s"].gsub(set_commonbase2(),set_commonbase())
          rescue => e
            p "error : #{e}, result[cp#{i}_s] : #{result["cp#{i}_s"]}"
            result["cp#{i}_s"] = "#{set_commonbase()}/asset/photo_none.png"
          end
          if result["cp#{i}_s"].include?("asset/asset") then
            result["cp#{i}_s"].gsub!('asset/asset', 'asset')
          end
          if File.exist?(result["cp#{i}_s"]) == false then
            result["cp#{i}_s"] = "#{set_commonbase()}/asset/photo_none.png"
          end
        end
      }

      @pgconn.disconnect

      if result == [] || result == nil then
        return {"result" => "ERROR"}
      end

      result["result"] = "OK"

      return result
    end

    def getZekkenNumberByTeamName team_name, event_code

      puts "team_name=#{team_name}, event_code=#{event_code}"
      @pgconn=UserPostgres.new
      dbname = set_dbname()
      puts "dbname=#{dbname}"
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'team_table' )

      if team_name.include?("'") then
        team_name.gsub!("'","''")
      end

      where = "team_name = '#{team_name}' AND event_code = '#{event_code}'"
      puts "where = #{where}"

      list = anytable.find2(where)
      result = ""

      puts "list = #{list}"
      if list == nil then
        @pgconn.disconnect
        return "no zekken number"
      end

      list.each { |rec|
        result = rec["zekken_number"]
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return "no zekken number"
      end
    end

    def get_lat_lng_bounds_for_event_code(event_code)
      @pgconn = UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost", "mobilous", 0, dbname)
      anytable = UserPostgresTable.new
      anytable.useTable(@pgconn, 'gps_detail')
    
      where = "event_code = '#{event_code}' ORDER BY serial_number"
      
      list = anytable.find2(where)
      p "list: #{list}"
    
      max_latitude = -Float::INFINITY
      min_latitude = Float::INFINITY
      max_longitude = -Float::INFINITY
      min_longitude = Float::INFINITY
    
      list.each do |rec|
        latitude = rec['latitude'].to_f
        longitude = rec['longitude'].to_f
    
        max_latitude = latitude if latitude > max_latitude
        min_latitude = latitude if latitude < min_latitude
        max_longitude = longitude if longitude > max_longitude
        min_longitude = longitude if longitude < min_longitude
      end
    
      @pgconn.disconnect
    
      {
        max_latitude: max_latitude,
        min_latitude: min_latitude,
        max_longitude: max_longitude,
        min_longitude: min_longitude
      }
    end
    

    def getGpsList zekken_number, event_code, sort = 'ASC'

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'gps_detail' )

      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}' ORDER BY serial_number #{sort}"

      list = anytable.find2(where)
      result_list = []

      if list == nil then
        return []
      end

      list.each { |rec|
        result_list.push(rec)
      }

      @pgconn.disconnect

      if result_list != [] && result_list != nil then
        return result_list
      else
        return []
      end

    end

    def getSerialNumberByCpNumber zekken_number, event_code, cp_number

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'gps_information' )

      where = "zekken_number = '#{zekken_number}' AND cp_number = #{cp_number}"

      list = anytable.find2(where)
      result = ""

      if list == nil then
        return "no sn"
      end

      list.each { |rec|
        result = rec["serial_number"]
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return "no sn"
      end

    end

    def getTeamNameByZekkenNumber zekken_number, event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'team_table' )

      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

      list = anytable.find2(where)
      result = ""

      list.each { |rec|
        result = rec["team_name"]
      }

      @pgconn.disconnect
      return result

    end

    def getFinalReport zekken_number, event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'report_list' )

      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

      list = anytable.find2(where)
      result = ""

      list.each { |rec|
        result = rec["report_address"]
      }

      @pgconn.disconnect

      if result == "" || result == nil then
        return "no report"
      end

      return result

    end

    def getTeamList event_code=nil

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'team_table' )

      if event_code == nil then
        where = "0 = 0"
      else
        where = "event_code = '#{event_code}'"
      end

      list = anytable.find2(where)
      result = []

      list.each { |rec|
        result.push(rec)
      }

      @pgconn.disconnect

      if result == "" || result == nil then
        return "no report"
      end

      return result


    end

    def getZekkenNumberByAgentId agent_id
      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'agent_login' )

      where = "agent_id = '#{agent_id}'"

      list = anytable.find2(where)
      result = ""
      agent_id = ""

      list.each { |rec|
        result = rec["zekken_number"]
        agent_id = rec["agent_id"]
      }

      @pgconn.disconnect

      if result == "" || result == nil then
        if agent_id == "" || agent_id == nil then
          return "no data"
        else
          return "no login"
        end
      end

      return result
    end

    def getDataFromDatabase(zekken_number, event_code)
      @pgconn = UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost", "mobilous", 0, dbname)
      anytable = UserPostgresTable.new
      anytable.useTable(@pgconn, 'gps_detail')
    
      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}' ORDER BY serial_number"
    
      list = anytable.find2(where)
      p "list: #{list}"
      result = []
    
      list.each { |rec|
        result.push(rec)
      }
    
      @pgconn.disconnect
      result # 返り値としてresultを返す
    end

    def getCPlatlngByCPnumber cp_number, event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'checkpoint_table' )

      where = "cp_number = " + cp_number.to_s + " AND event_code = '#{event_code}'"

      list = anytable.find2(where)
      record = {}

      if list == nil then
        return {"status" => "no cp error"}
      end

      list.each { |rec|
        record = rec
      }

      @pgconn.disconnect

      result = {}

      if result != "" && result != nil then
        result["status"] = "OK"
        result["record"] = record
        return result
      else
        return {"status" => "no cp error"}
      end
    end

    def getEventStartTime event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'event_table' )

      where = "event_code = '#{event_code}'"

      list = anytable.find2(where)
      result = ""

      if list == nil then
        return ""
      end

      list.each { |rec|
        result = rec["start_time"]
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return ""
      end

    end

    def getCPNumByPhotoPoint photo_point, event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'checkpoint_table' )

      where = "photo_point = " + photo_point.to_s + " AND event_code = '#{event_code}'"

      list = anytable.find2(where)
      result = ""

      if list == nil then
        return "no cp error"
      end

      list.each { |rec|
        result = rec["cp_number"]
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return "no cp error"
      end
    end

    def getPhotoPointByCPNum cp_number, event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'checkpoint_table' )

      where = "cp_number = " + cp_number.to_s + " AND event_code = '#{event_code}'"

      list = anytable.find2(where)
      result = ""

      if list == nil then
        return "no cp error"
      end

      list.each { |rec|
        result = rec["photo_point"]
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return "no cp error"
      end
    end

    def getCheckpointList event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'checkpoint_table' )

      where = "event_code = '#{event_code}'"

      list = anytable.find2(where)
      result_list = []

      if list == nil then
        return []
      end

      list.each { |rec|
        result_list.push(rec)
      }

      @pgconn.disconnect

      if result_list != [] && result_list != nil then
        return result_list
      else
        return []
      end

    end

    def getMaxUserNumByRSR event_code # RS => rogeSimRecord

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'rogSimRecord' )

      where = "user_serial = (SELECT max(user_serial) FROM rogSimRecord WHERE event_code = '#{event_code}' )"

      list = anytable.find2(where)
      result = ""

      if list == nil then
        return 0
      end

      list.each { |rec|
        result = rec['user_serial']
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return 0
      end

    end

    def getMaxNumByRSR userId, event_code # RS => rogeSimRecord

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'rogSimRecord' )

      where = "serial_number = (SELECT max(serial_number) FROM rogSimRecord WHERE event_code = '#{event_code}' AND user_serial = #{userId} )"

      list = anytable.find2(where)
      result = ""

      if list == nil then
        return 0
      end

      list.each { |rec|
        result = rec['serial_number']
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return 0
      end

    end

    def getRogSimResult userId, event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'rogSimRecord' )

      where = "user_serial = '#{userId}' AND event_code = '#{event_code}' ORDER BY serial_number"

      list = anytable.find2(where)
      result = []

      if list == nil then
        return []
      end

      count = 0

      list.each { |rec|
        result[count] = {"cp_number" => rec['cp_number']}
        count += 1
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return []
      end
    end

    def getPhotoFromRSV userId, event_code, num #RSV => rogeSimView
      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      table = 'rogeSimView'

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,table )

      where = "user_serial = '#{userId}' AND event_code = '#{event_code}'"

      list = anytable.find2(where)
      result = {}

      if list == nil then
        return {"result" => "ERROR"}
      end

      list.each { |rec|
        for i in 1..num do
          begin
            result["cp#{i}_s"] = rec["cp#{i}_s"].gsub(set_commonbase2(),set_commonbase())
          rescue => e
            p "error : #{e}, result[cp#{i}_s] : #{result["cp#{i}_s"]}"
            result["cp#{i}_s"] = "#{set_commonbase()}/asset/photo_none.png"
          end
          if result["cp#{i}_s"].include?("asset/asset") then
            result["cp#{i}_s"].gsub!('asset/asset', 'asset')
          end
          if File.exist?(result["cp#{i}_s"]) == false then
            result["cp#{i}_s"] = "#{set_commonbase()}/asset/photo_none.png"
          end
        end
      }

      @pgconn.disconnect

      if result == [] || result == nil then
        return {"result" => "ERROR"}
      end

      result["result"] = "OK"

      return result
    end

    def getEventData event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'event_table' )

      where = "event_code = '#{event_code}'"

      list = anytable.find2(where)
      result = {}

      if list == nil then
        return ""
      end

      list.each { |rec|
        result = rec
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return ""
      end

    end

    def getMaxNumByCLS # CLS => cp_listsheet

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'cp_listsheet' )

      where = "serial_number = (SELECT max(serial_number) FROM cp_listsheet)"

      list = anytable.find2(where)
      result = ""

      if list == nil then
        return 0
      end

      list.each { |rec|
        result = rec['serial_number']
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return 0
      end

    end

    def getNewUserName userId

      response = client.get_profile(userId)
      case response
      when Net::HTTPSuccess then
        contact = JSON.parse(response.body)

        username = contact['displayName']

        @pgconn=UserPostgres.new
        dbname = set_dbname()
        @pgconn.connectPg("localhost","mobilous",0,dbname)

        anytable=UserPostgresTable.new
        anytable.useTable( @pgconn,'user_table' )

        title = ["userid","user_name","create_at","update_at"]
        record = {"userid" => userId, "user_name" => username, "create_at" => DateTime.now, "update_at" => DateTime.now}

        sql = anytable.makeInsertKeySet(title,record)
        begin
          ret = anytable.exec(sql)         # true or false
        rescue => error
          p error
        end

        @pgconn.disconnect

        return {"user_name" => username, "zekken_number" => "no zekken number", "event_code" => ""}
      else
        p "#{
          response.code
        } #{
          response.body
        }"
        return {"user_name" => "get name error"}
      end

    end

    def haversine_distance(lat1, lon1, lat2, lon2)
      r = 6371 # 地球の半径（km）
      phi1 = lat1 * Math::PI / 180
      phi2 = lat2 * Math::PI / 180
      delta_phi = (lat2 - lat1) * Math::PI / 180
      delta_lambda = (lon2 - lon1) * Math::PI / 180
    
      a = Math.sin(delta_phi / 2) ** 2 + Math.cos(phi1) * Math.cos(phi2) * Math.sin(delta_lambda / 2) ** 2
      c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a))
    
      r * c
    end
    
    def calculate_speed_and_type(prev_point, current_point, next_point)
      if prev_point && next_point
        time_diff = (Time.parse(next_point['create_at']) - Time.parse(prev_point['create_at'])) / 3600.0 # 時間
        distance = haversine_distance(prev_point['latitude'].to_f, prev_point['longitude'].to_f,
                                      next_point['latitude'].to_f, next_point['longitude'].to_f)
        speed = distance / time_diff
      else
        speed = 0
      end
    
      movement_type = speed < 18 ? "walking/jogging" : "public_transport"
    
      [speed, movement_type]
    end
    
    def calculate_cumulative_distance(gps_data, index)
      distance = 0
      (1..index).each do |i|
        distance += haversine_distance(gps_data[i-1]['latitude'].to_f, gps_data[i-1]['longitude'].to_f,
                                       gps_data[i]['latitude'].to_f, gps_data[i]['longitude'].to_f)
      end
      distance
    end

#====================================
# DB:SELECT (check)
#====================================
    def checkBuypointSingleRecord zekken_number, event_code, cp_number
      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'gps_information' )
      
      #buy_pointであり、レコード数が1以下なら重複なしとして扱う
      where_relation = " JOIN checkpoint_table AS c ON g.event_code = c.event_code AND g.cp_number = c.cp_number WHERE g.zekken_number = '#{zekken_number}' AND g.event_code = '#{event_code}' AND g.cp_number = '#{cp_number}' AND c.buy_point > 0"
      count = nil
      list_relation = anytable.find_for_relation(where_relation)
      list_relation.each do |row|
        count = row["count"] if row.is_a?(Hash)  # rowがハッシュであれば
      end
      if count.to_i == 1 then
        return true
      else
        return false
      end
    end

    def cpDoubleCheck(zekken_number, event_code, cp_number) # 重複してたらtrue
      @pgconn = UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost", "mobilous", 0, dbname)
    
      anytable = UserPostgresTable.new
      anytable.useTable(@pgconn, 'gps_information')
    
      # 今日の日付のレコードのみをチェック
      today = Date.today.to_s
      
      # buy_pointであり、今日のレコード数が1以下なら重複なしとして扱う
      where_relation = " JOIN checkpoint_table AS c ON g.event_code = c.event_code AND g.cp_number = c.cp_number WHERE g.zekken_number = '#{zekken_number}' AND g.event_code = '#{event_code}' AND g.cp_number = '#{cp_number}' AND c.buy_point > 0 AND DATE(g.create_at) = '#{today}'"
      count = nil
      list_relation = anytable.find_for_relation(where_relation)
      list_relation.each do |row|
        count = row["count"] if row.is_a?(Hash)  # rowがハッシュであれば
      end
      if count.to_i == 1 then
        @pgconn.disconnect
        return false
      end
    
      # 今日の日付のレコードをチェック
      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}' AND cp_number = '#{cp_number}' AND DATE(create_at) = '#{today}'"
    
      list = anytable.find2(where)
      result = ""
    
      if list == nil then
        @pgconn.disconnect
        return false
      end
    
      list.each { |rec|
        result = rec["serial_number"]
      }
    
      @pgconn.disconnect
    
      if result != "" && result != nil then
        return true
      else
        return false
      end
    end

    # def cpDoubleCheck zekken_number, event_code, cp_number # 重複してたらtrue

    #   @pgconn=UserPostgres.new
    #   dbname = set_dbname()
    #   @pgconn.connectPg("localhost","mobilous",0,dbname)

    #   anytable=UserPostgresTable.new
    #   anytable.useTable( @pgconn,'gps_information' )
      
    #   #buy_pointであり、レコード数が1以下なら重複なしとして扱う
    #   where_relation = " JOIN checkpoint_table AS c ON g.event_code = c.event_code AND g.cp_number = c.cp_number WHERE g.zekken_number = '#{zekken_number}' AND g.event_code = '#{event_code}' AND g.cp_number = '#{cp_number}' AND c.buy_point > 0"
    #   count = nil
    #   list_relation = anytable.find_for_relation(where_relation)
    #   list_relation.each do |row|
    #     count = row["count"] if row.is_a?(Hash)  # rowがハッシュであれば
    #   end
    #   if count.to_i == 1 then
    #     return false
    #   end

    #   where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}' AND cp_number = '#{cp_number}'"

    #   list = anytable.find2(where)
    #   #return list
    #   #p list
    #   #return true
    #   result = ""

    #   if list == nil then
    #     return false
    #   end

    #   list.each { |rec|
    #     result = rec["serial_number"]
    #   }

    #   @pgconn.disconnect

    #   if result != "" && result != nil then
    #     return true
    #   else
    #     return false
    #   end

    # end

    def zekkenAuthorization zekken,password

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'team_table' )

      where = "zekken_number = '#{zekken}' AND password = '#{password}'"

      list = anytable.find2(where)
      result = {}

      if list == nil then
        return { "zekken_number" => "ERROR" }
      end

      list.each { |rec|
        result["zekken_number"] = rec["zekken_number"]
        result["team_name"] = rec["team_name"]
        result["event_code"] = rec["event_code"]
      }

      @pgconn.disconnect

      if result != {} && result != nil then
        result["result"] = "OK"
        return result
      else
        result["result"] = "ERROR"
        return result
      end
    end

    def agentAuthorization password

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'agent_table' )

      where = "password = '#{password}'"
      p "where : #{where}"
      list = anytable.find2(where)
      result = ""

      if list == nil then
        return "ERROR"
      end

      list.each { |rec|
        result = rec["agent_id"]
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return "ERROR"
      end
    end

    def checkStatus userId

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'chat_status' )

      where = "userid = '#{userId}'"

      list = anytable.find2(where)
      result = {}

      if list == nil then
        result['status'] = "nothing"
        return result
      end

      list.each { |rec|
        result['status'] = rec["status"]
        result['memory'] = rec["memory"]
      }

      @pgconn.disconnect

      if result != {} && result != nil then
        return result
      else
        result['status'] = "nothing"
        return result
      end

    end

    def checkAgentFlag zekken_number,event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'agent_flag' )

      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

      list = anytable.find2(where)
      result = {}

      if list == nil then
        return false
      end

      list.each { |rec|
        result = rec["agent_id"]
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return true
      else
        return false
      end

    end

    def checkBonusPointFlag(zekken_number, event_code)
      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost", "mobilous", 0, dbname)
    
      anytable=UserPostgresTable.new
      anytable.useTable(@pgconn, 'bonus_point_flag')
    
      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"
    
      list = anytable.find2(where)
      @pgconn.disconnect  # データベース接続を切断
    
      # ntuplesメソッドを使用して結果セットに含まれる行の数をチェック
      if list.ntuples.zero?
        return false  # レコードが存在しない場合はfalseを返す
      else
        return true  # レコードが1つでも見つかった場合はtrueを返す
      end
    end
    
    
    

    def checkEventCode event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'event_table' )

      where = "event_code = '#{event_code}'"

      list = anytable.find2(where)
      result = ""

      if list == nil then
        return ""
      end

      list.each { |rec|
        result = rec["event_name"]
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return result
      else
        return ""
      end

    end

    def checkZekkenExist zekken_number

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'team_table' )

      where = "zekken_number = '#{zekken_number}'"

      list = anytable.find2(where)
      result = ""

      if list == nil then
        return false
      end

      list.each { |rec|
        result = rec["team_name"]
      }

      @pgconn.disconnect

      if result != "" && result != nil then
        return true
      else
        return false
      end

    end

    def process_route_data(gps_data, bounds)
      width = 800
      height = 600
      
      # ルートポイントを正規化
      normalized_points = gps_data.map do |point|
        x = (point['longitude'].to_f - bounds[:min_longitude]) * width / (bounds[:max_longitude] - bounds[:min_longitude])
        y = height - (point['latitude'].to_f - bounds[:min_latitude]) * height / (bounds[:max_latitude] - bounds[:min_latitude])
        {
          x: x,
          y: y,
          longitude: point['longitude'].to_f,
          latitude: point['latitude'].to_f,
          timestamp: point['timestamp']
        }
      end
    
      {
        points: normalized_points,
        start: normalized_points.first,
        end: normalized_points.last,
        bounds: bounds
      }
    end

    # ユーザーのルート情報を取得するメソッド
    def get_user_route(pgconn, event_code, zekken_number)
      anytable = UserPostgresTable.new
      anytable.useTable(pgconn, 'gps_detail')

      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}' ORDER BY create_at ASC"
      route_data = anytable.find2(where)

      route_data.map do |point|
        {
          latitude: point['latitude'].to_f,
          longitude: point['longitude'].to_f,
          timestamp: point['create_at']
        }
      end
    end

#====================================
# DB:INSERT
#====================================

# def inputCPnumber zekken_number, event_code, cp_number, image_address, insert_number = 0

#   if insert_number == 0 then
#     next_num = getMaxNumByGI(zekken_number,event_code).to_i + 1
#   else
#     next_num = insert_number
#   end

#   @pgconn=UserPostgres.new
#   dbname = set_dbname()
#   @pgconn.connectPg("localhost","mobilous",0,dbname)

#   anytable=UserPostgresTable.new
#   anytable.useTable( @pgconn,'gps_information' )
  
#   #buy_pointであり、すでに同じcpのレコードが存在しているなら
#   where_relation = " JOIN checkpoint_table AS c ON g.event_code = c.event_code AND g.cp_number = c.cp_number WHERE g.zekken_number = '#{zekken_number}' AND g.event_code = '#{event_code}' AND g.cp_number = '#{cp_number}' AND c.buy_point > 0"
#   count = nil
#   list_relation = anytable.find_for_relation(where_relation)
#   list_relation.each do |row|
#     count = row["count"] if row.is_a?(Hash)  # rowがハッシュであれば
#   end
#   if count.to_i == 1 then
#     title = ["serial_number", "zekken_number","event_code","cp_number","image_address","create_at","update_at","minus_photo_flag"]
#     record = {"serial_number" => next_num, "zekken_number" => zekken_number, "event_code" => event_code, "cp_number" => cp_number, "image_address" => image_address,"create_at" => DateTime.now, "update_at" => DateTime.now, "minus_photo_flag" => 'true', "photo_point" => 0}
#     sql = anytable.makeInsertKeySet(title,record)
#     begin
#       #ret = anytable.exec(sql)         # true or false
#       anytable.insertExec(sql)         # true or false
#     rescue => error
#       p error
#     end
#     @pgconn.disconnect
#     return "200 OK"
#   end

#   title = ["serial_number", "zekken_number","event_code","cp_number","image_address","create_at","update_at"]
#   record = {"serial_number" => next_num, "zekken_number" => zekken_number, "event_code" => event_code, "cp_number" => cp_number, "image_address" => image_address,"create_at" => DateTime.now, "update_at" => DateTime.now}

#   sql = anytable.makeInsertKeySet(title,record)
#   begin
#     #ret = anytable.exec(sql)         # true or false
#     anytable.insertExec(sql)         # true or false
#   rescue => error
#     p error
#   end

#   @pgconn.disconnect

#   return "200 OK"

# end

def inputCPnumber(zekken_number, event_code, cp_number, image_address, insert_number = 0)
  puts "inputCPnumber"
  
  if insert_number == 0
    next_num = getMaxNumByGI(zekken_number, event_code).to_i + 1
  else
    next_num = insert_number
  end

  @pgconn = UserPostgres.new
  dbname = set_dbname()
  @pgconn.connectPg("localhost", "mobilous", 0, dbname)

  anytable = UserPostgresTable.new
  anytable.useTable(@pgconn, 'gps_information')

  # colabo_company_memoの初期化
  colabo_company_memo = ''

  # 日付チェックと値の設定
  current_date = Date.today
  if current_date >= Date.new(2024, 7, 8) && current_date <= Date.new(2024, 10, 31)
    colabo_company_memo = "FC岐阜"
  end

  # 同じ日付でのチェックポイント数を確認
  today = Date.today.to_s
  where_relation = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}' AND DATE(create_at) = '#{today}'"
  
  # buy_pointが0より大きいチェックポイントの数を確認
  buy_point_count = anytable.count("#{where_relation} AND cp_number IN (SELECT cp_number FROM checkpoint_table WHERE event_code = '#{event_code}' AND buy_point > 0)")
  
  # photo_pointのみのチェックポイントの数を確認
  photo_point_count = anytable.count("#{where_relation} AND cp_number IN (SELECT cp_number FROM checkpoint_table WHERE event_code = '#{event_code}' AND buy_point = 0)")

  # 現在のcp_numberのbuy_pointを確認
  cp_buy_point_query = "JOIN checkpoint_table c ON g.event_code = c.event_code AND g.cp_number = c.cp_number WHERE g.event_code = '#{event_code}' AND g.cp_number = '#{cp_number}'"
  cp_buy_point_result = anytable.find_for_relation(cp_buy_point_query, nil, "c.buy_point")
  
  current_cp_buy_point = 0
  if cp_buy_point_result && cp_buy_point_result.any?
    current_cp_buy_point = cp_buy_point_result.first['buy_point'].to_i
  end

  if (current_cp_buy_point > 0 && buy_point_count.to_i >= 2) || (current_cp_buy_point == 0 && photo_point_count.to_i >= 1)
    @pgconn.disconnect
    return "409 Conflict" # 既に制限に達している場合
  end

  # 同じ日付で同じチェックポイントのレコードが存在するか確認
  same_cp_count = anytable.count("#{where_relation} AND cp_number = '#{cp_number}'")

  if same_cp_count.to_i > 0
    @pgconn.disconnect
    return "409 Conflict" # 既に同じ日付で同じチェックポイントのレコードが存在する場合
  end

  title = ["serial_number", "zekken_number", "event_code", "cp_number", "image_address", "create_at", "update_at", "colabo_company_memo"]
  record = {
    "serial_number" => next_num,
    "zekken_number" => zekken_number,
    "event_code" => event_code,
    "cp_number" => cp_number,
    "image_address" => image_address,
    "create_at" => DateTime.now,
    "update_at" => DateTime.now,
    "colabo_company_memo" => colabo_company_memo
  }

  sql = anytable.makeInsertKeySet(title, record)
  begin
    anytable.insertExec(sql) # true or false
  rescue => error
    p error
    @pgconn.disconnect
    return "500 Internal Server Error"
  end

  @pgconn.disconnect

  return "200 OK"
end
    
   
    
    def test_inputCPnumber zekken_number, event_code, cp_number, image_address, insert_number = 0

      if insert_number == 0 then
        next_num = getMaxNumByGI(zekken_number,event_code).to_i + 1
      else
        next_num = insert_number
      end

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'gps_information' )

      #buy_pointであり、すでに同じcpのレコードが存在しているなら
      where_relation = " JOIN checkpoint_table AS c ON g.event_code = c.event_code AND g.cp_number = c.cp_number WHERE g.zekken_number = '#{zekken_number}' AND g.event_code = '#{event_code}' AND g.cp_number = '#{cp_number}' AND c.buy_point > 0"
      count = nil
      list_relation = anytable.find_for_relation(where_relation)
      list_relation.each do |row|
        count = row["count"] if row.is_a?(Hash)  # rowがハッシュであれば
      end
      if count.to_i == 1 then
        title = ["serial_number", "zekken_number","event_code","cp_number","image_address","create_at","update_at","minus_photo_flag"]
        record = {"serial_number" => next_num, "zekken_number" => zekken_number, "event_code" => event_code, "cp_number" => cp_number, "image_address" => image_address,"create_at" => DateTime.now, "update_at" => DateTime.now, "minus_photo_flag" => 'true'}
        sql = anytable.makeInsertKeySet(title,record)
        return sql
        begin
          #ret = anytable.exec(sql)         # true or false
          anytable.insertExec(sql)         # true or false
        rescue => error
          p error
        end
        @pgconn.disconnect
        return "200 OK"
      end

      title = ["serial_number", "zekken_number","event_code","cp_number","image_address","create_at","update_at"]
      record = {"serial_number" => next_num, "zekken_number" => zekken_number, "event_code" => event_code, "cp_number" => cp_number, "image_address" => image_address,"create_at" => DateTime.now, "update_at" => DateTime.now}

      sql = anytable.makeInsertKeySet(title,record)
      begin
        #ret = anytable.exec(sql)         # true or false
        anytable.insertExec(sql)         # true or false
      rescue => error
        p error
      end

      @pgconn.disconnect

      return "200 OK"

    end


    def inputStatus userId, status, memory

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'chat_status' )

      title = ["userid", "status", "memory", "create_at", "update_at"]
      record = {"userid" => userId, "status" => status, "memory" => memory, "create_at" => DateTime.now, "update_at" => DateTime.now }

      sql = anytable.makeInsertKeySet(title,record)
      begin
        ret = anytable.exec(sql)         # true or false
      rescue => error
        p error
      end

      @pgconn.disconnect

      return "OK"

    end

    def inputGPSlog zekken_number, event_code, lat, lon

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'gpslogger_data' )

      title = ["zekken_number","event_code","latitude","longitude","point_category","create_at","update_at"]
      record = {"zekken_number" => zekken_number, "event_code" => event_code, "latitude" => lat, "longitude" => lon, "point_category" => "route", "create_at" => DateTime.now, "update_at" => DateTime.now}

      sql = anytable.makeInsertKeySet(title,record)
      begin
        ret = anytable.exec(sql)         # true or false
      rescue => error
        p error
      end

      @pgconn.disconnect

      return "200 OK"

    end

    def inputGoalTime zekken_number, event_code, goal_time, late_point,goal_photo

      sn = getMaxNumByGI(zekken_number, event_code).to_i + 1

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'gps_information' )

      title = ["serial_number","zekken_number","event_code","cp_number","image_address","goal_time","late_point","create_at","update_at"]
      record = {"serial_number" => sn, "zekken_number" => zekken_number, "event_code" => event_code, "cp_number" => -1,"image_address" => goal_photo, "goal_time" => goal_time, "late_point" => late_point, "create_at" => DateTime.now, "update_at" => DateTime.now}

      sql = anytable.makeInsertKeySet(title,record)
      begin
        ret = anytable.exec(sql)         # true or false
      rescue => error
        p error
      end

      @pgconn.disconnect

      return "200 OK"

    end

    def inputAgentLogin agent_id, zekken_number, event_code
      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'agent_login' )

      title = ["agent_id", "zekken_number","event_code","create_at","update_at"]
      record = {"agent_id" => agent_id, "zekken_number" => zekken_number, "event_code" => event_code, "create_at" => DateTime.now, "update_at" => DateTime.now}

      sql = anytable.makeInsertKeySet(title,record)
      begin
        ret = anytable.exec(sql)         # true or false
      rescue => error
        p error
      end

      @pgconn.disconnect
    end

    def inputFinalReport zekken_number, event_code, pdf_address
      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'report_list' )

      title = ["zekken_number", "event_code", "report_address"]
      record = {"zekken_number" => zekken_number, "event_code" => event_code, "report_address" => pdf_address}

      sql = anytable.makeInsertKeySet(title,record)
      begin
        ret = anytable.exec(sql)         # true or false
      rescue => error
        p error
      end

      @pgconn.disconnect

      return "200 OK"
    end

    def inputAgentFlag zekken_number, event_code, agent_id

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'agent_flag' )

      title = ["zekken_number", "agent_id", "event_code", "create_at", "update_at"]
      record = {"zekken_number" => zekken_number, "agent_id" => agent_id, "event_code" => event_code, "create_at" => DateTime.now, "update_at" => DateTime.now }

      sql = anytable.makeInsertKeySet(title,record)
      begin
        ret = anytable.exec(sql)         # true or false
      rescue => error
        p error
      end

      @pgconn.disconnect

    end

    def inputTeamData event_code,class_name,zekken_number,team_name,pass

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'team_table' )

      title = ["zekken_number", "event_code", "team_name", "class_name", "password"]
      record = {"zekken_number" => zekken_number, "event_code" => event_code, "team_name" => team_name, "class_name" => class_name, "password" => pass }

      sql = anytable.makeInsertKeySet(title,record)
      begin
        ret = anytable.exec(sql)         # true or false
      rescue => error
        p error
      end

      @pgconn.disconnect

    end

    def inputRogSimRecord userId, event_code, cp_number, insert_number = 0

      if insert_number == 0 then
        next_num = getMaxNumByRSR(userId,event_code).to_i + 1
      else
        next_num = insert_number
      end

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'rogSimRecord' )

      title = ["serial_number", "user_serial","event_code","cp_number","create_at"]
      record = {"serial_number" => next_num, "user_serial" => userId, "event_code" => event_code, "cp_number" => cp_number, "create_at" => DateTime.now}

      sql = anytable.makeInsertKeySet(title,record)
      begin
        ret = anytable.exec(sql)         # true or false
      rescue => error
        p error
      end

      @pgconn.disconnect

      return "200 OK"

    end

    def inputCpListSheet header, record

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'cp_listsheet' )

      sql = anytable.makeInsertKeySet(header,record)
      begin
        ret = anytable.insertExec(sql)         # true or false
      rescue => error
        p error
      end

      @pgconn.disconnect

      return "200 OK"

    end

    def inputPrintQueueMemory queue

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'print_queue_memory' )

      title = ["queue_detail", "add_datetime"]
      record = {"queue_detail" => queue, "add_datetime" => DateTime.now}

      sql = anytable.makeInsertKeySet(title,record)
      begin
        ret = anytable.insertExec(sql)         # true or false
      rescue => error
        p error
      end

      @pgconn.disconnect

      return "200 OK"

    end

#====================================
# DB:UPDATE (add)
#====================================

    def addPhotoToGPS serial_number, photoURI

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      rec = {"image_address" => photoURI, "update_at" => DateTime.now}
      where = "serial_number = " + serial_number

      begin
        @pgconn.update("gps_information",rec,where)
      rescue => e
        p e
        message = {
          type: 'text',
          text: "写真データの保存に失敗しました。大変申し訳ありませんが、もう一度やり直して下さい。"
        }
        client.reply_message(event['replyToken'], message)

        @pgconn.disconnect

        return "UPDATE ERROR"
      end

      @pgconn.disconnect

    end

    def addUserData userId, zekken_number, event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      rec = {"zekken_number" => zekken_number, "event_code" => event_code,"update_at" => DateTime.now}
      where = "userid = '#{userId}'"

      begin
        @pgconn.update("user_table",rec,where)
      rescue => e
        p e

        @pgconn.disconnect

        return "UPDATE ERROR"
      end

      @pgconn.disconnect

      return "200 OK"

    end

    def addBuyFlagTrue serial_number, zekken_number, event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      rec = {"buy_flag" => true, "update_at" => DateTime.now}
      where = "event_code = '#{event_code}' AND zekken_number = '#{zekken_number}' AND serial_number = #{serial_number}"

      begin
        @pgconn.update("gps_information",rec,where)
      rescue => e
        p e

        @pgconn.disconnect

        return "UPDATE ERROR"
      end

      @pgconn.disconnect

      return "OK"

    end

    def addBuyFlagFalse serial_number, zekken_number, event_code

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      rec = {"buy_flag" => false, "update_at" => DateTime.now}
      where = "event_code = '#{event_code}' AND zekken_number = '#{zekken_number}' AND serial_number = #{serial_number}"

      begin
        @pgconn.update("gps_information",rec,where)
      rescue => e
        p e

        @pgconn.disconnect

        return "UPDATE ERROR"
      end

      @pgconn.disconnect

      return "OK"

    end

#====================================
# DB:UPDATE (change)
#====================================

    def changeSerialNumberInGPSInformation zekken_number, event_code, serial_number, new_serial_number

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      rec = {"serial_number" => new_serial_number, "update_at" => DateTime.now}
      where = "serial_number =  #{serial_number} AND zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

      begin
        @pgconn.update("gps_information",rec,where)
      rescue => e
        p e

        @pgconn.disconnect

        return "UPDATE ERROR"
      end

      @pgconn.disconnect

      return "OK"

    end

    def changeGoalTimeAndLatePoint zekken_number, event_code, goal_time, late_point

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      rec = {"goal_time" => goal_time, "late_point" => late_point, "update_at" => DateTime.now}
      where = "zekken_number = '#{zekken_number}' AND cp_number = -1 AND event_code = '#{event_code}'"

      begin
        @pgconn.update("gps_information",rec,where)
      rescue => e
        p e

        @pgconn.disconnect

        return "UPDATE ERROR"
      end

      @pgconn.disconnect

      return "OK"

    end

    def changeAgentLogin agent_id, zekken_number, event_code
      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      rec = {"zekken_number" => zekken_number, "event_code" => event_code, "update_at" => DateTime.now}
      where = "agent_id = '#{agent_id}'"

      begin
        @pgconn.update("agent_login",rec,where)
      rescue => e
        p e

        @pgconn.disconnect

        return "UPDATE ERROR"
      end

      @pgconn.disconnect

      return "OK"
    end

    def changeFinalReport zekken_number, event_code, pdf_address

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      rec = {"report_address" => pdf_address}
      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

      begin
        @pgconn.update("report_list",rec,where)
      rescue => e
        p e

        @pgconn.disconnect

        return "UPDATE ERROR"
      end

      @pgconn.disconnect

      return "OK"

    end

    def changeTeamClass zekken_number, event_code, new_class

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      rec = {"class_name" => new_class}
      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

      begin
        @pgconn.update("team_table",rec,where)
      rescue => e
        p e

        @pgconn.disconnect

        return "ERROR"
      end

      @pgconn.disconnect

      return "OK"

    end

#====================================
# DB:DELETE
#====================================

    def removeGI zekken_number, event_code, serial_number #GI => gps_information

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'gps_information' )

      where = "serial_number = #{serial_number} AND zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

      begin
        p anytable.remove(where)

        @pgconn.disconnect

        return "200 OK"
      rescue => e
        p e
        return "delete error"
      end

    end

    def removeAllGI zekken_number, event_code #GI => gps_information

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'gps_information' )

      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

      begin
        p anytable.remove(where)
        anytable.remove(where)
        @pgconn.disconnect

        return "200 OK"
      rescue => e
        return "delete error"
      end

    end

    def removeStatus userId #GI => gps_information

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'chat_status' )

      where = "userid = '#{userId}'"

      begin
        p anytable.remove(where)

        @pgconn.disconnect


        return "200 OK"
      rescue => e
        return "delete error"
      end

    end

#====================================
# RogSim
#====================================

    def rogainingSimulator event_code, course_time, pause_time_free, pause_time_paid, spare_time, target_velocity, free_node_to_visit, paid_node_to_visit
      p "target_velocity : #{target_velocity}"
      input_hash = {
        'course_time' => course_time,
        'pause_time_free' => pause_time_free,
        'pause_time_paid' => pause_time_paid,
        'spare_time' => spare_time,
        'target_velocity' => target_velocity,
        'free_node_to_visit' => free_node_to_visit,
        'paid_node_to_visit' => paid_node_to_visit
      }
      p "input_hash : #{input_hash}"
      input_json = JSON.generate(input_hash)

      file_origin = set_rogSimBase() + "/work"

      file_name = DateTime.now.to_s.gsub(':','-').gsub('+','_') + '.json'

      file_path = file_origin + "/input/" + file_name

      File.open(file_path, "w") do |f|
        f.write(input_json)
      end

      system("bash #{set_rogSimBase()}/rogeSim.sh #{event_code} #{file_name}")

      begin
        response = {"status" => "OK", "detail" => JSON.parse(File.read(file_origin + "/output/" + file_name))}
      rescue Errno::ENOENT => e
        response = {"status" => "ERROR", "detail" => "File not found."}
      rescue => e
        response = {"status" => "ERROR", "detail" => e}
      end

      return response

    end

#====================================
# Register
#====================================

    def cpNumberRegister userId, zekken_number, event_code, cp_number, slice_number, client, event

      if slice_number != 0 then
        cp_number.slice!(0,slice_number)
      end

      if (cp_number.to_s.start_with?('#')) then
        cp_number.delete_prefix!('#')
      end

      cp_number = cp_number.to_i

      if cp_number <= 0 then
        message = {
          type: 'text',
          text: text_errorCPnumber()
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_errorCPnumber())
        return "200 OK"
      end


      cp_number = cp_number.to_i

      if cp_number <= 0 then
        message = {
          type: 'text',
          text: text_errorNoCP(cp_number)
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_errorNoCP(cp_number))
        return "200 OK"
      end

      cp_name = getCPname(cp_number, event_code)

      if cp_name == "no cp error" then
        message = {
          type: 'text',
          text: text_errorNoCP(cp_number)
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_errorNoCP(cp_number))

        return "200 OK"
      end

      if cpDoubleCheck(zekken_number, event_code, cp_number) then
        message = {
          type: 'text',
          text: text_registredCP(cp_number,cp_name)
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_registredCP(cp_number,cp_name))
        return "200 OK"
      end

      inputStatus(userId,'cp_stanby',cp_number)


      message = {
        type: 'text',
        text: text_getCPnumber(cp_number, cp_name)
      }
      client.reply_message(event['replyToken'], message)
      chatLogger(userId, 'bot', 'text', text_getCPnumber(cp_number, cp_name))

      return "200 OK"
    end

    def zekkenNumberRegister userId, zekken_number, event_code, slice_number, client, event

      zekken_number.slice!(0,slice_number)

      if zekken_number.start_with?('[') then
        zekken_number.delete_prefix!("[")
      end
      if zekken_number.end_with?("]") then
        zekken_number.delete_suffix!("]")
      end

      for i in 1 .. 5 do
        if zekken_number.end_with?("-#{i}") then
          zekken_number.chop!
          zekken_number.chop!
        end
      end

      if (checkZekkenExist(zekken_number) == false) then
        message = {
          type: 'text',
          text: text_noZekkenNumber()
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_noZekkenNumber())
        return "200 OK"
      end

      inputStatus(userId, "zekkenAuthorization", zekken_number)

      message = {
        type: 'text',
        text: text_zekkenRegister(zekken_number)
      }
      client.reply_message(event['replyToken'], message)
      chatLogger(userId, 'bot', 'text', text_zekkenRegister(zekken_number))
      return "200 OK"

    end

    def goalRegister userId, zekken_number, event_code, goal_time, slice_number, client, event

      if cpDoubleCheck(zekken_number, event_code, -1) then
        message = {
          type: 'text',
          text: text_alreadyGoal()
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_alreadyGoal())
        return "200 OK"
      end

      goal_time.slice!(0,slice_number)

      if zekken_number.start_with?('[') then
        zekken_number.delete_prefix!("[")
      end
      if zekken_number.end_with?("]") then
        zekken_number.delete_suffix!("]")
      end

      if goal_time.include?('時') then
        goal_time.gsub!(/時/, ':')
      end
      if goal_time.include?('秒') then
        goal_time.delete_suffix!("秒")
        goal_time.gsub!(/分/, ':')
      end
      if goal_time.include?('分') then
        goal_time.delete_suffix!("分")
      end

      if goal_time.end_with?(':') then
        message = {
          type: 'text',
          text: text_goalError()
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_goalError())
        return "200 OK"
      end

      begin
        goaltime = Time.parse(goal_time)
      rescue => error
        p error
        message = {
          type: 'text',
          text: text_goalError()
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_goalError())
        return "200 OK"
      end

      result = getTeamDataByZekken_number(zekken_number, event_code)

      starttime = Time.parse(getEventStartTime(event_code))

      if result['class_name'].include?('3時間') then
        limittime = starttime + (3 * 60 * 60)
      elsif result['class_name'].include?('5時間') then
        limittime = starttime + (5 * 60 * 60)
      elsif result['class_name'].include?("テスト") || result['class_name'].include?("test") then
      #elsif result['class_name'] == "test" || result['class_name'] == "test2" then
        limittime = starttime + (5 * 60 * 60)
      else
        message = {
          type: 'text',
          text: 'エラーコード：DBS-001\nチーム情報の取得に失敗しました。\nゼッケン番号とこのエラーコードを添えて運営に問い合わせて下さい。'
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', 'エラーコード：DBS-001\nチーム情報の取得に失敗しました。\nゼッケン番号とこのエラーコードを添えて運営に問い合わせて下さい。')
        return "200 OK"
      end

      if limittime < goaltime then
        late_point = goaltime - limittime
        if late_point < 60
          late_point = 50
        else
          late_point = late_point/60 + 1
          late_point = late_point.floor*50
        end
      else
        late_point = 0
      end

      data = {"goal_time" => goal_time, "late_point" => late_point}

      jdata = JSON.generate(data)

      inputStatus(userId,'goal_stanby',jdata)

      message = {
        type: 'text',
        text: text_getGoal(goal_time)
      }
      client.reply_message(event['replyToken'], message)
      chatLogger(userId, 'bot', 'text', text_getGoal(goal_time))

      return "200 OK"
    end

    def agentRegister userId, command, client, event
      p "agentR command : #{command}"
      commandAr = command.split(' ')

      agent_id = agentAuthorization(commandAr[1])

      if (agent_id == "ERROR") then
        message = {
          type: 'text',
          text: text_notAgent()
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_notAgent())
        return "200 OK"
      else
      end

      event_code = commandAr[2]

      event_name = checkEventCode(event_code)

      if event_name == "" then
        message = {
          type: 'text',
          text: text_noEventName()
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_noEventName())

        return "200 OK"
      end

      result = addUserData(userId,agent_id,event_code)
      if result == "UPDATE ERROR" then
        message = {
          type: 'text',
          text: "エラーコード：USR-002\nログインには成功しましたが、チーム情報の紐付けに失敗しました。\n恐れ入りますが、時間をあけて再度パスワードを御入力頂けますでしょうか。\n※このエラーが何度も発生する場合、エラーコード、LINEのユーザー名を添えて、運営に問い合わせて下さい。"
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', "エラーコード：USR-002\nログインには成功しましたが、チーム情報の紐付けに失敗しました。\n恐れ入りますが、時間をあけて再度パスワードを御入力頂けますでしょうか。\n※このエラーが何度も発生する場合、エラーコード、LINEのユーザー名を添えて、運営に問い合わせて下さい。")
        return "200 OK"
      end

      message = {
        type: 'text',
        text: text_agentStart(event_name)
      }
      client.reply_message(event['replyToken'], message)
      chatLogger(userId, 'bot', 'text', text_agentStart(event_name))

      return "200 OK"

    end

    def checkinRegister userId, command, agent_id, event_code, client, event

      commandAr = command.split(' ')
      zekken_number = commandAr[1]

      teamData = getTeamDataByZekken_number(zekken_number, event_code)

      if teamData['result'] == "ERROR" then
        message = {
          type: 'text',
          text: text_zekkenNotExist(zekken_number)
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_agentStart(zekken_number))
        return "200 OK"
      end

      result = getZekkenNumberByAgentId(agent_id)

      if result == "no data" then
        inputAgentLogin(agent_id,zekken_number,event_code)
      else
        changeAgentLogin(agent_id,zekken_number,event_code)
      end

      if (checkAgentFlag(zekken_number, event_code) == false) then

        inputAgentFlag(zekken_number, event_code, agent_id)

      end

      message = {
        type: 'text',
        text: text_agentLogin(teamData["team_name"])
      }
      client.reply_message(event['replyToken'], message)
      chatLogger(userId, 'bot', 'text', text_agentLogin(teamData["team_name"]))
      return "200 OK"

    end

    def checkoutRegister userId, agent_id, event_code, client, event


      result = getZekkenNumberByAgentId(agent_id)

      if result == "no login" || result == "no data" then
        return "200 OK"
      end

      changeAgentLogin(agent_id,"","")

      message = {
        type: 'text',
        text: text_agentLogout()
      }
      client.reply_message(event['replyToken'], message)
      chatLogger(userId, 'bot', 'text', text_agentLogout())
      return "200 OK"

    end

    def insertTentativeRegister userId, zekken_number, event_code, command, client, event

      commandAr = command.split(' ')

      insert_number = commandAr[1].to_i

      cp_number = commandAr[2].to_i

      if insert_number == "" || cp_number == "" then
        message = {
          type: 'text',
          text: text_argumentError_Insert()
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_argumentError_Insert())
        return "200 OK"
      end

      if insert_number <= 0 then
        message = {
          type: 'text',
          text: text_notNaturakNumber()
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_notNaturakNumber())
        return "200 OK"
      end

      if cp_number <= 0 then
        message = {
          type: 'text',
          text: text_errorCPnumber()
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_errorCPnumber())
        return "200 OK"
      end

      photo_point = 0
      if cp_number < 996 then
        photo_point = getPhotoPointByCPNum(cp_number,event_code)
        if cp_number == "no cp error" then
          message = {
            type: 'text',
            text: text_errorNoCP(cp_number)
          }
          client.reply_message(event['replyToken'], message)
          chatLogger(userId, 'bot', 'text', text_errorNoCP(cp_number))
        end
      end

      if cp_number <= 0 then
        message = {
          type: 'text',
          text: text_errorNoCP(cp_number)
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_errorNoCP(cp_number))
        return "200 OK"
      end

      jdata = JSON.generate({"insert_number" => insert_number, "cp_number" => cp_number, "photo_point" => photo_point})

      cp_name = getCPname(cp_number, event_code)

      if cp_name == "no cp error" then
        message = {
          type: 'text',
          text: text_errorNoCP(cp_number)
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_errorNoCP(cp_number))
        return "200 OK"
      end

      if (cpDoubleCheck(zekken_number, event_code, cp_number)) then
        message = {
          type: 'text',
          text: text_registredCP(cp_number,cp_name)
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_registredCP(cp_number,cp_name))
        return "200 OK"
      end

      inputStatus(userId, "insert", jdata)

      message = {
        type: 'text',
        text: text_getCPnumber(cp_number, cp_name)
      }
      client.reply_message(event['replyToken'], message)
      chatLogger(userId, 'bot', 'text', text_getCPnumber(cp_number, cp_name))
      return "200 OK"
    end

    def insertRegister userId, zekken_number, event_code, jdata, image, client, event

      data = JSON.parse(jdata)

      insert_number = data["insert_number"]
      cp_number = data["cp_number"]
      photo_point = data["photo_point"]

      cp_name = getCPname(cp_number, event_code)

      if (cpDoubleCheck(zekken_number, event_code, cp_number)) then
        message = {
          type: 'text',
          text: text_registredCP(cp_number,cp_name)
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_registredCP(cp_number,cp_name))
        return "200 OK"
      end

      gps_list = getGpsList(zekken_number, event_code, "DESC")

      gps_list.each{ |rec|

        sn = rec["serial_number"]

        result = changeSerialNumberInGPSInformation(zekken_number, event_code, sn, sn.to_i + 1)

        if sn.to_i == insert_number.to_i then
          break
        end

      }

      inputCPnumber(zekken_number, event_code, cp_number, image, insert_number)

      result = removeStatus(userId)
      if result == "delete error" then
        for i in 1..10 do
          result = removeStatus(userId)
          if result == "200 OK" then
            break
          end
        end
      end

      if result == "delete error"
        message = {
          type: 'text',
          text: text_importantError("STA-002")
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_importantError("STA-002"))
        return "200 OK"
      end

      message = {
        type: 'text',
        text: text_insertComplete(cp_number,cp_name,insert_number)
      }
      client.reply_message(event['replyToken'], message)
      chatLogger(userId, 'bot', 'text', text_insertComplete(cp_number,cp_name,insert_number))

      return "200 OK"

    end

    def deleteRegister userId, zekken_number, event_code, command, client, event, api_flag = false
      commandAr = command.split(' ')

      c_sn = commandAr[1]

      removeGI(zekken_number, event_code, c_sn)

      gps_list = getGpsList(zekken_number,event_code, "ASC")

      gps_list.each{ |rec|

        sn = rec["serial_number"]

        if sn.to_i > c_sn.to_i then
          result = changeSerialNumberInGPSInformation(zekken_number, event_code, sn, sn.to_i - 1)
        end

      }

      if api_flag == true then
        return "OK"
      end
      message = {
        type: 'text',
        text: text_deleteCP(c_sn)
      }
      client.reply_message(event['replyToken'], message)
      chatLogger(userId, 'bot', 'text', text_deleteCP(c_sn))
      return "200 OK"

    end


    def moveRegister userId, zekken_number, event_code,command, client, event, api_flag = false

      commandAr = command.split(' ')
      old_sn = commandAr[1]
      new_sn = commandAr[2]

      max_sn = getMaxNumByGI(zekken_number,event_code)

      p "max_sn : #{max_sn}, old_sn : #{old_sn}, new_sn : #{new_sn}"

      if new_sn.to_i > max_sn.to_i || old_sn.to_i > max_sn.to_i then
        p "over num error"

        if api_flag then
          return "over num error"
        end

        message = {
          type: 'text',
          text: text_moveOverNum()
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_moveOverNum())
        return "200 OK"

      end

      if old_sn.to_i == new_sn.to_i then
        p "same num error"

        if api_flag then
          return "same num error"
        end

        message = {
          type: 'text',
          text: text_moveSameNum()
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_moveSameNum())
        return "200 OK"

      end

      changeSerialNumberInGPSInformation(zekken_number, event_code, old_sn, -1)

      gps_list = getGpsList(zekken_number, event_code, "ASC")

      gps_list.each{ |rec|

        sn = rec["serial_number"]

        if sn.to_i > old_sn.to_i then
          result = changeSerialNumberInGPSInformation(zekken_number, event_code, sn.to_i, sn.to_i - 1)
        end

      }

      if new_sn.to_i == max_sn.to_i then

      else

        gps_list = getGpsList(zekken_number, event_code, "DESC")

        gps_list.each{ |rec|

          sn = rec["serial_number"]

          result = changeSerialNumberInGPSInformation(zekken_number, event_code, sn.to_i, sn.to_i + 1)

          if sn.to_i == new_sn.to_i then
            break
          end

        }

      end

      changeSerialNumberInGPSInformation(zekken_number, event_code, -1, new_sn.to_i)

      if api_flag then
        return "OK"
      end

      message = {
        type: 'text',
        text: text_moveCP(old_sn, new_sn)
      }
      client.reply_message(event['replyToken'], message)
      chatLogger(userId, 'bot', 'text', text_moveCP(old_sn, new_sn))
      return "200 OK"

    end

    def timeChRegister userId, zekken_number, event_code, command, client, event
      commandAr = command.split(' ')

      goal_time = commandAr[1]

      if zekken_number.start_with?('[') then
        zekken_number.delete_prefix!("[")
      end
      if zekken_number.end_with?("]") then
        zekken_number.delete_suffix!("]")
      end

      if goal_time.include?('時') then
        goal_time.gsub!(/時/, ':')
      end
      if goal_time.include?('秒') then
        goal_time.delete_suffix!("秒")
        goal_time.gsub!(/分/, ':')
      end
      if goal_time.include?('分') then
        goal_time.delete_suffix!("分")
      end

      if goal_time.end_with?(':') then
        message = {
          type: 'text',
          text: text_goalError()
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_goalError())
        return "200 OK"
      end

      begin
        goaltime = Time.parse(goal_time)
      rescue => error
        p error
        message = {
          type: 'text',
          text: text_goalError()
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', text_goalError())
        return "200 OK"
      end

      result = getTeamDataByZekken_number(zekken_number, event_code)

      starttime = Time.parse(getEventStartTime(event_code))

      if result['class_name'].include?('3時間') then
        limittime = starttime + (3 * 60 * 60)
      elsif result['class_name'].include?('5時間') then
        limittime = starttime + (5 * 60 * 60)
      elsif result['class_name'].include?("テスト") || result['class_name'].include?("test") then
      #elsif result['class_name'] == "test" then
        limittime = starttime + (5 * 60 * 60)
      else
        message = {
          type: 'text',
          text: 'エラーコード：DBS-001\nチーム情報の取得に失敗しました。\nゼッケン番号とこのエラーコードを添えて運営に問い合わせて下さい。'
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', 'エラーコード：DBS-001\nチーム情報の取得に失敗しました。\nゼッケン番号とこのエラーコードを添えて運営に問い合わせて下さい。')
        return "200 OK"
      end

      if limittime < goaltime then
        late_point = goaltime - limittime
        if late_point < 60
          late_point = 50
        else
          late_point = late_point/60 + 1
          late_point = late_point.floor*50
        end
      else
        late_point = 0
      end

      result = changeGoalTimeAndLatePoint(zekken_number, event_code, goal_time, late_point)

      if result == "UPDATE ERROR" then
        message = {
          type: 'text',
          text: "システム的なエラーにより時刻の更新に失敗しました。もう一度やり直すか、このエラーが連続する場合、システム管理者に連絡してください。"
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', "システム的なエラーにより時刻の更新に失敗しました。もう一度やり直すか、このエラーが連続する場合、システム管理者に連絡してください。")
        return "200 OK"
      end

      message = {
        type: 'text',
        text: text_goalUpdate(goal_time)
      }
      client.reply_message(event['replyToken'], message)
      chatLogger(userId, 'bot', 'text', text_goalUpdate(goal_time))
      return "200 OK"

    end

    def reprinter userId, zekken_number, event_code, client, event

      message = {
        type: 'text',
        text: text_reprint()
      }
      client.reply_message(event['replyToken'], message)
      chatLogger(userId, 'bot', 'text', text_reprint())

      Thread.new do
        makeScoreboardThread(zekken_number, event_code, "true")
      end

    end

    def listUpper userId, zekken_number, event_code, client, event

      origin_list = getGpsList(zekken_number, event_code, "ASC")

      message_text = "チェックポイント通過リスト\n"

      origin_list.each { |rec|
        if rec["cp_number"].to_i == -1 then
          message_text += "【#{rec["serial_number"]}】ゴールタイム：#{rec["goal_time"]} 減点：#{rec["late_point"]}"
          break
        else
          if rec["buy_point"].to_i == 0 then
            point = rec["photo_point"]
          else
            point = rec["buy_point"]
          end
          message_text += "【#{rec["serial_number"]}】#{rec["cp_number"]}:#{rec["cp_name"]} 得点:#{point}\n"
        end
      }

      message = {
        type: 'text',
        text: message_text
      }
      client.reply_message(event['replyToken'], message)
      chatLogger(userId, 'bot', 'text', message_text)

      return "200 OK"

    end

    def startRegister userId, zekken_number, client, event
      inputStatus(userId, "start", "nil")

      message = {
        type: 'text',
        text: text_startStand()
      }
      client.reply_message(event['replyToken'], message)
      chatLogger(userId, 'bot', 'text', text_startStand())
      return "200 OK"
    end

#====================================
# Scoreboard
#====================================

    def makeScoreboardThread zekken_number, event_code, reprintF = false, budleF = false

      item = {"zekken_number" => zekken_number, "event_code" => event_code, "reprintF" => reprintF}

      itemJSON = JSON.generate(item)

      p "queue add : #{itemJSON}"

      inputPrintQueueMemory(itemJSON)

      $queue << itemJSON

    end

    def makeScoreboard zekken_number, event_code, reprintF = false, budleF = false
      filepath = getScoreboardGeneral(zekken_number, event_code)

      if filepath == "no_data" then
        return "no data error"
      end

      if reprintF then
        date_memory = DateTime.now.to_s.gsub(':','-').gsub('+','_')
        nextpath = set_commonbase() + "/#{event_code}" + "/scoreboard/#{zekken_number}_scoreboard_" + date_memory + ".xlsx"
      else
        nextpath = set_commonbase() + "/#{event_code}" + "/scoreboard/#{zekken_number}_scoreboard.xlsx"
      end

      if( !Dir.exist?(set_commonbase() + "/#{event_code}" + "/scoreboard") )then
        if( Dir.mkdir( set_commonbase() + "/#{event_code}" + "/scoreboard",0755 ) == 0 ) then
          #p "mkdir #{commonbase}"
        end
      end

      FileUtils.mv(filepath,nextpath)

      docpath_user = set_commonbase() + "/#{event_code}" + "/scoreboard"
      pdffile = "#{zekken_number}_scoreboard"

      if reprintF then
        pdffile = pdffile + '_' + date_memory
      end

      command ="/home/mobilousInstance/jodconverter-cli-4.4.5-SNAPSHOT/bin/jodconverter-cli -o #{docpath_user}/#{pdffile}.xlsx #{docpath_user}/#{pdffile}.pdf" #郡上のイベント後はこちらの仕様に置き換える
      system( command )
      puts command
      #system("echo 'sumasen@123' |sudo -S /var/www/lib/generatePDF.sh #{docpath_user} #{pdffile}")

      aws_url = s3Uploader("#{event_code}/scoreboard", docpath_user, pdffile + '.pdf')

      if getFinalReport(zekken_number, event_code) == "no report" then
        inputFinalReport(zekken_number, event_code, aws_url)
      else
        changeFinalReport(zekken_number, event_code, aws_url)
      end

      # File.delete(nextpath)
      if budleF == false then
        # File.delete(nextpath.gsub('.xlsx','.pdf'))
        return nextpath.gsub('.xlsx','.pdf')
      end
    end

    def getScoreboardGeneral zekken_number, event_code
      #usernameエラーで引っかかる
      #
      #$bean.read_bean
      #username = $bean.username
      username = 'test'

      record = {}

      record["zekken_number"] = zekken_number

      result = getGpsInfo(zekken_number, event_code)

      if result  == [] then
        return "no_data"
      end

      count = 0

      result.each { |rec|
        count += 1
      }

      result2 = getPhotoFromRR(zekken_number, event_code, count)

      if result2 == {} || result2["result"] == "ERROR" then

        return "no_data"

      end

      record.merge!(result2)

      if count <= 60 then
        document = "scoreboardGeneral"
      else
        document = "scoreboardGeneral2"
      end

      count = count * 2 + 1

      record["num_image"] = count

      ExcelObject.resetClass

      doc = AppExeExcelDoc.new
      #doc = AppExeGeneralDocument.new
      doc.init( username,0,document,"jp","/var/www/docbase","/home/mobilousInstance" )	# <== "/home/mobilousInstance" を出力先のベースフォルダーにしてください。

      pgconn=UserPostgres.new
      dbname = set_dbname()
      pgconn.connectPg("localhost","mobilous",0,dbname)

      rec = JSON.parse( JSON.generate(record) )
            #@sheetname = sheet
      #filename = doc.makeReport( pgconn,rec,"outfile.xlsx",0 )	# ==> Use ini file defnition 2021-11-15
      doc.makeReport( pgconn,rec )

      # make a excel file under /var/www/docbase/(@username)/(@projectno)/

      #file_path = filename.to_s.gsub(/wrote excel file on /,"")
      #puts "=========> file_path = #{file_path}"

      filename = doc.outfile
      file_path = doc.outpath
      puts "=========> file_path = #{file_path}, filename = #{filename}"

      return file_path

    end

#====================================
# rogSimResult
#====================================

    def makeRogSimResultSheet userId, event_code
      filepath = getRogSimResultSheet(userId, event_code)

      if filepath == "no_data" then
        return "no data error"
      end

      nextpath = set_commonbase() + "/#{event_code}" + "/rogeSimResult/#{userId}_rogeSimResult_#{DateTime.now.to_s.gsub(':','-').gsub('+','_')}.xlsx"

      if( !Dir.exist?(set_commonbase() + "/#{event_code}" + "/rogeSimResult") )then
        if( Dir.mkdir( set_commonbase() + "/#{event_code}" + "/rogeSimResult",0755 ) == 0 ) then
          #p "mkdir #{commonbase}"
        end
      end

      FileUtils.mv(filepath,nextpath)

      docpath_user = set_commonbase() + "/#{event_code}" + "/rogeSimResult"
      pdffile = "#{userId}_rogeSimResult_#{DateTime.now.to_s.gsub(':','-').gsub('+','_')}"

      command ="/home/mobilousInstance/jodconverter-cli-4.4.5-SNAPSHOT/bin/jodconverter-cli -o #{docpath_user}/#{pdffile}.xlsx #{docpath_user}/#{pdffile}.pdf" #郡上のイベント後はこちらの仕様に置き換える
      system( command )
      puts command
      #system("echo 'sumasen@123' |sudo -S /var/www/lib/generatePDF.sh #{docpath_user} #{pdffile}")

      aws_url = s3Uploader("#{event_code}/rogeSimResult", docpath_user, pdffile + '.pdf')

      return aws_url
    end

    def getRogSimResultSheet userId, event_code
      #usernameで引っかかる
      #$bean.read_bean
      #username = $bean.username
      username = 'test'

      record = {}

      record["user_serial"] = userId
      record["event_code"] = event_code

      result = getRogSimResult(userId, event_code)

      if result  == [] then
        return "no_data"
      end

      count = 0

      result.each { |rec|
        count += 1
      }

      result2 = getPhotoFromRSV(userId, event_code, count)

      if result2 == {} || result2["result"] == "ERROR" then

        return "no_data"

      end

      record.merge!(result2)

      document = "rogeSimResult"

      count = count + 1

      record["num_image"] = count

      ExcelObject.resetClass

      doc = AppExeExcelDoc.new
      #doc = AppExeGeneralDocument.new
      doc.init( username,0,document,"jp","/var/www/docbase","/home/mobilousInstance" )	# <== "/home/mobilousInstance" を出力先のベースフォルダーにしてください。

      pgconn=UserPostgres.new
      dbname = set_dbname()
      pgconn.connectPg("localhost","mobilous",0,dbname)

      rec = JSON.parse( JSON.generate(record) )
            #@sheetname = sheet
      #filename = doc.makeReport( pgconn,rec,"outfile.xlsx",0 )	# ==> Use ini file defnition 2021-11-15
      doc.makeReport( pgconn,rec )

      # make a excel file under /var/www/docbase/(@username)/(@projectno)/

      #file_path = filename.to_s.gsub(/wrote excel file on /,"")
      #puts "=========> file_path = #{file_path}"
      pgconn.disconnect
      filename = doc.outfile
      file_path = doc.outpath
      puts "=========> file_path = #{file_path}, filename = #{filename}"

      return file_path

    end

#====================================
# save / upload
#====================================

    def s3Uploader data_dir, filepath, filename

      bucket = s3_bucket().freeze
      region = s3_region().freeze
      access_key = s3_key().freeze
      secret_key = s3_Skey().freeze

      aws_storage = S3_StorageUtil.new( access_key,secret_key,region,bucket,data_dir )
      aws_bucket = S3_FolderUtil.new( "offlineRecog/work",aws_storage )

      originPath = filepath
      destPath = data_dir
      filename = filename

      aws_bucket.uploadFile( originPath,destPath,filename )

      aws_url = s3_domain() + '/' + data_dir + '/' + filename

      return aws_url

    end

    def photoSaver zekken_number, event_code, response

      commonbase = set_commonbase() + '/' + event_code
      commonbase2 = set_commonbase2() + '/' + event_code

      docpath = commonbase + '/' + zekken_number
      docpath2 = commonbase2 + '/' + zekken_number

      if( !Dir.exist?(docpath) )then
        if( Dir.mkdir( docpath,0755 ) == 0 ) then
          #p "mkdir #{commonbase}"
        end
      end

      filename = DateTime.now.to_s.gsub(':','-').gsub('+','_') + '.png'

      fullpath = docpath + '/' + filename
      fullpath2 = docpath2 + '/' + filename

      open(fullpath, 'w') do |pass|
        pass.write(response.body)
      end

      fullpath2 = s3Uploader("#{event_code}/#{zekken_number}",docpath,filename)

      filenameTh = filename.gsub(".png","_th.png")

      imageOri = Magick::Image.read(fullpath).first
      imageTh = imageOri.scale(image_size_width(), image_size_height())
      imageTh.write("temp/#{filenameTh}")

      nextpath = docpath + '/' + filenameTh

      FileUtils.mv("/var/www/temp/#{filenameTh}",nextpath)
      # File.delete(fullpath)

      return fullpath2

    end

    def chatLogger userId, talker, type, detail

      p "#{talker} : #{detail}"

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)

      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'chat_log' )

      title = ["userid","talker","message_type","message_detail","create_at"]
      record = {"userid" => userId, "talker" => talker, "message_type" => type, "message_detail" => detail, "create_at" => DateTime.now}

      sql = anytable.makeInsertKeySet(title,record)
      begin
        ret = anytable.exec(sql)         # true or false
      rescue => error
        p error
      end

      @pgconn.disconnect

      return "OK"

    end

        end

  def self.registered( app )
    app.helpers MobServer_gifuroge::Helpers

    app.get "/test_gifuroge" do
      crossdomain
      headjson
      result = "git push test from development to production3"
      p result
    end

  
  def calculateMonthlyScore(zekken_number, event_code)
    @pgconn = UserPostgres.new
    dbname = set_dbname()
    @pgconn.connectPg("localhost", "mobilous", 0, dbname)
    anytable = UserPostgresTable.new
    anytable.useTable(@pgconn, 'gps_information')
  
    # 現在の月の開始日と終了日を取得
    today = Date.today
    start_date = today.beginning_of_month
    end_date = today.end_of_month
  
    # 一ヶ月分のデータを取得
    where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}' " \
            "AND create_at >= '#{start_date}' AND create_at <= '#{end_date}' " \
            "ORDER BY create_at ASC"
    records = anytable.find2(where)
  
    @pgconn.disconnect
  
    # 日ごとにグループ化して得点を計算
    daily_scores = {}
    (start_date..end_date).each do |date|
      daily_records = records.select { |r| Date.parse(r['create_at']) == date }
      
      start_index = daily_records.index { |r| r['cp_number'] == -2 }
      end_index = daily_records.index { |r| r['cp_number'] == -1 }
      
      if start_index && end_index && start_index < end_index
        valid_records = daily_records[start_index+1...end_index]
        score = valid_records.sum { |r| r['photo_point'].to_i }
        daily_scores[date] = score
      else
        daily_scores[date] = 0
      end
    end
  
    # 月間の合計得点を計算
    total_monthly_score = daily_scores.values.sum
  
    return {
      daily_scores: daily_scores,
      total_monthly_score: total_monthly_score
    }
  end


#====================================
# LINEbot本体
#====================================

    app.post '/callback_gifuroge' do
      crossdomain
      # p "call back!"
      body = request.body.read
      # p "body read!"
      signature = request.env['HTTP_X_LINE_SIGNATURE']
      # p "signature read!"
      unless client.validate_signature(body, signature)
        p "400 Bad Request"
        error 400 do 'Bad Request' end
      end
      # p "not 400!"
      events = client.parse_events_from(body)
      # p "event read!"
      events.each do |event|
        userId = event['source']['userId']
        userdata = getUserName(userId)
        if userdata["user_name"] == "get name error" then
          message = {
            type: 'text',
            text: "エラーコード：USR-001\nユーザー名の取得に失敗しました。\n※このエラーが何度も発生する場合、エラーコードとゼッケン番号を添えて、運営に問い合わせて下さい。"
          }
          client.reply_message(event['replyToken'], message)
          chatLogger(userId, 'bot', 'text', "エラーコード：USR-001\nユーザー名の取得に失敗しました。\n※このエラーが何度も発生する場合、エラーコードとゼッケン番号を添えて、運営に問い合わせて下さい。")
          return "200 OK"
        end
        zekken_number = userdata["zekken_number"]
        event_code = userdata["event_code"]
        p "username : #{userdata["user_name"]} ( #{zekken_number} ( #{event_code} ) )"
        case event
        when Line::Bot::Event::Message
          notZekken = false
          status = checkStatus(userId)

          if zekken_number == "" then
            status = checkStatus(userId)
            if status['status'] != "zekkenAuthorization" then
              notZekken = true
            end
          end

          agentF = false
          agent_id = ""
          if zekken_number != nil then
            if (zekken_number.start_with?('a')) then
              agent_id = zekken_number
              zekken_number = getZekkenNumberByAgentId(zekken_number)
              agentF = true
              if status['status'] == "Goal" then
                result = removeStatus(userId)
                if result == "delete error" then
                  for i in 1..10 do
                    result = removeStatus(userId)
                    if result == "200 OK" then
                      break
                    end
                  end
                end
                status['status'] = "nothing"

                if result == "delete error"
                  message = {
                    type: 'text',
                    text: text_importantError("STA-002")
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', text_importantError("STA-002"))
                  return "200 OK"
                end
              end
            end
          end

          case event.type
          when Line::Bot::Event::MessageType::Text
            chatLogger(userId, 'user', 'text', event.message['text'])

            command = NKF.nkf('-w -Z4', NKF.nkf('--katakana -w', event.message['text'])).upcase # この一行により入力された内容は全て半角かつ、ひらがなはカタカナに、小文字アルファベットは大文字に、変換される
            if status['status'] == "Goal" then
              if command.to_s.start_with?('AGENT') then
                commandAr = event.message['text'].split(' ')

                agent_id = agentAuthorization(commandAr[1])

                if (agent_id == "ERROR") then
                  message = {
                    type: 'text',
                    text: text_notAgent()
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', text_notAgent())
                  return "200 OK"
                else
                end

                result = removeStatus(userId)
                if result == "delete error" then
                  for i in 1..10 do
                    result = removeStatus(userId)
                    if result == "200 OK" then
                      break
                    end
                  end
                end

                if result == "delete error"
                  message = {
                    type: 'text',
                    text: text_importantError("STA-002")
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', text_importantError("STA-002"))
                  return "200 OK"
                end
                agentRegister(userId,event.message['text'].to_s,client,event)
              elsif (command == 'ﾛｸﾞｱｳﾄ' || command == 'LOGOUT') then
                result = addUserData(userId, "", "")
                if result == "UPDATE ERROR" then
                  message = {
                    type: 'text',
                    text: "エラーコード：USR-003\nログアウトの処理に失敗しました。\n恐れ入りますが、時間をあけてもう一度やり直して下さい。\n※このエラーが何度も発生する場合、LINEのユーザー名とエラーコードを運営に問い合わせて下さい。"
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', "エラーコード：USR-003\nログアウトの処理に失敗しました。\n恐れ入りますが、時間をあけてもう一度やり直して下さい。\n※このエラーが何度も発生する場合、LINEのユーザー名とエラーコードを運営に問い合わせて下さい。")
                  return "200 OK"
                end
                result = removeStatus(userId)
                if result == "delete error" then
                  for i in 1..10 do
                    result = removeStatus(userId)
                    if result == "200 OK" then
                      break
                    end
                  end
                end

                if result == "delete error"
                  message = {
                    type: 'text',
                    text: text_importantError("STA-002")
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', text_importantError("STA-002"))
                  return "200 OK"
                end
                message = {
                  type: 'text',
                  text: text_loguotSuccess()
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_loguotSuccess())
                return "200 OK"
              elsif (command.start_with?('ｾﾞｯｹﾝ番号:')) then
                result = removeStatus(userId)
                if result == "delete error" then
                  for i in 1..10 do
                    result = removeStatus(userId)
                    if result == "200 OK" then
                      break
                    end
                  end
                end

                if result == "delete error"
                  message = {
                    type: 'text',
                    text: text_importantError("STA-002")
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', text_importantError("STA-002"))
                  return "200 OK"
                end

                zekkenNumberRegister(userId, command.to_s, event_code, 8, client,event)
              elsif (command.to_s.start_with?('ｾﾞｯｹﾝ:')) then
                result = removeStatus(userId)
                if result == "delete error" then
                  for i in 1..10 do
                    result = removeStatus(userId)
                    if result == "200 OK" then
                      break
                    end
                  end
                end

                if result == "delete error"
                  message = {
                    type: 'text',
                    text: text_importantError("STA-002")
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', text_importantError("STA-002"))
                  return "200 OK"
                end

                zekkenNumberRegister(userId, command.to_s, event_code, 6, client,event)
              elsif (command.to_s.start_with?('TIMECH')) then
                if (agentF) then
                  timeChRegister(userId, zekken_number, event_code, command, client, event)
                end
              elsif (command.to_s.start_with?('時間変更')) then
                if (agentF) then
                  timeChRegister(userId, zekken_number, command, client, event)
                end
              elsif (command.to_s.start_with?('REPRINT')) then
                if (agentF) then
                  reprinter(userId, zekken_number, event_code, client, event)
                end
              elsif (command.to_s.start_with?('再印刷')) then
                if (agentF) then
                  reprinter(userId, zekken_number, event_code, client, event)
                end
              elsif (command.to_s.start_with?('LIST')) then
                if (agentF) then
                  listUpper(userId, zekken_number, event_code, client, event)
                end
              elsif (command.to_s.start_with?('一覧')) then
                if (agentF) then
                  listUpper(userId, zekken_number, event_code, client, event)
                end
              else
                message = {
                  type: 'text',
                  text: text_alreadyGoal()
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_alreadyGoal())
                return "200 OK"
              end
            end

            if notZekken then
              if (command.start_with?('ｾﾞｯｹﾝ番号:')) then
                zekkenNumberRegister(userId, command.to_s, event_code, 8, client,event)
              elsif (command.to_s.start_with?('ｾﾞｯｹﾝ:')) then
                zekkenNumberRegister(userId, command.to_s, event_code, 6, client,event)
              elsif (command.to_s.start_with?('AGENT')) then
                agentRegister(userId,event.message['text'].to_s,client,event)
                return "200 OK"
              elsif (command.to_s.start_with?('代理人')) then
                agentRegister(userId,event.message['text'].to_s,client,event)
                return "200 OK"
              elsif (command.to_s.start_with?('CHECKIN')) then
                if (agentF) then
                  checkinRegister(userId,command.to_s,agent_id,event_code,client,event)
                end
              elsif (command.to_s.start_with?('代理ﾛｸﾞｲﾝ')) then
                if (agentF) then
                  checkinRegister(userId,command.to_s,agent_id,event_code,client,event)
                end
              elsif (command.to_s.start_with?('CHECKOUT')) then
                if (agentF) then
                  checkoutRegister(userId,agent_id,event_code,client,event)
                end
              elsif (command.to_s.start_with?('代理ﾛｸﾞｱｳﾄ')) then
                if (agentF) then
                  checkoutRegister(userId,agent_id,event_code,client,event)
                end
              else
                message = {
                  type: 'text',
                  text: text_noZekken1(userdata["user_name"])
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_noZekken1(userdata["user_name"]))
                message = {
                  type: 'text',
                  text: text_noZekken2()
                }
                client.push_message(userId, message)
                chatLogger(userId, 'bot', 'text', text_noZekken2())
                message = {
                  type: 'text',
                  text: text_noZekken3()
                }
                client.push_message(userId, message)
                chatLogger(userId, 'bot', 'text', text_noZekken3())
                return "200 OK"
              end
            end

            case status['status']
            when 'nothing'
              if (command.start_with?('ｾﾞｯｹﾝ番号:')) then
                zekkenNumberRegister(userId, command.to_s, event_code, 8, client,event)
              elsif (command.to_s.start_with?('ｾﾞｯｹﾝ:')) then
                zekkenNumberRegister(userId, command.to_s, event_code, 6, client,event)
              elsif (command.to_s.start_with?('CP番号:')) then
                cpNumberRegister(userId, zekken_number, event_code, command.to_s, 5, client,event)
              elsif (command.to_s.start_with?('CP:')) then
                cpNumberRegister(userId, zekken_number, event_code, command.to_s, 3, client,event)
              elsif (command.to_s.start_with?('ADD')) then
                cp_number = command.split(' ')[1]
                cpNumberRegister(userId, zekken_number, event_code, cp_number.to_s, 0, client,event)
              elsif (command.to_s.start_with?('追加')) then
                cp_number = command.split(' ')[1]
                cpNumberRegister(userId, zekken_number, event_code, cp_number.to_s, 0, client,event)
              elsif (command.to_s.start_with?('ｺﾞｰﾙ:') || command.to_s.start_with?('GOAL:')) then
                goalRegister(userId,zekken_number, event_code, command.to_s, 5, client,event)
              elsif (command.to_s.start_with?('ｺﾞｰﾙ ') || command.to_s.start_with?('GOAL ')) then
                command = command.split(' ')[1]
                goalRegister(userId,zekken_number, event_code, command.to_s, 0, client,event)
              elsif (command.to_s.start_with?('ｺﾞ:')) then
                goalRegister(userId,zekken_number, event_code, command.to_s, 3, client,event)
              elsif (command.to_s.start_with?('G:')) then
                goalRegister(userId,zekken_number, event_code, command.to_s, 2, client,event)
              elsif (command.to_s.start_with?('ｽﾀｰﾄ')) then
                startRegister(userId,zekken_number,client,event)
              elsif (command.to_s.start_with?('START')) then
                startRegister(userId,zekken_number,client,event)
              elsif (command.to_s.start_with?('AGENT')) then
                agentRegister(userId,event.message['text'].to_s,client,event)
              elsif (command.to_s.start_with?('代理人')) then
                agentRegister(userId,event.message['text'].to_s,client,event)
              elsif (command.to_s.start_with?('CHECKIN')) then
                if (agentF) then
                  checkinRegister(userId,command.to_s,agent_id,event_code,client,event)
                end
              elsif (command.to_s.start_with?('代理ﾛｸﾞｲﾝ')) then
                if (agentF) then
                  checkinRegister(userId,command.to_s,agent_id,event_code,client,event)
                end
              elsif (command.to_s.start_with?('CHECKOUT')) then
                if (agentF) then
                  checkoutRegister(userId,agent_id,event_code,client,event)
                end
              elsif (command.to_s.start_with?('代理ﾛｸﾞｱｳﾄ')) then
                if (agentF) then
                  checkoutRegister(userId,agent_id,event_code,client,event)
                end
              elsif (command.to_s.start_with?('INSERT')) then
                if (agentF) then
                  insertTentativeRegister(userId, zekken_number,event_code, command, client, event)
                end
              elsif (command.to_s.start_with?('挿入')) then
                if (agentF) then
                  insertTentativeRegister(userId, zekken_number, event_code, command, client, event)
                end
              elsif (command.to_s.start_with?('DELETE')) then
                if (agentF) then
                  deleteRegister(userId, zekken_number, event_code, command, client, event)
                end
              elsif (command.to_s.start_with?('削除')) then
                if (agentF) then
                  deleteRegister(userId, zekken_number, event_code, command, client, event)
                end
              elsif (command.to_s.start_with?('TIMECH')) then
                if (agentF) then
                  timeChRegister(userId, zekken_number, event_code, command, client, event)
                end
              elsif (command.to_s.start_with?('時間変更')) then
                if (agentF) then
                  timeChRegister(userId, zekken_number, event_code, command, client, event)
                end
              elsif (command.to_s.start_with?('REPRINT')) then
                if (agentF) then
                  reprinter(userId, zekken_number, event_code, client, event)
                end
              elsif (command.to_s.start_with?('再印刷')) then
                if (agentF) then
                  reprinter(userId, zekken_number, event_code, client, event)
                end
              elsif (command.to_s.start_with?('LIST')) then
                if (agentF) then
                  listUpper(userId, zekken_number, event_code, client, event)
                end
              elsif (command.to_s.start_with?('一覧')) then
                if (agentF) then
                  listUpper(userId, zekken_number, event_code, client, event)
                end
              elsif (command.to_s.start_with?('MOVE')) then
                if (agentF) then
                  moveRegister(userId, zekken_number, event_code, command, client, event)
                end
              elsif (command.to_s.start_with?('移動')) then
                if (agentF) then
                  moveRegister(userId, zekken_number, event_code, command, client, event)
                end
              elsif (command == 'ﾛｸﾞｱｳﾄ' || command == 'LOGOUT') then
                result = addUserData(userId, "", "")
                if result == "UPDATE ERROR" then
                  message = {
                    type: 'text',
                    text: "エラーコード：USR-003\nログアウトの処理に失敗しました。\n恐れ入りますが、時間をあけてもう一度やり直して下さい。\n※このエラーが何度も発生する場合、LINEのユーザー名とエラーコードを運営に問い合わせて下さい。"
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', "エラーコード：USR-003\nログアウトの処理に失敗しました。\n恐れ入りますが、時間をあけてもう一度やり直して下さい。\n※このエラーが何度も発生する場合、LINEのユーザー名とエラーコードを運営に問い合わせて下さい。")
                  return "200 OK"
                end
                message = {
                  type: 'text',
                  text: text_loguotSuccess()
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_loguotSuccess())
                return "200 OK"
              else # CP登録と仮定する

                if (command.to_s.start_with?('#')) then
                  command.delete_prefix!('#')
                end

                cp_number = command.to_i

                if cp_number <= 0 then
                  return "200 OK"
                end

                cpNumberRegister(userId, zekken_number, event_code, cp_number, 0, client,event)

              end
            when "zekkenAuthorization"
              if command.to_s.start_with?('PASSWORD:') then
                password = event.message['text'].to_s.slice(0,9)
              elsif command.to_s.start_with?('ﾊﾟｽﾜｰﾄﾞ:') then
                password = event.message['text'].to_s.slice(0,6)
              elsif (command == 'NO' || command == "ｷｬﾝｾﾙ" || command == "CANCEL" || command == "ｲｲｴ" || command == "ﾉｰ") then
                result = removeStatus(userId)
                if result == "delete error" then

                  message = {
                    type: 'text',
                    text: "エラーコード：STA-002\nパスワード受付のキャンセルに失敗しました。\n恐れ入りますが、時間をあけてもう一度やり直して下さい。\n※このエラーが何度も発生する場合、LINEのユーザー名とエラーコードを運営に問い合わせて下さい。"
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', "エラーコード：STA-002\nパスワード受付のキャンセルに失敗しました。\n恐れ入りますが、時間をあけてもう一度やり直して下さい。\n※このエラーが何度も発生する場合、LINEのユーザー名とエラーコードを運営に問い合わせて下さい。")
                  return "200 OK"

                end

                message = {
                  type: 'text',
                  text: text_AuthorizationCancel()
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_AuthorizationCancel())

                return "200 OK"

              else
                password = event.message['text']
              end
              result = zekkenAuthorization(status['memory'], password)
              if result["result"] == "OK"

                if getUserIdByZekkenNumber(result['zekken_number'],result["event_code"]) != "no zekken number" then
                  message = {
                    type: 'text',
                    text: text_alreadyZekken()
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', text_alreadyZekken())

                  return "200 OK"
                end

                result2 = addUserData(userId,result['zekken_number'],result["event_code"])
                if result2 == "UPDATE ERROR" then
                  message = {
                    type: 'text',
                    text: "エラーコード：USR-002\nログインには成功しましたが、チーム情報の紐付けに失敗しました。\n恐れ入りますが、時間をあけて再度パスワードを御入力頂けますでしょうか。\n※このエラーが何度も発生する場合、エラーコード、LINEのユーザー名を添えて、運営に問い合わせて下さい。"
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', "エラーコード：USR-002\nログインには成功しましたが、チーム情報の紐付けに失敗しました。\n恐れ入りますが、時間をあけて再度パスワードを御入力頂けますでしょうか。\n※このエラーが何度も発生する場合、エラーコード、LINEのユーザー名を添えて、運営に問い合わせて下さい。")
                  return "200 OK"
                end

                result3 = removeStatus(userId)
                if result3 == "delete error" then
                  for i in 1..10 do
                    result3 = removeStatus(userId)
                    if result3 == "200 OK" then
                      break
                    end
                  end
                end

                if result3 == "delete error"
                  message = {
                    type: 'text',
                    text: text_importantError("STA-002")
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', text_importantError("STA-002"))
                  return "200 OK"
                end

                message = {
                  type: 'text',
                  text: text_teamRegister(userdata["user_name"], result['team_name'])
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_teamRegister(userdata["user_name"], result['team_name']))


              elsif result["result"] == "ERROR"
                message = {
                  type: 'text',
                  text: text_passwordError()
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_passwordError())

              end

            when "cp_stanby"

              if (command == 'NO' || command == "ｷｬﾝｾﾙ" || command == "ｲｲｴ" || command == "ﾉｰ") then
                result = removeStatus(userId)
                if result == "delete error" then

                  message = {
                    type: 'text',
                    text: "エラーコード：STA-003\n写真受付のキャンセルに失敗しました。\n恐れ入りますが、時間をあけてもう一度やり直して下さい。\n※このエラーが何度も発生する場合、ゼッケン番号とエラーコードを運営に問い合わせて下さい。"
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', "エラーコード：STA-003\n写真受付のキャンセルに失敗しました。\n恐れ入りますが、時間をあけてもう一度やり直して下さい。\n※このエラーが何度も発生する場合、ゼッケン番号とエラーコードを運営に問い合わせて下さい。")
                  return "200 OK"

                end

                message = {
                  type: 'text',
                  text: text_AuthorizationCancel()
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_AuthorizationCancel())

                return "200 OK"
              end

            when "goal_stanby"

              if (command == 'NO' || command == "ｷｬﾝｾﾙ" || command == "ｲｲｴ" || command == "ﾉｰ") then
                result = removeStatus(userId)
                if result == "delete error" then

                  message = {
                    type: 'text',
                    text: "エラーコード：STA-003\n写真受付のキャンセルに失敗しました。\n恐れ入りますが、時間をあけてもう一度やり直して下さい。\n※このエラーが何度も発生する場合、ゼッケン番号とエラーコードを運営に問い合わせて下さい。"
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', "エラーコード：STA-003\n写真受付のキャンセルに失敗しました。\n恐れ入りますが、時間をあけてもう一度やり直して下さい。\n※このエラーが何度も発生する場合、ゼッケン番号とエラーコードを運営に問い合わせて下さい。")
                  return "200 OK"

                end

                message = {
                  type: 'text',
                  text: text_AuthorizationCancel()
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_AuthorizationCancel())

                return "200 OK"
              end

            when "insert"

              if (command == 'NO' || command == "ｷｬﾝｾﾙ" || command == "ｲｲｴ" || command == "ﾉｰ") then
                result = removeStatus(userId)
                if result == "delete error" then

                  message = {
                    type: 'text',
                    text: "エラーコード：STA-003\n写真受付のキャンセルに失敗しました。\n恐れ入りますが、時間をあけてもう一度やり直して下さい。\n※このエラーが何度も発生する場合、ゼッケン番号とエラーコードを運営に問い合わせて下さい。"
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', "エラーコード：STA-003\n写真受付のキャンセルに失敗しました。\n恐れ入りますが、時間をあけてもう一度やり直して下さい。\n※このエラーが何度も発生する場合、ゼッケン番号とエラーコードを運営に問い合わせて下さい。")
                  return "200 OK"

                end

                message = {
                  type: 'text',
                  text: text_AuthorizationCancel()
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_AuthorizationCancel())

                return "200 OK"
              end

            when "start"

              if (command == 'NO' || command == "ｷｬﾝｾﾙ" || command == "ｲｲｴ" || command == "ﾉｰ") then
                result = removeStatus(userId)
                if result == "delete error" then

                  message = {
                    type: 'text',
                    text: "エラーコード：STA-003\nスタートのキャンセルに失敗しました。\n恐れ入りますが、時間をあけてもう一度やり直して下さい。\n※このエラーが何度も発生する場合、ゼッケン番号とエラーコードを運営に問い合わせて下さい。"
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', "エラーコード：STA-003\nスタートのキャンセルに失敗しました。\n恐れ入りますが、時間をあけてもう一度やり直して下さい。\n※このエラーが何度も発生する場合、ゼッケン番号とエラーコードを運営に問い合わせて下さい。")
                  return "200 OK"

                end

                message = {
                  type: 'text',
                  text: text_AuthorizationCancel()
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_AuthorizationCancel())

                return "200 OK"

              elsif (command == 'YES' || command == 'ﾊｲ' || command == 'ｲｴｽ') then

                removeAllGI(zekken_number, event_code)

                inputCPnumber(zekken_number,event_code,-2,set_originbase() + '/start.png')

                result = removeStatus(userId)
                if result == "delete error" then
                  for i in 1..10 do
                    result = removeStatus(userId)
                    if result == "200 OK" then
                      break
                    end
                  end
                end

                if result == "delete error"
                  message = {
                    type: 'text',
                    text: text_importantError("STA-002")
                  }
                  client.reply_message(event['replyToken'], message)
                  chatLogger(userId, 'bot', 'text', text_importantError("STA-002"))
                  return "200 OK"
                end

                message = {
                  type: 'text',
                  text: text_startRun()
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_startRun())
                return "200 OK"

              end

            end
          when Line::Bot::Event::MessageType::Image
            if notZekken then
              chatLogger(userId, 'user', 'image', '[保存しなかった画像ファイル]')
              message = {
                type: 'text',
                text: text_noZekken1(userdata["user_name"])
              }
              client.reply_message(event['replyToken'], message)
              chatLogger(userId, 'bot', 'text', text_noZekken1(userdata["user_name"]))
              message = {
                type: 'text',
                text: text_noZekken2()
              }
              client.push_message(userId, message)
              chatLogger(userId, 'bot', 'text', text_noZekken2())
              message = {
                type: 'text',
                text: text_noZekken3()
              }
              client.push_message(userId, message)
              chatLogger(userId, 'bot', 'text', text_noZekken3())
              return "200 OK"
            end

            case status['status']
            when 'nothing'
              chatLogger(userId, 'user', 'image', '[保存しなかった画像ファイル]')
              # 何もなし
            when 'cp_stanby'

              response = client.get_message_content(event.message['id'])

              fullpath = photoSaver(zekken_number, event_code, response)

              chatLogger(userId, 'user', 'image', fullpath)
              
              ###
              #早野修正分
              #ユーザーのbuy_pointレコードが1なら追加の写真を要求する。
              #validation(重複チェック)などはいるか？
              inputCPnumber(zekken_number, event_code, status['memory'], fullpath)

              if checkBuypointSingleRecord(zekken_number, event_code, status['memory'])
                message = {
                  type: 'text',
                  text: text_ask_for_receipt_image(status['memory'])
                 }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_ask_for_receipt_image(status['memory']))
              return "200 OK"
              end
              ###

              
              result = removeStatus(userId)
              if result == "delete error" then
                for i in 1..10 do
                  result = removeStatus(userId)
                  if result == "200 OK" then
                    break
                  end
                end
              end

              if result == "delete error"
                message = {
                  type: 'text',
                  text: text_importantError("STA-002")
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_importantError("STA-002"))
                return "200 OK"
              end

              cp_name = getCPname(status['memory'], event_code)
              cp_number = status['memory'].to_i


              message = {
                type: 'text',
                text: text_registreCP(cp_number,cp_name)
              }
              client.reply_message(event['replyToken'], message)
              chatLogger(userId, 'bot', 'text', text_registreCP(cp_number,cp_name))

            when 'goal_stanby'

              response = client.get_message_content(event.message['id'])

              fullpath = photoSaver(zekken_number, event_code, response)

              chatLogger(userId, 'user', 'image', fullpath)

              jdata = status['memory']
              data = JSON.parse(jdata)

              inputGoalTime(zekken_number,event_code,data['goal_time'],data['late_point'],fullpath)

              result = removeStatus(userId)
              if result == "delete error" then
                for i in 1..10 do
                  result = removeStatus(userId)
                  if result == "200 OK" then
                    break
                  end
                end
              end

              if result == "delete error"
                message = {
                  type: 'text',
                  text: text_importantError("STA-002")
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_importantError("STA-002"))
                return "200 OK"
              end

              message = {
                type: 'text',
                text: text_getGoalPhoto()
              }
              client.reply_message(event['replyToken'], message)
              chatLogger(userId, 'bot', 'text', text_getGoalPhoto())

              inputStatus(userId, "Goal", 'nil')

              Thread.new do
                makeScoreboardThread(zekken_number, event_code)
              end
              return "200 OK"

            when "insert"

              response = client.get_message_content(event.message['id'])

              fullpath = photoSaver(zekken_number, event_code, response)

              chatLogger(userId, 'user', 'image', fullpath)

              insertRegister(userId, zekken_number, event_code, status['memory'], fullpath, client, event)

            end
          when Line::Bot::Event::MessageType::Location
            latitude = event.message['latitude']
            longitude = event.message['longitude']

            chatLogger(userId, 'user', 'location', "latitude : #{latitude} , longitude : #{longitude}")

            if notZekken then
              message = {
                type: 'text',
                text: text_noZekken1(userdata["user_name"])
              }
              client.reply_message(event['replyToken'], message)
              chatLogger(userId, 'bot', 'text', text_noZekken1(userdata["user_name"]))
              message = {
                type: 'text',
                text: text_noZekken2()
              }
              client.push_message(userId, message)
              chatLogger(userId, 'bot', 'text', text_noZekken2())
              message = {
                type: 'text',
                text: text_noZekken3()
              }
              client.push_message(userId, message)
              chatLogger(userId, 'bot', 'text', text_noZekken3())
              return "200 OK"
            end

            case status['status']
            when 'nothing'

              p "latitude : #{latitude} , longitude : #{longitude}"
              cp_data = getCPnameByThreshols(latitude,longitude, event_code)

              if cp_data["result"] == "no cp error" then
                message = {
                  type: 'text',
                  text: text_noLocation()
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_noLocation())
                return "200 OK"
              end

              if (cp_data["cp_number"].to_i == -1) then
                message = {
                  type: 'text',
                  text: text_goalCheck1()
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_goalCheck1())
                message = {
                  type: 'text',
                  text: text_goalCheck2()
                }
                client.push_message(userId, message)
                chatLogger(userId, 'bot', 'text', text_goalCheck2())
                message = {
                  type: 'text',
                  text: text_goalCheck3()
                }
                client.push_message(userId, message)
                chatLogger(userId, 'bot', 'text', text_goalCheck3())
                return "200 OK"
              end

              if cpDoubleCheck(zekken_number, event_code, cp_data['cp_number']) then
                message = {
                  type: 'text',
                  text: text_registredCP(cp_data["cp_number"],cp_data["cp_name"])
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_registredCP(cp_data["cp_number"],cp_data["cp_name"]))
                return "200 OK"
              end

              if cp_data['buy_point'] != '0' then
                inputStatus(userId,'cp_stanby',cp_data["cp_number"])

                message = {
                  type: 'text',
                  text: text_standByBuyPoint(cp_data["cp_number"],cp_data["cp_name"])
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_standByBuyPoint(cp_data["cp_number"],cp_data["cp_name"]))
                return "200 OK"
              end

              fullpath = set_originbase() + '/location_checked.png'

              inputCPnumber(zekken_number,event_code,cp_data["cp_number"],fullpath)

              cp_number = cp_data["cp_number"]

              message = {
                type: 'text',
                text: text_registreCP(cp_number,cp_data["cp_name"])
              }
              client.reply_message(event['replyToken'], message)
              chatLogger(userId, 'bot', 'text', text_registreCP(cp_number,cp_data["cp_name"]))
              return "200 OK"

            when 'cp_stanby'
              p "latitude : #{latitude} , longitude : #{longitude}"
              cp_data = {}
              if status['memory'].to_i == 998 then
                cp_data["cp_number"] = 998
                cp_data["cp_name"] = "テストチェックポイント2"
                cp_data["buy_point"] = '0'
              else
                cp_data = getCPnameByThreshols(latitude,longitude,event_code,status['memory'])
              end

              if cp_data["result"] == "no cp error" then
                cp_name = getCPname(status["memory"],event_code)
                if status["memory"].to_i < 996 then
                  cp_number = getPhotoPointByCPNum(status["memory"],event_code)
                else
                  cp_number = status["memory"]
                end
                message = {
                  type: 'text',
                  text: text_noLocationCSB(cp_number,cp_name)
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_noLocationCSB(cp_number,cp_name))
                return "200 OK"
              end

              if cp_data['buy_point'] != '0' then
                message = {
                  type: 'text',
                  text: text_BuyPointError(cp_data["cp_number"],cp_data["cp_name"])
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_BuyPointError(cp_data["cp_number"],cp_data["cp_name"]))
                return "200 OK"
              end

              fullpath = set_originbase() + '/location_checked.png'

              inputCPnumber(zekken_number,event_code,cp_data["cp_number"],fullpath)

              if status["memory"].to_i < 996 then
                cp_number = getPhotoPointByCPNum(status["memory"],event_code)
              else
                cp_number = status["memory"]
              end

              message = {
                type: 'text',
                text: text_registreCP(cp_number,cp_data["cp_name"])
              }
              client.reply_message(event['replyToken'], message)
              chatLogger(userId, 'bot', 'text', text_registreCP(cp_number,cp_data["cp_name"]))

              result = removeStatus(userId)
              if result == "delete error" then
                for i in 1..10 do
                  result = removeStatus(userId)
                  if result == "200 OK" then
                    break
                  end
                end
              end

              if result == "delete error"
                message = {
                  type: 'text',
                  text: text_importantError("STA-002")
                }
                client.reply_message(event['replyToken'], message)
                chatLogger(userId, 'bot', 'text', text_importantError("STA-002"))
                return "200 OK"
              end

              return "200 OK"

            end

          when Line::Bot::Event::MessageType::File
            if notZekken then
              chatLogger(userId, 'user', 'file', "[保存しなかったファイル]")
              message = {
                type: 'text',
                text: text_noZekken1(userdata["user_name"])
              }
              client.reply_message(event['replyToken'], message)
              chatLogger(userId, 'bot', 'text', text_noZekken1(userdata["user_name"]))
              message = {
                type: 'text',
                text: text_noZekken2()
              }
              client.push_message(userId, message)
              chatLogger(userId, 'bot', 'text', text_noZekken2())
              message = {
                type: 'text',
                text: text_noZekken3()
              }
              client.push_message(userId, message)
              chatLogger(userId, 'bot', 'text', text_noZekken3())
              return "200 OK"
            end
            chatLogger(userId, 'user', 'file', "[保存しなかったファイル]")

            # commonbase = set_commonbase()
            # docpath = commonbase + '/' + event_code + '/' + zekken_number
            # filename = DateTime.now.to_s.gsub(':','-').gsub('+','_') + '.xml'
            # fullpath = docpath + '/' + filename
            #
            # response = client.get_message_content(event.message['id'])
            #
            # open(fullpath, 'w') do |pass|
            #   pass.write(response.body)
            # end
            #
            # chatLogger(userId, 'user', 'file', fullpath)
            #
            # doc = REXML::Document.new(File.open(fullpath))
            #
            # doc.elements.each('gpx/trk/trkseg/trkpt') do |elm|
            #
            #   lat = elm.attribute('lat').value
            #   lon = elm.attribute('lon').value
            #
            #   p "lat : #{lat}, lon : #{lon}"
            #
            #   inputGPSlog(zekken_number,event_code,lat,lon)
            #
            # end

            message = {
              type: 'text',
              text: text_getGPSlogger()
            }
            client.reply_message(event['replyToken'], message)
          else
            chatLogger(userId, 'user', 'other', "[テキスト、画像、位置情報、ファイル以外の保存しなかった何か]")
            if notZekken then
              message = {
                type: 'text',
                text: text_noZekken1(userdata["user_name"])
              }
              client.reply_message(event['replyToken'], message)
              chatLogger(userId, 'bot', 'text', text_noZekken1(userdata["user_name"]))
              message = {
                type: 'text',
                text: text_noZekken2()
              }
              client.push_message(userId, message)
              chatLogger(userId, 'bot', 'text', text_noZekken2())
              message = {
                type: 'text',
                text: text_noZekken3()
              }
              client.push_message(userId, message)
              chatLogger(userId, 'bot', 'text', text_noZekken3())
              return "200 OK"
            end
          end
        end
      end
      p "200 OK"
    end

    # https://natnats.mobilous.com/get_zekken_list?event='ogaki'

    app.get "/get_zekken_list" do
      crossdomain
      headjson

      p "/get_zekken_list"
      p "params : #{params}"

      return JSON.generate(getZekkenListByTT(params['event']))

    end

    # https://natnats.mobilous.com/get_photo_list?zekken=[zekken_number]&pw=[password]

    app.get "/get_photo_list" do
      crossdomain

      p "/get_photo_list"

      zekken = params['zekken']
      password = params['pw']
      event_code = params['event']

      result = zekkenAuthorization(zekken,password)

      if result["zekken_number"] == "ERROR" || result["event_code"] != event_code then
        headjson
        return JSON.generate({"status" => "ERROR"})
      end
      # ゼッケン番号の変換。美濃加茂ロゲで、以下のゼッケン番号の登録を間違えていたので急造の変換
  zekken_conversion = {
    "MF3-160" => "MZ3-160",
    "MF3-161" => "MZ3-161",
    "MF3-162" => "MZ3-162",
    "MF3-163" => "MZ3-163",
    "MF5-170" => "MZ5-170",
    "MF5-171" => "MZ5-171",
    "MF5-172" => "MZ5-172",
    "MF5-173" => "MZ5-173",
    "MF5-174" => "MZ5-174",
    "MF5-175" => "MZ5-175",
    "MF5-176" => "MZ5-176",
    "MF5-177" => "MZ5-177",
    "MF5-178" => "MZ5-178",
    "MF5-179" => "MZ5-179"
  }

  if zekken_conversion.key?(zekken)
    zekken = zekken_conversion[zekken]
  end

      photo_list = []

      routePath = "#{set_commonbase()}/route_photo/#{zekken}.png"
      routePath2 = "#{set_commonbase2()}/route_photo/#{zekken}.png"

      if File.exist?(routePath) then
        photo_list.push(routePath2)
      end
      photo_list.concat(getPhotoList(zekken, event_code))

      headjson
      return JSON.generate({"status" => "OK", "photo_list" => photo_list, "report" => getFinalReport(zekken,event_code)})

    end

    # https://natnats.mobilous.com/getScoreboard?z_num=[zekken_number]

    app.get "/getScoreboard" do
      crossdomain

      p "/getScoreboard"
      p "params : #{params}"

      zekken_number = params["z_num"]
      event_code = params["event"]

      file_path = getScoreboardOgaki(zekken_number,event_code)

      content_type "application/xlsx"
      attachment "#{file_path}"

      send_file(file_path, :filename => "#{zekken_number}_scoreboardOgaki.xlsx",:type => "application/xlsx", :disposition => 'attachment')

    end

    # app.post "/xlsx_receive_ogaki" do
    #   crossdomain
    #
    #   savepath = set_commonbase() + "/temp/#{params['input_file'][:filename]}"
    #
    #   if (Dir.exist?(savepath) ) then
    #     File.delete(savepath)
    #   end
    #
    #   File.open(savepath, 'wb') do |f|
    #     f.write params['input_file'][:tempfile].read
    #   end
    #
    #   @pgconn=UserPostgres.new
    #   dbname = set_dbname()
    #   @pgconn.connectPg("localhost","mobilous",0,dbname)
    #
    #   template = AppExeExcelWorkBook.new
    #   template.readWorkBook(savepath)
    #
    #   dataSheet = AppExeExcelWorkSheet.new
    #   dataSheet.initLoad(template,"計算用")
    #
    #   if dataSheet.getCell(1,2).value == nil then
    #     return "no zekken error"
    #   end
    #
    #   zekken_number = dataSheet.getCell(1,2).value
    #
    #   anytable=UserPostgresTable.new
    #   anytable.useTable( @pgconn,'user_table' )
    #   anytable2=UserPostgresTable.new
    #   anytable2.useTable( @pgconn,'gps_information' )
    #
    #   userId = getUserIdByZekkenNumber(zekken_number)
    #
    #   if userId == "no zekken number" then
    #
    #     title = ["userid", "zekken_number", "create_at", "update_at"]
    #
    #     record = {"userid" => zekken_number, "zekken_number" => zekken_number, "create_at" => DateTime.now, "update_at" => DateTime.now}
    #
    #     sql = anytable.makeInsertKeySet(title,record)
    #     p sql
    #
    #     begin
    #       ret = anytable.exec(sql)         # true or false
    #     rescue => error
    #       p error
    #     end
    #
    #     userId = zekken_number
    #
    #   end
    #
    #
    #   title = ["user_name", "cp_number", "create_at", "update_at"]
    #
    #   col = 1
    #   line = 6
    #
    #   for i in 0 .. 99 do
    #     if dataSheet.getCell(line,col) == nil then
    #       break
    #     end
    #     if dataSheet.getCell(line,col).value == nil then
    #       break
    #     end
    #     rec = dataSheet.getCell(line,col).value
    #     if (cpDoubleCheck(zekken_number,rec)) then
    #       p "double! #{rec}"
    #     else
    #       record = {"user_name" => userId, "cp_number" => rec, "create_at" => DateTime.now, "update_at" => DateTime.now}
    #       sql = anytable2.makeInsertKeySet(title,record)
    #       p sql
    #
    #       begin
    #         ret = anytable2.exec(sql)         # true or false
    #       rescue => error
    #         p error
    #       end
    #     end
    #
    #     col += 1
    #
    #     if col == 11 then
    #       col = 1
    #       line += 5
    #     end
    #     if line == 56 then
    #       break
    #     end
    #   end
    #
    #   goal_time = dataSheet.getCell(3,8).value
    #
    #   late_point = dataSheet.getCell(56,3).value
    #
    #   title = ["user_name", "goal_time", "late_point", "create_at", "update_at"]
    #
    #   record = {"user_name" => userId, "goal_time" => goal_time, "late_point" => late_point, "create_at" => DateTime.now, "update_at" => DateTime.now}
    #   sql = anytable2.makeInsertKeySet(title,record)
    #   p sql
    #
    #   begin
    #     ret = anytable2.exec(sql)         # true or false
    #   rescue => error
    #     p error
    #   end
    #
    #   @pgconn.disconnect
    #
    #   p "200 OK"
    #
    # end

#====================================
# realtimemonitor API
#====================================

    app.get "/realtimeMonitor" do
      crossdomain

      p "/realtimeMonitor"
      p "params : #{params}"

      event_code = params["event_code"]
      class_name = params["class"]

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)
      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'realtimemonitor' )

        where = "event_code = '#{event_code}'"

      if class_name == nil || class_name == "" then
      else
        where = where + "AND class_name = '#{class_name}'"
      end

      list = anytable.find2(where)
      result = []

      list.each { |rec|
        result.push(rec)
      }

      @pgconn.disconnect

      headjson
      return JSON.generate(result)

    end

    app.get "/realtimeMonitor_zekken_narrow" do
      crossdomain

      p "/realtimeMonitor_zekken_narrow"
      p "params : #{params}"

      zekken_number = params["zekken"]
      event_code = params["event_code"]

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)
      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'realtimemonitor' )

      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

      list = anytable.find2(where)
      result = []

      list.each { |rec|
        result.push(rec)
      }

      @pgconn.disconnect

      headjson
      return JSON.generate(result)

    end

    app.get "/getRoute" do
      crossdomain
      
      p "/getRoute"
      p "params : #{params}"

      team_name = params["team"]
      event_code = params["event_code"]

      zekken_number = getZekkenNumberByTeamName(team_name,event_code)
      p "zekken_number: #{zekken_number}" 

      if zekken_number == "no zekken number" then
        headjson
        return JSON.generate({"status" => "ERROR"})
      end

      input_file = "#{set_commonbase}/#{event_code}/#{zekken_number}/waypoints.csv"

      bounds = get_lat_lng_bounds_for_event_code(event_code)
      include_min_lat = bounds[:min_latitude]
      include_max_lat = bounds[:max_latitude]
      include_min_lng = bounds[:min_longitude]
      include_max_lng = bounds[:max_longitude]

      #ファイルがあって
      #if File.exist?(input_file) && event_code != '大垣２'
      if File.exist?(input_file)
        data = CSV.read(input_file)
        if data.empty?
          result = getDataFromDatabase(zekken_number, event_code)
        else
          result = []
          data.each do |line|

            latitude = line[0].to_f
            longitude = line[1].to_f
    
            # 緯度経度が指定された範囲内にある場合のみ追加
            if (include_min_lat..include_max_lat).include?(latitude) && (include_min_lng..include_max_lng).include?(longitude)
              waypoint = {
                "latitude" => latitude.to_s,
                "longitude" => longitude.to_s,
                "create_at" => line[2]
              }
              result.push(waypoint)
            end
          end
        #csvそのまま表示する時の処理。
        # else
        #   result = []
        #   data.each do |line|
        #     waypoint = {
        #       "latitude" => line[0],
        #       "longitude" => line[1],
        #       "create_at" => line[2]
        #     }
        #     result.push(waypoint)
        #   end
        end
      else
        result = getDataFromDatabase(zekken_number, event_code)
      end

      # p "result: #{result}"

      result2 = {"status" => "OK"}

      result2["detail"] = result

      headjson
      return JSON.generate(result2)

    end

    app.get "/fetchUserLocations" do
      crossdomain
      
      p "/getRoute"
      p "params : #{params}"

      zekken_number = params["zekken_number"]
      event_code = params["event_code"]

      result = getDataFromDatabase(zekken_number, event_code)

      headjson
      return JSON.generate(result)
      

    end

    app.get "/generate_route_image" do
      begin
        crossdomain
        headjson
    
        p "/generate_route_image"
        p "params : #{params}"
    
        event_code = params["event_code"]
        zekken_number = params["zekken_number"]
    
        # データの取得
        gps_data = getGpsList(zekken_number, event_code)
        
        p "GPS data: #{gps_data}"
        
        if gps_data.empty?
          return JSON.generate({"status" => "ERROR", "detail" => "No GPS data found for this participant"})
        end
    
        # イベントの範囲を取得
        bounds = get_lat_lng_bounds_for_event_code(event_code)
        
        p "Bounds: #{bounds}"
    
        # 画像生成
        image = generate_route_image(gps_data, bounds)
    
        # 画像の保存
        filename = "route_#{event_code}_#{zekken_number}_#{Time.now.strftime("%Y%m%d%H%M%S")}.png"
        save_path = "#{set_commonbase()}/#{event_code}/route_images/#{filename}"
        
        # ディレクトリが存在しない場合は作成
        FileUtils.mkdir_p(File.dirname(save_path))
        
        image.write(save_path)
    
        # S3にアップロード
        s3_url = s3Uploader("#{event_code}/route_images", File.dirname(save_path), filename)
    
        return JSON.generate({"status" => "OK", "image_url" => s3_url})
      rescue => e
        puts "Error: #{e.message}"
        puts e.backtrace.join("\n")
        return JSON.generate({"status" => "ERROR", "detail" => e.message, "backtrace" => e.backtrace})
      end
    end
    


app.get "/getAllRoutes" do
  crossdomain

  p "/getAllRoutes"
  p "params : #{params}"

  event_code = params["event_code"]
  class_name_param = params["class_name"] # class_name パラメータの取得
  class_name = class_name_param unless class_name_param.nil? || class_name_param.empty? || class_name_param == "全て"

  @pgconn = UserPostgres.new
  dbname = set_dbname()
  @pgconn.connectPg("localhost", "mobilous", 0, dbname)
  anytable = UserPostgresTable.new
  anytable.useTable(@pgconn, 'gps_detail_for_animation')

  # class_name が指定されている場合は、それをクエリの条件に含める
  class_condition = class_name ? "AND class_name = '#{class_name}'" : ""

  result = {}
  zekken_list = anytable.find2("event_code = '#{event_code}' #{class_condition} ORDER BY zekken_number")
                    .map { |rec| rec["zekken_number"] }.uniq

  zekken_list.each do |zekken_number|
    team_name = get_team_name(zekken_number, event_code)
    input_file = "#{set_commonbase}/#{event_code}/#{zekken_number}/waypoints.csv"
    route_data = []

    if File.exist?(input_file) && event_code != '大垣２'
      CSV.foreach(input_file) do |line|
        waypoint = {
          "latitude" => line[0],
          "longitude" => line[1],
          "create_at" => line[2],
          "team_name" => team_name
          # class_name はここでは不要なので追加しない
        }
        route_data << waypoint
      end
    else
      where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}' #{class_condition} ORDER BY serial_number"
      list = anytable.find2(where)
      list.each do |rec|
        waypoint = rec.merge("team_name" => team_name) # ここでもclass_nameは不要
        route_data << waypoint
      end
    end

    result[zekken_number] = route_data
  end
  @pgconn.disconnect
  result2 = {"status" => "OK", "detail" => result}

  headjson
  return JSON.generate(result2)
end



    app.get "/getStartPoint" do
      crossdomain
      headjson

      p "getStartPoint"
      p "params : #{params}"

      event_code = params['event']

      result = getCPlatlngByCPnumber(-2, event_code)

      if result["status"] == "no cp error" then
        return JSON.generate({"status" => "ERROR", "detail" => "no such cp"})
      end

      return JSON.generate(result)
    end

    app.get "/get_ranking" do
      crossdomain

      p "get_ranking"
      p "params : #{params}"

      class_name = params["class"]
      event_code = params["event"]

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)
      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'ranking_fix' )

      where = "class_name = '#{class_name}' AND event_code ='#{event_code}' ORDER BY point DESC"

      list = anytable.find2(where)
      result = []

      list.each { |rec|
        result.push(rec)
      }

      @pgconn.disconnect

      headjson
      return JSON.generate(result)

    end

    app.get "/all_ranking_top3" do
      crossdomain
      headjson

      p "/all_ranking_top3"
      p "params : #{params}"

      event_code = params["event"]

      class_list =  ['3時間一般','3時間ファミリー','3時間自転車','3時間ソロ男子','3時間ソロ女子','3時間パラロゲ','3時間無料','5時間一般','5時間ファミリー','5時間自転車','5時間ソロ男子','5時間ソロ女子']
      class_map = {'3時間一般'=>'一般-３時間','3時間ファミリー'=>'ファミリー-３時間','3時間自転車'=>'自転車-３時間','3時間ソロ男子'=>'ソロ男子-３時間','3時間ソロ女子'=>'ソロ女子-３時間','3時間パラロゲ'=>'パラロゲ-３時間','3時間無料'=>'お試し-３時間','5時間一般'=>'一般-５時間','5時間ファミリー'=>'ファミリー-５時間','5時間自転車'=>'自転車-５時間','5時間ソロ男子'=>'ソロ男子-５時間','5時間ソロ女子'=>'ソロ女子-５時間'}

      result_hash = {}
      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","admin",0,dbname)
      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'ranking_fix' )

      class_list.each{ |class_name|

        alternative = class_map[class_name]
        where = "(class_name = '#{class_name}' or class_name = '#{alternative}') AND event_code = '#{event_code}' ORDER BY point DESC"

        list = anytable.find2(where)
        result = []

        count = 0

        list.each { |rec|
          count += 1
          result.push(rec)
          if count == 3 then
            break
          end
        }

        result_hash[class_name] = result

      }
      @pgconn.disconnect
      return JSON.generate(result_hash)

    end

    app.get "/analyze_point" do
      crossdomain
      headjson
    
      p "/analyze_point"
      p "params : #{params}"
    
      lat = params["lat"].to_f
      lng = params["lng"].to_f
      team_name = params["team_name"]
      event_code = params["event_code"]
    
      zekken_number = getZekkenNumberByTeamName(team_name, event_code)
      if zekken_number == "no zekken number" then
        return JSON.generate({"status" => "ERROR", "detail" => "該当するチーム名が存在しません。the team name is not exist."})
      end
    
      gps_data = getGpsList(zekken_number, event_code)
      
      # 最も近い地点を見つける
      closest_point = gps_data.min_by { |point| haversine_distance(lat, lng, point['latitude'].to_f, point['longitude'].to_f) }
    
      # 前後の点を含めた分析を行う
      index = gps_data.index(closest_point)
      prev_point = index > 0 ? gps_data[index - 1] : nil
      next_point = index < gps_data.length - 1 ? gps_data[index + 1] : nil
    
      # 速度と移動タイプを計算
      speed, movement_type = calculate_speed_and_type(prev_point, closest_point, next_point)
    
      # 累積距離を計算
      cumulative_distance = calculate_cumulative_distance(gps_data, index)
    
      result = {
        "status" => "OK",
        "latitude" => closest_point['latitude'],
        "longitude" => closest_point['longitude'],
        "time" => closest_point['create_at'],
        "speed" => speed,
        "movement_type" => movement_type,
        "cumulative_distance" => cumulative_distance
      }
    
      JSON.generate(result)
    end

    # app.get "/all_ranking_top3_for_fcgifu" do
    #   crossdomain
    #   headjson
    
    #   p "/all_ranking_top3_for_fcgifu"
    #   p "params : #{params}"
    
    #   result_hash = {}
    #   @pgconn = UserPostgres.new
    #   dbname = set_dbname()
    #   @pgconn.connectPg("localhost", "admin", 0, dbname)
    
    #   begin
    #     # Get all unique event_codes
    #     event_code
    #     s = get_unique_event_codes(@pgconn)
    
    #     event_codes.each do |event_code|
    #       # イベントコードごとに、クラス別の上位3チームを取得
    #       top_users_by_class = get_top_users_by_class(@pgconn, event_code)
          
    #       result_hash[event_code] = top_users_by_class
    #     end
    
    #     @pgconn.disconnect
    #     return JSON.generate(result_hash)
    #   rescue StandardError => e
    #     @pgconn.disconnect if @pgconn
    #     return JSON.generate({ error: e.message, backtrace: e.backtrace })
    #   end
    # end
    app.get "/all_ranking_for_fcgifu" do
      crossdomain
      headjson
    
      p "/all_ranking_for_fcgifu"
      p "params : #{params}"
    
      result_hash = {}
      @pgconn = UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost", "admin", 0, dbname)
    
      begin
        # Get all unique event_codes
        event_codes = get_unique_event_codes(@pgconn)
    
        event_codes.each do |event_code|
          # イベントコードごとに、全ユーザーを取得
          all_users_by_class = get_all_users_by_class(@pgconn, event_code)
          
          # 各クラスの全ユーザーのルート情報を取得
          all_users_by_class.each do |class_name, users|
            users.each do |user|
              user["route"] = get_user_route(@pgconn, event_code, user["zekken_number"])
            end
          end
    
          result_hash[event_code] = all_users_by_class
        end
    
        @pgconn.disconnect
        return JSON.generate(result_hash)
      rescue StandardError => e
        @pgconn.disconnect if @pgconn
        return JSON.generate({ error: e.message, backtrace: e.backtrace })
      end
    end

    app.get "/all_ranking_top3_for_fcgifu" do
      crossdomain
      headjson
    
      p "/all_ranking_top3_for_fcgifu"
      p "params : #{params}"
    
      result_hash = {}
      @pgconn = UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost", "admin", 0, dbname)
    
      begin
        # Get all unique event_codes
        event_codes = get_unique_event_codes(@pgconn)
    
        event_codes.each do |event_code|
          # イベントコードごとに、クラス別の上位3チームを取得
          top_users_by_class = get_top_users_by_class(@pgconn, event_code)
          
          # 各クラスの上位ユーザーのルート情報を取得
          top_users_by_class.each do |class_name, users|
            users.each do |user|
              user["route"] = get_user_route(@pgconn, event_code, user["zekken_number"])
            end
          end
    
          result_hash[event_code] = top_users_by_class
        end
    
        @pgconn.disconnect
        return JSON.generate(result_hash)
      rescue StandardError => e
        @pgconn.disconnect if @pgconn
        return JSON.generate({ error: e.message, backtrace: e.backtrace })
      end
    end
    
    ##移動経路出力

    app.get "/top_users_routes" do
      crossdomain
      headjson
      
      event_code = params['event_code']
      class_name = params['class_name']
      
      @pgconn = UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost", "mobilous", 0, dbname)
      
      routes = get_top_users_routes(@pgconn, event_code, class_name)
      
      bounds = get_lat_lng_bounds_for_event_code(event_code)
      
      processed_routes = {}
      routes.each do |zekken_number, route_data|
        processed_routes[zekken_number] = process_route_data(route_data, bounds)
      end
      
      @pgconn.disconnect
      
      JSON.generate({
        status: "OK",
        routes: processed_routes
      })
    end
    
    


#====================================
# rogaining simulator API
#====================================

    app.get "/getCheckpointList" do
      crossdomain
      headjson

      p "/getCheckpointList"
      p "params : #{params}"

      event_code = params['event']

      return JSON.generate(getCheckpointList(event_code))

    end

    app.get "/rogainingSimulator" do
      crossdomain
      headjson

      p "/rogainingSimulator"
      p "params : #{params}"

      begin
        event_code = params["event_code"].to_s
        course_time = params["course_time"].to_i
        pause_time_free = params["pause_time_free"].to_i
        pause_time_paid = params["pause_time_paid"].to_i
        spare_time = params["spare_time"].to_i
        target_velocity = params["target_velocity"].to_f
        p "target_velocity : #{target_velocity}"
      rescue => e
        p e
        return JSON.generate({"status" => "error", "detail" => "Invalid arguments provided."})
      end
      begin
        free_node_to_visit = params["free_node_to_visit"].scan(/\d+/).map(&:to_i)
      rescue => e
        p e
        free_node_to_visit = []
      end
      begin
        paid_node_to_visit = params["paid_node_to_visit"].scan(/\d+/).map(&:to_i)
      rescue => e
        p e
        paid_node_to_visit = []
      end
      simResult = rogainingSimulator(event_code, course_time, pause_time_free, pause_time_paid, spare_time, target_velocity, free_node_to_visit, paid_node_to_visit)

      if (simResult["status"] == "ERROR") then
        return JSON.generate({"status" => "ERROR", "detail" => simResult["detail"]})
      end

      # return JSON.generate(simResult) # まずは仮に

      cp_list = simResult["detail"]["route"]

      if cp_list[1].to_i == 0 then
        return JSON.generate({"status" => "ERROR", "detail" => "Could not find the optimal route."})
      end

      userId = getMaxUserNumByRSR(event_code).to_i + 1

      cp_list.shift #頭の0を除去

      response_cp_list = []

      inputRogSimRecord(userId,event_code,-2)

      result = getCPlatlngByCPnumber(-2,event_code)
      if result["status"] == "no cp error" then
        return JSON.generate({"status" => "ERROR", "detail" => "no cp error"})
      end
      response_cp_list.push("latitude" => result["record"]["latitude"], "longitude" => result["record"]["longitude"])

      cp_list.each do |n|
        if n.to_i == 0 then
          inputRogSimRecord(userId,event_code,-1)
          result = getCPlatlngByCPnumber(-1,event_code)
          if result["status"] == "no cp error" then
            return JSON.generate({"status" => "ERROR", "detail" => "no cp error"})
          end
          response_cp_list.push("latitude" => result["record"]["latitude"], "longitude" => result["record"]["longitude"])
        else
          inputRogSimRecord(userId,event_code,n.to_i)
          result = getCPlatlngByCPnumber(n.to_i,event_code)
          if result["status"] == "no cp error" then
            return JSON.generate({"status" => "ERROR", "detail" => "no cp error"})
          end
          response_cp_list.push("latitude" => result["record"]["latitude"], "longitude" => result["record"]["longitude"])
        end
      end

      # return JSON.generate({"status" => "OK", "detail" => response_cp_list}) #仮2

      begin
        file_address = makeRogSimResultSheet(userId,event_code)
      rescue => e
        p e
        return JSON.generate({"status" => "ERROR", "detail" => "Failed to generate the result data."})
      end

      if file_address == "no data error" then
        return JSON.generate({"status" => "ERROR", "detail" => "Failed to generate the result data."})
      end

      return JSON.generate({"status" => "OK", "cp_list" => response_cp_list, "address" => file_address})

    end

#====================================
# checkin admin API
#====================================

    app.get "/getCheckinList" do
      crossdomain
      headjson

      p "/getCheckinList"
      p "params : #{params}"

      zekken = params["zekken"]
      event_code = params["event"]

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)
      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'gps_detail_fix' )

      where = "zekken_number = '#{zekken}' AND event_code = '#{event_code}' ORDER BY serial_number"

      list = anytable.find2(where)
      result = []

      list.each { |rec|
        result.push(rec)
      }

      @pgconn.disconnect

      result2 = {}

      result2 = {"status" => "OK"}

      result2["detail"] = result

      return JSON.generate(result2)

    end

    # app.delete "/deleteCheckin" do
    app.get "/deleteCheckin" do # deleteではCORSpolisyのエラーが出るため
      crossdomain
      headjson

      p "/deleteCheckin"
      p "params : #{params}"

      zekken = params["zekken"]
      event_code = params["event"]
      sn = params["sn"]

      command = "DELETE #{sn}"

      result = deleteRegister('API', zekken, event_code, command, nil, nil, true)

      if result == "delete error" then
        return JSON.generate({"status" => "error"})
      end

      return JSON.generate({"status" => "OK"})

    end

    app.get "/moveCheckin" do # patchではCORSpolisyのエラーが出るため
      crossdomain
      headjson

      p "/moveCheckin"
      p "params : #{params}"

      zekken = params["zekken"]
      event_code = params["event"]
      old_sn = params["old_sn"]
      new_sn = params["new_sn"]

      command = "MOVE #{old_sn} #{new_sn}"

      result = moveRegister('API', zekken, event_code, command, nil, nil, true)

      if (checkAgentFlag(zekken, event_code) == false) then

        inputAgentFlag(zekken, event_code, 'checkinAdmin')

      end

      if result == 'OK' then
        return JSON.generate({"status" => "OK"})
      end

      return JSON.generate({"status" => "error", "detail" => result})

    end

    app.get "/startCheckin" do
      crossdomain
      headjson

      p "/startCheckin"
      p "params : #{params}"

      event_code = params["event"]
      zekken_number = params["zekken"]

      #FC岐阜コラボの時に、スタート時にチェックイン情報が全削除されないようにする

      # removeAllGI(zekken_number, event_code)

      inputCPnumber(zekken_number,event_code,-2,set_originbase() + '/start.png')

      #bonus_point_flagにデータがあったらポイント追加
      # if (checkBonusPointFlag(zekken_number, event_code) == true) then
      #   inputCPnumber(zekken_number,event_code,102,set_originbase() + '/start.png')
      # end

      # if (checkAgentFlag(zekken_number, event_code) == false) then
      #   inputAgentFlag(zekken_number, event_code, agent_id)
      # end

      return JSON.generate({"status" => "OK"})

    end

    app.get "/addCheckin" do
      crossdomain
      headjson

      p "/addCheckin"
      p "params : #{params}"

      event_code = params["event"]
      zekken_number = params["zekken"]
      checkin_list = params["list"]

      errorAr = []

      checkinArr = checkin_list.split(',')
      checkinArr.each { |cp_number|
        if cp_number != "" then
          if  cp_number == "no cp error" then
            errorAr.push(cp_number)
          elsif cpDoubleCheck(zekken_number, event_code, cp_number) then
            errorAr.push(cp_number)
          elsif getCPname(cp_number, event_code) == "no cp error" then
            errorAr.push(cp_number)
          else
            inputCPnumber(zekken_number,event_code,cp_number,set_originbase() + '/photo_none.png')
          end
        end
      }

      if (checkAgentFlag(zekken_number, event_code) == false) then
        inputAgentFlag(zekken_number, event_code, 'checkinAdmin')
      end

      if errorAr == [] then
        return JSON.generate({"status" => "OK"})
      else
        return JSON.generate({"status" => "not best", "detail" => errorAr.join(',')})
      end

    end

    app.get "/goalCheckin" do
      crossdomain
      headjson

      p "/goalCheckin"
      p "params : #{params}"

      event_code = params["event"]
      zekken_number = params["zekken"]

      goal_time = params['goal_time']

      begin
        goaltime = Time.parse(goal_time)
      rescue => error
        p error
        return JSON.generate({"status" => "ERROR", "detail" => "ゴール時間のパースに失敗しました。Goal time parsing failed."})
      end

      result = getTeamDataByZekken_number(zekken_number, event_code)

      starttime = Time.parse(getEventStartTime(event_code))

      if result['class_name'].include?('3時間') then
        limittime = starttime + (3 * 60 * 60)
      elsif result['class_name'].include?('5時間') then
        limittime = starttime + (5 * 60 * 60)
      #else
      elsif result['class_name'].include?("テスト") || result['class_name'].include?("test") then
        limittime = starttime + (5 * 60 * 60)
      else
        message = {
          type: 'text',
          text: 'エラーコード：DBS-001\nチーム情報の取得に失敗しました。\nゼッケン番号とこのエラーコードを添えて運営に問い合わせて下さい。'
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', 'エラーコード：DBS-001\nチーム情報の取得に失敗しました。\nゼッケン番号とこのエラーコードを添えて運営に問
い合わせて下さい。')
        return JSON.generate({"status" => "NG"})
      end

      if limittime < goaltime then
        late_point = goaltime - limittime
        if late_point < 60
          late_point = 50
        else
          late_point = late_point/60 + 1
          late_point = late_point.floor*50
        end
      else
        late_point = 0
      end

      inputGoalTime(zekken_number,event_code,goal_time,late_point,set_originbase() + '/photo_none.png')

      Thread.new do
        makeScoreboardThread(zekken_number, event_code)
      end

      return JSON.generate({"status" => "OK"})

    end

    app.post "/goal_from_rogapp" do
      crossdomain
      headjson
    
      p "goal_from_rogapp"
      p "params : #{params}"
    
      event_code = params["event_code"]
      zekken_number = getZekkenNumberByTeamName(params["team_name"], event_code)
      if zekken_number == "no zekken number" then
        return JSON.generate({"status" => "ERROR", "detail" => "該当するチーム名が存在しません。the team name is not exist."})
      end
    
      # if cpDoubleCheck(zekken_number, event_code, -1) then
      #   return JSON.generate({"status" => "ERROR", "detail" => "このチェックポイントは既に登録されています。the check point is already registered."})
      # end
    
      docpath = set_commonbase() + '/' + event_code + '/' + zekken_number
      docpath2 = set_commonbase2() + '/' + event_code + '/' + zekken_number
    
      if( !Dir.exist?(docpath) )then
        Dir.mkdir( docpath, 0755 )
      end
    
      filename = params['image'].split('/').last
    
      if filename.to_s.empty?
        return JSON.generate({"status" => "ERROR", "detail" => "画像アドレスが指定されていません。Image address is not specified."})
      end
    
      fullpath = docpath + '/' + filename
    
      begin
        url = params['image']
        encoded_url = URI::DEFAULT_PARSER.escape(url)
        puts "uri=#{encoded_url}"
        URI.open(encoded_url) do |image|
          File.open(fullpath, 'w') do |file|
            file.write(image.read)
          end
        end
      rescue => e
        p e
        return JSON.generate({"status" => "ERROR", "detail" => "写真の保存に失敗しました。Failed to save photo."})
      end
    
      fullpath2 = s3Uploader("#{event_code}/#{zekken_number}", docpath, filename)
    
      extension = filename.split('.').last
      filenameTh = filename.gsub(".#{extension}", "_th.#{extension}")
    
      imageOri = Magick::Image.read(fullpath).first
      imageTh = imageOri.scale(image_size_width(), image_size_height())
      imageTh.write("temp/#{filenameTh}")
    
      nextpath = docpath + '/' + filenameTh
      FileUtils.mv("/var/www/temp/#{filenameTh}", nextpath)
    
      goal_time = params['goal_time']
    
      begin
        goaltime = Time.parse(goal_time)
      rescue => error
        p error
        return JSON.generate({"status" => "ERROR", "detail" => "ゴール時間のパースに失敗しました。Goal time parsing failed."})
      end
    
      result = getTeamDataByZekken_number(zekken_number, event_code)
      starttime = Time.parse(getEventStartTime(event_code))
    
      if result['class_name'].include?('3時間') || result['class_name'].include?('３時間')
        limittime = starttime + (3 * 60 * 60)
      elsif result['class_name'].include?('5時間') || result['class_name'].include?('５時間')
        limittime = starttime + (5 * 60 * 60)
      elsif result['class_name'].include?("テスト") || result['class_name'].include?("test")
        limittime = starttime + (5 * 60 * 60)
      else
        return JSON.generate({
          "status" => "ERROR", 
          "detail" => "不明なクラス名です: #{result['class_name']}。運営に問い合わせてください。"
        })
      end
    
      puts "limittime=#{limittime} , goaltime=#{goaltime}"
      if limittime < goaltime then
        late_point = goaltime - limittime
        if late_point < 60
          late_point = 50
        else
          late_point = late_point/60 + 1
          late_point = late_point.floor*50
        end
      else
        late_point = 0
      end
    
      inputGoalTime(zekken_number, event_code, goal_time, late_point, fullpath2)

  begin
    scoreboard_result = makeScoreboard(zekken_number, event_code)
    
    if scoreboard_result
      # S3のURLを使用
      s3_url = scoreboard_result
      
      # ダウンロード用のURLを生成
      download_url = "/gifuroge/download_scoreboard?event_code=#{event_code}&zekken_number=#{zekken_number}"
      
      return JSON.generate({
        "status" => "OK", 
        "scoreboard_url" => s3_url,
        "download_url" => download_url
      })
    else
      return JSON.generate({
        "status" => "OK", 
        "detail" => "スコアボードの生成に失敗しました。Failed to generate scoreboard."
      })
    end
  rescue => e
    p e
    return JSON.generate({
      "status" => "ERROR",
      "detail" => "スコアボード生成中にエラーが発生しました: #{e.message}"
    })
  end
end

    # 新しいダウンロードエンドポイント（必要に応じて追加）
app.get "/download_scoreboard" do
  event_code = params["event_code"]
  zekken_number = params["zekken_number"]

  file_path = "#{set_commonbase()}/#{event_code}/scoreboard/#{zekken_number}_scoreboard.pdf"

  if File.exist?(file_path)
    send_file(file_path, :filename => "scoreboard_#{zekken_number}.pdf", :type => 'application/pdf')
  else
    status 404
    "File not found"
  end
end

    app.get "/changeGoalTimeCheckin" do
      crossdomain
      headjson

      p "changeGoalTimeCheckin"
      p "params : #{params}"

      event_code = params["event"]
      zekken_number = params["zekken"]

      goal_time = params['goal_time']

      begin
        goaltime = Time.parse(goal_time)
      rescue => error
        p error
        return JSON.generate({"status" => "ERROR", "detail" => "ゴール時間のパースに失敗しました。Goal time parsing failed."})
      end


      result = getTeamDataByZekken_number(zekken_number, event_code)

      starttime = Time.parse(getEventStartTime(event_code))

      if result['class_name'].include?('3時間') then
        limittime = starttime + (3 * 60 * 60)
      elsif result['class_name'].include?('5時間') then
        limittime = starttime + (5 * 60 * 60)
      elsif result['class_name'].include?("テスト") || result['class_name'].include?("test") then
      #else
        limittime = starttime + (5 * 60 * 60)
      else
        message = {
          type: 'text',
          text: 'エラーコード：DBS-001\nチーム情報の取得に失敗しました。\nゼッケン番号とこのエラーコードを添えて運営に問い合わせて下さい。'
        }
        client.reply_message(event['replyToken'], message)
        chatLogger(userId, 'bot', 'text', 'エラーコード：DBS-001\nチーム情報の取得に失敗しました。\nゼッケン番号とこのエラーコードを添えて運営に問
い合わせて下さい。')
        return JSON.generate({"status" => "NG"})
      end

      if limittime < goaltime then
        late_point = goaltime - limittime
        if late_point < 60
          late_point = 50
        else
          late_point = late_point/60 + 1
          late_point = late_point.floor*50
        end
      else
        late_point = 0
      end

      changeGoalTimeAndLatePoint(zekken_number, event_code, goal_time, late_point)

      return JSON.generate({"status" => "OK"})

    end

    app.get "/reprint" do
      crossdomain
      headjson

      p "reprint"
      p "params : #{params}"

      event_code = params["event"]
      zekken_number = params["zekken"]

      Thread.new do
        makeScoreboardThread(zekken_number, event_code, "true")
      end

      return JSON.generate({"status" => "OK"})

    end

    app.get "/serviceCheckTrue" do
      crossdomain
      headjson

      p "serviceCheckTrue"
      p "params : #{params}"

      event_code = params["event"]
      zekken_number = params["zekken"]
      serial_number = params["sn"]

      result = addBuyFlagTrue(serial_number, zekken_number, event_code)

      if result == "OK" then
        return JSON.generate({"status" => "OK"})
      else
        return JSON.generate({"status" => "ERROR"})
      end

    end

    app.get "/serviceCheckFalse" do
      crossdomain
      headjson

      p "serviceCheckFalse"
      p "params : #{params}"

      event_code = params["event"]
      zekken_number = params["zekken"]
      serial_number = params["sn"]

      result = addBuyFlagFalse(serial_number, zekken_number, event_code)

      if result == "OK" then
        return JSON.generate({"status" => "OK"})
      else
        return JSON.generate({"status" => "ERROR"})
      end

    end

    app.get "/getYetCheckSeeviceList" do
      crossdomain
      headjson

      p "getYetCheckSeeviceList"
      p "params : #{params}"

      event_code = params["event"]

      @pgconn=UserPostgres.new
      dbname = set_dbname()
      @pgconn.connectPg("localhost","mobilous",0,dbname)
      anytable=UserPostgresTable.new
      anytable.useTable( @pgconn,'gps_detail_fix' )

      where = "event_code = '#{event_code}' AND origin_buy_point != 0 ORDER BY create_at DESC"
      #where = "event_code = '#{event_code}' AND buy_flag = false ORDER BY create_at DESC"

      list = anytable.find2(where)
      result = []

      list.each { |rec|
        result.push(rec)
      }

      @pgconn.disconnect

      result2 = {}

      result2 = {"status" => "OK"}

      result2["detail"] = result

      return JSON.generate(result2)

    end


#====================================
# 登録周り API
#====================================

app.post "/register_team" do
  crossdomain
  headjson

  p "/register_team"
  p "params : #{params}"

  zekken_number = params["zekken_number"]
  event_code = params["event_code"]
  team_name = params["team_name"]
  class_name = params["class_name"]
  password = params["password"]

  # パラメータの値を表示
  puts "Received parameters:"
  puts "zekken_number: #{zekken_number.inspect}"
  puts "event_code: #{event_code.inspect}"
  puts "team_name: #{team_name.inspect}"
  puts "class_name: #{class_name.inspect}"
  puts "password: #{password.inspect}"

  # 必須パラメータのチェック（修正）
  missing_params = []
  [
    ['zekken_number', zekken_number],
    ['event_code', event_code],
    ['team_name', team_name],
    ['class_name', class_name],
    ['password', password]
  ].each do |name, value|
    missing_params << name if value.nil? || value.to_s.strip.empty?
  end

  if missing_params.any?
    return JSON.generate({
      "status" => "ERROR",
      "detail" => "Missing or empty required parameters: #{missing_params.join(', ')}"
    })
  end

  # データベース接続
  @pgconn = UserPostgres.new
  dbname = set_dbname()
  @pgconn.connectPg("localhost", "mobilous", 0, dbname)

  # team_tableの取得
  team_table = UserPostgresTable.new
  team_table.useTable(@pgconn, 'team_table')

  # 既存のチームをチェック
  existing_team = team_table.findOne("zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'")
  if existing_team
    @pgconn.disconnect
    return JSON.generate({
      "status" => "ERROR",
      "detail" => "Team with this zekken number and event code already exists"
    })
  end

  # 新しいチームの登録
  record = {
    "zekken_number" => zekken_number,
    "event_code" => event_code,
    "team_name" => team_name,
    "class_name" => class_name,
    "password" => password
  }

  begin
    sql = team_table.makeInsertKeySet(record.keys, record)
    puts "Executing SQL: #{sql}"  # SQLクエリをログ出力
    result = team_table.insertExec(sql)
    if result
      @pgconn.disconnect
      return JSON.generate({"status" => "OK", "detail" => "Team registered successfully"})
    else
      @pgconn.disconnect
      return JSON.generate({"status" => "ERROR", "detail" => "Failed to register team"})
    end
  rescue PG::UniqueViolation
    @pgconn.disconnect
    return JSON.generate({
      "status" => "ERROR",
      "detail" => "Team with this zekken number and event code already exists"
    })
  rescue => error
    p error
    @pgconn.disconnect
    return JSON.generate({"status" => "ERROR", "detail" => "Database error: #{error.message}"})
  end
end

app.post "/update_team_name" do
  crossdomain
  headjson

  p "/update_team_name"
  p "params : #{params}"

  zekken_number = params["zekken_number"]
  new_team_name = params["new_team_name"]
  event_code = params["event_code"]

  # 必須パラメータのチェック
  if [zekken_number, new_team_name, event_code].any?(&:nil?)
    return JSON.generate({"status" => "ERROR", "detail" => "Missing required parameters"})
  end

  @pgconn = UserPostgres.new
  dbname = set_dbname()
  @pgconn.connectPg("localhost", "mobilous", 0, dbname)

  rec = {"team_name" => new_team_name}
  where = "zekken_number = '#{zekken_number}' AND event_code = '#{event_code}'"

  begin
    result = @pgconn.update("team_table", rec, where)
    if result
      @pgconn.disconnect
      return JSON.generate({"status" => "OK", "detail" => "Team name updated successfully"})
    else
      @pgconn.disconnect
      return JSON.generate({"status" => "ERROR", "detail" => "Failed to update team name or no matching record found"})
    end
  rescue => error
    p error
    @pgconn.disconnect
    return JSON.generate({"status" => "ERROR", "detail" => "Database error: #{error.message}"})
  end
end

app.post '/input_cp' do
  content_type :json
  request.body.rewind
  data = JSON.parse(request.body.read)

  zekken_number = data['zekken_number']
  event_code = data['event_code']
  cp_number = data['cp_number']
  image_address = data['image_address']

  result = inputCPnumber(zekken_number, event_code, cp_number, image_address)

  status 200
  { result: result }.to_json
end

#====================================
# chatLogViewer API
#====================================

app.get "/get_chatlog" do
  crossdomain
  headjson

  p "get_chatlog"
  p "params : #{params}"

  event_code = params["event"]
  zekken_number = params["zekken"]

  userid = getUserIdByZekkenNumber(zekken_number, event_code)

  if userid == "no zekken number" then
    return JSON.generate({"status" => "ERROR", "detail" => "該当するユーザーを発見出来ませんでした"})
  end

  user_name = getUserName(userid)["user_name"]

  @pgconn=UserPostgres.new
  dbname = set_dbname()
  @pgconn.connectPg("localhost","mobilous",0,dbname)
  anytable=UserPostgresTable.new
  anytable.useTable( @pgconn,'chat_log' )

  where = "userid = '#{userid}' ORDER BY serial_number DESC"

  list = anytable.find2(where)
  result = []

  list.each { |rec|
    result.push(rec)
  }

  @pgconn.disconnect

  result2 = {}

  result2 = {"status" => "OK"}

  result2["detail"] = result

  result2["name"] = user_name

  return JSON.generate(result2)

end

#====================================
# user admin API
#====================================

  app.get "/teamRegister" do
    crossdomain
    headjson

    p "/teamRegister"
    p "params : #{params}"

    event = params["event"]
    class_name = params["class"]
    zekken = params["zekken"]
    team = params["team"]
    pass = params["pass"]

    inputTeamData(event,class_name,zekken,team,pass)

    return JSON.generate({"status" => "OK"})

  end

  app.get "/zekkenMaxNum" do
    crossdomain
    headjson

    p "/zekkenMaxNum"
    p "params : #{params}"

    event = params["event"]

    list = getZekkenListByTT(event)

    numList = []

    list.each do |rec|
      numList.push(rec.split('-')[1].to_i)
    end

    result = numList.max

    if result == nil then
      result = 0
    end

    return JSON.generate({"status" => "OK", "result" => result})

  end

  app.get "/zekkenDoubleCheck" do

    crossdomain
    headjson

    p "/zekkenDoubleCheck"
    p "params : #{params}"

    zekken = params['zekken']
    event = params['event']

    list = getZekkenListByTT(event)

    result = false

    list.each do |rec|
      if rec == zekken then
        result = true
        break
      end
    end

    return JSON.generate({"status" => "OK", "result" => result})

  end

  app.get "/teamClassChanger" do
    crossdomain
    headjson

    p "/teamClassChanger"
    p "params : #{params}"

    zekken = params['zekken']
    event = params['event']
    new_class = params['new_class']

    result = changeTeamClass(zekken, event, new_class)

    return JSON.generate({"status" => result})

  end

#====================================
# CP一覧シート作成用API
#====================================

  app.post "/makeCpListSheet" do
    crossdomain

    p "/makeCpListSheet"
    p "params : #{params}"

    event_code = params["event"]
    cp_csv = params["cp_csv"]
    sponsor_csv = params["sponsor_csv"]

    insert_record = {}
    insert_header = []

    event_data = getEventData(event_code)

    insert_header.push("event_name")
    insert_record["event_name"] = event_data["event_name"]

    insert_header.push("event_day")
    insert_record["event_day"] = event_data["event_day"]

    i = 1

    CSV.foreach(cp_csv[:tempfile], headers: true) do |row|

      #puts "row=#{row}"

      if row["sub_loc_id"].to_s.start_with?('#-1') then
        next
      end

      insert_header.push("sub_loc_id_#{i}")
      insert_record["sub_loc_id_#{i}"] = row["sub_loc_id"]

      insert_header.push("loc_name_#{i}")
      insert_record["loc_name_#{i}"] = row["loc_name"]

      insert_header.push("photo_#{i}")
      insert_record["photo_#{i}"] = "#{set_commonbase()}/#{event_code}/cps/#{row["photos"]}"

      insert_header.push("remark_#{i}")
      insert_record["remark_#{i}"] = row["remark"]

      insert_header.push("tags_#{i}")
      insert_record["tags_#{i}"] = row["tags"]

      i = i + 1
    end

    CSV.foreach(sponsor_csv[:tempfile], headers: true) do |row|

      for ii in 1..30 do
        if row["sponsor_#{ii}"] != nil && row["sponsor_#{ii}"] != "" then
          insert_header.push("sponsor_#{ii}")
          insert_record["sponsor_#{ii}"] = row["sponsor_#{ii}"]
        end
      end

    end

    sn = getMaxNumByCLS().to_i + 1

    insert_header.push("serial_number")
    insert_record["serial_number"] = sn

    #p "insert_header : #{insert_header}"
    #p "insert_record : #{insert_record}"

    inputCpListSheet(insert_header, insert_record)

    #ここから用紙生成プロセス

    #$bean.read_bean

    #username = $bean.username
    username = "natnats"

    record = insert_record

    record["num_image"] = i + 1

    document = "cpListSheet"

    ExcelObject.resetClass

    doc = AppExeExcelDoc.new
    #doc = AppExeGeneralDocument.new
    #doc.init( username,0,document,"jp","/var/www/docbase","/home/mobilousInstance" )    # <== "/home/mobilousInstance" を出力先のベースフォルダーにしてください。
    doc.init( username,0,document,"jp","/var/www/docbase","/home/ubuntu" )    # <== "/home/mobilousInstance" を出力先のベースフォルダーにしてください。

    pgconn=UserPostgres.new
    dbname = set_dbname()
    pgconn.connectPg("localhost","mobilous",0,dbname)

    rec = JSON.parse( JSON.generate(record) )
          #@sheetname = sheet
    #filename = doc.makeReport( pgconn,rec,"outfile.xlsx",0 )   # ==> Use ini file defnition 2021-11-15
    doc.makeReport( pgconn,rec )

    # make a excel file under /var/www/docbase/(@username)/(@projectno)/

    #file_path = filename.to_s.gsub(/wrote excel file on /,"")
    #puts "=========> file_path = #{file_path}"

    filename = doc.outfile
    file_path = doc.outpath
    puts "=========> file_path = #{file_path}, filename = #{filename}"

    content_type "application/xlsx"
    attachment "#{file_path}"

    send_file(file_path, :filename => "cpListSheet_#{event_code}.xlsx",:type => "application/xlsx", :disposition => 'attachment')

  end

#====================================
# 手動呼び出し用API
#====================================

  app.get "/makeAllScoreboard" do
    crossdomain
    headjson

    p "/makeAllScoreboard"
    p "params : #{params}"

    event_code = params["event"]

    zekken_list = getZekkenListByTT(event_code)

    if zekken_list == nil || zekken_list == [] then
      return JSON.generate({"status" => "ERROR", "detail" => "no zekkens"})
    end

    complete_list = {}
    fail_list = []
    error_list = {}

    zekken_list.each do |zekken|
      begin
        p "zekken : #{zekken}"
        result = makeScoreboardThread(zekken, event_code, false, true)

        if result == "no data erropr" then
          fail_list.push(zekken)
        else
          complete_list[zekken] = result
        end
      rescue => e
        p e
        error_list[zekken] = e
      end

    end

    return JSON.generate({"complete" => complete_list, "fail" => fail_list, "error" => error_list})

  end

#====================================
# rog app API
#====================================

    app.post "/start_from_rogapp" do
      p "/start_from_rogapp"
      crossdomain
      headjson

      p "start_from_rogapp"
      p "params : #{params}"

      

      event_code = params["event_code"]
      zekken_number = getZekkenNumberByTeamName(params["team_name"], event_code)
      if zekken_number == "no zekken number" then
        return JSON.generate({"status" => "ERROR", "detail" => "該当するチーム名が存在しません。the team name is not exist."})
      end

      #FC岐阜コラボの時はスタートでチェックポイントを削除しないように(同じルートを別日に回ることがあるため)
      # removeAllGI(zekken_number, event_code)

      inputCPnumber(zekken_number,event_code,-2,set_originbase() + '/start.png')

      # # bonus_point_flagにデータがあったらポイント追加
      # if checkBonusPointFlag(zekken_number, event_code)
      #   inputCPnumber(zekken_number, event_code, 102, set_originbase() + '/start.png')
      # end


      return JSON.generate({"status" => "OK"})

    end
    
    app.get "/practice" do
      crossdomain
      headjson
      practice_text = "practice"
      return practice_text
    end


    app.post "/checkin_from_rogapp" do
      crossdomain
      headjson

      p "checkin_from_rogapp"
      p "params : #{params}"

      event_code = params["event_code"]
      zekken_number = getZekkenNumberByTeamName(params["team_name"], event_code)

      # ディレクトリパスの構築
      base_path = "/var/www/html/images/gifuRoge"
      full_path = File.join(base_path, event_code, zekken_number.to_s)

      # ディレクトリの作成（既に存在する場合は何もしない）
      begin
        FileUtils.mkdir_p(full_path) unless File.directory?(full_path)
      rescue Errno::EACCES => e
        puts "エラー: ディレクトリ作成の権限がありません。 #{e.message}"
        return JSON.generate({"status" => "ERROR", "detail" => "サーバー内部エラー：ディレクトリ作成の権限がありません。"})
      rescue => e
        puts "エラー: ディレクトリ作成中に問題が発生しました。 #{e.message}"
        return JSON.generate({"status" => "ERROR", "detail" => "サーバー内部エラー：ディレクトリ作成中に問題が発生しました。"})
      end

      if zekken_number == "no zekken number" then
        return JSON.generate({"status" => "ERROR", "detail" => "該当するチーム名が存在しません。the team name is not exist."})
      end
      cp_number = params["cp_number"]

      if cp_number.to_i == -1 || cp_number.to_i == -2 then
        return JSON.generate({"status" => "ERROR", "detail" => "スタートやゴールをチェックインする時はそれぞれ専用のAPIを使ってチェックインしてください。When checking in for the start or finish, please use the respective dedicated API to check in."})
      end

      if cpDoubleCheck(zekken_number, event_code, cp_number) then
        return JSON.generate({"status" => "ERROR", "detail" => "このチェックポイントは既に登録されています。the check point is already registered."})
      end

      docpath = set_commonbase() + '/' + event_code + '/' + zekken_number
      docpath2 = set_commonbase2() + '/' + event_code + '/' + zekken_number

      if( !Dir.exist?(docpath) )then
        if( Dir.mkdir( docpath,0755 ) == 0 ) then
        end
      end

      filename = params['image'].split('/').last

      if filename == '' || filename == nil then
        return JSON.generate({"status" => "ERROR", "detail" => "画像アドレスが指定されていません。Image address is not specified."})
      end

      fullpath = docpath + '/' + filename

      begin
        url = params['image']
        encoded_url = URI::DEFAULT_PARSER.escape(url)
        puts "uri=#{encoded_url}"
        URI.open(encoded_url) do |image|
          File.open(fullpath, 'w') do |file|
            file.write(image.read)
          end
        end
      rescue => e
        p e
        return JSON.generate({"status" => "ERROR", "detail" => "写真の保存に失敗しました。Failed to save photo."})
      end

      fullpath2 = s3Uploader("#{event_code}/#{zekken_number}",docpath,filename)

      extension = filename.split('.').last

      filenameTh = filename.gsub(".#{extension}","_th.#{extension}")

      imageOri = Magick::Image.read(fullpath).first
      imageTh = imageOri.scale(image_size_width(), image_size_height())
      imageTh.write("temp/#{filenameTh}")

      nextpath = docpath + '/' + filenameTh

      FileUtils.mv("/var/www/temp/#{filenameTh}",nextpath)
      # File.delete(fullpath)

      fullpath = fullpath2

      inputCPnumber(zekken_number, event_code, cp_number, fullpath)  

      return JSON.generate({"status" => "OK"})

    end

    app.post "/remove_checkin_from_rogapp" do
      crossdomain
      headjson

      p "remove_checkin_from_rogapp"
      p "params : #{params}"
      begin
      event_code = params["event_code"]
      zekken_number = getZekkenNumberByTeamName(params["team_name"], event_code)
      if zekken_number == "no zekken number" then
        return JSON.generate({"status" => "ERROR", "detail" => "該当するチーム名が存在しません。the team name is not exist."})
      end
      cp_number = params["cp_number"]

      sn = getSerialNumberByCpNumber(zekken_number, event_code, cp_number)

      if sn == 'no sn' then
        return JSON.generate({"status" => "ERROR", "detail" => "該当するチェックインが見つかりません。No corresponding checkin found."})
      end

      command = "DELETE #{sn}"

      result = deleteRegister('API', zekken_number, event_code, command, nil, nil, true)

      # チェックポイントが2回登録されている場合があるための処理　ここから
      sn = getSerialNumberByCpNumber(zekken_number, event_code, cp_number)

      if sn != 'no sn' then
        command = "DELETE #{sn}"

        result = deleteRegister('API', zekken_number, event_code, command, nil, nil, true)
      end
      # チェックポイントが2回登録されている場合があるための処理　ここまで

      return JSON.generate({"status" => "OK"})

      rescue => e
        return JSON.generate({"status" => "ERROR", "detail" => "予期せぬエラー undefined error #{e}"})
      end

    end

    app.get "/get_team_list" do
      crossdomain
      headjson

      p "get_team_list"

      p "params : #{params}"

      event_code = params["event_code"]
      if event_code == '' then
        event_code = nil
      end

      return JSON.generate(getTeamList(event_code))
    end

    #元のゴール処理。fc岐阜用に同じ名前で修正したものを別のとこに作ってます
    # app.post "/goal_from_rogapp" do
    #   crossdomain
    #   headjson
    
    #   p "goal_from_rogapp"
    #   p "params : #{params}"
    
    #   event_code = params["event_code"]
    #   zekken_number = getZekkenNumberByTeamName(params["team_name"], event_code)
    #   if zekken_number == "no zekken number" then
    #     return JSON.generate({"status" => "ERROR", "detail" => "該当するチーム名が存在しません。the team name is not exist."})
    #   end
      
      

    #   # if cpDoubleCheck(zekken_number, event_code, -1) then
    #   #   return JSON.generate({"status" => "ERROR", "detail" => "このチェックポイントは既に登録されています。the check point is already registered."})
    #   # end
    
    #   docpath = set_commonbase() + '/' + event_code + '/' + zekken_number
    #   docpath2 = set_commonbase2() + '/' + event_code + '/' + zekken_number
    
    #   if( !Dir.exist?(docpath) )then
    #     Dir.mkdir( docpath, 0755 )
    #   end
    
    #   filename = params['image'].split('/').last
    
    #   if filename.to_s.empty?
    #     return JSON.generate({"status" => "ERROR", "detail" => "画像アドレスが指定されていません。Image address is not specified."})
    #   end
    
    #   fullpath = docpath + '/' + filename
    
    #   begin
    #     url = params['image']
    #     encoded_url = URI::DEFAULT_PARSER.escape(url)
    #     puts "uri=#{encoded_url}"
    #     URI.open(encoded_url) do |image|
    #       File.open(fullpath, 'w') do |file|
    #         file.write(image.read)
    #       end
    #     end
    #   rescue => e
    #     p e
    #     return JSON.generate({"status" => "ERROR", "detail" => "写真の保存に失敗しました。Failed to save photo."})
    #   end
    
    #   fullpath2 = s3Uploader("#{event_code}/#{zekken_number}", docpath, filename)
    
    #   extension = filename.split('.').last
    #   filenameTh = filename.gsub(".#{extension}", "_th.#{extension}")
    
    #   imageOri = Magick::Image.read(fullpath).first
    #   imageTh = imageOri.scale(image_size_width(), image_size_height())
    #   imageTh.write("temp/#{filenameTh}")
    
    #   nextpath = docpath + '/' + filenameTh
    #   FileUtils.mv("/var/www/temp/#{filenameTh}", nextpath)
    
    #   goal_time = params['goal_time']
    
    #   begin
    #     goaltime = Time.parse(goal_time)
    #   rescue => error
    #     p error
    #     return JSON.generate({"status" => "ERROR", "detail" => "ゴール時間のパースに失敗しました。Goal time parsing failed."})
    #   end
    
    #   result = getTeamDataByZekken_number(zekken_number, event_code)
    #   starttime = Time.parse(getEventStartTime(event_code))
    
    #   if result['class_name'].include?('3時間') || result['class_name'].include?('３時間')
    #     limittime = starttime + (3 * 60 * 60)
    #   elsif result['class_name'].include?('5時間') || result['class_name'].include?('５時間')
    #     limittime = starttime + (5 * 60 * 60)
    #   elsif result['class_name'].include?("テスト") || result['class_name'].include?("test")
    #     limittime = starttime + (5 * 60 * 60)
    #   else
    #     return JSON.generate({
    #       "status" => "ERROR", 
    #       "detail" => "不明なクラス名です: #{result['class_name']}。運営に問い合わせてください。"
    #     })
    #   end
    
    #   puts "limittime=#{limittime} , goaltime=#{goaltime}"
    #   if limittime < goaltime then
    #     late_point = goaltime - limittime
    #     if late_point < 60
    #       late_point = 50
    #     else
    #       late_point = late_point/60 + 1
    #       late_point = late_point.floor*50
    #     end
    #   else
    #     late_point = 0
    #   end
    
    #   inputGoalTime(zekken_number, event_code, goal_time, late_point, fullpath2)
    
    #   Thread.new do
    #     makeScoreboardThread(zekken_number, event_code)
    #   end
    
    #   return JSON.generate({"status" => "OK"})
    # end

    app.post "/get_waypoint_datas_from_rogapp" do
      crossdomain
      headjson

      begin

      p "/get_waypoint_datas_from_rogapp"
      p "params : #{params}"

      team_name = params["team_name"]
      event_code = params["event_code"]
      list = params["waypoints"]

      zekken_number = getZekkenNumberByTeamName(params["team_name"],event_code)
      if zekken_number == "no zekken number" then
        return JSON.generate({"status" => "ERROR", "detail" => "該当するチーム名が存在しません。the team name is not exist."})
      end

      data = []

      list.each do |rec|

        line = []
        line.push(rec['latitude'])
        line.push(rec['longitude'])
        line.push(rec['time'])

        data.push(line)

      end

      filebase = "#{set_commonbase}/#{event_code}/#{zekken_number}"

      output_file = "#{filebase}/waypoints.csv"

      if (File.exist?(output_file)) then
        input_data = CSV.read(output_file)
        data = input_data + data
      end

      if (!File.exist?(filebase)) then
        if( Dir.mkdir( filebase,0755 ) == 0 ) then
        end
      end

      CSV.open(output_file, 'w') do |csv|
        data.each do |row|
          csv << row
        end
      end

      return JSON.generate({"status" => "OK"})

      rescue => e
        p e
        return JSON.generate({"status" => "ERROR", "detail" => "予期せぬエラーが発生しました。undefined error : #{e}"})
      end

    end


    app.get "/check_event_code" do
      crossdomain
      headjson

      p "check_event_code"
      p "params : #{params}"

      zekken_number = params['zekken_number']
      password = params['pw']

      result = zekkenAuthorization(zekken_number, password)

      if result["result"] == "ERROR" then
        return JSON.generate({"status" => "ERROR"})
      else
        return JSON.generate({"status" => "OK", "event_code" => result["event_code"]})
      end

    end

  end

  end
end

