# Imports
import sqlalchemy
import pandas as pd
import pyodbc
import uuid
import csv
import psycopg2

#mssql connection
sql_conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=192.168.5.81;DATABASE=Eremedylive;UID=sa;PWD=1234567aA')
query = "select * FROM op_billing"


#postgresql connection
conn = psycopg2.connect(host="192.168.5.86",database="nbi_women", user="healthix_user", password="goldcrack_db@1020#!")
engine = sqlalchemy.create_engine('postgresql://healthix_user:goldcrack_db@1020#!@192.168.5.86:5432/nbi_women')

df = pd.read_sql(query, sql_conn)
print df

df1 = df[['CREDIT_COMPANY_ID','PATIENT_ID','CREDIT_COMPANY_SCHEME_ID','SCHEME_MEMBER_ID','VISIT_ID','PATIENT_ID','VISIT_TYPE',
'CENTER_ID','CREATED_DATE','CREATED_BY','MODIFIED_DATE','CREDIT_COMPANY_ID','CO_PAY']]
print df1

df1.to_csv('opbill.csv', sep='\t', index=False)
#df2 = pd.read_csv("opbill.csv")
cols = ['insurance_company','member_number','scheme_name','member','visit_number','patient_number','visit_type','provider','created',
'created_by','modified','payers','k_gross_amount']
df1.columns = cols
print df1
df1.to_csv('modifiedopbill.csv', sep='\t', index=False)
#Read modified.csv appending uuid4() field
fin = open('modifiedopbill.csv', 'rb')
fout = open('uuidmodifiedopbill.csv', 'w')

reader = csv.reader(fin, delimiter=',', quotechar='"')
writer = csv.writer(fout, delimiter=',', quotechar='"')

firstrow = True
for row in reader:
    if firstrow:
        row.append('id')
        firstrow = False
    else:
        row.append(uuid.uuid4())
    writer.writerow(row)

df2 = pd.read_csv("uuidmodifiedopbill.csv")

df2.to_sql(name='claims_claim', con=engine, if_exists='replace',index=False)