#!/usr/bin/python
# -*- coding: utf-8 -*-
# 必要なライブラリ
import pandas as pd
pd.set_option('display.max_columns', 99999)
pd.set_option('display.max_rows', 500)
import pandas as pd
from pandas import Series, DataFrame
import datetime
from datetime import timedelta
import worldmesh
from geojson import Point, Feature, FeatureCollection, dump

import cgi
import sqlite3
import sys
import json

import pyproj


# Webページ情報取得
data = sys.stdin.read()
params = json.loads(data)


### P1,2画面左　マップの量の算出
def area_daily_mean(df):
    # エリアごと年月日ごとに重み別のUU数をカウント
    df = df.groupby(area + ['yyyymmdd', 'value'], as_index=False)['id'].nunique()
    # 重みとUU数を掛けて重み別の推計人数とする
    df['cnt'] = df['value'] * df['id']
    # 重み別の推計人数を足し合わせて最終的な推計人数とする
    df = df.groupby(area, as_index=False)['cnt'].sum()
    # 日平均にする
    df['cnt'] = df['cnt'] / nod
    return df

### P1,2,3画面左　日別量の算出
def daily_sum(df, grouping_list):
    # 定したgrouping_listごとに重み別のUU数をカウント
    df = df.groupby(grouping_list, as_index=False)['id'].nunique()
    # 重みとUU数を掛けて重み別の推計人数とする
    df['cnt'] = df['value'] * df['id']
    # 重み別の推計人数を足し合わせて最終的な推計人数とする
    df = df.groupby(['yyyymmdd'], as_index=False)['cnt'].sum()
    return df

### P1,2,3画面左　時間帯別量の算出
def hour_mean(df, grouping_list):
    # 指定したgrouping_listごとに重み別のUU数をカウント
    df = df.groupby(grouping_list, as_index=False)['id'].nunique()
    # 重みとUU数を掛けて重み別の推計人数とする
    df['cnt'] = df['value'] * df['id']
    # 重み別の推計人数を足し合わせて最終的な推計人数とする
    df = df.groupby(['hour'], as_index=False)['cnt'].sum()
    # 日平均にする
    df['cnt'] = df['cnt'] / nod
    return df

# P1,3画面右　曜日別分析　年月日別合計量の算出
def holiday_daily_sum(df, grouping_list):
    # 指定したgrouping_listごとに重み別のUU数をカウント
    df = df.groupby(grouping_list, as_index=False)['id'].nunique()
    # 重みとUU数を掛けて重み別の推計人数とする
    df['cnt'] = df['value'] * df['id']
    # 重み別の推計人数を足し合わせて最終的な推計人数とする
    df = df.groupby(['yyyymmdd', 'holiday'], as_index=False)['cnt'].sum()
    return df

# P1,3画面右　曜日別分析　曜日平均量の算出
def holiday_mean(df):
    df = df.groupby(['holiday'], as_index=False)['cnt'].sum()
    # 日平均にする
    df.loc[df['holiday']=='平日', 'cnt'] = df['cnt'] / nod_week
    df.loc[df['holiday']=='休日', 'cnt'] = df['cnt'] / nod_holi
    return df

# P1,3画面右　曜日別分析　時間帯別平均量算の出用
def holiday_hour_mean(df, grouping_list):
    # 指定したgrouping_listごとに重み別のUU数をカウント
    df = df.groupby(grouping_list, as_index=False)['id'].nunique()
    # 重みとUU数を掛けて重み別の推計人数とする
    df['cnt'] = df['value'] * df['id']
    # 重み別の推計人数を足し合わせて最終的な推計人数とする
    df = df.groupby(['holiday', 'hour'], as_index=False)['cnt'].sum()
    # 日平均にする
    df.loc[df['holiday']=='平日', 'cnt'] = df['cnt'] / nod_week
    df.loc[df['holiday']=='休日', 'cnt'] = df['cnt'] / nod_holi
    return df

# P1,3画面右　属性別分析　年月日別合計量の算出
def attr_daily_sum(df, selected_attr, grouping_list):
    grouping_list.append(selected_attr)
    # 指定したgrouping_listごとに重み別のUU数をカウント
    df = df.groupby(grouping_list, as_index=False)['id'].nunique()
    # 重みとUU数を掛けて重み別の推計人数とする
    df['cnt'] = df['value'] * df['id']
    # 重み別の推計人数を足し合わせて最終的な推計人数とする
    df = df.groupby(['yyyymmdd', selected_attr], as_index=False)['cnt'].sum()
    return df
    
