using System.Data;

using System.Data.Odbc;

using System.Collections.Generic;

namespace winOrders

{

    static class MySQL

    {

        public static string Driver { get; set; }

        public static string Server { get; set; }

        public static int Port { get; set; }

        public static string User { get; set; }

        public static string Pass { get; set; }

        public static string Database { get; set; }

        public static OdbcConnection Connection { get; set; }

        public static Link CurrentUser { get; set; } 

        public static string ConnectionString()

        {// Формирование строки подключения к БД

            return "DRIVER="   + Driver + ";"

            + "SERVER="   + Server + ";"

            + "PORT="     + Port.ToString() + ";"

            + "USER="     + User + ";"

            + "PASSWORD=" + Pass + ";"

            + "DATABASE=" + Database + ";"

            + "stmt=SET NAMES 'cp1251';OPTION=4;";

        } 

        public static OdbcConnection GetConnection()

        {// Формирование подключения к БД

            return new OdbcConnection(ConnectionString());

        } 

        public static DataTable TableStructure(this string TableName)

        { // Возвращает таблицу структуры таблицы базы данных по имени таблицы

          // простите за тавтологию :)

            var dt = new DataTable();

            string SQLstring =

            @"SELECT

               i.COLUMN_COMMENT           as Synonym,

               i.COLUMN_NAME              as Name,

               i.DATA_TYPE                as Type,

               i.CHARACTER_MAXIMUM_LENGTH as Length,

               i.COLUMN_KEY               as _Key,

               k.REFERENCED_TABLE_NAME    as rTab,

               k.REFERENCED_COLUMN_NAME   as rCol

            FROM information_schema.COLUMNS as i

            LEFT JOIN information_schema.KEY_COLUMN_USAGE as k ON

               i.TABLE_SCHEMA = k.TABLE_SCHEMA AND

               i.TABLE_NAME   = k.TABLE_NAME AND

               i.COLUMN_NAME  = k.COLUMN_NAME

            WHERE i.TABLE_SCHEMA = '" + Database + "' and i.TABLE_NAME = '" + TableName + "'";

            dt.Fill(SQLstring);

            return dt;

        } 

        public static string GetSynonym(this string TableName)

        {// Определяет пользовательское представление таблицы БД по имени таблицы

            string SQLstring =

                @"SELECT LEFT(TABLE_COMMENT,LOCATE(';', TABLE_COMMENT)-1)

                FROM information_schema.`TABLES`

                where TABLE_SCHEMA = '"+Database+"' and TABLE_NAME = '"+TableName+"'";

            string resultString = GetCommand(SQLstring).ExecuteScalar().ToString();

            return resultString == "" ? TableName : resultString;

        } 

        public static string GetSynonym(this string TableName, string ColumnName)

        {// Определяет пользовательское представление колонки таблицы БД

            string SQLstring =

                "SELECT COLUMN_COMMENT FROM information_schema.`COLUMNS`"+

                "where TABLE_SCHEMA = '"+ Database+"'"

                +" and TABLE_NAME   = '"+ TableName+"'"

                +" and COLUMN_NAME  = '"+ ColumnName+"'";

            string resultString = GetCommand(SQLstring).ExecuteScalar().ToString();

            return resultString == "" ? ColumnName : resultString;

        } 

        public static string GetSynonym(this DataRow row)

        {// Находит пользовательское представление клонки в строке таблицы структуры БД

            return row.GetCol("Synonym") == "" ? row.GetName() : row.GetCol("Synonym");

        } 

        public static OdbcType GetOdbcType(string TypeName)

        {// Определение типа данных по имени типа

            switch (TypeName)

            {

                case "int":      return OdbcType.Int;

                case "varchar":  return OdbcType.VarChar;

                case "tinyint":  return OdbcType.TinyInt;

                case "date":     return OdbcType.Date;

                case "datetime": return OdbcType.DateTime; 

                default: return OdbcType.VarChar;

            }

        } 

        public static OdbcType GetOdbcType(this DataRow row)

        {// Получение значения типа данных из строки таблицы структуры БД

            return GetOdbcType(row.GetCol("Type"));

        } 

        public static int GetLenght(this DataRow row)

