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-bankFast");
open(outfile2, ">Z'-score-SP-grey-area");
open(outfile3, ">Z'-score-SP-healthy");
open(outfile4, ">Z'-score-SP-F-Test");

$X1Avg =0;
$X2Avg =0;
$X3Avg =0;
$X4Avg =0;
$X5Avg =0;
$ZAvg =0;
$count =0;
$X1AvgH =0;
$X2AvgH =0;
$X3AvgH =0;
$X4AvgH =0;
$X5AvgH =0;
$ZAvgH =0;
$countH =0;
$X1Mean =0;
$X2Mean =0;
$X3Mean =0;
$X4Mean =0;
$X5Mean =0;
$ZMean =0;
$X1MeanH =0;
$X2MeanH =0;
$X31MeanH =0;
$X4MeanH =0;
$X5MeanH =0;
$ZMeanH =0;
$countH =0;



my $currentAssets = $dbh->prepare('select B.totalCurrentAssets,B.totalCurrentLiabilities,B.totalAssets,B.retainedEarnings,B.totalLiabilities,B.totalStockholderEquity,I.earningsBeforeInterestAndTaxes,I.totalRevenue,B.symbol,B.periodEnding 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 = 0.717*$X1 + 0.847*$X2 + 3.107*$X3 + 0.420*$X4 + 0.998*$X5;
$symbol = $row[8];
$period = $row[9];
$companyQuery = "select companyName from stcks where symbol='$symbol'";
my $companyName = $dbh->prepare($companyQuery);
$companyName ->execute();
while($name = $companyName ->fetchrow_array()){
$nameOfCompany=$name;
}

if($Z<=0.41){
print outfile1 "$nameOfCompany \t";
print outfile1 "$symbol   ";
print outfile1 "$period  = ";
print outfile1 "$Z \n";
$X1Avg = $X1Avg + $X1*$X1;
$X2Avg = $X2Avg + $X2*$X2;
$X3Avg = $X3Avg + $X3*$X3;
$X4Avg = $X4Avg + $X4*$X4;
$X5Avg = $X5Avg + $X5*$X5;
$ZAvg = $ZAvg + $Z*$Z;

$X1Mean = $X1Mean + $X1;
$X2Mean = $X2Mean + $X2;
$X3Mean = $X3Mean + $X3;
$X4Mean = $X4Mean + $X4;
$X5Mean = $X5Mean + $X5;
$ZMean = $ZMean + $Z;

$count = $count + 1;

}
if($Z<=1.9 && $Z>0.41){
print outfile2 "$nameOfCompany \t";
print outfile2 "$symbol   ";
print outfile2 "$period  = ";
print outfile2 "$Z \n";
}
if($Z>1.9){
print outfile3 "$nameOfCompany \t";
print outfile3 "$symbol   ";
print outfile3 "$period  = ";
print outfile3 "$Z \n";
$X1AvgH = $X1AvgH + $X1*$X1;
$X2AvgH = $X2AvgH + $X2*$X2;
$X3AvgH = $X3AvgH + $X3*$X3;
$X4AvgH = $X4AvgH + $X4*$X4;
$X5AvgH = $X5AvgH + $X5*$X5;
$ZAvgH = $ZAvgH + $Z*$Z;

$X1MeanH = $X1MeanH + $X1;
$X2MeanH = $X2MeanH + $X2;
$X3MeanH = $X3MeanH + $X3;
$X4MeanH = $X4MeanH + $X4;
$X5MeanH = $X5MeanH + $X5;
$ZMeanH = $ZMeanH + $Z;

$countH = $countH + 1;


}

}

}

$X1Avg = $X1Avg/$count;
$X2Avg = $X2Avg/$count;
$X3Avg = $X3Avg/$count;
$X4Avg = $X4Avg/$count;
$X5Avg = $X5Avg/$count;
$ZAvg = $ZAvg/$count;

$X1Avg = sqrt($X1Avg);
$X2Avg = sqrt($X2Avg);
$X3Avg = sqrt($X3Avg);
$X4Avg = sqrt($X4Avg);
$X5Avg = sqrt($X5Avg);
$ZAvg = sqrt($ZAvg);

$X1Mean = $X1Mean/$count;
$X2Mean = $X2Mean/$count;
$X3Mean = $X3Mean/$count;
$X4Mean = $X4Mean/$count;
$X5Mean = $X5Mean/$count;
$ZMean = $ZMean/$count;

print outfile4 "Bankrupt Statistics  \n";
print outfile4 "Number of instances classified Bankrupt = $count  \n";
print outfile4 "Mean Values  \n";
print outfile4 "X1 = $X1Mean \n";
print outfile4 "X2 = $X2Mean \n";
print outfile4 "X3 = $X3Mean \n";
print outfile4 "X4 = $X4Mean \n";
print outfile4 "X5 = $X5Mean \n";
print outfile4 "Z = $ZMean \n";

print outfile4 "Mean Squared Values  \n";
print outfile4 "X1 = $X1Avg  \n";
print outfile4 "X2 = $X2Avg  \n";
print outfile4 "X3 = $X3Avg  \n";
print outfile4 "X4 = $X4Avg  \n";
print outfile4 "X5 = $X5Avg  \n";
print outfile4 "Z = $ZAvg  \n";


$X1AvgH = $X1AvgH/$countH;
$X2AvgH = $X2AvgH/$countH;
$X3AvgH = $X3AvgH/$countH;
$X4AvgH = $X4AvgH/$countH;
$X5AvgH = $X5AvgH/$countH;
$ZAvgH = $ZAvgH/$countH;

$X1AvgH = sqrt($X1AvgH);
$X2AvgH = sqrt($X2AvgH);
$X3AvgH = sqrt($X3AvgH);
$X4AvgH = sqrt($X4AvgH);
$X5AvgH = sqrt($X5AvgH);
$ZAvgH = sqrt($ZAvgH);

$X1MeanH = $X1MeanH/$countH;
$X2MeanH = $X2MeanH/$countH;
$X3MeanH = $X3MeanH/$countH;
$X4MeanH = $X4MeanH/$countH;
$X5MeanH = $X5MeanH/$countH;
$ZMeanH = $ZMeanH/$countH;

print outfile4 " \n";
print outfile4 "Healthy Statistics  \n";
print outfile4 "Number of instances classified Healthy = $countH  \n";
print outfile4 "Mean Values  \n";
print outfile4 "X1 = $X1MeanH  \n";
print outfile4 "X2 = $X2MeanH  \n";
print outfile4 "X3 = $X3MeanH  \n";
print outfile4 "X4 = $X4MeanH  \n";
print outfile4 "X5 = $X5MeanH  \n";
print outfile4 "Z = $ZMeanH  \n";

print outfile4 "Mean Squared Values  \n";
print outfile4 "X1 = $X1AvgH  \n";
print outfile4 "X2 = $X2AvgH  \n";
print outfile4 "X3 = $X3AvgH  \n";
print outfile4 "X4 = $X4AvgH  \n";
print outfile4 "X5 = $X5AvgH  \n";
print outfile4 "Z = $ZAvgH  \n";

$Dist = $ZMeanH - $ZMean;
print outfile4 "Z Distance between 2 groups = $Dist \n";




$dbh->disconnect;
close(INPUT);