using System;
using System.Data;
using System.Data.SqlClient ;
using System.Web.Mail;

namespace Final_Project_Alpha
{
	/// <summary>
	/// Summary description for insert.
	/// </summary>
	public class insert
	{
		sends_mail objmail = new sends_mail();
		connect objconn = new connect();
		DataSet dstemp = new DataSet();
		DataSet dstemp1 = new DataSet();
		DataSet dstemp2 = new DataSet();
		DataSet dstemp_a = new DataSet();
		DataSet dstemp_count = new DataSet();
		DataSet dstemp_assg = new DataSet();
		DataSet dstemp_email_user = new DataSet();
		DataSet dstemp_group = new DataSet();
		DataSet dstemp_email = new DataSet();
		DataSet dstemp_action = new DataSet();
        DataSet dstemp_gr_action = new DataSet();

		string date,time,str_user,str_user_rep;
		


		public insert()
		{
			//
			// TODO: Add constructor logic here
			//
		}


		public void insert_ticketdata(String cat, String dept , String subcat , String priority , String descp ,String loginID) 
		{
			
			try
			{
				string count_all = "SELECT COUNT(*) FROM admin_ticket";
				string login = loginID+"_ticket";
				objconn.openDb();			
				SqlDataAdapter myCommand_a = objconn.adapt_a();
				SqlDataAdapter myCommand_count = objconn.adapt_all(count_all);
				myCommand_count.SelectCommand.CommandText = "SELECT * FROM admin_ticket";


				string assg_rep = "SELECT name,e_mail FROM user_rep WHERE category = '"+cat+
								   "' AND department = '"+dept+"'";
				string email_user = "SELECT e_mail from users where user_id = '"+ loginID +"'";
				string gr_action = "select group_name from groups "+
								   "where category = '"+ cat +"' "+
					               "AND dept = '"+ dept +"' "+
								   "AND priority = '"+ priority +"'";
			



				SqlDataAdapter myCommand_assg = objconn.adapt_assg(assg_rep);
				SqlDataAdapter myCommand_email = objconn.adapt_assg(email_user);
				SqlDataAdapter myCommand_gr_action = objconn.adapt_assg(gr_action);
				
			
				
				SqlCommandBuilder cb_a = new SqlCommandBuilder(myCommand_a);
				SqlCommandBuilder cb_count = new SqlCommandBuilder(myCommand_count);
				SqlCommandBuilder cb_assg = new SqlCommandBuilder(myCommand_assg);
				SqlCommandBuilder cb_assg_user_email = new SqlCommandBuilder(myCommand_email);
				SqlCommandBuilder cb_gr_action = new SqlCommandBuilder(myCommand_gr_action);


				int count = myCommand_count.Fill(dstemp_count);
				count = count+1;
			
				myCommand_a.Fill(dstemp_a);
				myCommand_assg.Fill(dstemp_assg);
				myCommand_email.Fill(dstemp_email_user);
				DataTable tab = new DataTable("groups");

				dstemp_gr_action.Tables.Add(tab);
				myCommand_gr_action.Fill(dstemp_gr_action,"groups");


			
				DataRow r_a = dstemp_a.Tables[0].NewRow();
				DataRow r_asg = dstemp_assg.Tables[0].Rows[0];
				DataRow r_email = dstemp_email_user.Tables[0].Rows[0];

				string rep_email = r_asg["e_mail"].ToString();
				string user_email = r_email["e_mail"].ToString();


				

				string tic;
				tic = loginID;
				tic += "-";
				tic += dept;
				tic += "-";
				tic += count.ToString();


				date =  DateTime.Now.ToShortDateString();
				time =  DateTime.Now.ToShortTimeString();

				
				// row for admin table
				r_a["ticket_id"] = tic;
				r_a["post_date"] = date;
				r_a["post_time"] = time;
				r_a["description"] = descp;
				r_a["dept"] = dept;
				r_a["category"] = cat;
				r_a["sub_category"] = subcat;
				r_a["priority"] = priority;
				r_a["assigned_to"] = r_asg["name"];
				r_a["owner_user"] = loginID;
				r_a["status"] = "un-resolved";

					
				SqlConnection conn = new SqlConnection("server=(local);database=HelpDeskPro;User Id=faizan");
				SqlCommand cmd = new SqlCommand( gr_action, conn );
				conn.Open();
				SqlDataReader reader = cmd.ExecuteReader();
				
				
				if (reader.HasRows == true)
				{
					reader.Read(); 
				r_a["gname"]  = reader.GetString(0);
					 
				}
				else 
				{
				r_a["gname"]  =null;
				}
				
				reader.Close();
				conn.Close();

				dstemp_a.Tables[0].Rows.Add(r_a);
				myCommand_a.Update(dstemp_a);
				dstemp_a.AcceptChanges();


				string from = "saira.sidd@gmail.com";
				string sub_rep = "New ticket";
				string sub_user = "Ticket Submitted";

				string body_rep = "A new ticket has been assgined to you and ticket Number is :"+tic;
				string body_user = "your ticket has been posted and its number is :"+tic+
					               " we'll get back to you shortly";

				objmail.send_mail(rep_email,from,sub_rep,body_rep);
				objmail.send_mail(user_email,from,sub_user,body_user);

			
			}
			catch(Exception ex)
			{
				Console.Write("Data Set Error: " + ex.Message ) ;
							
			}


		}