# P1,3画面右　属性別分析　属性別平均量の算出
def attr_mean(df, selected_attr):
    df = df.groupby([selected_attr], as_index=False)['cnt'].sum()
    # 日平均にする
    df['cnt'] = df['cnt'] / nod
    return df

# P1,3画面右　属性別分析　時時間帯別平均○○量算出用関数
def attr_hour_mean(df, selected_attr, grouping_list):
    grouping_list.append(selected_attr)
    # 指定したgrouping_listごとに重み別のUU数をカウント
    df = df.groupby(grouping_list, as_index=False)['id'].nunique()
    # 重みとUU数を掛けて重み別の推計人数とする
    df['cnt'] = df['value'] * df['id']
    # 重み別の推計人数を足し合わせて最終的な推計人数とする
    df = df.groupby([selected_attr, 'hour'], as_index=False)['cnt'].sum()
    # 日平均にする
    df['cnt'] = df['cnt'] / nod
    return df

# P2画面右 滞在時間表の算出
def stay_minutes_matrix(df):
    df_all = df.drop_duplicates(subset=['timestamp', 'id']).copy()
    df = df_all[['timestamp', 'date', 'id', 'area']].reset_index(drop=True).sort_values('timestamp')
    df['prev_timestamp'] = df.groupby(['date', 'id'], as_index=False)['timestamp'].shift(1)
    df['prev_area']     = df.groupby(['date', 'id'], as_index=False)['area'].shift(1)
    # エリア別滞在時間
    ### 連続滞在時間
    df['time_diff'] = df['timestamp'] - df['prev_timestamp']
    df['dummy'] = 1
    df.loc[((df['prev_area']==df['area'])&(df['time_diff']<=timedelta(minutes=30))), 'dummy'] = 0
    df['dumsum'] = df.groupby(['date', 'id'])['dummy'].cumsum()
    df1 = df[(df['prev_area']==df['area'])&(df['time_diff']<=timedelta(minutes=30))].copy()
    df1['area_con_stay_minutes'] = round(df1.groupby(['id', 'date', 'dumsum'])['time_diff'].cumsum().dt.seconds/60, 1)
    df1 = pd.merge(df1.groupby(['id', 'date', 'dumsum'], as_index=False)['prev_timestamp'].min().rename(columns={'prev_timestamp': 'timestamp'}), df1.groupby(['id', 'date', 'dumsum'], as_index=False)['area_con_stay_minutes'].max(), on=['id', 'date', 'dumsum'])
    df = pd.merge(df, df1[['id', 'timestamp', 'area_con_stay_minutes']], on=['id', 'timestamp'], how='left')
    df.drop(columns=['time_diff', 'dummy', 'dumsum'], inplace=True)
    df['area_con_stay_minutes_bin'] = pd.cut(df['area_con_stay_minutes'], [0, 2, 5, 15, 30, 60, 120, 1440], labels=['2分未満', '2~5分', '5~15分', '15~30分', '30~60分', '60~120分', '120分以上'], right=False)
    ### 1日内での滞在時間
    df = pd.merge(df, df.groupby(['id', 'date', 'area'], as_index=False)['area_con_stay_minutes'].sum().rename(columns={'area_con_stay_minutes': 'area_daily_stay_minutes'}), on=['id', 'date', 'area'], how='left')
    df['area_daily_stay_minutes_bin'] = pd.cut(df['area_daily_stay_minutes'], [0, 2, 5, 15, 30, 60, 120, 1440], labels=['2分未満', '2~5分', '5~15分', '15~30分', '30~60分', '60~120分', '120分以上'], right=False)
    df_all = pd.merge(df_all, df[['timestamp', 'id', 'area_con_stay_minutes', 'area_con_stay_minutes_bin', 'area_daily_stay_minutes', 'area_daily_stay_minutes_bin']], on=['timestamp', 'id'], how='left')
    df = df_all.copy()
    df['area_daily_stay_minutes_bin'] = df['area_daily_stay_minutes_bin'].astype(str)
    df = df.groupby(['area', 'yyyymmdd', 'area_daily_stay_minutes_bin', 'value'], as_index=False)['id'].nunique()
    df['cnt'] = df['value'] * df['id']
    df = df.groupby(['area', 'area_daily_stay_minutes_bin'], as_index=False)['cnt'].sum()
    return df




