from django.contrib.humanize.templatetags.humanize import intcomma
from django.db.models import Avg, Count, F, Sum
from django.db.models.functions import ExtractMonth, ExtractYear
from django.utils.timezone import now
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
from dashboards.component import Chart, Table
from dashboards.component.chart import ChartSerializer
from dashboards.component.layout import Card, ComponentLayout, Div
from dashboards.component.table import TableSerializer
from dashboards.component.text import Stat, StatData
from dashboards.dashboard import Dashboard
from dashboards.registry import registry
from dateutil.relativedelta import relativedelta
from plotly.subplots import make_subplots
from example.sales.models import Sale
def get_total_profit(**kwargs):
profit = f'{Sale.objects.filter().aggregate(total_profit=Sum("profit_amount"))["total_profit"]:.2f}'
return StatData(
text="Total Profit",
sub_text=f"${intcomma(profit)}",
)
def get_total_cost(**kwargs):
profit = f'{Sale.objects.filter().aggregate(total_cost=Sum("cost_amount"))["total_cost"]:.2f}'
return StatData(
text="Total Cost",
sub_text=f"${intcomma(profit)}",
)
def get_avg_sales_per_date(**kwargs):
avg_sales_per_day = (
Sale.objects.values("paid_date")
.annotate(sales_per_day=Count("pk"))
.aggregate(avg_sales_per_day=Avg("sales_per_day"))["avg_sales_per_day"]
)
return StatData(
text="Avg. Sales Per Day",
sub_text=f"{int(avg_sales_per_day)}",
)
class ProfitByYearChartSerializer(ChartSerializer):
def get_queryset(self, *args, **kwargs):
return (
Sale.objects.complete()
.with_datetime()
.values("channel", "year")
.annotate(total_profit=Sum("profit_amount"))
.values("year", "channel", "total_profit")
.order_by("year")
)
def to_fig(self, df):
df["year"] = df["year"].astype("str")
fig = px.bar(
df,
y="year",
x="total_profit",
color="channel",
text_auto=True,
orientation="h",
)
fig.update_layout(xaxis_title="Year", yaxis_title="Profit")
return fig
class Meta:
title = "PROFIT BY YEAR"
class SalesByChannelYearChartSerializer(ChartSerializer):
def get_queryset(self, *args, **kwargs):
return (
Sale.objects.complete()
.with_datetime()
.values("year", "channel")
.annotate(total_sales=Sum("paid_amount"))
.values("year", "channel", "total_sales")
.order_by("total_sales")
)
def to_fig(self, df):
current_date = now()
current_year = df[df["year"] == current_date.year]
previous_year = df[df["year"] == current_date.year - 1]
fig = make_subplots(
rows=1,
cols=2,
shared_yaxes=True,
subplot_titles=(
f"Sales {current_date.year - 1}",
f"Sales {current_date.year}",
),
)
fig.add_trace(
go.Bar(
x=current_year["channel"],
y=current_year["total_sales"],
name=current_date.year,
text=current_year["total_sales"],
),
row=1,
col=1,
)
fig.add_trace(
go.Bar(
x=previous_year["channel"],
y=previous_year["total_sales"],
name=f"{current_date.year - 1}",
text=current_year["total_sales"],
),
row=1,
col=2,
)
return fig
class Meta:
title = "SALES BY CHANNEL"
class QuantityByChannelYearChartSerializer(ChartSerializer):
def get_queryset(self, *args, **kwargs):
return (
Sale.objects.complete()
.with_datetime()
.values("year", "channel")
.annotate(qty=Count("id"))
.values("year", "channel", "qty")
.order_by("qty")
)
def to_fig(self, df):
current_date = now()
current_year = df[df["year"] == current_date.year]
previous_year = df[df["year"] == current_date.year - 1]
fig = make_subplots(
rows=1,
cols=2,
shared_yaxes=True,
specs=[[{"type": "pie"}, {"type": "pie"}]],
subplot_titles=(
f"QUANTITY {current_date.year - 1}",
f"QUANTITY {current_date.year}",
),
)
fig.add_trace(
go.Pie(
labels=current_year["channel"],
values=current_year["qty"],
name=current_date.year,
texttemplate="%{label}<br>%{percent}",
textposition="inside",
hole=0.4,
),
row=1,
col=1,
)
fig.add_trace(
go.Pie(
labels=previous_year["channel"],
values=previous_year["qty"],
name=f"{current_date.year - 1}",
texttemplate="%{label}<br>%{percent}",
textposition="inside",
hole=0.4,
),
row=1,
col=2,
)
fig.update_layout(
# Add annotations in the center of the donut pies.
annotations=[
dict(
text=f"{current_date.year - 1}",
x=0.22,
y=0.46,
font_size=20,
showarrow=False,
),
dict(
text=f"{current_date.year}",
x=0.77,
y=0.46,
font_size=20,
showarrow=False,
),
]
)
return fig
class Meta:
title = "QUANTITY BY CHANNEL"
class QuantityByChannelChartSerializer(ChartSerializer):
def get_queryset(self, *args, **kwargs):
return (
Sale.objects.complete()
.with_datetime()
.values("channel")
.annotate(qty=Count("id"))
.values("channel", "qty")
.order_by("-qty")
)
def to_fig(self, df):
fig = px.funnel_area(
df,
names="channel",
values="qty",
)
return fig
class Meta:
title = "TOTAL SOLD BY CHANNEL"
class SalesByCountryYearChartSerializer(ChartSerializer):
def get_queryset(self, *args, **kwargs):
return (
Sale.objects.complete()
.with_datetime()
.values("year", "customer__country", "channel")
.annotate(total_profit=Sum("profit_amount"))
.values("year", "customer__country", "channel", "total_profit")
)
def to_fig(self, df):
fig = px.treemap(
df,
path=[px.Constant("all"), "year", "customer__country", "channel"],
values="total_profit",
color="customer__country",
maxdepth=3,
)
fig.update_traces(root_color="lightgrey")
return fig
class Meta:
title = "PROFIT BY COUNTRY PER YEAR"
class PaidVsCostMonthlyChartSerializer(ChartSerializer):
def get_queryset(self, *args, **kwargs):
from_datetime = now() - relativedelta(months=6)
return (
Sale.objects.complete()
.with_datetime()
.filter(paid_datetime__gte=from_datetime)
.values("year", "month")
.annotate(total_paid=Sum("paid_amount"), total_cost=Sum("cost_amount"))
.values("year", "month", "total_paid", "total_cost")
)
def to_fig(self, df):
df["date"] = pd.to_datetime(df[["year", "month"]].assign(DAY=1))
fig = go.Figure()
fig.add_trace(
go.Bar(
name="Total Paid",
x=df["date"],
y=df["total_paid"],
text=df["total_paid"],
),
)
fig.add_trace(
go.Bar(
name="Total Cost",
x=df["date"],
y=df["total_cost"],
text=df["total_cost"],
),
)
fig.update_layout(xaxis_title="Date", yaxis_title="Total")
return fig
class Meta:
title = "COST VS PAID BY MONTH"
class SalesChartSerializer(ChartSerializer):
def get_queryset(self, *args, **kwargs):
current_date = now()
return (
Sale.objects.all()
.annotate(
paid_datetime=current_date - F("paid_date")
) # convert time delta for rolling demo.
.values("paid_datetime")
.filter()
.annotate(
month=ExtractMonth("paid_datetime"), year=ExtractYear("paid_datetime")
)
.values("month", "year")
.annotate(total_profit=Sum("profit_amount"))
.values("month", "year", "total_profit")
.order_by("year", "month")
)
def to_fig(self, df):
current_date = now()
df["month"] = df["month"].astype(str)
current_year = df[df["year"] == current_date.year]
previous_year = df[df["year"] == current_date.year - 1]
fig = go.Figure()
fig.add_trace(
go.Bar(
x=current_year["month"],
y=current_year["total_profit"],
name=current_date.year,
),
)
fig.add_trace(
go.Scatter(
x=previous_year["month"],
y=previous_year["total_profit"],
name=f"Target ({current_date.year - 1})",
),
)
fig.update_layout(
xaxis_title="Month No.", yaxis_title="Total Profit", hovermode="x unified"
)
return fig
class Meta:
title = "Current year sales"
class StatusPerMonthChartSerializer(ChartSerializer):
def get_queryset(self, *args, **kwargs):
return (
Sale.objects.with_datetime()
.values("status", "month", "year")
.annotate(total=Count("status"))
.values("status", "month", "year", "total")
.order_by("-status", "year", "month")
)
def to_fig(self, df):
df["date"] = pd.to_datetime(df[["year", "month"]].assign(DAY=1))
fig = px.area(df, x="date", y="total", color="status")
fig.update_layout(
xaxis=dict(rangeslider=dict(visible=True), type="date"), hovermode="x"
)
return fig
class Meta:
title = "Completed & Refunded Per Month"
class CountryChannelBreakdownChartSerializer(ChartSerializer):
def to_fig(self, df):
fig = px.sunburst(
df, path=["channel", "customer__country"], values="profit_amount"
)
return fig
class Meta:
title = "Country/Channel breakdown"
fields = ["profit_amount", "channel", "customer__country"]
model = Sale
class ChannelDistributionsChartSerializer(ChartSerializer):
def to_fig(self, df):
fig = px.density_heatmap(df, x="paid_amount", y="profit_amount")
return fig
class Meta:
title = "Channel Distribution"
fields = ["profit_amount", "paid_amount"]
model = Sale
class AllSalesTableSerializer(TableSerializer):
def get_queryset(self, *args, **kwargs):
current_date = now()
return (
Sale.objects.filter(profit_amount__gt=33)
.annotate(
paid_datetime=current_date
- F("paid_date") # convert time delta for rolling demo.
)
.values("paid_datetime", "profit_amount", "channel", "customer")
)
class Meta:
columns = {
"paid_datetime": "Date",
"customer__name": "Customer",
"profit_amount": "Profit",
"channel": "Channel",
}
order = ["-profit_amount"]
class SalesDashboard(Dashboard):
total_profit = Stat(value=get_total_profit)
total_cost = Stat(value=get_total_cost)
avg_sales_per_date = Stat(value=get_avg_sales_per_date)
daily_sales = Chart(defer=SalesChartSerializer)
profit_by_year = Chart(defer=ProfitByYearChartSerializer)
sales_by_channel = Chart(defer=SalesByChannelYearChartSerializer)
sales_by_country = Chart(defer=SalesByCountryYearChartSerializer)
quantity_by_channel_year = Chart(defer=QuantityByChannelYearChartSerializer)
quantity_by_channel = Chart(defer=QuantityByChannelChartSerializer)
paid_vs_cost = Chart(defer=PaidVsCostMonthlyChartSerializer)
country_breakdown = Chart(defer=CountryChannelBreakdownChartSerializer)
channel_distributions = Chart(defer=ChannelDistributionsChartSerializer)
status_per_month = Chart(defer=StatusPerMonthChartSerializer)
all_sales = Table(defer=AllSalesTableSerializer)
class Meta:
name = "Sales"
class Layout(Dashboard.Layout):
components = ComponentLayout(
Card("total_profit", grid_css_classes="span-4"),
Card("total_cost", grid_css_classes="span-4"),
Card("avg_sales_per_date", grid_css_classes="span-4"),
Card("sales_by_country", grid_css_classes="span-8"),
Card("country_breakdown", grid_css_classes="span-4"),
Card("sales_by_channel", grid_css_classes="span-6"),
Card("quantity_by_channel_year", grid_css_classes="span-6"),
Card("quantity_by_channel", grid_css_classes="span-4"),
Card("paid_vs_cost", grid_css_classes="span-8"),
Card("profit_by_year", grid_css_classes="span-6"),
Card("channel_distributions", grid_css_classes="span-6"),
Card("daily_sales", grid_css_classes="span-6"),
Card("status_per_month", grid_css_classes="span-6"),
)
registry.register(SalesDashboard)
