# -*- coding: utf-8 -*- from django.shortcuts import render from django.http.response import HttpResponse,HttpResponseBadRequest from django.views.generic import ListView from django.core.exceptions import ObjectDoesNotExist import json from django.core import serializers import xlwt import requests from requests.auth import HTTPBasicAuth import json from io import BytesIO # from apps.EquipManage.views import CJSONEncoder from apps.AppInfoManage.models import MyUser,SCDstatus,CBDstatus,Equip,User_Log, Equip_SimInfo def get_siminfo(iccid): url = 'http://api.openluat.com/sim/iotcard/card' print('url:',url) appkey = "KDlhc9VY4rWSXkbM" appsecret = "DmwxcSB1b8dx7kLWqWMJNRLgyQhCvPJ7DHIfLdHIz9A18CifSHqJk3nWZ1vHv4cR" res = requests.post(url,json={'iccid':iccid},auth=HTTPBasicAuth(appkey,appsecret)) return res def get_simsinfo(iccids): url = 'http://api.openluat.com/sim/iotcard/cards' print('url:',url) appkey = "KDlhc9VY4rWSXkbM" appsecret = "DmwxcSB1b8dx7kLWqWMJNRLgyQhCvPJ7DHIfLdHIz9A18CifSHqJk3nWZ1vHv4cR" res = requests.post(url,json={'iccids':iccids},auth=HTTPBasicAuth(appkey,appsecret)) return res # Create your views here. # SIM卡列表： class SimInfo(ListView): def get(self, request): current_user = request.session.get('username') current_user = MyUser.objects.get(username=current_user) if current_user.is_superuser != True and current_user.is_staff != True and current_user.is_agency != True: print("---用户") nums = SCDstatus.objects.filter(equip_id__equip_user=current_user).count()+CBDstatus.objects.filter(equip_id__equip_user=current_user).count() elif current_user.is_superuser != True and current_user.is_staff != True and current_user.is_agency == True: print("---代理商") nums = SCDstatus.objects.filter(equip_id__equip_agency=current_user.user_agency).count()+CBDstatus.objects.filter( equip_id__equip_agency=current_user.user_agency).count() else: print("---管理员") nums = SCDstatus.objects.all().count()+CBDstatus.objects.all().count() User_Log.objects.create(log_user=current_user, log_ip=request.META['REMOTE_ADDR'], log_desc='SIM卡信息') return render(request, 'siminfo/siminfo.html', context={"nums": nums}) def post(self, request): req = request.POST.get('req') print("req:", req) simid = request.POST.get('simid') if simid: res = get_siminfo(simid) return HttpResponse(res.text) if req == "reqdat": page = int(request.POST.get('page')) print("page:", page) current_user = request.session.get('username') print("current_user:", current_user) current_user = MyUser.objects.get(username=current_user) if current_user.is_superuser != True and current_user.is_staff != True and current_user.is_agency != True: sta1 = SCDstatus.objects.filter(equip_id__equip_user=current_user) sta2 = CBDstatus.objects.filter(equip_id__equip_user=current_user) elif current_user.is_superuser != True and current_user.is_staff != True and current_user.is_agency == True: sta1 = SCDstatus.objects.filter(equip_id__equip_agency=current_user.user_agency) sta2 = CBDstatus.objects.filter(equip_id__equip_agency=current_user.user_agency) else: sta1 = SCDstatus.objects.all() sta2 = CBDstatus.objects.all() # 获取设备列表中的SIM卡的iccid号码： iccids = [] # 获取设备的id： equip_ids = [] # 获取设备的名称： equip_names = [] # 遍历查询流量信息： siminfos = [] for i in sta1: try: iccid = eval(i.scd_status)['iccid'] except: iccid = "" iccids.append(iccid) equip_ids.append(str(i.equip_id)) equip_names.append(str(i.equip_id.equip_name)) for i in sta2: try: iccid = eval(i.cbd_status)['iccid'] except: iccid = "" iccids.append(iccid) equip_ids.append(str(i.equip_id)) equip_names.append(str(i.equip_id.equip_name)) # print('all iccids :',iccids) # print('all equip_ids :',equip_ids) # print('all equip_names :',equip_names) iccids=iccids[(10*(page-1)):(page*10)] equip_ids=equip_ids[(10*(page-1)):(page*10)] equip_names=equip_names[(10*(page-1)):(page*10)] # print('iccids :',iccids) # print('equip_ids :',equip_ids) # print('equip_names :',equip_names) # 调用api查到的iccid号： equip_iccids = [] iccids_str = ",".join(iccids) print("iccids_str:",iccids_str) res = get_simsinfo(iccids_str).content print('res',res) siminfo_str=str(res, encoding = "utf-8") siminfo_dict=eval(siminfo_str) # 保存调用查询流量的api返回的数据； dat = [] for j in list(range(0,siminfo_dict['data']['count'])): equip_iccids.append(siminfo_dict['data']['card_list'][j]['iccid']) # print('equip_iccids:',equip_iccids) for j in list(range(0,len(iccids))): siminfo_dict=eval(siminfo_str) if (iccids[j] in equip_iccids) == True: dat.append(siminfo_dict['data']['card_list'][equip_iccids.index(iccids[j])]) else: dat.append({}) dat[j]['equip_id']=equip_ids[j] dat[j]['equip_name']=equip_names[j] # print("dat[%d]=%s" %(j,dat[j])) iccids[j] = ""# 每次判断玩将该值清空，防止多个设备用一个SIM卡测试时显示异常； # print('----- iccids :',iccids) print("------------------------",dat) data = json.dumps(dat,indent=4) # print("data:",data) return HttpResponse(data) # 查询单个卡流量信息： class SingleSim(ListView): def get(self, request): iccid = request.GET.get('iccid') print('iccid:',iccid) print('type iccid:',type(iccid)) re = get_siminfo(iccid) print('re:',re) return HttpResponse(re) def post(self, request): return HttpResponseBadRequest() # 查询多个卡流量信息： class MultiSim(ListView): def get(self, request): iccids = request.GET.get('iccids') print("iccids:", iccids) re = get_simsinfo(iccids) print('re:',re) return HttpResponse(re) def post(self, request): return HttpResponseBadRequest() from datetime import datetime, date class CJSONEncoder(json.JSONEncoder): def default(self, obj): if isinstance(obj, datetime): return obj.strftime('%Y-%m-%d %H:%M:%S') elif isinstance(obj, date): return obj.strftime('%Y-%m-%d') else: return json.JSONEncoder.default(self, obj) # 根据卡号查询设备 class Reverse_Lookup(ListView): def get(self, request): pass def post(self, request): iccid = request.POST.get('simid') print(iccid) cbd_obj = CBDstatus.objects.all().values() scd_obj = SCDstatus.objects.all().values() dat = [] for i in cbd_obj: try: if iccid == eval(i["cbd_status"])["iccid"]: print(eval(i["cbd_status"])["iccid"]) print(i["off_time"]) ic_eq = {"id":i["equip_id_id"],"lng":i["lng"],"lat":i["lat"],"offtime":i["off_time"]} dat.append(ic_eq) except Exception as e: # print("=====================================") # print(e) pass for x in scd_obj: try: # print(eval(x["scd_status"])["iccid"]) # print(x["off_time"]) if iccid == eval(x["scd_status"])["iccid"]: ic_eq = {"id":x["equip_id_id"],"lng":x["lng"],"lat":x["lat"],"offtime":x["off_time"]} dat.append(ic_eq) except Exception as e: # print("-----------------------------") # print(e) pass print(dat) data = json.dumps(dat, cls=CJSONEncoder) return HttpResponse(data) # 物联网卡查询套餐接口 class Iccid_Select(ListView): def get(self,request): pass def post(self,request): iccid = request.POST.get("iccid") url = 'https://api.openluat.com/sim/iotcard/product/fetch' payload = { 'iccid': iccid } appkey = 'KDlhc9VY4rWSXkbM' appsecret = 'DmwxcSB1b8dx7kLWqWMJNRLgyQhCvPJ7DHIfLdHIz9A18CifSHqJk3nWZ1vHv4cR' #在 http://sim.openluat.com/apiinterface 查看自己的appkey，appsecret。 auth = HTTPBasicAuth(appkey, appsecret) res = requests.post(url, json=payload, auth=auth) print(json.loads(res.text)) return HttpResponse(res.text) # 物联网卡充值接口 class Iccid_Pay(ListView): def get(self,request): pass def post(self,request): product_id = int(request.POST.get("product_id")) iccid = request.POST.get("iccid") num = int(request.POST.get("num")) extra_no = datetime.now().strftime("%Y%m%d%H%M%S") url = 'https://api.openluat.com/sim/iotcard/make_order' payload = { 'iccid': iccid, 'product_id': product_id, 'num': num, 'extra_no': extra_no, 'remark': '备注' } appkey = 'KDlhc9VY4rWSXkbM' appsecret = 'DmwxcSB1b8dx7kLWqWMJNRLgyQhCvPJ7DHIfLdHIz9A18CifSHqJk3nWZ1vHv4cR' #在 http://sim.openluat.com/apiinterface 查看自己的appkey，appsecret。 auth = HTTPBasicAuth(appkey, appsecret) res = requests.post(url, json=payload, auth=auth) return HttpResponse(res.text) # Sim卡信息页面 class SimInfo_View(ListView): def get(self,request): current_user = request.session.get('username') current_user = MyUser.objects.get(username=current_user) if current_user.is_superuser: role = "superuser" elif current_user.is_staff: role = "staff" elif current_user.is_agency: role = "agency" else: role = "user" return render(request, 'siminfo/siminfo.html', context={"role":role}) def post(self,request): pass # Sim卡信息列表 class SimInfo_List(ListView): def get(self,request): current_user = request.session.get('username') current_user = MyUser.objects.get(username=current_user) page = int(request.GET.get("page")) e_id = request.GET.get("e_id") iccid = request.GET.get("iccid") status = request.GET.get("status") if current_user.is_superuser != True and current_user.is_staff != True and current_user.is_agency != True: print("---用户") if e_id: simlist = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user,equip_id__equip_id__contains=e_id)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user,equip_id__equip_id__contains=e_id).count() elif iccid: simlist = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user,iccid__contains=iccid)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user,iccid__contains=iccid).count() elif status: status = int(status) simlist = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user,account_status=status)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user,account_status=status).count() else: simlist = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user).count() elif current_user.is_superuser != True and current_user.is_staff != True and current_user.is_agency == True: print("---代理商") if e_id: simlist = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency,equip_id__equip_id__contains=e_id)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency,equip_id__equip_id__contains=e_id).count() elif iccid: simlist = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency,iccid__contains=iccid)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency,iccid__contains=iccid).count() elif status: status = int(status) simlist = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency,account_status=status)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency,account_status=status).count() else: simlist = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency).count() else: print("---管理员") if e_id: simlist = Equip_SimInfo.objects.filter(equip_id__equip_id__contains=e_id)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_id__contains=e_id).count() elif iccid: simlist = Equip_SimInfo.objects.filter(iccid__contains=iccid)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(iccid__contains=iccid).count() elif status: status = int(status) simlist = Equip_SimInfo.objects.filter(account_status=status)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(account_status=status).count() else: simlist = Equip_SimInfo.objects.all()[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.all().count() return render(request, 'siminfo/siminfoList.html', context={"simlist":simlist,"nums":nums}) def post(self,request): pass # Sim卡信息列表 class SimInfo_ListView(ListView): def get(self,request): current_user = request.session.get('username') current_user = MyUser.objects.get(username=current_user) page = int(request.GET.get("page")) e_type = request.GET.get("e_type") status = request.GET.get("status") if current_user.is_superuser != True and current_user.is_staff != True and current_user.is_agency != True: print("---用户") if e_type != "" and status == "": simlist = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user,equip_type=e_type)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user,equip_type=e_type).count() elif e_type == "" and status != "": status = int(status) simlist = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user,account_status=status)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user,account_status=status).count() elif e_type != "" and status != "": status = int(status) simlist = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user,account_status=status,equip_type=e_type)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user,account_status=status,equip_type=e_type).count() else: simlist = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user).count() elif current_user.is_superuser != True and current_user.is_staff != True and current_user.is_agency == True: print("---代理商") if e_type != "" and status == "": simlist = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency,equip_type=e_type)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency,equip_type=e_type).count() elif e_type == "" and status != "": status = int(status) simlist = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency,account_status=status)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency,account_status=status).count() elif e_type != "" and status != "": status = int(status) simlist = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency,account_status=status,equip_type=e_type)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency,account_status=status,equip_type=e_type).count() else: simlist = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency).count() else: print("---管理员") if e_type != "" and status == "": simlist = Equip_SimInfo.objects.filter(equip_type=e_type)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(equip_type=e_type).count() elif e_type == "" and status != "": status = int(status) simlist = Equip_SimInfo.objects.filter(account_status=status)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(account_status=status).count() elif e_type != "" and status != "": status = int(status) simlist = Equip_SimInfo.objects.filter(account_status=status,equip_type=e_type)[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.filter(account_status=status,equip_type=e_type).count() else: simlist = Equip_SimInfo.objects.all()[(10*(page-1)):(page*10)] nums = Equip_SimInfo.objects.all().count() return render(request, 'siminfo/siminfoList.html', context={"simlist":simlist,"nums":nums}) def post(self,request): pass # Sim卡信息图表 class SimInfo_Chart(ListView): def get(self,request): pass def post(self,request): e_type = request.POST.get("etype") current_user = request.session.get('username') current_user = MyUser.objects.get(username=current_user) if current_user.is_superuser != True and current_user.is_staff != True and current_user.is_agency != True: print("---用户") sta = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user) if e_type: sta = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user,equip_type=e_type) elif current_user.is_superuser != True and current_user.is_staff != True and current_user.is_agency == True: print("---代理商") sta = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency) if e_type: sta = Equip_SimInfo.objects.filter(equip_type=e_type,equip_id__equip_agency=current_user.user_agency) else: print("---管理员") sta = Equip_SimInfo.objects.all() if e_type: sta = Equip_SimInfo.objects.filter(equip_type=e_type) dat = [] for i in sta: dat.append(i.account_status) data = json.dumps(dat) return HttpResponse(data) # sim卡数据导出 class SimInfo_Export(ListView): def get(self,request): current_user = request.session.get('username') current_user = MyUser.objects.get(username=current_user) status = request.GET.get("status") filename = request.GET.get("filename") if current_user.is_superuser != True and current_user.is_staff != True and current_user.is_agency != True: print("---用户") if status: status = int(status) simlist = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user,account_status=status) else: simlist = Equip_SimInfo.objects.filter(equip_id__equip_user=current_user) elif current_user.is_superuser != True and current_user.is_staff != True and current_user.is_agency == True: print("---代理商") if status: status = int(status) simlist = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency,account_status=status) else: simlist = Equip_SimInfo.objects.filter(equip_id__equip_agency=current_user.user_agency) else: print("---管理员") if status: status = int(status) simlist = Equip_SimInfo.objects.filter(account_status=status) else: simlist = Equip_SimInfo.objects.all() sta2 = [] for xx in simlist: sta2.append({"equip_id":xx.equip_id_id,"equip_name":xx.equip_name,"equip_type":xx.equip_type, "iccid":xx.iccid,"active_date":xx.active_date,"account_status":xx.account_status, "data_plan":xx.data_plan,"expiry_date":xx.expiry_date}) title = ["设备ID","设备名称","设备类型","ICCID","激活日期","SIM卡状态","套餐","到期时间"] book = xlwt.Workbook() # 创建一个excel对象 sheet = book.add_sheet('Sheet1',cell_overwrite_ok=True) # 添加一个sheet页 for i in range(len(title)): # 遍历列 sheet.write(0,i,title[i]) # 将title数组中的字段写入到0行i列中 for x in range(len(sta2)): #　遍历列表 sheet.write(x+1,0,sta2[x]["equip_id"]) sheet.write(x+1,1,sta2[x]["equip_name"]) sheet.write(x+1,2,sta2[x]["equip_type"]) sheet.write(x+1,3,sta2[x]["iccid"]) sheet.write(x+1,4,sta2[x]["active_date"]) sheet.write(x+1,5,sta2[x]["account_status"]) sheet.write(x+1,6,sta2[x]["data_plan"]) sheet.write(x+1,7,sta2[x]["expiry_date"]) sheet.col(0).width = 256 * 18 sheet.col(1).width = 256 * 18 sheet.col(2).width = 256 * 18 sheet.col(3).width = 256 * 18 sheet.col(4).width = 256 * 18 sheet.col(5).width = 256 * 18 sheet.col(6).width = 256 * 18 sheet.col(7).width = 256 * 18 # 写出到IO output = BytesIO() book.save(output) # 重新定位到开始 output.seek(0) response = HttpResponse(content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment;filename=%s.xls'%filename response.write(output.getvalue()) return response def post(self,request): pass