#!/usr/bin/perl -Ilibs

use strict;
use warnings;

use JSON ();
use POSIX qw(ceil);

use API::Config;

use lib ($API::Config::Config->{'Website_libs'});

use ECS::DB ();
use ECS::dbConfig;
use ECS::Shipping ();
use ECS::Shipping::Box ();
use ECS::Shipping::Courier ();
use ECS::Shipping::Shipment ();
use ECS::Search::ElasticSearch6 ();


package API::ShippingAdmin;

###############################################################################

sub searchShipments {
	my $self = shift;
	my $p = shift;
	
	$p->{'DBH'} = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysql'}});
	
	my $result = {
		'success'		=>	1, 
		'navigation'	=>	$p->{'action'} || '',
		'shipments'		=>	[]
	};
	
	if ( !$p->{'action'} or !$p->{'params'} ) {
		$result->{'success'} = 0;
		return $result;
	}
	
	$p->{'shipmentIDs'} = getSearchShipmentIDs($p);
	
	# No search results
	if ( !$p->{'shipmentIDs'} or !scalar(@{$p->{'shipmentIDs'}}) ) {
		$result->{'success'} = 0;
		return $result;
	}
	
	return getShipments(undef, $p);
}

# Requires: get the shipmentIDs for the requested search
# - $p->{'action'}
# - $p->{'params'} 
sub getSearchShipmentIDs {
	my $p = shift;
	
	# choose the table
	my $table = 'shipment';
	$table = 'orderShipping' if $p->{'action'} eq 'searchByOrder';
	
	# build the sql for the defined params
	my $params = '';
	foreach my $key (keys %{$p->{'params'}}) {
		next if !$p->{'params'}->{$key};
		
		my $text = join ',', map { qq/'$_'/} split(',', $p->{'params'}->{$key});
		
		$params .= $key.' IN ('.$text.') AND ';
	}
	$params =~ s/AND $//;
	
	# return the results
	my ($query, $sth, $rd);
	
	my $DBHr = $p->{DBHr}||$p->{DBH};
	$query = 'SELECT shipmentID FROM '. $table.' WHERE '.$params;

	eval {
		$sth = $DBHr->prepare($query);
		$sth->execute();
		$rd = $sth->fetchall_arrayref({});
	};

	return undef if $@ or $sth->err;

	my @results;
	
	foreach my $shipment (@{$rd}) {
		push @results, $shipment->{'shipmentID'};
	}
	
	return \@results;
}

