public void DisplaySensor()
{
this.dgvSensor.Rows.Clear();
List <Dictionary <String, String> > resultset = new List <Dictionary <string, string> >();
try
{
resultset = DBInteractor.QuickSelect("SELECT * FROM sensor");
}
catch (Exception ex)
{
MessageBox.Show("ERREUR : Impossible de se connecter à la base de données...\n\r\n\r" +
ex.Message + "\n\r" + ex.StackTrace);
}
foreach (Dictionary <String, String> line in resultset)
{
int row = dgvSensor.Rows.Add(new object[] {
line["uid"],
line["label"],
line["webservice"],
Properties.Resources.pencil3,
Properties.Resources.pbelle
});
dgvSensor.Rows[row].Tag = line["id"];
}
}
public static List <Dictionary <String, String> > getAllSensors()
{
Sensor s = new Sensor();
DateTime start = s.main.GetStartDate();
DateTime end = s.main.GetEndDate();
String startString = start.ToString("yyyy-MM-dd HH:mm:ss");
String endString = end.ToString("yyyy-MM-dd HH:mm:ss");
String idSensor = s.main.getSensor();
String query;
String whereClause = idSensor;
whereClause = (idSensor == "") ? "1" : whereClause = "sensor.id = " + idSensor;
query = "SELECT sensor.*, data.*" +
"FROM sensor INNER JOIN data " +
"ON data.sensor LIKE sensor.id " +
"WHERE " + whereClause + " " +
"AND data_date BETWEEN '" + startString + "' AND '" + endString + "' " +
"ORDER BY data_date DESC, sensor ";
List <Dictionary <String, String> > resultset = new List <Dictionary <string, string> >();
try
{
return(resultset = DBInteractor.QuickSelect(query));
}
catch (Exception ex)
{
MessageBox.Show("ERREUR : Impossible de se connecter à la base de données...\n\r\n\r" +
ex.Message + "\n\r" + ex.StackTrace);
}
return(resultset);
}
public void refreshSensor()
{
dgvSensor.Rows.Clear();
List <Dictionary <String, String> > resultset = new List <Dictionary <string, string> >();
try
{
resultset = DBInteractor.QuickSelect("SELECT * FROM sensor");
}
catch (Exception ex)
{
MessageBox.Show("ERREUR : Impossible de se connecter à la base de données...\n\r\n\r" +
ex.Message + "\n\r" + ex.StackTrace);
}
foreach (Dictionary <String, String> line in resultset)
{
DataGridViewRow row = new DataGridViewRow();
row.CreateCells(dgvSensor, new object[] {
line["uid"],
line["label"],
line["webservice"],
Properties.Resources.pencil3,
Properties.Resources.pbelle
});
row.Tag = line["id"];
dgvSensor.Rows.Add(row);
}
}
//if (dtpStart.Value > dtpEnd.Value) MessageBox.Show("Rectifiez votre sélection de dates");
//data.DisplayData();
public void refreshSensorMain()
{
cbSensor.Items.Clear();
cbSensor.DisplayMember = "Text";
cbSensor.ValueMember = "Name";
List <Dictionary <String, String> > lines = new List <Dictionary <string, string> >();
try
{
lines = DBInteractor.QuickSelect("SELECT id,label, webservice FROM sensor");
}
catch (Exception ex)
{
MessageBox.Show("ERREUR : Impossible de se connecter à la base de données...\n\r\n\r" +
ex.Message + "\n\r" + ex.StackTrace);
}
MenuItem empty_item = new MenuItem();
empty_item.Text = "";
empty_item.Name = "";
cbSensor.Items.Add(empty_item);
foreach (Dictionary <String, String> line in lines)
{
MenuItem item = new MenuItem();
item.Text = line["label"];
item.Name = line["id"];
item.Tag = line["webservice"];
cbSensor.Items.Add(item);
}
}
public void DisplayData()
{
this.dgBase.Rows.Clear();
Form form = this.ParentForm;
MainForm main = (MainForm)form;
DateTime start = main.GetStartDate();
DateTime end = main.GetEndDate();
String startString = start.ToString("yyyy-MM-dd HH:mm:ss");
String endString = end.ToString("yyyy-MM-dd HH:mm:ss");
String idSensor = main.getSensor();
String query;
String optionalClause = "WHERE 1 ";
if (idSensor != "")
{
optionalClause = "WHERE sensor.id = " + idSensor + " ";
}
query = "SELECT sensor.*, data.*" +
"FROM sensor INNER JOIN data " +
"ON data.sensor = sensor.id " +
optionalClause + " " +
"AND (data_date BETWEEN '" + startString + "' AND '" + endString + "') " +
"ORDER BY data_date DESC, sensor ";
List <Dictionary <String, String> > resultset = new List <Dictionary <string, string> >();
try
{
resultset = DBInteractor.QuickSelect(query);
}
catch (Exception ex)
{
MessageBox.Show("ERREUR : Impossible de se connecter à la base de données...\n\r\n\r" +
ex.Message + "\n\r" + ex.StackTrace);
}
foreach (Dictionary <String, String> line in resultset)
{
int row = dgBase.Rows.Add(new object[] {
line["label"],
line["data_date"],
line["temperature"],
line["humidity"],
line["uid"],
Properties.Resources.pbelle
});
dgBase.Rows[row].Tag = line["id"];
}
}
public void chTemp_Load()
{
chTempHumid.Series["Temperatures"].Points.Clear();
chTempHumid.Series["Humidité"].Points.Clear();
Form form = this.ParentForm;
MainForm main = (MainForm)form;
DateTime start = main.GetStartDate();
DateTime end = main.GetEndDate();
String startString = start.ToString("yyyy-MM-dd HH:mm:ss");
String endString = end.ToString("yyyy-MM-dd HH:mm:ss");
String idSensor = main.getSensor();
String query;
if (idSensor != "")
{
query = "SELECT data.*, sensor.*" +
"FROM data INNER JOIN sensor " +
"ON data.sensor = sensor.id " +
"WHERE sensor.id = " + idSensor + " " +
"AND (data_date BETWEEN '" + startString + "' AND '" + endString + "') " +
"ORDER BY data_date ASC";
List <Dictionary <String, String> > resultset = new List <Dictionary <string, string> >();
int lenResultset;
try
{
resultset = DBInteractor.QuickSelect(query);
}
catch (Exception ex)
{
MessageBox.Show("ERREUR : Impossible de se connecter à la base de données...\n\r\n\r" +
ex.Message + "\n\r" + ex.StackTrace);
}
lenResultset = resultset.Count;
foreach (Dictionary <String, String> line in resultset)
{
chTempHumid.Series["Temperatures"].Points.AddXY(line["data_date"], line["temperature"]);
chTempHumid.Series["Humidité"].Points.AddXY(line["data_date"], line["humidity"]);
}
}
}
private void dgvSensor_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == 4)
{
Form form = this.ParentForm;
MainForm main = (MainForm)form;
int row = e.RowIndex;
object object_id = dgvSensor.Rows[row].Tag;
String id = (String)object_id;
DialogResult result = MessageBox.Show("Etes vous sur de vouloir supprimer ce capteur ? Toutes les données y étant attaché vont être aussi supprimé ! "
, "Confirmation de suppresion", MessageBoxButtons.YesNo);
if (result == DialogResult.No)
{
return;
}
String queryData = "DELETE FROM data WHERE sensor = " + id;
String querySensor = "DELETE FROM sensor WHERE id = " + id;
DBInteractor.QuickExecute(queryData);
DBInteractor.QuickExecute(querySensor);
refreshSensor();
main.refreshSensorMain();
DisplaySensor();
}
if (e.ColumnIndex == 3)
{
int row = e.RowIndex;
object object_id = dgvSensor.Rows[row].Tag;
String id = (String)object_id;
List <Dictionary <String, String> > resultSensor = DBInteractor.QuickSelect("SELECT * FROM sensor WHERE id = " + id);
msltfLabelSensor.Text = resultSensor[0]["label"];
msltfWebServiceSensor.Text = resultSensor[0]["webservice"];
msltfUIDSensor.Text = resultSensor[0]["uid"];
idSensor = resultSensor[0]["id"];
}
}
public void ExportDataPDF(string filePath)
{
FileStream fs = new FileStream(filePath, FileMode.Create);
// Create an instance of the document class which represents the PDF document itself.
Document doc = new Document(PageSize.A4, 25, 25, 30, 30);
// Create an instance to the PDF file by creating an instance of the PDF
// Writer class using the document and the filestream in the constructor.
PdfWriter writer = PdfWriter.GetInstance(doc, fs);
// Open the document to enable you to write to the document
doc.Open();
// Add a simple and wellknown phrase to the document in a flow layout manner
doc.Add(new Paragraph("Rapport de données relatives à un capteur"));
doc.Add(new Paragraph("" + Environment.NewLine));
Form form = this.ParentForm;
MainForm main = (MainForm)form;
SynthesisForm synthesis = main.getSynthesis();
doc.Add(new Paragraph("Nom du capteur : " + main.getSynthesis().tfLabel.Text));
doc.Add(new Paragraph("UID du capteur : " + main.getSynthesis().tfUID.Text));
doc.Add(new Paragraph("Date et heure du premier relevé : " + main.getSynthesis().tfdtStart.Text));
doc.Add(new Paragraph("Date et heure du dernier relevé : " + main.getSynthesis().tfdtEnd.Text));
doc.Add(new Paragraph("Nombre de relevés : " + main.getSynthesis().tfNbr.Text));
doc.Add(new Paragraph("Amplitude temporelle des relevés : " + main.getSynthesis().tfAmplitude.Text));
doc.Add(new Paragraph("Température Minimum / Moyenne / Maximum : " + main.getSynthesis().mlMinTempData.Text + " / " +
main.getSynthesis().mlMedTempData.Text + " / " +
main.getSynthesis().mlMaxTempData.Text));
doc.Add(new Paragraph("Humidité Minimum / Moyenne / Maximum : " + main.getSynthesis().mlMinHumidData.Text + " / " +
main.getSynthesis().mlMedHumidData.Text + " / " +
main.getSynthesis().mlMaxHumidData.Text));
MemoryStream chTempHumidImageBuffer = synthesis.getCHTempHumidImageBuffer();
iTextSharp.text.Image iImage = iTextSharp.text.Image.GetInstance(chTempHumidImageBuffer.ToArray());
iImage.ScaleToFit(doc.PageSize);
iImage.SetAbsolutePosition(0, 250);
doc.Add(iImage);
doc.NewPage();
PdfPTable table = new PdfPTable(5);
table.HorizontalAlignment = 0;
//leave a gap before and after the table
table.SpacingBefore = 20f;
table.SpacingAfter = 30f;
PdfPCell cell0 = new PdfPCell(new Phrase("Rapport"));
cell0.Colspan = 5;
cell0.Border = 0;
cell0.HorizontalAlignment = 1;
table.AddCell(cell0);
table.AddCell("Nom du capteur");
table.AddCell("UID du capteur");
table.AddCell("Date-heure du relevé");
table.AddCell("Température");
table.AddCell("Humidité");
DateTime start = main.GetStartDate();
DateTime end = main.GetEndDate();
String startString = start.ToString("yyyy-MM-dd HH:mm:ss");
String endString = end.ToString("yyyy-MM-dd HH:mm:ss");
String idSensor = main.getSensor();
String query;
String optionalClause = "WHERE 1 ";
if (idSensor != "")
{
optionalClause = "WHERE sensor.id = " + idSensor + " ";
}
query = "SELECT sensor.*, data.*" +
"FROM sensor INNER JOIN data " +
"ON data.sensor = sensor.id " +
optionalClause + " " +
"AND (data_date BETWEEN '" + startString + "' AND '" + endString + "') " +
"ORDER BY sensor ASC, data_date ASC";
List <Dictionary <String, String> > resultset = new List <Dictionary <string, string> >();
try
{
resultset = DBInteractor.QuickSelect(query);
}
catch (Exception ex)
{
MessageBox.Show("ERREUR : Impossible de se connecter à la base de données...\n\r\n\r" +
ex.Message + "\n\r" + ex.StackTrace);
}
foreach (Dictionary <String, String> line in resultset)
{
table.AddCell(line["label"].ToString());
table.AddCell(line["uid"].ToString());
table.AddCell(line["data_date"].ToString());
table.AddCell(line["temperature"].ToString());
table.AddCell(line["humidity"].ToString());
}
doc.Add(table);
// Close the document
doc.Close();
try
{
// Close the writer instance
writer.Close();
// Always close open filehandles explicity
fs.Close();
Thread.Sleep(1000);
}
catch (Exception ex)
{
MessageBox.Show("Erreur à l'enregistrement: " + ex.Message, "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void SendMail()
{
try
{
String query;
query = "SELECT * FROM configuration ";
List <Dictionary <String, String> > resultset = new List <Dictionary <string, string> >();
try
{
resultset = DBInteractor.QuickSelect(query);
}
catch (Exception ex)
{
MessageBox.Show("ERREUR : Impossible de se connecter à la base de données...\n\r\n\r" +
ex.Message + "\n\r" + ex.StackTrace);
}
String mailUser;
String mailPassword;
mailUser = resultset[0]["key"];
mailPassword = resultset[0]["value"];
MailMessage mail = new MailMessage();
SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");
String destAddress = tfInputEmail.Text;
if (!Function.IsValidEmailAddress(destAddress))
{
throw new Exception("Veuillez saisir une adresse mail valide !");
}
mail.From = new MailAddress("*****@*****.**");
mail.To.Add(destAddress);
Form form = this.ParentForm;
MainForm main = (MainForm)form;
String idSensor = main.getSensor();
if (mrbPDF.Checked)
{
mail.Subject = "Rapport PDF relatif au capteur '" + idSensor + "'";
mail.Body = "Ci-joint les relevés du capteur '" + idSensor + "' au format PDF";
}
else
{
mail.Subject = "Données CSV (compatible Excel) relatif au(x) capteur(s) sélectionné(s)";
mail.Body = "Ci-joint les relevés relatif au(x) capteur(s) sélectionné(s) au format CSV, compatible avec le logiciel Excel ou LibreOffice Calc.";
}
mail.Attachments.Add(new Attachment(filePath));
SmtpServer.Port = 587;
SmtpServer.Credentials = new System.Net.NetworkCredential(mailUser, mailPassword);
SmtpServer.EnableSsl = true;
SmtpServer.Send(mail);
mail.Attachments[0].Dispose();
Thread.Sleep(1000);
MessageBox.Show("Mail envoyé avec succès");
}
catch (Exception ex)
{
MessageBox.Show("Erreur lors de l'envoi: " + ex.Message, "ERREUR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static List <Dictionary <String, String> > QuickSelect(String query)
{
Dictionary <String, String> parameters = new Dictionary <string, string>();
return(DBInteractor.QuickSelect(query, parameters));
}
public void DisplaySynthesis()
{
Form form = this.ParentForm;
MainForm main = (MainForm)form;
DateTime start = main.GetStartDate();
DateTime end = main.GetEndDate();
String startString = start.ToString("yyyy-MM-dd HH:mm:ss");
String endString = end.ToString("yyyy-MM-dd HH:mm:ss");
String idSensor = main.getSensor();
String labelSensor;
String query;
String queryTemp;
String queryHumid;
String uidSensor;
tfUID.Text = tfLabel.Text = tfdtStart.Text = tfdtEnd.Text = tfNbr.Text = mlMinTempData.Text = mlMaxTempData.Text = mlMedTempData.Text = mlMinHumidData.Text = mlMaxHumidData.Text = mlMedHumidData.Text = tfAmplitude.Text = "";
if (idSensor != "")
{
query = "SELECT data.*, sensor.*" +
"FROM data INNER JOIN sensor " +
"ON data.sensor = sensor.id " +
"WHERE sensor.id = " + idSensor + " " +
"AND (data_date BETWEEN '" + startString + "' AND '" + endString + "') " +
"ORDER BY data_date ASC";
queryTemp = "SELECT MIN(temperature) AS Tmin, MAX(temperature) AS Tmax, AVG(temperature) AS Tmed from data INNER JOIN sensor ON data.sensor = sensor.id WHERE sensor.id = " + idSensor;
queryHumid = "SELECT MIN(humidity) AS Hmin, MAX(humidity) AS Hmax, AVG(humidity) AS Hmed from data INNER JOIN sensor ON data.sensor = sensor.id WHERE sensor.id = " + idSensor;
List <Dictionary <String, String> > resultset = new List <Dictionary <string, string> >();
List <Dictionary <String, String> > resultTemp = new List <Dictionary <string, string> >();
List <Dictionary <String, String> > resultHumid = new List <Dictionary <string, string> >();
String dtStart = "";
String dtEnd = "";
String amplitude = "";
Decimal tempMin;
Decimal tempMax;
Decimal tempMed;
Decimal humidMin;
Decimal humidMax;
Decimal humidMed;
int lenResultset;
try
{
resultset = DBInteractor.QuickSelect(query);
resultTemp = DBInteractor.QuickSelect(queryTemp);
resultHumid = DBInteractor.QuickSelect(queryHumid);
}
catch (Exception ex)
{
MessageBox.Show("ERREUR : Impossible de se connecter à la base de données...\n\r\n\r" +
ex.Message + "\n\r" + ex.StackTrace);
}
lenResultset = resultset.Count;
//Si pas de data liées au capteur alors renvoi juste l'uid et le libellé du capteur sélectionné
if (lenResultset == 0)
{
uidSensor = DBInteractor.QuickSelect("SELECT uid FROM sensor WHERE sensor.id = " + idSensor)[0]["uid"].ToString();
labelSensor = DBInteractor.QuickSelect("SELECT label FROM sensor WHERE sensor.id = " + idSensor)[0]["label"].ToString();
tempMin = tempMax = tempMed = humidMin = humidMax = humidMed = 0;
}
else
{
uidSensor = resultset[0]["uid"];
// requête avec ORDER BY ASC --> 1ere ligne du resultset comprend la data la plus ancienne
labelSensor = resultset[0]["label"];
dtStart = resultset[0]["data_date"].ToString();
// requête avec ORDER BY ASC --> dernière ligne du resultset comprend la data la plus récente
dtEnd = resultset[lenResultset - 1]["data_date"].ToString();
tempMin = decimal.Parse(resultTemp[0]["Tmin"]);
tempMax = decimal.Parse(resultTemp[0]["Tmax"]);
tempMed = decimal.Parse(resultTemp[0]["Tmed"]);
humidMin = decimal.Parse(resultHumid[0]["Hmin"]);
humidMax = decimal.Parse(resultHumid[0]["Hmax"]);
humidMed = decimal.Parse(resultHumid[0]["Hmed"]);
TimeSpan tsAmplitude = DateTime.Parse(dtEnd).Subtract(DateTime.Parse(dtStart));
amplitude = string.Format("{0:dd\\ \\j\\o\\u\\r\\s\\ hh\\:mm\\:ss}", tsAmplitude);
}
tfUID.Text = uidSensor;
tfLabel.Text = labelSensor;
tfdtStart.Text = dtStart;
tfdtEnd.Text = dtEnd;
tfNbr.Text = lenResultset.ToString();
tfAmplitude.Text = amplitude;
mlMinTempData.Text = tempMin + "°C";
mlMaxTempData.Text = tempMax + "°C";
mlMedTempData.Text = Math.Round(tempMed, 1) + "°C";
mlMinHumidData.Text = humidMin + "%";
mlMaxHumidData.Text = humidMax + "%";
mlMedHumidData.Text = Math.Round(humidMed, 1) + "%";
chTemp_Load();
}
}