models.py 27.5 KB
Newer Older
Administrator committed
1 2 3 4 5 6 7 8
# coding: utf-8
"""Products modelsmain page."""
from django.db import models
from outils.common_imports import *
from outils.common import OdooAPI
import csv
import tempfile
import pymysql.cursors
9
import datetime
10
import re
Administrator committed
11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

vcats = []

if hasattr(settings, 'VRAC_CATEGS'):
    vcats = settings.VRAC_CATEGS



class CagetteProduct(models.Model):
    """Class to handle cagette Odoo products."""

    name = None
    id = None

    def __str__(self):
        """String returned where Object has to be print."""
        return self.name

    def get_product_from_barcode(barcode):
        api = OdooAPI()
        cond = [['barcode', '=', barcode],
                '|',
                ['active', '=', True],
                ['active', '=', False]]
Thibault Grandjean committed
35
        fields = ['id', 'uom_id', 'name', 'qty_available', 'barcode', 'active', 'shelf_id', 'product_tmpl_id']
Administrator committed
36 37 38

        return api.search_read('product.product', cond, fields)

39 40 41 42 43 44 45 46 47 48 49 50
    def get_products_stdprices(ids):
        api = OdooAPI()
        cond = [['id', 'in', ids]]
        fields = ['id', 'standard_price']

        try:
            res = api.search_read('product.product', cond, fields)
        except Exception as e:
            res = {'error': str(e)}

        return res

51

Administrator committed
52 53 54 55 56 57 58
    @staticmethod
    def get_product_info_for_label_from_template_id(template_id):
        """Get product info for label."""
        api = OdooAPI()
        cond = [['product_tmpl_id.id', '=', template_id]]
        fields = ['barcode', 'product_tmpl_id', 'pricetag_rackinfos',
                  'price_weight_net', 'price_volume', 'list_price',
59
                  'weight_net', 'volume', 'to_weight', 'meal_voucher_ok']
60
        if not getattr(settings, 'SHOW_MEAL_VOUCHER_OK_LINE_IN_PRODUCT_INFO_FOR_LABEL', True):
61
            fields.remove('meal_voucher_ok')
62 63 64 65 66 67 68 69 70 71 72 73 74
        additionnal_fields = getattr(settings, 'SHELF_LABELS_ADD_FIELDS', [])
        fields += additionnal_fields
        product_data = api.search_read('product.product', cond, fields)
        if product_data and 'suppliers' in additionnal_fields:
            cond = [['product_tmpl_id.id', '=', template_id]]
            fields = ['name']
            suppliers = api.search_read('product.supplierinfo', cond, fields)
            if suppliers:
                suppliers_name = []
                for s in suppliers:
                    suppliers_name.append(s['name'][1])
                product_data[0]['suppliers'] = ', '.join(list(set(suppliers_name)))
        return product_data
Administrator committed
75 76 77 78 79 80

    @staticmethod
    def generate_label_for_printing(templ_id, directory, price=None, nb=None):
        res = {}
        try:
            p = CagetteProduct.get_product_info_for_label_from_template_id(templ_id)
81

Administrator committed
82 83 84 85
            if (p and p[0]['product_tmpl_id'][0] == int(templ_id)):
                product = p[0]
                txt = ''
                for k, v in product.items():
86 87
                    if type(v) == list and len(v) > 0 :
                        v = v[-1]
Administrator committed
88 89 90 91 92 93 94 95
                    if k == 'product_tmpl_id':
                        k = 'name'
                    if k == 'list_price' and len(price) > 0 and float(price) > 0:
                        v = price
                    if k == 'price_weight_net' and len(v) > 0 and len(price) > 0 and float(price) > 0:
                        v = round(float(price) / float(product['weight_net']), 2)
                    if k == 'price_volume' and len(v) > 0 and len(price) > 0 and float(price) > 0:
                        v = round(float(price) / float(product['volume']), 2)
