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)