#!D:\perl\bin\perl
use strict;
#use warnings;
use DBI;
use List::Util qw(first);
# Replace datasource_name with the name of your data source.
# Replace database_username and database_password
# with the SQL Server database username and password.
my $data_source = q/dbi:ODBC:test/;
my $user = q/tracker/;
my $password = q/TrAck3r/;
#Absolute path to the svnlook program
my $svnlook = "D:\\Subversion\\bin\\svnlook.exe";
my $debugEnabled = 0; 
my $argNum = 0;
my ($repo_path, $txn_name) = @ARGV;
my $teamtrack = "sa_svndev";
## Check to see if debug is enabled 

foreach $argNum (0 .. $#ARGV) 
{ 
  if ( ($ARGV[$argNum] eq "-d") || ($ARGV[$argNum] eq "-D")) 
  { 
    $debugEnabled = 1; 
  } 
} 
if ($debugEnabled) 
{ 
  print "\n------ DEBUG IS ENABLED ------\n"; 
  ## Use revision numbers since there is not a transaction number 
  $txn_name = "-r $txn_name"; 
} 
else 
{ 
  ## Use transaction numbers 
  $txn_name = "-t $txn_name"; 
} 
my $committer = `$svnlook author $txn_name $repo_path`
    or die("Unable to get committer with svnlook.\n");
chomp($committer);
if ($committer eq $teamtrack )
{ 
print "Not Checking since user is teamtrack";
exit 0;
}
#print "\n\n$committer";
my $changeddirectory = `$svnlook dirs-changed $txn_name $repo_path`
     or die("Unable to get the changed directories with svnlook.\n");
#print "\n\nDirectory changed $changeddirectory";
my $strippeddirectory = $1 if ($changeddirectory =~ /(\d+)/);
#print "$strippeddirectory";
# Connect to the data source and get a handle for that connection.
my $dbh = DBI->connect($data_source, $user, $password)
    or die "Can't connect to $data_source: $DBI::errstr";
# This query generates a result set with one record in it.
my $sql = "select
ucs.ts_issueid as CNR_NUMBER,
ucs.ts_state as CNR_STATE,
LTRIM((SELECT 
      STUFF((SELECT ',  ' + List 
FROM 
      (SELECT
            List = substring((SELECT ( ', ' + TS_LOGINID )
                           FROM dbo.TS_USERS t2
                           WHERE t1.val = t2.TS_ID                           
                           FOR XML PATH( '' )
                          ), 3, 1000 )FROM (SELECT * FROM dbo.Split(ucs.ts_developers_ext, ',') WHERE VAL <> '') t1) as subquery
FOR XML PATH('')), 1, 1, ''))) as DEVELOPERS
from usr_cnr_solution ucs
inner join ts_states s on s.ts_id = ucs.ts_state
where s.ts_id = 88
";
# Prepare the statement.
my $sth = $dbh->prepare($sql)
   or die "Can't prepare statement: $DBI::errstr";
# Execute the statement.
$sth->execute();
# Fetch and display the result set value.
while ( my @row = ($sth->fetchrow_array )) {
 print "@row\n";
my $cnrissue = first { /$strippeddirectory/ } @row;
my $cnrdev = first { /$committer/ } @row;
print "$cnrissue";
print "$cnrdev";
}
# Disconnect the database from the database handle.
$dbh->disconnect;