<?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("./functions/utils.php");
include_once("./functions/address_type.php");
include_once("./functions/item_attribute.php");

function transfer_email()
{
	global $HTTP_SESSION_VARS;
	global $CONFIG_VARS;
	
	if(is_array($CONFIG_VARS['email.user_address_attribute']) && 
				isset($CONFIG_VARS['email.user_address_attribute']['s_attribute_type']))
	{
		$user_results = run_opendb_query("SELECT user_id, email, type FROM user");
		if($user_results)
		{
			while($user_r = mysql_fetch_array($user_results))
			{
				echo ("<p class=\"querytitle\">Transfer email address for user ".$user_r['user_id']."</p>");
	
				$addr_results = fetch_address_type_attribute_type_rs('EMAIL', $user_r['type'], 'update', TRUE);
				if($addr_results)
				{
					// should be only one attribute.
					while($addr_attribute_type_r = mysql_fetch_array($addr_results, MYSQL_ASSOC))
					{
						if($addr_attribute_type_r['s_attribute_type'] == $CONFIG_VARS['email.user_address_attribute']['s_attribute_type'])
						{
							if(!is_exists_valid_user_address($user_r['user_id'], $addr_attribute_type_r['s_address_type']))
							{
								$new_sequence_number = insert_user_address($user_r['user_id'], $addr_attribute_type_r['s_address_type']);
								if($new_sequence_number !== FALSE)
								{
									if(insert_user_address_attribute($new_sequence_number, $addr_attribute_type_r['s_attribute_type'], $addr_attribute_type_r['order_no'], $user_r['email']))
									{
										echo("<div class=\"upgrade_success\">Email address transferred</div>");
									}
									else
									{
										echo("<div class=\"upgrade_failure\">Email address not transferred</div>");
									}
								}
								else
								{
									echo("<div class=\"upgrade_failure\">Email address not transferred</div>");
								}
							}
							else
							{
								echo("<div class=\"upgrade_warning\">Email address already transferred</div>");
							}
						}
					}
					mysql_free_result($addr_results);
				}
			}
			mysql_free_result($user_results);
		}
	}
}

function is_exists_old_user_email_column()
{
	$result = run_opendb_query("SELECT user_id, email FROM user LIMIT 0,1");
	if($result)
		return TRUE;
	else
		return FALSE;
}

function is_exists_old_attribute_table()
{
	$result = run_opendb_query("SELECT 'x' FROM item_attribute_old LIMIT 0,1");
	if($result)
	{
		mysql_free_result($result);
		return TRUE;
	}
	else
		return FALSE;
}

function is_old_attribute_table()
{
	$result = run_opendb_query("SELECT lookup_attribute_val FROM item_attribute LIMIT 0,1");
	if($result)
	{
		mysql_free_result($result);
		return FALSE;
	}
	else
	{
		// not old as it succeeded.
		return TRUE;
	}
}

function is_lookup_attribute_type_converted()
{
	if(is_exists_old_attribute_table())
	{
		if(!is_old_attribute_table())
		{
			if(get_old_item_attribute_count()>0)
				return get_item_attribute_count()>0;
			else
				return TRUE; // otherwise nothing to transfer
		}
	}
	else
	{
		if(!is_old_attribute_table() && get_item_attribute_count()>0)
			return TRUE;
		else
			return FALSE;
	}
}

function is_old_user_addr_attr_table()
{
	$result = run_opendb_query("SELECT lookup_attribute_val FROM user_address_attribute LIMIT 0,1");
	if($result)
	{
		mysql_free_result($result);
		return FALSE;
	}
	else
	{
		// not old as it succeeded.
		return TRUE;
	}
}

/*
* Check whether patch_before has been executed or not.  Pick some
* table changes to base this test on, and make sure all table changes
* even those from most recent dev release are included, otherwise
* patch before will not be available.
*/
function is_patch_before_executed()
{
	if(run_opendb_query("SELECT change_owner_ind FROM s_status_type LIMIT 0,1") && 
			run_opendb_query("SELECT active_ind FROM user LIMIT 0,1"))
	{
		return TRUE;
	}
	else
	{
		return FALSE;
	}
}

