def regdatedau(request): if not request.user.is_authenticated(): return render_to_response('servicekpi/index.html', {}, context_instance=RequestContext(request)) else: kpi_id = "regdatedau" label = [u"日にち", u"当日", u"1ヶ月未満", u"2ヶ月未満", u"3ヶ月未満", u"半年未満", u"1年未満", u"1年以上", u"合計"] #タブメニューの中身を検索する app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id) #タブメニューで選択期間を取得する duration, start, end = udf.search_target(request) #選択期間のデータを取得する try: sql = "select yyyymmdd, app_id, os_type, " sql += "sum(today) as today, sum(under_1_month) as under_1_month, sum(under_2_month) as under_2_month" sql += ", sum(under_3_month) as under_3_month, sum(under_6_month) as under_6_month, sum(under_12_month) as under_12_month, sum(over_1_year) as over_1_year " sql += ' from mnl_kpi_regdatedau where ' sql += 'yyyymmdd <= ' + str(end) + ' and yyyymmdd >= ' + str(start) + ' ' sql += " and app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os sql += "' group by yyyymmdd, app_id, os_type" log = udf.get_data(sql) except: log = [] #選択期間の表を作成する data = [] head = [] l = Regdatedau(yyyymmdd = label[0], today = label[1], one_month = label[2], two_month = label[3], three_month = label[4], six_month = label[5], one_year = label[6], over_year = label[7], sum = label[8]) head.append(l) s0, s1, s2, s3, s4, s5, s6 = 0, 0, 0, 0, 0, 0, 0 for v in sorted(log, key = lambda x:x["yyyymmdd"]): s = int(v["today"]) + int(v["under_1_month"]) + int(v["under_2_month"]) + int(v["under_3_month"]) s += int(v["under_6_month"]) + int(v["under_12_month"]) + int(v["over_1_year"]) d = Regdatedau(yyyymmdd = v["yyyymmdd"], today = fmt.camma(int(v["today"])), one_month = fmt.camma(int(v["under_1_month"])), two_month = fmt.camma(int(v["under_2_month"])), three_month = fmt.camma(int(v["under_3_month"])), six_month = fmt.camma(int(v["under_6_month"])), one_year = fmt.camma(int(v["under_12_month"])), over_year = fmt.camma(int(v["over_1_year"])), sum = fmt.camma(s)) s0 += int(v["today"]) s1 += int(v["under_1_month"]) s2 += int(v["under_2_month"]) s3 += int(v["under_3_month"]) s4 += int(v["under_6_month"]) s5 += int(v["under_12_month"]) s6 += int(v["over_1_year"]) data.append(d) d = Regdatedau(yyyymmdd = "SUM", today = fmt.camma(s0), one_month = fmt.camma(s1), two_month = fmt.camma(s2), three_month = fmt.camma(s3), six_month = fmt.camma(s4), one_year = fmt.camma(s5), over_year = fmt.camma(s6), sum = fmt.camma(s0 + s1 + s2 + s3 + s4 + s5 + s6)) data.append(d) #選択期間のグラフを生成する graph_data = "[" x = [] for l in label: x.append("[" + '"' + l + '",') for v in sorted(log, key = lambda x:x["yyyymmdd"]): if int(v["yyyymmdd"]) % 100 == 1: x[0] += v["yyyymmdd"] + "," else: x[0] += "," x[1] += v["today"] + "," x[2] += v["under_1_month"] + "," x[3] += v["under_2_month"] + "," x[4] += v["under_3_month"] + "," x[5] += v["under_6_month"] + "," x[6] += v["under_12_month"] + "," x[7] += v["over_1_year"] + "," for i in range(0,8): y = x[i] x[i] = y[:-1] x[i] += "]" graph_data += x[i] + "," graph_data = graph_data[:-1] graph_data += "]" graphconfig = [] graphconfig.append(Graphconfig(graph="stacked", width = "2", data = graph_data, type = 'barWidth')) context = RequestContext(request, { 'user': User, "app_id" : app_ids, "os" : os_types, "year" : year, "month" : month, "date" : date, "duration" : duration, "label" : head, "data" : data, "graphconfig" : graphconfig, }) return render_to_response('servicekpi/regdatedau.html', {}, context_instance=context)
def rr(request): if not request.user.is_authenticated(): return render_to_response('servicekpi/index.html', {}, context_instance=RequestContext(request)) else: kpi_id = "rr" label = [u"日にち", u"1日後", u"3日後", u"7日後", u"14日後", u"30日後", u"60日後", u"90日後", u"180日後", u"360日後", u"720日後"] #タブメニューの中身を検索する app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id) #タブメニューで選択期間を取得する duration, start, end = udf.search_target(request) #選択期間のデータを取得する try: sql = "select yyyymmdd, app_id, os_type, " sql += "rr_001, rr_003, rr_007, rr_014, rr_030, rr_060, rr_090, rr_180, rr_360, rr_720 " sql += ' from mnl_kpi_rr where ' sql += 'yyyymmdd <= ' + str(end) + ' and yyyymmdd >= ' + str(start) + ' ' sql += " and app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os sql += "' group by yyyymmdd, app_id, os_type" log = udf.get_data(sql) except: log = [] #選択期間の表を作成する data = [] head = [] l = Rr(yyyymmdd = label[0], d001 = label[1], d003 = label[2], d007 = label[3], d014 = label[4], d030 = label[5], d060 = label[6], d090 = label[7], d180 = label[8], d360 = label[9], d720 = label[10]) head.append(l) for v in sorted(log, key = lambda x:x["yyyymmdd"]): d = Rr(yyyymmdd = v["yyyymmdd"], d001 = str(fmt.float1(v["rr_001"])), d003 = str(fmt.float1(v["rr_003"])), d007 = str(fmt.float1(v["rr_007"])), d014 = str(fmt.float1(v["rr_014"])), d030 = str(fmt.float1(v["rr_030"])), d060 = str(fmt.float1(v["rr_060"])), d090 = str(fmt.float1(v["rr_090"])), d180 = str(fmt.float1(v["rr_180"])), d360 = str(fmt.float1(v["rr_360"])), d720 = str(fmt.float1(v["rr_720"]))) data.append(d) #選択期間のグラフを生成する graph_data = "[" x = [] for l in label: x.append("[" + '"' + l + '",') for v in sorted(log, key = lambda x:x["yyyymmdd"]): if int(v["yyyymmdd"]) % 100 == 1: x[0] += v["yyyymmdd"] + "," else: x[0] += "," x[1] += v["rr_001"] + "," x[2] += v["rr_003"] + "," x[3] += v["rr_007"] + "," x[4] += v["rr_014"] + "," x[5] += v["rr_030"] + "," x[6] += v["rr_060"] + "," x[7] += v["rr_090"] + "," x[8] += v["rr_180"] + "," x[9] += v["rr_360"] + "," x[10] += v["rr_720"] + "," for i in range(0,11): y = x[i] x[i] = y[:-1] x[i] += "]" graph_data += x[i] + "," graph_data = graph_data[:-1] graph_data += "]" graphconfig = [] graphconfig.append(Graphconfig(graph="line", width = "1", data = graph_data, type = 'lineWidth')) context = RequestContext(request, { 'user': User, "app_id" : app_ids, "os" : os_types, "year" : year, "month" : month, "date" : date, "duration" : duration, "label" : head, "data" : data, "graphconfig" : graphconfig, }) return render_to_response('servicekpi/rr.html', {}, context_instance=context)
def firstpay(request): if not request.user.is_authenticated(): return render_to_response('servicekpi/index.html', {}, context_instance=RequestContext(request)) else: kpi_id = "firstpay" label = [u"日にち", u"NUU", u"1日以内", u"2日以内", u"3日以内", u"7日以内", u"14日以内", u"30日以内", u"30日以上"] #タブメニューの中身を検索する app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id) #タブメニューで選択期間を取得する duration, start, end = udf.search_target(request) #選択期間のデータを取得する try: sql = "select *" sql += ' from mnl_kpi_firstpay where ' sql += 'yyyymmdd <= ' + str(end) + ' and yyyymmdd >= ' + str(start) + ' ' sql += " and app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os sql += "'" log = udf.get_data(sql) except: log = [] #選択期間の表を作成する data = [] head = [] l = Firstpay(yyyymmdd = label[0], nuu = label[1], day01 = label[2], day02 = label[3], day03 = label[4], day07 = label[5], day14 = label[6], day30 = label[7], day60 = label[8]) head.append(l) for v in sorted(log, key = lambda x:x["yyyymmdd"]): d = Firstpay(yyyymmdd = v["yyyymmdd"], nuu = fmt.camma(int(v["nuu"])), day01 = v["day01"], day02 = fmt.camma(int(v["day02"])), day03 = fmt.camma(int(v["day03"])), day07 = fmt.camma(int(v["day07"])), day14 = fmt.camma(int(v["day14"])), day30 = fmt.camma(int(v["day30"])), day60 = fmt.camma(int(v["day60"]))) data.append(d) #選択期間のグラフを生成する graph_data = "[" x = [] i = 0 for l in label: if i != 1: x.append("[" + '"' + l + '",') i += 1 for v in sorted(log, key = lambda x:x["yyyymmdd"]): if int(v["yyyymmdd"]) % 100 == 1: x[0] += v["yyyymmdd"] + "," else: x[0] += "," x[1] += v["day01"] + "," x[2] += v["day02"] + "," x[3] += v["day03"] + "," x[4] += v["day07"] + "," x[5] += v["day14"] + "," x[6] += v["day30"] + "," x[7] += v["day60"] + "," length = len(x) for i in range(0,length): y = x[i] x[i] = y[:-1] x[i] += "]" graph_data += x[i] + "," graph_data = graph_data[:-1] graph_data += "]" graphconfig = [] graphconfig.append(Graphconfig(graph="line", width = "1", data = graph_data, type = 'lineWidth')) context = RequestContext(request, { 'user': User, "app_id" : app_ids, "os" : os_types, "year" : year, "month" : month, "date" : date, "duration" : duration, "label" : head, "data" : data, "graphconfig" : graphconfig, }) return render_to_response('servicekpi/firstpay.html', {}, context_instance=context)
def basickpi(request): if not request.user.is_authenticated(): return render_to_response('servicekpi/index.html', {}, context_instance=RequestContext(request)) else: kpi_id = "arpu" label = [u"日にち", u"ARPU", u"ARPPU", u"課金率"] #タブメニューの中身を検索する app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id) #タブメニューで選択期間を取得する duration, start, end = udf.search_target(request) #選択期間のデータを取得する try: sql = "select yyyymmdd, app_id, os_type, arpu, arppu, cvr" sql += ' from mnl_kpi_basickpi where ' sql += 'yyyymmdd <= ' + str(end) + ' and yyyymmdd >= ' + str(start) + ' ' sql += " and app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os sql += "'" log = udf.get_data(sql) except: log = [] #選択期間の表を作成する data = [] head = [] l = Arpu(yyyymmdd = label[0], arpu = label[1], arppu = label[2], cvr = label[3]) head.append(l) for v in sorted(log, key = lambda x:x["yyyymmdd"]): d = Arpu(yyyymmdd = v["yyyymmdd"], arpu = str(fmt.float1(v["arpu"])), arppu = str(fmt.float1(v["arppu"])), cvr = str(fmt.float1(v["cvr"]))) data.append(d) #選択期間のグラフを生成する graph_data = "[" x = [] i = 0 for l in label: if i <= 1: x.append("[" + '"' + l + '",') i += 1 for v in sorted(log, key = lambda x:x["yyyymmdd"]): if int(v["yyyymmdd"]) % 100 == 1: x[0] += v["yyyymmdd"] + "," else: x[0] += "," x[1] += v["arpu"] + "," for i in range(0,2): y = x[i] x[i] = y[:-1] x[i] += "]" graph_data += x[i] + "," graph_data = graph_data[:-1] graph_data += "]" graphconfig0 = [] graphconfig0.append(Graphconfig(graph="line", width = "1", data = graph_data, type = 'lineWidth')) graph_data = "[" x = [] i = 0 for l in label: if i == 0 or i == 2: x.append("[" + '"' + l + '",') i += 1 for v in sorted(log, key = lambda x:x["yyyymmdd"]): if int(v["yyyymmdd"]) % 100 == 1: x[0] += v["yyyymmdd"] + "," else: x[0] += "," x[1] += v["arppu"] + "," for i in range(0,2): y = x[i] x[i] = y[:-1] x[i] += "]" graph_data += x[i] + "," graph_data = graph_data[:-1] graph_data += "]" graphconfig1 = [] graphconfig1.append(Graphconfig(graph="line", width = "1", data = graph_data, type = 'lineWidth')) graph_data = "[" x = [] i = 0 for l in label: if i == 0 or i == 3: x.append("[" + '"' + l + '",') i += 1 for v in sorted(log, key = lambda x:x["yyyymmdd"]): if int(v["yyyymmdd"]) % 100 == 1: x[0] += v["yyyymmdd"] + "," else: x[0] += "," x[1] += v["cvr"] + "," for i in range(0,2): y = x[i] x[i] = y[:-1] x[i] += "]" graph_data += x[i] + "," graph_data = graph_data[:-1] graph_data += "]" graphconfig2 = [] graphconfig2.append(Graphconfig(graph="line", width = "1", data = graph_data, type = 'lineWidth')) context = RequestContext(request, { 'user': User, "app_id" : app_ids, "os" : os_types, "year" : year, "month" : month, "date" : date, "duration" : duration, "label" : head, "data" : data, "graphconfig0" : graphconfig0, "graphconfig1" : graphconfig1, "graphconfig2" : graphconfig2, }) return render_to_response('servicekpi/basickpi.html', {}, context_instance=context)
def revenuedetail(request): if not request.user.is_authenticated(): return render_to_response('servicekpi/index.html', {}, context_instance=RequestContext(request)) else: kpi_id = "logindaysdau" label = [u"日にち", u"総額", u"サブスクリプション", u"従量"] #タブメニューの中身を検索する app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id) #タブメニューで選択期間を取得する duration, start, end = udf.search_target(request) #選択期間のデータを取得する try: sql = "select yyyymmdd, app_id, os_type, " sql += "item_name, ctg_name, revenue, puu " sql += ' from mnl_kpi_revenue where ' sql += 'yyyymmdd <= ' + str(end) + ' and yyyymmdd >= ' + str(start) + ' ' sql += " and app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os sql += "'" log = udf.get_data(sql) except: log = [] #選択期間の表を作成する #データを整形する record = {} table_data = [] table = [] for v in log: record.setdefault(v["yyyymmdd"], {}) record[v["yyyymmdd"]].setdefault("総売上", 0) record[v["yyyymmdd"]].setdefault("総課金者数", 0) record[v["yyyymmdd"]].setdefault(v["item_name"] + "売上", float(v["revenue"])) record[v["yyyymmdd"]].setdefault(v["item_name"] + "課金者数", int(v["puu"])) record[v["yyyymmdd"]]["総売上"] += float(v["revenue"]) record[v["yyyymmdd"]]["総課金者数"] += int(v["puu"]) #テーブル作成用にフォーマットを変更 for d, r1 in record.items(): rec = {} rec.setdefault("yyyymmdd", d) for item_label, r2 in r1.items(): rec.setdefault(item_label, r2) table_data.append(rec) #自動配列のための処理 label_weight = {} for element in table_data: for key, value in element.items(): if key != "yyyymmdd" and key != "os_type": label_weight.setdefault(key, 0) if label_weight[key] < value: label_weight[key] = value label = ["日にち",] for k, v in sorted(label_weight.items(), key=lambda x:x[1], reverse = True): label.append(k) for v in sorted(table_data, key = lambda x:x["yyyymmdd"]): #テーブル用 row = [] for l in label: if l == "日にち": row.append(v["yyyymmdd"]) else: try: value = fmt.camma(int(v[l])) except: value = "0" row.append(value) table.append(row) doc = html_util.document(title = "", content = "", to_addrs = []) doc.add_ccchart_header() t = doc.make_table(label, table) data = [] data.append(Revenuedetail(data = t)) #選択期間のグラフを生成する graph_data = "[" x = [] for l in label: if l.find("売上") > 0 or l == "日にち": x.append("[" + '"' + l + '",') length = len(x) for k, v in sorted(record.items()): if int(k) % 100 == 1: x[0] += str(k) + "," else: x[0] += "," for i in range(0, length): find_flag = 0 for key, value in v.items(): if x[i].find(key) > 0: x[i] += str(value) + "," find_flag = 1 if find_flag == 0: x[i] += "0," for i in range(0,length): y = x[i] x[i] = y[:-1] x[i] += "]" graph_data += x[i] + "," graph_data = graph_data[:-1] graph_data += "]" graphconfig = [] graphconfig.append(Graphconfig(graph="line", width = "1", data = graph_data, type = 'lineWidth')) context = RequestContext(request, { 'user': User, "app_id" : app_ids, "os" : os_types, "year" : year, "month" : month, "date" : date, "duration" : duration, "data" : data, "graphconfig" : graphconfig, }) return render_to_response('servicekpi/revenuedetail.html', {}, context_instance=context)
def revenue(request): if not request.user.is_authenticated(): return render_to_response('servicekpi/index.html', {}, context_instance=RequestContext(request)) else: kpi_id = "logindaysdau" label = [u"日にち", u"総額", u"サブスクリプション", u"従量"] #タブメニューの中身を検索する app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id) #タブメニューで選択期間を取得する duration, start, end = udf.search_target(request) #選択期間のデータを取得する try: app = str(duration[0].app_id) + str(duration[0].os).lower() sql = "select date, name, sum(cnt) as cnt" sql += ' from analyze_summary where ' sql += "date <= '" + str(end) + "' and date >= '" + str(start) + "' " sql += " and application = '" + app + "'" + " and name like '%sales%' and country = '' group by date, name, application" log = udf.get_data(sql) except: log = [] #選択期間の表を作成する data = [] head = [] l = Revenue(yyyymmdd = label[0], all = label[1], sub = label[2], unit = label[3]) head.append(l) record = {} for v in log: record.setdefault(v["date"].replace("-",""), {}) record[v["date"].replace("-","")] record[v["date"].replace("-","")].setdefault("sub", 0) record[v["date"].replace("-","")].setdefault("all", 0) record[v["date"].replace("-","")].setdefault("pay", 0) record[v["date"].replace("-","")]["all"] += float(v["cnt"]) if v["name"] == "sales_subscription": record[v["date"].replace("-","")]["sub"] += float(v["cnt"]) else: record[v["date"].replace("-","")]["pay"] += float(v["cnt"]) for k, v in sorted(record.items()): d = Revenue(yyyymmdd = k, all= v["all"], sub = v["sub"], unit = v["pay"]) data.append(d) #選択期間のグラフを生成する graph_data = "[" x = [] for l in label: x.append("[" + '"' + l + '",') for k, v in sorted(record.items()): if int(k) % 100 == 1: x[0] += str(k) + "," else: x[0] += "," x[1] += str(v["all"]) + "," x[2] += str(v["sub"]) + "," x[3] += str(v["pay"]) + "," for i in range(0,4): y = x[i] x[i] = y[:-1] x[i] += "]" graph_data += x[i] + "," graph_data = graph_data[:-1] graph_data += "]" graphconfig = [] graphconfig.append(Graphconfig(graph="line", width = "1", data = graph_data, type = 'lineWidth')) context = RequestContext(request, { 'user': User, "app_id" : app_ids, "os" : os_types, "year" : year, "month" : month, "date" : date, "duration" : duration, "label" : head, "data" : data, "graphconfig" : graphconfig, }) return render_to_response('servicekpi/revenue.html', {}, context_instance=context)
def cvr(request): if not request.user.is_authenticated(): return render_to_response("servicekpi/index.html", {}, context_instance=RequestContext(request)) else: kpi_id = "cvr" label = [ u"日にち", u"当日無料登録率", u"無料体験登録率", u"当日課金登録率", u"1ヶ月課金率", u"2ヶ月課金率", u"3が月課金率", u"6ヶ月課金率", u"12ヶ月課金率", u"13ヶ月課金率", u"おためし有料化率", u"有料2ヶ月継続率", u"有料3ヶ月継続率", u"有料6ヶ月継続率", u"有料12ヶ月継続率", u"有料13ヶ月継続率", ] # タブメニューの中身を検索する app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id) # タブメニューで選択期間を取得する duration, start, end = udf.search_target(request) # 選択期間のデータを取得する try: sql = "select yyyymmdd, app_id, os_type, " sql += "trial, firstdaytrial, firstdaysubscription, subscription01, subscription02, subscription03, subscription06, subscription12, subscription13, trial2subscription, " sql += "trial2subscription02, trial2subscription03, trial2subscription06, trial2subscription12, trial2subscription13 " sql += " from mnl_kpi_cvr where " sql += "yyyymmdd <= " + str(end) + " and yyyymmdd >= " + str(start) + " " sql += " and app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os sql += "'" log = udf.get_data(sql) except: log = [] # 選択期間の表を作成する data = [] head = [] l = Cvr( yyyymmdd=label[0], trial_today=label[1], trial=label[2], pay_today=label[3], pay_01=label[4], pay_02=label[5], pay_03=label[6], pay_06=label[7], pay_12=label[8], pay_13=label[9], trial2pay=label[10], trial2pay02=label[11], trial2pay03=label[12], trial2pay06=label[13], trial2pay12=label[14], trial2pay13=label[15], ) head.append(l) for v in sorted(log, key=lambda x: x["yyyymmdd"]): d = Cvr( yyyymmdd=v["yyyymmdd"], trial_today=str(fmt.float1(v["firstdaytrial"])), trial=str(fmt.float1(v["trial"])), pay_today=str(fmt.float1(v["firstdaysubscription"])), pay_01=str(fmt.float1(v["subscription01"])), pay_02=str(fmt.float1(v["subscription02"])), pay_03=str(fmt.float1(v["subscription03"])), pay_06=str(fmt.float1(v["subscription06"])), pay_12=str(fmt.float1(v["subscription12"])), pay_13=str(fmt.float1(v["subscription13"])), trial2pay=str(fmt.float1(v["trial2subscription"])), trial2pay02=str(fmt.float1(v["trial2subscription02"])), trial2pay03=str(fmt.float1(v["trial2subscription03"])), trial2pay06=str(fmt.float1(v["trial2subscription06"])), trial2pay12=str(fmt.float1(v["trial2subscription12"])), trial2pay13=str(fmt.float1(v["trial2subscription13"])), ) data.append(d) # 選択期間のグラフを生成する graph_data = "[" x = [] i = 0 for l in label: if i <= 9: x.append("[" + '"' + l + '",') i += 1 for v in sorted(log, key=lambda x: x["yyyymmdd"]): if int(v["yyyymmdd"]) % 100 == 1: x[0] += v["yyyymmdd"] + "," else: x[0] += "," x[1] += v["firstdaytrial"] + "," x[2] += v["trial"] + "," x[3] += v["firstdaysubscription"] + "," x[4] += v["subscription01"] + "," x[5] += v["subscription02"] + "," x[6] += v["subscription03"] + "," x[7] += v["subscription06"] + "," x[8] += v["subscription12"] + "," x[9] += v["subscription13"] + "," length = len(x) for i in range(0, length): y = x[i] x[i] = y[:-1] x[i] += "]" graph_data += x[i] + "," graph_data = graph_data[:-1] graph_data += "]" graphconfig0 = [] graphconfig0.append(Graphconfig(graph="line", width="1", data=graph_data, type="lineWidth")) graph_data = "[" x = [] i = 0 for l in label: if i >= 10 or i == 0: x.append("[" + '"' + l + '",') i += 1 for v in sorted(log, key=lambda x: x["yyyymmdd"]): if int(v["yyyymmdd"]) % 100 == 1: x[0] += v["yyyymmdd"] + "," else: x[0] += "," x[1] += v["trial2subscription"] + "," x[2] += v["trial2subscription02"] + "," x[3] += v["trial2subscription03"] + "," x[4] += v["trial2subscription06"] + "," x[5] += v["trial2subscription12"] + "," x[6] += v["trial2subscription13"] + "," length = len(x) for i in range(0, length): y = x[i] x[i] = y[:-1] x[i] += "]" graph_data += x[i] + "," graph_data = graph_data[:-1] graph_data += "]" graphconfig1 = [] graphconfig1.append(Graphconfig(graph="line", width="1", data=graph_data, type="lineWidth")) context = RequestContext( request, { "user": User, "app_id": app_ids, "os": os_types, "year": year, "month": month, "date": date, "duration": duration, "label": head, "data": data, "graphconfig0": graphconfig0, "graphconfig1": graphconfig1, }, ) return render_to_response("servicekpi/cvr.html", {}, context_instance=context)
def logindaysdau(request): if not request.user.is_authenticated(): return render_to_response('servicekpi/index.html', {}, context_instance=RequestContext(request)) else: kpi_id = "logindaysdau" label = [u"日にち", u"1日", u"3日", u"7日", u"10日", u"14日", u"18日", u"21日", u"30日", u"合計"] #label = ["x", "y", "z", "x", "y", "z", "x", "y", "z"] #タブメニューの中身を検索する app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id) #タブメニューで選択期間を取得する duration, start, end = udf.search_target(request) #選択期間のデータを取得する try: sql = "select yyyymmdd, app_id, os_type, sum(day01) as day01, sum(day03) as day03" sql += ", sum(day07) as day07, sum(day10) as day10" sql += ", sum(day14) as day14, sum(day18) as day18" sql += ", sum(day21) as day21, sum(day30) as day30" sql += ' from mnl_kpi_logindaysdau where ' sql += 'yyyymmdd <= ' + str(end) + ' and yyyymmdd >= ' + str(start) + ' ' sql += " and app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os sql += "' group by yyyymmdd, app_id, os_type" log = udf.get_data(sql) except: log = [] #選択期間の表を作成する data = [] head = [] l = Logindaysdau(yyyymmdd = label[0], d01 = label[1], d03 = label[2], d07 = label[3], d10 = label[4], d14 = label[5], d18 = label[6], d21 = label[7], d30 = label[8], sum = label[9]) head.append(l) s0, s1, s2, s3, s4, s5, s6, s7 = 0, 0, 0, 0, 0, 0, 0, 0 for v in sorted(log, key = lambda x:x["yyyymmdd"]): s = int(v["day01"]) + int(v["day03"]) + int(v["day07"]) + int(v["day14"]) + int(v["day18"]) + int(v["day21"]) + int(v["day30"]) d = Logindaysdau(yyyymmdd = v["yyyymmdd"], d01 = fmt.camma(int(v["day01"])), d03 = fmt.camma(int(v["day03"])), d07 = fmt.camma(int(v["day07"])), d10 = fmt.camma(int(v["day10"])), d14 = fmt.camma(int(v["day14"])), d18 = fmt.camma(int(v["day18"])), d21 = fmt.camma(int(v["day21"])), d30 = fmt.camma(int(v["day30"])), sum = fmt.camma(s)) s0 += int(v["day01"]) s1 += int(v["day03"]) s2 += int(v["day07"]) s3 += int(v["day10"]) s4 += int(v["day14"]) s5 += int(v["day18"]) s6 += int(v["day21"]) s7 += int(v["day30"]) data.append(d) d = Logindaysdau(yyyymmdd = "SUM", d01 = fmt.camma(s0), d03 = fmt.camma(s1), d07 = fmt.camma(s2), d10 = fmt.camma(s3), d14 = fmt.camma(s4), d18 = fmt.camma(s5), d21 = fmt.camma(s6), d30 = fmt.camma(s7), sum = fmt.camma(s0 + s1 +s2 +s3 + s4 + s5 +s6 + s7)) data.append(d) #選択期間のグラフを生成する graph_data = "[" x = [] for l in label: x.append("[" + '"' + l + '",') for v in sorted(log, key = lambda x:x["yyyymmdd"]): if int(v["yyyymmdd"]) % 100 == 1: x[0] += v["yyyymmdd"] + "," else: x[0] += "," x[1] += v["day01"] + "," x[2] += v["day03"] + "," x[3] += v["day07"] + "," x[4] += v["day10"] + "," x[5] += v["day14"] + "," x[6] += v["day18"] + "," x[7] += v["day21"] + "," x[8] += v["day30"] + "," for i in range(0,9): y = x[i] x[i] = y[:-1] x[i] += "]" graph_data += x[i] + "," graph_data = graph_data[:-1] graph_data += "]" graphconfig = [] graphconfig.append(Graphconfig(graph="stacked", width = "2", data = graph_data, type = 'barWidth')) context = RequestContext(request, { 'user': User, "app_id" : app_ids, "os" : os_types, "year" : year, "month" : month, "date" : date, "duration" : duration, "label" : head, "data" : data, "graphconfig" : graphconfig, }) return render_to_response('servicekpi/logindaysdau.html', {}, context_instance=context)
def lastsessionuu(request): if not request.user.is_authenticated(): return render_to_response('servicekpi/index.html', {}, context_instance=RequestContext(request)) else: #わざと kpi_id = "logindaysdau" label = [u"日にち", u"新規登録UU", u"最終ログインUU"] #タブメニューの中身を検索する app_ids, os_types, year, month, date = udf.pulldown_menu(kpi_id) #タブメニューで選択期間を取得する duration, start, end = udf.search_target(request) #選択期間のデータを取得する try: sql = "select app_id, os_type, regdate, count(uid) as uu" sql += ' from mnl_kpi_lastsessionuu where ' sql += 'moddate <= ' + str(end) + ' and moddate >= ' + str(start) + ' ' sql += " and app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os sql += "' group by app_id, os_type, regdate" reg = udf.get_data(sql) sql = "select app_id, os_type, moddate, count(uid) as uu" sql += ' from mnl_kpi_lastsessionuu where ' sql += 'moddate <= ' + str(end) + ' and moddate >= ' + str(start) + ' ' sql += " and app_id = '" + duration[0].app_id + "' and os_type = '" + duration[0].os sql += "' group by app_id, os_type, moddate" log = udf.get_data(sql) except: log, reg = [], [] #選択期間の表を作成する data = [] head = [] l = Lastsessionuu(yyyymmdd = label[0], uu = label[1], nuu = label[2]) head.append(l) for v in sorted(log, key = lambda x:x["moddate"]): for vv in sorted(reg, key = lambda x:x["regdate"]): if v["moddate"] == vv["regdate"] and v["os_type"] == vv["os_type"]: d = Lastsessionuu(yyyymmdd = v["moddate"], uu = fmt.camma(int(v["uu"])), nuu = fmt.camma(int(vv["uu"]))) data.append(d) #選択期間のグラフを生成する graph_data = "[" x = [] for l in label: x.append("[" + '"' + l + '",') for v in sorted(log, key = lambda x:x["moddate"]): for vv in sorted(reg, key = lambda x:x["regdate"]): if v["moddate"] == vv["regdate"] and v["os_type"] == vv["os_type"]: if int(v["moddate"]) % 100 == 1: x[0] += v["moddate"] + "," else: x[0] += "," x[1] += vv["uu"] + "," x[2] += v["uu"] + "," for i in range(0,3): y = x[i] x[i] = y[:-1] x[i] += "]" graph_data += x[i] + "," graph_data = graph_data[:-1] graph_data += "]" graphconfig = [] graphconfig.append(Graphconfig(graph="line", width = "1", data = graph_data, type = 'lineWidth')) context = RequestContext(request, { 'user': User, "app_id" : app_ids, "os" : os_types, "year" : year, "month" : month, "date" : date, "duration" : duration, "label" : head, "data" : data, "graphconfig" : graphconfig, }) return render_to_response('servicekpi/lastsessionuu.html', {}, context_instance=context)