package ExcelExport;
use File::Temp qw/tempfile/;
use Spreadsheet::WriteExcel;
use Data::Show;
use strict;
use Text::CSV;
use Encode;
use utf8;
use Text::Unidecode;
use Try::Tiny;


sub export_excel {
   my $class = shift;
   my $c = shift;
   my $rs = shift;
   my %options = @_;

   my $filename = ($options{'filename'}) ? $options{'filename'} : '';
   my $columns = ($options{'columns'} ? $options{'columns'} : undef);
   my $headers = ($options{'headers'} ? $options{'headers'} : $columns);
   my $nouc = ($options{'nouc'} ? $options{'nouc'} : {});
show $nouc;
   my $fh; # Filehandle for the resulting excel file

   # load up temp file
   if ($filename) {
      # if filename give, load that file
      open($fh, ">", $filename) or die "cannot open $filename: $!";
   } else {
      # if no file give, create temple file.
      ( $fh, $filename ) = tempfile(); 
   }

   my $source = $rs->result_source;
   if(!$columns){
   	my $c = $source->columns_info;
   	my @k = keys %$c;
   	$columns = \@k;
   }

   # Create our new workbook
   my $workbook = Spreadsheet::WriteExcel->new($fh) or die "Problems creating new Excel file: $! : $filename";

   # Create a worksheet
   my $worksheet = $workbook->add_worksheet();

   # Create Bold formating
   my $bold = $workbook->add_format();
   $bold->set_bold();

   # Construct the header of the excel file
   my $column_cnt = 0;
   show $headers;

   foreach my $column ( @$headers ) {
      $worksheet->write(0, $column_cnt, $column, $bold);
      # Increment counter
      $column_cnt++;
   }

   # my $columnsi = $rs->result_source->columns_info;
   # my $colquote;
   # foreach my $k (keys %$columnsi){
   #    $colquote->{$k} = 1 if $columnsi->{$k} =~ /varchar|char|text/;
   # }

   # Add Data for each row
   my $row_cnt = 1; # Starts at 1 because 0 is taken by headers
   while (my $row = $rs->next) {
      # Loop through each column
      $column_cnt = 0;

      foreach my $c ( @$columns ) {
      	my $obj; my $column = $c;
      	if($c =~ /(.*?)\.(.*)/){
      		$obj = $1;
      		$column = $2;
      	}
# show $row->customerid;
# show $obj, $column;
# my $can = $row->can($obj);
# # show $obj if $obj && $row->can($obj);
#  if($obj && $row->can($obj)){
# show $column;
# # show $row->$obj;
# # show $row->$obj->can('get_column');
# # # show $row->distributor;
#  }
      	if($obj && $row->can($obj) && $row->$obj && $row->$obj->can($column)){ 

            my $v = $options{nouc}->{$column} ? $row->$obj->get_column($column) : uc($row->$obj->get_column($column)); 
            # $v = qq/"$v"/ if $colquote->{$column};
            $v = $v =~ /^\d+$/ && $v =~ /^0/ && $v !~ /^0$/ ? qq/="$v"/ : $v;
         	$worksheet->write($row_cnt, $column_cnt,  $v );            
         }elsif(!$obj && $column && ( $row->can($column) || $row->get_column($column)) ){
            my $v = uc($row->get_column($column) || $row->$column);
            # $v = qq/"$v"/ if $colquote->{$column};
            $v = $v =~ /^\d+$/ && $v =~ /^0/ && $v !~ /^0$/ ? qq/="$v"/ : $v;
            $worksheet->write($row_cnt, $column_cnt, $v);
         }else{
         	# column data doesn't exist from join
         	# my %r = $row->get_columns;
         	$worksheet->write($row_cnt, $column_cnt, '');
         }
         # Increment counter
         $column_cnt++;
      }

      # Increment counter
      $row_cnt ++;
   } 
   $workbook->close();
   close $fh or warn "$filename: $!";
   return ( $fh, $filename );
}


