<reponame>ksritharan/tectle<filename>python/Lib/site-packages/tectle/products.py<gh_stars>1-10
from .db import get_connection, get_data_dict
from flask import render_template, url_for, make_response
import requests
import traceback
import logging
logger = logging.getLogger()

def do_products():
    context = {'products_page': True}
    conn = get_connection()
    cur = conn.cursor()
    query = """
        SELECT p.*,
               (p.print_time/3600) print_time_hours,
               ((p.print_time % 3600)/60) print_time_minutes,
               (p.print_time % 60) print_time_seconds,
               pc.class package_class,
               COALESCE(i.quantity, 0) quantity
          FROM products p
     LEFT JOIN package_classes pc
               ON p.package_class_id = pc.id
     LEFT JOIN inventory i
               ON p.id = i.product_id
         ORDER BY sku
    """
    products = get_data_dict(cur, query)
    query = """
        SELECT *
          FROM package_classes
    """
    package_classes = get_data_dict(cur, query)
    
    query = """
        SELECT *
          FROM filament_inventory
    """
    filament_inventory = get_data_dict(cur, query)
    
    context['products'] = products
    context['package_classes'] = package_classes
    context['filament_inventory'] = filament_inventory
    return render_template('products.html', **context)
    
    
    
def do_add_class(class_data):
    response = None
    conn = get_connection()
    cur = conn.cursor()
    try:
        query = """
            SELECT COUNT(1)
              FROM package_classes
             WHERE class = '%(class)s'
        """ % class_data
        cur.execute(query)
        exists = cur.fetchone()[0] > 0
        if exists:
            response = make_response('package class already exists', 400)
        else:
            query = """
                INSERT INTO package_classes (class, weight_kg, cost)
                VALUES ('%(class)s', %(weight_kg)s, %(cost)s)
            """ % class_data
            cur.execute(query)
            conn.commit()
            response = make_response('success', 200)
    except Exception as e:
        logger.exception(e)
        tb = traceback.format_exc()
        response = make_response(tb, 400)
    return response

def do_edit_class(class_data):
    response = None
    conn = get_connection()
    cur = conn.cursor()
    try:
        query = """
            SELECT COUNT(1)
              FROM package_classes
             WHERE class = '%(class)s'
               AND id <> %(id)s
        """ % class_data
        cur.execute(query)
        exists = cur.fetchone()[0] > 0
        if exists:
            response = make_response('package class already exists', 400)
        else:
            query = """
                UPDATE package_classes
                   SET class = '%(class)s',
                       weight_kg = %(weight_kg)s,
                       cost = %(cost)s
                WHERE id = %(id)s
            """ % class_data
            cur.execute(query)
            conn.commit()
            response = make_response('success', 200)
    except Exception as e:
        logger.exception(e)
        tb = traceback.format_exc()
        response = make_response(tb, 400)
    return response

def do_remove_class(class_id):
    response = None
    conn = get_connection()
    cur = conn.cursor()
    try:
        query = """
            UPDATE products
               SET package_class_id = NULL
             WHERE package_class_id = %s
        """ % class_id
        cur.execute(query)
        query = """
            DELETE FROM package_classes
             WHERE id = %s
        """ % class_id
        cur.execute(query)
        conn.commit()
        response = make_response('success', 200)
    except Exception as e:
        logger.exception(e)
        tb = traceback.format_exc()
        response = make_response(tb, 400)
    
    return response

def do_edit_product_class(product_id, package_class_id):
    response = None
    conn = get_connection()
    cur = conn.cursor()
    try:
        query = """
            UPDATE products
               SET package_class_id = %s
             WHERE id = %s
        """ % (package_class_id, product_id)
        cur.execute(query)
        conn.commit()
        response = make_response('success', 200)
    except Exception as e:
        logger.exception(e)
        tb = traceback.format_exc()
        response = make_response(tb, 400)
    return response

def do_edit_filament(inv_data):
    response = None
    conn = get_connection()
    cur = conn.cursor()
    try:
        query = """
            SELECT COUNT(1)
              FROM filament_inventory
             WHERE color = '%(color)s'
        """ % inv_data
        cur.execute(query)
        exists = cur.fetchone()[0] > 0
        if not exists:
            response = make_response('color does not exist', 400)
        else:
            query = """
                UPDATE filament_inventory
                   SET weight_kg = %(weight_kg)s
                 WHERE color = '%(color)s'
            """ % inv_data
            cur.execute(query)
            conn.commit()
            response = make_response('success', 200)
    except Exception as e:
        logger.exception(e)
        tb = traceback.format_exc()
        response = make_response(tb, 400)
    return response

def do_edit_product_weight(weight_data):
    response = None
    conn = get_connection()
    cur = conn.cursor()
    try:
        query = """
            SELECT COUNT(1)
              FROM products
             WHERE sku = '%(sku)s'
        """ % weight_data
        cur.execute(query)
        exists = cur.fetchone()[0] > 0
        if not exists:
            response = make_response('sku does not exist', 400)
        else:
            query = """
                UPDATE products
                   SET filament_weight_kg = %(filament_weight_kg)s
                 WHERE sku = '%(sku)s'
            """ % weight_data
            cur.execute(query)
            conn.commit()
            response = make_response('success', 200)
    except Exception as e:
        logger.exception(e)
        tb = traceback.format_exc()
        response = make_response(tb, 400)
    return response
    
def do_edit_product_print_time(form_data):
    response = None
    conn = get_connection()
    cur = conn.cursor()
    try:
        query = """
            SELECT COUNT(1)
              FROM products
             WHERE sku = '%(sku)s'
        """ % form_data
        cur.execute(query)
        exists = cur.fetchone()[0] > 0
        if not exists:
            response = make_response('sku does not exist', 400)
        else:
            query = """
                UPDATE products
                   SET print_time = %(increment)s + print_time
                 WHERE sku = '%(sku)s'
            """ % form_data
            cur.execute(query)
            conn.commit()
            response = make_response('success', 200)
    except Exception as e:
        logger.exception(e)
        tb = traceback.format_exc()
        response = make_response(tb, 400)
    return response
    