3
dO                 @   s  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d Z4dd Z5dd Z6dd Z7dd Z8dd Z9dd Z:dd Z;dd Z<dd Z=dd  Z>d!d" Z?d#d$ Z@d%d& ZAe"jBd'e"jCd(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               C   s   t dS )Nzregister.html)r    r"   r"   @C:\Users\Dell\Desktop\data_up2\app\controllers\usercontroller.pyregister   s    r$   c               C   s   t dS )Nzcreate_user.html)r   r"   r"   r"   r#   add_user   s    r%   c              C   sX   t jjd} t jjd}t j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_byr"   r"   r#   
new_create   s    
r/   c              C   sj   t jjd} t jjdj }t jjd}t jjd}t|}tjd}t| |||| td tt	dS )Nr&   roler'   r(   r)   zUser added successfullyzauth_blueprint.users_display)
r   r*   r+   lowerr   r   r,   r   r   r   )r&   r0   r'   r(   r-   r.   r"   r"   r#   create$   s    
r2   c             C   s^   t | ||||d}ytjj| tjj  W n, tk
rX } ztjj  W Y d d }~X nX d S )N)r&   r0   r'   r(   r.   )r   r   r   addcommit	ExceptionZrollback)r&   r0   r'   r-   r.   userer"   r"   r#   r,   /   s    r,   c             C   s   t jdkr"tjj| }td|dS t jdkrtjj| }|d krDdS t jjd}t jjd}||_||_t	j
j  td ttd	S d S )
NGETzedit_user.html)r6   POSTzUser not found!r&   r'   zUser edited successfullyzauth_blueprint.users_display)r   methodr   queryr+   r   r*   r&   r'   r   r   r4   r   r   r   )user_idr6   r&   r'   r"   r"   r#   	edit_userQ   s    


r=   c       	      C   s   t jdkr"tjj| }td|dS t jdkrtjj| }|d krDdS t jjd}t jjd}t jjd}t jjd	}t jjd
}t jjd}t jjd}||_||_||_	||_
||_||_||_tjj  td ttdS d S )Nr8   zedit_db.html)db_datar9   zUser not found!connection_namehostnameportr&   r(   
schemaname	tablenamez  Data Source edited successfullyzauth_blueprint.data_display)r   r:   r   r;   r+   r   r*   r?   r@   rA   r&   r(   rB   rC   r   r   r4   r   r   r   )	r<   r>   r?   r@   rA   r&   r(   rB   rC   r"   r"   r#   edit_dbf   s0    


rD   c              C   s&   t jjt jj } t|  td| dS )Nzupload.html)r?   )r   r;   Zwith_entitiesr?   allprintr   )r?   r"   r"   r#   upload_file   s    rG   c              C   sL  t jd } td }| j}| j| t||d}tjj| tjj  |j	}dddddd	d
}t
jjf |}|j }|}td| tj|}	||	d< ||	d< |	jjj |	_tjdtjd xd|	j D ]V\}
}g }dddddddddddddddg}xJ|D ]B}||jkr2|| }tj|r"d n|}|j| q|jd  qW |j||g d}t|| y|j|| W q tk
r } zX|jjd  d!krtj d"|d   d#|d$  d%|d&   ntj d'| d(|  W Y d d }~X q t!k
r } ztj d'| d(|  W Y d d }~X qX qW |j  |j"  |j"  t#d) t$t%d*S )+NZ	excelFiler)   )	file_pathuploaded_byroot z	127.0.0.1i  data_uploaderT)r6   r(   hostrA   databaseraise_on_warningsz
file_path:added_byfile_idzdata_insertion_errors.log)filenamelevelAirline_CodeAirlineAirline_PNR
First_Name	Last_Namez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: zData inserted successfully.zauth_blueprint.file_display)&r   filesr   rR   saver   r   r3   r4   r)   mysql	connectorconnectcursorrF   pd
read_excelcolumnsstrstriploggingbasicConfigERRORZiterrowsindexZisnaappendextendexecuter   origargserrorr5   closer   r   r   )filerI   Zoriginal_fileZuploaded_fileZuploaded_file_idconfig
connectionri   pathdf_rowvaluesZcolumns_to_extractcolvalueZsqlr7   r"   r"   r#   	save_file   s|    






