
    dO                        d dl mZmZmZmZmZmZmZmZmZ d dl	Z	d dl
Z
d dlmZmZmZmZmZmZmZmZmZmZmZmZ d dlmZmZmZmZ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&m&Z& d dl'Z(d dl)Z)d dl*m+Z+m,Z, d d	l-m.Z. d dl/Z/d d
l0m1Z1 d dl2Z3d Z4d Z5d Z6d Z7d Z8d Z9d Z:d Z;d Z<d Z=d Z>d Z?d Z@d ZA e"jB        de"jC        d           dS )    )	requestjsonifyrender_templater   flashredirecturl_forsession	send_fileN)create_engineMetaDataTableinsertColumnIntegerFloatDateTimeBooleanStringinspectselect)UserDBDatadbdata
data_error)generate_password_hash)secure_filename)datetime)NoSuchTableErrorIntegrityError)infer_dtype)parsec                       t          d          S )Nzregister.htmlr        5/var/www/html/t/fyr/app/controllers/usercontroller.pyregisterr(      s    ?+++r&   c                       t          d          S )Nzcreate_user.htmlr$   r%   r&   r'   add_userr*      s    -...r&   c                  |   t           j                            d          } t           j                            d          }t           j                            d          }t          |          }t	          j        d          }t          | |||           t          d           t          t          d                    S )NusernameemailpasswordidzUser created successfullyzauth_blueprint.login)	r   formgetr   r	   	add_usersr   r   r   )r,   r-   r.   hashed_password
created_bys        r'   
new_creater5      s    |
++HLW%%E|
++H,X66OT""Jh
;;;	
%&&&G233444r&   c                     t           j                            d          } t           j                            d                                          }t           j                            d          }t           j                            d          }t	          |          }t          j        d          }t          | ||||           t          d           t          t          d                    S )Nr,   roler-   r.   r/   zUser added successfullyauth_blueprint.users_display)
r   r0   r1   lowerr   r	   r2   r   r   r   )r,   r7   r-   r.   r3   r4   s         r'   creater:   $   s    |
++H<F##))++DLW%%E|
++H,X66OT""Jhe_jAAA	
#$$$G:;;<<<r&   c                    t          | ||||          }	 t          j                            |           t          j                                         d S # t
          $ r)}t          j                                         Y d }~d S d }~ww xY w)N)r,   r7   r-   r.   r4   )r   r   r	   addcommit	Exceptionrollback)r,   r7   r-   r3   r4   useres          r'   r2   r2   /   s    EO`jkkkD

t

   

s   =A 
BBBc                    t           j        dk    r0t          j                            |           }t          d|          S t           j        dk    rt          j                            |           }|dS t           j                            d          }t           j                            d          }||_        ||_        t          j
                                         t          d           t          t          d	                    S d S )
NGETzedit_user.html)r@   POSTUser not found!r,   r-   zUser edited successfullyr8   )r   methodr   queryr1   r   r0   r,   r-   r   r	   r=   r   r   r   )user_idr@   r,   r-   s       r'   	edit_userrI   Q   s    ~z~~g&&/d;;;; 
6	!	!z~~g&&<$$<##J//  )) 


())) >??@@@ 
"	!r&   c                    t           j        dk    r0t          j                            |           }t          d|          S t           j        dk    rvt          j                            |           }|dS t           j                            d          }t           j                            d          }t           j                            d          }t           j                            d	          }t           j                            d
          }t           j                            d          }t           j                            d          }||_        ||_        ||_	        ||_
        ||_        ||_        ||_        t          j                                         t#          d           t%          t'          d                    S d S )NrC   zedit_db.html)db_datarD   rE   connection_namehostnameportr,   r.   
