<?php
/* ----------------------------------------------------------------------
 * PHP implementation of TPC-W
 * 
 * Copyright (c) 2004, Jan Wieck
 * 
 * Permission to use, copy, modify, and distribute this software and
 * its documentation for any purpose, without fee, and without a
 * written agreement is hereby granted, provided that the above
 * copyright notice and this paragraph and the following two
 * paragraphs appear in all copies.
 * 
 * IN NO EVENT SHALL THE AUTHORS BE LIABLE TO ANY PARTY FOR DIRECT,
 * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES,
 * INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE
 * AND ITS DOCUMENTATION, EVEN IF THE AUTHORS HAVE BEEN ADVISED OF
 * THE POSSIBILITY OF SUCH DAMAGE.
 * 
 * THE AUTHORS SPECIFICALLY DISCLAIM ANY WARRANTIES, INCLUDING, BUT
 * NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
 * FITNESS FOR A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED
 * HEREUNDER IS ON AN "AS IS" BASIS, AND THE AUTHORS HAVE NO
 * OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,
 * ENHANCEMENTS, OR MODIFICATIONS.
 *
 * db_mysql.inc -
 *
 * MySQL specific support functions
 * ----------------------------------------------------------------------
 */


$DB = false;


/* ----
 * db_connect()
 *
 *	Connect to the database
 * ----
 */
function db_connect()
{
	global $DB, $DB_host, $DB_user, $DB_pass, $DB_name;

	if ($DB)
	{
		return;
	}

	// $DB = mysql_connect($DB_host, $DB_user, $DB_pass);
	$DB = mysql_pconnect($DB_host, $DB_user, $DB_pass);
	if (!$DB)
	{
		tpcw_error("Database not available");
	}
	if (!mysql_select_db($DB_name))
	{
		mysql_close($DB);
		tpcw_error("Database not available");
	}
}


/* ----
 * db_start_transaction()
 *
 *  Start a transaction
 * ----
 */
function db_start_transaction()
{
	db_exec_command("begin;");
}


/* ----
 * db_commit_transaction()
 *
 *  Commit the current transaction
 * ----
 */
function db_commit_transaction()
{
	db_exec_command("commit;");
}


/* ----
 * db_rollback_transaction()
 *
 *  Rollback the current transaction
 * ----
 */
function db_rollback_transaction()
{
	db_exec_command("rollback;");
}


/* ----
 * db_disconnect()
 *
 *  Disconnect from the database
 * ----
 */
function db_disconnect()
{
	global $DB;

	// mysql_close($DB);
	// $DB = false;
}


/* ----
 * db_session_close()
 *
 *  Disconnect from the database at session end
 * ----
 */
function db_session_close()
{
	global $DB;

	mysql_close($DB);
	$DB = false;
}


/* ----
 * db_session_gc()
 *
 *  Remove old timed out sessions
 * ----
 */
function db_session_gc()
{
	db_connect();
	db_exec_command("delete from tpcw_session where ts < '" .(time() - 8400) . "';");
	db_session_close();
}


/* ----
 * db_reset()
 *
 *  Eventually rollback and disconnect from the database
 * ----
 */
function db_reset()
{
	global $DB;

	@ mysql_query("rollback;");
	@ mysql_close($DB);
}


/* ----
 * db_exec_command()
 *
 *  Execute an SQL statement without result and check for errors.
 * ----
 */
function db_exec_command($query)
{
	global $DB;

	$res = mysql_query($query);
	if (!$res)
	{
		$err = mysql_error($DB);
		mysql_close($DB);
		tpcw_error("Error: \"" . $query . "\": " . $err);
	}
}


/* ----
 * db_exec_select()
 *
 *  Execute a SELECT statement, check for errors and return the result.
 * ----
 */
function db_exec_select($query)
{
	global $DB;

	$res = mysql_query($query);
	if (!$res)
	{
		$err = mysql_error($DB);
		mysql_close($DB);
		tpcw_error("Error: \"" . $query . "\": " . $err);
	}

	return ($res);
}


/* ----
 * db_numrows()
 *
 *  Return the number of result rows in a DB query result
 * ----
 */
function db_numrows($res)
{
	return (mysql_num_rows($res));
}


/* ----
 * db_fetch_row()
 *
 *  Fetch the next result row from a DB query result
 * ----
 */
function db_fetch_row($res)
{
	return(mysql_fetch_row($res));
}