		public void insert_userdata(String id , String fname, String lname , String email , String dept) 
		{
			
			try
			{

				objconn.openDb();	
			
				str_user = "SELECT * FROM users";
				SqlDataAdapter myCommand1 = objconn.adaptregister(str_user);
				SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
				myCommand1.Fill(dstemp1);
				DataRow r1 = dstemp1.Tables[0].NewRow();
				r1["user_id"] = id;
				r1["First_Name"] = fname;
				r1["e_mail"] = email;
				r1["dept"] = dept;
				r1["Last_Name"] = lname;
				dstemp1.Tables[0].Rows.Add(r1);
				myCommand1.Update(dstemp1);
				dstemp1.AcceptChanges();
				
			}
			catch(Exception ex)
			{
				Console.Write("Data Set Error: " + ex.Message );
				
			}


		}


		public void insert_responses(String ticket_id , String rep_id, String response) 
		{
			
			try
			{

				objconn.openDb();	
			
				str_user = "SELECT * FROM responses";
				SqlDataAdapter myCommand1 = objconn.adaptregister(str_user);
				SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
				myCommand1.Fill(dstemp1);
				DataRow r1 = dstemp1.Tables[0].NewRow();
				r1["ticket_id"] = ticket_id;
				r1["rep_id"] = rep_id;
				r1["resp_date"] = DateTime.Now.ToShortDateString();
				r1["resp_time"] = DateTime.Now.ToShortTimeString();
				r1["response"] = response;
				dstemp1.Tables[0].Rows.Add(r1);
				myCommand1.Update(dstemp1);
				dstemp1.AcceptChanges();
				
			}
			catch(Exception ex)
			{
				Console.Write("Data Set Error: " + ex.Message );
				
			}


		}


		public void insert_groupdata(String gname,String category,String priority,String dept) 
		{
			
			try
			{

				objconn.openDb();	
				string str_gr,str_email;
				str_gr = "SELECT * FROM groups";
				str_email = "SELECT e_mail from user_rep where"+
							" category = '"+category+"' AND department = '"+dept+"'";

				SqlDataAdapter myCommand1 = objconn.adapt_group(str_gr);
				SqlDataAdapter email = objconn.adapt_all(str_email);
				SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
				SqlCommandBuilder cb_email = new SqlCommandBuilder(email);
				myCommand1.Fill(dstemp_group);
				email.Fill(dstemp_email);
				DataRow r1 = dstemp_group.Tables[0].NewRow();
				DataRow r2 = dstemp_email.Tables[0].Rows[0];
				
				r1["group_name"] = gname;
				r1["category"] = category;
				r1["priority"] = priority;
				r1["dept"] = dept;
				r1["email"] = r2["e_mail"];
						
				dstemp_group.Tables[0].Rows.Add(r1);
				myCommand1.Update(dstemp_group);
				dstemp_group.AcceptChanges();
				
			}
			catch(Exception ex)
			{
				Console.Write("Data Set Error: " + ex.Message );
				
			}


		}

	

		public void insert_user_rep_data(String name, String fname , String lname, String email , String category , String domain,String dept) 
		{
			
			try
			{

				objconn.openDb();	
				str_user_rep = "SELECT * FROM user_rep";
				SqlDataAdapter myCommand1 = objconn.adaptregister(str_user_rep);
				SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
				myCommand1.Fill(dstemp1);
				DataRow r1 = dstemp1.Tables[0].NewRow();
				r1["name"] = name;
				r1["fname"] = fname;
				r1["lname"] = lname;
				r1["e_mail"] = email;
				r1["department"] = dept;
				r1["category"] = category;
				r1["user_domain"] = domain;
				dstemp1.Tables[0].Rows.Add(r1);
				myCommand1.Update(dstemp1);
				dstemp1.AcceptChanges();
				
			}
			catch(Exception ex)
			{
				Console.Write("Data Set Error: " + ex.Message );
				
			}


		}






		public void insert_news(String news_id, String news_date , String news_desc) 
		{



		}





	}
}