*&,r   c             C   s~  t jjd}t| d}t|}t| tjj|}|j}|r|\}}}}}	d| d|	 d| d| d| 
}
t|
}t	 }t
||d|d}|j }t|gj|jj| k}|j|}d	d
 |D }x$|D ]}|jdd  |jdd  qW t| |d krd}nt|}tt jjdd}|d | }|| }||| }t| t|}|| d | }t| td||||||| |d	S )N	row_countrb   zmysql+mysqlconnector://:@/T)autoloadautoload_withc             S   s   g | ]}t |qS r"   )dict).0r   r"   r"   r#   
<listcomp>L  s    zshow_data.<locals>.<listcomp>rP   rQ      Zpagezshow_data.html)	page_datacurrent_page	num_pagesr   start_index	end_indexitem_iddata_source)r   rw   r+   rF   get_connection_detailsr   r;   rC   r
   r   r   rh   r   ZwherecrQ   ru   popintlenr   )r   r   r   connection_detailsZ
table_namer@   rA   r&   rB   r(   connection_stringenginemetadataZdb_tabler|   r;   Zresult_proxyZ
data_arrayr   r   r   r   r   Z
total_rowsr   r"   r"   r#   	show_data5  sB    "


r   c             C   s0   t jj| }|r(|j|j|j|j|jfS d S d S )N)r   r;   r+   r@   rA   r&   rB   r(   )r   r|   r"   r"   r#   r     s    r   c             C   s   t | }t| |r|\}}}}}d| d| d| d| d| 
}t|}	t|	d}
yt||
d|	d}|j }W n tk
r   d}Y nX |S dS d S )	Nzmysql+mysqlconnector://r   r   r   )bindT)r   r   F)r   rF   r
   r   r   existsr   )Zconnection_idrC   r   r@   rA   r&   rB   r(   r   r   r   tableZtable_existsr"   r"   r#   check_table_exists  s    "

r   c              C   s   t jjd} t jjd}td|  d}d}d}d}d}d	}d
| d| d| d| d| 
}t|}	t|	d}
tjj| }|j}td| t	j
|}|	j  }td |j||ddd W d Q R X dS )Nr   rQ   zitem IDrJ   rK   z	127.0.0.1Z3306rL   Zticket_detilszmysql+mysqlconnector://r   r   r   )r   z
file_path:Zinside_enginrs   F)Z	if_existsrr   zData inserted successfully.)r   r*   r+   rF   r
   r   r   r;   rH   rj   rk   beginZto_sql)r   rQ   r&   r(   r@   rA   rB   rC   r   r   r   r}   rH   r~   r|   r"   r"   r#   save_data_db  s(    
"



r   c              C   s   t d ddddddd} tjjf | }|j }d	}|j| |j }|j  |j  tj	|d
dddddddddddddddddgd}t
j }tj|dd}|j|dd d! W d Q R X |jd" t|d#dd$S )%NZgirJ   rK   z	127.0.0.1i  rL   T)r6   r(   rM   rA   rN   rO   zSELECT * FROM ticket_data;r)   rP   rQ   rT   rU   rV   rW   rX   zE-mailrY   rZ   r[   r\   r]   r^   r_   r`   ra   )rl   Z
xlsxwriter)r   ZSheet1F)Z
sheet_namerr   r   z	data.xlsx)attachment_filenameas_attachment)rF   rf   rg   rh   ri   ru   Zfetchallry   rj   Z	DataFrameioBytesIOZExcelWriterZto_excelseekr	   )r{   r|   ri   r;   r   r~   Z
excel_datawriterr"   r"   r#   download_excel  s.    
2
r   zerror_log.txtz)%(asctime)s - %(levelname)s - %(message)s)rR   rS   format)Dflaskr   r   r   r   r   r   r   r	   r   hashlibZ
sqlalchemyr
   r   r   r   r   r   r   r   r   r   r   r   app.models.modelr   r   r   r   r   Zwerkzeug.securityr   Zwerkzeug.utilsr   ro   osZpandasrj   r   ZnumpyZnpjsonZsqlalchemy.excr   r   Zpandas.api.typesr    reZdateutil.parserr!   Zmysql.connectorrf   r$   r%   r/   r2   r,   r=   rD   rG   r   r   r   r   r   r   rp   WARNINGr"   r"   r"   r#   <module>   sD   ,8
"  + 
*