Created
June 2, 2015 20:07
-
-
Save polymorphm/cdb3787fa8bc65c37910 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/env python3 | |
| # -*- mode: python; coding: utf-8 -*- | |
| assert str is not bytes | |
| import sys | |
| import xml.etree.ElementTree as ET | |
| import itertools | |
| CATEGORY_COLUMN_FIELD = 1 | |
| STORE_ID_FIELD = 0 | |
| LAYOUT_ID_FIELD = 0 | |
| LANGUAGE_ID_FIELD = 1 | |
| PRODUCT_STOCK_STATUS_ID_FIELD = 7 | |
| class ConvertError(Exception): | |
| pass | |
| def id_by_guid_func_create(id_iter, id_by_guid_map): | |
| def func(guid): | |
| try: | |
| id = id_by_guid_map[guid] | |
| except KeyError: | |
| pass | |
| else: | |
| return id | |
| id = next(id_iter) | |
| id_by_guid_map[guid] = id | |
| return id | |
| return func | |
| def sql_quote(s): | |
| assert not isinstance(s, bytes) | |
| if str is None: | |
| s = '' | |
| if not isinstance(s, str): | |
| s = str(s) | |
| s = s.replace('\\', '\\\\') | |
| s = s.replace('\'', '\\\'') | |
| s = '\'{}\''.format(s) | |
| return s | |
| def convert(begin_id, xml_fd, sql_fd): | |
| tree = ET.parse(xml_fd) | |
| root_el = tree.getroot() | |
| if root_el.tag != 'КоммерческаяИнформация': | |
| raise ConvertError | |
| group_el_list = [] | |
| product_el_list = [] | |
| group_meta_list = [] | |
| group_meta_by_guid_map = {} | |
| product_meta_list = [] | |
| category_id_iter = itertools.count(start=begin_id) | |
| product_id_iter = itertools.count(start=begin_id) | |
| category_id_by_guid_map = {None: 0} | |
| product_id_by_guid_map = {None: 0} | |
| category_id_by_guid = id_by_guid_func_create(category_id_iter, category_id_by_guid_map) | |
| product_id_by_guid = id_by_guid_func_create(product_id_iter, product_id_by_guid_map) | |
| for child1_el in root_el: | |
| for child2_el in child1_el: | |
| for child3_el in child2_el: | |
| if child1_el.tag == 'Классификатор' and \ | |
| child2_el.tag == 'Группы' and \ | |
| child3_el.tag == 'Группа': | |
| group_el_list.append((child3_el, None)) | |
| if child1_el.tag == 'Каталог' and \ | |
| child2_el.tag == 'Товары' and \ | |
| child3_el.tag == 'Товар': | |
| product_el_list.append(child3_el) | |
| scheduled_el_list = group_el_list | |
| while scheduled_el_list: | |
| new_scheduled_el_list = [] | |
| for child1_el, parent_guid in scheduled_el_list: | |
| guid = None | |
| name = None | |
| for child2_el in child1_el: | |
| if guid is None and child2_el.tag == 'Ид': | |
| guid = child2_el.text | |
| elif name is None and child2_el.tag == 'Наименование': | |
| name = child2_el.text | |
| if guid is None or name is None: | |
| continue | |
| group_meta = { | |
| 'guid': guid, | |
| 'parent_guid': parent_guid, | |
| 'category_id': category_id_by_guid(guid), | |
| 'parent_category_id': category_id_by_guid(parent_guid), | |
| 'name': name, | |
| } | |
| group_meta_list.append(group_meta) | |
| group_meta_by_guid_map[guid] = group_meta | |
| for child2_el in child1_el: | |
| for child3_el in child2_el: | |
| if child2_el.tag == 'Группы' and \ | |
| child3_el.tag == 'Группа': | |
| new_scheduled_el_list.append((child3_el, guid)) | |
| scheduled_el_list = new_scheduled_el_list | |
| for child1_el in product_el_list: | |
| guid = None | |
| barcode = None | |
| name = None | |
| groups = [] | |
| for child2_el in child1_el: | |
| if guid is None and child2_el.tag == 'Ид': | |
| guid = child2_el.text | |
| elif barcode is None and child2_el.tag == 'Штрихкод': | |
| barcode = child2_el.text | |
| elif name is None and child2_el.tag == 'Наименование': | |
| name = child2_el.text | |
| for child3_el in child2_el: | |
| if child2_el.tag == 'Группы' and \ | |
| child3_el.tag == 'Ид': | |
| groups.append(child3_el.text) | |
| if guid is None or name is None: | |
| continue | |
| product_meta = { | |
| 'guid': guid, | |
| 'product_id': product_id_by_guid(guid), | |
| 'barcode': barcode, | |
| 'name': name, | |
| 'groups': groups, | |
| } | |
| product_meta_list.append(product_meta) | |
| sql_fd.write('-- BEGIN of category list\n') | |
| for group_meta in group_meta_list: | |
| sql_line_list = [] | |
| sql_line_list.append('-- BEGIN of category') | |
| sql_line_list.append( | |
| 'INSERT INTO `oc_category` (`category_id`, `parent_id`, `top`, `column`, `status`, `date_added`, `date_modified`) VALUES ({}, {}, {}, {}, {}, now(), now());'.format( | |
| sql_quote(group_meta['category_id']), | |
| sql_quote(group_meta['parent_category_id']), | |
| sql_quote(1), | |
| sql_quote(CATEGORY_COLUMN_FIELD), | |
| sql_quote(1), | |
| ) | |
| ) | |
| sql_line_list.append( | |
| 'INSERT INTO `oc_category_description` (`category_id`, `language_id`, `name`, `meta_title`) VALUES ({}, {}, {}, {});'.format( | |
| sql_quote(group_meta['category_id']), | |
| sql_quote(LANGUAGE_ID_FIELD), | |
| sql_quote(group_meta['name']), | |
| sql_quote(group_meta['name']), | |
| ) | |
| ) | |
| sql_line_list.append( | |
| 'INSERT INTO `oc_category_to_store` (`category_id`, `store_id`) VALUES ({}, {});'.format( | |
| sql_quote(group_meta['category_id']), | |
| sql_quote(STORE_ID_FIELD), | |
| ) | |
| ) | |
| sql_line_list.append( | |
| 'INSERT INTO `oc_category_to_layout` (`category_id`, `store_id`, `layout_id`) VALUES ({}, {}, {});'.format( | |
| sql_quote(group_meta['category_id']), | |
| sql_quote(STORE_ID_FIELD), | |
| sql_quote(LAYOUT_ID_FIELD), | |
| ) | |
| ) | |
| sql_line_list.append('-- END of category\n') | |
| sql_fd.write('{}\n'.format('\n'.join(sql_line_list))) | |
| sql_fd.write('-- END of category list\n\n') | |
| sql_fd.write('-- BEGIN of product list\n') | |
| for product_meta in product_meta_list: | |
| sql_line_list = [] | |
| sql_line_list.append('-- BEGIN of product') | |
| sql_line_list.append( | |
| 'INSERT INTO `oc_product` (`product_id`, `model`, `ean`, `quantity`, `stock_status_id`, `shipping`, `date_available`, `subtract`, `minimum`, `status`, `date_added`,`date_modified`) VALUES (' | |
| '{}, {}, {}, {}, {}, {}, now(), {}, {}, {}, now(), now());'.format( | |
| sql_quote(product_meta['product_id']), | |
| sql_quote(product_meta['name']), | |
| sql_quote(product_meta['barcode']), | |
| sql_quote(1000), | |
| sql_quote(PRODUCT_STOCK_STATUS_ID_FIELD), | |
| sql_quote(1), | |
| sql_quote(1), | |
| sql_quote(1), | |
| sql_quote(1), | |
| ) | |
| ) | |
| sql_line_list.append( | |
| 'INSERT INTO `oc_product_description` (`product_id`, `language_id`, `name`, `meta_title`) VALUES (' | |
| '{}, {}, {}, {});'.format( | |
| sql_quote(product_meta['product_id']), | |
| sql_quote(LANGUAGE_ID_FIELD), | |
| sql_quote(product_meta['name']), | |
| sql_quote(product_meta['name']), | |
| ) | |
| ) | |
| for group_guid in product_meta['groups']: | |
| category_meta = group_meta_by_guid_map.get(group_guid) | |
| if category_meta is None: | |
| continue | |
| sql_line_list.append( | |
| 'INSERT INTO `oc_product_to_category` (`product_id`, `category_id`) VALUES (' | |
| '{}, {});'.format( | |
| sql_quote(product_meta['product_id']), | |
| sql_quote(category_meta['category_id']), | |
| ) | |
| ) | |
| sql_line_list.append( | |
| 'INSERT INTO `oc_product_to_store` (`product_id`, `store_id`) VALUES (' | |
| '{}, {});'.format( | |
| sql_quote(product_meta['product_id']), | |
| sql_quote(STORE_ID_FIELD), | |
| ) | |
| ) | |
| sql_line_list.append( | |
| 'INSERT INTO `oc_product_to_layout` (`product_id`, `store_id`, `layout_id`) VALUES (' | |
| '{}, {}, {});'.format( | |
| sql_quote(product_meta['product_id']), | |
| sql_quote(STORE_ID_FIELD), | |
| sql_quote(LAYOUT_ID_FIELD), | |
| ) | |
| ) | |
| sql_line_list.append('-- END of product\n') | |
| sql_fd.write('{}\n'.format('\n'.join(sql_line_list))) | |
| sql_fd.write('-- END of product list\n\n') | |
| def main(): | |
| begin_id = int(sys.argv[1]) | |
| in_path = sys.argv[2] | |
| out_path = sys.argv[3] | |
| with open(in_path, mode='rb') as xml_fd, \ | |
| open(out_path, mode='wt', encoding='utf-8', newline='\n') as sql_fd: | |
| convert(begin_id, xml_fd, sql_fd) | |
| if __name__ == '__main__': | |
| main() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment