#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pymssql
import sys
import locale
reload(sys)
sys.setdefaultencoding('utf-8')
server = "ip"
user = "sa"
password = "password"
database = "base"

conn = pymssql.connect(server, user, password, database)
cursor = conn.cursor()

banknotes = ["5000", "2000", "1000", "500", "200", "100", "50"]

def get_sum_for_terminals(acceptor_id):
        total_sum = 0
        for banknote_value in banknotes:
                cursor.execute("SELECT (denomination * count) FROM acceptorsupplies WHERE id = (SELECT  MAX(id) FROM acceptorsupplies WHERE denomination = '%s.000000' AND Acceptorid='%s' )" % (banknote_value, acceptor_id))
                banknote_sum = cursor.fetchall()
                if banknote_sum:
                        total_sum += int(banknote_sum[0][0])
        return total_sum

def get_acceptor_id(terminal_id):
        cursor.execute("SELECT id FROM acceptor WHERE  terminalid = '%s'" % terminal_id)
        acceptor_id = cursor.fetchall()[0][0]
        return acceptor_id

def get_cards_in(terminal_id):
        cursor.execute('''SELECT cardin.count - cardout.dispensecount - cardout.rejectcount
                       FROM carddispensersuppliesin as cardin
                       LEFT JOIN CardDispenserSuppliesOut as cardout on cardin.TerminalId = cardout.TerminalId
                       WHERE cardin.id = (SELECT max(id) FROM carddispensersuppliesin WHERE terminalid = '%s')
                       AND cardout.id = (SELECT max(id) FROM CardDispenserSuppliesOut WHERE terminalid = '%s')''' % (terminal_id, terminal_id))
        cards_in = cursor.fetchall()[0][0]
        return cards_in

def get_cards_out(terminal_id):
        cursor.execute("SELECT dispensecount FROM carddispensersuppliesout WHERE id = (SELECT max(id) FROM carddispensersuppliesout WHERE terminalid = '%s')" % terminal_id)
        cards_out = cursor.fetchall()[0][0]
        return cards_out

def get_cards_bad(terminal_id):
        cursor.execute("SELECT rejectcount FROM carddispensersuppliesout WHERE id = (SELECT max(id) FROM carddispensersuppliesout WHERE terminalid = '%s')" % terminal_id)
        cards_bad = cursor.fetchall()[0][0]
        return cards_bad

def get_online_status(terminal_id):
        cursor.execute("SELECT count(*) FROM TerminalOnline WHERE terminalid = '%s' and EndTime > dateadd(minute,-181, getdate())" % terminal_id)
        online_status = cursor.fetchall()[0][0]
        return online_status
def stat():
        cursor.execute("SELECT id FROM terminal WHERE isenabled = 'True' AND isremoved = 'False'")
        terminals = cursor.fetchall()

        terminal_names = {1: 'Term1', 2: 'Term2', 6: 'Term3', 7: 'Term4', 9: 'Term5', 10: 'Term6', 11: 'Term7', 12: 'Term8', 13: 'Term9', 14: 'Term10', 15: 'Term11', 16: 'Term12', 17: 'Term13'}
        term_id_acc_id = {}
        for terminal_id in terminals:
                term_id_acc_id[terminal_id] = [get_acceptor_id(terminal_id)]

        acc_id_sum = {}
        for acceptor_id in term_id_acc_id.values():
                acc_id_sum[acceptor_id[0]] = [get_sum_for_terminals(acceptor_id[0])]

        term_id_cards_in = {}
        for terminal_id in terminals:
                term_id_cards_in[terminal_id] = [get_cards_in(terminal_id[0])]

        term_id_cards_out = {}
        for terminal_id in terminals:
                term_id_cards_out[terminal_id] = [get_cards_out(terminal_id)]

        term_id_cards_bad = {}
        for terminal_id in terminals:
                term_id_cards_bad[terminal_id] = [get_cards_bad(terminal_id)]

        total_sum = 0
        for acc_id, sum in acc_id_sum.items():
                total_sum += sum[0]

        online_status = {}
        for terminal_id in terminals:
                if int(get_online_status(terminal_id)) == 0:
                        if terminal_names.has_key(terminal_id[0]):
                                term_name = terminal_names[terminal_id[0]]
                                online_status[term_name] = [get_online_status(terminal_id)]

        total_shit = []
        term_id_sum = {}
        for term_id, acc_id in term_id_acc_id.items():
                for acc_id_2, sum in acc_id_sum.items():
                        if acc_id[0] == acc_id_2:
                                for term_id_2, cards_in in term_id_cards_in.items():
                                        if term_id_2 == term_id:
                                                for term_id_3, cards_out in term_id_cards_out.items():
                                                        if term_id_3 == term_id:
                                                                for term_id_4,cards_bad in term_id_cards_bad.items():
                                                                        if term_id_4 == term_id:
                                                                                if terminal_names.has_key(term_id[0]):
                                                                                        term_name = terminal_names[term_id[0]]
                                                                                        #total_shit.append([term_name, sum[0], '11', cards_out[0],cards_bad[0]])
                                                                                        total_shit.append([term_name, sum[0], cards_in[0], cards_out[0],cards_bad[0]])
        return total_shit, total_sum, online_status