Example #1
0
def get_orcamento_executado_consolidado():
    years = [str(i) for i in range(2011, 2022)]

    for year in years:
        print("execucao : ", year)
        path = os.getcwd()

        path = path.split(
            "notebooks")[0] + f"data/orcamento_consolidado/execucao"

        files_before = glob.glob(f"{path}/*")

        profile = webdriver.FirefoxProfile()
        profile.set_preference("browser.download.dir", path)
        profile.set_preference("browser.download.folderList", 2)
        profile.set_preference(
            "browser.helperApps.neverAsk.saveToDisk",
            "application/csv,application/excel,application/vnd.msexcel,application/vnd.ms-excel,text/anytext,text/comma-separated-values,text/csv,application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/octet-stream",
        )
        profile.set_preference("browser.download.manager.showWhenStarting",
                               False)
        profile.set_preference(
            "browser.helperApps.neverAsk.openFile",
            "application/csv,application/excel,application/vnd.msexcel,application/vnd.ms-excel,text/anytext,text/comma-separated-values,text/csv,application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/octet-stream",
        )
        profile.set_preference("browser.helperApps.alwaysAsk.force", False)
        profile.set_preference("browser.download.manager.useWindow", False)
        profile.set_preference("browser.download.manager.focusWhenStarting",
                               False)
        profile.set_preference("browser.download.manager.alertOnEXEOpen",
                               False)
        profile.set_preference("browser.download.manager.showAlertOnComplete",
                               False)
        profile.set_preference("browser.download.manager.closeWhenDone", True)
        profile.set_preference("pdfjs.disabled", True)

        options = Options()
        ### run quiet
        options.headless = False

        firefox = webdriver.Firefox(
            options=options,
            firefox_profile=profile,
            executable_path=GeckoDriverManager().install(),
        )
        url = "https://www.fazenda.sp.gov.br/SigeoLei131/Paginas/FlexConsDespesa.aspx"

        firefox.get(url)
        # firefox.request('POST', url,)

        ano = Select(
            firefox.find_element_by_name("ctl00$ContentPlaceHolder1$ddlAno"))
        ano.select_by_value(year)

        firefox.find_element_by_name(
            "ctl00$ContentPlaceHolder1$cblFase$0").click()
        time.sleep(2)
        firefox.find_element_by_name(
            "ctl00$ContentPlaceHolder1$cblFase$1").click()
        time.sleep(2)
        firefox.find_element_by_name(
            "ctl00$ContentPlaceHolder1$cblFase$2").click()
        time.sleep(2)
        firefox.find_element_by_name(
            "ctl00$ContentPlaceHolder1$cblFase$3").click()
        time.sleep(2)
        firefox.find_element_by_name(
            "ctl00$ContentPlaceHolder1$cblFase$4").click()

        options = {
            "ctl00$ContentPlaceHolder1$ddlOrgao": "",
            "ctl00$ContentPlaceHolder1$ddlCategoria": "",
            "ctl00$ContentPlaceHolder1$ddlUo": "",
            "ctl00$ContentPlaceHolder1$ddlGrupo": "",
            "ctl00$ContentPlaceHolder1$ddlUge": "",
            "ctl00$ContentPlaceHolder1$ddlModalidade": "",
            "ctl00$ContentPlaceHolder1$ddlFonteRecursos": "",
            "ctl00$ContentPlaceHolder1$ddlElemento": "",
            "ctl00$ContentPlaceHolder1$ddlFuncao": "",
            "ctl00$ContentPlaceHolder1$ddlSubFuncao": "",
            "ctl00$ContentPlaceHolder1$ddlPrograma": "",
            "ctl00$ContentPlaceHolder1$ddlAcao": "",
            "ctl00$ContentPlaceHolder1$ddlProgramaTrabalho": "",
        }

        for op in options.keys():
            selected = Select(firefox.find_element_by_name(op))
            selected.select_by_value(options[op])
            time.sleep(5)

        firefox.find_element_by_name(
            "ctl00$ContentPlaceHolder1$btnPesquisar").click()

        time.sleep(60)
        firefox.find_element_by_name(
            "ctl00$ContentPlaceHolder1$btnExcel").click()
        time.sleep(60)

        firefox.quit()

        files_after = glob.glob(f"{path}/*")

        file_now = [file for file in files_after
                    if file not in files_before][0]

        os.rename(
            file_now,
            path + "/orcamento_executado_{}.csv".format(year),
        )
