def verify_seller(seller_name, seller_adhar_number, seller_email_id, property_name): try: db_obj = db_info.Land_Registry_Portal() query = 'SELECT owner_id FROM land_registry_portal.tbl_owner_details WHERE owner_name = %s AND adhar_number = %s AND email = %s;' args = (seller_name, seller_adhar_number, seller_email_id) res = db_obj.select_db(query, args) if res == (): return False, -1 owner_id = res[0]['owner_id'] query = 'SELECT current_owner_id, list_of_property_id FROM land_registry_portal.tbl_land_registry_portal_details WHERE property_name = %s;' args = (property_name) res = db_obj.select_db(query, args) if res == (): return False, -1 current_owner_id = res[0]['current_owner_id'] property_id = (res[0]['list_of_property_id']).split(',')[-1] if owner_id == current_owner_id: return True, property_id else: return False, -1 except: return False, -1
def retrieve_property_details_by_address(plot_number, sector_number, road_name, area_name, city_name, state_name, country_name, pin_code): property_details = [] args_list = [] db_obj = db_info.Land_Registry_Portal() query = 'SELECT * FROM land_registry_portal.tbl_property_address_details WHERE ' if plot_number == '' and sector_number == '' and road_name == '' and area_name == '' and city_name == '' and state_name == '' and country_name == '' and pin_code == '': query = query[:-7] + ';' #print(query) else: if plot_number != '': query += 'plot_number = %s AND ' args_list.append(plot_number) if sector_number != '': query += 'sector_number = %s AND ' args_list.append(sector_number) if road_name != '': query += 'road_name = %s AND ' args_list.append(road_name) if area_name != '': query += 'area_name = %s AND ' args_list.append(area_name) if city_name != '': query += 'city_name = %s AND ' args_list.append(city_name) if state_name != '': query += 'state_name = %s AND ' args_list.append(state_name) if country_name != '': query += 'country_name = %s AND ' args_list.append(country_name) if pin_code != '': query += 'pin_code = %s AND ' args_list.append(pin_code) query = query[:-5] + ';' #print(query) args = tuple(args_list) res = db_obj.select_db(query, args) #print(res) if res != (): for i in range(len(res)): property_name = res[i]['property_name'] address = 'Plot no. : ' + str( res[i]['plot_number'] ) + ', Sector no. : ' + str(res[i]['sector_number']) + ', ' + res[ i]['road_name'] + ', ' + res[i]['area_name'] + ', ' + res[i][ 'city_name'] + ', ' + res[i]['state_name'] + ', ' + res[i][ 'country_name'] + ' - ' + str(res[i]['pin_code']) property_details.append(property_name + ';' + address) return property_details
def extract_key_for_decryption(property_id): try: db_obj = db_info.Land_Registry_Portal() query = 'SELECT encryption_key FROM land_registry_portal.tbl_advanced_encryption_standard WHERE property_id = %s;' args = (property_id) res = db_obj.select_db(query, args) key = res[0]['encryption_key'] return key except: traceback.print_exc()
def add_details_to_blockchain(property_id, property_name): db_obj = db_info.Land_Registry_Portal() query = 'SELECT encryption_key FROM land_registry_portal.tbl_advanced_encryption_standard WHERE property_id = %s and property_name = %s;' args = (property_id, property_name) res = db_obj.select_db(query, args) encryption_key = res[0]['encryption_key'] #encryption_key = encryption_key.decode('utf-8') query = 'SELECT property_paper_hash FROM land_registry_portal.tbl_inter_planetary_file_system WHERE property_id = %s and property_name = %s;' args = (property_id, property_name) res = db_obj.select_db(query, args) property_paper_hash = res[0]['property_paper_hash'] query = 'SELECT current_owner_id FROM land_registry_portal.tbl_land_registry_portal_details WHERE property_name = %s;' args = (property_name) res = db_obj.select_db(query, args) current_owner_id = res[0]['current_owner_id'] query = 'SELECT owner_name, adhar_number, email FROM land_registry_portal.tbl_owner_details WHERE owner_id = %s;' args = (current_owner_id) res = db_obj.select_db(query, args) owner_name = res[0]['owner_name'] adhar_number = res[0]['adhar_number'] email = res[0]['email'] owner_details = str(property_id) + str(property_name) + str( encryption_key) + str(property_paper_hash) + str(owner_name) + str( adhar_number) + str(email) hash = Web3.soliditySha3(['string'], [owner_details]) print(hash) url = 'http://127.0.0.1:7545' web3 = Web3(Web3.HTTPProvider(url)) if web3.isConnected(): abi = json.loads( '[{"constant":false,"inputs":[{"internalType":"int256","name":"_property_id","type":"int256"},{"internalType":"string","name":"_owner_hash","type":"string"}],"name":"add_details","outputs":[],"payable":false,"stateMutability":"nonpayable","type":"function"},{"constant":true,"inputs":[{"internalType":"int256","name":"_seller_property_id","type":"int256"},{"internalType":"string","name":"_seller_hash","type":"string"}],"name":"approve_details","outputs":[{"internalType":"bool","name":"","type":"bool"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":true,"inputs":[{"internalType":"int256","name":"_property_id","type":"int256"}],"name":"getHash","outputs":[{"internalType":"string","name":"","type":"string"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":true,"inputs":[{"internalType":"int256","name":"_property_id","type":"int256"}],"name":"getStatus","outputs":[{"internalType":"int256","name":"","type":"int256"}],"payable":false,"stateMutability":"view","type":"function"},{"constant":false,"inputs":[{"internalType":"int256","name":"_property_id","type":"int256"}],"name":"revoke_ownership","outputs":[],"payable":false,"stateMutability":"nonpayable","type":"function"}]' ) address = web3.toChecksumAddress( '0x5f50715071EBa9adA24971FC82347a164cF233C1') contract = web3.eth.contract(address=address, abi=abi) tx_hash = contract.functions.add_details( property_id, str(hash)).transact( {"from": '0xC3777FdDe7B3CaEa4ae874D7cb94d1405b113eFd'}) web3.eth.waitForTransactionReceipt(tx_hash)
def verify_buyer_and_add_to_database(buyer_name, buyer_adhar_number, buyer_email_id): db_obj = db_info.Land_Registry_Portal() # Check whether buyer already exists in database or not. query = 'SELECT owner_id FROM land_registry_portal.tbl_owner_details WHERE owner_name = %s AND adhar_number = %s;' args = (buyer_name, buyer_adhar_number) owner_id = db_obj.select_db(query, args) if owner_id == (): # The owner details don't exist in the system. New owner_id needs to be created! query = 'INSERT INTO land_registry_portal.tbl_owner_details(owner_name, adhar_number, email) VALUES (%s, %s, %s);' args = (buyer_name, buyer_adhar_number, buyer_email_id) db_obj.insert_db(query, args)
def generate_key_for_advanced_encryption_standard(property_name, extension): try: #generate key key = Fernet.generate_key() db_obj = db_info.Land_Registry_Portal() query = 'INSERT INTO land_registry_portal.tbl_advanced_encryption_standard(property_name, encryption_key, extension) VALUES (%s, %s, %s);' args = (property_name, key.decode('utf-8'), extension) db_obj.insert_db(query, args) query = 'SELECT property_id FROM land_registry_portal.tbl_advanced_encryption_standard WHERE encryption_key = %s;' args = (key.decode('utf-8')) res = db_obj.select_db(query, args) #print(res) property_id = res[0]['property_id'] return property_id, key except: traceback.print_exc()
def upload_file_in_ipfs(property_id, property_name, file_name): # Connect to local node try: api = ipfsapi.connect('127.0.0.1', 5001) #print(api) new_file = api.add(file_name) ipfs_hash = new_file['Hash'] db_obj = db_info.Land_Registry_Portal() query = 'INSERT INTO land_registry_portal.tbl_inter_planetary_file_system(property_id, property_name, property_paper_hash) VALUES (%s, %s, %s);' args = (property_id, property_name, ipfs_hash) db_obj.insert_db(query, args) os.remove(file_name) return ipfs_hash except ipfsapi.exceptions.ConnectionError as ce: print(str(ce))
def add_witness_details(property_id, property_name, witness1_name, witness1_aadhar_number, witness1_email_id, witness1_mobno, witness2_name, witness2_aadhar_number, witness2_email_id, witness2_mobno): try: db_obj = db_info.Land_Registry_Portal() query = 'INSERT INTO land_registry_portal.tbl_witness_details VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s);' args = (property_id, property_name, witness1_name, witness2_name, witness1_aadhar_number, witness2_aadhar_number, witness1_email_id, witness2_email_id, witness1_mobno, witness2_mobno) db_obj.insert_db(query, args) except: traceback.print_exc() #update_database('abc', '123412341234', '*****@*****.**', 'prop1', 23) #update_database(624, 18, 'Akurdi Railway Station', 'Akurdi', 'Pune', 'Maharashtra', 'India', 411019)
def view_property(property_name): property_details = [] db_obj = db_info.Land_Registry_Portal() query = 'SELECT current_owner_id, list_of_property_id FROM land_registry_portal.tbl_land_registry_portal_details WHERE property_name = %s;' args = (property_name) res = db_obj.select_db(query, args) current_owner_id = res[0]['current_owner_id'] list_of_property_id = res[0]['list_of_property_id'] property_id = list_of_property_id.split(',')[-1] query = 'SELECT * FROM land_registry_portal.tbl_owner_details WHERE owner_id = %s;' args = (current_owner_id) res = db_obj.select_db(query, args) owner_name = res[0]['owner_name'] adhar_number = res[0]['adhar_number'] email = res[0]['email'] registration_time = res[0]['registration_time'] query = 'SELECT property_paper_hash FROM land_registry_portal.tbl_inter_planetary_file_system WHERE property_name = %s AND property_id = %s;' args = (property_name, property_id) res = db_obj.select_db(query, args) property_hash = res[0]['property_paper_hash'] #ModuleInterPlanetaryFileSystem.retrieve_file_from_ipfs(property_id, property_name, property_hash) #decrypted_file = ModuleAdvancedEncryptionStandard.decrypt_file(property_id) property_details.append(property_id) property_details.append(property_name) property_details.append(owner_name) property_details.append(adhar_number) property_details.append(email) property_details.append(registration_time) return property_details
def retrieve_property_details_by_name(owner_name, adhar_number, email_id): try: property_details = [] args_list = [] db_obj = db_info.Land_Registry_Portal() # Check whether owner already exists in database or not. query = 'SELECT owner_id FROM land_registry_portal.tbl_owner_details WHERE ' if owner_name == '' and adhar_number == '' and email_id == '': return property_details else: if owner_name != '': query += 'owner_name = %s AND ' args_list.append(owner_name) if adhar_number != '': query += 'adhar_number = %s AND ' args_list.append(adhar_number) if email_id != '': query += 'email = %s AND ' args_list.append(email_id) query = query[:-5] + ';' args = tuple(args_list) owner_id = db_obj.select_db(query, args) if owner_id == (): return property_details else: # Owner already exixts in database. Directly extract the owner id. owner_id = owner_id[0]['owner_id'] query = 'SELECT property_name FROM land_registry_portal.tbl_land_registry_portal_details WHERE current_owner_id = %s;' args = (owner_id) property_name = db_obj.select_db(query, args) if property_name == (): return property_details else: for d in property_name: query = 'SELECT * FROM land_registry_portal.tbl_property_address_details where property_name = %s;' args = (d['property_name']) res = db_obj.select_db(query, args) address = 'Plot no. : ' + str( res[0]['plot_number'] ) + ', Sector no. : ' + str( res[0]['sector_number'] ) + ', ' + res[0]['road_name'] + ', ' + res[0][ 'area_name'] + ', ' + res[0]['city_name'] + ', ' + res[ 0]['state_name'] + ', ' + res[0][ 'country_name'] + ' - ' + str( res[0]['pin_code']) property_details.append(d['property_name'] + ';' + address) return property_details except: return property_details
def update_database(owner_name, adhar_number, email, property_name, property_id, plot_number=None, sector_number=None, road_name=None, area_name=None, city_name=None, state_name=None, country_name=None, pin_code=None): try: db_obj = db_info.Land_Registry_Portal() # Check whether owner already exists in database or not. query = 'SELECT owner_id FROM land_registry_portal.tbl_owner_details WHERE owner_name = %s AND adhar_number = %s;' args = (owner_name, adhar_number) owner_id = db_obj.select_db(query, args) if owner_id == (): # The owner details don't exist in the system. New owner_id needs to be created! query = 'INSERT INTO land_registry_portal.tbl_owner_details(owner_name, adhar_number, email) VALUES (%s, %s, %s);' args = (owner_name, adhar_number, email) db_obj.insert_db(query, args) # The entry for new user is made. Now owner_id need to be extracted. query = 'SELECT * FROM land_registry_portal.tbl_owner_details WHERE owner_name = %s AND adhar_number = %s;' args = (owner_name, adhar_number) owner_id = db_obj.select_db(query, args) owner_id = owner_id[0]['owner_id'] else: # Owner already exixts in database. Directly extract the owner name. owner_id = owner_id[0]['owner_id'] # Check for a transaction related to property_name already exists or not. query = 'SELECT property_name FROM land_registry_portal.tbl_land_registry_portal_details WHERE property_name = %s;' args = (property_name) prop_name = db_obj.select_db(query, args) if prop_name == (): # The transaction with property_name doesn't exist. Need to create new transaction for the same. query = 'INSERT INTO land_registry_portal.tbl_land_registry_portal_details VALUES (%s, %s, %s, %s);' args = (property_name, owner_id, property_id, '') db_obj.insert_db(query, args) else: # The transaction with property_name already exists. So append previous old owner_id and property_id and update current owner_id. query = 'SELECT current_owner_id, list_of_property_id, list_of_owner_id FROM land_registry_portal.tbl_land_registry_portal_details WHERE property_name = %s;' args = (property_name) details = db_obj.select_db(query, args) old_owner_id = details[0]['current_owner_id'] list_of_property_id = details[0][ 'list_of_property_id'] + ',' + str(property_id) list_of_owner_id = details[0]['list_of_owner_id'] if list_of_owner_id == '': list_of_owner_id = list_of_owner_id + str(old_owner_id) else: list_of_owner_id = list_of_owner_id + ',' + str(old_owner_id) query = 'UPDATE land_registry_portal.tbl_land_registry_portal_details SET current_owner_id = %s, list_of_property_id = %s, list_of_owner_id = %s WHERE property_name = %s;' args = (owner_id, list_of_property_id, list_of_owner_id, property_name) db_obj.update_db(query, args) # Insert property details in database if plot_number != None: query = 'INSERT INTO land_registry_portal.tbl_property_address_details VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s);' args = (property_name, plot_number, sector_number, road_name, area_name, city_name, state_name, country_name, pin_code) db_obj.insert_db(query, args) except: traceback.print_exc()