Intership report Exchange programme IUT A Lille 1 2013

Laurène Ratajczak

COMPUTERIZED TEMPERATURE MONITORING SYSTEM AND WEB BASED FEEDBACK SYSTEM

BACHELOR´S THESIS | ABSTRACT TURKU UNIVERSITY OF APPLIED SCIENCES Exchange program IUT A Lille 1 French teacher : Patrick Lebegue Finnish teacher : Tiina Ferm

Laurène Ratajczak

TURUN AMMATTIKORKEAKOULU THESIS To validate my DUT Informatique at the University Lille 1, I had to make an intership. I choose to make this intership in Finland, at the Turku Unviersity of Applied Sciences. The goal of this projet was to manage the temperature of the Hakastaronkatu 15 residence and developing a feedback system from the occupants. During this project, I had to make a database to store the measure and develop some PHP page to manage it. I also had to make a feedback system for the occupants of the Hakastonkatu 15 appartments. Then, I had to make an automatic system to take temperature measure with a Raspberry Pi. It’s a part of the HEA project, a bigger project about the wellbeing and the energy efficiency in living.

KEYWORDS: Feedback, Computerized temperature monitoring system, temperature, web, PHP, Raspberry Pi

OPINNÄYTETYÖ (AMK) | TIIVISTELMÄ TURUN AMMATTIKORKEAKOULU Programme d’échange IUT A Lille 1 Professeur français : Patrick Lebegue Professeur finlandais : Tiine Ferm

Laurène Ratajczak

OPINNÄYTETYÖN NIMI Résumé Français : Pour valider mon DUT Informatique à l’Univeristé Lille 1, j’ai du faire un stage. J’ai choisit de faire ce stage à l’Université des Sciences appliquée de Turku. Le principal objectif de ce projet était de créer un système de gestion de température pour les appartement de la résidence Hakastaronkatu 15, ainsi que de dévelloper un système de feedbacks pour les résidents. Durant ce projet, j’ai du créer une base de donnée pour stocker les mesures de températures, et développer plusieurs pages PHP pour les gérer. J’ai aussi du créer un sytème de feedback pour les habitants de la résidence. Enfin, j’ai du mettre en place un sysytème de mesure automatique de températures avec un Rapsberry Pi. Ce projet fait parti du projet HEA, qui est un plus grand projet sur le bien être et l’énergie.

ASIASANAT: Mots clefs : Feedback, gestion de températures, température, web, PHP, Raspberry Pi

CONTENT LIST OF ABBREVIATIONS (OR) SYMBOLS

6

ACKNOWLEDGMENT

7

1 PRESENTATION

6

1.1 Context

6

1.2 Work environment

6

1.2.1 Hardware

6

1.2.2 Sofware

8

1.3 Internships missions

10

2 MISSIONS

11

2.1 Database

11

2.1.1 Temperatures data

11

2.1.2 Information pages data

12

2.1.3 Feedback System data

13

2.1.4 Other data

13

2.2 Information page with useful information for students and interface to manage it. 14 2.2.1 Overview.

14

2.2.2 Information page for students.

14

2.2.3 Management interface

15

2.3 Feedback system for Hakastaronkatu 15 residence.

17

2.3.1 Overview

17

2.3.2 Light feedback system integrated to the Sparnet login page.

17

2.3.3 More complete feedback form.

19

2.4 Administration interface for house janitor and temperature measuring.

20

2.4.1 Overview

20

2.4.2 Temperature measuring on a Raspberry Pi

20

2.4.3 Administration interface for house janitor

22

3 IMPROVEMENTS

25

3.1 Hard skills.

25

3.2 Soft skills.

25

SOURCE MATERIAL

26

APPENDICES Appendix 1. Sparknet login page screen Appendix 2. Information page management interface screen Appendix 3. Python program code source

PICTURES Picture 1: Ubuntu logo .................................................................................................. 7 Picture 2 : Raspberry Pi logo ........................................................................................ 7 Picture 3 : Problem list on the information page .......................................................... 14 Picture 4 : Solution of the selected problem on the information page .......................... 15 Picture 5 : States of stars on the feedback form .......................................................... 18

FIGURES Figure 1: Temperature line chart made with the Raspberry Pi measures

23

LIST OF ABBREVIATIONS (OR) SYMBOLS

PHP

PHP stands for :PHP:Hypertext Preprocessor, is a serverside scripting language to make dynamics web page or general applications.

HTML

HTML, which stands for HyperText Markup Language, is the predominant markup language for web pages.

CSS

