# -*- coding: utf-8 -*-
import datetime
from django.core.management.base import BaseCommand
from django.db import connections
from django.conf import settings
PATH_TO_FOLDER = '/Users/lloyd' # without end slash!
class Command(BaseCommand):
def add_arguments(self, parser):
parser.add_argument(
'--year',
dest='year',
type=int,
default=False
)
parser.add_argument(
'--month',
dest='month',
type=int,
default=False
)
def handle(self, *args, **options):
if options['year'] and options['month']:
date = datetime.date(options['year'], options['month'], day=1)
else:
date = datetime.date.today().replace(day=1)
end = date - datetime.timedelta(days=1)
start = end.replace(day=1)
self.devices_to_csv()
self.hit_to_csv(start, end)
self.prolonged_to_csv(start, end)
@staticmethod
def devices_to_csv():
cursor = connections[settings.DB_BASE].cursor()
sql = """
SELECT 'device_id', 'brand_name', 'screen_size', 'city', 'region'
UNION ALL
SELECT
`statistic_device`.`id`,
`system_brandname`.`name`,
`system_screensize`.`name`,
`system_playergeo`.`city_ru`,
`system_playergeo`.`region_ru`
FROM `statistic_device`
INNER JOIN `system_player` ON (`statistic_device`.`player_id` = `system_player`.`id`)
LEFT JOIN `system_playergeo` ON (`system_player`.`id` = `system_playergeo`.`player_id`)
LEFT OUTER JOIN `system_brandname` ON (`system_player`.`brand_name_id` = `system_brandname`.`id`)
LEFT OUTER JOIN `system_screensize` ON (`system_player`.`screen_size_id` = `system_screensize`.`id`)
INTO OUTFILE '%s/devices.csv'
FIELDS TERMINATED BY ','
ESCAPED BY ''
LINES TERMINATED BY '\n'
""" % PATH_TO_FOLDER
cursor.execute(sql)
@staticmethod
def prolonged_to_csv(start, end):
cursor = connections[settings.DB_STATISTIC_REPORT].cursor()
sql = """
SELECT 'action', 'param', 'params', 'device_id', 'date_start', 'date_end'
UNION ALL
SELECT
`action`,
`param`,
`params`,
`device_id`,
`date_start`,
`date_end`
FROM statistic_prolonged_partition
WHERE `date_start` BETWEEN '%s 00:00:00' AND '%s 23:59:59'
INTO OUTFILE '%s/prolonged.csv'
FIELDS TERMINATED BY ','
ESCAPED BY ''
LINES TERMINATED BY '\n'
""" % (start, end, PATH_TO_FOLDER)
cursor.execute(sql)
@staticmethod
def hit_to_csv(start, end):
cursor = connections[settings.DB_STATISTIC_REPORT].cursor()
sql = """
SELECT 'action', 'param', 'params', 'device_id', 'date'
UNION ALL
SELECT
`action`,
`param`,
`params`,
`device_id`,
`date`
FROM statistic_hit_partition
WHERE `date` BETWEEN '%s 00:00:00' AND '%s 23:59:59'
INTO OUTFILE '%s/hit.csv'
FIELDS TERMINATED BY ','
ESCAPED BY ''
LINES TERMINATED BY '\n'
""" % (start, end, PATH_TO_FOLDER)
cursor.execute(sql)