#!/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