def show_open_accounts():

print "Open Accounts Code"
connection_name = MySQLdb.connect(host='localhost', user='root', passwd='K0p!r1nn4', db='ACCOUNTS_NAME')
cursor = connection_name.cursor()

cursor.execute("SHOW TABLES;")
data1= cursor.fetchall()
tabla_cuenta = str(data1[len(data1)-1][0])

cursor.execute("SELECT * FROM `%s` WHERE STATE = 'A';" % tabla_cuenta )
data2= cursor.fetchall()
accounts= []

for j in xrange(len(data2)):
	accounts.append(data2[j][1])
print accounts

print '\n'

def new_account(date1):

##CREA ENTRADA NOMBRE DE CUENTA EN LA TABLA NOMBRES CUENTA
connection_name = MySQLdb.connect(host='localhost', user='root', passwd='K0p!r1nn4', db='ACCOUNTS_NAME')
cursor = connection_name.cursor()

cursor.execute("SHOW TABLES;")
data= cursor.fetchall()
tabla_cuenta = str(data[len(data)-1][0])	

add_bill = ("INSERT INTO `"+tabla_cuenta+"` "
			"(CODE, NAME, STATE, STARTED_TIME, PRINT_TIME, CHECKOUT_TIME) "
			"VALUES (%(CODE)s, %(NAME)s, %(STATE)s, %(STARTED_TIME)s, %(PRINT_TIME)s, %(CHECKOUT_TIME)s)")
CODE= cursor.lastrowid
str_now = date1
NAME = raw_input('Client name: ')
data_bill = {
	'CODE': CODE,
	'NAME': NAME,
	'STATE': 'A',
	'STARTED_TIME': str_now,
	'PRINT_TIME': '00:00:00',
	'CHECKOUT_TIME': '00:00:00',
}
cursor.execute(add_bill, data_bill)
connection_name.commit()
cursor.close()
connection_name.close()
connection_name = MySQLdb.connect(host='localhost', user='root', passwd='K0p!r1nn4', db='ACCOUNTS_PRODUCTS')
cursor = connection_name.cursor()

cursor.execute("CREATE TABLE `"+NAME+"` ("
	"  `CODE` mediumint(5) NOT NULL,"
	"  `NAME` varchar(40) NOT NULL,"
	"  `PRICE` float(8,2) NOT NULL,"
	"  `AMOUNT` smallint(5) NOT NULL,"
	"  `SUBTOTAL` float(8,2) NOT NULL,"
	"  PRIMARY KEY (`CODE`)"
	") ENGINE=InnoDB")	
connection_name.commit()
cursor.close()
connection_name.close()

def quick_sale():

con = MySQLdb.connect('localhost', 'root', 'K0p!r1nn4', 'PRODUCTS');
with con: 
	cur = con.cursor()
	cur.execute('''SELECT * FROM productos ORDER BY UnidadesVendidas DESC LIMIT 5''')
	rows = cur.fetchall()
	gabriel3 = range(len(rows))       
	for i,row in zip(gabriel3, rows):
		gabriel3[i] =str(row[2]) + str(row[3])
		print gabriel3[i]
	#return gabriel3
cur.close ()
con.close ()

def add_products(NAME):

family = raw_input('Family Code: ')
con = MySQLdb.connect('localhost', 'root', 'K0p!r1nn4', 'PRODUCTS');

with con: 
	cur = con.cursor()
	cur.execute('''SELECT * FROM productos WHERE Familia = %s''', family)
	data = cur.fetchall()
	print ('CODE	' + 'PRODUCT		' + '    PRICE')
	for row in data:
	        print ('%s	' % row[1] + '%s		' % row[2] + ' 				 	  %s' % row[3])
	        
	products_on_family = range(len(data))                       # ARRAY CON PRODUCTOS  ####### GABRIEL
	for i,row in zip(products_on_family, data):
		products_on_family[i] = str(row[2]) + str(row[3])
		
	#return products_on_family
	
cur.close()
con.close ()

#numero_linea = int(raw_input('Escriba el numero de línea seleccionada: '))  ## NUMERO DE LINEA (INICIA EN 0) ####### GABRIEL
#codigo = int(data[numero_linea][1])

