#!/usr/bin/perl -w

use warnings;
use strict;
use PDF::API2;
use CAM::PDF;
use CAM::PDF::PageText;
use String::Util ':all';
use Spreadsheet::ParseXLSX;
use Text::CSV;
use Date::Format;
use DateTime::Format::DateParse;

my $pdfFilename = "<insert PDF file name>";
my $pdf = CAM::PDF->new($pdfFilename);
my $text = $pdf->getPageText(1);
	
my $endString = "DELIVERY";
my $headIndex = index($text, $endString);
my $newText = substr($text, 0, $headIndex);
my $header = trim($newText);
	
my $amdNumStart = "NO. ";
my $amdNumEnd = "GAS";
my $amdNumStartIndex = index($header, $amdNumStart)+3;
my $amdNumEndIndex = index($header, $amdNumEnd);
my $amdNumString = substr($header, $amdNumStartIndex, $amdNumEndIndex - $amdNumStartIndex);
my $amdNum = trim($amdNumString);
	
my $poolNumStart = "30202";
my $poolNumEnd = "TIGER";
my $poolNumStartIndex = index($header, $poolNumStart);
my $poolNumEndIndex = index($header, $poolNumEnd);
my $poolNumString = substr($header, $poolNumStartIndex, $poolNumEndIndex - $poolNumStartIndex);
my $poolNum = trim($poolNumString);
	
my $dateStart = "DATED ";
my $dateStartIndex = index($header, $dateStart)+6;
my $dateEndIndex = index($header, $dateStart);
my $dateString = substr($header, $dateStartIndex, $dateEndIndex - $dateStartIndex);
my $reportDate = trim($dateString);

my @date = split(' ', $reportDate);
my $month = $date[0];
my $day = $date[1];
my $comma = chop($day);
my $year = $date[2];

my $dateJoin = join(' ', $month,$day,$year);

my $parsedDate = DateTime::Format::DateParse->parse_datetime($dateJoin);
my $billMonth = $parsedDate->strftime('%m%d%Y');

my $fileName = "<insert Excel file name>";
my $parser = Spreadsheet::ParseXLSX->new();
my $workbook = $parser->parse($fileName);
my $outfile = "NMGC_Snapshot_$billMonth.csv";

die $parser->error(), ".\n" if (!defined $workbook);

open(my $out, '>', $outfile) or die "Could not open $outfile' $!\n";

my $csv = Text::CSV->new();

my @fields = ('Customer Name','Street Address','City','Station Num','Customer Num','Premise Num','Point MDQ','Priority Class','Rate Schedule','Consolidated Station Num'); 

$csv->combine($amdNum, $poolNum, $billMonth,'','','','','','','');
print $out $csv->string, "\n";

$csv->combine(@fields);
print $out $csv->string, "\r\n\r\n";

for my $worksheet($workbook->worksheet(0)){
	
	my($firstRow, $lastRow) = $worksheet->row_range();
	
	for my $row(6 .. $lastRow){
		#array of cell values
		my $RowCellValues = [];
		
		for (my $col = 1; $col < 10; $col++) {
			my $cell = $worksheet->get_cell($row, $col);
			next unless $cell;
			push($RowCellValues, $cell->value());
		}
		my $status = $csv->print ($out, $RowCellValues);
		print $out "\r\n";
	}
}