96 97 98
                    if directory != "/product_labels/" or (directory == "/product_labels/" and k != "meal_voucher_ok"):
                        # add parameter to text unless it's for a product label and parameter is meal_voucher_ok
                        txt += k + '=' + str(v).strip() + "\r\n"
Administrator committed
99 100 101 102 103 104 105 106 107 108 109
                if not (nb is None) and len(nb) > 0:
                    txt += 'nb_impression=' + str(nb) + "\r\n"
                res['txt'] = txt
                os_file = settings.DAV_PATH + directory
                os_file += 'EtiquetteProduit_' + str(product['id'])
                os_file += '_' + str(int(time.time())) + '.txt'
                file = open(os_file, "w")
                file.write(txt)
                file.close()
        except Exception as e:
            res['error'] = str(e)
110
            coop_logger.error("Generate label : %s %s", templ_id, str(e))
Administrator committed
111 112
        return res

113 114 115 116
    @staticmethod
    def register_start_supplier_shortage(product_id, partner_id, date_start):
        """Start a supplier shortage for a product"""
        api = OdooAPI()
117 118 119 120 121 122 123 124 125 126

        c = [['product_id', '=', product_id],
                ['partner_id', '=', partner_id],
                ['date_start', '=', date_start]]
        existing = api.search_read('product.supplier.shortage', c)

        if existing:
            res = "already on shortage"
            return res

127 128 129 130 131 132 133 134
        f = {
            'product_id' : product_id,
            'partner_id' : partner_id,
            'date_start' : date_start,
        }
        res = api.create('product.supplier.shortage', f)
        return res

135
    @staticmethod
136
    def associate_supplier_to_product(data):
137
        api = OdooAPI()
Damien Moulard committed
138
        res = {}
139

140 141 142 143
        product_tmpl_id = data["product_tmpl_id"]
        partner_id = data["supplier_id"]
        package_qty = data["package_qty"]
        price = data["price"]
144
        
145 146 147 148 149 150 151 152 153 154 155 156 157
        # Look for existing association
        f = ["id"]
        c = [['product_tmpl_id', '=', product_tmpl_id], ['name', '=', partner_id]]
        res_existing_supplierinfo = api.search_read('product.supplierinfo', c, f)

        if (len(res_existing_supplierinfo) > 0):
            # A relation already exists, update it's start & end dates 
            psi_id = res_existing_supplierinfo[0]['id']

            today = datetime.date.today().strftime("%Y-%m-%d")

            f = {
                'date_start': today,
158 159 160 161 162
                'date_end': False,
                'price': price,
                'base_price': price,
                'package_qty': package_qty,
                'sequence': 1000  # lowest priority for the new suppliers
163 164 165 166
            }

            try:
                res["update"] = api.update('product.supplierinfo', psi_id, f)
167
                res["psi_id"] = psi_id
168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192
            except Exception as e:
                res['error'] = str(e)
        else:
            # Relation doesn't exists, create one
            f = ["id", "standard_price", "purchase_ok"]
            c = [['product_tmpl_id', '=', product_tmpl_id]]
            res_products = api.search_read('product.product', c, f)
            product = res_products[0]
            
            today = datetime.date.today().strftime("%Y-%m-%d")

            f = {
                'product_tmpl_id' : product_tmpl_id,
                'product_id' : product["id"],
                'name' : partner_id,
                'product_purchase_ok': product["purchase_ok"],
                'price': price,
                'base_price': price,
                'package_qty': package_qty,
                'date_start': today,
                'sequence': 1000  # lowest priority for the new suppliers
            }

            try:
                res['create'] = api.create('product.supplierinfo', f)
193
                res['psi_id'] = res['create'] # consistency between update & create res
194 195
            except Exception as e:
                res['error'] = str(e)
196 197 198

        return res

199
    @staticmethod
200
    def end_supplier_product_association(data):
201
        api = OdooAPI()
202
        res = {}
203 204 205 206 207

        product_tmpl_id = data["product_tmpl_id"]
        partner_id = data["supplier_id"]

        f = ["id"]
