def link_partcode_model_analogs(model_id, partcode_id):
db.i_request(
f"WITH s as (SELECT 1 FROM link_partcode_model_analogs "
f"WHERE model_id = {model_id} AND partcode_id = {partcode_id}), "
f"i as (INSERT INTO link_partcode_model_analogs (model_id, partcode_id) "
f"SELECT {model_id}, {partcode_id} WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING 0) "
f"SELECT * FROM i UNION ALL SELECT * FROM s")
def link_models_spr_details(model_id, spr_detail_id):
db.i_request(
f"WITH s as (SELECT id FROM details "
f"WHERE model_id = {model_id} AND spr_detail_id = {spr_detail_id}), i as "
f"(INSERT INTO details (model_id, spr_detail_id) SELECT {model_id}, {spr_detail_id} "
f"WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING id) SELECT id FROM i UNION ALL SELECT id FROM s"
)
def link_model_supplies(model_id, supplies_id):
db.i_request(
f"WITH s as (SELECT 1 FROM link_model_supplies "
f"WHERE model_id = {model_id} AND supplies_id = {supplies_id}), i as "
f"(INSERT INTO link_model_supplies (model_id, supplies_id) "
f"SELECT {model_id}, {supplies_id} "
f"WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING 0) SELECT * FROM i UNION ALL SELECT * FROM s"
)
def link_detail_options(detail_id, detail_option_id):
db.i_request(
f"WITH s as (SELECT 1 FROM link_details_options "
f"WHERE detail_id = {detail_id} AND detail_option_id = {detail_option_id}), i as "
f"(INSERT INTO link_details_options (detail_id, detail_option_id) "
f"SELECT {detail_id}, {detail_option_id} "
f"WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING 0) select * from i union all select * from s"
)
def insert_dict_partcode(code_name_en, code_name_ru):
if code_name_en:
q = db.i_request(f"WITH s as (SELECT id FROM dictionary_partcode "
f"WHERE LOWER(name_en) = LOWER('{code_name_en}')), i as "
f"(INSERT INTO dictionary_partcode (name_en, name_ru) SELECT '{code_name_en}', '{code_name_ru}' "
f"WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING id) SELECT id FROM i UNION ALL SELECT id FROM s")
else:
q = db.i_request(f"WITH s as (SELECT id FROM dictionary_partcode "
f"WHERE LOWER(name_ru) = LOWER('{code_name_ru}')), i as "
f"(INSERT INTO dictionary_partcode (name_ru) SELECT '{code_name_ru}' "
f"WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING id) SELECT id FROM i UNION ALL SELECT id FROM s")
return q[0][0]
def get_brand_id(brand):
q = db.i_request(
f'SELECT id FROM brands WHERE LOWER(name) = LOWER(\'{brand}\')')
if q:
return q[0][0]
else:
return 0
def check_supplies(model_id):
q = db.i_request(
f'SELECT * FROM all_cartridge WHERE {model_id} = ANY(model_id)')
if q:
return True
else:
return False
def get_brand_id(brand_name):
# SELECT * FROM models WHERE regexp_replace(name,'-','','g') ~* 'Brother HL1030';
q = db.i_request(f"SELECT id FROM brands WHERE LOWER(name) = LOWER('{brand_name}')")
if q:
return q[0][0]
else:
return 0
def insert_partcodes(code, pn_id, brand_id):
q = db.i_request(f"WITH s as (SELECT id FROM partcodes "
f"WHERE LOWER(code) = LOWER('{code}')), i as "
f"(INSERT INTO partcodes (code, manufacturer, dictionary_partcode_id, supplies) "
f"SELECT '{code}', {brand_id}, {pn_id}, TRUE "
f"WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING id) SELECT id FROM i UNION ALL SELECT id FROM s")
return q[0][0]
def check_parts(model_id):
q = db.i_request(
f'SELECT * FROM all_partcatalog WHERE model_id = {model_id} and partcode is not null'
)
if q:
return True
else:
return False
def check_errors(model_id):
q = db.i_request(
f'SELECT * FROM all_errors WHERE mid = {model_id} and code is not null'
)
if q:
return True
else:
return False
def check_options(model_id):
q = db.i_request(
f'SELECT * FROM all_options_model WHERE '
f'detail_id = (SELECT id FROM details WHERE model_id = {model_id} and partcode_id '
f'is null ORDER BY id LIMIT 1)')
if q:
return True
else:
return False
def insert_dictionary_partcode_options(text):
q = db.i_request(f'WITH s as (SELECT id FROM dictionary_partcode_options '
f'WHERE text_ru = \'{text}\'), i as (INSERT INTO dictionary_partcode_options (text_ru) '
f'SELECT \'{text}\' WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING id) '
f'SELECT id FROM i UNION ALL SELECT id FROM s')
if q:
return q[0][0]
else:
return 0
def insert_supplies_analog_model(brand_id, model):
q = db.i_request(f'WITH s as (SELECT id FROM supplies_analog_model '
f'WHERE brand_id = {brand_id} AND model = \'{model}\'), '
f'i as (INSERT INTO supplies_analog_model (brand_id, model) '
f'SELECT {brand_id}, \'{model}\' WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING id) '
f'SELECT id FROM i UNION ALL SELECT id FROM s')
if q:
return q[0][0]
else:
return 0
def get_model_id(model):
# SELECT * FROM models WHERE regexp_replace(name,'-','','g') ~* 'Brother HL1030';
# q = db.i_request(f"SELECT id FROM models WHERE LOWER(name) "
# f"SIMILAR TO LOWER(concat('%' || regexp_replace('{model}', '-', '', 'g') || '%'))")
q = db.i_request(
f"SELECT id FROM models WHERE LOWER(name) = LOWER('{model}')")
if q:
return q[0][0]
else:
return 0
def test():
q = db.i_request(
f"SELECT sd.name as detail_name, sd.name_ru as detail_name_ru, p.code as partcode, "
f"mu.name as module_name, mo.name as model_name, mo.main_image model_image, "
f"p.images partcode_image FROM details d "
f"LEFT JOIN partcodes p ON d.partcode_id = p.id "
f"LEFT JOIN spr_details sd ON sd.id = d.spr_detail_id "
f"LEFT JOIN spr_modules mu ON d.module_id=mu.id LEFT JOIN models mo ON d.model_id=mo.id "
f"WHERE sd.name %> 'cover'")
return q
def insert_detail_options(caption_spr_id, detail_option_spr_id):
q = db.i_request(
f"WITH s as (SELECT id FROM detail_options WHERE "
f"caption_spr_id = {caption_spr_id} AND detail_option_spr_id = {detail_option_spr_id}), "
f"i as (INSERT INTO detail_options (caption_spr_id, detail_option_spr_id, parent_id) "
f"SELECT {caption_spr_id}, {detail_option_spr_id}, 1 "
f"WHERE NOT EXISTS (SELECT 1 FROM s) returning id) SELECT id FROM i UNION ALL SELECT id FROM s"
)
return q[0][0]
def get_option_id(name):
q = db.i_request(
f"WITH s as (SELECT id FROM spr_detail_options WHERE name = '{name}'), "
f"i as (INSERT INTO spr_detail_options (name) SELECT '{name}' "
f"WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING id) SELECT * FROM i UNION ALL SELECT * FROM s"
)
if q:
return q[0][0]
else:
return 0
def link_cartridge_options(dictionary_partcode_caption_id, dictionary_partcode_option_id):
q = db.i_request(f'WITH s as (SELECT id FROM link_dictionary_partcode_options '
f'WHERE dictionary_partcode_caption_id = {dictionary_partcode_caption_id} AND '
f'dictionary_partcode_option_id = {dictionary_partcode_option_id}), '
f'i as (INSERT INTO link_dictionary_partcode_options (dictionary_partcode_caption_id, dictionary_partcode_option_id) '
f'SELECT {dictionary_partcode_caption_id}, {dictionary_partcode_option_id} WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING id) '
f'SELECT id FROM i UNION ALL SELECT id FROM s')
if q:
return q[0][0]
else:
return 0
def insert_spr_modules(module_name):
q = db.i_request(
f"WITH s as (SELECT id FROM spr_modules "
f"WHERE LOWER(name_ru) = LOWER('{module_name}')), i as "
f"(INSERT INTO spr_modules (name_ru) SELECT '{module_name}' "
f"WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING id) SELECT id FROM i UNION ALL SELECT id FROM s"
)
if q:
return q[0][0]
else:
return 0
def insert_partcodes(partcode):
q = db.i_request(
f"WITH s as (SELECT id FROM partcodes "
f"WHERE LOWER(code) = LOWER('{partcode}')), i as "
f"(INSERT INTO partcodes (code) SELECT '{partcode}' "
f"WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING id) SELECT id FROM i UNION ALL SELECT id FROM s"
)
if q:
return q[0][0]
else:
return 0
def insert_detail_spr_details(detail):
q = db.i_request(
f"WITH s as (SELECT id FROM spr_details "
f"WHERE LOWER(name_ru) SIMILAR TO "
f"LOWER(concat('%' || '{detail}' || '%'))), i as "
f"(INSERT INTO spr_details (name_ru) SELECT '{detail}' "
f"WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING id) SELECT id FROM i UNION ALL SELECT id FROM s"
)
if q:
return q[0][0]
else:
return 0
def insert_model_spr_details(model):
q = db.i_request(
f"WITH s as (SELECT id FROM spr_details "
f"WHERE LOWER(name) SIMILAR TO "
f"LOWER(concat('%' || regexp_replace('{model}', '-', '', 'g') || '%'))), i as "
f"(INSERT INTO spr_details (name) SELECT '{model}' "
f"WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING id) SELECT id FROM i UNION ALL SELECT id FROM s"
)
if q:
return q[0][0]
else:
return 0
def insert_model(model, brand_id):
q = db.i_request(
f"WITH s as (SELECT id FROM models "
f"WHERE LOWER(concat('%' || regexp_replace(name,'-','','g') || '%')) SIMILAR TO "
f"LOWER({model})), i as "
f"(INSERT INTO models (name, brand_id) SELECT '{model}', {brand_id} "
f"WHERE NOT EXISTS (SELECT 1 FROM s) RETURNING id) SELECT id FROM i UNION ALL SELECT id FROM s"
)
if q:
return q[0][0]
else:
return 0
def link_part_model_module_spr(partcode_id, model_id, module_id,
spr_detail_id):
q = db.i_request(
f"WITH s as (SELECT 1 FROM details "
f"WHERE partcode_id = {partcode_id} AND model_id = {model_id} AND module_id = {module_id} AND "
f"spr_detail_id = {spr_detail_id}), i as "
f"(INSERT INTO details (partcode_id, model_id, module_id, spr_detail_id) "
f"SELECT {partcode_id}, {model_id}, {module_id}, {spr_detail_id} WHERE NOT EXISTS "
f"(SELECT 1 FROM s) RETURNING 0) SELECT * FROM i UNION ALL SELECT * FROM s"
)
if q:
return q[0][0]
else:
return 0
def update_dict_partcode(dict_partcode_id, code_name_ru):
db.i_request(f"UPDATE dictionary_partcode SET name_ru = '{code_name_ru}' "
f"WHERE id = {dict_partcode_id}")
def link_supplies_analog(supplies_id, supplies_analog_id):
db.i_request(f'INSERT INTO link_supplies_analog (supplies_id, supplies_analog_id) '
f'VALUES({supplies_id}, {supplies_analog_id})')
def link_partcode_options(partcode_id, link_id):
db.i_request(f"INSERT INTO link_partcode_options (partcode_option_id, partcode_dictionary_id) VALUES ({link_id}, {partcode_id})")
def get_dict_partcode_id(code_name_en):
q = db.i_request(f"SELECT id FROM dictionary_partcode WHERE name_en = '{code_name_en}'")
if q:
return q[0][0]
def link_supplies_model_analog(partcode_id, supplies_analog_model_id):
db.i_request(f'INSERT INTO link_supplies_model_analog (partcode_id, supplies_analog_model_id) '
f'VALUES({partcode_id}, {supplies_analog_model_id})')