/* ----
 * db_freeresult()
 *
 *  Free all resources of a DB query result
 * ----
 */
function db_freeresult($res)
{
	return (mysql_freeresult($res));
}


/* ----
 * db_promotional()
 *
 *	Function to generate the HTML for the promotional thumbnail images
 *	at the top of several TPC-W pages. The output is returned as string.
 * ----
 */
function db_promotional($i_id)
{
	$query = "select I2.i_id, I2.i_thumbnail " .
			"from tpcw_item I, tpcw_item I2 " .
			"where I.i_id = '$i_id' " .
			"and (I.i_related1 = I2.i_id " .
			"  or I.i_related2 = I2.i_id " .
			"  or I.i_related3 = I2.i_id " .
			"  or I.i_related4 = I2.i_id " .
			"  or I.i_related5 = I2.i_id)";
	$res = db_exec_select($query);

	if (db_numrows($res) != 5)
	{
		tpcw_error("Query \"$query\" did not return 5 result rows");
	}

	return($res);
}


/* ----
 * db_home_customer_info()
 *
 *	Function to lookup the customer first- and lastname for the
 *	Home web interaction.
 * ----
 */
function db_home_customer_info($c_id)
{
	$query = "select c_fname, c_lname from tpcw_customer " .
			"where c_id = '" . addslashes($c_id) . "'; ";
	$res = db_exec_select($query);
	$row = db_fetch_row($res);
	db_freeresult($res);

	return($row);
}


/* ----
 * db_get_uname()
 *
 *	Function to lookup a customers C_UNAME in order_inquiry.php
 * ----
 */
function db_get_uname($c_id)
{
	$query = "select c_uname from tpcw_customer " .
			"where c_id = '" . addslashes($c_id) . "'; ";
	$res = db_exec_select($query);
	$row = db_fetch_row($res);
	db_freeresult($res);

	return($row);
}


/* ----
 * db_get_passwd()
 *
 *	Function to lookup a customers C_PASSWD in order_display.php
 * ----
 */
function db_get_passwd($c_uname)
{
	$query = "select c_passwd from tpcw_customer " .
			"where c_uname = '" . addslashes($c_uname) . "'; ";
	$res = db_exec_select($query);
	$row = db_fetch_row($res);
	db_freeresult($res);

	return($row);
}


/* ----
 * db_search_result()
 *
 *	Selection of the SearchResult item list according 2.11
 * ----
 */
function db_search_result($search_type, $search_string)
{
	if (strcmp($search_type, "Author") == 0)
	{
		$query = "select i_id, i_title, a_fname, a_lname " .
			"from tpcw_item, tpcw_author " .
			"where i_a_id = a_id " .
			"and a_lname like '" . addslashes($search_string) . "%' " .
			"order by i_title limit 50; ";
	}
	elseif (strcmp($search_type, "Title") == 0)
	{
		$query = "select i_id, i_title, a_fname, a_lname " .
			"from tpcw_item, tpcw_author " .
			"where i_a_id = a_id " .
			"and (i_title like '" . addslashes($search_string) . "%' " .
			"  or i_title like '% " . addslashes($search_string) . "%') " .
			"order by i_title limit 50; ";
	}
	elseif (strcmp($search_type, "Subject") == 0)
	{
		$query = "select i_id, i_title, a_fname, a_lname " .
			"from tpcw_item, tpcw_author " .
			"where i_a_id = a_id " .
			"and i_subject = '" . addslashes($search_string) . "' " .
			"order by i_title limit 50; ";
	}
	else
	{
		tpcw_error("Unsupported search type " . $search_type);
	}

	return (db_exec_select($query));
}


/* ----
 * db_new_products()
 *
 *	Selection of the NewProduct item list according 2.12
 * ----
 */
function db_new_products($subject)
{
	$query = "select i_id, i_title, a_fname, a_lname, i_pub_date " .
		"from tpcw_item, tpcw_author " .
		"where i_a_id = a_id " .
		"and i_subject = '" . addslashes($subject) . "' " .
		"order by i_pub_date desc, i_title asc limit 50;";
	
	return (db_exec_select($query));
}


/* ----
 * db_best_sellers()
 *
 *	Selection of the BestSellers item list according 2.13
 * ----
 */
function db_best_sellers($subject)
{
	/*
	 * Description from TPC-W 2.13:
	 *
	 * Of the entire set of (I_ID, I_TITLE) pairs for items on the 
	 * selected subject, the first 50 pairs (or less if the entire
	 * set contains less than 50 pairs) are obtained. The following
	 * search predicate is used (where <string> is the content of
	 * SUBJECT_STRING):
	 *
	 * I_SUBJECT =  <string>  for the order lines of the 3,333 most
	 * recent orders (where O_ID = OL_O_ID) based on O_DATE and 
	 * sorted by descending sum(OL_QTY) grouped on OL_I_ID
	 */

	/*
	 * Create a temp table containing the O_IDs of the 3333 most
	 * recent orders.
	 */
/*
	$query = "drop temporary table if exists last_3333; ";
	db_exec_command($query);

	$query = "create temporary table last_3333 (o_id bigint, key (o_id)); ";
	db_exec_command($query);

	$query = "insert into last_3333 " .
			"select o_id from tpcw_orders order by o_date desc limit 3333; ";
	db_exec_command($query);
*/

	/*
	 * select the list of 50 best sellers within this set of orders
	 * that have the requested subject
	 */
/*
	$query = "select i_id, i_title, a_fname, a_lname " .
			"from tpcw_order_line join tpcw_item on ol_i_id = i_id " .
			"    join tpcw_author on i_a_id = a_id " .
			"where ol_o_id in (select o_id from last_3333) " .
			"and i_subject = '" . addslashes($subject) . "' " .
			"group by i_id, i_title, a_fname, a_lname " .
			"order by sum(ol_qty) desc limit 50; ";
*/
	$query = "select i_id, i_title, a_fname, a_lname, sum(ol_qty) " .
			"from tpcw_order_line, tpcw_item, tpcw_author, " .
			"    (select o_id from tpcw_orders " .
			"        order by o_date desc limit 3333) as O " .
			"where ol_o_id = o_id " .
			"and i_subject = '" . addslashes($subject) . "' " .
			"and ol_i_id = i_id and i_a_id = a_id " .
			"group by i_id, i_title, a_fname, a_lname " .
			"order by 5 desc limit 50;";

	$res = db_exec_select($query);

/*
	$query = "drop temporary table last_3333; ";
	db_exec_command($query);
*/

	return ($res);
}


/* ----
 * db_product_detail()
 *
 *	Select the item information for the Product Detail page
 * ----
 */
function db_product_detail($i_id)
{
	$query = "select i_title, a_fname, a_lname, " .
			"    i_pub_date, i_publisher, i_subject, i_desc, " .
			"    i_image, i_cost, i_srp, i_avail, i_isbn, " .
			"    i_page, i_backing, i_dimension, " .
			"    i_srp - i_cost as you_save " .
			"from tpcw_item join tpcw_author on i_a_id = a_id " .
			"where i_id = '" . addslashes($i_id) . "';";
	$res = db_exec_select($query);
	$row = db_fetch_row($res);
	db_freeresult($res);

	return($row);
}


/* ----
 * db_admin_request()
 *
 *	Select the item information for the Admin Request page
 * ----
 */
function db_admin_request($i_id)
{
	$query = "select i_srp, i_cost, i_title, i_image, i_thumbnail, " .
			"    a_fname, a_lname " .
			"from tpcw_item join tpcw_author on i_a_id = a_id " .
			"where i_id = '" . addslashes($i_id) . "';";
	$res = db_exec_select($query);
	$row = db_fetch_row($res);
	db_freeresult($res);

	return($row);
}


/* ----
 * db_update_item()
 *
 * Implementation of TPC-W 2.16.3.3, Admin Confirm Web Interaction
 * ----
 */
function db_update_item ($i_id, $new_cost, $new_image, $new_thumb)
{
	global	$num_items;

	/*
	 * Of all the orders sorted by descending O_DATE, obtain the 
	 * set of the first 10,000.
	 */
/*
	$query = "drop temporary table if exists last_10000; ";
	db_exec_command($query);

	$query = "create temporary table last_10000 (o_id bigint, key (o_id)); ";
	db_exec_command($query);

	$query = "insert into last_10000 " .
			"select o_id from tpcw_orders order by o_date desc limit 10000; ";
	db_exec_command($query);
*/


	/*
	 * From all orders that include the targeted item in the above set,
	 * obtain the set of unique customers that placed these orders.
	 */
/*
	$query = "drop temporary table if exists last_buyers; ";
	db_exec_command($query);

	$query = "create temporary table last_buyers (o_c_id bigint, key (o_c_id)); ";
	db_exec_command($query);

	$query = "insert into last_buyers " .
			"select o_c_id from tpcw_order_line " .
			"    join tpcw_orders on ol_o_id = o_id " .
			"where ol_o_id in (select o_id from last_10000) " .
			"and ol_i_id = '" . addslashes($i_id) . "' " .
			"group by o_c_id; ";
	db_exec_command($query);
*/


	/*
	 * From all customers in the above set, obtain the list 
	 * of unique items ordered by these customers within the
	 * above set of 10,000 orders, and sort these items by 
	 * descending aggregated quantity (i.e., sum(OL_QTY) for 
	 * each unique OL_I_ID).
	 */
/*
	$query = "select ol_i_id, sum(ol_qty) from tpcw_order_line " .
			"where ol_o_id in (select o_id from tpcw_orders	" .
			"        where o_id in (select o_id from last_10000) " .
			"        and o_c_id in (select o_c_id from last_buyers)) " .
			"and ol_i_id <> '" . addslashes($i_id) . "' " .
			"group by ol_i_id " .
			"order by sum(ol_qty) desc, ol_i_id limit 5; ";
*/
	$query = "select ol_i_id, sum(ol_qty) from tpcw_order_line, " .
			"    (select o_id as o_id1 from tpcw_orders, " .
			"        (select o_id as o_id2 from tpcw_orders " .
			"            order by o_date desc limit 10000 " .
			"        ) as o2, " .
			"        (select o_c_id as o_c_id1 from tpcw_order_line, " .
			"            (select o_id, o_c_id from tpcw_orders " .
			"                order by o_date desc limit 10000 " .
			"            ) as o3 " .
			"            where ol_o_id = o3.o_id " .
			"            and ol_i_id = '" . addslashes($i_id) . "' " .
			"            group by 1 " .
			"        ) as c1 " .
			"        where o_id = o_id2 " .
			"        and o_c_id = c1.o_c_id1 " .
			"    ) as o1 " .
			"where ol_o_id = o1.o_id1 " .
			"and ol_i_id <> '" . addslashes($i_id) . "' " .
			"group by 1 " .
			"order by 2 desc, 1 asc limit 5; ";
	$res = db_exec_select($query);

	$nrelated = db_numrows($res);
	if ($nrelated == 0)
	{
		/*
		 * If the sorted list above contains no item, or only I_ID: 
		 *
		 * Then, create a list of 5 items by incrementing the targeted
		 * I_ID by steps of 7, such that (I_ID1 = I_ID + 7),
		 * (I_ID2 = I_ID + 14) (I_ID3 = I_ID + 21) ((I_ID4 = I_ID + 28)
		 * and (I_ID5 = I_ID + 35), wrapping back to the beginning of
		 * the I_ID range if the end is reached.
		 */
		$i_id1 = $i_id + 7;
		if ($i_id1 > $num_items) 
		{
			$i_id1 = $i_id1 % 10;
		}

		$i_id2 = $i_id1 + 7;
		if ($i_id2 > $num_items) 
		{
			$i_id2 = $i_id2 % 10;
		}

		$i_id3 = $i_id2 + 7;
		if ($i_id3 > $num_items) 
		{
			$i_id3 = $i_id3 % 10;
		}

		$i_id4 = $i_id3 + 7;
		if ($i_id4 > $num_items) 
		{
			$i_id4 = $i_id4 % 10;
		}

		$i_id5 = $i_id4 + 7;
		if ($i_id5 > $num_items) 
		{
			$i_id5 = $i_id5 % 10;
		}
	}
	else
	{
		if ($nrelated == 5)
		{
			/*
			 * If the sorted list above consists of 5 or more items,
			 * excluding I_ID:
			 */
			$row = db_fetch_row($res);
			$i_id1 = $row[0];
			$row = db_fetch_row($res);
			$i_id2 = $row[0];
			$row = db_fetch_row($res);
			$i_id3 = $row[0];
			$row = db_fetch_row($res);
			$i_id4 = $row[0];
			$row = db_fetch_row($res);
			$i_id5 = $row[0];
		}
		else
		{
			/*
			 * If the sorted list above contains between 1 and 4 items,
			 * excluding I_ID: ? Then, increment I_ID from the last item
			 * in the list until 5 items are obtained. For example, if
			 * the list contains only 3 items, then (I_ID4 = I_ID3 + 1)
			 * and (I_ID5 = I_ID3 + 2), wrapping back to the beginning
			 * of the I_ID range if the end is reached and skipping
			 * duplicate items.
			 */
			$row = db_fetch_row($res);
			$i_id1 = $row[0];

			if ($nrelated >= 2)
			{
				$row = db_fetch_row($res);
				$i_id2 = $row[0];
			}
			else
			{
				$i_id2 = $i_id1 + 1;
				while ($i_id2 > $num_items || $i_id2 == $i_id ||
						$i_id2 == $i_id1)
				{
					if ($i_id2 >= $num_items)
						$i_id2 = 1;
					else
						$i_id2++;
				}
			}

			if ($nrelated >= 3)
			{
				$row = db_fetch_row($res);
				$i_id3 = $row[0];
			}
			else
			{
				$i_id3 = $i_id2 + 1;
				while ($i_id3 > $num_items || $i_id3 == $i_id ||
						$i_id3 == $i_id1 || $i_id3 == $i_id2)
				{
					if ($i_id3 >= $num_items)
						$i_id3 = 1;
					else
						$i_id3++;
				}
			}

			if ($nrelated >= 4)
			{
				$row = db_fetch_row($res);
				$i_id4 = $row[0];
			}
			else
			{
				$i_id4 = $i_id3 + 1;
				while ($i_id4 > $num_items || $i_id4 == $i_id ||
						$i_id4 == $i_id1 || $i_id4 == $i_id2 ||
						$i_id4 == $i_id3)
				{
					if ($i_id4 >= $num_items)
						$i_id4 = 1;
					else
						$i_id4++;
				}
			}

			$i_id5 = $i_id4 + 1;
			while ($i_id5 > $num_items || $i_id5 == $i_id ||
					$i_id5 == $i_id1 || $i_id5 == $i_id2 ||
					$i_id5 == $i_id3 || $i_id5 == $i_id4)
			{
				if ($i_id5 >= $num_items)
					$i_id5 = 1;
				else
					$i_id5++;
			}
		}
	}

	db_freeresult($res);

	/*
	 * Update the ITEM with all the new information
	 */
	$query = "update tpcw_item set " .
			"i_cost      = " . $new_cost . ", " .
			"i_image     = " . $new_image . ", " .
			"i_thumbnail = " . $new_thumb . ", " .
			"i_related1  = '" . $i_id1 . "', " .
			"i_related2  = '" . $i_id2 . "', " .
			"i_related3  = '" . $i_id3 . "', " .
			"i_related4  = '" . $i_id4 . "', " .
			"i_related5  = '" . $i_id5 . "' " .
			"where i_id = '" . addslashes($i_id) . "'; ";
	db_exec_command($query);
	
	/*
	 * Select the data required to display the result page
	 */
	$query = "select i_title, a_fname, a_lname, " .
			"    i_pub_date, i_publisher, i_desc, i_image, " .
			"    i_thumbnail, i_cost, i_srp, i_avail, i_isbn, " .
			"    i_page, i_backing, i_dimension, " .
			"    i_srp - i_cost as you_save " .
			"from tpcw_item join tpcw_author on i_a_id = a_id " .
			"where i_id = '" . addslashes($i_id) . "'; ";
	$res = db_exec_select($query);
	$row = db_fetch_row($res);
	db_freeresult($res);

	/*
	 * Cleanup
	 */
/*
	$query = "drop temporary table last_10000; ";
	db_exec_command($query);

	$query = "drop temporary table last_buyers; ";
	db_exec_command($query);
*/

	return $row;
}


/* ----
 * db_order_display_hdr()
 *
 *	Select the order head information for the Order Display page
 * ----
 */
function db_order_display_hdr($c_uname)
{
	$query = "select o_id, c_fname, c_lname, c_phone, c_email, " .
			"    o_date, o_sub_total, o_tax, o_total, " .
			"    o_ship_type, o_ship_date, o_status, " .
			"    cx_type, cx_auth_id, " .
			"    A1.addr_street1, A1.addr_street2, A1.addr_city, " .
			"    A1.addr_state, A1.addr_zip, C1.co_name, " .
			"    A2.addr_street1, A2.addr_street2, A2.addr_city, " .
			"    A2.addr_state, A2.addr_zip, C2.co_name " .
			"from tpcw_customer left join tpcw_orders on c_id = o_c_id " .
			"    left join tpcw_cc_xacts on o_id = cx_o_id " .
			"    left join tpcw_address as A1 on o_bill_addr_id = A1.addr_id " .
			"    left join tpcw_country as C1 on A1.addr_co_id = C1.co_id " .
			"    left join tpcw_address as A2 on o_ship_addr_id = A2.addr_id " .
			"    left join tpcw_country as C2 on A2.addr_co_id = C2.co_id " .
			"where c_uname = '" . addslashes($c_uname) . "' " .
			"order by o_date desc, o_id desc limit 1;";
	$res = db_exec_select($query);
	$row = db_fetch_row($res);
	db_freeresult($res);

	return($row);
}