# Requires:
# - shipmentIDs: list of the shipmentID
sub getShipmentsByID {
	my $p = shift;

	return undef if !$p->{'shipmentIDs'};
	
	my $query = 'SELECT
		shipment.*,
		shipmentBox.shipmentBoxID,
		shipmentBox.shippingBoxID,
		shipmentBox.weight,
		shipmentBox.productKey,
		IFNULL(shippingBox.boxLabel, "Custom") AS boxLabel, 
		shippingBox.barcode,
		shippingBox.length, 
		shippingBox.width, 
		shippingBox.height, 
		shippingBox.allowIntl,
		shippingBox.resized,
		CONCAT(employeeInfo.nameFirst," ", employeeInfo.nameLast) AS employeeName	
	FROM shipment
		JOIN shipmentBox USING(shipmentID)
		JOIN shippingBox USING(shippingBoxID)
		LEFT JOIN shippingBoxComponent USING(shippingBoxID)
		LEFT JOIN employeeInfo ON shipment.employeeID = employeeInfo.employeeID
	WHERE shipment.active = "Y" AND shipment.shipmentID IN ('.join(',', @{$p->{'shipmentIDs'}}).')
	GROUP BY shipmentBox.shipmentBoxID';

	my ($sth, $rd);
	
	my $DBHr = $p->{DBHr}||$p->{DBH};
	eval {
		$sth = $DBHr->prepare($query);
		$sth->execute();
		$rd = $sth->fetchall_arrayref({});
	};
	
	return undef if $@ or $sth->err;
	
	my $shipments;
	
	local $p->{'couriers'} = ECS::Shipping::Courier::getCouriersConfig($p);
	
	foreach my $row ( @{$rd} ) {
		
		my $shipmentID = $row->{'shipmentID'};
		
		local $p->{'shipmentID'} = $shipmentID;
		local $p->{'input'}->{'shippingCourierCode'} = $row->{'shippingCourierCode'};
		local $p->{'input'}->{'shipping_country'} = $row->{'countryCode'};
		local $p->{'courier'} = $p->{'couriers'}->{$row->{'shippingCourierCode'}}; 
		
		if ( !defined $shipments->{$shipmentID} ) {
			$shipments->{$shipmentID} = {
				'shipmentID'	=>	$row->{'shipmentID'},
				'shipmentKey'	=>	$row->{'shipmentKey'},
				'shippingCourierCode'	=>	$row->{'shippingCourierCode'},
				'countryCode'	=>	$row->{'countryCode'},
				'totalProductWeight'	=>	$row->{'totalProductWeight'},
				'createdBy'		=>	$row->{'employeeName'} || 'E#'.($row->{'createdByOrderID'} || ''),
				'tsCreated'		=>  $row->{'tsCreated'},
				'keyType'		=>	$row->{'keyType'},
				'shipmentChangeLogs'	=>	getShipmentLogs($p),
				'intl'			=>	ECS::Shipping::Courier::isInternational($p),
			};
		}
		
		my %products = map { m/^(\d+)\[(\w+)\]/; $2 => $1 } split('_', $row->{'productKey'});
		
		local $p->{'shippingBoxID'} = $row->{'shippingBoxID'};
		
		push @{$shipments->{$shipmentID}->{'boxes'}}, {
			'shipmentBoxID'	=>	$row->{'shipmentBoxID'},
			'shippingBoxID'	=>	$row->{'shippingBoxID'},
			'weight'		=>	$row->{'weight'},
			'productKey'	=>	$row->{'productKey'},
			'boxLabel'		=>	$row->{'boxLabel'},
			'length'		=>	$row->{'length'} || '',
			'width'			=>	$row->{'width'} || '',
			'height'		=>	$row->{'height'} || '',
			'allowIntl'		=>	$row->{'allowIntl'} || 0,
			'resized'		=>	$row->{'resized'} || 0,
			'boxComponents'	=>	ECS::Shipping::Box::getBoxComponents($p),
			'products'		=>	\%products,
		};
	}
	
	my @results = values %{$shipments};
	
	return \@results;
}

sub getShipmentLogs {
	my $p = shift;
	
	return [] if !$p->{'shipmentID'};
	
	my ($sth, $rd);
	my $DBHr = $p->{DBHr}||$p->{DBH};
	
	eval {
		$sth = $DBHr->prepare(
			"SELECT 
				log.ts AS date,
				IFNULL(employeeInfo.username, CONCAT('E#', log.orderID) )AS doneBy,
				log.shipmentBoxID,
				oldBox.length AS oldBoxLength,
				oldBox.width AS oldBoxWidth,
				oldBox.height AS oldBoxHeight,
				newBox.length AS newBoxLength,
				newBox.width AS newBoxWidth,
				newBox.height AS newBoxHeight,
				log.field,
				log.oldValue,
				log.newValue 
			FROM shipmentChangeLog AS log
			LEFT JOIN employeeInfo USING(employeeID)
			LEFT JOIN shippingBox AS oldBox ON log.field = 'shippingBoxID' AND log.oldValue = oldBox.shippingBoxID
			LEFT JOIN shippingBox AS newBox ON log.field = 'shippingBoxID' AND log.newValue = newBox.shippingBoxID

			WHERE log.shipmentID = ?"
		);
		$sth->execute($p->{'shipmentID'});
		$rd = $sth->fetchall_arrayref({});
	};
	
	foreach my $log (@{$rd}) {
		if ($log->{'field'} eq 'shippingBoxID') {
			$log->{'oldValue'} = join('x', ( $log->{'oldBoxLength'}, $log->{'oldBoxWidth'}, $log->{'oldBoxHeight'} )) if $log->{'oldValue'};
			$log->{'newValue'} = join('x', ( $log->{'newBoxLength'}, $log->{'newBoxWidth'}, $log->{'newBoxHeight'} )) if $log->{'newValue'};		
		}
	}
	
	return [] if $@ || $sth->err || !scalar(@{$rd});
	
	return $rd;
}

