#!/bin/bash # Initialize ORESTAR table database cat > table.sql < $name.tab done # remove every transaction from previous session for Smith -- (before 2011-09-12) mv Smith.tab Smith2.tab && cat Smith2.tab | perl -ne '/([0-9]+)/; print if $1 > 1066181' > Smith.tab && rm Smith2.tab # genrate html table with caption and first row of headers echo ' ' # generate some query rows in the html table for i in Brady Hales Smith; do rm -f ${i}.db 2> /dev/null; sqlite3 -init table.sql ${i}.db '.import '"$i"'.tab candetable' 2> /dev/null > /dev/null echo "" echo "" done | perl -pe 's/^([0-9]+)$/${1}.00/g;s/^([0-9]+)\.([0-9])$/${1}.${2}0/g;s/^$/0.00/g;s/^[0-9]+.[0-9][0-9]$/\$$&/g;s/([0-9])([0-9]{3})([0-9]{3})\./$1,$2,$3./g;s/([0-9])([0-9]{3})\./$1,$2./g;' echo "" for i in Brady Hales Smith; do echo $i; sqlite3 $i.db ' select OccptnTxt,sum(Amount) as amt,count(*) as cnt from candetable where SubType like "%Contribution" and BookType = "Individual" group by OccptnTxt collate nocase having amt >= 1000 order by amt desc;'; echo "" done | perl -pe ' BEGIN{ sub average { my $num = shift; my $den = shift; return 0 if $den == 0; return int(($num/$den)*100)/100; } } s/^\|/No Occupation Provided\|/g; s/^(.*)\|(.*?)\|(.*?)$/"$1<\/th>\$$2<\/td>$3<\/td>" . average($2,$3) . "<\/td><\/tr>"/ge; if (! /$_ Large Individual Donations by Occupation (>=\$1000) OccupationTotal AmountCountAverage\n"; }; s/\&/\&/g;' for i in Brady Hales Smith; do echo $i; sqlite3 $i.db ' select EmpName || " " || EmpCity || ", " || EmpState,sum(Amount) as amt,count(*) as cnt from candetable where SubType like "%Contribution" and BookType = "Individual" group by EmpName collate nocase having amt >= 1000 order by amt desc;'; echo "" done | perl -pe ' BEGIN{ sub average { my $num = shift; my $den = shift; return 0 if $den == 0; return int(($num/$den)*100)/100; } } s/^ , \|/No Employer Provided\|/g; s/^(.*)\|(.*?)\|(.*?)$/"$1<\/th>\$$2<\/td>$3<\/td>" . average($2,$3) . "<\/td><\/tr>"/ge; if (! /$_ Large Individual Donations by Employer (>=\$1000) EmployerTotal AmountCountAverage\n"; }; s/\&/\&/g;' for i in Brady Hales Smith; do echo $i; sqlite3 $i.db ' select ContributorPayee || " " || City || ", " || State ,sum(Amount) as amt,count(*) as cnt from candetable where SubType like "%Contribution" and BookType = "Business Entity" group by ContributorPayee collate nocase having amt >= 1000 order by amt desc;'; echo "" done | perl -pe ' BEGIN{ sub average { my $num = shift; my $den = shift; return 0 if $den == 0; return int(($num/$den)*100)/100; } } s/^ , \|/No Business Name Provided\|/g; s/^(.*)\|(.*?)\|(.*?)$/"$1<\/th>\$$2<\/td>$3<\/td>" . average($2,$3) . "<\/td><\/tr>"/ge; if (! /$_ Large Business Donations (>=\$1000) Business NameTotal AmountCountAverage\n"; }; s/\&/\&/g;' for i in Brady Hales Smith; do echo $i; sqlite3 $i.db ' select ContributorPayee || " " || City || ", " || State ,sum(Amount) as amt,count(*) as cnt from candetable where SubType like "%Contribution" and BookType like "Political%" group by ContributorPayee collate nocase having amt >= 1000 order by amt desc;'; echo "" done | perl -pe ' BEGIN{ sub average { my $num = shift; my $den = shift; return 0 if $den == 0; return int(($num/$den)*100)/100; } } s/^ , \|/No Committee Name Provided\|/g; s/^(.*)\|(.*?)\|(.*?)$/"$1<\/th>\$$2<\/td>$3<\/td>" . average($2,$3) . "<\/td><\/tr>"/ge; if (! /$_ Large Political Committee or Party Donations (>=\$1000) Political Committee or Party NameTotal AmountCountAverage\n"; }; s/\&/\&/g;' rm Merged.db; sqlite3 Merged.db ' attach "Brady.db" as Brady; attach "Hales.db" as Hales; attach "Smith.db" as Smith; create table candetable as select "Brady" as candidate,* from Brady.candetable union select "Hales",* from Hales.candetable union select "Smith",* from Smith.candetable; ' sqlite3 Merged.db 'create table multiple as select count(distinct candidate) as cnt, sum(Amount) as amt, ContributorPayee || " " || City || ", " || State as name, (select sum(Amount) as tmpamt from candetable tmp where SubType like "%Contribution" and tmp.candidate = "Brady" and prime.ContributorPayee = tmp.ContributorPayee) as BradyAmount, (select sum(Amount) as tmpamt from candetable tmp where SubType like "%Contribution" and tmp.candidate = "Hales" and prime.ContributorPayee = tmp.ContributorPayee) as HalesAmount, (select sum(Amount) as tmpamt from candetable tmp where SubType like "%Contribution" and tmp.candidate = "Smith" and prime.ContributorPayee = tmp.ContributorPayee) as SmithAmount from candetable prime where ContributorPayee not like "Miscellaneous%" and SubType like "%Contribution" group by ContributorPayee having cnt > 1 order by cnt desc, amt desc;' echo "" echo "" sqlite3 -html Merged.db 'select * from multiple;' echo "
Donations to multiple competing candidates
Count of candidatesTotal AmountContributorBrady AmountHales AmountSmith Amount
" echo "" echo "" echo "" sqlite3 -html Merged.db 'select sum(BradyAmount),sum(HalesAmount),sum(SmithAmount) from multiple where BradyAmount is not null;' | sed -e 's/^//' echo "" sqlite3 -html Merged.db 'select sum(BradyAmount),sum(HalesAmount),sum(SmithAmount) from multiple where HalesAmount is not null;' | sed -e 's/^//' echo "" sqlite3 -html Merged.db 'select sum(BradyAmount),sum(HalesAmount),sum(SmithAmount) from multiple where SmithAmount is not null;' | sed -e 's/^//' echo "
Donors to one candidate donated also to other candidates in certain amounts
Donated CandidateAlso to BradyAlso to HalesAlso to Smith
Brady Donors
Hales Donors
Smith Donors
" echo "" echo "" echo "" sqlite3 -html Merged.db 'select sum(BradyAmount),sum(HalesAmount),sum(SmithAmount) from multiple where BradyAmount is null;' | sed -e 's/^//' echo "" sqlite3 -html Merged.db 'select sum(BradyAmount),sum(HalesAmount),sum(SmithAmount) from multiple where HalesAmount is null;' | sed -e 's/^//' echo "" sqlite3 -html Merged.db 'select sum(BradyAmount),sum(HalesAmount),sum(SmithAmount) from multiple where SmithAmount is null;' | sed -e 's/^//' echo "
Donors to two candidates donated (not donating to one) donated in certain amounts
Donated CandidateAlso to BradyAlso to HalesAlso to Smith
Non-Brady Donors
Non-Hales Donors
Non-Smith Donors
" echo "" echo "" sqlite3 -html Merged.db 'select Zip, sum(Amount) as amt, (select sum(Amount) as tmpamt from candetable tmp where ContributorPayee not like "Miscellaneous%" and SubType like "%Contribution" and tmp.candidate = "Brady" and prime.Zip = tmp.Zip) as BradyAmount, (select sum(Amount) as tmpamt from candetable tmp where ContributorPayee not like "Miscellaneous%" and SubType like "%Contribution" and tmp.candidate = "Hales" and prime.Zip = tmp.Zip) as HalesAmount, (select sum(Amount) as tmpamt from candetable tmp where ContributorPayee not like "Miscellaneous%" and SubType like "%Contribution" and tmp.candidate = "Smith" and prime.Zip = tmp.Zip) as SmithAmount from candetable prime where ContributorPayee not like "Miscellaneous%" and SubType like "%Contribution" group by Zip having amt > 0 order by Zip asc;' echo "
Donations by zip code in zip code order
Zip CodeTotal Amount DonatedBrady AmountHales AmountSmith Amount
" echo "" echo "" sqlite3 -html Merged.db 'select Zip, sum(Amount) as amt, (select sum(Amount) as tmpamt from candetable tmp where ContributorPayee not like "Miscellaneous%" and SubType like "%Contribution" and tmp.candidate = "Brady" and prime.Zip = tmp.Zip) as BradyAmount, (select sum(Amount) as tmpamt from candetable tmp where ContributorPayee not like "Miscellaneous%" and SubType like "%Contribution" and tmp.candidate = "Hales" and prime.Zip = tmp.Zip) as HalesAmount, (select sum(Amount) as tmpamt from candetable tmp where ContributorPayee not like "Miscellaneous%" and SubType like "%Contribution" and tmp.candidate = "Smith" and prime.Zip = tmp.Zip) as SmithAmount from candetable prime where ContributorPayee not like "Miscellaneous%" and SubType like "%Contribution" group by Zip having amt > 0 order by amt desc;' echo "
Donations by zip code in total amount order
Zip CodeTotal Amount DonatedBrady AmountHales AmountSmith Amount
" echo '

