fHTbaHMW

1# -*- coding: utf-8 -*-
2import csv
3import xmlrpc.client
4import os
5from os.path import join, dirname
6from dotenv import load_dotenv
7from odoo.exceptions import UserError
8import sys
9import psycopg2
10import time
11
12conn_string = "dbname='murgic' host = '192.168.178.23' port='5432' user='odoo' password='odoo'"
13conn = psycopg2.connect(conn_string)
14
15# prepare to read the env file
16dotenv_path = join(dirname(__file__), '.env')
17load_dotenv(dotenv_path)
18
19# get connection info from .env file
20url = os.getenv('URL')
21db = os.getenv('DB')
22username = os.getenv('USERNAME')
23password = os.getenv('PASSWORD')
24
25# connect to odoo database
26common = xmlrpc.client.ServerProxy('{}/xmlrpc/2/common'.format(url))
27
28# get uid, if false, something is wrong with credentials
29uid = common.authenticate(db, username, password, {})
30
31# connect to database object with odoo
32models = xmlrpc.client.ServerProxy('{}/xmlrpc/2/object'.format(url))
33
34# get product category - name Tyres
35category = models.execute_kw(db, uid, password,
36                             'product.category', 'search',
37                             [[['name', '=', 'Tyres']]])
38
39def do_ean_import(path):
40    # read a csv file, in this case it is file with guid and ean
41    reader = csv.reader(open(path, encoding='latin-1'), delimiter=';')
42    row_count = len(open(path).readlines())
43    # store/skip header
44    header = next(reader)
45    i = 0
46    start_time = time.time()
47
48    # read csv line by line
49    for row in reader:
50        # i += 1
51        # sys.stdout.write(str(i))
52        # sys.stdout.write("\r%d%%" % int(i / row_count))
53        # sys.stdout.flush()
54
55        # take elements from list, based on index of elements in header
56        guid = row[header.index('NR')]
57        # get ID of product.details, if that guid exists in table
58        pd_id = guid_exists(guid)
59
60        # if there is no guid in product.details
61        if not pd_id:
62
63            # i += 1
64
65            details_array = prepare_array(guid, row, header)
66
67            # insert 1 new record to product.template and 1 to product details
68            try:
69                # new_record_template = store_new_product_template(details_array['name'], details_array['default_code'])
70
71                # check now via default_code, i.e. GoJames ID
72                pt_id = check_default_code(details_array['default_code'])
73
74                details_array.update({'product_tmpl_id': pt_id})
75
76                new_record = store_new_product_details(details_array)
77
78                print('new record stored')
79
80            except:
81                raise UserError('Problem with saving %s ' % details_array['default_code'])
82
83        # there is GUID, check before updating if there is record in product.template
84        else:
85            i += 1
86            detail_complete = models.execute_kw(db, uid, password,
87                                                'product.details', 'read', [pd_id])
88            details_array = prepare_array(guid, row, header)
89
90            # what to do in case details do not contain product_tmpl_id
91            if not check_fk(detail_complete):
92                # check now via default_code, i.e. GoJames ID
93                pt_id = check_default_code(detail_complete[0]['default_code'])
94
95                # in case template is not found even with default_code
96                # create new entry
97                if not pt_id:
98                    new_record_template = store_new_product_template(details_array['name'],
99                                                                     details_array['default_code'])
100                    details_array.update({'product_tmpl_id': new_record_template, 'id': pd_id})
101                    # update details with product template id and send details id to know what record to update
102                    update_new_product_details(details_array)
103                    print('ovdje 1. put kad ne moze naci ni fk ni code')
104                else:
105                    details_array.update({'product_tmpl_id': pt_id, 'id': pd_id})
106                    update_new_product_details(details_array)
107                    print('ovdje 2. put')
108
109            else:
110                # just update entry in details from row in csv
111                details_array.update({'id': pd_id})
112                update_new_product_details(details_array)
113                print('updated')
114
115    # get time of execution
116    print('Total seconds execution is %s' % (time.time() - start_time))
117
118
119def guid_exists(guid):
120    exists = models.execute_kw(db, uid, password,
121                      'product.details', 'search',
122                      [[['guid', '=', guid]]])
123    if not exists:
124        return False
125    else:
126        return exists[0]
127
128
129def prepare_array(guid, row, header):
130    ean = row[header.index('EAN')]
131    gruppe = row[header.index('Gruppe')]
132    einsatz_zweck = row[header.index('EinsatzZweck')]
133    dimension = row[header.index('DIMENSION')]
134    brand = row[header.index('Hersteller')]
135    breite = row[header.index('BREITE')]
136    hoehe = row[header.index('HOEHE')]
137    bauart = row[header.index('BAUART')]
138    felge = row[header.index('FELGE')]
139    li = row[header.index('LI')]
140    gi = row[header.index('GI')]
141    geschw = row[header.index('GESCHW')]
142    name = row[header.index('Profil')]
143    description = row[header.index('ProfilText')]
144    type = 'product'
145    categ_id = category[0]
146    weight = row[header.index('Gewicht')]
147    default_code = row[header.index('ArtikelNr')]
148
149    return {'guid': guid,
150            'ean': ean,
151            'gruppe': gruppe,
152            'einsatz_zweck': einsatz_zweck,
153            'dimension': dimension,
154            'brand': brand,
155            'breite': breite,
156            'hoehe': hoehe,
157            'bauart': bauart,
158            'felge': felge,
159            'li': li,
160            'gi': gi,
161            'geschw': geschw,
162            'name': name,
163            'description': description,
164            'type': type,
165            'categ_id': categ_id,
166            'weight': weight,
167            'default_code': default_code,
168            }
169
170
171def store_new_product_template(name, default_code):
172    return models.execute_kw(db, uid, password, 'product.template', 'create', [{
173        'name': name,
174        'default_code': default_code,
175        'type': 'product'
176    }])
177
178def store_new_product_details(details_array):
179    return models.execute_kw(db, uid, password, 'product.details', 'create', [details_array])
180
181
182def update_new_product_details(details_array):
183    id = details_array['id']
184    del details_array['id']
185    return models.execute_kw(db, uid, password, 'product.details', 'write', [[id], details_array])
186
187
188def check_fk(detail_complete):
189    # here, I have to check if the record is stored in product_template
190    product_tmpl_id = detail_complete[0]['product_tmpl_id']
191
192    if not product_tmpl_id:
193        return False
194    else:
195        return product_tmpl_id[0]
196
197
198def check_default_code(default_code):
199    # here, I have to check if the record is stored in product_template
200    # xmlrpc way, very slow
201    # exists = models.execute_kw(db, uid, password,
202    #                            'product.template', 'search',
203    #                            [[['default_code', '=', default_code]]])
204
205    # pure sql, much faster
206    sql = "SELECT * FROM product_template WHERE default_code = %s"
207    cur = conn.cursor()
208    cur.execute(sql, (default_code,))
209    exists = cur.fetchone()
210
211    if exists is None:
212        return False
213    else:
214        return exists[0]