import cx_Oracle
import pymssql
from datetime import datetime, timedelta
import numpy as np
# select * from swzdh.rain;
# select * from swzdh.river;
# select * from swzdh.gw;



#麻峪雨量站
mayu_rainfall="30747850"

#陇驾庄水文站
ljz_swz="30700450"

#LQWB（龙泉）/ZSSC（再生水厂）/WTY（梧桐苑）/LSH（莲石湖）/HZZ（侯庄子）/GC（古城）
arr1=["30773702","30769360","30769280","30567304","30769320","30567303"]


haidian_rainfall="xxxx"

#SXC（双兴村93）/XM1（颐和园西门1）/XM2（颐和园西门2）/SYSC（水源三场336）/SJYY（四季御园2）/BW（北坞）
arr2=["30566324","30565080","30564400","30566335","30564840","30564880"]

#获取永定河15天的预测需要的数据
def get_ydh15_real_data():
    result =[]
    
    current_time = datetime.now()
    start_time =  current_time- timedelta(days=60)
    times=[]
    #时间序列
    for i in range(75):
        tmp = start_time +  timedelta(days=i)
        times.append(tmp.strftime("%Y-%m-%d"))
    
    #开始时间 结束时间
    start_str = start_time.strftime("%Y-%m-%d")
    end_str= current_time.strftime("%Y-%m-%d")    
    
   
    
    #麻峪数据
    mayu_data = get_data("rain",mayu_rainfall,start_str,end_str)
    mayu_dict={}  
    for i in range(len(mayu_data)):
        time = mayu_data[i]["time"]
        value =  mayu_data[i]["value"]
        if time not in mayu_dict:
            mayu_dict[time] = value
          
       
    #陇驾庄
    ljz_data =  get_data("river",ljz_swz,start_str,end_str)
    ljz_dict ={}
    for i in range(len(ljz_data)):
        time = ljz_data[i]["time"]
        value =  ljz_data[i]["value"]
        if time not in ljz_data:
            ljz_dict[time] = value
    
    mayu_value=[]
    ljz_value=[]
    
    for i in range(len(times)):
        tmp = times[i]
        if tmp in mayu_dict:
            mayu_value.append(tmp)
        else:
            mayu_value.append(0)
            
        if tmp in ljz_dict:
            ljz_value.append(tmp)
        else:
            ljz_value.append(0)
     
    
    result.append(times) 
    result.append(mayu_value) 
    result.append(ljz_value) 
    
    for i in(len(arr1)):
        data = get_data("gw",arr1[i],start_str,end_str)
        dictx ={}
        tmp_arr=[]
        for i in range(len(data)):
           time = data[i]["time"]
           value =  data[i]["value"]
           if time not in dictx:
               dictx[time] = value
               
               
        for i in range(len(times)):
            tmp = times[i]
            if tmp in dictx:
                tmp_arr.append(tmp)
            else:
                tmp_arr.append(0)
                
        result.append(tmp_arr) 
        
    np_arr = np.asarray(result)
    #数组转置
    np_result = np_arr.T
    
    return np_result


#获取玉泉山15天的预测需要的数据
def getyqs15_real_data():
    
    
    result =[]
    
    current_time = datetime.now()
    start_time =  current_time- timedelta(days=60)
    times=[]
    #时间序列
    for i in range(75):
        tmp = start_time +  timedelta(days=i)
        times.append(tmp.strftime("%Y-%m-%d"))
    
    #开始时间 结束时间
    start_str = start_time.strftime("%Y-%m-%d")
    end_str= current_time.strftime("%Y-%m-%d")    
    
   
    
    #麻峪数据
    hd_data = get_data("rain",haidian_rainfall,start_str,end_str)
    hd_dict={}  
    for i in range(len(hd_data)):
        time = hd_data[i]["time"]
        value =  hd_data[i]["value"]
        if time not in hd_dict:
            hd_dict[time] = value
          
       
    #陇驾庄
    ljz_data =  get_data("river",ljz_swz,start_str,end_str)
    ljz_dict ={}
    for i in range(len(ljz_data)):
        time = ljz_data[i]["time"]
        value =  ljz_data[i]["value"]
        if time not in ljz_data:
            ljz_dict[time] = value
    
    hd_value=[]
    ljz_value=[]
    
    for i in range(len(times)):
        tmp = times[i]
        if tmp in hd_dict:
            hd_value.append(tmp)
        else:
            hd_value.append(0)
            
        if tmp in ljz_dict:
            ljz_value.append(tmp)
        else:
            ljz_value.append(0)
     
    
    result.append(times) 
    result.append(hd_value) 
    result.append(ljz_value) 
    
    for i in(len(arr2)):
        data = get_data("gw",arr2[i],start_str,end_str)
        dictx ={}
        tmp_arr=[]
        for i in range(len(data)):
           time = data[i]["time"]
           value =  data[i]["value"]
           if time not in dictx:
               dictx[time] = value
               
               
        for i in range(len(times)):
            tmp = times[i]
            if tmp in dictx:
                tmp_arr.append(tmp)
            else:
                tmp_arr.append(0)
                
        result.append(tmp_arr) 
        
    np_arr = np.asarray(result)
    #数组转置
    np_result = np_arr.T
    
    return np_result



