from tkinter import filedialog
from tkinter import Tk
from tkinter import *

root = Tk()

folder_EnergyPLAN = filedialog.askdirectory(title = "Open EnergyPLAN folder")
folder_csv_xlsx = filedialog.askdirectory(title = "csv folder")
outputtable = filedialog.askopenfilename( filetypes = ( (".xlsx", "*.xlsx"), ("All files", "*.*") ),title = "Open case name table" )

'-------------------adjusts screen resolution-----------------------'
import os
# Gives user's home directory
userhome = os.path.expanduser('~')
print ("User's home Dir: " + userhome)
import subprocess
POWERSHELL_COMMAND = r'C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe'
subprocess.Popen([POWERSHELL_COMMAND,
                  'dir {}\Downloads\qres -Recurse | Unblock-File'.format(userhome)],
                 stdout=subprocess.PIPE,
                 stderr=subprocess.PIPE)
'--------------------define input file----------------------------'
import openpyxl
'-------------------define file storage location-------------------------------'
import pyautogui
import time
import subprocess

path = (outputtable)
wb_obj = openpyxl.load_workbook(path)
sheet_obj = wb_obj.active
m_row = sheet_obj.max_row
m_col = sheet_obj.max_column
time.sleep(10)

for j in range (1, m_col+1):
    name = sheet_obj.cell(row=1, column=j).value
    pyautogui.click(119, 96)
    pyautogui.typewrite('{}.txt'.format(name))
    time.sleep(0.05)
    pyautogui.typewrite(['enter'])
    t1= time.time()
    pyautogui.moveTo(317, 119)
    time.sleep(0.05)
    while pyautogui.pixel(360, 119)[2] != 69:
        time.sleep(0.05)
        t2 = time.time()
        if pyautogui.pixel(360, 119)[2] == 69:
            break
        if t2-t1 > 10:
            pyautogui.typewrite(['enter'])
            pyautogui.typewrite(['enter'])

    pyautogui.click(317, 119)
    time.sleep(0.05)
    while pyautogui.pixel(360, 119)[2] != 69:
        time.sleep(0.05)

        if pyautogui.pixel(360, 119)[2] == 69:
            break

    time.sleep(0.4)
    POWERSHELL_COMMAND = r'C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe'
    subprocess.Popen([POWERSHELL_COMMAND,
                      'Get-clipboard > {0}\{1}.csv'.format(folder_csv_xlsx, name)],
                     stdout = subprocess.PIPE,
                     stderr = subprocess.PIPE)
    time.sleep(0.8)

    import os

    os.system('cmd /c "echo off | clip"')
    time.sleep(0.1)

for j in range(1, m_col + 1):
    name = sheet_obj.cell(row=1, column=j).value
    from pathlib import Path
    file = Path() / (r'{0}\{1}.csv'.format(folder_csv_xlsx, name))  # or Path('./doc.txt')
    size = file.stat().st_size
    if size < 70000:
        pyautogui.click(119, 96)
        pyautogui.typewrite('{}.txt'.format(name))
        pyautogui.typewrite(['enter'])
        pyautogui.moveTo(317, 119)
        time.sleep(0.1)
        while pyautogui.pixel(360, 119)[2] != 69:
            time.sleep(0.1)
            if pyautogui.pixel(360, 119)[2] == 69:
                break
        pyautogui.click(317, 119)
        time.sleep(0.1)
        while pyautogui.pixel(360, 119)[2] != 69:
            time.sleep(0.1)
            if pyautogui.pixel(360, 119)[2] == 69:
                break

        time.sleep(2.5)
        POWERSHELL_COMMAND = r'C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe'
        subprocess.Popen([POWERSHELL_COMMAND,
                          'Get-clipboard > {0}\{1}.csv'.format(folder_csv_xlsx, name)],
                         stdout=subprocess.PIPE,
                         stderr=subprocess.PIPE)
        time.sleep(2.5)

        import os

        os.system('cmd /c "echo off | clip"')
        time.sleep(0.5)

    else: print(name, 'is ok')