#!/usr/bin/perl
use strict;
use DBI;

if($ARGV[0] != int($ARGV[0]) || $ARGV[0] eq '' || $ARGV[1] != int($ARGV[1]) || $ARGV[1] eq '')
{
    print "Usage:  ./bo_prod_vifan.pl year month \n\n";
    exit;
}
my $year = $ARGV[0];
my $month = $ARGV[1];
my $startdate="$year-$month-01";

my %groupings = ();
$groupings{CDN}{G}{code} = 'TN';
$groupings{CDN}{G}{descr} = 'PRIMARY SLITTING';
$groupings{CDN}{G}{order} = 20;
$groupings{USA}{P}{code} = 'TN';
$groupings{USA}{P}{descr} = 'PRIMARY SLITTING';
$groupings{USA}{P}{order} = 20;

my %site_ext = ();
$site_ext{USA} = "EXT";
$site_ext{CDN} = "BR";

my $dbh = DBI->connect('DBI:mysql:vifan','webuser') or die "Couldn't open database: ". DBI->errstr . "\n";
my $statementC = "SELECT TRIM(masterroll), qty, UCASE(site), family FROM consume ";
my $sthC = $dbh->prepare($statementC);
my $rcC = $sthC->execute();
my $refC = $sthC->fetchall_arrayref;
my %Cdata = ();
for my $x(0..$#{$refC})
{
    $Cdata{$refC->[$x][0]}{family} = $refC->[$x][3];
    $Cdata{$refC->[$x][0]}{site} = U$refC->[$x][2];
}

$statementC = "SELECT TRIM(masterroll), qty, UCASE(site), family FROM consume WHERE YEAR(transdate)=? AND MONTH(transdate)=?";
$sthC = $dbh->prepare($statementC);
$rcC = $sthC->execute($year,$month);
$refC = $sthC->fetchall_arrayref;
for my $x(0..$#{$refC})
{
    $Cdata{$refC->[$x][0]}{qty} += $refC->[$x][1];
    $Cdata{familyqty}{$refC->[$x][2]}{$refC->[$x][3]} += $refC->[$x][1];
}

my %label =();
my $statementR1 = "SELECT label, wc, master_1, slit_1 FROM rolls ";
my $sthR1 = $dbh->prepare($statementR1);
my $rcR1 = $sthR1->execute();
my $refR1 = $sthR1->fetchall_arrayref;
for my $x(0..$#{$refR1})
{
    $label{$refR1->[$x][0]}{wcout} = $refR1->[$x][1];
}

my $statementR = "SELECT net_wgt, grade, wc, TRIM(master_1), master_2, master_3, master_4, master_5, UCASE(prod_site), YEAR(prod_date), MONTH(prod_date), TRIM(label), slit_1 FROM rolls WHERE YEAR(prod_date)=? AND MONTH(prod_date)=? ORDER BY label";
my $sthR = $dbh->prepare($statementR);
my $rcR = $sthR->execute($year,$month);
my $refR = $sthR->fetchall_arrayref;
for my $x(0..$#{$refR})
{
    $label{$refR->[$x][11]}{mr} = $refR->[$x][3];
    if($refR->[$x][12] == 0)
    {
        $label{$refR->[$x][11]}{wcin} = $site_ext{$Cdata{$refR->[$x][3]}{site}};
    }
    else
    {
        $label{$refR->[$x][11]}{wcin} = $label{$refR->[$x][12]}{wcout};
    }
}

my %Rdata = ();
for my $x(0..$#{$refR})
{
    my $choice = $refR->[$x][1];
    if($choice == 4 ){$choice = "scrap";}
    elsif($choice == 1){$choice = "first"}
    else{$choice = "second";}
    
    #         prodsite          extsite                                  year            month               wcin                             wcout                         family                    grade
    $Rdata{$refR->[$x][8]}{$site_ext{$Cdata{$refR->[$x][3]}{site}}}{$refR->[$x][9]}{$refR->[$x][10]}{$label{$refR->[$x][11]}{wcin}}{$label{$refR->[$x][11]}{wcout}}{$Cdata{$refR->[$x][3]}{family}}{$choice} += $refR->[$x][0];
}

$dbh->disconnect;

open(FILE,">/home/web/vibacgroup_info/data_it/bo_prod_vifan.txt");
print FILE "Division;Grouping code;Year;Month;Site (Plant);Extrusion Work Center;Input Work Center;Output Work Center;Product code;Budget 1st Choice Hour Capacity;Budget Input Qty;Actual Input Qty;Prev Year/Month Input Qty;Budget Input Repro Qty;Actual Input Repro Qty;Prev Year/Month Input Repro Qty;Budget 1st choice Qty;Actual 1st choice Qty;Prev Year/Month 1st choice Qty;Budget 2nd choice Qty;Actual 2nd choice Qty;Prev Year/Month 2nd choice Qty;Budget Input Qty Compared;Actual Input Qty Compared;Prev Year/Month Input Qty Compared;Budget 1st choice Qty Compared;Actual 1st choice Qty Compared;Prev Year/Month 1st choice Qty Compared;Budget 2nd choice Qty Compared;Actual 2nd choice Qty Compared;Prev Year/Month 2nd choice Qty Compared;File creation date;1st Choice compared product hour capacity;Compared product code;Product type;Grouping order;Grouping description;Budget Dispersion Qty;Actual Dispersion Qty;Prev Year/Month Dispersion Qty;IND_CIG_POLO;IND_CIG_TOT;IND_CIG_ESTR\r\n";
my $date = DateStamp();
for my $site(keys %Rdata)
{
    for my $extsite(keys %{$Rdata{$site}})
    {
        for my $year(keys %{$Rdata{$site}{$extsite}})
        {
            for my $month(keys %{$Rdata{$site}{$extsite}{$year}})
            {
                for my $wcin(keys %{$Rdata{$site}{$extsite}{$year}{$month}})
                {
                    for my $wcout(keys %{$Rdata{$site}{$extsite}{$year}{$month}{$wcin}})
                    {
                        for my $family(keys %{$Rdata{$site}{$extsite}{$year}{$month}{$wcin}{$wcout}})
                        {
                            my $disp = $Cdata{familyqty}{$site}{$family} - $Rdata{$site}{$extsite}{$year}{$month}{$wcin}{$wcout}{$family}{first} - $Rdata{$site}{$extsite}{$year}{$month}{$wcin}{$wcout}{$family}{second} - $Rdata{$site}{$extsite}{$year}{$month}{$wcin}{$wcout}{$family}{scrap};
                            print FILE "1;$groupings{$site}{$wcout}{code};$year;$month;$site;$extsite;$wcin;$wcout;$family;;;$Cdata{familyqty}{$site}{$family};;;;;;$Rdata{$site}{$extsite}{$year}{$month}{$wcin}{$wcout}{$family}{first};;;$Rdata{$site}{$extsite}{$year}{$month}{$wcin}{$wcout}{$family}{second};;;;;;;;;;;$date;;;BF;$groupings{$site}{$wcout}{order};$groupings{$site}{$wcout}{descr};;$disp;;;;\r\n";
                        }
                    }
                }
            }
        }
    }
}
close(FILE);
exit;

sub DateStamp
{
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
    $year = $year + 1900;
    $mon++;
    if($mon < 10){ $mon = "0$mon"; }
    if($mday < 10){ $mday = "0$mday"; }
    if($hour < 10){ $hour = "0$hour"; }
    if($min < 10){ $min = "0$min"; }
    if($sec < 10){ $sec = "0$sec"; }
    my $timestamp = "$year-$mon-$mday";
    return $timestamp;
}