Subversion Repositories PEEPS

#!/usr/bin/perl

# Redirect error messages to a log of my choosing. (it's annoying to filter for errors in the shared env)

#my $error_log_path = $ENV{SERVER_NAME} eq "volunteers.rollercon.com" ? "/home3/rollerco/logs/" : "/tmp/";

#close STDERR;

#open STDERR, '>>', $error_log_path.'vorc_error.log' or warn "Failed to open redirected logfile ($0): $!";

#warn "Redirecting errors to ${error_log_path}vorc_error.log";

use strict;

use PEEPS;

use CGI qw/param header start_html url/;

use CGI::Cookie;

use tableViewer qw/ notInArray /;

our $h = HTML::Tiny->new( mode => 'html' );

my $dbh = getRCDBH ();

$ENV{HTTPS} = 'ON' if $ENV{SERVER_NAME} =~ /^peeps/;

$dbh->{PrintError} = 1;

my $cookie_string = authenticate (1) || die;

my ($EML, $PWD, $LVL) = split /&/, $cookie_string;

my $user = $ORCUSER;

#my $activated = $ORCUSER->{access};

use DateTime;

#use DateTime::Format::Strptime;

my $dt = DateTime->today;

$dt =~ s/T00\:00\:00$//;

#$user->{policy} = isPersonCovered ($user->{id});

#if ($user->{policy}) {

#  my $daysremaining = remainingPolicyDays ($user->{id}, $user->{policy});

#

#  ERROR ("Not Eligible for Renewal",

#         "You're currently covered by policy $user->{policy}, which has $daysremaining days remaining. You can only renew within the last 90 days of your current policy.",

#         "Viewed Purchase Confirmation page but wasn't eligible to renew.");

#}

## Check for DRAFT payments for the user, get the last one...

my $order_id = WebDB::trim scalar param ("order_id") // ERROR ("No OrderID Provided", "No OrderID provided to confirmation page.", "No OrderID provided to confirmation page.");

#my $checkout = $dbh->selectrow_hashref ("SELECT * FROM wftdi_peeps.square_order where status = ? and person_id = ? order by created desc limit 1", undef, "DRAFT", $user->{id});

my $checkout = $dbh->selectrow_hashref ("SELECT * FROM wftdi_peeps.square_order where status = ? and order_id = ? order by created desc limit 1", undef, "DRAFT", $order_id);

if (!$checkout->{square_id}) {

  ERROR ("Open Order Not Found",

         "There are no open orders or pending payments found with OrderID: $order_id",

         "Viewed Purchase Confirmation page with OrderID [$order_id] but didn't have any open payments.");

}

# Once we're here, we've found an order_id in DRAFT status. We should make sure it belongs to the user (or their league)

if ($checkout->{person_id} ne $user->{person_id} and notInArray ($checkout->{organization_id}, isLeagueAdmin ($user->{person_id}))) {

  # invalid OrderID for user.

  ERROR ("Invalid User for Order",

         "The provided OrderID does not belong to you or for a league that you have Administrative access.",

         "Attempted to view Confirmation page with someone else's OrderID [$order_id]");

}

use REST::Client;

use JSON;

use Data::Dumper;

my $client = REST::Client->new();

my $headers = {

  "Authorization" => 'Bearer '.getSetting ("SQUARE_AUTH_TOKEN"),

  "Content-Type" => "application/json",

};

$client->setHost (getSetting ("SQUARE_API_HOST"));

$client->GET(

  '/v2/orders/'.$checkout->{order_id},

  $headers

);

my $response = from_json($client->responseContent());

#warn Dumper($response);

