[Classiccity-pm] sort example

Keith Ledford keith-classiccitypm at ledfords.us
Wed May 19 08:44:16 CDT 2004


Hello all!

The list has been quite lately so I thought I would post a script I
wrote last week. 

At work we keep all of our customer data in a postgresql database. The
tables that are used in the script are:
accounts
accountdivisions
accountsites
site_pm_audit

As you can tell we have accounts that have divisions that have
sites. Each site is assigned a program manager. We are starting to
track the "state" of the site with a red/yellow/green status
indicator. That status is stored in the site_pm_audit table, along
with a timestamp and some notes. This is status is a big help for our
Network Operations Center as they can quickly tell the mood of the
site.

As with all data, management wanted a report via email each week that
would list the information for each site. They also like the reports
to be in MS Excel with summary data at the top of the spreadsheet.

So far this is a very basic perl script that will use a couple of perl
modules. It didn't take long to come up with the first version which
sent a spreadsheet that had the summary and then the sites listed
below sorted by the program manager, account, division, site, reverse
sort on status.

I am doing a selectall_hashref to get the data from our database. The
data being returned is in a data structure like:

%result_set => { site_id => {
				program_manager => "Some Name"
				account		=> "Some Account"
				division	=> "Some Division"
				site		=> "Some Site"
				current_status	=> "Status"
				last_update	=> "Time of update"
				notes		=> "Some Notes"
				}
		}


Management liked the first version, but of course had some changes to
make. They wanted the detail data to be sorted in decreasing status
order. This is where it started to get interesting. We are storing the
text of the status in the site_pm_audit table so I could not sort via
reverse alpha (yellow would be before red). I also did not want to
make any database changes because that would also mean UI changes. 

So I started hunting around and found a snippet of code that sparked
an idea. My sort was done by using

for my $curr_row ( sort { $$a{program_manager} cmp $$b{program_manager} 
                          || $$a{account} cmp $$b{account} 
		          || $$a{division} cmp $$b{division}
			  || $$a{site} cmp $$b{site}
			  || $$b{current_status} cmp $$a{current_status}
			} values %$result_set) {

but I needed to edit the way that 
$$b{current_status} cmp $$a{current_status} 
was returning the data. The end result is:

for my $curr_row ( sort { do {if ( $$a{current_status} eq 'Red' && $$b{current_status} ne 'Red') {
                                 -1;
			       } elsif ($$b{current_status} eq 'Red' && $$a{current_status} ne 'Red') {
  			         1;
			       } else {
				 $$b{current_status} cmp $$a{current_status}
			       }
			     }
			  || $$a{program_manager} cmp $$b{program_manager} 
                          || $$a{account} cmp $$b{account} 
		          || $$a{division} cmp $$b{division}
			  || $$a{site} cmp $$b{site}
			} values %$result_set) {

I am sure Mark or Darrell will respond with some other way that is
more efficient but, I this works and looks like typical perl.

I have attached a copy of the script for you to look over. If you have
any suggestions or comments let me know.

Enjoy!

(** Mike Rylander helped **) 


-- 
Keith Ledford
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Site_Status_Report.pl
Type: application/x-perl
Size: 4576 bytes
Desc: not available
Url : http://mail.pm.org/pipermail/classiccity-pm/attachments/20040519/7f1c83e9/Site_Status_Report.bin


More information about the Classiccity-pm mailing list