import serial
import pygame
from pygame.locals import *
import RPi.GPIO as GPIO
import time
import subprocess
import os
import speech_recognition as sr
from os import path
os.putenv('SDL_VIDEODRIVER', 'fbcon') # Display on piTFT
os.putenv('SDL_FBDEV', '/dev/fb1') #
os.putenv('SDL_MOUSEDRV', 'TSLIB') # Track mouse clicks on piTFT
os.putenv('SDL_MOUSEDEV', '/dev/input/touchscreen')
GPIO.setmode(GPIO.BCM)
GPIO.setup(27, GPIO.IN, pull_up_down=GPIO.PUD_UP)
#quit
def GPIO27_callback(channel):
  exit (0)
GPIO.add_event_detect(27,GPIO.FALLING,callback=GPIO27_callback)

def returnnumber(st):
 n=""
 for char in st:
  if char.isdigit():
   n=n+char
 return n
# get incoming number
def get_incoming(st):
 n=""
 in_flag = 0
 for char in st:
  if in_flag<2 and in_flag>0: n=n+char
  if char == '"':
   in_flag = in_flag +1
 return n[:-1]
# get connecting status
def get_status(st):
 n=0
 for char in st:
  if char.isdigit():
   n=n+1
  if n==3:
   return char

def check_over(st):
  return st[0]
# get battery status
def get_battery(st):
 n=""
 in_flag = 0
 for char in st:
  if in_flag<2 and in_flag>0: n=n+char
  if char == ',':
   in_flag = in_flag +1
 return n[:-1]
# translate time to minute:second mode
def get_progress(t):
    minutes=int(t/60)
    second=int(t-minutes*60)
    if second<10:
     time_string=str(minutes)+" : 0"+str(second)
    else:
     time_string=str(minutes)+" : "+str(second)
    return time_string

class Background(pygame.sprite.Sprite):
    def __init__(self, image_file, location):
        pygame.sprite.Sprite.__init__(self)  #call Sprite initializer
        self.image = pygame.image.load("/home/pi/project/"+image_file)
        self.rect = self.image.get_rect()
        self.rect.left, self.rect.top = location

pygame.init()
pygame.mouse.set_visible(False)
clock=pygame.time.Clock()
size = width, height = 240,320
black = 0, 0, 0
WHITE = 255, 255, 255
screen = pygame.display.set_mode(size)
background = Background("background.png",[0,0])
call = Background("call.png",[10,10])
music = Background("music.png",[80,10])
one = Background("1.png",[30,70])
two = Background("2.png",[100,70])
three = Background("3.png",[170,70])
four = Background("4.png",[30,130])
five = Background("5.png",[100,130])
six = Background("6.png",[170,130])
seven = Background("7.png",[30,190])
eight = Background("8.png",[100,190])
nine = Background("9.png",[170,190])
zero = Background("0.png",[100,250])
micro = Background("microphone.png",[40,25])
micro2 = Background ("microphone2.png",[40,25])
back = Background("back.png",[0,10])
call2 = Background("call2.png",[40,260])
delete = Background("delete.png",[180,260])
hang_up = Background("hang_up.png",[90,220])
hang_up2 = Background("hang_up.png",[130,220])
answer = Background("answer.png",[50,220])
battery = Background("battery.png",[200,10])

calling={'Calling...':(100,70)}
ring={'Ring...':(100,70)}
x1 = [20,40,60,80,100,120,140,160,180,200,220]
y1 = 40
my_font = pygame.font.Font(None, 15)
my_font2 = pygame.font.Font(None,25)
menu = 0
number = " "
press = 0
voice_recog=0
old_time=0
status='1'
previous_status=0

serialport = serial.Serial("/dev/ttyS0", 9600, timeout=0.5)
serialport.write("AT\r")
response = serialport.readlines(None)
serialport.write("ATE0\r")
response = serialport.readlines(None)
serialport.write("AT\r")
response = serialport.readlines(None)
serialport.write("AT+CBC\r")
response = serialport.readline()
while len(response)<5:
 response = serialport.readline()
battery_n=get_battery(response)

