Committer: dnikolaev
LJSUP-7245 Loss of precision in paidsummary.bml fixed.U trunk/htdocs/admin/accounts/paidsummary.bml
Modified: trunk/htdocs/admin/accounts/paidsummary.bml =================================================================== --- trunk/htdocs/admin/accounts/paidsummary.bml 2010-10-26 10:13:36 UTC (rev 9650) +++ trunk/htdocs/admin/accounts/paidsummary.bml 2010-10-27 06:34:56 UTC (rev 9651) @@ -6,6 +6,9 @@ my $PAGE_SIZE = 50; + # A list of the possible payment methods + my @pay_list = qw(cash cc check moneyorder offerpal paypal sms sup_sms sup_yandex wallet webmoney); + my $remote = LJ::get_remote(); return "You must first <a href=\"/login.bml?ret=1\">log in</a>." unless $remote; @@ -20,8 +23,6 @@ # What's our view mode? html or csv my $view_mode = $GET{view} eq 'csv' ? 'csv' : 'html'; - # A list of the possible payment methods - my @pay_list = qw(cash cc check moneyorder offerpal paypal sms sup_sms sup_yandex wallet webmoney); # Check if valid payment method specified my $pay_method = undef; foreach my $method (@pay_list) { @@ -34,18 +35,134 @@ my $users = LJ::ehtml($GET{users}); $users = 'all' unless $users eq 'cyr' or $users eq 'non-cyr' or $users eq 'anon'; # correcting bad input to 'all' - # Get all the relevant dates: current date and date range for reporting - my ($year, $month, $day, $date_low, $date_high) = get_dates(); + ### Get all the relevant dates: current date and date range for reporting - # Get the Payments - my $payments_ref = get_payments($date_low, $date_high, $pay_method, $users); + # default values for year/month/day + my $year = $GET{year} + 0; + my $month = $GET{month} + 0; + my $day = $GET{day} eq '*' ? '*' : $GET{day} + 0; + unless ($year && $month) { + my @time = localtime(); + $year = $time[5] + 1900; + $month = $time[4] + 1; + $day = $time[3]; + } + + # find low/high dates so we can do an indexed query from payments + my ($date_low, $date_high); + + my $fmt = sub { $dbh->quote(sprintf("%04d-%02d-%02d 00:00:00", @_)) }; + + # find beginning/end times for this pay period + if ($day eq '*') { + $date_low = $fmt->($year, $month, '01'); + if ($month >= 12) { + $date_high = $fmt->($year + 1, 1, '01'); + } else { + $date_high = $fmt->($year, $month + 1, '01'); + } + } else { + $date_low = $fmt->($year, $month, $day); + if ($day >= LJ::TimeUtil->days_in_month($month, $year)) { + if ($month >= 12) { + $date_high = $fmt->($year + 1, 1, '01'); + } else { + $date_high = $fmt->($year, $month + 1, '01'); + } + } else { + $date_high = $fmt->($year, $month, $day+1); + } + } + + ### Get the Payments + + # Get all the payments for the billing period + + my $payments_ref = {}; + + my $select_payments_sql = "SELECT * FROM payments WHERE used = 'Y'"; + + if ($pay_method && $pay_method ne '') { + $select_payments_sql .= " AND method = '$pay_method'"; + } else { + $select_payments_sql .= " AND method <> 'free'"; + } + + if ($users eq 'anon') { + $select_payments_sql .= " AND userid = 0"; + } + + $select_payments_sql .= " AND daterecv >= $date_low AND daterecv < $date_high"; + + $sth = $dbh->prepare($select_payments_sql); + $sth->execute; + $sth->{mysql_use_result} = 1; + die $dbh->errstr if $dbh->err; + + if ($users eq 'all' or $users eq 'anon') + { + while (my $row = $sth->fetchrow_hashref) { + $payments_ref->{$row->{payid}} = $row; + } + } + else + { + my @users; + while (my $row = $sth->fetchrow_hashref) { + $payments_ref->{$row->{payid}} = $row; + push @users, $row->{userid} if $users ne 'all' and $users ne 'anon'; + } + + my $us = LJ::load_userids(@users); + + foreach my $payid (keys %{$payments_ref}) { + my $userid = $payments_ref->{$payid}->{userid}; + delete $payments_ref->{$payid} + if not $us->{$userid} + or $users eq 'cyr' and not LJ::SUP->is_sup_enabled($us->{$userid}) + or $users eq 'non-cyr' and LJ::SUP->is_sup_enabled($us->{$userid}); + } + } + + # Get the Items for these Payments - my ($payitems_ref, $payid_piids_ref, $piid_dates_ref) = get_payitems($payments_ref); + my $payitems_ref = {}; + my $payid_piids_ref = {}; + my $piid_dates_ref = {}; + + if (scalar(keys %$payments_ref)) { + my $select_payitems_sql = 'SELECT * FROM payitems WHERE payid IN (' . join(',', keys %$payments_ref) . ')'; + $sth = $dbh->prepare($select_payitems_sql); + $sth->execute; + $sth->{mysql_use_result} = 1; + die $dbh->errstr if $dbh->err; + + while (my $row = $sth->fetchrow_hashref) { + my $payid = $row->{payid}; + my $piid = $row->{piid}; + + $payitems_ref->{$piid} = LJ::Pay::Payment::PayItem->new_memonly(%$row); + $piid_dates_ref->{$piid} = $payments_ref->{$payid}->{daterecv}; + $payid_piids_ref->{$payid} = [] unless exists $payid_piids_ref->{$payid}; + push @{ $payid_piids_ref->{$payid} }, $piid; + } + } + + # Get the refund amount on a per-payment and per-item basis - my ($payid_refunds_ref, $piid_refunds_ref) = get_refund_amounts($payments_ref, $payitems_ref, $payid_piids_ref); + my $payid_refunds_ref = {}; + my $piid_refunds_ref = {}; + + foreach my $item (values %{ $payitems_ref || {} }) { + if ($item->{status} eq 'refund') { + $piid_refunds_ref->{$item->{piid}} = -$item->{amt}; + $payid_refunds_ref->{$item->{payid}} += -$item->{amt}; + } + } + # This is the primary stats hash - it's documented at the end of this file my %stats = (); @@ -53,11 +170,125 @@ my %row_ctl = (count => 0, show => 0, skip => 0); my @row_html = (); - calculate_method_daily_and_total_numbers(\%row_ctl, $payments_ref, \%stats, \@row_html, $payid_refunds_ref); - calculate_item_numbers($payitems_ref, \%stats, $piid_refunds_ref, - $piid_dates_ref, $payments_ref) - if $payments_ref; + # Iterate over payments + foreach my $row (sort { $a->{payid} <=> $b->{payid} } values %{ $payments_ref || {} }) { + # Determine if the hash key is labelled as positive or negative + my $ctkey = $row->{amount} >= 0 ? 'cnt_pos' : 'cnt_neg'; + my $amtkey = $row->{amount} >= 0 ? 'amt_pos' : 'amt_neg'; + + # Get the date and refund amount + my $date = substr($row->{daterecv}, 0, 10); + my $refund = $payid_refunds_ref->{$row->{payid}}; + my $tokens = $row->{method} eq 'wallet' ? $row->{amount} : 0; + + # Update the overall stats, as well as the daily stats + foreach my $s (\%stats, $stats{daily}->{$date}) { + # Overall/Daily Stats + $s->{$ctkey}++; + $s->{amount} += $row->{amount}; + $s->{$amtkey} += $row->{amount}; + $s->{refund} += $refund; + $s->{tokens} += $tokens; + + # Method Breakout Stats for HTML output + $s->{method}->{$row->{method}}->{$ctkey}++; + $s->{method}->{$row->{method}}->{$amtkey} += $row->{amount}; + $s->{method}->{$row->{method}}->{amount} += $row->{amount}; + $s->{method}->{$row->{method}}->{tokens} += $tokens; + $s->{method}->{$row->{method}}->{refund} += $refund if $refund; + } + + # Go to the next payment if we're doing a CSV dump + next unless $view_mode eq 'html'; + + # Show $PAGE_SIZE payment summary rows for this time period + $row_ctl{count}++; + next if $GET{skip} && ++$row_ctl{skip} <= $GET{skip}; + + # If we've exceeded the requested page size, stop appending rows + next unless $row_ctl{show} < $PAGE_SIZE; + + # Build a row to display + $row_ctl{show}++; + push @row_html, [ + # userid in need of username + $row->{userid}, + + # pre_html + "<tr valign='top'>" . + "<td align='center'><a href=\"paiddetails.bml?payid=$row->{payid}\">#$row->{payid}</a></td>" . + "<td><b><a href=\"/userinfo.bml?user=", + + # mid_html + "\">", + + # post_html + "</a></b></td>" . + "<td>$row->{datesent}<br />$row->{daterecv}</td>" . + "<td align='right'>" . money($row->{amount}) . "</td>" . + "<td align='center'>$row->{used}/$row->{mailed}</td>" . + "<td>$row->{method}</td>" . + "<td>$row->{forwhat}</td>" . + "</tr>" + ]; + } + + foreach my $it (values %{ $payitems_ref || {} }) { + + my $refund = $piid_refunds_ref->{$it->{piid}}; + my $tokens = $payments_ref->{$it->{payid}}->{method} eq 'wallet'; + + # find item and subitem key strings + my $item = $it->{item}; + my $subkey; + if ($item eq 'clothes') { + $subkey = $it->{subitem}; + } + elsif ($item eq 'coupon') { + $subkey = $it->{amt}; + } + else { + $subkey = ( UNIVERSAL::isa($it, 'LJ::Pay::Payment::PayItem::Addon::Sized') + ? ((split('-', $it->{subitem}))[0] . '-') + : '' ) . + ( $it->{qty} || 0 ); + } + + # $subkey = 'zero' if $subkey == 0; + + my $ctkey = $it->{amt} >= 0 ? 'cnt_pos' : 'cnt_neg'; + my $amtkey = $it->{amt} >= 0 ? 'amt_pos' : 'amt_neg'; + my $date = substr($piid_dates_ref->{$it->{piid}}, 0,10); + + # Update Daily stats + foreach my $s ($stats{daily}->{$date}->{item}->{$item}) { + foreach my $ssub ($s, $s->{sub}->{$subkey}) { + $ssub->{$ctkey}++; + $ssub->{$amtkey} += $it->{amt}; + $ssub->{amount} += $it->{amt}; + $ssub->{tokens} += $it->{amt} if $tokens; + $ssub->{refund} += $refund if $refund; + } + } + + # Update Item stats + my $forwhat = $payments_ref->{$it->{payid}}->{forwhat}; + my $recur = $forwhat eq 'recbill' ? 'Recurring' : 'Non-recurring'; + foreach my $s ($stats{item}->{$item}) { + foreach my $ssub ($s, $s->{sub}->{$subkey}) { + foreach my $recurring ($recur, 'Total') { + $ssub->{$recurring}->{$ctkey} ++; + $ssub->{$recurring}->{$amtkey} += $it->{amt}; + $ssub->{$recurring}->{amount} += $it->{amt}; + $ssub->{$recurring}->{tokens} += $it->{amt} if $tokens; + $ssub->{$recurring}->{refund} += $refund if $refund; + } + } + } + } # $it + + if ($view_mode eq 'csv') { # Handle CSV output (which outputs different content than HTML output) handle_csv(\%stats, $year, $month, $day); @@ -315,265 +546,6 @@ } }; - sub get_dates { - # default values for year/month/day - my $year = $GET{year}+0; - my $month = $GET{month}+0; - my $day = $GET{day} eq '*' ? '*' : $GET{day}+0; - - unless ($year && $month) { - my @time = localtime(); - $year = $time[5]+1900; - $month = $time[4]+1; - $day = $time[3]; - } - - # find low/high dates so we can do an indexed query from payments - my ($date_low, $date_high); - - my $fmt = sub { $dbh->quote(sprintf("%04d-%02d-%02d 00:00:00", @_)) }; - - # find beginning/end times for this pay period - if ($day eq '*') { - $date_low = $fmt->($year, $month, '01'); - if ($month+1 > 12) { - $date_high = $fmt->($year+1, 1, '01'); - } else { - $date_high = $fmt->($year, $month+1, '01'); - } - } else { - $date_low = $fmt->($year, $month, $day); - if ($day+1 > LJ::TimeUtil->days_in_month($month, $year)) { - if ($month+1 > 12) { - $date_high = $fmt->($year+1, 1, '01'); - } else { - $date_high = $fmt->($year, $month+1, '01'); - } - } else { - $date_high = $fmt->($year, $month, $day+1); - } - } - - return ($year, $month, $day, $date_low, $date_high); - } - - sub get_payments { - my ($date_low, $date_high, $method, $users) = @_; - - $users = 'all' unless $users; - - my $method_sql; - if ($method && $method ne '') { - $method_sql = "AND method='$method' "; - } else { - $method_sql = "AND method<>'free' "; - } - - my $anon_sql; - if ($users eq 'anon') { - $anon_sql = "AND userid = 0 "; - } - - # Get all the payments for the billing period - $sth = $dbh->prepare("SELECT * FROM payments WHERE used='Y' " . - $method_sql . - $anon_sql . - "AND daterecv>$date_low AND daterecv<$date_high"); - $sth->execute; - $sth->{mysql_use_result} = 1; - my %payids; - my @users; - while (my $row = $sth->fetchrow_hashref) { - $payids{$row->{payid}} = $row; - push @users, $row->{userid} if $users ne 'all' and $users ne 'anon'; - } - - return \%payids if $users eq 'all' or $users eq 'anon'; - - my $us = LJ::load_userids(@users); - - foreach my $payid (keys %payids) { - my $userid = $payids{$payid}->{userid}; - delete $payids{$payid} - if not $us->{$userid} - or $users eq 'cyr' and not LJ::SUP->is_sup_enabled($us->{$userid}) - or $users eq 'non-cyr' and LJ::SUP->is_sup_enabled($us->{$userid}); - } - - return \%payids; - } - - sub get_payitems { - my $payments_ref = shift; - return ({}, {}, {}) unless $payments_ref && scalar(keys %$payments_ref); - my $sql = 'SELECT * FROM payitems WHERE payid IN (' . join(',', keys %$payments_ref) . ')'; - $sth = $dbh->prepare($sql); - $sth->execute; - $sth->{mysql_use_result} = 1; - die $dbh->errstr if $dbh->err; - - my %piids; - my %payid_piids; # Map payids to their piids - my %piid_dates; # Map piids to their payment's date - while (my $row = $sth->fetchrow_hashref) { - my $payid = $row->{payid}; - my $piid = $row->{piid}; - #$piids{$piid} = $row; -- old shop version - $piids{$piid} = LJ::Pay::Payment::PayItem->new_memonly(%$row); - $piid_dates{$piid} = $payments_ref->{$payid}->{daterecv}; - $payid_piids{$payid} = [] unless exists $payid_piids{$payid}; - push @{ $payid_piids{$payid} }, $piid; - } - - return \%piids, \%payid_piids, \%piid_dates; - } - - sub get_refund_amounts { - my ($payments_ref, $payitems_ref, $payid_piids_ref) = @_; - - # Get the refund amount for each payment - my (%payid_refunds, %piid_refunds); - foreach my $item (values %{$payitems_ref||{}}) { - if ($item->{status} eq 'refund') { - $piid_refunds{$item->{piid}} = -$item->{amt}; - $payid_refunds{$item->{payid}} += -$item->{amt}; - } - } - - return(\%payid_refunds, \%piid_refunds); - } - - sub calculate_method_daily_and_total_numbers { - my ($row_ctl_ptr, $payments_ref, $stats_ref, $row_html_ref, $payid_refunds_ref) = @_; - - # Iterate over payments - foreach my $row (sort { $a->{payid} <=> $b->{payid} } values %{$payments_ref||{}}) { - - # Determine if the hash key is labelled as positive or negative - my $ctkey = $row->{amount} >= 0 ? 'cnt_pos' : 'cnt_neg'; - my $amtkey = $row->{amount} >= 0 ? 'amt_pos' : 'amt_neg'; - - # Get the date and refund amount - my $date = substr($row->{daterecv}, 0, 10); - my $refund = $payid_refunds_ref->{$row->{payid}}; - my $tokens = $row->{method} eq 'wallet' ? $row->{amount} : 0; - - # Update the overall stats, as well as the daily stats - foreach my $stats ($stats_ref, $stats_ref->{daily}->{$date}) { - # Overall/Daily Stats - $stats->{$ctkey}++; - $stats->{amount} += $row->{amount}; - $stats->{$amtkey} += $row->{amount}; - $stats->{refund} += $refund; - $stats->{tokens} += $tokens; - - # Method Breakout Stats for HTML output - $stats->{method}->{$row->{method}}->{$ctkey}++; - $stats->{method}->{$row->{method}}->{$amtkey} += $row->{amount}; - $stats->{method}->{$row->{method}}->{amount} += $row->{amount}; - $stats->{method}->{$row->{method}}->{tokens} += $tokens; - $stats->{method}->{$row->{method}}->{refund} += $refund if $refund; - } - - # Go to the next payment if we're doing a CSV dump - next unless $view_mode eq 'html'; - - # Show $PAGE_SIZE payment summary rows for this time period - $row_ctl_ptr->{count}++; - next if $GET{skip} && ++$row_ctl_ptr->{skip} <= $GET{skip}; - - # If we've exceeded the requested page size, stop appending rows - next unless $row_ctl_ptr->{show} < $PAGE_SIZE; - - # Build a row to display - $row_ctl_ptr->{show}++; - push @$row_html_ref, [ - # userid in need of username - $row->{userid}, - - # pre_html - "<tr valign='top'>" . - "<td align='center'><a href=\"paiddetails.bml?payid=$row->{payid}\">#$row->{payid}</a></td>" . - "<td><b><a href=\"/userinfo.bml?user=", - - # mid_html - "\">", - - # post_html - "</a></b></td>" . - "<td>$row->{datesent}<br />$row->{daterecv}</td>" . - "<td align='right'>" . money($row->{amount}) . "</td>" . - "<td align='center'>$row->{used}/$row->{mailed}</td>" . - "<td>$row->{method}</td>" . - "<td>$row->{forwhat}</td>" . - "</tr>" - ]; - } - } - - sub calculate_item_numbers { - my ($payitems_ref, $stats_ref, $piid_refunds_ref, $piid_dates_ref, - $payments_ref) = @_; - - foreach my $it (values %{$payitems_ref||{}}) { - - my $refund = $piid_refunds_ref->{$it->{piid}}; - my $tokens = $payments_ref->{$it->{payid}}->{method} eq 'wallet'; - - # find item and subitem key strings - my $item = $it->{item}; - my $subkey = - ($item eq 'clothes' - ? $it->{subitem} - : ($item eq 'coupon' - ? $it->{amt} - : ( - ( UNIVERSAL::isa($it, 'LJ::Pay::Payment::PayItem::Addon::Sized') - ? ((split('-', $it->{subitem}))[0] . '-') - : '' - ) . - ( $it->{qty} - ? $it->{qty} - : 0 - ) - ) - ) - ); - $subkey = 'zero' if ($it->{qty} == 0 && ($item eq 'paidacct' || $item eq 'userpic')); - - my $ctkey = $it->{amt} >= 0 ? 'cnt_pos' : 'cnt_neg'; - my $amtkey = $it->{amt} >= 0 ? 'amt_pos' : 'amt_neg'; - my $date = substr($piid_dates_ref->{$it->{piid}}, 0,10); - - # Update Daily stats - foreach my $s ($stats_ref->{daily}->{$date}->{item}->{$item}) { - foreach my $ssub ($s, ($subkey ? $s->{sub}->{$subkey} : ())) { - $ssub->{$ctkey}++; - $ssub->{$amtkey} += $it->{amt}; - $ssub->{amount} += $it->{amt}; - $ssub->{tokens} += $it->{amt} if $tokens; - $ssub->{refund} += $refund if $refund; - } # ssub - } # $s - - # Update Item stats - my $forwhat = $payments_ref->{$it->{payid}}->{forwhat}; - my $recur = $forwhat eq 'recbill' ? 'Recurring' : 'Non-recurring'; - foreach my $s ($stats_ref->{item}->{$item}) { - foreach my $ssub ($s, ($subkey ? $s->{sub}->{$subkey} : ())) { - foreach my $recurring ($recur, 'Total') { - $ssub->{$recurring}->{$ctkey} ++; - $ssub->{$recurring}->{$amtkey} += $it->{amt}; - $ssub->{$recurring}->{amount} += $it->{amt}; - $ssub->{$recurring}->{tokens} += $it->{amt} if $tokens; - $ssub->{$recurring}->{refund} += $refund if $refund; - } # recurring - } # ssub - } # $s - - } # $it - } # subroutine - sub handle_csv { my ($stats_ref, $year, $month, $day) = @_; @@ -646,7 +618,7 @@ (",", $day, - $stats{amount}, + $stats_ref->{amount}, $dayref->{cnt_pos} + $dayref->{cnt_neg}, # payment methods @@ -834,85 +806,54 @@ return $num; } - #my %stats = ( - # amount, - # cnt_pos, - # cnt_neg, - # refund, - # daily => { - # 2005-01-01 => { - # cnt_pos, - # cnt_neg, - # amt_pos, - # amt_neg, - # refund, - # } - # .... - # method => { - # cash => { - # cnt_pos, - # cnt_neg, - # amt_pos, - # amt_neg, - # } - # ... - # }, - # item => { - # paidacct => { - # cnt_pos, - # cnt_neg, - # amt_pos, - # amt_neg, - # refund, - # sub => { - # 12 => { - # cnt_pos, - # cnt_neg, - # amt_pos, - # amt_neg, - # refund, - # }, - # }, - # ... - # diskquota => { - # cnt_pos, - # cnt_neg, - # amt_pos, - # amt_neg, - # refund, - # sub => { - # 1024-12 => { - # cnt_pos, - # cnt_neg, - # amt_pos, - # amt_neg, - # }, - # }, - # ... - # rename => { - # cnt_pos, - # cnt_neg, - # amt_pos, - # amt_neg, - # refund, - # }, - # ... - # clothes => { - # cnt_pos, - # cnt_neg, - # amt_pos, - # amt_neg, - # refund, - # sub => { - # mosaicposter-white-os => { - # cnt_pos, - # cnt_neg, - # amt_pos, - # amt_neg, - # refund, - # }, - # }, - # }, - # }; + # my %stats = ( + # amount, + # cnt_pos, + # cnt_neg, + # refund, + # daily => { + # 2005-01-01 => { + # cnt_pos, cnt_neg, amt_pos, amt_neg, refund, + # }, + # ... + # }, + # method => { + # cash => { + # cnt_pos, cnt_neg, amt_pos, amt_neg, + # }, + # ... + # }, + # item => { + # paidacct => { + # cnt_pos, cnt_neg, amt_pos, amt_neg, refund, + # sub => { + # 12 => { + # cnt_pos, cnt_neg, amt_pos, amt_neg, refund, + # }, + # ... + # }, + # }, + # diskquota => { + # cnt_pos, cnt_neg, amt_pos, amt_neg, refund, + # sub => { + # 1024-12 => { + # cnt_pos, cnt_neg, amt_pos, amt_neg, + # }, + # ... + # }, + # }, + # rename => { + # cnt_pos, cnt_neg, amt_pos, amt_neg, refund, + # }, + # clothes => { + # cnt_pos, cnt_neg, amt_pos, amt_neg, refund, + # sub => { + # mosaicposter-white-os => { + # cnt_pos, cnt_neg, amt_pos, amt_neg, refund, + # }, + # }, + # }, + # }, + # }; } _code?>