#!/usr/bin/perl

# $ENV{DBIC_TRACE}=1;
# use JSON;
use strict;
use Data::Show;
use lib '/home/sites/clm.dmadelivers.com/www/clmapp/lib';
use CLMApp::Schema;
use DateTime;
use Text::CSV;
use POSIX;
use MIME::Lite;
use Archive::Zip qw( :ERROR_CODES :CONSTANTS );
use Net::SFTP;
use Net::SFTP::Constants qw( :flags );
use Net::SSH::Perl::Kex;
use Net::SSH2::SFTP;
use Net::SFTP::Foreign;
use File::Copy;
use Mojo::JSON qw/encode_json decode_json/;
our $FTP = 1;
use Storable qw(dclone);

my $dbix = CLMApp::Schema->connect("dbi:mysql:host=rds1.dmadelivers.com;db=dmaclm", 'dmaclm', '3lUz1OV2!',{unsafe=>1,RaiseError=>0,PrintError=>1});

&default;

sub default{

	show $ARGV[0];
	# exit;

	# my $columns = [qw/customerid	customernumber	amphireid	warehouse.arrowstreamid	distributor.distributoramphireid	warehouse.arrowstreamname	warehouse.distributorwarehouseamphireid	chaingrp.chaingroupname	chaingrp.arrowstreamid	concept.chainconceptname	concept.arrowstreamid	ownershiptype	ownership	ownership2	customerunitnumber	unitname	address	city	state	zip	status.dscr	longitude	latitude	distancetodistcenter	opstatus	opstatusdate	invoicesystems.systemname	ordersystems.systemname/];
	my $oisystem = [];

	if($ARGV[0]){
		my @oisystem = split(/,/, $ARGV[0]);
		$oisystem = \@oisystem;
	}



	my $columns = [qw/customerid	customernumber	amphireid	warehouse.arrowstreamid	distributor.distributoramphireid	warehouse.arrowstreamname	warehouse.distributorwarehouseamphireid	chaingrp.chaingroupname	chaingrp.arrowstreamid	concept.chainconceptname	concept.arrowstreamid	ownershiptype	ownership	ownership2	customerunitnumber	unitname	address	city	state	zip	status.dscr	latitude  longitude	distancetodistcenter	opstatus	opstatusdate invoicesystems.systemname	ordersystems.systemname market.customwarehousename market.customwarehousenumber routing.routingidnumber accountingcode filecode/];

	my $scolumns = [qw/customerid	customernumber	amphireid	warehouse.arrowstreamid	distributor.distributoramphireid	warehouse.arrowstreamname	warehouse.distributorwarehouseamphireid	chaingrp.chaingroupname	chaingrp.arrowstreamid	concept.chainconceptname	concept.arrowstreamid	ownershiptype	ownership	ownership2	customerunitnumber	unitname	address	city	state	zip	status.dscr	latitude	longitude distancetodistcenter	opstatus	opstatusdate invoicesystems.systemname	ordersystems.systemname market.customwarehousename market.customwarehousenumber routing.routingidnumber accountingcode filecode email emailgov distributor.edicode distributor.distributorcode/];	

	my $header = ["Store #","Customer #","Amphire ID","Partner Number","Dist Co ID","Partner Abbrev","Warehouse ID","Brand","Brand ID","Concept","Concept ID","Operator Type","Operator","Sub Operator","Site #","Store Name","Address Line 1","City","State","Zip","Store Status","Latitude","Longitude","Distance to DC","Operating Status","Store Status Date","Invoice System","Order System","Custom Warehouse Name","Custom Warehouse Number","Routing Number","Accounting Code","File Code"];

# new systems
	my $columns = [qw/customerid	customernumber	amphireid	warehouse.arrowstreamid	distributor.distributoramphireid	warehouse.arrowstreamname	warehouse.distributorwarehouseamphireid	chaingrp.chaingroupname	chaingrp.arrowstreamid	concept.chainconceptname	concept.arrowstreamid	ownershiptype	ownership	ownership2	customerunitnumber	unitname	address	city	state	zip	status.dscr	 latitude	longitude distancetodistcenter	opstatus	opstatusdate routing.routingidnumber accountingcode filecode orderguideflagidval systemtype systemname systemfilespo systemfilesack systemfilesconf systemfilessalesinvoice systemfilescredit systemfilespa systemfilesog customwarehousename customwarehousenumber/];




	my $header = ["Store #","Customer #","Amphire ID","Partner Number","Dist Co ID","Partner Abbrev","Warehouse ID","Brand","Brand ID","Concept","Concept ID","Operator Type","Operator","Sub Operator","Site #","Store Name","Address Line 1","City","State","Zip","Store Status","Latitude","Longitude","Distance to DC","Operating Status","Store Status Date","Routing Number","Accounting Code","File Code","Order Guide Flag","System Type","System ID","Store Order","Store Acknowledgment","Store Confirmation","Store Sales","Store Credits","Store Price Adjustments","Store Order Guide","Custom Warehouse Name","Custom Warehouse Number"];



	if($ARGV[0]){
		# push(@$header,"Order Guide Flag");
	}else{
		# push(@$header,"Order Guide Flag");		
	}
show scalar(@$header), scalar(@$columns);
	my $rsearch = {

			"chaingrp.chaingroupid" => \'NOT IN(136,1,2,3,102,111,158,54,116,208,118,145,5,6,224,21,8,9,56,146,121,10,173,212,127,11,105,12,152,13,14,83,15,103,178,75,16,202,18,19,58,20,88,22,94,64,74,53,24,7,66,110,25,104,33,124,92,26,154,69,55,140,91,180,96,27,160,157,28,162,29,30,176,144,32,117,113,181,141,76,108,100,123,106,62,161,98,61,165,166,35,122,79,131,209,109,163,36,86,112,149,38,120,159,190,78,172,153,41,43,44,45,95,60,46,47,90,48,174,143,84,82,65,194,81,99,49,97,119,50,59,73,70,93,57,126,156,185,168,205,167,85,89,148,183,125)',
			# "me.statusid" => 1
			# 'me.customerid' => 1047629,
			# 'customersystem.active' => 1
			# 'customersystem.id' => { '!=' => 16 }
			# 'customersystem.deleted' => 0,

		};


	my $rparams = {
		prefetch => [{'customersystem' => [qw/invoicesystems ordersystems market/] },'status','distributor','concept','chaingrp','chainunit','user','warehouse','orderflag','market','ordersystems','invoicesystems','routing',{'back_office_system_customers' => 'system'}],
		# '+select' => [
		# 		{ 'group_concat' => 'system.systemname', -as => 'systemname'  },
		# 		{ 'group_concat' => 'system.systemid', -as => 'systemid'  }
		# ], 
		# '+as' => [qw/systemname systemid/],
		columns => $scolumns,
		# join => {'back_office_system_customers' => 'system'},
		group_by => [qw/me.customerid/],
		order_by => [qw/me.customerid/],
	  };		

	if($ARGV[0]){
		# $rsearch->{'system.systemname'} = { IN => $oisystem }; 
		push(@$header, 'EDI Code');
		push(@$columns, 'edicode');
		$rsearch->{'ordersystems.systemname'} = { IN => $oisystem }; 
		# $rsearch->{'me.statusid'} = 1;
		# $rsearch->{'me.emailgov'} = 0;
		# $rsearch->{'-or'} = [
		# 	# {'invoicesystems.systemname' => { IN => $oisystem }},
		# 	{'ordersystems.systemname' => { IN => $oisystem }},
		# ];
		# $rparams->{'+select'} = [
		# 	\'IF(orderguideflagid="3","",IF(orderguideflagid="1","Y","P")) AS orderguideflagidval'
		# ];
		# $rparams->{'+as'} = [qw/orderguideflagidval/];		
	}else{
		$rparams->{'+select'} = [
			\'IF(orderguideflagid="3","",IF(orderguideflagid="1","Y","P")) AS orderguideflagidval',
		];
		$rparams->{'+as'} = [qw/orderguideflagidval/];		
	}


	my $cnt = $dbix->resultset('Customer')->search($rsearch,$rparams)->count;	

	my $pages = POSIX::ceil($cnt / 100);
	my $csv = Text::CSV->new ( { binary => 1 } ) or die "Cannot use CSV: ".Text::CSV->error_diag ();
	$csv->eol("\n");


	my $dt = DateTime->now(time_zone  => 'America/Chicago');
	# my $filename = 'as.report.' . time() . '.csv';
	my $filename = 'CLM' . $dt->ymd('') . sprintf("%02d", $dt->hour) . sprintf("%02d", $dt->minute) . '.csv';
	if($ARGV[0]){
		$filename = 'CLM_GOV_' . $dt->ymd('') . sprintf("%02d", $dt->hour) . sprintf("%02d", $dt->minute) . '.csv';
	}else{
		# push(@$columns,'orderguideflagidval');
	}
	my $fileExport = '/tmp/' . $filename;

	show $fileExport;



	$csv->column_names(@$columns);
	open my $fhe, ">:encoding(utf8)", $fileExport or die "$fileExport: $!";
	print $fhe join(",", @$header) . "\n";

	$csv->column_names(@$columns);
	open my $fhe, ">:encoding(utf8)", $fileExport or die "$fileExport: $!";
	print $fhe join(",", @$header) . "\n";

	my $objs; my $page = 1;
	my @allobjs;
	# if($FTP || !$ARGV[0]){
		for(my $p=0; $p < $pages; $p++){
			# last;
			$objs = &process($scolumns, $p+1);
			$csv->print_hr($fhe, $_) foreach @$objs;
			push(@allobjs, @$objs);
		}
	# }
	close $fhe or die "$fileExport: $!"; 


	my $dt = DateTime->now();
	my $date = $dt->ymd('-'); 

# URL: sftp.arrowstream.com 
# User: DMA 
# Password: 74Bf^YQe2Z$G 
# /CLM/InboundCLM 
	# my $fileExport = '/tmp/CLM202112091650.csv';
	# my $filename = 'CLM202112091650.csv';

	# my %sftpargs = (user => 'DMA', password => '74Bf^YQe2Z$G', debug => 1, ssh_args => [ cipherx=>'aes256-cbc', options =>[ "MACs +hmac-sha1", "HashKnownHosts yes" ] ]);
	# my %sftpargs = (user => 'DMA', password => '74Bf^YQe2Z$G', debug => 1);	
	# my $sftp = Net::SFTP::Foreign->new('sftp.arrowstream.com', %sftpargs);
	# $sftp->setcwd('/CLM/InboundCLM/');
	# my $remote = '/CLM/InboundCLM/' . $filename;
	# my $local = $fileExport;
	# my $res = $sftp->put($local, $remote, \&callback);

	if(!$ARGV[0]){
# /Presale/InboundPresale
		my $tmpFileExport = $fileExport . '.tmp';
		copy($fileExport,$tmpFileExport);

		
		my $cmd = q~LD_LIBRARY_PATH=/usr/local/lib /usr/local/bin/curl -k -T ~ . $tmpFileExport . q~ -u "DMA:74Bf^YQe2Z\$G"  sftp://sftp.arrowstream.com/CLM/InboundCLM/~;
		show $cmd;
		my $res;
		$res = `$cmd` if $FTP;
		# show $res;

		# sleep(15);

		# my $cmd2 = q~LD_LIBRARY_PATH=/usr/local/lib /usr/local/bin/curl -k -Q "-RNFR /CLM/InboundCLM/~ . $filename . q~.tmp" -Q "-RNTO /CLM/InboundCLM/X~ . $filename . q~" -u "DMA:74Bf^YQe2Z\$G" sftp://sftp.arrowstream.com/CLM/InboundCLM/~;
		my $cmd2 = q~LD_LIBRARY_PATH=/usr/local/lib /usr/local/bin/curl -k -Q "-RENAME /CLM/InboundCLM/~ . $filename . q~.tmp /CLM/InboundCLM/~ . $filename . q~" -u "DMA:74Bf^YQe2Z\$G" sftp://sftp.arrowstream.com/CLM/InboundCLM/~;		
		show $cmd2;
		my $res2 = `$cmd2` if $FTP;
		show $res2;

# http://www.mukeshkumar.net/articles/curl/how-to-use-curl-command-line-tool-with-ftp-and-sftp		
# curl -p - --insecure  "ftp://82.45.34.23:21/CurlPutTest/" --user "testuser:testpassword" -Q "-RNFR /CurlPutTest/testfile.xml"  -Q "-RNTO /CurlPutTest/testfile.xml.tmp"   --ftp-create-dirs

	}elsif($ARGV[0]){
		# Server: ecommftp.zippyyum.com 
		# Username: zippy.dma 
		# Password: X7Vr72u4ueYC8DS9 
		my $cmd = q~LD_LIBRARY_PATH=/usr/local/lib /usr/local/bin/curl -k -T ~ . $fileExport . q~ -u zippy.dma:X7Vr72u4ueYC8DS9 ftp://ecommftp.zippyyum.com/~;
		show $cmd;
		my $res;
		$res = `$cmd` if $FTP;
		show $res;

		
		my @govcustomers = $dbix->resultset('Customer')->search({ 
				# statusid => 1,
				# 'customersystem.deleted' => 0,
				emailgov => 0,
				'ordersystems.systemname' => { IN => $oisystem },
				# 'me.customerid' => '1054670'
			},{
			prefetch => [{'customersystem' => [qw/invoicesystems ordersystems market/] },'status','distributor','concept','chaingrp','chainunit','user','warehouse','orderflag','market','ordersystems','invoicesystems',{'back_office_system_customers' => 'system'}],
			group_by => [qw/me.customerid/],
			order_by => [qw/me.customerid/],
			result_class=>'DBIx::Class::ResultClass::HashRefInflator',
	   });

	# 	my $govcustomers = $dbix->resultset('Customer')->search({ 
	# 			statusid => 1,
	# 			emailgov => 0,
	# 			'system.systemname' => { IN => $oisystem },
	# 		},{
	# 		prefetch => ['status','distributor','concept','chaingrp','chainunit','user','warehouse','orderflag','market','ordersystems','invoicesystems','routing',{'back_office_system_customers' => 'system'}],
	# 		group_by => [qw/me.customerid/],
	# 		order_by => [qw/me.customerid/],
	   # });
	# 	my $query = as_query($govcustomers);
	# 	show $query;
# show $oisystem, scalar(@govcustomers);
# exit;


		my $maildata = q~New store(s) have been added.  Please update your system accordingly.~ . "\n";
		my $maildatahtml = q~<b>New store(s) have been added.  Please update your system accordingly.</b><hr>~;

		if(scalar(@govcustomers) == 0){

			$maildata = q~No store(s) have been added.~ . "\n";
			$maildatahtml = q~<b>No store(s) have been added.</b><hr>~;

		}




			foreach my $customer (@govcustomers){
				next if $customer->{'ordersystems.systemname'} eq 'ERSNC';
				$maildata .= "DMA Customer ID: " . $customer->{customerid} . "\n";
				$maildata .= "Brand: " . $customer->{chaingrp}->{chaingroupname} . "\n";
				$maildata .= "Store #: " . $customer->{customerunitnumber} . "\n";
				$maildata .= "Store Name: " . $customer->{unitname} . "\n";
				$maildata .= "Customer #: " . $customer->{customernumber} . "\n";
				$maildata .= "Email: " . $customer->{email} . "\n";
				$maildata .= "Address: " . $customer->{address} . "\n\n";
				$maildata .= '-----------------------------------------------------' . "\n\n";

				$maildatahtml .= "<b>DMA Customer ID: </b>" . $customer->{customerid} . "<br/>";
				$maildatahtml .= "<b>Brand: </b>" . $customer->{chaingrp}->{chaingroupname} . "<br/>";
				$maildatahtml .= "<b>Store #: </b>" . $customer->{customerunitnumber} . "<br/>";
				$maildatahtml .= "<b>Store Name: </b>" . $customer->{unitname} . "<br/>";
				$maildatahtml .= "<b>Customer #: </b>" . $customer->{customernumber} . "<br/>";
				$maildatahtml .= "<b>Email: </b>" . $customer->{email} . "<br/>";
				$maildatahtml .= "<b>Address: </b>" . $customer->{address} . "<br/><br/>";
				$maildatahtml .= "<hr><br/><br/>";

			}

		
# show $sobjs;

		# foreach my $skey (keys %$sobjs){ 

			# my $toemail = $skey;
			
			my $subject = 'GOV Store(s) Setup: ' . $date;
# show $subject, $maildata;

			my $msg = MIME::Lite->new(
			    From    =>'root@dmadelivers.com',
			    To      => 'matt.kot@dmadelivers.com,oesupport@dmadelivers.com',
			    Bcc => 'dma@ace4it.com,matt.kot@dmadelivers.com,oesupport@dmadelivers.com', # paul.richards@dmadelivers.com
			    Subject => $subject,
			    Type => 'multipart/mixed',
			    # Type    => 'TEXT',
			    Data    => $maildata,
			);
print $maildatahtml . "\n";
print $maildata . "\n";	
print $fileExport . "\n";		
			$msg->attach(
				Type     => 'application/octet-stream',
				Data => $maildata
			);
			$msg->attach(
				Type     => 'text/html',
				Data => $maildatahtml
			);			
			# $msg->attach(
			# 	Type     => 'text/plain',
			# 	# Type     => 'text/plain',
			# 	# Type     => 'application/octet-stream',
			# 	Path => $fileExport,
			# );

			$msg->send;

			foreach my $gc (@govcustomers){
				$dbix->resultset('Customer')->search( { customernumber => $gc->{customernumber} } )->update({ emailgov => 1 });	
			}



		# }

	}

	my $subject = 'ArrowStream Customer Export: ' . $date;
	if($ARGV[0]){
		$subject = 'GOV Customer Export: ' . $date;
	}
	my $msg = MIME::Lite->new(
	    From    =>'root@dmadelivers.com',
	    # To 		=> 'dma@ace4it.com',
	    To      =>'matt.kot@dmadelivers.com, oesupport@dmadelivers.com',
	    # Cc      =>'',
	    Bcc => 'dma@ace4it.com,matt.kot@dmadelivers.com,oesupport@dmadelivers.com',
	    Subject => $subject,
	    Type    => 'TEXT',
	    Data    => $subject,
	);

	if($ARGV[0]){

		# my $zip = Archive::Zip->new();


		# $zip->addFile($fileExport, $filename);
		# $zip->writeToFileNamed($fileExport . '.zip');

		# $msg->attach(
		# 	    Type     =>'application/zip',
		# 	    Path     => $fileExport . '.zip',
		# 	    Filename => $filename . '.zip'
		# );
	}

	$msg->send;


	# my $stat = new File::Stat($file);
	# print "UPLOAD: $file " . scalar localtime( $stat->mtime ) . " " . $stat->size . "bytes\n";

	# # exists, not zero-bytes, and is less than 1 minute old
	# if(-e $file && -s $file){   # && time() < $stat->mtime + 60 
	# 	my $ftp = Net::FTP->new($host, Passive => 1, Debug => 1) or die "Cannot connect to some.host.name: $@";
	# #	my $ftp = Net::FTP->new($host, passive => 1, debug => 1, port => 2322, ssh_args => { port => '2322' }) or die "Cannot connect to some.host.name: $@";
		
	# 	$ftp->login($user,$pass) or die "Cannot login ", $ftp->message;
	# 	$ftp->cwd($dir) or die "Cannot change working directory ", $ftp->message;
	# 	my $uploaded = $ftp->put($file) or die "put failed ", $ftp->message;
	# 	$ftp->quit;
		
	# }else{
	# 	print "ERROR: File $file does not exist or is zero bytes or is too old.\n";
	# }


}


sub callback {
    my($sftp, $data, $offset, $size) = @_;
    print "Read $offset / $size bytes\n";
}

sub process{
	my $columns = shift;
	my $page = shift || 1;




	my $oisystem = [];
	if($ARGV[0]){
		my @oisystem = split(/,/, $ARGV[0]);
		$oisystem = \@oisystem;
	}

# show $page, $oisystem, $columns;
	my $rsearch = {

			"chaingrp.chaingroupid" => \'NOT IN(136,1,2,3,102,111,158,54,116,208,118,145,5,6,224,21,8,9,56,146,121,10,173,212,127,11,105,12,152,13,14,83,15,103,178,75,16,202,18,19,58,20,88,22,94,64,74,53,24,7,66,110,25,104,33,124,92,26,154,69,55,140,91,180,96,27,160,157,28,162,29,30,176,144,32,117,113,181,141,76,108,100,123,106,62,161,98,61,165,166,35,122,79,131,209,109,163,36,86,112,149,38,120,159,190,78,172,153,41,43,44,45,95,60,46,47,90,48,174,143,84,82,65,194,81,99,49,97,119,50,59,73,70,93,57,126,156,185,168,205,167,85,89,148,183,125)',
			# "market.customwarehousenumber" => 202062,
			# "me.marketid" => 4
			# 'me.customerid' => 1047629,
			# 'customersystem.active' => 1,
			# 'customersystem.deleted' => 0,
		
		};


	my $rparams = {
		prefetch => [{'customersystem' => [qw/invoicesystems ordersystems market/] },'status','distributor','concept','chaingrp','chainunit','user','warehouse','orderflag','ordersystems','invoicesystems','market','routing'],
		# '+select' => [
		# 		{ 'group_concat' => 'system.systemname', -as => 'systemname'  },
		# 		{ 'group_concat' => 'system.systemid', -as => 'systemid'  }
		# ], 
		# '+as' => [qw/systemname systemid/],
		columns => $columns,
		# join => {'back_office_system_customers' => 'system'},
		group_by => [qw/me.customerid/],
		order_by => [qw/me.customerid/],
		result_class=>'DBIx::Class::ResultClass::HashRefInflator',
		rows => 100,
		page => $page,
	};
	# $rsearch->{'me.customerid'} = '1054670';
	if($ARGV[0]){
		$rsearch->{'-or'} = [
			# {'invoicesystems.systemname' => { IN => $oisystem }},
			{'ordersystems.systemname' => { IN => $oisystem }},
		];
		# $rparams->{'+select'} = [
		# 	\'IF(orderguideflagid="3","",IF(orderguideflagid="1","Y","P")) AS orderguideflagidval'
		# ];
		# $rparams->{'+as'} = [qw/orderguideflagidval/];		
	}else{
		$rparams->{'+select'} = [
			\'IF(orderguideflagid="3","",IF(orderguideflagid="1","Y","P")) AS orderguideflagidval'
		];
		$rparams->{'+as'} = [qw/orderguideflagidval/];
		# push(@$columns, 'orderguideflagidval');
		# $rparams->{'+columns'} = [qw/orderguideflagidval/];
		# push(@{$rparams->{columns}},'orderguideflagidval');

	}




	my @res = $dbix->resultset('Customer')->search($rsearch,$rparams);

	# foreach my $r (@res){
	# 	show $r->{orderguideflagidval} if $r->{orderguideflagidval};
	# }

	my @objs;
	foreach my $r (@res){
		# next unless $r->{customerid} == 2000;
		my $obj;
		# show $r;

		my @newcs;
		foreach my $cs (@{$r->{customersystem}}){
			if(!$cs->{deleted}){
				push(@newcs, $cs);
			}
		}
		$r->{customersystem} = \@newcs;

		# show $r;

		foreach my $col (@$columns){
			# show $col;
			if($col =~ /^(.*?)\.(.*?)$/){
				my ($tb,$cl) = ($1,$2);
				# show $tb, $cl;
				my $key = $tb . '.' . $cl;
				# show $key;
				my $cval = $r->{$tb}->{$cl}; $cval =~ s/(\s|\t)+$//;
				$obj->{$key} = $cval;
			}else{
				my $cval = $r->{$col}; $cval =~ s/(\s|\t)+$//;
				$obj->{$col} = $cval;
			}
		}
		if(!$ARGV[0]){
			if($r->{orderguideflagidval}){
				$obj->{orderguideflagidval} = $r->{orderguideflagidval};
			}			
		}else{
			$obj->{edicode} = $r->{'distributor.edicode'} || $r->{distributor}->{edicode} || $r->{'distributor.distributorcode'} || $r->{distributor}->{distributorcode};
		}
		my $fields  = ["PO","ACK","CONF","SALES INVOICE","CREDIT","PA","OG"];

		# my $systemfiles = exists($r->{customersystem})

		if(scalar(@{ $r->{customersystem} })){

			foreach my $cs (@{ $r->{customersystem} }){
				my $csobj = dclone($obj);				
				$csobj->{systemtype} = $cs->{systemtype} == 1 ? 'BOH' : 'STORE ORDER';
				$csobj->{systemname} = $cs->{systemtype} == 1 ? ($cs->{invoicesystems}->{systemname} || $cs->{ordersystems}->{systemname}): ($cs->{ordersystems}->{systemname} || $cs->{invoicesystems}->{systemname});
				$csobj->{customwarehousename} = $cs->{market}->{customwarehousename};
				$csobj->{customwarehousenumber} = $cs->{market}->{customwarehousenumber};
				$csobj->{filecode} = $cs->{filecode};
				$csobj->{accountingcode} = $cs->{accountingcode};
				
				# show $cs;
				my $systemfiles = exists($cs->{systemfiles}) && $cs->{systemfiles} =~ /^\[/ ?  decode_json($cs->{systemfiles}) : $cs->{systemfiles} ? [$cs->{systemfiles}] : [];
				# show $systemfiles;
				foreach my $f (@$fields){
					$f =~ s/\s|\W//g;
					my $k = 'systemfiles' . lc($f);
					$csobj->{$k} = 'N';
				}
				foreach my $sf (@$systemfiles){
						$sf =~ s/\s|\W//g;
						my $k = 'systemfiles' . lc($sf);
						$csobj->{$k} = 'Y';
						# my $match = lc($f) eq  lc($sf) ? 'Y' : 'N';
						# $r->{$k} = $match eq 'Y' || $r->{$k} eq 'Y' ? 'Y' : 'N';
						# show $fields, $f, $sf, $k, $match, $r->{$k};
						#$csobj->{$k} = $match; # $r->{$k};
				}



				# show $csobj;
				push(@objs, $csobj);
			}
	


		}else{

			# show $obj;
			push(@objs, $obj);

		}



		# show $obj if $obj->{edicode};
# show $obj if $obj->{'distributor.edicode'};

	}

	# foreach my $o (@objs){
	# 	show $o->{orderguideflagidval} if $o->{orderguideflagidval};
	# }
# show \@objs;
	return \@objs;

}

  sub as_query{
    my $rs = shift;

    my $pretty = SQL::QueryBuilder::Pretty->new
        if SQL::QueryBuilder::Pretty->can('new');

    sub format_sql{
        my ($sql, @values) = @_;
        $sql =~ s/\?/_sql_value(shift(@values))/ge;
        return $pretty
            ? $pretty->print($sql)
            : $sql;
    }

    sub _sql_value{
        my $value = shift;

        if (ref $value && ref $value eq 'ARRAY'){
            $value = $value->[1];
        }

        if (!defined $value){
            return 'NULL';
        }
        elsif ($value =~ /^\d+(?:\.\d+)?$/){
            return $value;
        }
        else{
            return "'$value'";
        }
    }

    sub format_resultset{
        my ($rs) = @_;
        my $query = ${ $rs->as_query };
        return format_sql(@{$query});
    }
    return format_resultset($rs);

};


1;