schemaname	tablenamez  Data Source edited successfullyzauth_blueprint.data_display)r   rF   r   rG   r1   r   r0   rL   rM   rN   r,   r.   rO   rP   r   r	   r=   r   r   r   )	rH   rK   rL   rM   rN   r,   r.   rO   rP   s	            r'   edit_dbrQ   f   ss   ~,""7++~w???? 
6	!	!,""7++?$$(():;;!!*--\f%%!!*--!!*--<##L11
,"";//	"1###'%

0111 =>>???/ 
"	!r&   c                      t           j                            t           j                                                  } t          |            t          d|           S )Nzupload.htmlrL   )r   rG   with_entitiesrL   allprintr   rS   s    r'   upload_filerW      sG    l001GHHLLNNO	/=IIIIr&   c                     t           j        d         } t          d         }| j        }|                     |           t          ||          }t          j                            |           t          j                                         |j	        }dddddd	d
}t          j        j        di |}|                                }|}t          d|           t          j        |          }	||	d<   ||	d<   |	j        j                                        |	_        t)          j        dt(          j                   |	                                D ]I\  }
}g }g d}|D ]V}||j        v r6||         }t          j        |          rd n|}|                    |           A|                    d            W|                    ||g           d}t          ||           	 |                    ||           # t:          $ rk}|j        j        d         dk    r0t)          j         d|d          d|d          d|d                     nt)          j         d| d|            Y d }~d }~wtB          $ r%}t)          j         d| d|            Y d }~Cd }~ww xY w|                                 |"                                 |"                                 tG          d           tI          tK          d                    S )N	excelFiler/   )	file_pathuploaded_byroot 	127.0.0.1  data_uploaderTr@   r.   hostrN   databaseraise_on_warnings
file_path:added_byfile_idzdata_insertion_errors.log)filenamelevel)Airline_CodeAirlineAirline_PNR
First_Name	Last_NameE-mailOriginDestinationAmountRefund_AmountRefund_StatusBooking_StatusFare_AnalystFare_amt	all_Taxesa&  INSERT INTO ticket_data (Airline_Code,Airline,Airline_PNR, First_Name, Last_Name, `E-mail`, Origin, Destination, Amount, Refund_Amount, Refund_Status, Booking_Status, Fare_Analyst, Fare_amt, all_Taxes, added_by, file_id) VALUES (%s,%s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)r   i&  zDuplicate Entry: Airline_PNR=z, First_Name=   z, Last_Name=   zError: z. Data: Data inserted successfully.zauth_blueprint.file_displayr%   )&r   filesr	   rh   saver   r   r<   r=   r/   mysql	connectorconnectcursorrV   pd
read_excelcolumnsstrstriploggingbasicConfigERRORiterrowsindexisnaappendextendexecuter    origargserrorr>   closer   r   r   )filer[   original_fileuploaded_fileuploaded_file_idconfig
connectionr   pathdf_rowvaluescolumns_to_extractcolvaluesqlrA   s                     r'   	save_filer      sN   =%D$-KMMIIm =kJJJMJNN=!!!J$' #! F (22622J  F D	,	t		B !BzN$ByM%%''BJ !<GMRRRR ++-- 59 593
 
 
$ & 		$ 		$CciC "9Ee$$$$ d#### 	{$45666 xc&	9NN3'''' 	= 	= 	=v{1~%%xfQixxV\]^V_xxmstumvxxyyyy ;;;6;;<<< 	9 	9 	9M7A77v7788888888	9
 
LLNNN	
'(((G9::;;;s%   G11
J;A I!!J.JJc                 $   t           j                            d          }t          |           d}t	          |          }t          |           t
          j                            |          }|j        }|r|\  }}}}}	d| d|	 d| d| d| 
}
t          |
          }t                      }t          ||d|          }|                                }t          |g                              |j        j        | k              }|                    |          }d	 |D             }|D ].}|                    d
d            |                    dd            /t          |           |d k    rd}nt%          |          }t%          t           j                            dd                    }|dz
  |z  }||z   }|||         }t          |           t'          |          }||z   dz
  |z  }t          |           t)          d||||||| |	  	        S )N	row_country   mysql+mysqlconnector://:@/Tautoloadautoload_withc                 ,    g | ]}t          |          S r%   )dict).0r   s     r'   
<listcomp>zshow_data.<locals>.<listcomp>L  s    888Cd3ii888r&   rf   rg      pagezshow_data.html)	page_datacurrent_page	num_pagesr   start_index	end_indexitem_iddata_source)r   r   r1   rV   get_connection_detailsr   rG   rP   r   r   r   r   r   wherecrg   r   popintlenr   )r   r   r   connection_details
table_namerM   rN   r,   rO   r.   connection_stringenginemetadatadb_tabler   rG   result_proxy
data_arrayr   r   r   r   r   
total_rowsr   s                            r'   	show_datar   5  sp   l{++I	)K/<<	
!!+..J%J 99K6$*hjhjjjjHjjW[jj^hjj011 ::XFSSS ^^%%
z""(();w)FGG!))%0088<888
     
