def getDetailReport(self, excelTool): headers = [["需求单客户总数","countUserId"], ["需求单提交总数","wishListCount"], ["订单转化数","orderCount"], ["订单转化率","orderConversionRate"], ["订单总金额","orderAmountSum"], ["历史订单转化数","historyOrderCount"], ["历史订单总金额","historyOrderAmountSum"], ["待报价需求单","dbjWishListCount"], ["已报价需求单","ybjWishListCount"], ["已取消需求单","yxqWishListCount"], ["确认报价需求单","qrbjWishListCount"], ["待付款订单","dfkOrderCount"], ["已付款订单","yfkOrderCount"], ["部分发货订单","bffhOrderCount"], ["已发货订单","yfhOrderCount"], ["已签收订单","yqsOrderCount"], ["已结算订单","yjsOrderCount"]] userIdList = self.userDao.getExcludeUserIdList() dateFrom, dateTo = Config.getDateSpan() wishListBOList = self.wishListDao.getWishListByDateSpanExcludeUserIdList(dateFrom, dateTo, userIdList) orderList = self.offerListDao.getOrderByDateSpanExcludeUserIdList(dateFrom, dateTo, userIdList) historyDateFrom, historyDateTo = Config.getHistoryDateSpan() historyOrderList = self.offerListDao. \ getOrderByDateSpanExcludeUserIdList(historyDateFrom, historyDateTo, userIdList) userSet = set() wishListCount = len(wishListBOList) orderCount = len(orderList) orderConversionRate = (float(orderCount) / float(wishListCount)) if wishListCount else None orderAmountSum = float(0) historyOrderCount = len(historyOrderList) historyOrderAmountSum = float(0) dbjWishListCount = 0 ybjWishListCount = 0 yxqWishListCount = 0 qrbjWishListCount = 0 dfkOrderCount = 0 yfkOrderCount = 0 bffhOrderCount = 0 yfhOrderCount = 0 yqsOrderCount = 0 yjsOrderCount = 0 for wishListBO in wishListBOList: userSet.add(wishListBO.userId) if wishListBO.status == "XDBJ": dbjWishListCount += 1 if wishListBO.status == "XYBJ": ybjWishListCount += 1 if wishListBO.status == "XYQX" or wishListBO.status == "BYQX": yxqWishListCount += 1 if wishListBO.status == "XQRBJ": qrbjWishListCount += 1 for order in orderList: if order.status == "BDFK": dfkOrderCount += 1 if order.status == "BYFK": yfkOrderCount += 1 if order.status == "BBFFH": bffhOrderCount += 1 if order.status == "BYFH": yfhOrderCount += 1 if order.status == "BYQS": yqsOrderCount += 1 if order.status == "BYJS": yjsOrderCount +=1 orderAmountSum+=int(0 if not order.paidOfferAmount else order.paidOfferAmount) for historyOrder in historyOrderList: historyOrderAmountSum += int(0 if not historyOrder.paidOfferAmount else historyOrder.paidOfferAmount) detailReportDictList = [] detailReportDict = {} detailReportDict["countUserId"] = len(userSet) detailReportDict["wishListCount"] = wishListCount detailReportDict["orderCount"] = orderCount detailReportDict["orderConversionRate"] = ("{:.2%}".format(orderConversionRate)) if orderConversionRate else "" detailReportDict["orderAmountSum"] = "{:.2f}".format(orderAmountSum) detailReportDict["historyOrderCount"] = historyOrderCount detailReportDict["historyOrderAmountSum"] = "{:.2f}".format(historyOrderAmountSum) detailReportDict["dbjWishListCount"] = dbjWishListCount detailReportDict["ybjWishListCount"] = ybjWishListCount detailReportDict["yxqWishListCount"] = yxqWishListCount detailReportDict["qrbjWishListCount"] = qrbjWishListCount detailReportDict["dfkOrderCount"] = dfkOrderCount detailReportDict["yfkOrderCount"] = yfkOrderCount detailReportDict["bffhOrderCount"] = bffhOrderCount detailReportDict["yfhOrderCount"] = yfhOrderCount detailReportDict["yqsOrderCount"] = yqsOrderCount detailReportDict["yjsOrderCount"] = yjsOrderCount detailReportDictList.append(detailReportDict) excelTool.export(headers,detailReportDictList,"汇总明细报表")
def getStatReport(self, excelTool): headers = [["需求单客户总数","countUserId"], ["需求单提交总数","wishListCount"], ["订单总金额","orderAmountSum"], ["已报价需求单","ybjWishListCount"], ["确认报价需求单","qrbjWishListCount"], ["已付款订单","yfkOrderCount"], ["已发货订单","yfhOrderCount"], ["历史订单转化数","historyOrderCount"], ["历史订单总金额","historyOrderAmountSum"], ["客单价","wishListAvrgPrice"]] userIdList = self.userDao.getExcludeUserIdList() dateFrom, dateTo = Config.getDateSpan() wishListBOList = self.wishListDao.getWishListByDateSpanExcludeUserIdList(dateFrom, dateTo, userIdList) orderList = self.offerListDao.getOrderByDateSpanExcludeUserIdList(dateFrom, dateTo, userIdList) historyDateFrom, historyDateTo = Config.getHistoryDateSpan() historyOrderList = self.offerListDao. \ getOrderByDateSpanExcludeUserIdList(historyDateFrom, historyDateTo, userIdList) userSet = set() wishListCount = len(wishListBOList) orderAmountSum = float(0) historyOrderCount = len(historyOrderList) historyOrderAmountSum = float(0) ybjWishListCount = 0 qrbjWishListCount = 0 yfkOrderCount = 0 yfhOrderCount = 0 for wishListBO in wishListBOList: userSet.add(wishListBO.userId) if wishListBO.status == "XYBJ": ybjWishListCount += 1 if wishListBO.status == "XQRBJ": qrbjWishListCount += 1 for order in orderList: if order.status == "BYFK": yfkOrderCount += 1 if order.status == "BYFH": yfhOrderCount += 1 orderAmountSum+=int(0 if not order.paidOfferAmount else order.paidOfferAmount) for historyOrder in historyOrderList: historyOrderAmountSum += int(0 if not historyOrder.paidOfferAmount else historyOrder.paidOfferAmount) detailReportDictList = [] detailReportDict = {} detailReportDict["countUserId"] = len(userSet) detailReportDict["wishListCount"] = wishListCount detailReportDict["orderAmountSum"] = "{:.2f}".format(orderAmountSum) detailReportDict["historyOrderCount"] = historyOrderCount detailReportDict["historyOrderAmountSum"] = "{:.2f}".format(historyOrderAmountSum) detailReportDict["ybjWishListCount"] = ybjWishListCount detailReportDict["qrbjWishListCount"] = qrbjWishListCount detailReportDict["yfkOrderCount"] = yfkOrderCount detailReportDict["yfhOrderCount"] = yfhOrderCount detailReportDict['wishListAvrgPrice'] = "{:.2f}".format(historyOrderAmountSum/historyOrderCount) detailReportDictList.append(detailReportDict) excelTool.export(headers,detailReportDictList,"统计汇总表")