<?php
/* 	OpenDb - Open Media Lending Database
	Copyright (C) 2001,2002 by Jason Pell

	This program is free software; you can redistribute it and/or
	modify it under the terms of the GNU General Public License
	as published by the Free Software Foundation; either version 2
	of the License, or (at your option) any later version.

	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
	GNU General Public License for more details.

	You should have received a copy of the GNU General Public License
	along with this program; if not, write to the Free Software
	Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
*/
include_once("./admin/s_attribute_type/functions.php");
include_once("./admin/s_item_type/functions.php");

/*
* Get rid of spaces in s_attribute_type_lookup value fields, and update the
* item_attribute's that point to these problem value fields to the new format.
*/
function remove_lookup_whitespace()
{
	// Find any value columns which have space in them.
	$result = run_opendb_query("SELECT satl.s_attribute_type, satl.display, satl.value, sat.s_field_type FROM s_attribute_type sat, s_attribute_type_lookup satl WHERE sat.s_attribute_type = satl.s_attribute_type AND (satl.value LIKE '% %' OR satl.value LIKE '%	%')");
	if($result && mysql_num_rows($result)>0)
	{
		while($lookup_r = mysql_fetch_array($result, MYSQL_ASSOC))
		{
			exec_patch_sql_statement("UPDATE s_attribute_type_lookup SET value = '".preg_replace("/[\s]*/", "", $lookup_r['value'])."', display = '".ifempty($lookup_r['display'],$lookup_r['value'])."' WHERE s_attribute_type = '".$lookup_r['s_attribute_type']."' AND value = '".$lookup_r['value']."'");
			if($lookup_r['s_field_type'] == 'CATEGORY')
			{
				$query = "SELECT i.id, i.category ".
					"FROM item i ".
					"WHERE ( UPPER(i.category) = '".str_replace('\_','_',$lookup_r['value'])."' OR ".
					"UPPER(i.category) LIKE '% ".$lookup_r['value']." %' OR ".
					"UPPER(i.category) LIKE '".$lookup_r['value']." %' OR ".
					"UPPER(i.category) LIKE '% ".$lookup_r['value']."') ";

				$result2 = run_opendb_query($query);
				if($result2)
				{		
					while($item_r = mysql_fetch_array($result2, MYSQL_ASSOC))
					{	
						print_r($attribute_val_r);
						exec_patch_sql_statement("UPDATE item SET category = '".str_replace($lookup_r['value'], preg_replace("/[\s]*/", "", $lookup_r['value']), $item_r['category'])."' ".
												"WHERE id = '".$item_r['id']."'");
					}
				}
			}
			else
			{
				$query = "SELECT ia.item_id, ia.s_attribute_type, ia.order_no, ia.attribute_val ".
					"FROM item_attribute ia ".
					"WHERE ( UPPER(ia.attribute_val) = '".str_replace('\_','_',$lookup_r['value'])."' OR ".
					"UPPER(ia.attribute_val) LIKE '% ".$lookup_r['value']." %' OR ".
					"UPPER(ia.attribute_val) LIKE '".$lookup_r['value']." %' OR ".
					"UPPER(ia.attribute_val) LIKE '% ".$lookup_r['value']."') ";

				$result2 = run_opendb_query($query);
				if($result2)
				{		
					while($attribute_val_r = mysql_fetch_array($result2, MYSQL_ASSOC))
					{	
						exec_patch_sql_statement("UPDATE item_attribute SET attribute_val = '".str_replace($lookup_r['value'], preg_replace("/[\s]*/", "", $lookup_r['value']), $attribute_val_r['attribute_val'])."' ".
												"WHERE item_id = '".$attribute_val_r['item_id']."' AND ".
												"s_attribute_type = '".$attribute_val_r['s_attribute_type']."' AND ".
												"order_no = '".$attribute_val_r['order_no']."'");
					}
				}
			}
		}
		mysql_free_result($result);
	}
	else
	{
		echo("<div class=\"upgrade_warning\">No Lookup records found with whitespace.</div>");
	}
}

function is_sat_from_siat_updated()
{
	$result = run_opendb_query("SELECT s_field_type FROM s_item_attribute_type LIMIT 0,1");
	if($result)
	{
		mysql_free_result($result);
		return FALSE;
	}
	else
		return TRUE;
}