# API request to get all the boxes per each courier
sub getAllCourierShippingBoxes {
	my $self = shift;
	shift;
	
	my $p;
	
	$p->{'DBH'}  = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysql'}});
	$p->{'DBHr'} = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysqlSlave'}, readonly => 1});
	
	$p->{'allRates'} = 1;
	
	ECS::Shipping::Box::getBoxes($p);
	ECS::Shipping::Courier::getCouriers($p);
	ECS::Shipping::Courier::getBoxes($p);
	
	my $couriers;
	
	foreach my $shippingCourierCode (keys %{$p->{'couriers'}}) {
		local $p->{'shippingBoxes'} = $p->{'couriers'}->{$shippingCourierCode}->{'boxes'};
		$couriers->{$shippingCourierCode} = formatShippingBoxes($p);
	}
	
	return $couriers;
	
	return 1;
}
# API request to get extra couriers config
sub getCouriersConfig {
	my $self = shift;
	shift;

	my $p;
	
	$p->{'couriersConfig'} = ECS::Shipping::Courier::getCouriersConfig($p);
	
	my $couriers;
	
	foreach my $shippingCourierCode (keys %{$p->{'couriersConfig'}}) {
		local $p->{'courier'} = $p->{'couriersConfig'}->{$shippingCourierCode}; 
        
        my $maxBoxWeight;
        
        # Problem: Maximum Box Weights for Freight are based on freightRate records
        # So, We need to run a database query to get this number.
        # Note: this is not the best solution to this problem.
        if ($shippingCourierCode eq 'FREIGHT') {
            
            $p->{'DBH'}  ||= ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysql'}});
            $p->{'DBHr'} ||= ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysqlSlave'}, readonly => 1});
            
            my $getWeight = $p->{'DBHr'}->prepare(
                'SELECT maxWeight FROM freightRate ORDER BY maxWeight DESC LIMIT 1'
            );
            
            $getWeight->execute();
            
            ($maxBoxWeight) = $getWeight->fetchrow_array;
            $maxBoxWeight += 0;
        }
		
		$couriers->{$shippingCourierCode} = {
			'maxBoxWeight'			=>	$maxBoxWeight || ECS::Shipping::Courier::getMaxBoxWeight($p),
			'maxBoxMultiWeight'		=>	$maxBoxWeight || ECS::Shipping::Courier::getMaxBoxMultiWeight($p),
			'domesticDestinations'	=>	ECS::Shipping::Courier::getDomesticDestinations($p) 
		}; 
	}
	
	return $couriers;
	
	return 1;
}

# format the courier shipping boxes
# return an array of the parents, each with its own array of cut-down boxes
sub formatShippingBoxes {
	my $p = shift;
	
	return if !$p->{'shippingBoxes'};
	
	my @parentBoxes;
	my $childBoxes;
	
	foreach my $boxID ( sort {$a<=>$b} keys %{$p->{'shippingBoxes'}} ) {
		my $box = $p->{'boxes'}->{$boxID};
		
		# build the cut-down boxes separately from the parents
		if ($box->{'resizedParentID'}) {
			push @{$childBoxes->{$box->{'resizedParentID'}}}, $box;
		} else {
			push @parentBoxes, $box;
		}
	}
	
	# assign the correct children to the parent boxes
	foreach my $parentBox ( @parentBoxes ) {
		next if !$childBoxes->{$parentBox->{'shippingBoxID'}};
		
		$parentBox->{'resizedBoxes'} = [ sort {$a->{'height'} <=> $b->{'height'}} @{$childBoxes->{$parentBox->{'shippingBoxID'}}} ];
	}
	
	return \@parentBoxes;
}