if ($response->{order}->{state} eq "DRAFT") {

  # Checkout isn't complete yet...

  ERROR ("Open Payment",

         "It looks like you haven't completed check out at Square yet. ".$h->a ({href=>$checkout->{url}}, "[Click Here]")." to complete your payment.",

         "Viewed Confirmation page with an open payment.");

} elsif ($response->{order}->{state} eq "OPEN") {

  # Checkout completed...

  # create start and end dates based on the user's timezone

  my $user_tz = $response->{order}->{tenders}->[0]->{note} || 'America/Chicago';

#  warn $user_tz;

  # Parse the UTC timestamp and set its timezone to 'UTC'

  use DateTime::Format::Strptime qw( );

  my $format = DateTime::Format::Strptime->new(

    pattern   => '%Y-%m-%dT%H:%M:%SZ',

    strict    => 1,

    time_zone => "UTC",

#    on_error  => "croak",

  );

  my $dt = $format->parse_datetime( $response->{order}->{tenders}->[0]->{created_at} );

  $dt->set_time_zone( $user_tz );

  my $adjusted_created_at = $dt->strftime( '%Y-%m-%d %H:%M:%S' );

  # "Fix" UTC format for MySQL

  $response->{order}->{tenders}->[0]->{created_at} =~ s/T/ /;

  $response->{order}->{tenders}->[0]->{created_at} =~ s/Z$//;

  # Update the square order status and add the exact time of payment.

  $dbh->do ("update square_order set status = ?, payment_id = ?, payment_time = ? where square_id = ?", undef,

    "PAID",

    $response->{order}->{tenders}->[0]->{id},

    $response->{order}->{tenders}->[0]->{created_at},

    $checkout->{square_id}

  );

  # Here's where it gets complicated.  We have to figure out what kind of policy it is to know how to update the coverage records...

  if ($checkout->{policy_id} eq "1") { # Personal Accident Medical

    my $policy_id = isPersonCovered ($user->{id});

    if ($policy_id) {

      # extend existing coverage

      $dbh->do ("update coverage set end = date_add(end, INTERVAL 1 YEAR) where id = ?", undef, $policy_id);

    } else {

      # insert new coverage

      my ($new_policy_id) = $dbh->selectrow_array ("select max(id)+1 from coverage");

      $dbh->do ("insert into coverage (id, person_id, policy_id, policy_name, fee, created, start, end, active) values (?, ?, ?, ?, ?, ?, date(?), date_add(date(?), INTERVAL 1 YEAR), ?)", undef,

        $new_policy_id,

        $user->{id},

        $checkout->{policy_id},

        $response->{order}->{line_items}->[0]->{name},

        $response->{order}->{tenders}->[0]->{amount_money}->{amount} / 100,

        $response->{order}->{tenders}->[0]->{created_at},

        $adjusted_created_at,

        $adjusted_created_at,

        1

      );

      $dbh->do ("replace into full_person select * from v_person where id = ?", undef, $user->{id});

      $policy_id = isPersonCovered ($user->{id});

    }

    if ($policy_id) {

      my $daysremaining = remainingPolicyDays ($user->{id}, $policy_id);

      communicateConfirmation ({ to => $user->{email},

                                 policy => $response->{order}->{line_items}->[0]->{name},

                                 message => "You're currently covered by policy $policy_id, which has $daysremaining days remaining." });

    } else {

      ERROR ("Unknown Error Confirming Purchase",

             "It seemed like you successfully purchased insurance, but then there was an issue updating our records. You should probably email peeps\@wftdi.com and tell them to investigate.",

             "ERROR: Checkout complete, but PEEPS coverage failed to update.");

    }

  } elsif ($checkout->{policy_id} eq "2") { # League General Liability

    my $policy_id = isLeagueCovered ($checkout->{organization_id});

    if ($policy_id) {

      # extend existing coverage

      $dbh->do ("update org_coverage set end = date_add(end, INTERVAL 1 YEAR) where id = ?", undef, $policy_id);

    } else {

      # insert new coverage

      my ($new_policy_id) = $dbh->selectrow_array ("select max(id)+1 from org_coverage");

      $dbh->do ("insert into org_coverage (id, organization_id, policy_id, policy_name, fee, created, start, end, active) values (?, ?, ?, ?, ?, ?, date(?), date_add(date(?), INTERVAL 1 YEAR), ?)", undef,

        $new_policy_id,

        $checkout->{organization_id},

        $checkout->{policy_id},

        $response->{order}->{line_items}->[0]->{name},

        $response->{order}->{tenders}->[0]->{amount_money}->{amount} / 100,

        $response->{order}->{tenders}->[0]->{created_at},

        $adjusted_created_at,

        $adjusted_created_at,

        1

      );

      $policy_id = isLeagueCovered ($user->{id});

    }

    if ($policy_id) {

      my $daysremaining = remainingOrgPolicyDays ($checkout->{organization_id}, $policy_id);

      communicateConfirmation ({ to => $user->{email},

                                 policy => $response->{order}->{line_items}->[0]->{name},

                                 message => "Your league is currently covered by policy $policy_id, which has $daysremaining days remaining.",

                                 orgid => $checkout->{organization_id} });

    } else {

      ERROR ("Unknown Error Confirming Purchase",

             "It seemed like you successfully purchased insurance, but then there was an issue updating our records. You should probably email peeps\@wftdi.com and tell them to investigate.",

             "ERROR: Checkout complete, but PEEPS coverage failed to update.");

    }

  } elsif ($checkout->{policy_id} eq "3") { # League Alcohol Liability

    my $policy_id = isLeagueCovered ($checkout->{organization_id}, undef, "WFTDA League Alcohol Liability");

    if ($policy_id) {

      # extend existing coverage

      $dbh->do ("update org_coverage set end = date_add(end, INTERVAL 1 YEAR) where id = ?", undef, $policy_id);

    } else {

      # insert new coverage

      my ($new_policy_id) = $dbh->selectrow_array ("select max(id)+1 from org_coverage");

      $dbh->do ("insert into org_coverage (id, organization_id, policy_id, policy_name, fee, created, start, end, active) values (?, ?, ?, ?, ?, ?, date(?), MAKEDATE(YEAR(CURDATE()), 365), ?)", undef,

        $new_policy_id,

        $checkout->{organization_id},

        $checkout->{policy_id},

        $response->{order}->{line_items}->[0]->{name},

        $response->{order}->{tenders}->[0]->{amount_money}->{amount} / 100,

        $response->{order}->{tenders}->[0]->{created_at},

        $adjusted_created_at,

        1

      );

      $policy_id = isLeagueCovered ($user->{id});

    }

    if ($policy_id) {

      my $daysremaining = remainingOrgPolicyDays ($checkout->{organization_id}, $policy_id);

      communicateConfirmation ({ to => $user->{email},

                                 policy => $response->{order}->{line_items}->[0]->{name},

                                 message => "Your league is currently covered by policy $policy_id, which has $daysremaining days remaining.",

                                 orgid => $checkout->{organization_id} });

    } else {

      ERROR ("Unknown Error Confirming Purchase",

             "It seemed like you successfully purchased insurance, but then there was an issue updating our records. You should probably email peeps\@wftdi.com and tell them to investigate.",

             "ERROR: Checkout complete, but PEEPS coverage failed to update.");

    }

  } elsif ($checkout->{policy_id} eq "4") { # RollerCon

    # insert new coverage

    my ($new_policy_id) = $dbh->selectrow_array ("select max(id)+1 from coverage");

    $dbh->do ("insert into coverage (id, person_id, policy_id, policy_name, fee, created, start, end, active) values (?, ?, ?, ?, ?, ?, ?, ?, ?)", undef,

      $new_policy_id,

      $user->{id},

      $checkout->{policy_id},

      $response->{order}->{line_items}->[0]->{name},

      $response->{order}->{tenders}->[0]->{amount_money}->{amount} / 100,

      $response->{order}->{tenders}->[0]->{created_at},

      "2026-07-09",

      "2026-07-11",

      1

    );

    my $policy_id = $new_policy_id;

    if ($policy_id) {

      communicateConfirmation ({ to => $user->{email},

                                 policy => $response->{order}->{line_items}->[0]->{name},

                                 message => "You are covered for RollerCon on-site event skating activities by policy $policy_id, July 9-11, 2026." });

    } else {

      ERROR ("Unknown Error Confirming Purchase",

             "It seemed like you successfully purchased insurance, but then there was an issue updating our records. You should probably email peeps\@wftdi.com and tell them to investigate.",

             "ERROR: Checkout complete, but PEEPS coverage failed to update.");

    }

  }

} else {

  # Something weird happened.

  ERROR ();

}

