from django import db
from django.conf import settings
from django.core.management.base import NoArgsCommand
from data.models import NewAidsCases
import csv

# National Priorities Project Data Repository
# import_new_aids_cases.py
# Updated 6/17/2010, Joshua Ruihley, Sunlight Foundation

# Imports HHS State New AIDS Cases Data
# source info: latest years are stored in PDF format.  for the 6/2011 updated, we pulled last several years manually into the old copy of aids.csv :(
# npp csv: http://assets.nationalpriorities.org/raw_data/health/aids.csv (updated 6/17/2010)
# destination model:  NewAIDSCases

# HOWTO:
# 1) Download source files from url listed above
# 2) Convert source file to .csv with same formatting as npp csv
# 3) change SOURCE_FILE variable to the the path of the source file you just created
# 5) Run as Django management command from your project path "python manage.py import_new_aids_cases

# safe to re-run:  NO

SOURCE_FILE = '%s/health/aids.csv' % (settings.LOCAL_DATA_ROOT)

class Command(NoArgsCommand):
    
    def handle_noargs(self, **options):
        data_reader = csv.reader(open(SOURCE_FILE))
        
        def clean_int(value):
            if value <> '':
                value = int(value.replace(',', '').replace(' ', ''))
            else:
                value = None
            return value
        
        for i, row in enumerate(data_reader):
            if i == 0:
                year_row = row;            
            else:
                for j,col in enumerate(row):
                    if j == 0:
                        state = col
                    elif j > 0:
                        year = year_row[j]
                        value = col
                        record = NewAidsCases(state=state, year=year, value=clean_int(value))
                        record.save()
@@ -112,49 +112,160 @@
 om d
-ata.models import NewAidsCases%0Aimport csv
+jango.core.exceptions import MultipleObjectsReturned%0Afrom data.models import NewAidsCases%0Aimport csv%0Aimport sys%0Afrom npp_api.data.utils import clean_num
 %0A%0A# 
@@ -338,65 +338,8 @@
 s.py
-%0A# Updated 6/17/2010, Joshua Ruihley, Sunlight Foundation
 %0A%0A# 
@@ -526,16 +526,260 @@
 .csv :(%0A
+#   http://www.cdc.gov/hiv/topics/surveillance/resources/reports/#surveillance--%3EHIV Surveillance Report (Diagnosis of HIV Infection%0A#   and AIDS in the United States and Dependent Areas, 2010) --%3E Table 20 AIDS diagnosis, by area of residence%0A
 # npp cs
@@ -1436,216 +1436,49 @@
     
-%0A        def clean_int(value):%0A            if value %3C%3E '':%0A                value = int(value.replace(',', '').replace(' ', ''))%0A            else:%0A                value = None%0A            return value%0A        
+insert_count = 0%0A        update_count = 0
 %0A   
@@ -1712,24 +1712,32 @@
  state = col
+.strip()
 %0A           
@@ -1828,20 +1828,17 @@
     
-value = col%0A
+try:%0A    
     
@@ -1878,16 +1878,28 @@
 idsCases
+.objects.get
 (state=s
@@ -1903,17 +1903,16 @@
 e=state,
- 
 year=yea
@@ -1916,67 +1916,870 @@
 year
-, 
+)%0A                            if record.value %3C%3E clean_num(col):%0A                                record.
 value
-=
+ = 
 clean_
-int(value))%0A                        record.save()
+num(col)%0A                                record.save()%0A                                db.reset_queries()%0A                                update_count = update_count + 1%0A                        except MultipleObjectsReturned:%0A                            print 'error: multiple records exist for %25s %25s' %25 (year, state)%0A                            continue%0A                        except:%0A                            record = NewAidsCases(year = year, state=state, value = clean_num(col))%0A                            record.save()%0A                            db.reset_queries()%0A                            insert_count = insert_count + 1%0A        print '%25s import complete. %25s records updated, %25s inserted' %25 (SOURCE_FILE, update_count, insert_count)%0A

update script to import new aids cases--allow re-run, augment comments re: source file location