CSS (Cascading Style Sheets)(CSS) is a style sheet for web pages.

GPIO

General Purpose Input/Output, are input and output ports on computers.

RTC

Real Time Clock.

.

ACKNOWLEDGMENT First of all, I wanted to thank all people who help me to make this intership, and who participate to making this experience rewarding. First, I want to thank my finnish tutor, Tiina Ferm who help me during the entire project. Then, I thank my french tutor, Patrick Lebegue, for his support. I want to thank the other people who work with me on this project too, in particular Ojala Olli, who help me for the sensor and the Raspberry Pi. Too finish, thank you at Reetta Partala and all the International Office, for their friendly welcome.

6

1 PRESENTATION 1.1

Context

This intern ship was made to valid my DUT Informatique at the IUT A of the Université Lille 1, France. It takes place on the Turku University of Applied Science, on the city of Salo. I mainly worked on the Salon AMK building.

My previous skills, acquired during my 2 years formation, were mainly programming skills. I knew some programming languages like Java, C, C++, Python for example. I had also skills on databases and analysis. For example, I knew the SQL languages, which was very useful on this internship. I had project management skill too, that help me a lot to organize my work all along this 3 month internship.

During this period, I had to work with a team which already begins to work on this project, taking temperature measure. I developed alone, under the responsibility of my teacher, Tiina Ferm, who met me every week to help me and check my work.

1.2

Work environment

1.2.1 Hardware 1.2.1.1 Operating system All the PHP pages were developed with my own computer, under the Ubuntu operating system (UNIX system).

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

7

Picture 1: Ubuntu logo

1.2.1.2 Raspberry Pi Raspberry pi is a simple credit-card-sized computer, developed for promoting the teaching of computer sciences. It’s a computer who permitted a large choice of application thank to its low cost and its little size. In this project, I use the Raspberry Pi the take temperature measure by connecting it with temperature sensor.

Picture 2 : Raspberry Pi logo

1.2.1.3 DS18s20 temperature sensor The temperature measure was taken thank to a DS18s20 temperature sensor connected to the Raspberry Pi. It’s a little 1-Wire digital temperature sensor which take measure without interruptions.

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

8

1.2.2 Sofware 1.2.2.1 Languages PHP : The main part of my development was on the PHP language. I used PHP language for 2 main raisons. On one hand, my work is to develop feedback system and administrator interface used through internet navigator and connected with a database. PHP is a very well adapted language to work on a web server. One the other hand, the feedback system is made to be included on the Spark net login page, already coded in PHP, so I need to use this language to avoid every compatibility problems. Python : I’ve also had to make a Python program for the raspberry Pi. I used Python because it already included on the Raspberry Pi and I already knew this language thanks to a previous project made during my third semester studies. Python is a high-level programming language with a very simple syntax, so it very simple to use it, even on the Raspberry Pi which I didn’t work with before. 1.2.2.2 Tools X.A.M.P.P for Unix : XAMPP is an easy to install Apache distribution with MySQL and PHP included. It give the most useful tool to configure a web server quickly and without problem. It also included phpMyAdmin, which is a Web database management to manage easily MySQL databases.

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

9

PHPlot : PHPlot is a free graph library for dynamic chart. It permitted to make lot of sort of chart, like line-chars or pie-chart. 1.2.2.3 Other softwares used Text editors : PHP and Python are not compilated languages, so you don’t need a very complete environment to develop with these languages. That’s why I mainly worked with simple text editors. On my own computer, I used Emacs, which is a powerful text editor on Linux. Emacs have a syntax highlighting very useful. On the raspberry Pi, I used the simple leafpad editor, already included on the operating system. It’s a very light editor, that’s why I prefer use it, because the raspberry isn’t very powerful. Web browsers : I tested all my PHP pages with the navigators Firefox, Chromium and Internet Explorer on my own computer, and with Midori navigator on the Raspberry Pi.

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

10

1.3

Internships missions

I had many missions during this internship.

First, I had to desing and implement a database for the entire project. There was no existing database and all data are messy. For example, temperature measures were taken manually and write on simple sheet of paper.

Secondly, I had to develop an information page which gathers all the useful information for exchange student, and an interface to manage it in an easy way.

Then, my tasks was to make a Feedback system for the apartments of the Hakastaronkatu 15 residence, which could be included on the Sparknet login page (see annexes), which is the page to access the residence network.

To finish, I had to make an automatic system to take temperature measures and an interface for the house janitor. So he can see the temperature measures and the feedbacks on a same place.

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

11

2 MISSIONS 2.1

Database

