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
'''