use strict;
return sub
{
	my $dbh = shift;
	
	my ($sth, $sth1, $maxId, $id, $row, %num_pgate_owner_type);
	
	
	$sth = $dbh->prepare("select max(id) from jin_pgate");
	$sth->execute;
	$maxId = $sth->fetchrow + 0;
	
	my $getPGate = sub
	{
		my ($owner_id, $type, $settings) = @_;

		my $sthi = $dbh->prepare("select id from jin_pgate where owner_id=? and type=? and settings=?");
		$sthi->execute($owner_id, $type, $settings);

		my $id;
		
		if ($sthi->rows > 0)
		{
			$id = $sthi->fetch->[0] + 0;
		}
		else
		{
			$id = ++$maxId;
			my $key_owner_type = "$owner_id:$type";
			my $title = ucfirst $type;
			$title .= " $num_pgate_owner_type{$key_owner_type}"
				if $num_pgate_owner_type{$key_owner_type};
			$dbh->do("insert into jin_pgate (id,owner_id,type,title,settings) values (?,?,?,?,?)", undef, $id, $owner_id, $type, $title, $settings);
			$num_pgate_owner_type{$key_owner_type}++;
		}
		
		$id;
	};
	
	my @pgatesCfg = (
		[qw/manual pg_manual_ message/],
		[qw/paypal pg_paypal_ email fwdipn_numtry fwdipn_url/],
		[qw/2checkout pg_2c_ sid secret/],
		[qw/clickbank pg_cb_ nickname/],
		[qw/moneybookers pg_mb_ email/],
		[qw/authnet pg_an_ x_login txn_key/],
		[qw/linkpoint pg_lp_ storename certfile/],
	);
	
	$sth = $dbh->prepare("select * from jin_product where pgates='' order by id");
	$sth->execute;

	while (my $row = $sth->fetchrow_hashref)
	{
		my @pgateIds;
	
		for my $pgc (@pgatesCfg)
		{
			my ($type, $pfx, @fields) = @$pgc;
			if ($row->{"${pfx}use"})
			{
				push @pgateIds, &$getPGate(
					$row->{owner_id}, $type,
					join(':', map { $_.':'.$row->{"${pfx}$_"} } @fields),
				);
			}
		}
		
		$dbh->do("update jin_product set pgates=? where id=?", undef, join(',', @pgateIds), $row->{id});
	}
	

	$sth = $dbh->prepare("select pk.id as pk_id,pr.id as pr_id,pr.pgates from jin_package as pk
inner join jin_package_product as rel on rel.pack_id=pk.id
inner join jin_product as pr on pr.id=rel.prod_id
where pk.type=3 and pk.pgates=''");
	$sth->execute;

	while (my $row = $sth->fetchrow_hashref)
	{
		my $sthi = $dbh->prepare("update jin_package set pgates=? where id=?");
		$sthi->execute($row->{pgates}, $row->{pk_id});

		$sthi = $dbh->prepare("delete from jin_product where id=?");
		$sthi->execute($row->{pr_id});

		$sthi = $dbh->prepare("delete from jin_package_product where prod_id=? and pack_id=?");
		$sthi->execute($row->{pr_id}, $row->{pk_id});
	}
	


	my %pgate_types;
	$sth = $dbh->prepare("select id,type from jin_pgate");
	$sth->execute;
	while (my $row = $sth->fetchrow_hashref)
	{
		$pgate_types{$row->{id}} = $row->{type};
	}

	
	$sth = $dbh->prepare("select max(id) from jin_pgtxn");
	$sth->execute;
	$maxId = $sth->fetchrow + 0;
	
	$sth = $dbh->prepare("select s.id,s.owner_id,s.order_id,s.paid,s.txn_pgate_type,s.txn_code,s.pgate_processed,p.pgates
from jin_sale as s
left join jin_pgtxn as t on t.order_id=s.order_id and t.sale_id=s.id
left join jin_product as p on p.id=s.prod_id
where t.id is null
order by s.id");
	$sth->execute;

	while (my $row = $sth->fetchrow_hashref)
	{
		my ($pgate_id) = grep { $pgate_types{$_} eq $row->{txn_pgate_type} } split ',', $row->{pgates};
		$pgate_id ||= 0;
	
		$maxId++;
		$dbh->do("insert into jin_pgtxn set id=?,owner_id=?,txn_code=?,processed=?,successful=?,pgate_id=?,pgate_type=?,order_id=?,sale_id=?", undef, $maxId, $row->{owner_id}, $row->{txn_code}, $row->{pgate_processed}, $row->{paid}, $pgate_id, $row->{txn_pgate_type}, $row->{order_id}, $row->{id});
	}
	

	{
		$sth = $dbh->prepare("select distinct sale_id,id from jin_pgtxn");
		$sth->execute;
		my $sthi = $dbh->prepare("update jin_paylog set pgtxn_id=? where sale_id=?");
		while (my $row = $sth->fetchrow_hashref)
		{
			$sthi->execute($row->{id}, $row->{sale_id});
		}
	}
	

	$sth = $dbh->prepare("select id,settings from jin_pgate where type='manual'");
	$sth->execute;

	while (my $row = $sth->fetchrow_hashref)
	{
		if ($row->{settings} =~ s/Sale\.ID/INVOICE/g)
		{
			my $sthi = $dbh->prepare("update jin_pgate set settings=? where id=?");
			$sthi->execute($row->{settings}, $row->{id});
		}
	}

	
	
	$sth = $dbh->prepare("select sale_id from jin_user_affprog where ctime is NULL and sale_id != 0");
	$sth->execute;

	while (my $row = $sth->fetchrow_hashref)
	{
		my $sthi = $dbh->prepare("select ctime from jin_sale where id=?");
		$sthi->execute($row->{sale_id});
		
		if ($sthi->rows > 0)
		{
			$dbh->do("update jin_user_affprog set ctime=? where ctime is NULL and sale_id=?", undef, $sthi->fetch->[0], $row->{sale_id});
		}
	}
	
	
	$dbh->do("update jin_user_affprog set ctime=now() where ctime is NULL");
	
	
	$sth = $dbh->prepare("delete from zen_id_dbtable");
	$sth->execute;
	
};

