-
-
Save nfxpnk/9322814 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/perl | |
| use MIME::Lite; | |
| use DBI; | |
| use GD::Graph::lines; | |
| use Data::Dumper; | |
| use POSIX qw(strftime); | |
| use Date::Parse; | |
| $ENV{'ORACLE_HOME'} = "/opt/oracle/db11g"; | |
| $ENV{'LD_LIBRARY_PATH'} = "/opt/oracle/db11g/lib/"; | |
| my $dbh = DBI->connect("dbi:Oracle:host=atlas-db-01.ecofabric.com;sid=ATLAS", 'APEXIQ', 'Apexiq-1'); | |
| my $graphImagePath = "/tmp/"; | |
| my $sql = 'SELECT ROUND(AVG(score)) FROM "APEXIQ"."V_SCORE_REPORT"'; | |
| my $sth = $dbh->prepare($sql); | |
| $sth->execute(); | |
| my $avgScore = $sth->fetchrow(); | |
| my $currentDate = strftime ("%m/%d/%Y", localtime); | |
| my $reportHtml = qq{<body> | |
| <br> | |
| <center>Average <b><font color="df741a">JIRA Score</font></b> of all users is <b><font color="df741a">$avgScore</font></b></center> | |
| <br> | |
| <br> | |
| <div style="text-align:center;"><img src="cid:graphImage.gif"></div> | |
| <br> | |
| <br> | |
| <table style="border-collapse:collapse;border-style:solid;border-width:1px" summary="Script output" align="center" border="1" width="900px"> | |
| <tr> | |
| <th scope="col" style="background:#cccc99"> | |
| </th> | |
| <th scope="col" style="background:#cccc99"> | |
| FULL NAME | |
| </th> | |
| <th scope="col" style="background:#cccc99"> | |
| POSITION | |
| </th> | |
| <th scope="col" style="background:#cccc99"> | |
| MANAGER | |
| </th> | |
| <th scope="col" style="background:#cccc99"> | |
| <span class="il">SCORE</span> | |
| </th> | |
| <th scope="col" style="background:#cccc99"> | |
| <span class="il">GRADE</span> | |
| </th> | |
| <th scope="col" style="background:#cccc99"> | |
| <span class="il">7 DAYS</span> | |
| </th> | |
| <th scope="col" style="background:#cccc99"> | |
| <span class="il">30 DAYS</span> | |
| </th> | |
| </tr> | |
| }; | |
| $sql = 'SELECT * from "APEXIQ"."V_SCORE_REPORT"'; | |
| $sth = $dbh->prepare($sql); | |
| $sth->execute(); | |
| my $grade; | |
| sub score_fmt { | |
| if ("$_[0]" > 0) { | |
| $_[0] = qq{<font color="green">+$_[0]</font>}; | |
| } elsif ($_[0] < 0) { | |
| $_[0] = qq{<font color="red">$_[0]</font>}; | |
| } elsif ($_[0] = 0) { | |
| $_[0] = qq{<font color="black">$_[0]</font>}; | |
| } | |
| } | |
| while (my ($id, $user_name, $full_name, $position, $manager, $score, $score_seven, $score_thirty) = $sth->fetchrow()) { | |
| # grading | |
| if ($score <= 800) { | |
| $grade = "Bad"; | |
| } elsif ($score >= 801 and $score <= 1000) { | |
| $grade = "Not Good"; | |
| } elsif ($score >= 1001 and $score <= 1200) { | |
| $grade = "Good"; | |
| } elsif ($score >= 1201 and $score <= 1400) { | |
| $grade = "Very Good"; | |
| } elsif ($score >= 1401) { | |
| $grade = "Awesome"; | |
| } | |
| $full_name = qq{<a href="http://jira.ontrq.com/ViewProfile.jspa?name=$user_name" style="text-decoration:none">$full_name</a>}; | |
| $score_format = qq{<center><a href="http://jira.ontrq.com/ViewProfile.jspa?name=$user_name" style="text-decoration:none"><b><font color="df741a">$score</font></b></a></center>}; | |
| $grade = qq{<center><font color="df741a">$grade</font></center>}; | |
| $score_seven = $score-$score_seven; | |
| $score_thirty = $score-$score_thirty; | |
| &score_fmt($score_seven); | |
| &score_fmt($score_thirty); | |
| $reportHtml .= qq{ | |
| <tr> | |
| <td> | |
| $id | |
| </td> | |
| <td> | |
| $full_name | |
| </td> | |
| <td> | |
| $position | |
| </td> | |
| <td> | |
| $manager | |
| </td> | |
| <td> | |
| $score_format | |
| </td> | |
| <td> | |
| $grade | |
| </td> | |
| <td align="right" style="font-size:5px"> | |
| $score_seven | |
| </td> | |
| <td align="right" style="font-size:5px"> | |
| $score_thirty | |
| </td> | |
| </tr> | |
| }; | |
| } | |
| $reportHtml .= qq{</table></body>}; | |
| $sql = 'SELECT * FROM "APEXIQ"."JR_AVG_SCORE" WHERE trunc(date_report) >= trunc(sysdate-30) ORDER BY date_report ASC'; | |
| $sth = $dbh->prepare($sql); | |
| $sth->execute(); | |
| my $minScore = 0; | |
| my $maxScore = 0; | |
| my @timeArray; | |
| my @scoreArray; | |
| while (my ($id, $date, $score) = $sth->fetchrow()) { | |
| my $time = strftime ("%m/%d", localtime str2time($date)); | |
| push(@timeArray, $time); | |
| push(@scoreArray, $score); | |
| if($minScore > $score) { | |
| $minScore = $score; | |
| } | |
| if($maxScore < $score) { | |
| $maxScore = $score; | |
| } | |
| } | |
| if($maxScore < 1500) { | |
| $maxScore = 1500; | |
| } else { | |
| $maxScore = 2500; | |
| } | |
| $myGraph = new GD::Graph::lines(600,300); | |
| $myGraph->set( | |
| x_label => '', | |
| y_label => 'JIRA Score', | |
| title => 'Average JIRA Score History', | |
| y_max_value => 1150, | |
| y_min_value => 1000, | |
| y_tick_number => 20, | |
| x_tick_offset => 0, | |
| y_label_skip => 2, | |
| axis_space => 0, | |
| box_axis => 0, | |
| line_width => 3, | |
| transparent => 0, | |
| ); | |
| my @data = ([@timeArray], [@scoreArray]); | |
| $myGraph->plot(\@data); | |
| local(*OUT); | |
| my $ext = $myGraph->export_format; | |
| my $graphImage = $graphImagePath . 'reportGraph.' . $ext; | |
| open(OUT, ">" . $graphImage) or die 'Cannot open ' . $graphImage . ' for write'; | |
| binmode OUT; | |
| print OUT $myGraph->gd->$ext(); | |
| close OUT; | |
| my $msg = MIME::Lite->new( | |
| #To => 'internal@sysiq.com', | |
| #Cc => 'i.kovnatskiy@sysiq.com, s.lube@sysiq.com', | |
| Bcc => 'i.kovnatskiy@sysiq.com', | |
| From => 'JIRA Notifications <do_not_reply@ontrq.com>', | |
| Subject => 'JIRA Scores Daily Digest ' . $currentDate, | |
| Type => 'multipart/related' | |
| ); | |
| $msg->attach( | |
| Type => 'text/html', | |
| Data => $reportHtml | |
| ); | |
| $msg->attach( | |
| Type => 'image/gif', | |
| Id => 'graphImage.gif', | |
| Path => $graphImage, | |
| ); | |
| $msg->send('smtp','appmail.ecofabric.com'); | |
| unlink $graphImage; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment