#!/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 Storable qw(dclone);
our $FTP = 1;

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	longitude	latitude	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	longitude	latitude	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"];

	if($ARGV[0]){
		# push(@$header,"Order Guide Flag");
	}else{
		push(@$header,"Order Guide Flag");		
	}

	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' => 11065
#			'customersystem.deleted' => 0,

		};


	my $rparams = {
		prefetch => [{'customersystem' => [qw/invoicesystems ordersystems market/] },'status','distributor','concept','chaingrp','chainunit','user','warehouse','orderflag','market','ordersystems','invoicesystems','routing',{customersystem => [qw/ordersystems invoicesystems/]},{'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;	
show $cnt;
	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";


	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/~;
		# my $cmd = q~LD_LIBRARY_PATH=/usr/local/lib /usr/local/bin/curl -k -T ~ . $tmpFileExport . q~ -u "DMA:jtO\@OC\&Y\@DppR4Ns"  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`;
		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/~;
		my $cmd = q~LD_LIBRARY_PATH=/usr/local/lib /usr/local/bin/curl -k -T ~ . $fileExport . q~ -u zippy.dma:jtO\@OC\&Y\@DppR4Ns ftp://ecommftp.zippyyum.com/~;
		show $cmd;
		my $res;
		$res = `$cmd` if $FTP;
		show $res;

		
		my @govcustomers = $dbix->resultset('Customer')->search({ 
				statusid => 1,
				emailgov => 0,
				'ordersystems.systemname' => { IN => $oisystem },
				# 'me.customerid' => 11065
#				'customersystem.deleted' => 0,
			},{
			prefetch => ['status','distributor','concept','chaingrp','chainunit','user','warehouse','orderflag','market','ordersystems','invoicesystems',{'back_office_system_customers' => 'system'},{customersystem => [qw/ordersystems invoicesystems/]}],
			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;
# show 'process ' . $page;



	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' => 11065
			# 'me.customernumber' => {'IN' => [722675946,722675947,722675948]},
			# 'me.customernumber' => {'IN' => [722675946,722675947,722675948]},
#			'customersystem.deleted' => 0,
		};


	my $rparams = {
		prefetch => [{'customersystem' => [qw/invoicesystems ordersystems market/] },'status','distributor','concept','chaingrp','chainunit','user','warehouse','orderflag','ordersystems','invoicesystems','market','routing',{customersystem => [qw/ordersystems invoicesystems/]}],
		# '+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';
	# $rsearch->{'me.customerid'} = '1052148';
	 
	if($ARGV[0]){
		# $rsearch->{'-or'} = [
		# 	# {'invoicesystems.systemname' => { IN => $oisystem }},
		# 	{'ordersystems.systemname' => { IN => $oisystem }},
		# ];
		$rsearch->{'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');

	}

show $rsearch;


	my @res = $dbix->resultset('Customer')->search($rsearch,$rparams);
	foreach my $r (@res){
# show $r;		
		my $hasis = $r->{invoicesystems} ? 1 : 0;
		my $hasos = $r->{ordersystems} ? 1 : 0;
			# $r->{invoicesystems} ||= {};
			# $r->{ordersystems} ||= {};
		if((!$hasis 
			|| !$hasos) 
			&& scalar(@{$r->{customersystem}})){
			my $osystemname = $r->{'ordersystems.systemname'};
			my $isystemname = $r->{'invoicesystems.systemname'};
			foreach my $cs (@{$r->{customersystem}}){

				if($cs->{ordersystems} && $cs->{ordersystems}->{systemname}){
					$r->{'ordersystems.systemname'} = $cs->{ordersystems}->{systemname};
				}
				if($osystemname ne $r->{'ordersystems.systemname'}){
					
				}
				if($cs->{invoicesystems} && $cs->{invoicesystems}->{systemname}){
					$r->{'invoicesystems.systemname'} = $cs->{invoicesystems}->{systemname};
				}	
				if($isystemname ne $r->{'invoicesystems.systemname'}){
					
				}							
			}
		}
	}
# show @res;
	# foreach my $r (@res){
	# 	show $r->{orderguideflagidval} if $r->{orderguideflagidval};
	# }

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



		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"];
if(scalar(@{ $r->{customersystem} })){

			foreach my $cs (@{ $r->{customersystem} }){
				my $csobj = dclone($obj);				
# show $cs->{invoicesystems}->{systemname}, $cs->{ordersystems}->{systemname};				
				$csobj->{systemtype} = $cs->{systemtype} == 1 ? 'BOH' : 'STORE ORDER';
				if($ARGV[0]){
					$csobj->{systemname} = $cs->{systemtype} == 1 ? $cs->{invoicesystems}->{systemname} : $cs->{systemtype} == 0 ? $cs->{ordersystems}->{systemname} : undef;
				}else{
					$csobj->{systemname} = $cs->{systemtype} == 1 ? ($cs->{invoicesystems}->{systemname} || $cs->{ordersystems}->{systemname}): ($cs->{ordersystems}->{systemname} || $cs->{invoicesystems}->{systemname});					
				}
					# $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};
				}

my $is = $cs->{invoicesystems}->{systemname};
my $os = $cs->{ordersystems}->{systemname};
$csobj->{'ordersystems.systemname'} = $cs->{ordersystems}->{systemname};
$csobj->{'invoicesystems.systemname'} = $cs->{invoicesystems}->{systemname};
# show $os, $is;
				# show $csobj;
				if ( grep( /^$is$/, @$oisystem ) || grep( /^$os$/, @$oisystem )) {
					push(@objs, $csobj);
				}
			}
	


		}else{

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

		}


# 		# show $obj if $obj->{edicode};
# # show $obj if $obj->{'distributor.edicode'};
# 		push(@objs, $obj);
	}

	# 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;