
    ]@e                        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,m-Z- d d	l.m/Z/ d dl0Z0d d
l1m2Z2 d dl3Z4d Z5d Z6d Z7d Z8d Z9d Z:d Z;d Z<d Z=d Z>d Z?d Z@d ZAd ZBd ZCd ZD e"jE        de"jF        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SQLAlchemyError)infer_dtype)parsec                       t          d          S )Nzregister.htmlr        @C:\Users\Dell\Desktop\data_up2\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_creater6      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/   r0   zUser added successfullyauth_blueprint.users_display)
r   r1   r2   lowerr   r	   r3   r   r   r   )r-   r8   r.   r/   r4   r5   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-   r8   r.   r/   r5   )r   r   r	   addcommit	Exceptionrollback)r-   r8   r.   r4   r5   useres          r(   r3   r3   /   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)rA   POSTUser not found!r-   r.   zUser edited successfullyr9   )r   methodr   queryr2   r   r1   r-   r.   r   r	   r>   r   r   r   )user_idrA   r-   r.   s       r(   	edit_userrJ   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 )NrD   zedit_db.html)db_datarE   rF   connection_namehostnameportr-   r/   
schemaname	tablenamez  Data Source edited successfullyzauth_blueprint.data_display)r   rG   r   rH   r2   r   r1   rM   rN   rO   r-   r/   rP   rQ   r   r	   r>   r   r   r   )	rI   rL   rM   rN   rO   r-   r/   rP   rQ   s	            r(   edit_dbrR   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rM   )r   rH   with_entitiesrM   allprintr   rT   s    r(   upload_filerX      sG    l001GHHLLNNO	/=IIIIr'   c                  V   t          d           t          j        d         } t          d         }| j        }|                     |           t          d           ddddd	d
d}t          j        j        di |}|	                                }|}t          d|           t          j        |          }|j        d         }t          |||          }	t          j                            |	           t          j                                         |	j        }
||d<   |
|d<   |j        j                                        |_        t+          j        dt*          j                   |                                D ]\  }}g }g d}t          d           |D ]V}||j        v r6||         }t          j        |          rd n|}|                    |           A|                    d            Wd }|                    ||
|g           t          |           d}	 |                    ||           # t          j        j        $ r)}|j        dk    r|                                  Y d }~d }~ww xY w|                                 |!                                 |!                                 dS )Nzinside save_file_user 	excelFiler0    jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjroot 	127.0.0.1  data_uploaderTrA   r/   hostrO   databaseraise_on_warnings
file_path:r   )	file_pathuploaded_byinserted_rows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_Taxes&kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkka7  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,updated_by) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)i&  zInserted Sucessfullyr&   )"rW   r   filesr	   rk   savemysql	connectorconnectcursorpd
read_excelshaper   r   r=   r>   r0   columnsstrstriploggingbasicConfigERRORiterrowsindexisnaappendextendexecuter    errnor@   close)filerg   original_fileconfig
connectionr   pathdfnum_rowsuploaded_fileuploaded_file_id_rowvaluescolumns_to_extractcolvalue
updated_bysqlrB   s                       r(   save_file_userr      s   	
"###=%D$-KMMIIm	
,---
 #! F (22622J  F D	,	t		B x{H =kX`aaaMJNN=!!!J$' !BzN$ByM%%''BJ !<GMRRRR ++-- .& .&3
 
 
" 	6777% 		$ 		$CciC "9Ee$$$$ d####
{$4Z@AAAf I	&NN3''''- 	& 	& 	&w$##%%%	& 
LLNNN!!s   H--I*I%%I*c                     t          d           t          d         } t          j        d         }|j        }|                    |           t          d           ddddd	d
d}t          j        j        d0i |}|	                                }t          j        |          }|j        j                                        |_        d}t          d|           |                                D ]\  }}	t          d           g }
g d}|D ]f}||	j        v rF|	|         }t          d|           t          j        |          rd n|}|
                    |           Q|
                    d            gt          d           	 |
d         |
d         |
d         |
d         |
d         |
d         |
d         |
d         |
d         |
d         |
d         |
d         |
d         |
d         |
d          |
d!         |
d"         | |
d         |
d         |
d         |
d         |
d         f}	 |                    ||           |                                 t+          j        d#           nO# t.          $ rB}|                                 t+          j        d$t          |          z             Y d }~nd }~ww xY w# t.          $ r6}|                                 t+          j        d%|            Y d }~d }~ww xY w|
d"         }t          d&|           d'}t          d(|           |                    ||f           |                                d         }t          d)|           	 t6          j                            |*                                          }|r5||_        t@          j                                         t          d+           nt          d,           n'# tB          $ r}t          d-|           Y d }~nd }~ww xY w|                                 |"                                 |"                                 tG          d.           tI          tK          d/                    S )1N"llllllllllllllllllllllllllllllllllr0   rZ   mmmmmmmmmmmmmmmmmr\   r]   r^   r_   r`   Tra   a  UPDATE ticket_data SET Airline_Code = %s, Airline = %s, Airline_PNR = %s, First_Name = %s, Last_Name = %s, `E-mail` = %s, Origin = %s, Destination = %s, Amount = %s, Refund_Amount = %s, Refund_Status = %s, Booking_Status = %s, Fare_Analyst = %s, Fare_amt = %s, all_Taxes = %s, added_by = %s, file_id = %s, updated_by = %s WHERE Airline_PNR = %s AND First_Name = %s AND Last_Name = %s AND Origin = %s AND Amount = %s
update_sqlz	inide for)rm   rn   ro   rp   rq   rr   rs   rt   ru   rv   rw   rx   ry   rz   r{   ri   rj   zvalue:zin midr                           	   
                     zUpdate successfulzError during update: %szUpdate Error: zfile_id:zNSELECT COUNT(*) FROM ticket_data WHERE file_id = %s AND updated_by IS NOT NULLzExecuting query:zupdated_rows:)r0   zRecord updated successfully.z&No record found for the given file_id.zSQLAlchemy Error:zData updated successfully.zauth_blueprint.file_displayr&   )&rW   r	   r   r}   rk   r~   r   r   r   r   r   r   r   r   r   r   r   r   r   r   r>   r   infor?   r@   errorfetchoner   rH   	filter_byfirstupdated_rowsr   r!   r   r   r   r   )r   r   r   r   r   r   r   r   r   r   r   r   r   r   update_valuesrB   update_errorrj   rH   r   
data_entrys                        r(   update_file_ckeckerr      s   	
.///J=%DMMIIm	
 #! F (22622J  F 
}	%	%B%%''BJ	h , 
,z"""++-- *; *;3k
 
 
 & 	$ 	$CciChu%%% "9Ee$$$$d####h	; q	6!9fQiF1Ivayq	6!9fQiF2Jr
r
F2Jr
F2Jvbz:q	6!9fQiF1I	MBz=999!!###01111 B B B##%%%7#a&&@AAAAAAAAB  	; 	; 	;!!!M9<99::::::::	; RjG	*g\E	
e$$$
NN57*%%%??$$Q'L	/<(((
&Z))W)55;;==
 	<&2J#J01111:;;; & & &!1%%%%%%%%& 
LLNNN 

&'''G9::;;;s\   >BJ,>IJ,
J'%8J"J,"J''J,,
K,6+K''K,A8O 
O8O33O8c                    t           j                            d          }t          |           d}t	          |          }t          |           t
          j                            |          }|j        }|r|\  }}}}}	d| d|	 d| d| d| 
}
t          j	        
                    ||||	|          }|                    d	          }d
