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_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 _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 _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_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_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_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_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 _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_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 _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_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_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 _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 _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_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 _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 _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 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 _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_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 _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_all_company_data(self): sql_connector = PgConnector(self.service) db_result = sql_connector.query(VcmsCompany). \ filter(VcmsCompany.deprecated==0). \ order_by(VcmsCompany.sn.desc()) return_data = collections.OrderedDict() for row in db_result: return_data[row.sn] = { "sn": row.sn, "company_name": row.company_name, "service_list": self._get_company_service_data(int(row.sn)), "enabled": row.enabled } 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
def _set_main_product_image(self, company_sn, service_sn, product_sn, thumbnail): ut = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") sql_connector = PgConnector(self.service) rs = sql_connector.query(VcmsCompanyProduct). \ filter(VcmsCompanyProduct.company_sn==int(company_sn)). \ filter(VcmsCompanyProduct.service_sn==int(service_sn)). \ filter(VcmsCompanyProduct.sn==int(product_sn)). \ update({ "thumbnail" : thumbnail, "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 _get_prod_sku_mapping_data(self, company_sn, service_sn, sku, barcode, product_name): 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.barcode==str(barcode)). \ filter(VcmsCompanyProduct.product_name==str(product_name)). \ filter(VcmsCompanyProduct.enabled==1). \ order_by(VcmsCompanyProduct.sn) return_data = collections.OrderedDict() for row in db_result: return_data[row_sn] = row.sn sql_connector.get_session().get_bind().close() return return_data
def _get_download_error_csv_content(self, company_sn, service_sn, csv_sn): sql_connector = PgConnector(self.service) db_result = sql_connector.query(VcmsCompanyProductCsvContent). \ filter(VcmsCompanyProductCsvContent.company_sn==company_sn). \ filter(VcmsCompanyProductCsvContent.service_sn==service_sn). \ filter(VcmsCompanyProductCsvContent.product_csv_sn==int(csv_sn)). \ filter(VcmsCompanyProductCsvContent.product_created_mark==0). \ order_by(VcmsCompanyProductCsvContent.sn) return_data = collections.OrderedDict() for row in db_result: return_data[row.sn] = { "sku": row.sku, "barcode": row.barcode, "product_name": row.product_name, "message": "Duplicated" } sql_connector.get_session().get_bind().close() return return_data