while 1:
 #main menu 
 if menu == 0:
  screen.blit(background.image,background.rect)
  screen.blit(call.image,call.rect)
  screen.blit(music.image,music.rect)
  screen.blit(battery.image,battery.rect)
  screen.blit(my_font.render(battery_n,True,(0,0,0)),(208,35))
  pygame.display.flip()

  for event in pygame.event.get():
   if(event.type is MOUSEBUTTONDOWN):
    pos = pygame.mouse.get_pos()
    x,y = pos
    if y>10 and y<70 and x>10 and x<70:
     menu = 1
    if y>10 and y<70 and x>80 and x<145:
     GPIO.cleanup()
     import m_player2

 #phone call menu
 if menu == 1:
  screen.fill(WHITE)
  screen.blit(one.image,one.rect)
  screen.blit(two.image,two.rect)
  screen.blit(three.image,three.rect)
  screen.blit(four.image,four.rect)
  screen.blit(five.image,five.rect)
  screen.blit(six.image,six.rect)
  screen.blit(seven.image,seven.rect)
  screen.blit(eight.image,eight.rect)
  screen.blit(nine.image,nine.rect)
  screen.blit(zero.image,zero.rect)
  screen.blit(back.image,back.rect)
  screen.blit(call2.image,call2.rect)
  screen.blit(delete.image,delete.rect)
  if voice_recog == 0:
    screen.blit(micro.image,micro.rect)
  screen.blit(my_font2.render(number,True,(0,0,0)),(80,30))
  pygame.display.flip()
  #voice recognition to dial    
  if voice_recog == 1:
   screen.blit(micro2.image,micro2.rect)
   pygame.display.flip()
   cmd = 'arecord -d 8 -D plughw:1,0 output.wav'
   print subprocess.check_output(cmd, shell=True)
   voice_recog = 0
   AUDIO_FILE = path.join(path.dirname('\home\pi\project'), 'output.wav')
   r=sr.Recognizer()
   with sr.AudioFile(AUDIO_FILE) as source:
    audio = r.record(source)  # read the entire audio file
   try:
       number = number + returnnumber(r.recognize_google(audio))
   except sr.UnknownValueError:
       number = number
   except sr.RequestError as e:
       number = number

  for event in pygame.event.get():
   if(event.type is MOUSEBUTTONDOWN):
    pos = pygame.mouse.get_pos()
    x,y = pos
    if x>0 and x<20 and y>0 and y<30:
     menu = 0
    if x>30 and x<80 and y>65 and y<115:
     number = number+'1'
    if x>100 and x<150 and y>65 and y<115:
     number = number+'2'
    if x>170 and x<220 and y>65 and y<115:
     number = number+'3'
    if x>30 and x<80 and y>125 and y<175:
     number = number+'4'
    if x>100 and x<150 and y>125 and y<175:
     number = number+'5'
    if x>170 and x<220 and y>125 and y<175:
     number = number+'6'
    if x>30 and x<80 and y>185 and y<235:
     number = number+'7'
    if x>100 and x<150 and y>185 and y<235:
     number = number+'8'
    if x>170 and x<220 and y>185 and y<235:
     number = number+'9'
    if x>100 and x<150 and y>245 and y<295:
     number = number+'0'
    if x>170 and x<220 and y>245 and y<295:
     number = number[:-1]
    #make call 
    if x>30 and x<80 and y>245 and y<295:
     menu = 2
     voice_recog=0
     print("Calling " + number);
     serialport.write("AT\r")
     response = serialport.readlines(None)
     serialport.write("ATD " + number + ';\r')
     response = serialport.readlines(None)
     print response

    if x>40 and x<70 and y>20 and y<60:
     voice_recog = 1
 #calling menu    
 if menu == 2:

   while len(s)<5:
    serialport.write("AT+CLCC\r")
    s=serialport.readline()
    status=get_status(s)

   if status == '0':
    previous_status=1
    if old_time==0:
     old_time=time.time()
    else:
     current_time=time.time()-old_time
     screen.fill(WHITE)
     screen.blit(my_font2.render(number,True,(0,0,0)),(80,30))
     screen.blit(my_font2.render(get_progress(current_time),True,(0,0,0)),(100,60))
     screen.blit(hang_up.image,hang_up.rect)
     pygame.display.flip()
   else:
    if previous_status==1:
     menu=1
     previous_status=0
    else:
     old_time=0
     screen.fill(WHITE)
     screen.blit(my_font2.render(number,True,(0,0,0)),(80,30))
     for my_text, text_pos in calling.items():
      text_surface = my_font2.render(my_text, True, black)
      rect = text_surface.get_rect(center=text_pos)
      screen.blit(text_surface, rect)
     screen.blit(hang_up.image,hang_up.rect)
     pygame.display.flip()

   #hang up
   for event in pygame.event.get():
    if(event.type is MOUSEBUTTONDOWN):
     pos = pygame.mouse.get_pos()
     x,y = pos
     print (x,y)
     if x>100 and x<160 and y>220 and y<270:
      old_time=0
      previous_status=0
      menu = 1
      n=0
      while n<2000:
       serialport.write("ATH\r")
       n=n+1
 #incoming call detection
 s = serialport.readline()
 if (s =='RING\r\n'):
     menu = 3
     serialport.write("AT+CLCC\r")
     s = serialport.readline()
     while len(s)<10:
      s = serialport.readline()
     incoming_number=get_incoming(s)
     number=incoming_number
 #incoming menu
 if menu == 3:
  screen.fill(WHITE)
  screen.blit(my_font2.render(incoming_number,True,(0,0,0)),(60,90))
  for my_text, text_pos in ring.items():
    text_surface = my_font2.render(my_text, True, black)
    rect = text_surface.get_rect(center=text_pos)
    screen.blit(text_surface, rect)
  screen.blit(hang_up2.image,hang_up2.rect)
  screen.blit(answer.image,answer.rect)

  pygame.display.flip()
  for event in pygame.event.get():
    if(event.type is MOUSEBUTTONDOWN):
     pos = pygame.mouse.get_pos()
     x,y = pos
     if x>50 and x<110 and y>220 and y<270:
      menu = 2
      n=0
      while n<800:
       serialport.write("ATA\r")
       n=n+1
      status='0'
     if x>130 and x<190 and y>220 and y<270:
      menu = 0
      n=0
      while n<2000:
       serialport.write("ATH\r")
       n=n+1

