======= Métodos exclusivos del punto de venta =======

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