#!/usr/bin/env python3 # -*- coding: utf-8 -*- import calendar import datetime import hashlib import time import uuid import paypalrestsdk import xlrd import xlwt from django.db import transaction, connection from django.db.models import F, Sum, Count, Q from django.http import HttpResponse, StreamingHttpResponse from django.utils.encoding import escape_uri_path from django.views.generic.base import View from Ansjer.config import PAYPAL_CRD from Controller.Cron.CronTaskController import CronUpdateDataView from Controller.UnicomCombo.UnicomComboTaskController import UnicomComboTaskView from Model.models import VodBucketModel, CDKcontextModel, Store_Meal, Order_Model, \ UID_Bucket, ExperienceContextModel, Lang, CloudLogModel, UidSetModel, Unused_Uid_Meal, \ Device_Info, DeviceTypeModel, UnicomComboOrderInfo, AiService, CountryModel, CouponLang, CouponConfigModel, \ CouponCombo, CouponModel from Object.ResponseObject import ResponseObject from Object.TokenObject import TokenObject from Object.UnicomObject import UnicomObjeect from Service.CommonService import CommonService class serveManagement(View): def get(self, request, *args, **kwargs): request.encoding = 'utf-8' operation = kwargs.get('operation') return self.validation(request.GET, request, operation) def post(self, request, *args, **kwargs): request.encoding = 'utf-8' operation = kwargs.get('operation') return self.validation(request.POST, request, operation) def validation(self, request_dict, request, operation): language = request_dict.get('language', 'en') response = ResponseObject(language, 'pc') if operation == 'exportCloudUserList': # 导出云存用户信息 return self.exportCloudUserList(request_dict, response) elif operation == 'getCloudDataList': return self.getCloudDataList(request_dict, response) elif operation == 'vodOrderReconcile': return self.vodOrderReconcile(request, request_dict, response) elif operation == 'cloudDataExport': # 导出流失预警 return self.cloudDataExport(request, response) else: tko = TokenObject( request.META.get('HTTP_AUTHORIZATION'), returntpye='pc') # if tko.code != 0: # return response.json(tko.code) # response.lang = tko.lang userID = tko.userID if operation == 'getVodBucketList': return self.getVodBucketList(userID, request_dict, response) elif operation == 'addOrEditVodBucket': return self.addOrEditVodBucket(userID, request_dict, response) elif operation == 'deleteVodBucket': return self.deleteVodBucket(userID, request_dict, response) elif operation == 'getStoreMealList': return self.getStoreMealList(userID, request_dict, response) elif operation == 'addOrEditStoreMeal': return self.addOrEditStoreMeal(userID, request_dict, response) elif operation == 'deleteStoreMeal': return self.deleteStoreMeal(userID, request_dict, response) elif operation == 'getStoreMealLanguage': return self.getStoreMealLanguage( userID, request_dict, response) elif operation == 'addOrEditStoreMealLanguage': return self.addOrEditStoreMealLanguage( userID, request_dict, response) elif operation == 'deleteStoreMealLanguage': return self.deleteStoreMealLanguage( userID, request_dict, response) # CDK elif operation == 'getCdkList': return self.getCdkList(userID, request_dict, response) elif operation == 'createCdk': return self.createCdk(request_dict, response) elif operation == 'getVodStoreMeal': return self.getVodStoreMeal(response) elif operation == 'editExpress': return self.editExpress(request_dict, response) elif operation == 'deleteCdk': return self.deleteCdk(request_dict, response) elif operation == 'downloadCDK': return self.downloadCDK(request_dict, response) # 优惠券 elif operation == 'getCouponList': # 查询优惠券 return self.getCouponList(request_dict, response) elif operation == 'getCouponId': # 查询优惠券id return self.getCouponId(response) elif operation == 'addOrEditCoupon': # 添加/编辑优惠券 return self.addOrEditCoupon(request_dict, response) elif operation == 'deleteCoupon': # 删除优惠券 return self.deleteCoupon(request_dict, response) # 优惠券语言 elif operation == 'getCouponLangList': # 查询优惠券语言 return self.getCouponLangList(request_dict, response) elif operation == 'addOrEditCouponLang': # 添加/编辑优惠券语言 return self.addOrEditCouponLang(request_dict, response) elif operation == 'deleteCouponLang': # 删除优惠券语言 return self.deleteCouponLang(request_dict, response) # 优惠券使用 elif operation == 'getCouponUsingList': # 查询优惠券使用 return self.getCouponUsingList(request_dict, response) elif operation == 'editCouponUsing': # 编辑优惠券使用 return self.editCouponUsing(request_dict, response) elif operation == 'deleteCouponUsing': # 删除优惠券使用 return self.deleteCouponUsing(request_dict, response) elif operation == 'getDeviceOrderList': return self.getDeviceOrderList(request_dict, response) elif operation == 'deleteDeviceOrder': return self.deleteDeviceOrder(userID, request_dict, response) elif operation == 'getDevicePackageList': # 云存设备套餐 return self.getDevicePackageList(request_dict, response) elif operation == 'deleteDevicePackage': return self.deleteDevicePackage(userID, request_dict, response) elif operation == 'experiencereset': # 重置设备云存体验 return self.do_experience_reset(request_dict, userID, response) # 云存用户信息 elif operation == 'getCloudUserList': # 获取云存用户信息 return self.getCloudUserList(request_dict, response) elif operation == 'paypal-cycle-cancel': # 取消循环扣款 return self.paypal_cycle_cancel(request_dict, response) elif operation == 'distributeCoupons': # 发放云存优惠券 return self.distributeCoupons(request_dict, response) # 流失预警 elif operation == 'deviceAttritionAlert': return self.deviceAttritionAlert(request_dict, response) elif operation == 'deactivationPackage': # 停用套餐 return self.deactivationPackage(request_dict, response) else: return response.json(404) @staticmethod def paypal_cycle_cancel(request_dict, response): """ 取消云存循环扣款 """ order_id = request_dict.get('orderID', None) if not order_id: return response.json(444) order_qs = Order_Model.objects.filter(orderID=order_id) order_qs = order_qs.filter(~Q(agreement_id='')).values("agreement_id") if not order_qs.exists(): return response.json(800) paypalrestsdk.configure(PAYPAL_CRD) agreement_id = order_qs[0]['agreement_id'] try: now_time = int(time.time()) billing_agreement = paypalrestsdk.BillingAgreement.find(agreement_id) if billing_agreement.state != 'Active': Order_Model.objects.filter(agreement_id=agreement_id).update(agreement_id='', updTime=now_time) return response.json(0) cancel_note = {"note": "Canceling the agreement"} if billing_agreement.cancel(cancel_note): Order_Model.objects.filter(agreement_id=agreement_id).update(agreement_id='', updTime=now_time) return response.json(0) else: return response.json(10052) except Exception as e: print(repr(e)) return response.json(10052) @staticmethod def distributeCoupons(request_dict, response): user_id = request_dict.get('userID', None) coupon_id = request_dict.get('couponID', None) valid_time = request_dict.get('validTime', None) if not all([user_id, coupon_id]): return response.json(444) now_time = int(time.time()) try: CouponModel.objects.create(userID=user_id, coupon_config_id=coupon_id, valid_time=valid_time, distribute_time=now_time, create_time=now_time, update_time=now_time) return response.json(0) except Exception as e: return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def getVodBucketList(self, userID, request_dict, response): # 查询存储桶数据 print('request_dict: ', request_dict) isSelect = request_dict.get('isSelect', None) if isSelect: # 获取全部数据作为存储桶选项 vod_bucket_qs = VodBucketModel.objects.all().values('id', 'bucket') return response.json( 0, {'list': CommonService.qs_to_list(vod_bucket_qs)}) bucket = request_dict.get('bucket', None) mold = request_dict.get('mold', None) is_free = request_dict.get('is_free', None) pageNo = request_dict.get('pageNo', None) pageSize = request_dict.get('pageSize', None) if not all([pageNo, pageSize]): return response.json(444) page = int(pageNo) line = int(pageSize) try: if bucket or mold or is_free: # 条件查询 if bucket: vod_bucket_qs = VodBucketModel.objects.filter( bucket=bucket) elif mold: vod_bucket_qs = VodBucketModel.objects.filter( mold=int(mold)) elif is_free: vod_bucket_qs = VodBucketModel.objects.filter( is_free=int(is_free)) else: # 查询全部 vod_bucket_qs = VodBucketModel.objects.filter().all() total = len(vod_bucket_qs) vod_buckets = vod_bucket_qs[(page - 1) * line:page * line] vod_bucket_list = [] for vod_bucket in vod_buckets: vod_bucket_list.append({ 'bucketID': vod_bucket.id, 'bucket': vod_bucket.bucket, 'content': vod_bucket.content, 'mold': vod_bucket.mold, 'area': vod_bucket.area, 'region': vod_bucket.region, 'endpoint': vod_bucket.endpoint, 'is_free': vod_bucket.is_free, 'storeDay': vod_bucket.storeDay, 'region_id': vod_bucket.region_id, 'addTime': time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(vod_bucket.addTime)), 'updTime': time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(vod_bucket.updTime)), }) print('vod_bucket_list: ', vod_bucket_list) return response.json( 0, {'list': vod_bucket_list, 'total': total}) except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def addOrEditVodBucket(self, userID, request_dict, response): # 添加/编辑存储桶 print('request_dict: ', request_dict) bucketID = request_dict.get('bucketID', None) bucket = request_dict.get('bucket', '').strip() # 移除字符串头尾的空格 content = request_dict.get('content', '').strip() mold = int(request_dict.get('mold', 1)) area = request_dict.get('area', '').strip() region = request_dict.get('region', '').strip() endpoint = request_dict.get('endpoint', '').strip() is_free = int(request_dict.get('is_free', 0)) storeDay = int(request_dict.get('storeDay', 0)) region_id = int(request_dict.get('region_id', 1)) isEdit = request_dict.get('isEdit', None) if not all([bucket, content, area, region, endpoint]): return response.json(444) try: now_time = int(time.time()) vod_bucket_data = { 'bucket': bucket, 'content': content, 'mold': mold, 'area': area, 'region': region, 'endpoint': endpoint, 'is_free': is_free, 'storeDay': storeDay, 'region_id': region_id, } if isEdit: if not bucketID: return response.json(444) vod_bucket_data['updTime'] = now_time VodBucketModel.objects.filter( id=bucketID).update( **vod_bucket_data) else: vod_bucket_data['addTime'] = now_time VodBucketModel.objects.create(**vod_bucket_data) return response.json(0) except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def deleteVodBucket(self, userID, request_dict, response): # 删除存储桶 print('request_dict: ', request_dict) bucketID = request_dict.get('bucketID', None) if not bucketID: return response.json(444) try: VodBucketModel.objects.filter(id=bucketID).delete() return response.json(0) except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def getStoreMealList(self, userID, request_dict, response): # 获取云存套餐信息数据 print('request_dict: ', request_dict) isSelect = request_dict.get('isSelect', None) if isSelect: # 获取套餐ID作为选项 store_meal_qs = Store_Meal.objects.all().values('id', 'bucket__bucket') return response.json( 0, {'list': CommonService.qs_to_list(store_meal_qs)}) bucket = request_dict.get('bucket', None) pageNo = request_dict.get('pageNo', None) pageSize = request_dict.get('pageSize', None) if not all([pageNo, pageSize]): return response.json(444) page = int(pageNo) line = int(pageSize) try: if bucket: # 条件查询 bucket_id = VodBucketModel.objects.filter( bucket=bucket).values('id')[0]['id'] store_meal_qs = Store_Meal.objects.filter( bucket_id=bucket_id) else: # 查询全部 store_meal_qs = Store_Meal.objects.filter() store_meal_val = store_meal_qs.values( 'id', 'bucket__bucket', 'day', 'expire', 'commodity_type', 'commodity_code', 'is_discounts', 'discount_price', 'virtual_price', 'price', 'currency', 'symbol', 'is_show', 'add_time', 'update_time') total = len(store_meal_val) store_meals = store_meal_val[(page - 1) * line:page * line] store_meal_list = [] for store_meal in store_meals: # 获取支付方式列表 pay_type_list = [ pay_type['id'] for pay_type in Store_Meal.objects.get( id=store_meal['id']).pay_type.values('id')] # 组织响应数据 store_meal_list.append({ 'storeMealID': store_meal['id'], 'bucket': store_meal['bucket__bucket'], 'day': store_meal['day'], 'expire': store_meal['expire'], 'commodity_type': store_meal['commodity_type'], 'pay_type': pay_type_list, 'commodity_code': store_meal['commodity_code'], 'is_discounts': store_meal['is_discounts'], 'discount_price': store_meal['discount_price'], 'virtual_price': store_meal['virtual_price'], 'price': store_meal['price'], 'currency': store_meal['currency'], 'symbol': store_meal['symbol'], 'is_show': store_meal['is_show'], 'addTime': store_meal['add_time'].strftime("%Y-%m-%d %H:%M:%S"), 'updTime': store_meal['update_time'].strftime("%Y-%m-%d %H:%M:%S"), }) print('store_meal_list: ', store_meal_list) return response.json( 0, {'list': store_meal_list, 'total': total}) except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def addOrEditStoreMeal(self, userID, request_dict, response): # 添加/编辑套餐 print('request_dict: ', request_dict) storeMealID = request_dict.get('storeMealID', None) bucket = request_dict.get('bucket', '') day = int(request_dict.get('day', 0)) expire = int(request_dict.get('expire', 0)) commodity_type = int(request_dict.get('commodity_type', 0)) pay_type = request_dict.get( 'pay_type', '')[ 1:-1].split(',') # '[1,2]' -> ['1','2'] commodity_code = request_dict.get('commodity_code', '') is_discounts = int(request_dict.get('is_discounts', 0)) discount_price = request_dict.get('discount_price', '') virtual_price = request_dict.get('virtual_price', '') price = request_dict.get('price', '') currency = request_dict.get('currency', '') symbol = request_dict.get('symbol', '') is_show = int(request_dict.get('is_show', 0)) isEdit = request_dict.get('isEdit', None) if not all([bucket, pay_type, price, currency, symbol]): return response.json(444) try: bucket_id = VodBucketModel.objects.filter( bucket=bucket).values('id')[0]['id'] store_meal_data = { 'bucket_id': bucket_id, 'day': day, 'expire': expire, 'commodity_type': commodity_type, 'commodity_code': commodity_code, 'is_discounts': is_discounts, 'discount_price': discount_price, 'virtual_price': virtual_price, 'price': price, 'currency': currency, 'symbol': symbol, 'is_show': is_show, } if isEdit: if not storeMealID: return response.json(444) Store_Meal.objects.filter( id=storeMealID).update( **store_meal_data) Store_Meal.objects.get(id=storeMealID).pay_type.set(pay_type) else: Store_Meal.objects.create( **store_meal_data).pay_type.set(pay_type) return response.json(0) except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def deleteStoreMeal(self, userID, request_dict, response): # 删除套餐信息 print('request_dict: ', request_dict) storeMealID = request_dict.get('storeMealID', None) if not storeMealID: return response.json(444) try: Store_Meal.objects.filter(id=storeMealID).delete() return response.json(0) except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def getStoreMealLanguage(self, userID, request_dict, response): # 获取套餐语言 print('request_dict: ', request_dict) storeMealID = request_dict.get('storeMealID', None) pageNo = request_dict.get('pageNo', None) pageSize = request_dict.get('pageSize', None) if not all([pageNo, pageSize]): return response.json(444) page = int(pageNo) line = int(pageSize) try: if storeMealID: # 条件查询 store_meal_lang_qs = Store_Meal.objects.filter(id=storeMealID) else: # 查询全部 store_meal_lang_qs = Store_Meal.objects.filter( lang__isnull=False) store_meal_lang_val = store_meal_lang_qs.values( 'id', 'lang__id', 'lang__lang', 'lang__title', 'lang__content', 'lang__discount_content', ) total = len(store_meal_lang_val) store_meal_langs = store_meal_lang_val[( page - 1) * line:page * line] store_meal_lang_list = [] for store_meal_lang in store_meal_langs: store_meal_lang_list.append({ 'storeMealID': store_meal_lang['id'], 'langID': store_meal_lang['lang__id'], 'lang': store_meal_lang['lang__lang'], 'title': store_meal_lang['lang__title'], 'content': store_meal_lang['lang__content'], 'discountContent': store_meal_lang['lang__discount_content'], }) print('store_meal_lang_list: ', store_meal_lang_list) return response.json( 0, {'list': store_meal_lang_list, 'total': total}) except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def addOrEditStoreMealLanguage(self, userID, request_dict, response): # 添加/编辑套餐语言 print('request_dict: ', request_dict) storeMealID = request_dict.get('storeMealID', None) lang = request_dict.get('lang', None) title = request_dict.get('title', None) content = request_dict.get('content', None) discount_content = request_dict.get('discount_content', '') isEdit = request_dict.get('isEdit', None) if not all([storeMealID, lang, title, content]): return response.json(444) try: # 查询套餐是否存在 store_meal_qs = Store_Meal.objects.get(id=storeMealID) if not store_meal_qs: return response.json(173) if isEdit: # 编辑 langID = request_dict.get('langID', None) if not langID: return response.json(444) Lang.objects.filter( id=langID).update( lang=lang, title=title, content=content, discount_content=discount_content) else: # 添加 lang_obj = Lang.objects.filter( lang=lang, title=title, content=content, discount_content=discount_content) if not lang_obj.exists(): # 数据不存在,lang表创建数据 Lang.objects.create( lang=lang, title=title, content=content, discount_content=discount_content) lang_obj = Lang.objects.filter( lang=lang, title=title, content=content, discount_content=discount_content) store_meal_qs.lang.add(*lang_obj) # store_meal表添加语言数据 return response.json(0) except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def deleteStoreMealLanguage(self, userID, request_dict, response): # 删除套餐语言 storeMealID = request_dict.get('storeMealID', None) langID = request_dict.get('langID', None) if not all([storeMealID, langID]): return response.json(444) try: storeMeal_qs = Store_Meal.objects.get(id=storeMealID) if not storeMeal_qs: return response.json(173) lang_qs = Lang.objects.filter(id=langID) storeMeal_qs.lang.remove(*lang_qs) return response.json(0) except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def getCdkList(self, userID, request_dict, response): # 获取激活码列表 pageNo = request_dict.get('pageNo', None) pageSize = request_dict.get('pageSize', None) cdk = request_dict.get('cdk', None) order = request_dict.get('order', None) is_activate = request_dict.get('is_activate', None) mold = request_dict.get('mold', None) lang = request_dict.get('lang', 'cn') if not all([pageNo, pageSize]): return response.json(444) page = int(pageNo) line = int(pageSize) try: if cdk: searchVal = cdk.strip() if order: searchVal = order.strip() if is_activate: searchVal = is_activate.strip() cdk_qs = CDKcontextModel.objects.filter().all() if cdk: cdk_qs = cdk_qs.filter(cdk__contains=searchVal) if order: cdk_qs = cdk_qs.filter(order__contains=searchVal) if is_activate: cdk_qs = cdk_qs.filter(is_activate=searchVal) if mold: cdk_qs = cdk_qs.filter(rank__bucket__mold=mold) cdk_qs = cdk_qs.filter(rank__lang__lang=lang) cdk_qs = cdk_qs.annotate(rank__title=F('rank__lang__title')) cdk_qs = cdk_qs.values( 'id', 'cdk', 'create_time', 'valid_time', 'is_activate', 'is_down', 'rank__id', 'rank__title', 'order', 'create_time', 'rank__bucket__mold') cdk_qs = cdk_qs.order_by('-create_time') # 根据CDK创建时间降序排序 count = cdk_qs.count() cdk_qs = cdk_qs[(page - 1) * line:page * line] return response.json( 0, {'list': list(cdk_qs), 'total': count}) except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def editExpress(self, request_dict, response): order_id = request_dict.get('orderID', None) express_id = request_dict.get('express_id', None) if not all([order_id, express_id]): return response.json(444, {'error param': 'orderID or express_id'}) try: CDKcontextModel.objects.filter(order=order_id).update(express_id=express_id) return response.json(0) except Exception as e: return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def getVodStoreMeal(self, response): try: store_meal = Store_Meal.objects.filter(Q(is_show=0), ~Q(commodity_code='paypal_cycle'), Q(lang__lang='cn')).values('id', 'lang__title', 'lang__content', 'pixel_level') if not store_meal.exists(): return response.json(0, []) store_meal_list = [] for item in store_meal: pixel_content = '4k像素' if item['pixel_level'] == 1 else '4k像素以下' store_meal_list.append({'id': item['id'], 'title': item['lang__title'] + '--' + item['lang__content'] + '(' + pixel_content + ')'}) return response.json(0, store_meal_list) except Exception as e: return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def createCdk(self, request_dict, response): cdk_num = request_dict.get("cdknum", None) mold = request_dict.get('mold', None) order = request_dict.get('order', None) rank = request_dict.get('rank', None) if not all([cdk_num, rank]): return response.json(444, {'error param': 'cdknum or rank'}) store_meal = Store_Meal.objects.filter(id=rank) # sm_qs = Store_Meal.objects.filter( # pay_type__payment='cdk_pay', bucket__mold=mold, is_show=0) if not store_meal.exists(): return response.json(173) cdk_list = [] for i in range(int(cdk_num)): now_time = int(time.time()) cdk = hashlib.md5((str(uuid.uuid1()) + str(now_time)).encode('utf-8')).hexdigest() cdk_model = CDKcontextModel( cdk=cdk, create_time=now_time, valid_time=0, is_activate=0, is_down=0, rank_id=rank, order=order, ) cdk_list.append(cdk_model) try: CDKcontextModel.objects.bulk_create(cdk_list) except Exception as e: return response.json(404, repr(e)) else: return response.json(0) def deleteCdk(self, request_dict, response): cdk_id = request_dict.get("id", None) try: CDKcontextModel.objects.get(id=cdk_id).delete() return response.json(0) except Exception as e: return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def downloadCDK(self, request_dict, response): region = request_dict.get('region', None) content = '' if region == 'cn': # 下载国内未使用激活码 content += '激活码(国内)\n' cdk_inactivate_qs = CDKcontextModel.objects.filter(is_down=0, is_activate=0, rank__bucket__mold=0, rank__is_show=0).values('cdk') else: # 下载国外未使用激活码 content += '激活码(国外)\n' cdk_inactivate_qs = CDKcontextModel.objects.filter(is_down=0, is_activate=0, rank__bucket__mold=1, rank__is_show=0).values('cdk') for cdk_inactivate in cdk_inactivate_qs: content += cdk_inactivate['cdk'] + '\n' # print(content) cdk_inactivate_qs.update(is_down=1) response = StreamingHttpResponse(content) response['Content-Type'] = 'application/octet-stream' response['Content-Disposition'] = 'attachment;filename="CDK.txt"' return response @staticmethod def getCouponList(request_dict, response): combo_id = request_dict.get('comboID', None) pageNo = request_dict.get('pageNo', None) pageSize = request_dict.get('pageSize', None) if not all([pageNo, pageSize]): return response.json(444) page = int(pageNo) line = int(pageSize) try: if combo_id: # 根据套餐id查询 coupon_id_list = CouponCombo.objects.filter(combo_id=combo_id).values_list('coupon_id', flat=True) coupon_qs = CouponConfigModel.objects.filter(id__in=coupon_id_list).values( 'id', 'type', 'use_range', 'coupon_discount') else: coupon_qs = CouponConfigModel.objects.filter().values( 'id', 'type', 'use_range', 'coupon_discount') count = coupon_qs.count() coupon_list = list(coupon_qs[(page - 1) * line:page * line]) for coupon in coupon_list: coupon['combo_id'] = 'NA' coupon_combo_qs = CouponCombo.objects.filter(coupon_id=coupon['id']).values('combo_id') if coupon_combo_qs.exists(): coupon['combo_id'] = coupon_combo_qs[0]['combo_id'] return response.json(0, {'list': coupon_list, 'total': count}) except Exception as e: return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) @staticmethod def getCouponId(response): coupon_qs = CouponConfigModel.objects.all().values('id') return response.json(0, {'list': list(coupon_qs)}) @staticmethod def addOrEditCoupon(request_dict, response): coupon_id = request_dict.get('couponID', None) combo_id = request_dict.get('comboID', None) coupon_type = int(request_dict.get('type', 0)) use_range = request_dict.get('useRange', '') coupon_discount = request_dict.get('couponDiscount', '') is_edit = request_dict.get('isEdit', None) if not all([coupon_type, use_range, coupon_discount]): return response.json(444) try: now_time = int(time.time()) coupon_data = { 'type': coupon_type, 'use_range': use_range, 'coupon_discount': coupon_discount } if is_edit: if not coupon_id: return response.json(444) CouponConfigModel.objects.filter(id=coupon_id).update(**coupon_data) CouponCombo.objects.filter(coupon_id=coupon_id).update(combo_id=combo_id, update_time=now_time) else: coupon = CouponConfigModel.objects.create(**coupon_data) coupon_id = coupon.id # 关联套餐 CouponCombo.objects.create(coupon_id=coupon_id, combo_id=combo_id, create_time=now_time, update_time=now_time) return response.json(0) except Exception as e: return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) @staticmethod def deleteCoupon(request_dict, response): coupon_id = request_dict.get('couponID', None) if not coupon_id: return response.json(444) try: CouponConfigModel.objects.filter(id=coupon_id).delete() return response.json(0) except Exception as e: return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) @staticmethod def getCouponLangList(request_dict, response): coupon_id = request_dict.get('couponID', None) page = request_dict.get('pageNo', None) line = request_dict.get('pageSize', None) if not all([page, line]): return response.json(444) page = int(page) line = int(line) try: if coupon_id: # 条件查询 coupon_qs = CouponConfigModel.objects.filter(id=coupon_id) else: # 查询语言id非空数据 coupon_qs = CouponConfigModel.objects.filter(~Q(lang__id=None)) coupon_lang_qs = coupon_qs.values( 'id', 'lang__id', 'lang__lang', 'lang__instruction', 'lang__quota', 'lang__unit', 'lang__remark' ) total = len(coupon_lang_qs) coupon_lang_qs = coupon_lang_qs[(page - 1) * line:page * line] coupon_lang_list = [] for coupon_lang in coupon_lang_qs: coupon_lang_list.append({ 'couponID': coupon_lang['id'], 'langID': coupon_lang['lang__id'], 'lang': coupon_lang['lang__lang'], 'instruction': coupon_lang['lang__instruction'], 'quota': coupon_lang['lang__quota'], 'unit': coupon_lang['lang__unit'], 'remark': coupon_lang['lang__remark'] }) return response.json(0, {'list': coupon_lang_list, 'total': total}) except Exception as e: return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) @staticmethod def addOrEditCouponLang(request_dict, response): coupon_id = request_dict.get('couponID', None) lang = request_dict.get('lang', None) instruction = request_dict.get('instruction', '') quota = request_dict.get('quota', '') unit = request_dict.get('unit', '') unit = request_dict.get('unit', '') remark = request_dict.get('remark', None) is_edit = request_dict.get('isEdit', None) if not all([coupon_id, lang]): return response.json(444) try: # 查询优惠券是否存在 coupon_qs = CouponConfigModel.objects.get(id=coupon_id) if not coupon_qs: return response.json(173) if is_edit: # 编辑 lang_id = request_dict.get('langID', None) if not lang_id: return response.json(444) CouponLang.objects.filter( id=lang_id).update( lang=lang, instruction=instruction, quota=quota, unit=unit, remark=remark ) else: # 添加 lang_obj = CouponLang.objects.filter( lang=lang, instruction=instruction, quota=quota, unit=unit, remark=remark ) if not lang_obj.exists(): # 数据不存在,lang表创建数据 CouponLang.objects.create( lang=lang, instruction=instruction, quota=quota, unit=unit, remark=remark ) lang_obj = CouponLang.objects.filter( lang=lang, instruction=instruction, quota=quota, unit=unit, remark=remark ) coupon_qs.lang.add(*lang_obj) # coupon_config表添加语言数据 return response.json(0) except Exception as e: return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) @staticmethod def deleteCouponLang(request_dict, response): coupon_id = request_dict.get('couponID', None) lang_id = request_dict.get('langID', None) if not all([coupon_id, lang_id]): return response.json(444) try: coupon_qs = CouponConfigModel.objects.get(id=coupon_id) if not coupon_qs: return response.json(173) lang_qs = CouponLang.objects.filter(id=lang_id) coupon_qs.lang.remove(*lang_qs) return response.json(0) except Exception as e: return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) @staticmethod def getCouponUsingList(request_dict, response): user_id = request_dict.get('userID', None) use_status = request_dict.get('useStatus', None) page_no = request_dict.get('pageNo', None) page_size = request_dict.get('pageSize', None) if not all([page_no, page_size]): return response.json(444) page = int(page_no) line = int(page_size) try: coupon_qs = CouponModel.objects.all() if user_id: coupon_qs = coupon_qs.filter(userID=user_id) if use_status: coupon_qs = coupon_qs.filter(use_status=use_status) if not coupon_qs.exists(): return response.json(0, []) coupon_qs = coupon_qs.values( 'id', 'use_status', 'distribute_time', 'valid_time', 'userID', 'update_time', 'create_time') count = coupon_qs.count() coupons = coupon_qs[(page - 1) * line:page * line] coupon_list = [] for coupon in coupons: coupon_list.append({ 'id': coupon['id'], 'useStatus': coupon['use_status'], 'distributeTime': coupon['distribute_time'], 'validTime': coupon['valid_time'], 'userID': coupon['userID'], 'updateTime': coupon['update_time'], 'createTime': coupon['create_time'], }) return response.json(0, {'list': coupon_list, 'total': count}) except Exception as e: return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) @staticmethod def editCouponUsing(request_dict, response): id = request_dict.get('id', None) use_status = request_dict.get('useStatus', 0) valid_time = request_dict.get('validTime', None) if not all([id, use_status, valid_time]): return response.json(444) try: now_time = int(time.time()) CouponModel.objects.filter(id=id).update(use_status=use_status, valid_time=valid_time, update_time=now_time) return response.json(0) except Exception as e: return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) @staticmethod def deleteCouponUsing(request_dict, response): id = request_dict.get('id', None) if not id: return response.json(444) try: CouponModel.objects.filter(id=id).delete() return response.json(0) except Exception as e: return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def getDeviceOrderList(self, request_dict, response): print('request_dict: ', request_dict) pageNo = request_dict.get('pageNo', None) pageSize = request_dict.get('pageSize', None) uid = request_dict.get('uid', None) channel = request_dict.get('channel', None) orderID = request_dict.get('orderID', None) userID__username = request_dict.get('userID__username', None) currency = request_dict.get('currency', None) payType = request_dict.get('payType', None) status = request_dict.get('status', None) timeRange = request_dict.getlist('timeRange[]', None) orderType = request_dict.get('orderType', None) serialNumber = request_dict.get('serialNumber', None) trade_no = request_dict.get('tradeNo', None) userID__phone = request_dict.get('userID__phone', None) if not all([pageNo, pageSize]): return response.json(444) page = int(pageNo) line = int(pageSize) try: omqs = Order_Model.objects.all() # 筛选指定设备id的订单 if uid: omqs = omqs.filter(UID=uid) if channel: omqs = omqs.filter(channel=channel) if orderID: omqs = omqs.filter(orderID=orderID) if userID__username: omqs = omqs.filter(userID__username=userID__username) if currency: omqs = omqs.filter(currency=currency) if payType: omqs = omqs.filter(payType=payType) if status: omqs = omqs.filter(status=status) if orderType: omqs = omqs.filter(order_type=int(orderType)) if trade_no: omqs = omqs.filter(trade_no=trade_no) if userID__phone: omqs = omqs.filter(userID__phone=userID__phone) if serialNumber: device_uid = CommonService.query_uid_with_serial(serialNumber) omqs = omqs.filter(UID=device_uid) if timeRange: startTime, endTime = int( timeRange[0][:-3]), int(timeRange[1][:-3]) omqs = omqs.filter( addTime__gte=startTime, addTime__lte=endTime) if not omqs.exists(): return response.json(0, []) order_list = [] count = omqs.count() order_ql = omqs.values("orderID", "UID", "userID__username", "userID__NickName", "channel", "desc", "price", "refunded_amount", "currency", "addTime", "updTime", "paypal", "payType", "rank__day", "rank__price", "status", "order_type", "paymentID", "trade_no", "payTime", "userID__region_country", "userID__phone") order_ql = order_ql.order_by('-addTime') # 根据CDK创建时间降序排序 order_ql = order_ql[(page - 1) * line:page * line] for order in order_ql: # 查询国家信息 country = 'N/A' country_qs = CountryModel.objects.filter(id=order['userID__region_country']).values('country_name') if country_qs.exists(): country = country_qs[0]['country_name'] serialNumber = CommonService.query_serial_with_uid(order['UID']) data = { 'orderID': order['orderID'], 'UID': order['UID'], 'userID__username': order['userID__username'], 'userID__NickName': order['userID__NickName'], 'channel': order['channel'], 'desc': order['desc'], 'price': order['price'], 'refunded_amount': order['refunded_amount'], 'currency': order['currency'], 'addTime': order['addTime'], 'updTime': order['updTime'], 'paypal': order['paypal'], 'payType': order['payType'], 'rank__day': order['rank__day'], 'rank__price': order['rank__price'], 'status': order['status'], 'order_type': order['order_type'], 'payTime': order['payTime'] if order['payTime'] else 'N/A', 'serialNumber': 'N/A' if serialNumber == order['UID'] else serialNumber, 'country': country, 'userID__phone': order['userID__phone'], } # 订单显示(或不显示)停用/退款功能 if order['order_type'] == 0: # 云存 uid_bucket = UID_Bucket.objects.filter(uid=order['UID']).values('use_status') user_status = uid_bucket[0]['use_status'] if uid_bucket.exists() else '' if user_status != '': data['user_status'] = user_status else: data['user_status'] = 2 elif order['order_type'] == 1: # ai ai_service_qs = AiService.objects.filter( Q(orders_id=order['orderID']) & ~Q(use_status=2)) if ai_service_qs.exists(): data['user_status'] = 1 else: data['user_status'] = 2 elif order['order_type'] == 2: # 联通4G unicom_combor_order_qs = UnicomComboOrderInfo.objects.filter( Q(order_id=order['orderID']) & ~Q(status=2)) if unicom_combor_order_qs.exists(): data['user_status'] = 1 else: data['user_status'] = 2 # 添加PayPal交易号字段 data['trade_no'] = 'N/A' if data['payType'] == 1: data['trade_no'] = order['trade_no'] if order['trade_no'] else 'N/A' data['express_id'] = '' if data['payType'] == 11: cdk_context_qs = CDKcontextModel.objects.filter(order=data['orderID']).values('express_id') if cdk_context_qs.exists(): data['express_id'] = cdk_context_qs[0]['express_id'] order_list.append(data) return response.json( 0, {'list': order_list, 'total': count}) except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def vodOrderReconcile(self, request, request_dict, response): file = request.FILES.get('file', None) if not all([file]): return response.json(444, {'error param': 'file'}) try: rd_book = xlrd.open_workbook(filename=None, file_contents=file.read()) rd_sheet = rd_book.sheet_by_index(0) date = rd_sheet.cell_value(1, 0) month = int(date.split('/')[0]) year = int(date.split('/')[2]) last_day = calendar.monthrange(year, month)[1] start_time = datetime.datetime(year, month, 1) end_time = datetime.datetime(year, month, last_day) + datetime.timedelta(hours=24) start_time = CommonService.str_to_timestamp(start_time.strftime('%Y-%m-%d %H:%M:%S')) end_time = CommonService.str_to_timestamp(end_time.strftime('%Y-%m-%d %H:%M:%S')) wt_book = xlwt.Workbook(encoding='utf-8') wt_sheet = wt_book.add_sheet('对账结果', cell_overwrite_ok=True) row_start = 0 # 第一部分表格填写 first_header = ['支付类型', '销售总金额', '销售总数量', '退款总额', '退款总数量', '应收金额', '平台手续费', '账务实收', '金额相差'] for index, content in enumerate(first_header): wt_sheet.write(row_start, index, content) row_start += 1 all_order_qs = Order_Model.objects.filter(addTime__gte=start_time, addTime__lt=end_time, status__in=[1, 5, 6]) trade_no_list = [] paid_order_qs = all_order_qs.filter(status=1) for i in paid_order_qs: if i.trade_no not in rd_sheet.col_values(9, 1): trade_no_list.append(i.trade_no) # 已付款订单 paid = all_order_qs.filter(payType=1, status=1).aggregate(total=Sum('price'), count=Count('UID')) paid_total = paid['total'] if paid['total'] else 0 paid_count = paid['count'] if paid['count'] else 0 # 全额退款订单 refund = all_order_qs.filter(payType=1, status__in=[5, 6]).aggregate(total=Sum('refunded_amount'), count=Count('UID')) refund_total = refund['total'] if refund['total'] else 0 refund_count = refund['count'] if refund['count'] else 0 wt_sheet.write(row_start, 0, 'PayPal') wt_sheet.write(row_start, 1, paid_total + refund_total) wt_sheet.write(row_start, 2, paid_count + refund_count) wt_sheet.write(row_start, 3, refund_total) wt_sheet.write(row_start, 4, refund_count) wt_sheet.write(row_start, 5, paid_total) wt_sheet.write(row_start, 8, label=xlwt.Formula('f{row}-h{row}+g{row}'.format(row=row_start + 1))) row_start += 1 # 第二部分表格填写 row_start += 2 paypal_money = 0 fee_money = 0 for row in range(rd_sheet.nrows): if row == 0: wt_sheet.write(row_start, 0, '是否匹配账单') for col in range(rd_sheet.ncols): value = rd_sheet.cell_value(row, col) temp_col = col + 1 wt_sheet.write(row_start, temp_col, str(value)) row_start += 1 continue if rd_sheet.cell_value(row, 0) == '合计': break paypal_money += rd_sheet.cell_value(row, 7) fee_money += rd_sheet.cell_value(row, 6) transaction_id = rd_sheet.cell_value(row, 9) order_qs = all_order_qs.filter(trade_no=transaction_id) if not order_qs.exists(): col_value_list = rd_sheet.row_values(row) col_value_list.insert(0, '否') for index, value in enumerate(col_value_list): wt_sheet.write(row_start, index, str(value)) row_start += 1 # 第三部分表格填写 row_start += 1 third_header = ['是否匹配账单', '交易ID', '订单ID', '设备UID', '用户名', '账号昵称', '通道', '商品描述', '支付方式', '价格', '支付状态', '已退金额', '添加时间', '更新时间'] diff_order_qs = all_order_qs.filter(trade_no__in=trade_no_list).values('trade_no', 'orderID', 'UID', 'userID__username', 'userID__NickName', 'channel', 'desc', 'payType', 'price', 'status', 'refunded_amount', 'addTime', 'updTime') for index, content in enumerate(third_header): wt_sheet.write(row_start, index, content) row_start += 1 for item in diff_order_qs: values_list = item.values() for col, content in enumerate(values_list): if col == 0: wt_sheet.write(row_start, col, '否') if col == 7: content = 'PayPal' if col == 9: if content == 1: content = '支付成功' elif content == 5: content = '全额退款' elif content == 6: content = '部分退款' if col in [11, 12]: content = CommonService.timestamp_to_str(int(content)) wt_sheet.write(row_start, col + 1, str(content)) row_start += 1 wt_sheet.write(1, 6, fee_money) wt_sheet.write(1, 7, paypal_money) res = HttpResponse(content_type='application/vnd.ms-excel') res['Content-Disposition'] = 'attachment; filename={}'.format(escape_uri_path(file.name)) wt_book.save(res) return res except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def deleteDeviceOrder(self, userID, request_dict, response): orderID = request_dict.get('orderID', None) if orderID: Order_Model.objects.filter(orderID=orderID).delete() return response.json(0) else: return response.json(444) def getDevicePackageList(self, request_dict, response): pageNo = request_dict.get('pageNo', None) pageSize = request_dict.get('pageSize', None) uid = request_dict.get('uid', None) if not all([pageNo, pageSize]): return response.json(444) page = int(pageNo) line = int(pageSize) try: ubqs = UID_Bucket.objects.all() if uid: ubqs = ubqs.filter(uid__contains=uid) if not ubqs.exists(): return response.json(0, []) count = ubqs.count() ubqs = ubqs.values( 'id', 'uid', 'channel', 'status', 'endTime', 'bucket__bucket', 'bucket__storeDay', 'bucket__area') ubqs = ubqs.order_by('-addTime') # 根据CDK创建时间降序排序 ubqs = ubqs[(page - 1) * line:page * line] return response.json( 0, {'list': list(ubqs), 'total': count}) except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def deleteDevicePackage(self, userID, request_dict, response): orderID = request_dict.get('orderID', None) if orderID: Order_Model.objects.filter(orderID=orderID).delete() return response.json(0) else: return response.json(444) # 重置设备云存体验 def do_experience_reset(self, request_dict, userID, response): bid = request_dict.get("id", None) ubq = UID_Bucket.objects.filter(id=bid) if ubq: eq = ExperienceContextModel.objects.filter(uid=ubq[0].uid) if eq: eq.delete() Order_Model.objects.filter(uid_bucket_id=bid).delete() ubq.delete() return response.json(0) else: return response.json(10007) else: return response.json(0, '重置云存体验失败') @classmethod def getCloudUserList(cls, request_dict, response): print('request_dict: ', request_dict) # UID_Bucket表查询数据 uid = request_dict.get('uid', None) status = request_dict.get('status', None) use_status = request_dict.get('use_status', None) has_unused = request_dict.get('has_unused', None) addTimeRange = request_dict.getlist('addTimeRange[]', None) endTimeRange = request_dict.getlist('endTimeRange[]', None) # Order_Model表查询数据 username = request_dict.get('username', None) phone = request_dict.get('phone', None) userEmail = request_dict.get('userEmail', None) payType = request_dict.get('payType', None) # uid_set 表查询 ucode = request_dict.getlist('ucode', None) version = request_dict.getlist('version', None) # 日志表查询 logTimeRange = request_dict.getlist('logTimeRange[]', None) # 序列号查询 serialNumber = request_dict.get('serialNumber', None) pageNo = request_dict.get('pageNo', None) pageSize = request_dict.get('pageSize', None) if not all([pageNo, pageSize]): return response.json(444) page = int(pageNo) line = int(pageSize) try: uid_bucket_qs = UID_Bucket.objects.all() if uid: uid_bucket_qs = uid_bucket_qs.filter(uid__icontains=uid) if status: uid_bucket_qs = uid_bucket_qs.filter(status=status) if use_status: uid_bucket_qs = uid_bucket_qs.filter(use_status=use_status) if has_unused: uid_bucket_qs = uid_bucket_qs.filter(has_unused=has_unused) if serialNumber: device_info_qs = Device_Info.objects.filter(serial_number=serialNumber).values('UID') uid = device_info_qs[0]['UID'] if device_info_qs.exists() else 'N/A' uid_bucket_qs = uid_bucket_qs.filter(uid__icontains=uid) if addTimeRange: addStartTime, addEndTime = int( addTimeRange[0][:-3]), int(addTimeRange[1][:-3]) uid_bucket_qs = uid_bucket_qs.filter( addTime__gte=addStartTime, addTime__lte=addEndTime) if endTimeRange: endStartTime, endEndTime = int( endTimeRange[0][:-3]), int(endTimeRange[1][:-3]) uid_bucket_qs = uid_bucket_qs.filter( addTime__gte=endStartTime, addTime__lte=endEndTime) uid_list = [] uid_set_dict = {} if ucode and ucode != ['']: uid_set_qs = UidSetModel.objects.filter(ucode__in=ucode).values('uid', 'ucode', 'version').distinct() for uid_set in uid_set_qs: uid_list.append(uid_set['uid']) uid_set_dict[uid_set['uid']] = { 'ucode': uid_set['ucode'], 'version': uid_set['version'] } uid_bucket_qs = uid_bucket_qs.filter(uid__in=uid_list) else: uid_set_qs = UidSetModel.objects.filter().values('uid', 'ucode', 'version').distinct() for uid_set in uid_set_qs: uid_list.append(uid_set['uid']) uid_set_dict[uid_set['uid']] = { 'ucode': uid_set['ucode'], 'version': uid_set['version'] } if not uid_bucket_qs.exists(): return response.json(0, []) order_qs = Order_Model.objects.filter(uid_bucket_id__in=uid_bucket_qs.values('id')) if username or phone or userEmail or payType: if username: order_qs = order_qs.filter(userID__username=username) if phone: order_qs = order_qs.filter(userID__phone__contains=phone) if userEmail: order_qs = order_qs.filter( userID__userEmail__contains=userEmail) if payType: order_qs = order_qs.filter(payType=int(payType)) # 过滤套餐关联的UID_Bucket数据 uid_bucket_qs = uid_bucket_qs.filter( id__in=order_qs.values_list( 'uid_bucket_id', flat=True)) cg_qs = CloudLogModel.objects.filter( operation='cloudstorage/queryvodlist') if logTimeRange: logStartTime, logEndTime = int( logTimeRange[0][:-3]), int(logTimeRange[1][:-3]) cg_qs = cg_qs.filter( time__gte=logStartTime, time__lte=logEndTime) # 过滤套餐关联的UID_Bucket数据 uid_bucket_qs = uid_bucket_qs.filter( uid__in=cg_qs.values('uid')) list_data = [] count = uid_bucket_qs.count() uid_bucket_qs = uid_bucket_qs.order_by('-addTime')[(page - 1) * line:page * line] for uid_bucket in uid_bucket_qs: for order in order_qs.filter(uid_bucket_id=uid_bucket.id) \ .values('orderID', 'uid_bucket_id', 'desc', 'userID__userID', 'UID', 'price', 'payType', 'userID__username', 'userID__phone', 'userID__userEmail', 'userID__data_joined', 'agreement_id'): # 套餐到期时间累加未使用套餐 unused_qs = Unused_Uid_Meal.objects.filter(uid=uid_bucket.uid).values('num', 'expire') if unused_qs.exists(): addMonth = 0 for unused in unused_qs: addMonth += unused['num'] * unused['expire'] endTime = CommonService.calcMonthLater(addMonth, uid_bucket.endTime) endTime = time.strftime("%Y--%m--%d %H:%M:%S", time.localtime(endTime)) else: endTime = time.strftime("%Y--%m--%d %H:%M:%S", time.localtime(uid_bucket.endTime)) uid = uid_bucket.uid.upper() data = { 'id': uid_bucket.id, 'orderId': order['orderID'], 'uid': uid, 'agreementId': order['agreement_id'] if order['agreement_id'] else 'N/A', 'channel': uid_bucket.channel, 'status': uid_bucket.status, 'endTime': endTime, 'addTime': time.strftime("%Y--%m--%d %H:%M:%S", time.localtime(uid_bucket.addTime)), 'use_status': uid_bucket.use_status, 'has_unused': uid_bucket.has_unused, 'desc': order['desc'], 'payType': order['payType'], 'price': order['price'], 'username': order['userID__username'], 'userID': order['userID__userID'], 'phone': order['userID__phone'], 'userEmail': order['userID__userEmail'], 'data_joined': order['userID__data_joined'].strftime("%Y-%m-%d %H:%M:%S"), 'playcount': cg_qs.filter(operation='cloudstorage/queryvodlist', uid=order['UID']).count(), 'serial_number': 'N/A' } device_info_qs = Device_Info.objects.filter(UID=uid).values('serial_number') if device_info_qs.exists(): data['serial_number'] = device_info_qs[0]['serial_number'] if device_info_qs[0][ 'serial_number'] else 'N/A' if uid in uid_set_dict: data['ucode'] = uid_set_dict[uid]['ucode'] data['version'] = uid_set_dict[uid]['version'] list_data.append(data) return response.json( 0, {'list': list_data, 'total': count}) except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def exportCloudUserList(self, request_dict, response): # UID_Bucket表查询数据 uid = request_dict.get('uid', None) status = request_dict.get('status', None) use_status = request_dict.get('use_status', None) has_unused = request_dict.get('has_unused', None) addTimeRange = request_dict.getlist('addTimeRange[]', None) endTimeRange = request_dict.getlist('endTimeRange[]', None) # Order_Model表查询数据 username = request_dict.get('username', None) phone = request_dict.get('phone', None) userEmail = request_dict.get('userEmail', None) payType = request_dict.get('payType', None) # uid_set 表查询 ucode = request_dict.getlist('ucode', None) version = request_dict.getlist('version', None) # 日志表查询 logTimeRange = request_dict.getlist('logTimeRange[]', None) pageNo = request_dict.get('pageNo', None) pageSize = request_dict.get('pageSize', None) if not all([pageNo, pageSize]): return response.json(444) page = int(pageNo) line = int(pageSize) try: uid_bucket_qs = UID_Bucket.objects.all() if uid: uid_bucket_qs = uid_bucket_qs.filter(uid__contains=uid) if status: uid_bucket_qs = uid_bucket_qs.filter(status=status) if use_status: uid_bucket_qs = uid_bucket_qs.filter(use_status=use_status) if has_unused: uid_bucket_qs = uid_bucket_qs.filter(has_unused=has_unused) if addTimeRange: addStartTime, addEndTime = int( addTimeRange[0][:-3]), int(addTimeRange[1][:-3]) uid_bucket_qs = uid_bucket_qs.filter( addTime__gte=addStartTime, addTime__lte=addEndTime) if endTimeRange: endStartTime, endEndTime = int( endTimeRange[0][:-3]), int(endTimeRange[1][:-3]) uid_bucket_qs = uid_bucket_qs.filter( addTime__gte=endStartTime, addTime__lte=endEndTime) if not uid_bucket_qs.exists(): return response.json(0, []) order_qs = Order_Model.objects.filter( uid_bucket_id__in=uid_bucket_qs.values('id')) if username or phone or userEmail or payType: if username: order_qs = order_qs.filter(userID__username=username) if phone: order_qs = order_qs.filter(userID__phone__contains=phone) if userEmail: order_qs = order_qs.filter( userID__userEmail__contains=userEmail) if payType: order_qs = order_qs.filter(payType=int(payType)) # 过滤套餐关联的UID_Bucket数据 uid_bucket_qs = uid_bucket_qs.filter( id__in=order_qs.values_list( 'uid_bucket_id', flat=True)) uidset_qs = UidSetModel.objects.filter( uid__in=uid_bucket_qs.values('uid')) if ucode or version: if ucode: uidset_qs = uidset_qs.filter(ucode=ucode) if version: uidset_qs = uidset_qs.filter(version=version) cg_qs = CloudLogModel.objects.filter( operation='cloudstorage/queryvodlist') if logTimeRange: logStartTime, logEndTime = int( logTimeRange[0][:-3]), int(logTimeRange[1][:-3]) cg_qs = cg_qs.filter( time__gte=logStartTime, time__lte=logEndTime) list_data = [] count = uid_bucket_qs.count() uid_bucket_qs = uid_bucket_qs.order_by('-addTime')[(page - 1) * line:page * line] for uid_bucket in uid_bucket_qs: data = { 'id': uid_bucket.id, 'uid': uid_bucket.uid, 'channel': uid_bucket.channel, 'status': uid_bucket.status, 'endTime': time.strftime( "%Y--%m--%d %H:%M:%S", time.localtime( uid_bucket.endTime)), 'addTime': time.strftime( "%Y--%m--%d %H:%M:%S", time.localtime( uid_bucket.addTime)), 'use_status': uid_bucket.use_status, 'has_unused': uid_bucket.has_unused} for order in order_qs.filter( uid_bucket_id=uid_bucket.id).values( 'uid_bucket_id', 'desc', 'userID__userID', 'UID', 'price', 'payType', 'userID__username', 'userID__phone', 'userID__userEmail', 'userID__data_joined'): data['desc'] = order['desc'] data['payType'] = order['payType'] data['price'] = order['price'] data['username'] = order['userID__username'] data['phone'] = order['userID__phone'] data['userEmail'] = order['userID__userEmail'] data['data_joined'] = order['userID__data_joined'].strftime( "%Y-%m-%d %H:%M:%S") data['playcount'] = cg_qs.filter( operation='cloudstorage/queryvodlist', uid=order['UID']).count() for uidset in uidset_qs.filter( uid=uid_bucket.uid).values( 'ucode', 'version'): data['ucode'] = uidset['ucode'] data['version'] = uidset['version'] list_data.append(data) response = HttpResponse(content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=userinfo.xls' workbook = xlwt.Workbook(encoding='utf-8') sheet1 = workbook.add_sheet('UID') headtitle = [ 'id', '用户账号', '用户手机号', '用户邮箱', '注册时间', '设备UID', '设备通道', '云存状态', '添加时间', '到期时间', '使用状态', '是否有未使用套餐', '套餐描述', '支付方式', '价格', '播放次数', '产品编码', '版本' ] headnum = 0 for title in headtitle: sheet1.write(0, headnum, title) headnum = headnum + 1 fields = [ 'id', 'username', 'phone', 'userEmail', 'data_joined', 'uid', 'channel', 'status', 'addTime', 'endTime', 'use_status', 'has_unused', 'desc', 'payType', 'price', 'playcount', 'ucode', 'version' ] num = 1 for item in list_data: fieldnum = 0 for key in fields: val = item[key] if key == 'payType': if val == 1: val = 'PayPal' if val == 2: val = '支付宝' if val == 3: val = '微信支付' if val == 10: val = '免费体验' if val == 11: val = '激活码' sheet1.write(num, fieldnum, val) fieldnum = fieldnum + 1 num = num + 1 workbook.save(response) return response except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def getCloudDataList(self, request_dict, response): year = request_dict.get('year', None) Jan = int(time.mktime(time.strptime(year + '-1-1 00:00:00', "%Y-%m-%d %H:%M:%S"))) Feb = int(time.mktime(time.strptime(year + '-2-1 00:00:00', "%Y-%m-%d %H:%M:%S"))) Mar = int(time.mktime(time.strptime(year + '-3-1 00:00:00', "%Y-%m-%d %H:%M:%S"))) Apr = int(time.mktime(time.strptime(year + '-4-1 00:00:00', "%Y-%m-%d %H:%M:%S"))) May = int(time.mktime(time.strptime(year + '-5-1 00:00:00', "%Y-%m-%d %H:%M:%S"))) Jun = int(time.mktime(time.strptime(year + '-6-1 00:00:00', "%Y-%m-%d %H:%M:%S"))) Jul = int(time.mktime(time.strptime(year + '-7-1 00:00:00', "%Y-%m-%d %H:%M:%S"))) Aug = int(time.mktime(time.strptime(year + '-8-1 00:00:00', "%Y-%m-%d %H:%M:%S"))) Sep = int(time.mktime(time.strptime(year + '-9-1 00:00:00', "%Y-%m-%d %H:%M:%S"))) Oct = int(time.mktime(time.strptime(year + '-10-1 00:00:00', "%Y-%m-%d %H:%M:%S"))) Nov = int(time.mktime(time.strptime(year + '-11-1 00:00:00', "%Y-%m-%d %H:%M:%S"))) Dec = int(time.mktime(time.strptime(year + '-12-1 00:00:00', "%Y-%m-%d %H:%M:%S"))) Jan_next = int(time.mktime(time.strptime(str(int(year) + 1) + '-1-1 00:00:00', "%Y-%m-%d %H:%M:%S"))) list_data = [] vod_bucket_qs = VodBucketModel.objects.filter() if not vod_bucket_qs.exists(): return response.json(173) try: for vod_bucket in vod_bucket_qs: vod_bucket_id = vod_bucket.id store_meal = Store_Meal.objects.filter(bucket_id=vod_bucket_id, lang__lang='cn').values('lang__title', 'lang__content') if not store_meal.exists(): continue name = store_meal[0]['lang__title'] + '-' + store_meal[0]['lang__content'] order = Order_Model.objects.filter(rank__bucket_id=vod_bucket_id) Jan_count = order.filter(status=1, addTime__range=[Jan, Feb]).count() Feb_count = order.filter(status=1, addTime__range=[Feb, Mar]).count() Mar_count = order.filter(status=1, addTime__range=[Mar, Apr]).count() Apr_count = order.filter(status=1, addTime__range=[Apr, May]).count() May_count = order.filter(status=1, addTime__range=[May, Jun]).count() Jun_count = order.filter(status=1, addTime__range=[Jun, Jul]).count() Jul_count = order.filter(status=1, addTime__range=[Jul, Aug]).count() Aug_count = order.filter(status=1, addTime__range=[Aug, Sep]).count() Sep_count = order.filter(status=1, addTime__range=[Sep, Oct]).count() Oct_count = order.filter(status=1, addTime__range=[Oct, Nov]).count() Nov_count = order.filter(status=1, addTime__range=[Nov, Dec]).count() Dec_count = order.filter(status=1, addTime__range=[Dec, Jan_next]).count() data = [Jan_count, Feb_count, Mar_count, Apr_count, May_count, Jun_count, Jul_count, Aug_count, Sep_count, Oct_count, Nov_count, Dec_count] cloud_data = { 'name': name, 'type': 'line', 'data': data, } list_data.append(cloud_data) return response.json(0, {'list': list_data}) except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) @classmethod def deviceAttritionAlert(cls, request_dict, response): """ 流失预警界面 @param request_dict: @param response: @param userName:用户名 @param uid:设备uid @param grade:预警等级 """ userName = request_dict.get('userName', None) uid = request_dict.get('uid', None) grade = request_dict.get('grade', None) pageNo = request_dict.get('pageNo', None) pageSize = request_dict.get('pageSize', None) if not all({pageNo, pageSize}): return response.json(444) # 获取云存套餐信息表内的设备uid page = int(pageNo) line = int(pageSize) if userName == '' or uid == '' or grade == '': # 判断是否为空 if userName == '': userName = None if uid == '': uid = None if grade == '': grade = None para_list = [userName, uid, grade] not_upload_list = [] para_count = para_list.count(None) try: # 筛选数据 cursor = connection.cursor() sql = "SELECT t1.userID_id, du.username, t1.uid, t1.addTime, t1.use_status, t1.`day`, t1.grade FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, t1.`day`, t1.grade FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, t1.`day`,(CASE WHEN t1.`day` >= '15' AND t1.`day` < '25' THEN '一号预警' WHEN t1.use_status = '1' AND t1.`day` >= '25' THEN '二号预警' WHEN t1.use_status = 2 AND t1.`day` >= '25' THEN '八号预警' ELSE '预警取消' END ) AS grade FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, t1.`day` FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, TIMESTAMPDIFF(DAY,FROM_UNIXTIME(t1.addTime, '%Y-%m-%d' ),DATE_FORMAT( NOW(), '%Y-%m-%d' )) AS 'day' FROM (SELECT di.userID_id, vub.uid, vub.addTime, vub.use_status FROM vod_uid_bucket AS vub, device_info AS di WHERE vub.uid = di.UID AND vub.addTime > '1669824000' GROUP BY di.userID_id) AS t1 WHERE t1.uid NOT IN (SELECT uid FROM uid_cloud_storage_count)) AS t1 WHERE t1.`day`>='15' AND t1.userID_id is NOT NULL) AS t1 ORDER BY t1.userID_id)AS t1 WHERE t1.userID_id NOT IN (SELECT di.userID_id FROM `uid_cloud_storage_count` AS vcsc LEFT JOIN device_info AS di ON vcsc.uid = di.UID WHERE di.userID_id is NOT NULL))AS t1 LEFT JOIN device_user AS du ON t1.userID_id = du.userID order by t1.addTime " cursor.execute(sql) uid_type_tuple = cursor.fetchall() total = len(uid_type_tuple) cursor.close() # 执行完，关闭 connection.close() result_list = [] more_list = [] # 多个参数查询结果 new_list = [] # 单独一个参数查询结果 col_names = [desc[0] for desc in cursor.description] for uid_type in uid_type_tuple: uid_dict = dict(zip(col_names, uid_type)) result_list.append(uid_dict) if para_count != 2 and para_count != 3: # 两个及以上参数进行筛查 if userName and uid and grade == None: for result in result_list: if result['username'] == userName and result['uid'] == uid: more_list.append(result) if userName and grade and uid == None: for result in result_list: if result['username'] == userName and result['grade'] == grade: more_list.append(result) if uid and grade and userName == None: for result in result_list: if result['uid'] == uid and result['grade'] == grade: more_list.append(result) if userName and uid and grade: for result in result_list: if result['username'] == userName and result['uid'] == uid and result['grade'] == grade: more_list.append(result) result_list = more_list if para_count == 2: # 一个参数进行筛查 if userName: for result in result_list: if result['username'] == userName: new_list.append(result) if uid: for result in result_list: if result['uid'] == uid: new_list.append(result) if grade: for result in result_list: if result['grade'] == grade: new_list.append(result) result_list = new_list else: result_list = result_list result_list = result_list[(page - 1) * line:page * line] for item in result_list: # 获取账号下的云存设备数量 userID_id = item['userID_id'] device_info_qs = Device_Info.objects.filter(userID_id=userID_id).annotate(count=Count('UID')).values( 'UID') count = device_info_qs.count() if count == 1 or count == 0: item['device_number'] = count else: device_number = 0 for device_info in device_info_qs: uid = device_info['UID'] uid_set_qs = UidSetModel.objects.filter(uid=uid).values('ucode', 'device_type') ucode = uid_set_qs[0]['ucode'] if uid_set_qs.exists() else '' device_type = uid_set_qs[0]['device_type'] if uid_set_qs.exists() else '' cloud_vod = CommonService.is_cloud_device(ucode, device_type) # 判断设备是否为ipc设备和是否支持云存 if cloud_vod: device_number += 1 item['device_number'] = device_number item['other_device'] = 0 not_upload_list.append(item) return response.json(0, {'result_list': not_upload_list, 'total': total}) except Exception as e: meg = '异常详情,errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e)) return response.json(500, meg) def deactivationPackage(self, request_dict, response): """ 停用套餐 @param request_dict: @param response: """ status = request_dict.get('status', None) if status in [1, 5, 6, 7]: # 付款状态 return response.json(10059) uid = request_dict.get('uid', None) orderID = request_dict.get('orderID', None) orderType = request_dict.get('orderType', None) payType = request_dict.get('payType', None) userName = request_dict.get('userName', None) # 查询订单 if not all([orderID, orderType, uid, userName]): return response.json(444) nowTime = int(time.time()) try: with transaction.atomic(): # 云存套餐 if orderType == '0' and payType != '10': order_qs = Order_Model.objects.filter(orderID=orderID, userID__username=userName, UID=uid).values( 'rank__bucket_id') if not order_qs.exists(): return response.json(14) uid_bucket_qs = UID_Bucket.objects.filter(uid=uid, use_status=1).values('has_unused') has_unused = uid_bucket_qs[0]['has_unused'] if uid_bucket_qs.exists() else '' # 判断套餐是否唯一 if has_unused == 0: uid_bucket_qs.update(endTime=nowTime) CronUpdateDataView.updateUnusedUidBucket(response) return response.json(0) # 当设备套餐不唯一时 # 判断停用套餐是否是为未使用套餐 unused_uid_Meal_qs = Unused_Uid_Meal.objects.filter(uid=uid, bucket_id=order_qs[0]['rank__bucket_id']) unused_uid_Meal_number = unused_uid_Meal_qs.count() if unused_uid_Meal_number == 1: unused_uid_Meal_qs.delete() return response.json(0) return response.json(10059) # 未使用套餐类型重复 # AI套餐 if orderType == '1' and payType != '10': return response.json(10059) # order_qs = Order_Model.objects.filter(orderID=orderID, userID__username=userName, UID=uid).values( # 'ai_rank_id') # order_qs = order_qs.objects.filter(UID=uid) # ai_service_qs = AiService.objects.filter(uid=uid, use_status=1).values('addTime', 'endTime') # ai_service_number = ai_service_qs.count() # if ai_service_number <= 1: # ai_service_qs.update(endTime=nowTime) # return response.json(0) # # 当设备套餐不唯一时 # ai_service_qs = AiService.objects.filter(uid=uid, bucket_id=order_qs[0]['ai_rank_id']) # ai_serverice_number = ai_service_qs.count() # if ai_serverice_number == 1: # ai_service_qs.update(endTime=nowTime) # return response.json(0) # return response.json(10059) # 未使用套餐类型重复 # 联通4G套餐 if orderType == '2' and payType != '10': now_time = int(time.time()) combo_info_qs = UnicomComboOrderInfo.objects.filter(~Q(status=2), order_id=orderID) if not combo_info_qs.exists(): return response.json(10059) iccid = combo_info_qs.first().iccid combo_info_qs.update(status=2, updated_time=now_time) combo_info_qs = UnicomComboOrderInfo.objects.filter(status=1) if combo_info_qs: return response.json(10059) unicom_api = UnicomObjeect() usage_flow = unicom_api.get_flow_usage_total(iccid) today = datetime.datetime.today() year = today.year month = today.month task_view = UnicomComboTaskView() result = task_view.query_unused_combo_and_activate(iccid, year, month, usage_flow) if not result: # 停用设备 unicom_api.change_device_to_disable(iccid) else: unicom_api.change_device_to_activate(iccid) return response.json(0) return response.json(173) except Exception as e: print(e) return response.json(500, 'error_line:{}, error_msg:{}'.format(e.__traceback__.tb_lineno, repr(e))) def cloudDataExport(self, request_dict, response): """ 流失预警导出功能 """ cursor = connection.cursor() sql = "SELECT t1.userID_id, du.username, t1.uid, t1.addTime, t1.use_status, t1.`day`, t1.grade FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, t1.`day`, t1.grade FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, t1.`day`,(CASE WHEN t1.`day` >= '15' AND t1.`day` < '25' THEN '一号预警' WHEN t1.use_status = '1' AND t1.`day` >= '25' THEN '二号预警' WHEN t1.use_status = 2 AND t1.`day` >= '25' THEN '八号预警' ELSE '预警取消' END ) AS grade FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, t1.`day` FROM(SELECT t1.userID_id, t1.uid, t1.addTime, t1.use_status, TIMESTAMPDIFF(DAY,FROM_UNIXTIME(t1.addTime, '%Y-%m-%d' ),DATE_FORMAT( NOW(), '%Y-%m-%d' )) AS 'day' FROM (SELECT di.userID_id, vub.uid, vub.addTime, vub.use_status FROM vod_uid_bucket AS vub, device_info AS di WHERE vub.uid = di.UID AND vub.addTime > '1669824000' GROUP BY di.userID_id) AS t1 WHERE t1.uid NOT IN (SELECT uid FROM uid_cloud_storage_count)) AS t1 WHERE t1.`day`>='15' AND t1.userID_id is NOT NULL) AS t1 ORDER BY t1.userID_id)AS t1 WHERE t1.userID_id NOT IN (SELECT di.userID_id FROM `uid_cloud_storage_count` AS vcsc LEFT JOIN device_info AS di ON vcsc.uid = di.UID WHERE di.userID_id is NOT NULL))AS t1 LEFT JOIN device_user AS du ON t1.userID_id = du.userID order by t1.addTime " cursor.execute(sql) uid_type_tuple = cursor.fetchall() cursor.close() # 执行完，关闭 connection.close() result_list = [] not_upload_list = [] try: with transaction.atomic(): col_names = [desc[0] for desc in cursor.description] for uid_type in uid_type_tuple: uid_dict = dict(zip(col_names, uid_type)) result_list.append(uid_dict) for item in result_list: # 获取账号下的云存设备数量 device_info_qs = Device_Info.objects.filter(userID_id=item['userID_id']).values('UID') count = device_info_qs.count() if count == 1 or count == 0: item['device_number'] = count else: device_number = 0 for device_info in device_info_qs: uid_set_qs = UidSetModel.objects.filter(uid=device_info['UID']).values('ucode', 'device_type') device_type_qs = DeviceTypeModel.objects.filter(type=uid_set_qs[0]['device_type']).values( 'model') model = device_type_qs[0]['model'] if device_type_qs.exists() else '' # 判断设备是否为ipc设备和是否支持云存 if model == 2: if len(uid_set_qs[0]['ucode']) > 4: number = uid_set_qs[0]['ucode'][-4] else: continue if number in ['4', '5']: device_number += 1 else: continue item['device_number'] = device_number item['other_device'] = 0 not_upload_list.append(item) # 创建Excel,导出所有流失预警数据 file_name = "流失预警.xls" if not_upload_list: ws = xlwt.Workbook(encoding="UTF-8") w = ws.add_sheet('流失预警', cell_overwrite_ok=True) w.write(0, 0, u'用户名') w.write(0, 1, u'用户ID') w.write(0, 2, u'设备UID') w.write(0, 3, u'设备自云存开通起多少天没有上传数据') w.write(0, 4, u'设备数量') w.write(0, 5, u'用户有无上传过数据') w.write(0, 6, u'是否有有效期内的付费套餐') w.write(0, 7, u'预警级别') excel_row = 1 for obj in not_upload_list: data_username = obj['username'] data_userID_id = obj['userID_id'] data_uid = obj['uid'] data_day = obj['day'] data_device_number = obj['device_number'] if data_device_number == 1: data_device_number = 'N/A' data_use_status = obj['use_status'] if data_use_status == 1: data_use_status = '有' else: data_use_status = '无' data_other_device = '无' data_grade = obj['grade'] w.write(excel_row, 0, data_username) w.write(excel_row, 1, data_userID_id) w.write(excel_row, 2, data_uid) w.write(excel_row, 3, data_day) w.write(excel_row, 4, data_device_number) w.write(excel_row, 5, data_other_device) w.write(excel_row, 6, data_use_status) w.write(excel_row, 7, data_grade) excel_row += 1 # 生成相应格式给前端 res = HttpResponse(content_type='application/vnd.ms-excel') res['Content-Disposition'] = 'attachment; filename={}'.format(escape_uri_path(file_name)) ws.save(res) return res except Exception as e: meg = '异常详情,errLine:{}, errMsg:{}'.format(e.__traceback__.tb_lineno, repr(e)) return response.json(500, meg)