<reponame>Data-to-Knowledge/GroundwaterQuantity
# -*- coding: utf-8 -*-
"""
Created on Tue Apr  9 10:44:36 2019

@author: michaelek
"""
import os
import pandas as pd
from pdsql import mssql

pd.options.display.max_columns = 10

############################################
### Parameters

server = 'edwprod01'
database = 'hydro'
sites_table = 'ExternalSite'
lf_sites_table = 'LowFlowRestrSite'

sites_cols = ['ExtSiteID', 'NZTMX', 'NZTMY', 'CwmsName']
lf_cols = ['site', 'date', 'restr_category']

from_date = '2018-01-01'
to_date = '2018-02-28'

py_path = os.path.realpath(os.path.dirname(__file__))
lf_cat_csv = 'lowflow_categories.csv'
lf_cwms_csv = 'lowflow_cwms_summ.csv'


############################################
### Process data

lf1 = mssql.rd_sql(server, database, lf_sites_table, lf_cols, where_in={'site_type': ['LowFlow']}, from_date=from_date, to_date=to_date, date_col='date')

lf1.loc[lf1['restr_category'] == 'Deactivated', 'restr_category'] = 'No'
lf1.loc[lf1['restr_category'] == 'Full', 'restr_category'] = 'Partial'

sites1 = mssql.rd_sql(server, database, sites_table, sites_cols, where_in={'ExtSiteID': lf1.site.unique().tolist()})
sites1.rename(columns={'ExtSiteID': 'site'}, inplace=True)

count1 = lf1.groupby('site').date.count().max()
lf2 = lf1.groupby('site').restr_category.value_counts()

lf3 = lf2.unstack(1)

lf3['category'] = 'Some Days on Restriction'
lf3.loc[lf3['No'] == count1, 'category'] = 'Not on Restriction'
lf3.loc[lf3['Partial'] == count1, 'category'] = 'All Days on Restriction'

lf4 = lf3.drop(['No', 'Partial'], axis=1).reset_index().copy()

both1 = pd.merge(lf4, sites1, on='site')

cwms1 = both1.groupby('CwmsName').category.value_counts().unstack(1)
cwms1[cwms1.isnull()] = 0
cwms1 = cwms1.astype(int)

both1.to_csv(os.path.join(py_dir, lf_cat_csv), index=False)
cwms1.to_csv(os.path.join(py_dir, lf_cwms_csv))


# -*- coding: utf-8 -*-
"""
Created on Tue Apr  9 10:44:36 2019

@author: michaelek
"""
import os
import pandas as pd
from pdsql import mssql

pd.options.display.max_columns = 10

############################################
### Parameters

server = 'edwprod01'
database = 'hydro'
sites_table = 'ExternalSite'
lf_sites_table = 'LowFlowRestrSite'

sites_cols = ['ExtSiteID', 'NZTMX', 'NZTMY', 'CwmsName']
lf_cols = ['site', 'date', 'restr_category']

from_date = '2018-01-01'
to_date = '2018-02-28'

py_path = os.path.realpath(os.path.dirname(__file__))
lf_cat_csv = 'lowflow_categories.csv'
lf_cwms_csv = 'lowflow_cwms_summ.csv'


############################################
### Process data

lf1 = mssql.rd_sql(server, database, lf_sites_table, lf_cols, where_in={'site_type': ['LowFlow']}, from_date=from_date, to_date=to_date, date_col='date')

lf1.loc[lf1['restr_category'] == 'Deactivated', 'restr_category'] = 'No'
lf1.loc[lf1['restr_category'] == 'Full', 'restr_category'] = 'Partial'

sites1 = mssql.rd_sql(server, database, sites_table, sites_cols, where_in={'ExtSiteID': lf1.site.unique().tolist()})
sites1.rename(columns={'ExtSiteID': 'site'}, inplace=True)

count1 = lf1.groupby('site').date.count().max()
lf2 = lf1.groupby('site').restr_category.value_counts()

lf3 = lf2.unstack(1)

lf3['category'] = 'Some Days on Restriction'
lf3.loc[lf3['No'] == count1, 'category'] = 'Not on Restriction'
lf3.loc[lf3['Partial'] == count1, 'category'] = 'All Days on Restriction'

lf4 = lf3.drop(['No', 'Partial'], axis=1).reset_index().copy()

both1 = pd.merge(lf4, sites1, on='site')

cwms1 = both1.groupby('CwmsName').category.value_counts().unstack(1)
cwms1[cwms1.isnull()] = 0
cwms1 = cwms1.astype(int)

both1.to_csv(os.path.join(py_dir, lf_cat_csv), index=False)
cwms1.to_csv(os.path.join(py_dir, lf_cwms_csv))
# -*- coding: utf-8 -*- Created on Tue Apr  9 10:44:36 2019  @author: michaelek ############################################ ### Parameters ############################################ ### Process data