popufare/server/scripts/PopufareAPI.py

#!/usr/bin/python3
#
# Copyright (c) 2019 Clementine Computing LLC.
# 
# This file is part of PopuFare.
# 
# PopuFare is free software: you can redistribute it and/or modify
# it under the terms of the GNU Affero General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
# 
# PopuFare is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Affero General Public License for more details.
# 
# You should have received a copy of the GNU Affero General Public License
# along with PopuFare.  If not, see <https://www.gnu.org/licenses/>.
#

## WORK IN PROGRESS

import json
import mysql.connector
import time
import datetime

#conn = mysql.connector.connect(user='bus', password='bus', host='localhost', database='busdb', port=3306)
conn = mysql.connector.connect(user='busapi', password='bus', host='localhost', database='busdb', port=5506)

Function = [
  "User",
  "Card",
  "Pass",
  "Log",
  "PricePoints",
  "AdminGetCard", "AdminGetCards", "AdminGetPass", "AdminGetUser",
  "AdminGetAdmin", "AdminGetPassesOnCard", "AdminGetPendingQueue", "AdminProcessPendingQueue",
  "AdminRemovePendingQueue", "AdminCreateCardBlock", "AdminCreateCard", "AdminSetUser",
  "AdminSetAdmin", "AdminAddPass", "AdminAddCard", "AdminAddUser",
  "AdminAddAdmin", "AdminRemovePass", "AdminRemovePasses", "AdminRemoveCard",
  "AdminRemoveUser", "AdminRemoveAdmin", "AdminTransferCard", "AdminTransferPass",
  "AdminGetPassOptions", "AdminAddCardToUser", "AdminRemoveCardFromUser", "AdminGetAdminPermissions",
  "AdminAddAdminPermissions", "AdminRemoveAdminPermissions", "AdminAddAdminApiPermissions", "AdminRemoveAdminApiPermissions",
  "AdminSetAdminApiPermissions", "AdminGetCustomCard", "AdminGetAdmins", "AdminSearchCards",
  "AdminSearchUsers", "AdminSearchAdmins"]

PASS_FIELDS = ["logical_card_id", "issued", "activated", "deactivated", "firstused", "lastused",
               "nrides_orig", "nrides_remain", "nday_orig", "nday_expiration",
               "expired", "rule", "queue_order", "comment", "paytype" ]

CARD_FIELDS = ["mag_token", "rfid_token", "comment", "userid", "issued", "deactivated", "lastused",
               "firstused", "group_id", "issuetype"]

USER_FIELDS = ["username", "comment", "first_name", "last_name", "phone",
               "email", "address", "city", "state", "zip", "passwordhash",
               "shipping_address", "shipping_city", "shipping_state", "shipping_zip",
               "shipping_name", "shipping_country_code", "shipping_country_name"]

def UserInfo(db, ctx):
  res = {}
  userid = -1
  if ("userid" in ctx):
    userid = ctx["userid"]

  pass_fields = PASS_FIELDS.copy()
  card_fields = CARD_FIELDS.copy()
  user_fields = USER_FIELDS.copy()

  res["userid"] = userid

  cursor = db.cursor()

  ## fill in user data
  ##
  res["user"] = {}

  fields = USER_FIELDS.copy()
  query = "select " + ",".join(fields) + " from users where userid = %s"
  cursor.execute(query, [userid])
  row = cursor.fetchone()
  for idx,f in enumerate(pass_fields):
    res["user"][f] = row[idx]

  ## go through each card and fill in card data and pass data
  ##
  res["card"] = []
  query = "select logical_card_id from user_card where userid = %s and active = 1 order by logical_card_id asc"
  card_cursor = db.cursor()
  card_cursor.execute(query, [userid])
  rows = card_cursor.fetchall()
  for row in rows:

    card_res = Card(db, {"action":"get", "logical_card_id": row[0]})
    card_res["pass"] = []

    ## finally, go through each pass
    ##
    pass_query = "select user_pass_id from user_pass where logical_card_id = %s and active = 1 and expired = 0 order by queue_order asc"
    pass_cursor = db.cursor()
    pass_cursor.execute(pass_query, [card_res["logical_card_id"]])
    pass_rows = pass_cursor.fetchall()
    for pass_row in pass_rows:

      pass_res = Pass(db, {"action":"get", "user_pass_id":pass_row[0]})
      card_res["pass"].append(pass_res)

    res["card"].append(card_res)

  return res


