public void fillBWIClist()
{
string[] txtFlds;
txtFlds = new string[2];
if (usingSQLServer == true)
{
SqlConnection cn = new SqlConnection("Data Source=ZM-SQL-1;" +
"Initial Catalog=ZM_GALLAGHER; Integrated Security=SSPI;");
cn.Open();
SqlCommand cmd = new SqlCommand();
cmd = cn.CreateCommand();
cmd.CommandText = "delete from InventoryCriteriaSaved where criteria_sector='BWIC'" +
" and criteria_field='BWICID'";
cmd.ExecuteNonQuery();
cmd = cn.CreateCommand();
SqlDataReader Rdr;
string SQL = "SELECT BWIC_NAME, COUNT(*) AS CNT FROM BWICINVENTORY GROUP BY BWIC_NAME ORDER BY BWIC_NAME ASC;";
cmd.CommandText = SQL;
Rdr = cmd.ExecuteReader();
BWICListcheckedListBox.Items.Clear();
while (Rdr.Read())
{
if (Rdr.GetValue(0).ToString().Equals(""))
{
BWICListcheckedListBox.Items.Add("Missing", false);
}
else
{
txtFlds[0] = Rdr.GetValue(0).ToString();
txtFlds[1] = Rdr.GetValue(1).ToString();
BWICListcheckedListBox.Items.Add(txtFlds[0] + ": \t" + txtFlds[1], false);
}
}
Rdr.Close();
}
}
public void ChargerService(MySqlConnection Cnx)
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = Cnx;
cmd.CommandText = "service";
cmd.CommandType = CommandType.TableDirect;
MySqlDataReader Rdr;
Rdr = cmd.ExecuteReader();
while (Rdr.Read())
{
if (Rdr.GetString(2) == "P")
{
Service unService = new Service(Int32.Parse(Rdr.GetString(0)), Rdr.GetString(1), Rdr.GetString(2), Rdr.GetString(3), Int32.Parse(Rdr.GetString(4)));
lesServices.Add(unService);
}
else if (Rdr.GetString(2) == "A")
{
Service unService = new Service(Int32.Parse(Rdr.GetString(0)), Rdr.GetString(1), Rdr.GetString(2), Decimal.Parse(Rdr.GetString(5)));
lesServices.Add(unService);
}
}
}
private void buildBWICcriteria()
{
string SQL;
string sPiece;
int i;
string[] txtFlds;
txtFlds = new string[2];
//***
SQL = "select BWIC_NAME, COUNT(*) AS CNT from BWICinventory where 1=1 ";
i = 0;
//BWIC_NAME, COUNT(*) AS CNT FROM BWICINVENTORY GROUP BY BWIC_NAME ORDER BY BWIC_NAME ASC;
//mbsRangescheckedListBox.Items.Count
if (BWICdatecheckedListBox.CheckedItems.Count > 0)
{
SQL += " and FILE_DATE IN (";
while (i < BWICdatecheckedListBox.Items.Count)
{
if (BWICdatecheckedListBox.GetItemCheckState(i).ToString().Equals("Checked"))
{
sPiece = BWICdatecheckedListBox.Items[i].ToString();
if (sPiece.IndexOf(":") > 0)
{
sPiece = sPiece.Substring(0, sPiece.IndexOf(":"));
}
SQL += "'" + sPiece + "',";
}
i++;
}
SQL = SQL.Substring(0, SQL.Length - 1);
SQL += ") ";
}
SQL += " GROUP BY BWIC_NAME ORDER BY BWIC_NAME ASC;";
if (usingSQLServer == true)
{
SqlConnection cn = new SqlConnection("Data Source=ZM-SQL-1;" +
"Initial Catalog=ZM_GALLAGHER; Integrated Security=SSPI;");
cn.Open();
SqlCommand cmd = new SqlCommand();
cmd = cn.CreateCommand();
cmd.CommandText = SQL;
SqlDataReader Rdr;
Rdr = cmd.ExecuteReader();
BWICListcheckedListBox.Items.Clear();
while (Rdr.Read())
{
if (Rdr.GetValue(0).ToString().Equals(""))
{
BWICListcheckedListBox.Items.Add("Missing", false);
}
else
{
txtFlds[0] = Rdr.GetValue(0).ToString();
txtFlds[1] = Rdr.GetValue(1).ToString();
BWICListcheckedListBox.Items.Add(txtFlds[0] + ": \t" + txtFlds[1], false);
}
}
Rdr.Close();
}
}
private void addBWICList(string savedSearch, string criteria)
{
if (criteria.IndexOf(":") > 0)
{
criteria = criteria.Substring(0, criteria.IndexOf(":"));
}
if (usingSQLServer == false)
{
}
if (usingSQLServer == true)
{
SqlConnection cn = new SqlConnection("Data Source=ZM-SQL-1;" +
"Initial Catalog=ZM_GALLAGHER; Integrated Security=SSPI;");
cn.Open();
SqlCommand cmd = new SqlCommand();
string SQL = "select count(*) from InventoryCriteriaSaved where criteria_sector='BWIC' and " +
" criteria_field='BWICID' and " +
" criteria_min='" + criteria + "' ";
//if (MuniradioButtonGroup.Checked == true)
//{
// SQL += " and criteria_searchowner='GROUP';";
//}
//else
//{
SQL += " and criteria_searchowner='" + userID + "';";
//}
cmd = cn.CreateCommand();
SqlDataReader Rdr;
cmd.CommandText = SQL;
Rdr = cmd.ExecuteReader();
Rdr.Read();
if (Rdr.GetValue(0).ToString().Equals("0"))
{
Rdr.Close();
//if (MuniradioButtonGroup.Checked == true)
//{
// cmd.CommandText = "insert into InventoryCriteriaSaved values ('MUNI','" + savedSearch +
// "','State','" + criteria + "', '','Text','GROUP');";
//}
//else
//{
cmd.CommandText = "insert into InventoryCriteriaSaved values ('BWIC','','BWICID','" + criteria + "', '','Text','" + userID + "');";
//}
cmd.ExecuteNonQuery();
}
else
{
Rdr.Close();
}
cn.Close();
}
}
static void Main(string[] args)
{
int choix;
string sCnx;
decimal mtBudget;
MySqlConnection Cnx;
MySqlCommand Cmd;
MySqlDataReader Rdr;
// chaîne de caractères de connexion
sCnx = "server=localhost;uid=root;database=gesper;port=3306;pwd=siojjr";
//création d'un objet connexion
Cnx = new MySqlConnection(sCnx);
//ouverture de la connexion
try
{
Cnx.Open();
Console.WriteLine("connexion réussie");
}
catch (Exception e)
{
Console.WriteLine("erreur connexion " + e.Message.ToString());
}
do
{
do
{
Console.WriteLine("1 - liste complète des employés (utiliser une requête)");
Console.WriteLine("2 - liste complète des services (utiliser la table, sans écrire de requête)");
Console.WriteLine("3 - budget moyen des services administratifs");
Console.WriteLine("4 - liste des employés ne possédant pas de diplome ");
Console.WriteLine("5 - fin du traitement");
Console.WriteLine();
choix = Int32.Parse(Console.ReadLine());
} while (choix < 0 || choix > 5);
switch (choix)
{
case 1:
Console.WriteLine("1 - liste complète des employés (utiliser une requête)");
// la commande
Cmd = new MySqlCommand();
Cmd.Connection = Cnx;
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = "select * from employe;";
try
{
Rdr = Cmd.ExecuteReader();
while (Rdr.Read())
{
// avec le numéro de la colonne
Console.WriteLine(Rdr["emp_id"].ToString() + " " + Rdr["emp_nom"].ToString() + " " + Rdr["emp_prenom"].ToString() + " " + Rdr["emp_salaire"].ToString());
}
Rdr.Close();
}
catch (Exception e)
{
Console.WriteLine("{0} ", e.Message);
}
Console.WriteLine();
break;
case 2:
Console.WriteLine("2 - liste complète des services (utiliser la table, sans écrire de requête)");
// la commande
Cmd = new MySqlCommand();
Cmd.CommandText = "service;";
Cmd.CommandType = CommandType.TableDirect;
try
{
Rdr = Cmd.ExecuteReader();
while (Rdr.Read())
{
Console.WriteLine(Rdr["ser_id"].ToString() + " " + Rdr["ser_designation"].ToString() + " " + Rdr["ser_type"].ToString());
}
Rdr.Close();
}
catch (Exception e)
{
Console.WriteLine("{0} ", e.Message);
}
Console.WriteLine();
break;
case 3:
Console.WriteLine("3 - budget moyen des services administratifs");
Cmd = new MySqlCommand();
Cmd.Connection = Cnx;
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = "select ser_designation, avg(ser_budget) from service where ser_type = 'A';";
try
{
mtBudget = Cmd.ExecuteScalar();
Console.WriteLine(mtBudget["ser_designation"].toString() + " " + mtBudget["avg(ser_budget)"].toString());
}
catch (Exception e)
{
Console.WriteLine("{0} ", e.Message);
}
Console.WriteLine();
break;
case 4:
Console.WriteLine("4 - liste des employés ne possédant pas de diplome");
Cmd = new MySqlCommand();
Cmd.Connection = Cnx;
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = "SELECT emp_nom,emp_prenom,ser_designation FROM service s INNER JOIN employe e on s.ser_id = e.emp_service WHERE emp_id NOT IN(SELECT pos_employe FROM posseder); ";
MySqlDataReader pasDiplome;
pasDiplome = Cmd.ExecuteReader();
try
{
while (pasDiplome.Read())
{
Console.WriteLine(pasDiplome["emp_nom"].ToString() + " " + pasDiplome["emp_prenom"].ToString());
}
}
catch (Exception e)
{
Console.WriteLine("{0} ", e.Message);
}
pasDiplome.Close();
Console.WriteLine();
break;
case 5:
Console.WriteLine("Fin du traitement");
break;
}
}while (choix != 5);
Console.ReadLine();
}