def db_dl_prodinfo(dbinfo, orders_scenes):
"""
Queries the database to get the associated product options
This query is meant to go with downloads by product
:param dbinfo: Database connection information
:type dbinfo: dict
:param orders_scenes: Order id's that have been downloaded from
based on web logs and scene names
:type orders_scenes: tuple
:return: Dictionary of count values
"""
ids = zip(*orders_scenes)[0]
ids = remove_duplicates(ids)
sql = ('SELECT o.orderid, o.product_opts '
'FROM ordering_order o '
'WHERE o.orderid = ANY (%s)')
with DBConnect(**dbinfo) as db:
db.select(sql, (ids, ))
results = {k: val for k, val in db.fetcharr}
return results
def editItem(category_name, item_name):
db = DBConnect()
item = db.getItemByName(item_name)
if request.method == 'POST':
name = request.form['name']
description = request.form['description']
categoryName = request.form['category']
if name and description and categoryName:
category = db.getCategoryByName(categoryName)
db.editItem(item, name, description, category.id)
return redirect(
url_for('showItem',
category_name=category.name,
item_name=name))
if request.method == 'GET':
# Authorization check before serving the edit page
userEmail = session.get('email')
userID = db.getUserIDByEmail(userEmail)
if userID != item.user.id:
return redirect(
url_for('error',
error='You are not\
authorized to edit this item'))
categories = db.getAllCategories()
return render_template('editItem.html',
selectedItem=item,
categories=categories)
def newCategory():
if request.method == 'POST':
# Get name from the posted form
name = request.form['name'].strip()
# Verify that a name was entered
if name:
db = DBConnect()
# Check if this category already exsists
if not db.categoryNameUsed(name):
# Add the new category
db.addCategory(name, 1)
# Redirect to catalog page
return redirect(url_for('catalog'))
# If the category was not added, redirect the
# user to the error page
return redirect(
url_for('error',
error='This name\
was already used'))
return redirect(url_for('error', error='You need to enter a name'))
if request.method == 'GET':
user = session.get('username')
if user is None:
return redirect(url_for('showLogin'))
return render_template('newCategory.html')
def newItem(category_name):
if request.method == 'POST':
# Strip off the extra spaces the user may have entered
name = request.form['name'].strip()
description = request.form['description']
# Ensure we have needed item info
if name and description:
db = DBConnect()
isUsed = db.itemNameUsed(name)
# Check if the item name has already been used somewhere else
if not isUsed['used']:
category = db.getCategoryByName(category_name)
userID = db.getUserIDByEmail(session['email'])
db.addItem(name, description, category.id, userID)
return redirect(
url_for('showItem\
',
category_name=category_name,
item_name=name))
return redirect(
url_for('error',
error='This\
item name has already been used'))
return redirect(
url_for('error',
error='You need to enter\
both a name and description'))
if request.method == 'GET':
user = session.get('username')
if user is None:
return redirect(url_for('showLogin'))
return render_template('newItem.html', categoryName=category_name)
def db_top10stats(begin_date, end_date, sensors, dbinfo):
"""
Queries the database to get the total number of unique users
dates are given as ISO 8601 'YYYY-MM-DD'
:param source: EE or ESPA
:type source: str
:param begin_date: Date to start the count on
:type begin_date: str
:param end_date: Date to stop the count on
:type end_date: str
:param sensors: which sensor types to process (['tm4', 'etm7',...])
:type sensors: tuple
:param dbinfo: Database connection information
:type dbinfo: dict
:return: Dictionary of the count
"""
sql = '''select u.email, coalesce(sum(jsonb_array_length(product_opts->sensors->'inputs')),0) scenes
from ordering_order o
left join lateral jsonb_object_keys(product_opts) sensors on True
join auth_user u
on o.user_id = u.id
where o.order_date::date >= %s
and o.order_date::date <= %s
and sensors in %s
and product_opts->sensors ? 'inputs'
group by u.email
order by scenes desc
limit 10'''
with DBConnect(**dbinfo) as db:
db.select(sql, (begin_date, end_date, sensors))
return db[:]
def query_sensor_count(dbinfo, start, end, sensors=None):
"""Select aggregate number of scenes sorted by sensor."""
sql = '''
select count(s.name) n_scenes,
left(s.name, 4) sensor,
extract(month from o.order_date) mm,
extract(year from o.order_date) yy
from ordering_scene s
join ordering_order o on o.id=s.order_id
where o.order_date::date >= '{0}'
and o.order_date::date <= '{1}'
and s.sensor_type = 'landsat'
group by sensor, yy, mm'''
with DBConnect(**dbinfo) as db:
dat = sqlio.read_sql_query(sql.format(start, end), db.conn)
d2 = dat.pivot_table(index='mm', values='n_scenes',
columns='sensor').fillna(0)
def p_month_name(mmm):
"""Format integers as month names."""
return datetime.date(2017, mmm, 1).strftime('%b')
d2.index = d2.index.astype(int).map(p_month_name)
if 'LO08' in d2.columns:
d2['LC08'] += d2['LO08']
return d2[sensors]
def db_prodinfo(dbinfo, begin_date, end_date, sensors):
"""
Queries the database to build the ordered product counts
dates are given as ISO 8601 'YYYY-MM-DD'
:param dbinfo: Database connection information
:type dbinfo: dict
:param begin_date: Date to start the counts on
:type begin_date: str
:param end_date: Date to end the counts on
:type end_date: str
:param sensors: which sensors to process (['tm4','etm7',...])
:type sensors: tuple
:return: Dictionary of count values
"""
sql = ('SELECT product_opts '
'FROM ordering_order '
'left join lateral jsonb_object_keys(product_opts) sensors on True '
'WHERE order_date::date >= %s '
'AND order_date::date <= %s '
'AND sensors in %s '
"and product_opts->sensors ? 'inputs' "
"group by product_opts, id")
init = {'total': 0}
with DBConnect(**dbinfo) as db:
db.select(sql, (begin_date, end_date, sensors))
results = reduce(counts_prodopts, [process_db_prodopts(r, sensors) for r in db.fetcharr], init)
results['title'] = 'What was Ordered'
return results
def db_uniquestats(source, begin_date, end_date, sensors, dbinfo):
"""
Queries the database to get the total number of unique users
dates are given as ISO 8601 'YYYY-MM-DD'
:param source: EE or ESPA
:type source: str
:param begin_date: Date to start the count on
:type begin_date: str
:param end_date: Date to stop the count on
:type end_date: str
:param sensors: which sensor types to process (['tm4', 'etm7',...])
:type sensors: tuple
:param dbinfo: Database connection information
:type dbinfo: dict
:return: Dictionary of the count
"""
sql = '''select count(distinct(email))
from ordering_order
left join lateral jsonb_object_keys(product_opts) sensors on True
where order_date::date >= %s
and order_date::date <= %s
and order_source = %s
and sensors in %s
and product_opts->sensors ? 'inputs' ;'''
with DBConnect(**dbinfo) as db:
db.select(sql, (begin_date, end_date, source, sensors))
return db[0][0]
def catalog():
db = DBConnect()
categories = db.getAllCategories()
recentItems = db.getAllRecentItems()
return render_template('categories.\
html',
categories=categories,
recentItems=recentItems)
def showCategory(category_name):
db = DBConnect()
categories = db.getAllCategories()
items = db.getItemsByCategory(category_name)
count = db.getItemCountByCategory(category_name)
return render_template('showCategory.html',
categories=categories,
items=items,
categoryName=category_name,
numberOfItems=count)
def query_scene_count(dbinfo, start, end, who=None):
"""Query count of scenes ordered per path/row.
Arsg:
dbinfo (dict): database connection info
start (str): begining of period range (e.g. '2018-04-01')
end (str): end of periof range (e.g. '2018-04-30')
Returns:
np.array: number of scenes per path/row
"""
sql = '''
select count(*) n_scenes,
case when split_part(s.name, '_', 2) = ''
then right(left(s.name,6),3)
else left(split_part(s.name, '_', 3), 3) end as path,
case when split_part(s.name, '_', 2) = ''
then right(left(s.name,9),3)
else right(split_part(s.name, '_', 3), 3) end as row
from ordering_scene s
left join ordering_order o on o.id=s.order_id
where
o.order_date::date >= '{0}'
and o.order_date::date <= '{1}'
and s.sensor_type = 'landsat'
{2}
group by path, row
;
'''
email_str = '' if who is 'ALL' else ("and o.email = '%s'" % who)
with DBConnect(**dbinfo) as db:
dat = sqlio.read_sql_query(sql.format(start, end, email_str), db.conn)
dat['path'] = dat['path'].astype(int)
dat['row'] = dat['row'].astype(int)
dat['alpha'] = dat['n_scenes'].apply(lambda v: get_alpha
(v, MAXALPHA, MINALPHA,
dat['n_scenes'].min(),
dat['n_scenes'].max()))
dat = dat.sort_values(by='alpha')
return (
dat[['path', 'row', 'alpha']].values,
dat['n_scenes'].min(),
dat['n_scenes'].max()
)
def db_orderstats(source, begin_date, end_date, sensors, dbinfo):
"""
Queries the database to get the total number of orders
separated by USGS and non-USGS emails
dates are given as ISO 8601 'YYYY-MM-DD'
:param source: EE or ESPA
:type source: str
:param begin_date: Date to start the count on
:type begin_date: str
:param end_date: Date to stop the count on
:type end_date: str
:param sensors: which sensor types to process (['tm4', 'etm7',...])
:type sensors: tuple
:param dbinfo: Database connection information
:type dbinfo: dict
:return: Dictionary of the counts
"""
sql = ('''select COUNT(distinct orderid)
from ordering_order
left join lateral jsonb_object_keys(product_opts) sensors on True
where order_date::date >= %s
and order_date::date <= %s
and orderid like '%%@usgs.gov-%%'
and order_source = %s
and sensors in %s
and product_opts->sensors ? 'inputs' ;''',
'''select COUNT(distinct orderid)
from ordering_order
left join lateral jsonb_object_keys(product_opts) sensors on True
where order_date::date >= %s
and order_date::date <= %s
and orderid not like '%%@usgs.gov-%%'
and order_source = %s
and sensors in %s
and product_opts->sensors ? 'inputs' ;''')
counts = {'orders_month': 0,
'orders_usgs': 0,
'orders_non': 0}
with DBConnect(**dbinfo) as db:
for q in sql:
db.select(q, (begin_date, end_date, source, sensors))
if 'not like' in q:
counts['orders_non'] += int(db[0][0])
else:
counts['orders_usgs'] += int(db[0][0])
counts['orders_month'] = counts['orders_usgs'] + counts['orders_non']
return counts
def get_email_addr(dbinfo, who):
"""
Retrieve email address(es) from the database
for a specified role
"""
key = 'email.{0}'.format(who)
sql = 'select value from ordering_configuration where key = %s'
with DBConnect(**dbinfo) as db:
db.select(sql, key)
out = db[0][0].split(',')
return out
def showItem(category_name, item_name):
db = DBConnect()
selectedItem = db.getItemByName(item_name)
userEmail = session.get('email')
userID = db.getUserIDByEmail(userEmail)
if userID != selectedItem.user.id:
return render_template('publicShowItem.html\
',
selectedItem=selectedItem,
categoryName=category_name)
return render_template('showItem.html\
',
selectedItem=selectedItem,
categoryName=category_name)
def current_pass(db_info):
"""
Retrieves the current password from the database
:param db_info: database connection information
:type db_info: dict
:return: exception message
"""
sql_str = "select value from ordering_configuration where key = 'landsatds.password'"
with DBConnect(**db_info) as db:
db.select(sql_str)
curr = db[0][0]
return curr
def get_config_value(dbinfo, key):
"""
Retrieve a specified configuration value
:param dbinfo: DB connection information
:param key: table key to get the value for
:return: value
"""
sql = ('SELECT value from ordering_configuration ' 'WHERE key = %s')
with DBConnect(**dbinfo) as db:
db.select(sql, key)
ret = db[0][0]
return ret
def catalogJSON():
user = session.get('username')
if user is None:
return redirect(url_for('showLogin'))
db = DBConnect()
categories = db.getAllCategories()
# Building the json object to be returned
total = {'Category': []}
for c in categories:
items = db.getItemsByCategory(c.name)
total['Category'].append({
'id': c.id,
'name': c.name,
'items': [i.serialize for i in items]
})
return jsonify(total)
def update_db(passwrd, db_info):
"""
Update the database with the new password
:param passwrd: new password
:type passwrd: string
:param db_info: database connection information
:type db_info: dict
:return: exception message
"""
sql_str = "update ordering_configuration set value = %s where key = 'landsatds.password'"
try:
with DBConnect(**db_info) as db:
db.execute(sql_str, passwrd)
db.commit()
except Exception:
raise CredentialException('Error updating the database with the new password')
def __init__(self, env):
self._base_url = base_url_from_env(env)
self._first_name = ''
self._last_name = ''
self._job_title = ''
self._email = ''
self._password = ''
self._space_guid = ''
self._organization_guid = ''
self._role = ''
self._person_guid = ''
self._employee_guid = ''
self._user_guid = ''
self._invite_code_guid = ''
self._headers = {}
self._dbconn = DBConnect(env)
self._is_admin = False
def db_scenestats(source, begin_date, end_date, dbinfo):
"""
Queries the database for the number of scenes ordered
separated by USGS and non-USGS emails
dates are given as ISO 8601 'YYYY-MM-DD'
:param source: EE or ESPA
:type source: str
:param begin_date: Date to start the count on
:type begin_date: str
:param end_date: Date to stop the count on
:type end_date: str
:param dbinfo: Database connection information
:type dbinfo: dict
:return: Dictionary of the counts
"""
sql = ('''select COUNT(*)
from ordering_scene
inner join ordering_order on ordering_scene.order_id = ordering_order.id
where ordering_order.order_date::date >= %s
and ordering_order.order_date::date <= %s
and ordering_order.orderid like '%%@usgs.gov-%%'
and ordering_order.order_source = %s;''', '''select COUNT(*)
from ordering_scene
inner join ordering_order on ordering_scene.order_id = ordering_order.id
where ordering_order.order_date::date >= %s
and ordering_order.order_date::date <= %s
and ordering_order.orderid not like '%%@usgs.gov-%%'
and ordering_order.order_source = %s;''')
counts = {'scenes_month': 0, 'scenes_usgs': 0, 'scenes_non': 0}
with DBConnect(**dbinfo) as db:
for q in sql:
db.select(q, (begin_date, end_date, source))
if 'not like' in q:
counts['scenes_non'] += int(db[0][0])
else:
counts['scenes_usgs'] += int(db[0][0])
counts['scenes_month'] = counts['scenes_usgs'] + counts['scenes_non']
return counts
def deleteItem(category_name, item_name):
db = DBConnect()
item = db.getItemByName(item_name)
if request.method == 'POST':
db.deleteItem(item)
return redirect(url_for('showCategory', category_name=category_name))
if request.method == 'GET':
# Authorization check before serving the delete page
userEmail = session.get('email')
userID = db.getUserIDByEmail(userEmail)
if userID != item.user.id:
return redirect(
url_for('error',
error='You are not\
authorized to delete this item'))
return render_template('deleteItem.html',
categoryName=category_name,
itemName=item_name)
def db_dl_prodinfo(dbinfo, ids):
"""
Queries the database to get the associated product options
:param dbinfo: Database connection information
:type dbinfo: dict
:param ids: Order id's that have been downloaded from based on web logs
:type ids: tuple
:return: Dictionary of count values
"""
ids = remove_duplicates(ids)
sql = ('SELECT o.orderid, o.product_options '
'FROM ordering_order o '
'WHERE o.orderid = ANY (%s)')
with DBConnect(**dbinfo) as db:
db.select(sql, (ids, ))
results = [x for x in db]
return results
def db_prodinfo(dbinfo, begin_date, end_date):
"""
Queries the database to build the ordered product counts
dates are given as ISO 8601 'YYYY-MM-DD'
:param dbinfo: Database connection information
:type dbinfo: dict
:param begin_date: Date to start the counts on
:type begin_date: str
:param end_date: Date to end the counts on
:type end_date: str
:return: Dictionary of count values
"""
sql = ('''SELECT COUNT(s.name) "total",
SUM(CASE WHEN o.product_options::json->>'include_cfmask' = 'true' THEN 1 ELSE 0 END) "include_cfmask",
SUM(CASE WHEN o.product_options::json->>'include_customized_source_data' = 'true' THEN 1 ELSE 0 END) "include_customized_source_data",
SUM(CASE WHEN o.product_options::json->>'include_sr_evi' = 'true' THEN 1 ELSE 0 END) "include_sr_evi",
SUM(CASE WHEN o.product_options::json->>'include_source_metadata' = 'true' THEN 1 ELSE 0 END) "include_source_metadata",
SUM(CASE WHEN o.product_options::json->>'include_sr_msavi' = 'true' THEN 1 ELSE 0 END) "include_sr_msavi",
SUM(CASE WHEN o.product_options::json->>'include_sr_nbr' = 'true' THEN 1 ELSE 0 END) "include_sr_nbr",
SUM(CASE WHEN o.product_options::json->>'include_sr_nbr2' = 'true' THEN 1 ELSE 0 END) "include_sr_nbr2",
SUM(CASE WHEN o.product_options::json->>'include_sr_ndmi' = 'true' THEN 1 ELSE 0 END) "include_sr_ndmi",
SUM(CASE WHEN o.product_options::json->>'include_sr_ndvi' = 'true' THEN 1 ELSE 0 END) "include_sr_ndvi",
SUM(CASE WHEN o.product_options::json->>'include_sr_savi' = 'true' THEN 1 ELSE 0 END) "include_sr_savi",
SUM(CASE WHEN o.product_options::json->>'include_source_data' = 'true' THEN 1 ELSE 0 END) "include_source_data",
SUM(CASE WHEN o.product_options::json->>'include_sr' = 'true' THEN 1 ELSE 0 END) "include_sr",
SUM(CASE WHEN o.product_options::json->>'include_sr_thermal' = 'true' THEN 1 ELSE 0 END) "include_sr_thermal",
SUM(CASE WHEN o.product_options::json->>'include_sr_toa' = 'true' THEN 1 ELSE 0 END) "include_sr_toa"
FROM ordering_order o
JOIN ordering_scene s ON s.order_id = o.id
WHERE LENGTH(o.product_options) > 0
AND o.order_date::date >= %s
AND o.order_date::date <= %s;''')
with DBConnect(cursor_factory=psycopg2.extras.DictCursor, **dbinfo) as db:
db.select(sql, (begin_date, end_date))
results = dict(db[0])
results['title'] = 'What was Ordered'
return results
def db_uniquestats(source, begin_date, end_date, dbinfo):
"""
Queries the database to get the total number of unique users
dates are given as ISO 8601 'YYYY-MM-DD'
:param source: EE or ESPA
:type source: str
:param begin_date: Date to start the count on
:type begin_date: str
:param end_date: Date to stop the count on
:type end_date: str
:param dbinfo: Database connection information
:type dbinfo: dict
:return: Dictionary of the count
"""
sql = '''select count(distinct(split_part(orderid, '-', 1)))
from ordering_order
where order_date::date >= %s
and order_date::date <= %s
and order_source = %s;'''
with DBConnect(**dbinfo) as db:
db.select(sql, (begin_date, end_date, source))
return db[0][0]
from employee import Employee
from erp_organization import Organization
from workspace import Workspace
from dbconnect import DBConnect
urllib3.disable_warnings()
while True:
env = input("Введите среду (test/dev/demo/prod): ").lower()
if env not in ['test', 'dev', 'demo', 'prod']:
continue
break
new_space = Workspace(env)
new_space_admin = Employee(env)
db = DBConnect(env)
while True:
space_name = input("Введите Имя Спейса (должно быть уникальным): ")
if space_name.lower() in db.space_codes_on_server():
print("Пространство с таким именем уже сущестует. Введите другое имя")
continue
break
while True:
email = input("Введите валидный email (логин должен быть уникальным в рамках всех спейсов): ")
if new_space_admin.login_exists(email):
print("Логин занят")
continue
break
def gconnect():
# Verify that the token the client sends the server matches the one
# that was sent
if request.args.get('state') != session['state']:
# If they do not match then respond with an error
response = make_response(json.dumps('Invalid state token'), 401)
response.headers['Content-Type'] = 'application/json'
return response
# If the state tokens match then we take our code
code = request.data
# Try to use the one time code and exchange it for a
# credentials object
try:
# Create oauth flow object and adds client secret key info to
# that object
oauth_flow = flow_from_clientsecrets('secret/client_secrets.json',
scope='')
# Specify that this the one time code flow this server sends off
oauth_flow.redirect_uri = 'postmessage'
# Init exchange
credentials = oauth_flow.step2_exchange(code)
# Handle the case where an error occurs during the exchange
except FlowExchangeError:
response = make_response(
json.dumps('Failed to upgrade the\
authorization code'), 401)
response.headers['Content-Type'] = 'application/json'
return response
# Check to see if there is a valid access token inside of the
# returned credentials
access_token = credentials.access_token
url = 'https://www.googleapis.com/oauth2/v1/tokeninfo?access_token=%s'\
% access_token
h = httplib2.Http()
result = json.loads(h.request(url, 'GET')[1])
if result.get('error') is not None:
response = make_response(json.dumps(result['error']), 500)
response.headers['Content-Type'] = 'application/json'
return response
# Compare id in the credentials object against the id returned
# by the google api server
gplus_id = credentials.id_token['sub']
if result['user_id'] != gplus_id:
response = make_response(
json.dumps('Token user ID does not\
match given user ID'), 401)
response.headers['Content-Type'] = 'application/json'
return response
# Compare client IDs
if result['issued_to'] != CLIENT_ID:
response = make_response(
json.dumps('Token client ID does not\
match the apps ID'), 401)
print('Token client ID does not match the apps ID')
response.headers['Content-Type'] = 'application/json'
return response
# Check if the user is already logged into the system
stored_credentials = session.get('credentials')
stored_gplus_id = session.get('gplus_id')
if stored_credentials is not None and stored_gplus_id == gplus_id:
response = make_response(
json.dumps('Current user is\
already logged in'), 200)
response.headers['Content-Type'] = 'application/json'
return response
# Store credentials and google plus id in this session
session['credentials'] = credentials.access_token
session['gplus_id'] = gplus_id
# Get more information about the user from the google plus api
userinfo_url = 'https://www.googleapis.com/oauth2/v1/userinfo'
params = {'access_token': credentials.access_token, 'alt': 'json'}
answer = requests.get(userinfo_url, params=params)
data = json.loads(answer.text)
# Store user info in login session
session['username'] = data['email']
session['email'] = data['email']
session['picture'] = data['picture']
db = DBConnect()
userID = db.getUserIDByEmail(session['email'])
if userID is None:
db.createUser(session['username'], session['email'],
session['picture'])
userID = db.getUserIDByEmail(session['email'])
session['user_id'] = userID
output = ''
output += '<h1>Welcome, '
output += session['username']
return output
