o
    ÅÐb‰)  ã                   @   sF  d dl mZmZ d dl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 d dlmZ d dlmZ d d	lmZ d dlZd
dlmZmZmZ d dlmZ ejjejejejej dZ!e!j"ddZ"dd„ Z#dd„ Z$dd„ Z%dd„ Z&edddd„ ƒZ'eddedd„ ƒƒZ(edddd„ ƒZ)eddd d!„ ƒZ*eddd"d#„ ƒZ+dS )$é    )ÚrenderÚredirect)ÚHttpResponseN)ÚListView)ÚauthenticateÚloginÚlogout)ÚHttpResponseRedirect)Úmessages)Úlogin_required)Údateé   )Úunauthenticated_userÚallowed_usersÚ
admin_only)Úsettings)ÚuserÚpasswordÚhostÚdatabaseT)Ú
dictionaryc                 C   ó   t | dddiƒS )Nzportal/home.htmlÚtitleZHome©r   ©Úrequest© r   ú*/var/www/secure340b-portal/portal/views.pyÚhome   ó   r   c                 C   r   )Nzportal/about.htmlr   ZAboutr   r   r   r   r   Úabout   r   r    c                 C   sv   | j jrtdƒS | jdkr3| j d¡}| j d¡}t| ||d}|d ur-t| |ƒ tdƒS t 	| d¡ i }t
| d|ƒS )Nú../customer-report-listÚPOSTÚusernamer   )r#   r   z!Username OR password is incorrectzportal/login.html)r   Úis_authenticatedr   Úmethodr"   Úgetr   r   r
   Úinfor   )r   r#   r   r   Úcontextr   r   r   Ú	loginPage"   s   

