NickPiscitelli/gemini.pl

## gemini.pl
#!/usr/bin/env perl

use strict;
use warnings;
use Data::Dumper qw(Dumper);
use DBI;
use Getopt::Long;

my ($gemail, $guser, $gdays);
GetOptions(
    'email=s' => \$gemail,
    'user=s' => \$guser,
    'days=i' => \$gdays
);

my $dbh = DBI->connect('dbi:ODBC:DSN=<<DSN_NAME>>','<<DOMAIN>>\\<<USER>>','<<PASS>>');

my $sth;
my @months = qw(Jan Feb Mar Apr Jun Jul Aug Sep Oct Nov Dec);

my $template = <<template;
<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>
<html xmlns='http://www.w3.org/1999/xhtml'>
 <head>
  <meta http-equiv='Content-Type' content='text/html; charset=UTF-8' />
  <title>Demystifying Email Design</title>
  <meta name='viewport' content='width=device-width, initial-scale=1.0'/>
</head>
<body>
##REPLACE##
</body>
</html>
template

my %users = (
    'yyyy' => 'xxx',
    'yyyy' => 'xxx',
    'yyyy' => 'xxx',
    'yyyy' => 'xxx',
    'yyyy' => 'xxx'
);

if ($guser && ($users{$guser} || $gemail)){
    %users = (
        $guser => $gemail || $users{$guser}
    );
}

for my $user (keys %users){
    $sth = $dbh->prepare(q{
     SELECT gemini_issues.issueid, summary, 'weekNumber'=Datepart(WK, timeentrydate), 'dateLogged'=CONVERT(VARCHAR(32), timeentrydate, 107), hours, minutes FROM gemini_users, gemini_issues, gemini_timetracking WHERE gemini_timetracking.userid = gemini_users.userid AND gemini_timetracking.issueid = gemini_issues.issueid AND gemini_timetracking.timeentrydate >= '18 Apr 2015' AND surname = ? ORDER BY dateLogged SELECT 'dateLogged'=CONVERT(VARCHAR(32), timeentrydate, 107), CASE WHEN Sum(minutes) > 59 THEN Sum(hours) + Floor(Sum(minutes) / 60) ELSE Sum(hours) END hours, CASE WHEN Sum(minutes) > 59 THEN Sum(minutes)%60 ELSE Sum(minutes) END minutes FROM gemini_users, gemini_issues, gemini_timetracking WHERE gemini_timetracking.userid = gemini_users.userid AND gemini_timetracking.issueid = gemini_issues.issueid AND gemini_timetracking.timeentrydate >= '18 Apr 2015' AND surname = ? GROUP BY CONVERT(VARCHAR(32), timeentrydate, 107) ORDER BY dateLogged
    });
    $sth->execute($user,$user);

    my %days;
    while (my $row = $sth->fetchrow_hashref){
        $days{$row->{dateLogged}} //= [];
    	push @{$days{$row->{dateLogged}}}, $row;
    }

    my $out = '<h1><b><u>Time Sheet for '.$user.'</u></b></h1><br><br><br>';
    my @loop_keys = sort { indexGet($b) cmp indexGet($a) || dayGet($b) <=> dayGet($a) } keys %days;
    my $r = 0;
    for my $k (@loop_keys){
        last if $r++ == $gdays;
    	$out .= '<b><u>'.$k.'</u></b><br><br>';
        my $totalH = 0;
        my $totalM = 0;
        for my $row (sort { dayGet($a) <=> dayGet($b) } @{$days{$k}}){
            my $hours = $row->{hours} ? $row->{hours}.' hour' : '';
            my $minutes = $row->{hours} && $row->{minutes} ? ' and ' : '';
            $minutes .= $row->{minutes} ? $row->{minutes}.' minutes' : '';
            $out .= '<b>'.$row->{summary}.':</b> '.$hours.$minutes."<br /><br />\n\n";
            my $link = 'http://devserver/gemini/workspace/0/item/'.$row->{issueid};
    	    $out .= '<a href=\"'.$link.'\">'.$link.'</a>';
            $out .= "<br><br />\n";
            $totalH += $row->{hours};
            $totalM += $row->{minutes};
        }
    	$out .= "<b><u>Total Time:</u></b> $totalH hours $totalM minutes<br><br><br /><br />\n\n";
    }

    my $t = "From: web-department\@fragrancenet.com\n";
    $t .= "To: ".$users{$user}."\n";
    $t .= "Subject: Gemini Time\n";
    $t .= "Content-Type: text/html\n";
    $t .= "MIME-Version: 1.0\n";

    my $tmp = $template;
    $tmp =~ s/##REPLACE##/$out/;
    $t .= $tmp;

    `echo "$t" | sendmail -t`;
}

sub indexGet {
    my $val = shift;
    $val =~ /^([A-Za-z]{3})/;
    $val = $1;
    for my $i (0..$#months){
        if ($months[$i] eq $val){
            return $i;
        }
    }
}