/*
* Check that these actions have been applied.
* ALTER TABLE user DROP email;
* ALTER TABLE user DROP location;
* DROP TABLE item_attribute_old;
*/
function is_patch_after_executed()
{
	if(is_exists_old_user_email_column())
		return FALSE;
			
	if(is_exists_old_attribute_table())
		return FALSE;
			
	//else
	return TRUE;
}

function is_address_type_executed()
{
	$result = run_opendb_query("SELECT 'x' FROM s_address_type LIMIT 0,1");
	if($result)
		return TRUE;
	else
		return FALSE;
}

/*
* whether we need to run dev11+ patch for additional column in user_address_attribute
* table, that was introduced in dev11.
*/
function is_exists_dev11_user_address_attribute()
{
	$result = run_opendb_query("SELECT lookup_attribute_val FROM user_address_attribute LIMIT 0,1");
	if($result)
		return TRUE;
	else
		return FALSE;
}

function is_exists_old_user_addr_attr_table()
{
	$result = run_opendb_query("SELECT 'x' FROM user_address_attribute_old LIMIT 0,1");
	if($result)
	{
		mysql_free_result($result);
		return TRUE;
	}
	else
	{
		return FALSE;
	}
}

/*
* Assumes s_address_type functionality already installed.
*/
function is_user_addresses_transferred()
{
	// the easy way is to see if there are records in the table
	if(get_user_addr_attr_count()>0)
		return TRUE;
	else 
	{
		if(is_exists_old_user_email_column())
		{
			// we have to be more clever, in case there were no addresses to transfer.
			$result = run_opendb_query("SELECT 'x' FROM user WHERE LENGTH(IFNULL(email,'')) > 0");
			if($result && mysql_num_rows($result)>0)
			{
				// if there are records in user table, with a email address not null, then
				// the transfer process has not occured.
				mysql_free_result($result);
				return FALSE;
			}
			else
			{
				// looks like we have nothing to transfer.
				return TRUE;
			}
		}
		else if(is_exists_old_user_addr_attr_table())
		{
			// the very first query does a count against the user_address_attribute table, so
			// if its empty, then the transfer has not occurred.
			return FALSE;
		}
		else
		{
			return TRUE;
		}
	}
}

function get_user_addr_attr_count()
{
	$result = run_opendb_query("SELECT COUNT('x') as count FROM user_address_attribute");
	if($result)
	{
		$found = mysql_fetch_array($result);
		mysql_free_result($result);
		return $found['count'];
	}
	else
		return FALSE;
}

function get_item_attribute_count()
{
	$result = run_opendb_query("SELECT COUNT('x') as count FROM item_attribute");
	if($result)
	{
		$found = mysql_fetch_array($result);
		mysql_free_result($result);
		return $found['count'];
	}
	else
		return FALSE;
}

function get_old_item_attribute_count()
{
	$result = run_opendb_query("SELECT COUNT('x') as count FROM item_attribute_old");
	if($result)
	{
		$found = mysql_fetch_array($result);
		mysql_free_result($result);
		return $found['count'];
	}
	else
		return FALSE;
}

