compose-and-upload

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
#
# Copyright (C) 2017, 2019, 2020 Open Tech Strategies, LLC
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU Affero General Public License as published
# by the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU Affero General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.

__doc__ = """\
Compose all of the Lever for Action for Women's Health Open Call CSV files.

Usage:

  $ compose-and-upload \\
       --proposals-csv=PROPOSALS_CSV \\
       --attachments-dir=ATTACHMENTS_DIR \\
       --tdc-config-dir=TDC_CONFIG_DIR \\
       --pare=PARE \\
       --collection-only

Command-line options:
  --proposals-csv FILE            FILE is a CSV file representing the bulk
                                  of the proposal information

  --attachments-dir DIR           DIR is a directory for compose-csvs to look in for what attachments
                                  will be uploaded to the torque wiki.  It needs to have subdirectories
                                  by proposal number.

  --tdc-config-dir DIR            DIR is the location for files that are the base configuration files
                                  needed by Torque, and can be optionally, manually, put on
                                  the torque wiki.  We don't automatically do that because we want to
                                  overwrite the configuration out there.

  --pare ARG                      If ARG is a number, reduce the number of items to 1/ARG.  If
                                  ARG begins with +, then ARG is a comma separated list of
                                  keys to include.  If ARG begins with @, then ARG is a
                                  file with a list of keys to include.  For both + and @,
                                  the list of keys will be limited to only the ones provided.

  --collection-only               Only upload the created CSV file.  Don't upload attachments or
                                  create wiki pages.  For use to speed up process when wiki has been
                                  created already.
"""

from etl import competition, wiki, toc, tdc, utils, Geocoder
import config
import getopt
import sys
import os
import csv
from datetime import datetime


class AFWHTaxIdentParser(competition.InformationTransformer):
    charity_check = "Charity Check"
    selection = "Selection"
    subtypes = [
        "Australia Business Number",
        "CPNJ",
        "Revenue Agency Business",
        "FCRA Registration Number",
        "KVK Number",
        "Nonprofit Organzation Number",
        "Chartity Number",
        "Company Number",
        "Country Name",
    ]

    column_name = "Applicant Tax Identification Number"

    def columns_to_remove(self):
        all_subtypes = AFWHTaxIdentParser.subtypes
        all_subtypes.append(AFWHTaxIdentParser.charity_check)
        all_subtypes.append(AFWHTaxIdentParser.selection)
        return [AFWHTaxIdentParser.column_name + "|" + subtype for subtype in all_subtypes]

    def column_names(self):
        return [AFWHTaxIdentParser.column_name]

    def cell(self, proposal, column_name):
        if proposal.cell(AFWHTaxIdentParser.column_name + "|" + AFWHTaxIdentParser.charity_check):
            return proposal.cell(AFWHTaxIdentParser.column_name + "|" + AFWHTaxIdentParser.charity_check)["ein"]
        elif proposal.cell(AFWHTaxIdentParser.column_name + "|" + AFWHTaxIdentParser.selection):
            resp = False
            for subtype in AFWHTaxIdentParser.subtypes:
                if proposal.cell(AFWHTaxIdentParser.column_name + "|" + subtype):
                    resp = proposal.cell(AFWHTaxIdentParser.column_name + "|" + subtype)

            if not resp:
                raise Exception("Selection tax ident parser chosen, but no underlying found...")
            return resp
        else:
            print(proposal.key())
            raise Exception("Neither Charity Check nor Selection was available...")


