Synchronize a web page with Google Docs

I work in an organization that deals with outsourcing throughout Russia, including the installation of SCS in the stores of our customers. Installation orders have recently become much larger and a new department has been created that monitors these discoveries. I was given the task to develop a small site for monitoring these works. The website should have counters of the reverse report, which shows the time before the store opens, a line with data about this store (customer, brand, city, address, application number in our main information system, type of work and opening date), and all this information on the site must be taken from a spreadsheet located on google docs.

Training

I chose CentOS 6 as the operating system for the machine on which I will deploy the web server. As a web server, I chose apache2, since I am more familiar with it. MySql was chosen as the DBMS, since we will use small tables, so I think this is the most optimal choice. I will not describe the installation of CentOS, apache, MySql, since such posts are already on Habré.



Change the MySql /etc/my.cnf config:

[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql default-character-set=latin1 init-connect='SET NAMES latin1' character-set-server=latin1 init-connect='SET NAMES latin1' collation-server=latin1_general_cs skip-character-set-client-handshake #character_set_system=utf8 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysql] default-character-set=latin1 [client] default-character-set=latin1 

')

I used the latin1 encoding, as with other encodings I could not get the correct display of Russian characters.



Set a root password for MySql:

mysqladmin -u root password sqlpass

password - system password

sqlpass - the password that will be set to go to MySql



Go to the console MySql:

 mysql -u root -p'sqlpass' 

Create a database:

 CREATE DATABASE `otkritiya`; 

Create tables:

 use otkritiya CREATE TABLE `ot` ( `id` int(11) NOT NULL AUTO_INCREMENT, `zakazchik` varchar(100) DEFAULT NULL, `brend` varchar(100) DEFAULT NULL, `gorod` varchar(100) DEFAULT NULL, `adress` varchar(300) DEFAULT NULL, `ticket` varchar(50) NULL, `type` varchar(100) DEFAULT NULL, `data` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `ololo` ( `raz` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, `dva` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `tri` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qw` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qe` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qr` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qt` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL, `qy` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qa` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qs` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `sa` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qf` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qg` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qu` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qi` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qj` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qo` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qp` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qd` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qh` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qk` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `ql` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qz` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qx` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qc` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qv` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qb` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `nv` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qn` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `qm` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `we` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `wr` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `wt` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `wy` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `wu` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `wi` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `wo` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `wp` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `wa` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `ws` varchar(50) CHARACTER SET latin1 DEFAULT NULL, `f` char(50) CHARACTER SET latin1 DEFAULT NULL, `e` char(50) CHARACTER SET latin1 DEFAULT NULL, `c` char(50) CHARACTER SET latin1 DEFAULT NULL, `d` char(50) CHARACTER SET latin1 DEFAULT NULL ); 

The data from Google docs will be loaded into the ololo table. The number of columns in the MySql table should be equal to the number of columns in the Google docs table. The table ot will be added data from the table ololo, which we need to display on the site.

Let's start

Sync with Google docs.

Since I do not know php well, I solved the problem of synchronizing Google docs and MySql with the help of the bash script and the crond daemon

Create a folder:

 mkdir /home/google 

We download the file in txt format from Google docs to the / home / googl folder named test.txt (to download the file from Google docs, add output = txt at the end of the link after the word sharing):

 wget --no-check-certificate --output-document=/home/google/test.txt 'https://docs.google.com/spreadsheet/ccc?key=0Aq38OdEhOxztdG81eXcjJO82aXl2VWc&usp=sharing&output=txt' 

Transfer the file to our database folder:

 cp /home/google/test.txt /var/lib/mysql/otkritiya/ 

Change the rights to this file:

 chmod 777 /var/lib/mysql/otkritiya/test.txt 

Without this, I could not get the data from the file into the Mysql table.



Upload the data from the file to the table:

 mysql -u root -p'sqlpass' -e "LOAD DATA INFILE 'test.txt' INTO TABLE otkritiya.ololo IGNORE 2 LINES;" 

Since at the beginning of my file there are two lines that I do not need in the database, I use "IGNORE 2 LINES"

Now add the required data from the ololo table to the ot table:

 mysql -u root -p'sqlpass' -e "use otkritiya; insert into ot (zakazchik , brend , gorod , adress , ticket , type , data) select ololo.dva , ololo.tri , ololo.qe , ololo.qt , ololo.qy , ololo.qa , STR_TO_DATE(ololo.wo , '%d.%m.%Y') from ololo where ololo.qy <> '' and ololo.qy not in (select ot.ticket from ot where ot.ticket is not null);" 

Details on the last command: use the select insert construct to insert data from one table to another, the function STR_TO_DATE (ololo.wo, '% d.% M.% Y') converts text in dd.mm.yyyy format to a date in the format yyyy-mm-dd, there are two conditions for select: 1) ololo.qy <> '' - the field with the application number is not empty and 2) ololo.qy not in (select ot.ticket from ot where ot.ticket is not null) - in this condition we check that the order number from the table ololo does not match the number of the application in the table ot, in order to avoid duplication of lines.



