新增PCI最小复用距离类

import xlrd
from django.core.exceptions import MultipleObjectsReturned
from django.db.models import Model
from geographiclib.geodesic import Geodesic

from dbbackend.models import ZJcell, LngLat, ZJenb


class Cellinforeader:
    """读取excel的类"""

    def __init__(self, path=r'C:\Users\Administrator\Desktop\嘉兴工参\圈定区域网内邻区关系(1).xlsx', sheetname='圈定小区'):
        try:
            self.workbook = xlrd.open_workbook(path)
            self.sheet = self.workbook.sheet_by_name(sheetname)
        except Exception:
            print(Exception)
        # 列名与列号字典，例如{'小区名称':2}
        self.col_dict = {}
        self.mismatchedcgi = dict()
        self.mismatchedcgi_t = dict()
        self.mismatchedrowid = []
        # sheet.ncols:  Nominal number of columns in sheet.
        for i in range(self.sheet.ncols):
            self.col_dict[self.alltypestrip(self.sheet.cell_value(0, i))] = i
            # ' abc '.strip()去除字符串前后的空格

    # 提取每行数据，导入小区数据库ZJcell中
    def insertcells(self, cgi='CGI', cellname='小区名称', freq='频段', pci='PCID', lng='基站经度', lat='基站纬度',
                    azimuth='扇区方位角', enbname='基站名称', enbid='LTEENODEBID'):
        insertcount = 0
        for i in range(1, self.sheet.nrows):
            row = self.sheet.row_values(i)
            # 下面啰嗦了这么多行，无非是要实现两个功能，一是根据列名寻找数据，避免根据列的位置寻找数据时格式不能改
            # 二是利用strip函数将字符串前后的空格删除，避免基站名后面带空格的情况，这里利用的小技巧有：
            # 1、把列名和列号做成dict，这样就可直接通过列名取列号，比循环查找函数省时间
            # 2、alltypestrip()函数利用了python三目运算
            # 3、因为变量名重复，所以在结果的变量名前加下划线 _ ，加以区分
            # 4、这么多重复的写法，看似可以使用循环来解决，但那样反而复杂，不如使用相同的格式列出来，以后看着清晰，
            #    相当于配置的赋值都应该这样写
            _enbname = self.alltypestrip(row[self.col_dict[enbname]])
            _enbid = self.alltypestrip(row[self.col_dict[enbid]])
            _lng = self.alltypestrip(row[self.col_dict[lng]])
            _lat = self.alltypestrip(row[self.col_dict[lat]])
            _cgi = self.alltypestrip(row[self.col_dict[cgi]])
            _cellname = self.alltypestrip(row[self.col_dict[cellname]])
            _freq = self.alltypestrip(row[self.col_dict[freq]])
            _pci = self.alltypestrip(row[self.col_dict[pci]])
            _azimuth = self.alltypestrip(row[self.col_dict[azimuth]])

            enb, createdornot = ZJenb.objects.get_or_create(enbname=_enbname, enbid=_enbid)
            lnglat, createdornot1 = LngLat.objects.get_or_create(lng=_lng, lat=_lat)
            cell, createdornot2 = ZJcell.objects.get_or_create(cgi=_cgi, cellname=_cellname, freq=_freq, pci=_pci,
                                                               azimuth=_azimuth, lnglat=lnglat, enb=enb)
            # ' abc '.strip()去除字符串前后的空格

            # get_or_create函数返回元组的第二个数据表示是否新创建了一条数据
            if createdornot2:
                insertcount += 1
        print("导入" + str(insertcount) + "条数据")

    # 对enbid 'float' object使用strip函数时报错，所以增加这个函数，使用strip前判断一下是不是str类型。
    @staticmethod
    def alltypestrip(args):
        return args.strip() if type(args) is str else args

    # 插入邻区关系
    def insertadj(self, scgi='本地小区CGI', tcgi='邻小区CGI'):
        insertcount = 0
        for i in range(1, self.sheet.nrows):
            row = self.sheet.row_values(i)
            _scgi = self.alltypestrip(row[self.col_dict[scgi]])
            _tcgi = self.alltypestrip(row[self.col_dict[tcgi]])

            source = None
            try:
                source = ZJcell.objects.get(cgi__exact=_scgi)
            except (ZJcell.DoesNotExist, MultipleObjectsReturned):
                print('邻区表中的源小区cgi在小区表中不存在......:' + _scgi)
                self.mismatchedcgi.update({str(i): _scgi})
            target = None
            try:
                target = ZJcell.objects.get(cgi__exact=_tcgi)
            except (ZJcell.DoesNotExist, MultipleObjectsReturned):
                print('邻区表中的目标小区cgi在小区表中不存在....:' + _tcgi)
                self.mismatchedcgi_t.update({str(i): _tcgi})

            # 如果source和target中有一个为None，则不能添加邻区关系
            if source and target:
                insertcount += 1
                try:
                    source.adj.add(target)
                except Exception:
                    print(Exception)
                    insertcount -= 1
            else:
                self.mismatchedrowid.append(i)
        print('共添加' + str(insertcount) + '条邻区')

    # 将所有不匹配的邻区中的cgi打印出来
    def xlwtmismatchedrow(self):
        pass


