Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/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"
- def get_sum_for_terminals(acceptor_id, cursor):
- banknotes = ["5000", "2000", "1000", "500", "200", "100", "50"]
- 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):
- 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):
- 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):
- 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):
- 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):
- 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():
- conn = pymssql.connect(server, user, password, database)
- cursor = conn.cursor()
- 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, cursor)]
- 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], cursor)]
- term_id_cards_in = {}
- for terminal_id in terminals:
- term_id_cards_in[terminal_id] = [get_cards_in(terminal_id[0], cursor)]
- term_id_cards_out = {}
- for terminal_id in terminals:
- term_id_cards_out[terminal_id] = [get_cards_out(terminal_id, cursor)]
- term_id_cards_bad = {}
- for terminal_id in terminals:
- term_id_cards_bad[terminal_id] = [get_cards_bad(terminal_id, cursor)]
- 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, cursor)]
- 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]])
- cursor.close()
- return total_shit, total_sum, online_status
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement