date_end = yyyy + "-" + str('%02d' % int(mm)) + "-" + str('%02d' % int(dd))
yyyymmdd, yyyymm, yyyy, mm, dd = date_func.get_yyyymmdd(start)
date_start = yyyy + "-" + str('%02d' % int(mm)) + "-" + str('%02d' % int(dd))
from_addr = '*****@*****.**'
to_addrs = ['*****@*****.**', '*****@*****.**']
#to_addrs = ['*****@*****.**']
list = ['kidsnbooks', 'rhythmtap', 'rhythmtapplus', 'rhythmetv', 'kdmode',
'rhythm', 'rhythmplus', 'rhythmonstage', 'goccozoo']
allinone_tab = {}
#後でメールの文章を入れる
t = "サービス詳細KPIレポート" + "(" + str(end) + ")"
c = "詳細KPIレポートです。お手数ですが添付ファイルをダウンロードの上、ご覧ください。全タイトル入っていますので重いかもしれません。"
doc = html_util.document(title = unicode(t, 'utf-8'), content = unicode(c, 'utf-8'), to_addrs = to_addrs)
doc.add_ccchart_header()
for element in list:
summary_app_name = element
title = ''
app_analyze = {}
if summary_app_name == 'kidsnbooks':
title = 'スマほん'
app_analyze = {'iOS' : 'kidsnbooks', 'Android' : 'kidsnbooksandroid'}
if summary_app_name == 'rhythmtap':
title = 'リズムタップ'
app_analyze = {'iOS' : 'rhythmtap', 'Android' : 'rhythmtapandroid'}
if summary_app_name == 'rhythmtapplus':
title = 'リズムタッププラス'
argvs = sys.argv
day01 = int(argvs[1])
day03 = date_func.ndaysago(day01, 2)
day07 = date_func.ndaysago(day01, 6)
day30 = date_func.ndaysago(day01, 29)
day60 = date_func.ndaysago(day01, 59)
day90 = date_func.ndaysago(day01, 89)
from_addr = '*****@*****.**'
#to_addrs = ['*****@*****.**', '*****@*****.**']
to_addrs = ['*****@*****.**']
html = ""
doc = html_util.document()
#売上
#継続率
#ログイン日数DAU
db = db_util.db_command("takagi", "7TgYiaI9", "inhousedb.smartsrv.in", "statistics")
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 summary_logindaysdau where '
sql += 'yyyymmdd <= ' + str(end) + ' and yyyymmdd >= ' + str(start) + ' '
sql += " and app_id = 'kidsnbooks' group by yyyymmdd, app_id, os_type"
log = db.db_anyselect(sql)
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 realtimerevenue(request):
if not request.user.is_authenticated():
return render_to_response('servicekpi/index.html', {},
context_instance=RequestContext(request))
else:
label = []
today = datetime.datetime.today()
end = today.strftime('%Y%m%d')
start = today.strftime('%Y%m') + "01"
#選択期間のデータを取得する
try:
sql = "select yyyymmdd, app_id, os_type, "
sql += "sum(revenue) as revenue"
sql += ' from mnl_kpi_revenue where '
sql += 'yyyymmdd <= ' + str(end) + ' and yyyymmdd >= ' + str(start) + ' '
sql += " group by yyyymmdd, app_id, os_type"
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(v["app_id"] +"(" + v["os_type"] + ")", int(float(v["revenue"])))
record[v["yyyymmdd"]]["総売上"] += int(float(v["revenue"]))
#テーブル作成用にフォーマットを変更
header = {}
for d, r1 in record.items():
rec = {}
rec.setdefault("yyyymmdd", d)
header.setdefault("yyyymmdd", "ALL")
for item_label, r2 in r1.items():
rec.setdefault(item_label, r2)
header.setdefault(item_label, 0)
header[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)
label_weight[key] += value
label = ["日にち",]
for k, v in sorted(label_weight.items(), key=lambda x:x[1], reverse = True):
label.append(k)
#テーブル用
row = []
for l in label:
if l == "日にち":
row.append(header["yyyymmdd"])
else:
try:
value = fmt.camma(int(header[l]))
except:
value = "0"
row.append(value)
table.append(row)
for v in sorted(table_data, key = lambda x:x["yyyymmdd"], reverse = True):
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))
context = RequestContext(request, {
'user': User,
"data" : data,
})
return render_to_response('servicekpi/realtimerevenue.html', {},
context_instance=context)