sub saveShipments {
	my $self = shift;
	my $p = shift;
	
	return {'success' => 0} if !$p->{'shipments'} or !$p->{'employeeID'};
		
	$p->{'DBH'}  = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysql'}});
	$p->{'DBHr'} = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysqlSlave'}, readonly => 1});
	
	my $response = {'success' => 1};

	my @shipmentIDs;
	
	foreach my $shipment ( @{$p->{'shipments'}} ) {
		
		next if !$shipment->{'shipmentID'};
		
		# Shipment was marked to be removed: don't delete, just set as inactive
		if ( defined $shipment->{'remove'} and $shipment->{'remove'} ) {
			my $sth;
			
			eval {
				$sth = $p->{'DBH'}->prepare('UPDATE shipment SET active = "N" WHERE shipmentID = ?');
				$sth->execute($shipment->{'shipmentID'});
				
				$sth = $p->{'DBH'}->prepare('INSERT INTO shipmentChangeLog (shipmentID, field, oldValue, newValue, employeeID) VALUES (?,?,?,?,?)');
				$sth->execute(
					$shipment->{'shipmentID'},
					'active',
					'Y',
					'N',
					$p->{'employeeID'}
				);
				
				# Clear the Elastic Search entry
				ECS::Search::ElasticSearch6::delete({
					'shipment'   => 'shipment_'.$shipment->{'shipmentID'},
				});
			};	
			next;
		}
		
		next if !scalar(@{$shipment->{'shipmentBoxes'}});
		
		local $p->{'shipment'}->{'shipmentID'} = $shipment->{'shipmentID'};
		local $p->{'shipment'}->{'newShipmentBoxes'} = $shipment->{'shipmentBoxes'};
		ECS::Shipping::Shipment::updateShipmentBoxes($p);
		
		# update shipment in Elastic Search
		my $shipment = ECS::Shipping::Shipment::getShipmentByID({
			'DBH' 		=>	$p->{'DBH'},
			'shipmentID'=>	$shipment->{'shipmentID'}
		});
		
		use POSIX qw(ceil);
		
		ECS::Search::ElasticSearch6::setShipment({
			'shipmentID'	=> 	$shipment->{'shipmentID'},
			'shipmentKey'	=>	$shipment->{'shipmentKey'},
			'mfgShipmentKey'=>	$shipment->{'mfgShipmentKey'},
			'keyType'		=>	$shipment->{'keyType'},
			'boxVolume'		=>	ceil($shipment->{'boxVolume'}),	
		});
		
		push @shipmentIDs, $shipment->{'shipmentID'};
		
	}
	
	$response->{'shipmentIDs'} = join(',', @shipmentIDs);
	
	return $response;
}

# Creates new shipment quote by part(ES#) only; used exclusively in Shipping Admin
# Formats the input data and calls the ECS libs to do the create operations
# Requires:
# - employeeID
# - shipmentBoxes
# - shippingCouriers
# - shippingCountries
sub addShipmentQuoteByPart {
	my $self = shift;
	my $p = shift;
	
	return {'success' => 0} if !$p->{'employeeID'} or !$p->{'shipmentBoxes'} or !$p->{'shippingCouriers'} or !$p->{'shippingCountries'};
	
	$p->{'DBH'}  = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysql'}});
	$p->{'DBHr'} = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysqlSlave'}, readonly => 1});
	
	my $result = { 'success'	=>	1 };
	my $products;	
	
	foreach my $shipmentBox ( @{$p->{'shipmentBoxes'}} ) {
		foreach my $product ( sort { $a->{'key'} <=> $b->{'key'} } @{$shipmentBox->{'products'}} ) {
			
			$shipmentBox->{'productKey'} .= $product->{'qty'}.'['.$product->{'key'}.']_';
			# add products to the main hash for the general shipmentKey
			$products->{$product->{'key'}} += $product->{'qty'};
		}
		$shipmentBox->{'productKey'} =~ s/_$//;
		$shipmentBox->{'keyType'} = 'part';
	}
	
	$p->{'keyType'} = 'part';
	
	$p->{'shipmentKeyByPart'} = join( '_', map {$products->{$_}.'['.$_.']'} sort { $a <=> $b } keys %{$products} ) ;
	
	# build the Mfg Shipment Key from the available productIDs
	my $partnumberCount = 0;
	my ( $sth, $rd );	
	
	eval {
		$sth = $p->{'DBHr'}->prepare("SELECT productID, partnumber FROM product WHERE productID IN (".join( ',', (keys %{$products}) ) .")");
		$sth->execute();
		$rd = $sth->fetchall_arrayref({});
	};
	
	foreach my $item (@{$rd}) {
		$item->{'qty'} = $products->{$item->{'productID'}};
		$partnumberCount++ if $item->{'partnumber'};
	}
	return {'success' => 0} if scalar(keys %{$products}) != $partnumberCount;
	
	$p->{'shipmentKeyByMfg'} = ECS::Shipping::Shipment::buildShipmentKeyByMfg($rd);
	
	$p->{'shipmentIDs'} = ECS::Shipping::Shipment::addShipmentQuoteByPart($p);
	
	return getShipments(undef, $p);
}

