import pyodbc as po
import json
import shutil
import os
import datetime
import zipfile
import sys
import requests
import urllib3
import os.path


urllib3.disable_warnings()
CurrentPath = os.path.dirname(os.path.realpath(__file__))
with open(CurrentPath+"/config/config.json") as json_data_file:
    config = json.load(json_data_file)
    ServerSIR = config['ServerSIR']
    DatabaseSIR = config['DatabaseSIR']
    UsernameSIR = config['DBUserSIR']
    PasswordSIR = config['DBPasswordSIR']
    RepositoryArchivoElectronicoOficial = config['RepositoryArchivoElectronicoOficial']
    '''RepositoryArchivoElectronicoOficial = 'c:/data/temp/'''
    TempFolder= 'c:/tmp/'
    today = datetime.date.today()
    YEAR = today.year

def WriteToLog(Referencias): 
    with open('./log/Referencias.txt', 'a') as f:
      Referencias = Referencias.replace('[', '')
      Referencias = Referencias.replace(']', '')
      f.write(f"{Referencias}\n")


def ZipReference(foldername, TempFolder, Cliente):
   zipobj = zipfile.ZipFile(TempFolder+ '_____'+ Cliente + '.zip', 'w', zipfile.ZIP_DEFLATED)
   rootlen = len(TempFolder) + 1
   for base, dirs, files in os.walk(TempFolder):
       for file in files:
           fn = os.path.join(base, file)
           zipobj.write(fn, fn[rootlen:])


def CreateFolderContent():
    try:
        Files2Process = []
        cnxn = po.connect(
            "DRIVER={ODBC Driver 17 for SQL Server};SERVER=" + ServerSIR
            + ";DATABASE=" + DatabaseSIR
            + ";UID=" + UsernameSIR
            + ";PWD=" + PasswordSIR
        )
        cursor = cnxn.cursor()
        cursor.execute("""SELECT P.sReferencia, p.Archivo, P.Aduana, P.Patente, P.Pedimento, P.[Pedimento Fecha Pago], Peds.nTipoCambio, 
                            P.Clave, P.Cliente FROM SIR.Admin.SIR_VT_PedimPagados P 
                            INNER JOIN SIR.SIR.SIR_149_PEDIMENTO AS Peds ON Peds.nIdPedimento149=ID
                            WHERE P.ClienteClave=2096 and p.[Pedimento FechaPago]>=CAST( GETDATE()-5 AS Date )""")
        for row in cursor:
          Referencia = row[0]
          Cliente = row[8]
          ReferenceFolder = TempFolder + Referencia
          if os.path.exists(ReferenceFolder): shutil.rmtree(ReferenceFolder)
          shutil.copytree(RepositoryArchivoElectronicoOficial + str(YEAR) + '/' + Referencia, ReferenceFolder)
          Files2Process.append(ReferenceFolder+'_____'+Cliente+'.zip')
          ZipReference(Referencia, ReferenceFolder, Cliente)
        URL = 'https://localhost:5001/api/AmazonInvoice/UploadSIRReference'
        multiple_files=[]          
        for file in Files2Process:
            print(file)
            if os.path.exists(file):
                multiple_files.append(('files', (file.replace(TempFolder,''), open(file, 'rb'), 'application/zip')))  
        r = requests.post(url=URL, files=multiple_files, verify=False, timeout=30)
        print(r.text)
        WriteToLog(r.text)
    except Exception as e:
        print("Error: %s" % e)
    finally:
        cnxn.close()


''' contents = [os.path.join(TempFolder, i) for i in os.listdir(TempFolder)]
[shutil.rmtree(i) if os.path.isdir(i) and not os.path.islink(i) else os.remove(i) for i in contents] '''
CreateFolderContent()