208
        c = [['product_tmpl_id', '=', product_tmpl_id], ['name', '=', partner_id], ['date_end', '=', False]]
209 210
        res_supplierinfo = api.search_read('product.supplierinfo', c, f)

211 212 213
        # End all active associations in case multiple are open (which shouldn't happen)
        for psi in res_supplierinfo:
            psi_id = psi['id']
214

215
            today = datetime.date.today().strftime("%Y-%m-%d")
216

217 218 219 220 221 222 223 224
            f = {
                'date_end': today
            }

            try:
                res["update"] = api.update('product.supplierinfo', psi_id, f)
            except Exception as e:
                res['error'] = str(e)
225 226 227

        return res

Damien Moulard committed
228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244
    @staticmethod
    def update_product_purchase_ok(product_tmpl_id, purchase_ok):
        api = OdooAPI()
        res = {}

        f = {
            'purchase_ok': purchase_ok
        }

        try:
            res["update"] = api.update('product.template', product_tmpl_id, f)
        except Exception as e:
            res["error"] = str(e)
            print(str(e))

        return res

Damien Moulard committed
245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261
    @staticmethod
    def update_product_internal_ref(product_tmpl_id, default_code):
        api = OdooAPI()
        res = {}

        f = {
            'default_code': default_code
        }

        try:
            res["update"] = api.update('product.template', product_tmpl_id, f)
        except Exception as e:
            res["error"] = str(e)
            print(str(e))

        return res

262
    @staticmethod
263 264 265 266 267
    def commit_actions_on_product(data):
        """ Update: 
            - NPA (ne pas acheter) 
            - Product is active
            - Minimal stock
268
            - price /supplier
269
        """
270 271 272
        res = {}
        try:
            api = OdooAPI()
273

274 275 276 277 278
            # Minimal & Actual stock, Active
            f = {
                'minimal_stock': float(data['minimal_stock']),
                'active': not data['to_archive']
            }
279

280
            # NPA
281 282 283 284 285 286 287
            if 'simple-npa' in data['npa']:
                f['purchase_ok'] = 0
            if 'npa-in-name' in data['npa']:
                #  Add [NPA] in product name if needed
                f['name'] = data['name'] if ('[NPA]' in data['name']) else data['name'] + " [NPA]"
                f['purchase_ok'] = 0
            elif '[NPA]' in data['name']:
288
                # Remove [NPA] from name
289 290 291 292 293 294 295 296 297 298
                f['name'] = re.sub(r'( \[NPA\])', '', data['name'])

            current_name = data['name'] if ('name' not in f) else f['name']
            if 'fds-in-name' in data['npa']:
                f['name'] = current_name if '[FDS]' in data['name'] else current_name + " [FDS]"
                f['purchase_ok'] = 0
            elif '[FDS]' in current_name:
                f['name'] = re.sub(r'( \[FDS\])', '', current_name)
            if len(data['npa']) == 0:
                f['purchase_ok'] = 1
299

300 301 302 303 304 305 306 307 308 309
            res["update"] = api.update('product.template', int(data['id']), f)

            # Update suppliers info
            res["update_supplierinfo"] = []
            for supplierinfo in data["suppliersinfo"]:
                f= {'price': supplierinfo["price"]}
                res_update_si = api.update('product.supplierinfo', int(supplierinfo['supplierinfo_id']), f)

                res["update_supplierinfo"].append(res_update_si)

310 311
        except Exception as e:
            res["error"] = str(e)
312
            coop_logger.error("commit_actions_on_product : %s %s", str(e), str(data))
313
        return res
Administrator committed
314 315 316
class CagetteProducts(models.Model):
    """Initially used to make massive barcode update."""

317

318 319 320 321 322 323 324 325 326 327
    @staticmethod
    def get_simple_list():
        res = []
        api = OdooAPI()
        try:
            res = api.execute('lacagette.products', 'get_simple_list', {'only_purchase_ok': True})
        except Exception as e:
            coop_logger.error("Odoo api products simple list : %s", str(e))
        return res

