def test_employee_update(client, app):
response = client.get('/human_resources/employees/101098/update')
assert response.status_code == 200
client.post('human_resources/employees/101098/update',
data={
'name': 'Update User',
'first_name': 'Update',
'last_name': 'User',
'att_uid': ''
})
with app.app_context():
db = get_db()
employee = db.execute(
'SELECT EmployeeName, ATTUID, Email '
'FROM DimEmployee '
'WHERE EmployeeKey = ?', 101098).fetchone()
assert employee.EmployeeName == 'Update User'
assert employee.ATTUID == ''
client.post('human_resources/employees/101098/update',
data={
'name': 'Update User',
'first_name': 'Update',
'last_name': 'User',
'att_uid': 'ca941g'
})
with app.app_context():
db = get_db()
employee = db.execute(
'SELECT EmployeeName, ATTUID, Email '
'FROM DimEmployee '
'WHERE EmployeeKey = ?', 101098).fetchone()
assert employee.EmployeeName == 'Update User'
assert employee.ATTUID == 'ca941g'
def manufacturers_index():
db = get_db()
manufacturers = db.execute('SELECT ManufacturerKey, ManufacturerName'
' FROM DimManufacturer'
' ORDER BY ManufacturerName').fetchall()
return render_template('data_warehouse/manufacturers/index.html',
manufacturers=manufacturers)
def manufacturers_update(id):
manufacturer = get_manufacturer(id)
if request.method == 'POST':
manufacturer_name = request.form['manufacturer_name']
error = None
if not manufacturer_name:
error = 'Manufacturer Name is required.'
if error is not None:
flash(error)
else:
try:
db = get_db()
db.execute(
'UPDATE DimManufacturer'
' SET ManufacturerName = ?'
' WHERE ManufacturerKey = ?', (manufacturer_name, id))
db.commit()
return redirect(url_for('data_warehouse.manufacturers_index'))
except IntegrityError:
error = 'Manufacturer Name {} already exists.'.format(
manufacturer_name)
flash(error)
return render_template(
'data_warehouse/manufacturers/update.html',
manufacturer=manufacturer)
return render_template('data_warehouse/manufacturers/update.html',
manufacturer=manufacturer)
def categories_update(id):
category = get_category(id)
if request.method == 'POST':
category_name = request.form['category_name']
error = None
if not category_name:
error = 'Category Name is required.'
if error is not None:
flash(error)
else:
try:
db = get_db()
db.execute(
'UPDATE DimCategory'
' SET CategoryName = ?'
' WHERE CategoryKey = ?', (category_name, id))
db.commit()
return redirect(url_for('data_warehouse.categories_index'))
except IntegrityError:
error = 'Category Name {} already exists.'.format(
category_name)
flash(error)
return render_template('data_warehouse/categories/update.html',
category=category)
return render_template('data_warehouse/categories/update.html',
category=category)
def categories_index():
db = get_db()
categories = db.execute('SELECT CategoryKey, CategoryName'
' FROM DimCategory'
' ORDER BY CategoryName').fetchall()
return render_template('data_warehouse/categories/index.html',
categories=categories)
def employees_create():
db = get_db()
form = EmployeeCreateForm()
if form.validate_on_submit():
new_employee = db.execute(
'INSERT INTO DimEmployee (EmployeeName, FirstName, LastName)'
' OUTPUT INSERTED.EmployeeKey, INSERTED.EmployeeName'
' VALUES (?, ?, ?)', (form.name.data, form.first_name.data,
form.last_name.data)).fetchone()
db.commit()
employee = {
'EmployeeKey': new_employee[0],
'EmployeeName': new_employee[1]
}
email_address = db.execute(
'UPDATE DimEmployee '
'SET Email = ? '
'OUTPUT INSERTED.Email '
'WHERE EmployeeKey = ? ',
(str(form.initials.data).lower() + str(employee['EmployeeKey']) +
'@mywirelessgroup.com', employee['EmployeeKey'])).fetchone()[0]
db.commit()
employee['Email'] = email_address
return redirect(
url_for('human_resources.employees_detail',
id=employee['EmployeeKey']))
return render_template('human_resources/employees/create.html', form=form)
def employees_index():
employees = get_db().execute('SELECT EmployeeName, '
'EmployeeKey, '
'Email '
'FROM DimEmployee').fetchall()
return render_template('human_resources/employees/index.html',
employees=employees)
def employees_update(id):
form = EmployeeUpdateForm()
if form.validate_on_submit():
try:
db = get_db()
db.execute(
'UPDATE DimEmployee'
' SET EmployeeName = ?, FirstName = ?, LastName = ?, ATTUID = ?'
' WHERE EmployeeKey = ?',
(form.name.data, form.first_name.data, form.last_name.data,
form.att_uid.data, id))
db.commit()
return redirect(url_for('human_resources.employees_index'))
except IntegrityError:
error = 'ATTUID {} already exists.'.format(form.att_uid.data)
flash(error)
return render_template('human_resources/employees/update.html',
form=form)
employee = get_employee(id)
form.name.data = employee.EmployeeName
form.first_name.data = employee.FirstName
form.last_name.data = employee.LastName
form.att_uid.data = employee.ATTUID
return render_template('human_resources/employees/update.html', form=form)
def test_manufacturers_create(client, app):
assert client.get('/data_warehouse/manufacturers/create').status_code == 200
client.post('/data_warehouse/manufacturers/create', data={'manufacturer_name': 'New Manufacturer'})
with app.app_context():
db = get_db()
count = db.execute('SELECT COUNT(ManufacturerKey) FROM DimManufacturer').fetchone()[0]
assert count == 5
def test_categories_create(client, app):
assert client.get('/data_warehouse/categories/create').status_code == 200
client.post('/data_warehouse/categories/create', data={'category_name': 'New Category'})
with app.app_context():
db = get_db()
count = db.execute('SELECT COUNT(CategoryKey) FROM DimCategory').fetchone()[0]
assert count == 3
def test_locations_update(client, app):
assert client.get('/data_warehouse/locations/1/update').status_code == 200
client.post('/data_warehouse/locations/1/update', data={'name': 'AT&T - Fremont', 'region': 2})
with app.app_context():
db = get_db()
store = db.execute('SELECT StoreName FROM DimStore WHERE StoreKey = 1').fetchone()
assert store.StoreName == 'AT&T - Fremont'
def index():
db = get_db()
product_no_manufacturer = db.execute(
'SELECT ProductKey'
' FROM DimProduct'
' WHERE ManufacturerKey = -1').fetchone()
no_manufacturer = product_no_manufacturer
return render_template('data_warehouse/index.html',
no_manufacturer=no_manufacturer)
def test_get_close_db(app):
with app.app_context():
db = get_db()
assert db is get_db()
with pytest.raises(pyodbc.ProgrammingError) as e:
db.execute('SELECT 1')
assert 'closed' in str(e.value)
with app.app_context():
db_raw = get_db_raw()
assert db_raw is get_db_raw()
with pytest.raises(pyodbc.ProgrammingError) as e:
db_raw.execute('SELECT 1')
assert 'closed' in str(e.value)
def get_location_district(id):
location_district = get_db().execute(
'SELECT s.StoreName, d.DistrictName, a.StartDate, a.EndDate'
' FROM DimStoreAssignment a JOIN DimStore s ON a.StoreKey = s.StoreKey'
' JOIN DimDistrict d ON a.DistrictKey = d.DistrictKey'
' WHERE a.StoreKey = ?'
' ORDER BY a.StartDate DESC', id)
return location_district
def test_employees_index(client, app):
response = client.get('/human_resources/employees')
assert b'Employees' in response.data
with app.app_context():
db = get_db()
count = db.execute(
'SELECT COUNT(EmployeeKey) FROM DimEmployee').fetchone()[0]
assert count == 1
def test_categories_update(client, app):
assert client.get('/data_warehouse/categories/1/update').status_code == 200
assert client.get('/data_warehouse/categories/3/update').status_code == 404
client.post('/data_warehouse/categories/1/update', data={'category_name': 'updated'})
with app.app_context():
db = get_db()
category = db.execute('SELECT CategoryName FROM DimCategory WHERE CategoryKey = 1').fetchone()
assert category[0] == 'updated'
def test_locations_create(client, app):
assert client.get('/data_warehouse/locations/create').status_code == 200
client.post('/data_warehouse/locations/create', data={'name': 'Test', 'region': 2, 'dealer_code': 'Test',
'rq_abbreviation': 'Test', 'is_active': 'y'})
with app.app_context():
db = get_db()
count = db.execute('SELECT COUNT(StoreKey) FROM DimStore').fetchone()[0]
assert count == 2
def test_manufacturers_update(client, app):
assert client.get('/data_warehouse/manufacturers/1/update').status_code == 200
assert client.get('/data_warehouse/manufacturers/4/update').status_code == 404
client.post('/data_warehouse/manufacturers/1/update', data={'manufacturer_name': 'updated'})
with app.app_context():
db = get_db()
category = db.execute('SELECT ManufacturerName FROM DimManufacturer WHERE ManufacturerKey = 1').fetchone()
assert category[0] == 'updated'
def test_categories_index(client, app):
response = client.get('/data_warehouse/categories')
assert b'Phone' in response.data
assert b'Accessory' in response.data
with app.app_context():
db = get_db()
count = db.execute('SELECT COUNT(CategoryKey) FROM DimCategory').fetchone()[0]
assert count == 2
def products_update(id):
product = get_product(id)
db = get_db()
form = ProductForm()
manufacturers = db.execute('SELECT ManufacturerKey, ManufacturerName'
' FROM DimManufacturer').fetchall()
categories = db.execute('SELECT CategoryKey, CategoryName'
' FROM DimCategory').fetchall()
if request.method == 'POST':
subcategories = db.execute(
'SELECT SubcategoryKey, SubcategoryName'
' FROM DimSubcategory'
' WHERE CategoryKey = ?', form.category_key.data).fetchall()
else:
subcategories = db.execute(
'SELECT SubcategoryKey, SubcategoryName'
' FROM DimSubcategory'
' WHERE CategoryKey = ?', product.CategoryKey).fetchall()
manufacturers_select = [(m.ManufacturerKey, m.ManufacturerName)
for m in manufacturers]
categories_select = [(c.CategoryKey, c.CategoryName) for c in categories]
subcategories_select = [(s.SubcategoryKey, s.SubcategoryName)
for s in subcategories]
form.manufacturer_key.choices = manufacturers_select
form.category_key.choices = categories_select
form.subcategory_key.choices = subcategories_select
if form.validate_on_submit():
try:
db.execute(
'UPDATE DimProduct'
' SET ManufacturerKey = ?, CategoryKey = ?, ProductName = ?, SubcategoryKey = ?'
' WHERE ProductKey = ?',
(form.manufacturer_key.data, form.category_key.data,
form.product_name.data, form.subcategory_key.data, id))
db.commit()
error = 'Updated product {}: {}.'.format(id,
form.product_name.data)
flash(error)
cache.clear()
return redirect(url_for('data_warehouse.index'))
except IntegrityError:
error = 'Store Name {} already exists.'.format(form.name.data)
flash(error)
return render_template('data_warehouse/locations/update.html',
form=form)
form.manufacturer_key.data = product.ManufacturerKey
form.category_key.data = product.CategoryKey
form.product_name.data = product.ProductName
form.subcategory_key.data = product.SubcategoryKey if product.SubcategoryKey else None
return render_template('data_warehouse/products/update.html', form=form)
def get_employee(id):
employee = get_db().execute(
'SELECT EmployeeKey, EmployeeName, FirstName, LastName, ATTUID, Email'
' FROM DimEmployee'
' WHERE EmployeeKey = ?', (id, )).fetchone()
if employee is None:
abort(404, "Employee id {0} doesn't exist.".format(id))
return employee
def get_category(id):
category = get_db().execute(
'SELECT CategoryKey, CategoryName'
' FROM DimCategory'
' WHERE CategoryKey = ?', (id, )).fetchone()
if category is None:
abort(404, "Category id {0} doesn't exist.".format(id))
return category
def locations_index():
db = get_db()
locations = db.execute(
'SELECT s.StoreKey, s.StoreName, r.RegionName, s.DealerCode, s.RQAbbreviation'
' FROM DimStore s JOIN DimRegion r'
' ON s.RegionKey = r.RegionKey'
' WHERE s.IsActive = 1'
' ORDER BY s.StoreName').fetchall()
return render_template('data_warehouse/locations/index.html',
locations=locations)
def get_products_no_manufacturer():
products = get_db().execute(
'SELECT p.ProductKey, m.ManufacturerName, c.CategoryName, p.ProductName, s.SubcategoryName'
' FROM DimProduct p '
' LEFT JOIN DimManufacturer m on p.ManufacturerKey = m.ManufacturerKey'
' LEFT JOIN DimCategory c on p.CategoryKey = c.CategoryKey'
' LEFT JOIN DimSubcategory s on p.SubcategoryKey = s.SubcategoryKey'
' WHERE p.ManufacturerKey = -1').fetchall()
return products
def get_product(id):
product = get_db().execute(
'SELECT ManufacturerKey, CategoryKey, ProductName, SubcategoryKey'
' FROM DimProduct'
' WHERE ProductKey = ?', (id)).fetchone()
if product is None:
abort(404, "Product SKU {} doesn't exist.".format(id))
return product
def get_manufacturer(id):
manufacturer = get_db().execute(
'SELECT ManufacturerKey, ManufacturerName'
' FROM DimManufacturer'
' WHERE ManufacturerKey = ?', (id, )).fetchone()
if manufacturer is None:
abort(404, "Manufacturer id {0} doesn't exist.".format(id))
return manufacturer
def test_manufacturers_index(client, app):
response = client.get('/data_warehouse/manufacturers')
assert b'Apple' in response.data
assert b'Samsung' in response.data
assert b'LG Electronics' in response.data
assert b'Amazon' not in response.data
with app.app_context():
db = get_db()
count = db.execute('SELECT COUNT(ManufacturerKey) FROM DimManufacturer').fetchone()[0]
assert count == 4
def test_products_update(client, app):
assert client.get('/data_warehouse/products/AEDEPB000159/update').status_code == 200
client.post('/data_warehouse/products/AEDEPB000159/update', data={'product_name': 'Product Update',
'manufacturer_key': 1,
'category_key': 2,
'subcategory_key': 1})
with app.app_context():
db = get_db()
product = db.execute('SELECT ProductName FROM DimProduct WHERE ProductKey = ?', 'AEDEPB000159').fetchone()
assert product.ProductName == 'Product Update'
def get_location(id):
location = get_db().execute(
'SELECT s.StoreKey, s.StoreName, s.RegionKey, s.DealerCode, s.RQAbbreviation, s.IsActive, a.DistrictKey'
' FROM DimStore s JOIN DimRegion r ON s.RegionKey = r.RegionKey'
' LEFT JOIN DimStoreAssignment a on s.StoreKey = a.StoreKey AND a.EndDate IS NULL'
' WHERE s.StoreKey = ?', (id, )).fetchone()
if location is None:
abort(404, "Location id {0} doesn't exist.".format(id))
return location
def subcategory(category):
db = get_db()
subcategories = db.execute(
'SELECT SubcategoryKey, SubcategoryName'
' FROM DimSubcategory'
' WHERE CategoryKey = ?', category).fetchall()
sub_array = []
for s in subcategories:
sub_obj = dict()
sub_obj['id'] = s.SubcategoryKey
sub_obj['name'] = s.SubcategoryName
sub_array.append(sub_obj)
return jsonify({'subcategories': sub_array})