df = pd.read_csv('testdata_3.csv', dtype = 'object')
# 属性カラムを判断し左からattr1, attr2, .. と命名
attr_columns = df.columns.drop(['id', 'timestamp', 'area', 'area_lat', 'area_lng', 'value'])
attrs = [] # attr1, attr2, ...→この後の処理に使用 
attrs_name = [] #  csv内のもとの名前→HTML内のフィルターの題名や、属性分析の属性選択リストに使用
for i, column in enumerate(attr_columns):
    df.rename(columns={column: f'attr{i+1}'}, inplace=True)
    attrs.append(f'attr{i+1}')
    attrs_name.append(column)
# 型指定（attr情報はobjectのまま）
df['id'] = df['id'].astype(str)
df['timestamp'] = pd.to_datetime(df['timestamp'])
df['area'] = df['area'].astype(str)
df['area_lat'] = df['area_lat'].astype(float)
df['area_lng'] = df['area_lng'].astype(float)
df['value'] = df['value'].astype(float)
# 時間を持たせる
df['year'] = df['timestamp'].dt.year
df['month'] = df['timestamp'].dt.month
df['date']  = df['timestamp'].dt.day
df['hour']  = df['timestamp'].dt.hour
df['dow'] = df['timestamp'].dt.strftime("%a")
# 平日・休日を作成、祝日は考慮しない(不変でないため)
df.loc[df['dow'].isin(['Mon', 'Tue', 'Wed', 'Thu', 'Fri']), 'holiday'] = '平日'
df.loc[df['dow'].isin(['Sat', 'Sun']), 'holiday'] = '休日'
df['yyyymmdd'] = df['timestamp'].dt.date
df_all = df.copy()

# フィルターを反映 ※ここでは選択された項目は例です。実装ではフィルター欄で選択された項目をリストとして入れてください。
df = df_all.copy()
'''
selected_year = [2019]
selected_month = [7]
selected_date = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14]
selected_hour = [10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21]
selected_holiday = ['平日', '休日']
selected_area = ['上野公園', '蔵前']
selected_attr1 = ['男性', '女性']
selected_attr2 = ['30', '40', '50', '60']
selected_attr3 = ['すべて']
selected_attr4 = ['すべて']
selected_attr5 = ['すべて']
'''

selected_year = ['']
selected_month = ['']
selected_date = ['']
selected_hour = ['']
selected_dow = ['']
selected_holiday = ['']
selected_area = ['']
selected_attr1 = ['']
selected_attr2 = ['']
selected_attr3 = ['']
selected_attr4 = ['']
selected_attr5 = ['']

if len(params['year']) != 0:
    selected_year =  [int(i) for i in params['year']]
if len(params['month']) != 0:
    selected_month =  [int(i) for i in params['month']]
if len(params['date']) != 0:
    selected_date =  [int(i) for i in params['date']]
if len(params['time']) != 0:
    selected_hour =  [int(i) for i in params['time']]
if len(params['yobi']) != 0:
    selected_dow =  params['yobi']
if len(params['holyday']) != 0:
    selected_holiday = params['holyday']
if len(params['area']) != 0:
    selected_area = params['area']
if len(params['attr1']) != 0:
    selected_attr1 = params['attr1']
if len(params['attr2']) != 0:
    selected_attr2 = params['attr2']
if len(params['attr3']) != 0:
    selected_attr3 = params['attr3']
if len(params['attr4']) != 0:
    selected_attr4 = params['attr4']
if len(params['attr5']) != 0:
    selected_attr5 = params['attr5']

if params['attr'] is not None :
    if len(params['attr']) != 0:
        selected_attr = params['attr']
else :
    selected_attr = ""

if selected_year != ['すべて']:
    df = df[df['year'].isin(selected_year)]
if selected_month != ['すべて']:
    df = df[df['month'].isin(selected_month)]
if selected_date != ['すべて']:
    df = df[df['date'].isin(selected_date)]