function convert_lookup_item_attributes()
{
	// first of all get a list of all s_attribute_type_lookup s_attribute_type's
	$lookup_attribute_type_r = array();
	$results = run_opendb_query("SELECT DISTINCT s_attribute_type FROM s_attribute_type_lookup");
	if($results)
	{
		while($attribute_type_r = mysql_fetch_array($results))
		{
			$lookup_attribute_type_r[] = $attribute_type_r['s_attribute_type'];
		}
		mysql_free_result($results);
	}
	
	// Get a list of s_attribute_type_lookups.
	$results = run_opendb_query("SELECT item_id, s_attribute_type, order_no, attribute_val, update_on FROM item_attribute_old ORDER BY item_id, order_no");
	if($results)
	{
		$item_id = NULL;
		while($item_attribute_type_r = mysql_fetch_array($results))
		{
			if($item_id == NULL || $item_id != $item_attribute_type_r['item_id'])
			{
				$item_id = $item_attribute_type_r['item_id'];
				echo("<div class=\"colortext\">Transferring attributes for item $item_id...</div>");
			}
			
			echo("<div class=\"colortext\">Attribute: ".$item_attribute_type_r['s_attribute_type'].".".$item_attribute_type_r['order_no']."</div>");
			
			// is lookup attribute type
			if(in_array($item_attribute_type_r['s_attribute_type'], $lookup_attribute_type_r))
			{
				$lookup_attribute_val_r = trim_explode(' ', trim($item_attribute_type_r['attribute_val']));
				if(insert_lookup_item_attributes($item_attribute_type_r['item_id'], $item_attribute_type_r['s_attribute_type'], $item_attribute_type_r['order_no'], $lookup_attribute_val_r))
				{
					echo("<div class=\"upgrade_success\">Lookup Attribute transferred successfully.</div>");
				}
				else
				{
					echo("<div class=\"upgrade_failure\">Lookup Attribute not transferred successfully.</div>");
				}
			}
			else
			{
				if(insert_item_attribute($item_attribute_type_r['item_id'], $item_attribute_type_r['s_attribute_type'], $item_attribute_type_r['order_no'], trim($item_attribute_type_r['attribute_val'])))
				{
					echo("<div class=\"upgrade_success\">Attribute transferred successfully.</div>");
				}
				else
				{
					echo("<div class=\"upgrade_failure\">Attribute not transferred successfully.</div>");
				}
			}
		}
		mysql_free_result($results);
		
		echo( "<p><div class=\"success\">Done!</div></p>");
	}
}

function check_item_attribute_transfer()
{
	echo( "<h3>Lookup Attribute Transfer results...</h3>");
	
	echo("<p>Please Note: <ul><li>The OLD and NEW Distinct Item Counts should be exactly the same.</li>".
		"<li>The NEW Lookup Item Attribute Count should be higher than the OLD Lookup Item Attribute Count</p></li></ul>");
	
	// first of all get a list of all s_attribute_type_lookup s_attribute_type's
	$lookup_attribute_type_r = array();
	$results = run_opendb_query("SELECT DISTINCT s_attribute_type FROM s_attribute_type_lookup");
	if($results)
	{
		while($attribute_type_r = mysql_fetch_array($results))
		{
			$lookup_attribute_type_r[] = $attribute_type_r['s_attribute_type'];
		}
		mysql_free_result($results);
	}
	
	$in_clause = format_sql_in_clause($lookup_attribute_type_r);
	
	echo("<h2>OLD Lookup Item Attribute, Distinct Item Count</h2>");
	echo("<p class=\"querytitle\">SELECT COUNT(DISTINCT item_id) AS count FROM item_attribute_old WHERE s_attribute_type IN($in_clause)</p>");
	$result = run_opendb_query("SELECT COUNT(DISTINCT item_id) AS count FROM item_attribute_old WHERE s_attribute_type IN($in_clause)");
	if($result && mysql_num_rows($result)>0)
	{
    	$found = mysql_fetch_array($result, MYSQL_ASSOC);
		mysql_free_result($result);
		echo("<div class=\"upgrade_success\">".$found['count']." records.</div>");
		
		echo("<h2>OLD Lookup Item Attribute Count</h2>");
		echo("<p class=\"querytitle\">SELECT COUNT(*) AS count FROM item_attribute_old WHERE s_attribute_type IN($in_clause)</p>");
		$result = run_opendb_query("SELECT COUNT(*) AS count FROM item_attribute_old WHERE s_attribute_type IN($in_clause)");
		if($result && mysql_num_rows($result)>0)
		{
    		$found = mysql_fetch_array($result, MYSQL_ASSOC);
			mysql_free_result($result);
			echo("<div class=\"upgrade_success\">".$found['count']." records.</div>");
		}
		else
		{
			echo("<div class=\"upgrade_failure\">Count failed.</div>");
		}
		
		echo("<h2>NEW Lookup Item Attribute, Distinct Item Count</h2>");
		echo("<p class=\"querytitle\">SELECT COUNT(DISTINCT item_id) AS count FROM item_attribute WHERE s_attribute_type IN($in_clause)</p>");
		$result = run_opendb_query("SELECT COUNT(DISTINCT item_id) AS count FROM item_attribute WHERE s_attribute_type IN($in_clause)");
		if($result && mysql_num_rows($result)>0)
		{
    		$found = mysql_fetch_array($result, MYSQL_ASSOC);
			mysql_free_result($result);
			echo("<div class=\"upgrade_success\">".$found['count']." records.</div>");
			
			echo("<h2>NEW Lookup Item Attribute Count</h2>");
			echo("<p class=\"querytitle\">SELECT COUNT(*) AS count FROM item_attribute WHERE s_attribute_type IN($in_clause)</p>");
			$result = run_opendb_query("SELECT COUNT(*) AS count FROM item_attribute WHERE s_attribute_type IN($in_clause)");
			if($result && mysql_num_rows($result)>0)
			{
    			$found = mysql_fetch_array($result, MYSQL_ASSOC);
				mysql_free_result($result);
				echo("<div class=\"upgrade_success\">".$found['count']." records.</div>");
			}
			else
			{
				echo("<div class=\"upgrade_failure\">Count failed.</div>");
			}
		}
		else
		{
			echo("<div class=\"upgrade_failure\">Count failed.</div>");
		}
	}
	else
	{
		echo("<div class=\"upgrade_failure\">Count failed.</div>");
	}
}

