def _set_df(self):
sql_code = 'ATTACH DATABASE ? AS ?'
args = (str(constants.DB_PATHS[constants.STARS_DB]), constants.STARS_DB)
con = sql.connect(
constants.DB_PATHS[constants.STATEWIDE_DATASETS_DB], uri=True,
timeout=constants.DB_TIMEOUT
)
db_attached = utilities.execute_sql(
sql_code=sql_code, args=args, open_con=con, dontfetch=True
)
if db_attached:
results = utilities.execute_sql(
sql_code=self.query, open_con=con, getcursor=True
)
if results:
column_names = [i[0] for i in results.description]
data = results.fetchall()
self.df = pd.DataFrame(data, columns=column_names)
con.close()
def _insert_values(
self, db_name, column_names, values):
'''
Args:
db_name: A string that represents the name of the
database that contains the table where the values
will be inserted.
column_names: A list of strings with each of the column
names for the table where the values will be inserted.
values: A list with the values that will be inserted into
the table.
'''
column_string = self._get_column_string(column_names)
place_holders = self._get_value_place_holders(len(column_names))
sql_code = (
f'INSERT INTO {self.table_name}({column_string}) '
f'VALUES({place_holders})')
utilities.execute_sql(
sql_code=sql_code, args=values, db_name=db_name, dml=True,
many=True
)
def _attach_values_db(self):
sql_code = 'ATTACH DATABASE ? AS ?'
args = (str(constants.DB_PATHS[self.values_db]), self.values_db)
utilities.execute_sql(sql_code=sql_code,
args=args,
open_con=self.con,
dontfetch=True)
def _set_tables(self):
if not self._business_table_exists():
self._create_table(constants.BUSINESSES_DB, self.business_columns)
# drops the values table if it exists
sql_code = f'DROP TABLE IF EXISTS {self.table_name}'
utilities.execute_sql(
sql_code=sql_code, db_name=self.values_db, dml=True
)
self._create_table(
self.values_db, self.quarterly_columns, quarterly=True)
def _create_table(
self, db_name, column_names, quarterly=False):
'''
Creates a table in the given database with the column names in
the list provided and if the data is quarterly then the column
names will have different types. If it is quarterly then the id
column will be of the text type, but the rest of the columns will
be integers. If the data is not quarterly then the columns will all
be of the text type. The table will be created in the database of
the name that is provided and with the names the list of column
names. The only thing that will change in the definition of the table
if the data is quarterly is the data type of the id column in the
definition of the table when it is created and the other parts of the
table will be the same.
Args:
db_name: A string representing the name of the
database where the new table will be created in.
column_names: A list of strings which are the column names
that will be used for the new table.
quarterly: A boolean that flags whether or not the data will
go into one of the quarterly databases or not.
'''
data_type = 'INTEGER' if quarterly else 'TEXT'
column_string = self._get_column_string(column_names, data_type)
if not quarterly:
# sets data type for business code id column
column = constants.BUSINESS_CODE_ID_COLUMN_NAME.upper()
column_string = column_string.replace(
f'{column} TEXT', f'{column} INTEGER'
)
# sets id as primary key
column_string = column_string.replace(
f'{constants.ID_COLUMN_NAME} {data_type}',
f'{constants.ID_COLUMN_NAME} TEXT PRIMARY KEY'
)
schema = f'CREATE TABLE {self.table_name} ({column_string})'
if not quarterly:
schema = self._insert_business_code_foreign_key(schema)
utilities.execute_sql(
sql_code=schema, db_name=db_name, dml=True
)
def _set_selected_report_attributes(self):
condition = (
f'={self.selected_report_ids[0]}'
if len(self.selected_report_ids) == 1
else f'IN {self.selected_report_ids}'
)
query = (
f'SELECT Id, Name, Path, IsDuplex '
f'FROM {constants.PACKET_REPORTS_TABLE} '
f'WHERE Id {condition}'
)
results = utilities.execute_sql(
sql_code=query, db_name=constants.STARS_DB, fetchall=True
)
if results:
# adds the attributes to the list of selected report attributes
# according to the order of the selected report ids
for attributes in results:
report_id = attributes[0]
if self.selected_report_attributes:
index = self._get_selected_report_attributes_index(report_id)
if index < len(self.selected_report_attributes):
self.selected_report_attributes.insert(index, attributes)
else:
self.selected_report_attributes.append(attributes)
else:
self.selected_report_attributes.append(attributes)
def _get_geo_ranges(self, range_ids):
args = [
self.jurisdiction.id,
]
# if there is more than one range id or a list or range ids
if isinstance(range_ids, list):
# the range id will be checked against all range ids
range_id_condition = f'IN {tuple(range_ids)}'
else:
# only the one range id has to be checked
range_id_condition = '=?'
# adds the range id to the list or arguments that will be passed
# along with the sql query
args.append(range_ids)
query = f'''
SELECT street, street_type, dir, pdir, side, low, high
FROM geo_ranges
WHERE jurisdiction_id=? AND range_id {range_id_condition}
'''
results = utilities.execute_sql(
sql_code=query,
args=args,
fetchall=True,
db_name=constants.STATEWIDE_DATASETS_DB)
return results
def _set_business_code_totals_countywide(self):
query = f'''
SELECT {constants.BUSINESS_CODE_ID_COLUMN_NAME}, {self.bmy_periods_string_county}
FROM {constants.BUSINESS_CODE_TOTALS_TABLE} t,
{constants.JURISDICTIONS_TABLE} j
WHERE t.{constants.TAC_COLUMN_NAME}=j.{constants.TAC_COLUMN_NAME}
AND j.{constants.COUNTY_ID_COLUMN_NAME}=?
GROUP BY {constants.BUSINESS_CODE_ID_COLUMN_NAME}
'''
args = (self.jurisdiction.county_id, )
results = utilities.execute_sql(
sql_code=query,
args=args,
db_name=constants.STATEWIDE_DATASETS_DB,
fetchall=True,
attach_db='starsdb')
if results:
for business_code_id, amount in results:
self.business_code_totals[business_code_id] = amount
def _set_jurisdiction_ebi(self):
if self.is_countywide:
query = f'''
SELECT SUM({EBI_COLUMN_NAME})
FROM {EBI_COLUMN_NAME} e,
{constants.JURISDICTIONS_TABLE} j
WHERE e.Id=j.{constants.TAC_COLUMN_NAME}
AND j.HasData=1
AND j.{constants.COUNTY_ID_COLUMN_NAME}=?
'''
args = (self.jurisdiction.county_id, )
attach_db = 'starsdb'
else:
query = f'''
SELECT {EBI_COLUMN_NAME}
FROM {EBI_COLUMN_NAME}
WHERE Id=?
'''
args = (self.jurisdiction.tac, )
attach_db = ''
results = utilities.execute_sql(
sql_code=query,
args=args,
attach_db=attach_db,
db_name=constants.STATEWIDE_DATASETS_DB)
if results and results[0] is not None:
self.jurisdiction_ebi = results[0] // 1000
def _set_segments(self):
'''
Stores the segment ids and their names.
'''
self.segments = {}
query = f'''
SELECT {constants.CATEGORY_ID_COLUMN_NAME},
Id, name
FROM {constants.SEGMENTS_TABLE}
'''
results = utilities.execute_sql(sql_code=query,
db_name=constants.STARS_DB,
fetchall=True)
if results:
# sorts by the segment name
results = sorted(results, key=itemgetter(2))
for category_id, segment_id, name in results:
if category_id not in self.segments:
self.segments[category_id] = {}
self.segments[category_id][segment_id] = name
def _set_business_codes(self):
'''
Stores the business codes and their ids in a dictionary with the
id as the key and the name as the value inside a nested dictionary
with the sgment_id as the key.
'''
self.business_codes = {}
query = f'''
SELECT {constants.SEGMENT_ID_COLUMN_NAME},
Id, name
FROM {constants.BUSINESS_CODES_TABLE}
'''
results = utilities.execute_sql(sql_code=query,
db_name=constants.STARS_DB,
fetchall=True)
if results:
# sorts by the business code name
results = sorted(results, key=itemgetter(2))
for segment_id, business_code_id, name in results:
if name != 'BLANK':
if segment_id not in self.business_codes:
self.business_codes[segment_id] = {}
self.business_codes[segment_id][business_code_id] = name
def _query_tables(self):
table_count = len(self.lookup_tables)
progress = 1
for i, table_name in enumerate(self.lookup_tables, start=1):
if not self.abort:
self.progress.update_progress(
progress,
f'Querying table {i} of {table_count}: {table_name}'
' Results for "{}" = {:,}'.format(
self.lookup_value, self.result_count))
query = self.query.replace('table_name', table_name)
results = utilities.execute_sql(sql_code=query,
open_con=self.con,
fetchall=True,
show_error=False)
if results:
self.results.extend(results)
self.result_count += len(results)
progress += 100 / table_count
def _set_county_pool_amounts(self):
query = f'''
SELECT SUM(county_pool_amount)
FROM {constants.CDTFA_ALLOCATION_TABLE} c,
{constants.JURISDICTIONS_TABLE} j
WHERE c.{constants.TAC_COLUMN_NAME} = j.{constants.TAC_COLUMN_NAME}
AND j.{constants.COUNTY_ID_COLUMN_NAME} = ?
AND period IN {tuple(self.period_headers)}
GROUP BY period
ORDER BY period
'''
amounts = utilities.execute_sql(
sql_code=query,
args=(self.jurisdiction.county_id, ),
db_name=constants.STATEWIDE_DATASETS_DB,
attach_db=constants.STARS_DB,
fetchall=True)
if amounts:
self.county_pool_amounts = [int(x[0]) for x in amounts]
def _set_categories(self):
query = f'''
SELECT Id, name
FROM {constants.CATEGORIES_TABLE}
'''
self.categories = utilities.execute_sql(sql_code=query,
db_name=constants.STARS_DB,
fetchall=True)
def _set_businesses(self):
self.businesses = []
results = utilities.execute_sql(sql_code=self.businesses_query,
db_name=constants.BUSINESSES_DB,
fetchall=True)
if results:
self.businesses = results
def _set_category_totals(self):
args = ((self.jurisdiction.county_id, ) if self.is_county_wide else
(self.jurisdiction.tac, ))
self.category_totals = utilities.execute_sql(
sql_code=self.sales_tax_query,
args=args,
db_name=constants.STATEWIDE_DATASETS_DB,
fetchall=True,
attach_db=constants.STARS_DB)
def _insert_new_rows(self):
column_names = list(self.df)
column_string = ','.join(column_names)
value_place_holders = ','.join('?' for _ in range(len(column_names)))
query = f'''
INSERT INTO {self.table_name}({column_string})
VALUES({value_place_holders})
'''
values = self.df.values.tolist()
utilities.execute_sql(sql_code=query,
args=values,
db_name=self.db_name,
dml=True,
many=True)
def _set_dataframes(self):
for name in DATA_NAMES:
data = utilities.execute_sql(
sql_code=self.queries[name],
args=self.args[name],
db_name=constants.STATEWIDE_DATASETS_DB,
fetchall=True,
attach_db=constants.STARS_DB)
self.dfs[name] = pd.DataFrame(
data,
columns=[constants.CATEGORY_COLUMN_NAME] + self.period_headers)
def _delete_current_rows(self):
query = f'''
DELETE FROM {self.table_name}
WHERE {constants.TAC_COLUMN_NAME}=?
'''
deleted = utilities.execute_sql(sql_code=query,
args=(self.jurisdiction.tac, ),
db_name=self.db_name,
dml=True)
return deleted
def on_execute_click(self):
permit_number = self._get_permit_number()
if permit_number:
business_code_id = self.gui.business_code_id.get()
if business_code_id:
business_code_id = business_code_id.split(':')[0].strip()
business_name = self._get_business_name()
values = [business_name, business_code_id]
# 1 for insert and 2 for alter
execute_type = self.gui.execute_option.get()
if execute_type == 1:
values.insert(0, permit_number)
sql_code = ('INSERT INTO '
'permits(id, business, business_code_id) '
'VALUES(?, ?, ?)')
message = 'inserted'
else:
values.append(permit_number)
sql_code = ('UPDATE permits '
'SET business=?, business_code_id=? '
'WHERE id=?')
message = 'altered'
executed = utilities.execute_sql(
sql_code=sql_code, args=values, db_name=constants.STARS_DB,
dml=True, gui=self.gui
)
if executed:
self.gui.results_message.set(f'{permit_number} {message}.')
self._clear_values()
# resets the value of the business code widget to the default
self._set_default_business_code()
# reset the execute option to "Insert"
self.gui.execute_option.set(1)
else:
msg.showerror(
self.title,
'Please select a value for (business_code_id).',
parent=self.gui)
else:
self.gui.permit_ent.focus()
def _get_rep_info(self):
query = f'''
SELECT email, phone
FROM {constants.REPS_TABLE}
WHERE id=?
'''
rep_info = utilities.execute_sql(
sql_code=query, args=(self.jurisdiction.rep_id, ),
db_name=constants.STARS_DB
)
return rep_info
def get_data(self):
sql_code = f'ATTACH DATABASE ? AS ?'
args = (
str(constants.DB_PATHS[self.values_db]),
self.values_db
)
con = sql.connect(
constants.DB_PATHS[constants.BUSINESSES_DB], uri=True,
timeout=constants.DB_TIMEOUT
)
business_db_attached = utilities.execute_sql(
sql_code=sql_code, args=args, open_con=con, dontfetch=True
)
results = []
if business_db_attached:
results = utilities.execute_sql(
sql_code=self.query, open_con=con, getcursor=True
)
data = None
if results:
column_names = [i[0] for i in results.description]
data = results.fetchall()
data = {'column_names' : column_names, 'data' : data}
con.close()
return data
def _get_adjustments_report_attributes(self):
report_id = self.check_files_ids['adjustments']
query = f'''
SELECT Id, Name, Path, IsDuplex
FROM {constants.PACKET_REPORTS_TABLE}
WHERE Id=?
'''
args = (report_id, )
return utilities.execute_sql(
sql_code=query, args=args, db_name=constants.STARS_DB
)
def _set_business_codes(self):
self.business_codes = []
query = f'''
SELECT id, name
FROM {constants.BUSINESS_CODES_TABLE}
'''
results = utilities.execute_sql(
sql_code=query, db_name=constants.STARS_DB, fetchall=True
)
if results:
self.business_codes = [f'{i[0]} : {i[1]}' for i in results]
def _set_categories(self):
self.categories = []
query = f'''
SELECT name
FROM {constants.CATEGORIES_TABLE}
'''
results = utilities.execute_sql(sql_code=query,
db_name=constants.STARS_DB,
fetchall=True)
if results:
for i in results:
self.categories.append(i[0])
def _set_region_ebi(self):
sql_code = 'ATTACH DATABASE ? AS ?'
args = (str(constants.DB_PATHS[constants.STARS_DB]),
constants.STARS_DB)
con = sql.connect(constants.DB_PATHS[constants.STATEWIDE_DATASETS_DB],
uri=True,
timeout=constants.DB_TIMEOUT)
db_attached = utilities.execute_sql(sql_code=sql_code,
args=args,
open_con=con,
dontfetch=True)
if db_attached:
query = f'''
SELECT SUM(ebi)
FROM ebi e, {constants.COUNTIES_TABLE} c,
{constants.JURISDICTIONS_TABLE} j
WHERE e.Id=j.{constants.TAC_COLUMN_NAME}
AND j.HasData=1
AND j.{constants.COUNTY_ID_COLUMN_NAME}=c.Id
AND c.{constants.REGION_ID_COLUMN_NAME}=?
'''
args = (self.jurisdiction.region_id, )
results = utilities.execute_sql(sql_code=query,
args=args,
open_con=con)
if results:
self.region_ebi = results[0] // 1000
def _get_geos(self):
query = '''
SELECT from_range_id, to_range_id, name
FROM geo_names
WHERE jurisdiction_id=?
'''
results = utilities.execute_sql(
sql_code=query,
args=(self.jurisdiction.id, ),
db_name=constants.STATEWIDE_DATASETS_DB,
fetchall=True)
geos = {}
if results:
results.sort()
# insert a row with the column names
results.insert(0, ['FROM', 'TO', 'NAME'])
# formated strings with the geo numbers and name to show in text file
text_lines = [
'{:>4} - {:>5} : {}'.format(from_id, to_id, name)
for from_id, to_id, name in results
]
text_file = f'Export {self.report_name}'
exclude_geos = utilities.get_excluded_from_text_file(
text_file, text_lines, self.jurisdiction)
if exclude_geos:
exclude_geos = [
line.rsplit(':', 1)[0].split('-') for line in exclude_geos
]
exclude_geos = [(int(x[0].strip()), int(x[1].strip()))
for x in exclude_geos]
# loops through all result rows minus the row for the column
# names that that was inserted at the beginning
for from_id, to_id, name in results[1:]:
if (from_id, to_id) not in exclude_geos:
geos[f'{from_id}-{to_id}'] = name
return geos
def _set_naics_codes_dictionary(self):
self.naics_codes = {}
query = f'''
SELECT naics, {constants.BUSINESS_CODE_ID_COLUMN_NAME}
FROM {constants.NAICS_TO_BUSINESS_CODE_TABLE}
'''
results = utilities.execute_sql(
sql_code=query, db_name=constants.STARS_DB, fetchall=True
)
if results:
for i in range(self.NAICS_DIGITS, 1, -1):
naics_codes = self._get_naics_codes_dictionary(results, i)
self.naics_codes[i] = naics_codes
def _set_counties(self):
query = f'''
SELECT {constants.ID_COLUMN_NAME}, name
FROM {constants.COUNTIES_TABLE}
WHERE {constants.REGION_ID_COLUMN_NAME}=?
'''
args = (self.jurisdiction.region_id, )
results = utilities.execute_sql(sql_code=query,
args=args,
db_name=constants.STARS_DB,
fetchall=True)
if results:
for county_id, name in results:
self.counties.append((county_id, name))
def _set_addon_ids(self):
query = (
f'SELECT {constants.ID_COLUMN_NAME} '
f'FROM {constants.ADDONS_TABLE} '
f'WHERE JurisdictionId=?'
)
results = utilities.execute_sql(
sql_code=query, args=(self.jurisdiction.id, ),
db_name=constants.STARS_DB, fetchall=True
)
if results:
if len(results) == 1:
results = results[0]
for i in results:
self.addon_ids.append(i)
