a
    h                  	   @   s  d dl mZmZmZmZmZmZ d dlmZm	Z	 d dl
m
Z
mZ d dlmZ d dlmZmZmZ d dlmZmZ d dlmZmZmZmZmZ d dlmZmZmZmZ d d	l m!Z! d d
l"m#Z# d dl$m%Z% d dl&m'Z' d dl(Z(d dl)Z)d dl*Z+d dl,Z,d dl-m.Z. d dl/m0Z0 d dl1Z2d dl mZ d dlmZm	Z	 d dlm3Z3 ej4j5j6ej7d e Z8e89e%j: e8j;e#dgddgdgd e(<dZ=e(<dZ>dd Z?e8@dee?feAeAeAeAeAedddZBe8Cdeded ed edee?feAeAeAeAe	eeA  ed!d"d#ZDe8Cd$d%d& ZEe8Cd'eded ed ededee?feAeAeAe	eeA  e	eeA  ed(d)d*ZFe8Cd+ee?fed,d-d.ZGe8Cd/ed ee?feAed0d1d2ZHd\d4d5ZId]d6d7ZJd^d8d9ZKd:d; ZLd_d<d=ZMd`d?d@ZNdAdB ZOe8CdCed ee?feAeAeAeAeeA edDdEdFZPe8CdGdHdI ZQee+jRdJdKdLZSe8@dMed ee?feedNdOdPZTdQdR ZUe8@dSed ee?feedNdTdUZVe8CdVee?fed,dWdXZWe8CdYee?fed,dZd[ZXdS )a    )FastAPIDependsQuery
UploadFileFileHTTPException)ListOptional)datetime	timedelta)and_)Sessionsessionmakerrelationship)modelsdatabase)fetch_kpi_dataparse_dotnet_dategenerate_date_rangefind_continuous_rangesstore_kpi_response)
InstrumentKPIKPIValueMarketExchnges)SessionLocal)CORSMiddleware)admin_routes)	KPIFilterN)IntegrityError)JSONResponse)r   )func)bind*T)Zallow_originsZallow_credentialsZallow_methodsZallow_headersZDATASTREAM_API_USERNAMEZDATASTREAM_API_PASSWORDc                  c   s*   t  } z| V  W |   n
|   0 d S N)r   r   closedb r(   (/var/www/html/stock_analysis/app/main.pyget_db+   s    r*   z/fetch-kpis/)token	frequencysymbol
start_dateend_dater'   c              
   C   s  dd | tj D }| ddidd |D |||dd|ddid	d
dgd}t| |}td t| |d d }	dd |	d D }
|	d d d d d }| tjj|d }|stj|d}|	| |
  || |	d D ]}|d }|d d d }|d d d}| tjj|d }|sdtj|d}|	| |
  || t|
|D ]d\}}|d u rqn| tjj|j|j||d }|sntj|j|j||||d}|	| qnq|
  ddiS )Nc                 S   s   g | ]
}|j qS r(   code.0kr(   r(   r)   
<listcomp>5       zfetch_kpis.<locals>.<listcomp>RequestAllMetadataTc                 S   s   g | ]}|d didqS 
ReturnNameTValue
Propertiesr(   r3   r1   r(   r(   r)   r5   ;   r6      ZStartZEndZ	FrequencyZKindr9   r:    Z	DataTypesDater   ZTagZ
TokenValuer<   ZDataRequestsz<=============== Response from DataStream API ===============ZDataResponsesr   c                 S   s   g | ]}t |qS r(   )r   r3   dr(   r(   r)   r5   O   r6   ZDatesZDataTypeValuesZSymbolValuesZSymbolr-   ZDataTyper;   ZCurrencyr0   )instrument_idkpi_idr,   date)rG   rH   r,   rI   valuecurrencymessagezKPI data fetched and stored.)queryr   r   allr   printr   	filter_byfirstaddcommitrefreshgetzipr   id)r+   r,   r-   r.   r/   r'   	kpi_codesbodyresponsedatadatesZinstrument_symbol
instrumententrykpi_codevaluesrK   kpirI   rJ   existingZkpi_valr(   r(   r)   
fetch_kpis2   sv    






rc   z/kpis/Q.)r-   r,   r.   r/   kpisr'   c              	      s  t d}t|d }t|d }|rdd  t D td fdd|D }	td|	 |	D ],}
 tj	|