As a result, we received all the necessary information from Google docs in tables ot.

We automate this process, as the Google docs document is constantly updated.



My bash script:

 wget --no-check-certificate --output-document=/home/google/test.txt 'https://docs.google.com/spreadsheet/ccc?key=0Aq38OdEhOxztdG81eXE9TMEttS182aXl2VWc&usp=sharing&output=txt' rm -rf /var/lib/mysql/otkritiya/test.txt cp /home/google/test.txt /var/lib/mysql/otkritiya/ chmod 777 /var/lib/mysql/otkritiya/test.txt mysql -u root -p'sqlpass' -e "truncate table otkritiya.ololo;" mysql -u root -p'sqlpass' -e "truncate table otkritiya.ot;" mysql -u root -p'sqlpass' -e "LOAD DATA INFILE 'test.txt' INTO TABLE otkritiya.ololo IGNORE 2 LINES;" mysql -u root -p'sqlpass' -e "use otkritiya; insert into ot (zakazchik , brend , gorod , adress , ticket , type , data) select ololo.dva , ololo.tri , ololo.qe , ololo.qt , ololo.qy , ololo.qa , STR_TO_DATE(ololo.wo , '%d.%m.%Y') from ololo where ololo.qy <> '' and ololo.qy not in (select ot.ticket from ot where ot.ticket is not null);" rm -rf /home/google/test.txt rm -rf /var/lib/mysql/otkritiya/test.txt 

In my script, not only the ololo table, but also the ot table is constantly reset, since in my case it is possible not only to add information to the Google docs tables, but also to change the existing one (mysql -u root -p'sqlpass' -e "truncate table otkritiya.ot; "mysql -u root -p'sqlpass' -e" truncate table otkritiya.ololo; ").



We create the web page itself.



Here is an example of my index.php page:

 <head> <meta http-equiv="refresh" content="300"> <title> </title> <style style="text/css"> #top_header{ width:1600px; margin:0 auto; height:300px; } #central_header{ margin:0 auto; height:800px; text-align:center; } .letter { color: #FFFFFF; } .count_down{ padding: 3px; font-family:Georgia, "Times New Roman", Times, serif; font-size:24px; border-bottom: 2px solid red; font-weight:900; color:#CD0000; } .count_down sup{ font-family:"Lucida Grande", "Lucida Sans Unicode", Verdana, Arial, Helvetica, sans-serif; font-size:11px; color:#CD0000; font-weight:900; } .text { color:#000000; text-align: center; } </style> <script type="text/javascript" src="js/jquery-1.3.2.min.js"></script> <script type="text/javascript" src="js/countdown.js"></script> <script type="text/javascript" src="js/countdown2.js"></script> </head> <body> <div class="text"> <h1>\ </h1> </div> <?php $nom = 0; $zak = 0; $bre = 0; $gor = 0; $adr = 0; $tic = 0; $typ = 0; $otk = 0; include 'bebebe.php'; ?> <div id="central_header"> <div id="count_down_container"></div> <script type="text/javascript"> var target_date=new cdtime("count_down_container", " <?php include 'ololo.php'; ?> 10:0:00") target_date.displaycountdown("days", displayCountDown) </script> <p><span class="letter">L</span></p> <?php $nom = 0; $zak = 0; $bre = 0; $gor = 0; $adr = 0; $tic = 0; $typ = 0; $otk = 0; include 'bebebe2.php'; ?> <div id="central_header"> <div id="count_down_container2"></div> <script type="text/javascript"> var target_date=new cdtime("count_down_container2", " <?php include 'ololo2.php'; ?> 10:0:00") target_date.displaycountdown("days", displayCountDown) </script> 

