#!/usr/bin/python
# coding: UTF-8
# Rec10 TS Recording Tools
# Copyright (C) 2009 Yukikaze
import os
import MySQLdb
path = str(os.path.dirname(os.path.abspath(__file__))) + "/"
dbpath = path + "ch.db"
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()
    def select_all_timeline(self):
        db = self.connect_db()
        recdata = []
        dl = db[1].execute("SELECT id,type,chtxt,title,btime,etime,deltatime,deltaday,opt FROM timeline")
        dls = db[1].fetchall()
        for id, typet, chtxt, title, btime, etime, deltatime, deltaday, opt in dls:
            ret = {}
            ret['id'] = id
            ret['type'] = typet
            ret['chtxt'] = chtxt
            ret['title'] = title
            btime = btime.strftime("%Y-%m-%d %H:%M:%S")
            etime = etime.strftime("%Y-%m-%d %H:%M:%S")
            ret['btime'] = btime
            ret['etime'] = etime
            ret['opt'] = opt
            ret['deltatime'] = ""
            ret['deltaday'] = ""
            if deltatime == None:
                deltatime = "3"
            if deltaday == None:
                deltaday = "7"
            if typet == 'key':
                ret['deltatime'] = deltatime
            elif typet == 'keyevery':
                ret['deltatime'] = deltatime
                ret['deltaday'] = deltaday
            recdata.append(ret)
        self.close_db(db)
        return recdata
    def select_by_chtxt_timeline(self, chtxt):
        db = self.connect_db()
        recdata = []
        dl = db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM timeline WHERE chtxt = %s ", (chtxt, ))
        d = db[1].fetchall()
        for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in d:
            ret = {}
            ret['type'] = typet
            ret['chtxt'] = chtxt
            ret['title'] = title
            btime = btime.strftime("%Y-%m-%d %H:%M:%S")
            etime = etime.strftime("%Y-%m-%d %H:%M:%S")
            ret['btime'] = btime
            ret['etime'] = etime
            ret['opt'] = opt
            if deltatime == None:
                deltatime = "3"
            if deltaday == None:
                deltaday = "7"
            if typet == 'key':
                ret['deltatime'] = deltatime
            elif typet == 'keyevery':
                ret['deltatime'] = deltatime
                ret['deltaday'] = deltaday
            recdata.append(ret)
        self.close_db(db)
        return recdata
    def select_by_id_timeline(self, id):
        db = self.connect_db()
        recdata = []
        dl = db[1].execute("SELECT type, chtxt, title, btime, etime, deltatime ,deltaday ,opt FROM timeline WHERE id = %s ", (id, ))
        d = db[1].fetchall()
        for typet, chtxt, title, btime, etime, deltatime, deltaday, opt in d:
            ret = {}
            ret['type'] = typet
            ret['chtxt'] = chtxt
            ret['title'] = title
            btime = btime.strftime("%Y-%m-%d %H:%M:%S")
            etime = etime.strftime("%Y-%m-%d %H:%M:%S")
            ret['btime'] = btime
            ret['etime'] = etime
            ret['opt'] = opt
            if deltatime == None:
                deltatime = "3"
            if deltaday == None:
                deltaday = "7"
            if typet == 'key':
                ret['deltatime'] = deltatime
            elif typet == 'keyevery':
                ret['deltatime'] = deltatime
                ret['deltaday'] = deltaday
            recdata.append(ret)
        self.close_db(db)
        return recdata
    def select_all_epg_ch(self):
        db = self.connect_db()
        ret = db[1].execute("SELECT bctype,ontv,chtxt,chname,ch,csch,updatetime FROM epg_ch")
        ret = db[1].fetchall()
        self.close_db(db)
        return ret
    def select_by_chtxt_epg_ch(self, chtxt):
        db = self.connect_db()
        ret = db[1].execute("SELECT bctype,ontv,chtxt,ch,csch,updatetime FROM epg_ch WHERE chtxt=%s", (chtxt, ))
        ret = db[1].fetchall()
        self.close_db(db)
        return ret
    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))
        #db.commit()
        self.close_db(db)
    def schedule_timeline(self, btime, etime):
        db = self.connect_db()
        dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,timeline.title,timeline.btime,timeline.etime FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
        dbexe = dbexe + " WHERE NOT( ( timeline.etime <= %s ) OR ( timeline.btime >= %s ) )"
        db[1].execute(dbexe, (btime, etime))
        rets = []
        for typet, bctypet, chtxtt, titlet, btimet, etimet in db[1].fetchall():
            ret = [typet, bctypet, chtxtt, titlet, btimet.strftime("%Y-%m-%d %H:%M:%S"), etimet.strftime("%Y-%m-%d %H:%M:%S")]
            rets.append(ret)
        self.close_db(db)
        return rets
    def count_schedule_timeline(self, btime, etime):
        """
        count rectasknum
        return [te num,bs/cs num]
        """
        db = self.connect_db()
        dbexe = "SELECT type,epg_ch.bctype,timeline.chtxt,title FROM timeline INNER JOIN epg_ch ON timeline.chtxt=epg_ch.chtxt"
        dbexe = dbexe + " WHERE NOT( ( timeline.etime <= %s ) OR ( timeline.btime >= %s ) )"
        Srec = 0
        Trec = 0
        db[1].execute(dbexe, (btime, etime))
        for typet, bctypet, chtxtt, titlet in db[1].fetchall():
            if (typet == "reserve_flexible") or (typet == "reserve_fixed") or (typet == "search_today") or (typet == "search_everyday"):
                if bctypet.find("cs") > -1:
                    Srec = Srec + 1
                elif bctypet.find("bs") > -1:
                    Srec = Srec + 1
                elif bctypet.find("te") > -1:
                    Trec = Trec + 1
        self.close_db(db)
        return [Trec, Srec]
    def del_by_id_timeline(self, id):
        db = self.connect_db()
        db[1].execute('DELETE FROM timeline WHERE id=' + id)
        self.close_db(db)
    def new_iepg(self):
        db = self.connect_db()
        try:
            db[1].execute("CREATE TABLE in_iepg \
            (\
            id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,\
            station VARCHAR(200) NOT NULL,\
            chtxt VARCHAR(30) NOT NULL\
            )")
        except:
            ""
        self.close_db(db)
    def add_iepg(self, station, chtxt):
        db = self.connect_db()
        db[1].execute('INSERT IGNORE INTO in_iepg (station,chtxt) VALUES (%s,%s)', (station, chtxt))
        self.close_db(db)
    def del_by_id_iepg(self, num):
        db = self.connect_db()
        db[1].execute("DELETE FROM in_iepg \
        WHERE id=%s", \
                      (num, ))
        self.close_db(db)
    def select_by_chtxt_iepg(self, chtxt):
        db = self.connect_db()
        db[1].execute("\
        SELECT \
        id,station,chtxt \
        FROM in_iepg \
        WHERE chtxt=%s "\
                      , (chtxt, ))
        retall = db[1].fetchall()
        self.close_db(db)
        rets = []
        if len(retall) > 0:
            for ret in retall:
                a = list(ret)
                a[0] = str(ret[0])
                rets.append(a)
        return rets
    def select_by_station_iepg(self, station):
        db = self.connect_db()
        db[1].execute("\
        SELECT \
        id,station,chtxt \
        FROM in_iepg \
        WHERE station=%s "\
                      , (station, ))
        retall = db[1].fetchall()
        self.close_db(db)
        rets = []
        if len(retall) > 0:
            for ret in retall:
                a = list(ret)
                a[0] = str(ret[0])
                rets.append(a)
        return rets
    def select_all_iepg(self):
        db = self.connect_db()
        db[1].execute("\
        SELECT \
        id,station,chtxt \
        FROM in_iepg "\
        )
        retall = db[1].fetchall()
        self.close_db(db)
        rets = []
        if len(retall) > 0:
            for ret in retall:
                a = list(ret)
                a[0] = str(ret[0])
                rets.append(a)
        return rets