import pandas as pd
import pyodbc
import numpy as np
from datetime import datetime as dt


# for developmnet phase only
try:
    crsr.close()
except:
    pass


# we want to execute the temp table
conn_str = 'DRIVER={SQL Server};SERVER=wtk-crouch;DATABASE=wtk_dw_synd;'
conn = pyodbc.connect(conn_str, autocommit=True)
crsr = conn.cursor()

sql = """\
if OBJECT_ID('tempdb..##base') is not null drop table ##base

SET NOCOUNT ON;
SELECT  top 1000
CAST(LEFT(accounting_period,4) AS int) as as_at_year,  (CAST(LEFT(accounting_period,4) AS int) - yoa_code) * 4 + 
 CAST(CASE
	 WHEN RIGHT(accounting_period,2) IN (1,2,3) THEN 1
	 WHEN RIGHT(accounting_period,2) IN (4,5,6) THEN 2
	 WHEN RIGHT(accounting_period,2) IN (7,8,9) THEN 3
	 WHEN RIGHT(accounting_period,2) IN (10,11,12) THEN 4
	END AS int) dev_qrt,


s.scc_code scc, sc.sub_class_name, y.yoa_code yoa, u.underwriter_name, b.broker_name, ue.underwriting_entity_name, rg.reserving_group_name,
	i_clm_pd AS claim_paid,
	i_clm_os AS claim_os, 
	i_clm_inc AS claims_incurred, 
	usm_i_prm AS premium_gross
INTO #base
   FROM dbo.vw_fact_gross g
LEFT JOIN dbo.vw_dim_yoa y on y.yoa_key = g.yoa_key
LEFT JOIN dbo.vw_dim_scc s on s.scc_key = g.scc_key
LEFT JOIN dbo.vw_dim_sub_class sc on sc.sub_class_key = g.sub_class_key
LEFT JOIN dbo.vw_dim_underwriter u on u.underwriter_key = g.underwriter_key
LEFT JOIN dbo.vw_dim_broker b on b.broker_key = g.broker_key
LEFT JOIN dbo.vw_dim_underwriting_entity ue on ue.underwriting_entity_key = g.underwriting_entity_key
LEFT JOIN dbo.vw_dim_reserving_group rg on rg.reserving_group_key = g.reserving_group_key

WHERE currency_type_key = 5 --cnv3 currency type

SELECT * FROM #base

"""
crsr.execute(sql)
data =crsr.fetchall()

cols = []
for i,_ in enumerate(crsr.description):
    cols.append(crsr.description[i][0])

pd.DataFrame(np.array(data), columns = cols)


crsr.close()







df = pd.DataFrame(columns =["as_at_year","dev_qrt","scc", "sub_class", "yoa", "uw_name", "broker", "idc", "res_name", "claim_paid", "claim_os", "claim_inc", "prm_grs"] )
for i in range(len(data)):
    df.loc[i] = list(data[i])