popufare/server/scripts/RideLogicAPIQueryWrapper.pm

package RideLogicAPIQueryWrapper;

use strict;
use RideLogic ();
use RideLogicDBI;
use POSIX;

require RideLogic ;
require RideLogicDBI;
require Exporter;
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);

$VERSION     = "0.01";
@ISA         = qw( Exporter RideLogicDBI );
@EXPORT      = qw( );
@EXPORT_OK   = qw( );
%EXPORT_TAGS = ( DEFAULT => [ qw( ) ] );

my $PACKAGE_NAME = "RideLogicAPIQueryWrapper";
my $DEFAULT_GROUP_NAME = "ORG";

# our constructor
sub connect 
{
  my $class = shift;

  my $r = $class->RideLogicDBI::connect(@_);

  # default to raising an error
  if (!defined($_[3])) 
  {
    my $dbh = ${$r->{'dbh_ref'}};
  }

  return $r;


}


###################################################

sub unix_to_readable_time {
  my $unix_time = shift;
  my @a = ( $unix_time ? localtime($unix_time) : localtime() );
  return sprintf('%d-%02d-%02d %02d:%02d:%02d', (1900+$a[5]), (1+$a[4]), $a[3], $a[2], $a[1], $a[0]);
}


sub audit_users_start
{
  my $self = shift;
  my $id = shift;
  my $comment = shift;

  my $dbh = ${$self->{'dbh_ref'}};
  return RideLogic::audit_users_start($dbh, $id, $comment);
}

sub audit_users_end
{
  my $self = shift;
  my $id = shift;
  my $audit_id = shift;

  my $dbh = ${$self->{'dbh_ref'}};
  return RideLogic::audit_users_end($dbh, $id, $audit_id);
}

sub audit_admins_start
{
  my $self = shift;
  my $id = shift;
  my $comment = shift;

  my $dbh = ${$self->{'dbh_ref'}};
  return RideLogic::audit_admins_start($dbh, $id, $comment);
}

sub audit_admins_end
{
  my $self = shift;
  my $id = shift;
  my $audit_id = shift;

  my $dbh = ${$self->{'dbh_ref'}};
  return RideLogic::audit_admins_end($dbh, $id, $audit_id);
}

sub audit_user_card_start
{
  my $self = shift;
  my $id = shift;
  my $comment = shift;

  my $dbh = ${$self->{'dbh_ref'}};
  return RideLogic::audit_user_card_start($dbh, $id, $comment);
}

sub audit_user_card_end
{
  my $self = shift;
  my $id = shift;
  my $audit_id = shift;

  my $dbh = ${$self->{'dbh_ref'}};
  return RideLogic::audit_user_card_end($dbh, $id, $audit_id);
}

sub audit_user_pass_start
{
  my $self = shift;
  my $id = shift;
  my $comment = shift;

  my $dbh = ${$self->{'dbh_ref'}};
  return RideLogic::audit_user_pass_start($dbh, $id, $comment);

}

sub audit_user_pass_end
{
  my $self = shift;
  my $id = shift;
  my $audit_id = shift;

  my $dbh = ${$self->{'dbh_ref'}};
  return RideLogic::audit_user_pass_end($dbh, $id, $audit_id);

}

#####################
# Helper functions
#####################

