
    G	/iG                     D   U d dl m Z mZ d dl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 d dlmZmZmZ d dlmZmZ d dlZd dlmc mc mZ d d	lmZ d d
lmZmZ d dlm Z  d dl!m"Z" d dl#m$Z$ d dl%m&Z&m'Z' d dl(m)Z) d dl*m+Z+ d dl,m-Z- dZ.e/ed   df   e0d<   dZ1 G d d      Z2y)    )datetime	timedeltaN)ListLiteral)HTTPException
UploadFile)FileResponse)OAuth2PasswordRequestForm)ConnectionConfigFastMailMessageSchema)Workbookload_workbook)Session)
JWTManagerJWTPayloadSchema)Messages)TblGroup)TblSimulation)StudentBase
TblStudent)generate_secure_password)
send_email)CustomResponse)studentadmin
superadmin.VALID_ROLESdownload_excelc                       e Zd ZdedefdZdej                  fdZde	fdZ
de	fdZde	fd	Zd
efdZdeej                      fdZededefd       Zd ZdefdZdefdZdefdZd Zd
efdZd
efdZy)StudentServicedbtokenc                      || _         || _        y N)r"   r#   )selfr"   r#   s      >/var/www/html/retail-simulation-api/app/api/student/service.py__init__zStudentService.__init__    s    
    requestc                    K   t        j                  |      }t        j                  || j                         | j                  j                          t        dt        j                        S w)N1statusmessage)	r   model_validater   creater"   commitr   r   STUDENT_CREAT)r&   r*   created_students      r'   create_studentzStudentService.create_student$   sK     %44W=/4773S(2H2HIIs   A+A-
student_idc                    K   t        j                  || j                        }t        j                  j                  |      S wr%   )r   	get_by_idr"   schemasStudentResponser0   r&   r6   r   s      r'   get_studentzStudentService.get_student*   s3     &&z477;&&55g>>s   A Ac                    K   t        j                  || j                        }|st        dt        j
                        t        j                  j                  |d      S wN  status_codedetailT)from_attributes)	r   r8   r"   r   r   STUDENT_NOT_FOUNDr9   r:   r0   r;   s      r'   get_student_getzStudentService.get_student_get.   sN     %%j$'':C8R8RSS&&55gt5TTs   AA!c                 (  K   t        j                  || j                        }|st        dt        j
                        |j                  st        dt        j                        t        j                  j                  |j                  d      S wr>   )r   r8   r"   r   r   rD   groupGROUP_NOT_ASSIGNEDr9   GroupResponser0   r;   s      r'   get_student_groupz StudentService.get_student_group4   sm     &&z477;C8R8RSS}}C8S8STT$$33GMMSW3XXs   BB
group_codec                   K   | j                   j                  t              j                  t        j                  |k(        j                         }|sg S |D cg c]!  }t        j                  j                  |      # c}S c c}w wr%   )	r"   queryr   filterrK   allr9   GetSimulationGroupStudentr0   )r&   rK   studentsss       r'   get_simulation_group_studentz+StudentService.get_simulation_group_student<   sy     GGMM*%VJ))Z78SU 	 I 
 --<<Q?
 	
 
s   AB&B>Bc                 p  K   |D ]y  }t        j                  |j                               }|j                  t	        dt
        j                        c S t        j                  |j                  || j                         { | j                  j                          t	        dt
        j                        S w)N-1r-   r,   )r   r0   
model_dumpr6   r   r   SEGMENT_NOT_FOUNDr   updater"   r2   STUDENT_UPDATE)r&   r*   requpdated_students       r'   update_studentzStudentService.update_studentR   s     C)889IJO))1%T8;U;UVVo88$''R	 
 	S(2I2IJJs   B4B6credentialsc           	        K   |j                  t              j                  t        j                  | j                  k(        j                         }|st        dt        j                        | j                  |j                  k7  rt        dt        j                        |j                  r|j                  t              j                  t        j                  |j                  k(  t        j                  dk(  t        j                  |j                  k7        j                         }|rt        dd|j                   d      d|_        |j                          t!        |j                  ddt#        j$                         t'        d	
      z         }t)        j*                  |      }|j,                  r|j,                  j.                  nd }|d|j                  d|j                  |dS w)Nr?   r@   i  r,   i  z	Student 'z'' from your group is already logged in.r      )minutes)r6   	user_type	user_roleexpbearer)access_token
