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 _reset_all_detection_feature_data(self):
sql_connector = PgConnector(self.service)
sql = """
TRUNCATE TABLE public.company_product_image_features;
ALTER SEQUENCE public.company_product_image_features_sn_seq RESTART 1;
UPDATE public.company_product_images
set bbox_totals = 0,
detection_send_mark = 0,
detection_send_time = null,
detection_finish_mark = 0,
detection_finish_time = null,
feature_extraction_send_mark = 0,
feature_extraction_send_time = null,
feature_extraction_finish_mark = 0,
feature_extraction_finish_time = null;
"""
sql_connector.execute_raw_sql(sql)
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return 0
def _reset_product_image_cnt(self):
sql_connector = PgConnector(self.service)
sql = """
select product_sn,
count(sn) as cnt
from company_product_images
where deprecated = 0
group by product_sn
"""
rs = sql_connector.execute_raw_sql(sql)
for row in rs:
cnt_sql = """
update company_products
set image_totals = '%s'
where sn = '%s'
""" % (row.cnt, row.product_sn)
sql_connector.execute_raw_sql(cnt_sql)
sql_connector.get_session().commit()
sql_connector.get_session().get_bind().close()
return 0
def _get_search_product_by_binding_feature(self, company_sn, service_sn,
feature_data):
sql_connector = PgConnector(self.service)
sql = "with"
i = 1
for row in feature_data:
sql += """
"%s" as (select company_product_image_features.product_sn as "b_%s"
from company_product_image_features
left join company_products on company_products.sn = company_product_image_features.product_sn
where company_product_image_features.company_sn = %s and
company_product_image_features.service_sn = %s and
company_product_image_features.enabled = 1 and
company_product_image_features.deprecated = 0 and
company_products.enabled = 1
order by cube(company_product_image_features.feature) <-> cube(array%s) limit 1),""" % (
i, i, company_sn, service_sn, row["feature"])
i += 1
str = ""
for j in range(1, i):
str += """ "%s",""" % (j)
final_sql = sql[:-1] + """
select * from %s""" % (str[:-1])
rs = sql_connector.execute_raw_sql(final_sql)
return_data = collections.OrderedDict()
for row in rs:
for j in range((i - 1)):
rs2 = self._get_product_data(company_sn, row[j])
if "product_name" in rs2:
return_data[(j + 1)] = {
"product_sn": row[j],
"product_name": rs2["product_name"],
"name": rs2["product_name"],
"sku": rs2["sku"],
"barcode": rs2["barcode"]
}
sql_connector.get_session().get_bind().close()
return return_data