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})')