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

import sys
import getopt

POPUFARE_API_VERSION = "2.0.0"

#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, db_info = { "user":_USER, "pass":_PASSWORD, "host":_HOST, "db":_DATABASE, "port":_PORT}):
  #_conn = mysql.connector.connect(user=_USER, password=_PASSWORD, host=_HOST, database=_DATABASE, port=_PORT)
  _conn = mysql.connector.connect(user=db_info["user"], password=db_info["pass"], host=db_info["host"], database=db_info["db"], port=db_info["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)
    elif ctx["function"] == "Reissue":
      res = Reissue(_conn, ctx)
    elif ctx["function"] == "RecycleCard":
      res = RecycleCard(_conn, ctx)
    elif ctx["function"] == "AddCardBlock":
      res = AddCardBlock(_conn, ctx)
    elif ctx["function"] == "Search":
      res = Search(_conn, ctx)
    elif ctx["function"] == "Pending":
      res = Pending(_conn, ctx)
    elif ctx["function"] == "ping":
      res["result"] = "success"
      res["data"] = "pong"

  _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 = { "result":"fail"}

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

  cursor = db.cursor()

  if action == "get":

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

    group_res["result"] = "success"

  elif action == "add":

    if "group_name" not in ctx:
      group_res["result"] = "fail"
      group_res["api_comment"] = "must provide group name to add"
      return res

    group_name = ctx["group_name"]

    group_id = -1
    cursor.execute("select max(group_id) from groups")
    x = cursor.fetchone()
    if x[0] is None:
      group_id = 1
    else:
      group_id = int(x[0]) + 1

    cursor.execute("insert into groups (group_id, group_name) values (%s, %s)", [group_id, group_name])

    group_res["result"] = "success"
    group_res["group_id"] = group_id
    group_res["group_name"] = group_name

  elif action == "getone":

    group_res["group"] = []

    query = "select group_id, group_name from groups where group_name = %s "
    cursor.execute(query, [ctx["group"]])
    row = cursor.fetchone()
    if not row:
      group_res["result"] = "fail"
      group_res["api_comment"] = "invalid group"
    else:
      group_res["result"] = "success"
      group_res["group_id"] = row[0]
  elif action == "default":
    return Group(db, {"action":"getone", "group":"TEST-ORG"})

  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 _fixup_card_pass_state(cursor, cid):

  num_active_query = "select count(active) from user_pass " + \
    " where logiacl_card_id = %s and expired = 0"
  cursor.execute(num_active_query, [cid])
  num_res = int(cursor.fetchone()[0])

  # nothing to do
  #
  if num_res == 1:
    return

  min_queue_query = "select min(queue_order) from user_pass " + \
    " where logical_card_id = %s and expired = 0 "
  cursor.execute(min_queue_query, [cid])
  minq = int(cursor.fetchone()[0])

  set_active_query = "update user_pass set active = 1 " + \
    " where logical_card_id = %s and expired = 0 and queue_order = %s "
  cursor.execute(set_active_query, [cid, minq])



def Reissue(db, ctx):
  res = {"result":"fail"}
  cursor = db.cursor()

  fields = [ "src_logical_card_id", "dst_logical_card_id" ]

  val = {}
  for f in fields:
    if f in ctx:
      if ctx[f] and len(ctx[f]) > 0:
        val[f] = ctx[f]

  if not ("src_logical_card_id" in val):
    return res

  if not ("dst_logical_card_id" in val):
    return res
  
  cursor.execute("select max(queue_order) from user_pass where logiacl_card_id = %s", [val["src_logical_card_id"]])
  cursor.execute("select count(queue_order) from user_pass where expired = 0 logiacl_card_id = %s", [val["src_logical_card_id"]])

  src_query = "select user_pass_id, active, expired, queue_order " + \
    " from user_pass where logical_card_id = %s " + \
    " order by queue_order asc "
  cursor.execute(src_query, [val["src_logical_card_id"]]);
  src_rows = cursor.fetchall()

  dst_card_order_queue = 0
  cursor.execute("select count(queue_order) from user_pass where logical_card_id = %s", [val["dst_logical_card_id"]])
  dst_pass_count = cursor.fetchone()[0];

  if int(dst_pass_count) > 0:
    dst_query = "select max(queue_order) from user_pass where logical_card_id = %s"
    cursor.execute(dst_query,  [val["dst_logical_card_id"]])
    dst_card_order_queue = int(cursor.fetchone()[0]);

  for src_row in src_rows:
    if (src_row[2] == 1): continue

    dst_card_order_queue += 1
    xfer_query = "update user_pass set active = 0, logical_card_id = %s, queue_order = %s where user_pass_id = %s"
    cursor.execute(xfer_query, [val["dst_logiacl_card_id"], dst_card_order_queue, src_row[0]] )

  _fixup_card_pass_state(val["dst_logical_card_id"])
  res["result"] = "success"

  return res

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

def RecycleCard(db, ctx):
  res = {"result":"fail"}
  cursor = db.cursor()

  fields = ["logical_card_id", "rfid_token", "mag_token", "group",
            "userid",
            "rule", "pass_class", "nrides_remain", "nrides_orig", "nday_orig" ]
  val = {}
  for f in fields:
    if f in ctx:
      if ctx[f] and len(ctx[f]) > 0:
        val[f] = ctx[f]

  if ((not ("logical_card_id" in val)) and
      (not ("rfid_token" in val)) and
      (not ("mag_token" in val))):
    return res

  logical_card_id = -1
  if not ("logical_card_id" in val):
    if "mag_token" in val:
      query = "select logical_card_id from user_card where mag_token = %s and active = 1" 
      cursor.execute(query, [val["mag_token"]])
      cid =  cursor.fetchone()
      if cid is not None:
        val["logical_card_id"] = cid[0]
      else:
        return res
    elif "rfid_token" in val:
      query = "select logical_card_id from user_card where rfid_token = %s and active = 1" 
      cursor.execute(query, [val["rfid_token"]])
      cid = cursor.fetchone()
      if cid is not None:
        val["logical_card_id"] = cid[0]
      else:
        return res


  group_info = Group(db, {"action":"default"})
  if "group" in val:
    group_info = Group(db, {"action":"getone", "group":val["group"]})

  if group_info["result"] != "success":
    res["api_comment"] = "invalid group"
    return res

  query = "update user_card set active = 0 where logical_card_id = %s"
  cursor.execute(query, [val["logical_card_id"]])

  card_info = {"action":"add", "group_id":group_info["group_id"], "active":1}
  if "mag_token" in val: card_info["mag_token"] = val["mag_token"]
  if "rfid_token" in val: card_info["rfid_token"] = val["rfid_token"]

  card_res = Card(db, card_info)
  if card_res["result"] != "success":
    res["result"] = "fail"
    res["api_comment"] = "failed to find card"
    return res

  res["logical_card_id"] = card_res["logical_card_id"]

  if "pass_class" in val:

    pass_opt = {"action":"add", "logical_card_id": res["logical_card_id"] }

    if val["pass_class"] == "OTHER":
      pass_opt["rule"] = val["rule"]
      Pass(db, pass_opt)
    elif val["pass_class"] == "NRIDE":
      pass_opt["rule"] = val["rule"]
      pass_opt["nrides_orig"] = val["nrides_orig"]
      pass_opt["nrides_remain"] = val["nrides_remain"]
      Pass(db, pass_opt)
    elif val["pass_class"] == "NDAY":
      pass_opt["rule"] = val["rule"]
      pass_opt["nday_orig"] = val["nday_orig"]
      Pass(db, pass_opt)
    

  return res


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

def Search(db, ctx):
  res = {"result":"fail"}
  cursor = db.cursor()

  fields = ["search_type", "search_string", "start", "count"]

  start = "0"
  count = "100"

  val = {}
  for f in fields:
    if f in ctx:
      if ctx[f] and len(ctx[f]) > 0:
        val[f] = ctx[f]

  if "start" in val: start = val["start"]
  if "count" in val: count = val["count"]

  if val["search_type"] == "card":

    t = val["search_string"]
    p = '%' + t + '%'

    query = "select logical_card_id, mag_token, rfid_token, userid, issued, firstused, lastused, group_id, issuetype"
    query += " from user_card where mag_token like %s "
    query += "   or rfid_token like %s "
    query += "   or comment like %s "
    query += "   or issuetype like %s "
    query += "  and active = 1 "
    query += " order by logical_card_id desc"
    query += " limit %s,%s "

    _v = [p,p, p, p, int(start), int(start) + int(count)]

    #cursor.execute(query, [p, p, p, p])
    cursor.execute(query, _v)

    rows = cursor.fetchall()
    res["data"] = []
    for row in rows:
      _d = {
        "iscard": 1,
        "logical_card_id": row[0],
        "mag_token": row[1],
        "rfid_token": row[2],
        "userid": row[3],
        "issued": row[4],
        "firstused": row[5],
        "lastused": row[6],
        "group_id": row[7],
        "issuetype": row[8]
      }
      res["data"].append( _d )

    res["result"] = "success"

  elif val["search_type"] == "user":

    t = val["search_string"]
    p = '%' + t + '%'

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

    query = "select userid, " + ", ".join(fields)
    query += " from users "
    query += " where " + " like %s or ".join(fields) + " like %s "
    query += "  and active = 1 "
    query += " order by userid desc"
    query += " limit %s,%s "

    print("user query:", query)

    _v = [p]*len(fields)
    _v.append(int(start))
    _v.append(int(start) + int(count))

    #cursor.execute(query, [p]*len(fields))
    cursor.execute(query, _v)
    rows = cursor.fetchall()
    res["data"] = []
    for row in rows:
      _d = { "userid": row[0], "isuser": 1 }
      for idx,f in enumerate(fields):
        _d[f] = row[idx+1]
      res["data"].append( _d )

    res["result"] = "success"

  elif val["search_type"] == "admin":
    t = val["search_string"]
    p = '%' + t + '%'

    fields = [ "username", "comment" ]

    query = "select userid, group_id, " + ", ".join(fields)
    query += " from admins "
    query += " where " + " like %s or ".join(fields) + " like %s "
    query += " and active = 1 "
    query += " order by userid desc"
    query += " limit %s,%s "

    print("user query:", query)

    _v = [p]*len(fields)
    _v.append(int(start))
    _v.append(int(start) + int(count))

    cursor.execute(query, _v)
    rows = cursor.fetchall()
    res["data"] = []
    for row in rows:
      _d = { "userid": row[0], "group_id": row[1], "isadmin": 1 }
      for idx,f in enumerate(fields):
        _d[f] = row[idx+2]
      res["data"].append( _d )

    res["result"] = "success"

  return res


##            _     _                     _   _     _            _    
##   __ _  __| | __| |   ___ __ _ _ __ __| | | |__ | | ___   ___| | __
##  / _` |/ _` |/ _` |  / __/ _` | '__/ _` | | '_ \| |/ _ \ / __| |/ /
## | (_| | (_| | (_| | | (_| (_| | | | (_| | | |_) | | (_) | (__|   < 
##  \__,_|\__,_|\__,_|  \___\__,_|_|  \__,_| |_.__/|_|\___/ \___|_|\_\
##                                                                    

def AddCardBlock(db, ctx):
  res = {"result":"fail"}
  cursor = db.cursor()

  fields = ["mag_token", "rfid_token", "group_id",
            "rule", "nday_orig", "nrides_remain", "nrides_orig",
            "count",
            "pass_rule", "pass_nrides_remain", "pass_nrides_orig",
            "pass_nday_orig", "pass_class"]

  val = {}
  for f in fields:
    if f in ctx:
      if ctx[f] and len(ctx[f]) > 0:
        val[f] = ctx[f]

  if not ("count" in val):
    res["result"] = "fail"
    res["api_comment"] = "count must be positive"
    return res

  if ((not ("mag_token" in val)) and
      (not ("rfid_token" in val))):
    res["result"] = "fail"
    res["api_comment"] = "mag_token and/or rfid_token must be specified"
    return res

  use_mag = False
  use_rfid = False
  if "mag_token" in val:
    use_mag = True
    pfx_mag_cred = val["mag_token"].split(":")[0]
    base_mag_cred = int(val["mag_token"].split(":")[-1])

  if "rfid_token" in val:
    use_rfid = True
    pfx_rfid_cred = ":".join(val["rfid_token"].split(":")[0:2])
    base_rfid_cred = int(val["rfid_token"].split(":")[-1])

  logical_card_ids = []
  for idx in range(int(val["count"])):

    card_info = {"action":"add", "group_id":val["group_id"], "active":1}
    if "mag_token" in val: card_info["mag_token"] = val["mag_token"]
    if "rfid_token" in val: card_info["rfid_token"] = val["rfid_token"]

    if use_mag:
      card_info["mag_token"] = pfx_mag_cred + ":" + str(base_mag_cred + idx)
    if use_rfid:
      card_info["rfid_token"] = pfx_rfid_cred + ":" + str(base_rfid_cred + idx)

    card_res = Card(db, card_info)
    if card_res["result"] != "success":
      res["result"] = "fail"
      res["api_comment"] = "failed to add card"
      return res
    logical_card_id = card_res["logical_card_id"]
 
    if "pass_rule" in val:
      pass_info = {"action":"add", "logical_card_id":logical_card_id, "active":1,
                   "rule" : val["pass_rule"] }
      if val["pass_class"] == "OTHER":
        pass
      elif val["pass_class"] == "NRIDE":
        pass_info["nrides_remain"] = val["pass_nrides_remain"]
        pass_info["nrides_orig"] = val["pass_nrides_orig"]
      elif val["pass_class"] == "NDAY":
        pass_info["nday_orig"] = val["pass_nday_orig"]
      pass_res = Pass(db, pass_info)

    logical_card_ids.append(logical_card_id)

  res["result"] = "success"
  res["logical_card_ids"] = logical_card_ids

  return res

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

def Pending(db, ctx):
  res = {"result":"fail"}
  cursor = db.cursor()

  fields = ["org_card_order_queue_id", "userid", "logical_card_id", "created", "processed", "comment" ]

  val = {}
  for f in fields:
    if f in ctx:
      if (type(ctx[f]) == int) or (ctx[f] and ((type(ctx[f]) != int) and (len(ctx[f]) > 0))):
        val[f] = ctx[f]
  if not "comment" in val:
    val["comment"] = ''

  if (ctx["action"] == "getall"):
    query = "select org_card_order_queue_id, userid, logical_card_id, "
    query += " date_format(created, '%Y-%m-%d %H:%i:%S'), "
    query += " date_format(processed,'%Y-%m-%d %H:%i:%S'), comment, pending "
    query += " from org_card_order_queue "
    query += " where pending = 1 "
    query += " order by org_card_order_queue_id asc"

    cursor.execute(query)
    rows =  cursor.fetchall()
    res["data"] = []
    for row in rows:

      _c = Card(db, {"action":"get", "logical_card_id":row[2]})

      mag_token = ""
      if "mag_token" in _c: mag_token = _c["mag_token"]
      rfid_token = ""
      if "rfid_token" in _c: rfid_token = _c["rfid_token"]

      _r = {"org_card_order_queue_id":row[0], "userid":row[1], "logical_card_id":row[2], "created": row[3],
            "processed":row[4], "comment":row[5], "pending":row[6],
            "mag_token":mag_token, "rfid_token":rfid_token}
      res["data"].append(_r)
    res["result"] = "success"

  if (ctx["action"] == "get"):

    query = "select org_card_order_queue_id, userid, logical_card_id, "
    query += " date_format(created, '%Y-%m-%d %H:%i:%S'), "
    query += " date_format(processed,'%Y-%m-%d %H:%i:%S'), comment, pending "
    query += " from org_card_order_queue "
    query += " where pending = 1 and org_card_order_queue_id = %s"

    cursor.execute(query, [val["org_card_order_queue_id"]])
    row = cursor.fetchone()
    if row is None:
      res["api_comment"] = "pending card not found"
      return res

    cols = ["org_card_order_queue_id", "userid", "logical_card_id", "created", "processed", "comment", "pending" ]
    res["pending_card"] = {}
    for idx,f in enumerate(cols):
      res["pending_card"][f] = row[idx]

    cid = res["pending_card"]["logical_card_id"]

    _r = CardInfo(db, {"action":"get", "logical_card_id":cid})
    if _r["result"] == "fail":
      return _r

    res["cardinfo"] = _r
    res["result"] = "success"

  elif (ctx["action"] == "add"):
    query = "insert into org_card_order_queue (userid, logical_card_id, comment, pending, created) values (%s,%s,%s,1,now())"
    cursor.execute(query, [val["userid"], val["logical_card_id"], val["comment"]])
    res["org_card_order_queue_id"] = cursor.lastrowid
    res["result"] = "success"

  elif (ctx["action"] == "process"):
    query = "update org_card_order_queue set pending = 0, processed = now() where org_card_order_queue_id = %s"
    cursor.execute(query, [val["org_card_order_queue_id"]])
    res["result"] = "success"

  db.commit()
  return res

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

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

def show_version(ofp):
  ofp.write("PopufareAPI version " +  POPUFARE_API_VERSION + "\n")
  ofp.flush()

def show_help(ofp):
  show_version(ofp)
  ofp.write("\nusage:\n")
  ofp.write("\n")
  ofp.write("    PopufareAPI [-h] [-v] [-c config] [-u dbuser] [-p dbpass] [-H dbhost] [-D db] [-P dbport] <json_data>\n")
  ofp.write("\n")
  ofp.write("  -c     config file\n")
  ofp.write("  -u     db user\n")
  ofp.write("  -p     db pass\n")
  ofp.write("  -H     db host\n")
  ofp.write("  -D     db name\n")
  ofp.write("  -P     db port\n")
  ofp.write("  -h     help (this screen)\n")
  ofp.write("  -v     show version\n")
  ofp.write("\n")
  ofp.write("example:\n")
  ofp.write("\n")
  ofp.write("  PopufareAPI -d '{\"function\":\"ping\"}\n")
  ofp.write("\n")
  ofp.flush()

if __name__ == "__main__":

  api_opt = {
    "db_info": {
      "user":"busapi",
      "pass":"bus",
      "host":"localhost",
      "db":"busdb",
      "port":5506
    },
    "config":"",
    "data":""
  }

  data = {}

  opts,args = getopt.getopt(sys.argv[1:], "hd:u:p:H:D:P:c:")

  for o,a in opts:
    if o == "-h":
      show_help(sys.stdout)
      sys.exit(0)
    elif o == "-c":
      api_opt["config"] = a

    elif o == "-u":
      api_opt["db_info"]["user"] = a
    elif o == "-p":
      api_opt["db_info"]["pass"] = a
    elif o == "-H":
      api_opt["db_info"]["host"] = a
    elif o == "-D":
      api_opt["db_info"]["db"] = a
    elif o == "-P":
      api_opt["db_info"]["port"] = int(a)

    #elif o == "-d":
    #  api_opt["data"] = json.loads(a)

    else:
      show_help(sys.stderr)
      sys.exit(-1)

  if len(args) < 1:
    sys.stderr.write("no data request\n")
    show_help(sys.stderr)
    sys.exit(-1)

  api_opt["data"] = json.loads(args[0])

  res = Request(api_opt["data"], api_opt["db_info"])
  print(json.dumps(res,indent=2))

  #conn = mysql.connector.connect(user=_USER, password=_PASSWORD, host=_HOST, database=_DATABASE, port=_PORT)
  #conn = mysql.connector.connect(user=api_opt["dbuser"], password=api_opt["dbpass"], host=api_opt["dbhost"], database=api_opt["db"], port=api_opt["dbport"])
  #main(conn)
  #conn.close()