Index: dataconv/cleansqlite.py ================================================================== --- dataconv/cleansqlite.py +++ dataconv/cleansqlite.py @@ -19,37 +19,53 @@ """ import os import sys import sqlite3 +import pprint from time import strftime, localtime class CleanError(Exception): """ Raised when an error is detected while cleaning a database """ pass -class SqliteClean(object): +class SqliteClean(): """ Class with methods to clean an SQLite3 database created from the Paradox database. - - This class must be subclassed by each of the breeds so that actions - peculiar to that breed can be carried out. """ - def __init__(self, breed, dbname, debug=False): + def __init__(self, dbname, debug=False): """ Initialise this instance """ self.warnings = [''] - self.breed = breed self.db_name = dbname self.db_conn = None self.debug = debug self.date_now = strftime('%Y-%m-%d', localtime()) self.ts_now = "%s 00:00:00" % self.date_now - self.breed_flocks = None - self.flock_unknown = None - self.flock_owner_corr = None - self.sheep_flock_corr = None + self.breed_flocks = 'SSB%' + self.flock_unknown = "SSB901" + # A tuple of tuples. Each sub-tuple is new_change_date, flock_no, old_change_date + self.flock_owner_corr = (('1983-01-01', '0431', '2001-12-12'),) + + # A tuple of tuples. Each sub-tuple is a missing owner record: + # flock_no, owner_person_id, owner_change_date, last_changed + # The Zxxxx records appear to be as a result of the owner of flock 2060 buying + # sheep and re-registering them... + self.sheep_flock_corr = (('039764', '2777','2005-09-01'), + ('040573', '2453', '2005-09-24'), # Mistake? + ('040573', '2769', '2005-09-24'), # Mistake? + ('040574', '2453', '2005-09-24'), # Mistake? + ('040574', '2769', '2005-09-24'), # Mistake? + ('043326', '1191', '2009-11-14'), + ('050081', '2906', '2010-08-19'), + ('051146', '2402', '2013-04-06'), # Spurious + ('051146', '3072', '2013-04-06'), # Spurious + ('Z5491', '2060', '1989-04-01'), # Duplicate Regn + ('Z6813', '2060', '1989-04-01'), # Duplicate Regn + ('Z6986', '2060', '1989-03-26'), # Duplicate Regn + ('Z6989', '2060', '1989-03-24'), # Duplicate Regn + ('Z6990', '2060', '1989-04-02')) # Duplicate Regn try: self.db_conn = sqlite3.connect(self.db_name) except sqlite3.Error as err: raise CleanError("Error opening database: %s" % str(err)) @@ -70,10 +86,20 @@ def clean(self): """ Run the methods to clean the database. Subclasses should extend this method to run their own cleaning method before calling this method """ + self.message( "Correcting persons table...") + self.correct_persons() + self.message( "Correcting regions tables...") + self.correct_regions() + self.message( "Correcting miscellaneous tables...") + self.correct_breed_tables() + self.message( "Correcting sheep transfers...") + self.correct_sheep_transfers() + self.message( "Correcting inspection venues...") + self.clean_insp_venues() self.message( "Eliminating nulls...") self.eliminate_nulls() self.message( "Checking foreign key integrity...") self.check_integrity() self.message( "Rationalising phone types...") @@ -576,13 +602,11 @@ except sqlite3.Error as err: raise CleanError("Error testing registering persons: " "%s" % str(err)) - if self.breed == 'sss': - exception_list = sss_exception_list - + exception_list = sss_exception_list try: # The breeder query is very complex if run as one query, so # much so that the SQLite 3.7.3 query planner has a bug # (sqlite check-in [d30f7b2def]) and it takes many minutes to run. # To avoid this a temporary table is created from the base query. @@ -917,58 +941,12 @@ ((self.ts_now, row[0], row[1]) for row in rows)) self.db_conn.commit() except sqlite3.Error as err: raise CleanError("Error inserting records into sheep_flock_audit: {}".format(err)) - c.close() - -class SSSClean(SqliteClean): - """ Subclass to clean up the SSS tables """ - - def __init__(self, db_name, debug): - """ Initialise this instance """ - super(SSSClean, self).__init__('sss', db_name, debug) - self.breed_flocks = 'SSB%' - self.flock_unknown = "SSB901" - # A tuple of tuples. Each sub-tuple is new_change_date, flock_no, old_change_date - self.flock_owner_corr = (('1983-01-01', '0431', '2001-12-12'),) - - # A tuple of tuples. Each sub-tuple is a missing owner record: - # flock_no, owner_person_id, owner_change_date, last_changed - - # The Zxxxx records appear to be as a result of the owner of flock 2060 buying - # sheep and re-registering them... - - self.sheep_flock_corr = (('039764', '2777','2005-09-01'), - ('040573', '2453', '2005-09-24'), # Mistake? - ('040573', '2769', '2005-09-24'), # Mistake? - ('040574', '2453', '2005-09-24'), # Mistake? - ('040574', '2769', '2005-09-24'), # Mistake? - ('043326', '1191', '2009-11-14'), - ('050081', '2906', '2010-08-19'), - ('051146', '2402', '2013-04-06'), # Spurious - ('051146', '3072', '2013-04-06'), # Spurious - ('Z5491', '2060', '1989-04-01'), # Duplicate Regn - ('Z6813', '2060', '1989-04-01'), # Duplicate Regn - ('Z6986', '2060', '1989-03-26'), # Duplicate Regn - ('Z6989', '2060', '1989-03-24'), # Duplicate Regn - ('Z6990', '2060', '1989-04-02')) # Duplicate Regn - - - def clean(self): - """ Run our own methods first, then call the super-class' method """ - self.message("Cleaning SSS database %s" % - os.path.basename(self.db_name), False, True) - self.message("Correcting SSS tables...") - self.correct_persons() - self.correct_regions() - self.correct_breed_tables() - self.correct_sheep_transfers() - self.correct_insp_venues() - super(SSSClean, self).clean() - print("\n".join(self.warnings)) - + c.close() + def correct_persons(self): """ Correct some person data """ try: curs = self.db_conn.cursor() curs.execute("update persons set forename = 'Stanley', title = 'The Late Dr' " @@ -1276,83 +1254,10 @@ "WHERE Inspector_1 = 'J.M. Marshall'") self.db_conn.commit() except sqlite3.Error as err: raise CleanError("Correcting inspector name in approval: %s" % str(err)) - - try: - """ Harmonise the Inspection venue names. """ - c.execute("UPDATE approval set venue = 'Unrecorded' " - "where venue = ''") - c.execute("UPDATE approval set venue = 'Ardingly' " - "where venue = 'Ardingley'") - c.execute("UPDATE approval set venue = 'Bath and West Show' " - "where venue LIKE 'Bath%'") - c.execute("UPDATE approval set venue = 'Bellingham Show' " - "where venue LIKE 'Bellingham%'") - c.execute("UPDATE approval set venue = 'Bristol Sale' " - "where venue LIKE 'Bristol%'") - c.execute("UPDATE approval set venue = 'Braxfield House' " - "WHERE venue = 'Braxfield';") - c.execute("UPDATE approval set venue = 'Carlisle Sale' " - "where venue LIKE 'Carlisle%'") - c.execute("UPDATE approval set venue = 'Devon Show' " - "where venue LIKE 'Devon%'") - c.execute("UPDATE approval set venue = 'Dorchester Show' " - "where venue LIKE 'Dorchester%'") - c.execute("UPDATE approval set venue = 'Eccleshall Show' " - "where venue LIKE 'Eccles%'") - c.execute("UPDATE approval set venue = 'Ewingston Farm' " - "where venue = 'Ewinston Farm'") - c.execute("UPDATE approval set venue = 'Fife Show' " - "where venue LIKE 'Fife%'") - c.execute("UPDATE approval set venue = 'Frome Show and Sale' " - "where venue LIKE 'Frome%'") - c.execute("UPDATE approval set venue = 'Easter Glentore' " - "where venue = 'Lonriggend'") - c.execute("UPDATE approval set venue = 'Nant-y-Derw' " - "where venue LIKE 'Nant%' and venue NOT LIKE '%Nantymwyn%'") - c.execute("UPDATE approval set venue = 'New Forest Show' " - "where venue LIKE 'New Forest%'") - c.execute("UPDATE approval set venue = 'Newbury Show' " - "where venue LIKE 'Newbury%'") - c.execute("UPDATE approval set venue = 'Owner''s Farm' " - "where venue LIKE 'Owner%' OR venue = 'at home' ") - c.execute("UPDATE approval set venue = 'Ravendale Farm' " - "where venue LIKE 'Ravendale%'") - c.execute("UPDATE approval set venue = 'Royal Highland Show' " - "where venue LIKE 'Royal Shig%'") - c.execute("UPDATE approval set venue = 'SSBG Show and Sale' " - "where venue LIKE 'SSBG%'") - c.execute("UPDATE approval set venue = 'RBST Show and Sale' " - "where venue LIKE 'Show %' OR venue LIKE 'Stoneleigh%'") - c.execute("UPDATE approval set venue = 'Singleton Show' " - "where venue LIKE 'Singleton%'") - c.execute("UPDATE approval set venue = 'Skipton Sale' " - "where venue LIKE 'Skipton%'") - c.execute("UPDATE approval set venue = 'Shustoke Show' " - "where venue LIKE 'Shustoke%'") - c.execute("UPDATE approval set venue = 'South Worden Farm' " - "where venue LIKE 'South Worden%'") - c.execute("UPDATE approval set venue = 'South Wales' " - "where venue = 'SouthWales'") - c.execute("UPDATE approval set venue = 'Tenbury Wells Show' " - "where venue LIKE 'Tenbury%'") - c.execute("UPDATE approval set venue = 'The Brackens, Shropshire' " - "where venue LIKE 'The Brackens%'") - c.execute("UPDATE approval set venue = 'Uffculme Show' " - "where venue LIKE 'Uffculme%'") - c.execute("UPDATE approval set venue = 'York Show and Sale' " - "where venue LIKE 'York%' AND venue NOT LIKE '%Museum%'" ) - c.execute("UPDATE approval set venue = 'Unrecorded' " - "where venue = 'unrecorded'") - - self.db_conn.commit() - - except sqlite3.Error as err: - raise CleanError("Error harmonising inspection venues: %s" - % str(err)) try: """ Harmonise the genotype testing lab names. """ c.execute("UPDATE prpgene " "SET testing_lab = 'Catapult Systems Ltd.' " @@ -1473,113 +1378,353 @@ except Exception as err: raise CleanError("Error correcting Z0417 and Z10487: {}".format(str(err))) c.close() - def correct_insp_venues(self): + def clean_insp_venues(self): """ Clean up the inspection venues """ - venue_clean = [('Ardingly Show', 'Ardingly'), - ('Member 2502, 18 Queens Court, Irvine', '18 Queens Court' ), - ('Member 3304, Barry, Glamorgan', 'Barry CF62 8EE'), - ('Carlisle Show and Sale', 'Carlisle Sale'), - ('Cranleigh Show', 'Cranleigh'), - ('Devon County Show', 'Devon Show'), - ('Dornoch, Sutherland', 'Dornock'), - ('Dornoch, Sutherland', 'Dornoch'), - ('Edinburgh Genetics, Malvern', 'Edinburgh'), - ('Member 1724, Ewingston Farm, East Lothian', 'Ewingsto'), - ('Member 1724, Ewingston Farm, East Lothian', 'Ewingston'), - ('Member 2674, Fullwood Stile Farm, Derbyshire', 'Fulwood'), - ('Goosnargh and Longridge Show', 'Goosnargh and Longridge'), - ('Member 3204, Greenlands Farm, Carnforth', 'Greenlands'), - ('Harbridge House Meadow', 'Hasbridge House Meadow'), - ('Herts County Show', 'Herts Show'), - ('Member 2152, Middletown Farm, Lanark', 'Middletown'), - ('Member 2152, Middletown Farm, Lanark', 'Middleton Farm'), - ('Member 2002, Middle Munty Farm, Taunton', 'Middle Munty'), - ('Milton Abbot', 'MILTON ABBOT'), - ('New Forest Show', 'New Hampshire County Show'), - ('Okehampton Show', 'Okehampton Show'), - ('Okehampton Show', 'Okehampton'), - ('Ripley Show','Ripley'), - ('Sedgemoor Show and Sale', 'Sedgemoor'), - ('Sedgemoor Show and Sale', 'Sedgemoor Auction'), - ('Sedgemoor Show and Sale', 'Sedgemoor Show & Sale'), - ('Sedgemoor Show and Sale', 'Sedgemoor Market'), - ('Sedgemoor Show and Sale', 'SEDGEMOOR SHOW & SALE'), - ('South Beds Show', 'South Beds'), - ('Member 2160, St Baldred''s, East Lothian', 'St.Baldred''s'), - ('Member 2160, St Baldred''s, East Lothian', 'St Baldred''s'), - ('Member 2436 Summerside Farm, Lanarkshire', 'Summerside'), - ('Westmorland Show', 'Westmoreland Show'), - ('Westmorland Show', 'West Morland Show'), - ('Member 2580, Wood Cottage Farm, Macclesfield', 'Weod Cottage'), - ('Member 2580, Wood Cottage Farm, Macclesfield', 'Woodcottage Farm'), - ('Flag Fen Show', 'Flag Fen'), - ('Chelford Show', 'Chelford'), - ('Member 1610, Wycoller, Lancashire', 'Wycoller'), - ('Member 0108, Braxfield House, Lanark', 'Braxfield House'), - ('Member 1191, Cedar Cottage, Fife', 'Cedar Cottage'), - ('Member 2549, The Brackens, Shropshire', 'The Brackens, Shropshire'), - ('Member 2549, The Brackens, Shropshire', 'Brackens'), - ('Member 0824, Rivendell, Hexham', 'Rivendell'), - ('Member 2519, Essex Wildlife Trust', 'Essex Wildlife Trust'), - ('Member 2848, Sandyacre, Newark', 'Sandyacres'), - ('Member 1003, Willowcroft, Somerset', 'Willowcroft'), - ('Member 1003, Willowcroft, Somerset', 'Crockerton'), - ('Member 2000, The Rench, West Lothian', 'The Rench'), - ('Member 2000, The Rench, West Lothian', 'Rench'), - ('Member 2472, Nut Tree Cottage, Salisbury', 'Nut Tree Cottage'), - ('Member 2472, Nut Tree Cottage, Salisbury', 'Mandeville Flock'), - ('Member 0306, The Old Surgery, Shropshire', 'The Old Surgery'), - ('Member 0306, The Old Surgery, Shropshire', 'Ercall Heath'), - ('Member 1350, Ramsbottom, Bury', 'Ramsbottom'), - ('Lesmahagow Show', 'Lesmahagow'), - ('Member 0402, Draywood Cottage, Yeovil', 'Draywood'), - ('Member 0402, Draywood Cottage, Yeovil', 'Odcombe'), - ('Member 2592, Romanno Mains, Peeblesshire', 'Romanno Mains'), - ('Member 3280, Hornacott Manor, Cornwall', 'Hornacott Manor'), - ('Member 2513, Newhouse Farm, East Sussex', 'Chalvington, E.Sussex'), - ('Member 2513, Newhouse Farm, East Sussex', 'Calvington, E.Sussex'), - ('Member 2865, Highfield House, York', 'Highfield House'), - ('Member 2962, Abacus Acres, Bedfordshire', 'Abacus Acres'), - ('Member 3252, Cronklea, Lockerbie', 'Cronklea'), -#~ ('Lockerbie Show', 'Lockerbie'), - ('Member 3187, The Old Barn, Doncaster', 'The Old Barn, Sutton'), - ('Member 1003, Fell House, Crockerton, Wilts.', 'Fell House (Jean Curtis''s'), - ('Member 3146, Fronhaul, Carmarthenshire', 'Fronhaul, Whitland'), - ('Member 3957, Boggs Holdings, East Lothian', 'Boggs Holdings'), - ('Member 3298, Twin Oaks, Welwyn, Herts', 'Twin Oaks, Welwyn'), - ('Member 1483, Clyngwynne Fach, carmarthenshire', 'Llanboidy'), - ('Member 2536, Dyffryn Isaf, Pembrokeshire', 'Dyffryn Isaf'), - ('Member 2186, Cefn Llanfair, Ceredigion', 'Landysul'), - ('Member 2220, Woodlands, Driffield', 'Woodlands, Kilham'), - ('Member 2220, Woodlands, Driffield', 'Kilham'), - ('Member 3744, Mount Pleasant, Somerset', 'Mount Pleasant'), - ('Member 2865, Highfield House, York', 'Harlthorpe'), - ('Member 3527, Jasmine Cottage, Highbridge, Somerset', 'Jasmine Cottage'), - ('Member 3230, Winders Wood Cottage, Corbridge, Northumberland', 'Winders Wood Cottage'), - ] - - - venue_clean_like = [('Compton Basset', 'Compton%'), - ('Ellingham and Ringwood Show', 'Ellingham%'), - ('Goosnargh and Longridge Show','Gosssnargh%'), - ('M6 J36 Auction Mart', 'Junction 36%'), - ('Llandovery Mart', 'Llandovery%'), - ('Melton Mowbray Show and Sale', 'Melton Mowbray%'), - ('Member 2321, Old Seaxpenne Farm, Shaftesbury', '%Seaxpenny%'), - ('Sedgemoor Show and Sale', 'Sedgmoor%'), - ('Member 2033, Sunninglye Farmhouse, Kent', 'Sunninglye%'), - ('Member 2116, Welland Down Farm, Devon', 'Welland%'),] - + + try:# Add a new columns 'venue_type' and 'member_no' to the approval table + curs = self.db_conn.cursor() + curs.execute("alter table approval add column venue_type text") + curs.execute("update approval set venue_type = 'Unknown'") + curs.execute("alter table approval add column member_no text") + self.db_conn.commit() + except Exception as err: + raise CleanError(f"Error adding columns to approval: {str(err)}") + + # Set a real venue for inspections with a venue of 'at home' + try: + curs = self.db_conn.cursor() + curs.execute("update approval set venue = 'Rivendell' where venue like 'Owner%' " + "and owner_person = 269 and sort_approval_date = '2002-01-01';") + curs.execute("update approval set venue = 'Fasgadh, Clachan Seil, by Oban, Argyll', " + "venue_type = '2036' " + "where venue like 'Owner%' and owner_person = 1085 and " + "sort_approval_date = '2002-09-15';") + curs.execute("update approval set venue = 'Bents, Stoneyburn, Bathgate, West Lothian', " + "venue_type = '2557' " + "where venue like 'Owner%' and owner_person = 1165 and " + "sort_approval_date = '2002-09-15';") + curs.execute("update approval set venue = 'Braxfield' where venue like 'Owner%' " + "and owner_person = 11 and sort_approval_date = '2002-09-15';") + curs.execute("update approval set venue = 'The Old Surgery' where venue like 'Owner%' " + "and owner_person = 48 and sort_approval_date = '2002-09-29';") + curs.execute("update approval set venue = " + "'Woodview Farm, Shortheath, Swadlincode, Derbyshire', " + "venue_type = '0673' " + "where venue like 'Owner%' and owner_person = 194 and " + "sort_approval_date = '2002-09-29';") + + curs.execute("update approval set venue = " + "'Rose Cottage, Upton Magna, Shrewsbury, Shropshire', " + "venue_type = '1308' " + "where venue like 'Owner%' and owner_person = 472 and " + "sort_approval_date = '2002-09-29';") + curs.execute("update approval set venue = " + "'The Stables, Lower Penn, Wolverhampton, West Midlands', " + "venue_type = '2534' " + "where venue like 'Owner%' and owner_person = 1137 and " + "sort_approval_date = '2002-09-29';") + curs.execute("update approval set venue = " + "'Nidus Cottage, Church Aston, Newport, Shropshire', " + "venue_type = '0943' " + "where venue like 'Owner%' and owner_person = 331 and " + "sort_approval_date = '2002-09-29';") + curs.execute("update approval set venue = 'Willowcroft' where venue = 'at home' " + "and owner_person= 355 and sort_approval_date = '2002-10-06';") + curs.execute("update approval set venue = " + "'The Bytack, Mynd, Bucknell, Shropshire', " + "venue_type = 'Farm', member_no = '2471' " + "where venue like 'Owner%' and owner_person = 1116 and " + "sort_approval_date = '2002-10-22';") + + curs.execute("update approval set venue = 'Middleton Farm' where venue like 'Owner%' " + "and owner_person = 717 and sort_approval_date = '2002-12-10';") + curs.execute("update approval set venue = 'Draywood' where venue = 'at home' " + "and sort_approval_date = '2006-08-27';") + except Exception as err: + raise CleanError(f"Error setting real venues instead of 'at home': {str(err)}") + + curs.execute("select * from approval where venue like 'Owner%' " + "order by sort_approval_date;") + rows = curs.fetchall() + for row in rows: + pprint.pprint(row) + + curs.execute("select * from approval where venue = 'at home' " + "order by sort_approval_date;") + rows = curs.fetchall() + for row in rows: + pprint.pprint(row) + + venue_clean = [ + + ('Abacus Acres, Bedfordshire', '2962', 'Abacus Acres'), + ("Ainstable, Cumbria", '3092', "Ainstable, Cumbria"), + ('Airyholme Farm, York', '1058', 'Airyholme Farm'), + ("Applegarth House, Binfield, Berkshire", '0376', "Binfield"), + ('Ash Farm, Iddesleigh, Devon', '0031', 'Ash Farm'), + + ("Balcombes Barn, Lewes, East Sussex", '3310', "Balcombes Barn"), + ('Barry, Glamorgan', '3304', 'Barry CF62 8EE'), + ('Bath and West Show', 'Event', "Bath%"), + ("Bellingham Show", 'Event', "Bellingham%"), + ('Birch Farm, Guildford, Surrey', '0376', 'Birch Farm'), + ('Boggs Holdings, East Lothian', '3957', 'Boggs Holdings'), + ("Bradworthy, Devon", 'Unknown', 'Bradworthy'), + ("Braunston, Daventry, Northamptonshire", '2833', 'Braunston'), + ('Braxfield House, Lanark', '0108', 'Braxfield%'), + + + ("Breoch Park, New Abbey, Dumfries", '2423', 'Breoch Park'), + ("Bristol Sale", 'Event', "Bristol%"), + ('Bull Hill Farm, Ashbourne, Derbyshire', '3380', 'Bull Hill Farm'), + + ("Carlisle Show and Sale", 'Unknown', 'Carlisle%'), + ('Cedar Cottage, Craigrothie, Cupar, Fife', '1191', 'Cedar Cottage'), + ('Cedar Cottage, Craigrothie, Cupar, Fife', '1191', 'Craigrothie'), + ('Cefn Llanfair, Ceredigion', '2186', 'Landysul'), + ('Cefn Llanfair, Ceredigion', '2186', 'Cefn Llanfair'), + ('Chelford Show', 'Unknown', 'Chelford'), + ("Chertsey Show, Surrey", "Event", "Chertsey%"), + ('Clyngwynne Fach, Carmarthenshire', '1483', 'Llanboidy'), + ('Lower End Farm. Compton Bassett, Wiltshire', '1060', 'Compton%'), + ('Cotswold Farm Park, Cheltenham, Gloucestershire', 'Event', 'Cotswold Farm Park'), + ('Cranleigh Show', 'Unknown', 'Cranleigh'), + ('Cronklea, Lockerbie', '3252', 'Cronklea'), + + ('Dancing Gate Farm, Underskiddaw, Keswick, Cumbria', '3159', 'Dancing Gate%'), + ('Devon County Show', 'Event', 'Devon%'), + ('Dorchester Show', 'Event', 'Dorchester%'), + ('Dowmin, Huntly, Aberdeenshire', '3241', 'Dowmin Farm'), + ('Draywood Cottage, Yeovil, Somerset', '0402', 'Draywood'), + ('Draywood Cottage, Yeovil, Somerset', '0402', 'Odcombe'), + ('Dyffryn Isaf, Pembrokeshire', '2536', 'Dyffryn Isaf'), + ('Dyke Farm, Penrith, Cumbria', '1015', 'Dyke Farm'), + + ('East Balgray Farm, Irvine, Ayrshire', '2502', 'East Bolgray Farm'), + ("East Anglia", 'Event', "E. Anglia"), + ("East Down End, Egloskery, Launceston, Cornwall", '2384', "East Down End"), + ("Easter Glentore, Airdrie, Lanarkshire", '0937', "Easter Glentore"), + ("Easter Glentore, Airdrie, Lanarkshire", '0937', "Longriggend"), + ("Easter Glentore, Airdrie, Lanarkshire", '0937', "Lonriggend"), + ('Eccleshall Show', 'Event','Eccles%'), + ("Edinburgh Genetics, Malvern", "Event", "Edinburgh Genetics, Malvern"), + ('Edinburgh Genetics, Malvern','Event', 'Edinburgh'), + ('Ellingham and Ringwood Show', 'Unknown', 'Ellingham%'), + ('Essex Wildlife Trust', '2519','Essex Wildlife Trust'), + ('Ewingston, Humbie, East Lothian', '1724', 'Humbie%'), + ('Ewingston, Humbie, East Lothian', '1724', 'Ewingsto%'), + ('Ewingston, Humbie, East Lothian', '1724', 'Ewinsto%'), + + ("Fairhaven, Higher Walton, Preston, Lancashire", '2414', "Fairhaven"), + ('Fell House, Crockerton, Wilts.', '1003', "Fell House (Jean Curtis's)"), + ("Field View, Haddington, East Lothian", '2425', "Haddington"), + ('Fife Show', 'Unknown', 'Fife%'), + ("Findon Sheep Fair", "Event", "Findon Sheep Fair"), + ('Fines House Farm, Bishop Aukland, County Durham', '3610', 'Fines House Farm'), + ('Flag Fen Show', 'Unknown', 'Flag Fen'), + ('Formakin Farm, Somerset', '1199', 'Formakin Farm'), + ('Frome Show and Sale', 'Unknown', 'Frome%'), + ('Fronhaul, Whitland, Carmarthenshire', '3146', 'Fronhaul, Whitland'), + ('Fullwood Stile Farm, Hope, Derbyshire', '2674', 'Fulwood'), + ('Fullwood Stile Farm, Hope, Derbyshire', '2674', 'Fullwood Stile Farm'), + + ("Goldcrest, Foston, Grantham, Lincolnshire", '2638', "Goldcrest"), + ('Goosnargh and Longridge Show', 'Unknown', 'Goosnargh and Longridge'), + ('Goosnargh and Longridge Show', 'Unknown', 'Gosssnargh%'), + ('Greenlands Farm, Carnforth', '3204', 'Greenlands%'), + + ("Harbridge House (Meadow), Harbridge, Hampshire", 'Event', 'Hasbridge House Meadow'), + ("Harbridge House (Meadow), Harbridge, Hampshire", 'Event', "Harbridge House Meadow"), + ("Hartpury College, Gloucester", "Event", "Hartpury College"), + ('Herts County Show', 'Unknown', 'Herts Show'), + ('Higher Wringworthy Farm, Looe, Cornwall', '2740', 'Higher Wringworthy Farm'), + ('Highfield House, York', '2865', 'Highfield House'), + ('Highfield House, York', '2865', 'Harlthorpe'), + ('Highlands Farm, Kent', 'Event', 'Highlands Farm'), + ("Hill House, Ballindean, Perthshire", '0583', "Ballindean"), + ("Hillend Farm, Bishops Castle, Shropshire", '1514', "Hillend"), + ("Holme Lacey College, Herefordshire", "Event", "Holme Lacey"), + ('Home Farm, Stoney Stratton, Somerset', '1514', 'Home Farm'), + ('Home Farm, Stoney Stratton, Somerset', '1514', 'Stoney Stratton'), + ('Hornacott Manor, Cornwall', '3280', 'Hornacott Manor'), + ('Horton Park Farm (Horton Country Park), Kent', 'Event', 'Horton Park Farm'), + ("Howburn Cottage, Kincardine O'Neil, Aberdeenshire", '3023', "Howburn Cottage"), + ("Hudley Mill, Charles, Barnstable, Devon", '2046', "Charles, Devon"), + + ("Inspectors Meeting, NAC", "Event", "Inspectors Mtg, NAC"), + ('Irvine, Ayrshire', '2502', '18 Queens Court' ), + + ('Jasmine Cottage, Highbridge, Somerset', '3527', 'Jasmine Cottage'), + + ("Lackham, Wiltshire", 'Event', "Lackham"), + ('Llandovery Mart', 'Event', 'Llandovery%'), + ('Lesmahagow Show', 'Unknown', 'Lesmahagow'), + ('Little Copse Farm, Devon', '2157', 'Little Copse Farm'), + ('Lothlorien Farm, Denbighshire', '2711', 'Lothlorien Farm'), + ('Lower Dickfield Farm, Ramsbotttom, Lancashire', '3041', 'Lower Dickfield Farm'), + ('Lower End Farm. Compton Bassett, Wiltshire', '1060', 'Lower End Farm'), + ("Lower Gravenor Farm, Bishop's Castle, Shropshire", '0828', "Bishop's Castle"), + + ("Masham Sheep Fair, North Yorkshire", "Event", "Masham Sheep Fair"), + ('Meadowcroft Farm, Whitby, North Yorkshire', '2688', 'Meadowcroft Farm'), + ('Melton Mowbray Show and Sale', "Event", 'Melton Mowbray%'), + ('Middletown Farm, Lanark', '2152', 'Middleton Farm'), + ('Middletown Farm, Lanark', '2152', 'Middletown%'), + ('Middle Munty Farm, Taunton, Somerset', '2002', 'Middle Munty'), + ('Middle Munty Farm, Taunton, Somerset', '2002', 'Middle Munty Farm'), + ('Milton Abbot', 'Unknown', 'MILTON ABBOT'), + ('Mount Pleasant, Somerset', '3744', 'Mount Pleasant'), + ('M6 J36 Auction Mart', 'Event', 'Junction 36%'), + + ("NAC, Stoneleigh", "Event", "NAC"), + ("Nant-y-Derw, Builth Wells, Powys", "0833", "Builth Wells"), + ("Nantymyn Retreat, Rhandirmwyn, Llandovery, Dyfed", '0768', "Nantymwyn"), + ('New Forest Show', 'Unknown', 'New Hampshire County Show'), + ('New Forest Show', 'Unknown', 'New Forest%'), + ("Newbury Show", "Unknown", "Newbury%"), + ('Newhouse Farm, Chalvington, East Sussex', '2513', 'Chalvington, E.Sussex'), + ('Newhouse Farm, Chalvington, East Sussex', '2513', 'Calvington, E.Sussex'), + ('Not recorded', 'Unknown', ''), + ('Not recorded', 'Unknown', 'Unknown'), + ('Not recorded', 'Unknown', 'unrecorded'), + ('Not recorded', 'Unknown', 'Unrecorded'), + ('Not recorded', 'Unknown', 'Not Specified'), + ('Nut Tree Cottage, Salisbury', '2472', 'Nut Tree Cottage'), + ('Nut Tree Cottage, Salisbury', '2472', 'Mandeville Flock'), + + ('Okehampton Show', 'Unknown', 'Okehampton'), + ('Okehampton Show', 'Unknown', 'Okehampton Show'), + ('Old Seaxpenne Farm, Shaftesbury', '2321', '%Seaxpenny%'), + ("Owner's Farm (multiple unidentified)", 'On Farm', "at home"), + ("Owner's Farm (multiple unidentified)", 'On Farm', "Owner%"), + + ('Pretty Oak Farm, Chard, Somerset', '0984', 'Pretty Oak Farm'), + + ('Quendale House, Wheathampstead, Hertfordshire', '1833', 'Quendale Farm'), + + ('Rambridge Farm, Chobham, Surrey', '0582', 'Rambridge'), + ('Ramsbottom, Bury, Lancashire', '1350', 'Ramsbottom'), + ('Ravendale Farm, Hitchin, Hertfordshire', '0022', 'Ravendale%'), + ('RBST Show and Sale', 'Unknown', 'Show %'), + ('RBST Show and Sale', 'Unknown', 'Stoneleigh%'), + + ('Tenbury Wells Show', '', 'Tenbury%'), + ('The Brackens, Ercall Heath, Shropshire', '0306', 'The Brackens%'), + + + ("Re-inspection", "Event", "Re-inspection"), + ('Ripley Show', 'Unknown', 'Ripley'), + ('Rivendell, Hexham', '0824', 'Rivendell'), + ('Rivendell, Hexham', '0824', 'Hexham'), + ('Romanno Mains, Peeblesshire', '2592', 'Romanno Mains'), + ('Royal Highland Show', 'Unknown', 'Royal Shig%'), + + ('Sandyacre, Newark', '2848', 'Sandyacres'), + ('Sedgemoor Show and Sale', "Event", 'Sedgemoor'), + ('Sedgemoor Show and Sale', "Event", 'Sedgemoor Auction'), + ('Sedgemoor Show and Sale', "Event", 'Sedgemoor Show & Sale'), + ('Sedgemoor Show and Sale', "Event", 'Sedgemoor Market'), + ('Sedgemoor Show and Sale', "Event", 'SEDGEMOOR SHOW & SALE'), + ('Sedgemoor Show and Sale', "Event", 'Sedgmoor%'), + ('Severn View Farm, Badminton, Gloucestershire', '2371', 'Severn View Farm'), + ("Shetland Sheep 2000 Lerwick", "Event", "Shetland Sheep 2000 Lerwick"), + ('Shustoke Show', '', 'Shustoke%'), + ('Singleton Show', '', 'Singleton%'), + ('Skipton Sale', '', 'Skipton%'), + ('Snagg Farm, Ditcheat, Somerset', '3958', '%Ditcheat'), + ("Snelston Hall, Ashbourne, Derbyshire", '2846', "Snelston Hall"), + ('South Beds Show', "Event", 'South Beds'), + ("South of England Show, Ardingly", "Unknown", "Ardingl%"), + ('South Wales', 'Unknown', 'SouthWales'), + ('South Worden Farm, Holmsworthy, Devon', '0771', 'South Worden%'), + ('Spring Hill Farm, Eyemouth, Berwickshire', '2270', 'Spring Hill Farm'), + ('Spring Holton Farm, Yeovil, Somerset', '2317', 'Spring Holton Farm'), + ('SSBG Show and Sale', 'Unknown', 'SSBG%' ), + ('St Baldred''s, East Lothian', '2160', "St.Baldred's"), + ('St Baldred''s, East Lothian', '2160', "St Baldred's"), + ('Staples Farm, Datchworth Hertfordshire', '2141', 'Staples Farm'), + ('Stubb Farm, Skipton, North Yorkshire', '3566', 'Stubb Farm'), + ('Summerside, Wishaw, Lanarkshire', '2436', 'Summerside Farm'), + ('Summerside, Wishaw, Lanarkshire', '2436', 'Summerside'), + ('Sunninglye Farmhouse, Kent', '2033', 'Sunninglye%'), + ("SW Area Workshop", "Event", "SW Area Workshop"), + + ("Temple Newsam, Leeds", "Event", "Temple Newsam"), + ("Teaths Farm, Kirkfield Bank, Lanark, Lanarkshire", '2088', "Teaths Farm, Lanark"), + ('The Brackens, Ercall Heath, Shropshire', '0306', 'Brackens'), + ('The Bytack, Mynd, Bucknell, Shropshire', "2471", 'Mynd'), + ('The Old Barn, Sutton, Doncaster, South Yorkshire', '3187', 'The Old Barn, Sutton'), + ('The Old Surgery, Ercall Heath, Shropshire', '0306', 'The Old Surgery'), + ('The Old Surgery, Ercall Heath, Shropshire', '0306', 'Ercall Heath'), + ('The Rench, West Lothian', '2000', 'The Rench'), + ('The Rench, West Lothian', '2000', 'Rench'), + ('Twin Oaks, Welwyn, Hertfordshire', '3298', 'Twin Oaks, Welwyn'), + ("Tynrhos, Llanrhystud, Aberystwyth, Dyfed", '0544', 'Dunstone'), + + ('Uffculme Show', '', 'Uffculme%'), + ('Upper Meadows, Dornoch, Sutherland', '2262', 'Dornock'), + ('Upper Meadows, Dornoch, Sutherland', '2262', 'Dornoch'), + ("Upper Millsteads, Canonbie, Dumfriesshire", '2583', 'Canonbie'), + + ('Voley Farm, Parracombe, Devon', '2102', 'Voley Farm'), + + ('Welland Down Farm, Devon', '2116', 'Welland%'), + ('Westmorland Show', 'Unknown', 'Westmoreland Show'), + ('Westmorland Show', 'Unknown', 'West Morland Show'), + ('Willowcroft, Crockerton, Somerset', '1003', 'Willowcroft'), + ('Willowcroft, Crockerton, Somerset', '1003', 'Crockerton'), + ('Winders Wood Cottage, Corbridge, Northumberland', '3230', 'Winders Wood Cottage'), + ('Wood Cottage Farm, Macclesfield, Cheshire', '2580', 'Woodcottage Farm'), + ("Wood Cottage Farm, Macclesfield, Cheshire", '2580', "Wood Cottage"), + ('Woodlands, Driffield, East Yorkshire', '2220', 'Woodlands, Kilham'), + ('Woodlands, Driffield, East Yorkshire', '2220' ,'Kilham'), + ('Wycoller, Lancashire', '1610', 'Wycoller'), + ('Wyndford Farm, Broxburn, West Lothian', '2036', 'Wyndford Farm'), + + ("York Museum", "Event", "York Museum"), + ] + for row in venue_clean: + if len(row) != 3: + print(row) + + try: curs = self.db_conn.cursor() - curs.executemany("update approval set venue = ? where venue = ?;", - (row for row in venue_clean)) - curs.executemany("update approval set venue = ? where venue like ?;", - (row for row in venue_clean_like)) + curs.executemany("update approval set venue = ? , venue_type = ? " + "where venue like ?;", (row for row in venue_clean)) + self.db_conn.commit() + except Exception as err: + raise CleanError(f"Error correcting inspection venues: {str(err)}") + + try: + curs = self.db_conn.cursor() + curs.execute("UPDATE approval set venue = 'York Show and Sale' " + "where venue LIKE 'York%' AND venue NOT LIKE '%Museum%'" ) + curs.execute("update approval set venue = 'Nant-y-Derw, Builth Wells, Powys', " + "venue_type = '0833' where venue like 'Nant%' and " + "venue not like '%Nantymwyn%';"), + self.db_conn.commit() except Exception as err: - raise CleanError(f"Error correcting inspection venues: {str(err)}") + raise CleanError(f"Error correcting multi-like venues: {str(err)}") + + try: # Update the venue_type and member_no columns + curs = self.db_conn.cursor() + curs.execute("update approval set venue_type = 'Event' " + "where lower(venue) like '%show%' or " + "lower(venue) like '%sale%' ;") + curs.execute("update approval set venue_type = 'Farm', member_no = venue_type " + "where substr(venue_type, 1, 1) in ('0', '1', '2', '3', '4');") + + self.db_conn.commit() + except sqlite3.Error as err: + raise CleanError(f"Error updating the approval venue_type: {str(err)}") - + curs.execute("select * from approval where venue like 'Owner%' " + "order by sort_approval_date;") + rows = curs.fetchall() + for row in rows: + pprint.pprint(row) + Index: dataconv/convert.py ================================================================== --- dataconv/convert.py +++ dataconv/convert.py @@ -397,11 +397,11 @@ try: shutil.copyfile(sourcedb, self.cleandb) except shutil.Error as sherr: ConvertError("Error copying raw sqlite file: %s" % str(sherr)) - cleaner = cleansqlite.SSSClean(self.cleandb, debug) + cleaner = cleansqlite.SqliteClean(self.cleandb, debug) cleaner.clean() if self.ops['extract'] and not self.actions['keep']: os.remove(sourcedb) if self.ops['transform']: Index: dataconv/transform.py ================================================================== --- dataconv/transform.py +++ dataconv/transform.py @@ -309,11 +309,13 @@ self.message(" Transforming sheep identity tables...") self.transform_sheep_identity_tables() if not self.nomem: self.message(" Transforming tag changes...") self.transform_tag_audit_history() + self.message(" Transforming secondary registration data tables...") + self.transform_regn_secondary_tables() self.message(" Creating essential views...") self.create_essential_views() self.message(" Creating user tables...") self.create_user_tables() @@ -1706,35 +1708,17 @@ self.populate_table_many('eid_type', "INSERT INTO eid_type " "VALUES(?, '', CAST(? as TEXT));", ((t, self.ts_now) for t in ('Bolus', 'Chip', 'Ear Tag'))) - self.create_table('venue_type') - self.populate_table_many('venue_type', - "INSERT INTO venue_type " - "VALUES(?, '', CAST(? as TEXT));", - ((t, self.ts_now) for t in ('Event', 'On Farm', 'Other'))) - self.create_table('insp_venue') self.populate_table('insp_venue', "INSERT INTO insp_venue " - "SELECT DISTINCT NULL, venue, 'Other', '', " + "SELECT DISTINCT NULL, venue, venue_type, member_no, '', " "CAST(? as TEXT) FROM olddb.approval;", (self.ts_now,)) - try: # Update the venue_type column - self.curs.execute("update insp_venue set venue_type = 'Event' " - "where lower(venue) like '%show%' or " - "lower(venue) like '%sale%' ;") - self.curs.execute("update insp_venue set venue_type = 'On Farm' " - "where lower(venue) like 'Member%' or " - "lower(venue) like '%farm%';") - self.dbconn.commit() - except sqlite3.Error as err: - raise TransformError(f"Error updating the ins_venue venue_type: {str(err)}") - - self.create_table('testing_lab') self.populate_table('testing_lab', "INSERT INTO testing_lab " "SELECT DISTINCT NULL, testing_lab, '', " "CAST(? as TEXT) " @@ -2243,10 +2227,11 @@ "'ear_tag', ?, '', ?, ?);", (row for row in ch_rows)) self.dbconn.commit() except sqlite3.Error as err: raise TransformError("Error inserting tag change data " "into audit_history: %s" % str(err)) + def transform_regn_secondary_tables(self): """ Transform the secondary data tables: ai_sires, inspection, inspector and prp_genetics. """ Index: maketest.py ================================================================== --- maketest.py +++ maketest.py @@ -160,11 +160,11 @@ print("Creating tar file '{}'".format(tarf)) out = tarfile.open(os.path.join(self.outdir, tarf), 'w:gz') out.add(self.tsvpath, '') out.close() shutil.rmtree(self.tsvpath) - os.remove(self.newdb) +#~ os.remove(self.newdb) if self.refresh: test.refresh_test_database(os.path.splitext(self.outfile)[0]) class TestDB(transform.Transform): @@ -265,10 +265,12 @@ self.reset_transfers() self.message(" Inserting person related records...") self.select_person_related() self.message(" Inserting member related records...") self.select_member_related() + self.message(" Inserting inspection related records...") + self.select_insp_related() self.message(" Adding record to allow Login and Change Password to be tests...") self.add_login_test() self.message(" Creating indexes on new tables...") for tablename in self.new_tables: self.create_indexes(tablename) @@ -707,10 +709,12 @@ self.new_table('member') try: self.curs.execute("insert into member select * from olddb.member " "where member_no in (select distinct member_no from person) ;") self.dbconn.commit() + self.curs.execute("select * from member where member_no = '0833';") + print(f"Member 0833: {self.curs.fetchone()}") except sqlite3.Error as err: raise transform.TransformError( "Error inserting member records: {}".format(str(err))) self.new_table('mem_pmts') @@ -739,10 +743,31 @@ "where member_no in (select member_no from member);") self.dbconn.commit() except sqlite3.Error as err: raise transform.TransformError("Error inserting fbk_order records: %s" % str(err)) + + def select_insp_related(self): + """ Add member and person records for the inspection venue foreign key constraint """ + print("Selecting insp related members") + try: + self.curs.execute("insert into member select * from olddb.member " + "where member_no in " + "(select distinct member_no from olddb.insp_venue) " + "and member_no not in (select member_no from member);") + self.curs.execute("insert into mem_pmts select * from olddb.mem_pmts " + "where member_no in " + "(select distinct member_no from olddb.insp_venue) " + "and member_no not in (select member_no from mem_pmts);") + self.curs.execute("insert into person select * from olddb.person " + "where member_no in " + "(select distinct member_no from olddb.insp_venue) " + "and person_id not in (select person_id from person);") + self.dbconn.commit() + except sqlite3.Error as err: + raise transform.TransformError( + f"Error inserting member records for venues: {str(err)}") def reset_transfers(self): """ Remove transfers, either from real transfers or from running 'Update Presumed Dead' that cause errors when running the test suite. This will need to be updated at intervals as and when test failures are due to @@ -778,13 +803,13 @@ try: self.curs.execute("delete from transfer where regn_no = '041084' " "and transfer_date > '2006-09-02';") self.dbconn.commit() except sqlite3.Error as err: - raise transform.TransformError("Error deleting transfer records for 041084: %s" % - str(err)) - + raise transform.TransformError(f"Error deleting transfer records for " + "041084: {str(err)}") + def add_login_test(self): """ Add records and passwords to allow the Login and Change Password function to be tested """ # Add a test email to member 1189 primary person, and to a secondary person Index: ppdb/config_db.py ================================================================== --- ppdb/config_db.py +++ ppdb/config_db.py @@ -147,10 +147,16 @@ 'DELETE': ()},}) uri_tree.sheep.insp = sheepuri.Inspection(appconf, {'tools.check_auth.url_roles': {'GET': ('regsec', 'memsec', 'socsec', 'admin'), 'PUT': ('regsec', 'admin',), + 'POST': (), + 'DELETE': ()},}) + + uri_tree.sheep.inspnew = sheepuri.InspNew(appconf, + {'tools.check_auth.url_roles': {'GET': ('regsec', 'admin'), + 'PUT': (), 'POST': ('regsec', 'admin',), 'DELETE': ()},}) uri_tree.sheep.nav = sheepuri.Nav(appconf, {'tools.check_auth.url_roles': {'GET': ('regsec', 'memsec', 'socsec', 'admin'), Index: ppdb/const.py ================================================================== --- ppdb/const.py +++ ppdb/const.py @@ -26,11 +26,11 @@ JINJA_ENV = None """ The Jinja2 templating environment. Initialised at app startup by ppd.py """ VERSION = '0.93.0' VERSION__doc = """ The PPDB code version """ -SCHEMA = '34.0' +SCHEMA = '34.2' SCHEMA__doc = """ The PPDB database schema version """ BREED_ID_CHAR = 'Z' Index: ppdb/handlers/sheepuri.py ================================================================== --- ppdb/handlers/sheepuri.py +++ ppdb/handlers/sheepuri.py @@ -843,45 +843,64 @@ @cherrypy.expose class Inspection(SheepBase): """ Sheep inspections. - This class supports the GET, POST and PUT methods. + This class supports the GET and PUT methods. Additional path elements/query args are: 'rid' and 'hist'. - 'rid' is a sheep registration number, or a name which resolves to a single sheep. - - 'hist', as a parameter to GET, specifies that the sheep's transfer - history is to be reurned, otherwise and HTML form allowing the sheep's - transfer history to be updated is returned. - """ def __init__(self, config, path_conf=None): super().__init__(config, path_conf) self.resname = "Inspections" def GET(self, rid=None): - """ GET a sheep's Inspection history. """ + """ GET a sheep's Inspection history or a summary page if rid is None. """ page_info = self.html_appconf_items() - page_info.update({'pagetitle': 'Sheep Inspections', + page_info.update({'pagetitle': 'Sheep Inspection History', 'requestpath': cherrypy.request.path_info}) page_info['titlebar'] = page_info['pagetitle'] - template = const.JINJA_ENV.get_template('templates/underconst.tmpl') + template = const.JINJA_ENV.get_template('templates/inspection.tmpl') return template.render(page_info) def PUT(self): - """ Update a sheep's transfer history. """ + """ Update a sheep's inspection history. """ return 'Under Construction!' + +@cherrypy.expose +class InspNew(SheepBase): + """ New Sheep inspections. + + This class supports the GET and POST methods. + Additional path elements/query args are: 'rid' and 'hist'. + + """ + + def __init__(self, config, path_conf=None): + super().__init__(config, path_conf) + self.resname = "Inspections" + + def GET(self): + """ GET a page to enter new sheep inspections. """ + page_info = self.html_appconf_items() + page_info['venues'] = memlib.get_insp_venues() + page_info['inspectors'] = memlib.get_inspectors() + page_info.update({'pagetitle': 'Sheep Inspections', + 'requestpath': cherrypy.request.path_info}) + page_info['titlebar'] = page_info['pagetitle'] + template = const.JINJA_ENV.get_template('templates/inspnew.tmpl') + return template.render(page_info) + def POST(self): - """ Update a sheep's transfer history. """ + """ Add a new inspection record. """ return 'Under Construction!' - - @cherrypy.expose class Genotype(SheepBase): """ Sheep genotyping. This class supports the GET, POST and PUT methods. Index: ppdb/lib/memlib.py ================================================================== --- ppdb/lib/memlib.py +++ ppdb/lib/memlib.py @@ -612,11 +612,34 @@ "e.date_assigned, e.change_reason, e.last_changed " "from email e " "where e.email_id = ? and address <> '';", (email_id,)) return curs.fetchone() - +def get_inspectors(status=None): + """ Return the full and trainee inspectors and their status """ + with pglib.get_conn(): + with pglib.cursor() as curs: + curs.execute("select p.person_id, substring(r.role from 1 for 1) as status," + " p.initials || ' ' || p.surname as insp_name " + "from person p join person_role r on p.person_id = r.person_id " + "where r.role in ('Inspector', 'Trainee') " + "order by status, p.surname; ") +#~ return [{key: row[key] for key in row.keys()} for row in curs.fetchall()] + return curs.fetchall() + +def get_insp_venues(venue_type=None): + """ Return the recorded inspection venues """ + with pglib.get_conn(): + with pglib.cursor() as curs: + if not venue_type: + curs.execute("select venue_id, venue, venue_type from insp_venue " + "order by venue_type, venue;") + else: + curs.execute("select venue_id, venue, venue_type from insp_venue " + "where venue_type = ? order by venue;", (venue_type,)) + return curs.fetchall() + def get_mem_data_for_dialog(rows, sortby, sortdir): """ Return rows of member data for the multi-member dialog. """ orderby = MEMBER_SORT_TRANSLATE.get(sortby, 'm.member_no') qry = ("select m.member_no, p.title, p.initials, p.forename, p.surname, m.address_1, " "m.address_2, m.address_3, m.post_town, m.county, m.post_code, m.country " Index: ppdb/lib/sheep_schema.py ================================================================== --- ppdb/lib/sheep_schema.py +++ ppdb/lib/sheep_schema.py @@ -44,11 +44,11 @@ """ # Disable constant name case warnings # pylint: disable=C0103 # The schema version -schema_version = "34.0" +schema_version = "34.2" #: The table creation order - parent and look-up tables must be created and populated #: before their child tables. table_list = ["version", "audit_history", "mem_dates", "pmt_method", "mem_source", "nonren_reason", "phone_type", "role_type", "county", @@ -55,11 +55,11 @@ "adj_county", "country", "region", "in_region", "post_area", "adj_post_area", "mem_class", "member", "person", "mem_pmts", "privacy_options", "mem_privacy", "phone", "email", "person_role", "reps_county", "reps_country", "reps_region", "flock", "flock_owner", "health_scheme", "tag_prefix", 'eid_type', - "id_reason", "venue_type", "insp_venue", "testing_lab", "prp_alleles", + "id_reason", "insp_venue", "testing_lab", "prp_alleles", "colour", "pattern", "regn_code", "transfer_reason", "flock_book_vol", "fbk_order", "flock_stats", "flock_health", "sheep", "transfer", "ai_sires","flock_prefix", "ear_tag", "eid", "inspection", "inspector", "prp_genetics", "rc_queue", "pc_queue", "pedcerts"] @@ -928,33 +928,24 @@ "columns": [("eid_type", "VARCHAR(32) PRIMARY KEY"), ("change_reason", "VARCHAR(32) NOT NULL"), ("last_changed", "TIMESTAMP NOT NULL")] } -#: The known Inspection venue types. -venue_type = { - "type": "table", - "history": "yes", - "triggers": ["set_ts", "audit_action"], - "select_cols": [0], - "columns": [("venue_type", "VARCHAR(24) PRIMARY KEY"), - ("change_reason", "VARCHAR(32) NOT NULL"), - ("last_changed", "TIMESTAMP NOT NULL")] -} - #: The known Inspection venues. insp_venue = { "type": "table", "history": "yes", "triggers": ["set_ts", "audit_action"], "select_cols": [0], "columns": [("venue_id", "SERIAL PRIMARY KEY"), ("venue", "TEXT NOT NULL UNIQUE"), - ("venue_type", "VARCHAR(24) NOT NULL REFERENCES venue_type (venue_type) " - "ON UPDATE RESTRICT ON DELETE RESTRICT"), - ("change_reason", "VARCHAR(32) NOT NULL"), - ("last_changed", "TIMESTAMP NOT NULL")] + ("venue_type", "TEXT NOT NULL"), + ("member_no", "VARCHAR(6) REFERENCES member (member_no) " + "ON UPDATE RESTRICT ON DELETE RESTRICT"), ("change_reason", "VARCHAR(32) NOT NULL"), + ("last_changed", "TIMESTAMP NOT NULL")], +#~ "constraints": ["CONSTRAINT venue_type_check CHECK (venue_type in ('Event', 'On Farm', " +#~ "'Other'))"] } #: The known PrP genotyping testing laboratories. testing_lab = { "type": "table", ADDED ppdb/static/js/inspnew.js Index: ppdb/static/js/inspnew.js ================================================================== --- /dev/null +++ ppdb/static/js/inspnew.js @@ -0,0 +1,126 @@ +// +// inspection.js +// +// A module of event handler assignments for the sheep inspection page. +// +// Copyright PR Hardman 2009 - 2022 +// +// This program is free software: you can redistribute it and/or modify +// it under the terms of the GNU General Public License as published by +// the Free Software Foundation, either version 3 of the License, or +// (at your option) any later version. +// +// This program is distributed in the hope that it will be useful, +// but WITHOUT ANY WARRANTY; without even the implied warranty of +// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +// GNU General Public License for more details./ +// +// You should have received a copy of the GNU General Public License +// along with this program. If not, see . +// +"use strict"; + +import * as ppdb from "./ppdb.js"; +import {consts} from "./constants.js"; + +const uri_root = document.getElementById("uri-root-txt").innerHTML; +const selrec = document.getElementById("venue-selrec"); +const tbody = document.getElementById("venues-tbody"); + +window.onload = function() { + //~ // Assign the elements object + //~ sheep.get_elements(); + //~ sheep.copy_colours(); + //~ sheep.set_sex(document.getElementById("sex").value); + ppdb.add_tooltip_listeners(); + // need to do this as the browser displays a cached page on Ctrl+R + //~ sheep.set_edit(false); +}; + +function check_input() { + +} + +function submit_data() { + +} + +function clear_insp() { + +} +async function new_venue_session() { + // Start a new 'venue session' + // Make sure there is no inspection waiting to be saved before opening the + // New Inspection Session dialog + const uri_root = document.getElementById("uri-root-txt").innerHTML; + + if (document.getElementById("ear-tag").value !== '') { + await ppdb.warning_dialog("You must Save or Cancel the current inspection " + + "starting ane Venue Session"); + return consts.XHR_CANCEL; + } + // Open the new venue session dialog + let resp = await venue_session_dialog(); + if (resp !== consts.XHR_SUBMIT) { + return; + } +} + + +async function venue_session_dialog() { + // The New Venue Session dialog + return new Promise((resolve) => { + // Activate/deactivate the venue session dialog. + // Resolve with a dict with owner string and change date + const dialog = document.getElementById("venue-session-dialog"); + const sel_venue = document.getElementById("venue-selrec"); + const newvenue_fld = document.getElementById("new-venue-field"); + const newvenue_msg = document.getElementById("new-venue-msg"); + const date_fld = document.getElementById("insp-date-field"); + const date_msg = document.getElementById("insp-date-msg"); + + date_fld.value = new Date().toISOString().slice(0, 10); + date_msg.className = 'invis'; + newvenue_msg.className = 'invis'; + console.log("Making dialog visible"); + dialog.className = dialog.className.replace('invis', 'visible'); + + document.getElementById('venue-dlg-cancel').onclick = () => { + dialog.className = dialog.className.replace('visible', 'invis'); + resolve(consts.XHR_CANCEL); + }; + + document.getElementById('venue-dlg-submit').onclick = () => { + dialog.className = dialog.className.replace('visible', 'invis'); + resolve({venue: sel_venue.value, date: date_fld.value,}); + }; + }); +} + + +// Assign the event handlers + +document.getElementById("new-venue-btn").onclick = new_venue_session; + +document.getElementById("save-btn").onclick = async () => { + if (check_input()) { + let body = ppdb.jsonify_data_elements(document.getElementById("input_block")); + submit_data("PUT", body) + .then((resp) => { + if (resp === consts.XHR_CANCEL) {return;} + }) + .catch(err => { + ppdb.error_message(err) + .then(() => {return;}); + }); + + } +}; + +document.getElementById("cancel-btn").onclick = clear_insp; + +document.getElementById("venues-tbody").onclick = () => { + ppdb.set_table_row(event, selrec); +}; + + Index: ppdb/static/style/ppdb.css ================================================================== --- ppdb/static/style/ppdb.css +++ ppdb/static/style/ppdb.css @@ -776,11 +776,11 @@ width: 1.2em; height: 1.2em; text-align: center; } -.dlg_grid { +.dlg_grid, .dlg-grid { width: 100%; display: grid; grid-template-columns: max-content auto; grid-auto-rows: auto; grid-column-gap: 0.3em; Index: ppdb/templates/base.tmpl ================================================================== --- ppdb/templates/base.tmpl +++ ppdb/templates/base.tmpl @@ -294,12 +294,10 @@
Sheep Data Lookup Registrations Transfers - Inspections - Genotyping Pedigrees Progeny List Common Ancestors {# Confirmation of Registration @@ -313,10 +311,36 @@ Sheep
Sheep Data Lookup
+
+ {% break %} + {% endif %} + {% endfor %} + + {% for role in loginroles %} + {% if role in ('admin', 'regsec') %} +
+ Inspections +
+ New Inspections + View Inspections + Inspectors + Venues +
+
+ {% break %} + {% endif %} + {% endfor %} + {% for role in loginroles %} + {% if role in ('admin', 'regsec') %} +
+ Genotyping +
+ Genotyping +
{% break %} {% endif %} {% endfor %} ADDED ppdb/templates/inspection.tmpl Index: ppdb/templates/inspection.tmpl ================================================================== --- /dev/null +++ ppdb/templates/inspection.tmpl @@ -0,0 +1,137 @@ +{# +// inspection.tmpl +// +// The Jinja2 template for the Sheep Inspections page +// +// Copyright PR Hardman 2009 - 2022. +// +// This program is free software: you can redistribute it and/or modify +// it under the terms of the GNU General Public License as published by +// the Free Software Foundation, either version 3 of the License, or +// (at your option) any later version. +// +// This program is distributed in the hope that it will be useful, +// but WITHOUT ANY WARRANTY; without even the implied warranty of +// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +// GNU General Public License for more details./ +// +// You should have received a copy of the GNU General Public License +// along with this program. If not, see . +// +#} +{% extends "templates/base.tmpl" %} +{% block styles %} + +{% endblock %} +{% block content %} +
+ + {{uri_root}} + +
+
+

{{pagetitle|e}}

+
 
+
+ + + + + + + + + + + + + {% for insp in inspections %} + {% if loop.index is odd %} + + {% else %} + + {% endif %} + + + + + + + + {% endfor %} + +
+ Regn No {{sb_regnno}}
+
+ Sheep Name {{sb_name}}
+
+ Insp. Date {{sb_date}}
+
+ Venue {{sb_venue}}
+
+ Result {{sb_result}}
+
{{insp[0]}}{{insp[1]}}{{insp[2]}}{{insp[3]}}{{insp[4]}}{{insp[5]}}
+
+ Click in a row to see the full details including inspectors +
+
+
 
+
+
+
+
+ +
{# End of inner div #} +{% endblock %} +{% block dialogs %} +{% endblock %} +{%block scripts %} +{% endblock %} ADDED ppdb/templates/inspnew.tmpl Index: ppdb/templates/inspnew.tmpl ================================================================== --- /dev/null +++ ppdb/templates/inspnew.tmpl @@ -0,0 +1,308 @@ +{# +// inspnew.tmpl +// +// The Jinja2 template for the New Inspection page +// +// Copyright PR Hardman 2009 - 2022. +// +// This program is free software: you can redistribute it and/or modify +// it under the terms of the GNU General Public License as published by +// the Free Software Foundation, either version 3 of the License, or +// (at your option) any later version. +// +// This program is distributed in the hope that it will be useful, +// but WITHOUT ANY WARRANTY; without even the implied warranty of +// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +// GNU General Public License for more details./ +// +// You should have received a copy of the GNU General Public License +// along with this program. If not, see . +// +#} +{% extends "templates/base.tmpl" %} +{% block styles %} + +{% endblock %} +{% block content %} +
+ + {{uri_root}} + +
+
{{pagetitle|e}}
+
 
+
+

Venue Name here - Inspection Date here

+
+
+ +
+ + +
+ + {{regn_no}} + + {{dob}} + + {{full_name}} + + {{colour}} +   + Status + +
+ +
+   +
+ + Transfer text + +
+   +   + +
+
+ + +
+
+ + +
+
+ + +
+
+
 
+
+
+ + + + + + + + + + + + {% for insp in inspectors %} + {% if loop.index0 is even %} + + {% else %} + + {% endif %} + + + + + {% endfor %} + +
T/INameSelect
{{insp[0]}}{{insp[1]}}
+
+
+
 
+ + {% if ("regsec" in loginroles) %} +
+
+ +
 
+ +
 
+ +
+
+ {% endif %} +
+
+
+ + {# End of inner div #} +{% endblock %} +{% block dialogs %} +
+
+

Set Venue Session Parameters

+
+ +
+ + + + + + + + + + {% for venue in venues %} + {% if loop.index is odd %} + + {% else %} + + {% endif %} + + + + + {% endfor %} + +
VenueType
{{venue[0]}}{{venue[1]}}{{venue[2]}}
+
+
+
+ Click in a row to select it, or click the 'Add New' button for a new venue +
+
+ +
+
 
+
+ +
+ +
+
 
+
+
+ +
+ +
+
 
+
+
+
+
+
+
 
+
+ + +
+
+ +{% endblock %} +{%block scripts %} + +{% endblock %} Index: ppdb/test/selweb/test_flock_data.py ================================================================== --- ppdb/test/selweb/test_flock_data.py +++ ppdb/test/selweb/test_flock_data.py @@ -55,10 +55,16 @@ def get_health_schemes(self): """ Get the list of known health schemes """ resp = test.make_request(self.host, 'GET', "/test/health", 200) return resp["data"] + + def get_last_flock(self): + """ Get the last flock number """ + resp = test.make_request(self.host, 'GET', "/test/flock?move=last", 200) + print(f"Last flock: {resp['data']}") + return resp["data"] def get_flock_data(self, flock_no=None, move=''): """ Get the flock's data using the test URI """ @@ -255,11 +261,11 @@ self.wait_for_text("page_title", "Data for Flock 0002 - Talwrn") def test_BB(self): """ BB - test the 'last' VCR control """ self.click("last_btn") - self.wait_for_text("page_title", "Data for Flock 3606 - Judys") + self.wait_for_text("page_title", f"Data for Flock {self.LAST_FLOCK} - Judys") def test_BC(self): """ BC - test the 'fast back' VCR control """ self.click('fprev_btn') self.wait_for_text("page_title", "Data for Flock 3335 - Calanais") @@ -395,18 +401,18 @@ ts = row.find_elements_by_tag_name('td')[2].get_attribute("innerHTML") self.assert_ge(self.submit_time, ts) self.assert_ge(ts, util.isots_now()) # Check the rest of the Owners frame - expected = [['3607', 'Dr HG Johnson (Henry)', ts], + expected = [['3960', 'Dr HG Johnson (Henry)', ts], ['2603', 'Miss CM Rigg (Charlotte)', '2002-11-18 12:00:00'],] self.check_table_frame('flk_owner_tframe', expected) def test_EA(self): """ EA - test the Health Schemes. Use a test membera Life member to be sure the member is current """ - self.driver.get("http://%s/flock/3606" % self.host) - self.wait_for_text("page_title", "Data for Flock 3606 - Judys") + self.driver.get(f"http://{self.host}/flock/{self.LAST_FLOCK}") + self.wait_for_text("page_title", f"Data for Flock {self.LAST_FLOCK} - Judys") # Check that the Change button is disabled # Check that the checkboxes are enabled when the Change button is clicked self.click('health_btn') @@ -467,12 +473,12 @@ self.assert_equal(hs.get_attribute("checked"), None) @attr('prefix') def test_FA(self): """ FA - Test the New Prefix Dialog id correctly initialoised, then cancel it """ - self.driver.get("http://%s/flock/3606" % self.host) - self.wait_for_text("page_title", "Data for Flock 3606 - Judys") + self.driver.get(f"http://{self.host}/flock/{self.LAST_FLOCK}") + self.wait_for_text("page_title", f"Data for Flock {self.LAST_FLOCK} - Judys") self.click("new_prefix_btn") self.wait_for_visibility("input_dialog", True) self.wait_for_text("input_dlg_title", "Add New Prefix") self.wait_for_text("input_dlg_instr", "Enter the new tag prefix") @@ -513,11 +519,11 @@ """ FE - test the New Prefixr dialog with a non-EID prefix - cancel the change """ self.click('new_prefix_btn') self.wait_for_visibility("input_dialog", True) self.set_input_field("input_dlg_field", "UK330743", True) self.click('input_dlg_submit') - self.check_yesno_dialog("You are trying to add a non-EID EU prefix to flock 3606
" + self.check_yesno_dialog(f"You are trying to add a non-EID EU prefix to flock {self.LAST_FLOCK}
" "A non-EID EU prefix may only be used to register sheep born before 1/1/2010
" "Do you want to continue?", "yesno_dlg_no") @attr('prefix') def test_FF(self): @@ -524,11 +530,11 @@ """ FF - test the New Prefix dialog with a non-EID prefix - accept the prefix""" self.click('new_prefix_btn') self.wait_for_visibility("input_dialog", True) self.set_input_field("input_dlg_field", "UK330743", True) self.click('input_dlg_submit') - self.check_yesno_dialog("You are trying to add a non-EID EU prefix to flock 3606
" + self.check_yesno_dialog(f"You are trying to add a non-EID EU prefix to flock {self.LAST_FLOCK}
" "A non-EID EU prefix may only be used to register sheep born before 1/1/2010
" "Do you want to continue?", "yesno_dlg_yes") self.check_prefixes_frame([('UK330743', 1)]) @attr('prefix') @@ -570,12 +576,12 @@ self.check_info_dialog("Current Prefix updated") @attr('flock') def test_GA(self): """ GA - New Flock - Check the input dialog is correctly initialised """ - self.driver.get("http://%s/flock/3606" % self.host) - self.wait_for_text("page_title", "Data for Flock 3606 - Judys") + self.driver.get(f"http://{self.host}/flock/{self.LAST_FLOCK}") + self.wait_for_text("page_title", f"Data for Flock {self.LAST_FLOCK} - Judys") self.click("new_flock_btn") self.wait_for_visibility("input_dialog", True) self.wait_for_text("input_dlg_title", "Create New Flock") self.wait_for_text("input_dlg_instr", "Enter flock owner name or member no") @@ -756,11 +762,11 @@ """ GK - New Flock - A duplicate flock name """ self.click("new_flock_btn") self.set_input_field("input_dlg_field", self.NF_MEM, True) self.click('input_dlg_submit') - self.check_yesno_dialog("Owner Ms JS Bloggs (Jo) already owns flock 3608 - Wealden" + self.check_yesno_dialog(f"Owner Ms JS Bloggs (Jo) already owns flock {self.NF_MEM} - Wealden" "
Add a new flock anyway?", "yesno_dlg_yes") self.wait_for_visibility("input_dialog", True) self.set_input_field("input_dlg_field", "ferndale") self.click('input_dlg_submit') @@ -775,11 +781,11 @@ """ GL - New Flock - A duplicate flock name """ self.click("new_flock_btn") self.set_input_field("input_dlg_field", self.NF_MEM, True) self.click('input_dlg_submit') - self.check_yesno_dialog("Owner Ms JS Bloggs (Jo) already owns flock 3608 - Wealden" + self.check_yesno_dialog(f"Owner Ms JS Bloggs (Jo) already owns flock {self.NF_MEM} - Wealden" "
Add a new flock anyway?", "yesno_dlg_yes") self.wait_for_visibility("input_dialog", True) self.set_input_field("input_dlg_field", "of ferndale") self.click('input_dlg_submit') @@ -794,11 +800,11 @@ """ GM - New Flock - An existing flock name with 'of' """ self.click("new_flock_btn") self.set_input_field("input_dlg_field", self.NF_MEM, True) self.click('input_dlg_submit') - self.check_yesno_dialog("Owner Ms JS Bloggs (Jo) already owns flock 3608 - Wealden" + self.check_yesno_dialog(f"Owner Ms JS Bloggs (Jo) already owns flock {self.NF_MEM} - Wealden" "
Add a new flock anyway?", "yesno_dlg_yes") self.wait_for_visibility("input_dialog", True) self.set_input_field("input_dlg_field", "of wealden") self.click('input_dlg_submit') @@ -887,10 +893,11 @@ @classmethod def setup_class(cls): test.setup_class(cls, 'flock data', 'Firefox', refresh=True) print("Getting health schemes") cls.HDATA = cls.get_health_schemes(cls) + cls.LAST_FLOCK = cls.get_last_flock(cls)[0] print("Adding dummy member") test.add_dummy_member(cls) print("Adding new flock owner") cls.add_new_flock_owner(cls, cls.host) @@ -903,13 +910,14 @@ @classmethod def setup_class(cls): test.setup_class(cls, 'flock data', 'Chrome', refresh=True) cls.HDATA = cls.get_health_schemes(cls) + cls.LAST_FLOCK = cls.get_last_flock(cls)[0] test.add_dummy_member(cls) cls.add_new_flock_owner(cls, cls.host) @classmethod def teardown_class(cls): test.teardown_class(cls) Index: ppdb/test/selweb/test_mainmenu.py ================================================================== --- ppdb/test/selweb/test_mainmenu.py +++ ppdb/test/selweb/test_mainmenu.py @@ -434,12 +434,12 @@ elem.click() self.check_menu_items((("Sheep Data", True), ("Lookup", True), ("Registrations", True), ("Transfers", True), - ("Inspections", True), - ("Genotyping", True), + ("Inspections", False), + ("Genotyping", False), ("Pedigrees", True), ("Progeny List", True), ("Common Ancestors", True),) ) @@ -503,12 +503,12 @@ elem.click() self.check_menu_items((("Sheep Data", True), ("Lookup", True), ("Registrations", True), ("Transfers", True), - ("Inspections", True), - ("Genotyping", True), + ("Inspections", False), + ("Genotyping", False), ("Pedigrees", True), ("Progeny List", True), ("Common Ancestors", True),) ) Index: ppdb/test/selweb/test_mem_data.py ================================================================== --- ppdb/test/selweb/test_mem_data.py +++ ppdb/test/selweb/test_mem_data.py @@ -45,10 +45,16 @@ def get_privacy_opts(self): """ Get the privacy options - name and text sorted by text - from the server """ resp = test.make_request(self.host, 'GET', "/test/meminfo/privopts", 200) return resp["data"] + def get_last_flock(self): + """ Get the last flock number """ + resp = test.make_request(self.host, 'GET', "/test/flock?move=last", 200) + print(f"Last flock: {resp['data']}") + return resp["data"] + def check_dialog(self, expected): """ Check that the dialog is visible with the expected text, and can be dismissed """ dialog = self.driver.find_element_by_id("overlay") self.assert_in("visible", dialog.get_attribute("class")) @@ -1147,15 +1153,15 @@ self.wait_for_visibility("input_dialog", False) elem = self.driver.find_element_by_id('pers_0_info') self.check_yesno_dialog("Create a new flock 'Symbionic' for Dr HG Johnson?", "yesno_dlg_yes") - self.check_info_dialog("A new Flock 3607 has been created for Dr HG Johnson") + self.check_info_dialog(f"A new Flock {self.TEST_MEM_NO} has been created for Dr HG Johnson") self.wait_for_staleness(elem) self.check_members_persons(self.TEST_MEM_NO) self.check_display_fields({'pers_0_flock_0': - ['Flock: {} Symbionic'.format(self.TEST_MEM_NO), '']}) + [f'Flock: {self.TEST_MEM_NO} Symbionic', '']}) @attr('persons') def test_SB(self): """ SB - Test 'Add New Flock' - create a new flock for the secondary person. """ select = Select(self.driver.find_element_by_id('flock_menu_1_0')) @@ -1169,27 +1175,28 @@ self.wait_for_visibility("input_dialog", False) elem = self.driver.find_element_by_id('pers_1_info') self.check_yesno_dialog("Create a new flock 'of Bloggsy' for Mr J Bloggs?", "yesno_dlg_yes") - - self.check_info_dialog("A new Flock 3609 has been created for Mr J Bloggs") + flkno = str(int(self.TEST_MEM_NO) + 2) + self.check_info_dialog(f"A new Flock {flkno} has been created for Mr J Bloggs") self.wait_for_staleness(elem) self.check_members_persons(self.TEST_MEM_NO) self.check_display_fields({'pers_1_flock_0': - ['Flock: {} of Bloggsy'.format(str(int(self.TEST_MEM_NO) + 2)), '']}) + [f'Flock: {flkno} of Bloggsy', '']}) @attr('persons') def test_SC(self): """ SC - Test 'Add New Flock' - create a second new flock for the secondary person. """ select = Select(self.driver.find_element_by_id('flock_menu_1_0')) select.select_by_visible_text('Create New Flock') self.wait_for_visibility("yesno_dialog", True) + flkno = str(int(self.TEST_MEM_NO) + 2) self.wait_for_text("yesno_dlg_text", - "Owner Mr J Bloggs already owns flock {} - of Bloggsy
" - "Add a new flock anyway?".format(str(int(self.TEST_MEM_NO) + 2))) + f"Owner Mr J Bloggs already owns flock {flkno} - of Bloggsy
" + "Add a new flock anyway?") self.click("yesno_dlg_yes") self.wait_for_visibility("yesno_dialog", False) self.wait_for_visibility('input_dialog', True) self.wait_for_text("input_dlg_title", "Create New Flock") @@ -1206,17 +1213,17 @@ self.click("flocknamesdlg_yes") self.wait_for_visibility("flocknames_dialog", False) self.check_yesno_dialog("Create a new flock 'Bloggsy2' for Mr J Bloggs?", "yesno_dlg_yes") - self.check_info_dialog("A new Flock 3610 has been created for Mr J Bloggs") + flkno2 = str(int(self.TEST_MEM_NO) + 3) + self.check_info_dialog(f"A new Flock {flkno2} has been created for Mr J Bloggs") self.wait_for_staleness(elem) self.check_members_persons(self.TEST_MEM_NO) - self.check_display_fields({'pers_1_flock_1': - ['Flock: {} Bloggsy2'.format(str(int(self.TEST_MEM_NO) + 3)), '']}) + self.check_display_fields({'pers_1_flock_1': [f'Flock: {flkno2} Bloggsy2', '']}) @attr('persons') def test_TA(self): """ TA - Test making persons active/inactive. These actions each refresh the page """ lastpers = self.get_last_person() @@ -1381,10 +1388,11 @@ @classmethod def setup_class(cls): test.setup_class(cls, 'member details', 'Firefox', refresh=True) test.add_dummy_member(cls) + cls.LAST_FLOCK = cls.get_last_flock(cls)[0] @classmethod def teardown_class(cls): test.teardown_class(cls) @@ -1393,10 +1401,11 @@ @classmethod def setup_class(cls): test.setup_class(cls, 'member details', 'Chrome', refresh=True) test.add_dummy_member(cls) + cls.LAST_FLOCK = cls.get_last_flock(cls)[0] @classmethod def teardown_class(cls): test.teardown_class(cls) Index: ppdb/test/utests/test_flock_uris.py ================================================================== --- ppdb/test/utests/test_flock_uris.py +++ ppdb/test/utests/test_flock_uris.py @@ -84,13 +84,13 @@ ('BA', "/db/flock/dun*", 200, 'html', "Result of searching on 'dun*' - 4 results"), ('BB', "/ofb/flock/Dun*", 200, 'html', "Result of searching on 'Dun*' - 4 results"), ('BC', "/db/flock/dun*", 200, 'json', "data#4"), # Navigation requests - ('CA', "/db/flock/nav/last", 200, 'json', "3606"), - ('CB', "/db/flock/nav/last", 200, 'json', "3606"), - ('CC', "/db/flock/nav/last", 200, 'json', "3606"), + ('CA', "/db/flock/nav/last", 200, 'json', "3959"), + ('CB', "/db/flock/nav/last", 200, 'json', "3959"), + ('CC', "/db/flock/nav/last", 200, 'json', "3959"), ('CD', "/db/flock/nav/prev", 400, 'json', "Missing 'oldflk' parameter"), ('CE', "/db/flock/nav?rid=prev&oldflk=1189", 200, 'json', "1184"), ('CF', "/db/flock/nav/next/2000", 200, 'json', "2002"), ('CG', "/db/flock/nav?rid=fprev&oldflk=1731", 200, 'json', "1461"), ('CH', "/db/flock/nav?rid=fnext&oldflk=1698", 200, 'json', "2026"),