# called both directly and indirectly by Shipping Admin
sub getShipments {
	my $self = shift;
	my $p = shift;

	return {'success' => 0} if !$p->{'shipmentIDs'};
	
	$p->{'DBH'}  = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysql'}}) if !$p->{'DBH'};
	$p->{'DBHr'} = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysqlSlave'}, readonly => 1}) if !$p->{'DBHr'};
	
	my $result = {
		'success'	=>	1 
	};
	
	$result->{'shipments'} = getShipmentsByID({
		'DBH'			=>	$p->{'DBH'},
		'DBHr'			=>	$p->{'DBHr'},
		'shipmentIDs'	=>	$p->{'shipmentIDs'}
	}) || ();
	
	$result->{'navigation'} = $p->{'action'} || '';	
	
	return $result;
}

sub cleanIntlBoxQuoting {
	my $self = shift;
	my $input = shift;
	
	my $p = {
		'DBH'			=>	ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysql'}}),
		'DBHr'			=>	ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysqlSlave'}, readonly => 1 }),
		'shippingBoxID'	=>	$input->{'shippingBoxID'}, 
		'employeeID'	=>	$input->{'employeeID'}
	};
	
	my $result = {
		'success' => 1,
		'error' => undef,
	};
	
	my $query = "SELECT DISTINCT shipment.shipmentID, shippingCourierCode, countryCode 
		FROM shipment 
			JOIN shipmentBox USING(shipmentID) 
		WHERE shipment.active = 'Y' AND shipment.countryCode <> 'US' AND shipmentBox.shippingBoxID = ?";
	
	my ($sth, $shipments);
	
	eval {
		$sth = $p->{'DBHr'}->prepare($query);
		$sth->execute($p->{'shippingBoxID'});
		$shipments = $sth->fetchall_arrayref({});
	};
	
	if ($@ or $sth->err) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Cannot retrieve shipping quoting information for this box.';
		return $result;
	} 
	
	if (!scalar @{$shipments}) { # the shipping box does not have any non-US quoting history yet; there's no data to clean
		$result->{'success'} = 1;
		undef $result->{'error'};
		return $result;
	}
	
	my $shipmentsQuery = '';
	my @elasticSearchShipments;
	
	# fields for logging the removal action
	my $insertLogQuery = 'INSERT INTO shipmentChangeLog (shipmentID, field, oldValue, newValue, employeeID) VALUES ';
	my @paramsLog;
	
	foreach my $shipment (@{$shipments}){
		# get courier information
		local $p->{'allRates'} = 0;
		local $p->{'input'}->{'shippingCourierCode'} = $shipment->{'shippingCourierCode'};
		local $p->{'input'}->{'shipping_country'} = $shipment->{'countryCode'};
		ECS::Shipping::Courier::getCouriers($p);
		
		local $p->{'courier'} = $p->{'couriers'}->{$shipment->{'shippingCourierCode'}};
		
		next if !ECS::Shipping::Courier::isInternational($p);
			
		$shipmentsQuery .= $shipment->{'shipmentID'}.',';
		
		push ( @elasticSearchShipments, {
			id => 'shipment_' . $shipment->{'shipmentID'},
			index => 'estore',
			type => 'shipment'
		});
		
		# build the insert data for logging the deactivation
		$insertLogQuery .= "(?,?,?,?,?),";
		push @paramsLog, ($shipment->{'shipmentID'}, 'active', 'Y', 'N', $p->{'employeeID'});
	}
	$shipmentsQuery =~ s/,$//;
	$insertLogQuery =~ s/,$//;
	
	if ( !$shipmentsQuery ) { # the non-US history turned out to not be international (US territories etc.)
		$result->{'success'} = 1;
		undef $result->{'error'};
		return $result;
	}
	
	eval {
		# deactivate the quoting history
		$sth = $p->{'DBH'}->prepare('UPDATE shipment SET active = "N" WHERE shipmentID IN ('.$shipmentsQuery.')');
		$sth->execute();
		
		# log the deactivation of the quoting history
		$sth = $p->{'DBH'}->prepare($insertLogQuery);
		$sth->execute(@paramsLog);
	};
	
	if ($@ or $sth->err) {
		$result->{'success'} = 0;
		$result->{'error'} = 'Cannot clear international shipping quoting history for this box.';
		return $result;
	} 
		
	ECS::Search::ElasticSearch6::bulk_delete(@elasticSearchShipments);
	
	return $result;
}

