import requests
import os
from common import root
from db import conn, query_db

legal = 'serecap'
username = 'serecapuser'
password = 'Welcome1'

# sqlstring = "SELECT DISTINCT LoanXID from markit_prices";
# common.cursor.execute(sqlstring)

flag = False

# with open( os.path.join(common.root, "data", "Facility files",
#                         "facility_latest.csv"), "wb") as fh:
#     for loanxid in common.cursor.fetchall():
#         r = requests.get('https://loans.markit.com/loanx/LoanXOneFacility.csv?LEGALENTITY={0}&USERNAME={1}&PASSWORD={2}&LOANXID={3}'.format(legal, username, password, loanxid[0]))
#         if flag:
#             fh.write(r.content.split('\n')[1] + "\n")
#         else:
#             fh.write(r.content.split('\n')[0] + "\n")
#             fh.write(r.content.split('\n')[1] + "\n")
#             flag = True


sqlstring  = "select loanxid from markit_prices2 except (select loanxid from latest_markit_prices2)"
loan_ids = query_db(sqlstring, one=False)
with open( os.path.join(root, "data", "Facility files",
                        "facility_test.csv"), "wb") as fh:
    for loanxid in loan_ids:
        r = requests.get('https://loans.markit.com/loanx/LoanXOneFacility.csv?LEGALENTITY={0}&USERNAME={1}&PASSWORD={2}&LOANXID={3}'.format(legal, username, password, loanxid[0]))
        if flag:
            fh.write(r.content.split('\n')[1] + "\n")
        else:
            fh.write(r.content.split('\n')[0] + "\n")
            fh.write(r.content.split('\n')[1] + "\n")
            flag = True

conn.close()