2.1.1 Temperatures data I had to make a database for data of the project. At the beginning, temperature measures are made with simple thermometers and stock on paper sheet. To make a computerized system, I needed to store these measures on a database. We measured the temperature of each apartment of Hakastaronkatu 15. There are 11 apartments, plus common parts like the laundry of the sauna. Each apartments is measured daily and can be measured more than on time by day. For that reason, I decided to store the date and the time with each measure, and the place where it was taken.

The schema of the Temperature table of my database is: 

Value_Temp: an integer with 5 numbers, 3 after the comma.



date_time_Temp : datetime, in the form yyyy-mm-dd hh:mm:ss (ex 201304-01 10:00:30)



Location_Temp : the precise measure location.



ID_Apartment : the unique ID of the apartment, a string with 20 char (varchar 10)

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

12

Each Appartment are stored on the Apartment table, which contains the following information: 

ID_Apartment : the unique ID of the apartment varchar(20)



Num_Apartment : The number of the Appartment



Adr1_Apartment : The address (1st line)



Adr2_Apartment : The address (2nd line, if it needed)



Town_Apartment : The town



Postal_Code_Apartment : The postal code

2.1.2 Information pages data Useful informations for students already exists, but I have to formalize them in order to store it on the database. A database is more convenient to insert, and edit information.

I order to simplify the information page for students, I formalize the information like this : 

Problem : Very short phrase to describe the problem



Quick help : Short answer to solve the problem quickly



Details : More details to solve the problem

So, naturally, the structure of the How_To table is : 

Problem : Primary Key, varchar(100)



Quick_Help : varchar(255)



Details : Text (mySQL type)

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

13

2.1.3 Feedback System data There is 2 way to give a feedback:

The light feedback system, with only 2 fields (apartment number and note). And the more complete feedback form with the apartment number, the precise location, the note, a field for other details, the priority and a contact field.

All these information are stored on the Feedback table, which is organized like this: 

ID_Feedback : an interger, primary key.



ID_Apartment : the unique ID of the Apartment.



Note_Feedback : The note given.



Location_Feedback : The precise location of the feedback, ex room 1a.



Remarks_Feedback : Text, the remarks given by the student.



Datetime_Feedback : The date of the feedback, on the form yyyy-mm-dd hh:mm:ss.



Urgent : Boolean, 0 if not urgent, 1 if urgent.



Contact_Feedback : Text, Contact information if the student want to be contacted.

2.1.4 Other data For this project, I've also stored data for the login system, indeed, there is some page visible only for the administrators.

That's why I've made a simple table Admins to store these data : 

Firstname_Admins : The administrator firstname.



Lastname_Admins : The administrator lastname.



Password_Admins : The administrator password, coded with the md2 hashcode algorithm like this : hashcode(login.password ) for the security.



Log_Admins : The administrator login.

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

14

2.2

Information page with useful information for students and interface to manage it.

2.2.1 Overview. This project part consists in making a simple page with useful information for exchange student of the University. The main goal is to put all the information in a same page, as simple as possible. On this page, students just have to choose his problem on the list, valid, and the solution of his problem is shown just above. To manage this page, I made an interface for the international coordinator of Salo, Reetta Partala. 2.2.2 Information page for students. The information page is a very simple page which gathers all useful data. These data are presented on the form: problem/quick help/details.

This page is divided in two parts. The first part, visible when you open the page, is a simple list with all the problems on the database and a validate button.

Picture 3 : Problem list on the information page

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

15

The second part of the page is visible as soon as you choose one problem and valid, it’s the solution of the selected problem.

Picture 4 : Solution of the selected problem on the information page

The list is made thanks to a simple request on the How_To table on the database. In the same way, the answer to the problem is found thanks to another request on this table, which search information matching with the selected problem.

All these process are made on the same PHP page.

2.2.3 Management interface The management interface permitted to the International Coordinator of Salo to easily add, edit or delete data on the database, even if he have no computing science skills.

The most difficult point of this part were to make the interface complete, but as simple as possible.

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

16

The page contains 2 different parts:

The top of the page contains a table with all the problems already put on the database. You can edit it on a simple click on the problem name.

The second part is a simple form, with 3 text fields. Respectively for the problem nature, the quick help, and the details. A screen of this interface is visible on the annexes.

When you add problem on the database, another PHP page is called to check if the problem already exist and make the insertion. Then, the user is redirected one the previous page. On the 2nd called page, the field Problem is checked. If the problem is already on the database, the previous entry is edit. Else, the new problem is inserted into the database.