sub gen_token {
  my $len = shift;
  my ($r, $tok);
  my @a = qw(0 1 2 3 4 5 6 7 8 9 a b c d e f);
  open R, "/dev/urandom";
  read(R, $r, $len/2);
  close R;

  my @r = split(//, $r);
  for (my $i=0; $i<$len/2; $i++) {
    $tok .= $a[ord($r[$i]) >> 4];
    $tok .= $a[ord($r[$i]) & 0xf];
  }

  return $tok;

}



####################################################

sub initiate_session {
  my $self = shift;
  my $srv_tok = shift;
  my $usr_tok = shift;
  my $ip = shift;

  my $dbh = ${$self->{'dbh_ref'}};

  $dbh->begin_work();
  my $q = $self->prepare("insert into
                          org_api_session (timestamp, server_token, user_token, ip, active)
                          values           (    now(),            ?,          ?,  ?,      1)");
  $q->execute($srv_tok, $usr_tok, $ip) || return 0;
  $dbh->commit();

  return 1;

}


####################################################

sub get_api_server_session_age {
  my $self = shift;
  my $pass = shift;
  my $tok = shift;
  my $ip = shift;
  my ($query, $result);

  my $dbh = ${$self->{'dbh_ref'}};
  $query = $dbh->prepare("select time_to_sec( timediff( now(), timestamp)) sec, server_token, user_token, ip, active
                            from org_api_session
                           where active = 1 and ip = ? and sha1( concat( ? , server_token )) = ? limit 1");
  $result = $query->execute($ip, $pass, $tok);
  my $row = $query->fetchrow_hashref;
  return -1 if (!$row);

  return $row->{'sec'};

}


####################################################

sub get_api_user_session_age {
  my $self = shift;
  my $pass = shift;
  my $tok = shift;
  my $ip = shift;

  my ($query, $result);

  my $dbh = ${$self->{'dbh_ref'}};
  $query = $self->prepare("select time_to_sec( timediff( now(), timestamp)) sec, 
                                  server_token, 
                                  user_token, 
                                  ip, 
                                  active
                             from org_api_session
                            where active = 1 
                              and ip = ? 
                              and sha1( concat( ? , user_token )) = ? 
                            limit 1");
  $result = $query->execute($ip, $pass, $tok);
  my $row = $query->fetchrow_hashref;
  return -1 if (!$row);

  return $row->{'sec'};
}

####################################################

sub deactivate_session {
  my $self = shift;
  my $pass = shift;
  my $tok = shift;
  my $ip = shift;

  my ($query, $result);
  my $dbh = ${$self->{'dbh_ref'}};

  $query = $self->prepare("update org_api_session 
                              set active = 0 
                            where ip = ? 
                              and active = 1
                              and sha1(concat(?,server_token)) = ?");
  $result = $query->execute($ip, $pass, $tok);

}


####################################################

####################################################

sub set_user_reset_attempts {
  my $self = shift;
  my $user_id = shift;
  my $n = shift;
  my ($query, $result);

  my $dbh = ${$self->{'dbh_ref'}};

  my $audit_id = $self->audit_users_start($dbh, $user_id, "RideLogicAPIQueryWrapper::set_user_reset_attempts : updating reset attempts");
  $query = $self->prepare("update users set reset_attempts = ? where userid = ?");
  $result = $query->execute($n, $user_id);
  $self->audit_users_end($dbh, $user_id, $audit_id);

}


####################################################

sub update_user_reset_attempts {
  my $self = shift;
  my $user_id = shift;
  my ($query, $result);

  my $dbh = ${$self->{'dbh_ref'}};

  $dbh->begin_work();
  my $audit_id = $self->audit_users_start($dbh, $user_id, "ridelogic_web_api: updating reset attempts");
  $query = $self->prepare("update users set reset_attempts = 0 where userid = ?");
  $result = $query->execute($user_id);
  $self->audit_users_end($dbh, $user_id, $audit_id);
  $dbh->commit();

}

##########################
#
# ...
#
##########################

sub create_register_email {
  my $self = shift;
  my $registration_token = shift;
  my $email = shift;
  my $FUNC_NAME = "create_register_email";
  my ($query, $result, $row);

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

#  $self->begin_work();
  $query = $self->prepare("insert into org_api_register_email (timestamp, token, active, email) values ( now() , ?, ?, ?)");
  $result = $query->execute( $registration_token, 1, $email);
#  $self->commit();

  $self->unlock_commit() if $lock_local;
  return 1;

}


sub get_register_email_info {
  my $self = shift;
  my $registration_token = shift;
  my $FUNC_NAME = "get_register_email_info";
  my ($query, $result, $row);

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  $query = $self->prepare("select timestamp, 
                                  token, 
                                  time_to_sec(timediff(now(), timestamp)) sec, 
                                  email
                             from org_api_register_email
                            where token = ? and active = 1
                            limit 1");
  $result = $query->execute( $registration_token );

  $row = $query->fetchrow_arrayref;
  if (!$row)
  {
    $self->unlock_rollback() if $lock_local;
    return (-1, undef);
  }

  my ($sec, $email) = ($row->[2], $row->[3]);

  $self->unlock_commit() if $lock_local;
  return ($row->[2], $row->[3]);

}


####################################################

sub deactivate_register_email {
  my $self = shift;
  my $registration_token = shift;
  my $FUNC_NAME = "deactivate_register_email";
  my ($query, $result, $row);

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  $query = $self->prepare("update org_api_register_email set active = 0 where token = ?");
  $result = $query->execute($registration_token);

  $self->unlock_commit() if $lock_local;
  return 1;

}


####################################################

sub insert_password_reset {
  my $self = shift;
  my $token = shift;
  my $userid = shift;
  my $email = shift;
  my $FUNC_NAME = "insert_password_reset";
  my ($query, $result, $row);

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;


  $query = $self->prepare("insert into org_api_password_reset (timestamp, token, userid, email, active) values ( now() , ?, ?, ?, 1)");
  $result = $query->execute( $token, $userid, $email );


  $self->unlock_commit() if $lock_local;
  return 1;

}

##########

sub deactivate_stale_password_resets {
  my $self = shift;
  my $token = shift;
  my $userid = shift;
  my $FUNC_NAME = "deactivate_stale_password_resets";
  my ($query, $result, $row);

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  $query = $self->prepare("update org_api_password_reset set active = 0 where userid = ? and token != ?");
  $result = $query->execute( $userid, $token );

  $self->unlock_commit() if $lock_local;
  return 1;

}

##########

sub increment_user_reset_attempts {
  my $self = shift;
  my $userid = shift;

  my $FUNC_NAME = "increment_user_reset_attempts";
  my ($query, $result, $row);

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my %thash;
  my $r = 
    $self->GetUser(\%thash, { UserId => $userid } );

  if (!$r)
  {
    $self->unlock_rollback() if $lock_local;
    return undef;
  }
  my $new_reset_attempts = $thash{ResetAttempts}+1;

  %thash = ();
  $r =
    $self->SetUser( \%thash, { UserId => $userid, ResetAttempts => $new_reset_attempts } );

  if (!$r)
  {
    $self->unlock_rollback() if $lock_local;
    return undef;
  }


  $self->unlock_commit() if $lock_local;
  return 1;

}

##########


sub get_password_reset_request_age {
  my $self = shift;
  my $tok = shift;
  my $FUNC_NAME = 'get_password_reset_request_age';

  my ($query, $result, $row);

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  $query = $self->prepare("select time_to_sec(timediff(now(), timestamp)) sec
                            from org_api_password_reset where token = ? and active = 1
                            order by timestamp desc
                            limit 1");
  $result = $query->execute($tok);

  $row = $query->fetchrow_arrayref;
  if (!$row)
  {
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  $self->unlock_commit() if $lock_local;
  return $row->[0];

}

##########

sub get_password_reset_request_user_id {
  my $self = shift;
  my $tok = shift;
  my $FUNC_NAME = 'get_password_reset_request_user_id';

  my ($query, $result, $row);

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  $query = $self->prepare("select userid 
                            from org_api_password_reset where token = ? and active = 1
                            order by timestamp desc
                            limit 1");
  $result = $query->execute($tok);

  $row = $query->fetchrow_arrayref;
  if (!$row)
  {
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  $self->unlock_commit() if $lock_local;
  return $row->[0];

}

##########

sub deactivate_password_reset_for_user {
  my $self = shift;
  my $userid = shift;

  my $FUNC_NAME = 'deactivate_password_reset';
  my ($query, $r);

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  $query = $self->prepare("update org_api_password_reset set active = 0 where userid = ? ");
  $r = $query->execute( $userid );

  $self->unlock_commit() if $lock_local;
}


##########

my %function_locked_table_map = (

  '_common' => {   'user_card' => 'w',
                   'user_pass' => 'w',
                   'active_rider_table' => 'w',
                   'audit_user_pass' => 'w',
                   'audit_user_card' => 'w',
                   'org_card_order_queue' => 'w',
                   'diagnostic_log' => 'w',
                   'groups' => 'r',
                   'users' => 'r',
                   'admins' => 'r',
                   'rule_class' => 'r',
                   'rule_mappings' => 'r',
                   'org_api_session' => 'w',
                   'org_default_card_value' => 'r',
                   'org_default_pass_value' => 'r' },

# inhered from common
  'GetCard'                     =>  '_common',
  'GetCards'                    =>  '_common',
  'IssueBlankCard'              =>  '_common',
  'AddCard'                     =>  '_common',
  'TransferCard'                =>  '_common',
  'RemoveCard'                  =>  '_common',
  'GetPendingQueueCard'         =>  '_common',
  'ProcessPendingQueue'         =>  '_common',
  'RemovePendingQueue'          =>  '_common',
  'insert_active_rider_table'   =>  '_common',
  'CreateCard'                  =>  '_common',
  'RemovePass'                  =>  '_common',
  'TransferPass'                =>  '_common',
  'AddPass'                     =>  '_common',


# explicit
  'get_user_id'                                  => { 'users' => 'r' , 'groups' => 'r' }  ,
  'CreateUser'                                   => { 'users' => 'w', 'audit_users' => 'w' , 'groups' => 'r' } ,
  'RemoveUser'                                   => { 'users' => 'w', 'user_card' => 'w', 'audit_users' => 'w' , 'audit_user_card' => 'w', 'groups' => 'r' } ,
  'GetUser'                                      => { 'users' => 'r' , 'groups' => 'r' }  ,
  'SetUser'                                      => { 'users' => 'w', 'audit_users' => 'w' , 'groups' => 'r' } ,
  'username_exists'                              => { 'users' => 'r' }  ,
  'valid_user'                                   => { 'users' => 'r' } ,
  'get_default_mag_track'                        => { 'org_default_card_value' => 'r' } ,
  'get_default_rflength'                         => { 'org_default_card_value' => 'r' } ,
  'get_default_rfsite'                           => { 'org_default_card_value' => 'r' } ,
  'construct_mag_token'                          => { 'org_default_card_value' => 'r' } ,
  'construct_rfid_token'                         => { 'org_default_card_value' => 'r' } ,
  'construct_user_card_credential_match'         => { 'org_default_card_value' => 'r', 'groups' => 'r' } ,
  'update_user_card'                             => { 'audit_user_card' => 'w', 'user_card' => 'w' } ,
  'GetDefaultCardGroupInfo'                      => { 'groups' => 'r' }  ,
  'get_group_name'                               => { 'groups' => 'r' }  ,
  'get_user_pass_group_name'                     => { 'user_card' => 'r', 'user_pass' => 'r', 'groups' => 'r' } ,
  'get_admin_group_name'                         => { 'groups' => 'r', 'admins' => 'r' } ,
  'get_group_id'                                 => { 'groups' => 'r' }  ,
#  'get_entry_group_name'                      => { 'groups' => 'r', $table => 'r' }  ,
  'disassociate_user_from_card'                  => { 'audit_user_card' => 'w', 'user_card' => 'w' } ,
  'associate_user_to_card'                       => { 'audit_user_card' => 'w', 'user_card' => 'w' } ,
  'GetIthacaCollegeLogicalCardId'                => { 'user_card' => 'r', 'groups' => 'r', 'org_default_card_value' => 'r' } ,
  'get_user_card_userid'                         => { 'user_card' => 'r' } ,
  'get_user_card_group_id'                       => { 'user_card' => 'r' } ,
  'GetLogicalCardId'                             => { 'org_default_card_value' => 'r', 'user_card' => 'r' } ,
  'user_card_active_pass_count'                  => { 'user_pass' => 'r' } ,
  'pending_card'                                 => { 'user_card' => 'r' } ,
  'delete_user_card_from_active_rider_table'     => { 'active_rider_table' => 'w' } ,
  'get_active_rider_table'                       => { 'active_rider_table' => 'r' } ,
  'deactivate_user_card'                         => { 'audit_user_card' => 'w', 'user_card' => 'w' } ,
  'user_card_credential_exists'                  => { 'user_card' => 'r' } ,
  'insert_user_card'                             => { 'audit_user_card' => 'w', 'user_card' => 'w' } ,
  'validate_active_rider_table_logical_card_id'  => { 'active_rider_table' => 'r' } ,
  'validate_rule'                                => { 'rule_mappings' => 'r', 'rule_class' => 'r' } ,
  'get_default_rule'                             => { 'rule_mappings' => 'r' } ,
  'get_rule_class'                               => { 'rule_class' => 'r' } ,
  'active_user_card_exists'                      => { 'org_default_card_value' => 'r', 'user_card' => 'r' } ,
  'insert_user_pass'                             => { 'audit_user_pass' => 'w', 'user_pass' => 'w' } ,
  'get_user_pass_logical_card_id'                => { 'user_pass' => 'r' } ,
  'GetUserPass'                                  => { 'user_card' => 'r', 'user_pass' => 'r', 'rule_class' => 'r' } ,
  'GetPassesOnCard'                              => { 'org_default_card_value' => 'r', 'user_card' => 'r', 'user_pass' => 'r', 'rule_class' => 'r', 'groups' => 'r' } ,
  'user_card_has_active_pass'                    => { 'user_pass' => 'r' } ,
  'activate_user_card_pass'                      => { 'user_pass' => 'w' } ,
  'user_card_pass_count'                         => { 'user_pass' => 'r' } ,
  'get_next_queue_order'                         => { 'user_pass' => 'r' } ,
  'deactivate_user_pass'                         => { 'audit_user_pass' => 'w', 'user_pass' => 'w', 'user_card' => 'r', 'rule_class' => 'r' } ,
  'update_user_pass'                             => { 'audit_user_pass' => 'w', 'user_pass' => 'w' } ,
  'get_pass_type_and_name'                       => { 'rule_class' => 'r', 'rule_mappings' => 'r' } ,
  'GetPricePoints'                               => { 'user_card' => 'r', 'groups' => 'r', 'org_default_card_value' => 'r', 'org_default_pass_value' => 'r', 'price_point' => 'r' } ,
  'check_dup_billing_log'                        => { 'billing_log' => 'r' } ,
  'insert_billing_log'                           => { 'billing_log' => 'w' } ,
  'diagnostic_log'                               => { 'diagnostic_log' => 'w' } ,
  'api_log'                                      => { 'org_api_log' => 'w' } ,
  'admin_exists'                                 => { 'admins' => 'r' } ,
  'get_admin_id'                                 => { 'admins' => 'r' }  ,
  'CreateAdmin'                                  => { 'admins' => 'w', 'audit_admins' => 'w', 'groups' => 'r' } ,

  'GetAdmin'                                     => { 'admins' => 'r', 'groups' => 'r' } ,

  'SetAdmin'                                     => { 'admins' => 'w', 'audit_admins' => 'w', 'groups' => 'r' } ,
  'RemoveAdmin'                                  => { 'admins' => 'w', 'audit_admins' => 'w' },
  'GetPendingQueue'                              => { 'org_card_order_queue' => 'r', user_card => 'r', 'org_default_card_value' => 'r', groups => 'r'  },

  'GetPassOptions'                               => { 'pass_option' => 'r', 'admins' => 'r', 'groups' => 'r', 'rule_class' => 'r', 'rule_mappings' => 'r', 'org_default_pass_value' => 'r'  },

  'SearchCards'                                  => { 'user_card' => 'r', 'users' => 'r', 'groups' => 'r' },
  'SearchUsers'                                  => { 'user_card' => 'r', 'users' => 'r', 'groups' => 'r' },
  'SearchAdmins'                                 => { 'admins' => 'r', 'groups' => 'r' },

  'create_register_email'                        => { 'org_api_register_email' => 'w' },
  'get_register_email_info'                      => { 'org_api_register_email' => 'r' },
  'deactivate_register_email'                    => { 'org_api_register_email' => 'w' },

  'insert_password_reset'                        => { 'org_api_password_reset' => 'w' },
  'increment_user_reset_attempts'                => { 'users' => 'w', 'audit_users' => 'w' } ,

  'get_password_reset_request_age'               => { 'org_api_password_reset' => 'r' } ,
  'get_password_reset_request_user_id'           => { 'org_api_password_reset' => 'r' } ,
  'deactivate_password_reset_for_user'           => { 'org_api_password_reset' => 'w' } ,

  'get_user_group_name' => { 'users' => 'r', 'groups' => 'r' },

# inhereted
  'get_user_card_group_name'      => 'GetCard',
  'get_dest_group_name'           => 'get_user_card_group_name',
  'get_dest_user_card_group_name' => 'GetCard',
  'GetDestLogicalCardId'          => 'GetLogicalCardId',
  'confirm_card_user_id'          => 'GetCard',


# none
  'valid_pass_param' => {}


);

#
# instead of having each function list the tables it needs,
# maybe store them in the database and retrieve them as needed?
# will consider for later.
sub get_table_access_permissions_for_function {
  my $self      = shift;
  my $func_name = shift;
  my $key;
  my $val;

  my $max_jump = scalar(keys(%function_locked_table_map));

  $key = $func_name;

  return undef if !exists($function_locked_table_map{$key});

  for (my $cur=0; $cur < $max_jump; $cur++) {
    $val = $function_locked_table_map{$key};
    return $val if (ref($val) eq 'HASH');
    $key = $val;
  }

  return undef;

}

sub begin_locked_transaction_for_function {
  my $self      = shift;
  my $func_name = shift;

  my $h = $self->get_table_access_permissions_for_function($func_name);
  return undef if !$h;
  return undef if scalar(keys(%$h))==0;
  return $self->begin_locked_transaction( $h ) ;
}


sub begin_locked_transaction_for_functions {
  my $self = shift;
#  my $faref = shift;

  my %lhash;


#  foreach my $func (@$faref) 
  foreach my $func (@_)
  {

    my $r = $self->get_table_access_permissions_for_function($func);
    #return undef if !$r;

    foreach my $k (keys(%$r)) 
    {

      # upgrade read access to write
      # but not the other way around
      if ( !exists($lhash{$k}) )
      {
        $lhash{$k} = $r->{$k};
      } 
      else 
      {
        $lhash{$k} = 'w' if ($r->{$k} eq 'w');
      }
    }
  }

  return $self->begin_locked_transaction(\%lhash);

}

## same as above but allow for additional tables to be locked
sub begin_locked_transaction_for_functions_and_tables {
  my $self = shift;
  my $func_aref = shift;
  my $table_href = shift;

  my %lhash;


  foreach my $func (@$func_aref)
  {

    my $r = $self->get_table_access_permissions_for_function($func);
    #return undef if !$r;

    foreach my $k (keys(%$r)) 
    {

      # upgrade read access to write
      # but not the other way around
      if ( !exists($lhash{$k}) )
      {
        $lhash{$k} = $r->{$k};
      } 
      else 
      {
        $lhash{$k} = 'w' if ($r->{$k} eq 'w');
      }
    }
  }

  foreach my $table (keys(%$table_href)) 
  {
    $lhash{$table} = $table_href->{$table}
  }

  return $self->begin_locked_transaction(\%lhash);

}


##########

#sub lock_common {
#  my $self = shift;
#
##  my $lock_tables =
##    $self->prepare("lock tables user_card write, " .
##                   " user_pass write, " .
##                   " active_rider_table write, " .
##                   " audit_user_pass write, " .
##                   " audit_user_card write, " .
##                   " org_card_order_queue write, " .
##                   " diagnostic_log write, " .
##                   " groups read, " .
##                   " users read, " .
##                   " rule_class read, " .
##                   " rule_mappings read, " .
##                   " org_default_card_value read, " .
##                   " org_default_pass_value read " 
##                   );
##  $lock_tables->execute();
##  $self->lock_active(1);
#
##  $self->begin_locked_transaction_for_function("_common");
#
#}

sub begin_locked_transaction_common {
  my $self = shift;

#  $self->lock_common();
#  $self->begin_work();

  $self->begin_locked_transaction_for_function("_common");

}

sub unlock {
  my $self = shift;
  my $unlock_tables = $self->prepare("unlock tables");
  $unlock_tables->execute();

  $self->lock_active(0);
}


sub begin_locked_transaction {
  my $self = shift;
  my $href = shift;
  my $n=0;

  my $query_str = "lock tables" ;
  while ( my ($k, $v) = each(%$href) )
  {
    $query_str .= "," if ($n++)>0;
    $query_str .= " $k ";
    if ( $v =~ m/^[rR]/ ) 
    {
      $query_str .= "read";
    }
    elsif ($v =~ m/^[wW]/ ) 
    {
      $query_str .= "write";
    }
  }

  $self->lock_active(1);

  my $query =  
    $self->prepare($query_str);
  $query->execute();

  $self->begin_work();
  return 1;
}

sub unlock_rollback {
  my $self = shift;

  $self->rollback();

  my $query = $self->prepare("unlock tables");
  $query->execute();

  $self->lock_active(0);

  return 1;

#  return $self->rollback();
}

sub unlock_commit {
  my $self = shift;
  my $query = $self->prepare("unlock tables");
  $query->execute();

  $self->lock_active(0);

  return $self->commit();
}



####################################################
##
##  User Functions
##
####################################################

my %users_field_map =
  ( userid => 'UserId',
    username => 'UserName', 
    passwordhash => 'PasswordHash',
    first_name => 'FirstName', 
    last_name => 'LastName',
    phone => 'Phone', 
    email => 'Email', 
    address => 'Address', 
    city => 'City', 
    state => 'State', 
    zip => 'ZIP', 
    comment => 'Comment',
    shipping_name => 'ShippingName',
    shipping_address => 'ShippingAddress', 
    shipping_city => 'ShippingCity',
    shipping_state => 'ShippingState', 
    shipping_zip => 'ShippingZIP',
    shipping_country_code => 'ShippingCountryCode', 
    shipping_country_name => 'ShippingCountryName',
    reset_attempts => 'ResetAttempts',
    active => 'Active',
    group_id => 'GroupId'
  );

####################################################


sub get_user_id {
  my $self = shift;
  my $phash = shift;
  my $FUNC_NAME = 'get_user_id';
  my ($query, $result, $row);

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $credential_match = " active = 1 ";
  my @credential_val;

  if ( defined($phash->{'UserName'}) )
  {
    $credential_match .= " and username = ? ";
    push @credential_val , $phash->{'UserName'};
  }

  if ( defined($phash->{'UserId'}) )
  {
    $credential_match .= " and userid = ? ";
    push @credential_val, $phash->{'UserId'};
  }

  if ( defined($phash->{'PasswordHash'}) )
  {
    $credential_match .= " and passwordhash = ? ";
    push @credential_val, $phash->{'PasswordHash'};
  }


  return undef if scalar(@credential_val)==0;

###
  if ( defined($phash->{'GroupId'}) )
  {
    $credential_match .= " and group_id = ? ";
    push @credential_val, $phash->{'GroupId'};
  }
  elsif ( defined($phash->{'GroupName'}) )
  {
    $credential_match .= " and group_id = ? ";
    push @credential_val, $self->get_group_id( $phash->{'GroupName'} );
  }
###


  $query = $self->prepare( "select userid " .
                          "  from users " . 
                          " where $credential_match " .
                          " limit 1 ");
  $result = $query->execute(@credential_val);
  $row = $query->fetchrow_arrayref;

#  $rhash->{'UserId'} = $row->[0];

  if (!$row) 
  {
    $self->unlock_rollback() if $lock_local;
    return undef;
  }
  $self->unlock_commit() if $lock_local;

  return $row->[0];

}

####################################################

sub CreateUser {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;
  my ($query, $result);
  my $FUNC_NAME = "CreateUser";

  my %blacklist_field = ( active => 1, created => 1, reset_attempts => 1, userid => 1 );

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  if ( !defined($phash->{'UserName'}) ||
        $self->username_exists($phash->{'UserName'}) )
  {
    $rhash->{'Reason'} = "Invalid username";
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  # default group or group name to group id conversion
  if ( !defined($phash->{GroupId}) )
  {
    my $group_id = $self->get_group_id( $phash->{GroupName} || $DEFAULT_GROUP_NAME );

    if (!$group_id)
    {
      $rhash->{'Reason'} = "Invalid GroupName";
      $self->unlock_rollback() if $lock_local;
      return undef;
    }

    $phash->{GroupId} = $group_id;

#    push @db_field, 'group_id';
#    push @param, $group_id;
  }


  my @db_field;
  my @param;
  while (my ($k, $v) = each(%users_field_map) )
  {
    next if ( $blacklist_field{lc($k)} );
    push @db_field, $k;
    push @param, $phash->{$v};
  }

  my $audit_id = $self->audit_users_start(undef, "$PACKAGE_NAME: $FUNC_NAME");
  $query = $self->prepare("insert into users " .
                          " ( created, active, reset_attempts, " . join(", ", @db_field) . " ) values " .
                          " ( now(), 1, 0 " . ", ?"x(scalar(@db_field)) . " )");
  $result = $query->execute(@param);
  my $user_id = $self->last_insert_id();
  $self->audit_users_end($user_id, $audit_id);

  $query = $self->prepare("select userid, " . join(', ', @db_field) . " from users where userid = ?");
  $query->execute($user_id);
  my $href = $query->fetchrow_hashref;
  for (my $i=0; $i<scalar(@db_field); $i++)
  {
    $rhash->{$users_field_map{$db_field[$i]}} = $href->{$db_field[$i]};
  }
  $rhash->{'UserId'} = $href->{'userid'};

  $self->unlock_commit() if $lock_local;
  return 1;

}

####################################################

sub RemoveUser {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;
  my ($query, $result);
  my $FUNC_NAME = "RemoveUser";

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $user_id = $self->get_user_id($phash);

  if (!$user_id) 
  {
    $rhash->{'Reason'} = "Invalid user credential";
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  my %cards;
  my $r = $self->GetCards( \%cards, { UserId => $user_id } );
  if (!$r)
  {
    $rhash->{'Reason'} = $cards{'Reason'};
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  my $n_card = $cards{NCard};
  for (my $k=0; $k < $n_card ; $k++) 
  {
    my $ind = "";
    $ind = "[$k]" if $k;

    $self->disassociate_user_from_card($cards{"CardId$ind"});
  }


  my $audit_id = $self->audit_users_start(undef, "$PACKAGE_NAME: $FUNC_NAME");
  $query = $self->prepare("update users set active = 0 where userid = ? ");
  $result = $query->execute($user_id);
  $self->audit_users_end($user_id, $audit_id);


  $self->unlock_commit() if $lock_local;
  return 1;

}

####################################################

sub GetUser {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my ($query, $result);
  my $FUNC_NAME = "GetUser";

#  my %blacklist_field = ( 'passwordhash' => 1 );

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $userid = $self->get_user_id($phash);

  if (!$userid) 
  {
    $rhash->{'Reason'} = "Invalid user credential";
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  my $count=0;
  my $fields;
  while ( my ($k, $v) = each(%users_field_map) )
  {
#    next if ( $blacklist_field{lc($k)} );
    $fields .= ", " if ($count++);
    $fields .= " users.$k $v ";
  }

  $query = $self->prepare("select $fields , groups.group_name GroupName " .
                          "  from users , groups ".
                          " where users.userid = ? " .
                          "   and users.active = 1 " .
                          "   and users.group_id = groups.group_id " .
                          " limit 1");
  $query->execute($userid);
  my $row = $query->fetchrow_hashref;

  if (!$row)
  {
    $rhash->{'Reason'} = "Invalid User";
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  $self->unlock_commit() if $lock_local;

  while ( my ($k, $v) = each(%$row) )
  {
    $rhash->{$k} = $v;
  }

  return 1;

}


####################################################


sub SetUser {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my ($query, $result, $i, $j);
  my $FUNC_NAME= "SetUser";

  my $update_fields;
  my @update_val;

  my %blacklist_field = ( username => 1, userid => 1 );

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  # could be updating password.  only look up by username, userid and group
  my $userid = $self->get_user_id( 
    { 
      UserName => $phash->{UserName},
      UserId => $phash->{UserId},
      GroupId => $phash->{GroupId},
      GroupName => $phash->{GroupName}
    }
  );

  if (!defined($userid)) 
  {
    $rhash->{'Reason'} = "Invalid user credential";
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  while ( my ($k, $v) = each(%users_field_map) )
  {
    next if $blacklist_field{lc($k)} || !exists($phash->{$v});
    $update_fields .= ", " if $update_fields;
    $update_fields .= " $k = ? ";
    push @update_val, $phash->{$v};
  }

  # special consideration for groups
  if ( !defined($phash->{GroupId}) )
  {
    if ( defined($phash->{GroupName}) )
    {
      my $group_id = $self->get_group_id( $phash->{GroupName} );
      if ( !$group_id )
      {
        $rhash->{'Reason'} = "Invalid GroupName";
        $self->unlock_rollback() if $lock_local;
        return undef;
      }

      $update_fields .= ", " if $update_fields;
      $update_fields .= " group_id = ? ";
      push @update_val, $group_id;
    }
  }


  if (scalar(@update_val)==0)
  {
    $rhash->{'Reason'} = "Nothing to update";
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  push @update_val, $userid;

  my $audit_id = 
    $self->audit_users_start($userid, "$PACKAGE_NAME: $FUNC_NAME");
  $query = 
    $self->prepare("update users " .
                   "   set $update_fields " . 
                   " where userid = ?");
  $result = 
    $query->execute(@update_val);
  $self->audit_users_end($userid, $audit_id );

  $rhash->{'UserId'} = $userid;

  $self->unlock_commit() if $lock_local;

  return 1;

}


####################################################

sub username_exists {
  my $self = shift;
  my $username = shift;
  my $FUNC_NAME = 'username_exists';
  my ($query, $result, $row);

  my $lock_local = ( $self->lock_active() ? 0 : 1 );

#  $self->begin_locked_transaction( { 'users' => 'r' } ) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  $query = $self->prepare("select count(userid) num " .
                          "  from users " .
                          " where username = ? " .
                          "   and active = 1 ");
  $result = $query->execute($username);
  $row = $query->fetchrow_arrayref;

  $self->unlock_commit() if $lock_local;

  return ($row->[0] != 0);

}

####################################################

sub valid_user {
  my $self = shift;
  my $userid = shift;
  my $FUNC_NAME = 'valid_user';

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
#  $self->begin_locked_transaction( 
#    { 
#      'users' => 'r' 
#    } 
#  ) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = 
    $self->prepare("select count(userid) num " .
                   "  from users " .
                   " where userid = ? " .
                   "   and username is not null " .
                   "   and passwordhash is not null");
  my $result = 
    $query->execute($userid);

  $self->unlock_commit() if $lock_local;

  return ($query->fetchrow_arrayref->[0] == 1);
}


####################################################
##
##  Card Functions
##
####################################################

my @user_card_field = 
 qw( issued 
     mag_token 
     rfid_token 
     comment 
     userid 
     active 
     deactivated 
     lastused 
     firstused 
     group_id 
     issuetype );


my %user_card_field_map =
  ( mag_token => 'MagToken',
    rfid_token => 'RFIDToken',
    logical_card_id => 'CardId',
    active => 'Active',
    comment => 'Comment',
    lastused => 'LastUsed',
    issued => 'Issued',
    firstused => 'FirstUsed',
    userid => 'UserId',
    deactivated => 'Deactivated',
    group_id => 'GroupId',
    issuetype => 'IssueType'
  );

###########
sub get_default_mag_track
{
  my $self = shift;
  my $group_id = shift;
  my $FUNC_NAME = 'get_default_mag_track';

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
#  $self->begin_locked_transaction( 
#    { 
#      'org_default_card_value' => 'r' 
#    } 
#  ) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = 
    $self->prepare("select mag_track " .
                   "  from org_default_card_value " .
                   " where group_id = ? " .
                   "    or group_id is null " .
                   " order by group_id desc " .
                   " limit 1");
  $query->execute($group_id);

  $self->unlock_commit() if $lock_local;

  return $query->fetchrow_arrayref->[0];
}

###########
sub get_default_rflength
{
  my $self = shift;
  my $group_id = shift;
  my $FUNC_NAME = 'get_default_rflength';

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
#  $self->begin_locked_transaction( 
#   { 
#      'org_default_card_value' => 'r' 
#    } 
#  ) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = 
    $self->prepare("select rf_length " .
                   "  from org_default_card_value " .
                   " where group_id = ? " .
                   "    or group_id is null " .
                   " order by group_id desc " .
                   " limit 1");
  $query->execute($group_id);

  $self->unlock_commit() if $lock_local;


  return $query->fetchrow_arrayref->[0];
}

###########

sub get_default_rfsite
{
  my $self = shift;
  my $group_id = shift;
  my $FUNC_NAME = 'get_default_rfsite';

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = 
    $self->prepare("select rf_site " .
                   "  from org_default_card_value " .
                   " where group_id = ? " .
                   "    or group_id is null " .
                   " order by group_id desc " .
                   " limit 1");
  $query->execute($group_id);

  $self->unlock_commit() if $lock_local;

  return $query->fetchrow_arrayref->[0];
}

###

sub construct_mag_token
{
  my $self = shift;
  my $phash = shift;
  my $FUNC_NAME = 'construct_mag_token';

  return $phash->{'MagToken'} if $phash->{'MagToken'};
  return undef if !$phash->{'MagStripe'};

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $mag_track = ( $phash->{'MagTrack'} || $self->get_default_mag_track( $phash->{'GroupId'} ) );

  $self->unlock_commit() if $lock_local;

  return $mag_track . ":" . $phash->{'MagStripe'};
}

###

sub construct_rfid_token
{
  my $self = shift;
  my $phash = shift;
  my $FUNC_NAME = 'construct_rfid_token';

  return $phash->{'RFIDToken'} if $phash->{'RFIDToken'};
  return undef if !$phash->{'RFID'};

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;


  my $rflength  = ( $phash->{'RFLength'} || $self->get_default_rflength( $phash->{'GroupId'} ) ); 
  my $rfsite    = ( $phash->{'RFSite'}   || $self->get_default_rfsite( $phash->{'GroupId'} ) );

  $self->unlock_commit() if $lock_local;

  return $rflength. ":" . $rfsite . ":" . $phash->{'RFID'};
}

###########
sub construct_user_card_credential_match
{
  my $self = shift;
  my $phash = shift;
  my $FUNC_NAME = 'construct_user_card_credential_match';

  my $credential_match = "true";
  my @credential_val;

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;


  if (defined($phash->{'MagToken'}) || defined($phash->{'MagStripe'}) )
  {
    $credential_match .= " and mag_token = ? ";
    push @credential_val, $self->construct_mag_token($phash);
  }


  if (defined($phash->{'RFIDToken'}) || defined($phash->{'RFID'}) )
  {
    $credential_match .= " and rfid_token = ? ";
    push @credential_val, $self->construct_rfid_token($phash);
  }

  if (defined($phash->{'CardId'}))
  {
    $credential_match .= " and logical_card_id = ? ";
    push @credential_val, $phash->{'CardId'};
  }

  return ($credential_match, \@credential_val) if (scalar(@credential_val)==0);

###
  if (defined($phash->{'GroupId'}))
  {
    $credential_match .= " and group_id = ? ";
    push @credential_val, $phash->{'GroupId'};
  }
  elsif (defined($phash->{'GroupName'}))
  {
    $credential_match .= " and group_id = ? ";
    push @credential_val, $self->get_group_id( $phash->{'GroupName'} );
  }
###

  $self->unlock_commit() if $lock_local;

  return ($credential_match, \@credential_val);

}

###

sub update_user_card {
  my $self = shift;
  my $logical_card_id = shift;
  my $param_hash = shift;
  my $FUNC_NAME = "update_user_card";

  return undef if !$logical_card_id;

  my $str;
  my @val;

  while ( my ($k, $v) = each(%$param_hash) )
  {
    next if (!$user_card_field_map{$k});
    $str .= " , " if $str;
    $str .= " $k = ? ";
    push @val, $v;
  }
  return undef if (scalar(@val)==0);

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
#  $self->begin_locked_transaction( 
#      { 
#        'audit_user_card' => 'w', 
#        'user_card' => 'w'
#      } 
#    ) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $audit_id = 
    $self->audit_user_card_start($logical_card_id, "$PACKAGE_NAME: $FUNC_NAME");
  my $query =
    $self->prepare("update user_card set $str where logical_card_id = ? ");
  $query->execute(@val, $logical_card_id);
  $self->audit_user_card_end($logical_card_id, $audit_id);

  $self->unlock_commit() if $lock_local;

  return 1;
}

###########

sub GetCard {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;
  my $FUNC_NAME = 'GetCard';
  my ($query, $result);

  my @field;
  my @db_field;

  my %blacklisted_field = ( acitve => '1' );

  my %thash = %$phash;

  while ( my ($k, $v) = each(%user_card_field_map) ) 
  {
    next if (defined($blacklisted_field{$k}));
    push @field, $v;
    push @db_field, "$k $v";
  }

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

#  $phash->{GroupId} = $self->get_group_id( $phash->{GroupName} || $DEFAULT_GROUP_NAME )
#    if (!$phash->{GroupId});

#  my $logical_card_id =
#    $self->GetLogicalCardId($phash);

  my $logical_card_id;
  if ($thash{CardId}) 
  {
    $logical_card_id = $self->GetLogicalCardId( { CardId => $thash{CardId} } )
  } 
  else 
  {
    $thash{GroupId} = $self->get_group_id( $phash->{GroupName} || $DEFAULT_GROUP_NAME )
      if (!$phash->{GroupId});

    $logical_card_id =
      $self->GetLogicalCardId(\%thash);
  }

  if (!$logical_card_id)
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{'Reason'} = "Invalid Card";

    return undef;
  }

  $query = 
    $self->prepare("select user_card." . join(", user_card.", @db_field) . " , groups.group_name GroupName " . 
                   "  from user_card, groups " . 
                   " where user_card.logical_card_id = ? " .
                   "   and user_card.active = 1 " .
                   "   and user_card.group_id = groups.group_id " .
                   " order by user_card.logical_card_id " .
                   " limit 1");
  $result = $query->execute($logical_card_id);

  my $row = $query->fetchrow_hashref;
  if (!$row)
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{'Reason'} = "Invalid Credential";
    return undef;
  }

  ($rhash->{'MagTrack'}, $rhash->{'MagStripe'}) = 
    split(/:/, $row->{'MagToken'});

  ($rhash->{'RFLength'}, $rhash->{'RFSite'}, $rhash->{'RFID'}) = 
    split(/:/, $row->{'RFIDToken'});

  for (my $i=0; $i<scalar(@field); $i++) 
  {
    next if ($field[$i] =~ /Mag(Track|Stripe)|RF(Length|Site|ID)/) and ($field[$i] ne 'RFIDToken');
    $rhash->{$field[$i]} = $row->{$field[$i]};
  }
  $rhash->{GroupName} = $row->{GroupName};

  $self->unlock_commit() if $lock_local;

  return 1;

}


####################################################

#sub get_cards {
sub GetCards {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my $FUNC_NAME = 'GetCards';

  my ($query, $result);
  my @field;
  my @db_field;

  my %blacklisted_field = ( active => '1' );

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

#  my $user_id = $self->get_user_id($phash);
  my $user_id = $self->get_user_id( 
    { 
      UserName => $phash->{UserName},
      UserId => $phash->{UserId},
      GroupId => $phash->{UserGroupId},
      GroupName => $phash->{UserGroupName}
    }
  );

  my $max_card = $phash->{'MaxCard'};

  if (!defined($user_id))
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{'Reason'} = "Invalid UserId";

    return undef;
  }

  while ( my ($k, $v) = each(%user_card_field_map) ) 
  {
    next if (defined($blacklisted_field{$k}));
    push @field, $v;
    push @db_field, "$k $v";
  }

  my @param;
  push @param, $user_id;

  my $limit;
  if ($max_card)
  {
    $limit = " limit ? ";
    push @param, $max_card;
  }


  $query = 
    $self->prepare("select " . join(", ", @db_field) . " " .
                   "  from user_card " .
                   " where userid = ? " . 
                   "   and active = 1 " .
                   " order by CardId " . 
                   " $limit ");
  $result = $query->execute(@param);

  my $k=0;
  my $ind = '';
  while (my $row = $query->fetchrow_hashref) 
  {
    last if $max_card && ($k>=$max_card);

    ($rhash->{"MagTrack" . $ind }, $rhash->{"MagStripe" . $ind }) = 
      split(/:/, $row->{'MagToken'});

    ($rhash->{"RFLength" . $ind }, $rhash->{"RFSite" . $ind }, $rhash->{"RFID" . $ind }) = 
      split(/:/, $row->{'RFIDToken'});

    for (my $i=0; $i<scalar(@field); $i++) 
    {
      next if ($field[$i] =~ /Mag(Track|Stripe)|RF(Length|Site|ID)/) and ($field[$i] ne 'RFIDToken');
      $rhash->{ $field[$i] . $ind } = $row->{$field[$i]};
    }

    $ind = "[" . (++$k) . "]";

  }
  $rhash->{'NCard'} = $k;

  $self->unlock_commit() if $lock_local;

  return 1;

}


####################################################

### DEFAULT TO ORG
### this might need to change depending on how the
### system evolves
#sub get_default_card_group_info {
sub GetDefaultCardGroupInfo
{
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my $FUNC_NAME = 'GetDefaultCardGroupInfo';

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = 
    $self->prepare("select group_id GroupId, group_name GroupName from groups where group_name = ? ");
  $query->execute($DEFAULT_GROUP_NAME);
  my $row = $query->fetchrow_hashref;

  while ( my ($k, $v) = each(%$row) )
  {
    $rhash->{$k} = $v;
  }

  $self->unlock_commit() if $lock_local;

  return 1;
}

###

sub get_group_name {
  my $self = shift;
  my $phash = shift;
  my $FUNC_NAME = 'get_group_name';

  return $phash->{GroupName} if ($phash->{GroupName});

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query =
    $self->prepare("select group_name from groups where group_id = ?");
  $query->execute($phash->{GroupId});

  my $row = $query->fetchrow_arrayref;

  $self->unlock_commit() if $lock_local;
  return $DEFAULT_GROUP_NAME if !$row;
  return $row->[0];

}

###

sub get_user_card_group_name {
  my $self = shift;
  my $phash = shift;
  my $FUNC_NAME = 'get_user_card_group_name';

  my %r;
  $self->GetCard(\%r, $phash);
  return $r{GroupName};

}

###

sub get_dest_group_name {
  my $self = shift;
  my $phash = shift;

  my %thash;

  $thash{GroupId}   = $phash->{DestGroupId}   if $phash->{DestGroupId};
  $thash{GroupName} = $phash->{DestGroupName} if $phash->{DestGroupName};

  #return $self->get_user_card_group_name(\%thash);
  return $self->get_group_name(\%thash);

}

sub get_dest_user_card_group_name {
  my $self = shift;
  my $phash = shift;

  my $to_logical_card_id   = $self->GetDestLogicalCardId($phash);

  my %r;
  $self->GetCard(\%r, { CardId => $to_logical_card_id } );
  return $r{GroupName};

}

###

sub get_user_pass_group_name {
  my $self = shift;
  my $phash = shift;

  my $FUNC_NAME = 'get_user_pass_group_name';

  return undef if (!$phash->{PassId});

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = 
    $self->prepare("select groups.group_name 
                      from groups, 
                           user_card, 
                           user_pass
                     where user_pass.user_pass_id = ?
                       and user_card.logical_card_id = user_pass.logical_card_id
                       and groups.group_id = user_card.group_id");
  $query->execute($phash->{PassId});
  my $row = $query->fetchrow_arrayref;

  $self->unlock_commit() if $lock_local;

  return undef if !$row;
  return $row->[0];

}

###

sub get_user_group_name {
  my $self = shift;
  my $phash = shift;
  my $FUNC_NAME = 'get_user_group_name';

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $user_id = $self->get_user_id($phash);

  my $query =
    $self->prepare("select groups.group_name 
                      from users, groups
                     where users.userid = ?
                       and users.group_id = groups.group_id ");
  $query->execute($user_id);

  my $row = $query->fetchrow_arrayref;

  $self->unlock_commit() if $lock_local;

  return undef if !$row;
  return $row->[0];

  #return $DEFAULT_GROUP_NAME;

}

###

sub get_admin_group_name {
  my $self = shift;
  my $phash = shift;

  my $FUNC_NAME = 'get_admin_group_name';

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $str = " admins.active = 1 ";
  my @val;
  if ($phash->{UserId})
  {
    $str .= " and admins.userid = ? ";
    push @val, $phash->{UserId};
  }

  if ($phash->{UserName})
  {
    $str .= " and admins.username = ? ";
    push @val, $phash->{UserName};
  }

  my $query = $self->prepare("select groups.group_name 
                                from groups,
                                     admins
                               where $str
                                 and groups.group_id = admins.group_id");
  $query->execute(@val);
  my $row = $query->fetchrow_arrayref;

  $self->unlock_commit() if $lock_local;

  return $row->[0];


}

###

sub get_group_id 
{
  my $self = shift;
  my $group_name = shift;

  my $FUNC_NAME = 'get_group_id';

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
#  $self->begin_locked_transaction( { 'groups' => 'r' } ) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query =
    $self->prepare("select group_id from groups where group_name = ? ");
  $query->execute($group_name);
  my $row = $query->fetchrow_arrayref;

  $self->unlock_commit() if $lock_local;

  return undef if !$row;
  return $row->[0];
}

###

my %table_id_field = (
  user_card => 'logical_card_id',
);

###

sub get_entry_group_name
{
  my $self = shift;
  my $table = shift;
  my $id = shift;

  my $FUNC_NAME = 'get_entry_group_name';


  return undef if (!$table_id_field{$table});

  my $lock_local = ( $self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'groups' => 'r', $table => 'r' } ) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query =
    $self->prepare("select groups.group_name " .
                    "  from $table, groups " .
                    " where $table." . $table_id_field{$table} . " = ?" .
                    "   and groups.group_id = $table.group_id");
  $query->execute($id);

  my $row = $query->fetchrow_arrayref;

  $$self->unlock_commit() if $lock_local;

  return undef if !$row;
  return $row->[0];
}


############
#sub issue_blank_card {
sub IssueBlankCard
{
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my $FUNC_NAME = "IssueBlankCard";
  my ($query, $result);

  my %blacklisted_field = ( active => 1, mag_token => 1, rfid_token => 1, issued => 1, group_id => 1 );

  my @val;
  my @db_field;

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

#  my $user_id = $self->get_user_id($phash);
  my $user_id = $self->get_user_id( 
    { 
      UserName => $phash->{UserName},
      UserId => $phash->{UserId},
      GroupId => $phash->{UserGroupId},
      GroupName => $phash->{UserGroupName}
    }
  );



  if (!defined($user_id))
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{'Reason'} = "Invalid UserId";
    return undef;
  }

  $phash->{UserId} = $user_id;

  while ( my ($k, $v) = each(%user_card_field_map) )
  {
    next if defined($blacklisted_field{$k});
    push @db_field, $k;
    push @val, $phash->{$v};
  }

  my %group_hash;
  $self->GetDefaultCardGroupInfo(\%group_hash, $phash);
  my $group_id = $group_hash{'GroupId'};


  my $audit_id 
    = $self->audit_user_card_start(undef, "$PACKAGE_NAME: $FUNC_NAME");
  $query = 
    $self->prepare("insert into user_card ( issued, active, group_id, " . join(", ", @db_field) . " ) " .
                   "values ( now(), 1, ? " . ", ?"x(scalar(@db_field)) . " ) ");
  $result = 
    $query->execute($group_id, @val);
  $rhash->{'CardId'} = $self->last_insert_id();
  $self->audit_user_card_end($rhash->{'CardId'}, $audit_id );

  $query = 
    $self->prepare("insert into org_card_order_queue (userid, logical_card_id, created, processed, comment, pending) " .
                   "values (?, ?, now(), null, null, 1)");
  $result = 
    $query->execute($user_id, $rhash->{'CardId'});

  $self->unlock_commit() if $lock_local;

  # DO NOT put newly issued card into active_rider_table, will be handled whent he entry is removed from ordered queue
  return 1;

}


sub disassociate_user_from_card 
{
  my $self = shift;
  my $card_id = shift;
  my $FUNC_NAME = "disassociate_user_from_card";

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
#  $self->begin_locked_transaction( 
#      {
#        'audit_user_card' => 'w',
#        'user_card' => 'w'
#      }
#    ) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $audit_id = 
    $self->audit_user_card_start($card_id, "$PACKAGE_NAME: $FUNC_NAME");
  my $query = 
    $self->prepare("update user_card " .
                   "   set userid = null " .
                   " where logical_card_id = ?");
  my $result = 
    $query->execute($card_id);
  $self->audit_user_card_end($card_id);

  $self->unlock_commit() if $lock_local;

  return 1;
}

sub associate_user_to_card 
{
  my $self = shift;
  my $card_id = shift;
  my $user_id = shift;
  my $FUNC_NAME = "associate_user_to_card";

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
#  $self->begin_locked_transaction( 
#      {
#        'audit_user_card' => 'w',
#        'user_card' => 'w'
#      }
#    ) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $audit_id = 
    $self->audit_user_card_start($card_id, "$PACKAGE_NAME: $FUNC_NAME");
  my $query = 
    $self->prepare("update user_card " . 
                   "   set userid = ? " .
                   " where logical_card_id = ?");
  my $result = 
    $query->execute($user_id, $card_id);
  $self->audit_user_card_end($card_id, $audit_id );

  $self->unlock_commit() if $lock_local;

  return 1;
}


#sub get_ithacacollege_logical_card_id {
sub GetIthacaCollegeLogicalCardId {
  my $self = shift;
  my $phash = shift;

  my $FUNC_NAME = 'GetIthacaCollegeLogicalCardId';

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
#  $self->begin_locked_transaction(
#      {
#        'user_card' => 'r',
#        'groups' => 'r',
#        'org_default_card_value' => 'r'
#      }
#    ) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $mag_token = 
    $self->construct_mag_token( $phash );
  if (!$mag_token) 
  {
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  my $query =
    $self->prepare("select user_card.logical_card_id " .
                    " from user_card, groups " .
                   " where user_card.mag_token like concat(?, '__') " .
                     " and groups.group_name = 'ITHACACOLLEGE' " .
                     " and groups.group_id = user_card.group_id " .
                     " and user_card.active = 1 ");
  $query->execute($mag_token);
  my $row = $query->fetchrow_arrayref;
  if (!$row) 
  {
    $self->unlock_rollback() if $lock_local;
    return undef ;
  }

  $self->unlock_commit() if $lock_local;

  return $row->[0];
}



#########################
##
##
## Search Functions
##
##


sub SearchCards {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;
  my $FUNC_NAME = "SearchCards";

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my %group_hash;
  if ($phash->{NGroup})
  {
    my $n_group = $phash->{NGroup};
    for (my $k=0; $k<$n_group; $k++) 
    {
      my $ind = "";
      $ind = "[$k]" if $k;
      $group_hash{ $phash->{"GroupName$ind"} } = 1;
    }
  }

  my @param;
  for (my $i=0; $i<8; $i++) {
    push @param, '%' . $phash->{SearchText} . '%' ;
  }

  my $has_limit = 0;
  if ($phash->{MaxItem})
  {
    $has_limit = 1;
    push @param, $phash->{MaxItem};
  }

  my $query =
    $self->prepare("select user_card.logical_card_id CardId, " .
                         " user_card.mag_token MagToken, " .
                         " user_card.rfid_token RFIDToken, " .
                         " user_card.comment Comment, " .
                         " user_card.userid UserId, " .
                         " user_card.lastused LastUsed, " .
                         " user_card.firstused FirstUsed, " .
                         " user_card.group_id GroupId, " .
                         " users.username UserName, " .
                         " groups.group_name GroupName " .
                    " from user_card " .
                    " left join users on (users.userid = user_card.userid and users.active = 1) " .
                    " left join groups on (groups.group_id = user_card.group_id) " .
                    " where user_card.active = 1 " .
                      " and ( user_card.mag_token like ? " .
                         " or user_card.rfid_token like ? " .
                         " or user_card.comment like ? " .
                         " or user_card.issued like ? " .
                         " or user_card.lastused like ? " .
                         " or user_card.firstused like ? " .
                         " or users.username like ? " .
                         " or groups.group_name like ? ) " .
                   ( $has_limit ? " limit ? " : "" )  );
  $query->execute(@param);                   

  my $count=0;
  while (my $row = $query->fetchrow_hashref) 
  {
    next if ( !$group_hash{ $row->{GroupName} });

    my $ind = ( $count ? "[$count]" : "" );

    foreach my $k (keys(%$row)) 
    {
      $rhash->{"$k$ind"} = $row->{$k};
    }
    $count++;
  }
  $rhash->{NItem} = $count;


  $self->unlock_commit() if $lock_local;
  return 1;

}

##

sub SearchUsers {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;
  my $FUNC_NAME = "SearchUsers";

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my %group_hash;
  if ($phash->{NGroup})
  {
    my $n_group = $phash->{NGroup};
    for (my $k=0; $k<$n_group; $k++) 
    {
      my $ind = "";
      $ind = "[$k]" if $k;
      $group_hash{ $phash->{"GroupName$ind"} } = 1;
    }
  }

  my @param;

  my $has_limit = 0;
  if ($phash->{MaxItem})
  {
    $has_limit = 1;
  }

  my %blacklist_field = ( active => 1, reset_attempts => 1, passwordhash => 1  );

  my $search_fields  = "false";
  my $returned_fields;
  while ( my ($k, $v) = each(%users_field_map) )
  {
    next if $blacklist_field{lc($k)};
    $search_fields .= " or ";
    $search_fields .= " users.$k  like ? ";
    push @param, '%' . $phash->{SearchText} . '%';

    $returned_fields .= ", " if $returned_fields;
    $returned_fields .= " users.$k $v ";
  }

  if ($has_limit)
  {
    push @param, $phash->{MaxItem};
  }


  my $query =
    $self->prepare("select $returned_fields , groups.group_name GroupName " .
                    " from users, groups " .
                    " where users.active = 1 " .
                      " and users.group_id = groups.group_id " .
                      " and ( $search_fields  ) " .
                   ( $has_limit ? " limit ? " : "" )  );
  $query->execute(@param);                   

  my $count=0;
  while (my $row = $query->fetchrow_hashref) 
  {

    next if (!$group_hash{ $row->{GroupName} });

    my $ind = ( $count ? "[$count]" : "" );

    foreach my $k (keys(%$row)) 
    {
      $rhash->{"$k$ind"} = $row->{$k};
    }
    $count++;
  }
  $rhash->{NItem} = $count;

  $self->unlock_commit() if $lock_local;
  return 1;

}

##
my %admins_field_map = (
  username => 'UserName',
  password => 'PasswordHash',
  userid => 'UserId',
  group_id => 'GroupId'
);


sub SearchAdmins {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;
  my $FUNC_NAME = "SearchAdmins";

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my %blacklist_field = ( 'password' => 1 );

  my @param;

  my %group_hash;
  if ($phash->{NGroup})
  {
    my $n_group = $phash->{NGroup};
    for (my $k=0; $k<$n_group; $k++) 
    {
      my $ind = "";
      $ind = "[$k]" if $k;
      $group_hash{ $phash->{"GroupName$ind"} } = 1;
    }
  }

  my $has_limit = 0;
  if ($phash->{MaxItem})
  {
    $has_limit = 1;
  }

  my $search_fields  = "false";
  my $returned_fields;
  while ( my ($k, $v) = each(%admins_field_map) )
  {
    next if $blacklist_field{lc($k)};
    $search_fields .= " or ";
    $search_fields .= " admins.$k  like ? ";
    push @param, '%' . $phash->{SearchText} . '%';

    $returned_fields .= ", " if $returned_fields;
    $returned_fields .= " admins.$k $v ";
  }

  if ($has_limit)
  {
    push @param, $phash->{MaxItem};
  }


  my $query =
    $self->prepare("select $returned_fields , groups.group_name GroupName " .
                    " from admins left join groups on (admins.group_id = groups.group_id) " .
                    " where admins.active = 1 " .
                      " and ( $search_fields  ) " .
                   ( $has_limit ? " limit ? " : "" )  );
  $query->execute(@param);                   

  my $count=0;
  while (my $row = $query->fetchrow_hashref) 
  {

    next if (!$group_hash{ $row->{GroupName} });

    my $ind = ( $count ? "[$count]" : "" );

    foreach my $k (keys(%$row)) 
    {
      $rhash->{"$k$ind"} = $row->{$k};
    }
    $count++;
  }
  $rhash->{NItem} = $count;

  $self->unlock_commit() if $lock_local;
  return 1;

}

####################################################
#
# Associates a pre-existing card in the database to
# a user.
# 
#sub add_card {
sub AddCard 
{
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;
  my $FUNC_NAME = "AddCard";

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

#  my $user_id = $self->get_user_id($phash);
  my $user_id = $self->get_user_id( 
    { 
      UserName => $phash->{UserName},
      UserId => $phash->{UserId}
#      GroupId => $phash->{UserGroupId},
#      GroupName => $phash->{UserGroupName}
    }
  );

  if (!$user_id) 
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{'Reason'} = "Invalid User";
    return undef;
  }

  # we have to do special processing if it's an ithacacollege card
  my $logical_card_id =
   ( (lc($phash->{'Type'}) eq 'ithacacollege') ? 
     $self->GetIthacaCollegeLogicalCardId( $phash ) :
     $self->GetLogicalCardId( $phash ) );

  if (!$logical_card_id)
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{'Reason'} = "Invalid Card (1)";
    return undef;
  }

  my $card_orig_userid = 
    $self->get_user_card_userid( $logical_card_id );
                             
  # Card could be associated to 'ghost' user: A user
  #  without a username or password hash.  In this
  #  case, assigning the card is allowed.
  if ($card_orig_userid)
  {
    if ( $self->valid_user($card_orig_userid) )
    {
      $self->unlock_rollback() if $lock_local;
      $rhash->{'Reason'} = "Invalid Card (2)";
      return undef;
    }

    $self->disassociate_user_from_card($logical_card_id);
  }

  # set user_card entry to userid
  $self->associate_user_to_card($logical_card_id, $user_id);

  $self->unlock_commit() if $lock_local;

  $rhash->{'CardId'} = $logical_card_id;
  return 1;

}


#sub transfer_card {
sub TransferCard 
{
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my $FUNC_NAME = 'TransferCard';

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
#  $self->begin_locked_transaction_common() if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  $phash->{GroupId} = $self->get_group_id( $phash->{GroupName} || $DEFAULT_GROUP_NAME )
    if (!$phash->{GroupId});

  $phash->{ToGroupId} = $self->get_group_id( $phash->{ToGroupName} || $DEFAULT_GROUP_NAME )
    if (!$phash->{ToGroupId});

  my ($i, $j, $k, $n, $ind);
  my %thash;


  my $from_logical_card_id = $self->GetLogicalCardId($phash);
  my $to_logical_card_id   = $self->GetDestLogicalCardId($phash);

  if (!$from_logical_card_id || !$to_logical_card_id)
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{'Reason'} = "Invalid source or destination card (from $from_logical_card_id, to $to_logical_card_id)";
    return undef;
  }

  my $user_id =
    $self->get_user_card_userid($from_logical_card_id);

#  my $to_card_user_id =
#    $self->get_user_card_userid($to_logical_card_id);
#
#  if ($to_card_user_id != $user_id)
#  {
#    $self->unlock_rollback();
#    $rhash->{'Reason'} = "Permission Denied: Destination card not owned by user";
#    return undef;
#  }



  $self->GetPassesOnCard( \%thash, { CardId => $from_logical_card_id } );

  $n = $thash{'NPass'};
  for ($i=0; $i<$n; $i++) 
  {
    $ind = "[$i]" if $i>0;

    my %dummy;
#    $self->transfer_pass( \%dummy, { ToCardId => $to_logical_card_id, 
    $self->TransferPass( \%dummy, { ToCardId => $to_logical_card_id, 
                                     PassId => $thash{'PassId' . $ind } }, 0 );
  }

  $self->disassociate_user_from_card($from_logical_card_id);
  $self->associate_user_to_card($to_logical_card_id, $user_id);

  $self->unlock_commit() if $lock_local;

  $rhash->{'CardId'} = $to_logical_card_id;
  return 1;

}


####################################################

sub get_user_card_userid {
  my $self = shift;
  my $logical_card_id = shift;

  my $FUNC_NAME = 'get_user_card_userid';

  return undef if !$logical_card_id;

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction(
#      {
#        'user_card' => 'r'
#      }
#    ) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = 
    $self->prepare("select userid " .
                    " from user_card " .
                   " where logical_card_id = ? ");
  $query->execute($logical_card_id);
  my $row = $query->fetchrow_arrayref;
  if (!$row)
  {
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  $self->unlock_commit() if $lock_local;
  return $row->[0];
}

###

sub get_user_card_group_id {
  my $self = shift;
  my $logical_card_id = shift;

  my $FUNC_NAME = 'get_user_card_group_id';

  return undef if !$logical_card_id;

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction(
#      {
#        'user_card' => 'r'
#      }
#    ) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = 
    $self->prepare("select group_id " .
                    " from user_card " .
                   " where logical_card_id = ? ");
  $query->execute($logical_card_id);
  my $row = $query->fetchrow_arrayref;
  if (!$row)
  {
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  $self->unlock_commit() if $lock_local;
  return $row->[0];
}

###

#sub get_logical_card_id 
sub GetLogicalCardId
{
  my $self = shift;
  my $phash = shift;

  my $FUNC_NAME = 'GetLogicalCardId';

  my $lock_local = ($self->lock_active() ? 0 : 1);
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my ($credential_match, $credential_param_ref) =
    $self->construct_user_card_credential_match($phash);

  if (scalar(@$credential_param_ref)==0)
  {
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  my $query =
    $self->prepare("select logical_card_id " .
                   "  from user_card " .
                   " where $credential_match " .
                   "   and active = 1 " .
                   " limit 1 ");
  $query->execute(@$credential_param_ref);
  my $row = $query->fetchrow_arrayref;


  $self->unlock_commit() if $lock_local;

  return undef if !$row;
  return $row->[0];
}

#sub get_dest_logical_card_id {
sub GetDestLogicalCardId 
{
  my $self = shift;
  my $phash = shift;
#  return $self->get_logical_card_id( { #UserId => $phash->{'UserId'},

  my $FUNC_NAME = 'GetDestLogicalCardId';

  return $self->GetLogicalCardId( { MagToken=> $phash->{'ToMagToken'},
                                    MagTrack => $phash->{'ToMagTrack'},
                                    MagStripe => $phash->{'ToMagStripe'},
                                    RFLength => $phash->{'ToRFLength'},
                                    RFID => $phash->{'ToRFID'},
                                    RFIDToken => $phash->{'ToRFIDToken'},
                                    CardId => $phash->{'ToCardId'},
                                    GroupId => $phash->{'ToGroupId'} } );
}


####

sub user_card_active_pass_count {
  my $self = shift;
  my $logical_card_id = shift;

  my $FUNC_NAME = 'user_card_active_pass_count';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction(
#      {
#        'user_pass' => 'r'
#      }
#    ) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = 
    $self->prepare("select count(user_pass_id) ".
                   "  from user_pass " .
                   " where logical_card_id = ? " .
                   "   and expired = 0 " .
                   "   and active = 1 ");
  $query->execute($logical_card_id);

  $self->unlock_commit() if $lock_local;

  return $query->fetchrow_arrayref->[0];
}

#####

sub pending_card {
  my $self = shift;
  my $logical_card_id = shift;

  my $FUNC_NAME = 'pending_card';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'user_card' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = 
    $self->prepare("select mag_token, rfid_token " .
                   "  from user_card " .
                   " where logical_card_id = ? " .
                   "   and active = 1 " );
  $query->execute($logical_card_id);
  my $row = $query->fetchrow_arrayref;

  $self->unlock_commit() if $lock_local;

  return 0 if !$row;
  return !($row->[0]) && !($row->[1]);
}

#####

sub delete_user_card_from_active_rider_table {
  my $self = shift;
  my $logical_card_id = shift;

  my $FUNC_NAME = 'delete_user_card_from_active_rider_table';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'active_rider_table' => 'w' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  if (!$self->validate_active_rider_table_logical_card_id( $logical_card_id ))
  {
    $self->unlock_rollback() if $lock_local;
    return undef 
  }

  my $query = 
    $self->prepare(" insert into " .
                   " active_rider_table (logical_card_id, deleted) " .
                   "             values (              ?,       1) ");
  $query->execute($logical_card_id);

  $self->unlock_commit() if $lock_local;
  return 1;

}


#####


sub confirm_card_user_id {
  my $self = shift;
  my $phash = shift;
  my $user_id = shift;

  my $FUNC_NAME = 'confirm_card_user_id';

  my %thash;
  my $r = 
    $self->GetCard(\%thash, $phash);
  return 0 if (!$r) or ($thash{UserId} != $user_id);
  return 1;
}


#####

my %active_rider_table_map = (
  logical_card_id => 'logical_card_id',
  seq_num => 'seq_num',
  rfid_token => 'rfid_token',
  mag_token => 'mag_token',
  rule_name => 'rule_name',
  rule_param => 'rule_param',
  deleted => 'deleted'
  );


sub get_active_rider_table 
#sub GetActiveRiderTable
{
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my $FUNC_NAME = 'get_active_rider_table';

  my $logical_card_id = $phash->{'logical_card_id'};
  return undef if !$logical_card_id;

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'active_rider_table' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query =
    $self->prepare("select max(seq_num) m " .
                   "  from active_rider_table " .
                   " where deleted = 0 " .
                   "   and logical_card_id = ? ");
  $query->execute($logical_card_id);
  
  my $seq_num = $query->fetchrow_arrayref->[0];
  if (!$seq_num) 
  {
    $self->unlock_commit() if $lock_local;
    return undef;
  }

  $query =
    $self->prepare("select " . join(", ", keys(%active_rider_table_map)) .
                   "  from active_rider_table " .
                   " where logical_card_id = ? " .
                   "   and deleted = 0 " .
                   "   and seq_num = ?");
  $query->execute($logical_card_id, $seq_num);
  my $row = $query->fetchrow_hashref;

  $self->unlock_commit() if $lock_local;
  
  return undef if (!$row);
  foreach my $k (keys(%active_rider_table_map)) 
  {
    $rhash->{$active_rider_table_map{$k}} = $row->{$k};
  }

  return 1;
}


#####

sub deactivate_user_card {
  my $self = shift;
  my $logical_card_id = shift;
  my $FUNC_NAME = "deactivate_user_card";

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'audit_user_card' => 'w', 'user_card' => 'w' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;


  my $audit_id = 
    $self->audit_user_card_start($logical_card_id, "$PACKAGE_NAME: $FUNC_NAME");
  my $query = 
    $self->prepare("update user_card " .
                   "   set active = 0, " .
                   "       deactivated = now() " .
                   " where logical_card_id = ?");
  my $result = 
    $query->execute($logical_card_id);
  $self->audit_user_card_end($logical_card_id, $audit_id);

  $self->unlock_commit() if $lock_local;

}


###
# deactivate user_card entry
###
#sub remove_card {
sub RemoveCard
{
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;
  my $FUNC_NAME = "RemoveCard";

  my $lock_local = ($self->lock_active() ? 0 : 1);

  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

#  $phash->{GroupId} = $self->get_group_id( $phash->{GroupName} || $DEFAULT_GROUP_NAME )
#    if (!$phash->{GroupId});
  if ($phash->{GroupName} && !$phash->{GroupId}) 
  {
    $phash->{GroupId} = $self->get_group_id( $phash->{GroupName} );
  }


  my $logical_card_id =
    $self->GetLogicalCardId($phash);

  if (!$logical_card_id)
  {
    $self->unlock_rollback() if $lock_local;

    $rhash->{'Reason'} = "Invalid CardId";
    return undef;
  }

  # if card isn't an 'ordered' card
  if ( !$self->pending_card($logical_card_id) )
  {
    if (!$self->delete_user_card_from_active_rider_table($logical_card_id))
    {
      $self->unlock_rollback() if $lock_local;

      $rhash->{'Reason'} = "Internal Error";
      return undef;
    }
  }
  else  # if it is, remove it from the card order queue
  {
    my %thash;
    if (!$self->RemovePendingQueueByCardId( \%thash, { 'CardId' => $logical_card_id } ))
    {
      $self->unlock_rollback() if $lock_local;
      $rhash->{'Reason'} = $thash{'Reason'};
      return undef;
    }
  }


  $self->deactivate_user_card($logical_card_id);

  $self->unlock_commit() if $lock_local;

  return 1;

}

###

sub GetPendingQueue {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;
  my $FUNC_NAME = "GetPendingQueue";
  my ($query, $result, $row);

  $query = $self->prepare("select org_card_order_queue_id,
                                  userid,
                                  logical_card_id,
                                  created,
                                  processed,
                                  comment,
                                  pending
                             from org_card_order_queue
                            where pending = 1
                         order by org_card_order_queue_id");
  $query->execute();

  my $ind;
  my $n=0;
  while (my $row = $query->fetchrow_hashref)
  {
    $ind = "[$n]" if $n>0;
    $n++;

    $rhash->{'QueueId' . $ind} = $row->{org_card_order_queue_id};
    $rhash->{'UserId' . $ind}  = $row->{userid};
    $rhash->{'CardId' . $ind}  = $row->{logical_card_id};
    $rhash->{'Created' . $ind} = $row->{created};

    my %trhash;
    my %tphash = ( CardId => $row->{logical_card_id} );

    my $r =
      $self->GetCard(\%trhash, \%tphash);

    $rhash->{'IssueType' . $ind} = $trhash{'IssueType'};

  }

  $rhash->{NCard} = $n;

  return 1;


}

###

sub GetPendingQueueCard {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;
  my $FUNC_NAME = "GetPendingQueueCard";

  my $lock_local = ($self->lock_active() ? 0 : 1);

  my $queue_id = $phash->{QueueId};
  if (!$queue_id)
  {
    $rhash->{Reason} = "Invalid QueueId";
    return undef ;
  }

#  $self->begin_locked_transaction_common() if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = 
    $self->prepare("select logical_card_id
                      from org_card_order_queue
                     where org_card_order_queue_id = ?
                       and pending = 1");
  $query->execute($queue_id);
  my $row = $query->fetchrow_arrayref;

  if (!$row)
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{Reason} = "No cards found";
    return undef;
  }

  my $logical_card_id = $row->[0];

  return $self->GetCard($rhash, { CardId => $logical_card_id } );

}


###
sub ProcessPendingQueue {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;
  my $FUNC_NAME = "ProcessPendingQueue";
  my ($query, $row);

  my $lock_local = ($self->lock_active() ? 0 : 1);

  my $queue_id = $phash->{QueueId};

  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  $query = $self->prepare("select logical_card_id, userid
                              from org_card_order_queue
                             where org_card_order_queue_id = ?
                               and pending = 1");
  $query->execute($queue_id);
  $row = $query->fetchrow_arrayref;

  if (!$row)
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{Reason} = "No Pending Card";
    return undef;
  }

  $phash->{UserId} = $row->[1];
  $phash->{CardId} = $row->[0];

  my %resp;
  my $r =
    $self->TransferCard(\%resp, $phash);
  if (!$r)
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{Reason} = $resp{Reason};
    return undef;
  }

  my $card_id = $resp{CardId};
  $rhash->{CardId} = $resp{CardId};


  %resp = ();
  $r = 
    $self->RemoveCard(\%resp, { CardId => $phash->{CardId} } );
  if (!$r)
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{Reason} = $resp{Reason};
    return undef;
  }

  $query = $self->prepare("update org_card_order_queue
                               set pending = 0
                             where org_card_order_queue_id = ?");
  $query->execute($queue_id);

  $self->unlock_commit() if $lock_local;

  return 1;

}


###
sub RemovePendingQueue {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;
  my $FUNC_NAME = "RemovePendingQueue";
  my ($query, $row, $queue_id);

  $queue_id = $phash->{QueueId};

  my $lock_local = ($self->lock_active() ? 0 : 1);
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;


  $query = $self->prepare("select logical_card_id
                             from org_card_order_queue
                            where org_card_order_queue_id = ?
                              and pending = 1");
  $query->execute($queue_id);
  $row = $query->fetchrow_arrayref;
  $queue_id = $phash->{QueueId};

  if (!$queue_id || !$row)
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{Reason} = "No entry in card order queue";
    return undef;
  }


  my %t;
  $self->RemoveCard(\%t, { CardId => $row->[0] } );

  $query = $self->prepare("update org_card_order_queue
                              set pending = 0
                            where org_card_order_queue_id = ?");
  $query->execute($queue_id);

  $self->unlock_commit() if $lock_local;

  return (1, "RemovePendingQueue");


}


####
#
# remove only the org_card_order_queue entry by the card id
#
sub RemovePendingQueueByCardId {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;
  my $FUNC_NAME = "RemovePendingQueueByCardId";
  my ($query, $row, $card_id, $queue_id);

  $card_id = $phash->{CardId};

  my $lock_local = ($self->lock_active() ? 0 : 1);
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  $query = $self->prepare("select org_card_order_queue_id, logical_card_id
                             from org_card_order_queue
                            where pending = 1
                              and logical_card_id = ?");
  $query->execute($card_id);
  $row = $query->fetchrow_arrayref;

  if (!$row)
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{Reason} = "No entry in card order queue";
    return undef;
  }

  $queue_id = $row->[0];

  $query = $self->prepare("update org_card_order_queue set pending = 0 where org_card_order_queue_id = ?");
  $query->execute( $queue_id );

  $self->unlock_commit() if $lock_local;

  return (1, "RemovePendigQueueByCardId");

}



############################


#sub active_user_card
sub user_card_credential_exists
{
  my $self        = shift;
  my $param_hash  = shift;

  my $FUNC_NAME = 'user_card_credential_exists';

  my @field = qw( logical_card_id mag_token rfid_token );
  my $query;

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'user_card' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  foreach my $field (@field)
  {
    if ($param_hash->{$field})
    {

      $query =
        $self->prepare("select count(logical_card_id) num " .
                       "  from user_card " .
                       " where " . $field . " = ? " .
                       "   and active = 1 " .
                       " limit 1 ");
      $query->execute($param_hash->{$field});
#      return 1 if $query->fetchrow_arrayref->[0] > 0;
      if ($query->fetchrow_arrayref->[0] > 0) 
      {
        $self->unlock_commit() if $lock_local;
        return 1;
      }
    }

  }

  $self->unlock_commit() if $lock_local;

  return 0;
  
}

############################
#
# insert user card helper function
# active defaults to 1
# issued defaults to now()
#
sub insert_user_card
{
  my $self = shift;
  my $param_hash  = shift;
  my $FUNC_NAME = "insert_user_card";
  
  my @field;
  my @val;
  my $query;

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'audit_user_card' => 'w', 'user_card' => 'w' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;


 
  if ( $self->user_card_credential_exists( $param_hash ) )
  {

   
    $self->unlock_rollback() if $lock_local;
    return undef 
  }

  my @default_field = ('issued', 'active');
  my @default_value = ('now()', '1');
  my @default_token;

  # construct default values
  for (my $i=0; $i<scalar(@default_field); $i++) 
  {
    my $f = $default_field[$i];
    push @field, $f;
    if (defined($param_hash->{$f}))
    {
      push @default_token, "?";
      push @val, $param_hash->{$f};
    }
    else
    {
      push @default_token, $default_value[$i];
    }
  }

  # populate values
  foreach my $k (keys(%user_card_field_map)) 
  {
    next if grep $_ eq $k, @default_field;
    push @field, $k;
    push @val, (defined($param_hash->{$k}) ? $param_hash->{$k} : undef );
  }

  my $audit_id = 
    $self->audit_user_card_start(undef, "$PACKAGE_NAME: $FUNC_NAME");
  $query = 
    $self->prepare("insert into user_card ( " . join(', ', @field) . ") " .
                                  "values ( " . join(', ', @default_token) . ", ?"x(scalar(@field)-scalar(@default_token)) . ")");
  $query->execute(@val);

  my $logical_card_id = $self->last_insert_id();
  $self->audit_user_card_end($logical_card_id, $audit_id);

  $self->unlock_commit() if $lock_local;

  return $logical_card_id

}


############################
##########

###
#
# returns 0 if logical_card_id is not found or last entry is deleted
# returns 1 if logical_card_id found
#
sub validate_active_rider_table_logical_card_id
{
  my $self            = shift;
  my $logical_card_id = shift;
  my $FUNC_NAME = "validate_active_rider_table_logical_card_id";

  return 0 if !$logical_card_id;

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'active_rider_table' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;


  my $query =
    $self->prepare("select max(seq_num) " .
                    " from active_rider_table " .
                   " where logical_card_id = ? ");
  $query->execute($logical_card_id);
  my $seq_num = $query->fetchrow_arrayref->[0];

  $query =
    $self->prepare("select logical_card_id, ".
                   "       seq_num, " . 
                   "       mag_token, " .
                   "       rfid_token, " .
                   "       deleted " .
                   "  from active_rider_table " .
                   " where seq_num = ? ");
  $query->execute($seq_num);
  my $aref = $query->fetchrow_arrayref;

  $self->unlock_commit() if $lock_local;

  return 0 if !$aref;
  return 0 if $aref->[4];

  return 1;

}

##########

sub validate_rule
{
  my $self = shift;
  my $rule = shift;
  my $rule_param = shift;
  my $group_id = shift;

  my $FUNC_NAME = 'validate_rule';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'rule_mappings' => 'r', 'rule_class' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query =
    $self->prepare("select rulename, " .
                   "       ruleclass " .
                   "  from rule_class " .
                   " where rulename = ? " .
                   " limit 1 ");
  $query->execute($rule);
  my $aref = $query->fetchrow_arrayref;
  if (!$aref)
  {
    $self->unlock_rollback() if $lock_local;
    return 0;
  }

  my $db_rule_name  = $aref->[0];
  my $db_rule_class = $aref->[1];
  
  if ( lc($db_rule_class) eq 'nride')
  {
    if (!$rule_param or !($rule_param =~ m/^\d+\s*$/))
    {
      $self->unlock_rollback() if $lock_local;
      return 0;
    }
  }
  elsif (lc($db_rule_class) eq 'nday')
  {
    if (!$rule_param or 
        ( !($rule_param =~ m/^\d+\s*$/) && 
          !($rule_param =~ m/^\d+ \d\d\d\d[-\/]\d?\d[-\/]\d?\d \d\d:\d\d:\d\d\s*$/) ) )
    {
      $self->unlock_rollback() if $lock_local;
      return 0;
    }
  }

  if ($group_id)
  {
    $query =
      $self->prepare("select count(group_id) num " .
                     "  from rule_mappings " .
                     " where rule = ? " .
                     "   and active = 1 " .
                     "   and (group_id = ? or group_id is null) " .
                     " limit 1 ");
    $query->execute($db_rule_name, $group_id);
    if ($query->fetchrow_arrayref->[0] != 1)
    {
      $self->unlock_rollback() if $lock_local;
      return 0;
    }
  }

  $self->unlock_commit() if $lock_local;

  return 1;

}

##########

sub get_default_rule {
  my $self = shift;

  my $FUNC_NAME = 'get_default_rule';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'rule_mappings' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = 
    $self->prepare("select rule " .
                    " from rule_mappings " .
                   " where group_id is null " .
                   " limit 1");
  $query->execute();

  $self->unlock_commit() if $lock_local;

  return $query->fetchrow_arrayref->[0];
}

###

sub get_rule_class {
  my $self = shift;
  my $rule = shift;

  my $FUNC_NAME = 'get_rule_class';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'rule_class' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query =
    $self->prepare("select ruleclass " .
                    " from rule_class " .
                   " where rulename = ? ");
  $query->execute($rule);
  my $row = $query->fetchrow_arrayref;

  $self->unlock_commit() if $lock_local;

  return undef if !$row;
  return $row->[0];
}

###


sub insert_active_rider_table
{
  my $self = shift;
  my $param_hash = shift;
  my $FUNC_NAME = "insert_active_rider_table";

  return undef if !defined($param_hash->{'logical_card_id'}) or !$param_hash->{'logical_card_id'};

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->lock_common() if $lock_local;
#  $self->begin_locked_transaction_common() if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $default_rule =
    $self->get_default_rule();

  my $query = 
    $self->prepare(" select user_card.mag_token, " .
                          " user_card.rfid_token, " .
                          " user_card.group_id, " .
                          " case when user_pass.rule is null then '" . $default_rule . "' " .
                                                           " else user_pass.rule end, " .
                          " user_pass.nrides_remain, " .
                          " user_pass.nday_orig, " .
                          " user_pass.nday_expiration, " .
                          " user_pass.comment " .
                     " from user_card left join user_pass " .
                       " on (user_card.logical_card_id = user_pass.logical_card_id and" .
                           " user_pass.active = 1) " .
                    " where user_card.logical_card_id = ? " .
                      " and user_card.active = 1 ");
  $query->execute( $param_hash->{'logical_card_id'} );
  my $aref = $query->fetchrow_arrayref;

#  return undef if !$aref;
  if (!$aref) 
  {
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  my @field = qw( 
    logical_card_id 
    rfid_token 
    mag_token 
    rule_name 
    rule_param 
    deleted 
  );

  my $rule      = $aref->[3];
  my $nride     = $aref->[4];
  my $nday      = $aref->[5];
  my $nday_exp  = $aref->[6];
  my $art_param = $aref->[7];

  my $rule_type = 
    $self->get_rule_class($rule);

  if (lc($rule_type) eq 'nride')
  {
    $art_param = $nride;
  }
  elsif (lc($rule_type) eq 'nday')
  {
    $art_param = $nday;
    if ($nday_exp)
    {
      $art_param .= " $nday_exp";
    }
  }

  if ( !$self->validate_rule( $rule, $art_param ) )
  {
    $self->unlock_rollback() if $lock_local;
    die "$PACKAGE_NAME: $FUNC_NAME: invalid logical_card_id, rule or rule_param ($rule, $nride, $nday, $nday_exp)";
  }

  $query = 
    $self->prepare("insert into active_rider_table ( " . join(', ', @field) . ") " .
                                          " values ( ?" . ", ?"x(scalar(@field)-1) . ") ");
  $query->execute($param_hash->{'logical_card_id'},
                  $aref->[1],
                  $aref->[0],
                  $rule,
                  $art_param,
                  0);

  $self->unlock_commit() if $lock_local;

  return 1;
                  
}

###

sub active_user_card_exists {
  my $self = shift;
  my $phash = shift;

  my $FUNC_NAME = 'active_user_card_exists';

  my @fields = qw(MagToken MagStripe RFIDToken RFID CardId);

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'org_default_card_value' => 'r', 'user_card' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;


  foreach my $f (@fields)
  {
    next if $f =~ /^Group(Id|Name)$/;
    next if !$phash->{$f};

    my $card_id;
    if ($phash->{GroupId}) 
    {
      $card_id =
        $self->GetLogicalCardId( { $f => $phash->{$f}, GroupId => $phash->{GroupId} } );
    }
    else
    {
      $card_id =
        $self->GetLogicalCardId( { $f => $phash->{$f} } );
    }

    if ($card_id)
    {
      $self->unlock_commit() if $lock_local;
      return 1;
    }

  }

  $self->unlock_commit() if $lock_local;
  return undef;

}

######
#
# create a credential in database
# 1) lock relevant tables (active_rider_table, user_card, user_pass, audit_user_card, audit_user_pass)
# 2) insert into user_card
# 3) insert into active_rider_table
# 4) unlock tables
#
######
#sub create_card 
sub CreateCard
{
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my $FUNC_NAME = "CreateCard";

  my $lock_local = ($self->lock_active() ? 0 : 1);
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  $phash->{GroupId} = $self->get_group_id( $phash->{GroupName} || $DEFAULT_GROUP_NAME )
    if (!$phash->{GroupId});

  my %insert_param;
  while ( my ($k, $v) = each(%user_card_field_map) )
  {
    $insert_param{$k} = $phash->{$v};
  }

  $insert_param{'active'} = 1 if !defined($phash->{'Active'});  # default to active card
  $insert_param{'mag_token'}  = $self->construct_mag_token( $phash );
  $insert_param{'rfid_token'} = $self->construct_rfid_token( $phash );


  if (!$insert_param{'group_id'})
  {
    my %rhash;
    $self->GetDefaultCardGroupInfo(\%rhash, $phash);
    $insert_param{'group_id'} = $rhash{'GroupId'};
  }

  if ($self->active_user_card_exists( $phash ))
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{'Reason'} = "Active card already exists";
    return undef;
  }


  $rhash->{'CardId'} =
    $self->insert_user_card( \%insert_param );

  if (!$rhash->{'CardId'}) {
    $rhash->{'Reason'} = "Insert Failed!";
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  $self->insert_active_rider_table( { logical_card_id => $rhash->{'CardId'} } );

  $self->unlock_commit() if $lock_local;

  return $rhash->{'CardId'};

}


################################
##
##
## user_pass functions
##
##
################################

my %user_pass_field_map = 
 ( user_pass_id     => "PassId",
   logical_card_id  => "CardId",
   issued           => "Issued",
   firstused        => "FirstUsed",
   lastused         => "LastUsed",
   activated        => "Activated",
   deactivated      => "Deactivated",
   rule             => "Rule",
   nrides_orig      => "NRideOrig",
   nrides_remain    => "NRideRemain",
   nday_orig        => "NDayOrig",
   nday_expiration  => "NDayExpiration",
   queue_order      => "QueueOrder",
   comment          => "Comment", 
   active           => "Active", 
   expired          => "Expired",
   paytype          => "PaymentType",
   comment          => "Comment"
 );

#
# insert user pass helper function
# issued defaults to now()
# if active is set, activated defaults to now
#
sub insert_user_pass
{
  my $self = shift;
  my $param_hash = shift;
  my $FUNC_NAME = "insert_user_pass";

  my @field;
  my @val;
  my $query;

  my @default_field = ('issued', 'activated');
  my @default_value = ('now()' , 'now()');
  my @default_token;

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'audit_user_pass' => 'w', 'user_pass' => 'w' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  # set defaults
  for (my $i=0; $i<scalar(@default_field); $i++) 
  {
    my $f = $default_field[$i];
    push @field, $f;
    if (defined($param_hash->{$f}))
    {
      push @default_token, "?";
      push @val, $param_hash->{$f};
    }
    else
    {
      push @default_token, $default_value[$i];
    }
  }

  # set value array
  foreach my $k (keys(%user_pass_field_map)) 
  {
    next if grep $_ eq $k, @default_field;
    push @field, $k;
    push @val, (defined($param_hash->{$k}) ? $param_hash->{$k} : undef );
  }

  my $audit_id = 
    $self->audit_user_pass_start(undef, "$PACKAGE_NAME: $FUNC_NAME");
  $query = 
    $self->prepare("insert into user_pass ( " . join(', ', @field) . ") " .
                                  "values ( " . join(', ', @default_token) . ", ?"x(scalar(@field)-scalar(@default_token)) . ")");
  $query->execute(@val);

  my $pass_id = 
    $self->last_insert_id();
  $self->audit_user_pass_end($pass_id, $audit_id);

  $self->unlock_commit() if $lock_local;

  return $pass_id;

}

###

sub get_user_pass_logical_card_id {
  my $self = shift;
  my $pass_id = shift;

  my $FUNC_NAME = 'get_user_pass_logical_card_id';

  return undef if !$pass_id;

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'user_pass' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query =
    $self->prepare("select logical_card_id " .
                    " from user_pass " .
                   " where user_pass_id = ? ");
  $query->execute($pass_id);
  my $row = $query->fetchrow_arrayref;

  $self->unlock_commit() if $lock_local;

  return undef if !$row;

  return $row->[0];


}

#sub get_user_pass {
sub GetUserPass
{
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my $FUNC_NAME = 'GetUserPass';

  my ($query, $result);

  if ( !$phash->{'CardId'} and !$phash->{'PassId'} )
  {
    $rhash->{'Reason'} = "Invalid Parameters";
    return undef;
  }

  my $fields = "rule_class.ruleclass Type";
  while ( my ($k, $v) = each(%user_pass_field_map) )
  {
    $fields .= ", user_pass.$k $v";
  }

  my @param;
  push @param, $phash->{'CardId'} if $phash->{'CardId'};
  push @param, $phash->{'UserId'} if $phash->{'UserId'};
  push @param, $phash->{'PassId'} if $phash->{'PassId'};
  push @param, $phash->{'Active'} if $phash->{'Active'};

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'user_card' => 'r', 'user_pass' => 'r', 'rule_class' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;


  $query = 
    $self->prepare("select $fields " .
                   " from user_pass, " .
                   "      user_card, " .
                   "      rule_class " .
                   "where user_card.logical_card_id = user_pass.logical_card_id ".
                    " and user_card.active = 1 " .
                    " and user_pass.expired = 0 " .
                    ( defined($phash->{'CardId'}) ? " and user_card.logical_card_id = ? " : "" ) .
                    ( defined($phash->{'UserId'}) ? " and user_card.userid = ? " : "" ) .
                    ( defined($phash->{'PassId'}) ? " and user_pass.user_pass_id = ? " : "") .
                    ( defined($phash->{'Active'}) ? " and user_pass.active = ? " : "" ) .
                    " and user_pass.rule = rule_class.rulename ");
  $query->execute(@param);
  my $row = $query->fetchrow_hashref;

  $self->unlock_commit() if $lock_local;

  if (!$row) 
  {
    $rhash->{'Reason'} = "No Results";
    return undef;
  }

  while ( my ($k, $v) = each(%$row) )
  {
    $rhash->{$k} = $v;
  }

  return 1;

}


###
#
#sub get_user_pass_old {
#  my $self = shift;
#  my $rhash = shift;
#  my $phash = shift;
#
#  my ($query, $result);
#
#  if (!$phash->{'PassId'})
#  {
#    $rhash->{'Reason'} = "Invalid PassId";
#    return undef;
#  }
#
#  my $fields = "rule_class.ruleclass Type";
#  while ( my ($k, $v) = each(%user_pass_field_map) )
#  {
#    $fields .= ", user_pass.$k $v";
#  }
#
#  my @param;
#  push @param, $phash->{'PassId'};
#  push @param, $phash->{'UserId'} if $phash->{'UserId'};
#
#  $query = 
#    $self->prepare("select $fields " .
#                   " from user_pass, " .
#                   "      user_card, " .
#                   "      rule_class " .
#                   "where user_pass.user_pass_id = ? ".
#                    " and user_card.logical_card_id = user_pass.logical_card_id ".
#                    " and user_card.active = 1 " .
#                    ( defined($phash->{'UserId'}) ? " and user_card.userid = ? " : "" ) .
#                    " and user_pass.rule = rule_class.rulename ");
#  $query->execute(@param);
#  my $row = $query->fetchrow_hashref;
#  if (!$row) 
#  {
#    $rhash->{'Reason'} = "Invalid UserId or PassId";
#    return undef;
#  }
#
#  while ( my ($k, $v) = each(%$row) )
#  {
#    $rhash->{$k} = $v;
#  }
#
#  return 1;
#
#}
#
###

#sub get_passes_on_card {
sub GetPassesOnCard
{
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my $FUNC_NAME = 'GetPassesOnCard';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'org_default_card_value' => 'r', 'user_card' => 'r', 'user_pass' => 'r', 'rule_class' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;


  my $logical_card_id = 
    $self->GetLogicalCardId($phash);

  my $fields = " case when rule_class.rulename is null then 'OTHER' " .
               "      else rule_class.ruleclass end Type ";
  while ( my ($k, $v) = each(%user_pass_field_map) )
  {
    $fields .= ", user_pass.$k $v";
  }

  my $query = 
    $self->prepare(" select $fields " .
                   "   from user_pass left join rule_class " .
                   "     on rule_class.rulename = user_pass.rule " .
                   "  where user_pass.logical_card_id = ? " .
                   "    and user_pass.expired = 0 " .
                   "  order by user_pass.queue_order asc ");
  my $result = $query->execute($logical_card_id);
  
  my $ind = '';
  my $pos = 0;
  while (my $row = $query->fetchrow_hashref) {
    last if ($phash->{'MaxPass'} && ($pos >= $phash->{'MaxPass'}));
    while ( my ($k, $v) = each(%$row) )
    {
      $rhash->{$k . $ind} = $v;
    }
    $ind = "[" . (++$pos) . "]";
  }
  $rhash->{'NPass'} = '0';
  $rhash->{'NPass'} = $pos if $pos;

  $self->unlock_commit() if $lock_local;

  return 1;

}

###

sub user_card_has_active_pass {
  my $self = shift;
  my $logical_card_id = shift;

  my $FUNC_NAME = 'user_card_has_active_pass';


  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'user_pass' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;


  my $query =
    $self->prepare("select count(user_pass_id) num " .
                    " from user_pass " .
                   " where logical_card_id = ?  " .
                     " and active = 1 " .
                     " and expired = 0 " );
  $query->execute($logical_card_id);

  $self->unlock_commit() if $lock_local;

  return ($query->fetchrow_arrayref->[0]==1);
}

###

sub activate_user_card_pass {
  my $self = shift;
  my $card_id = shift;

  my $FUNC_NAME = 'activate_user_card_pass';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'user_pass' => 'w' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;


  my $query =
    $self->prepare("select min(queue_order) " .
                    " from user_pass " .
                   " where logical_card_id = ? " .
                     " and expired = 0 ");
  $query->execute($card_id);
  my $q = $query->fetchrow_arrayref->[0];
  if (!defined($q)) 
  {
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  $query =
    $self->prepare("select user_pass_id " .
                    " from user_pass " .
                   " where logical_card_id = ? " .
                     " and queue_order = ? ");
  $query->execute($card_id, $q);
  my $user_pass_id = $query->fetchrow_arrayref->[0];

  $query =
    $self->prepare("update user_pass " .
                     " set active = 1, " .
                         " activated = now() " .
                   " where user_pass_id = ? ");
  $query->execute($user_pass_id);

  $self->unlock_commit() if $lock_local;

  return 1;

}


####

sub user_card_pass_count {
  my $self = shift;
  my $card_id = shift;

  my $FUNC_NAME = 'user_card_pass_count';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'user_pass' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query =
    $self->prepare("select count(user_pass_id) num " .
                    " from user_pass " .
                    "where logical_card_id = ? " .
                    "  and expired = 0 ");
  $query->execute($card_id);

  $self->unlock_commit() if $lock_local;

  return $query->fetchrow_arrayref->[0];
}


###

sub get_next_queue_order {
  my $self = shift;
  my $logical_card_id = shift;

  my $FUNC_NAME = 'get_next_queue_order';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'user_pass' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = 
    $self->prepare("select max(queue_order) num " .
                   "  from user_pass " .
                   " where logical_card_id = ? ");
  $query->execute($logical_card_id);
  my $q = $query->fetchrow_arrayref->[0];

  $self->unlock_commit() if $lock_local;

  return ( $q ? ($q+1) : 1 );
}


###

sub RemovePass {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my $FUNC_NAME = 'RemovePass';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction_common() if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my %pass;
  my $r = $self->GetUserPass(\%pass, $phash);
  if ( (!$pass{PassId}) || (!$r) ) 
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{Reason} = ($pass{Reason} || "Invalid PassId");
    return undef;
  }

  my $pass_id = $pass{PassId};
  my $card_id = $pass{CardId};

  my $ret = 
    $self->update_user_pass($pass_id, 
      { 
        active      => 0, 
        expired     => 1, 
        deactivated => unix_to_readable_time()  
      } 
    );


  if ($pass{Active}) 
  {
    $self->activate_user_card_pass($card_id);
    $self->insert_active_rider_table( { logical_card_id => $card_id } );
  }


  $self->unlock_commit() if $lock_local;
  return 1;

}

##

sub deactivate_user_pass {
  my $self = shift;
  my $pass_id = shift;
  my $FUNC_NAME = "deactivate_user_pass";

  return undef if !$pass_id;

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'audit_user_pass' => 'w', 'user_pass' => 'w', 'user_card' => 'r', 'rule_class' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my %pass;
#  $self->get_user_pass(\%pass, { PassId => $pass_id });
  $self->GetUserPass(\%pass, { PassId => $pass_id });

#  return undef if (!$pass{'PassId'} or !$pass{'Active'});
  if ((!$pass{'PassId'} or !$pass{'Active'}))
  {
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

#  return $self->update_user_pass($pass_id, { active => 0, expired => 1, deactivated => unix_to_readable_time()  } );
  my $ret = 
    $self->update_user_pass($pass_id, 
      { 
        active      => 0, 
        expired     => 1, 
        deactivated => unix_to_readable_time()  
      } 
    );

  $self->unlock_commit() if $lock_local;
#  return $ret;
  return 1;

}

###

sub update_user_pass {
  my $self = shift;
  my $pass_id = shift;
  my $fields = shift;
  my $FUNC_NAME = "update_user_pass";

  return undef if !$pass_id;

  my $str;
  my @val;

  while ( my ($k, $v) = each(%$fields) ) 
  {
    next if (!$user_pass_field_map{$k});
    $str .= " , " if $str;
    $str .= " $k = ? ";
    push @val, $v;
  }
  return undef if (scalar(@val)==0);

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'audit_user_pass' => 'w', 'user_pass' => 'w' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $audit_id = 
    $self->audit_user_pass_start($pass_id, "$PACKAGE_NAME: $FUNC_NAME");
  my $query = 
    $self->prepare("update user_pass set $str where user_pass_id = ? ");
  $query->execute(@val, $pass_id);
  $self->audit_user_pass_end($pass_id, $audit_id);

  $self->unlock_commit() if $lock_local;

  return 1;

}

####

#sub transfer_pass {
sub TransferPass
{
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my $FUNC_NAME = 'TransferPass';

  my $pass_id = $phash->{'PassId'};

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->lock_common() if $lock_local;
#  $self->begin_locked_transaction_common() if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $from_card_id = 
    $self->get_user_pass_logical_card_id(  $pass_id );


  my $to_card_id =
    $self->GetDestLogicalCardId( $phash );
#    $self->get_dest_logical_card_id( $phash );

  if (!$from_card_id || !$to_card_id)
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{'Reason'} = "Invalid Card (from id $from_card_id, to id $to_card_id)";
    return undef;
  }

  my $queue_order = 
    $self->get_next_queue_order($to_card_id);

  # move pass to new card
  $self->update_user_pass($pass_id, { logical_card_id => $to_card_id,
                                      queue_order     => $queue_order,
                                      active          => 0 } );

  # if we removed an active pass, activate the current pass and update the art
  if ( !$self->user_card_has_active_pass($from_card_id) )
  {
    if ($self->user_card_pass_count($from_card_id) > 0)
    {
      $self->activate_user_card_pass($from_card_id);
    }

    if (!$self->pending_card($from_card_id))
    {
      $self->insert_active_rider_table( { logical_card_id => $from_card_id } );
    }
  }

  # if we added an active pass, activate the current pass and update the art
  if ( !$self->user_card_has_active_pass($to_card_id) )
  {
    if ($self->user_card_pass_count($to_card_id) > 0)
    {
      $self->activate_user_card_pass($to_card_id);
    }

    if (!$self->pending_card($to_card_id))
    {
      $self->insert_active_rider_table( { logical_card_id => $to_card_id } );
    }
  }

  $self->unlock_commit() if $lock_local;

  $rhash->{'PassId'} = $pass_id;

  return 1;

}

###

sub get_pass_type_and_name {
  my $self = shift;
  my $group_id = shift;
  my $type = shift;

  my $FUNC_NAME = 'get_pass_type_and_name';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'rule_class' => 'r', 'rule_mappings' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;


  my $query =
    $self->prepare("select rule_class.ruleclass, " .
                         " rule_class.rulename " .
                    " from rule_mappings, rule_class " .
                   " where rule_mappings.group_id = ? " .
                     " and rule_mappings.rule like ? " .
                     " and rule_class.rulename = rule_mappings.rule " .
                     " and rule_mappings.active = 1 ");
  $query->execute($group_id, '%-' . $type);
  my $row = $query->fetchrow_arrayref;

  $self->unlock_commit() if $lock_local;

  return undef if !$row;
  return ($row->[0], $row->[1]);
}

###

sub valid_pass_param {
  my $self = shift;
  my $pass_type = shift;
  my $phash= shift;

  my $FUNC_NAME = 'valid_pass_param';

  return 0
  if ( (lc($pass_type) eq 'nride') &&
       (!$phash->{'NRideOrig'} || !$phash->{'NRideRemain'}) );
#       !$phash->{'NRideOrig'} && !$phash->{'NRideRemain'} );

  return 0
  if ( (lc($pass_type) eq 'nride') &&
       ($phash->{'NRideOrig'} < $phash->{'NRideRemain'}) );

  return 0
  if ( (lc($pass_type) eq 'nday') &&
       !$phash->{'NDayOrig'} );

  return 0
  if ( (lc($pass_type) eq 'nday') &&
       $phash->{'NDayExpiration'} &&
       (!($phash->{'NDayExpiration'} =~ m/^\d\d\d\d[-\/]\d\d?[-\/]\d\d?  *\d\d?:\d\d?:\d\d?$/)) );

  return 1;

}



###

#sub add_pass {
sub AddPass
{
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my $FUNC_NAME = 'AddPass';

  my %blacklisted_fields = 
  ( PassId => 1, 
    Active => 1, 
    Expired => 1 );

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->lock_common() if $lock_local;
#  $self->begin_locked_transaction_common() if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

#  my $logical_card_id = $self->GetLogicalCardId($phash);
  my $logical_card_id = $self->GetLogicalCardId( 
  {
    CardId => $phash->{CardId},
    MagTrack => $phash->{MagTrack},
    MagStripe => $phash->{MagStripe},
    MagToken=> $phash->{MagToken},
    RFLength => $phash->{RFLength},
    RFSite => $phash->{RFSite},
    RFID => $phash->{RFID},
    RFIDToken => $phash->{RFIDToken},
    GroupId => $phash->{CardGroupId},
    GroupName => $phash->{CardGroupName}
  } );

  #my $group_id        = $self->get_user_card_group_id($logical_card_id);
  my $group_id;
  if ($phash->{GroupId})
  {
    $group_id = $phash->{GroupId};
  }
  else 
  {
    $group_id = $self->get_group_id( $phash->{GroupName} || $DEFAULT_GROUP_NAME );
  }


  if (!$logical_card_id || !$group_id)
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{'Reason'} = "Invalid Card ($logical_card_id, $group_id)";
    return undef;
  }

  my ($pass_type, $rule_name) =
    $self->get_pass_type_and_name($group_id, $phash->{'Type'});

  if (!$pass_type || !$rule_name) 
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{'Reason'} = "Invalid rule";
    return undef;
  }

  if ( !$self->valid_pass_param($pass_type, $phash) )
  {
    $self->unlock_rollback() if $lock_local;
    $rhash->{'Reason'} = "Invalid rule/parameter combination for $pass_type";

### DEBUG
    if (lc($pass_type) eq 'nday') {
      $rhash->{'Reason'} .= "(NDayOrig " . $phash->{NDayOrig} . ", NDay " . $phash->{NDay} . ", NDayExpiration " . $phash->{NDayExpiration} . ")";
    } elsif (lc($pass_type) eq 'nride') {
      $rhash->{'Reason'} .= "(NRideOrig " . $phash->{NRideOrig} . ", NRideRemain" . $phash->{NRideRemain} . ")";
    }
### DEBUG

    return undef;
  }

  my %insert_param;
  while ( my ($k, $v) = each(%user_pass_field_map) )
  {
    next if !$phash->{$v} || $blacklisted_fields{$v};
    $insert_param{$k} = $phash->{$v};
  }

  $insert_param{'rule'} = $rule_name;
  $insert_param{'logical_card_id'} = $logical_card_id;
  $insert_param{'active'} = 0;
  $insert_param{'expired'} = 0;
  $insert_param{'queue_order'} = 
    $self->get_next_queue_order($logical_card_id);

  my $pass_id = 
    $self->insert_user_pass( \%insert_param );

  if (!$self->pending_card($logical_card_id) && 
      !$self->user_card_has_active_pass($logical_card_id))
  {
    $self->activate_user_card_pass($logical_card_id);
    $self->insert_active_rider_table( { logical_card_id => $logical_card_id } );
  }

  $self->unlock_commit() if $lock_local;

  $rhash->{'PassId'} = $pass_id;

  return 1;

}


###
my %pass_option_map = 
( id => "PassOptionId",
  group_id => "GroupId", 
  param => "Param",
  name => "Name",
  rule => "Rule",
  type => "Type",
  description => "Description",
  num_opt => "NumPassOption",
  option0 => "Option0",
  option1 => "Option1",
  option2 => "Option2",
  option3 => "Option3"
  );

my %default_pass_value_map =
( start => "Start",
  end => "Expiration",
  nday => "DefaultNDay",
  nride => "DefaultNRide"
  );

sub GetPassOptions
{
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my $FUNC_NAME = 'GetPassOptions';

  my ($query, $row);
  my $group_inclusion;
  my @group_name;
  my @gruop_id;
  my $n;

  $n = $phash->{NGroup};
  if (!$n)
  {
    $rhash->{Reason} = "Invalid NGroup";
    return undef;
  }

  $group_inclusion = "(false ";
  for (my $i=0; $i<$n; $i++)
  {
    my $key = "GroupName";
    $key = "${key}[$i]" if $i;

    push @group_name, $phash->{$key};
    $group_inclusion .= " or groups.group_name = ? ";
  }
  $group_inclusion .= ")";

  my $lock_local = ($self->lock_active() ? 0 : 1);
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  $query =
    $self->prepare("select group_id from groups where $group_inclusion");
  $query->execute(@group_name);

  $group_inclusion = "(false ";
  my @group_id;
  while ($row = $query->fetchrow_arrayref) 
  {
    push @group_id, $row->[0];
    $group_inclusion .= " or pass_option.group_id = ? ";
  }
  $group_inclusion .= ")";

  $query =
    $self->prepare("select pass_option." . join(", pass_option.", keys(%pass_option_map) ) . " , " .
                         " org_default_pass_value." . join(", org_default_pass_value.", keys(%default_pass_value_map)) .  "," .
                         " groups.group_name GroupName " .
                    " from groups, pass_option left join org_default_pass_value " .
                      " on (     pass_option.rule = org_default_pass_value.name " .
                     "       and pass_option.group_id = org_default_pass_value.group_id ) " .
                   " where $group_inclusion " .
                     " and pass_option.active = 1 " .
                     " and groups.group_id = pass_option.group_id " .
                   " order by pass_option.id ");
  $query->execute(@group_id);

  my $ind = '';
  my $pos = 0;
  while ($row = $query->fetchrow_hashref) 
  {
    while (my ($key, $val) = each(%pass_option_map))
    {
      $rhash->{$val . $ind} = $row->{$key};
    }
    while (my ($key, $val) = each(%default_pass_value_map))
    {
      $rhash->{$val . $ind} = $row->{$key};
    }

    $rhash->{"GroupName$ind"} = $row->{GroupName};

    $ind = "[" . (++$pos) . "]";
  }

  $rhash->{'NPassOption'} = $pos;

  $self->unlock_commit() if $lock_local;

  return 1;

}


####

my %price_point_map = 
( id => "PricePointId",
  price => "Price",
  param => "Param",
  name => "Name",
  rule => "Rule",
  type => "Type",
  description => "Description",
  num_opt => "NumPriceOption",
  price_option0 => "PriceOption0",
  price_option1 => "PriceOption1",
  price_option2 => "PriceOption2",
  price_option3 => "PriceOption3"
  );

  
####

#sub get_price_points {
sub GetPricePoints
{
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my $FUNC_NAME = 'GetPricePoints';

  my ($query, $row);

  my $lock_local = ($self->lock_active() ? 0 : 1);
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $logical_card_id = 
    $self->GetLogicalCardId($phash);

  my %group_hash;
  $self->GetDefaultCardGroupInfo(\%group_hash, $phash);
  my $group_id = $group_hash{'GroupId'};
  my $group_name = $group_hash{'GroupName'};

  if ($logical_card_id)
  {
    $query =
      $self->prepare("select user_card.group_id, " .
                           " groups.group_name " .
                      " from user_card, groups " .
                     " where user_card.logical_card_id = ? " .
                      "  and groups.group_id = user_card.group_id " .
                     " limit 1 ");
    $query->execute($logical_card_id);
    $row = $query->fetchrow_arrayref;
    if ($row) {
      $group_id = $row->[0];
      $group_name = $row->[1];
    }
  }

  $query =
    $self->prepare("select price_point." . join(", price_point.", keys(%price_point_map) ) . " , " .
                         " org_default_pass_value." . join(", org_default_pass_value.", keys(%default_pass_value_map)) . 
                    " from price_point left join org_default_pass_value " .
                      " on (price_point.name = org_default_pass_value.name) " .
                   " where price_point.group_id = ? and price_point.active = 1 " .
                   " order by price_point.id ");
  $query->execute($group_id);

  #$rhash->{'GroupId'} = $group_id;
  #$rhash->{'GroupName'} = $group_name;

  my $ind = '';
  my $pos = 0;
  while ($row = $query->fetchrow_hashref) 
  {
    while (my ($key, $val) = each(%price_point_map))
    {
      $rhash->{$val . $ind} = $row->{$key};
    }
    while (my ($key, $val) = each(%default_pass_value_map))
    {
      $rhash->{$val . $ind} = $row->{$key};
    }

    $rhash->{'GroupId' . $ind} = $group_id;
    $rhash->{'GroupName' . $ind} = $group_name;

    $ind = "[" . (++$pos) . "]";
  }

  $rhash->{'NPricePoint'} = $pos;

  $self->unlock_commit() if $lock_local;

  return 1;

}

###

sub check_dup_billing_log {
  my $self = shift;
  my $unix_ts = shift;
  my $md5 = shift;

  my $FUNC_NAME = 'check_dup_billing_log';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'billing_log' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = 
    $self->prepare("select count(*) num 
                      from billing_log
                     where ride_time = FROM_UNIXTIME(?) 
                       and conf_checksum = ?");
  my $r = $query->execute($unix_ts, $md5);

  $self->unlock_commit() if $lock_local;

  return $query->fetchrow_arrayref->[0] ;
}

###

sub insert_billing_log {
  my $self = shift;

  my $FUNC_NAME = 'insert_billing_log';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'billing_log' => 'w' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = 
    $self->prepare("REPLACE INTO billing_log 
                      (conf_checksum, 
                       equip_num, 
                       driver, 
                       paddle, 
                       route, 
                       trip, 
                       stop, 
                       ride_time, 
                       latitude, 
                       longitude, 
                       action, 
                       rule, 
                       ruleparam, 
                       reason, 
                       credential, 
                       logical_card_id, 
                       cash_value, 
                       stop_name) 
                    VALUES (?, ?, ?, ?, ?, ?, ?, FROM_UNIXTIME(?), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    ");
  my $r = $query->execute(@_);

  $self->unlock_commit() if $lock_local;

  return $r;
}

###

sub diagnostic_log {
  my $self = shift;
  my $loglvl = shift;
  my $str = shift;

  my $FUNC_NAME = 'diagnostic_log';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'diagnostic_log' => 'w' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $ins =
    $self->prepare("insert into diagnostic_log (loglvl, message) values (?, ?)");

  my $ret = $ins->execute($loglvl, $str);

  $self->unlock_commit() if $lock_local;

  return $ret;
}

sub api_log {
  my $self = shift;
  my $msg = shift;

  my $FUNC_NAME = "api_log";

  my $lock_local = ($self->lock_active() ? 0 : 1);
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $ins =
    $self->prepare("insert into org_api_log (log) values (?)");

  my $ret = $ins->execute($msg);

  $self->unlock_commit() if $lock_local;

  return $ret;



}


### Admin specific functions

sub admin_exists {
  my $self = shift;
  my $username = shift;

  my $FUNC_NAME = 'admin_exists';

  my $lock_local = ($self->lock_active() ? 0 : 1);
#  $self->begin_locked_transaction( { 'admins' => 'r' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $query = $self->prepare("select username from admins where active = 1 and username = ? ");  # and active = 1 ");
  $query->execute($username);
  my $row = $query->fetchrow_arrayref;

  $self->unlock_commit() if $lock_local;

  return 0 if !$row or ($row->[0] ne $username);
  return 1;

}

##

sub get_admin_id {
  my $self = shift;
  my $phash = shift;

  my $FUNC_NAME = 'get_admin_id';

  my ($query, $result, $row);

  my $credential_match = " active = 1 ";
  my @credential_val;

  if ( defined($phash->{'UserName'}) )
  {
    $credential_match .= " and username = ? ";
    push @credential_val , $phash->{'UserName'};
  }

  if ( defined($phash->{'UserId'}) )
  {
    $credential_match .= " and userid = ? ";
    push @credential_val, $phash->{'UserId'};
  }

  if ( defined($phash->{'PasswordHash'}) )
  {
#    $credential_match .= " and passwordhash = ? ";
    $credential_match .= " and password = ? ";
    push @credential_val, $phash->{'PasswordHash'};
  }

  return undef if scalar(@credential_val)==0;

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
#  $self->begin_locked_transaction( { 'admins' => 'r' } ) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;


  $query = $self->prepare( "select userid " .
                          "  from admins " . 
                          " where $credential_match " .
                          " limit 1 ");
  $result = $query->execute(@credential_val);
  $row = $query->fetchrow_arrayref;

  if (!$row) 
  {
    $self->unlock_rollback() if $lock_local;
    return undef;
  }
  $self->unlock_commit() if $lock_local;

  return $row->[0];

}

###

sub CreateAdmin {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;
  my ($query, $result);
  my $FUNC_NAME = "CreateAdmin";

  my %blacklist_field = ( userid => 1 );

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
#  $self->begin_locked_transaction( { 'admins' => 'w', 'audit_admins' => 'w' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;


  if ( !defined($phash->{'UserName'}) ||
        $self->admin_exists($phash->{'UserName'}) )
  {
    $rhash->{'Reason'} = "Invalid admin";
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  my $group_id;
  if ($phash->{GroupId})
  {
    $group_id = $phash->{GroupId};
  }
  else 
  {
    $group_id = $self->get_group_id( $phash->{GroupName} || $DEFAULT_GROUP_NAME );
  }

  if (!$group_id) 
  {
    $rhash->{Reason} = "Bad GroupId";
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  my @db_field;
  my @param;
  while (my ($k, $v) = each(%admins_field_map) )
  {
    next if ( $blacklist_field{lc($k)} );
    push @db_field, $k;

    if ($v eq 'GroupId') 
    {
      push @param, $group_id;
    }
    else
    {
      push @param, $phash->{$v};
    }
  }

  my $audit_id = $self->audit_admins_start(undef, "$PACKAGE_NAME: $FUNC_NAME");

  my $query_str = "insert into admins " .
                          " ( " . join(", ", @db_field) . " ) values " .
                          " ( ? " . ", ?"x(scalar(@db_field)-1) . " )";

  $query = $self->prepare($query_str);

  $result = $query->execute(@param);
  my $user_id = $self->last_insert_id();
  $self->audit_admins_end($user_id, $audit_id);

  $query = $self->prepare("select userid, " . join(', ', @db_field) . " from admins where userid = ?");
  $query->execute($user_id);
  my $href = $query->fetchrow_hashref;
  for (my $i=0; $i<scalar(@db_field); $i++)
  {
    $rhash->{$admins_field_map{$db_field[$i]}} = $href->{$db_field[$i]};
  }
  $rhash->{'UserId'} = $href->{'userid'};

  $self->unlock_commit() if $lock_local;
  return 1;

}


####

sub GetAdmin {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my ($query, $result);
  my $FUNC_NAME = "GetAdmin";

#  my %blacklist_field = ( 'password' => 1 );
  my %blacklist_field = ( );

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $userid = $self->get_admin_id($phash);

  if (!$userid) 
  {
    $rhash->{'Reason'} = "Invalid user credential";
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  my $count=0;
  my $fields;
  while ( my ($k, $v) = each(%admins_field_map) )
  {
    next if ( $blacklist_field{lc($k)} );
    $fields .= ", " if ($count++);
    $fields .= " admins.$k $v ";
  }

  $query = $self->prepare("select groups.group_name GroupName, " .
                          "       $fields " .
                          "  from admins left join groups on (admins.group_id = groups.group_id) ".
                          " where admins.userid = ? " .
                          "   and admins.active = 1 " .
                          " limit 1");
  $query->execute($userid);
  my $row = $query->fetchrow_hashref;

  if (!$row)
  {
    $rhash->{'Reason'} = "Invalid Admin";
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  $self->unlock_commit() if $lock_local;

  while ( my ($k, $v) = each(%$row) )
  {
    $rhash->{$k} = $v;
  }

  return 1;

}

###

sub SetAdmin {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;

  my ($query, $result, $i, $j);
  my $FUNC_NAME= "SetAdmin";

  my $update_fields;
  my @update_val;

  my %blacklist_field = ( username => 1, userid => 1 );

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $userid = $self->get_admin_id(
    {
      UserName => $phash->{UserName},
      UserId => $phash->{UserId}
    }
  );

  if (!defined($userid)) 
  {
    $rhash->{'Reason'} = "Invalid user credential";
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  while ( my ($k, $v) = each(%admins_field_map) )
  {
    next if $blacklist_field{lc($k)} || !exists($phash->{$v});
    $update_fields .= ", " if $update_fields;
    $update_fields .= " $k = ? ";
    push @update_val, $phash->{$v};
  }

  if (scalar(@update_val)==0)
  {
    $rhash->{'Reason'} = "Nothing to update";
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  push @update_val, $userid;

  my $audit_id = 
    $self->audit_admins_start($userid, "$PACKAGE_NAME: $FUNC_NAME");
  $query = 
    $self->prepare("update admins " .
                   "   set $update_fields " . 
                   " where userid = ?");
  $result = 
    $query->execute(@update_val);
  $self->audit_admins_end($userid, $audit_id );

  $rhash->{'UserId'} = $userid;

  $self->unlock_commit() if $lock_local;

  return 1;

}

###

sub RemoveAdmin {
  my $self = shift;
  my $rhash = shift;
  my $phash = shift;
  my ($query, $result);
  my $FUNC_NAME = "RemoveAdmin";

  my $lock_local = ( $self->lock_active() ? 0 : 1 );
#  $self->begin_locked_transaction( { 'admins' => 'w', 'audit_admins' => 'w' }) if $lock_local;
  $self->begin_locked_transaction_for_function($FUNC_NAME) if $lock_local;

  my $user_id = $self->get_admin_id($phash);

  if (!$user_id) 
  {
    $rhash->{'Reason'} = "Invalid user credential";
    $self->unlock_rollback() if $lock_local;
    return undef;
  }

  my $audit_id = $self->audit_admins_start(undef, "$PACKAGE_NAME: $FUNC_NAME");
  $query = $self->prepare("update admins set active = 0 where userid = ? ");
  $result = $query->execute($user_id);
  $self->audit_admins_end($user_id, $audit_id);

  $self->unlock_commit() if $lock_local;
  return 1;

}



return 1;