3
؀hP                 @   s   d dl mZmZmZmZmZmZmZmZ d dl	m	Z	m
Z
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m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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lGT d dlHT d dlIT d dlJZKd d lLmMZM d!d" ZNejOd#d$gd%d&d' ZPdS )(    )Flaskjsonifyrender_templateurl_forrequestredirectjsonmake_response)datetime	timedeltadateN)app)db)BookingTypeSeedBookingStatusSeedMandatoryPaymentsSeed)BookingGuestTypesSeed)BookingType)Booking)BookingGuest)BookingPayment)PaymentMethod)BookingStatus)Facility)	Inventory)Detail)	Mandatory)Transaction)Gatepass)GatepassGuest)GatepassDetail)	GuestType)GatepassVehicle)Vehicle)SalesforceData)Sun)Modify)Gate)VehicleDetail)Destination)*)OrderedDictc             C   s   | j j  d S )N)sessionclose)self r/   0/booking_service/app/routes/bookings_csv_urls.pyr-   ,   s    r-   z/generate/reports/csvPOST)methodsc        ?      C   s  t jd } t jd }g }g }g }g }g }g }g }g }	g }
g }| dksL|dkrg }| dkrb|jd |dkrt|jd d|i}tt|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t
jt
jt
j tj!tj"tj#j$tj%d kj$tj| kj$tj|kj& }x|D ]}t' }|jj(d	|d
< |jj(d	|d< |j|d< |jr|jnd|d< dj)|j|j|d< |j|d< |j|d< |j|d< |j |d< tjjt*j+j,|j#dj- \}||d< t.j/t0j)|j}|j d d d |d< |j"d kr:t1t1|j"d nd|d< |j!d krXt1|j!nd|d< |j"d krvt1|j"nd|d< |j"d krt1t1|j"t1t1|j"d  nd|d< |j| |j2 d krt' }|jj(d	|d!< |jj(d	|d"< |j|d< t.j/t0j)|j}|j d d d |d< |j"d krBt1t1|j"d nd|d< |j!d kr`t1|j!nd|d< |j"d kr~t1|j"nd|d< |j"d krt1t1|j"t1t1|j"d  nd|d< |j| qD|j2 d#krt' }|jj(d	|d!< |jj(d	|d"< |j|d< t.j/t0j)|j}|j d d d |d< |j"d krNt3t3|j"d nd|d< |j!d krlt1|j!nd|d< |j"d krt1|j"nd|d< |j"d krt1t1|j"t1t1|j"d  nd|d< |j| nt' }|jj(d	|d!< |jj(d	|d"< |j|d< t.j/t0j)|j}|j d d d |d< |j"d krJt3t3|j"d nd|d< |j!d krht1|j!nd|d< |j"d krt1|j"nd|d< |j"d krt1t1|j"t1t1|j"d  nd|d< |j| qDW tjjt4j$t4j5| kj$t4j5|kj$t4j%d kj& }x|D ]}t' }t.j/t6j)|j7}y|j5j(d	|d$< |j d d d% |d&< |j8|d'< |j9|d(< |j:|d)< t.j/t0j)|j;}|j<|d*< |j d d d |d+< d,|d< t1|j=|d-< t1|j=t1dt1t1|j>t3|j: t3|j9t1|j?    |d< t1|j=t1t1|j>t3|j: t3|j9t3|j?   |d.< t1|j=t1t1|j>t3|j: t3|j9t3|j?   t1t1|j>t3|j: t3|j9t3|j?   |d< |j| W n t@k
r   Y nX qW tjjtAj	tBtAjCtBjDkj	tEtAjCtEjDkj	tFtAjCtFjDkjtAjGtAjHtAjItAjJtAjKtEjtEjtFjLtFjMtBjNtBjOtBjPtBjQj$tAjR| kj$tAjS|kj$tAj%d kj& }x|D ]}t' }|jKj(d	|d/< |jH|d0< |jQ|d1< t3|jNdkr|jQnt3|jN|jQ |d2< t1|jO|d3< |jL|d4< t1|jM|d5< |jJ	rHy.tjjtTjUj$tTjV|jJkj- \} | |d6< W n t@k
	rD   d |d6< Y nX nd |d6< t.j/t0j)|jI}|j d d d |d+< t1t1t1|jOt3|jNt1|jP  t3|jLt1|jM  t3|jQ }!|!|d7< t1t1t1|jOt3|jNt1|jP  t3|jLt1|jM  t3|jQ d8 }"|"|d9< t1|!|" |d:< |j| qpW tjjtWj$tWjX| ktWjY|kj$tWj%d kjZtWjXj[ j& }#x~|#D ]t}$t' }%t.j/t\j)|$j]}&y<|$j|%d;< |$jXj(d	|%d<< |$jYj(d	|%d=< |$j^|%d>< d|&j k