/**
* From 0.80-dev11 onwards, we need to add a lookup_attribute_val to the user_address_attribute
* table.
*/
function convert_lookup_user_address_attributes()
{
	// first of all get a list of all s_attribute_type_lookup s_attribute_type's
	$lookup_attribute_type_r = array();
	$results = run_opendb_query("SELECT s_attribute_type FROM s_attribute_type WHERE s_field_type = 'ADDRESS'");
	if($results)
	{
		while($attribute_type_r = mysql_fetch_array($results))
		{
			if(is_lookup_attribute_type($attribute_type_r['s_attribute_type']))
			{
				$lookup_attribute_type_r[] = $attribute_type_r['s_attribute_type'];
			}
		}
		mysql_free_result($results);
	}
	
	$results = run_opendb_query("SELECT ua_sequence_number, s_attribute_type, order_no, attribute_val FROM user_address_attribute_old ORDER BY ua_sequence_number, order_no");
	if($results)
	{
		$sequence_number = NULL;
		while($user_addr_attribute_type_r = mysql_fetch_array($results))
		{
			if($sequence_number == NULL || $sequence_number != $user_addr_attribute_type_r['ua_sequence_number'])
			{
				$sequence_number = $user_addr_attribute_type_r['ua_sequence_number'];
				echo("<div class=\"colortext\">Transferring user address attributes for ua_sequence_number $sequence_number...</div>");
			}
			
			echo("<div class=\"colortext\">Attribute: ".$user_addr_attribute_type_r['s_attribute_type'].".".$user_addr_attribute_type_r['order_no']."</div>");
			
			// is lookup attribute type
			if(in_array($user_addr_attribute_type_r['s_attribute_type'], $lookup_attribute_type_r))
			{
				$lookup_attribute_val_r = trim_explode(' ', $user_addr_attribute_type_r['attribute_val']);
				if(insert_lookup_user_address_attributes($user_addr_attribute_type_r['ua_sequence_number'], $user_addr_attribute_type_r['s_attribute_type'], $user_addr_attribute_type_r['order_no'], $lookup_attribute_val_r))
				{
					echo("<div class=\"upgrade_success\">Lookup Attribute transferred successfully.</div>");
				}
				else
				{
					echo("<div class=\"upgrade_failure\">Lookup Attribute not transferred successfully.</div>");
				}
			}
			else
			{
				if(insert_user_address_attribute($user_addr_attribute_type_r['ua_sequence_number'], $user_addr_attribute_type_r['s_attribute_type'], $user_addr_attribute_type_r['order_no'], trim($user_addr_attribute_type_r['attribute_val'])))
				{
					echo("<div class=\"upgrade_success\">Attribute transferred successfully.</div>");
				}
				else
				{
					echo("<div class=\"upgrade_failure\">Attribute not transferred successfully.</div>");
				}
			}
		}
		mysql_free_result($results);
		
		echo( "<p><div class=\"success\">Done!</div></p>");
	}
}

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_patch_button("patch_before", "1. Execute patch_before.sql", !is_patch_before_executed());
		echo("&nbsp;");
		print_patch_button("address_type_sql", "2. Execute address_type.sql", is_patch_before_executed() && !is_address_type_executed());
		echo("&nbsp;");
		print_patch_button("transfer_email", "3. Transfer Email Addresses", is_patch_before_executed() && is_address_type_executed() && is_exists_old_user_email_column() && !is_user_addresses_transferred());
		echo("&nbsp;");
		print_patch_button("item_attribute_sql", "4. Execute item_attribute.sql", is_patch_before_executed() && is_address_type_executed() && is_user_addresses_transferred() && is_old_attribute_table());
		echo("&nbsp;");
		print_patch_button("convert_lookup_item_attributes", "5. Convert Lookup Item Attributes", is_patch_before_executed() && is_address_type_executed() && !is_lookup_attribute_type_converted() && is_exists_old_attribute_table());
		echo("&nbsp;");
		
		$next_step_no = '6';
		
		// do not show these options for pre dev-11 patching
		if(is_patch_before_executed() && is_address_type_executed() && 
					(!is_exists_dev11_user_address_attribute() || is_exists_old_user_addr_attr_table()))
		{
			echo("&nbsp;");
			print_patch_button("convert_user_addr_attributes1", "6. Convert User Address Attributes Part 1 (0.80-dev11+)", is_patch_before_executed() && is_address_type_executed() && !is_old_attribute_table() && is_lookup_attribute_type_converted() && !is_exists_old_user_addr_attr_table());
			echo("&nbsp;");
			print_patch_button("convert_user_addr_attributes2", "7. Convert User Address Attributes Part 2 (0.80-dev11+)", is_patch_before_executed() && is_address_type_executed() && !is_old_attribute_table() && is_lookup_attribute_type_converted() && is_exists_old_user_addr_attr_table() && !is_user_addresses_transferred());
			echo("&nbsp;");
			print_patch_button("convert_user_addr_attributes3", "8. Convert User Address Attributes Part 3 (0.80-dev11+)", is_patch_before_executed() && is_address_type_executed() && !is_old_attribute_table() && is_lookup_attribute_type_converted() && is_exists_old_user_addr_attr_table() && is_user_addresses_transferred());
			echo("&nbsp;");
			
			$next_step_no = '9';
		}
		
		print_patch_button("patch_after", $next_step_no.". Execute patch_after.sql", is_patch_before_executed() && is_address_type_executed() && is_lookup_attribute_type_converted() && is_user_addresses_transferred() && !is_patch_after_executed());
		
		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.  If you experience problems with Adding/Editing Items, such as 'Invalid Item Type' errors, 
		then it is quite possible that this patch was <b>not</b> completed successfully.</b></p>");
	}
	else 
	{
		if(isset($HTTP_VARS['patch_before'] ))
			exec_patch_sql_file($HTTP_VARS['from'], "patch_before.sql", TRUE);
		else if(isset($HTTP_VARS['address_type_sql']))
			exec_patch_sql_file($HTTP_VARS['from'], "address_type.sql", TRUE);
		else if(isset($HTTP_VARS['transfer_email']))
			transfer_email();
		else if(isset($HTTP_VARS['item_attribute_sql']))
			exec_patch_sql_file($HTTP_VARS['from'], "item_attribute.sql", TRUE);
		else if(isset($HTTP_VARS['convert_lookup_item_attributes']))
		{
			convert_lookup_item_attributes();
			
			// now display the results of the transfer
			check_item_attribute_transfer();
		}
		else if(isset($HTTP_VARS['convert_user_addr_attributes1']))
			exec_patch_sql_file($HTTP_VARS['from'], "user_address_attribute.sql", TRUE);
		else if(isset($HTTP_VARS['convert_user_addr_attributes2']))
			convert_lookup_user_address_attributes();
		else if(isset($HTTP_VARS['convert_user_addr_attributes3']))
			exec_patch_sql_statement("DROP TABLE user_address_attribute_old", TRUE);
		else if(isset($HTTP_VARS['patch_after'] ))
			exec_patch_sql_file($HTTP_VARS['from'], "patch_after.sql", TRUE);
	}
}
?>