import itertools import os import time from datetime import datetime from dateutil import parser import xlsxwriter from django.http import HttpResponse from DepositAPI import settings from DepositAPI.api.Api import BaseApiView from DepositAPI.model.BaseModel import BaseModel from DepositAPI.model.Truck import Truck as DBModel class Truck(BaseApiView, BaseModel): def __init__(self): self.modelClass = DBModel() def export_excel_detail(self, request, pk): self.get_request_info(request) self.get_common_query_string(request) timenow = time.time() file_name = "XeHang_%s.xlsx" % (timenow) if not os.path.exists(settings.TEMP_DOWNLOAD_PATH): os.makedirs(settings.TEMP_DOWNLOAD_PATH) file_path = os.path.join(settings.TEMP_DOWNLOAD_PATH, file_name) # Create a workbook and add a worksheet. workbook = xlsxwriter.Workbook(file_path) # Start from the first cell. Rows and columns are zero indexed. row = itertools.count(0) col = itertools.count(0) # Define format cell cell_title = workbook.add_format({'bold': True, 'align': 'center', 'valign': 'vcenter', 'font_name': 'Calibri', 'font_size': 14}) align_left = workbook.add_format({'align': 'left', 'valign': 'vcenter', 'font_name': 'Calibri', 'font_size': 12, 'border': 1}) cell_header = workbook.add_format({'bold': True, 'align': 'left', 'valign': 'vcenter', 'font_name': 'Calibri', 'font_size': 12, 'border': 1}) cell_header_center = workbook.add_format({'bold': True, 'align': 'center', 'valign': 'vcenter', 'font_name': 'Calibri', 'font_size': 12, 'border': 1}) currency = workbook.add_format({'align': 'right', 'valign': 'vcenter', 'num_format': '#,##0.00', 'font_name': 'Calibri', 'font_size': 12, 'border': 1}) number = workbook.add_format({'align': 'right', 'valign': 'vcenter', 'num_format': '#,##0', 'font_name': 'Calibri', 'font_size': 12, 'border': 1}) number_bold = workbook.add_format({'bold': True, 'align': 'right', 'valign': 'vcenter', 'num_format': '#,##0', 'font_name': 'Calibri', 'font_size': 12, 'border': 1}) # Get list item to export sql_query = f"""SELECT * FROM (SELECT o.ID, o.OrderCode, o.LicensePlate, o.OrderDate, o.NumberOfProduct, o.Weight, o.Volume, o.Revenue, p.UserName, p.ProductNameCN, p.ProductNameVN, p.`Code`, p.SuffixBill, p.Deposit, p.ExchangeRate, p.Weight as BillWeight, p.Volume as BillVolume, p.NumberOfPack, fn_enum(p.ReceiveStatus, 'receive_type') as ReceiveStatus FROM tbl_truck AS o LEFT JOIN tbl_product AS p ON o.ID = p.TruckID) as t WHERE t.ID = {pk}""" items, total = self.modelClass.get_list_objects(sql_query, self.sortBy, self.descending, 1000000, 0, True) worksheet = workbook.add_worksheet('Danh sách xe hàng') # Initial header row_index = next(row) worksheet.write(row_index, next(col), 'Mã hiệu', cell_header) worksheet.write(row_index, next(col), 'Biển số xe', cell_header) worksheet.write(row_index, next(col), 'Ngày xếp xe', cell_header) worksheet.write(row_index, next(col), 'Số đơn hàng', cell_header) worksheet.write(row_index, next(col), 'Cân nặng', cell_header) worksheet.write(row_index, next(col), 'Thể tích', cell_header) worksheet.write(row_index, next(col), 'Doanh thu', cell_header) worksheet.write(row_index, next(col), 'Mã KH', cell_header) worksheet.write(row_index, next(col), 'Tên hàng TQ', cell_header) worksheet.write(row_index, next(col), 'Tên hàng VN', cell_header) worksheet.write(row_index, next(col), 'Mã vào kho', cell_header) worksheet.write(row_index, next(col), 'Vận đơn', cell_header) worksheet.write(row_index, next(col), 'Chi tệ', cell_header) worksheet.write(row_index, next(col), 'Tỷ giá', cell_header) worksheet.write(row_index, next(col), 'Cân nặng', cell_header) worksheet.write(row_index, next(col), 'Thể tích', cell_header) worksheet.write(row_index, next(col), 'Tổng kiện', cell_header) worksheet.write(row_index, next(col), 'Hạ Cont', cell_header) last_order_code = '' for item in items: col = itertools.count(0) row_index = next(row) if last_order_code != item['OrderCode']: last_order_code = item['OrderCode'] worksheet.write(row_index, next(col), item['OrderCode'], align_left) worksheet.write(row_index, next(col), item['LicensePlate'], align_left) worksheet.write(row_index, next(col), datetime.strptime(item['OrderDate'], '%Y-%m-%dT%H:%M:%S.000Z').strftime('%d-%m-%Y') if item['OrderDate'] is not None else '', align_left) worksheet.write(row_index, next(col), item['NumberOfProduct'], number) worksheet.write(row_index, next(col), item['Weight'], number) worksheet.write(row_index, next(col), item['Volume'], number) worksheet.write(row_index, next(col), item['Revenue'], number) else: worksheet.write(row_index, next(col), '', align_left) worksheet.write(row_index, next(col), '', align_left) worksheet.write(row_index, next(col), '', align_left) worksheet.write(row_index, next(col), '', align_left) worksheet.write(row_index, next(col), '', align_left) worksheet.write(row_index, next(col), '', align_left) worksheet.write(row_index, next(col), '', align_left) worksheet.write(row_index, next(col), item['UserName'], align_left) worksheet.write(row_index, next(col), item['ProductNameCN'], align_left) worksheet.write(row_index, next(col), item['ProductNameVN'], align_left) worksheet.write(row_index, next(col), item['Code'], align_left) worksheet.write(row_index, next(col), item['SuffixBill'], align_left) worksheet.write(row_index, next(col), item['Deposit'], number) worksheet.write(row_index, next(col), item['ExchangeRate'], number) worksheet.write(row_index, next(col), item['BillWeight'], number) worksheet.write(row_index, next(col), item['BillVolume'], number) worksheet.write(row_index, next(col), item['NumberOfPack'], number) worksheet.write(row_index, next(col), item['ReceiveStatus'], align_left) workbook.close() f = open(file_path, 'rb') response = HttpResponse(f, content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") response['Content-Disposition'] = 'attachment; filename=%s' % file_name try: os.remove(file_path) except OSError: pass return response def export_list(self, request): self.get_request_info(request) self.get_common_query_string(request) timenow = time.time() file_name = "XeHang_%s.xlsx" % (timenow) if not os.path.exists(settings.TEMP_DOWNLOAD_PATH): os.makedirs(settings.TEMP_DOWNLOAD_PATH) file_path = os.path.join(settings.TEMP_DOWNLOAD_PATH, file_name) # Create a workbook and add a worksheet. workbook = xlsxwriter.Workbook(file_path) # Start from the first cell. Rows and columns are zero indexed. row = itertools.count(0) col = itertools.count(0) # Define format cell cell_title = workbook.add_format({'bold': True, 'align': 'center', 'valign': 'vcenter', 'font_name': 'Calibri', 'font_size': 14}) align_left = workbook.add_format({'align': 'left', 'valign': 'vcenter', 'font_name': 'Calibri', 'font_size': 12, 'border': 1}) cell_header = workbook.add_format({'bold': True, 'align': 'left', 'valign': 'vcenter', 'font_name': 'Calibri', 'font_size': 12, 'border': 1}) cell_header_center = workbook.add_format({'bold': True, 'align': 'center', 'valign': 'vcenter', 'font_name': 'Calibri', 'font_size': 12, 'border': 1}) currency = workbook.add_format({'align': 'right', 'valign': 'vcenter', 'num_format': '#,##0.00', 'font_name': 'Calibri', 'font_size': 12, 'border': 1}) number = workbook.add_format({'align': 'right', 'valign': 'vcenter', 'num_format': '#,##0', 'font_name': 'Calibri', 'font_size': 12, 'border': 1}) number_bold = workbook.add_format({'bold': True, 'align': 'right', 'valign': 'vcenter', 'num_format': '#,##0', 'font_name': 'Calibri', 'font_size': 12, 'border': 1}) # Get list item to export where = self.build_query_by_filter(self.filter, self.quickFilter, 't') sql_query = """SELECT * FROM (SELECT o.ID, o.LicensePlate, o.OrderDate, o.StoreDate, o.NumberOfProduct, o.NumberOfPack as TotalNumberOfPack, o.Weight, o.Volume, p.UserName, p.ProductNameCN, p.`Code`, p.SuffixBill, p.Deposit, p.Weight as BillWeight, p.Volume as BillVolume, op.NumberOfPack, o.LicensePlateVN, o.Caller, o.TranferDate, o.ReleaseTruckDate FROM tbl_truck AS o LEFT JOIN tbl_truck_product as op ON op.TruckID = o.ID LEFT JOIN tbl_product AS p ON p.ID = op.ProductID) as t %s""" % where items, total = self.modelClass.get_list_objects(sql_query, self.sortBy, self.descending, 1000000, 0, True) worksheet = workbook.add_worksheet('Danh sách xe hàng') # Initial header row_index = next(row) worksheet.write(row_index, next(col), 'Biển số xe', cell_header) worksheet.write(row_index, next(col), 'Biển số xe (VN)', cell_header) worksheet.write(row_index, next(col), 'Người gọi xe', cell_header) worksheet.write(row_index, next(col), 'Ngày xếp xe', cell_header) worksheet.write(row_index, next(col), 'Ngày bốc', cell_header) worksheet.write(row_index, next(col), 'Ngày trả xe', cell_header) worksheet.write(row_index, next(col), 'Ngày nhập kho', cell_header) worksheet.write(row_index, next(col), 'Số đơn hàng', cell_header) worksheet.write(row_index, next(col), 'Mã KH', cell_header) worksheet.write(row_index, next(col), 'Tên hàng', cell_header) worksheet.write(row_index, next(col), 'Mã vào kho', cell_header) worksheet.write(row_index, next(col), 'Vận đơn', cell_header) worksheet.write(row_index, next(col), 'Chi tệ', cell_header) worksheet.write(row_index, next(col), 'Cân nặng', cell_header) worksheet.write(row_index, next(col), 'Thể tích', cell_header) worksheet.write(row_index, next(col), 'Tổng kiện', cell_header) last_order_code = 0 for item in items: col = itertools.count(0) row_index = next(row) if last_order_code != item['ID']: last_order_code = item['ID'] worksheet.write(row_index, next(col), item['LicensePlate'], align_left) worksheet.write(row_index, next(col), item['LicensePlateVN'], align_left) worksheet.write(row_index, next(col), item['Caller'], align_left) worksheet.write(row_index, next(col), parser.parse(item['OrderDate']).strftime('%d-%m-%Y') if item['OrderDate'] is not None else '', align_left) worksheet.write(row_index, next(col), parser.parse(item['TranferDate']).strftime('%d-%m-%Y') if item['TranferDate'] is not None else '', align_left) worksheet.write(row_index, next(col), parser.parse(item['ReleaseTruckDate']).strftime('%d-%m-%Y') if item['ReleaseTruckDate'] is not None else '', align_left) worksheet.write(row_index, next(col), parser.parse(item['StoreDate']).strftime('%d-%m-%Y') if item['StoreDate'] is not None else '', align_left) worksheet.write(row_index, next(col), item['NumberOfProduct'], number) worksheet.write(row_index, next(col), '', align_left) worksheet.write(row_index, next(col), '', align_left) worksheet.write(row_index, next(col), '', align_left) worksheet.write(row_index, next(col), '', align_left) worksheet.write(row_index, next(col), '', align_left) worksheet.write(row_index, next(col), item['Weight'], currency) worksheet.write(row_index, next(col), item['Volume'], currency) worksheet.write(row_index, next(col), item['TotalNumberOfPack'], number) row_index = next(row) col = itertools.count(0) for _ in range(8): worksheet.write(row_index, next(col), '', align_left) worksheet.write(row_index, next(col), item['UserName'], align_left) worksheet.write(row_index, next(col), item['ProductNameCN'], align_left) worksheet.write(row_index, next(col), item['Code'], align_left) worksheet.write(row_index, next(col), item['SuffixBill'], align_left) worksheet.write(row_index, next(col), item['Deposit'], currency) worksheet.write(row_index, next(col), item['BillWeight'], currency) worksheet.write(row_index, next(col), item['BillVolume'], currency) worksheet.write(row_index, next(col), item['NumberOfPack'], number) workbook.close() f = open(file_path, 'rb') response = HttpResponse(f, content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") response['Content-Disposition'] = 'attachment; filename=%s' % file_name try: os.remove(file_path) except OSError: pass return response