Administrator committed
328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399
    @staticmethod
    def __unique_product_list(plist):
        # 276
        upl = {}
        for p in plist:
            if not (p['id'] in upl):
                if ('image_medium' in p):
                    p['image'] = p['image_medium']
                    p['image_medium'] = ''
                # if ('image' in p):
                #    p['image'] = __process_img_data(p, 'image')
                if type(p['image']) is bool:
                    p['image'] = ''
                if p['categ_id'][0] == settings.CATEG_FRUIT:
                    p['categ'] = 'F'
                elif p['categ_id'][0] == settings.CATEG_LEGUME:
                    p['categ'] = 'L'
                elif (p['name'].lower().find(' vrac') > -1) or (p['categ_id'][0] in vcats):
                    p['categ'] = 'V'
                else:
                    p['categ'] = 'A'

                if not (len(p['image']) == 0 and p['list_price'] == 1.0):
                    if p['name'].find('A SUPPRIMER') == -1:
                        upl[p['id']] = p
        # 169
        return upl.values()

    @staticmethod
    def get_products_to_weight(withCandidate=False, fields=[]):
        api = OdooAPI()
        cond = [['active', '=', True],
                ['to_weight', '=', True],
                ['available_in_pos', '=', True]]
        if not withCandidate:
            cond = [('active', '=', True), ('to_weight', '=', True), ('available_in_pos', '=', True), '|', ('barcode', '=like', '0491%'), ('barcode', '=like', '0493%')]

        return api.search_read('product.product', cond, fields)

    @staticmethod
    def get_vrac_products_from_name(withCandidate=False, fields=[]):
        api = OdooAPI()
        cond = [['active', '=', True],
                ['available_in_pos', '=', True],
                ['name', 'ilike', 'vrac']]
        if not withCandidate:
            cond = [('active', '=', True), ('name', 'ilike', 'vrac'), ('available_in_pos', '=', True), '|', ('barcode', '=like', '0491%'), ('barcode', '=like', '0493%')]
        return api.search_read('product.product', cond, fields)

    @staticmethod
    def get_vrac_products_from_cats(cats, withCandidate=False, fields=[]):
        api = OdooAPI()
        cond = [['active', '=', True],
                ['available_in_pos', '=', True],
                ['categ_id', 'in', cats]]
        return api.search_read('product.product', cond, fields)

    @staticmethod
    def get_fl_products(withCandidate=False, fields=[]):
        api = OdooAPI()
        flv_cats = [settings.CATEG_FRUIT, settings.CATEG_LEGUME]
        cond = [['active', '=', True],
                ['available_in_pos', '=', True],
                ['categ_id', 'in', flv_cats]]
        if not withCandidate:
            cond = [('active', '=', True), ('categ_id', 'in', flv_cats), ('available_in_pos', '=', True), '|', ('barcode', '=like', '0493%'), ('barcode', '=like', '0499%')]
        return api.search_read('product.product', cond, fields)

    @staticmethod
    def get_products_for_label_appli(withCandidate=False):
        fields = ['sale_ok', 'uom_id', 'barcode',
                  'name', 'display_name', 'list_price', 'categ_id', 'image_medium']
400 401
        if getattr(settings, 'EXPORT_POS_CAT_FOR_SCALES', False) is True:
            fields.append('pos_categ_id')
Administrator committed
402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417
        to_weight = CagetteProducts.get_products_to_weight(withCandidate, fields)
        if len(vcats) > 0:
            vrac = CagetteProducts.get_vrac_products_from_cats(vcats, withCandidate, fields)
        else:
            vrac = CagetteProducts.get_vrac_products_from_name(withCandidate, fields)
        flv = CagetteProducts.get_fl_products(withCandidate, fields)
        products = CagetteProducts.__unique_product_list(to_weight + vrac + flv)
        return products

    @staticmethod
    def get_all_available():
        api = OdooAPI()
        cond = [['active', '=', True], ['sale_ok', '=', True]]
        fields = ['uom_id', 'display_name','barcode']
        return api.search_read('product.product', cond, fields)