sub export_excel_hr {
   my $class = shift;
   my $c = shift;
   my $rs = shift;
   my %options = @_;

   my $filename = ($options{'filename'}) ? $options{'filename'} : '';  # must have
   my $columns = ($options{'columns'} ? $options{'columns'} : undef);  # must have
   my $headers = ($options{'headers'} ? $options{'headers'} : $columns);

   my $fh; 
   if ($filename) {
      open($fh, ">", $filename) or die "cannot open $filename: $!";
   } else {
      ( $fh, $filename ) = tempfile(); 
   }

   my $workbook = Spreadsheet::WriteExcel->new($fh) or die "Problems creating new Excel file: $! : $filename";
   my $worksheet = $workbook->add_worksheet();
   my $bold = $workbook->add_format();
   $bold->set_bold();

   my $column_cnt = 0;
   # foreach my $column ( @$columns ) {
   #    $worksheet->write(0, $column_cnt, $column, $bold);
   #    $column_cnt++;
   # }

   foreach my $column ( @$headers ) {
      $worksheet->write(0, $column_cnt, $column, $bold);
      # Increment counter
      $column_cnt++;
   }


# show $columns,$rs;
   my $row_cnt = 1; # Starts at 1 because 0 is taken by headers
   foreach my $row (@$rs){
      # Loop through each column
      $column_cnt = 0;

      foreach my $c ( @$columns ) {
         my $v = '';
         if($c =~ /^(.*?)\.(.*)$/){
            my $obj = $1;
            my $col = $2;
# show $obj, $col;            
            $v = $row->{$obj}->{$col};
         }else{
            $v = $row->{$c};
         }
# show $v;         
         $v = uc($v) unless $options{'nouc'};
         $v = $v =~ /^\d+$/ && $v =~ /^0/ ? qq/="$v"/ : $v;

         $worksheet->write($row_cnt, $column_cnt,  $v );                     
         $column_cnt++;
      }
      $row_cnt ++;
   } 
   $workbook->close();
   close $fh or warn "$filename: $!";
   return ( $fh, $filename );
}


sub export_csv {
   my $class = shift;
   my $c = shift;
   my $rs = shift;
   my %options = @_;
   my $filename = ($options{'filename'}) ? $options{'filename'} : '';
   my $columns = ($options{'columns'} ? $options{'columns'} : undef);
   my $trim = ($options{'trim'} ? $options{'trim'} : undef);
   my $sep_char = ($options{'sep_char'} ? $options{'sep_char'} : "\t");
   my $quote_char = ($options{quote_char} ? $options{quote_char} : undef);
   my $headers = ($options{'headers'} ? $options{'headers'} : $columns);
   my $eheaders = $options{'eheaders'} ? 1 : 0;
   my $fh; # Filehandle for the resulting excel file


   # load up temp file
   if ($filename) {
      # if filename give, load that file
      open($fh, ">", $filename) or die "cannot open $filename: $!";
   } else {
      # if no file give, create temple file.
      ( $fh, $filename ) = tempfile(); 
   }


   my $csv = Text::CSV->new ( { binary => 1,  quote_char => $quote_char,  sep_char => $sep_char, eol => "\r\n"  } )  or warn "Cannot use CSV: ".Text::CSV->error_diag ();


   my $source = $rs->result_source;
   if(!$columns){
      my $c = $source->columns_info;
      my @k = keys %$c;
      $columns = \@k;
   }
   
   show $columns;
   show ref($options{exportrowcb});
   # Create our new workbook

   open $fh, ">:encoding(utf8)", "$filename" or warn "$filename: $!";

# show @$columns;
   # Create a worksheet
   $csv->column_names(@$columns);

   # Construct the header of the excel file
   my $column_cnt = 0;
   # $csv->print($fh,$_) for @$columns;

   # Add Data for each row
   my $row_cnt = 1; # Starts at 1 because 0 is taken by headers
   my @rows;
   $csv->print($fh,$headers) if $eheaders;
   while (my $row = $rs->next) {
      # Loop through each column
      $column_cnt = 0;
      my @col;
      foreach my $c ( @$columns ) {
         my $obj; my $column = $c;
         if($c =~ /(.*?)\.(.*)/){
            $obj = $1;
            $column = $2;
         }
# show $row->customernumber;
# show $row->get_columns;
# show $column;
# warn $row_cnt;

try{
         if($obj && $row->can($obj) && $row->$obj && $row->$obj->can($column)){ 
            my $v = $row->$obj->$column;
            $v =~ s/\t|\n/ /g;
            $v =~ s/^\s+//;
            $v =~ s/\s+$//;            
            $v = substr($v,0,$trim->{$column} || $trim->{'_default'}) if $trim && ($trim->{$column} || $trim->{'_default'});
            $v = unidecode($v);
            push(@col,  $v);
         }elsif($column && ( $row->can($column) || $row->get_column($column)) ){
            my $v = $row->get_column($column);
            $v =~ s/\t|\n/ /g;
            $v =~ s/^\s+//;
            $v =~ s/\s+$//;
            $v = substr($v,0,$trim->{$column} || $trim->{'_default'}) if $trim && ($trim->{$column} || $trim->{'_default'});   
            $v = unidecode($v);                
            push(@col, $v);
         }else{
            # column data doesn't exist from join
            push(@col, '');
         }
} catch {
#   show $row->customernumber;
   print $_;
   warn $_;
};
         # Increment counter
         $column_cnt++;
      }
      if($options{exportrowcb} && ref($options{exportrowcb}) eq 'CODE'){
         $options{exportrowcb}->(\@col);

      }

      push(@rows,\@col);
      $csv->print($fh,\@col);
#	print $fh "\n";
      # Increment counter
      $row_cnt ++;
   } 
   
   close $fh or warn "$filename: $!";
   return ( $fh, $filename );
}


1;