d	
 sp t|
|
d
 qp   ndd  t D }	 tj	| d
 }|st| d} |     |  fdd|	D } tjtj|jktj|ktj|tj|ktj|ktjttjt|k }dd |D td t|||}td| tfdd|D }td| |r@t||}td| |D ]`\}}|ddidd |	D | | |dd| ddidddgd}t||}t  | qi }|	D ]}
 tj	|
d	
 }|slqH ttj|jktj|jktj|ktj|ktj|k!tj }dd |D ||j"p|j#< qH| |t$|t$||d }|S )!NDATASTREAM_API_TOKEN%Y-%m-%dc                 S   s   i | ]}|j p|j|jqS r(   namer1   r2   r(   r(   r)   
<dictcomp>   r6   zget_kpis.<locals>.<dictcomp>Name to Code Mapping:c                    s   g | ]}  ||qS r(   rU   r3   ri   name_code_mapr(   r)   r5      r6   zget_kpis.<locals>.<listcomp>KPI Codes to Fetch:r0   r1   ri   c                 S   s   g | ]
}|j qS r(   r0   r2   r(   r(   r)   r5      r6   rF   c                    s@   g | ]8}  tjj|d  dur  tjj|d  qS r0   NrM   r   rW   rP   Zscalarr=   r&   r(   r)   r5      s   c                 S   s   h | ]}|d  qS r   r(   rD   r(   r(   r)   	<setcomp>   r6   zget_kpis.<locals>.<setcomp>Existing Dates in DB:Full Date Range:c                    s   g | ]}| vr|qS r(   r(   rD   existing_datesr(   r)   r5      r6   Missing Dates:Chunks:r7   Tc                 S   s   g | ]}|d didqS r8   r(   r=   r(   r(   r)   r5      r6   r>   r?   r9   r:   r@   rA   rC   c                 S   s"   g | ]}t |j|j|jd qS )rI   rJ   rK   strrI   rJ   rK   r3   rr(   r(   r)   r5      s
   )r-   r,   r.   r/   re   )%osgetenvr
   strptimerI   rM   r   rN   rO   rP   rQ   rR   rS   r   rT   r   filterrG   rW   r,   rH   in_group_byhavingr!   countlenr   sortedr   	isoformatr   r   order_byri   r1   r~   )r-   r,   r.   r/   re   r'   r+   startendrX   r1   r]   kpi_idsdb_dates
full_rangemissing_dateschunkschunk_start	chunk_endrY   rZ   kpi_datara   recordsr(   r'   ry   ro   r)   get_kpis   s    	


















z
r   z/stocks_details/c                  C   sB   t  } | t }g }|D ]}||j|jd q|   |S )Nr-   ri   )r   rM   r   rN   appendr-   ri   r%   )r'   instrumentsresultinstr(   r(   r)   
get_stockse  s    
r   z/stock_performance_calculation/)r,   r.   r/   re   symbolsr'   c           4         sj  t d}t|d }t|d }dd |D }|rdd  t D td fdd|D }	td	|	 |	D ],}
 tj	|
d

 s~ t|
|
d q~   ndd  t D }	|r ttj| }n t }dd |D }i }|rH|D ]*} tj	|d
 }||vrft|d} |     |  fdd|	D } tjtj|jktj| ktj|tj|ktj|ktjttjt|k }dd |D td t| ||}td| tfdd|D }td| |rt|| }td| |D ]`\}}|ddidd |	D | | | dd|ddidddgd }t ||}t! | qBi }|	D ]}
 tj	|