token_typeemail_idroler6   group_id)rM   r   rN   student_emailusernamefirstr   r   rD   passwordINCORRECT_PASSWORDrK   login_statusr6   
first_namer2   r   r   utcnowr   r   create_access_tokenrG   ri   )r]   r"   r   activepayloadre   ri   s          r'   student_loginzStudentService.student_login[   s    ((:&--j.F.F+J^J^.^_eegC8R8RSS7#3#33C8S8STTXXj)00%%););;''3.%%););; eg	 
 # #&v'8'8&99`a   #
		"))!Ib$99	
 "55g>-4]]7==))("-- ++!
 	
s   G(G*c                 ,   | j                   j                  t        dt        j                        | j
                  j                  t              j                  t        j                  | j                   j                  k(        j                         }|st        dt        j                        |j                  dk7  rt        dt        j                        d|_        | j
                  j                          t        dt        j                        S )Ni  r@   r?   r,   i  0r-   )r#   r6   r   r   STUDENT_ID_REQUIEDr"   rM   r   rN   rl   CATCHMENT_NOT_FOUNDro   STUDENT_NOT_LOGINr2   r   STUDENT_LOGOUT)r&   r   s     r'   student_logoutzStudentService.student_logout   s    ::  (C8S8STT''--
+22:3H3HDJJLaLa3aciikC8T8TUU3&C8R8RSS"S(2I2IJJr)   simulation_codec                   K   |j                         j                  d      s|dz  }t        j                  j	                  t
        |      }t        j                  j                  |      st        dd| d      t        |d|      S w)zH
        Download an Excel file from the download_excel folder.
        z.xlsxr?   zExcel file 'z' not found.r@   zAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
media_typefilename)	lowerendswithospathjoin	EXCEL_DIRexistsr   r	   )r&   r}   filepaths      r'   r   zStudentService.download_excel   s{     
 $$&//8w&O77<<	?;ww~~h'C,FWWc8deeZ$
 	
