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(outfile, ">DistanceToDefault-Correlation");

$lastSymbol = "null";
$lastDistanceToDefault =0;
my $symbolsQuery = $dbh->prepare('select symbol from SPBalanceSheet');
$symbolsQuery ->execute();
while($symbol = $symbolsQuery ->fetchrow_array()){

$queryStringForAssets = "select totalAssets from SPBalanceSheet where symbol= '$symbol' ";
my $assetValues = $dbh->prepare($queryStringForAssets);	
$assetValues ->execute();
$sumAsset =0;
while($asset = $assetValues ->fetchrow_array()){
	$sumAsset += $asset; 
	$lastAsset = $asset;
}

$queryStringForNumOfAssets = "select count(*) from SPBalanceSheet where symbol= '$symbol' ";
my $numOfAssets = $dbh->prepare($queryStringForNumOfAssets );	
$numOfAssets ->execute();
while($countResult = $numOfAssets ->fetchrow_array()){
$count=$countResult;
}

$avgAssetValue = $sumAsset/$count;


$assetSquaredSum =0;
my $assetValues2 = $dbh->prepare($queryStringForAssets);	
$assetValues2 ->execute();
while($assetiValue = $assetValues2 ->fetchrow_array()){

$asseti = $assetiValue - $avgAssetValue; 
$assetiSquared = $asseti*$asseti;
$assetSquaredSum += $assetiSquared;
}

if($count==1){
$stdDevSquared = $assetSquaredSum/($count);
}else{
$stdDevSquared = $assetSquaredSum/($count-1);
}
$stdDeviation = sqrt($stdDevSquared); 

$queryStringForLiabilities = "select totalLiabilities,longTermDebt from SPBalanceSheet where symbol= '$symbol' ";
my $DefaultPointValues = $dbh->prepare($queryStringForLiabilities);
$DefaultPointValues ->execute();
while(@liabilities = $DefaultPointValues ->fetchrow_array()){
$totalLiabilities = $liabilities[0];
$longTermDebt = $liabilities[1];
}	
$marketNetWorth = $lastAsset - $totalLiabilities + $longTermDebt ;
if($stdDeviation==0){
$DistanceToDefault = 1000 ;
}
else{
$DistanceToDefault = $marketNetWorth/$stdDeviation ;
}

$companyQuery = "select companyName from stcks where symbol='$symbol'";
my $companyName = $dbh->prepare($companyQuery);
$companyName ->execute();
while($name = $companyName ->fetchrow_array()){
$nameOfCompany=$name;
}

if($lastSymbol ne $symbol){
	print outfile "company=$lastNameOfComapny  DD = $lastDistanceToDefault \n";
}

$lastSymbol = $symbol;
$lastNameOfComapny = $nameOfCompany;
$lastDistanceToDefault = $DistanceToDefault ;
}
print outfile "$company=$lastNameOfComapny DD=lastDistanceToDefault \n";


$dbh->disconnect;
close(INPUT);