
    i&5jJ(                         d dl Z d dlmZmZmZ d dlmZ d dlmZ d dlm	Z	 dZ
dZdZ e j                  e      Zd	 Zd
 Zd Zd Zd Zd Zd Zd Zy)    N)datetime	timedeltatimezone)text)get_async_db)defaultdictx   z   ){   |   }   c                     | sg S | j                  d      D cg c],  }|j                         st        |j                               . c}S c c}w )N,)splitstripint)	id_stringxs     </var/www/html/ls-ibe-bot-apis/app/utils/daily_data_insert.py	parse_idsr   ;   s?    	$-OOC$8F$8qAGGIC	N$8FFFs
   AAc                      | y t        | d      S )N   )round)vals    r   
safe_floatr   @   s    
{a=    c                     | j                         | j                  | j                         d   | j                         dk\  rddS ddS )N      r   )Day_of_WeekMonthWeek_of_Year
Is_Weekend)weekdaymonthisocalendar)date_objs    r   compute_time_featuresr(   E   sR    '') ,,.q1#++-2a	  9:	 r   c                    K   t        d      }| j                  |||t        t        d       d {   }|j	                         S 7 w)Na  
        SELECT 
            date,
            sub_hotel_id,
            total_active_rooms,
            total_rented
        FROM hotel_future_occupancies
        WHERE date BETWEEN :start_date AND :end_date
        AND hotel_id = :hotel_id
        AND sub_hotel_id IN :sub_hotels
    )
start_dateend_datehotel_id
sub_hotelsr   executeHOTEL_ID
SUB_HOTELSfetchall)dbr*   r+   queryresults        r   fetch_30_days_datar6   M   sQ      
 
	E ::e  	&  F ??s   .AAAc                    K   t        d      }| j                  |t        t        d       d {   }|j	                         S 7 w)Nz
        SELECT product_id, sub_hotel_id, type_id AS room_type_id
        FROM products
        WHERE hotel_id = :hotel_id
        AND sub_hotel_id IN :sub_hotels
    )r,   r-   r.   )r3   r4   r5   s      r   fetch_productsr8   c   sK       	E ::e &  F
 ??s   ,AAAc                    K   t        d      }| j                  ||t        t        d       d {   }|j	                         S 7 w)Nz
        SELECT date, sub_hotel_id, room_type_id, room_type_price
        FROM hotel_price_122
        WHERE date = :target_date
        AND rate_plan_id = :rate_plan_id
        AND channel_id = 1
        AND sub_hotel_id IN :sub_hotels
    )target_daterate_plan_idr-   )r   r/   RATE_PLAN_IDr1   r2   )r3   r:   r4   r5   s       r   fetch_pricesr=   r   sN       	E ::e"$ &  F ??s   -AAAc                    K   |sy t        d      }| j                  ||       d {    | j                          d {    y 7 7 w)NaZ  
        INSERT INTO daily_room_type_features (
            date,
            sub_hotel_id,
            room_type_id,
            rate_plan_id,
            room_type_price,
            total_inventory,
            booked_inventory,
            Occupancy_Rate,
            Day_of_Week,
            Month,
            Week_of_Year,
            Is_Weekend,
            Bookings_Last_1_Day,
            Bookings_Last_3_Days,
            Bookings_Last_7_Days,
            Bookings_Last_14_Days,
            Occupancy_Lag_1,
            Occupancy_Lag_7,
            Occupancy_Lag_14,
            Occupancy_Lag_30
        )
        VALUES (
            :date,
            :sub_hotel_id,
            :room_type_id,
            :rate_plan_id,
            :room_type_price,
            :total_inventory,
            :booked_inventory,
            :Occupancy_Rate,
            :Day_of_Week,
            :Month,
            :Week_of_Year,
            :Is_Weekend,
            :Bookings_Last_1_Day,
            :Bookings_Last_3_Days,
            :Bookings_Last_7_Days,
            :Bookings_Last_14_Days,
            :Occupancy_Lag_1,
            :Occupancy_Lag_7,
            :Occupancy_Lag_14,
            :Occupancy_Lag_30
        )
        ON DUPLICATE KEY UPDATE
            room_type_price = VALUES(room_type_price),
            total_inventory = VALUES(total_inventory),
            booked_inventory = VALUES(booked_inventory),
            Occupancy_Rate = VALUES(Occupancy_Rate),
            Day_of_Week = VALUES(Day_of_Week),
            Month = VALUES(Month),
            Week_of_Year = VALUES(Week_of_Year),
            Is_Weekend = VALUES(Is_Weekend),
            Bookings_Last_1_Day = VALUES(Bookings_Last_1_Day),
            Bookings_Last_3_Days = VALUES(Bookings_Last_3_Days),
            Bookings_Last_7_Days = VALUES(Bookings_Last_7_Days),
            Bookings_Last_14_Days = VALUES(Bookings_Last_14_Days),
            Occupancy_Lag_1 = VALUES(Occupancy_Lag_1),
            Occupancy_Lag_7 = VALUES(Occupancy_Lag_7),
            Occupancy_Lag_14 = VALUES(Occupancy_Lag_14),
            Occupancy_Lag_30 = VALUES(Occupancy_Lag_30)
    )r   r/   commit)r3   recordsr4   s      r   insert_daily_featuresrA      sJ      > >	E@ **UG
