package CLMApp::Controller::Customer;
use Mojo::Base 'Mojolicious::Controller';
use Data::Show;
use Moose;
use Moose::Autobox;
use Perl6::Junction qw/any/;
use Data::Show;
use JSON;
use Mojo::JSON qw(decode_json encode_json);
use Cwd;
use Hash::Flatten qw(:all);
use Storable 'dclone';
$Storable::Deparse = 1;
$Storable::Eval = 1;
use File::Temp qw/tempfile/;
use File::Copy qw(copy move);
use Spreadsheet::WriteExcel;
use DateTime;
use DateTime::Format::DateParse;
use Mojo::IOLoop::Subprocess;
use Encode qw(decode encode);
use Class::Tiny qw//, {
	title => 'Customers',
	table => 'Customer',
	module => 'customer',
	pk => 'customerid',
	columns => [qw//],
	prefetch => [qw/distributor/],
	order_by => ['me.customerid']
};

sub index{
	my $c = shift;
	$c->search(1) if $c->req->url->path->to_abs_string eq '/customer';
	my ($frm,$butt) = $c->populate_form('search');

	$c->stash(frm => undef,butt => undef);
	$c->stash(title=> $c->title(),table => $c->module() . 'table' ,module=> $c->module());


	$c->render(template => 'customer/index');
}

sub search{
	my $c = shift;
	my $string = shift;
	my ($frm,$butt) = $c->populate_form('search');

	$c->stash(frm => $frm,butt => $butt);
	$c->stash(title=> $c->title(),table => undef ,module=> $c->module());

	if($string){
		$c->stash('customersearch', $c->render_to_string('customer/edit'));
	}else{
		$c->render(template => 'customer/edit');		
	}
	
}


sub read {
	my $c = shift;
	
	# show $c->{form};
	my $p = $c->req->params->to_hash;
	if($c->stash('customerid') eq 'add'){
		return $c->add();
	};
	if($c->stash('customerid') eq 'template'){
		return $c->template();
	};	
	if($c->stash('customerid') eq 'bulkedit'){
		return $c->bulkedit();
	};	
	if($c->stash('customerid') eq 'search'){
		return $c->search();
	};	
	my @c = $c->db->resultset('Customer')->search({'me.customerid'=>$c->stash('customerid')}, 
		{result_class=>'DBIx::Class::ResultClass::HashRefInflator',
			prefetch =>['distributor','chaingrp','warehouse','concept','status','user', 
		#	'customercomment'
			# {customercomment => [qw/usercreatedby userupdatedby/] }
			]
		}
		) or return $c->render(text => 'Record not found.', status => 405);

# get related as arref
	$c[0]->{systemid} = [$c->db->resultset($c->table)->search_related('back_office_system_customers', {"me.". $c->pk =>$c->stash($c->pk)})]->map(sub {
		my %r = $_[0]->get_columns;
		return $r{systemid};
	});
	

	my @comments = split(/\n+/, $c[0]->{comments});
show \@comments;

	# my @c =  [ $c->db->resultset('Customer')->find({customerid=>$c->stash('customerid')})->distributor ]->map(sub {
	# 		my %c = $_->get_columns;
	# 		return \%c;
	# 	});
# show $c[0];

	my ($frm,$butt) = $c->populate_form('default',$c[0]);

	$c->stash(frm => $frm);
	$c->stash(butt => $butt);

	$c->render(template => 'customer/edit');
	# $c->render(json => $c[0]);
}

sub add{
	my $c = shift;
	my ($frm,$butt) = $c->populate_form('add');

	$c->stash(frm => $frm);
	$c->stash(butt => $butt);

	$c->render(template => 'customer/edit');

}

sub template{
	my $c = shift;
	my ($frm,$butt) = $c->populate_form('template');

	$c->stash(frm => $frm);
	$c->stash(butt => $butt);

	$c->render(template => 'customer/edit');

}


sub bulkedit{
	my $c = shift;
	my ($frm,$butt) = $c->populate_form('bulkedit');

	$c->stash(frm => $frm);
	$c->stash(butt => $butt);

	$c->render(template => 'customer/edit');

}

