import openpyxl as pxl
from openpyxl import load_workbook
import pandas as pd
from datetime import datetime as dt
from pprint import pprint as prt
import os
import json
# Custom modules
import onBaseData
import inputManager

with open('config.json') as json_file:
    config = json.load(json_file)

with open(config["sql_query"]) as sqlQFile:
    sqlQuery = sqlQFile.read() 

loginStr = inputManager.get_login()
startDate, endDate = inputManager.get_timeframe(startDate= "09/27/2022", endDate= dt.now().strftime("%m/%d/%y"))

rawData = onBaseData.get_data(login=loginStr, startDate=startDate, endDate=endDate, sqlQuery=sqlQuery)
fullData = onBaseData.inital_data_processing(raw_report= rawData)

newReport =  f"ACHVerificationReport {dt.now().strftime('%m-%d-%y')}.xlsx"
os.system(f"cp {config['template']} {config['output_dir']}'{newReport}'")

with pd.ExcelWriter(config['output_dir']+newReport, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
    fullData.to_excel(writer, sheet_name="raw_data")