class AFWHOrganizationCombiner(competition.InformationTransformer):
    def columns_to_remove(self):
        return [
            "Organization Location|Domestic",
            "Organization Location|International",
        ]

    def column_names(self):
        return ["Organization Location"]

    def cell(self, proposal, column_name):
        location = None
        if proposal.cell("Organization Location|Domestic"):
            location = proposal.cell("Organization Location|Domestic")
        elif proposal.cell("Organization Location|International"):
            location = proposal.cell("Organization Location|International")
        else:
            return {}

        overrides = {
            "us": {
                "pr": ("PR", None),
                "puerto rico": ("PR", None),
                "gu": ("GU", None),
                "guam": ("GU", None),
                "washington, dc": (None, "DC"),
                "washington dc": (None, "DC"),
                "d.c.": (None, "DC"),
                "district of colombia": (None, "DC"),
                "district of columbia,": (None, "DC"),
                "carlifornia": (None, "CA"),
                "san francisco": (None, "CA"),
                "indiana (in)": (None, "IN"),
                "virginia (va)": (None, "VA"),
                "boston massachusetts": (None, "MA"),
                "massachusetts (ma)": (None, "MA"),
                "massachuesetts": (None, "MA"),
                "mass": (None, "MA"),
                "new york state": (None, "NY"),
                "united states (+1)": (None, ""),
                "united states of america": (None, ""),
                "colorad": (None, "CO"),
                "colarado": (None, "CO"),
                "nc- north carolina": (None, "NC"),
                "buncombe": (None, "NC"),
            },
            "ca": {
                "ontari0": (None, "ON"),
                "ontaro": (None, "ON"),
                "canada": (None, ""),
                "bc - british columbia": (None, "BC"),
                "québec, canada": (None, "QC"),
            }
        }

        country_override = overrides.get(location[competition.LocationCombiner.COUNTRY].lower(), {})
        state_override = country_override.get(location[competition.LocationCombiner.STATE].lower(), None)
        if state_override:
            if state_override[0] != None:
                location[competition.LocationCombiner.COUNTRY] = state_override[0]
            if state_override[1] != None:
                location[competition.LocationCombiner.STATE] = state_override[1]

        if location[competition.LocationCombiner.COUNTRY] == "US" and location[competition.LocationCombiner.STATE] == "kjjln":
            return {}

        return location

class AFWHAttachments(competition.BasicAttachments):
    keys = [
        "Governance Structure",
        "Proof of Registration",
        "Audited Finacial Statements",
        "Form 990",
        "Audited Financial Statements",
        "Fiscal Sponsorship Agreement",
        "Audited Financial Statements of Fiscal Sponsor",
    ]

    def cell(self, proposal, column_name):
        attachments = [a for a in self.attachments if a.key == proposal.key()]

        for attachment in attachments:
            for key in AFWHAttachments.keys:
                if proposal.cell("Attachments|" + key) and attachment.file == proposal.cell("Attachments|" + key)[0]["name"]:
                    attachment.name = key
        return super().cell(proposal, column_name)


class AFWHFilterOldProposals(competition.ProposalFilter):
    def filter_proposal(self, proposal):
        try:
            return (datetime.strptime(proposal.cell("Submission Date"), "%Y-%m-%dT%H:%M:%S.%fZ") < datetime(2024, 10, 9))
        except ValueError:
            return (datetime.strptime(proposal.cell("Submission Date"), "%Y-%m-%dT%H:%M:%SZ") < datetime(2024, 10, 9))