r|&j d d d% nd?|%d@< |$j_|%dA< t3|$j`|%d1< t1|$ja|%dB< t1|$jb|%dC< t1|$jc|%dD< t1|$jd|%dE< t1|$je|%dF< t1|$jf|%dG< t1|$jg|%dH< t.j/t0j)|jI}|j d d d |%dI< d,|%dJ< t1|$jh|%dK< |$jY|$jX jidLkr`t3|$jbdkst3|$jddkr$t1t3|$jat1|$je t3|$jct1|$je  t1|$jh |%dM< n:t1t3|$jat1|$jb t3|$jct1|$jd  t1|$jh |%dM< nj|$jY|$jX jidL }'tj|'t1krtk|'}'t1t3|$jat1|$jb |' t3|$jct1|$jd |'  t1|$jh |%dM< |j|% W n t@k
r   Y nX 
qzW | |dN}(t.j/tljmdO })|)jndPkrx|)j D ]}*t' }+|*d$ |+d$< |*dQ |+dQ< |*dR |+dR< |*dS |+dS< |*dT |+dT< |*dU |+dU< |*dV |+dV< |*dW |+dW< |*dX |+dX< |*dY |+dY< |	j|+ q$W ng }	t.jotljmdZ |(d[},|,jndPkr>xd|,j D ]R}-t' }.|-d$ |.d$< |-d\ |.d\< |-d] |.d]< |-d |.d< |-d^ |.d^< |
j|. qW ng }
t.jotljmd_ |(d[}/|/jndPkrx||/j D ]j}0t' }1|0d$ |1d$< |0d` |1d`< |0d |1da< |0db |1dc< |0d |1dd< |0d |1de< |0df |1dg< |j|1 qlW ng }dht jkrttpjqditr|  dj tr| dk }2tpjs|}3|3jt|2dldmdn tpjs|}4|4jt|2dodmdn tpjs|}5|5jt|2dpdmdn tpjs|}6|6jt|2dqdmdn tpjs|}7|7jt|2drdmdn tpjs|}8|8jt|2dsdmdn tpjs|}9|9jt|2dtdmdn tpjs|	}:|:jt|2dudmdn tpjs|
};|;jt|2dvdmdn tpjs|}<|<jt|2dwdmdn |2ju  dxtljmdy  dz tr|  dj tr| dk j)tljmdy }=|||||||=d{}>tt|>dPfS ||||||d|}>tt|>dPfS d S )}N
start_dateend_date  zStart Date is missingzEnd Date is missingmessagesi  z%Y-%m-%dcheck_in	check_outbooking_ref_codezN/ATicketz{} {}	full_nameemailphone_numbercitycountry)payment_method_public_idpayment_methoddatar   currency_namecurrencyg{Gz?Zvat_tax_amountZinvoice_amountamount_paidnetZmpesaZCheck_InZ	Check_Outzcredit cardDatenameinventory_namecodeadultschildrenZMode_of_PaymentZpurchase_currencyKESrateZtotal_revenueZCheck_In_DateZGatepass_Ref_CodeZNo_Of_NightsZ
Bed_NightsZDiscountZVehicle_CountZVehicle_Costr)   ZTotal_Revenueg(\?ZVATZNetZ
Booking_IdZFacility_Booking_Check_In_DateZFacility_Booking_Check_Out_DateZFacility_CodezNot AvailableZFacility_NameZFacility_Analysis_CodeZFacility_Booking_AdultsZFacility_Cost_Per_AdultZFacility_Booking_ChildrenZFacility_Cost_Per_ChildZFacility_Fixed_CostZFacility_Discount_RateZFacility_Commission_RateZFacility_Purchase_CurrencyZAnalysis_CurrencyZRate   ZTotal_Amount)r3   r4   MEMBERSHIP_SERVICE   ZMemberIDZFullNameZGenderdobZIDNumberEmailPhoneNumberr   ZPriceVOLUNTEER_SERVICE)r   programZid_alien_numbercostDONATION_SERVICEZDonation_IdZPaymentModeZpayment_optionsZCauseZCountryZCurrencyamountAmountZcsvzreports/OPCSalesReporttoz.xlsxzBookings DataF)indexzGatepass DatazInventory DatazFacility DatazMpesa SummaryzCredit Card SummaryzBank Transfer SummaryzMembership SummaryzVolunteer Program SummaryzDonations Summaryzhttps://SERVERz/docs/reports/OPCSalesReport)all_bookingsinventory_bookingsgatepass_bookingsfacility_bookingsmpesa_summarycredit_card_arrayurl)r_   r`   ra   rc   rb   rd   )vr   r   appendr	   r   r   r,   queryr   joinr   booking_public_id
booking_idr   add_columnsbooking_typebooking_check_in_dateticketbooking_check_out_dateactual_booking_check_in_dateactual_booking_check_out_dater:   
session_id
created_atstatuspayment_statusrE   
first_name	last_nameemail_addressr>   r?   r@   booking_amountrF   rB   filterdeletion_markerallr+   strftimeformatr   payment_method_name	filter_byfirstrequestsgetget_currencyfloatlowerintr   inventory_booking_dateget_inventory_detailsinventory_idinventory_codeinventory_booking_adultsinventory_booking_childreninventory_cost_currencyinventory_payment_methodinventory_rate_at_timeinventory_cost_per_childinventory_cost_per_adult	Exceptionr   r   gatepass_public_idgatepass_idr    r"   gatepass_done_bygatepass_ref_codegatepass_currencydestinationgatepass_dategatepass_vehicle_countgatepass_cost_per_vehiclegatepass_guest_countgatepass_discount_rategatepass_cost_per_ppgatepass_no_of_nightsr3   r4   r)   gatepass_destination_namegatepass_destination_public_idr   facility_booking_check_in_datefacility_booking_check_out_dateorder_bydescget_facility_detailsfacility_idfacility_codefacility_analysis_codefacility_no_of_nightsfacility_booking_adultsfacility_cost_per_adultfacility_booking_childrenfacility_cost_per_childfacility_fixed_costfacility_discount_ratefacility_commission_ratefacility_rate_at_timedaystyperoundr   configstatus_codepostpandaZExcelWriterstrZ	DataFrameZto_excelsave)?Z	startdateZenddatebookings_arrayinventory_arrayZgatspass_arrayfacility_arrayZmpesa_arrayZbank_transfer_arrayrd   Zmembers_arrayZvolunteers_arrayZdonations_arrayr7   responseObjectr_   bookingZresponse_dataZ__payment_methodrE   Z
mpesa_dataZ
m_currencyZcredit_cardZ
c_currencyZbank_transferZ
b_currencyr`   	inventoryinventory_bookingrJ   
gatepassesgatepassZgatepass_datar   Ztotalsvatrb   facilityfacility_datafacility_detailsZnum_of_weekspayloadZpaid_membersmembermember_dataZpaid_volunteersZ
volunteersZv_dataZpaid_donationsdonationZd_datawriterZbookings_data_dfZgatespass_data_dfZinventory_data_dfZfacilty_data_dfZmpesa_data_dfZcredit_card_dfZbank_transfer_dfZmemberships_dfZvolunteers_dfZdonations_dfZfile_location_namereponseObjectr/   r/   r0   generateCSVreports1   s   








&4

&4
&4
&44



B:f

*
"6<

*
 <<B"









6r   )Qflaskr   r   r   r   r   r   r   r	   r
   r   r   pymysqlosmathr   uuidroutesr   r   routes.seed_functionsr   r   r   r   database.booking_typesr   database.bookingsr   database.booking_guestsr   database.booking_paymentsr   database.payment_methodsr   database.booking_statusr   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.vehicler#   database.salesforcer$   database.sunr%   database.modifyr&   database.gater'   database.vehicle_detailsr(   database.destinationr)   	variablesfunctions.date_operatorsfunctions.currency_operatorsZpandasr   collectionsr+   r-   router   r/   r/   r/   r0   <module>   sH   ((