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

#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"] = []

    if card_res["result"] == "success":

      ## 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)
    card_res["result"] = "success"

  return card_res

##                      _        __       
##  _   _ ___  ___ _ __(_)_ __  / _| ___  
## | | | / __|/ _ \ '__| | '_ \| |_ / _ \ 
## | |_| \__ \  __/ |  | | | | |  _| (_) |
##  \__,_|___/\___|_|  |_|_| |_|_|  \___/ 
##                                        

def UserInfo(db, ctx):
  res = {}
  res["result"] = "fail"
  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)

  res["result"] = "success"
  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["result"] = "success"
      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["result"] = "fail"
      res["api_comment"] = "pass not found"


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

    if (not "logical_card_id" in ctx) or (ctx["logical_card_id"] == ''):
      res["result"] = "fail"
      res["api_comment"] = "must have logical_card_id to add pass"
    else:

      ## 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
      res["result"] = "success"

      _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
    res["result"] = "success"

    _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
    res["result"] = "success"


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

  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]
      res["result"] = "success"
    else:
      res["result"] = "fail"
      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
    res["result"] = "success"

  elif (ctx["action"] == "update"):
    if not "logical_card_id" in ctx:
      res["result"] = "fail"
      res["api_comment"] = "must supply a logical_card_id"
    else:

      update_field = []
      update_val = []

      query_card_id = ctx["logical_card_id"]
      cursor.execute("select logical_card_id from user_card where logical_card_id = %s", [query_card_id])
      rows = cursor.fetchall()
      if len(rows) == 0:
        res["result"] = "fail"
        res["api_comment"] = "card not found"
      else:

        print(">>>>", len(rows))

        for row in rows:
          logical_card_id = row[0]

        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
        res["result"] = "success"

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

  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])

    res["result"] = "success"

  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"]

  print("cp.user")

  ## USER GET
  ##
  
  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]
      res["result"] = "success"
    else:
      res["result"] = "fail"
      res["api_comment"] = "user not found"

  ## USER ADD
  ##

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


    if ((not "password" in ctx) or
        (not "username" in ctx) ):
      res["api_comment"] = "invalid parameters, need username and password to create account"
      res["result"] = "fail"
    else:
      uname = ctx["username"]
      pword = ctx["password"]

      fields.append("active")
      fields.append("created")
      for f in user_fields:
        if f in ctx:  user_vals.append(ctx[f])
        elif f == "passwordhash":
          ha = hashlib.sha256()
          ha.update(str.encode(uname))
          ha.update(str.encode(pword))
          user_vals.append(ha.hexdigest())
        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
      res["result"] = "success"

  ## USER UPDATE
  ##

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

    if not "userid" in ctx:
      res["result"] = "fail"
      res["api_comment"] = "no userid specified"
    else:

      uname = ''
      query = "select username from users where userid = %s";
      cursor.execute(query, [userid])
      rows = cursor.fetchall()
      for row in rows:
        uname = row[0]

      if uname == '':
        res["result"] = "fail"
        res["api_comment"] = "could not find username"
      else:

        update_field = []
        update_val = []

        print("user_field:", user_fields)
        print("ctx:", ctx)

        for f in user_fields:
          if (f == "passwordhash") and ("password" in ctx):
            update_field.append(" passwordhash = %s ")
            ha = hashlib.sha256()
            ha.update(str.encode(uname))
            ha.update(str.encode(ctx["password"]))
            update_val.append(ha.hexdigest())
          elif f in ctx:
            update_field.append(f + "= %s")
            update_val.append(ctx[f])
          else:
            pass
            #update_val.append(None)
        update_val.append(userid)

        if len(update_field) == 0:
          print("NOPE")

        print("manage_user.update>>>", userid, ":".join(update_field), ":".join(update_val), len(update_field))

        query = "update users set " + ",".join(update_field) + " where userid = %s"

        print("WTFFF???", query)

        cursor.execute(query, update_val)
        res["userid"] = userid
        res["result"] = "success"

  ## USER DELETE
  ##

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

  ## USER SEARCH
  ##

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

    res["userids"] = []
    res["userid"] = userid
    res["result"] = "success"

    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()