function is_s_field_type_in_sat_table()
{
	$result = run_opendb_query("SELECT s_field_type FROM s_attribute_type LIMIT 0,1");
	if($result)
	{
		mysql_free_result($result);
		return FALSE;
	}
	else
		return TRUE;
}

function update_sat_from_siat()
{
	$result = run_opendb_query("SELECT DISTINCT s_attribute_type, s_field_type FROM s_item_attribute_type WHERE length(ifnull(s_field_type,''))>0");
	if($result)
	{
		while($s_item_attr_type_r = mysql_fetch_array($result))
		{
			// Not sure whether this will be a problem in reality, but it is a problem for me.
			if($s_item_attr_type_r['s_field_type'] == 'STATUS_IND')
				$s_item_attr_type_r['s_field_type'] = 'STATUSTYPE';
				
			exec_patch_sql_statement("UPDATE s_attribute_type SET s_field_type = '".$s_item_attr_type_r['s_field_type']."' WHERE s_attribute_type = '".$s_item_attr_type_r['s_attribute_type']."'");
		}
		mysql_free_result($result);
	}
}

function is_reviews_updated()
{
	$result = run_opendb_query("SELECT sequence_number FROM review LIMIT 0,1");
	if($result)
	{
		mysql_free_result($result);
		return TRUE;
	}
	else
		return FALSE;
}

function update_reviews()
{
	// If not yet converted.
	if(!is_reviews_updated())
	{
		if(exec_patch_sql_statement("RENAME TABLE review TO review_old"))
		{
			// Create new table definition.
			$query = "CREATE TABLE review (".
					"\n\tsequence_number	integer(10) unsigned NOT NULL auto_increment,".
  					"\n\tauthor_id		varchar(20) NOT NULL,".
					"\n\titem_id		integer(10) unsigned NOT NULL,".
					"\n\tcomment		text,".
					"\n\trating		varchar(1) NOT NULL,".
					"\n\tupdate_on		timestamp(14) NOT NULL,".
					"\n\tPRIMARY KEY ( sequence_number ),".
					"\n\tKEY author_item ( author_id, item_id )".
					"\n) TYPE=MyISAM COMMENT='Item Reviews'";
			
			if(exec_patch_sql_statement($query))
			{
				$query = "SELECT author_id, item_id, comment, rating, update_on FROM review_old ORDER BY update_on ASC";
				$result = run_opendb_query($query);
				if($result)
				{
					while($review_r = mysql_fetch_array($result))
					{
						exec_patch_sql_statement("INSERT INTO review (item_id,author_id,comment,rating,update_on) ".
										"VALUES ('".$review_r['item_id']."','".$review_r['author_id']."','".addslashes($review_r['comment'])."','".$review_r['rating']."','".$review_r['update_on']."')");
					}
					mysql_free_result($result);
				}
				
				exec_patch_sql_statement("DROP TABLE review_old");
			}
		}
	}
}