def Pass(db, ctx):
  res = {}
  passid = -1
  if ("user_pass_id" in ctx):
    passid = ctx["user_pass_id"]

  pass_fields = PASS_FIELDS.copy()

  cursor = db.cursor()
  fields = pass_fields.copy()
  field_vals = []

  if (ctx["action"] == "get"):
    query = "select " + ",".join(pass_fields) + " from user_pass where user_pass_id = %s"
    cursor.execute(query, [passid])
    row = cursor.fetchone()

    res["user_pass_id"] = passid 
    for idx,f in enumerate(pass_fields):
      if isinstance(row[idx], datetime.datetime):
        res[f] = row[idx].strftime("%Y-%M-%d %H:%m:%S")
      else:
        res[f] = row[idx]


  elif (ctx["action"] == "add"):

    fields.append("active")
    for f in pass_fields:
      if f in ctx:  field_vals.append(ctx[f])
      else:         field_vals.append(None)
    field_vals.append(1)

    query = "insert into user_pass (" + ",".join(fields) + ") values (" + ",".join(["%s"]*len(fields)) + ")"
    cursor.execute(query, field_vals)
    res["user_pass_id"] = cursor.lastrowid

  elif (ctx["action"] == "update"):
    update_field = []
    update_val = []

    for f in pass_fields:
      if f in ctx:
        update_field.append(f + "= %s")
        update_val.append(ctx[f])
    update_val.append(passid)

    query = "update user_pass set " + ",".join(update_field) + " where user_pass_id = %s"
    cursor.execute(query, update_val)
    res["user_pass_id"] = passid

  elif (ctx["action"] == "delete"):
    query = "delete from user_pass where user_pass_id = %s"
    cursor.execute(query, [passid])

  db.commit()
  return res

def Card(db, ctx):

  card_fields = CARD_FIELDS.copy()
  res = {}
  cardid = -1
  if ("logical_card_id" in ctx):
    cardid = ctx["logical_card_id"]

  cursor = db.cursor()
  fields = card_fields.copy()
  field_vals = []

  if (ctx["action"] == "get"):
    query = "select " + ",".join(card_fields) + " from user_card where logical_card_id = %s"
    cursor.execute(query, [cardid])
    row = cursor.fetchone()

    res["logical_card_id"] = cardid 
    for idx,f in enumerate(card_fields):
      if isinstance(row[idx], datetime.datetime):
        res[f] = row[idx].strftime("%Y-%M-%d %H:%m:%S")
      else:
        res[f] = row[idx]

  elif (ctx["action"] == "add"):

    fields.append("active")
    for f in card_fields:
      if f in ctx:  field_vals.append(ctx[f])
      else:         field_vals.append(None)
    field_vals.append(1)

    query = "insert into user_card (" + ",".join(fields) + ") values (" + ",".join(["%s"]*len(fields)) + ")"
    cursor.execute(query, field_vals)
    res["logical_card_id"] = cursor.lastrowid

  elif (ctx["action"] == "update"):
    update_field = []
    update_val = []

    for f in card_fields:
      if f in ctx:
        update_field.append(f + "= %s")
        update_val.append(ctx[f])
    update_val.append(cardid)

    query = "update user_card set " + ",".join(update_field) + " where logical_card_id = %s"
    cursor.execute(query, update_val)
    res["logical_card_id"] = cardid

  elif (ctx["action"] == "delete"):
    query = "delete from user_card where logical_card_id = %s"
    cursor.execute(query, [cardid])

  elif (ctx["action"] == "search"):

    query = "select logical_card_id from user_card where "

    if "mag_token" in ctx:
      query += " mag_token like %s "
      field_vals.append( '%' + ctx["mag_token"])

    if "rfid_token" in ctx:
      if len(field_vals)>0: query += " and "
      query += " rfid_token like %s "
      field_vals.append( '%' + ctx["rfid_token"])

    query_limit = " "
    if "limit" in ctx:
      query_limit = " limit %s "
      search_vals.append(ctx["limit"])
    query += query_limit

    cursor.execute(query, field_vals)

    res["cardids"] = []

    rows = cursor.fetchall()
    for row in rows:
      res["cardids"].append(row[0])

  db.commit()

  return res

