models.py

from django.db import models
from outils.common_imports import *

from outils.common import OdooAPI
from products.models import CagetteProducts, CagetteProduct
from inventory.models import CagetteInventory

import os
from datetime import date, datetime, timezone
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font
from statistics import *


# Prefix for temp shelf inventory files
tmp_inv_file_prefix = 'temp/inventory_shelf_'
default_inventory_start_datetime = "0001-01-01 00:00:00"

def as_text(value):
    """ Utils """
    return str(value) if value is not None else ""

class Shelf(models.Model):

    def __init__(self, id=0):
        """Init with odoo id."""
        self.id = int(id)
        self.o_api = OdooAPI()

    def get(self):
        res = {}
        try:
            c = [['id', '=', self.id]]
            f = []
            data = self.o_api.search_read('product.shelfs', c, f)
            res = data[0]
        except Exception as e:
            res['error'] = "Le rayon n'a pas pu être trouvé (" + str(e) + ")"
        return res

    def get_products(self, additional_fields=[]):
        res = {}
        try:
            c = [['shelf_id', '=', self.id]]
            f = [
                'barcode',
                'name',
                'uom_id',
                'qty_available',
                'standard_price',
                'last_inv_delta',
                'last_inv_losses',
                'active'
            ]

            f += additional_fields
            pdts = self.o_api.search_read('product.product', c, f)
            for p in pdts:
                for k, v in p.items():
                    if v is False:
                        p[k] = ''
            res['data'] = pdts
        except Exception as e:
            res['error'] = "Le rayon n'a pas pu être créé (" + str(e) + ")"
        return res

    def create(self, params):
        res = {}
        try:
            name = params.get('name')
            description = params.get('description')
            sort_order = params.get('sort_order')
            f = {'name': name, 'description': description, 'sort_order': sort_order}
            self.id = self.o_api.create('product.shelfs',f)
            f['id'] = self.id
            res = f
        except Exception as e:
            res['error'] = "Le rayon n'a pas pu être créé (" + str(e) + ")"
        return res

    def update(self, params):
        res = {}
        try:
            id = int(params.get('id'))
            name = params.get('name')
            description = params.get('description')
            sort_order = params.get('sort_order')
            f = {'name': name, 'description': description, 'sort_order': sort_order}

            result = self.o_api.update('product.shelfs', id, f)

            if result:
                c = [['id','=', id]]
                res = self.o_api.search_read('product.shelfs', c)
                res = res[0]
            else:
                res['error'] = "Le rayon n'a pas pu être mis à jour"
        except Exception as e:
            res['error'] = "Le rayon n'a pas pu être mis à jour (" + str(e) + ")"
        return res

    def update_shelf_with_inventory_data(self, params):
        res = {}
        f = {}
        if params['inventory_status'] == '' :
            f['inventory_status'] = 'step1_done'
        elif params['inventory_status'] == 'step1_done' :
            # Inventory all done
            f['inventory_status'] = ''
            f['date_last_inventory'] = date.today().strftime("%Y-%m-%d")
            f['ongoing_inv_start_datetime'] = default_inventory_start_datetime # Reset

            if 'last_inventory_id' in params:
                f['last_inventory_id'] = params['last_inventory_id']
            if 'shelf_delta' in params:
                f['last_inv_delta_percentage'] = params['shelf_delta']
            if 'shelf_losses' in params:
                f['last_inv_losses_percentage'] = params['shelf_losses']

        try:
            res['update'] = self.o_api.update('product.shelfs', self.id, f)
        except Exception as e:
            coop_logger.error("Error while updating products shelf %s", stre(e))
            res['error'] = "Error while updating products shelf (" + str(e) + ")"

        return res

    def update_products_with_inventory_data(self, products):
        done = []
        missed = []

        for p in products:
            try:
                f = {
                'last_inv_delta': p['delta'],
                'last_inv_losses': p['losses']
                }

                result = self.o_api.update('product.product', p['id'], f)
                done.append({'id': p['id'], 'update': result})
            except Exception as e:
                missed.append({'id': p['id'], 'update': False})

        return {'missed': missed, 'done': done}

    def delete(self, params):
        res = {}
        try:
            shelf_id = params.get('id')
            res = self.o_api.execute('product.shelfs', 'unlink', [shelf_id])
        except Exception as e:
            coop_logger.error("Rayon, delete : %s, %s", str(e), str(params))
            res['error'] = "Le rayon n'a pas pu être détruit"
        return res

    def print_labels(self):
        res = {}
        try:
            for p in self.get_products(['product_tmpl_id'])['data']:
             CagetteProduct().generate_label_for_printing(p['product_tmpl_id'][0], '/labels/')
            res['success'] = "Le rayon a correctement été imprimé"
        except Exception as e:
            coop_logger.error("Rayon, print labels : %s", str(e))
            res['error'] = "Le rayon n'a pas pu être imprimé"
        return res

    def _get_pdts_from_barcodes(self, barcodes):
        c = [['barcode', 'in', barcodes]]
        f = ['barcode']
        return self.o_api.search_read('product.product', c, f)

    def add_products_by_barcodes(self, barcodes):
        res = {}
        try:
            #  TODO : send directly barcodes as params (to long to test /debug for now)
            pids = []
            barcodes = list(map(str, barcodes))
            # Get bc as should be stored in Odoo
            bc_map = CagetteProducts.get_fixed_barcode_correspondance(barcodes)

            p_res = self._get_pdts_from_barcodes(list(bc_map.values()))
            if (p_res):
                found_bc = []
                for p in p_res:
                    pids.append(p['id'])
                    found_bc.append(p['barcode'])

                if (len(p_res) != len(barcodes)):
                    res['missing'] = list(set(barcodes) ^ set(found_bc))
                params = {'shelf_id': self.id, 'pids': pids}
                res['added'] = self.o_api.execute('product.shelfs', 'add_products', params)
            else:
                # None of the list have been found
                res['msg'] = "No product found"
        except Exception as e:
            coop_logger.error("Rayons, add_products_by_barcodes : %s, %s", str(e), str(barcodes))
            res['error'] = "L'enregistrement n'a pas pu se réaliser"
        return res

    def update_last_product_added_date(self):
        res = {}
        today = date.today().strftime("%Y-%m-%d")
        f = {'date_last_product_added': today}

        try:
            res["update"] = self.o_api.update('product.shelfs', self.id, f)
        except Exception as e:
            res['error'] = str(e)

        return res

    def set_begin_inventory_datetime(self):
        res = {}
        now = datetime.now(timezone.utc).isoformat()  # save UTC time
        f = {'ongoing_inv_start_datetime': now}

        try:
            res["update"] = self.o_api.update('product.shelfs', self.id, f)
            res["inventory_begin_datetime"] = now
        except Exception as e:
            res['error'] = str(e)

        return res

    def delete_ongoing_inv_data(self):
        """ Reset shelf state & inventory data """
        res = {}

        # Reset shelf state
        f = {
            'inventory_status': '',
            'ongoing_inv_start_datetime': default_inventory_start_datetime
        }

        try:
            res["update"] = self.o_api.update('product.shelfs', self.id, f)

            try:
                # Delete local file
                res["tmp_inventory"] = self.remove_tmp_inventory()
            except Exception as e:
                pass
        except Exception as e:
            res['error'] = str(e)

        return res

    def save_tmp_inventory(self, inventory_data):
        """Save inventory data in a json temp file"""
        res = {}
        try:
            filename = tmp_inv_file_prefix + str(self.id) + '.json'
            with open(filename, 'w') as outfile:
                json.dump(inventory_data, outfile)

            res['file_saved'] = True
        except Exception as e:
            res['error'] = str(e)

        return res

    def remove_tmp_inventory(self):
        """Remove json temp inventory file"""
        try:
            filename = tmp_inv_file_prefix + str(self.id) + '.json'
            os.remove(filename)

            lockfilename = tmp_inv_file_prefix + str(self.id) + '.lock'
            try:
                os.remove(lockfilename)
            except Exception as e:
                pass

            return True
        except Exception as e:
            return False

    def get_full_inventory_data(self, inventory_data) :
        """
            Get data from json file of inventory's first step,
            and concatenate product qties of both steps.

            Also check for duplicates.
                If found, concatenate qties and remove duplicate.
        """
        # get saved data from first step
        first_inventory = None
        try:
            filename = tmp_inv_file_prefix + str(self.id) + '.json'
            with open(filename) as json_file:
                first_inventory = json.load(json_file)
        except Exception as e:
            coop_logger.error("Unable to process first step file : %s", e)
            import errno
            raise FileExistsError(errno.ENOENT, os.strerror(errno.ENOENT), filename)

        lockfilename = tmp_inv_file_prefix + str(self.id) + '.lock'

        # Look for lock file: if exists, first step file is being processed so stop here
        try:
            with open(lockfilename) as lock_file:
                return {'error': 'First step file busy', 'busy': True}
        except Exception as e:
            pass

        # Verification passed, create the lock file to indicate first step file is being processed
        try:
            with open(lockfilename, 'w') as lock_file:
                json.dump({}, lock_file)
        except Exception as e:
            coop_logger.error("Unable to create lock file : %s", e)

        if first_inventory:
            # if poducts in saved data
            if 'products' in first_inventory :
                # for each product in incoming inventory data
                for p in inventory_data['products'] :
                    # find product in saved data
                    matching_product = None
                    mp_index = -1
                    for i, p2 in enumerate(first_inventory['products'], start=0):
                        if p['id'] == p2['id']:
                            mp_index = i
                            matching_product = p2

                    # concatenate qtys if found
                    if mp_index != -1:
                        qty = float(p['qty']) + float(matching_product['qty'])
                        p['qty'] = qty

                        # pop product in first_inventory products list
                        first_inventory['products'].pop(mp_index)

            # if products remain in first step data, add it to inventory data
            if len(first_inventory['products']) > 0 :
                inventory_data['products'] = inventory_data['products'] + first_inventory['products']

            # Add user comments from step 1
            if 'user_comments' in first_inventory:
                inventory_data['user_comments_step1'] = first_inventory['user_comments']
        else:
            inventory_data['error'] = "Missing first step data"

        # Make another pass through data to remove duplicates
        safe_from_duplicates = []
        for p in inventory_data['products']:
            found = False
            for p2 in safe_from_duplicates:
                if p['id'] == p2['id']:
                    # Concatenate qties for duplicates
                    qty = float(p['qty']) + float(p2['qty'])
                    p2['qty'] = qty
                    found = True

            if not found:
                safe_from_duplicates.append(p)

        inventory_data['products'] = safe_from_duplicates

        return inventory_data

    def save_inventory_report(self, inventory_data) :
        """Save a report of differences found after a shelf inventory"""
        res = {}
        try:
            wb = Workbook()
            ws = wb.active
            ws.title = "Rapport d'inventaire"

            ref_count = 0
            units_count_kg = 0
            units_count_unit = 0
            initial_stock_unit = 0
            initial_stock_kg = 0

            initial_shelf_value = 0
            inventory_value = 0

            shelf_delta_abs_unit = 0
            shelf_delta_rel_unit = 0

            shelf_delta_abs_kg = 0
            shelf_delta_rel_kg = 0

            ws.append( ['Rapport d\'inventaire'] )
            ws.append( ['Rayon (Numéro) :', inventory_data['shelf_name'] + ' (' + str(inventory_data['shelf_num']) + ')'] )
            ws.append( ['Date :', inventory_data['date'].strftime("%d/%m/%Y")] )
            ws.append( [] )
            ws.append( ['Nom de l\'article', 'UoM', 'Stock théorique initial', 'Stock final', 'Delta (Qté)', 'Pertes (€)'] )
            titles_row = ws.max_row

            for p in inventory_data['products'] :
                p['delta'] = delta = float(p['qty']) - float(p['qty_available'])
                p['losses'] = losses = delta * float(p['standard_price'])
                ws.append( [p['name'], p['uom_id'][1], round(p['qty_available'], 2), round(p['qty'], 2), round(delta, 2), round(losses, 2)] )

                # Count references in shelf after inventory
                if p['qty'] > 0:
                    ref_count += 1

                # Calculate value in shelf after inventory
                inventory_value += float(p['qty']) * float(p['standard_price'])
                # And before as well
                initial_shelf_value += float(p['qty_available']) * float(p['standard_price'])

                # For products sold by unit
                if p['uom_id'][0] == 1:
                    # Count initial stock
                    initial_stock_unit += float(p['qty_available'])

                    # Count units in shelf
                    units_count_unit += float(p['qty'])

                    # Global delta in absolute value (found + missing units count)
                    shelf_delta_abs_unit += abs(delta)

                    # Global delta in relative value (found - missing units count)
                    shelf_delta_rel_unit += delta

                # For products sold by kg
                else:
                    initial_stock_kg += float(p['qty_available'])
                    units_count_kg += float(p['qty'])
                    shelf_delta_abs_kg += abs(delta)
                    shelf_delta_rel_kg += delta

            # END FOR

            # Calculate shelf delta in %
            if initial_stock_unit > 0 :
                shelf_delta_rel_unit_percent = (shelf_delta_rel_unit / initial_stock_unit) * 100
            else :
                shelf_delta_rel_unit_percent = 0

            if initial_stock_kg > 0 :
                shelf_delta_rel_kg_percent = (shelf_delta_rel_kg / initial_stock_kg) * 100
            else :
                shelf_delta_rel_kg_percent = 0

            # Losses in shelf (initial shelf value - final value)
            shelf_losses = initial_shelf_value - inventory_value

            # Shelf losses in % : shelf_losses / initial stock
            if initial_shelf_value > 0 :
                shelf_losses_percent = (shelf_losses / initial_shelf_value) * 100
            else :
                shelf_losses_percent = 0

            ws.append( [] )

            ws.append( ['Nombre total de références :', ref_count] )
            ws.append( ['Quantité totale de produits au KG :', round(units_count_kg, 2)] )
            ws.append( ['Quantité totale de produits à l\'unité :', round(units_count_unit, 2)] )
            ws.append( [] )
            ws.append( ['Valeur d\'inventaire du rayon :', round(inventory_value, 2)] )

            ws.append( [] )
            ws.append( ['Deltas du rayon'] )
            ws.append( ['Pour les produits à l\'UNITÉ'] )
            ws.append( ['   Delat absolu : ', round(shelf_delta_abs_unit, 2), '(= qté d\'unités trouvées + qté d\'unités disparues)'] )
            ws.append( ['   Delta relatif : ', round(shelf_delta_rel_unit, 2), '(= qté d\'unités trouvées - qté d\'unités disparues)'] )
            ws.append( ['   Delta relatif en % : ', round(shelf_delta_rel_unit_percent, 2), '(= delta relatif / stock total théorique initial)'] )
            ws.append( ['Pour les produits au KG'] )
            ws.append( ['   Delta absolu : ', round(shelf_delta_abs_kg, 2)] )
            ws.append( ['   Delta relatif : ', round(shelf_delta_rel_kg, 2)] )
            ws.append( ['   Delta relatif en % : ', round(shelf_delta_rel_kg_percent, 2)] )

            ws.append( [] )
            ws.append( ['Pertes pour le rayon : ', round(shelf_losses, 2), '(= valeur totale avant comptage - valeur totale d\'inventaire)'] )
            ws.append( ['Pertes en % du rayon : ', round(shelf_losses_percent, 2), '(= pertes du rayon / valeur totale avant comptage)'] )

            # User comments
            ws.append( [] )
            if 'user_comments_step1' not in inventory_data or inventory_data['user_comments_step1'] == '':
                inventory_data['user_comments_step1'] = 'Aucun'

            ws.append( ['Problèmes survenus durant le comptage en rayon :', inventory_data['user_comments_step1']] )
            # Merge cells for comments
            merge_begin = ws.max_row
            merge_end = ws.max_row+3
            ws.append( [] )
            ws.append( [] )
            ws.append( [] )
            ws.merge_cells(start_row=merge_begin, start_column=1, end_row=merge_end, end_column=1)
            ws.merge_cells(start_row=merge_begin, start_column=2, end_row=merge_end, end_column=6)
            # Styling merged cells
            top_left_cell = ws['A'+str(merge_begin)]
            top_left_cell.alignment = Alignment(vertical="top")
            top_left_cell.font = Font(bold=True)
            top_left_cell = ws['B'+str(merge_begin)]
            top_left_cell.alignment = Alignment(vertical="top")

            if 'user_comments' not in inventory_data or inventory_data['user_comments'] == '':
                inventory_data['user_comments'] = 'Aucun'
            ws.append( ['Problèmes survenus durant le comptage en réserve :', inventory_data['user_comments']] )
            merge_begin = ws.max_row
            merge_end = ws.max_row+3
            ws.append( [] )
            ws.append( [] )
            ws.append( [] )
            ws.merge_cells(start_row=merge_begin, start_column=1, end_row=merge_end, end_column=1)
            ws.merge_cells(start_row=merge_begin, start_column=2, end_row=merge_end, end_column=6)
            top_left_cell = ws['A'+str(merge_begin)]
            top_left_cell.alignment = Alignment(vertical="top")
            top_left_cell.font = Font(bold=True)
            top_left_cell = ws['B'+str(merge_begin)]
            top_left_cell.alignment = Alignment(vertical="top")


            # Styling

            # Columns size
            for column_cells in ws.columns:
                # "Auto fit" the first column width to content
                length = max(len(as_text(cell.value)) for cell in column_cells)

                # For other columns than the first, set size
                if column_cells[3].column_letter != "A" :
                    length = 20

                ws.column_dimensions[column_cells[3].column_letter].width = length

            # First column Bold
            col = ws.column_dimensions['A']
            col.font = Font(bold=True)

            # Make that line bold
            row = ws.row_dimensions[titles_row]
            row.font = Font(bold=True)


            # Save file
            report_file_name = "inventaire_rayon"+str(round(inventory_data['shelf_num'], 2))+"_"+inventory_data['date'].strftime("%Y-%m-%d")+".xlsx"

            wb.save(filename=report_file_name)

            # Attach report to Odoo inventory instance
            inventory = CagetteInventory(inventory_data['inventory_id'])
            inventory.attach_file(report_file_name)

            res['msg'] = 'Rapport enregistré.'

            # Store some inventory data in shelf & products tables
            res['products'] = inventory_data['products']
            res['shelf_losses'] = round(shelf_losses_percent, 2)
            # For delta, keep only one
            if shelf_delta_rel_kg_percent == 0:
                res['shelf_delta'] = round(shelf_delta_rel_unit_percent, 2)
            else:
                res['shelf_delta'] = round(shelf_delta_rel_kg_percent, 2)
        except Exception as e:
            res =  'Erreur lors de l\'enregistrement du rapport : ' + str(e)

        return res

