import os
import uuid

import xlrd

from console.sim_card.models import SimCard
from django.conf import settings

from common.utils.models import BaseConnection


def import_sim_card(file):
    parent_path = os.path.dirname(settings.BASE_DIR)
    url = os.path.join(parent_path, "files", "{}.xlsx".format(uuid.uuid4()))
    with open(url, "wb+") as f:
        for i in file.chunks():
            f.write(i)

    book = xlrd.open_workbook(url)
    sheet = book.sheet_by_index(0)
    rows = sheet.nrows
    sim_card = []
    for i in range(1, rows):
        sim_card.append(
            SimCard(
                iccid=sheet.cell_value(i, 0),
                sim_number=sheet.cell_value(i, 1),
                start_time=str(sheet.cell_value(i, 2)),
                end_time=str(sheet.cell_value(i, 3)),
                activate_time=str(sheet.cell_value(i, 4)),
                flow=sheet.cell_value(i, 5),
                operator=sheet.cell_value(i, 6),
                supplier=sheet.cell_value(i, 7),
                status=sheet.cell_value(i, 8),
                validity_period=sheet.cell_value(i, 9)
            ))
    os.remove(url)
    SimCard.objects.bulk_create(sim_card)


def get_sim_card_list(**kwargs):
    conn = BaseConnection()
    order_by = kwargs.get("order_by")
    sql = """
    select A.*,B.imei,D.name,E.number from sim_card A 
    left join monitor_device B on A.sim_number=B.iccid
    left join monitor_points C on B.monitor_point_id=C.id
    left join customer D on C.customer_id=D.id
    left join electricity_user E on C.electricity_user_id=E.id
    where 1=1
    """
    if kwargs.get("keyword"):
        sql += """ and (A.iccid like "%{}%" or A.sim_number like "%{}%" or A.operator like "%{}%"
         or A.supplier like "%{}%" or D.name like "%{}%")""".format(
            kwargs.get("keyword"), kwargs.get("keyword"), kwargs.get("keyword"), kwargs.get("keyword"),
            kwargs.get("keyword"))
    if kwargs.get("end_time"):
        sql += """ and date_format(A.end_time,"%Y-%m")='{}'""".format(kwargs.get("end_time"))

    if order_by:
        if order_by.startswith("-"):
            order_by = order_by.lstrip("-") + " desc"
        sql += """ order by {}""".format(order_by)
    return conn.query(sql)
