from django.db.models import Q, F, Sum, When, Case from django.http import QueryDict from django.utils import timezone from django.utils.translation import gettext_lazy as _ from accounts.filters import DailyCreditGeneratedReportFilterSet from accounts.models import FifaAccount, FifaAccountCurrentItems from futplus.settings import TIME_ZONE from trade.filters import DailyInvestTradeGenerateReportFilterSet from trade.models import ConsoleTradeOneQuality, ConsoleTradeInvestDailyProfitLog, ConsoleTradeOneHistory from trade.tables import DailyInvestTradeGenerateChartDashboardCardTable from utils import number_convertor_to_milion from utils.dashboard_cards import FilterDashboardCard, ChartDashboardCard, height_16_8, TableDashboardCard from utils.date_utils import normalize_month_range from utils.query_extra_func import ToCharTZ class ConsoleTradeQualityReportChartDashboardCard(ChartDashboardCard, FilterDashboardCard): required_permission__all = 'manager_commands' template_name = 'generic_views/dashboard-cards/generic-chart.html' title = _('Trade Quality') title_mdi = 'mdi-sale' # title_link = reverse_lazy('panel:monthly-sale-report') model = ConsoleTradeOneQuality # ordering = ('date',) chart_width = 500 chart_height = height_16_8(chart_width) chart_wrapper_css = 'responsive-embed ratio_16_8' filterset_class = DailyCreditGeneratedReportFilterSet quality = 'gold1' def get_filterset_kwargs(self, filterset_class): kwargs = super().get_filterset_kwargs(filterset_class) if not kwargs['data']: kwargs['data'] = QueryDict(mutable=True) data: QueryDict = kwargs['data'] data._mutable = True start_date = normalize_month_range( start_date=timezone.localtime() - timezone.timedelta(days=31), future_months=0)[0] end_date = normalize_month_range(future_months=0)[1] data.setdefault('start_date', start_date.strftime('%Y-%m-%d')) data.setdefault('end_date', end_date.strftime('%Y-%m-%d')) return kwargs def extra_filter_remaining_queryset(self, queryset): return queryset def get_chart_config(self): queryset = self.model.objects.filter(name=self.quality) # queryset = queryset.related_to(self.request.user) filterset = self.filterset queryset = self.filter_queryset(queryset, filterset=filterset) labels = list(queryset.values_list('create_time', flat=True)) values_sell_price = list(queryset.values_list('sell_price', flat=True)) return { 'type': 'line', 'data': { 'labels': labels, 'datasets': [{ 'type': 'line', 'label': self.quality, 'data': values_sell_price, 'stack': 'stack-1', 'backgroundColor': self.get_chart_dataset_background_color(1, 3)[0], 'borderColor': self.get_chart_dataset_background_color(1, 3)[0], 'lineWidth': 2, 'fill': False, # 'hidden':True, # 'showLine': False, # 'steppedLine': 'middle', 'datalabels': { 'display': False, }, }, # { # 'label': _('Generate - Trade'), # 'data': values_today_production_mines_trade, # 'stack': 'stack-0', # 'backgroundColor': self.get_chart_dataset_background_color(1, 1) * len( # values_today_production_mines_trade), # 'borderWidth': 1, # }, { # 'label': _('Trade'), # 'data': values_today_console_trades_profit, # 'stack': 'stack-0', # 'backgroundColor': self.get_chart_dataset_background_color(1, 2) * len( # values_today_console_trades_profit), # 'borderWidth': 1, # }, { # 'label': _('credit'), # 'data': values_today_credit, # 'stack': 'stack-0', # 'backgroundColor': self.get_chart_dataset_background_color(1, 3) * len(values_today_credit), # 'borderWidth': 1, # }, { # 'label': _('discharge'), # 'data': values_today_discharges_coin, # 'stack': 'stack-0', # 'backgroundColor': self.get_chart_dataset_background_color(1, 4) * len(values_today_discharges_coin), # 'borderWidth': 1, # } ], }, 'options': { 'scales': { 'xAxes': [{ 'stacked': True, 'autoSkip': False, }], 'yAxes': [{ 'stacked': True, 'ticks': { 'beginAtZero': True, }, }], }, 'tooltips': { 'mode': 'index', # 'intersect': False, }, 'legend': { 'display': True, 'position': 'bottom', }, 'plugins': { 'labels': { 'render': 'value', }, } } } class ConsoleTradeQualityGold1ReportChartDashboardCard(ConsoleTradeQualityReportChartDashboardCard): quality = 'gold1' class ConsoleTradeQualityGold0ReportChartDashboardCard(ConsoleTradeQualityReportChartDashboardCard): quality = 'gold0' class ConsoleTradeQualitySilver1ReportChartDashboardCard(ConsoleTradeQualityReportChartDashboardCard): quality = 'silver1' class ConsoleTradeQualityBronze1ReportChartDashboardCard(ConsoleTradeQualityReportChartDashboardCard): quality = 'bronze1' class DailyInvestTradeGenerateChartDashboardCard(TableDashboardCard, ChartDashboardCard, FilterDashboardCard): required_permission__all = 'manager_commands' template_name = 'generic_views/dashboard-cards/generic-chart-table.html' title = _('Daily Invest Trade Generated') title_mdi = 'mdi-sale' # title_link = reverse_lazy('panel:monthly-sale-report') model = ConsoleTradeInvestDailyProfitLog # ordering = ('date',) chart_width = 500 chart_height = height_16_8(chart_width) chart_wrapper_css = 'responsive-embed ratio_16_8' filterset_class = DailyInvestTradeGenerateReportFilterSet table_class = DailyInvestTradeGenerateChartDashboardCardTable paginate_by = 25 def get_filterset_kwargs(self, filterset_class): kwargs = super().get_filterset_kwargs(filterset_class) if not kwargs['data']: kwargs['data'] = QueryDict(mutable=True) data: QueryDict = kwargs['data'] data._mutable = True start_date = normalize_month_range( start_date=timezone.localtime() - timezone.timedelta(days=31), future_months=0)[0] end_date = normalize_month_range(future_months=0)[1] data.setdefault('start_date', start_date.strftime('%Y-%m-%d')) data.setdefault('end_date', end_date.strftime('%Y-%m-%d')) return kwargs def get_table_data(self): if not self.table_data: return [{'title': '', 'sum': 0}] return self.table_data # def get_queryset(self): # return None def get_chart_config(self): queryset = self.model.objects.filter( Q(fifa_account__delete_console_reason=None) | Q(fifa_account__delete_console_reason='') ).exclude( Q(fifa_account__console=None) | Q(fifa_account__console__investor=None) ) fifa_accounts = FifaAccount.objects.filter( Q(delete_console_reason=None) | Q(delete_console_reason='') ).exclude( Q(console=None) | Q(console__investor=None) ) # queryset = queryset.related_to(self.request.user) filterset = self.filterset queryset = self.filter_queryset(queryset, filterset=filterset) # queryset = self.extra_filter_payments_queryset(queryset) # bellow is first day data added and has bad data queryset = queryset.exclude(create_time__date=timezone.datetime(year=2023, month=5, day=11)) console_trade_histories = ConsoleTradeOneHistory.objects.filter( Q(bought_items_managed=True) | Q(list_try_count__gte=1), fifa_account__id__in=queryset.values_list('fifa_account__id', flat=True), ).order_by('fifa_account', '-create_time').distinct('fifa_account').values_list('id', flat=True) console_trade_histories = ConsoleTradeOneHistory.objects.filter(id__in=console_trade_histories).filter( # todo : remove this filter after data complete # Q(club_bronze1_items__gt=0) | # Q(club_silver1_items__gt=0) | # Q(club_gold0_items__gt=0) | # Q(club_gold1_items__gt=0) | # Q(club_position_modifier_items__gt=0) ).annotate( bronze_1_items=F('club_bronze1_items') + Case(When(quality_name='bronze1', then=F('transfer_list_items_count')), default=0), silver_1_items=F('club_silver1_items') + Case(When(quality_name='silver1', then=F('transfer_list_items_count')), default=0), gold_0_items=F('club_gold0_items') + Case(When(quality_name='gold0', then=F('transfer_list_items_count')), default=0), gold_1_items=F('club_gold1_items') + Case(When(quality_name='gold1', then=F('transfer_list_items_count')), default=0), position_modifier_items=F('club_position_modifier_items') + Case( When(quality_name='position_modifier', then=F('transfer_list_items_count')), default=0), stock=Case( When(quality_name='bronze1', then=F('transfer_list_items_count') + F('club_bronze1_items')), When(quality_name='silver1', then=F('transfer_list_items_count') + F('club_silver1_items')), When(quality_name='gold0', then=F('transfer_list_items_count') + F('club_gold0_items')), When(quality_name='gold1', then=F('transfer_list_items_count') + F('club_gold1_items')), When(quality_name='position_modifier', then=F('transfer_list_items_count') + F('club_position_modifier_items')), default=0), ) club_bronze1_items = console_trade_histories.aggregate(Sum('bronze_1_items')).get('bronze_1_items__sum') credit_bronze1_acc = console_trade_histories.filter(quality_name='bronze1').aggregate( Sum('fifa_account__credit')).get('fifa_account__credit__sum') club_silver1_items = console_trade_histories.aggregate(Sum('silver_1_items')).get('silver_1_items__sum') credit_silver1_acc = console_trade_histories.filter(quality_name='silver1').aggregate( Sum('fifa_account__credit')).get('fifa_account__credit__sum') club_gold0_items = console_trade_histories.aggregate(Sum('gold_0_items')).get('gold_0_items__sum') credit_gold0_acc = console_trade_histories.filter(quality_name='gold0').aggregate( Sum('fifa_account__credit')).get('fifa_account__credit__sum') club_gold1_items = console_trade_histories.aggregate(Sum('gold_1_items')).get('gold_1_items__sum') credit_gold1_acc = console_trade_histories.filter(quality_name='gold1').aggregate( Sum('fifa_account__credit')).get('fifa_account__credit__sum') club_position_modifier_items = console_trade_histories.aggregate(Sum('position_modifier_items')).get( 'position_modifier_items__sum') credit_position_modifier_acc = console_trade_histories.filter(quality_name='position_modifier').aggregate( Sum('fifa_account__credit')).get('fifa_account__credit__sum') # a = queryset.values('create_time__date').order_by('create_time__date').annotate( # formatted_create_time=ToCharTZ('create_time', TIME_ZONE, 'dd.MM.yyyy HH24'), # # formatted_create_time=Func( # # F('create_time__date'), # # Value('dd.MM.yyyy'), # # function='to_char', # # output_field=CharField() # # ), # sum_credit_change=Sum('credit_change'), # sum_forcast_change=Sum('forcast_change'), # sum_today_sell_count=Sum('today_sell_count'), # ) # labels = list(a.values_list('formatted_create_time', flat=True)) labels = [] # values_today_production = list(a.values_list('sum_today_production', flat=True)) # values_today_credit_change = list(a.values_list('sum_credit_change', flat=True)) values_today_credit_change = [] # values_today_console_trades_profit = list(a.values_list('sum_today_console_trades_profit', flat=True)) # values_today_forcast_change = list(a.values_list('sum_forcast_change', flat=True)) values_today_forcast_change = [] # values_today_sell_count = list(a.values_list('sum_today_sell_count', flat=True)) values_today_sell_count = [] consoles_invest_trade_active = queryset.distinct('fifa_account__console').values( 'fifa_account__console').count() accounts_invest_trade_active = queryset.distinct('fifa_account').values('fifa_account').count() all_bronze0_tradable_items = FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum('club_bronze0_tradable_items')).get('club_bronze0_tradable_items__sum') all_bronze1_tradable_items = FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum('club_bronze1_tradable_items')).get('club_bronze1_tradable_items__sum') all_silver0_tradable_items = FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum('club_silver0_tradable_items')).get('club_silver0_tradable_items__sum') all_silver1_tradable_items = FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum('club_silver1_tradable_items')).get('club_silver1_tradable_items__sum') all_gold0_tradable_items = FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum('club_gold0_tradable_items')).get('club_gold0_tradable_items__sum') all_gold1_tradable_items = FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum('club_gold1_tradable_items')).get('club_gold1_tradable_items__sum') all_gold1_special_tradable_items = FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum('club_gold1_special_tradable_items')).get('club_gold1_special_tradable_items__sum') all_bronze0_untradable_items = FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum('club_bronze0_untradable_items')).get('club_bronze0_untradable_items__sum') all_bronze1_untradable_items = FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum('club_bronze1_untradable_items')).get('club_bronze1_untradable_items__sum') all_silver0_untradable_items = FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum('club_silver0_untradable_items')).get('club_silver0_untradable_items__sum') all_silver1_untradable_items = FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum('club_silver1_untradable_items')).get('club_silver1_untradable_items__sum') all_gold0_untradable_items = FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum('club_gold0_untradable_items')).get('club_gold0_untradable_items__sum') all_gold1_untradable_items = FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum('club_gold1_untradable_items')).get('club_gold1_untradable_items__sum') all_gold1_special_untradable_items = FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum('club_gold1_special_untradable_items')).get('club_gold1_special_untradable_items__sum') all_sbc_storage_items = FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum('sbc_storage_count')).get('sbc_storage_count__sum') list_rating_items = [] for iiie in range(80, 100): list_rating_items.append({'title': f'{iiie}th Rating', 'items_count': FifaAccountCurrentItems.objects.filter(fifa_account__in=fifa_accounts).aggregate(Sum(f'rating_{iiie}_items')).get(f'rating_{iiie}_items__sum')}) self.table_data = [{ 'title': _('Consoles Invest Trade Active'), # 'sum': DecimalCurrency(sum(v or 0 for v in values_forecast), currency).as_text(), 'sum': consoles_invest_trade_active, }, { 'title': _('Accounts Invest Trade Active'), # 'sum': DecimalCurrency(sum(v or 0 for v in values_payments), currency).as_text(), 'sum': accounts_invest_trade_active, }, { 'title': _('Trade'), # 'sum': DecimalCurrency(sum(v or 0 for v in values_remaining), currency).as_text(), 'sum': 0, }, { 'title': _('Bronze Rare Items'), # 'sum': DecimalCurrency(sum(v or 0 for v in values_remaining), currency).as_text(), 'sum': f"{console_trade_histories.filter(quality_name='bronze1').count()}", 'accounts_credit': f'{number_convertor_to_milion(credit_bronze1_acc or 0)}', 'items_count': f'{club_bronze1_items}', 'fifa_accounts_count': f"{fifa_accounts.filter(console_trade_one_quality='bronze1').count()} - {console_trade_histories.filter(fifa_account__console_trade_one_quality='bronze1', club_bronze1_items__lte=0, transfer_list_items_count__lte=0).count()}", }, { 'title': _('Silver Rare Items'), # 'sum': DecimalCurrency(sum(v or 0 for v in values_remaining), currency).as_text(), 'sum': f"{console_trade_histories.filter(quality_name='silver1').count()}", 'accounts_credit': f'{number_convertor_to_milion(credit_silver1_acc or 0)}', 'items_count': f'{club_silver1_items}', 'fifa_accounts_count': f"{fifa_accounts.filter(console_trade_one_quality='silver1').count()} - {console_trade_histories.filter(fifa_account__console_trade_one_quality='silver1', club_silver1_items__lte=0, transfer_list_items_count__lte=0).count()}", }, { 'title': _('Gold Common Items'), # 'sum': DecimalCurrency(sum(v or 0 for v in values_remaining), currency).as_text(), 'sum': f"{console_trade_histories.filter(quality_name='gold0').count()}", 'accounts_credit': f'{number_convertor_to_milion(credit_gold0_acc or 0)}', 'items_count': f'{club_gold0_items}', 'fifa_accounts_count': f"{fifa_accounts.filter(console_trade_one_quality='gold0').count()} - {console_trade_histories.filter(fifa_account__console_trade_one_quality='gold0', club_gold0_items__lte=0, transfer_list_items_count__lte=0).count()}", }, { 'title': _('Gold Rare Items'), # 'sum': DecimalCurrency(sum(v or 0 for v in values_remaining), currency).as_text(), 'sum': f"{console_trade_histories.filter(quality_name='gold1').count()}", 'accounts_credit': f'{number_convertor_to_milion(credit_gold1_acc or 0)}', 'items_count': f'{club_gold1_items}', 'fifa_accounts_count': f"{fifa_accounts.filter(console_trade_one_quality='gold1').count()} - {console_trade_histories.filter(fifa_account__console_trade_one_quality='gold1', club_gold1_items__lte=0, transfer_list_items_count__lte=0).count()}", }, { 'title': _('Position Modifier Items'), # 'sum': DecimalCurrency(sum(v or 0 for v in values_remaining), currency).as_text(), 'sum': f"{console_trade_histories.filter(quality_name='position_modifier').count()}", 'accounts_credit': f'{number_convertor_to_milion(credit_position_modifier_acc or 0)}', 'items_count': f'{club_position_modifier_items}', 'fifa_accounts_count': f"{fifa_accounts.filter(console_trade_one_quality='position_modifier').count()} - {console_trade_histories.filter(fifa_account__console_trade_one_quality='position_modifier', club_position_modifier_items__lte=0, transfer_list_items_count__lte=0).count()}", }, # { # 'title': _('Bronze Rare Accounts'), # # 'sum': DecimalCurrency(sum(v or 0 for v in values_remaining), currency).as_text(), # 'sum': f"{fifa_accounts.filter(console_trade_one_quality='bronze1').count()} - {console_trade_histories.filter(fifa_account__console_trade_one_quality='bronze1', club_bronze1_items__lte=0, transfer_list_items_count__lte=0).count()}", # }, { # 'title': _('Silver Rare Accounts'), # # 'sum': DecimalCurrency(sum(v or 0 for v in values_remaining), currency).as_text(), # 'sum': f"{fifa_accounts.filter(console_trade_one_quality='silver1').count()} - {console_trade_histories.filter(fifa_account__console_trade_one_quality='silver1', club_silver1_items__lte=0, transfer_list_items_count__lte=0).count()}", # }, { # 'title': _('Gold Common Accounts'), # # 'sum': DecimalCurrency(sum(v or 0 for v in values_remaining), currency).as_text(), # 'sum': f"{fifa_accounts.filter(console_trade_one_quality='gold0').count()} - {console_trade_histories.filter(fifa_account__console_trade_one_quality='gold0', club_gold0_items__lte=0, transfer_list_items_count__lte=0).count()}", # }, { # 'title': _('Gold Rare Accounts'), # # 'sum': DecimalCurrency(sum(v or 0 for v in values_remaining), currency).as_text(), # 'sum': f"{fifa_accounts.filter(console_trade_one_quality='gold1').count()} - {console_trade_histories.filter(fifa_account__console_trade_one_quality='gold1', club_gold1_items__lte=0, transfer_list_items_count__lte=0).count()}", # }, { # 'title': _('Position Modifier Accounts'), # # 'sum': DecimalCurrency(sum(v or 0 for v in values_remaining), currency).as_text(), # 'sum': f"{fifa_accounts.filter(console_trade_one_quality='position_modifier').count()} - {console_trade_histories.filter(fifa_account__console_trade_one_quality='position_modifier', club_position_modifier_items__lte=0, transfer_list_items_count__lte=0).count()}", # }, { 'title': _('None Quality Accounts'), # 'sum': DecimalCurrency(sum(v or 0 for v in values_remaining), currency).as_text(), 'sum': f"{fifa_accounts.filter(Q(console_trade_one_quality=None) | Q(console_trade_one_quality=''), run_console_invest_trade_one=True).count()}", }, { 'title': _('all bronze0 tradable items'), 'items_count': f"{all_bronze0_tradable_items:,}", }, { 'title': _('all bronze1 tradable items'), 'items_count': f"{all_bronze1_tradable_items:,}", }, { 'title': _('all silver0 tradable items'), 'items_count': f"{all_silver0_tradable_items:,}", }, { 'title': _('all silver1 tradable items'), 'items_count': f"{all_silver1_tradable_items:,}", }, { 'title': _('all gold0 tradable items'), 'items_count': f"{all_gold0_tradable_items:,}", }, { 'title': _('all gold1 tradable items'), 'items_count': f"{all_gold1_tradable_items:,}", }, { 'title': _('all gold special tradable items'), 'items_count': f"{all_gold1_special_tradable_items:,}", }, { 'title': _('all bronze0 untradable items'), 'items_count': f"{all_bronze0_untradable_items:,}", }, { 'title': _('all bronze1 untradable items'), 'items_count': f"{all_bronze1_untradable_items:,}", }, { 'title': _('all silver0 untradable items'), 'items_count': f"{all_silver0_untradable_items:,}", }, { 'title': _('all silver1 untradable items'), 'items_count': f"{all_silver1_untradable_items:,}", }, { 'title': _('all gold0 untradable items'), 'items_count': f"{all_gold0_untradable_items:,}", }, { 'title': _('all gold1 untradable items'), 'items_count': f"{all_gold1_untradable_items:,}", }, { 'title': _('all gold1 special untradable items'), 'items_count': f"{all_gold1_special_untradable_items:,}", }, { 'title': _('all sbc storage'), 'items_count': f"{all_sbc_storage_items:,}", }, ] self.table_data += list_rating_items return { 'type': 'bar', 'data': { 'labels': labels, 'datasets': [ { # 'type': 'line', # 'label': _('Generated'), # 'data': values_today_production, # 'stack': 'stack-1', # 'backgroundColor': self.get_chart_dataset_background_color(1, 3)[0], # 'borderColor': self.get_chart_dataset_background_color(1, 3)[0], # 'lineWidth': 2, # 'fill': False, # # 'hidden':True, # # 'showLine': False, # # 'steppedLine': 'middle', # 'datalabels': { # 'display': True, # }, # }, { 'label': _('Credit Change'), 'data': values_today_credit_change, 'stack': 'stack-0', 'backgroundColor': self.get_chart_dataset_background_color(1, 1) * len( values_today_credit_change), 'borderWidth': 1, }, { 'label': _('Forcast Change'), 'data': values_today_forcast_change, 'stack': 'stack-0', 'backgroundColor': self.get_chart_dataset_background_color(1, 2) * len( values_today_forcast_change), 'borderWidth': 1, }, { 'label': _('Sell Count'), 'data': values_today_sell_count, 'stack': 'stack-0', 'backgroundColor': self.get_chart_dataset_background_color(1, 4) * len( values_today_sell_count), 'borderWidth': 1, } ], }, 'options': { 'scales': { 'xAxes': [{ 'stacked': True, 'autoSkip': False, }], 'yAxes': [{ 'stacked': True, 'ticks': { 'beginAtZero': True, }, }], }, 'tooltips': { 'mode': 'index', # 'intersect': False, }, 'legend': { 'display': True, 'position': 'bottom', }, 'plugins': { 'labels': { 'render': 'value', }, } } }