#!/usr/bin/python
# coding: UTF-8
# Rec10 TS Recording Tools
# Copyright (C) 2009-2011 Yukikaze
import cgi
import os
import os.path
import re
import datetime
import ConfigParser
import MySQLdb
import time
try:
    import simplejson as json
except ImportError:
    import json
#import cgitb
#cgitb.enable()

path = str(os.path.dirname(os.path.abspath(__file__))) + "/"
confp = ConfigParser.SafeConfigParser()
Conf = 'rec10.conf'
cpath=""
if (os.path.exists(os.path.join(path,Conf))):
    cpath=os.path.join(path,Conf)
elif (os.path.exists(os.path.join("/etc/rec10",Conf))):
    cpath=os.path.join("/etc/rec10",Conf)
elif (os.path.exists(os.path.join("/etc",Conf))):
    cpath=os.path.join("/etc",Conf)
confp.read(cpath)
def getpath(string):
    global confp
    return confp.get('path', string)
def getdbpath(string):
    global confp
    return confp.get('db', string)
def getdb():
    retdb = ""
    if getdbpath('db') == 'MySQL':
        dbn = getdbpath("mysql_dbname")
        dbh = getdbpath("mysql_host")
        dbu = getdbpath("mysql_user")
        dbpwd = getdbpath("mysql_passwd")
        dbport = int(getdbpath("mysql_port"))
        #print [dbn,dbh,dbu,dbpwd,dbport]
        retdb = DB_MySQL(dbname=dbn, host=dbh, user=dbu, passwd=dbpwd, port=dbport)
    return retdb
