3
_ziV                @   s  d dl mZmZ d dlmZmZmZ d dlmZ d dlZd dl	Z	d dl
Z
d dlZd dlZd dlZd dlZd dlZd dlmZmZmZmZ d dlmZ d dlmZmZ d dlmZmZ d d	lmZ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)m*Z* d dl+m,Z, d dl-m.Z. d dl/m0Z0 d dl1m2Z2 d dl3m4Z4 d dl5m6Z6 d dl7m8Z8 d dl9m:Z: d dl;m<Z< d dl=m>Z> d dl?m@Z@ d dlAmBZB d dlCmDZD d dlEmFZF d dlGmHZH d dlImJZJ d d lKmLZL d d!lMmNZN d d"lOmPZP d d#lQmRZR d d$lSmTZT d d%lUmVZV d d&lWmXZX d d'lYmZZZ d d(l[m\Z\ d d)l]m^Z^ d d*l_m`Z` d d+lambZb d d,lcmdZd d d-lemfZf d d.lgmhZh d d/limjZj d d0lkT d d0llT d d0lmT d d1lnmoZompZp d d2lqmrZr d d0lnT d d3lsmtZt d d4lmuZu ejvd5d6gd7d8d9 Zwejvd:d6gd7d;d< Zxejvd=d6gd7d>d? Zyejvd@d6gd7dAdB ZzejvdCdDdE Z{ejvdFd6gd7dGdH Z|ejvdId6gd7dJdK Z}ejvdLd6gd7dMdN Z~ejvdOd6gd7dPdQ ZejvdRd6gd7dSdT ZejvdUd6gd7dVdW ZdS )X    )jsonifyrequest)datetime	timedeltadate)OrderedDictN)appbookings_loggerreceipt_print_options
format_fee)db)sessionTrackingticketGenerator)db_cache	FromCache)get_booking_status_idcurrencyHandler)BookingTypeSeedBookingStatusSeedMandatoryPaymentsSeed)BookingGuestTypesSeed)BookingType)Booking)BookingGuest)BookingStatus)BookingPayment)Facility)	Inventory)Detail)	Mandatory)Transaction)Gatepass)GatepassGuest)GatepassDetail)	GuestType)GatepassVehicle)Group)Vehicle)SalesforceData)Sun)Modify)Gate)VehicleDetail)Destination)PaymentMethod)PaymentGateway)Ticket)Member)CheckInVehicle)ContactList)Note)Invoice)MandatoryPaymentPrices)BookingActivity)Partner)School)SchoolBooking)*)bookingTotalGraphbookingTotal)or_)currencyPostProcessorInvoice)defaultdictz/reports/gate-entry/todayPOST)methodsc              C   sn  dddg} t tj| }|r.t|g ddfS tjjtjt	tj
t	jkjttjtjkjt	jt	jt	jt	jtjtjtjtjtjtjtjtjtjtjtjtjjtj d kjtj!t"dkjtj!t"dkjtjdkjtjt#j$ j%d	kj& }|s"g }|j'd
t#j$ j%d  td|idfS g }x|D ]}ytjjt(jt)t(j*t)jkjt(j+t)j,t)j-jt(j d kjt(j*|jkjt)j!t"dkjt)j!t"dkj. }t/ }|jj%d|d< |j+|d< |j,d |j- |d< |j|d< |j|d< |j|d< |j|j |d< t0j1t2j3|j}|j d d d |d< t4|j|d< t5|jt5|j t5|j }	t5dt5|jd  |	 }
t4|
|d< |jrtjjt6jt6j7|jkj. }|j8|d< nd |d< |j'| W n t9k
r   Y nX q.W t#j$ j%d}d| d  }t:j;|}t:j<|}|j=|d!d"d# t:j>|dddddgdddgd$d%}|j=|d&}|j?  td'| |d(d)fS )*N
session_idz
Session ID)fieldalias)messagesdatai  UpdatedDeletedr   z%Y-%m-%dz)There is no data to display for the date z%d-%m-%Ymessagez%d-%b-%yDateBooking_Ref Client_NameZConservancy_Fee_CategoryZNo_of_GuestsNo_of_NightsZReceiptsrG   currency_nameCurrencyZCost_per_Person   d   Z
Total_Costr-   z%d-%m-%Y %H:%M:%Szreports/gate/GateEntryDataz.xlsxZ	BreakdownF)indexsum)rT   valuesaggfunczPivot Tablez.https://bookings.olpejetaconservancy.org/docs/)urlrG      )@fieldValidationr   jsonr   r   sessionqueryr"   joinr   gatepass_guest_typepayment_public_idr!   gatepass_idgatepass_public_idadd_columnspayment_personpayment_person_income_codepayment_person_dept_codegatepass_guest_countgatepass_discount_rategatepass_cost_per_ppgatepass_no_of_nightsgatepass_discount_reasongatepass_guest_rate_at_timegatepass_guest_cost_at_timegatepass_guest_currency_at_timegatepass_currencydestination
start_date
booking_idfilterdeletion_markerstatusr   r   nowstrftimeallappendr   r   booking_public_idbooking_ref_code
first_name	last_namefirstr   requestsgetget_currencyformatroundfloatr-   gatepass_destination_public_idgatepass_destination_name	ExceptionpandaExcelWriter	DataFrameto_excelpivot_tablesave)validation_listrF   get_gatepass_guestsrJ   
guest_datasingle_guestget_bookingZreturn_guest_dataguest_currencytotal_without_discounttotal_with_discountZget_destination_detailsdate_generatedfilenamewriterZguest_data_dftableZpivot_df r   3/booking_service/app/routes/booking_reports_urls.pygenerate_gate_entry_report@   s    








r   z/reports/details/todayc        >   V   C   sr   dddg} t tj| }|r,td|idfS ytjd }tjd }W n$ tk
rh   tj }tj }Y nX y.tjtjd }tjtjd }tjd }W n( tk
r   tj }tj }d	}Y nX g }x$||kr|j	| |t
d
d }qW g }|dkrttjjtjtjd kjtjtdkjtjtdkjtjtdkjtjtdkjtjdkjtjj||j }	n|dkrtjjtjtjd kjtjtdkjtjtdkjtjtdkjtjtdkjtjj||j }	n\tjjtjtjd kjtjtdkjtjtdkjtjtdkjtjj||j }	xz|	D ]p}
i }t||
jd tjjtjttjtjkjtjjtjd kjtj |
jkj! }tjjt"jt#t"j$t#j%kjt#j&jt"jd kjt"jtdkjt"jtdkjt"j |
jkj! }|sd}n|j&}|rNy
|j}W n tk
rJ   |
j'}Y nX n|
j'}d	}d	}|
j(d
kst|
j(dkrtjjt)jt)j*t)j t)j+t)j,t)j-t)j.t)j/jt)jd kjt)j |
jkjt)jd kj0t)j1j2 j }x"|D ]}||j*j3d d }qW |
j(d
krd}nd}n0|
j(dkr$d}n|
j(dkr6d}n|
j(sBd}x|d D ]}|d  d!krN|r|d"krxd!}n|d# }n|d# }t4 }|
jj3d$|d%< |
j5|d&< |
j6 |d'< ||d(< |d) |d*< ||d+< ||d,< ||d-< |d  |d.< ||d/< |d  |d#  |d0< |d1 |d2< |d3 |d4< |d5 |d6< d!|d7< |d8 |d9< y<t7|d3 t8|
j9 d|d:< t7|d8 t8|
j9 d|d;< W n& tk
r   d!|d:< d!|d;< Y nX t:|
d<d	pd	|d<< t:|
d=d	pd	|d=< |j	| qNW x|d> D ]}|d> d!kr|r"|d"krd!}n|d? }n|d? }t4 }|
jj3d$|d%< |
j5|d&< |
j6 |d'< ||d(< |d@ |d*< ||d+< ||d,< ||d-< |d> |d.< ||d/< |d> |d?  |d0< |d1 |d2< |dA |d4< |d5 |d6< d!|d7< |d8 |d9< |
j9d kr d!|d:< d!|d;< n8t7|dA t8|
j9 d|d:< t7|d8 t8|
j9 d|d;< t:|
d<d	pHd	|d<< t:|
d=d	p^d	|d=< |j	| qW x4|dB D ]&}|dC |dD  d!k	r|r|d"krd!}nd
}nd
}t4 }|
jj3d$|d%< |
j5|d&< |
j6 |d'< ||d(< |dE |d*< ||d+< ||d,< ||d-< |dC |d.< ||d/< |dC d
 |d0< |d1 |d2< |dF |d4< |dG rh|dH |d6< nd!|d6< d!|d7< |dG r|dI |d9< y<t7|dF t8|
j9 d|d:< t7|dI t8|
j9 d|d;< W n  tk
r   t|
jY nX nj|dJ |d9< y<t7|dJ t8|
j9 d|d:< t7|dJ t8|
j9 d|d;< W n  tk
	rV   t|
jY nX t:|
d<d		phd	|d<< t:|
d=d		p~d	|d=< |j	| |dK |dL  d!kr|	r|d"k	rd!}nd
}nd
}t4 }|
jj3d$|d%< |
j5|d&< |
j6 |d'< ||d(< |dE |d*< ||d+< ||d,< ||d-< |dK |dL  |d.< ||d/< |dK |dL  d
 |d0< |d1 |d2< |dM |d4< |dG 
