def test_add_vals_row(self):
ws = Workbook().active
ws.append(("head1", "head2", "head3"))
ws.append(("vals", "morevals", "allthevals"))
r = etl.add_vals(ws, ['new', 'header'], 'D1', 'r')
rh = etl.get_values(r.rows[0])
self.assertEqual(rh, ['head1', 'head2', 'head3', 'new', 'header'])
def test_add_vals_row(self):
ws = Workbook().active
ws.append(("head1", "head2", "head3"))
ws.append(("vals", "morevals", "allthevals"))
r = etl.add_vals(ws, ['new','header'], 'D1','r')
rh = etl.get_values(r.rows[0])
self.assertEqual(rh, ['head1', 'head2', 'head3', 'new','header'])
def prep_row(r, locs):
# check to see if numeric rows are None - if so, make 0
r = etl.get_values(r, setnull=True)
return Trainings(
priority=etl.xstr(r[locs["priority"]-1], setnull=True),
access_method=etl.xstr(r[locs["access_method"]-1], setnull=True),
hub=etl.xstr(r[locs["hub"]-1], setnull=True),
as_of=etl.xstr(r[locs["as_of"]-1], setnull=True),
dist_code=etl.xstr(r[locs["dist_code"]-1], setnull=True),
vdc_code=etl.xstr(r[locs["vdc_code"]-1], setnull=True),
uid=etl.xstr(r[locs["uid"]-1], setnull=True),
imp_agency=etl.xstr(r[locs["imp_agency"]-1], setnull=True),
source_agency=etl.xstr(r[locs["source_agency"]-1], setnull=True),
local_partner=etl.xstr(r[locs["local_partner"]-1], setnull=True),
contact_name=etl.xstr(r[locs["contact_name"]-1], setnull=True),
contact_email=etl.xstr(r[locs["contact_email"]-1], setnull=True),
contact_phone=etl.xstr(r[locs["contact_phone"]-1], setnull=True),
district=etl.xstr(r[locs["district"]-1], setnull=True),
vdc=etl.xstr(r[locs["vdc"]-1], setnull=True),
ward=etl.xstr(r[locs["ward"]-1], setnull=True),
train_sub=etl.xstr(r[locs["train_sub"]-1], setnull=True),
audience=etl.xstr(r[locs["audience"]-1], setnull=True),
train_title=etl.xstr(r[locs["train_title"]-1], setnull=True),
demo_inc=etl.xstr(r[locs["demo_inc"]-1], setnull=True),
iec_dist=etl.xstr(r[locs["iec_dist"]-1], setnull=True),
dur_session=etl.xstr(r[locs["dur_session"]-1], setnull=True),
amt_parti=etl.xstr(r[locs["amt_parti"]-1], setnull=True),
total_cost=etl.xstr(r[locs["total_cost"]-1], setnull=True),
total_parti=etl.xstr(r[locs["total_parti"]-1], setnull=True),
males=etl.xstr(r[locs["males"]-1], setnull=True),
females=etl.xstr(r[locs["females"]-1], setnull=True),
third_gen=etl.xstr(r[locs["third_gen"]-1], setnull=True),
elderly=etl.xstr(r[locs["elderly"]-1], setnull=True),
children=etl.xstr(r[locs["children"]-1], setnull=True),
person_dis=etl.xstr(r[locs["person_dis"]-1], setnull=True),
fem_hh=etl.xstr(r[locs["fem_hh"]-1], setnull=True),
vuln_hh=etl.xstr(r[locs["vuln_hh"]-1], setnull=True),
act_status=etl.xstr(r[locs["act_status"]-1], setnull=True),
#start_dt=etl.xstr(r[locs["start_dt"]-1], setnull=True),
start_day=etl.xstr(r[locs["start_day"]-1], setnull=True),
start_month=etl.xstr(r[locs["start_month"]-1], setnull=True),
start_year=etl.xstr(r[locs["start_year"]-1], setnull=True),
#comp_dt=etl.xstr(r[locs["comp_dt"]-1], setnull=True),
comp_day=etl.xstr(r[locs["comp_day"]-1], setnull=True),
comp_month=etl.xstr(r[locs["comp_month"]-1], setnull=True),
comp_year=etl.xstr(r[locs["comp_year"]-1], setnull=True),
comments=etl.xstr(r[locs["comments"]-1], setnull=True))
def prep_row(r, locs):
#check to see if numeric rows are None - if so, make 0
r = etl.get_values(r, setnull=True)
return Distributions(
priority=etl.xstr(r[locs["priority"]-1], setnull=True),
access_method=etl.xstr(r[locs["access_method"]-1], setnull=True),
hub=etl.xstr(r[locs["hub"]-1], setnull=True),
as_of=etl.xstr(r[locs["as_of"]-1], setnull=True),
dist_code=etl.xstr(r[locs["dist_code"]-1], setnull=True),
vdc_code=etl.xstr(r[locs["vdc_code"]-1], setnull=True),
act_cat=etl.xstr(r[locs["act_cat"]-1], setnull=True),
imp_agency=etl.xstr(r[locs["imp_agency"]-1], setnull=True),
source_agency=etl.xstr(r[locs["source_agency"]-1], setnull=True),
local_partner=etl.xstr(r[locs["local_partner"]-1], setnull=True),
contact_name=etl.xstr(r[locs["contact_name"]-1], setnull=True),
contact_email=etl.xstr(r[locs["contact_email"]-1], setnull=True),
contact_phone=etl.xstr(r[locs["contact_phone"]-1], setnull=True),
district=etl.xstr(r[locs["district"]-1], setnull=True),
vdc=etl.xstr(r[locs["vdc"]-1], setnull=True),
ward=etl.xstr(r[locs["ward"]-1], setnull=True),
act_type=etl.xstr(r[locs["act_type"]-1], setnull=True),
act_desc=etl.xstr(r[locs["act_desc"]-1], setnull=True),
targeting=etl.xstr(r[locs["targeting"]-1], setnull=True),
quantity=etl.xstr(r[locs["quantity"]-1], setnull=True),
total_hh=etl.xstr(r[locs["total_hh"]-1], setnull=True),
avg_hh_cost=etl.xstr(r[locs["avg_hh_cost"]-1], setnull=True),
fem_hh=etl.xstr(r[locs["fem_hh"]-1], setnull=True),
vuln_hh=etl.xstr(r[locs["vuln_hh"]-1], setnull=True),
act_status=etl.xstr(r[locs["act_status"]-1], setnull=True),
#start_dt=etl.xstr(r[locs["start_dt"]-1], setnull=True),
start_day=etl.xstr(r[locs["start_day"]-1], setnull=True),
start_month=etl.xstr(r[locs["start_month"]-1], setnull=True),
start_year=etl.xstr(r[locs["start_year"]-1], setnull=True),
#comp_dt=etl.xstr(r[locs["comp_dt"]-1], setnull=True),
comp_day=etl.xstr(r[locs["comp_day"]-1], setnull=True),
comp_month=etl.xstr(r[locs["comp_month"]-1], setnull=True),
comp_year=etl.xstr(r[locs["comp_year"]-1], setnull=True),
comments=etl.xstr(r[locs["comments"]-1], setnull=True))
def test_consolidate_specific(self):
#scenarios are: new agency, agency that is > 80pct inserted, agency <80 pct
#create historical db
db = Workbook().active
db.append(("Implementing agency", "dummy", "Additional Comments",
"Last Update"))
#other agency
for i in xrange(10):
db.append(('agency_not_inserting', 'dummy', 'add', '6-16-90'))
#agency over 80
for i in xrange(5):
db.append(('agency_existing_over_80', 'dummy', 'add', '6-16-90'))
#agency under 80
for i in xrange(40):
db.append(('agency_existing_under_80', 'dummy', 'add', '6-16-90'))
for i in xrange(40):
db.append(('Government', 'dummy', 'add', '6-16-90'))
#create agency not in db
wb1 = Workbook()
wb1.create_sheet(2, 'Distributions')
wb1.create_sheet(3, 'Trainings')
ws1 = wb1.get_sheet_by_name('Distributions')
ws1.append(("Implementing agency", "dummy", "Additional Comments"))
wb1.get_sheet_by_name('Trainings').append(
("Implementing agency", "dummy", "Additional Comments"))
wb1.get_sheet_by_name('Trainings').append(("agency", "dummy", "etc"))
for i in xrange(5):
ws1.append(("madnewagency", "dummy"))
#create agency in db >80
wb2 = Workbook()
wb2.create_sheet(2, 'Distributions')
wb2.create_sheet(3, 'Trainings')
ws2 = wb2.get_sheet_by_name('Distributions')
ws2.append(("Implementing agency", "dummy", "Additional Comments"))
wb2.get_sheet_by_name('Trainings').append(
("Implementing agency", "dummy", "Additional Comments"))
wb2.get_sheet_by_name('Trainings').append(("agency", "dummy", "etc"))
for i in xrange(50):
ws2.append(("agency_existing_over_80", "dummy"))
#create agency in db <80
wb3 = Workbook()
wb3.create_sheet(2, 'Distributions')
wb3.create_sheet(3, 'Trainings')
ws3 = wb3.get_sheet_by_name('Distributions')
ws3.append(("Implementing agency", "dummy", "Additional Comments"))
wb3.get_sheet_by_name('Trainings').append(
("Implementing agency", "dummy", "Additional Comments"))
wb3.get_sheet_by_name('Trainings').append(("agency", "dummy", "etc"))
for i in xrange(4):
ws3.append(("agency_existing_under_80", "dummy"))
#create another agency NOT in db
wb4 = Workbook()
wb4.create_sheet(2, 'Distributions')
wb4.create_sheet(3, 'Trainings')
ws4 = wb4.get_sheet_by_name('Distributions')
ws4.append(("Implementing agency", "dummy", "Additional Comments"))
wb4.get_sheet_by_name('Trainings').append(
("Implementing agency", "dummy", "Additional Comments"))
wb4.get_sheet_by_name('Trainings').append(("agency", "dummy", "etc"))
for i in xrange(25):
ws4.append(("datnewnew", "dummy"))
#gov
wb5 = Workbook()
wb5.create_sheet(2, 'Distributions')
wb5.create_sheet(3, 'Trainings')
ws5 = wb5.get_sheet_by_name('Distributions')
ws5.append(("Implementing agency", "dummy", "Additional Comments"))
wb5.get_sheet_by_name('Trainings').append(
("Implementing agency", "dummy", "Additional Comments"))
wb5.get_sheet_by_name('Trainings').append(("agency", "dummy", "etc"))
for i in xrange(3):
ws5.append(("Government", "dummy"))
#final counts should be:
#other agency: 10 (agency_not_inserting)
#agency over 80: 50 (agency_existing_over_80)
#agnecy under 80: 40 (agency_existing_under_80)
#new agency: 25 (datnewnew)
#other new agency: 5 (madnewagency)
cons = etl.consolidate(db, ((wb1, 'f'), (wb2, 'f'), (wb3, 'f'),
(wb4, 'f'), (wb5, 'f')))
cons_sheet = cons.get_sheet_by_name('Distributions')
etl.print_sheet(cons_sheet)
r = etl.get_values(cons_sheet.columns[0])
c = Counter(r)
print c
print etl.get_values(cons_sheet.rows[1])
self.assertEqual(c['agency_not_inserting'], 10)
self.assertEqual(c['agency_existing_over_80'], 50)
self.assertEqual(c['agency_existing_under_80'], 40)
self.assertEqual(c['datnewnew'], 25)
self.assertEqual(c['Government'], 43)
self.assertEqual(len(r), 173 + 1) #+1 for header
def test_add_vals_col(self):
ws = Workbook().active
r = etl.add_vals(ws, ['new', 'vals'], 'B1', 'c')
rh = etl.get_values(r.columns[1])
self.assertEqual(rh, ['new', 'vals'])
def test_get_values_null(self):
db = Workbook().active
db.append(('val', 'key', 'blank'))
db.append(('val', ))
self.assertEqual(etl.get_values(db.rows[1], setnull=True),
['val', None, None])
def test_get_values(self):
db = Workbook().active
db.append(('val', 'key', 'makeblank'))
db.append(('val', 'key'))
self.assertEqual(etl.get_values(db.rows[0]), ['val', 'key', ''])
def test_consolidate_specific(self):
#scenarios are: new agency, agency that is > 80pct inserted, agency <80 pct
#create historical db
db = Workbook().active
db.append(("Implementing agency", "dummy", "Additional Comments", "Last Update"))
#other agency
for i in xrange(10):
db.append(('agency_not_inserting','dummy','add','6-16-90'))
#agency over 80
for i in xrange(5):
db.append(('agency_existing_over_80','dummy','add','6-16-90'))
#agency under 80
for i in xrange(40):
db.append(('agency_existing_under_80','dummy','add','6-16-90'))
for i in xrange(40):
db.append(('Government','dummy','add','6-16-90'))
#create agency not in db
wb1 = Workbook()
wb1.create_sheet(2, 'Distributions')
wb1.create_sheet(3, 'Trainings')
ws1 = wb1.get_sheet_by_name('Distributions')
ws1.append(("Implementing agency", "dummy", "Additional Comments"))
wb1.get_sheet_by_name('Trainings').append(("Implementing agency", "dummy", "Additional Comments"))
wb1.get_sheet_by_name('Trainings').append(("agency", "dummy", "etc"))
for i in xrange(5):
ws1.append(("madnewagency", "dummy"))
#create agency in db >80
wb2 = Workbook()
wb2.create_sheet(2, 'Distributions')
wb2.create_sheet(3, 'Trainings')
ws2 = wb2.get_sheet_by_name('Distributions')
ws2 .append(("Implementing agency", "dummy", "Additional Comments"))
wb2.get_sheet_by_name('Trainings').append(("Implementing agency", "dummy", "Additional Comments"))
wb2.get_sheet_by_name('Trainings').append(("agency", "dummy", "etc"))
for i in xrange(50):
ws2.append(("agency_existing_over_80", "dummy"))
#create agency in db <80
wb3 = Workbook()
wb3.create_sheet(2, 'Distributions')
wb3.create_sheet(3, 'Trainings')
ws3 = wb3.get_sheet_by_name('Distributions')
ws3.append(("Implementing agency", "dummy", "Additional Comments"))
wb3.get_sheet_by_name('Trainings').append(("Implementing agency", "dummy", "Additional Comments"))
wb3.get_sheet_by_name('Trainings').append(("agency", "dummy", "etc"))
for i in xrange(4):
ws3.append(("agency_existing_under_80", "dummy"))
#create another agency NOT in db
wb4 = Workbook()
wb4.create_sheet(2, 'Distributions')
wb4.create_sheet(3, 'Trainings')
ws4 = wb4.get_sheet_by_name('Distributions')
ws4 .append(("Implementing agency", "dummy", "Additional Comments"))
wb4.get_sheet_by_name('Trainings').append(("Implementing agency", "dummy", "Additional Comments"))
wb4.get_sheet_by_name('Trainings').append(("agency", "dummy", "etc"))
for i in xrange(25):
ws4.append(("datnewnew", "dummy"))
#gov
wb5 = Workbook()
wb5.create_sheet(2, 'Distributions')
wb5.create_sheet(3, 'Trainings')
ws5 = wb5.get_sheet_by_name('Distributions')
ws5 .append(("Implementing agency", "dummy", "Additional Comments"))
wb5.get_sheet_by_name('Trainings').append(("Implementing agency", "dummy", "Additional Comments"))
wb5.get_sheet_by_name('Trainings').append(("agency", "dummy", "etc"))
for i in xrange(3):
ws5.append(("Government", "dummy"))
#final counts should be:
#other agency: 10 (agency_not_inserting)
#agency over 80: 50 (agency_existing_over_80)
#agnecy under 80: 40 (agency_existing_under_80)
#new agency: 25 (datnewnew)
#other new agency: 5 (madnewagency)
cons = etl.consolidate(db, ((wb1,'f'), (wb2,'f'), (wb3,'f'), (wb4,'f'), (wb5,'f')))
cons_sheet = cons.get_sheet_by_name('Distributions')
etl.print_sheet(cons_sheet)
r = etl.get_values(cons_sheet.columns[0])
c = Counter(r)
print c
print etl.get_values(cons_sheet.rows[1])
self.assertEqual(c['agency_not_inserting'], 10)
self.assertEqual(c['agency_existing_over_80'], 50)
self.assertEqual(c['agency_existing_under_80'], 40)
self.assertEqual(c['datnewnew'], 25)
self.assertEqual(c['Government'], 43)
self.assertEqual(len(r), 173+1) #+1 for header
def test_add_vals_col(self):
ws = Workbook().active
r = etl.add_vals(ws, ['new','vals'], 'B1','c')
rh = etl.get_values(r.columns[1])
self.assertEqual(rh, ['new','vals'])
def test_get_values_null(self):
db = Workbook().active
db.append(('val', 'key','blank'))
db.append(('val',))
self.assertEqual(etl.get_values(db.rows[1], setnull = True),['val', None, None])
def test_get_values(self):
db = Workbook().active
db.append(('val', 'key', 'makeblank'))
db.append(('val', 'key'))
self.assertEqual(etl.get_values(db.rows[0]),['val','key', ''])