[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