        {// Получение длины значения для колонки из строки талицы структуры БД

            return row.GetCol("Length") == "" ? 1 : int.Parse(row.GetCol("Length"));

        } 

        public static string GetName(this DataRow row)

        {// получение поля "Name"

         // необходимо часто, поэтому выделено в отдельный метод

            return row.GetCol("Name");

        } 

        public static string GetCol(this DataRow row, string ColName)

        {// Получение колонки таблицы по имени с проверкой существования колонки

            if (row.Table.Columns.Contains(ColName))

                return row[ColName].ToString();

            else throw new Exception("Нет колонки "+ColName+"!");

        } 

        public static bool GetBoolCol(this DataRow row, string ColName)

        {// Конвертация tinyint в bool

            return row.GetCol(ColName) == "1" ? true : false;

        } 

        public static int GetLastId()

        {// Получение последнего автоматически сгенерированного идентификатора

            return int.Parse(GetCommand("SELECT LAST_INSERT_ID()").ExecuteScalar().ToString());

        } 

        public static int GetId(this DataRow row)

        {// Получение поля "id"

            string id = row.GetCol("id").ToString();

            return id=="" ? 0 : int.Parse(id);

        } 

        public static OdbcParameter GetParameter(this DataRow row)

        {// Формирование параметра к запросу

            return new OdbcParameter("@" + row.GetName(), row.GetOdbcType(), row.GetLenght(), row.GetName());

        } 

        public static OdbcDataAdapter GetAdapter(string SQLstring)

       {// Получение дата-адаптера

            return new OdbcDataAdapter(SQLstring, Connection);

        } 
 

        public static void Fill(this DataTable table, string SQLstring)

        {// заполнение таблицы данными на основе строки SQL запроса

            table.Clear();

            GetAdapter(SQLstring).Fill(table);

        } 

        public static OdbcCommand GetCommand(string SQLstring)

        {// Получение объекта OdbcCommand

            return new OdbcCommand(SQLstring, Connection);

        } 

        public static bool TestConnection()

        {// Проверка соединения с БД

            bool result = true;

            try

            {

                Connection.Open();

            }

            catch (Exception)

            {

                result = false;

            }

            return result;

        } 

        public static List<Link> ToLinksList(this DataTable dt)

        {// Формирование списка ссылок из таблицы

            return new List<Link>(dt.AsEnumerable()

                .Select(r => new Link(dt.TableName, r.GetId(), r.GetName())));

        } 

        public static List<Link> GetLinkCollection(string tableName)

        {// Получение списка ссылок для выпадающих списков

            DataTable dt = new DataTable(tableName);

            dt.Fill("SELECT id, Name FROM "+tableName);

            return dt.ToLinksList();

        } 

        public static string ToMySQL(this DateTime date)

        {// Конвертация даты в строку формата MySQL

            return "'" + string.Format("{0:yyyy-MM-dd HH:mm:ss}", date) + "'";

        }

    }

}

{

      public string TableName { get; private set; }

      public int Id { get; set; }

      public string Name { get; set; } 

      public Link(string tableName)

      {

            this.TableName = tableName;

      } 

      public Link(string tableName, int id, string name)

      {

            this.TableName = tableName;

            this.Id = id;

            this.Name = name;

      } 

      public Link(string tableName, int id)

      {

            this.TableName = tableName;

            this.Id = id;

            this.Name = MySQL.GetCommand("SELECT `Name` FROM `"+tableName+

                  "` WHERE `id` = '" + id + "'").ExecuteScalar().ToString();

      } 

      public Link(string tableName, DataRow row, string colPrefix)

      {

            this.TableName = tableName;

            this.Id = int.Parse(row.GetCol(colPrefix+"_id"));

            this.Name = row.GetCol(colPrefix + "_name");

      } 

      public override string ToString()

      {

            return this.Name;

      } 

      public int CompareTo(object obj)

      {

            var other = obj as Link;

            int result = this.Name.CompareTo(other.Name);

            return result == 0 ? this.Id.CompareTo(other.Id) : result;

      }

}