D!!!	$	*$		y>>	 w|''2233L  !#y0Ki'I ;y01I	) ZJi'!+	9I	)+y|gp  }F  T_  kt  }D  Q\  ]  ]  ]  ]r&   c                     t           j                            |           }|r |j        |j        |j        |j        |j        fS d S )N)r   rG   r1   rM   rN   r,   rO   r.   )r   r   s     r'   r   r     sM    !!+..J 	O!
 	
 tr&   c           
      0   t          |           }t          |           |ru|\  }}}}}d| d| d| d| d| 
}t          |          }	t          |	          }
	 t	          ||
d|	          }|                                }n# t          $ r d}Y nw xY w|S dS )	Nr   r   r   r   bindTr   F)r   rV   r   r   r   existsr   )connection_idrP   r   rM   rN   r,   rO   r.   r   r   r   tabletable_existss                r'   check_table_existsr     s    />>	
 9K6$*hjhjjjjHjjW[jj^hjj011(((	!)XFSSSE <<>>LL 	! 	! 	! LLL	!  us   'B BBc            
      V   t           j                            d          } t           j                            d          }t          d|            d}d}d}d}d}d	}d
| d| d| d| d| 
}t	          |          }	t          |	          }
t          j                            |           }|j        }t          d|           t          j
        |          }|	                                5 }t          d           |                    ||dd           d d d            n# 1 swxY w Y   dS )Nr   rg   zitem IDr\   r]   r^   3306r`   ticket_detilsr   r   r   r   r   re   inside_enginr   F)	if_existsr   r{   )r   r0   r1   rV   r   r   r   rG   rZ   r   r   beginto_sql)r   rg   r,   r.   rM   rN   rO   rP   r   r   r   r   rZ   r   r   s                  r'   save_data_dbr     s   ly))Gly))G	)WHHH	DJIf(ffXffffSWffZdff,--FV$$$H:>>'""DI	,	"""	y	!	!B	 J:n
		)Z85	IIIJ J J J J J J J J J J J J J J )(s   ))DD"%D"c                  \   t          d           ddddddd} t          j        j        di | }|                                }d	}|                    |           |                                }|                                 |                                 t          j	        |g d
          }t          j                    }t          j        |d          5 }|                    |dd           d d d            n# 1 swxY w Y   |                    d           t          |dd          S )Ngir\   r]   r^   r_   r`   Tra   zSELECT * FROM ticket_data;)r/   rf   rg   rj   rk   rl   rm   rn   ro   rp   rq   rr   rs   rt   ru   rv   rw   rx   )r   
xlsxwriter)r   Sheet1F)
sheet_namer   r   z	data.xlsx)attachment_filenameas_attachmentr%   )rV   r~   r   r   r   r   fetchallr   r   	DataFrameioBytesIOExcelWriterto_excelseekr
   )r   r   r   rG   r   r   
excel_datawriters           r'   download_excelr     s   	$KKK#! F (22622J  F )E
NN5??D LLNNN 
d  %N  %N  %N  
O  
O  
OB J	
<	8	8	8 >F
Fxu===> > > > > > > > > > > > > > > OOA '   s   C<<D D zerror_log.txtz)%(asctime)s - %(levelname)s - %(message)s)rh   ri   format)Dflaskr   r   r   r   r   r   r	   r
   r   hashlib
sqlalchemyr   r   r   r   r   r   r   r   r   r   r   r   app.models.modelr   r   r   r   r   werkzeug.securityr   werkzeug.utilsr   r   ospandasr   r   numpynpjsonsqlalchemy.excr   r    pandas.api.typesr!   redateutil.parserr"   mysql.connectorr~   r(   r*   r5   r:   r2   rI   rQ   rW   r   r   r   r   r   r   r   WARNINGr%   r&   r'   <module>r      s   f f f f f f f f f f f f f f f f f f f f f f 				  | | | | | | | | | | | | | | | | | | | | | | | | | | | | ; ; ; ; ; ; ; ; ; ; ; ; ; ; 4 4 4 4 4 4 * * * * * *  				                : : : : : : : : ( ( ( ( ( ( 				 ! ! ! ! ! !    , , ,/ / /5 5 5	= 	= 	=  DA A A*@ @ @@J J J
f< f< f<T4] 4] 4]R    () ) )8' ' 'T  _GOLw x x x x x xr&   