def test_mismatch_header_cons(self):
#create agency not in db
wb1 = Workbook()
wb1.create_sheet(2, 'Distributions')
ws1 = wb1.get_sheet_by_name('Distributions')
ws1.append(("not ", "matching"))
for i in xrange(5):
ws1.append(("madnewagency", "dummy"))
#create agency in db >80
wb2 = Workbook()
wb2.create_sheet(2, 'Distributions')
ws2 = wb1.get_sheet_by_name('Distributions')
ws2.append(("no", "way"))
for i in xrange(50):
ws2.append(("agency_existing_over_80", "dummy"))
r = etl.consolidate(wb1, (wb2))
self.assertEqual(len(r.rows), 5)
def test_mismatch_header_cons(self):
#create agency not in db
wb1 = Workbook()
wb1.create_sheet(2, 'Distributions')
ws1 = wb1.get_sheet_by_name('Distributions')
ws1 .append(("not ", "matching"))
for i in xrange(5):
ws1.append(("madnewagency", "dummy"))
#create agency in db >80
wb2 = Workbook()
wb2.create_sheet(2, 'Distributions')
ws2 = wb1.get_sheet_by_name('Distributions')
ws2 .append(("no", "way"))
for i in xrange(50):
ws2.append(("agency_existing_over_80", "dummy"))
r = etl.consolidate(wb1,(wb2))
self.assertEqual(len(r.rows), 5)
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_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
