https://oauth.peliqan.io

# This script will delete records in the DWH that were deleted in the source (Exact Online)

schema = 'exact online'

st.write("Getting most recent deleted records from Exact Online.")
pq.run_pipeline(connection_name = 'exact online', is_async = False, tables="deleted")
st.write("Done getting new deleted records.")

dbconn = pq.dbconnect(pq.DW_NAME)
schema = '"' + schema + '"'

ts = pq.get_state()
if not ts:
    ts = "2020-01-01 00:00:00"

query = f"""
SELECT d.entitykey, d.division, e.name as entityname, d._sdc_batched_at
FROM {schema}.deleted d 
INNER JOIN {schema}.entitytype e
ON d.entitytype = e.number
WHERE d._sdc_batched_at > '{ts}'
ORDER BY d._sdc_batched_at
"""

rows = dbconn.fetch(pq.DW_NAME, query = query)

st.write("Deleting %s records." % len(rows))

for row in rows:
    table_name = schema + '.' + row["entityname"].lower()
    id = row["entitykey"]
    division = row["division"]
    
    delete_query = f"DELETE FROM %s WHERE id='%s' AND division=%s" % (table_name, id, division)    
    result = dbconn.execute(pq.DW_NAME, query = delete_query)
    st.write("Deleting %s with id %s in division %s: %s." % (row["entityname"], id, division, result["status"]))

if len(rows):
    pq.set_state(row["_sdc_batched_at"])

# This script will sync only selected divisions from Exact Online.
#
# Uses a table as input, set table name below.
# Also set correct connection name below.
# Add a new Exact Online connection for only one division id.
# After first run, disable the ETL scheduler and use the scheduler of this script.
#
# Add a schedule "every minute" to this script (to process "Sync now")

table_name = "exact_online_divisions_to_sync.divisions"
connection_name = "Exact Online"

import time, datetime, pytz
dbconn = pq.dbconnect(pq.DW_NAME)

st.write("Syncing divisions with 'Sync enabled' and last sync more than 24 hours ago, and also divisions with 'Sync now' enabled.")
query_divisions_to_sync = """
SELECT 
   division_id
FROM {table_name}
WHERE 
(
   sync_enabled = TRUE AND 
   TO_TIMESTAMP(last_sync, 'YYYY-MM-DD HH24:MI:SS') < NOW() - INTERVAL '1 DAY' 
)
OR sync_now = TRUE
""".format(table_name = table_name)

# Get divisions to sync from table, run pipeline per division, and update row in table
rows = dbconn.fetch(pq.DW_NAME, query = query_divisions_to_sync)

for row in rows:
    division = row["division_id"]
    st.write(f"Running ETL pipeline for Exact Online division: %s" % division)
    try:
        run_pipeline_result = pq.run_pipeline(connection_name = connection_name, is_async = False, categories = str(division))
    except:
        st.write("A pipeline for Exact Online is already running. Will try again on next run.")
        exit()

    run_pipeline_details = pq.get_pipeline_runs(connection_name = connection_name, run_id = run_pipeline_result["run_id"])
    status = run_pipeline_details["data"][0]["status"]
    ts = datetime.datetime.now(pytz.timezone('Europe/Brussels'))
    
    update_query = f"UPDATE %s SET sync_now = FALSE, last_sync = '%s', status = '%s' WHERE division_id = %s" % (table_name, ts, status, division)
    dbconn.execute(pq.DW_NAME, query = update_query)
    time.sleep(10) # Avoid error that pipeline is still running