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
import copy

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

_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",
               "active", "expired", "queue_order",
               "rule", "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"]

GROUP_FIELDS = ["id", "group_id", "group_name"]
RULECLASS_FIELDS = ["id", "group_id", "group_name"]

def Request(ctx):
  _conn = mysql.connector.connect(user=_USER, password=_PASSWORD, host=_HOST, database=_DATABASE, port=_PORT)

  res = {}
  if "function" in ctx:
    if ctx["function"] == "CardInfo":
      res = CardInfo(_conn, ctx)
    elif ctx["function"] == "UserInfo":
      res = UserInfo(_conn, ctx)
    elif ctx["function"] == "User":
      res = User(_conn, ctx)
    elif ctx["function"] == "Card":
      res = Card(_conn, ctx)
    elif ctx["function"] == "Pass":
      res = Pass(_conn, ctx)
    elif ctx["function"] == "Group":
      res = Group(_conn, ctx)
    elif ctx["function"] == "Ruleclass":
      res = Ruleclass(_conn, ctx)

  _conn.close()

  return res

def CardInfo(db, ctx):
  card_res = {}


  action = "get"
  if "action" in ctx:
    action = ctx["action"]

  if action == "get":

    print("CardInfo:", ctx)

    cardid = -1
    if "logical_card_id" in ctx:
      cardid = ctx["logical_card_id"]
    card_res["logical_card_id"] = cardid

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

    ## through each of the passes on the card
    ##
    pass_query = "select user_pass_id from user_pass where logical_card_id = %s 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)

    card_res["user"] = {}
    if ((card_res["userid"] is not None) and (int(card_res["userid"]) >= 0)):
      card_res["user"] = User(db, {"action":"get", "userid": card_res["userid"] })


  elif action == "search":

    card_res["cards"] = []
    res_cardid = Card(db, ctx)
    for cid in res_cardid["logical_card_ids"]:
      _c = CardInfo(db, {"action":"get", "logical_card_id":cid})
      card_res["cards"].append(_c)

  return card_res


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()
  if row is None:
    res["api_comment"] = "user not found"
    return res

  res["user"]["userid"] = userid
  for idx,f in enumerate(user_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 = CardInfo(db, {"logical_card_id":row[0]})
    res["card"].append(card_res)

  return res

def _update_pass_bits(cursor, passid):
  q = "select logical_card_id from user_pass where user_pass_id = %s"
  cursor.execute(q, [passid])
  rows = cursor.fetchall()

  print("\n\nupdating pass bits", passid, "\n\n")

  cardid = -1
  for row in rows:
    cardid = row[0]
    break

  print("\n\nupdating pass bits cardid:", cardid, "\n\n")

  if cardid < 0: return

  q = "update user_pass set active = 0 where logical_card_id = %s"
  cursor.execute(q, [cardid])

  q = "update user_pass set active = 1 where logical_card_id = %s and expired = 0 and queue_order = " + \
  "( select min(x.queue_order) from user_pass x where x.logical_card_id = %s and x.expired = 0 )"
  cursor.execute(q, [cardid,cardid])


def Ruleclass(db, ctx):
  res = {}

  ruleclass_fields = RULECLASS_FIELDS.copy()

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

  if ctx["action"] == "search":
    query = "select id, rulename, ruleclass from rule_class"

    cursor.execute(query)
    rows = cursor.fetchall()

    res["ruleclass"] = []

    for row in rows:
      res["ruleclass"].append({"id":row[0], "rulename":row[1], "ruleclass":row[2]})

  db.commit()
  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()

    if row is not None:
      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]
    else:
      res["api_comment"] = "pass not found"


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

    ## fill in some default values
    ##
    dt = time.strftime('%Y-%m-%d %H:%M:%S')
    if "issued" not in ctx: ctx["issued"] = dt
    if "expired" not in ctx: ctx["expired"] = 0
    if "active" not in ctx: ctx["active"] = 0


    if "logical_card_id" in ctx:
      cardid = ctx["logical_card_id"]
      _q = "select queue_order from user_pass where logical_card_id = %s and expired = 0 order by queue_order desc limit 1"
      _c = db.cursor()
      _c.execute(_q, [cardid])
      _r = _c.fetchone()
      if _r is not None:
        ctx["queue_order"] = int(_r[0])+1
      else:
        ctx["active"] = 1
        ctx["queue_order"] = 0
    else:
      ctx["queue_order"] = 0

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

    query = "insert into user_pass (" + ",".join(fields) + ") values (" + ",".join(["%s"]*len(fields)) + ")"

    #print(query)
    #print(fields, field_vals)

    cursor.execute(query, field_vals)

    res["user_pass_id"] = cursor.lastrowid

    _update_pass_bits(cursor, passid);

  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

    _update_pass_bits(cursor, passid);

  elif (ctx["action"] == "deactivate"):
    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 active = 0, expired = 1 where user_pass_id = %s"
    cursor.execute(query, [passid])

    _update_pass_bits(cursor, passid);
    res["user_pass_id"] = passid


  elif (ctx["action"] == "delete"):
    query = "delete from user_pass where user_pass_id = %s"
    cursor.execute(query, [passid])
    _update_pass_bits(cursor, 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()

    if row is not None:
      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]
    else:
      res["api_comment"] = "card not found"

  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 "

    n_search = 0

    if "logical_card_id" in ctx:
      query += " logical_card_id = %s"
      field_vals.append( ctx["logical_card_id"])
      n_search += 1

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

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

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

    res["logical_card_ids"] = []

    if n_search > 0:
      cursor.execute(query, field_vals)
      rows = cursor.fetchall()
      for row in rows:
        res["logical_card_ids"].append(row[0])

  db.commit()

  return res

def Group(db,ctx):
  group_res = { }

  action = "get"
  if "action" in ctx:
    cation = ctx["action"]

  cursor = db.cursor()

  if action == "get":
    print("Group:", ctx)

    group_res["group"] = []

    query = "select group_id, group_name from groups order by group_id asc"
    cursor.execute(query)
    rows = cursor.fetchall()
    for row in rows:
      group_res["group"].append({"group_id":row[0], "group_name":row[1]})

  db.commit()
  return group_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()

    if row is not None:
      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]
    else:
      res["api_comment"] = "user not found"


  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_val.append(ctx["limit"])

    query = "select userid from users where " + " and ".join(search_field) + query_limit
    cursor.execute(query, search_val)
    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))

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

  res = Request({"function":"CardInfo", "action":"search", "logical_card_id":1})
  print("request.card.search:", res)

if __name__ == "__main__":
  conn = mysql.connector.connect(user=_USER, password=_PASSWORD, host=_HOST, database=_DATABASE, port=_PORT)
  main(conn)
  conn.close()