Автоматизированная информационная система «Оптовый продовольственный склад»

    {

        public IDictionary OpenWindows { get; private set; } 

        public Form1()

        {

            InitializeComponent();

            this.OpenWindows = new Dictionary<string, Form>(); 

        }

***

        {

            frmListManager list;

            if (OpenWindows.Contains(TableName))

            {

                list = (frmListManager)OpenWindows[TableName];

                list.Activate();

            }

            else

            {

                list = new frmListManager(TableName, this);

                OpenWindows.Add(TableName, list);

                list.Show();

            }

        } 

        private void lnkUsers_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)

        {

            OpenList("users");

        }

        {

            var dt = new DataTable();

            string SQLstring =

              @"SELECT params.id        as idrow,

                       params.checked   as checked,

                       charac.name      as characteristic,

                       params.equality  as equality,

                       params.val       as val,

                       orderin.id       as id,

                       orderin.date     as date,

                       kgoods.name      as good,

                      orderin.`count`  as kol,

                       orderin.status   as status

                FROM orderinparameters as params

                LEFT JOIN kcharacteristics as charac

                     ON params.characteristic = charac.id

                LEFT JOIN orderin ON params.idorder = orderin.id

                LEFT JOIN kgoods ON orderin.good = kgoods.id

                WHERE orderin.client = '" + MySQL.CurrentUser.Id + "'";

            dt.Fill(SQLstring); 

            var parameters = from row in dt.AsEnumerable()

                             select new

                             {

                                 idorder  = row.GetId(),

                                 date     = DateTime.Parse(row.GetCol("date")),

                                 good     = row.GetCol("good"),

                                 count    = int.Parse(row.GetCol("kol")),

                                 status   = row.GetCol("status"),

                                 idrow    = int.Parse(row.GetCol("idrow")),

                                 check    = row.GetBoolCol("checked"),

                                 charac   = row.GetCol("characteristic"),

                                 equality = row.GetCol("equality"),

                                 val      = row.GetCol("val")

                             };

            // Группировка коллекции по составному ключу, содержащему

            // код, дату, код товара, количество, статус заявки

            var orders = from param in parameters

                         group param by new

                         {

                             id     = param.idorder,

                             date   = param.date,

                             good   = param.good,

                             count  = param.count,

                             status = param.status

                         } into ord

                         select new // все соберём в отдельный анонимный тип

                         {          // для облегчения привязки данных

                             order  = ord.Key.id,

                             date   = ord.Key.date,

                             good   = ord.Key.good,

                             count  = ord.Key.count,

                             status = ord.Key.status,

                             OrderParameters = ord

                         };

            bsOrderIn.DataSource = orders;

        }

        {

            var dt = new DataTable();

            #region Формирование строки запроса

            var SQLstring =

              @"SELECT

                  users.FullName                 as client,

                  kgoods.name                    as good,

                  orderin.id                     as id,

                  orderin.`count`                as kol,

                  orderin.status                 as status,

                  prices.price                   as price,

                  orderin.`count` * prices.price as summ

                FROM orderin

                LEFT JOIN kgoods ON orderin.good   = kgoods.id

                LEFT JOIN users  ON orderin.client = users.id

                LEFT JOIN (

                  SELECT

                    g.kindOfGood   as good,

                    AVG(c.`Value`) as price

                  FROM characteristics as c

                  LEFT JOIN goods as g ON c.good=g.id

                  WHERE kChar = '1'

                  GROUP BY g.kindOfGood

                ) as prices ON orderin.good=prices.good";

            #endregion

            dt.Fill(SQLstring); 

            // конвертация DataTable в анонимный тип для облегчения доступа

            var orders = from row in dt.AsEnumerable()

                         select new

                         {

                             id     = row.GetId(),

                             client = row.GetCol("client"),

                             good   = row.GetCol("good"),

                             status = int.Parse(row.GetCol("status")),

                             kol    = int.Parse(row.GetCol("kol")),

                             price  = float.Parse(row.GetCol("price")),

                             summ   = float.Parse(row.GetCol("summ"))

                         };

            // Группировка по покупателям

            var clients = from order in orders

                          group order by order.client into gr

                          select new

                          {

                              client = gr.Key,

                              kol    = gr.Count(),

                              summ   = gr.Sum(p => p.summ),

                              orders = gr

                          };

            bsOrderInOnClients.DataSource = clients;

        }

using System.Data;

using System.Data.Odbc;

using System.Windows;

using System.Windows.Forms;

using System.Linq; 

namespace winOrders

{

    public partial class frmListManager : Form

    {

        private OdbcDataAdapter adapter;

        private DataTable dt = new DataTable();

        private Form1 MainForm; 

        // Имя таблицы БД для отображения списка

        public string Table { get; private set; } 

        public frmListManager(string tblName, Form1 mForm)

