
    R;h                         d dl mZmZ d dl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 d dlmZ d dlmZ d dlZ G d	 d
      Zy)    )HTTPException
UploadFileN)Session)CourseFilterQueryCourseListCreatCourseListUpdate)CourseListBaseTblCourseList)TblFullCourse)CustomResponse)IntegrityErrorc                   ^    e Zd ZdedefdZdefdZd Zd Z	de
fd	Zd
efdZd Zd
efdZy)CourseListServicedbtokenc                      || _         || _        y N)r   r   )selfr   r   s      7/var/www/html/beacon-api/app/api/course_list/service.py__init__zCourseListService.__init__   s    
    filec                   K   |j                   r)|j                   j                         j                  d      st        dd      	 t	        j
                  |j                        }dddd	d
ddd}|j                         D ]   }||j                  vst        dd|        |j                  t	        j                  |      d       }|j                         D ]g  \  }}|d   }| j                  j                  t              j!                  t        j"                  |k(        j%                         }	|	r|	j&                  nd }
|t	        j(                  |d         rt+        |d         nd |d   |d   |d   |d   t	        j(                  |d         rt+        |d         nd |
d}| j                  j                  t,              j!                  t,        j.                  |k(        j%                         }|r'|j1                         D ]  \  }}t3        |||        Bt-        di |}| j                  j5                  |       j | j                  j7                          t9        dd      S # t        $ r}t        dd|       d }~ww xY ww)N)z.xlsxz.xls  z,Only Excel files (.xlsx, .xls) are supportedstatus_codedetailzError reading Excel file: course_nameduration_hrsoperational_areafunctional_areatarget_audience	standardsvalidity_days)Course NameDuration (Hrs)Operational AreaFunctional AreaTarget Audience	StandardsValidity DayszMissing column in Excel: r%   r&   r'   r(   r)   r*   r+   )r   r   r    r!   r"   r#   r$   full_course_id1z%Courses uploaded/updated Successfullystatusmessage )filenamelowerendswithr   pd
read_excelr   	Exceptionkeyscolumnswherenotnulliterrowsr   queryr   filtercoursefirstr,   notnaintr
   r   itemssetattraddcommitr   )r   r   dfecolumn_mappingcol_rowr   full_courser,   course_dataexisting_coursekeyvalue
new_courses                   r   upload_coursesz CourseListService.upload_courses   s\    }}T]]%8%8%:%C%CDU%VC8fgg	Ztyy)B ), 200$,
 "&&(C"**$#>WX[W\<]^^ ) XXbjjnd+kkmFAsm,K''--6==$$3eg  <G[77DN*>@hhsK[G\>]C(8$9 :cg$'(:$;#&'8#9#&'8#9 ->@hhs?G[>\S%9!:bf"0	K #ggmmM:AA))[8eg  "-"3"3"5JCOS%8 #6 +9[9
J'1 $2 	S2YZZS  	ZC:TUVTW8XYY	Zs1   AJ
I) %+J
GJ
)	J2JJJ
c                 d   K   | j                   j                  t              j                         S wr   )r   r=   r   allr   s    r   get_all_full_coursesz&CourseListService.get_all_full_courses@   "     ww}}]+//11   .0c                 d   K   | j                   j                  t              j                         S wr   )r   r=   r
   rU   rV   s    r   get_all_coursesz!CourseListService.get_all_coursesC   rX   rY   filtersc                 (  K   | j                   j                  t              }|j                  r,|j	                  t        j                  |j                  k(        }|j
                  r,|j	                  t        j
                  |j
                  k(        }|j                  r,|j	                  t        j                  |j                  k(        }|j                  r,|j	                  t        j                  |j                  k(        }|j                         S wr   )	r   r=   r
   r!   r>   r    r"   r#   rU   )r   r\   r=   s      r   filter_coursesz CourseListService.filter_coursesF   s     m,""LL!>!>'BYBY!YZE##LL!?!?7C[C[![\E""LL!>!>'BYBY!YZELL!8!8G<M<M!MNEyy{s   DDrequestc                 d  K   t        j                  |      }|j                  s|j                  rw| j                  j                  t              j                  t        j                  |j                  k(        j                         }|st        dd      |j                  |_        	 t        j                  || j                         | j                  j                          t!        dd      S # t        $ r5 | j                  j                          t        dd|j                         w xY ww)Nr   zInvalid full_course_idr   z)Course already exists for full_course_id r-   zCourse Created Successfullyr.   )r	   model_validater   r,   r   r=   r   r>   r@   r   r?   r
   createrF   r   rollbackr   )r   r_   created_courserM   s       r   create_coursezCourseListService.create_courseR   s     '66w?))n.K.K''--6==,,0M0MMeg  #<TUU)4););N&	  9GGNN S2OPP  	GGB>C`C`Bab 	s   B%D0(:C/ "D0/>D--D0c                 0   d}d}d|i}	 t        j                  | d|d      }|j                          |j                         }g }|D ]z  }t        di |j                  d
      |j                  d      |j                  d      |j                  d      d}	| j                  j                  |	       |j                  |       | | j                  j                          d|iS # t         j                  $ r}t	        ddt        |       	      d }~ww xY w)Nz.https://lms.marinerskills.com/lmsmarinerskillsz$6598E51A-4C41-416C-821F-A50EBBBE4E99Keyz/FullCourseList
   )jsontimeouti  zLMS API error: r   ProgramBatchCourseBatchCourseCode)programbatchr?   batch_course_codestored_coursesr1   )requestspostraise_for_statusRequestExceptionr   strri   r   getr   mergeappendrF   )
r   LMS_BASE_URLAPI_KEYpayloadresprH   dataresultsitemr?   s
             r   sync_full_course_listz'CourseListService.sync_full_course_listf   s   G8'"	T==L>!AY[\D!!# yy{D" 88I.'*((8,%)XX.?%@	& F GGMM&!NN4   	 '** (( 	TC/#a&8RSS	Ts   +C$ $D7DDc                    K   t        j                  |      }t        j                  |j                  || j
                        }|st        dd      t        dd      S w)Ni  zCourse not foundr   r-   zCourse updated successfullyr.   )r	   ra   r
   update	course_idr   r   r   )r   r_   company_basecompanys       r   update_coursezCourseListService.update_course~   sS     %44W=&&w'8'8,PC8JKKS2OPPs   AAN)__name__
__module____qualname__r   dictr   r   rS   rW   r[   r   r^   r   re   r   r   r   r1   r   r   r   r   
   s]    7 4 .[ .[`22
,= 
Q? Q(+0Q+; Qr   r   )fastapir   r   pandasr5   sqlalchemy.ormr   app.api.course_list.schemasr   r   r   app.models.main.course_listr	   r
   app.models.main.full_coursesr   app.utils.schemas_utilsr   sqlalchemy.excr   rs   r   r1   r   r   <module>r      s1    -  " \ \ E 6 2 ) yQ yQr   