import serial
import pygame
from pygame.locals import *
import RPi.GPIO as GPIO
import time
import subprocess
import os
import speech_recognition as sr
from os import path
os.putenv('SDL_VIDEODRIVER', 'fbcon') # Display on piTFT
os.putenv('SDL_FBDEV', '/dev/fb1') #
os.putenv('SDL_MOUSEDRV', 'TSLIB') # Track mouse clicks on piTFT
os.putenv('SDL_MOUSEDEV', '/dev/input/touchscreen')
GPIO.setmode(GPIO.BCM)
GPIO.setup(27, GPIO.IN, pull_up_down=GPIO.PUD_UP)
#quit
def GPIO27_callback(channel):
  exit (0)
GPIO.add_event_detect(27,GPIO.FALLING,callback=GPIO27_callback)

def returnnumber(st):
 n=""
 for char in st:
  if char.isdigit():
   n=n+char
 return n
# get incoming number
def get_incoming(st):
 n=""
 in_flag = 0
 for char in st:
  if in_flag<2 and in_flag>0: n=n+char
  if char == '"':
   in_flag = in_flag +1
 return n[:-1]
# get connecting status
def get_status(st):
 n=0
 for char in st:
  if char.isdigit():
   n=n+1
  if n==3:
   return char

def check_over(st):
  return st[0]
# get battery status
def get_battery(st):
 n=""
 in_flag = 0
 for char in st:
  if in_flag<2 and in_flag>0: n=n+char
  if char == ',':
   in_flag = in_flag +1
 return n[:-1]
# translate time to minute:second mode
def get_progress(t):
    minutes=int(t/60)
    second=int(t-minutes*60)
    if second<10:
     time_string=str(minutes)+" : 0"+str(second)
    else:
     time_string=str(minutes)+" : "+str(second)
    return time_string

class Background(pygame.sprite.Sprite):
    def __init__(self, image_file, location):
        pygame.sprite.Sprite.__init__(self)  #call Sprite initializer
        self.image = pygame.image.load("/home/pi/project/"+image_file)
        self.rect = self.image.get_rect()
        self.rect.left, self.rect.top = location

pygame.init()
pygame.mouse.set_visible(False)
clock=pygame.time.Clock()
size = width, height = 240,320
black = 0, 0, 0
WHITE = 255, 255, 255
screen = pygame.display.set_mode(size)
background = Background("background.png",[0,0])
call = Background("call.png",[10,10])
music = Background("music.png",[80,10])
one = Background("1.png",[30,70])
two = Background("2.png",[100,70])
three = Background("3.png",[170,70])
four = Background("4.png",[30,130])
five = Background("5.png",[100,130])
six = Background("6.png",[170,130])
seven = Background("7.png",[30,190])
eight = Background("8.png",[100,190])
nine = Background("9.png",[170,190])
zero = Background("0.png",[100,250])
micro = Background("microphone.png",[40,25])
micro2 = Background ("microphone2.png",[40,25])
back = Background("back.png",[0,10])
call2 = Background("call2.png",[40,260])
delete = Background("delete.png",[180,260])
hang_up = Background("hang_up.png",[90,220])
hang_up2 = Background("hang_up.png",[130,220])
answer = Background("answer.png",[50,220])
battery = Background("battery.png",[200,10])

calling={'Calling...':(100,70)}
ring={'Ring...':(100,70)}
x1 = [20,40,60,80,100,120,140,160,180,200,220]
y1 = 40
my_font = pygame.font.Font(None, 15)
my_font2 = pygame.font.Font(None,25)
menu = 0
number = " "
press = 0
voice_recog=0
old_time=0
status='1'
previous_status=0

serialport = serial.Serial("/dev/ttyS0", 9600, timeout=0.5)
serialport.write("AT\r")
response = serialport.readlines(None)
serialport.write("ATE0\r")
response = serialport.readlines(None)
serialport.write("AT\r")
response = serialport.readlines(None)
serialport.write("AT+CBC\r")
response = serialport.readline()
while len(response)<5:
 response = serialport.readline()
battery_n=get_battery(response)

