def _get_company_license_request_data(self, company_sn):
return_data = collections.OrderedDict()
if company_sn > 0:
sql_connector = PgConnector(self.service)
db_result = sql_connector.query(VcmsLicenseRequest). \
filter(VcmsLicenseRequest.company_sn == company_sn). \
filter(VcmsLicenseRequest.deprecated == 0). \
order_by(VcmsLicenseRequest.sn.desc())
for row in db_result:
return_data[row.sn] = {
"sn": row.sn,
"company_sn": row.company_sn,
"company_name": row.company_name,
"service_name": row.service_name,
"license_feature": row.license_feature,
"version": row.version,
"trial_type": row.trial_type,
"start_date": row.start_date,
"expire_date": row.expire_date,
"license_count": row.license_count,
"batch_license_count": row.batch_license_count,
"ct_user_sn": row.ct_user_sn,
"ut_user_sn": row.ut_user_sn,
"enabled": row.enabled,
"ct": str(row.ct),
"ut": str(row.ut)
}
sql_connector.get_session().get_bind().close()
return return_data
def _get_pkl_file_content_data(self, feature_pkl_sn):
sql_connector = PgConnector(self.service)
db_result = sql_connector.query(VcmsFeaturePklContent). \
filter(VcmsFeaturePklContent.feature_pkl_sn==feature_pkl_sn). \
filter(VcmsFeaturePklContent.enabled==1). \
filter(VcmsFeaturePklContent.deprecated==0). \
order_by(VcmsFeaturePklContent.sn)
return_data = collections.OrderedDict()
for row in db_result:
barcode = None
if row.barcode is not None:
barcode = row.barcode
return_data[row.sn] = {
"sn": row.sn,
"feature_pkl_sn": row.sn,
"company_sn": row.company_sn,
"service_sn": row.service_sn,
"branch_sn": row.branch_sn,
"pkl_key": row.pkl_key,
"image_sn": row.image_sn,
"sku": row.sku,
"feature_sn": row.feature_sn,
"product_sn": row.product_sn,
"product_name": row.product_name,
"barcode": barcode,
"feature": row.feature
}
sql_connector.get_session().get_bind().close()
return return_data
def _reset_feature_enabled_by_product_status(self, company_sn, service_sn):
sql_connector = PgConnector(self.service)
sql = """
update company_product_image_features
set enabled = 1
where product_sn in (SELECT sn FROM company_products where enabled = 1 and company_sn = %s and service_sn = %s) and
company_sn = %s and service_sn = %s;
update company_product_image_features
set enabled = 1
where image_sn in (SELECT sn FROM company_product_images where enabled = 1 and company_sn = %s and service_sn = %s) and
company_sn = %s and service_sn = %s;
update company_product_image_features
set enabled = 0
where product_sn in (SELECT sn FROM company_products where enabled = 0 and company_sn = %s and service_sn = %s) and
company_sn = %s and service_sn = %s;
update company_product_image_features
set enabled = 0
where image_sn in (SELECT sn FROM company_product_images where enabled = 0 and company_sn = %s and service_sn = %s) and
company_sn = %s and service_sn = %s;""" % (
company_sn, service_sn, company_sn, service_sn, company_sn,
service_sn, company_sn, service_sn, company_sn, service_sn,
company_sn, service_sn, company_sn, service_sn, company_sn,
service_sn)
sql_connector.execute_raw_sql(sql)
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return 0
def _get_product_image_csv_contents_for_creating_product_images(
self, product_image_csv_sn):
sql_connector = PgConnector(self.service)
db_result = sql_connector.query(VcmsCompanyProductImageCsvContent). \
filter(VcmsCompanyProductImageCsvContent.product_image_csv_sn==int(product_image_csv_sn)) . \
filter(VcmsCompanyProductImageCsvContent.image_created_time==None). \
filter(VcmsCompanyProductImageCsvContent.image_created_mark==0). \
filter(VcmsCompanyProductImageCsvContent.enabled==1). \
filter(VcmsCompanyProductImageCsvContent.deprecated==0). \
order_by(VcmsCompanyProductImageCsvContent.sn)
return_data = collections.OrderedDict()
for row in db_result:
return_data[row.sn] = {
"sn": row.sn,
"company_sn": row.company_sn,
"product_image_csv_sn": row.product_image_csv_sn,
"sku": row.sku,
"barcode": row.barcode,
"product_name": row.product_name,
"ori_file_path": row.ori_file_path,
"sys_file_path": row.sys_file_path,
"url": row.url,
"service_sn": row.service_sn,
"ct_user_sn": row.ct_user_sn
}
sql_connector.get_session().get_bind().close()
return return_data
def _get_product_data(self, company_sn, product_sn):
sql_connector = PgConnector(self.service)
db_result = sql_connector.query(VcmsCompanyProduct). \
filter(VcmsCompanyProduct.company_sn==int(company_sn)). \
filter(VcmsCompanyProduct.sn==int(product_sn)). \
filter(VcmsCompanyProduct.enabled==1). \
filter(VcmsCompanyProduct.deprecated==0). \
order_by(VcmsCompanyProduct.sn.desc())
return_data = collections.OrderedDict()
for row in db_result:
return_data = {
"sn": row.sn,
"company_sn": row.company_sn,
"service_sn": row.service_sn,
"barcode": row.barcode,
"sku": row.sku,
"product_name": row.product_name,
"abbreviation": row.abbreviation,
"thumbnail": row.thumbnail,
"image_totals": row.image_totals,
"ct_user_sn": row.ct_user_sn,
"ut_user_sn": row.ut_user_sn,
"enabled": row.enabled,
"ct": str(row.ct),
"deprecated": row.deprecated
}
sql_connector.get_session().get_bind().close()
return return_data
def _get_company_service_product_data(self, service_sn, search_key, limit,
offset):
sql_connector = PgConnector(self.service)
if search_key is None or search_key == "":
db_result = sql_connector.query(VcmsCompanyProduct). \
filter(VcmsCompanyProduct.service_sn==int(service_sn)). \
filter(VcmsCompanyProduct.deprecated==0). \
order_by(VcmsCompanyProduct.sn.desc()). \
limit(limit).offset(offset)
else:
db_result = sql_connector.query(VcmsCompanyProduct). \
filter(VcmsCompanyProduct.service_sn==int(service_sn)). \
filter(VcmsCompanyProduct.product_name==str(search_key)). \
filter(VcmsCompanyProduct.deprecated==0). \
order_by(VcmsCompanyProduct.sn.desc()). \
limit(limit).offset(offset)
return_data = collections.OrderedDict()
for row in db_result:
return_data[row.sn] = {
"sn": row.sn,
"company_sn": row.company_sn,
"service_sn": row.service_sn,
"barcode": row.barcode,
"sku": row.sku,
"product_name": row.product_name,
"abbreviation": row.abbreviation,
"thumbnail": row.thumbnail,
"image_totals": row.image_totals,
"ct_user_sn": row.ct_user_sn,
"ut_user_sn": row.ut_user_sn,
"enabled": row.enabled,
"ct": str(row.ct)
}
sql_connector.get_session().get_bind().close()
return return_data
def _get_company_service_data(self, company_sn):
return_data = collections.OrderedDict()
if company_sn > 0:
company_list = self._get_all_company_data()
sql_connector = PgConnector(self.service)
db_result = sql_connector.query(VcmsCompanyService). \
filter(VcmsCompanyService.company_sn==company_sn). \
order_by(VcmsCompanyService.sn.desc())
for row in db_result:
return_data[row.sn] = {
"sn": row.sn,
"company_service_sn": row.sn,
"company_sn": row.company_sn,
"company_name":
company_list[row.company_sn]["company_name"],
"system_service_sn": row.service_sn,
"per_product_image_cnt": row.per_product_image_cnt,
"service_sn": row.service_sn,
"max_product_cnt": row.max_product_cnt,
"min_training_cnt": row.min_training_cnt,
"ct_user_sn": row.ct_user_sn,
"ut_user_sn": row.ut_user_sn,
"enabled": row.enabled,
"ct": str(row.ct),
"deprecated": row.deprecated
}
sql_connector.get_session().get_bind().close()
return return_data
def _get_company_license_detail_data(self, sn):
return_data = collections.OrderedDict()
if sn > 0:
sql_connector = PgConnector(self.service)
db_result = sql_connector.query(VcmsLicense). \
filter(VcmsLicense.sn == sn). \
filter(VcmsLicense.deprecated == 0). \
order_by(VcmsLicense.sn.desc())
for row in db_result:
return_data[row.sn] = {
"sn": row.sn,
"license_key": row.license_key,
"generate_type": row.generate_type,
"request_sn": row.request_sn,
"company_sn": row.company_sn,
"company_name": row.company_name,
"encrypt_type": row.encrypt_type,
"license_feature": row.license_feature,
"version": row.version,
"trial_type": row.trial_type,
"id_type": row.id_type,
"hostid": row.hostid,
"start_date": row.start_date,
"expire_date": row.expire_date,
"connect_count": row.connect_count,
"server": row.server,
"port": row.port,
"ct_user_sn": row.ct_user_sn,
"ut_user_sn": row.ut_user_sn,
"enabled": row.enabled,
"ct": str(row.ct),
"ut": str(row.ut)
}
sql_connector.get_session().get_bind().close()
return return_data
def _set_file_manage_time_for_importing_products(self, sn):
ut = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql_connector = PgConnector(self.service)
rs = sql_connector.query(VcmsCompanyProductCsv). \
filter(VcmsCompanyProductCsv.sn==int(sn)). \
update({
"file_manage_time" : ut
})
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return rs
def _get_error_csv_content(self, csv_sn):
return_value = 0
sql_connector = PgConnector(self.service)
rs = sql_connector.query(VcmsCompanyProductCsvContent). \
filter(VcmsCompanyProductCsvContent.product_csv_sn==int(csv_sn)). \
filter(VcmsCompanyProductCsvContent.product_created_mark==0).count()
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
if rs > 0:
return_value = 1
return return_value
def _set_csv_enabled_for_importing_product_images(self, sn):
ut = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql_connector = PgConnector(self.service)
rs = sql_connector.query(VcmsCompanyProductImageCsv). \
filter(VcmsCompanyProductImageCsv.sn==int(sn)). \
update({
"enabled" : 1,
"ut" : ut
})
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return rs
def _check_product_by_sku(self, company_sn, service_sn, sku):
sql_connector = PgConnector(self.service)
db_result = sql_connector.query(VcmsCompanyProduct). \
filter(VcmsCompanyProduct.company_sn==int(company_sn)). \
filter(VcmsCompanyProduct.service_sn==int(service_sn)). \
filter(VcmsCompanyProduct.sku==str(sku)). \
filter(VcmsCompanyProduct.deprecated==0)
check_data = 0
for row in db_result:
check_data = 1
sql_connector.get_session().get_bind().close()
return check_data
def _set_result_for_features(self, sn, feature):
ut = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql_connector = PgConnector(self.service)
rs = sql_connector.query(VcmsCompanyProductImageFeature). \
filter(VcmsCompanyProductImageFeature.sn==int(sn)). \
update({
"feature" : feature,
"ut" : ut
})
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return rs
def _set_processing_time_for_detections(self, image_sn):
ut = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql_connector = PgConnector(self.service)
rs = sql_connector.query(VcmsCompanyProductImage). \
filter(VcmsCompanyProductImage.sn==int(image_sn)). \
update({
"detection_send_time" : ut,
"ut" : ut
})
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return rs
def _add_company_service_pkl_data(self, company_sn, service_sn, branch_sn,
pkl_key):
sql_connector = PgConnector(self.service)
pkl = VcmsFeaturePklFile(company_sn=company_sn,
service_sn=service_sn,
branch_sn=branch_sn,
pkl_key=pkl_key)
sql_connector.get_session().add(pkl)
sql_connector.get_session().commit()
pkl = pkl.sn
sql_connector.get_session().get_bind().close()
return pkl
def _get_company_service_product_cnt(self, service_sn, search_key):
sql_connector = PgConnector(self.service)
if search_key is None or search_key == "":
db_data_cnt = sql_connector.query(VcmsCompanyProduct). \
filter(VcmsCompanyProduct.service_sn==int(service_sn)). \
filter(VcmsCompanyProduct.deprecated==0).count()
else:
db_data_cnt = sql_connector.query(VcmsCompanyProduct). \
filter(VcmsCompanyProduct.service_sn==int(service_sn)). \
filter(VcmsCompanyProduct.product_name==str(search_key)). \
filter(VcmsCompanyProduct.deprecated==0).count()
sql_connector.get_session().get_bind().close()
return int(db_data_cnt)
def _set_product_created_mark_for_importing_products(self, sn):
ut = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql_connector = PgConnector(self.service)
rs = sql_connector.query(VcmsCompanyProductCsvContent). \
filter(VcmsCompanyProductCsvContent.sn==int(sn)). \
update({
"product_created_mark" : 1,
"product_created_time" : ut,
"ut" : ut
})
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return rs
def _set_output_mark_for_pkl_file(self, pkl_key):
output_pkl_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql_connector = PgConnector(self.service)
rs = sql_connector.query(VcmsFeaturePklFile). \
filter(VcmsFeaturePklFile.pkl_key==pkl_key). \
filter(VcmsFeaturePklFile.output_pkl_mark==0). \
update({
"output_pkl_mark" : 1,
"output_pkl_time" : output_pkl_time
})
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return rs
def _set_image_status_by_file_status(self, image_sn, enabled, deprecated):
sql_connector = PgConnector(self.service)
ut = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
rs = sql_connector.query(VcmsCompanyProductImage). \
filter(VcmsCompanyProductImage.sn==int(image_sn)). \
update({
"enabled" : int(enabled),
"ut" : ut,
"deprecated" : int(deprecated)
})
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return rs
def _set_csv_for_importing_products(self, company_sn, user_file_name,
sys_file_name, service_sn):
sql_connector = PgConnector(self.service)
csv = VcmsCompanyProductCsv(company_sn=company_sn,
user_file_name=user_file_name,
sys_file_name=sys_file_name,
service_sn=service_sn,
ct_user_sn=int(session["admin_id"]))
sql_connector.get_session().add(csv)
sql_connector.get_session().commit()
sn = csv.sn
sql_connector.get_session().get_bind().close()
return sn
def _reset_result_for_detections(self, image_sn):
ut = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql_connector = PgConnector(self.service)
rs = sql_connector.query(VcmsCompanyProductImageFeature). \
filter(VcmsCompanyProductImageFeature.image_sn==int(image_sn)). \
update({
"enabled" : 0,
"deprecated" : 1,
"ut" : ut
})
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return rs
def _set_finish_time_for_features(self, image_sn):
ut = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql_connector = PgConnector(self.service)
rs = sql_connector.query(VcmsCompanyProductImage). \
filter(VcmsCompanyProductImage.sn==int(image_sn)). \
update({
"feature_extraction_send_mark" : 1,
"feature_extraction_finish_mark" : 1,
"feature_extraction_finish_time" : ut,
"ut" : ut
})
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return rs
def _get_empty_main_product_image(self):
sql_connector = PgConnector(self.service)
db_result = sql_connector.query(VcmsCompanyProduct). \
filter(VcmsCompanyProduct.thumbnail==""). \
order_by(VcmsCompanyProduct.sn)
return_data = collections.OrderedDict()
for row in db_result:
return_data[row.sn] = {
"sn": row.sn,
"company_sn": row.company_sn,
"service_sn": row.service_sn
}
sql_connector.get_session().get_bind().close()
return return_data
def _del_company_service_pkl_data(self, company_sn, service_sn, branch_sn):
ut = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql_connector = PgConnector(self.service)
rs = sql_connector.query(VcmsFeaturePklFile). \
filter(VcmsFeaturePklFile.company_sn==int(company_sn)). \
filter(VcmsFeaturePklFile.service_sn==int(service_sn)). \
filter(VcmsFeaturePklFile.branch_sn==int(branch_sn)). \
update({
"enabled" : 0,
"ut" : ut
})
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return rs
def _set_product_csv_file(self, company_sn, user_file_name, sys_file_name,
service_sn, ct_user_sn, enabled):
sql_connector = PgConnector(self.service)
csv = VcmsCompanyProductCsv(company_sn=company_sn,
user_file_name=user_file_name,
sys_file_name=sys_file_name,
service_sn=service_sn,
ct_user_sn=ct_user_sn,
enabled=enabled)
sql_connector.get_session().add(csv)
sql_connector.get_session().commit()
sn = csv.sn
sql_connector.get_session().get_bind().close()
return sn
def set_pkl_upd_finish_mark_to_test_server(self, pkl_key):
pkl_update_finish_time = datetime.datetime.now().strftime(
"%Y-%m-%d %H:%M:%S")
sql_connector = PgConnector(self.service)
rs = sql_connector.query(VcmsFeaturePklFile). \
filter(VcmsFeaturePklFile.pkl_key==pkl_key). \
filter(VcmsFeaturePklFile.pkl_update_finish_mark==0). \
update({
"pkl_update_finish_mark" : 1,
"pkl_update_finish_time" : pkl_update_finish_time
})
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return rs
def _get_company_service_list(self, company_sn):
sql_connector = PgConnector(self.service)
db_result = sql_connector.query(VcmsCompanyService). \
filter(VcmsCompanyService.company_sn==company_sn). \
filter(VcmsCompanyService.enabled==1). \
filter(VcmsCompanyService.deprecated==0). \
order_by(VcmsCompanyService.sn.desc())
return_data = collections.OrderedDict()
for row in db_result:
return_data[row.sn] = {
"sysyem_service_sn": row.service_sn,
}
sql_connector.get_session().get_bind().close()
return return_data
def _reset_feature_by_product_image_status(self, company_sn, service_sn,
image_sn, enabled):
sql_connector = PgConnector(self.service)
ut = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
rs = sql_connector.query(VcmsCompanyProductImageFeature). \
filter(VcmsCompanyProductImageFeature.company_sn==int(company_sn)). \
filter(VcmsCompanyProductImageFeature.service_sn==int(service_sn)). \
filter(VcmsCompanyProductImageFeature.image_sn==int(image_sn)). \
update({
"enabled" : int(enabled),
"ut" : ut
})
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return rs
def _get_company_service_branch_product_enabled_status_data(
self, company_sn, service_sn, branch_sn, enabled):
sql_connector = PgConnector(self.service)
db_result = sql_connector.query(VcmsCompanyBranchProduct). \
filter(VcmsCompanyBranchProduct.company_sn==int(company_sn)). \
filter(VcmsCompanyBranchProduct.service_sn==int(service_sn)). \
filter(VcmsCompanyBranchProduct.branch_sn==int(branch_sn)). \
filter(VcmsCompanyBranchProduct.enabled==enabled). \
filter(VcmsCompanyBranchProduct.deprecated==0). \
order_by(VcmsCompanyBranchProduct.sn)
return_data = collections.OrderedDict()
for row in db_result:
return_data[row.product_sn] = row.product_sn
sql_connector.get_session().get_bind().close()
return return_data
def _reset_image_for_detections(self, image_sn):
ut = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql_connector = PgConnector(self.service)
rs = sql_connector.query(VcmsCompanyProductImage). \
filter(VcmsCompanyProductImage.sn==int(image_sn)). \
update({
"bbox_totals" : 0,
"detection_send_mark" : 0,
"detection_send_time" : None,
"detection_finish_mark" : 0,
"detection_finish_time" : None
})
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return rs