By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,848 Members | 1,359 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,848 IT Pros & Developers. It's quick & easy.

Grouping report by financial year

P: 2
I have a script which prints extracts from a database to produce an HTML report by month, grouped by calendar year. The relevant part of the script is below. I now want to adapt the script to group by financial year eg 2007/08 March to April inclusive. Can anyone suggest how I could adapt the script, maybe be creating a new variable 'finyear'?

Expand|Select|Wrap|Line Numbers
  1. sub report_5 () {
  2.     my ($title) = @_;
  3.     output_title($title,5);
  4.  
  5.     output_heading ('Month', '| Sales', ' | Sales value', ' | Renewals', ' | Renewals value', ' | Total', ' | Total value');
  6.  
  7.     my $query = "select 
  8.             SUM(IF(ord.order_prod_code = 'SUB1YR', 1,     0))     as sales_count, 
  9.             SUM(IF(ord.order_prod_code = 'SUB1YR', amt,     0))     as sales_amt, 
  10.             SUM(IF(ord.order_prod_code = 'SUB1YR', 0,     1))     as renewals_count, 
  11.             SUM(IF(ord.order_prod_code = 'SUB1YR', 0,     amt))     as renewals_amt, 
  12.             DATE_FORMAT(pay.date, \"%M% %Y\") as month, 
  13.             DATE_FORMAT(pay.date, \"%Y\") as year
  14.     from 
  15.             $main::UsersTable         as users,
  16.             $main::SubscriptionTable     as subs,
  17.             $main::CustTable         as cust,
  18.             $main::TxnTable         as txn,
  19.             $main::OrderTable         as ord,
  20.             $main::PaymentTable         as pay
  21.             where users.user_id     = subs.user_id
  22.             and   pay.paymkey     = subs.payment_id                and   pay.txn_id     = txn.txnkey
  23.             and   ord.order_txn_id     = txn.txnkey
  24.             and   cust.custkey     = txn.txn_cust_id
  25.             and   pay.status = 'AUTH'                    group by DATE_FORMAT(pay.date, \"%Y\"), DATE_FORMAT(pay.date, \"%M% %Y\")
  26.             order by pay.date desc
  27.             ";
##etc
Jan 3 '08 #1
Share this Question
Share on Google+
2 Replies


eWish
Expert 100+
P: 971
From the looks of the code supplied the sub routine is only taking 1 argument which does not effect the query itself. Where is the code that determines the date you want to select between?

--Kevin
Jan 3 '08 #2

P: 2
From the looks of the code supplied the sub routine is only taking 1 argument which does not effect the query itself. Where is the code that determines the date you want to select between?

--Kevin
As I really don't understand Perl code, perhaps it's easier if I copy the whole code:

Expand|Select|Wrap|Line Numbers
  1. #!/usr/bin/perl
  2.  
  3. BEGIN {
  4.         require 'config.pl';
  5.         require 'akinit.pl';
  6. }
  7. require 'login_users.pl';
  8. require 'logout.pl';
  9. require 'dbconnect.pl';
  10. require 'modem.pl';
  11. require 'misc.pl';
  12. use Audit;
  13.  
  14.  
  15. $HtmlStart = read_html('login.htm');
  16.  
  17. use DBI;
  18. use CGI;
  19. use CGI::Carp qw(fatalsToBrowser);
  20.  
  21.  
  22. use Login;
  23.  
  24. &DBconnect;
  25.  
  26. # Initialise a few variables
  27. $query_CGI = new CGI;
  28. @param_CGI=$query_CGI->param();
  29. my $countDel;
  30. $Nbr_Parms=$#param_CGI+1;
  31.  
  32. $html_format = $query_CGI->param('html_format');
  33. $password = $query_CGI->param('password');
  34.  
  35. if ($html_format) {
  36.     print "content-Type: text/html\n\n";
  37.     print '<html><body><nobr>';
  38. }
  39. else {
  40.     print "content-Type: text/plain\n\n";
  41.     print "Use CTRL+F in your browser to find text, File->Save As to save a copy to import into Works\n";
  42.     print "e.g. save to a file called transactions.csv\n";
  43. }
  44.  
  45.  
  46. if ($html_format) {
  47. }
  48. report_5('Net monthly sales totals');
  49.  
  50. if ($html_format) {
  51.     print '</nobr></body></html>';
  52. }
  53.  
  54.  
  55.  
  56.  
  57. sub report_5 () {
  58.     my ($title) = @_;
  59.     output_title($title,5);
  60.  
  61.     output_heading ('Month', '| Sales', ' | Sales value', ' | Renewals', ' | Renewals value', ' | Total', ' | Total value');
  62.  
  63.     my $query = "select 
  64.             SUM(IF(ord.order_prod_code = 'SUB1YR', 1,     0))     as sales_count, 
  65.             SUM(IF(ord.order_prod_code = 'SUB1YR', amt,     0))     as sales_amt, 
  66.             SUM(IF(ord.order_prod_code = 'SUB1YR', 0,     1))     as renewals_count, 
  67.             SUM(IF(ord.order_prod_code = 'SUB1YR', 0,     amt))     as renewals_amt, 
  68.             DATE_FORMAT(pay.date, \"%M% %Y\") as month, 
  69.             DATE_FORMAT(pay.date, \"%Y\") as year
  70.     from 
  71.             $main::UsersTable         as users,
  72.             $main::SubscriptionTable     as subs,
  73.             $main::CustTable         as cust,
  74.             $main::TxnTable         as txn,
  75.             $main::OrderTable         as ord,
  76.             $main::PaymentTable         as pay
  77.             where users.user_id     = subs.user_id
  78.             and   pay.paymkey     = subs.payment_id                and   pay.txn_id     = txn.txnkey
  79.             and   ord.order_txn_id     = txn.txnkey
  80.             and   cust.custkey     = txn.txn_cust_id
  81.             and   pay.status = 'AUTH'                    group by DATE_FORMAT(pay.date, \"%Y\"), DATE_FORMAT(pay.date, \"%M% %Y\")
  82.             order by pay.date desc
  83.             ";
  84.  
  85.  
  86.  
  87.     my $sth = $dbh->prepare($query);
  88.     $sth->execute();
  89.  
  90.     $country_count = 0;
  91.     $country_sales = 0;
  92.     $country = '';
  93.     $year = '';
  94.  
  95.     while (my $hash = $sth->fetchrow_hashref()) {
  96.         $month = $hash->{month};
  97.         $amt = $hash->{amt};
  98.         $count = $hash->{count};
  99.  
  100.  
  101.         if (($year ne $hash->{year}) and ($year ne '')) {
  102.             output_bold_record("Total for $year",$y_sales_count_total, $y_sales_amt_total,$y_renewals_count_total,$y_renewals_amt_total,$y_all_count_total,$y_all_amt_total);
  103.             if ($html_format) {
  104.                 print '<tr height=10><td></td></tr>';
  105.             }
  106.             $y_sales_count_total     = 0;
  107.             $y_sales_amt_total     = 0;
  108.             $y_renewals_count_total = 0;
  109.             $y_renewals_amt_total     = 0;
  110.             $y_all_count_total     = 0;
  111.             $y_all_amt_total     = 0;
  112.         }
  113.  
  114.         $year = $hash->{year};
  115.  
  116.  
  117.         output_record(
  118.             $month,
  119.             $hash->{sales_count},
  120.             $hash->{sales_amt},
  121.             $hash->{renewals_count},
  122.             $hash->{renewals_amt},
  123.             $hash->{sales_count} + $hash->{renewals_count},
  124.             $hash->{sales_amt} + $hash->{renewals_amt}
  125.         );
  126.         $sales_count_total     += $hash->{sales_count};
  127.         $sales_amt_total     += $hash->{sales_amt};
  128.         $renewals_count_total     += $hash->{renewals_count};
  129.         $renewals_amt_total     += $hash->{renewals_amt};
  130.         $all_count_total     += $hash->{sales_count} + $hash->{renewals_count};
  131.         $all_amt_total         += $hash->{sales_amt} + $hash->{renewals_amt};
  132.  
  133.  
  134.  
  135.         $y_sales_count_total     += $hash->{sales_count};
  136.         $y_sales_amt_total     += $hash->{sales_amt};
  137.         $y_renewals_count_total += $hash->{renewals_count};
  138.         $y_renewals_amt_total     += $hash->{renewals_amt};
  139.         $y_all_count_total     += $hash->{sales_count} + $hash->{renewals_count};
  140.         $y_all_amt_total     += $hash->{sales_amt} + $hash->{renewals_amt};
  141.     }
  142.  
  143.     output_bold_record("Total for $year",$y_sales_count_total, $y_sales_amt_total,$y_renewals_count_total,$y_renewals_amt_total,$y_all_count_total,$y_all_amt_total);
  144.     if ($html_format) {
  145.         print '<tr height=10><td></td></tr>';
  146.     }
  147.     output_bold_record('Total',$sales_count_total, $sales_amt_total,$renewals_count_total,$renewals_amt_total,$all_count_total,$all_amt_total);
  148.  
  149.     output_footer();
  150. }
  151.  
  152.  
  153.  
  154. sub get_current_status () {
  155.     my ($dbh, $user_id) = @_;
  156.  
  157.     $Query="SELECT first_name, last_name FROM $UsersTable as u , $SubscriptionTable as s WHERE u.user_id =?
  158.             AND NOW() >= s.start_date
  159.             AND NOW() <= s.end_date
  160.         AND u.user_id = s.user_id";
  161.  
  162.     $sth_user = $dbh->prepare($Query)
  163.         ||print "AK572 could not select $Query $!\n $DBI::errstr";
  164.     $sth_user->execute($user_id)
  165.         ||print "AK573 could not select $Query $!\n $DBI::errstr";
  166.  
  167.     if ($sth_user->rows) {
  168.         return 'LIVE';
  169.     }
  170.     else {
  171.         return 'LAPSED';
  172.     }
  173. }
  174.  
  175.  
  176. sub superseded () {
  177.  
  178.     #
  179.     # See if there are any subscriptions for the user that end after the date provided
  180.     #
  181.  
  182.     my ($dbh, $user_id, $end_date) = @_;
  183.  
  184.     $Query="SELECT * FROM $UsersTable as u , $SubscriptionTable as s WHERE u.user_id =?
  185.             AND s.end_date > ?
  186.         AND u.user_id = s.user_id";
  187.  
  188.     $sth_user = $dbh->prepare($Query)
  189.         ||print "AK574 could not select $Query $!\n $DBI::errstr";
  190.     $sth_user->execute($user_id, $end_date)
  191.         ||print "AK575 could not select $Query $!\n $DBI::errstr";
  192.  
  193.     if ($sth_user->rows) {
  194.         return 1;
  195.     }
  196.     else {
  197.         return 0;
  198.     }
  199. }
  200.  
  201.  
  202. sub output_title () {
  203.     my ($title, $name) = @_;
  204.  
  205.     if ($html_format) {
  206.         print "<a name=$name>";
  207.         print "<p><H2>$title </H2>\n";
  208.     }
  209.     else {
  210.         print "\n$title \n";
  211.     }
  212. }
  213.  
  214.  
  215. sub output_sub_total () {
  216.     if ($html_format) {
  217.         print "<tr><td colspan=$no_fields><nobr><b>";
  218.         print join(' ', @_);
  219.         print '</b></nobr></td></tr>' . "\n";
  220.     }
  221.     else {
  222.         print join(' ', @_) . "\n";
  223.     }
  224. }
  225.  
  226. sub output_record () {
  227.     if ($html_format) {
  228.             print '<tr><td><nobr>';
  229.             print join('</nobr></td><td align=right><nobr>', @_);
  230.             print '</nobr></td></tr>' . "\n";
  231.     }
  232.     else {
  233.         print join(',', @_) . "\n";
  234.     }
  235. }
  236.  
  237. sub output_bold_record () {
  238.     if ($html_format) {
  239.             print '<tr><td><b><nobr>';
  240.             print join('</nobr></b></td><td align=right><b><nobr>', @_);
  241.             print '</nobr></b></td></tr>' . "\n";
  242.     }
  243.     else {
  244.         print join(',', @_) . "\n";
  245.     }
  246. }
  247. sub output_heading () {
  248.     $no_fields = scalar(@_);
  249.     if ($html_format) {
  250.         print '<table>';
  251.         print '<tr><td><nobr><b>';
  252.         print join('</b></nobr></td><td><nobr><b>', @_);
  253.         print '</b></nobr></td></tr>' . "\n";
  254.     }
  255.     else {
  256.         print join(',', @_) . "\n";
  257.     }
  258. }
  259.  
  260. sub output_footer () {
  261.     if ($html_format) {
  262.         print '</table>';
  263.     }
  264.     else {
  265.         print "\n\n\n";
  266.     }
  267. }
  268.  
  269.  
Jan 4 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.