<?php
// $Id: table_check.php,v 1.3 2005/10/28 11:56:02 ohwada Exp $

// 2005-10-14 K.OHWADA
// corresponding to too many links

//================================================================
// check table validation
// 2005-01-20 K.OHWADA
//================================================================

include '../../../mainfile.php';
include '../../../include/cp_header.php';

global $xoopsDB, $xoopsUser, $xoopsModule;
xoops_cp_header();

echo "<h3>check table adjustment</h3>\n";

// check permission
$flag = false;
if ($xoopsUser && $xoopsUser->isAdmin($xoopsModule->mid())) 
{	$flag = true;	}

if (!$flag)
{
	echo "<font color='red'>you are not admin</font><br>";
	xoops_cp_footer();
	exit();
}

$check = new table_check();
$op = $check->get_post_get_pram();

switch ($op) 
{
case 'check_all':
	$check->check_all();
	break;

case 'check_link_in_catlink':
	$check->check_link_in_catlink();
	break;

case 'check_cat_in_catlink':
	$check->check_cat_in_catlink();
	break;

case 'check_catlink_in_link':
	$check->check_catlink_in_link();
	break;

case 'check_catlink_in_cat':
	$check->check_catlink_in_cat();
	break;

case "del_link_from_link":
	$check->del_link_from_link();
	break;

case "del_link_from_catlink":
	$check->del_link_from_catlink();
	break;

case "del_cat_from_catlink":
	$check->del_cat_from_catlink();
	break;

case 'main':
default:
	$check->print_form_start();
	break;

}

echo "<hr>\n";
xoops_cp_footer();
exit();
// --- main end ---


//================================================================
// class table_check
//================================================================
class table_check
{
	var $FLAG_DEBUG_LINK = 0;
	var $MODULE_URL;

	var $db;
	var $table_link;
	var $table_cat;
	var $table_catlink;

