HYTOS

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data.SQLite;

using System.Globalization;

using System.Data;

using System.Windows.Forms;

namespace POSCO_Report

{

	public static class DB

	{

		const string HMB_From_To = "HMB_From_To";

		const string HMB_LIST = "HMB_LIST";

		const string HMB_VALUE = "HMB_VALUE";

		const string Stream_No = "Stream_No";

		const string Components = "Components";

		const string Attributes = "Attributes";

		const string Symbol = "Symbol";

		const string SymbolType = "SymbolType";

		const string SymbolAttribute = "SymbolAttribute";

		public static bool CheckConnection()

		{

			ProjectInformation projectInformation = ProjectInformation.GetInstance();

			bool result = false;

			try

			{

				if (projectInformation.DBType.Equals(DBType.SQLite))

				{

					using (SQLiteConnection connection = new SQLiteConnection(projectInformation.ConnString, true))

					{

						connection.Open();

						if (connection.State.Equals(ConnectionState.Open))

						{

                            using (SQLiteCommand cmd = connection.CreateCommand())

                            {

                                cmd.CommandText = "SELECT NAME FROM sqlite_master WHERE type='table'";

                                using (SQLiteDataReader dr = cmd.ExecuteReader())

                                using (DataTable dt = new DataTable())

                                {

                                    dt.Load(dr);

                                    if (dt.Select(string.Format("NAME = '{0}'", HMB_From_To)).Length.Equals(1) &&

										dt.Select(string.Format("NAME = '{0}'", HMB_LIST)).Length.Equals(1) &&

										dt.Select(string.Format("NAME = '{0}'", HMB_VALUE)).Length.Equals(1) &&

										dt.Select(string.Format("NAME = '{0}'", Stream_No)).Length.Equals(1) &&

										dt.Select(string.Format("NAME = '{0}'", Components)).Length.Equals(1) &&

										dt.Select(string.Format("NAME = '{0}'", Attributes)).Length.Equals(1))

                                    {

										result = true;

									}

                                }

                            }

						}

						connection.Close();

					}

				}

				else if (projectInformation.DBType.Equals(DBType.MSSQL))

				{

					throw new Exception("mssql");

				}

			}

			catch (Exception ex)

			{

				MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error);

			}

			return result;

		}

		public static DataTable GetSymbolTable()

		{

			DataTable dt = new DataTable();

			ProjectInformation projectInformation = ProjectInformation.GetInstance();

			try

			{

				if (projectInformation.DBType.Equals(DBType.SQLite))

				{

					using (SQLiteConnection connection = new SQLiteConnection(projectInformation.ConnString, true))

					{

						connection.Open();

						if (connection.State.Equals(ConnectionState.Open))

						{

							using (SQLiteCommand cmd = connection.CreateCommand())

							{

								cmd.CommandText = string.Format("SELECT s.UID, s.Name, st.Type, s.BaseSymbol FROM {0} s LEFT JOIN {1} st on s.SymbolType_UID = st.UID", Symbol, SymbolType);

								using (SQLiteDataReader dr = cmd.ExecuteReader())

									dt.Load(dr);

							}

						}

						connection.Close();

					}

				}

				else if (projectInformation.DBType.Equals(DBType.MSSQL))

				{

					throw new Exception("mssql");

				}

			}

			catch (Exception ex)

			{

				MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error);

			}

