<pre name="code" class="plain">yoliu@mnsdev13~/svn/WWW/content/gc/gdes/cgi-bin$ vim asset.pl
1 use strict;
2 use warnings;
3 use CGI;
4 use URI;
5 use Data::Dumper qw(Dumper);
6 use Log::Dispatch::File;
7 use POSIX qw(strftime);
8 use File::Basename;
9 use FixInc::Logger;
10 use FixInc::DBAccess;
11 use CGI::Carp qw(fatalsToBrowser);
12 use Smart::Comments;
13 use Smart::Comments;
14 my $q = new CGI;
15 my $srv = $q->param('searchServer');
16 my $name_fl = $q->param('nameFl');
17
18 my $dbh = FixInc::DBAccess->connect(
19 server => $srv,
20 database => 'dmo_govcorp',
21 log => FixInc::Logger->new(),
22 login => 'READONLY',
23 ) or die "Cannot connect to $srv: $FixInc::DBAccess::errstr";
24 my $searchSQL = "select top 10 party.party_id,count(*) from party,asset where asset.issuer_id = party.party_id group by party.party_id order by count(*) desc";
25
26 # my @resJson;
27
28 my $sth = $dbh -> selectall_arrayref ($searchSQL);
29
30 my $tmp;
31 foreach $tmp (@{$sth})
32
33 { print join(',', @$tmp), "\n";
34 }
35
"asset.pl" 36L, 1044C written
yoliu@mnsdev13~/svn/WWW/content/gc/gdes/cgi-bin$ perl5 asset.pl
0x000019000050632a,88558
0x0002a0000f8cd7f6,78217
0x0000fa0004a4e2b8,44813
0x0002a0005ba0f5ac,40806
0x000019000050662f,33836
0x0003f90049bc1619,33146
0x00003400008fb70c,32811
0x000019000050622b,30803
0x000019000050642d,30561
0x0002460001a6a84b,29253
yoliu@mnsdev13~/svn/WWW/content/gc/gdes/cgi-bin$
1 use strict;
2 use warnings;
3 use CGI;
4 use URI;
5 use Data::Dumper qw(Dumper);
6 use Log::Dispatch::File;
7 use POSIX qw(strftime);
8 use File::Basename;
9 use FixInc::Logger;
10 use FixInc::DBAccess;
11 use CGI::Carp qw(fatalsToBrowser);
12 use Smart::Comments;
13 use Smart::Comments;
14 use Time::Local;
15 my $q = new CGI;
16 my $srv = $q->param('searchServer');
17 my $name_fl = $q->param('nameFl');
18 open(FD,"LOG.txt")||die("Can not open the file!$!n");
19
20 my %s;
21 my %e;
22 while(<FD> )
23 {
24 if( /(.*)\s*INFO.*Created changeset\s*(.*)/){
25 $s{$2} =$1;
26 }
27 if( /(.*)\s*INFO.*Closing changeset\s*(.*)/){
28 $e{$2} =$1;
29 }
30 }
31
32 foreach my $k (keys %s) {
33 next unless exists $s{$k} and exists $e{$k};
34 my $t1 = l2t($e{$k});
35 my $t2 = l2t($s{$k});
36 print "$e{$k} - $s{$k} = " .($t1-$t2);
37 }
38
39 sub l2t
40 {
41 my ($str) = @_;
42 if ($str=~/(\d{4})\/(\d{2})\/(\d{2})\s*(\d{2}):(\d{2}):(\d{2})/) {
43 #print "$1 $2 $3 $4 $5 $6\n";
44 return timelocal( $6, $5, $4, $3, $2, $1 );
45 }
46 return undef;
47 }
48
48
49
yoliu@mnsdev13~/svn/WWW/content/gc/gdes/cgi-bin$ perl5 asset.pl
2015/10/15 01:03:36 - 2015/10/15 01:03:34 = 2
yoliu@mnsdev13~/svn/WWW/content/gc/gdes/cgi-bin$
yoliu@mnsdev13~/svn/WWW/content/gc/gdes/cgi-bin$ vim asset.pl
1 use strict;
2 use warnings;
3 use CGI;
4 use URI;
5 use Data::Dumper qw(Dumper);
6 use Log::Dispatch::File;
7 use POSIX qw(strftime);
8 use File::Basename;
9 use FixInc::Logger;
10 use FixInc::DBAccess;
11 use CGI::Carp qw(fatalsToBrowser);
12 use Smart::Comments;
13 use Smart::Comments;
14 my $q = new CGI;
15 my $srv = $q->param('searchServer');
16 my $name_fl = $q->param('nameFl');
17
18 my $dbh = FixInc::DBAccess->connect(
19 server => $srv,
20 database => 'dmo_govcorp',
21 log => FixInc::Logger->new(),
22 login => 'READONLY',
23 ) or die "Cannot connect to $srv: $FixInc::DBAccess::errstr";
24 my $searchSQL = "select top 10 party.party_id,count(*) from party,asset where asset.issuer_id = party.party_id group by party.party_id order by count(*) desc";
25
26 my $sth = $dbh -> selectall_hashref ($searchSQL,['party_id']);
27 foreach my $cat ( keys %$sth ) {
28 print join(', ', sort { length($b) <=> length($a) } values %{ $sth->{$cat} }), "\n";
29 }
30
~
~
~
~
~
"asset.pl" 30L, 1070C written
yoliu@mnsdev13~/svn/WWW/content/gc/gdes/cgi-bin$ perl5 asset.pl
0x00003400008fb70c, 32812
0x000019000050622b, 30802
0x0002a0005ba0f5ac, 40852
0x000019000050642d, 30561
0x000019000050662f, 33836
0x000019000050632a, 88558
0x0003f90049bc1619, 33215
0x0002a0000f8cd7f6, 78253
0x0002460001a6a84b, 29253
0x0000fa0004a4e2b8, 44840
yoliu@mnsdev13~/svn/WWW/content/gc/gdes/cgi-bin$ vim asset.pl
1 use strict;
2 use warnings;
3 use CGI;
4 use URI;
5 use Data::Dumper qw(Dumper);
6 use Log::Dispatch::File;
7 use POSIX qw(strftime);
8 use File::Basename;
9 use FixInc::Logger;
10 use FixInc::DBAccess;
11 use CGI::Carp qw(fatalsToBrowser);
12 use Smart::Comments;
13 use Smart::Comments;
14 my $q = new CGI;
15 my $srv = $q->param('searchServer');
16 my $name_fl = $q->param('nameFl');
17
18 my $dbh = FixInc::DBAccess->connect(
19 server => $srv,
20 database => 'dmo_govcorp',
21 log => FixInc::Logger->new(),
22 login => 'READONLY',
23 ) or die "Cannot connect to $srv: $FixInc::DBAccess::errstr";
24 my $searchSQL = "select top 10 party.party_id,count(*) from party,asset where asset.issuer_id = party.party_id group by party.party_id order by count(*) desc";
25
26 # my @resJson;
27
28 my $sth = $dbh -> selectall_arrayref ($searchSQL);
29 # determine dimensions of matrix
30 my $rows = (!defined ($sth) ? 0 : scalar (@{$sth}));
31 my $cols = ($rows == 0 ? 0 : scalar (@{$sth->[0]}));
32 for (my $i = 0; $i < $rows; $i ++) # print each row
33 {
34 my $delim = "";
35 for (my $j = 0; $j < $cols; $j ++)
36 {
37 $sth -> [$i][$j] = "" if !defined ($sth -> [$i][$j]); # NULL?
38 print $delim . $sth -> [$i][$j];
39 $delim = ",";
40 }
41 print "\n";
42 }
"asset.pl" 42L, 1469C written
yoliu@mnsdev13~/svn/WWW/content/gc/gdes/cgi-bin$ perl5 asset.pl
0x000019000050632a,88558
0x0002a0000f8cd7f6,78210
0x0000fa0004a4e2b8,44800
0x0002a0005ba0f5ac,40760
0x000019000050662f,33836
0x0003f90049bc1619,33140
0x00003400008fb70c,32810
0x000019000050622b,30803
0x000019000050642d,30561
0x0002460001a6a84b,29253
yoliu@mnsdev13~/svn/WWW/content/gc/gdes/cgi-bin$
yoliu@mnsdev13~/svn/WWW/content/gc/gdes/cgi-bin$ vim asset.pl
1 use strict;
2 use warnings;
3 use CGI;
4 use URI;
5 use Data::Dumper qw(Dumper);
6 use Log::Dispatch::File;
7 use POSIX qw(strftime);
8 use File::Basename;
9 use FixInc::Logger;
10 use FixInc::DBAccess;
11 use CGI::Carp qw(fatalsToBrowser);
12 use Smart::Comments;
13 use Smart::Comments;
14 my $q = new CGI;
15 my $srv = $q->param('searchServer');
16 my $name_fl = $q->param('nameFl');
17
18 my $dbh = FixInc::DBAccess->connect(
19 server => $srv,
20 database => 'dmo_govcorp',
21 log => FixInc::Logger->new(),
22 login => 'READONLY',
23 ) or die "Cannot connect to $srv: $FixInc::DBAccess::errstr";
24 my $searchSQL = "select top 10 party.party_id,count(*) from party,asset where asset.issuer_id = party.party_id group by party.party_id order by count(*) desc";
25
26 my @resJson;
27
28
29 my $sth = $dbh->prepare($searchSQL);
30 $sth->execute();
31 while(my @row=$sth->fetchrow_array()){
32 if ($name_fl){
33 my $myrow = join "#|%",@row;
34 push @resJson,$myrow;
35 }
36 else {
37 push @resJson,@row;
38 }
39 }
40 $sth->finish();
41 $dbh->disconnect;
42 print "Content-Type:application/json\r\n\r\n";
43 my $i=0;
44
45 for(@resJson)
46 {
47 $i++;
48
49 if ($i % 2)
50 {
51 print $_."," ;
52 }
53 else
54 {
55 print "$_\n" ;
56
57 }
58 }
"asset.pl" 58L, 1663C written
yoliu@mnsdev13~/svn/WWW/content/gc/gdes/cgi-bin$ perl5 asset.pl
Content-Type:application/json
0x000019000050632a,88558
0x0002a0000f8cd7f6,78191
0x0000fa0004a4e2b8,44792
0x0002a0005ba0f5ac,40735
0x000019000050662f,33836
0x0003f90049bc1619,33107
0x00003400008fb70c,32806
0x000019000050622b,30803
0x000019000050642d,30561
0x0002460001a6a84b,29253
yoliu@mnsdev13~/svn/WWW/content/gc/gdes/cgi-bin$
别人给的提示:
把搜索结果打印出来
hash
my $sth = $dbh->prepare("SELECT * FROM foo");
$sth->execute();
while (my $ref = $sth->fetchrow_hashref()) {
print "Found a row: id = $ref->{'id'}, name = $ref->{'name'}\n";
}
$sth->finish
array
while (@row = $sth->fetchrow_array) {
$sth2->execute($row[0]);
}
1 #!/usr/local/bin/perl5
2 ##############################################################
3 ###
4 ### $Id: gc_benchmark_curver.pl 53862 2013-02-22 09:19:54Z eric.li $
5 ###
6 ##############################################################
7 use strict;
8 use warnings;
9
10 use CGI;
11 use URI;
12 use Data::Dumper qw(Dumper);
13 use Log::Dispatch::File;
14 use POSIX qw(strftime);
15 use File::Basename;
16 use FixInc::Logger;
17 use FixInc::DBAccess;
18 use CGI::Carp qw(fatalsToBrowser);
19 use Smart::Comments;
20
21 my $q = new CGI;
22
23 my $srv = $q->param('searchServer'); # no default
24 my $sKey = $q->param('searchKey');
25 my $sValue = $q->param('sValue');
26 my $name_fl = $q->param('nameFl');
27
28 my $dbh = FixInc::DBAccess->connect(
29 server => $srv,
30 database => 'dmo_rigs',
31 log => FixInc::Logger->new(),
32 login => 'READONLY',
33 ) or die "Cannot connect to $srv: $FixInc::DBAccess::errstr";
34
35 my $condition;
36 if($sKey eq 'chain_ric'){
37 if ( $sValue =~ /\%/ ) {
38 $condition = " upper(c.chain_ric) like upper('$sValue') \n";
39 } else {
40 $condition = " upper(c.chain_ric) = upper('$sValue') \n";
41 }
42 }
43 elsif ($sKey eq 'long_name'){
44 if ( $sValue =~ /\%/ ) {
45 $condition = " upper(c.short_name) like upper('$sValue') OR upper(c.long_name) like upper('$sValue')
46 OR upper(c.long_disp_name) like upper('$sValue') OR upper(c.short_disp_name) like upper('$sValue') ";
47 } else {
48 $condition = " upper(c.short_name) = upper('$sValue') OR upper(c.long_name) = upper('$sValue')
49 OR upper(c.long_disp_name) = upper('$sValue') OR upper(c.short_disp_name) = upper('$sValue') ";
50 }
51 }
52 elsif($sKey eq 'rt_ric'){
53 if ( $sValue =~ /\%/ ){
54 $condition = "exists (select 1 from points p where p.curve_id = c.curve_id and upper(rt_ric) like upper('$sValue'))\n";
55 }else{
56 $condition = "exists (select 1 from points p where p.curve_id = c.curve_id and upper(rt_ric) = upper('$sValue'))\n";
57 }
58 }elsif($sKey eq 'curve_id'){
59 $condition = "c.curve_id = $sValue\n";
60 }
61
62 my $curve_name = $name_fl ?',c.short_name,c.long_name':'';
63 my $order = $name_fl ?'c.long_name,c.short_name':'c.curve_id';
64
65 my $searchSQL = "SELECT c.curve_id $curve_name FROM dmo_rigs..curves c WHERE $condition ORDER BY $order";
66
67 my @resJson;
68
69 my $sth = $dbh->prepare($searchSQL);
70 $sth->execute();
71 while(my @row=$sth->fetchrow_array()){
72 if ($name_fl){
73 my $myrow = join "#|%",@row;
74 push @resJson,$myrow;
75 }
76 else {
77 push @resJson,@row;
78 }
79 }
80 $sth->finish();
81 $dbh->disconnect;
82 print "Content-Type:application/json\r\n\r\n";
83 print join(",",@resJson);
84
85
86 __END__
87
因为是根据SQl 写语句嘛
SQL是这样的:
1> select top 10 party.party_id,count(*) from party,assetwhere asset.issuer_id = party.party_id group by party.party_id order bycount(*) desc
2> go
party_id
------------------ -----------
0x000019000050632a 88557
0x0002a0000f8cd7f6 76602
0x0000fa0004a4e2b8 43586
0x0002a0005ba0f5ac 39669
0x000019000050662f 33835
0x00003400008fb70c 32641
0x0003f90049bc1619 31755
0x000019000050622b 30805
0x000019000050642d 30561
0x0002460001a6a84b 29252
(10 rows affected)
1>
1 use strict;
2 use warnings;
3
4 use CGI;
5 use URI;
6 use Data::Dumper qw(Dumper);
7 use Log::Dispatch::File;
8 use POSIX qw(strftime);
9 use File::Basename;
10 use FixInc::Logger;
11 use FixInc::DBAccess;
12 use CGI::Carp qw(fatalsToBrowser);
13 use Smart::Comments;
14 use Smart::Comments;
15
16 my $q = new CGI;
17 my $srv = $q->param('searchServer');
18 my $name_fl = $q->param('nameFl');
19 my $dbh = FixInc::DBAccess->connect(
20 server => $srv,
21 database => 'dmo_govcorp',
22
23 log => FixInc::Logger->new(),
24 login => 'READONLY',
25
26
27 ) or die "Cannot connect to $srv: $FixInc::DBAccess::errstr";
28 my $searchSQL = "select top 10 party.party_id,count(*) from party,asset where asset.issuer_id = party.party_id gr oup by party.party_id order by count(*) desc";
29
30
31 my @resJson;
32
33
34 my $sth = $dbh->prepare($searchSQL);
35 $sth->execute();
36 while(my @row=$sth->fetchrow_array()){
37 if ($name_fl){
38 my $myrow = join "#|%",@row;
39 push @resJson,$myrow;
40 }
41 else {
42 push @resJson,@row;
43 }
44 }
45 $sth->finish();
46 $dbh->disconnect;
47 print "Content-Type:application/json\r\n\r\n";
48 print join(",",@resJson);
运行结果:
yoliu@mnsdev13~/svn/WWW/content/gc/gdes/cgi-bin$ perl5 asset.pl
Content-Type:application/json
0x000019000050632a,88558,0x0002a0000f8cd7f6,78133,0x0000fa0004a4e2b8,44702,0x0002a0005ba0f5ac,40639,0x000019000050662f,33836,0x0003f90049bc1619,33038,0x00003400008fb70c,32795,0x000019000050622b,30803,0x000019000050642d,30561,0x0002460001a6a84b,29253
yoliu@mnsdev13~/svn/WWW/content/gc/gdes/cgi-bin$
43 my $i=0;
44
45 for(@resJson)
46 {
47 $i++;
48
49 if ($i % 2)
50 {
51 print $_."," ;
52 }
53 else
54 {
55 print "$_\n" ;
56
57 }
58 }
也可以更改为: 42 print "Content-Type:application/json\r\n\r\n";
43 print $_,"\n" for map {$_ % 2 ? () : ($resJson[$_] . "," . $resJson[$_+1]) } (0..$#resJson);
另外开始的时候:
map {print $_,"\n"} @resJson;
结果也是一样的。
今天又写了个新的: