using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;	// This is the library with the SQL types we're using below

namespace jessica_phase1sample2
{
	/// <summary>
	/// Summary description for TwoLangSpammers
	/// </summary>
	public class TwoLangSpammers : System.Web.UI.Page
	{
		private SqlConnection sqlConn;	// Declare a connection object
		private SqlCommand sqlCom;		  // Declare a command object
		private SqlDataReader sqlRdr;	  // Declare a reader to read the query result
		private string strSqlString = "server=IISQLSRV; uid=coolStudent; pwd=imsocool; database=spam2";

		protected System.Web.UI.WebControls.DataGrid SpammerDataGrid;
		protected System.Web.UI.WebControls.Label SpammerLangLabel;

		// The following is to display error messages, if any
		protected System.Web.UI.HtmlControls.HtmlGenericControl spanErrMsg;
	
		private void Page_Load(object sender, System.EventArgs e)
		{
			// Set the text of the label to include that of both of the languages that
			// were set in the Session variable.
			SpammerLangLabel.Text = "Spammers that sent spams in both " + Session["Lang1"] 
				                      + " and " + Session["Lang2"];

			try 
			{
				// Start a new connection to the database specified in strSqlString
				sqlConn = new SqlConnection( strSqlString );
				sqlConn.Open();	// open the connection


				// Execute query -- Here you could make 'strSql' any query you want
				string strSql = "SELECT DISTINCT h.FromAddr " + 
											  "FROM UnicodeHeaderV2 as h " + 
												"join UnicodeBody as b1 on h.id = b1.id " + 
												"join UnicodeBody as b2 on h.id = b2.id " + 
												"join Languages as l1 on l1.id = b1.langid " +
												"join Languages as l2 on l2.id = b2.langid " +
												"where l1.name = '" + Session["Lang1"] 
														+ "' and l2.name = '" + Session["Lang2"] + "'";

				// The command needs to know the query and the connection
				sqlCom = new SqlCommand(strSql, sqlConn);	
				// Execute the command
				sqlRdr = sqlCom.ExecuteReader();
				SpammerDataGrid.DataSource = sqlRdr;
				SpammerDataGrid.DataBind();

				// Close the sql connection at the end
				sqlConn.Close();

			} 
			catch (Exception e1)
			{
				spanErrMsg.InnerHtml = "<b><font color='red'>" + e1.Message.ToString() + "</font></b>";
			}
		}
		#region Web Form Designer generated code
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: This call is required by the ASP.NET Web Form Designer.
			//
			InitializeComponent();
			base.OnInit(e);
		}
		
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{    
			this.Load += new System.EventHandler(this.Page_Load);

		}
		#endregion
	}
}