AnsjerUIDManage/Controller/FileController.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import base64
import json
import logging
import os
import time
import zipfile

import xlwt
from django.db.models import Count

from django.http import StreamingHttpResponse, HttpResponse, QueryDict
from django.utils.decorators import method_decorator
from django.views import View
from django.views.decorators.csrf import csrf_exempt
from django.db import transaction

from AnsjerUIDManage.config import BASE_DIR
from Model.models import UIDModel, UserUIDModel, UserModel, LogModel, MacModel, OrderTaskModel, OrderUIDModel, \
    SerialNumberModel, OrderSerialNumberModel, UserSerialNumberModel, CompanySerialModel
from Object.RedisObject import RedisObject
from Object.TokenObject import TokenObject
from Object.ResponseObject import ResponseObject
from Service.CommonService import CommonService
from bulk_update.helper import bulk_update
from Object.S3Email import S3Email


class UploadUIDFileView(View):

    @method_decorator(csrf_exempt)
    def dispatch(self, request, *args, **kwargs):
        return super(UploadUIDFileView, self).dispatch(request, *args, **kwargs)

    def get(self, request, *args, **kwargs):
        request.encoding = 'utf-8'
        request_dict = request.GET
        fileName = request.FILES.get('fileName', None)
        return self.validate(fileName, request_dict, request)

    def post(self, request, *args, **kwargs):
        request.encoding = 'utf-8'
        request_dict = request.POST
        fileName = request.FILES.get('fileName', None)
        return self.validate(fileName, request_dict, request)

    def validate(self, fileName, request_dict, request):
        token = request_dict.get('token', None)
        area = request_dict.get('area', None)
        isReset = request_dict.get('isReset', 0)
        content = request_dict.get('fileName', None)
        print('content')
        print(content)

        content = base64.b64decode(content).decode().strip()
        content = content[3:(len(content) - 3)]
        uids = content.split('\n')
        # print(uids)
        # print(len(uids))

        response = ResponseObject()

        tko = TokenObject(token)
        if tko.code != 0:
            return response.json(tko.code)

        # if not fileName and not area:
        #     return response.json(444, 'fileName,area')

        data = {}
        duplicate = []
        for line in uids:

            if len(line) < 20:
                continue

            if data.__contains__(line):
                duplicate.append(line)
            else:
                data[line] = ''

        if isReset == 0:
            return self.do_insert(data, duplicate, area, response, request, tko)
        else:
            return self.do_reset(data, response, area, request, tko)

    @transaction.atomic
    def do_insert(self, data, duplicate, area, response, request, token):
        bulk = []
        count = 0
        add_time = int(time.time())
        update_time = int(time.time())
        keys = data.keys()

        # 获取最新的mac，判断分配到哪里
        mac = MacModel.objects.filter().values('id', 'value', 'is_active')[0]
        current_mac = mac['value']
        username = 'cspublic@ansjer.com'
        if current_mac[-8:] == '0F:42:40':  # 一组一共1048576个，此mac是第100w个时
            sys_msg_text = "当前旧UID管理系统mac地址已分配到" + current_mac + "，此mac地址是当前组的第100w个，还剩下48576个可分配，mac地址即将用完。"
            S3Email().faEmail(sys_msg_text, username)
        elif current_mac[-8:] == '0F:90:60':  # 一组一共1048576个，此mac是第102w个时
            sys_msg_text = "当前旧UID管理系统mac地址已分配到" + current_mac + "，此mac地址是当前组的第102w个，还剩下28576个可分配，mac地址即将用完。"
            S3Email().faEmail(sys_msg_text, username)
        elif not mac['is_active']:
            return response.json(175)
        elif current_mac[-8:] == '0F:FF:FF':
            MacModel.objects.filter().update(is_active=False)  # 更改mac可使用的状态，当再此调用接口时使用上面条件进行阻止
            sys_msg_text = "当前旧UID管理系统mac地址已分配到" + current_mac + "，mac地址已分配使用完，请更换分组。"
            S3Email().faEmail(sys_msg_text, username)
            return response.json(175)
        if not mac['is_active']:
            return response.json(175)
        # redisObject = RedisObject(db=3)
        key = ''
        tmpMac = mac['value']
        savePoint = None
        for item in keys:
            key = item.strip()
            # value = redisObject.get_data(key)
            # if value is False:
            #     # redisObject.set_data(key, '1', 600)
            # else:
            #     duplicate.append(key)
            #     continue
            bulk.append(UIDModel(
                uid=item.strip(),
                uid_extra='',
                status=0,
                add_time=add_time,
                update_time=update_time,
                area=area,
                mac=mac['value']
            ))
            try:
                try:
                    if (count % 5000) == 0:
                        savePoint = transaction.savepoint()
                        UIDModel.objects.bulk_create(bulk)
                        bulk.clear()
                        data = {
                            'value': mac['value'],
                            'is_active': tmpMac is not None
                        }
                        MacModel.objects.filter().update(**data)
                except Exception as e:
                    # print('--------------------------error 5000')
                    # print(repr(e))
                    if savePoint:
                        transaction.rollback(savePoint)
                    djangoLogger = logging.getLogger('django')
                    djangoLogger.exception(repr(e))
                    return response.json(174, str(e))
                else:
                    savePoint = None
            except Exception as e:
                # print('--------------------------error 5001')
                # print(repr(e))
                return response.json(174, str(e))
            count += 1
            tmpMac = CommonService.updateMac(mac['value'])
            if tmpMac is None:
                # 能分配的mac已用完
                break
            else:
                mac['value'] = tmpMac

        # 当bulk不足5000时，还有数据要插入
        try:
            try:
                savePoint = transaction.savepoint()  # 事务保存点
                if len(bulk) > 0:
                    UIDModel.objects.bulk_create(bulk)
                    bulk.clear()
            except Exception as e:
                # print('--------------------------error')
                # print(repr(e))
                if savePoint:
                    transaction.rollback(savePoint)
                return response.json(174)
            else:
                del data
                del bulk
                data = {
                    'value': mac['value'],
                    'is_active': tmpMac is not None
                }
                MacModel.objects.filter().update(**data)
        except Exception as e:
            # print('--------------------------error 1111')
            # print(repr(e))
            return response.json(174)

        # print('重复：')
        # print(duplicate)

        operation = self.formatOperation(operation='上传', quantity=int(count), area=int(area))
        print(operation)
        self.add_log(request, operation, token)

        if tmpMac is None:
            return response.json(175, {'last_uid': key})
        return response.json(0, {'count': count, 'duplicate_count': len(duplicate), 'data': duplicate})

    def do_reset(self, data, response, area, request, token):
        keys = data.keys()
        uids = []
        count = 0
        for key in keys:
            uids.append(key.strip())
            if len(uids) % 5000 == 0:
                count += self.do_update_uid_status(uids, area)
                uids.clear()

        if len(uids) > 0:
            count += self.do_update_uid_status(uids, area)
            uids.clear()

        operation = self.formatOperation('重置', int(count), int(area))
        self.add_log(request, operation, token)
        return response.json(0)

    def do_update_uid_status(self, uids, area):
        uid_qs = UIDModel.objects.filter(uid__in=uids, area=area, status=2)
        if uid_qs.exists():
            uid_ids = []
            for uid in uid_qs:
                if uid.status == 2:
                    uid.status = 1
                    uid_ids.append(uid.id)
            UIDModel.objects.bulk_update(uid_qs, fields=['status'])

            try:
                OrderUIDModel.objects.filter(uid__id__in=tuple(uid_ids)).delete()
            except Exception as e:
                print(e)

            return uid_qs.count()
        return 0

    def add_log(self, request, operation, token):
        ip = CommonService.get_ip_address(request)
        now_time = time.time()
        content = json.loads(json.dumps(request.POST))
        user_qs = UserModel.objects.filter(id=token.userID)

        if content.__contains__('fileName'):
            del content['fileName']
        log = {
            'status': 200,
            'content': json.dumps(content),
            'ip': ip,
            'time': now_time,
            'url': 'upload',
            'operation': operation,
            'user': user_qs[0]
        }

        try:
            LogModel.objects.create(**log)
        except Exception as e:
            print('出错')
            print(repr(e))

    def formatOperation(self, operation, quantity, area):
        str = '{operation}{quantity}个{area}UID'
        if area == 0:
            return str.format(operation=operation, quantity=quantity, area='国内')
        else:
            return str.format(operation=operation, quantity=quantity, area='国外')


