Stored Procedure

DROP TABLE IF EXISTS usaspending_staging;

CREATE TABLE usaspending_staging (
    unique_transaction_id  BINARY(16) /*PRIMARY KEY*/,
    transaction_status varchar(45),
    obligatedamount varchar(45),
    baseandexercisedoptionsvalue varchar(45),
    baseandalloptionsvalue varchar(45),
    maj_agency_cat varchar(45),
    mod_agency varchar(45),
    maj_fund_agency_cat varchar(45),
    contractingofficeagencyid varchar(45),
    contractingofficeid varchar(45),
    fundingrequestingagencyid varchar(45),
    fundingrequestingofficeid varchar(45),
    fundedbyforeignentity varchar(45),
    signeddate varchar(45),
    effectivedate varchar(45),
    currentcompletiondate varchar(45),
    ultimatecompletiondate varchar(45),
    lastdatetoorder varchar(45),
    contractactiontype varchar(45),
    reasonformodification varchar(45),
    typeofcontractpricing varchar(45),
    priceevaluationpercentdifference varchar(10),
    subcontractplan varchar(100),
    type_of_contract varchar(50),
    lettercontract varchar(100),
    multiyearcontract varchar(100),
    performancebasedservicecontract varchar(100),
    majorprogramcode varchar(100),
    contingencyhumanitarianpeacekeepingoperation varchar(100),
    contractfinancing varchar(100),
    costorpricingdata varchar(100),
    costaccountingstandardsclause varchar(100),
    descriptionofcontractrequirement varchar(100),
    purchasecardaspaymentmethod varchar(100),
    numberofactions varchar(100),
    nationalinterestactioncode varchar(100),
    progsourceagency varchar(100),
    progsourceaccount varchar(100),
    progsourcesubacct varchar(100),
    account_title varchar(100),
    rec_flag varchar(100),
    typeofidc varchar(100),
    multipleorsingleawardidc varchar(100),
    programacronym varchar(100),
    vendorname varchar(100),
    vendoralternatename varchar(100),
    vendorlegalorganizationname varchar(100),
    vendordoingasbusinessname varchar(100),
    divisionname varchar(100),
    divisionnumberorofficecode varchar(100),
    vendorenabled varchar(100),
    vendorlocationdisableflag varchar(100),
    ccrexception varchar(100),
    streetaddress varchar(100),
    streetaddress2 varchar(100),
    streetaddress3 varchar(100),
    city varchar(100),
    state varchar(100),
    zipcode varchar(100),
    vendorcountrycode varchar(100),
    vendor_state_code varchar(100),
    vendor_cd varchar(100),
    congressionaldistrict varchar(100),
    vendorsitecode varchar(100),
    vendoralternatesitecode varchar(100),
    dunsnumber varchar(100),
    parentdunsnumber varchar(100),
    phoneno varchar(100),
    faxno varchar(100),
    registrationdate varchar(100),
    renewaldate varchar(100),
    mod_parent varchar(100),
    locationcode varchar(100),
    statecode varchar(100),
    pop_state_code varchar(100),
    placeofperformancecountrycode varchar(100),
    placeofperformancezipcode varchar(100),
    pop_cd varchar(100),
    placeofperformancecongressionaldistrict varchar(100),
    psc_cat varchar(100),
    productorservicecode varchar(100),
    systemequipmentcode varchar(100),
    claimantprogramcode varchar(100),
    principalnaicscode varchar(100),
    informationtechnologycommercialitemcategory varchar(100),
    gfe_gfp varchar(100),
    useofepadesignatedproducts varchar(100),
    recoveredmaterialclauses varchar(100),
    seatransportation varchar(100),
    contractbundling varchar(100),
    consolidatedcontract varchar(100),
    countryoforigin varchar(100),
    placeofmanufacture varchar(100),
    manufacturingorganizationtype varchar(100),
    agencyid varchar(100),
    piid varchar(100),
    modnumber varchar(100),
    transactionnumber varchar(100),
    fiscal_year varchar(100),
    idvagencyid varchar(100),
    idvpiid varchar(100),
    idvmodificationnumber varchar(100),
    solicitationid varchar(100),
    extentcompeted varchar(100),
    reasonnotcompeted varchar(100),
    numberofoffersreceived varchar(100),
    commercialitemacquisitionprocedures varchar(100),
    commercialitemtestprogram varchar(100),
    smallbusinesscompetitivenessdemonstrationprogram varchar(100),
    a76action varchar(100),
    competitiveprocedures varchar(100),
    solicitationprocedures varchar(100),
    typeofsetaside varchar(100),
    localareasetaside varchar(100),
    evaluatedpreference varchar(100),
    fedbizopps varchar(100),
    research varchar(100),
    statutoryexceptiontofairopportunity varchar(100),
    organizationaltype varchar(100),
    numberofemployees varchar(100),
    annualrevenue varchar(100),
    firm8aflag varchar(100),
    hubzoneflag varchar(100),
    sdbflag varchar(100),
    issbacertifiedsmalldisadvantagedbusiness varchar(100),
    shelteredworkshopflag varchar(100),
    hbcuflag varchar(100),
    educationalinstitutionflag varchar(100),
    womenownedflag varchar(100),
    veteranownedflag varchar(100),
    srdvobflag varchar(100),
    localgovernmentflag varchar(100),
    minorityinstitutionflag varchar(100),
    aiobflag varchar(100),
    stategovernmentflag varchar(100),
    federalgovernmentflag varchar(100),
    minorityownedbusinessflag varchar(100),
    apaobflag varchar(100),
    tribalgovernmentflag varchar(100),
    baobflag varchar(100),
    naobflag varchar(100),
    saaobflag varchar(100),
    nonprofitorganizationflag varchar(100),
    isothernotforprofitorganization varchar(100),
    isforprofitorganization varchar(100),
    isfoundation varchar(100),
    haobflag varchar(100),
    ishispanicservicinginstitution varchar(100),
    verysmallbusinessflag varchar(100),
    hospitalflag varchar(100),
    contractingofficerbusinesssizedetermination varchar(100),
    is1862landgrantcollege varchar(100),
    is1890landgrantcollege varchar(100),
    is1994landgrantcollege varchar(100),
    isveterinarycollege varchar(100),
    isveterinaryhospital varchar(100),
    isprivateuniversityorcollege varchar(100),
    isschoolofforestry varchar(100),
    isstatecontrolledinstitutionofhigherlearning varchar(100),
    isserviceprovider varchar(100),
    receivescontracts varchar(100),
    receivesgrants varchar(100),
    receivescontractsandgrants varchar(100),
    isairportauthority varchar(100),
    iscouncilofgovernments varchar(100),
    ishousingauthoritiespublicortribal varchar(100),
    isinterstateentity varchar(100),
    isplanningcommission varchar(100),
    isportauthority varchar(100),
    istransitauthority varchar(100),
    issubchapterscorporation varchar(100),
    islimitedliabilitycorporation varchar(100),
    isforeignownedandlocated varchar(100),
    isarchitectureandengineering varchar(100),
    isdotcertifieddisadvantagedbusinessenterprise varchar(100),
    iscitylocalgovernment varchar(100),
    iscommunitydevelopedcorporationownedfirm varchar(100),
    iscommunitydevelopmentcorporation varchar(100),
    isconstructionfirm varchar(100),
    ismanufacturerofgoods varchar(100),
    iscorporateentitynottaxexempt varchar(100),
    iscountylocalgovernment varchar(100),
    isdomesticshelter varchar(100),
    isfederalgovernmentagency varchar(100),
    isfederallyfundedresearchanddevelopmentcorp varchar(100),
    isforeigngovernment varchar(100),
    isindiantribe varchar(100),
    isintermunicipallocalgovernment varchar(100),
    isinternationalorganization varchar(100),
    islaborsurplusareafirm varchar(100),
    islocalgovernmentowned varchar(100),
    ismunicipalitylocalgovernment varchar(100),
    isnativehawaiianownedorganizationorfirm varchar(100),
    isotherbusinessororganization varchar(100),
    isotherminorityowned varchar(100),
    ispartnershiporlimitedliabilitypartnership varchar(100),
    isschooldistrictlocalgovernment varchar(100),
    issmallagriculturalcooperative varchar(100),
    issoleproprietorship varchar(100),
    istownshiplocalgovernment varchar(100),
    istriballyownedfirm varchar(100),
    istribalcollege varchar(100),
    isalaskannativeownedcorporationorfirm varchar(100),
    iscorporateentitytaxexempt varchar(100),
    walshhealyact varchar(100),
    servicecontractact varchar(100),
    davisbaconact varchar(100),
    clingercohenact varchar(100),
    otherstatutoryauthority varchar(100),
    interagencycontractingauthority varchar(100)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- load data

truncate table usaspending_staging;

start transaction;

LOAD DATA CONCURRENT INFILE '2000_All_Contracts_Full_20110401.csv'
    IGNORE
    INTO TABLE `usaspending_staging`
    FIELDS TERMINATED BY ','
    -- OPTIONALLY ENCLOSED BY '\"'
    LINES TERMINATED BY '\n'
    -- LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES
    (
        @unique_transaction_id,
        transaction_status,
        obligatedamount,
        baseandexercisedoptionsvalue,
        baseandalloptionsvalue,
        maj_agency_cat,
        mod_agency,
        maj_fund_agency_cat,
        contractingofficeagencyid,
        contractingofficeid,
        fundingrequestingagencyid,
        fundingrequestingofficeid,
        fundedbyforeignentity,
        signeddate,
        effectivedate,
        currentcompletiondate,
        ultimatecompletiondate,
        lastdatetoorder,
        contractactiontype,
        reasonformodification,
        typeofcontractpricing,
        priceevaluationpercentdifference,
        subcontractplan,
        type_of_contract,
        lettercontract,
        multiyearcontract,
        performancebasedservicecontract,
        majorprogramcode,
        contingencyhumanitarianpeacekeepingoperation,
        contractfinancing,
        costorpricingdata,
        costaccountingstandardsclause,
        descriptionofcontractrequirement,
        purchasecardaspaymentmethod,
        numberofactions,
        nationalinterestactioncode,
        progsourceagency,
        progsourceaccount,
        progsourcesubacct,
        account_title,
        rec_flag,
        typeofidc,
        multipleorsingleawardidc,
        programacronym,
        vendorname,
        vendoralternatename,
        vendorlegalorganizationname,
        vendordoingasbusinessname,
        divisionname,
        divisionnumberorofficecode,
        vendorenabled,
        vendorlocationdisableflag,
        ccrexception,
        streetaddress,
        streetaddress2,
        streetaddress3,
        city,
        state,
        zipcode,
        vendorcountrycode,
        vendor_state_code,
        vendor_cd,
        congressionaldistrict,
        vendorsitecode,
        vendoralternatesitecode,
        dunsnumber,
        parentdunsnumber,
        phoneno,
        faxno,
        registrationdate,
        renewaldate,
        mod_parent,
        locationcode,
        statecode,
        pop_state_code,
        placeofperformancecountrycode,
        placeofperformancezipcode,
        pop_cd,
        placeofperformancecongressionaldistrict,
        psc_cat,
        productorservicecode,
        systemequipmentcode,
        claimantprogramcode,
        principalnaicscode,
        informationtechnologycommercialitemcategory,
        gfe_gfp,
        useofepadesignatedproducts,
        recoveredmaterialclauses,
        seatransportation,
        contractbundling,
        consolidatedcontract,
        countryoforigin,
        placeofmanufacture,
        manufacturingorganizationtype,
        agencyid,
        piid,
        modnumber,
        transactionnumber,
        fiscal_year,
        idvagencyid,
        idvpiid,
        idvmodificationnumber,
        solicitationid,
        extentcompeted,
        reasonnotcompeted,
        numberofoffersreceived,
        commercialitemacquisitionprocedures,
        commercialitemtestprogram,
        smallbusinesscompetitivenessdemonstrationprogram,
        a76action,
        competitiveprocedures,
        solicitationprocedures,
        typeofsetaside,
        localareasetaside,
        evaluatedpreference,
        fedbizopps,
        research,
        statutoryexceptiontofairopportunity,
        organizationaltype,
        numberofemployees,
        annualrevenue,
        firm8aflag,
        hubzoneflag,
        sdbflag,
        issbacertifiedsmalldisadvantagedbusiness,
        shelteredworkshopflag,
        hbcuflag,
        educationalinstitutionflag,
        womenownedflag,
        veteranownedflag,
        srdvobflag,
        localgovernmentflag,
        minorityinstitutionflag,
        aiobflag,
        stategovernmentflag,
        federalgovernmentflag,
        minorityownedbusinessflag,
        apaobflag,
        tribalgovernmentflag,
        baobflag,
        naobflag,
        saaobflag,
        nonprofitorganizationflag,
        isothernotforprofitorganization,
        isforprofitorganization,
        isfoundation,
        haobflag,
        ishispanicservicinginstitution,
        verysmallbusinessflag,
        hospitalflag,
        contractingofficerbusinesssizedetermination,
        is1862landgrantcollege,
        is1890landgrantcollege,
        is1994landgrantcollege,
        isveterinarycollege,
        isveterinaryhospital,
        isprivateuniversityorcollege,
        isschoolofforestry,
        isstatecontrolledinstitutionofhigherlearning,
        isserviceprovider,
        receivescontracts,
        receivesgrants,
        receivescontractsandgrants,
        isairportauthority,
        iscouncilofgovernments,
        ishousingauthoritiespublicortribal,
        isinterstateentity,
        isplanningcommission,
        isportauthority,
        istransitauthority,
        issubchapterscorporation,
        islimitedliabilitycorporation,
        isforeignownedandlocated,
        isarchitectureandengineering,
        isdotcertifieddisadvantagedbusinessenterprise,
        iscitylocalgovernment,
        iscommunitydevelopedcorporationownedfirm,
        iscommunitydevelopmentcorporation,
        isconstructionfirm,
        ismanufacturerofgoods,
        iscorporateentitynottaxexempt,
        iscountylocalgovernment,
        isdomesticshelter,
        isfederalgovernmentagency,
        isfederallyfundedresearchanddevelopmentcorp,
        isforeigngovernment,
        isindiantribe,
        isintermunicipallocalgovernment,
        isinternationalorganization,
        islaborsurplusareafirm,
        islocalgovernmentowned,
        ismunicipalitylocalgovernment,
        isnativehawaiianownedorganizationorfirm,
        isotherbusinessororganization,
        isotherminorityowned,
        ispartnershiporlimitedliabilitypartnership,
        isschooldistrictlocalgovernment,
        issmallagriculturalcooperative,
        issoleproprietorship,
        istownshiplocalgovernment,
        istriballyownedfirm,
        istribalcollege,
        isalaskannativeownedcorporationorfirm,
        iscorporateentitytaxexempt,
        walshhealyact,
        servicecontractact,
        davisbaconact,
        clingercohenact,
        otherstatutoryauthority,
        interagencycontractingauthority )
        SET unique_transaction_id = UNHEX(@unique_transaction_id);

commit;

drop procedure if exists process_usaspending;

delimiter //

create procedure process_usaspending()
begin

    -- declare variables
    DECLARE not_found INT;
    DECLARE agency INT;
    DECLARE org INT;
    DECLARE unique_transaction_id BINARY(16);
    DECLARE transaction_status VARCHAR(100);
    DECLARE obligatedamount VARCHAR(100);
    DECLARE maj_agency_cat VARCHAR(100);
    DECLARE mod_agency VARCHAR(100);
    DECLARE effectivedate VARCHAR(100);
    DECLARE ultimatecompletiondate VARCHAR(100);
    DECLARE descriptionofcontractrequirement VARCHAR(100);
    DECLARE account_title VARCHAR(100);
    DECLARE vendorname VARCHAR(100);
    DECLARE vendoralternatename VARCHAR(100);
    DECLARE vendorlegalorganizationname VARCHAR(100);
    DECLARE vendordoingasbusinessname VARCHAR(100);
    DECLARE streetaddress VARCHAR(100);
    DECLARE streetaddress2 VARCHAR(100);
    DECLARE streetaddress3 VARCHAR(100);
    DECLARE city VARCHAR(100);
    DECLARE state VARCHAR(100);
    DECLARE zipcode VARCHAR(100);
    DECLARE vendorcountrycode VARCHAR(100);
    DECLARE vendor_state_code VARCHAR(100);
    DECLARE vendor_cd VARCHAR(100);
    DECLARE dunsnumber VARCHAR(100);
    DECLARE parentdunsnumber VARCHAR(100);
    DECLARE phoneno VARCHAR(100);
    DECLARE faxno VARCHAR(100);
    DECLARE mod_parent VARCHAR(100);
    DECLARE pop_state_code VARCHAR(100);
    DECLARE placeofperformancecountrycode VARCHAR(100);
    DECLARE placeofperformancezipcode VARCHAR(100);
    DECLARE pop_cd VARCHAR(100);
    DECLARE placeofperformancecongressionaldistrict VARCHAR(100);
    DECLARE psc_cat VARCHAR(100);
    DECLARE productorservicecode VARCHAR(100);
    DECLARE principalnaicscode VARCHAR(100);

    -- set cursor to hold each row of staging data
    DECLARE staging_cursor CURSOR FOR SELECT unique_transaction_id, transaction_status, obligatedamount, maj_agency_cat, mod_agency, effectivedate, ultimatecompletiondate, descriptionofcontractrequirement, account_title, vendorname, vendoralternatename, vendorlegalorganizationname, vendordoingasbusinessname, streetaddress, streetaddress2, streetaddress3, city, state, zipcode, vendorcountrycode, vendor_state_code, vendor_cd, dunsnumber, parentdunsnumber, phoneno, faxno, mod_parent, pop_state_code, placeofperformancecountrycode, placeofperformancezipcode, pop_cd, placeofperformancecongressionaldistrict, psc_cat, productorservicecode, principalnaicscode FROM usaspending_staging;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET not_found = 1;
    OPEN staging_cursor;
    REPEAT
        FETCH staging_cursor into unique_transaction_id, transaction_status, obligatedamount, maj_agency_cat, mod_agency, effectivedate, ultimatecompletiondate, descriptionofcontractrequirement, account_title, vendorname, vendoralternatename, vendorlegalorganizationname, vendordoingasbusinessname, streetaddress, streetaddress2, streetaddress3, city, state, zipcode, vendorcountrycode, vendor_state_code, vendor_cd, dunsnumber, parentdunsnumber, phoneno, faxno, mod_parent, pop_state_code, placeofperformancecountrycode, placeofperformancezipcode, pop_cd, placeofperformancecongressionaldistrict, psc_cat, productorservicecode, principalnaicscode;
        -- try and fetch agency
        SET agency = COALESCE(SELECT org_agency_o_id FROM orgs_agencies WHERE org_agency_code = maj_agency_cat,SELECT min(org_id) FROM orgs WHERE org_name LIKE CONCAT('U.S. ',SUBSTRING(maj_agency_cat,5)))
        IF agency = NULL THEN
            INSERT INTO orgs (org_name,org_name_length,org_type,org_sub_types) VALUES (CONCAT('U.S. ',SUBSTRING(maj_agency_cat,5)),LENGTH(CONCAT('U.S. ',SUBSTRING(maj_agency_cat,5))),'org','Org,GovernmentEntity,Federal,Agency');
            SET agency = LAST_INSERT_ID();
        END IF;
        -- try and fetch org
        SET org = COALESCE(SELECT MIN(org_id) FROM orgs WHERE org_name IN (vendorname, vendoralternatename, vendorlegalorganizationname, vendordoingasbusinessname), SELECT MIN(org_alias_org_id) FROM orgs_aliases WHERE org_alias in (endorname, vendoralternatename, vendorlegalorganizationname, vendordoingasbusinessname))
        IF org = NULL THEN
            INSERT INTO orgs(org_name,org_name_length,org_type,org_sub_types,org_created) VALUES (vendorname,LENGTH(vendorname),'org','org',DATE());
            SET org = LAST_INSERT_ID();
        END IF
        -- add aff information
        INSERT INTO affs(aff_e1_id,aff_e1_type,aff_e2_id,aff_e2_type) VALUES (agency,'org',org,'org');
        UNTIL not_found = 1
    END REPEAT
    CLOSE staging_cursor;

end; //

CALL process_usaspending();