
v__c           @   s   d  d l  Z  d  d l Z d  d l Z d  d l Z d  d l m Z d  d l m Z d  d l Z d  d l m Z d  d l	 j
 Z
 d  d l m Z d  d l m Z d  d l  m Z d e f d	     YZ d S(
   iN(   t   datetime(   t	   timedelta(   t   OrderedDict(   t   DataContainer(   t   DBConnector(   t   ProgrammingErrort   DataDBc           B   s  e  Z d  Z i i i d d 6d d 6d 6i d d 6d[ d 6d	 6i d
 d 6d d 6d 6i d d 6d d 6d 6i d d 6d\ d 6d 6i d d 6d d 6d 6i d d 6d d 6d 6i d d 6d] d 6d 6i d d 6d d 6d 6i d_ d 6da d 6d 6i d d 6d d 6d 6i d d 6d d 6d 6i d d 6d d 6d 6d  6i i d d 6d d 6d! 6i d
 d 6d" d 6d# 6i d
 d 6d d 6d$ 6d% 6i i d d 6d d 6d& 6i d d 6d d 6d 6d' 6Z i i d( d) g d 6d* d+ g d 6d, d- 6d. 6i d/ d0 g d 6d1 d2 g d 6d3 d- 6d4 6i dc d 6de d 6d6 6i dg d 6di d 6d7 6Z d8   Z d9   Z e d:    Z e d;    Z	 e d<    Z
 e d=    Z e d>    Z d?   Z d@   Z dA   Z dB   Z dC   Z dD   Z dE   Z dF   Z e dj e dG   Z e dj dH   Z e dI    Z dJ   Z e dK    Z dL   Z dM   Z dN   Z dO   Z dP   Z  dQ   Z! dR   Z" dS   Z# dT   Z$ dU   Z% dV   Z& dW   Z' dX   Z( dY   Z) RS(k   s   %Y-%m-%d %H:%M:%Sg        t   mini<   t   maxt   F_ACi   i   i?   t   E_TOTALii   t   T_INVii  t
   I_DC_TOTALi  i  t   P_DCi  t   U_DCt   I_DC_i   t   P_DC_t   U_DC_i
   t   Q_ACii N  t   P_AC_L1t   P_AC_L2t   P_AC_L3t   INVERTERt   P_RADid   t   T_Mt   T_At   METEOt   I_t	   STRINGBOXgߌ3gb,gb,8gߌ3Gi&   t	   precisiont   realgXgXw	gXw	 gXi5   t   floati   t   intt   bigintc         C   s_   t  j |  | | | |  t j |  _ d |  _ i  |  _ d  |  _ d  |  _ i  |  _	 d  |  _
 d  S(   Ni    (   R   t   __init__t   settingst   loggert   retry_countt   device_infot   Nonet   _DataDB__plant_timezonet   _DataDB__plant_rest   _DataDB__day_min_yieldt   nominal_acp(   t   selft   hostt   usert   passwordt   database(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyR"   d   s    					c         C   sE   t  d k rA d j d | d |  } | j |  |  j j   n  d  S(   NR   s   IF NOT EXISTS ( SELECT *  FROM sys.columns WHERE  object_id = OBJECT_ID(N'[dbo].[{_tbl}]')   
                     AND name = 'COL_E_TODAY_CALC') 
                     ALTER TABLE {_tbl1} ADD COL_E_TODAY_CALC bigintt   _tblt   _tbl1(   t   typet   formatt   executest   _connectiont   commit(   R,   t   curt   device_typet   tblt   sql(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __create_etoday_for_inverterx   s
    	c         C   sS   | d d k rO | d  k r* d | |  <t S| d | d k rO d | |  <t Sn  t S(   Nt   versiont   ignoret   UNKNOWN_DEVICEt   UNKNOWN_DEVICE_VERSION(   R'   t   Falset   True(   t	   device_idR&   t   device_datat   list_status_code(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __check_device_version   s    

c         C   sK   d j  d |  } |  j | | f  |  j   } x | D] } | d SWd  S(   Ns   USE {_db};
                 SELECT TOP 1 device_value
                 FROM xxx_RAW_Device_Properties
                 WHERE table_name=%s AND device_name='Config_Version' 
                 ORDER BY ID ASCt   _dbi    (   R4   t   executet   fetchall(   R8   t   dbR:   R;   t   rest   row(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __get_device_version   s
    c         c   s7   x0 t  d t |   |  D] } |  | | | !Vq Wd  S(   Ni    (   t   ranget   len(   t   arrt
   chunk_sizet   i(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __chunk_arr   s    c         C   s   i  } d j  d |  } |  j | | f  |  j   } xI | D]A } | \ } } }	 }
 } i | d 6|	 d 6|
 d 6| d 6| | <q> W| S(   Ns&  USE {_db};
                 SELECT A.device_value, A.column_name, A.device_vpc, B.value_type, B.datatype
                 FROM xxx_RAW_Device_Properties AS A
                 JOIN commondb.dbo.value_type AS B ON A.device_vpc=B.vpc
                 WHERE table_name=%s AND device_vpc is not NULLRG   t   column_namet   vpct
   value_typet	   data_type(   R4   RH   RI   (   R8   RJ   R:   t   valuesR;   RK   RL   t   vidt   colnameRU   RV   RW   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __get_device_values   s    
c         C   s   |  d j    d k r i  } |  d } x5 | D]- } | | } | d d	 k r- d | | <q- q- Wxz | D]o } | | } x\ | D]T } | j |  } | d  k	 r| | d k r| t t t |  | |   | | <q| q| Wqe Wn  d  S(
   NR3   R   RX   RV   t	   E_DAY_KWHt   E_TOTAL_KWHi  t   NULL(   R\   R]   (   t   uppert   getR'   t   strR    R   (   R&   t   datat   vid_to_scaleRX   RY   t   valuet   tst   d(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __scale_data   s    


c   	      C   s  i  } d j  d |  j  } d j  d | d |  } | j | | f  | j   } | d  k rl d | | <d  S| d | d <| d	 | d
 <d } | j | | d f  | j   } | d | d <|  j | | d | d  t j | |  j | d  | d <t j | |  j | d  | d <| S(   Ns  USE {_db};
                 SELECT TOP 1 A.Table_name, B.Table_Name
                 FROM xxx_Device_Mappings AS B
                 INNER JOIN xxx_RAW_Device_Properties AS A ON B.RAW_Table_Name=A.TABLE_NAME
                 WHERE A.DEVICE_NAME='ldid' AND A.DEVICE_VALUE=%sRG   s   {_lid}_{_did}t   _lidt   _didR?   i    R:   i   t   dest_tbls   SELECT DEVICE_VALUE FROM xxx_Raw_Device_Properties
                 WHERE TABLE_NAME=%s AND
                 DEVICE_NAME='Device_Class'R3   R=   RX   (	   R4   R0   RH   t   fetchoneR'   t#   _DataDB__create_etoday_for_inverterR   t   _DataDB__get_device_versiont   _DataDB__get_device_values(	   R,   R8   t	   logger_idRC   t   list_status_codesR&   R;   t   ldidRK   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __get_device_info   s,    	
c         C   sk   |  j  d  k rd d } | j |  | j   } d } x | D] } | d } q; Wt j |  |  _  n  |  j  S(   NsY   SELECT Value FROM xxx_Plant_Device_Properties WHERE Value_key='plant.timezone.identifier's   Europe/Berlini    (   R(   R'   RH   RI   t   pytzt   timezone(   R,   R8   R;   RK   t   tzIdentifierRL   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __get_plant_timezone   s    c         C   sZ   |  j  d  k rS d } | j |  | j   } | d  k	 rS t | d  |  _  qS n  |  j  S(   Nsi   SELECT Value FROM xxx_Plant_Device_Properties
                    WHERE Value_key='plant.data.resolution'i    (   R)   R'   RH   Rk   R    (   R,   R8   R;   RK   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __get_plant_res  s    c         C   s   | j  d  } | j  d  } t j | t j  } t j j |  } | j |  j	 |   } | j
 d d d d d d  }	 |	 j t j  }
 t j |
 t j  } d j d | d	  } | j | | | f  | j   } | d  k	 r | d } n d } | S(
   NRC   R&   t   houri    t   minutet   seconds   SELECT TOP 1 COL_E_TODAY_CALC FROM dbo.{_tbl}
                 WHERE Date_and_time >= %s AND Date_and_time < %s
                 AND COL_E_TODAY_CALC IS NOT NULL ORDER BY Date_and_time DESCR1   Rj   (   R`   R    t   strptimeR   t   fmtRs   t   utct   localizet
   astimezonet   _DataDB__get_plant_timezonet   replacet   strftimeR4   RH   Rk   R'   (   R,   R8   t   deviceRe   RC   R&   t   dttst   ts_UTC_localizedt   ts_plantt   ts_midnightt   start_time_dttst
   start_timeR;   RK   t   last_etoday_yield(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __get_last_etoday  s    c         C   s  | j  d  } | j  d  } t j | t j  } t j j |  } | j |  j	 |   }	 |	 j
 d d d d d d  }
 |	 j
 d d d d d d  } |  j j  |  } | r | j  |
 j t j   } | r | Sn  |
 j t j  } | j t j  } | d  k r|  j j d	  d  Sy6 d
 j d | d d |  } | j | | | f  Wn t k
 r2} d | d k rd } | j | | j  d   d  } | j   } x | D] } | d } qW| rd j d | d | d  } | j | | | f  q/|  j j d j d |   q3|  j j |  d  Sn X| j   } d  } x | D] } | d } qLW| sX|  j j d  | j  d  } d  } | d } x2 | D]* } | | } | d d k r| } PqqWx | D] } | | k r|  j j d j d |   d  S| j  |  } | j  |  } | d  k r@|  j j d  q|  j j d  PqWn  |  j j  |  s}t   |  j | <n  | |  j | |
 j t j  <| S(   NRC   R&   Rx   i    Ry   Rz   i   i;   sK   DataDB: can't get first yield of the day since there is no device info set.s   SELECT TOP 3 {_col} FROM dbo.{_tbl}
                 WHERE Date_and_time >= %s AND Date_and_time <= %s
                 AND {_col} IS NOT NULL ORDER BY Date_and_time ASCR1   Rj   t   _cols   Invalid column namei   s   SELECT Column_Name FROM dbo.xxx_virtual_devices WHERE table_name=%s AND ((Device_Class='E' OR Device_Class='E_TOTAL') AND (Device_Class_2 = 'E_LIFE' OR Device_Class_2='E_TOTAL'))s   SELECT TOP 3 {_column} FROM dbo.{_tbl}
                             WHERE Date_and_time >= %s AND Date_and_time <= %s
                             AND {_column} IS NOT NULL ORDER BY Date_and_time ASCt   _columnsU   Could not find E_TOTAL value for {_did} (Searched: COL_SR_196767 and class_2 E_TOTAL)Ri   sI   DataDB: no first day yield available from database. Searching in data nowRb   RX   RV   R]   sA   DataDB: no first yield of the day found for this timestamp: {_ts}t   _tssk   DataDB: can't get first yield of the day since there is datum available for value type E_TOTAL_KWH in data.s%   DataDB: found day yield from set data(   R`   R    R{   R   R|   Rs   R}   R~   R   R   R   R*   R   R'   R$   t   warningR4   RH   t	   ExceptionRI   t   debugt   errort   infoR   (   R,   R8   R   Re   t   col_total_yieldRC   R&   R   R   R   R   t   ts_end_of_dayt   device_yieldt   midnight_yieldR   t   end_timeR;   t   errRT   RK   RL   t	   res_yieldRb   t	   yield_vidRX   RY   Rd   t   next_tsRf   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __get_first_yield_of_the_day8  s    				

		
c         C   s   | j  d  } d  } d  } | d } xA | D]9 } | | }	 |	 d d k r, | } |	 j  d  }
 Pq, q, Wx2 | D]* } | | }	 |	 d d k rp | } Pqp qp W| d  k	 r |  j | | | | |
  n% | d  k	 r |  j | | | |  n  d  S(	   NR&   RX   RV   R]   R
   t   col_namet
   P_AC_TOTAL(   R]   R
   (   R`   R'   t%   _DataDB__calc_etoday_from_total_yieldt   _DataDB__calc_etoday_from_acp(   R,   R8   R   Rp   R&   t   vid_total_yieldt   vid_pacRX   RY   Rd   R   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __calc_etoday  s*    


c         C   s  | j  d  } | j  d  } |  j |  } xc| D][}	 |  j | | |	 |  }
 |
 d  k	 r4 | j  |	  } | j  |  } | d  k	 r| d k ry t |  t |
  } Wn/ t k
 r } t t |   t |
  } n X| d k r t |  | d <nB |  j j	 d t |  d t |
   |  j j	 d t |   | d	 } | j  d  d  k ri d
 d 6d d 6d
 d 6d
 d 6| d <qqq4 q4 Wd  S(   NR&   Rb   R^   i    t   COL_E_TODAY_CALCs   Calculated negative value s'    for COL_E_TODAY_CALC. Midnight yield: s   Current total yield: RX   u   COL_E_TODAY_CALCRV   u   bigintRW   RU   RT   (
   R`   R   t#   _DataDB__get_first_yield_of_the_dayR'   t   longt
   ValueErrorR   Ra   R$   R   (   R,   R8   R   Rp   R   R   R&   Rb   t   tzRe   R   Rf   t   current_total_yieldt   col_e_today_calc_valt   eRX   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __calc_etoday_from_total_yield  s8    		

c         C   s   |  j  j d  | j d  } |  j |  } | d  k sF | d k rZ |  j  j d  d  S| j d  } t t | j     } x| D]}	 | j |	  }
 |
 j |  } t	 j
 |	 |  j  t d | d  } t	 j | |  j  } | d  k	 r | d k r | j |  d  k	 r*| | j d  } n |  j | | |	  } y: t |  t |  d } t t |  t |   } Wn t k
 r} q n X| d	 k rt |  |
 d <n |  j  j d
 j d |   | d } | j d  d  k ri d d 6d d 6d d 6d d 6| d <qq q Wd  S(   Nt   __calc_etoday_from_acpR&   R^   s@   No resolution defined for plant. Can't calculate etoday_from_acpRb   t   secondsi<   R   i    s    Calculated negative value {_val}t   _valRX   u   COL_E_TODAY_CALCRV   u   bigintRW   RU   RT   (   R$   R   R`   t   _DataDB__get_plant_resR'   R   t   listt   sortedt   keysR    R{   R|   R   R   t   _DataDB__get_last_etodayR   Ra   R   R4   (   R,   R8   R   Rp   R   R&   RK   Rb   t   ts_arrRe   Rf   t   acpt	   prev_dttst   prev_tst   prev_yield_todayt   current_yieldR   R   RX   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyR     sF    	 


c         C   s1  |  j  d  } |  j  d  } | r3 d } d } n d } d } | j  d  } t   } t j   }	 x| D]}
 | j  |
  } | d j d	 | d
  7} | d 7} xM| D]E} t j | t j  } | | k r q n  |	 | } t | j	    } |	 j
 d  } | j  |  } | j  |  d  k r| rfd j d |  d j d |  d j d |  g | | <qd j d |  d j d |  g | | <n  | j  |
 d  } | j  d  j   d& k r| | j d j d |   q | | j |  q Wqj Wd } t } g  } x t |  D] \ } } | j  |  } | r_t } | d j d d j |   7} n | d j d d j |   7} | d d  d! k rd" j d# | d$ d	 | d% |  } d } t } t |  d! k r| j |  qqqWt |  d! k r-d" j d# | d$ d	 | d% |  } | j |  n  | S('   NRb   R&   s*   Date_and_time, Storage_time, Storage_delays
   %s, %s, %ss   Date_and_time, Storage_times   %s, %sRX   s   , {_col}R   RT   s   , %ss   %Y-%m-%d %H:%M:%Ss   '{_ts}'R   s   '{_sec}'t   _secR^   RW   t   textt   strings   nvarchar(32)s   varchar(32)s   varchar(200)s   '{_v}'t   _vt    s   ({_d})t   _dt   ,s   ,({_d})i   i  i    s)   INSERT INTO {_tbl} ({_col}) VALUES {_val}R1   Rj   R   (   R   R   s   nvarchar(32)s   varchar(32)s   varchar(200)(   R`   R   R    t   utcnowR4   R{   R   R|   R    t   total_secondsR   R'   t   lowert   appendRB   t	   enumerateRA   t   joinRO   (   R   t   date_filter_listt   storage_delayRb   R&   t   sql_column_namet   sql_value_data_placeholderRX   t   sql_datat   now_objRY   Rd   Re   t   ts_objt   delayt   dsect   nowt	   data_dictt   vt   sql_rowt	   first_rowt   prepared_sql_insert_queriest   idxRL   R;   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __prepare_sql_string7  sn    		

"	" "		c         C   s  |  j  d  } |  j  d  } d } | j  d  } t   } t j   j d  } t   } x| D]}	 | j  |	  }
 | d j d |
 d  7} xS| D]K} | j  |  } | j  |  d  k r d	 j d
 |  d	 j d
 |  g | | <n  | j  |	 d  } |
 j  d  j   d" k r9| | j d j d |   n | | j |  | j  |  d  k rld | | <n  | | c d j d |
 d  7<|
 j  d  j   d# k r| | c d j d |  7<q | | c d j d |  7<q Wqa Wg  } d } | j	   } t
 t j | |   } x | D] } d } xr | D]j } | j  |  } d j d d j |   } | d j d | d d | d | d
 | d  | | d!   7} q4W| j |  q!W| S($   NRb   R&   s   Date_and_time, Storage_timeRX   s   %Y-%m-%d %H:%M:%Ss   , {_col}R   RT   s   '{_ts}'R   R^   RW   R   R   s   nvarchar(32)s   varchar(32)s   varchar(200)s   '{_v}'R   R   s   {_col}=s   '{_v}',s   {_v},i2   s   ({_d})R   R   s  IF NOT EXISTS(SELECT * FROM {_tbl} WHERE Date_and_time='{_ts}')
                            BEGIN
                            INSERT INTO {_tbl} ({_col}) VALUES {_val}
                            END
                        ELSE
                            BEGIN
                            UPDATE {_tbl}
                            SET {_update_sql}
                            WHERE Date_and_time='{_ts}'
                            END;R1   Rj   R   t   _update_sqli(   R   R   s   nvarchar(32)s   varchar(32)s   varchar(200)(   R   R   s   nvarchar(32)s   varchar(32)s   varchar(200)(   R`   R   R    R   R   R4   R'   R   R   R   R   R   t   _DataDB__chunk_arrR   (   R   R   Rb   R&   R   RX   R   R   t
   update_sqlRY   Rd   Re   R   R   t   prepared_sql_queriesRQ   R   t	   ts_chunkst   chunkR;   RL   t   sql_insert_row(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __prepare_sql_overwrite  sT    			"  $	c         C   s   | j  d  } t | j    } t j t t |   t j  } t j t t	 |   t j  } | j  d  } d j
 d | d  } |  j | | | f  |  j   } g  }	 x | D] }
 |	 j |
 d  q W|	 S(   NRb   R&   so   SELECT Date_and_time AS [date] FROM dbo.{_tbl}
                 WHERE Date_and_time>=%s AND Date_and_time <= %sR1   Rj   i    (   R`   R   R   R    R{   t   nextt   iterR   R|   t   reversedR4   RH   RI   R   (   R8   R   Rb   t   datest   start_date_utct   end_date_utcR&   R;   RK   t   date_filterRL   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __get_date_data_from_device  s    !!c      	   C   s&  |  j  j d j d t j   d | j d | j d    t | d  d k ry |  j  j d j d | j d    d	 S| j d  } |  j | | j | |  } | | d
 <| j	 s t
 j | | | |  s d	 Sn  t } |  j | d | d j   |  st } n  t
 j | | j d   ys |  j | |  | d j   d k rx|  j | | j |  |  j | | j |  n  |  j | | d j    Wn9 t k
 r}	 |  j  j d  |  j  j t |	   n X| d j   d k r/y |  j | | |  Wq/t k
 r+}	 |  j  j d t |	   q/Xn  y\ | j set
 j | |  }
 |  j | |
 |  } n% g  }
 |  j  j d  |  j |  } WnJ t k
 r}	 |  j  j d  t
 j | |  }
 |  j | |
 |  } n Xx | D] } | j |  qWt |  d k r|  j  j!   n  d | d <d	 S(   sO   Returns the timestamps which has been inserted into the database for the devices   DataDB: {_pid} {_lid} {_did}t   _pidRh   Ri   RC   Rb   i    s,   DataDB: {_did} device has no data to insert.NR&   R:   R3   R   s/   An error occurred in the data point filter codes$   An error occurred in __calc_etoday: s   Overwriting datas   Error in overwrite codet   SUCCESSt   status("   R$   R   R4   t   ost   getpidt   loggerIdR`   RO   t   _DataDB__get_device_infot   ignore_versionR   t   _DataDB__check_device_versionRB   t   _DataDB__add_storage_delay_colR_   RA   t   _DataDB__scale_datat   _DataDB__check_datatype_ranget   _DataDB__filter_acpt   _DataDB__filter_etotalt   _DataDB__filter_generic_valuesR   R   Ra   t   _DataDB__calc_etodayt	   overwritet"   _DataDB__get_date_data_from_devicet   _DataDB__prepare_sql_stringR   t   _DataDB__prepare_sql_overwriteR   RH   R6   R7   (   R,   R8   t	   containerR   Rp   t   last_data_valuesRC   R&   R   R   R   t   sql_query_listt	   sql_query(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt
   __set_data  sj    	
	#				c         C   sN   t  j |  t j  } t  j | t j  } | | k  r: d S| | k rJ d Sd S(   Nii   i    (   R    R{   R   R|   (   t   at   bt   a_dtt   b_dt(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   cmp_dirty_ts5  s    c         C   s`  |  j  j d j d t j     |  j d  k r\|  j j   } | j d j d |  j	   | j d  | j
   d } | |  j	 k r |  j  j d  t  n  i  } i  } g  } x | j D] } x | j D] } | j | j  }	 |	 s"t i | j d 6t   d	 6d  d
 6 }	 |	 | | j <n  t   }
 x! | j D] } | j |
 | j <q5W|
 |	 d	 | j <| j |	 d
 <| j | k r | j | j  q q Wq W| j d t j  g  } y3 |  j |  |  j j   |  j | | j  } Wn# t k
 r} |  j  j d  n X|  j |  i  } xK| D]C} | | k rad | | <|  j  j d j d |   q#n  |  j  j d j d |   i  } | j |  }	 t  |	  d k rq#n  d } xy| |  j! k r+y* |  j" | | |	 | |  |  j! d } Wqt k
 r'} |  j j#   t$ |  j% d  d k r\|  j  j d j d |   d | | j <| d 7} q(t$ |  j% d  d k r|  j  j& d j d |   d | | j <|  j! d } q(|  j  j d t$ t j    d | j d | d t$ t' j(     |  j  j t$ |   d | | <|  j! d } qXqWt  |  d k r#x% |	 d	 j)   D] } | | | <qOWq#q#Wy |  j* | |  Wn t+ k
 r} d | d k r=y |  j, |  |  j j   Wn9 t k
 r} |  j  j d  |  j  j t$ |   n X|  j* | |  q=n- t k
 r<} |  j  j d t$ |   n X| j- s\|  j. | |  q\n  | S(   Ns   DataDB: {_pid} entered set_dataR   s	   USE {_db}RG   s   select db_name()i    s3   Mismatch between current db and routing db. ExitingRC   Rb   R=   t   cmps0   Something went wrong with the hidden device codeR   s8   DataDB: device {_did} is marked as hidden. Going to skipRi   s!   DataDB processing data for {_did}i   t   deadlockis   DataDB: deadlock {_did}t   SERVER_SIDE_ERRORs#   Violation of PRIMARY KEY constraints:   DataDB: duplicated data found and ignored for device: {_d}R   s   DataDB: Exception raised(s   ) t    s   Invalid object names3   ERROR - could not create xxx_dirty_timestamps tables:   An error ocurred while trying to insert dirty timestamps: (/   R$   R   R4   R   R   R6   R'   t   cursorRH   R0   Rk   R   R   t   timestampDatat
   deviceDataR`   t   deviceIdR   t	   valueDataRd   t   idt	   timestampR=   R   t   sortR   R  t!   _DataDB__create_hidden_devs_tableR7   t   _DataDB__get_hidden_devicesR   t   _DataDB__activate_pas_serviceRO   R%   t   _DataDB__set_datat   rollbackRa   t   findR   t	   tracebackt
   format_excR   t   _DataDB__insert_dirty_tsR   t   _DataDB__create_dirty_ts_tablet   bulk_insertt%   _DataDB__update_last_data_for_devices(   R,   R   R8   t   curr_dbt   list_tsstatus_codest   devicest   dirty_tsRb   t   devR   RX   R   t   hidden_devicesR   Rp   RC   R   t	   try_countRe   R   t   err1(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   set_dataB  s    		
		
		
			>
	c         C   s   i  } |  j  |  } | S(   N(   R(  (   R,   R   Rp   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   setData  s    c         C   s  g  } g  } x| D]} | j  |  } | j  d  } | j  d  d k r | j  d  } | r$| j  d  d k r$| j d j d |   | j  d	  }	 t t |	   }
 |	 j  |
  } y3 | j d
 j d |
 d | d | d d d   Wn/ t k
 r#} |  j j d j d |   n X| d } xp | D]h } | | } | j  |  } | d d k r5| r5| j d j d |
 d | d | d | d   Pq5q5W| d } xs | D]h } | | } | j  |  } | d d k r| r| j d j d |
 d | d | d | d   PqqWq$q q Wt |  d k rwd j d d j	 |   } d j d |  } | j
 |  n  t |  d k rd j d d j	 |   } d j d |  } | j
 |  n  |  j j   d  S(   NR&   R   R   Rj   R3   R   s   '{_tbl}'R1   Rb   s5   ('{_ts}', '{_tbl}', {_val}, '{_col}', 'Energy_Yield')R   R   R   R   s/   [{_did}] - Device has no COL_E_TODAY_CALC valueRi   RX   RU   t   196620s:   ('{_ts}', '{_tbl}', {_val}, '{_col}', 'Inverter_Power_AC')RT   t   196626s:   ('{_ts}', '{_tbl}', {_val}, '{_col}', 'Inverter_Power_DC')i    s   ({_tbl})R   s9   DELETE FROM dbo.xxx_Last_Data WHERE Table_Name in {_tbl} s   {_val}sl   INSERT INTO dbo.xxx_Last_Data (Date_and_Time, Table_Name, Value_Value, Value_Type, Value_Name) VALUES {_val}(   R`   R   R4   R   R   t   KeyErrorR$   R   RO   R   RH   R6   R7   (   R,   R8   R"  t   delete_device_tbl_listt   insert_device_tbl_listRC   R   R&   R:   Rb   t   last_added_tst   data_valuesR   RX   RY   Rd   t
   data_valuet   sql_tblR;   t   sql_val(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __update_last_data_for_devices  sl    
	



				c         C   s   d } | j  |  d  S(   Ns   IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.[xxx_Dirty_Timestamps]') and OBJECTPROPERTY(id, N'IsTable') = 1)
                BEGIN
                CREATE TABLE dbo.[xxx_Dirty_Timestamps]
                (
                    data_ts datetime,
                    state nvarchar(10) NOT NULL DEFAULT 'dirty',  
                    modified datetime         
                    CONSTRAINT dirty_timestamps_pk PRIMARY KEY (data_ts)
                )
                END
             (   RH   (   R,   R8   R;   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __create_dirty_ts_table  s    c      
   C   s   d j  d |  } | j |  | j   } | g  k rD | d } n t Sd j  d |  } | j |  d } | d | d | d d | d d f
 } | j | |  t S(	   s?   get device alias, if no alias is found - no need to add column s`    SELECT Virtual_Device_Name FROM xxx_Virtual_Devices
                WHERE TABLE_NAME='{_table}'t   _tablei    s
   IF NOT EXISTS (SELECT * FROM dbo.syscolumns where id = object_id(N'dbo.[{_table}]') 
                                AND name ='Storage_delay')
                BEGIN
                ALTER TABLE  dbo.[{_table}] ADD Storage_delay int
                END
             sv  IF NOT EXISTS (SELECT Table_Name FROM xxx_Virtual_Devices (READUNCOMMITTED) WHERE Table_Name=%s AND Column_Name=%s) 
                        INSERT INTO xxx_Virtual_Devices (Table_Name, Column_Name, Device_Type, Device_Class, Device_Class_2, Virtual_Device_Name, Translation_Key, Station_ID, Unit_String)
                        VALUES(%s, %s, %s, %s, %s, %s, %s, '-1', %s) t   Storage_delayt   DELAYR   (   R4   RH   RI   RA   RB   (   R,   t
   table_namet   dev_categoryR8   R;   RK   t   aliast   params(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __add_storage_delay_col"  s    c         C   s  t  j   } t |  d k r" d S| d } | t |  d } d } | j | | | f  | j   } g  } xY | D]Q }	 |	 d }
 | j |
  |
 j t j  | k ru | j	 |
 j t j   qu qu Wt |  d k rd } x | D] }
 | j | |
  q W|  j
 j   n  t |  d k r)d Sg  } t } xO | D]G } | rjt } | j d j d |   q<| j d j d |   q<Wd	 j d |  } | d
 j |  7} y | j |  |  j
 j   Wn2t k
 rf} d | d k ry |  j |  |  j
 j   Wn9 t k
 rE} |  j j d  |  j j t t   n X| j |  |  j
 j   qn t k
 r} |  j j d  |  j j t |   |  j j |  |  j j d  |  j j |  |  j j |  |  j j |  |  j j |  n Xd S(   s   inserts or updates new dirty timestamps. This function should be called after all data of the setData 
        has been inserted into the database.i    Ni   s   SELECT data_ts AS date FROM xxx_Dirty_Timestamps 
                       WHERE data_ts >= convert(datetime, %s, 21) 
                       AND data_ts <= convert(datetime, %s, 21)sU   UPDATE xxx_Dirty_Timestamps SET state='dirty', modified=GETUTCDATE() WHERE data_ts=%ss7   (convert(datetime, '{_dt}', 21), 'dirty', GETUTCDATE())t   _dts8   ,(convert(datetime, '{_dt}', 21), 'dirty', GETUTCDATE())s   IF NOT EXISTS (SELECT data_ts FROM xxx_Dirty_Timestamps WHERE data_ts=convert(datetime, '{_dt}', 21)) INSERT INTO xxx_Dirty_Timestamps (data_ts, state, modified) VALUESR  s   Invalid object names3   ERROR - could not create xxx_dirty_timestamps tables&   ERROR while inserting dirty timestampsRK   (   R    R   RO   RH   RI   R   R   R   R|   t   removeR6   R7   RB   RA   R4   R   R   R  R   R$   R   Ra   R   (   R,   R8   R#  t   utc_nowt   first_tst   last_tsR  RK   t   dirty_ts_updateRL   t   dateR;   t   sql_listt   is_firstRe   t   sql_manyR   R'  (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __insert_dirty_tsB  sn    

 	c         C   s   d } | j  |  | j   } t |  d k r d } y | j  |  |  j j   Wq t k
 r } |  j j d  |  j j t t	   q Xn  d S(   s   Function activates the PAS service for this plant if not set yet. 
        This should only happen if the titanium is importing the datasL   SELECT * FROM xxx_Plant_Device_Properties WHERE Value_Key='calc_plant_yield'i    sp   INSERT INTO xxx_Plant_Device_Properties (Value_Key, Value) 
                     VALUES('calc_plant_yield', '1')sB   ERROR - could not create plant device property: 'calc_plant_yield'N(
   RH   RI   RO   R6   R7   R   R$   R   Ra   R   (   R,   R8   R;   RK   R   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __activate_pas_service  s    	c         C   s&   d j  d |  j  } | j |  d  S(   NsY  USE {_db};
                 IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.[xxx_Hidden_Devices]') and OBJECTPROPERTY(id, N'IsTable') = 1)
                 BEGIN CREATE TABLE dbo.[xxx_Hidden_Devices] (sdid nvarchar(100), lid nvarchar(100)        
                 CONSTRAINT hidden_devices_pk PRIMARY KEY (sdid, lid) ) ENDRG   (   R4   R0   RH   (   R,   R8   R;   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __create_hidden_devs_table  s    c         C   sQ   g  } d } | j  | | f  | j   } x | D] } | j | d  q2 W| S(   Ns0   SELECT sdid FROM xxx_Hidden_Devices WHERE lid=%si    (   RH   RI   R   (   R,   R8   t   lidt   hidden_devsR;   RK   RL   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __get_hidden_devices  s    c         C   s  |  j  j d  |  j d k r7 |  j | |  |  _ n  | j d  } | |  j j   k r_ d Sd } xC | d d j   D]- \ } } | j d  d k s qz n  | } qz W| s d Sx | j d  D] } | d | j |  }	 |	 d k r q n  t |	  }	 t |  j |  d	 }
 |	 d k	 rW|	 |
 k rW|  j  j	 d
  | d | j
 |  n  |	 d k	 r |	 d k  r |  j  j	 d  | d | j
 |  q q Wd S(   s   This function filters power value which are 1.5 x nominal power of the inverter.
        It also filters values which are below 0 W.
        TODO: Discuss if small negative values should be filtered as well.s   __filter_acp()RC   NR&   RX   RV   R   Rb   g      ?sF   DataDB: Value P_AC_TOTAL exceeds nominal power * 1.5. Discarding valuei    s?   DataDB: Value P_AC_TOTAL has a negative value. Discarding value(   R$   R   R+   R'   t   _DataDB__get_all_nom_acpR`   R   t   itemsR   R   t   pop(   R,   R$  R$   R8   RC   t   pac_vidRY   t   vdictRe   t   pac_valt   ac_limit(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __filter_acp  s6    !
		c         C   s  |  j  j d  | j d  } | |  j j   k r8 d  Sd  } xC | d d j   D]- \ } } | j d  d k rz qS n  | } qS W| s d  SxI| j d  D]8} | d | j |  }	 |	 d  k r q n  y t |	  }	 Wn# t k
 rt t	 |	   }	 n Xt
 t	 |  j |   d	 d
 d d }
 |	 d  k	 rt |	  t |
  k r|  j  j d j d |
 d |	 d | d |   | d | j |  n  |	 d  k	 r |	 d k  r |  j  j d  | d | j |  q q Wd  S(   Ns   __filter_etotal()RC   R&   RX   RV   R]   R
   Rb   i  i   in   id   sz   DataDB: Value E_TOTAL exceeds the limit {_etotal_limit}. Discarding value {_val} for device {_dev_id}, value id: {_val_id}t   _etotal_limitR   t   _dev_idt   _val_idi    s<   DataDB: Value E_TOTAL has a negative value. Discarding value(   R]   R
   (   R$   R   R`   R+   R   R'   RO  R   R   R   R    R   R4   RP  (   R,   R$  R$   R8   RC   t
   etotal_vidRY   RR  Re   t
   etotal_valt   etotal_limit(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __filter_etotal  s<    !
)$
	c   
   
   C   sJ  |  j  j d  | d k r% d } n  | t j j   k r> d  Sx| j d  D]} x| d | j   D]\ } } | | d d j   k ri | d d | j d  } | d  k r qi q n qi | } | d k r| j d	  d
 k r| d j	   r| d  } n  | j
 d  } n  | d k r<d | k r<d } q<n  t j | j |  d  k r^qi n* t j | | d } t j | | d }	 | d k rqi n  t |  } | | k r|  j  j d j d | d | d |   | d | j |  qi | |	 k  ri |  j  j d j d | d |	 d |   | d | j |  qi qi WqN Wd  S(   Ns   __filter_generic_values()t   SENSORR   Rb   R&   RX   RV   R   t   _i   ii   t   _KWHR   R   R   R   R^   se   DataDB: Measurement {val} exceeds the allowed maximum {max_limit} with {data_point}. Discarding valuet   valt	   max_limitt
   data_pointsf   DataDB: Measurement {val} is below the allowed minimum {min_limit} with {data_point}. Discarding valuet	   min_limit(   R$   R   R   t   correction_mapR   R`   RO  R'   t   countt   isdigitt   rstripR   R   R4   RP  (
   R,   t   dev_datat   dtypeRe   R`  Rb  t   val_vidt   cropped_valRa  Rc  (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __filter_generic_values  sL    	!		%

c         C   s   d j  d |  j d |  } | j |  | j   } i  } xx | D]p } | d d k	 rA | d j | d  d } | j d d  } | j d	 d
  } | j i | d | 6 qA qA W| S(   s9    get installed ac_p for all inverters of the given loggers  use {db};
                SELECT ISNULL(Fix_Value, Maximum_Value), Table_Name 
                FROM xxx_Virtual_Devices
                WHERE Device_Class='P_AC' 
                AND Device_Type='INVERTER' 
                AND Table_Name like '%[_]{logger}[_]%'RJ   R$   i    i   R^  t   dasht   -t   slasht   /N(   R4   R0   RH   RI   R'   t   splitR   t   update(   R,   R$   R8   R;   RK   t   dev_acpRL   t   dev_id(    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __get_all_nom_acpK  s    	c   
   
   C   s  x| j  d  D]} x| d | j   D]\ } } | j  d  j  d  d  k rY d  S| d d j  |  d  k r| q+ n  | d d | d } | d  k s+ d | k r q+ n  d | k rt |  d k r|  j j d j d	 | d
 | d |   | d | j |  d  S| d k r$d } n  t j	 j  |  } | d  k rFd  Sy | } t
 |  } WnQ t k
 r}	 |  j j d j d	 | d
 | d |   | d | j |  d  SXt | d  t k r| d d | k o| d d k n s*| d d | k o| d d k n s*| d k o=t |  | d k s|  j j d j d	 | d
 | d |   | d | j |  qq+ | d | k o| d k n s+ |  j j d j d	 | d
 | d |   | d | j |  q+ q+ Wq Wd  S(   NRb   R&   RX   RW   R^   s   nvarchar(200)i   sZ   DataDB: Measurement {vid} of device {dev_id} is out of range, value {dp}. Discarding valueRY   t   dpRt  t   integerR    s[   DataDB: Measurement {vid} of device {dev_id} is out of range, value: {dp}. Discarding valueR   i    i   R   R   (   R`   RO  R'   RO   R$   R   R4   RP  R   t   datatype_filterR   R   R3   R   (
   R,   Rt  Rh  Re   t   val_idRb  t   datatypet   dt_ranget   str_dpR   (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   __check_datatype_ranged  sV    !
	
,,
$
l            l    i@KL i@ iiigi'  i I       i   I       il            l         l            l    N(*   t   __name__t
   __module__R|   Rd  Rx  R"   Rl   t   staticmethodR   Rm   R   Rn   R   R   R   R   R   R   R   R   R   R'   RB   R   R   R   R  R  R(  R)  R  R  R   R  R  R  R  R   R   R   RN  R   (    (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyR      s   			.				y		5	4TF	L			H		 	R					(	.	6	(   t   pymssqlt   timeR   Rs   R    R   R  t   collectionsR   t   src.settingsR#   t   src.containers.DataContainerR   t   src.dbconnectors.dbR   R   R   (    (    (    s.   /var/www/titaniumci/src/dbconnectors/DataDB.pyt   <module>   s   