using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace dataClasses
{
	/// <summary>
	/// Summary description for database.
	/// </summary>
	public class dataFunctions
	{
		
		#region Local Variable Initialization
		//These are the local variables used throughout the class
		//Most of these are the vars used for the accessor methods
		//In region "Getter/Setter Things"
		//Each on is a field for the SP

		private string _address_line1; 
		private string _address_line2;
		private string _address_line3;
		private string _city;
		private int _state_id;
		private int _postal_code;
		private string _country;
		private int _prefix_id;
		private string _first_name;
		private string _last_name;
		private string _home_phone;
		private string _cell_phone;
		private string _work_phone;
		private string _fax;
		private string _email;
		private DateTime _dob;
		private int _address_id;
		private int _person_id;
		
		//Used to return results for each IUD method
		private int results;
		#endregion
		
		#region Getter/Setter Things


		//All of these are pretty much self explanatory.
		//These are all accessible by using, for example
		// dataClasses.dataFunctions df = new dataClasses.dataFunctions();
		// df.Address_ID = "whatever";

		public int Address_ID
		{
			get { return _address_id; }
			set { _address_id = value; }
		}

		public int Person_ID
		{
			get { return _person_id; }
			set { _person_id = value; }
		}
		
		public string Address_Line1
		{
			get { return _address_line1; }
			set { _address_line1 = value; }
		}

		public string Address_Line2
		{
			get { return _address_line2; }
			set { _address_line2 = value; }
		}

		public string Address_Line3
		{
			get { return _address_line3; }
			set { _address_line3 = value; }
		}

		public string City
		{
			get { return _city; }
			set { _city = value; }
		}

		public int State_ID
		{
			get { return _state_id; }
			set { _state_id = value; }
		}

		public int Postal_Code
		{
			get { return _postal_code; }
			set { _postal_code = value; }
		}

		public string Country
		{
			get { return _country; }
			set { _country = value; }
		}

		public int Prefix_ID
		{
			get { return _prefix_id; }
			set { _prefix_id = value; }
		}

		public string First_Name
		{
			get { return _first_name; }
			set { _first_name = value; }
		}

		public string Last_Name
		{
			get { return _last_name; }
			set { _last_name = value; }
		}

		public string Home_Phone
		{
			get { return _home_phone; }
			set { _home_phone = value; }
		}

		public string Cell_Phone
		{
			get { return _cell_phone; }
			set { _cell_phone = value; }
		}

		public string Work_Phone
		{
			get { return _work_phone; }
			set { _work_phone = value; }
		}

		public string Fax
		{
			get { return _fax; }
			set { _fax = value; }
		}

		public string Email
		{
			get { return _email; }
			set { _email = value; }
		}

		public DateTime DOB
		{
			get { return _dob; }
			set { _dob = value; }
		}

		#endregion


		#region Constructor Logic
		public dataFunctions()
		{
			//Constructor. Set everything to nothing. Har!

			_address_line1 = "";
			_address_line2 = "";
			_address_line3 = "";
			_city = "";
			_state_id = 0;
			_postal_code = 0;
			_country = "";
			_prefix_id = 0;
			_first_name = "";
			_last_name = "";
			_home_phone = "";
			_cell_phone = "";
			_work_phone = "";
			_fax = "";
			_email = "";
			_dob = System.DateTime.Now; //I just set something safe in here.
			_address_id = 0;
			_person_id = 0;


		}
		#endregion



		#region InsertCommand
		public int dbInsert()
		{
			
			//Create generic connection hooplah

			//Get the connection string from the web.config file
			//For easy portability
			SqlConnection cnInsert = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);

			SqlCommand cmInsert = new SqlCommand("sp_IUD", cnInsert);
			
			cmInsert.Connection = cnInsert;
			cmInsert.CommandType = CommandType.StoredProcedure;

			#region Parameters
			
			//All the massive amounts of parameters to input
			//The IUD values are hardcoded. 

			//0 -- Insert
			//1 -- Update
			//2 -- Delete

			cmInsert.Parameters.Add("@IUD", SqlDbType.Int);
			cmInsert.Parameters["@IUD"].Value = 0;

			cmInsert.Parameters.Add("@ADDRESS_LINE1", SqlDbType.Char);
			cmInsert.Parameters["@ADDRESS_LINE1"].Value = _address_line1;

			cmInsert.Parameters.Add("@ADDRESS_LINE2", SqlDbType.Char);
			cmInsert.Parameters["@ADDRESS_LINE2"].Value = _address_line2;

			cmInsert.Parameters.Add("@ADDRESS_LINE3", SqlDbType.Char);
			cmInsert.Parameters["@ADDRESS_LINE3"].Value = _address_line3;

			cmInsert.Parameters.Add("@CITY", SqlDbType.Char);
			cmInsert.Parameters["@CITY"].Value = _city;

			cmInsert.Parameters.Add("@STATE_ID", SqlDbType.Int);
			cmInsert.Parameters["@STATE_ID"].Value = _state_id;

			cmInsert.Parameters.Add("@POSTAL_CODE", SqlDbType.BigInt);
			cmInsert.Parameters["@POSTAL_CODE"].Value = _postal_code;

			cmInsert.Parameters.Add("@COUNTRY", SqlDbType.Char);
			cmInsert.Parameters["@COUNTRY"].Value = _country;


			cmInsert.Parameters.Add("@PREFIX_ID", SqlDbType.Int);
			cmInsert.Parameters["@PREFIX_ID"].Value = _prefix_id;

			cmInsert.Parameters.Add("@FIRST_NAME", SqlDbType.Char);
			cmInsert.Parameters["@FIRST_NAME"].Value = _first_name;

			cmInsert.Parameters.Add("@LAST_NAME", SqlDbType.Char);
			cmInsert.Parameters["@LAST_NAME"].Value = _last_name;

			cmInsert.Parameters.Add("@HOME_PHONE", SqlDbType.Char);
			cmInsert.Parameters["@HOME_PHONE"].Value = _home_phone;

			cmInsert.Parameters.Add("@WORK_PHONE", SqlDbType.Char);
			cmInsert.Parameters["@WORK_PHONE"].Value = _work_phone;

			cmInsert.Parameters.Add("@CELL_PHONE", SqlDbType.Char);
			cmInsert.Parameters["@CELL_PHONE"].Value = _cell_phone;

			cmInsert.Parameters.Add("@FAX", SqlDbType.Char);
			cmInsert.Parameters["@FAX"].Value = _fax;

			cmInsert.Parameters.Add("@EMAIL", SqlDbType.Char);
			cmInsert.Parameters["@EMAIL"].Value = _email;

			cmInsert.Parameters.Add("@DOB", SqlDbType.DateTime);
			cmInsert.Parameters["@DOB"].Value = _dob;

			#endregion


			//Test to see if any errors happen when actually making the connection
			try
			{
				cnInsert.Open();
				results = cmInsert.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				throw(e);
			}
			
			//return an int representing how many rows were affected. 
			//Combined with the error handling, this gives a good idea
			//As to whether or not it worked
			return results;
		}

		#endregion 
		
		#region Update Command
		public int dbUpdate()
		{

			//Generic Connection info
			SqlConnection cnUpdate = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
			
			SqlCommand cmUpdate = new SqlCommand("sp_IUD", cnUpdate);
			
			cmUpdate.Connection = cnUpdate;
			cmUpdate.CommandType = CommandType.StoredProcedure;

			#region Parameters
			
			//These next two parameters define which rows to update in the db
			cmUpdate.Parameters.Add("@ADDRESS_ID", SqlDbType.Int);
			cmUpdate.Parameters["@ADDRESS_ID"].Value = _address_id;

			cmUpdate.Parameters.Add("@PERSON_ID", SqlDbType.Int);
			cmUpdate.Parameters["@PERSON_ID"].Value = _person_id;




			//More parameters
			cmUpdate.Parameters.Add("@IUD", SqlDbType.Int);
			cmUpdate.Parameters["@IUD"].Value = 1;

			cmUpdate.Parameters.Add("@ADDRESS_LINE1", SqlDbType.Char);
			cmUpdate.Parameters["@ADDRESS_LINE1"].Value = _address_line1;

			cmUpdate.Parameters.Add("@ADDRESS_LINE2", SqlDbType.Char);
			cmUpdate.Parameters["@ADDRESS_LINE2"].Value = _address_line2;

			cmUpdate.Parameters.Add("@ADDRESS_LINE3", SqlDbType.Char);
			cmUpdate.Parameters["@ADDRESS_LINE3"].Value = _address_line3;

			cmUpdate.Parameters.Add("@CITY", SqlDbType.Char);
			cmUpdate.Parameters["@CITY"].Value = _city;

			cmUpdate.Parameters.Add("@STATE_ID", SqlDbType.Int);
			cmUpdate.Parameters["@STATE_ID"].Value = _state_id;

			cmUpdate.Parameters.Add("@POSTAL_CODE", SqlDbType.BigInt);
			cmUpdate.Parameters["@POSTAL_CODE"].Value = _postal_code;

			cmUpdate.Parameters.Add("@COUNTRY", SqlDbType.Char);
			cmUpdate.Parameters["@COUNTRY"].Value = _country;


			cmUpdate.Parameters.Add("@PREFIX_ID", SqlDbType.Int);
			cmUpdate.Parameters["@PREFIX_ID"].Value = _prefix_id;

			cmUpdate.Parameters.Add("@FIRST_NAME", SqlDbType.Char);
			cmUpdate.Parameters["@FIRST_NAME"].Value = _first_name;

			cmUpdate.Parameters.Add("@LAST_NAME", SqlDbType.Char);
			cmUpdate.Parameters["@LAST_NAME"].Value = _last_name;

			cmUpdate.Parameters.Add("@HOME_PHONE", SqlDbType.Char);
			cmUpdate.Parameters["@HOME_PHONE"].Value = _home_phone;

			cmUpdate.Parameters.Add("@WORK_PHONE", SqlDbType.Char);
			cmUpdate.Parameters["@WORK_PHONE"].Value = _work_phone;

			cmUpdate.Parameters.Add("@CELL_PHONE", SqlDbType.Char);
			cmUpdate.Parameters["@CELL_PHONE"].Value = _cell_phone;

			cmUpdate.Parameters.Add("@FAX", SqlDbType.Char);
			cmUpdate.Parameters["@FAX"].Value = _fax;

			cmUpdate.Parameters.Add("@EMAIL", SqlDbType.Char);
			cmUpdate.Parameters["@EMAIL"].Value = _email;

			cmUpdate.Parameters.Add("@DOB", SqlDbType.DateTime);
			cmUpdate.Parameters["@DOB"].Value = _dob;

			#endregion
			
			//Same as above
			try
			{
				cnUpdate.Open();
				results = cmUpdate.ExecuteNonQuery();
			}
			
			catch(Exception e)
			{
				throw(e);
			}
			
			//Yup, you guessed it. Same thing.
			return results;
		}
		#endregion

		#region Delete Command
		public int dbDelete()
		{
			//Generic Connection info
			SqlConnection cnDelete = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
			
			SqlCommand cmDelete = new SqlCommand("sp_IUD", cnDelete);

			cmDelete.Connection = cnDelete;
			cmDelete.CommandType = CommandType.StoredProcedure;


			//The only parameter needed is the Person_ID
			//The Proc takes care of deleting based on whatever PERSON_ID is given
			cmDelete.Parameters.Add("@PERSON_ID", SqlDbType.Int);
			cmDelete.Parameters["@ADDRESS_ID"].Value = _person_id;

			cmDelete.Parameters.Add("@IUD", SqlDbType.Int);
			cmDelete.Parameters["@IUD"].Value = 2;

			//Error Handling
			try
			{
				cnDelete.Open();
				results = cmDelete.ExecuteNonQuery();
			}
			
			catch(Exception e)
			{
				throw(e);
			}

			//Gimmie some results ^_^
			return results;

		}

		#endregion

		#region Get Everything Command ^_^
		
		public SqlDataReader dbGetAll()
		{

			//Generic Connection Info
			SqlConnection cnGetAll = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);


			//SQL Statement to select everything from the Address and Person table. 
			//Used to fill simple info in a small datagrid
			//Datagrid has a details button so that you can click again and get all the info if needed
			SqlCommand cmGetAll = new SqlCommand("SELECT * FROM tbl_address INNER JOIN tbl_person ON tbl_address.address_id = tbl_person.address_id ", cnGetAll);

			cmGetAll.Connection = cnGetAll;
			cmGetAll.CommandType = CommandType.Text;

			SqlDataReader dr;

			//Error handling
			try
			{
				dr = cmGetAll.ExecuteReader();
			}
			catch(Exception e)
			{

				throw e;
			}
			
			//Gimmie my reader!!
			return dr;
		}

		#endregion

		#region Cleanup Local Variables
		
		//I provided this method just in case
		//The values will stay unless otherwise told to leave, heh
		//And if the programmer used the same instance of the object
		//In the same method of the page, it could be disastrous. So,
		//If the programmer wishes to do that, he doesn't have to set
		//All the values to nothing again. This does it for him/her.
		public void dbCleanup()
		{
			_address_line1 = "";
			_address_line2 = "";
			_address_line3 = "";
			_city = "";
			_state_id = 0;
			_postal_code = 0;
			_country = "";
			_prefix_id = 0;
			_first_name = "";
			_last_name = "";
			_home_phone = "";
			_cell_phone = "";
			_work_phone = "";
			_fax = "";
			_email = "";
			_dob = System.DateTime.Now;
			_address_id = 0;
			_person_id = 0;
		}
		#endregion
	}
}