{

   public DateTime Date { get; set; }

    public int Id { get; set; } 

    public Operation()

    {

        this.Date = DateTime.Now;

    } 

    public int CompareTo(object obj)

    {// Функция нужна для возможности упорядочивания

        var other = obj as Operation;

        int result = this.Date.CompareTo(other.Date);

        return result == 0 ? this.Id.CompareTo(other.Id) : result;

    } 

    public virtual bool Save()

    {

        return true;

    }

}

{

    public Link Client { get; set; }

    public float Count { get; set; }

    public int Status { get; set; } // Пока число, потом надо будет создать перечисление

    public List<OrderInParameter> Parameters { get; set; } 

    private Link _good;

    public Link Good

    {

        get { return _good; }

        set {

            _good = value;

            GenerateNewParameters();

        }

    }

    public OrderIn()

    {

        this.Client = MySQL.CurrentUser;

    } 

    public OrderIn(int id)

    {

        var dt = new DataTable(); 

        string SQLstring =

        @"SELECT orderin.id     as id,

                orderin.date   as date,

                orderin.client as client_id,

                users.Name     as client_name,

                orderin.good   as good_id,

                kgoods.Name    as good_name,

                orderin.count  as count,

                orderin.status as status

        FROM orderin

        LEFT JOIN users ON orderin.client = users.id

        LEFT JOIN kgoods ON orderin.good = kgoods.id

        WHERE orderin.id = '" + id + "'"; 

        dt.Fill(SQLstring); 

        if (dt.Rows.Count > 0)

        {

            var row = dt.Rows[0];

            this.Id = id;

            this.Date = DateTime.Parse(row.GetCol("date"));

            this.Client = new Link("users", row, "client");

            // присваиваю приватному свойству для того, чтобы

            // геттер не генерил новые параметры

            _good = new Link("kgoods", row, "good");

            this.Count = float.Parse(row.GetCol("count"));

            this.Status = int.Parse(row.GetCol("status"));

        } 
 

        dt.Clear(); 

        SQLstring =

        @"SElECT o.id             as id,

                o.checked        as checked,

                o.characteristic as char_id,

                k.name           as char_name,

                o.equality       as equality,

                o.val            as val

        FROM orderinparameters as o

        LEFT JOIN kcharacteristics as k ON o.characteristic = k.id

        WHERE o.idorder = '" + this.Id + "'"; 

        dt.Fill(SQLstring); 

        var parameters = dt.AsEnumerable()

            .Select(row => new OrderInParameter(this,

                row.GetId(),

                row.GetBoolCol("checked"),

                new Link("kcharacteristics", row, "char"),

                row.GetCol("equality"),

                row.GetCol("val"))); 

        this.Parameters = new List<OrderInParameter>(parameters);

    } 

    public void GenerateNewParameters()

    {

        var dt = new DataTable(); 

        string SQLstring =

        @"SELECT curCh.id       as id,

                curCh.checked  as checked,

                kc.id          as char_id,

                kc.Name        as char_name,

                curCh.equality as equality,

                curCh.val      as val

            FROM kchargoods as kcg

            LEFT JOIN kcharacteristics as kc ON kcg.KindOfCharacteristic = kc.id

            LEFT JOIN (

                SELECT id, checked, characteristic, val, equality

                FROM orderinparameters

                WHERE idorder = '" + this.Id+@"'

            ) as curCh ON kc.id = curCh.characteristic

            WHERE KindOfGood = '" + this.Good.Id + "'"; 

        dt.Fill(SQLstring); 

        var parameters = dt.AsEnumerable()

            .Select(row => new OrderInParameter(this,

                row.GetId(),

                row.GetBoolCol("checked"),

                new Link("kcharacteristics", row, "char"),

                row.GetCol("equality"),

                row.GetCol("val"))); 

        this.Parameters = new List<OrderInParameter>(parameters);

    } 

    public void OpenForm(Form1 mainForm)

    {

        frmOrderIn f;

        if (mainForm.OpenWindows.Contains(this.ToString()))

        {

            f = (frmOrderIn)mainForm.OpenWindows[this.ToString()];

            f.Activate();

        }

        else

        {

            f = new frmOrderIn(this, mainForm);

            mainForm.OpenWindows.Add(this.ToString(), f);

            f.Show();

        }

    } 

    public override string ToString()

    {

        return "Заявка клиента № "+this.Id.ToString() + " от " + this.Date.ToString();

    } 

    public override bool Save()

    {

        var command = MySQL.GetCommand(

            (this.Id == 0 ? "INSERT INTO" : "UPDATE") + " orderin SET " +

            "`date`   = " + this.Date.ToMySQL() + ", " +

            "`client` = " + this.Client.Id      + ", " +

            "`status` = " + this.Status         + ", " +

            "`good`   = " + this.Good.Id        + ", " +

            "`count`  = " + this.Count          + " " +

            (this.Id == 0 ? "" : ("WHERE id = " + this.Id + "")));

        command.ExecuteNonQuery();

        this.Id = this.Id == 0 ? MySQL.GetLastId() : this.Id; 

        // Сначала удалить те характеристики, которые были со старым товаром,

        // а потом установить характеристики, которые должны описывать новый товар

        command.CommandText = "DELETE from orderinparameters WHERE idorder = '" + this.Id + "'";

        command.ExecuteNonQuery();

        if (this.Parameters.Count != 0)

        {

            var SQLvalues =

                (from par in Parameters

                select ("(" +

                    par.Id                   + ", " +   // id

                    par.Order.Id             + ", " +   // idorder

                   (par.Checked ? "1" : "0") + ", " +   // checked

                    par.Characteristic.Id    + ", " +   // characteristic

              "'" + par.Equality             + "', " +  // equality

              "'" + par.Val                  + "')"     // val

                )).Aggregate((a, b) => a + ", " + b);

            command.CommandText =

            @"INSERT INTO orderinparameters (id, idorder, checked, characteristic, equality, val)

                VALUES " + SQLvalues + ";";

            command.ExecuteNonQuery();

        }

        return true;

    }

} 