class Checkpci:
    """检查pci冲突与混淆，得出可用pci"""
    # cgilist格式：['460-00-325632-129','460-00-325632-131'] D1 331 D1 332
    # freqpcilist格式：[{'cellname': 'Z737423嘉兴线务局LY建委大楼WZD_129', 'freq': 'D1', 'pci': 341},
    #                   {'cellname': 'Z730485嘉兴纺织大厦D_1', 'freq': 'D1', 'pci': 115}]
    # def __init__(self, cgilist=None, freqpcilist=None):
    def __init__(self, freqlist=None, mod3list=None):
        if not freqlist:
            freqlist = ['F1', 'F2', 'D1', 'D2', 'D3']
        if not mod3list:
            mod3list = [0, 1, 2]
        self.freqpcimatrix = dict()
        for f in freqlist:
            self.freqpcimatrix[f] = [i for i in range(504) if i % 3 in mod3list]

    # 根据输入的频点和PCI，从初始化freqpcimatrix频点PCI矩阵中剔除相应频点的PCI
    def delfreqpci(self, freqpcilist=None):
        if freqpcilist:
            for freq, pci in freqpcilist:
                try:
                    self.freqpcimatrix.get(freq).remove(pci)
                except (AttributeError, ValueError):
                    print('预置的频点PCI矩阵中没有' + freq + '频点，或PCI' + str(pci))

    # 根据输入的小区cgi列表，查询对应的小区频点和PCI，从初始化频点PCI矩阵中剔除
    def delbycgi(self, cgilist=None, cellnamelist=None):
        freqpcilist = None
        if cgilist:
            try:
                freqpcilist = ZJcell.objects.filter(cgi__in=cgilist).values_list("freq", "pci")
            except TypeError:
                print('cgilist参数为空，或者格式不对')
        elif cellnamelist:
            try:
                freqpcilist = ZJcell.objects.filter(cellname__in=cellnamelist).values_list("freq", "pci")
            except TypeError:
                print('cellnamelist参数为空，或者格式不对')
        if freqpcilist:
            self.delfreqpci(freqpcilist=freqpcilist)

    # 根据输入的小区，查找该小区邻区表，将这个小区及其邻区的频点PCI从列表中剔除。
    def delbycelladj(self, cgi=None, cellname=None):
        source = None
        freqpcilist = None
        if cgi:
            try:
                source = ZJcell.objects.get(cgi__exact=cgi)
            except (ZJcell.DoesNotExist, MultipleObjectsReturned):
                print('邻区表中的源小区cgi在小区表中不存在......:' + cgi)
        elif cellname:
            try:
                source = ZJcell.objects.get(cellname__exact=cellname)
            except (ZJcell.DoesNotExist, MultipleObjectsReturned):
                print('邻区表中的源小区cgi在小区表中不存在......:' + cgi)
        if source:
            freqpcilist = [(source.freq, source.pci)]
            adjfreqpcilist = source.adj.values_list('freq', 'pci')
            if adjfreqpcilist:
                freqpcilist.extend(adjfreqpcilist)
        if freqpcilist:
            self.delfreqpci(freqpcilist=freqpcilist)

    # 输入指定的邻小区列表，将这些邻区的邻区中存在的频点和pci组合从初始频点PCI矩阵中剔除。
    # 注意，这个函数与delbycgi()区别是，这个函数还需要查找列表中的小区的邻区，而delbycgi()只查列表中的小区。
    def delbycelllist(self, cgilist=None, cellnamelist=None):
        if cgilist:
            for cgi in cgilist:
                self.delbycelladj(cgi=cgi)
        elif cellnamelist:
            for cellname in cellnamelist:
                self.delbycelladj(cellname=cellname)