if selected_hour != ['すべて']:
    df = df[df['hour'].isin(selected_hour)]
if selected_holiday != ['すべて']:
    df = df[df['holiday'].isin(selected_holiday)]
if selected_area != ['すべて']:
    df = df[df['area'].isin(selected_area)]
if 'selected_attr1' in locals():
    if selected_attr1 != ['すべて']:
        df = df[df['attr1'].isin(selected_attr1)]
if 'selected_attr2' in locals():
    if selected_attr2 != ['すべて']:
        df = df[df['attr2'].isin(selected_attr2)]
if 'selected_attr3' in locals():
    if selected_attr3 != ['すべて']:
        df = df[df['attr3'].isin(selected_attr3)]
if 'selected_attr4' in locals():
    if selected_attr4 != ['すべて']:
        df = df[df['attr4'].isin(selected_attr4)]
if 'selected_attr5' in locals():
    if selected_attr5 != ['すべて']:
        df = df[df['attr5'].isin(selected_attr5)]
df_selected = df.copy().reset_index(drop=True)

if len(df_selected) == 0:
    result = {}
    print("Content-type: application/json")
    print("\n\n")
    print(json.dumps(result))

    print('\n')
    exit()
    
# フィルター後の日数を持っておく
nod = df_selected['yyyymmdd'].nunique()
nod_week = df_selected[df_selected['holiday']=='平日']['yyyymmdd'].nunique()
nod_holi = df_selected[df_selected['holiday']=='休日']['yyyymmdd'].nunique()
# 冗長になるのでorigin情報とdestination情報をまとめたリストを持っておく
area = ['area', 'area_lat', 'area_lng']

####### P1,2共通の画面左側 #######

### マップ用のエリア別平均推計人数(人/日) df_a
df_a = area_daily_mean(df_selected)
### 年月日別合計 df_b
df_b = daily_sum(df_selected, ['yyyymmdd', 'value'])
### 時間帯別平均 df_c
df_c = hour_mean(df_selected, ['yyyymmdd', 'hour', 'value'])

####### P1右側 曜日別分析 #######

# 年月日別合計 df_d
df_d = holiday_daily_sum(df_selected, ['yyyymmdd', 'holiday', 'value'])
# 曜日別平均 df_e
df_e = holiday_mean(df_d)
# 時間帯別平均 df_f
df_f = holiday_hour_mean(df_selected, ['yyyymmdd', 'holiday', 'hour', 'value'])

####### P1右側 属性別分析 #######
# ※ 「集計対象」で選択された属性が「attr1」の場合の例です。
for i, column in enumerate(attr_columns):
    df_selected.rename(columns={f'attr{i+1}' : column }, inplace=True)

if selected_attr != "" :
    # 年月日別合計 df_g
    df_g = attr_daily_sum(df_selected, selected_attr, ['yyyymmdd', 'value'])
    # 属性別平均 df_h
    df_h = attr_mean(df_g, selected_attr)
    # 時間帯別平均 df_i
    df_i = attr_hour_mean(df_selected, selected_attr, ['yyyymmdd', 'hour', 'value'])

####### P2右側 滞在時間表 #######
# 滞在時間表 df_j
df_j = stay_minutes_matrix(df_selected)

geojson_data = df_a.to_dict(orient='records')

# Prepare Geojson FeatureCollection
ft_all = []
ft_all_2 = []

geo_json_key = 0
for p in geojson_data:
    geo_json_key = geo_json_key + 1
    lon, lat = float(p["area_lng"]), float(p["area_lat"])
    ft = Feature(geometry = Point((lon, lat,)),
        properties = {'key':geo_json_key,'name2': p['area'],'name': "{:.2f}".format(float( p['cnt']))})
    ft_all.append(ft)

ft_colct = FeatureCollection(ft_all)

with open('outfile3.geojson', 'w') as f:
    dump(ft_colct, f, indent=2)


df_json_list = [*df_all.groupby(area).groups]
i = 0
area_id_list = []
for area_i in df_json_list :
    i = i + 1
    ft_2 = Feature(geometry = Point((float(area_i[2]), float(area_i[1]), )),
                 properties = {'name': area_i[0]},id = i)
    area_id = [i,area_i[0]]
    area_id_list.append(area_id)
    ft_all_2.append(ft_2)

