def communication_requirements(self, request, pk=None, *args, **kwargs): """ 创建沟通需求 :param request: :param pk: :return: """ verify = self.verify_teacher(request, pk) if verify == 0 and request.user.is_superuser is False: return Response({"error": "该问题的培训老师不是您,无权操作!"}, status=status.HTTP_400_BAD_REQUEST) data = self.request.data instructors = data.get("training_instructors") # 培训联系人 training_contact = data.get("training_contact") # 培训联系人电话 training_contactnum = data.get("training_contactnum") # 培训联系人QQ training_contactqq = data.get("training_contactqq") # 培训联系人职位 training_position = data.get("training_position") # 客户培训需求 customer_training_needs = data.get("customer_training_needs") # 沟通方式 communication_way = data.get("communication_way") # 最终培训方式 final_training_method = data.get("final_training_method") # 培训预计开始时间 start_time = data.get("start_time") # 培训预计结束时间 end_time = data.get("end_time") # 未培训原因 untrained_cause = data.get("untrained_cause") training_instructors = self.get_user(instructors) # 改动记录 log_id = OpenStationViewSet.create_base_log(request, pk, "培训管理", 3) mat_log = MatterLog() mat_log.train_contact(pk, data, log_id) mat_log.status_change(pk, log_id, 4) matter = Matter.objects.get(pk=pk) matter.training_instructors = training_instructors matter.training_contact = training_contact matter.training_contactnum = training_contactnum matter.training_contactqq = training_contactqq matter.training_position = training_position matter.customer_training_needs = customer_training_needs matter.communication_way = communication_way matter.final_training_method = final_training_method if start_time != '': matter.start_time = str_to_date(start_time) if end_time != '': matter.end_time = str_to_date(end_time) matter.untrained_cause = untrained_cause matter.matter_status = 4 matter.save() return Response({"info": "沟通培训需求创建成功"}, status=status.HTTP_200_OK)
def derive_site(self, request): start_date = request.GET.get('start_date') end_date = request.GET.get('end_date') deploy = request.GET.get('deploy') industry = request.GET.get('industry') action_dict = dict(OPERATE_ACTION_CHOICES) # 去除续费客户 action_dict.pop(3) name = 'siteData' title_row = ['时间', '部署方式', '行业'] + list(action_dict.values()) title_key = ['date', 'deploy', 'industry'] + list(action_dict.values()) title = dict(zip(title_key, title_row)) content_list = [] params = {} if start_date: params["date__gte"] = str_to_date(start_date) if end_date: params["date__lte"] = str_to_date(end_date) if industry: params['industry'] = industry if deploy: params['deploy_way'] = deploy site_data = OperatingRecord.objects.all().filter(**params).values( 'date', 'action', 'num') if not site_data: content_list.append( dict(zip(title_key, [i - i for i in range(len(title_key))]))) re_dict = {} for item in site_data: key = date_to_str(item.get('date')) if re_dict.get(key): re_dict[key].append(item) else: re_dict[key] = [item] for date_key, each in re_dict.items(): write_list = {'date': date_key, 'industry': '全部', 'deploy': '全部'} if industry: write_list['industry'] = industry if deploy: write_list['deploy'] = deploy_dict.get(int(deploy)) write_list.update( dict( zip(action_dict.values(), [zero - zero for zero in range(len(action_dict))]))) for i in each: action = action_dict.get(i.get('action')) write_list[action] = i.get('num') content_list.append(write_list) excl = Excel_export(filename=name, title=title, content=content_list) response = excl.export_csv() return response
def handle_get_date(self, start_date, end_date): start_date = str_to_date(start_date) end_date = str_to_date(end_date) date_list = [] day_number = (end_date - start_date).days for d in range(day_number): date_list.append(datetime_delta(start_date, days=int(d))) return date_list
def create_put_up(request): today = datetime.date.today() bod = request.body bod = str(bod, encoding="utf-8") bod = json.loads(bod) goods_sn = bod.get('goods_sn', '') on_time = bod.get('putaway_recent_on_time', '1970-01-01').strip() off_time = bod.get('putaway_off_time', '1970-01-01').strip() sell_status = bod.get('sell_status', '1') recommend = bod.get('recommend', '2') put_price = bod.get('put_price', '0') goods_price = bod.get('goods_price', '') user = request.user.last_name on_time = str_to_date(on_time) off_time = str_to_date(off_time) today = datetime.date.today() # 判断请求方式以及上架状态 if (request.method == 'PUT') and (on_time <= today) and (off_time > today): return Response({'error': '上架商品无法修改,请先下架'}, status=status.HTTP_400_BAD_REQUEST) # 单品上架新增信息 s_goods_info = { "putaway_recent_on_time": on_time, "putaway_off_time": off_time, "sell_status": sell_status, "recommend": recommend, "put_price": put_price, "putaway_operator": user, "goods_price": goods_price } # 组合商品上架新增信息 m_goods_info = { "putaway_recent_on_time": on_time, "putaway_off_time": off_time, "sell_status": sell_status, "recommend": recommend, "put_price": put_price, "putaway_operator": user, } if goods_sn[0] == 'A': res = SingleGoods.objects.filter(goods_sn=goods_sn).update( **s_goods_info) if goods_sn[0] == 'B': res = MultipleGoods.objects.filter(m_goods_sn=goods_sn).update( **m_goods_info) OperateLog.create_log(request) return Response({"info": "上架成功"}, status=status.HTTP_200_OK)
def get_queryset(self): kwargs = self.request.GET query_params = {"user_id": self.request.user.id} from_date = kwargs.get("form_date", '').strip() to_date = kwargs.get("to_date", '').strip() operationmodule = kwargs.get("name", None) if from_date and from_date != 'undefined': query_params.update({"operationtime__gte": str_to_date(from_date)}) if to_date and to_date != 'undefined': to_date = datetime_delta(str_to_date(to_date), days=1) query_params.update({"operationtime__lte": to_date}) if operationmodule: query_params.update({"operationmodule": operationmodule}) return OperateLog.objects.all().filter(**query_params).exclude( operationmodule__in=("客户库", "培训管理")).order_by("-operationtime")
def get_station_number(self, request): # 约束日期 start_date = request.GET.get('start_date') end_date = request.GET.get('end_date') if not (end_date and start_date): return Response({'error': '请输入开始时间和结束时间!!!'}, status=status.HTTP_400_BAD_REQUEST) # 部署方式 deploy = request.GET.get('deploy', '') # 运营指标 action = request.GET.get('action', '') industry = request.GET.get('industry', '') params = {} action_list = list(dict(OPERATE_ACTION_CHOICES).values()) action_list.remove('新增产品') action_dict = dict(OPERATE_ACTION_CHOICES) if start_date: params['date__gte'] = start_date if end_date: params['date__lte'] = end_date if deploy: params['deploy_way'] = deploy if industry: params['industry'] = industry if action: params['action'] = action action_list = [dict(OPERATE_ACTION_CHOICES)[int(action)]] total = OperatingRecord.objects.all().filter(**params).exclude(action=3).values('action', 'date') \ .annotate(total=Sum('num')).values('action', 'date', 'total') days = (str_to_date(end_date) - str_to_date(start_date)).days + 1 a = {} for j in action_list: a.update({j: []}) for i in range(days): a[j].append(0) for each in total: index = int((each['date'] - str_to_date(start_date)).days) a[action_dict[each['action']]][index] = each['total'] data = [] for key, value in a.items(): data.append({'name': key, 'data': value}) return Response(data=data, status=status.HTTP_200_OK)
def matter_export(self, request): """ 问题列表导出 :param request: :return: """ # 获取参数 start_time = request.GET.get("start_time") end_time = request.GET.get("end_time") matter_flow = MatterFlowViewsets() query_params = {} if start_time: query_params.update({"start_time__gte": str_to_date(start_time)}) if end_time: end_time = datetime_delta(str_to_date(end_time), days=1) query_params.update({"end_time__lte": end_time}) name = "培训管理-客户培训" title_key = ["id", "company_name", "company_id", "deploy_way", "impl_cslt", "oper_supt", "commercial", "training_instructors", "training_method", "training_contact", "start_time", "end_time", "training_model"] title_value = ["id", "企业名称", "企业ID", "部署方式", "实施顾问", "运营顾问", "商务","培训讲师","培训方式", "培训联系人","培训开始时间", "培训结束时间","培训模块"] title = dict(zip(title_key, title_value)) content_list = [] queryset = Matter.objects.filter(**query_params).values('id', 'matter_status', 'company_matter') # 如果查询不是空集 if not queryset: content_list.append(dict(zip(title_key, [i-i for i in range(len(title_key))]))) excl = Excel_export(filename=name, title=title, content=content_list) response = excl.export_csv() return response for query in queryset: id = query["id"] company_matter = query["company_matter"] dic_comstaion = matter_flow.get_comstaion(company_matter) train_dict = matter_flow.get_trainfo(id) query.update(dic_comstaion) query.update(train_dict) content_list.append(query) excl = Excel_export(filename=name, title=title, content=content_list) response = excl.export_csv() return response
def get_queryset(self): kwargs = self.request.GET query_params = {} from_date = kwargs.get("form_date", None) to_date = kwargs.get("to_date", None) username = kwargs.get("username", None) operationmodule = kwargs.get("name", None) if from_date: query_params.update({"operationtime__gte": str_to_date(from_date)}) if to_date: to_date = datetime_delta(str_to_date(to_date), days=1) query_params.update({"operationtime__lte": to_date}) if username: query_params.update({"user__last_name__icontains": username}) if operationmodule: query_params.update({"operationmodule": operationmodule}) return OperateLog.objects.all().filter(**query_params).exclude( operationmodule__in=("客户库", "培训管理")).order_by("-operationtime")
def get_queryset(self): kwargs = self.request.GET user_id = self.request.user.id from_date = kwargs.get("form_date", None) to_date = kwargs.get("to_date", None) action = kwargs.get("action", None) queryset = OperateLog.objects.all().filter( user=user_id).order_by("-id") if from_date: queryset = queryset.filter( operationtime__gte=str_to_date(from_date)) if to_date: queryset = queryset.filter(operationtime__lte=str_to_date(to_date)) if action: queryset = queryset.filter(action=action) return queryset
def get_queryset(self): kwargs = self.request.GET query_params = {} from_date = kwargs.get("form_date", None) to_date = kwargs.get("to_date", None) username = kwargs.get("username", None) action = kwargs.get("action", None) if from_date: query_params.update({"operationtime__gte": str_to_date(from_date)}) if to_date: to_date = datetime_delta(str_to_date(to_date), days=1) query_params.update({"operationtime__lte": to_date}) if username: query_params.update({"user__last_name__icontains": username}) if action: query_params.update({"action": action}) return OperateLog.objects.all().filter( **query_params).order_by("-operationtime")
def get_station_total(self, request): """ 站点变化趋势计算原理: total = 基站点数量+净增长站点数量 """ # 约束日期 start_date = request.GET.get('start_date') end_date = request.GET.get('end_date') industry = request.GET.get('industry', '') if not (end_date and start_date): return Response({'error': '请输入开始时间和结束时间!!!'}, status=status.HTTP_400_BAD_REQUEST) params = {} if start_date: params['date__gte'] = start_date if end_date: params['date__lte'] = end_date if industry: params['industry'] = industry # 上线了 时间小于统计开始时间 经典站点 计算基站点数量 start_total = OpenStationManage.objects.all().filter(online_status=1, updated_at__lt=start_date, station_info__classify=1) \ .aggregate(total=Sum('is_enable')) # 计算净增长站点数量 total = OperatingRecord.objects.all().filter(**params).filter(action__in=[4, 5]) \ .values('action', 'date').annotate(total=Sum('num')).values('action', 'date', 'total') start_number = int(start_total.get('total', 0)) days = (str_to_date(end_date) - str_to_date(start_date)).days + 1 a = [] for i in range(days): a.append(0) for each in total: index = (each['date'] - str_to_date(start_date)).days if each['action'] == 4: a[index] += int(each['total']) if each['action'] == 5: a[index] -= int(each['total']) data = {'name': 'total', 'data': []} for num in a: data['data'].append(start_number + num) return Response(data=data, status=status.HTTP_200_OK)
def get_station_title(self, request): queryset = OperatingRecord.objects.all().order_by('-id') start_date = str_to_date(request.GET.get('start_date')) end_date = str_to_date(request.GET.get('end_date')) industry = request.GET.get('industry', '') deploy_way = request.GET.get('depoly', '') params = {} if start_date: params['date__gte'] = start_date if end_date: params['date__lte'] = end_date if industry: params['industry'] = industry if deploy_way: params['deploy_way'] = deploy_way queryset = queryset.filter(**params) ret = {} online_set = queryset.exclude(action=3).extra(select={'name': 'action'}).defer('action') \ .values('name').annotate(value=Sum('num')).values('name', 'value') opt_dict = dict(OPERATE_ACTION_CHOICES) online_dict = {} for online in online_set: if opt_dict[online["name"]] not in online_dict: online_dict.update({opt_dict[online["name"]]: online["value"]}) else: k = int(online["name"]) key = opt_dict[k] online_dict[key] += online["value"] # 对其他状态没有数据进行补零 for op in opt_dict: if opt_dict[op] not in online_dict: online_dict[opt_dict[op]] = 0 online_dict.pop("新增产品") ret['name'] = list(online_dict.keys()) ret['data'] = list(online_dict.values()) return Response(data=ret, status=status.HTTP_200_OK)
def add_grid(): date = str_to_date("2018-08-01") inquir = InquiriesData.objects.filter(date__gte=date).all() for i in inquir: group_name = i.server_grp try: grid = Grid.objects.get(group__group_name=group_name).grid_name except: grid = 0 i.grid = grid i.save()
def test_history_channel(request): str_date_start = request.GET.get('start_date') str_date_end = request.GET.get('end_date') start_date = str_to_date(str_date_start) end_date = str_to_date(str_date_end) if Redis_base().get("history_channel_status") == 1: return Response({"status": 1}, status=status.HTTP_200_OK) else: handle_history_channel(start_date, end_date) request.body = json.dumps({ "start_date": str_date_start, "end_date": str_date_end }).encode() request.method = "SCRIPT" OperateLog.create_log(request) # 脚本执行状态存储到redis 进行中 = 1 Redis_base().set("history_channel_status", 1) return Response({}, status=status.HTTP_200_OK)
def get_consult(request): str_date_start = request.GET.get('start_date') str_date_end = request.GET.get('end_date') start_date = str_to_date(str_date_start) end_date = str_to_date(str_date_end) if not (start_date and end_date): return Response({'error': '日期上传错误'}, status=status.HTTP_400_BAD_REQUEST) if Redis_base().get("consult_status") == 1: return Response({"status": 1}, status=status.HTTP_200_OK) else: # 脚本执行状态修改为已完成 = 1 Redis_base().set("consult_status", 1) handle_consult(start_date, end_date) request.body = json.dumps({ "start_date": str_date_start, "end_date": str_date_end }).encode() request.method = "SCRIPT" OperateLog.create_log(request) return Response({}, status=status.HTTP_200_OK)
def date_change(self, pk, log_id, new_start, new_end): """ 开始时间/结束时间变更记录 :param pk: :param log_id: :param new_start: :param new_end: :return: """ try: aa = str_to_date(new_start) except: pass matter = Matter.objects.get(pk=pk) old_start = matter.start_time if old_start is not None: old_start = date_to_str(old_start, format='%Y-%m-%d %H:%M:%S') if old_start != new_start: dict1 = { "word": "start_time", "name": "开始时间", "old_value": old_start, "new_value": new_start, "log_id": log_id } DetailLog.objects.create(**dict1) old_end = matter.end_time if old_end is not None: old_end = date_to_str(old_end, format='%Y-%m-%d %H:%M:%S') # 如果结束时间不为空 if old_end != new_end: dict2 = { "word": "end_time", "name": "结束时间", "old_value": old_end, "new_value": new_end, "log_id": log_id } DetailLog.objects.create(**dict2) return 'ok'
def company_data(request): # 开始日期 from_date = str_to_date(request.GET.get("from_date", "").strip()) # 结束日期 to_date = str_to_date(request.GET.get("to_date", "").strip()) # 企业id company_id = request.GET.get("company_id", "").strip() # 是否有对比上周条件 compare = request.GET.get("compare", "").strip() data = {} params = {} params_compare = {} week = datetime.timedelta(days=7) if from_date and to_date: params["date__gte"] = from_date params["date__lte"] = to_date queryset = RefactoringConsultingAndVisitors.objects.filter(**params).all() site_company = get_site_company() data["company_name"] = "" # 构造对比时间段 compare_from = from_date - week compare_to = to_date - week params_compare["date__gte"] = compare_from params_compare["date__lte"] = compare_to queryset_compare = RefactoringConsultingAndVisitors.objects.filter( **params_compare).all() if company_id != "": # 判断该企业 exists = RefactoringConsultingAndVisitors.objects.filter( company_id=company_id).exists() if exists: queryset = queryset.filter(company_id=company_id) else: return Response(data={"error": "该企业不存在"}, status=status.HTTP_400_BAD_REQUEST) if compare == '1': queryset_compare = queryset_compare.filter(company_id=company_id) # 获取企业名称 for site in site_company: if company_id == site["siteid"]: data["company_name"] = site["name"] break # 咨询量 conz_list = [] # 访客量 visz_list = [] result_set = queryset.values('date').\ annotate(ef_conf=Sum('valid_consulting'), in_conf=Sum("invalid_consulting"), ef_vis=Sum('unique_vistor')). \ values_list('date', 'ef_conf', 'in_conf', 'ef_vis') # 初步获取咨询量和访客量 conz_dict, visz_dict = initial_dict(result_set) # 补0数据 conz_date = patch_zero(from_date, to_date, conz_dict) conz_list.append({'name': "咨询量", 'data': conz_date.values()}) # 补齐没有访客量的日期的数据 visz_date = patch_zero(from_date, to_date, visz_dict) visz_list.append({'name': "访客量", 'data': visz_date.values()}) # 对比条件咨询量&访客量 if compare == '1': compare_set = queryset_compare.values('date'). \ annotate(ef_conf=Sum('valid_consulting'), in_conf=Sum("invalid_consulting"), ef_vis=Sum('valid_visitors')). \ values_list('date', 'ef_conf', 'in_conf', 'ef_vis') # 初步获取咨询量和访客量 conz_compare, visz_compare = initial_dict(compare_set) conz_compare = patch_zero(compare_from, compare_to, conz_compare) conz_list.append({'name': "对比上周咨询量", 'data': conz_compare.values()}) visz_compare = patch_zero(compare_from, compare_to, visz_compare) visz_list.append({'name': "对比上周访客量", 'data': visz_compare.values()}) data.update({"con": conz_list, "vis": visz_list}) OperateLog.create_log(request) return Response(data=data, status=status.HTTP_200_OK)
def jd_company_data(request): # 开始日期 from_date = str_to_date(request.GET.get("from_date", "").strip()) # 结束日期 to_date = str_to_date(request.GET.get("to_date", "").strip()) # 企业id company_id = request.GET.get("company_id", "").strip() # 是否有对比上周条件 compare = request.GET.get("compare", "").strip() data = {} params = {} params_compare = {} week = datetime.timedelta(days=7) if from_date and to_date: params["date__gte"] = from_date params["date__lte"] = to_date # 构造对比时间段 compare_from = from_date - week compare_to = to_date - week params_compare["date__gte"] = compare_from params_compare["date__lte"] = compare_to # 无对比条件咨询量&访客量查询集 query_inquire = InquiriesData.objects.filter(**params).all() query_visitor = VistorData.objects.filter(**params).all() # 有对比条件的咨询量&访客量查询集 comapre_inquire = InquiriesData.objects.filter(**params_compare).all() comapre_visitor = VistorData.objects.filter(**params_compare).all() data["company_name"] = "" # 判断该企业id if company_id != "": query_inquire = query_inquire.filter(company_id=company_id) query_visitor = query_visitor.filter(company_id=company_id) if compare == '1': comapre_inquire = comapre_inquire.filter(company_id=company_id) comapre_visitor = comapre_visitor.filter(company_id=company_id) # 获取企业名称 try: company_name = OpenStationManage.objects.filter(station_info__company_id=company_id).\ first().company_info.company_name data["company_name"] = company_name except: pass # 咨询量 conz_list = [] # 访客量 visz_list = [] inquire_set = query_inquire.values('date').annotate( ef_conf=Sum('inquires_num')).values_list('date', 'ef_conf') visitor_set = query_visitor.values('date').annotate( ef_conf=Sum('visitor_num')).values_list('date', 'ef_conf') # 初步获取咨询量 conz_dict, _ = initial_dict(inquire_set) visz_dict, _ = initial_dict(visitor_set) # 补0数据 conz_date = patch_zero(from_date, to_date, conz_dict) conz_list.append({'name': "咨询量", 'data': conz_date.values()}) # 补齐没有访客量的日期的数据 visz_date = patch_zero(from_date, to_date, visz_dict) visz_list.append({'name': "访客量", 'data': visz_date.values()}) # 对比条件咨询量&访客量 if compare == '1': compare_inquire = comapre_inquire.values('date').annotate( ef_conf=Sum('inquires_num')).values_list('date', 'ef_conf') comapre_visitor = comapre_visitor.values('date').annotate( ef_conf=Sum('visitor_num')).values_list('date', 'ef_conf') # 初步获取咨询量和访客量 conz_compare, _ = initial_dict(compare_inquire) visz_compare, _ = initial_dict(comapre_visitor) conz_compare = patch_zero(compare_from, compare_to, conz_compare) conz_list.append({'name': "对比上周咨询量", 'data': conz_compare.values()}) visz_compare = patch_zero(compare_from, compare_to, visz_compare) visz_list.append({'name': "对比上周访客量", 'data': visz_compare.values()}) data.update({"con": conz_list, "vis": visz_list}) return Response(data=data, status=status.HTTP_200_OK)
def derive_enterprise(self, request): company_id = request.GET.get('company_id') start_date = request.GET.get('start_date') end_date = request.GET.get('end_date') version = request.GET.get('version', 0) name = 'enterpriseData' if company_id: name = CompanyInfo.objects.all().filter(classify=version)\ .filter(open_station__station_info__company_id=company_id)\ .values_list('company_name', flat=True) if not name: name = 'unknownSite' title = {'date': '时间', 'uv': '访客量', 'pv': '咨询量'} content_list = [] params = {} if start_date: params["date__gte"] = str_to_date(start_date) if end_date: params["date__lte"] = str_to_date(end_date) if company_id: params['company_id'] = company_id if int(version) == 1: re_data = RefactoringConsultingAndVisitors.objects.all().filter(**params).values('date')\ .annotate(con=Sum('valid_consulting'), in_con=Sum('invalid_consulting'), uv=Sum('unique_vistor'))\ .values('date', 'con', 'in_con', 'uv') if not re_data: title_key = title.keys() content_list.append( dict(zip(title_key, [i - i for i in range(len(title_key))]))) for i in re_data: inner_data = [ i.get('date'), i.get('uv'), int(i.get('con')) + int(i.get('in_con')) ] inner_dict = dict(zip(title.keys(), inner_data)) content_list.append(inner_dict) else: query_con = InquiriesData.objects.all().filter(**params) query_vis = VistorData.objects.all().filter(**params) consult = query_con.values('date').annotate(consult=Sum('inquires_num'))\ .values('date', 'consult') visitor = query_vis.values('date').annotate(visitor=Sum('visitor_num'))\ .values('date', 'visitor') if not (consult and visitor): title_key = title.keys() content_list.append( dict(zip(title_key, [i - i for i in range(len(title_key))]))) long = consult short = visitor if len(visitor) >= len(consult): long = visitor short = consult for item in long: for each in short: if item['date'] == each['date']: v_value = item.get('visitor') if item.get( 'visitor') else each.get('visitor') c_value = item.get('consult') if item.get( 'consult') else each.get('consult') content_list.append({ 'date': item['date'], 'uv': v_value, 'pv': c_value }) # title = {"siteid": "企业id", "name": "企业名称"} # content = [{"siteid": "kf_123", "name": "哈喽"}, {"siteid": "kf_1234", "name": "哈喽1"}] excl = Excel_export(filename=name, title=title, content=content_list) response = excl.export_csv() return response
def del_inquires(): inq = VistorData.objects.filter( date__in=(str_to_date('2018-09-11'), str_to_date('2018-09-12'))).delete()
def derive_grid(self, request): grid = request.GET.get('grid') deploy = request.GET.get('deploy') start_date = request.GET.get('start_date') end_date = request.GET.get('end_date') version = request.GET.get('version', 0) params = {} name = 'gridData' if grid: params['grid'] = grid name = grid if start_date: params["date__gte"] = str_to_date(start_date) if end_date: params["date__lte"] = str_to_date(end_date) title = { 'date': '时间', 'deploy': '部署方式', 'grid': '节点', 'uv': '访客量', 'pv': '咨询量' } content_list = [] if int(version) == 1: if deploy: params['deploy'] = deploy re_data = RefactoringConsultingAndVisitors.objects.all().filter(**params)\ .values('date', 'grid')\ .annotate(con=Sum('valid_consulting'), in_con=Sum('invalid_consulting'), uv=Sum('unique_vistor'))\ .values('date', 'deploy', 'grid', 'con', 'in_con', 'uv') if not re_data: title_key = title.keys() content_list.append( dict(zip(title_key, [i - i for i in range(len(title_key))]))) for i in re_data: inner_data = [ i.get('date'), deploy_dict.get(int(i.get('deploy'))), i.get('grid'), i.get('uv'), int(i.get('con')) + int(i.get('in_con')) ] inner_dict = dict(zip(title.keys(), inner_data)) content_list.append(inner_dict) else: if deploy: params['deploy_way'] = deploy query_con = InquiriesData.objects.all().filter(**params) query_vis = VistorData.objects.all().filter(**params) consult = query_con.values('date', 'grid').annotate(consult=Sum('inquires_num')) \ .values('date', 'consult', 'grid', 'deploy_way') visitor = query_vis.values('date', 'grid').annotate(visitor=Sum('visitor_num')) \ .values('date', 'visitor', 'grid', 'deploy_way') if not (consult and visitor): title_key = title.keys() content_list.append( dict(zip(title_key, [i - i for i in range(len(title_key))]))) long = consult short = visitor if len(visitor) >= len(consult): long = visitor short = consult for item in long: for each in short: if item['date'] == each['date'] and item['grid'] == each['grid'] \ and item['deploy_way'] == each['deploy_way']: v_value = item.get('visitor') if item.get( 'visitor') else each.get('visitor') c_value = item.get('consult') if item.get( 'consult') else each.get('consult') content_list.append({ 'date': item['date'], 'deploy': deploy_dict.get(int(item['deploy_way'])), 'grid': item['grid'], 'uv': v_value, 'pv': c_value }) excl = Excel_export(filename=name, title=title, content=content_list) response = excl.export_csv() return response
def derive_channel(self, request): channel = request.GET.get('channel') start_date = request.GET.get('start_date') end_date = request.GET.get('end_date') deploy = request.GET.get('deploy') industry = request.GET.get('industry') version = request.GET.get('version', 0) deploy_dict = dict(DEPLOY_WAYS) re_channel = dict(REFACTORING_CHANNEL_CHOICES) channel_dict = dict(CHANNEL_CHOICES) name = 'enterpriseData' if channel: name = channel if not name: name = 'unknownSite' title = {} content_list = [] params = {} if start_date: params["date__gte"] = str_to_date(start_date) if end_date: params["date__lte"] = str_to_date(end_date) if industry: params['industry'] = industry if channel: params['channel'] = channel # 只要选择了行业或者部署方式就直接用 没有选择就显示全部 if int(version) == 1: title_row = ['时间', '部署方式', '行业'] + list(re_channel.values()) title_key = ['date', 'deploy', 'industry'] + list( re_channel.values()) title = dict(zip(title_key, title_row)) if deploy: params['deploy'] = deploy re_data = RefactoringConsultingAndVisitors.objects.all().filter(**params).values('date', 'channel')\ .annotate(con=Sum('valid_consulting'), in_con=Sum('invalid_consulting'))\ .values('date', 'channel', 'con', 'in_con') if not re_data: content_list.append( dict(zip(title_key, [i - i for i in range(len(title_key))]))) re_dict = {} for item in re_data: key = date_to_str(item['date']) if re_dict.get(key): re_dict[key].append(item) else: re_dict[key] = [item] for date_key, each in re_dict.items(): write_list = { 'date': date_key, 'industry': '全部', 'deploy': '全部' } if industry: write_list['industry'] = industry if deploy: write_list['deploy'] = deploy_dict.get(int(deploy)) write_list.update( dict( zip(re_channel.values(), [zero - zero for zero in range(len(re_channel))]))) for i in each: channel = re_channel.get(i.get('channel')) value = int(i.get('con')) + int(i.get('in_con')) write_list[channel] = value content_list.append(write_list) else: title_row = ['时间', '部署方式', '行业'] + list(channel_dict.values()) title_key = ['date', 'deploy', 'industry'] + list( channel_dict.values()) title = dict(zip(title_key, title_row)) if deploy: params['deploy_way'] = deploy con_data = InquiriesData.objects.all().filter(**params).values('date', 'channel')\ .annotate(num=Sum('inquires_num'))\ .values('date', 'channel', 'num') if not con_data: content_list.append( dict(zip(title_key, [i - i for i in range(len(title_key))]))) re_dict = {} for item in con_data: key = date_to_str(item['date']) if re_dict.get(key): re_dict[key].append(item) else: re_dict[key] = [item] for date_key, each in re_dict.items(): write_list = { 'date': date_key, 'industry': '全部', 'deploy': '全部' } if industry: write_list['industry'] = industry if deploy: write_list['deploy'] = deploy_dict.get(int(deploy)) write_list.update( dict( zip(channel_dict.values(), [zero - zero for zero in range(len(channel_dict))]))) for i in each: channel = channel_dict.get(i.get('channel')) value = i.get('num') write_list[channel] = value content_list.append(write_list) excl = Excel_export(filename=name, title=title, content=content_list) response = excl.export_csv() return response
def handle(self, *args, **options): from_date, to_date = str_to_date(options['from_date']), str_to_date( options['to_date']) manage = InquiresFetcherManager() print('fetch...') manage.fetch_history(from_date, to_date)
def get_grid_data(request): """ 暂时没用 :param request: :return: """ # 节点 grid = request.GET.getlist('grid', '') if isinstance(grid, list) and len(grid) > 6: return Response({'error': '选择节点数超过6个!!!'}, status=status.HTTP_400_BAD_REQUEST) # 约束日期 start_date = str_to_date(request.GET.get('start_date')) end_date = str_to_date(request.GET.get('end_date')) if not (end_date and start_date): return Response({'error': '请输入开始时间和结束时间!!!'}, status=status.HTTP_400_BAD_REQUEST) # 部署方式 deploy = request.GET.get('deploy', '') # 默认是咨询量(0) 访客量是1 index = request.GET.get('index', 0) params = {} params["date__gte"] = start_date params["date__lte"] = end_date # 基础查询集 base_query = RefactoringConsultingAndVisitors.objects.all().filter( **params) # 如果存在部署方式 if deploy: base_query = base_query.filter(deploy=deploy) consult = base_query.aggregate(invalid=Sum('valid_consulting'), valid=Sum('invalid_consulting')) invalid = consult.get("invalid") valid = consult.get("valid") if invalid == None: invalid = 0 if valid == None: valid = 0 consult_num = valid + invalid visitors = base_query.aggregate(total=Sum('unique_vistor')) visitor = visitors.get("total") if visitor == None: visitor = 0 visitor_num = visitor # 有节点参数 if grid: inq_list = [] vis_list = [] for g in grid: inquire_set = base_query.filter(grid=g).values('date').annotate( ef_conf=Sum('valid_consulting'), in_conf=Sum("invalid_consulting"), ef_vis=Sum('unique_vistor')). \ values_list('date', 'ef_conf', 'in_conf', 'ef_vis') # 初始化咨询量&访客量 inq_dict, vis_dict = initial_dict(inquire_set) # 补齐没有咨询量&访客的日期的数据 que_date = patch_zero(start_date, end_date, inq_dict) vis_date = patch_zero(start_date, end_date, vis_dict) inq_list.append({"name": g, 'data': que_date.values()}) vis_list.append({"name": g, 'data': vis_date.values()}) else: inq_list = [] vis_list = [] inquire_set = base_query.values('date').annotate( ef_conf=Sum('valid_consulting'), in_conf=Sum("invalid_consulting"), ef_vis=Sum('unique_vistor')). \ values_list('date', 'ef_conf', 'in_conf', 'ef_vis') # 初始化咨询量&访客量 inq_dict, vis_dict = initial_dict(inquire_set) # 补齐没有咨询量&访客的日期的数据 que_date = patch_zero(start_date, end_date, inq_dict) vis_date = patch_zero(start_date, end_date, vis_dict) inq_list.append({"name": "all", 'data': que_date.values()}) vis_list.append({"name": "all", 'data': vis_date.values()}) # 显示访客量 if index == '1': broken_line = vis_list # 显示咨询量 else: broken_line = inq_list grid_set = RefactoringConsultingAndVisitors.objects.filter( **params).values_list('grid', flat=True) grid_list = list(set(grid_set)) deploy_list = [] deploy_dict = dict(DEPLOY_WAYS) for key, value in deploy_dict.items(): deploy_list.append({'name': value, 'value': key}) data_dict = { "consult": consult_num, "visitors": visitor_num, "broken_line": broken_line, "deploy_list": deploy_list, "grid_list": grid_list, } return Response(data=data_dict, status=status.HTTP_200_OK)
def jd_home_rest(request): params = dict() from_date = str_to_date(request.GET.get("from_date", "").strip()) to_date = str_to_date(request.GET.get("to_date", "").strip()) if from_date and to_date: params["date__gte"] = from_date params["date__lte"] = to_date # 该时间段内的总的咨询量 all_con = InquiriesData.objects.all().filter( **params).values('inquires_num') all_set = all_con.aggregate(valid=Sum('inquires_num')) total_con = all_set.get("valid", 0) if total_con == None: total_con = 0 total_con = int(total_con) # 咨询量和访客量数据 conz_list = [] visz_list = [] # 咨询量 inquire_query = InquiriesData.objects.filter(**params).values('date').\ annotate(value=Sum('inquires_num')).values_list('date', 'value') conz_dict, _ = initial_dict(inquire_query) # 补0数据 conz_date = patch_zero(from_date, to_date, conz_dict) conz_list.append({'name': "咨询量", 'data': conz_date.values()}) # 访客量 visitor_query = VistorData.objects.filter(**params).values('date').\ annotate(value=Sum('visitor_num')).values_list('date','value') vis_dict, _ = initial_dict(visitor_query) # 补齐没有访客量的日期的数据 visz_date = patch_zero(from_date, to_date, vis_dict) visz_list.append({'name': "访客量", 'data': visz_date.values()}) # 咨询量渠道占比 channal_set = InquiriesData.objects.all(). \ filter(**params).values('channel'). \ annotate(ef_conf=Sum('inquires_num')). \ values('channel', 'ef_conf') channal_list = bing_picture('channel', jdchannal_dict, channal_set) # 部署咨询量饼状图 deploy_list = [] deploy_set = InquiriesData.objects.all(). \ filter(**params).values('deploy_way'). \ annotate(ef_conf=Sum('inquires_num')). \ values('deploy_way', 'ef_conf') deploy_list = bing_picture('deploy_way', deploy_dict, deploy_set) # 部署咨询量折线图 # 目标数据结构 [{"data":[23,3,4], "name": "标准版"}, {"data":[2,4,5,6], "name": 缺省}] dez_list = [] for de in deploy_dict: dez_dict = {} # 遍历所有部署方式 dep_zhe = de dez_set = InquiriesData.objects.filter(**params).filter(deploy_way=dep_zhe).\ values('date').annotate(ef_conf=Sum('inquires_num')).\ values_list('date', 'ef_conf') dez_dict, _ = initial_dict(dez_set) # 补齐没有咨询量的日期的数据 que_date = patch_zero(from_date, to_date, dez_dict) dez_list.append({'name': deploy_dict[de], 'data': que_date.values()}) grid_list = [] # 以节点为维度,取有效咨询量和无效咨询量之和排名前十的数据 grid_set = InquiriesData.objects.all(). \ filter(**params).values('grid'). \ annotate(ef_conf=Sum('inquires_num')). \ order_by('ef_conf').reverse()[:10]. \ values('grid', 'ef_conf') for grid in grid_set: ef_conf = grid.get("ef_conf", 0) grid["total"] = int(ef_conf) del grid["ef_conf"] if total_con == 0: grid["percent"] = 0 elif int(grid["total"]) != 0: grid["percent"] = round((int(grid["total"]) / total_con) * 100, 3) else: grid["percent"] = 0 grid_list.append(grid) # 行业咨询量top10 industry_list = [] industry_dict = {} # 以行业为维度,效咨询量排名前十的数据 industry_set = InquiriesData.objects.all(). \ filter(**params).values('industry'). \ annotate(ef_conf=Sum('inquires_num')). \ order_by('ef_conf').reverse()[:10] industry_dict["data"] = [] industry_dict["industry"] = [] for industry in industry_set: ef_conf = industry.get("ef_conf", 0) con = int(ef_conf) per = round((con / total_con) * 100, 2) percent = str(per) + '%' industry_dict["data"].append({"value": con, "per": percent}) industry_dict["industry"].append(industry["industry"]) industry_dict["name"] = "咨询量" industry_list.append(industry_dict) data = { "con": conz_list, "vis": visz_list, "channal": channal_list, "grid": grid_list, "deploy": deploy_list, "dez": dez_list, "industry": industry_list } return Response(data=data, status=status.HTTP_200_OK)
def home_rest(request): params = dict() from_date = str_to_date(request.GET.get("from_date", "").strip()) to_date = str_to_date(request.GET.get("to_date", "").strip()) if from_date and to_date: params["date__gte"] = from_date params["date__lte"] = to_date # 总得咨询量 all_con = RefactoringConsultingAndVisitors.objects.filter(**params).values( 'valid_consulting', 'invalid_consulting') all_set = all_con.aggregate(valid=Sum('valid_consulting'), unvalid=Sum('invalid_consulting')) valid = all_set.get("valid", 0) unvalid = all_set.get("unvalid", 0) if valid == None: valid = 0 if unvalid == None: unvalid = 0 total_con = int(valid) + int(unvalid) # 咨询量和访客量数据 conz_list = [] visz_list = [] consulting = RefactoringConsultingAndVisitors.objects.all(). \ filter(**params).values('date').annotate( ef_conf=Sum('valid_consulting'), in_conf=Sum("invalid_consulting"), ef_vis=Sum('unique_vistor')). \ values_list('date', 'ef_conf', 'in_conf', 'ef_vis') # 初步获取咨询量和访客量 conz_dict, visz_dict = initial_dict(consulting) # 补0数据 conz_date = patch_zero(from_date, to_date, conz_dict) conz_list.append({'name': "咨询量", 'data': conz_date.values()}) # 补齐没有访客量的日期的数据 visz_date = patch_zero(from_date, to_date, visz_dict) visz_list.append({'name': "访客量", 'data': visz_date.values()}) # 咨询量渠道占比 channal_list = [] channal_set = RefactoringConsultingAndVisitors.objects.all(). \ filter(**params).values('channel'). \ annotate(ef_conf=Sum('valid_consulting'), in_conf=Sum("invalid_consulting")). \ values('channel', 'ef_conf', 'in_conf') channal_list = bing_picture('channel', channal_dict, channal_set) # 部署咨询量饼状图 deploy_list = [] deploy_set = RefactoringConsultingAndVisitors.objects.all(). \ filter(**params).values('deploy'). \ annotate(ef_conf=Sum('valid_consulting'), in_conf=Sum("invalid_consulting")). \ values('deploy', 'ef_conf', 'in_conf') deploy_list = bing_picture('deploy', deploy_dict, deploy_set) # 部署咨询量折线图 # 目标数据结构 [{"data":[23,3,4], "name": "标准版"}, {"data":[2,4,5,6], "name": 缺省}] dez_list = [] for de in deploy_dict: dez_dict = {} # 遍历所有部署方式 dep_zhe = de dez_set = RefactoringConsultingAndVisitors.objects.filter(**params).filter(deploy=dep_zhe).\ values('date').annotate(ef_conf=Sum('valid_consulting'), in_conf=Sum("invalid_consulting")).\ values_list('date', 'ef_conf', 'in_conf') for it in dez_set: sum_it = int(it[1]) + int(it[2]) dez_dict.update({it[0]: sum_it}) # 补齐没有咨询量的日期的数据 que_date = patch_zero(from_date, to_date, dez_dict) dez_list.append({'name': deploy_dict[de], 'data': que_date.values()}) gride_list = [] # 以节点为维度,取有效咨询量和无效咨询量之和排名前十的数据 grid_set = RefactoringConsultingAndVisitors.objects.all(). \ filter(**params).values('grid'). \ annotate(ef_conf=Sum('valid_consulting'), in_conf=Sum("invalid_consulting")). \ order_by(F('ef_conf') + F('in_conf')).reverse()[:10]. \ values('grid', 'ef_conf', 'in_conf') for grid in grid_set: ef_conf = grid.get("ef_conf", 0) in_conf = grid.get("in_conf", 0) grid["total"] = int(ef_conf) + int(in_conf) del grid["ef_conf"] del grid["in_conf"] if int(grid["total"]) != 0: grid["percent"] = round((int(grid["total"]) / total_con) * 100, 3) else: grid["percent"] = 0 gride_list.append(grid) # 行业咨询量top10 industry_list = [] industry_dict = {} # 以行业为维度,取有效咨询量和无效咨询量之和排名前十的数据 industry_set = RefactoringConsultingAndVisitors.objects.all(). \ filter(**params).values('industry'). \ annotate(ef_conf=Sum('valid_consulting'), in_conf=Sum("invalid_consulting")). \ order_by(F('ef_conf') + F('in_conf')).reverse()[:10] industry_dict["data"] = [] industry_dict["industry"] = [] for industry in industry_set: ef_conf = industry.get("ef_conf", 0) in_conf = industry.get("in_conf", 0) con = int(ef_conf) + int(in_conf) per = round((con / total_con) * 100, 2) percent = str(per) + '%' industry_dict["data"].append({"value": con, "per": percent}) industry_dict["industry"].append(industry["industry"]) industry_dict["name"] = "咨询量" industry_list.append(industry_dict) data = { "con": conz_list, "vis": visz_list, "channal": channal_list, "grid": gride_list, "deploy": deploy_list, "dez": dez_list, "industry": industry_list } return Response(data=data, status=status.HTTP_200_OK)
def derive_industry(self, request): start_date = request.GET.get('start_date') end_date = request.GET.get('end_date') deploy = request.GET.get('deploy') industry = request.GET.get('industry') version = request.GET.get('version', 0) name = 'siteData' title_row = ['时间', '部署方式', '行业', '咨询量', '访客量'] title_key = ['date', 'deploy', 'industry', 'pv', 'uv'] title = dict(zip(title_key, title_row)) content_list = [] params = {} if start_date: params["date__gte"] = str_to_date(start_date) if end_date: params["date__lte"] = str_to_date(end_date) if industry: params['industry'] = industry if int(version) == 1: if deploy: params['deploy'] = deploy re_data = RefactoringConsultingAndVisitors.objects.all().filter(**params)\ .values('date')\ .annotate(pv=Sum('valid_consulting'), in_pv=Sum('invalid_consulting'), uv=Sum('unique_vistor'))\ .values('date', 'pv', 'uv', 'in_pv') if not re_data: content_list.append( dict(zip(title_key, [i - i for i in range(len(title_key))]))) re_dict = {} for item in re_data: key = date_to_str(item.get('date')) if re_dict.get(key): re_dict[key].append(item) else: re_dict[key] = [item] for date_key, each in re_dict.items(): write_list = { 'date': date_key, 'industry': '全部', 'deploy': '全部', 'pv': 0, 'uv': 0 } if industry: write_list['industry'] = industry if deploy: write_list['deploy'] = deploy_dict.get(int(deploy)) for i in each: pv = int(i.get('pv')) + int(i.get('in_pv')) write_list['uv'] = int(i.get('uv')) write_list['pv'] = pv content_list.append(write_list) else: if deploy: params['deploy_way'] = deploy pv_data = InquiriesData.objects.all().filter(**params).values('date')\ .annotate(pv=Sum('inquires_num')).values('date', 'pv') uv_data = VistorData.objects.all().filter(**params).values('date')\ .annotate(uv=Sum('visitor_num')).values('date', 'uv') if not (uv_data and pv_data): content_list.append( dict(zip(title_key, [i - i for i in range(len(title_key))]))) long = pv_data short = uv_data if len(uv_data) >= len(pv_data): long = uv_data short = pv_data for item in long: date_value = date_to_str(item['date']) for each in short: if item['date'] == each['date']: v_value = item.get('uv') if item.get( 'uv') else each.get('uv') c_value = item.get('pv') if item.get( 'pv') else each.get('pv') write_list = { 'date': date_value, 'industry': '全部', 'deploy': '全部', 'pv': 0, 'uv': 0 } if industry: write_list['industry'] = industry if deploy: write_list['deploy'] = deploy_dict.get(int(deploy)) write_list['uv'] = v_value write_list['pv'] = c_value content_list.append(write_list) excl = Excel_export(filename=name, title=title, content=content_list) response = excl.export_csv() return response
def jd_grid_data(request): # 节点 grid = request.GET.getlist('grid', '') if isinstance(grid, list) and len(grid) > 6: return Response({'error': '选择节点数超过6个!!!'}, status=status.HTTP_400_BAD_REQUEST) # 约束日期 start_date = str_to_date(request.GET.get('start_date')) end_date = str_to_date(request.GET.get('end_date')) if not (end_date and start_date): return Response({'error': '请输入开始时间和结束时间!!!'}, status=status.HTTP_400_BAD_REQUEST) # 部署方式 deploy = request.GET.get('deploy', '') # 默认是咨询量(0) 访客量是1 index = request.GET.get('index', 0) params = {} params["date__gte"] = start_date params["date__lte"] = end_date # 咨询量基础查询集 base_inquire = InquiriesData.objects.filter(**params) # 访客量基础查询集 base_visitor = VistorData.objects.all().filter(**params) # 如果存在部署方式 if deploy: base_inquire = base_inquire.filter(deploy_way=deploy) base_visitor = base_visitor.filter(deploy_way=deploy) # 有节点参数 if grid: inq_list = [] vis_list = [] consult = base_inquire.filter(grid__in=grid).aggregate( total=Sum('inquires_num')) visitors = base_visitor.filter(grid__in=grid).aggregate( total=Sum('visitor_num')) for g in grid: inquire_set = base_inquire.filter(grid=g).values('date').\ annotate(ef_conf=Sum('inquires_num')).\ values_list('date', 'ef_conf') visitor_st = base_visitor.filter(grid=g).values('date').\ annotate(ef_conf=Sum('visitor_num')).\ values_list('date', 'ef_conf') # 初始化咨询量&访客量 inq_dict, _ = initial_dict(inquire_set) vis_dict, _ = initial_dict(visitor_st) # 补齐没有咨询量&访客的日期的数据 que_date = patch_zero(start_date, end_date, inq_dict) vis_date = patch_zero(start_date, end_date, vis_dict) inq_list.append({"name": g, 'data': que_date.values()}) vis_list.append({"name": g, 'data': vis_date.values()}) else: consult = base_inquire.aggregate(total=Sum('inquires_num')) visitors = base_visitor.aggregate(total=Sum('visitor_num')) inq_list = [] vis_list = [] inquire_set = base_inquire.values('date').\ annotate(ef_conf=Sum('inquires_num')).\ values_list('date', 'ef_conf') visitor_st = base_visitor.values('date'). \ annotate(ef_conf=Sum('visitor_num')). \ values_list('date', 'ef_conf') # 初始化咨询量&访客量 inq_dict, _ = initial_dict(inquire_set) vis_dict, _ = initial_dict(visitor_st) # 补齐没有咨询量&访客的日期的数据 que_date = patch_zero(start_date, end_date, inq_dict) vis_date = patch_zero(start_date, end_date, vis_dict) inq_list.append({"name": "all", 'data': que_date.values()}) vis_list.append({"name": "all", 'data': vis_date.values()}) # 显示访客量 if index == '1': grid_set = VistorData.objects.filter(**params).values_list('grid', flat=True) broken_line = vis_list # 显示咨询量 else: grid_set = InquiriesData.objects.filter(**params).values_list( 'grid', flat=True) broken_line = inq_list grid_list = list(set(grid_set)) deploy_list = [] deploy_dict = dict(DEPLOY_WAYS) for key, value in deploy_dict.items(): deploy_list.append({'name': value, 'value': key}) data_dict = { "consult": consult["total"], "visitors": visitors["total"], "broken_line": broken_line, "deploy_list": deploy_list, "grid_list": grid_list, } return Response(data=data_dict, status=status.HTTP_200_OK)
def __init__(self, start_time): self.start_time = str(start_time) + ' 00:00:00' self.end_time = date_to_str( datetime_delta(str_to_date(start_time), days=1)) + ' 00:00:00' self.grid_data = self.get_grid()