function update_item_types()
{
	global $HTTP_SESSION_VARS;
	
	// Use '*', in case title_prompt has already been dropped.
	$query = "SELECT * FROM s_item_type";
	$result = run_opendb_query($query);
	if($result)
	{
		while($item_type_r = mysql_fetch_array($result))
		{
			echo ("<p class=\"querytitle\">Update Item Type (".$item_type_r['s_item_type'].") Structure</p>");
	
			if(!fetch_sfieldtype_item_attribute_type($item_type_r['s_item_type'], 'CATEGORY'))
			{				
				echo("<div class=\"upgrade_failure\">Item attribute type for s_field_type CATEGORY is missing.  Use System Admin 'Types' tool to rectify after upgrade.</div>");
			}
			
			if(!is_exists_item_attribute_type($item_type_r['s_item_type'], 'S_TITLE'))
			{
				// Insert new S_TITLE attribute.
				if(insert_s_item_attribute_type($item_type_r['s_item_type'], 'S_TITLE', '1', $item_type_r['title_prompt'], 'Y', $HTTP_SESSION_VARS['user_id']))
					echo("<div class=\"upgrade_success\">Item attribute type S_TITLE inserted</div>");
				else
					echo("<div class=\"upgrade_failure\">Item attribute type S_TITLE <b>not</b> inserted</div>");
			}
			else
			{
				echo("<div class=\"upgrade_warning\">Item attribute type S_TITLE already inserted.</div>");
			}
			
			if(!is_exists_item_attribute_type($item_type_r['s_item_type'], 'S_STATUS'))
			{		
				// Insert new S_STATUS attribute.
				if(insert_s_item_attribute_type($item_type_r['s_item_type'], 'S_STATUS', '254', NULL, 'N', $HTTP_SESSION_VARS['user_id']))
					echo("<div class=\"upgrade_success\">Item attribute type S_STATUS inserted</div>");
				else
					echo("<div class=\"upgrade_failure\">Item attribute type S_STATUS <b>not</b> inserted</div>");
			}
			else
			{
				echo("<div class=\"upgrade_warning\">Item attribute type S_STATUS already inserted.</div>");
			}
			
			if(!is_exists_item_attribute_type($item_type_r['s_item_type'], 'S_STATCMNT'))
			{		
				// Insert new S_STATUS attribute.
				if(insert_s_item_attribute_type($item_type_r['s_item_type'], 'S_STATCMNT', '255', NULL, 'N', $HTTP_SESSION_VARS['user_id']))
					echo("<div class=\"upgrade_success\">Item attribute type S_STATCMNT inserted</div>");
				else
					echo("<div class=\"upgrade_failure\">Item attribute type S_STATCMNT <b>not</b> inserted</div>");
			}
			else
			{
				echo("<div class=\"upgrade_warning\">Item attribute type S_STATCMNT already inserted.</div>");
			}

			if(!is_exists_item_attribute_type($item_type_r['s_item_type'], 'S_DURATION'))
			{				
				// Insert new S_DURATION attribute.
				if(insert_s_item_attribute_type($item_type_r['s_item_type'], 'S_DURATION', '200', NULL, 'N', $HTTP_SESSION_VARS['user_id']))
					echo("<div class=\"upgrade_success\">Item attribute type S_DURATION inserted</div>");
				else
					echo("<div class=\"upgrade_failure\">Item attribute type S_DURATION <b>not</b> inserted</div>");
			}
			else
			{
				echo("<div class=\"upgrade_warning\">Item attribute type S_DURATION already inserted.</div>");
			}
		}
		mysql_free_result($result);
	}
}

if($CONFIG_VARS['patch.enable']===TRUE)
{
	if($HTTP_VARS['op'] == "menu")
	{
		echo("<p>
		</p>");

		echo("<DIV ALIGN=\"CENTER\">");
		echo("<FORM action=\"$PHP_SELF\" method=\"get\" target=\"main\">");
		print_button("patch_before", "1. Execute patch_before.sql", FALSE);
		echo("&nbsp;");
		print_button("update_siat", "2. Copy S_FIELD_TYPE values to Attribute Type", is_sat_from_siat_updated());
		echo("&nbsp;");
		print_button("update_types", "3. Update Item Types", FALSE);
		echo("&nbsp;");
		print_button("update_reviews", "4. Convert Reviews", is_reviews_updated());
		echo("&nbsp;");
		print_button("remove_lookup_whitespace", "5. Lookup Record(s) whitespace removal", is_s_field_type_in_sat_table());
		echo("&nbsp;");
		print_button("patch_after", "6. Execute patch_after.sql", FALSE);
		echo("<INPUT TYPE=\"hidden\" name=\"op\" value=\"result\">");
		echo("<INPUT TYPE=\"hidden\" name=\"from\" value=\"".$HTTP_VARS['from']."\">");
		echo("</FORM>");
		echo("</DIV>");
		
		echo("<p class=\"colortext\">Please ensure that you complete ALL steps.  The steps must be completed
		in order, or it is possible that not everything will be updated correctly.  If you experience problems
		with Adding/Editing Items, such as 'Invalid Item Type' errors, then it is quite possible that this patch
		was not completed successfully.</b></p>");
	}
	else 
	{
		if(isset($HTTP_VARS['update_types']))
			update_item_types();	
		else if(isset($HTTP_VARS['update_siat']))
			update_sat_from_siat();
		else if(isset($HTTP_VARS['update_reviews']))
			update_reviews();
		else if(isset($HTTP_VARS['remove_lookup_whitespace']))
			remove_lookup_whitespace();
		else if(isset($HTTP_VARS['patch_before'] ))
			exec_patch_sql_file($HTTP_VARS['from'], "patch_before.sql", TRUE);
		else if(isset($HTTP_VARS['patch_after'] ))
			exec_patch_sql_file($HTTP_VARS['from'], "patch_after.sql", TRUE);
	}
}
?>