ft_colct_2 = FeatureCollection(ft_all_2)

with open('outfile3_point.geojson', 'w') as f:
    dump(ft_colct_2, f, indent=2)


if selected_attr != "" :
    df_g.rename(columns={selected_attr : 'attr1'}, inplace=True)
    df_h.rename(columns={selected_attr : 'attr1'}, inplace=True)
    df_i.rename(columns={selected_attr : 'attr1'}, inplace=True)

df_d_m = df_d.pivot_table(values=['cnt'], index=['yyyymmdd'], columns=['holiday'], aggfunc='sum',fill_value=0)
df_d_m.columns = [cols[1] for cols in df_d_m.columns]
df_d_m = df_d_m.reset_index()
if not '平日' in df_d_m.columns:
    df_d_m['平日'] = 0
if not '休日' in df_d_m.columns:
    df_d_m['休日'] = 0

if selected_attr != "" :
    df_g_m = df_g.pivot_table(values=['cnt'], index=['yyyymmdd'], columns=['attr1'], aggfunc='sum',fill_value=0,margins=True)
    df_g_m = df_g_m.iloc[:-1]
    df_g_m.columns = [cols[1] for cols in df_g_m.columns]
    df_g_m = df_g_m.reset_index()

df_j_m = df_j.pivot_table(values=['cnt'], index=['area'], columns=['area_daily_stay_minutes_bin'], aggfunc='sum',fill_value=0)
df_j_m.columns = [cols[1] for cols in df_j_m.columns]
df_j_m = df_j_m.reset_index()

collist = ['2分未満', '2~5分', '5~15分', '15~30分', '30~60分', '60~120分', '120分以上']
n_collist = ['area']
for col in collist:
    if col in df_j_m.columns :
        n_collist.append(col)

df_j_m = df_j_m.reindex(columns=n_collist)

df_j_m_2 = df_j_m.to_dict(orient='records')

html_cross1 = '<table  class="od-table"><tbody><tr><th class="od_title"></th>'
for n in df_j_m.columns:
    if str(n) == 'area':
        continue
    html_cross1 += '<th class="destination">' + str(n) + '</th>'
html_cross1 += '</tr>'

for df_list in df_j_m_2:
    html_cross1 += '<tr>'
    origin_area_name = ''
    for n in df_j_m.columns: 
        value = df_list[n]
        if str(n) == 'area':
            html_cross1 += '<td>' + value + '</td>'
            origin_area_name = value
        elif str(n) == origin_area_name :
            html_cross1 += '<td>-</td>'
        else:    
            html_cross1 += '<td>' + "{:,.3f}".format(float(value)) + '</td>'
    html_cross1 += '</tr>'

html_cross1 += '</tbody></table>'

result = {}
df_b['yyyymmdd'] = df_b['yyyymmdd'].astype(str)
df_d_m['yyyymmdd'] = df_d_m['yyyymmdd'].astype(str)
if selected_attr != "" :
    df_g_m['yyyymmdd'] = df_g_m['yyyymmdd'].astype(str)

result['df_a'] = df_a.to_dict(orient='records')
result['df_b'] = df_b.to_dict(orient='records')
result['df_c'] = df_c.to_dict(orient='records')
result['df_d'] = df_d_m.to_dict(orient='records')
result['df_e'] = df_e.to_dict(orient='records')
result['df_f'] = df_f.to_dict(orient='records')
if selected_attr != "" :
    result['df_g'] = df_g_m.to_dict(orient='records')
    result['df_h'] = df_h.sort_values('cnt', ascending=False).to_dict(orient='records')
    result['df_i'] = df_i.to_dict(orient='records')
else :
    result['df_g'] = []
    result['df_h'] = []
    result['df_i'] = []
result['df_j'] = df_j_m.to_dict(orient='records')
result['cross'] = html_cross1
result['pm'] = params
df_selected['yyyymmdd'] = df_selected['yyyymmdd'].astype(str)
df_selected2 = df_selected.drop(columns='timestamp')
result['df_selected'] = df_selected2.to_dict(orient='records')
result['area_id_list'] = area_id_list

print("Content-type: application/json")
print("\n\n")
print(json.dumps(result))

print('\n')