$$$
))+ %s   $AAAAAAc                   !"#$%K   	 t        j                  t        j                        t	        |       z
  j                         %%t	        d      z
  }t               4 d {   }t        ||%       d {   }t        |       d {   }t        |%       d {   }|D ci c]  }|j                  |j                   }}t        d       !|D ]  }t        t        |j                              }	t        t        |j                               }
|	D ]T  }|j#                  |      }|s|j
                  |j$                  |f}!|   dxx   dz  cc<   ||
v sE!|   dxx   dz  cc<   V  !fd"|D ci c]%  }|j$                  |j                  f|j&                  ' }}g }!j)                         D ]+  \  \  }$#}|%k7  r|d   }|d   }|r||z  nd	}"#$%fd
} |d      } |d      } |d      } |d      } "%t	        d      z
  $#      \  }}} "%t	        d      z
  $#      \  }}} "%t	        d      z
  $#      \  }}} "%t	        d      z
  $#      \  }}}%$#t*        |j#                  $#f      ||t-        |      dt/        %      ||||t-        |      t-        |      t-        |      t-        |      d}|j1                  |       . t3        ||       d {    t4        j7                  d%        	 d d d       d {    y 7 7 7 7 c c}w c c}w 7 D7 # 1 d {  7  sw Y   y xY w# t8        $ r%} t4        j;                  d|  d%        Y d } ~ y d } ~ ww xY ww)N)tzdays   c                      dddS )Nr   )totalbooked rJ   r   r   <lambda>z!save_daily_data.<locals>.<lambda>   s    1r   rH   r   rI   c                 r    j                  | ||f      }|r|d   dk(  ry|d   }|d   }|r||z  nd}|||fS )NrH   r   )r   r   r   rI   )get)date	sub_hotel	room_typedatarH   rI   occdaily_metricss          r   get_metricsz$save_daily_data.<locals>.get_metrics   sW    $(($	9)EFtG}1"Wh(-fun1fc))r   r   c                 t    d}t        d| dz         D ]"  } t        |      z
        \  }}}||z  }$ |S )Nr   r   rD   )ranger   )	rE   total_bookingsd_brT   room_type_idsub_hotel_idr:   s	        r   sum_bookingsz%save_daily_data.<locals>.sum_bookings  sT    %&N"1dQh/"-')*;;((#1a
 '!+ 0 *)r            )rN   r\   r[   r;   room_type_pricetotal_inventorybooked_inventoryOccupancy_Rate)Bookings_Last_1_DayBookings_Last_3_DaysBookings_Last_7_DaysBookings_Last_14_DaysOccupancy_Lag_1Occupancy_Lag_7Occupancy_Lag_14Occupancy_Lag_30z"Data saved successfully for date: zError: z for date: )r   nowr   utcr   rN   r   r6   r8   r=   
product_idr[   r   setr   total_active_roomstotal_rentedrM   r\   ra   itemsr<   r   r(   appendrA   logwarning	Exceptionerror)&ir*   r3   occ_rowsproduct_rows
price_rowspproduct_maprowactiverentedpidrP   key	price_mapoutputrN   rQ   rH   rI   occ_rater]   
bookings_1
bookings_3
bookings_7bookings_14rY   lag_1lag_7lag_14lag_30recorderS   rT   r[   r\   r:   s&                                    @@@@@r   save_daily_datar      s    x||x||4ya7HHNNP 9"#55
>>R/JLLH!/!33L+B<<J BNNA1<<7KN ( ) M
  Ys'='=>?Ys'7'789!C + 4I$ 88S%5%5yAC!#&w/14/f}%c*8494 "	   * $#A 0!2C2CC#  
 F<I<O<O<Q82|\D;&Wh-26E>	* *!_
)!_
)!_
*2. *+	q8I*I<Yef1e)+	q8I*I<Yef1e*;9K+K\[gh1f*;9K+K\[gh1f ($0$0$0'0}}lL5Q'R',(.&0&: ,K8 ,6,6,6-8'1%'8'1%'8(26(:(26(:+0 f%o =R| (F333KK<[MJKc ">>L3< ODJ 4[ ">>>d  		GA3k+78s   NAM %L&&M )M :L);M L,M !L/"M *L2BM M ;*L7%EM 7L<8M M  L>!M %N&M )M ,M /M 2M >M  MM	MM NM 	NM>9N>NN)loggingr   r   r   
sqlalchemyr   app.models.connectionr   collectionsr   r<   r0   r1   	getLogger__name__ru   r   r   r(   r6   r8   r=   rA   r   rJ   r   r   <module>r      se     2 2  . #
g!`G

,$ENyr   