####################################################

def get_data(types,num,start_time,end_time):

    if types =='river':
        return river_list(start_time,end_time,num)
    if types =='rain':
        return rain_list(start_time,end_time,num)
    
    if types =='gw':
        return gw_list(start_time,end_time,num)
    
#河流 水文站
def river_list(start_time,end_time,STCD):
    # 建立与Oracle数据库的连接
    connection = cx_Oracle.connect('mzy/mzy_^22dPoO0@192.168.44.8:1521/swzdh') 
    # 创建游标对象
    cursor = connection.cursor()

    sql = """  
    SELECT * FROM swzdh.river  
    WHERE STCD = :STCD and tm BETWEEN TO_DATE(:start_time, 'YYYY-MM-DD') AND TO_DATE(:end_time, 'YYYY-MM-DD')  order by tm ASC
    """
    res =[]
    try:
        cursor.execute(sql, {'start_time': start_time, 'end_time': end_time,'STCD':STCD}) 
        
        column_names = [row[0] for row in cursor.description]  
        print("字段名:", column_names)  
    
        # 获取所有结果集
        result = cursor.fetchall()
        
        # 输出结果
        for row in result:     
            times = row[2]
            date_str = times.strftime("%Y-%m-%d")
            dicts={"time":date_str,"value":row[4],"stname":row[1]}
            res.append(dicts)
          
    except Exception as e:
            print("Error occurred: ", str(e))
    finally:
    # 关闭游标和连接
        cursor.close()
        connection.close()
    return res
   
   #降雨量 
def rain_list(start_time,end_time,STCD):
    # 建立与Oracle数据库的连接
    connection = cx_Oracle.connect('mzy/mzy_^22dPoO0@192.168.44.8:1521/swzdh') 
    # 创建游标对象
    cursor = connection.cursor()

    sql = """  
    SELECT * FROM swzdh.rain  
    WHERE STCD = :STCD and tm BETWEEN TO_DATE(:start_time, 'YYYY-MM-DD') AND TO_DATE(:end_time, 'YYYY-MM-DD')  order by tm ASC
    """
    res =[]
    try:
        cursor.execute(sql, {'start_time': start_time, 'end_time': end_time,'STCD':STCD}) 
        
        column_names = [row[0] for row in cursor.description]  
        print("字段名:", column_names)  
    
        # 获取所有结果集
        result = cursor.fetchall()
        print(result)
        
        date_str_arr=[]
        date_str_dict={}
        # 输出结果
        name =""
        for row in result:  
            name=  row[1]
            times = row[2]
            date_str = times.strftime("%Y-%m-%d")
            R =  row[3]
            if date_str in date_str_dict:
                date_str_dict[date_str] = date_str_dict[date_str] + R
            else:
                date_str_dict[date_str] = R
                date_str_arr.append(date_str)
        for item in date_str_arr:
           value = round(date_str_dict[item],2)
           temp ={"time":item,"value":value,"stname":name}
           res.append(temp)
        
    except Exception as e:
            print("Error occurred: ", str(e))
    finally:
    # 关闭游标和连接
        cursor.close()
        connection.close()
    return res 

    
# 地下水监测井  两个表 V_WT_YRS，V_Z_YRS
def gw_list(start_time,end_time,STCD):

    conn = pymssql.connect(server='192.168.44.66',
                           user='xsyrs', 
                           password='gws@xsyrs2024', 
                           database='DB_DXS',
                           as_dict=True)
    
    cursor = conn.cursor()
    res =[]
    try:
        sql = "SELECT * FROM V_Z_YRS where STCD = '" +STCD +"' and TM >= '"+start_time +"' and TM <= '"+end_time +"' order by TM ASC" 
        cursor.execute(sql)
        result = cursor.fetchall()
    
        for row in result:
            times = row["TS"]
            date_str = times.strftime("%Y-%m-%d")
            value =  float(row["Z"])
            bd = float(row["BD"])
            dicts={"time":date_str,"value":value,"bd":bd}
            res.append(dicts)
    
        
    except Exception as e:
        print("Error occurred:", str(e))
    finally:
        cursor.close()
        conn.close()
    return res