# Title: Exchange rate
# Description: This script gets the exchange rate from the web service and saves it to the database
# Version: 1.0

# Prerequisites: Python 3., pyodbc, requests
# pip install pyodbc
# pip install requests
import pyodbc
import requests

# Pobranie dla innego dnia  https://static.nbp.pl/dane/kursy/xml/dir.txt  (szukamy tylko z prefixami a który oznacza kurs średni)
# https://static.nbp.pl/dane/kursy/xml/[id].xml przykład  https://static.nbp.pl/dane/kursy/xml/a051z240312.xml 
# get xml from the web service https://static.nbp.pl/dane/kursy/xml/LastA.xml and save to variable

# URL of the web service
#url = "https://static.nbp.pl/dane/kursy/xml/LastA.xml"
url ="https://static.nbp.pl/dane/kursy/xml/LastA.xml"
# Send HTTP request to the specified URL and save the response from server in a response object called r
r = requests.get(url)

# Saving the XML data
xml_data = r.text
# remove <?xml version="1.0" encoding="ISO-8859-2"?>
xml_data = xml_data.replace('<?xml version="1.0" encoding="ISO-8859-2"?>', '')
# print(xml_data)

# Execute sql server stored procedure using python
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=192.168.22.104;DATABASE=crm_lisicki_rawa;UID=platformacrm;PWD=xxxxxxx')

# execute the stored procedure with the XML parameter
conn.execute("{call [tools].dodaj_kursy (?)}", xml_data)
conn.commit()

# Create a new cursor from the connection
cursor = conn.cursor()
# Execute the query to get the last 10 currency exchange rates
cursor.execute("select top 10  format(publicationdate , 'yyyy-MM-dd') date  from [common].[currencyExchange] group by publicationdate order by 1 desc")

# Fetch all rows from the last executed statement
rows = cursor.fetchall()

# Print each row
for row in rows:
    print(row.date)

# Close the cursor and connection
cursor.close()
conn.close()


print ("Exchange rate has been updated")