r|dN |d6< nd!|d6< d!|d7< |dG r|dO |d9< y<t7|dM t8|
j9 d|d:< t7|dO t8|
j9 d|d;< W n  tk
r   t|
jY nX nj|dP |d9< y<t7|dP t8|
j9 d|d:< t7|dP t8|
j9 d|d;< W n  tk
rn   t|
jY nX t:|
d<d	pd	|d<< t:|
d=d	pd	|d=< |j	| qW xh|dQ D ]Z}|dR dSkr|dT dUkr|dV |dW  d!kry"t7|dX |dY  |dZ  d}W n@ tk
rR } z"t;d[ t;|
j5 t|
j5W Y d d }~X nX t4 }|
jj3d$|d%< |
j5|d&< |
j6 |d'< ||d(< |d\ |d*< ||d+< ||d,< ||d-< |d] |d.< |d^ |d/< |d] |d^  |d0< |d1 |d2< ||d4< t7|dY d|d6< yt7|dZ d|d7< W n. tk
rB } zt|
j5W Y d d }~X nX t7|dX d|d9< y>t7|t8|
j9 d|d:< t7t7|dX dt8|
j9 d|d;< W n  tk
r   t|
jY nX t:|
d<d	pd	|d<< t:|
d=d	pd	|d=< |j	| |d_ d!krPt7|d` |da  |db  d}t4 }|
jj3d$|d%< |
j5|d&< |
j6 |d'< ||d(< |d\ |d*< ||d+< ||d,< ||d-< |d_ |d.< |d^ |d/< |d_ |d^  |d0< |d1 |d2< ||d4< t7|da d|d6< t7|db d|d7< t7|d` d|d9< t7|t8|
j9 d|d:< t7|d` t8|
j9 d|d;< t:|
d<d	p*d	|d<< t:|
d=d	p@d	|d=< |j	| |dc d!krt7|dd |de  |df  d}t4 }|
jj3d$|d%< |
j5|d&< |
j6 |d'< ||d(< |d\ |d*< ||d+< ||d,< ||d-< |dc |d.< |d^ |d/< |dc |d^  |d0< |d1 |d2< ||d4< t7|de d|d6< t7|df d|d7< t7|dd d|d9< t7|t8|
j9 d|d:< t7t7|dd dt8|
j9 d|d;< t:|
d<d	pd	|d<< t:|
d=d	pd	|d=< |j	| |dT dgkr|dV d!kr<t7|dh d}t4 }|
jj3d$|d%< |
j5|d&< |
j6 |d'< ||d(< |d\ |d*< ||d+< ||d,< ||d-< |dV |d.< |d^ |d/< |dV |d^  |d0< |d1 |d2< |di |d4< t7|dj d|d6< t7|dk d|d7< ||d9< y8t7|di t8|
j9 d|d:< t7|t8|
j9 d|d;< W n  tk
r   t|
jY nX t:|
d<d	pd	|d<< t:|
d=d	p,d	|d=< |j	| |dW d!krt7|dl d}t4 }|
jj3d$|d%< |
j5|d&< |
j6 |d'< ||d(< |d\ |d*< ||d+< ||d,< ||d-< |dW |d.< |d^ |d/< |dW |d^  |d0< |d1 |d2< |dm |d4< t7|dn d|d6< t7|do d|d7< ||d9< t7|dm t8|
j9 d|d:< t7|t8|
j9 d|d;< t:|
d<d	pdd	|d<< t:|
d=d	pzd	|d=< |j	| q|dR dpkr|dV |d_  d!kryt7|di |dq d!  d}W nF t<k
r   t7|di d d}Y n  tk
r   t|
jY nX t4 }|
jj3d$|d%< |
j5|d&< |
j6 |d'< ||d(< |d\ |d*< ||d+< ||d,< ||d-< |dV |d_  |d.< |d^ |d/< |dV |d_ |d^   |d0< |d1 |d2< |di |d4< y|dq |d6< W n t<k
r   d!|d6< Y nX d!|d7< ||d9< y8t7|di t8|
j9 d|d:< t7|t8|
j9 d|d;< W n  tk
r\   t|
jY nX t:|
d<d	pnd	|d<< t:|
d=d	pd	|d=< |j	| |dW |dc  d!krHyt7|dm |dr d!  d}W n( t<k
r   t7|dm d d}Y nX t4 }|
jj3d$|d%< |
j5|d&< |
j6 |d'< ||d(< |d\ |d*< ||d+< ||d,< ||d-< |dW |dc  |d.< |d^ |d/< |dW |dc |d^   |d0< |d1 |d2< |dm |d4< y|dr |d6< W n t<k
r   d!|d6< Y nX d!|d7< ||d9< t7|dm t8|
j9 d|d:< t7|t8|
j9 d|d;< t:|
d<d	p"d	|d<< t:|
d=d	p8d	|d=< |j	| |ds d!kr|d^ dt d
krnd
}n&|d^ dt d
krt7|d^ dt d
 }yt7|ds |du d!  d}W n( t<k
r   t7|ds d d}Y nX t4 }|
jj3d$|d%< |
j5|d&< |
j6 |d'< ||d(< |d\ |d*< ||d+< ||d,< ||d-< d
|d.< ||d/< d
| |d0< |d1 |d2< |ds |d4< y|du |d6< W n t<k
r   d!|d6< Y nX d!|d7< ||d9< t7|ds t8|
j9 d|d:< t7|t8|
j9 d|d;< t:|
d<d	pd	|d<< t:|
d=d	pd	|d=< |j	| qW x|dv D ]}t7|dw d}t4 }|
jj3d$|d%< |
j5|d&< |
j6 |d'< ||d(< |d\ dx dy |d*< ||d+< ||d,< ||d-< |d] |d.< |d^ |d/< |d] |d^  |d0< |d1 |d2< ||d4< t7|dz d|d6< t7|d{ d|d7< t7|d| d|d9< y>t7|t8|
j9 d|d:< t7t7|d| dt8|
j9 d|d;< W n  tk
rl   t|
jY nX |j	| t7|d} d}t4 }|
jj3d$|d%< |
j5|d&< |
j6 |d'< ||d(< |d\ dx d~ |d*< ||d+< ||d,< ||d-< |d] |d.< |d^ |d/< d!|d0< |d1 |d2< ||d4< t7|d d|d6< t7|d d|d7< t7|d d|d9< y>t7|t8|
j9 d|d:< t7t7|d dt8|
j9 d|d;< W n  tk
r   t|
jY nX |j	| q$W qPW tjjt)jtt)j tjkjt)j*t)j t)j+t)j,t)j-t)j.t)j/jt)jd kjtjd kjtjtdkj } g }!x| D ]}x|D ]}"|j*j3d|"j3dkrHt4 }#|j*j3d$|#d%< tjjtjtjd kjtj|j kj! }$tjjtjttjtjkjtjjtjd kjtj |j kj! }|ry
|j}%W n tk
r   |$j'}%Y nX n|$j'}%|$j5|#d&< |%|#d(< |$jj3d$|#d< tjjt=jt=jd kjt=j>|j.kj! }&|&d krd|#d< d	|#d< nR|&j?|#d< |&j@d krd}'d}(n"|&jAd krd}'d}(n|&j@}'|&jA}(|'d |( |#d< tjjtBjtBjC|j-kj! })|j/r(tjjtDjtDjE|j/kj! }*|*jF|#d< n
|)jG|#d< tHjItJjK|j,}+y|+j d d! d |#d< W n tk
r~   d|#d< Y nX t8|j+|#d< |$jj3d},tjL|,d}-|j*j3d}.tjL|.d}/|j*t
d
d }0|0j3d}1tjL|1d}2|-|/krd|#d< nD|-|2krd|#d< n0|-|/kr d|#d< n|-|2k r4d|#d< nd|#d< |!j	|# qHW q:W tj j3d}3d|3 d }4tMjN|4}5tMjO|}6|6jP|5ddd tQ|d!krtMjR|6d%d&d*d+gd.d0d:d;gdd}7|7jP|5d}8tMjR|6d%d*d+gd.d0d:d;gdd}9|9jP|5d}:tS|!dd d}!tMjO|!};|;jP|5ddd tQ|!d!k rVtMjR|;d%ddgdgdd}<|<jP|5d}=|5jT  tdd|4 idfS )NrC   z
Session ID)rD   rE   rF   i  rq   end_datebooking_type rR   )days1rI   	Cancelled	AbandonedComplimentary7769748C2TrH   zN/A   z%Y-%m-%d,PaidzIncomplete Payment      z
To InvoicezNot Paidguestspayment_guestsr   zDay Tripno_of_nightsz%d-%b-%yrK   rL   Booking_TyperN   rd   Z	Item_Namer-   Payment_StatusZTransaction_DatesNumberrO   z
Bed Nightsbooking_currency_namerQ   "payment_person_cost_after_discountZGross_Amountbase_vatVATZCatering_Levybase_amount_minus_vatZ
Net_AmountzGross_Base(KES)zNet_Base(KES)cuinvoicenumberticketvehiclesvehicle_no_of_nightsvehicle_charge_categoryvehicle_cost_after_discountinventory_bookingsinventory_booking_adultsinventory_booking_extra_adultsinventory_name,inventory_booking_adult_cost_after_discountsvatreport_adult_vatreport_adult_price_minus_vatinventory_booking_adult_costinventory_booking_children inventory_booking_extra_children,inventory_booking_child_cost_after_discountsreport_child_vatreport_child_price_minus_vatinventory_booking_child_costfacility_bookingsfacility_typeAccomodationaccomodation_typeStablesfacility_booking_adultsfacility_booking_childrenbase_fixed_minus_taxesbase_fixed_vatbase_fixed_caterzwhere error occurredfacility_nameno_of_roomsfacility_no_of_nightsfacility_booking_extra_adultsbase_extra_adult_minus_taxesbase_extra_adult_vatbase_extra_adult_caterfacility_booking_extra_childrenbase_extra_child_minus_taxesbase_extra_child_vatbase_extra_child_caterPelicanbase_adult_minus_taxesfacility_booking_adult_costbase_adult_vatbase_adult_caterbase_child_minus_taxesfacility_booking_child_costbase_child_vatbase_child_caterzCamping Sitesbase_adult_cost_vatbase_child_cost_vatfacility_booking_fixed_cost   base_fixed_cost_vatexternal_facility_bookingsexternal_facility_gross-ZExternalfixed_vat_externalfixed_catering_externalfixed_minus_taxes_externalcommission_grossZ
Commissionfixed_vat_commissionfixed_catering_commissionfixed_minus_taxes_commissionZ
Start_DatezQNVN-310119Z	ReferenceZCard_DetailsZ0000z...Payment_MethodrG   rP   ZPayment_CurrencyKESZPayment_AmountzCurrent DayZPayment_DayzPrevious Dayz
Future DayzPast DayZDefaultz%d-%m-%Y_%H:%M:%Szreports/gate/DailyReport_z.xlsxZSalesF)rT   rU   )rT   rV   rW   zSales Pivot(Book_Ref)zSales Pivot(Item_Name)c             S   s   | d S )Nr   r   )orderr   r   r   <lambda>0  s    z0generate_complete_entry_report.<locals>.<lambda>)keyZPaymentszPayments Pivot TablerX   z.https://bookings.olpejetaconservancy.org/docs/rY   r   r   r   )UrZ   r   r[   r   r   r   rv   GenerateDateFromStringgenerateDatery   r   r   r\   r]   r   rs   rt   ru   r   r   booking_check_in_datebetweenrx   r=   rz   r:   r^   r9   	school_idschool_public_idrc   school_namerr   r~   r!   r-   rp   r   r   booking_done_bypayment_statusr    transaction_datetransaction_totaltransaction_payment_currencytransaction_payment_methodtransaction_booking_public_idtransaction_payment_gatewayorder_by
created_atdescrw   r   r{   return_booking_typer   r   currency_selling_rate_at_timegetattrprintKeyErrorr   transaction_idmpesa_referencecard_first_fourcard_last_fourr.   payment_method_public_idr/   payment_gateway_public_idpayment_gateway_namepayment_method_namer   r   r   r   strptimer   r   r   r   lenr   sortedr   )>r   rF   start_date_requestend_date_requestrq   r   r   
date_rangerG   get_bookingssingle_bookingbooking_detailscheck_school_bookingZget_booking_gatepass_detaildestination_namer  Ztransaction_datesr  get_booking_transactionssingle_transactionr   r   return_datasingle_vehiclesingle_inventorysingle_facilityZgross_amounte
net_amountweekssingle_external_facilityZreturn_data_externalget_days_transactionstransaction_datasingle_datereturn_transactionget_booking_detailsZtransaction_byget_booking_paymentr  r  get_payment_methodget_gatewaycurrencyZbooking_revenue_day_strZbooking_revenue_dayZbooking_transaction_day_strZbooking_transaction_dayZprevious_dayZ$previous_day_booking_transaction_strZ previous_day_booking_transactionr   r   r   data_dfZbooking_ref_pivotZbooking_ref_pivot_dfZbooking_hospitality_pivotZbooking_hospitality_pivot_dftransaction_dfZtransactions_pivotZtransactions_pivot_dfr   r   r   generate_complete_entry_report   s   






























































































































































r=  z/reports/generalc        	      C   s$  dddddddddg} t tj| }|r<td|id	fS ytjd
 rTtjd
 }nd }W n tk
rr   d }Y nX |stjjtj	tj
tjd kj	tjtjd kj }n@tjjtj	tj|kj	tj
tjd kj	tjtjd kj }|sg }|jd td|idfS g }x|D ]}i }t||j |jj|d< |j
|d< |j|d< |j|d< |j|d< tjjtj	tjd kj	tj|jkj }|r|jdkrd|d< nd|d< n8|jdkrd|d< n"|jdkrd|d< n|jj|d< |d |d< |d |d< |j| qW td|idfS )Nrq   z
Start date)rD   rE   r   zEnd daterC   z
Session IDrF   i  r   z There are no records to display.rJ   i  r   Check_In_DateCheck_Out_DateZDone_ByZRef_CoderR   r   Statusz
To InvoicezChecked Outz
Checked Inr   rQ   
total_costBooking_TotalrG   rY   )rZ   r   r[   r   r   r   r\   r]   r   rs   r   booking_check_out_daterx   r   ry   r=   rz   b_typebooking_type_namer  r{   r5   rt   rr   r~   checked_out
checked_inb_statusbooking_status_name)	r   rF   r   r#  rJ   rG   singler*  check_to_invoicer   r   r   generate_generic_report  sh    












rL  z/reports/summaryc              C   s  dddddddddg} t tj| }|r<td|id	fS tjtjd }tjtjd }g }x$||kr|j| |td
d }qbW g }g }xj|D ]`}i }tj	j
tjtjd kjtjtj|jdkjtjtdkjtjtdkjtjj j }	|jd|d< t|	|d< g }
g }x|	D ]}i }t||j |
j|d  |d rvx |d D ]}|
j|d  q^W |d rx |d D ]}|
j|d  qW |jtd|j|d  q(W t|
|d< t||d< d|d< |jt| |j| qW t|t|ddfS )Nrq   z
Start date)rD   rE   r   zEnd daterC   z
Session IDrF   i  rR   )r   z%Y-%m-%dr   zNo-Showr   bookings_countguest_totalr   facility_booking_guestsr   inventory_booking_guestsz$162fface-f5f1-41de-913b-d2bb784dda3arA  r   booking_totalr   r:  )rG   grand_totalrY   )rZ   r   r[   r   r   r   ry   r   r   r\   r]   r   rs   rt   r   generateDateTimerw   ru   r   r  r  rx   r  r<   rz   r   r:  rU   )r   rF   rq   r   Z	date_listrG   rR  r4  r*  get_all_bookingsrN  rQ  rJ  booking_dataeach_facilityeach_inventoryr   r   r   generate_summary  sf    