| d}|                    || f           |                                }|                                 |                                 |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_countr   mysql+mysqlconnector://:@/)rb   rO   rA   r/   rc   T)
dictionaryzSELECT * FROM z WHERE file_id = %sri   rj      pagezshow_data.html)	page_datacurrent_page	num_pagesr   start_index	end_indexitem_iddata_source)r   argsr2   rW   get_connection_detailsr   rH   rQ   r   r   r   r   r   fetchallr   popintlenr   )r   r   r   connection_details
table_namerN   rO   r-   rP   r/   connection_stringr   r   rH   
data_arrayr   r   r   r   r   
total_rowsr   s                         r(   	show_datar     s   l{++I	)K/<<	
!!+..J%J 9K6$*hjhjjjjHjjW[jj^hjj _,, - 
 

 ""d"33 A@@@uwj)))__&&
 	     
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   rH   r2   rN   rO   r-   rP   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   bindT)autoloadautoload_withF)r   rW   r   r   r   existsr   )connection_idrQ   r   rN   rO   r-   rP   r/   r   enginemetadata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   rj   zitem IDr\   r]   r^   3306r`   ticket_detilsr   r   r   r   r   re   inside_enginr   F)	if_existsr   zData inserted successfully.)r   r1   r2   rW   r   r   r   rH   rf   r   r   beginto_sql)r   rj   r-   r/   rN   rO   rP   rQ   r   r   r   r   rf   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          j        d          } t          |            | dk    r.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	d          S dddddd	d
}t          j        j        di |}|                                }t          j        d          }	t          |	           d}|                    ||	f           |                                }|	                                 |	                                 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	d          S )Nr8   checkergir\   r]   r^   r_   r`   Tra   zSELECT * FROM ticket_data;r0   ri   r   rj   rm   rn   ro   rp   rq   rr   rs   rt   ru   rv   rw   rx   ry   rz   r{   r   
xlsxwriterr   Sheet1F
sheet_namer   r   Aapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheet	data.xlsxmimetypeas_attachmentdownload_namer0   z.SELECT * FROM ticket_data WHERE added_by = %s;r&   )r	   r2   rW   r   r   r   r   r   r   r   r   	DataFrameioBytesIOExcelWriterto_excelseekr
   )

