def insert_default_stocktakingcollections():
db.session.add(StocktakingCollection(admin_id=1))
db.session.add(StocktakingCollection(admin_id=1))
db.session.flush()
stocktakings = [{
'product_id': 1,
'count': 100
}, {
'product_id': 2,
'count': 50
}, {
'product_id': 3,
'count': 25
}, {
'product_id': 4,
'count': 33
}]
for s in stocktakings:
db.session.add(Stocktaking(**s, collection_id=1))
stocktakings = [{
'product_id': 1,
'count': 50
}, {
'product_id': 2,
'count': 25
}, {
'product_id': 3,
'count': 12
}, {
'product_id': 4,
'count': 3
}]
for s in stocktakings:
db.session.add(Stocktaking(**s, collection_id=2))
db.session.commit()
def test_balance_between_stocktakings_with_profit_and_loss(self):
"""
This test checks whether the profits and losses and the resulting
balance are calculated correctly.
"""
# Manipulate the product price timestamps
ts = datetime.strptime('2017-01-01 09:00:00', '%Y-%m-%d %H:%M:%S')
for id in [1, 2, 3, 4]:
ProductPrice.query.filter_by(id=id).first().timestamp = ts
db.session.commit()
# Insert the first stocktaking
db.session.add(StocktakingCollection(admin_id=1))
db.session.flush()
stocktakings = [{
'product_id': 1,
'count': 100
}, {
'product_id': 2,
'count': 100
}, {
'product_id': 3,
'count': 100
}, {
'product_id': 4,
'count': 100
}]
for s in stocktakings:
db.session.add(Stocktaking(**s, collection_id=1))
# Manipulate first stocktaking timestamp
# First stocktaking: 01.01.2018
ts = datetime.strptime('2018-01-01 09:00:00', '%Y-%m-%d %H:%M:%S')
StocktakingCollection.query.filter_by(id=1).first().timestamp = ts
# Insert the second stocktaking
db.session.add(StocktakingCollection(admin_id=1))
db.session.flush()
stocktakings = [
{
'product_id': 1,
'count': 110
}, # Products have been added!
{
'product_id': 2,
'count': 90
}, # Products have been lost!
{
'product_id': 3,
'count': 100
},
{
'product_id': 4,
'count': 100
}
]
for s in stocktakings:
db.session.add(Stocktaking(**s, collection_id=2))
# Manipulate second stocktaking timestamp
# Second stocktaking: 01.02.2018
ts = datetime.strptime('2018-02-01 09:00:00', '%Y-%m-%d %H:%M:%S')
StocktakingCollection.query.filter_by(id=2).first().timestamp = ts
# Query the stocktakings.
start = StocktakingCollection.query.filter_by(id=1).first()
end = StocktakingCollection.query.filter_by(id=2).first()
result = stocktaking_helpers._get_balance_between_stocktakings(
start, end)
self.assertTrue('products' in result)
products = result['products']
# Check if all products are in the balance
self.assertEqual({1, 2, 3, 4}, set(products.keys()))
# Check purchase count
self.assertEqual(products[1]['purchase_count'], 0)
self.assertEqual(products[2]['purchase_count'], 0)
self.assertEqual(products[3]['purchase_count'], 0)
self.assertEqual(products[4]['purchase_count'], 0)
# Check purchase sum price
self.assertEqual(products[1]['purchase_sum_price'], 0)
self.assertEqual(products[2]['purchase_sum_price'], 0)
self.assertEqual(products[3]['purchase_sum_price'], 0)
self.assertEqual(products[4]['purchase_sum_price'], 0)
# Check replenish count
self.assertEqual(products[1]['replenish_count'], 0)
self.assertEqual(products[2]['replenish_count'], 0)
self.assertEqual(products[3]['replenish_count'], 0)
self.assertEqual(products[4]['replenish_count'], 0)
# Check differences
self.assertEqual(products[1]['difference'], 10)
self.assertEqual(products[2]['difference'], -10)
self.assertEqual(products[3]['difference'], 0)
self.assertEqual(products[4]['difference'], 0)
# Check balance
self.assertEqual(products[1]['balance'], 10 * 300)
self.assertEqual(products[2]['balance'], -10 * 50)
self.assertEqual(products[3]['balance'], 0)
self.assertEqual(products[4]['balance'], 0)
# Check overall balance
self.assertEqual(result['balance'], 2500)
self.assertEqual(result['loss'], 500)
self.assertEqual(result['profit'], 3000)
def test_balance_between_stocktakings_product_creation(self):
"""
This test checks whether the calculation of the balance works
correctly if a new product has been added since the first stocktaking.
"""
# Insert the first stocktaking
db.session.add(StocktakingCollection(admin_id=1))
db.session.flush()
stocktakings = [{
'product_id': 1,
'count': 100
}, {
'product_id': 2,
'count': 50
}, {
'product_id': 3,
'count': 25
}, {
'product_id': 4,
'count': 33
}]
for s in stocktakings:
db.session.add(Stocktaking(**s, collection_id=1))
# Manipulate first stocktaking timestamp
# First stocktaking: 01.01.2018
ts = datetime.strptime('2018-01-01 09:00:00', '%Y-%m-%d %H:%M:%S')
StocktakingCollection.query.filter_by(id=1).first().timestamp = ts
# Create a product.
data = {'name': 'Bread', 'price': 100, 'tags': [1]}
self.post(url='/products', role='admin', data=data)
# Manipulate the product price timestamps
ts = datetime.strptime('2017-01-01 09:00:00', '%Y-%m-%d %H:%M:%S')
for product_price_id in [1, 2, 3, 4, 5]:
ProductPrice.query.filter_by(
id=product_price_id).first().timestamp = ts
db.session.commit()
# Create replenishment.
replenishments = [{'product_id': 5, 'amount': 20, 'total_price': 2000}]
data = {
'replenishments': replenishments,
'comment': 'My test comment',
'timestamp': '2020-02-24 12:00:00Z',
'seller_id': 5
}
self.post(url='/replenishmentcollections', data=data, role='admin')
ts = datetime.strptime('2018-03-01 09:00:00', '%Y-%m-%d %H:%M:%S')
ReplenishmentCollection.query.filter_by(id=1).first().timestamp = ts
db.session.commit()
# Insert the second stocktaking.
stocktakings = [{
'product_id': 1,
'count': 100
}, {
'product_id': 2,
'count': 50
}, {
'product_id': 3,
'count': 25
}, {
'product_id': 4,
'count': 33
}, {
'product_id': 5,
'count': 10
}]
t = datetime.strptime('2018-04-01 09:00:00', '%Y-%m-%d %H:%M:%S')
data = {'stocktakings': stocktakings, 'timestamp': int(t.timestamp())}
self.post(url='/stocktakingcollections', data=data, role='admin')
# Query the stocktakings.
start = StocktakingCollection.query.filter_by(id=1).first()
end = StocktakingCollection.query.filter_by(id=2).first()
self.assertFalse(5 in [s.product_id for s in start.stocktakings])
self.assertTrue(5 in [s.product_id for s in end.stocktakings])
result = stocktaking_helpers._get_balance_between_stocktakings(
start, end)
self.assertTrue('products' in result)
products = result['products']
# Check if all products are in the balance
self.assertEqual({1, 2, 3, 4, 5}, set(products.keys()))
# Check purchase count
self.assertEqual(products[1]['purchase_count'], 0)
self.assertEqual(products[2]['purchase_count'], 0)
self.assertEqual(products[3]['purchase_count'], 0)
self.assertEqual(products[4]['purchase_count'], 0)
self.assertEqual(products[5]['purchase_count'], 0)
# Check purchase sum price
self.assertEqual(products[1]['purchase_sum_price'], 0)
self.assertEqual(products[2]['purchase_sum_price'], 0)
self.assertEqual(products[3]['purchase_sum_price'], 0)
self.assertEqual(products[4]['purchase_sum_price'], 0)
self.assertEqual(products[5]['purchase_sum_price'], 0)
# Check replenish count
self.assertEqual(products[1]['replenish_count'], 0)
self.assertEqual(products[2]['replenish_count'], 0)
self.assertEqual(products[3]['replenish_count'], 0)
self.assertEqual(products[4]['replenish_count'], 0)
self.assertEqual(products[5]['replenish_count'], 20)
# Check differences
self.assertEqual(products[1]['difference'], 0)
self.assertEqual(products[2]['difference'], 0)
self.assertEqual(products[3]['difference'], 0)
self.assertEqual(products[4]['difference'], 0)
self.assertEqual(products[5]['difference'], -10)
# Check balance
self.assertEqual(products[1]['balance'], 0)
self.assertEqual(products[2]['balance'], 0)
self.assertEqual(products[3]['balance'], 0)
self.assertEqual(products[4]['balance'], 0)
self.assertEqual(products[5]['balance'], -10 * 100)
# Check overall balance
self.assertEqual(result['balance'], -1000)
self.assertEqual(result['profit'], 0)
def test_balance_between_stocktakings_product_set_to_inactive(self):
"""
This test checks whether the calculation of the balance works
correctly if a product has been set to inactive since the first
stocktaking.
"""
# Manipulate the product price timestamps
ts = datetime.strptime('2017-01-01 09:00:00', '%Y-%m-%d %H:%M:%S')
for product_price_id in [1, 2, 3, 4]:
ProductPrice.query.filter_by(
id=product_price_id).first().timestamp = ts
db.session.commit()
# Insert the first stocktaking
db.session.add(StocktakingCollection(admin_id=1))
db.session.flush()
stocktakings = [{
'product_id': 1,
'count': 100
}, {
'product_id': 2,
'count': 50
}, {
'product_id': 3,
'count': 25
}, {
'product_id': 4,
'count': 33
}]
for s in stocktakings:
db.session.add(Stocktaking(**s, collection_id=1))
# Manipulate first stocktaking timestamp
# First stocktaking: 01.01.2018
ts = datetime.strptime('2018-01-01 09:00:00', '%Y-%m-%d %H:%M:%S')
StocktakingCollection.query.filter_by(id=1).first().timestamp = ts
# Insert a purchase.
t = datetime.strptime('2018-03-05 09:00:00', '%Y-%m-%d %H:%M:%S')
db.session.add(
Purchase(user_id=1, product_id=1, amount=90, timestamp=t))
# Insert the second stocktaking.
stocktakings = [{
'product_id': 1,
'count': 0
}, {
'product_id': 2,
'count': 50
}, {
'product_id': 3,
'count': 25
}, {
'product_id': 4,
'count': 33
}]
t = datetime.strptime('2018-04-01 09:00:00', '%Y-%m-%d %H:%M:%S')
data = {'stocktakings': stocktakings, 'timestamp': int(t.timestamp())}
self.post(url='/stocktakingcollections', data=data, role='admin')
# Insert the third stocktaking. (Product 1 is not included)
stocktakings = [{
'product_id': 2,
'count': 50
}, {
'product_id': 3,
'count': 25
}, {
'product_id': 4,
'count': 33
}]
t = datetime.strptime('2018-04-02 09:00:00', '%Y-%m-%d %H:%M:%S')
data = {'stocktakings': stocktakings, 'timestamp': int(t.timestamp())}
self.post(url='/stocktakingcollections', data=data, role='admin')
# Query the stocktakings.
start = StocktakingCollection.query.filter_by(id=1).first()
end = StocktakingCollection.query.filter_by(id=3).first()
self.assertTrue(1 in [s.product_id for s in start.stocktakings])
self.assertFalse(1 in [s.product_id for s in end.stocktakings])
result = stocktaking_helpers._get_balance_between_stocktakings(
start, end)
self.assertTrue('products' in result)
products = result['products']
# Check if all products are in the balance
self.assertEqual({1, 2, 3, 4}, set(products.keys()))
# Check purchase count
self.assertEqual(products[1]['purchase_count'], 90)
self.assertEqual(products[2]['purchase_count'], 0)
self.assertEqual(products[3]['purchase_count'], 0)
self.assertEqual(products[4]['purchase_count'], 0)
# Check purchase sum price
self.assertEqual(products[1]['purchase_sum_price'], 27000)
self.assertEqual(products[2]['purchase_sum_price'], 0)
self.assertEqual(products[3]['purchase_sum_price'], 0)
self.assertEqual(products[4]['purchase_sum_price'], 0)
# Check replenish count
self.assertEqual(products[1]['replenish_count'], 0)
self.assertEqual(products[2]['replenish_count'], 0)
self.assertEqual(products[3]['replenish_count'], 0)
self.assertEqual(products[4]['replenish_count'], 0)
# Check differences
self.assertEqual(products[1]['difference'], -10)
self.assertEqual(products[2]['difference'], 0)
self.assertEqual(products[3]['difference'], 0)
self.assertEqual(products[4]['difference'], 0)
# Check balance
self.assertEqual(products[1]['balance'], -10 * 300)
self.assertEqual(products[2]['balance'], 0)
self.assertEqual(products[3]['balance'], 0)
self.assertEqual(products[4]['balance'], 0)
# Check overall balance
self.assertEqual(result['balance'], -3000)
self.assertEqual(result['loss'], 3000)
self.assertEqual(result['profit'], 0)
def test_balance_between_stocktakings_forgotten_purchases_inactive_products(
self):
"""
TODO
"""
# Since the start of this test is the same as the previous one, it
# can be run again to generate the required data.
self.test_balance_between_stocktakings_multiple_stocktakings()
# Ooops. We forgot to insert some two purchases (inactive products). Lets do it now (AFTER THIRD STOCKTAKING!)
self.assertFalse(Product.query.filter_by(id=4).first().active)
t = datetime.strptime('2018-04-02 09:00:00', '%Y-%m-%d %H:%M:%S')
db.session.add(Purchase(user_id=3, product_id=4, amount=2,
timestamp=t))
# Insert the fourth stocktaking.
t = datetime.strptime('2018-04-05 09:00:00', '%Y-%m-%d %H:%M:%S')
db.session.add(StocktakingCollection(admin_id=1, timestamp=t))
db.session.flush()
stocktakings = [{
'product_id': 1,
'count': 40
}, {
'product_id': 2,
'count': 5
}, {
'product_id': 3,
'count': 5
}, {
'product_id': 4,
'count': 0
}]
for s in stocktakings:
db.session.add(Stocktaking(collection_id=4, **s))
db.session.commit()
# Query the stocktakings.
start = StocktakingCollection.query.filter_by(id=1).first()
end = StocktakingCollection.query.filter_by(id=4).first()
result = stocktaking_helpers._get_balance_between_stocktakings(
start, end)
self.assertTrue('products' in result)
products = result['products']
# Check if all products are in the balance
self.assertEqual({1, 2, 3, 4}, set(products.keys()))
# Check purchase count
self.assertEqual(products[1]['purchase_count'], 9)
self.assertEqual(products[2]['purchase_count'], 15)
self.assertEqual(products[3]['purchase_count'], 13)
self.assertEqual(products[4]['purchase_count'], 2)
# Check purchase sum price
self.assertEqual(products[1]['purchase_sum_price'], 2700)
self.assertEqual(products[2]['purchase_sum_price'], 750)
self.assertEqual(products[3]['purchase_sum_price'], 1300)
self.assertEqual(products[4]['purchase_sum_price'], 400)
# Check replenish count
self.assertEqual(products[1]['replenish_count'], 10)
self.assertEqual(products[2]['replenish_count'], 0)
self.assertEqual(products[3]['replenish_count'], 5)
self.assertEqual(products[4]['replenish_count'], 0)
# Check differences
self.assertEqual(products[1]['difference'], -61)
self.assertEqual(products[2]['difference'], -30)
self.assertEqual(products[3]['difference'], -12)
self.assertEqual(products[4]['difference'], -31)
# Check balance
self.assertEqual(products[1]['balance'], -61 * 300)
self.assertEqual(products[2]['balance'], -30 * 50)
self.assertEqual(products[3]['balance'], -12 * 100)
self.assertEqual(products[4]['balance'], -31 * 200)
# Check overall balance
self.assertEqual(result['balance'], -27200)
self.assertEqual(result['loss'], 27200)
self.assertEqual(result['profit'], 0)
def test_balance_between_stocktakings_multiple_stocktakings(self):
"""
This test checks whether the calculation of the balance works
correctly over several stocktakings.
"""
# Since the start of this test is the same as the previous one, it
# can be run again to generate the required data.
self.test_balance_between_stocktakings_two_stocktakings()
# Insert some purchases between the second and the third stocktaking
t = datetime.strptime('2018-03-05 09:00:00', '%Y-%m-%d %H:%M:%S')
db.session.add(Purchase(user_id=1, product_id=1, amount=2,
timestamp=t))
t = datetime.strptime('2018-03-06 09:00:00', '%Y-%m-%d %H:%M:%S')
db.session.add(
Purchase(user_id=1, product_id=2, amount=10, timestamp=t))
t = datetime.strptime('2018-03-07 09:00:00', '%Y-%m-%d %H:%M:%S')
db.session.add(Purchase(user_id=2, product_id=3, amount=5,
timestamp=t))
t = datetime.strptime('2018-03-08 09:00:00', '%Y-%m-%d %H:%M:%S')
db.session.add(Purchase(user_id=3, product_id=1, amount=4,
timestamp=t))
# Insert the third stocktaking.
t = datetime.strptime('2018-04-01 09:00:00', '%Y-%m-%d %H:%M:%S')
db.session.add(StocktakingCollection(admin_id=1, timestamp=t))
db.session.flush()
stocktakings = [{
'product_id': 1,
'count': 40
}, {
'product_id': 2,
'count': 5
}, {
'product_id': 3,
'count': 5
}, {
'product_id': 4,
'count': 0
}]
for s in stocktakings:
db.session.add(Stocktaking(collection_id=3, **s))
# The count of product 4 is 0 so the product will be set inactive!
Product.query.filter_by(id=4).first().active = False
db.session.commit()
# Query the stocktakings.
start = StocktakingCollection.query.filter_by(id=1).first()
end = StocktakingCollection.query.filter_by(id=3).first()
# Timeline
# Date Event
#
# 01.01.2017 Purchase: 100 x Product 1
# 01.01.2017 Replenishment 1
# (Both are not taken into account for the calculation)
# -----------------------------------
# 01.01.2018 Stocktaking 1
# - Product 1: 100
# - Product 2: 50
# - Product 3: 25
# - Product 4: 33
#
# 01.02.2018 Purchase: 1 x Product 1
# 02.02.2018 Purchase: 5 x Product 2
# 03.02.2018 Purchase: 8 x Product 3
# 04.02.2018 Purchase: 2 x Product 1
# -----------------------------------
# 05.02.2018 Replenishment 2
# - Product 1: 10
# - Product 3: 5
#
# -----------------------------------
# 05.03.2018 Purchase: 2 x Product 1
# 06.03.2018 Purchase: 10 x Product 2
# 07.03.2018 Purchase: 5 x Product 3
# 08.03.2018 Purchase: 4 x Product 1
# -----------------------------------
# 01.04.2018 Stocktaking 3
# - Product 1: 40
# - Product 2: 5
# - Product 3: 5
# - Product 4: 0
# -----------------------------------
# Balance:
# Product 1: 100 (start) - 9 (purchase) + 10 (replenishment) = 101
# Product 2: 50 (start) - 15 (purchase) + 0 (replenishment) = 35
# Product 3: 25 (start) - 13 (purchase) + 5 (replenishment) = 17
# Product 4: 33 (start) - 0 (purchase) + 0 (replenishment) = 33
# -----------------------------------
# Loss between first and third stocktaking
# Product 1: 101 - 40 = 61
# Product 2: 35 - 5 = 30
# Product 3: 17 - 5 = 12
# Product 4: 33 - 0 = 33
result = stocktaking_helpers._get_balance_between_stocktakings(
start, end)
self.assertTrue('products' in result)
products = result['products']
# Check if all products are in the balance
self.assertEqual({1, 2, 3, 4}, set(products.keys()))
# Check purchase count
self.assertEqual(products[1]['purchase_count'], 9)
self.assertEqual(products[2]['purchase_count'], 15)
self.assertEqual(products[3]['purchase_count'], 13)
self.assertEqual(products[4]['purchase_count'], 0)
# Check purchase sum price
self.assertEqual(products[1]['purchase_sum_price'], 2700)
self.assertEqual(products[2]['purchase_sum_price'], 750)
self.assertEqual(products[3]['purchase_sum_price'], 1300)
self.assertEqual(products[4]['purchase_sum_price'], 0)
# Check replenish count
self.assertEqual(products[1]['replenish_count'], 10)
self.assertEqual(products[2]['replenish_count'], 0)
self.assertEqual(products[3]['replenish_count'], 5)
self.assertEqual(products[4]['replenish_count'], 0)
# Check differences
self.assertEqual(products[1]['difference'], -61)
self.assertEqual(products[2]['difference'], -30)
self.assertEqual(products[3]['difference'], -12)
self.assertEqual(products[4]['difference'], -33)
# Check balance
self.assertEqual(products[1]['balance'], -61 * 300)
self.assertEqual(products[2]['balance'], -30 * 50)
self.assertEqual(products[3]['balance'], -12 * 100)
self.assertEqual(products[4]['balance'], -33 * 200)
# Check overall balance
self.assertEqual(result['balance'], -27600)
self.assertEqual(result['loss'], 27600)
self.assertEqual(result['profit'], 0)
def create_stocktakingcollections(admin):
"""
Insert a new stocktakingcollection.
:param admin: Is the administrator user, determined by
@adminRequired.
:return: A message that the creation was successful.
:raises DataIsMissing: If not all required data is available.
:raises ForbiddenField : If a forbidden field is in the data.
:raises WrongType: If one or more data is of the wrong type.
:raises EntryNotFound: If the product with with the id of any
replenishment does not exist.
:raises InvalidAmount: If amount of any replenishment is less than
or equal to zero.
:raises CouldNotCreateEntry: If any other error occurs.
"""
data = json_body()
required = {'stocktakings': list, 'timestamp': int}
required_s = {'product_id': int, 'count': int}
optional_s = {'keep_active': bool}
# Check all required fields
check_fields_and_types(data, required)
stocktakings = data['stocktakings']
# Check for stocktakings in the collection
if not stocktakings:
raise exc.DataIsMissing()
for stocktaking in stocktakings:
product_id = stocktaking.get('product_id')
product = Product.query.filter_by(id=product_id).first()
if not product:
raise exc.EntryNotFound()
if not product.countable:
raise exc.InvalidData()
# Get all active product ids
products = (Product.query.filter(Product.active.is_(True)).filter(
Product.countable.is_(True)).all())
active_ids = list(map(lambda p: p.id, products))
data_product_ids = list(map(lambda d: d['product_id'], stocktakings))
# Compare function
def compare(x, y):
return collections.Counter(x) == collections.Counter(y)
# We need an entry for all active products. If some data is missing,
# raise an exception
if not compare(active_ids, data_product_ids):
raise exc.DataIsMissing()
# Check the timestamp
try:
timestamp = datetime.datetime.fromtimestamp(data['timestamp'])
assert timestamp <= datetime.datetime.now()
except (AssertionError, TypeError, ValueError, OSError, OverflowError):
# AssertionError: The timestamp is after the current time.
# TypeError: Invalid type for conversion.
# ValueError: Timestamp is out of valid range.
# OSError: Value exceeds the data type.
# OverflowError: Timestamp out of range for platform time_t.
raise exc.InvalidData()
# Create stocktakingcollection
collection = StocktakingCollection(admin_id=admin.id, timestamp=timestamp)
db.session.add(collection)
db.session.flush()
# Check for all required data and types
for stocktaking in stocktakings:
# Check all required fields
check_fields_and_types(stocktaking, required_s, optional_s)
# Get all fields
product_id = stocktaking.get('product_id')
count = stocktaking.get('count')
keep_active = stocktaking.get('keep_active', False)
# Check amount
if count < 0:
raise exc.InvalidAmount()
# Does the product changes its active state?
product = Product.query.filter_by(id=product_id).first()
if count == 0 and keep_active is False:
product.active = False
# Create and insert stocktakingcollection
try:
for stocktaking in stocktakings:
s = Stocktaking(collection_id=collection.id,
product_id=stocktaking.get('product_id'),
count=stocktaking.get('count'))
db.session.add(s)
db.session.commit()
except IntegrityError:
raise exc.CouldNotCreateEntry()
return jsonify({'message': 'Created stocktakingcollection.'}), 201