To prevent from SQL injection, all queries used on this project are prepared queries. Its mean these queries are already optimized and pre-compiled.

I use also the PHP method htmlentities() which prevent form the HTML injection.

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

17

2.3

Feedback system for Hakastaronkatu 15 residence.

2.3.1 Overview This part consists in developing a feedback system for the apartments of the Hakastaronkatu 15 residence. There are 2 way to give a feedback, one quick way, thanks to the little form at the top of the Sparknet login page, and another, more complete.

Each feedback concerns one place in particular (one apartment or the commons parts, like laundry or sauna). The user has to give a note, between 1 and 5, to show his satisfaction. 1 is the worst note and 5 the best. This note is given by clicking on stars. I choose the star vote system to give a note because it’s a very visual way to give a note, and it doesn’t need a lot of place, of it can easily be integrated on other pages. 2.3.2 Light feedback system integrated to the Sparnet login page. The light feedback system is the first way to give a feedback. It’ll be integrated on the top of the Sparknet login page. It simply contains a list with all the apartments on the database and a star vote system. To give a feedback, user just have to choose his apartment and give an note thank to the stars, then, he clicks on the ‘ok’ button and the feedback is added to the database. The stars are links to the same page and just add a ‘vote’ parameter on the url, with the value of the note. For example, the first star add ‘?vote=1’ to the actual address.

When you click on the star, there is also a JavaScript method which is called to change the picture to a fill star.

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

18

Picture 5 : States of stars on the feedback form

If the ‘vote’ parameter is on the url, an hidden field is added to the form, with the value of the note, else, the value of the vote field is put at 1.

There is another hidden field which used to transfer the date and the time to the insertion php page : InsertVote.php. This page is called by validating the form with the button.

On this page, all parameter are checked. If the note value is under 1, it’s change into 1 instead. On the same way, if the value is more than 5, it’s the value 5 which is added on the database.

After all thess process, the user is redirected to the previous page and another parameter is added to the url, the insert parameter.

This parameter is used to check and inform the user if the vote has been correctly added to the database. This parameter had 2 value, ‘ok’ if the vote is correctly added, and ‘error’ else.

For example, if the vote is correctly add, the user is redirect to the page : LightFeedback.php?insert=ok If all is ok, an alert pop up with the message “thank for your vote ” is displayed, else, there is an error message instead.

This very light feedback system is supposed to be included on the Sparknet login page, so that every resident of Hakastaronkatu 15 can see it.

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

19

2.3.3 More complete feedback form. If he wants, the exchange student can also give a feedback with more details, concerning one room in particular, or if there is one important remark.

For this kind of things, there is another feedback form, more complete, which will be accessible thank to a link on the Sparknet login page.

This other form is very similar to the first one, but with some other field. The user can fill one or more fields on this form. The only required fields are the note and the apartment.

It work as the first one, a page is called by validating the form, checks the parameters, adds the vote on the database and redirect to the previous page with the parameter called ‘insert’. As the light feedback system, is parameter has the value ‘ok’ if the vote is correctly insert, or ‘error’ if an error has occurred.

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

20

2.4

Administration interface for house janitor and temperature measuring.

2.4.1 Overview This part consist in taking temperature measures with a temperature sensor connected to a raspberry Pi and making a web interface for the house janitor which sum up the measures and the feedbacks given by the residents.

I never used a Raspberry Pi before, so it takes me some time to adapt. Plus, I need to connected the sensor but I had no skill on hardware.

To connect the sensor and make the Python program which takes the measures, I helped myself with a tutorial from the University of Cambridge website.

To access the administration interface, you need to be logged in to the login page. If somebody try to access another page without being logged, he is automatically redirected to the login page. 2.4.2 Temperature measuring on a Raspberry Pi To take the temperatures measures, I used a DS18s20 temperature sensor connected to the GPIO ports of the raspberry. I had some difficulty to connect it because it was the first time I used these ports before, but I worked with a schema who explains where to connect the sensor.

Then I had to learn how to read the measure from the raspberry. Thanks to the tutorial from the University of Cambridge, it was quite easy.

The value of the measure is visible by reading a simple file.

This kind of sensor take measures without interruption, so I had to think about how many measures I would take by days.

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

21

At the beginning, I took one measure every 15 minutes but I realized that there were no big variations between the measures. That’s why I changed and took one measure per hour.

After learning how to read the measures and deciding how many measure I would take, I had to make a program to make it.

I made this program in Python because I already knew this language. This program makes an infinite loop and read the value of the measure on the file. Then, the value is added on the database with the date and the place. I read the place form an external .ini file so it’s very easy to change.

