using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;	// access
using System.Text;	// string builder

namespace Company4
{

	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class gridForm : System.Windows.Forms.Form
	{
		private System.Windows.Forms.DataGrid dataGrid1;
		// form data globals
		OleDbCommand aCommand;
		//OleDbDataReader aReader;
		System.Data.DataSet aDataSet;
		System.Data.OleDb.OleDbDataAdapter adapt;
		private System.Windows.Forms.Button btnSave;
		private System.Windows.Forms.Button btnAddNew;
		private System.Windows.Forms.Button btnHistory;
		private System.Windows.Forms.Button btnRefresh;
		private System.Windows.Forms.CheckBox chkRecentContacts;

		private string strSelectCompanies;

		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		public gridForm()
		{
			//
			// Required for Windows Form Designer support
			//
			InitializeComponent();

			//
			// TODO: Add any constructor code after InitializeComponent call
			//
			bool bRet = DataInitConnection("C:\\Learn\\data\\swinfra.mdb" , ref Globals.aConnection);
			aDataSet = new DataSet("tblCompany"); //allocate it below
			strSelectCompanies = "SELECT *  FROM tblCompany where priority > 0 ORDER BY priority DESC";
			if (bRet==true) bRet = this.DataReadToDataset(strSelectCompanies , ref aDataSet);
			if (bRet==true) 
			{
				this.dataGrid1.DataSource = aDataSet;
				this.dataGrid1.DataMember = "tblCompany";
				this.dataGrid1.SetDataBinding( aDataSet , "tblCompany" );
			}

			//Create_StoredProcedure();

		}

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

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			this.dataGrid1 = new System.Windows.Forms.DataGrid();
			this.btnSave = new System.Windows.Forms.Button();
			this.btnAddNew = new System.Windows.Forms.Button();
			this.btnHistory = new System.Windows.Forms.Button();
			this.btnRefresh = new System.Windows.Forms.Button();
			this.chkRecentContacts = new System.Windows.Forms.CheckBox();
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
			this.SuspendLayout();
			// 
			// dataGrid1
			// 
			this.dataGrid1.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom) 
				| System.Windows.Forms.AnchorStyles.Left) 
				| System.Windows.Forms.AnchorStyles.Right)));
			this.dataGrid1.DataMember = "";
			this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
			this.dataGrid1.Location = new System.Drawing.Point(0, 0);
			this.dataGrid1.Name = "dataGrid1";
			this.dataGrid1.Size = new System.Drawing.Size(972, 428);
			this.dataGrid1.TabIndex = 0;
			this.dataGrid1.Click += new System.EventHandler(this.dataGrid1_Click);
			this.dataGrid1.DoubleClick += new System.EventHandler(this.dataGrid1_DoubleClick);
			// 
			// btnSave
			// 
			this.btnSave.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
			this.btnSave.Location = new System.Drawing.Point(836, 444);
			this.btnSave.Name = "btnSave";
			this.btnSave.Size = new System.Drawing.Size(120, 32);
			this.btnSave.TabIndex = 1;
			this.btnSave.Text = "Save";
			this.btnSave.Click += new System.EventHandler(this.btnSave_Click);
			// 
			// btnAddNew
			// 
			this.btnAddNew.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
			this.btnAddNew.Location = new System.Drawing.Point(557, 442);
			this.btnAddNew.Name = "btnAddNew";
			this.btnAddNew.Size = new System.Drawing.Size(120, 32);
			this.btnAddNew.TabIndex = 2;
			this.btnAddNew.Text = "Add New";
			this.btnAddNew.Click += new System.EventHandler(this.btnAddNew_Click);
			// 
			// btnHistory
			// 
			this.btnHistory.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
			this.btnHistory.Location = new System.Drawing.Point(303, 441);
			this.btnHistory.Name = "btnHistory";
			this.btnHistory.Size = new System.Drawing.Size(120, 32);
			this.btnHistory.TabIndex = 3;
			this.btnHistory.Text = "History";
			this.btnHistory.Click += new System.EventHandler(this.btnHistory_Click);
			// 
			// btnRefresh
			// 
			this.btnRefresh.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
			this.btnRefresh.Location = new System.Drawing.Point(16, 440);
			this.btnRefresh.Name = "btnRefresh";
			this.btnRefresh.Size = new System.Drawing.Size(120, 32);
			this.btnRefresh.TabIndex = 4;
			this.btnRefresh.Text = "Refresh";
			this.btnRefresh.Click += new System.EventHandler(this.btnRefresh_Click);
			// 
			// chkRecentContacts
			// 
			this.chkRecentContacts.Location = new System.Drawing.Point(152, 448);
			this.chkRecentContacts.Name = "chkRecentContacts";
			this.chkRecentContacts.Size = new System.Drawing.Size(112, 16);
			this.chkRecentContacts.TabIndex = 6;
			this.chkRecentContacts.Text = "Recent Contacts";
			// 
			// gridForm
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(968, 490);
			this.Controls.Add(this.chkRecentContacts);
			this.Controls.Add(this.btnRefresh);
			this.Controls.Add(this.btnHistory);
			this.Controls.Add(this.btnAddNew);
			this.Controls.Add(this.btnSave);
			this.Controls.Add(this.dataGrid1);
			this.Name = "gridForm";
			this.Text = "Grid Form";
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new gridForm());
		}

		#region form data access code
		/// <summary>
		/// Data functions - init the connection (global to form)
		/// </summary>
		protected bool DataInitConnection(string strSource, ref OleDbConnection conn)
		{
			/// refer to 
			/// we're hardcoding the strings
			/// 

			string Provider="Microsoft.Jet.OLEDB.4.0";
			string Password="";
			string UserID="admin";
			string DatabaseName="C:\\Learn\\data\\swinfra.mdb";
			string Mode="ReadWrite" ;
			/// set up the connection string
			StringBuilder strBuild = new StringBuilder();
			strBuild.AppendFormat( "Provider={0};Password=\"{1}\";User ID={2};Data Source={3};Mode={4}", Provider, Password, UserID, DatabaseName, Mode );

			string strConnection  = strBuild.ToString();
			Console.WriteLine( strConnection );

			try
			{
				conn = new OleDbConnection(strConnection);
				conn.Open();
			}
			catch( Exception ex)
			{
				Console.WriteLine("Error: {0}", ex);
				return false;
			}
			return true;
		}

		/// <summary>
		/// PURPOSE: 1) populate a dataset for display (we only do one table here)
		/// PURPOSE: 2) generate the update, delete, insert commands
		/// RETURN: return true if it works, return recordset as a reference
		/// INPUTS: connection as global, dataset reference, sql string to execute
		/// </summary>
		protected bool DataReadToDataset(string strSQL , ref System.Data.DataSet ds)
		{
			// db command link to connection, sql query 
			//2) create the command object and store the sql query
			aCommand = new OleDbCommand(strSQL, Globals.aConnection);
			try
			{
				// 3) data adapter set link to command
				adapt = new OleDbDataAdapter(strSQL, Globals.aConnection);
				adapt.SelectCommand = aCommand;

				// 5) Command builder will generate the command required to update the
				// datasource from your select statement
				// using command builder
				System.Data.OleDb.OleDbCommandBuilder commandBuilder = 
					new OleDbCommandBuilder(adapt);
				commandBuilder.QuotePrefix="[";
				commandBuilder.QuoteSuffix="]";
				
				adapt.UpdateCommand = commandBuilder.GetUpdateCommand();	// 8/1/2004 this worked - it looks like hell but it works like magic
				adapt.InsertCommand = commandBuilder.GetInsertCommand();
				adapt.DeleteCommand = commandBuilder.GetDeleteCommand();
				//

				// 4) data set, link to data adapter
				//ds = new DataSet("tblCompany");
				
				adapt.Fill(ds , "tblCompany"); // hoping to fix the no error - no write problem
				
			}

				//Some usual exception handling
			catch(OleDbException ex)
			{
				Console.WriteLine("Error: {0}", ex.Errors[0].Message);
				return false;
			}
			return true;
		} // DataReadToRecordset

		// the simple commands to bind to the grid
		private void Data_BindGrid( ref System.Windows.Forms.DataGrid dg , ref System.Data.DataSet ds , string strDM )
		{
			dg.DataSource = ds;
			dg.DataMember = strDM;
		} // Data_BindGrid

		// *****************************************************
		// add new record to tblCompany
		private void Handle_AddNew()
		{
			string strSQL = "INSERT INTO tblCompany([Company],[Names],[languages],[address]"
				+ ",[phone],[misc],[contact],[web])"
				+ " VALUES (' ',' ', ' ', ' ', ' ', ' ', ' ', ' ') ";
			strSQL = "INSERT INTO tblCompany";	// try a simpler version
			strSQL = "INSERT INTO tblCompany([misc])"
				+ " VALUES ('') ";
			System.Data.OleDb.OleDbCommand  ole_command = new OleDbCommand(strSQL,Globals.aConnection);
			// how to refresh the grid?
			try
			{
				ole_command.ExecuteNonQuery();	// add a new record
				HandleSave();	// save the record
				OleDbCommand myOleCmd = new OleDbCommand("SELECT @@IDENTITY", Globals.aConnection);
				int lastID = (int)myOleCmd.ExecuteScalar();	// get the last id entered
				Console.Write(" Handle_AddNew , last id entered: {0:D}\n" , lastID);
				Globals.Clear();
				Globals.iRecord = lastID;		
				Company4.DetailsBound frm = new DetailsBound();
				frm.callingForm = null;
				frm.Show();


			}
			catch(System.Data.OleDb.OleDbException exO)
			{
				Console.WriteLine(strSQL);
				Console.WriteLine("{0}",exO.ToString());
				System.Windows.Forms.MessageBox.Show(" Error in Add Line: " + exO.Message);
			}
			catch( Exception ex)
			{
				Console.WriteLine("Error: {0}", ex);
			}
		} // Handle_AddNew
		
		/// <summary>
		/// PURPOSE: create an MS Access stored procedure
		/// UNDONE: while we include the syntax to delete the procedure, we don't test for 
		/// it's existence, so this routine would be run only to set up the database. The procedure
		/// remains in the database.
		/// REFERENCE: http://www.devcity.net/net/article.aspx?alias=msaccess_sp
		/// </summary>
		private void Create_StoredProcedure()
		{
			string strDEL = "DROP TABLE UpdateCompanyDetails;";

			string strSQL = "CREATE PROC UpdateCompanyDetails ( inID LONG , "
				+ " inCompany VARCHAR(255), "
				+ " inNames VARCHAR(255), "
				+ " inLanguages VARCHAR(255), "
				+ " inAddress VARCHAR(255), "
				+ " inPhone VARCHAR(255), "
				+ " inMisc TEXT, "
				//  If the data type name TEXT is used without specifying
				// the optional length, for example TEXT(25), a LONGTEXT field is 
				// created. 
				+ " inContact VARCHAR(255), "
				+ " inWeb VARCHAR(255) ,"
				+ " inAgent BIT, "
				+ " inPriority LONG, "
				+ " inContacted DATETIME "
				+ " ) "
				+ " AS UPDATE tblCompany "
				+ " SET  tblCompany.[Company] = inCompany ,"
				+ " tblCompany.[Names] = inNames ,"
				+ " tblCompany.[Languages] = inLanguages, "
				+ " tblCompany.[Address] = inAddress, "
				+ " tblCompany.[Phone] = inPhone, "
				+ " tblCompany.[Misc] = inMisc, "
				+ " tblCompany.[Contact] = inContact, "
				+ " tblCompany.[Web] = inWeb ,"
				+ " tblCompany.[Agent] = inAgent, "
				+ " tblCompany.[Priority] = inPriority, "
				+ " tblCompany.[Contacted] = inContacted "
				+ "  WHERE ID=inID;";

/* the below worked
			string strSQL = "CREATE PROC UpdateDetails( inID LONG , "
				+ " inCompany VARCHAR(255), "
				+ " inNames VARCHAR(255), "
				+ " inLanguages VARCHAR(255), "
				+ " inAddress VARCHAR(255), "
				+ " inPhone VARCHAR(255), "
				//+ " inMisc TEXT(250), "
				+ " inContact VARCHAR(255), "
				+ " inWeb VARCHAR(255) "
				//+ " inAgent YESNO, "
				//+ " inPriority INTEGER, "
				//+ " inContacted DATETIME "
				+ " ) "
				+ " AS UPDATE tblCompany "
				+ " SET  tblCompany.[Company] = inCompany ,"
				+ " tblCompany.[Names] = inNames ,"
				+ " tblCompany.[Languages] = inLanguages, "
				+ " tblCompany.[Address] = inAddress, "
				+ " tblCompany.[Phone] = inPhone, "
				//+ " tblCompany.[Misc] = inMisc, "
				+ " tblCompany.[Contact] = inContact, "
				+ " tblCompany.[Web] = inWeb "
				//+ " tblCompany.[Agent] = inAgent, "
				//+ " tblCompany.[inPriority] = inPriority, "
				//+ " tblCompany.[inContacted] = inContacted ,"
				+ "  WHERE ID=inID;";

			strSQL = "CREATE PROC procTest2(inID LONG, " 
            + "inCompany VARCHAR(40) , "
			+ " inNames VARCHAR(40) "
			+ " ) AS UPDATE tblCompany SET " 
            + "   tblCompany.[Company]=inCompany , tblCompany.[Names]=inNames  WHERE ID = inID;";
*/
			// how to refresh the grid?
			try
			{
				System.Data.OleDb.OleDbCommand  ole_command = new OleDbCommand("",Globals.aConnection);
				//ole_command.CommandText = strDEL;
				//ole_command.ExecuteNonQuery();	// delete the query
				ole_command.CommandText = strSQL;
				ole_command.ExecuteNonQuery();	// add the query
			}
			catch(System.Data.OleDb.OleDbException exO)
			{
				Console.WriteLine(strSQL);
				Console.WriteLine("{0}",exO.ToString());
				//System.Windows.Forms.MessageBox.Show(" Error in Add Line: " + exO.Message);
			}
			catch( Exception ex)
			{
				Console.WriteLine("Error: {0}", ex);
			}
		}
		
		#endregion


		private void btnSave_Click(object sender, System.EventArgs e)
		{
			HandleSave();
		}

		private void HandleSave()
		{
			try
			{
				if(this.aDataSet.HasChanges() == true) 
				{
					// what about Add New?
					System.Console.WriteLine("Update command text: " + this.adapt.UpdateCommand.CommandText);
					this.adapt.Update(this.aDataSet, "tblCompany");  
				}
			}
			catch(Exception ex)
			{
				
				System.Windows.Forms.MessageBox.Show("Error in save: " + ex.ToString() );
				System.Console.WriteLine("***************************************** Errors");
				System.Console.WriteLine(ex);
			}
		}

		/// <summary>
		/// double clicking on the grid brings up the details screen for editing
		/// (you can always edit the bound grid)
		/// bug - it's picking the wrong row after we sort the grid, and the following technique
		/// of getting the underlying data
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		private void dataGrid1_DoubleClick(object sender, System.EventArgs e)
		{
			//Globals.glbDataRow = dt.Rows[dataGrid1.CurrentRowIndex];
			Globals.iSelectedGridRow = this.dataGrid1.CurrentRowIndex;
			
			int K = this.dataGrid1.CurrentRowIndex;
			Globals.iRecord = System.Convert.ToInt32( this.dataGrid1[K,0].ToString() );
			int i=1;
			Globals.strCompany = this.dataGrid1[K,i++].ToString();
			Globals.strNames = this.dataGrid1[K,i++].ToString();
			Globals.strTechnologies = this.dataGrid1[K,i++].ToString();
			Globals.strAddress = this.dataGrid1[K,i++].ToString();
			Globals.strPhone = this.dataGrid1[K,i++].ToString();
			Globals.strMisc = this.dataGrid1[K,i++].ToString();
			Globals.strContact = this.dataGrid1[K,i++].ToString();
			Globals.strWeb = this.dataGrid1[K,i++].ToString();
			Globals.strAgent = this.dataGrid1[K,i++].ToString();
			Globals.strPriority = this.dataGrid1[K,i++].ToString();
			Globals.strDateContacted = this.dataGrid1[K,i++].ToString();
			
			Company4.DetailsBound frm = new DetailsBound();
			frm.callingForm = this;
			frm.Show();
		}



		private void dataGrid1_Click(object sender, System.EventArgs e)
		{
			string strCo = dataGrid1[dataGrid1.CurrentRowIndex,1].ToString();
			System.Console.WriteLine( strCo );
			Globals.strCompany = strCo;
			Globals.iRecord = System.Convert.ToInt32(dataGrid1[dataGrid1.CurrentRowIndex,0].ToString());
		}

		/// <summary>
		/// public function that we called at one point from a version of the details
		/// screen when we didn't know how to do updates from that screen.
		/// </summary>
		/// <param name="iSelected"></param>
		/// <param name="strCompany"></param>
		/// <param name="strNames"></param>
		/// <param name="strTechnologies"></param>
		/// <param name="strAddress"></param>
		/// <param name="strPhone"></param>
		/// <param name="strMisc"></param>
		/// <param name="strContact"></param>
		/// <param name="strWeb"></param>
		/// <param name="Agent"></param>
		public void SetRow( int iSelected , string strCompany,string  strNames,
			string strTechnologies,string strAddress,string strPhone,
			string strMisc,string strContact,
			string strWeb, bool Agent, string strDateContacted, string strPriority)
		{
			int i=1;
			this.dataGrid1[iSelected,i++] = strCompany;
			this.dataGrid1[iSelected,i++] = strNames;
			this.dataGrid1[iSelected,i++] = strTechnologies;
			this.dataGrid1[iSelected,i++] = strAddress;
			this.dataGrid1[iSelected,i++] = strPhone;
			this.dataGrid1[iSelected,i++] = strMisc;
			this.dataGrid1[iSelected,i++] = strContact;
			this.dataGrid1[iSelected,i++] = strWeb;
			this.dataGrid1[iSelected,i++] = Agent;
			i++;
			if (strDateContacted.Length>1) this.dataGrid1[iSelected,i] = System.DateTime.Parse( strDateContacted);
			// date is not working this.dataGrid1[iSelected,i++] = strPriority;
			//HandleSave();
		}


		private void Refresh_Grid()
		{
			// how to refresh the grid
			// in msdn: Dataset.Update, all you will need to do is to call DataGrid.Refresh 
			try
			{
				this.aDataSet.Dispose();	// try the dispose, because we were getting multiple rows for each company
				this.aDataSet = new DataSet("tblCompany");
				// make the check box active, show either all the active companies, or only the current ones
				if ( this.chkRecentContacts.Checked == false)
				{
					bool bRet = this.DataReadToDataset(strSelectCompanies, ref aDataSet);
				}
				else
				{
					bool bRet = this.DataReadToDataset("SELECT *  FROM ContactedCompanies", ref aDataSet);
				}

				this.dataGrid1.DataSource = aDataSet;
				this.dataGrid1.DataMember = "tblCompany";
				this.dataGrid1.SetDataBinding( aDataSet , "tblCompany" );
				this.dataGrid1.Refresh();
			}
			catch
			{
				// we're basically just never failing - we don't care about the error here
			}
		}

		private void btnAddNew_Click(object sender, System.EventArgs e)
		{
			Handle_AddNew();	// add a record
			//Refresh_Grid();
		}



		private void btnHistory_Click(object sender, System.EventArgs e)
		{
			string strCo = dataGrid1[dataGrid1.CurrentRowIndex,1].ToString();
			System.Console.WriteLine( strCo );
			Globals.strCompany = strCo;
			Company4.ContactHistory frm = new ContactHistory();
			frm.Show();
		}

		private void btnRefresh_Click(object sender, System.EventArgs e)
		{
		Refresh_Grid();
		}

	} // form class
} // namespace company4