Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Sample of script to manage future schedule json #234

Open
cooliobr opened this issue Nov 10, 2022 · 21 comments
Open

Sample of script to manage future schedule json #234

cooliobr opened this issue Nov 10, 2022 · 21 comments

Comments

@cooliobr
Copy link

cooliobr commented Nov 10, 2022 •
edited by jb-alvarado 

#!/bin/bash
## MySQL
MyUSER="USER"                 # DB_USERNAME
MyPASS="PASS"            # DB_PASSWORD
MyHOST="IP_HOST"        # DB_HOSTNAME
DB_NAME="DB"                  # DB_NAME

USER=$1
PASS=$2
CHANNEL=$3
DAY=$4
RED='\033[0;31m'
YELLOW='\033[1;33m'
GREEN='\033[0;32m'
WHITE='\033[1;37m'
GRAY='\033[1;30m'
NC='\033[0m' # No Color


TOKEN=`curl -s -X POST http://127.0.0.1:8787/auth/login/ -H 'Content-Type: application/json' -d '{ "username": "'$USER'", "password": "'$PASS'" }'| jq . | grep token | cut -d ':' -f2 | sed 's/\"//g' | sed 's/\ //g'`

echo -e "${RED}Token: ${YELLOW}$TOKEN ${NC}"
echo -e "\n"

EPG=`curl -s -X GET http://127.0.0.1:8787/api/playlist/1?date=$DAY -H 'Content-Type: application/json' -H "Authorization: Bearer $TOKEN" | jq -c '.program[] | select(.category!="advertisement" )' | sed 's/\/var\/lib\/ffplayout\/tv-media\///g'`
## Cria arquivo do dia

## Loop de leitura
while read line;
do
        FILE=`echo $line | cut -d ":" -f5 | cut -d "}" -f1 | sed 's/\"//g'`
        TIME=`echo $line | cut -d ":" -f4 | cut -d "," -f1`
        CONVERTED=`date -d@$TIME -u +%H:%M:%S`
        nome=$(MYSQL_PWD="$MyPASS" mysql -u $MyUSER $DB_NAME -h $MyHOST -e "SELECT nome FROM videos WHERE arquivo = '$FILE' LIMIT 1;"  -B --skip-column-names | grep -v "Using a password")
        descricao=$(MYSQL_PWD="$MyPASS" mysql -u $MyUSER $DB_NAME -h $MyHOST -e "SELECT descricao FROM videos WHERE arquivo = '$FILE' LIMIT 1;"  -B --skip-column-names | grep -v "Using a password")
        background=$(MYSQL_PWD="$MyPASS" mysql -u $MyUSER $DB_NAME -h $MyHOST -e "SELECT background FROM videos WHERE arquivo = '$FILE' LIMIT 1;"  -B --skip-column-names | grep -v "Using a password")
        video_id=$(MYSQL_PWD="$MyPASS" mysql -u $MyUSER $DB_NAME -h $MyHOST -e "SELECT id FROM videos WHERE arquivo = '$FILE' LIMIT 1;"  -B --skip-column-names | grep -v "Using a password")
        # Set data
        echo -e "${GREEN}Programa:${NC}                 $nome"
        echo -e "${GREEN}Sinopse:${NC}                  $descricao"
        echo -e "${GREEN}Capa:${NC}                     $background"
        echo -e "${GREEN}Id:${NC}                       $video_id"
        echo -e "${YELLOW}Time in sec:${NC}                   $TIME"
        echo -e "${GRAY}Time in HH:MM:SS :${NC}   $CONVERTED"
        echo -e "${GREEN}Arquivo:${NC}          $FILE"
        echo -e ""

done < <(curl -s -X GET http://127.0.0.1:8787/api/playlist/1?date=$DAY -H 'Content-Type: application/json' -H "Authorization: Bearer $TOKEN" | jq -c '.program[] | select(.category!="advertisement" )' | sed 's/\/var\/lib\/ffplayout\/tv-media\///g')

For each record different of advertisement in playlist, it will get file name and search in database: name, description, images and any other information that you have stored to this file.
@linnikvv
Copy link

Hello. Thank you very much for this script. Could you make a detailed guide on setting up and using this script. I will be very grateful to you)

@cooliobr
Copy link
Author

