# receipt_kpi Updates

import sys
import os
import mysql.connector
import datetime
import time

import default_setting

def recHeader (startDate,cnx):
# connection
	cursor = cnx.cursor(dictionary=True)
# Insert Day Header if non exists
	i = 0
	dataQuery = "SELECT id FROM receipt_kpi_header WHERE start_date='"+startDate+"' LIMIT 1"
	cursor.execute(dataQuery) 
	row = cursor.fetchone()
	if row:
		i=i+1
	
	if i == 0:
		dataQuery = "INSERT INTO receipt_kpi_header (id,start_date,working_minutes,daily_target,throughput,date_created,created_by,last_updated,last_updated_by)"
		dataQuery += " VALUES(DEFAULT,'"+startDate+"',0,0,0,now(),'upload',now(),'upload')"
		cursor.execute(dataQuery) 
		cnx.commit()
		
	cursor.close()	
	return "OK  -recHeader"
	
def recBody (startDate, dayDate, day, cnx):
# connection
	cursor = cnx.cursor(dictionary=True)
# Get rbtd / cts count
	i = 0
	rbtd = 0
	dataQuery = "SELECT COUNT(id) AS rbtd FROM transaction_history WHERE substr(date_created,1,10)='"+dayDate+"' AND substr(short_code,1,4)='RBTD' ORDER BY date_created"
	cursor.execute(dataQuery) 
	row = cursor.fetchone()
	if row:
		i=i+1
		rbtd = row['rbtd']
	i = 0
	cts = 0
	dataQuery = "SELECT COUNT(id) AS cts FROM transaction_history WHERE substr(date_created,1,10)='"+dayDate+"' AND substr(short_code,1,3)='CTS' ORDER BY date_created"
	cursor.execute(dataQuery) 
	row = cursor.fetchone()
	if row:
		i=i+1
		cts = row['cts']
	i = 0
	dataQuery = "SELECT id FROM receipt_kpi_body WHERE start_date='"+startDate+"' AND day_date = '"+dayDate+"' LIMIT 1"
	cursor.execute(dataQuery) 
	row = cursor.fetchone()
	if row:
		i=i+1
	if i == 0:
		dataQuery = "INSERT INTO receipt_kpi_body (id,start_date,day_date,day,daily_target,rbtd,cts,date_created,created_by,last_updated,last_updated_by)"
		dataQuery += " VALUES(DEFAULT,'"+startDate+"','"+dayDate+"','"+day+"',0,"+str(rbtd)+","+str(cts)+",now(),'upload',now(),'upload')"
	else:
		dataQuery = "UPDATE receipt_kpi_body SET rbtd="+str(rbtd)+", cts="+str(cts)+", last_updated=now(),last_updated_by='upload' WHERE start_date='"+startDate+"' AND day_date = '"+dayDate+"' LIMIT 1"
	cursor.execute(dataQuery)	
	cnx.commit()
	cursor.close()
	
	return "OK  -recBody"

def recDetail (startDate, dayDate, hour, cnx):
# connection
	cursor = cnx.cursor(dictionary=True)
	dataQuery = "SELECT id FROM receipt_kpi_detail WHERE start_date='"+startDate+"' AND day_date = '"+dayDate+"' AND hour = '"+hour+"' LIMIT 1"
	i = 0
	cursor.execute(dataQuery) 
	row = cursor.fetchone()
	if row:
		i=i+1
	if i == 0:
		dataQuery = "INSERT INTO receipt_kpi_detail (id,start_date,day_date,hour,working_minutes,hourly_target,date_created,created_by,last_updated,last_updated_by)"
		dataQuery += " VALUES(DEFAULT,'"+startDate+"','"+dayDate+"','"+hour+"',0,0,now(),'upload',now(),'upload')"
		cursor.execute(dataQuery)
		cnx.commit()
	dataQuery = "SELECT COUNT(id) AS rbtd FROM transaction_history WHERE substr(date_created,1,13)='"+dayDate+" "+hour[0:2]+"' AND substr(short_code,1,4)='RBTD' ORDER BY date_created"
	cursor.execute(dataQuery)
	rbtd = 0
	i = 0
	row = cursor.fetchone()
	if row:
		i=i+1
		rbtd = row['rbtd']
#	print("TEST1:",dataQuery,rbtd)
	dataQuery = "SELECT COUNT(id) AS cts FROM transaction_history WHERE substr(date_created,1,13)='"+dayDate+" "+hour[0:2]+"' AND substr(short_code,1,3)='CTS' ORDER BY date_created"
	cursor.execute(dataQuery)
	cts = 0
	i = 0
	row = cursor.fetchone()
	if row:
		i=i+1
		cts = row['cts']
	dataQuery = "UPDATE receipt_kpi_detail SET rbtd="+str(rbtd)+",cts="+str(cts)+" WHERE day_date='"+dayDate+"' AND hour='"+hour+"' LIMIT 1"
	cursor.execute(dataQuery)
	cnx.commit()
	cursor.close()
	return "OK  -recDetail"
	
def main():
	dayDate = datetime.date.today().strftime('%Y-%m-%d')
	if (len(sys.argv) > 1):
		dayDate = sys.argv[1]
	today = datetime.datetime.strptime(dayDate, '%Y-%m-%d')
	hour = datetime.datetime.now().strftime('%H:00')
	if (len(sys.argv) > 2):
		hour = sys.argv[2][0:2]+":00"
#	today = datetime.date.today()
	day = today.strftime('%a')
	tempDate = today - datetime.timedelta(days=today.weekday())
	startDate = tempDate.strftime('%Y-%m-%d')
	
# Default Connection / System Settings
	defaults = default_setting.defaultSettings()
# mySql Connector
	cnx = mysql.connector.connect(user=defaults['dbuser'], password=defaults['dbpwd'],host=defaults['dbhost'],database=defaults['dbase'])
# Call updates
# Prev.Hour
	if hour != "01:00":
		if hour == "00:00":
			prevHour = "23:00"
		else:
			hourInt = int(hour[0:2])-1
			prevHour = str(hourInt)+":00"
		if len(prevHour) < 5:
			prevHour = "0"+prevHour
		prevDay = day
		prevStartDate = startDate
		prevDayDate = dayDate
	else:
		prevHour = "00:00"
		tempDate = today - datetime.timedelta(days=1)
		prevDay = tempDate.strftime('%a')
		prevDayDate = tempDate.strftime('%Y-%m-%d')
		if prevDay == "Mon":
			prevStartDate = prevDayDate
		else:
			tempDate = tempDate - datetime.timedelta(days=tempDate.weekday())
			prevStartDate = tempDate.strftime('%Y-%m-%d')
		
	print(prevStartDate,prevDay,prevDayDate,prevHour)
		
	if (prevStartDate != startDate):
		response = recHeader(prevStartDate,cnx)
	if (prevDayDate != dayDate):
		response = recBody(prevStartDate,prevDayDate,prevDay,cnx)
	response = recDetail(prevStartDate,prevDayDate,prevHour,cnx)
# Current Hour
	print(startDate,day,dayDate,hour)
	response = recHeader(startDate,cnx)
	
	response = recBody(startDate,dayDate,day,cnx)
	response = recDetail(startDate,dayDate,hour,cnx)
	
if __name__ == '__main__':
	if (len(sys.argv) < 2):
		while True:
			main()
			for remaining in range(1800, 0, -1):
				sys.stdout.write("\r")
				sys.stdout.write("{:3d} seconds remaining.".format(remaining))
				sys.stdout.flush()
				time.sleep(1)
	else:
		main()