/* ----
 * db_order_display_lines()
 *
 *	Select the order lines for the Order Display page
 * ----
 */
function db_order_display_lines($o_id)
{
	$query = "select ol_i_id, i_title, i_publisher, " .
			"    i_cost, ol_qty, ol_discount, ol_comments " .
			"from tpcw_order_line join tpcw_item on ol_i_id = i_id " .
			"where ol_o_id = '" . $o_id . "' " .
			"order by ol_id;";
	return(db_exec_select($query));

}


/* ----
 * db_shop_cart_get_customer()
 *
 *  Get customer information for the Shopping Cart page
 * ----
 */
function db_shop_cart_get_customer($c_id)
{
	$query = "select c_fname, c_lname, c_discount from tpcw_customer " .
			"where c_id = '" . addslashes($c_id) . "'; ";
	$res = db_exec_select($query);
	$row = db_fetch_row($res);
	db_freeresult($res);

	return($row);
}


/* ----
 * db_shop_cart_get_item()
 *
 *  Get item information for the Shopping Cart page
 * ----
 */
function db_shop_cart_get_item($i_id)
{
	$query = "select i_cost, i_srp, i_title, i_backing from tpcw_item " .
			"where i_id = '" . addslashes($i_id) . "'; ";
	$res = db_exec_select($query);
	$row = db_fetch_row($res);
	db_freeresult($res);

	return($row);
}


/* ----
 * db_shop_cart_get_promitem()
 *
 *  Get promotional item information for the Shopping Cart page
 * ----
 */
function db_shop_cart_get_promitem($i_id)
{
	$query = "select IR.i_id, IR.i_cost, IR.i_srp, IR.i_title, IR.i_backing " .
			"from tpcw_item I, tpcw_item IR " .
			"where I.i_id = '" . addslashes($i_id) . "' " .
			"and I.i_related1 = IR.i_id; ";
	$res = db_exec_select($query);
	$row = db_fetch_row($res);
	db_freeresult($res);

	return($row);
}


/* ----
 * db_store_address()
 *
 *  Retrieve an address record, or store a new one if not found.
 * ----
 */
function db_store_address ($street1, $street2, $city, $state, $zip, $country)
{
	global	$DB;

	$query = "select co_id from tpcw_country " .
			"where co_name = '" . addslashes($country) . "'; ";
	$res = db_exec_select($query);
	$row = db_fetch_row($res);
	db_freeresult($res);
	if (!$row)
	{
		db_rollback_transaction();
		db_disconnect();

		tpcw_error("Unknown Country Name \"" . $country . "\"");
	}
	$co_id = $row[0];

	$query = "select addr_id from tpcw_address " .
			"where addr_street1  = '" . addslashes($street1) . "' " .
			"and   addr_street2  = '" . addslashes($street2) . "' " .
			"and   addr_city     = '" . addslashes($city) . "' " .
			"and   addr_state    = '" . addslashes($state) . "' " .
			"and   addr_zip      = '" . addslashes($zip) . "' " .
			"and   addr_co_id    = '" . addslashes($co_id) . "' " .
			"for update; ";
	$res = db_exec_select($query);
	$row = db_fetch_row($res);
	db_freeresult($res);
	if ($row)
	{
		return(array($row[0], $co_id));
	}

	$query = "insert into tpcw_address (" .
			"    addr_street1, addr_street2, addr_city, " .
			"    addr_state, addr_zip, addr_co_id " .
			") values (" .
			"'" . addslashes($street1) . "', " .
			"'" . addslashes($street2) . "', " .
			"'" . addslashes($city) . "', " .
			"'" . addslashes($state) . "', " .
			"'" . addslashes($zip) . "', " .
			"'" . addslashes($co_id) . "'); ";
	db_exec_command($query);

	$addr_id = mysql_insert_id($DB);
	if ($addr_id == 0)
	{
		db_rollback_transaction();
		db_disconnect();

		tpcw_error("Address Insert failed - no addr_id returned");
	}

	return (array($addr_id, $co_id));
}


