import re
import os
import datetime
from openpyxl import Workbook
from django.core.management import BaseCommand
from common.mixins import LoggerMixin
from apps.doc.models import DDARecords
from settings import conf
class Command(BaseCommand, LoggerMixin):
def __init__(self):
super().__init__()
self.sheet_info = [
('今日新建',
('Application No', 'DDA Found?', 'DDA Found Time', 'ID Found?', 'ID Found Time', 'BC Found?',
'BC Found Time'),
('application_id', 'is_dda_found', 'is_id_found', 'is_bc_found', 'dda_found_time', 'id_found_time',
'bc_found_time')),
('今日完成',
('Application No', 'DDA Found Time', 'ID Found Time', 'BC Found Time', 'Create Time'),
('application_id', 'dda_found_time', 'id_found_time', 'bc_found_time', 'create_time')),
('累计进行中',
('Application No', 'DDA Found?', 'DDA Found Time', 'ID Found?', 'ID Found Time', 'BC Found?',
'BC Found Time', 'Create Time'),
('application_id', 'is_dda_found', 'is_id_found', 'is_bc_found', 'dda_found_time', 'id_found_time',
'bc_found_time', 'create_time')),
]
self.month_sheet_info = [
('本月新建',
('Application No', 'DDA Found?', 'DDA Found Time', 'ID Found?', 'ID Found Time', 'BC Found?',
'BC Found Time'),
('application_id', 'is_dda_found', 'is_id_found', 'is_bc_found', 'dda_found_time', 'id_found_time',
'bc_found_time')),
('本月完成',
('Application No', 'DDA Found Time', 'ID Found Time', 'BC Found Time', 'Create Time'),
('application_id', 'dda_found_time', 'id_found_time', 'bc_found_time', 'create_time')),
('累计进行中',
('Application No', 'DDA Found?', 'DDA Found Time', 'ID Found?', 'ID Found Time', 'BC Found?',
'BC Found Time', 'Create Time'),
('application_id', 'is_dda_found', 'is_id_found', 'is_bc_found', 'dda_found_time', 'id_found_time',
'bc_found_time', 'create_time')),
]
self.dda_dir = os.path.join(conf.DATA_DIR, 'HIL', 'SF5-CL-S-1', 'DDA')
self.excel_name_base = 'DDA_Data'
# self.complete_dda_dir = os.path.join(self.dda_dir, 'complete')
# self.wanting_dda_dir = os.path.join(self.dda_dir, 'wanting')
# self.dda_img_prefix = '{0}_0'.format(consts.DDA_FIELD)
# self.id_img_prefix = '{0}_0'.format(consts.IC_FIELD)
# self.bc_img_prefix = '{0}_0'.format(consts.BC_FIELD)
def add_arguments(self, parser):
parser.add_argument(
'--date',
default=datetime.date.today() - datetime.timedelta(days=1),
dest='date',
help='将要计算的日期，格式: 2018-01-01'
)
def handle(self, *args, **kwargs):
date = kwargs.get('date')
if isinstance(date, str):
if not re.match(r'\d{4}-\d{2}-\d{2}', date):
print('date format error')
return
date_str = date
else:
date_str = date.strftime('%Y-%m-%d')
target_date = datetime.datetime.strptime(date_str, '%Y-%m-%d')
next_date = target_date + datetime.timedelta(days=1)
do_month = True if next_date.day == 1 else False
wanting_querysets = None
excel_name = '{0}({1}).xlsx'.format(self.excel_name_base, date_str)
excel_path = os.path.join(self.dda_dir, excel_name)
wb = Workbook()
for sheet_name, header, query_fields in self.sheet_info:
ws = wb.create_sheet(sheet_name)
ws.append(header)
if sheet_name == '今日新建':
querysets = DDARecords.objects.filter(create_time__gt=target_date,
create_time__lt=next_date).values(*query_fields)
for queryset in querysets:
col_values = (
queryset.get('application_id', ''),
queryset.get('is_dda_found', 'False'),
queryset.get('dda_found_time', ''),
queryset.get('is_id_found', 'False'),
queryset.get('id_found_time', ''),
queryset.get('is_bc_found', 'False'),
queryset.get('bc_found_time', ''),
)
ws.append(col_values)
elif sheet_name == '今日完成':
querysets = DDARecords.objects.filter(update_time__gt=target_date,
update_time__lt=next_date,
all_found=True).values(*query_fields)
for queryset in querysets:
col_values = (
queryset.get('application_id', ''),
queryset.get('dda_found_time', ''),
queryset.get('id_found_time', ''),
queryset.get('bc_found_time', ''),
queryset.get('create_time', ''),
)
ws.append(col_values)
else:
querysets = DDARecords.objects.filter(all_found=False).values(*query_fields)
if do_month:
wanting_querysets = querysets
for queryset in querysets:
col_values = (
queryset.get('application_id', ''),
queryset.get('is_dda_found', 'False'),
queryset.get('dda_found_time', ''),
queryset.get('is_id_found', 'False'),
queryset.get('id_found_time', ''),
queryset.get('is_bc_found', 'False'),
queryset.get('bc_found_time', ''),
queryset.get('create_time', ''),
)
ws.append(col_values)
wb.remove(wb.get_sheet_by_name('Sheet'))
wb.save(excel_path)
if do_month:
target_month = target_date.month
target_year = target_date.year
month_excel_name = '{0}({1}-{2}).xlsx'.format(self.excel_name_base, target_year, target_month)
month_excel_path = os.path.join(self.dda_dir, month_excel_name)
month_wb = Workbook()
for sheet_name, header, query_fields in self.month_sheet_info:
ws = month_wb.create_sheet(sheet_name)
ws.append(header)
if sheet_name == '本月新建':
querysets = DDARecords.objects.filter(create_time__year=target_year,
create_time__month=target_month).values(*query_fields)
for queryset in querysets:
col_values = (
queryset.get('application_id', ''),
queryset.get('is_dda_found', 'False'),
queryset.get('dda_found_time', ''),
queryset.get('is_id_found', 'False'),
queryset.get('id_found_time', ''),
queryset.get('is_bc_found', 'False'),
queryset.get('bc_found_time', ''),
)
ws.append(col_values)
elif sheet_name == '本月完成':
querysets = DDARecords.objects.filter(update_time__year=target_year,
update_time__month=target_month,
all_found=True).values(*query_fields)
for queryset in querysets:
col_values = (
queryset.get('application_id', ''),
queryset.get('dda_found_time', ''),
queryset.get('id_found_time', ''),
queryset.get('bc_found_time', ''),
queryset.get('create_time', ''),
)
ws.append(col_values)
else:
for queryset in wanting_querysets:
col_values = (
queryset.get('application_id', ''),
queryset.get('is_dda_found', 'False'),
queryset.get('dda_found_time', ''),
queryset.get('is_id_found', 'False'),
queryset.get('id_found_time', ''),
queryset.get('is_bc_found', 'False'),
queryset.get('bc_found_time', ''),
queryset.get('create_time', ''),
)
ws.append(col_values)
month_wb.remove(month_wb.get_sheet_by_name('Sheet'))
month_wb.save(month_excel_path)