Rec10WEBG3/trunk/rec10webg3.py

   1 #!/usr/bin/python
   2 # coding: UTF-8
   3 # Rec10 TS Recording Tools
   4 # Copyright (C) 2009-2011 Yukikaze
   5 import cgi
   6 import os
   7 import os.path
   8 import re
   9 import datetime
  10 import ConfigParser
  11 import MySQLdb
  12 import time
  13 try:
  14     import simplejson as json
  15 except ImportError:
  16     import json
  17 #import cgitb
  18 #cgitb.enable()
  19
  20 path = str(os.path.dirname(os.path.abspath(__file__))) + "/"
  21 confp = ConfigParser.SafeConfigParser()
  22 Conf = 'rec10.conf'
  23 cpath=""
  24 if (os.path.exists(os.path.join(path,Conf))):
  25     cpath=os.path.join(path,Conf)
  26 elif (os.path.exists(os.path.join("/etc/rec10",Conf))):
  27     cpath=os.path.join("/etc/rec10",Conf)
  28 elif (os.path.exists(os.path.join("/etc",Conf))):
  29     cpath=os.path.join("/etc",Conf)
  30 confp.read(cpath)
  31 def getpath(string):
  32     global confp
  33     return confp.get('path', string)
  34 def getdbpath(string):
  35     global confp
  36     return confp.get('db', string)
  37 def getdb():
  38     retdb = ""
  39     if getdbpath('db') == 'MySQL':
  40         dbn = getdbpath("mysql_dbname")
  41         dbh = getdbpath("mysql_host")
  42         dbu = getdbpath("mysql_user")
  43         dbpwd = getdbpath("mysql_passwd")
  44         dbport = int(getdbpath("mysql_port"))
  45         #print [dbn,dbh,dbu,dbpwd,dbport]
  46         retdb = DB_MySQL(dbname=dbn, host=dbh, user=dbu, passwd=dbpwd, port=dbport)
  47     return retdb
  48 class DB_MySQL:
  49     dbname = ""
  50     dbhost = ""
  51     dbusr = ""
  52     dbpasswd = ""
  53     dbport = 0
  54     def __init__(self, dbname, user, passwd, host="localhost", port=3306):
  55         self.dbname = dbname
  56         self.dbhost = host
  57         self.dbusr = user
  58         self.dbpasswd = passwd
  59         self.dbport = port
  60     def connect_db(self):
  61         """
  62         dbへの接続
  63         """
  64         con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport,\
  65                         user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
  66         cur = con.cursor()
  67         cur.execute('set names utf8;')
  68         return [con, cur]
  69     def close_db(self, db):
  70         db[1].close()
  71         db[0].close()
  72
  73     ###timeline系
  74     def timeline2dic(self,timeline):
  75         ret={}
  76         ret['id']=timeline[0]
  77         ret['type']=timeline[1]
  78         ret['chtxt']=timeline[2]
  79         ret['title']=timeline[3]
  80         ret['btime']=timeline[4].strftime("%m/%d %H:%M")
  81         ret['etime']=timeline[5].strftime("%H:%M")
  82         ret['bt']=timeline[4]
  83         ret['et']=timeline[5]
  84         if not timeline[6]:
  85                         ret['deltatime']=timeline[6]
  86         else:
  87                         ret['deltatime']="3"
  88         if not timeline[7]:
  89                         ret['deltaday']=timeline[7]
  90         else:
  91                         ret['deltaday']="7"
  92         ret['opt']=timeline[8]
  93         ret['epgtitle']=timeline[9]
  94         ret['epgbtime']=timeline[10]
  95         ret['epgetime']=timeline[11]
  96         ret['epgduplicate']=timeline[12]
  97         ret['epgchange']=timeline[13]
  98         ret['epgexp']=timeline[14]
  99         ret['counter']=timeline[15]
 100         return ret
 101     def select_all_timeline(self):
 102         db = self.connect_db()
 103         recdata = []
 104         dl = db[1].execute("SELECT id,type,chtxt,title,btime,etime,deltatime,deltaday,opt,\
 105                                 epgtitle,epgbtime,epgetime,epgduplicate,epgchange,epgexp,counter FROM timeline ORDER BY btime")
 106         dls = db[1].fetchall()
 107         for line in dls:
 108             recdata.append(self.timeline2dic(line))
 109         self.close_db(db)
 110         return recdata
 111     def select_by_chtxt_timeline(self, chtxt):
 112         db = self.connect_db()
 113         recdata = []
 114         dl = db[1].execute("SELECT id,type,chtxt,title,btime,etime,deltatime,deltaday,opt,\
 115                 epgtitle,epgbtime,epgetime,epgduplicate,epgchange,epgexp,counter FROM timeline WHERE chtxt = %s ", (chtxt, ))
 116         d = db[1].fetchall()
 117         for line in dls:
 118             recdata.append(self.timeline2dic(line))
 119         self.close_db(db)
 120         return recdata
 121     def select_by_id_timeline(self, idt):
 122         db = self.connect_db()
 123         recdata = []
 124         dl = db[1].execute("SELECT id,type,chtxt,title,btime,etime,deltatime,deltaday,opt,\
 125                 epgtitle,epgbtime,epgetime,epgduplicate,epgchange,epgexp,counter FROM timeline WHERE id = %s ", (idt, ))
 126         d = db[1].fetchall()
 127         for line in dls:
 128             recdata.append(self.timeline2dic(line))
 129         self.close_db(db)
 130         return recdata
 131         def select_by_btime_etime_chtxt_timeline(self,btime,etime,chtxt):
 132                 db = self.connect_db()
 133         recdata = []
 134         dl = db[1].execute("SELECT id,type,chtxt,title,btime,etime,deltatime,deltaday,opt,\
 135         epgtitle,epgbtime,epgetime,epgduplicate,epgchange,epgexp,counter FROM timeline \
 136         WHERE btime >= %s AND etime <= %s AND chtxt = %s", (btime,etime,chtxt ))
 137         dls = db[1].fetchall()
 138         for line in dls:
 139             recdata.append(self.timeline2dic(line))
 140         self.close_db(db)
 141         return recdata
 142     def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
 143         db = self.connect_db()
 144         db[1].execute('INSERT IGNORE INTO timeline (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
 145             values (%s,%s,%s,%s,%s,%s,%s,%s)', (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
 146         self.close_db(db)
 147
 148
 149     ########epg_ch系
 150     def epg_ch2dic(self,epg_ch):
 151         ret={}
 152         ret['bctype']=epg_ch[0]
 153         ret['chtxt']=epg_ch[1]
 154         ret['ch']=epg_ch[2]
 155         ret['csch']=epg_ch[3]
 156         ret['chname']=epg_ch[4]
 157         ret['updatetime']=epg_ch[5]
 158         ret['status']=epg_ch[6]
 159         ret['visible']=epg_ch[7]
 160         if ret["chtxt"].split("_")[0]=="BS":
 161             ret["type"]="BS"
 162         elif ret["chtxt"].split("_")[0]=="CS":
 163             ret["type"]="CS"
 164         else:
 165             ret["type"]="TE"
 166         return ret
 167     def select_all_epg_ch(self):
 168         db = self.connect_db()
 169         r1 = db[1].execute("SELECT bctype,chtxt,ch,csch,chname,updatetime,status,visible FROM epg_ch")
 170         lines = db[1].fetchall()
 171         ret=[]
 172         for ls in lines:
 173             ret.append(self.epg_ch2dic(ls))
 174         self.close_db(db)
 175         return ret
 176     def select_by_chtxt_epg_ch(self, chtxt):
 177         db = self.connect_db()
 178         r1 = db[1].execute("SELECT bctype,chtxt,ch,csch,chname,updatetime,status,visible FROM epg_ch WHERE chtxt=%s", (chtxt, ))
 179         lines = db[1].fetchall()
 180         ret=[]
 181         for ls in lines:
 182             ret.append(epg_ch2dic(ls))
 183         self.close_db(db)
 184         return ret
 185
 186     ########epg_timeline系
 187     def epg_timeline2dic(self,epg_timeline):
 188         ret={}
 189         ret['bctype']=epg_timeline[0]
 190         ret['channel']=epg_timeline[1]
 191         ret['chtxt']=epg_timeline[1]
 192         ret['start']=datetime.datetime(*time.strptime(epg_timeline[2], "%Y%m%d%H%M%S")[:-3])
 193         ret['stop']=datetime.datetime(*time.strptime(epg_timeline[3], "%Y%m%d%H%M%S")[:-3])
 194         ret['bt']=ret['start']
 195         ret['et']=ret['stop']
 196         ret['btime']=ret['bt'].strftime("%Y-%m-%d %H:%M:%S")
 197         ret['etime']=ret['et'].strftime("%Y-%m-%d %H:%M:%S")
 198         ret['title']=epg_timeline[4]
 199         ret['exp']=epg_timeline[5]
 200         ret['longexp']=epg_timeline[6]
 201         ret['category']=epg_timeline[7]
 202         return ret
 203     def select_all_epg_timeline(self):
 204         db = self.connect_db()
 205         r1 = db[1].execute("SELECT bctype,channel,start,stop,title,exp,longexp,category FROM epg_timeline")
 206         lines = db[1].fetchall()
 207         ret=[]
 208         for ls in lines:
 209             ret.append(self.epg_timeline2dic(ls))
 210         self.close_db(db)
 211         return ret
 212     def select_by_chtxt_epg_timeline(self, chtxt):
 213         db = self.connect_db()
 214         r1 = db[1].execute("SELECT bctype,channel,start,stop,title,exp,longexp,category FROM epg_timeline WHERE channel=%s", (chtxt, ))
 215         lines = db[1].fetchall()
 216         ret=[]
 217         for ls in lines:
 218             ret.append(self.epg_timeline2dic(ls))
 219         self.close_db(db)
 220         return ret
 221     def select_by_btime_etime_chtxt_epg_timeline(self,btime,etime,chtxt):
 222         db = self.connect_db()
 223         start=datetime.datetime(*time.strptime(btime, "%Y-%m-%d %H:%M:%S")[:-3]).strftime("%Y%m%d%H%M%S")
 224         stop=datetime.datetime(*time.strptime(etime, "%Y-%m-%d %H:%M:%S")[:-3]).strftime("%Y%m%d%H%M%S")
 225         recdata = []
 226         dl = db[1].execute("SELECT bctype,channel,start,stop,title,exp,longexp,category FROM epg_timeline \
 227         WHERE start >= %s AND stop <= %s AND channel = %s", (start,stop,chtxt ))
 228         dls = db[1].fetchall()
 229         for line in dls:
 230             recdata.append(self.epg_timeline2dic(line))
 231         self.close_db(db)
 232         return recdata
 233     def select_by_keyword_title_epg_timeline(self,keyword="",chtxt="",btime="",etime=""):
 234         keyt=""
 235         chtxtt=""
 236         btimet=""
 237         etimet=""
 238         if len(keyword)>0:
 239             keyt=" AND title LIKE '%"+keyt+"%' "
 240         if len(btime)>0:
 241             start=datetime.datetime(*time.strptime(btime, "%Y-%m-%d %H:%M:%S")[:-3]).strftime("%Y%m%d%H%M%S")
 242             stop=datetime.datetime(*time.strptime(etime, "%Y-%m-%d %H:%M:%S")[:-3]).strftime("%Y%m%d%H%M%S")
 243             btimet=" AND start >= "+start+" "
 244             etimet=" AND stop <= "+stop+" "
 245         if len(chtxt)>0:
 246             chtxtt=" AND channel = "+chtxt+" "
 247         db = self.connect_db()
 248         recdata = []
 249         #print "SELECT bctype,channel,start,stop,title,exp,longexp,category FROM epg_timeline \
 250         #WHERE bctype LIKE '%' "+keyt+btimet+etimet+chtxtt+""
 251         dl = db[1].execute("SELECT bctype,channel,start,stop,title,exp,longexp,category FROM epg_timeline \
 252         WHERE bctype LIKE '%' "+keyt+btimet+etimet+chtxtt+"")
 253         dls = db[1].fetchall()
 254         for line in dls:
 255             recdata.append(self.epg_timeline2dic(line))
 256         self.close_db(db)
 257         return recdata
 258 #####ここまでDBの読み込み/書き込み系
 259 global db
 260 db=getdb()
 261 jsonHeader="Content-Type: application/json;charset=utf-8\n"
 262 htmlHeader="Content-Type: text/html; charset=utf-8\n"
 263 global f
 264 f = cgi.FieldStorage()
 265 #mode="timeline"
 266 mode=""
 267
 268
 269
 270 if f.getfirst('mode',""):
 271         mode=f.getfirst('mode', "")
 272 if mode=="chtypelist":
 273     #print "Content-Type: application/json;charset=utf-8\n"
 274     print jsonHeader
 275     typest={"identifier":"id","items":[
 276                 {
 277                         "id":"search_everyday",
 278                         "label":u"隔日検索"
 279                 },{
 280                         "id":"search_today",
 281                         "label":u"当日検索"
 282                 },{
 283                         "id":"reserve_flexible",
 284                         "label":u"浮動予約"
 285                 },{
 286                         "id":"reserve_fixed",
 287                         "label":u"確定予約"
 288                 },{
 289                         "id":"convert_b25_ts",
 290                         "label":u"解読予約"
 291                 },{
 292                         "id":"convert_ts_mp4",
 293                         "label":u"縁故予約"
 294                 }
 295     ]}
 296     print json.dumps(typest)
 297 if mode=="chlist":
 298     chls=db.select_all_epg_ch()
 299     chll={"identifier":"id","items":[]}
 300     for chl in chls:
 301         chll["items"].append({"id":chl["chtxt"],"type":chl["type"],"label":chl["chname"]})
 302     print jsonHeader
 303     print json.dumps(chll)
 304 if mode=="timeline":####録画一覧用
 305     tlll={"identifier":"id","items":[]}
 306     dbl=None
 307     if f.getfirst("chtxt"):
 308         dbl=db.select_by_btime_etime_chtxt_timeline(btime,etime,chtxt)
 309     else:
 310         dbl=db.select_all_timeline()
 311     if dbl:
 312         for dbi in dbl:
 313             tlll["items"].append({"id":dbi["id"],"btime":dbi["btime"],"etime":dbi["etime"],
 314                                   "chtxt":dbi["chtxt"],"title":dbi["title"],"type":dbi["type"],
 315                                   "deltatime":dbi["deltatime"],"deltaday":dbi["deltaday"],
 316                                   "opt":dbi["opt"]})
 317     #print jsonHeader
 318     print htmlHeader
 319     print json.dumps(tlll,encoding="utf-8")
 320 if mode=="epg-timeline":
 321     tlll={"items":[]}
 322     btime=f.getfirst("btime")
 323     etime=f.getfirst("etime")
 324     if not btime:
 325         btime=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
 326     if not etime:
 327         et=datetime.datetime.now()+datetime.timedelta(days=8)
 328         etime=et.strftime("%Y-%m-%d %H:%M:%S")
 329     chtxt=f.getfirst("chtxt")
 330     if not chtxt:
 331         chtxt="18_3096"
 332     if chtxt:
 333         dbl=db.select_by_btime_etime_chtxt_epg_timeline(btime,etime,chtxt)
 334         for dbi in dbl:
 335             tlll["items"].append({"id":dbi["btime"],"btime":dbi["btime"],
 336                                   "chtxt":dbi["chtxt"],"title":dbi["title"],
 337                                   "exp":dbi["exp"],"longexp":dbi["longexp"],
 338                                   "category":dbi["category"]})
 339         print jsonHeader
 340         #print htmlHeader
 341         print json.dumps({"data":tlll},encoding="utf-8")
 342 if mode=="epg-search":
 343     tlll={"items":[]}
 344     btime=f.getfirst("btime")
 345     etime=f.getfirst("etime")
 346     if not btime:
 347         bt=datetime.datetime.now()
 348         btime=bt.strftime("%Y-%m-%d %H:%M:%S")
 349     if not etime:
 350         et=datetime.datetime.now()+datetime.timedelta(days=8)
 351         etime=et.strftime("%Y-%m-%d %H:%M:%S")
 352     chtxt=f.getfirst("chtxt")
 353     if not chtxt:
 354         chtxt=""
 355     keyword=f.getfirst("keyword")
 356     if not keyword:
 357         keyword=""
 358     dbl=db.select_by_keyword_title_epg_timeline(keyword=keyword,chtxt=chtxt,btime=btime,etime=etime)
 359     for dbi in dbl:
 360         tlll["items"].append({"id":dbi["btime"],"btime":dbi["btime"],
 361         "chtxt":dbi["chtxt"],"title":dbi["title"],
 362         "exp":dbi["exp"],"longexp":dbi["longexp"],
 363         "category":dbi["category"]})
 364     print jsonHeader
 365     #print htmlHeader
 366     print json.dumps({"data":tlll},encoding="utf-8")