/* ----
 * db_buy_get_customer()
 *
 *  Select customer information for the BuyRequest page
 * ----
 */
function db_buy_get_customer ($uname)
{
	$query = "select c_id, c_passwd, " .
			"c_fname, c_lname, c_addr_id, " .
			"addr_street1, addr_street2, addr_city, " .
			"addr_state, addr_zip, co_id, co_name, " .
			"c_phone, c_email, c_birthdate, " .
			"c_discount, c_data " .
			"from tpcw_customer " .
			"    join tpcw_address on addr_id = c_addr_id " .
			"    join tpcw_country on addr_co_id = co_id " .
			"where c_uname = '" . addslashes($uname) . "' " .
			"for update; ";
	$res = db_exec_select($query);
	$row = db_fetch_row($res);
	db_freeresult($res);
	if (!$row)
	{
		db_rollback_transaction();
		db_disconnect();
		tpcw_error("Invalid username or password!");
	}
	$c_id = $row[0];

	$query = "update tpcw_customer " .
			"set c_login = CURRENT_TIMESTAMP, " .
			"    c_expiration = CURRENT_TIMESTAMP + '2:00:00' " .
			"where c_id = '" . $c_id . "'; ";
	db_exec_command($query);

	return ($row);
}


/* ----
 * db_buy_new_customer()
 *
 *  Create a new customer for the BuyRequest page
 * ----
 */
function db_buy_new_customer ()
{
	global	$DB, $vars,
		$c_id, $c_uname, $c_passwd, $c_fname, $c_lname, $c_addr_id,
		$addr_street1, $addr_street2, $addr_city,
		$addr_state, $addr_zip, $co_id, $co_name,
		$c_phone, $c_email, $c_birthdate, $c_discount, $c_data;

	$c_fname		= $vars['FNAME'];
	$c_lname		= $vars['LNAME'];
	$c_fname		= $vars['FNAME'];
	$addr_street1	= $vars['STREET1'];
	$addr_street2	= $vars['STREET2'];
	$addr_city		= $vars['CITY'];
	$addr_state		= $vars['STATE'];
	$addr_zip		= $vars['ZIP'];
	$co_name		= $vars['COUNTRY'];
	$c_phone		= $vars['PHONE'];
	$c_email		= $vars['EMAIL'];
	$c_birthdate	= date("Y-m-d");
	$c_discount		= sprintf("%.2f", ((double)tpcw_rand(0, 50)) / 100.0);
	$c_data			= $vars['DATA'];

	$addr_info = db_store_address($addr_street1, $addr_street2,
				$addr_city, $addr_state, $addr_zip, $co_name);
	$c_addr_id = $addr_info[0];
	$co_id     = $addr_info[1];

	$query = "insert into tpcw_customer (" .
			"    c_uname, c_passwd, c_fname, c_lname, c_addr_id, " .
			"    c_phone, c_email, c_since, c_last_visit, " .
			"    c_login, c_expiration, c_discount, " .
			"    c_balance, c_ytd_payment, " .
			"    c_birthdate, c_data " .
			") values (" .
			"'TMP_" . posix_getpid() . "', " .
			"'tmp_" . posix_getpid() . "', " .
			"'" . addslashes($c_fname) . "', " .
			"'" . addslashes($c_lname) . "', " .
			"'" . addslashes($c_addr_id) . "', " .
			"'" . addslashes($c_phone) . "', " .
			"'" . addslashes($c_email) . "', " .
			"CURRENT_DATE, CURRENT_DATE, " .
			"CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + '2:00:00', " .
			"'" . addslashes($c_discount) . "', " .
			"'0.00', '0.00', " .
			"'" . addslashes($c_birthdate) . "', " .
			"'" . addslashes($c_data) . "'); ";
	db_exec_command($query);
	$c_id		= mysql_insert_id($DB);
	if ($c_id == 0)
	{
		db_rollback_transaction();
		db_disconnect();

		tpcw_error("Customer Insert failed - no c_id returned");
	}
	$c_uname	= tpcw_DigSyl($c_id, 0);
	$c_passwd	= strtolower($c_uname);

	$query = "update tpcw_customer " .
			"set c_uname  = '" . addslashes($c_uname) . "', " .
			"    c_passwd = '" . addslashes($c_passwd) . "' " .
			"where c_id = '" . $c_id . "'; ";
	db_exec_command($query);
}


