
    7i                     P    d dl Z d dl mZ d dlmZmZ d dlZd dlmZ  G d d      Zy)    N)load_workbook)DictAny)datetimec                   n    e Zd Zd Zdedeeef   deeef   fdZdedeeef   fdZdedeeef   fdZ	y)	StudentExcelServicec                    d| _         g d| _        i ddddddddd	dd
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddi ddddddd
dddddddddddddddddddddddd dd!d"d#dd$d"ddd%d"d&dd'd"ddd(d"d)dd*d"ddd+d,d#dd-d,d&dd.d,d)di d/d,d0dd1d,d2dd3d4d&dd5d4d6dd7d4d)dd8d4d0dd9d:d#dd;d:d<dd=d:d&dd>d:d)dd?d@dAddBd@dCddDd@dEddFd@dGddHd@dIddJdKdAddLdKdCddKdEddKdGddKdIddMdAddMdNddMdCddMdEddMdOddP| _        y )QNz'files/Simulation Test file - clean.xlsx)
Assu Sum Mod APlan Working AAssu Sum Mod BPlan Working BAssu Sum Mod OnlinePlan Working OnlineRollout PlanConcept Org StructureWarehouse (Central)Warehouse (Direct Store)z Store Size r
   I6)sheetcellz No.of Bills p.m - Yr.1 I7z No of items per bill I8z Avg Price per item I9z COGS I10z Income z
 Rent p.m z Employee Cost carpet_area_br   capex_b	it_cost_bsales_psf_bgp_percent_bonline_trafficr   E6conversion_rateE7avg_order_valueE8fulfillment_costE9stores_y1_bangalorer   D6stores_y2_bangalorestores_y1_chennaiD7stores_y2_chennaistores_y1_hyderabadD8stores_y2_hyderabad
ceo_salaryr   
cfo_salarymarketing_head_salaryoperations_head_salaryD9hr_head_salaryD10warehouse_manager_salaryr   warehouse_staff_countC8warehouse_staff_salarywarehouse_rentdelivery_manager_salaryr   delivery_staff_countC7delivery_staff_salarydelivery_vehicle_cost
rent_psf_ar   E15staff_count_aE25avg_staff_salary_aE26electricity_cost_aE35maintenance_cost_aE40
rent_psf_br   staff_count_br   E20E30)avg_staff_salary_belectricity_cost_bmaintenance_cost_btech_team_costmarketing_spendplatform_commissionpayment_gatewaylogistics_cost)
excel_pathinput_sheetsfield_mapping)selfs    D/var/www/html/retail-simulation-api/app/api/student_excel/service.py__init__zStudentExcelService.__init__   sf   C
O
&6EO
 '2BD(QO
 %0@$&O	O

 #.>$MO
  0%@O
 "2EBO
 $4eDO
 )95IO
  0%@O
  0%@O
  0%@O
  0%@O
  0%@O
  0%@O
   0%@!O
"  0%@#O
( '7F)O
* !14@+O
, #3TB-O
. %5tD/O
0 &6F1O
6 (=tL7O
8 )>M9O
: )>M;O
< *? N=O
B "^T#JCO
D "^T#JEO
F  >4!HGO
H  >4!HIO
J "^T#JKO
L "^T#JMO
R $;TJSO
T $;TJUO
V $/FPT%UWO
X %0GQU&VYO
Z (?O[O
` '2GQU(VaO
b $/Dd%ScO
d %0Et&TeO
f (=tLgO
l &1KUY'ZmO
n #.HRV$WoO
p $/ISW%XqO
r $/ISW%XsO
x $4eDyO
z '7G{O
| !,<e"L}O
~ !,<e"LO
@ !,<e"LAO
F $4eDGO
H '7GIO
J -=e"L,<e"L,<e"L )>uM)>N-BE#R)>N(=uM]O
    
student_id	form_datareturnc                    ddl }ddl}d}|j                  j                  |      s |j                  |       | d| d}t        |d      5 }|j                  ||d       ddd       t        |      |d	S # 1 sw Y   xY w)
z"Save student form data to databaser   Nstudent_data/.jsonw   )indent)savedr_   )jsonospathexistsmakedirsopendumplen)r[   r_   r`   rj   rk   db_dirdb_filefs           r\   save_student_dataz%StudentExcelService.save_student_datac   s~     	ww~~f%BKKHAj\/ '31IIi1I-   YzBB  s   A77B c                 (   | j                  |      }t        | j                        }d}|j                         D ]K  \  }}|| j                  v s| j                  |   }|d   }|d   }	||j
                  v s=||   }
||
|	<   |dz  }M t        |d      rd|j                  _        t        j                         j                  d      }| j                  j                  dd	| d      }|j                  |       || _        |j                          ||d
S )z,Update existing Excel file with student datar   r   r      calculationautoz%Y%m%d_%H%M%Sz.xlsx	_updated_)updated_cellsrX   )_get_student_data_from_dbr   rX   itemsrZ   
sheetnameshasattrrx   calcModer   nowstrftimereplacesaveclose)r[   r_   rc   wbr{   
field_namevaluemapping
sheet_namecell_addressws	timestampoutput_paths                r\   update_excel_with_database_dataz3StudentExcelService.update_excel_with_database_datau   s    55jA 4??+ ".!3!3!5JT///,,Z8$W-
&v.JB',B|$!Q&M "6 2}%&,BNN# LLN++O<	oo--g9+U7ST
 &

!.kJJr^   c                     ddl }ddl}d| d}|j                  j                  |      si S t	        |d      5 }|j                  |      cddd       S # 1 sw Y   yxY w)zGet student data from databaser   Nzstudent_data/re   r)rj   rk   rl   rm   ro   load)r[   r_   rj   rk   rs   rt   s         r\   r|   z-StudentExcelService._get_student_data_from_db   sU    !*U3ww~~g&I '3199Q<	  	 s   AAN)
__name__
__module____qualname__r]   strr   r   ru   r   r|    r^   r\   r   r      st    Y
vCC CDcN CtTWY\T\~ C$$K# $K$sCx. $KL C  DcN  r^   r   )openpyxlr   typingr   r   rk   r   r   r   r^   r\   <module>r      s      "  	 `  ` r^   