opencart-xml-to-sql.py

#!/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()