418 419 420 421 422 423 424 425 426 427 428 429 430

    @staticmethod
    def get_pos_categories():
        api = OdooAPI()
        fields = ['name', 'parent_id', 'sequence', 'image_small']
        try:
            res = api.search_read('pos.category', [], fields)
        except Exception as e:
            coop_logger.error('Getting POS categories : %s', str(e))
            res = []

        return res

Administrator committed
431 432
    @staticmethod
    def get_all_barcodes():
433
        """Needs lacagette_products Odoo module to be active."""
Administrator committed
434
        result = {}
435 436 437 438 439 440 441 442 443
        api = OdooAPI()
        try:
            res = api.execute('lacagette.products', 'get_barcodes', {})

            if 'list' in res:
                result['pdts'] = {}
                for p in res['list']:
                    # transcode result to compact format (for bandwith save and browser memory)
                    # real size / 4 (for 2750 products)
444 445 446 447 448 449
                    # following 2 lines is only useful for La Cagette (changing uom_id in Database has cascade effects...)
                    # TODO : Use mapping list in config.py
                    if p['uom_id'] == 3:
                        p['uom_id'] = 21
                    if p['uom_id'] == 20:
                        p['uom_id'] = 1
450 451 452 453 454 455 456
                    result['pdts'][p['barcode']] = [
                                                    p['display_name'],
                                                    p['sale_ok'],
                                                    p['purchase_ok'],
                                                    p['available_in_pos'],
                                                    p['id'],
                                                    p['standard_price'],
457
                                                    p['list_price'],
458 459 460 461 462 463 464 465
                                                    p['uom_id']]
                if 'uoms' in res and 'list' in res['uoms']:
                    result['uoms'] = res['uoms']['list']
            elif 'error' in res:
                result['error'] = res['error']
        except Exception as e:
                result['error'] = str(e)
        return result
Administrator committed
466

467 468 469 470 471 472 473 474 475 476
    def get_uoms():
        result = {}
        api = OdooAPI()
        try:
            cond = [['active', '=', True]]
            fields = ['display_name', 'uom_type']
            res = api.search_read('product.uom', cond, fields)
            result['list'] = res
        except Exception as e:
                result['error'] = str(e)
Administrator committed
477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519
        return result

    @staticmethod
    def find_bc_errors():
        from outils.functions import checkEAN13
        import re
        p_with_errors = {'missing': [], 'length': [], 'check': []}
        api = OdooAPI()
        # exclude teams and capital shares
        cond = [['active', '=', True], ['id', '>', 4], ['id', '!=', 1008]]
        cond.append(['available_in_pos', '=', True])
        cond.append(['sale_ok', '=', True])
        fields = ['barcode', 'display_name']
        p_with_bc = api.search_read('product.product', cond, fields)
        for p in p_with_bc:
            if (p['barcode'] is False):
                p_with_errors['missing'].append(p)
            else:
                try:
                    if (checkEAN13(p['barcode']) is False):
                        p_with_errors['check'].append(p)
                except:
                    p['barcode'] = re.sub('[ ]', '[ESP]', p['barcode'])
                    p['barcode'] = re.sub('[\t]', '[TAB]', p['barcode'])
                    p_with_errors['length'].append(p)

        return p_with_errors

    @staticmethod
    def get_sales(request):
        from outils.functions import getMonthFromRequestForm
        res = {}
        m_res = getMonthFromRequestForm(request)
        if 'month' in m_res:
            api = OdooAPI()
            res = api.execute('lacagette.pos_product_sales', 'get_pos_data', m_res)
            res['month'] = m_res['month']
        else:
            res = m_res
        return res

    @staticmethod
    def get_barcode_rules():
