from we_module.we import We
we = We(True)
import csv
import re
import pandas
import datetime
import time
import numpy
import os
# contract sync error file from running log_vs_contract.py file
with open('unique_contracts.csv', 'r') as contracts:
error_contracts = 0
for row in contracts:
error_contracts = error_contracts +1
# SQL find all agreements on time period
memberships = '''
SELECT distinct count(uuid)
from spaceman_public.membership_agreements ma
where ma.created_at > '2018-02-28'
and ma.created_at < '2018-04-01'
'''
ma_ag = we.get_tbl_query(memberships)
print('contracts created during this time:', ma_ag)
print('sync errors during this time:', error_contracts)
print('ratio:', error_contracts / ma_ag *100, '%')
'sortkey': 'stargate_market'
}
}
]}
return config
def main(we, **kwargs):
ss_sync = we.get_tbl_query("""
select market.name,territory.name,region.name
from stargate_bi_tables.bi_market as market
left join stargate_bi_tables.bi_territory as territory on market.territory_uuid=territory.uuid
left join stargate_bi_tables.bi_region as region on territory.region_id=region.id
LIMIT 10;
""")
data = we.get_google_sheet(GS_KEY, 'test')
#print(ss_sync)
print(data)
#ss_sync.to_csv('test.csv')
we.put_google_data(ss_sync, GS_KEY, 'First-try', delete_existing=False)
GS_KEY = '1DaGjCaYpD8lqagmsuwTkODZlvgD1353u--Q0BWgpqQM'
we = We(True)
config = config()
print(config['schedule'][0]['input_json']['key'])
main(we, kwargs=config)
from we_module.we import We
import queries
we = We(True)
import pandas as pd
import csv
# Variables:
start_date = '2018-08-01'
end_date = '2018-09-01' # Not inclusive
time_period = 'Day'
output_file_destination = f'./Reports/looker_output{start_date}to{end_date}.csv'
reuse_file_destination = f'./Reports/looker_reuse{start_date}to{end_date}.csv'
fulloutput_file_destination = f'./Reports/looker_fulloutput{start_date}to{end_date}.csv'
printoutput_file_destination = f'./Reports/looker_printoutput{start_date}to{end_date}.txt'
# Get query results as pandas dfs
looker_df = we.get_tbl_query(
queries.create_looker_query(time_period, start_date, end_date))
cw_df = we.get_tbl_query(
queries.create_salesforce_closedwon_query(time_period, start_date,
end_date))
cl_df = we.get_tbl_query(
queries.create_salesforce_closedlost_query(time_period, start_date,
end_date))
re_df = we.get_tbl_query(
queries.create_sapi_reuserecords_query(time_period, start_date, end_date))
sm_df = we.get_tbl_query(
queries.create_spaceman_r_cr_ma_query(time_period, start_date, end_date))
org_df = we.get_tbl_query(
queries.create_orgs_from_billing(start_date, end_date))
from we_module.we import We
import queries
we = We(True)
import pandas as pd
import csv
import label_sync_issue as lsi
from dateutil.relativedelta import relativedelta as rd
from datetime import datetime as dt
# Variables:
start_date = '2018-07-01'
end_date = '2018-08-01'# Not inclusive
start_date_nextmonth = str((dt.strptime(start_date, "%Y-%m-%d") + rd(months=+1)).date())
end_date_nextmonth = str((dt.strptime(end_date, "%Y-%m-%d") + rd(months=+1)).date())
output_file_destination = f'./Reports/transaction_output{start_date}to{end_date}.csv'
reuse_file_destination = f'./Reports/transaction_reuse{start_date}to{end_date}.csv'
fulloutput_file_destination = f'./Reports/transaction_fulloutput{start_date}to{end_date}.csv'
printoutput_file_destination = f'./Reports/transaction_printoutput{start_date}to{end_date}.txt'
# Get query results as pandas dfs
vtrans_df = we.get_tbl_query(queries.create_vtrans_query_notrunc(start_date, end_date))
cw_df = we.get_tbl_query(queries.create_salesforce_closedwon_query_notrunc(start_date, end_date))
cl_df = we.get_tbl_query(queries.create_salesforce_closedlost_query_notrunc(start_date, end_date))
cl_nextmonth_df = we.get_tbl_query(f'''select o.reservation_uuid_c, o.contract_uuid_c, o.total_desks_reserved_net_c, o.close_date
from salesforce_v2.opportunity o
where o.close_date >= TIMESTAMP '{start_date_nextmonth}' and o.close_date < TIMESTAMP '{end_date_nextmonth}' and o.total_desks_reserved_net_c < 0
''')
re_df = we.get_tbl_query(queries.create_sapi_reuserecords_query_notrunc(start_date, end_date))
# Merge closed won and closed lost tables
sf_df = cl_df.merge(cw_df, left_on=['contract_uuid_c'], right_on=['contract_uuid_c'], how='outer')
import os
from we_module.we import We
import pandas as pd
pd.set_option('display.max_columns', 100)
pd.set_option('display.width', 1000)
pd.set_option('display.max_colwidth', 1000)
rs_conn = 'postgresql://[email protected]:Luye@[email protected]:5439/analyticdb'
os.environ['REDSHIFT_CONN'] = rs_conn
we = We()
"""
segment_sql = '''
select * from playground.page_segment t
where t.timestamp>=date'2019-06-08'
and t.timestamp<date'2019-06-09'
order by t.timestamp
'''
heap_sql = '''
select * from playground.page_heap t
where t.time>=date'2019-06-08'
and t.time<date'2019-06-09'
order by t.time
'''
df_seg = we.get_tbl_query(segment_sql)
df_seg.index = df_seg['id']
df_seg = df_seg.drop(columns=['id'])
df_heap = we.get_tbl_query(heap_sql)
df_heap.index = df_heap['event_id']
#!/usr/bin/env python
from we_module.we import We
we = We(True)
import csv
import re
import pandas
import datetime
import time
import numpy
import os
# test commit message
# opportunities table
opportunities = '''
SELECT o.created_date, o.contract_signed_on_c, o.contract__sent__date___c, o.id, o.account_id, o.name, o.owner_id, a.uuid___c, o.contract_uuid_c
from salesforce._opportunity o
join salesforce._account a on a.id = o.account_id
where o.contract_signed_on_c >= '2018-04-01'
and o.contract_signed_on_c <= '2018-04-30'
'''
opp = we.get_tbl_query(opportunities)
opp.to_csv('opportunities_output.csv')
#memberships table
memberships = '''
SELECT *
from spaceman_public.membership_agreements ma
where ma.signed_at >= '2018-04-01'
and ma.signed_at <= '2018-04-30'
from we_module.we import We
import queries
we = We(True)
import pandas as pd
import csv
# Variables:
start_date = '2018-08-01'
end_date = '2018-09-01' # Not inclusive
time_period = 'Day'
output_file_destination = f'./Reports/looker_output{start_date}to{end_date}.csv'
reuse_file_destination = f'./Reports/looker_reuse{start_date}to{end_date}.csv'
fulloutput_file_destination = f'./Reports/looker_fulloutput{start_date}to{end_date}.csv'
printoutput_file_destination = f'./Reports/looker_printoutput{start_date}to{end_date}.txt'
test = f'./Reports/test{start_date}to{end_date}.csv'
# Get query results as pandas dfs
#looker_df = we.get_tbl_query(queries.create_looker_query(time_period, start_date, end_date))
cw_df = we.get_tbl_query(
queries_sf.create_salesforce_closed_won_query(time_period, start_date,
end_date))
#cl_df = we.get_tbl_query(queries.create_salesforce_closedlost_query(time_period, start_date, end_date))
#re_df = we.get_tbl_query(queries.create_sapi_reuserecords_query(time_period, start_date, end_date))
#sm_df = we.get_tbl_query(queries.create_spaceman_r_cr_ma_query(time_period, start_date, end_date))
#org_df = we.get_tbl_query(queries.create_orgs_from_billing(start_date, end_date))
cw_df.to_csv(test, encoding='utf-8', index=False)