import pandas as pd
import numpy as np
import datetime
import pymysql
from sqlalchemy import create_engine, true
from urllib import parse
import time
import phoenixdb


def test_card_flow():
    database_url =  'http://hb-bp182rj5nf626041g-proxy-phoenix.hbase.rds.aliyuncs.com:8765'
    conn = phoenixdb.connect(database_url,autocommit=True)
    cursor=conn.cursor()
    #cursor.execute("CREATE TABLE algo_safety_score_d(id INTEGER(255) PRIMARY KEY,create_time VARCHAR(55),update_time VARCHAR(55),sn VARCHAR(55),score_d FLOAT(5),risk_index VARCHAR(255))")#)
    #cursor.execute("UPSERT INTO algo_dwd_indicator VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", (1,'PK500A20100000783','2022-12-04 12:00:00','2022-12-04 14:00:00','1',30,100,70,25,15,10,203,70,1.5,1,1))
    #cursor.execute("UPSERT INTO algo_dwd_indicator VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", (2,'PK500A20100000783','2022-12-05 12:00:00','2022-12-04 14:00:00','1',25,80,55,35,15,20,100.1,70.2,1.5,1,0))
    #cursor.execute("DROP TABLE algo_safety_score_d")
    #cursor.execute("CREATE TABLE algo_all_fault_info_done(id INTEGER PRIMARY KEY,start_time VARCHAR(55),end_time VARCHAR(55),sn VARCHAR(55),IMEI VARCHAR(55),fault_lvl INTEGER(1), fault_code VARCHAR(4),fault_reason VARCHAR(55),fault_advice VARCHAR(55),fault_location VARCHAR(255),odo FLOAT(7),creat_time VARCHAR(55),update_time VARCHAR(55),create_by VARCHAR(10),update_by VARCHAR(10),is_delete BOOLEAN(1))")
    #cursor.execute("UPSERT INTO algo_all_fault_info_done VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", (1,'2022-11-29 12:00:00','2022-11-29 13:00:00','PK500A20100000783','MKYWXN640N19BK472',2,'C201','测试——原因','测试——建议','测试——位置',1212341,'2022-11-29 13:00:00','2022-11-29 13:00:00','寿学琦','寿学琦',0))
    #cursor.execute("UPSERT INTO algo_safety_score_h VALUES (?,?,?,?,?,?,?)", ('1392720306_PK50201A000002129_9223370366123612059','2022-11-29 12:00:00','2022-11-29 12:00:00','PK500A20100000783',0,'88.51','{"bat_cell":"96","env":"96","electric":"96","charge":"96","connnect":"96","over_discharge":"96","bms":"96","high_volt":"96"}'))
    #for i in range(0,23):
    #i=4
    #cursor.execute("UPSERT INTO tb_hours_status VALUES (?,?,?,?,?,?)", (i,'2022-11-29 12:00:00','2022-11-29 12:00:00','PK500A20100000783','0',i))
    # cursor.execute("DROP TABLE algo_all_fault_info_ing")
    #cursor.execute("CREATE TABLE algo_safety_score_d(id INTEGER PRIMARY KEY,create_time INTEGER(2),sn VARCHAR(55),safety_score_d FLOAT(3))")#)
    #cursor.execute("CREATE TABLE algo_all_fault_info_done (id INTEGER PRIMARY KEY,start_time INTEGER(13),end_time INTEGER(13),sn VARCHAR(55),vin VARCHAR(55),fault_lvl INTEGER(1),fault_code VARCHAR(4),fault_reason VARCHAR(55),fault_advice VARCHAR(55),fault_location VARCHAR(255),odo FLOAT(7),creat_time INTEGER(13),create_by VARCHAR(10),update_by VARCHAR(10),is_delete BOOLEAN(1))")
    #cursor.execute("UPSERT INTO algo_all_fault_info_done VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", (1,1667990161,1667990163,'PK500A20100000783','PK500A20100000783',1,'C101','测试——原因','测试——建议','测试——位置',1212341,1667990161,'寿学琦','寿学琦',0))
    #cursor.execute("UPSERT INTO algo_safety_score_d VALUES  (?,?,?,?)",(1,1,'PK500A20100000783',96.5))
    cursor.execute("SELECT * FROM algo_safety_score_h ")#WHERE sn =PK500A20100000783 d ORDER BY ID DESC LIMIT 1
    #cursor.execute("DELETE FROM algo_safety_score_h WHERE {} =0".format('hour'))
    df=pd.DataFrame(cursor.fetchall())
    print(df)
    # df.to_excel(r'C:\Users\Eric4.12V\Desktop\tempout.xlsx')

if __name__ == '__main__':
    test_card_flow()
    
    # {"lvl1":{"confirm_time":"60","remove_time":"60","over_vol":"3.5"},
    #  "lvl2":{"confirm_time":"60","remove_time":"60","over_vol":"3.6"},
    #  "lvl3":{"confirm_time":"60","remove_time":"60","over_vol":"3.7"},
    #  }