123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304 |
- #!/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)
|