using System;
using System.Data.SqlClient;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;

namespace PictureService
{
	/// <summary>
	/// Summary description for Service1.
	/// </summary>

    [WebService(Namespace = "http://harbormist.com/",
       Description = "A web service which randomly selects a " +
       "background image URL from a database (dB=Driya_Kevin).  " +
       "[Programmers: Driya Amandita, Kevin Jenkins.]")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
	public class PictureService : System.Web.Services.WebService
	{

		public PictureService()
		{
			InitializeComponent(); // required
		}

		private System.Data.SqlClient.SqlConnection sqlConnectionPicDB;


		#region Component Designer generated code
		
		//Required by the Web Services Designer 
		private IContainer components = null;
				
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			this.sqlConnectionPicDB = new System.Data.SqlClient.SqlConnection();
			// 
			// sqlConnectionPicDB
			// 
            this.sqlConnectionPicDB.ConnectionString = 
                "workstation id=TABBY;packet size=4096;" +
                "user id=********;" +
                "data source=\"sql2k502.discountasp.net\";" +
                "persist security info=True;" +
                "initial catalog=SQL2005_229378_harbor;" +
                "password=********";

		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if(disposing && components != null)
			{
				components.Dispose();
			}
			base.Dispose(disposing);		
		}
		
		#endregion


		private bool picIDExists(int id)
		{
			try
			{
				string query = "select keyword from WebBackgroundImages where " +
                    "pictureid = " + id.ToString();
				this.sqlConnectionPicDB.Open();
				SqlCommand mycommand = 
					new SqlCommand(query, this.sqlConnectionPicDB);
				string keywd = (string) mycommand.ExecuteScalar();
				this.sqlConnectionPicDB.Close();
				if (keywd==null) return false;
				keywd=keywd.Trim();
				if (keywd.Equals("")) return false;
				return true;
			}
			catch 
			{
				return false;
			}
		}


		private int getPicCount()
		{
			try 
			{
				string query = "select count(pictureid) from WebBackgroundImages";
				this.sqlConnectionPicDB.Open();
				SqlCommand mycommand = new SqlCommand(query, 
                    this.sqlConnectionPicDB);
				int count = (int) mycommand.ExecuteScalar();
				this.sqlConnectionPicDB.Close();
				return count;
			} 
			catch 
			{
				return 0;
			}
		}


		[WebMethod(Description="Returns the next available PicID " +
            "for use of a new entry")]
		public int generatePicID()
		{
			int id=0;
			do 
			{
				id++;
			} while (this.picIDExists(id));				
			return id;
		}

		/// <summary>
		/// Gets a random picture, from any keyword.
		/// </summary>
		/// <returns></returns>
		[WebMethod(Description="Gets a random image URL from the " +
            "server, of any keyword")]
		public string GetPicture()
		{
			string image=null;
			int max = this.getPicCount();
			Random r = new Random();
			int id=r.Next(max);
			do
			{
				id = r.Next(max);
			} while (!this.picIDExists(id)); // gets a random id that exists
                                             // in the server
			try
			{
				string query = "select url from WebBackgroundImages " + 
                    "where pictureid =" + id.ToString();
				this.sqlConnectionPicDB.Open();
				SqlCommand mycommand = 
					new SqlCommand(query, this.sqlConnectionPicDB);
				image = (string) mycommand.ExecuteScalar();
			}
			catch
			{

			} 
			finally
			{
				this.sqlConnectionPicDB.Close();
			}
			return image;
		}


		[WebMethod(Description="Takes in a keyword less than 10 characters " +
            "and no escapable characters and returns an image url of the " +
            "keyword if such exists or null if not")]	
		public string GetKeywordPicture(string keyword)
		{
			string keywd, image=null;
			DataTable datatable = new DataTable();

			// escapes characters for sql injection
			if (keyword.Length>10) 
			{
				keywd = keyword.Substring(0,10);
			} else keywd = keyword;
			keywd.Replace("'", "' '");

			// get the desired pictures to a data table
			string query = "select url from WebBackgroundImages where keyword = '" +
                keywd + "'";
			try 
			{
				this.sqlConnectionPicDB.Open();
				SqlDataAdapter adaptsql = new SqlDataAdapter(query,
                    this.sqlConnectionPicDB);
				adaptsql.Fill(datatable);
				adaptsql.Dispose();				
			} 
			catch 
			{
			}
			finally 
			{
				this.sqlConnectionPicDB.Close();
			}

			// pick one of them at random
			Random r = new Random();
			int index = r.Next(datatable.Rows.Count);
			int i=0;
			foreach (DataRow drow in datatable.Rows)
			{
				if (i==index)
				{
					image = (string) drow["url"];
					return image;
				} 
				else 
				{
					i++;
				}
			}
			return null;
		}


	} // end class
} // end namespace