Hi, first, this scipt work only in linux or OSX with MySQL but you can use any other database and remember, this is a basic script.
in Linux:
sudo apt install jq mysql-client
in OSX:
brew reinstall jq mysql-client

Create a database in MySQL.
MySQL> create database epg;
Create table or tables.
This is my case, you need to modify to your use.

CREATE TABLE `videos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tmdb_id` int(10) DEFAULT NULL,
  `nome` varchar(220) DEFAULT NULL,
  `descricao` varchar(1024) DEFAULT NULL,
  `midia` int(3) DEFAULT NULL,
  `temporada` int(2) NOT NULL DEFAULT '1',
  `episodio` int(4) NOT NULL DEFAULT '1',
  `tempo` time DEFAULT NULL,
  `arquivo` varchar(256) DEFAULT NULL,
  `image` varchar(128) DEFAULT NULL,
  `rating` varchar(10) DEFAULT NULL,
  `cast` varchar(1024) DEFAULT NULL,
  `diretor` varchar(50) DEFAULT NULL,
  `ano` int(5) DEFAULT NULL,
  `country` varchar(128) DEFAULT NULL,
  `star` int(5) DEFAULT NULL,
  `category` varchar(128) DEFAULT NULL,
  `channel` int(1) NOT NULL DEFAULT '1',
  `background` varchar(256) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `enabled` varchar(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

After this, you need to populate this table with your media informations, you will need to modify script to your use, as i told before, it is my use case. With this script you will can generate xmltv format if you modify, more about xmltv format you can find here http://wiki.xmltv.org/index.php/XMLTVFormat

@linnikvv
Copy link

Infelizmente o script não funciona. Eu criei o arquivo epg.sh, mas recebo um erro ao executá-lo.
2022-11-11_16-08-02

@cooliobr
Copy link
Author

Infelizmente o script não funciona. Eu criei o arquivo epg.sh, mas recebo um erro ao executá-lo. 2022-11-11_16-08-02

Instala o jq
sudo apt install jq

@linnikvv
Copy link 

#!/bin/bash
## MySQL
MyUSER="USER"                 # DB_USERNAME
MyPASS="PASS"            # DB_PASSWORD
MyHOST="IP_HOST"        # DB_HOSTNAME
DB_NAME="DB"                  # DB_NAME

USER=$1
PASS=$2
CHANNEL=$3
DAY=$4
RED='\033[0;31m'
YELLOW='\033[1;33m'
GREEN='\033[0;32m'
WHITE='\033[1;37m'
GRAY='\033[1;30m'
NC='\033[0m' # No Color


TOKEN=`curl -s -X POST http://127.0.0.1:8787/auth/login/ -H 'Content-Type: application/json' -d '{ "username": "'$USER'", "password": "'$PASS'" }'| jq . | grep token | cut -d ':' -f2 | sed 's/\"//g' | sed 's/\ //g'`

echo -e "${RED}Token: ${YELLOW}$TOKEN ${NC}"
echo -e "\n"

EPG=`curl -s -X GET http://127.0.0.1:8787/api/playlist/1?date=$DAY -H 'Content-Type: application/json' -H "Authorization: Bearer $TOKEN" | jq -c '.program[] | select(.category!="advertisement" )' | sed 's/\/var\/lib\/ffplayout\/tv-media\///g'`
## Cria arquivo do dia

## Loop de leitura
while read line;
do
        FILE=`echo $line | cut -d ":" -f5 | cut -d "}" -f1 | sed 's/\"//g'`
        TIME=`echo $line | cut -d ":" -f4 | cut -d "," -f1`
        CONVERTED=`date -d@$TIME -u +%H:%M:%S`
        nome=$(MYSQL_PWD="$MyPASS" mysql -u $MyUSER $DB_NAME -h $MyHOST -e "SELECT nome FROM videos WHERE arquivo = '$FILE' LIMIT 1;"  -B --skip-column-names | grep -v "Using a password")
        descricao=$(MYSQL_PWD="$MyPASS" mysql -u $MyUSER $DB_NAME -h $MyHOST -e "SELECT descricao FROM videos WHERE arquivo = '$FILE' LIMIT 1;"  -B --skip-column-names | grep -v "Using a password")
        background=$(MYSQL_PWD="$MyPASS" mysql -u $MyUSER $DB_NAME -h $MyHOST -e "SELECT background FROM videos WHERE arquivo = '$FILE' LIMIT 1;"  -B --skip-column-names | grep -v "Using a password")
        video_id=$(MYSQL_PWD="$MyPASS" mysql -u $MyUSER $DB_NAME -h $MyHOST -e "SELECT id FROM videos WHERE arquivo = '$FILE' LIMIT 1;"  -B --skip-column-names | grep -v "Using a password")
        # Set data
        echo -e "${GREEN}Programa:${NC}                 $nome"
        echo -e "${GREEN}Sinopse:${NC}                  $descricao"
        echo -e "${GREEN}Capa:${NC}                     $background"
        echo -e "${GREEN}Id:${NC}                       $video_id"
        echo -e "${YELLOW}Time in sec:${NC}                   $TIME"
        echo -e "${GRAY}Time in HH:MM:SS :${NC}   $CONVERTED"
        echo -e "${GREEN}Arquivo:${NC}          $FILE"
        echo -e ""

done < <(curl -s -X GET http://127.0.0.1:8787/api/playlist/1?date=$DAY -H 'Content-Type: application/json' -H "Authorization: Bearer $TOKEN" | jq -c '.program[] | select(.category!="advertisement" )' | sed 's/\/var\/lib\/ffplayout\/tv-media\///g')

For each record different of advertisement in playlist, it will get file name and search in database: name, description, images and any other information that you have stored to this file.

Preciso fazer alterações neste arquivo além de conectar ao banco de dados?

@cooliobr
Copy link
Author 

#!/bin/bash
## MySQL
MyUSER="USER"                 # DB_USERNAME
MyPASS="PASS"            # DB_PASSWORD
MyHOST="IP_HOST"        # DB_HOSTNAME
DB_NAME="DB"                  # DB_NAME

USER=$1
PASS=$2
CHANNEL=$3
DAY=$4
RED='\033[0;31m'
YELLOW='\033[1;33m'
GREEN='\033[0;32m'
WHITE='\033[1;37m'
GRAY='\033[1;30m'
NC='\033[0m' # No Color


TOKEN=`curl -s -X POST http://127.0.0.1:8787/auth/login/ -H 'Content-Type: application/json' -d '{ "username": "'$USER'", "password": "'$PASS'" }'| jq . | grep token | cut -d ':' -f2 | sed 's/\"//g' | sed 's/\ //g'`

echo -e "${RED}Token: ${YELLOW}$TOKEN ${NC}"
echo -e "\n"

EPG=`curl -s -X GET http://127.0.0.1:8787/api/playlist/1?date=$DAY -H 'Content-Type: application/json' -H "Authorization: Bearer $TOKEN" | jq -c '.program[] | select(.category!="advertisement" )' | sed 's/\/var\/lib\/ffplayout\/tv-media\///g'`
## Cria arquivo do dia

## Loop de leitura
while read line;
do
        FILE=`echo $line | cut -d ":" -f5 | cut -d "}" -f1 | sed 's/\"//g'`
        TIME=`echo $line | cut -d ":" -f4 | cut -d "," -f1`
        CONVERTED=`date -d@$TIME -u +%H:%M:%S`
        nome=$(MYSQL_PWD="$MyPASS" mysql -u $MyUSER $DB_NAME -h $MyHOST -e "SELECT nome FROM videos WHERE arquivo = '$FILE' LIMIT 1;"  -B --skip-column-names | grep -v "Using a password")
        descricao=$(MYSQL_PWD="$MyPASS" mysql -u $MyUSER $DB_NAME -h $MyHOST -e "SELECT descricao FROM videos WHERE arquivo = '$FILE' LIMIT 1;"  -B --skip-column-names | grep -v "Using a password")
        background=$(MYSQL_PWD="$MyPASS" mysql -u $MyUSER $DB_NAME -h $MyHOST -e "SELECT background FROM videos WHERE arquivo = '$FILE' LIMIT 1;"  -B --skip-column-names | grep -v "Using a password")
        video_id=$(MYSQL_PWD="$MyPASS" mysql -u $MyUSER $DB_NAME -h $MyHOST -e "SELECT id FROM videos WHERE arquivo = '$FILE' LIMIT 1;"  -B --skip-column-names | grep -v "Using a password")
        # Set data
        echo -e "${GREEN}Programa:${NC}                 $nome"
        echo -e "${GREEN}Sinopse:${NC}                  $descricao"
        echo -e "${GREEN}Capa:${NC}                     $background"
        echo -e "${GREEN}Id:${NC}                       $video_id"
        echo -e "${YELLOW}Time in sec:${NC}                   $TIME"
        echo -e "${GRAY}Time in HH:MM:SS :${NC}   $CONVERTED"
        echo -e "${GREEN}Arquivo:${NC}          $FILE"
        echo -e ""

done < <(curl -s -X GET http://127.0.0.1:8787/api/playlist/1?date=$DAY -H 'Content-Type: application/json' -H "Authorization: Bearer $TOKEN" | jq -c '.program[] | select(.category!="advertisement" )' | sed 's/\/var\/lib\/ffplayout\/tv-media\///g')

For each record different of advertisement in playlist, it will get file name and search in database: name, description, images and any other information that you have stored to this file.

Preciso fazer alterações neste arquivo além de conectar ao banco de dados?

seu ffplayout é local?
instalou o mysql?
populou a tabela?
os dados do mysql como banco, tabelas e colunas, você deve deixar como o seu.

@linnikvv
Copy link

seu ffplayout é local? - Sim
você instalou o mysql? - Servidor remoto
completou a tabela? - Sim
dados mysql como banco de dados, tabelas e colunas, você deve deixá-los como seus. - Criei as tabelas de usuário e banco de dados

@linnikvv
Copy link

Preciso fazer alguma alteração nessas linhas?

USER=$1
PASS=$2
CHANNEL=$3
DAY=$4

2 similar comments
@linnikvv
Copy link

Preciso fazer alguma alteração nessas linhas?

USER=$1
PASS=$2
CHANNEL=$3
DAY=$4

@linnikvv
Copy link

Preciso fazer alguma alteração nessas linhas?

USER=$1
PASS=$2
CHANNEL=$3
DAY=$4

@cooliobr
Copy link
Author

Preciso fazer alguma alteração nessas linhas?

USER=$1
PASS=$2
CHANNEL=$3
DAY=$4

./epg.sh (user do ffplayout) (pass do ffplayout) ( id do canal ) (dia no formato AAAA-MM-DD)

@linnikvv
Copy link

2022-11-11_17-30-15

@linnikvv
Copy link

Por favor, escreva com mais detalhes a sequência de ações. Eu entendo pouco disso.
2022-11-11_17-30-15

@cooliobr
Copy link
Author

./epg.sh USER PASS 1 2022-11-01

@linnikvv
Copy link

./epg.sh ПРОПУСК ПОЛЬЗОВАТЕЛЯ 1 2022-11-01

2022-11-11_18-38-13

@cooliobr
Copy link
Author

da um cat no epg.sh, deve ter colado algo errado no arquivo pois a linha 1 tem
MyUSER="USER" # DB_USERNAME

@cooliobr
Copy link
Author

Gravacao.de.Tela.2022-11-11.as.13.49.09.mov

@linnikvv
Copy link

Gravacao.de.Tela.2022-11-11.as.13.49.09.mov

Can you tell me how to fix this error?
2022-11-11_19-03-29

@cooliobr
Copy link
Author

Gravacao.de.Tela.2022-11-11.as.13.49.09.mov

Can you tell me how to fix this error? 2022-11-11_19-03-29

https://bobcares.com/blog/mysql-dump-error-illegal-mix-of-collations-how-to-resolve/

@linnikvv
Copy link

Gravacao.de.Tela.2022-11-11.as.13.49.09.mov

Can you tell me how to fix this error? 2022-11-11_19-03-29

https://bobcares.com/blog/mysql-dump-error-illegal-mix-of-collations-how-to-resolve/

Obrigado pela ajuda. Outra dúvida é onde posso encontrar o arquivo epg e como fazer epg por 7 dias?

@cooliobr
Copy link
Author

para gerar o arquivo xmltv você terá de escrever a sua maneira o output desse script, quanto a fazer para 7 dias, o pessoal que mantém o ffplayout está desenvolvendo #201 mas não sei quando irão liberar essa modificação. no momento você pode gerar o XML colocando a data e hora de inicio de sua programação e ir somando o tempo de cada programa/video

@cooliobr cooliobr mentioned this issue Nov 13, 2022
Open
@cooliobr cooliobr mentioned this issue Oct 3, 2023
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@cooliobr @linnikvv