coding utf-8 import datetime from django core management base import B

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
# -*- 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)