sub bulkeditupdate{
	my $c = shift;
	my $j = $c->req->params->to_hash;
	# show $j->{search}->[0];
	# # $j->{search} =~ s/\}"(,.*)$/\}"/sm;
	# # if($j->{search} =~ /^(.*?)\{(.*?)\}/smi){
	# if($j->{search} =~ /^\[\{(.*?)\}/smi){
	# 		my $body = $2;
	# 		show $body;
	# 		$j->{search} = '{' . $body . '}';
	# }
	$j->{search} = ref($j->{search}) eq 'ARRAY' ? $j->{search}->[0] : $j->{search};


	show $j->{search}, $j;
	# return $c->render(text => 'TEST POINT 1', status => 405);
	
	if(exists($j->{brandedprocurement})){
		$j->{brandedprocurementdeleted} = $j->{brandedprocurement} == 1 ? 0 : 1;
	}
	my $search = $j->{search} ? decode_json(encode('UTF-8', $j->{search}, Encode::FB_CROAK)) : {};
	show $search;
	delete($j->{search});
	delete($j->{opstatusdate});
	# return $c->render(json => $j);

	# return $c->render(text => 'TEST');

	$c->render_later;

	my $subprocess = Mojo::IOLoop::Subprocess->new;
	$subprocess->run(
	  sub {
	    my $subprocess = shift;
	    my $f = 'dmaclm.'. time() . '.sql';
		my $cmd = qq#/usr/bin/mysqldump  -udma -p3lUz1OV2! -hrds1.dmadelivers.com dmaclm BPMigration BackOfficeSystem BackOfficeSystemChainConcept BackOfficeSystemCustomer BackSystemOfficeChain BulkEditHistory ChainConcept ChainGroup ChainUnit Country Customer DMAProgramManager DMAVP DistributorCompany DistributorWarehouse EmailNotification EmailNotificationWarehouse Notes OperatingStatus OrderFlag Ownership PreSaleItem Setting OrderInvoiceSystem Status Users Market Routing CustomerSystem CustomerTemplate EntityType > /tmp/$f#;
		system($cmd);

	  },
	  sub {
	    my ($subprocess, $err, @results) = @_;
	
					my $cnt = 0;
						if($j->{invoicesystem} < 0 || $j->{ordersystem} < 0){


								if($j->{invoicesystem} < 0){


										my $systemid = abs($j->{invoicesystem});
										if($j->{chaingroupid}){

												my $csearch = {
													'me.chaingroupid' => $j->{chaingroupid}, 
													'customersystem.systemid' => $systemid, 
													'customersystem.systemtype' => 1
												};
												$csearch->{distributorcompanyid} = $search->{distributorcompanyid} if $search->{distributorcompanyid};
												$csearch->{distributorwarehouseid} = $search->{distributorwarehouseid} if $search->{distributorwarehouseid};
												$csearch->{chainconceptid} = $search->{chainconceptid} if $search->{chainconceptid};
												$csearch->{ownershiptype} = $search->{ownershiptype} if $search->{ownershiptype};
												$csearch->{ownership} = $search->{ownership} if $search->{ownership};
show $csearch;

												my @customers = $c->db->resultset('Customer')->search($csearch,{
													prefetch => [qw/customersystem/],
													result_class => 'DBIx::Class::ResultClass::HashRefInflator'
												});

												show scalar(@customers);
												
												foreach my $cs (@customers){

													# show $cs;
												
													
													# my @customer = $c->db->resultset('Customer')->search({'me.customerid' => $cs->{customerid}, 'me.invoicesystem' => $systemid, 'me.chaingroupid' => $j->{chaingroupid}, 'customersystem.systemid' => $systemid, 'customersystem.systemtype' => 1},{
													# 	prefetch => [qw/customersystem/],
													# 	result_class => 'DBIx::Class::ResultClass::HashRefInflator'
													# });
													
													$c->db->resultset('CustomerSystem')->search({'me.customerid' => $cs->{customerid}, 'me.systemid' => $systemid, 'me.systemtype' => 1})->update({deleted => 1});

													my $update = { invoicesystem => 0 };
													$c->db->resultset('Customer')->search({'me.customerid' => $cs->{customerid} },{prefetch => [qw/customersystem/]})->update($update);




													$cnt++;


												}
										}
								}

								if($j->{ordersystem} < 0){

show $j;
										my $systemid = abs($j->{ordersystem});
										if($j->{chaingroupid}){
												my $csearch = {
													'me.chaingroupid' => $j->{chaingroupid}, 
													'customersystem.systemid' => $systemid, 
													'customersystem.systemtype' => 0
												};
												$csearch->{distributorcompanyid} = $search->{distributorcompanyid} if $search->{distributorcompanyid};
												$csearch->{distributorwarehouseid} = $search->{distributorwarehouseid} if $search->{distributorwarehouseid};
												$csearch->{chainconceptid} = $search->{chainconceptid} if $search->{chainconceptid};
												$csearch->{ownershiptype} = $search->{ownershiptype} if $search->{ownershiptype};
												$csearch->{ownership} = $search->{ownership} if $search->{ownership};
												

show $csearch;

												my @customers = $c->db->resultset('Customer')->search($csearch,{
													prefetch => [qw/customersystem/],
													result_class => 'DBIx::Class::ResultClass::HashRefInflator'
												});

												show scalar(@customers);
												my $cnt = 0;
												foreach my $cs (@customers){

													# show $cs;
												
													
													# my @customer = $c->db->resultset('Customer')->search({'me.customerid' => $cs->{customerid}, 'me.ordersystem' => $systemid, 'me.chaingroupid' => $j->{chaingroupid}, 'customersystem.systemid' => $systemid, 'customersystem.systemtype' => 0},{
													# 	prefetch => [qw/customersystem/],
													# 	result_class => 'DBIx::Class::ResultClass::HashRefInflator'
													# });
													
													$c->db->resultset('CustomerSystem')->search({'me.customerid' => $cs->{customerid}, 'me.systemid' => $systemid, 'me.systemtype' => 0},{prefetch => [qw//]})->update({deleted => 1, active => 0});

													my $update = { 'me.ordersystem' => 0 };
													$c->db->resultset('Customer')->search({'me.customerid' => $cs->{customerid} },{prefetch => [qw/customersystem/]})->update($update);




													$cnt++;


												}
										}
								}								

						}
					

# show $cnt . ' records removed';

#show $update;
#						$c->db->resultset('CustomerSystem')->search($cssearch)->update($update);
# return $c->render(json => $update);		


			foreach my $s (keys %$search){
				delete($search->{$s}) unless $search->{$s} ne '';
				if(ref($search->{$s}) eq 'ARRAY' && scalar(@{$search->{$s}}) == 0){
					delete($search->{$s});
				}elsif(ref($search->{$s}) eq 'ARRAY'){
					$search->{$s} = {'IN' => $search->{$s}};
				}
			}
			foreach my $k (keys %$j){
				delete($j->{$k}) unless $j->{$k} ne '';
			}
			my $ssystemid = $search->{systemid}; delete($search->{systemid});
			my $ssystemids = ref($j->{systemid}) eq 'ARRAY' ? $j->{systemid} : $j->{systemid} ? [$j->{systemid}] : [];
			show $search;

			if($j->{systemids}){
				$search->{'back_office_system_customers.systemid'} = ref($j->{systemids}) eq 'ARRAY' ?  {'IN' => $j->{systemids}} : {'IN' => [$j->{systemids}] }; 
			}

			delete($search->{systemids});
			delete($j->{search}); 
			delete($j->{systemids}); 
			delete($j->{systemid});
			
			
			# delete($search->{'back_office_system_customers.systemid'}) unless scalar(@$ssystemids);
			my $join = [];
			if($search->{'back_office_system_customers.systemid'}){
				$join = [qw/back_office_system_customers/];
			}

			show $search, $j;
			# return;
			my @fres;
			my @res = $c->db->resultset('Customer')->search($search,
				{
					result_class=>'DBIx::Class::ResultClass::HashRefInflator',
					join => $join,
				}
			);
			my $csearch = \%$search;
			my @cres = $c->db->resultset('Customer')->search($csearch,
				{
					result_class=>'DBIx::Class::ResultClass::HashRefInflator',
					join => $join,
				}
			);


# show scalar(@res), \@res, \@cres;			

			if(scalar(@res)){	
					my @customerid = $c->db->resultset('Customer')->search($search, 
						# {result_class=>'DBIx::Class::ResultClass::HashRefInflator',}
						{ join => $join },
					)->get_column('customerid')->all;	
					
					$c->db->resultset('BulkEditHistory')->create({
						searchcriteria => encode_json($search),
						editcriteria => encode_json($j),
						customerids => encode_json(\@customerid),
						metadata => undef,
						deleted => 0, 
						created  => \'NOW()',
						createdby => $c->session->{user_id},
						updated => \'NOW()',
						updatedby => $c->session->{user_id},
					});

		# return $c->render(json => \@res);

					$j->{lastchangeddate} = \'NOW()';
					$j->{lastupdatedbyid} = $c->session->{user_id};
					if($j->{brandedprocurement} == 1 && $search->{brandedprocurement} == 0){
						$j->{bpflagdate} = \'NOW()';
					}

					show $j, $search, $ssystemids, scalar(@customerid);
					# return $c->render(text => 'TEST POINT 2', status => 405);

					# get CustomerSystems 
					# if($j->{marketid} || $j->{accountingcode} || $j->{filecode} && ($j->{invoicesystem} || $j->{ordersystem})){
					if(($j->{marketid} || $j->{accountingcode} || $j->{filecode}) || ($j->{invoicesystem} || $j->{ordersystem})){
						# what if both
							my $update = {};
							my $cssearch = { 
								customerid => { 'IN' => \@customerid },
							};
							
							$cssearch->{marketid} = $j->{marketid} if $j->{marketid};
							$update->{marketid} = $j->{marketid} if $j->{marketid};
							$cssearch->{accountingcode} = $j->{accountingcode} if $j->{accountingcode};
							$update->{accountingcode} = $j->{accountingcode} if $j->{accountingcode};
							$cssearch->{filecode} = $j->{filecode} if $j->{filecode};
							$update->{filecode} = $j->{filecode} if $j->{filecode};



						if($j->{invoicesystem} > 0 || $j->{ordersystem} > 0){
							


							if($j->{invoicesystem} > 0){
								$update->{systemtype} = 1;
								$cssearch->{systemtype} = 1;
								$update->{systemid} = $j->{invoicesystem};
								# $cssearch->{systemid} = $j->{ordersystem};
								my @oisystems = $c->db->resultset('OrderInvoiceSystem')->search({id => $j->{invoicesystem}},{result_class=>'DBIx::Class::ResultClass::HashRefInflator'});
								my $oisystem = $oisystems[0];
								$update->{systemfiles} = $oisystem->{systemfiles};

								my @markets = $c->db->resultset('Market')->search({chaingroupid => $j->{chaingroupid}, ordersystem => $j->{ordersystem} },{result_class=>'DBIx::Class::ResultClass::HashRefInflator'});
								my $market = $markets[0];								

								show $search->{invoicesystem}, $j->{invoicesystem};


									if($search->{invoicesystem} != $j->{invoicesystem}){
									# add CustomerSystem
											foreach my $cid (@customerid){
													print join(',',@customerid) . "\n";
													my @cs = $c->db->resultset('Customer')->search({ customerid => $cid},{result_class=>'DBIx::Class::ResultClass::HashRefInflator'});
													my $cs = $cs[0];

													$c->db->resultset('CustomerSystem')->create({
															customerid       => $cs->{customerid},
															systemtype       => 1,
															systemid         => $j->{invoicesystem},
															systemfiles      => $oisystem->{systemfiles},
															marketid         => $cs->{marketid} || $market->{id},
															accountingcode   => $cs->{accountingcode},
															filecode         => $cs->{filecode},
															active           => 1,
													});


											}
									}

							}



							if($j->{ordersystem} > 0){
								$update->{systemtype} = 0;
								$cssearch->{systemtype} = 0;
								$update->{systemid} = $j->{ordersystem};
								# $cssearch->{systemid} = $j->{ordersystem};
								# get systemfiles 
								my @oisystems = $c->db->resultset('OrderInvoiceSystem')->search({id => $j->{ordersystem}},{result_class=>'DBIx::Class::ResultClass::HashRefInflator'});
								my $oisystem = $oisystems[0];
# show $oisystem;								
								$update->{systemfiles} = $oisystem->{systemfiles};

								my @markets = $c->db->resultset('Market')->search({chaingroupid => $j->{chaingroupid}, ordersystem => $j->{ordersystem} },{result_class=>'DBIx::Class::ResultClass::HashRefInflator'});
								my $market = $markets[0];

								
								

									if($search->{ordersystem} != $j->{ordersystem}){
									# add CustomerSystem
											foreach my $cid (@customerid){
													my @cs = $c->db->resultset('Customer')->search({ customerid => $cid},{result_class=>'DBIx::Class::ResultClass::HashRefInflator'});
													my $cs = $cs[0];
													my $cscreate = {
															customerid       => $cs->{customerid},
															systemtype       => 0,
															systemid         => $j->{ordersystem},
															systemfiles      => $oisystem->{systemfiles},
															marketid         => $cs->{marketid} || $market->{id},
															accountingcode   => $cs->{accountingcode},
															filecode         => $cs->{filecode},
															active           => 1,
													};
show $cscreate;													
# return $c->render(text => 'TEST POINT 4', status => 405);													
													$c->db->resultset('CustomerSystem')->create($cscreate);


											}
									}
								}	


							}




						}













					if(scalar(@$ssystemids) && $ssystemids){
						show $ssystemids;
						# $c->db->resultset('Customer')->search($search,{
						# 	join => [qw/back_office_system_customers/],
						# })->update($j);


						
						if(scalar(@$ssystemids)){
							# delete all then add
							$c->db->resultset('BackOfficeSystemCustomer')->search({ customerid => { 'IN' => \@customerid }})->delete;

							foreach my $sid (@$ssystemids){
								next unless $sid;
								foreach my $cid (@customerid){
									$c->db->resultset('BackOfficeSystemCustomer')->create({systemid => $sid, customerid => $cid});
								}
							}							
						}



					}elsif($search->{'back_office_system_customers.systemid'}){
						show $search;
						$c->db->resultset('BackOfficeSystemCustomer')->search({ customerid => { 'IN' => \@customerid }})->delete;

					}else{
						$c->db->resultset('Customer')->search($search)->update($j);
					}
					# $c->db->resultset('Customer')->search($search)->update($j);

show $search, $j;
					return $c->render(json => \@customerid);
			}
			


			$c->render(json => \@res);


# end of process
	  }
	);



}


sub create {
	my $c = shift;
	my $j = $c->req->params->to_hash;
	$j->{setupbyid} = $c->session->{user_id};
	$j->{lastupdatedbyid} = $c->session->{user_id};
	my $dt = DateTime->now(time_zone => 'America/Chicago');
	$j->{lastchangeddate} =  $dt->ymd('-') . ' ' . $dt->hms(':');
	$j->{setupdate} =  $dt->ymd('-') . ' ' . $dt->hms(':');
	if($j->{brandedprocurement}){
		$j->{bpflagdate} =  $dt->ymd('-') . ' ' . $dt->hms(':');
	}else{
		delete($j->{bpflagdate});
	}

	my $commentsdb = $j->{comments}; 
	delete($j->{comments});
	# my $cobj = {customerid => $cid, active => 1, deleted => 0, created => \'NOW()', updated => \'NOW()', createdby => $c->session->{user_id}, updatedby => $c->session->{user_id}, customercommentdescription => $commentsdb };
	# # my $uobj = {updated => \'NOW()', updatedby => $c->session->{user_id}, customercommentdescription => $commentsdb };  

	# $c->db->resultset('CustomerComment')->create($cobj);

	foreach my $k (keys %$j){
		$j->{$k} =~ s/^\s+//;
		$j->{$k} =~ s/\s+$//;
	}

	my @dupes = $c->db->resultset('Customer')->search({ customernumber => $j->{customernumber} , 'distributorwarehouseid' => $j->{distributorwarehouseid} }, 
				{result_class=>'DBIx::Class::ResultClass::HashRefInflator',});

	if($dupes[0] && $dupes[0]->{customerid}){
		my $dupeid = $dupes[0]->{customerid};
		return $c->render(text => "Duplicate record DMA Cust ID #$dupeid already exists.", status => 405);	
	}

# server-side required
	$c->setup;
	my $form = $c->{_forms}->{add};
	foreach my $e (@{ $form->eorder }){
		my $el = $form->elems->$e;
		if($el->attrs->required && exists($j->{$e}) && $j->{$e} eq undef){
			return $c->render(text => '"' . $el->title  . '" Field required.', status => 405);
		}
	}
		



	if(ref($j->{backofficesys}) eq 'ARRAY'){
		$j->{backofficesys} = join(",", @{$j->{backofficesys}});
	}
# do related table shit
	my $a = join(", ", ($j->{address}, $j->{city}, $j->{state}, $j->{zip}) );
	($j->{latitude},$j->{longitude}) = $c->gpslatlon($a);
	$j->{latitude} ||= 0;
	$j->{longitude} ||= 0;


	my $w = $c->db->resultset('DistributorWarehouse')->find({distributorwarehouseid => $j->{distributorwarehouseid} },{result_class=>'DBIx::Class::ResultClass::HashRefInflator'});
	my $d = join(", ", ($w->{address}, $w->{cityname}, $w->{state}, $w->{zip}) );
	$j->{distancetodistcenter} = $c->gpslatlon($a,$d);
	$j->{distancetodistcenter} ||= 0;

	$j->{entitytypeid} ||= 0;
	

	$j->{invoicesystem} ||= 0;
	$j->{ordersystem} ||= 0;	

	$j->{routingid} ||= 0;

	my $systemid = $j->{systemid}; delete($j->{systemid});
	my $s = $c->db->resultset('Customer')->create($j);

	if($systemid){
	  	my @systems = $c->db->resultset('BackOfficeSystem')->search({ systemid => { '-in' => $systemid } }); # in ARRAY or STRING
		$s->set_systems(\@systems);
	}


	# Brand, Store #, Store Name, Customer #, Email
	#chaingroupid, unitname, customerunitnumber , customernumber
	
							# 	my $subject = 'Setup GOV Customer'
	            #   $c->mail(
              #   mail   => {
              #     To       => 'OEsupport@dmadelivers.com',
              #     # To => 'dma@ace4it.com',
              #     From => $c->config->{mail}->{from}, 
              #     'Reply-to' => $c->config->{mail}->{replyto},
              #     Cc => $c->config->{mail}->{cc},
              #     Bcc    => $c->config->{mail}->{bcc},
              #     # Bcc => 'dma@ace4it.com',
              #     Subject  => $e->{subject},
              #     Data     => $data,
              #   },
              #   how => $c->config->{mail}->{how},
              #   howargs => $c->config->{mail}->{howargs},   

              # );






	my $pk = $c->pk;
	my %p = $c->db->resultset('Customer')->by_id($s->$pk)->get_columns;
	my $cobj = {customerid => $p{customerid}, active => 1, deleted => 0, created => \'NOW()', updated => \'NOW()', createdby => $c->session->{user_id}, updatedby => $c->session->{user_id}, customercommentdescription => $commentsdb };
	# my $uobj = {updated => \'NOW()', updatedby => $c->session->{user_id}, customercommentdescription => $commentsdb };  

	$c->db->resultset('CustomerComment')->create($cobj);

	$c->render(json => \%p);
}

sub update{
		my $c = shift;
		$c->req->params->remove('customersystem');
		$c->req->params->remove('customersystemcontent');
		$c->req->params->remove('setupdate');
		$c->req->params->remove('commentslist');
		my $j = $c->req->params->to_hash;
my $commentsdb = $j->{comments};
		my $cid = $c->stash('customerid');
show $j;
# should have a dupe
		my @dupes = $c->db->resultset('Customer')->search({ customernumber => $j->{customernumber} , 'distributorwarehouseid' => $j->{distributorwarehouseid}, customerid => { '!=' => $cid} }, 
					{result_class=>'DBIx::Class::ResultClass::HashRefInflator',});

		if($dupes[0] && $dupes[0]->{customerid} != $cid){
			my $dupeid = $dupes[0]->{customerid};
			return $c->render(text => "Duplicate record DMA Cust ID #$dupeid already exists.", status => 405);	
		}




		show $cid;
		delete $j->{customerid};
		$j->{brandedprocurement} ||= 0;
		$j->{invoicesystem} ||= 0;
		$j->{ordersystem} ||= 0;
		$j->{entitytypeid} ||= 0;
		$j->{lastupdatedbyid} = $c->session->{user_id};
		my $dt = DateTime->now(time_zone => 'America/Chicago');
		$j->{lastchangeddate} =  $dt->ymd('-') . ' ' . $dt->hms(':');
		if($j->{brandedprocurement} == 1){
			$j->{brandedprocurementdeleted} = 0;
		}
# show $j;
		foreach my $k (keys %$j){
			$j->{$k} =~ s/^\s+//g;
			$j->{$k} =~ s/\s+$//g;
		}
# show $j;		

# should have a dupe
		my @dupes = $c->db->resultset('Customer')->search({ customernumber => $j->{customernumber} , 'distributorwarehouseid' => $j->{distributorwarehouseid}, customerid => { '!=' => $cid} }, 
					{result_class=>'DBIx::Class::ResultClass::HashRefInflator',});

		if($dupes[0] && $dupes[0]->{customerid} != $cid){
			my $dupeid = $dupes[0]->{customerid};
			return $c->render(text => "Duplicate record DMA Cust ID #$dupeid already exists.", status => 405);	
		}


		my $dt =  DateTime->now(time_zone  => 'America/Chicago');		
		

		

		my @ccusts = $c->db->resultset('Customer')->search({customerid => { '=' => $cid} }, 
					{result_class=>'DBIx::Class::ResultClass::HashRefInflator',});
		my $diffparams = {}; 
		my $ccust = $ccusts[0];
		foreach my $dk (keys %$j){
			next if $dk =~ /lastchangeddate comments/;
			if($ccust->{$dk} ne $j->{$dk}){
				$diffparams->{$dk} = $j->{$dk};
			}
		}
		my @diffkeys = keys %$diffparams;


		my $comment = $dt->year() . sprintf("%02d", $dt->month()) . sprintf("%02d", $dt->day()) . "\t" .  $c->session->{username} . "\t" . 'UPDATE RECORD: ' . join(',',@diffkeys) . "\n-----------\n";
		$j->{comments} = $comment . $j->{comments};

# check if created???
		my $ccount = $c->db->resultset('CustomerComment')->search({customerid => $cid})->count;	

		my $cobj = {customerid => $cid, active => 1, deleted => 0, created => \'NOW()', updated => \'NOW()', createdby => $c->session->{user_id}, updatedby => $c->session->{user_id}, customercommentdescription => $commentsdb };
		# my $uobj = {updated => \'NOW()', updatedby => $c->session->{user_id}, customercommentdescription => $commentsdb };  

		$c->db->resultset('CustomerComment')->create($cobj);
		
# show $ccust,$diffparams, $comment, $j;
# return $c->render('text' => 'TEST UPDATE');


# server-side required
# use Time::HiRes qw(  gettimeofday tv_interval );
# my $t0 = [gettimeofday];


        my $join = ['distributor','chaingrp','concept','warehouse','chainunit'];
        my $as =  [qw/user unit dma password blank statuschar modify no/]; # systemname
        my $select = [\['CONCAT(customernumber,distributor.bpdcode,warehouse.bplocationid)'],
          \['CONCAT("DMA","-",me.chainunitid)'],
          \['"DMA"'],
          \['"Welcome1"'],
          \['""'],
          \['IF(me.statusid=1,IF(me.brandedprocurementdeleted=1,"I","A"),IF(me.brandedprocurementdeleted=1,"I","I"))'],
          \['"M"'],
          \['"N"'],
          # \['GROUP_CONCAT(system.systemname)']
        ];

		my @bp = $c->db->resultset('Customer')->search({customerid => $cid }, 
					{result_class=>'DBIx::Class::ResultClass::HashRefInflator', prefetch => [qw/distributor warehouse chaingrp concept/], join => $join, '+as' => $as, '+select' => $select});

		$bp[0]->{systemid} = [$c->db->resultset($c->table)->search_related('back_office_system_customers', {"me.customerid" => $cid})]->map(sub {
			my %r = $_[0]->get_columns;
			return $r{systemid};
		});
# show $bp[0]->{brandedprocurement}, $j->{brandedprocurement};
		if($bp[0]->{brandedprocurement} == 0 && $j->{brandedprocurement} == 1){
			$j->{bpflagdate} = $dt->ymd('-') . ' ' . $dt->hms(':');
		}else{
			delete($j->{bpflagdate});
		}

		$c->setup;
		my $form = $c->{_forms}->{default};
		foreach my $e (@{ $form->eorder }){
			my $el = $form->elems->$e;
			if($el->attrs->required && exists($j->{$e}) && $j->{$e} eq undef){
				return $c->render(text => '"' . $el->title  . '" Field required.', status => 405);
			}
		}
		
# show tv_interval ( $t0, [gettimeofday]);

		if(ref($j->{backofficesys}) eq 'ARRAY'){
			$j->{backofficesys} = join(",", @{$j->{backofficesys}});
		}
# do related table shit
		# my $a = join(", ", ($j->{address}, $j->{city}, $j->{state}, $j->{zip}) );
		my $a = join(", ", ($j->{zip}) );
		# show $a;
		($j->{latitude},$j->{longitude}) = $c->gpslatlon($a);


		my $w = $c->db->resultset('DistributorWarehouse')->find({distributorwarehouseid => $j->{distributorwarehouseid} },{result_class=>'DBIx::Class::ResultClass::HashRefInflator'});
		# my $d = join(", ", ($w->{address}, $w->{cityname}, $w->{state}, $w->{zip}) );
		my $d = join(", ", ($w->{zip}) );
		$j->{distancetodistcenter} = $c->gpslatlon($a,$d);

		if(!$j->{latitude} || !$j->{longitude} ){
#			return $c->render(text => 'GPS Coordinates and Distance Failed. Please check the address.', status => 405);	
			$j->{latitude} = 0;
			$j->{longitude} = 0;
			$j->{distancetodistcenter} = 0;

		}





# method two many_to_many
		my $customer =  $c->db->resultset($c->table)->find({ $c->pk() => $c->stash($c->pk) });
		my $systemid = $j->{systemid}; delete($j->{systemid});
		if($systemid){
		  	my @systems = $c->db->resultset('BackOfficeSystem')->search({ systemid => { '-in' => $systemid} }); # in ARRAY or STRING
			$customer->set_systems(\@systems);

		}else{
			my @systems = $c->db->resultset('BackOfficeSystemCustomer')->search({ customerid => $c->stash($c->pk) }); # in ARRAY or STRING
			foreach my $s (@systems){
				$customer->remove_from_systems($s);
			}
		}



		my %c = $c->db->resultset('Customer')->by_id($c->stash('customerid'))->update($j)->get_columns or return $c->render(text => 'Record not found or cannot update.', status => 405);	


# set chain unit to active
		if($j->{statusid} == 1){
			$c->db->resultset('ChainUnit')->find({chainunitid => $j->{chainunitid}})->update({active => 1});
		}else{

			my @res = $c->db->resultset('ChainUnit')->search({'me.chainunitid' => $j->{chainunitid} },{
					join => [qw/customer/], 
					result_class=>'DBIx::Class::ResultClass::HashRefInflator',
			});
			foreach my $r (@res){
				my $cnt = $c->db->resultset('Customer')->search({'me.chainunitid' => $r->{chainunitid}, 'me.statusid' => 1})->count;
				if($cnt == 0){
					$c->db->resultset('ChainUnit')->find({'chainunitid' => $r->{chainunitid} })->update({active => 0});
				}
			}
		}

# send BP migration email
# show @bp, $j;

		if($j->{statusid} == 1 && $j->{brandedprocurement} == 1 && $bp[0]->{brandedprocurement} == 0){
			$c->check_email_notification(\@bp, 1);
		}

		if($j->{ordersystem} || $j->{invoicesystem}){


# check if exists order system				
				my @cs = $c->db->resultset('CustomerSystem')->search({customerid => $j->{customerid}, systemtype => 0},{
						# prefetch => [qw/customer/],
						result_class=>'DBIx::Class::ResultClass::HashRefInflator',
				});

				my @oois = $c->db->resultset('OrderInvoiceSystem')->search({id => $j->{ordersystem}},{
						result_class=>'DBIx::Class::ResultClass::HashRefInflator',
				});

				my @iois = $c->db->resultset('OrderInvoiceSystem')->search({id => $j->{invoicesystem}},{
						result_class=>'DBIx::Class::ResultClass::HashRefInflator',
				});

# update or create

				if(scalar(@cs)){




	# update - record exists
					if($cs[0]->{systemid} != $j->{ordersystem}){
									$c->db->resultset('CustomerSystem')->search({customerid => $j->{customerid}, systemtype => 0},{})->update({
											systemid => $j->{ordersystem},
											systemfiles => $oois[0]->{systemfiles}
									});								
					}elsif($j->{ordersystem}){


								$c->db->resultset('CustomerSystem')->create({
										customerid       => $j->{customerid},
										systemtype       => 0,
										systemid         => $j->{ordersystem},
										systemfiles      => $oois[0]->{systemfiles},
										marketid         => $j->{marketid},
										accountingcode   => $j->{accountingcode},
										filecode         => $j->{filecode},
										active           => 1,
								});


					}
					if($j->{invoicesystem}){

		# update - record exists
						if($cs[0]->{systemid} != $j->{invoicesystem}){
										$c->db->resultset('CustomerSystem')->search({customerid => $j->{customerid}, systemtype => 1},{})->update({
												systemid => $j->{invoicesystem},
												systemfiles => $iois[0]->{systemfiles}
										});									
						}else{


									$c->db->resultset('CustomerSystem')->create({
											customerid       => $j->{customerid},
											systemtype       => 1,
											systemid         => $j->{invoicesystem},
											systemfiles      => $iois[0]->{systemfiles},
											marketid         => $j->{marketid},
											accountingcode   => $j->{accountingcode},
											filecode         => $j->{filecode},
											active           => 1,
									});


						}

					}


				}else{

					



				}
			}

		$c->render(json=>\%c);
}

sub delete{
  my $c = shift;
  my($customerid) = ($c->param('customerid'));
  # $c->db->resultset('Customer')->search({customerid => $customerid})->delete();
  $c->render(json=>{customerid=>$customerid});
}

sub report_email{
	my $c = shift;
	my $p = $c->req->params->to_hash;

	my $excols = [qw/customerid customernumber amphireid customerunitnumber unitname address city state zip email/];

	my @emails = $c->db->resultset('Customer')->search({
			# 'me.active' => 1,
			'me.email' => { '!=' => ""}
		},
		{
			result_class=>'DBIx::Class::ResultClass::HashRefInflator',
			order_by => [qw/customerid/],
			columns => $excols

		});

show scalar(@emails);

    my $jobj = {
      "draw" => $p->{draw},
      "recordsTotal" => scalar(@emails),
      "recordsFiltered" => scalar(@emails),
      "data" => \@emails,
      # "header" => $cols
    };

    if($p->{export}){
    		my $fdir = '/tmp/';
			my $filename = 'CustomerEmail.' . time() . '.xls';
			my $filepath = $fdir . $filename;
			my ( $fh, $ofilename ) = $c->export_hr(\@emails,filename => $filepath, columns => $excols);

			my $dfilepath = getcwd() . '/public/files/' . $filename;
			move $filepath, $dfilepath;
			$c->res->headers->location('/files/' . $filename);
			$c->rendered(302);
			return 1;

	}else{
		$c->render(json => $jobj);
	}  

}


sub exportas{

		



}


sub list {
	my $c = shift;
	my $p = shift || $c->req->params->to_hash;

	show $p;
	# $c->req->param('customersystem.deleted',0);
	# $p->{'customersystem.deleted'} = 0;


# what to do about nested objects??
	# backoffice.ack backoffice.active backoffice.cat backoffice.chaingroupamphireid backoffice.conf backoffice.default0 backoffice.description backoffice.inv backoffice.systemid backoffice.systemname
	my $cols = [qw/address amphireid chainconceptid chaingroupid chainunitid city comments countrycode customerid customernumber customerunitnumber distancetodistcenter  groupname lastchangeddate lastupdatedbyid latitude longitude orderguideflagid ownership ownership2 setupdate state statusid unitname zip  chaingrp.chaingroupamphireid chaingrp.chaingroupid chaingrp.chaingroupname chaingrp.chaingrouppam chaingrp.chaingroupvp concept.chainconceptamphireid concept.chainconceptid concept.chainconceptname concept.parentid distributor.dcnbr distributor.distributoramphireid distributor.distributorcode distributor.distributorcompanyid distributor.name distributorcompanyid distributorwarehouseid user.active user.fullname user.isadmin user.password user.user_id user.username warehouse.address warehouse.cityname warehouse.countrycode warehouse.distributorlocid warehouse.distributorwarehouseamphireid warehouse.distributorwarehouseid warehouse.distributorwarehousename warehouse.latitude warehouse.longitude warehouse.parentid warehouse.siteshortname warehouse.state warehouse.zip brandedprocurement brandedprocurementdeleted opendate opstatus opstatusdate bpflagdate ownershiptype invoicesystem invoicesystems.systemname ordersystem ordersystems.systemname market.customwarehousename market.customwarehousenumber market.marketid/];

	my $excols; my $header;
	if($p->{exportas}){






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

		# $header = ["STORE #","CUSTOMER #","CUSTOMER AMPHIRE ID","DISTRIBUTOR AMPHIRE ID","WAREHOUSE AMPHIRE ID", "PARTNER ABBREV","BRAND","CONCEPT","OPERATOR TYPE","OPERATOR","SUB OPERATOR","SITE #","STORE NAME","ADDRESS LINE 1","CITY","STATE","ZIP","STORE STATUS - NEED TO CHANGE TO OPEN, CLOSED, PLANNED OPEN, TEMP CLOSED","LATITUDE","LONGITUDE","DISTANCE TO DC","BRAND ID","CONCEPT ALT ID","PARTNER NUMBER","OPERATING STATUS","OPERATING STATUS DATE","INVOICE SYSTEM","ORDER SYSTEM"];

		# $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"];

		# $excols = [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 routing.routingidnumber accountingcode filecode orderguideflagidval systemtype systemname systemfilespo systemfilesack systemfilesconf systemfilessalesinvoice systemfilescredit systemfilespa systemfilesog customwarehousename customwarehousenumber/];



	}elsif($p->{export}){

		# $excols = [qw/customerid customernumber amphireid distributor.name warehouse.distributorlocid chaingrp.chaingroupamphireid concept.chainconceptamphireid chaingrp.chaingroupvp chaingrp.chaingrouppam ownership customerunitnumber unitname address city state zip orderflag.dscr setupdate lastchangeddate user.fullname status.dscr ownership2 groupname longitude latitude distancetodistcenter countrycode systemname/];
		# $excols = [qw/customerid customernumber amphireid distributor.name warehouse.siteshortname chaingrp.chaingroupamphireid concept.chainconceptamphireid ownership customerunitnumber chainunit.chainunitname unitname address city state zip orderflag.dscr  status.dscr ownership2 longitude latitude distancetodistcenter systemname brandedprocurement distributorwarehouse.latitude distributorwarehouse.longitude opstatus opstatusdate comments/];
		$excols = [qw/customerid customernumber amphireid distributor.name warehouse.siteshortname chaingrp.chaingroupname concept.chainconceptname ownershiptype ownership ownership2 customerunitnumber chainunit.chainunitname unitname address city state zip orderflag.dscr  status.dscr  longitude latitude distancetodistcenter systemname brandedprocurement distributorwarehouse.latitude distributorwarehouse.longitude opendate opstatus opstatusdate invoicesystems.systemname ordersystems.systemname chaingrp.arrowstreamid concept.arrowstreamid warehouse.arrowstreamid comments/];

		$header = ["DMA Id","Dist Cust Nbr","Amphire Id","Distributor Co","Warehouse Short Name","Chain Group Id","Chain Concept Id","Operator Type","Operator", "Sub-Operator","Store Nbr","Chain Unit","Unit Name","Address","City","State","Zip","OG Flag","Status","Unit Latitude","Unit Longitude","Unit Distance to DC","System Name","Branded Procurement","DC Latitude","DC Longitude", "Operating Status","Operating Status Date","Invoice System","Order System","AS Group ID","AS Concept ID","AS Warehouse ID","Comments"];

# CustomerId CustomerNumber AmphireId DistributorCompany DistributorWarehouse ChainGroup ChainParent ChainConcept DMAVPId DMAProgramManagerId OwnershipId ownership CustomerUnitNumber UnitName Address City State Zip OrderGuideFlagId SetupDate LastChangedDate LastInvDate LastUpdatedById StatusId Ownership2 GroupName Longitude Latitude DistancetoDistCenter CountryCode SystemName

	}

	my $resort;
	my $sort = $p->{'order[0][column]'};
	$resort = 'systemname' and $p->{'order[0][column]'} = 0 if $sort == 12;


	# status.displayorder status.dscr status.id 'status'
	my $prefetch = ['status','distributor','concept','chaingrp','chainunit','user','warehouse','orderflag','ordersystems','invoicesystems','market','customersystem','routing',{'back_office_system_customers' => 'system'}];
	#  
	my $join = {'back_office_system_customers' => 'system'};
	#  'concept' => 'back_office_system_chain_concepts'
	my $as =  [qw/systemname systemid/];
	my $select = [{ 'group_concat' => 'system.systemname', -as => 'systemname'  },
		{ 'group_concat' => 'system.systemid', -as => 'systemid'  }
	];

	my $exportas = 0;
	if($p->{exportas}){

		push(@$select, \'IF(orderguideflagid="3","",IF(orderguideflagid="1","Y","P")) AS orderguideflagidval');
		push(@$as, 'orderguideflagidval');

		# $rparams->{'+select'} = [
		# 	\'IF(orderguideflagid="3","",IF(orderguideflagid="1","Y","P")) AS orderguideflagidval',
		# ];
		# $rparams->{'+as'} = [qw/orderguideflagidval/];	
			delete($p->{exportas});
			$c->req->params->remove('exportas');
			$exportas = 1;

	}

	show $select, $as;

	my $ecols; # = {'system.systemname' => \['GROUP_CONCAT(system.systemname)'] };
	# my $join; my $as; my $select; my $ecols;
	my $having;
	my $systemname = $p->{"columns[11][search][value]"};
	# $c->req->param('columns[11][search][value]','');
	$p->{"columns[11][search][value]"} = undef;
	if($systemname){
		$having = [ systemname => \[ "LIKE '%$systemname%'"] ];
	}
	my $systemid = $p->{'systemid[]'} || $p->{systemid};
	delete($p->{systemid});
	if($systemid){
		$having = ref($systemid) eq 'ARRAY' ? [ systemid => \[ "IN(".join(',', @$systemid).")"] ] : [ systemid => \[ "IN($systemid)"] ];
	}	

	my $jobj;

	if($exportas){

show "EXPORTAS";
		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"];

		# my $excols = [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 opendate	opstatus	opstatusdate routing.routingidnumber accountingcode filecode orderguideflagidval systemtype systemname systemfilespo systemfilesack systemfilesconf systemfilessalesinvoice systemfilescredit systemfilespa systemfilesog customwarehousename customwarehousenumber/];

		my $excols = [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 opendate	opstatus routing.routingidnumber accountingcode filecode orderguideflagidval systemtype systemname systemfilespo systemfilesack systemfilesconf systemfilessalesinvoice systemfilescredit systemfilespa systemfilesog customwarehousename customwarehousenumber/];


		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 opendate opstatus	opstatusdate routing.routingidnumber accountingcode filecode orderguideflagidval/];		

		# my $excols = [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 opendate	opstatus	opstatusdate routing.routingidnumber accountingcode filecode orderguideflagidval systemtype systemname systemfilespo systemfilesack systemfilesconf systemfilessalesinvoice systemfilescredit systemfilespa systemfilesog customwarehousename customwarehousenumber/];

		# 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 opendate	opstatus	opstatusdate routing.routingidnumber accountingcode filecode orderguideflagidval/];		

		my ($result,$rsearch, $rparams) = $c->dtsearch({
				returnresult => 1,
				as => $as, select => $select, ecols => $ecols, join => $join, params => $p, table=> 'Customer', order_by => $p->{order_by} || 'me.customerid', sort => 'desc', group_by => [qw/me.customerid/], having => $having, exheader => $header, excolumns=> $excols, columns => $cols, prefetch => [{'customersystem' => [qw/invoicesystems ordersystems markets/] },'status','distributor','concept','chaingrp','chainunit','user','warehouse','orderflag','market','ordersystems','invoicesystems','routing',{'back_office_system_customers' => 'system'}], defer => $having ? 0 : 1, nouc => {'customwarehousename' => 1}
		});

		# show $result,$rsearch, $rparams;


	my @objs;
	show scalar(@$result);
	foreach my $r (@$result){
		# next unless $r->{customerid} == 2000;
		my $obj;
		# 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)+$// if $cval;
				$obj->{$key} = $cval;
			}else{
				my $cval = $r->{$col}; $cval =~ s/(\s|\t)+$// if $cval;
				$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);				
						$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{
					push(@objs, $r);
				}

		}
		show scalar(@objs);
		my $dt = DateTime->now(time_zone  => 'America/Chicago');
		# my $filename = 'as.report.' . time() . '.csv';
		my $filename = 'CLM.Customer.AS.Export.' . $dt->ymd('') . sprintf("%02d", $dt->hour) . sprintf("%02d", $dt->minute) . '.csv';
		my $fileExport = '/tmp/' . $filename;

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

		$csv->column_names(@$excols);
		open my $fhe, ">:encoding(utf8)", $fileExport or die "$fileExport: $!";
		print $fhe join(",", @$header) . "\n";
		$csv->print_hr($fhe, $_) foreach @objs;
		close $fhe or die "$fileExport: $!";

    $c->res->headers->content_type('text/csv');
    $c->render_file(
            'filepath' => $fileExport,
            'format' => 'csv',                 
            # will change Content-Type "application/x-download" to "application/pdf"
            'content_disposition' => 'attachment',   
            # will change Content-Disposition from "attachment" to "inline"  
    ); 



		# show \@objs;

		return ($result,$rsearch, $rparams);

	}else{
show $p;

		$jobj = $c->dtsearch({as => $as, select => $select, ecols => $ecols, join => $join, params => $p, table=> 'Customer', order_by => $p->{order_by} || 'me.customerid', sort => 'desc', group_by => [qw/me.customerid/], having => $having, exheader => $header, excolumns=> $excols, columns => $cols, prefetch => $prefetch, defer => $having ? 0 : 1, nouc => {'customwarehousename' => 1}});
	}


	
	return 1 if $jobj == 1;
	# my @jdata;
	# foreach my $j (@{ $jobj->{data}}){
	# 	my $csdeleted = 0;
	# 	show $j;
	# 	if($j->{customersystem}){

	# 		foreach my $cs (@{$j->{customersystem}}){
	# 			if($cs->{customersystem}->{deleted}){
	# 				$csdeleted = 1;
	# 			}
	# 		}
	# 	}
	# 	push(@jdata,$j) if $csdeleted == 0;		
	# }
	# $jobj->{data} = \@jdata;

	if($resort){
		my $dir = $p->{'order[0][dir]'};
		my @data = @{ $jobj->{data} };
		if($dir eq 'desc'){
			@data = sort { $a->{$resort} cmp $b->{$resort} } @data;
		}else{
			@data = sort { $b->{$resort} cmp $a->{$resort} } @data;
		}
		$jobj->{data} = \@data;
	}





	# get table views
	my $u = $c->db->resultset('User')->find({user_id => $c->session->{user_id}});
	my $json = JSON->new->allow_nonref;
	my $settings = $json->decode( $u->settings || '{}' );
	my $views = $settings->{datatables}->{views}->{customertable};
	$jobj->{views} = $views;

	$c->render(json => $jobj);
}


sub report{
	my $c = shift;

	$c->stash(frm => undef,butt => undef);
	$c->stash(title=>'Report',table => 'customertable',module => 'customer');


	$c->render(template => 'customer/report');
}

sub report_orderguide {
	my $c = shift;
	my $p = $c->req->params->to_hash;

	my $cols = [qw/concept.chainconceptname warehouse.distributorwarehousename orderflag.dscr/];
	my $excols = [qw/concept.chainconceptname warehouse.distributorwarehousename orderflag.dscr flagcnt/];
	my @c = $c->db->resultset('Customer')->search({statusid => 1, orderguideflagid => [1,2]}, 
		{result_class=>'DBIx::Class::ResultClass::HashRefInflator',
			join  =>['distributor','chaingrp','warehouse','concept','status','user','orderflag'],
			group_by => [qw/chainconceptid distributorwarehouseid orderguideflagid/],
			columns => $cols,
			'+select' => [{'COUNT' => 'customerid', '-as' => 'flagcnt'}],
			'+as' => [qw/flagcnt/]
		}
	);

	my @c2 = $c->db->resultset('Customer')->search({statusid => 1, orderguideflagid => [3]}, 
		{result_class=>'DBIx::Class::ResultClass::HashRefInflator',
			join  =>['distributor','chaingrp','warehouse','concept','status','user','orderflag'],
			group_by => [qw/chainconceptid distributorwarehouseid orderguideflagid/],
			columns => $cols,
			'+select' => [{'COUNT' => 'customerid', '-as' => 'flagcnt'}],
			'+as' => [qw/flagcnt/]
		}
	);

	push(@c,@c2);


	@c = sort { $a->{concept}->{chainconceptname} cmp $b->{concept}->{chainconceptname} } @c;
# show $c;

    my $jobj = {
      "draw" => $p->{draw},
      "recordsTotal" => scalar(@c),
      "recordsFiltered" => scalar(@c),
      "data" => \@c,
      "header" => $cols
    };

    if($p->{export}){
    		my $fdir = '/tmp/';
			my $filename = 'OrderGuide.' . time() . '.xls';
			my $filepath = $fdir . $filename;
			my ( $fh, $ofilename ) = $c->export_hr(\@c,filename => $filepath, columns => $excols);

			my $dfilepath = getcwd() . '/public/files/' . $filename;
			move $filepath, $dfilepath;
			$c->res->headers->location('/files/' . $filename);
			$c->rendered(302);
			return 1;

	}else{
		$c->render(json => $jobj);
	}    

}

sub report_mismatched {
	my $c = shift;
	my $p = $c->req->params->to_hash;


	my @custsys = $c->db->resultset('CustomerSystem')->search({ 
		'me.active' => 1,
		'me.deleted' => 0,
		'me.marketid' => {'>' => 0},
		'markets.distributorwarehouseid' => {'>' => 0},
	},{
		# prefetch => [{customersystems => 'customer'}],
		prefetch => [qw/systems_customer/,{markets => [qw/distwhs chaingrp/]}],
		result_class=>'DBIx::Class::ResultClass::HashRefInflator',
	});

	my @data;
	foreach my $custsys (@custsys){
		if($custsys->{systems_customer}->{distributorwarehouseid} != $custsys->{markets}->{distributorwarehouseid}){
			push(@data,$custsys);
		}
	}


	my $length = $c->req->param('length') || 250;
	my $start = $c->req->param('start') || 0;

	my @rdata;
	if($length){
		@rdata = splice(@data,$start,$length+$start);
	}

show $rdata[0];	

	my $json = {
		"data" => \@rdata,
		"draw" => $c->req->param('draw'),
		"recordsFiltered" =>  scalar(@data),
		"recordsTotal" =>  scalar(@data)
	};

	$c->render(json => $json);

}



sub report_missing {
	my $c = shift;
	my $p = $c->req->params->to_hash;






	$p->{ssearch} = {
		'distributor.distributoramphireid' => {'NOT IN' => [qw/379 773 1119 808 385 888 828/]},


		statusid => 1,
		amphireid => ["-or" => [
				{ '=' => ""  },
				{ '=' => undef  }
			]
		]
	};

	foreach my $k (keys %$p){
		if($k =~ /columns\[(\d+)\]\[search\]\[value\]/ && $p->{$k} ne ""){
			my $col = $1;
			if($p->{"columns[$col][search][value]"}){
				$p->{ssearch}->{ $p->{"columns[$col][data]"} } = { 'LIKE' => '%' . $p->{"columns[$col][search][value]"} . '%' };
			}
			
		}
	}

	# $p->{asearch} = 1;
	# $p->{statusid} = 1;
	# $p->{amphireid} = ["-or" => [
	# 		{ '=' => ""  },
	# 		{ '=' => undef  }
	# 	]
	# ];

	# $p->{'columns[2][search][value]'} = {'!=' => "INACTIVE" };
	# $p->{'columns[3][search][value]'} = ["-or" => [
	# 		{ '=' => ""  },
	# 		{ '=' => undef  }
	# 	]
	# ];


	# if(!$p->{'order[0][column]'}){

	# 	$p->{order_by} = 'me.setupdate';
	# 	$p->{'order[0][column]'} = 'setupdate';
	# 	$p->{'order[0][dir]'} = 'asc';
	# 	$p->{ 'columns[setupdate][data]'  } = 'setupdate';

	# }
	

	$c->list($p);
}


sub export_missing_opstatus {
	my $c = shift;

 # select * from Customer where SetupDate >= DATE_SUB(NOW(), INTERVAL 1 WEEK) AND OpStatus="";
 # select * from Customer where LastChangedDate >= DATE_SUB(NOW(), INTERVAL 1 WEEK) AND OpStatus="";

 	my $dt =  DateTime->now(time_zone  => 'America/Chicago');

 	my $filename = 'dma_missing_opstatus.' . $dt->ymd('') . $dt->hms('') . '.xls';
 	if($c->req->param('updated')){
 		$filename = 'dma_missing_opstatus.updated.' . $dt->ymd('') . $dt->hms('') . '.xls';
		$c->req->param('ssearch', {
			lastchangeddate => {'>=' => \'DATE_SUB(NOW(), INTERVAL 1 WEEK)'},
			opstatus => "",
			chaingroupid => { 'NOT IN' => [4,107]}
		});
 	}else{
 		$filename = 'dma_missing_opstatus.created.' . $dt->ymd('') . $dt->hms('') . '.xls';
		$c->req->param('ssearch', {
			setupdate => {'>=' => \'DATE_SUB(NOW(), INTERVAL 1 WEEK)'},
			opstatus => "",
			chaingroupid => { 'NOT IN' => [4,107] }
		});

 	}


	my $p = $c->req->params->to_hash;


	$p->{export} = 1;
	# $p->{csv} = 1;


	my $cols = [qw/customerid customernumber amphireid distributor.distributoramphireid setupdate lastchangeddate unitname/];

	my $excols = [qw/customernumber customerid amphireid setupdate lastchangeddate unitname/];
	# status.displayorder status.dscr status.id 'status'
	my $join = [qw/distributor/];
	my $prefetch = [];
	my $ecols = [];
	


	my $jobj = $c->dtsearch({columns => $cols, excolumns => $excols, ecols => $ecols, join => $join, params => $p, table=> 'Customer', order_by => 'me.customerid', sort => 'asc', group_by => [qw/me.customerid/], prefetch => $prefetch, defer => 1, filename => $filename});
	
	return 1 if $jobj == 1;

	$c->render(json => $jobj);
}

sub report_market{
	my $c = shift;
	my $p = $c->req->params->to_hash;

# select * from CustomerSystem cs LEFT JOIN Customer c ON cs.CustomerId=c.CustomerId where cs.MarketId=0 AND c.ChainGroupId=42 AND c.MarketId>0\G
	my @res = $c->db->resultset('CustomerSystem')->search({ 'me.active'=> 1, 
		'systems_customer.statusid' => 1, 
		'systems_customer.marketid' =>  {'>' => 0}, 
		'me.marketid' => 0
	},{
			prefetch => [{'systems_customer' => [qw/market chaingrp concept/]}], 
			result_class=>'DBIx::Class::ResultClass::HashRefInflator',
	});

# return $c->render(text =>'something debug');

	my @data = @res; 
	# foreach my $r (@res){

	# }
	my $length = $c->req->param('length') || 250;
	my $start = $c->req->param('start') || 0;

	my @rdata;
	if($length){
		@rdata = splice(@res,$start,$length+$start);
	}

show $rdata[0];	

	my $json = {
		"data" => \@rdata,
		"draw" => $c->req->param('draw'),
		"recordsFiltered" =>  scalar(@data),
		"recordsTotal" =>  scalar(@data)
	};
# show $json;
	$c->render(json => $json);

}


sub report_gps {
	my $c = shift;

	my $p = $c->req->params->to_hash;
show $p;

	my $ssearch = {
		'-and' => [
			[
					'me.longitude' => ["-or" => [
						{ '=' => 0  },
						{ '=' => ""  },
						{ '=' => undef  }
					]],
					'me.latitude' => ["-or" => [
						{ '=' => 0  },
						{ '=' => ""  },
						{ '=' => undef  }
					]],
					'me.distancetodistcenter' => ["-or" => [
						{ '=' => 0  },
						{ '=' => ""  },
						{ '=' => undef  }
					]],
			],
			[
					'me.longitude' => { '>' => 0  },
					'me.latitude' => { '>' => 0  },
					'me.distancetodistcenter' => ["-or" => [
						{ '=' => 0  },
						{ '=' => ""  },
						{ '=' => undef  }
					]],
			],
			statusid => 1
		]		
	};

      my %rvals;
      my @cols;
      foreach my $k (keys %$p){
        if($k =~ /columns\[(\d+)\]\[data\]/){
          $cols[$1] = $p->{$k};
        }
      }
      foreach my $k (keys %$p){     
        if($k =~ /columns\[(\d+)\]\[search\]\[value\]/){
          $rvals{$cols[$1]} = $p->{$k};
        }
      }

      foreach my $c (keys %rvals){
      	show $c, $rvals{$c};
        if($c =~ /\./ && $rvals{$c} ne ""){
          if(ref($rvals{$c}) eq '' && $c !~ /.*id$/i){
            push(@{ $ssearch->{'-and'} }, $c => {'like', '%' . $rvals{$c} . '%'});
          }else{
             push(@{ $ssearch->{'-and'} }, $c => $rvals{$c});
          }
        }elsif($rvals{$c} ne ""){
          if(ref($rvals{$c}) eq ''  && $c !~ /.*id$/i){
            push(@{ $ssearch->{'-and'} }, 'me.' . $c => {'like', '%' . $rvals{$c} . '%'});
          }else{
             push(@{ $ssearch->{'-and'} }, 'me.' .$c => $rvals{$c} );
          }              
        }
      }


	$c->req->param('ssearch', $ssearch);


	my $p = $c->req->params->to_hash;
	$c->list($p);
}


sub export_gfs {
	my $c = shift;
	$c->req->param('ssearch', {
		'distributor.distributoramphireid' => 114,
		statusid => 1
	});
	my $p = $c->req->params->to_hash;


	$p->{export} = 1;
	$p->{csv} = 1;
	$p->{csv_seperator} = "\t";

	my $cols = [qw/customerid customernumber amphireid distributor.distributoramphireid/];

	my $excols = [qw/customernumber customerid/];
	# status.displayorder status.dscr status.id 'status'
	my $join = [qw/distributor/];
	my $prefetch = [];
	my $ecols = [];
	my $dt =  DateTime->now(time_zone  => 'America/Chicago');


	my $jobj = $c->dtsearch({columns => $cols, excolumns => $excols, ecols => $ecols, join => $join, params => $p, table=> 'Customer', order_by => 'me.customerid', sort => 'asc', group_by => [qw/me.customerid/], prefetch => $prefetch, defer => 1, filename => 'DMACustList' . '.txt'});
	
	return 1 if $jobj == 1;

	$c->render(json => $jobj);
}




sub export_itn{

	my $c = shift;
	my $p = shift || $c->req->params->to_hash;

	$p->{export} = 1;
	$p->{csv} = 1;
	$p->{csv_seperator} = "\t";
# Unit	ChainGroup	ChainConcept	CustomerId	CustomerUnitNumber	UnitName	Address	City	State	Zip	Ownership	StatusId	AmphireId	CustomerNumber	DistributorCompany	DistributorWarehouseId	OrderGuideFlagId	Ownership2	GroupName	DistanceDistCenter	Latitude	Longitude	CountryCode	SetupDate

	my $cols = [qw/chaingrp.chaingroupamphireid concept.chainconceptamphireid customerid customerunitnumber unitname address city state zip ownership statusid amphireid customernumber distributor.distributoramphireid warehouse.distributorlocid orderflag.dscr ownership2  distancetodistcenter latitude longitude countrycode setupdate/];

	my $excols = [qw/unit chaingrp.chaingroupamphireid concept.chainconceptamphireid customerid customerunitnumber unitname address city state zip ownership statusid amphireid customernumber distributor.distributoramphireid warehouse.distributorlocid orderflagdscr ownership2 chainunitname distancetodistcenter latitude longitude countrycode setupdate systemname/];
	# status.displayorder status.dscr status.id 'status'
	my $join = ['chainunit','status','distributor','chaingrp','concept','user','warehouse','orderflag',{'back_office_system_customers' => 'system'}];
	my $as =  [qw/systemname orderflagdscr unit setupdate chainunitname/];
	my $select = [{ 'group_concat' => 'system.systemname', -as => 'systemname'  },
		\[ "IF(orderflag.dscr = 'N/A','',orderflag.dscr)"] ,
		{ 'CONCAT' => '"01"', -as => 'unit' },
		\[ "DATE_FORMAT(setupdate,'%m/%d/%Y %r')"] ,
		\["chainunit.chainunitname as chainunitname"]
	];
	my $prefetch = [];
	my $ecols = [qw/unit systemname/];
	my $dt =  DateTime->now(time_zone  => 'America/Chicago');
	my $date = $dt->ymd('') . $dt->hms('');

	my $trim = { unitname => 100, _default => 50 };

	my $jobj = $c->dtsearch({columns => $cols, excolumns => $excols, as => $as, select => $select, ecols => $ecols, join => $join, params => $p, table=> 'Customer', order_by => 'me.customerid', sort => 'asc', group_by => [qw/me.customerid/], prefetch => $prefetch, defer => 1, trim => $trim, filename => 'DMAOPF' . $date . '.TXT'});
	
	return 1 if $jobj == 1;

	$c->render(json => $jobj);
}

sub export_itn_bp{

	my $c = shift;
	my $p = shift || $c->req->params->to_hash;

	$p->{export} = 1;
	$p->{csv} = 1;
	$p->{csv_seperator} = "\t";
# Unit	ChainGroup	ChainConcept	CustomerId	CustomerUnitNumber	UnitName	Address	City	State	Zip	Ownership	StatusId	AmphireId	CustomerNumber	DistributorCompany	DistributorWarehouseId	OrderGuideFlagId	Ownership2	GroupName	DistanceDistCenter	Latitude	Longitude	CountryCode	SetupDate

	my $cols = [qw/chaingrp.chaingroupamphireid concept.chainconceptamphireid customerid customerunitnumber unitname address city state zip ownership statusid amphireid customernumber distributor.distributoramphireid warehouse.distributorlocid orderflag.dscr ownership2  distancetodistcenter latitude longitude countrycode setupdate brandedprocurement/];

	my $excols = [qw/unit chaingrp.chaingroupamphireid concept.chainconceptamphireid customerid customerunitnumber unitname address city state zip ownership statusid amphireid customernumber distributor.distributoramphireid warehouse.distributorlocid orderflagdscr ownership2 chainunitname distancetodistcenter latitude longitude countrycode setupdate systemname brandedprocurement/];
	# status.displayorder status.dscr status.id 'status'
	my $join = ['chainunit','status','distributor','chaingrp','concept','user','warehouse','orderflag',{'back_office_system_customers' => 'system'}];
	my $as =  [qw/systemname orderflagdscr unit setupdate chainunitname/];
	my $select = [{ 'group_concat' => 'system.systemname', -as => 'systemname'  },
		\[ "IF(orderflag.dscr = 'N/A','',orderflag.dscr)"] ,
		{ 'CONCAT' => '"01"', -as => 'unit' },
		\[ "DATE_FORMAT(setupdate,'%m/%d/%Y %r')"] ,
		\["chainunit.chainunitname as chainunitname"]
	];
	my $prefetch = [];
	my $ecols = [qw/unit systemname/];
	my $dt =  DateTime->now(time_zone  => 'America/Chicago');
	my $date = $dt->ymd('') . $dt->hms('');

	my $trim = { unitname => 100, _default => 50 };

	my $jobj = $c->dtsearch({columns => $cols, excolumns => $excols, as => $as, select => $select, ecols => $ecols, join => $join, params => $p, table=> 'Customer', order_by => 'me.customerid', sort => 'asc', group_by => [qw/me.customerid/], prefetch => $prefetch, defer => 1, trim => $trim, filename => 'DMAOPF' . $date . '.TXT'});
	
	return 1 if $jobj == 1;

	$c->render(json => $jobj);
}

sub export_bpunitmaster{
	my $c = shift;
	my $p = shift || $c->req->params->to_hash;

	$p->{export} = 1;
	$p->{csv} = 1;
	$p->{csv_seperator} = "\t";



# DMA+Unit#	DMA	DistributorCode(new field)	DistributorCentrCode(new field)	Customer#	ConceptITNid	UnitName	No-Add1	No-Add2	No-Add3	No-City	No-State	No-Postal	Country(2char)	No-Phone	No-Email	DistributorCodeStorefront	LanguageCode(en-US)	NoGLN	NoReserved	CustomerActiveInactive(A|I)	UnitActiveInactive(A|I)	BPStatus	ALUpdate(M)	UpdateType(M)	
	# MUST USE FKEY distributorcompanyid TO FORCE LEFT JOIN??????????????? OR ELSE SINGLE SUB SELECTS???
	my $cols = [qw/distributor.distributorcompanyid warehouse.distributorwarehouseid concept.chainconceptid chaingrp.chaingroupid chainunit.chainunitid distributor.distributoramphireid distributor.bpdcode amphireid customernumber warehouse.distributorlocid concept.chainconceptamphireid chainunit.chainunitname chainunit.active warehouse.bplocationid brandedprocurement me.statusid chaingrp.bpstorefrontcode distributor.bpdcode brandedprocurementdeleted/];

	my $excols = [qw/unit dma distributor.bpdcode warehouse.bplocationid customernumber concept.chainconceptamphireid chainunit.chainunitname blank blank blank blank blank blank blank blank blank storefront language blank blank customeractive unitactive modify modify/];

	my $h = $c->req->url->base->host; 
	$c->stash('dev',0);
	if($h =~ /^dev/ || $c->req->param('refresh')){
		$c->stash('dev',1);
	}
show $c->stash('dev');
	# status.displayorder status.dscr status.id 'status'
	my $join = ['distributor','chaingrp','concept','warehouse','chainunit'];
	my $as =  [qw/unit dma language blank unitactive customeractive bpactive modify storefront/];
	my $select = [
		\['CONCAT("DMA","-",chainunit.chainunitid)'],
		\['"DMA"'],
		\['"EN-US"'],
		\['""'],
		\['IF(chainunit.active=1,IF(me.brandedprocurementdeleted=1,"I","A"),IF(me.brandedprocurementdeleted=1,"I","I"))'],
		# \['IF(chainunit.active=1,"A","I")'],
		\['IF(statusid=1,"A","I")'],
		\['IF(brandedprocurement=1,"A","I")'],
		($c->stash('dev') ? \['"R"'] : \['"M"']),
		\['IF(chaingrp.bpstorefrontcode!="",chaingrp.bpstorefrontcode,distributor.bpdcode)']
	];

	# I D M M
	my $prefetch = [];
	my $ecols = [qw/user unit dma password blank statuschar modify/];

	$p->{ssearch} = {brandedprocurement => 1, 'me.chainunitid' => { '!=' => 0 }};

	my $dt =  DateTime->now(time_zone  => 'America/Chicago');
	my $date = $dt->ymd('') . $dt->hms('');
	my $jobj = $c->dtsearch({columns => $cols, excolumns => $excols, as => $as, select => $select, ecols => $ecols, join => $join, params => $p, table=> 'Customer', order_by => 'me.customerid', sort => 'asc', group_by => [qw/me.customerid/], prefetch => $prefetch, defer => 1, filename => 'DMAUNITMASTER.' . $date . '.TXT', sep_char => '|', exportrowcb => sub {
			my $cols = shift;

# FSA logins			
			foreach my $c (@$cols){
				if($c =~ /^(201|501)~(.*)$/){
					$c = $2;
				}
			}

	  }
	});
	
	return 1 if $jobj == 1;

	$c->render(json => $jobj);

}

sub export_bpunitloginent{
	my $c = shift;
	my $p = shift || $c->req->params->to_hash;

	$p->{export} = 1;
	$p->{csv} = 1;
	$p->{csv_seperator} = "\t";


	# get customernumber for BUYEFF and BIRCH
	my @buyeffbirch;
	my $cnt = $c->db->resultset('Customer')->search({brandedprocurement => 1,  '-or' => [{'system.systemname' => 'BIRCHPW'},{'system.systemname' => 'BUYEFF'}] },{ prefetch => [qw/distributor/], join => [{'back_office_system_customers' => 'system'}], '+as' => [qw/systemname/], '+select' => [\'GROUP_CONCAT(system.systemname) AS systemname'], group_by => 'back_office_system_customers.customerid', result_class=>'DBIx::Class::ResultClass::HashRefInflator' })->count;

	if($cnt){	
		@buyeffbirch =  $c->db->resultset('Customer')->search({brandedprocurement => 1,  '-or' => [{'system.systemname' => 'BIRCHPW'},{'system.systemname' => 'BUYEFF'}] },{ prefetch => [qw/distributor/], join => [{'back_office_system_customers' => 'system'}], '+as' => [qw/systemname/], '+select' => [\'GROUP_CONCAT(system.systemname) AS systemname'], group_by => 'back_office_system_customers.customerid', result_class=>'DBIx::Class::ResultClass::HashRefInflator' });
	}

	# my @buyeffbirch =  $c->db->resultset('BackOfficeSystem')->search({'customer.brandedprocurement' => 1,  '-or' => [{'me.systemname' => 'BIRCHPW'},{'me.systemname' => 'BUYEFF'}] },{ 
	# 		prefetch => [{back_office_system_customers => {customer => 'distributor'}}], 
	# 		#join => [{'back_office_system_customers' => 'system'}], 
	# 		# '+as' => [qw/systemname/], 
	# 		# '+select' => [\'GROUP_CONCAT(me.systemname) AS systemname'], 
	# 		group_by => 'customers.customerid', 
	# 		result_class=>'DBIx::Class::ResultClass::HashRefInflator' 
	# });

#show @buyeffbirch;
	my %buyeffbirch;
	foreach my $r (@buyeffbirch){
		$buyeffbirch{ $r->{customernumber} } = $r->{distributor}->{distributorcode} . $r->{customerid};
	}
#show %buyeffbirch;


	# MUST USE FKEY distributorcompanyid TO FORCE LEFT JOIN??????????????? OR ELSE SINGLE SUB SELECTS???
	my $cols = [qw/distributor.distributorcompanyid warehouse.distributorwarehouseid concept.chainconceptid chaingrp.chaingroupid chainunit.chainunitid distributor.distributoramphireid distributor.bpdcode warehouse.bplocationid distributor.distributoramphireid amphireid me.statusid customernumber unitname brandedprocurementdeleted/];

	my $excols = [qw/dma unit user distributor.bpdcode warehouse.bplocationid customernumber user no unitname blank blank blank statuschar modify modify/];

	


	# status.displayorder status.dscr status.id 'status'
	my $join = ['distributor','chaingrp','concept','warehouse','chainunit'];
	my $as =  [qw/user unit dma password blank statuschar modify no/]; # systemname
	my $select = [\['CONCAT(customernumber,distributor.bpdcode,warehouse.bplocationid)'],
		\['CONCAT("DMA","-",me.chainunitid)'],
		\['"DMA"'],
		\['"Welcome1"'],
		\['""'],
		\['IF(me.statusid=1,IF(me.brandedprocurementdeleted=1,"I","A"),IF(me.brandedprocurementdeleted=1,"I","I"))'],
		($c->stash('dev') || $c->req->param('refresh') ? \['"R"'] : \['"M"']),
		\['"N"'],
		# \['GROUP_CONCAT(system.systemname)']
	];
	# D M M
	my $prefetch = [];
	my $ecols = [qw/user unit dma password blank statuschar modify/];

	$p->{ssearch} = {brandedprocurement => 1};

	my $trim = { unitname => 49, _default => 50 };

	my $dt =  DateTime->now(time_zone  => 'America/Chicago');
	my $date = $dt->ymd('') . $dt->hms('');
	my $jobj = $c->dtsearch({columns => $cols, excolumns => $excols, as => $as, select => $select, ecols => $ecols, join => $join, params => $p, table=> 'Customer', order_by => 'me.customerid', sort => 'asc', group_by => [qw/me.customerid/], prefetch => $prefetch, defer => 1, filename => 'DMAUNITLOGONENT.' . $date . '.TXT', sep_char => '|',  exportrowcb => sub {
			my $cols = shift;




			# foreach my $c (@$cols){

			# }
			#100264800GFSUSGFS_16-CPS
			foreach my $c (@$cols){
				if($c =~ /GFSUSGFS/){
					if($c =~ /^(\d+)\s*GFSUSGFS_(\d+).*$/){
						$c = $1 . 'GFSUS' . $2;
					}
				}
			}

# GFS Logon
			foreach my $c (@$cols){
				if($c =~ /\d+~(\d+)(FSA|SSA).*_(\d+).*/){
					$c = $1 . $2 . $3;

				# 44213759USFSUSFS_9O-DMA
				}
				if($c =~ /USFS/){
					if($c =~ /^(\d+)USFS/){
						$c = $1 . 'USFS9O';
					}
				}

			}


# FSA logins			
			foreach my $c (@$cols){
				if($c =~ /^(201|501)~(.*)$/){
					$c = $2;
				}
			}
# 			my $sycnt = $c->db->resultset('Customer')->search({ customernumber => $cols->[5], '-or' => [{'system.systemname' => 'BIRCH'},{'system.systemname' => 'BUYEFF'}] },{ join => [{'back_office_system_customers' => 'system'}], '+as' => [qw/systemname/], '+select' => [\'GROUP_CONCAT(system.systemname) AS systemname'], result_class=>'DBIx::Class::ResultClass::HashRefInflator' })->count; # in ARRAY or STRING	
# show $sycnt if $sycnt;				
			# foreach my $sys (@systems){
			# 	if($sys->{systemname} eq 'BIRCH' || $sys->{systemname} eq 'BUYEFF'){

			# 	}
			# }





			if( exists($buyeffbirch{ $cols->[5] }) ){
				$cols->[6] = $buyeffbirch{ $cols->[5] };
				$cols->[6] = ucfirst(lc($cols->[6]));
				$cols->[7] = 'N';
			}

# home baackup for import				
				$cols->[2] =~ s/\s+//g;
#				$cols->[2] =~ s/[^[:ascii:]]//g;
				$cols->[6] =~ s/\s+//g;
#				$cols->[6] =~ s/[^[:ascii:]]//g;


			if($cols->[4] =~ /9O-DMA/ && $cols->[2] =~ /(84193580|34192773|34192765|54192794|64192768|74192766)/){
					my $cno = $1;
					$cols->[2] = $cno . 'USFS9OLOV';
					$cols->[6] = $cno . 'USFS9OLOV';
			}


		}});


	return 1 if $jobj == 1;

	$c->render(json => $jobj);

}

sub export_bpthirdpartyacount{
	my $c = shift;
	my $p = shift || $c->req->params->to_hash;

	$p->{export} = 1;
	$p->{csv} = 1;
	$p->{csv_seperator} = "\t";
# DistributorCode	DistributorWarehouse/DistributorLocId	CustomerNumber	SystemName	-blank	Y	Y	Y	Y	R

	# MUST USE FKEY distributorcompanyid TO FORCE LEFT JOIN??????????????? OR ELSE SINGLE SUB SELECTS???
	my $cols = [qw/distributorcompanyid distributor.bpdcode distributor.distributorcode warehouse.distributorlocid warehouse.bplocationid customernumber/];

	my $excols = [qw/distributor.bpdcode warehouse.bplocationid customernumber systemname blank rchar crtfield uuid/];

	# status.displayorder status.dscr status.id 'status'
	my $join = ['status','distributor','chaingrp','concept','user','warehouse','orderflag',{'back_office_system_customers' => 'system'}];
	my $as =  [qw/systemname blank rchar crtfield uuid/];
	my $select = [
		{ 'group_concat' => 'system.systemname', -as => 'systemname'  },
		\[ '""'], # blank
		\['"M"'],
		{'CONCAT' => ['warehouse.distributorwarehouseamphireid','"-"','me.customernumber'], -as => 'crtfield' },
		{'CONCAT' => ['me.customerid'], -as => 'uuid'}
	];
	my $prefetch = [];
	my $ecols = [qw/systemname blank rchar/];

	$p->{ssearch} = {'system.systemname' => [ "-or" => [
			{ '!=' => ""  },
			{ '!=' => undef  }
		],
	],

	'me.brandedprocurement' => 1
	};

	# my $having = \'COUNT(me.brandedprocurement) > 1';

	my $dt =  DateTime->now(time_zone  => 'America/Chicago');
	my $date = $dt->ymd('') . $dt->hms('');

	my $jobj = $c->dtsearch({columns => $cols, excolumns => $excols, as => $as, select => $select, ecols => $ecols, join => $join, params => $p, table=> 'Customer', order_by => 'me.customerid', sort => 'asc', group_by => [qw/me.customerid/], prefetch => $prefetch, defer => 1, filename => 'DMATHIRDPARTYACCOUNT.' . $date . '.TXT', sep_char => '|', exportrowcb => sub {
			my $cols = shift;

# RFS|508|62058|CRT|508-62058|M		
			# foreach my $c (@$cols){
			if($cols->[3] =~ /^(CRT|DLTJ)/){
				$cols->[4] = $cols->[6];
			}
			if($cols->[3] =~ /^(CRTPANDA)/ && $cols->[0] !~ /BEK/){
				$cols->[4] = $cols->[2]
			}
			# }
			pop(@$cols);
			pop(@$cols);

	  }});
	
	return 1 if $jobj == 1;

	$c->render(json => $jobj);
}

sub export_bphierarchymaster{

# childid/concept	DMA 	parentid/chain		conceptname

	my $c = shift;
	my $p = shift || $c->req->params->to_hash;

	$p->{export} = 1;
	$p->{csv} = 1;
	$p->{csv_seperator} = "\t";



	my @rows;

	$c->db->storage->dbh_do(sub{ 
		my ($s, $d, @a) = @_; 

		my $stmt = q/SELECT me.chainconceptid, me.chainconceptamphireid, "DMA" dma, chaingrp.chaingroupamphireid, chaingrp.chaingroupname, me.chainconceptname FROM ChainConcept me  JOIN ChainGroup chaingrp ON chaingrp.chaingroupid = me.chaingroupid LEFT JOIN Customer customer ON customer.chainconceptid = me.chainconceptid WHERE customer.brandedprocurement=1 GROUP BY customer.chainconceptid, me.chainconceptid HAVING COUNT(customer.brandedprocurement) >= 1 ORDER BY me.chainconceptid ASC/;		

		my $tbl = $d->selectall_hashref($stmt,qw/chainconceptid/);
		my @tbl;
		foreach my $k (keys %$tbl){
			push(@tbl,$tbl->{$k});
		}

		
    # {
    #   chainconceptamphireid => 1044702,
    #   chainconceptid => 80,
    #   chainconceptname => "PANCHEROS",
    #   chaingroupamphireid => 1044701,
    #   chaingroupname => "PANCHEROS",
    #   dma => "DMA",
    # }
    	my %group; my %gseen;
    	my %concept;
		foreach my $r (@tbl){
			$group{$r->{chaingroupamphireid}} = $r->{chaingroupname} unless $gseen{$r->{chaingroupamphireid}};
			$gseen{$r->{chaingroupamphireid}} = 1;

			push(@{ $concept{$r->{chaingroupamphireid}} }, { chainconceptamphireid => $r->{chainconceptamphireid}, chainconceptname => $r->{chainconceptname} });
		}

		my @group = keys %group;
		@group = sort(@group);

no warnings 'uninitialized';
		foreach my $g (@group){
# 1010029|DMA||QDOBA MEXICAN GRILL			
# 1010054|DMA|1010029|QDOBA			
			my @cgroup = ($g,'DMA','DMA',$group{$g});
			push(@rows, join('|',@cgroup));

			foreach my $cn (@{$concept{$g}}){

				my @ccon = ($cn->{chainconceptamphireid}, 'DMA', $g, $cn->{chainconceptname});
				push(@rows, join('|',@ccon));

			}

		}

	});

	my $out = join("\r\n", @rows);

	my $dt =  DateTime->now(time_zone  => 'America/Chicago');
	my $date = $dt->ymd('') . $dt->hms('');

	my $fdir = '/tmp/';
	my $filename = 'DMAHIERARCHYMASTER.' . $date . '.TXT';
	my $filepath = $fdir . $filename;
	my $path = Mojo::File->new($filepath);
	$path->spurt($out);

	$c->render_file(
	'filepath' => $filepath,
	'format' => 'csv',                 
	'content_disposition' => 'attachment',   
	);  


	$c->render(text => $out);



	# $p->{asearch} = 1;
	# $p->{brandedprocurement} = 1;
	# MUST USE FKEY distributorcompanyid TO FORCE LEFT JOIN??????????????? OR ELSE SINGLE SUB SELECTS???
	# my $cols = [qw/chaingroupid chaingrp.chaingroupname chainconceptname chainconceptamphireid chaingrp.chaingroupname chaingrp.chaingroupamphireid/];


	# my $cols = [qw/chaingroupid chaingrp.chaingroupamphireid chainconceptamphireid chaingrp.chaingroupname chaingrp.chaingroupamphireid chainconceptname/];

	# my $excols = [qw/chainconceptamphireid dma chaingrp.chaingroupamphireid chainconceptname/];

	# # status.displayorder status.dscr status.id 'status'
	# my $join = ['chaingrp','customer'];
	# my $as =  [qw/dma/];
	# my $select = [
	# 	\['"DMA"'],
	# ];
	# my $prefetch = [];
	# my $ecols = [qw/dma/];

	# my $having = \'COUNT(customer.brandedprocurement) > 1';

	# my $dt =  DateTime->now(time_zone  => 'America/Chicago');
	# my $date = $dt->ymd('') . $dt->hms('');

	# my $jobj = $c->dtsearch({columns => $cols, excolumns => $excols, as => $as, select => $select, ecols => $ecols, join => $join, params => $p, table=> 'ChainConcept', order_by => 'me.chainconceptid', sort => 'asc', group_by => [qw/customer.chainconceptid me.chainconceptid/], prefetch => $prefetch, defer => 1, having => $having  , filename => 'DMAHIERARCHYMASTER.' . $date . '.TXT', sep_char => '|'});
	
	# return 1 if $jobj == 1;

	# $c->render(json => $jobj);
}


sub export_gps{
	my $c = shift;
	my $p = shift || $c->req->params->to_hash;

	$p->{asearch} = 1;
	$p->{statusid} = 1;
	$p->{export} = 1;
	# $p->{csv} = 1;
	# $p->{csv_seperator} = "\t";


	# MUST USE FKEY distributorcompanyid TO FORCE LEFT JOIN??????????????? OR ELSE SINGLE SUB SELECTS???
	my $cols = [qw/distributor.distributorcompanyid warehouse.distributorwarehouseid concept.chainconceptid chaingrp.chaingroupid chainunit.chainunitid distributor.distributoramphireid distributor.bpdcode warehouse.bplocationid distributor.distributoramphireid amphireid me.statusid customernumber unitname customerid unitname customerunitnumber address city state zip latitude longitude distancetodistcenter warehouse.siteshortname warehouse.address warehouse.cityname warehouse.state warehouse.zip warehouse.latitude warehouse.longitude/]; # ordersystems.systemname invoicesystems.systemname

	# my $excols = [qw/dma unit user distributor.bpdcode warehouse.bplocationid customernumber user no unitname blank blank blank statuschar modify modify/];
	my $excols = [qw/customernumber amphireid unitname customerunitnumber address city state zip latitude longitude distancetodistcenter warehouse.siteshortname warehouse.address warehouse.cityname warehouse.state warehouse.zip warehouse.latitude warehouse.longitude/]; # ordersystem invoicesystem

	# status.displayorder status.dscr status.id 'status'
	my $join = ['distributor','chaingrp','concept','warehouse','chainunit',]; # {'customersystem' => [qw/invoicesystems ordersystems market/] }
	my $as =  [qw//]; # systemname
	my $select = [];
	my $prefetch = [];
	my $ecols = [qw//];

	# $p->{ssearch} = {brandedprocurement => 1};


	my $dt =  DateTime->now(time_zone  => 'America/Chicago');
	my $date = $dt->ymd('') . $dt->hms('');
	my $jobj = $c->dtsearch({columns => $cols, excolumns => $excols, as => $as, select => $select, ecols => $ecols, join => $join, params => $p, table=> 'Customer', order_by => 'me.customerid', sort => 'asc', group_by => [qw/me.customerid/], prefetch => $prefetch, defer => 1, filename => 'DMACLMGPS.' . $date . '.xls', sep_char => ","});


	return 1 if $jobj == 1;

	$c->render(json => $jobj);

}





sub setup{
	my $c = shift;
	my $p = $c->req->params->to_hash;
	show $p;

	my $urlpath = $c->req->url->path->to_abs_string;
	my $customerid;
	if($urlpath =~ /(\d+)/){
		$customerid = $1;
	}

	my $host = $c->req->url->base->host;
	show $host;
	# my $eorder = [qw/  statusid opfstatus customernumber amphireid distributorcompanyid distributorwarehouseid chaingroupid chainconceptid chaingroupvp chaingrouppam brandedprocurement bpflagdate email emailgov longitude latitude distancetodistcenter comments customerid empty empty empty empty empty empty ownershiptype ownership ownership2 chainunitid customerunitnumber unitname address city state zip countrycode orderguideflagid systemid opstatus opstatusdate invoicesystem ordersystem marketid routingid accountingcode filecode setupdate lastchangeddate lastupdatedbyid/ ];
	# if($host =~ /dev\./ || $host =~ /\.rajya/){
	my $eorder = [qw/  statusid opfstatus customernumber amphireid chaingroupid chainconceptid distributorcompanyid distributorwarehouseid chaingroupvp chaingrouppam brandedprocurement bpflagdate email emailgov longitude latitude distancetodistcenter comments customerid setupdate lastchangeddate lastupdatedbyid empty empty empty empty empty empty entitytypeid ownershiptype ownership ownership2 chainunitid customerunitnumber unitname address city state zip countrycode orderguideflagid systemid opendate opstatus opstatusdate storestatus storestatusdate routingid invoicesystem ordersystem marketid  accountingcode filecode customersystem empty customersystemcontent/ ];

#   
	my $eorder = [qw/statusid customernumber customerid chaingroupid chainconceptid distributorcompanyid distributorwarehouseid ownershiptype ownership ownership2 entitytypeid customerunitnumber chainunitid unitname paneladdresshead paneladdressbodyhead address city state zip countrycode paneladdressbodyfoot paneladdressfoot opendate opstatus opstatusdate storestatus storestatusdate orderguideflagid empty 
		 comments commentslist panelmetadatahead panelmetadatabodyhead longitude latitude distancetodistcenter setupdate setupbyid lastchangeddate lastupdatedbyid panelmetadatabodyfoot panelmetadatafoot amphireid opfstatus systemid chaingroupvp chaingrouppam brandedprocurement bpflagdate email emailgov routingid invoicesystem ordersystem marketid  accountingcode filecode customersystem empty customersystemcontent/ ];

# entitytypeid storestatus storestatusdate


	my $fform = {
		attrs => {
			name => 'customerform',
			onsubmit => 'javascript: return false;',
			title => '',
			action => '',
			method => 'POST',
			# formon => '',
			# formoff => '',
			formon => 'form-always',
			formoff => 'form-never',
			cols => 2
		},
		eorder => $eorder,
		sorder => [qw//],
		elems =>     {
			empty => { attrs =>{ name => 'empty'}, type => 'empty' },
			# opstatus => {
			#       attrs => { class => "form-control input-md", name => "opstatus" },
			#       default => undef,
			#       labels => ["NEW","OWNERSHIP CHANGE","WAREHOUSE TRANSFER","CLOSURE"],
			#       # populate => { defaults => "orderflag", labels => "dscr", vals => "id" },
			#       title => "Operating Status",
			#       type => "select",
			#       vals => ["NEW","OWNERSHIP CHANGE","WAREHOUSE TRANSFER","CLOSURE"],
			#     },	
# paneladdresshead paneladdressbodyhead 
# paneladdressbodyfoot paneladdressfoot

# panelmetadatahead panelmetadatabodyhead 
# panelmetadatabodyfoot panelmetadatafoot
			panelmetadatahead => {
			      attrs => { class => "", name => "panelmetadata" },
			      title => "MetaData",
			      type => "panelhead",
			      val => undef,
			    },
			panelmetadatafoot => {
			      attrs => { class => "", name => "panelmetadatafoot" },
			      title => "MetaData",
			      type => "panelfoot",
			      val => undef,
			    },
			panelmetadatabodyhead => {
			      attrs => { class => "", name => "panelmetadatabodyhead" },
			      title => "MetaData",
			      type => "panelbodyhead",
			      val => undef,
			    },	
			panelmetadatabodyfoot => {
			      attrs => { class => "", name => "panelmetadatabodyfoot" },
			      title => "MetaData",
			      type => "panelbodyfoot",
			      val => undef,
			    },
			paneladdresshead => {
			      attrs => { class => "", name => "paneladdress" },
			      title => "Address",
			      type => "panelhead",
			      val => undef,
			    },
			paneladdressfoot => {
			      attrs => { class => "", name => "paneladdressfoot" },
			      title => "Address",
			      type => "panelfoot",
			      val => undef,
			    },
			paneladdressbodyhead => {
			      attrs => { class => "", name => "paneladdressbodyhead" },
			      title => "Address",
			      type => "panelbodyhead",
			      val => undef,
			    },	
			paneladdressbodyfoot => {
			      attrs => { class => "", name => "paneladdressbodyfoot" },
			      title => "Address",
			      type => "panelbodyfoot",
			      val => undef,
			    },				    		    			    
			customersystemcontent => {
			      attrs => {
			                 class => "form-control input-md",
			                 name => "customersystemcontent",
			                 # required => "required",
			               },
			      title => "",
			      type => "readonly",
			      val => undef,	
			      format => sub {
			      	my $v = shift;
			      	return '';
			      }	

			},
			customersystem => {

			      attrs => {
			                 class => "form-control input-md",
			                 name => "customersystem",
			                 # required => "required",
			               },
			      title => "3rd Party Systems",
			      type => "readonly",
			      val => undef,	
			      format => sub {
			      	my $v = shift;
			      	return '<a style="font-size: 1.25em; font-weight: bold;" class="customersystemview" href="javascript:;">ADD</a>';
			      }	


			},


			bpflagdate => {
			      attrs => {
			                 class => "form-control input-md",
			                 name => "bpflagdate",
			                 # required => "required",
			               },
			      title => "BP Flag Date",
			      type => "hidden",
			      val => undef,	
			      format => sub {
			      	my $v = shift;

			      	return "" if $v eq '0000-00-00 00:00:00' || $v !~ /\d+-\d+-\d+ \d+:\d+:\d+/;
			      	my $dt = DateTime::Format::DateParse->parse_datetime( $v, 'America/Chicago' );
			      	$v = $dt->strftime( "%D %r");
			      	return $v;
			      }			      			
			},
			opstatus => {
			      attrs => {
			                    class => "input-md form-control",
			                    name => "opstatus",
			                    # required => "required",
			                  },
			      default => undef,
			      labels => [],
			      populate => {
			                    defaults => {
			                    	module => "OperatingStatus",
			                    	options => {
						                prefetch => [qw//],	                    		
			                    		order_by => "operatingstatusid"
			                    	},
			                    	# fk => 'id',
			                    	where => [qw//],
			                    	vals => [],
			                    },			                    
			                    labels => 'operatingstatusname',
			                    vals => "operatingstatusname",
			                    data => [qw//]
			                  },				                  
			      title => "Operating Status",
			      type => "select",
			      vals => [],
			      data => [],
			    },			
			opendate => {
			      attrs => {
			                 class => "form-control input-md",
			                 name => "opendate",
			                 "data-format" => "YYYYMMDD",
			                 placeholder => "YYYYMMDD",
			                 # readonly => "readonly"
			                 # required => "required",
			               },
			      title => "Planned Open Date",
			      type => "textfield",
			      val => undef,
			},			


			opstatusdate => {
			      attrs => {
			                 class => "form-control input-md",
			                 name => "opstatusdate",
			                 "data-format" => "YYYYMMDD",
			                 readonly => "readonly"
			                 # required => "required",
			               },
			      title => "Operating Status Date",
			      type => "textfield",
			      val => undef,
			},			
			storestatus => {


			      attrs => { class => "form-control input-md", name => "storestatus"},
			      default => undef,
			      title => "Store Status",
			      type => "select",
			      vals => ['OPEN','CLOSED','PLANNED OPEN','TEMP CLOSED'],
			      labels => ['OPEN','CLOSED','PLANNED OPEN','TEMP CLOSED'],


			},			
			storestatusdate => {
			      attrs => {
			                 class => "form-control input-md",
			                 name => "storestatusdate",
			                 "data-format" => "YYYYMMDD",
			                 placeholder => "YYYYMMDD",
			               },
			      title => "Store Status Date",
			      type => "textfield",
			      val => undef,
			},
			address => {
			      attrs => {
			                 class => "form-control input-md",
			                 name => "address",
			                 required => "required",
			               },
			      title => "Address",
			      type => "textfield",
			      val => undef,
			    },
			amphireid => {
			      attrs => { class => "form-control input-md", name => "amphireid" },
			      title => "ITN ID",
			      type => "textfield",
			      val => undef,
			    },
			entitytypeid => {
			      attrs => {
			                    class => "input-md form-control",
			                    name => "entitytypeid",
			                  },
			      default => undef,
			      labels => [],
			      populate => {
			                    defaults => {
			                    	module => "EntityType",
			                    	options => {
						                	prefetch => [qw//],	                    		
			                    		order_by => "entityname"
			                    	},
			                    	# fk => 'id',
			                    	where => [qw/active deleted/],
			                    	vals => [1,0],
			                    },			                    
			                    labels => 'entityname',
			                    vals => "entitytypeid",
			                    # data => ["chaingroupid"]
				   },	
			      title => "Entity Type",
			      type => "select",
			      vals => [],
			},				    
			invoicesystem => {
			      attrs => {
			                    class => "input-md form-control",
			                    name => "invoicesystem",
			                  },
			      default => undef,
			      labels => [],
			      # populate => {
			      #               defaults => "backofficesystem",
			      #               labels => "systemname",
			      #               vals => "systemid",
			      #             },
			      populate => {
			                    defaults => {
			                    	module => "OrderInvoiceSystem",
			                    	options => {
						                prefetch => [qw//],	                    		
			                    		order_by => "systemname"
			                    	},
			                    	# fk => 'id',
			                    	where => [qw/deleted invoicesystem/],
			                    	vals => [0,1],
			                    },			                    
			                    labels => 'systemname',
			                    vals => "id",
			                    # data => ["chaingroupid"]
				   },	
			      title => "Invoice System",
			      type => "select",
			      vals => [],
			    },
			ordersystem => {
			      attrs => {
			                    class => "input-md form-control",
			                    name => "ordersystem",
			                  },
			      default => undef,
			      labels => [],
			      # populate => {
			      #               defaults => "backofficesystem",
			      #               labels => "systemname",
			      #               vals => "systemid",
			      #             },
			      populate => {
			                    defaults => {
			                    	module => "OrderInvoiceSystem",
			                    	options => {
						                prefetch => [qw//],	                    		
			                    		order_by => "systemname"
			                    	},
			                    	# fk => 'id',
			                    	where => [qw/deleted ordersystem/],
			                    	vals => [0,1],
			                    },			                    
			                    labels => 'systemname',
			                    vals => "id",
			                    # data => ["chaingroupid"]
				   },	
			      title => "Order System",
			      type => "select",
			      vals => [],
			},				    			    
			systemid => {
			      attrs => {
			                    class => "input-md form-control selectpicker",
			                    name => "systemid",
			                  },
			      defaults => [],
			      labels => [],
			      # populate => {
			      #               defaults => "backofficesystem",
			      #               labels => "systemname",
			      #               vals => "systemid",
			      #             },
			      populate => {
			                    defaults => {
			                    	module => "BackOfficeSystem",
			                    	options => {
						                prefetch => [qw//],	                    		
			                    		order_by => "systemname"
			                    	},
			                    	# fk => 'id',
			                    	where => [qw/active/],
			                    	vals => [1],
			                    },			                    
			                    labels => 'systemname',
			                    vals => "systemid",
			                    # data => ["chaingroupid"]
				   },	
			      title => "Backoffice System(s)",
			      type => "selectpicker",
			      vals => [],
			    },
			chainconceptid => {
			      attrs => {
			                    class => "input-md form-control",
			                    name => "chainconceptid",
			                    required => "required",
			                  },
			      default => undef,
			      labels => [],
			      populate => {
			                    defaults => {
			                    	module => "ChainConcept",
			                    	options => {
						                prefetch => [qw/chaingrp/],	                    		
			                    		order_by => "chainconceptname"
			                    	},
			                    	# fk => 'id',
			                    	where => [qw//],
			                    	vals => [],
			                    },			                    
			                    labels => 'chainconceptname',
			                    vals => "chainconceptid",
			                    data => ["chaingroupid"]
			                  },				                  
			      title => "Chain Concept",
			      type => "select",
			      vals => [],
			      data => [],
			 },
			chainunitid => {
			      # attrs => {
			      #            class => "form-control input-md",
			      #            name => "chainunitid",
			      #            # required => "required",
			      #            readonly => "readonly"
			      #          },
			      # title => "Unit Nbr",
			      # type => "textfield",
			      # val => undef,
			      attrs => {
			                    class => "input-md form-control bscombobox",
			                    name => "chainunitid",
			                    required => "required",
			                  },
			      default => undef,
			      labels => [],
			      populate => {
			                    # defaults => "user",
			                    defaults => {
			                    	module => "ChainUnit",
			                    	options => {
						                prefetch => [],			                    		
			                    		order_by => "chainunitname"
			                    	},

			                    	fk => 'chainunitid',
			                    	where => [qw//],
			                    	vals => [],
			                    },			                    
			                    # labels => 'chainunitname',
			                    labels => sub {
			                    	my ($el,$dv) = (@_);
			                    	return sprintf("%s", $dv->{chainunitname});
			                    },			                    
			                    vals => "chainunitid",
			                    data => [qw/chaingroupid chainconceptid/]
			                  },				                  
			      title => "Chain Unit",
			      type => "select",
			      vals => [],
			      data => [],
			},
			chainunitfilter  => {
			      attrs => { class => "", name => "chainunitfilter" },
				  labels => ['Show All Global Units'],			      
				  title => "Chain Unit Display",
			      type => "checkbox",
			      vals => [1],
			      default => 1
			},	    
			chaingroupid => {
			      attrs => {
			                    class => "input-md form-control",
			                    name => "chaingroupid",
			                    required => "required",
			                  },
			      default => undef,
			      labels => [],
			      # populate => {
			      #               defaults => "chaingroup",
			      #               labels => "chaingroupname",
			      #               vals => "chaingroupid",
			      #               data => ['chaingroupvp','chaingrouppam']
			      #             },
			      populate => {
			                    # defaults => "user",
			                    defaults => {
			                    	module => "ChainGroup",
			                    	options => {
						                prefetch => [],			                    		
			                    		order_by => "chaingroupname"
			                    	},

			                    	# fk => 'id',
			                    	where => [qw//],
			                    	vals => [],			                    	

			                    },			                    
			                    labels => 'chaingroupname',
			                    # sub {
			                    # 	my ($el,$dv) = (@_);
			                    # 	return sprintf("%s - %s %s - %s", $dv->{iacctnmbr} || 'N/A', $dv->{fname}, $dv->{lname}, $dv->{company});
			                    # },
			                    vals => "chaingroupid",
			                    data => ['chaingroupvp','chaingrouppam'],
			                  },			      
			      title => "Chain",
			      type => "select",
			      vals => [],
			      data => []
			    },
			city => {
			      attrs => {
			                 class => "form-control input-md",
			                 name => "city",
			                 required => "required",
			               },
			      title => "City",
			      type => "textfield",
			      val => undef,
			    },
			email => {
			      attrs => {
			                 class => "form-control input-md noemail",
			                 name => "email",
			                 # required => "required",
			               },
			      title => "Email",
			      type => "textfield",
			      val => undef,
			    },			    
			comments => {
			      attrs => { class => "form-control", name => "comments", required => "required" },
			      title => "Comments",
			      type => "textfield",
			      val => undef,
			    },
				commentslist => {
			      attrs => {
			                    class => "input-md form-control wordwrap",
			                    name => "commentslist",
			                    # required => "required",
			                    multiple => "multiple",
			                    size => 5,
			                    noemptyoption => 1,
			                    style => "white-space: normal; word-wrap: break-word;"
			                  },
			      default => undef,
			      labels => [],
			      # populate => {
			      #               defaults => "distributorcompany",
			      #               labels => "name",
			      #               vals => "commentslist",
			      #             },
			      populate => {
			                    # defaults => "user",
			                    defaults => {
			                    	module => "CustomerComment",
			                    	options => {
						                prefetch => [qw/usercreatedby userupdatedby/],			                    		
			                    		order_by => "updated"
			                    	},
			                    	# fk => 'id',
			                    	where => [qw/me.active me.deleted me.customerid/],
			                    	vals => [1,0,$c->stash('customerid')],
			                    },			                    
			                    labels => sub {
			                    	my ($el,$dv) = (@_);
			                    	# show $dv;
			                    	my $comment = sprintf("%s - %s - %s", $dv->{updated}, $dv->{userupdatedby}->{username}, $dv->{customercommentdescription});
			               show $comment;
			                    	return $comment;
			                    },
			                    vals => "customercommentid",
			                    data => [qw//]
			                  },			      
			      title => "Comment History",
			      type => "select",
			      vals => [],
			    },			    
			# countrycode => {
			#       attrs => {
			#                     class => "input-md form-control",
			#                     name => "countrycode",
			#                     required => "required",
			#                   },
			#       default => 'USA-United States',
			#       labels => [],
			#       populate => { defaults => "country", labels => "displayname", vals => "countrycode" },
			#       title => "Country",
			#       type => "select",
			#       vals => [],
			#     },
			countrycode => {
			      attrs => {
			                    class => "input-md form-control",
			                    name => "countrycode",
			                    required => "required",
			                  },
			      default => 'USA-United States',
			      labels => [],
			      # populate => {
			      #               defaults => "chaingroup",
			      #               labels => "chaingroupname",
			      #               vals => "chaingroupid",
			      #               data => ['chaingroupvp','chaingrouppam']
			      #             },
			      populate => {
			                    # defaults => "user",
			                    defaults => {
			                    	module => "Country",
			                    	options => {
						                prefetch => [],			                    		
			                    		order_by => "displayname"
			                    	},

			                    	# fk => 'id',
			                    	where => [qw//],
			                    	vals => [],			                    	

			                    },			                    
			                    labels => 'displayname',
			                    # sub {
			                    # 	my ($el,$dv) = (@_);
			                    # 	return sprintf("%s - %s %s - %s", $dv->{iacctnmbr} || 'N/A', $dv->{fname}, $dv->{lname}, $dv->{company});
			                    # },
			                    vals => "countrycode",
			                    data => [],
			                  },			      
			      title => "Country",
			      type => "select",
			      vals => [],
			      data => []
			    },			
			customerid => {
			      attrs => { class => "form-control input-md", name => "customerid" },
			      title => "DMA Cust ID",
			      type => "readonly",
			      val => undef,
			    },
			customernumber => {
			      attrs => {
			                 class => "form-control input-md",
			                 name => "customernumber",
			                 required => "required",
			               },
			      title => "Customer Nbr",
			      type => "textfield",
			      val => undef,
			    },
			customerunitnumber => {
			      attrs => { class => "form-control input-md", name => "customerunitnumber" },
			      title => "Store Nbr",
			      type => "textfield",
			      val => undef,
			    },
			distancetodistcenter => {
			      attrs => { class => "form-control input-md", name => "distancetodistcenter" },
			      title => "Distributor Distance",
			      type => "readonly",
			      val => undef,
			    },
			distributorcompanyid => {
			      attrs => {
			                    class => "input-md form-control",
			                    name => "distributorcompanyid",
			                    required => "required",
			                  },
			      default => undef,
			      labels => [],
			      # populate => {
			      #               defaults => "distributorcompany",
			      #               labels => "name",
			      #               vals => "distributorcompanyid",
			      #             },
			      populate => {
			                    # defaults => "user",
			                    defaults => {
			                    	module => "DistributorCompany",
			                    	options => {
						                prefetch => [],			                    		
			                    		order_by => "name"
			                    	},
			                    	# fk => 'id',
			                    	where => [qw/me.active/],
			                    	vals => [1],
			                    },			                    
			                    labels => 'name',
			                    # sub {
			                    # 	my ($el,$dv) = (@_);
			                    # 	return sprintf("%s - %s %s - %s", $dv->{iacctnmbr} || 'N/A', $dv->{fname}, $dv->{lname}, $dv->{company});
			                    # },
			                    vals => "distributorcompanyid",
			                    data => [qw//]
			                  },			      
			      title => "Distributor",
			      type => "select",
			      vals => [],
			    },
			distributorwarehouseid => {
			      attrs => {
			                    class => "input-md form-control",
			                    name => "distributorwarehouseid",
			                    required => "required",
			                  },
			      default => undef,
			      labels => [],
			      # populate => {
			      #               defaults => "distributorwarehouse",
			      #               labels => "distributorwarehousename",
			      #               vals => "distributorwarehouseid",
			      #               data => ["distributorcompanyid"]
			      #             },
			      populate => {
			                    # defaults => "user",
			                    defaults => {
			                    	module => "DistributorWarehouse",
			                    	options => {
						                prefetch => [qw/distributor/],			                    		
			                    		order_by => "distributorwarehousename"
			                    	},
			                    	# fk => 'id',
			                    	where => [qw/me.active me.clmactive/],
			                    	vals => [1,1],
			                    },			                    
			                    labels => 'distributorwarehousename',
			                    # sub {
			                    # 	my ($el,$dv) = (@_);
			                    # 	return sprintf("%s - %s %s - %s", $dv->{iacctnmbr} || 'N/A', $dv->{fname}, $dv->{lname}, $dv->{company});
			                    # },
			                    vals => "distributorwarehouseid",
			                    data => [qw/distributorcompanyid/]
			                  },			                  
			      title => "Warehouse",
			      type => "select",
			      vals => [],
			      data => []
			    },
			chaingrouppam => {
			      attrs => {
			                    class => "input-md form-control",
			                    disabled => "disabled",
			                    name => "chaingrouppam",
			                  },
			      default => undef,
			      labels => [],
			      populate => {
			                    # defaults => "dmaprogrammanager",
					      		# defaults => "dmavp", 
					            defaults => {
					                    	module => "DmaprogramManager",
					                    	options => {
					                    		order_by => "dmaprogrammanager",
					                    		select => [qw/dmaprogrammanagerid dmaprogrammanager/],
					                    		as => [qw/chaingrouppam dmaprogrammanager/]
					                    	},
					                    	# fk => 'id',
					                    	where => [qw//],
					                    	vals => [],
					             },
			                    labels => "dmaprogrammanager",
			                    vals => "chaingrouppam",
								data => ["chaingrouppam"]

			                  },
			      title => "DMA SCS",
			      type => "select",
			      vals => [],
			      data => []
			    },
			'chaingroupvp' => {
			      attrs => {
			                    class => "input-md form-control",
			                    disabled => "disabled",
			                    name => "chaingroupvp",
			                  },
			      default => undef,
			      labels => [],
			      populate => { 
			      		defaults => "dmavp", 
			                    defaults => {
			                    	module => "Dmavp",
			                    	options => {
			                    		order_by => "dmavp",
			                    		select => [qw/dmavpid dmavp/],
			                    		as => [qw/chaingroupvp dmavp/]
			                    	},
			                    	# fk => 'id',
			                    	where => [qw//],
			                    	vals => [],
			                    },				      		
						labels => "dmavp", 
						vals => "chaingroupvp",
						data => ["chaingroupvp"]
			       },
			      title => "DMA VP",
			      type => "select",
			      vals => [],
			      data => []
			    },
			groupname => {
			      attrs => {
			                 class => "form-control input-md",
			                 name => "groupname",
			#                 required => "required",
			                 autocomplete => "off",
			               },
			      title => "Unit (Group Name)",
			      type => "textfield",
			      val => undef,
			    },
			lastchangeddate => {
			      attrs => { class => "form-control input-md", name => "lastchangeddate" },
			      title => "Updated",
			      type => "readonly",
			      val => undef,
			      format => sub {
			      	my $v = shift;
			      	my $dt = DateTime::Format::DateParse->parse_datetime( $v, 'America/Chicago' );
			      	$v = $dt->strftime( "%D %r");
			      	return $v;
			      }
			    },
			# lastinvdate => {
			#       attrs => { class => "form-control input-md", name => "lastinvdate" },
			#       title => "Invoiced",
			#       type => "readonly",
			#       val => undef,
			#     },
			lastupdatedbyid => {
			      attrs => { class => "form-control input-md", name => "lastupdatedbyid" },
			      default => undef,
			      labels => [],
			      populate => { defaults => "user", labels => "fullname", vals => "user_id" },
			      title => "Updated By",
			      type => "readonly",
			      vals => [],
			    },
			setupbyid => {
			      attrs => { class => "form-control input-md", name => "setupbyid" },
			      default => undef,
			      labels => [],
			      populate => { defaults => "user", labels => "fullname", vals => "user_id" },
			      title => "Created By",
			      type => "readonly",
			      vals => [],
			    },			    
			latitude => {
			      attrs => { class => "form-control input-md", name => "latitude" },
			      title => "Latitude",
			      type => "readonly",
			      val => undef,
			    },
			longitude => {
			      attrs => { class => "form-control input-md", name => "longitude" },
			      title => "Longitude",
			      type => "readonly",
			      val => undef,
			    },
			ownershiptype => {
			      attrs => { class => "form-control input-md", name => "ownershiptype", required => "required" },
			      default => undef,
			      title => "Operator Type",
			      type => "select",
			      vals => ["CORPORATE","FRANCHISEE","UNDEFINED","GPO"],
						labels => ["CORPORATE","FRANCHISEE","UNDEFINED","GPO"],

			    },
			orderguideflagid => {
			      attrs => { class => "form-control input-md", name => "orderguideflagid" },
			      default => 3,
			      labels => ['','Y','P'],
			      populate => { defaults => "orderflag", labels => "dscr", vals => "id" },
			      title => "Order Guide",
			      type => "select",
			      vals => [3,1,2],
			    },
			brandedprocurement => {
			      attrs => { class => "form-control input-md", name => "brandedprocurement", },
			      default => undef,
			      labels => [qw/Y N/],
			      title => "Branded Procurement", 
			      type => "select",
			      vals => [1,0],
			    },	
			brandedprocurementdeleted => {
			      attrs => { class => "form-control input-md", name => "brandedprocurementdeleted", disabled => "disabled"},
			      default => undef,
			      labels => [qw/D N/],
			      title => "Branded Procurement Deleted", 
			      type => "select",
			      vals => [1,0],
			    },				    		    
			ownership => {
			      attrs => { class => "form-control input-md", name => "ownership", required => "required" },
			      title => "Operator",
			      type => "textfield",
			      val => undef,
			    },
			ownership2 => {
			      attrs => { class => "form-control input-md", name => "ownership2" },
			      title => "Sub-Operator",
			      type => "textfield",
			      val => undef,
			    },
			setupdate => {
			      attrs => { class => "form-control input-md", name => "setupdate" },
			      title => "Created",
			      type => "readonly",
			      val => undef,
			      format => sub {
			      	my $v = shift;
			      	my $dt = DateTime::Format::DateParse->parse_datetime( $v, 'America/Chicago' );
			      	$v = $dt->strftime( "%D %r");
			      	return $v;
			      }
			    },
			state => {
			      attrs => {
			                 class => "form-control input-md",
			                 name => "state",
			                 required => "required",
			               },
			      title => "State",
			      type => "textfield",
			      val => undef,
			    },
			statusid => {
			      attrs => { class => "form-control input-md", name => "statusid", required=>'required' },
			      default => 0,
			      labels => [],
			      populate => { defaults => "astatus", labels => "dscr", vals => "id" },
			      title => "Status",
			      type => "select",
			      vals => [],
			    },
			opfstatus => {
			      attrs => { class => "form-control input-md", name => "opfstatus", required=>'required' },
			      default => 0,
			      labels => ['Active','Inactive'],
			      # populate => { defaults => "astatus", labels => "dscr", vals => "id" },
			      title => "OPF Status",
			      type => "select",
			      vals => [1,0],
			    },
			emailgov => {
			      attrs => { class => "form-control input-md", name => "emailgov" },
			      default => 0,
			      labels => ["N","Y"],
			      title => "Email GOV Sent",
			      type => "select",
			      vals => [0,1],
			    },			    
			unitname => {
			      attrs => {
			                 class => "form-control input-md",
			                 name => "unitname",
			                 required => "required",
			               },
			      title => "Account Name",
			      type => "textfield",
			      val => undef,
			    },
			zip => {
			      attrs => { class => "form-control input-md", name => "zip", required => "required" },
			      title => "Zip",
			      type => "textfield",
			      val => undef,
			    },
			spacer => {
				attrs => { name => 'spacer'}, type => 'spacer'
			},
			chainunitcreate => {
				type => 'button',
				attrs => [{
					class => 'btn btn-primary',
					name => 'chainunitcreate',
					type => 'button'
				}],
				title => 'Create Chain Unit',
				labels => ["Create"],

			},
			accountingcode => {
			      attrs => { class => "form-control input-md", name => "accountingcode" },
			      title => "Accounting Code",
			      # type => "textfield",
			      type => 'hidden',
			      val => undef,
			},
			filecode => {
			      attrs => { class => "form-control input-md", name => "filecode" },
			      title => "File Code",
			      # type => "textfield",
			      type => 'hidden',
			      val => undef,
			},				
			'marketid' => {
			      attrs => {
			                    class => "input-md form-control",
			                    name => "marketid",
			                  },
			      default => undef,
			      labels => [],
			      populate => { 
			      		defaults => "marketid", 
			                    defaults => {
			                    	module => "Market",
			                    	options => {
			                    		order_by => [qw/chaingrp.chaingroupname customwarehousename customwarehousenumber/],
			                    		prefetch => [qw/chaingrp/]
			                    	},
			                    	# fk => 'id',
			                    	prefetch => [qw/chaingrp/],
			                    	where => [qw/me.active/],
			                    	vals => [1],

			                    },				      		
                    labels => sub {
                    	my ($el,$dv) = (@_);
                    	return sprintf("%s - %s - %s", $dv->{'chaingrp'}->{'chaingroupname'}, $dv->{customwarehousename}, $dv->{customwarehousenumber});
                    },	
								vals => "marketid",
								data => ['customwarehousename','customwarehousenumber']
			       },
			      title => "Market",
			      # type => "select",
			      type => 'hidden',
			      vals => [],
			      data => []
			    },	
			'routingid' => {
			      attrs => {
			                    class => "input-md form-control",
			                    name => "routingid",
			                  },
			      default => undef,
			      labels => [],
			      populate => { 
			      		defaults => "routingid", 
			                    defaults => {
			                    	module => "Routing",
			                    	options => {
			                    		order_by => [qw/chaingrp.chaingroupname customwarehousename customwarehousenumber/],
			                    		prefetch => [qw/chaingrp/]
			                    	},
			                    	# fk => 'id',
			                    	prefetch => [qw/chaingrp/],
			                    	where => [qw/me.active/],
			                    	vals => [1],

			                    },				      		
                    labels => sub {
                    	my ($el,$dv) = (@_);
                    	return sprintf("%s - %s - %s", $dv->{'chaingrp'}->{'chaingroupname'}, $dv->{customwarehousename}, $dv->{customwarehousenumber});
                    },	
								vals => "routingid",
								data => ['customwarehousename','customwarehousenumber']
			       },
			      title => "Routing",
			      type => "select",
			      vals => [],
			      data => []
			    },


    },


	};

	my $aform = dclone $fform;
	$aform->{attrs}->{name} = 'customeraddform';
	$aform->{attrs}->{formon} = '';
	$aform->{attrs}->{formoff} = 'form-never';
	# $aform->{eorder} = [qw/statusid customernumber amphireid distributorcompanyid distributorwarehouseid chaingroupid chainconceptid chaingroupvp chaingrouppam  brandedprocurement comments empty spacer ownership ownership2 chainunitid customerunitnumber unitname address city state zip countrycode orderguideflagid systemid opstatus opstatusdate/];
	# $aform->{eorder} = [qw/statusid customernumber amphireid chaingroupid chainconceptid distributorcompanyid distributorwarehouseid chaingroupvp chaingrouppam brandedprocurement  email comments entitytypeid ownershiptype ownership ownership2 empty empty empty empty chainunitid opendate customerunitnumber unitname address city state zip countrycode orderguideflagid systemid opstatus opstatusdate storestatus storestatusdate invoicesystem ordersystem marketid routingid accountingcode filecode/];

	$aform->{eorder} = [qw/statusid customernumber chaingroupid chainconceptid distributorcompanyid distributorwarehouseid ownershiptype ownership ownership2 entitytypeid customerunitnumber chainunitid unitname address city state zip countrycode opendate opstatus opstatusdate storestatus storestatusdate orderguideflagid comments amphireid systemid chaingroupvp chaingrouppam brandedprocurement email routingid invoicesystem ordersystem marketid accountingcode filecode/];

	$aform->{sorder} = [];
	my $dt =  DateTime->now(time_zone  => 'America/Chicago');
	$aform->{elems}->{opstatusdate}->{val} = $dt->year() . sprintf("%02d", $dt->month()) . sprintf("%02d", $dt->day());
	$aform->{elems}->{opstatusdate}->{default} = $dt->year() . sprintf("%02d", $dt->month()) . sprintf("%02d", $dt->day());

	delete($aform->{elems}->{comments}->{attrs}->{required});
	# $aform->{elems}->{comments}->{val} = $dt->year() . sprintf("%02d", $dt->month()) . sprintf("%02d", $dt->day()) . "\t" .  $c->session->{username} . "\t" . 'ADD RECORD';
	$aform->{elems}->{comments}->{val} = 'ADD RECORD';
	
	# show $aform->{elems}->{comments}, $p->{comments};

	my $beform = dclone $aform;
	$beform->{attrs}->{name} = 'customerbulkeditform';

	$beform->{elems}->{systemidnew} = {
			      attrs => {
			                    class => "input-md form-control selectpicker",
			                    name => "systemidnew",
			                  },
			      defaults => [],
			      labels => [],
			      # populate => {
			      #               defaults => "backofficesystem",
			      #               labels => "systemname",
			      #               vals => "systemid",
			      #             },
			      populate => {
			                    defaults => {
			                    	module => "BackOfficeSystem",
			                    	options => {
						                prefetch => [qw//],	                    		
			                    		order_by => "systemname"
			                    	},
			                    	# fk => 'id',
			                    	where => [qw/active/],
			                    	vals => [1],
			                    },			                    
			                    labels => 'systemname',
			                    vals => "systemid",
			                    # data => ["chaingroupid"]
				   },	
			      title => "Backoffice System(s) New",
			      type => "selectpicker",
			      vals => [],
	};
	$beform->{eorder} = [qw/statusid customernumber chaingroupid chainconceptid distributorcompanyid distributorwarehouseid ownershiptype ownership ownership2 entitytypeid customerunitnumber chainunitid unitname address city state zip countrycode opendate opstatus  storestatus storestatusdate orderguideflagid comments systemid chaingroupvp chaingrouppam brandedprocurement routingid invoicesystem ordersystem marketid accountingcode filecode/]; # opstatusdate



	my @be = (qw/customerunitnumber unitname address city state zip countrycode customernumber amphireid chainunitid chaingroupvp chaingrouppam comments /);
	foreach my $be (@be){
		$beform->{elems}->{$be}->{attrs}->{disabled} = 'disabled'; 
	}
	$beform->{elems}->{marketid}->{attrs}->{readonly} = 'readonly';
	$beform->{elems}->{accountingcode}->{attrs}->{readonly} = 'readonly';
	$beform->{elems}->{filecode}->{attrs}->{readonly} = 'readonly';
	foreach my $e (keys %{$beform->{elems}}){
		# show $beform->{elems}->{$e};
		if($beform->{elems}->{$e}->{attrs} && ref($beform->{elems}->{$e}->{attrs}) eq 'HASH'){
			delete($beform->{elems}->{$e}->{attrs}->{required});
		}
	}
# add add remove to bulk edit
#	show $beform->{elems}->{ordersystem}, $beform->{elems}->{invoicesystem};



	# foreach my $fek (keys %{ $aform->{elems} }){
	# 	if(ref($aform->{elems}->{$fek}->{attrs}) eq 'HASH'){
	# 		delete($aform->{elems}->{$fek}->{attrs}->{disabled});
	# 	}
	# }

	my $sform = dclone $fform;
	my $ssform = {
				chaingroupid => {
			      attrs => {
			                    class => "input-md form-control",
			                    name => "chaingroupid",
			                    required => "required",
			                  },
			      default => undef,
			      labels => [],
			      # populate => {
			      #               defaults => "chaingroup",
			      #               labels => "chaingroupname",
			      #               vals => "chaingroupid",
			      #               data => ['chaingroupvp','chaingrouppam']
			      #             },
			      populate => {
			                    # defaults => "user",
			                    defaults => {
			                    	module => "ChainGroup",
			                    	options => {
						                prefetch => [],			                    		
			                    		order_by => "chaingroupname"
			                    	},

			                    	# fk => 'id',
			                    	where => [qw/active/],
			                    	vals => [1],			                    	

			                    },			                    
			                    labels => 'chaingroupname',
			                    # sub {
			                    # 	my ($el,$dv) = (@_);
			                    # 	return sprintf("%s - %s %s - %s", $dv->{iacctnmbr} || 'N/A', $dv->{fname}, $dv->{lname}, $dv->{company});
			                    # },
			                    vals => "chaingroupid",
			                    data => ['chaingroupvp','chaingrouppam','invoicesystem','ordersystem'],
			                  },			      
			      title => "Chain",
			      type => "select",
			      vals => [],
			      data => []
			    },
				chainconceptid => {
				      attrs => {
				                    class => "input-md form-control",
				                    name => "chainconceptid",
				                    required => "required",
				                  },
				      default => undef,
				      labels => [],
				      populate => {
				                    defaults => {
				                    	module => "ChainConcept",
				                    	options => {
							                prefetch => [qw/chaingrp/],	                    		
				                    		order_by => "chainconceptname"
				                    	},
				                    	# fk => 'id',
				                    	where => [qw/me.active chaingrp.active/],
				                    	vals => [1,1],
				                    },			                    
				                    labels => 'chainconceptname',
				                    vals => "chainconceptid",
				                    data => ["chaingroupid"]
				                  },				                  
				      title => "Chain Concept",
				      type => "select",
				      vals => [],
				      data => [],
				},
				distributorcompanyid => {
			      attrs => {
			                    class => "input-md form-control",
			                    name => "distributorcompanyid",
			                    required => "required",
			                  },
			      default => undef,
			      labels => [],
			      # populate => {
			      #               defaults => "distributorcompany",
			      #               labels => "name",
			      #               vals => "distributorcompanyid",
			      #             },
			      populate => {
			                    # defaults => "user",
			                    defaults => {
			                    	module => "DistributorCompany",
			                    	options => {
						                prefetch => [],			                    		
			                    		order_by => "name"
			                    	},
			                    	# fk => 'id',
			                    	where => [qw/active/],
			                    	vals => [1],
			                    },			                    
			                    labels => 'name',
			                    # sub {
			                    # 	my ($el,$dv) = (@_);
			                    # 	return sprintf("%s - %s %s - %s", $dv->{iacctnmbr} || 'N/A', $dv->{fname}, $dv->{lname}, $dv->{company});
			                    # },
			                    vals => "distributorcompanyid",
			                    data => [qw//]
			                  },			      
			      title => "Distributor",
			      type => "select",
			      vals => [],
			    },
			distributorwarehouseid => {
			      attrs => {
			                    class => "input-md form-control",
			                    name => "distributorwarehouseid",
			                    required => "required",
			                  },
			      default => undef,
			      labels => [],
			      # populate => {
			      #               defaults => "distributorwarehouse",
			      #               labels => "distributorwarehousename",
			      #               vals => "distributorwarehouseid",
			      #               data => ["distributorcompanyid"]
			      #             },
			      populate => {
			                    # defaults => "user",
			                    defaults => {
			                    	module => "DistributorWarehouse",
			                    	options => {
						                prefetch => [qw/distributor/],			                    		
			                    		order_by => "distributorwarehousename"
			                    	},
			                    	# fk => 'id',
			                    	where => [qw/me.active distributor.active/],
			                    	vals => [1,1],
			                    },			                    
			                    labels => 'distributorwarehousename',
			                    # sub {
			                    # 	my ($el,$dv) = (@_);
			                    # 	return sprintf("%s - %s %s - %s", $dv->{iacctnmbr} || 'N/A', $dv->{fname}, $dv->{lname}, $dv->{company});
			                    # },
			                    vals => "distributorwarehouseid",
			                    data => [qw/distributorcompanyid/]
			                  },			                  
			      title => "Warehouse",
			      type => "select",
			      vals => [],
			      data => []
			    },
			chaingrouppam => {
			      attrs => {
			                    class => "input-md form-control",
			                    disabled => "disabled",
			                    name => "chaingrouppam",
			                  },
			      default => undef,
			      labels => [],
			      populate => {
			                    # defaults => "dmaprogrammanager",
					      		# defaults => "dmavp", 
					            defaults => {
					                    	module => "DmaprogramManager",
					                    	options => {
					                    		order_by => "dmaprogrammanager",
					                    		select => [qw/dmaprogrammanagerid dmaprogrammanager/],
					                    		as => [qw/chaingrouppam dmaprogrammanager/]
					                    	},
					                    	# fk => 'id',
					                    	where => [qw/active/],
					                    	vals => [1],
					             },
			                    labels => "dmaprogrammanager",
			                    vals => "chaingrouppam",
								data => ["chaingrouppam"]

			                  },
			      title => "DMA SCS",
			      type => "select",
			      vals => [],
			      data => []
			    },
			'chaingroupvp' => {
			      attrs => {
			                    class => "input-md form-control",
			                    disabled => "disabled",
			                    name => "chaingroupvp",
			                  },
			      default => undef,
			      labels => [],
			      populate => { 
			      		defaults => "dmavp", 
			                    defaults => {
			                    	module => "Dmavp",
			                    	options => {
			                    		order_by => "dmavp",
			                    		select => [qw/dmavpid dmavp/],
			                    		as => [qw/chaingroupvp dmavp/]
			                    	},
			                    	# fk => 'id',
			                    	where => [qw/active/],
			                    	vals => [1],
			                    },				      		
						labels => "dmavp", 
						vals => "chaingroupvp",
						data => ["chaingroupvp"]
			       },
			      title => "DMA VP",
			      type => "select",
			      vals => [],
			      data => []
			    },		
			opstatusdate => {
			      attrs => {
			                 class => "form-control input-md",
			                 name => "opstatusdate",
			                 "data-format" => "YYYYMMDD",
			                 # readonly => "readonly"
			                 # required => "required",
			               },
			      title => "Operating Status Date",
			      type => "textfield",
			      val => undef,
			},	
	    		
			};

	foreach my $k (keys %$ssform){
		$sform->{elems}->{$k} = $ssform->{$k};
		$aform->{elems}->{$k} = $ssform->{$k} unless $k eq 'chaingroupvp' || $k eq 'chaingrouppam';
	}

	$c->{_forms}->{default} = $c->form($fform);
	#$sform->{elems}->{statusid}->{type} = 'checkbox';
	$sform->{eorder} = [qw/statusid customernumber customerid chaingroupid chainconceptid distributorcompanyid distributorwarehouseid ownershiptype ownership ownership2 entitytypeid customerunitnumber chainunitid unitname address city state zip countrycode opendate opstatus  storestatus storestatusdate orderguideflagid comments systemid chaingroupvp chaingrouppam brandedprocurement routingid invoicesystem ordersystem marketid accountingcode filecode/]; #opstatusdate
	$sform->{attrs}->{name} = 'customersearchform';
	$sform->{attrs}->{formon} = '';
	$sform->{attrs}->{formoff} = 'form-never';	
	$sform->{elems}->{asearch} = {
			      attrs => { class => "form-control", name => "asearch" },
			      title => "Search",
			      type => "hidden",
			      val => 1,
			    };
	$sform->{attrs}->{formon} = '';
	$sform->{attrs}->{formoff} = 'form-never';
	$sform->{sorder} = [];
	foreach my $fek (keys %{ $sform->{elems} }){
		if(ref($aform->{elems}->{$fek}->{attrs}) eq 'HASH'){
			delete($sform->{elems}->{$fek}->{attrs}->{disabled});
		}
	}
	$sform->{elems}->{customerid}->{type} = 'textfield';
	$sform->{elems}->{brandedprocurement}->{default} = undef;
	$sform->{elems}->{chaingroupvp}->{attrs}->{name} = 'chaingrp.chaingroupvp';
	$sform->{elems}->{chaingrouppam}->{attrs}->{name} = 'chaingrp.chaingrouppam';

# show $sform;

	$c->{_forms}->{template} = $c->form($aform);
	$c->{_forms}->{default} = $c->form($fform);
	$c->{_forms}->{search} = $c->form($sform);
	$c->{_forms}->{add} = $c->form($aform);
	$c->{_forms}->{bulkedit} = $c->form($beform);


	$c->{_buttongroup}->{template} = $c->form([
			{
				type => 'buttongroup',
				attrs => [{
					class => 'btn btn-success',
					name => 'templatesave',
					type => 'button'
				},				
				],
				labels => ["Save Criteria"],
			},
	]);


	$c->{_buttongroup}->{bulkedit} = $c->form([
			{
				type => 'buttongroup',
				attrs => [{
					class => 'btn btn-success',
					name => 'bulkedit',
					type => 'button'
				},
				{
					class => 'btn btn-danger form-reset',
					name => 'form-reset',
					type => 'reset'
				},				
				],
				labels => ["Bulk Edit Records","Reset"],
			},
	]);
	$c->{_buttongroup}->{add} = $c->form([
			{
				type => 'buttongroup',
				attrs => [{
					class => 'btn btn-success',
					name => 'add',
					type => 'button'
				},
				{
					class => 'btn btn-success',
					name => 'addedit',
					type => 'button'
				},				
				{
					class => 'btn btn-danger form-reset',
					name => 'form-reset',
					type => 'reset'
				},				
				],
				labels => ["Add Record","Add Record & Edit","Reset"],
			},
	]);

	$c->{_buttongroup}->{search} = $c->form([
			{
				type => 'buttongroup',
				attrs => [{
					class => 'btn btn-primary',
					name => 'search',
					type => 'button'
				},
				{
					class => 'btn btn-danger form-reset',
					name => 'form-reset',
					type => 'reset'
				},					
				],
				labels => ["Search","Reset"],
			},
	]);		


	$c->{_buttongroup}->{default} = $c->form([
			# {
			# 	type => 'buttongroup',
			# 	class => 'form-off',
			# 	attrs => [{
			# 		class => 'btn btn-warning form-off',
			# 		name => 'edit',
			# 		type => 'button'
			# 	},	
						
			# 	],
			# 	labels => ["Edit","Delete"],
			# 	# vals => [qw/save reset/],
			# },		
		    {
				type => 'buttongroup',
				class => 'form-always',
				attrs => [{
					class => 'btn btn-success',
					name => 'save',
					type => 'button'
				},
				# {
				# 	class => 'btn btn-warning form-reset',
				# 	name => 'form-reset',
				# 	type => 'reset'
				# },
				# {
				# 	class => 'btn btn-primary form-cancel',
				# 	name => 'cancel',
				# 	type => 'button'
				# },	
				{
					class => 'btn btn-default ',
					name => 'close',
					type => 'button',
					'data-dismiss' => 'modal'
				},
				{
					class => 'btn btn-danger',
					name => 'delete',
					type => 'button'
				},
				{
					class => 'btn btn-primary',
					name => 'copy',
					type => 'button'
				},
				],
				labels => ["Save", "Close", "Delete","Copy"],
				# vals => [qw/save reset/],
			},

	]);





}



1;