class Shelfs(models.Model):

    def get_all(precision='full'):
        shelfs = []
        try:
            api = OdooAPI()
            if precision == 'simple':
                res = api.search_read('product.shelfs', [], ['name', 'sort_order'], order='sort_order asc')
            else:
                res = api.execute('product.shelfs', 'get', {})
            for r in res:
                if precision != 'simple':
                    if r['ongoing_inv_start_datetime'] == "1-01-01 00:00:00":
                        r['ongoing_inv_start_datetime'] = default_inventory_start_datetime
                    if r['date_last_product_added'] == "1-01-01":
                        r['date_last_product_added'] = ""
                    coop_logger.info(str(r))
                shelfs.append(r)
        except Exception as e:
            coop_logger.error("Rayons, get_all : %s", str(e))
        return shelfs

    @staticmethod
    def get_shelfs_sortorder(shelf_ids=[]):
        """Get all shelves sort_order"""
        res = []
        try:
            api = OdooAPI()
            if len(shelf_ids) == 0:
                c = []
            else:
                c = [['id', 'in', shelf_ids]]
            f = ['id', 'sort_order']
            res = api.search_read('product.shelfs', c, f)
        except Exception as e:
            coop_logger.error("Rayons, get_shelfs_sortorder : %s", str(e))
        return res

    @staticmethod
    def make_products_shelf_links(data):
        """Set shelf_id for each product found in data."""
        res = {}
        try:
            api = OdooAPI()
            res['done'] = []
            
            #  First of all, group product by shelf_id to save api server calls
            products_shelf = {}
            for elt in data:
                if elt['shelf_id'] not in products_shelf:
                    products_shelf[elt['shelf_id']] = []
                products_shelf[elt['shelf_id']].append(int(elt['product_id']))

            # iterate on each shelf element to record changes
            for shelf_id, product_ids in products_shelf.items():
                f = {'shelf_id': shelf_id}
                if api.update('product.product', product_ids , f):
                    res['done'] += product_ids
        except Exception as e:
            res['error'] = str(e)
            coop_logger.error("Rayons, make_products_shelf_links : %s", str(e))
        return res