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,"统计汇总表")
