bigdataAPI/apps/IOTCard/views.py

from rest_framework import status
from rest_framework import viewsets
from rest_framework.response import Response
from django.http import HttpResponse
from rest_framework.decorators import action
from rest_framework.serializers import ValidationError

import requests
import uuid
import os
import datetime
from io import BytesIO
import pandas as pd
from django.utils.encoding import escape_uri_path

from .serializers import PlatSimInfoSerializer
from .models import PlatSimInfo
from utils.paginations import CustomPagination
from utils.utils import DeviceInfoUtils, GetSIMInfo, GetDashujuSiqingDeviceInfo


# Create your views here.
def time_dif(checkdatetime):
    # 获取传入时间和当前时间差
    nowdatetime = datetime.datetime.now()
    checkdatetime = datetime.datetime.strptime(checkdatetime, "%Y-%m-%d %H:%M:%S")

    timedif = nowdatetime - checkdatetime
    return timedif.days

def time_dif_new(stamp):
    nowdatetime = datetime.datetime.now()
    check = datetime.datetime.fromtimestamp(stamp)
    timedif = nowdatetime - check
    return timedif.days

class PlatformIOTCardViewSet(viewsets.ModelViewSet):
    serializer_class = PlatSimInfoSerializer
    pagination_class = CustomPagination
    queryset = PlatSimInfo.objects.all().order_by("-id")

    def list(self, request, *args, **kwargs):
        device_type = request.query_params.get("device_type")
        deviceId = request.query_params.get("deviceId")
        simId = request.query_params.get("simId")
        expire_start_time = request.query_params.get("expire_start_time")
        expire_end_time = request.query_params.get("expire_end_time")
        queryset = self.get_queryset()
        if device_type:
            queryset = queryset.filter(device_type=device_type)
        if deviceId:
            if len(deviceId.strip()) < 4:
                raise ValidationError("设备号长度不能小于4位")
            queryset = queryset.filter(deviceId__icontains=deviceId.strip())
        if simId:
            if len(simId.strip()) < 4:
                raise ValidationError("物联网卡号长度不能小于4位")
            queryset = queryset.filter(simId__icontains=simId.strip())
        if expire_start_time:
            queryset = queryset.filter(expiry_date__range=(expire_start_time, expire_end_time))
        page = self.paginate_queryset(queryset)
        serializer = self.get_serializer(page, many=True)
        return self.get_paginated_response(serializer.data)

    def create(self, request, *args, **kwargs):
        device_id = request.data.get("device_id").strip()
        simId = request.data.get("simId").strip()
        if len(simId) > 20 or len(simId) < 19:
            raise ValidationError("物联网卡长度介于19-20位")

        if self.get_queryset().filter(simId=simId).exists():
            raise ValidationError("该物联网卡号已存在,请核查!")

        if self.get_queryset().filter(deviceId=device_id, input_type=2).exists():
            raise ValidationError("该设备已存在手动录入类型,请核查!")

        device_info_list = DeviceInfoUtils().get_equip_list(
            d_id=device_id,
            isfullId=1
        )
        if device_info_list:
            device_info = device_info_list[0]
            device_type = device_info["device_type"]
            platform = device_info["plat"]
        else:
            device_type = ""
            platform = ""
        if device_type not in ["测报灯", "孢子仪"]:
            raise ValidationError("不支持该类型设备手动录入物联网卡！")
        if platform == "四情平台":
            platformid = 1
        elif platform == "大数据平台":
            platformid = 2
        else:
            raise ValidationError("平台不存在该设备ID,请核查！")
        requests.post(url="http://172.16.220.3/equip_simiccid", data={"e_id": device_id, "iccid": simId}, timeout=10)
        requests.post(url="http://127.0.0.1:8002/api/api_gateway?method=forecast.send_control.device_sim",
                      data={"device_id": device_id, "iccid": simId, "type": "change"}, timeout=10)
        sim_operators, account_status, active_date, data_plan, data_usage, data_balance, expiry_date = GetSIMInfo(
            simId).get_sim_info()
        request_data = {
            "simId": simId,
            "sim_operators": sim_operators,
            "input_type": 2,
            "deviceId": device_id,
            "device_type": device_type,
            "platform": platformid,
            "account_status": account_status,
            "active_date": active_date,
            "data_plan": data_plan,
            "data_usage": data_usage,
            "data_balance": data_balance,
            "expiry_date": expiry_date
        }
        serializer = self.get_serializer(data=request_data)
        serializer.is_valid(raise_exception=True)
        self.perform_create(serializer)
        headers = self.get_success_headers(serializer.data)
        return Response(serializer.data, status=status.HTTP_201_CREATED, headers=headers)

    def update(self, request, pk, *args, **kwargs):
        req = request.data.get("req")
        instance = self.get_object()
        if req == "upgrade":
            iccid = instance.simId
            sim_operators, account_status, active_date, data_plan, data_usage, data_balance, expiry_date = GetSIMInfo(
                iccid).get_sim_info()
            update_data = {
                "sim_operators": sim_operators,
                "account_status": account_status,
                "active_date": active_date,
                "data_plan": data_plan,
                "data_usage": data_usage,
                "data_balance": data_balance,
                "expiry_date": expiry_date
            }
        elif req == "change":
            deviceId = instance.deviceId
            if instance.input_type == 1:
                raise ValidationError("设备读取物联网卡禁止更改")
            simId = request.data.get("simId").strip()
            if len(simId) > 20 or len(simId) < 19:
                raise ValidationError("物联网卡长度介于19-20位")
            if self.get_queryset().filter(simId=simId).exists():
                raise ValidationError("预更改卡号已存在,请核对!")
            try:
                requests.post(url="http://172.16.220.3/equip_simiccid", data={"e_id": deviceId, "iccid": simId},
                              timeout=10)
                requests.post(url="http://127.0.0.1:8002/api/api_gateway?method=forecast.send_control.device_sim",
                              data={"device_id": deviceId, "iccid": simId, "type": "change"}, timeout=10)
            except:
                pass
            sim_operators, account_status, active_date, data_plan, data_usage, data_balance, expiry_date = GetSIMInfo(
                simId).get_sim_info()
            update_data = {
                "simId": simId,
                "sim_operators": sim_operators,
                "account_status": account_status,
                "active_date": active_date,
                "data_plan": data_plan,
                "data_usage": data_usage,
                "data_balance": data_balance,
                "expiry_date": expiry_date
            }
        else:
            raise ValidationError("未识别req参数")
        serializer = self.get_serializer(instance, data=update_data, partial=True)
        serializer.is_valid(raise_exception=True)
        self.perform_update(serializer)
        return Response(serializer.data)

    @action(methods=['get'], detail=False, url_path='get_device_type', url_name='get_device_type')
    def get_device_type(self, request, *args, **kwargs):
        queryset = PlatSimInfo.objects.raw("SELECT id,device_type FROM plat_sim_info GROUP BY device_type;")
        data = []
        for i in queryset:
            data.append(i.device_type)
        return Response(data)

    @action(methods=['get'], detail=False, url_path='down_excel', url_name='down_excel')
    def down_excel(self, request, *args, **kwargs):
        filename = request.query_params.get("filename")
        if filename == "发货表示例模板.xlsx":
            save_data = dict(设备ID=[])
        elif filename == "合宙续存流量卡筛选模板.xlsx":
            save_data = dict(ICCID=[], 续费价格=[], 当月消耗流量=[])
        elif filename == "SIMBoss续存流量卡筛选模板.xlsx":
            save_data = dict(ICCID=[], 筛查月份1=[], 筛查月份2=[], 筛查月份3=[], 筛查月份4=[], 筛查月份5=[],
                             筛查月份6=[], 筛查月份7=[], 筛查月份8=[], 筛查月份9=[])
        else:
            now_dir = os.path.dirname(__file__)
            file_path = os.path.join(now_dir, "excel_folder", filename)
            if os.path.exists(file_path) == False:
                raise ValidationError("文件不存在,请确认文件名")
            else:
                with open(file_path, 'rb') as f:
                    data = f.read()
            response = HttpResponse(data, content_type="application/vnd.ms-excel")
            response['Content-Disposition'] = 'attachment;filename={}'.format(escape_uri_path(filename))
            os.remove(file_path)
            return response
        df = pd.DataFrame(data=save_data)
        output = BytesIO()
        df.to_excel(output, index=False)
        output.seek(0)
        response = HttpResponse(content_type='application/vnd.ms-excel')
        response['Content-Disposition'] = 'attachment;filename={}'.format(escape_uri_path(filename))
        response.write(output.getvalue())
        return response

    @action(methods=['post'], detail=False, url_path='delivery_card_filter', url_name='delivery_card_filter')
    def card_delivery_filter(self, request, *args, **kwargs):
        file = request.FILES.get('file')
        file_dataframe = pd.read_excel(file)
        save_data = dict(deviceid=[], platform=[], device_type=[], order_id=[], owen_user=[], status=[], location=[], sim_1=[],is_recharge_1=[], sim_2=[], is_recharge_2=[])

        for deviceId in file_dataframe.get("设备ID",[]):
            if isinstance(deviceId, int):
                deviceId = str(int(deviceId))
            else:
                deviceId = deviceId.strip()
            save_data["deviceid"].append(deviceId)
            device_info_list = DeviceInfoUtils().get_equip_list(d_id=deviceId, isfullId=1)
            save_data["platform"].append(device_info_list[0]["plat"] if device_info_list else "未知")
            save_data["device_type"].append(device_info_list[0]["device_type"] if device_info_list else "")
            save_data["order_id"].append(device_info_list[0]["order_id"] if device_info_list else "")
            save_data["owen_user"].append(device_info_list[0]["owen_user"] if device_info_list else "")
            save_data["status"].append(device_info_list[0]["status"] if device_info_list else "")
            save_data["location"].append(device_info_list[0]["location"] if device_info_list else "")

            sim_1_queryset = self.get_queryset().filter(deviceId=deviceId, input_type=1)
            if sim_1_queryset.count() == 0:
                save_data["sim_1"].append("暂无")
                save_data["is_recharge_1"].append("")
            else:
                sim_1_obj = sim_1_queryset[0]
                save_data["sim_1"].append(sim_1_obj.simId)
                if sim_1_obj.expiry_date == "未知":
                    save_data["is_recharge_1"].append("未知")
                else:
                    time_difference = time_dif(sim_1_obj.expiry_date)
                    if time_difference >= -180:
                        save_data["is_recharge_1"].append("是")
                    else:
                        save_data["is_recharge_1"].append("否")

            sim_2_queryset = self.get_queryset().filter(deviceId=deviceId, input_type=2)
            if sim_2_queryset.count() == 0:
                save_data["sim_2"].append("暂无")
                save_data["is_recharge_2"].append("")
            else:
                sim_2_obj = sim_2_queryset[0]
                save_data["sim_2"].append(sim_2_obj.simId)
                if sim_2_obj.expiry_date == "未知":
                    save_data["is_recharge_2"].append("未知")
                else:
                    time_difference = time_dif(sim_2_obj.expiry_date)
                    if time_difference >= -180:
                        save_data["is_recharge_2"].append("是")
                    else:
                        save_data["is_recharge_2"].append("否")

        df = pd.DataFrame(data=save_data)
        save_filename = str(uuid.uuid4()) + ".xlsx"
        now_dir = os.path.dirname(__file__)
        save_path = os.path.join(now_dir, "excel_folder", save_filename)
        df.to_excel(save_path, index=False, header=["设备ID", "所属平台", "设备类型", "任务单号", "所属用户", "设备状态", "设备位置", "设备自读SIM卡", "是否需要充值", "人工录入SIM卡", "是否需要充值"])
        return Response(save_filename)

    @action(methods=['post'], detail=False, url_path='xucun_card_filter', url_name='xucun_card_filter')
    def xucun_card_filter(self, request, *args, **kwargs):
        simtype = request.data.get("simtype")
        renew_file = request.FILES.get("file")
        file_dataframe = pd.read_excel(renew_file)
        dashuju_siqing = GetDashujuSiqingDeviceInfo()
        if simtype == "1":  # 合宙卡
            save_data = dict(ICCID=[],续费价格=[],是否需要充值=[],设备ID=[],所属平台=[],设备类型=[],任务单号=[],所属用户=[],设备状态=[],设备位置=[])
            sim_list = list(file_dataframe["ICCID"])
            price_list = list(file_dataframe["续费价格"])
            usage_list = list(file_dataframe["当月消耗流量"])
            for index, usage in enumerate(usage_list):
                save_data["ICCID"].append(str(sim_list[index]))
                save_data["续费价格"].append(str(price_list[index]))
                card_queryset = self.get_queryset().filter(simId=str(sim_list[index]).strip())
                if card_queryset.exists():
                    card_obj = card_queryset.last()
                    deviceId = card_obj.deviceId
                    save_data["设备ID"].append(deviceId)
                    # device_info_list = DeviceInfoUtils().get_equip_list(d_id=deviceId, isfullId=1)
                    device_info_list = dashuju_siqing.get_together(device_id=deviceId)
                    if device_info_list:
                        device_info = device_info_list
                        uptime = device_info["uptime"]
                        save_data["所属平台"].append(device_info["plat"])
                        save_data["设备类型"].append(device_info["device_type"])
                        save_data["任务单号"].append(device_info["order_id"])
                        save_data["所属用户"].append(device_info["owner_user"])
                        save_data["设备状态"].append(device_info["status"])
                        save_data["设备位置"].append(device_info["location"])
                    else:
                        uptime = ""
                        save_data["所属平台"].append("")
                        save_data["设备类型"].append("")
                        save_data["任务单号"].append("")
                        save_data["所属用户"].append("")
                        save_data["设备状态"].append("")
                        save_data["设备位置"].append("")
                else:
                    uptime = ""
                    save_data["设备ID"].append("")
                    save_data["所属平台"].append("")
                    save_data["设备类型"].append("")
                    save_data["任务单号"].append("")
                    save_data["所属用户"].append("")
                    save_data["设备状态"].append("")
                    save_data["设备位置"].append("")
                try:
                    usage_value = float(usage)
                except:
                    usage_value = ""
                if usage_value == 0 or usage_value == "":
                    if uptime:
                        time_diff_days = time_dif_new(uptime)
                        if time_diff_days > 270:
                            save_data["是否需要充值"].append("否")
                        else:
                            save_data["是否需要充值"].append("是")
                    else:
                        save_data["是否需要充值"].append("未知")
                else:
                    save_data["是否需要充值"].append("是")
        else:  # SIMboss
            save_data = dict(ICCID=[], 是否需要充值=[],设备ID=[],所属平台=[],设备类型=[],任务单号=[],所属用户=[],设备状态=[],设备位置=[])
            for index, sim in enumerate(file_dataframe["ICCID"]):
                save_data["ICCID"].append(str(sim))
                card_queryset = self.get_queryset().filter(simId=str(sim).strip())
                if card_queryset.exists():
                    try:
                        card_obj = card_queryset.last()
                        deviceId = card_obj.deviceId
                        save_data["设备ID"].append(deviceId)
                        # device_info_list = DeviceInfoUtils().get_equip_list(d_id=deviceId, isfullId=1)
                        device_info_list = dashuju_siqing.get_together(device_id=deviceId)
                        if device_info_list:
                            device_info = device_info_list
                            save_data["所属平台"].append(device_info["plat"])
                            save_data["设备类型"].append(device_info["device_type"])
                            save_data["任务单号"].append(device_info["order_id"])
                            save_data["所属用户"].append(device_info["owner_user"])
                            save_data["设备状态"].append(device_info["status"])
                            save_data["设备位置"].append(device_info["location"])
                        else:
                            save_data["所属平台"].append("")
                            save_data["设备类型"].append("")
                            save_data["任务单号"].append("")
                            save_data["所属用户"].append("")
                            save_data["设备状态"].append("")
                            save_data["设备位置"].append("")
                    except Exception as e:
                        save_data["设备ID"].append("")
                        save_data["所属平台"].append("")
                        save_data["设备类型"].append("")
                        save_data["任务单号"].append("")
                        save_data["所属用户"].append("")
                        save_data["设备状态"].append("")
                        save_data["设备位置"].append("")
                else:
                    save_data["设备ID"].append("")
                    save_data["所属平台"].append("")
                    save_data["设备类型"].append("")
                    save_data["任务单号"].append("")
                    save_data["所属用户"].append("")
                    save_data["设备状态"].append("")
                    save_data["设备位置"].append("")
                status = 0
                for i in range(9):
                    if float(file_dataframe["筛查月份{}".format(str(i + 1))][index]) > 1:
                        status = 1
                        break
                if status == 1:
                    save_data["是否需要充值"].append("是")
                else:
                    save_data["是否需要充值"].append("否")
        dashuju_siqing.conn.close()
        df = pd.DataFrame(data=save_data)
        save_filename = str(uuid.uuid4()) + ".xlsx"
        now_dir = os.path.dirname(__file__)
        save_path = os.path.join(now_dir, "excel_folder", save_filename)
        print(save_path)
        df.to_excel(save_path, index=False)
        return Response(save_filename)

    @action(methods=['post'], detail=False, url_path='inquiries', url_name='inquiries')
    def inquiries(self, request):
        iccid = request.data.get("iccid")
        if iccid:
            sim_operators, account_status, active_date, data_plan, data_usage, data_balance, expiry_date = GetSIMInfo(
                iccid).get_sim_info()
            data = {
                "simId": iccid,
                "sim_operators": "合宙" if sim_operators == 1 else "SIMBoss" if sim_operators == 2 else "未知",
                "account_status": account_status,
                "active_date": active_date,
                "data_plan": data_plan,
                "data_usage": data_usage,
                "data_balance": data_balance,
                "expiry_date": expiry_date
            }
            return Response(data)
        else:
            raise ValidationError("iccid参数缺失")