class UploadSerialNumberFileView(View):
    # 上传文件重置序列号使用状态
    @method_decorator(csrf_exempt)
    def dispatch(self, request, *args, **kwargs):
        return super(UploadSerialNumberFileView, self).dispatch(request, *args, **kwargs)

    def get(self, request, *args, **kwargs):
        request.encoding = 'utf-8'
        request_dict = request.GET
        return self.reset_serial_number(request_dict, request)

    def post(self, request, *args, **kwargs):
        request.encoding = 'utf-8'
        request_dict = request.POST
        return self.reset_serial_number(request_dict, request)

    def reset_serial_number(self, request_dict, request):
        token = request_dict.get('token', None)
        content = request_dict.get('fileName', None)
        print('content: ', content)

        response = ResponseObject()
        tko = TokenObject(token)
        if tko.code != 0:
            return response.json(tko.code)

        try:
            # redis加锁,防止同时进行其他操作
            redisObj = RedisObject()
            serial_operate_lock_key = 'serial_operate_lock'
            isLock = redisObj.CONN.setnx(serial_operate_lock_key, 1)
            if not isLock:
                return response.json(5)
            redisObj.CONN.expire(serial_operate_lock_key, 60)

            content = base64.b64decode(content).decode().strip()
            content = content[3:(len(content)-3)]
            serial_number_list = content.split('\n')
            serial_number_list = list(map(self.cut_len, serial_number_list))    # 获取序列号前6位
            # print('serial_number_list: ', serial_number_list)

            # 重置使用状态为已下载(2)的序列号为已分配(1)
            serial_number_qs = SerialNumberModel.objects.filter(serial_number__in=serial_number_list, use_status=2)
            if not serial_number_qs.exists():
                return response.json(173)
            count = serial_number_qs.count()    # 需要重置的序列号个数
            if count:
                with transaction.atomic():
                    # 删除订单
                    order_serial_number = OrderSerialNumberModel.objects.filter(serial_number__in=serial_number_qs)
                    order = order_serial_number.values('order_id').distinct()   # 确定订单id
                    OrderTaskModel.objects.filter(id__in=order).delete()    # 删除订单
                    order_serial_number.delete()    # 删除订单与序列号关联表数据

                    serial_number_qs.update(use_status=1)   # 重置序列号状态为已分配
                    operation = self.formatOperation('重置', int(count))
                    self.add_log(request, operation, tko)
            redisObj.del_data(key=serial_operate_lock_key)      # redis解锁
            return response.json(0)
        except Exception as e:
            # print('e:', e)
            return response.json(500, repr(e))

    def cut_len(self, serial_number):
        serial_number = serial_number[:6]
        return serial_number

    def add_log(self, request, operation, token):
        ip = CommonService.get_ip_address(request)
        now_time = time.time()
        content = json.loads(json.dumps(request.POST))
        user_qs = UserModel.objects.filter(id=token.userID)

        if content.__contains__('fileName'):
            del content['fileName']
        log = {
            'status': 200,
            'content': json.dumps(content),
            'ip': ip,
            'time': now_time,
            'url': 'uploadSerialNumber',
            'operation': operation,
            'user': user_qs[0]
        }
        LogModel.objects.create(**log)

    def formatOperation(self, operation, quantity):
        operate = '{operation}{quantity}个序列号'
        return operate.format(operation=operation, quantity=quantity)