CODE = utilities.getInt('Enter the product code: ',0, 	99999) ####### GABRIEL
AMOUNT = utilities.getInt('Enter amount: ',0, 	99999) ####### GABRIEL

	######################################  INICIO PARA INVENTARIO ################################

con = MySQLdb.connect('localhost', 'root', 'K0p!r1nn4', 'PRODUCTS');
with con: 
	cur = con.cursor()
	cur.execute('''UPDATE productos SET UnidadesVendidas = UnidadesVendidas + %s WHERE Codigo = %s''' %  (AMOUNT, CODE)) 
cur.close ()
con.close ()
con = MySQLdb.connect('localhost', 'root', 'K0p!r1nn4', 'PRODUCTS');
with con: 
	cur = con.cursor()
	cur.execute('''UPDATE productos SET ExistenciaUnidad1 = InventarioUnidad1 - UnidadesVendidas''') 
cur.close ()
con.close ()

######################################  FIN PARA INVENTARIO ################################ 

con1 = MySQLdb.connect('localhost', 'root', 'K0p!r1nn4', 'PRODUCTS');

with con1: 

	cur1 = con1.cursor()
	cur1.execute("SELECT * FROM productos WHERE Codigo = '%s'" % CODE)  #### ingresa y selecciona producto
	data1 = cur1.fetchall()
	for row in data1:
	        print ('%s	' % row[1] + '%s		' % row[2] + ' 				 	  %s' % row[3])

cur1.close ()
con1.close ()

### comprueba si el código del producto está apuntado en la cuenta
flag=int(check_code(str(NAME), str(CODE)))
if flag == 0:  
### ingresa la linea de producto a la tabla de nombre (ingreso del producto por primera vez)
	connection_name = MySQLdb.connect(host='localhost', user='root', passwd='K0p!r1nn4', db='ACCOUNTS_PRODUCTS')
	cursor = connection_name.cursor()
	add = ("INSERT INTO `"+NAME+"` "
			"(CODE, NAME, PRICE, AMOUNT, SUBTOTAL) "
			"VALUES (%(CODE)s, %(NAME)s, %(PRICE)s, %(AMOUNT)s, %(SUBTOTAL)s) ")
	for row in data1:
		L1=row[1]
		L2=row[2]
		L3=row[3]	

	product = {
		'CODE': L1,
		'NAME': L2,
		'PRICE': L3,
		'AMOUNT': AMOUNT,
		'SUBTOTAL': AMOUNT*L3,
	}
	cursor.execute(add, product)

 #Make sure data is committed to the database
	connection_name.commit()
	cursor.close()
	connection_name.close()
else:
	connection_name = MySQLdb.connect(host='localhost', user='root', passwd='K0p!r1nn4', db='ACCOUNTS_PRODUCTS')
	with connection_name: 
		cursor = connection_name.cursor()
		cursor.execute('''UPDATE %s SET AMOUNT = AMOUNT + %s WHERE CODE = %s''' %  (NAME, AMOUNT, CODE)) 
	connection_name.commit()
	cursor.close()
	connection_name.close()
	
	connection_name = MySQLdb.connect(host='localhost', user='root', passwd='K0p!r1nn4', db='ACCOUNTS_PRODUCTS')
	with connection_name: 
		cursor = connection_name.cursor()
		cursor.execute('''UPDATE %s SET SUBTOTAL = AMOUNT*PRICE''' %  (NAME)) 
	connection_name.commit()
	cursor.close()
	connection_name.close()

def check_code(name, code):

connection_name = MySQLdb.connect(host='localhost', user='root', passwd='K0p!r1nn4', db='ACCOUNTS_PRODUCTS')
cursor = connection_name.cursor()
cursor.execute("SELECT COUNT(1) FROM %s WHERE CODE = '%s'" % (name, code))
rows = cursor.fetchall()
for row in rows:
	igualdad_codigo=row[0]
	print (row[0]) 
connection_name.commit()
cursor.close()
connection_name.close()
return igualdad_codigo

def checkout_account(NAME):

print_account(NAME)
connection_name = MySQLdb.connect(host='localhost', user='root', passwd='K0p!r1nn4', db='ACCOUNTS_NAME')
cursor = connection_name.cursor()
cursor.execute("SHOW TABLES;")
data= cursor.fetchall()
tabla_cuenta = str(data[len(data)-1][0])
cursor.execute("UPDATE `%s` SET STATE = 'P' WHERE NAME = '%s'" % (tabla_cuenta, NAME))
connection_name.commit()
cursor.close()
connection_name.close()

