using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
namespace MySQL_Sdelki
{
public partial class Userform : Form
{
private string connection = "datasource=localhost;" + "database=dogovors;" + "username=root;" + "password=31337;";
private MySQL q;
public Userform()
{
InitializeComponent();
q = new MySQL(connection);
foreach (var value in DogP.Controls.OfType<ComboBox>())
{
value.SelectedIndexChanged += cBoxIndexSelectedDog;
}
// foreach (var value in ExcursionPanel.Controls.OfType<ComboBox>())
// {
// value.SelectedIndexChanged += cBoxIndexSelectedExcursion;
// }
Dogs.Checked = true;
// cat.SelectedIndexChanged += cBoxIndexSelected;
}
private void button1_Click(object sender, EventArgs e)
{
string comand = "select adrs.str as Объект,dog as Название,opf.form as ОПФ_Стороны_№1, sides.side as Сторона_№1, t1.form as ОПФ_Стороны_№2, t2.side as Сторона_№2, ldate as Дата_окончания, cats.cat as Категория, t3.str as Место_хранения from dogs,sides,adrs,opf,cats inner join adrs as t3 inner join opf as t1 inner join sides as t2 where adrs.id_a=obj and opf.id_f=sides.id_f and t1.id_f=t2.id_f and sides.id_s=s1 and t2.id_s=s2 and id_cat=dogs.cat and t3.id_a=keep order by adrs.str;";
DataTable dt = new DataTable();
using (MySqlConnection con = new MySqlConnection())
{
con.ConnectionString = connection;
MySqlCommand com = new MySqlCommand(comand, con);
try
{
con.Open();
using (MySqlDataReader dr = com.ExecuteReader())
{
if (dr.HasRows)
{
dt.Load(dr);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
con.Close();
}
dataGridView1.DataSource = dt;
}
private void Back_Click(object sender, EventArgs e)
{
try
{
var startform = new Startform();
if (!startform.Created)
{
startform = new Startform();
}
startform.Show();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
this.Hide();
}
}
private void userformClosed(object sender, System.Windows.Forms.FormClosedEventArgs e)
{
Application.Exit();
}
private void Dogs_CheckedChanged(object sender, EventArgs e)
{
PerP.Visible = false;
DogP.Visible = true;
try
{
string getCat="select distinct(Cats.Cat) as Категория from Cats;";
cat.DataSource = q.getTable(getCat);
cat.DisplayMember = "Категория";
cat.SelectedIndex = -1;
string getObj = "select distinct(adrs.str) as Объект from adrs";
obj.DataSource = q.getTable(getObj);
obj.DisplayMember = "Объект";
obj.SelectedIndex = -1;
string gets1 = "select distinct(sides.side) as Сторона_№1 from sides,dogs where s1=id_s;";
s1.DataSource = q.getTable(gets1);
s1.DisplayMember = "Сторона_№1";
s1.SelectedIndex = -1;
string gets2 = "select distinct(sides.side) as Сторона_№2 from sides,dogs where s2=id_s;";
s2.DataSource = q.getTable(gets2);
s2.DisplayMember = "Сторона_№2";
s2.SelectedIndex = -1;
string getKeep = "select adrs.str as Место_хранения from adrs where id_a=1 or id_a=3";
Keep.DataSource = q.getTable(getKeep);
Keep.DisplayMember = "Место хранения";
Keep.SelectedIndex = -1;
string comand = "select adrs.str as Объект,dog as Название,opf.form as ОПФ_Стороны_№1, sides.side as Сторона_№1, t1.form as ОПФ_Стороны_№2, t2.side as Сторона_№2, ldate as Дата_окончания, cats.cat as Категория, t3.str as Место_хранения from dogs,sides,adrs,opf,cats inner join adrs as t3 inner join opf as t1 inner join sides as t2 where adrs.id_a=obj and opf.id_f=sides.id_f and t1.id_f=t2.id_f and sides.id_s=s1 and t2.id_s=s2 and id_cat=dogs.cat and t3.id_a=keep;";
dataGridView1.DataSource = q.getTable(comand);
}
catch (Exception ex)
{
MessageBox.Show("Something went wrong:" + ex.ToString());
}
}
private void Pers_CheckedChanged(object sender, EventArgs e)
{
}
private void label1_Click(object sender, EventArgs e)
{
}
private void Cancel_Click(object sender, EventArgs e)
{
cat.SelectedIndex = -1;
s1.SelectedIndex = -1;
s2.SelectedIndex = -1;
obj.SelectedIndex = -1;
Keep.SelectedIndex = -1;
string com = "select adrs.str as Объект,dog as Название,opf.form as ОПФ_Стороны_№1, sides.side as Сторона_№1, t1.form as ОПФ_Стороны_№2, t2.side as Сторона_№2, ldate as Дата_окончания, cats.cat as Категория, t3.str as Место_хранения from dogs,sides,adrs,opf,cats inner join adrs as t3 inner join opf as t1 inner join sides as t2 where adrs.id_a=obj and opf.id_f=sides.id_f and t1.id_f=t2.id_f and sides.id_s=s1 and t2.id_s=s2 and id_cat=dogs.cat and t3.id_a=keep;";
dataGridView1.DataSource = q.getTable(com); //отображаем таблицу заново
}
private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
{
string s = dateTimePicker1.Value.Date.ToString("yyyy-MM-dd");
dataGridView1.DataSource = q.getTable("call getDate('" + s + "');");
}
private void cBoxIndexSelectedDog(object sender, EventArgs e)
{
try
{
List<string> mas = new List<string>();
foreach (var sBox in DogP.Controls.OfType<ComboBox>())
mas.Add(sBox.Text);
mas[0] = mas[0] != "" ? "and Cats.cat= '" + mas[0] + "'" : mas[0];
mas[1] = mas[1] != "" ? "and adrs.str= '" + mas[1] + "'" : mas[1];
mas[2] = mas[2] != "" ? "and sides.side= '" + mas[2] + "'" : mas[2];
mas[3] = mas[3] != "" ? "and sides.side= '" + mas[3] + "'" : mas[3];
mas[4] = mas[4] != "" ? "and adrs.str= '" + mas[4] + "'" : mas[4];
string com = "select adrs.str as Объект,dog as Название,opf.form as ОПФ_Стороны_№1, sides.side as Сторона_№1, t1.form as ОПФ_Стороны_№2, t2.side as Сторона_№2, ldate as Дата_окончания, cats.cat as Категория, t3.str as Место_хранения from dogs,sides,adrs,opf,cats inner join adrs as t3 inner join opf as t1 inner join sides as t2 where adrs.id_a=obj and opf.id_f=sides.id_f and t1.id_f=t2.id_f and sides.id_s=s1 and t2.id_s=s2 and id_cat=dogs.cat and t3.id_a=keep "+mas[0] + mas[1] + mas[2] + mas[3] + mas[4] + ";";
dataGridView1.DataSource = q.getTable(com);
mas.Clear();
}
catch (Exception ex)
{
MessageBox.Show("Something went wrong:\n" + ex.ToString());
}
}
}
}