[BUG/Module] Duplicate TVs

for($i=0;$i<$affected;$i++) {
	$newid +=$i;
	duplicateTVs($myChildren[$i],$newid);
	duplicateKeywords($myChildren[$i],$newid);
	duplicateAccess($myChildren[$i],$newid);
}

for($i=0;$i<$affected;$i++) {
	duplicateTVs($myChildren[$i],$newid);
	duplicateKeywords($myChildren[$i],$newid);
	duplicateAccess($myChildren[$i],$newid);
	$newid++;
}

<?php

echo '<pre>';
$error = false;

// You will need a copy of modx_site_tmplvar_contentvalues for this to work

// empty table
$sql = 'TRUNCATE TABLE modx_site_tmplvar_contentvalues_copy';
$modx->db->query($sql);

// load table
$sql = 'INSERT INTO modx_site_tmplvar_contentvalues_copy SELECT * FROM modx_site_tmplvar_contentvalues';
$modx->db->query($sql);

// clear values for non-existing documents
$sql = 'SELECT cv.id FROM modx_site_tmplvar_contentvalues_copy cv 
		LEFT JOIN intl_site_content sc ON cv.contentid = sc.id
		WHERE sc.id IS NULL';
$res = $modx->db->query($sql);

$ids = array();
while($row = mysql_fetch_assoc($res))
{
	$ids[] = $row['id'];
}

$sql = sprintf("DELETE FROM modx_site_tmplvar_contentvalues_copy WHERE id IN ('%s')", implode("','", $ids));
$modx->db->query($sql);
$aff = mysql_affected_rows();
if($aff)
{
	echo sprintf("Deleted %u values for %u non-existing documents!\n", mysql_affected_rows(), count($ids));
	$error = true;
}

// clear TV values that no longer belong to the document
$sql = 'SELECT contentid AS id FROM modx_site_tmplvar_contentvalues_copy';
$res = $modx->db->query($sql);

$ids = array();
while($row = mysql_fetch_assoc($res))
{
	$ids[] = $row['id'];
}

$sql = sprintf("SELECT id, template FROM intl_site_content WHERE id IN ('%s') ORDER BY template, id", implode("','", $ids));
$res = $modx->db->query($sql);

$tmpl = array(); $tmplid = 0;
while($row = mysql_fetch_assoc($res))
{
	if($tmplid != $row['template'])
	{
		$tmplid = $row['template'];
		$tmpl[] = $tmplid;
	}
	$tmpl_ids[$row['template']][] = $row['id'];
}

$sql = sprintf("SELECT * FROM intl_site_tmplvar_templates WHERE templateid IN ('%s')", implode("','", $tmpl));
$res = $modx->db->query($sql);

while($row = mysql_fetch_assoc($res))
{
	$vars[$row['templateid']][] = $row['tmplvarid'];
}

foreach($tmpl_ids as $tmpl => $ids)
{
	foreach($ids as $id)
	{
		$sql = sprintf("DELETE FROM modx_site_tmplvar_contentvalues_copy WHERE contentid = %u AND tmplvarid NOT IN ('%s')", 
			$id, implode("','", $vars[$tmpl]));
		$modx->db->query($sql);
		$aff = mysql_affected_rows();
		if($aff)
		{
			echo sprintf("Deleted %u obsolete values for document ID %u!\n", mysql_affected_rows(), $id);
			$error = true;
		}
	}
}

// clear duplicate TV values
$sql = 'SELECT contentid AS id FROM modx_site_tmplvar_contentvalues_copy GROUP BY contentid, tmplvarid 
		HAVING count(id) > 1 ORDER BY contentid ASC';

$res = $modx->db->query($sql);

$ids = array();
while($row = mysql_fetch_assoc($res))
{
	$ids[] = $row['id'];
}

$sql = sprintf("SELECT * FROM modx_site_tmplvar_contentvalues_copy WHERE contentid IN ('%s') ORDER BY contentid, tmplvarid, value", implode("','", $ids));
$res = $modx->db->query($sql);

$veq = false; $ceq = false; $id = 0; $tmplvarid = 0; $contentid = 0; $value = ''; $del = array(); $check = array();
while($row = mysql_fetch_assoc($res))
{
	if($tmplvarid == $row['tmplvarid'])
	{
		$veq = true;
	}
	else
	{
		$tmplvarid = $row['tmplvarid'];
	}

	if($contentid == $row['contentid'] && $veq)
	{
		$ceq = true;
	}
	else
	{
		$contentid = $row['contentid'];
	}

	if($value == $row['value'] && $ceq)
	{
		$del[] = $row['id'];
	}
	else if($ceq)
	{
		$check[] = $id;
		$check[] = $row['id'];
		$value = $row['value'];
	}
	else
	{
		$value = $row['value'];
	}
	
	$id  = $row['id'];
	$ceq = false;
	$veq = false;
}

if(count($del))
{
	$sql = sprintf("DELETE FROM modx_site_tmplvar_contentvalues_copy WHERE id IN ('%s')", implode("','", $del));
	$modx->db->query($sql);
	$aff = mysql_affected_rows();
	if($aff)
	{
		echo sprintf("Deleted %u duplicate values!\n", mysql_affected_rows());
		$error = true;
	}
}

if(count($check))
{
	echo "Run the following SQL statement and fix the ambiguous values manually!\n\n";
	echo sprintf("SELECT * FROM modx_site_tmplvar_contentvalues_copy WHERE id IN ('%s') ORDER BY contentid, tmplvarid, value\n\n", implode("','", $check));
	$error = true;
}

if(!$error)
{
	// empty table
	$sql = 'TRUNCATE TABLE modx_site_tmplvar_contentvalues_copy';
	$modx->db->query($sql);
	
	echo 'No errors to report!';
}
else
{
	if(!count($check))
	{
		echo sprintf("
		Run the following commands to create a backup of your old data and start using the new values:\n\n
		ALTER TABLE modx_site_tmplvar_contentvalues RENAME backup_%s_modx_site_tmplvar_contentvalues\n
		ALTER TABLE modx_site_tmplvar_contentvalues_copy RENAME modx_site_tmplvar_contentvalues", 
		date('d_m_Y__h_i_s', time()));
	}
}

echo '</pre>';
?>