        {

            InitializeComponent(); 

            MainForm = mForm;

            bindingSource1.DataSource = dt;

            Table = tblName.Trim();

            this.Text = Table.GetSynonym();

            // Получение информации о структуре таблицы

            DataTable tableStructure = Table.TableStructure();       

            //"Linq-заклинания" для получения строки SQL запросов и параметров к ней

            var SQLparams = tableStructure.AsEnumerable()      // Адаптация DataTable для Linq

                .Where(row => row.GetName() != "id")           // Нужны все, кроме "id"

                .Select(row => row.GetParameter());            // "Превращение" в OdbcParameter

            var SqlVars =

                tableStructure.AsEnumerable()                  // Адаптация DataTable для Linq

                .Where(row => row.GetName() != "id")           // Нужны все, кроме "id"

                .Select(row => " `" + row.GetName() + "` = ?") // Формирование переменной SQL-запроса

                .Aggregate((a, b) => a + ", " + b);            // Сборка в одну строку 

            adapter = MySQL.GetAdapter("select * from " + Table);

            adapter.RowUpdated += da_RowUpdated; // Подписка нужна для обновления поля id в dt

            adapter.InsertCommand =

                new OdbcCommand("INSERT INTO " + Table + " SET" + SqlVars, MySQL.Connection);

            foreach (var row in SQLparams) adapter.InsertCommand.Parameters.Add(row);

            adapter.UpdateCommand =

                new OdbcCommand("UPDATE " + Table + " SET" + SqlVars +

                               " WHERE id = ?", MySQL.Connection);

            foreach (var row in SQLparams) adapter.UpdateCommand.Parameters.Add(row);

            adapter.UpdateCommand.Parameters.Add("@id", OdbcType.Int, 0, "id");

            adapter.DeleteCommand =

                new OdbcCommand("DELETE FROM " + Table + " WHERE id = ?", MySQL.Connection);

            adapter.DeleteCommand.Parameters.Add("@id", OdbcType.Int, 0, "id");

            adapter.Fill(dt); 

            DataGridViewColumn dgvCol;

            foreach (DataRow row in tableStructure.Rows)

            {

                if (row.GetCol("rTab") == "")

                {

                    if (row.GetCol("Type") == "tinyint") // у нас это булево

                    {

                        var tmpCol = new DataGridViewCheckBoxColumn();

                        tmpCol.DataPropertyName = row.GetName();

                        tmpCol.TrueValue = 1;

                        tmpCol.FalseValue = 0;

                        tmpCol.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCellsExceptHeader; 

                        dgvCol = tmpCol;

                    }

                    else

                    {

                        var tmpCol = new DataGridViewTextBoxColumn();

                        tmpCol.DataPropertyName = row.GetName(); 

                        dgvCol = tmpCol;

                    }

                }

                else // Эта колонка - внешняя ссылка

                {

                    var tmpCol = new DataGridViewComboBoxColumn();

                    tmpCol.DataPropertyName = row.GetName();

                    tmpCol.DataSource = new DataTable(row.GetCol("rTab"));

                    tmpCol.ValueMember = row.GetCol("rCol");

                    tmpCol.DisplayMember = "Name";

                    tmpCol.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;

                    MySQL.GetAdapter("select `"

                        + row.GetCol("rCol") + "`, Name from "

                        + row.GetCol("rTab")).Fill((DataTable)tmpCol.DataSource); 

                    dgvCol = tmpCol;

                }

                dgvCol.Name = row.GetName(); 

                if (row.GetSynonym() != "") dgvCol.HeaderText = row.GetSynonym();

                if (row.GetName() == "id")

                {// идентификатор редактировать нельзя

                    dgvCol.ReadOnly = true;

                    dgvCol.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCellsExceptHeader;

                }

                dataGridView1.Columns.Add(dgvCol);

            } 

            if (Table == "users")

            {// Отсюда у пользователей можно поменять только полное наименование,

             // добавлять/удалять, менять имя/пароль можно только через администратор БД

                dataGridView1.AllowUserToAddRows = false;

                dataGridView1.AllowUserToDeleteRows = false;

                dataGridView1.Columns["Name"].ReadOnly = true;

                toolStripButton2.Visible = false;

            }

        } 

        void da_RowUpdated(object sender, OdbcRowUpdatedEventArgs e)

        {

            if (e.Errors == null && e.StatementType == StatementType.Insert)

            {// при добавлении новой строки автоинкрементное поле id

             // само собой в гриде не появляется, надо его так "выковыривать"

                e.Row["id"] = MySQL.GetLastId().ToString();

            }

        } 

        private void frmListManager_FormClosed(object sender, FormClosedEventArgs e)

        {

            MainForm.OpenWindows.Remove(Table);

        } 

        private void frmListManager_Shown(object sender, EventArgs e)

        {

            //connection.Open();

        } 

        private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)

        {

            if (dt.Rows.Count < dataGridView1.Rows.Count)

            { // Добавлена строка или ячейка - чекбокс

                if (dataGridView1[e.ColumnIndex, e.RowIndex] is DataGridViewCheckBoxCell)

                { // какой-то странный глюк с чекбоксами

                    if (dt.Rows.Count < (dataGridView1.Rows.Count-1))

                    { // Количество строк другое почему-то при добавлении

                        bindingSource1.EndEdit();

                        adapter.Update(dt);

                    }

                    else if(dt.Rows[e.RowIndex][e.ColumnIndex].ToString() != dataGridView1[e.ColumnIndex, e.RowIndex].Value.ToString())

                    {

                        bindingSource1.EndEdit();

                        adapter.Update(dt);

                    }

                }

                else

                {

                    bindingSource1.EndEdit();

                    adapter.Update(dt);

                }

            }

            else if (dt.Rows[e.RowIndex][e.ColumnIndex].ToString() != dataGridView1[e.ColumnIndex, e.RowIndex].Value.ToString())

            { // Изменено значение

                bindingSource1.EndEdit();

                adapter.Update(dt);

            }

        } 

        private void toolStripButton2_Click(object sender, EventArgs e)

        {

            DialogResult result = MessageBox.Show("Вы действительно хотите удалить строку?",

                                                  "Удаление",

                                                  MessageBoxButtons.OKCancel);

            if (result == DialogResult.OK)

            {

                dataGridView1.Rows.Remove(dataGridView1.CurrentRow);

                adapter.Update(dt);

            }

        }

   }

}