520 521 522 523 524 525 526
        result = {'patterns': [], 'aliases': {}}
        try:
            import re
            c = [['type', 'in', ['FF_price_to_weight', 'price', 'price_to_weight', 'product', 'weight', 'alias']], ['barcode_nomenclature_id','=', 1]]
            rules = OdooAPI().search_read('barcode.rule', c, ['pattern', 'type', 'alias'], order="sequence ASC")
            # As rules are ordered by sequence, let's find where to stop (.* pattern)
            stop_idx = len(rules) - 1
527 528
            #  .* (catchall) rules, if exists, may be not the last rule
            #  let's find it and set stop_idx consequently
529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547
            i = 0
            for r in rules:
                if r['pattern'] == ".*":
                    stop_idx = i
                i += 1
            if stop_idx > 0:
                rules = rules[:stop_idx - 1]
            for r in rules:
                if r['type'] == 'alias':
                    alias_bc = re.sub('[^0-9]', '', r['pattern'])
                    if len(alias_bc) > 0:
                        result['aliases'][alias_bc] = r['alias']
                elif '{' in r['pattern'] or '.' in r['pattern']:
                    result['patterns'].append(r)
        except Exception as e:
            result['error'] = str(e)
            coop_logger.error("Get Barcode Rules : %s", str(e))
        # coop_logger.info("Fin get bc rules : %s", str(result))
        return result
Administrator committed
548 549 550 551 552 553 554 555 556


    @staticmethod
    def get_fixed_barcode_correspondance(barcodes):
        import re
        from outils.functions import computeEAN13Check
        bc_map = {}
        rules = CagetteProducts.get_barcode_rules()

557
        rules = rules['patterns']
Administrator committed
558 559 560 561 562 563
        # now, just keep rules with N in pattern
        rules = list(filter(lambda x: '.' in x['pattern'], rules))
        rules = list(map(lambda x: x['pattern'], rules))

        # now remove {NN...} from pattern
        rules = list(map(lambda x: re.sub(r'{.+}', '', x), rules))
564
        # coop_logger.info('rules = %s', rules)
Administrator committed
565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587
        # now compile regex for pattern
        regex = []
        for r in rules:
            regex.append(re.compile(r))
        for bc in barcodes:
            found = False
            for r in regex:
                if found is False:
                    if r.match(bc):
                        generic = ''
                        for i in range(0, len(r.pattern)):
                            if r.pattern[i] == '.':
                                generic += bc[i]
                            else:
                                generic += r.pattern[i]
                        generic = generic.ljust(12, '0')
                        generic += str(computeEAN13Check(generic))
                        bc_map[bc] = generic
                        found = True
            if found is False:
                bc_map[bc] = bc
        return bc_map

588 589 590 591 592 593 594 595 596 597 598
    @staticmethod
    def get_template_products_sales_average(params={}):
        api = OdooAPI()
        res = {}
        try:
            res = api.execute('lacagette.products', 'get_template_products_sales_average', params)
        except Exception as e:
            coop_logger.error('get_template_products_sales_average %s (%s)', str(e), str(params))
            res["error"] = str(e)
        return res

599
    @staticmethod
600
    def get_products_for_order_helper(supplier_ids, pids = [], stats_from = None):
601
        """ 
602 603 604
            supplier_ids: Get products by supplier if one or more supplier id is set. If set, pids is ignored.
            pids: If set & supplier_ids is None/empty, get products specified in pids. In this case, suppliers info won't be fetched.
            stats_from: date from which we should calculate sells stats.
605
        """
606 607 608 609 610 611
        api = OdooAPI()
        res = {}

        try:
            today = datetime.date.today().strftime("%Y-%m-%d")

Damien Moulard committed
612
            if supplier_ids is not None and len(supplier_ids) > 0:
613
                # Get products/supplier relation
614
                f = ["id", "product_tmpl_id", 'date_start', 'date_end', 'package_qty', 'price', 'name', 'product_code', 'sequence']
615
                c = [['name', 'in', [ int(x) for x in supplier_ids]]]
616 617 618 619
                psi = api.search_read('product.supplierinfo', c, f)

                # Filter valid data
                ptids = []
