exception.py

import logging
import requests
from flask import current_app as app
from flask_restful import Resource as BaseResource
from flask_restful import reqparse, fields, marshal_with

from sdp.common import db
from sdp.common import lego_week

log = logging.getLogger(__file__)
log.setLevel(logging.DEBUG)

class Resource(BaseResource):

    SAP_GET_EXCEPIONS_PATH = (
        'sap/opu/odata/sap/Y2SDP_SRV/ExceptionThresholdItemSet'
        '(BU=\'1002085\',Customer=\'{}\')?$format=json')
    SAP_GET_LISTINGS_PATH = (
        '/sap/opu/odata/sap/Y2SDP_SRV/MaterialSet?$filter=Customer_ID eq \'{}\'&$format=json')

    QUERY = '''\
    SELECT sdp_id as Customer,
           sdp_name as sdp_name,
           material_number as material_number,
           exception1,
           exception2,
           exception3,
           exception4,
           exception5
    FROM exception 
    inner join sdp_mapping on exception.customer = sdp_mapping.sdp_id
    WHERE customer = %(customer)s 
      AND dp = %(dp)s 
      AND ( exception1 > %(ex1)s OR 
            exception2 > %(ex2)s OR
            exception3 < %(ex3)s OR 
            abs(exception4) > %(ex4)s OR
            exception5 < %(ex5)s );
'''
    FIELDS = {
        'ItemID': fields.String(attribute='item_number', default='Missing'),
        'ItemDesc': fields.String(attribute='material_name', default='Missing'),
        'RegionID': fields.String(default='RegionID'),
        'BU': fields.String(default='BU'),
        'Customer': fields.String(default='Customer'),
        'CustomerDesc': fields.String(attribute='sdp_name', default='Missing'),
        'MaterialNumber': fields.String(attribute='material_number', default='Missing'),
        'ThemeID': fields.String(default='ThemeID'),
        'ThemeDesc': fields.String(attribute='theme_name', default='Missing'),
        'ProductNumber': fields.String(attribute='product_number', default='Missing'),
        'Exception1': fields.Float(
            attribute=lambda x: Resource._round_exception(x['exception1'])),
        'IsRedException1': fields.Boolean(),
        'Exception2': fields.Float(
            attribute=lambda x: Resource._round_exception(x['exception2'])),
        'IsRedException2': fields.Boolean(),
        'Exception3': fields.Float(
            attribute=lambda x: Resource._round_exception(x['exception3'])),
        'IsRedException3': fields.Boolean(),
        'Exception4': fields.Float(
            attribute=lambda x: Resource._round_exception(x['exception4'])),
        'IsRedException4': fields.Boolean(),
        'Exception5': fields.Float(
            attribute=lambda x: Resource._round_exception(x['exception5'])),
        'IsRedException5': fields.Boolean(),
    }

    @marshal_with(FIELDS)
    def get(self, customer):
        """
        Gets all exceptions for a single customer
        :param customer: SDP ID
        :return: JSON array of exceptions joined to customer and material data
        """
        # Retrieve exceptions threshold
        url_base = app.config['SAP_API_URL']
        url = '{}/{}'.format(
            url_base, self.SAP_GET_EXCEPIONS_PATH.format(customer))
        log.info('Calling {}'.format(url))

        res = requests.get(url, verify=False, auth=requests.auth.HTTPBasicAuth(
            app.config['SAP_API_USERNAME'], app.config['SAP_API_PASSWORD']))
        log.info('Result: {}'.format(res.json()))

        # Retrieve product/theme mapping
        listings_url = '{}/{}'.format(url_base, self.SAP_GET_LISTINGS_PATH.format(customer))
        customer_listings = requests.get(listings_url, verify=False, auth=requests.auth.HTTPBasicAuth(
            app.config['SAP_API_USERNAME'], app.config['SAP_API_PASSWORD']))

        try:
            product_theme = [
                (item['MaterialNo'], item['ThemeID'], item['ThemeText'], item['ProductNo'], item['MaterialShortText'])
                for item in customer_listings.json()['d']['results']
            ]
            product_theme = dict(map(lambda x: (x[0], (x[1], x[2], x[3], x[4])), product_theme))

        except KeyError as e:
            log.error(f"Unable to parse SAP response")
            log.error(f"{e}")
            raise

        dp = lego_week.Week.thisweek()
        log.info(f'exception DP request: {dp}')

        # NOTE: Exception5 is stored in database in percents
        query_params = {
            'customer': customer,
            'dp': str(dp),
            'ex1': float(res.json()['d']['Exception1']) / 100,
            'ex2': float(res.json()['d']['Exception2']) / 100,
            'ex3': float(res.json()['d']['Exception3']) / 100,
            'ex4': float(res.json()['d']['Exception5']) / 100,
            # Exception 4 and 5 are swapped in SAP in naming convention.
            'ex5': float(res.json()['d']['Exception4']),
        }

        items = db.fetch_all(self.QUERY, query_params)
        for item in items:
            item.update({
                'IsRedException1': bool(float(item['exception1']) > float(query_params['ex1'])),
                'IsRedException2': bool(float(item['exception2']) > float(query_params['ex2'])),
                'IsRedException3': bool(float(item['exception3']) < float(query_params['ex3'])),
                'IsRedException4': bool(abs(round(float(item['exception4']*100))) >= abs(round(float(query_params['ex4']*100)))),
                'IsRedException5': bool(float(item['exception5']) < float(query_params['ex5'])),
            })
            try:
                item.update({
                    'ThemeID': product_theme[item.get('material_number')][0],
                    'theme_name': product_theme[item.get('material_number')][1],
                    'product_number': product_theme[item.get('material_number')][2],
                    'item_number': product_theme[item.get('material_number')][2],
                    'material_name': product_theme[item.get('material_number')][3],
                })
            except KeyError:
                log.error("Product %s inexistent in SAP but exists in Exceptions", item.get('item_number'))

        return items

    @staticmethod
    def _round_exception(value):
        return round(float(value), 3)


item_parser = reqparse.RequestParser()
item_parser.add_argument('exception1', dest='ex1', type=float, required=True)
item_parser.add_argument('exception2', dest='ex2', type=float, required=True)
item_parser.add_argument('exception3', dest='ex3', type=float, required=True)
item_parser.add_argument('exception4', dest='ex4', type=float, required=True)
item_parser.add_argument('exception5', dest='ex5', type=float, required=True)


class ItemResource(BaseResource):

    QUERY = '''\
INSERT INTO exception (dp, customer, material_number, exception1, exception2,
    exception3, exception4, exception5)
VALUES (%(dp)s, %(customer)s, %(material_number)s, %(ex1)s, %(ex2)s, %(ex3)s,
        %(ex4)s, %(ex5)s)
ON DUPLICATE KEY UPDATE
    exception1=%(ex1)s, exception2=%(ex2)s, exception3=%(ex3)s,
    exception4=%(ex4)s, exception5=%(ex5)s;
'''

    def post(self, customer, item, dp):
        """
        Saves the exception data
        :param customer: SDP ID
        :param item: Material Number
        :param dp: The demand plan week
        :return: status
        """
        args = item_parser.parse_args()

        # TODO yrunts: need to validate dp

        exception = {
            'dp': dp,
            'customer': customer,
            'material_number': item,
            'ex1': args['ex1'],
            'ex2': args['ex2'],
            'ex3': args['ex3'],
            'ex4': args['ex4'],
            'ex5': args['ex5'],
        }
        log.info('Saving exception: {}'.format(exception))
        db.commit(self.QUERY, exception)

        return {'status': 'updated'}