]c @s8ddlZddlZddlZddlZddlZddlZddlmZddlmZddlj Z ddl Z ddl Z ddlZejdddddejd d d d4d YZd d5dYZdd6dYZdZdZdZdZdZdZdZdZdZdZdZdZdZ dZ!dZ"d Z#d!Z$d"Z%d#Z&d$Z'd%Z(d&Z)d'Z*d(Z+d)Z,d*Z-d+Z.d,Z/d-Z0d.Z1d/Z2d0Z3d1Z4d2Z5d3Z6dS(7iN(tdatetime(t timedeltatformats'%(asctime)s %(levelname)-8s %(message)stfilenames/var/www/epg/log.txttleveltdatefmts%Y-%m-%d %H:%M:%StProvidercBseZdZRS(cCsd|_d|_dS(Nt(tidtname(tself((s/var/www/epg/epg_db.pyt__init__s (t__name__t __module__R (((s/var/www/epg/epg_db.pyRstChannelcBseZdZRS(cCs:d|_d|_d|_d|_d|_d|_dS(NR(RtcallSigntaffiliateCallSignR tnumbertimageUrl(R ((s/var/www/epg/epg_db.pyR s      (R R R (((s/var/www/epg/epg_db.pyRstProgramcBseZdZRS(cCsCd|_d|_d|_d|_d|_d|_d|_dS(NRitNA(ttitletNonet startTimet durationMinst descriptionRtgenrestcast(R ((s/var/www/epg/epg_db.pyR $s      (R R R (((s/var/www/epg/epg_db.pyR#scCsYtjdddtj}|jdt}|jdt|j|j dS(Ntusertroottpasswordtbuffereds use zvepg( tmariadbtconnecttsystem_definestmariaDbPasswordtcursortTruetexecutetcreateDbTablestcommittclose(tmariadb_connectionR$((s/var/www/epg/epg_db.pytupdateDb.s   cCstjd}tjdjtj|tjdddtj}|j dt }y|j dWntj k rnX|j d|j d t dS( Ns %Y%m%d-%H%M%Ss0mysqldump -u root -p{} zvepg > zvepg_save_{}.sqlRRRRsdrop database zvepgs+CREATE DATABASE zvepg CHARACTER SET ='utf8's use zvepg(ttimetstrftimetostsystemRR"R#R R!R$R%R&tErrorR'(ttimestrR*R$((s/var/www/epg/epg_db.pytresetDb@s  cCsjtjdddtj}|jdt}|jdd}y|j|Wn>tjk r}|jdkrt j dj |qnXd }y|j|Wn>tjk r}|jdkrt j dj |qnXd }y|j|Wn>tjk rA}|jdkrBt j dj |qBnXd }y|j|Wn>tjk r}|jdkrt j dj |qnXd }y|j|Wn>tjk r}|jdkrt j dj |qnXd }y|j|Wn>tjk rI}|jdkrJt j dj |qJnXd}y|j|Wn>tjk r}|jdkrt j dj |qnXd}y|j|Wn>tjk r}|jdkrt j dj |qnXd}y|j|Wn>tjk rQ}|jdkrRt j dj |qRnX|j |j dS(NRRRRs use zvepgsCREATE TABLE `providers` (`providerName` varchar(64), `country` varchar(64), `postalCode` char(32), `providerId` varchar(64), constraint mapId primary key (country, postalCode, providerId))is sql error: {}sCREATE TABLE `providerChannelMap` (`providerId` varchar(64), `channelId` varchar(64), `channelNumber` varchar(10), constraint mapId primary key (providerId, channelId))sCREATE TABLE `channels` (`channelId` varchar(64) primary key not null, `callSign` varchar(64), `affiliateCallSign` varchar(64), `name` varchar(64), `imageUrl` varchar(64) default '')s\CREATE TABLE `integrators` (`name` varchar(64) primary key not null, `password` varchar(32))sqCREATE TABLE `epgs` (`integrator` varchar(64),`name` varchar(64), `type` char(32) default 'IP', `country` varchar(64), `postalCode` char(32), `epgId` char(32), `backgroundColor` char(16) default '#101010', `evenRowColor` char(16) default '#303030', `oddRowColor` char(16) default '#505050', `fontColor` char(16) default '#b0b0b0', constraint mapId primary key (epgId))s|CREATE TABLE `epgProviderMap` (`epgId` char(32), `providerId` varchar(64), constraint mapId primary key (epgId, providerId))sCREATE TABLE `selectedChannels` (`epgId` char(32), `channelId` varchar(64), `providerId` varchar(64), `localChannelNumber` varchar(10) default '', `contentUrl` varchar(132) default '', constraint mapId primary key (epgId, providerId, channelId))sgCREATE TABLE `selectedChannelsLastChangeId` (`epgId` char(32) primary key not null, `lastChangeId` int)sCREATE TABLE `channelPrograms` (`id` int primary key auto_increment, `channelId` varchar(64), `name` varchar(64), `startTime` datetime, `durationMins` int, `description` varchar(512), `imageUrl` varchar(64), `genres` varchar(64), `cast` varchar(64))(R R!R"R#R$R%R&R0terrnotsystexitRR(R)(R*R$tsqlte((s/var/www/epg/epg_db.pyR'Qsv  cCsidd6}t|dkr7d|dR*R$R6trow((s/var/www/epg/epg_db.pytcheckIntegratorPasswords(           cCsidd6}t|dkr7d|dR*R$R6((s/var/www/epg/epg_db.pytchangeIntegratorPasswords&          cCs idd6}tjdddtj}|jdt}|jddj|}|j||jd krd |dR*R$R6((s/var/www/epg/epg_db.pyt delIntegrators,            cCsidd6gd6}tjdddtj}|jdt}|jdd }|j|x|D]}|dj|qhW|j|j |S( NR8R9t integratorsRRRRs use zvepguSELECT name FROM integrators( R R!R"R#R$R%R&tappendR(R)(R>R*R$R6R ((s/var/www/epg/epg_db.pytgetIntegrators#s     cCs!idd6dddgd6}|S(NR8R9tUSAtCANtGBRt countries((R>((s/var/www/epg/epg_db.pyt getCountries4sc CsYidd6|d6|d6gd6}|dkr[tjd|sd|dR*R$R6RURVtprovidert providersSrvt providerSrv((s/var/www/epg/epg_db.pyR\9s\"                 "    c Cs"t||||||||t S(N(t addChangeEpgR%(tepgIdRHR ttypeRRRStproviderIdsStrt newProviderOk((s/var/www/epg/epg_db.pyt changeEpgusc Cs"t||||||||t S(N(R`RZ(RaRHR RbRRRSRcRd((s/var/www/epg/epg_db.pytaddEpgysc  CsIidd6|d6|d6|d6|d6|d6} tjdt|d krgd | d 63 charactersR;tIPtRFsEPG type not RF or IPRPsCountry s not supportedt;isNo providerIds providedRRRRs use zvepgu select * from epgs WHERE name=%ss Name existsRTtnoneRUu0select * from epgProviderMap WHERE providerId=%stfalsesInvalid providerId, {}tErrNewProviders$New Provider and newProviderOk falset newProvidersu)SELECT * FROM integrators where name='{}'sInegrator does not existsRs{:0>3}-{:0>3}-{:0>3}iu!SELECT * FROM epgs where epgId={}sDuplicate EPG IDueUPDATE epgs SET integrator = %s, name = %s, type = %s, country = %s, postalCode = %s WHERE epgId = %su+DELETE FROM epgProviderMap where epgId='{}'s4EPG_DB.PY: addChangeEpg -- change deleting existing udinsert into epgs(integrator, name, type, country, postalCode, epgId) VALUES (%s, %s, %s, %s, %s, %s)sart/ZeeVee-Logo.pngsart/epgIcons/epg_t_icons/EPG_DB.PY: addChangeEpg -- adding provider '{}'u*INSERT INTO epgProviderMap VALUES (%s, %s)u<SELECT 1 FROM providerChannelMap WHERE providerId=%s LIMIT 1sFailed to contact serviceuZINSERT INTO channels(channelId, callSign, affiliateCallSign, name) VALUES (%s, %s, %s, %s)uXINSERT INTO providerChannelMap(providerId, channelId, channelNumber) VALUES (%s, %s, %s)tnumChannelsToUpdate(!tloggingtdebugR<RQtsplitR R!R"R#R$R%R&R=R\RZRKRtrandomtrandintR0tshutiltcopyfileR[tgetProviderChannelsR)RRRR RtincrementLastChangeIdR(tstartUpdateAllChannelsInfo(RaRHR RbRRRSRcRdtchangeR>RPt providerIdsR*R$R6Rmt allProviderst epgProvidersRUt providerMatchR]tchannelstchannel((s/var/www/epg/epg_db.pyR`}s0                         9     " "     0'      cCsidd6dd6}t|dkr>d|dR*R$R6((s/var/www/epg/epg_db.pyt validateEpg"s"        cCsidd6}tjdddtj}|jdt}|jddj|}|j||jd krd |dR*R$R6RU((s/var/www/epg/epg_db.pytdelEpg:s,            cCsidd6gd6}tjdddtj}|jdt}|jd|jdt}|jdd j|}y|j|Wn)tjk rd |dR*R$tcursor2R6R RbRRRSRatepgRURVRT((s/var/www/epg/epg_db.pytgetEpgs_s8        -    c Csidd6}tjdddtj}|jdt}|jddj|}|j||jd krd |d 63 characterssName > 63 characterssLocal_{}_{}_{}it t_t channelIdu1SELECT channelId FROM channels where channelId=%sisLocal channel already existssart/localChannelIcons/RnsEPG_DB.PY: ImageURL {}Ruginsert into channels(channelId, callSign, affiliateCallSign, name, imageUrl) values(%s, %s, %s, %s, %s)RRu\insert into providerChannelMap(providerId, channelId, channelNumber) values(%s, %s, 'Local')(R R!R"R#R$R%R&RR=R<RsRttreplaceRpRqRxR(R)( RaRR R>R*R$R6RRRU((s/var/www/epg/epg_db.pytaddLocalChannelsN         !        c Csidd6}tjdddtj}|jdt}|jddj|}|j||jd krd |d 63 characterssName > 63 charactersRu1SELECT channelId FROM channels where channelId=%st ChanNotExistsLocal channel does not existsart/localChannelIcons/RnsEPG_DB.PY: ImageURL {}RuAUPDATE channels SET callSign = %s, name = %s WHERE channelId = %s(R R!R"R#R$R%R&RR=R<RpRqtupdateChannelProgramsRxR(R)( RaRRR R>R*R$R6R((s/var/www/epg/epg_db.pytchangeLocalChannelsF                 cCs:idd6}tjdddtj}|jdt}|jddj|}|j||jd krd |dR*R$R6((s/var/www/epg/epg_db.pytdeleteLocalChannels4          c Csidd6gd6}tjdddtj}|jdt}|jd|jd d j|}|j||jd krd |dnXd}i}|j||f|jd krxU|D]J\}}} y||rnWqtt ||| ||t||R*R$R6REttmpIconRTRURV((s/var/www/epg/epg_db.pytgetEpgChannelssP            c Cs|jdt}|jd|jdd}y|j||fWn>tjk r}g|dR$R6R7RRRR t channelNumberRRRR((s/var/www/epg/epg_db.pyRwBsF    %               cCsidd6}g}g}yg|D]}|jd^q#}g|D](}t|dkrEt|d^qE}g|D]"}t|dkrz|d^qz}Wn)tk r}tjdj|nXtt|}t|t|kst|t|kr)d|d;;[] where localChannelNumber is non-zero and uniqueR;RRRRs use zvepgu&SELECT name FROM epgs WHERE epgId='{}'iRsEPG does not existu-DELETE FROM selectedChannels WHERE epgId='{}'sEPG_DB.PY: providerId=schanId=s locChan=s url=uwINSERT INTO selectedChannels (epgId, providerId, channelId, localChannelNumber, contentUrl) VALUES (%s, %s, %s, %s, %s)s(EPG_DB.PY: Set selectedChannels error {}(RrR<tfloatt ValueErrorRpRqRtsortedtsetR R!R"R#R$R%R&R=R0RRxR(R)(RatselectedChannelInfoR>t localNumst contentUrlstxt channelInfotvalR7R*R$R6RURRR((s/var/www/epg/epg_db.pytsetEpgChannelszsJ "530       )#   cCs|jdt}|jdd}|j||f|jdkrld}d}|j|||fn<|j}t|dd}d}|j|||f|S(NRs use zvepguESELECT lastChangeId FROM selectedChannelsLastChangeId WHERE epgId=%s iiuNINSERT INTO selectedChannelsLastChangeId (epgId, lastChangeId) values (%s, %s)uIUPDATE selectedChannelsLastChangeId SET lastChangeId = %s WHERE epgId=%s (R$R%R&R=RDtint(R*RaR$R6t lastChangeIdRE((s/var/www/epg/epg_db.pyRxs  cCstjdddtj}|jdt}|jdtjdd}|j||j dkrtjd t j d n|j S( NRRRRs use zvepgsIEPG_DB.PY: startingUpdateAllChannelsInfo -- checking if some need updatesu?SELECT channelId FROM channels WHERE name = '' or imageUrl = ''is.EPG_DB.PY: Starting update on needed channels s\echo '/usr/bin/python /var/www/epg/epg.py --update_all_channels_info' | at now &> daemon.txt( R R!R"R#R$R%R&RpRqR=R.R/(R*R$R6((s/var/www/epg/epg_db.pyRys    cCsxidd6}tjdtjdddtj}|jdt}|jdd }|j||j |d <|S( NR8R9s-EPG_DB.PY: updateAllChannelProgs starting... RRRRs use zvepgu?SELECT channelId FROM channels WHERE name = '' or imageUrl = ''Ro( RpRqR R!R"R#R$R%R&R=(R>R*R$R6((s/var/www/epg/epg_db.pytgetUpdateAllChannelsInfoStatuss     c Cslidd6}tjdddtj}|jdt}|jdt}|jd|jdtjdd }|j|d }x|D]\}|j d d krqnd j |GHt j |}|j }|j} tjd j ||| d}|j||d | d |f|d7}|dd kr|jqqW|j|j|S(NR8R9RRRRs use zvepgs EPG_DB.PY: UPDATE all channels!!u?SELECT channelId FROM channels WHERE name = '' or imageUrl = ''iRs{}u3EPG_DB.PY: UPDATE channel={}, name={}, imageURL={}u=UPDATE channels SET name = %s, imageUrl=%s WHERE channelId=%si?ii (R R!R"R#R$R%R&RpRqtfindRR[tgetChannelInfoR RR(R)( R>R*R$RR6tcommitSkipCountRRR R((s/var/www/epg/epg_db.pytupdateAllChannelsInfos4       !   c Cs|jdt}|jdd}|j||f|j}tdd}tdd}tjjddd dd d|}g}xtddD]r} t } |d| _ |j d | _ d| _ d| _d| _d| _d| _|j| ||7}qW|S(NRs use zvepgu,SELECT name FROM channels WHERE channelId=%sthoursii t microseconditsecondtminutei0is%Y-%m-%dT%H:%MZi<sLocal ProgrammingRi3(R$R%R&RDRRtutcnowRtrangeRRR-RRRRRRRK( R*RR$R6RtoneHourt beforeHourst startDatetprogramstitprogram((s/var/www/epg/epg_db.pytcreateLocalPrograms s*  (        c Csb|jdt}|jdtjdt|jddkrZt||}ntj |}dj |}y|j|Wn,t j k r}tjdj |nXx|D]}t j|jd}t|jd kr|jd d n|j}d }|j|||jd ||j|d |jd |jd |jd fqWdS(NRs use zvepgs)EPG_DB.PY: entering updateChannelProgramsRiu0DELETE FROM channelPrograms WHERE channelId='{}'s.EPG_DB.PY: updateChannelProgs delete error: {}s%Y-%m-%dT%H:%MZiis...uINSERT INTO channelPrograms (channelId, name, startTime, durationMins, description, imageUrl, genres, cast) values (%s, "%s", %s, %s, "%s", %s, "%s", "%s")i>(R$R%R&RpRqRRRR[t getProgramsRR R0RtstrptimeRR<RRRRRR( R*RR$RR6R7tprogRR((s/var/www/epg/epg_db.pyR(s"   /cCs$idd6}tjdddtj}|jdt}|jdt}|jd|jdtjdd }|j|x,|D]$\}d j |GHt ||qWd }|j|x4|D],\}}d }|j||d |fqW|j |j tjd|S(NR8R9RRRRs use zvepgs-EPG_DB.PY: updateAllChannelProgs starting... u/SELECT DISTINCT channelId FROM selectedChannelsschan {}u<SELECT lastChangeId, epgId FROM selectedChannelsLastChangeIduJUPDATE selectedChannelsLastChangeId SET lastChangeId = %s WHERE epgId = %sis)EPG_DB.PY: updateAllChannelProgs complete( R R!R"R#R$R%R&RpRqRRR(R)(R>R*R$RR6RRRa((s/var/www/epg/epg_db.pytupdateAllChannelProgramsFs*         c Cs(idd6}tjdddtj}|jdt}|jdd|d }d|d } ytj|| Wnt k rnXd j |} |j| |j d krd |dR*R$RtpermIconR6((s/var/www/epg/epg_db.pyt saveEpgConfigds,      /   cCsidd6gd6}yt|}Wn&tk rLd|dtlastChangeIdReqR*R$R6RERRt prevChannelRRt channelNameRRtchannelImageUrlRRtprogNameRRRt progImageUrlRRR((s/var/www/epg/epg_db.pytgetEpgProgramss                  4                  c Cstjdddtj}|jdt}|jdd}|j||jdkrddGHnHd d }|jd d d ddd}|GHdjdt |GHx=|D]5\}}}}} } |j||||| | GHqW|j |j dS(NRRRRs use zvepguCSELECT integrator, name, type, country, postalCode, epgId FROM epgsisThere are no epgss{:>15}it IntegratortNametTypetCountrys Postal-codetepgIDs{}t=( R R!R"R#R$R%R&R=RR<R(R)( R*R$R6t row_formattheaderRHR RbRRRSRa((s/var/www/epg/epg_db.pytshowEpgss    ! cCstjdddtj}|jdt}|jdd}|j|x%|D]\}}dj||GHqTW|j|j dS(NRRRRs use zvepgu(SELECT callSign, channelId FROM channelsscallSign={}, id={}( R R!R"R#R$R%R&RR(R)(R*R$R6RR((s/var/www/epg/epg_db.pyt showChannelss   cCstjdddtj}|jdt}|jdd}|j|x1|D])\}}}}dj||||GHqTW|j|j dS(NRRRRs use zvepguCSELECT providerId, providerName, country, postalCode FROM providerss-providerId={} name={} countr={} postalCode={}( R R!R"R#R$R%R&RR(R)(R*R$R6RURVRRRS((s/var/www/epg/epg_db.pyt showProviderss   c Cstjdddtj}|jdt}|jddj|}|GH|j|x@|D]8\}}}}}}} dj|||||| GHqbW|j|j dS(NRRRRs use zvepguSELECT providerId, channels.channelId, callSign, affiliateCallSign, name, channelNumber, imageUrl FROM providerChannelMap JOIN channels ON providerChannelMap.channelId = channels.channelId WHERE providerId='{}' sLchannelId={}, callSign={}, affiliate={}, name={}, channelNumber={}, image={}( R R!R"R#R$R%R&RR(R)( RUR*R$R6RRRR RR((s/var/www/epg/epg_db.pytshowProviderChannels!s   "  ((((7R.RuRpR,RWR4RRtmysql.connectort connectorR t epg_graceR[RsR"t basicConfigtDEBUGRRRR+R2R'R?RFRGRIRLRQR\ReRfR`RRRRRRRRwRRxRyRRRRRRRRRRR(((s/var/www/epg/epg_db.pytsh             _    !   <    % $ 5 2 & 2 8 5    %     i