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})