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():
    result = []
    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 += ";"
      result = conn.execute(sql)
    conn.close
    return result