r)   c                 C   s   t | ƒ tdƒS )Nú../login)r   r   r   r   r   r   Ú
logoutUser6   s   r+   r*   )Z	login_urlc           (      C   sÒ  | j  d¡}| j}d}d}t ||jf¡ t ¡ }|D ]}|d t|ƒkr'd}q|dkr0tdƒS |}d}t ||f¡ t ¡ }	d}
t |
|f¡ t ¡ }d	}t ||f¡ t ¡ }t	 
|¡}|D ].}d
 t|d ƒ¡|d< d
 t|d ƒ¡|d< d
 t|d ƒ¡|d< d
 t|d ƒ¡|d< q`g }g }g }g }g }g }g }g }g }|D ]I}|d |d  }|d |d  }| |d ¡ | |d ¡ | t|d ƒ¡ | t|d ƒ¡ | t|d ƒ¡ | t|ƒ¡ | t|ƒ¡ q£d}t ||f¡ t ¡ }|D ]}| t|d ƒ¡ | t|d ƒ¡ qü||||	|||t|ƒt|ƒt|ƒt|ƒt|ƒt|ƒt|ƒdœ}g d¢} | D ],}!|! d¡d }"|! d¡d }#tdt|"ƒdƒ d¡}$d}%||!f}&t |%|&¡ t ¡ }'q6t| d|ƒS )NÚreportr   z6SELECT reportid FROM portal_user_map WHERE userid = %sÚreportidr   r!   ú(SELECT * FROM report_queue WHERE id = %szŠSELECT * FROM 340b_claims 
        WHERE report_identifier = 
        (SELECT report_identifier FROM report_queue where id = %s) limit 100aÕ  SELECT quarter as quarter, count(*) as count, 
        SUM(total_payment) as sum_tp, 
        SUM(disp_fee) as sum_df,
        SUM(retail_margin) as sum_rm, 
        SUM(total_payment)-SUM(disp_fee) as paidce,
        SUM(disp_fee)-SUM(retail_margin) as impact,
        SUM(dir_fee) as sum_dir
        FROM 340b_claims
        WHERE report_identifier =
        (SELECT report_identifier FROM report_queue where id = %s)
        GROUP BY quarter
        ORDER BY quarterz${:,.2f}Úsum_tpÚsum_dfÚpaidceÚimpactÚsum_rmÚquarterÚcountÚsum_dira9  SELECT quarter as quarter, 
        count(*) as uninsured_count, 
        SUM(total_payment) as sum_uninsured_pmts
        FROM 340b_claims
        WHERE report_identifier =
        (SELECT report_identifier FROM report_queue where id = %s) AND uninsured = "YES"
        GROUP BY quarter
        ORDER BY quarter;Úuninsured_countZsum_uninsured_pmts)ÚbcdataÚ
table_dataÚ
report_numÚreportsÚclaimsÚlabelsÚ	countdataÚ	totalpmtsÚdispfeesÚdirfeesr1   r2   Ú	uninsuredZuninsuredpmts©z2021-01z2021-02z2021-03z2021-04z2021-05z2021-06z2021-07z2021-08z2021-09z2021-10z2021-11z2021-12z2022-01z2022-02z2022-03ú-il  z%BzõSELECT COUNT(*), SUM(transaction_payment), SUM(disp_fee), SUM(retail_margin) FROM 340b_claims 
            WHERE report_identifier = (SELECT report_identifier FROM report_queue where id = %s) 
            AND DATE_FORMAT(fill_date,"%Y-%m") = %s;zportal/dashboard.html)ÚGETr&   r   ÚcursorÚexecuteÚidÚfetchallÚintr   ÚcopyÚdeepcopyÚformatÚfloatÚappendÚlistÚsplitr   ÚstrftimeÚfetchoner   )(r   r,   Úcurrent_userZ
allow_viewZ	block_sqlZallowed_reportsZreport_checkÚ	report_idÚsqlr;   Ú	claim_sqlr<   Úbc_sqlÚbc_datar9   ZqtrÚ	bcquarterÚbccountÚbctotalpaymentsÚ	bcdispfeeÚbcpaidceÚbcimpactÚbcdirfeeZbccountuninsuredZbc_sum_uninsured_pmtsr4   Úpaid_cer2   Úuninsured_sqlÚuninsured_dataÚins_quarterr(   Úpast_monthsZ
past_monthZtable_monthZ
table_yearZtable_month_labelZtable_queryZtable_recordZtable_resultr   r   r   Ú	dashboard;   sœ   €
òrf   c                 C   s&   d}t  |¡ t  ¡ }t| dd|iƒS )NzSELECT * from report_queuezportal/reportlist.htmlÚ
reportlist)rF   rG   rI   r   )r   Úrpt_sqlrg   r   r   r   rg   Á   s   
rg   c                 C   s6   g }| j }d}t ||jf¡ t ¡ }t| dd|iƒS )Nz_SELECT * FROM report_queue WHERE id IN (SELECT reportid FROM portal_user_map WHERE userid = %s)z portal/customer-report-list.htmlrg   )r   rF   rG   rH   rI   r   )r   ZreportslistrT   Zreport_list_queryrg   r   r   r   ÚcustomerreportlistË   s   
ÿri   c                 C   sB   d}t  |¡ t  ¡ }g }|D ]}| |¡ q|}t| dd|iƒS )Nz#SELECT * from report_queue limit 3;zportal/claims.htmlÚtable)rF   rG   rI   rO   r   )r   rh   rg   Z	tablelistÚrrj   r   r   r   Ú
claim_list×   s   
rl   c                 C   s¸  | j }d}t ||jf¡ t ¡ }|d d }d}|f}t ||¡ t ¡ }d}|f}	t ||	¡ t ¡ }
d}t ||	¡ t ¡ }d}t ||	¡ t ¡ }g }g }g }g }g }g }g }g }|D ]I}|d |d	  }|d	 |d
  }| |d ¡ | |d ¡ | t|d ƒ¡ | t|d	 ƒ¡ | t|d ƒ¡ | t|ƒ¡ | t|ƒ¡ q_|D ]}| t|dƒƒ¡ q«||||
||t|ƒt|ƒt|ƒt|ƒt|ƒt|ƒdœ}g d¢}t| d|ƒS )Nz7SELECT * FROM portal_user_map WHERE userid = %s LIMIT 1r   r-   r.   z‹SELECT * FROM 340b_claims 
        WHERE report_identifier = 
        (SELECT report_identifier FROM report_queue WHERE id = %s) LIMIT 100;aÖ  SELECT quarter as quarter, count(*) as count, 
        SUM(total_payment) as sum_tp, 
        SUM(disp_fee) as sum_df,
        SUM(retail_margin) as sum_rm, 
        SUM(total_payment)-SUM(disp_fee) as paidce,
        SUM(disp_fee)-SUM(retail_margin) as impact,
        SUM(dir_fee) as sum_dir
        FROM 340b_claims
        WHERE report_identifier =
        (SELECT report_identifier FROM report_queue where id = %s)
        GROUP BY quarter
        ORDER BY quarter;zýSELECT quarter as quarter, count(*) as uninsured_count
        FROM 340b_claims
        WHERE report_identifier =
        (SELECT report_identifier FROM report_queue where id = %s) AND uninsured = "YES"
        GROUP BY quarter
        ORDER BY quarter;r/   r0   r3   r4   r5   r6   r7   )r8   r:   r;   r<   r=   r>   r?   r@   rA   r1   r2   rB   rC   zportal/report.html)	r   rF   rG   rH   rI   rO   rJ   rP   r   )r   rT   Zreport_id_queryrU   Zthis_reportidrV   Zsql_datar;   rW   Z
claim_datar<   rX   rY   rb   rc   rZ   r[   r\   r]   r^   r_   r`   Zbc_count_uninsuredr4   ra   r2   rd   r(   re   r   r   r   r,   å   sl   ôr,   ),Zdjango.shortcutsr   r   Údjango.httpr   Zmysql.connectorÚmysqlZdjango.views.genericr   Údjango.contrib.authr   r   r   r	   Údjango.contribr
   Zdjango.contrib.auth.decoratorsr   Údatetimer   rK   Ú
decoratorsr   r   r   Údjango.confr   Ú	connectorÚconnectÚDB_USERÚDB_PASSWORDÚDB_HOSTÚDB_NAMEÚconnrF   r   r    r)   r+   rf   rg   ri   rl   r,   r   r   r   r   Ú<module>   sF    û
 

