o
    ayhD                     @   s  d dl 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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% edddd Z&edddd Z'edddd Z(edddd Z)edddd Z*edddd Z+edddd  Z,eddd!d" Z-eddd#d$ Z.eddd%d& Z/eddd'd( Z0eddd)d* Z1dS )+    )login_required)renderget_object_or_404)SumFCountSale)Expense)ProductCustomer)Supplier)Order)Employee
AttendancePayrollLeave)timezone)	timedelta)render_to_string)HttpResponseN)Decimalz/auth/login/)	login_urlc                 C   s   t   }|tdd }tjjd||djtddd pd}t	jj||d	jtd
dd p1d}|| }t
j }||||t  d}t| d|S )z2Reports dashboard with overview of all key metrics   daysT)is_completesale_date__date__gtesale_date__date__ltetotal_amounttotalr"   r   )	date__gte	date__lteamount)total_salestotal_expensesprofit_losstotal_customersgenerated_datezreports/reports_dashboard.html)r   nowdater   r	   objectsfilter	aggregater   r
   r   countr   )requesttodaythirty_days_agor&   r'   r(   r)   context r5   &/var/www/html/kangema/reports/views.pyreports_dashboard   s@   
r7   c                 C   s   | j dt  }| j dt  }tjjd||gdjt	ddd p)d}t
jj||gd	jt	d
dd p<d}|| }|||||d}t| d|S )N
start_dateend_dateTr   sale_date__ranger    r!   r"   r   date__ranger%   )r&   r'   r(   r8   r9   zreports/profit_loss_report.html)GETgetr   r+   r,   r	   r-   r.   r/   r   r
   r   )r1   r8   r9   r&   r'   r(   r4   r5   r5   r6   profit_loss_report7   s(   r@   c                 C   s   | j dt  }| j dt  }tjj||gdjt	dd}tj
djt	ddd	d d
 }||||d}t| d|S )Nr8   r9   r;   r    )r&   items__product__nameitems__quantity)
total_soldz-total_sold   )salestop_selling_productsr8   r9   zreports/sales_report.html)r>   r?   r   r+   r,   r	   r-   r.   r/   r   valuesannotateorder_byr   )r1   r8   r9   rF   rG   r4   r5   r5   r6   sales_reportT   s   rK   c                 C   sh   | j dt  }| j dt  }tjj||gddj	t
dd}|||d}t| d|S )	Nr8   r9   r<   category__namer%   r!   )expensesr8   r9   zreports/expense_report.html)r>   r?   r   r+   r,   r
   r-   r.   rH   rI   r   r   )r1   r8   r9   rM   r4   r5   r5   r6   expense_reportj   s   rN   c                 C   sJ   t jjttdtd dd pd}t jjdd}||d}t| d	|S )
Nstock_quantitybuying_price)total_valuerQ   r   
   stock_quantity__lt)total_inventory_valuelow_stock_itemszreports/inventory_report.html)r   r-   r/   r   r   r.   r   )r1   rU   rV   r4   r5   r5   r6   inventory_report|   s   rW   c                  C   s@  ddl m} ddlm} ddlm} ddlm} ddlm	} ddl
m
}m} t||d}	z|j }
W n |jy?   d }
Y nw d }|
rT|
jrTz|
jj}W n   d }Y | jd	}| jd
}| jdd}|sut |dd  }n||d }|st  }n||d }td}|jj|	d|dd}|D ]}||j7 }|jptd}|dkr||8 }q|jj|	||dd}|}g }td}td}td}|d}|D ]}}t|j|j|j}|jdkr6||j7 }||j7 }| |j!d|j" d| dd |jd ||d |jr5|jdkr5||j8 }||j7 }| |j!d|j" d d |j||d q||j7 }||j7 }| |j!d|j" d| d|jd d ||d q|j#dd |dkd |	|||||t |
||||d}|d}|$|}t%d d!}d"|	j& d#|d$< |j'||d%}|j(rt%d&| d' S |S )(Nr   )get_template)pisar   r   )Shop)datetimer   )customer_idr8   r9   
sort_orderdescr   r   z%Y-%m-%dz0.00credit)customerpayment_methodsale_date__date__lt	sale_date)r`   r   r   z	Invoice #z ())r,   description	cash_salecredit_salecredit_paymentbalancesalezPayment for Invoice #zSale #c                 S   s   | d S )Nr,   r5   )xr5   r5   r6   <lambda>  s    z(customer_statement_pdf.<locals>.<lambda>)keyreverse)r`   statement_entriesopening_balancefinal_balancer8   r9   r*   shop	logo_pathtotal_debitstotal_creditstotal_cash_salesz#reports/customer_statement_pdf.htmlzapplication/pdfcontent_typez attachment; filename="statement_z.pdf"Content-Disposition)destzWe had some errors <pre>z</pre>))django.template.loaderrX   	xhtml2pdfrY   customers.modelsr   sales.modelsr	   settings.modelsrZ   r[   r   r   r-   firstDoesNotExistlogopathr>   r?   r   r+   r,   strptimer   r.   rJ   r    credit_amountdictPAYMENT_METHOD_CHOICESra   appendrc   idsortr   r   r\   	CreatePDFerr) r1   r\   rX   rY   r   r	   rZ   r[   r   r`   rr   rs   r8   r9   r]   rp   opening_salesrj   credit_paid	all_salesrunning_balancero   rt   ru   rv   sales_for_balancepayment_method_displayr4   templatehtmlresponsepisa_statusr5   r5   r6   customer_statement_pdf   s   












