from collections import OrderedDict
from sqlalchemy import func, cast, Date

from dataactcore.models.stagingModels import DetachedAwardProcurement

file_model = DetachedAwardProcurement

mapping = OrderedDict([
    ('detached_award_proc_unique', ['ContractTransactionUniqueKey', 'ContractTransactionUniqueKey ()']),
    ('unique_award_key', ['PrimeAwardUniqueKey', 'PrimeAwardUniqueKey ()']),
    ('piid', ['PIID', 'PIID (1A)']),
    ('award_modification_amendme', ['AwardModificationAmendmentNumber', 'AwardModificationAmendmentNumber (1B)']),
    ('transaction_number', ['Transaction Number', 'Transaction Number (1D)']),
    ('referenced_idv_agency_iden', ['Referenced IDV Agency Identifier', 'Referenced IDV Agency Identifier (1H)']),
    ('referenced_idv_agency_desc', ['Referenced IDV Agency Name', 'Referenced IDV Agency Name ()']),
    ('parent_award_id', ['ParentAwardId', 'ParentAwardId (1C)']),
    ('referenced_idv_modificatio', ['Referenced IDV Modification Number', 'Referenced IDV Modification Number (1G)']),
    ('federal_action_obligation', ['FederalActionObligation', 'FederalActionObligation (3C)']),
    ('total_obligated_amount', ['TotalDollarsObligated', 'TotalDollarsObligated (3CT)']),
    ('base_exercised_options_val', ['BaseAndExercisedOptionsValue', 'BaseAndExercisedOptionsValue (3B)']),
    ('current_total_value_award', ['CurrentTotalValueOfAward', 'CurrentTotalValueOfAward (3BT)']),
    ('base_and_all_options_value', ['BaseAndAllOptionsValue', 'BaseAndAllOptionsValue (3A)']),
    ('potential_total_value_awar', ['PotentialTotalValueOfAward', 'PotentialTotalValueOfAward (3AT)']),
    ('action_date', ['ActionDate', 'ActionDate (2A)']),
    ('period_of_performance_star', ['PeriodOfPerformanceStartDate', 'PeriodOfPerformanceStartDate (2B)']),
    ('period_of_performance_curr', ['PeriodOfPerformanceCurrentEndDate', 'PeriodOfPerformanceCurrentEndDate (2C)']),
    ('period_of_perf_potential_e', ['PeriodOfPerformancePotentialEndDate', 'PeriodOfPerformancePotentialEndDate (2D)']),
    ('ordering_period_end_date', ['OrderingPeriodEndDate', 'OrderingPeriodEndDate (2E)']),
    ('solicitation_date', ['SolicitationDate', 'SolicitationDate (2H)']),
    ('awarding_agency_code', ['AwardingAgencyCode', 'AwardingAgencyCode ()']),
    ('awarding_agency_name', ['AwardingAgencyName', 'AwardingAgencyName ()']),
    ('awarding_sub_tier_agency_c', ['AwardingSubTierAgencyCode', 'AwardingSubTierAgencyCode (4A)']),
    ('awarding_sub_tier_agency_n', ['AwardingSubTierAgencyName', 'AwardingSubTierAgencyName ()']),
    ('awarding_office_code', ['AwardingOfficeCode', 'AwardingOfficeCode (4B)']),
    ('awarding_office_name', ['AwardingOfficeName', 'AwardingOfficeName ()']),
    ('funding_agency_code', ['FundingAgencyCode', 'FundingAgencyCode ()']),
    ('funding_agency_name', ['FundingAgencyName', 'FundingAgencyName ()']),
    ('funding_sub_tier_agency_co', ['FundingSubTierAgencyCode', 'FundingSubTierAgencyCode (4C)']),
    ('funding_sub_tier_agency_na', ['FundingSubTierAgencyName', 'FundingSubTierAgencyName ()']),
    ('funding_office_code', ['FundingOfficeCode', 'FundingOfficeCode (4D)']),
    ('funding_office_name', ['FundingOfficeName', 'FundingOfficeName ()']),
    ('foreign_funding', ['Foreign Funding', 'Foreign Funding (4F)']),
    ('foreign_funding_desc', ['Foreign Funding Description Tag', 'Foreign Funding Description Tag ()']),
    ('sam_exception', ['SAM Exception', 'SAM Exception (9J)']),
    ('sam_exception_description', ['SAM Exception Description Tag', 'SAM Exception Description Tag ()']),
    ('awardee_or_recipient_uei', ['AwardeeOrRecipientUEI', 'AwardeeOrRecipientUEI (9M)']),
    ('awardee_or_recipient_legal', ['AwardeeOrRecipientLegalEntityName', 'AwardeeOrRecipientLegalEntityName (13GG)']),
    ('vendor_doing_as_business_n', ['Entity Doing Business As Name', 'Entity Doing Business As Name (13HH)']),
    ('cage_code', ['CAGE Code', 'CAGE Code (9L)']),
    ('ultimate_parent_uei', ['UltimateParentUEI', 'UltimateParentUEI ()']),
    ('ultimate_parent_legal_enti', ['UltimateParentLegalEntityName', 'UltimateParentLegalEntityName ()']),
    ('legal_entity_country_code', ['LegalEntityCountryCode', 'LegalEntityCountryCode (13QQ)']),
    ('legal_entity_country_name', ['LegalEntityCountryName', 'LegalEntityCountryName ()']),
    ('legal_entity_address_line1', ['LegalEntityAddressLine1', 'LegalEntityAddressLine1 (13JJ)']),
    ('legal_entity_address_line2', ['LegalEntityAddressLine2', 'LegalEntityAddressLine2 (13KK)']),
    ('legal_entity_city_name', ['LegalEntityCityName', 'LegalEntityCityName (13MM)']),
    ('legal_entity_state_code', ['LegalEntityStateCode', 'LegalEntityStateCode (13NN)']),
    ('legal_entity_state_descrip', ['LegalEntityStateDescription', 'LegalEntityStateDescription (13NN)']),
    ('legal_entity_zip4', ['LegalEntityZIP+4', 'LegalEntityZIP+4 (13PP)']),
    ('legal_entity_congressional', ['LegalEntityCongressionalDistrict', 'LegalEntityCongressionalDistrict (9F)']),
    ('vendor_phone_number', ['Entity Phone Number', 'Entity Phone Number (13RR)']),
    ('vendor_fax_number', ['Entity Fax Number', 'Entity Fax Number (13SS)']),
    ('place_of_perform_city_name', ['PrimaryPlaceOfPerformanceCityName', 'PrimaryPlaceOfPerformanceCityName (9D)']),
    ('place_of_perform_county_na', ['PrimaryPlaceOfPerformanceCountyName', 'PrimaryPlaceOfPerformanceCountyName ()']),
    ('place_of_performance_state', ['PrimaryPlaceOfPerformanceStateCode', 'PrimaryPlaceOfPerformanceStateCode (9C)']),
    ('place_of_perfor_state_desc', ['PrimaryPlaceOfPerformanceStateName', 'PrimaryPlaceOfPerformanceStateName ()']),
    ('place_of_performance_zip4a', ['PrimaryPlaceOfPerformanceZIP+4', 'PrimaryPlaceOfPerformanceZIP+4 (9K)']),
    ('place_of_performance_congr', ['PrimaryPlaceOfPerformanceCongressionalDistrict',
                                    'PrimaryPlaceOfPerformanceCongressionalDistrict (9G)']),
    ('place_of_perform_country_c', ['PrimaryPlaceOfPerformanceCountryCode',
                                    'PrimaryPlaceOfPerformanceCountryCode (9C)']),
    ('place_of_perf_country_desc', ['PrimaryPlaceOfPerformanceCountryName', 'PrimaryPlaceOfPerformanceCountryName ()']),
    ('pulled_from', ['Award Or IDV Flag', 'Award Or IDV Flag ()']),
    ('contract_award_type', ['ContractAwardType', 'ContractAwardType (12B)']),
    ('contract_award_type_desc', ['ContractAwardTypeDescriptionTag', 'ContractAwardTypeDescriptionTag ()']),
    ('idv_type', ['IDV_Type', 'IDV_Type (12A)']),
    ('idv_type_description', ['IDV_Type Description Tag', 'IDV_Type Description Tag ()']),
    ('multiple_or_single_award_i', ['Multiple or Single Award IDV', 'Multiple or Single Award IDV (6E)']),
    ('multiple_or_single_aw_desc', ['Multiple or Single Award IDV Description Tag',
                                    'Multiple or Single Award IDV Description Tag ()']),
    ('type_of_idc', ['Type of IDC', 'Type of IDC (6D)']),
    ('type_of_idc_description', ['Type of IDC Description Tag', 'Type of IDC Description Tag ()']),
    ('type_of_contract_pricing', ['TypeOfContractPricing', 'TypeOfContractPricing (6A)']),
    ('type_of_contract_pric_desc', ['TypeOfContractPricingDescriptionTag', 'TypeOfContractPricingDescriptionTag ()']),
    ('award_description', ['AwardDescription', 'AwardDescription (6M)']),
    ('action_type', ['ActionType', 'ActionType (12C)']),
    ('action_type_description', ['ActionTypeDescriptionTag', 'ActionTypeDescriptionTag ()']),
    ('solicitation_identifier', ['Solicitation Identifier', 'Solicitation Identifier (1E)']),
    ('number_of_actions', ['Number of Actions', 'Number of Actions (6Q)']),
    ('inherently_government_func', ['Inherently Governmental Functions', 'Inherently Governmental Functions (6T)']),
    ('inherently_government_desc', ['Inherently Governmental Functions Description Tag',
                                    'Inherently Governmental Functions Description Tag ()']),
    ('product_or_service_code', ['Product or Service Code', 'Product or Service Code (8A)']),
    ('product_or_service_co_desc', ['Product or Service Code Description Tag',
                                    'Product or Service Code Description Tag ()']),
    ('contract_bundling', ['Contract Bundling', 'Contract Bundling (8N)']),
    ('contract_bundling_descrip', ['Contract Bundling Description Tag', 'Contract Bundling Description Tag ()']),
    ('dod_claimant_program_code', ['DoD Claimant Program Code', 'DoD Claimant Program Code (8F)']),
    ('dod_claimant_prog_cod_desc', ['DoD Claimant Program Code Description Tag',
                                    'DoD Claimant Program Code Description Tag ()']),
    ('naics', ['NAICS', 'NAICS (8G)']),
    ('naics_description', ['NAICS_Description', 'NAICS_Description ()']),
    ('recovered_materials_sustai', ['Recovered Materials/Sustainability', 'Recovered Materials/Sustainability (8L)']),
    ('recovered_materials_s_desc', ['Recovered Materials/Sustainability Description Tag',
                                    'Recovered Materials/Sustainability Description Tag ()']),
    ('domestic_or_foreign_entity', ['Domestic or Foreign Entity', 'Domestic or Foreign Entity (8Q)']),
    ('domestic_or_foreign_e_desc', ['Domestic or Foreign Entity Description Tag',
                                    'Domestic or Foreign Entity Description Tag ()']),
    ('program_system_or_equipmen', ['DOD Acquisition Program', 'DOD Acquisition Program (8B)']),
    ('program_system_or_equ_desc', ['DOD Acquisition Program Description Tag',
                                    'DOD Acquisition Program Description Tag ()']),
    ('information_technology_com', ['Information Technology Commercial Item Category',
                                    'Information Technology Commercial Item Category (8H)']),
    ('information_technolog_desc', ['Information Technology Commercial Item Category Description Tag',
                                    'Information Technology Commercial Item Category Description Tag ()']),
    ('epa_designated_product', ['EPA-Designated Product', 'EPA-Designated Product (8K)']),
    ('epa_designated_produc_desc', ['EPA-Designated Product Description Tag',
                                    'EPA-Designated Product Description Tag ()']),
    ('country_of_product_or_serv', ['Country of Product or Service Origin',
                                    'Country of Product or Service Origin (9E)']),
    ('country_of_product_or_desc', ['Country of Product or Service Origin Description Tag',
                                    'Country of Product or Service Origin Description Tag ()']),
    ('place_of_manufacture', ['Place of Manufacture', 'Place of Manufacture (9H)']),
    ('place_of_manufacture_desc', ['Place of Manufacture Description Tag',
                                   'Place of Manufacture Description Tag ()']),
    ('subcontracting_plan', ['Subcontracting Plan', 'Subcontracting Plan (11B)']),
    ('subcontracting_plan_desc', ['Subcontracting Plan Description Tag', 'Subcontracting Plan Description Tag ()']),
    ('extent_competed', ['Extent Competed', 'Extent Competed (10A)']),
    ('extent_compete_description', ['Extent Competed Description Tag', 'Extent Competed Description Tag ()']),
    ('solicitation_procedures', ['Solicitation Procedures', 'Solicitation Procedures (10M)']),
    ('solicitation_procedur_desc', ['Solicitation Procedures Description Tag',
                                    'Solicitation Procedures Description Tag ()']),
    ('type_set_aside', ['Type Set Aside', 'Type Set Aside (10N)']),
    ('type_set_aside_description', ['Type Set Aside Description Tag', 'Type Set Aside Description Tag ()']),
    ('evaluated_preference', ['Evaluated Preference', 'Evaluated Preference (10P)']),
    ('evaluated_preference_desc', ['Evaluated Preference Description Tag',
                                   'Evaluated Preference Description Tag ()']),
    ('research', ['Research', 'Research (10Q)']),
    ('research_description', ['Research Description Tag', 'Research Description Tag ()']),
    ('fair_opportunity_limited_s', ['Fair Opportunity Limited Sources', 'Fair Opportunity Limited Sources (10R)']),
    ('fair_opportunity_limi_desc', ['Fair Opportunity Limited Sources Description Tag',
                                    'Fair Opportunity Limited Sources Description Tag ()']),
    ('other_than_full_and_open_c', ['Other than Full and Open Competition',
                                    'Other than Full and Open Competition (10C)']),
    ('other_than_full_and_o_desc', ['Other than Full and Open Competition Description Tag',
                                    'Other than Full and Open Competition Description Tag ()']),
    ('number_of_offers_received', ['Number of Offers Received', 'Number of Offers Received (10D)']),
    ('commercial_item_acquisitio', ['Commercial Item Acquisition Procedures',
                                    'Commercial Item Acquisition Procedures (10H)']),
    ('commercial_item_acqui_desc', ['Commercial Item Acquisition Procedures Description Tag',
                                    'Commercial Item Acquisition Procedures Description Tag ()']),
    ('small_business_competitive', ['Small Business Competitiveness Demonstration Program',
                                    'Small Business Competitiveness Demonstration Program (10K)']),
    ('commercial_item_test_progr', ['Simplified Procedures for Certain Commercial Items',
                                    'Simplified Procedures for Certain Commercial Items (10J)']),
    ('commercial_item_test_desc', ['Commercial Item Test Program Description Tag',
                                   'Commercial Item Test Program Description Tag ()']),
    ('a_76_fair_act_action', ['A-76 FAIR Act Action', 'A-76 FAIR Act Action (10L)']),
    ('a_76_fair_act_action_desc', ['A-76 FAIR Act Action Description Tag',
                                   'A-76 FAIR Act Action Description Tag ()']),
    ('fed_biz_opps', ['Contract Opportunities Notice', 'Contract Opportunities Notice (10S)']),
    ('fed_biz_opps_description', ['Contract Opportunities Notice Description Tag',
                                  'Contract Opportunities Notice Description Tag ()']),
    ('local_area_set_aside', ['Local Area Set Aside', 'Local Area Set Aside (10U)']),
    ('local_area_set_aside_desc', ['Local Area Set Aside Description Tag',
                                   'Local Area Set Aside Description Tag ()']),
    ('price_evaluation_adjustmen', ['Price Evaluation Adjustment Preference Percent Difference',
                                    'Price Evaluation Adjustment Preference Percent Difference (11C)']),
    ('clinger_cohen_act_planning', ['Clinger-Cohen Act Planning Compliance',
                                    'Clinger-Cohen Act Planning Compliance (7A)']),
    ('clinger_cohen_act_pla_desc', ['Clinger-Cohen Act Planning Compliance Description Tag',
                                    'Clinger-Cohen Act Planning Compliance Description Tag ()']),
    ('materials_supplies_article', ['Materials, Supplies, Articles & Equip',
                                    'Materials, Supplies, Articles & Equip (7B)']),
    ('materials_supplies_descrip', ['Materials, Supplies, Articles & Equip Description Tag',
                                    'Materials, Supplies, Articles & Equip Description Tag ()']),
    ('labor_standards', ['Labor Standards', 'Labor Standards (7C)']),
    ('labor_standards_descrip', ['Labor Standards Description Tag', 'Labor Standards Description Tag ()']),
    ('construction_wage_rate_req', ['Construction Wage Rate Requirements', 'Construction Wage Rate Requirements (7D)']),
    ('construction_wage_rat_desc', ['Construction Wage Rate Requirements Description Tag',
                                    'Construction Wage Rate Requirements Description Tag ()']),
    ('interagency_contracting_au', ['Interagency Contracting Authority', 'Interagency Contracting Authority (7E)']),
    ('interagency_contract_desc', ['Interagency Contracting Authority Description Tag',
                                   'Interagency Contracting Authority Description Tag ()']),
    ('other_statutory_authority', ['Other Statutory Authority', 'Other Statutory Authority (7F)']),
    ('additional_reporting', ['Additional Reporting', 'Additional Reporting (7G)']),
    ('program_acronym', ['Program Acronym', 'Program Acronym (6P)']),
    ('referenced_idv_type', ['Referenced IDV Type', 'Referenced IDV Type ()']),
    ('referenced_idv_type_desc', ['Referenced IDV Type Description Tag', 'Referenced IDV Type Description Tag ()']),
    ('referenced_mult_or_single', ['Referenced IDV Multiple or Single', 'Referenced IDV Multiple or Single ()']),
    ('referenced_mult_or_si_desc', ['Referenced IDV Multiple or Single Description Tag',
                                    'Referenced IDV Multiple or Single Description Tag ()']),
    ('major_program', ['Major program', 'Major program (6G)']),
    ('national_interest_action', ['National Interest Action', 'National Interest Action (6R)']),
    ('national_interest_desc', ['National Interest Action Description Tag',
                                'National Interest Action Description Tag ()']),
    ('cost_or_pricing_data', ['Cost or Pricing Data', 'Cost or Pricing Data (6J)']),
    ('cost_or_pricing_data_desc', ['Cost or Pricing Data Description Tag',
                                   'Cost or Pricing Data Description Tag ()']),
    ('cost_accounting_standards', ['Cost Accounting Standards Clause', 'Cost Accounting Standards Clause (6L)']),
    ('cost_accounting_stand_desc', ['Cost Accounting Standards Clause Description Tag',
                                    'Cost Accounting Standards Clause Description Tag ()']),
    ('government_furnished_prope', ['Government Furnished Property (GFP)', 'Government Furnished Property (GFP) (8J)']),
    ('government_furnished_desc', ['Government Furnished Property GFP Description Tag',
                                   'Government Furnished Property GFP Description Tag ()']),
    ('sea_transportation', ['Sea Transportation', 'Sea Transportation (8M)']),
    ('sea_transportation_desc', ['Sea Transportation Description Tag', 'Sea Transportation Description Tag ()']),
    ('undefinitized_action', ['Undefinitized Action', 'Undefinitized Action (6B)']),
    ('undefinitized_action_desc', ['Undefinitized Action Description Tag',
                                   'Undefinitized Action Description Tag ()']),
    ('consolidated_contract', ['Consolidated Contract', 'Consolidated Contract (8P)']),
    ('consolidated_contract_desc', ['Consolidated Contract Description Tag',
                                    'Consolidated Contract Description Tag ()']),
    ('performance_based_service', ['Performance-Based Service Acquisition',
                                   'Performance-Based Service Acquisition (6F)']),
    ('performance_based_se_desc', ['Performance-Based Service Acquisition Description Tag',
                                   'Performance-Based Service Acquisition Description Tag ()']),
    ('multi_year_contract', ['Multi Year Contract', 'Multi Year Contract (6C)']),
    ('multi_year_contract_desc', ['Multi Year Contract Description Tag', 'Multi Year Contract Description Tag ()']),
    ('contract_financing', ['Contract Financing', 'Contract Financing (6K)']),
    ('contract_financing_descrip', ['Contract Financing Description Tag', 'Contract Financing Description Tag ()']),
    ('purchase_card_as_payment_m', ['Purchase Card as Payment Method', 'Purchase Card as Payment Method (6N)']),
    ('purchase_card_as_paym_desc', ['Purchase Card as Payment Method Description Tag',
                                    'Purchase Card as Payment Method Description Tag ()']),
    ('contingency_humanitarian_o', ['Emergency Acquisition', 'Emergency Acquisition (6H)']),
    ('contingency_humanitar_desc', ['Emergency Acquisition Description Tag',
                                    'Emergency Acquisition Description Tag ()']),
    ('alaskan_native_owned_corpo', ['Alaskan Native Corporation Owned Firm',
                                    'Alaskan Native Corporation Owned Firm (13YA)']),
    ('american_indian_owned_busi', ['American Indian Owned Business', 'American Indian Owned Business (13Y)']),
    ('indian_tribe_federally_rec', ['Indian Tribe Federally Recognized', 'Indian Tribe Federally Recognized (13YB)']),
    ('native_hawaiian_owned_busi', ['Native Hawaiian Organization Owned Firm',
                                    'Native Hawaiian Organization Owned Firm (13YC)']),
    ('tribally_owned_business', ['Tribally Owned Firm', 'Tribally Owned Firm (13YD)']),
    ('veteran_owned_business', ['Veteran Owned Business', 'Veteran Owned Business (13V)']),
    ('service_disabled_veteran_o', ['Service Disabled Veteran Owned Business',
                                    'Service Disabled Veteran Owned Business (13W)']),
    ('woman_owned_business', ['Woman Owned Business', 'Woman Owned Business (13U)']),
    ('women_owned_small_business', ['Women Owned Small Business', 'Women Owned Small Business (13UB)']),
    ('economically_disadvantaged', ['Economically Disadvantaged Women Owned Small Business',
                                    'Economically Disadvantaged Women Owned Small Business (13UC)']),
    ('joint_venture_women_owned', ['Joint Venture Women Owned Small Business',
                                   'Joint Venture Women Owned Small Business (13UD)']),
    ('joint_venture_economically', ['Joint Venture Economically Disadvantaged Women Owned Small Business',
                                    'Joint Venture Economically Disadvantaged Women Owned Small Business (13UE)']),
    ('minority_owned_business', ['Minority Owned Business', 'Minority Owned Business (13UA)']),
    ('subcontinent_asian_asian_i', ['Subcontinent Asian Asian - Indian American Owned Business',
                                    'Subcontinent Asian Asian - Indian American Owned Business (13DD)']),
    ('asian_pacific_american_own', ['Asian Pacific American Owned Business',
                                    'Asian Pacific American Owned Business (13Z)']),
    ('black_american_owned_busin', ['Black American Owned Business', 'Black American Owned Business (13AA)']),
    ('hispanic_american_owned_bu', ['Hispanic American Owned Business', 'Hispanic American Owned Business (13BB)']),
    ('native_american_owned_busi', ['Native American Owned Business', 'Native American Owned Business (13CC)']),
    ('other_minority_owned_busin', ['Other Minority Owned Business', 'Other Minority Owned Business (13DE)']),
    ('contracting_officers_deter', ['Contracting Officer\'s Determination of Business Size',
                                    'Contracting Officer\'s Determination of Business Size (11A)']),
    ('contracting_officers_desc', ['Contracting Officer\'s Determination of Business Size Description Tag',
                                   'Contracting Officer\'s Determination of Business Size Description Tag ()']),
    ('emerging_small_business', ['Emerging Small Business', 'Emerging Small Business (13M)']),
    ('community_developed_corpor', ['Community Developed Corporation Owned Firm',
                                    'Community Developed Corporation Owned Firm (13LB)']),
    ('labor_surplus_area_firm', ['Labor Surplus Area Firm', 'Labor Surplus Area Firm (13LC)']),
    ('us_federal_government', ['U.S. Federal Government', 'U.S. Federal Government (13K)']),
    ('federally_funded_research', ['Federally Funded Research and Development Corp',
                                   'Federally Funded Research and Development Corp (13KB)']),
    ('federal_agency', ['Federal Agency', 'Federal Agency (13KA)']),
    ('us_state_government', ['U.S. State Government', 'U.S. State Government (13J)']),
    ('us_local_government', ['U.S. Local Government', 'U.S. Local Government (13I)']),
    ('city_local_government', ['City Local Government', 'City Local Government (13IA)']),
    ('county_local_government', ['County Local Government', 'County Local Government (13IB)']),
    ('inter_municipal_local_gove', ['Inter-Municipal Local Government', 'Inter-Municipal Local Government (13IC)']),
    ('local_government_owned', ['Local Government Owned', 'Local Government Owned (13ID)']),
    ('municipality_local_governm', ['Municipality Local Government', 'Municipality Local Government (13IE)']),
    ('school_district_local_gove', ['School District Local Government', 'School District Local Government (13IF)']),
    ('township_local_government', ['Township Local Government', 'Township Local Government (13IG)']),
    ('us_tribal_government', ['U.S. Tribal Government', 'U.S. Tribal Government (13L)']),
    ('foreign_government', ['Foreign Government', 'Foreign Government (13LA)']),
    ('organizational_type', ['OrganizationalType', 'OrganizationalType ()']),
    ('corporate_entity_not_tax_e', ['Corporate Entity Not Tax Exempt', 'Corporate Entity Not Tax Exempt (13LD)']),
    ('corporate_entity_tax_exemp', ['Corporate Entity Tax Exempt', 'Corporate Entity Tax Exempt (13LE)']),
    ('partnership_or_limited_lia', ['Partnership or Limited Liability Partnership',
                                    'Partnership or Limited Liability Partnership (13LF)']),
    ('sole_proprietorship', ['Sole Proprietorship', 'Sole Proprietorship (13LG)']),
    ('small_agricultural_coopera', ['Small Agricultural Cooperative', 'Small Agricultural Cooperative (13LH)']),
    ('international_organization', ['International Organization', 'International Organization (13LI)']),
    ('us_government_entity', ['U.S. Government Entity', 'U.S. Government Entity (13LJ)']),
    ('community_development_corp', ['Community Development Corporation', 'Community Development Corporation (13TB)']),
    ('domestic_shelter', ['Domestic Shelter', 'Domestic Shelter (13TD)']),
    ('educational_institution', ['Educational Institution', 'Educational Institution (13T)']),
    ('foundation', ['Foundation', 'Foundation (13TJ)']),
    ('hospital_flag', ['Hospital Flag', 'Hospital Flag (13VV)']),
    ('manufacturer_of_goods', ['Manufacturer of Goods', 'Manufacturer of Goods (13TE)']),
    ('veterinary_hospital', ['Veterinary Hospital', 'Veterinary Hospital (13TH)']),
    ('hispanic_servicing_institu', ['Hispanic Servicing Institution', 'Hispanic Servicing Institution (13TI)']),
    ('contracts', ['Contracts', 'Contracts (13X)']),
    ('grants', ['Federal Assistance Awards', 'Federal Assistance Awards (13XA)']),
    ('receives_contracts_and_gra', ['All Awards', 'All Awards (13XB)']),
    ('airport_authority', ['Airport Authority', 'Airport Authority (13XC)']),
    ('council_of_governments', ['Council of Governments', 'Council of Governments (13XD)']),
    ('housing_authorities_public', ['Housing Authorities Public/Tribal', 'Housing Authorities Public/Tribal (13XE)']),
    ('interstate_entity', ['Interstate Entity', 'Interstate Entity (13XF)']),
    ('planning_commission', ['Planning Commission', 'Planning Commission (13XG)']),
    ('port_authority', ['Port Authority', 'Port Authority (13XH)']),
    ('transit_authority', ['Transit Authority', 'Transit Authority (13XI)']),
    ('subchapter_s_corporation', ['Subchapter S Corporation', 'Subchapter S Corporation (13XJ)']),
    ('limited_liability_corporat', ['Limited Liability Corporation', 'Limited Liability Corporation (13XK)']),
    ('foreign_owned_and_located', ['Foreign Owned', 'Foreign Owned (13XL)']),
    ('for_profit_organization', ['For Profit Organization', 'For Profit Organization (13EA)']),
    ('nonprofit_organization', ['Nonprofit Organization', 'Nonprofit Organization (13EE)']),
    ('other_not_for_profit_organ', ['Other Not For Profit Organization', 'Other Not For Profit Organization (13EF)']),
    ('the_ability_one_program', ['The AbilityOne Program', 'The AbilityOne Program (13Q)']),
    ('private_university_or_coll', ['Private University or College', 'Private University or College (13SA)']),
    ('state_controlled_instituti', ['State Controlled Institution of Higher Learning',
                                    'State Controlled Institution of Higher Learning (13SC)']),
    ('c1862_land_grant_college', ['1862 Land Grant College', '1862 Land Grant College (13RA)']),
    ('c1890_land_grant_college', ['1890 Land Grant College', '1890 Land Grant College (13RB)']),
    ('c1994_land_grant_college', ['1994 Land Grant College', '1994 Land Grant College (13RC)']),
    ('minority_institution', ['Minority Institution', 'Minority Institution (13S)']),
    ('historically_black_college', ['Historically Black College or University',
                                    'Historically Black College or University (13R)']),
    ('tribal_college', ['Tribal College', 'Tribal College (13SD)']),
    ('alaskan_native_servicing_i', ['Alaskan Native Servicing Institution',
                                    'Alaskan Native Servicing Institution (13SF)']),
    ('native_hawaiian_servicing', ['Native Hawaiian Servicing Institution',
                                   'Native Hawaiian Servicing Institution (13SG)']),
    ('school_of_forestry', ['School of Forestry', 'School of Forestry (13SB)']),
    ('veterinary_college', ['Veterinary College', 'Veterinary College (13SE)']),
    ('dot_certified_disadvantage', ['DoT Certified Disadvantaged Business Enterprise',
                                    'DoT Certified Disadvantaged Business Enterprise (13NB)']),
    ('self_certified_small_disad', ['Self-Certified Small Disadvantaged Business',
                                    'Self-Certified Small Disadvantaged Business (13NC)']),
    ('small_disadvantaged_busine', ['Small Disadvantaged Business', 'Small Disadvantaged Business (13P)']),
    ('c8a_program_participant', ['8a Program Participant', '8a Program Participant (13N)']),
    ('historically_underutilized', ['Historically Underutilized Business Zone HUBZone Firm',
                                    'Historically Underutilized Business Zone HUBZone Firm (13O)']),
    ('sba_certified_8_a_joint_ve', ['SBA Certified 8 a Joint Venture', 'SBA Certified 8 a Joint Venture (13NA)']),
    ('last_modified', ['LastModifiedDate', 'LastModifiedDate (2F)'])
])
db_columns = [key for key in mapping]


