use DBI;

# Connect to the finance database on localhost
my $dsn="DBI:mysql:host=localhost;database=finance";
my $dbh = DBI->connect ($dsn, "","") or
    die "Cannot connect to server\n";
print "Connected\n";

open(outfile1, ">Z-score-SP-Industrial");
open(outfile2, ">Z-score-SP-Utilities");
open(outfile3, ">Z-score-SP-Technology");
open(outfile4, ">Z-score-SP-BasicMaterial");
open(outfile5, ">Z-score-SP-NonCyclicalConsumer");
open(outfile6, ">Z-score-SP-CyclicalConsumer");
open(outfile7, ">Z-score-SP-Financial");
open(outfile8, ">Z-score-SP-Energy");
open(outfile9, ">Z-score-SP-Telecommunications");

$lastCompany = "null";
$lastZScore = 0;
$lastSymbol = "null";

my $currentAssets = $dbh->prepare('select B.totalCurrentAssets,B.totalCurrentLiabilities,B.totalAssets,B.retainedEarnings,B.totalLiabilities,B.totalStockholderEquity,I.earningsBeforeInterestAndTaxes,I.totalRevenue,B.symbol from SPBalanceSheet B,SPIncomeStatement I where B.symbol=I.symbol and B.periodEnding=I.periodEnding');
$currentAssets ->execute;
while(@row = $currentAssets ->fetchrow_array()) { 
$X1=$row[0]-$row[1];
if($row[2]!= 0 && $row[4]!=0){
$X1 = $X1/$row[2];
$X2 = $row[3]/$row[2];
$X3 = $row[6]/$row[2];
$X4 = $row[5]/$row[4];
$X5 = $row[7]/$row[2];
$Z = 1.2*$X1 + 1.4*$X2 + 3.3*$X3 + 0.6*$X4 + 0.99*$X5;
$symbol = $row[8];
$companyQuery = "select companyName from stcks where symbol='$symbol'";
my $companyName = $dbh->prepare($companyQuery);
$companyName ->execute();
while($name = $companyName ->fetchrow_array()){
$nameOfCompany=$name;
}

if($lastCompany ne $nameOfCompany){
$sectQueryString = "select sector from industry where symbol= '$lastSymbol'";
my $sectString  =$dbh->prepare($sectQueryString);
$sectString->execute();
while($sector = $sectString->fetchrow_array()){
$sectorName =$sector ;
}

if($sectorName eq "Industrial" ){
print outfile1 "$lastCompany  $lastZScore \n";
}
if($sectorName eq "Utilities" ){
print outfile2 "$lastCompany  $lastZScore \n";
}
if($sectorName eq "Technology" ){
print outfile3 "$lastCompany  $lastZScore \n";
}
if($sectorName eq "Basic Materials" ){
print outfile4 "$lastCompany  $lastZScore \n";
}
if($sectorName eq "Consumer, Non-Cyclical" ){
print outfile5 "$lastCompany  $lastZScore \n";
}
if($sectorName eq "Consumer, Cyclical" ){
print outfile6 "$lastCompany  $lastZScore \n";
}
if($sectorName eq "Financial" ){
print outfile7 "$lastCompany  $lastZScore \n";
}
if($sectorName eq "Energy" ){
print outfile8 "$lastCompany  $lastZScore \n";
}
if($sectorName eq "Telecommunications" ){
print outfile9 "$lastCompany  $lastZScore \n";
}


}
}
$lastCompany = $nameOfCompany;
$lastZScore = $Z;
$lastSymbol = $symbol;
}

$dbh->disconnect;
close(INPUT);