After the insertion, the program sleeps during 1 hour before taking another measure.

As the Raspberry has no RTC, you need to connect the Raspberry to the internet before starting the program.

Plus, there are some commands line to use before starting the program to make the connection between the Raspberry and the sensor, that’s why I made a simple bash script which execute these commands and then start the python program.

You can see the complete code of the python program on the annexes.

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

22

2.4.3 Administration interface for house janitor This is a most complete page I had to make. When you open this page, you are automatically redirected to the login page. I need to be log in to access this interface.

Then, the administrator is redirect to the home page of the interface.

At the top of this page, there is a menu, with links to add some data into the database, like news administrator or manual temperature measure. There also are link to logout and changes password.

Just below, there are others links to navigate on the page.

The most part of the page contains table, with various information. The first table shows averages, maximum and minimum temperatures for each apartment. The second show averages of feedback note for these apartments.

Then, there are two tables for the last feedback posted. The first concerns the urgent feedbacks, and the other concerns the non urgent feedbacks.

Each table is made in a separate php file included on the Admin.php page. I made it for make the code cleaner.

To have more details about one specific apartment, the administrator has to click on the number of the apartment.

On the top of the details page, there is one menu with some link to return to the main page, or navigate on the page. These links are also at the bottom of the page.

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

23

Then, the page itself is composed of 4 parts.

The first part is a little sum up table, with the temperatures maximum and minimum, and the averages of measures values and feedbacks.

The second is a graph, made thank to the PHPlot open library. On this line chart, you can see the variations on the temperature values during the current day. The 3rd part of this page is another graph with the average of the temperatures for the current month per day. Thanks to a list, you can see the temperatures graph for the previous months.

Figure 1: Temperature line chart made with the Raspberry Pi measures

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

24

The last part is composed of 2 tables which show the urgent feedbacks in one table, and the normal feedbacks in the other table.

These feedbacks are only feedbacks concerning the selected apartment for the last 30 days.

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

25

3 IMPROVEMENTS 3.1

Hard skills.

I learned lot of things during this project. Concerning the hard skills, I new a new langagues, PHP. Plus, I prefect my other programming skills by developing an entire project, from the beginning to the end. I knew lot of useful things about databases management, like how to make a complete database script, or how making regular saves of the database is important. I also learn how to use some groupware tools like Google Drive, to share quickly my work. Too finish, working in another country make me improuve my English language. 3.2

Soft skills.

On the Soft skills side, I mainly learn about self management. Indeed, I had more independence than during my studies, so I had to learn how manage my time and my work, making schedule and respect it. Then, I also learn some usefull things about communication. During this project, I had to show my work to many people, like my teacher, my co-workers or future users. So I had to adapt my presentation to other people. For example, I understood that I should not use technical words in front of people with no computer science skills. Finally, I learned another way to work, and also another way to live, in a different country.

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

26

SOURCE MATERIAL PHP documentation : http://www.php.net/manual/fr/index.php PHPlot offical page : http://phplot.sourceforge.net/ Raspberry Pi official website :http://www.php.net/manual/fr/index.php CSS, HTML and Javascript documentation :http://www.w3schools.com/ Mysql documentation : http://dev.mysql.com/doc/ Python documentation : http://docs.python.org/3/ Tutorial from the University of Cambridge : www.cl.cam.ac.uk/projects/raspberrypi/tutorials/temperature/

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

Appendix

Sparknet login page screen

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

Appendix

Information page management interface screen

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak

Appendix

Python program source code : import MySQLdb import ConfigParser import time #read iniFile config=ConfigParser.ConfigParser() config.read("/home/pi/Desktop/TemperatureSensor.ini") #connection database db = MySQLdb.connect(config.get('Database', 'Host'), config.get('Database', 'User'), config.get('Database', 'Password'), config.get('Database', 'DatabaseName')) cursor = db.cursor() while True : tempFile = open("/sys/bus/w1/devices/"+config.get('Sensor', 'Serial_number')+"/w1_slave") content = tempFile.read() tempFile.close tempData = content.split("\n")[1].split(" ")[9] tempValue = float(tempData[2:]) tempValue = tempValue / 1000 print tempValue query = "insert into Temperature(date_time_Temp, ID_Apartment, Location_Temp, Value_Temp) values(now(), '"+config.get('Data', 'Place')+"','',"+str(tempValue)+" );" try: cursor.execute(query) db.commit() except: db.rollback() time.sleep(3600)

TURKU UNIVERSITY OF APPLIED SCIENCES THESIS | Laurène Ratajczak