def User(db, ctx):
  user_fields = USER_FIELDS.copy()
  res = {}

  cursor = db.cursor()
  fields = user_fields.copy()
  user_vals = []

  userid = -1
  if "userid" in ctx: userid = ctx["userid"]
  
  if (ctx["action"] == "get"):
    query = "select " + ",".join(user_fields) + " from users where userid = %s"
    cursor.execute(query, [userid])
    row = cursor.fetchone()

    res["userid"] = userid
    for idx,f in enumerate(user_fields):
      if isinstance(row[idx], datetime.datetime):
        res[f] = row[idx].strftime("%Y-%M-%d %H:%m:%S")
      else:
        res[f] = row[idx]


  elif (ctx["action"] == "add"):
    fields.append("active")
    fields.append("created")
    for f in user_fields:
      if f in ctx:  user_vals.append(ctx[f])
      else:         user_vals.append(None)
    user_vals.append(1)
    user_vals.append(time.strftime('%Y-%m-%d %H:%M:%S'))

    query = "insert into users (" + ",".join(fields) + ") values (" + ",".join(["%s"]*len(fields)) + ")"
    cursor.execute(query, user_vals)
    res["userid"] = cursor.lastrowid

  elif (ctx["action"] == "update"):

    update_field = []
    update_val = []

    for f in user_fields:
      if f in ctx:
        update_field.append(f + "= %s")
        update_val.append(ctx[f])
    update_val.append(userid)

    query = "update users set " + ",".join(update_field) + " where userid = %s"
    cursor.execute(query, update_val)
    res["userid"] = userid

  elif (ctx["action"] == "delete"):
    query = "delete from users where userid = %s"
    cursor.execute(query, [userid])

  elif (ctx["action"] == "search"):

    res["userids"] = []
    res["userid"] = userid

    search_field = []
    search_val = []
    for f in user_fields:
      if f in ctx:
        search_field.append(f + " like %s")
        search_val.append('%' + ctx[f] + '%')
    query_limit = " "
    if "limit" in ctx:
      query_limit = " limit %s "
      search_vals.append(ctx["limit"])

    query = "select userid from users where " + " and ".join(search_field) + query_limit
    cursor.execute(query, search_vals)
    rows = cursor.fetchall()
    for row in rows:
      res["userids"].append(row[0])

  db.commit()

  return res


def main(db):
  print("main")

  print("---------")
  print("---------")
  print("---------")

  res = User(db, {"action":"add", "username":"abe" })
  print("user.add:", res)

  res = User(db, {"action":"update", "username":"abeabe", "userid":res["userid"]})
  print("user.update:", res)

  res = User(db, {"action":"get", "userid":res["userid"]})
  print("user.get:", res)

  res = User(db, {"action":"delete", "userid": res["userid"]})
  print("user.delete:", res)
  
  print("---------")
  print("---------")
  print("---------")

  res = Card(db, {"action":"get", "logical_card_id":1})
  print("card.get:", res)

  res = Card(db, {"action":"add", "mag_token":"2:1234", "rfid_token":"26:20:415", "comment":"testing api", "userid":1})
  print("card.add:", res)

  res = Card(db, {"action":"update", "mag_token":"2:9234", "logical_card_id":res["logical_card_id"]})
  print("card.update:", res)

  res = Card(db, {"action":"delete", "logical_card_id":res["logical_card_id"]})
  print("card.delete:", res)

  print("---------")
  print("---------")
  print("---------")

  res = Pass(db, {"action":"get", "user_pass_id":11})
  print("pass.get:", res)

  res = Pass(db, {"action":"add", "logical_card_id":1, "queue_order":9, "rule":"TEST-ORG-NDAY", "nday_orig":3})
  print("pass.add:", res)

  res = Pass(db, {"action":"update", "user_pass_id":res["user_pass_id"],  "queue_order":10, "rule":"TEST-ORG-NDAY", "nday_orig":5})
  print("pass.update:", res)

  res = Pass(db, {"action":"delete", "user_pass_id":res["user_pass_id"]})
  print("pass.delete:", res)

  print("---------")
  print("---------")
  print("---------")


  res = UserInfo(db, {"userid":348})
  print("userinfo:", json.dumps(res, indent=2))

if __name__ == "__main__":
  main(conn)