public class OrderInParameter : IComparable

{

    public int Id { get; set; }

    public OrderIn Order { get; set; }

    public bool Checked { get; set; }

    public Link Characteristic { get; set; }

    public object Val { get; set; } 

    private string _equality;

    public string Equality

    {

        get { return _equality; }

        set { _equality = value == "" ? "=" : value; }

   } 

    public OrderInParameter()

    {

        this.Checked = false;

        this.Equality = "=";

    } 

    public OrderInParameter(OrderIn order)

    {

        this.Order = order;

        this.Checked = false;

        this.Equality = "=";

    } 

    public OrderInParameter(OrderIn order, int id)

    {

        var dt = new DataTable();

        string SQlstring =

            @"SElECT o.id       as id,

                    o.idorder  as idorder,

                    o.checked  as checked,

                   k.id       as char_id,

                    k.name     as char_name,

                    o.equality as equality,

                    o.val    as val

            FROM orderinparameters as o

            LEFT JOIN kcharacteristics as k ON o.characteristic = k.id

            WHERE o.id = '" + id + "'"; 
 

        dt.Fill(SQlstring); 

        if (dt.Rows.Count > 0)

        {

            var row = dt.Rows[0]; 

            this.Id = id;

            this.Order = order;

            this.Checked = row.GetBoolCol("checked");

            this.Characteristic = new Link("kcharacteristics", row, "char");

            this.Equality = row.GetCol("equality");

            this.Val = row.GetCol("val");

        }

    } 

    public OrderInParameter(OrderIn order, int id, bool check, Link characteristic, string equality, object val)

    {

        this.Id = id;

        this.Order = order;

        this.Checked = check;

        this.Characteristic = characteristic;

        this.Equality = equality;

        this.Val = val;

    } 

    public int CompareTo(object obj)

    {

        var other = obj as OrderInParameter;

        int result = this.Characteristic.CompareTo(other.Characteristic);

        return result;

    }

}

        {

            string oldUser = MySQL.User;

            string oldPass = MySQL.Pass;

            MySQL.User = txtUser.Text.Trim();

            MySQL.Pass = txtPass.Text.Trim();

            MySQL.Connection = MySQL.GetConnection();

            if (!MySQL.TestConnection())

            {

                MessageBox.Show("Неправильные имя пользователя или пароль!", "Ошибка аутентификации", MessageBoxButtons.OK);

                MySQL.User = oldUser;

                MySQL.Pass = oldPass; 

                this.DialogResult = DialogResult.Cancel;

            }