コード例 #1
0
ファイル: growthrate.py プロジェクト: ytworks/private
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)
コード例 #2
0
ファイル: kpicsv.py プロジェクト: ytworks/private
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"1ヶ月課金率", u"おためし有料化率", u"有料2ヶ月継続率",
                      u"有料3ヶ月継続率", u"1日後継続率", u"7日後継続率"]
            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)