#!/usr/bin/perl

#
#Table VI Annual coal workers’ pneumoconiosis mortality risk by cumulative exposure to coala.
# By region, exposure level and age
# A regions BCDE regions
# b c d e
# Low exposure High exposure Low exposure High exposure
# Cumulative Cumulative Annual Cumulative Annual Cumulative
# Annual
# f g
#Age Annual risk
# exposure exposure risk exposure risk exposure
# risk
#20-29 1.7 0.00001 8.3 0.00004 4.2 0.00002 12.5 0.00007
#30-44 2.7 0.00001 13.4 0.00007 6.7 0.00004 20.1 0.00011
#45-59 4.0 0.00002 20.2 0.00011 10.1 0.00005 30.3 0.00023
#60-69 4.6 0.00002 22.8 0.00012 11.4 0.00006 34.1 0.00029
#70-79 4.7 0.00002 23.3 0.00012 11.7 0.00006 35.0 0.00030
#80+ 4.9 0.00003 24.6 0.00013 12.3 0.00006 36.9 0.00034
#a: Based on [Kuempel, et al. 1995]
# At 0.2* the United States’ MSHA PEL (of 2 mg/m3)
#b:
#c: At 1.0* the United States’ MSHA PEL
#d: At 0.5* the United States’ MSHA PEL
#e: At 1.5* the United States’ MSHA PEL
# Average exposure in mg/m3.yr
#f:
#g Annual risk of death from coal workers pneumoconiosis

use strict;
use DBD;

use Spreadsheet::ParseExcel;
use constant SHORT_TON => 907.18; #0.907 metric ton or 907.18 kilograms

#3 kilograms per metric ton (kg/t) of coal mined,
#with the ambient dust concentration ranging
#from 10 to 300 micrograms per cubic meter (μg/
#m3) above the background level at the mine site

use constant DUST_PER_SHORT_TON => 3.0;#0.003306934 short tons

my @years = qw(1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007);

#read_xls('RecentCoalConsumption.xls');
#read_xls('RecentPrimaryCoalProductionMST.xls');
my $dbd = DBD->new;
$dbd->Connect_DB;
my %countries; #hash to hold countries and ids
$dbd->get_countries_DB(\%countries);
# foreach my $country_id (keys %countries){
# print " $country_id = ".$countries{$country_id}{name}."\n";
# }
my %years; #hash to hold countries and ids
$dbd->get_years_DB(\%years);
# foreach my $id (keys %years){
# print " $id = ".$years{$id}{year}."\n";
# }
foreach my $country_id (keys %countries){

foreach my $id (keys %years){
print " $id = ".$years{$id}{year}."\n";
my $production = $dbd->get_country_production_DB(
$countries{$country_id}{name},
$years{$id}{year}
);
my $consumption = $dbd->get_country_consumption_DB(
$countries{$country_id}{name},
$years{$id}{year}
);

print " ".$countries{$country_id}{name}." ";
print " ".$years{$id}{year}." production = $production consumption = $consumption\n";

}
}

sub read_xls {

my $file = shift;

my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse($file);
if ( !defined $workbook ) {
die $parser->error(), ".\n";
}
my %country;
for my $worksheet ( $workbook->worksheets() ) {

my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();

for my $row ( $row_min .. $row_max ) {
my $cntry = '';
my $cnt = 0;
for my $col ( $col_min .. $col_max ) {

my $cell = $worksheet->get_cell( $row, $col );
next unless $cell;
if($col == 1)
{
$cntry = $cell->value();
}
elsif($col > 2)
{
my $year = $years[$cnt];
my $production = $cell->value();

$production =~ s/- -/NULL/;

# print " '$production' ";
$country{$cntry}{$year} = $production;
# print "counrtry $cntry year $year ".$cell->value(). "\n";
$cnt++;
}
# print "Row, Col = ($row, $col)\n";
# print "Value = ", $cell->value(), "\n";
# print "Unformatted = ", $cell->unformatted(), "\n";
# print "\n";

}
#die;
}
}

#$dbd->Init_DB;

#foreach(@years){
# $dbd->add_year_DB($_);
#}
foreach my $cnty (keys %country)
{
# my $country_id = $dbd->add_country_DB($cnty);
# print "Country = $cnty id = $country_id ";

foreach my $year (sort (keys %{$country{$cnty}}))
{
# my $year_id = $dbd->add_year_DB($year);
my $consumption_id = $dbd->add_production_DB(
$country{$cnty}{$year},#$value,
$year, #$year,
$cnty,#$country,
);

print "year $year = ".$country{$cnty}{$year}." ";
# # my $dust = ($country{$cnty}{$year} * 1000000) * DUST_PER_SHORT_TON;
## print " Dust produced $dust ";
}
print "\n";
}
}