while 1:
 #main menu 
 if menu == 0:
  screen.blit(background.image,background.rect)
  screen.blit(call.image,call.rect)
  screen.blit(music.image,music.rect)
  screen.blit(battery.image,battery.rect)
  screen.blit(my_font.render(battery_n,True,(0,0,0)),(208,35))
  pygame.display.flip()

  for event in pygame.event.get():
   if(event.type is MOUSEBUTTONDOWN):
    pos = pygame.mouse.get_pos()
    x,y = pos
    if y>10 and y<70 and x>10 and x<70:
     menu = 1
    if y>10 and y<70 and x>80 and x<145:
     GPIO.cleanup()
     import m_player2

 #phone call menu
 if menu == 1:
  screen.fill(WHITE)
  screen.blit(one.image,one.rect)
  screen.blit(two.image,two.rect)
  screen.blit(three.image,three.rect)
  screen.blit(four.image,four.rect)
  screen.blit(five.image,five.rect)
  screen.blit(six.image,six.rect)
  screen.blit(seven.image,seven.rect)
  screen.blit(eight.image,eight.rect)
  screen.blit(nine.image,nine.rect)
  screen.blit(zero.image,zero.rect)
  screen.blit(back.image,back.rect)
  screen.blit(call2.image,call2.rect)
  screen.blit(delete.image,delete.rect)
  if voice_recog == 0:
    screen.blit(micro.image,micro.rect)
  screen.blit(my_font2.render(number,True,(0,0,0)),(80,30))
  pygame.display.flip()
  #voice recognition to dial    
  if voice_recog == 1:
   screen.blit(micro2.image,micro2.rect)
   pygame.display.flip()
   cmd = 'arecord -d 8 -D plughw:1,0 output.wav'
   print subprocess.check_output(cmd, shell=True)
   voice_recog = 0
   AUDIO_FILE = path.join(path.dirname('\home\pi\project'), 'output.wav')
   r=sr.Recognizer()
   with sr.AudioFile(AUDIO_FILE) as source:
    audio = r.record(source)  # read the entire audio file
   try:
       number = number + returnnumber(r.recognize_google(audio))
   except sr.UnknownValueError:
       number = number
   except sr.RequestError as e:
       number = number

  for event in pygame.event.get():
   if(event.type is MOUSEBUTTONDOWN):
    pos = pygame.mouse.get_pos()
    x,y = pos
    if x>0 and x<20 and y>0 and y<30:
     menu = 0
    if x>30 and x<80 and y>65 and y<115:
     number = number+'1'
    if x>100 and x<150 and y>65 and y<115:
     number = number+'2'
    if x>170 and x<220 and y>65 and y<115:
     number = number+'3'
    if x>30 and x<80 and y>125 and y<175:
     number = number+'4'
    if x>100 and x<150 and y>125 and y<175:
     number = number+'5'
    if x>170 and x<220 and y>125 and y<175:
     number = number+'6'
    if x>30 and x<80 and y>185 and y<235:
     number = number+'7'
    if x>100 and x<150 and y>185 and y<235:
     number = number+'8'
    if x>170 and x<220 and y>185 and y<235:
     number = number+'9'
    if x>100 and x<150 and y>245 and y<295:
     number = number+'0'
    if x>170 and x<220 and y>245 and y<295:
     number = number[:-1]
    #make call 
    if x>30 and x<80 and y>245 and y<295:
     menu = 2
     voice_recog=0
     print("Calling " + number);
     serialport.write("AT\r")
     response = serialport.readlines(None)
     serialport.write("ATD " + number + ';\r')
     response = serialport.readlines(None)
     print response

    if x>40 and x<70 and y>20 and y<60:
     voice_recog = 1
 #calling menu    
 if menu == 2:

   while len(s)<5:
    serialport.write("AT+CLCC\r")
    s=serialport.readline()
    status=get_status(s)

   if status == '0':
    previous_status=1
    if old_time==0:
     old_time=time.time()
    else:
     current_time=time.time()-old_time
     screen.fill(WHITE)
     screen.blit(my_font2.render(number,True,(0,0,0)),(80,30))
     screen.blit(my_font2.render(get_progress(current_time),True,(0,0,0)),(100,60))
     screen.blit(hang_up.image,hang_up.rect)
     pygame.display.flip()
   else:
    if previous_status==1:
     menu=1
     previous_status=0
    else:
     old_time=0
     screen.fill(WHITE)
     screen.blit(my_font2.render(number,True,(0,0,0)),(80,30))
     for my_text, text_pos in calling.items():
      text_surface = my_font2.render(my_text, True, black)
      rect = text_surface.get_rect(center=text_pos)
      screen.blit(text_surface, rect)
     screen.blit(hang_up.image,hang_up.rect)
     pygame.display.flip()

   #hang up
   for event in pygame.event.get():
    if(event.type is MOUSEBUTTONDOWN):
     pos = pygame.mouse.get_pos()
     x,y = pos
     print (x,y)
     if x>100 and x<160 and y>220 and y<270:
      old_time=0
      previous_status=0
      menu = 1
      n=0
      while n<2000:
       serialport.write("ATH\r")
       n=n+1
 #incoming call detection
 s = serialport.readline()
 if (s =='RING\r\n'):
     menu = 3
     serialport.write("AT+CLCC\r")
     s = serialport.readline()
     while len(s)<10:
      s = serialport.readline()
     incoming_number=get_incoming(s)
     number=incoming_number
 #incoming menu
 if menu == 3:
  screen.fill(WHITE)
  screen.blit(my_font2.render(incoming_number,True,(0,0,0)),(60,90))
  for my_text, text_pos in ring.items():
    text_surface = my_font2.render(my_text, True, black)
    rect = text_surface.get_rect(center=text_pos)
    screen.blit(text_surface, rect)
  screen.blit(hang_up2.image,hang_up2.rect)
  screen.blit(answer.image,answer.rect)

  pygame.display.flip()
  for event in pygame.event.get():
    if(event.type is MOUSEBUTTONDOWN):
     pos = pygame.mouse.get_pos()
     x,y = pos
     if x>50 and x<110 and y>220 and y<270:
      menu = 2
      n=0
      while n<800:
       serialport.write("ATA\r")
       n=n+1
      status='0'
     if x>130 and x<190 and y>220 and y<270:
      menu = 0
      n=0
      while n<2000:
       serialport.write("ATH\r")
       n=n+1