sub dayGet {
    my $val = shift;
    if (ref $val){
        $val = $val->{dateLogged};
    }
    $val =~ /^[A-Za-z]{3} (\d\d)/;
    $val = $1;
    print $1."\n";
    return $1;
}
	#!/usr/bin/env perl

	use strict;
	use warnings;
	use Data::Dumper qw(Dumper);
	use DBI;
	use Getopt::Long;

	my ($gemail, $guser, $gdays);
	GetOptions(
	'email=s' => \$gemail,
	'user=s' => \$guser,
	'days=i' => \$gdays
	);

	my $dbh = DBI->connect('dbi:ODBC:DSN=<<DSN_NAME>>','<<DOMAIN>>\\<<USER>>','<<PASS>>');

	my $sth;
	my @months = qw(Jan Feb Mar Apr Jun Jul Aug Sep Oct Nov Dec);

	my $template = <<template;
	<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>
	<html xmlns='http://www.w3.org/1999/xhtml'>
	<head>
	<meta http-equiv='Content-Type' content='text/html; charset=UTF-8' />
	<title>Demystifying Email Design</title>
	<meta name='viewport' content='width=device-width, initial-scale=1.0'/>
	</head>
	<body>
	##REPLACE##
	</body>
	</html>
	template

	my %users = (
	'yyyy' => 'xxx',
	'yyyy' => 'xxx',
	'yyyy' => 'xxx',
	'yyyy' => 'xxx',
	'yyyy' => 'xxx'
	);

	if ($guser && ($users{$guser} \|\| $gemail)){
	%users = (
	$guser => $gemail \|\| $users{$guser}
	);
	}

	for my $user (keys %users){
	$sth = $dbh->prepare(q{
	SELECT gemini_issues.issueid, summary, 'weekNumber'=Datepart(WK, timeentrydate), 'dateLogged'=CONVERT(VARCHAR(32), timeentrydate, 107), hours, minutes FROM gemini_users, gemini_issues, gemini_timetracking WHERE gemini_timetracking.userid = gemini_users.userid AND gemini_timetracking.issueid = gemini_issues.issueid AND gemini_timetracking.timeentrydate >= '18 Apr 2015' AND surname = ? ORDER BY dateLogged SELECT 'dateLogged'=CONVERT(VARCHAR(32), timeentrydate, 107), CASE WHEN Sum(minutes) > 59 THEN Sum(hours) + Floor(Sum(minutes) / 60) ELSE Sum(hours) END hours, CASE WHEN Sum(minutes) > 59 THEN Sum(minutes)%60 ELSE Sum(minutes) END minutes FROM gemini_users, gemini_issues, gemini_timetracking WHERE gemini_timetracking.userid = gemini_users.userid AND gemini_timetracking.issueid = gemini_issues.issueid AND gemini_timetracking.timeentrydate >= '18 Apr 2015' AND surname = ? GROUP BY CONVERT(VARCHAR(32), timeentrydate, 107) ORDER BY dateLogged
	});
	$sth->execute($user,$user);

	my %days;
	while (my $row = $sth->fetchrow_hashref){
	$days{$row->{dateLogged}} //= [];
	push @{$days{$row->{dateLogged}}}, $row;
	}

	my $out = '<h1><b><u>Time Sheet for '.$user.'</u></b></h1><br><br><br>';
	my @loop_keys = sort { indexGet($b) cmp indexGet($a) \|\| dayGet($b) <=> dayGet($a) } keys %days;
	my $r = 0;
	for my $k (@loop_keys){
	last if $r++ == $gdays;
	$out .= '<b><u>'.$k.'</u></b><br><br>';
	my $totalH = 0;
	my $totalM = 0;
	for my $row (sort { dayGet($a) <=> dayGet($b) } @{$days{$k}}){
	my $hours = $row->{hours} ? $row->{hours}.' hour' : '';
	my $minutes = $row->{hours} && $row->{minutes} ? ' and ' : '';
	$minutes .= $row->{minutes} ? $row->{minutes}.' minutes' : '';
	$out .= '<b>'.$row->{summary}.':</b> '.$hours.$minutes."<br /><br />\n\n";
	my $link = 'http://devserver/gemini/workspace/0/item/'.$row->{issueid};
	$out .= '<a href=\"'.$link.'\">'.$link.'</a>';
	$out .= "<br><br />\n";
	$totalH += $row->{hours};
	$totalM += $row->{minutes};
	}
	$out .= "<b><u>Total Time:</u></b> $totalH hours $totalM minutes<br><br><br /><br />\n\n";
	}

	my $t = "From: web-department\@fragrancenet.com\n";
	$t .= "To: ".$users{$user}."\n";
	$t .= "Subject: Gemini Time\n";
	$t .= "Content-Type: text/html\n";
	$t .= "MIME-Version: 1.0\n";

	my $tmp = $template;
	$tmp =~ s/##REPLACE##/$out/;
	$t .= $tmp;

	`echo "$t" \| sendmail -t`;
	}

	sub indexGet {
	my $val = shift;
	$val =~ /^([A-Za-z]{3})/;
	$val = $1;
	for my $i (0..$#months){
	if ($months[$i] eq $val){
	return $i;
	}
	}
	}

	sub dayGet {
	my $val = shift;
	if (ref $val){
	$val = $val->{dateLogged};
	}
	$val =~ /^[A-Za-z]{3} (\d\d)/;
	$val = $1;
	print $1."\n";
	return $1;
	}