sub emailConfirmation {

  my $target = shift // return;

  my $subject = "WFTDA Insurance Coverage Confirmation";

  my $message = $h->p ($h->br ()).

                $h->p ({ style => "text-align: center;" }, $h->img ({ alt => "", src => "https://peeps.gadell.org/images/wftda-insurance-logo.svg", style => "width: 300px; height: 97px;"})).

                $h->p (

                         $h->br (),

                         ["Greetings", $h->br (), $h->br ()],

                         "Thank you for enrolling in WFTDA Insurance! This email confirms your coverage is in effect for 12 months, please retain it for your records. For additional resources, forms and information, please visit ".$h->a ({ href => "https://wftdi.com" }, "wftdi.com").'.  ',

                         $h->br (),

                         "WFTDA Insurance",

                         $h->br ()

                       );

  use PEEPSMailer;

  EmailUser ($target, $subject, $message);

}

sub communicateConfirmation {

  my $parameter = shift // return;

  my $target = $parameter->{to};

  my $policy = $parameter->{policy};

  my $message = $parameter->{message};

  my $orgid = $parameter->{orgid} // "";

  my @league_admins = $orgid ? map { $_->[0] } @{ $dbh->selectall_arrayref ("select distinct email from person left join role on person.id = role.person_id where role = ? and member_org_id = ? and person.id <> ?", undef, "League Admin", $orgid, $user->{person_id}) } : ();

  my $subject = $policy." Coverage Confirmation";

  print header (-cookie=>CGI::Cookie->new(-name=>'PEEPSAUTH',-value=>"$cookie_string"));

  printRCHeader("Insurance Purchase Confirmation");

  print $h->close ("table");

  print $h->h2 ($subject);

  print $h->div ({ style=>"max-width:450px;" }, "You have successfully purchased $policy coverage.", $message, " ");

  print $h->button ({onclick => "window.location.href='/';"}, "Home");

  print $h->close ("BODY", "HTML");

  my $message = $h->p ($h->br ()).

                $h->p ({ style => "text-align: center;" }, $h->img ({ alt => "", src => "https://peeps.gadell.org/images/wftda-insurance-logo.svg", style => "width: 300px; height: 97px;"})).

                $h->p (

                         $h->br (),

                         ["Greetings", $h->br (), $h->br ()],

                         ["You have successfully purchased $policy coverage.", $message, $h->br (), $h->br ()],

                         "Thank you for enrolling in WFTDA Insurance! This email confirms your coverage is in effect, please retain it for your records. For additional resources, forms and information, please visit ".$h->a ({ href => "https://wftdi.com" }, "wftdi.com").'.  ',

                         $h->br (),

                         "WFTDA Insurance",

                         $h->br ()

                       );

  if ($orgid) {

    orglogit ($user->{id}, $orgid, "Successfully purchased $policy coverage");

    logit ($user->{id}, "Successfully purchased $policy coverage for ".getLeagueName ($orgid).".");

  } else {

    logit ($user->{id}, "Successfully purchased $policy coverage.")

  }

  use PEEPSMailer;

  if (scalar @league_admins) {

    EmailMultipleUsers ({ to => [$target], cc => \@league_admins, subject => $subject, body => $message });

  } else {

    EmailUser ($target, $subject, $message);

  }

  exit;

}

sub ERROR {

  my $header = shift // "Unknown Error";

  my $text   = shift // "Something unexpectedly bad happened.";

  my $logmsg = shift // "Unknown Error happened while viewing the Confirmation page";

  print header (-cookie=>CGI::Cookie->new(-name=>'PEEPSAUTH',-value=>"$cookie_string"));

  printRCHeader("Confirmation");

  print $h->close ("table");

  print $h->h2 ($header);

  print $h->div ({ style=>"max-width:450px;" }, $text, " ");

  print $h->button ({onclick => "window.location.href='/';"}, "Home");

  print $h->close ("BODY", "HTML");

  logit ($user->{id}, $logmsg);

  exit;

}