Синхронизация веб страницы с Google Docs

в 5:53, , рубрики: Google, Google Docs, linux, mysql, php, метки: , , ,

Я работаю в организации, которая занимается аутсорсингом по всей России, в том числе монтажом СКС в магазинах наших заказчиков. Заказов на монтаж в последнее время стало намного больше и был создан новый отдел, который занимается мониторингом этих открытий. Мне была поставлена задача разработать небольшой сайт для мониторинга данных работ. На сайте должны быть счетчики обратного отчета, отображающее время до открытия магазина, строка с данными об этом магазине (заказчик, бренд, город, адрес, номер заявки в нашей основной информационной системе, тип работ и дата открытия), а вся эта информация на сайте должна браться из таблицы, находящийся на Google docs.

Подготовка

Я выбрал CentOS 6, как операционную систему для машины, на которой я буду разворачивать веб сервер. В качестве веб сервера я выбрал apache2, так как я с ним больше знаком. В качестве СУБД было выбрано MySql, так как таблицы мы будем использовать небольшие, поэтому я считаю, что это самый оптимальный выбор. Я не буду описывать установку CentOS, apache, MySql, так как такие посты уже есть на хабре.

Меняем конфиг MySql /etc/my.cnf: 

[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

Я использовал кодировку latin1, так как с другими кодировками я не смог добиться правильного отображения русских символов.

Установим пароль для root на MySql:
mysqladmin -u root password sqlpass
password — пароль системный
sqlpass — пароль который будет установлен на в ход в MySql

Заходим в консоль MySql:

mysql -u root -p'sqlpass'

Создаем дазу данных:

CREATE DATABASE `otkritiya`;

Создаем таблицы:

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
);

В таблицу ololo будут загружаться данные из Google docs. Количество столбцов таблицы MySql должно быть равно количеству столбцов таблицы Google docs. В таблицу ot будут добавляться данные из таблицы ololo, которые нам необходимо отображать на сайте.

Начнем

Синхронизация с Google docs.
Так как я плохо знаю php я решил задачу синхронизации Google docs и MySql при помощи bash скрипта и демона crond
Создадим папку:

mkdir /home/google

Выкачиваем файл в формате txt с Google docs в папку /home/googl с именем test.txt (что бы скачать файл с Google docs нужно в конце ссылки после слова sharing добавить output=txt):

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

Переносим файл в папку нашей базы данных:

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

Меняем права на этот файл:

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

Без этого у меня не получалось выгрузить данные из файла в таблицу Mysql.

Выгружаем данные из файла в таблицу: 

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

Так как в начале моего файла есть две строки, которые мне не нужны в базе я использую «IGNORE 2 LINES»
Теперь добавляем в таблицу ot необходимые данные из таблицы ololo:

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);"

Подробно по поводу последней команды: используем конструкцию select insert для вставки данных из одной таблицы в другую, функция STR_TO_DATE(ololo.wo, '%d.%m.%Y') преобразует текст в формате дд.мм.гггг в дату в формате гггг-мм-дд, условия для select два: 1) ololo.qy <> '' — поле с номером заявки не пустое и 2) ololo.qy not in (select ot.ticket from ot where ot.ticket is not null) — в этом условие мы проверяем, что номер заявки из таблицы ololo не совпадает c номером заявки в таблице ot, чтобы избежать дублирования строк.

В итоге мы получили всю необходимую информацию из Google docs в таблицы ot.
Автоматизируем данный процесс, так как документ Google docs постоянно дополняется.

Мой bash скрипт:

wget --no-check-certificate --output-document=/home/google/test.txt 'https://docs.google.com/spreadsheet/ccc?key=0Aq38OdEhOxztdG81eXczMjJObE9TMEttS182aXl2VWc&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

В моем скрипте постоянно обнуляется не только таблица ololo, но и таблица ot, так как в моем случае возможен вариант не только дополнения информации в таблицы Google docs, но и изменения существующей (mysql -u root -p'sqlpass' -e «truncate table otkritiya.ot;» mysql -u root -p'sqlpass' -e «truncate table otkritiya.ololo;»).

Создаем саму веб страницу.

Вот пример моей страницы index.php:

<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>

Было необходимо, чтобы счетчик считал в режиме реального времени, а не после обновления страницы, поэтому пришлось использовать java скрипт (я не знаком с java скриптами, поэтому тот что я использую здесь, я подсмотрел на каком то сайте). Скрипту для работы необходимо передавать: день, месяц, год, время. День, месяц и год передаются скрипту из таблицы ot с помощью php скрипта ololo.php, а время задано вручную, так как оно не меняется 10:0:00. Мне было необходимо на одной странице использовать несколько счетчиков и я не нашел более хорошего способа для этого, кроме как добавить для каждого счетчика свой отдельный div.

Пример моего php скрипта для передачи данных java скрипту:

<?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"
?>

С помощью функций DAYOFMONTH, MONTH, YEAR мы получаем номер дня, месяца и года. Условие TO_DAYS(NOW()) — TO_DAYS(data) <= 2 нужно, чтобы получать только те строки, где дата не старше, чем два дня назад. Сортировка order by data asc нужна чтобы сортироваться по дата от меньшего к большему.Limit 0,1 выдает только одну первую строку (в следующих файлах первая цифра будет меняться что бы брать из этого же запроса только вторую строчку например 1,1 и т.д.).

Пример моего php скрипта, показывающего строку с данными (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> |";

— делаем номер заявки ссылской на корпоративную информационную систему.На выходе я получил страницу со счетчиком и строкой данных, но счетчиков и строк данных мне надо вывести на страницу 45 штук для этого необходимо сделать 45 копий файлов ololo.php, bebebe.php и countdown.js с несколькими изменениями, этот процесс я автоматизировал при помощи скриптов:

Копирование:

#!/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

Замена значения limit:

#!/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

Теперь можно зайти на веб страницу ваш_ip/ и посмотреть результат.

Автор: evil75

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js