[ljcom] r11552: LJSUP-10941: Need a page to display cert...

LJSUP-10941: Need a page to display certain figures from the LiveJournal Shop

U   trunk/htdocs/admin/profit.bml

Modified: trunk/htdocs/admin/profit.bml
===================================================================
--- trunk/htdocs/admin/profit.bml	2012-03-02 09:19:39 UTC (rev 11551)
+++ trunk/htdocs/admin/profit.bml	2012-03-02 13:07:28 UTC (rev 11552)
@@ -7,21 +7,48 @@
 my $selfpromo_month = 0;
 my $selfpromo_day = 0;
 my $dbh = LJ::get_db_writer;
-my $sth = $dbh->prepare("select userid,amount from payments where daterecv > '2012-01-01' and used != 'C' and method != 'wallet';");
-$sth->execute();
-while (my $row = $sth->fetchrow_hashref) {
-    $total_sum += $row->{amount} if LJ::SUP->is_sup_enabled(LJ::load_userid($row->{userid}));
-}
-$sth = $dbh->prepare("SELECT amount,propval,userid from payments left outer join payitems on payments.payid=payitems.payid left outer join payitemprop on payitemprop.piid=payitems.piid WHERE used = 'Y' AND method = 'wallet' AND year(daterecv)=year(now()) and month(daterecv)=month(now()) and item='selfpromo' and payitemprop.propname='selfpromo_refund';");
-$sth->execute();
-while (my $row = $sth->fetchrow_hashref) {
-    $selfpromo_month += (($row->{amount})-($row->{propval}/100)) if LJ::SUP->is_sup_enabled(LJ::load_userid($row->{userid}));
-}
-$sth = $dbh->prepare("SELECT amount,propval,userid from payments left outer join payitems on payments.payid=payitems.payid left outer join payitemprop on payitemprop.piid=payitems.piid WHERE used = 'Y' AND method = 'wallet' AND year(daterecv)=year(now()) and month(daterecv)=month(now()) and item='selfpromo' and payitemprop.propname='selfpromo_refund' and day(payments.daterecv)=day(now());");
-$sth->execute();
-while (my $row = $sth->fetchrow_hashref) {
-    $selfpromo_day += (($row->{amount})-($row->{propval}/100)) if LJ::SUP->is_sup_enabled(LJ::load_userid($row->{userid}));
-}
+
+my $total_sum = $dbh->selectrow_array("
+    SELECT SUM(amount) 
+    FROM payments 
+    JOIN user USING(userid)
+    WHERE daterecv > '2012-01-01' 
+      AND used != 'C'
+      AND method != 'wallet'
+      AND caps & 0xc00 = 0x400
+");
+
+my $selfpromo_month = $dbh->selectrow_array("
+    SELECT SUM(amount - propval / 100)
+    FROM payments 
+    LEFT OUTER JOIN payitems ON payments.payid = payitems.payid 
+    LEFT OUTER JOIN payitemprop ON payitemprop.piid = payitems.piid 
+    JOIN user USING(userid)
+    WHERE used = 'Y' 
+      AND method = 'wallet' 
+      AND year(daterecv) = year(now()) 
+      AND month(daterecv) = month(now())
+      AND item='selfpromo'
+      AND payitemprop.propname = 'selfpromo_refund'
+      AND caps & 0xc00 = 0x400
+");
+
+my $selfpromo_day = $dbh->selectrow_array("
+    SELECT SUM(amount - propval / 100)
+    FROM payments 
+    LEFT OUTER JOIN payitems ON payments.payid = payitems.payid 
+    LEFT OUTER JOIN payitemprop ON payitemprop.piid = payitems.piid 
+    JOIN user USING(userid)
+    WHERE used = 'Y' 
+      AND method = 'wallet' 
+      AND year(daterecv) = year(now()) 
+      AND month(daterecv) = month(now())
+      AND item='selfpromo'
+      AND payitemprop.propname = 'selfpromo_refund' 
+      AND day(payments.daterecv) = day(now())
+      AND caps & 0xc00 = 0x400
+");
+
 return int($total_sum) . "," . 
 int($selfpromo_month*LJ::Pay::Wallet::EXCHANGE_RATE) . "," . 
 int($selfpromo_day*LJ::Pay::Wallet::EXCHANGE_RATE);