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

# -*- 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)