r   c                 C   s   | j dt  tdd }| j dt  }tjj||gd	 }t
jj||gdddd	jtd
ddd d }tjjtdd d}tjj||gd	 }||||||d}t| d|S )Nr8   r   r   r9   )date_added__rangerA   customer__idcustomer__first_namecustomer__last_namer    total_spent-total_spentrE   credit_limitd   )credit_balance__gte)r)   top_customerscustomers_nearing_credit_limitcustomer_growthr8   r9   zreports/customer_report.html)r>   r?   r   r+   r,   r   r   r-   r.   r0   r	   rH   rI   r   rJ   r   r   )r1   r8   r9   r)   r   r   r   r4   r5   r5   r6   customer_report#  s(    	r   c                 C   s   | j dt  tdd }| j dt  }tj }t	jj
||gdjttdtd dd	d
dd d }t	jj
||gdjttdtd dd
 pXd}t	jj
d||gdd	jtdd}||||||d}t| d|S )Nr8   r   r   r9   created_at__rangeitems__quantity_ordereditems__buying_pricer   supplier__namer   r   rE   r   F)is_receivedr   r   )total_pending)total_supplierstop_supplierstotal_expenditurepending_ordersr8   r9   zreports/supplier_report.html)r>   r?   r   r+   r,   r   r   r-   r0   r   r.   rI   r   r   rH   rJ   r/   r   r   )r1   r8   r9   r   r   r   r   r4   r5   r5   r6   supplier_reportD  s8    
	r   c           	      C   s   | j dt  tdd }| j dt  }tj }t	jj
||gddddjtdd	d
}tjj
||gdjtddd pId}tjj
||gd }tjj
||gddddddd}|||||||d}t| d|S )Nr8   r   r   r9   r<   employee__first_nameemployee__last_namestatusr!   z-total)month__rangesalary_paid)
total_paidr   r   )hire_date__range)start_date__rangeapproved)total_employeesattendance_summarytotal_payrollemployee_growthleave_summaryr8   r9   zreports/employee_report.html)r>   r?   r   r+   r,   r   r   r-   r0   r   r.   rH   rI   r   rJ   r   r/   r   r   r   )	r1   r8   r9   r   r   r   r   r   r4   r5   r5   r6   employee_reporti  s@    


r   c                 C   sj   | j dt  }| j dt  }tjjdd }tjj||gd}||||d}t	| d|S )Nr8   r9   F)r   r   )r   recent_ordersr8   r9   zreports/order_report.html)
r>   r?   r   r+   r,   r   r-   r.   r0   r   )r1   r8   r9   r   r   r4   r5   r5   r6   order_report  s   r   c           	      C   s   | j dt  }| j dt  }tjjd||gdjt	ddd p)d}t
jj||gd	jt	d
dd p<d}|| }g d|||gd}t|}tdd}d|d< |j|dd |S )Nr8   r9   Tr:   r    r!   r"   r   r<   r%   )zTotal SaleszTotal ExpenseszProfit/Loss)MetriczAmount (Ksh)zapplication/vnd.ms-excelrw   z.attachment; filename="profit_loss_report.xlsx"ry   F)index)r>   r?   r   r+   r,   r	   r-   r.   r/   r   r
   pd	DataFramer   to_excel)	r1   r8   r9   r&   r'   r(   datadfr   r5   r5   r6   export_profit_loss_excel  s   (&

r   c                 C   sb   | j d}| j d}tjjdddddd}t|}td	d
}d|d< |j	|ddd |S )Nr8   r9   rR   rS   namerL   rO   reorder_levelzAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheetrw   z,attachment; filename="inventory_report.xlsx"ry   Fopenpyxl)r   engine)
r>   r?   r   r-   r.   rH   r   r   r   r   )r1   r8   r9   rV   r   r   r5   r5   r6   export_inventory_report_excel  s   

r   )2django.contrib.auth.decoratorsr   django.shortcutsr   r   django.db.modelsr   r   r   r~   r	   expenses.modelsr
   inventory.modelsr   r}   r   suppliers.modelsr   orders.modelsr   	hr.modelsr   r   r   r   django.utilsr   r[   r   r{   r   django.httpr   pandasr   decimalr   r7   r@   rK   rN   rW   r   r   r   r   r   r   r   r5   r5   r5   r6   <module>   sR    
#




 
 
$
(