class DownloadUIDFileView(View):

    @method_decorator(csrf_exempt)
    def dispatch(self, request, *args, **kwargs):
        return super(DownloadUIDFileView, self).dispatch(request, *args, **kwargs)

    def get(self, request, *args, **kwargs):
        request.encoding = 'utf-8'
        request_dict = request.GET
        return self.validate(request_dict)

    def post(self, request, *args, **kwargs):
        request.encoding = 'utf-8'
        request_dict = request.POST
        return self.validate(request_dict)

    def validate(self, request_dict):
        token = request_dict.get('token', None)
        area = request_dict.get('area', None)
        quantity = int(request_dict.get('quantity', None))
        fileType = request_dict.get('fileType', None)
        order_number = request_dict.get('order_number', None)
        board = request_dict.get('board', None)
        plan = request_dict.get('plan', None)
        checksum = request_dict.get('checksum', None)
        ic_model = request_dict.get('ic_model', None)
        order_quantity = request_dict.get('order_quantity', None)
        response = ResponseObject()

        # print(area)
        # print(quantity)
        token = TokenObject(token)
        if token.code != 0:
            return response.json(token.code)

        if not area or not order_number or not board or not plan or not checksum or not ic_model or not order_quantity or not fileType:
            return response.json(444)

        area = int(area)

        if area >= 0 and quantity > 0:

            # 保存订单信息
            now_time = int(time.time())
            order = {
                'order_number': order_number,
                'board': board,
                'plan': plan,
                'checksum': checksum,
                'ic_model': ic_model,
                'quantity': order_quantity,
                'add_time': now_time
            }

            tmp = OrderTaskModel.objects.create(**order)
            print(tmp)

            order = OrderTaskModel.objects.filter(order_number=order_number).order_by('-add_time')[0]

            uid_qs = UserUIDModel.objects.filter(user__id=token.userID, uid__status=1, uid__area=area)

            uid_values = uid_qs.values('uid__id', 'uid__uid', 'uid__mac', 'uid__uid_extra', 'uid__add_time', 'uid__update_time', 'uid__area')

            count = uid_values.count()
            if count < quantity:
                return response.json(444, '设备UID不足')

            if uid_values.exists():

                uid_values = uid_values[0:quantity]
                uid_qs = uid_qs[0: quantity]
                if fileType == 'txt':
                    # return self.download_txt(uid_values, uid_qs, order)
                    # return self.download_excel(uid_values, order)
                    return self.download_zip(uid_values, order)
                elif fileType == 'excel':
                    return self.download_excel(uid_values, order)
                else:
                    return response.json(444, 'fileType')
            else:
                return response.json(444, '111')
        else:
            return response.json(444, '222')

    def download_txt(self, uid_values, uid_qs, order):
        updates = []
        updates_uid = []
        content = ''
        now_time = int(time.time())
        for i in range(len(uid_values)):
            # print(item)
            item = uid_values[i]
            mac: str = item['uid__mac']
            index = mac.rfind(':')
            tmp = mac[0:index] + '\t' + mac[index:]
            content += tmp + '\t'
            content += item['uid__uid'].strip()
            content += '\r\n'
            uidModel = UIDModel(
                id=item['uid__id'],
                uid=item['uid__uid'],
                mac=item['uid__mac'],
                uid_extra=item['uid__uid_extra'],
                status=2,
                add_time=item['uid__add_time'],
                update_time=now_time,
                area=item['uid__area']
            )
            updates.append(uidModel)

            order_uid = OrderUIDModel(uid=uidModel, order=order, add_time=now_time, update_time=now_time)
            updates_uid.append(order_uid)

            if len(updates) % 5000 == 0:
                UIDModel.objects.bulk_update(updates, fields=["status"])
                OrderUIDModel.objects.bulk_create(updates_uid)
                updates.clear()
                updates_uid.clear()
            # print(item['uid__uid'])

        if len(updates) > 0:
            UIDModel.objects.bulk_update(updates, fields=["status"])
            OrderUIDModel.objects.bulk_create(updates_uid)
            updates.clear()
            updates_uid.clear()

        del updates
        del updates_uid
        content = content[0:len(content) - 1]
        response = StreamingHttpResponse(content)
        response['Content-Type'] = 'application/octet-stream'
        response['Content-Disposition'] = 'attachment;filename=UID'+time.strftime('-%Y-%m-%d-%H-%M-%S', time.localtime()) + '.txt'
        return response

    def download_excel(self, uid_qs, order):
        response = HttpResponse(content_type='application/vnd.ms-excel')
        response['Content-Disposition'] = 'attachment; filename=UID' + time.strftime('-%Y-%m-%d-%H-%M-%S',
                                                                                     time.localtime()) + '.xls'
        workbook = xlwt.Workbook(encoding='utf-8')
        sheet1 = workbook.add_sheet('UID')
        # row1 = [u'设备UID']
        # for i in range(0, len(row1)):
        #     sheet1.write(0, i, row1[i])

        num = 1
        updates = []
        updates_uid = []
        now_time = int(time.time())
        for item in uid_qs:
            uid = item['uid__uid']
            mac = item['uid__mac']
            index = mac.rfind(':')
            sheet1.write(num, 0, mac[0:index])
            sheet1.write(num, 1, mac[index:])
            sheet1.write(num, 2, uid)
            num += 1
            uidModel = UIDModel(
                id=item['uid__id'],
                uid=item['uid__uid'],
                mac=item['uid__mac'],
                uid_extra=item['uid__uid_extra'],
                status=2,
                add_time=item['uid__add_time'],
                update_time=now_time,
                area=item['uid__area']
            )
            updates.append(uidModel)

            order_uid = OrderUIDModel(uid=uidModel, order=order, add_time=now_time, update_time=now_time)
            updates_uid.append(order_uid)

            if len(updates) % 5000 == 0:
                UIDModel.objects.bulk_update(updates, fields=["status"])
                OrderUIDModel.objects.bulk_create(updates_uid)
                updates.clear()
                updates_uid.clear()
            # print(item['uid__uid'])

        if len(updates) > 0:
            UIDModel.objects.bulk_update(updates, fields=["status"])
            OrderUIDModel.objects.bulk_create(updates_uid)
            updates.clear()
            updates_uid.clear()

        UIDModel.objects.bulk_update(updates, fields=["status"])
        workbook.save(response)
        return response

    def download_zip(self, uid_qs, order):
        dir_name = 'static/' + time.strftime('%Y_%m_%d_%H_%M_%S', time.localtime())
        path = '/'.join((BASE_DIR, dir_name)).replace('\\', '/') + '/'
        if not os.path.exists(path):
            os.mkdir(path)

        filename = time.strftime('_%Y_%m_%d_%H_%M_%S', time.localtime())
        txt_filename = 'UID' + filename + '.txt'
        excel_filename = 'UID' + filename + '.xls'

        txt_file = open(path + txt_filename, 'w+')
        workbook = xlwt.Workbook(encoding='utf-8')
        sheet1 = workbook.add_sheet('UID')
        num = 1

        updates = []
        updates_uid = []
        content = ''
        now_time = int(time.time())
        for i in range(len(uid_qs)):
            # print(item)
            item = uid_qs[i]
            uid = item['uid__uid']
            mac = item['uid__mac']
            index = mac.rfind(':')
            tmp = mac[0:index] + '\t' + mac[index:]
            content += tmp + '\t'
            content += item['uid__uid'].strip()
            content += '\r\n'

            sheet1.write(num, 0, mac[0:index])
            sheet1.write(num, 1, mac[index:])
            sheet1.write(num, 2, uid)
            num += 1

            uidModel = UIDModel(
                id=item['uid__id'],
                uid=item['uid__uid'],
                mac=item['uid__mac'],
                uid_extra=item['uid__uid_extra'],
                status=2,
                add_time=item['uid__add_time'],
                update_time=now_time,
                area=item['uid__area']
            )
            updates.append(uidModel)

            order_uid = OrderUIDModel(uid=uidModel, order=order, add_time=now_time, update_time=now_time)
            updates_uid.append(order_uid)

            if len(updates) % 5000 == 0:
                UIDModel.objects.bulk_update(updates, fields=["status"])
                OrderUIDModel.objects.bulk_create(updates_uid)
                updates.clear()
                updates_uid.clear()
            # print(item['uid__uid'])

        if len(updates) > 0:
            UIDModel.objects.bulk_update(updates, fields=["status"])
            OrderUIDModel.objects.bulk_create(updates_uid)
            updates.clear()
            updates_uid.clear()

        del updates
        del updates_uid
        content = content[0:len(content) - 1]
        txt_file.write(content)
        txt_file.close()

        workbook.save(path + excel_filename)

        zip_name = path[0:path.rfind('/')] + '.zip'
        return self.get_zip(path, os.listdir(path), zip_name)

    def get_zip(self, path, files, zip_name):
        zp = zipfile.ZipFile(zip_name, 'w', zipfile.ZIP_DEFLATED)
        for file in files:
            zp.write(filename=(path + file), arcname=str(file))
        zp.close()

        response = StreamingHttpResponse(open(zip_name, 'rb'))
        response['content_type'] = "application/octet-stream"
        response['Content-Disposition'] = 'attachment; filename=UID' + time.strftime('_%Y_%m_%d_%H_%M_%S',
                                                                                     time.localtime()) + '.zip'
        return response