d

 }|sАq ttj|jktj|jktj| ktj|ktj|k"tj }d!d |D ||j#p0|j$< q|%||i qi }td"| |D ]B} tj	|d
 }t&j't(t)d#}td$| |j*j+}|j*j,}td%| td&| |j-|d'g||d(} | . } | j/rt0d)| j1d* }!| j1d+ }"|"|! |! d, }#td-|!d.d/|"d.d0|#d.d1 g }$td2|  |j-|d3g||d(}%|%. }%|%j/rx|$2|d d d d d4 td5 |%j1d* }&|%j1d+ }'|'|& |& d, }(|(|# })|t3|&d6t3|'d6t3|(d6|t3|!d6t3|"d6t3|#d6t3|)d6d7	}*|$2|* td8|&d.d/|'d.d0|(d.d9|)d.d:	 t45|$}+t46d;d  t46d<d  t46d=d  t46d>d  td? |+7d@}+|| t8|t8||+j9dAdBdC},t|+ |%||,i qZg }-|: D ]\}}.|-;|.dD  qt45|-}+|+dE j<dFdGdH=dI|+dJ< |+> D ]T\}/}0|0dK }1|: D ]8\}2}.|.dD d@ dK |1krt?|0dJ |.dD d@ dJ< qqtdL|+ ||dM}3t|3 |3S )NNrf   rg   c                 S   s$   g | ]}| d D ]}| qqS ),)splitstrip)r3   itemsr(   r(   r)   r5     r6   z5get_stock_performance_calculation.<locals>.<listcomp>c                 S   s   i | ]}|j p|j|jqS r(   rh   r2   r(   r(   r)   rj     r6   z5get_stock_performance_calculation.<locals>.<dictcomp>rk   c                    s   g | ]}  ||qS r(   rl   rm   rn   r(   r)   r5     r6   rp   r0   rq   c                 S   s   g | ]
}|j qS r(   r0   r2   r(   r(   r)   r5     r6   c                 S   s   h | ]
}|j qS r(   rF   )r3   r   r(   r(   r)   ru     r6   z4get_stock_performance_calculation.<locals>.<setcomp>rF   c                    s@   g | ]8}  tjj|d  dur  tjj|d  qS rr   rs   r=   r&   r(   r)   r5     s   c                 S   s   h | ]}|d  qS rt   r(   rD   r(   r(   r)   ru     r6   rv   rw   c                    s   g | ]}| vr|qS r(   r(   rD   rx   r(   r)   r5     r6   rz   r{   r7   Tc                 S   s   g | ]}|d didqS r8   r(   r=   r(   r(   r)   r5     r6   r>   r?   r9   r:   r@   rA   rC   c                 S   s"   g | ]}t |j|j|jd qS r|   r}   r   r(   r(   r)   r5     s
   r   )usernamepasswordzProcessing symbol:r.   r/   ZPI)Z	date_fromZdate_tozNo index data found!)r   r   )r   d   z
ATX Index: z.2fu    → u    → Return: z%
zProcessing: P)TickerStart Price	End PriceReturn %zExcess vs ATX %u     → No data   )	r   r   r   r   z
Index NamezIndex Startz	Index EndzIndex Return %Total Score %z  u   % → Excess vs ATX: %zdisplay.max_columnszdisplay.max_rowszdisplay.widthzdisplay.max_colwidthz
--- All Stocks Details ---r   r   )Zorient)r-   r,   r.   r/   stock_with_performancer   r   FZdense)Z	ascendingmethodZInt64ZRankr   perf_df)Zstock_performance_datastock_wise_kpi_data)@r   r   r
   r   rI   rM   r   rN   rO   rP   rQ   rR   rS   r   r   r-   r   rT   r   rG   rW   r,   rH   r   r   r!   r   r   r   r   r   r   r   r   r   ri   r1   updatedsZ
Datastreamr   r   market_exchange
index_code
index_nameZfetchZdropnaempty
ValueErrorZilocr   roundpd	DataFrameZ
set_optionZfillnar~   Zto_dictitemsextendZrankZastypeiterrowsint)4r,   r.   r/   re   r   r'   r+   r   r   rX   r1   r   Zexisting_symbolsr   r-   r]   r   r   r   r   r   r   r   rY   rZ   r   ra   r   Zresponse_dataZDSZindex_symbolr   Zdf_indexZindex_startZ	index_endZindex_returnZperformanceZdf_stockZstart_priceZ	end_priceZstock_returnZexcess_returnZall_datar   Zcalculation_responserowsdetails_rowtickerZsymZnew_responser(   r   r)   !get_stock_performance_calculations  sF   




























