def read_tgt_excel(sheetname):
excel = Excel(settings.TGT_FILE_PATH)
tgtcolnames = excel.get_column_names(sheetname)
tgt_colnames = []
for tgt_col_item in tgtcolnames:
if tgt_col_item is not None:
tgt_colnames.append(tgt_col_item)
return tgt_colnames
def setUpClass(cls):
cls.driver = select_Browser_WebDriver()
cls.url = 'http://pos.beta.acewill.net/consume'
cls.excel = Excel(
os.path.join(gl.dataPath, 'posCardData.xls').decode('utf-8'))
cls.toexcel = Excel(
os.path.join(gl.dataPath,
'posNotNameCardData.xls').decode('utf-8'))
def setUpClass(cls):
cls.driver = select_Browser_WebDriver()
cls.url = join_url('/consume')
#实体卡-未开过卡的
cls.excel = Excel(os.path.join(gl.dataPath, 'posCardData.xls'))
#不记名卡-未开过卡的
cls.toexcel = Excel(os.path.join(gl.dataPath,
'posNotNameCardData.xls'))
def read_src_excel(sheetname):
excel = Excel(settings.SRC_FILE_PATH)
srccolnames = excel.get_column_names(sheetname)
src_colnames = []
for src_col_item in srccolnames:
if src_col_item is not None:
src_colnames.append(src_col_item)
return src_colnames
def get_add_columns(sheetname):
_path = settings.TGT_FILE_PATH
_tgtexcel = Excel(_path)
_tgtcolumn = _tgtexcel.get_column_names(sheetname)
_match_columns = get_match_columns(sheetname)
for _item in _match_columns:
if _item in _tgtcolumn and _item is not None:
_tgtcolumn.remove(_item)
_sflogger.info('added column: {}'.format(_tgtcolumn))
return _tgtcolumn
def get_del_columns(sheetname):
_path = settings.SRC_FILE_PATH
_srcexcel = Excel(_path)
_srccolumn = _srcexcel.get_column_names(sheetname)
_match_columns = get_match_columns(sheetname)
for _item in _match_columns:
if _item in _srccolumn and _item is not None:
_srccolumn.remove(_item)
_sflogger.info('deleted column: {}'.format(_srccolumn))
return _srccolumn
def test_write(self):
exl = Excel(
'/Users/tatumn/home/workspace/python/github.com/tiantaozhang/excel_calc/test/data/产品报价明细.xlsx'
)
pwd = '/Users/tatumn/home/workspace/python/github.com/tiantaozhang/excel_calc/test/simple1.xls'
exl.write(
pwd, [u'表格1', u'sheet2'], {
u'表格1': [[u'a', 'bc', u'结果', u'会是怎样呢'], [u'哈哈', 'bc', 'ddd'],
['sasd', 'fda'], [u'好的', u'好的', u'好的', u'好的']],
'sheet2': [['test', u'测试'], [u'测试', u'😀']]
})
def setBgColor(sheetname):
_getSetList = get_diff_rowdata(sheetname)
_filepath = settings.TGT_FILE_PATH
_excel = Excel(_filepath)
_wb = _excel.get_wb()
_ws = _excel.get_sheet(sheetname)
for curitem in _ws.iter_rows():
if curitem[0].row in _getSetList:
for cell in curitem:
cell.fill = PatternFill(fgColor='FF0000', fill_type='solid')
_wb.save(settings.END_FILE_PATH)
def __init__(self, master=None):
Frame.__init__(self, master)
self.__excel = Excel("")
self.grid()
self.e1 = StringVar()
self.e2 = StringVar()
self.e3 = StringVar()
self.e4 = StringVar()
self.eResult = StringVar()
self.createWidgets()
logging.basicConfig(filename='excel.txt',
level=logging.INFO,
filemode='w')
def get_compare_colNum(sheetname, idx):
_srcpath = settings.SRC_FILE_PATH
_tgtpath = settings.TGT_FILE_PATH
_srcexcel = Excel(_srcpath)
_tgtexcel = Excel(_tgtpath)
_srccolumn = _srcexcel.get_column_names(sheetname)
_tgtcolumn = _tgtexcel.get_column_names(sheetname)
#getcompare column position for both sides
_matchcolumn = []
for _item in _srccolumn:
if _item in _tgtcolumn and _item is not None:
_matchcolumn.append(_item)
for _item in _matchcolumn:
if _item in idx:
_matchcolumn.remove(_item)
_sheadnum = []
_theadnum = []
for _maccol in _matchcolumn:
_curshead = _srcexcel.convert_col2header(sheetname, _maccol)
_sheadnum.append(_curshead)
for _maccol in _matchcolumn:
_curthead = _tgtexcel.convert_col2header(sheetname, _maccol)
_theadnum.append(_curthead)
_compareColZips = zip(_sheadnum, _theadnum)
#getcompare row position for both sides
return _compareColZips
# get_compare_colNum('CAPS Industry KPIs New',['Name'])
def qiandao_recharge(self):
page = int(self.get_argument('pageNum', 1))
psize = int(self.get_argument('numPerPage', 100))
action = self.get_argument('action', '')
tody = self.get_date(1) + datetime.timedelta(days=1)
start = self.get_argument('start', '')
yest = tody - datetime.timedelta(days=1)
if not start:
start = tody - datetime.timedelta(days=7)
else:
start = datetime.datetime.strptime(start, '%Y-%m-%d')
end = yest
count, stats = 0, []
stats = QiandaoRecharge.mgr().get_all_stat(start, end)
if action == 'export':
title = [('ds', '时间'), ('rechargetype', '充值类型'), ('num', '数量'),
('amount', '金额')]
xls = Excel().generate(title, stats, 1)
filename = 'qiandao_basic_%s.xls' % (yest.strftime('%Y-%m-%d'))
self.set_header('Content-Disposition',
'attachment;filename=%s' % filename)
self.finish(xls)
else:
count = len(stats)
stats = stats[(page - 1) * psize:page * psize]
self.render('data/qiandao_recharge.html',
date=yest.strftime('%Y-%m-%d'),
start=start.strftime('%Y-%m-%d'),
page=page,
psize=psize,
count=count,
stats=stats)
def qiandao_basic(self):
page = int(self.get_argument('pageNum', 1))
psize = int(self.get_argument('numPerPage', 100))
action = self.get_argument('action', '')
tody = self.get_date(1) + datetime.timedelta(days=1)
start = self.get_argument('start', '')
yest = tody - datetime.timedelta(days=1)
if not start:
start = tody - datetime.timedelta(days=7)
else:
start = datetime.datetime.strptime(start, '%Y-%m-%d')
end = yest
count, stats = 0, []
stats = QiandaoBasic.mgr().get_all_stat(start, end)
if action == 'export':
title = [('ds', '日期'), ('qiandaonum', '签到人数'),
('buqiannum1', '补签请求人数'), ('buqiannum2', '补签成功人数'),
('bangdingnum', '绑定手机号'), ('mergenum', '合并账户')]
xls = Excel().generate(title, stats, 1)
filename = 'qiandao_basic_%s.xls' % (yest.strftime('%Y-%m-%d'))
self.set_header('Content-Disposition',
'attachment;filename=%s' % filename)
self.finish(xls)
else:
count = len(stats)
stats = stats[(page - 1) * psize:page * psize]
self.render('data/qiandao_basic.html',
date=yest.strftime('%Y-%m-%d'),
start=start.strftime('%Y-%m-%d'),
page=page,
psize=psize,
count=count,
stats=stats)
def qiandao_level(self):
page = int(self.get_argument('pageNum', 1))
psize = int(self.get_argument('numPerPage', 100))
tody = self.get_date(1) + datetime.timedelta(days=1)
start = self.get_argument('start', '')
action = self.get_argument('action', '')
yest = tody - datetime.timedelta(days=1)
if not start:
start = tody - datetime.timedelta(days=7)
else:
start = datetime.datetime.strptime(start, '%Y-%m-%d')
end = yest
count, stats = 0, []
stats = Qiandao.mgr().get_all_stat(start, end)
if action == 'export':
title = [('ds', '时间'), ('level', '等级'), ('card6', '6阅饼'),
('card11', '11阅饼'), ('card16', '16阅饼'),
('card21', '21阅饼'), ('card26', '26阅饼'),
('card52', '52阅饼')]
xls = Excel().generate(title, stats, 1)
filename = 'qiandao_level_%s.xls' % (yest.strftime('%Y-%m-%d'))
self.set_header('Content-Disposition',
'attachment;filename=%s' % filename)
self.finish(xls)
else:
count = len(stats)
stats = stats[(page - 1) * psize:page * psize]
self.render('data/qiandao_level.html',
date=yest.strftime('%Y-%m-%d'),
start=start.strftime('%Y-%m-%d'),
page=page,
psize=psize,
count=count,
stats=stats)
def highlight_columns(sheetname):
dif_items = get_dif_columns(sheetname)
excel = Excel(settings.TGT_FILE_PATH)
wb = excel.get_wb()
ws = excel.get_sheet(sheetname)
max_row = excel.get_max_row(sheetname)
col_hdr = []
for dif_col in dif_items:
col_hdr_item = excel.convert_col2header(sheetname=sheetname,
column_name=dif_col)
col_hdr.append(col_hdr_item)
#print(col_hdr)
# for row_nbr in range(1,max_row-490):
# for hdr in col_hdr:
# cell_item = hdr+str(row_nbr)
# print(cell_item)
# cell_item.fill = openpyxl.styles.fills.Color.
# ws[cell_item].fill = colors.RED
#
# wb.save('new_compare_result.xlsx')
for item in get_dif_columns(sheetname):
cells = excel.get_column(sheetname, column_name=item)
print(cells)
for cell in cells:
cell.fill = PatternFill(patternType='solid', fgColor='00FF0000')
wb.save('new_compare_result.xlsx')
def setUpClass(cls):
cls.driver = select_Browser_WebDriver()
cls.url = join_url('/card/index')
cls.excel = Excel(
os.path.join(
gl.dataPath, 'posChargeCard.xls'
)
)
#从excel获取一条标记为N的卡号
cls.cardNo = float(
cls.excel.getCardNo(cell_col=0,cell_valueType=1)
).__int__().__str__()
def get_data_message(path,sheetname,idx=None):
#initial index into list
_headername = []
#store message data
_alldata = []
_excel = Excel(path)
if idx is None:
_mactch_column_name = compareData.get_match_columns(sheetname)
for columnname in _mactch_column_name:
_curcells = _excel.convert_col2header(sheetname,columnname)
_headername.append(_curcells)
else:
_indexCols = idx.split(',')
_mactch_column_name = compareData.get_match_columns(sheetname, _indexCols)
for columnname in _indexCols:
_curcells = _excel.convert_col2header(sheetname, columnname)
_headername.append(_curcells)
_cursheet = _excel.get_sheet(sheetname)
for _row in range(2, _cursheet.max_row+1):
_rowdata = []
for _column in _headername:
_cellname = "{}{}".format(_column, _row)
#get current cell line number and line column
_cellvalue = _cursheet[_cellname].value
_cellvalue = str(_cellvalue).upper()
_rowdata.append(_cellvalue.strip())
#upper all values
_alldata.append(_rowdata)
for _item in _alldata[::-1]:
if _item in _alldata:
_getcount = _alldata.count(_item)
_item.append(_getcount)
return _alldata
def recommendation(self):
page = int(self.get_argument('pageNum', 1))
psize = int(self.get_argument('numPerPage', 100))
tody = self.get_date(1) + datetime.timedelta(days=1)
start = self.get_argument('start', '')
appid = self.get_argument('appid', '')
action = self.get_argument('action', '')
type = self.get_argument('type', '')
yest = tody - datetime.timedelta(days=1)
if not start:
start = tody - datetime.timedelta(days=7)
else:
start = datetime.datetime.strptime(start, '%Y-%m-%d')
end = yest
count, stats = 0, []
stats = []
_start, days = start, []
while _start <= end:
_end = _start + datetime.timedelta(days=1)
stat, appids = Recommendation.mgr(
).get_recommendation_one_day_stat(_start, appid)
stat = self.data_process(stat, appids, _start)
stat = self.bubblesort(stat, 'download')
stats.extend(stat)
days.append(_start)
_start = _end
for stat in stats:
stat['time'] = stat['time'].strftime('%Y-%m-%d')
stats = Service.inst().fill_app_info(stats)
count = len(stats)
if action == 'export':
title = [('time', '时间'), ('appid', 'appid'), ('appname', 'app名字'),
('download', '下载'), ('install', '安装')]
xls = Excel().generate(title, stats, 1)
filename = 'recommendation_%s.xls' % (yest.strftime('%Y-%m-%d'))
self.set_header('Content-Disposition',
'attachment;filename=%s' % filename)
self.finish(xls)
else:
stats = stats[(page - 1) * psize:page * psize]
self.render('data/recommendation.html',
appid=appid,
type=type,
date=yest.strftime('%Y-%m-%d'),
start=start.strftime('%Y-%m-%d'),
page=page,
psize=psize,
count=count,
stats=stats)
def wapbookstat(self):
partner_id = int(self.get_argument('partner_id', 0))
wap_type = self.get_argument('wap_type', '')
charge_type = self.get_argument('charge_type', '')
page = int(self.get_argument('pageNum', 1))
psize = int(self.get_argument('numPerPage', 100))
tody = self.get_date(1) + datetime.timedelta(days=1)
yest = tody - datetime.timedelta(days=1)
action = self.get_argument('action', '')
start = yest.strftime('%Y-%m-%d')
count, stats = 0, []
stats = WapBookStat.mgr().get_wap_book_stat(time=start)
if wap_type:
stats = stats.filter(wap_type=wap_type)
if partner_id != 0:
stats = stats.filter(partner_id=partner_id)
if charge_type:
if charge_type == 'book':
charge_type = '10'
elif charge_type == 'chapter':
charge_type = '20'
stats = stats.filter(charge_type=charge_type)
stats = Service.inst().fill_book_info(stats)
count = len(stats)
if action == 'export':
title = [('book_id', '书ID'), ('name', '书名'), ('author', '作者'),
('cp', '版权'), ('category_0', '类别'), ('category_1', '子类'),
('wap_type', 'wap类型'), ('charge_type', '计费类型'),
('state', '状态'), ('fee', '月饼消费'), ('pay_down', '付费订购数'),
('pay_user', '付费用户数'), ('visit_uv', '访问UV'),
('login_uv', '登陆UV'), ('visit', '访问PV'),
('read_pv', '阅读PV')]
xls = Excel().generate(title, stats, 1)
filename = 'book_%s.xls' % (yest.strftime('%Y-%m-%d'))
self.set_header('Content-Disposition',
'attachment;filename=%s' % filename)
self.finish(xls)
else:
stats = stats[(page - 1) * psize:page * psize]
self.render('data/wap_book_stat.html',
wap_type=wap_type,
charge_type=charge_type,
page=page,
psize=psize,
date=start,
stats=stats,
partner_id=partner_id,
count=count)
def excel(books, start):
title = [('time', '时间'), ('book_id', '书ID'), ('name', '书名'),
('author', '作者'), ('cp', '版权'), ('category_0', '类别'),
('category_1', '子类'), ('state', '状态'), ('charge_type', '计费类型'),
('fee', '收益'), ('pay_down', '付费下载数'), ('pay_user', '付费下载用户数'),
('free_down', '免费下载数'), ('free_user', '免费下载用户数'), ('pv', '简介访问数'),
('uv', '简介访问人数')]
books = bubblesortdesc(books)
data = books
start = start.strftime('%Y-%m-%d')
filename = "%s.xls" % (start)
f = open(filename, 'wb')
f.write(Excel().generate(title, data))
f.close()
return filename
def charge_by_book(self):
plan_id = int(self.get_argument('plan_id', 0))
partner_id = int(self.get_argument('partner_id', 0))
page = int(self.get_argument('pageNum', 1))
psize = int(self.get_argument('numPerPage', 100))
order_field = self.get_argument('orderField', 'uv')
assert order_field in ('pay_user', 'free_user', 'fee', 'uv', 'e_val',
'e_val_1wk', 'e_val_4wk')
action = self.get_argument('action', '')
book_ids = self.get_argument('book_ids', '')
bookid_list = [i for i in book_ids.split(',') if i]
count, books = 0, []
q = EValueCharegeByBook.mgr().get_all_data(bookid_list)
count = len(q)
if action == 'export':
res = q.orderby(order_field, 'DESC')
else:
res = q.orderby(order_field,
'DESC')[(page - 1) * psize:page * psize]
books = Service.inst().fill_book_info(res)
e_val_fields = ['e_val', 'e_val_1wk', 'e_val_4wk']
for book in books:
book['fee'] = '%.0f' % float(book['fee'])
for i in e_val_fields:
if book[i] == None:
book[i] = 0
if action == 'export':
title = [('book_id', '书ID'), ('name', '书名'), ('fee', '本月饼消费'),
('free_user', '免费用户数'), ('pay_user', '付费用户数'),
('uv', 'UV'), ('e_val', 'E值'), ('e_val_1wk', '一周E值'),
('e_val_4wk', '四周E值')]
xls = Excel().generate(title, books, 1)
filename = 'evalue_book.xls'
self.set_header('Content-Disposition',
'attachment;filename=%s' % filename)
self.finish(xls)
else:
self.render('data/e_value_charge_by_book.html',
plan_id=plan_id,
partner_id=partner_id,
book_ids=book_ids,
page=page,
psize=psize,
books=books,
count=count,
order_field=order_field)
def test_read(self):
exl = Excel(
'/Users/tatumn/home/workspace/python/github.com/tiantaozhang/excel_calc/test/data/快递费用明细.xlsx'
)
sheets = exl.read()
print sheets
exl.changePath(
'/Users/tatumn/home/workspace/python/github.com/tiantaozhang/excel_calc/test/simple1.xls'
)
sheets = exl.read()
pprint.pprint(sheets)
def index(self):
plan_id = int(self.get_argument('plan_id', 0))
partner_id = int(self.get_argument('partner_id', 0))
page = int(self.get_argument('pageNum', 1))
psize = int(self.get_argument('numPerPage', 100))
order_field = self.get_argument('orderField', 'usnum')
charge_type = self.get_argument('charge_type', '')
assert order_field in ('cpay_user', 'cpay_user_percentage', 'cfee',
'ARPU', 'usnum')
action = self.get_argument('action', '')
book_ids = self.get_argument('book_ids', '')
bookid_list = [i for i in book_ids.split(',') if i]
startpoint = self.get_argument('startpoint', '书架')
count, books = 0, []
q = EValue.mgr().get_all_data(bookid_list, startpoint)
count = len(q)
if action == 'export':
res = q.orderby(order_field, 'DESC')
else:
res = q.orderby(order_field,
'DESC')[(page - 1) * psize:page * psize]
books = Service.inst().fill_book_info_by_bid(res)
# pagination
if action == 'export':
title = [('bid', '书ID'), ('name', '书名'), ('cfee', '章月饼消费'),
('usnum', '总人数'), ('cpay_user', '付费人数'),
('cpay_user_percentage', '付费人数/总人数'), ('ARPU', 'ARPU')]
xls = Excel().generate(title, books, 1)
filename = 'evalue_chapter.xls'
self.set_header('Content-Disposition',
'attachment;filename=%s' % filename)
self.finish(xls)
else:
self.render('data/e_value.html',
plan_id=plan_id,
partner_id=partner_id,
book_ids=book_ids,
startpoint=startpoint,
page=page,
psize=psize,
charge_type=charge_type,
books=books,
count=count,
order_field=order_field)
def baoyue_stat(self):
page = int(self.get_argument('pageNum', 1))
psize = int(self.get_argument('numPerPage', 100))
partner_id = int(self.get_argument('partner_id', 0))
version_name = self.get_argument('version_name', '')
action = self.get_argument('action', '')
tody = self.get_date(1) + datetime.timedelta(days=1)
start = self.get_argument('start', '')
yest = tody - datetime.timedelta(days=1)
if not start:
start = tody - datetime.timedelta(days=7)
else:
start = datetime.datetime.strptime(start, '%Y-%m-%d')
end = yest
count, stats = 0, []
stats = OperaBaoyueSum.mgr().get_all_stat(start, end)
if partner_id:
stats = stats.filter(partner_id=partner_id)
if version_name:
stats = stats.filter(versionname=version_name)
if action == 'export':
title = [('ds', '日期'), ('partner_id', '渠道'), ('versionname', '版本'),
('orderpv', '订购次数'), ('orderuv', '订购人数'),
('renewtimes', '续订次数'), ('rechargingnum', '主账号消费'),
('giftrechargingnum', '副账号消费')]
xls = Excel().generate(title, stats, 1)
filename = 'qiandao_basic_%s.xls' % (yest.strftime('%Y-%m-%d'))
self.set_header('Content-Disposition',
'attachment;filename=%s' % filename)
self.finish(xls)
else:
count = len(stats)
stats = stats[(page - 1) * psize:page * psize]
self.render('data/baoyue_stat.html',
partner_id=partner_id,
version_name=version_name,
date=yest.strftime('%Y-%m-%d'),
start=start.strftime('%Y-%m-%d'),
page=page,
psize=psize,
count=count,
stats=stats)
def startpoint(self):
plan_id = int(self.get_argument('plan_id', 0))
partner_id = int(self.get_argument('partner_id', 0))
page = int(self.get_argument('pageNum', 1))
psize = int(self.get_argument('numPerPage', 100))
order_field = self.get_argument('orderField', 'shujia')
assert order_field in ('shucheng', 'shujia', 'dabao')
action = self.get_argument('action', '')
book_ids = self.get_argument('book_ids', '')
bookid_list = [i for i in book_ids.split(',') if i]
count, books = 0, []
q = EValueStartPointAll.mgr().get_all_data(bookid_list)
count = len(q)
if action == 'export':
res = q.orderby(order_field, 'DESC')
else:
res = q.orderby(order_field,
'DESC')[(page - 1) * psize:page * psize]
books = Service.inst().fill_book_info_by_bid(res)
if action == 'export':
title = [('bid', '书ID'), ('name', '书名'), ('shucheng', '书城'),
('shujia', '书架'), ('dabao', '打包'), ('top1', 'top1'),
('top2', 'top2'), ('top3', 'top3'), ('top4', 'top4'),
('top5', 'top5'), ('top6', 'top6'), ('top7', 'top7'),
('top8', 'top8'), ('top9', 'top9'), ('top10', 'top10')]
xls = Excel().generate(title, books, 1)
filename = 'evalue_startpoint.xls'
self.set_header('Content-Disposition',
'attachment;filename=%s' % filename)
self.finish(xls)
else:
self.render('data/e_value_startpoint.html',
plan_id=plan_id,
partner_id=partner_id,
book_ids=book_ids,
page=page,
psize=psize,
books=books,
count=count,
order_field=order_field)
def max_cid_max_fee(self):
plan_id = int(self.get_argument('plan_id', 0))
partner_id = int(self.get_argument('partner_id', 0))
page = int(self.get_argument('pageNum', 1))
psize = int(self.get_argument('numPerPage', 100))
order_field = self.get_argument('orderField', 'maxfee')
assert order_field in ('maxcid', 'maxfee')
action = self.get_argument('action', '')
book_ids = self.get_argument('book_ids', '')
bookid_list = [i for i in book_ids.split(',') if i]
count, books = 0, []
q = EValueMaxCidMaxFee.mgr().get_all_data(bookid_list)
count = len(q)
if action == 'export':
res = q.orderby(order_field, 'DESC')
else:
res = q.orderby(order_field,
'DESC')[(page - 1) * psize:page * psize]
books = Service.inst().fill_book_info_by_bid(res)
if action == 'export':
title = [('bid', '书ID'), ('name', '书名'), ('author', '作者'),
('cp', '版权'), ('category_0', '类别'), ('category_1', '子类'),
('charge_type', '计费类型'), ('state', '状态'),
('maxcid', '最大章节'), ('maxfee', '最大收入')]
xls = Excel().generate(title, books, 1)
filename = 'evalue_max_cid_max_fee.xls'
self.set_header('Content-Disposition',
'attachment;filename=%s' % filename)
self.finish(xls)
else:
self.render('data/e_value_max_cid_max_fee.html',
plan_id=plan_id,
partner_id=partner_id,
book_ids=book_ids,
order_field=order_field,
page=page,
psize=psize,
books=books,
count=count)
def pvuv_stat(self):
page = int(self.get_argument('pageNum', 1))
psize = int(self.get_argument('numPerPage', 100))
action = self.get_argument('action', '')
tody = self.get_date(1) + datetime.timedelta(days=1)
start = self.get_argument('start', '')
key = self.get_argument('key', '')
yest = tody - datetime.timedelta(days=1)
if not start:
start = tody - datetime.timedelta(days=7)
else:
start = datetime.datetime.strptime(start, '%Y-%m-%d')
end = yest
count, stats = 0, []
stats = A0.mgr().get_pv_uv_multi_days(start, end)
if key:
stats = stats.filter(a0=key)
for stat in stats:
stat['time'] = stat['time'].strftime('%Y-%m-%d')
if action == 'export':
title = [('time', '日期'), ('a0', 'KEY'), ('pv', 'PV'), ('uv', 'UV')]
xls = Excel().generate(title, stats, 1)
filename = 'qiandao_basic_%s.xls' % (yest.strftime('%Y-%m-%d'))
self.set_header('Content-Disposition',
'attachment;filename=%s' % filename)
self.finish(xls)
else:
count = len(stats)
stats = stats[(page - 1) * psize:page * psize]
self.render('data/pvuv_stat.html',
key=key,
date=yest.strftime('%Y-%m-%d'),
start=start.strftime('%Y-%m-%d'),
page=page,
psize=psize,
count=count,
stats=stats)
def get_match_columns(sheetname, idx=None):
_srcpath = settings.SRC_FILE_PATH
_tgtpath = settings.TGT_FILE_PATH
_srcexcel = Excel(_srcpath)
_tgtexcel = Excel(_tgtpath)
_srccolumn = _srcexcel.get_column_names(sheetname)
_tgtcolumn = _tgtexcel.get_column_names(sheetname)
_matchcolumn = []
for _item in _srccolumn:
if _item in _tgtcolumn and _item is not None:
_matchcolumn.append(_item)
if idx is not None:
for i in _matchcolumn:
if i in idx:
_matchcolumn.remove(i)
_sflogger.info('matched column: {}'.format(_matchcolumn))
return _matchcolumn
def setUpClass(cls):
cls.driver = select_Browser_WebDriver()
cls.url = join_url('/actualcard/opencard')
cls.toexcel = Excel(os.path.join(gl.dataPath, 'actualopencard.xls'))
def setUpClass(cls):
cls.excel = Excel("加油卡完整用例.xls")
cls.sheet_data = cls.excel.get_data("查询")
def book_stat(self):
page = int(self.get_argument('pageNum', 1))
psize = int(self.get_argument('numPerPage', 20))
charge_type = self.get_argument('charge_type', '')
factory_id = int(self.get_argument('factory_id', 0))
order_field = self.get_argument('orderField', 'fee')
query_mode = self.get_argument('query_mode', 'accounting_book')
group = self.get_argument('group', '')
assert order_field in ('pay_user', 'free_user', 'pay_down',
'free_down', 'pv', 'uv', 'fee', 'batch_fee',
'batch_pv', 'batch_uv', 'real_fee')
action = self.get_argument('action', '')
# perm
q = Factory.mgr().Q()
group and q.filter(group=group)
factory_list = self.filter_factory_acc(q[:], query_mode)
# for safety reason, some time a perm doesn't have a val, and in this Situation a factory CANN'T see all factory accouting data!!!
cuser = self.get_current_user()
if len(factory_list) > 150 and (cuser['name'] not in SAFE_USER):
factory_list = []
if factory_id == 0 and factory_list:
factory_id = factory_list[0].id
partner_list = Partnerv2.mgr().Q().filter(factory_id=factory_id)[:]
# scope list
scopeid_list = []
for i in partner_list:
scope = Scope.mgr().Q().filter(platform_id=6,
run_id=0,
partner_id=i.partner_id)[0]
if scope:
scopeid_list.append(scope.id)
tody = self.get_date(1) + datetime.timedelta(days=1)
yest = tody - datetime.timedelta(days=1)
last = yest - datetime.timedelta(days=1)
start = self.get_argument('start', '')
if start:
start = datetime.datetime.strptime(start, '%Y-%m-%d')
else:
start = yest
count, books = 0, []
q = BookStat.mgr().get_accouting_multy_scope_stat(
scopeid_list, order_field, start)
charge_type and q.filter(charge_type=charge_type)
q = q.orderby(order_field, 'DESC')
count = len(q)
if action == 'export':
books = q.data()
for i in books:
i['batch_fee'] = "%0.2f" % i['batch_fee']
else:
books = q[(page - 1) * psize:page * psize]
for i in books:
i['batch_fee'] = "%0.2f" % i['batch_fee']
books = Service.inst().fill_book_info(books)
# pagination
page_count = (count + psize - 1) / psize
for book in books:
book['fee'] = "%.01f" % float(book['fee'])
book['real_fee'] = "%.01f" % float(book['real_fee'])
if action == 'export':
books = Service.inst().fill_book_count_info(books)
while (self.do_books_have_two_or_empty_title(books)):
books = self.remove_books_two_or_empty_title(books)
title = [('time', '时间'), ('book_id', '书ID'), ('name', '书名'),
('author', '作者'), ('cp', '版权'), ('category_2', '类别'),
('category_1', '子类'), ('category_0', '三级分类'),
('state', '状态'), ('charge_type', '计费类型'), ('fee', '收益'),
('real_fee', '主账户收益'), ('pay_down', '付费下载数'),
('pay_user', '付费下载用户数'), ('free_down', '免费下载数'),
('free_user', '免费下载用户数'), ('pv', '简介访问数'),
('uv', '简介访问人数'), ('batch_fee', '批量订购阅饼消费')]
xls = Excel().generate(title, books, 1)
filename = 'book_%s.xls' % (yest.strftime('%Y-%m-%d'))
self.set_header('Content-Disposition',
'attachment;filename=%s' % filename)
self.finish(xls)
else:
self.render('data/accounting_book.html',
page=page,
psize=psize,
count=count,
page_count=page_count,
books=books,
charge_type=charge_type,
order_field=order_field,
start=start.strftime('%Y-%m-%d'),
factory_id=factory_id,
factory_list=factory_list,
query_mode=query_mode)