ASJServer/Controller/BatchProcessingDatabase.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import traceback
from Ansjer.settings import *
from django.views.generic.base import View
from django.views.decorators.csrf import csrf_exempt
from openpyxl import load_workbook
from openpyxl.compat import range
import xlrd
from Service.ResponseService import *
from Service.CommonService import CommonService
from Model.models import Device_Info, Device_User

class batchAddEquipmentView(View):
    @csrf_exempt
    def post(self, request, *args, **kwargs):
        request.encoding = 'utf-8'
        fileType = request.POST.get('fileType', None)
        fileName = request.FILES.get('fileName', None)

        if fileName != None and fileType != None:
            return self.batchAddEquipment(fileType, fileName)
        else:
            return ResponseJSON(800)

    @csrf_exempt
    def get(self, request, *args, **kwargs):
        request.encoding = 'gb2312'

        fileType = request.GET.get('fileType', None)
        fileName = request.FILES.get('fileName', None)

        if fileName != None and fileType != None:
            return self.batchAddEquipment(fileType, fileName)
        else:
            return ResponseJSON(800)

        #return HttpResponse(self.batchAddEquipmentfromTXTDict('D://1.txt'))
        #return HttpResponse(self.batchAddEquipmentfromExecl2('D:/2.xlsx'))

    def batchAddEquipment(self, fileType, fileName):
        filename = fileName.name
        filename = filename.replace(' ', '+')

        try:
            filePath = '/'.join((BASE_DIR, "static/SysFile", fileType)).replace('\\', '/') + '/'

            if not os.path.exists(filePath):
                os.makedirs(filePath)
                fullPath = filePath + filename
                if os.path.exists(fullPath):
                    os.remove(fullPath)
                destination = open(fullPath, 'wb+')
                for chunk in fileName.chunks():
                    destination.write(chunk)
                destination.close()
            else:
                fullPath = filePath + filename
                if os.path.exists(fullPath):
                    os.remove(fullPath)
                destination = open(fullPath, 'wb+')
                for chunk in fileName.chunks():
                    destination.write(chunk)
                destination.close()
        except Exception as e:
            errorInfo = traceback.format_exc()
            print('上传文件错误: %s' % errorInfo)
            return ResponseJSON(700, {
                'details': repr(e)
            })

        if fileType == 'TXT':
            response = HttpResponse(self.batchAddEquipmentfromTXT(fullPath))
            return response
        elif fileType == 'Excel':
            response = HttpResponse(self.batchAddEquipmentfromExecl(fullPath))
            return response

    def batchAddEquipmentfromExecl(self, filePath):
        if not os.path.isfile(filePath):
            return ResponseJSON(701)
        else:
            try:
                xlsData = xlrd.open_workbook(filename = filePath)
            except Exception as e:
                errorInfo = traceback.format_exc()
                print('Open the Excel file error: %s' % errorInfo)
                return ResponseJSON(702, {
                    'details': repr(e)
                })
            else:
                sheetsCount = len(xlsData.sheets())
                table = xlsData.sheets()[0]  # 打开第一张表
                nrows = table.nrows  # 获取表的行数

                eInfo = []
                error_list = []
                error_msg = {}
                errorCount = addCount = 0

                for index in range(nrows):  # 循环逐行添加
                    eInfo = table.row_values(index)

                    try:
                        User = Device_User.objects.filter(userID = eInfo[0])
                    except Exception as e:
                        errorCount += 1
                        errorInfo = traceback.format_exc()
                        print('error message: %s' % errorInfo)

                        errorDict = {'UID': eInfo[2], 'error_msg': u'error message: {0}'.format(repr(e))}
                        error_list.append(errorDict)

                        continue

                    if not User:
                        errorCount += 1
                        errorDict = {'UID': eInfo[2], 'error_msg': u'error message: The UserID {0} does not exist!'.format(eInfo[0])}
                        error_list.append(errorDict)

                        continue
                    try:
                        tupleInfo = Device_Info.objects.get_or_create(userID_id=eInfo[0],
                                                                      NickName=eInfo[1],
                                                                      UID=eInfo[2], SID=eInfo[3],
                                                                      View_Account=eInfo[4],
                                                                      View_Password=eInfo[5],
                                                                      AudioFormat=eInfo[6],
                                                                      EventNotification=eInfo[7],
                                                                      ChannelIndex=eInfo[8],
                                                                      Online=eInfo[9],
                                                                      mMonitorIndex=eInfo[10],
                                                                      Type=eInfo[11],
                                                                      DebugMode=eInfo[12],
                                                                      NotificationMode=eInfo[13],
                                                                      qvgaLevel=eInfo[14],
                                                                      defaults={'id': CommonService.getUserID(getUser=False)})
                    except Exception as e:
                        errorCount += 1
                        errorInfo = traceback.format_exc()
                        print('error message: %s' % errorInfo)

                        errorDict = {'UID': eInfo[2], 'error_msg': u'error message: {0}'.format(repr(e))}
                        error_list.append(errorDict)

                        continue
                    else:
                        if tupleInfo[1] == True:
                            addCount += 1
                        else:
                            errorCount += 1

                if len(error_list) > 0:
                    error_msg['error'] = error_list

                xlsData.release_resources()
                if xlsData:
                    del xlsData

                resultDict = { "result_code": 0, "reason": u'Success'}
                if errorCount > 0:
                    if len(error_msg) > 0:
                        if sheetsCount > 1:
                            resultDict.update({
                                "result": {'appending': addCount, 'errorCount': errorCount, 'error_msg': error_msg,
                                           'sheetsCount': 'The Execl contains {0} tables!'.format(sheetsCount)},
                                 "error_code": 704
                            })
                        else:
                            resultDict.update({
                                "result": {'appending': addCount, 'errorCount': errorCount, 'error_msg': error_msg},
                                 "error_code": 705
                            })

                        errorJSON = json.dumps(resultDict, ensure_ascii=False)
                        return errorJSON
                    else:
                        if sheetsCount > 1:
                            resultDict.update({
                                "result": {'appending': addCount, 'appended': errorCount,
                                    'sheetsCount': 'The Execl contains {0} tables!'.format(sheetsCount)},
                                "error_code": 704,
                            })
                        else:
                            resultDict.update({
                                "result": {'appending': addCount, 'appended': errorCount},
                                "error_code": 705,
                            })

                        errorJSON = json.dumps(resultDict, ensure_ascii=False)
                        return errorJSON
                else:
                    if sheetsCount > 1:
                        resultDict.update({
                            "result": {'appending': addCount,
                                       'sheetsCount': 'The Execl contains {0} tables!'.format(sheetsCount)},
                            "error_code": 704,
                        })
                    else:
                        resultDict.update({
                            "result": {'appending': addCount},
                            "error_code": 0,
                        })
                    JSON = json.dumps(resultDict, ensure_ascii=False)
                    return JSON

    def batchAddEquipmentfromExecl2(self, filePath):
        '''

        :param filePath:
        :return:
        :remarks: 不支持旧版xls
        '''
        #filePath = 'D:/2.xlsx'
        try:
            wb = load_workbook(filename = filePath)
            ws = wb.get_sheet_names()
            ws = wb.get_sheet_by_name(ws[0])
            rows = ws.iter_rows()
            #cols = ws.iter_cols()
            #rows = ws.rows  # 行数
            #columns = ws.columns  # 列数
        except Exception as e:
            errorInfo = traceback.format_exc()
            print('error message: %s' % errorInfo)
            return ResponseFormal(702,repr(e))
        error_list = []
        error_msg = {}
        equipmentlists = []
        errorCount = addCount = 0
        for row in rows:
            eInfo = []
            for cell in row:
                #print(cell.coordinate, cell.value)
                if cell.value == None:
                    cell.value = ''
                eInfo.append(cell.value)

            try:
                User = Device_User.objects.filter(userID = eInfo[0])
            except Exception as e:
                errorCount += 1
                errorInfo = traceback.format_exc()
                print('error message: %s' % errorInfo)

                errorDict = {'UID': eInfo[2], 'error_msg': u'error message: {0}'.format(repr(e))}
                error_list.append(errorDict)

                continue

            if not User:
                errorCount += 1
                errorDict = {'UID': eInfo[2],
                             'error_msg': u'error message: The UserID {0} does not exist!'.format(eInfo[0])}
                error_list.append(errorDict)
                continue

            eqValid = Device_Info.objects.filter(userID_id = eInfo[0], UID=eInfo[2])
            if eqValid:
                errorCount += 1
                errorDict = {'UID': eInfo[2], 'error_msg': u'The UID has already existed!'}
                error_list.append(errorDict)
                continue

            equipment = Device_Info(id=CommonService.getUserID(getUser=False), userID_id=eInfo[0],
                                    NickName=eInfo[1], UID=eInfo[2], SID=eInfo[3],
                                    View_Account=eInfo[4], View_Password=eInfo[5],
                                    AudioFormat=eInfo[6], EventNotification=eInfo[7],
                                    ChannelIndex=eInfo[8], Online=eInfo[9], mMonitorIndex=eInfo[10],
                                    Type=eInfo[11], DebugMode=eInfo[12], NotificationMode=eInfo[13],
                                    qvgaLevel=eInfo[14], )
            equipmentlists.append(equipment)

        try:
            elists = Device_Info.objects.bulk_create(equipmentlists)
        except Exception as e:
            errorCount += 1
            errorInfo = traceback.format_exc()
            print('error message: %s' % errorInfo)

            errorDict = {'UID': eInfo[2], 'error_msg': u'error message: {0}'.format(repr(e))}
            error_list.append(errorDict)
        else:
            if len(equipmentlists) != len(elists):
                addCount += len(elists)
                errorCount += (len(equipmentlists) - len(elists))
            else:
                addCount += len(elists)

        if len(error_list) > 0:
            error_msg['error'] = error_list

        if errorCount == 0:
            return ResponseFormal(0, {
                'appending': addCount
            })
        else:
            return ResponseFormal(0, {
                'appending': addCount,
                'errorCount': errorCount,
                'error_msg': error_msg,
            })

    def batchAddEquipmentfromTXT(self, filePath):
        '''
        txt文件批量导入数据库，通过list方式
        :param filePath:
        :return:
        '''
        if os.path.isfile(filePath):
            file = open(filePath, encoding='utf-8')
            eInfo = []
            error_list = []
            error_msg = errorDict = {}
            errorCount = addCount = 0

            for line in file:
                eInfo = line.split(';')
                for index in range(len(eInfo)):
                    if index > 5:
                        eInfo[index] = int(eInfo[index])
                    else:
                        continue

                dUser = Device_User.objects.filter(userID = eInfo[0])
                if not dUser:
                    errorCount += 1
                    errorDict = {"userID": eInfo[0], "error_msg'": u"The userID_id is no exist!"}
                    error_list.append(errorDict)

                    continue

                try:
                    tupleInfo = Device_Info.objects.get_or_create(userID_id=eInfo[0], NickName=eInfo[1],
                                                      UID=eInfo[2], SID=eInfo[3], View_Account=eInfo[4],
                                                      View_Password=eInfo[5], AudioFormat=eInfo[6],
                                                      EventNotification=eInfo[7], ChannelIndex=eInfo[8],
                                                      Online=eInfo[9], mMonitorIndex=eInfo[10],
                                                      Type=eInfo[11], DebugMode=eInfo[12],
                                                      NotificationMode=eInfo[13], qvgaLevel=eInfo[14],
                                                      defaults={'id': CommonService.getUserID(getUser=False)})
                except Exception as e:
                    errorCount += 1
                    errorInfo = traceback.format_exc()
                    print('error message: %s' % errorInfo)

                    errorDict = {'UID': eInfo[2], 'error_msg': u'error message: {0}'.format(repr(e))}
                    error_list.append(errorDict)

                    continue
                else:
                    if tupleInfo[1] == True:
                        addCount += 1
                    else:
                        errorDict = {'UID': eInfo[2], 'error_msg': u'The UID is existed!'}
                        error_list.append(errorDict)
                        errorCount += 1

            file.close()

            if len(error_list) > 0:
                error_msg['error'] = error_list

            if errorCount > 0:
                if len(error_msg) > 0:
                    return ResponseFormal(0, {
                        'appending': addCount,
                        'errorCount': errorCount,
                        'error_msg': error_msg,
                    })
                else:
                    return ResponseFormal(0, {'appending': addCount,'appended': errorCount,})
            else:
                return ResponseFormal(0,{'appending': addCount})
        else:
            return ResponseFormal(701)

    def batchAddEquipmentfromTXTDict(self, filePath):
        if os.path.isfile(filePath):
            file = open(filePath, encoding='utf-8')

            error_list = []
            error_msg = {}
            errorCount = addCount = 0
            equipmentList = []
            equipmentDict = {}

            try:
                for line in file:
                    equipmentStr = eval(line)
                    if isinstance(eval(equipmentStr), dict):
                        equipmentDict.update(eval(equipmentStr))
                    else:
                        return ResponseFormal(706)
                    UID = equipmentDict.get('UID', None)
                    userID_id = equipmentDict.get('userID_id', None)

                    dUser = Device_User.objects.filter(userID = userID_id)
                    if dUser:
                        if UID != None and userID_id != None:
                            dInfo = Device_Info.objects.filter(userID_id = userID_id, UID = UID)
                            if dInfo:
                                errorCount += 1
                                continue
                            else:
                                equipment = Device_Info(id = CommonService.getUserID(getUser=False), **equipmentDict)
                                equipmentList.append(equipment)
                        else:
                            errorCount += 1
                            errorDict = {'userID_id': userID_id, 'error_msg': u'The userID_id is None or UID is None!'}
                            error_list.append(errorDict)

                            continue

                    else:
                        errorCount += 1
                        errorDict = {'userID_id': userID_id, 'error_msg': u'The userID_id is no exist!'}
                        error_list.append(errorDict)

                        continue

                file.close()
                sss = Device_Info.objects.bulk_create(equipmentList)
                print(type(sss), sss)

            except Exception as e:
                errorInfo = traceback.format_exc()
                print('The error Info: %s' % errorInfo)
                return ResponseFormal(707,{'details':repr(e)})
            else:
                if len(error_list) > 0:
                    error_msg['error'] = error_list

                if errorCount > 0:
                    if error_msg != None:
                        return ResponseFormal(0, {'errorCount': errorCount,
                                    'error': error_msg,
                                    'appending': len(equipmentList)})
                    else:
                        return ResponseFormal(0, { 'errorCount': errorCount,
                                    'appending': len(equipmentList)})
                else:
                    return ResponseFormal(0,{'appending':len(equipmentList)})
        else:
            return ResponseFormal(701)