class DownloadSerialNumberFileView(View):

    @method_decorator(csrf_exempt)
    def dispatch(self, request, *args, **kwargs):
        return super(DownloadSerialNumberFileView, self).dispatch(request, *args, **kwargs)

    def get(self, request, *args, **kwargs):
        request.encoding = 'utf-8'
        request_dict = request.GET
        return self.validate(request_dict)

    def post(self, request, *args, **kwargs):
        request.encoding = 'utf-8'
        request_dict = request.POST
        return self.validate(request_dict)

    def validate(self, request_dict):
        token = request_dict.get('token', None)
        quantity = int(request_dict.get('quantity', None))
        fileType = request_dict.get('fileType', None)
        order_number = request_dict.get('order_number', None)
        board = request_dict.get('board', None)
        plan = request_dict.get('plan', None)
        checksum = request_dict.get('checksum', None)
        ic_model = request_dict.get('ic_model', None)
        order_quantity = request_dict.get('order_quantity', None)
        company_id = request_dict.get('company_id', None)
        response = ResponseObject()

        # print(area)
        # print(quantity)
        token = TokenObject(token)
        if token.code != 0:
            return response.json(token.code)

        if not order_number or not board or not plan or not checksum or not ic_model or not order_quantity or not fileType or not company_id:
            return response.json(444)

        # redis加锁,防止同时进行其他操作
        redisObj = RedisObject()
        serial_operate_lock_key = 'serial_operate_lock'
        isLock = redisObj.CONN.setnx(serial_operate_lock_key, 1)
        if not isLock:
            return response.json(5)
        redisObj.CONN.expire(serial_operate_lock_key, 60)

        if quantity > 0:

            # 保存订单信息
            now_time = int(time.time())
            order = {
                'order_number': order_number,
                'board': board,
                'plan': plan,
                'checksum': checksum,
                'ic_model': ic_model,
                'quantity': order_quantity,
                'add_time': now_time
            }

            tmp = OrderTaskModel.objects.create(**order)
            print(tmp)

            order = OrderTaskModel.objects.filter(order_number=order_number).order_by('-add_time')[0]

            user_qs = UserModel.objects.filter(id=token.userID)
            if not user_qs.exists():
                return response.json(9)

            user = user_qs[0]
            result = UserSerialNumberModel.objects.filter(user__id=user.id,
                                                          serial_number__use_status=1).aggregate(
                num=Count('serial_number__use_status'))


            us_qs = UserSerialNumberModel.objects.filter(user__id=user.id,
                                                          serial_number__use_status=1).values('serial_number__serial_number')

            sns = []
            for us in us_qs:
                sns.append(us['serial_number__serial_number'])

            cs_qs = CompanySerialModel.objects.filter(serial_number__in=sns, status=1, company__secret=company_id).values('serial_number', 'company__mark')
            sns = []
            mark = ""
            if cs_qs.exists():
                for cs in cs_qs:
                    sns.append(cs['serial_number'])
                mark = cs_qs[0]['company__mark']

            sn_qs = SerialNumberModel.objects.filter(serial_number__in=sns)
            count = result['num']
            if count < quantity:
                return response.json(14)

            if sn_qs.exists():

                sn_qs = sn_qs[0: quantity]
                if fileType == 'txt':
                    # return self.download_txt(uid_values, uid_qs, order)
                    # return self.download_excel(uid_values, order)
                    fileTypeName  = 'NewFile'
                    return self.download_zip(sn_qs, order , fileTypeName, mark)
                else:
                    return response.json(444, 'fileType')
            else:
                return response.json(444, '111')
        else:
            return response.json(444, '222')


    def download_zip(self, sn_qs, order , fileTypeName, mark):
        dir_name = 'static/' + time.strftime('%Y_%m_%d_%H_%M_%S', time.localtime())
        path = '/'.join((BASE_DIR, dir_name)).replace('\\', '/') + '/'
        if not os.path.exists(path):
            os.mkdir(path)

        filename = time.strftime('_%Y_%m_%d_%H_%M_%S', time.localtime())
        txt_filename = 'SerialNumber' +  fileTypeName +'_'+ filename + '.txt'
        excel_filename = 'SerialNumber' + fileTypeName + '_' + filename + '.xls'

        txt_file = open(path + txt_filename, 'w+')
        workbook = xlwt.Workbook(encoding='utf-8')
        sheet1 = workbook.add_sheet('SerialNumber')
        num = 0

        updates = []
        company_serial_updates = []
        updates_serial_number = []
        content = ''
        now_time = int(time.time())
        mac_qs = MacModel.objects.filter()
        mac = mac_qs.values('value')[0]['value']
        for i in range(len(sn_qs)):
            # print(item)
            item = sn_qs[i]
            serial_number = item.serial_number
            # xls文件写入内容
            sheet1.write(num, 0, serial_number + mark)
            sheet1.write(num, 1, mac)
            # txt文件写入内容
            content += serial_number.strip() + mark + ' ' + mac + '\n'
            num += 1
            mac = CommonService.updateMac(mac)  # mac地址值+1

            serialNumberModel = SerialNumberModel(
                id=item.id,
                serial_number=serial_number,
                status=item.status,
                use_status=2,
                add_time=now_time
            )
            updates.append(serialNumberModel)

            company_serial_updates.append(serial_number)  # 获取序列号

            order_serial_number = OrderSerialNumberModel(serial_number=item, order=order, add_time=now_time, update_time=now_time)
            updates_serial_number.append(order_serial_number)

            if len(updates) % 5000 == 0:
                bulk_update(updates)
                OrderSerialNumberModel.objects.bulk_create(updates_serial_number)
                updates.clear()
                updates_serial_number.clear()
        mac_qs.update(value=mac, update_time=now_time)  # 更新mac表数据

        CompanySerialModel.objects.filter(serial_number__in=company_serial_updates).update(status=2) # 更新序列号状态

        if len(updates) > 0:
            bulk_update(updates)
            OrderSerialNumberModel.objects.bulk_create(updates_serial_number)
            updates.clear()
            updates_serial_number.clear()

        del updates
        del updates_serial_number
        content = content[0:len(content) - 1]
        txt_file.write(content)
        txt_file.close()

        workbook.save(path + excel_filename)

        zip_name = path[0:path.rfind('/')] + '.zip'
        return self.get_zip(path, os.listdir(path), zip_name , fileTypeName)

    def get_zip(self, path, files, zip_name, fileTypeName ):
        zp = zipfile.ZipFile(zip_name, 'w', zipfile.ZIP_DEFLATED)
        for file in files:
            zp.write(filename=(path + file), arcname=str(file))
        zp.close()

        response = StreamingHttpResponse(open(zip_name, 'rb'))
        response['content_type'] = "application/octet-stream"
        response['Content-Disposition'] = 'attachment; filename=SerialNumber_'+ fileTypeName + time.strftime('_%Y_%m_%d_%H_%M_%S',
                                                                                     time.localtime()) + '.zip'
        serial_operate_lock_key = 'serial_operate_lock'
        redisObj = RedisObject()
        redisObj.del_data(key=serial_operate_lock_key)  # redis解锁
        return response