/* ----
 * db_new_order()
 *
 *  Create a new ORDER
 * ----
 */
function db_new_order($o_c_id, $o_sub_total, $o_tax, $o_total,
		$o_ship_type, $o_ship_date, $o_bill_addr_id, $o_ship_addr_id)
{
	global	$DB;

	$query = "insert into tpcw_orders (" .
			"    o_c_id, o_date, o_sub_total, " .
			"    o_tax, o_total, o_ship_type, o_ship_date, " .
			"    o_bill_addr_id, o_ship_addr_id, o_status " .
			") values (" .
			"'" . addslashes($o_c_id) . "', " .
			"CURRENT_DATE, " .
			"'" . addslashes($o_sub_total) . "', " .
			"'" . addslashes($o_tax) . "', " .
			"'" . addslashes($o_total) . "', " .
			"'" . addslashes($o_ship_type) . "', " .
			"'" . addslashes($o_ship_date) . "', " .
			"'" . addslashes($o_bill_addr_id) . "', " .
			"'" . addslashes($o_ship_addr_id) . "', " .
			"'PENDING'); ";
	db_exec_command($query);

	$o_id = mysql_insert_id($DB);
	if ($o_id == 0)
	{
		db_rollback_transaction();
		db_disconnect();

		tpcw_error("Order Insert failed - no o_id returned");
	}

	return ($o_id);
}


/* ----
 * db_store_order_line()
 *
 *  Add an order line to the order. This includes adjusting
 *  I_STOCK in the ordered item.
 * ----
 */
function db_store_order_line($ol_id, $ol_o_id, $ol_i_id,
		$ol_qty, $ol_discount, $ol_comments)
{
	$query = "select i_stock from tpcw_item " .
			"where i_id = '" . addslashes($ol_i_id) . "'; ";
	$res = db_exec_select($query);
	$row = db_fetch_row($res);
	db_freeresult($res);
	if (!$row)
	{
		db_rollback_transaction();
		db_disconnect();

		tpcw_error("Item " . $ol_i_id . " not found");
	}
	$i_stock = $row[0];
	
	$query = "insert into tpcw_order_line (" .
			"    ol_id, ol_o_id, ol_i_id, ol_qty, ol_discount, ol_comments " .
			") values (" .
			"'" . addslashes($ol_id) . "', " .
			"'" . addslashes($ol_o_id) . "', " .
			"'" . addslashes($ol_i_id) . "', " .
			"'" . addslashes($ol_qty) . "', " .
			"'" . addslashes($ol_discount) . "', " .
			"'" . addslashes($ol_comments) . "'); ";
	db_exec_command($query);

	if ($i_stock >= $ol_qty + 10)
	{
		$i_stock -= $ol_qty;
	}
	else
	{
		$i_stock = $i_stock - $ol_qty + 21;
	}
	$query = "update tpcw_item set i_stock = '" . $i_stock . "' " .
			"where i_id = '" . addslashes($ol_i_id) . "'; ";
	db_exec_command($query);
}


/* ----
 * db_store_cc_xact()
 *
 *  Store Credit Card Authorization information
 * ----
 */
function db_store_cc_xact($cx_o_id, $cx_type, $cx_cc_num, $cx_cc_name,
		$cx_expiration, $cx_auth_id, $cx_xact_amt, $cx_xact_date, $cx_co_id)
{
	$query = "insert into tpcw_cc_xacts (" .
			"    cx_o_id, cx_type, cx_cc_num, cx_cc_name, " .
			"    cx_expiry, cx_auth_id, cx_xact_amt, cx_xact_date, " .
			"    cx_co_id" .
			") values (" .
			"'" . addslashes($cx_o_id) . "', " .
			"'" . addslashes($cx_type) . "', " .
			"'" . addslashes($cx_cc_num) . "', " .
			"'" . addslashes($cx_cc_name) . "', " .
			"'" . addslashes($cx_expiration) . "', " .
			"'" . addslashes($cx_auth_id) . "', " .
			"'" . addslashes($cx_xact_amt) . "', " .
			"'" . addslashes($cx_xact_date) . "', " .
			"'" . addslashes($cx_co_id) . "'); ";
	db_exec_command($query);
}


?>