downloaderr   r   r   rH   r   r   
excel_datawriterrA   s
             r(   download_excelr    s   {6""J	*Yd'!%
 
 _,66v66
""$$ -u   	 \$  )_  )_  )_  `  `  ` Z\\
^J|<<< 	BKK85KAAA	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	 X%	
 
 
 	
 '!%
 
 _,66v66
""$$[d@utg&&&   	 \$  )_  )_  )_  `  `  ` Z\\
^J|<<< 	BKK85KAAA	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	B 	 X%	
 
 
 	
s$   D&&D*-D*I**I.1I.c                    t          d|            ddddddd}t          j        j        di |}|                                }t          j        d	          }| }t          |           d
}|                    ||f           |                                }|	                                 |	                                 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d          S )Nr   r\   r]   r^   r_   r`   Tra   r0   z-SELECT * FROM ticket_data WHERE file_id = %s;r   r   r   r   r   Fr   r   r   r   r  r&   )rW   r   r   r   r   r	   r2   r   r   r   r   r  r  r  r  r	  r
  r
   )r   r   r   r   rA   rj   rH   r   r   r  r  s              r(   download_excel_filer  )  s   	)G#! F (22622J  F	T		DG	$KKK;E
NN5'$$$??D LLNNN 
d  %[  %[  %[  
\  
\  
\B J	
<	8	8	8 >F
Fxu===> > > > > > > > > > > > > > > OOA T!	   s   ?D$$D(+D(zerror_log.txtz)%(asctime)s - %(levelname)s - %(message)s)rk   rl   format)G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    r!   pandas.api.typesr"   redateutil.parserr#   mysql.connectorr   r)   r+   r6   r;   r3   rJ   rR   rX   r   r   r   r   r   r   r  r  r   WARNINGr&   r'   r(   <module>r#     s   e e e e e e e e e e e e e e e e e e e e e e 				  | | | | | | | | | | | | | | | | | | | | | | | | | | | | ; ; ; ; ; ; ; ; ; ; ; ; ; ; 4 4 4 4 4 4 * * * * * *  				                J J J J J J J J J J ( ( ( ( ( ( 				 ! ! ! ! ! !    , , ,/ / /5 5 5	= 	= 	=  DA A A*@ @ @@J J J
f" f" f"P}< }< }<d<] <] <]b    () ) )8T
 T
 T
l* * *\  _GOLw x x x x x xr'   