def query_data(session, agency_code, agency_type, start, end):
    """ Request D1 file data

        Args:
            session: DB session
            agency_code: FREC or CGAC code for generation
            agency_type: The type of agency (awarding or funding) to generate the file for
            start: Beginning of period for D file
            end: End of period for D file

        Returns:
            The rows using the provided dates for the given agency.
    """
    rows = initial_query(session).\
        filter(func.cast_as_date(file_model.action_date) >= start).\
        filter(func.cast_as_date(file_model.action_date) <= end)

    # Funding or awarding agency filtering
    if agency_type == 'funding':
        rows = rows.filter(file_model.funding_agency_code == agency_code)
    else:
        rows = rows.filter(file_model.awarding_agency_code == agency_code)

    return rows


def initial_query(session):
    return session.query(*[
        file_model.detached_award_proc_unique,
        file_model.unique_award_key,
        file_model.piid,
        file_model.award_modification_amendme,
        file_model.transaction_number,
        file_model.referenced_idv_agency_iden,
        file_model.referenced_idv_agency_desc,
        file_model.parent_award_id,
        file_model.referenced_idv_modificatio,
        file_model.federal_action_obligation,
        file_model.total_obligated_amount,
        file_model.base_exercised_options_val,
        file_model.current_total_value_award,
        file_model.base_and_all_options_value,
        file_model.potential_total_value_awar,
        func.to_char(cast(file_model.action_date, Date), 'YYYYMMDD'),
        func.to_char(cast(file_model.period_of_performance_star, Date), 'YYYYMMDD'),
        func.to_char(cast(file_model.period_of_performance_curr, Date), 'YYYYMMDD'),
        func.to_char(cast(file_model.period_of_perf_potential_e, Date), 'YYYYMMDD'),
        func.to_char(cast(file_model.ordering_period_end_date, Date), 'YYYYMMDD'),
        func.to_char(cast(file_model.solicitation_date, Date), 'YYYYMMDD'),
        file_model.awarding_agency_code,
        file_model.awarding_agency_name,
        file_model.awarding_sub_tier_agency_c,
        file_model.awarding_sub_tier_agency_n,
        file_model.awarding_office_code,
        file_model.awarding_office_name,
        file_model.funding_agency_code,
        file_model.funding_agency_name,
        file_model.funding_sub_tier_agency_co,
        file_model.funding_sub_tier_agency_na,
        file_model.funding_office_code,
        file_model.funding_office_name,
        file_model.foreign_funding,
        file_model.foreign_funding_desc,
        file_model.sam_exception,
        file_model.sam_exception_description,
        file_model.awardee_or_recipient_uei,
        file_model.awardee_or_recipient_legal,
        file_model.vendor_doing_as_business_n,
        file_model.cage_code,
        file_model.ultimate_parent_uei,
        file_model.ultimate_parent_legal_enti,
        file_model.legal_entity_country_code,
        file_model.legal_entity_country_name,
        file_model.legal_entity_address_line1,
        file_model.legal_entity_address_line2,
        file_model.legal_entity_city_name,
        file_model.legal_entity_state_code,
        file_model.legal_entity_state_descrip,
        file_model.legal_entity_zip4,
        file_model.legal_entity_congressional,
        file_model.vendor_phone_number,
        file_model.vendor_fax_number,
        file_model.place_of_perform_city_name,
        file_model.place_of_perform_county_na,
        file_model.place_of_performance_state,
        file_model.place_of_perfor_state_desc,
        file_model.place_of_performance_zip4a,
        file_model.place_of_performance_congr,
        file_model.place_of_perform_country_c,
        file_model.place_of_perf_country_desc,
        file_model.pulled_from,
        file_model.contract_award_type,
        file_model.contract_award_type_desc,
        file_model.idv_type,
        file_model.idv_type_description,
        file_model.multiple_or_single_award_i,
        file_model.multiple_or_single_aw_desc,
        file_model.type_of_idc,
        file_model.type_of_idc_description,
        file_model.type_of_contract_pricing,
        file_model.type_of_contract_pric_desc,
        file_model.award_description,
        file_model.action_type,
        file_model.action_type_description,
        file_model.solicitation_identifier,
        file_model.number_of_actions,
        file_model.inherently_government_func,
        file_model.inherently_government_desc,
        file_model.product_or_service_code,
        file_model.product_or_service_co_desc,
        file_model.contract_bundling,
        file_model.contract_bundling_descrip,
        file_model.dod_claimant_program_code,
        file_model.dod_claimant_prog_cod_desc,
        file_model.naics,
        file_model.naics_description,
        file_model.recovered_materials_sustai,
        file_model.recovered_materials_s_desc,
        file_model.domestic_or_foreign_entity,
        file_model.domestic_or_foreign_e_desc,
        file_model.program_system_or_equipmen,
        file_model.program_system_or_equ_desc,
        file_model.information_technology_com,
        file_model.information_technolog_desc,
        file_model.epa_designated_product,
        file_model.epa_designated_produc_desc,
        file_model.country_of_product_or_serv,
        file_model.country_of_product_or_desc,
        file_model.place_of_manufacture,
        file_model.place_of_manufacture_desc,
        file_model.subcontracting_plan,
        file_model.subcontracting_plan_desc,
        file_model.extent_competed,
        file_model.extent_compete_description,
        file_model.solicitation_procedures,
        file_model.solicitation_procedur_desc,
        file_model.type_set_aside,
        file_model.type_set_aside_description,
        file_model.evaluated_preference,
        file_model.evaluated_preference_desc,
        file_model.research,
        file_model.research_description,
        file_model.fair_opportunity_limited_s,
        file_model.fair_opportunity_limi_desc,
        file_model.other_than_full_and_open_c,
        file_model.other_than_full_and_o_desc,
        file_model.number_of_offers_received,
        file_model.commercial_item_acquisitio,
        file_model.commercial_item_acqui_desc,
        file_model.small_business_competitive,
        file_model.commercial_item_test_progr,
        file_model.commercial_item_test_desc,
        file_model.a_76_fair_act_action,
        file_model.a_76_fair_act_action_desc,
        file_model.fed_biz_opps,
        file_model.fed_biz_opps_description,
        file_model.local_area_set_aside,
        file_model.local_area_set_aside_desc,
        file_model.price_evaluation_adjustmen,
        file_model.clinger_cohen_act_planning,
        file_model.clinger_cohen_act_pla_desc,
        file_model.materials_supplies_article,
        file_model.materials_supplies_descrip,
        file_model.labor_standards,
        file_model.labor_standards_descrip,
        file_model.construction_wage_rate_req,
        file_model.construction_wage_rat_desc,
        file_model.interagency_contracting_au,
        file_model.interagency_contract_desc,
        file_model.other_statutory_authority,
        file_model.additional_reporting,
        file_model.program_acronym,
        file_model.referenced_idv_type,
        file_model.referenced_idv_type_desc,
        file_model.referenced_mult_or_single,
        file_model.referenced_mult_or_si_desc,
        file_model.major_program,
        file_model.national_interest_action,
        file_model.national_interest_desc,
        file_model.cost_or_pricing_data,
        file_model.cost_or_pricing_data_desc,
        file_model.cost_accounting_standards,
        file_model.cost_accounting_stand_desc,
        file_model.government_furnished_prope,
        file_model.government_furnished_desc,
        file_model.sea_transportation,
        file_model.sea_transportation_desc,
        file_model.undefinitized_action,
        file_model.undefinitized_action_desc,
        file_model.consolidated_contract,
        file_model.consolidated_contract_desc,
        file_model.performance_based_service,
        file_model.performance_based_se_desc,
        file_model.multi_year_contract,
        file_model.multi_year_contract_desc,
        file_model.contract_financing,
        file_model.contract_financing_descrip,
        file_model.purchase_card_as_payment_m,
        file_model.purchase_card_as_paym_desc,
        file_model.contingency_humanitarian_o,
        file_model.contingency_humanitar_desc,
        file_model.alaskan_native_owned_corpo,
        file_model.american_indian_owned_busi,
        file_model.indian_tribe_federally_rec,
        file_model.native_hawaiian_owned_busi,
        file_model.tribally_owned_business,
        file_model.veteran_owned_business,
        file_model.service_disabled_veteran_o,
        file_model.woman_owned_business,
        file_model.women_owned_small_business,
        file_model.economically_disadvantaged,
        file_model.joint_venture_women_owned,
        file_model.joint_venture_economically,
        file_model.minority_owned_business,
        file_model.subcontinent_asian_asian_i,
        file_model.asian_pacific_american_own,
        file_model.black_american_owned_busin,
        file_model.hispanic_american_owned_bu,
        file_model.native_american_owned_busi,
        file_model.other_minority_owned_busin,
        file_model.contracting_officers_deter,
        file_model.contracting_officers_desc,
        file_model.emerging_small_business,
        file_model.community_developed_corpor,
        file_model.labor_surplus_area_firm,
        file_model.us_federal_government,
        file_model.federally_funded_research,
        file_model.federal_agency,
        file_model.us_state_government,
        file_model.us_local_government,
        file_model.city_local_government,
        file_model.county_local_government,
        file_model.inter_municipal_local_gove,
        file_model.local_government_owned,
        file_model.municipality_local_governm,
        file_model.school_district_local_gove,
        file_model.township_local_government,
        file_model.us_tribal_government,
        file_model.foreign_government,
        file_model.organizational_type,
        file_model.corporate_entity_not_tax_e,
        file_model.corporate_entity_tax_exemp,
        file_model.partnership_or_limited_lia,
        file_model.sole_proprietorship,
        file_model.small_agricultural_coopera,
        file_model.international_organization,
        file_model.us_government_entity,
        file_model.community_development_corp,
        file_model.domestic_shelter,
        file_model.educational_institution,
        file_model.foundation,
        file_model.hospital_flag,
        file_model.manufacturer_of_goods,
        file_model.veterinary_hospital,
        file_model.hispanic_servicing_institu,
        file_model.contracts,
        file_model.grants,
        file_model.receives_contracts_and_gra,
        file_model.airport_authority,
        file_model.council_of_governments,
        file_model.housing_authorities_public,
        file_model.interstate_entity,
        file_model.planning_commission,
        file_model.port_authority,
        file_model.transit_authority,
        file_model.subchapter_s_corporation,
        file_model.limited_liability_corporat,
        file_model.foreign_owned_and_located,
        file_model.for_profit_organization,
        file_model.nonprofit_organization,
        file_model.other_not_for_profit_organ,
        file_model.the_ability_one_program,
        file_model.private_university_or_coll,
        file_model.state_controlled_instituti,
        file_model.c1862_land_grant_college,
        file_model.c1890_land_grant_college,
        file_model.c1994_land_grant_college,
        file_model.minority_institution,
        file_model.historically_black_college,
        file_model.tribal_college,
        file_model.alaskan_native_servicing_i,
        file_model.native_hawaiian_servicing,
        file_model.school_of_forestry,
        file_model.veterinary_college,
        file_model.dot_certified_disadvantage,
        file_model.self_certified_small_disad,
        file_model.small_disadvantaged_busine,
        file_model.c8a_program_participant,
        file_model.historically_underutilized,
        file_model.sba_certified_8_a_joint_ve,
        func.to_char(cast(file_model.last_modified, Date), 'YYYYMMDD')])
