#!/usr/bin/python
# -*- coding: utf8 -*-

#Library nya
import sys
import time
import datetime
import telepot
import uniout
import MySQLdb
from ConfigParser import SafeConfigParser
from twx.botapi import TelegramBot, ReplyKeyboardMarkup

db_host = 'localhost'
db_uid = 'root'
db_pass = 'passoword'
db_name = 'belajar'

# Config database nya
db = MySQLdb.connect(db_host, db_uid, db_pass, db_name, charset='utf8', init_command='SET NAMES UTF8')

def handle(msg):
    #Deklarasi untuk profile Telegram
    chat_id = msg['chat']['id']
    command = msg['text']
    user_id = msg['from']['id']
    username = msg['from']['first_name']
    fullname = msg['from']['last_name']+msg['from']['first_name']
    timestr = time.strftime("%Y-%m-%d_%H")
    content_type, chat_type, chat_id = telepot.glance(msg)
    db.commit()

    if (user_id == cek_user(msg)):
        command = msg['text']
        print ('Got command: %s' % command)
        #Dekklarasi command apa saja yang mau dimasukan
        if command == 'halo':
            keyboard = [
                ['Halo', 'erlang'],
                ['Cek id', 'ddd'],
                ['ccc'],['Tutup']
            ]
            reply_markup = ReplyKeyboardMarkup.create(keyboard)
            bot.sendMessage(chat_id, "Assalamu'alaikum "+username+", ..?",reply_markup=reply_markup)
        elif command == 'erlang':
            bot.sendPhoto(chat_id=chat_id, photo=open('E:/@@@/@smartfren/capture trial/Compare_KPI_1' + '.jpg','rb'))
            bot.sendPhoto(chat_id=chat_id, photo=open('E:/@@@/@smartfren/capture trial/Compare_KPI_2' + '.jpg','rb'))
        elif command == 'Cek id':
            print ('Got command: %s' % timestr + command)
            bot.sendMessage(chat_id, 'Kamu adalah {} dan user kamu adalah {}'.format(username, user_id))
        elif 'Tutup' in command:
            hide_keyboard = {'hide_keyboard': True}
            bot.sendMessage(chat_id, 'Fungsi KeyBoard ditutup!', reply_markup=hide_keyboard)
        elif 'ccc' in command:
            reps_content = query(command)
            bot.sendMessage(chat_id, reps_content)
        elif 'ddd' in command:
            reps_content = query(command)
            bot.sendMessage(chat_id, reps_content)
    else:
        print ('Got command: '+'Any person try message from ({}) and user id ({})'.format(username, user_id))
        bot.sendMessage(chat_id, 'Hello {}! Sorry, your user_id {} not registered'.format(username, user_id))

def query(requ):

    cursor = db.cursor()
    sql = "SELECT * FROM monitor WHERE tanggal LIKE '%" + str(requ.encode('utf-8')) + "%'"

    try:
        cursor.execute(sql)
        # memecahkan hasil dari table
        results = cursor.fetchall()
        output_beneran = []
        # memberikan hasil jika hasil sesuai sama tabel maka keluar sesuai hasil
        if len(results)>0:
            output =  results
            for row in results:
                Tanggal = row[0]
                DOWNTIME_PLN = row[1]
                RUN_GENSET = row[2]
                SHREDDER_MILL_DOWNTIME = row[3]
                SHREDDER_CRUMB_DOWNTIME = row[4]
                SHREDDER_CRUMB_AMPERE = row[5]
                has_trans = row[6]
                # Now print fetched result
                #output = (SHREDDER_CRUMB_AMPERE)
                output = ('Tanggal {tanggal},Downtime {DOWNTIME_PLN},run genset {genset},shredder mill downtime {shd},shredder crumb downtime {scd},shredder crumb ampere {sca},has trans {trans} '.format(tanggal=Tanggal,DOWNTIME_PLN = DOWNTIME_PLN,genset=RUN_GENSET,shd=SHREDDER_MILL_DOWNTIME,scd=SHREDDER_CRUMB_DOWNTIME,sca=SHREDDER_CRUMB_AMPERE,trans=has_trans))
                #print (SHREDDER_CRUMB_AMPERE)
                output_beneran.append(output)
            return output_beneran
        else:
            output = 'Data not found'
    except:
        output = 'error'
    return output

def cek_user(msg):
    user_id = msg['from']['id']
    cursor = db.cursor()
    sql = "SELECT * FROM user WHERE `id` = '%d'" % user_id
    try:
        cursor.execute(sql)
        # memecahkan hasil dari table
        results = cursor.fetchall()
        if len(results)>0:
            output = ''
            for row in results:
                id = row[0]
                keterangan = row[1]
                # Now print fetched result
                output = id
        else:
            print ('Got command: '+'Any person try send message from ({}) and user id ({})'.format(username, user_id))
            bot.sendMessage(chat_id, 'Hello {}! Sorry, your user_id {} not registered'.format(username, user_id))

    except:
        output = 'error'
    return output


# deklarasi database


bot = telepot.Bot('321559260:AAEtxA_pRZifaXw5BP66diXv4PcKUm6evUc')
bot.message_loop(handle)
print 'Membaca ...'

while 1:
    time.sleep(1)