You should have received a copy of the GNU General Public License
    along with this program.  If not, see <https:www.gnu.org/licenses/>.
    
"""
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))

    def message(self, message, warn=False, init=False):

                sys.stdout.flush()
                
    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...")
        self.rationalise_phone_types()
        self.message( "Cleaning Areas...")

                raise CleanError("%s sheep did not have their registering "
                                            "person found." % len(rows))
        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. 
            c.execute("CREATE TABLE brdr_person_base AS %s" % brdr_base_qry)
            self.db_conn.commit()

                                    "values(NULL, 3, 3, ?, 'System', ?, ?, 'SSB900', "
                                    "'2021-01-08', '2021-01-08', 4);", 
                                    ((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()                        
           
        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))
        
    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' "
                         "where person_no = '587';")
        except sqlite3.Error as err:

            """ Correct Inspector name """
            c.execute("UPDATE approval SET Inspector_1 = 'JM Watson' "
                        "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.' "
                        "WHERE testing_lab = 'CATAPULT'")
            c.execute("UPDATE prpgene "

                        "where sheep_no = 'Z10487';") 
            self.db_conn.commit()
        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'),
                
        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', "
                        ('Goosnargh and Longridge Show', 'Goosnargh and Longridge'),
                        ('Member 3204, Greenlands Farm, Carnforth', 'Greenlands'),
                        ('Harbridge House Meadow', 'Hasbridge House Meadow'),
                                "venue_type = 'Farm', member_no = '2471' "
                                "where venue like 'Owner%' and owner_person = 1116 and "
                                "sort_approval_date = '2002-10-22';")
                            
                        ('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'),
            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'),

                        ('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'),
            ('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'),
                        ('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'),
            ('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%'),
           
                        ('Member 2848, Sandyacre, Newark', 'Sandyacres'),
                        ('Member 1003, Willowcroft, Somerset', 'Willowcroft'),
                        ('Member 1003, Willowcroft, Somerset', 'Crockerton'),
            ("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'),
                        ('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'),
            ('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'),
                        ('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'),
            ('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%'),
                        ('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'),
            ('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'),
            
                        ('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'),
            ('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%'),
            
                        ('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%'),
            
            ("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'),
            
                                ('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%'),
            ('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)

                                ('Member 2116, Welland Down Farm, Devon', 'Welland%'),]
                                
        
        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)
           

                
            # Make a copy of the raw file
            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']:
            if self.ops['clean']:
                sourcedb = self.cleandb

        self.message(" Transforming sheep_flock table...")
        self.transform_sheep_flock_table()
        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()
        self.message(" Creating login tables")
        self.populate_login_tables()

        self.create_table('eid_type')
        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) "
                            "FROM olddb.prpgene;", (self.ts_now,))
                            

            self.curs.executemany("INSERT INTO audit_history "
                                "VALUES (NULL, 'UPDATE', ?, 'converter', 'Change', "
                                "'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.
        """
        
        self.message("    creating ai_sires...")

        # Tar the files
        tarf = "{}.tar.gz".format(os.path.splitext(self.outfile)[0])
        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):
    """ Class to convert a web schema Sqlite3 database to a test database.
        

        self.select_flock_related()
        self.message(" Resetting transfers...")
        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)
        self.message(" Detaching the 'old' database...")  
        self.curs.execute("detach database olddb;")

    def select_member_related(self):
        """ Select the member records related to the persons in the person table. """
        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')
        try:
            self.curs.execute("insert into mem_pmts "

            self.curs.execute("insert into fbk_order "
                                "select * from olddb.fbk_order "
                                "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 
            additional transfers
        """

                                                                                str(err))
        # test_sheep_data.py testcase CA - 47916 should still be in flock 1003
        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
        try:
            ts_now = util.isots_now()

                                        'PUT': ('regsec', 'admin',),
                                        'POST': ('regsec', 'admin',),
                                        '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'),
                                        'PUT': ('regsec', 'admin',),
                                        'POST': ('regsec', 'admin',),

""" The absolute path to the package. Initialised at app startup by ppd.py """

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'

USER_FUNC_MEMSEC = 'M'
USER_FUNC_REGSEC = 'R'

                "data": {"flock_no": flockno, "flock_name": flklist[0][1],
                        "prefixes": prefixes}}