s   BB	filec                 ~  *+K   	 dd l }dd l+|j                  j                  d        +j                  |j                  dd       }g d}d }|j                         D ]R  \  }}|D cg c]9  }t        |      j                         j                         j                  dd      ; }	}|	|k(  sP|} n |K|j                  |j                  d i      }dd||j                  d	      j                  j                         d
S |j                  j                  d        +j                  |j                  d|      }|j                  j                  j                         j                  j                         j                  j                  dd      |_        +fd*|j                  *      }|j!                   +j"                  |      d       }*fd}
d|j                  v r|d   j%                  |
      |d<   dt        fd}|j                         D ]  \  }}|d   |j'                  d      }|j'                  d      }|r|s4 ||      }| j(                  j+                  t,              j/                  t,        j0                  |k(  t,        j2                  |k(        j5                         }|r|j6                  |j8                  |df<   |}| j(                  j+                  t,              j/                  t,        j6                  j;                  | d      t,        j2                  |k(        j=                         }g }|D ]9  }	 |j?                  tA        |j6                  jC                  d      d                ; |rtE        |      dz   nd}| d|d}||j8                  |df<    i }|j                         D ].  \  }}|d   }|s|jG                  |g       j?                  |       0 g }g }|jI                         D ]  \  }}|d   jI                         D ci c]  \  }}| *|       } }}tK        |      }!| j'                  d      }| j'                  d      }| j(                  j+                  tL              j/                  tL        j2                  |k(        j5                         }"|"sEtM        |d|       }"| j(                  jO                  |"       | j(                  jQ                          | j(                  j+                  t,              j/                  t,        j6                  |k(        j5                         }#|#sj| d   rtA        | d         n|!}$t-        ||||$| j'                  d            }#| j(                  jO                  |#       | j(                  jQ                          nCdD ]$  }%| j'                  |%      tS        |#|%| |%          & | j(                  jQ                          |D ]  }|jI                         D ci c]  \  }}| *|       }}}| j(                  j+                  tT              jW                  |d         j5                         }&|&r9|d   |&_,        |d    |&_-        |d!   |&_.        | j(                  jQ                          t_               }'tU        |d   |d    |d   |d!   |'d"|#      }(| j(                  jO                  |(         | j(                  jQ                          d$d%|d&S c c}w #  Y xY wc c}}w c c}}w # t`        $ r})d't        |)      d(cY d })~)S d })~)ww xY ww))Nr   openpyxl)engineheader)	rp   	last_namerj   roll_norK   r}   
group_namenumber_of_memberscategory_assigned _rU   z&Missing required headers in Excel file   )r.   errorexpected_headerssample_rowsc                     | "t        | t              r j                  |       ry t        | t              r!| j	                         j                         dv ry | S )N)nannonenull )
isinstancefloatisnastrstripr   )xpds    r'   clean_valuez>StudentService.upload_students_from_excel.<locals>.clean_value   sG    9Au!5'"''!*a%!'')//*;?Z*Zr)   c                 `     |       } t        | t              rt        t        |             S | S r%   )r   r   r   int)r   r   s    r'   clean_group_codezCStudentService.upload_students_from_excel.<locals>.clean_group_code   s)    Na's1v;&r)   rK   namec                 ^    | j                         j                         j                  dd      S )Nr   -)r   upperreplace)r   s    r'   slugifyz:StudentService.upload_students_from_excel.<locals>.slugify   s$    zz|))+33C==r)   r   r}   z-%r      03dzAuto Created )r}   simulation_namer   r   )rK   r}   r   r   r   )r   r}   r   r   rj   rj   rp   r   r   r   )rp   r   rj   r   rm   rh   rK   r,   zUpload successful)r.   r/   warningsrw   )r.   r   )1numpypandasr   seek
read_exceliterrowsr   r   r   r   r   headvaluestolistcolumnsapplymapwherenotnaapplygetr"   rM   r   rN   r   r}   rl   rK   atlikerO   appendr   splitmax
setdefaultitemslenr   addr2   setattrr   	filter_byrp   r   r   r   	Exception),r&   r   npdfr   header_row_indexirowcol
normalizedr   r   indexr   sim_code
group_slugexisting_groupprefixexisting_codesnumsgnext_numnew_group_codegroup_student_mapr   gcerror_messageswarning_messagesrK   rQ   kvrl   student_count
simulationrG   num_membersfieldexistingpassword_plainr   er   r   s,                                             @@r'   upload_students_from_excelz)StudentService.upload_students_from_excel   s    C	4
 IINN1tyyDIB 
  $++-3  #" HNN$**,44S#>"   !11'($ (  'ZZ/"E(8#%771:#4#4#;#;#=	  IINN1tyyDTUB--/3399;??GGSQBJ [)B("((2,-B rzz)#%l#3#9#9:J#K< >c > !kkm
s|$,!$!6J"ww'89H%X !(!4J h/$//:=$44@  # &5C5N5Ne\12 'Fh/$//44xr]C$44@  # D+! KKALL,>,>s,CB,G(HI , 37D	AAH(.xq#%?N1?BEE%-.U ,V !#++-3&%00R8??D (
  N!(9(?(?(A$
H7?{7H7H7JK7Jtq!KN*7JK #H 99%67"YY|4
GGMM-0VM99XEFUW  "!.(0*7z(B"J GGKK
+GGNN$GGMM(+VH//:=>UW   !!45 E"567*   %#-(0#-*5*/))4G*HE GGKK&GGNN$"J 99U+7#E5%,?"J
 GGNN$#C9<EA1k!n,CE j1"_1EF   .1,.?+-0-=*+.y>(( %=%?N(#&|#4"%k"2&)/&: #I!/&#-G GGKK(9 $c )B^ GGNN ., WX!  Lb FJ  	4!CF33	4s   \=A"\ (>\ &\ .A\ ?\= D\ D#\ 6\8A\ A\ \/F\ A\ \
C%\ ?\= \ \
\ 	\:"\5/\:0\=5\::\=rj   c                    | j                   j                  t              j                  |      j	                         }|st        dd      d}d|j                   d|j                   d}t        |||	      }|st        d
d      dddS )Nr   r?   zStudent not foundr@   Your Student Portal PasswordHello ,

Your password is: 

Please keep it secure.tosubjectbodyi  zFailed to send emailr,   zPassword sent to emailr-   )	r"   rM   r   r   rl   r   rp   rm   r   )r&   rj   r   r   r   successs         r'   send_password_to_emailz%StudentService.send_password_to_emaili  s    ''--
+55M5RXXZC8KLL0**++B7CSCSBTTnowTJC8NOO*BCCr)   c                 P   | j                   j                  t              j                         }|st	        dd      g }|D ]W  }d}d|j
                   d|j                   d}t        |j                  ||      }|r=|j                  |j                         Y |rd	d
