import pyodbc
import pandas as pd
#from datetime import timedelta

outfile=open(r"C:\Users\Stav\Documents\SeeboProjects\Sec-tools\FanucTable_output_5.tab", "w")
outfile.write("Tag"+"\t"+"start_zero_timestampe"+"\t"+"end_zero_timestampe"+"\t"+"zero_seconds_datediff"+"\n")

server = u'amit-test.database.windows.net'
database = u'SecoTools'
username = u'amit'
password = u'Seebo2smart'

cn = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password,autocommit=True)
cn2 = pyodbc.connect(
    'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password,autocommit=True)

sql = ' select top 90000 * from [dbo].[fanuc_data_fact] order by tag, timestamp;'
df = pd.read_sql(sql, cn)

# Create a cursor from the connection
cursor=cn.cursor()
cursor2=cn2.cursor()

cursor.execute(' select top 90000 * from [dbo].[fanuc_data_fact] order by tag, timestamp;')
row = cursor.fetchone()
row_1 = cursor.fetchone() #the next row
flag=False
flag1=False



tag=row[0]
if row[2]==0.0:
   start_zero_ts=row[1]
   flag=True
while row and row_1:
    ID=row[0]
    timestampe=row[1]
    value=row[2]
    ID_1=row_1[0]
    timestampe_1=row_1[1]
    value_1=row_1[2]
    if  (ID==ID_1 and value!=0.0 and value_1==0.0) or (ID!=ID_1 and value_1==0.0):
         start_zero_ts=row_1[1]
         flag=True
         print("first_ts",start_zero_ts)
    if  (ID==ID_1) and (value==0.0)  and (value_1==0.0)  :
        flag1=True
        print("continue")
    if (flag and flag1 and ((ID!=ID_1 and value==0.0) or (ID==ID_1 and value_1!=0.0 and value==0.0))):
            datediff=(timestampe-start_zero_ts).total_seconds() #float
            outfile.write(ID+"\t"+str(start_zero_ts)+"\t"+str(timestampe)+"\t"+str(datediff)+"\n")
            cursor2.execute("insert into fanuc_data_not_active_attr values (?,?,?,?)", ID, start_zero_ts ,timestampe ,datediff)
            cn2.commit()
            flag=False
            flag1=False



    row = row_1
    row_1 = cursor.fetchone()


outfile.close()
cursor.close()
cursor2.close()
del cursor,cursor2, cn, cn2