def main():
    """Compose the LFC input and emit it as html-ized csv."""
    try:
        opts, args = getopt.getopt(
            sys.argv[1:],
            "",
            [
                "proposals-json=",
                "attachments-dir=",
                "tdc-config-dir=",
                "pare=",
                "collection-only",
            ],
        )
    except getopt.GetoptError as err:
        sys.stderr.write("ERROR: '%s'\n" % err)
        sys.exit(2)

    proposals_json = None
    attachments_dir = None
    tdc_config_dir = None
    pare = None
    collection_only = False
    for o, a in opts:
        if o == "--proposals-json":
            proposals_json = a
        elif o == "--attachments-dir":
            attachments_dir = a
        elif o == "--pare":
            pare = a
        elif o == "--collection-only":
            collection_only = True
        elif o == "--tdc-config-dir":
            tdc_config_dir = a
        else:
            sys.stderr.write("ERROR: unrecognized option '%s'\n" % o)
            sys.exit(2)

    if proposals_json is None:
        sys.stderr.write("ERROR: need --proposals-json option.\n\n")
        sys.stderr.write(__doc__)
        sys.exit(1)

    comp = competition.JsonBasedCompetition(
        proposals_json, "AFWH", "Application #", pare
    )

    comp.filter_proposals(AFWHFilterOldProposals())

    fix_cell_processor = competition.FixCellProcessor()
    comp.process_all_cells_special(fix_cell_processor)
    fix_cell_processor.report()

    comp.process_cells_special(
        "Organization Name", competition.RemoveNewlineProcessor()
    )
    #comp.process_cells_special("Project Title", competition.RemoveNewlineProcessor())
    comp.process_cells_special("Key Words and Phrases", competition.ToListProcessor())

    comp.add_supplemental_information(competition.AirtableColumnsAdder())
    comp.add_supplemental_information(competition.MediaWikiTitleAdder("Organization Name"))
    comp.add_supplemental_information(
        competition.GlobalViewMediaWikiTitleAdder("AFWH", "Organization Name")
    )

    comp.transform_sheet(AFWHTaxIdentParser())

    sdg_list = [
        "SDG 1 - No Poverty;",
        "SDG 2 - Zero Hunger;",
        "SDG 3 - Good Health & Well-Being;",
        "SDG 4 - Quality Education;",
        "SDG 5 - Gender Equality;",
        "SDG 6 - Clean Water & Sanitation;",
        "SDG 7 - Affordable & Clean Energy;",
        "SDG 8 - Decent Work & Economic Growth;",
        "SDG 9 - Industry, Innovation, & Infrastructure;",
        "SDG 10 - Reduced Inequalities;",
        "SDG 11 - Sustainable Cities & Communities;",
        "SDG 12 - Responsible Consumption & Production;",
        "SDG 13 - Climate Action;",
        "SDG 14 - Life Below Water;",
        "SDG 15 - Life on Land;",
        "SDG 16 - Peace, Justice, & Strong Institutions;",
        "SDG 17 - Partnerships for the Goals;",
    ]
    comp.process_cells_special(
        "Sustainable Development Goals",
        competition.SustainableDevelopmentGoalProcessor(
            sdg_list, ignored="NOT APPLICABLE"
        ),
    )

    comp.process_cells_special(
        "Annual Operating Budget",
        competition.AnnualBudgetProcessor(
            {
                competition.AnnualBudget.LESS_THAN_1_MIL: "Less than $1 Million",
                competition.AnnualBudget.BETWEEN_1_MIL_AND_5_MIL: "$1.0 to 5.0 Million",
                competition.AnnualBudget.BETWEEN_5_MIL_AND_10_MIL: "$5.1 to 10 Million",
                competition.AnnualBudget.BETWEEN_10_MIL_AND_25_MIL: "$10.1 to 25 Million",
                competition.AnnualBudget.BETWEEN_25_MIL_AND_50_MIL: "$25.1 to 50 Million",
                competition.AnnualBudget.BETWEEN_50_MIL_AND_100_MIL: "$50.1 to 100 Million",
                competition.AnnualBudget.BETWEEN_100_MIL_AND_250_MIL: "$100.1 to 250 Million",
                competition.AnnualBudget.BETWEEN_250_MIL_AND_500_MIL: "$250.1 to 500 Million",
                competition.AnnualBudget.BETWEEN_500_MIL_AND_750_MIL: "$500.1 to 750 Million",
                competition.AnnualBudget.BETWEEN_750_MIL_AND_1_BIL: "$750.1 Million to $1 Billion",
                competition.AnnualBudget.MORE_THAN_1_BIL: "$1 Billion +",
            }
        ),
    )
    for idx in ["1", "2", "3"]:
        comp.process_cells_special(
            "Priority Populations|%s" % idx, competition.StripRegexProcessor(": ?.*$")
        )
    comp.process_cells_special(
        "Primary Subject Area", competition.StripRegexProcessor("^.*: ?")
    )
    comp.process_cells_special(
        "Primary Subject Area", competition.PrimarySubjectAreaProcessor()
    )

    comp.add_supplemental_information(
        competition.StaticColumnAdder("Competition Name", "AFWH")
    )
    # Placeholder for future data imported from airtable
    comp.add_supplemental_information(competition.MediaWikiTabsAdder())

    if config.geocode_api_key:
        geocoder = Geocoder.Geocoder(config.geocode_api_key)
        comp.process_cells_special(
            "Organization Location", competition.GeocodeProcessor(geocoder)
        )

    for contact in ["Primary", "Secondary"]:
        comp.transform_sheet(competition.NameSplitter("%s Contact|Name" % contact))
        comp.transform_sheet(
            competition.PersonCombiner(
                column_name="%s Contact" % contact,
                first_name="%s Contact|Name|First Name" % contact,
                last_name="%s Contact|Name|Last Name" % contact,
                title="%s Contact|Title" % contact,
                phone="%s Contact|Phone" % contact,
                email="%s Contact|Email" % contact,
            )
        )

    for idx in range(1, 4):
        comp.transform_sheet(competition.NameSplitter("Key Staff #%s|Name" % idx))
        comp.transform_sheet(
            competition.PersonCombiner(
                "Key Staff #%s|Person" % idx,
                first_name="Key Staff #%s|Name|First Name" % idx,
                last_name="Key Staff #%s|Name|Last Name" % idx,
                title="Key Staff #%s|Title" % idx,
            )
        )
        comp.transform_sheet(
            competition.KeyStaffCombiner(
                "Key Staff|%s" % idx,
                person="Key Staff #%s|Person" % idx,
                biography="Key Staff #%s|Biography" % idx,
            )
        )

    comp.transform_sheet(
        competition.ColumnCombinerToArray(
            "Key Staff", [("Key Staff|%s" % idx) for idx in range(1, 4)]
        )
    )


    for num in ["1", "2", "3", "4", "5"]:
        work = "Current"
        comp.transform_sheet(
            competition.LocationCombiner(
                column_name="%s Work #%s Location" % (work, num),
                country="%s Work #%s Location|Country" % (work, num),
                state="%s Work #%s Location|State/Province" % (work, num),
                #ignore_not_in_config=True,
            )
        )

    comp.transform_sheet(
        competition.ColumnCombinerToArray(
            "Priority Populations",
            [
                "Priority Populations|1",
                "Priority Populations|2",
                "Priority Populations|3",
            ],
        )
    )

    comp.transform_sheet(AFWHOrganizationCombiner())
    comp.process_cells_special(
        "Organization Location", competition.LocationCombiner("Organization Location")
    )

    for num in range(1,6):
        comp.transform_sheet(
            competition.FunderCombiner(
                column_name="Funder|%s" % num,
                name="Funder #%s" % num,
                amount="Funder #%s: Amount of Funding" % num,
                first_year="Funder #%s: First Year of Funding" % num,
                last_year="Funder #%s: Last Year of Funding" % num,
            )
        )

    comp.transform_sheet(
        competition.ColumnCombinerToArray(
            "Funders",
            [
                "Funder|1",
                "Funder|2",
                "Funder|3",
                "Funder|4",
                "Funder|5",
            ],
        )
    )

    comp.transform_sheet(
        competition.ColumnCombinerToArray(
            "Current Work Locations",
            [
                "Current Work #1 Location",
                "Current Work #2 Location",
                "Current Work #3 Location",
                "Current Work #4 Location",
                "Current Work #5 Location",
            ],
        )
    )

    comp.transform_sheet(
        competition.ColumnCombiner(
            "Source",
            {
                "Source|Selection": "Source",
                "Source|Other Direct Outreach": "Other Direct Outreach",
                "Source|Other News Source": "Other News Source",
                "Source|Social Media": "Social Media",
                "Source|Partner Organization": "Partner",
                "Source|Event": "Event",
                "Source|Other": "Other",
            }
        )
    )

    attachments = AFWHAttachments(
        comp.sorted_proposal_keys, attachments_dir
    )
    comp.add_supplemental_information(attachments)
    comp.add_supplemental_information(
        competition.StaticColumnAdder("Competition Name", "AFWH")
    )
    comp.add_supplemental_information(competition.MediaWikiTabsAdder())
    comp.sort("Organization Name")

    comp.add_allowlist_exception("Submittable ID", "Application")
    comp.add_allowlist_exception("Project Name", "Application")
    comp.add_allowlist_exception("Department Name", "Application")
    comp.add_allowlist_exception("Year of Formation", "Application")
    comp.add_allowlist_exception("External References", "Application")
    comp.add_allowlist_exception("Overview of Work", "Application")
    comp.add_allowlist_exception("Issue Landscape", "Application")
    comp.add_allowlist_exception("Stakeholder Engagement", "Application")
    comp.add_allowlist_exception("Vision for Growth", "Application")
    comp.add_allowlist_exception("Age of Organization", "Application")
    comp.add_allowlist_exception("Additional Information", "Application")
    comp.add_allowlist_exception("Institution Affiliation", "Application")

    for key in AFWHAttachments.keys:
        comp.remove_information(competition.ColumnRemover("Attachments|" + key))

    comp.remove_information(competition.ColumnRemover("Submission Date"))

    comp.add_toc(toc.GenericToc("Populations", toc.GenericToc.ListColumnAccessor("Priority Populations")))
    comp.add_toc(toc.AnnualBudgetToc("Annual Operating Budget"))

    comp.add_toc(
        toc.GenericToc(
            "Number_of_Employees",
            "Number of Employees",
            [
                "Fewer than 10 Full-time Employees",
                "10 to 25 Full-time Employees",
                "26 to 50 Full-time Employees",
                "51 to 100 Full-time Employees",
                "101 to 300 Full-time Employees",
                "301 to 500 Full-time Employees",
                "501 to 1,000 Full-time Employees",
                "1,000+ Full-time Employees",
            ],
        )
    )

    comp.add_toc(
        toc.SummaryToc(
            "Number_of_Employees_Summary",
            "Number of Employees",
            [
                "Fewer than 10 Full-time Employees",
                "10 to 25 Full-time Employees",
                "26 to 50 Full-time Employees",
                "51 to 100 Full-time Employees",
                "101 to 300 Full-time Employees",
                "301 to 500 Full-time Employees",
                "501 to 1,000 Full-time Employees",
                "1,000+ Full-time Employees",
            ],
            explore_filter_name="number_of_employes",
            sort=toc.GenericToc.SortMethod.COUNT,
        )
    )
    comp.add_toc(
        toc.SummaryToc(
            "Populations_Summary",
            toc.GenericToc.ListColumnAccessor("Priority Populations"),
            explore_filter_name="priority_populations",
            sort=toc.GenericToc.SortMethod.COUNT,
        )
    )
    comp.add_toc(
        toc.SummaryToc(
            "Annual_Operating_Budget_Summary",
            "Annual Operating Budget",
            toc.AnnualBudgetToc.BUDGET_GROUPINGS,
            explore_filter_name="annual_operating_budget",
            sort=toc.GenericToc.SortMethod.COUNT,
        )
    )

    overrides = {
        "Tanzania": "United Republic of Tanzania",
        "Eswatini": "Swaziland",
        "United Kingdom of Great Britain and Northern Ireland": "United Kingdom",
        "Congo, Democratic Republic of the": "Democratic Republic of the Congo",
        "Congo":  "Democratic Republic of the Congo",
        "Côte d'Ivoire": "Ivory Coast",
        "Russian Federation": "Russia",
        "Mauritius": "Mauritania",
        "North Macedonia": "Macedonia",
        "Syrian Arab Republic": "Syria",
        "Saint Lucia": False,
        "Grenada": False,
        "Hong Kong": False,
        "Singapore": False,
        "Guam": False,
        "Maldives": False,
        "Timor-Leste": "East Timor",
        "Korea (Republic of) (South Korea)": "South Korea",
        "Moldova, Republic of": "Moldova",
        "Antigua and Barbuda": False,
        "Czechia": "Czech Republic",
    }

    comp.add_toc(
        toc.CountryChoroplethMap(
            "Registrations_by_Country",
            "Organization Location",
            [239, 188, 179],
            [239, 65, 35],
            country_override=overrides,
            max_num=100,
            legend_title="Registrations",
        )
    )

    comp.process_tocs()

    if tdc_config_dir is not None:
        tdc.AllProposals(comp).generate(tdc_config_dir)
        tdc.ValidProposals(comp, "Admin Review Status", "Valid").generate(
            tdc_config_dir
        )
        tdc.Columns(comp).generate(tdc_config_dir)
        tdc.ProcessedCollection(comp).generate(tdc_config_dir)

    my_wiki = wiki.WikiSession(
        config.username, config.password, comp.name, config.wiki_url
    )
    my_wiki.collection_only = collection_only
    my_wiki.upload_collection(comp, cache_videos=config.cache_videos)
    my_wiki.upload_attachments(attachments.attachments)

    my_wiki.sanity_check_wiki()


if __name__ == "__main__":
    main()