#!/usr/bin/python
 
 
import os,sys
from datetime import datetime,timedelta
import cx_Oracle as orcl
import mysql.connector as pymysql
from pprint import pprint
 
alarms = {198087342:'2G'}
 
db=orcl.connect('sys','oracle','minos',mode=orcl.SYSDBA)
cursor =db.cursor()
cursor.execute("select position1,customattr5,to_char(alarmraisedtime_gmt,'YYYY-MM-DD HH24:MI:SS'),code from CAFFM4X.currentalarm  where code in ('198087342')")
p=cursor.fetchall()
 
print p
 
 
conn = pymysql.connect(user='omcr',password='omcr',host='117.239.216.50',database='omcr')
mycursor = conn.cursor()
 
for j in p:
	values =[j[1][0:17],j[1][0:15],j[0].split('@')[-2],'Zte',alarms[j[3]],datetime.strptime(j[2],'%Y-%m-%d %H:%M:%S')]
	#bts_name=j[1].split('_')[-2]
	print values
	sql=("insert ignore into omcr.currentbtsdown_sectors(sector_name,bts_name,bsc_name,make,tech,down_time) values(%s,%s,%s,%s,%s,%s)")
        mycursor.execute(sql,(values))
 
 
cursor.execute("select position1,customattr5,to_char(alarmraisedtime_gmt,'YYYY-MM-DD HH24:MI:SS'),to_char(alarmclearedtime,'YYYY-MM-DD HH24:MI:SS'),code from CAFFM4X.historyalarm  \
where code in ('198087342') and alarmclearedtime>sysdate-3/24")
 
outage = cursor.fetchall()
 
for k in outage:
	values =[k[1][0:17],k[1][0:15],k[0].split('@')[-2],'Zte',alarms[k[4]],datetime.strptime(k[2],'%Y-%m-%d %H:%M:%S'),datetime.strptime(k[3],'%Y-%m-%d %H:%M:%S')]
	#print values
	sql = ("replace into omcr.currentbtsdown_sectors(sector_name,bts_name,bsc_name,make,tech,down_time,up_time) values(%s,%s,%s,%s,%s,%s,%s)")
	mycursor.execute(sql,(values))
 
 
conn.commit()
conn.close()
# your code goes here

IyEvdXNyL2Jpbi9weXRob24KCgppbXBvcnQgb3Msc3lzCmZyb20gZGF0ZXRpbWUgaW1wb3J0IGRhdGV0aW1lLHRpbWVkZWx0YQppbXBvcnQgY3hfT3JhY2xlIGFzIG9yY2wKaW1wb3J0IG15c3FsLmNvbm5lY3RvciBhcyBweW15c3FsCmZyb20gcHByaW50IGltcG9ydCBwcHJpbnQKCmFsYXJtcyA9IHsxOTgwODczNDI6JzJHJ30KCmRiPW9yY2wuY29ubmVjdCgnc3lzJywnb3JhY2xlJywnbWlub3MnLG1vZGU9b3JjbC5TWVNEQkEpCmN1cnNvciA9ZGIuY3Vyc29yKCkKY3Vyc29yLmV4ZWN1dGUoInNlbGVjdCBwb3NpdGlvbjEsY3VzdG9tYXR0cjUsdG9fY2hhcihhbGFybXJhaXNlZHRpbWVfZ210LCdZWVlZLU1NLUREIEhIMjQ6TUk6U1MnKSxjb2RlIGZyb20gQ0FGRk00WC5jdXJyZW50YWxhcm0gIHdoZXJlIGNvZGUgaW4gKCcxOTgwODczNDInKSIpCnA9Y3Vyc29yLmZldGNoYWxsKCkKCnByaW50IHAKCgpjb25uID0gcHlteXNxbC5jb25uZWN0KHVzZXI9J29tY3InLHBhc3N3b3JkPSdvbWNyJyxob3N0PScxMTcuMjM5LjIxNi41MCcsZGF0YWJhc2U9J29tY3InKQpteWN1cnNvciA9IGNvbm4uY3Vyc29yKCkKCmZvciBqIGluIHA6Cgl2YWx1ZXMgPVtqWzFdWzA6MTddLGpbMV1bMDoxNV0salswXS5zcGxpdCgnQCcpWy0yXSwnWnRlJyxhbGFybXNbalszXV0sZGF0ZXRpbWUuc3RycHRpbWUoalsyXSwnJVktJW0tJWQgJUg6JU06JVMnKV0KCSNidHNfbmFtZT1qWzFdLnNwbGl0KCdfJylbLTJdCglwcmludCB2YWx1ZXMKCXNxbD0oImluc2VydCBpZ25vcmUgaW50byBvbWNyLmN1cnJlbnRidHNkb3duX3NlY3RvcnMoc2VjdG9yX25hbWUsYnRzX25hbWUsYnNjX25hbWUsbWFrZSx0ZWNoLGRvd25fdGltZSkgdmFsdWVzKCVzLCVzLCVzLCVzLCVzLCVzKSIpCiAgICAgICAgbXljdXJzb3IuZXhlY3V0ZShzcWwsKHZhbHVlcykpCgoJCmN1cnNvci5leGVjdXRlKCJzZWxlY3QgcG9zaXRpb24xLGN1c3RvbWF0dHI1LHRvX2NoYXIoYWxhcm1yYWlzZWR0aW1lX2dtdCwnWVlZWS1NTS1ERCBISDI0Ok1JOlNTJyksdG9fY2hhcihhbGFybWNsZWFyZWR0aW1lLCdZWVlZLU1NLUREIEhIMjQ6TUk6U1MnKSxjb2RlIGZyb20gQ0FGRk00WC5oaXN0b3J5YWxhcm0gIFwKd2hlcmUgY29kZSBpbiAoJzE5ODA4NzM0MicpIGFuZCBhbGFybWNsZWFyZWR0aW1lPnN5c2RhdGUtMy8yNCIpCgpvdXRhZ2UgPSBjdXJzb3IuZmV0Y2hhbGwoKQoKZm9yIGsgaW4gb3V0YWdlOgoJdmFsdWVzID1ba1sxXVswOjE3XSxrWzFdWzA6MTVdLGtbMF0uc3BsaXQoJ0AnKVstMl0sJ1p0ZScsYWxhcm1zW2tbNF1dLGRhdGV0aW1lLnN0cnB0aW1lKGtbMl0sJyVZLSVtLSVkICVIOiVNOiVTJyksZGF0ZXRpbWUuc3RycHRpbWUoa1szXSwnJVktJW0tJWQgJUg6JU06JVMnKV0KCSNwcmludCB2YWx1ZXMKCXNxbCA9ICgicmVwbGFjZSBpbnRvIG9tY3IuY3VycmVudGJ0c2Rvd25fc2VjdG9ycyhzZWN0b3JfbmFtZSxidHNfbmFtZSxic2NfbmFtZSxtYWtlLHRlY2gsZG93bl90aW1lLHVwX3RpbWUpIHZhbHVlcyglcywlcywlcywlcywlcywlcywlcykiKQoJbXljdXJzb3IuZXhlY3V0ZShzcWwsKHZhbHVlcykpCgoKY29ubi5jb21taXQoKQpjb25uLmNsb3NlKCkKIyB5b3VyIGNvZGUgZ29lcyBoZXJl