"

'
$


	
 

r   z/kpi-options/r&   c                 C   s   |  t }dd |D S )Nc                 S   s    g | ]}|j |jp|j d qS )rq   rq   r2   r(   r(   r)   r5     r6   z#get_kpi_options.<locals>.<listcomp>)rM   r   rN   )r'   re   r(   r(   r)   get_kpi_options  s    r   z/symbol-search/)rM   r'   c                 C   s8   | ttjd|  dd }dd |D S )Nr   
   c                 S   s   g | ]}|j |jd qS )r   r   r3   ir(   r(   r)   r5     r6   z!symbol_search.<locals>.<listcomp>)rM   r   r   r-   ZilikelimitrN   )rM   r'   resultsr(   r(   r)   symbol_search  s    
r   positivec                 C   s   dd | D }t ||k rdS |dkr4t||||S |dkrFt||S |dkr\t||||S |dkrpt|||S |dkrt|||S dS )	Nc                 S   s   g | ]}|j d ur|j qS r$   )rJ   )r3   vr(   r(   r)   r5     r6   z)check_trend_condition.<locals>.<listcomp>FZconsecutive_growthZnegative_to_positiveZ
yoy_growthZpost_transition_growthZabsolute_threshold)r   check_consecutive_growthcheck_negative_to_positivecheck_yoy_growthcheck_post_transition_growthcheck_absolute_threshold)r`   
trend_typequarters	threshold	direction	data_listr(   r(   r)   check_trend_condition  s    
r   c                    s8   |dkrt  fdd| D S t  fdd| D S d S )Nr   c                 3   s   | ]}|d uo| kV  qd S r$   r(   r3   rJ   r   r(   r)   	<genexpr>  r6   z+check_absolute_threshold.<locals>.<genexpr>c                 3   s   | ]}|d uo| kV  qd S r$   r(   r   r   r(   r)   r     r6   )any)r   r   r   r(   r   r)   r     s    r   c                    sx   t | k rdS dd t| | dd  D |dkrBfdd nfdd t fd	d
tt  d D S )NFc                 S   s<   g | ]4\}}|d ur|d ur|dkr|| t | d qS )Nr   r   )abs)r3   prevcurrr(   r(   r)   r5     s   z,check_consecutive_growth.<locals>.<listcomp>r>   negativec                    s
   |   kS r$   r(   gr   r(   r)   <lambda>  r6   z*check_consecutive_growth.<locals>.<lambda>c                    s   |  kS r$   r(   r   r   r(   r)   r     r6   c                 3   s4   | ],}t  fd d|| d  D V  qdS )c                 3   s   | ]} |V  qd S r$   r(   )r3   r   )comparer(   r)   r     r6   z5check_consecutive_growth.<locals>.<genexpr>.<genexpr>r>   N)rN   r   )r   growth_listr   r(   r)   r     s   z+check_consecutive_growth.<locals>.<genexpr>r   )r   rV   r   range)r   r   r   r   r(   )r   r   r   r   r)   r     s    r   c                 C   s   t | |k rdS tt | | d D ]V}| |||  }|d }tdd |d | D r$tdd ||d  D r$ dS q$dS )NFr>   r   c                 s   s   | ]}|d uo|dk V  qd S Nr   r(   r3   xr(   r(   r)   r     r6   z-check_negative_to_positive.<locals>.<genexpr>c                 s   s   | ]}|d uo|dkV  qd S r   r(   r   r(   r(   r)   r     s   T)r   r   r   rN   )r   r   r   ZsegmentZhalfr(   r(   r)   r     s    "
r   c                 C   s   t | |d k rdS d}tdt | D ]}| | d ur&| |d  d ur&| |d  dkr&| | | |d   t| |d   d }|dkr|| kr|d7 }n|dkr||kr|d7 }nd}||kr& dS q&dS )N   Fr   r   r   r>   T)r   r   r   )r   r   r   r   r   r   growthr(   r(   r)   r     s    ,(

r   r   c                 C   s   t t| | d D ]}| | d ur| | dk r| |d  d ur| |d  dkrd}t |d |d | D ]p}| | d u s| |d  d u s| |d  dkrd} q| | | |d   t| |d   d }||k rjd} qqj|r dS qdS )Nr>   r   TFr   )r   r   r   )r   Zquarters_afterr   r   Z	growth_okjr   r(   r(   r)   r     s    8,(r   c                 C   s   |  tjj|d }|s dS |  tjtjj|ktjj|j	ktjj
|ktjj|ktjj|ktjj  }|rt|dk rdS |d}	t|dd}
t|dd}|dd	}t||	|
||}|rt|d
d d}d|fS dS )z&Apply trend logic for advanced filtersr0   )FNr   trendr   r   r   r   r   c                 S   s   | j S r$   )rI   )r   r(   r(   r)   r     r6   z'apply_advanced_filter.<locals>.<lambda>)keyT)rM   r   r   rP   rQ   r   r   rG   rH   rW   r,   rI   r   ZascrN   r   rU   r   floatr   max)r'   rG   r_   Zfilter_datar,   r.   r/   ra   r`   r   r   r   r   passedZlatest_valuer(   r(   r)   apply_advanced_filter  s.    





r   z/filter-stocks/)r,   r.   r/   logical_operatorfiltersr'   c              
   C   sn  dd |D }t d| |tj }g }|D ](}	g }
d}|D ]}|d }d|v rt||	j||| ||\}}|r|d7 }|
||jt	|j
d qB|d	 }t|d
 }|tjj|d }|sqB|tjtjj|	jktjj|jktjj| ktjj
|ktjj
|k}|dkr0|tjj|k}n|dkrN|tjj|k}nx|dkrl|tjj|k }nZ|dkr|tjj|k}n<|dkr|tjj|k}n|dkrB|tjj|k}nqB|tjj
  }|rB|d7 }|
||jt	|j
d qB| dkr4|t|kr4||	j|
d q0| dkr0|dkr0||	j|
d q0t d| d|iS )Nc                 S   s   g | ]}t |qS r(   )jsonloads)r3   fr(   r(   r)   r5   (  r6   z%filter_stocks_get.<locals>.<listcomp>zlogical_operator ===>r   r_   r   r>   )r_   rJ   rI   operatorrJ   r0   gtZgteltZlteeqneAND)r-   Z
kpi_valuesORmatching_resultsr   )rO   rM   r   r   rN   r   rW   r   rJ   r~   rI   r   r   rP   rQ   r   r   rG   rH   r,   r   Zdescupperr   r-   )r,   r.   r/   r   r   r'   Zparsed_filtersr   r  r]   Zinstrument_kpi_dataZfilter_pass_countr   r_   r   Zkpi_value_objr   rJ   ra   Zkpi_values_queryr(   r(   r)   filter_stocks_get  s    







	








r  z/routesc                   C   s   dd t jD S )Nc                 S   s   g | ]
}|j qS r(   )path)r3   router(   r(   r)   r5   y  r6   zlist_routes.<locals>.<listcomp>)appZroutesr(   r(   r(   r)   list_routesw  s    r
  )uploaded_filereturnc              
   C   sZ   | j  }ztt|}W n6 tyT } ztdd| dW Y d }~n
d }~0 0 |S )N  zCould not read Excel file: status_codeZdetail)filereadr   Z
read_excelioBytesIO	Exceptionr   )r  contentsdfer(   r(   r)   read_excel_to_df|  s    
(r  z/upload/market-exchanges)r  r'   c              
      s  t | }h d}|t|jj svdd |jD  |t   }|r\tdd| d|j fdd|D d}d	}d	}|	 D ]\}}t|
d
 }	|	r|	 dkrq|tj|	d }
|
rt|
d|
j|
_t|
d|
j|
_t|
d|
j|
_|d7 }qt|	t|
ddt|
ddt|
ddd}
||
 z|  |d7 }W q ty   |  Y qY q0 q|  d||dS )ze
    Expect Excel with columns (at least):
    index_code, index_name, echange_name, market_name
    >   r   market_nameechange_namer   c                 S   s   i | ]}|  |qS r(   lowerr3   cr(   r(   r)   rj     r6   z+upload_market_exchanges.<locals>.<dictcomp>r  zMissing columns: r  c                    s   i | ]} | |qS r(   r(   r2   Z	lower_mapr(   r)   rj     r6   columnsr   r   nanr   r   r  r  r>   r@   r   r   r  r  ok)statusinsertedupdated)r  issubsetsetr!  r~   r  keysr   renamer   rU   r   rM   r   rP   rQ   r   r  r  rR   flushr   rollbackrS   )r  r'   r  expectedmissingr'  r(  r   r   r   mxr(   r  r)   upload_market_exchanges  sF    

r2  c                 C   s,   | d u rd S t |  }| dv r(d S |S )N>   r@   noner"  )r~   r   r  )rJ   r   r(   r(   r)   safe_str  s    r4  z/upload/instrumentsc              	      s@  t | }h d}dd |jD  |t   }|rFtdd| d|j fdd|D d}d	\}}}| D ]\}}	t|	d
}
t|	d}t|	d}t|	d}t|	d}t|	d}|
r|s|d7 }qr|	t
j|
d }|s t
|
dddd}|| |  |	ttj|k}|rX||	ttj|k}|rz||	ttj|k}| }|r|j|_|r||_|r||_|r||_|r||_|d7 }qrt|j|||||d}|| z|  |d7 }W qr ty&   |  |d7 }Y qrY qr0 qr|  d|||dS )z
    Expect Excel with columns (at least):
    market_index_code (this maps to market_exchanges.index_code), short_code, symbol, ric, ticker, name
    >   ricr   r-   market_index_coderi   
short_codec                 S   s   i | ]}|  |qS r(   r  r  r(   r(   r)   rj     r6   z&upload_instruments.<locals>.<dictcomp>r  z$Missing columns (case-insensitive): r  c                    s   i | ]} | |qS r(   r(   r2   r  r(   r)   rj     r6   r   )r   r   r   r6  r-   r7  r5  r   ri   r>   r#  r@   r$  )market_exchange_idr7  r-   r5  r   ri   r%  )r&  r'  r(  skipped)r  r!  r*  r+  r   r,  r   r4  rU   rM   r   rP   rQ   rR   r-  r   r   r-   unionr5  r   rW   r8  r7  ri   r   r.  rS   )r  r'   r  r/  r0  r'  r9  r(  r   r   r6  r-   r7  r5  r   ri   r1  rM   r   r(   r  r)   upload_instruments  sl    



r;  z/market-exchangesc              	   C   sX   |  t }td| g }|D ].}td| ||j|j|j|j|j	d q t
|S )Nr   r   )rW   r   r   r  r  )rM   r   rN   rO   r   rW   r   r   r  r  r    r'   r   payloadr   r(   r(   r)   list_market_exchanges  s    


r>  z/instrumentsc                 C   sf   |  t }g }|D ]F}||j|jr.|jnd |jr>|jjnd |j|j	|j
|j|jd qt|S )N)rW   r8  r6  r7  r-   r5  r   ri   )rM   r   rN   r   rW   r8  r   r   r7  r-   r5  r   ri   r    r<  r(   r(   r)   list_instruments  s    

r?  )r   )r   )r   )r   )r   r   )YZfastapir   r   r   r   r   r   typingr   r	   r
   r   Z
sqlalchemyr   Zsqlalchemy.ormr   r   r   Zapp.dbr   r   Zapp.utils.datastream_apir   r   r   r   r   Zapp.db.modelsr   r   r   r   Zapp.db.databaser   Zfastapi.middleware.corsr   Zapp.apir   Zapp.schemasr   r   r   Zpandasr   r  Zsqlalchemy.excr   Zfastapi.responsesr    Zpydatastreamr   r!   ZBasemetadataZ
create_allZenginer	  Zinclude_routerZrouterZadd_middlewarer   r   r   r*   postr~   rc   rU   r   r   r   r   r   r   r   r   r   r   r   r   r  r
  r   r  r2  r4  r;  r>  r?  r(   r(   r(   r)   <module>   s    

"L
 f


   





X
 0	 H