class DB_MySQL:
    dbname = ""
    dbhost = ""
    dbusr = ""
    dbpasswd = ""
    dbport = 0
    def __init__(self, dbname, user, passwd, host="localhost", port=3306):
        self.dbname = dbname
        self.dbhost = host
        self.dbusr = user
        self.dbpasswd = passwd
        self.dbport = port
    def connect_db(self):
        """
        dbへの接続
        """
        con = MySQLdb.connect(db=self.dbname, host=self.dbhost, port=self.dbport,\
			user=self.dbusr, passwd=self.dbpasswd, charset="utf8")
        cur = con.cursor()
        cur.execute('set names utf8;')
        return [con, cur]
    def close_db(self, db):
        db[1].close()
        db[0].close()
    
    ###timeline系
    def timeline2dic(self,timeline):
        ret={}
        ret['id']=timeline[0]
        ret['type']=timeline[1]
        ret['chtxt']=timeline[2]
        ret['title']=timeline[3]
        ret['btime']=timeline[4].strftime("%m/%d %H:%M")
        ret['etime']=timeline[5].strftime("%H:%M")
        ret['bt']=timeline[4]
        ret['et']=timeline[5]
        if not timeline[6]:
			ret['deltatime']=timeline[6]
        else:
			ret['deltatime']="3"
        if not timeline[7]:
			ret['deltaday']=timeline[7]
        else:
			ret['deltaday']="7"
        ret['opt']=timeline[8]
        ret['epgtitle']=timeline[9]
        ret['epgbtime']=timeline[10]
        ret['epgetime']=timeline[11]
        ret['epgduplicate']=timeline[12]
        ret['epgchange']=timeline[13]
        ret['epgexp']=timeline[14]
        ret['counter']=timeline[15]
        return ret
    def select_all_timeline(self):
        db = self.connect_db()
        recdata = []
        dl = db[1].execute("SELECT id,type,chtxt,title,btime,etime,deltatime,deltaday,opt,\
				epgtitle,epgbtime,epgetime,epgduplicate,epgchange,epgexp,counter FROM timeline ORDER BY btime")
        dls = db[1].fetchall()
        for line in dls:
            recdata.append(self.timeline2dic(line))
        self.close_db(db)
        return recdata
    def select_by_chtxt_timeline(self, chtxt):
        db = self.connect_db()
        recdata = []
        dl = db[1].execute("SELECT id,type,chtxt,title,btime,etime,deltatime,deltaday,opt,\
                epgtitle,epgbtime,epgetime,epgduplicate,epgchange,epgexp,counter FROM timeline WHERE chtxt = %s ", (chtxt, ))
        d = db[1].fetchall()
        for line in dls:
            recdata.append(self.timeline2dic(line))
        self.close_db(db)
        return recdata
    def select_by_id_timeline(self, idt):
        db = self.connect_db()
        recdata = []
        dl = db[1].execute("SELECT id,type,chtxt,title,btime,etime,deltatime,deltaday,opt,\
		epgtitle,epgbtime,epgetime,epgduplicate,epgchange,epgexp,counter FROM timeline WHERE id = %s ", (idt, ))
        d = db[1].fetchall()
        for line in dls:
            recdata.append(self.timeline2dic(line))
        self.close_db(db)
        return recdata
	def select_by_btime_etime_chtxt_timeline(self,btime,etime,chtxt):
		db = self.connect_db()
        recdata = []
        dl = db[1].execute("SELECT id,type,chtxt,title,btime,etime,deltatime,deltaday,opt,\
        epgtitle,epgbtime,epgetime,epgduplicate,epgchange,epgexp,counter FROM timeline \
        WHERE btime >= %s AND etime <= %s AND chtxt = %s", (btime,etime,chtxt ))
        dls = db[1].fetchall()
        for line in dls:
            recdata.append(self.timeline2dic(line))
        self.close_db(db)
        return recdata
    def add_timeline(self, type="", chtxt="", title="", btime="", etime="", deltatime="", deltaday="", opt=""):
        db = self.connect_db()
        db[1].execute('INSERT IGNORE INTO timeline (type,chtxt,title,btime,etime,deltatime,deltaday,opt) \
            values (%s,%s,%s,%s,%s,%s,%s,%s)', (type, chtxt, title, btime, etime, deltatime, deltaday, opt))
        self.close_db(db)
        
        
    ########epg_ch系
    def epg_ch2dic(self,epg_ch):
        ret={}
        ret['bctype']=epg_ch[0]
        ret['chtxt']=epg_ch[1]
        ret['ch']=epg_ch[2]
        ret['csch']=epg_ch[3]
        ret['chname']=epg_ch[4]
        ret['updatetime']=epg_ch[5]
        ret['status']=epg_ch[6]
        ret['visible']=epg_ch[7]
        if ret["chtxt"].split("_")[0]=="BS":
            ret["type"]="BS"
        elif ret["chtxt"].split("_")[0]=="CS":
            ret["type"]="CS"
        else:
            ret["type"]="TE"
        return ret
    def select_all_epg_ch(self):
        db = self.connect_db()
        r1 = db[1].execute("SELECT bctype,chtxt,ch,csch,chname,updatetime,status,visible FROM epg_ch")
        lines = db[1].fetchall()
        ret=[]
        for ls in lines:
            ret.append(self.epg_ch2dic(ls))
        self.close_db(db)
        return ret
    def select_by_chtxt_epg_ch(self, chtxt):
        db = self.connect_db()
        r1 = db[1].execute("SELECT bctype,chtxt,ch,csch,chname,updatetime,status,visible FROM epg_ch WHERE chtxt=%s", (chtxt, ))
        lines = db[1].fetchall()
        ret=[]
        for ls in lines:
            ret.append(epg_ch2dic(ls))
        self.close_db(db)
        return ret

    ########epg_timeline系
    def epg_timeline2dic(self,epg_timeline):
        ret={}
        ret['bctype']=epg_timeline[0]
        ret['channel']=epg_timeline[1]
        ret['chtxt']=epg_timeline[1]
        ret['start']=datetime.datetime(*time.strptime(epg_timeline[2], "%Y%m%d%H%M%S")[:-3])
        ret['stop']=datetime.datetime(*time.strptime(epg_timeline[3], "%Y%m%d%H%M%S")[:-3])
        ret['bt']=ret['start']
        ret['et']=ret['stop']
        ret['btime']=ret['bt'].strftime("%Y-%m-%d %H:%M:%S")
        ret['etime']=ret['et'].strftime("%Y-%m-%d %H:%M:%S")
        ret['title']=epg_timeline[4]
        ret['exp']=epg_timeline[5]
        ret['longexp']=epg_timeline[6]
        ret['category']=epg_timeline[7]
        return ret
    def select_all_epg_timeline(self):
        db = self.connect_db()
        r1 = db[1].execute("SELECT bctype,channel,start,stop,title,exp,longexp,category FROM epg_timeline")
        lines = db[1].fetchall()
        ret=[]
        for ls in lines:
            ret.append(self.epg_timeline2dic(ls))
        self.close_db(db)
        return ret
    def select_by_chtxt_epg_timeline(self, chtxt):
        db = self.connect_db()
        r1 = db[1].execute("SELECT bctype,channel,start,stop,title,exp,longexp,category FROM epg_timeline WHERE channel=%s", (chtxt, ))
        lines = db[1].fetchall()
        ret=[]
        for ls in lines:
            ret.append(self.epg_timeline2dic(ls))
        self.close_db(db)
        return ret
    def select_by_btime_etime_chtxt_epg_timeline(self,btime,etime,chtxt):
        db = self.connect_db()
        start=datetime.datetime(*time.strptime(btime, "%Y-%m-%d %H:%M:%S")[:-3]).strftime("%Y%m%d%H%M%S")
        stop=datetime.datetime(*time.strptime(etime, "%Y-%m-%d %H:%M:%S")[:-3]).strftime("%Y%m%d%H%M%S")
        recdata = []
        dl = db[1].execute("SELECT bctype,channel,start,stop,title,exp,longexp,category FROM epg_timeline \
        WHERE start >= %s AND stop <= %s AND channel = %s", (start,stop,chtxt ))
        dls = db[1].fetchall()
        for line in dls:
            recdata.append(self.epg_timeline2dic(line))
        self.close_db(db)
        return recdata
    def select_by_keyword_title_epg_timeline(self,keyword="",chtxt="",btime="",etime=""):
        keyt=""
        chtxtt=""
        btimet=""
        etimet=""
        if len(keyword)>0:
            keyt=" AND title LIKE '%"+keyt+"%' "
        if len(btime)>0:
            start=datetime.datetime(*time.strptime(btime, "%Y-%m-%d %H:%M:%S")[:-3]).strftime("%Y%m%d%H%M%S")
            stop=datetime.datetime(*time.strptime(etime, "%Y-%m-%d %H:%M:%S")[:-3]).strftime("%Y%m%d%H%M%S")
            btimet=" AND start >= "+start+" "
            etimet=" AND stop <= "+stop+" "
        if len(chtxt)>0:
            chtxtt=" AND channel = "+chtxt+" "
        db = self.connect_db()
        recdata = []
        #print "SELECT bctype,channel,start,stop,title,exp,longexp,category FROM epg_timeline \
        #WHERE bctype LIKE '%' "+keyt+btimet+etimet+chtxtt+""
        dl = db[1].execute("SELECT bctype,channel,start,stop,title,exp,longexp,category FROM epg_timeline \
        WHERE bctype LIKE '%' "+keyt+btimet+etimet+chtxtt+"")
        dls = db[1].fetchall()
        for line in dls:
            recdata.append(self.epg_timeline2dic(line))
        self.close_db(db)
        return recdata