Example #2
0
def get_orcamento_executado():
    today = datetime.datetime.today().strftime("%Y-%m-%d")
    ano = int(datetime.datetime.today().strftime("%Y"))
    years = [str(i) for i in range(2020, ano + 1)]

    for year in years:
        print("execucao : ", year)
        path = os.getcwd()

        year_path = path.split("notebooks")[0] + f"data/orcamento/{year}"
        if not os.path.exists(year_path):
            os.mkdir(year_path)
            os.mkdir(year_path + "/executado")
            os.mkdir(year_path + "/receita")
            os.mkdir(year_path + "/receita/arrecadado")
            os.mkdir(year_path + "/receita/previsto")

        path = path.split("notebooks")[0] + f"data/orcamento/{year}/executado"

        files_before = glob.glob(f"{path}/*")

        profile = webdriver.FirefoxProfile()
        profile.set_preference("browser.download.dir", path)
        profile.set_preference("browser.download.folderList", 2)
        profile.set_preference(
            "browser.helperApps.neverAsk.saveToDisk",
            "application/csv,application/excel,application/vnd.msexcel,application/vnd.ms-excel,text/anytext,text/comma-separated-values,text/csv,application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/octet-stream",
        )
        profile.set_preference("browser.download.manager.showWhenStarting",
                               False)
        profile.set_preference(
            "browser.helperApps.neverAsk.openFile",
            "application/csv,application/excel,application/vnd.msexcel,application/vnd.ms-excel,text/anytext,text/comma-separated-values,text/csv,application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/octet-stream",
        )
        profile.set_preference("browser.helperApps.alwaysAsk.force", False)
        profile.set_preference("browser.download.manager.useWindow", False)
        profile.set_preference("browser.download.manager.focusWhenStarting",
                               False)
        profile.set_preference("browser.download.manager.alertOnEXEOpen",
                               False)
        profile.set_preference("browser.download.manager.showAlertOnComplete",
                               False)
        profile.set_preference("browser.download.manager.closeWhenDone", True)
        profile.set_preference("pdfjs.disabled", True)

        options = Options()
        ### run quiet
        options.headless = True

        firefox = webdriver.Firefox(
            options=options,
            firefox_profile=profile,
            executable_path=GeckoDriverManager().install(),
        )
        url = "https://www.fazenda.sp.gov.br/SigeoLei131/Paginas/FlexConsDespesa.aspx"

        firefox.get(url)
        # firefox.request('POST', url,)

        ano = Select(
            firefox.find_element_by_name("ctl00$ContentPlaceHolder1$ddlAno"))
        ano.select_by_value(year)

        firefox.find_element_by_name(
            "ctl00$ContentPlaceHolder1$cblFase$0").click()
        time.sleep(2)
        firefox.find_element_by_name(
            "ctl00$ContentPlaceHolder1$cblFase$1").click()
        time.sleep(2)
        firefox.find_element_by_name(
            "ctl00$ContentPlaceHolder1$cblFase$2").click()
        time.sleep(2)
        firefox.find_element_by_name(
            "ctl00$ContentPlaceHolder1$cblFase$3").click()
        time.sleep(2)
        firefox.find_element_by_name(
            "ctl00$ContentPlaceHolder1$cblFase$4").click()
        time.sleep(2)

        options = {
            "ctl00$ContentPlaceHolder1$ddlOrgao": "",
            "ctl00$ContentPlaceHolder1$ddlCategoria": "",
            "ctl00$ContentPlaceHolder1$ddlUo": "",
            "ctl00$ContentPlaceHolder1$ddlGrupo": "",
            "ctl00$ContentPlaceHolder1$ddlUge": "",
            "ctl00$ContentPlaceHolder1$ddlModalidade": "",
            "ctl00$ContentPlaceHolder1$ddlFonteRecursos": "",
            "ctl00$ContentPlaceHolder1$ddlElemento": "",
            "ctl00$ContentPlaceHolder1$ddlFuncao": "",
            "ctl00$ContentPlaceHolder1$ddlSubFuncao": "",
            "ctl00$ContentPlaceHolder1$ddlPrograma": "",
            "ctl00$ContentPlaceHolder1$ddlAcao": "",
            "ctl00$ContentPlaceHolder1$ddlProgramaTrabalho": "",
        }

        for op in options.keys():
            selected = Select(firefox.find_element_by_name(op))
            selected.select_by_value(options[op])
            time.sleep(5)

        firefox.find_element_by_name(
            "ctl00$ContentPlaceHolder1$btnPesquisar").click()

        time.sleep(60)
        firefox.find_element_by_name(
            "ctl00$ContentPlaceHolder1$btnExcel").click()
        time.sleep(60)

        firefox.quit()

        files_after = glob.glob(f"{path}/*")

        file_now = [file for file in files_after
                    if file not in files_before][0]

        os.rename(
            file_now,
            path + "/{}_orcamento_{}.csv".format(today, year),
        )

        df = pd.read_csv(path + "/{}_orcamento_{}.csv".format(today, year),
                         encoding="windows-1254")

        df = df[df["Órgão"].notnull()]

        df["date"] = today

        df.columns = manipulate.normalize_cols(df.columns)

        cols = [
            "dotacao_inicial",
            "dotacao_atual",
            "empenhado",
            "liquidado",
            "pago",
            "pago_restos",
        ]

        for col in cols:
            df[col] = df[col].str.replace(".", "").str.replace(",", ".")
            df[col] = pd.to_numeric(df[col], errors="coerce")

        df = df.loc[:, df.isnull().mean() < 0.98]

        df.to_csv(path + "/last_data.csv", encoding="utf-8", index=False)

        df.to_csv(
            path + "/{}_orcamento_{}.csv".format(today, year),
            encoding="utf-8",
            index=False,
        )
        print("\n")