def getReport(self, excelTool): headers = [["订单号", "offerListSn"], ["订单状态", "status"], ["报价次数","offerCount"], ["维修店名称", "companyName"], ["填单人", "wishListMaker"], ["填单人手机", "wishListMakerTel"], ["对应供应商", "sellerName"], ["备注(取消原因)", "reason"]] userIdList = self.userDao.getExcludeUserIdList() dateFrom, dateTo = Config.getDateSpan() offerListBOList = self.offerListDao.getOrderByDateSpanExcludeUserIdList(dateFrom, dateTo, userIdList) if len(offerListBOList) == 0: return offerListIdList = [] wishListIdList = [] for offerListBO in offerListBOList: wishListIdList.append(offerListBO.wishListId) offerListIdList.append(offerListBO.id) # 根据offerListIdList获取wishListBOList信息 wishListBOList = self.wishListDao.getByIdList(wishListIdList) wishListIdKeyDict = {} for wishList in wishListBOList: wishListIdKeyDict[wishList.id] = wishList # 获取取消原因 offerListActionReasonBOList = self.offerListActionReasonDao.getByOfferListIdList(offerListIdList) offerListActionReasonOfferListIdKeyDict = {} for offerListActionReasonBO in offerListActionReasonBOList: offerListActionReasonOfferListIdKeyDict[offerListActionReasonBO.offerListId] = offerListActionReasonBO #获取需求单报价次数 olgOfferCountDict = self.offerListGoodsDao.countOfferNumber(offerListIdList); orderReportDictList = [] for offerList in offerListBOList: orderReportDict = {} orderReportDict['offerListSn'] = offerList.offerListSn.strip() orderReportDict['offerCount'] = olgOfferCountDict.get(offerList.id,"") orderReportDict['sellerName'] = offerList.sellerName.strip() orderReportDict['status'] = Config.statusDict.get(offerList.status, "") wishList = wishListIdKeyDict.get(offerList.wishListId, None) if wishList != None: orderReportDict['companyName'] = wishList.companyName.strip() orderReportDict['wishListMaker'] = wishList.wishListMaker.strip() orderReportDict['wishListMakerTel'] = wishList.wishListMakerTel.strip() offerListActionReasonBO = offerListActionReasonOfferListIdKeyDict.get(offerList.id, None) if offerListActionReasonBO != None: orderReportDict['reason'] = offerListActionReasonBO.reason.strip() else: orderReportDict['reason'] = "" orderReportDictList.append(orderReportDict) excelTool.export(headers, orderReportDictList, "订单报表")
def getReport(self, excelTool): headers = [["需求单号", "wishListSn"], ["需求单状态", "status"], ["需求单提交的时间", "gmtCreate"], ["首次报价的时间", "olGmtCreate"], ["维修店名称", "companyName"], ["填单人", "wishListMaker"], ["填单人手机", "wishListMakerTel"], ["对应供应商", "sellerName"], ["供应商对接人手机", "telephone"], ["备注(取消原因)", "reason"]] userIdList = self.userDao.getExcludeUserIdList() dateFrom, dateTo = Config.getDateSpan() wishListBOList = self.wishListDao.getWishListByDateSpanExcludeUserIdList(dateFrom, dateTo, userIdList) if len(wishListBOList) == 0: return wishListIdList = [wl.id for wl in wishListBOList] offerListBOList = self.offerListDao.getByWishListIdList(wishListIdList) offerListWishListIdKeyDict = {} for offerList in offerListBOList: offerListWishListIdKeyDict[offerList.wishListId] = offerList wishListActionReasonBOList = self.wishListActionReasonDao.getByWishListIdList(wishListIdList) wishListActionReasonBOWishListIdKeyDict = {} for wishListActionReasonBO in wishListActionReasonBOList: wishListActionReasonBOWishListIdKeyDict[wishListActionReasonBO.wishListId] = wishListActionReasonBO wishListReportDictList = [] for wishListBO in wishListBOList: wishListReportDict = {} wishListId = wishListBO.id wishListReportDict["wishListSn"] = wishListBO.wishListSn wishListReportDict["status"] = Config.statusDict[wishListBO.status] wishListReportDict["gmtCreate"] = wishListBO.gmtCreate.strftime('%Y-%m-%d %H:%M:%S') wishListReportDict["companyName"] = wishListBO.companyName wishListReportDict["wishListMaker"] = wishListBO.wishListMaker wishListReportDict["wishListMakerTel"] = wishListBO.wishListMakerTel wishListReportDict["olGmtCreate"] = "" wishListReportDict["sellerName"] = "" wishListReportDict["telephone"] = "" wishListReportDict["reason"] = "" offerListBO = offerListWishListIdKeyDict.get(wishListId, None) if offerListBO != None: wishListReportDict["olGmtCreate"] = offerListBO.gmtCreate.strftime('%Y-%m-%d %H:%M:%S') wishListReportDict["sellerName"] = offerListBO.sellerName wishListReportDict["telephone"] = offerListBO.telephone wishListActionReasonBO = wishListActionReasonBOWishListIdKeyDict.get(wishListId, None) if wishListActionReasonBO != None: wishListReportDict["reason"] = wishListActionReasonBO.reason wishListReportDictList.append(wishListReportDict) excelTool.export(headers, wishListReportDictList, "需求单报表")
def getFeatureReport(self, excelTool): headers = [["订单号", "offerListSn"], ["feature", "feature"], ["签收时间", "receiveTime"]] dateFrom, dateTo = Config.getDateSpan() exFileterSeller = Config.excludeFilterSeller offerListBOList = self.offerListDao.getByReciveTimeSpan(dateFrom, dateTo, exFileterSeller) if len(offerListBOList) == 0: return featureReportDictList = [] for offerListBO in offerListBOList: featureReportDict = {} featureReportDict["offerListSn"] = offerListBO.offerListSn featureReportDict["feature"] = offerListBO.feature featureReportDict["receiveTime"] = offerListBO.receiveTime.strftime('%Y-%m-%d %H:%M:%S') featureReportDictList.append(featureReportDict) excelTool.export(headers, featureReportDictList, "金蝶号数据报表")
def sendMail( cls, fileName, toAddrMap=Config.lopToAddrMap, emailTextFilePath=Config.defaultEmailTextFilePath, subject=None ): emailTool = cls() emailTool.start(Config.smtpServer, Config.fromAddrMap, Config.emailPassword) if fileName: file = open(emailTextFilePath, "r") emialText = file.read() emailTool.attachText(emialText) emailTool.attachFile(fileName) else: file = open(Config.noReportEmailTextFilePath, "r") emialText = file.read() emailTool.attachText(emialText) subjectName = (Config.getDate().date() - timedelta(days=1)).strftime("%Y年%m月%d日报表") if subject is not None: subjectName = subject + subjectName emailTool.send(subjectName, toAddrMap) emailTool.stop()
#coding=utf-8 from common.config.Config import Config from common.email.EmailTool import EmailTool from server.SummaryServiceImpl import SummaryServiceImpl from server.WishListServiceImpl import WishListServiceImpl __author__ = 'chenjinlong' from dal.util.conn.MySqlConn import MySqlConn from dal.mapper.SellerBrandCityMapper import SellerBrandCityMapper #初始化配置参数 Config.initConf() #@SSHTunnel.sshWrapper @MySqlConn.dbWrapper def main(): sellerBrandCityMapper = SellerBrandCityMapper() sellerBrandCityDOList = sellerBrandCityMapper.getBySellerId(10036); sql_item = "SELECT * FROM db_wish_list WHERE " sql = " union\n".join([sql_item+"city_id=%s AND series=%s \n" %(sellerBrandCityDO.cityId,sellerBrandCityDO.carCategoryId) for sellerBrandCityDO in sellerBrandCityDOList]) sqlFile = open("sql.txt","w") sqlFile.write(sql) main()
def getDetailReport(self, excelTool): headers = [["需求单号", "wishListSn"], ["需求单配件信息","goodsInfo"], ["需求单状态", "status"], ["需求单提交的时间", "gmtCreate"], ["首次报价的时间", "olGmtCreate"], ["首次报价间隔(分钟)","firstQuoteDiff"], ["维修店名称", "companyName"], ["填单人", "wishListMaker"], ["填单人手机", "wishListMakerTel"], ["对应供应商", "sellerName"], ["供应商对接人手机", "telephone"], ["备注(取消原因)", "reason"]] userIdList = self.userDao.getExcludeUserIdList() dateFrom, dateTo = Config.getDateSpan() wishListBOList = self.wishListDao.getWishListByDateSpanExcludeUserIdList(dateFrom, dateTo, userIdList) if len(wishListBOList) == 0: return wishListIdList = [wl.id for wl in wishListBOList] offerListBOList = self.offerListDao.getByWishListIdList(wishListIdList) offerListWishListIdKeyDict = {} for offerList in offerListBOList: offerListWishListIdKeyDict[offerList.wishListId] = offerList wishListActionReasonBOList = self.wishListActionReasonDao.getByWishListIdList(wishListIdList) wishListActionReasonBOWishListIdKeyDict = {} for wishListActionReasonBO in wishListActionReasonBOList: wishListActionReasonBOWishListIdKeyDict[wishListActionReasonBO.wishListId] = wishListActionReasonBO wlgInfoWishListIdKeyDict = self.wishListGoodsDao.getInfoWishListIdKeyByWishListIdList(wishListIdList) detailReportDictList = [] for wishListBO in wishListBOList: detailReportDict = {} wishListId = wishListBO.id detailReportDict["wishListSn"] = wishListBO.wishListSn detailReportDict["goodsInfo"] = wlgInfoWishListIdKeyDict.get(wishListId,"") detailReportDict["status"] = Config.statusDict[wishListBO.status] detailReportDict["gmtCreate"] = wishListBO.gmtCreate.strftime('%Y-%m-%d %H:%M:%S') detailReportDict["olGmtCreate"] = "" detailReportDict["firstQuoteDiff"] = "" detailReportDict["companyName"] = wishListBO.companyName detailReportDict["wishListMaker"] = wishListBO.wishListMaker detailReportDict["wishListMakerTel"] = wishListBO.wishListMakerTel detailReportDict["sellerName"] = "" detailReportDict["telephone"] = "" detailReportDict["reason"] = "" offerListBO = offerListWishListIdKeyDict.get(wishListId, None) if offerListBO != None: detailReportDict["olGmtCreate"] = offerListBO.gmtCreate.strftime('%Y-%m-%d %H:%M:%S') detailReportDict["sellerName"] = offerListBO.sellerName detailReportDict["telephone"] = offerListBO.telephone firstQuoteDiff = offerListBO.gmtCreate-wishListBO.gmtCreate detailReportDict["firstQuoteDiff"] = firstQuoteDiff.seconds/60 wishListActionReasonBO = wishListActionReasonBOWishListIdKeyDict.get(wishListId, None) if wishListActionReasonBO != None: detailReportDict["reason"] = wishListActionReasonBO.reason detailReportDictList.append(detailReportDict) excelTool.export(headers, detailReportDictList, "需求单明细报表")
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,"统计汇总表")