	var $op;
	var $limit;
	var $offset;


//---------------------------------------------------------
// constructor
//---------------------------------------------------------
function table_check()
{
	global $xoopsDB, $xoopsModule;

// dir name
	$MODULE_DIRNAME   = $xoopsModule->dirname();
	$MODULE_ROOT      = XOOPS_ROOT_PATH."/modules/".$MODULE_DIRNAME;
	$this->MODULE_URL = XOOPS_URL."/modules/".$MODULE_DIRNAME;

// table name
	$this->db            = $xoopsDB;
	$this->table_link    = $xoopsDB->prefix( $MODULE_DIRNAME."_link" );
	$this->table_cat     = $xoopsDB->prefix( $MODULE_DIRNAME."_category" );
	$this->table_catlink = $xoopsDB->prefix( $MODULE_DIRNAME."_catlink" );


}


//---------------------------------------------------------
// function
//---------------------------------------------------------
function get_post_get_pram()
{
	$this->op     = $this->get_post_get('op');
	$this->limit  = $this->get_post_get('limit', 0);
	$this->offset = $this->get_post_get('offset', 0);
	$this->next   = $this->limit + $this->offset;
	return $this->op;
}

function get_post_get($key, $default='')
{
	$val = '';
	if     ( isset($_POST[$key]) )  $val = $_POST[$key];
	elseif ( isset($_GET[$key]) )   $val = $_GET[$key];
	else  $val = $default;
	return $val;
}


//---------------------------------------------------------
// check
//---------------------------------------------------------
function check_all()
{
	$total_link      = $this->get_num_from_link();
	$total_cat       = $this->get_num_from_cat();
	$total_catlink   = $this->get_num_from_catlink();

?>
<h4>check all</h4>

<table>
<tr><td>total link     </td><td><?php echo $total_link; ?></td></tr>
<tr><td>total category </td><td><?php echo $total_cat; ?></td></tr>
<tr><td>total catlink  </td><td><?php echo $total_catlink; ?></td></tr>
</table>
<br />
<?php

	$lid_from_link    = $this->get_lid_from_link();
	$cid_from_cat     = $this->get_cid_from_cat();
	$lid_from_catlink = $this->get_lid_from_catlink();
	$cid_from_catlink = $this->get_cid_from_catlink();

// check zombie link
	$lid_res_arr = $this->check_in_array($lid_from_link, $lid_from_catlink);
	if ( count($lid_res_arr) )
	{
		$this->print_form_del_link_from_catlink($lid_res_arr);
		return;
	}

	echo "OK check lid of link in catlink table<br />\n";

// check zombie category
	$cid_res_arr = $this->check_in_array($cid_from_cat, $cid_from_catlink);
	if ( count($cid_res_arr) )
	{
		$this->print_form_del_cat_from_catlink($cid_res_arr);
		return;
	}

	echo "OK check cid of categoty in catlink table<br />\n";

// check link without category
	$lid_res_arr = $this->check_in_array($lid_from_catlink, $lid_from_link);
	if ( count($lid_res_arr) )
	{
		$this->print_form_del_link($lid_res_arr);
		return;
	}

	echo "OK check lid of catlink in link table<br />\n";

// check link without category
	$cid_res_arr = $this->check_in_array($cid_from_catlink, $cid_from_cat);
	if ( count($cid_res_arr) )
	{
		$this->print_cat_list($cid_res_arr);
		return;
	}

	echo "OK check lid of catlink in link table<br />\n";

	echo "<hr>\n";
	echo "<h4>FINISHED<h4>\n";
	echo "<a href='table_check.php'>Check Again</a><br /><br />\n";
	echo "<a href='index.php'>GOTO INDEX</a><br />\n";

}

function check_link_in_catlink()
{
	$total_link       = $this->get_num_from_link();
	$total_catlink    = $this->get_num_link_from_catlink();
	$lid_from_link    = $this->get_lid_from_link();
	$lid_from_catlink = $this->get_lid_from_catlink($this->limit, $this->offset);

	echo "<h4>check lid of link in catlink table</h4>\n";
	echo "total link: $total_link <br>\n";
	echo "$this->offset -> $this->next in catlink $total_catlink <br>\n";

// check zombie link
	$lid_res_arr = $this->check_in_array($lid_from_link, $lid_from_catlink);
	if ( count($lid_res_arr) )
	{
		$this->print_form_del_link_from_catlink($lid_res_arr);
		return;
	}

// next
	if (($this->limit > 0) && ($this->next < $total_catlink))
	{
		$this->print_form_next();
	}
	else
	{
		$this->print_check_next('check_cat_in_catlink');
	}
}

function check_cat_in_catlink()
{
	$total_cat        = $this->get_num_from_cat();
	$total_catlink    = $this->get_num_cat_from_catlink();
	$cid_from_cat     = $this->get_cid_from_cat();
	$cid_from_catlink = $this->get_cid_from_catlink($this->limit, $this->offset);

	echo "<h4>check cid of categoty in catlink table</h4>\n";
	echo "total category: $total_cat <br>\n";
	echo "$this->offset -> $this->next in catlink $total_catlink<br>\n";

// check zombie category
	$cid_res_arr = $this->check_in_array($cid_from_cat, $cid_from_catlink);
	if ( count($cid_res_arr) )
	{
		$this->print_form_del_cat_from_catlink($cid_res_arr);
		return;
	}

// next
	$this->next = $this->offset + $this->limit;
	if (($this->limit > 0) && ($this->next < $total_catlink))
	{
		$this->print_form_next();
	}
	else
	{
		$this->print_check_next('check_catlink_in_link');
	}
}


function check_catlink_in_link()
{
	$total_link       = $this->get_num_from_link();
	$total_catlink    = $this->get_num_link_from_catlink();
	$lid_from_link    = $this->get_lid_from_link($this->limit, $this->offset);
	$lid_from_catlink = $this->get_lid_from_catlink();

	echo "<h4>check lid of catlink in link table</h4>\n";
	echo "total catlink: $total_catlink <br>\n";
	echo "$this->offset -> $this->next in link $total_link <br>\n";

// check link without category
	$lid_res_arr = $this->check_in_array($lid_from_catlink, $lid_from_link);
	if ( count($lid_res_arr) )
	{
		$this->print_form_del_link($lid_res_arr);
		return;
	}

// next
	if (($this->limit > 0) && ($this->next < $total_link))
	{
		$this->print_form_next();
	}
	else
	{
		$this->print_check_next('check_catlink_in_cat');
	}
}

function check_catlink_in_cat()
{
	$total_cat        = $this->get_num_from_cat();
	$total_catlink    = $this->get_num_cat_from_catlink();
	$cid_from_cat     = $this->get_cid_from_cat($this->limit, $this->offset);
	$cid_from_catlink = $this->get_cid_from_catlink();

	echo "<h4>check cid of catlink in category table</h4>\n";
	echo "total catlink: $total_catlink <br>\n";
	echo "$this->offset -> $this->next in category $total_cat <br>\n";

// check link without category
	$cid_res_arr = $this->check_in_array($cid_from_catlink, $cid_from_cat);
	if ( count($cid_res_arr) )
	{
		$this->print_cat_list($cid_res_arr);
		return;
	}

// next
	if (($this->limit > 0) && ($this->next < $total_cat))
	{
		$this->print_form_next();
	}
	else
	{
		echo "<hr>\n";
		echo "<h4>FINISHED<h4>\n";
		echo "<a href='table_check.php'>Check Again</a><br /><br />\n";
		echo "<a href='index.php'>GOTO INDEX</a><br />\n";
	}

}

// check id of arr_2 in arr_1
function check_in_array($arr_1, $arr_2)
{
	$arr_3 = array();

	foreach ($arr_2 as $id)
	{
		if ( !in_array($id, $arr_1) )
		{
			$arr_3[] = $id;
		}
	}

	return $arr_3;
}

function print_check_next($op)
{
	echo "<hr>\n";
	echo "<h4>OK<h4>\n";
	echo "<a href='table_check.php?op=".$op."&amp;limit=".$this->limit."&amp;offset=0'>GOTO Next</a><br /><br />\n";
	echo "<hr>\n";	
	echo "<a href='table_check.php'>Check Again</a><br /><br />\n";
	echo "<a href='index.php'>GOTO INDEX</a><br />\n";

}

//---------------------------------------------------------
// print_form
//---------------------------------------------------------
function print_form_start()
{
	$action = xoops_getenv('PHP_SELF');

	$total_link      = $this->get_num_from_link();
	$total_cat       = $this->get_num_from_cat();
	$total_catlink   = $this->get_num_from_catlink();
	$num_link_from_catlink = $this->get_num_link_from_catlink();
	$num_cat_from_catlink  = $this->get_num_cat_from_catlink();

?>
<h4>check zombie link</h4>

<table>
<tr><td>lid of link table    </td><td><?php echo $total_link; ?></td></tr>
<tr><td>lid of catlink table </td><td><?php echo $num_link_from_catlink; ?></td></tr>
<tr><td>cid of category table</td><td><?php echo $total_cat; ?></td></tr>
<tr><td>cid of catlink table </td><td><?php echo $num_cat_from_catlink; ?></td></tr>
<tr><td>records of catlink table </td><td><?php echo $total_catlink; ?></td></tr>
</table>
<br />
<?php

if ($total_link != $num_link_from_catlink)
{
	echo "<font color='red'>unmatch number of lids</font><br />\n";
}

if ($total_cat < $num_cat_from_catlink)
{
	echo "<font color='red'>unmatch number of cids</font><br />\n";
}
elseif ($total_cat != $num_cat_from_catlink)
{
	echo "unmatch number of cids, may be OK<br />\n";
}

?>
<br />
When there are many links, timeout may occure.<br />
Plaese set limit, and start at "check lid of link in catlink table"<br />
limit = 0 means unlimitd<br />
<br />

<form action='<?php echo $action; ?>' method='post'>
<input type='hidden' name='offset' value='0' />

<table>
<tr><td>op</td><td>
<input type='radio' name='op' value='check_all' checked />CHECK ALL<br />
<input type='radio' name='op' value='check_link_in_catlink' />check lid of link in catlink table<br />
<input type='radio' name='op' value='check_cat_in_catlink' />check cid of category in catlink table<br />
<input type='radio' name='op' value='check_catlink_in_link' />check lid of catlink in link table<br />
<input type='radio' name='op' value='check_catlink_in_cat' />check cid of catlink in cattgory table<br />
</td></tr>
<tr><td>limit</td><td><input type='text' name='limit'  value='0' /></td></tr>
<tr><td></td><td><input type='submit' value='GO'></td></tr>
</table>

</form>
<?php

}

function print_form_next()
{
	$action = xoops_getenv('PHP_SELF');

?>
<br />
<form action='<?php echo $action; ?>' method='post'>
<input type='hidden' name='op'     value='<?php echo $this->op; ?>'>
<input type='hidden' name='limit'  value='<?php echo $this->limit; ?>'>
<input type='hidden' name='offset' value='<?php echo $this->next; ?>'>
<input type='submit' value='NEXT <?php echo $this->limit; ?>'>
</form>
<?php

}

function print_form_del_link_from_catlink($lid_arr)
{
	$count  = count($lid_arr);
	$action = xoops_getenv('PHP_SELF');

?>
<br>
<hr>
<h4><font color='red'>ERROR</font></h4>
There are <b><?php echo $count; ?></b> zombie links in catlink table <br>
<br>
<form action='<?php echo $action; ?>' method='post'>
<input type='hidden' name='op'     value='del_link_from_catlink'>
<input type='hidden' name='limit'  value='<?php echo $this->limit; ?>'>
<input type='hidden' name='offset' value='<?php echo $this->offset; ?>'>
<?php

	foreach($lid_arr as $lid)
	{
		echo "<input type='hidden' name='lid[]' value='$lid'>";
		echo "lid: $lid <br>\n";
	}

	echo "<br>\n";

?>
<br />
<input type='submit' value='DELETE link from catlink'>
</form>
<br />
<a href='table_check.php'>Check Again</a><br />
<br />
<a href='index.php'>GOTO INDEX</a><br />
<?php

}

function print_form_del_cat_from_catlink($cid_arr)
{
	$count  = count($cid_arr);
	$action = xoops_getenv('PHP_SELF');

?>
<br>
<hr>
<h4><font color='red'>ERROR</font></h4>
There are <b><?php echo $count; ?></b> zombie categories in catlink table <br>
<br>
<form action='<?php echo $action; ?>' method='post'>
<input type='hidden' name='op'     value='del_cat_from_catlink'>
<input type='hidden' name='limit'  value='<?php echo $this->limit; ?>'>
<input type='hidden' name='offset' value='<?php echo $this->offset; ?>'>
<?php

	foreach($cid_arr as $cid)
	{
		echo "<input type='hidden' name='cid[]' value='$cid'>";
		echo "cid: $cid <br>\n";
	}

	echo "<br>\n";

?>
<br />
<input type='submit' value='DELETE category from catlink'>
</form>
<br />
<a href='table_check.php'>Check Again</a><br />
<br />
<a href='index.php'>GOTO INDEX</a><br />
<?php

}

function print_form_del_link($lid_arr)
{
	$action = xoops_getenv('PHP_SELF');
	$count  = count($lid_arr);

?>
<h4><font color='red'>ERROR</font></h4>
There are <b><?php echo $count; ?></b> links without category in link table <br>
<br>
<?php

if ($this->FLAG_DEBUG_LINK)
{
	echo "<form action='$action' method='post'>\n";
	echo "<input type='hidden' name='op'     value='del_link_from_link'  />\n";
	echo "<input type='hidden' name='limit'  value='". $this->limit . "' />\n";
	echo "<input type='hidden' name='offset' value='". $this->offset ."' />\n";
}

?>
<table border='1'><tr>
<th>link id</th>
<th>title</th>
</tr>
<?php

	foreach($lid_arr as $lid)
	{
		$title = $this->get_link_title_by_lid($lid);

		$link_id = sprintf("%03d", $lid);
		$modlink = $this->MODULE_URL."/admin/link_manage.php?op=modLink&lid=".$lid;
		$title   = htmlspecialchars($title);

		echo "<tr><td>";
		echo "<a href='$modlink'>$link_id</a>";
		echo "<input type='hidden' name='lid[]' value='$lid'>";
		echo "</a>";
		echo "<td>$title</td>";
		echo "</tr>\n";
	}

?>
</table>
<br />
<?php

if ($this->FLAG_DEBUG_LINK)
{
	echo "<input type='submit' value='DELETE link form link'>";
	echo "</form>\n";
}

?>
<br />
<a href='table_check.php'>Check Again</a><br />
<br />
<a href='index.php'>GOTO INDEX</a><br />
<?php


}

function print_cat_list($cid_arr)
{
	$action = xoops_getenv('PHP_SELF');
	$count  = count($cid_arr);

?>
<h4>Notice</h4>
There are <b><?php echo $count; ?></b> categories without link in category table <br>
<br>
<table border='1'><tr>
<th>category id</th>
<th>title</th>
</tr>
<?php

	foreach($cid_arr as $cid)
	{
		$title = $this->get_cat_title_by_cid($cid);

		$cat_id = sprintf("%03d", $cid);
		$modlink = $this->MODULE_URL."/admin/category_manage.php?op=modCat&cid=".$cid;
		$title   = htmlspecialchars($title);

		echo "<tr>";
		echo "<td><a href='$modlink'>$cat_id</a>";
		echo "<td>$title</td>";
		echo "</tr>\n";
	}

?>
</table>
<br />
<a href='table_check.php'>Check Again</a><br />
<br />
<a href='index.php'>GOTO INDEX</a><br />
<?php

}

//---------------------------------------------------------
// delete data from table
//---------------------------------------------------------
function del_link_from_link()
{
	echo "<h4>delete link from link table</h4>\n";

	$lid_arr = array();
	if ( isset($_POST['lid']) )  $lid_arr = $_POST['lid'];

	foreach ($lid_arr as $lid)
	{
		echo "lid: $lid <br>\n";
		$this->del_link_by_lid($lid);
	}

	$this->print_check_again('check_catlink_in_link');

}

function del_link_from_catlink()
{
	echo "<h4>delete link from catlink table</h4>\n";

	$lid_arr = array();
	if ( isset($_POST['lid']) )  $lid_arr = $_POST['lid'];

	foreach ($lid_arr as $lid)
	{
		echo "lid: $lid <br>\n";
		$this->del_catlink_by_lid($lid);
	}

	$this->print_check_again('check_link_in_catlink');

}

function del_cat_from_catlink()
{
	echo "<h4>delete category from catlink table</h4>\n";

	$cid_arr = array();
	if ( isset($_POST['cid']) )  $cid_arr = $_POST['cid'];

	foreach ($cid_arr as $cid)
	{
		echo "cid: $cid <br>\n";
		$this->del_catlink_by_cid($cid);
	}

	$this->print_check_again('check_cat_in_catlink');

}

function print_check_again($op)
{
	echo "<hr>\n";
	echo "<h4>DELETED</h4>\n";
	echo "<a href='table_check.php?op=".$op."&amp;limit=".$this->limit."&amp;offset=".$this->offset."'>Check Again</a><br />\n";
}

//---------------------------------------------------------
// SQL function
//---------------------------------------------------------
function get_num_from_link()
{
	$sql = "SELECT count(*) FROM ".$this->table_link;
	return $this->sql_num($sql);
}

function get_num_from_cat()
{
	$sql = "SELECT count(*) FROM ".$this->table_cat;
	return $this->sql_num($sql);
}

function get_num_from_catlink()
{
	$sql = "SELECT count(*) FROM ".$this->table_catlink;
	return $this->sql_num($sql);
}

function get_num_link_from_catlink()
{
	$sql = "SELECT DISTINCT lid FROM ".$this->table_catlink." ORDER BY lid";
	$arr = $this->sql_first_row($sql);
	$num = count($arr);
	return $num;
}

function get_num_cat_from_catlink()
{
	$sql = "SELECT DISTINCT cid FROM ".$this->table_catlink." ORDER BY cid";
	$arr = $this->sql_first_row($sql);
	$num = count($arr);
	return $num;
}

function get_lid_from_link($limit=0, $offset=0)
{
	$sql = "SELECT lid FROM .".$this->table_link." ORDER BY lid";
	return $this->sql_first_row($sql, $limit, $offset);
}

function get_lid_from_catlink($limit=0, $offset=0)
{
	$sql = "SELECT DISTINCT lid FROM ".$this->table_catlink." ORDER BY lid";
	return $this->sql_first_row($sql, $limit, $offset);
}

function get_cid_from_cat($limit=0, $offset=0)
{
	$sql = "SELECT cid FROM .".$this->table_cat." ORDER BY cid";
	return $this->sql_first_row($sql, $limit, $offset);
}

function get_cid_from_catlink($limit=0, $offset=0)
{
	$sql = "SELECT DISTINCT cid FROM ".$this->table_catlink." ORDER BY cid";
	return $this->sql_first_row($sql, $limit, $offset);
}

function get_link_title_by_lid($lid)
{
	$sql = "SELECT title FROM ".$this->table_link." WHERE lid=$lid";
	$res = $this->sql_exec($sql);
	$row = $this->db->fetchRow($res);

	return $row[0];
}

function get_cat_title_by_cid($cid)
{
	$sql = "SELECT title FROM ".$this->table_cat." WHERE cid=$cid";
	$res = $this->sql_exec($sql);
	$row = $this->db->fetchRow($res);

	return $row[0];
}

function del_link_by_lid($lid)
{
	$sql = "DELETE FROM ".$this->table_link." WHERE lid=$lid";
	$this->sql_exec($sql);
}

function del_catlink_by_lid($lid)
{
	$sql = "DELETE FROM ".$this->table_catlink." WHERE lid=$lid";
	$this->sql_exec($sql);
}

function del_catlink_by_cid($cid)
{
	$sql = "DELETE FROM ".$this->table_catlink." WHERE cid=$cid";
	$this->sql_exec($sql);
}

function sql_num($sql)
{
	$res = $this->sql_exec($sql);
	$row = $this->db->fetchRow($res); 
	$num = intval( $row[0] );
	return $num;
}

function sql_first_row($sql, $limit=0, $offset=0)
{
	$res = $this->sql_exec($sql, $limit, $offset);

	$arr = array();
	while ( $row = $this->db->fetchRow($res) ) 
	{
		$arr[] = $row[0];
	}

	return $arr;
}

function sql_exec($sql, $limit=0, $offset=0)
{ 
	$ret = $this->db->queryF($sql, $limit, $offset);
	if ($ret != false ) { return $ret; }

	$error = $this->db->error();
	echo "<font color='red'>$sql<br>$error</font><br>";

	return false;
}

// --- class end ---
}


?>