def test_invalid_file(self):
valid_test_file = os.path.join(os.getcwd(), "tests", "data", "file_not_found.xlsx")
product_file = ProductsExcelImporter(valid_test_file)
with pytest.raises(Exception) as exinfo:
product_file.verify_file()
assert exinfo.match("No such file or directory:")
def test_import_with_internal_product_id(self):
"""Test the import of the internal product id column"""
test_value = "some custom data"
global CURRENT_PRODUCT_TEST_DATA
CURRENT_PRODUCT_TEST_DATA = pd.DataFrame([[
"Product A", "description of Product A", ".53", "USD",
"Cisco Systems", test_value
]],
columns=[
"product id",
"description",
"list price", "currency",
"vendor",
"internal product id"
])
user = User.objects.get(username="******")
product_file = ProductsExcelImporter("virtual_file.xlsx",
user_for_revision=user)
product_file.verify_file()
product_file.import_to_database()
assert Product.objects.count() == 1
# verify imported data
pa = Product.objects.get(product_id="Product A")
assert pa.internal_product_id is not None
assert pa.internal_product_id == test_value
def test_import_with_unknown_vendor(self):
"""test with vendor that doesn't exist"""
global CURRENT_PRODUCT_TEST_DATA
CURRENT_PRODUCT_TEST_DATA = pd.DataFrame(
[[
"Product A", "description of Product A", ".53", "USD",
"FooBar Unknown",
datetime.datetime(2016, 1, 1),
datetime.datetime(2016, 1, 2),
datetime.datetime(2016, 1, 3),
datetime.datetime(2016, 1, 4),
datetime.datetime(2016, 1, 5),
datetime.datetime(2016, 1, 6),
datetime.datetime(2016, 1, 7),
datetime.datetime(2016, 1, 8),
datetime.datetime(2016, 1, 9), "", ""
]],
columns=PRODUCTS_TEST_DATA_COLUMNS)
user = User.objects.get(username="******")
product_file = ProductsExcelImporter("virtual_file.xlsx",
user_for_revision=user)
product_file.verify_file()
with pytest.raises(Exception) as exinfo:
product_file.import_to_database()
assert exinfo.match("unknown vendor ")
def test_import_with_internal_product_id(self):
"""Test the import of the internal product id column"""
test_value = "some custom data"
global CURRENT_PRODUCT_TEST_DATA
CURRENT_PRODUCT_TEST_DATA = pd.DataFrame(
[
[
"Product A",
"description of Product A",
".53",
"USD",
"Cisco Systems",
test_value
]
], columns=[
"product id",
"description",
"list price",
"currency",
"vendor",
"internal product id"
]
)
user = User.objects.get(username="******")
product_file = ProductsExcelImporter(
"virtual_file.xlsx",
user_for_revision=user
)
product_file.verify_file()
product_file.import_to_database()
assert Product.objects.count() == 1
# verify imported data
pa = Product.objects.get(product_id="Product A")
assert pa.internal_product_id is not None
assert pa.internal_product_id == test_value
def test_import_with_list_price_without_leading_zero(self):
"""Should ensure that a list price of .xy is saved as 0.xy value, not None/Null value"""
global CURRENT_PRODUCT_TEST_DATA
CURRENT_PRODUCT_TEST_DATA = pd.DataFrame(
[
[
"Product A",
"description of Product A",
".53",
"USD",
"Cisco Systems",
datetime.datetime(2016, 1, 1),
datetime.datetime(2016, 1, 2),
datetime.datetime(2016, 1, 3),
datetime.datetime(2016, 1, 4),
datetime.datetime(2016, 1, 5),
datetime.datetime(2016, 1, 6),
datetime.datetime(2016, 1, 7),
datetime.datetime(2016, 1, 8),
datetime.datetime(2016, 1, 9),
]
], columns=PRODUCTS_TEST_DATA_COLUMNS
)
user = User.objects.get(username="******")
product_file = ProductsExcelImporter(
"virtual_file.xlsx",
user_for_revision=user
)
product_file.verify_file()
product_file.import_to_database()
assert Product.objects.count() == 1
# verify imported data
pa = Product.objects.get(product_id="Product A")
assert pa.list_price is not None
assert pa.list_price == 0.53
def prepare_import_products_excel_file(filename, verify_file=True, start_import=True):
valid_test_file = os.path.join(os.getcwd(), "tests", "data", filename)
product_file = ProductsExcelImporter(valid_test_file)
if verify_file:
product_file.verify_file()
if start_import:
product_file.import_to_database()
return product_file
def test_valid_import(self):
product_file = ProductsExcelImporter("virtual_file.xlsx")
assert product_file.is_valid_file() is False
product_file.verify_file()
assert product_file.is_valid_file() is True
product_file.import_to_database()
assert product_file.amount_of_products == 2
assert Product.objects.count() == 2
p = Product.objects.get(product_id="Product A")
assert p.description == "description of Product A"
assert p.list_price == 4000.0
assert p.currency == "USD"
assert p.vendor == Vendor.objects.get(id=1)
assert p.eox_update_time_stamp == datetime.date(2016, 1, 1)
assert p.eol_ext_announcement_date == datetime.date(2016, 1, 2)
assert p.end_of_sale_date == datetime.date(2016, 1, 3)
assert p.end_of_new_service_attachment_date == datetime.date(
2016, 1, 4)
assert p.end_of_sw_maintenance_date == datetime.date(2016, 1, 5)
assert p.end_of_routine_failure_analysis == datetime.date(2016, 1, 6)
assert p.end_of_service_contract_renewal == datetime.date(2016, 1, 7)
assert p.end_of_support_date == datetime.date(2016, 1, 8)
assert p.end_of_sec_vuln_supp_date == datetime.date(2016, 1, 9)
assert p.internal_product_id == "12345"
assert p.tags == "chassis"
p = Product.objects.get(product_id="Product B")
assert p.description == "description of Product B"
assert p.list_price == 6000.0
assert p.currency == "USD"
assert p.vendor == Vendor.objects.get(id=1)
assert p.eox_update_time_stamp is None
assert p.eol_ext_announcement_date is None
assert p.end_of_sale_date is None
assert p.end_of_new_service_attachment_date is None
assert p.end_of_sw_maintenance_date is None
assert p.end_of_routine_failure_analysis is None
assert p.end_of_service_contract_renewal is None
assert p.end_of_support_date is None
assert p.end_of_sec_vuln_supp_date is None
assert p.internal_product_id == ""
assert p.tags == ""
def test_import_with_different_vendors(self):
"""ensure that Product constraints are valid"""
global CURRENT_PRODUCT_TEST_DATA
CURRENT_PRODUCT_TEST_DATA = pd.DataFrame(
[
[
"Product A",
"description of Product A",
".53",
"USD",
"Cisco Systems",
datetime.datetime(2016, 1, 1),
datetime.datetime(2016, 1, 2),
datetime.datetime(2016, 1, 3),
datetime.datetime(2016, 1, 4),
datetime.datetime(2016, 1, 5),
datetime.datetime(2016, 1, 6),
datetime.datetime(2016, 1, 7),
datetime.datetime(2016, 1, 8),
datetime.datetime(2016, 1, 9),
"",
""
],
[
"Product A",
"description of Product A",
".53",
"USD",
"Juniper Networks",
datetime.datetime(2016, 1, 1),
datetime.datetime(2016, 1, 2),
datetime.datetime(2016, 1, 3),
datetime.datetime(2016, 1, 4),
datetime.datetime(2016, 1, 5),
datetime.datetime(2016, 1, 6),
datetime.datetime(2016, 1, 7),
datetime.datetime(2016, 1, 8),
datetime.datetime(2016, 1, 9),
"",
""
]
], columns=PRODUCTS_TEST_DATA_COLUMNS
)
user = User.objects.get(username="******")
product_file = ProductsExcelImporter(
"virtual_file.xlsx",
user_for_revision=user
)
product_file.verify_file()
product_file.import_to_database()
assert Product.objects.count() == 2
def test_valid_import(self):
product_file = ProductsExcelImporter("virtual_file.xlsx")
assert product_file.is_valid_file() is False
product_file.verify_file()
assert product_file.is_valid_file() is True
product_file.import_to_database()
assert product_file.amount_of_products == 2
assert Product.objects.count() == 2
p = Product.objects.get(product_id="Product A")
assert p.description == "description of Product A"
assert p.list_price == 4000.0
assert p.currency == "USD"
assert p.vendor == Vendor.objects.get(id=1)
assert p.eox_update_time_stamp == datetime.date(2016, 1, 1)
assert p.eol_ext_announcement_date == datetime.date(2016, 1, 2)
assert p.end_of_sale_date == datetime.date(2016, 1, 3)
assert p.end_of_new_service_attachment_date == datetime.date(2016, 1, 4)
assert p.end_of_sw_maintenance_date == datetime.date(2016, 1, 5)
assert p.end_of_routine_failure_analysis == datetime.date(2016, 1, 6)
assert p.end_of_service_contract_renewal == datetime.date(2016, 1, 7)
assert p.end_of_support_date == datetime.date(2016, 1, 8)
assert p.end_of_sec_vuln_supp_date == datetime.date(2016, 1, 9)
p = Product.objects.get(product_id="Product B")
assert p.description == "description of Product B"
assert p.list_price == 6000.0
assert p.currency == "USD"
assert p.vendor == Vendor.objects.get(id=1)
assert p.eox_update_time_stamp is None
assert p.eol_ext_announcement_date is None
assert p.end_of_sale_date is None
assert p.end_of_new_service_attachment_date is None
assert p.end_of_sw_maintenance_date is None
assert p.end_of_routine_failure_analysis is None
assert p.end_of_service_contract_renewal is None
assert p.end_of_support_date is None
assert p.end_of_sec_vuln_supp_date is None
def test_valid_import_with_revision_user(self):
global CURRENT_PRODUCT_TEST_DATA
CURRENT_PRODUCT_TEST_DATA = DEFAULT_PRODUCT_TEST_DATA
user = User.objects.get(username="******")
product_file = ProductsExcelImporter(
"virtual_file.xlsx",
user_for_revision=user
)
product_file.verify_file()
product_file.import_to_database()
assert Product.objects.count() == 2
# verify reversion comment
versions = Version.objects.all()
assert len(versions) == 2, "Should be two reversion"
assert "manual product import" == versions.first().revision.comment
assert user == versions.first().revision.user
def test_import_with_list_price_without_leading_zero(self):
"""Should ensure that a list price of .xy is saved as 0.xy value, not None/Null value"""
global CURRENT_PRODUCT_TEST_DATA
CURRENT_PRODUCT_TEST_DATA = pd.DataFrame(
[
[
"Product A",
"description of Product A",
".53",
"USD",
"Cisco Systems",
datetime.datetime(2016, 1, 1),
datetime.datetime(2016, 1, 2),
datetime.datetime(2016, 1, 3),
datetime.datetime(2016, 1, 4),
datetime.datetime(2016, 1, 5),
datetime.datetime(2016, 1, 6),
datetime.datetime(2016, 1, 7),
datetime.datetime(2016, 1, 8),
datetime.datetime(2016, 1, 9),
"",
""
]
], columns=PRODUCTS_TEST_DATA_COLUMNS
)
user = User.objects.get(username="******")
product_file = ProductsExcelImporter(
"virtual_file.xlsx",
user_for_revision=user
)
product_file.verify_file()
product_file.import_to_database()
assert Product.objects.count() == 1
# verify imported data
pa = Product.objects.get(product_id="Product A")
assert pa.list_price is not None
assert pa.list_price == 0.53
def import_price_list(self, job_file_id, create_notification_on_server=True, update_only=False, user_for_revision=None):
"""
import products from the given price list
:param job_file_id: ID within the database that references the Excel file that should be imported
:param create_notification_on_server: create a new Notification Message on the Server
:param update_only: Don't create new products in the database, update only existing ones
:param user_for_revision: username that should be used for the revision tracking (only if started manually)
"""
def update_task_state(status_message):
"""Update the status message of the task, which is displayed in the watch view"""
self.update_state(state=TaskState.PROCESSING, meta={
"status_message": status_message
})
update_task_state("Try to import uploaded file...")
try:
import_excel_file = JobFile.objects.get(id=job_file_id)
except:
msg = "Cannot find file that was uploaded."
logger.error(msg, exc_info=True)
result = {
"error_message": msg
}
return result
# verify that file exists
try:
import_products_excel = ProductsExcelImporter(
path_to_excel_file=import_excel_file.file,
user_for_revision=User.objects.get(username=user_for_revision)
)
import_products_excel.verify_file()
update_task_state("File valid, start updating the database...")
import_products_excel.import_to_database(status_callback=update_task_state, update_only=update_only)
update_task_state("Database import finished, processing results...")
summary_msg = "User <strong>%s</strong> imported a Product list, %s Products " \
"changed." % (user_for_revision, import_products_excel.valid_imported_products)
detail_msg = "<div style=\"text-align:left;\">%s " \
"Products successful updated. " % import_products_excel.valid_imported_products
if import_products_excel.invalid_products != 0:
detail_msg += "%s entries are invalid. Please check the following messages for " \
"more details." % import_products_excel.invalid_products
if len(import_products_excel.import_result_messages) != 0:
detail_msg += "<ul>"
for e in import_products_excel.import_result_messages:
detail_msg += "<li>%s</li>" % e
detail_msg += "</ul></div>"
# if the task was executed eager, set state to SUCCESS (required for testing)
if self.request.is_eager:
self.update_state(state=TaskState.SUCCESS, meta={
"status_message": detail_msg
})
if create_notification_on_server:
NotificationMessage.objects.create(
title="Import product list",
type=NotificationMessage.MESSAGE_INFO,
summary_message=summary_msg,
detailed_message=detail_msg
)
# drop the file
import_excel_file.delete()
result = {
"status_message": detail_msg
}
except (InvalidImportFormatException, InvalidExcelFileFormat) as ex:
msg = "import failed, invalid file format (%s)" % ex
logger.error(msg, ex)
result = {
"error_message": msg
}
except Exception as ex: # catch any exception
msg = "Unexpected exception occurred while importing product list (%s)" % ex
logger.error(msg, ex)
result = {
"error_message": msg
}
# if the task was executed eager, set state to SUCCESS (required for testing)
if self.request.is_eager:
self.update_state(state=TaskState.SUCCESS, meta=result)
return result
def test_import_with_list_price_of_zero(self):
"""Should ensure that a list price of 0 is saved as 0 value, not None/Null value"""
global CURRENT_PRODUCT_TEST_DATA
CURRENT_PRODUCT_TEST_DATA = pd.DataFrame(
[
[
"Product A",
"description of Product A",
"0",
"USD",
"Cisco Systems",
datetime.datetime(2016, 1, 1),
datetime.datetime(2016, 1, 2),
datetime.datetime(2016, 1, 3),
datetime.datetime(2016, 1, 4),
datetime.datetime(2016, 1, 5),
datetime.datetime(2016, 1, 6),
datetime.datetime(2016, 1, 7),
datetime.datetime(2016, 1, 8),
datetime.datetime(2016, 1, 9),
],
[
"Product B",
"description of Product B",
"0.00",
"USD",
"Cisco Systems",
datetime.datetime(2016, 1, 1),
datetime.datetime(2016, 1, 2),
datetime.datetime(2016, 1, 3),
datetime.datetime(2016, 1, 4),
datetime.datetime(2016, 1, 5),
datetime.datetime(2016, 1, 6),
datetime.datetime(2016, 1, 7),
datetime.datetime(2016, 1, 8),
datetime.datetime(2016, 1, 9),
],
[
"Product A2",
"description of Product A",
0,
"USD",
"Cisco Systems",
datetime.datetime(2016, 1, 1),
datetime.datetime(2016, 1, 2),
datetime.datetime(2016, 1, 3),
datetime.datetime(2016, 1, 4),
datetime.datetime(2016, 1, 5),
datetime.datetime(2016, 1, 6),
datetime.datetime(2016, 1, 7),
datetime.datetime(2016, 1, 8),
datetime.datetime(2016, 1, 9),
],
[
"Product B2",
"description of Product B",
0.00,
"USD",
"Cisco Systems",
datetime.datetime(2016, 1, 1),
datetime.datetime(2016, 1, 2),
datetime.datetime(2016, 1, 3),
datetime.datetime(2016, 1, 4),
datetime.datetime(2016, 1, 5),
datetime.datetime(2016, 1, 6),
datetime.datetime(2016, 1, 7),
datetime.datetime(2016, 1, 8),
datetime.datetime(2016, 1, 9),
],
[
"Product C",
"description of Product C",
"",
"USD",
"Cisco Systems",
datetime.datetime(2016, 1, 1),
datetime.datetime(2016, 1, 2),
datetime.datetime(2016, 1, 3),
datetime.datetime(2016, 1, 4),
datetime.datetime(2016, 1, 5),
datetime.datetime(2016, 1, 6),
datetime.datetime(2016, 1, 7),
datetime.datetime(2016, 1, 8),
datetime.datetime(2016, 1, 9),
]
], columns=PRODUCTS_TEST_DATA_COLUMNS
)
user = User.objects.get(username="******")
product_file = ProductsExcelImporter(
"virtual_file.xlsx",
user_for_revision=user
)
product_file.verify_file()
product_file.import_to_database()
assert Product.objects.count() == 5
# verify imported data
pa = Product.objects.get(product_id="Product A")
assert pa.list_price is not None
assert pa.list_price == 0.00
pa2 = Product.objects.get(product_id="Product A2")
assert pa2.list_price is not None
assert pa2.list_price == 0.00
pb = Product.objects.get(product_id="Product B")
assert pb.list_price is not None
assert pb.list_price == 0.00
pb2 = Product.objects.get(product_id="Product B2")
assert pb2.list_price is not None
assert pb2.list_price == 0.00
pc = Product.objects.get(product_id="Product C")
assert pc.list_price is None, "No list price provided, therefore it should be None"
def import_price_list(self, job_file_id, create_notification_on_server=True, update_only=False, user_for_revision=None):
"""
import products from the given price list
:param job_file_id: ID within the database that references the Excel file that should be imported
:param create_notification_on_server: create a new Notification Message on the Server
:param update_only: Don't create new products in the database, update only existing ones
:param user_for_revision: username that should be used for the revision tracking (only if started manually)
"""
def update_task_state(status_message):
"""Update the status message of the task, which is displayed in the watch view"""
self.update_state(state=TaskState.PROCESSING, meta={
"status_message": status_message
})
update_task_state("Try to import uploaded file...")
try:
import_excel_file = JobFile.objects.get(id=job_file_id)
except:
msg = "Cannot find file that was uploaded."
logger.error(msg, exc_info=True)
result = {
"error_message": msg
}
return result
# verify that file exists
try:
import_products_excel = ProductsExcelImporter(
path_to_excel_file=import_excel_file.file,
user_for_revision=User.objects.get(username=user_for_revision)
)
import_products_excel.verify_file()
update_task_state("File valid, start updating the database...")
import_products_excel.import_to_database(status_callback=update_task_state, update_only=update_only)
update_task_state("Database import finished, processing results...")
summary_msg = "User <strong>%s</strong> imported a Product list, %s Products " \
"changed." % (user_for_revision, import_products_excel.valid_imported_products)
detail_msg = "<div style=\"text-align:left;\">%s " \
"Products successful updated. " % import_products_excel.valid_imported_products
if import_products_excel.invalid_products != 0:
detail_msg += "%s entries are invalid. Please check the following messages for " \
"more details." % import_products_excel.invalid_products
if len(import_products_excel.import_result_messages) != 0:
detail_msg += "<ul>"
for e in import_products_excel.import_result_messages:
detail_msg += "<li>%s</li>" % e
detail_msg += "</ul></div>"
# if the task was executed eager, set state to SUCCESS (required for testing)
if self.request.is_eager:
self.update_state(state=TaskState.SUCCESS, meta={
"status_message": detail_msg
})
if create_notification_on_server:
NotificationMessage.objects.create(
title="Import product list",
type=NotificationMessage.MESSAGE_INFO,
summary_message=summary_msg,
detailed_message=detail_msg
)
# drop the file
import_excel_file.delete()
result = {
"status_message": detail_msg
}
except (InvalidImportFormatException, InvalidExcelFileFormat) as ex:
msg = "import failed, invalid file format (%s)" % ex
logger.error(msg, ex)
result = {
"error_message": msg
}
except Exception as ex: # catch any exception
msg = "Unexpected exception occurred while importing product list (%s)" % ex
logger.error(msg, ex)
result = {
"error_message": msg
}
# if the task was executed eager, set state to SUCCESS (required for testing)
if self.request.is_eager:
self.update_state(state=TaskState.SUCCESS, meta=result)
return result