#####ここまでDBの読み込み/書き込み系
global db
db=getdb()
jsonHeader="Content-Type: application/json;charset=utf-8\n"
htmlHeader="Content-Type: text/html; charset=utf-8\n"
global f
f = cgi.FieldStorage()
#mode="timeline"
mode=""



if f.getfirst('mode',""):
	mode=f.getfirst('mode', "")
if mode=="chtypelist":
    #print "Content-Type: application/json;charset=utf-8\n"
    print jsonHeader
    typest={"identifier":"id","items":[
		{
			"id":"search_everyday",
			"label":u"隔日検索"
		},{
			"id":"search_today",
			"label":u"当日検索"
		},{
			"id":"reserve_flexible",
			"label":u"浮動予約"
		},{
			"id":"reserve_fixed",
			"label":u"確定予約"
		},{
			"id":"convert_b25_ts",
			"label":u"解読予約"
		},{
			"id":"convert_ts_mp4",
			"label":u"縁故予約"
		}
    ]}
    print json.dumps(typest)
if mode=="chlist":
    chls=db.select_all_epg_ch()
    chll={"identifier":"id","items":[]}
    for chl in chls:
        chll["items"].append({"id":chl["chtxt"],"type":chl["type"],"label":chl["chname"]})
    print jsonHeader
    print json.dumps(chll)