It was necessary that the counter read in real time, and not after updating the page, so I had to use the java script (I am not familiar with the java scripts, so the one that I use here, I spied on some kind of site). The script must be passed to work: day, month, year, time. The day, month and year are transferred to the script from the ot table using the ololo.php php script, and the time is set manually, since it does not change 10: 0: 00. It was necessary for me to use several counters on one page and I did not find a better way for this, except to add my own separate div for each counter.



An example of my php script to transfer data to the java script:

 <?php $host='localhost'; $database='otkritiya'; $user='root'; $pswd='sqlpass'; $dbh = mysql_connect($host, $user, $pswd); mysql_select_db($database); $query = "SELECT DAYOFMONTH(data) , MONTH(data) , YEAR(data) FROM `ot` WHERE TO_DAYS(NOW()) - TO_DAYS(data) <= 2 order by data asc limit 0,1"; $res = mysql_query($query); while($row = mysql_fetch_array($res)) { $ol = $row['DAYOFMONTH(data)']; $lo = $row['MONTH(data)']; $ye = $row['YEAR(data)']; }; echo "$lo $ol, $ye" ?> 

Using the functions DAYOFMONTH, MONTH, YEAR we get the number of the day, month and year. The condition TO_DAYS (NOW ()) - TO_DAYS (data) <= 2 is needed to receive only those lines where the date is not older than two days ago. Sorting order by data asc is needed to sort by date from smaller to larger. Limit 0.1 returns only one first line (in the following files, the first digit will change to take only the second line from the same query, for example, 1.1, etc. .).



An example of my php script showing the data string (bebebe.php):

 <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" /> <style style="text/css"> .color { color: #8B8378; front-family: 'Times New Roman', Times, serif; font-style: italic; font-size: 14pt; font-weight: normal; text-align: center; } .shir { color: #000000; front-family: 'Times New Roman', Times, serif; font-size: 14pt; font-weight: 900; text-align: center; } </style> </head> <body> <div class="shir"> <?php $host='localhost'; $database='otkritiya'; $user='root'; $pswd='sqlpass'; $dbh = mysql_connect($host, $user, $pswd); mysql_query('SET NAMES latin1'); mysql_select_db($database); $query = "select * from ot where TO_DAYS(NOW()) - TO_DAYS(data) <= 2 order by data asc limit 0,1"; $res = mysql_query($query); while($row = mysql_fetch_array($res)) { $nom = $row['id']; $zak = $row['zakazchik']; $bre = $row['brend']; $gor = $row['gorod']; $adr = $row['adress']; $tic = $row['ticket']; $typ = $row['type']; $otk = $row['data']; }; echo '<span class="color">№: </span>'; echo "$nom |"; echo '<span class="color">: </span>'; echo "$zak |"; echo '<span class="color">: </span>'; echo "$bre |"; echo '<span class="color">: </span>'; echo "$gor |"; echo '<span class="color">: </span>'; echo "$adr |"; echo '<span class="color">№ : </span>'; echo "<a href='http://componysite.com/task.jsp?id=".$tic."'>".$tic."</a> |"; echo '<span class="color"> : </span>'; echo "$typ |"; echo '<span class="color">: </span>'; echo "$otk"; ?> </div> </body> </html> 

 echo "<a href='http://componysite.com/task.jsp?id=".$tic."'>".$tic."</a> |"; 

- we make the application number of the reference to the corporate information system. At the exit, I received a page with a counter and a data line, but I need to output 45 counters and data lines to this page, you need to make 45 copies of the ololo.php, bebebe.php and countdown files. js with a few changes, I automated this process using scripts:



Copy:

 #!/bin/bash for i in 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 do cp ololo.php ololo$i.php cp bebebe.php bebebe$i.php cp js/countdown.js js/countdown$i.js done 

Replace the limit value:

 #!/bin/bash for i in 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 do a=$(($i - 1)) b=$(($i + 100)) sed "s/0,1/$a,1/g" bebebe.php > bebebe$b.php && mv bebebe$b.php bebebe$i.php done 

Now you can go to your_ip web page and see the result.