def print_account(client):

now = datetime.datetime.now()
connection_name = MySQLdb.connect(host='localhost', user='root', passwd='K0p!r1nn4', db='ACCOUNTS_PRODUCTS')
cursor = connection_name.cursor()
cursor.execute("SELECT * FROM %s;" % client)
products = cursor.fetchall()
products_range=len(products)
width, height = letter
styles = getSampleStyleSheet()

styleH = styles['Heading1']
styleBH = styles["Normal"]
styleBH.alignment = TA_CENTER
styleNH = styles["Normal"]
styleNH.alignment = TA_RIGHT
styleN = styles["BodyText"]
styleN.alignment = TA_RIGHT

c = canvas.Canvas("bill.pdf", pagesize=letter)

head = Paragraph('''<b>La Magnolia Bar/Restaurante</b>''', styleH)
data= [[head]]
table = Table(data, colWidths=[10.0 * cm])
table.wrapOn(c, width, height)
table.drawOn(c, *coord(0.8, 1.5, height, cm))
c.drawString(30,735,'Frente al costado Este de la Municipalidad de Paraíso, Cartago, Costa Rica')
c.line(30,730,580,730)
c.drawString(30,715,'Client: %s'% client)
c.line(30,710,580,710)
c.drawString(480,748,"%s" % now.strftime("%Y-%m-%d %H:%M"))
c.line(480,745,580,745)


#Headers
hcode = Paragraph('''<b>Code</b>''', styleBH)
hname = Paragraph('''<b>Name</b>''', styleBH)
hamount = Paragraph('''<b>Price</b>''', styleBH)
hprice = Paragraph('''<b>Amount</b>''', styleBH)
hsubtotal = Paragraph('''<b>Subtotal</b>''', styleBH)

data= [[hcode, hname, hamount, hprice, hsubtotal]]

# Texts

for i in xrange(products_range):
	code = Paragraph('%s' % (str(products[i][0])), styleN) 
	name = Paragraph('%s' % (str(products[i][1])), styleN) 
	price = Paragraph('¢%s' % (str(products[i][2])), styleN)
	amount = Paragraph('%s' % (str(products[i][3])), styleN) 
	subtotal = Paragraph('¢%s' % (str(products[i][4])), styleN)
	data.append([code, name, price, amount, subtotal])
	print data[i]
print data
	
#Generate Table
table = Table(data, colWidths=[2.00 * cm, 7 * cm, 3 * cm, 3.5* cm, 3.5 * cm])
table.wrapOn(c, width, height)
table.drawOn(c, *coord(1, 7, height, cm))

#Total
tax = Paragraph('''<b>13%</b>''', styleN)
subtotal = Paragraph('''<b>¢%s</b>''' % calculate_total(products) , styleN)
total=Paragraph('''<b>¢%s</b>''' % (calculate_total(products)+(calculate_total(products)*0.13)) , styleN)
hsubtotal = Paragraph('''<b>SUBTOTAL:</b>''', styleNH)
htax = Paragraph('''<b>TAX:</b>''', styleNH)
htotal = Paragraph('''<b>TOTAL:</b>''', styleNH)
data= [[hsubtotal,subtotal],[htax,tax], [htotal,total]]
table2 = Table(data, colWidths=[2.00 * cm, 2.00 * cm])
table2.wrapOn(c, width, height)
table2.drawOn(c, *coord(16.5, 27, height, cm))
c.line(30,100,580,100)
c.save()
os.system('evince bill.pdf')

def others_menu():

os.system('clear')
mode=utilities.getInt('MENU\n\nSelect your choice:\n1.Delete products from Account\n2.Change account type\n3.\n4.\n\n0.Exit\n\nChoice:',0,3)
if mode==0:
	return
elif mode==1:
	print 'Under Construction!'
	time.sleep(3)
elif mode==2: 
	print 'Under Construction!'
	time.sleep(3)

def coord(x, y, height, unit=1):

  x, y = x * unit, height -  y * unit
  return x, y

def calculate_total(products):

total=0
for i in xrange(len(products)):
	total=total+products[i][4]
return total