using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace DataMing
{
	/// <summary>
	/// Form1 的摘要說明。
	/// </summary>
	public class Form1 : System.Windows.Forms.Form
	{
		private System.Windows.Forms.DataGrid dataGrid1;
		static private int kind = 0;//分類數目
		static private int feature = 0;
		private string path = null;
		static private DataSet ds = new DataSet();
		private System.Windows.Forms.GroupBox groupBox1;
		private System.Windows.Forms.OpenFileDialog openFileDialog1;
		private System.Windows.Forms.DataGrid dataGrid2;
		private System.Windows.Forms.MainMenu mainMenu1;
		private System.Windows.Forms.MenuItem menuItem1;
		private System.Windows.Forms.MenuItem menuItem2;
		private System.Windows.Forms.MenuItem menuItem3;
		private System.Windows.Forms.MenuItem menuItem4;
		private System.Windows.Forms.GroupBox groupBox2;
		private System.Windows.Forms.MenuItem menuItem5;
		/// <summary>
		/// 必需的設計器變量。
		/// </summary>
		private System.ComponentModel.Container components = null;

		public Form1()
		{
			//
			// Windows 窗體設計器支持所必需的
			//
			InitializeComponent();

			//
			// TODO: 在 InitializeComponent 調用后添加任何構造函數代碼
			//
			
		}

		/// <summary>
		/// 清理所有正在使用的資源。
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows 窗體設計器生成的代碼
		/// <summary>
		/// 設計器支持所需的方法 - 不要使用代碼編輯器修改
		/// 此方法的內容。
		/// </summary>
		private void InitializeComponent()
		{
			this.dataGrid1 = new System.Windows.Forms.DataGrid();
			this.groupBox1 = new System.Windows.Forms.GroupBox();
			this.openFileDialog1 = new System.Windows.Forms.OpenFileDialog();
			this.dataGrid2 = new System.Windows.Forms.DataGrid();
			this.mainMenu1 = new System.Windows.Forms.MainMenu();
			this.menuItem1 = new System.Windows.Forms.MenuItem();
			this.menuItem2 = new System.Windows.Forms.MenuItem();
			this.menuItem3 = new System.Windows.Forms.MenuItem();
			this.menuItem4 = new System.Windows.Forms.MenuItem();
			this.groupBox2 = new System.Windows.Forms.GroupBox();
			this.menuItem5 = new System.Windows.Forms.MenuItem();
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
			this.groupBox1.SuspendLayout();
			((System.ComponentModel.ISupportInitialize)(this.dataGrid2)).BeginInit();
			this.groupBox2.SuspendLayout();
			this.SuspendLayout();
			// 
			// dataGrid1
			// 
			this.dataGrid1.AlternatingBackColor = System.Drawing.Color.White;
			this.dataGrid1.BackColor = System.Drawing.Color.White;
			this.dataGrid1.BackgroundColor = System.Drawing.Color.Ivory;
			this.dataGrid1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
			this.dataGrid1.CaptionBackColor = System.Drawing.Color.DarkSlateBlue;
			this.dataGrid1.CaptionForeColor = System.Drawing.Color.Lavender;
			this.dataGrid1.CaptionText = "標準差與均值";
			this.dataGrid1.DataMember = "";
			this.dataGrid1.FlatMode = true;
			this.dataGrid1.Font = new System.Drawing.Font("Tahoma", 8F);
			this.dataGrid1.ForeColor = System.Drawing.Color.Black;
			this.dataGrid1.GridLineColor = System.Drawing.Color.Wheat;
			this.dataGrid1.HeaderBackColor = System.Drawing.Color.CadetBlue;
			this.dataGrid1.HeaderFont = new System.Drawing.Font("Tahoma", 8F, System.Drawing.FontStyle.Bold);
			this.dataGrid1.HeaderForeColor = System.Drawing.Color.Black;
			this.dataGrid1.LinkColor = System.Drawing.Color.DarkSlateBlue;
			this.dataGrid1.Location = new System.Drawing.Point(16, 24);
			this.dataGrid1.Name = "dataGrid1";
			this.dataGrid1.ParentRowsBackColor = System.Drawing.Color.Ivory;
			this.dataGrid1.ParentRowsForeColor = System.Drawing.Color.Black;
			this.dataGrid1.ReadOnly = true;
			this.dataGrid1.SelectionBackColor = System.Drawing.Color.Wheat;
			this.dataGrid1.SelectionForeColor = System.Drawing.Color.DarkSlateBlue;
			this.dataGrid1.Size = new System.Drawing.Size(544, 112);
			this.dataGrid1.TabIndex = 0;
			// 
			// groupBox1
			// 
			this.groupBox1.Controls.Add(this.dataGrid2);
			this.groupBox1.Location = new System.Drawing.Point(48, 8);
			this.groupBox1.Name = "groupBox1";
			this.groupBox1.Size = new System.Drawing.Size(568, 232);
			this.groupBox1.TabIndex = 1;
			this.groupBox1.TabStop = false;
			this.groupBox1.Text = "訓練數據";
			// 
			// dataGrid2
			// 
			this.dataGrid2.AlternatingBackColor = System.Drawing.Color.White;
			this.dataGrid2.BackColor = System.Drawing.Color.White;
			this.dataGrid2.BackgroundColor = System.Drawing.Color.Ivory;
			this.dataGrid2.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
			this.dataGrid2.CaptionBackColor = System.Drawing.Color.DarkSlateBlue;
			this.dataGrid2.CaptionForeColor = System.Drawing.Color.Lavender;
			this.dataGrid2.CaptionText = "訓練數據";
			this.dataGrid2.DataMember = "";
			this.dataGrid2.FlatMode = true;
			this.dataGrid2.Font = new System.Drawing.Font("Tahoma", 8F);
			this.dataGrid2.ForeColor = System.Drawing.Color.Black;
			this.dataGrid2.GridLineColor = System.Drawing.Color.Wheat;
			this.dataGrid2.HeaderBackColor = System.Drawing.Color.CadetBlue;
			this.dataGrid2.HeaderFont = new System.Drawing.Font("Tahoma", 8F, System.Drawing.FontStyle.Bold);
			this.dataGrid2.HeaderForeColor = System.Drawing.Color.Black;
			this.dataGrid2.LinkColor = System.Drawing.Color.DarkSlateBlue;
			this.dataGrid2.Location = new System.Drawing.Point(16, 24);
			this.dataGrid2.Name = "dataGrid2";
			this.dataGrid2.ParentRowsBackColor = System.Drawing.Color.Ivory;
			this.dataGrid2.ParentRowsForeColor = System.Drawing.Color.Black;
			this.dataGrid2.SelectionBackColor = System.Drawing.Color.Wheat;
			this.dataGrid2.SelectionForeColor = System.Drawing.Color.DarkSlateBlue;
			this.dataGrid2.Size = new System.Drawing.Size(496, 184);
			this.dataGrid2.TabIndex = 0;
			// 
			// mainMenu1
			// 
			this.mainMenu1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
																					  this.menuItem1,
																					  this.menuItem5});
			// 
			// menuItem1
			// 
			this.menuItem1.Index = 0;
			this.menuItem1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
																					  this.menuItem2,
																					  this.menuItem3,
																					  this.menuItem4});
			this.menuItem1.Text = "文件&F";
			// 
			// menuItem2
			// 
			this.menuItem2.Index = 0;
			this.menuItem2.Text = "添加訓練數據";
			this.menuItem2.Click += new System.EventHandler(this.menuItem2_Click);
			// 
			// menuItem3
			// 
			this.menuItem3.Index = 1;
			this.menuItem3.Text = "顯示標準差與均值";
			this.menuItem3.Click += new System.EventHandler(this.menuItem3_Click);
			// 
			// menuItem4
			// 
			this.menuItem4.Index = 2;
			this.menuItem4.Text = "";
			// 
			// groupBox2
			// 
			this.groupBox2.Controls.Add(this.dataGrid1);
			this.groupBox2.Location = new System.Drawing.Point(48, 256);
			this.groupBox2.Name = "groupBox2";
			this.groupBox2.Size = new System.Drawing.Size(576, 152);
			this.groupBox2.TabIndex = 2;
			this.groupBox2.TabStop = false;
			this.groupBox2.Text = "特征值的標準差與均值";
			this.groupBox2.Visible = false;
			// 
			// menuItem5
			// 
			this.menuItem5.Index = 1;
			this.menuItem5.Text = "form2";
			this.menuItem5.Click += new System.EventHandler(this.menuItem5_Click);
			// 
			// Form1
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
			this.ClientSize = new System.Drawing.Size(704, 465);
			this.Controls.Add(this.groupBox2);
			this.Controls.Add(this.groupBox1);
			this.Menu = this.mainMenu1;
			this.Name = "Form1";
			this.Text = "分類";
			((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
			this.groupBox1.ResumeLayout(false);
			((System.ComponentModel.ISupportInitialize)(this.dataGrid2)).EndInit();
			this.groupBox2.ResumeLayout(false);
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// 應用程序的主入口點。
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new Form1());
		}

		/// <summary>
		/// 根據樣本數據進行訓練
		/// </summary>
		/// <param name="path">訓練數據文件的路徑</param>
		/// <param name="db" > 訓練數據的元數據</param>
		/// <returns></returns>
		static private DataSet GetDataSet(string path,DataTable db)
		{			
			string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ;Extended Properties=Excel 8.0;Data Source = " + path;
			OleDbConnection myConn = new OleDbConnection(strCon );

			feature = db.Rows.Count  - 1;//獲取特征數目

			//			string strCom = " SELECT v5,count(v5) as total,stdev(v1) as dev1,avg(v1) as avg1,stdev(v2) as dev2,avg(v2) as avg2,stdev(v3) as dev3," +
			//				"avg(v3) as avg3,stdev(v3) as dev4,avg(v4) as avg4 FROM [sheet1$] group by v5 having count(*)>2";
			/*-----------------------------
			 * 預處理，將列名轉化為固定的列名
			 * 
			 ------------------------------*/
			StringBuilder sb = new StringBuilder("select ");

			int count = db.Rows.Count ;
			string s = db.Rows[count-1][0].ToString();

			sb.Append(s + " as v" + count.ToString() + ",count(" + s +") as total");

			count--;

			for(int i = count;i>0;i--)
			{
				string ms = ",stdev(" + db.Rows[i-1][0].ToString() + ") as dev" + i + ",avg(" +  db.Rows[i-1][0].ToString() + ") as avg" + i;
				sb.Append(ms); 
			}
			sb.Append(" from [sheet1$] group by " + s + " having count(*) > 1");
			//Console.WriteLine("sb={0}",sb);

			try
			{ 
				myConn.Open ( ) ;
				OleDbDataAdapter da = new OleDbDataAdapter(sb.ToString(),myConn);
				da.Fill(ds);
				kind = ds.Tables[0].Rows.Count;//分類數目

				myConn.Close();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.Message);
			}
			return ds;
		}

		/// <summary>
		/// 獲取訓練數據的元素據
		/// </summary>
		/// <param name="path"></param>
		/// <returns></returns>
		static DataTable GetSchema(string path)
		{
			string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ;Extended Properties=Excel 8.0;Data Source = " + path;
			OleDbConnection myConn = new OleDbConnection(strCon );
			string str = "select top 1 * from [sheet1$]";

			OleDbCommand myCommand = new OleDbCommand(str,myConn);
			OleDbDataReader myReader;
			DataTable schemaTable = new DataTable();

			try
			{ 
				myConn.Open ( ) ;
				myReader = myCommand.ExecuteReader();

				schemaTable = myReader.GetSchemaTable();
				
				myConn.Close();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.Message);
			}
			return schemaTable;
		}

		/// <summary>
		/// 根據文件路徑，獲取待分類的數據集
		/// </summary>
		/// <param name="path">文件路徑</param>
		/// <returns>數據集合</returns>
		static private DataTable GetDataTable(string path)
		{
			string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ;Extended Properties=Excel 8.0;Data Source = " + path;
			OleDbConnection myConn = new OleDbConnection(strCon );
            
			string myCon = "select * from [sheet1$]";

			DataSet source = new DataSet();
			try
			{ 
				myConn.Open ( ) ;
				OleDbDataAdapter da = new OleDbDataAdapter(myCon,myConn);
				da.Fill(source);

				myConn.Close();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.Message);
			}
			return source.Tables[0];
		}

		private void menuItem2_Click(object sender, System.EventArgs e)
		{
			
			OpenFileDialog openFileDialog1 = new OpenFileDialog();

			openFileDialog1.InitialDirectory = "c:\\" ;
			openFileDialog1.Filter = "txt files (*.xls)|*.xls" ;
			openFileDialog1.FilterIndex = 1 ;
			openFileDialog1.RestoreDirectory = true ;

			if(openFileDialog1.ShowDialog() == DialogResult.OK)
			{
				path = openFileDialog1.FileName;
				dataGrid2.DataSource = GetDataTable(path);
				dataGrid1.DataSource = GetDataSet(path,GetSchema(path));
			}

		}

		private void menuItem3_Click(object sender, System.EventArgs e)
		{
			groupBox2.Visible = !groupBox2.Visible;
			menuItem3.Text = groupBox2.Visible ?"隱藏標準差與均值":"顯示標準差與均值";
		}

		/// <summary>
		/// 將數據分類
		/// </summary>
		/// <param name="db">有待分類的數據</param>
	    private void Classify(DataTable db)
		{
			if(db.Columns.Count!=feature)
			{
				MessageBox.Show(this,"輸入特征值的數目不對！","錯誤提示");
			}
		
			//添加結果列
				DataColumn col = new DataColumn();
				col.ColumnName = "result";
				col.DataType = Type.GetType("System.String");
				db.Columns.Add(col);

				double[] input = new double[feature];
				foreach(DataRow dr in db.Rows)
				{
					for(int i=0;i<feature;i++)
					{
						try
						{
							input[i] = double.Parse(dr[i].ToString());
						}
						catch(Exception ex)
						{
							Console.WriteLine(ex.Message);
						}
					}
					dr["result"] = ReturnType(input);
				}

		}
     
		/// <summary>
		/// 計算概率，分布為高斯分布
		/// </summary>
		/// <param name="x">樣本值</param>
		/// <param name="avg">樣本均值</param>
		/// <param name="dev">樣本方差</param>
		/// <returns>概率</returns>
		 private double GetPossibility(double x,double avg,double dev)
		{
			double n = -(x-avg)*(x-avg)/(2*dev*dev);
			double m = 1/(dev*Math.Sqrt(2*Math.PI));
			return Math.Exp(n)*m;
		}
		/// <summary>
		/// 根據輸入的特征值，返回其所屬的類
		/// </summary>
		/// <param name="input">特征值數組</param>
		/// <returns>類名</returns>
		 private string ReturnType(double[] input)
		{
			if(input.Length != feature)
			{
				return "ERROR!";
			}

			int total=0;//計算樣本的總數
			foreach (DataRow dr in ds.Tables[0].Rows)
			{
				try
				{
					total += int.Parse(dr["total"].ToString());
				}
				catch(Exception ex)
				{
					MessageBox.Show(this,ex.Message,"ERROR");
				}
			}

			int index = -1;//所屬類的索引
			int c = -1;
			double p = 0;//屬于某類的概率

			foreach (DataRow dr in ds.Tables[0].Rows)
			{
				c++;
				double sp = double.Parse(dr["total"].ToString())/total;

				//計算P(X|Ci)
				double temp = 1;
				for(int i=1;i<=feature;i++)
				{
					double avg = double.Parse(dr["avg" + i].ToString());
					double dev = double.Parse(dr["dev" + i].ToString());
					temp *= GetPossibility(input[i-1],avg,dev);	
				}

				temp *= sp;//P(X|Ci)P(Ci)

				if(p<temp)
				{
					p = temp;
					index = c;
				}
			}
			
			return ds.Tables[0].Rows[index]["v" + (feature+1)].ToString();     
		}

		private void menuItem5_Click(object sender, System.EventArgs e)
		{
			Form2 f2 = new Form2();
			f2.Show();
		}
	}
}