[LON-CAPA-cvs] cvs: modules /raeburn/register registrations.pm
raeburn
raeburn at source.lon-capa.org
Tue Mar 19 20:13:34 EDT 2019
raeburn Wed Mar 20 00:13:34 2019 EDT
Modified files:
/modules/raeburn/register registrations.pm
Log:
- Two additional columns in registration report for events with fees:
(a) Amount paid, (b) Amount owed.
- Additional filter when creating registration report for events with fees:
Payment status: Made payment, Payment owed, No payment needed, or Any.
-------------- next part --------------
Index: modules/raeburn/register/registrations.pm
diff -u modules/raeburn/register/registrations.pm:1.13 modules/raeburn/register/registrations.pm:1.14
--- modules/raeburn/register/registrations.pm:1.13 Tue Mar 19 23:59:13 2019
+++ modules/raeburn/register/registrations.pm Wed Mar 20 00:13:33 2019
@@ -6,7 +6,7 @@
# privilege for an event can edit the contents of the registration
# form.
#
-# $Id: registrations.pm,v 1.13 2019/03/19 23:59:13 raeburn Exp $
+# $Id: registrations.pm,v 1.14 2019/03/20 00:13:33 raeburn Exp $
#
# Stuart P Raeburn
#
@@ -91,7 +91,7 @@
my %sponsorinfo = ();
my ($year,$event,$portal,$returnpage,$event_id,$webpath,$year_quoted,
- $event_quoted,$branch,$choice,$editaction,$phase);
+ $event_quoted,$branch,$choice,$editaction,$phase,$fees);
$portal = "/registrations";
if (exists $params{'return'}) {
$returnpage = $params{'return'};
@@ -145,7 +145,7 @@
&invalid_event($r,$dbh,$year,$event,$portal,$returnpage,$exitpage,\@sponsors,\%sponsorinfo,$quoted_user);
return OK;
}
- ($event_id,$webpath) = $dbh->selectrow_array("SELECT event_id,webpath FROM event_config WHERE year=$year_quoted AND event=$event_quoted");
+ ($event_id,$webpath,$fees) = $dbh->selectrow_array("SELECT event_id,webpath,fees FROM event_config WHERE year=$year_quoted AND event=$event_quoted");
if ($event_id eq '') {
&invalid_event($r,$dbh,$year,$event,$portal,$returnpage,$exitpage,\@sponsors,\%sponsorinfo,$quoted_user);
return OK;
@@ -280,26 +280,42 @@
$returnpage,$exitpage,\@sponsors,\%sponsorinfo);
}
} else {
-# Special entries for username and modified
+# Special entries for username, modified, amount paid, and amount owed
my $fields = @rowitems;
@{$rowitems[$fields+1]} = ('user_id');
$rowtitles[$fields+1] = 'Username';
@{$rowitems[$fields]} = ('modified');
$rowtitles[$fields] = 'Date last modified';
+ my @extraorder = ('modified','user_id');
+ if ($fees) {
+ @{$rowitems[$fields+2]} = ('paid');
+ $rowtitles[$fields+2] = 'Amount paid';
+ @{$rowitems[$fields+3]} = ('owed');
+ $rowtitles[$fields+3] = 'Amount owed';
+ push(@extraorder,('paid','owed'));
+ }
my $extracount = $fields;
my %extrafields = (
user_id => 'Username',
modified => 'Date last modified',
);
- foreach my $extra (sort(keys(%extrafields))) {
+ if ($fees) {
+ $extrafields{'paid'} = 'Amount paid';
+ $extrafields{'owed'} = 'Amount owed';
+ }
+ foreach my $extra (@extraorder) {
$extracount ++;
$form_elements{$extra}{'v_position'} = $extracount;
$form_elements{$extra}{'h_position'} = 1;
$form_elements{$extra}{'required'} = 'N';
$form_elements{$extra}{'title'} = $extrafields{$extra};
$form_elements{$extra}{'rowtitle'} = $extrafields{$extra};
- $form_elements{$extra}{'tablename'} = 'event_registration';
+ if (($extra eq 'paid') || ($extra eq 'owed')) {
+ $form_elements{$extra}{'tablename'} = 'webcredit';
+ } else {
+ $form_elements{$extra}{'tablename'} = 'event_registration';
+ }
my $vpos = $extracount-1;
my $hpos = 0;
unless(exists($rowitems[$vpos])) {
@@ -309,7 +325,7 @@
$rowitems[$vpos][$hpos] = $extra;
}
if ($choice eq 'display') {
- &generate_report($r,$event,$year,$event_id,\%params,$user,$portal,$returnpage,$exitpage,\@info,\@workshop,$dbh,$contact_email,$contact_name,\%form_elements,\@rowitems,\@rowtitles,\@sponsors,\%sponsorinfo,$webpath);
+ &generate_report($r,$event,$year,$event_id,\%params,$user,$portal,$returnpage,$exitpage,\@info,\@workshop,$dbh,$contact_email,$contact_name,\%form_elements,\@rowitems,\@rowtitles,\@sponsors,\%sponsorinfo,$webpath,$fees);
} else {
&configure_report($r,$event_id,\%params,$user,$portal,$returnpage,$exitpage,\@info,\@workshop,$dbh,$contact_email,$contact_name,\%form_elements,\@rowitems,\@rowtitles,\@sponsors,\%sponsorinfo);
}
@@ -954,6 +970,39 @@
<tr>
<td colspan="4"> </td>
</tr>
+ <tr class="LC_adminbar">
+ <td> </td>
+ <td valign="bottom"><nobr><img src="/images/requests/bl_step5.gif" width="21" height="24" valign="middle""> </nobr>
+ </td>
+ <td align="left">
+ <nobr><b>Payment status of users</b></nobr>
+ </td>
+ <td width="100%"> </td>
+ </tr>
+ <tr>
+ <td colspan="4"> </td>
+ </tr>
+ <tr>
+ <td> </td>
+ <td colspan="3">
+ <table>
+ <tr>
+ <td>
+ <select name="payment">
+ <option value="-1" selected="selected">Please select</option>
+ <option value="yes">Made payment</option>
+ <option value="owed">Payment owed</option>
+ <option value="none">No payment needed</option>
+ <option value="any">Any payment status</option>
+ </select>
+ </td>
+ </tr>
+ </table>
+ </td>
+ </tr>
+ <tr>
+ <td colspan="4"> </td>
+ </tr>
<tr>
<td> </td>
<td colspan="3"><input type="button" class="buttoncolored" name="display" value="Display data" onClick="javascript:validForm()"></td>
@@ -968,7 +1017,7 @@
}
sub generate_report {
- my ($r,$event,$year,$event_id,$params,$user,$portal,$returnpage,$exitpage,$info,$workshop,$dbh,$contact_email,$contact_name,$form_elements,$rowitems,$rowtitles,$sponsors,$sponsorinfo,$webpath) = @_;
+ my ($r,$event,$year,$event_id,$params,$user,$portal,$returnpage,$exitpage,$info,$workshop,$dbh,$contact_email,$contact_name,$form_elements,$rowitems,$rowtitles,$sponsors,$sponsorinfo,$webpath,$fees) = @_;
my $enc_event = &HTML::Entities::encode($event);
my %quoted_params;
my $quoted_user = $dbh->quote( $user );
@@ -989,6 +1038,7 @@
my $statusfilter = '';
my $startstr = '';
my $endstr = '';
+ my $payfilter = '';
foreach my $param (%{$params}) {
if ($param eq 'domains') {
if (ref($$params{$param}) eq 'ARRAY') {
@@ -1007,6 +1057,10 @@
unless (($status eq 'any') || ($status == -1)) {
$statusfilter = " AND er.status='$status' ";
}
+ } elsif ($param eq 'payment') {
+ if (($$params{$param} eq 'yes') || ($$params{$param} eq 'none') || ($$params{$param} eq 'owed')) {
+ $payfilter = $$params{$param};
+ }
}
}
unless (defined($$params{'nostartdate'})) {
@@ -1120,19 +1174,98 @@
}
my %attendancedays;
foreach my $user_id (@users) {
- my @days;
+ my @curr_attendancedays;
my $user_id_quoted = $dbh->quote($user_id);
my $statement = "SELECT name from attendancedays WHERE user_id = $user_id_quoted AND event_id = '$event_id'";
my $sth = $dbh->prepare("$statement");
$sth->execute();
while (my @row = $sth->fetchrow_array) {
if (@row > 0) {
+ push(@curr_attendancedays, at row);
foreach my $day (@row) {
$attendancedays{$user_id}{$day} = 'Y';
}
}
}
$sth->finish;
+ if (@curr_attendancedays > 0) {
+ my (%check_ok,%regtype);
+ $statement = "SELECT iid,description FROM products WHERE (event_id = '$event_id' AND registration = 'Y')";
+ my $sth = $dbh->prepare($statement);
+ $sth->execute();
+ while (my ($iid,$desc) = $sth->fetchrow_array()) {
+ $check_ok{$iid} = &Apache::LON::register::check_conditions($dbh,$iid,$event_id,$quoted_user);
+ $regtype{$iid} = $desc;
+ }
+ $sth->finish;
+ foreach my $iid (keys(%check_ok)) {
+ if ($check_ok{$iid}) {
+ $currvalue{$user_id}{$regtype{$iid}} = 'Y';
+ }
+ }
+ }
+ }
+ my (%payment, at consortium,%feeinfo,%owed,%paid);
+ if ($fees) {
+ if (grep(/^paid$/, at fields)) {
+ my $statement = "SELECT p.user_id,w.amount from purchaser p, webcredit w WHERE (p.event_id = '$event_id' AND w.pid = p.pid AND (w.state='A' || w.state='D'))";
+ my $sth = $dbh->prepare("$statement");
+ $sth->execute();
+ while (my ($user_id,$amount) = $sth->fetchrow_array) {
+ if (grep(/^\Q$user_id\E$/, at users)) {
+ $payment{$user_id} = sprintf("%.2f",$amount);
+ }
+ }
+ }
+ if (@users) {
+ my $sth = $dbh->prepare("SELECT iid,description,cost,required,longdesc,type,waiver,infotext,displayorder,dependency,displayoptional,localdiscount FROM products WHERE event_id = '$event_id'");
+ $sth->execute;
+ while ( my ($iid,$desc,$cost,$reqd,$longdesc,$type,$waiver,$infotext,$displayorder,$dependency,$displayoptional,$localdiscount) = $sth->fetchrow_array) {
+ $feeinfo{$iid}{desc} = $desc;
+ $feeinfo{$iid}{cost} = $cost;
+ $feeinfo{$iid}{reqd} = $reqd;
+ $feeinfo{$iid}{longdesc} = $longdesc;
+ $feeinfo{$iid}{type} = $type;
+ $feeinfo{$iid}{waiver} = $waiver;
+ $feeinfo{$iid}{infotext} = $infotext;
+ $feeinfo{$iid}{displayorder} = $displayorder;
+ $feeinfo{$iid}{dependency} = $dependency;
+ $feeinfo{$iid}{displayoptional} = $displayoptional;
+ $feeinfo{$iid}{localdiscount} = $localdiscount;
+ }
+ $sth->finish;
+ $sth = $dbh->prepare("SELECT user_id FROM consortium");
+ $sth->execute;
+ while (my $user_id = $sth->fetchrow_array) {
+ push(@consortium,$user_id);
+ }
+ $sth->finish;
+ foreach my $user (@users) {
+ ($paid{$user},$owed{$user}) = &get_payment_status($dbh,$user,$event_id,\@consortium,\%feeinfo,$currvalue{$user});
+ }
+ }
+ if ($payfilter) {
+ my @okusers;
+ foreach my $user_id (@users) {
+ if ($payment{$user_id}) {
+ if ($payfilter eq 'yes') {
+ push(@okusers,$user_id);
+ }
+ } else {
+ if ($payfilter eq 'none') {
+ unless ($owed{$user_id} > 0) {
+ push(@okusers,$user_id);
+ }
+ }
+ }
+ if ($payfilter eq 'owed') {
+ if ($owed{$user_id} > 0) {
+ push(@okusers,$user_id);
+ }
+ }
+ }
+ @users = @okusers;
+ }
}
my $jscript = '';
my $headerweb = '';
@@ -1182,6 +1315,20 @@
push(@{$data[$rowcount]},'N');
}
}
+ } elsif ($field eq 'paid') {
+ my $shown = '$0.00';
+ if ($payment{$user_id}) {
+ $shown = '$'.$payment{$user_id};
+ }
+ $webcontent[$rowcount] .= '<td align="right">'.$shown.'</td>';
+ push(@{$data[$rowcount]},$shown);
+ } elsif ($field eq 'owed') {
+ my $shown = '$0.00';
+ if ($owed{$user_id}) {
+ $shown = '$'.$owed{$user_id};
+ }
+ $webcontent[$rowcount] .= '<td align="right">'.$shown.'</td>';
+ push(@{$data[$rowcount]},$shown);
} else {
$webcontent[$rowcount] .= '<td align="right">'.$currvalue{$user_id}{$field}.'</td>';
push(@{$data[$rowcount]},$currvalue{$user_id}{$field});
@@ -1262,6 +1409,87 @@
return;
}
+sub get_payment_status {
+ my ($dbh,$user,$event_id,$consortium,$feeinfo,$currvalue) = @_;
+ my $islocal = &Apache::LON::register::is_local($user,$event_id);
+ my (%total,%balance,%credit,%curr);
+ if (ref($currvalue) eq 'HASH') {
+ %curr = %{$currvalue};
+ }
+ my $quoted_user = $dbh->quote( $user );
+ my (%total,%balance,%credit);
+ my $statement = "SELECT t.tid,t.iid,t.quantity,t.status,t.amount FROM purchaser p, transactions t, webcredit w WHERE p.event_id = '$event_id' AND p.user_id = $quoted_user AND p.pid = t.pid AND w.wid = t.wid AND w.state IS NOT NULL ORDER BY t.tid";
+ my $sth = $dbh->prepare("$statement");
+ $sth->execute();
+ while (my ($tid,$iid,$quant,$iidstatus,$amount) = $sth->fetchrow_array) {
+ if ($iid) {
+ $total{$iid} += $quant;
+ $balance{$iid} += $amount;
+ }
+ }
+ $sth->finish;
+ my %conditionals;
+ foreach my $iid (sort(keys(%{$feeinfo}))) {
+ if ($$feeinfo{$iid}{'dependency'} ne '') {
+ if ($curr{$$feeinfo{$iid}{'dependency'}} eq 'Y') {
+ $curr{$$feeinfo{$iid}{desc}} = 'Y';
+ }
+ }
+ if ($$feeinfo{$iid}{reqd} eq 'Y') {
+ if ($$feeinfo{$iid}{'type'} eq 'accomm') {
+ my $quoted_type = $dbh->quote( $$feeinfo{$iid}{desc} );
+ my $nights = $dbh->selectrow_array("SELECT nights FROM event_accommodation WHERE (user_id = $quoted_user AND event_id = '$event_id' AND type = $quoted_type)");
+ $total{$iid} = $nights;
+ } else {
+ if ($total{$iid} eq '' || $total{$iid} == 0) {
+ $total{$iid} = 1;
+ }
+ }
+ if ($balance{$iid} eq '') {
+ $balance{$iid} = 0;
+ }
+ } elsif ($curr{$$feeinfo{$iid}{desc}} eq 'Y') {
+ if ($$feeinfo{$iid}{'type'} eq 'accomm') {
+ my $quoted_type = $dbh->quote( $$feeinfo{$iid}{desc} );
+ my $nights = $dbh->selectrow_array("SELECT nights FROM event_accommodation WHERE (user_id = $quoted_user AND event_id = '$event_id' AND type = $quoted_type)");
+ $total{$iid} = $nights;
+ } elsif ($$feeinfo{$iid}{'type'} eq 'conditional') {
+ my $check_ok = &Apache::LON::register::check_conditions($dbh,$iid,$event_id,$quoted_user);
+ if ($check_ok) {
+ $total{$iid} = 1;
+ $conditionals{$iid} = 1;
+ } else {
+ $conditionals{$iid} = 0;
+ }
+ } else {
+ if ($total{$iid} eq '' || $total{$iid} == 0) {
+ $total{$iid} = 1;
+ }
+ }
+ if ($balance{$iid} eq '') {
+ $balance{$iid} = 0;
+ }
+ }
+ }
+ my $islocal = &Apache::LON::register::is_local($user,$event_id);
+ my $paid = 0;
+ my $owed = 0;
+ foreach my $iid (keys(%balance)) {
+ if (($$feeinfo{$iid}{'waiver'} eq 'Y') && (grep/^\Q$user\E$/,@{$consortium})) {
+ $credit{$iid} = $balance{$iid};
+ } elsif (($$feeinfo{$iid}{'waiver'} eq 'Y') && ($islocal)) {
+ $credit{$iid} = $balance{$iid} - ($total{$iid}*($$feeinfo{$iid}{'cost'}-$$feeinfo{$iid}{'localdiscount'}));
+ } else {
+ $credit{$iid} = $balance{$iid} - ($total{$iid} * $$feeinfo{$iid}{'cost'});
+ }
+ $paid += $balance{$iid};
+ $owed -= $credit{$iid};
+ }
+ $paid = sprintf("%.2f",$paid);
+ $owed = sprintf("%.2f",$owed);
+ return($paid,$owed);
+}
+
sub display_edit_menu {
my ($r,$dbh,$event_id,$year,$event,$portal,$params,$rowitems,$returnpage,
$exitpage,$sponsors,$sponsorinfo) = @_;
More information about the LON-CAPA-cvs
mailing list