You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
524 lines
22 KiB
524 lines
22 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.ComponentModel;
|
|
using System.Data;
|
|
using System.Drawing;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Text.Json;
|
|
using System.Threading;
|
|
using System.Windows.Forms;
|
|
|
|
namespace AssistDB
|
|
{
|
|
public partial class AssitDB : Form
|
|
{
|
|
List<Class1> jsonFile;
|
|
private List<Column> cols;
|
|
private String listView2Content;
|
|
private List<String> listTable = new List<string>();
|
|
|
|
public AssitDB(List<Class1> jsonFile)
|
|
{
|
|
this.jsonFile = jsonFile;
|
|
InitializeComponent();
|
|
allSchema();
|
|
//allTable();
|
|
|
|
}
|
|
|
|
public void allSchema()
|
|
{
|
|
|
|
this.listView1.Items.Clear();
|
|
this.jsonFile.ForEach(json => this.listView1.Items.Add(json.schema));
|
|
this.jsonFile.ForEach(json => this.comboBox1.Items.Add(json.schema));
|
|
this.jsonFile.ForEach(json => this.comboBox2.Items.Add(json.schema));
|
|
|
|
}
|
|
|
|
public void findByNameSchema(String search)
|
|
{
|
|
Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
|
|
this.listView1.Items.Clear();
|
|
List<String> list = this.jsonFile.Select(name => name.schema).Where(schema => schema.Contains(search.ToUpper())).ToList();
|
|
list.ForEach(name => this.listView1.Items.Add(name));
|
|
Splash.CloseForm();
|
|
}
|
|
|
|
public void findByNameTable(String search)
|
|
{
|
|
Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
|
|
if (search.Length > 2)
|
|
{
|
|
this.listView2.Items.Clear();
|
|
List<string> listResult = new List<string>();
|
|
foreach (Class1 cl in this.jsonFile)
|
|
{
|
|
foreach (Datum data in cl.data)
|
|
{
|
|
if (data.table.Contains(search.ToUpper()))
|
|
{
|
|
listResult.Add(data.table);
|
|
|
|
}
|
|
}
|
|
}
|
|
|
|
listResult.Sort();
|
|
listResult = listResult.Distinct().ToList();
|
|
listResult.ForEach(table => this.listView2.Items.Add(table));
|
|
|
|
}
|
|
else
|
|
{
|
|
this.listView2.Clear();
|
|
this.listView2.Items.Add("Veuillez renseigner une recherche plus grande");
|
|
}
|
|
Splash.CloseForm();
|
|
}
|
|
|
|
public void findByNameTableInSchema(String schema, String search)
|
|
{
|
|
Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
|
|
if (search.Length > 2)
|
|
{
|
|
this.listView2.Items.Clear();
|
|
|
|
foreach (Class1 cl in this.jsonFile)
|
|
{
|
|
if(cl.schema.Equals(schema))
|
|
{
|
|
foreach (Datum data in cl.data)
|
|
{
|
|
if (data.table.Contains(search.ToUpper()))
|
|
{
|
|
this.listView2.Items.Add(data.table);
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
else
|
|
{
|
|
this.listView2.Clear();
|
|
this.listView2.Items.Add("Veuillez renseigner une recherche plus grande");
|
|
}
|
|
Splash.CloseForm();
|
|
}
|
|
|
|
private void searchBox_TextChanged(object sender, EventArgs e)
|
|
{
|
|
findByNameSchema(this.searchBox.Text);
|
|
}
|
|
|
|
private void textBox1_TextChanged(object sender, EventArgs e)
|
|
{
|
|
if (this.listView1.SelectedItems.Count > 0)
|
|
{
|
|
findByNameTableInSchema(listView1.SelectedItems[0].Text, this.textBox1.Text);
|
|
}
|
|
else
|
|
{
|
|
findByNameTable(this.textBox1.Text);
|
|
}
|
|
|
|
}
|
|
|
|
private void listView1_SelectedIndexChanged(object sender, EventArgs e)
|
|
{
|
|
Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
|
|
if (listView1.SelectedItems.Count > 0)
|
|
{
|
|
if (this.listView2.SelectedItems.Count > 0)
|
|
{
|
|
this.listView2_SelectedIndexChanged(sender, e);
|
|
}
|
|
else
|
|
{
|
|
|
|
this.listView2Content = this.listView1.SelectedItems[0].Text;
|
|
this.listView2.Items.Clear();
|
|
Class1 schema = this.jsonFile.Where(json => json.schema.Equals(this.listView2Content)).First();
|
|
foreach (Datum data in schema.data)
|
|
{
|
|
this.listTable.Add(data.table);
|
|
}
|
|
this.listTable.ForEach(table => this.listView2.Items.Add(table));
|
|
|
|
}
|
|
}
|
|
Splash.CloseForm();
|
|
|
|
}
|
|
|
|
private void listView2_SelectedIndexChanged(object sender, EventArgs e)
|
|
{
|
|
Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
|
|
if (this.listView2.SelectedItems.Count > 0)
|
|
{
|
|
|
|
HashSet<Class1> schemas = new HashSet<Class1>();
|
|
Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
|
|
foreach (Class1 schema in this.jsonFile)
|
|
{
|
|
foreach (Datum table in schema.data)
|
|
{
|
|
|
|
if (table.table.Equals(listView2.SelectedItems[0].Text)) {
|
|
if (this.listView1.SelectedItems.Count > 0)
|
|
{
|
|
if (schema.schema.Equals(listView1.SelectedItems[0].Text))
|
|
{
|
|
this.listView3.Clear();
|
|
this.richTextBox1.Clear();
|
|
this.cols = new List<Column>();
|
|
this.richTextBox1.SelectionFont = new Font("Consolas", 12);
|
|
this.richTextBox1.AppendText(Environment.NewLine + "-----------------------------------------");
|
|
this.richTextBox1.AppendText(Environment.NewLine + "Table name : " + table.table);
|
|
this.richTextBox1.AppendText(Environment.NewLine + "Table texte : " + table.table_text);
|
|
this.richTextBox1.AppendText(Environment.NewLine + "-----------------------------------------");
|
|
this.listView3.Items.Add("Toutes les conlonnes");
|
|
foreach (Column col in table.columns)
|
|
{
|
|
this.listView3.Items.Add(col.name);
|
|
this.cols.Add(col);
|
|
printCol(col);
|
|
}
|
|
}
|
|
}
|
|
schemas.Add(schema);
|
|
}
|
|
}
|
|
}
|
|
this.listView1.Items.Clear();
|
|
schemas.ToList().ForEach(json => this.listView1.Items.Add(json.schema));
|
|
Splash.CloseForm();
|
|
}
|
|
else
|
|
{
|
|
this.listView3.Items.Clear();
|
|
this.listView3.Items.Add("Veuillez selectionner un schéma");
|
|
}
|
|
Splash.CloseForm();
|
|
}
|
|
|
|
private void listView3_SelectedIndexChanged(object sender, EventArgs e)
|
|
{
|
|
Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
|
|
if (this.textBox2.Text.Equals(""))
|
|
{
|
|
this.richTextBox1.Clear();
|
|
if (this.listView3.SelectedItems.Count > 0)
|
|
{
|
|
if (listView3.SelectedItems[0].Text.Equals("Toutes les conlonnes"))
|
|
{
|
|
printAllcol(cols);
|
|
}
|
|
else
|
|
{
|
|
Column column = this.cols.Where(col => col.name.Equals(listView3.SelectedItems[0].Text)).First();
|
|
printCol(column);
|
|
}
|
|
|
|
}
|
|
} else
|
|
{
|
|
|
|
this.richTextBox1.Clear();
|
|
if (this.listView3.SelectedItems.Count > 0)
|
|
{
|
|
HashSet<Datum> tables = new HashSet<Datum>();
|
|
Column colres= null;
|
|
foreach (Class1 schema in this.jsonFile)
|
|
{
|
|
foreach (Datum table in schema.data)
|
|
{
|
|
foreach (Column col in table.columns)
|
|
{
|
|
if(col.name != null)
|
|
{
|
|
if (col.name.Equals(this.listView3.SelectedItems[0].Text))
|
|
{
|
|
colres = col;
|
|
tables.Add(table);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
printCol(colres);
|
|
this.listView2.Items.Clear();
|
|
tables.ToList().ForEach(json => this.listView2.Items.Add(json.table));
|
|
}
|
|
|
|
}
|
|
Splash.CloseForm();
|
|
|
|
}
|
|
|
|
private void button1_Click(object sender, EventArgs e)
|
|
{
|
|
allSchema();
|
|
this.searchBox.Text = "";
|
|
}
|
|
|
|
private void button2_Click(object sender, EventArgs e)
|
|
{
|
|
this.listView2.Items.Clear();
|
|
this.listView3.Items.Clear();
|
|
this.richTextBox1.Clear();
|
|
this.textBox1.Text = "";
|
|
|
|
}
|
|
|
|
private void printCol(Column col)
|
|
{
|
|
this.richTextBox1.SelectionFont = new Font("Consolas", 12);
|
|
this.richTextBox1.AppendText(Environment.NewLine + "-----------------------------------------");
|
|
this.richTextBox1.AppendText(Environment.NewLine + "Colonne : " + col.name);
|
|
this.richTextBox1.AppendText(Environment.NewLine + "Data type : " + col.data_type);
|
|
this.richTextBox1.AppendText(Environment.NewLine + "Length : " + col.length);
|
|
this.richTextBox1.AppendText(Environment.NewLine + "Colonne texte : " + col.column_text);
|
|
this.richTextBox1.AppendText(Environment.NewLine + "-----------------------------------------");
|
|
}
|
|
|
|
private void printAllcol(List<Column> cols)
|
|
{
|
|
cols.ForEach(col => printCol(col));
|
|
}
|
|
|
|
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
|
|
{
|
|
Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
|
|
if (this.comboBox1.SelectedIndex > -1)
|
|
{
|
|
this.comboBox3.Items.Clear();
|
|
this.listView5.Items.Clear();
|
|
Class1 schema = this.jsonFile.Where(json => json.schema.Equals(comboBox1.SelectedItem)).First();
|
|
|
|
foreach (Datum data in schema.data)
|
|
{
|
|
this.comboBox3.Items.Add(data.table);
|
|
}
|
|
|
|
}
|
|
Splash.CloseForm();
|
|
}
|
|
|
|
private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
|
|
{
|
|
Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
|
|
if (this.comboBox2.SelectedIndex > -1)
|
|
{
|
|
this.comboBox4.Items.Clear();
|
|
this.listView5.Items.Clear();
|
|
Class1 schema = this.jsonFile.Where(json => json.schema.Equals(comboBox2.SelectedItem)).First();
|
|
|
|
foreach (Datum data in schema.data)
|
|
{
|
|
this.comboBox4.Items.Add(data.table);
|
|
}
|
|
|
|
}
|
|
Splash.CloseForm();
|
|
}
|
|
|
|
private void searchJoinTable()
|
|
{
|
|
Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
|
|
if (this.comboBox3.SelectedIndex > -1 && this.comboBox4.SelectedIndex > -1)
|
|
{
|
|
|
|
this.listView5.Items.Clear();
|
|
Class1 schema = this.jsonFile.Where(json => json.schema.Equals(comboBox1.SelectedItem)).First();
|
|
Datum table = schema.data.Where(data => data.table.Equals(comboBox3.SelectedItem)).First();
|
|
|
|
Class1 schema2 = this.jsonFile.Where(json => json.schema.Equals(comboBox2.SelectedItem)).First();
|
|
Datum table2 = schema2.data.Where(data => data.table.Equals(comboBox4.SelectedItem)).First();
|
|
|
|
foreach(Column col in table.columns)
|
|
{
|
|
foreach(Column col2 in table2.columns)
|
|
{
|
|
if(col.name.Contains(col2.name))
|
|
{
|
|
this.listView5.Items.Add(col.name);
|
|
}
|
|
}
|
|
}
|
|
|
|
}
|
|
Splash.CloseForm();
|
|
|
|
}
|
|
|
|
private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
|
|
{
|
|
searchJoinTable();
|
|
}
|
|
|
|
private void comboBox4_SelectedIndexChanged(object sender, EventArgs e)
|
|
{
|
|
searchJoinTable();
|
|
}
|
|
|
|
private void listView5_SelectedIndexChanged(object sender, EventArgs e)
|
|
{
|
|
Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
|
|
if (this.listView5.SelectedItems.Count == 1)
|
|
{
|
|
this.richTextBox3.Clear();
|
|
Class1 schema = this.jsonFile.Where(json => json.schema.Equals(comboBox1.SelectedItem)).First();
|
|
Datum table = schema.data.Where(data => data.table.Equals(comboBox3.SelectedItem)).First();
|
|
|
|
foreach (Column col in table.columns)
|
|
{
|
|
if (col.name.Equals(this.listView5.SelectedItems[0].Text))
|
|
{
|
|
this.richTextBox3.SelectionFont = new Font("Consolas", 12);
|
|
this.richTextBox3.AppendText(Environment.NewLine + "-----------------------------------------");
|
|
this.richTextBox3.AppendText(Environment.NewLine + "Colonne : " + col.name);
|
|
this.richTextBox3.AppendText(Environment.NewLine + "Data type : " + col.data_type);
|
|
this.richTextBox3.AppendText(Environment.NewLine + "Length : " + col.length);
|
|
this.richTextBox3.AppendText(Environment.NewLine + "Colonne texte : " + col.column_text);
|
|
this.richTextBox3.AppendText(Environment.NewLine + "-----------------------------------------");
|
|
}
|
|
}
|
|
this.richTextBox2.Clear();
|
|
writeJoin(""); // JOIN
|
|
writeJoin(" LEFT "); // LEFT JOIN
|
|
writeJoin("RIGHT "); // RIGHT JOIN
|
|
writeJoin("INNER "); // INNER JOIN
|
|
writeJoin("OUTER "); // OUTER JOIN
|
|
} else if (this.listView5.SelectedItems.Count > 1)
|
|
{
|
|
this.richTextBox3.Clear();
|
|
this.richTextBox3.SelectionFont = new Font("Consolas", 12);
|
|
Class1 schema = this.jsonFile.Where(json => json.schema.Equals(comboBox1.SelectedItem)).First();
|
|
Datum table = schema.data.Where(data => data.table.Equals(comboBox3.SelectedItem)).First();
|
|
|
|
for (int i=0; i< this.listView5.SelectedItems.Count; i++)
|
|
{
|
|
foreach (Column col in table.columns)
|
|
{
|
|
if (col.name.Equals(this.listView5.SelectedItems[i].Text))
|
|
{
|
|
this.richTextBox3.AppendText(Environment.NewLine + "-----------------------------------------");
|
|
this.richTextBox3.AppendText(Environment.NewLine + "Colonne : " + col.name);
|
|
this.richTextBox3.AppendText(Environment.NewLine + "Data type : " + col.data_type);
|
|
this.richTextBox3.AppendText(Environment.NewLine + "Length : " + col.length);
|
|
this.richTextBox3.AppendText(Environment.NewLine + "Colonne texte : " + col.column_text);
|
|
this.richTextBox3.AppendText(Environment.NewLine + "-----------------------------------------");
|
|
}
|
|
}
|
|
}
|
|
this.richTextBox2.Clear();
|
|
writeJoin(""); // JOIN
|
|
writeJoin(" LEFT "); // LEFT JOIN
|
|
writeJoin("RIGHT "); // RIGHT JOIN
|
|
writeJoin("INNER "); // INNER JOIN
|
|
writeJoin("OUTER "); // OUTER JOIN
|
|
}
|
|
Splash.CloseForm();
|
|
}
|
|
|
|
private void writeJoin(string join)
|
|
{
|
|
string schema1 = comboBox1.SelectedItem.ToString();
|
|
string schema2 = comboBox2.SelectedItem.ToString();
|
|
string table1 = comboBox3.SelectedItem.ToString();
|
|
string table2 = comboBox4.SelectedItem.ToString();
|
|
string select = "SELECT " + table1.ToLower() + ".*, " + table2.ToLower() + ".* ";
|
|
string from = "FROM " +
|
|
schema1 + "." + table1 + " AS " + table1.ToLower();
|
|
string jointure = join + "JOIN " + schema2 + "." + table2 + " AS " + table2.ToLower() + " ";
|
|
string on = "ON " + table1.ToLower() + "." + this.listView5.SelectedItems[0].Text + "=" + table2.ToLower() +
|
|
"." + this.listView5.SelectedItems[0].Text;
|
|
if(this.listView5.SelectedItems.Count > 1)
|
|
{
|
|
for (int i = 1; i < this.listView5.SelectedItems.Count; i++)
|
|
{
|
|
on += "\nAND " + table1.ToLower() + "." + this.listView5.SelectedItems[i].Text + "=" + table2.ToLower() +
|
|
"." + this.listView5.SelectedItems[i].Text + " ";
|
|
}
|
|
}
|
|
|
|
|
|
|
|
this.richTextBox2.SelectionFont = new Font("Consolas", 14);
|
|
if(join.Equals(""))
|
|
{
|
|
this.richTextBox2.AppendText(Environment.NewLine + "------------------------ JOIN -------------------------");
|
|
}
|
|
else
|
|
{
|
|
this.richTextBox2.AppendText(Environment.NewLine + "--------------------- " + join + "JOIN ----------------------");
|
|
}
|
|
|
|
this.richTextBox2.AppendText(Environment.NewLine + select);
|
|
this.richTextBox2.AppendText(Environment.NewLine + from);
|
|
this.richTextBox2.AppendText(Environment.NewLine + jointure);
|
|
this.richTextBox2.AppendText(Environment.NewLine + on);
|
|
this.richTextBox2.AppendText(Environment.NewLine + "---------------------------------------------------------");
|
|
this.richTextBox2.AppendText(Environment.NewLine);
|
|
}
|
|
|
|
private void AssitDB_FormClosed(object sender, FormClosedEventArgs e)
|
|
{
|
|
Application.Exit();
|
|
}
|
|
|
|
private void textBox2_TextChanged(object sender, EventArgs e)
|
|
{
|
|
Splash.ShowSplashScreen(this.Location, this.Height, this.Width);
|
|
if (textBox2.Text.Length > 2)
|
|
{
|
|
this.listView3.Items.Clear();
|
|
List<string> listResult = new List<string>();
|
|
List<string> listResultTable = new List<string>();
|
|
foreach (Class1 cl in this.jsonFile)
|
|
{
|
|
foreach (Datum data in cl.data)
|
|
{
|
|
foreach(Column col in data.columns)
|
|
{
|
|
if(col.name != null )
|
|
{
|
|
if (col.name.Contains(textBox2.Text.ToUpper()))
|
|
{
|
|
listResult.Add(col.name);
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
listResult.Sort();
|
|
|
|
listResult = listResult.Distinct().ToList();
|
|
|
|
listResult.ForEach(col => this.listView3.Items.Add(col));
|
|
|
|
}
|
|
else
|
|
{
|
|
this.listView3.Clear();
|
|
this.listView3.Items.Add("Veuillez renseigner une recherche plus grande");
|
|
}
|
|
Splash.CloseForm();
|
|
}
|
|
|
|
private void button3_Click(object sender, EventArgs e)
|
|
{
|
|
this.listView3.Items.Clear();
|
|
this.textBox2.Text = "";
|
|
}
|
|
|
|
}
|
|
}
|
|
|