def insert(inserted):
# assigning variables from inserted
global data
data = inserted[0]
single_elements = inserted[1]
mark_data = inserted[2]
mark_elements = inserted[3]
gs_bag_data = inserted[4]
gs_elements = inserted[5]
national_cor_data = inserted[6]
national_cor_elements = inserted[7]
record_attorney_data = inserted[8]
record_attorney_elements = inserted[9]
applicant_data = inserted[10]
applicant_elements = inserted[11]
nat_trade_data = inserted[12]
nat_trade_elements = inserted[13]
# connect to the postgres database
conn = psycopg2.connect(database=settings.database(),
user=settings.user(),
password=settings.password()) # Connect database
# format all the datafields
data = proof_data(data, single_elements)
mark_data = proof_data(mark_data, mark_elements)
gs_bag_data = proof_data(gs_bag_data, gs_elements)
national_cor_data = proof_data(national_cor_data, national_cor_elements)
record_attorney_data = proof_data(record_attorney_data,
record_attorney_elements)
applicant_data = proof_data(applicant_data, applicant_elements)
nat_trade_data = proof_data(nat_trade_data, nat_trade_elements)
# create a cursor which can execute postgres commands
cur = conn.cursor() # Initiate the cursor which executes postgres commands
# cur.execute('''drop table if exists ''' + table_out +';') # Remove old table
# make lawyer table
print data['ApplicationNumberText']
# create lawyer table
cur.execute('''CREATE TABLE IF NOT EXISTS lawyer
(
id SERIAL PRIMARY KEY,
attorney_name TEXT,
attorney_comment TEXT,
contact_name TEXT,
organization_standard_name TEXT,
address_line_text TEXT,
address_line_text_2 TEXT,
city_name TEXT,
geographic_region_name VARCHAR(25),
country_code VARCHAR(25),
postal_code VARCHAR(25),
email_address_text TEXT,
phone_number VARCHAR(25),
fax_number VARCHAR(25),
website TEXT
);''')
# insert into lawyer table
cur.execute(
'''INSERT INTO lawyer
(
attorney_name,
attorney_comment,
contact_name,
organization_standard_name,
address_line_text,
address_line_text_2,
city_name,
geographic_region_name,
country_code,
postal_code,
email_address_text,
phone_number,
fax_number,
website
)
SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT email_address_text
FROM lawyer
WHERE email_address_text LIKE %s
)''', (record_attorney_data['PersonFullName'],
record_attorney_data['CommentText'],
national_cor_data['PersonFullName'],
national_cor_data['OrganizationStandardName'],
national_cor_data['AddressLineText'],
national_cor_data['AddressLineText2'], national_cor_data['CityName'],
national_cor_data['GeographicRegionName'],
national_cor_data['CountryCode'], national_cor_data['PostalCode'],
national_cor_data['EmailAddressText'],
national_cor_data['PhoneNumber'], national_cor_data['FaxNumber'],
get_website(national_cor_data['EmailAddressText']),
national_cor_data['EmailAddressText']))
# create trademark table
cur.execute('''CREATE TABLE IF NOT EXISTS trademark
(
id SERIAL PRIMARY KEY,
serial_number INTEGER,
registration_office_code VARCHAR(5),
ip_office_code VARCHAR(5),
registration_number VARCHAR(20),
application_date DATE,
registration_date DATE,
filing_place VARCHAR(5),
mark_current_status_date DATE,
mark_category VARCHAR(25),
mark_feature_category VARCHAR(100),
first_used_date DATE,
blank_month_1 BOOLEAN,
blank_day_1 BOOLEAN,
first_used_commerce_date DATE,
blank_month_2 BOOLEAN,
blank_day_2 BOOLEAN,
publication_identifier VARCHAR(100),
publication_date DATE,
class_number VARCHAR(5),
goods_services_description_text TEXT,
national_status_category TEXT,
national_status_code TEXT,
national_status_external_description_text TEXT
);''')
# insert into trademark table
cur.execute(
'''INSERT INTO trademark
(
serial_number,
registration_office_code,
IP_office_code,
registration_number,
application_date,
registration_date,
filing_place,
mark_current_status_date,
mark_category,
mark_feature_category,
first_used_date,
blank_month_1,
blank_day_1,
first_used_commerce_date,
blank_month_2,
blank_day_2,
publication_identifier,
publication_date,
class_number,
goods_services_description_text,
national_status_category,
national_status_code,
national_status_external_description_text
)
SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s
FROM lawyer
WHERE lawyer.email_address_text LIKE %s
AND NOT EXISTS
(
SELECT serial_number
FROM trademark
WHERE serial_number = %s
)''',
(data['ApplicationNumberText'], data['RegistrationOfficeCode'],
data['IPOfficeCode'], data['RegistrationNumber'], get_date(data),
data['RegistrationDate'], data['FilingPlace'],
data['MarkCurrentStatusDate'], data['MarkCategory'],
data['MarkFeatureCategory'], data['FirstUsedDate'],
data['BlankMonth1'], data['BlankDay1'], data['FirstUsedCommerceDate'],
data['BlankMonth2'], data['BlankDay2'], data['PublicationIdentifier'],
data['PublicationDate'], data['ClassNumber'],
data['GoodsServicesDescriptionText'], data['NationalStatusCategory'],
data['NationalStatusCode'],
data['NationalStatusExternalDescriptionText'],
national_cor_data['EmailAddressText'], data['ApplicationNumberText']))
# create word_mark table
cur.execute('''CREATE TABLE IF NOT EXISTS word_mark
(
id SERIAL PRIMARY KEY,
mark_verbal_element_text VARCHAR(1000),
mark_significant_verbal_element_text VARCHAR(1000),
mark_standard_character_indicator BOOLEAN
); ''')
# insert into word_mark table
if (data['MarkVerbalElementText'] is None):
data['MarkVerbalElementText'] = 'No word_mark available'
cur.execute(
'''INSERT INTO word_mark
(
mark_verbal_element_text,
mark_significant_verbal_element_text,
mark_standard_character_indicator
)
SELECT %s, %s, %s
WHERE NOT EXISTS
(
SELECT mark_verbal_element_text
FROM word_mark
WHERE mark_verbal_element_text = %s
)''', (data['MarkVerbalElementText'],
data['MarkSignificantVerbalElementText'],
data['MarkStandardCharacterIndicator'],
data['MarkVerbalElementText']))
# create image_mark table
cur.execute('''CREATE TABLE IF NOT EXISTS image_mark
(
id SERIAL PRIMARY KEY,
image_file_name VARCHAR(1000),
mark_image_colour_claimed_text TEXT,
mark_image_colour_part_claimed_text TEXT,
image_colour_indicator BOOLEAN
);''')
# insert into image_mark table
if (data['MarkVerbalElementText'] is None):
image_file = 'No image available'
else:
image_file = data['ApplicationNumberText'] + '.png'
cur.execute(
'''INSERT INTO image_mark
(
image_file_name,
mark_image_colour_claimed_text,
mark_image_colour_part_claimed_text,
image_colour_indicator
)
SELECT %s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT image_file_name
FROM image_mark
WHERE image_file_name = %s
)''', (image_file, data['MarkImageColourClaimedText'],
data['MarkImageColourPartClaimedText'], data['ImageColourIndicator'],
image_file))
# create sound_mark table
cur.execute('''CREATE TABLE IF NOT EXISTS sound_mark
(
id SERIAL PRIMARY KEY,
mark_sound VARCHAR(1000)
);''')
# insert into sound_mark table
if (data['MarkSound'] is None):
data['MarkSound'] = 'No sound_mark available'
cur.execute(
'''INSERT INTO sound_mark
(
mark_sound
)
SELECT %s
WHERE NOT EXISTS
(
SELECT mark_sound
FROM sound_mark
WHERE mark_sound = %s
)''', (data['MarkSound'], data['MarkSound']))
# create current_basis table
cur.execute('''CREATE TABLE IF NOT EXISTS current_basis
(
id SERIAL PRIMARY KEY,
basis_foreign_application_indicator BOOLEAN,
basis_foreign_registration_indicator BOOLEAN,
basis_use_indicator BOOLEAN,
basis_intent_to_use_indicator BOOLEAN,
no_basis_indicator BOOLEAN
);''')
# insert into current_basis table
cur.execute(
'''INSERT INTO current_basis
(
basis_foreign_application_indicator,
basis_foreign_registration_indicator,
basis_use_indicator,
basis_intent_to_use_indicator,
no_basis_indicator
)
SELECT %s, %s, %s, %s, %s
FROM trademark
WHERE trademark.serial_number = %s
AND NOT EXISTS
(
SELECT
(
basis_foreign_application_indicator,
basis_foreign_registration_indicator,
basis_use_indicator,
basis_intent_to_use_indicator,
no_basis_indicator
)
FROM current_basis
WHERE basis_foreign_application_indicator = %s
AND basis_use_indicator = %s
AND basis_intent_to_use_indicator = %s
AND basis_foreign_registration_indicator = %s
AND no_basis_indicator = %s
)''', (data['BasisForeignApplicationIndicator'],
data['BasisForeignRegistrationIndicator'], data['BasisUseIndicator'],
data['BasisIntentToUseIndicator'], data['NoBasisIndicator'],
data['ApplicationNumberText'],
data['BasisForeignApplicationIndicator'], data['BasisUseIndicator'],
data['BasisIntentToUseIndicator'],
data['BasisForeignRegistrationIndicator'], data['NoBasisIndicator']))
# create mark event table
cur.execute('''CREATE TABLE IF NOT EXISTS mark_event
(
id SERIAL PRIMARY KEY,
mark_event_category TEXT,
mark_event_code TEXT,
mark_event_description_text TEXT,
mark_event_entry_number INTEGER,
mark_event_additional_text TEXT,
mark_event_date DATE
);''')
# insert into mark event table
for i in range(len(mark_data.values()[0])):
cur.execute(
'''INSERT INTO mark_event
(
mark_event_category,
mark_event_code,
mark_event_description_text,
mark_event_entry_number,
mark_event_additional_text,
mark_event_date
)
SELECT %s, %s, %s, %s, %s, %s
FROM trademark
WHERE trademark.serial_number = %s
AND NOT EXISTS
(
SELECT
(
mark_event_category,
mark_event_code,
mark_event_description_text,
mark_event_entry_number,
mark_event_additional_text
)
FROM mark_event
WHERE mark_event_category LIKE %s
AND mark_event_code LIKE %s
AND mark_event_description_text LIKE %s
AND mark_event_entry_number = %s
AND mark_event_additional_text LIKE %s
)''', (mark_data['MarkEventCategory'][i], mark_data['MarkEventCode'][i],
mark_data['MarkEventDescriptionText'][i],
mark_data['MarkEventEntryNumber'][i],
mark_data['MarkEventAdditionalText'][i],
mark_data['MarkEventDate'][i], data['ApplicationNumberText'],
mark_data['MarkEventCategory'][i], mark_data['MarkEventCode'][i],
mark_data['MarkEventDescriptionText'][i],
mark_data['MarkEventEntryNumber'][i],
mark_data['MarkEventAdditionalText'][i]))
# create goods and services table
cur.execute('''CREATE TABLE IF NOT EXISTS gs_bag
(
id SERIAL PRIMARY KEY,
classification_kind_code VARCHAR(25),
class_number INTEGER,
national_class_number INTEGER
);''')
# insert into goods and services table
for i in range(len(gs_bag_data.values()[0])):
cur.execute(
'''INSERT INTO gs_bag
(
classification_kind_code,
class_number,
national_class_number
)
SELECT %s, %s, %s
FROM trademark
WHERE trademark.serial_number = %s
AND NOT EXISTS
(
SELECT
(
classification_kind_code,
class_number,
national_class_number
)
FROM gs_bag
WHERE classification_kind_code LIKE %s
AND (class_number = %s OR national_class_number = %s)
)''', (gs_bag_data['ClassificationKindCode'][i],
gs_bag_data['ClassNumber'][i],
gs_bag_data['NationalClassNumber'][i],
data['ApplicationNumberText'],
gs_bag_data['ClassificationKindCode'][i],
gs_bag_data['ClassNumber'][i],
gs_bag_data['NationalClassNumber'][i]))
# create applicant table
cur.execute('''CREATE TABLE IF NOT EXISTS applicant
(
id SERIAL PRIMARY KEY,
legal_entity_name TEXT,
national_legal_entity_code TEXT,
incorporation_country_code VARCHAR(25),
incorporation_state VARCHAR(25),
organization_standard_name TEXT,
entity_name TEXT,
entity_name_category TEXT,
address_line_text TEXT,
address_line_text_2 TEXT,
city_name TEXT,
geographic_region_name TEXT,
country_code VARCHAR(25),
postal_code VARCHAR(25)
);''')
# insert into applicant table
for i in range(len(applicant_data.values()[0])):
cur.execute(
'''INSERT INTO applicant
(
legal_entity_name,
national_legal_entity_code,
incorporation_country_code,
incorporation_state,
organization_standard_name,
entity_name,
entity_name_category,
address_line_text,
address_line_text_2,
city_name,
geographic_region_name,
country_code,
postal_code
)
SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT
(
legal_entity_name,
incorporation_country_code,
incorporation_state,
organization_standard_name,
entity_name
)
FROM applicant
WHERE legal_entity_name LIKE %s
AND incorporation_country_code LIKE %s
AND incorporation_state LIKE %s
AND organization_standard_name LIKE %s
AND entity_name LIKE %s
)''', (applicant_data['LegalEntityName'][i],
applicant_data['NationalLegalEntityCode'][i],
applicant_data['IncorporationCountryCode'][i],
applicant_data['IncorporationState'][i],
applicant_data['OrganizationStandardName'][i],
applicant_data['EntityName'][i],
applicant_data['EntityNameCategory'][i],
applicant_data['AddressLineText'][i],
applicant_data['AddressLineText2'][i],
applicant_data['CityName'][i],
applicant_data['GeographicRegionName'][i],
applicant_data['CountryCode'][i], applicant_data['PostalCode'][i],
applicant_data['LegalEntityName'][i],
applicant_data['IncorporationCountryCode'][i],
applicant_data['IncorporationState'][i],
applicant_data['OrganizationStandardName'][i],
applicant_data['EntityName'][i]))
# create national trademark information table
cur.execute('''CREATE TABLE IF NOT EXISTS national_trademark
(
id SERIAL PRIMARY KEY,
register_category TEXT,
amended_principal_register_indicator BOOLEAN,
amended_supplemental_register_indicator BOOLEAN,
mark_current_status_external_description_text TEXT
); ''')
# insert into national trademark information table
cur.execute(
'''INSERT INTO national_trademark
(
register_category,
amended_principal_register_indicator,
amended_supplemental_register_indicator,
mark_current_status_external_description_text
)
SELECT %s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT
(
register_category,
amended_principal_register_indicator,
amended_supplemental_register_indicator,
mark_current_status_external_description_text
)
FROM national_trademark
WHERE register_category LIKE %s
AND amended_principal_register_indicator = %s
AND amended_supplemental_register_indicator = %s
AND mark_current_status_external_description_text LIKE %s
)''', (nat_trade_data['RegisterCategory'],
nat_trade_data['AmendedPrincipalRegisterIndicator'],
nat_trade_data['AmendedSupplementalRegisterIndicator'],
nat_trade_data['MarkCurrentStatusExternalDescriptionText'],
nat_trade_data['RegisterCategory'],
nat_trade_data['AmendedPrincipalRegisterIndicator'],
nat_trade_data['AmendedSupplementalRegisterIndicator'],
nat_trade_data['MarkCurrentStatusExternalDescriptionText']))
# all join tables connecting every table to trademarks table
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# create trademark_word_mark table
# connects trademark to word_mark
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_word_mark
(
trademark_id INTEGER,
word_mark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT word_mark_id_fk
FOREIGN KEY (word_mark_id)
REFERENCES word_mark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_word_mark
cur.execute(
'''INSERT INTO trademark_word_mark
(
trademark_id,
word_mark_id
)
SELECT trademark.id, word_mark.id
FROM trademark
CROSS JOIN word_mark
WHERE serial_number = %s
AND mark_verbal_element_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
word_mark_id
)
FROM trademark_word_mark
WHERE trademark_id = trademark.id
AND word_mark_id = word_mark.id
)''', (data['ApplicationNumberText'], data['MarkVerbalElementText']))
# note, the argument list must always be a LIST (even single tuple)!
# id FROM trademark WHERE serial_number = %s
# create trademark_image_mark table
# connects trademark to image_mark
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_image_mark
(
trademark_id INTEGER,
image_mark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT image_mark_id_fk
FOREIGN KEY (image_mark_id)
REFERENCES image_mark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_image_mark
cur.execute(
'''INSERT INTO trademark_image_mark
(
trademark_id,
image_mark_id
)
SELECT trademark.id, image_mark.id
FROM trademark
CROSS JOIN image_mark
WHERE serial_number = %s
AND image_file_name LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
image_mark_id
)
FROM trademark_image_mark
WHERE trademark_id = trademark.id
AND image_mark_id = image_mark.id
)''', (data['ApplicationNumberText'], image_file))
# create trademark_sound_mark table
# connects trademark to sound_mark
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_sound_mark
(
trademark_id INTEGER,
sound_mark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT sound_mark_id_fk
FOREIGN KEY (sound_mark_id)
REFERENCES sound_mark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_sound_mark
cur.execute(
'''INSERT INTO trademark_sound_mark
(
trademark_id,
sound_mark_id
)
SELECT trademark.id, sound_mark.id
FROM trademark
CROSS JOIN sound_mark
WHERE serial_number = %s
AND mark_sound LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
sound_mark_id
)
FROM trademark_sound_mark
WHERE trademark_id = trademark.id
AND sound_mark_id = sound_mark.id
)''', (data['ApplicationNumberText'], data['MarkSound']))
# create trademark_lawyer table
# connects trademark to lawyer
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_lawyer
(
trademark_id INTEGER,
lawyer_id INTEGER,
application_date DATE,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT lawyer_id_fk
FOREIGN KEY (lawyer_id)
REFERENCES lawyer (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_lawyer
cur.execute(
'''INSERT INTO trademark_lawyer
(
trademark_id,
lawyer_id,
application_date
)
SELECT trademark.id, lawyer.id, %s
FROM trademark
CROSS JOIN lawyer
WHERE serial_number = %s
AND email_address_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
lawyer_id,
application_date
)
FROM trademark_lawyer
WHERE trademark_id = trademark.id
AND lawyer_id = lawyer.id
AND application_date = %s
);''', (get_date(data), data['ApplicationNumberText'],
national_cor_data['EmailAddressText'], get_date(data)))
# create trademark_current_basis table
# connects trademark to current_basis
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_current_basis
(
trademark_id INTEGER,
current_basis_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT current_basis_id_fk
FOREIGN KEY (current_basis_id)
REFERENCES current_basis (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_current_basis
cur.execute(
'''INSERT INTO trademark_current_basis
(
trademark_id,
current_basis_id
)
SELECT trademark.id, current_basis.id
FROM trademark
CROSS JOIN current_basis
WHERE serial_number = %s
AND basis_foreign_application_indicator = %s
AND basis_use_indicator = %s
AND basis_intent_to_use_indicator = %s
AND basis_foreign_registration_indicator = %s
AND no_basis_indicator = %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
current_basis_id
)
FROM trademark_current_basis
WHERE trademark_id = trademark.id
AND current_basis_id = current_basis.id
)''', (data['ApplicationNumberText'],
data['BasisForeignApplicationIndicator'], data['BasisUseIndicator'],
data['BasisIntentToUseIndicator'],
data['BasisForeignRegistrationIndicator'], data['NoBasisIndicator']))
# create trademark_mark_event table
# connects trademark to mark_event
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_mark_event
(
trademark_id INTEGER,
mark_event_id INTEGER,
mark_event_date DATE,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT mark_event_id_fk
FOREIGN KEY (mark_event_id)
REFERENCES mark_event (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_mark_event
for i in range(len(mark_data.values()[0])):
cur.execute(
'''INSERT INTO trademark_mark_event
(
trademark_id,
mark_event_id,
mark_event_date
)
SELECT trademark.id, mark_event.id, %s
FROM trademark
CROSS JOIN mark_event
WHERE serial_number = %s
AND mark_event_category LIKE %s
AND mark_event_code LIKE %s
AND mark_event_description_text LIKE %s
AND mark_event_entry_number = %s
AND mark_event_additional_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
mark_event_id,
mark_event_date
)
FROM trademark_mark_event
WHERE trademark_id = trademark.id
AND mark_event_id = mark_event.id
AND mark_event_date = %s
)''', (mark_data['MarkEventDate'][i], data['ApplicationNumberText'],
mark_data['MarkEventCategory'][i], mark_data['MarkEventCode'][i],
mark_data['MarkEventDescriptionText'][i],
mark_data['MarkEventEntryNumber'][i],
mark_data['MarkEventAdditionalText'][i],
mark_data['MarkEventDate'][i]))
# create trademark_gs_bag table
# connects trademark to gs_bag
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_gs_bag
(
trademark_id INTEGER,
gs_bag_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT gs_bag_id_fk
FOREIGN KEY (gs_bag_id)
REFERENCES gs_bag (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_gs_bag
for i in range(len(gs_bag_data.values()[0])):
cur.execute(
'''INSERT INTO trademark_gs_bag
(
trademark_id,
gs_bag_id
)
SELECT trademark.id, gs_bag.id
FROM trademark
CROSS JOIN gs_bag
WHERE serial_number = %s
AND classification_kind_code LIKE %s
AND (gs_bag.class_number = %s OR national_class_number = %s)
AND NOT EXISTS
(
SELECT
(
trademark_id,
gs_bag_id
)
FROM trademark_gs_bag
WHERE trademark_id = trademark.id
AND gs_bag_id = gs_bag.id
)''', (data['ApplicationNumberText'],
gs_bag_data['ClassificationKindCode'][i],
gs_bag_data['ClassNumber'][i],
gs_bag_data['NationalClassNumber'][i]))
# create trademark_applicant table
# connects trademark to applicant
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_applicant
(
trademark_id INTEGER,
applicant_id INTEGER,
applicant_role TEXT,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT applicant_id_fk
FOREIGN KEY (applicant_id)
REFERENCES applicant (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_applicant
for i in range(len(applicant_data.values()[0])):
cur.execute(
'''INSERT INTO trademark_applicant
(
trademark_id,
applicant_id,
applicant_role
)
SELECT trademark.id, applicant.id, %s
FROM trademark
CROSS JOIN applicant
WHERE serial_number = %s
AND incorporation_country_code LIKE %s
AND incorporation_state LIKE %s
AND organization_standard_name LIKE %s
AND entity_name LIKE %s
AND postal_code LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
applicant_id,
applicant_role
)
FROM trademark_applicant
WHERE trademark_id = trademark.id
AND applicant_id = applicant.id
AND applicant_role = %s
)''', (applicant_data['CommentText'][i], data['ApplicationNumberText'],
applicant_data['IncorporationCountryCode'][i],
applicant_data['IncorporationState'][i],
applicant_data['OrganizationStandardName'][i],
applicant_data['EntityName'][i], applicant_data['PostalCode'][i],
applicant_data['CommentText'][i]))
# create trademark_national_trademark table
# connects trademark to nationa_trademark
cur.execute('''CREATE TABLE IF NOT EXISTS trademark_national_trademark
(
trademark_id INTEGER,
national_trademark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT national_trademark_id_fk
FOREIGN KEY (national_trademark_id)
REFERENCES national_trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);''')
# insert into trademark_national_trademark
cur.execute(
'''INSERT INTO trademark_national_trademark
(
trademark_id,
national_trademark_id
)
SELECT trademark.id, national_trademark.id
FROM trademark
CROSS JOIN national_trademark
WHERE serial_number = %s
AND register_category LIKE %s
AND amended_principal_register_indicator = %s
AND amended_supplemental_register_indicator = %s
AND mark_current_status_external_description_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
national_trademark_id
)
FROM trademark_national_trademark
WHERE trademark_id = trademark.id
AND national_trademark_id = national_trademark.id
)''', (data['ApplicationNumberText'], nat_trade_data['RegisterCategory'],
nat_trade_data['AmendedPrincipalRegisterIndicator'],
nat_trade_data['AmendedSupplementalRegisterIndicator'],
nat_trade_data['MarkCurrentStatusExternalDescriptionText']))
conn.commit() # validate all changes and commit them to postgres
conn.close()
def __init__(self, *args, **kwds):
wx.Frame.__init__(self, *args, **kwds)
self.cleanShutdown = False
self.db = settings.database()
self.SetSizeHints(450, 300)
self.SetMinSize((450,300))
x = self.getPreference('width', 'Int')
y = self.getPreference('height', 'Int')
self.SetSize((x,y)) # read from db along with next line for position
x = self.getPreference('xpos', 'Int')
y = self.getPreference('ypos', 'Int')
self.SetPosition((x,y))
self.Update()
self.names = ["unnamed"]
self.docs = []
self.mostRecentPath = self.getPreference('lastPath', 'Str')
self.tb = bp.ButtonPanel(self, -1, "")
"""
self.tb = self.CreateToolBar ( (wx.TB_HORIZONTAL | wx.NO_BORDER | wx.TB_FLAT ) )
self.tb.SetToolBitmapSize(( 32, 32 ))
"""
blank_img = icons.getblankBitmap()
new_img = icons.getnew01Bitmap()
open_img = icons.getopen01Bitmap()
save_img = icons.getsave01Bitmap()
#close_img = icons.getclose01Bitmap()
self.Bind(wx.EVT_CLOSE, self.CloseThis)
self.vs = wx.BoxSizer(wx.VERTICAL)
self.hs = wx.BoxSizer(wx.HORIZONTAL)
self.SetSizer(self.vs)
self.search = wx.SearchCtrl(self.tb, size=(200,-1), style=wx.TE_PROCESS_ENTER)
self.search.ShowCancelButton(True)
self.search.Bind(wx.EVT_TEXT, self.OnIncrSearch)
self.search.Bind(wx.EVT_TEXT_ENTER, self.OnSearch)
self.search.Bind(wx.EVT_SEARCHCTRL_CANCEL_BTN, self.OnCancel)
self.search.Bind(wx.EVT_KEY_DOWN, self.OnSearchKey)
btn_new = bp.ButtonInfo(self.tb, 1001, new_img, text='New')
btn_open = bp.ButtonInfo(self.tb, 1002, open_img, text='Open')
btn_save = bp.ButtonInfo(self.tb, 1003, save_img, text='Save')
#btn_close = bp.ButtonInfo(self.tb, 1004, close_img)
self.tb.AddButton(btn_new)
self.tb.AddButton(btn_open)
self.tb.AddButton(btn_save)
self.tb.AddControl(self.search)
#self.tb.AddButton(btn_close)
self.Bind(wx.EVT_BUTTON, self.Menu101, btn_new)
self.Bind(wx.EVT_BUTTON, self.Menu102, btn_open)
self.Bind(wx.EVT_BUTTON, self.Menu105, btn_save)
#self.Bind(wx.EVT_BUTTON, self.Menu104, btn_close)
self.vs.Add(self.tb, 0, wx.EXPAND)
self.tb.DoLayout()
"""
self.tb.AddLabelTool(1000, '', blank_img)
self.tb.AddLabelTool(1001, 'New', new_img, longHelp='Begin a new document')
#self.tb.AddLabelTool(1004, 'Close', close_img, longHelp="Close this tab")
self.tb.AddLabelTool(1002, 'Open', open_img, longHelp='Open a new document')
self.tb.AddLabelTool(1003, 'Save', save_img, longHelp='Save this document')
self.tb.AddLabelTool(1000, '', blank_img)
#self.tb.AddLabelTool(1004, 'Run', run_img)
self.tb.AddLabelTool(1000, '', blank_img)
self.tb.AddControl(self.search)
self.tb.Realize()
self.Bind(wx.EVT_TOOL, self.Menu101, id=1001)
self.Bind(wx.EVT_TOOL, self.Menu102, id=1002)
self.Bind(wx.EVT_TOOL, self.Menu105, id=1003)
"""
v = self.getPreference('ViewStatusBar', 'String')
self.CreateStatusBar() # switched off by default as is horizontal scrollbar
self.sb = self.GetStatusBar()
#self.sb.SetStatusWidths([-1, -1])
self.nb = wx.aui.AuiNotebook(self)
self.prefsPanel = wx.Panel(self.nb, 888, (0,10), (10,10))
# may need to create several tabs depending upon last position
self.docs.append(PySTC(self, 0, self.db))
self.nb.AddPage(self.docs[0], "Untitled") # use filename here
self.Bind(wx.aui.EVT_AUINOTEBOOK_PAGE_CLOSE, self.CloseTab, self.nb)
self.m = wx.MenuBar()
self.vs.Add(self.nb, 1, wx.EXPAND)
self.vs.Layout()
self.m1a = wx.Menu()
self.oldDocs = self.db.GetDocs()
for i in range(len(self.oldDocs)):
if i < 10:
self.m1a.Append(1081+i, self.oldDocs[i])
self.m1a.Append(1099, "Clear Menu")
self.Bind(wx.EVT_MENU, self.ClearOldDocsMenu, id=1099)
self.pd = wx.PrintData()
self.pd.SetPaperId(wx.PAPER_A4)
self.pd.SetPrintMode(wx.PRINT_MODE_PRINTER)
self.m1b = wx.Menu()
self.sessions = self.db.GetSessions()
for i in range(len(self.sessions)):
self.m1b.Append(1111+i, self.sessions[i])
m1 = wx.Menu() # file
m1.Append(101, '&New\tCtrl+N', "Begin a new document")
m1.Append(102, "&Open\tCtrl+O", "Open an existing document")
m1.AppendMenu(103, "Open recent", self.m1a) # need a list of files from the db
m1.Append(104, "Close tab\tCtrl+W")
m1.AppendSeparator()
m1.AppendMenu(111, "Open session", self.m1b)
m1.Append(112, "Save session", "Save this as a current session")
m1.Append(113, "Manage sessions", "Edit or delete sessions")
m1.AppendSeparator()
m1.Append(105, "&Save\tCtrl+S", "Save this document now")
m1.Append(106, "Save &As\tShift+Ctrl+S", "Save this document with a different name")
m1.Append(107, "Export as")
m1.Append(108, "Revert to saved")
m1.AppendSeparator()
m1.Append(109, "Print\tCtrl+P")
m1.Append(110, "Page setup\tShift+Ctrl+P")
self.Bind(wx.EVT_MENU, self.Menu101, id=101)
self.Bind(wx.EVT_MENU, self.Menu102, id=102)
self.Bind(wx.EVT_MENU, self.Menu104, id=104)
self.Bind(wx.EVT_MENU, self.Menu105, id=105)
self.Bind(wx.EVT_MENU, self.Menu106, id=106)
self.Bind(wx.EVT_MENU, self.Menu109, id=109)
self.Bind(wx.EVT_MENU, self.Menu110, id=110)
self.Bind(wx.EVT_MENU, self.Menu112, id=112)
self.Bind(wx.EVT_MENU_RANGE, self.SelectOldDocument, id=1081, id2=1091)
self.Bind(wx.EVT_MENU_RANGE, self.LoadSession, id=1111, id2=1120)
m2a = wx.Menu() # find submenu for edit menu
m2a.Append(2081, "Find\tCtrl+F")
m2a.Append(2082, "Find next\tTAB")
m2a.Append(2083, "Find previous\tShift+TAB")
m2b = wx.Menu() # Convert line endings
m2b.Append(2091, "LF (Mac OS X, Unix, Linux)")
m2b.Append(2092, "CR (Older Macintosh)")
m2b.Append(2093, "CR/LF (Windows. DOS)")
m2 = wx.Menu() # edit
m2.Append(201, "Undo\tCtrl+Z")
m2.Append(202, "Redo\tShift+Ctrl+Z")
m2.AppendSeparator()
m2.Append(203, "Cut\tCtrl+X")
m2.Append(204, "Copy\tCtrl+C")
m2.Append(205, "Paste\Ctrl+V")
m2.Append(206, "Delete")
m2.Append(207, "Select all\tCtrl+A")
m2.AppendSeparator()
m2.Append(211, "To lower case\tAlt+Ctrl+Shift+C")
m2.Append(212, "To UPPER case\tCtrl+Shift+C")
m2.AppendMenu(213, "Convert line endings", m2b)
m2.AppendSeparator()
m2.AppendMenu(208, "Find", m2a)
m2.AppendSeparator()
m2.Append(209, "Spelling")
m2.Append(210, "Special characters\tAlt+Ctrl+T")
self.Bind(wx.EVT_MENU, self.Menu201, id=201)
self.Bind(wx.EVT_MENU, self.Menu202, id=202)
self.Bind(wx.EVT_MENU, self.Menu203, id=203)
self.Bind(wx.EVT_MENU, self.Menu204, id=204)
self.Bind(wx.EVT_MENU, self.Menu205, id=205)
self.Bind(wx.EVT_MENU, self.Menu206, id=206)
self.Bind(wx.EVT_MENU, self.Menu207, id=207)
self.Bind(wx.EVT_MENU, self.Menu211, id=211)
self.Bind(wx.EVT_MENU, self.Menu212, id=212)
self.Bind(wx.EVT_MENU, self.Menu2091, id=2091)
self.Bind(wx.EVT_MENU, self.Menu2092, id=2092)
self.Bind(wx.EVT_MENU, self.Menu2093, id=2093)
self.Bind(wx.EVT_MENU, self.Menu2081, id=2081)
m3 = wx.Menu() # format
m3.Append(301, "Text font")
m3.Append(302, "Background colour")
m3.Append(303, "blah")
m6a = wx.Menu() # languages submenu for programming menu
la = len(languages)
for i in range(la):
m6a.Append(1601 + i, languages[i], "", wx.ITEM_RADIO)
m6 = wx.Menu() # programming
m6.Append(601, "Run/Compile\tF5")
m6.Append(602, "Run/compile options\tShift+F5")
m6.AppendMenu(603, "Change language", m6a)
self.Bind(wx.EVT_MENU_RANGE, self.ChangeLanguage, id=1601, id2=1700)
m4 = wx.Menu() # View
m4.Append(401, "Tool bar", "", wx.ITEM_CHECK)
m4.Append(402, "Tabs", "", wx.ITEM_CHECK)
m4.Append(403, "Status bar", "", wx.ITEM_CHECK)
m4.Append(404, "Horizontal scrollbar", "", wx.ITEM_CHECK)
m4.Append(405, "Vertical scrollbar", "", wx.ITEM_CHECK)
m4.Append(406, "Line numbers", "", wx.ITEM_CHECK)
m4.Append(407, "Folding", "", wx.ITEM_CHECK)
doc = self.docs[0]
m4.Check(401, True)
m4.Check(402, True)
sb = self.GetStatusBar().IsShown()
if sb:
m4.Check(403, True)
else:
m4.Check(403, False)
if doc.GetUseHorizontalScrollBar():
m4.Check(404, True)
else:
m4.Check(404, False)
if doc.GetUseVerticalScrollBar():
m4.Check(405, True)
else:
m4.Check(405, False)
if doc.GetMarginWidth(0) == 0:
m4.Check(406, False)
else:
m4.Check(406, True)
if doc.GetMarginWidth(1) == 0:
m4.Check(407, False)
else:
m4.Check(407, True)
self.Bind(wx.EVT_MENU, self.Menu401, id=401)
self.Bind(wx.EVT_MENU, self.Menu403, id=403)
self.Bind(wx.EVT_MENU, self.Menu404, id=404)
self.Bind(wx.EVT_MENU, self.Menu405, id=405)
self.Bind(wx.EVT_MENU, self.Menu406, id=406)
self.Bind(wx.EVT_MENU, self.Menu407, id=407)
m5 = wx.Menu() # bookmarks
m5.Append(501, "Add bookmark for here")
m5.Append(502, "Manage all bookmarks")
m5.Append(503, "bookmark #1")
m7 = wx.Menu()
#item = m7.Append(wx.ID_EXIT, text="&Quit")
#self.Bind(wx.ID_EXIT, self.CloseApp, -1)
item = m7.Append(wx.ID_HELP, "Test & Help", "Help information")
self.Bind(wx.EVT_MENU, self.OnHelp, item)
item = m7.Append(wx.ID_ABOUT, "&About TiDEd", "More information about TiDEd")
self.Bind(wx.EVT_MENU, self.OnAbout, item)
item = m7.Append(wx.ID_PREFERENCES, text="&Preferences\tCtrl+,")
self.Bind(wx.EVT_MENU, self.OnPrefs, item)
#item = m7.Append(wx.ID_QUIT, "&Quit\tCmd+Q")
self.m.Append(m1, "&File")
self.m.Append(m2, "Edit")
self.m.Append(m3, "Format")
self.m.Append(m6, "Programming")
self.m.Append(m4, "View")
self.m.Append(m5, "Bookmarks")
self.m.Append(m7, "&Help")
self.SetMenuBar(self.m)
v = self.getPreference('ViewStatusBar', 'Boolean')
sb = self.GetStatusBar()
if (v == True):
sb.Show(True)
else:
sb.Hide()
v = self.getPreference('ViewToolBar', 'Boolean')
if (v == True):
self.tb.Show(True)
else:
self.tb.Show(False)
def insert(inserted):
# assigning variables from inserted
global data
data = inserted[0]
single_elements = inserted[1]
mark_data = inserted[2]
mark_elements = inserted[3]
gs_bag_data = inserted[4]
gs_elements = inserted[5]
national_cor_data = inserted[6]
national_cor_elements = inserted[7]
record_attorney_data = inserted[8]
record_attorney_elements = inserted[9]
applicant_data = inserted[10]
applicant_elements = inserted[11]
nat_trade_data = inserted[12]
nat_trade_elements = inserted[13]
# connect to the postgres database
conn = psycopg2.connect(database = settings.database(),
user = settings.user(),
password = settings.password()) # Connect database
# format all the datafields
data = proof_data(data, single_elements)
mark_data = proof_data(mark_data, mark_elements)
gs_bag_data = proof_data(gs_bag_data, gs_elements)
national_cor_data = proof_data(national_cor_data, national_cor_elements)
record_attorney_data = proof_data(record_attorney_data,
record_attorney_elements)
applicant_data = proof_data(applicant_data, applicant_elements)
nat_trade_data = proof_data(nat_trade_data, nat_trade_elements)
# create a cursor which can execute postgres commands
cur = conn.cursor() # Initiate the cursor which executes postgres commands
# cur.execute('''drop table if exists ''' + table_out +';') # Remove old table
# make lawyer table
print data['ApplicationNumberText']
# create lawyer table
cur.execute(
'''CREATE TABLE IF NOT EXISTS lawyer
(
id SERIAL PRIMARY KEY,
attorney_name TEXT,
attorney_comment TEXT,
contact_name TEXT,
organization_standard_name TEXT,
address_line_text TEXT,
address_line_text_2 TEXT,
city_name TEXT,
geographic_region_name VARCHAR(25),
country_code VARCHAR(25),
postal_code VARCHAR(25),
email_address_text TEXT,
phone_number VARCHAR(25),
fax_number VARCHAR(25),
website TEXT
);'''
)
# insert into lawyer table
cur.execute(
'''INSERT INTO lawyer
(
attorney_name,
attorney_comment,
contact_name,
organization_standard_name,
address_line_text,
address_line_text_2,
city_name,
geographic_region_name,
country_code,
postal_code,
email_address_text,
phone_number,
fax_number,
website
)
SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT email_address_text
FROM lawyer
WHERE email_address_text LIKE %s
)''',
(
record_attorney_data['PersonFullName'],
record_attorney_data['CommentText'],
national_cor_data['PersonFullName'],
national_cor_data['OrganizationStandardName'],
national_cor_data['AddressLineText'],
national_cor_data['AddressLineText2'],
national_cor_data['CityName'],
national_cor_data['GeographicRegionName'],
national_cor_data['CountryCode'],
national_cor_data['PostalCode'],
national_cor_data['EmailAddressText'],
national_cor_data['PhoneNumber'],
national_cor_data['FaxNumber'],
get_website(national_cor_data['EmailAddressText']),
national_cor_data['EmailAddressText']
)
)
# create trademark table
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark
(
id SERIAL PRIMARY KEY,
serial_number INTEGER,
registration_office_code VARCHAR(5),
ip_office_code VARCHAR(5),
registration_number VARCHAR(20),
application_date DATE,
registration_date DATE,
filing_place VARCHAR(5),
mark_current_status_date DATE,
mark_category VARCHAR(25),
mark_feature_category VARCHAR(100),
first_used_date DATE,
blank_month_1 BOOLEAN,
blank_day_1 BOOLEAN,
first_used_commerce_date DATE,
blank_month_2 BOOLEAN,
blank_day_2 BOOLEAN,
publication_identifier VARCHAR(100),
publication_date DATE,
class_number VARCHAR(5),
goods_services_description_text TEXT,
national_status_category TEXT,
national_status_code TEXT,
national_status_external_description_text TEXT
);'''
)
# insert into trademark table
cur.execute(
'''INSERT INTO trademark
(
serial_number,
registration_office_code,
IP_office_code,
registration_number,
application_date,
registration_date,
filing_place,
mark_current_status_date,
mark_category,
mark_feature_category,
first_used_date,
blank_month_1,
blank_day_1,
first_used_commerce_date,
blank_month_2,
blank_day_2,
publication_identifier,
publication_date,
class_number,
goods_services_description_text,
national_status_category,
national_status_code,
national_status_external_description_text
)
SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s
FROM lawyer
WHERE lawyer.email_address_text LIKE %s
AND NOT EXISTS
(
SELECT serial_number
FROM trademark
WHERE serial_number = %s
)''',
(
data['ApplicationNumberText'],
data['RegistrationOfficeCode'],
data['IPOfficeCode'],
data['RegistrationNumber'],
get_date(data),
data['RegistrationDate'],
data['FilingPlace'],
data['MarkCurrentStatusDate'],
data['MarkCategory'],
data['MarkFeatureCategory'],
data['FirstUsedDate'],
data['BlankMonth1'],
data['BlankDay1'],
data['FirstUsedCommerceDate'],
data['BlankMonth2'],
data['BlankDay2'],
data['PublicationIdentifier'],
data['PublicationDate'],
data['ClassNumber'],
data['GoodsServicesDescriptionText'],
data['NationalStatusCategory'],
data['NationalStatusCode'],
data['NationalStatusExternalDescriptionText'],
national_cor_data['EmailAddressText'],
data['ApplicationNumberText']
)
)
# create word_mark table
cur.execute(
'''CREATE TABLE IF NOT EXISTS word_mark
(
id SERIAL PRIMARY KEY,
mark_verbal_element_text VARCHAR(1000),
mark_significant_verbal_element_text VARCHAR(1000),
mark_standard_character_indicator BOOLEAN
); '''
)
# insert into word_mark table
if (data['MarkVerbalElementText'] is None):
data['MarkVerbalElementText'] = 'No word_mark available'
cur.execute(
'''INSERT INTO word_mark
(
mark_verbal_element_text,
mark_significant_verbal_element_text,
mark_standard_character_indicator
)
SELECT %s, %s, %s
WHERE NOT EXISTS
(
SELECT mark_verbal_element_text
FROM word_mark
WHERE mark_verbal_element_text = %s
)''',
(
data['MarkVerbalElementText'],
data['MarkSignificantVerbalElementText'],
data['MarkStandardCharacterIndicator'],
data['MarkVerbalElementText']
)
)
# create image_mark table
cur.execute(
'''CREATE TABLE IF NOT EXISTS image_mark
(
id SERIAL PRIMARY KEY,
image_file_name VARCHAR(1000),
mark_image_colour_claimed_text TEXT,
mark_image_colour_part_claimed_text TEXT,
image_colour_indicator BOOLEAN
);'''
)
# insert into image_mark table
if (data['MarkVerbalElementText'] is None):
image_file = 'No image available'
else:
image_file = data['ApplicationNumberText'] + '.png'
cur.execute(
'''INSERT INTO image_mark
(
image_file_name,
mark_image_colour_claimed_text,
mark_image_colour_part_claimed_text,
image_colour_indicator
)
SELECT %s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT image_file_name
FROM image_mark
WHERE image_file_name = %s
)''',
(
image_file,
data['MarkImageColourClaimedText'],
data['MarkImageColourPartClaimedText'],
data['ImageColourIndicator'],
image_file
)
)
# create sound_mark table
cur.execute(
'''CREATE TABLE IF NOT EXISTS sound_mark
(
id SERIAL PRIMARY KEY,
mark_sound VARCHAR(1000)
);'''
)
# insert into sound_mark table
if (data['MarkSound'] is None):
data['MarkSound'] = 'No sound_mark available'
cur.execute(
'''INSERT INTO sound_mark
(
mark_sound
)
SELECT %s
WHERE NOT EXISTS
(
SELECT mark_sound
FROM sound_mark
WHERE mark_sound = %s
)''',
(
data['MarkSound'],
data['MarkSound']
)
)
# create current_basis table
cur.execute(
'''CREATE TABLE IF NOT EXISTS current_basis
(
id SERIAL PRIMARY KEY,
basis_foreign_application_indicator BOOLEAN,
basis_foreign_registration_indicator BOOLEAN,
basis_use_indicator BOOLEAN,
basis_intent_to_use_indicator BOOLEAN,
no_basis_indicator BOOLEAN
);'''
)
# insert into current_basis table
cur.execute(
'''INSERT INTO current_basis
(
basis_foreign_application_indicator,
basis_foreign_registration_indicator,
basis_use_indicator,
basis_intent_to_use_indicator,
no_basis_indicator
)
SELECT %s, %s, %s, %s, %s
FROM trademark
WHERE trademark.serial_number = %s
AND NOT EXISTS
(
SELECT
(
basis_foreign_application_indicator,
basis_foreign_registration_indicator,
basis_use_indicator,
basis_intent_to_use_indicator,
no_basis_indicator
)
FROM current_basis
WHERE basis_foreign_application_indicator = %s
AND basis_use_indicator = %s
AND basis_intent_to_use_indicator = %s
AND basis_foreign_registration_indicator = %s
AND no_basis_indicator = %s
)''',
(
data['BasisForeignApplicationIndicator'],
data['BasisForeignRegistrationIndicator'],
data['BasisUseIndicator'],
data['BasisIntentToUseIndicator'],
data['NoBasisIndicator'],
data['ApplicationNumberText'],
data['BasisForeignApplicationIndicator'],
data['BasisUseIndicator'],
data['BasisIntentToUseIndicator'],
data['BasisForeignRegistrationIndicator'],
data['NoBasisIndicator']
)
)
# create mark event table
cur.execute(
'''CREATE TABLE IF NOT EXISTS mark_event
(
id SERIAL PRIMARY KEY,
mark_event_category TEXT,
mark_event_code TEXT,
mark_event_description_text TEXT,
mark_event_entry_number INTEGER,
mark_event_additional_text TEXT,
mark_event_date DATE
);'''
)
# insert into mark event table
for i in range(len(mark_data.values()[0])):
cur.execute(
'''INSERT INTO mark_event
(
mark_event_category,
mark_event_code,
mark_event_description_text,
mark_event_entry_number,
mark_event_additional_text,
mark_event_date
)
SELECT %s, %s, %s, %s, %s, %s
FROM trademark
WHERE trademark.serial_number = %s
AND NOT EXISTS
(
SELECT
(
mark_event_category,
mark_event_code,
mark_event_description_text,
mark_event_entry_number,
mark_event_additional_text
)
FROM mark_event
WHERE mark_event_category LIKE %s
AND mark_event_code LIKE %s
AND mark_event_description_text LIKE %s
AND mark_event_entry_number = %s
AND mark_event_additional_text LIKE %s
)''',
(
mark_data['MarkEventCategory'][i],
mark_data['MarkEventCode'][i],
mark_data['MarkEventDescriptionText'][i],
mark_data['MarkEventEntryNumber'][i],
mark_data['MarkEventAdditionalText'][i],
mark_data['MarkEventDate'][i],
data['ApplicationNumberText'],
mark_data['MarkEventCategory'][i],
mark_data['MarkEventCode'][i],
mark_data['MarkEventDescriptionText'][i],
mark_data['MarkEventEntryNumber'][i],
mark_data['MarkEventAdditionalText'][i]
)
)
# create goods and services table
cur.execute(
'''CREATE TABLE IF NOT EXISTS gs_bag
(
id SERIAL PRIMARY KEY,
classification_kind_code VARCHAR(25),
class_number INTEGER,
national_class_number INTEGER
);'''
)
# insert into goods and services table
for i in range(len(gs_bag_data.values()[0])):
cur.execute(
'''INSERT INTO gs_bag
(
classification_kind_code,
class_number,
national_class_number
)
SELECT %s, %s, %s
FROM trademark
WHERE trademark.serial_number = %s
AND NOT EXISTS
(
SELECT
(
classification_kind_code,
class_number,
national_class_number
)
FROM gs_bag
WHERE classification_kind_code LIKE %s
AND (class_number = %s OR national_class_number = %s)
)''',
(
gs_bag_data['ClassificationKindCode'][i],
gs_bag_data['ClassNumber'][i],
gs_bag_data['NationalClassNumber'][i],
data['ApplicationNumberText'],
gs_bag_data['ClassificationKindCode'][i],
gs_bag_data['ClassNumber'][i],
gs_bag_data['NationalClassNumber'][i]
)
)
# create applicant table
cur.execute(
'''CREATE TABLE IF NOT EXISTS applicant
(
id SERIAL PRIMARY KEY,
legal_entity_name TEXT,
national_legal_entity_code TEXT,
incorporation_country_code VARCHAR(25),
incorporation_state VARCHAR(25),
organization_standard_name TEXT,
entity_name TEXT,
entity_name_category TEXT,
address_line_text TEXT,
address_line_text_2 TEXT,
city_name TEXT,
geographic_region_name TEXT,
country_code VARCHAR(25),
postal_code VARCHAR(25)
);'''
)
# insert into applicant table
for i in range(len(applicant_data.values()[0])):
cur.execute(
'''INSERT INTO applicant
(
legal_entity_name,
national_legal_entity_code,
incorporation_country_code,
incorporation_state,
organization_standard_name,
entity_name,
entity_name_category,
address_line_text,
address_line_text_2,
city_name,
geographic_region_name,
country_code,
postal_code
)
SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT
(
legal_entity_name,
incorporation_country_code,
incorporation_state,
organization_standard_name,
entity_name
)
FROM applicant
WHERE legal_entity_name LIKE %s
AND incorporation_country_code LIKE %s
AND incorporation_state LIKE %s
AND organization_standard_name LIKE %s
AND entity_name LIKE %s
)''',
(
applicant_data['LegalEntityName'][i],
applicant_data['NationalLegalEntityCode'][i],
applicant_data['IncorporationCountryCode'][i],
applicant_data['IncorporationState'][i],
applicant_data['OrganizationStandardName'][i],
applicant_data['EntityName'][i],
applicant_data['EntityNameCategory'][i],
applicant_data['AddressLineText'][i],
applicant_data['AddressLineText2'][i],
applicant_data['CityName'][i],
applicant_data['GeographicRegionName'][i],
applicant_data['CountryCode'][i],
applicant_data['PostalCode'][i],
applicant_data['LegalEntityName'][i],
applicant_data['IncorporationCountryCode'][i],
applicant_data['IncorporationState'][i],
applicant_data['OrganizationStandardName'][i],
applicant_data['EntityName'][i]
)
)
# create national trademark information table
cur.execute(
'''CREATE TABLE IF NOT EXISTS national_trademark
(
id SERIAL PRIMARY KEY,
register_category TEXT,
amended_principal_register_indicator BOOLEAN,
amended_supplemental_register_indicator BOOLEAN,
mark_current_status_external_description_text TEXT
); '''
)
# insert into national trademark information table
cur.execute(
'''INSERT INTO national_trademark
(
register_category,
amended_principal_register_indicator,
amended_supplemental_register_indicator,
mark_current_status_external_description_text
)
SELECT %s, %s, %s, %s
WHERE NOT EXISTS
(
SELECT
(
register_category,
amended_principal_register_indicator,
amended_supplemental_register_indicator,
mark_current_status_external_description_text
)
FROM national_trademark
WHERE register_category LIKE %s
AND amended_principal_register_indicator = %s
AND amended_supplemental_register_indicator = %s
AND mark_current_status_external_description_text LIKE %s
)''',
(
nat_trade_data['RegisterCategory'],
nat_trade_data['AmendedPrincipalRegisterIndicator'],
nat_trade_data['AmendedSupplementalRegisterIndicator'],
nat_trade_data['MarkCurrentStatusExternalDescriptionText'],
nat_trade_data['RegisterCategory'],
nat_trade_data['AmendedPrincipalRegisterIndicator'],
nat_trade_data['AmendedSupplementalRegisterIndicator'],
nat_trade_data['MarkCurrentStatusExternalDescriptionText']
)
)
# all join tables connecting every table to trademarks table
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# TABLE CONNECTIONS
# create trademark_word_mark table
# connects trademark to word_mark
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_word_mark
(
trademark_id INTEGER,
word_mark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT word_mark_id_fk
FOREIGN KEY (word_mark_id)
REFERENCES word_mark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_word_mark
cur.execute(
'''INSERT INTO trademark_word_mark
(
trademark_id,
word_mark_id
)
SELECT trademark.id, word_mark.id
FROM trademark
CROSS JOIN word_mark
WHERE serial_number = %s
AND mark_verbal_element_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
word_mark_id
)
FROM trademark_word_mark
WHERE trademark_id = trademark.id
AND word_mark_id = word_mark.id
)''',
(
data['ApplicationNumberText'],
data['MarkVerbalElementText']
)
)
# note, the argument list must always be a LIST (even single tuple)!
# id FROM trademark WHERE serial_number = %s
# create trademark_image_mark table
# connects trademark to image_mark
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_image_mark
(
trademark_id INTEGER,
image_mark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT image_mark_id_fk
FOREIGN KEY (image_mark_id)
REFERENCES image_mark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_image_mark
cur.execute(
'''INSERT INTO trademark_image_mark
(
trademark_id,
image_mark_id
)
SELECT trademark.id, image_mark.id
FROM trademark
CROSS JOIN image_mark
WHERE serial_number = %s
AND image_file_name LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
image_mark_id
)
FROM trademark_image_mark
WHERE trademark_id = trademark.id
AND image_mark_id = image_mark.id
)''',
(
data['ApplicationNumberText'],
image_file
)
)
# create trademark_sound_mark table
# connects trademark to sound_mark
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_sound_mark
(
trademark_id INTEGER,
sound_mark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT sound_mark_id_fk
FOREIGN KEY (sound_mark_id)
REFERENCES sound_mark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_sound_mark
cur.execute(
'''INSERT INTO trademark_sound_mark
(
trademark_id,
sound_mark_id
)
SELECT trademark.id, sound_mark.id
FROM trademark
CROSS JOIN sound_mark
WHERE serial_number = %s
AND mark_sound LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
sound_mark_id
)
FROM trademark_sound_mark
WHERE trademark_id = trademark.id
AND sound_mark_id = sound_mark.id
)''',
(
data['ApplicationNumberText'],
data['MarkSound']
)
)
# create trademark_lawyer table
# connects trademark to lawyer
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_lawyer
(
trademark_id INTEGER,
lawyer_id INTEGER,
application_date DATE,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT lawyer_id_fk
FOREIGN KEY (lawyer_id)
REFERENCES lawyer (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_lawyer
cur.execute(
'''INSERT INTO trademark_lawyer
(
trademark_id,
lawyer_id,
application_date
)
SELECT trademark.id, lawyer.id, %s
FROM trademark
CROSS JOIN lawyer
WHERE serial_number = %s
AND email_address_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
lawyer_id,
application_date
)
FROM trademark_lawyer
WHERE trademark_id = trademark.id
AND lawyer_id = lawyer.id
AND application_date = %s
);''',
(
get_date(data),
data['ApplicationNumberText'],
national_cor_data['EmailAddressText'],
get_date(data)
)
)
# create trademark_current_basis table
# connects trademark to current_basis
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_current_basis
(
trademark_id INTEGER,
current_basis_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT current_basis_id_fk
FOREIGN KEY (current_basis_id)
REFERENCES current_basis (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_current_basis
cur.execute(
'''INSERT INTO trademark_current_basis
(
trademark_id,
current_basis_id
)
SELECT trademark.id, current_basis.id
FROM trademark
CROSS JOIN current_basis
WHERE serial_number = %s
AND basis_foreign_application_indicator = %s
AND basis_use_indicator = %s
AND basis_intent_to_use_indicator = %s
AND basis_foreign_registration_indicator = %s
AND no_basis_indicator = %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
current_basis_id
)
FROM trademark_current_basis
WHERE trademark_id = trademark.id
AND current_basis_id = current_basis.id
)''',
(
data['ApplicationNumberText'],
data['BasisForeignApplicationIndicator'],
data['BasisUseIndicator'],
data['BasisIntentToUseIndicator'],
data['BasisForeignRegistrationIndicator'],
data['NoBasisIndicator']
)
)
# create trademark_mark_event table
# connects trademark to mark_event
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_mark_event
(
trademark_id INTEGER,
mark_event_id INTEGER,
mark_event_date DATE,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT mark_event_id_fk
FOREIGN KEY (mark_event_id)
REFERENCES mark_event (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_mark_event
for i in range(len(mark_data.values()[0])):
cur.execute(
'''INSERT INTO trademark_mark_event
(
trademark_id,
mark_event_id,
mark_event_date
)
SELECT trademark.id, mark_event.id, %s
FROM trademark
CROSS JOIN mark_event
WHERE serial_number = %s
AND mark_event_category LIKE %s
AND mark_event_code LIKE %s
AND mark_event_description_text LIKE %s
AND mark_event_entry_number = %s
AND mark_event_additional_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
mark_event_id,
mark_event_date
)
FROM trademark_mark_event
WHERE trademark_id = trademark.id
AND mark_event_id = mark_event.id
AND mark_event_date = %s
)''',
(
mark_data['MarkEventDate'][i],
data['ApplicationNumberText'],
mark_data['MarkEventCategory'][i],
mark_data['MarkEventCode'][i],
mark_data['MarkEventDescriptionText'][i],
mark_data['MarkEventEntryNumber'][i],
mark_data['MarkEventAdditionalText'][i],
mark_data['MarkEventDate'][i]
)
)
# create trademark_gs_bag table
# connects trademark to gs_bag
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_gs_bag
(
trademark_id INTEGER,
gs_bag_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT gs_bag_id_fk
FOREIGN KEY (gs_bag_id)
REFERENCES gs_bag (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_gs_bag
for i in range(len(gs_bag_data.values()[0])):
cur.execute(
'''INSERT INTO trademark_gs_bag
(
trademark_id,
gs_bag_id
)
SELECT trademark.id, gs_bag.id
FROM trademark
CROSS JOIN gs_bag
WHERE serial_number = %s
AND classification_kind_code LIKE %s
AND (gs_bag.class_number = %s OR national_class_number = %s)
AND NOT EXISTS
(
SELECT
(
trademark_id,
gs_bag_id
)
FROM trademark_gs_bag
WHERE trademark_id = trademark.id
AND gs_bag_id = gs_bag.id
)''',
(
data['ApplicationNumberText'],
gs_bag_data['ClassificationKindCode'][i],
gs_bag_data['ClassNumber'][i],
gs_bag_data['NationalClassNumber'][i]
)
)
# create trademark_applicant table
# connects trademark to applicant
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_applicant
(
trademark_id INTEGER,
applicant_id INTEGER,
applicant_role TEXT,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT applicant_id_fk
FOREIGN KEY (applicant_id)
REFERENCES applicant (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_applicant
for i in range(len(applicant_data.values()[0])):
cur.execute(
'''INSERT INTO trademark_applicant
(
trademark_id,
applicant_id,
applicant_role
)
SELECT trademark.id, applicant.id, %s
FROM trademark
CROSS JOIN applicant
WHERE serial_number = %s
AND incorporation_country_code LIKE %s
AND incorporation_state LIKE %s
AND organization_standard_name LIKE %s
AND entity_name LIKE %s
AND postal_code LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
applicant_id,
applicant_role
)
FROM trademark_applicant
WHERE trademark_id = trademark.id
AND applicant_id = applicant.id
AND applicant_role = %s
)''',
(
applicant_data['CommentText'][i],
data['ApplicationNumberText'],
applicant_data['IncorporationCountryCode'][i],
applicant_data['IncorporationState'][i],
applicant_data['OrganizationStandardName'][i],
applicant_data['EntityName'][i],
applicant_data['PostalCode'][i],
applicant_data['CommentText'][i]
)
)
# create trademark_national_trademark table
# connects trademark to nationa_trademark
cur.execute(
'''CREATE TABLE IF NOT EXISTS trademark_national_trademark
(
trademark_id INTEGER,
national_trademark_id INTEGER,
CONSTRAINT trademark_id_fk
FOREIGN KEY (trademark_id)
REFERENCES trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT national_trademark_id_fk
FOREIGN KEY (national_trademark_id)
REFERENCES national_trademark (id)
MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
);'''
)
# insert into trademark_national_trademark
cur.execute(
'''INSERT INTO trademark_national_trademark
(
trademark_id,
national_trademark_id
)
SELECT trademark.id, national_trademark.id
FROM trademark
CROSS JOIN national_trademark
WHERE serial_number = %s
AND register_category LIKE %s
AND amended_principal_register_indicator = %s
AND amended_supplemental_register_indicator = %s
AND mark_current_status_external_description_text LIKE %s
AND NOT EXISTS
(
SELECT
(
trademark_id,
national_trademark_id
)
FROM trademark_national_trademark
WHERE trademark_id = trademark.id
AND national_trademark_id = national_trademark.id
)''',
(
data['ApplicationNumberText'],
nat_trade_data['RegisterCategory'],
nat_trade_data['AmendedPrincipalRegisterIndicator'],
nat_trade_data['AmendedSupplementalRegisterIndicator'],
nat_trade_data['MarkCurrentStatusExternalDescriptionText']
)
)
conn.commit() # validate all changes and commit them to postgres
conn.close()