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.Bill import Bill as DBModel
from DepositAPI.api.Response import SuccessResponse, ErrorResponse
from DepositAPI.common.Utility import get_prop, del_prop
from DepositAPI.common.const.hpodconst import ROLE_TYPE

class Bill(BaseApiView, BaseModel):
    def __init__(self):
        self.modelClass = DBModel()
    def aggregate_data(self, request):
        self.get_request_info(request)
        self.get_common_query_string(request)
        items, totalRow = self.modelClass.get_list(self.user_id, self.role_id, self.filter, self.quickFilter, self.sortBy, self.descending, self.rowsPerPage, self.offset)
        return SuccessResponse(data=items, total=totalRow, offset=self.offset, rowsPerPage=self.rowsPerPage)
    def get_item_detail(self, request, pk):
        self.get_request_info(request)
        self.get_common_query_string(request)
        item = self.modelClass.get_detail_for_user(pk, self.user_id, self.role_id)
        if item is None:
            return ErrorResponse(status=404, reason="Không tìm thấy phiếu xuất kho.")
        return SuccessResponse(data=item)
    def get_list_by_ref(self, request):
        self.get_request_info(request)
        self.get_common_query_string(request)
        ref_id = self.params.get("ref_id", "")
        items, totalRow = self.modelClass.get_list_by_ref(self, ref_id, self.sortBy, self.descending, self.rowsPerPage, self.offset)
        return SuccessResponse(
            data=items, total=totalRow, offset=self.offset, rowsPerPage=self.rowsPerPage
        )
    def edit_data(self, request, pk):
        bill_code = get_prop(request, 'BillCode', False, None)
        if bill_code is not None:
            bill_code = bill_code.strip()
            request.data['BillCode'] = bill_code
            existed = self.modelClass.get_detail_by_object({'BillCode': bill_code})
            if existed is not None and len(existed) > 0 and existed['ID'] != int(pk):
                return ErrorResponse(status=406, reason="Mã phiếu xuất đã tồn tại.")
        ret = self.modelClass.update(request, pk)
        return SuccessResponse(data=ret)

    def add_entry(self, request):
        bill_code = get_prop(request, 'BillCode', False, None)
        if bill_code is not None:
            bill_code = bill_code.strip()
            request.data['BillCode'] = bill_code
            existed = self.modelClass.get_detail_by_object({'BillCode': bill_code})
            if existed is not None and len(existed) > 0:
                return ErrorResponse(status=406, reason="Mã phiếu xuất đã tồn tại.")
        
        item = self.modelClass.insert(request)
        return SuccessResponse( data=item )

    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')
        params = {}
        if self.role_id == ROLE_TYPE.Sale:
            where += " AND t.CustomerSaleID = :sale_id"
            params['sale_id'] = self.user_id
        sql_query = """SELECT * FROM (SELECT o.ID, o.BillCode, o.ExportDate, o.NumberOfCode, o.NumberOfPack, o.Weight, o.Volume, o.Deposit, o.FeeShipVND, o.DiscountAmount, o.AmountVND, o.LineAmount,
                        p.UserName as CustomerName, p.ProductNameCN, p.`Code`, p.SuffixBill, p.Deposit as BillDeposit, p.Weight as BillWeight, p.Volume as BillVolume, op.NumberOfPack as BillNumberOfPack, u.SaleID AS CustomerSaleID
                        FROM tbl_bill AS o
                        LEFT JOIN tbl_bill_product as op ON op.BillID = o.ID
                        LEFT JOIN tbl_product AS p ON p.ID = op.ProductID
                        LEFT JOIN tbl_user AS u ON u.ID = o.UID) as t %s""" % where
        items, total = self.modelClass.get_list_objects(
            sql_query, self.sortBy, self.descending, 1000000, 0, True,
            params=params
        )
        worksheet = workbook.add_worksheet('Danh sách xe hàng')
        # Initial header
        row_index = next(row)
        worksheet.write(row_index, next(col), 'Mã phiếu', cell_header)
        worksheet.write(row_index, next(col), 'Ngày xuất', cell_header)
        worksheet.write(row_index, next(col), 'Tổng bill', cell_header)
        worksheet.write(row_index, next(col), 'Phí ship', cell_header)
        worksheet.write(row_index, next(col), 'Tổng tiền', cell_header)
        worksheet.write(row_index, next(col), 'Chiết khấu', cell_header)
        worksheet.write(row_index, next(col), 'Thành tiền', 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['BillCode'], align_left)
                worksheet.write(row_index, next(col), parser.parse(item['ExportDate']).strftime('%d-%m-%Y') if item['ExportDate'] is not None else '', align_left)
                worksheet.write(row_index, next(col), item['NumberOfCode'], number)
                worksheet.write(row_index, next(col), item['FeeShipVND'], number)
                worksheet.write(row_index, next(col), item['AmountVND'], number)
                worksheet.write(row_index, next(col), item['DiscountAmount'], number)
                worksheet.write(row_index, next(col), item['LineAmount'], 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), item['Deposit'], currency)
                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['NumberOfPack'], number)
                row_index = next(row)
                col = itertools.count(0)

            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['CustomerName'], 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['BillDeposit'], 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['BillNumberOfPack'], 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