			return dt;

		}

		public static DataTable GetSymbolAttributeTable()

		{

			DataTable dt = new DataTable();

			ProjectInformation projectInformation = ProjectInformation.GetInstance();

			try

			{

				if (projectInformation.DBType.Equals(DBType.SQLite))

				{

					using (SQLiteConnection connection = new SQLiteConnection(projectInformation.ConnString, true))

					{

						connection.Open();

						if (connection.State.Equals(ConnectionState.Open))

						{

							using (SQLiteCommand cmd = connection.CreateCommand())

							{

								cmd.CommandText = string.Format("SELECT DISTINCT Attribute FROM {0}", SymbolAttribute);

								using (SQLiteDataReader dr = cmd.ExecuteReader())

									dt.Load(dr);

							}

						}

						connection.Close();

					}

				}

				else if (projectInformation.DBType.Equals(DBType.MSSQL))

				{

					throw new Exception("mssql");

				}

			}

			catch (Exception ex)

			{

				MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error);

			}

			return dt;

		}

		public static DataTable GetHMBCaseDataTable()

		{

			DataTable dt = new DataTable();

			ProjectInformation projectInformation = ProjectInformation.GetInstance();

			try

			{

				if (projectInformation.DBType.Equals(DBType.SQLite))

				{

					using (SQLiteConnection connection = new SQLiteConnection(projectInformation.ConnString, true))

					{

						connection.Open();

						if (connection.State.Equals(ConnectionState.Open))

						{

							using (SQLiteCommand cmd = connection.CreateCommand())

							{

								cmd.CommandText = string.Format(@"

								SELECT hv.UID, hv.Value, hv.[Case], hv.HMB_LIST_UID, hl.Name, hl.Unit, hl.Type, st.Stream_No

								FROM {0} hv 

								LEFT JOIN {1} hl 

								ON hv.HMB_LIST_UID = hl.UID

								LEFT JOIN {2} st

								ON hv.Stream_No_UID = st.UID", HMB_VALUE, HMB_LIST, Stream_No);

								using (SQLiteDataReader dr = cmd.ExecuteReader())

									dt.Load(dr);

							}

						}

						connection.Close();

					}

				}

				else if (projectInformation.DBType.Equals(DBType.MSSQL))

				{

					throw new Exception("mssql");

				}

			}

			catch (Exception ex)

			{

				MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error);

			}

			return dt;

		}

		public static DataTable GetHMBListDataTable()

		{

			DataTable dt = new DataTable();

			ProjectInformation projectInformation = ProjectInformation.GetInstance();

			try

			{

				if (projectInformation.DBType.Equals(DBType.SQLite))

				{

					using (SQLiteConnection connection = new SQLiteConnection(projectInformation.ConnString, true))

					{

						connection.Open();

						if (connection.State.Equals(ConnectionState.Open))

						{

							using (SQLiteCommand cmd = connection.CreateCommand())

							{

								cmd.CommandText = string.Format(@"

								SELECT *

								FROM {0}", HMB_LIST);

								using (SQLiteDataReader dr = cmd.ExecuteReader())

									dt.Load(dr);

							}

						}

						connection.Close();

					}

				}

				else if (projectInformation.DBType.Equals(DBType.MSSQL))

				{

					throw new Exception("mssql");

				}

			}

			catch (Exception ex)

			{

				MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error);

			}

			return dt;

		}

		public static DataTable GetSymbolStreamData(string streamNoAttrName)

		{

			DataTable dt = new DataTable();

			ProjectInformation projectInformation = ProjectInformation.GetInstance();

			try

			{

				if (projectInformation.DBType.Equals(DBType.SQLite))

				{

					using (SQLiteConnection connection = new SQLiteConnection(projectInformation.ConnString, true))

					{

						connection.Open();

						if (connection.State.Equals(ConnectionState.Open))

						{

							using (SQLiteCommand cmd = connection.CreateCommand())

							{

								cmd.CommandText = string.Format(@"

								SELECT c.UID, c.SYMBOL_UID, a.[VALUE], sa.ATTRIBUTE 

								FROM COMPONENTS c, Attributes a, SymbolAttribute sa

								WHERE c.UID = a.COMPONENTS_UID AND a.SymbolAttribute_UID = sa.UID AND sa.Attribute = '{0}'", streamNoAttrName);

								using (SQLiteDataReader dr = cmd.ExecuteReader())

									dt.Load(dr);

							}

						}

						connection.Close();

					}

				}

				else if (projectInformation.DBType.Equals(DBType.MSSQL))

				{

					throw new Exception("mssql");

				}

			}

			catch (Exception ex)

			{

				MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error);

			}

			return dt;

		}

		public static DataTable GetSymbolItemData(string itemAttrName)

		{

			DataTable dt = new DataTable();

			ProjectInformation projectInformation = ProjectInformation.GetInstance();

			try

			{

				if (projectInformation.DBType.Equals(DBType.SQLite))

				{

					using (SQLiteConnection connection = new SQLiteConnection(projectInformation.ConnString, true))

					{

						connection.Open();

						if (connection.State.Equals(ConnectionState.Open))

						{

							using (SQLiteCommand cmd = connection.CreateCommand())

							{

								cmd.CommandText = string.Format(@"

								SELECT c.UID, c.SYMBOL_UID, a.[VALUE], sa.ATTRIBUTE 

								FROM COMPONENTS c, Attributes a, SymbolAttribute sa

								WHERE c.UID = a.COMPONENTS_UID AND a.SymbolAttribute_UID = sa.UID AND sa.Attribute = '{0}'", itemAttrName);

								using (SQLiteDataReader dr = cmd.ExecuteReader())

									dt.Load(dr);

							}

						}

						connection.Close();

					}

				}

				else if (projectInformation.DBType.Equals(DBType.MSSQL))

				{

					throw new Exception("mssql");

				}

			}

			catch (Exception ex)

			{

				MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error);

			}

			return dt;

		}

		public static string GetAttributeValue(string componentUID, string attrName)

		{

			string result = string.Empty;

			ProjectInformation projectInformation = ProjectInformation.GetInstance();

			try

			{

				if (projectInformation.DBType.Equals(DBType.SQLite))

				{

					using (SQLiteConnection connection = new SQLiteConnection(projectInformation.ConnString, true))

					{

						connection.Open();

						if (connection.State.Equals(ConnectionState.Open))

						{

							using (SQLiteCommand cmd = connection.CreateCommand())

							{

								DataTable dt = new DataTable();

								cmd.CommandText = string.Format(@"

								SELECT c.UID, c.SYMBOL_UID, a.[VALUE], sa.ATTRIBUTE 

								FROM COMPONENTS c, Attributes a, SymbolAttribute sa

								WHERE c.UID = a.COMPONENTS_UID AND a.SymbolAttribute_UID = sa.UID AND sa.Attribute = '{0}' AND c.UID = '{1}'", attrName, componentUID);

								using (SQLiteDataReader dr = cmd.ExecuteReader())

									dt.Load(dr);

								if (dt.Rows.Count.Equals(1))

									result = dt.Rows[0]["VALUE"].ToString();

							}

						}

						connection.Close();

					}

				}

				else if (projectInformation.DBType.Equals(DBType.MSSQL))

				{

					throw new Exception("mssql");

				}

			}

			catch (Exception ex)

			{

				MessageBox.Show(ex.Message, "error", MessageBoxButtons.OK, MessageBoxIcon.Error);

			}

			return result;

		}

	}

}