|dS dddS )Nr?   zNo students foundr@   r   r   r   r   r   rw   zSome emails failed to send)r.   r/   failed_emailsr,   z$Passwords sent to all student emailsr-   )
r"   rM   r   rO   r   rp   rm   r   rj   r   )r&   rQ   failuresr   r   r   r   s          r'   send_passwords_to_all_studentsz-StudentService.send_passwords_to_all_studentst  s    77==,002C8KLLG4GG..//FwGWGWFXXrsD G$9$97QUVG 5 56   7!) 
 *PQQr)   c                    K   | j                   j                  t              j                  t        j                  |k(        j                         }|S wr%   )r"   rM   r   rN   rK   rO   )r&   rK   rQ   s      r'   get_students_by_groupz$StudentService.get_students_by_group  s>     GGMM*%VJ))Z78SU 	
 s   AAc                   K   | j                  |       d {   }|st        dd      t        ddddddd	dd
	      }t        |      }d}|D ]  }d}d|j                   d|j
                   d|j                   d|j
                   d|j                   d|j                   d|j                   d| d}t        ||j                  g|d      }	|j                  |	       d {     dddS 7 7 w)Nr?   z No students found for this groupr@   zinfo@compunet.solutions	Jn193518fzsmtp.stackmail.comi  TF)	MAIL_USERNAMEMAIL_PASSWORD	MAIL_FROMMAIL_SERVER	MAIL_PORTMAIL_SSL_TLSMAIL_STARTTLSUSE_CREDENTIALSVALIDATE_CERTSz%https://trilliant.compunet.in/retail/z$Your Retail Simulation Login Detailsz
            Hello r   z,

            Your account for the Retail Simulation Platform has been created.

            Here are your login details:

            First Name : z
            Last Name  : z
            Email      : z
            Password   : z
            Group Code : z6

            Login using the link below:
            z

            Please change your password after first login.

            Regards,
            College Professor / Admin
            plain)r   
recipientsr   subtyper   z)Emails sent to all students in this groupr-   )r   r   r   r   rp   r   rj   rm   rK   r   send_message)
r&   rK   rQ   conffm	login_urlrR   r   r   r/   s
             r'   send_group_mailzStudentService.send_group_mail  s<    33J??C8Z[[3%/, 
. d^;	A<G<<.!++ / ,, (++ '//* +** &,, ( K D, $OO,	G //'***C H  B
 	
 @z +s"   C2C.C
C2"C0#C20C2N)__name__
__module____qualname__r   r   r(   r9   StudentCreater5   r   r<   rE   rJ   r   rS   r   StudentUpdater\   staticmethodr
   ru   r|   r   r   r   r   r   r   r   r)   r'   r!   r!      s    7 )9 JG,A,A J?S ?U UY# Y
S 
,KD1F1F,G K "
)B "
 "
 "
J
K
C 
(D4Z D4N	DC 	DR(c C
 C
r)   r!   )3r   r   r   typingr   r   fastapir   r   fastapi.responsesr	   fastapi.securityr
   fastapi_mailr   r   r   r   r   r   r   r   app.api.student.schemasapir   r9   sqlalchemy.ormr   app.dependency.authanticationr   r   app.locale.messagesr   app.models.main.groupr   app.models.main.simulationr   app.models.main.studentr   r   app.utils.common_utilsr   app.utils.emailr   app.utils.schemas_utilsr   r   tuple__annotations__r   r!   r  r)   r'   <module>r+     sy    ( ( 	   - * 6 B B ,  ) ) " F ( * 4 ; ; & 2FU7;<cAB  	t
 t
r)   