By looking at the most early data record it is possible to determine when a contributor was in the <=$100 donation set for their first donations and then got promoted to larger non-aggregated donors later that year.

' for y in 2011 2012; do for i in Brady Hales Smith; do for j in '' ' and State = "OR"' ' and City = "Portland" and State = "OR"'; do echo -ne "

For year $y$j for candidate $i " sqlite3 $i.db ' create temp table firsts as select prime.ContributorPayee,min(prime.TranId) as mintid from candetable prime where prime.SubType like "%Contribution"'"$j"' and substr(TranDate,7) = "'"$y"'" group by prime.ContributorPayee; create temp table corrections as select case AggregateAmount-Amount between 0 and 100 when 1 then AggregateAmount-Amount else 0 end as correction from firsts left join candetable on (mintid = TranId); select "a total of $" || sum(correction) || " from " || count(correction) || " donors, averaging $" || round(avg(correction),2) || " per donation got promoted from <= $100 donors to > $100 aggregate donors." from corrections where correction >0; ' echo "

" done done done { cat </' sh imagegen.sh echo '

See table above or imagegen.sh script

' echo '

Daily History Image version of the above Weekly History Image created by imagegen.sh.

' echo "" echo "" for i in '100.01 and 200' '200.01 and 500' '500.01 and 1000' '1000.01 and 2000' '2000.01 and 5000' '5000.01 and 10000' '10000.01 and 1000000'; do for c in Brady Smith Hales; do echo -n ""; sqlite3 -html $c.db ' select sum(Amount), sum(case State when "OR" then Amount else 0 end), sum(case City when "Portland" then Amount else 0 end) from candetable where SubType like "%Contribution" and ContributorPayee not like "Miscellaneous%" and Amount between '"$i"'; ' | sed -e 's/^//' done done echo "
Donations by Amount bucket and city/state/usa source
CandidateBetween these dollar figuresUSA AmountOregon AmountPortland Amount
$c$i
" for i in Brady Hales Smith; do echo "" echo "" sqlite3 -html $i.db 'select City || ", " || State, sum(Amount) as amt from candetable where SubType = "Cash Expenditure" group by City collate nocase, State collate nocase order by amt desc;' | sed -e 's/>, None Given" done echo ""
Expenditures by $i by location ordered by amount
City and StateTotal Amount Expended