def growthrate(request):
if not request.user.is_authenticated():
return render_to_response('servicekpi/index.html', {},
context_instance=RequestContext(request))
else:
if request.POST:
wb = Workbook()
c_border = xlwt.Borders()
c_border.top = xlwt.Borders.THICK
c_border.bottom = xlwt.Borders.THICK
c_border.left = xlwt.Borders.THICK
c_border.right = xlwt.Borders.THICK
font0 = xlwt.Font()
font0.name = 'Osaka'
c_style = xlwt.XFStyle()
c_style.borders = c_border
c_style.font = font0
year, month = int(request.POST["sy"]), int(request.POST["sm"])
start0 = str(year) + str(month).zfill(2)
cdg_db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
sql = "select * "
sql += "from mnl_kpi_monthly_subscription where date <= "
sql += str(start0)
log = cdg_db.db_anyselect(sql)
data = {}
for v in log:
data.setdefault(v["app_id"], {})
data[v["app_id"]].setdefault(v["os_type"], {})
data[v["app_id"]][v["os_type"]].setdefault(v["regdate"], {})
data[v["app_id"]][v["os_type"]][v["regdate"]].setdefault(v["date"], float(v["uu"]))
ws = []
i = 0
for app_id, d0 in data.items():
for os, d1 in d0.items():
j = 0
ws.append(wb.add_sheet(str(app_id) + "_" + str(os)))
total = {}
for regdate, d2 in sorted(d1.items()):
k = j
ws[i].write(j + 1, 0, regdate, c_style)
for date, uu in sorted(d2.items()):
if k == j:
ws[i].write(0, k + 1, date, c_style)
ws[i].write(j + 1, k + 1, uu, c_style)
total.setdefault(date, {})
total[date].setdefault("uu", 0)
if k == j:
total[date].setdefault("col", k + 1)
total[date]["uu"]+= uu
k += 1
j += 1
ws[i].write(j + 1, 0, u"合計", c_style)
ws[i].write(j + 2, 0, u"成長率", c_style)
p = 0
for key, value in sorted(total.items()):
try:
col = value["col"]
except:
col += 1
ws[i].write(j + 1, col, value["uu"], c_style)
g = 100 * value["uu"] / p if p != 0 else "NONE"
ws[i].write(j + 2, col, g, c_style)
p = value["uu"]
i += 1
file_name = 'growthrate_' + str(start0) + '.xls'
response = HttpResponse(mimetype='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename=%s' % file_name
wb.save(response)
return response
else:
year, month = udf.pulldown_menu_yyyymm("cvr")
context = RequestContext(request, {
'user': User,
"year" : year,
"month" : month,
})
return render_to_response('servicekpi/growthrate.html', {},
context_instance=context)
def kpicsv(request):
if not request.user.is_authenticated():
return render_to_response('servicekpi/index.html', {},
context_instance=RequestContext(request))
else:
if request.POST:
year, month = int(request.POST["sy"]), int(request.POST["sm"])
start0 = str(year) + str(month).zfill(2) + "01"
dd = date(int(year), int(month), 1) + relativedelta(months = 1)
end0 = dd.strftime('%Y%m%d')
dd = date(int(year), int(month), 1) + relativedelta(months = -1)
start1 = dd.strftime('%Y%m%d')
end1 = start0
dd = date(int(year), int(month), 1) + relativedelta(months = -2)
start2 = dd.strftime('%Y%m%d')
end2 = start1
dd = date(int(year), int(month), 1) + relativedelta(months = -3)
start3 = dd.strftime('%Y%m%d')
end3 = start2
c, p = crossanalysis.get_kpis(start0, end0, start1, end1, start2, end2, start3, end3)
wb = Workbook()
ws0 = wb.add_sheet(str(start0))
ws1 = wb.add_sheet(str(start1))
c_border = xlwt.Borders()
c_border.top = xlwt.Borders.THICK
c_border.bottom = xlwt.Borders.THICK
c_border.left = xlwt.Borders.THICK
c_border.right = xlwt.Borders.THICK
font0 = xlwt.Font()
font0.name = 'Osaka'
c_style = xlwt.XFStyle()
c_style.borders = c_border
c_style.font = font0
file_name = 'kpi_' + str(start0) + '.xls'
response = HttpResponse(mimetype='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename=%s' % file_name
header = [u"アプリ名", u"OS", u"当日無料登録率", u"当日課金率", u"１ヶ月課金率", u"おためし有料化率", u"有料２ヶ月継続率",
u"有料３ヶ月継続率", u"１日後継続率", u"７日後継続率"]
i = 0
for h in header:
ws0.write(0, i, h, c_style)
ws1.write(0, i, h, c_style)
i += 1
i = 1
for app_id, c0 in c.items():
for os_type, c1 in c0.items():
ws0.write(i, 0, app_id, c_style)
ws0.write(i, 1, os_type, c_style)
ws0.write(i, 2, c1["ft"], c_style)
ws0.write(i, 3, c1["fs"], c_style)
ws0.write(i, 4, c1["s01"], c_style)
ws0.write(i, 5, c1["t2s"], c_style)
ws0.write(i, 6, c1["t2s02"], c_style)
ws0.write(i, 7, c1["t2s03"], c_style)
ws0.write(i, 8, c1["rr_001"], c_style)
ws0.write(i, 9, c1["rr_007"], c_style)
i += 1
i = 1
for app_id, c0 in p.items():
for os_type, c1 in c0.items():
ws1.write(i, 0, app_id, c_style)
ws1.write(i, 1, os_type, c_style)
ws1.write(i, 2, c1["ft"], c_style)
ws1.write(i, 3, c1["fs"], c_style)
ws1.write(i, 4, c1["s01"], c_style)
ws1.write(i, 5, c1["t2s"], c_style)
ws1.write(i, 6, c1["t2s02"], c_style)
ws1.write(i, 7, c1["t2s03"], c_style)
ws1.write(i, 8, c1["rr_001"], c_style)
ws1.write(i, 9, c1["rr_007"], c_style)
i += 1
wb.save(response)
return response
else:
year, month = udf.pulldown_menu_yyyymm("cvr")
context = RequestContext(request, {
'user': User,
"year" : year,
"month" : month,
})
return render_to_response('servicekpi/kpicsv.html', {},
context_instance=context)