#Database related ptyhon function to implement projection and selection of songs on music player
#This script is a list of functions. Import this file to use all the functions.
# the coding refres to  https://dev.mysql.com/doc/connector-python/en/connector-python-tutorial-cursorbuffered.html
import mysql.connector
from mysql.connector import errorcode, Error
import numpy as np

#connect to database
def connect():
    """ Connect to MySQL database """
    try:
        print('Connecting to MySQL database...')
        conn = mysql.connector.connect(host='localhost',
                                       database='project5725',
                                       user='root',
                                       password='tbybzm6287065')
        if conn.is_connected():
            print('Connected to MySQL database')

    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
         print("Something is wrong with your user name or password")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
         print("Database does not exist")
        else:
         print(err)
    finally:
        conn.close()
        print('Connection completed')

#create database
def create_database(name):
    try:
        conn = mysql.connector.connect(host='localhost',
                                       user='root',
                                       password='tbybzm6287065')
        cursor = conn.cursor()

        cursor.execute(
            "CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(name))
    except mysql.connector.Error as err:
        print("Failed creating database: {}".format(err))
        exit(1)
    finally:
        try:
            conn.database = db_name


        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_BAD_DB_ERROR:
               create_database(db_name)
               conn.database = db_name
            else:
               print(err)
               exit(1)
        else:
            if conn.is_connected():
                 print('connection established.')
            else:
                 print('connection failed.')


#table format
TABLES = {}
TABLES['songs'] = (
    "CREATE TABLE `playlist` ("
    "  `song_id` int NOT NULL,"
    "  `song_name` varchar(100) NOT NULL,"
    "  `singer_name` varchar(100) NOT NULL,"
    "  `genre` varchar(100) NOT NULL,"
    "  `album_name` varchar(100) NOT NULL,"
    "  PRIMARY KEY (`song_id`)"
    ") ENGINE=InnoDB")

#create table in a specfic database
def create_tables(TABLES,db_name):
    response='0'
    conn = mysql.connector.connect(host='localhost',
                                       database=db_name,
                                       user='root',
                                       password='tbybzm6287065')
    if conn.is_connected():
                 print('connection established.')
    else:
                 print('connection failed.')
    cursor = conn.cursor()
    for name, ddl in TABLES.items():
        try:
            print("Creating table {}: ".format(name))
            cursor.execute(ddl)
        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:

                response="playlist already exists."
            else:

                response=err.msg
        else:
            print("OK")

    cursor.close()
    conn.close()

    return response

#add tuple (song) in table of music
def add_music(info):
   conn = mysql.connector.connect(user='root', database='project5725',password='tbybzm6287065')
   cursor = conn.cursor()
   add_music = ("INSERT INTO Songs "
               "(song_id, song_name, singer_name, genre, album_name) "
               "VALUES (%s, %s, %s, %s, %s)" "ON DUPLICATE KEY UPDATE")
   cursor.execute(add_music, info)
   conn.commit()

   cursor.close()
   conn.close()


#select music with its ID and print the result onto screen
def select_music(ID):
    conn = mysql.connector.connect(user='root', database='project5725',password='tbybzm6287065')
    cursor = conn.cursor()

    query = ("SELECT * FROM Songs "
             "WHERE song_id = %s")%(ID)


    cursor.execute(query)
    #fet each row of table
    row = cursor.fetchone()

    while row is not None:
     print(row)
     row = cursor.fetchone()

    cursor.close()
    conn.close()

#delete a song from the table list
def delete_music(ID):
    conn = mysql.connector.connect(user='root', database='project5725',password='tbybzm6287065')
    cursor = conn.cursor()

    query = ("DELETE FROM Songs "
             "WHERE song_id = %s")




    cursor.execute(query,(ID,))

    conn.commit()
    cursor.close()
    conn.close()


#select all the songs from music table and store into a numpy array
def select_all_music():
    try:
         conn = mysql.connector.connect(user='root', database='project5725',password='tbybzm6287065')
         cursor = conn.cursor()

         query = ("SELECT * FROM Songs ")


         result=[]
         counter=0
         cursor.execute(query)
         row = cursor.fetchone()

         #encoding the ptyhon list object into numpy array
         while row is not None:
          result=np.append(result,row)
          counter=counter+1
          row = cursor.fetchone()
    except Error as error:
         print(error)

    finally:
         result=np.reshape(result,(counter,5))
         cursor.close()
         conn.close()
    return result

#select all the songs from playlist and store into a numpy array 
def select_playlist():
    response='0'
    try:
         conn = mysql.connector.connect(user='root', database='project5725',password='tbybzm6287065')
         cursor = conn.cursor()

         query = ("SELECT * FROM playlist ")


         result=[]
         counter=0
         cursor.execute(query)
         row = cursor.fetchone()
         #encoding the ptyhon list object into numpy array
         while row is not None:
          result=np.append(result,row)
          counter=counter+1
          row = cursor.fetchone()
    except Error as error:
         print(error)
         response='No playlist exists'
    finally:
         result=np.reshape(result,(counter,5))
         cursor.close()
         conn.close()
    return result, response

# delete the playlist from database 
def delete_list(name):
   conn = mysql.connector.connect(user='root', database='project5725',password='tbybzm6287065')
   cursor = conn.cursor()
   response='0'
   try:
         delete_music = ("Drop Table %s")%(name)

         cursor.execute(delete_music,)
         response='Deletion of playlist completed'
   except Error as error:
         response='No playlist exists'

   finally:
         cursor.close()
         conn.close()
   return response

# add song into the playlist database
def add_list(info):
   conn = mysql.connector.connect(user='root', database='project5725',password='tbybzm6287065')
   cursor = conn.cursor()
   try:

         add_music = ("INSERT IGNORE INTO playlist"
               "(song_id, song_name, singer_name, genre, album_name) "
               "VALUES (%s, %s, %s, %s, %s)")
         cursor.execute(add_music,info,)
         conn.commit()
   except Error as error:
         print(error)
   else :print("OK")
   finally:
         cursor.close()
         conn.close()

#update specfic table with its specfic attribute
def update_music(attribute,ID,content):
    # prepare query and data
    query =("UPDATE Songs "
            "SET %s = '%s'"
            "WHERE song_id = %s")%(attribute,content,ID,)

    try:
        conn = mysql.connector.connect(user='root', database='project5725',password='tbybzm6287065')
        cursor = conn.cursor()

        cursor.execute(query)

        # accept the changes
        conn.commit()

    except Error as error:
        print(error)

    finally:
        cursor.close()
        conn.close()
#update_music('album_name',23,'Kingdom Come') 

#slection of songs based on name of singer
def select_singer(name):
    try:
         query = ("SELECT * FROM Songs "
             "WHERE singer_name = '%s'")%(name)

         conn = mysql.connector.connect(user='root', database='project5725',password='tbybzm6287065')
         cursor = conn.cursor()
         result=[]
         counter=0
         cursor.execute(query)

         row = cursor.fetchone()

         while row is not None:
           result=np.append(result,row)
           counter=counter+1
           row = cursor.fetchone()

    except Error as error:
         print(error)

    finally:
         result=np.reshape(result,(counter,5))
         cursor.close()
         conn.close()
    return result

#selection of songs based on genre 
def select_genre(name):
    try:
         query = ("SELECT * FROM Songs "
             "WHERE genre = '%s'")%(name)

         conn = mysql.connector.connect(user='root', database='project5725',password='tbybzm6287065')
         cursor = conn.cursor()
         result=[]
         counter=0
         cursor.execute(query)

         row = cursor.fetchone()

         while row is not None:
           result=np.append(result,row)
           counter=counter+1
           row = cursor.fetchone()

    except Error as error:
         print(error)

    finally:
         result=np.reshape(result,(counter,5))
         cursor.close()
         conn.close()
    return result

#selection of songs based on album name  
def select_album(name):
    try:
         query = ("SELECT * FROM Songs "
             "WHERE album_name = '%s'")%(name)

         conn = mysql.connector.connect(user='root', database='project5725',password='tbybzm6287065')
         cursor = conn.cursor()
         result=[]
         counter=0
         cursor.execute(query)

         row = cursor.fetchone()

         while row is not None:
           result=np.append(result,row)
           counter=counter+1
           row = cursor.fetchone()

    except Error as error:
         print(error)

    finally:
         result=np.reshape(result,(counter,5))
         cursor.close()
         conn.close()
    return result


#testing the connection
if __name__ == '__main__':
    connect()

# this script automatcally generate a predefined number of ground truth voice samples
# upon recording, instruction would be given
# number of samples is defined with variable 'k' in main function
import pyaudio
import wave
import os
import time
import struct
import numpy as np
import matplotlib.pyplot as plt
from scipy.fftpack import fft


# Options
CHUNK = 1024 # The size of each audio chunk coming from the input device.
FORMAT = pyaudio.paInt16 # Should not be changed, as this format is best for speech recognition.
RATE = 44100 # Speech recognition only works well with this rate.  
RECORD_SECONDS = 5 # Number of seconds to record, can be changed.
number=0
nrecord=4
CHANNELS=1
FILENAME=np.empty(nrecord,dtype=object)

for number in range (nrecord):

    FILENAME[number] = ("voice_sample%d"%number+".wav")



def save_audio(wav_file,k):
    """
    Stream audio from an input device and save it.
    """
    p = pyaudio.PyAudio()

    stream = p.open(
        format=FORMAT,
        channels=CHANNELS,
        rate=RATE,
        input=True,
        output=True,
        frames_per_buffer=CHUNK
    )

    print("* recording  %d"%(k+1))

    frames = []

    for i in range(0, int(RATE / CHUNK * RECORD_SECONDS)):
        data = stream.read(CHUNK)
        frames.append(data)

    print("* done recording %d"%(k+1))

    stream.stop_stream()
    stream.close()

    p.terminate()

    wf = wave.open(wav_file, 'wb')
    wf.setnchannels(CHANNELS)
    wf.setsampwidth(p.get_sample_size(FORMAT))
    wf.setframerate(RATE)
    wf.writeframes(b''.join(frames))
    wf.close()

# Run the thing!； k defines the number of files
if __name__ == '__main__':
     for k in range (nrecord):
          print("* prepare recording %d"%(k+1))
          time.sleep(2)
          save_audio(FILENAME[k],k)
     print("***********************************************")
     print("* Done all the recordings")

    #result = recognize(WAVE_OUTPUT_FILENAME)
    # print "You just said: {0}".format(result[0])

# A script to implement time domain based cross correlation to evaluate the matchness of testing wav file and groudn truth.
import pyaudio
import wave
import os
import time
import struct
import numpy as np
import matplotlib.pyplot as plt
from scipy.fftpack import fft
#format the wav
CHUNK = 1024 # The size of each audio chunk coming from the input device.
FORMAT = pyaudio.paInt16 # Should not be changed, as this format is best for speech recognition.
RATE = 44100 # Speech recognition only works well with this rate.  Don't change unless your microphone demands it.
RECORD_SECONDS = 5 # Number of seconds to record, can be changed.


CHANNELS=1
FILENAME=np.empty(nrecord,dtype=object)

#record a wav file 
def save_audio(wav_file,k):
    """
    Stream audio from an input device and save it.
    """

    p = pyaudio.PyAudio()

    #device = find_device(p, ["input", "mic", "audio"])
    #device_info = p.get_device_info_by_index(device)
    #channels = int(device_info['maxInputChannels'])

    stream = p.open(
        format=FORMAT,
        channels=CHANNELS,
        rate=RATE,
        input=True,
        output=True,
        frames_per_buffer=CHUNK
    )

    print("* recording  %d"%(k+1))

    frames = []

    for i in range(0, int(RATE / CHUNK * RECORD_SECONDS)):
        data = stream.read(CHUNK)
        frames.append(data)

    print("* done recording %d"%(k+1))

    stream.stop_stream()
    stream.close()

    p.terminate()

    wf = wave.open(wav_file, 'wb')
    wf.setnchannels(CHANNELS)
    wf.setsampwidth(p.get_sample_size(FORMAT))
    wf.setframerate(RATE)
    wf.writeframes(b''.join(frames))
    wf.close()

save_audio( "voice_signal.wav",0)


nrecord=4
records=[]
#encode the ground truth into numpy array 
for NUMBER in range (nrecord):
#encoding string into float number
 a=wave.open("voice_sample%d"%NUMBER+".wav", 'rb')
 datasize=a.getsampwidth()*a.getnframes()
 nframe=int(datasize/2)
 data=a.readframes(nframe)
 data_int=struct.unpack(str(nframe)+'h',data)
 data_np=np.array(data_int, float)
 # normalization
 total=np.sum(data_np,axis=0)
 data_np=data_np/total*1000000
 a.close()

 data_np=np.reshape(data_np,(1,nframe))

 records=np.append(records,data_np)
records=records.reshape(nrecord,nframe)
baseline=records
 #yf=fft(data_int)
 #yf=np.abs(yf)
 #length=len(yf)
 #yf=np.abs(yf[0:int(length/2)]/65536/CHUNK)
 #yf=np.reshape(yf,(1,int(nframe/2)))


#reocording wav file

save_audio("voice_signal.wav", 0)
#encoding of testing voice file
t=wave.open("voice_signal.wav", 'rb')
datasize=t.getsampwidth()*t.getnframes()
nframe=int(datasize/2)
test_data=t.readframes(nframe)
test_int=struct.unpack(str(nframe)+'h',test_data)
test_data=np.array(test_int, float)
total=np.sum(test_data,axis=0)
test_data=test_data/total*1000000
t.close()

#average every 100 bins
t_s=[]
for i in range (0,int(nframe/100)):
   a=i*100
   s=np.sum(test_data[a:a+100])
   s=s/100
   t_s=np.append(t_s,s)
t_s=np.reshape(t_s,(int(nframe/100)))
t_s.shape



##cross corelation using np.coorelate function
sum_result=[]
for j in range (4):
     sum=0
     sum= np.correlate(t_s, baseline[j])
     sum_result=np.append(sum_result,sum)
sum_result=np.reshape(sum_result,(nrecord,))
sum_result

# A script to implement FFT based cross correlation to evaluate the matchness of testing wav file and groudn truth.
import pyaudio
import wave
import os
import time
import struct
import numpy as np
import matplotlib.pyplot as plt
from scipy.fftpack import fft
#format the wav
NUMBER=0
records2=[]
nrecord=4
#record a wav file 
def save_audio(wav_file,k):
    """
    Stream audio from an input device and save it.
    """



    #device = find_device(p, ["input", "mic", "audio"])
    #device_info = p.get_device_info_by_index(device)
    #channels = int(device_info['maxInputChannels'])

    stream = p.open(
        format=FORMAT,
        channels=CHANNELS,
        rate=RATE,
        input=True,
        output=True,
        frames_per_buffer=CHUNK
    )

    print("* recording  %d"%(k+1))

    frames = []

    for i in range(0, int(RATE / CHUNK * RECORD_SECONDS)):
        data = stream.read(CHUNK)
        frames.append(data)

    print("* done recording %d"%(k+1))

    stream.stop_stream()
    stream.close()

    p.terminate()

    wf = wave.open(wav_file, 'wb')
    wf.setnchannels(CHANNELS)
    wf.setsampwidth(p.get_sample_size(FORMAT))
    wf.setframerate(RATE)
    wf.writeframes(b''.join(frames))
    wf.close()


#encode the ground truth into numpy array 
for NUMBER in range (nrecord):
 #encoding string into float number
 a=wave.open("voice_sample%d"%NUMBER+".wav", 'rb')
 datasize=a.getsampwidth()*a.getnframes()
 print("number_of_data:",datasize)
 nframe=int(datasize/2)
 print("number_of_frame:",nframe)
 data=a.readframes(nframe)
 data_int=struct.unpack(str(nframe)+'h',data)
 print("Length=",len(data_int))

 # normalization
 data_np=np.array(data_int, float)

 total=np.sum(data_np,axis=0)
 data_np=data_np/total*1000000

 a.close()

 #fft 
 yf=fft(data_np)
 yf=np.abs(yf)
 length=len(yf)
 yf=np.abs(yf[0:int(length/2)]/65536/CHUNK)

 # transform it into numpy array format
 yf=np.reshape(yf,(1,int(nframe/2)))

 records2=np.append(records2,yf)

records2=records2.reshape(nrecord,int(nframe/2))
records2.shape

# generate ground truth by taking average of adjacent 100 bins to reduce noise
baseline=[]
for i in range (0, nrecord):
   r=[]
   for j in range (0,int(nframe/100)):
      a=i*100
      s=np.sum(records2[i][a:a+100])
      s=s/100
      r=np.append(r,s)
   r=np.reshape(r,(int(nframe/100),1))
   baseline=np.append(baseline,r)
baseline=np.reshape(baseline, (nrecord,(int(nframe/100))) )
print(baseline.shape)


#reocording wav file

save_audio("voice_signal3.wav", 0)

#encodig the testing wav file
t=wave.open("voice_signal3.wav", 'rb')
datasize=t.getsampwidth()*t.getnframes()
nframe=int(datasize/2)
data=t.readframes(nframe)
data_int=struct.unpack(str(nframe)+'h',data)
t.close()
data_np=np.array(data_int, float)

#normalization
total=np.sum(data_np,axis=0)
data_np=data_np/total*1000000

#fft
tf=fft(data_np)
length=len(tf)
tf=np.abs(tf[0:int(length/2)]/65536/CHUNK)
tf=np.abs(tf)

#slection of songs based on genre
t_s=[]
for i in range (0,int(nframe/100)):
   a=i*100
   s=np.sum(tf[a:a+100])
   s=s/100
   t_s=np.append(t_s,s)
t_s=np.reshape(t_s,(int(nframe/100)))
t_s.shape
sum_result=[]

#multiplication; cross corelation
for j in range (nrecord):
     sum=0
     for i in range (int(nframe/100)):
         sum=sum+t_s[i]*baseline[j][i]
     sum_result=np.append(sum_result,sum)
sum_result=np.reshape(sum_result,(nrecord,))
sum_result