if mode=="timeline":####録画一覧用
    tlll={"identifier":"id","items":[]}
    dbl=None
    if f.getfirst("chtxt"):
        dbl=db.select_by_btime_etime_chtxt_timeline(btime,etime,chtxt)
    else:
        dbl=db.select_all_timeline()
    if dbl:
        for dbi in dbl:
            tlll["items"].append({"id":dbi["id"],"btime":dbi["btime"],"etime":dbi["etime"],
                                  "chtxt":dbi["chtxt"],"title":dbi["title"],"type":dbi["type"],
                                  "deltatime":dbi["deltatime"],"deltaday":dbi["deltaday"],
                                  "opt":dbi["opt"]})
    #print jsonHeader
    print htmlHeader
    print json.dumps(tlll,encoding="utf-8")
if mode=="epg-timeline":
    tlll={"items":[]}
    btime=f.getfirst("btime")
    etime=f.getfirst("etime")
    if not btime:
        btime=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    if not etime:
        et=datetime.datetime.now()+datetime.timedelta(days=8)
        etime=et.strftime("%Y-%m-%d %H:%M:%S")
    chtxt=f.getfirst("chtxt")
    if not chtxt:
        chtxt="18_3096"
    if chtxt:
        dbl=db.select_by_btime_etime_chtxt_epg_timeline(btime,etime,chtxt)
        for dbi in dbl:
            tlll["items"].append({"id":dbi["btime"],"btime":dbi["btime"],
                                  "chtxt":dbi["chtxt"],"title":dbi["title"],
                                  "exp":dbi["exp"],"longexp":dbi["longexp"],
                                  "category":dbi["category"]})
        print jsonHeader
        #print htmlHeader
        print json.dumps({"data":tlll},encoding="utf-8")
if mode=="epg-search":
    tlll={"items":[]}
    btime=f.getfirst("btime")
    etime=f.getfirst("etime")
    if not btime:
        bt=datetime.datetime.now()
        btime=bt.strftime("%Y-%m-%d %H:%M:%S")
    if not etime:
        et=datetime.datetime.now()+datetime.timedelta(days=8)
        etime=et.strftime("%Y-%m-%d %H:%M:%S")
    chtxt=f.getfirst("chtxt")
    if not chtxt:
        chtxt=""
    keyword=f.getfirst("keyword")
    if not keyword:
        keyword=""
    dbl=db.select_by_keyword_title_epg_timeline(keyword=keyword,chtxt=chtxt,btime=btime,etime=etime)
    for dbi in dbl:
        tlll["items"].append({"id":dbi["btime"],"btime":dbi["btime"],
	"chtxt":dbi["chtxt"],"title":dbi["title"],
	"exp":dbi["exp"],"longexp":dbi["longexp"],
	"category":dbi["category"]})
    print jsonHeader
    #print htmlHeader
    print json.dumps({"data":tlll},encoding="utf-8")