#################################################################################
# Imports Postal data into table courierDIT

sub importPostalDIT {

	my $self = shift;
	my $p = shift;	
	my $dataToPush = [];
	my $distortedColumnOrder = 0;
	my $corruptData = [];
	my $firstRow = 1;
	my $validCodes = JSON->new->allow_nonref->decode($p->{'validCodes'});
	my $validShippingServiceCodes = {};
	
	foreach my $courier ( keys %$validCodes ){
		my %hash = map { lc($_) => $validCodes->{$courier}->{$_} } keys %{$validCodes->{$courier}};
		$validShippingServiceCodes->{$courier} = \%hash;	
	}
	
	$p->{'DBH'}  = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysql'}});
	$p->{'DBHr'} = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysqlSlave'}, readonly => 1});
	my $result;
	
	my $selectSQL = "
		SELECT * from courierDaysInTransit where 
			zip = ? 
			AND shippingCourierCode = ?
			AND shippingServiceCode =? 
	";
	my $selectRow = $p->{'DBHr'}->prepare($selectSQL);
	
	foreach my $row ( @{$p->{'filedata'}} ){
	
		my ( $postalCode, $courier, $service, $zone, $DIT ) = map{ $_ =~ s/^\s+|\s+$//g; $_ } @$row;
		if ( $firstRow ){
			$firstRow = 0;
			if( lc($postalCode) ne 'postal code' || lc($courier) ne 'courier' || lc($service) ne 'service' || lc($zone) ne 'zone' || lc($DIT) ne 'dit' ){
				$distortedColumnOrder = 1;
			}
			if ($distortedColumnOrder){
				$result = { 'success' => 0, 'errorMsg' => "The first row should contain field names in the following order : (Postal Code, Courier, Service, Zone, DIT)" };
				return JSON->new->utf8->encode($result);
			}
			
			next;
		}
		
		my @error_comment = ();
		my $isdataCorrupt = 0;
		
		if( $postalCode !~ /^\d{5}$/ ){
			push @error_comment, 'The Postal Code should be 5 digits long.';
			$isdataCorrupt = 1;
		}
		
		if( !(grep { $courier eq $_ } keys %$validShippingServiceCodes ) ){
			push @error_comment, 'The courier is not valid.';
			$isdataCorrupt = 1;
		}
		
		if( !(grep { lc($service) eq $_ } keys %{$validShippingServiceCodes->{$courier}} ) ){
			push @error_comment, 'The service is not associated with courier.';
			$isdataCorrupt = 1;
		}
		
		if( $zone !~ /^\d+$/ && $courier ne 'USPS' ){
			push @error_comment, 'The zone should consists of digits only.';
			$isdataCorrupt = 1;
		}
		
		if( $DIT !~ /^\d+$/ ){
			push @error_comment, 'The DIT should consists of digits only.';
			$isdataCorrupt = 1;
		}
		
		##validating values
		if ( $isdataCorrupt	){
			my $errorComment = join " ", @error_comment;
			push @$corruptData, {
				'postalCode' => $postalCode eq '' ? 'No Value' : $postalCode,
				'courier'    => $courier eq '' ? 'No Value' : $courier ,
				'zone'       => $zone eq '' ? 'No Value' : $zone,
				'service'    => $service eq '' ? 'No Value' : $service,
				'DIT'        => $DIT eq '' ? 'No Value' : $DIT,
				'Comment'    => $errorComment,
			};
			next;
		}
		
		push @$dataToPush, {
			'postalCode' => $postalCode,
			'courier'    => $courier,
			'zone'       => $courier eq 'USPS' ? '-1' : $zone,
			'service'    => $validShippingServiceCodes->{$courier}->{lc($service)},
			'DIT'        => $DIT
		};		
	}
	
	if ( scalar @$corruptData and !scalar @$dataToPush ){
		$result = {'success' => 0, 'errorMsg' => 'Whole data is corrupt'};
		$result->{'corruptData'} = $corruptData;
		return JSON->new->utf8->encode($result);
	};
	if ( !scalar @$dataToPush ){
		$result = {'success' => 0, 'errorMsg' => 'File is blank'};
		return JSON->new->utf8->encode($result);
	};
	
	foreach my $row ( @$dataToPush ){
		$selectRow->execute($row->{'postalCode'}, $row->{'courier'}, $row->{'service'});
		my $data = $selectRow->fetchrow_hashref;
		
		##if we have same DIT and zone in records
		next if( defined $data and $data->{'daysInTransit'} eq $row->{'DIT'} and $data->{'assignedZone'} eq $row->{'zone'} );
		
		if( defined $data ){
			 $self->updateDIT({
			  'employeeID'          => $p->{employeeID},
			  'shippingServiceCode' => $data->{'shippingServiceCode'},
			  'zip'                 => $data->{'zip'},
			  'assignedZone'        => $row->{'zone'},
			  'daysInTransit'       => $row->{'DIT'},
			  'action'              => 'update',
			  'shippingCourierCode' => $row->{'courier'},
			  'currentDIT'          => $data->{'daysInTransit'},
			  'currentZone'			=> $data->{'assignedZone'},
			});
		} else {
			$self->insertDIT({
			  'employeeID'          => $p->{employeeID},
			  'shippingServiceCode' => $row->{'service'},
			  'zip'                 => $row->{'postalCode'},
			  'assignedZone'        => $row->{'zone'},
			  'daysInTransit'       => $row->{'DIT'},
			  'action'              => 'add',
			  'shippingCourierCode' => $row->{'courier'}
			});
		}
	}
	
	$result = {'success' => 1};
	if( scalar @$corruptData ){
		$result->{'corruptData'} = $corruptData;
	}
	
	return JSON->new->utf8->encode($result);
	
}

###############################################################################
sub getDITData {
	my $self = shift;
	my $p = shift;

	$p->{'DBH'}  = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysql'}});
	$p->{'DBHr'} = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysqlSlave'}, readonly => 1});

	my $result = {
		'success' => 1,
	};

	if (!$p->{'params'} ) {
		$result->{'success'} = 0;
		return $result;
	}

	my @whereClause;
	my @bindValues;
	my $nextStep = $p->{params}->{nextStep};
	foreach my $key (keys %{$p->{params}}) {
		next if (grep /$key/, qw/nextStep section/);
		next if($p->{params}->{section} eq 'add' && $key eq 'zip');
		push @whereClause, "courierDaysInTransit.$key = ?";
		push @bindValues, $p->{params}->{$key};
	}
	my $whereClause = join(' AND ', @whereClause);
	
	if (! $whereClause ) {
		$result->{'success'} = 0;
		$result->{'status'} = 5;
		return $result;
	}

	my $searchResult;
	if($nextStep eq 'shippingServiceCode')
	{
		$searchResult = $p->{'DBHr'}->selectall_arrayref("SELECT distinct courierDaysInTransit.shippingServiceCode, name, apiName FROM courierDaysInTransit join shippingService using (shippingServiceCode) WHERE $whereClause ORDER BY name", undef, (@bindValues));
	}
	else
	{
		if ($p->{params}->{section} eq 'add' && $nextStep eq 'assignedZone') {
			$whereClause .= ' AND assignedZone != 0'
		}
		$searchResult = $p->{'DBHr'}->selectall_arrayref("SELECT distinct $nextStep FROM courierDaysInTransit WHERE $whereClause ORDER BY $nextStep", undef, (@bindValues));
	}

	$result->{'searchResult'} = $searchResult;

	if ($nextStep eq 'assignedZone' && $p->{params}->{section} eq 'update') {
		my @whereClause;
		my @bindValues;
		foreach my $key (keys %{$p->{params}}) {
			next if (grep /$key/, qw/nextStep section zip/);
			push @whereClause, "courierDaysInTransit.$key = ?";
			push @bindValues, $p->{params}->{$key};
		}
		my $whereClause = join(' AND ', @whereClause);
		$result->{'allZones'} = $p->{'DBHr'}->selectall_arrayref("SELECT distinct $nextStep FROM courierDaysInTransit WHERE $whereClause AND assignedZone != 0 ORDER BY $nextStep", undef, (@bindValues));
	}

	return JSON->new->utf8->encode($result);
}

###############################################################################
sub updateDIT {
	my $self = shift;
	my $p = shift;

	my $result = {
		'success' => 1,
	};

	$p->{'zip'} =~ s/^\s+|\s+$//g;
	$p->{'DBH'}  = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysql'}});
	$p->{'DBHr'} = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysqlSlave'}, readonly => 1});

	if($p->{shippingCourierCode} eq 'USPS')
	{
		$p->{assignedZone} = -1;
	}

	my $update;
	eval{
		$update = $p->{'DBH'}->do("UPDATE courierDaysInTransit SET assignedZone = ?, daysInTransit = ? WHERE zip = ? and shippingCourierCode = ? and shippingServiceCode = ?", undef, ($p->{'assignedZone'}, $p->{'daysInTransit'}, $p->{'zip'}, $p->{'shippingCourierCode'}, $p->{'shippingServiceCode'}));
	};
	if($@)
	{
		$result->{status} = 3;
		$result->{success} = 0;
	}
	else
	{
		$result->{message} = '';
		if ($update) 
		{
			if ($update eq '0E0') {
				$result->{status} = 4;
				$result->{success} = 0;
			}
			else {
				######send data for change log#######
				$self->daysInTransitChangeLog($p);
				$result->{success} = 1;
			}
		}
	}

	return $result;
}

sub insertDIT
{
	my $self = shift;
	my $p = shift;

	my $result = {
		'success' => 1,
	};

	$p->{'zip'} =~ s/^\s+|\s+$//g;
	$p->{'DBH'}  = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysql'}});
	$p->{'DBHr'} = ECS::DB->connect({'config' => $ECS::dbConfig::db{'mysqlSlave'}, readonly => 1});
	if($p->{shippingCourierCode} eq 'USPS')
	{
		$p->{assignedZone} = -1;
	}
	
	my $insert;
	eval
	{
		$insert = $p->{'DBH'}->do("insert ignore into courierDaysInTransit (zip, shippingCourierCode, assignedZone, shippingServiceCode, daysInTransit) values (?, ?, ?, ?, ?)", undef, ($p->{zip}, $p->{shippingCourierCode}, $p->{assignedZone}, $p->{shippingServiceCode}, $p->{daysInTransit}));
	};
	
	$result->{message} = '';
	if($@)
	{
		$result->{status} = 3;
		$result->{success} = 0;
	}
	else
	{
		if ($insert) {
			if ($insert eq '0E0') {
				$result->{status} = 2;
				$result->{success} = 0;
			}
			else {
				######send data for change log#######
				$self->daysInTransitChangeLog($p);
				$result->{success} = 1;
			}
		}
	}

	return $result;
}

sub daysInTransitChangeLog
{
	my $self = shift;
	my $p = shift;
	
	my %fieldsToUpdate = (
		'assignedZone' => 'currentZone',
		'daysInTransit' => 'currentDIT'
	);
	
	my @bindParams;
	my @bindQuery;
	my $sql = "insert into daysInTransitChangeLog (employeeID, zip, shippingCourierCode, shippingServiceCode, field, oldValue, newValue) values ";
	foreach my $field(keys %fieldsToUpdate)
	{
		next unless ($p->{$field} ne ($p->{$fieldsToUpdate{$field}} || ''));
		push @bindQuery, '(?, ?, ?, ?, ?, ?, ?)';
		push @bindParams, ($p->{employeeID}, $p->{zip}, $p->{shippingCourierCode}, $p->{shippingServiceCode}, $field, $p->{$fieldsToUpdate{$field}} || undef, $p->{$field});
	}
	$sql .= join ', ', @bindQuery;
	
	eval {
		my $sth = $p->{'DBH'}->prepare($sql);
		$sth->execute(@bindParams);
	};
	if($@)
	{
		warn "error while logging postal codes days in transit changes: $@\n";
	}
}

1;