@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!'
        
    def POST(self):
        """ Update a sheep's transfer 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):
        """ Add a new inspection record. """
        return 'Under Construction!'
@cherrypy.expose
class Genotype(SheepBase):
    """ Sheep genotyping.

    This class supports the GET, POST and PUT methods.
    Additional path elements/query args are: 'rid' and 'hist'.
    

        with pglib.cursor() as curs:
            curs.execute("select e.email_id, e.person_id, e.address, e.comment, "
                                "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 "
        "from member m "
        "join person p on m.member_no = p.member_no "

    along with this program.  If not, see <https://www.gnu.org/licenses/>.
    
"""
# 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", 
            "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"]
                
login_tables = ["access_role", "user_login", "user_role", "url_role"]

    "triggers": ["set_ts", "audit_action"],
    "select_cols": [0],
    "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"),
                ("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"),
                ("change_reason", "VARCHAR(32) NOT NULL"),
                ("last_changed", "TIMESTAMP 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",
    "history": "yes",
    "triggers": ["set_ts", "audit_action"],

//
// 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 <https://www.gnu.org/licenses/>.
//
"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);
};

  font-size: 1.5em;
  line-height: 1.2em;
  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;
  grid-row-gap: 0.2em;
  align-items: center;

            <div id="sidenav-sheep" class="level2-btn">
              <a class="db-btn">Sheep</a>
              <div class="level2">
                <a class="db-btn" href="{{uri_root}}/sheep">Sheep Data</a>
                <a class="db-btn" href="{{uri_root}}/sheep/lookup">Lookup</a>
                <a class="db-btn" href="{{uri_root}}/sheep/new">Registrations</a>
                <a class="db-btn" href="{{uri_root}}/sheep/transfer">Transfers</a>
                <a class="db-btn" href="{{uri_root}}/sheep/insp">Inspections</a>
                <a class="db-btn" href="{{uri_root}}/sheep/genetics">Genotyping</a>
                <a class="db-btn" href="{{uri_root}}/sheep/pedigree">Pedigrees</a>
                <a class="db-btn" href="{{uri_root}}/sheep/progeny">Progeny List</a>
                <a class="db-btn" href="{{uri_root}}/sheep/commanc">Common Ancestors</a>
              {#
                <a class="db-btn" href="{{uri_root}}/sheep/defreg">Confirmation of Registration</a>
                <a class="db-btn" href="{{uri_root}}/sheep/pclist">Deferred Pedigree Certificates</a>
              #}
              </div>
            </div>
            {% break %}
          {% elif role == 'socsec' %}
            <div id="sidenav-sheep" class="level2-btn">
              <a class="db-btn">Sheep</a>
              <div class="level2">
                <a class="db-btn" href="{{uri_root}}/sheep">Sheep Data</a>
                <a class="db-btn" href="{{uri_root}}/sheep/lookup">Lookup</a>
              </div>
            </div>
            {% break %}
          {% endif %}
        {% endfor %}
        
        {% for role in loginroles %}
          {% if role in ('admin', 'regsec') %}
            <div id="sidenav-insp" class="level2-btn">
              <a class="db-btn">Inspections</a>
              <div class="level2">
                <a class="db-btn" href="{{uri_root}}/sheep/inspnew">New Inspections</a>
                <a class="db-btn" href="{{uri_root}}/sheep/insp">View Inspections</a>
                <a class="db-btn" href="{{uri_root}}/sheep/insp">Inspectors</a>
                <a class="db-btn" href="{{uri_root}}/sheep/insp">Venues</a>
              </div>
            </div>
            {% break %}
          {% endif %}
        {% endfor %}
        {% for role in loginroles %}
          {% if role in ('admin', 'regsec') %}
            <div id="sidenav-prp" class="level2-btn">
              <a class="db-btn">Genotyping</a>
              <div class="level2">
                <a class="db-btn" href="{{uri_root}}/sheep/genetics">Genotyping</a>
              </div>
            </div>
            {% break %}
          {% endif %}
        {% endfor %}
        
        {% for role in loginroles %}
          {% if role in ('admin', 'regsec') %}

{# 
// 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 <https://www.gnu.org/licenses/>.
//
#}
{% extends "templates/base.tmpl" %}
{% block styles %}
<style>
  #inspections-tframe {
    width: 70em;
  }

  #inspections-tframe td:nth-child(n + 2) {
    text-align: center;
  }

  #inspections-tframe td:nth-child(1),
  #inspections-tframe th:nth-child(1) {
    width: 0em;
  }

  #inspections-tframe td:nth-child(2),
  #inspections-tframe th:nth-child(2) {
    width: 6em;
  }

  #inspections-tframe td:nth-child(3),
  #inspections-tframe th:nth-child(3) {
    width: 20em;
  }

  #inspections-tframe td:nth-child(4),
  #inspections-tframe th:nth-child(4) {
    width: 6em;
  }

  #inspections-tframe td:nth-child(5),
  #inspections-tframe th:nth-child(5) {
    width: 30em;
  }

  #inspections-tframe td:nth-child(6),
  #inspections-tframe th:nth-child(6) {
    width: 8em;
  }

  #inspections-tframe tbody {
    height: 35em;
  }

</style>
{% endblock %}
{% block content %}
  <div id="inner" class="boxed centre">
    <noscript><p class="hilite">This page requires Javascript to function. Please enable Javascript in your browser's preference settings.</p></noscript>
    <span id="uri-root-txt" class="nodisplay">{{uri_root}}</span>
    <input id="venue-id" class="nodisplay" name = "venue-id" value="{{venue_id_no}}"> 
  <div class="centre">
    <div class="shrink">
      <h3 id="page-title" >{{pagetitle|e}}</h3>
      <div class="spacer">&nbsp;</div>
        <div class="shrink">
          <table id="inspections-tframe" class="table_frame">
            <thead>
              <tr>
                <th></th>
                <th class="datahead"><div id="regnno-btn" class="sort-btn" data-sb="regnno" 
                    tabindex="-1">
                    <span >Regn No</span><span >&nbsp;{{sb_regnno}}</span></div>
                </th>
                <th class="datahead"><div id="name-btn" class="sort-btn" data-sb="name" 
                    tabindex="-1">
                    <span >Sheep Name</span><span >&nbsp;{{sb_name}}</span></div>
                </th>
                <th class="datahead"><div id="date-btn" class="sort-btn" data-sb="date" 
                    tabindex="-1">
                    <span >Insp. Date</span><span >&nbsp;{{sb_date}}</span></div>
                </th>
                <th class="datahead"><div id="venue-btn" class="sort-btn" data-sb="venue" 
                    tabindex="-1">
                    <span >Venue</span><span >&nbsp;{{sb_venue}}</span></div>
                </th>
                <th class="datahead"><div id="result-btn" class="sort-btn" data-sb="result" 
                    tabindex="-1">
                    <span >Result</span><span >&nbsp;{{sb_result}}</span></div>
                </th>
              </tr>
            </thead>
            <tbody id="inspections-tbody">
              {% for insp in inspections %}
                {% if loop.index is odd %}
                  <tr class="even-row">
                {% else %}
                  <tr>
                {% endif %}
                  <td>{{insp[0]}}</td>
                  <td>{{insp[1]}}</td>
                  <td>{{insp[2]}}</td>
                  <td>{{insp[3]}}</td>
                  <td>{{insp[4]}}</td>
                  <td>{{insp[5]}}</td>
                </tr>
              {% endfor %}
            </tbody>
          </table>
          <div class="left">
            <span>Click in a row to see the full details including inspectors</span>
          </div>
        </div>
        <div >&nbsp;</div>
        <div class="boxed">
        </div>
      </div>
    </div>
    
  </div> {# End of inner div #}
{% endblock %}
{% block dialogs %}
{% endblock %}
{%block scripts %}
{% endblock %}

{# 
// 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 <https://www.gnu.org/licenses/>.
//
#}
{% extends "templates/base.tmpl" %}
{% block styles %}
<style>
  div.two-col-outer {
    display: flex;
    justify-content: space-evenly;
  } 

  div.two-col-inner {
    width: 50%;
    display: grid;
    grid-template-columns: 7em 1fr;
    grid-auto-rows: minmax(2em, auto);
    grid-column-gap: 0.3em;
    grid-row-gap: 0.1em;
    align-items: start;
    text-align: initial;
  }
  
  div.inner-left {grid-auto-rows: 2em;}
  
  div.two-col-inner label {
    text-align: right;
  }

  div.venue-ctls {
      display: flex;
      flex-direction: column; 
      justify-content: flex-start;
  }
  
  div.newinsp {
    width: 72em;
    margin: auto;
  }
 
  #insp-tframe {
    width: 28em;
    margin: 0.2em;
  }
  #insp-tframe td:nth-child(1),
  #insp-tframe th:nth-child(1) {
    width: 0;
    max-width: 0;
    visibility: hidden;
  }
  #insp-tframe td:nth-child(2),
  #insp-tframe th:nth-child(2) {
    width: 2em;
    padding-left: 0.2em;
  }
  #insp-tframe td:nth-child(3),
  #insp-tframe th:nth-child(3) {
    width: 26em;
    padding-left: 0.2em;
  }

  #insp-tframe td:nth-child(4),
  #insp-tframe th:nth-child(4) {
    width: 5em;
    text-align: center;
  }
  #insp-tframe tbody {
      height: 20em;
      overflow-x: hidden;
      overflow-y: scroll;
    }
  
  div.result-btns {
    display: flex;
    justify-content: start;
  }
  
  div.venue-box {
    display: flex;
    flex-direction: column;
    align-items: center;
    justify-content: flex-start;
  }
  
  #venues-tframe {
      width: 33em;
      margin: 0.2em;
      
  }
  #venues-tframe td:nth-child(1),
  #venues-tframe th:nth-child(1) {
    width: 0;
    max-width: 0;
    visibility: hidden;
  }
  #venues-tframe td:nth-child(2),
  #venues-tframe th:nth-child(2) {
      width: 33em;
      padding-left: 0.2em;
  }
  #venues-tframe td:nth-child(3),
  #venues-tframe th:nth-child(3) {
      width: 8em;
      text-align: center;
      padding-right: 1em;
  }
  #venues-tframe td:nth-child(3) {
      text-align: center;
      padding-right: 0em;
  }
  #venues-tframe tbody {
      height: 24em;
      overflow-x: hidden;
      overflow-y: scroll;
    }
</style>
{% endblock %}
{% block content %}
  <div id="inner" class="boxed centre">
    <noscript><p class="hilite">This page requires Javascript to function. Please enable Javascript in your browser's preference settings.</p></noscript>
    <span id="uri-root-txt" class="nodisplay">{{uri_root}}</span>
    <input id="venue-id" class="nodisplay" name="venue-id" value="{{venue_id_no}}"> 
    <div class="centre">
      <div id="page_title">{{pagetitle|e}}</div>
      <div class="spacer">&nbsp;</div>
        <div class="newinsp boxed">
          <h3 id="venue-text">Venue Name here - Inspection Date here</h3> 
          <div class="two-col-outer"> 
            <div class="two-col-inner inner-left">
              <label>Ear Tag: </label>
              <div>
                <input id="ear-tag" name="tag-no" size="17" maxlength="17" tabindex="0" 
                                      value="{{tag_no}}" {{state}}>
                <span id="tag-error" class=""></span>
              </div>
              <label>Regn No: </label>
              <span id="regn-no-fld">{{regn_no}}</span>
              <label>Date of Birth: </label>
              <span id="dob-fld">{{dob}}</span>
              <label>Name: </label>
              <span id="full-name">{{full_name}}</span>
              <label>Colour: </label>
              <span id="colour">{{colour}}</span>
              <span>&nbsp;</span>
              <span id="sheep-status" class="">Status</span>
              <label>Owner: </label>
              <div>
                <input id="owner" name="owner" size="16" maxlength="16" tabindex="0" 
                      value="{{owner}}" {{state}}>
              </div>
              <span>&nbsp;</span>
              <div>
                <input id="xfer_cbox" class="" type="checkbox" name="xfer_sheep" 
                      value="xfer" tabindex="0" disabled>
                <span id="xfer_text" class="">Transfer text</span>
                <input id="xfer_date" class="" name="xfer_date" size="10" 
                      maxlength="10" tabindex="0" disabled>
              </div>
              <span>&nbsp;</span>
              <span>&nbsp;</span>
              <label>Result: </label>
              <div id="insp-result" class="result-btns">
                <div>
                  <input id="insp-pass" name="result" value="Pass" type="radio" checked>
                  <label for="insp-pass" class="left"> Pass</label>
                </div>
                <div>
                  <input id="insp-fail" name="result" value="fail" type="radio">
                  <label for="insp-fail" class="left"> Fail</label>
                </div>
                <div>
                  <input id="insp-post" name="result" value="post" type="radio">
                  <label for="insp-post" class="left"> Postponed</label>
                </div>
              </div>
              <div>&nbsp;</div>
            </div>
            <div class="two-col-inner">
              <label class="grid-top">Inspectors: </label>
              <table id="insp-tframe" class="table_frame grid-top">
                <thead>
                  <tr>
                    <th></th>
                    <th>T/I</th>
                    <th>Name</th>
                    <th>Select</th>
                  </tr>
                </thead>
                <tbody id="insp-cbody" data-pfx="insp">
                  {% for insp in inspectors %}
                    {% if loop.index0 is even %}
                      <tr class="even_row">
                    {% else %}
                      <tr>
                    {% endif %}
                      <td>{{insp[0]}}</td>
                      <td>{{insp[1]}}</td>
                      <td><label for="insp{{loop.index0}}">{{insp[2]}}</label</td>
                      <td><input id="insp{{loop.index0}}" type="checkbox"></td>
                    </tr>
                  {% endfor %}
                </tbody>
              </table>
            </div>
          </div>
          <div>&nbsp;</div>
    
          {% if ("regsec" in loginroles) %} 
          <div class="centre shrink">
            <div class="buttons_box boxed">
              <button id="new-venue-btn" tabindex="-1">New Venue Session</button>
              <div class="h-pad3">&nbsp;</div>
              <button id="save-btn" tabindex="-1" disabled>Save</button>
              <div class="h-pad3">&nbsp;</div>
              <button id="cancel-btn" tabindex="-1" disabled>Cancel</button>
            </div>
          </div>
          {% endif %}
        </div>
      </div>
    </div>
    
  </div> {# End of inner div #}
{% endblock %}
{% block dialogs %}
  <div id="venue-session-dialog" class="dialog invis">
    <div class="list_dialog">
      <h2 id="venue-session-title" class="centre">Set Venue Session Parameters</h2> 
      <div class="top boxed" >
        <span class="nodisplay" id="venue-selrec"></span>
        <div class="venue-box">
          <table id="venues-tframe" class="table_frame">
            <thead>
              <tr>
                <th></th>
                <th>Venue</th>
                <th>Type</th>
              </tr>
            </thead>
            <tbody id="venues-tbody">
              {% for venue in venues %}
                {% if loop.index is odd %}
                  <tr class="even_row">
                {% else %}
                  <tr class="">
                {% endif %}
                  <td>{{venue[0]}}</td>
                  <td>{{venue[1]}}</td>
                  <td>{{venue[2]}}</td>
                </tr>
              {% endfor %}
            </tbody>
          </table>
          <div class="venue-ctls">
            <div>
              <div>
                <span>Click in a row to select it, or click the 'Add New' button for a new venue</span>
              </div>
              <div class="centre">
                <button id="add-venue-btn">Add New Venue</button>
              </div>
              <div>&nbsp;</div>
              <div class="dlg-grid">
                <label id="insp-date-label">Inspections Date:</label>
                <div>
                  <input type="text" id="insp-date-field" size="24" maxlength="32">
                </div>
                <div id="insp-date-msg" class="invis">&nbsp;</div>
              </div> 
              <div class="dlg-grid invis">
                <label id="new-venue-label" class="grayed">Venue Name:</label>
                <div>
                  <input type="text" id="new-venue-field" size="72" maxlength="96" disabled>
                </div>
                <div id="new-venue-msg" class="invis">&nbsp;</div>
              </div>
            </div>
          </div>
        </div>
      </div>
      <div class="spacer">&nbsp;</div>
      <div class="buttons_box">
        <button id="venue-dlg-submit">OK</button>
        <button id="venue-dlg-cancel">Cancel</button>
    </div>
  </div> 

{% endblock %}
{%block scripts %}
<script type="module" src="{{uri_root}}/static/js/inspnew.js"></script>
{% endblock %}

        resp = test.make_request(self.host, 'PUT', "/person", 200, body=body)
        return resp["data"]                                                                
        
    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 """
        
        def check_return(resp):
            if resp['status'] == 'OK':

        """ BA - test the 'first' VCR control """
        self.click('first_btn')
        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")
    
    def test_BD(self):

        tbody =  self.driver.find_element_by_id("flk_owner_tbody")
        row = tbody.find_elements_by_css_selector('tr')[0]
        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')
        self.check_health_schemes_state(True)
                    

        
        self.check_health_schemes_state(False)
        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")
        self.wait_for_text("input_dlg_label", "New Prefix:")
        self.click('input_dlg_cancel')

    @attr('prefix')
    def test_FE(self):
        """ 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<br>"
        self.check_yesno_dialog(f"You are trying to add a non-EID EU prefix to flock {self.LAST_FLOCK}<br>"
            "A non-EID EU prefix may only be used to register sheep born before 1/1/2010<br>"
            "Do you want to continue?", "yesno_dlg_no")

    @attr('prefix')
    def test_FF(self):
        """ 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<br>"
        self.check_yesno_dialog(f"You are trying to add a non-EID EU prefix to flock {self.LAST_FLOCK}<br>"
            "A non-EID EU prefix may only be used to register sheep born before 1/1/2010<br>"
            "Do you want to continue?", "yesno_dlg_yes")
        self.check_prefixes_frame([('UK330743', 1)])    
    
    @attr('prefix')
    def test_FG(self):
        """ FG - test the New Prefix dialog with a non-matching EID prefix - cancel """

        self.wait_for_text("change_prefix_btn", "Change Current Prefix")
        self.check_prefixes_frame([('UK0320743', 0), ('UK330743', 1)])  
        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")
        self.wait_for_text("input_dlg_label", "Owner:")
        self.click('input_dlg_cancel')

    @attr('flock')
    def test_GK(self):
        """ 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"
                                "<br>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')
        self.wait_for_visibility("input_dialog", False)
        
        self.check_warning_dialog("Flock 'of Ferndale' is already owned by Mr N Hill "
                                    "(Member 0153).<br><br>Flock names with and without "
                                    "'of' are regarded as identical.")
        
    @attr('flock')
    def test_GL(self):
        """ 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"
                                "<br>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')
        self.wait_for_visibility("input_dialog", False)
        
        self.check_warning_dialog("Flock 'of Ferndale' is already owned by Mr N Hill "
                                    "(Member 0153).<br><br>Flock names with and without "
                                    "'of' are regarded as identical.")
        
    @attr('flock')
    def test_GM(self):
        """ 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"
                                "<br>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')
        self.wait_for_visibility("input_dialog", False)
        

class TestFlockDataPGFF(TCases):
    
    @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)
    
    @classmethod        
    def teardown_class(cls):  
        test.teardown_class(cls)

@attr('chrome')
class TestFlockDataPGCr(TCases):
    
    @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)
        

        """ Test GE - Open the Sheep submenu and check all the items are present """
        elem = self.wait_for_link_text("Sheep") 
        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),)
                            )
            
    def test_GF(self):
        """ Test GF - Open the Flock Book submenu and check all the items are present """

        """ Test HE - Open the Sheep submenu and check all the items are present """
        elem = self.wait_for_link_text("Sheep") 
        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),)
                            )
            
    def test_HF(self):
        """ Test HF - Open the Flock Book submenu and check all the items are present """

    """ Common methods for testing the memrpts form """
    
    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"))
        self.assert_equal(self.driver.find_element_by_id("info_dlg_text").text, expected)
        self.driver.find_element_by_id("info_dialog_close").click()

        self.set_input_field("input_dlg_field", "symbionic")
        self.click('input_dlg_submit')
        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'))
        select.select_by_visible_text('Create New Flock')
        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 new flock name")
        self.wait_for_text("input_dlg_label", "Flock Name:")
        self.set_input_field("input_dlg_field", "of bloggsy")
        self.click('input_dlg_submit')
        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<br>"
            "Add a new flock anyway?".format(str(int(self.TEST_MEM_NO) + 2)))
                            f"Owner Mr J Bloggs already owns flock {flkno} - of Bloggsy<br>"
                            "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")
        self.wait_for_text("input_dlg_instr", "Enter new flock name")
        self.wait_for_text("input_dlg_label", "Flock Name:")
        self.set_input_field("input_dlg_field", "bloggsy2")
        self.click('input_dlg_submit')
        self.wait_for_visibility("input_dialog", False)
        
        elem = self.driver.find_element_by_id('pers_1_info')
        self.wait_for_visibility("flocknames_dialog", True)
        self.wait_for_text("flocknamesdlg_title", 
                                        "Flocks with names similar to 'Bloggsy2'")  
        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")
        flkno2 = str(int(self.TEST_MEM_NO) + 3)                                                                    
        self.check_info_dialog("A new Flock 3610 has been created for Mr J Bloggs")                                                                    
        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': 
        self.check_display_fields({'pers_1_flock_1': [f'Flock: {flkno2} Bloggsy2', '']})
                        ['Flock: {} Bloggsy2'.format(str(int(self.TEST_MEM_NO) + 3)), '']})
        
    @attr('persons')       
    def test_TA(self):
        """ TA - Test making persons active/inactive. These actions each refresh the page """
        lastpers = self.get_last_person()
        select = Select(self.driver.find_element_by_id('person_menu_' + str(lastpers)))
        elem = self.driver.find_element_by_id('pers_0_info')

@attr('firefox', 'local')
class TestMembersPGFF(TCases):
    
    @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)

@attr('chrome', 'local')
class TestMembersPGCr(TCases):
    
    @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)
        

    
    # Multiple flock requests
    ('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"),
    
    # Prefixes requests