# 根据经纬度及搜索范围，查找pci的最小复用距离，即pci列表中每个pci在所给经纬度点处，计算距离最近的同频同pci小区的距离
class Findsamepci:
    """以经纬度点为圆心，查找搜索半径内，与所给pci列表中相同PCI的小区，并计算小区到圆心距离，这个距离成为最小复用距离"""
    # 初始化函数的作用是确定圆心及半径，并找出半径内所有小区。注意，因为只找小区，不找小区的邻区，为了尽可能多的找到
    # 全部复用的PCI，所以半径必须足够大，一般应超过10公里（km），这样才能从更广的范围内查找最小复用距离。
    def __init__(self, lng, lat, radius=20000):
        north = Geodesic.WGS84.Direct(lat, lng, 0, radius)
        northlat = north['lat2']
        south = Geodesic.WGS84.Direct(lat, lng, 180, radius)
        southlat = south['lat2']
        east = Geodesic.WGS84.Direct(lat, lng, 90, radius)
        eastlng = east['lat2']
        west = Geodesic.WGS84.Direct(lat, lng, 270, radius)
        westlng = west['lat2']
        cellinrectangle_list = ZJcell.objects.filter(lnglat__lng__gt=westlng, lnglat__lng__lt=eastlng,
                                                     lnglat__lat__gt=southlat, lnglat__lat__lt=northlat)
        # 经过测试，这样用相当于建立了两个不同的QuerySet对象：
        '''
        source_list = ZJcell.objects.filter(cgi__in=['460-00-325632-129','460-00-325632-131'])
        another_list=source_list
        another_list
        <QuerySet [<ZJcell: Z736934嘉兴质检所D_129>, <ZJcell: Z736934嘉兴质检所D_131>]>
        source_list
        <QuerySet [<ZJcell: Z736934嘉兴质检所D_129>, <ZJcell: Z736934嘉兴质检所D_131>]>
        another_list = source_list.exclude(cgi__exact='460-00-325632-129')
        another_list
        <QuerySet [<ZJcell: Z736934嘉兴质检所D_131>]>
        source_list
        <QuerySet [<ZJcell: Z736934嘉兴质检所D_129>, <ZJcell: Z736934嘉兴质检所D_131>]>
        '''
        self.cellincycle_list = cellinrectangle_list
        if cellinrectangle_list:
            for cell in cellinrectangle_list:
                geodict1 = Geodesic.WGS84.Inverse(lat, lng, cell.lnglat.lat, cell.lnglat.lng)
                if geodict1['s12'] > radius:
                    self.cellincycle_list = cellinrectangle_list.exclude(id__exact=cell.id)


        '''
        self.freqpcimatrix = {'D1': [i for i in range(504) if i%3 in mod3list], 'D2': [i for i in range(504)],
                              'D3': [i for i in range(504)], 'F1': [i for i in range(504)],
                              'F2': [i for i in range(504)]}
        self.freqpcilist = list()
        try:
            self.freqpcilist = freqpcilist.copy()
        except AttributeError:
            print('freqpcilist参数为空，或者格式不对')

        try:
            qlist = ZJcell.objects.filter(cgi__in=cgilist).values("cellname", "freq", "pci")
            self.freqpcilist.extend(qlist)
        except TypeError:
            print('cgilist参数为空，或者格式不对')
        '''
    '''    
    # 将参数中传入的“频点pci”与初始化时给定cgi或“频点pci”列表对比，看是否存在相同的，有相同为True，否则为False
    def comparepci(self, freq='D1', pci=0):
        for freqpci1 in self.freqpcilist:
            if freqpci1['freq'] is freq and freqpci1['pci'] is pci:
                return True
        return False
    '''