import sqlite3
import datetime 

class DBAccess():

  __path = "jibun.db"

  #itemから全てのレコードを取得
  def get_all_item():
    result = []
    with sqlite3.connect(DBAccess.__path) as conn:
      sql =  " SELECT item_id, item_name FROM item;"
      result = conn.execute(sql)
    conn.close
    return result

  #recordにレコードを1件登録
  def insert_record(item_id, assessment, comment):
    with sqlite3.connect(DBAccess.__path) as conn:

      #現在日時の取得
      dt_now = datetime.datetime.now()
      strDate = str(dt_now.date())
      strTime = dt_now.time().strftime('%X')
      
      sql =  " INSERT INTO record (item_id, assessment, comment, date, time)"
      sql += " VALUES(?, ?, ?, ?, ?);"
      values = (item_id, assessment, comment, strDate, strTime)
      conn.execute(sql,values)
    conn.close

  #recordから全てのレコードを取得
  def get_all_record(page):
    result = []
    rows = 10
    start = (int(page) - 1 ) * rows
    with sqlite3.connect(DBAccess.__path) as conn:
      sql =  " SELECT r.time, r.date, i.item_name, r.assessment, r.comment"
      sql += " FROM record r, item i"
      sql += " WHERE r.item_id = i.item_id"
      sql += " ORDER BY r.date DESC, r.time DESC"     # DESC 降順
      sql += " LIMIT " + str(start) + "," + str(rows) # LIMIT 開始位置, 取得行数
      sql += ";"
      result = conn.execute(sql)
    conn.close
    return result

  #recordから全てのレコード件数を取得
  def get_all_record_count():
    cnt = 0
    with sqlite3.connect(DBAccess.__path) as conn:
      sql = " SELECT id FROM record;"
      result = conn.execute(sql)
      cnt = len(result.fetchall())
    conn.close
    return cnt
    