rX  z/bookings/view/reportc           Q   C   s	  yt tjd } W n tk
r*   d} Y nX yt tjd }W n tk
rV   d}Y nX | dkrtj }|jd}tjj	t
jt
j|kjt
jj jttj }n.tjj	t
jt
jj jttj| |dj}|sg }|jd td	|id
fS g }g }x|D ]}|j|j qW tj }	y|	jtd|id}
W n6 tjjtjjtjj fk
rn } zW Y d d }~X nX x|D ]}i }|j!|d< |j"j#|d< |j$|d< |j%|d< |j&|d< |j'|d< |j(|	|d< |j)|	|d< tjj	t*j+t,t*j-t,j.kj/t,j0jt*j|j!kj1 }tjj	t2jt2j|j!kj1 }y2|rD|j0|d< n|rV|j3|d< n
|j4|d< W n  tk
r   |j4|d< Y nX yFtjj	t5jt5j|j!kjt5j6t7dkj1 }|j8|d< |j9|d< W n& t:k
r   d|d< d|d< Y nX |j;|d< i }t<||j! |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)< tjj	t=jt=j>d kjt=j|j!kj1 }|r|j>dkrd*|d+< d,j?t@jAd- |d.< nd/|d+< d,j?t@jAd0 |d.< n|jBdkrBd1|d+< d,j?t@jAd2 |d.< n|jCdkrnd3|d+< d4j?t@jAd5 |d.< nb|j>dkrd*|d+< d,j?t@jAd- |d.< n6|jDjEd6krd,j?t@jAd7 |d.< n|jDjEd8krd,j?t@jAd9 |d.< n|jDjEd:krd,j?t@jAd; |d.< n|jDjEd<kr,d,j?t@jAd= |d.< n|jDjEdkrPd,j?t@jAd> |d.< nt|jDjEd?krtd,j?t@jAd@ |d.< nP|jDjEdAkrd,j?t@jAdB |d.< n,|jDjEdCkrd,j?t@jAdD |d.< ndE|d.< |jDjE|d+< |j6|dF< |jr|jdGkrdH|dI< |j|dJ< nyBx<|
jF dK D ],}|dL |jkr|dM |dI< |j|dJ< qW W nr tGtHfk
r~ } zdN|dI< |j|dJ< W Y d d }~X n: t:tIfk
r } zdO|dI< |j|dJ< W Y d d }~X nX ndN|dI< |j|dJ< |j|dP< |jJ|dQ< tjj	tKjtKj>d kjtKj|j!kjttj1 }|rx|jL|dR< tM|jN}y |dS d |dS< |dT |dU< W n& tk
rt   d |dS< d |dU< Y nX nd |dR< d |dS< d |dU< |jOr"|jOj1 }|rtP|jQ}y|dM |dV< |dW |dX< W n> tk
r   |dY |dV< |dZ r d[|dX< ndN|dX< Y nX nd |dV< d |dX< nd |dV< d |dX< |jRdkrFd\|d]< |jRd^krZd_|d]< |jRd`krndA|d]< |jRdakrd/|d]< |jRsdb|d]< tjj	tSjtSj>d kjtSj|j!kj }|rdc|dd< nd|dd< tjj	t5jt5j|j!kjt5jj j1 }tjj	tTj+tUtUjVtTjWkjtUj|j!kj1 }|	r>|jX|de< ndf|de< |	rX|jY|dg< ndh|dg< |j| qxW tdK|id
fS d S )iNpagerR   items2   r   z%Y-%m-%dFz.There are currently no bookings in the system.rJ   rY   	users_ids)r[   rz   r   booking_type_idr   rC  r{   r   r   r  rH   email_addressphone_numberr   booking_ticketr   rN  r   vehicle_totalaccommodations
activitiesr   booking_currencyrA  rQ  tax_breakdownr   cateringcatering_levy	after_taxr   booking_statusz!background-color: {}; color: #fff	CANCELLEDbooking_colorz
To Invoice
TO_INVOICEzChecked OutCHECKED_OUTz
Checked Inz background-color:{}; color: #fff
CHECKED_INUnconfirmedUNCONFIRMED	Confirmed	CONFIRMEDzNo-ShowNO_SHOWr   	ABANDONEDUPDATEDDepositDEPOSITr   COMPLIMENTARY	Postponed	POSTPONEDz&background-color: #fff; color: #000000booking_status_idguestPublicsession_userrC   rG   	public_id	full_namezN/AzNetwork Errorr  
updated_atpartner_booking_refpartner_typenamepartner_namemember_namelevelmembership_levelcompany_name	corporate	Corporater   booking_payment_statusr   zIncomplete Paymentr   r   zNot PaidTcredit_note_issuedcountryzcountry not setrp   zdestination not set)Zintr   argsr   r   rv   rw   r   r\   r]   r   rs   r  r  rr   r  optionsr   r   rx   paginaterZ  ry   r   rC   r   Sessionpostget_user_from_aumra
exceptionsConnectionErrorTimeoutConnectTimeoutrz   rD  rE  r   r   rC  r{   return_facilitiesreturn_inventoryr:   r^   r9   r  r  rc   r  r~   r&   organisation_namer  r   ru   r   r^  r_  AttributeErrorr   r=   r5   rt   r   r   configrF  rG  rH  rI  r[   
IndexErrorr  UnboundLocalErrorr  r8   r  get_details_partner
partner_idmember_detailsget_details_member	member_idr  
CreditNoter-   r!   rp   r   r  r   )rY  rZ  rv   todayreturn_bookingsoutputrG   id_arrayeach_idrequest_sessionreturn_userconnection_errorrJ  r*  r&  check_org_bookingr6  booking_inforK  user
user_errornetwork_related_errorsget_partner_bookingpartner_infor%  member_infoget_credit_notesget_latest_booking_detailget_destinationr   r   r   view_all_bookings_report8  s   






























r  z/revenue/chartc        ,         s  yt jd } t jd }W n$ tk
r<   tj } tj }Y nX tjjtj	tj
d kj	tjtdkj	tjtdkj	tjtdkj	tjj| |j }g }x|D ]|}tjjtjttjtjkjttjtjkjtjtjtjtjtjtjtjtjtj tj!tj"tj#tj$tj%tj&tj'tj(j	tjtdkj	tjdkj	tj
d kj	tj)|j*kj	tjtdkj+t,t-j }t.j/t0d|jj1d	id
}t2|j d d d }d|d  }xn|D ]d}	i }
|	j|
d< |	j'|
d< |	j|
d< |	j|
d< |	j|
d< |	j&|
d< |	j|
d< |	j |
d< |	j|	j  |
d< t2|	jt2|	j  t2|	j }t3t2dt2|	jd  | d}t3|t2|j4 d|
d< t2dt2|	jd  t2|	jt2|	j   t2|	j }t3|d}t3|| d}t3|t2|j4 d|
d< || }||
d< t3|| d}t3|t2|j4 d|
d< |j5|
 qW qW tjjtjtjtjj	tj
d kj }g }g }g }g }d}d}d}d}d}d}g }x|D ] i } j|d<  jj6 |d<  fdd|D }t7|dkrd} d}!d}"d}#d}$d}%i }&i }'i }(i })xV|D ]N}	| |	d  } |!|	d  }!|"|	d  }"|#|	d  }#|$|	d  }$|%|	d  }%qW  jj6 |&d< | |&d <  jj6 |)d< |!|)d <  jj6 |'d< |#|'d!< |j5|& |j5|' |j5|) |%dkrЈ jj6 |(d< |%|(d!< |j5|(  jj6 t8|"t8|#t8|$t8|%t8|!t8| d"}*|j5|* ||" }||# }||$ }||% }||! }||  }qW |||||t8|t8|t8|t8|||d#d$}+t9d|+id%fS )&Nrq   r   rI   r   r   rH   r   
query_datez%Y-%m-%d)r[   rG   vat_raterR   rS   r`   gatepass_guest_public_idrd   
guest_typeno_of_guestsgatepass_payment_scheduler   r   
bed_nightsr   
gross_basenet_basegross_discountbase_discount_amount_minus_vatc                s   g | ]}|d   j kr|qS )r`   )r`   ).0x)rJ  r   r   
<listcomp>	  s    z"ResidencyChart.<locals>.<listcomp>r  numberamount)r  total_gross_basetotal_net_basetotal_discount_grosstotal_discount_nettotal_bed_nightstotal_count)total_residency_grosstotal_residency_nettotal_residency_discount_grosstotal_residency_discount_nettotal_bednightsr  )residency_countresidency_bed_nightsresidency_revenueresidency_discountsZresidency_tabularZresidency_totalsrY   ):r   r[   r   r   rv   r   r\   r]   r   rs   rt   ru   r   r   r  rx   r"   r^   r   r_   r`   r!   ra   rb   rc   rd   residency_proofre   rf   rg   rh   ri   rj   rk   rl   rm   rn   ro   r  r  rp   rr   rz   r  r   r   r   r  get_vat_for_daterw   r   r   r  ry   titler  r  r   ),rq   r   r#  guest_arraybookingget_all_gatepass_guestsget_latest_vatr  vat_calculation_amount
each_guestr   r   r   base_with_discountrounded_baser   discountr  return_paymentsr  r  r  r  r  r  r  r  r  r  total_tabular_datar*  Zcurrent_residency_guestZtotal_guest_countr  r  r  total_gross_discounttotal_net_discountZresidency_count_objZresidency_revenue_objZresidency_discount_objZresidency_bed_night_objtabular_dataresidency_objectr   )rJ  r   ResidencyChart}	  s*   










4








r  z/vehicle-revenue/chartc        ,         s.  yt jd } t jd }W n$ tk
r<   tj } tj }Y nX tjjtj	tj
d kj	tjtdkj	tjtdkj	tjtdkj	tjj| |j }g }xd|D ]Z}tjjtjttjtjkjttjtjkjtjtjtjtjtjtjtjtjtj tj!tj"tjtj#tj$tj%tj&j	tj
d kj	tjtdkj	tjdkj	tj
d kj	tj'|j(kj	tjtdkj)t*t+j }t,j-t.d|jj/d	id
}t0|j d d d }d|d  }xD|D ]:}	i }
|	j|
d< |	j|
d< |	j|
d< t1|	jt1|	j |
d< t0|	jt0|	j t0|	j }t2t0dt0|	jd  | d}t2|t0|j3 d|
d< t0dt0|	jd  t0|	jt0|	j  t0|	j }t2|d}t2|| d}t2|t0|j3 d|
d< || }||
d< t2|| d}t2|t0|j3 d|
d< |j4|
 qW qW tjjtj	tj
d kj5tj6j7 j)t*t+j }g }g }g }g }d}d}d}d}d}d}g }x|D ]  fdd|D }t8|dkrld}d} d}!d}"d}#d}$i }%i }&i }'i }(xV|D ]N})||)d  }| |)d  } |!|)d  }!|"|)d  }"|#|)d  }#|$|)d  }$qW  j#|%d< ||%d<  j#|&d< | |&d<  j#|'d< |"|'d< |j4|% |j4|& |j4|' |$dkr j#|(d< |$|(d< |j4|(  j#j9 t1|!t1|"t1|#t1|$t1| t1|d}*|j4|* ||! }||" }||# }||$ }||  }|| }qlW |||||||||||dd}+t:d|+id fS )!Nrq   r   rI   r   r   rH   r   r  z%Y-%m-%d)r[   rG   r  rR   rS   vehicle_charge_public_idvehicle_countr   vehicle_bed_nightsr   Zgross_base_kesZnet_base_kesr  r  c                s   g | ]}|d   j kr|qS )r  )r  )r  r  )rJ  r   r   r  
  s    z VehicleChart.<locals>.<listcomp>r  r  r  )r  r  r  r  r  r  r  )total_vehicle_grosstotal_vehicle_nettotal_vehicle_discount_grosstotal_vehicle_discount_netr  r  )r  vehicle_revenuevehicle_discountsr  Zvehicle_tabularZvehicle_totalsrY   );r   r[   r   r   rv   r   r\   r]   r   rs   rt   ru   r   r   r  rx   r%   r^   r!   ra   rb   r'   gatepass_vehicle_typer  rc   gatepass_vehicle_countgatepass_cost_per_vehiclegatepass_vehicle_public_idgatepass_vehicle_no_of_nights gatepass_vehicle_discount_reasongatepass_vehicle_currencygatepass_vehicle_discount_rate!gatepass_vehicle_currency_at_timegatepass_vehicle_cost_at_timegatepass_vehicle_rate_at_timer   vehicle_charge_income_codevehicle_charge_outlet_codevehicle_charge_dept_coderr   rz   r  r   r   r   r  r  rw   r   r  r   r  ry   r  vehicle_charge_idascr  r  r   ),rq   r   r#  vehicle_arrayr  get_all_gatepass_vehiclesr  r  r  each_vehiclereturn_vehicle_datar   r   r  r  r   r  r  Zget_vehicle_charger  r  r  r  r  r  r  r  r  r  r  Zcurrent_vehicleZtotal_vehicle_countr  r  r  r  r  Zvehicle_count_objZvehicle_bed_night_objZvehicle_revenue_objvehicle_discount_objr+  r  Zvehicle_objectr   )rJ  r   VehicleChartV
  s$   




4











r  z/inventory/chartc        Z   &      s
  yt jd } t jd }W n$ tk
r<   tj } tj }Y nX tjjtj	tj
d kj	tjtdkj	tjtdkj	tjtdkj	tjj| |j }g }g }x,|D ]"}tjjtj	tj
d kj	tjtdkj	tj|jkjttj }tjtd|jjdid	}t|j d
 d d }d|d  }	x|D ]}
i }tjtj|
j}|j d
 d d |d< |
j|d< |d |kr|j|d  t|
j t|
j!|
j"  }dt|
j#d  | }dt|
j$d  | }|
j$dkr|}n|}dt|
j#d  t|
j |
j!|
j"   }dt|
j$d  | }|}||	 }|
j!|
j" |d< |d d |d< |j d
 d d r||d< y4t%|t|j& d|d< t%|t|j& d|d< W n  tk
r   t|jY nX n^||d< y4t%|t|j& d|d< t%|t|j& d|d< W n  tk
r,   t|jY nX || }t%|t|j& d|d< t%||	 d}t%|t|j& d|d< |
j'|
j( |d< |
j'|
j( d |d< t|
j)t|
j'|
j(  }dt|
j#d  | }dt|
j$d  | }|
j$dkr|}n|}dt|
j#d  t|
j)|
j'|
j(   }dt|
j$d  | }|}||	 }||	 }|j d
 d d r||d< y4t%|t|j& d|d< t%|t|j& d|d< W n  tk
r   t|jY nX n^||d< y4t%|t|j& d|d< t%|t|j& d|d< W n  tk
r   t|jY nX || }t%|t|j& d|d < t%||	 d}t%|t|j& d|d!< |
j!|
j" |
j'|
j(  |d"< |d" d |d#< |j d
 d d r|| |d$< y<t%|| t|j& d|d%< t%|| t|j& d|d&< W n  tk
r
   t|jY nX nj|| |d$< y<t%|| t|j& d|d'< t%|| t|j& d|d(< W n  tk
rv   t|jY nX || } t%| t|j& d|d)< t%| |	 d}t%|t|j& d|d*< |j| q:W qW g }!g }"g }#g }$g }%g }&g }'g }(g })g }*g }+g },g }-g }.d}/d}0d}1d}2d}3d}4d}5d}6d}7g }8x\|D ]R i }9 fd+d,|D }:t*|:dkr:|:d d j+ };|;|9d< d}<d}=d}1d}>d}?d}@d}Ad}Bd}Cd}Dd}Ed}Fd}Gd}Hi }Ii }Ji }Ki }Li }Mi }Ni }Oi }Pi }Qi }Ri }Si }Ti }Ui }Vx|:D ]}W|<|Wd  }<|=|Wd  }=|1|Wd"  }1|@|Wd#  }@|>|Wd  }>|?|Wd  }?|A|Wd  }A|B|Wd  }B|C|Wd  }C|D|Wd  }D|E|Wd%  }E|F|Wd&  }F|G|Wd)  }G|H|Wd*  }HqW |;|Id< |<|Id-< |;|Jd< |=|Jd-< |;|Kd< |1|Kd-< |;|Ld< |>|Ld-< |;|Md< |?|Md-< |;|Nd< |@|Nd-< |;|Od< |A|Od.< |;|Rd< |B|Rd.< |;|Pd< |C|Pd.< |;|Sd< |D|Sd.< |;|Qd< |E|Qd.< |;|Td< t,|F|Td.< |!j|I |"j|J |#j|K |$j|L |%j|M |&j|N |'j|O |(j|R |)j|P |*j|S |+j|Q |,j|T |Hdk
r|;|Ud< |G|Ud.< |;|Vd< |H|Vd.< |-j|U |.j|V |;j+ t,|Et,|Ft,|Gt,|Ht,|@t,|1|<|=d/	}X|8j|X |2|E }2|3|F }3|4|G }4|5|H }5|6|@ }6|7|1 }7q:W |!|"|#|$|%|&|'|(|)|*|+|,|-|.|8|2|3|4|5|6|7d0d1}Yt-d
|Yid2fS )3Nrq   r   rI   r   r   rH   r  z%Y-%m-%d)r[   rG   r   r  rR   rS   r  r   inventory_idZadults_numberZadult_bed_nightsr   Zadult_net_amountr   zadult_gross_base(KES)zadult_net_base(KES)Zgross_base_adult_discount_kesZnet_base_adult_discount_keschildren_numberZchildren_bed_nightsZchild_net_amountzchild_gross_base(KES)zchild_net_base(KES)Zgross_base_child_discount_kesZnet_base_child_discount_kesZtotal_numberr  r/  zgross_base(KES)znet_base(KES)r  r  Zgross_base_discount_kesZnet_base_discount_kesc                s   g | ]}|d   kr|qS )r  r   )r  r  )rJ  r   r   r  .  s    z"InventoryChart.<locals>.<listcomp>r  r  )	r  r  r  r  r  r  r  adult_countchild_count)total_inventory_grosstotal_inventory_nettotal_inventory_discount_grosstotal_inventory_discount_netr  r  )r  r  inventory_countadult_bed_nightchild_bed_nightinventory_bed_nightadult_gross_baseadult_net_basechild_gross_basechild_net_baser  r  discount_gross_basediscount_net_baseinventory_tabular_dataZinventory_totalsrY   ).r   r[   r   r   rv   r   r\   r]   r   rs   rt   ru   r   r   r  rx   r   rr   rz   r  r   r   r   r  r  rw   r   r   get_inventory_detailsr   r  ry   inventory_cost_per_adultr   r   inventory_discount_rateinventory_commission_rater   r  r   r   inventory_cost_per_childr  r  r  r   )Zrq   r   r#  inventory_arrayZunique_inventoryr  r   r  r  r  r,  inventory_datar  adult_total_without_discountadult_total_with_discount_rate!adult_total_with_special_discountadult_total!report_adult_with_normal_discount"report_adult_with_special_discountreport_adult_with_discountr   adult_discount_amountr  child_total_without_discountchild_total_with_discount_rate!child_total_with_special_discountchild_total!report_child_with_normal_discount"report_child_with_special_discountreport_child_with_discountr   child_discount_amountdiscount_amountr  r  r  r  r  r  r  r  r  r  r  r  r  r  Ztotal_inventory_adult_countZtotal_inventory_child_countZtotal_inventory_countr  r  r  r  r  r  r  r*  Zcurrent_inventory_bookingsr   Ztotal_adult_countZtotal_child_countZtotal_adult_bed_nightsZtotal_child_bed_nightZtotal_inventory_bed_nightsZtotal_adult_gross_baseZtotal_adult_net_baseZtotal_child_gross_baseZtotal_child_net_baser  r  r  r  Zinventory_adult_count_objZinventory_child_count_objZinventory_count_objZinventory_adult_bed_nights_objZinventory_child_bed_nights_objZinventory_bed_nights_objZinventory_gross_adult_objZinventory_gross_child_objZinventory_gross_objZinventory_net_adult_objZinventory_net_child_objZinventory_net_objZinventory_gross_discount_objZinventory_net_discount_objrW  r  Zinventory_objectr   )rJ  r   InventoryChart"  s   

























r7  z/bookings-revenue/chartc        4      C   s  yt jd } t jd }W n$ tk
r<   tj } tj }Y nX g }g }g }tjjtj	tj
d kj	tjtdkj	tjtdkj	tjtdkj	tjtdkj	tjdkj	tjt jd kj	tjt jd kj }d}d}d}d}	d}
d}d}d}i }i }i }i }i }i }i }i }d}g }x|D ]v}i }|j}|j| yt|| tt jd	 |d
 t|d d|j|jjd}tt jd	 |d
 t|d d|j|jjd}|t| }|t| }tt jd	 |d
 t|d d|j|jjd}tt jd	 |d
 t|d d|j|jjd}|t| }|	t| }	tt jd	 |d
 t|d d|j|jjd}tt jd	 |d
 t|d d|j|jjd} |
t| }
|t|  }tt jd	 |d
 t|d d|j|jjd}!tt jd	 |d
 t|d d|j|jjd}"|t|! }|t|" }tt jd	 |d
 t|d d|j|jjd}#||# }W nD tk
r }$ z&t|$ tj }%tt|$|%ddfS d }$~$X nX q&W |dkrd|d< ||d< |j| |dkrd|d< ||d< |j| |dkrd|d< ||d< |j| |	dkr2d|d< |	|d< |j| |
dkrVd|d< |
|d< |j| |dkrzd|d< ||d< |j| |dkrd|d< ||d< |j| |dkrd|d< ||d< |j| tjt jd }&tjt jd }'|'tdd }'tjjt j!tt j"tjkj#t j"t j$t j%t j&t j't j(t j)t j*t j+t j,tjtj-tj.tj/tj0tjtjtj1tjj	t j
d kj	t j,|&kj	t j,|'k j	tj
d kj	tjtdkj }(g })d}*d}+d},i }-i }.x|(D ]z}/|/j'dkrnf|/j'd krnX|/j&}0t|/j$}1tt jd	 |0|1d|/j*|/jjd}2|/j|kr&|*|2 }*n|+|2 }+|,|2 },qW d!|-d< |*|-d< d"|.d< |+|.d< |)j|- |)j|. |g|,g|||)d#}3|j|3 td$|3id%fS )&Nrq   r   rI   r   r   r   r   r   r:  booking_currency_idrN  rR   z%Y-%m-%dguest_discountra  vehicle_discountinventory_totalinventory_discountfacility_totalfacility_discountrA  )errortracei  	Residencyr  r  r'   ZActivityr   )r   z$746bb582-89ed-4c83-ac3c-f25d9d60b266z$37da2c36-59d2-4bf5-916e-f1c7a1596fc0zCurrent Revenue TransactionszOther Transactions)Zbookings_totalr  bookings_revenueZbookings_discountsr3  rG   rY   )2r   r[   r   r   rv   r   r\   r]   r   rs   rt   ru   r   r   r   rx   rz   ry   viewBookingsTotalr?   r   r  r  rw   r  	traceback
format_excr   strr   r   r   r    r^   rr   rc   r  transaction_total_currencyr	  r
  r  $payment_currency_buying_rate_at_time%payment_currency_selling_rate_at_timer  r  r{   r   r:  r  currency_buying_rate_at_time)4r   r!  r  rB  Zbookings_discountr#  	guest_sumr9  vehicle_sumr:  inventory_sumr<  facility_sumr>  Zguest_sum_objZguest_discount_objZvehicle_sum_objr
  Zinventory_sum_objZinventory_discount_objZfacility_sum_objZfacility_discount_objrQ  Zbooking_id_arrr$  r%  rr   Ztotal_guest_with_discountZtotal_guest_discountZtotal_vehicle_with_discountZtotal_vehicle_discountZtotal_inventory_with_discountZtotal_inventory_discountZtotal_facility_with_discountZtotal_facility_discountZtotal_booking_amountr.  r@  rq   r   select_transactionsr3  Zcurrent_period_transactionZother_transactionsZtotal_transactionsZcurrent_transaction_objZother_transaction_objr)  transaction_currencyr  Ztotal_transacted_amountr  r   r   r   BookingsChart  s   


(






















rQ  z/transactions/chartc              C   sX  yt jd } t jd }W n$ tk
r<   tj } tj }Y nX tj| d}|tdd } | jd} tj|d}|tdd }|jd}tj	j
tjttjtjkjtjtjtjtjtjtjtjtjtjtjtjtjtjtjjtjd kjtjd kjtjtdkjtj| kjtj|k j  }g }t!t"}x|D ]}tj	j
t#jt#j$|jkj% }|jrtj	j
t&jt&j'|jkj% }	|	j(}
n|j)}
t*d|j|j|j|j|j}|
|kr||
 d	 | ||
 d	< ni }|
|d
< ||d	< |||
< q4W d}xH|D ]@}i }|| }|d
 |d< |d	 |d< ||d  }|j+| qW |d|id}t,d|idfS )Nrq   r   z%Y-%m-%dr   )r   rR   r   z$162fface-f5f1-41de-913b-d2bb784dda3abase_amountpayment_methodr  r  r  )r3  r  rG   rY   )-r   r[   r   r   rv   r  r   rw   r   r\   r]   r    r^   r   rr   rz   rc   r  r  r  r  r	  r
  rH  rI  r  r:  r  rs   rt   ru   r   rx   r@   listr.   r  r~   r/   r  r  r  r?   ry   r   )rq   r   start_date_objend_date_objr2  r3  Ztransaction_method_objr)  r8  r9  rS  rR  r5  totalrG   Ztransaction_objZdata1Z
res_objectr   r   r   TransactionsChart  sh    





rX  z/reports/inspection-reportc              C   s  dddg} t tj| }|r,td|idfS ytjd }tjd }W n& tk
rj   tj jd }}Y nX ytj	|}tj	|}W n  t
k
r   tj  }}Y nX ||k rg }|jd	 td
|idfS |tdd tdd }g }tjjd}tjjtjtjd ktjdktjj||jtt}	|rP|dkrP|	jtjdkj }
n.|rv|dkrv|	jtjd kj }
n|	j }
x|
D ]}tjjtjtj|jkj }|j rt!jt"j#|j }|j d d |j d  }nd }|j$dkr0tjjt%j&t't%j(t'j)kj*t'j+jt%j|jkj }|j+}n8|j$dkrbtjjt,jt,j|jkj }|j-}n|j.}tjjt/jt/j|jkj }d}d}d}xD|D ]<}|j0}|j1}|j2}||d 7 }||d 7 }||d 7 }qW t3 }|jjd|d< ||d< ||d< ||d< ||d< |j4|d< |j5|d < |jdkr2d!nd"|d#< ||d$< |j| qW tj jd%}d&| d' }t6j7|}t6j8|}|j9|d(d)d* |j:  td+d,| id-fS ).NrC   z
Session ID)rD   rE   rF   i  rq   r   z%Y-%m-%dz/The End Date cannot come before the Start Date.rJ   rR   )r   )secondsis_alertr   truer   r|   rM   r}   8D7E6504C3237489r   r   z%d-%b-%yrK   zClient NamezDriver NamezDriver Phone NumberzVehicle RegistrationzBooking RefzInspection NoteTrueFalsezIs AlertzInspected Byz%d-%m-%Y_%H-%M-%Sz%reports/gate/InspectedBookingsReport_z.xlsxzInspected BookingsF)rT   rX   z.https://bookings.olpejetaconservancy.org/docs/rY   );rZ   r   r[   r   r  r   rv   rw   r   r   
ValueErrorry   r   r   r   r\   r]   r4   rs   rt   ranger_noter  r  r  r   r   alertrx   r   rz   rr   r~   rC   r   get_single_userr   r   r:   r^   r9   r  r  rc   r  r&   r  r  r2   check_vehicle_regcheck_vehicle_drivercheck_vehicle_phone_numberr   r{   noter   r   r   r   r   )r   rF   r   r!  rq   r   rJ   rG   rZ  r]   Zget_inspected_bookingsZinspected_bookingr   get_userr~  r&  r  r  check_in_vehicleZall_vehiclesZall_driversZall_driver_phone_numbervehiclevehicle_regdriver_nameZdriver_phone_numberr*  r   r   r   r;  r   r   r   #generate_complete_inspection_report2  s    








rm  )flaskr   r   r   r   r   collectionsr   osmathr   uuidpdfkitsysrD  pandasr   routesr   r	   r
   r   r   r   r   r   r   routes.bookings_urlsr   r   routes.seed_functionsr   r   r   r   database.booking_typesr   database.bookingsr   database.booking_guestsr   database.booking_statusr   database.booking_paymentsr   database.facilityr   database.inventoryr   database.booking_detailsr   database.mandatory_paymentsr   database.transactionr    database.gatepassr!   database.gatepass_guestsr"   database.gatepass_detailsr#   database.booking_guest_typesr$   database.gatepass_vehiclesr%   database.groupr&   database.vehicler'   database.salesforcer(   database.sunr)   database.modifyr*   database.gater+   database.vehicle_detailsr,   database.destinationr-   database.payment_methodsr.   database.payment_gatewaysr/   database.booking_ticketsr0   database.memberr1   database.check_in_vehiclesr2   database.contact_listr3   database.bookings_notesr4   database.invoicer5   !database.mandatory_payment_pricesr6   database.booking_activity_logr7   database.partnerr8   database.schoolr9   database.school_bookingr:   	variablesfunctions.validationfunctions.date_operatorsfunctions.booking_snippetsr<   r=   
sqlalchemyr>   routes.print_urlsr?   r@   router   r=  rL  rX  r  r  r  r7  rQ  rX  rm  r   r   r   r   <module>   s    ~             eTN  G Z M   P wM