small-inventory-management/src/admin.py

#!/usr/bin/env python
#
# All-in-one admin script.
#

import os
import sys
import csv
import time
import sqlite3 as sql

# Nonstandard imports:
import config

def init():
    '''
    Initiates the databases in SQLite
    '''
    conn = sql.connect(config.db)
    cursor = conn.cursor()

    try:
        cursor.execute("""

        CREATE TABLE catalog (
            name TEXT NOT NULL,
            price REAL NOT NULL, 
            stock INTEGER NOT NULL
        )
        
        """)
        
        cursor.executemany("""
        INSERT INTO catalog VALUES (?, ?, 0)
        """, config.prices)

        cursor.execute(
        """

        CREATE TABLE transactions (
            trans_id INTEGER PRIMARY KEY AUTOINCREMENT,
            customer TEXT,
            email TEXT,
            sabonete_mel INTEGER,
            sabonete_patchouli INTEGER,
            sabonete_papoula INTEGER,
            sabonete_capimlimao INTEGER,
            alcoolgel INTEGER,
            status TEXT,
            rating INTEGER,
            create_date TEXT,
            paid_date TEXT,
            sent_date TEXT
        )
        
        """)

        conn.commit()

        print "Database initialized."

    except sql.OperationalError:
        print "Databases already exist. Cannot init."
    
    conn.close()

def new_purchase(customer, email, mel, patchouli, papoula, capimlimao, gel):
    '''
    Logs in a new purchase in detail (every single product)
    '''
    conn = sql.connect(config.db)
    cursor = conn.cursor()

    purchases = (mel, patchouli, papoula, capimlimao, gel)
    new_id = "unknown"
    i = 0

    try:
        for product in config.products:
            cursor.execute("""
                SELECT stock FROM catalog
                WHERE name = ?
            """, (product,))
            
            in_stock = int(cursor.fetchone()[0])
            if purchases[i] > in_stock:
                i = product
                raise ValueError, "unavailable"

            else:
                cursor.execute("""
                    UPDATE catalog
                    SET stock = ?
                    WHERE name = ?
                """, (in_stock - purchases[i], product))

            i += 1

        # if everything was available, commit these changes:
        conn.commit()

        # log this transaction in the history:
        cursor.execute("""
            INSERT INTO transactions (
                customer, email, 
                sabonete_mel, 
                sabonete_patchouli, 
                sabonete_papoula, 
                sabonete_capimlimao, 
                alcoolgel, 
                create_date, status
            )
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, "pendente")
        """, (customer, email, mel, patchouli, papoula, capimlimao, gel,
                time.ctime()
        ))

        conn.commit()

        new_id = cursor.lastrowid
        conn.close()
        return (new_id, "ok")

    except sql.OperationalError, e:
        print "Error: %s" % e
        print "Please check if the order is correctly typed."
        conn.rollback()
        conn.close()
        raise sql.OperationalError, e

    except ValueError:
        conn.rollback()
        conn.close()
        new_id = "unavailable"
        return (new_id, i)


def view_availability(prodname):
    '''
    Returns the available quantity of a given product in the database
    '''
    conn = sql.connect(config.db)
    cursor = conn.cursor()
    
    cursor.execute("""
        SELECT stock FROM catalog
        WHERE name = ?
    """, (prodname,))

    quantity = cursor.fetchone()
    conn.close()

    if quantity is not None:
        return quantity[0]
    else:
        return "product not found"


def view_purchase(trans_id):
    '''
    Views an existing purchase.
    '''
    conn = sql.connect(config.db)
    cursor = conn.cursor()

    cursor.execute("""
    SELECT customer, email, 
        sabonete_mel,
        sabonete_patchouli,
        sabonete_papoula,
        sabonete_capimlimao,
        alcoolgel,
        status, create_date 
    FROM transactions
    WHERE trans_id = ?
    """, (trans_id,))

    order = cursor.fetchone()
    conn.close()

    return order


def update_price():
    '''
    When the price changes (in the config), run this to update the DB.
    '''
    conn = sql.connect(config.db)
    cursor = conn.cursor()

    try:
        cursor.executemany(
        """
        UPDATE catalog 
        SET price = ?
        WHERE name = ?
        """, config.prices
        )

        conn.commit()
    except sql.OperationalError, e:
        print "Error: %s" % e
        print "Please run the 'init' command to initialize the database."
        conn.rollback()

    conn.close()

def view_everything():
    '''
    View all the stock
    '''
    conn = sql.connect(config.db)
    cursor = conn.cursor()

    cursor.execute("SELECT name, stock FROM catalog")

    for row in cursor.fetchall():
        print row

    conn.close()


def get_transactions(status):
    '''
    Lists all transaction IDs and customers for a given status.

    Returns tuple of tuple(id, email address)
    '''
    conn = sql.connect(config.db)
    cursor = conn.cursor()

    cursor.execute("""
        SELECT trans_id, email FROM transactions
        WHERE status = ?
    """, (status,))

    transactions = cursor.fetchall()
    conn.close()

    return transactions


if __name__ == "__main__":
    if "update" in sys.argv:
        update_price()
    elif "init" in sys.argv:
        init()
    elif "new" in sys.argv:
        customer = raw_input("Nome do cliente: ")
        email = raw_input("Email para contato: ")
        while True:
            mel = input("Qtd sabonetes mel: ")
            patchouli = input("Qtd sabonetes patchouli: ")
            papoula = input("Qtd sabonetes papoula: ")
            capimlimao = input("Qtd sabonetes capim-limao: ")
            alcool_gel = input("Qtd alcool-gel: ")
            ok = raw_input("Informacoes ok? (s/n) ")
            if ok == "s":
                break
        try:
            code = new_purchase(customer, email, mel, patchouli, papoula, capimlimao, alcool_gel)
            if code[1] == "ok":
                print "Pedido cadastrado. Codigo do pedido: %s" % code[0]

            else:
                print "Produto em falta no estoque: %s" % code[1]

        except sql.OperationalError, e:
            print "Erro ao cadastrar: %s" % e
    elif "view" in sys.argv:
        transaction = raw_input("Codigo do pedido: ")
        purchase = view_purchase(transaction)

        if purchase is not None:
            print "Sumario do pedido #%s:" % transaction
            print "Cliente: %s (%s)" % (purchase[0], purchase[1])
            print "************************"
            print "Descricao do pedido:" 
            if purchase[2] != 0:
                print "Sabonete de Mel: %s unidades" % purchase[2]
            if purchase[3] != 0:
                print "Sabonete de Patchouli: %s unidades" % purchase[3]
            if purchase[4] != 0:
                print "Sabonete de Papoula: %s unidades" % purchase[4]
            if purchase[5] != 0:
                print "Sabonete de Capim-limao: %s unidades" % purchase[5]
            if purchase[6] != 0:
                print "Alcool-gel: %s unidades" % purchase[6]

            print "************************"
            print "Status do pedido: %s" % purchase[7]
            print "Criado em: %s" % purchase[8]
        else:
            print "Pedido nao encontrado."
    elif "debug" in sys.argv:
        view_everything()
    elif "trans" in sys.argv:
        trans = get_transactions("pendente")
        for tran in trans:
            print "Order #%s by %s" % (tran[0], tran[1])
    else:
        print "Options are 'init', 'update', 'new', 'view', 'trans' 'debug'"
    sys.exit(0)