620
                valid_psi = []
621 622
                for p in psi:
                    if (p["product_tmpl_id"] is not False 
623
                        and (p["date_start"] is False or p["date_start"] is not False and p["date_start"] <= today) 
624 625
                        and (p["date_end"] is False or p["date_end"] is not False and p["date_end"] > today)):
                        valid_psi.append(p)
626 627
                        ptids.append(p["product_tmpl_id"][0])
            else:
628
                ptids = [ int(x) for x in pids ]
629 630

            # Get products templates
631 632 633 634 635 636 637 638 639 640
            f = [
                "id",
                "state",
                "name",
                "default_code",
                "qty_available",
                "incoming_qty",
                "uom_id",
                "purchase_ok",
                "supplier_taxes_id",
641 642
                "product_variant_ids",
                "minimal_stock"
643
            ]
644
            c = [['id', 'in', ptids], ['purchase_ok', '=', True], ['active', '=', True]]
645 646
            products_t = api.search_read('product.template', c, f)
            filtered_products_t = [p for p in products_t if p["state"] != "end" and p["state"] != "obsolete"]
647

648 649
            sales_average_params = {
                'ids': ptids, 
650 651
                # 'from': '2019-04-10', 
                # 'to': '2019-08-10',
652
            }
653 654 655 656

            if stats_from is not None and stats_from != '':
                sales_average_params['from'] = stats_from

657
            sales = CagetteProducts.get_template_products_sales_average(sales_average_params)
658

659 660 661 662 663
            if 'list' in sales and len(sales['list']) > 0:
                sales = sales['list']
            else:
                sales = []
            
664
            # Add supplier data to product data
665
            for i, fp in enumerate(filtered_products_t):
Damien Moulard committed
666
                if supplier_ids is not None and len(supplier_ids) > 0:
667 668
                    # Add all the product suppliersinfo (products from multiple suppliers into the suppliers list provided)
                    filtered_products_t[i]['suppliersinfo'] = []
669
                    for psi_item in valid_psi: 
670 671
                        if psi_item["product_tmpl_id"] is not False and psi_item ["product_tmpl_id"][0] == fp["id"]:
                            filtered_products_t[i]['suppliersinfo'].append({
672
                                'id': int(psi_item["id"]),
673 674
                                'supplier_id': int(psi_item["name"][0]),
                                'package_qty': psi_item["package_qty"],
675
                                'price': psi_item["price"],
676 677
                                'product_code': psi_item["product_code"],
                                'sequence': psi_item["sequence"]
678
                            })
679

680 681
                for s in sales:
                    if s["id"] == fp["id"]:
682
                        filtered_products_t[i]['daily_conso'] = s["average_qty"]
683 684
                        filtered_products_t[i]['sigma'] = s["sigma"]
                        filtered_products_t[i]['vpc'] = s["vpc"]
685

686 687
            res["products"] = filtered_products_t
        except Exception as e:
688
            coop_logger.error('get_products_for_order_helper %s (%s)', str(e))
689 690 691 692
            res["error"] = str(e)

        return res

Administrator committed
693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719
class OFF(models.Model):
    """OpenFoodFact restricted DB queries."""

    conn = None

    def __init__(self):
        self.conn = pymysql.connect(host='localhost',
                                    user=settings.SQL_OFF['user'],
                                    db=settings.SQL_OFF['db'],
                                    password=settings.SQL_OFF['pwd'],
                                    charset='utf8',
                                    cursorclass=pymysql.cursors.DictCursor)

    def get_products(self):
        res = {}
        if self.conn:
            try:
                with self.conn.cursor() as cursor:
                    sql = "SELECT code, nova_group, nutrition_grade_fr, energy_100g, quantity, categories, labels, manufacturing_places, origins, url FROM produits"
                    cursor.execute(sql)
                    for row in cursor:
                        res[str(row['code'])] = row
            except Exception as e:
                res['error'] = str(e)
            finally:
                self.conn.close()
        return res